[Home] [Help]
View: BIM_FCTV_RESPONSES
Product: | BIM - Marketing Intelligence |
Description: | Used by the concurrent programs to load the summary tables. |
Implementation/DBA Data: |
APPS.BIM_FCTV_RESPONSES
|
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') ))
, JII.PARTY_ID PARTY_ID
FROM JTF_IH_ACTIVITIES JIA
, JTF_IH_RESULTS_B JIR
, 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.RESULT_ID = JIR.RESULT_ID
AND JIR.POSITIVE_RESPONSE_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
Name |
CAMPAIGN_ID |
MEDIA_ID |
CHANNEL_ID |
EVENT_OFFER_ID |
EVENT_HEADER_ID |
OFFER_ID |
START_DATE_TIME |
MARKET_SEGMENT_ID |
TARGET_SEGMENT_ID |
SOURCE_LIST_ID |
GEOGRAPHY_CODE |
PARTY_ID |