eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
[Home] [Help]
MATERIALIZED VIEW: APPS.PJI_TIME_MV

Source


SELECT /* 12.0: bug#4526784 */
       period_name,
       quarter_name,
	   year,
	   period_type,
	   calendar_id,
	   period_id,
	   quarter_id,
	   year_id,
	   ROWNUM SEQUENCE,
	   period_start_date,
	   period_end_date,
	   TO_NUMBER(TO_CHAR(period_start_date,'J')) start_date_time,
	   TO_NUMBER(TO_CHAR(period_end_date,'J')) end_date_time,
	   quarter_start_date,
	   quarter_end_date,
	   year_start_date,
	   year_end_date
FROM (
SELECT * FROM (
SELECT yr.name period_name,
       yr.name quarter_name,
	   yr.name year,
	   'FII_TIME_ENT_YEAR' period_type,
	   -1 calendar_id,
	   yr.ent_year_id period_id,
	   yr.ent_year_id quarter_id,
	   yr.ent_year_id year_id,
	   yr.start_date period_start_date,
	   yr.end_date period_end_date,
	   yr.start_date quarter_start_date,
	   yr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_ENT_YEAR yr
UNION ALL
SELECT qtr.name period_name,
       qtr.name quarter_name,
	   yr.name year,
	   'FII_TIME_ENT_QTR' period_type,
	   -1 calendar_id,
	   qtr.ent_qtr_id period_id,
	   qtr.ent_qtr_id quarter_id,
	   yr.ENT_YEAR_ID year_id,
	   qtr.start_date period_start_date,
	   qtr.end_date period_end_date,
	   qtr.start_date quarter_start_date,
	   qtr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_ENT_QTR qtr,
       FII.FII_TIME_ENT_YEAR yr
WHERE  qtr.ent_year_id = yr.ent_year_id
UNION ALL
SELECT pd.name period_name,
       qtr.name quarter_name,
	   yr.name year,
	   'FII_TIME_ENT_PERIOD' period_type,
	   -1 calendar_id,
	   pd.ent_period_id period_id,
	   qtr.ent_qtr_id quarter_id,
	   yr.ent_year_id year_id,
	   pd.start_date period_start_date,
	   pd.end_date period_end_date,
	   qtr.start_date quarter_start_date,
	   qtr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_ENT_PERIOD pd,
       FII.FII_TIME_ENT_QTR qtr,
       FII.FII_TIME_ENT_YEAR yr
WHERE  pd.ent_qtr_id  = qtr.ent_qtr_id
AND    pd.ent_year_id = yr.ent_year_id
UNION ALL
SELECT wk.name period_name,
       NULL quarter_name,
	   NULL year,
	   'FII_TIME_WEEK' period_type,
	   -1 calendar_id,
	   wk.week_id period_id,
	   -1 quarter_id,
	   -1 year_id,
	   wk.start_date period_start_date,
	   wk.end_date period_end_date,
	   NULL quarter_start_date,
	   NULL quarter_end_date,
	   NULL year_start_date,
	   NULL year_end_date
FROM   FII.FII_TIME_WEEK wk
UNION ALL
SELECT yr.name period_name,
       yr.name quarter_name,
	   yr.name year,
	   'FII_TIME_CAL_YEAR' period_type,
	   yr.calendar_id calendar_id,
	   yr.cal_year_id period_id,
	   yr.cal_year_id quarter_id,
	   yr.cal_year_id year_id,
	   yr.start_date period_start_date,
	   yr.end_date period_end_date,
	   yr.start_date quarter_start_date,
	   yr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_CAL_YEAR yr
WHERE  yr.calendar_id  in ( select distinct gl_calendar_id from PJI.PJI_ORG_EXTR_INFO)
UNION ALL
SELECT qtr.name period_name,
       qtr.name quarter_name,
	   yr.name year,
	   'FII_TIME_CAL_QTR' period_type,
	   qtr.calendar_id calendar_id,
	   qtr.cal_qtr_id period_id,
	   qtr.cal_qtr_id quarter_id,
	   yr.cal_year_id year_id,
	   qtr.start_date period_start_date,
	   qtr.end_date period_end_date,
	   qtr.start_date quarter_start_date,
	   qtr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_CAL_QTR qtr,
       FII.FII_TIME_CAL_YEAR yr
WHERE  qtr.cal_year_id = yr.cal_year_id
AND    qtr.calendar_id  in ( select distinct gl_calendar_id from PJI.PJI_ORG_EXTR_INFO)
UNION ALL
SELECT pd.name period_name,
       qtr.name quarter_name,
	   yr.name year,
	   'FII_TIME_CAL_PERIOD' period_type,
	   pd.calendar_id calendar_id,
	   pd.cal_period_id period_id,
	   qtr.cal_qtr_id quarter_id,
	   qtr.cal_year_id year_id,
	   pd.start_date period_start_date,
	   pd.end_date period_end_date,
	   qtr.start_date quarter_start_date,
	   qtr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_CAL_PERIOD pd,
       FII.FII_TIME_CAL_QTR qtr,
       FII.FII_TIME_CAL_YEAR yr
WHERE  pd.cal_qtr_id  = qtr.cal_qtr_id
AND    qtr.cal_year_id = yr.cal_year_id
AND    pd.calendar_id  in ( select distinct gl_calendar_id from PJI.PJI_ORG_EXTR_INFO)
UNION ALL
SELECT pd.name period_name,
       qtr.name quarter_name,
	   yr.name year,
	   'FII_TIME_PA_PERIOD' period_type,
	   pd.calendar_id calendar_id,
	   pd.cal_period_id period_id,
	   qtr.cal_qtr_id quarter_id,
	   qtr.cal_year_id year_id,
	   pd.start_date period_start_date,
	   pd.end_date period_end_date,
	   qtr.start_date quarter_start_date,
	   qtr.end_date quarter_end_date,
	   yr.start_date year_start_date,
	   yr.end_date year_end_date
FROM   FII.FII_TIME_CAL_PERIOD pd,
       FII.FII_TIME_CAL_QTR qtr,
       FII.FII_TIME_CAL_YEAR yr
WHERE  pd.cal_qtr_id  = qtr.cal_qtr_id
AND    qtr.cal_year_id = yr.cal_year_id
AND    pd.calendar_id  in ( select distinct pa_calendar_id from PJI.PJI_ORG_EXTR_INFO)
)
ORDER BY period_type, period_id)