MSC_AP_445_FISCAL_CAL

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
CALENDAR_NAME SR_CALENDAR_CODE DESCRIPTION YEAR YEAR_START_DATE YEAR_END_DATE YEAR_DESCRIPTION QUARTER QUARTER_SEQ_NUM QUARTER_START_DATE MONTH MONTH_START_DATE MONTH_END_DATE

Name

CALENDAR_NAME

SR_CALENDAR_CODE

DESCRIPTION

YEAR

YEAR_START_DATE

YEAR_END_DATE

YEAR_DESCRIPTION

QUARTER

QUARTER_SEQ_NUM

QUARTER_START_DATE

MONTH

MONTH_START_DATE

MONTH_END_DATE

Query

SQL_Statement
SELECT PR1.USER_PERIOD_SET_NAME CALENDAR_NAME, PR1.PERIOD_SET_NAME \|\|' '\|\|PR1.PERIOD_TYPE SR_CALENDAR_CODE, PR1.DESCRIPTION DESCRIPTION, PR2.PERIOD_YEAR YEAR, PR2.YEAR_START_DATE YEAR_START_DATE, (Select end_date from gl_periods PR3 where PR3.period_set_name = PR2.period_set_name and PR3.period_year = PR2.period_year and PR3.period_type = PR2.period_type and PR3.adjustment_period_flag = 'N' and PR3.period_num = (select max(period_num) from gl_periods PR4, GL_CALENDARS PR5 where PR4.period_set_name = PR2.period_set_name and PR4.period_year = PR2.period_year and PR4.period_type = PR2.period_type and PR4.adjustment_period_flag = 'N' and PR4.period_set_name = PR5.period_set_name AND PR4.period_type = PR5.period_type AND PR5.CALENDAR_TYPE_CODE = 'ACCOUNTING' AND PR5.NON_ADJ_PERIOD_FREQ_CODE in ('MONTH', '4_4_5', '4_5_4', '5_4_4') )) YEAR_END_DATE, 'FY-'\|\|TO_CHAR(PR2.YEAR_START_DATE,'YY') YEAR_DESCRIPTION, PR2.PERIOD_YEAR\|\|' Q'\|\|PR2.QUARTER_NUM QUARTER, PR2.QUARTER_NUM QUARTER_SEQ_NUM, PR2.QUARTER_START_DATE QUARTER_START_DATE, PR2.PERIOD_NAME MONTH, PR2.START_DATE MONTH_START_DATE, PR2.END_DATE MONTH_END_DATE FROM GL_PERIODS PR2, GL_CALENDARS PR1 WHERE PR2.PERIOD_SET_NAME = PR1.PERIOD_SET_NAME AND PR2.PERIOD_TYPE = PR1.PERIOD_TYPE AND PR1.CALENDAR_TYPE_CODE = 'ACCOUNTING' AND PR1.NON_ADJ_PERIOD_FREQ_CODE in ('MONTH', '4_4_5', '4_5_4', '5_4_4') AND PR2.YEAR_START_DATE IS NOT NULL AND PR2.START_DATE IS NOT NULL AND PR2.ADJUSTMENT_PERIOD_FLAG = 'N'

SQL_Statement

SELECT

PR1.USER_PERIOD_SET_NAME CALENDAR_NAME,

PR1.PERIOD_SET_NAME ||' '||PR1.PERIOD_TYPE SR_CALENDAR_CODE,

PR1.DESCRIPTION DESCRIPTION,

PR2.PERIOD_YEAR YEAR,

PR2.YEAR_START_DATE YEAR_START_DATE,

(Select end_date

from gl_periods PR3

where PR3.period_set_name = PR2.period_set_name

and PR3.period_year = PR2.period_year

and PR3.period_type = PR2.period_type

and PR3.adjustment_period_flag = 'N'

and PR3.period_num = (select max(period_num)

from gl_periods PR4, GL_CALENDARS PR5

where PR4.period_set_name = PR2.period_set_name

and PR4.period_year = PR2.period_year

and PR4.period_type = PR2.period_type

and PR4.adjustment_period_flag = 'N'

and PR4.period_set_name = PR5.period_set_name

AND PR4.period_type = PR5.period_type

AND PR5.CALENDAR_TYPE_CODE = 'ACCOUNTING'

AND PR5.NON_ADJ_PERIOD_FREQ_CODE in ('MONTH', '4_4_5', '4_5_4', '5_4_4')

)) YEAR_END_DATE,

'FY-'||TO_CHAR(PR2.YEAR_START_DATE,'YY') YEAR_DESCRIPTION,

PR2.PERIOD_YEAR||' Q'||PR2.QUARTER_NUM QUARTER,

PR2.QUARTER_NUM QUARTER_SEQ_NUM,

PR2.QUARTER_START_DATE QUARTER_START_DATE,

PR2.PERIOD_NAME MONTH,

PR2.START_DATE MONTH_START_DATE,

PR2.END_DATE MONTH_END_DATE

FROM GL_PERIODS PR2,

GL_CALENDARS PR1

WHERE PR2.PERIOD_SET_NAME = PR1.PERIOD_SET_NAME

AND PR2.PERIOD_TYPE = PR1.PERIOD_TYPE

AND PR1.CALENDAR_TYPE_CODE = 'ACCOUNTING'

AND PR1.NON_ADJ_PERIOD_FREQ_CODE in ('MONTH', '4_4_5', '4_5_4', '5_4_4')

AND PR2.YEAR_START_DATE IS NOT NULL

AND PR2.START_DATE IS NOT NULL

AND PR2.ADJUSTMENT_PERIOD_FLAG = 'N'