eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: BIM_EDW_CMPFRCST_FCV

Product:	BIM - Marketing Intelligence
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT AMC.CAMPAIGN_ID || '-' || AAM.ACTIVITY_METRIC_ID || '-' || INST.INSTANCE_CODE CMPFRCST_PK
, AMC.CAMPAIGN_ID || '-' || INST.INSTANCE_CODE || '-CMP' CMPGN_FK
, AMC.USER_STATUS_ID || '-' || INST.INSTANCE_CODE CMPGN_STATUS_FK
, DECODE( AMC.ARC_CHANNEL_FROM
, 'EVEH'
, AMC.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-EVENT-L1'
, 'EVEO'
, AMC.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-EVENT_OFFER'
, 'CHLS'
, 'NA_EDW'
, 'NA_EDW' ) EVENT_FK
, DECODE( AMC.ARC_CHANNEL_FROM
, 'EVEH'
, 'NA_EDW'
, 'EVEO'
, 'NA_EDW'
, 'CHLS'
, EDW_BIM_MEDIA_PKG.GET_MEDIA_CHANNEL_FK( AMC.MEDIA_ID
, AMC.CHANNEL_ID
, INST.INSTANCE_CODE )
, 'NA_EDW' ) MEDCHNL_FK
, TO_CHAR(AMC.ACTUAL_EXEC_START_DATE
, 'DD-MM-YYYY') || '-DAY' TIME_FK
, 'NA_EDW' BASE_CURRENCY_FK
, AMC.TRANSACTION_CURRENCY_CODE TRX_CURRENCY_FK
, 'NA_EDW' MKTSGMT_FK
, 'NA_EDW' TGTSGMT_FK
, 'NA_EDW' SRCLST_FK
, 'NA_EDW' CUSTOMER_FK
, DECODE(AMC.ORG_ID
, ''
, 'NA_EDW'
, AMC.ORG_ID || '-' || INST.INSTANCE_CODE ) ORG_FK
, 'NA_EDW' ITEM_FK
, 'NA_EDW' UOM_FK
, 'NA_EDW' BILL_TO_SITE_FK
, 'NA_EDW' SHIP_TO_SITE_FK
, INST.INSTANCE_CODE INSTANCE_FK
, TO_NUMBER(NULL) CMPGN_ACTUAL_COST_G
, TO_NUMBER(NULL) CMPGN_ACTUAL_COST_B
, AAM.TRANS_ACTUAL_VALUE CMPGN_ACTUAL_COST_T
, TO_NUMBER(NULL) CMPGN_FRCST_COST_G
, TO_NUMBER(NULL) CMPGN_FRCST_COST_B
, AAM.TRANS_FORECASTED_VALUE CMPGN_FRCST_COST_T
, TO_NUMBER(NULL) CMPGN_FRCST_REVENUE_G
, TO_NUMBER(NULL) CMPGN_FRCST_REVENUE_B
, TO_NUMBER(NULL) CMPGN_FRCST_REVENUE_T
, AMC.CAMPAIGN_ID CAMPAIGN_ID
, AMT.METRIC_PARENT_ID ROLLUP_METRIC_ID
, AAM.TRANSACTION_CURRENCY_CODE TRANSACTION_CURRENCY_CODE
, AAM.LAST_CALCULATED_DATE LAST_CALCULATED_DATE
, 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
, AMC.CREATION_DATE CREATION_DATE
, GREATEST( AMC.LAST_UPDATE_DATE
, NVL(AAM.LAST_UPDATE_DATE
, AMC.LAST_UPDATE_DATE) ) LAST_UPDATE_DATE
, TO_NUMBER(NULL) SECURITY_GROUP_ID
, 1 VIEW_ID
, INC.SEQ_ID SEQ_ID
FROM AMS_CAMPAIGNS_ALL_B AMC
, AMS_ACT_METRICS_ALL AAM
, AMS_METRICS_ALL_B AMT
, BIM_EDW_CMPFRCST_INC INC
, EDW_LOCAL_INSTANCE INST
WHERE AMC.CAMPAIGN_ID = AAM.ACT_METRIC_USED_BY_ID
AND AAM.ARC_ACT_METRIC_USED_BY = 'CAMP'
AND AAM.METRIC_ID = AMT.METRIC_ID
AND AMT.SUMMARY_METRIC_ID IS NULL
AND AMT.METRIC_CATEGORY = 901
AND AMC.STATUS_CODE NOT IN ( 'NEW'
, 'PLANNING' )
AND AMC.SHOW_CAMPAIGN_FLAG = 'Y'
AND AMC.CAMPAIGN_ID = INC.PRIMARY_KEY
AND ( ( AMC.LAST_UPDATE_DATE > TO_DATE( '1000/01/01'
, 'YYYY/MM/DD' ) ) OR ( AAM.LAST_UPDATE_DATE > TO_DATE( '1000/01/01'
, 'YYYY/MM/DD' ) ) ) UNION ALL SELECT AMC.CAMPAIGN_ID || '-' || AAM.ACTIVITY_METRIC_ID || '-' || INST.INSTANCE_CODE CMPFRCST_PK
, AMC.CAMPAIGN_ID || '-' || INST.INSTANCE_CODE || '-CMP' CMPGN_FK
, AMC.USER_STATUS_ID || '-' || INST.INSTANCE_CODE CMPGN_STATUS_FK
, DECODE( AMC.ARC_CHANNEL_FROM
, 'EVEH'
, AMC.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-EVENT-L1'
, 'EVEO'
, AMC.CHANNEL_ID || '-' || INST.INSTANCE_CODE || '-EVENT_OFFER'
, 'CHLS'
, 'NA_EDW'
, 'NA_EDW' ) EVENT_FK
, DECODE( AMC.ARC_CHANNEL_FROM
, 'EVEH'
, 'NA_EDW'
, 'EVEO'
, 'NA_EDW'
, 'CHLS'
, EDW_BIM_MEDIA_PKG.GET_MEDIA_CHANNEL_FK( AMC.MEDIA_ID
, AMC.CHANNEL_ID
, INST.INSTANCE_CODE )
, 'NA_EDW' ) MEDCHNL_FK
, TO_CHAR(AMC.ACTUAL_EXEC_START_DATE
, 'DD-MM-YYYY') || '-DAY' TIME_FK
, 'NA_EDW' BASE_CURRENCY_FK
, AMC.TRANSACTION_CURRENCY_CODE TRX_CURRENCY_FK
, 'NA_EDW' MKTSGMT_FK
, 'NA_EDW' TGTSGMT_FK
, 'NA_EDW' SRCLST_FK
, 'NA_EDW' CUSTOMER_FK
, DECODE(AMC.ORG_ID
, ''
, 'NA_EDW'
, AMC.ORG_ID || '-' || INST.INSTANCE_CODE ) ORG_FK
, 'NA_EDW' ITEM_FK
, 'NA_EDW' UOM_FK
, 'NA_EDW' BILL_TO_SITE_FK
, 'NA_EDW' SHIP_TO_SITE_FK
, INST.INSTANCE_CODE INSTANCE_FK
, TO_NUMBER(NULL) CMPGN_ACTUAL_COST_G
, TO_NUMBER(NULL) CMPGN_ACTUAL_COST_B
, TO_NUMBER(NULL) CMPGN_ACTUAL_COST_T
, TO_NUMBER(NULL) CMPGN_FRCST_COST_G
, TO_NUMBER(NULL) CMPGN_FRCST_COST_B
, TO_NUMBER(NULL) CMPGN_FRCST_COST_T
, TO_NUMBER(NULL) CMPGN_FRCST_REVENUE_G
, TO_NUMBER(NULL) CMPGN_FRCST_REVENUE_B
, AAM.TRANS_FORECASTED_VALUE CMPGN_FRCST_REVENUE_T
, AMC.CAMPAIGN_ID CAMPAIGN_ID
, AMT.METRIC_PARENT_ID ROLLUP_METRIC_ID
, AAM.TRANSACTION_CURRENCY_CODE TRANSACTION_CURRENCY_CODE
, AAM.LAST_CALCULATED_DATE LAST_CALCULATED_DATE
, 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
, AMC.CREATION_DATE CREATION_DATE
, GREATEST( AMC.LAST_UPDATE_DATE
, NVL(AAM.LAST_UPDATE_DATE
, AMC.LAST_UPDATE_DATE) ) LAST_UPDATE_DATE
, TO_NUMBER(NULL) SECURITY_GROUP_ID
, 2 VIEW_ID
, INC.SEQ_ID SEQ_ID
FROM AMS_CAMPAIGNS_ALL_B AMC
, AMS_ACT_METRICS_ALL AAM
, AMS_METRICS_ALL_B AMT
, BIM_EDW_CMPFRCST_INC INC
, EDW_LOCAL_INSTANCE INST
WHERE AMC.CAMPAIGN_ID = AAM.ACT_METRIC_USED_BY_ID
AND AAM.ARC_ACT_METRIC_USED_BY = 'CAMP'
AND AAM.METRIC_ID = AMT.METRIC_ID
AND AMT.SUMMARY_METRIC_ID IS NULL
AND AMT.METRIC_CATEGORY = 902
AND AMC.STATUS_CODE NOT IN ( 'NEW'
, 'PLANNING' )
AND AMC.SHOW_CAMPAIGN_FLAG = 'Y'
AND AMC.CAMPAIGN_ID = INC.PRIMARY_KEY
AND ( ( AMC.LAST_UPDATE_DATE > TO_DATE( '1000/01/01'
, 'YYYY/MM/DD' ) ) OR ( AAM.LAST_UPDATE_DATE > TO_DATE( '1000/01/01'
, 'YYYY/MM/DD' ) ) ) WITH READ ONLY

