[Home] [Help]
226:
227: /* find out the day that is on */
228: select min(bsd.shift_date)
229: into l_cur_date
230: from bom_shift_dates bsd, bom_resource_shifts brs
231: where bsd.calendar_code = l_cal_code and
232: bsd.shift_date >= trunc( p_date )and
233: brs.department_id = p_dept_id and
234: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
238:
239: /* find out prior and next day in calendar */
240: select max(bsd.shift_date)
241: into l_prior_date
242: from bom_shift_dates bsd, bom_resource_shifts brs
243: where bsd.calendar_code = l_cal_code and
244: bsd.shift_date < l_cur_date and
245: brs.department_id = p_dept_id and
246: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
249: bsd.seq_num is not null;
250:
251: select min(bsd.shift_date)
252: into l_next_date
253: from bom_shift_dates bsd, bom_resource_shifts brs
254: where bsd.calendar_code = l_cal_code and
255: bsd.shift_date > l_cur_date and
256: brs.department_id = p_dept_id and
257: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
278: from bom_shift_times bst
279: where bst.calendar_code = l_cal_code
280: group by bst.shift_num
281: ) st ,
282: bom_resource_shifts brs
283: where bsd.calendar_code = l_cal_code and
284: bsd.shift_num = st.shift_num and
285: brs.department_id = p_dept_id and
286: brs.resource_id = nvl( p_resource_id, brs.resource_id) and