View: BIM_FCTV_TARGETS

Product:	BIM - Marketing Intelligence(Obsolete)
Description:	Used by the concurrent programs to load the summary tables.
Implementation/DBA Data:	APPS.BIM_FCTV_TARGETS

View Text

SELECT NVL(BSC.CAMPAIGN_ID
, -999) CAMPAIGN_ID
, NVL(BSC.MEDIA_ID
, -999) MEDIA_ID
, NVL(BSC.CHANNEL_ID
, -999) CHANNEL_ID
, NVL(BSC.EVENT_OFFER_ID
, -999) EVENT_OFFER_ID
, NVL(BSC.EVENT_ID
, -999) EVENT_HEADER_ID
, NVL(BSC.OFFER_ID
, -999) OFFER_ID
, JIA.START_DATE_TIME START_DATE_TIME
, NVL(APM.MARKET_SEGMENT_ID
, -999) MARKET_SEGMENT_ID
, BIM_TARGET_SEGMENT_PKG.TARGET_SEGMENT_FK(JII.PARTY_ID
, JIA.SOURCE_CODE) TARGET_SEGMENT_ID
, NVL(APS.IMPORT_LIST_HEADER_ID
, -999) SOURCE_LIST_ID
, DECODE(NVL(HZL.LOCATION_ID
, -999)
, -999
, '-999>>-999>>-999>>-999'
, UPPER(NVL(HZL.COUNTRY
, '-999') || '>>' || NVL(HZL.STATE
, '-999') || '>>' || NVL(HZL.CITY
, '-999') || '>>' || NVL(HZL.POSTAL_CODE
, '-999') ))
FROM JTF_IH_ACTIVITIES JIA
, JTF_IH_MEDIA_ITEMS JIM
, JTF_IH_OUTCOMES_B JIO
, BIM_SOURCE_CODE_DETAILS BSC
, JTF_IH_INTERACTIONS JII
, AMS_PARTY_MARKET_SEGMENTS APM
, AMS_PARTY_SOURCES APS
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HZL
WHERE JIA.MEDIA_ID = JIM.MEDIA_ID
AND JIM.DIRECTION = 'OUTBOUND'
AND JIA.OUTCOME_ID = JIO.OUTCOME_ID
AND JIO.POSITIVE_OUTCOME_FLAG = 'Y'
AND JIA.SOURCE_CODE_ID = BSC.SOURCE_CODE_ID(+)
AND JIA.INTERACTION_ID = JII.INTERACTION_ID
AND JII.PARTY_ID = APM.PARTY_ID(+)
AND JII.START_DATE_TIME BETWEEN APM.START_DATE_ACTIVE(+)
AND NVL(APM.END_DATE_ACTIVE(+)
, SYSDATE)
AND APM.MARKET_SEGMENT_FLAG(+) = 'Y'
AND JII.PARTY_ID = APS.PARTY_ID(+)
AND JII.PARTY_ID = HPS.PARTY_ID(+)
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND HPS.LOCATION_ID = HZL.LOCATION_ID (+) WITH READ ONLY

Columns