View: AMS_CAMPAIGN_PRODUCTS_V

Product:	AMS - Marketing
Description:	This view returns the products associated to the campaigns.
Implementation/DBA Data:	APPS.AMS_CAMPAIGN_PRODUCTS_V

View Text

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'

Columns