VIEW: APPS.BIL_TX_OPP_SOURCE_V

Source

View Text - Preformatted

SELECT amscv.source_code_id, amscv.source_code, amscv.name, flv.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 campt.language = USERENV('LANG') AND TRUNC(NVL(CAMPB.ACTUAL_EXEC_START_DATE, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(CAMPB.ACTUAL_EXEC_END_DATE, SYSDATE)) >= TRUNC(SYSDATE) 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 eveht.language = USERENV('LANG') AND TRUNC(NVL(EVEHB.ACTIVE_FROM_DATE, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(EVEHB.ACTIVE_TO_DATE, SYSDATE)) >= TRUNC(SYSDATE) 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 eveot.language = USERENV('LANG') AND TRUNC(NVL(EVEOB.EVENT_START_DATE, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(EVEOB.EVENT_END_DATE, SYSDATE)) >= TRUNC(SYSDATE) 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 CHLST.language = USERENV('LANG') AND TRUNC(NVL(CHLSB.START_DATE_TIME, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(CHLSB.END_DATE_TIME, SYSDATE)) >= TRUNC(SYSDATE) UNION ALL select SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, QPLV.DESCRIPTION from AMS_SOURCE_CODES SOC, AMS_OFFERS OFFR, QP_LIST_HEADERS_VL QPLV where SOC.ARC_SOURCE_CODE_FOR = 'OFFR' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = OFFR.QP_LIST_HEADER_ID and OFFR.QP_LIST_HEADER_ID = QPLV.LIST_HEADER_ID AND TRUNC(NVL(QPLV.START_DATE_ACTIVE, SYSDATE)) <= TRUNC(SYSDATE) AND TRUNC(NVL(QPLV.END_DATE_ACTIVE, SYSDATE)) >= TRUNC(SYSDATE)) 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.lookup_code = amscv.source_type

View Text - HTML Formatted

SELECT AMSCV.SOURCE_CODE_ID
, AMSCV.SOURCE_CODE
, AMSCV.NAME
, FLV.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 CAMPT.LANGUAGE = USERENV('LANG')
AND TRUNC(NVL(CAMPB.ACTUAL_EXEC_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(CAMPB.ACTUAL_EXEC_END_DATE
, SYSDATE)) >= TRUNC(SYSDATE) 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 EVEHT.LANGUAGE = USERENV('LANG')
AND TRUNC(NVL(EVEHB.ACTIVE_FROM_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(EVEHB.ACTIVE_TO_DATE
, SYSDATE)) >= TRUNC(SYSDATE) 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 EVEOT.LANGUAGE = USERENV('LANG')
AND TRUNC(NVL(EVEOB.EVENT_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(EVEOB.EVENT_END_DATE
, SYSDATE)) >= TRUNC(SYSDATE) 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 CHLST.LANGUAGE = USERENV('LANG')
AND TRUNC(NVL(CHLSB.START_DATE_TIME
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(CHLSB.END_DATE_TIME
, SYSDATE)) >= TRUNC(SYSDATE) UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, QPLV.DESCRIPTION
FROM AMS_SOURCE_CODES SOC
, AMS_OFFERS OFFR
, QP_LIST_HEADERS_VL QPLV
WHERE SOC.ARC_SOURCE_CODE_FOR = 'OFFR'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = OFFR.QP_LIST_HEADER_ID
AND OFFR.QP_LIST_HEADER_ID = QPLV.LIST_HEADER_ID
AND TRUNC(NVL(QPLV.START_DATE_ACTIVE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(QPLV.END_DATE_ACTIVE
, SYSDATE)) >= TRUNC(SYSDATE)) 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.LOOKUP_CODE = AMSCV.SOURCE_TYPE