MSC_ANALYTIC_CALENDARS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
CALENDAR_DATE CALENDAR_CODE DESCRIPTION CALENDAR_NAME CALENDAR_TYPE WEEK_START_DATE WEEK_NEXT_DATE WEEK_END_DATE PERIOD_START_DATE PERIOD_NAME PERIOD_NEXT_DATE PERIOD_LAST_DATE MONTH QUARTER YEAR MONTH_START_DATE MONTH_END_DATE QUARTER_START_DATE QUARTER_END_DATE YEAR_START_DATE YEAR_END_DATE

Query

SQL_Statement

SELECT MCD.CALENDAR_DATE, MCD.CALENDAR_CODE, MCD.Description, MC.CALENDAR_NAME, MC.CALENDAR_TYPE, MWSD.WEEK_START_DATE, MWSD.NEXT_DATE WEEK_NEXT_DATE, MWSD.WEEK_END_DATE, MPSD.PERIOD_START_DATE, MPSD.PERIOD_NAME, MPSD.NEXT_DATE PERIOD_NEXT_DATE, MPSD.PERIOD_LAST_DATE, null as MONTH, null as QUARTER, null as YEAR, null as MONTH_START_DATE, null as MONTH_END_DATE, null as QUARTER_START_DATE, null as QUARTER_END_DATE, null as YEAR_START_DATE, null as YEAR_END_DATE FROM MSC_CALENDAR_DATES MCD , MSC_CALENDARS MC , MSC_CAL_WEEK_START_DATES MWSD, MSC_Catalog_hierarchies ch , MSC_PERIOD_START_DATES MPSD WHERE MC.calendar_code =ch.hierarchy_id AND MC.calendar_code =MWSD.Calendar_code AND MC.calendar_code =MPSD.Calendar_code AND MCD.Calendar_code =MC.Calendar_code AND MCD.Calendar_code =MWSD.Calendar_code AND MCD.Calendar_code = MPSD.Calendar_code AND MCD.Calendar_code =ch.hierarchy_id AND MCD.CALENDAR_DATE >= MWSD.WEEK_START_DATE AND MCD.CALENDAR_DATE <MWSD.NEXT_DATE AND MCD.CALENDAR_DATE >= MPSD.PERIOD_START_DATE AND MCD.CALENDAR_DATE < MPSD.NEXT_DATE AND ch.catalog_id =102 union all SELECT CALENDAR_DATE, CALENDAR_CODE, Description, CALENDAR_NAME, CALENDAR_TYPE, WEEK_START_DATE, WEEK_NEXT_DATE, WEEK_END_DATE, PERIOD_START_DATE, PERIOD_NAME, PERIOD_NEXT_DATE, PERIOD_LAST_DATE, MONTH, QUARTER, YEAR, MONTH_START_DATE, MONTH_END_DATE, QUARTER_START_DATE, QUARTER_END_DATE, YEAR_START_DATE, YEAR_END_DATE FROM ( select cd.CALENDAR_DATE, MC.CALENDAR_CODE, MC.Description, MC.CALENDAR_NAME, MC.CALENDAR_TYPE, null as WEEK_START_DATE, null as WEEK_NEXT_DATE, null as WEEK_END_DATE, null as PERIOD_START_DATE, null as PERIOD_NAME, null as PERIOD_NEXT_DATE, null as PERIOD_LAST_DATE, MCM.MONTH, MCM.QUARTER, MCM.YEAR, MCM.MONTH_START_DATE, MCM.MONTH_END_DATE, MCM.QUARTER_START_DATE, MCM.QUARTER_END_DATE, MCM.YEAR_START_DATE, MCM.YEAR_END_DATE, mcm.month_end_date-mcm.month_start_date AS num_of_days from MSC_CALENDARS MC , MSC_Catalog_hierarchies ch , MSC_CALENDAR_MONTHS MCM, msc_calendar_dates cd WHERE MC.Calendar_code=MCM.Calendar_code and MC.Calendar_code=ch.hierarchy_id and mc.calendar_type =3 and ch.catalog_id=102 and cd.calendar_code = 17 and cd.calendar_date >= MCM.start_of_month and cd.calendar_date <=MCM.month_end_date )