Oracle EBS - Advanced Pricing - Modifier details Queries

 

-- =====================================================================

-- Advanced Pricing - Modifier details

-- Bind: :p_modifier_name  (e.g. 'TEST-MFR-01')

-- =====================================================================

-- ---------------------------------------------------------------------

-- Query 1: Modifiers Summary + Discounts/Charges + Pricing Attributes

--          + Line Qualifiers (aggregated, so no row duplication)

--          One row per modifier line / product / pricing attribute

-- ---------------------------------------------------------------------

SELECT qlh.name                              modifier_list_name,

       qlh.list_type_code                    list_type,            -- DLT = Discount List

       qlh.active_flag                       list_active,

       qlh.automatic_flag                    list_automatic,

       qlh.start_date_active                 list_start_date,

       qlh.end_date_active                   list_end_date,

       -- Modifiers Summary tab

       qll.list_line_no                      modifier_no,

       qll.modifier_level_code               modifier_level,       -- LINE / ORDER / LINEGROUP

       qll.list_line_type_code               modifier_type,        -- DIS / SUR / PBH / FREIGHT_CHARGE ...

       qll.start_date_active                 line_start_date,

       qll.end_date_active                   line_end_date,

       qll.automatic_flag                    line_automatic,

       qll.override_flag                     line_override,

       qll.pricing_phase_id,

       qll.product_precedence                precedence,

       qll.incompatibility_grp_code,

       qll.pricing_group_sequence            bucket,

       -- Discounts/Charges tab

       qll.arithmetic_operator               application_method,   -- % / AMT / NEWPRICE / LUMPSUM

       qll.operand                           value,

       qll.price_break_type_code,                                  -- POINT / RANGE (for PBH)

       qll.accrual_flag,

       qll.charge_type_code,

       qll.include_on_returns_flag,

       -- Product (Modifiers Summary - Product Attribute)

       qpa.product_attribute_context,

       qpa.product_attribute,                                      -- PRICING_ATTRIBUTE1 = Item

       qpa.product_attr_value,

       CASE WHEN qpa.product_attribute_context = 'ITEM'

             AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'

            THEN msib.segment1 END           item_number,

       qpa.product_uom_code,

       qpa.excluder_flag,                                          -- Y = Exclude button

       -- Pricing Attributes button

       qpa.pricing_attribute_context,

       qpa.pricing_attribute,

       qpa.comparison_operator_code          pricing_attr_operator,

       qpa.pricing_attr_value_from,

       qpa.pricing_attr_value_to,

       -- Line Qualifiers button (aggregated)

       (SELECT LISTAGG(qq.qualifier_grouping_no || ':' ||

                       qq.qualifier_context || '.' || qq.qualifier_attribute || ' ' ||

                       qq.comparison_operator_code || ' ' ||

                       qq.qualifier_attr_value ||

                       NVL2(qq.qualifier_attr_value_to, '-' || qq.qualifier_attr_value_to, NULL),

                       ' | ') WITHIN GROUP (ORDER BY qq.qualifier_grouping_no, qq.qualifier_id)

        FROM   qp_qualifiers qq

        WHERE  qq.list_header_id = qll.list_header_id

        AND    qq.list_line_id   = qll.list_line_id)              line_qualifiers

FROM   qp_list_headers_vl     qlh,

       qp_list_lines          qll,

       qp_pricing_attributes  qpa,

       mtl_system_items_b     msib

WHERE  qlh.list_header_id      = qll.list_header_id

AND    qll.list_line_id        = qpa.list_line_id (+)

AND    msib.inventory_item_id (+) = DECODE(qpa.product_attribute,

                                           'PRICING_ATTRIBUTE1', qpa.product_attr_value)

AND    msib.organization_id   (+) = qp_util.get_item_validation_org

AND    qlh.name                = :p_modifier_name

ORDER  BY qll.list_line_no, qpa.excluder_flag, qpa.pricing_attribute_id;

-- ---------------------------------------------------------------------

-- Query 2: Line Qualifiers (detail - one row per qualifier)

-- ---------------------------------------------------------------------

SELECT qlh.name                       modifier_list_name,

       qll.list_line_no               modifier_no,

       qq.qualifier_grouping_no       grouping_no,

       qq.qualifier_context,

       qq.qualifier_attribute,

       qq.comparison_operator_code    operator,

       qq.qualifier_attr_value        value_from,

       qq.qualifier_attr_value_to     value_to,

       qq.qualifier_precedence        precedence,

       qq.start_date_active,

       qq.end_date_active

FROM   qp_list_headers_vl qlh,

       qp_list_lines      qll,

       qp_qualifiers      qq

WHERE  qlh.list_header_id = qll.list_header_id

AND    qq.list_header_id  = qll.list_header_id

AND    qq.list_line_id    = qll.list_line_id

AND    qlh.name           = :p_modifier_name

ORDER  BY qll.list_line_no, qq.qualifier_grouping_no;

-- ---------------------------------------------------------------------

-- Query 3: List (header) Qualifiers - "List Qualifiers" button

--          Header-level qualifiers are stored with list_line_id = -1

-- ---------------------------------------------------------------------

SELECT qlh.name                       modifier_list_name,

       qq.qualifier_grouping_no       grouping_no,

       qq.qualifier_context,

       qq.qualifier_attribute,

       qq.comparison_operator_code    operator,

       qq.qualifier_attr_value        value_from,

       qq.qualifier_attr_value_to     value_to,

       qq.start_date_active,

       qq.end_date_active

FROM   qp_list_headers_vl qlh,

       qp_qualifiers      qq

WHERE  qq.list_header_id  = qlh.list_header_id

AND    NVL(qq.list_line_id, -1) = -1

AND    qlh.name           = :p_modifier_name

ORDER  BY qq.qualifier_grouping_no;

-- ---------------------------------------------------------------------

-- Query 4: Price Breaks (only for modifier lines of type PBH)

-- ---------------------------------------------------------------------

SELECT qlh.name                       modifier_list_name,

       parent.list_line_no            pbh_modifier_no,

       parent.price_break_type_code,

       child.list_line_no             break_line_no,

       qpa.pricing_attribute          break_attribute,      -- e.g. PRICING_ATTRIBUTE10 = Item Quantity

       qpa.pricing_attr_value_from    break_from,

       qpa.pricing_attr_value_to      break_to,

       child.arithmetic_operator      application_method,

       child.operand                  value

FROM   qp_list_headers_vl    qlh,

       qp_list_lines         parent,

       qp_rltd_modifiers     rm,

       qp_list_lines         child,

       qp_pricing_attributes qpa

WHERE  qlh.list_header_id       = parent.list_header_id

AND    parent.list_line_type_code = 'PBH'

AND    rm.from_rltd_modifier_id = parent.list_line_id

AND    rm.rltd_modifier_grp_type = 'PRICE BREAK'

AND    child.list_line_id       = rm.to_rltd_modifier_id

AND    qpa.list_line_id         = child.list_line_id

AND    qlh.name                 = :p_modifier_name

ORDER  BY parent.list_line_no,

          TO_NUMBER(qpa.pricing_attr_value_from);