View: AMS_PROGRAM_COMPONENTS_V

Product:	AMS - Marketing
Description:	This view is created for giving general information about progam Components
Implementation/DBA Data:	APPS.AMS_PROGRAM_COMPONENTS_V

View Text

SELECT CAM.CAMPAIGN_ID || CAM.ROLLUP_TYPE CAMPAIGN_KEY
, CAM.PARENT_CAMPAIGN_ID || PARENT.ROLLUP_TYPE PROGRAM_KEY
, CAM.CAMPAIGN_ID
, CAM.CAMPAIGN_NAME
, CAM.PARENT_CAMPAIGN_ID PROGRAM_ID
, CAM.ROLLUP_TYPE
, TO_CHAR(CAM.VERSION_NO)
, CAM.SOURCE_CODE
, CAM.STATUS_CODE
, CAM.STATUS_DATE
, CAM.USER_STATUS_ID
, USRSTSTL.NAME USER_STATUS
, CAM.BUSINESS_UNIT_ID
, CAM.PRIORITY
, CAM.CAMPAIGN_TYPE OBJECT_SUB_TYPE
, CAM.START_PERIOD_NAME
, CAM.ACTUAL_EXEC_START_DATE START_DATE
, CAM.END_PERIOD_NAME
, CAM.ACTUAL_EXEC_END_DATE END_DATE
, CAM.CITY_ID COUNTRY_ID
, JLOCAREATL.LOCATION_AREA_NAME COUNTRY_NAME
, CAM.OWNER_USER_ID OWNER_ID
, JTFRESVL.RESOURCE_NAME OWNER_NAME
, JTFRESVL.SOURCE_EMAIL OWNER_MAIL_ID
, CAM.DESCRIPTION
, CAM.FUNCTIONAL_CURRENCY_CODE
, CAM.BUDGET_AMOUNT_FC
, CAM.TRANSACTION_CURRENCY_CODE
, CAM.BUDGET_AMOUNT_TC
, CAM.OBJECT_VERSION_NUMBER
, CAM.CUSTOM_SETUP_ID
FROM AMS_CAMPAIGNS_VL CAM
, JTF_LOC_HIERARCHIES_B JLOCB
, JTF_LOC_AREAS_TL JLOCAREATL
, JTF_RS_RESOURCE_EXTNS_VL JTFRESVL
, AMS_USER_STATUSES_TL USRSTSTL
, AMS_CAMPAIGNS_ALL_B PARENT
WHERE CAM.ACTIVE_FLAG = 'Y'
AND CAM.SHOW_CAMPAIGN_FLAG = 'Y'
AND USRSTSTL.USER_STATUS_ID = CAM.USER_STATUS_ID
AND USRSTSTL.LANGUAGE = USERENV('LANG')
AND JLOCB.LOCATION_HIERARCHY_ID (+) = CAM.CITY_ID
AND JLOCAREATL.LOCATION_AREA_ID (+)= JLOCB.COUNTRY_ID
AND JLOCAREATL.LANGUAGE (+) = USERENV('LANG')
AND JTFRESVL.RESOURCE_ID = CAM.OWNER_USER_ID
AND PARENT.CAMPAIGN_ID (+) = CAM.PARENT_CAMPAIGN_ID UNION ALL SELECT EVH.EVENT_HEADER_ID || 'EVEH'
, EVH.PROGRAM_ID || PARENT.ROLLUP_TYPE
, EVH.EVENT_HEADER_ID
, EVH.EVENT_HEADER_NAME
, EVH.PROGRAM_ID
, 'EVEH'
, NULL
, EVH.SOURCE_CODE
, EVH.SYSTEM_STATUS_CODE STATUS_CODE
, EVH.LAST_STATUS_DATE STATUS_DATE
, EVH.USER_STATUS_ID
, USRSTSTL.NAME USER_STATUS
, EVH.BUSINESS_UNIT_ID
, EVH.PRIORITY_TYPE_CODE PRIORITY
, EVH.EVENT_TYPE_CODE OBJECT_SUB_TYPE
, EVH.START_PERIOD_NAME
, EVH.ACTIVE_FROM_DATE START_DATE
, EVH.END_PERIOD_NAME
, EVH.ACTIVE_TO_DATE END_DATE
, TO_NUMBER(EVH.COUNTRY_CODE) COUNTRY_ID
, JLOCAREATL.LOCATION_AREA_NAME COUNTRY_NAME
, EVH.OWNER_USER_ID OWNER_ID
, JTFRESVL.RESOURCE_NAME OWNER_NAME
, JTFRESVL.SOURCE_EMAIL OWNER_MAIL_ID
, EVH.DESCRIPTION
, EVH.CURRENCY_CODE_FC FUNCTIONAL_CURRENCY_CODE
, EVH.FUND_AMOUNT_FC BUDGET_AMOUNT_FC
, EVH.CURRENCY_CODE_TC TRANSACTION_CURRENCY_CODE
, EVH.FUND_AMOUNT_TC BUDGET_AMOUNT_TC
, EVH.OBJECT_VERSION_NUMBER
, EVH.SETUP_TYPE_ID CUSTOM_SETUP_ID
FROM AMS_EVENT_HEADERS_VL EVH
, JTF_LOC_HIERARCHIES_B JLOCB
, JTF_LOC_AREAS_TL JLOCAREATL
, JTF_RS_RESOURCE_EXTNS_VL JTFRESVL
, AMS_USER_STATUSES_TL USRSTSTL
, AMS_CAMPAIGNS_ALL_B PARENT
WHERE EVH.PROGRAM_ID IS NOT NULL
AND EVH.ACTIVE_FLAG = 'Y'
AND USRSTSTL.USER_STATUS_ID = EVH.USER_STATUS_ID
AND USRSTSTL.LANGUAGE = USERENV('LANG')
AND JLOCB.LOCATION_HIERARCHY_ID (+) = EVH.COUNTRY_CODE
AND JLOCAREATL.LOCATION_AREA_ID (+)= JLOCB.COUNTRY_ID
AND JLOCAREATL.LANGUAGE (+) = USERENV('LANG')
AND JTFRESVL.RESOURCE_ID = EVH.OWNER_USER_ID
AND PARENT.CAMPAIGN_ID (+) = EVH.PROGRAM_ID UNION ALL SELECT EVEO.EVENT_OFFER_ID || EVENT_OBJECT_TYPE
, EVEO.PARENT_ID || PARENT.ROLLUP_TYPE
, EVEO.EVENT_OFFER_ID
, EVEO.EVENT_OFFER_NAME
, PARENT_ID PROGRAM_ID
, EVENT_OBJECT_TYPE
, NULL
, EVEO.SOURCE_CODE
, EVEO.SYSTEM_STATUS_CODE STATUS_CODE
, EVEO.LAST_STATUS_DATE STATUS_DATE
, EVEO.USER_STATUS_ID
, USRSTSTL.NAME USER_STATUS
, EVEO.BUSINESS_UNIT_ID
, EVEO.PRIORITY_TYPE_CODE PRIORITY
, EVEO.EVENT_TYPE_CODE OBJECT_SUB_TYPE
, EVEO.START_PERIOD_NAME
, EVEO.EVENT_START_DATE START_DATE
, EVEO.END_PERIOD_NAME
, EVEO.EVENT_END_DATE END_DATE
, TO_NUMBER(EVEO.COUNTRY_CODE) COUNTRY_ID
, JLOCAREATL.LOCATION_AREA_NAME COUNTRY_NAME
, EVEO.OWNER_USER_ID OWNER_ID
, JTFRESVL.RESOURCE_NAME OWNER_NAME
, JTFRESVL.SOURCE_EMAIL OWNER_MAIL_ID
, EVEO.DESCRIPTION
, EVEO.CURRENCY_CODE_FC FUNCTIONAL_CURRENCY_CODE
, EVEO.FUND_AMOUNT_FC BUDGET_AMOUNT_FC
, EVEO.CURRENCY_CODE_TC TRANSACTION_CURRENCY_CODE
, EVEO.FUND_AMOUNT_TC BUDGET_AMOUNT_TC
, EVEO.OBJECT_VERSION_NUMBER
, EVEO.SETUP_TYPE_ID CUSTOM_SETUP_ID
FROM AMS_EVENT_OFFERS_VL EVEO
, JTF_LOC_HIERARCHIES_B JLOCB
, JTF_LOC_AREAS_TL JLOCAREATL
, JTF_RS_RESOURCE_EXTNS_VL JTFRESVL
, AMS_USER_STATUSES_TL USRSTSTL
, AMS_CAMPAIGNS_ALL_B PARENT
WHERE EVEO.PARENT_TYPE = 'RCAM'
AND EVEO.EVENT_OBJECT_TYPE = 'EONE'
AND EVEO.ACTIVE_FLAG = 'Y'
AND USRSTSTL.USER_STATUS_ID = EVEO.USER_STATUS_ID
AND USRSTSTL.LANGUAGE = USERENV('LANG')
AND JLOCB.LOCATION_HIERARCHY_ID (+) = EVEO.COUNTRY_CODE
AND JLOCAREATL.LOCATION_AREA_ID (+)= JLOCB.COUNTRY_ID
AND JLOCAREATL.LANGUAGE (+) = USERENV('LANG')
AND JTFRESVL.RESOURCE_ID = EVEO.OWNER_USER_ID
AND PARENT.CAMPAIGN_ID (+) = EVEO.PARENT_ID

Columns