VIEW: APPS.PA_REP_PERIOD_DATES_V

Source

View Text - Preformatted

SELECT 'GL', glper.period_year, glper.period_name, glper.start_date, glper.end_date FROM pa_implementations imp, gl_sets_of_books gl, gl_periods glper, gl_period_statuses glpersts, gl_lookups prsts, gl_date_period_map glmaps WHERE imp.set_of_books_id = gl.set_of_books_id AND gl.period_set_name = glper.period_set_name AND gl.accounted_period_type = glper.period_type AND glpersts.set_of_books_id = gl.set_of_books_id AND glpersts.period_type = glper.period_type AND glpersts.period_name = glper.period_name AND glpersts.period_year = glper.period_year AND glpersts.closing_status = prsts.lookup_code AND glmaps.period_type = glper.period_type AND glmaps.period_name = glper.period_name AND glmaps.period_set_name = glper.period_set_name AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID AND prsts.lookup_code IN('C','F','N','O','P') AND prsts.lookup_type ='CLOSING_STATUS' UNION SELECT 'PA', paperiod.period_year, paperiod.period_name, paperiod.PA_START_DATE, paperiod.PA_end_DATE FROM pa_periods_v paperiod, pa_implementations imp, gl_period_statuses glpersts WHERE glpersts.period_type = imp.pa_period_type AND imp.set_of_books_id = paperiod.set_of_books_id AND glpersts.set_of_books_id = imp.set_of_books_id AND glpersts.period_name = paperiod.period_name AND glpersts.period_year = paperiod.period_year AND glpersts.application_id = 275 UNION SELECT 'QR', glper.period_year, to_char(glper.quarter_num), min(glper.start_date), max(glper.end_date) FROM pa_implementations imp, gl_sets_of_books gl, gl_periods glper, gl_period_statuses glpersts, gl_lookups prsts, gl_date_period_map glmaps WHERE imp.set_of_books_id = gl.set_of_books_id AND gl.period_set_name = glper.period_set_name AND gl.accounted_period_type = glper.period_type AND glpersts.set_of_books_id = gl.set_of_books_id AND glpersts.period_type = glper.period_type AND glpersts.period_name = glper.period_name AND glpersts.period_year = glper.period_year AND glpersts.closing_status = prsts.lookup_code AND glmaps.period_type = glper.period_type AND glmaps.period_name = glper.period_name AND glmaps.period_set_name = glper.period_set_name AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID AND prsts.lookup_code IN('C','F','N','O','P') AND prsts.lookup_type ='CLOSING_STATUS' GROUP BY glper.period_year, glper.quarter_num UNION SELECT 'YR', glper.period_year, to_char(glper.period_year), min(glper.start_date), max(glper.end_date) FROM pa_implementations imp, gl_sets_of_books gl, gl_periods glper, gl_period_statuses glpersts, gl_lookups prsts, gl_date_period_map glmaps WHERE imp.set_of_books_id = gl.set_of_books_id AND gl.period_set_name = glper.period_set_name AND gl.accounted_period_type = glper.period_type AND glpersts.set_of_books_id = gl.set_of_books_id AND glpersts.period_type = glper.period_type AND glpersts.period_name = glper.period_name AND glpersts.period_year = glper.period_year AND glpersts.closing_status = prsts.lookup_code AND glmaps.period_type = glper.period_type AND glmaps.period_name = glper.period_name AND glmaps.period_set_name = glper.period_set_name AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID AND prsts.lookup_code IN('C','F','N','O','P') AND prsts.lookup_type ='CLOSING_STATUS' GROUP BY glper.period_year UNION SELECT 'GE', period_year, TO_CHAR((NEXT_DAY(TO_DATE('01-JAN-'||TO_CHAR(period_Year),'DD-MON-YYYY'), TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7), ((NEXT_DAY(TO_DATE('01-JAN-'||TO_CHAR(period_Year),'DD-MON-YYYY'), TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) - 6, (NEXT_DAY(TO_DATE('01-JAN-'||TO_CHAR(period_Year),'DD-MON-YYYY'), TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7 FROM pa_rep_year_cal_v, pa_rep_seq_number WHERE seq_number BETWEEN 1 AND 53

View Text - HTML Formatted

SELECT 'GL'
, GLPER.PERIOD_YEAR
, GLPER.PERIOD_NAME
, GLPER.START_DATE
, GLPER.END_DATE
FROM PA_IMPLEMENTATIONS IMP
, GL_SETS_OF_BOOKS GL
, GL_PERIODS GLPER
, GL_PERIOD_STATUSES GLPERSTS
, GL_LOOKUPS PRSTS
, GL_DATE_PERIOD_MAP GLMAPS
WHERE IMP.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GL.PERIOD_SET_NAME = GLPER.PERIOD_SET_NAME
AND GL.ACCOUNTED_PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLPERSTS.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GLPERSTS.PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLPERSTS.PERIOD_NAME = GLPER.PERIOD_NAME
AND GLPERSTS.PERIOD_YEAR = GLPER.PERIOD_YEAR
AND GLPERSTS.CLOSING_STATUS = PRSTS.LOOKUP_CODE
AND GLMAPS.PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLMAPS.PERIOD_NAME = GLPER.PERIOD_NAME
AND GLMAPS.PERIOD_SET_NAME = GLPER.PERIOD_SET_NAME
AND GLPERSTS.APPLICATION_ID = PA_PERIOD_PROCESS_PKG.APPLICATION_ID
AND PRSTS.LOOKUP_CODE IN('C'
, 'F'
, 'N'
, 'O'
, 'P')
AND PRSTS.LOOKUP_TYPE ='CLOSING_STATUS' UNION SELECT 'PA'
, PAPERIOD.PERIOD_YEAR
, PAPERIOD.PERIOD_NAME
, PAPERIOD.PA_START_DATE
, PAPERIOD.PA_END_DATE
FROM PA_PERIODS_V PAPERIOD
, PA_IMPLEMENTATIONS IMP
, GL_PERIOD_STATUSES GLPERSTS
WHERE GLPERSTS.PERIOD_TYPE = IMP.PA_PERIOD_TYPE
AND IMP.SET_OF_BOOKS_ID = PAPERIOD.SET_OF_BOOKS_ID
AND GLPERSTS.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND GLPERSTS.PERIOD_NAME = PAPERIOD.PERIOD_NAME
AND GLPERSTS.PERIOD_YEAR = PAPERIOD.PERIOD_YEAR
AND GLPERSTS.APPLICATION_ID = 275 UNION SELECT 'QR'
, GLPER.PERIOD_YEAR
, TO_CHAR(GLPER.QUARTER_NUM)
, MIN(GLPER.START_DATE)
, MAX(GLPER.END_DATE)
FROM PA_IMPLEMENTATIONS IMP
, GL_SETS_OF_BOOKS GL
, GL_PERIODS GLPER
, GL_PERIOD_STATUSES GLPERSTS
, GL_LOOKUPS PRSTS
, GL_DATE_PERIOD_MAP GLMAPS
WHERE IMP.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GL.PERIOD_SET_NAME = GLPER.PERIOD_SET_NAME
AND GL.ACCOUNTED_PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLPERSTS.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GLPERSTS.PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLPERSTS.PERIOD_NAME = GLPER.PERIOD_NAME
AND GLPERSTS.PERIOD_YEAR = GLPER.PERIOD_YEAR
AND GLPERSTS.CLOSING_STATUS = PRSTS.LOOKUP_CODE
AND GLMAPS.PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLMAPS.PERIOD_NAME = GLPER.PERIOD_NAME
AND GLMAPS.PERIOD_SET_NAME = GLPER.PERIOD_SET_NAME
AND GLPERSTS.APPLICATION_ID = PA_PERIOD_PROCESS_PKG.APPLICATION_ID
AND PRSTS.LOOKUP_CODE IN('C'
, 'F'
, 'N'
, 'O'
, 'P')
AND PRSTS.LOOKUP_TYPE ='CLOSING_STATUS' GROUP BY GLPER.PERIOD_YEAR
, GLPER.QUARTER_NUM UNION SELECT 'YR'
, GLPER.PERIOD_YEAR
, TO_CHAR(GLPER.PERIOD_YEAR)
, MIN(GLPER.START_DATE)
, MAX(GLPER.END_DATE)
FROM PA_IMPLEMENTATIONS IMP
, GL_SETS_OF_BOOKS GL
, GL_PERIODS GLPER
, GL_PERIOD_STATUSES GLPERSTS
, GL_LOOKUPS PRSTS
, GL_DATE_PERIOD_MAP GLMAPS
WHERE IMP.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GL.PERIOD_SET_NAME = GLPER.PERIOD_SET_NAME
AND GL.ACCOUNTED_PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLPERSTS.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GLPERSTS.PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLPERSTS.PERIOD_NAME = GLPER.PERIOD_NAME
AND GLPERSTS.PERIOD_YEAR = GLPER.PERIOD_YEAR
AND GLPERSTS.CLOSING_STATUS = PRSTS.LOOKUP_CODE
AND GLMAPS.PERIOD_TYPE = GLPER.PERIOD_TYPE
AND GLMAPS.PERIOD_NAME = GLPER.PERIOD_NAME
AND GLMAPS.PERIOD_SET_NAME = GLPER.PERIOD_SET_NAME
AND GLPERSTS.APPLICATION_ID = PA_PERIOD_PROCESS_PKG.APPLICATION_ID
AND PRSTS.LOOKUP_CODE IN('C'
, 'F'
, 'N'
, 'O'
, 'P')
AND PRSTS.LOOKUP_TYPE ='CLOSING_STATUS' GROUP BY GLPER.PERIOD_YEAR UNION SELECT 'GE'
, PERIOD_YEAR
, TO_CHAR((NEXT_DAY(TO_DATE('01-JAN-'||TO_CHAR(PERIOD_YEAR)
, 'DD-MON-YYYY')
, TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (SEQ_NUMBER-1) * 7)
, ((NEXT_DAY(TO_DATE('01-JAN-'||TO_CHAR(PERIOD_YEAR)
, 'DD-MON-YYYY')
, TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (SEQ_NUMBER-1) * 7) - 6
, (NEXT_DAY(TO_DATE('01-JAN-'||TO_CHAR(PERIOD_YEAR)
, 'DD-MON-YYYY')
, TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (SEQ_NUMBER-1) * 7
FROM PA_REP_YEAR_CAL_V
, PA_REP_SEQ_NUMBER
WHERE SEQ_NUMBER BETWEEN 1
AND 53