[Home] [Help]
MATERIALIZED VIEW: APPS.ENI_DBI_CO_UNION_MV
Source
select /* 12.0: bug#4526784 */ organization_id
, item_id
, item_org_id
, change_order_type_id
, reason_code
, priority_code
, status_type
, 'I' source_flag
, coalesce(report_date_julian, week_id, ent_period_id, ent_qtr_id, ent_year_id) time_id
, nvl2(report_date_julian, 1,
nvl2(week_id, 16,
nvl2(ent_period_id, 32,
nvl2(ent_qtr_id, 64,
nvl2(ent_year_id, 128, null)
)
)
)
) period_type_id
, ent_year_id year_id
, ent_qtr_id qtr_id
, ent_period_id month_id
, week_id week_id
, report_date_julian day_id
, count(*) cnt
, sum(cnt) implemented_sum
, count(cnt) implemented_cnt
, null cancelled_sum
, null cancelled_cnt
, null new_sum
, null new_cnt
, sum(mv.cnt*(mv.implementation_date - mv.creation_date+1)) cycle_time_sum
, count(mv.cnt*(mv.implementation_date - mv.creation_date+1)) cycle_time_cnt
, sum(mv.cnt*(mv.implementation_date - approval_date+1)) approve_to_impl_sum
, count(mv.cnt*(mv.implementation_date - approval_date+1)) approve_to_impl_cnt
, sum(mv.cnt*(mv.approval_date - mv.creation_date+1)) create_to_approve_sum
, count(mv.cnt*(mv.approval_date - mv.creation_date+1)) create_to_approve_cnt
, sum(case when ((mv.implementation_date - mv.creation_date+1) >= 0
and (mv.implementation_date - mv.creation_date+1) < 2)
then mv.cnt else 0 end) bucket1_sum
, count(case when ((mv.implementation_date - mv.creation_date+1) >= 0
and (mv.implementation_date - mv.creation_date+1) < 2)
then mv.cnt else 0 end) bucket1_cnt
, sum(case when ((mv.implementation_date - mv.creation_date+1) >= 2
and (mv.implementation_date - mv.creation_date+1) < 6)
then mv.cnt else 0 end) bucket2_sum
, count(case when ((mv.implementation_date - mv.creation_date+1) >= 2
and (mv.implementation_date - mv.creation_date+1) < 6)
then mv.cnt else 0 end) bucket2_cnt
, sum(case when ((mv.implementation_date - mv.creation_date+1) >= 6
and (mv.implementation_date - mv.creation_date+1) < 11)
then mv.cnt else 0 end) bucket3_sum
, count(case when ((mv.implementation_date - mv.creation_date+1) >= 6
and (mv.implementation_date - mv.creation_date+1) < 11)
then mv.cnt else 0 end) bucket3_cnt
, sum(case when (mv.implementation_date - mv.creation_date+1) > 10
then mv.cnt else 0 end) bucket4_sum
, count(case when (mv.implementation_date - mv.creation_date+1) > 10
then mv.cnt else 0 end) bucket4_cnt
, grouping_id
( ent_year_id
, ent_qtr_id
, ent_period_id
, week_id
, report_date_julian
, organization_id
, item_id
, item_org_id
, change_order_type_id
, reason_code
, priority_code
, status_type
)
from ENI_DBI_CO_DNUM_MV mv
, FII.FII_TIME_DAY t
where mv.implementation_date is not null
and mv.implementation_date = t.report_date
group by
organization_id
, item_id
, item_org_id
, cube
( change_order_type_id
, reason_code
, priority_code
, status_type
)
, grouping sets
( ent_year_id
, ent_qtr_id
, ent_period_id
, week_id
, report_date_julian
)
union all
select organization_id
, item_id
, item_org_id
, change_order_type_id
, reason_code
, priority_code
, status_type
, 'C' source_flag
, coalesce(report_date_julian, week_id, ent_period_id, ent_qtr_id, ent_year_id) time_id
, nvl2(report_date_julian, 1,
nvl2(week_id, 16,
nvl2(ent_period_id, 32,
nvl2(ent_qtr_id, 64,
nvl2(ent_year_id, 128, null)
)
)
)
) period_type_id
, ent_year_id year_id
, ent_qtr_id qtr_id
, ent_period_id month_id
, week_id week_id
, report_date_julian day_id
, count(*) cnt
, null implemented_sum
, null implemented_cnt
, sum(cnt) cancelled_sum
, count(cnt) cancelled_cnt
, null new_sum
, null new_cnt
, null cycle_time_sum
, null cycle_time_cnt
, null approve_to_impl_sum
, null approve_to_impl_cnt
, null create_to_approve_sum
, null create_to_approve_cnt
, null bucket1_sum
, null bucket1_cnt
, null bucket2_sum
, null bucket2_cnt
, null bucket3_sum
, null bucket3_cnt
, null bucket4_sum
, null bucket4_cnt
, grouping_id
( ent_year_id
, ent_qtr_id
, ent_period_id
, week_id
, report_date_julian
, organization_id
, item_id
, item_org_id
, change_order_type_id
, reason_code
, priority_code
, status_type
)
from ENI_DBI_CO_DNUM_MV mv
, FII.FII_TIME_DAY t
where mv.cancellation_date is not null
and mv.cancellation_date = t.report_date
group by
organization_id
, item_id
, item_org_id
, cube
( change_order_type_id
, reason_code
, priority_code
, status_type
)
, grouping sets
( ent_year_id
, ent_qtr_id
, ent_period_id
, week_id
, report_date_julian
)
union all
select organization_id
, item_id
, item_org_id
, change_order_type_id
, reason_code
, priority_code
, status_type
, 'N' source_flag
, coalesce(report_date_julian, week_id, ent_period_id, ent_qtr_id, ent_year_id) time_id
, nvl2(report_date_julian, 1,
nvl2(week_id, 16,
nvl2(ent_period_id, 32,
nvl2(ent_qtr_id, 64,
nvl2(ent_year_id, 128, null)
)
)
)
) period_type_id
, ent_year_id year_id
, ent_qtr_id qtr_id
, ent_period_id month_id
, week_id week_id
, report_date_julian day_id
, count(*) cnt
, null implemented_sum
, null implemented_cnt
, null cancelled_sum
, null cancelled_cnt
, sum(cnt) new_sum
, count(cnt) new_cnt
, null cycle_time_sum
, null cycle_time_cnt
, null approve_to_impl_sum
, null approve_to_impl_cnt
, null create_to_approve_sum
, null create_to_approve_cnt
, null bucket1_sum
, null bucket1_cnt
, null bucket2_sum
, null bucket2_cnt
, null bucket3_sum
, null bucket3_cnt
, null bucket4_sum
, null bucket4_cnt
, grouping_id
( ent_year_id
, ent_qtr_id
, ent_period_id
, week_id
, report_date_julian
, organization_id
, item_id
, item_org_id
, change_order_type_id
, reason_code
, priority_code
, status_type
)
from ENI_DBI_CO_DNUM_MV mv
, FII.FII_TIME_DAY t
where mv.creation_date = t.report_date
group by
organization_id
, item_id
, item_org_id
, cube
( change_order_type_id
, reason_code
, priority_code
, status_type
)
, grouping sets
( ent_year_id
, ent_qtr_id
, ent_period_id
, week_id
, report_date_julian
)