VIEW: APPS.ASO_SOURCE_NAME_V

Source

View Text - Preformatted

SELECT amscv.source_code_id as source_code_id, amscv.source_code as source_code, amscv.name as name, amscv.source_type as source_type, amscv.object_id, flv.meaning as source_type_meaning FROM ( SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, CAMPT.CAMPAIGN_NAME NAME FROM AMS_SOURCE_CODES SOC, AMS_CAMPAIGNS_ALL_TL campt, AMS_CAMPAIGNS_ALL_B campb WHERE SOC.ARC_SOURCE_CODE_FOR = 'CAMP' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = CAMPB.CAMPAIGN_ID and CAMPB.CAMPAIGN_ID = CAMPT.CAMPAIGN_ID and CAMPB.status_code in ('ACTIVE','COMPLETED') AND campt.language = USERENV('LANG') UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, EVEHT.EVENT_HEADER_NAME FROM AMS_SOURCE_CODES SOC, AMS_EVENT_HEADERS_all_b EVEHB, AMS_EVENT_HEADERS_ALL_TL EVEHT WHERE SOC.ARC_SOURCE_CODE_FOR = 'EVEH' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = EVEHB.EVENT_HEADER_ID AND EVEHB.EVENT_HEADER_ID = EVEHT.EVENT_HEADER_ID and EVEHB.system_status_code in ('ACTIVE','COMPLETED') AND eveht.language = USERENV('LANG') UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, EVEOT.EVENT_OFFER_NAME FROM AMS_SOURCE_CODES SOC, AMS_EVENT_OFFERS_ALL_B EVEOB, AMS_EVENT_OFFERS_ALL_TL EVEOT WHERE SOC.ARC_SOURCE_CODE_FOR IN ('EVEO','EONE') and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = EVEOB.EVENT_OFFER_ID AND EVEOB.EVENT_OFFER_ID = EVEOT.EVENT_OFFER_ID and EVEOB.system_status_code in ('ACTIVE','COMPLETED') AND eveot.language = USERENV('LANG') UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, CHLST.SCHEDULE_NAME FROM AMS_SOURCE_CODES SOC, ams_campaign_schedules_tl CHLST, ams_campaign_schedules_b CHLSB WHERE SOC.ARC_SOURCE_CODE_FOR = 'CSCH' AND SOC.ACTIVE_FLAG = 'Y' AND SOC.SOURCE_CODE_FOR_ID = CHLSB.SCHEDULE_ID AND CHLSB.SCHEDULE_ID = CHLST.SCHEDULE_ID AND CHLSB.status_code in ('ACTIVE','COMPLETED') AND CHLST.language = USERENV('LANG') ) amscv, fnd_lookup_values flv where flv.lookup_type = 'AMS_SYS_ARC_QUALIFIER' AND flv.language = USERENV ('LANG') AND flv.view_application_id = 530 AND flv.security_group_id = 0 AND flv.lookup_code = amscv.source_type

View Text - HTML Formatted

SELECT AMSCV.SOURCE_CODE_ID AS SOURCE_CODE_ID
, AMSCV.SOURCE_CODE AS SOURCE_CODE
, AMSCV.NAME AS NAME
, AMSCV.SOURCE_TYPE AS SOURCE_TYPE
, AMSCV.OBJECT_ID
, FLV.MEANING AS SOURCE_TYPE_MEANING
FROM ( SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, CAMPT.CAMPAIGN_NAME NAME
FROM AMS_SOURCE_CODES SOC
, AMS_CAMPAIGNS_ALL_TL CAMPT
, AMS_CAMPAIGNS_ALL_B CAMPB
WHERE SOC.ARC_SOURCE_CODE_FOR = 'CAMP'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = CAMPB.CAMPAIGN_ID
AND CAMPB.CAMPAIGN_ID = CAMPT.CAMPAIGN_ID
AND CAMPB.STATUS_CODE IN ('ACTIVE'
, 'COMPLETED')
AND CAMPT.LANGUAGE = USERENV('LANG') UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, EVEHT.EVENT_HEADER_NAME
FROM AMS_SOURCE_CODES SOC
, AMS_EVENT_HEADERS_ALL_B EVEHB
, AMS_EVENT_HEADERS_ALL_TL EVEHT
WHERE SOC.ARC_SOURCE_CODE_FOR = 'EVEH'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = EVEHB.EVENT_HEADER_ID
AND EVEHB.EVENT_HEADER_ID = EVEHT.EVENT_HEADER_ID
AND EVEHB.SYSTEM_STATUS_CODE IN ('ACTIVE'
, 'COMPLETED')
AND EVEHT.LANGUAGE = USERENV('LANG') UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, EVEOT.EVENT_OFFER_NAME
FROM AMS_SOURCE_CODES SOC
, AMS_EVENT_OFFERS_ALL_B EVEOB
, AMS_EVENT_OFFERS_ALL_TL EVEOT
WHERE SOC.ARC_SOURCE_CODE_FOR IN ('EVEO'
, 'EONE')
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = EVEOB.EVENT_OFFER_ID
AND EVEOB.EVENT_OFFER_ID = EVEOT.EVENT_OFFER_ID
AND EVEOB.SYSTEM_STATUS_CODE IN ('ACTIVE'
, 'COMPLETED')
AND EVEOT.LANGUAGE = USERENV('LANG') UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, CHLST.SCHEDULE_NAME
FROM AMS_SOURCE_CODES SOC
, AMS_CAMPAIGN_SCHEDULES_TL CHLST
, AMS_CAMPAIGN_SCHEDULES_B CHLSB
WHERE SOC.ARC_SOURCE_CODE_FOR = 'CSCH'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = CHLSB.SCHEDULE_ID
AND CHLSB.SCHEDULE_ID = CHLST.SCHEDULE_ID
AND CHLSB.STATUS_CODE IN ('ACTIVE'
, 'COMPLETED')
AND CHLST.LANGUAGE = USERENV('LANG') ) AMSCV
, FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND FLV.LANGUAGE = USERENV ('LANG')
AND FLV.VIEW_APPLICATION_ID = 530
AND FLV.SECURITY_GROUP_ID = 0
AND FLV.LOOKUP_CODE = AMSCV.SOURCE_TYPE