[Home] [Help]
137:
138: /* find out the day that is on */
139: select min(bsd.shift_date)
140: into l_cur_date
141: from bom_shift_dates bsd, bom_resource_shifts brs
142: where bsd.calendar_code = p_cal_code and
143: bsd.shift_date >= trunc( p_date )and
144: brs.department_id = p_dept_id and
145: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
149:
150: /* find out prior and next day in calendar */
151: select max(bsd.shift_date)
152: into l_prior_date
153: from bom_shift_dates bsd, bom_resource_shifts brs
154: where bsd.calendar_code = p_cal_code and
155: bsd.shift_date < l_cur_date and
156: brs.department_id = p_dept_id and
157: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
160: bsd.seq_num is not null;
161:
162: select min(bsd.shift_date)
163: into l_next_date
164: from bom_shift_dates bsd, bom_resource_shifts brs
165: where bsd.calendar_code = p_cal_code and
166: bsd.shift_date > l_cur_date and
167: brs.department_id = p_dept_id and
168: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
195: from bom_shift_times bst
196: where bst.calendar_code = p_cal_code
197: group by bst.shift_num
198: ) st ,
199: bom_resource_shifts brs
200: where bsd.calendar_code = p_cal_code and
201: bsd.shift_num = st.shift_num and
202: brs.department_id = p_dept_id and
203: brs.resource_id = nvl( p_resource_id, brs.resource_id) and
1640: where sd.from_date <= sd.to_date;
1641:
1642: select brs.capacity_units
1643: into l_units
1644: from bom_resource_shifts brs
1645: where brs.department_id = p_dept_id and
1646: brs.resource_id = p_resource_id and
1647: brs.shift_num = p_shift_num;
1648: else
1814: Begin
1815: Begin
1816: select bsd.seq_num
1817: into l_shift_seq
1818: from bom_shift_dates bsd, mtl_parameters mp, bom_resource_shifts brs
1819: where mp.organization_id = p_org_id and
1820: mp.calendar_code = bsd.calendar_code and
1821: brs.department_id = p_department_id and
1822: brs.resource_id = p_resource_id and