Product: | AMS - Marketing |
---|---|
Description: | This view returns products / product categories associated to marketing objects like Events, campaigns. |
Implementation/DBA Data: | APPS.AMS_ACT_PRODUCTS_V |
SELECT AAP.ACTIVITY_PRODUCT_ID
, AAP.OBJECT_VERSION_NUMBER
, AAP.ARC_ACT_PRODUCT_USED_BY
, LKUP1.MEANING
, AAP.ACT_PRODUCT_USED_BY_ID
, AAP.LEVEL_TYPE_CODE
, LKUP2.MEANING
, AAP.CATEGORY_ID
, DECODE(AAP.CATEGORY_ID
, NULL
, NULL
, ICAT.CATEGORY_CONCAT_SEGS)
, AAP.CATEGORY_SET_ID
, DECODE(AAP.CATEGORY_SET_ID
, NULL
, NULL
, ICST.CATEGORY_SET_NAME)
, AAP.ORGANIZATION_ID
, AAP.INVENTORY_ITEM_ID
, DECODE(AAP.INVENTORY_ITEM_ID
, NULL
, NULL
, ITM.CONCATENATED_SEGMENTS)
, DECODE(AAP.INVENTORY_ITEM_ID
, NULL
, NULL
, ITM.DESCRIPTION)
, AAP.EXCLUDED_FLAG
, AAP.LINE_LUMPSUM_QTY
, AAP.LINE_LUMPSUM_AMOUNT
, AAP.SCAN_VALUE
, AAP.UOM_CODE
, AAP.ADJUSTMENT_FLAG
, AAP.SCAN_UNIT_FORECAST
, AAP.CHANNEL_ID
, AAP.QUANTITY
FROM AMS_ACT_PRODUCTS AAP
, MTL_SYSTEM_ITEMS_VL ITM
, MTL_CATEGORIES_V ICAT
, MTL_CATEGORY_SETS_V ICST
, AMS_LOOKUPS LKUP1
, AMS_LOOKUPS LKUP2
WHERE LKUP1.LOOKUP_TYPE(+)='ARC_ACT_PRODUCT_USED_BY'
AND LKUP1.LOOKUP_CODE(+)=AAP.ARC_ACT_PRODUCT_USED_BY
AND LKUP2.LOOKUP_TYPE(+)='AMS_PRODUCT_LEVEL'
AND LKUP2.LOOKUP_CODE(+)=AAP.LEVEL_TYPE_CODE
AND AAP.CATEGORY_ID=ICAT.CATEGORY_ID(+)
AND AAP.INVENTORY_ITEM_ID=ITM.INVENTORY_ITEM_ID(+)
AND AAP.ORGANIZATION_ID=ITM.ORGANIZATION_ID(+)
AND AAP.CATEGORY_SET_ID=ICST.CATEGORY_SET_ID(+)