View: BIC_CAMPAIGN_LIST_V

View Text

SELECT CAMP.CAMPAIGN_ID CAMPAIGN_ID
, CAMP.CAMPAIGN_TYPE CAMPAIGN_TYPE
, CAMP.STATUS_CODE STATUS_CODE
, CAMP.STATUS_DATE STATUS_DATE
, CAMP.ACTIVE_FLAG ACTIVE_FLAG
, CAMP.PRIORITY PRIORITY
, CAMP.CHANNEL_ID CHANNEL_ID
, CAMP.ACTUAL_EXEC_START_DATE START_DATE
, CAMP.ACTUAL_EXEC_END_DATE END_DATE
, CAMP.FORECASTED_PLAN_START_DATE FORECASTED_PLAN_START_DATE
, CAMP.FORECASTED_PLAN_END_DATE FORECASTED_PLAN_END_DATE
, CAMP.FORECASTED_EXEC_START_DATE FORECASTED_EXEC_START_DATE
, CAMP.FORECASTED_EXEC_END_DATE FORECASTED_EXEC_END_DATE
, CAMP.ACTUAL_PLAN_START_DATE ACTUAL_PLAN_START_DATE
, CAMP.ACTUAL_PLAN_END_DATE ACTUAL_PLAN_END_DATE
, CAMP.ACTUAL_EXEC_START_DATE ACTUAL_EXEC_START_DATE
, CAMP.ACTUAL_EXEC_END_DATE ACTUAL_EXEC_END_DATE
, ACH.CHANNEL_NAME CHANNEL_NAME
, AMD.MEDIA_NAME MEDIA_NAME
, JIA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, PSEG.PARTY_ID PARTY_ID
, JIA.CUST_ORG_ID CUST_ORG_ID
, ORG.ORG_NAME
, CTL.CAMPAIGN_NAME
, JIA.OUTCOME_ID
, JIA.RESULT_ID
, JIA.MEDIA_ID
FROM JTF_IH_ACTIVITIES JIA
, AMS_SOURCE_CODES ASCD
, AMS_CAMPAIGNS_ALL_B CAMP
, AMS_CHANNELS_TL ACH
, AMS_MEDIA_TL AMD
, BIC_DIMV_OPERATING_UNITS ORG
, AMS_CAMPAIGNS_ALL_TL CTL
, BIC_CAMPAIGN_MARKET_SEGMENTS_V SEG
, AMS_PARTY_MARKET_SEGMENTS PSEG
WHERE JIA.SOURCE_CODE_ID (+) = ASCD.SOURCE_CODE_ID
AND ASCD.ARC_SOURCE_CODE_FOR = 'CAMP'
AND ASCD.SOURCE_CODE_FOR_ID = CAMP.CAMPAIGN_ID
AND CAMP.CHANNEL_ID = ACH.CHANNEL_ID (+)
AND CAMP.MEDIA_ID = AMD.MEDIA_ID (+)
AND CAMP.ORG_ID = ORG.ORG_ID (+)
AND CAMP.CAMPAIGN_ID = CTL.CAMPAIGN_ID
AND SEG.MARKET_SEGMENT_ID = PSEG.MARKET_SEGMENT_ID
AND SEG.CAMPAIGN_ID = CAMP.CAMPAIGN_ID
AND ACH.LANGUAGE(+) = USERENV('LANG')
AND AMD.LANGUAGE(+) = USERENV('LANG')
AND CTL.LANGUAGE(+) = USERENV('LANG') UNION SELECT CAMP.CAMPAIGN_ID CAMPAIGN_ID
, CAMP.CAMPAIGN_TYPE TYPE
, CAMP.STATUS_CODE STATUS
, CAMP.STATUS_DATE STATUS_DATE
, CAMP.PRIORITY PRIORITY
, CAMP.ACTIVE_FLAG ACTIVE_FLAG
, CAMP.CHANNEL_ID CHANNEL_ID
, CAMP.FORECASTED_PLAN_START_DATE FORECASTED_PLAN_START_DATE
, CAMP.FORECASTED_PLAN_END_DATE FORECASTED_PLAN_END_DATE
, CAMP.FORECASTED_EXEC_START_DATE FORECASTED_EXEC_START_DATE
, CAMP.FORECASTED_EXEC_END_DATE FORECASTED_EXEC_END_DATE
, CAMP.ACTUAL_PLAN_START_DATE ACTUAL_PLAN_START_DATE
, CAMP.ACTUAL_PLAN_END_DATE ACTUAL_PLAN_END_DATE
, CAMP.ACTUAL_EXEC_START_DATE ACTUAL_EXEC_START_DATE
, CAMP.ACTUAL_EXEC_END_DATE ACTUAL_EXEC_END_DATE
, CAMP.ACTUAL_EXEC_START_DATE START_DATE
, CAMP.ACTUAL_EXEC_END_DATE END_DATE
, ACH.CHANNEL_NAME CHANNEL_NAME
, AMD.MEDIA_NAME MEDIA_NAME
, JIA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, PSEG.PARTY_ID PARTY_ID
, JIA.CUST_ORG_ID CUST_ORG_ID
, ORG.ORG_NAME
, CTL.CAMPAIGN_NAME
, JIA.OUTCOME_ID
, JIA.RESULT_ID
, JIA.MEDIA_ID
FROM JTF_IH_ACTIVITIES JIA
, AMS_SOURCE_CODES ASCD
, AMS_CAMPAIGNS_ALL_B CAMP
, AMS_CAMPAIGN_SCHEDULES CSCH
, BIC_DIMV_OPERATING_UNITS ORG
, AMS_CHANNELS_TL ACH
, AMS_MEDIA_TL AMD
, AMS_CAMPAIGNS_ALL_TL CTL
, BIC_CAMPAIGN_MARKET_SEGMENTS_V SEG
, AMS_PARTY_MARKET_SEGMENTS PSEG
WHERE JIA.SOURCE_CODE_ID = ASCD.SOURCE_CODE_ID
AND ASCD.ARC_SOURCE_CODE_FOR = 'CSCH'
AND ASCD.SOURCE_CODE_FOR_ID = CSCH.CAMPAIGN_SCHEDULE_ID
AND CSCH.CAMPAIGN_ID = CAMP.CAMPAIGN_ID
AND SEG.MARKET_SEGMENT_ID = PSEG.MARKET_SEGMENT_ID
AND SEG.CAMPAIGN_ID = CAMP.CAMPAIGN_ID
AND CAMP.CHANNEL_ID = ACH.CHANNEL_ID (+)
AND CAMP.MEDIA_ID = AMD.MEDIA_ID (+)
AND CAMP.ORG_ID = ORG.ORG_ID (+)
AND CAMP.CAMPAIGN_ID = CTL.CAMPAIGN_ID
AND ACH.LANGUAGE(+) = USERENV('LANG')
AND AMD.LANGUAGE(+) = USERENV('LANG')
AND CTL.LANGUAGE(+) = USERENV('LANG')

Columns