Monday, October 16, 2017

Oracle Pricing Table list

QP_LIST_HEADERS_B
QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.

QP_LIST_LINES
QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B. This table stores all types of list lines; price list lines, all types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Lookup Type, 'LIST_LINE_TYPE_CODE'.

QP_PRICING_ATTRIBUTES
QP_PRICING_ATTRIBUTES stores product information and pricing attributes. The PRODUCT_ATTRIBUTE and PRODUCT_ATTRIBUTE_VALUE columns identify the product or level in the item hierarchy, i.e item context at which the price or modifier is set. The PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE and PRICING_ATTR_VALUE_FROM columns store the pricing attributes which further define what is being priced. If the PRICING_ATTRIBUTE_CONTEXT is VOLUME the pricing attributes column stores the break unit attribute, e.g. item quantity and both the PRICING_ATTR_VALUE_FROM and PRICING_ATTR_VALUE_TO columns can be populated. The Product information is repeated for all pricing attributes. A record is always created for the VOLUME context which may or may not have an attribute defined.
Tables 

Qp_interface_list_header  
qp_interface_list_lines  
qp_interface_qualifiers  
qp_interface_pricing_attribs  
qp_interface_errors  
qp_list_headers_b  
qp_list_headers_tl  
qp_list_lines  
qp_qualifiers  
qp_pricing_attributes  
qp_rltd_modifiers


Relationship

QP_LIST_HEADERS_B  <---LIST_HEADER_ID ---> QP_LIST_LINES

QP_LIST_LINES <---LIST_HEADER_ID,LIST_LINE_ID ---> QP_PRICING_ATTRIBUTES

QP_PRICING_ATTRIBUTES <---PRODUCT_ATTR_VALUE <> TO_CHAR(MSI.INVENTORY_ITEM_ID)---> mtl_system_items_b

Useful Queries

 

    /* Formatted on 7/18/2014 11:02:10 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');           --Price List Name
    
/* Formatted on 7/18/2014 11:02:04 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name
    
    
/* Formatted on 7/18/2014 11:01:59 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');--Price List Name
    
/* Formatted on 7/18/2014 11:01:53 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;
    /* Formatted on 7/18/2014 11:01:45 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');                --Price List Name
    
/* Formatted on 7/18/2014 11:01:35 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name
    
    
/* Formatted on 7/18/2014 11:01:29 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');       --Price List Name


  /* Formatted on 7/18/2014 11:01:23 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...