[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( DECODE(ODL.INVOICE_TO_ORG_ID
, ''
, ODH.INVOICE_TO_ORG_ID
, ODL.INVOICE_TO_ORG_ID )
, ODH.ORG_ID) BILL_TO_GEOGRAPHY_CODE
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(DECODE(ODL.SHIP_TO_ORG_ID
, ''
, ODH.SHIP_TO_ORG_ID
, ODL.SHIP_TO_ORG_ID )
, ODH.ORG_ID ) SHIP_TO_GEOGRAPHY_CODE
, ODL.LINE_ID LINE_ID
, ODL.LINE_NUMBER LINE_NUMBER
, 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(ODL.INVENTORY_ITEM_ID
, -999) INVENTORY_ITEM_ID
, NVL(ODL.SHIP_FROM_ORG_ID
, -999 ) ITEM_ORGANIZATION_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
, ODL.ORDER_QUANTITY_UOM ORDERED_QUANTITY_UOM
, ODL.ORDERED_QUANTITY ORDERED_QUANTITY
, ODL.SHIPPED_QUANTITY SHIPPED_QUANTITY
, ODL.CANCELLED_QUANTITY CANCELLED_QUANTITY
, ODL.ACTUAL_SHIPMENT_DATE ACTUAL_SHIPMENT_DATE
, ODL.UNIT_SELLING_PRICE SELLING_PRICE
, ODL.UNIT_LIST_PRICE LIST_PRICE
, ODL.TOP_MODEL_LINE_ID TOP_MODEL_LINE_ID
, ODL.LINK_TO_LINE_ID LINK_TO_LINE_ID
FROM ASO_I_ORDER_HEADERS_V ODH
, ASO_I_ORDER_LINES_V ODL
, BIM_SOURCE_CODE_DETAILS SRCD
, AMS_PARTY_MARKET_SEGMENTS PMKT
WHERE ODH.HEADER_ID = ODL.HEADER_ID
AND ODH.MARKETING_SOURCE_CODE_ID = SRCD.SOURCE_CODE_ID (+)
AND ODH.ORDER_CATEGORY_CODE <> 'RETURN'
AND ODL.LINE_CATEGORY_CODE <> 'RETURN'
AND ODL.BOOKED_FLAG = 'Y'
AND NVL(ODL.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'