[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(b.creation_date)
FROM as_sales_leads a, as_sales_leads_log b
WHERE a.sales_lead_id = b.sales_lead_id
AND b.sales_lead_id = p_sales_lead_id ;
SELECT tc_code from_currency,
decode(prim_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
FROM BIM_I_MKT_RATES
WHERE prim_conversion_rate < 0
AND tc_code is not null
AND trx_date >= p_start_date
ORDER BY tc_code,
trx_date ;
SELECT tc_code from_currency,
decode(sec_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
FROM BIM_I_MKT_RATES
WHERE sec_conversion_rate < 0
AND tc_code is not null
AND trx_date >= p_start_date
ORDER BY tc_code,
trx_date ;
SELECT COUNT(*) INTO l_cnt_miss_rate1 FROM BIM_I_MKT_RATES
WHERE
prim_conversion_rate < 0
AND tc_code is not null
AND trx_date >= p_start_date;
SELECT COUNT(*) INTO l_cnt_miss_rate2 FROM BIM_I_MKT_RATES
WHERE
sec_conversion_rate <0
AND tc_code is not null
AND trx_date >= p_start_date;
l_last_update_date DATE;
l_last_update_date DATE;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Drop index before inserting.');
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Inserting table bim_i_marketing_facts_stg');
INSERT /*+ append parallel */
INTO BIM_I_MARKETING_FACTS_STG CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
LEAD_ID ,
METRIC_TYPE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS ,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_TARGETED ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2
)
SELECT /*+ parallel */
-- BIM_I_MARKETING_FACTS_s.nextval ,
sysdate
,sysdate
,-1
,-1
,-1
,transaction_create_date
,lead_id
,metric_type
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,nvl(category_id,-1)
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,leads
,opportunities
,opportunity_amt
,opportunities_open
,orders_booked
,orders_booked_amt
,revenue_forecasted
,revenue_actual
,cost_forecasted
,cost_actual
,budget_approved
,budget_requested
,responses_forecasted
,responses_positive
,customers_targeted
,customers_new
,registrations
,cancellations
,attendance
,OPPORTUNITY_AMT_S
,ORDERS_BOOKED_AMT_S
,REVENUE_FORECASTED_S
,REVENUE_ACTUAL_S
,COST_FORECASTED_S
,COST_ACTUAL_S
,BUDGET_REQUESTED_S
,BUDGET_APPROVED_S
,CONVERSION_RATE_S
,metric1
,metric2
FROM (
SELECT transaction_create_date transaction_create_date
,lead_id lead_id
,metric_type metric_type
,source_code_id source_code_id
,object_type object_type
,object_id object_id
,child_object_type child_object_type
,child_object_id child_object_id
,lead_rank_id lead_rank_id
,object_country object_country
,object_region object_region
,child_object_country child_object_country
,child_object_region child_object_region
,category_id category_id
,business_unit_id business_unit_id
,start_date start_date
,end_date end_date
,object_status object_status
,child_object_status child_object_status
,object_purpose object_purpose
,child_object_purpose child_object_purpose
,activity_type activity_type
,activity_id activity_id
,conversion_rate
,from_currency
,sum(leads) leads
,sum(opportunities) opportunities
,sum(opportunity_amt) opportunity_amt
,sum(opportunities_open) opportunities_open
,sum(orders_booked) orders_booked
,sum(orders_booked_amt) orders_booked_amt
,sum(budget_requested) budget_requested
,sum(budget_approved) budget_approved
,sum(revenue_forecasted) revenue_forecasted
,sum(revenue_actual) revenue_actual
,sum(cost_forecasted) cost_forecasted
,sum(cost_actual) cost_actual
,sum(responses_forecasted) responses_forecasted
,sum(responses_positive) responses_positive
,sum(customers_targeted) customers_targeted
,sum(customers_new) customers_new
,sum(registrations) registrations
,sum(cancellations) cancellations
,sum(attendance) attendance
,sum(OPPORTUNITY_AMT_S) OPPORTUNITY_AMT_S
,sum(ORDERS_BOOKED_AMT_S) ORDERS_BOOKED_AMT_S
,sum(REVENUE_FORECASTED_S) REVENUE_FORECASTED_S
,sum(REVENUE_ACTUAL_S ) REVENUE_ACTUAL_S
,sum(COST_FORECASTED_S ) COST_FORECASTED_S
,sum(COST_ACTUAL_S ) COST_ACTUAL_S
,sum(BUDGET_REQUESTED_S ) BUDGET_REQUESTED_S
,sum(BUDGET_APPROVED_S) BUDGET_APPROVED_S
,CONVERSION_RATE_S CONVERSION_RATE_S
,sum(metric1) metric1
,sum(metric2) metric2
FROM (
--actual revenue
SELECT trunc(f3.last_update_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,sum(nvl(f3.func_actual_delta,0)) revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 902
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
union all --actual revenue at schedule level
SELECT trunc(f3.last_update_date) transaction_create_date
,0 lead_id
,'REVENUE' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,sum(nvl(f3.func_actual_delta,0)) REVENUE_ACTUAL
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by IN ('CSCH','EVEO')
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
--AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 902
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_actual_delta,0)) <>0
union all --cost
SELECT
case
when trunc(f3.last_update_date) < p_start_date then p_start_date
else trunc(f3.last_update_date)
end transaction_create_date
,0 lead_id
,'COST' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,sum(nvl(f3.func_actual_delta,0)) cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date <= p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 901
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY case
when trunc(f3.last_update_date) < p_start_date then p_start_date
else trunc(f3.last_update_date)
end
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
union all --cost at schedule level
SELECT case
when trunc(f3.last_update_date) < p_start_date then p_start_date
else trunc(f3.last_update_date)
end transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,sum(nvl(f3.func_actual_delta,0)) cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date <= p_end_date
AND f3.arc_act_metric_used_by IN ('CSCH','EVEO')
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
--AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 901
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY case
when trunc(f3.last_update_date) < p_start_date then p_start_date
else trunc(f3.last_update_date)
end
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
--sbehera 15 jan 2004
--for campaign forecasted response
union all --forecasted response
SELECT trunc(f3.last_update_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
--AND a.object_type NOT IN ('RCAM')
-- AND a.object_type='CAMP' commented for camp,event and one off
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 903
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
--for campaign schedule forecasted response
union all --forecasted campaign schedule response
SELECT trunc(f3.last_update_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
--AND a.object_type NOT IN ('RCAM')
AND a.child_object_type in('CSCH','EVEO')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 903
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
union all --targeted audience
SELECT trunc(p.creation_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,count(p.list_entry_id) customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_list_entries p
,ams_act_lists q
,bim_i_source_codes a
WHERE p.creation_date between p_start_date and p_end_date
AND p.list_header_id = q.list_header_id
AND q.list_used_by = a.child_object_type
AND q.list_used_by_id = a.child_object_id
AND a.object_type NOT IN ('RCAM')
AND q.list_used_by in ('CSCH','EVEO')
AND q.list_act_type = 'TARGET'
AND p.enabled_flag='Y'
GROUP BY trunc(p.creation_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
union all --targeted audience for schedules of type event
SELECT trunc(p.creation_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,count(p.list_entry_id) customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_list_entries p
,ams_act_lists q
,bim_i_source_codes a
,ams_campaign_schedules_b sch
WHERE p.creation_date between p_start_date and p_end_date
AND p.list_header_id = q.list_header_id
AND q.list_used_by = 'EONE'
AND q.list_act_type = 'TARGET'
AND sch.schedule_id = a.child_object_id
AND a.child_object_type = 'CSCH'
AND sch.activity_type_code = 'EVENTS'
AND q.list_used_by_id = sch.related_event_id
AND a.object_type NOT IN ('RCAM')
AND p.enabled_flag='Y'
GROUP BY trunc(p.creation_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
union all
--budget1
SELECT /*+ USE_HASH(S A B) */
case
when trunc(nvl(s.approval_date,s.last_update_date)) < p_start_date then p_start_date
else trunc(nvl(s.approval_date,s.last_update_date))
end transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate_s
,nvl(s.request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,sum(nvl(s.approved_amount,0)) budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.act_budget_used_by_id = b.source_code_for_id
AND s.arc_act_budget_used_by = b.arc_source_code_for
AND b.source_code_id = a.source_code_id
AND a.object_type NOT IN ('RCAM')
AND s.budget_source_type = 'FUND'
AND s.parent_act_budget_id IS NULL
AND a.start_date <= p_end_date
AND trunc(nvl(s.approval_date,s.last_update_date)) <= p_end_date
AND s.status_code = 'APPROVED'
GROUP BY
case
when trunc(nvl(s.approval_date,s.last_update_date)) < p_start_date then p_start_date
else trunc(nvl(s.approval_date,s.last_update_date))
end
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,fii_currency.get_global_rate_primary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,fii_currency.get_global_rate_secondary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,nvl(s.request_currency,'USD')
HAVING sum(nvl(s.approved_amount,0)) > 0
--budget2
union all
SELECT /*+ USE_HASH(S A B) */
case
when trunc(a.start_date) < p_start_date then p_start_date
else trunc(a.start_date)
end transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0-sum(nvl(s.approved_amount,0)) budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.arc_act_budget_used_by = 'FUND'
AND s.parent_act_budget_id IS NULL
AND s.budget_source_type = b.arc_source_code_for
AND s.budget_source_id = b.source_code_for_id
AND b.source_code_id = a.source_code_id
AND a.object_type NOT IN ('RCAM')
AND a.start_date <= p_end_date
AND s.approval_date <= p_end_date
GROUP BY
case
when trunc(a.start_date) < p_start_date then p_start_date
else trunc(a.start_date)
end
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,fii_currency.get_global_rate_primary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,fii_currency.get_global_rate_secondary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,nvl(request_currency,'USD')
union all
--budget1 for Camp Schedules and Event Schedules
SELECT /*+ USE_HASH(S A B) */
trunc(nvl(s.approval_date,s.last_update_date)) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate_s
,nvl(s.request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,sum(nvl(s.approved_amount,0)) metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.act_budget_used_by_id = b.source_code_for_id
AND s.arc_act_budget_used_by = b.arc_source_code_for
AND b.source_code_id = a.source_code_id
AND a.child_object_type IN ('CSCH','EVEO')
AND s.budget_source_type = 'FUND'
AND a.start_date >= p_start_date
AND a.start_date <= p_end_date
AND trunc(nvl(s.approval_date,s.last_update_date)) <= p_end_date
AND s.status_code = 'APPROVED'
GROUP BY
trunc(nvl(s.approval_date,s.last_update_date))
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,fii_currency.get_global_rate_primary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,fii_currency.get_global_rate_secondary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,nvl(s.request_currency,'USD')
HAVING sum(nvl(s.approved_amount,0)) > 0
union all
--budget2 for Camp Schedules and Event Schedules
SELECT /*+ USE_HASH(S A B) */
trunc(a.start_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0-sum(nvl(s.approved_amount,0)) metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.arc_act_budget_used_by = 'FUND'
AND s.budget_source_type = b.arc_source_code_for
AND s.budget_source_id = b.source_code_for_id
AND b.source_code_id = a.source_code_id
AND a.child_object_type IN ('CSCH','EVEO')
AND a.start_date >= p_start_date
AND a.start_date <= p_end_date
AND s.approval_date <= p_end_date
GROUP BY
trunc(nvl(s.approval_date,s.last_update_date))
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,fii_currency.get_global_rate_primary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,fii_currency.get_global_rate_secondary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
,nvl(request_currency,'USD')
union all --registration1
SELECT /*+ USE_HASH(S A B) */
trunc(X.last_reg_status_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_event_registrations X
,bim_i_source_codes A
WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
AND X.event_offer_id = A.child_object_id
AND A.child_object_type ='EVEO'
AND a.object_type NOT IN ('RCAM')
AND a.start_date >= p_start_date
AND a.start_date <= p_end_date
GROUP BY
trunc(X.last_reg_status_date)
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
union all --registration2
SELECT /*+ USE_HASH(S A B) */
trunc(X.last_reg_status_date) transaction_create_date
,0 lead_id
,'OTHER' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_event_registrations X
,bim_i_source_codes A
WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
AND X.event_offer_id = A.object_id
AND A.object_type ='EONE'
AND a.object_type NOT IN ('RCAM')
AND a.start_date >= p_start_date
AND a.start_date <= p_end_date
GROUP BY
trunc(X.last_reg_status_date)
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
)
GROUP BY transaction_create_date
,lead_id
,metric_type
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,category_id
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,conversion_rate_s) inner ;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Second insert into bim_i_marketing_facts_stg');
INSERT /*+ append parallel */
INTO BIM_I_MARKETING_FACTS_STG CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
LEAD_ID ,
METRIC_TYPE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS ,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2)
SELECT /*+ parallel */
--BIM_I_MARKETING_FACTS_s.nextval ,
sysdate
,sysdate
,-1
,-1
,-1
,transaction_create_date
,lead_id
,metric_type
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,nvl(category_id,-1)
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,leads
,opportunities
,opportunity_amt
,opportunities_open
,orders_booked
,orders_booked_amt
,revenue_forecasted
,revenue_actual
,cost_forecasted
,cost_actual
,budget_approved
,budget_requested
,responses_forecasted
,responses_positive
,customers_new
,registrations
,cancellations
,attendance
,OPPORTUNITY_AMT_S
,ORDERS_BOOKED_AMT_S
,REVENUE_FORECASTED_S
,REVENUE_ACTUAL_S
,COST_FORECASTED_S
,COST_ACTUAL_S
,BUDGET_REQUESTED_S
,BUDGET_APPROVED_S
,CONVERSION_RATE_S
,METRIC1
,METRIC2
FROM (
SELECT transaction_create_date transaction_create_date
,lead_id lead_id
,metric_type metric_type
,source_code_id source_code_id
,object_type object_type
,object_id object_id
,child_object_type child_object_type
,child_object_id child_object_id
,lead_rank_id lead_rank_id
,object_country object_country
,object_region object_region
,child_object_country child_object_country
,child_object_region child_object_region
,category_id category_id
,business_unit_id business_unit_id
,start_date start_date
,end_date end_date
,object_status object_status
,child_object_status child_object_status
,object_purpose object_purpose
,child_object_purpose child_object_purpose
,activity_type activity_type
,activity_id activity_id
,conversion_rate
,from_currency
,sum(leads) leads
,sum(opportunities) opportunities
,sum(opportunity_amt) opportunity_amt
,sum(opportunities_open) opportunities_open
,sum(orders_booked) orders_booked
,sum(orders_booked_amt) orders_booked_amt
,sum(budget_requested) budget_requested
,sum(budget_approved) budget_approved
,sum(revenue_forecasted) revenue_forecasted
,sum(revenue_actual) revenue_actual
,sum(cost_forecasted) cost_forecasted
,sum(cost_actual) cost_actual
,sum(responses_forecasted) responses_forecasted
,sum(responses_positive) responses_positive
,sum(customers_new) customers_new
,sum(registrations) registrations
,sum(cancellations) cancellations
,sum(attendance) attendance
,sum(OPPORTUNITY_AMT_S) OPPORTUNITY_AMT_S
,sum(ORDERS_BOOKED_AMT_S) ORDERS_BOOKED_AMT_S
,sum(REVENUE_FORECASTED_S) REVENUE_FORECASTED_S
,sum(REVENUE_ACTUAL_S ) REVENUE_ACTUAL_S
,sum(COST_FORECASTED_S ) COST_FORECASTED_S
,sum(COST_ACTUAL_S ) COST_ACTUAL_S
,sum(BUDGET_REQUESTED_S ) BUDGET_REQUESTED_S
,sum(BUDGET_APPROVED_S) BUDGET_APPROVED_S
,CONVERSION_RATE_S CONVERSION_RATE_S
,sum(metric1) metric1
,sum(metric2) metric2
FROM (
SELECT trunc(a.start_date) transaction_create_date
,0 lead_id
,'FREV' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,sum(nvl(f3.func_forecasted_delta,0)) revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 902
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
UNION ALL
SELECT trunc(a.start_date) transaction_create_date
,0 lead_id
,'FREV' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,sum(nvl(f3.func_forecasted_delta,0)) revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by IN ('CSCH','EVEO')
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
-- AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 902
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
union all --cost and revenue
SELECT trunc(a.start_date) transaction_create_date
,0 lead_id
,'FCOST' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,sum(nvl(f3.func_forecasted_delta,0)) cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 901
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
union all --cost and revenue at schedule level
SELECT trunc(a.start_date) transaction_create_date
,0 lead_id
,'FCOST' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,sum(nvl(f3.func_forecasted_delta,0)) cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by In ('CSCH','EVEO')
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 901
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
AND a.object_type NOT IN ('RCAM')
GROUP BY a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
)
GROUP BY transaction_create_date
,lead_id
,metric_type
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,category_id
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,conversion_rate_s
) inner ;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Third insert into bim_i_marketing_facts_stg');
INSERT /*+ append parallel */
INTO BIM_I_MARKETING_FACTS_STG CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
METRIC1 ,
METRIC2
)
SELECT /*+ parallel */
sysdate
,sysdate
,-1
,-1
,-1
,trunc(s.start_date)
,s.source_code_id
,s.object_type
,s.object_id
,s.child_object_type
,s.child_object_id
,0
,s.object_country
,s.object_region
,s.child_object_country
,s.child_object_region
,nvl(s.category_id,-1)
,s.business_unit_id
,s.start_date
,s.end_date
,s.object_status
,s.child_object_status
,s.object_purpose
,s.child_object_purpose
,s.activity_type
,s.activity_id
,0
,null
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM bim_i_source_codes s,
bim_i_marketing_facts f
where s.child_object_id = 0
and f.child_object_id(+) = 0
and s.object_id = f.object_id (+)
and s.object_type = f.object_type (+)
AND s.object_type NOT IN ('RCAM')
and f.object_id IS NULL;
BIS_COLLECTION_UTILITIES.deleteLogForObject('MARKETING_FACTS');
INSERT /*+ append parallel */
INTO BIM_I_MARKETING_FACTS CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2
)
SELECT /*+ parallel */
sysdate
,sysdate
,-1
,-1
,-1
,trunc(s.start_date)
,s.source_code_id
,s.object_type
,s.object_id
,s.child_object_type
,s.child_object_id
,0
,s.object_country
,s.object_region
,s.child_object_country
,s.child_object_region
,nvl(s.category_id,-1)
,s.business_unit_id
,s.start_date
,s.end_date
,s.object_status
,s.child_object_status
,s.object_purpose
,s.child_object_purpose
,s.activity_type
,s.activity_id
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,null
,0
,0
FROM bim_i_source_codes s,
bim_i_marketing_facts f
where s.child_object_id > 0
and f.child_object_id(+) > 0
and s.child_object_id = f.child_object_id (+)
and s.child_object_type = f.child_object_type (+)
and f.child_object_id is null;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Insert into bim_i_mkt_rates.');
INSERT /*+ append parallel */
INTO BIM_I_MKT_RATES MRT(tc_code,
trx_date,
prim_conversion_rate,
sec_conversion_rate)
SELECT from_currency,
transaction_create_date,
FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
FROM (select distinct from_currency from_currency,
transaction_create_date transaction_create_date
from bim_i_marketing_facts_stg);
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:After Insert into bim_i_mkt_rates.');
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Final Insert into bim_i_marketing_facts.');
INSERT /*+ append parallel */
INTO BIM_I_MARKETING_FACTS CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
LEAD_ID ,
METRIC_TYPE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS ,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_TARGETED ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2
)
SELECT /*+ parallel */
-- BIM_I_MARKETING_FACTS_s.nextval ,
sysdate
,sysdate
,-1
,-1
,-1
,transaction_create_date
,lead_id
,metric_type
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,nvl(category_id,-1)
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,leads
,opportunities
,opportunity_amt*rt.prim_conversion_rate
,opportunities_open
,orders_booked
,orders_booked_amt*rt.prim_conversion_rate
,revenue_forecasted*rt.prim_conversion_rate
,revenue_actual*rt.prim_conversion_rate
,cost_forecasted*rt.prim_conversion_rate
,cost_actual*rt.prim_conversion_rate
,budget_approved*rt.prim_conversion_rate
,budget_requested*rt.prim_conversion_rate
,responses_forecasted
,responses_positive
,customers_targeted
,customers_new
,registrations
,cancellations
,attendance
,OPPORTUNITY_AMT*sec_conversion_rate
,ORDERS_BOOKED_AMT*sec_conversion_rate
,REVENUE_FORECASTED*sec_conversion_rate
,REVENUE_ACTUAL*sec_conversion_rate
,COST_FORECASTED*sec_conversion_rate
,COST_ACTUAL*sec_conversion_rate
,BUDGET_REQUESTED*sec_conversion_rate
,BUDGET_APPROVED*sec_conversion_rate
,CONVERSION_RATE_S
,metric1*rt.prim_conversion_rate
,metric1*sec_conversion_rate
FROM bim_i_marketing_facts_stg a, bim_i_mkt_rates rt
where a.from_currency = rt.tc_code(+)
and a.transaction_create_date= rt.trx_date(+);
l_last_update_date DATE;
DELETE from bim_i_marketing_facts where transaction_create_date>= p_start_date and metric_type is not null;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Error in first insert:'||sqlerrm(sqlcode));
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:First insert BIM_I_MARKETING_FACTS_STG');
INSERT
INTO BIM_I_MARKETING_FACTS_STG CDF
( -- MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
LEAD_ID ,
METRIC_TYPE ,
TRANSACTION_CREATE_DATE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_TARGETED ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2
)
SELECT
--BIM_I_MARKETING_FACTS_s.nextval,
sysdate
,sysdate
,-1
,-1
,-1
,0
,'OTHER'
,transaction_create_date
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,nvl(category_id,-1)
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,leads
,opportunities
,opportunity_amt
,opportunities_open
,orders_booked
,orders_booked_amt
,revenue_forecasted
,revenue_actual
,cost_forecasted
,cost_actual
,budget_approved
,budget_requested
,responses_forecasted
,responses_positive
,customers_targeted
,customers_new
,registrations
,cancellations
,attendance
,OPPORTUNITY_AMT_S
,ORDERS_BOOKED_AMT_S
,REVENUE_FORECASTED_S
,REVENUE_ACTUAL_S
,COST_FORECASTED_S
,COST_ACTUAL_S
,BUDGET_REQUESTED_S
,BUDGET_APPROVED_S
,CONVERSION_RATE_S
,metric1
,metric2
FROM (
SELECT transaction_create_date transaction_create_date
,source_code_id source_code_id
,object_type object_type
,object_id object_id
,child_object_type child_object_type
,child_object_id child_object_id
,lead_rank_id lead_rank_id
,object_country object_country
,object_region object_region
,child_object_country child_object_country
,child_object_region child_object_region
,category_id category_id
,business_unit_id business_unit_id
,start_date start_date
,end_date end_date
,object_status object_status
,child_object_status child_object_status
,object_purpose object_purpose
,child_object_purpose child_object_purpose
,activity_type activity_type
,activity_id activity_id
,conversion_rate
,from_currency
,sum(leads) leads
,sum(opportunities) opportunities
,sum(opportunity_amt) opportunity_amt
,sum(opportunities_open) opportunities_open
,sum(orders_booked) orders_booked
,sum(orders_booked_amt) orders_booked_amt
,sum(budget_requested) budget_requested
,sum(budget_approved) budget_approved
,sum(revenue_forecasted) revenue_forecasted
,sum(revenue_actual) revenue_actual
,sum(cost_forecasted) cost_forecasted
,sum(cost_actual) cost_actual
,sum(responses_forecasted) responses_forecasted
,sum(responses_positive) responses_positive
,sum(customers_targeted) customers_targeted
,sum(customers_new) customers_new
,sum(registrations) registrations
,sum(cancellations) cancellations
,sum(attendance) attendance
,sum(OPPORTUNITY_AMT_S) OPPORTUNITY_AMT_S
,sum(ORDERS_BOOKED_AMT_S) ORDERS_BOOKED_AMT_S
,sum(REVENUE_FORECASTED_S) REVENUE_FORECASTED_S
,sum(REVENUE_ACTUAL_S ) REVENUE_ACTUAL_S
,sum(COST_FORECASTED_S ) COST_FORECASTED_S
,sum(COST_ACTUAL_S ) COST_ACTUAL_S
,sum(BUDGET_REQUESTED_S ) BUDGET_REQUESTED_S
,sum(BUDGET_APPROVED_S) BUDGET_APPROVED_S
,CONVERSION_RATE_S CONVERSION_RATE_S
,sum(metric1) metric1
,sum(metric2) metric2
FROM (
--cost and revenue
SELECT /*+ USE_NL(F3 A G3) ordered */ trunc(f3.last_update_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,sum(nvl(f3.func_actual_delta,0)) REVENUE_ACTUAL
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM
ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 902
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
union all --cost and revenue
SELECT /*+ USE_NL(F3 A G3) ordered */ trunc(f3.last_update_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f3.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,sum(nvl(f3.func_actual_delta,0)) REVENUE_ACTUAL
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM
ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by in ('CSCH','EVEO')
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
AND a.object_type NOT IN ('RCAM')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 902
--AND g3.metric_parent_id IS NULL
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f3.functional_currency_code,'USD')
HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
UNION ALL
SELECT /*+ USE_NL(F1 G1 A) ordered */
trunc(f1.last_update_date) creation_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f1.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,sum(nvl(f1.func_actual_delta,0)) cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 CONVERSION_RATE_S
,0 metric1
,0 metric2
FROM bim_i_source_codes a
,ams_act_metric_hst f1
,ams_metrics_all_b g1
WHERE f1.last_update_date between p_start_date and p_end_date
AND f1.arc_act_metric_used_by = a.object_type
AND f1.act_metric_used_by_id =a.object_id
AND a.child_object_id =0
AND a.object_type NOT IN ('RCAM')
AND g1.metric_category = 901
AND g1.metric_id = f1.metric_id
AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
GROUP BY trunc(f1.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f1.functional_currency_code,'USD')
HAVING sum(nvl(f1.func_actual_delta,0)) <> 0
UNION ALL
SELECT /*+ USE_NL(F1 G1 A) ordered */
trunc(f1.last_update_date) creation_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f1.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,sum(nvl(f1.func_actual_delta,0)) cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_new
,0 customers_targeted
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 CONVERSION_RATE_S
,0 metric1
,0 metric2
FROM bim_i_source_codes a
,ams_act_metric_hst f1
,ams_metrics_all_b g1
WHERE f1.last_update_date between p_start_date and p_end_date
AND f1.arc_act_metric_used_by in ('CSCH','EVEO')
AND f1.act_metric_used_by_id =a.child_object_id
AND f1.ARC_ACT_METRIC_USED_BY = a.child_object_type
AND a.object_type NOT IN ('RCAM')
AND g1.metric_category = 901
AND g1.metric_id = f1.metric_id
AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
GROUP BY trunc(f1.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f1.functional_currency_code,'USD')
HAVING sum(nvl(f1.func_actual_delta,0)) <> 0
--sbehera 15 jan 2004
--for campaign forecasted response
union all --forecasted response
SELECT trunc(f3.last_update_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.arc_act_metric_used_by = a.object_type
AND f3.act_metric_used_by_id = a.object_id
AND a.child_object_id =0
--AND a.object_type NOT IN ('RCAM')
-- AND a.object_type='CAMP' commented for camp.,event,one off
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 903
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
--for campaign schedule forecasted response
union all --forecasted campaign schedule response
SELECT trunc(f3.last_update_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_act_metric_hst f3
,ams_metrics_all_b g3
,bim_i_source_codes a
WHERE f3.last_update_date between p_start_date and p_end_date
AND f3.act_metric_used_by_id = a.child_object_id
AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
--AND a.object_type NOT IN ('RCAM')
AND a.child_object_type in ('CSCH','EVEO')
AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
AND g3.metric_category = 903
AND g3.metric_id = f3.metric_id
GROUP BY trunc(f3.last_update_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
union all --targeted audience
SELECT trunc(p.creation_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,count(p.list_entry_id) customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_list_entries p
,ams_act_lists q
,bim_i_source_codes a
WHERE p.creation_date between p_start_date and p_end_date
AND p.list_header_id = q.list_header_id
AND q.list_used_by = a.child_object_type
AND q.list_used_by_id = a.child_object_id
AND a.object_type NOT IN ('RCAM')
AND q.list_used_by in ('CSCH','EVEO')
AND q.list_act_type = 'TARGET'
AND p.enabled_flag='Y'
GROUP BY trunc(p.creation_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
union all --targeted audience for schedules of type event
SELECT trunc(p.creation_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,count(p.list_entry_id) customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_list_entries p
,ams_act_lists q
,bim_i_source_codes a
,ams_campaign_schedules_b sch
WHERE p.creation_date between p_start_date and p_end_date
AND p.list_header_id = q.list_header_id
AND q.list_used_by = 'EONE'
AND q.list_act_type = 'TARGET'
AND sch.schedule_id = a.child_object_id
AND a.child_object_type = 'CSCH'
AND sch.activity_type_code = 'EVENTS'
AND q.list_used_by_id = sch.related_event_id
AND a.object_type NOT IN ('RCAM')
AND p.enabled_flag='Y'
GROUP BY trunc(p.creation_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
union all --budgets
SELECT /*+ USE_NL(A B S) */
trunc(nvl(s.approval_date,s.last_update_date)) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate_s
,nvl(s.request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,sum(nvl(s.approved_amount,0)) budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.act_budget_used_by_id = b.source_code_for_id
AND s.arc_act_budget_used_by = b.arc_source_code_for
AND b.source_code_id = a.source_code_id
AND a.object_type NOT IN ('RCAM')
AND s.budget_source_type = 'FUND'
AND s.parent_act_budget_id IS NULL
AND s.status_code = 'APPROVED'
AND trunc(nvl(s.approval_date,s.last_update_date)) between p_start_date and p_end_date
GROUP BY
trunc(nvl(s.approval_date,s.last_update_date))
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,nvl(s.request_currency,'USD')
HAVING sum(nvl(s.approved_amount,0)) > 0
union all --budget2
SELECT /*+ USE_NL(A B S) */
trunc(a.start_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0-sum(nvl(s.approved_amount,0)) budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.arc_act_budget_used_by = 'FUND'
AND s.parent_act_budget_id IS NULL
AND s.budget_source_type = b.arc_source_code_for
AND s.budget_source_id = b.source_code_for_id
AND b.source_code_id = a.source_code_id
AND a.object_type NOT IN ('RCAM')
AND nvl(s.approval_date,s.last_update_date) between p_start_date and p_end_date
GROUP BY
trunc(nvl(s.approval_date,s.last_update_date))
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,nvl(request_currency,'USD')
union all --budget1 for campaign schedules and event schedules
SELECT /*+ USE_NL(A B S) */
trunc(nvl(s.approval_date,s.last_update_date)) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate_s
,nvl(s.request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,sum(nvl(s.approved_amount,0)) metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.act_budget_used_by_id = b.source_code_for_id
AND s.arc_act_budget_used_by = b.arc_source_code_for
AND b.source_code_id = a.source_code_id
AND a.child_object_type IN ('CSCH','EVEO')
AND s.budget_source_type = 'FUND'
AND s.status_code = 'APPROVED'
AND trunc(nvl(s.approval_date,s.last_update_date)) between p_start_date and p_end_date
GROUP BY
trunc(nvl(s.approval_date,s.last_update_date))
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,nvl(s.request_currency,'USD')
HAVING sum(nvl(s.approved_amount,0)) > 0
union all --budget2 for campaign schedules and Events
SELECT /*+ USE_NL(A B S) */
trunc(a.start_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(request_currency,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0-sum(nvl(s.approved_amount,0)) metric1
,0 metric2
FROM ozf_act_budgets S
,bim_i_source_codes A
,ams_source_codes B
WHERE s.arc_act_budget_used_by = 'FUND'
AND s.budget_source_type = b.arc_source_code_for
AND s.budget_source_id = b.source_code_for_id
AND b.source_code_id = a.source_code_id
AND a.child_object_type IN ('CSCH','EVEO')
AND nvl(s.approval_date,s.last_update_date) between p_start_date and p_end_date
GROUP BY
trunc(nvl(s.approval_date,s.last_update_date))
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
,nvl(request_currency,'USD')
union all --registration1
SELECT /*+ USE_NL(X A) */
trunc(X.last_reg_status_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_event_registrations X
,bim_i_source_codes A
WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
AND X.event_offer_id = A.child_object_id
AND A.child_object_type ='EVEO'
AND a.object_type NOT IN ('RCAM')
GROUP BY
trunc(X.last_reg_status_date)
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
union all --registration2
SELECT /*+ USE_NL(X A) */
trunc(X.last_reg_status_date) transaction_create_date
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,null from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,0 cost_actual
,0 cost_forecasted
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_S
,0 metric1
,0 metric2
FROM ams_event_registrations X
,bim_i_source_codes A
WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
AND X.event_offer_id = A.object_id
AND A.object_type ='EONE'
AND a.object_type NOT IN ('RCAM')
GROUP BY
trunc(X.last_reg_status_date)
,a.source_code_id
,a.object_id
,a.object_type
,a.child_object_type
,a.child_object_id
,a.object_country
,a.child_object_country
,a.object_region
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.business_unit_id
,a.start_date
,a.end_date
)
GROUP BY transaction_create_date
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,category_id
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,conversion_rate_s) inner;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Second insert BIM_I_MARKETING_FACTS_STG');
INSERT INTO bim_i_marketing_facts_STG
( --MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
LEAD_ID ,
METRIC_TYPE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS ,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2)
(
SELECT /*+ use_hash(INNER) */
sysdate creation_date
,sysdate last_update_date
,-1 created_by
,-1 last_updated_by
,-1 last_update_login
,inner.transaction_create_date transaction_create_date
,0 lead_id
,inner.metric_type metric_type
,inner.source_code_id source_code_id
,inner.object_type object_type
,inner.object_id object_id
,inner.child_object_type child_object_type
,inner.child_object_id child_object_id
,inner.lead_rank_id lead_rank_id
,inner.object_country object_country
,inner.object_region object_region
,inner.child_object_country child_object_country
,inner.child_object_region child_object_region
,inner.category_id category_id
,inner.business_unit_id business_unit_id
,inner.start_date start_date
,inner.end_date end_date
,inner.object_status object_status
,inner.child_object_status child_object_status
,inner.object_purpose object_purpose
,inner.child_object_purpose child_object_purpose
,inner.activity_type activity_type
,inner.activity_id activity_id
,inner.conversion_rate conversion_rate
,inner.from_currency from_currency
,inner.leads leads
,inner.opportunities opportunities
,inner.opportunity_amt opportunity_amt
,inner.opportunities_open opportunities_open
,inner.orders_booked orders_booked
,inner.orders_booked_amt orders_booked_amt
,inner.revenue_forecasted revenue_forecasted
,inner.revenue_actual revenue_actual
,inner.cost_forecasted cost_forecasted
,inner.cost_actual cost_actual
,inner.budget_approved budget_approved
,inner.budget_requested budget_requested
,inner.responses_forecasted responses_forecasted
,inner.responses_positive responses_positive
,inner.customers_new customers_new
,inner.registrations registrations
,inner.cancellations cancellations
,inner.attendance attendance
,inner.OPPORTUNITY_AMT_S OPPORTUNITY_AMT_S
,inner.ORDERS_BOOKED_AMT_S ORDERS_BOOKED_AMT_S
,inner.REVENUE_FORECASTED_S REVENUE_FORECASTED_S
,inner.REVENUE_ACTUAL_S REVENUE_ACTUAL_S
,inner.COST_FORECASTED_S COST_FORECASTED_S
,inner.COST_ACTUAL_S COST_ACTUAL_S
,inner.BUDGET_REQUESTED_S BUDGET_REQUESTED_S
,inner.BUDGET_APPROVED_S BUDGET_APPROVED_S
,inner.CONVERSION_RATE_S CONVERSION_RATE_S
,inner.metric1 metric1
,inner.metric2 metric2
FROM (
SELECT /*+ USE_NL(F1 G1 A) ordered */
trunc(a.start_date) transaction_create_date
,'FCOST' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f1.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,sum(nvl(f1.func_forecasted_delta,0)) cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM bim_i_source_codes a
,ams_act_metric_hst f1
,ams_metrics_all_b g1
WHERE
f1.arc_act_metric_used_by = a.object_type
AND f1.act_metric_used_by_id = a.object_id
AND a.object_type NOT IN ('RCAM')
AND a.child_object_id = 0
AND g1.metric_category = 901
AND g1.metric_id = f1.metric_id
AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
GROUP BY
a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f1.functional_currency_code,'USD')
HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
UNION ALL
SELECT /*+ USE_NL(F1 G1 A) ordered */
trunc(a.start_date) transaction_create_date
,'FCOST' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,category_id category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f1.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,0 revenue_forecasted
,0 revenue_actual
,sum(nvl(f1.func_forecasted_delta,0)) cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM bim_i_source_codes a
,ams_act_metric_hst f1
,ams_metrics_all_b g1
WHERE
f1.arc_act_metric_used_by in ('CSCH','EVEO')
AND f1.act_metric_used_by_id = a.child_object_id
AND a.object_type NOT IN ('RCAM')
AND f1.ARC_ACT_METRIC_USED_BY = a.child_object_type
AND g1.metric_category = 901
AND g1.metric_id = f1.metric_id
AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
GROUP BY
a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f1.functional_currency_code,'USD')
HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
UNION ALL
SELECT /*+ USE_NL(F1 G1 A) ordered */
trunc(a.start_date) transaction_create_date
,'FREV' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f1.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,sum(nvl(f1.func_forecasted_delta,0)) revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM bim_i_source_codes a
,ams_act_metric_hst f1
,ams_metrics_all_b g1
WHERE f1.arc_act_metric_used_by = a.object_type
AND f1.act_metric_used_by_id = a.object_id
AND a.object_type NOT IN ('RCAM')
AND a.child_object_id = 0
AND g1.metric_category = 902
AND g1.metric_id = f1.metric_id
AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
GROUP BY
a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f1.functional_currency_code,'USD')
HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
UNION ALL
SELECT /*+ USE_NL(F1 G1 A) ordered */
trunc(a.start_date) transaction_create_date
,'FREV' metric_type
,a.source_code_id source_code_id
,a.object_type object_type
,a.object_id object_id
,a.child_object_type child_object_type
,a.child_object_id child_object_id
,0 lead_rank_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.business_unit_id business_unit_id
,a.start_date
,a.end_date
,a.object_status object_status
,a.child_object_status child_object_status
,a.object_purpose object_purpose
,a.child_object_purpose child_object_purpose
,a.activity_type activity_type
,a.activity_id activity_id
,0 conversion_rate
,nvl(f1.functional_currency_code,'USD') from_currency
,0 leads
,0 opportunities
,0 opportunity_amt
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders_booked
,0 orders_booked_amt
,0 budget_requested
,0 budget_approved
,sum(nvl(f1.func_forecasted_delta,0)) revenue_forecasted
,0 revenue_actual
,0 cost_forecasted
,0 cost_actual
,0 responses_forecasted
,0 responses_positive
,0 customers_targeted
,0 customers_new
,0 registrations
,0 cancellations
,0 attendance
,0 OPPORTUNITY_AMT_S
,0 ORDERS_BOOKED_AMT_S
,0 REVENUE_FORECASTED_S
,0 REVENUE_ACTUAL_S
,0 COST_FORECASTED_S
,0 COST_ACTUAL_S
,0 BUDGET_REQUESTED_S
,0 BUDGET_APPROVED_S
,0 conversion_rate_s
,0 metric1
,0 metric2
FROM bim_i_source_codes a
,ams_act_metric_hst f1
,ams_metrics_all_b g1
WHERE
f1.arc_act_metric_used_by in ('CSCH','EVEO')
AND f1.act_metric_used_by_id = a.child_object_id
AND f1.ARC_ACT_METRIC_USED_BY = a.child_object_type
AND a.object_type NOT IN ('RCAM')
AND g1.metric_category = 902
AND g1.metric_id = f1.metric_id
AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
GROUP BY
a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,a.category_id
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,a.start_date
,a.end_date
,a.business_unit_id
,nvl(f1.functional_currency_code,'USD')
HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
) inner
WHERE NOT EXISTS
(SELECT source_code_id from bim_i_marketing_facts facts
WHERE facts.object_id = inner.object_id
AND facts.object_type = inner.object_type
AND facts.source_code_id = inner.source_code_id
AND facts.metric_type = inner.metric_type
AND facts.child_object_type = inner.child_object_type
AND facts.child_object_id = inner.child_object_id)
);
/* WHEN MATCHED THEN UPDATE SET
facts.last_update_date = changes.last_update_date
WHEN NOT MATCHED THEN INSERT
(
facts.creation_date
,facts.last_update_date
,facts.created_by
,facts.last_updated_by
,facts.last_update_login
,facts.metric_type
,facts.lead_id
,facts.transaction_create_date
,facts.source_code_id
,facts.object_type
,facts.object_id
,facts.child_object_type
,facts.child_object_id
,facts.lead_rank_id
,facts.object_country
,facts.object_region
,facts.child_object_country
,facts.child_object_region
,facts.category_id
,facts.business_unit_id
,facts.start_date
,facts.end_date
,facts.object_status
,facts.child_object_status
,facts.object_purpose
,facts.child_object_purpose
,facts.activity_type
,facts.activity_id
,facts.conversion_rate
,facts.from_currency
,facts.leads
,facts.opportunities
,facts.opportunity_amt
,facts.opportunities_open
,facts.orders_booked
,facts.orders_booked_amt
,facts.revenue_forecasted
,facts.revenue_actual
,facts.cost_forecasted
,facts.cost_actual
,facts.budget_approved
,facts.budget_requested
,facts.responses_forecasted
,facts.responses_positive
,facts.customers_new
,facts.registrations
,facts.cancellations
,facts.attendance
,facts.OPPORTUNITY_AMT_S OPPORTUNITY_AMT_S
,facts.ORDERS_BOOKED_AMT_S ORDERS_BOOKED_AMT_S
,facts.REVENUE_FORECASTED_S REVENUE_FORECASTED_S
,facts.REVENUE_ACTUAL_S REVENUE_ACTUAL_S
,facts.COST_FORECASTED_S COST_FORECASTED_S
,facts.COST_ACTUAL_S COST_ACTUAL_S
,facts.BUDGET_REQUESTED_S BUDGET_REQUESTED_S
,facts.BUDGET_APPROVED_S BUDGET_APPROVED_S
,facts.CONVERSION_RATE_S CONVERSION_RATE_S
)
VALUES
(
changes.creation_date
,changes.last_update_date
,changes.created_by
,changes.last_updated_by
,changes.last_update_login
,changes.metric_type
,changes.lead_id
,changes.transaction_create_date
,changes.source_code_id
,changes.object_type
,changes.object_id
,changes.child_object_type
,changes.child_object_id
,changes.lead_rank_id
,changes.object_country
,changes.object_region
,changes.child_object_country
,changes.child_object_region
,nvl(changes.category_id,-1)
,changes.business_unit_id
,changes.start_date
,changes.end_date
,changes.object_status
,changes.child_object_status
,changes.object_purpose
,changes.child_object_purpose
,changes.activity_type
,changes.activity_id
,changes.conversion_rate
,changes.from_currency
,changes.leads
,changes.opportunities
,changes.opportunity_amt
,changes.opportunities_open
,changes.orders_booked
,changes.orders_booked_amt
,changes.revenue_forecasted
,changes.revenue_actual
,changes.cost_forecasted
,changes.cost_actual
,changes.budget_approved
,changes.budget_requested
,changes.responses_forecasted
,changes.responses_positive
,changes.customers_new
,changes.registrations
,changes.cancellations
,changes.attendance
,changes.OPPORTUNITY_AMT_S OPPORTUNITY_AMT_S
,changes.ORDERS_BOOKED_AMT_S ORDERS_BOOKED_AMT_S
,changes.REVENUE_FORECASTED_S REVENUE_FORECASTED_S
,changes.REVENUE_ACTUAL_S REVENUE_ACTUAL_S
,changes.COST_FORECASTED_S COST_FORECASTED_S
,changes.COST_ACTUAL_S COST_ACTUAL_S
,changes.BUDGET_REQUESTED_S BUDGET_REQUESTED_S
,changes.BUDGET_APPROVED_S BUDGET_APPROVED_S
,changes.CONVERSION_RATE_S CONVERSION_RATE_S
); */
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Third insert BIM_I_MARKETING_FACTS_STG');
INSERT INTO BIM_I_MARKETING_FACTS_STG CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
from_currency ,
LEADS,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
METRIC1 ,
METRIC2
)
SELECT sysdate
,sysdate
,-1
,-1
,-1
,TRUNC(a.start_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,0
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,nvl(a.category_id,-1)
,a.business_unit_id
,a.start_date
,a.end_date
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,null
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM bim_i_source_codes a
where a.child_object_id =0
and a.obj_last_update_date >l_sc_s_date
and not exists (
select b.object_id,b.object_type from
bim_i_marketing_facts b
where b.child_object_id =0
and a.object_id = b.object_id
AND a.object_type NOT IN ('RCAM')
and a.object_type = b.object_type);
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Inserting into BIM_I_MKT_RATES');
INSERT
INTO BIM_I_MKT_RATES MRT(tc_code,
trx_date,
prim_conversion_rate,
sec_conversion_rate)
SELECT from_currency,
transaction_create_date,
FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
FROM (select distinct from_currency from_currency,
transaction_create_date transaction_create_date
from bim_i_marketing_facts_stg);
DELETE from bim_i_marketing_facts_stg where transaction_create_date>= p_start_date;
BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Final insert into bim_i_marketing_facts');
INSERT
INTO BIM_I_MARKETING_FACTS CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
LEAD_ID ,
METRIC_TYPE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
CONVERSION_RATE ,
FROM_CURRENCY ,
LEADS ,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_TARGETED ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2
)
SELECT /*+ parallel */
-- BIM_I_MARKETING_FACTS_s.nextval ,
sysdate
,sysdate
,-1
,-1
,-1
,transaction_create_date
,lead_id
,metric_type
,source_code_id
,object_type
,object_id
,child_object_type
,child_object_id
,lead_rank_id
,object_country
,object_region
,child_object_country
,child_object_region
,nvl(category_id,-1)
,business_unit_id
,start_date
,end_date
,object_status
,child_object_status
,object_purpose
,child_object_purpose
,activity_type
,activity_id
,conversion_rate
,from_currency
,leads
,opportunities
,opportunity_amt*rt.prim_conversion_rate
,opportunities_open
,orders_booked
,orders_booked_amt*rt.prim_conversion_rate
,revenue_forecasted*rt.prim_conversion_rate
,revenue_actual*rt.prim_conversion_rate
,cost_forecasted*rt.prim_conversion_rate
,cost_actual*rt.prim_conversion_rate
,budget_approved*rt.prim_conversion_rate
,budget_requested*rt.prim_conversion_rate
,responses_forecasted
,responses_positive
,customers_targeted
,customers_new
,registrations
,cancellations
,attendance
,OPPORTUNITY_AMT*sec_conversion_rate
,ORDERS_BOOKED_AMT*sec_conversion_rate
,REVENUE_FORECASTED*sec_conversion_rate
,REVENUE_ACTUAL*sec_conversion_rate
,COST_FORECASTED*sec_conversion_rate
,COST_ACTUAL*sec_conversion_rate
,BUDGET_REQUESTED*sec_conversion_rate
,BUDGET_APPROVED*sec_conversion_rate
,CONVERSION_RATE_S
,metric1*rt.prim_conversion_rate
,metric1*sec_conversion_rate
FROM bim_i_marketing_facts_stg a, bim_i_mkt_rates rt
where a.from_currency = rt.tc_code(+)
and a.transaction_create_date= rt.trx_date(+);
INSERT INTO BIM_I_MARKETING_FACTS CDF (
--MKT_DAILY_TRANSACTION_ID ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
CREATED_BY ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_CREATE_DATE ,
SOURCE_CODE_ID ,
OBJECT_TYPE ,
OBJECT_ID ,
CHILD_OBJECT_TYPE ,
CHILD_OBJECT_ID ,
LEAD_RANK_ID ,
OBJECT_COUNTRY ,
OBJECT_REGION ,
CHILD_OBJECT_COUNTRY ,
CHILD_OBJECT_REGION ,
CATEGORY_ID ,
BUSINESS_UNIT_ID ,
START_DATE ,
END_DATE ,
OBJECT_STATUS ,
CHILD_OBJECT_STATUS ,
OBJECT_PURPOSE ,
CHILD_OBJECT_PURPOSE ,
ACTIVITY_TYPE ,
ACTIVITY_ID ,
LEADS,
OPPORTUNITIES ,
OPPORTUNITY_AMT ,
OPPORTUNITIES_OPEN ,
ORDERS_BOOKED ,
ORDERS_BOOKED_AMT ,
REVENUE_FORECASTED ,
REVENUE_ACTUAL ,
COST_FORECASTED ,
COST_ACTUAL ,
BUDGET_APPROVED ,
BUDGET_REQUESTED ,
RESPONSES_FORECASTED ,
RESPONSES_POSITIVE ,
CUSTOMERS_NEW ,
REGISTRATIONS ,
CANCELLATIONS ,
ATTENDANCE ,
OPPORTUNITY_AMT_S ,
ORDERS_BOOKED_AMT_S ,
REVENUE_FORECASTED_S ,
REVENUE_ACTUAL_S ,
COST_FORECASTED_S ,
COST_ACTUAL_S ,
BUDGET_REQUESTED_S ,
BUDGET_APPROVED_S ,
CONVERSION_RATE_S ,
metric1 ,
metric2)
SELECT sysdate
,sysdate
,-1
,-1
,-1
,TRUNC(a.start_date)
,a.source_code_id
,a.object_type
,a.object_id
,a.child_object_type
,a.child_object_id
,0
,a.object_country
,a.object_region
,a.child_object_country
,a.child_object_region
,nvl(a.category_id,-1)
,a.business_unit_id
,a.start_date
,a.end_date
,a.object_status
,a.child_object_status
,a.object_purpose
,a.child_object_purpose
,a.activity_type
,a.activity_id
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,null
,0
,0
FROM bim_i_source_codes a
where a.child_object_id >0
and a.obj_last_update_date >l_sc_s_date
and not exists (
select b.child_object_id,b.child_object_type from
bim_i_marketing_facts b
where b.child_object_id >0
and a.child_object_id = b.child_object_id
AND a.object_type NOT IN ('RCAM')
and a.child_object_type = b.child_object_type);
BIS_COLLECTION_UTILITIES.log('Before Insert into log.');
BIS_COLLECTION_UTILITIES.log('After Insert into log.');