eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
[Home] [Help]
MATERIALIZED VIEW: APPS.MSC_PHUB_DATES_MV

Source


SELECT "A3"."CALENDAR_DATE" "CALENDAR_DATE",TRUNC("A3"."CALENDAR_DATE",'MM') "MONTH_START_DATE",TRUNC(ADD_MONTHS("A3"."CALENDAR_DATE",1),'MM')-1 "MONTH_END_DATE",TRUNC("A3"."CALENDAR_DATE",'YYYY') "YEAR_START_DATE",TO_CHAR("A3"."CALENDAR_DATE",'MON') "MONTH_NAME",TO_CHAR("A3"."CALENDAR_DATE",'YYYY') "YEAR_NAME","A2"."MFG_SEQ_NUM" "MFG_SEQ_NUM","A2"."MFG_WEEK_START_DATE" "MFG_WEEK_START_DATE","A2"."MFG_WEEK_END_DATE" "MFG_WEEK_END_DATE","A2"."MFG_PERIOD_START_DATE" "MFG_PERIOD_START_DATE","A2"."MFG_PERIOD_END_DATE" "MFG_PERIOD_END_DATE","A2"."MFG_PERIOD_NAME" "MFG_PERIOD_NAME","A1"."FIS_PERIOD_SET_NAME" "FIS_PERIOD_SET_NAME","A1"."FIS_PERIOD_NUM" "FIS_PERIOD_NUM","A1"."FIS_PERIOD_NAME" "FIS_PERIOD_NAME","A1"."FIS_PERIOD_DESCRIPTION" "FIS_PERIOD_DESCRIPTION","A1"."FIS_PERIOD_START_DATE" "FIS_PERIOD_START_DATE","A1"."FIS_PERIOD_END_DATE" "FIS_PERIOD_END_DATE","A1"."FIS_QUARTER_NUM" "FIS_QUARTER_NUM","A1"."FIS_QUARTER_NAME" "FIS_QUARTER_NAME","A1"."FIS_QUARTER_START_DATE" "FIS_QUARTER_START_DATE","A1"."FIS_YEAR" "FIS_YEAR","A1"."FIS_YEAR_START_DATE" "FIS_YEAR_START_DATE" FROM  (SELECT "A4"."MIN_DATE"+LEVEL-1 "CALENDAR_DATE" FROM  (SELECT MIN("A5"."MIN_DATE") "MIN_DATE",MAX("A5"."MAX_DATE") "MAX_DATE" FROM  ( (SELECT MIN("A8"."MFG_WEEK_START_DATE") "MIN_DATE",MAX("A8"."MFG_WEEK_END_DATE") "MAX_DATE" FROM  (SELECT DISTINCT "MD"."CALENDAR_DATE" "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"."MSC_CALENDAR_DATES" "MD","MSC"."MSC_CAL_WEEK_START_DATES" "MW","MSC"."MSC_PERIOD_START_DATES" "MP" WHERE "MD"."CALENDAR_CODE"= (SELECT "MSC_APCC_CONFIG"."CAL_CODE" "CAL_CODE" FROM "MSC"."MSC_APCC_CONFIG" "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">="MW"."WEEK_START_DATE" AND "MD"."CALENDAR_DATE"<="MW"."NEXT_DATE"-1 AND "MD"."CALENDAR_DATE">="MP"."PERIOD_START_DATE" AND "MD"."CALENDAR_DATE"<="MP"."NEXT_DATE"-1 AND "MD"."EXCEPTION_SET_ID"=(-1)) "A8")UNION (SELECT MIN("A7"."FIS_PERIOD_START_DATE") "MIN_DATE",MAX("A7"."FIS_PERIOD_END_DATE") "MAX_DATE" FROM  (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"."MSC_TRADING_PARTNERS" "O","MSC"."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"||':'||"FP"."PERIOD_SET_NAME"= (SELECT "MSC_APCC_CONFIG"."PERIOD_SET_NAME" "PERIOD_SET_NAME" FROM "MSC"."MSC_APCC_CONFIG" "MSC_APCC_CONFIG" WHERE ROWNUM=1) AND NVL("FP"."ADJUSTMENT_PERIOD_FLAG",'N')='N') "A7")) "A5") "A4" CONNECT BY LEVEL<="A4"."MAX_DATE"-"A4"."MIN_DATE"+1) "A3", (SELECT DISTINCT "MD"."CALENDAR_DATE" "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"."MSC_CALENDAR_DATES" "MD","MSC"."MSC_CAL_WEEK_START_DATES" "MW","MSC"."MSC_PERIOD_START_DATES" "MP" WHERE "MD"."CALENDAR_CODE"= (SELECT "MSC_APCC_CONFIG"."CAL_CODE" "CAL_CODE" FROM "MSC"."MSC_APCC_CONFIG" "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">="MW"."WEEK_START_DATE" AND "MD"."CALENDAR_DATE"<="MW"."NEXT_DATE"-1 AND "MD"."CALENDAR_DATE">="MP"."PERIOD_START_DATE" AND "MD"."CALENDAR_DATE"<="MP"."NEXT_DATE"-1 AND "MD"."EXCEPTION_SET_ID"=(-1)) "A2", (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"."MSC_TRADING_PARTNERS" "O","MSC"."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"||':'||"FP"."PERIOD_SET_NAME"= (SELECT "MSC_APCC_CONFIG"."PERIOD_SET_NAME" "PERIOD_SET_NAME" FROM "MSC"."MSC_APCC_CONFIG" "MSC_APCC_CONFIG" WHERE ROWNUM=1) AND NVL("FP"."ADJUSTMENT_PERIOD_FLAG",'N')='N') "A1" WHERE "A3"."CALENDAR_DATE"="A2"."CALENDAR_DATE"(+) AND "A3"."CALENDAR_DATE">="A1"."FIS_PERIOD_START_DATE"(+) AND "A3"."CALENDAR_DATE"<="A1"."FIS_PERIOD_END_DATE"(+)