VIEW: APPS.MSD_SR_MFG_TIME_V

Source

View Text - Preformatted

SELECT bcd.calendar_code, nvl(bcd.seq_num, -1), 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, bps.period_name || ' ' || decode (bps.period_name, to_char(to_date('01','MM'),'MON'), decode(SIGN(bps.next_date -bps.period_start_date), 1, to_char((bps.next_date -1), 'YYYY'), to_char(bps.period_start_date, 'YYYY')), to_char(bps.period_start_date, 'YYYY')) MONTH, 'Period: ' || to_char(bps.period_start_date) month_description, bps.period_start_date month_start_date, decode(SIGN(bps.next_date -bps.period_start_date), 1, (bps.next_date -1), NULL) month_end_date, 'Week: ' || to_char(bcws.week_start_date) WEEK, 'Week: ' || to_char(bcws.week_start_date) week_description, bcws.week_start_date, decode(SIGN(bcws.next_date -bcws.week_start_date), 1, (bcws.next_date -1), NULL) week_end_date, bcd.calendar_date DAY, bcd.calendar_date day_description FROM bom_period_start_dates bps, bom_cal_week_start_dates bcws, bom_calendar_dates bcd WHERE bcd.exception_set_id = -1 AND bcws.calendar_code = bcd.calendar_code AND bcws.exception_set_id = bcd.exception_set_id AND bcd.calendar_date BETWEEN bcws.week_start_date AND decode(SIGN(bcws.next_date -bcws.week_start_date), 1, (bcws.next_date -1), NULL) AND bps.calendar_code = bcd.calendar_code AND bps.exception_set_id = bcd.exception_set_id AND bcd.calendar_date BETWEEN bps.period_start_date AND decode(SIGN(bps.next_date -bps.period_start_date), 1, (bps.next_date -1), NULL)

View Text - HTML Formatted

SELECT BCD.CALENDAR_CODE
, NVL(BCD.SEQ_NUM
, -1)
, 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
, BPS.PERIOD_NAME || ' ' || DECODE (BPS.PERIOD_NAME
, TO_CHAR(TO_DATE('01'
, 'MM')
, 'MON')
, DECODE(SIGN(BPS.NEXT_DATE -BPS.PERIOD_START_DATE)
, 1
, TO_CHAR((BPS.NEXT_DATE -1)
, 'YYYY')
, TO_CHAR(BPS.PERIOD_START_DATE
, 'YYYY'))
, TO_CHAR(BPS.PERIOD_START_DATE
, 'YYYY')) MONTH
, 'PERIOD: ' || TO_CHAR(BPS.PERIOD_START_DATE) MONTH_DESCRIPTION
, BPS.PERIOD_START_DATE MONTH_START_DATE
, DECODE(SIGN(BPS.NEXT_DATE -BPS.PERIOD_START_DATE)
, 1
, (BPS.NEXT_DATE -1)
, NULL) MONTH_END_DATE
, 'WEEK: ' || TO_CHAR(BCWS.WEEK_START_DATE) WEEK
, 'WEEK: ' || TO_CHAR(BCWS.WEEK_START_DATE) WEEK_DESCRIPTION
, BCWS.WEEK_START_DATE
, DECODE(SIGN(BCWS.NEXT_DATE -BCWS.WEEK_START_DATE)
, 1
, (BCWS.NEXT_DATE -1)
, NULL) WEEK_END_DATE
, BCD.CALENDAR_DATE DAY
, BCD.CALENDAR_DATE DAY_DESCRIPTION
FROM BOM_PERIOD_START_DATES BPS
, BOM_CAL_WEEK_START_DATES BCWS
, BOM_CALENDAR_DATES BCD
WHERE BCD.EXCEPTION_SET_ID = -1
AND BCWS.CALENDAR_CODE = BCD.CALENDAR_CODE
AND BCWS.EXCEPTION_SET_ID = BCD.EXCEPTION_SET_ID
AND BCD.CALENDAR_DATE BETWEEN BCWS.WEEK_START_DATE
AND DECODE(SIGN(BCWS.NEXT_DATE -BCWS.WEEK_START_DATE)
, 1
, (BCWS.NEXT_DATE -1)
, NULL)
AND BPS.CALENDAR_CODE = BCD.CALENDAR_CODE
AND BPS.EXCEPTION_SET_ID = BCD.EXCEPTION_SET_ID
AND BCD.CALENDAR_DATE BETWEEN BPS.PERIOD_START_DATE
AND DECODE(SIGN(BPS.NEXT_DATE -BPS.PERIOD_START_DATE)
, 1
, (BPS.NEXT_DATE -1)
, NULL)