VIEW: APPS.CLN_PROCAT_CATITEM_V

Source

View Text - Preformatted

SELECT DISTINCT ll.LIST_HEADER_ID, ll.LIST_LINE_ID, ll.START_DATE_ACTIVE, ll.END_DATE_ACTIVE, ll.PRODUCT_PRECEDENCE, ll.operand 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 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')

View Text - HTML Formatted

SELECT DISTINCT LL.LIST_HEADER_ID
, LL.LIST_LINE_ID
, LL.START_DATE_ACTIVE
, LL.END_DATE_ACTIVE
, LL.PRODUCT_PRECEDENCE
, LL.OPERAND 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
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')