[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_MKT_FCT_MV
Source
SELECT /* DUMMY COMMENTS */
1 umark
,a.transaction_create_date transaction_create_date
,b.source_code_id source_code_id
,b.object_type object_type
,b.object_id object_id
,b.child_object_type child_object_type
,b.child_object_id child_object_id
,b.object_country object_country
,nvl(b.category_id,-1) category_id
,b.object_region object_region
,b.child_object_country child_object_country
,b.child_object_region child_object_region
,b.business_unit_id business_unit_id
,b.start_date start_date
,b.end_date end_date
,b.object_status object_status
,b.child_object_status child_object_status
,b.object_purpose object_purpose
,b.child_object_purpose child_object_purpose
,b.activity_type activity_type
,b.activity_id activity_id
,null lead_source
,null channel_code
,null lead_rank_id
,null cust_category
,null lead_creation_date
,null lead_converted_date
,0 leads
,0 leads_c
,0 rank_a
,0 rank_a_c
,0 rank_b
,0 rank_b_c
,0 rank_c
,0 rank_c_c
,0 rank_d
,0 rank_d_c
,0 rank_z
,0 rank_z_c
,0 leads_converted
,0 leads_converted_c
,0 aleads_converted
,0 aleads_converted_c
,0 leads_open
,0 leads_open_c
,0 leads_new
,0 leads_new_c
,0 leads_changed
,0 leads_changed_c
,0 leads_dead
,0 leads_dead_c
,0 leads_closed
,0 leads_closed_c
,0 leads_customer
,0 leads_customer_c
,0 leads_prospect
,0 leads_prospect_c
,0 leads_conv_customer
,0 leads_conv_customer_c
,0 leads_conv_prospect
,0 leads_conv_prospect_c
,0 leads_qualified
,0 leads_qualified_c
,0 leads_qualified_a
,0 leads_qualified_a_c
,0 orders_booked
,0 orders_booked_c
,0 orders_booked_amt
,0 orders_booked_amt_c
,0 orders_invoiced_amt
,0 orders_invoiced_amt_c
,sum(a.budget_requested) budget_requested
,count(a.budget_requested) budget_requested_c
,sum(a.budget_approved) budget_approved
,count(a.budget_approved) budget_approved_c
,sum(a.revenue_forecasted) revenue_forecasted
,count(a.revenue_forecasted) revenue_forecasted_c
,sum(a.revenue_actual) revenue_actual
,count(a.revenue_actual) revenue_actual_c
,sum(a.cost_forecasted) cost_forecasted
,count(a.cost_forecasted) cost_forecasted_c
,sum(a.cost_actual) cost_actual
,count(a.cost_actual) cost_actual_c
,sum(a.customers_targeted) customers_targeted
,count(a.customers_targeted) customers_targeted_c
,sum(a.responses_forecasted) responses_forecasted
,count(a.responses_forecasted) responses_forecasted_c
,0 responses_positive
,0 responses_positive_c
,0 new_opportunity_amt
,0 new_opportunity_amt_c
,0 won_opportunity_amt
,0 won_opportunity_amt_c
,0 lost_opportunity_amt
,0 lost_opportunity_amt_c
,0 open_opportunity_amt
,0 open_opportunity_amt_c
,0 won_opportunity_cnt
,0 won_opportunity_cnt_c
,0 lost_opportunity_cnt
,0 lost_opportunity_cnt_c
,0 camp_started
,0 camp_started_c
,0 even_started
,0 even_started_c
,0 camp_sch_started
,0 camp_sch_started_c
,0 even_sch_started
,0 even_sch_started_c
,0 camp_ended
,0 camp_ended_c
,0 even_ended
,0 even_ended_c
,0 camp_sch_ended
,0 camp_sch_ended_c
,0 even_sch_ended
,0 even_sch_ended_c
,sum(a.registrations) registrations
,count(a.registrations) registrations_c
,sum(a.cancellations) cancellations
,count(a.cancellations) cancellations_c
,sum(a.attendance) attendance
,count(a.attendance) attendance_c
,sum(a.budget_requested_s) budget_requested_s
,count(a.budget_requested_s) budget_requested_s_c
,sum(a.budget_approved_s) budget_approved_s
,count(a.budget_approved_s) budget_approved_s_c
,sum(a.revenue_forecasted_s) revenue_forecasted_s
,count(a.revenue_forecasted_s) revenue_forecasted_s_c
,sum(a.revenue_actual_s) revenue_actual_s
,count(a.revenue_actual_s) revenue_actual_s_c
,sum(a.cost_forecasted_s) cost_forecasted_s
,count(a.cost_forecasted_s) cost_forecasted_s_c
,sum(a.cost_actual_s) cost_actual_s
,count(a.cost_actual_s) cost_actual_s_c
,sum(a.metric1) budget_approved_sch
,count(a.metric1) budget_approved_sch_c
,sum(a.metric2) budget_approved_sch_s
,count(a.metric2) budget_approved_sch_s_c
,0 booked_order_amount_s
,0 booked_order_amount_s_c
,0 invoiced_order_amount_s
,0 invoiced_order_amount_s_c
,0 new_opportunity_amt_s
,0 new_opportunity_amt_s_c
,0 won_opportunity_amt_s
,0 won_opportunity_amt_s_c
,0 lost_opportunity_amt_s
,0 lost_opportunity_amt_s_c
,0 open_opportunity_amt_s
,0 open_opportunity_amt_s_c
,count(*) all_c
FROM BIM.BIM_I_MARKETING_FACTS a
,BIM.BIM_I_SOURCE_CODES b
WHERE a.source_code_id = b.source_code_id
GROUP BY a.transaction_create_date
,b.source_code_id
,b.object_id
,b.object_type
,b.child_object_id
,b.child_object_type
,b.object_country
,b.object_region
,nvl(b.category_id,-1)
,b.child_object_country
,b.child_object_region
,b.business_unit_id
,b.start_date
,b.end_date
,b.object_status
,b.child_object_status
,b.object_purpose
,b.child_object_purpose
,b.activity_type
,b.activity_id
UNION ALL
SELECT
2 umark
,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
,object_country object_country
,vbh_category_id category_id
,object_region object_region
,null child_object_country
,null child_object_region
,0 business_unit_id
,null start_date
,null end_date
,null object_status
,null child_object_status
,null object_purpose
,null child_object_purpose
,null activity_type
,activity_id activity_id
,lead_source lead_source
,channel_code channel_code
,lead_rank_id lead_rank_id
,cust_category cust_category
,lead_creation_date lead_creation_date
,lead_converted_date lead_converted_date
,sum(leads) leads
,count(leads) leads_c
,sum(rank_a) rank_a
,count(rank_a) rank_a_c
,sum(rank_b) rank_b
,count(rank_b) rank_b_c
,sum(rank_c) rank_c
,count(rank_c) rank_c_c
,sum(rank_d) rank_d
,count(rank_d) rank_d_c
,sum(rank_z) rank_z
,count(rank_z) rank_z_c
,sum(leads_converted) leads_converted
,count(leads_converted) leads_converted_c
,sum(aleads_converted) aleads_converted
,count(aleads_converted) aleads_converted_c
,sum(leads_open) leads_open
,count(leads_open) leads_open_c
,sum(leads_new) leads_new
,count(leads_new) leads_new_c
,sum(leads_changed) leads_changed
,count(leads_changed) leads_changed_c
,sum(leads_dead) leads_dead
,count(leads_dead) leads_dead_c
,sum(leads_closed) leads_closed
,count(leads_closed) leads_closed_c
,sum(leads_customer) leads_customer
,count(leads_customer) leads_customer_c
,sum(leads_prospect) leads_prospect
,count(leads_prospect) leads_prospect_c
,sum(leads_conv_customer) leads_conv_customer
,count(leads_conv_customer) leads_conv_customer_c
,sum(leads_conv_prospect) leads_conv_prospect
,count(leads_conv_prospect) leads_conv_prospect_c
,sum(leads_qualified) leads_qualified
,count(leads_qualified) leads_qualified_c
,sum(leads_qualified_a) leads_qualified_a
,count(leads_qualified_a) leads_qualified_a_c
,0 orders_booked
,0 orders_booked_c
,sum(booked_order_amount) orders_booked_amt
,count(booked_order_amount) orders_booked_amt_c
,sum(invoiced_order_amount) orders_invoiced_amt
,count(invoiced_order_amount) orders_invoiced_amt_c
,0 budget_requested
,0 budget_requested_c
,0 budget_approved
,0 budget_approved_c
,0 revenue_forecasted
,0 revenue_forecasted_c
,0 revenue_actual
,0 revenue_actual_c
,0 cost_forecasted
,0 cost_forecasted_c
,0 cost_actual
,0 cost_actual_c
,0 customers_targeted
,0 customers_targeted_c
,0 responses_forecasted
,0 responses_forecasted_c
,sum(response_count) responses_positive
,count(response_count) responses_positive_c
,sum(new_opportunity_amt) new_opportunity_amt
,count(new_opportunity_amt) new_opportunity_amt_c
,sum(won_opportunity_amt) won_opportunity_amt
,count(won_opportunity_amt) won_opportunity_amt_c
,sum(lost_opportunity_amt) lost_opportunity_amt
,count(lost_opportunity_amt) lost_opportunity_amt_c
,sum(open_opportunity_amt) open_opportunity_amt
,count(open_opportunity_amt) open_opportunity_amt_c
,sum(won_opportunity_cnt) won_opportunity_cnt
,count(won_opportunity_cnt) won_opportunity_cnt_c
,sum(lost_opportunity_cnt) lost_opportunity_cnt
,count(lost_opportunity_cnt) lost_opportunity_cnt_c
,sum(camp_started) camp_started
,count(camp_started) camp_started_c
,sum(even_started) even_started
,count(even_started) even_started_c
,sum(camp_sch_started) camp_sch_started
,count(camp_sch_started) camp_sch_started_c
,sum(even_sch_started) even_sch_started
,count(even_sch_started) even_sch_started_c
,sum(camp_ended) camp_ended
,count(camp_ended) camp_ended_c
,sum(even_ended) even_ended
,count(even_ended) even_ended_c
,sum(camp_sch_ended) camp_sch_ended
,count(camp_sch_ended) camp_sch_ended_c
,sum(even_sch_ended) even_sch_ended
,count(even_sch_ended) even_sch_ended_c
,0 registrations
,0 registrations_c
,0 cancellations
,0 cancellations_c
,0 attendance
,0 attendance_c
,0 budget_requested_s
,0 budget_requested_s_c
,0 budget_approved_s
,0 budget_approved_s_c
,0 revenue_forecasted_s
,0 revenue_forecasted_s_c
,0 revenue_actual_s
,0 revenue_actual_s_c
,0 cost_forecasted_s
,0 cost_forecasted_s_c
,0 cost_actual_s
,0 cost_actual_s_c
,0 budget_approved_sch
,0 budget_approved_sch_c
,0 budget_approved_sch_s
,0 budget_approved_sch_s_c
,sum(booked_order_amount_s) booked_order_amount_s
,count(booked_order_amount_s) booked_order_amount_s_c
,sum(invoiced_order_amount_s) invoiced_order_amount_s
,count(invoiced_order_amount_s) invoiced_order_amount_s_c
,sum(new_opportunity_amt_s) new_opportunity_amt_s
,count(new_opportunity_amt_s) new_opportunity_amt_s_c
,sum(won_opportunity_amt_s) won_opportunity_amt_s
,count(won_opportunity_amt_s) won_opportunity_amt_s_c
,sum(lost_opportunity_amt_s) lost_opportunity_amt_s
,count(lost_opportunity_amt_s) lost_opportunity_amt_s_c
,sum(open_opportunity_amt_s) open_opportunity_amt_s
,count(open_opportunity_amt_s) open_opportunity_amt_s_c
,count(*) all_c
FROM BIM_I_EXT_COL_MV
GROUP BY transaction_create_date
,lead_creation_date
,lead_converted_date
,source_code_id
,object_id
,object_type
,child_object_id
,child_object_type
,object_country
,vbh_category_id
,object_region
,activity_id
,lead_source
,channel_code
,lead_rank_id
,cust_category