View: CLN_PROCAT_CATITEM_V

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

View Text

SELECT /*+ INDEX(LL QP_LIST_LINES_N1) INDEX(QPA QP_PRICING_ATTRIBUTES_N2) INDEX(MSIB MTL_SYSTEM_ITEMS_B_U1) INDEX(MSIT MTL_SYSTEM_ITEMS_TL_U1) INDEX(MCI MTL_CUSTOMER_ITEMS_U2 ) */ DISTINCT LL.LIST_HEADER_ID
, LL.LIST_LINE_ID
, LL.START_DATE_ACTIVE
, LL.END_DATE_ACTIVE
, LL.PRODUCT_PRECEDENCE
, MSIB.LIST_PRICE_PER_UNIT LIST_PRICE
, QPA.PRODUCT_UOM_CODE LIST_PRICE_UOM_CODE
, MSIB.MINIMUM_ORDER_QUANTITY
, MSIB.PRIMARY_UOM_CODE
, MSIB.MAX_WARRANTY_AMOUNT
, MSIB.CONCATENATED_SEGMENTS
, MSIB.INVENTORY_ITEM_ID
, MSIB.ORGANIZATION_ID
, MSIT.DESCRIPTION ITEM_DESCRIPTION
, HCA.PARTY_ID
, NVL(MCI.CUSTOMER_ITEM_NUMBER
, MSIB.CONCATENATED_SEGMENTS)
, (SELECT MAX(REVISION)
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MIR.INVENTORY_ITEM_ID = QPA.PRODUCT_ATTR_VALUE ) REVISION
, MSIB.SERVICE_ITEM_FLAG
, LL.CONTEXT
, LL.ATTRIBUTE1
, LL.ATTRIBUTE2
, LL.ATTRIBUTE3
, LL.ATTRIBUTE4
, LL.ATTRIBUTE5
, LL.ATTRIBUTE6
, LL.ATTRIBUTE7
, LL.ATTRIBUTE8
, LL.ATTRIBUTE9
, LL.ATTRIBUTE10
, LL.ATTRIBUTE11
, LL.ATTRIBUTE12
, LL.ATTRIBUTE13
, LL.ATTRIBUTE14
, LL.ATTRIBUTE15
, MCI.CUSTOMER_ITEM_NUMBER
FROM QP_LIST_LINES LL
, QP_PRICING_ATTRIBUTES QPA
, MTL_SYSTEM_ITEMS_B_KFV MSIB
, MTL_SYSTEM_ITEMS_TL MSIT
, MTL_CUSTOMER_ITEM_XREFS MCIX
, MTL_CUSTOMER_ITEMS MCI
, HZ_CUST_ACCOUNTS HCA
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 MSIB.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID
AND QPA.PRODUCT_ATTR_VALUE = MCIX.INVENTORY_ITEM_ID (+)
AND MCIX.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)
AND NVL(MCI.INACTIVE_FLAG
, 'N') = 'N'
AND QPA.EXCLUDER_FLAG = 'N'
AND QPA.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND LL.LIST_LINE_TYPE_CODE IN ('PLL'
, 'PBH')
AND NVL(MCI.CUSTOMER_ID
, HCA.CUST_ACCOUNT_ID) = HCA.CUST_ACCOUNT_ID
AND MSIT.LANGUAGE = USERENV('LANG')
AND PRODUCT_PRECEDENCE = (SELECT /*+ INDEX(QPLLIN QP_LIST_LINES_N1) INDEX(QPAIN 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 QPLLIN.LIST_LINE_ID = QPAIN.LIST_LINE_ID
AND QPAIN.EXCLUDER_FLAG = 'N'
AND QPAIN.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QPAIN.PRODUCT_ATTR_VALUE = QPA.PRODUCT_ATTR_VALUE)
AND ( (NVL(FND_PROFILE.VALUE('CLN_ITEM_SEND_CUST_XREF_ONLY')
, 'N') = 'Y'
AND MCI.CUSTOMER_ITEM_NUMBER IS NOT NULL) OR NVL(FND_PROFILE.VALUE('CLN_ITEM_SEND_CUST_XREF_ONLY')
, 'N') = 'N')

Columns