Tables
QP_LIST_HEADERS_BQP_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.
Relationship
QP_LIST_HEADERS_BQP_LIST_LINES
QP_PRICING_ATTRIBUTES TO_CHAR(MSI.INVENTORY_ITEM_ID)---> mtl_system_items_b
Useful Queries
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
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
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
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;
转载于:http://blog.itpub.net/26687597/viewspace-1204376/