[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_MKT_REGN_MV
Source
select /* Dummy Comment */ a.time_id time_id
,a.period_type_id period_type_id
,a.object_region object_region
,decode(grouping(a.object_country),1,'N',0,a.object_country) object_country
,a.object_country object_country_c
,decode(grouping(a.category_id),1,-9,0,a.category_id) category_id
,a.category_id category_id_c
,grouping_id(a.object_country,a.time_id,a.period_type_id,
a.category_id,a.object_region) col_c
,sum(a.leads) leads
,count(a.leads) leads_c
,sum(a.rank_a) rank_a
,count(a.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(a.leads_converted) leads_converted
,count(a.leads_converted) leads_converted_c
,sum(a.leads_conversion_time) leads_conversion_time
,count(a.leads_conversion_time) leads_conversion_time_c
,sum(a.aleads_converted) aleads_converted
,count(a.aleads_converted) aleads_converted_c
,sum(a.aleads_conversion_time) aleads_conversion_time
,count(a.aleads_conversion_time) aleads_conversion_time_c
,sum(a.leads_open) leads_open
,count(a.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(a.leads_customer) leads_customer
,count(a.leads_customer) leads_customer_c
,sum(a.leads_prospect) leads_prospect
,count(a.leads_prospect) leads_prospect_c
,sum(a.leads_conv_customer) leads_conv_customer
,count(a.leads_conv_customer) leads_conv_customer_c
,sum(a.leads_conv_prospect) leads_conv_prospect
,count(a.leads_conv_prospect) leads_conv_prospect_c
,sum(a.leads_qualified) leads_qualified
,count(a.leads_qualified) leads_qualified_c
,sum(a.leads_qualified_a) leads_qualified_a
,count(a.leads_qualified_a) leads_qualified_a_c
,sum(a.responses_positive) responses_positive
,count(a.responses_positive) responses_positive_c
,sum(a.responses_forecasted) responses_forecasted
,count(a.responses_forecasted) responses_forecasted_c
,sum(orders_booked) orders_booked
,count(orders_booked) orders_booked_c
,sum(customers_targeted) customers_targeted
,count(customers_targeted) customers_targeted_c
,sum(a.cost_actual) cost_actual
,count(a.cost_actual) cost_actual_c
,sum(cost_forecasted) cost_forecasted
,count(cost_forecasted) cost_forecasted_c
,sum(a.budget_approved) budget_approved
,count(a.budget_approved) budget_approved_c
,sum(budget_requested) budget_requested
,count(budget_requested) budget_requested_c
,sum(a.orders_booked_amt) orders_booked_amt
,count(a.orders_booked_amt) orders_booked_amt_c
,sum(a.orders_invoiced_amt) orders_invoiced_amt
,count(a.orders_invoiced_amt) orders_invoiced_amt_c
,sum(revenue_actual) revenue_actual
,count(revenue_actual) revenue_actual_c
,sum(revenue_forecasted) revenue_forecasted
,count(revenue_forecasted) revenue_forecasted_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
,sum(registrations) registrations
,count(registrations) registrations_c
,sum(cancellations) cancellations
,count(cancellations) cancellations_c
,sum(attendance) attendance
,count(attendance) attendance_c
,sum(budget_requested_s) budget_requested_s,
count(budget_requested_s) budget_requested_s_c,
sum(budget_approved_s) budget_approved_s,
count(budget_approved_s) budget_approved_s_c,
sum(revenue_forecasted_s) revenue_forecasted_s,
count(revenue_forecasted_s) revenue_forecasted_s_c,
sum(revenue_actual_s) revenue_actual_s,
count(revenue_actual_s) revenue_actual_s_c,
sum(cost_forecasted_s) cost_forecasted_s,
count(cost_forecasted_s) cost_forecasted_s_c,
sum(cost_actual_s) cost_actual_s,
count(cost_actual_s) cost_actual_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_mkt_kpi_int_mv a
group by
time_id,period_type_id,object_region,
cube(a.object_country,a.category_id)