VIEW: APPS.AMS_CAMPAIGN_PRODUCTS_V

Source

View Text - Preformatted

SELECT distinct offr.act_offer_used_by_id , itm.inventory_item_id , itm.concatenated_segments , 'PRODUCT' FROM qp_pricing_attributes qp, qp_list_lines qpln, ams_act_offers offr, mtl_system_items_vl itm WHERE offr.qp_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' AND offr.arc_act_offer_used_by = 'CAMP' UNION ALL SELECT distinct offr.act_offer_used_by_id, cat.category_id , cat.category_concat_segs , 'FAMILY' FROM qp_pricing_attributes qp, qp_list_lines qpln, ams_act_offers offr, mtl_categories_v cat WHERE offr.qp_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' AND offr.arc_act_offer_used_by = 'CAMP'

View Text - HTML Formatted

SELECT DISTINCT OFFR.ACT_OFFER_USED_BY_ID
, ITM.INVENTORY_ITEM_ID
, ITM.CONCATENATED_SEGMENTS
, 'PRODUCT'
FROM QP_PRICING_ATTRIBUTES QP
, QP_LIST_LINES QPLN
, AMS_ACT_OFFERS OFFR
, MTL_SYSTEM_ITEMS_VL ITM
WHERE OFFR.QP_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'
AND OFFR.ARC_ACT_OFFER_USED_BY = 'CAMP' UNION ALL SELECT DISTINCT OFFR.ACT_OFFER_USED_BY_ID
, CAT.CATEGORY_ID
, CAT.CATEGORY_CONCAT_SEGS
, 'FAMILY'
FROM QP_PRICING_ATTRIBUTES QP
, QP_LIST_LINES QPLN
, AMS_ACT_OFFERS OFFR
, MTL_CATEGORIES_V CAT
WHERE OFFR.QP_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'
AND OFFR.ARC_ACT_OFFER_USED_BY = 'CAMP'