VIEW: APPS.BIM_FCTV_TARGETS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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