View: BIM_FCTV_OPPR_CYCLE

Product:	BIM - Marketing Intelligence(Obsolete)
Description:	Used by the concurrent programs to load the summary tables.
Implementation/DBA Data:	APPS.BIM_FCTV_OPPR_CYCLE

View Text

SELECT NVL(BSCD.CAMPAIGN_ID
, -999) CAMPAIGN_ID
, NVL(BSCD.MEDIA_ID
, -999) MEDIA_ID
, NVL(BSCD.CHANNEL_ID
, -999) CHANNEL_ID
, NVL(APMS.MARKET_SEGMENT_ID
, -999) MARKET_SEGMENT_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') ))
, NVL(ALA.CHANNEL_CODE
, '-999') SALES_CHANNEL_CODE
, ALA.LEAD_ID OPPORTUNITY_ID
, ALA.CREATION_DATE OPPR_CREATION_DATE
, NVL(AOH.HEADER_ID
, -999) ORDER_HEADER_ID
, AOH.ORDERED_DATE ORDERED_DATE
FROM AS_LEADS_ALL ALA
, BIM_SOURCE_CODE_DETAILS BSCD
, AMS_PARTY_MARKET_SEGMENTS APMS
, ASO_QUOTE_RELATED_OBJECTS ASO1
, ASO_QUOTE_RELATED_OBJECTS ASO2
, ASO_I_ORDER_HEADERS_V AOH
, 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 APMS.MARKET_SEGMENT_FLAG(+) = 'Y'
AND ALA.SOURCE_PROMOTION_ID = BSCD.SOURCE_CODE_ID(+)
AND ALA.LEAD_ID = ASO1.OBJECT_ID
AND ASO1.OBJECT_TYPE_CODE = 'OPPORTUNITY'
AND ASO1.RELATIONSHIP_TYPE_CODE = 'OPP_QUOTE'
AND ASO1.QUOTE_OBJECT_ID = ASO2.QUOTE_OBJECT_ID
AND ASO1.QUOTE_OBJECT_TYPE_CODE = ASO2.QUOTE_OBJECT_TYPE_CODE
AND ASO2.RELATIONSHIP_TYPE_CODE = 'QUOTE_ORDER'
AND ASO2.OBJECT_ID = AOH.HEADER_ID
AND ALA.ADDRESS_ID = HZL.LOCATION_ID (+) WITH READ ONLY

Columns