VIEW: APPS.MSC_PHUB_DATES_MV#

Source

View Text - Preformatted

with mfg as (         select distinct             md.calendar_date,             md.seq_num mfg_seq_num,             mw.week_start_date mfg_week_start_date,             mw.next_date-1 mfg_week_end_date,             mp.period_start_date mfg_period_start_date,             mp.next_date-1 mfg_period_end_date,             mp.period_name mfg_period_name         from msc_calendar_dates md, msc_cal_week_start_dates mw, msc_period_start_dates mp         where md.calendar_code=(select cal_code from msc_apcc_config where rownum=1)             and md.calendar_code=mp.calendar_code             and md.sr_instance_id=mp.sr_instance_id             and md.exception_set_id=mp.exception_set_id             and md.calendar_code=mw.calendar_code             and md.sr_instance_id=mw.sr_instance_id             and md.exception_set_id=mw.exception_set_id             and md.calendar_date between mw.week_start_date and mw.next_date-1             and md.calendar_date between mp.period_start_date and mp.next_date-1             and md.exception_set_id=-1),         fis as (         select             fp.period_set_name fis_period_set_name,             fp.period_num fis_period_num,             fp.period_name fis_period_name,             fp.description fis_period_description,             fp.start_date fis_period_start_date,             fp.end_date fis_period_end_date,             fp.quarter_num fis_quarter_num,             'Q'||fp.quarter_num fis_quarter_name,             fp.quarter_start_date fis_quarter_start_date,             fp.period_year fis_year,             fp.year_start_date fis_year_start_date         from msc_trading_partners o, msc_bis_periods fp         where fp.sr_instance_id=o.sr_instance_id             and fp.organization_id=o.sr_tp_id             and o.partner_type=3             and o.organization_code||':'||period_set_name=(select period_set_name from msc_apcc_config where rownum=1)             and nvl(fp.adjustment_period_flag,'N')='N')     select         d.calendar_date,         trunc(d.calendar_date, 'MM') month_start_date,         trunc(add_months(d.calendar_date, 1), 'MM') - 1 month_end_date,         trunc(d.calendar_date, 'YYYY') year_start_date,         to_char(d.calendar_date, 'MON') month_name,         to_char(d.calendar_date, 'YYYY') year_name,         mfg_seq_num,         mfg_week_start_date,         mfg_week_end_date,         mfg_period_start_date,         mfg_period_end_date,         mfg_period_name,         fis_period_set_name,         fis_period_num,         fis_period_name,         fis_period_description,         fis_period_start_date,         fis_period_end_date,         fis_quarter_num,         fis_quarter_name,         fis_quarter_start_date,         fis_year,         fis_year_start_date     from         (select min_date+level-1 calendar_date         from             (select min(min_date) min_date, max(max_date) max_date             from                 (select                     min(mfg_week_start_date) min_date,                     max(mfg_week_end_date) max_date                 from mfg                 union                 select                     min(fis_period_start_date) min_date,                     max(fis_period_end_date) max_date                 from fis                 )             )         connect by level <= max_date-min_date+1) d, mfg, fis     where d.calendar_date=mfg.calendar_date(+)         and d.calendar_date between fis.fis_period_start_date(+) and fis.fis_period_end_date(+)

View Text - HTML Formatted