Columns

Name
CMPFRCST_PK
CMPGN_FK
CMPGN_STATUS_FK
EVENT_FK
MEDCHNL_FK
TIME_FK
BASE_CURRENCY_FK
TRX_CURRENCY_FK
MKTSGMT_FK
TGTSGMT_FK
SRCLST_FK
CUSTOMER_FK
ORG_FK
ITEM_FK
UOM_FK
BILL_TO_SITE_FK
SHIP_TO_SITE_FK
INSTANCE_FK
CMPGN_ACTUAL_COST_G
CMPGN_ACTUAL_COST_B
CMPGN_ACTUAL_COST_T
CMPGN_FRCST_COST_G
CMPGN_FRCST_COST_B
CMPGN_FRCST_COST_T
CMPGN_FRCST_REVENUE_G
CMPGN_FRCST_REVENUE_B
CMPGN_FRCST_REVENUE_T
CAMPAIGN_ID
ROLLUP_METRIC_ID
TRANSACTION_CURRENCY_CODE
LAST_CALCULATED_DATE
USER_MEASURE1
USER_MEASURE2
USER_MEASURE3
USER_MEASURE4
USER_MEASURE5
USER_FK1
USER_FK2
USER_FK3
USER_FK4
USER_FK5
USER_ATTRIBUTE1
USER_ATTRIBUTE2
USER_ATTRIBUTE3
USER_ATTRIBUTE4
USER_ATTRIBUTE5
USER_ATTRIBUTE6
USER_ATTRIBUTE7
USER_ATTRIBUTE8
USER_ATTRIBUTE9
USER_ATTRIBUTE10
USER_ATTRIBUTE11
USER_ATTRIBUTE12
USER_ATTRIBUTE13
USER_ATTRIBUTE14
USER_ATTRIBUTE15
CREATION_DATE
LAST_UPDATE_DATE
SECURITY_GROUP_ID
VIEW_ID
SEQ_ID