VIEW: APPS.BIM_FCTV_LEADLINES

Source

View Text - Preformatted

SELECT ASL.SALES_LEAD_ID , ASLL.SALES_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(ASL.CUSTOMER_ID, BSCD.SOURCE_CODE) , NVL(ASL.CHANNEL_CODE, '-999') , nvl(ASLL.INTEREST_TYPE_ID, -999) , nvl(ASLL.PRIMARY_INTEREST_CODE_ID, -999) , nvl(ASLL.SECONDARY_INTEREST_CODE_ID, -999) , nvl(ASLL.INVENTORY_ITEM_ID, -999) , ASLL.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') )) , ASLL.BUDGET_AMOUNT , ASLL.QUANTITY FROM AS_SALES_LEADS ASL , AS_SALES_LEAD_LINES ASLL , BIM_SOURCE_CODE_DETAILS BSCD , AMS_PARTY_MARKET_SEGMENTS APMS , HZ_LOCATIONS HZL WHERE ASL.SALES_LEAD_ID = ASLL.SALES_LEAD_ID AND ASL.SOURCE_PROMOTION_ID = BSCD.SOURCE_CODE_ID (+) AND ASL.CUSTOMER_ID = APMS.PARTY_ID (+) AND ASL.CREATION_DATE BETWEEN APMS.START_DATE_ACTIVE(+) AND NVL(APMS.END_DATE_ACTIVE(+), SYSDATE) AND NVL(APMS.MARKET_SEGMENT_FLAG,'Y') = 'Y' AND ASL.ADDRESS_ID = HZL.LOCATION_ID (+) WITH READ ONLY

View Text - HTML Formatted

SELECT ASL.SALES_LEAD_ID
, ASLL.SALES_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(ASL.CUSTOMER_ID
, BSCD.SOURCE_CODE)
, NVL(ASL.CHANNEL_CODE
, '-999')
, NVL(ASLL.INTEREST_TYPE_ID
, -999)
, NVL(ASLL.PRIMARY_INTEREST_CODE_ID
, -999)
, NVL(ASLL.SECONDARY_INTEREST_CODE_ID
, -999)
, NVL(ASLL.INVENTORY_ITEM_ID
, -999)
, ASLL.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') ))
, ASLL.BUDGET_AMOUNT
, ASLL.QUANTITY
FROM AS_SALES_LEADS ASL
, AS_SALES_LEAD_LINES ASLL
, BIM_SOURCE_CODE_DETAILS BSCD
, AMS_PARTY_MARKET_SEGMENTS APMS
, HZ_LOCATIONS HZL
WHERE ASL.SALES_LEAD_ID = ASLL.SALES_LEAD_ID
AND ASL.SOURCE_PROMOTION_ID = BSCD.SOURCE_CODE_ID (+)
AND ASL.CUSTOMER_ID = APMS.PARTY_ID (+)
AND ASL.CREATION_DATE BETWEEN APMS.START_DATE_ACTIVE(+)
AND NVL(APMS.END_DATE_ACTIVE(+)
, SYSDATE)
AND NVL(APMS.MARKET_SEGMENT_FLAG
, 'Y') = 'Y'
AND ASL.ADDRESS_ID = HZL.LOCATION_ID (+) WITH READ ONLY