VIEW: APPS.MSD_SR_TIME_LB_V

Source

View Text - Preformatted

SELECT CALENDAR_TYPE, CALENDAR_CODE, SEQ_NUM, YEAR , YEAR_DESCRIPTION, YEAR_START_DATE, YEAR_END_DATE, QUARTER, QUARTER_DESCRIPTION, QUARTER_START_DATE, QUARTER_END_DATE, MONTH, MONTH_DESCRIPTION, MONTH_START_DATE, MONTH_END_DATE, WEEK , WEEK_DESCRIPTION, WEEK_START_DATE, WEEK_END_DATE, DAY, DAY_DESCRIPTION, WORKING_DAY from ( SELECT 2 CALENDAR_TYPE , mcd.calendar_code CALENDAR_CODE, nvl(mcd.seq_num,-1) SEQ_NUM, NULL YEAR, NULL YEAR_DESCRIPTION, NULL YEAR_START_DATE, NULL YEAR_END_DATE, NULL QUARTER, NULL QUARTER_DESCRIPTION, NULL QUARTER_START_DATE, NULL QUARTER_END_DATE, mps.period_name ||' '|| to_char(mps.period_start_date, 'YYYY') MONTH, 'Period: '|| to_char(mps.period_start_date) MONTH_DESCRIPTION, mps.period_start_date MONTH_START_DATE, decode(Sign(mps.next_date-mps.period_start_date),1, (mps.next_date-1), NULL ) MONTH_END_DATE, 'Week: ' || to_char(mcws.week_start_date) WEEK, 'Week: ' || to_char(mcws.week_start_date) WEEK_DESCRIPTION, mcws.week_start_date, decode(Sign(mcws.next_date-mcws.week_start_date), 1, (mcws.next_date-1),NULL ) week_end_date, mcd.calendar_date day, TO_CHAR(mcd.calendar_date ,'DD-MON-YYYY') day_description , null WORKING_DAY FROM msc_period_start_dates mps, msc_cal_week_start_dates mcws, msc_calendar_dates mcd WHERE mcd.exception_set_id = -1 AND mcws.calendar_code = mcd.calendar_code AND mcws.exception_set_id = mcd.exception_set_id AND mcd.calendar_date BETWEEN mcws.week_start_date AND Decode( Sign(mcws.next_date-mcws.week_start_date), 1, (mcws.next_date-1), null) AND mps.calendar_code = mcd.calendar_code AND mps.exception_set_id = mcd.exception_set_id AND mcd.calendar_date BETWEEN mps.period_start_date AND Decode( Sign(mps.next_date-mps.period_start_date), 1, (mps.next_date-1), null) and mcd.sr_instance_id = mps.sr_instance_id and mcws.sr_instance_id = mps.sr_instance_id order by mcd.calendar_code ,mcd.calendar_date )

View Text - HTML Formatted

SELECT CALENDAR_TYPE
, CALENDAR_CODE
, SEQ_NUM
, YEAR
, YEAR_DESCRIPTION
, YEAR_START_DATE
, YEAR_END_DATE
, QUARTER
, QUARTER_DESCRIPTION
, QUARTER_START_DATE
, QUARTER_END_DATE
, MONTH
, MONTH_DESCRIPTION
, MONTH_START_DATE
, MONTH_END_DATE
, WEEK
, WEEK_DESCRIPTION
, WEEK_START_DATE
, WEEK_END_DATE
, DAY
, DAY_DESCRIPTION
, WORKING_DAY
FROM ( SELECT 2 CALENDAR_TYPE
, MCD.CALENDAR_CODE CALENDAR_CODE
, NVL(MCD.SEQ_NUM
, -1) SEQ_NUM
, NULL YEAR
, NULL YEAR_DESCRIPTION
, NULL YEAR_START_DATE
, NULL YEAR_END_DATE
, NULL QUARTER
, NULL QUARTER_DESCRIPTION
, NULL QUARTER_START_DATE
, NULL QUARTER_END_DATE
, MPS.PERIOD_NAME ||' '|| TO_CHAR(MPS.PERIOD_START_DATE
, 'YYYY') MONTH
, 'PERIOD: '|| TO_CHAR(MPS.PERIOD_START_DATE) MONTH_DESCRIPTION
, MPS.PERIOD_START_DATE MONTH_START_DATE
, DECODE(SIGN(MPS.NEXT_DATE-MPS.PERIOD_START_DATE)
, 1
, (MPS.NEXT_DATE-1)
, NULL ) MONTH_END_DATE
, 'WEEK: ' || TO_CHAR(MCWS.WEEK_START_DATE) WEEK
, 'WEEK: ' || TO_CHAR(MCWS.WEEK_START_DATE) WEEK_DESCRIPTION
, MCWS.WEEK_START_DATE
, DECODE(SIGN(MCWS.NEXT_DATE-MCWS.WEEK_START_DATE)
, 1
, (MCWS.NEXT_DATE-1)
, NULL ) WEEK_END_DATE
, MCD.CALENDAR_DATE DAY
, TO_CHAR(MCD.CALENDAR_DATE
, 'DD-MON-YYYY') DAY_DESCRIPTION
, NULL WORKING_DAY
FROM MSC_PERIOD_START_DATES MPS
, MSC_CAL_WEEK_START_DATES MCWS
, MSC_CALENDAR_DATES MCD
WHERE MCD.EXCEPTION_SET_ID = -1
AND MCWS.CALENDAR_CODE = MCD.CALENDAR_CODE
AND MCWS.EXCEPTION_SET_ID = MCD.EXCEPTION_SET_ID
AND MCD.CALENDAR_DATE BETWEEN MCWS.WEEK_START_DATE
AND DECODE( SIGN(MCWS.NEXT_DATE-MCWS.WEEK_START_DATE)
, 1
, (MCWS.NEXT_DATE-1)
, NULL)
AND MPS.CALENDAR_CODE = MCD.CALENDAR_CODE
AND MPS.EXCEPTION_SET_ID = MCD.EXCEPTION_SET_ID
AND MCD.CALENDAR_DATE BETWEEN MPS.PERIOD_START_DATE
AND DECODE( SIGN(MPS.NEXT_DATE-MPS.PERIOD_START_DATE)
, 1
, (MPS.NEXT_DATE-1)
, NULL)
AND MCD.SR_INSTANCE_ID = MPS.SR_INSTANCE_ID
AND MCWS.SR_INSTANCE_ID = MPS.SR_INSTANCE_ID ORDER BY MCD.CALENDAR_CODE
, MCD.CALENDAR_DATE )