select /* 12.0: bug#4526784 */
'Enterprise Year :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_ENT_YEAR'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 2 and b.sequence + 2
union all
select 'Enterprise Quarter :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_ENT_QTR'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 4 and b.sequence + 4
union all
select 'Enterprise Period :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_ENT_PERIOD'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 12 and b.sequence + 12
union all
select 'Enterprise Week :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_WEEK'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 13 and b.sequence + 13
union all
select 'Fiscal Year :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_CAL_YEAR'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 2 and b.sequence + 2
union all
select 'Fiscal Quarter :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_CAL_QTR'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 4 and b.sequence + 4
union all
select 'Fiscal Period :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_CAL_PERIOD'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 12 and b.sequence + 12
union all
select 'PA Period :'||b.period_name reporting_period,
b.period_id reporting_period_id,
a.period_name,
a.period_id,
a.sequence - b.sequence sequence,
a.period_type,
a.period_start_date,
b.period_name original_reporting_period
from
pji_time_mv a,
pji_time_mv b
where
a.period_type = b.period_type
and a.period_type = 'FII_TIME_PA_PERIOD'
and a.calendar_id = b.calendar_id
and a.sequence between b.sequence - 12 and b.sequence + 12