WITH MFG AS ( SELECT DISTINCT MD.CALENDAR_DATE
, MD.SEQ_NUM MFG_SEQ_NUM
, MW.WEEK_START_DATE MFG_WEEK_START_DATE
, MW.NEXT_DATE-1 MFG_WEEK_END_DATE
, MP.PERIOD_START_DATE MFG_PERIOD_START_DATE
, MP.NEXT_DATE-1 MFG_PERIOD_END_DATE
, MP.PERIOD_NAME MFG_PERIOD_NAME
FROM MSC_CALENDAR_DATES MD
, MSC_CAL_WEEK_START_DATES MW
, MSC_PERIOD_START_DATES MP
WHERE MD.CALENDAR_CODE=(SELECT CAL_CODE
FROM MSC_APCC_CONFIG
WHERE ROWNUM=1)
AND MD.CALENDAR_CODE=MP.CALENDAR_CODE
AND MD.SR_INSTANCE_ID=MP.SR_INSTANCE_ID
AND MD.EXCEPTION_SET_ID=MP.EXCEPTION_SET_ID
AND MD.CALENDAR_CODE=MW.CALENDAR_CODE
AND MD.SR_INSTANCE_ID=MW.SR_INSTANCE_ID
AND MD.EXCEPTION_SET_ID=MW.EXCEPTION_SET_ID
AND MD.CALENDAR_DATE BETWEEN MW.WEEK_START_DATE
AND MW.NEXT_DATE-1
AND MD.CALENDAR_DATE BETWEEN MP.PERIOD_START_DATE
AND MP.NEXT_DATE-1
AND MD.EXCEPTION_SET_ID=-1)
, FIS AS ( SELECT FP.PERIOD_SET_NAME FIS_PERIOD_SET_NAME
, FP.PERIOD_NUM FIS_PERIOD_NUM
, FP.PERIOD_NAME FIS_PERIOD_NAME
, FP.DESCRIPTION FIS_PERIOD_DESCRIPTION
, FP.START_DATE FIS_PERIOD_START_DATE
, FP.END_DATE FIS_PERIOD_END_DATE
, FP.QUARTER_NUM FIS_QUARTER_NUM
, 'Q'||FP.QUARTER_NUM FIS_QUARTER_NAME
, FP.QUARTER_START_DATE FIS_QUARTER_START_DATE
, FP.PERIOD_YEAR FIS_YEAR
, FP.YEAR_START_DATE FIS_YEAR_START_DATE
FROM MSC_TRADING_PARTNERS O
, MSC_BIS_PERIODS FP
WHERE FP.SR_INSTANCE_ID=O.SR_INSTANCE_ID
AND FP.ORGANIZATION_ID=O.SR_TP_ID
AND O.PARTNER_TYPE=3
AND O.ORGANIZATION_CODE||':'||PERIOD_SET_NAME=(SELECT PERIOD_SET_NAME
FROM MSC_APCC_CONFIG
WHERE ROWNUM=1)
AND NVL(FP.ADJUSTMENT_PERIOD_FLAG
, 'N')='N') SELECT D.CALENDAR_DATE
, TRUNC(D.CALENDAR_DATE
, 'MM') MONTH_START_DATE
, TRUNC(ADD_MONTHS(D.CALENDAR_DATE
, 1)
, 'MM') - 1 MONTH_END_DATE
, TRUNC(D.CALENDAR_DATE
, 'YYYY') YEAR_START_DATE
, TO_CHAR(D.CALENDAR_DATE
, 'MON') MONTH_NAME
, TO_CHAR(D.CALENDAR_DATE
, 'YYYY') YEAR_NAME
, MFG_SEQ_NUM
, MFG_WEEK_START_DATE
, MFG_WEEK_END_DATE
, MFG_PERIOD_START_DATE
, MFG_PERIOD_END_DATE
, MFG_PERIOD_NAME
, FIS_PERIOD_SET_NAME
, FIS_PERIOD_NUM
, FIS_PERIOD_NAME
, FIS_PERIOD_DESCRIPTION
, FIS_PERIOD_START_DATE
, FIS_PERIOD_END_DATE
, FIS_QUARTER_NUM
, FIS_QUARTER_NAME
, FIS_QUARTER_START_DATE
, FIS_YEAR
, FIS_YEAR_START_DATE
FROM (SELECT MIN_DATE+LEVEL-1 CALENDAR_DATE
FROM (SELECT MIN(MIN_DATE) MIN_DATE
, MAX(MAX_DATE) MAX_DATE
FROM (SELECT MIN(MFG_WEEK_START_DATE) MIN_DATE
, MAX(MFG_WEEK_END_DATE) MAX_DATE
FROM MFG UNION SELECT MIN(FIS_PERIOD_START_DATE) MIN_DATE
, MAX(FIS_PERIOD_END_DATE) MAX_DATE
FROM FIS ) ) CONNECT BY LEVEL <= MAX_DATE-MIN_DATE+1) D
, MFG
, FIS
WHERE D.CALENDAR_DATE=MFG.CALENDAR_DATE(+)
AND D.CALENDAR_DATE BETWEEN FIS.FIS_PERIOD_START_DATE(+)
AND FIS.FIS_PERIOD_END_DATE(+)