VIEW: APPS.AMS_PROGRAM_COMPONENTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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