[Home] [Help]
| Product: | BIM - Marketing Intelligence(Obsolete) |
|---|---|
| Description: | Used by the concurrent programs to load the summary tables. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ODH.HEADER_ID HEADER_ID
, ODH.ORDER_NUMBER ORDER_NUMBER
, ODH.PARTY_ID CUSTOMER_ID
, ODH.CUST_ACCT_ID ACCOUNT_ID
, NVL(ODH.SALES_CHANNEL_CODE
, '-999') SALES_CHANNEL_CODE
, ODH.ORDERED_DATE ORDERED_DATE
, ODH.ORG_ID ORG_ID
, ODH.CURRENCY_CODE TRANSACTION_CURRENCY
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(ODH.INVOICE_TO_ORG_ID
, ODH.ORG_ID ) BILL_TO_GEOGRAPHY_CODE
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(ODH.SHIP_TO_ORG_ID
, ODH.ORG_ID) SHIP_TO_GEOGRAPHY_CODE
, ODH.MARKETING_SOURCE_CODE_ID HDR_SOURCE_CODE_ID
, NVL(SRCD.CAMPAIGN_ID
, -999) CAMPAIGN_ID
, NVL(SRCD.MEDIA_ID
, -999) MEDIA_ID
, NVL(SRCD.CHANNEL_ID
, -999) CHANNEL_ID
, NVL(SRCD.EVENT_OFFER_ID
, -999) EVENT_OFFER_ID
, NVL(SRCD.EVENT_ID
, -999) EVENT_ID
, NVL(PMKT.MARKET_SEGMENT_ID
, -999) MARKET_SEGMENT_ID
, ODH.CREATION_DATE CREATION_DATE
, ODH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ODH.RETURN_REASON_CODE RETURN_REASON_CODE
, ODH.EXCHANGE_RATE EXCHANGE_RATE
, ODH.EXCHANGE_TYPE_CODE EXCHANGE_TYPE_CODE
FROM ASO_I_ORDER_HEADERS_V ODH
, BIM_SOURCE_CODE_DETAILS SRCD
, AMS_PARTY_MARKET_SEGMENTS PMKT
WHERE ODH.MARKETING_SOURCE_CODE_ID = SRCD.SOURCE_CODE_ID (+)
AND ODH.ORDER_CATEGORY_CODE <> 'RETURN'
AND ODH.BOOKED_FLAG = 'Y'
AND NVL(ODH.CANCELLED_FLAG
, 'N' ) = 'N'
AND ODH.PARTY_ID = PMKT.PARTY_ID (+)
AND ODH.ORDERED_DATE >= PMKT.START_DATE_ACTIVE (+)
AND ODH.ORDERED_DATE <= NVL( PMKT.END_DATE_ACTIVE
, ODH.ORDERED_DATE)
AND PMKT.MARKET_SEGMENT_FLAG (+) = 'Y'