[Home] [Help]
| Product: | BIM - Marketing Intelligence |
|---|---|
| Description: | Used by the concurrent programs to load the summary tables. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ACCT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACCT.PARTY_ID PARTY_ID
, ACCT.SOURCE_CODE SOURCE_CODE
, SRC.SOURCE_CODE_ID SOURCE_CODE_ID
, SRC.CAMPAIGN_ID CAMPAIGN_ID
, SRC.MEDIA_ID MEDIA_ID
, SRC.CHANNEL_ID CHANNEL_ID
, SRC.EVENT_ID EVENT_ID
, SRC.EVENT_OFFER_ID EVENT_OFFER_ID
, BIM_MARKET_SEGMENT_PKG.MARKET_SEGMENT_FK(ACCT.PARTY_ID
, ODH.ORDERED_DATE ) MARKET_SEGMENT_ID
, BIM_TARGET_SEGMENT_PKG.TARGET_SEGMENT_FK(ACCT.PARTY_ID
, ACCT.SOURCE_CODE) TARGET_SEGMENT_ID
, NVL(ODH.SALES_CHANNEL_CODE
, '-999') SALES_CHANNEL_CODE
, NVL(ODL.INVENTORY_ITEM_ID
, -999) INVENTORY_ITEM_ID
, NVL(ODL.SHIP_FROM_ORG_ID
, -999) ITEM_ORGANIZATION_ID
, 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
, ODH.ORDERED_DATE FIRST_ORDER_DATE
FROM ASO_I_ORDER_LINES_V ODL
, ASO_I_ORDER_HEADERS_V ODH
, BIM_SOURCE_CODE_DETAILS SRC
, HZ_CUST_ACCOUNTS ACCT
WHERE ACCT.SOURCE_CODE = SRC.SOURCE_CODE
AND ACCT.CUST_ACCOUNT_ID = ODH.CUST_ACCT_ID
AND ACCT.PARTY_ID = ODH.PARTY_ID
AND SRC.SOURCE_CODE_ID = ODH.MARKETING_SOURCE_CODE_ID
AND ODH.HEADER_ID = ODL.HEADER_ID
AND ODH.HEADER_ID = ( SELECT MIN( HDR.HEADER_ID)
FROM ASO_I_ORDER_HEADERS_V HDR
WHERE HDR.CUST_ACCT_ID = ACCT.CUST_ACCOUNT_ID
AND HDR.PARTY_ID = ACCT.PARTY_ID ) WITH READ ONLY