[Home] [Help]
PACKAGE BODY: APPS.WIP_UPDATE_SETUP_RESOURCES
Source
1 PACKAGE BODY WIP_UPDATE_SETUP_RESOURCES AS
2 /* $Header: wpusetrb.pls 115.9 2002/12/03 11:28:12 simishra ship $ */
3
4 g_scheduled_jobs Number_Tbl_Type;
5
6
7 PROCEDURE DELETE_SETUP_RESOURCES(p_wip_entity_id NUMBER,
8 p_organization_id NUMBER,
9 p_operation_seq_num NUMBER,
10 p_resource_seq_num NUMBER) IS
11
12 BEGIN
13
14 -- delete old setup resource instances
15 delete from wip_op_resource_instances
16 where wip_entity_id = p_wip_entity_id
17 and organization_id = p_organization_id
18 and operation_seq_num = p_operation_seq_num
19 and resource_seq_num in (
20 select resource_seq_num
21 from wip_operation_resources wor
22 where wor.wip_entity_id = p_wip_entity_id
23 and wor.organization_id = p_organization_id
24 and wor.operation_seq_num = p_operation_seq_num
25 and wor.parent_resource_seq = p_resource_seq_num);
26
27 -- delete old setup resources
28 delete from wip_operation_resources
29 where wip_entity_id = p_wip_entity_id
30 and organization_id = p_organization_id
31 and operation_seq_num = p_operation_seq_num
32 and parent_resource_seq = p_resource_seq_num;
33
34 END DELETE_SETUP_RESOURCES;
35
36
37 PROCEDURE UPDATE_SETUP_RESOURCES_PVT(p_wip_entity_id IN NUMBER,
38 p_organization_id IN NUMBER,
39 p_operation_seq IN NUMBER,
40 p_resource_seq IN NUMBER,
41 x_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2)
44 IS
45 l_setup_id NUMBER;
46 l_resource_id NUMBER;
47 l_dept_id NUMBER;
48
49 l_next_wip_entity_id NUMBER;
50 l_next_operation_seq_num NUMBER;
51 l_next_resource_seq_num NUMBER;
52 l_next_instance_id NUMBER;
53 l_next_serial_number VARCHAR2(30);
54 l_next_setup_id NUMBER;
55 l_next_processed_qty NUMBER;
56 l_next_start_date DATE;
57
58 l_update_resources BOOLEAN := TRUE;
59
60 l_transition_time NUMBER;
61 l_uom_code VARCHAR2(3);
62 l_std_op_id NUMBER;
63 l_setup_op_res_seq NUMBER;
64 l_num_of_instances NUMBER;
65
66 l_instance_id NUMBER;
67 l_serial_number VARCHAR2(30);
68 l_res_completion_date DATE;
69 l_ri_completion_date DATE;
70
71
72 cursor op_res_instances(p_wip_entity_id NUMBER,
73 p_operation_seq_num NUMBER,
74 p_resource_seq_num NUMBER) is
75 select wori.instance_id,
76 wori.serial_number,
77 wori.completion_date
78 from wip_op_resource_instances wori
79 where wori.wip_entity_id = p_wip_entity_id
80 and wori.organization_id = p_organization_id
81 and wori.operation_seq_num = p_operation_seq_num
82 and wori.resource_seq_num = p_resource_seq_num;
83
84
85 cursor next_op_res(p_completion_date DATE,
86 p_resource_id NUMBER) IS
87 select wip_entity_id,
88 operation_seq_num,
89 resource_seq_num,
90 setup_id,
91 processed_qty,
92 start_date
93 from (select wor.wip_entity_id wip_entity_id,
94 wor.operation_seq_num operation_seq_num,
95 wor.resource_seq_num resource_seq_num,
96 wor.setup_id,
97 nvl(wo.quantity_running,0)+nvl(wo.quantity_completed,0) processed_qty,
98 wor.start_date start_date
99 from wip_operation_resources wor,
100 wip_operations wo
101 where wor.start_date >= p_completion_date
102 and wor.resource_id = p_resource_id
103 and wor.parent_resource_seq is null
104 and wo.wip_entity_id = wor.wip_entity_id
105 and wo.organization_id = wor.organization_id
106 and wo.operation_seq_num = wor.operation_seq_num
107 order by wor.start_date)
108 where rownum = 1;
109
110
111
112
113 cursor next_op_res_inst(p_completion_date DATE,
114 p_resource_id NUMBER,
115 p_instance_id NUMBER,
116 p_serial_number VARCHAR2) IS
117 select wip_entity_id,
118 operation_seq_num,
119 resource_seq_num,
120 instance_id,
121 serial_number,
122 setup_id,
123 processed_qty,
124 start_date
125 from (select wori1.wip_entity_id wip_entity_id,
126 wori1.operation_seq_num operation_seq_num,
127 wori1.resource_seq_num resource_seq_num,
128 wori1.instance_id instance_id,
129 wori1.serial_number serial_number,
130 wor1.setup_id,
131 nvl(wo.quantity_running,0)+nvl(wo.quantity_completed,0) processed_qty,
132 wor1.start_date start_date
133 from wip_op_resource_instances wori1,
134 wip_operation_resources wor1,
135 wip_operations wo
136 where wor1.wip_entity_id = wori1.wip_entity_id
137 and wor1.operation_seq_num = wori1.operation_seq_num
138 and wor1.resource_seq_num = wori1.resource_seq_num
139 and wori1.start_date >= p_completion_date
140 and wor1.resource_id = p_resource_id
141 and wor1.parent_resource_seq is null
142 and wori1.instance_id = p_instance_id
143 and nvl(wori1.serial_number,-1) = nvl(p_serial_number,-1)
144 and wo.wip_entity_id = wor1.wip_entity_id
145 and wo.organization_id = wor1.organization_id
146 and wo.operation_seq_num = wor1.operation_seq_num
147 order by wori1.start_date)
148 where rownum = 1;
149
150 cursor get_transition(p_resource_id NUMBER,
151 l_from_setup_id NUMBER,
152 l_to_setup_id NUMBER) IS
153 select transition_time, operation_id
154 from (select inv_convert.inv_um_convert
155 (-1,2,transition_time,
156 transition_uom, 'MIN', NULL, NULL) transition_time,
157 operation_id
158 from bom_setup_transitions
159 where resource_id = l_resource_id
160 and organization_id = p_organization_id
161 and from_setup_id = l_setup_id
162 and to_setup_id = l_next_setup_id
163 union
164 select inv_convert.inv_um_convert
165 (-1,2,transition_time,
166 transition_uom, 'MIN', NULL, NULL) transition_time,
167 operation_id
168 from bom_setup_transitions
169 where resource_id = l_resource_id
170 and organization_id = p_organization_id
171 and from_setup_id is null
172 and to_setup_id = l_next_setup_id)
173 where rownum = 1;
174
175 cursor setup_resource_cursor(p_std_op_id NUMBER) IS
176 select bsor.resource_id,
177 bsor.resource_seq_num,
178 bsor.assigned_units,
179 bsor.schedule_flag,
180 bso.department_id
181 FROM bom_std_op_resources bsor,
182 bom_standard_operations bso
183 WHERE bsor.standard_operation_id = p_std_op_id
184 and bso.standard_operation_id = bsor.standard_operation_id;
185
186 cursor instance_cursor(p_resource_id NUMBER,
187 p_dept_id NUMBER) IS
188 select instance_id,
189 serial_number
190 from bom_dept_res_instances
191 where resource_id = p_resource_id
192 and department_id = p_dept_id;
193
194
195
196 BEGIN
197
198 -- get the current resource and setup
199 select wor.setup_id,
200 wor.resource_id,
201 nvl(wor.department_id, wo.department_id),
202 wor.completion_date
203 into l_setup_id,
204 l_resource_id,
205 l_dept_id,
206 l_res_completion_date
207 from wip_operation_resources wor,
208 wip_operations wo
209 where wor.wip_entity_id = p_wip_entity_id
210 and wor.organization_id = p_organization_id
211 and wor.operation_seq_num = p_operation_seq
212 and wor.resource_seq_num = p_resource_seq
213 and wo.wip_entity_id = wor.wip_entity_id
214 and wo.organization_id = wor.organization_id
215 and wo.operation_seq_num = wor.operation_seq_num;
216
217
218 SAVEPOINT start_point;
219
220
221 -- for each resource instance, find the necessary setup resources for the
222 -- NEXT activity using the same instance
223
224 OPEN op_res_instances(p_wip_entity_id,p_operation_seq, p_resource_seq);
225 LOOP
226 FETCH op_res_instances INTO l_instance_id, l_serial_number, l_ri_completion_date;
227
228 IF op_res_instances%ROWCOUNT = 0 THEN
229 OPEN next_op_res(l_res_completion_date, l_resource_id);
230 FETCH next_op_res INTO l_next_wip_entity_id, l_next_operation_seq_num,
231 l_next_resource_seq_num, l_next_setup_id,
232 l_next_processed_qty, l_next_start_date;
233 CLOSE next_op_res;
234
235 ELSE
236 OPEN next_op_res_inst(l_ri_completion_date, l_resource_id, l_instance_id, l_serial_number);
237 FETCH next_op_res_inst INTO l_next_wip_entity_id, l_next_operation_seq_num,
238 l_next_resource_seq_num, l_next_instance_id,
239 l_next_serial_number, l_next_setup_id,
240 l_next_processed_qty, l_next_start_date;
241 CLOSE next_op_res_inst;
242
243 end if;
244
245 -- check if the job found is in the excluded list
246 FOR i IN 1..g_scheduled_jobs.count loop
247 if g_scheduled_jobs(i) = l_next_wip_entity_id THEN
248 l_update_resources := FALSE;
249 exit;
250 end if;
251 END LOOP;
252
253
254 -- if the job is in the excluded list or if the next job already has activity,
255 -- then do NOT update setup resources and exit
256 if l_update_resources = FALSE or l_next_processed_qty > 0 then
257 exit;
258 end if;
259
260 -- get the appropriate transition for the opep resource instance
261 OPEN get_transition(l_resource_id, l_setup_id, l_next_setup_id);
262 FETCH get_transition INTO l_transition_time, l_std_op_id;
263 IF get_transition%NOTFOUND THEN
264 l_update_resources := FALSE;
265 exit;
266 END IF;
267 CLOSE get_transition;
268
269 DELETE_SETUP_RESOURCES(l_next_wip_entity_id,
270 p_organization_id,
271 l_next_operation_seq_num,
272 l_next_resource_seq_num);
273
274 -- get the max resource sequence for the operation
275 select max(res)
276 into l_setup_op_res_seq
277 from (select max(resource_seq_num) res
278 from wip_operation_resources
279 where wip_entity_id = l_next_wip_entity_id
280 and operation_seq_num = l_next_operation_seq_num
281 union
282 select max(resource_seq_num) res
283 from wip_sub_operation_resources
284 where wip_Entity_id = l_next_wip_entity_id
285 and operation_seq_num = l_next_operation_seq_num);
286
287 l_setup_op_res_seq := l_setup_op_res_seq+10;
288
289 -- insert setup resource for the resource that is being setup
290 INSERT INTO WIP_OPERATION_RESOURCES
291 (last_update_date,
292 last_updated_by,
293 creation_date,
294 created_by,
295 last_update_login,
296 organization_id,
297 wip_entity_id,
298 repetitive_schedule_id,
299 operation_seq_num,
300 resource_seq_num,
301 resource_id,
302 uom_code,
303 basis_type,
304 activity_id,
305 standard_rate_flag,
306 usage_rate_or_amount,
307 scheduled_flag,
308 assigned_units,
309 autocharge_type,
310 applied_resource_units,
311 applied_resource_value,
312 start_date,
313 completion_date,
314 parent_resource_seq,
315 substitute_group_num,
316 replacement_group_num,
317 schedule_seq_num
318 )
319 select
320 SYSDATE,
321 FND_GLOBAL.USER_ID,
322 SYSDATE,
323 FND_GLOBAL.USER_ID,
324 FND_GLOBAL.LOGIN_ID,
325 p_organization_id,
326 l_next_wip_entity_id,
327 NULL,
328 l_next_operation_seq_num,
329 l_setup_op_res_seq,
330 l_resource_id,
331 'MIN',
332 2, -- basis type: PER_LOT
333 wor.activity_id,
334 wor.standard_rate_flag,
335 l_transition_time, -- usage_rate_or_amount
336 1, -- scheduled_flag: YES
337 wor.assigned_units,
338 wor.autocharge_type,
339 0, -- applied_resource_units
340 0, -- applied_resource_value
341 l_next_start_date - (l_transition_time/1440),
342 l_next_start_date,
343 l_next_resource_seq_num,
344 wor.substitute_group_num,
345 wor.replacement_group_num,
346 wor.schedule_seq_num
347 FROM
348 wip_operation_resources wor
349 WHERE wor.wip_entity_id = l_next_wip_entity_id
350 and wor.organization_id = p_organization_id
351 and wor.operation_seq_num = l_next_operation_seq_num
352 and wor.resource_seq_num = l_next_resource_seq_num;
353
354 insert into wip_op_resource_instances
355 (ORGANIZATION_ID,
356 WIP_ENTITY_ID,
357 OPERATION_SEQ_NUM,
358 RESOURCE_SEQ_NUM,
359 INSTANCE_ID,
360 SERIAL_NUMBER,
361 START_DATE,
362 COMPLETION_DATE,
363 LAST_UPDATE_DATE,
364 LAST_UPDATED_BY,
365 CREATION_DATE,
366 CREATED_BY,
367 LAST_UPDATE_LOGIN)
368 select
369 ORGANIZATION_ID,
370 WIP_ENTITY_ID,
371 OPERATION_SEQ_NUM,
372 l_setup_op_res_seq,
373 l_instance_id,
374 l_serial_number,
375 START_DATE,
376 COMPLETION_DATE,
377 SYSDATE,
378 FND_GLOBAL.USER_ID,
379 SYSDATE,
380 FND_GLOBAL.USER_ID,
381 LAST_UPDATE_LOGIN
382 from wip_operation_resources
383 where wip_entity_id = l_next_wip_entity_id
384 and organization_id = p_organization_id
385 and operation_seq_num = l_next_operation_seq_num
386 and resource_seq_num = l_setup_op_res_seq;
387
388 FOR setup_res_rec in setup_resource_cursor(l_std_op_id) LOOP
389
390 l_setup_op_res_seq := l_setup_op_res_seq+10;
391
392 INSERT INTO WIP_OPERATION_RESOURCES
393 (last_update_date,
394 last_updated_by,
395 creation_date,
396 created_by,
397 last_update_login,
398 organization_id,
399 wip_entity_id,
400 repetitive_schedule_id,
401 operation_seq_num,
402 resource_seq_num,
403 resource_id,
404 uom_code,
405 basis_type,
406 activity_id,
407 standard_rate_flag,
408 usage_rate_or_amount,
409 scheduled_flag,
410 assigned_units,
411 autocharge_type,
412 applied_resource_units,
413 applied_resource_value,
414 start_date,
415 completion_date,
416 parent_resource_seq,
417 substitute_group_num,
418 replacement_group_num,
419 schedule_seq_num
420 )
421 select
422 SYSDATE,
423 FND_GLOBAL.USER_ID,
424 SYSDATE,
425 FND_GLOBAL.USER_ID,
426 FND_GLOBAL.LOGIN_ID,
427 p_organization_id,
428 l_next_wip_entity_id,
429 NULL,
430 l_next_operation_seq_num,
431 l_setup_op_res_seq,
432 setup_res_rec.resource_id,
433 'MIN',
434 2, -- basis type: PER_LOT
435 wor.activity_id,
436 wor.standard_rate_flag,
437 l_transition_time, -- usage_rate_or_amount
438 setup_res_rec.schedule_flag, -- scheduled_flag: YES
439 setup_res_rec.assigned_units,
440 wor.autocharge_type,
441 0, -- applied_resource_units
442 0, -- applied_resource_value
443 decode(setup_res_rec.schedule_flag,
444 1,l_next_start_date - (l_transition_time/1440),
445 l_next_start_date),
446 l_next_start_date,
447 l_next_resource_seq_num,
448 wor.substitute_group_num,
449 wor.replacement_group_num,
450 wor.schedule_seq_num
451 FROM
452 wip_operation_resources wor
453 where wor.wip_entity_id = l_next_wip_entity_id
454 and wor.organization_id = p_organization_id
455 and wor.operation_seq_num = l_next_operation_seq_num
456 and wor.resource_seq_num = l_next_resource_seq_num;
457
458 l_num_of_instances:= 0;
459
460 FOR instance_rec in instance_cursor(setup_res_rec.resource_id, setup_res_rec.department_id) LOOP
461
462 -- inserting a setup op resource instance for a given op resource
463 if (l_num_of_instances < setup_res_rec.assigned_units) then
464
465 l_num_of_instances := l_num_of_instances + 1;
466
467 insert into wip_op_resource_instances
468 (ORGANIZATION_ID,
469 WIP_ENTITY_ID,
470 OPERATION_SEQ_NUM,
471 RESOURCE_SEQ_NUM,
472 INSTANCE_ID,
473 SERIAL_NUMBER,
474 START_DATE,
475 COMPLETION_DATE,
476 LAST_UPDATE_DATE,
477 LAST_UPDATED_BY,
478 CREATION_DATE,
479 CREATED_BY,
480 LAST_UPDATE_LOGIN)
481 select
482 ORGANIZATION_ID,
483 WIP_ENTITY_ID,
484 OPERATION_SEQ_NUM,
485 l_setup_op_res_seq,
486 instance_rec.instance_id,
487 instance_rec.serial_number,
488 START_DATE,
489 COMPLETION_DATE,
490 SYSDATE,
491 FND_GLOBAL.USER_ID,
492 SYSDATE,
493 FND_GLOBAL.USER_ID,
494 LAST_UPDATE_LOGIN
495 from wip_operation_resources
496 where wip_entity_id = l_next_wip_entity_id
497 and organization_id = p_organization_id
498 and operation_seq_num = l_next_operation_seq_num
499 and resource_seq_num = l_setup_op_res_seq
500 and parent_resource_seq is not null;
501 ELSE
502 exit;
503 END IF;
504 END LOOP;
505
506 END LOOP;
507 exit when op_res_instances%NOTFOUND;
508 END LOOP;
509
510
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN
513 return;
514 WHEN OTHERS THEN
515 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
516 p_count => x_msg_count,
517 p_data => x_msg_data);
518 x_status := fnd_api.g_ret_sts_error;
519 ROLLBACK to start_point;
520 RETURN ;
521
522
523
524 END UPDATE_SETUP_RESOURCES_PVT;
525
526
527
528 --
529 --
530 -- Public Functions
531 --
532
533 PROCEDURE UPDATE_SETUP_RESOURCES_PUB(p_wip_entity_id IN NUMBER,
534 p_organization_id IN NUMBER,
535 x_status OUT NOCOPY VARCHAR2,
536 x_msg_count OUT NOCOPY NUMBER,
537 x_msg_data OUT NOCOPY VARCHAR2)
538 IS
539 CURSOR OPERATION_RESOURCES_CURS(p_wip_entity_id NUMBER) IS
540 select wor.operation_seq_num,
541 wor.resource_seq_num
542 from wip_operation_resources wor
543 where wor.wip_entity_id = p_wip_entity_id;
544
545
546 BEGIN
547
548 FOR op_res_rec in OPERATION_RESOURCES_CURS(p_wip_entity_id) LOOP
549 UPDATE_SETUP_RESOURCES_PVT(p_wip_entity_id,
550 p_organization_id,
551 op_res_rec.operation_seq_num,
552 op_res_rec.resource_seq_num,
553 x_status,
554 x_msg_count,
555 x_msg_data);
556 END LOOP;
557
558 END UPDATE_SETUP_RESOURCES_PUB;
559
560
561 PROCEDURE UPDATE_SETUP_RESOURCES_PUB(p_wip_entity_id IN NUMBER,
562 p_organization_id IN NUMBER,
563 p_list_weid IN Number_Tbl_Type,
564 x_status OUT NOCOPY VARCHAR2,
565 x_msg_count OUT NOCOPY NUMBER,
566 x_msg_data OUT NOCOPY VARCHAR2) IS
567
568 BEGIN
569 ADD_SCHEDULED_JOBS(p_list_weid);
570
571 UPDATE_SETUP_RESOURCES_PUB(p_wip_entity_id,
572 p_organization_id,
573 x_status,
574 x_msg_count,
575 x_msg_data);
576
577 END UPDATE_SETUP_RESOURCES_PUB;
578
579
580 PROCEDURE DELETE_SETUP_RESOURCES_PUB(p_wip_entity_id IN NUMBER,
581 p_organization_id IN NUMBER) IS
582 BEGIN
583
584 -- delete old setup resource instances
585 delete from wip_op_resource_instances wori
586 where wori.wip_entity_id = p_wip_entity_id
587 and wori.organization_id = p_organization_id
588 and wori.resource_seq_num in (
589 select resource_seq_num
590 from wip_operation_resources wor
591 where wor.wip_entity_id = p_wip_entity_id
592 and wor.organization_id = p_organization_id
593 and wor.parent_resource_seq is not null);
594
595 -- delete old setup resources
596 delete from wip_operation_resources
597 where wip_entity_id = p_wip_entity_id
598 and organization_id = p_organization_id
599 and parent_resource_seq is not null;
600
601 END DELETE_SETUP_RESOURCES_PUB;
602
603 PROCEDURE ADD_SCHEDULED_JOBS(p_list_weid IN Number_Tbl_Type) IS
604 orig_num_of_records NUMBER;
605 BEGIN
606 orig_num_of_records := g_scheduled_jobs.count;
607
608 FOR i IN 1..p_list_weid.count loop
609 if p_list_weid(i) = -1 THEN
610 EXIT;
611 end if;
612 g_scheduled_jobs(orig_num_of_records + i) := p_list_weid(i);
613 END LOOP;
614
615 END ADD_SCHEDULED_JOBS;
616
617 PROCEDURE DELETE_SCHEDULED_JOBS_TBL IS
618 BEGIN
619 g_scheduled_jobs.delete;
620
621 END DELETE_SCHEDULED_JOBS_TBL;
622
623
624 END WIP_UPDATE_SETUP_RESOURCES;