[Home] [Help]
| Product: | BIM - Marketing Intelligence(Obsolete) |
|---|---|
| Description: | Used by the concurrent programs to load the summary tables. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT 'EVEO' || TO_CHAR(AME.EVENT_OFFER_ID) EVENT_OFFER_ID
, 'EVEH' || TO_CHAR(AME.EVENT_HEADER_ID) EVENT_HEADER_ID
, AME.EVO_START_DATE EVENT_START_DATE
, AME.EVO_END_DATE EVENT_END_DATE
, AME.REG_NUMBER_ENROLLED NUMBER_ENROLLED
, AME.REG_NUMBER_AVAILABLE NUMBER_SPOTS_AVAILABLE
, AME.REG_NUMBER_ATTENDED NUMBER_ATTENDED
, AME.REG_NUMBER_CANCELLED NUMBER_CANCELLED
, 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 EVENT_ACTUAL_COST_T
, BIM_CAMPAIGN_ACTUAL_COST_PKG.GET_CAMPAIGN_ACTUAL_COST(AME.EVENT_OFFER_ID
, 'TRANSACTION') CAMPAIGN_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) EVENT_ACTUAL_COST_B
, BIM_CAMPAIGN_ACTUAL_COST_PKG.GET_CAMPAIGN_ACTUAL_COST(AME.EVENT_OFFER_ID
, 'BASE') CAMPAIGN_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_P_EVENT_OFFERS_V AME
, AMS_P_ACT_METRICS_V AMA1
, AMS_P_ACT_METRICS_V AMA2
WHERE AME.EVENT_OFFER_ID = AMA1.ACT_METRIC_USED_BY_ID(+)
AND AMA1.ARC_ACT_METRIC_USED_BY(+) = 'EVEO'
AND AMA1.METRIC_CATEGORY(+) = 'COST'
AND AMA1.SUMMARY_METRIC_ID(+) IS NULL
AND AME.EVENT_OFFER_ID = AMA2.ACT_METRIC_USED_BY_ID(+)
AND AMA2.ARC_ACT_METRIC_USED_BY(+) = 'EVEO'
AND AMA2.METRIC_CATEGORY(+) = 'REVENUE'
AND AMA1.SUMMARY_METRIC_ID(+) IS NULL
AND AME.EVO_LEVEL = 'MAIN'
AND AME.EVO_START_DATE IS NOT NULL WITH READ ONLY