VIEW: APPS.BIM_FCTV_OPPLINES

Source

View Text - Preformatted

SELECT ALA.LEAD_ID , ALLA.LEAD_LINE_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' ) , NVL(ALLA.INTEREST_TYPE_ID, -999) , NVL(ALLA.PRIMARY_INTEREST_CODE_ID, -999) , NVL(ALLA.SECONDARY_INTEREST_CODE_ID, -999) , NVL(ALLA.INVENTORY_ITEM_ID, -999) , ALLA.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') )) , ALLA.TOTAL_AMOUNT , ALLA.QUANTITY FROM AS_LEADS_ALL ALA , AS_LEAD_LINES_ALL ALLA , BIM_SOURCE_CODE_DETAILS BSCD , AMS_PARTY_MARKET_SEGMENTS APMS , HZ_LOCATIONS HZL WHERE ALA.LEAD_ID = ALLA.LEAD_ID AND 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

View Text - HTML Formatted

SELECT ALA.LEAD_ID
, ALLA.LEAD_LINE_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' )
, NVL(ALLA.INTEREST_TYPE_ID
, -999)
, NVL(ALLA.PRIMARY_INTEREST_CODE_ID
, -999)
, NVL(ALLA.SECONDARY_INTEREST_CODE_ID
, -999)
, NVL(ALLA.INVENTORY_ITEM_ID
, -999)
, ALLA.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') ))
, ALLA.TOTAL_AMOUNT
, ALLA.QUANTITY
FROM AS_LEADS_ALL ALA
, AS_LEAD_LINES_ALL ALLA
, BIM_SOURCE_CODE_DETAILS BSCD
, AMS_PARTY_MARKET_SEGMENTS APMS
, HZ_LOCATIONS HZL
WHERE ALA.LEAD_ID = ALLA.LEAD_ID
AND 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