[Home] [Help]
MATERIALIZED VIEW: APPS.BIV_RES_SUM_MV
Source
select /* 12.0: bug#4526784 */
decode(grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, c.report_date_julian
, 1, c.week_id
, 3, c.ent_period_id
, 7, c.ent_qtr_id
, 15, c.ent_year_id) time_id
, decode(grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, 1
, 1, 16
, 3, 32
, 7, 64
, 15, 128) period_type_id
, decode( grouping_id( nvl(pc.master_id,pc.id)
, f.customer_id
, f.owner_group_id
, f.sr_creation_channel
, f.resolution_code) , 30, 6 -- Resolution
, 29, 5 -- Channel
, 27, 3 -- Assignment Group
, 23, 2 -- Customer
, 15, 1 -- Prod
, 0 ) grp_id
, f.incident_type_id
, f.incident_severity_id
, pc.vbh_category_id
, nvl(pc.master_id,pc.id) product_id
, f.customer_id
, f.owner_group_id
, f.sr_creation_channel
, f.resolution_code
, count(*) resolution_count
, sum(f.time_to_resolution) total_time_to_resolution
, sum(case
when (b.range1_low is null or f.time_to_resolution >= b.range1_low) and
(b.range1_high is null or f.time_to_resolution < b.range1_high) then
1 else 0
end) time_to_resolution_b1
, sum(case
when (f.time_to_resolution >= b.range2_low) and
(b.range2_high is null or f.time_to_resolution < b.range2_high) then
1 else 0
end) time_to_resolution_b2
, sum(case
when (f.time_to_resolution >= b.range3_low) and
(b.range3_high is null or f.time_to_resolution < b.range3_high) then
1 else 0
end) time_to_resolution_b3
, sum(case
when (f.time_to_resolution >= b.range4_low) and
(b.range4_high is null or f.time_to_resolution < b.range4_high) then
1 else 0
end) time_to_resolution_b4
, sum(case
when (f.time_to_resolution >= b.range5_low) and
(b.range5_high is null or f.time_to_resolution < b.range5_high) then
1 else 0
end) time_to_resolution_b5
, sum(case
when (f.time_to_resolution >= b.range6_low) and
(b.range6_high is null or f.time_to_resolution < b.range6_high) then
1 else 0
end) time_to_resolution_b6
, sum(case
when (f.time_to_resolution >= b.range7_low) and
(b.range7_high is null or f.time_to_resolution < b.range7_high) then
1 else 0
end) time_to_resolution_b7
, sum(case
when (f.time_to_resolution >= b.range8_low) and
(b.range8_high is null or f.time_to_resolution < b.range8_high) then
1 else 0
end) time_to_resolution_b8
, sum(case
when (f.time_to_resolution >= b.range9_low) and
(b.range9_high is null or f.time_to_resolution < b.range9_high) then
1 else 0
end) time_to_resolution_b9
, sum(case
when (f.time_to_resolution >= b.range10_low) and
(b.range10_high is null or f.time_to_resolution < b.range10_high) then
1 else 0
end) time_to_resolution_b10
/* the following columns prefixed with mv_ exist solely to enable
MV to be fast refreshable */
, grouping_id( f.incident_type_id
, nvl(pc.master_id,pc.id)
, pc.vbh_category_id
, f.incident_severity_id
, f.customer_id
, f.owner_group_id
, f.sr_creation_channel
, f.resolution_code
, c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ) mv_grouping_id
, c.report_date_julian mv_day_id
, c.week_id mv_week_id
, c.ent_period_id mv_period_id
, c.ent_qtr_id mv_ent_qtr_id
, c.ent_year_id mv_ent_year_id
, count(f.time_to_resolution) mv_total_time_to_resolution
, count(case
when (b.range1_low is null or f.time_to_resolution >= b.range1_low) and
(b.range1_high is null or f.time_to_resolution < b.range1_high) then
1 else 0
end) mv_time_to_resolution_b1
, count(case
when (f.time_to_resolution >= b.range2_low) and
(b.range2_high is null or f.time_to_resolution < b.range2_high) then
1 else 0
end) mv_time_to_resolution_b2
, count(case
when (f.time_to_resolution >= b.range3_low) and
(b.range3_high is null or f.time_to_resolution < b.range3_high) then
1 else 0
end) mv_time_to_resolution_b3
, count(case
when (f.time_to_resolution >= b.range4_low) and
(b.range4_high is null or f.time_to_resolution < b.range4_high) then
1 else 0
end) mv_time_to_resolution_b4
, count(case
when (f.time_to_resolution >= b.range5_low) and
(b.range5_high is null or f.time_to_resolution < b.range5_high) then
1 else 0
end) mv_time_to_resolution_b5
, count(case
when (f.time_to_resolution >= b.range6_low) and
(b.range6_high is null or f.time_to_resolution < b.range6_high) then
1 else 0
end) mv_time_to_resolution_b6
, count(case
when (f.time_to_resolution >= b.range7_low) and
(b.range7_high is null or f.time_to_resolution < b.range7_high) then
1 else 0
end) mv_time_to_resolution_b7
, count(case
when (f.time_to_resolution >= b.range8_low) and
(b.range8_high is null or f.time_to_resolution < b.range8_high) then
1 else 0
end) mv_time_to_resolution_b8
, count(case
when (f.time_to_resolution >= b.range9_low) and
(b.range9_high is null or f.time_to_resolution < b.range9_high) then
1 else 0
end) mv_time_to_resolution_b9
, count(case
when (f.time_to_resolution >= b.range10_low) and
(b.range10_high is null or f.time_to_resolution < b.range10_high) then
1 else 0
end) mv_time_to_resolution_b10
, count(*) mv_count
/* end of fast refreshable needed columns */
from
BIV.BIV_DBI_RESOLUTION_SUM_F f
, FII.FII_TIME_DAY c
, ENI.ENI_OLTP_ITEM_STAR pc
, BIS.BIS_BUCKET bb
, BIS.BIS_BUCKET_CUSTOMIZATIONS b
where
trunc(f.report_date) = c.report_date
and f.inventory_item_id = pc.inventory_item_id
and f.inv_organization_id = pc.organization_id
and bb.short_name = 'BIV_DBI_RESOLUTION_CYCLE_TIME'
and bb.bucket_id = b.bucket_id
and f.time_to_resolution >= 0
group by
grouping sets ( (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id, c.report_date_julian)
, (c.ent_year_id, c.ent_qtr_id, c.ent_period_id, c.week_id)
, (c.ent_year_id, c.ent_qtr_id, c.ent_period_id)
, (c.ent_year_id, c.ent_qtr_id)
, (c.ent_year_id) )
, f.incident_type_id
, f.incident_severity_id
, pc.vbh_category_id
, grouping sets ( (nvl(pc.master_id,pc.id))
, (f.customer_id)
, (f.owner_group_id)
, (f.sr_creation_channel)
, (f.resolution_code)
, (nvl(pc.master_id,pc.id),f.customer_id,f.owner_group_id,f.sr_creation_channel,f.resolution_code)
)