1 PACKAGE BODY WIP_WPS_RES_INSTANCE_AVAIL AS
2 /* $Header: wipzinsb.pls 115.0 2003/09/03 23:03:13 jaliu noship $ */
3
4 var_gt_user_id number;
5 var_gt_debug boolean;
6
7 function check_24( var_time in number) return number is
8 begin
9 /*
10 if var_gt_debug then
11 dbms_output.put_line('In check_24 '|| to_char(var_time));
12 end if;
13 */
14 if var_time > 24*3600 then
15 return var_time - 24*3600;
16 else
17 return var_time;
18 end if;
19 end check_24;
20
21 procedure update_avail( var_rowid in ROWID,
22 var_date in DATE,
23 var_from_time in number,
24 var_to_time in number) is
25 var_time1 number;
26 var_time2 number;
27 var_date1 DATE;
28 begin
29
30 -- dbms_output.put_line(' rowid: '||var_rowid);
31 var_time1 := check_24(var_from_time);
32 var_time2 := check_24(var_to_time);
33 /*
34 if the start time is in the next, this avail should be on the
35 following day
36 */
37
38 var_date1 := var_date;
39 if var_time1 < var_from_time then
40 var_date1 := var_date1 + 1;
41 end if;
42
43 UPDATE mrp_net_resource_avail
44 SET shift_date = var_date1,
45 from_time = var_time1,
46 to_time = var_time2
47 WHERE rowid = var_rowid;
48 end update_avail;
49
50 procedure delete_avail( var_rowid in ROWID) is
51 begin
52 /*
53 if var_gt_debug then
54 dbms_output.put_line('about to delete');
55 end if;
56 */
57 DELETE from mrp_net_resource_avail
58 WHERE rowid = var_rowid;
59 end delete_avail;
60
61 procedure insert_avail( var_date in DATE,
62 var_department_id in number,
63 var_resource_id in number,
64 var_instance_id in number,
65 var_serial_num in varchar2,
66 var_organization_id in number,
67 var_shift_num in number,
68 var_simulation_set in varchar2,
69 var_from_time in number,
70 var_to_time in number,
71 var_cap_units in number) is
72 var_time1 number;
73 var_time2 number;
74 var_date1 DATE;
75
76 begin
77 var_time1 := check_24(var_from_time);
78 var_time2 := check_24(var_to_time);
79
80 /*
81 if the start time is in the next, this avail should be on the
82 following day
83 */
84
85 var_date1 := var_date;
86 if var_time1 < var_from_time then
87 var_date1 := var_date1 + 1;
88 end if;
89 /*
90 if var_gt_debug then
91 dbms_output.put_line('Ready to insert' ||
92 ' Dept ' || to_char(var_department_id) ||
93 ' Res ' || to_char(var_resource_id) ||
94 ' shift ' || to_char(var_shift_num) ||
95 ' date '|| to_char(var_date) ||
96 ' from time '|| to_char(var_from_time/3600)||
97 ' to time '|| to_char(var_to_time/3600) ||
98 ' units '|| to_char(var_cap_units));
99 end if;
100 */
101 INSERT into mrp_net_resource_avail(
102 department_id,
103 resource_id,
104 instance_id,
105 serial_number,
106 organization_id,
107 shift_num,
108 shift_date,
109 from_time,
110 to_time,
111 capacity_units,
112 simulation_set,
113 last_update_date,
114 last_updated_by,
115 creation_date,
116 created_by)
117 VALUES(
118 var_department_id,
119 var_resource_id,
120 var_instance_id,
121 var_serial_num,
122 var_organization_id,
123 var_shift_num,
124 var_date1,
125 var_time1,
126 var_time2,
127 var_cap_units,
128 var_simulation_set,
129 sysdate,
130 var_gt_user_id,
131 sysdate,
132 var_gt_user_id);
133 end insert_avail;
134
135 PROCEDURE calc_ins_avail( arg_organization_id IN number,
136 arg_department_id IN number,
137 arg_resource_id IN number,
138 arg_simulation_set IN varchar2,
139 arg_instance_id IN number,
140 arg_serial_num IN varchar2,
141 arg_24hr_flag IN number,
142 arg_start_date IN date default SYSDATE,
143 arg_cutoff_date IN date) is
144
145 cursor changes is
146 SELECT distinct
147 changes.action_type,
148 changes.from_time,
149 DECODE(LEAST(changes.to_time, changes.from_time),
150 changes.to_time, changes.to_time + 24*3600,
151 changes.to_time),
152 dates.shift_date,
153 changes.shift_num,
154 reschanges.capacity_change
155 from bom_shift_dates dates,
156 bom_res_instance_changes changes,
157 bom_resource_changes reschanges,
158 mtl_parameters param
159 WHERE dates.calendar_code = param.calendar_code
160 AND dates.exception_set_id = param.calendar_exception_set_id
161 AND dates.seq_num is not null
162 AND dates.shift_date between changes.from_date AND
163 NVL(changes.to_date, changes.from_date)
164 AND dates.shift_num = changes.shift_num
165 AND param.organization_id = arg_organization_id
166 AND changes.to_date >= trunc(arg_start_date)
167 AND changes.from_date <= arg_cutoff_date
168 AND changes.simulation_set = arg_simulation_set
169 AND changes.action_type = CHANGE_WORKDAY
170 AND reschanges.action_type = CHANGE_WORKDAY
171 AND changes.resource_id = arg_resource_id
172 AND changes.instance_id = arg_instance_id
173 AND nvl(changes.serial_number,-1) = nvl(arg_serial_num, -1)
174 AND changes.department_id = arg_department_id
175 AND reschanges.department_id = changes.department_id
176 AND reschanges.resource_id = changes.resource_id
177 AND reschanges.shift_num = changes.shift_num
178 AND reschanges.from_date = changes.from_date
179 AND reschanges.to_date = changes.to_date
180 AND reschanges.simulation_set = changes.simulation_set
181 AND reschanges.action_type = changes.action_type
182 AND reschanges.from_time = changes.from_time
183 AND reschanges.to_time = changes.to_time
184 -- Removed for bug #2318675 (24hr changes were ignored)
185 --AND NOT (changes.from_time = changes.to_time AND
186 -- changes.from_date = changes.to_date)
187 ORDER BY dates.shift_date, changes.from_time;
188
189
190 var_action_type number;
191 var_from_time number;
192 var_to_time number;
193 var_shift_date date;
194 var_from_shift_time number;
195 var_to_shift_time number;
196 var_orig_cap number;
197 var_shift_num number;
198 var_cap_change number;
199 var_orig_from_time number;
200 var_orig_to_time number;
201 var_next_from_time number;
202 var_rowid rowid;
203 var_rowcount number;
204
205 cursor avail is
206 SELECT capacity_units capacity_units,
207 from_time from_time,
208 DECODE(LEAST(to_time, from_time),
209 to_time, to_time + 24*3600,
210 to_time) to_time,
211 rowid
212 FROM mrp_net_resource_avail
213 WHERE department_id = arg_department_id
214 AND resource_id = arg_resource_id
215 AND instance_id = arg_instance_id
216 AND nvl(serial_number,-1) = nvl(arg_serial_num, -1)
217 AND simulation_set = arg_simulation_set
218 AND organization_id = arg_organization_id
219 AND shift_num = var_shift_num
220 AND shift_date = var_shift_date
221 ORDER BY 2, 3;
222
223 begin
224 var_gt_user_id := fnd_global.user_id;
225
226 if arg_24hr_flag = 2 THEN
227 insert into mrp_net_resource_avail(
228 organization_id,
229 department_id,
230 resource_id,
231 instance_id,
232 serial_number,
233 shift_num,
234 shift_date,
235 from_time,
236 to_time,
237 capacity_units,
238 simulation_set,
239 last_update_date,
240 last_updated_by,
241 creation_date,
242 created_by)
243 select arg_organization_id,
244 arg_department_id,
245 arg_resource_id,
246 arg_instance_id,
247 arg_serial_num,
248 res_shifts.shift_num,
249 dates.shift_date,
250 shifts.from_time,
251 shifts.to_time,
252 -- the capacity unit for instance can only be 1.
253 1,
254 arg_simulation_set,
255 sysdate,
256 var_gt_user_id,
257 sysdate,
258 var_gt_user_id
259 FROM bom_shift_dates dates,
260 bom_shift_times shifts,
261 bom_resource_shifts res_shifts,
262 bom_department_resources dept_res1,
263 bom_dept_res_instances dept_ins,
264 mtl_parameters param
265 WHERE dates.calendar_code = param.calendar_code
266 AND dates.exception_set_id = param.calendar_exception_set_id
267 AND dates.shift_num = shifts.shift_num
268 AND dates.seq_num is not null
269 AND dates.shift_date >= trunc(arg_start_date)
270 AND dates.shift_date <= arg_cutoff_date
271 AND shifts.shift_num = res_shifts.shift_num
272 AND shifts.calendar_code = param.calendar_code
273 AND res_shifts.department_id = dept_res1.department_id
274 AND res_shifts.resource_id = dept_res1.resource_id
275 AND NVL(dept_res1.available_24_hours_flag, 2) = 2
276 AND dept_res1.share_from_dept_id is null
277 AND dept_res1.resource_id = arg_resource_id
278 AND dept_res1.department_id = arg_department_id
279 AND dept_ins.department_id = arg_department_id
280 AND dept_ins.resource_id = arg_resource_id
281 AND dept_ins.instance_id = arg_instance_id
282 AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num, -1)
283 AND param.organization_id = arg_organization_id
284 AND NOT EXISTS
285 (SELECT NULL
286 FROM bom_resource_changes changes
287 WHERE changes.department_id = dept_res1.department_id
288 AND changes.resource_id = dept_res1.resource_id
289 AND changes.simulation_set = arg_simulation_set
290 AND changes.shift_num = dates.shift_num
291 AND changes.from_date = dates.shift_date
292 AND changes.action_type = DELETE_WORKDAY);
293
294 -- debug
295 /*
296 select count(*) into var_rowcount
297 FROM mrp_net_resource_avail
298 where resource_id = arg_resource_id
299 and instance_id = arg_instance_id
300 and department_id = arg_department_id;
301
302 dbms_output.put_line(' Inserted '|| to_char(var_rowcount)||' avails'); */
303
304
305 else
306 insert into mrp_net_resource_avail(
307 organization_id,
308 department_id,
309 resource_id,
310 instance_id,
311 serial_number,
312 shift_num,
313 shift_date,
314 from_time,
315 to_time,
316 capacity_units,
317 simulation_set,
318 last_update_date,
319 last_updated_by,
320 creation_date,
321 created_by)
322 select arg_organization_id,
323 arg_department_id,
324 arg_resource_id,
325 arg_instance_id,
326 arg_serial_num,
327 0,
328 dates.calendar_date,
329 1,
330 24*60*60 - 1,
331 1,
332 arg_simulation_set,
333 sysdate,
334 var_gt_user_id,
335 sysdate,
336 var_gt_user_id
337 FROM bom_calendar_dates dates,
338 bom_department_resources dept_res1,
339 bom_dept_res_instances dept_ins,
340 mtl_parameters param
341 WHERE dates.calendar_code = param.calendar_code
342 AND dates.exception_set_id = param.calendar_exception_set_id
343 AND dates.calendar_date <= arg_cutoff_date
344 AND dates.seq_num is not null
345 AND dates.calendar_date >= trunc(arg_start_date)
346 AND NVL(dept_res1.available_24_hours_flag, 2) = 1
347 AND dept_res1.share_from_dept_id is null
348 AND dept_res1.resource_id = arg_resource_id
349 AND dept_res1.department_id = arg_department_id
350 AND dept_ins.department_id = arg_department_id
351 AND dept_ins.resource_id = arg_resource_id
352 AND dept_ins.instance_id = arg_instance_id
353 AND nvl(dept_ins.serial_number,-1) = nvl(arg_serial_num,-1)
354 AND param.organization_id = arg_organization_id;
355 end if;
356
357 -- return;
358
359 if arg_24hr_flag = 2 then
360 OPEN changes;
361 loop
362 FETCH changes INTO
363 var_action_type,
364 var_orig_from_time,
365 var_orig_to_time,
366 var_shift_date,
367 var_shift_num,
368 var_cap_change;
369 EXIT WHEN changes%NOTFOUND;
370 -- since the capacity for instance can be only 1,
371 -- the changes can be only 1 or -1
372 if var_cap_change > 0 then
373 var_cap_change := 1;
374 else
375 var_cap_change := -1;
376 end if;
377
378 --dbms_output.put_line('cap_change: '||var_orig_from_time||'-'||
379 -- var_orig_to_time||' '||var_shift_date||' '||
380 -- var_cap_change);
381
382 /*----------------------------------------------------------+
383 | For each modification we get the current resource |
384 | calendar and process sections of the modification that |
385 | overlaps with the shift segment |
386 +----------------------------------------------------------*/
387 -- Initialize the variables
388 var_from_time := var_orig_from_time;
389 -- var_next_from_time := var_orig_from_time;
390 var_to_time := var_orig_to_time;
391 var_rowcount := 0;
392 OPEN avail;
393 LOOP
394 FETCH avail INTO
395 var_orig_cap,
396 var_from_shift_time,
397 var_to_shift_time,
398 var_rowid;
399 EXIT WHEN avail%NOTFOUND;
400 -- Set the from time for the modification to the start of
401 -- the unprocessed section
402 var_from_time := var_orig_from_time;
406 -- If you have completely processed the modification you are
403 -- Set the to time to the original to time of the modification
404 var_to_time := var_orig_to_time;
405
407 -- done so exit
408 if (var_from_time > var_to_time) then
409 EXIT;
410 end if;
411 -- if the shift spans over midnight
412 -- and the shift exception starts on the next day
413 -- then, the shift exception times need to be moved to the next day
414 if var_to_shift_time > 24*60*60 then
415 if var_from_time < var_to_shift_time - 24*60*60 then
416 var_from_time := var_from_time + 24*60*60;
417 var_to_time := var_to_time + 24*60*60;
418 end if;
419 end if;
420
421 -- If the from time or to time is outside of the shift, then
422 -- we will just add the entire capacity.. skip partial
423 if var_from_time >= var_to_shift_time
424 OR var_to_time <= var_from_shift_time then
425 goto skip;
426 end if;
427
428 -- If the shift starts, before the modification
429 if var_from_shift_time < var_from_time then
430 -- Then, if the end of the shift is before or equal to the end
431 -- of the modification, update avail from the start of
432 -- the shift to the start of the modification.
433 if var_to_shift_time <= var_to_time then
434 update_avail(var_rowid,
435 var_shift_date,
436 var_from_shift_time,
437 var_from_time -1);
438 else
439 update_avail(var_rowid,
440 var_shift_date,
441 var_from_shift_time,
442 var_from_time -1);
443 insert_avail(var_shift_date,
444 arg_department_id,
445 arg_resource_id,
446 arg_instance_id,
447 arg_serial_num,
448 arg_organization_id,
449 var_shift_num,
450 arg_simulation_set,
451 var_to_time+1,
452 var_to_shift_time,
453 var_orig_cap);
454 end if;
455
456 elsif var_from_shift_time >= var_from_time then
457 if var_to_shift_time > var_to_time then
458 update_avail(var_rowid,
459 var_shift_date,
460 var_to_time+1,
461 var_to_shift_time);
462 else
463 delete_avail(var_rowid);
464 end if;
465 end if;
466
467 <<skip>>
468 NULL;
469 end loop;
470 close avail;
471
472 -- Insert modification
473 if var_cap_change = 1 then
474 insert_avail(var_shift_date,
475 arg_department_id,
476 arg_resource_id,
477 arg_instance_id,
478 arg_serial_num,
479 arg_organization_id,
480 var_shift_num,
481 arg_simulation_set,
482 var_from_time,
483 var_to_time,
484 var_cap_change);
485 end if;
486 end loop;
487 close changes;
488
489 -- Finally add the availability from the add workday type modifications
490 -- dbms_output.put_line('going to insert added workdays');
491 INSERT into mrp_net_resource_avail(
492 organization_id,
493 department_id,
494 resource_id,
495 instance_id,
496 serial_number,
497 shift_num,
498 shift_date,
499 from_time,
500 to_time,
501 capacity_units,
502 simulation_set,
503 last_update_date,
504 last_updated_by,
505 creation_date,
506 created_by)
507 select arg_organization_id,
508 arg_department_id,
509 arg_resource_id,
510 arg_instance_id,
511 arg_serial_num,
512 changes.shift_num,
513 changes.from_date,
514 changes.from_time,
515 changes.to_time,
516 1,
517 arg_simulation_set,
518 sysdate,
519 var_gt_user_id,
520 sysdate,
521 var_gt_user_id
522 FROM bom_res_instance_changes changes
523 WHERE changes.department_id = arg_department_id
524 AND changes.resource_id = arg_resource_id
525 and changes.instance_id = arg_instance_id
526 and nvl(changes.serial_number,-1) = nvl(arg_serial_num, -1)
527 AND changes.action_type = ADD_WORKDAY
528 AND changes.simulation_set= arg_simulation_set;
529
530 end if;
531 end calc_ins_avail;
532
533
534 end WIP_WPS_RES_INSTANCE_AVAIL;
[Home] [Help]