[Home] [Help]
PACKAGE BODY: APPS.WIP_JOB_DTLS_SUBSTITUTIONS
Source
1 PACKAGE BODY WIP_JOB_DTLS_SUBSTITUTIONS AS
2 /* $Header: wipjdstb.pls 120.20.12010000.2 2008/09/09 16:46:14 hliew ship $ */
3
4 type date_tbl_t is table of date ; /* Fix for Bug4656331 */
5 type rowid_tbl_t is table of varchar2(18) ; /* Fix for Bug4656331 */
6
7
8 Procedure Delete_Resource (p_group_id in number,
9 p_wip_entity_id in number,
10 p_organization_id in number,
11 p_err_code out NOCOPY varchar2,
12 p_err_msg out NOCOPY varchar2) IS
13
14 CURSOR res_info (p_group_id number,
15 p_wip_entity_id number,
16 p_organization_id number) IS
17 SELECT distinct operation_seq_num,
18 resource_seq_num, resource_id_old, resource_id_new,
19 usage_rate_or_amount,
20 last_update_date, last_updated_by, creation_date, created_by,
21 last_update_login, request_id, program_application_id,
22 program_id, program_update_date,
23 scheduled_flag, assigned_units, applied_resource_units,
24 applied_resource_value, uom_code, basis_type,
25 activity_id, autocharge_type, standard_rate_flag,
26 start_date, completion_date,attribute_category, attribute1,
27 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
28 attribute8,attribute9,attribute10,attribute11,attribute12,
29 attribute13,attribute14,attribute15
30 FROM WIP_JOB_DTLS_INTERFACE
31 WHERE group_id = p_group_id
32 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
33 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
34 AND wip_entity_id = p_wip_entity_id
35 AND organization_id = p_organization_id
36 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
37 AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
38
39 l_ret_exp_status boolean := true; --Bug#4675116
40
41 BEGIN
42
43 begin
44 FOR cur_row IN res_info(p_group_id,
45 p_wip_entity_id,
46 p_organization_id) LOOP
47
48 Delete_Resource_Usage(p_wip_entity_id,
49 p_organization_id,
50 cur_row.operation_seq_num,
51 cur_row.resource_seq_num,
52 p_err_code,
53 p_err_msg);
54
55 l_ret_exp_status := WIP_WS_EXCEPTIONS.close_exception_jobop_res
56 (
57 p_wip_entity_id => p_wip_entity_id,
58 p_operation_seq_num => cur_row.operation_seq_num,
59 p_resource_seq_num => cur_row.resource_seq_num,
60 p_organization_id => p_organization_id
61 );
62
63 DELETE FROM WIP_OPERATION_RESOURCES
64 WHERE wip_entity_id = p_wip_entity_id
65 AND organization_id = p_organization_id
66 AND operation_seq_num = cur_row.operation_seq_num
67 AND resource_seq_num = cur_row.resource_seq_num
68 AND resource_id = cur_row.resource_id_old;
69
70 END LOOP;
71
72 exception
73 when others then
74 p_err_msg := 'WIPJDSTB, Delete_Resource: ' || SQLERRM;
75 p_err_code := SQLCODE;
76 end;
77
78 END Delete_Resource;
79
80
81 Procedure Add_Resource (p_group_id number,
82 p_wip_entity_id number,
83 p_organization_id number,
84 p_err_code out NOCOPY varchar2,
85 p_err_msg out NOCOPY varchar2) IS
86
87
88 CURSOR res_info (p_group_id number,
89 p_wip_entity_id number,
90 p_organization_id number) IS
91 SELECT distinct operation_seq_num,
92 resource_seq_num, resource_id_old, resource_id_new,
93 usage_rate_or_amount,
94 last_update_date, last_updated_by, creation_date, created_by,
95 last_update_login, request_id, program_application_id,
96 program_id, program_update_date,
97 scheduled_flag, assigned_units, applied_resource_units,
98 applied_resource_value, uom_code, basis_type,
99 activity_id, autocharge_type, standard_rate_flag,
100 start_date, completion_date,attribute_category, attribute1,
101 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
102 attribute8,attribute9,attribute10,attribute11,attribute12,
103 attribute13,attribute14,attribute15,schedule_seq_num,
104 substitute_group_num,replacement_group_num, firm_flag, setup_id,
105 group_sequence_id, group_sequence_number, maximum_assigned_units,
106 parent_seq_num, batch_id
107 FROM WIP_JOB_DTLS_INTERFACE
108 WHERE group_id = p_group_id
109 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
110 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
111 AND wip_entity_id = p_wip_entity_id
112 AND organization_id = p_organization_id
113 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
114 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
115
116 l_scheduling_method number;
117 l_scheduled_start_date date;/* Bug 3669728*/
118 l_scheduled_completion_date date;/* Bug 3669728*/
119 l_first_unit_start_date date;/* Bug 3669728*/
120 l_last_unit_completion_date date;/* Bug 3669728*/
121
122
123 BEGIN
124
125 begin
126
127 /* Moved the delete statement out of the loop for fixing bug 4357678
128 Every time in the loop, this was deleting the inserted records as well */
129 FOR cur_row IN res_info(p_group_id,
130 p_wip_entity_id,
131 p_organization_id) LOOP
132 -- if adding setup resource, delete all existing setup resources
133 if (cur_row.parent_seq_num is not null) then
134 DELETE FROM WIP_OPERATION_RESOURCES
135 WHERE wip_entity_id = p_wip_entity_id
136 AND organization_id = p_organization_id
137 AND operation_seq_num = cur_row.operation_seq_num
138 AND parent_resource_seq = cur_row.parent_seq_num;
139 end if;
140 END LOOP ;
141
142 FOR cur_row IN res_info(p_group_id,
143 p_wip_entity_id,
144 p_organization_id) LOOP
145
146
147 /* insert into table */
148 INSERT INTO WIP_OPERATION_RESOURCES(
149 wip_entity_id,
150 organization_id,
151 operation_seq_num,
152 resource_seq_num,
153 resource_id,
154 usage_rate_or_amount,
155 last_update_date,
156 last_updated_by,
157 creation_date,
158 created_by,
159 last_update_login,
160 request_id,
161 program_application_id,
162 program_id,
163 program_update_date,
164 scheduled_flag,
165 assigned_units,
166 applied_resource_units,
167 applied_resource_value,
168 uom_code,
169 basis_type,
170 activity_id,
171 autocharge_type,
172 standard_rate_flag,
173 start_date,
174 completion_date,
175 attribute_category,
176 attribute1,
177 attribute2,
178 attribute3,
179 attribute4,
180 attribute5,
181 attribute6,
182 attribute7,
183 attribute8,
184 attribute9,
185 attribute10,
186 attribute11,
187 attribute12,
188 attribute13,
189 attribute14,
190 attribute15,
191 schedule_seq_num,
192 substitute_group_num,
193 replacement_group_num,
194 firm_flag,
195 setup_id,
196 group_sequence_id,
197 group_sequence_number,
198 maximum_assigned_units,
199 parent_resource_seq,
200 batch_id)
201 VALUES (
202 p_wip_entity_id,
203 p_organization_id,
204 cur_row.operation_seq_num,
205 cur_row.resource_seq_num,
206 cur_row.resource_id_new,
207 cur_row.usage_rate_or_amount,
208 sysdate,/*BUG 6721823*/
209 cur_row.last_updated_by,
210 cur_row.creation_date,
211 cur_row.created_by,
212 cur_row.last_update_login,
213 cur_row.request_id,
214 cur_row.program_application_id,
215 cur_row.program_id,
216 cur_row.program_update_date,
217 cur_row.scheduled_flag,
218 cur_row.assigned_units,
219 cur_row.applied_resource_units,
220 cur_row.applied_resource_value,
221 cur_row.uom_code,
222 cur_row.basis_type,
223 cur_row.activity_id,
224 cur_row.autocharge_type,
225 cur_row.standard_rate_flag,
226 cur_row.start_date,
227 cur_row.completion_date,
228 cur_row.attribute_category,
229 cur_row.attribute1,
230 cur_row.attribute2,
231 cur_row.attribute3,
232 cur_row.attribute4,
233 cur_row.attribute5,
234 cur_row.attribute6,
235 cur_row.attribute7,
236 cur_row.attribute8,
237 cur_row.attribute9,
238 cur_row.attribute10,
239 cur_row.attribute11,
240 cur_row.attribute12,
241 cur_row.attribute13,
242 cur_row.attribute14,
243 cur_row.attribute15,
244 cur_row.schedule_seq_num,
245 cur_row.substitute_group_num,
246 cur_row.replacement_group_num,
247 cur_row.firm_flag,
248 cur_row.setup_id,
249 cur_row.group_sequence_id,
250 cur_row.group_sequence_number,
251 cur_row.maximum_assigned_units,
252 cur_row.parent_seq_num,
253 cur_row.batch_id);
254
255 IF WIP_JOB_DETAILS.std_alone = 0 THEN
256
257 SELECT scheduling_method INTO l_scheduling_method
258 FROM WIP_JOB_SCHEDULE_INTERFACE
259 WHERE group_id = p_group_id
260 AND wip_entity_id = p_wip_entity_id
261 AND organization_id = p_organization_id;
262
263 END IF;
264
265 -- We check that whether there're usage records for this resource
266 -- If it's stand alone or the scheduling_method is manaul (3)
267 -- we need default a resource usage.
268
269 IF WIP_JOB_DETAILS.std_alone = 1 OR
270 ( WIP_JOB_DETAILS.std_alone = 0 AND l_scheduling_method = 3) THEN
271
272 IF Num_Of_Usage(p_group_id, /* Fix for bug#3636378 */
273 p_wip_entity_id,
274 p_organization_id,
275 cur_row.operation_seq_num,
276 cur_row.resource_seq_num) = 0 THEN
277
278 Add_Default_Usage(p_wip_entity_id,
279 p_organization_id,
280 cur_row.operation_seq_num,
281 cur_row.resource_seq_num);
282
283 END IF;
284 END IF;
285
286 /* bug#3669728 - begin */
287 select scheduled_start_date,scheduled_completion_date
288 into l_scheduled_start_date,l_scheduled_completion_date
289 from wip_discrete_jobs
290 where wip_entity_id = p_wip_entity_id
291 AND organization_id = p_organization_id;
292
293 IF (cur_row.start_date is not null
294 AND cur_row.start_date < l_scheduled_start_date)
295 THEN
296 UPDATE wip_discrete_jobs
297 set scheduled_start_date = cur_row.start_date
298 where wip_entity_id = p_wip_entity_id
299 AND organization_id = p_organization_id;
300 END IF;
301 IF (cur_row.completion_date is not null
302 AND cur_row.completion_date > l_scheduled_completion_date)
303 THEN
304 UPDATE wip_discrete_jobs
305 set scheduled_completion_date = cur_row.completion_date
306 where wip_entity_id = p_wip_entity_id
307 AND organization_id = p_organization_id;
308 END IF;
309
310 select first_unit_start_date,last_unit_completion_date
311 into l_first_unit_start_date,l_last_unit_completion_date
312 from wip_operations
313 where wip_entity_id = p_wip_entity_id
314 AND organization_id = p_organization_id
315 AND operation_seq_num = cur_row.operation_seq_num;
316
317 IF (cur_row.start_date is not null
318 AND cur_row.start_date < l_first_unit_start_date)
319 THEN
320 UPDATE wip_operations
321 set first_unit_start_date = cur_row.start_date,
322 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
323 where wip_entity_id = p_wip_entity_id
324 AND organization_id = p_organization_id
325 AND operation_seq_num = cur_row.operation_seq_num;
326 END IF;
327 IF (cur_row.completion_date is not null
328 AND cur_row.completion_date > l_last_unit_completion_date)
329 THEN
330 UPDATE wip_operations
331 set last_unit_completion_date = cur_row.completion_date,
332 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
333 where wip_entity_id = p_wip_entity_id
334 AND organization_id = p_organization_id
335 AND operation_seq_num = cur_row.operation_seq_num;
336 END IF;
337 /* bug#3669728 - end */
338
339 END LOOP;
340
341 exception
342 when others then
343 p_err_msg := 'WIPJDSTB, Add_Resource: ' || SQLERRM;
344 p_err_code := SQLCODE;
345 end;
346
347 END Add_Resource;
348
349
350 Procedure Change_Resource (p_group_id number,
351 p_wip_entity_id number,
352 p_organization_id number,
353 p_err_code out NOCOPY varchar2,
354 p_err_msg out NOCOPY varchar2) IS
355
356 CURSOR res_info (p_group_id number,
357 p_wip_entity_id number,
358 p_organization_id number) IS
359 SELECT distinct operation_seq_num,
360 resource_seq_num, resource_id_old, resource_id_new,
361 usage_rate_or_amount,
362 last_update_date, last_updated_by, creation_date, created_by,
363 last_update_login, request_id, program_application_id,
364 program_id, program_update_date,
365 scheduled_flag, assigned_units, applied_resource_units,
366 applied_resource_value, uom_code, basis_type,
367 activity_id, autocharge_type, standard_rate_flag,
368 start_date, completion_date,attribute_category, attribute1,
369 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
370 attribute8,attribute9,attribute10,attribute11,attribute12,
371 attribute13,attribute14,attribute15, schedule_seq_num,
372 substitute_group_num, replacement_group_num, firm_flag,setup_id,
373 group_sequence_id, group_sequence_number, maximum_assigned_units,
374 parent_seq_num, batch_id
375 FROM WIP_JOB_DTLS_INTERFACE
376 WHERE group_id = p_group_id
377 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
378 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
379 AND wip_entity_id = p_wip_entity_id
380 AND organization_id = p_organization_id
381 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
382 AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
383
384 cursor missing_res_csr
385 is
386 select wo.first_unit_start_date,
387 rowidtochar(wor.rowid)
388 from wip_operation_resources wor,
389 wip_operations wo
390 where wo.wip_entity_id = wor.wip_entity_id
391 and wo.organization_id = wor.organization_id
392 and wo.operation_seq_num = wor.operation_seq_num
393 and wor.wip_entity_id = p_wip_entity_id
394 and wor.organization_id = p_organization_id
395 and not exists ( select 1
396 FROM WIP_JOB_DTLS_INTERFACE
397 WHERE group_id = p_group_id
398 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
399 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
400 AND wip_entity_id = p_wip_entity_id
401 AND organization_id = p_organization_id
402 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
403 AND operation_seq_num = wor.operation_seq_num
404 AND resource_seq_num = wor.resource_seq_num
405 ) ;
406
407 l_source_code varchar2(255) ;
408
409
410 l_scheduling_method number := 0;
411 l_replace_res number := 0;
412 l_current_sub number;
413 x_status varchar2(30);
414 x_msg_count number;
415 x_msg_data varchar2(30);
416 l_scheduled_start_date date;/* Bug 3669728*/
417 l_scheduled_completion_date date;/* Bug 3669728*/
418 l_first_unit_start_date date;/* Bug 3669728*/
419 l_last_unit_completion_date date;/* Bug 3669728*/
420 l_dummy2 VARCHAR2(1);
421 l_logLevel number;
422
423 l_ret_exp_status boolean := true; --Bug#4675116
424
425 l_rowidTbl rowid_tbl_t ; /* Fix for Bug#4656331 */
426 l_fusdTbl date_tbl_t ; /* Fix for Bug#4656331 */
427
428 BEGIN
429
430 begin
431 FOR cur_row IN res_info(p_group_id,
432 p_wip_entity_id,
433 p_organization_id) LOOP
434
435 l_replace_res := 0;
436
437 select substitute_group_num
438 into l_current_sub
439 from wip_operation_resources
440 where wip_entity_id = p_wip_entity_id
441 and operation_seq_num = cur_row.operation_seq_num
442 and resource_seq_num = cur_row.resource_seq_num;
443
444 IF (cur_row.substitute_group_num = l_current_sub) THEN
445
446 begin
447 select 1
448 into l_replace_res
449 from dual
450 where exists (select 1
451 from wip_sub_operation_resources
452 where wip_entity_id = p_wip_entity_id
453 and operation_seq_num = cur_row.operation_seq_num
454 and substitute_group_num = cur_row.substitute_group_num
455 and replacement_group_num = cur_row.replacement_group_num);
456 exception
457 when no_data_found then
458 l_replace_res := 0;
459 end;
460
461 if (l_replace_res = 1) then
462 wip_sub_op_resources_pkg.Replace_Resources(
463 p_wip_entity_id,
464 null,
465 cur_row.operation_seq_num,
466 cur_row.substitute_group_num,
467 cur_row.replacement_group_num,
468 x_status,
469 x_msg_count,
470 x_msg_data);
471 UPDATE WIP_OPERATION_RESOURCES
472 SET start_date = NVL(cur_row.start_date,start_date),
473 completion_date = NVL(cur_row.completion_date,completion_date),
474 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
475 WHERE wip_entity_id = p_wip_entity_id
476 AND organization_id = p_organization_id
477 AND operation_seq_num = cur_row.operation_seq_num
478 AND substitute_group_num = cur_row.substitute_group_num;
479 end if;
480 END IF;
481
482 IF (l_replace_res = 0) then
483
484 --Bug#4675116
485 IF (CUR_ROW.RESOURCE_ID_OLD <> CUR_ROW.RESOURCE_ID_NEW) THEN
486 L_RET_EXP_STATUS := WIP_WS_EXCEPTIONS.CLOSE_EXCEPTION_JOBOP_RES
487 (
488 P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
489 P_OPERATION_SEQ_NUM => cur_row.OPERATION_SEQ_NUM,
490 P_RESOURCE_SEQ_NUM => cur_row.RESOURCE_SEQ_NUM,
491 P_ORGANIZATION_ID => P_ORGANIZATION_ID
492 );
493 END IF;
494
495 UPDATE WIP_OPERATION_RESOURCES
496 SET resource_id = cur_row.resource_id_new,
497 usage_rate_or_amount = nvl(cur_row.usage_rate_or_amount,
498 usage_rate_or_amount),
499 last_update_date = sysdate,/*BUG 6721823*/
500 last_updated_by = cur_row.last_updated_by,
501 creation_date = cur_row.creation_date,
502 created_by = cur_row.created_by,
503 last_update_login = cur_row.last_update_login,
504 request_id = cur_row.request_id,
505 program_application_id = cur_row.program_application_id,
506 program_id = cur_row.program_id,
507 program_update_date = cur_row.program_update_date,
508 scheduled_flag = nvl(cur_row.scheduled_flag,
509 scheduled_flag),
510 assigned_units = nvl(cur_row.assigned_units, assigned_units),
511 uom_code = nvl(cur_row.uom_code, uom_code),
512 basis_type = nvl(cur_row.basis_type, basis_type),
513 activity_id = nvl(cur_row.activity_id, activity_id),
514 autocharge_type = nvl(cur_row.autocharge_type, autocharge_type),
515 standard_rate_flag = nvl(cur_row.standard_rate_flag, standard_rate_flag),
516 start_date = nvl(cur_row.start_date, start_date),
517 completion_date = nvl(cur_row.completion_date, completion_date),
518 attribute_category = NVL(cur_row.attribute_category,
519 attribute_category),
520 attribute1 = NVL(cur_row.attribute1,attribute1),
521 attribute2 = NVL(cur_row.attribute2,attribute2),
522 attribute3 = NVL(cur_row.attribute3,attribute3),
523 attribute4 = NVL(cur_row.attribute4,attribute4),
524 attribute5 = NVL(cur_row.attribute5,attribute5),
525 attribute6 = NVL(cur_row.attribute6,attribute6),
526 attribute7 = NVL(cur_row.attribute7,attribute7),
527 attribute8 = NVL(cur_row.attribute8,attribute8),
528 attribute9 = NVL(cur_row.attribute9,attribute9),
529 attribute10 = NVL(cur_row.attribute10,attribute10),
530 attribute11 = NVL(cur_row.attribute11,attribute11),
531 attribute12 = NVL(cur_row.attribute12,attribute12),
532 attribute13 = NVL(cur_row.attribute13,attribute13),
533 attribute14 = NVL(cur_row.attribute14,attribute14),
534 attribute15 = NVL(cur_row.attribute15,attribute15),
535 schedule_seq_num = decode(cur_row.schedule_seq_num, fnd_api.g_miss_num, null, cur_row.schedule_seq_num),
536 substitute_group_num = decode(cur_row.substitute_group_num, fnd_api.g_miss_num, null, cur_row.substitute_group_num),
537 replacement_group_num = decode(cur_row.replacement_group_num, fnd_api.g_miss_num, null, cur_row.replacement_group_num),
538 firm_flag = NVL(cur_row.firm_flag, firm_flag),
539 setup_id = NVL(cur_row.setup_id, setup_id),
540 group_sequence_id = NVL(cur_row.group_sequence_id, group_sequence_id),
541 group_sequence_number = NVL(cur_row.group_sequence_number, group_sequence_number),
542 maximum_assigned_units = NVL(cur_row.maximum_assigned_units, maximum_assigned_units),
543 parent_resource_seq = NVL(cur_row.parent_seq_num, parent_resource_seq),
544 batch_id = NVL(cur_row.batch_id,batch_id)
545 WHERE wip_entity_id = p_wip_entity_id
546 AND organization_id = p_organization_id
547 AND operation_seq_num = cur_row.operation_seq_num
548 AND resource_seq_num = cur_row.resource_seq_num
549 AND resource_id = cur_row.resource_id_old;
550
551 Delete_Resource_Usage(p_wip_entity_id,
552 p_organization_id,
553 cur_row.operation_seq_num,
554 cur_row.resource_seq_num,
555 p_err_code,
556 p_err_msg);
557
558 IF WIP_JOB_DETAILS.std_alone = 0 THEN
559
560 SELECT scheduling_method INTO l_scheduling_method
561 FROM WIP_JOB_SCHEDULE_INTERFACE
562 WHERE group_id = p_group_id
563 AND wip_entity_id = p_wip_entity_id
564 AND organization_id = p_organization_id;
565
566 END IF;
567
568 -- We check that whether there're usage records for this resource
569 -- If it's stand alone or the scheduling_method is manual (3)
570 -- we need default a resource usage.
571
572 IF WIP_JOB_DETAILS.std_alone = 1 OR
573 ( WIP_JOB_DETAILS.std_alone = 0 AND l_scheduling_method = 3) THEN
574
575 IF Num_Of_Usage(p_group_id, /* Fix for bug#3636378 */
576 p_wip_entity_id,
577 p_organization_id,
578 cur_row.operation_seq_num,
579 cur_row.resource_seq_num) = 0 THEN
580
581 Add_Default_Usage(p_wip_entity_id,
582 p_organization_id,
583 cur_row.operation_seq_num,
584 cur_row.resource_seq_num);
585
586 END IF;
587
588 END IF;
589
590 /* bug#3669728 - begin */
591 select scheduled_start_date,scheduled_completion_date
592 into l_scheduled_start_date,l_scheduled_completion_date
593 from wip_discrete_jobs
594 where wip_entity_id = p_wip_entity_id
595 AND organization_id = p_organization_id;
596
597 IF (cur_row.start_date is not null
598 AND cur_row.start_date < l_scheduled_start_date)
599 THEN
600 UPDATE wip_discrete_jobs
601 set scheduled_start_date = cur_row.start_date
602 where wip_entity_id = p_wip_entity_id
603 AND organization_id = p_organization_id;
604 END IF;
605 IF (cur_row.completion_date is not null
606 AND cur_row.completion_date > l_scheduled_completion_date)
607 THEN
608 UPDATE wip_discrete_jobs
609 set scheduled_completion_date = cur_row.completion_date
610 where wip_entity_id = p_wip_entity_id
611 AND organization_id = p_organization_id;
612 END IF;
613
614 select first_unit_start_date,last_unit_completion_date
615 into l_first_unit_start_date,l_last_unit_completion_date
616 from wip_operations
617 where wip_entity_id = p_wip_entity_id
618 AND organization_id = p_organization_id
619 AND operation_seq_num = cur_row.operation_seq_num;
620
621 IF (cur_row.start_date is not null
622 AND cur_row.start_date < l_first_unit_start_date)
623 THEN
624 UPDATE wip_operations
625 set first_unit_start_date = cur_row.start_date,
626 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
627 where wip_entity_id = p_wip_entity_id
628 AND organization_id = p_organization_id
629 AND operation_seq_num = cur_row.operation_seq_num;
630 END IF;
631 IF (cur_row.completion_date is not null
632 AND cur_row.completion_date > l_last_unit_completion_date)
633 THEN
634 UPDATE wip_operations
635 set last_unit_completion_date = cur_row.completion_date,
636 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
637 where wip_entity_id = p_wip_entity_id
638 AND organization_id = p_organization_id
639 AND operation_seq_num = cur_row.operation_seq_num;
640 END IF;
641 /* bug#3669728 - end */
642
643 END IF;
644
645 END LOOP;
646
647
648 /* Fix for Bug#4656331. Update missing resource start date and completion
649 date
650 */
651 select source_code,
652 scheduling_method
653 into l_source_code,
654 l_scheduling_method
655 from wip_job_schedule_interface
656 where group_id = p_group_id
657 and wip_entity_id = p_wip_entity_id
658 and organization_id = p_organization_id ;
659
660 /* Fix for Bug#6394857 (FP of 6370245). Removed scheduling method condition in following if and also
661 open cursor when source_code is MSC.
662
663 if (l_source_code = 'MSC' and l_scheduling_method = WIP_CONSTANTS.ML_MANUAL) then
664 */
665
666 if (l_source_code = 'MSC') then
667
668 open missing_res_csr ;
669 fetch missing_res_csr
670 bulk collect into l_fusdTbl, l_rowidTbl ;
671 close missing_res_csr ;
672
673 forall i in 1..l_fusdTbl.count
674 update wip_operation_resources
675 set start_date = l_fusdTbl(i),
676 completion_date = l_fusdTbl(i),
677 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
678 where rowid = chartorowid(l_rowidTbl(i)) ;
679 end if ;
680
681 exception
682 when others then
683 p_err_msg := 'WIPJDSTB, Change_Resource: ' || SQLERRM;
684 p_err_code := SQLCODE;
685 l_logLevel := fnd_log.g_current_runtime_level;
686 if (l_logLevel <= wip_constants.trace_logging) then
687 wip_logger.log(p_err_msg, l_dummy2);
688 end if;
689
690 /* 4656331. Close cursor if still open */
691 if missing_res_csr%ISOPEN then
692 close missing_res_csr ;
693 end if ;
694
695 wip_logger.cleanup(l_dummy2);
696 end;
697 END Change_Resource;
698
699
700
701 Procedure Delete_Sub_Resource (p_group_id in number,
702 p_wip_entity_id in number,
703 p_organization_id in number,
704 p_err_code out NOCOPY varchar2,
705 p_err_msg out NOCOPY varchar2) IS
706
707 CURSOR res_info (p_group_id number,
708 p_wip_entity_id number,
709 p_organization_id number) IS
710 SELECT distinct operation_seq_num,
711 resource_seq_num, resource_id_old, resource_id_new,
712 usage_rate_or_amount,
713 last_update_date, last_updated_by, creation_date, created_by,
714 last_update_login, request_id, program_application_id,
715 program_id, program_update_date,
716 scheduled_flag, assigned_units, applied_resource_units,
717 applied_resource_value, uom_code, basis_type,
718 activity_id, autocharge_type, standard_rate_flag,
719 start_date, completion_date,attribute_category, attribute1,
720 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
721 attribute8,attribute9,attribute10,attribute11,attribute12,
722 attribute13,attribute14,attribute15
723 FROM WIP_JOB_DTLS_INTERFACE
724 WHERE group_id = p_group_id
725 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
726 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
727 AND wip_entity_id = p_wip_entity_id
728 AND organization_id = p_organization_id
729 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
730 AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
731
732
733 BEGIN
734
735 begin
736 FOR cur_row IN res_info(p_group_id,
737 p_wip_entity_id,
738 p_organization_id) LOOP
739
740 Delete_Resource_Usage(p_wip_entity_id,
741 p_organization_id,
742 cur_row.operation_seq_num,
743 cur_row.resource_seq_num,
744 p_err_code,
745 p_err_msg);
746
747 DELETE FROM WIP_SUB_OPERATION_RESOURCES
748 WHERE wip_entity_id = p_wip_entity_id
749 AND organization_id = p_organization_id
750 AND operation_seq_num = cur_row.operation_seq_num
751 AND resource_seq_num = cur_row.resource_seq_num
752 AND resource_id = cur_row.resource_id_old;
753
754 END LOOP;
755
756 exception
757 when others then
758 p_err_msg := 'WIPJDSTB, Delete_Resource: ' || SQLERRM;
759 p_err_code := SQLCODE;
760 end;
761
762 END Delete_Sub_Resource;
763
764
765 Procedure Add_Sub_Resource (p_group_id number,
766 p_wip_entity_id number,
767 p_organization_id number,
768 p_err_code out NOCOPY varchar2,
769 p_err_msg out NOCOPY varchar2) IS
770
771
772 CURSOR res_info (p_group_id number,
773 p_wip_entity_id number,
774 p_organization_id number) IS
775 SELECT distinct operation_seq_num,
776 resource_seq_num, resource_id_old, resource_id_new,
777 usage_rate_or_amount,
778 last_update_date, last_updated_by, creation_date, created_by,
779 last_update_login, request_id, program_application_id,
780 program_id, program_update_date,
781 scheduled_flag, assigned_units, maximum_assigned_units,applied_resource_units,
782 applied_resource_value, uom_code, basis_type,
783 activity_id, autocharge_type, standard_rate_flag,
784 start_date, completion_date,attribute_category, attribute1,
785 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
786 attribute8,attribute9,attribute10,attribute11,attribute12,
787 attribute13,attribute14,attribute15,schedule_seq_num,
788 substitute_group_num,replacement_group_num
789 FROM WIP_JOB_DTLS_INTERFACE
790 WHERE group_id = p_group_id
791 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
792 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
793 AND wip_entity_id = p_wip_entity_id
794 AND organization_id = p_organization_id
795 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
796 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
797
798 l_scheduling_method number;
799
800 BEGIN
801
802 begin
803 FOR cur_row IN res_info(p_group_id,
804 p_wip_entity_id,
805 p_organization_id) LOOP
806
807 /* insert into table */
808 INSERT INTO WIP_SUB_OPERATION_RESOURCES(
809 wip_entity_id,
810 organization_id,
811 operation_seq_num,
812 resource_seq_num,
813 resource_id,
814 usage_rate_or_amount,
815 last_update_date,
816 last_updated_by,
817 creation_date,
818 created_by,
819 last_update_login,
820 request_id,
821 program_application_id,
822 program_id,
823 program_update_date,
824 scheduled_flag,
825 assigned_units,
826 maximum_assigned_units,
827 applied_resource_units,
828 applied_resource_value,
829 uom_code,
830 basis_type,
831 activity_id,
832 autocharge_type,
833 standard_rate_flag,
834 start_date,
835 completion_date,
836 attribute_category,
837 attribute1,
838 attribute2,
839 attribute3,
840 attribute4,
841 attribute5,
842 attribute6,
843 attribute7,
844 attribute8,
845 attribute9,
846 attribute10,
847 attribute11,
848 attribute12,
849 attribute13,
850 attribute14,
851 attribute15,
852 schedule_seq_num,
853 substitute_group_num,
854 replacement_group_num)
855
856 VALUES (
857 p_wip_entity_id,
858 p_organization_id,
859 cur_row.operation_seq_num,
860 cur_row.resource_seq_num,
861 cur_row.resource_id_new,
862 cur_row.usage_rate_or_amount,
863 sysdate,/*BUG 6721823*/
864 cur_row.last_updated_by,
865 cur_row.creation_date,
866 cur_row.created_by,
867 cur_row.last_update_login,
868 cur_row.request_id,
869 cur_row.program_application_id,
870 cur_row.program_id,
871 cur_row.program_update_date,
872 cur_row.scheduled_flag,
873 cur_row.assigned_units,
874 cur_row.maximum_assigned_units,
875 cur_row.applied_resource_units,
876 cur_row.applied_resource_value,
877 cur_row.uom_code,
878 cur_row.basis_type,
879 cur_row.activity_id,
880 cur_row.autocharge_type,
881 cur_row.standard_rate_flag,
882 cur_row.start_date,
883 cur_row.completion_date,
884 cur_row.attribute_category,
885 cur_row.attribute1,
886 cur_row.attribute2,
887 cur_row.attribute3,
888 cur_row.attribute4,
889 cur_row.attribute5,
890 cur_row.attribute6,
891 cur_row.attribute7,
892 cur_row.attribute8,
893 cur_row.attribute9,
894 cur_row.attribute10,
895 cur_row.attribute11,
896 cur_row.attribute12,
897 cur_row.attribute13,
898 cur_row.attribute14,
899 cur_row.attribute15,
900 cur_row.schedule_seq_num,
901 cur_row.substitute_group_num,
902 cur_row.replacement_group_num);
903
904
905 END LOOP;
906
907 exception
908 when others then
909 p_err_msg := 'WIPJDSTB, Add_Sub_Resource: ' || SQLERRM;
910 p_err_code := SQLCODE;
911 end;
912
913 END Add_Sub_Resource;
914
915
916 Procedure Change_Sub_Resource (p_group_id number,
917 p_wip_entity_id number,
918 p_organization_id number,
919 p_err_code out NOCOPY varchar2,
920 p_err_msg out NOCOPY varchar2) IS
921
922 CURSOR res_info (p_group_id number,
923 p_wip_entity_id number,
924 p_organization_id number) IS
925 SELECT distinct operation_seq_num,
926 resource_seq_num, resource_id_old, resource_id_new,
927 usage_rate_or_amount,
928 last_update_date, last_updated_by, creation_date, created_by,
929 last_update_login, request_id, program_application_id,
930 program_id, program_update_date,
931 scheduled_flag, assigned_units, maximum_assigned_units,applied_resource_units,
932 applied_resource_value, uom_code, basis_type,
933 activity_id, autocharge_type, standard_rate_flag,
934 start_date, completion_date,attribute_category, attribute1,
935 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
936 attribute8,attribute9,attribute10,attribute11,attribute12,
937 attribute13,attribute14,attribute15, schedule_seq_num,
938 substitute_group_num, replacement_group_num
939 FROM WIP_JOB_DTLS_INTERFACE
940 WHERE group_id = p_group_id
941 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
942 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
943 AND wip_entity_id = p_wip_entity_id
944 AND organization_id = p_organization_id
945 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
946 AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
947
948 l_scheduling_method number := 0;
949
950 BEGIN
951
952 begin
953 FOR cur_row IN res_info(p_group_id,
954 p_wip_entity_id,
955 p_organization_id) LOOP
956
957 /* update the table */
958 /** Fix for bug 2438722 - correct attribute cols updated **/
959 UPDATE WIP_SUB_OPERATION_RESOURCES
960 SET resource_id = cur_row.resource_id_new,
961 usage_rate_or_amount = cur_row.usage_rate_or_amount,
962 last_update_date = sysdate,/*BUG 6721823*/
963 last_updated_by = cur_row.last_updated_by,
964 creation_date = cur_row.creation_date,
965 created_by = cur_row.created_by,
966 last_update_login = cur_row.last_update_login,
967 request_id = cur_row.request_id,
968 program_application_id = cur_row.program_application_id,
969 program_id = cur_row.program_id,
970 program_update_date = cur_row.program_update_date,
971 scheduled_flag = cur_row.scheduled_flag,
972 assigned_units = cur_row.assigned_units,
973 maximum_assigned_units = cur_row.maximum_assigned_units,
974 applied_resource_units = nvl(cur_row.applied_resource_units,applied_resource_units),
975 applied_resource_value = nvl(cur_row.applied_resource_value,applied_resource_value),
976 uom_code = cur_row.uom_code,
977 basis_type = nvl(cur_row.basis_type, basis_type),
978 activity_id = cur_row.activity_id,
979 autocharge_type = nvl(cur_row.autocharge_type, autocharge_type),
980 standard_rate_flag = nvl(cur_row.standard_rate_flag, standard_rate_flag),
981 start_date = nvl(cur_row.start_date, start_date),
982 completion_date = nvl(cur_row.completion_date, completion_date),
983 attribute_category = NVL(cur_row.attribute_category,
984 attribute_category),
985 attribute1 = NVL(cur_row.attribute1,attribute1),
986 attribute2 = NVL(cur_row.attribute2,attribute2),
987 attribute3 = NVL(cur_row.attribute3,attribute3),
988 attribute4 = NVL(cur_row.attribute4,attribute4),
989 attribute5 = NVL(cur_row.attribute5,attribute5),
990 attribute6 = NVL(cur_row.attribute6,attribute6),
991 attribute7 = NVL(cur_row.attribute7,attribute7),
992 attribute8 = NVL(cur_row.attribute8,attribute8),
993 attribute9 = NVL(cur_row.attribute9,attribute9),
994 attribute10 = NVL(cur_row.attribute10,attribute10),
995 attribute11 = NVL(cur_row.attribute11,attribute11),
996 attribute12 = NVL(cur_row.attribute12,attribute12),
997 attribute13 = NVL(cur_row.attribute13,attribute13),
998 attribute14 = NVL(cur_row.attribute14,attribute14),
999 attribute15 = NVL(cur_row.attribute15,attribute15),
1000 schedule_seq_num = decode(cur_row.schedule_seq_num, fnd_api.g_miss_num, null, cur_row.schedule_seq_num),
1001 substitute_group_num = cur_row.substitute_group_num,
1002 replacement_group_num = cur_row.replacement_group_num
1003 WHERE wip_entity_id = p_wip_entity_id
1004 AND organization_id = p_organization_id
1005 AND operation_seq_num = cur_row.operation_seq_num
1006 AND resource_seq_num = cur_row.resource_seq_num
1007 AND resource_id = cur_row.resource_id_old;
1008
1009 Delete_Resource_Usage(p_wip_entity_id,
1010 p_organization_id,
1011 cur_row.operation_seq_num,
1012 cur_row.resource_seq_num,
1013 p_err_code,
1014 p_err_msg);
1015
1016 END LOOP;
1017
1018 exception
1019 when others then
1020 p_err_msg := 'WIPJDSTB, Change_Sub_Resource: ' || SQLERRM;
1021 p_err_code := SQLCODE;
1022 end;
1023 END Change_Sub_Resource;
1024
1025 Procedure Add_Resource_Instance (p_group_id number,
1026 p_wip_entity_id number,
1027 p_organization_id number,
1028 p_err_code out NOCOPY varchar2,
1029 p_err_msg out NOCOPY varchar2) IS
1030
1031
1032 CURSOR res_info (p_group_id number,
1033 p_wip_entity_id number,
1034 p_organization_id number) IS
1035 SELECT distinct operation_seq_num, resource_seq_num, resource_serial_number,
1036 resource_instance_id, start_date, completion_date, batch_id, interface_id,
1037 created_by, creation_date,last_updated_by,last_update_date
1038 FROM WIP_JOB_DTLS_INTERFACE
1039 WHERE group_id = p_group_id
1040 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1041 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1042 AND wip_entity_id = p_wip_entity_id
1043 AND organization_id = p_organization_id
1044 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
1045 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
1046
1047 BEGIN
1048
1049 begin
1050
1051 /* delete all existing instances on a resource before add */
1052 Delete_Resource_Instance(p_group_id, p_wip_entity_id, p_organization_id,
1053 WIP_JOB_DETAILS.WIP_ADD, p_err_code, p_err_msg);
1054
1055 FOR cur_row IN res_info(p_group_id,
1056 p_wip_entity_id,
1057 p_organization_id) LOOP
1058
1059 /* insert into table */
1060 INSERT INTO WIP_OP_RESOURCE_INSTANCES (
1061 WIP_ENTITY_ID,
1062 OPERATION_SEQ_NUM,
1063 RESOURCE_SEQ_NUM,
1064 ORGANIZATION_ID,
1065 LAST_UPDATE_DATE,
1066 LAST_UPDATED_BY,
1067 CREATION_DATE,
1068 CREATED_BY,
1069 INSTANCE_ID,
1070 SERIAL_NUMBER,
1071 START_DATE,
1072 COMPLETION_DATE,
1073 BATCH_ID
1074 )
1075 VALUES (
1076 p_wip_entity_id,
1077 cur_row.operation_seq_num,
1078 cur_row.resource_seq_num,
1079 p_organization_id,
1080 sysdate,/*BUG 6721823*/
1081 cur_row.last_updated_by,
1082 cur_row.creation_date,
1083 cur_row.created_by,
1084 cur_row.resource_instance_id,
1085 cur_row.resource_serial_number,
1086 cur_row.start_date,
1087 cur_row.completion_date,
1088 cur_row.batch_id
1089 );
1090 end loop;
1091
1092 exception
1093 when others then
1094 p_err_msg := 'WIPJDSTB, Add_Resource_Instance: ' || SQLERRM;
1095 p_err_code := SQLCODE;
1096 end;
1097
1098 END Add_Resource_Instance;
1099
1100 Procedure Change_Resource_Instance(p_group_id number,
1101 p_wip_entity_id number,
1102 p_organization_id number,
1103 p_err_code out NOCOPY varchar2,
1104 p_err_msg out NOCOPY varchar2) IS
1105
1106 CURSOR res_info (p_group_id number,
1107 p_wip_entity_id number,
1108 p_organization_id number) IS
1109 SELECT distinct operation_seq_num, resource_seq_num, resource_serial_number,
1110 resource_instance_id, start_date, completion_date, batch_id,
1111 created_by, creation_date,last_updated_by,last_update_date
1112 FROM WIP_JOB_DTLS_INTERFACE
1113 WHERE group_id = p_group_id
1114 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1115 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1116 AND wip_entity_id = p_wip_entity_id
1117 AND organization_id = p_organization_id
1118 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
1119 AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
1120
1121 l_ret_exp_status boolean := true; --Bug#4675116
1122
1123 BEGIN
1124
1125 begin
1126
1127 FOR cur_row IN res_info(p_group_id,
1128 p_wip_entity_id,
1129 p_organization_id) LOOP
1130
1131 --Bug#4675116
1132 L_RET_EXP_STATUS := WIP_WS_EXCEPTIONS.close_exp_res_instance_update
1133 (
1134 P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
1135 P_OPERATION_SEQ_NUM => CUR_ROW.OPERATION_SEQ_NUM,
1136 P_RESOURCE_SEQ_NUM => CUR_ROW.RESOURCE_SEQ_NUM,
1137 P_INSTANCE_ID => CUR_ROW.RESOURCE_INSTANCE_ID,
1138 P_SERIAL_NUMBER => CUR_ROW.RESOURCE_SERIAL_NUMBER,
1139 P_ORGANIZATION_ID => P_ORGANIZATION_ID
1140 );
1141
1142 UPDATE WIP_OP_RESOURCE_INSTANCES
1143 SET serial_number = nvl(cur_row.resource_serial_number,serial_number),
1144 last_update_date = sysdate,/*BUG 6721823*/
1145 last_updated_by = nvl(cur_row.last_updated_by,last_updated_by),
1146 creation_date = nvl(cur_row.creation_date,creation_date),
1147 created_by = nvl(cur_row.created_by,created_by),
1148 start_date = nvl(cur_row.start_date,start_date),
1149 completion_date = nvl(cur_row.completion_date,completion_date),
1150 batch_id = nvl(cur_row.batch_id,batch_id)
1151 WHERE wip_entity_id = p_wip_entity_id
1152 AND organization_id = p_organization_id
1153 AND operation_seq_num = cur_row.operation_seq_num
1154 AND resource_seq_num = cur_row.resource_seq_num
1155 AND instance_id = cur_row.resource_instance_id;
1156
1157 Delete_Resource_Usage(p_wip_entity_id,
1158 p_organization_id,
1159 cur_row.operation_seq_num,
1160 cur_row.resource_seq_num,
1161 p_err_code,
1162 p_err_msg);
1163
1164
1165 end LOOP;
1166
1167
1168 exception
1169 when others then
1170 p_err_msg := 'WIPJDSTB, Change_Resource_Instance: ' || SQLERRM;
1171 p_err_code := SQLCODE;
1172 end;
1173 End Change_Resource_Instance;
1174
1175 Procedure Delete_Resource_Instance (p_group_id in number,
1176 p_wip_entity_id in number,
1177 p_organization_id in number,
1178 p_substitution_type in number,
1179 p_err_code out NOCOPY varchar2,
1180 p_err_msg out NOCOPY varchar2) IS
1181 CURSOR res_info (p_group_id number,
1182 p_wip_entity_id number,
1183 p_organization_id number) IS
1184 SELECT distinct operation_seq_num, resource_seq_num, resource_serial_number,
1185 resource_instance_id, start_date, completion_date, batch_id
1186 FROM WIP_JOB_DTLS_INTERFACE
1187 WHERE group_id = p_group_id
1188 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1189 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1190 AND wip_entity_id = p_wip_entity_id
1191 AND organization_id = p_organization_id
1192 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
1193 AND substitution_type = p_substitution_type;
1194
1195 l_ret_exp_status boolean := true; --Bug#4675116
1196
1197 begin
1198
1199 begin
1200 FOR cur_row IN res_info(p_group_id,
1201 p_wip_entity_id,
1202 p_organization_id) LOOP
1203
1204 Delete_Resource_Usage(p_wip_entity_id,
1205 p_organization_id,
1206 cur_row.operation_seq_num,
1207 cur_row.resource_seq_num,
1208 p_err_code,
1209 p_err_msg);
1210
1211 --BUG#4675116
1212 L_RET_EXP_STATUS := WIP_WS_EXCEPTIONS.CLOSE_EXCEPTION_RES_INSTANCE
1213 (
1214 P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
1215 P_OPERATION_SEQ_NUM => CUR_ROW.OPERATION_SEQ_NUM,
1216 P_RESOURCE_SEQ_NUM => CUR_ROW.RESOURCE_SEQ_NUM,
1217 P_INSTANCE_ID => CUR_ROW.RESOURCE_INSTANCE_ID,
1218 P_SERIAL_NUMBER => CUR_ROW.RESOURCE_SERIAL_NUMBER,
1219 P_ORGANIZATION_ID => P_ORGANIZATION_ID
1220 );
1221
1222 DELETE FROM WIP_OP_RESOURCE_INSTANCES
1223 WHERE wip_entity_id = p_wip_entity_id
1224 AND organization_id = p_organization_id
1225 AND operation_seq_num = cur_row.operation_seq_num
1226 AND resource_seq_num = cur_row.resource_seq_num
1227 AND instance_id = cur_row.resource_instance_id;
1228
1229 END LOOP;
1230
1231 exception
1232 when others then
1233 p_err_msg := 'WIPJDSTB, Delete_Resource_Instance: ' || SQLERRM;
1234 p_err_code := SQLCODE;
1235 end;
1236
1237 END Delete_Resource_Instance;
1238
1239
1240 Procedure Delete_Requirement (p_group_id number,
1241 p_wip_entity_id number,
1242 p_organization_id number,
1243 p_err_code out NOCOPY varchar2,
1244 p_err_msg out NOCOPY varchar2) IS
1245
1246
1247 CURSOR req_info(p_group_Id number,
1248 p_wip_entity_id number,
1249 p_organization_id number) IS
1250 SELECT distinct operation_seq_num,
1251 inventory_item_id_old, inventory_item_id_new,
1252 quantity_per_assembly,
1253 last_update_date, last_updated_by, creation_date, created_by,
1254 last_update_login, request_id, program_application_id,
1255 program_id, program_update_date,
1256 department_id, wip_supply_type, date_required,
1257 required_quantity, quantity_issued, supply_subinventory,
1258 supply_locator_id, mrp_net_flag, mps_required_quantity,
1259 mps_date_required, attribute_category, attribute1,
1260 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1261 attribute8,attribute9,attribute10,attribute11,attribute12,
1262 attribute13,attribute14,attribute15
1263 FROM WIP_JOB_DTLS_INTERFACE
1264 WHERE group_id = p_group_id
1265 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1266 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1267 AND wip_entity_id = p_wip_entity_id
1268 AND organization_id = p_organization_id
1269 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1270 AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
1271
1272 x_return_status VARCHAR(1);
1273 x_msg_data VARCHAR(2000);
1274 l_dummy VARCHAR(1);
1275 BEGIN
1276 x_return_status := fnd_api.g_ret_sts_success;
1277
1278 begin
1279 FOR cur_row IN req_info(p_group_id,
1280 p_wip_entity_id,
1281 p_organization_id) LOOP
1282
1283 DELETE FROM WIP_REQUIREMENT_OPERATIONS
1284 WHERE wip_entity_id = p_wip_entity_id
1285 AND organization_id = p_organization_id
1286 AND operation_seq_num = cur_row.operation_seq_num
1287 AND inventory_item_id = cur_row.inventory_item_id_old;
1288
1289 wip_picking_pvt.cancel_comp_allocations(p_wip_entity_id => p_wip_entity_id,
1290 p_operation_seq_num => cur_row.operation_seq_num,
1291 p_inventory_item_id => cur_row.inventory_item_id_old,
1292 p_wip_entity_type => wip_constants.discrete,
1293 x_return_status => x_return_status,
1294 x_msg_data => x_msg_data);
1295
1296 if (x_return_status <> fnd_api.g_ret_sts_success) then
1297 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1298 end if;
1299
1300 END LOOP;
1301 exception
1302 when others then
1303 p_err_msg := 'WIPJDSTB, Delete_Requirement: ' || SQLERRM;
1304 p_err_code := SQLCODE;
1305 wip_jsi_utils.record_error_text(p_err_msg, TRUE);
1306 end;
1307
1308 END Delete_Requirement;
1309
1310
1311 Procedure Add_Requirement (p_group_id number,
1312 p_wip_entity_id number,
1313 p_organization_id number,
1314 p_err_code out NOCOPY varchar2,
1315 p_err_msg out NOCOPY varchar2) IS
1316
1317
1318 CURSOR req_info(p_group_Id number,
1319 p_wip_entity_id number,
1320 p_organization_id number) IS
1321 SELECT distinct operation_seq_num,
1322 inventory_item_id_old, inventory_item_id_new,
1323 quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1324 last_update_date, last_updated_by, creation_date, created_by,
1325 last_update_login, request_id, program_application_id,
1326 program_id, program_update_date,
1327 department_id, wip_supply_type, date_required,
1328 required_quantity, quantity_issued,
1329 basis_type, /* LBM Project */
1330 supply_subinventory,
1331 supply_locator_id, mrp_net_flag, mps_required_quantity,
1332 mps_date_required, auto_request_material, comments,
1333 attribute_category, attribute1,
1334 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1335 attribute8,attribute9,attribute10,attribute11,attribute12,
1336 attribute13,attribute14,attribute15
1337 FROM WIP_JOB_DTLS_INTERFACE
1338 WHERE group_id = p_group_id
1339 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1340 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1341 AND wip_entity_id = p_wip_entity_id
1342 AND organization_id = p_organization_id
1343 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1344 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
1345
1346 l_material_issue_by_mo VARCHAR2(1);
1347 BEGIN
1348
1349 begin
1350 FOR cur_row IN req_info(p_group_id,
1351 p_wip_entity_id,
1352 p_organization_id) LOOP
1353
1354 /** Fix for bug 2649338
1355 -- stripped off time part from date_required and mps_date_required **/
1356 INSERT INTO WIP_REQUIREMENT_OPERATIONS(
1357 wip_entity_id,
1358 organization_id,
1359 operation_seq_num,
1360 inventory_item_id,
1361 quantity_per_assembly,
1362 component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1363 last_update_date,
1364 last_updated_by,
1365 creation_date,
1366 created_by,
1367 last_update_login,
1368 request_id,
1369 program_application_id,
1370 program_id,
1371 program_update_date,
1372 department_id,
1373 wip_supply_type,
1374 date_required,
1375 required_quantity,
1376 quantity_issued,
1377 basis_type, /* LBM Project */
1378 supply_subinventory ,
1379 supply_locator_id,
1380 mrp_net_flag,
1381 mps_required_quantity,
1382 mps_date_required,
1383 auto_request_material,
1384 comments,
1385 attribute_category,
1386 attribute1,
1387 attribute2,
1388 attribute3,
1389 attribute4,
1390 attribute5,
1391 attribute6,
1392 attribute7,
1393 attribute8,
1394 attribute9,
1395 attribute10,
1396 attribute11,
1397 attribute12,
1398 attribute13,
1399 attribute14,
1400 attribute15)
1401 VALUES (
1402 p_wip_entity_id,
1403 p_organization_id,
1404 cur_row.operation_seq_num,
1405 cur_row.inventory_item_id_new,
1406 round(cur_row.quantity_per_assembly, 6),
1407 cur_row.component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1408 sysdate,/*BUG 6721823*/
1409 cur_row.last_updated_by,
1410 cur_row.creation_date,
1411 cur_row.created_by,
1412 cur_row.last_update_login,
1413 cur_row.request_id,
1414 cur_row.program_application_id,
1415 cur_row.program_id,
1416 cur_row.program_update_date,
1417 cur_row.department_id,
1418 cur_row.wip_supply_type,
1419 cur_row.date_required,
1420 cur_row.required_quantity,
1421 cur_row.quantity_issued,
1422 cur_row.basis_type, /* LBM Project */
1423 cur_row.supply_subinventory ,
1424 cur_row.supply_locator_id,
1425 cur_row.mrp_net_flag,
1426 cur_row.mps_required_quantity,
1427 cur_row.mps_date_required,
1428 cur_row.auto_request_material,
1429 cur_row.comments,
1430 cur_row.attribute_category,
1431 cur_row.attribute1,
1432 cur_row.attribute2,
1433 cur_row.attribute3,
1434 cur_row.attribute4,
1435 cur_row.attribute5,
1436 cur_row.attribute6,
1437 cur_row.attribute7,
1438 cur_row.attribute8,
1439 cur_row.attribute9,
1440 cur_row.attribute10,
1441 cur_row.attribute11,
1442 cur_row.attribute12,
1443 cur_row.attribute13,
1444 cur_row.attribute14,
1445 cur_row.attribute15);
1446
1447 END LOOP;
1448 exception
1449 when others then
1450 p_err_msg := 'WIPJDSTB, Add_Requirement: ' || SQLERRM;
1451 p_err_code := SQLCODE;
1452 wip_jsi_utils.record_error_text(p_err_msg, TRUE);
1453 end;
1454
1455 END Add_Requirement;
1456
1457
1458 Procedure Change_Requirement (p_group_id number,
1459 p_wip_entity_id number,
1460 p_organization_id number,
1461 p_err_code out NOCOPY varchar2,
1462 p_err_msg out NOCOPY varchar2) IS
1463
1464 CURSOR req_info(p_group_Id number,
1465 p_wip_entity_id number,
1466 p_organization_id number) IS
1467 SELECT distinct operation_seq_num,
1468 inventory_item_id_old, inventory_item_id_new,
1469 quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1470 last_update_date, last_updated_by, creation_date, created_by,
1471 last_update_login, request_id, program_application_id,
1472 program_id, program_update_date,
1473 department_id, wip_supply_type, date_required,
1474 required_quantity, quantity_issued,
1475 basis_type, /* LBM Project */
1476 supply_subinventory,
1477 supply_locator_id, mrp_net_flag, mps_required_quantity,
1478 mps_date_required, auto_request_material, comments,
1479 attribute_category, attribute1,
1480 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1481 attribute8,attribute9,attribute10,attribute11,attribute12,
1482 attribute13,attribute14,attribute15
1483 FROM WIP_JOB_DTLS_INTERFACE
1484 WHERE group_id = p_group_id
1485 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1486 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1487 AND wip_entity_id = p_wip_entity_id
1488 AND organization_id = p_organization_id
1489 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1490 AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
1491
1492 l_material_issue_by_mo VARCHAR2(1);
1493 x_return_status VARCHAR2(1);
1494 x_msg_data VARCHAR2(2000);
1495 l_required_quantity NUMBER;
1496 l_wip_supply_type NUMBER;
1497 l_supply_subinventory VARCHAR(30);
1498 l_supply_locator_id NUMBER;
1499 l_dummy VARCHAR2(1);
1500
1501 l_ret_exp_status boolean := true; --Bug#4675116
1502
1503 BEGIN
1504
1505 begin
1506 FOR cur_row IN req_info(p_group_id,
1507 p_wip_entity_id,
1508 p_organization_id) LOOP
1509
1510 SELECT required_quantity, wip_supply_type, supply_subinventory, supply_locator_id
1511 into l_required_quantity, l_wip_supply_type, l_supply_subinventory, l_supply_locator_id
1512 FROM wip_requirement_operations
1513 WHERE wip_entity_id = p_wip_entity_id
1514 AND organization_id = p_organization_id
1515 AND operation_seq_num = cur_row.operation_seq_num
1516 AND inventory_item_id = cur_row.inventory_item_id_old;
1517
1518 If (l_required_quantity <> cur_row.required_quantity AND
1519 (cur_row.inventory_item_id_new is NULL or
1520 cur_row.inventory_item_id_old = cur_row.inventory_item_id_new) and
1521 WIP_PICKING_PUB.Is_Component_Pick_Released(p_wip_entity_id => p_wip_entity_id,
1522 p_org_id => p_organization_id,
1523 p_operation_seq_num => cur_row.operation_seq_num,
1524 p_inventory_item_id => cur_row.inventory_item_id_old)) then
1525
1526 FND_MESSAGE.set_name('WIP', 'WIP_QTY_REQ_CHANGE_WARNING');
1527 wip_jsi_utils.record_current_error(TRUE) ;
1528
1529 wip_picking_pub.Update_Component_BackOrdQty(p_wip_entity_id => p_wip_entity_id,
1530 p_operation_seq_num => cur_row.operation_seq_num,
1531 p_new_component_qty => cur_row.required_quantity,
1532 p_inventory_item_id => cur_row.inventory_item_id_old,
1533 x_return_status => x_return_status,
1534 x_msg_data => x_msg_data);
1535 if (x_return_status <> fnd_api.g_ret_sts_success) then
1536 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1537 end if;
1538
1539 elsif ( (l_wip_supply_type <> cur_row.wip_supply_type or
1540 l_supply_subinventory <> cur_row.supply_subinventory or
1541 l_supply_locator_id <> cur_row.supply_locator_id ) and
1542 (cur_row.inventory_item_id_new is NULL or
1543 cur_row.inventory_item_id_old = cur_row.inventory_item_id_new) and
1544 WIP_PICKING_PUB.Is_Component_Pick_Released(p_wip_entity_id => p_wip_entity_id,
1545 p_org_id => p_organization_id,
1546 p_operation_seq_num => cur_row.operation_seq_num,
1547 p_inventory_item_id => cur_row.inventory_item_id_old)) then
1548
1549 WIP_PICKING_PUB.cancel_comp_allocations(p_wip_entity_id => p_wip_entity_id,
1550 p_operation_seq_num => cur_row.operation_seq_num,
1551 p_inventory_item_id => cur_row.inventory_item_id_old,
1552 p_wip_entity_type => WIP_CONSTANTS.DISCRETE,
1553 x_return_status => x_return_status,
1554 x_msg_data => x_msg_data);
1555
1556 FND_MESSAGE.set_name('WIP', 'WIP_SUPPLY_CHANGE_WARNING2');
1557 wip_jsi_utils.record_current_error(TRUE);
1558 end if;
1559
1560 --Bug#4675116
1561 IF (CUR_ROW.INVENTORY_ITEM_ID_OLD <> CUR_ROW.INVENTORY_ITEM_ID_NEW) THEN
1562 L_RET_EXP_STATUS := WIP_WS_EXCEPTIONS.CLOSE_EXCEPTION_COMPONENT
1563 (
1564 P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
1565 P_OPERATION_SEQ_NUM => CUR_ROW.OPERATION_SEQ_NUM,
1566 P_COMPONENT_ITEM_ID => CUR_ROW.INVENTORY_ITEM_ID_OLD,
1567 P_ORGANIZATION_ID => P_ORGANIZATION_ID
1568 );
1569 END IF;
1570
1571
1572 /* update table */
1573 /** Fix for bug 2438722 - correct attribute cols updated **/
1574 /** Fix for bug 2649338
1575 -- stripped off time part from date_required and mps_date_required **/
1576 UPDATE WIP_REQUIREMENT_OPERATIONS
1577 SET inventory_item_id = NVL(cur_row.inventory_item_id_new,
1578 inventory_item_id),
1579 quantity_per_assembly = NVL(round(
1580 cur_row.quantity_per_assembly,6),
1581 quantity_per_assembly),
1582 component_yield_factor = NVL(cur_row.component_yield_factor,
1583 component_yield_factor),/*Component Yield Enhancement(Bug 4369064)*/
1584
1585 /* LBM Project: if user wants to change the basis_type to null (item basis), he needs to insert fnd_api.g_miss_num into interface table. This should be in the interface user guide */
1586 /* Bug 5468646 - update component basis */
1587 basis_type = decode(cur_row.basis_type, fnd_api.g_miss_num, null, null, basis_type, cur_row.basis_type),
1588 last_update_date = sysdate,/*BUG 6721823*/
1589 last_updated_by = cur_row.last_updated_by,
1590 creation_date = cur_row.creation_date,
1591 created_by = cur_row.created_by,
1592 last_update_login = NVL(cur_row.last_update_login,
1593 last_update_login),
1594 request_id = NVL(cur_row.request_id,
1595 request_id),
1596 program_application_id = NVL(cur_row.program_application_id,
1597 program_application_id),
1598 program_id = NVL(cur_row.program_id,
1599 program_id),
1600 program_update_date = NVL(cur_row.program_update_date,
1601 program_update_date),
1602 department_id = NVL(cur_row.department_id,
1603 department_id),
1604 wip_supply_type = NVL(cur_row.wip_supply_type,
1605 wip_supply_type),
1606 date_required = NVL(cur_row.date_required,
1607 date_required),
1608 required_quantity = NVL(cur_row.required_quantity,
1609 required_quantity),
1610 /* Bug 4887280 - modify decode statement for supply_subinventory, and supply_locator */
1611 supply_subinventory = decode(nvl(cur_row.wip_supply_type, 4),
1612 1, --push
1613 decode(cur_row.supply_subinventory,
1614 NULL,
1615 supply_subinventory,
1616 fnd_api.g_miss_char,
1617 NULL,
1618 cur_row.supply_subinventory),
1619 nvl(cur_row.supply_subinventory, supply_subinventory)),
1620 supply_locator_id = decode(nvl(cur_row.wip_supply_type, 4),
1621 1, --push
1622 decode(cur_row.supply_locator_id,
1623 NULL,
1624 supply_locator_id,
1625 fnd_api.g_miss_num,
1626 NULL,
1627 cur_row.supply_locator_id),
1628 nvl(cur_row.supply_locator_id, supply_locator_id)),
1629 mrp_net_flag = NVL(cur_row.mrp_net_flag,
1630 mrp_net_flag),
1631 mps_required_quantity = NVL(cur_row.mps_required_quantity,
1632 mps_required_quantity),
1633 mps_date_required = NVL(cur_row.mps_date_required,
1634 mps_date_required),
1635 auto_request_material = NVL( cur_row.auto_request_material,
1636 auto_request_material),
1637 comments = NVL( cur_row.comments, comments),
1638 attribute_category = NVL(cur_row.attribute_category,
1639 attribute_category),
1640 attribute1 = NVL(cur_row.attribute1,attribute1),
1641 attribute2 = NVL(cur_row.attribute2,attribute2),
1642 attribute3 = NVL(cur_row.attribute3,attribute3),
1643 attribute4 = NVL(cur_row.attribute4,attribute4),
1644 attribute5 = NVL(cur_row.attribute5,attribute5),
1645 attribute6 = NVL(cur_row.attribute6,attribute6),
1646 attribute7 = NVL(cur_row.attribute7,attribute7),
1647 attribute8 = NVL(cur_row.attribute8,attribute8),
1648 attribute9 = NVL(cur_row.attribute9,attribute9),
1649 attribute10 = NVL(cur_row.attribute10,attribute10),
1650 attribute11 = NVL(cur_row.attribute11,attribute11),
1651 attribute12 = NVL(cur_row.attribute12,attribute12),
1652 attribute13 = NVL(cur_row.attribute13,attribute13),
1653 attribute14 = NVL(cur_row.attribute14,attribute14),
1654 attribute15 = NVL(cur_row.attribute15,attribute15)
1655 WHERE wip_entity_id = p_wip_entity_id
1656 AND organization_id = p_organization_id
1657 AND operation_seq_num = cur_row.operation_seq_num
1658 AND inventory_item_id = cur_row.inventory_item_id_old;
1659
1660 END LOOP;
1661 exception
1662 when others then
1663 p_err_msg := 'ERROR IN WIPJDSTB.PLS: CHANGE_REQ ' || SQLERRM;
1664 p_err_code := SQLCODE;
1665 wip_jsi_utils.record_error_text(p_err_msg, TRUE);
1666 end;
1667
1668 END Change_Requirement;
1669
1670 Procedure Add_Operation (p_group_id in number,
1671 p_wip_entity_id in number,
1672 p_organization_id in number,
1673 x_err_code out NOCOPY varchar2,
1674 x_err_msg out NOCOPY varchar2 ,
1675 x_return_status out NOCOPY varchar2) IS
1676
1677 CURSOR oper_info ( p_group_id number,
1678 p_wip_entity_id number,
1679 p_organization_id number) IS
1680 SELECT distinct parent_header_id, operation_seq_num, standard_operation_id,
1681 department_id, description , first_unit_start_date,
1682 first_unit_completion_date, last_unit_start_date,
1683 last_unit_completion_date, minimum_transfer_quantity,
1684 count_point_type, backflush_flag,last_update_date,
1685 last_updated_by, creation_date,created_by, last_update_login,
1686 request_id, program_application_id, program_id, program_update_date,
1687 long_description,
1688 attribute_category, attribute1, attribute2, attribute3,
1689 attribute4, attribute5,
1690 attribute6, attribute7, attribute8, attribute9, attribute10,
1691 attribute11, attribute12, attribute13, attribute14, attribute15
1692 FROM WIP_JOB_DTLS_INTERFACE
1693 WHERE group_id = p_group_id
1694 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1695 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1696 AND wip_entity_id = p_wip_entity_id
1697 AND organization_id = p_organization_id
1698 AND load_type = WIP_JOB_DETAILS.WIP_OPERATION
1699 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
1700
1701 CURSOR operations(p_wip_entity_id number,
1702 p_organization_id number) IS
1703 SELECT operation_seq_num
1704 FROM wip_operations
1705 WHERE wip_entity_id = p_wip_entity_id
1706 AND organization_id = p_organization_id
1707 ORDER BY operation_seq_num ;
1708
1709 l_scheduled_quantity number := 0;
1710 l_quantity_in_queue number := 0;
1711 l_first_operation number := 0;
1712 l_qty_in_queue_old number := 0;
1713 previous_operation number := 0;
1714 next_operation number := 0;
1715 l_open_quantity number;
1716 l_scrap_qty number := 0;
1717 l_status_type number; /*Bug 3484856*/
1718 l_load_type number; /*Bug 3484856*/
1719 l_scheduled_start_date date;/* Bug 3659006*/
1720 l_scheduled_completion_date date;/* Bug 3659006*/
1721 l_first_unit_start_date date; /* Bug 6132987 (FP of 5886171) */
1722 l_last_unit_completion_date date; /* Bug 6132987 (FP of 5886171) */
1723
1724 BEGIN
1725 begin
1726
1727 IF p_group_id IS NULL OR p_organization_id IS NULL OR
1728 p_wip_entity_id IS NULL THEN
1729
1730 x_err_code := SQLCODE;
1731 x_err_msg := 'Error in wipjdstb.pls'|| SQLERRM;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 return;
1734 END IF;
1735
1736 FOR cur_oper IN oper_info(p_group_id,p_wip_entity_id,p_organization_id) LOOP
1737
1738 -- Bug 3484856 - select the status of job and load type from wjsi
1739 SELECT wdj.start_quantity, greatest(wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped, 0), wdj.status_type, we.load_type
1740 INTO l_scheduled_quantity, l_open_quantity,l_status_type, l_load_type
1741 FROM wip_discrete_jobs wdj,wip_job_schedule_interface we
1742 WHERE wdj.wip_entity_id = p_wip_entity_id
1743 AND wdj.organization_id = p_organization_id
1744 AND wdj.wip_entity_id = we.wip_entity_id (+)
1745 AND wdj.organization_id = we.organization_id (+)
1746 AND we.group_id = p_group_id
1747 AND we.process_phase = WIP_CONSTANTS.ML_VALIDATION
1748 AND we.process_status in ( WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING )
1749 AND we.header_id = cur_oper.parent_header_id;
1750
1751 begin
1752 SELECT min(operation_seq_num)
1753 INTO l_first_operation
1754 FROM WIP_OPERATIONS
1755 WHERE wip_entity_id = p_wip_entity_id
1756 AND organization_id = p_organization_id;
1757 exception
1758 when no_data_found then
1759 null;
1760 end;
1761
1762 -- Bug 3484856 - If job is 'Unreleased' or creating a work-order,
1763 -- then quantity_in_queue should be 0 else create the operation with
1764 -- open qty in queue
1765 IF l_first_operation is null then
1766 if l_status_type = WIP_CONSTANTS.UNRELEASED or
1767 ( l_load_type is NOT NULL and
1768 l_load_type in ( WIP_CONSTANTS.CREATE_JOB,
1769 WIP_CONSTANTS.CREATE_NS_JOB,
1770 WIP_CONSTANTS.CREATE_EAM_JOB ) ) then
1771 l_quantity_in_queue := 0;
1772 else
1773 l_quantity_in_queue := l_open_quantity;
1774 end if;
1775
1776 /* Fix for bug 4273638: Since the operation being added is the
1777 first operation,move the components currently under op-seq 1
1778 to the operation being added */
1779 WIP_OPERATIONS_UTILITIES.Update_Operationless_Reqs
1780 (p_wip_entity_id,
1781 p_organization_id,
1782 cur_oper.operation_seq_num,
1783 NULL, /* repetitive schedule id */
1784 cur_oper.department_id,
1785 cur_oper.first_unit_start_date);
1786
1787
1788 ELSIF l_first_operation > cur_oper.operation_seq_num THEN
1789
1790 select quantity_in_queue into l_quantity_in_queue
1791 from wip_operations
1792 where wip_entity_id = p_wip_entity_id
1793 and organization_id = p_organization_id
1794 and operation_seq_num = l_first_operation;
1795
1796 -- need to erase the quantity from the current 1st op or
1797 -- else we are creating duplicate qty
1798 update wip_operations
1799 set quantity_in_queue = 0,
1800 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
1801 where wip_entity_id = p_wip_entity_id
1802 and organization_id = p_organization_id
1803 and operation_seq_num = l_first_operation;
1804
1805 ELSE
1806 l_quantity_in_queue := 0;
1807
1808 END IF;
1809
1810 IF cur_oper.standard_operation_id is not null THEN
1811 wip_operations_pkg.add(p_organization_id,
1812 p_wip_entity_id,
1813 cur_oper.operation_seq_num,
1814 cur_oper.standard_operation_id,
1815 cur_oper.department_id);
1816 UPDATE WIP_OPERATIONS
1817 SET last_update_date = sysdate,/*BUG 6721823*/
1818 last_updated_by = NVL(cur_oper.last_updated_by,last_updated_by),
1819 creation_date = NVL(cur_oper.creation_date,creation_date),
1820 created_by = NVL(cur_oper.created_by, created_by),
1821 last_update_login = NVL(cur_oper.last_update_login,last_update_login),
1822 request_id = NVL(cur_oper.request_id ,request_id),
1823 program_application_id =NVL(cur_oper.program_application_id,program_application_id),
1824 program_id = NVL(cur_oper.program_id,program_id),
1825 program_update_date = NVL(cur_oper.program_update_date,
1826 program_update_date),
1827 description = NVL(cur_oper.description,description),
1828 first_unit_start_date = NVL(cur_oper.first_unit_start_date,
1829 first_unit_start_date),
1830 first_unit_completion_date =NVL(cur_oper.first_unit_completion_date,
1831 first_unit_completion_date),
1832 last_unit_start_date = NVL(cur_oper.last_unit_start_date,
1833 last_unit_start_date),
1834 last_unit_completion_date = NVL(cur_oper.last_unit_completion_date,
1835 last_unit_completion_date),
1836 count_point_type = NVL(cur_oper.count_point_type,count_point_type),
1837 backflush_flag = NVL(cur_oper.backflush_flag,backflush_flag),
1838 minimum_transfer_quantity = NVL(cur_oper.minimum_transfer_quantity,
1839 minimum_transfer_quantity),
1840 long_description = NVL(cur_oper.long_description,
1841 long_description),
1842 attribute_category = NVL(cur_oper.attribute_category,
1843 attribute_category),
1844 attribute1 = NVL(cur_oper.attribute1,attribute1),
1845 attribute2 = NVL(cur_oper.attribute2,attribute2),
1846 attribute3 = NVL(cur_oper.attribute3,attribute3),
1847 attribute4 = NVL(cur_oper.attribute4,attribute4),
1848 attribute5 = NVL(cur_oper.attribute5,attribute5),
1849 attribute6 = NVL(cur_oper.attribute6,attribute6),
1850 attribute7 = NVL(cur_oper.attribute7,attribute7),
1851 attribute8 = NVL(cur_oper.attribute8,attribute8),
1852 attribute9 = NVL(cur_oper.attribute9,attribute9),
1853 attribute10 = NVL(cur_oper.attribute10,attribute10),
1854 attribute11 = NVL(cur_oper.attribute11,attribute11),
1855 attribute12 = NVL(cur_oper.attribute12,attribute12),
1856 attribute13 = NVL(cur_oper.attribute13,attribute13),
1857 attribute14 = NVL(cur_oper.attribute14,attribute14),
1858 attribute15 = NVL(cur_oper.attribute15,attribute15),
1859 quantity_in_queue = l_quantity_in_queue
1860 where wip_entity_id = p_wip_entity_id
1861 and organization_id = p_organization_id
1862 and operation_seq_num = cur_oper.operation_seq_num
1863 /* Bug 6132987 (FP of 5886171) */
1864 RETURNING first_unit_start_date, last_unit_completion_date
1865 INTO l_first_unit_start_date, l_last_unit_completion_date;
1866
1867 /* Bug 6132987 (FP of 5886171) - The scheduling dates are updated in WO in the statement above but they are
1868 not updated in WOR.This causes validation to fail in verify_operation, if the resource dates are outside
1869 the operation date ranges.
1870 So compare the WOR dates with the WO dates and update the WOR dates so that the validation does not fail */
1871 DECLARE
1872 CURSOR C_WOR IS
1873 select start_date, completion_date, rowid
1874 from wip_operation_resources wor
1875 where wor.wip_entity_id = p_wip_entity_id
1876 and wor.organization_id = p_organization_id
1877 and wor.operation_seq_num = cur_oper.operation_seq_num;
1878
1879 l_wor_start_date DATE := null;
1880 l_wor_completion_date DATE := null;
1881 BEGIN
1882 FOR rec in C_WOR LOOP
1883 l_wor_start_date := null;
1884 l_wor_completion_date := null;
1885
1886 IF (l_first_unit_start_date > rec.start_date) THEN
1887 l_wor_start_date := l_first_unit_start_date;
1888 END IF;
1889
1890 IF (l_last_unit_completion_date < rec.completion_date) THEN
1891 l_wor_completion_date := l_last_unit_completion_date;
1892 END IF;
1893
1894 IF ((l_wor_start_date IS NOT NULL) OR (l_wor_completion_date IS NOT NULL)) THEN
1895 UPDATE WIP_OPERATION_RESOURCES
1896 SET start_date = nvl(l_wor_start_date, start_date),
1897 completion_date = nvl(l_wor_completion_date, completion_date),
1898 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
1899 WHERE rowid = rec.rowid;
1900 END IF;
1901 END LOOP;
1902 END;
1903 /* Bug 6132987 (FP of 5886171) */
1904
1905
1906 ELSE
1907
1908 /* For Enhancement#2864382. Calculate cumulative_scrap_quantity for this operation */
1909 IF cur_oper.operation_seq_num > l_first_operation THEN
1910 SELECT SUM(quantity_scrapped)
1911 INTO l_scrap_qty
1912 FROM wip_operations
1913 WHERE organization_id = p_organization_id
1914 AND wip_entity_id = p_wip_entity_id
1915 AND operation_seq_num < cur_oper.operation_seq_num;
1916 END IF;
1917
1918
1919 INSERT INTO WIP_OPERATIONS
1920 ( wip_entity_id,
1921 operation_seq_num,
1922 organization_id,
1923 last_update_date,
1924 last_updated_by,
1925 creation_date,
1926 created_by,
1927 last_update_login,
1928 request_id,
1929 program_application_id,
1930 program_id,
1931 program_update_date,
1932 standard_operation_id,
1933 department_id,
1934 description,
1935 scheduled_quantity,
1936 quantity_in_queue,
1937 quantity_running,
1938 quantity_waiting_to_move,
1939 quantity_rejected,
1940 quantity_scrapped,
1941 quantity_completed,
1942 cumulative_scrap_quantity, /* for 2864382 */
1943 first_unit_start_date,
1944 first_unit_completion_date,
1945 last_unit_start_date,
1946 last_unit_completion_date,
1947 count_point_type,
1948 backflush_flag,
1949 minimum_transfer_quantity,
1950 long_description,
1951 attribute_category,
1952 attribute1,
1953 attribute2,
1954 attribute3,
1955 attribute4,
1956 attribute5,
1957 attribute6,
1958 attribute7,
1959 attribute8,
1960 attribute9,
1961 attribute10,
1962 attribute11,
1963 attribute12,
1964 attribute13,
1965 attribute14,
1966 attribute15)
1967 VALUES
1968 ( p_wip_entity_id,
1969 cur_oper.operation_seq_num,
1970 p_organization_id,
1971 sysdate,/*BUG 6721823*/
1972 cur_oper.last_updated_by,
1973 cur_oper.creation_date,
1974 cur_oper.created_by,
1975 cur_oper.last_update_login,
1976 cur_oper.request_id,
1977 cur_oper.program_application_id,
1978 cur_oper.program_id,
1979 cur_oper.program_update_date,
1980 cur_oper.standard_operation_id,
1981 cur_oper.department_id,
1982 cur_oper.description,
1983 l_scheduled_quantity,
1984 l_quantity_in_queue,
1985 0,0,0,0,0,
1986 l_scrap_qty,
1987 cur_oper.first_unit_start_date,
1988 cur_oper.first_unit_completion_date,
1989 cur_oper.last_unit_start_date,
1990 cur_oper.last_unit_completion_date,
1991 cur_oper.count_point_type,
1992 cur_oper.backflush_flag,
1993 cur_oper.minimum_transfer_quantity,
1994 cur_oper.long_description,
1995 cur_oper.attribute_category,
1996 cur_oper.attribute1,
1997 cur_oper.attribute2,
1998 cur_oper.attribute3,
1999 cur_oper.attribute4,
2000 cur_oper.attribute5,
2001 cur_oper.attribute6,
2002 cur_oper.attribute7,
2003 cur_oper.attribute8,
2004 cur_oper.attribute9,
2005 cur_oper.attribute10,
2006 cur_oper.attribute11,
2007 cur_oper.attribute12,
2008 cur_oper.attribute13,
2009 cur_oper.attribute14,
2010 cur_oper.attribute15);
2011
2012 END IF;
2013
2014 /* Bug 3659006 ->modify the job start/end to allow adding operation outside the start/end
2015 of job */
2016
2017 select scheduled_start_date,scheduled_completion_date
2018 into l_scheduled_start_date,l_scheduled_completion_date
2019 from wip_discrete_jobs
2020 where wip_entity_id = p_wip_entity_id
2021 AND organization_id = p_organization_id;
2022
2023 IF (cur_oper.first_unit_start_date is not null
2024 AND cur_oper.first_unit_start_date < l_scheduled_start_date)
2025 THEN
2026 UPDATE wip_discrete_jobs
2027 set scheduled_start_date = cur_oper.first_unit_start_date
2028 where wip_entity_id = p_wip_entity_id
2029 AND organization_id = p_organization_id;
2030 END IF;
2031 IF (cur_oper.last_unit_completion_date is not null
2032 AND cur_oper.last_unit_completion_date > l_scheduled_completion_date)
2033 THEN
2034 UPDATE wip_discrete_jobs
2035 set scheduled_completion_date = cur_oper.last_unit_completion_date
2036 where wip_entity_id = p_wip_entity_id
2037 AND organization_id = p_organization_id;
2038 END IF;
2039 /*END bug 3659006 */
2040
2041 END LOOP;
2042
2043 FOR each_oper IN OPERATIONS(p_wip_entity_id,p_organization_id) LOOP
2044
2045 IF previous_operation = 0 then
2046
2047 UPDATE WIP_OPERATIONS
2048 SET PREVIOUS_OPERATION_SEQ_NUM = NULL,
2049 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
2050 WHERE wip_entity_id = p_wip_entity_id
2051 AND organization_id = p_organization_id
2052 AND operation_seq_num = each_oper.operation_seq_num;
2053
2054 previous_operation := each_oper.operation_seq_num;
2055
2056 ELSE
2057
2058 UPDATE WIP_OPERATIONS
2059 SET PREVIOUS_OPERATION_SEQ_NUM = previous_operation,
2060 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
2061 WHERE wip_entity_id = p_wip_entity_id
2062 AND organization_id = p_organization_id
2063 AND operation_seq_num = each_oper.operation_seq_num;
2064
2065 UPDATE WIP_OPERATIONS
2066 SET NEXT_OPERATION_SEQ_NUM = each_oper.operation_seq_num,
2067 LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
2068 WHERE wip_entity_id = p_wip_entity_id
2069 AND organization_id = p_organization_id
2070 AND operation_seq_num = previous_operation;
2071
2072 -- Fix for Bug#2246970
2073
2074 previous_operation := each_oper.operation_seq_num;
2075
2076 END IF;
2077 END LOOP;
2078
2079 exception
2080 when others then
2081 x_return_status := FND_API.G_RET_STS_ERROR;
2082 x_err_code := SQLCODE;
2083 x_err_msg := 'ERROR IN WIPJDSTB : '||SQLERRM;
2084 return;
2085 end;
2086
2087 END ADD_OPERATION;
2088
2089 Procedure Change_Operation (p_group_id in number,
2090 p_wip_entity_id in number,
2091 p_organization_id in number,
2092 x_err_code out NOCOPY varchar2,
2093 x_err_msg out NOCOPY varchar2,
2094 x_return_status out NOCOPY varchar2) IS
2095
2096 CURSOR oper_info ( p_group_id number,
2097 p_wip_entity_id number,
2098 p_organization_id number) IS
2099 SELECT WJDI.parent_header_id, WJDI.operation_seq_num, WJDI.standard_operation_id,
2100 WJDI.department_id, WJDI.description , WJDI.first_unit_start_date,
2101 WJDI.first_unit_completion_date, WJDI.last_unit_start_date,
2102 WJDI.last_unit_completion_date, WJDI.minimum_transfer_quantity,
2103 WJDI.count_point_type, WJDI.backflush_flag,WJDI.last_update_date,
2104 WJDI.last_updated_by, WJDI.creation_date,WJDI.created_by, WJDI.last_update_login,
2105 WJDI.request_id, WJDI.program_application_id, WJDI.program_id, WJDI.program_update_date,
2106 WJDI.long_description,
2107 WJDI.attribute_category, WJDI.attribute1, WJDI.attribute2, WJDI.attribute3,
2108 WJDI.attribute4, WJDI.attribute5,
2109 WJDI.attribute6, WJDI.attribute7, WJDI.attribute8, WJDI.attribute9, WJDI.attribute10,
2110 WJDI.attribute11, WJDI.attribute12, WJDI.attribute13, WJDI.attribute14, WJDI.attribute15,
2111 WO.standard_operation_id curr_standard_operation_id
2112 FROM WIP_JOB_DTLS_INTERFACE WJDI, WIP_OPERATIONS WO
2113 WHERE group_id = p_group_id
2114 AND WJDI.process_phase = WIP_CONSTANTS.ML_VALIDATION
2115 AND WJDI.process_status IN (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2116 AND WJDI.wip_entity_id = p_wip_entity_id
2117 AND WJDI.organization_id = p_organization_id
2118 AND WJDI.load_type = WIP_JOB_DETAILS.WIP_OPERATION
2119 AND WJDI.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE
2120 AND WO.wip_entity_id = p_wip_entity_id
2121 AND WO.organization_id = p_organization_id
2122 AND WO.operation_seq_num = WJDI.operation_seq_num;
2123
2124 l_scheduled_start_date date;/* Bug 3659006*/
2125 l_scheduled_completion_date date;/* Bug 3659006*/
2126 l_ret_exp_status boolean := true; /* Bug#4675116 */
2127
2128 BEGIN
2129 begin
2130
2131 IF p_group_id IS NULL OR p_organization_id IS NULL OR
2132 p_wip_entity_id IS NULL THEN
2133
2134 x_err_code := SQLCODE;
2135 x_err_msg := 'Error in wipjdstb.pls'|| SQLERRM;
2136 x_return_status := FND_API.G_RET_STS_ERROR;
2137 return;
2138 END IF;
2139
2140 FOR cur_oper IN oper_info(p_group_id,p_wip_entity_id,p_organization_id) LOOP
2141
2142 /* fix for bug#2653621, disable update of std_op_id and its department, same as form */
2143 if (cur_oper.curr_standard_operation_id is not null or cur_oper.standard_operation_id is not null ) then
2144 WIP_JSI_Utils.record_error_text
2145 ( 'Changing of Std Operation ID, and its associated Department ID in an operation is disallowed',
2146 true);
2147 cur_oper.standard_operation_id := null;
2148 cur_oper.department_id := null;
2149 end if;
2150
2151 --Bug#4675116
2152 L_RET_EXP_STATUS := WIP_WS_EXCEPTIONS.CLOSE_EXCEPTION_JOBOP
2153 (
2154 P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
2155 P_OPERATION_SEQ_NUM => cur_oper.operation_seq_num,
2156 P_DEPARTMENT_ID => cur_oper.department_id,
2157 P_ORGANIZATION_ID => P_ORGANIZATION_ID
2158 );
2159
2160 UPDATE WIP_OPERATIONS
2161 SET last_update_date = sysdate,/*BUG 6721823*/
2162 last_updated_by = NVL(cur_oper.last_updated_by,last_updated_by),
2163 creation_date = NVL(cur_oper.creation_date,creation_date),
2164 created_by = NVL(cur_oper.created_by, created_by),
2165 last_update_login = NVL(cur_oper.last_update_login,last_update_login),
2166 request_id = NVL(cur_oper.request_id ,request_id),
2167 program_application_id =NVL(cur_oper.program_application_id,
2168 program_application_id),
2169 program_id = NVL(cur_oper.program_id,program_id),
2170 program_update_date = NVL(cur_oper.program_update_date,
2171 program_update_date),
2172 standard_operation_id = NVL(cur_oper.standard_operation_id,
2173 standard_operation_id),
2174 department_id = NVL(cur_oper.department_id,department_id),
2175 description = NVL(cur_oper.description,description),
2176 first_unit_start_date = NVL(cur_oper.first_unit_start_date,
2177 first_unit_start_date),
2178 first_unit_completion_date =NVL(cur_oper.first_unit_completion_date,
2179 first_unit_completion_date),
2180 last_unit_start_date = NVL(cur_oper.last_unit_start_date,
2181 last_unit_start_date),
2182 last_unit_completion_date = NVL(cur_oper.last_unit_completion_date,
2183 last_unit_completion_date),
2184 count_point_type = NVL(cur_oper.count_point_type,count_point_type),
2185 backflush_flag = NVL(cur_oper.backflush_flag,backflush_flag),
2186 minimum_transfer_quantity = NVL(cur_oper.minimum_transfer_quantity,
2187 minimum_transfer_quantity),
2188 long_description = NVL(cur_oper.long_description,
2189 long_description),
2190 attribute_category = NVL(cur_oper.attribute_category,
2191 attribute_category),
2192 attribute1 = NVL(cur_oper.attribute1,attribute1),
2193 attribute2 = NVL(cur_oper.attribute2,attribute2),
2194 attribute3 = NVL(cur_oper.attribute3,attribute3),
2195 attribute4 = NVL(cur_oper.attribute4,attribute4),
2196 attribute5 = NVL(cur_oper.attribute5,attribute5),
2197 attribute6 = NVL(cur_oper.attribute6,attribute6),
2198 attribute7 = NVL(cur_oper.attribute7,attribute7),
2199 attribute8 = NVL(cur_oper.attribute8,attribute8),
2200 attribute9 = NVL(cur_oper.attribute9,attribute9),
2201 attribute10 = NVL(cur_oper.attribute10,attribute10),
2202 attribute11 = NVL(cur_oper.attribute11,attribute11),
2203 attribute12 = NVL(cur_oper.attribute12,attribute12),
2204 attribute13 = NVL(cur_oper.attribute13,attribute13),
2205 attribute14 = NVL(cur_oper.attribute14,attribute14),
2206 attribute15 = NVL(cur_oper.attribute15,attribute15)
2207 where wip_entity_id = p_wip_entity_id
2208 and organization_id = p_organization_id
2209 and operation_seq_num = cur_oper.operation_seq_num;
2210
2211 /* Bug 5026773 - Update Material Rquirements to reflect the changed operation. */
2212 WIP_OPERATIONS_UTILITIES.Update_Reqs
2213 (p_wip_entity_id,
2214 p_organization_id,
2215 cur_oper.operation_seq_num,
2216 NULL, /* repetitive schedule id */
2217 cur_oper.department_id,
2218 cur_oper.first_unit_start_date);
2219
2220 /* Bug 3659006 -> modify the job start/end to allow operation FUSD/LUCD modification outsi
2221 de the start/end of job*/
2222 select scheduled_start_date,scheduled_completion_date
2223 into l_scheduled_start_date,l_scheduled_completion_date
2224 from wip_discrete_jobs
2225 where wip_entity_id = p_wip_entity_id
2226 AND organization_id = p_organization_id;
2227
2228 IF (cur_oper.first_unit_start_date is not null
2229 AND cur_oper.first_unit_start_date < l_scheduled_start_date)
2230 THEN
2231 UPDATE wip_discrete_jobs
2232 set scheduled_start_date = cur_oper.first_unit_start_date
2233 where wip_entity_id = p_wip_entity_id
2234 AND organization_id = p_organization_id;
2235 END IF;
2236 IF (cur_oper.last_unit_completion_date is not null
2237 AND cur_oper.last_unit_completion_date > l_scheduled_completion_date)
2238 THEN
2239 UPDATE wip_discrete_jobs
2240 set scheduled_completion_date = cur_oper.last_unit_completion_date
2241 where wip_entity_id = p_wip_entity_id
2242 AND organization_id = p_organization_id;
2243 END IF;
2244 /*END bug 3659006 */
2245
2246 -- Start : Fix Bug#5116297/5129311
2247 -- Update date_required for Components on this Operation
2248 UPDATE WIP_REQUIREMENT_OPERATIONS WRO
2249 SET WRO.DATE_REQUIRED =
2250 (SELECT FIRST_UNIT_START_DATE
2251 FROM WIP_OPERATIONS
2252 WHERE WIP_ENTITY_ID = p_wip_entity_id
2253 AND OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM)
2254 AND ORGANIZATION_ID = p_organization_id
2255 ),
2256 LAST_UPDATED_BY = nvl(cur_oper.last_updated_by, last_updated_by),
2257 LAST_UPDATE_DATE = SYSDATE,
2258 LAST_UPDATE_LOGIN = nvl(cur_oper.last_update_login, last_update_login),
2259 REQUEST_ID = nvl(cur_oper.request_id, request_id),
2260 PROGRAM_UPDATE_DATE = SYSDATE,
2261 PROGRAM_ID = nvl(cur_oper.program_id, program_id),
2262 PROGRAM_APPLICATION_ID = nvl(cur_oper.program_application_id, program_application_id)
2263 WHERE WIP_ENTITY_ID = p_wip_entity_id
2264 AND OPERATION_SEQ_NUM = cur_oper.operation_seq_num
2265 AND ORGANIZATION_ID = p_organization_id ;
2266
2267 -- End : Fix Bug#5116297/5129311
2268
2269
2270 END LOOP;
2271
2272 exception
2273 when others then
2274 x_return_status := FND_API.G_RET_STS_ERROR;
2275 x_err_code := SQLCODE;
2276 x_err_msg := 'ERROR IN WIPJDSTB : '||SQLERRM;
2277 return;
2278 end;
2279
2280 END CHANGE_OPERATION;
2281
2282 Procedure Verify_Operation (p_group_id in number,
2283 p_wip_entity_id in number,
2284 p_organization_id in number,
2285 x_err_code out NOCOPY varchar2,
2286 x_err_msg out NOCOPY varchar2,
2287 x_return_status out NOCOPY varchar2) IS
2288
2289 /* Fix for Bug#4398726. Added WIP_JOB_SCHEDULE_INTERFACE in following sql
2290 * statement to bypass validation for records populated by planning
2291 * This is done as per Planning request
2292 */
2293 cursor c_invalid_rows is
2294 select wjdi.interface_id
2295 from wip_job_dtls_interface wjdi
2296 where wjdi.group_id = p_group_id
2297 and wjdi.process_phase = wip_constants.ml_validation
2298 and wjdi.process_status in (wip_constants.running,
2299 wip_constants.warning)
2300 and wjdi.load_type = wip_job_details.wip_operation
2301 and wjdi.substitution_type in (wip_job_details.wip_add, wip_job_details.wip_change)
2302 and wjdi.wip_entity_id = p_wip_entity_id
2303 and wjdi.organization_id = p_organization_id
2304 and (wjdi.first_unit_start_date is not null or
2305 wjdi.last_unit_completion_date is not null)
2306 /* Fix for Bug#6394857(FP of 6370245).
2307 and wjdi.group_id not in (select wjsi.group_id
2308 from wip_job_schedule_interface wjsi
2309 where wjsi.group_id = p_group_id
2310 and wjsi.source_code = 'MSC'
2311 )
2312 */
2313 and wjdi.operation_seq_num =
2314 (select wo.operation_seq_num
2315 from wip_operations wo
2316 where wo.wip_entity_id = p_wip_entity_id
2317 and wo.organization_id = p_organization_id
2318 and wo.operation_seq_num = wjdi.operation_seq_num
2319 and (wo.first_unit_start_date >
2320 (select min(start_date)
2321 from wip_operation_resources wor
2322 where wor.wip_entity_id = p_wip_entity_id
2323 and wor.organization_id = p_organization_id
2324 and wor.operation_seq_num = wo.operation_seq_num)
2325 or
2326 wo.last_unit_completion_date <
2327 (select max(completion_date)
2328 from wip_operation_resources wor
2329 where wor.wip_entity_id = p_wip_entity_id
2330 and wor.organization_id = p_organization_id
2331 and wor.operation_seq_num = wo.operation_seq_num)));
2332
2333 l_error_exists boolean := false;
2334
2335 begin
2336 for l_inv_row in c_invalid_rows loop
2337 l_error_exists := true;
2338 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULE_DATE');
2339 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
2340 if(wip_job_details.std_alone = 1) then
2341 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
2342 p_text => substr(fnd_message.get,1,500),
2343 p_error_type => wip_jdi_utils.msg_error);
2344 else
2345 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2346 p_text => substr(fnd_message.get,1,500),
2347 p_error_type => wip_jdi_utils.msg_error);
2348 end if;
2349 end loop;
2350
2351 if(l_error_exists) then
2352 update wip_job_dtls_interface wjdi
2353 set process_status = wip_constants.error
2354 where wjdi.group_id = p_group_id
2355 and wjdi.process_phase = wip_constants.ml_validation
2356 and wjdi.process_status in (wip_constants.running,
2357 wip_constants.warning)
2358 and wjdi.load_type = wip_job_details.wip_operation
2359 and wjdi.substitution_type in (wip_job_details.wip_add, wip_job_details.wip_change)
2360 and wjdi.wip_entity_id = p_wip_entity_id
2361 and wjdi.organization_id = p_organization_id
2362 and (wjdi.first_unit_start_date is not null or
2363 wjdi.last_unit_completion_date is not null)
2364 and wjdi.operation_seq_num =
2365 (select wo.operation_seq_num
2366 from wip_operations wo
2367 where wo.wip_entity_id = p_wip_entity_id
2368 and wo.organization_id = p_organization_id
2369 and wo.operation_seq_num = wjdi.operation_seq_num
2370 and (wo.first_unit_start_date >
2371 (select min(start_date)
2372 from wip_operation_resources wor
2373 where wor.wip_entity_id = p_wip_entity_id
2374 and wor.organization_id = p_organization_id
2375 and wor.operation_seq_num = wo.operation_seq_num)
2376 or
2377 wo.last_unit_completion_date <
2378 (select max(completion_date)
2379 from wip_operation_resources wor
2380 where wor.wip_entity_id = p_wip_entity_id
2381 and wor.organization_id = p_organization_id
2382 and wor.operation_seq_num = wo.operation_seq_num)));
2383
2384 end if;
2385
2386 Exception
2387 when others then
2388 x_return_status := FND_API.G_RET_STS_ERROR;
2389 x_err_msg := 'ERROR IN WIPJDSTB.VERIFY_OPERATION: ' || SQLERRM;
2390 x_err_code := to_char(SQLCODE);
2391 return;
2392
2393 end;
2394
2395
2396 Procedure Delete_Resource_Usage(p_wip_entity_id number,
2397 p_organization_id number,
2398 p_operation_seq_num number,
2399 p_resource_seq_num number,
2400 x_err_code out NOCOPY varchar2,
2401 x_err_msg out NOCOPY varchar2) IS
2402
2403 BEGIN
2404
2405 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
2406 WHERE wip_entity_id = p_wip_entity_id
2407 AND organization_id = p_organization_id
2408 AND operation_seq_num = p_operation_seq_num
2409 AND resource_seq_num = p_resource_seq_num;
2410
2411 exception
2412 When others then
2413 x_err_code := SQLCODE;
2414 x_err_msg := 'Error in wipjdstb: '||SQLERRM;
2415 return;
2416
2417 END DELETE_RESOURCE_USAGE;
2418
2419 Procedure Substitution_Res_Usages( p_group_id in number,
2420 p_wip_entity_id in number,
2421 p_organization_id in number,
2422 x_err_code out NOCOPY varchar2,
2423 x_err_msg out NOCOPY varchar2,
2424 x_return_status out NOCOPY varchar2) IS
2425
2426 Cursor Usage_info (p_group_id number,
2427 p_wip_entity_id number,
2428 p_organization_id number) IS
2429 SELECT operation_seq_num, resource_seq_num
2430 FROM WIP_JOB_DTLS_INTERFACE
2431 WHERE group_id = p_group_id
2432 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2433 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2434 AND wip_entity_id = p_wip_entity_id
2435 AND organization_id = p_organization_id
2436 AND load_type in (WIP_JOB_DETAILS.WIP_RES_USAGE,
2437 WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
2438 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
2439
2440 Begin
2441
2442 FOR cur_row in Usage_info(p_group_id, p_wip_entity_id,p_organization_id)Loop
2443
2444 Sub_Usage(p_group_id, p_wip_entity_id, p_organization_id,
2445 cur_row.operation_seq_num, cur_row.resource_seq_num,
2446 x_err_code, x_err_msg, x_return_status);
2447
2448 END LOOP;
2449
2450 END SUBSTITUTION_RES_USAGES;
2451
2452
2453 Procedure Sub_Usage (p_group_id in number,
2454 p_wip_entity_id in number,
2455 p_organization_id in number,
2456 p_operation_seq_num in number,
2457 p_resource_seq_num in number,
2458 x_err_code out NOCOPY varchar2,
2459 x_err_msg out NOCOPY varchar2,
2460 x_return_status out NOCOPY varchar2) IS
2461
2462 Cursor Usage_Update (p_group_id number, p_wip_entity_id number,
2463 p_organization_id number, p_operation_seq_num number,
2464 p_resource_seq_num number) IS
2465 SELECT distinct wip_entity_id , organization_id, operation_seq_num,
2466 resource_seq_num, resource_instance_id, start_date,
2467 completion_date, assigned_units, resource_serial_number,
2468 last_update_date, last_updated_by, creation_date, created_by,
2469 last_update_login, program_application_id, request_id, program_id,
2470 program_update_date, substitution_type
2471 FROM WIP_JOB_DTLS_INTERFACE
2472 WHERE group_id = p_group_id
2473 AND wip_entity_id = p_wip_entity_id
2474 AND organization_id = p_organization_id
2475 AND operation_seq_num = p_operation_seq_num
2476 AND resource_seq_num = p_resource_seq_num
2477 AND load_type in (WIP_JOB_DETAILS.WIP_RES_USAGE,
2478 WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
2479 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING);
2480
2481 x_statement varchar2(2000);
2482 l_resource_id number;
2483
2484 BEGIN
2485
2486 x_statement := NULL;
2487
2488 begin
2489
2490 IF p_group_id IS NULL OR p_organization_id IS NULL OR p_wip_entity_id IS NULL
2491 OR p_operation_seq_num IS NULL OR p_resource_seq_num IS NULL THEN
2492 x_err_code := SQLCODE;
2493 x_err_msg := 'Error in wiprustb.pls: Primary key cannot be null!';
2494 x_return_status := FND_API.G_RET_STS_ERROR;
2495 return;
2496 END IF;
2497
2498 /************************************************************************
2499 CHECK THAT IF GROUP_ID, ORGANIZATION_ID, WIP_ENTITY_ID,
2500 OPERATION_SEQ_NUM AND RESOURCE_SEQ_NUM IS NULL
2501 **********************************************************************/
2502
2503 /* delete all existing resource and resource instance usages.*/
2504 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
2505 WHERE wip_entity_id = p_wip_entity_id
2506 AND organization_id = p_organization_id
2507 AND operation_seq_num = p_operation_seq_num
2508 AND resource_seq_num = p_resource_seq_num;
2509
2510 /**********************DELETE ALL EXISTING RECORDS BEFORE ADD *************/
2511
2512 FOR cur_update IN Usage_Update(p_group_id , p_wip_entity_id,
2513 p_organization_id, p_operation_seq_num,
2514 p_resource_seq_num) LOOP
2515
2516 IF cur_update.substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
2517
2518 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
2519 ( WIP_ENTITY_ID ,
2520 ORGANIZATION_ID,
2521 OPERATION_SEQ_NUM,
2522 RESOURCE_SEQ_NUM,
2523 INSTANCE_ID,
2524 START_DATE ,
2525 COMPLETION_DATE,
2526 ASSIGNED_UNITS ,
2527 LAST_UPDATE_DATE,
2528 LAST_UPDATED_BY,
2529 CREATION_DATE,
2530 CREATED_BY,
2531 LAST_UPDATE_LOGIN,
2532 REQUEST_ID,
2533 PROGRAM_APPLICATION_ID,
2534 PROGRAM_ID,
2535 PROGRAM_UPDATE_DATE,
2536 SERIAL_NUMBER)
2537 VALUES
2538 ( cur_update.wip_entity_id,
2539 cur_update.organization_id,
2540 cur_update.operation_seq_num,
2541 cur_update.resource_seq_num,
2542 cur_update.resource_instance_id,
2543 cur_update.start_date,
2544 cur_update.completion_date,
2545 cur_update.assigned_units,
2546 sysdate,/*BUG 6721823*/
2547 cur_update.last_updated_by,
2548 cur_update.creation_date,
2549 cur_update.created_by,
2550 cur_update.last_update_login,
2551 cur_update.request_id,
2552 cur_update.program_application_id,
2553 cur_update.program_id,
2554 cur_update.program_update_date,
2555 cur_update.resource_serial_number);
2556
2557 END IF;
2558
2559 END LOOP;
2560
2561 Update_cumulative_time(p_wip_entity_id,
2562 p_operation_seq_num,
2563 p_resource_seq_num);
2564
2565 exception
2566 When others then
2567 x_err_code := SQLCODE;
2568 x_err_msg := 'Error in wipjdstb: '|| SQLERRM;
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 return;
2571 end;
2572
2573 END Sub_Usage;
2574
2575 -- Used to check whether there're some usage record for a resource.
2576
2577 Function Num_Of_Usage(p_group_id number, /* Fix for bug#3636378 */
2578 p_wip_entity_id number,
2579 p_organization_id number,
2580 p_operation_seq_num number,
2581 p_resource_seq_num number) return number IS
2582
2583 x_count number := 0;
2584
2585 BEGIN
2586
2587 select count(*) into x_count
2588 from wip_job_dtls_interface
2589 where group_id = p_group_id /* Fix for bug#3636378 */
2590 and wip_entity_id = p_wip_entity_id
2591 and organization_id = p_organization_id
2592 and operation_seq_num = p_operation_seq_num
2593 and resource_seq_num = p_resource_seq_num
2594 and load_type = WIP_JOB_DETAILS.WIP_RES_USAGE;
2595
2596 return x_count;
2597
2598
2599 END Num_Of_Usage;
2600
2601 -- We will insert a default usage record if
2602 -- 1. the program is stand alone
2603 -- 2. the scheduling method for this job is manully.
2604
2605 Procedure Add_Default_Usage(p_wip_entity_id number,
2606 p_organization_id number,
2607 p_operation_seq_num number,
2608 p_resource_seq_num number) IS
2609
2610 BEGIN
2611
2612 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
2613 (WIP_ENTITY_ID,
2614 OPERATION_SEQ_NUM,
2615 RESOURCE_SEQ_NUM,
2616 ORGANIZATION_ID,
2617 LAST_UPDATE_DATE,
2618 LAST_UPDATED_BY,
2619 CREATION_DATE,
2620 CREATED_BY,
2621 LAST_UPDATE_LOGIN,
2622 REQUEST_ID,
2623 PROGRAM_APPLICATION_ID,
2624 PROGRAM_ID,
2625 PROGRAM_UPDATE_DATE,
2626 START_DATE,
2627 COMPLETION_DATE,
2628 ASSIGNED_UNITS)
2629 SELECT
2630 WIP_ENTITY_ID,
2631 OPERATION_SEQ_NUM,
2632 RESOURCE_SEQ_NUM,
2633 ORGANIZATION_ID,
2634 LAST_UPDATE_DATE,
2635 LAST_UPDATED_BY,
2636 CREATION_DATE,
2637 CREATED_BY,
2638 LAST_UPDATE_LOGIN,
2639 REQUEST_ID,
2640 PROGRAM_APPLICATION_ID,
2641 PROGRAM_ID,
2642 PROGRAM_UPDATE_DATE,
2643 START_DATE,
2644 COMPLETION_DATE,
2645 ASSIGNED_UNITS
2646 FROM WIP_OPERATION_RESOURCES
2647 WHERE wip_entity_id = p_wip_entity_id
2648 AND organization_id = p_organization_id
2649 AND operation_seq_num = p_operation_seq_num
2650 AND resource_seq_num = p_resource_seq_num;
2651
2652 /*Bug 5727185/5576967: Update cumulative processing time for WORU record*/
2653 Update_cumulative_time(p_wip_entity_id,
2654 p_operation_seq_num,
2655 p_resource_seq_num);
2656
2657 END Add_Default_Usage;
2658
2659 Procedure Add_Op_Link (p_group_id number,
2660 p_wip_entity_id number,
2661 p_organization_id number,
2662 p_err_code out NOCOPY varchar2,
2663 p_err_msg out NOCOPY varchar2) IS
2664
2665
2666 CURSOR op_link_info (p_group_id number,
2667 p_wip_entity_id number,
2668 p_organization_id number) IS
2669 SELECT distinct operation_seq_num, next_network_op_seq_num,
2670 last_update_date, last_updated_by, creation_date, created_by,
2671 last_update_login,
2672 attribute_category, attribute1,
2673 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2674 attribute8,attribute9,attribute10,attribute11,attribute12,
2675 attribute13,attribute14,attribute15,schedule_seq_num,
2676 substitute_group_num,replacement_group_num,batch_id
2677 FROM WIP_JOB_DTLS_INTERFACE
2678 WHERE group_id = p_group_id
2679 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2680 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2681 AND wip_entity_id = p_wip_entity_id
2682 AND organization_id = p_organization_id
2683 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
2684 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
2685
2686 l_scheduling_method number;
2687
2688 BEGIN
2689
2690 begin
2691 FOR cur_row IN op_link_info(p_group_id,
2692 p_wip_entity_id,
2693 p_organization_id) LOOP
2694
2695 /* insert into table */
2696 INSERT INTO WIP_OPERATION_NETWORKS(
2697 prior_operation,
2698 next_operation,
2699 wip_entity_id,
2700 organization_id,
2701 last_update_date,
2702 last_updated_by,
2703 creation_date,
2704 created_by,
2705 last_update_login,
2706 attribute_category,
2707 attribute1,
2708 attribute2,
2709 attribute3,
2710 attribute4,
2711 attribute5,
2712 attribute6,
2713 attribute7,
2714 attribute8,
2715 attribute9,
2716 attribute10,
2717 attribute11,
2718 attribute12,
2719 attribute13,
2720 attribute14,
2721 attribute15)
2722 VALUES (
2723 cur_row.operation_seq_num,
2724 cur_row.next_network_op_seq_num,
2725 p_wip_entity_id,
2726 p_organization_id,
2727 sysdate,/*BUG 6721823*/
2728 cur_row.last_updated_by,
2729 cur_row.creation_date,
2730 cur_row.created_by,
2731 cur_row.last_update_login,
2732 cur_row.attribute_category,
2733 cur_row.attribute1,
2734 cur_row.attribute2,
2735 cur_row.attribute3,
2736 cur_row.attribute4,
2737 cur_row.attribute5,
2738 cur_row.attribute6,
2739 cur_row.attribute7,
2740 cur_row.attribute8,
2741 cur_row.attribute9,
2742 cur_row.attribute10,
2743 cur_row.attribute11,
2744 cur_row.attribute12,
2745 cur_row.attribute13,
2746 cur_row.attribute14,
2747 cur_row.attribute15);
2748
2749 END LOOP;
2750
2751 exception
2752 when others then
2753 p_err_msg := 'WIPJDSTB, Add_Op_Link: ' || SQLERRM;
2754 p_err_code := SQLCODE;
2755 end;
2756
2757 END Add_Op_Link;
2758
2759 Procedure Delete_Op_Link (p_group_id number,
2760 p_wip_entity_id number,
2761 p_organization_id number,
2762 p_err_code out NOCOPY varchar2,
2763 p_err_msg out NOCOPY varchar2) IS
2764
2765 CURSOR op_link_info (p_group_id number,
2766 p_wip_entity_id number,
2767 p_organization_id number) IS
2768 SELECT distinct operation_seq_num, next_network_op_seq_num,
2769 last_update_date, last_updated_by, creation_date, created_by,
2770 last_update_login,
2771 attribute_category, attribute1,
2772 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2773 attribute8,attribute9,attribute10,attribute11,attribute12,
2774 attribute13,attribute14,attribute15,schedule_seq_num,
2775 substitute_group_num,replacement_group_num,batch_id
2776 FROM WIP_JOB_DTLS_INTERFACE
2777 WHERE group_id = p_group_id
2778 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2779 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2780 AND wip_entity_id = p_wip_entity_id
2781 AND organization_id = p_organization_id
2782 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
2783 AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
2784
2785 BEGIN
2786
2787 begin
2788 FOR cur_row IN op_link_info(p_group_id,
2789 p_wip_entity_id,
2790 p_organization_id) LOOP
2791
2792 DELETE FROM WIP_OPERATION_NETWORKS
2793 WHERE wip_entity_id = p_wip_entity_id
2794 AND organization_id = p_organization_id
2795 AND prior_operation = cur_row.operation_seq_num
2796 AND next_operation = cur_row.next_network_op_seq_num;
2797
2798 END LOOP;
2799 exception
2800 when others then
2801 p_err_msg := 'WIPJDSTB, Delete_Op_Link: ' || SQLERRM;
2802 p_err_code := SQLCODE;
2803 end;
2804
2805 END Delete_Op_Link;
2806
2807
2808 Procedure Add_Serial_Association(p_group_id in number,
2809 p_wip_entity_id in number,
2810 p_organization_id in number,
2811 x_err_code out NOCOPY varchar2,
2812 x_err_msg out NOCOPY varchar2,
2813 x_return_status out NOCOPY varchar2) IS
2814
2815 CURSOR ser_info ( p_group_id number,
2816 p_wip_entity_id number,
2817 p_organization_id number) IS
2818 SELECT wjdi.serial_number_new serial_number,
2819 nvl(we.primary_item_id, wjsi.primary_item_id) primary_item_id,
2820 nvl(we.organization_id, wjsi.organization_id) organization_id
2821 FROM wip_job_dtls_interface wjdi,
2822 wip_job_schedule_interface wjsi,
2823 wip_entities we
2824 WHERE wjdi.group_id = p_group_id
2825 AND wjsi.group_id = p_group_id
2826 AND wjdi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2827 AND wjdi.process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2828 AND wjdi.wip_entity_id = p_wip_entity_id
2829 AND wjdi.organization_id = p_organization_id
2830 AND wjdi.load_type = WIP_JOB_DETAILS.WIP_SERIAL
2831 AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_ADD
2832 AND wjdi.parent_header_id = wjsi.header_id
2833 AND wjsi.wip_entity_id = we.wip_entity_id (+);
2834
2835 begin
2836 savepoint wipjdstb10;
2837 IF p_group_id IS NULL OR p_organization_id IS NULL OR
2838 p_wip_entity_id IS NULL THEN
2839
2840 x_err_code := SQLCODE;
2841 x_err_msg := 'Error in wipjdstb.pls'|| SQLERRM;
2842 x_return_status := FND_API.G_RET_STS_ERROR;
2843 return;
2844 END IF;
2845
2846 for l_serRec in ser_info(p_group_id, p_wip_entity_id, p_organization_id) loop
2847 wip_utilities.update_serial(p_serial_number => l_serRec.serial_number,
2848 p_organization_id => l_serRec.organization_id,
2849 p_inventory_item_id => l_serRec.primary_item_id,
2850 p_wip_entity_id => p_wip_entity_id,
2851 p_operation_seq_num => null,
2852 p_intraoperation_step_type => null,
2853 x_return_status => x_return_status);
2854 if(x_return_status <> fnd_api.g_ret_sts_success) then
2855 rollback to wipjdstb10;
2856 wip_utilities.get_message_stack(p_msg => x_err_msg);
2857 exit;
2858 end if;
2859 end loop;
2860 exception
2861 when others then
2862 x_return_status := FND_API.G_RET_STS_ERROR;
2863 x_err_code := SQLCODE;
2864 x_err_msg := 'ERROR IN WIPJDSTB : '||SQLERRM;
2865
2866 END Add_Serial_Association;
2867
2868 Procedure Change_Serial_Association (p_group_id in number,
2869 p_wip_entity_id in number,
2870 p_organization_id in number,
2871 x_err_code out NOCOPY varchar2,
2872 x_err_msg out NOCOPY varchar2,
2873 x_return_status out NOCOPY varchar2) IS
2874
2875 CURSOR ser_info ( p_group_id number,
2876 p_wip_entity_id number,
2877 p_organization_id number) IS
2878 SELECT wjdi.serial_number_new,
2879 wjdi.serial_number_old,
2880 nvl(we.primary_item_id, wjsi.primary_item_id) primary_item_id,
2881 nvl(we.organization_id, wjsi.organization_id) organization_id
2882 FROM wip_job_dtls_interface wjdi,
2883 wip_job_schedule_interface wjsi,
2884 wip_entities we
2885 WHERE wjdi.group_id = p_group_id
2886 AND wjsi.group_id = p_group_id
2887 AND wjdi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2888 AND wjdi.process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2889 AND wjdi.wip_entity_id = p_wip_entity_id
2890 AND wjdi.organization_id = p_organization_id
2891 AND wjdi.load_type = WIP_JOB_DETAILS.WIP_SERIAL
2892 AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE
2893 and wjdi.parent_header_id = wjsi.header_id
2894 and wjsi.wip_entity_id = we.wip_entity_id (+);
2895
2896 BEGIN
2897 savepoint wipjdstb20;
2898 IF p_group_id IS NULL OR p_organization_id IS NULL OR
2899 p_wip_entity_id IS NULL THEN
2900
2901 x_err_code := SQLCODE;
2902 x_err_msg := 'Error in wipjdstb.pls'|| SQLERRM;
2903 x_return_status := FND_API.G_RET_STS_ERROR;
2904 return;
2905 END IF;
2906
2907 for l_serRec in ser_info(p_group_id, p_wip_entity_id, p_organization_id) loop
2908 wip_utilities.update_serial(p_serial_number => l_serRec.serial_number_new,
2909 p_organization_id => l_serRec.organization_id,
2910 p_inventory_item_id => l_serRec.primary_item_id,
2911 p_wip_entity_id => p_wip_entity_id,
2912 p_operation_seq_num => null,
2913 p_intraoperation_step_type => null,
2914 x_return_status => x_return_status);
2915 if(x_return_status <> fnd_api.g_ret_sts_success) then
2916 rollback to wipjdstb20;
2917 wip_utilities.get_message_stack(p_msg => x_err_msg);
2918 exit;
2919 end if;
2920
2921 wip_utilities.update_serial(p_serial_number => l_serRec.serial_number_old,
2922 p_organization_id => l_serRec.organization_id,
2923 p_inventory_item_id => l_serRec.primary_item_id,
2924 p_wip_entity_id => null,
2925 p_operation_seq_num => null,
2926 p_intraoperation_step_type => null,
2927 x_return_status => x_return_status);
2928 if(x_return_status <> fnd_api.g_ret_sts_success) then
2929 wip_utilities.get_message_stack(p_msg => x_err_msg);
2930 exit;
2931 end if;
2932
2933 end loop;
2934
2935 exception
2936 when others then
2937 x_return_status := FND_API.G_RET_STS_ERROR;
2938 x_err_code := SQLCODE;
2939 x_err_msg := 'ERROR IN WIPJDSTB : '||SQLERRM;
2940 END Change_Serial_Association;
2941
2942 Procedure Delete_Serial_Association (p_group_id in number,
2943 p_wip_entity_id in number,
2944 p_organization_id in number,
2945 x_err_code out NOCOPY varchar2,
2946 x_err_msg out NOCOPY varchar2,
2947 x_return_status out NOCOPY varchar2) IS
2948
2949 CURSOR ser_info ( p_group_id number,
2950 p_wip_entity_id number,
2951 p_organization_id number) IS
2952 SELECT wjdi.serial_number_old serial_number,
2953 nvl(we.primary_item_id, wjsi.primary_item_id) primary_item_id,
2954 nvl(we.organization_id, wjsi.organization_id) organization_id
2955 FROM wip_job_dtls_interface wjdi,
2956 wip_job_schedule_interface wjsi,
2957 wip_entities we
2958 WHERE wjdi.group_id = p_group_id
2959 AND wjsi.group_id = p_group_id
2960 AND wjdi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2961 AND wjdi.process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2962 AND wjdi.wip_entity_id = p_wip_entity_id
2963 AND wjdi.organization_id = p_organization_id
2964 AND wjdi.load_type = WIP_JOB_DETAILS.WIP_SERIAL
2965 AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_DELETE
2966 AND wjdi.parent_header_id = wjsi.header_id
2967 AND wjsi.wip_entity_id = we.wip_entity_id (+);
2968
2969 BEGIN
2970 savepoint wipjdstb30;
2971 IF p_group_id IS NULL OR p_organization_id IS NULL OR
2972 p_wip_entity_id IS NULL THEN
2973
2974 x_err_code := SQLCODE;
2975 x_err_msg := 'Error in wipjdstb.pls'|| SQLERRM;
2976 x_return_status := FND_API.G_RET_STS_ERROR;
2977 return;
2978 END IF;
2979
2980 for l_serRec in ser_info(p_group_id, p_wip_entity_id, p_organization_id) loop
2981 wip_utilities.update_serial(p_serial_number => l_serRec.serial_number,
2982 p_organization_id => l_serRec.organization_id,
2983 p_inventory_item_id => l_serRec.primary_item_id,
2984 p_wip_entity_id => null,
2985 p_operation_seq_num => null,
2986 p_intraoperation_step_type => null,
2987 x_return_status => x_return_status);
2988 if(x_return_status <> fnd_api.g_ret_sts_success) then
2989 rollback to wipjdstb30;
2990 wip_utilities.get_message_stack(p_msg => x_err_msg);
2991 exit;
2992 end if;
2993 end loop;
2994 exception
2995 when others then
2996 x_return_status := FND_API.G_RET_STS_ERROR;
2997 x_err_code := SQLCODE;
2998 x_err_msg := 'ERROR IN WIPJDSTB : '||SQLERRM;
2999 END Delete_Serial_Association;
3000
3001 Procedure Default_Serial_Associations(p_rowid in rowid,
3002 p_wip_entity_id in number,
3003 p_organization_id in number,
3004 x_err_msg out NOCOPY varchar2,
3005 x_return_status out NOCOPY varchar2) IS
3006 l_defaultSN NUMBER;
3007 l_rowCount NUMBER;
3008 l_jobQty NUMBER;
3009 l_primary_item_id NUMBER;
3010 l_start_quantity NUMBER;
3011 l_start_serial VARCHAR2(30);
3012 l_end_serial VARCHAR2(30);
3013 l_serialization_start_op NUMBER;
3014 l_load_type NUMBER;
3015
3016 begin
3017 x_return_status := fnd_api.g_ret_sts_success;
3018
3019 select default_wip_auto_assoc_sn
3020 into l_defaultSN
3021 from wip_parameters
3022 where organization_id = p_organization_id;
3023
3024 select load_type
3025 into l_load_type
3026 from wip_job_schedule_interface
3027 where rowid = p_rowid;
3028
3029 if(l_defaultSN = wip_constants.yes and
3030 l_load_type = wip_constants.create_job) then
3031 --get the number of serial numbers defaulted and the start quantity of the job
3032
3033 SELECT count(*)
3034 INTO l_rowCount
3035 FROM mtl_serial_numbers
3036 WHERE wip_entity_id = p_wip_entity_id;
3037
3038 SELECT start_quantity,
3039 primary_item_id,
3040 serialization_start_op
3041 INTO l_start_quantity,
3042 l_primary_item_id,
3043 l_serialization_start_op
3044 FROM wip_discrete_jobs
3045 WHERE wip_entity_id = p_wip_entity_id;
3046
3047 --if the user did not provide enough serial numbers
3048 if(l_serialization_start_op is not null and
3049 l_start_quantity > l_rowCount) then
3050 wip_utilities.generate_serials(p_item_id => l_primary_item_id,
3051 p_org_id => p_organization_id,
3052 p_qty => to_number(l_start_quantity - l_rowCount),
3053 p_wip_entity_id => p_wip_entity_id,
3054 p_revision => null,
3055 p_lot => null,
3056 x_start_serial => l_start_serial,
3057 x_end_serial => l_end_serial,
3058 x_return_status => x_return_status,
3059 x_err_msg => x_err_msg);
3060 end if;
3061 end if;
3062 end default_serial_associations;
3063
3064 Procedure Update_Cumulative_Time (
3065 p_wip_entity_id in number,
3066 p_operation_seq_num in number,
3067 p_resource_seq_num in number) IS
3068 cursor res_usage (p_wip_entity_id number,
3069 p_operation_seq_num number,
3070 p_resource_seq_num number) is
3071 select start_date,
3072 completion_date,
3073 cumulative_processing_time
3074 from wip_operation_resource_usage
3075 where wip_entity_id = p_wip_entity_id
3076 and operation_seq_num = p_operation_seq_num
3077 and resource_seq_num = p_resource_seq_num
3078 and instance_id is null
3079 order by start_date
3080 for update;
3081
3082 current_cpt NUMBER := 0;
3083 begin
3084 for cur_row in res_usage(p_wip_entity_id, p_operation_seq_num, p_resource_seq_num) loop
3085 current_cpt := current_cpt + wip_datetimes.datetime_diff_to_mins(cur_row.completion_date, cur_row.start_date);
3086 update wip_operation_resource_usage
3087 set cumulative_processing_time = current_cpt
3088 where current of res_usage;
3089 end loop;
3090 end update_cumulative_time;
3091
3092 END WIP_JOB_DTLS_SUBSTITUTIONS;