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