Product: | BIM - Marketing Intelligence(Obsolete) |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
SELECT ASLL.LEAD_LINE_ID || '-' || INST.INSTANCE_CODE OPPR_LINE_PK
, BSCD.CAMPAIGN_FK CMPGN_FK
, EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK( ASLL.SOURCE_PROMOTION_ID
, INST.INSTANCE_CODE ) CMPGN_STATUS_FK
, BSCD.EVENT_FK EVENT_FK
, BSCD.MEDIA_CHANNEL_FK MEDCHNL_FK
, EDW_BIM_MKTSGMTS_PKG.GET_MKTSGMT_FK(ASL.CREATION_DATE
, ASL.CUSTOMER_ID
, INST.INSTANCE_CODE) MKTSGMT_FK
, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK(INST.INSTANCE_CODE
, ASLL.SOURCE_PROMOTION_ID
, ASL.CUSTOMER_ID ) TGTSGMT_FK
, EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK(INST.INSTANCE_CODE
, ASL.CUSTOMER_ID ) SRCLST_FK
, DECODE(ASLL.OFFER_ID
, ''
, 'NA_EDW'
, ASLL.OFFER_ID || '-' || INST.INSTANCE_CODE ) OFFER_FK
, ASLL.LEAD_LINE_ID || '-' || INST.INSTANCE_CODE OPPLINE_FK
, ASL.STATUS || '-' || INST.INSTANCE_CODE OPPSTATUS_FK
, ASL.CUSTOMER_ID || '-' || INST.INSTANCE_CODE || '-PARTY-TPRT' CUSTOMER_FK
, DECODE( ASL.DECISION_DATE
, ''
, 'NA_EDW'
, TO_CHAR(ASL.DECISION_DATE
, 'DD-MM-YYYY' ) || '-DAY' ) TIME_FK
, DECODE(ASLL.ORG_ID
, ''
, 'NA_EDW'
, ASLL.ORG_ID || '-' || INST.INSTANCE_CODE ) ORG_FK
, EDW_ITEMS_PKG.GET_ITEM_FK( ASLL.INVENTORY_ITEM_ID
, ASLL.ORGANIZATION_ID
, ASLL.INTEREST_TYPE_ID
, ASLL.PRIMARY_INTEREST_CODE_ID
, ASLL.SECONDARY_INTEREST_CODE_ID
, INST.INSTANCE_CODE ) ITEM_FK
, EDW_UTIL.GET_EDW_BASE_UOM(ASLL.UOM_CODE
, ASLL.INVENTORY_ITEM_ID ) UOM_FK
, DECODE(ASL.CHANNEL_CODE
, ''
, 'NA_EDW'
, ASL.CHANNEL_CODE || '-' || INST.INSTANCE_CODE ) SLSCHNL_FK
, 'NA_EDW' BASE_CURRENCY_FK
, ASL.CURRENCY_CODE TRX_CURRENCY_FK
, DECODE(ASL.ADDRESS_ID
, ''
, 'NA_EDW'
, ASL.ADDRESS_ID || '-' || INST.INSTANCE_CODE || '-PARTY_SITE' ) BILL_TO_SITE_FK
, DECODE(ASL.ADDRESS_ID
, ''
, 'NA_EDW'
, ASL.ADDRESS_ID || '-' || INST.INSTANCE_CODE || '-PARTY_SITE' ) SHIP_TO_SITE_FK
, INST.INSTANCE_CODE INSTANCE_FK
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( ASLL.TOTAL_AMOUNT
, TO_NUMBER(NULL)
, ASL.CURRENCY_CODE
, ''
, ASL.CREATION_DATE ) OPPR_AMOUNT_G
, TO_NUMBER(NULL) OPPR_AMOUNT_B
, ASLL.TOTAL_AMOUNT OPPR_AMOUNT_T
, ASLL.QUANTITY OPPR_UNITS
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, ASLL.CREATION_DATE CREATION_DATE
, GREATEST(ASL.LAST_UPDATE_DATE
, ASLL.LAST_UPDATE_DATE ) LAST_UPDATE_DATE
, TO_NUMBER(NULL) SECURITY_GROUP_ID
, 1 VIEW_ID
, INC.SEQ_ID SEQ_ID
FROM AS_LEAD_LINES_ALL ASLL
, AS_LEADS_ALL ASL
, EDW_BIM_SOURCE_CODE_DETAILS BSCD
, BIM_EDW_OPRNTIES_INC INC
, EDW_LOCAL_INSTANCE INST
WHERE ASL.LEAD_ID = ASLL.LEAD_ID
AND ASLL.LEAD_LINE_ID = INC.PRIMARY_KEY
AND NVL(ASLL.SOURCE_PROMOTION_ID
, -999) = BSCD.SOURCE_CODE_ID
AND ( ( ASL.LAST_UPDATE_DATE > TO_DATE( '1000/01/01'
, 'YYYY/MM/DD' ) ) OR ( ASLL.LAST_UPDATE_DATE > TO_DATE( '1000/01/01'
, 'YYYY/MM/DD' ) ) )