Thursday, October 20, 2011

Item & Pricing Details



SELECT
MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 SKU,
MSI.ITEM_TYPE,
MCB.SEGMENT1 CATEGORY_NAME,
MCST.CATEGORY_SET_NAME,
MSI.DESCRIPTION,
QLH.NAME,
QLL.CREATION_DATE,
QLL.LIST_LINE_TYPE_CODE,
QLL.OPERAND,
QPA.PRODUCT_ATTRIBUTE_CONTEXT,
QPA.PRODUCT_ATTRIBUTE,
QPA.PRODUCT_ATTR_VALUE,
QPA.PRODUCT_UOM_CODE,
QPA.PRICING_ATTRIBUTE_CONTEXT,
QPA.PRICING_ATTRIBUTE,
QPA.PRICING_ATTR_VALUE_FROM
FROM
MTL_CATEGORY_SETS MCST,
MTL_CATEGORIES_B MCB,
MTL_ITEM_CATEGORIES MIC,
MTL_SYSTEM_ITEMS MSI,
QP_PRICING_ATTRIBUTES QPA,
QP_LIST_LINES QLL,
QP_LIST_HEADERS_TL QLH
WHERE
MIC.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MCST.STRUCTURE_ID = MCB.STRUCTURE_ID
AND QPA.LIST_LINE_ID = QLL.LIST_LINE_ID
AND QPA.LIST_HEADER_ID = QLH.LIST_HEADER_ID
AND QLH.LIST_HEADER_ID = QLL.LIST_HEADER_ID
AND MSI.INVENTORY_ITEM_ID = QPA.PRODUCT_ATTR_VALUE
AND QLH.LANGUAGE = 'US'
--AND QLH.NAME = 'US USD PRICE LIST' -- PRICE LIST NAME
--AND QPA.PRODUCT_ATTR_VALUE = '1928882' -- INVENTORY ITEM ID
--AND QPA.PRICING_ATTRIBUTE_CONTEXT = 'ITEM'
--AND MCST.CATEGORY_SET_NAME = ('PURCHASING') -- CATEGORY SET NAME
--AND MCB.SEGMENT1 = ('PURCHASING') -- CATEGORY NAME
--AND MSI.ORGANIZATION_ID = 7
--MSI.SEGMENT1 = '21212903-CWS' -- ITEM NUMBER
ORDER BY ITEM_TYPE DESC

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.