select d1.detail_date calendar_date,
d1.week_start_date mfg_cal_week_start_date,
d1.month_start_date month_start_date,
fiscal.start_date fiscal_period_start_date,
d1.calendar_code,d1.exception_set_id,
fiscal.period_set_name,
1 day_count
from (select d.detail_date,d.month_start_date,
mfg.week_start_date,mfg.calendar_code,
mfg.exception_set_id
from msc_calendar_dtl d,
(select mcd.detail_date,w.week_start_date,
m.calendar_code,w.exception_set_id
from msc_calendar_dtl mcd, msc_cal_week_start_dates w,msc_calendars m
where m.calendar_code =fnd_profile.value('MSC_HUB_CAL_CODE')
and m.calendar_code = w.calendar_code
and w.week_start_date<=mcd.detail_date
and w.next_date>mcd.detail_date) mfg
where mfg.detail_date(+) = d.detail_date) d1,
(select mcd.detail_date,mbp.start_date,mbp.period_set_name
from msc_calendar_Dtl mcd,msc_bis_periods mbp
where msc_get_name.org_code(mbp.organization_id, mbp.sr_instance_id)||':'|| mbp.period_set_name
= fnd_profile.value('MSC_HUB_PERIOD_SET_NAME')
and mbp.start_date<=mcd.detail_date
and mbp.end_date>= mcd.detail_date) fiscal
where d1.detail_date = fiscal.detail_date(+)
[Home] [Help]