View: CLN_PROCAT_ITEM_CATEGORIES_V

Product:	CLN - Supply Chain Trading Connector for RosettaNet
Description:
Implementation/DBA Data:	APPS.CLN_PROCAT_ITEM_CATEGORIES_V

View Text

SELECT DISTINCT LL.LIST_HEADER_ID
, MCT.DESCRIPTION CATEGORY_DESCRIPTION
, MCT.CATEGORY_ID
FROM QP_LIST_LINES LL
, QP_PRICING_ATTRIBUTES QPA
, MTL_SYSTEM_ITEMS_B_KFV MSIB
, MTL_CUSTOMER_ITEM_XREFS MCIX
, MTL_CUSTOMER_ITEMS MCI
, HZ_CUST_ACCOUNTS HCA
, MTL_DEFAULT_CATEGORY_SETS MDC
, MTL_ITEM_CATEGORIES MIC
, MTL_CATEGORIES_V MC
, MTL_CATEGORY_SETS_B MCSB
, MTL_CATEGORIES_TL MCT
WHERE LL.LIST_LINE_ID = QPA.LIST_LINE_ID
AND QPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QPA.PRODUCT_ATTR_VALUE = MSIB.INVENTORY_ITEM_ID
AND QPA.PRODUCT_ATTR_VALUE = MCIX.INVENTORY_ITEM_ID (+)
AND MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID (+)
AND QPA.EXCLUDER_FLAG = 'N'
AND QPA.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND LL.LIST_LINE_TYPE_CODE IN ('PLL'
, 'PBH')
AND MCI.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND MSIB.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MDC.FUNCTIONAL_AREA_ID = 7
AND MIC.CATEGORY_SET_ID = MDC.CATEGORY_SET_ID
AND MIC.CATEGORY_SET_ID = MCSB.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MC.STRUCTURE_ID = MCSB.STRUCTURE_ID
AND MIC.CATEGORY_ID = MCT.CATEGORY_ID
AND MCT.LANGUAGE = USERENV('LANG')
AND PRODUCT_PRECEDENCE = (SELECT /*+ INDEX(QP_PRICING_ATTRIBUTES_N2) */ MAX(PRODUCT_PRECEDENCE)
FROM QP_LIST_LINES QPLLIN
, QP_PRICING_ATTRIBUTES QPAIN
WHERE QPLLIN.LIST_HEADER_ID = LL.LIST_HEADER_ID
AND QPAIN.LIST_LINE_ID = QPLLIN.LIST_LINE_ID
AND QPAIN.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QPAIN.PRODUCT_ATTR_VALUE = QPA.PRODUCT_ATTR_VALUE
AND QPAIN.EXCLUDER_FLAG = 'N'
AND QPAIN.PRICING_ATTRIBUTE_CONTEXT IS NULL)

Columns