View: BIM_EDW_LEADS_FCV

View Text

SELECT ASLL.SALES_LEAD_LINE_ID || '-' || INST.INSTANCE_CODE LEAD_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.SALES_LEAD_LINE_ID || '-' || INST.INSTANCE_CODE LEADLINE_FK
, ASL.STATUS_CODE || '-' || INST.INSTANCE_CODE LEADSTATUS_FK
, ASL.CUSTOMER_ID || '-' || INST.INSTANCE_CODE || '-PARTY-TPRT' CUSTOMER_FK
, TO_CHAR(ASL.CREATION_DATE
, 'DD-MM-YYYY') || '-DAY' TIME_FK
, 'NA_EDW' 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.BUDGET_AMOUNT
, TO_NUMBER(NULL)
, ASL.CURRENCY_CODE
, ''
, ASL.CREATION_DATE ) LEAD_AMOUNT_G
, TO_NUMBER(NULL) LEAD_AMOUNT_B
, ASLL.BUDGET_AMOUNT LEAD_AMOUNT_T
, ASLL.QUANTITY LEAD_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_SALES_LEAD_LINES ASLL
, AS_SALES_LEADS ASL
, EDW_BIM_SOURCE_CODE_DETAILS BSCD
, BIM_EDW_LEADS_INC INC
, EDW_LOCAL_INSTANCE INST
WHERE ASL.SALES_LEAD_ID = ASLL.SALES_LEAD_ID
AND ASLL.SALES_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' ) ) )

Columns