[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_OBJ_METR_MV
Source
SELECT
(CASE
WHEN GROUPING(b.report_date_julian)=0 THEN b.report_date_julian
WHEN GROUPING(b.week_id)=0 THEN b.week_id
WHEN GROUPING(b.ent_period_id)=0 THEN b.ent_period_id
WHEN GROUPING(b.ent_qtr_id)=0 THEN b.ent_qtr_id
ELSE b.ent_year_id
END
) time_id,
(CASE
WHEN GROUPING(b.report_date_julian)=0 THEN 1
WHEN GROUPING(b.week_id)=0 THEN 16
WHEN GROUPING(b.ent_period_id)=0 THEN 32
WHEN GROUPING(b.ent_qtr_id)=0 THEN 64
ELSE 128
END
) period_type_id,
a.object_region object_region,
a.object_country object_country,
c.parent_source_code_id source_code_id,
c.prior_id immediate_parent_id,
a.category_id category_id,
a.lead_source lead_source,
a.channel_code channel_code,
a.lead_rank_id lead_rank_id,
a.cust_category cust_category,
-- null child_object_usage,
a.activity_id activity_id,
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(decode(lead_converted_date, null, 0,leads_converted*(lead_converted_date-lead_creation_date)))
leads_conversion_time,
count(decode(lead_converted_date, null, 0,leads_converted*(lead_converted_date-lead_creation_date)))
leads_conversion_time_c,
sum(aleads_converted) aleads_converted,
count(aleads_converted) aleads_converted_c,
sum(decode(lead_converted_date, null, 0,aleads_converted*(lead_converted_date-lead_creation_date)))
aleads_conversion_time,
count(decode(lead_converted_date, null, 0,aleads_converted*(lead_converted_date-lead_creation_date)))
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(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(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,
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,
b.report_date_julian dummy1_fr,
b.week_id dummy2_fr,
b.ent_period_id dummy3_fr,
b.ent_qtr_id dummy4_fr,
b.ent_year_id dummy5_fr,
grouping_id(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id,
a.object_region,a.object_country,c.parent_source_code_id,c.prior_id,a.category_id,
a.lead_source,a.channel_code,a.lead_rank_id,a.cust_category,a.activity_id
) dummy6_fr
FROM bim_i_mkt_fct_mv a,
FII.FII_TIME_DAY b,
BIM.BIM_I_SOURCE_DENORM c
WHERE b.report_date = a.transaction_create_date
AND a.source_code_id = c.source_code_id
GROUP BY GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id),
c.parent_source_code_id,
c.prior_id,
a.category_id ,
a.object_country,
a.object_region,
a.lead_source,
a.channel_code,
a.lead_rank_id,
a.cust_category,
a.activity_id