select /* 12.0: bug#4526784 */
c.report_date
, grouping_id( e.vbh_category_id
, nvl(e.master_id,e.id)
, f.customer_id ) grp_id
, f.act_bac_district_id parent_district_id
, f.act_bac_assignee_type record_type
, f.act_bac_assignee_id district_id
, to_char(f.act_bac_assignee_id) || '.' || f.act_bac_district_id district_id_c
, f.task_type_id
, f.customer_id
, nvl(e.master_id,e.id) product_id
, e.vbh_category_id
, count(*) backlog_count
, sum(greatest(0,c.aging_date-nvl(f.planned_start_date,c.aging_date))) total_backlog_age
, sum(case
when f.planned_start_date is null then
1
when (b.range1_low is null or
f.planned_start_date > c.aging_date or
c.aging_date-f.planned_start_date >= b.range1_low) and
(b.range1_high is null or
c.aging_date-f.planned_start_date < b.range1_high) then
1 else 0
end) backlog_age_b1
, sum(case
when c.aging_date-f.planned_start_date >= b.range2_low and
(b.range2_high is null or
c.aging_date-f.planned_start_date < b.range2_high) then
1 else 0
end) backlog_age_b2
, sum(case
when c.aging_date-f.planned_start_date >= b.range3_low and
(b.range3_high is null or
c.aging_date-f.planned_start_date < b.range3_high) then
1 else 0
end) backlog_age_b3
, sum(case
when c.aging_date-f.planned_start_date >= b.range4_low and
(b.range4_high is null or
c.aging_date-f.planned_start_date < b.range4_high) then
1 else 0
end) backlog_age_b4
, sum(case
when c.aging_date-f.planned_start_date >= b.range5_low and
(b.range5_high is null or
c.aging_date-f.planned_start_date < b.range5_high) then
1 else 0
end) backlog_age_b5
, sum(case
when c.aging_date-f.planned_start_date >= b.range6_low and
(b.range6_high is null or
c.aging_date-f.planned_start_date < b.range6_high) then
1 else 0
end) backlog_age_b6
, sum(case
when c.aging_date-f.planned_start_date >= b.range7_low and
(b.range7_high is null or
c.aging_date-f.planned_start_date < b.range7_high) then
1 else 0
end) backlog_age_b7
, sum(case
when c.aging_date-f.planned_start_date >= b.range8_low and
(b.range8_high is null or
c.aging_date-f.planned_start_date < b.range8_high) then
1 else 0
end) backlog_age_b8
, sum(case
when c.aging_date-f.planned_start_date >= b.range9_low and
(b.range9_high is null or
c.aging_date-f.planned_start_date < b.range9_high) then
1 else 0
end) backlog_age_b9
, sum(case
when c.aging_date-f.planned_start_date >= b.range10_low and
(b.range10_high is null or
c.aging_date-f.planned_start_date < b.range10_high) then
1 else 0
end) backlog_age_b10
/* the following columns prefixed with mv_ exist solely to enable
MV to be fast refreshable */
, grouping_id( c.report_date
, f.act_bac_district_id
, f.act_bac_assignee_type
, f.act_bac_assignee_id
, to_char(f.act_bac_assignee_id) || '.' || f.act_bac_district_id
, f.task_type_id
, f.customer_id
, nvl(e.master_id,e.id)
, e.vbh_category_id
) mv_grp_id
, count(*) mv_count
, count(greatest(0,c.aging_date-nvl(f.planned_start_date,c.aging_date))) mv_total_backlog_age
, count(case
when f.planned_start_date is null then
1
when (b.range1_low is null or
f.planned_start_date > c.aging_date or
c.aging_date-f.planned_start_date >= b.range1_low) and
(b.range1_high is null or
c.aging_date-f.planned_start_date < b.range1_high) then
1 else 0
end) mv_backlog_age_b1
, count(case
when c.aging_date-f.planned_start_date >= b.range2_low and
(b.range2_high is null or
c.aging_date-f.planned_start_date < b.range2_high) then
1 else 0
end) mv_backlog_age_b2
, count(case
when c.aging_date-f.planned_start_date >= b.range3_low and
(b.range3_high is null or
c.aging_date-f.planned_start_date < b.range3_high) then
1 else 0
end) mv_backlog_age_b3
, count(case
when c.aging_date-f.planned_start_date >= b.range4_low and
(b.range4_high is null or
c.aging_date-f.planned_start_date < b.range4_high) then
1 else 0
end) mv_backlog_age_b4
, count(case
when c.aging_date-f.planned_start_date >= b.range5_low and
(b.range5_high is null or
c.aging_date-f.planned_start_date < b.range5_high) then
1 else 0
end) mv_backlog_age_b5
, count(case
when c.aging_date-f.planned_start_date >= b.range6_low and
(b.range6_high is null or
c.aging_date-f.planned_start_date < b.range6_high) then
1 else 0
end) mv_backlog_age_b6
, count(case
when c.aging_date-f.planned_start_date >= b.range7_low and
(b.range7_high is null or
c.aging_date-f.planned_start_date < b.range7_high) then
1 else 0
end) mv_backlog_age_b7
, count(case
when c.aging_date-f.planned_start_date >= b.range8_low and
(b.range8_high is null or
c.aging_date-f.planned_start_date < b.range8_high) then
1 else 0
end) mv_backlog_age_b8
, count(case
when c.aging_date-f.planned_start_date >= b.range9_low and
(b.range9_high is null or
c.aging_date-f.planned_start_date < b.range9_high) then
1 else 0
end) mv_backlog_age_b9
, count(case
when c.aging_date-f.planned_start_date >= b.range10_low and
(b.range10_high is null or
c.aging_date-f.planned_start_date < b.range10_high) then
1 else 0
end) mv_backlog_age_b10
/* end of fast refreshable needed columns */
from
ISC.ISC_FS_TASK_BACKLOG_F f
, ISC.ISC_FS_TASK_BAC_DATES_F c
, ENI.ENI_OLTP_ITEM_STAR e
, BIS.BIS_BUCKET bb
, BIS.BIS_BUCKET_CUSTOMIZATIONS b
where
c.report_date between f.backlog_date_from
and f.backlog_date_to
and f.inventory_item_id = e.inventory_item_id
and f.inv_organization_id = e.organization_id
and f.task_type_rule = 'DISPATCH'
and f.source_object_type_code = 'SR'
and f.deleted_flag = 'N'
and bb.short_name = 'BIV_FS_BACKLOG_AGING'
and bb.bucket_id = b.bucket_id
group by
c.report_date
, f.act_bac_district_id
, f.act_bac_assignee_type
, f.act_bac_assignee_id
, to_char(f.act_bac_assignee_id) || '.' || f.act_bac_district_id
, f.task_type_id
, rollup( e.vbh_category_id
, nvl(e.master_id,e.id)
, f.customer_id
)
[Home] [Help]