[Home] [Help]
| Product: | BIM - Marketing Intelligence |
|---|---|
| Description: | Used by the concurrent programs to load the summary tables. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT AMC.CAMPAIGN_ID CAMPAIGN_ID
, NVL( AMC.MEDIA_ID
, -999) MEDIA_ID
, NVL( AMC.ARC_CHANNEL_FROM || TO_CHAR(AMC.CHANNEL_ID)
, -999) CHANNEL_ID
, AMC.ACTUAL_EXEC_START_DATE ACTUAL_EXEC_START_DATE
, AMC.ACTUAL_EXEC_END_DATE ACTUAL_EXEC_END_DATE
, NVL( AMC.ORG_ID
, -999) ORG_ID
, AMA1.TRANSACTION_CURRENCY_CODE TRANSACTION_CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE') FUNCTIONAL_CURRENCY_CODE
, AMA1.TRANS_FORECASTED_VALUE FORECASTED_COST_T
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AMA1.TRANSACTION_CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, AMA1.LAST_CALCULATED_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, AMA1.TRANS_FORECASTED_VALUE) FORECASTED_COST_B
, AMA1.TRANS_ACTUAL_VALUE CAMPAIGN_ACTUAL_COST_T
, DECODE(AMC.ARC_CHANNEL_FROM
, 'EVEH'
, BIM_EVENT_ACTUAL_COST_PKG.GET_EVENT_ACTUAL_COST(AMC.ARC_CHANNEL_FROM
, AMC.CHANNEL_ID
, 'TRANSACTION')
, 'EVEO'
, BIM_EVENT_ACTUAL_COST_PKG.GET_EVENT_ACTUAL_COST(AMC.ARC_CHANNEL_FROM
, AMC.CHANNEL_ID
, 'TRANSACTION')
, 0 ) EVENT_ACTUAL_COST_T
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AMA1.TRANSACTION_CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, AMA1.LAST_CALCULATED_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, AMA1.TRANS_ACTUAL_VALUE) CAMPAIGN_ACTUAL_COST_B
, DECODE(AMC.ARC_CHANNEL_FROM
, 'EVEH'
, BIM_EVENT_ACTUAL_COST_PKG.GET_EVENT_ACTUAL_COST(AMC.ARC_CHANNEL_FROM
, AMC.CHANNEL_ID
, 'BASE')
, 'EVEO'
, BIM_EVENT_ACTUAL_COST_PKG.GET_EVENT_ACTUAL_COST(AMC.ARC_CHANNEL_FROM
, AMC.CHANNEL_ID
, 'BASE')
, 0 ) EVENT_ACTUAL_COST_B
, AMA2.TRANS_FORECASTED_VALUE FORECASTED_REVENUE_T
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AMA2.TRANSACTION_CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, AMA2.LAST_CALCULATED_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, AMA2.TRANS_FORECASTED_VALUE) FORECASTED_REVENUE_B
FROM AMS_CAMPAIGNS_ALL_B AMC
, AMS_P_ACT_METRICS_V AMA1
, AMS_P_ACT_METRICS_V AMA2
WHERE AMC.CAMPAIGN_ID = AMA1.ACT_METRIC_USED_BY_ID(+)
AND AMA1.ARC_ACT_METRIC_USED_BY(+) = 'CAMP'
AND AMA1.METRIC_CATEGORY (+) = 'COST'
AND AMA1.SUMMARY_METRIC_ID(+) IS NULL
AND AMC.CAMPAIGN_ID = AMA2.ACT_METRIC_USED_BY_ID(+)
AND AMA2.ARC_ACT_METRIC_USED_BY(+) = 'CAMP'
AND AMA2.METRIC_CATEGORY (+) = 'REVENUE'
AND AMA2.SUMMARY_METRIC_ID(+) IS NULL
AND AMC.ACTUAL_EXEC_START_DATE IS NOT NULL WITH READ ONLY