VIEW: APPS.AMS_OFFER_PRODUCTS_V

Source

View Text - Preformatted

SELECT distinct qph.list_header_id , itm.inventory_item_id , itm.concatenated_segments , 'PRODUCT' FROM qp_pricing_attributes qp, qp_list_lines qpln, qp_list_headers qph, mtl_system_items_vl itm WHERE qph.list_header_id = qpln.list_header_id AND qpln.list_line_id = qp.list_line_id AND qp.product_attr_value = itm.inventory_item_id AND qp.product_attribute = 'PRICING_ATTRIBUTE1' AND qp.product_attribute_context = 'ITEM' UNION ALL SELECT distinct qph.list_header_id, cat.category_id , cat.category_concat_segs , 'FAMILY' FROM qp_pricing_attributes qp, qp_list_lines qpln, qp_list_headers qph, mtl_categories_v cat WHERE qph.list_header_id = qpln.list_header_id AND qpln.list_line_id = qp.list_line_id AND qp.product_attr_value = cat.category_id AND qp.product_attribute = 'PRICING_ATTRIBUTE2' AND qp.product_attribute_context = 'ITEM'

View Text - HTML Formatted

SELECT DISTINCT QPH.LIST_HEADER_ID
, ITM.INVENTORY_ITEM_ID
, ITM.CONCATENATED_SEGMENTS
, 'PRODUCT'
FROM QP_PRICING_ATTRIBUTES QP
, QP_LIST_LINES QPLN
, QP_LIST_HEADERS QPH
, MTL_SYSTEM_ITEMS_VL ITM
WHERE QPH.LIST_HEADER_ID = QPLN.LIST_HEADER_ID
AND QPLN.LIST_LINE_ID = QP.LIST_LINE_ID
AND QP.PRODUCT_ATTR_VALUE = ITM.INVENTORY_ITEM_ID
AND QP.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QP.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' UNION ALL SELECT DISTINCT QPH.LIST_HEADER_ID
, CAT.CATEGORY_ID
, CAT.CATEGORY_CONCAT_SEGS
, 'FAMILY'
FROM QP_PRICING_ATTRIBUTES QP
, QP_LIST_LINES QPLN
, QP_LIST_HEADERS QPH
, MTL_CATEGORIES_V CAT
WHERE QPH.LIST_HEADER_ID = QPLN.LIST_HEADER_ID
AND QPLN.LIST_LINE_ID = QP.LIST_LINE_ID
AND QP.PRODUCT_ATTR_VALUE = CAT.CATEGORY_ID
AND QP.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE2'
AND QP.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'