VIEW: APPS.BIM_FCTV_OPPR_CYCLE

Source

View Text - Preformatted

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

View Text - HTML Formatted

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