[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_OBJ_REGN_MV
Source
SELECT
a.time_id time_id,
a.period_type_id period_type_id,
decode(grouping(a.object_country),1,'N',0,a.object_country)
object_country,
a.object_country object_country_c,
a.source_code_id source_code_id,
a.immediate_parent_id immediate_parent_id,
decode(grouping(a.category_id),1,-9,0,a.category_id)
category_id,
a.category_id category_id_c,
--null child_object_usage,
a.object_region object_region,
sum(responses_forecasted) responses_forecasted,
count(responses_forecasted) responses_forecasted_c,
sum(responses_positive) responses_positive,
count(responses_positive) responses_positive_c,
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(leads_conversion_time) leads_conversion_time,
count(leads_conversion_time) leads_conversion_time_c,
sum(aleads_converted) aleads_converted,
count(aleads_converted) aleads_converted_c,
sum(aleads_conversion_time) aleads_conversion_time,
count(aleads_conversion_time) aleads_conversion_time_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(a.leads_dead) leads_dead,
count(a.leads_dead) leads_dead_c,
sum(a.leads_closed) leads_closed,
count(a.leads_closed) leads_closed_c,
sum(a.leads_qualified) leads_qualified,
count(a.leads_qualified) leads_qualified_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(orders_booked) orders_booked,
count(orders_booked) orders_booked_c,
sum(orders_booked_amt) orders_booked_amt,
count(orders_booked_amt) orders_booked_amt_c,
sum(orders_invoiced_amt) orders_invoiced_amt,
count(orders_invoiced_amt) orders_invoiced_amt_c,
sum(cost_forecasted) cost_forecasted,
count(cost_forecasted) cost_forecasted_c,
sum(cost_actual) cost_actual,
count(cost_actual) cost_actual_c,
sum(budget_requested) budget_requested,
count(budget_requested) budget_requested_c,
sum(budget_approved) budget_approved,
count(budget_approved) budget_approved_c,
sum(revenue_forecasted) revenue_forecasted,
count(revenue_forecasted) revenue_forecasted_c,
sum(revenue_actual) revenue_actual,
count(revenue_actual) revenue_actual_c,
sum(customers_targeted) customers_targeted,
count(customers_targeted) customers_targeted_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) orders_booked_amt_s,
count(booked_order_amount_s) booked_order_amount_s_c,
sum(invoiced_order_amount_s) orders_invoiced_amt_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,
sum(a.budget_approved_sch) budget_approved_sch,
count(a.budget_approved_sch) budget_approved_sch_c,
sum(a.budget_approved_sch_s) budget_approved_sch_s,
count(a.budget_approved_sch_s) budget_approved_sch_s_c,
count(*) all_c,
grouping_id(a.time_id,
a.period_type_id,
a.source_code_id,
a.object_region,
a.immediate_parent_id,
a.object_country,
a.category_id
) grouping_id
from BIM_I_OBJ_METR_MV a
group by a.time_id,
a.period_type_id,
a.object_region,
a.source_code_id,
a.immediate_parent_id,
cube(a.object_country,a.category_id)