[Home] [Help]
PACKAGE BODY: APPS.WIP_JOB_DETAILS
Source
1 Package Body WIP_JOB_DETAILS as
2 /* $Header: wipjdldb.pls 120.2 2005/12/16 13:39:31 yulin noship $ */
3
4
5 Procedure Load_All_Details( p_group_id in number,
6 p_parent_header_id in number,
7 p_std_alone in integer,
8 x_err_code out nocopy varchar2,
9 x_err_msg out nocopy varchar2,
10 x_return_status out nocopy varchar2 ) IS
11
12 Cursor Job_Cur IS
13 select distinct wip_entity_id,
14 organization_id
15 from wip_job_dtls_interface
16 where group_id = p_group_id
17 and parent_header_id = p_parent_header_id
18 and process_phase = WIP_CONSTANTS.ML_VALIDATION
19 and process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING) ;
20
21 Cursor wdj_cur IS
22 select distinct wip_entity_id,
23 organization_id
24 from wip_job_dtls_interface
25 where group_id = p_group_id
26 and process_phase = WIP_CONSTANTS.ML_VALIDATION
27 and process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING) ;
28
29 Cursor Oper_Cur (p_wip_entity_id number, p_organization_id number) IS
30 select distinct wip_entity_id,
31 organization_id,
32 load_type, substitution_type
33 from wip_job_dtls_interface
34 where group_id = p_group_id
35 and ((p_std_alone = 0
36 and parent_header_id = p_parent_header_id)
37 OR p_std_alone = 1)
38 and wip_entity_id = p_wip_entity_id
39 and organization_id = p_organization_id
40 and load_type = WIP_JOB_DETAILS.WIP_OPERATION
41 and process_phase = WIP_CONSTANTS.ML_VALIDATION
42 and process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING) ;
43
44 Cursor Res_Cur (p_wip_entity_id number, p_organization_id number) IS
45 select distinct wip_entity_id,
46 organization_id,
47 load_type, substitution_type
48 from wip_job_dtls_interface
49 where group_id = p_group_id
50 and ((p_std_alone = 0
51 and parent_header_id = p_parent_header_id)
52 OR p_std_alone = 1)
53 and wip_entity_id = p_wip_entity_id
54 and organization_id = p_organization_id
55 and load_type = WIP_JOB_DETAILS.WIP_RESOURCE
56 and process_phase = WIP_CONSTANTS.ML_VALIDATION
57 and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
58
59 Cursor ResInst_Cur (p_wip_entity_id number, p_organization_id number) IS
60 select distinct wip_entity_id,
61 organization_id,
62 load_type, substitution_type
63 from wip_job_dtls_interface
64 where group_id = p_group_id
65 and ((p_std_alone = 0
66 and parent_header_id = p_parent_header_id)
67 OR p_std_alone = 1)
68 and wip_entity_id = p_wip_entity_id
69 and organization_id = p_organization_id
70 and load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
71 and process_phase = WIP_CONSTANTS.ML_VALIDATION
72 and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
73
74 Cursor Req_Cur (p_wip_entity_id number, p_organization_id number) IS
75 select distinct wip_entity_id,
76 organization_id,
77 load_type, substitution_type
78 from wip_job_dtls_interface
79 where group_id = p_group_id
80 and ((p_std_alone = 0
81 and parent_header_id = p_parent_header_id)
82 OR p_std_alone = 1 )
83 and wip_entity_id = p_wip_entity_id
84 and organization_id = p_organization_id
85 and load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
86 and process_phase = WIP_CONSTANTS.ML_VALIDATION
87 and process_status IN (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING);
88
89 Cursor Res_Usage_Cur (p_wip_entity_id number, p_organization_id number) IS
90 select distinct wip_entity_id,
91 organization_id,
92 load_type, substitution_type
93 from wip_job_dtls_interface
94 where group_id = p_group_id
95 and ((p_std_alone = 0
96 and parent_header_id = p_parent_header_id)
97 OR p_std_alone = 1)
98 and wip_entity_id = p_wip_entity_id
99 and organization_id = p_organization_id
100 and load_type in (WIP_JOB_DETAILS.WIP_RES_USAGE,
101 WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
102 and process_phase = WIP_CONSTANTS.ML_VALIDATION
103 and process_status IN (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING);
104
105 Cursor SubRes_Cur (p_wip_entity_id number, p_organization_id number) IS
106 select distinct wip_entity_id,
107 organization_id,
108 load_type, substitution_type
109 from wip_job_dtls_interface
110 where group_id = p_group_id
111 and ((p_std_alone = 0
112 and parent_header_id = p_parent_header_id)
113 OR p_std_alone = 1)
114 and wip_entity_id = p_wip_entity_id
115 and organization_id = p_organization_id
116 and load_type = WIP_JOB_DETAILS.WIP_SUB_RES
117 and process_phase = WIP_CONSTANTS.ML_VALIDATION
118 and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
119
120 Cursor Op_Link_Cur (p_wip_entity_id number, p_organization_id number) IS
121 select distinct wip_entity_id,
122 organization_id,
123 load_type, substitution_type
124 from wip_job_dtls_interface
125 where group_id = p_group_id
126 and ((p_std_alone = 0
127 and parent_header_id = p_parent_header_id)
128 OR p_std_alone = 1)
129 and wip_entity_id = p_wip_entity_id
130 and organization_id = p_organization_id
131 and load_type = WIP_JOB_DETAILS.WIP_OP_LINK
132 and process_phase = WIP_CONSTANTS.ML_VALIDATION
133 and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
134
135 Cursor Serials_Cur (p_wip_entity_id number, p_organization_id number) IS
136 select distinct wip_entity_id,
137 organization_id,
138 load_type, substitution_type
139 from wip_job_dtls_interface
140 where group_id = p_group_id
141 and ((p_std_alone = 0
142 and parent_header_id = p_parent_header_id)
143 OR p_std_alone = 1)
144 and wip_entity_id = p_wip_entity_id
145 and organization_id = p_organization_id
146 and load_type = WIP_JOB_DETAILS.WIP_SERIAL
147 and process_phase = WIP_CONSTANTS.ML_VALIDATION
148 and process_status IN (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
149
150 x_count number;
151
152 cur_job job_cur%ROWTYPE ;
153
154 l_dummy2 VARCHAR2(1);
155 l_logLevel number;
156
157 BEGIN
158
159 begin
160 x_err_code := NULL;
161 x_err_msg := NULL;
162 x_count := 0;
163 std_alone := p_std_alone;
164 l_logLevel := fnd_log.g_current_runtime_level;
165
166 /** p_group_id can not be null **/
167 IF p_group_id IS NULL THEN
168 x_err_msg := 'ERROR: You have to specify a group_id to load job details.';
169 x_err_code := -999;
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 return;
172 END IF;
173
174 IF p_std_alone = 0 AND p_parent_header_id IS NULL THEN
175 x_err_msg := 'ERROR: You have to give a parent header id to specify the job.';
176 x_err_code := -999;
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 return;
179 END IF;
180
181
182 /********************************************************************
183 ***** for ALL jobs in the given group that has PENDING status ******
184 ********************************************************************/
185 /* set process_status = RUNNING and generate new unique interface_id*/
186 WIP_JDI_Utils.begin_processing_request(p_group_id,
187 p_parent_header_id,
188 x_err_code,
189 x_err_msg,
190 x_return_status);
191
192 default_wip_entity_id(p_group_id,
193 p_parent_header_id,
194 x_err_code,
195 x_err_msg,
196 x_return_status);
197
198 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
199 return;
200
201 END IF;
202
203 /* Following if condition is added for Bug#3636378 */
204 IF p_std_alone = 1 then
205 open wdj_cur ;
206 ELSE
207 open job_cur ;
208 END IF ;
209
210 LOOP
211 /* Following if condition is added for Bug#3636378 */
212 IF p_std_alone = 1 then
213 fetch wdj_cur into cur_job ;
214 exit when wdj_cur%NOTFOUND ;
215
216 /* bug 4650624 */
217 wip_jsi_utils.current_interface_id := null;
218 ELSE
219 fetch job_cur into cur_job ;
220 exit when job_cur%NOTFOUND ;
221
222 /* bug 4650624 */
223 select interface_id
224 into wip_jsi_utils.current_interface_id
225 from wip_job_schedule_interface
226 where group_id = p_group_id
227 and header_id = p_parent_header_id;
228
229 END IF ;
230
231 /*** Validate general info. for this job ***/
232 WIP_JOB_DTLS_VALIDATIONS.Jobs(p_group_id,
233 p_parent_header_id);
234
235 WIP_JOB_DTLS_VALIDATIONS.Job_Status(p_group_id,
236 p_parent_header_id);
237
238 WIP_JOB_DTLS_VALIDATIONS.Is_Firm(p_group_id,
239 p_parent_header_id);
240
241 WIP_JOB_DTLS_VALIDATIONS.Load_Sub_Types (p_group_id,
242 p_parent_header_id,
243 cur_job.wip_entity_id,
244 cur_job.organization_id);
245
246 WIP_JOB_DTLS_VALIDATIONS.Last_Updated_By(P_Group_Id,
247 p_parent_header_id,
248 cur_job.wip_entity_id,
249 cur_job.organization_id);
250 WIP_JOB_DTLS_VALIDATIONS.Created_By(P_Group_Id,
251 p_parent_header_id,
252 cur_job.wip_entity_id,
253 cur_job.organization_id);
254
255 FOR l_cur IN OPER_CUR (cur_job.wip_entity_id,
256 cur_job.organization_id) LOOP
257
258 WIP_OPERATION_DEFAULT.Default_Operations
259 (p_group_id,
260 p_parent_header_id,
261 l_cur.wip_entity_id,
262 l_cur.organization_id,
263 l_cur.substitution_type ,
264 x_err_code ,
265 x_err_msg ,
266 x_return_status );
267
268 /* default operation records */
269
270 IF l_cur.substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
271
272 WIP_OPERATION_VALIDATE.Add_Operation(p_group_id,
273 p_parent_header_id,
274 l_cur.wip_entity_id,
275 l_cur.organization_id,
276 x_err_code, x_err_msg,
277 x_return_status);
278 /* validate operation records */
279
280 ELSIF l_cur.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
281
282 WIP_OPERATION_VALIDATE.Change_Operation(p_group_id,
283 p_parent_header_id,
284 l_cur.wip_entity_id,
285 l_cur.organization_id,
286 x_err_code, x_err_msg,
287 x_return_status);
288
289 END IF; /* end of operation validation */
290 END LOOP;
291
292 /**** Error out nocopy the whole job if any validations failed ****/
293 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
294 p_parent_header_id,
295 cur_job.wip_entity_id,
296 cur_job.organization_id);
297
298
299 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_OPERATION
300 (p_group_id,
301 cur_job.wip_entity_id,
302 cur_job.organization_id,
303 x_err_code ,
304 x_err_msg,
305 x_return_status);
306
307 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_OPERATION
308 (p_group_id,
309 cur_job.wip_entity_id,
310 cur_job.organization_id,
311 x_err_code ,
312 x_err_msg,
313 x_return_status);
314
315 /*************END PROCESSING OPERATIONS********************/
316
317 WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num ( p_group_id,
318 p_parent_header_id,
319 cur_job.wip_entity_id,
320 cur_job.organization_id);
321
322 FOR l_cur IN RES_CUR (cur_job.wip_entity_id,
323 cur_job.organization_id) LOOP
324
325 IF l_cur.substitution_type = WIP_DELETE THEN
326 WIP_RESOURCE_VALIDATIONS.Delete_Resource(
327 p_group_id,
328 l_cur.wip_entity_id,
329 l_cur.organization_id,
330 l_cur.substitution_type);
331
332 ELSIF l_cur.substitution_type = WIP_ADD THEN
333 WIP_RESOURCE_VALIDATIONS.Add_Resource(
334 p_group_id,
335 l_cur.wip_entity_id,
336 l_cur.organization_id,
337 l_cur.substitution_type);
338
339 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
340 WIP_RESOURCE_VALIDATIONS.Change_Resource(
341 p_group_id,
342 l_cur.wip_entity_id,
343 l_cur.organization_id,
344 l_cur.substitution_type);
345 END IF;
346 END LOOP;
347
348 /**** Error out nocopy the whole job if any validations failed ****/
349 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
350 p_parent_header_id,
351 cur_job.wip_entity_id,
352 cur_job.organization_id);
353
354
355 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE(
356 p_group_id,
357 cur_job.wip_entity_id,
358 cur_job.organization_id,
359 x_err_code,
360 x_err_msg);
361
362 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_RESOURCE(
363 p_group_id,
364 cur_job.wip_entity_id,
365 cur_job.organization_id,
366 x_err_code,
367 x_err_msg);
368 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_RESOURCE(
369 p_group_id,
370 cur_job.wip_entity_id,
371 cur_job.organization_id,
372 x_err_code,
373 x_err_msg);
374
375 FOR l_cur IN RESINST_CUR (cur_job.wip_entity_id,
376 cur_job.organization_id) LOOP
377
378 IF l_cur.substitution_type = WIP_DELETE THEN
379 WIP_RES_INST_VALIDATIONS.Delete_Resource_Instance(
380 p_group_id,
381 l_cur.wip_entity_id,
382 l_cur.organization_id,
383 l_cur.substitution_type,
384 x_err_code,
385 x_err_msg);
386 if (l_logLevel <= wip_constants.trace_logging) then
387 IF x_err_code IS NOT NULL THEN
388 wip_logger.log(x_err_code, l_dummy2);
389 wip_logger.log(x_err_msg, l_dummy2);
390 end if;
391 end if;
392
393 ELSIF l_cur.substitution_type = WIP_ADD THEN
394 WIP_RES_INST_VALIDATIONS.Add_Resource_Instance(
395 p_group_id,
396 l_cur.wip_entity_id,
397 l_cur.organization_id,
398 l_cur.substitution_type,
399 x_err_code,
400 x_err_msg);
401 if (l_logLevel <= wip_constants.trace_logging) then
402 IF x_err_code IS NOT NULL THEN
403 wip_logger.log(x_err_code, l_dummy2);
404 wip_logger.log(x_err_msg, l_dummy2);
405 end if;
406 end if;
407
408 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
409 WIP_RES_INST_VALIDATIONS.Change_Resource_Instance(
410 p_group_id,
411 l_cur.wip_entity_id,
412 l_cur.organization_id,
413 l_cur.substitution_type,
414 x_err_code,
415 x_err_msg);
416 if (l_logLevel <= wip_constants.trace_logging) then
417 IF x_err_code IS NOT NULL THEN
418 wip_logger.log(x_err_code, l_dummy2);
419 wip_logger.log(x_err_msg, l_dummy2);
420 end if;
421 end if;
422 END IF;
423 END LOOP;
424
425 /**** Error out nocopy the whole job if any validations failed ****/
426 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
427 p_parent_header_id,
428 cur_job.wip_entity_id,
429 cur_job.organization_id);
430
431
432 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE_INSTANCE(
433 p_group_id,
434 cur_job.wip_entity_id,
435 cur_job.organization_id,
436 WIP_JOB_DETAILS.WIP_DELETE,
437 x_err_code,
438 x_err_msg);
439
440 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_RESOURCE_INSTANCE(
441 p_group_id,
442 cur_job.wip_entity_id,
443 cur_job.organization_id,
444 x_err_code,
445 x_err_msg);
446 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_RESOURCE_INSTANCE(
447 p_group_id,
448 cur_job.wip_entity_id,
449 cur_job.organization_id,
450 x_err_code,
451 x_err_msg);
452
453 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
454 p_parent_header_id,
455 cur_job.wip_entity_id,
456 cur_job.organization_id);
457
458 /****** MATERAIL REQUIREMENTS processing ******************/
459 FOR l_cur IN REQ_CUR (cur_job.wip_entity_id,
460 cur_job.organization_id) LOOP
461
462 IF l_cur.substitution_type = WIP_DELETE THEN
463
464 WIP_REQUIREMENT_VALIDATIONS.Delete_Req(
465 p_group_id,
466 l_cur.wip_entity_id,
467 l_cur.organization_id,
468 l_cur.substitution_type);
469 ELSIF l_cur.substitution_type = WIP_ADD THEN
470
471 WIP_REQUIREMENT_VALIDATIONS.Add_Req(
472 p_group_id,
473 l_cur.wip_entity_id,
474 l_cur.organization_id,
475 l_cur.substitution_type);
476 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
477
478 WIP_REQUIREMENT_VALIDATIONS.Change_Req(
479 p_group_id,
480 l_cur.wip_entity_id,
481 l_cur.organization_id,
482 l_cur.substitution_type);
483
484 END IF; /* End requirement processing */
485 END LOOP;
486
487 /**** Error out nocopy the whole job if any validations failed ****/
488 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
489 p_parent_header_id,
490 cur_job.wip_entity_id,
491 cur_job.organization_id);
492
493
494 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_REQUIREMENT(
495 p_group_id,
496 cur_job.wip_entity_id,
497 cur_job.organization_id,
498 x_err_code,
499 x_err_msg);
500
501 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_REQUIREMENT(
502 p_group_id,
503 cur_job.wip_entity_id,
504 cur_job.organization_id,
505 x_err_code,
506 x_err_msg);
507
508 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_REQUIREMENT(
509 p_group_id,
510 cur_job.wip_entity_id,
511 cur_job.organization_id,
512 x_err_code,
513 x_err_msg);
514
515
516 /************** Processing resource usage **********************/
517 FOR l_cur IN RES_USAGE_CUR (cur_job.wip_entity_id,
518 cur_job.organization_id) LOOP
519
520 WIP_RES_USAGE_DEFAULT.Default_Resource_Usages
521 (p_group_id,
522 p_parent_header_id,
523 l_cur.wip_entity_id,
524 l_cur.organization_id,
525 x_err_code,
526 x_err_msg,
527 x_return_status);
528
529 WIP_RES_USAGE_VALIDATE.Validate_Usage(p_group_id,
530 l_cur.wip_entity_id,
531 l_cur.organization_id,
532 x_err_code,
533 x_err_msg,
534 x_return_status);
535
536 END LOOP; /* End req_cur loop for validation */
537
538 /**** Error out nocopy the whole job if any validations failed ****/
539 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
540 p_parent_header_id,
541 cur_job.wip_entity_id,
542 cur_job.organization_id);
543
544
545 WIP_JOB_DTLS_SUBSTITUTIONS.Substitution_Res_Usages
546 (p_group_id,
547 cur_job.wip_entity_id,
548 cur_job.organization_id,
549 x_err_code ,
550 x_err_msg,
551 x_return_status);
552
553 /********* Substitute Resources ********/
554 WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num ( p_group_id,
555 p_parent_header_id,
556 cur_job.wip_entity_id,
557 cur_job.organization_id);
558
559 FOR l_cur IN SUBRES_CUR (cur_job.wip_entity_id,
560 cur_job.organization_id) LOOP
561
562 IF l_cur.substitution_type = WIP_DELETE THEN
563 WIP_RESOURCE_VALIDATIONS.Delete_Sub_Resource(
564 p_group_id,
565 l_cur.wip_entity_id,
566 l_cur.organization_id,
567 l_cur.substitution_type);
568
569 ELSIF l_cur.substitution_type = WIP_ADD THEN
570 WIP_RESOURCE_VALIDATIONS.Add_Sub_Resource(
571 p_group_id,
572 l_cur.wip_entity_id,
573 l_cur.organization_id,
574 l_cur.substitution_type);
575
576 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
577 WIP_RESOURCE_VALIDATIONS.Change_Sub_Resource(
578 p_group_id,
579 l_cur.wip_entity_id,
580 l_cur.organization_id,
581 l_cur.substitution_type);
582 END IF;
583 END LOOP;
584
585 /**** Error out nocopy the whole job if any validations failed ****/
586 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
587 p_parent_header_id,
588 cur_job.wip_entity_id,
589 cur_job.organization_id);
590
591
592 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_SUB_RESOURCE(
593 p_group_id,
594 cur_job.wip_entity_id,
595 cur_job.organization_id,
596 x_err_code,
597 x_err_msg);
598
599 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_SUB_RESOURCE(
600 p_group_id,
601 cur_job.wip_entity_id,
602 cur_job.organization_id,
603 x_err_code,
604 x_err_msg);
605 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_SUB_RESOURCE(
606 p_group_id,
607 cur_job.wip_entity_id,
608 cur_job.organization_id,
609 x_err_code,
610 x_err_msg);
611
612 /** At this point, both resource and sub res changes have been done.
613 See if the sum of these changes created any violations of the
614 rules regarding sub groups **/
615 WIP_RESOURCE_VALIDATIONS.Check_Sub_Groups(p_group_id,
616 cur_job.organization_id,
617 cur_job.wip_entity_id);
618
619 /****** begin OPERATION LINKS processing ******************/
620 FOR l_cur IN OP_LINK_CUR (cur_job.wip_entity_id,
621 cur_job.organization_id) LOOP
622 /* WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num ( p_group_id,
623 p_parent_header_id,
624 cur_job.wip_entity_id,
625 cur_job.organization_id);
626 */
627
628 IF l_cur.substitution_type = WIP_DELETE THEN
629
630 WIP_OP_LINK_VALIDATIONS.Delete_Op_Link(
631 p_group_id,
632 l_cur.wip_entity_id,
633 l_cur.organization_id,
634 l_cur.substitution_type,
635 x_err_code,
636 x_err_msg,
637 x_return_status);
638
639 ELSIF l_cur.substitution_type = WIP_ADD THEN
640
641 WIP_OP_LINK_VALIDATIONS.Add_Op_Link(
642 p_group_id,
643 l_cur.wip_entity_id,
644 l_cur.organization_id,
645 l_cur.substitution_type,
646 x_err_code,
647 x_err_msg,
648 x_return_status);
649 END IF;
650 END LOOP;
651
652 /**** Error out nocopy the whole job if any validations failed ****/
653 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
654 p_parent_header_id,
655 cur_job.wip_entity_id,
656 cur_job.organization_id);
657
658 -- wipjdsts.pls
659 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_OP_LINK(
660 p_group_id,
661 cur_job.wip_entity_id,
662 cur_job.organization_id,
663 x_err_code,
664 x_err_msg);
665
666 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_OP_LINK(
667 p_group_id,
668 cur_job.wip_entity_id,
669 cur_job.organization_id,
670 x_err_code,
671 x_err_msg);
672
673 /****** end of OPERATION LINKS processing ******************/
674
675 /********* Associate Serial Numbers ********/
676 FOR l_cur IN SERIALS_CUR (cur_job.wip_entity_id,
677 cur_job.organization_id) LOOP
678
679 IF l_cur.substitution_type = WIP_DELETE THEN
680 WIP_SERIAL_ASSOC_VALIDATIONS.Delete_Serial(
681 p_group_id,
682 l_cur.wip_entity_id,
683 l_cur.organization_id,
684 l_cur.substitution_type);
685
686 ELSIF l_cur.substitution_type = WIP_ADD THEN
687 WIP_SERIAL_ASSOC_VALIDATIONS.Add_Serial(
688 p_group_id,
689 l_cur.wip_entity_id,
690 l_cur.organization_id,
691 l_cur.substitution_type);
692
693 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
694 WIP_SERIAL_ASSOC_VALIDATIONS.Change_Serial(
695 p_group_id,
696 l_cur.wip_entity_id,
697 l_cur.organization_id,
698 l_cur.substitution_type);
699 END IF;
700 END LOOP;
701
702 /**** Error out nocopy the whole job if any validations failed ****/
703 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
704 p_parent_header_id,
705 cur_job.wip_entity_id,
706 cur_job.organization_id);
707
708
709 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_SERIAL_ASSOCIATION(
710 p_group_id,
711 cur_job.wip_entity_id,
712 cur_job.organization_id,
713 x_err_code,
714 x_err_msg,
715 x_return_status);
716
717 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_SERIAL_ASSOCIATION(
718 p_group_id,
719 cur_job.wip_entity_id,
720 cur_job.organization_id,
721 x_err_code,
722 x_err_msg,
723 x_return_status);
724 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_SERIAL_ASSOCIATION(
725 p_group_id,
726 cur_job.wip_entity_id,
727 cur_job.organization_id,
728 x_err_code,
729 x_err_msg,
730 x_return_status);
731
732 WIP_JOB_DTLS_SUBSTITUTIONS.VERIFY_OPERATION
733 (p_group_id,
734 cur_job.wip_entity_id,
735 cur_job.organization_id,
736 x_err_code ,
737 x_err_msg,
738 x_return_status);
739
740 /**** Error out the whole job if any validations failed ****/
741 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
742 p_parent_header_id,
743 cur_job.wip_entity_id,
744 cur_job.organization_id);
745
746 SELECT count(*)
747 INTO x_count
748 from WIP_JOB_DTLS_INTERFACE
749 WHERE group_id = p_group_id
750 AND parent_header_id = p_parent_header_id
751 AND wip_entity_id = cur_job.wip_entity_id
752 AND organization_id = cur_job.organization_id
753 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
754 AND process_status = WIP_CONSTANTS.ERROR ;
755
756 IF x_count <> 0 THEN
757 x_err_code := -20239;
758 x_err_msg := 'VALIDATION ERROR HAPPENED!';
759 x_return_status := FND_API.G_RET_STS_ERROR;
760
761 END IF; /* end processing resource usage*/
762
763 /*************** CLEAN UP ************************/
764
765 IF x_err_code IS NULL THEN
766
767 /* set process_status = COMPLETED */
768 UPDATE wip_job_dtls_interface
769 SET process_status = WIP_CONSTANTS.COMPLETED
770 WHERE group_id = p_group_id
771 AND wip_entity_id = cur_job.wip_entity_id
772 AND (p_parent_header_id IS NULL OR
773 (p_parent_header_id IS NOT NULL AND
774 parent_header_id = p_parent_header_id))
775 AND organization_id = cur_job.organization_id
776 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
777 AND process_status = WIP_CONSTANTS.RUNNING ;
778
779 /* DELETE THE COMPLETED ROWS FROM INTERFACE TABLE */
780 DELETE from wip_job_dtls_interface
781 WHERE group_id = p_group_id
782 AND parent_header_id = p_parent_header_id
783 AND wip_entity_id = cur_job.wip_entity_id
784 AND organization_id = cur_job.organization_id
785 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
786 AND process_status = WIP_CONSTANTS.COMPLETED;
787
788 END IF;
789
790 WIP_JDI_Utils.end_processing_request(cur_job.wip_entity_id,
791 cur_job.organization_id);
792
793 END LOOP; /* end job_cursor in given group */
794
795 exception
796 when others then
797 if x_err_msg is null then
798 x_err_msg := 'WIPJDLDB load all details ' || SQLERRM;
799 x_err_code := SQLCODE;
800 end if;
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 IF std_alone = 1 THEN
803 rollback; /* rollback if there''s any error */
804 END IF;
805 end;
806 END Load_All_Details;
807
808
809 procedure default_wip_entity_id(p_group_id number,
810 p_parent_header_id number,
811 x_err_code out nocopy varchar2,
812 x_err_msg out nocopy varchar2,
813 x_return_status out nocopy varchar2) IS
814
815 l_wip_entity_id number;
816 l_organization_id number;
817 x_statement varchar(500);
818
819 cursor c_invalid_rows is
820 select interface_id
821 from wip_job_dtls_interface wjdi
822 where wjdi.group_id = p_group_id
823 and wjdi.parent_header_id = p_parent_header_id
824 and wjdi.process_phase = wip_constants.ml_validation
825 and wjdi.process_status = wip_constants.running
826 and wjdi.parent_header_id is not null
827 and ( wjdi.wip_entity_id is not null
828 or wjdi.organization_id is not null);
829
830 l_err_msg VARCHAR2(30);
831 l_error_exists boolean := false;
832 Begin
833
834 if(std_alone = 1) then
835 l_err_msg := 'WIP_HEADER_IGNORED';
836 else
837 l_err_msg := 'WIP_WEI_IGNORED';
838 end if;
839
840 for l_inv_row in c_invalid_rows loop
841 l_error_exists := true;
842 fnd_message.set_name('WIP', l_err_msg);
843 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
844 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
845 p_text => substr(fnd_message.get,1,500),
846 p_error_type => wip_jdi_utils.msg_error);
847 end loop;
848 if(l_error_exists) then
849 update wip_job_dtls_interface wjdi
850 set process_status = wip_constants.warning
851 where group_id = p_group_id
852 and parent_header_id = p_parent_header_id
853 and process_phase = wip_constants.ml_validation
854 and process_status = wip_constants.running
855 and wjdi.parent_header_id is not null
856 and ( wjdi.wip_entity_id is not null
857 or wjdi.organization_id is not null);
858
859 end if;
860
861 begin
862 IF p_parent_header_id IS NOT NULL AND
863 std_alone = 0 THEN
864 select wip_entity_id , organization_id
865 into l_wip_entity_id, l_organization_id
866 from wip_job_schedule_interface
867 where header_id = p_parent_header_id
868 and group_id = p_group_id
869 and process_status IN (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
870
871 Update wip_job_dtls_interface
872 Set wip_entity_id = l_wip_entity_id,
873 organization_id = l_organization_id
874 where group_id = p_group_id
875 and parent_header_id = p_parent_header_id
876 and process_phase = 2
877 and process_status in (2,5);
878 END IF;
879
880 exception
881 when no_data_found then --could not find the ML row
882 fnd_message.set_name('WIP', 'WIP_JOB_DOES_NOT_EXIST');
883 fnd_message.set_token('INTERFACE', to_char(wip_jsi_utils.current_interface_id));
884 x_err_code := SQLCODE;
885 x_err_msg := substr(fnd_message.get, 1, 500);
886 x_return_status := FND_API.G_RET_STS_ERROR;
887 when others then
888 x_err_code := SQLCODE;
889 x_err_msg := 'WIPJDLDB default wip_entity_id '||SQLERRM;
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 end;
892
893 END default_wip_entity_id;
894
895 End WIP_JOB_DETAILS;