View: BIM_DIMV_SOURCE_CODES_V

Product:	BIM - Marketing Intelligence(Obsolete)
Description:	Shows details for all marketing source codes.
Implementation/DBA Data:	APPS.BIM_DIMV_SOURCE_CODES_V

View Text

SELECT SRC.SOURCE_CODE_ID SOURCE_CODE_ID
, SRC.SOURCE_CODE SOURCE_CODE
, -999 CAMPAIGN_SCHEDULE_ID
, CMP.CAMPAIGN_ID CAMPAIGN_ID
, NVL(CMP.MEDIA_ID
, -999) MEDIA_ID
, DECODE(NVL(CMP.CHANNEL_ID
, -999)
, -999
, TO_CHAR(-999)
, CMP.ARC_CHANNEL_FROM || TO_CHAR(CMP.CHANNEL_ID) ) CHANNEL_ID
, -999 OFFER_ID
, DECODE(CMP.ARC_CHANNEL_FROM
, 'EVEH'
, 'EVEH' || TO_CHAR(CMP.CHANNEL_ID)
, TO_CHAR(-999 )) EVENT_ID
, DECODE(CMP.ARC_CHANNEL_FROM
, 'EVEO'
, 'EVEO' || TO_CHAR(CMP.CHANNEL_ID)
, TO_CHAR(-999)) EVENT_OFFER_ID
, SRC.CREATION_DATE CREATION_DATE
FROM AMS_SOURCE_CODES SRC
, AMS_CAMPAIGNS_ALL_B CMP
WHERE SRC.SOURCE_CODE_FOR_ID = CMP.CAMPAIGN_ID
AND SRC.ARC_SOURCE_CODE_FOR = 'CAMP' UNION SELECT SRC.SOURCE_CODE_ID SOURCE_CODE_ID
, SRC.SOURCE_CODE SOURCE_CODE
, SRC.SOURCE_CODE_FOR_ID CAMPAIGN_SCHEDULE_ID
, CMP.CAMPAIGN_ID CAMPAIGN_ID
, NVL(CMP.MEDIA_ID
, -999) MEDIA_ID
, DECODE(NVL(CMP.CHANNEL_ID
, -999)
, -999
, TO_CHAR(-999)
, CMP.ARC_CHANNEL_FROM || TO_CHAR(CMP.CHANNEL_ID) ) CHANNEL_ID
, -999 OFFER_ID
, DECODE(CMP.ARC_CHANNEL_FROM
, 'EVEH'
, 'EVEH' || TO_CHAR(CMP.CHANNEL_ID)
, TO_CHAR(-999 )) EVENT_ID
, DECODE(CMP.ARC_CHANNEL_FROM
, 'EVEO'
, 'EVEO' || TO_CHAR(CMP.CHANNEL_ID)
, TO_CHAR(-999) ) EVENT_OFFER_ID
, SRC.CREATION_DATE CREATION_DATE
FROM AMS_SOURCE_CODES SRC
, AMS_CAMPAIGNS_ALL_B CMP
, AMS_CAMPAIGN_SCHEDULES SCHED
WHERE SRC.SOURCE_CODE_FOR_ID = SCHED.CAMPAIGN_SCHEDULE_ID
AND SCHED.CAMPAIGN_ID = CMP.CAMPAIGN_ID
AND SRC.ARC_SOURCE_CODE_FOR = 'CSCH' UNION SELECT SRC.SOURCE_CODE_ID SOURCE_CODE_ID
, SRC.SOURCE_CODE SOURCE_CODE
, -999 CAMPAIGN_SCHEDULE_ID
, CMP.CAMPAIGN_ID CAMPAIGN_ID
, NVL(CMP.MEDIA_ID
, -999) MEDIA_ID
, DECODE(NVL(CMP.CHANNEL_ID
, -999)
, -999
, TO_CHAR(-999)
, CMP.ARC_CHANNEL_FROM || TO_CHAR(CMP.CHANNEL_ID) ) CHANNEL_ID
, OFFR.ACTIVITY_OFFER_ID OFFER_ID
, DECODE(CMP.ARC_CHANNEL_FROM
, 'EVEH'
, 'EVEH' || TO_CHAR(CMP.CHANNEL_ID)
, TO_CHAR(-999)) EVENT_ID
, DECODE(CMP.ARC_CHANNEL_FROM
, 'EVEO'
, 'EVEO' || TO_CHAR(CMP.CHANNEL_ID)
, TO_CHAR(-999 )) EVENT_OFFER_ID
, SRC.CREATION_DATE CREATION_DATE
FROM AMS_SOURCE_CODES SRC
, AMS_CAMPAIGNS_ALL_B CMP
, AMS_ACT_OFFERS OFFR
WHERE SRC.SOURCE_CODE_FOR_ID = OFFR.ACTIVITY_OFFER_ID
AND OFFR.ACT_OFFER_USED_BY_ID = CMP.CAMPAIGN_ID
AND SRC.ARC_SOURCE_CODE_FOR = 'OFFR' UNION SELECT SRC.SOURCE_CODE_ID SOURCE_CODE_ID
, SRC.SOURCE_CODE SOURCE_CODE
, -999 CAMPAIGN_SCHEDULE_ID
, -999 CAMPAIGN_ID
, -999 MEDIA_ID
, TO_CHAR(-999) CHANNEL_ID
, -999 OFFER_ID
, DECODE( ARC_SOURCE_CODE_FOR
, 'EVEH'
, 'EVEH' || TO_CHAR(SOURCE_CODE_FOR_ID)
, TO_CHAR(-999)) EVENT_ID
, TO_CHAR(-999) EVENT_OFFER_ID
, SRC.CREATION_DATE CREATION_DATE
FROM AMS_SOURCE_CODES SRC
WHERE SRC.ARC_SOURCE_CODE_FOR = 'EVEH' UNION SELECT SRC.SOURCE_CODE_ID SOURCE_CODE_ID
, SRC.SOURCE_CODE SOURCE_CODE
, -999 CAMPAIGN_SCHEDULE_ID
, -999 CAMPAIGN_ID
, -999 MEDIA_ID
, TO_CHAR(-999) CHANNEL_ID
, -999 OFFER_ID
, NVL('EVEH' || TO_CHAR(EVT.EVENT_HEADER_ID)
, TO_CHAR(-999)) EVENT_ID
, DECODE( ARC_SOURCE_CODE_FOR
, 'EVEO'
, 'EVEO' || TO_CHAR(SOURCE_CODE_FOR_ID)
, TO_CHAR(-999)) EVENT_OFFER_ID
, SRC.CREATION_DATE CREATION_DATE
FROM AMS_EVENT_OFFERS_ALL_B EVT
, AMS_SOURCE_CODES SRC
WHERE EVT.EVENT_OFFER_ID = SRC.SOURCE_CODE_FOR_ID
AND SRC.ARC_SOURCE_CODE_FOR = 'EVEO' WITH READ ONLY

Columns