VIEW: APPS.GMP_MFG_TIME_WEEKS_V

Source

View Text - Preformatted

SELECT calendar_id, week_no, week_start_date, week_end_date FROM ( select msd.calendar_id, max(week_start_end.week_start_date) WEEK_START_DATE, max(week_start_end.week_end_date) WEEK_END_DATE, count(week_start_end.calendar_id) WEEK_NO FROM ( SELECT msd.calendar_id calendar_id, (msd.calendar_date - ( to_number(to_char(msd.calendar_date,'D') -1 ))) GROUP_MEMBER, min(msd.calendar_date) WEEK_START_DATE, max(msd.calendar_date) WEEK_END_DATE FROM mr_shcl_dtl msd WHERE msd.calendar_date BETWEEN (msd.calendar_date - ( to_number(to_char(msd.calendar_date,'D') -1 ))) AND (msd.calendar_date + (( to_number(to_char(msd.calendar_date,'D') -1 ) - 7) * -1)-1) GROUP BY msd.calendar_id, (msd.calendar_date - ( to_number(to_char(msd.calendar_date,'D') -1 ))) ) week_start_end , mr_shcl_dtl msd WHERE week_start_end.calendar_id = msd.calendar_id AND msd.calendar_date >= week_start_end.week_start_date GROUP BY msd.calendar_date, msd.calendar_id ORDER BY 2 ,1 ) GROUP BY calendar_id, week_no, week_start_date, week_end_date

View Text - HTML Formatted

SELECT CALENDAR_ID
, WEEK_NO
, WEEK_START_DATE
, WEEK_END_DATE
FROM ( SELECT MSD.CALENDAR_ID
, MAX(WEEK_START_END.WEEK_START_DATE) WEEK_START_DATE
, MAX(WEEK_START_END.WEEK_END_DATE) WEEK_END_DATE
, COUNT(WEEK_START_END.CALENDAR_ID) WEEK_NO
FROM ( SELECT MSD.CALENDAR_ID CALENDAR_ID
, (MSD.CALENDAR_DATE - ( TO_NUMBER(TO_CHAR(MSD.CALENDAR_DATE
, 'D') -1 ))) GROUP_MEMBER
, MIN(MSD.CALENDAR_DATE) WEEK_START_DATE
, MAX(MSD.CALENDAR_DATE) WEEK_END_DATE
FROM MR_SHCL_DTL MSD
WHERE MSD.CALENDAR_DATE BETWEEN (MSD.CALENDAR_DATE - ( TO_NUMBER(TO_CHAR(MSD.CALENDAR_DATE
, 'D') -1 )))
AND (MSD.CALENDAR_DATE + (( TO_NUMBER(TO_CHAR(MSD.CALENDAR_DATE
, 'D') -1 ) - 7) * -1)-1) GROUP BY MSD.CALENDAR_ID
, (MSD.CALENDAR_DATE - ( TO_NUMBER(TO_CHAR(MSD.CALENDAR_DATE
, 'D') -1 ))) ) WEEK_START_END
, MR_SHCL_DTL MSD
WHERE WEEK_START_END.CALENDAR_ID = MSD.CALENDAR_ID
AND MSD.CALENDAR_DATE >= WEEK_START_END.WEEK_START_DATE GROUP BY MSD.CALENDAR_DATE
, MSD.CALENDAR_ID ORDER BY 2
, 1 ) GROUP BY CALENDAR_ID
, WEEK_NO
, WEEK_START_DATE
, WEEK_END_DATE