View: BIM_EDW_EVTFRCST_FCV

View Text

SELECT EVO.EVENT_OFFER_ID || '-' || INST.INSTANCE_CODE || '-EVENT_OFFER' EVTFRCST_PK
, 'NA_EDW' CMPGN_FK
, 'NA_EDW' CMPGN_STATUS_FK
, EVO.EVENT_OFFER_ID || '-' || INST.INSTANCE_CODE || '-EVENT_OFFER' EVENT_FK
, 'NA_EDW' MDCHNL_FK
, 'NA_EDW' MKTSGMT_FK
, 'NA_EDW' TGTSGMT_FK
, 'NA_EDW' SRCLST_FK
, 'NA_EDW' OFFER_FK
, 'NA_EDW' CUSTOMER_FK
, DECODE(EVO.EVENT_START_DATE
, ''
, 'NA_EDW'
, TO_CHAR(EVO.EVENT_START_DATE
, 'DD-MM-YYYY') || '-DAY' ) TIME_FK
, DECODE(EVO.ORG_ID
, ''
, 'NA_EDW'
, EVO.ORG_ID || '-' || INST.INSTANCE_CODE ) ORG_FK
, 'NA_EDW' ITEM_FK
, 'NA_EDW' UOM_FK
, 'NA_EDW' SLSCHNL_FK
, 'NA_EDW' BASE_CURRENCY_FK
, EVCT.TRANSACTION_CURRENCY_CODE TRX_CURRENCY_FK
, 'NA_EDW' BILL_TO_SITE_FK
, 'NA_EDW' SHIP_TO_SITE_FK
, INST.INSTANCE_CODE INSTANCE_FK
, DECODE(EVCT.EVENT_OFFER_TOT_ACTL_COST
, ''
, ''
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( EVCT.EVENT_OFFER_TOT_ACTL_COST
, TO_NUMBER(NULL)
, EVCT.TRANSACTION_CURRENCY_CODE
, ''
, EVCT.LAST_CALCULATED_DATE )) EVENT_ACTUAL_COST_G
, TO_NUMBER(NULL) EVENT_ACTUAL_COST_B
, EVCT.EVENT_OFFER_TOT_ACTL_COST EVENT_ACTUAL_COST_T
, DECODE(EVCT.EVENT_OFFER_TOT_FRCST_COST
, ''
, ''
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( EVCT.EVENT_OFFER_TOT_FRCST_COST
, TO_NUMBER(NULL)
, EVCT.TRANSACTION_CURRENCY_CODE
, ''
, EVCT.LAST_CALCULATED_DATE )) EVENT_FRCST_COST_G
, TO_NUMBER(NULL) EVENT_FRCST_COST_B
, EVCT.EVENT_OFFER_TOT_FRCST_COST EVENT_FRCST_COST_T
, DECODE(EVRV.EVENT_OFFER_TOT_REVENUE
, ''
, ''
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( EVRV.EVENT_OFFER_TOT_REVENUE
, TO_NUMBER(NULL)
, EVRV.TRANSACTION_CURRENCY_CODE
, ''
, EVRV.LAST_CALCULATED_DATE )) EVENT_FRCST_REVENUE_G
, TO_NUMBER(NULL) EVENT_FRCST_REVENUE_B
, EVRV.EVENT_OFFER_TOT_REVENUE EVENT_FRCST_REVENUE_T
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.ATTENDED_FLAG ='Y') EVENT_NUMBER_CANCELLED
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.ATTENDED_FLAG ='Y') EVENT_NUMBER_ATTENDED
, ( NVL(EVO.REG_EFFECTIVE_CAPACITY
, 0)- (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID AND REG.SYSTEM_STATUS_CODE='REGISTERED')) EVENT_NUMBER_AVAILABLE
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='ENROLLED') EVENT_NUMBER_ENROLLED
, 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
, EVO.CREATION_DATE CREATION_DATE
, EVO.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TO_NUMBER(NULL) SECURITY_GROUP_ID
, 1 VIEW_ID
, INC.SEQ_ID SEQ_ID
FROM AMS_EVENT_OFFERS_ALL_B EVO
, BIM_EDW_EVENT_COST_V EVCT
, BIM_EDW_EVENT_REVENUE_V EVRV
, BIM_EDW_EVTFRCST_INC INC
, EDW_LOCAL_INSTANCE INST
WHERE EVO.EVENT_OFFER_ID = EVCT.EVENT_OFFER_ID (+)
AND EVO.EVENT_OFFER_ID = EVRV.EVENT_OFFER_ID (+)
AND EVO.EVENT_LEVEL = 'MAIN'
AND EVO.EVENT_START_DATE IS NOT NULL
AND EVO.EVENT_OFFER_ID = INC.PRIMARY_KEY WITH READ ONLY

Columns