[Home] [Help]
MATERIALIZED VIEW: APPS.BIM_I_LD_GENSG_T_MV
Source
SELECT /* Dummy Comment */
1 umark
,a.group_id group_id
,a.resource_id resource_id
,a.channel_code channel_code
,a.cust_category cust_category
,a.lead_rank_id lead_rank_id
,a.lead_source lead_source
,a.lead_region lead_region
,a.lead_country lead_country
,-1 update_time_id
,(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
,-1 update_period_type_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
,sum(a.leads) leads
,sum(a.leads_new) leads_new
,sum(a.leads_open) leads_open
,sum(a.leads_changed) leads_changed
,sum(a.leads_converted) leads_converted
,sum(decode(a.lead_converted_date, null, 0,
a.leads_converted*(a.lead_converted_date-a.lead_creation_date)))
conversion_time
,sum(a.leads_dead) leads_dead
,sum(a.leads_closed) leads_closed
,sum(a.leads_qualified) leads_qualified
,0 leads_new_conv
,0 leads_new_changed
,sum(a.rank_a) rank_a
,sum(a.rank_b) rank_b
,sum(a.rank_c) rank_c
,sum(a.rank_d) rank_d
,sum(a.rank_z) rank_z
,sum(a.leads_qualified_a) leads_qualified_a
,sum(a.orders_booked_amt) orders_booked_amt
,sum(a.orders_invoiced_amt) orders_invoiced_amt
,sum(a.lead_amount) lead_amount
,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
,0 dummy6_fr
,0 dummy7_fr
,0 dummy8_fr
,0 dummy9_fr
,GROUPING_ID(a.group_id,a.resource_id,a.channel_code,a.cust_category,
a.lead_rank_id,a.lead_source,a.lead_region,a.lead_country,
b.report_date_julian,b.week_id,
b.ent_period_id,b.ent_qtr_id,b.ent_year_id) dummy10_fr
,count(a.leads) dummy11_fr
,count(a.leads_new) dummy12_fr
,count(a.leads_open) dummy13_fr
,count(a.leads_changed) dummy14_fr
,count(a.leads_converted) dummy15_fr
,count(decode(a.lead_converted_date, null, 0,
a.leads_converted*(a.lead_converted_date-a.lead_creation_date)))
dummy16_fr
,count(a.leads_dead) dummy17_fr
,count(a.leads_closed) dummy18_fr
,count(a.leads_qualified) dummy19_fr
,0 dummy20_fr
,0 dummy21_fr
,count(a.rank_a) dummy22_fr
,count(a.rank_b) dummy23_fr
,count(a.rank_c) dummy24_fr
,count(a.rank_d) dummy25_fr
,count(a.rank_z) dummy26_fr
,count(a.orders_booked_amt) dummy27_fr
,count(a.leads_qualified_a) dummy28_fr
,count(a.orders_invoiced_amt) dummy29_fr
,count(a.lead_amount) dummy30_fr
,count(*) dummy31_fr
from
bim_i_ld_gh_sum_mv a
,FII.FII_TIME_DAY b
WHERE a.transaction_create_date = b.report_date
GROUP BY
a.group_id
,a.resource_id
,a.channel_code
,a.cust_category
,a.lead_rank_id
,a.lead_source
,a.lead_region
,a.lead_country
,GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)
UNION ALL
SELECT
2 umark
,a.group_id group_id
,a.resource_id resource_id
,a.channel_code channel_code
,a.cust_category cust_category
,a.lead_rank_id lead_rank_id
,a.lead_source lead_source
,a.lead_region lead_region
,a.lead_country lead_country
,(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
) update_time_id
,(CASE
WHEN GROUPING(c.week_id)=0 THEN c.week_id
WHEN GROUPING(c.ent_period_id)=0 THEN c.ent_period_id
WHEN GROUPING(c.ent_qtr_id)=0 THEN c.ent_qtr_id
ELSE c.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
) update_period_type_id
,(CASE
WHEN GROUPING(c.week_id)=0 THEN 16
WHEN GROUPING(c.ent_period_id)=0 THEN 32
WHEN GROUPING(c.ent_qtr_id)=0 THEN 64
ELSE 128
END
) period_type_id
,0 leads
,0 leads_new
,0 leads_open
,0 leads_changed
,0 leads_converted
,0 conversion_time
,0 leads_dead
,0 leads_closed
,0 leads_qualified
,sum(leads_new) leads_new_conv
,0 leads_new_changed
,0 rank_a
,0 rank_b
,0 rank_c
,0 rank_d
,0 rank_z
,0 leads_qualified_a
,0 orders_booked_amt
,0 orders_invoiced_amt
,0 lead_amount
,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
,c.week_id dummy6_fr
,c.ent_period_id dummy7_fr
,c.ent_qtr_id dummy8_fr
,c.ent_year_id dummy9_fr
,GROUPING_ID(a.group_id,a.resource_id,a.channel_code,a.cust_category,
a.lead_rank_id,a.lead_source,a.lead_region,a.lead_country,
b.report_date_julian,b.week_id,
b.ent_period_id,b.ent_qtr_id,b.ent_year_id,c.week_id,
c.ent_period_id,c.ent_qtr_id,c.ent_year_id) dummy10_fr
,0 dummy11_fr
,0 dummy12_fr
,0 dummy13_fr
,0 dummy14_fr
,0 dummy15_fr
,0 dummy16_fr
,0 dummy17_fr
,0 dummy18_fr
,0 dummy19_fr
,count(a.leads_new) dummy20_fr
,0 dummy21_fr
,0 dummy22_fr
,0 dummy23_fr
,0 dummy24_fr
,0 dummy25_fr
,0 dummy26_fr
,0 dummy27_fr
,0 dummy28_fr
,0 dummy29_fr
,0 dummy30_fr
,count(*) dummy31_fr
from
bim_i_ld_gh_sum_mv a
,FII.FII_TIME_DAY b
,FII.FII_TIME_DAY c
WHERE a.lead_converted_date = b.report_date
AND a.lead_creation_date = c.report_date
GROUP BY
a.group_id
,a.resource_id
,a.channel_code
,a.cust_category
,a.lead_rank_id
,a.lead_source
,a.lead_region
,a.lead_country
,GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)
,GROUPING SETS (c.week_id,c.ent_period_id,c.ent_qtr_id,c.ent_year_id)