[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_MKT_KPI_INT_MV
Source
SELECT /* Dummy Comment */
(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
,a.category_id category_id
,'XXXX' Parent_category_Id
,a.lead_source lead_source
,a.channel_code channel_code
,a.lead_rank_id lead_rank_id
,a.cust_category cust_category
,a.activity_id activity_id
,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(decode(a.lead_converted_date, null, 0,
a.leads_converted*(a.lead_converted_date-a.lead_creation_date)))
leads_conversion_time
,sum(a.aleads_converted) aleads_converted
,count(a.aleads_converted) aleads_converted_c
,sum(decode(a.lead_converted_date, null, 0,
a.aleads_converted*(a.lead_converted_date-a.lead_creation_date)))
aleads_conversion_time
,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(leads_qualified_a) leads_qualified_a
,count(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
,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,a.category_id,a.lead_source,a.channel_code,
a.lead_rank_id,a.cust_category,a.activity_id)
dummy6_fr
,count(decode(a.lead_converted_date, null, 0,
a.leads_converted*(a.lead_converted_date-a.lead_creation_date)))
dummy7_fr
,count(decode(a.lead_converted_date, null, 0,
a.aleads_converted*(a.lead_converted_date-a.lead_creation_date)))
dummy8_fr
,count(*) all_c
from bim_mkt_kpi_f_mv a,
FII.FII_TIME_DAY b
where a.transaction_create_date = b.report_date
group by
a.object_region
,a.object_country
,a.category_id
,a.lead_source
,a.channel_code
,a.lead_rank_id
,a.cust_category
,a.activity_id
,GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)