VIEW: APPS.CLN_PROCAT_ITEM_CATEGORIES_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)