[Home] [Help]
View: BIM_FCTV_OPPORTUNITIES
Product: | BIM - Marketing Intelligence(Obsolete) |
Description: | Used by the concurrent programs to load the summary tables. |
Implementation/DBA Data: |
APPS.BIM_FCTV_OPPORTUNITIES
|
View Text
SELECT ALA.LEAD_ID
, NVL(BSCD.CAMPAIGN_ID
, -999)
, NVL(BSCD.EVENT_ID
, -999)
, NVL(BSCD.EVENT_OFFER_ID
, -999)
, NVL(BSCD.CHANNEL_ID
, -999)
, NVL(BSCD.MEDIA_ID
, -999)
, NVL(APMS.MARKET_SEGMENT_ID
, -999)
, BIM_TARGET_SEGMENT_PKG.TARGET_SEGMENT_FK(ALA.CUSTOMER_ID
, BSCD.SOURCE_CODE)
, NVL( ALA.CHANNEL_CODE
, '-999')
, ALA.CREATION_DATE
, 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 AS_LEADS_ALL ALA
, BIM_SOURCE_CODE_DETAILS BSCD
, AMS_PARTY_MARKET_SEGMENTS APMS
, HZ_LOCATIONS HZL
WHERE ALA.CUSTOMER_ID = APMS.PARTY_ID (+)
AND ALA.CREATION_DATE BETWEEN APMS.START_DATE_ACTIVE(+)
AND NVL(APMS.END_DATE_ACTIVE(+)
, SYSDATE)
AND NVL(APMS.MARKET_SEGMENT_FLAG
, 'Y') = 'Y'
AND ALA.SOURCE_PROMOTION_ID = BSCD.SOURCE_CODE_ID (+)
AND ALA.ADDRESS_ID = HZL.LOCATION_ID (+) WITH READ ONLY
Columns
Name |
OPPORTUNITY_LEAD_ID |
CAMPAIGN_ID |
EVENT_HEADER_ID |
EVENT_OFFER_ID |
CHANNEL_ID |
MEDIA_ID |
MARKET_SEGMENT_ID |
TARGET_SEGMENT_ID |
SALES_CHANNEL_CODE |
CREATION_DATE |
GEOGRAPHY_CODE |