VIEW: APPS.AMS_P_OSO_SOURCE_CODES_V

Source

View Text - Preformatted

SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, CAMP.CAMPAIGN_NAME NAME, CAMP.STATUS_CODE STATUS, CAMP.ACTUAL_EXEC_START_DATE START_DATE, CAMP.ACTUAL_EXEC_END_DATE END_DATE, CAMP.INBOUND_URL, CAMP.INBOUND_EMAIL_ID, CAMP.INBOUND_PHONE_NO, SOC.RELATED_SOURCE_CODE, SOC.RELATED_SOURCE_OBJECT, SOC.RELATED_SOURCE_ID, LKP.MEANING FROM AMS_SOURCE_CODES SOC, AMS_CAMPAIGNS_VL CAMP, AMS_LOOKUPS lkp WHERE SOC.ARC_SOURCE_CODE_FOR = 'CAMP' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = CAMP.CAMPAIGN_ID and CAMP.STATUS_CODE IN ('ACTIVE', 'ON_HOLD','COMPLETED') and LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, EVEH.EVENT_HEADER_NAME, EVEH.SYSTEM_STATUS_CODE, EVEH.ACTIVE_FROM_DATE, EVEH.ACTIVE_TO_DATE, EVEH.URL, EVEH.EMAIL, EVEH.PHONE, SOC.RELATED_SOURCE_CODE, SOC.RELATED_SOURCE_OBJECT, SOC.RELATED_SOURCE_ID, LKP.MEANING FROM AMS_SOURCE_CODES SOC, AMS_EVENT_HEADERS_VL EVEH, AMS_LOOKUPS lkp WHERE SOC.ARC_SOURCE_CODE_FOR = 'EVEH' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = EVEH.EVENT_HEADER_ID and EVEH.SYSTEM_STATUS_CODE IN ('ACTIVE', 'ON_HOLD','COMPLETED') and LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, EVEO.EVENT_OFFER_NAME, EVEO.SYSTEM_STATUS_CODE, EVEO.EVENT_START_DATE, EVEO.EVENT_END_DATE, EVEO.URL, EVEO.EMAIL, EVEO.PHONE, SOC.RELATED_SOURCE_CODE, SOC.RELATED_SOURCE_OBJECT, SOC.RELATED_SOURCE_ID, LKP.MEANING FROM AMS_SOURCE_CODES SOC, AMS_EVENT_OFFERS_VL EVEO, AMS_LOOKUPS lkp WHERE SOC.ARC_SOURCE_CODE_FOR = 'EVEO' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = EVEO.EVENT_OFFER_ID and EVEO.SYSTEM_STATUS_CODE IN ('ACTIVE', 'ON_HOLD','COMPLETED') and LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, EVEO.EVENT_OFFER_NAME, EVEO.SYSTEM_STATUS_CODE, EVEO.EVENT_START_DATE, EVEO.EVENT_END_DATE, EVEO.URL, EVEO.EMAIL, EVEO.PHONE, SOC.RELATED_SOURCE_CODE, SOC.RELATED_SOURCE_OBJECT, SOC.RELATED_SOURCE_ID, LKP.MEANING FROM AMS_SOURCE_CODES SOC, AMS_EVENT_OFFERS_VL EVEO, AMS_LOOKUPS lkp WHERE SOC.ARC_SOURCE_CODE_FOR = 'EONE' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = EVEO.EVENT_OFFER_ID and EVEO.SYSTEM_STATUS_CODE IN ('ACTIVE', 'ON_HOLD','COMPLETED') and LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR and EVEO.PARENT_TYPE IS NULL UNION ALL SELECT SOC.SOURCE_CODE_ID, SOC.SOURCE_CODE, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE, SOC.SOURCE_CODE_FOR_ID OBJECT_ID, CHLS.SCHEDULE_NAME, CHLS.STATUS_CODE, CHLS.START_DATE_TIME, CHLS.END_DATE_TIME, null, null, null, SOC.RELATED_SOURCE_CODE, SOC.RELATED_SOURCE_OBJECT, SOC.RELATED_SOURCE_ID, LKP.MEANING FROM AMS_SOURCE_CODES SOC, AMS_CAMPAIGN_SCHEDULES_VL CHLS, AMS_LOOKUPS lkp WHERE SOC.ARC_SOURCE_CODE_FOR = 'CSCH' and SOC.ACTIVE_FLAG = 'Y' and SOC.SOURCE_CODE_FOR_ID = CHLS.SCHEDULE_ID and CHLS.STATUS_CODE IN ('ACTIVE', 'ON_HOLD','COMPLETED') and LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR 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, OFFR.STATUS_CODE, QPLV.START_DATE_ACTIVE, QPLV.END_DATE_ACTIVE, NULL, NULL, NULL, SOC.RELATED_SOURCE_CODE, SOC.RELATED_SOURCE_OBJECT, SOC.RELATED_SOURCE_ID, LKP.MEANING FROM AMS_SOURCE_CODES SOC, AMS_OFFERS OFFR, QP_LIST_HEADERS_VL QPLV, AMS_LOOKUPS lkp 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 OFFR.STATUS_CODE IN ('ACTIVE', 'ONHOLD','COMPLETED') and LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR

View Text - HTML Formatted

SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, CAMP.CAMPAIGN_NAME NAME
, CAMP.STATUS_CODE STATUS
, CAMP.ACTUAL_EXEC_START_DATE START_DATE
, CAMP.ACTUAL_EXEC_END_DATE END_DATE
, CAMP.INBOUND_URL
, CAMP.INBOUND_EMAIL_ID
, CAMP.INBOUND_PHONE_NO
, SOC.RELATED_SOURCE_CODE
, SOC.RELATED_SOURCE_OBJECT
, SOC.RELATED_SOURCE_ID
, LKP.MEANING
FROM AMS_SOURCE_CODES SOC
, AMS_CAMPAIGNS_VL CAMP
, AMS_LOOKUPS LKP
WHERE SOC.ARC_SOURCE_CODE_FOR = 'CAMP'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = CAMP.CAMPAIGN_ID
AND CAMP.STATUS_CODE IN ('ACTIVE'
, 'ON_HOLD'
, 'COMPLETED')
AND LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, EVEH.EVENT_HEADER_NAME
, EVEH.SYSTEM_STATUS_CODE
, EVEH.ACTIVE_FROM_DATE
, EVEH.ACTIVE_TO_DATE
, EVEH.URL
, EVEH.EMAIL
, EVEH.PHONE
, SOC.RELATED_SOURCE_CODE
, SOC.RELATED_SOURCE_OBJECT
, SOC.RELATED_SOURCE_ID
, LKP.MEANING
FROM AMS_SOURCE_CODES SOC
, AMS_EVENT_HEADERS_VL EVEH
, AMS_LOOKUPS LKP
WHERE SOC.ARC_SOURCE_CODE_FOR = 'EVEH'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = EVEH.EVENT_HEADER_ID
AND EVEH.SYSTEM_STATUS_CODE IN ('ACTIVE'
, 'ON_HOLD'
, 'COMPLETED')
AND LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, EVEO.EVENT_OFFER_NAME
, EVEO.SYSTEM_STATUS_CODE
, EVEO.EVENT_START_DATE
, EVEO.EVENT_END_DATE
, EVEO.URL
, EVEO.EMAIL
, EVEO.PHONE
, SOC.RELATED_SOURCE_CODE
, SOC.RELATED_SOURCE_OBJECT
, SOC.RELATED_SOURCE_ID
, LKP.MEANING
FROM AMS_SOURCE_CODES SOC
, AMS_EVENT_OFFERS_VL EVEO
, AMS_LOOKUPS LKP
WHERE SOC.ARC_SOURCE_CODE_FOR = 'EVEO'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = EVEO.EVENT_OFFER_ID
AND EVEO.SYSTEM_STATUS_CODE IN ('ACTIVE'
, 'ON_HOLD'
, 'COMPLETED')
AND LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, EVEO.EVENT_OFFER_NAME
, EVEO.SYSTEM_STATUS_CODE
, EVEO.EVENT_START_DATE
, EVEO.EVENT_END_DATE
, EVEO.URL
, EVEO.EMAIL
, EVEO.PHONE
, SOC.RELATED_SOURCE_CODE
, SOC.RELATED_SOURCE_OBJECT
, SOC.RELATED_SOURCE_ID
, LKP.MEANING
FROM AMS_SOURCE_CODES SOC
, AMS_EVENT_OFFERS_VL EVEO
, AMS_LOOKUPS LKP
WHERE SOC.ARC_SOURCE_CODE_FOR = 'EONE'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = EVEO.EVENT_OFFER_ID
AND EVEO.SYSTEM_STATUS_CODE IN ('ACTIVE'
, 'ON_HOLD'
, 'COMPLETED')
AND LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR
AND EVEO.PARENT_TYPE IS NULL UNION ALL SELECT SOC.SOURCE_CODE_ID
, SOC.SOURCE_CODE
, SOC.ARC_SOURCE_CODE_FOR SOURCE_TYPE
, SOC.SOURCE_CODE_FOR_ID OBJECT_ID
, CHLS.SCHEDULE_NAME
, CHLS.STATUS_CODE
, CHLS.START_DATE_TIME
, CHLS.END_DATE_TIME
, NULL
, NULL
, NULL
, SOC.RELATED_SOURCE_CODE
, SOC.RELATED_SOURCE_OBJECT
, SOC.RELATED_SOURCE_ID
, LKP.MEANING
FROM AMS_SOURCE_CODES SOC
, AMS_CAMPAIGN_SCHEDULES_VL CHLS
, AMS_LOOKUPS LKP
WHERE SOC.ARC_SOURCE_CODE_FOR = 'CSCH'
AND SOC.ACTIVE_FLAG = 'Y'
AND SOC.SOURCE_CODE_FOR_ID = CHLS.SCHEDULE_ID
AND CHLS.STATUS_CODE IN ('ACTIVE'
, 'ON_HOLD'
, 'COMPLETED')
AND LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR 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
, OFFR.STATUS_CODE
, QPLV.START_DATE_ACTIVE
, QPLV.END_DATE_ACTIVE
, NULL
, NULL
, NULL
, SOC.RELATED_SOURCE_CODE
, SOC.RELATED_SOURCE_OBJECT
, SOC.RELATED_SOURCE_ID
, LKP.MEANING
FROM AMS_SOURCE_CODES SOC
, AMS_OFFERS OFFR
, QP_LIST_HEADERS_VL QPLV
, AMS_LOOKUPS LKP
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 OFFR.STATUS_CODE IN ('ACTIVE'
, 'ONHOLD'
, 'COMPLETED')
AND LKP.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER'
AND LKP.LOOKUP_CODE = SOC.ARC_SOURCE_CODE_FOR