View: BIM_EDW_INTRCTNS_FCV

View Text

SELECT JIA.ACTIVITY_ID || '-' || INST.INSTANCE_CODE INTERACTION_PK
, BSCD.CAMPAIGN_FK CMPGN_FK
, EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK( JIA.SOURCE_CODE_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( JIA.START_DATE_TIME
, JII.PARTY_ID
, INST.INSTANCE_CODE) MKTSGMT_FK
, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK(INST.INSTANCE_CODE
, JIA.SOURCE_CODE_ID
, JII.PARTY_ID ) TGTSGMT_FK
, EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK(INST.INSTANCE_CODE
, JII.PARTY_ID ) SRCLST_FK
, EDW_BIM_IH_MEDIA_PKG.INTRCTN_MEDIA_FK ( JIA.MEDIA_ID
, INST.INSTANCE_CODE ) INTRCTN_MEDIA_FK
, DECODE(JIA.OUTCOME_ID
, ''
, 'NA_EDW'
, JIA.OUTCOME_ID || '-' || INST.INSTANCE_CODE ) INTRCTN_OUTCOME_FK
, DECODE(JIA.REASON_ID
, ''
, 'NA_EDW'
, JIA.REASON_ID || '-' || INST.INSTANCE_CODE ) INTRCTN_REASON_FK
, DECODE(JIA.RESULT_ID
, ''
, 'NA_EDW'
, JIA.RESULT_ID || '-' || INST.INSTANCE_CODE ) INTRCTN_RESULT_FK
, BSCD.OFFER_FK OFFER_FK
, JII.PARTY_ID || '-' || INST.INSTANCE_CODE || '-PARTY-TPRT' CUSTOMER_FK
, DECODE(JIA.START_DATE_TIME
, ''
, 'NA_EDW'
, TO_CHAR(JIA.START_DATE_TIME
, 'DD-MM-YYYY') || '-DAY') TIME_FK
, DECODE( JII.ORG_ID
, ''
, 'NA_EDW'
, JII.ORG_ID || '-' || INST.INSTANCE_CODE ) ORG_FK
, DECODE(HPS.PARTY_SITE_ID
, ''
, 'NA_EDW'
, HPS.PARTY_SITE_ID || '-' || INST.INSTANCE_CODE || '-PARTY_SITE' ) BILL_TO_SITE_FK
, DECODE(HPS.PARTY_SITE_ID
, ''
, 'NA_EDW'
, HPS.PARTY_SITE_ID || '-' || INST.INSTANCE_CODE || '-PARTY_SITE' ) SHIP_TO_SITE_FK
, INST.INSTANCE_CODE INSTANCE_FK
, JIA.ACTIVITY_ID ACTIVITY_ID
, JIA.INTERACTION_ID INTERACTION_ID
, JIA.DURATION ACTIVITY_DURATION
, JIA.START_DATE_TIME ACTIVITY_START_TIME
, JIA.END_DATE_TIME ACTIVITY_END_TIME
, JIA.SOURCE_CODE SOURCE_CODE
, 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
, JIA.CREATION_DATE CREATION_DATE
, TO_NUMBER(NULL) SECURITY_GROUP_ID
, 1 VIEW_ID
, INC.SEQ_ID SEQ_ID
FROM JTF_IH_ACTIVITIES JIA
, JTF_IH_INTERACTIONS JII
, HZ_PARTY_SITES HPS
, EDW_BIM_SOURCE_CODE_DETAILS BSCD
, BIM_EDW_INTRCTNS_INC INC
, EDW_LOCAL_INSTANCE INST
WHERE JIA.INTERACTION_ID = JII.INTERACTION_ID
AND JII.PARTY_ID = HPS.PARTY_ID (+)
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND JIA.ACTIVITY_ID = INC.PRIMARY_KEY
AND NVL(JIA.SOURCE_CODE_ID
, -999) = BSCD.SOURCE_CODE_ID

Columns