[Home] [Help]
PACKAGE BODY: APPS.WIP_JOB_DETAILS
Source
1 Package Body WIP_JOB_DETAILS as
2 /* $Header: wipjdldb.pls 120.3 2011/09/19 14:01:20 sisankar ship $ */
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 l_params wip_logger.param_tbl_t;
157
158 BEGIN
159 l_params(1).paramName := 'p_groupID';
160 l_params(1).paramValue := p_group_id;
161 l_params(2).paramName := 'p_parent_header_id';
162 l_params(2).paramValue := p_parent_header_id;
163 l_params(3).paramName := 'p_std_alone';
164 l_params(3).paramValue := p_std_alone;
165
166 wip_logger.entryPoint(p_procName => 'WIP_JOB_DETAILS.Load_All_Details',
167 p_params => l_params,
168 x_returnStatus => l_dummy2);
169
170 begin
171 x_err_code := NULL;
172 x_err_msg := NULL;
173 x_count := 0;
174 std_alone := p_std_alone;
175 l_logLevel := fnd_log.g_current_runtime_level;
176
177 /** p_group_id can not be null **/
178 IF p_group_id IS NULL THEN
179 x_err_msg := 'ERROR: You have to specify a group_id to load job details.';
180 x_err_code := -999;
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 return;
183 END IF;
184
185 IF p_std_alone = 0 AND p_parent_header_id IS NULL THEN
186 x_err_msg := 'ERROR: You have to give a parent header id to specify the job.';
187 x_err_code := -999;
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 return;
190 END IF;
191
192
193 /********************************************************************
194 ***** for ALL jobs in the given group that has PENDING status ******
195 ********************************************************************/
196 /* set process_status = RUNNING and generate new unique interface_id*/
197 WIP_JDI_Utils.begin_processing_request(p_group_id,
198 p_parent_header_id,
199 x_err_code,
200 x_err_msg,
201 x_return_status);
202
203 default_wip_entity_id(p_group_id,
204 p_parent_header_id,
205 x_err_code,
206 x_err_msg,
207 x_return_status);
208
209 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
210 return;
211
212 END IF;
213
214 /* Following if condition is added for Bug#3636378 */
215 IF p_std_alone = 1 then
216 open wdj_cur ;
217 ELSE
218 open job_cur ;
219 END IF ;
220
221 LOOP
222 /* Following if condition is added for Bug#3636378 */
223 IF p_std_alone = 1 then
224 fetch wdj_cur into cur_job ;
225 exit when wdj_cur%NOTFOUND ;
226
227 /* bug 4650624 */
228 wip_jsi_utils.current_interface_id := null;
229 ELSE
230 fetch job_cur into cur_job ;
231 exit when job_cur%NOTFOUND ;
232
233 /* bug 4650624 */
234 select interface_id
235 into wip_jsi_utils.current_interface_id
236 from wip_job_schedule_interface
237 where group_id = p_group_id
238 and header_id = p_parent_header_id;
239
240 END IF ;
241
242 /*** Validate general info. for this job ***/
243 WIP_JOB_DTLS_VALIDATIONS.Jobs(p_group_id,
244 p_parent_header_id);
245
246 WIP_JOB_DTLS_VALIDATIONS.Job_Status(p_group_id,
247 p_parent_header_id);
248
249 WIP_JOB_DTLS_VALIDATIONS.Is_Firm(p_group_id,
250 p_parent_header_id);
251
252 WIP_JOB_DTLS_VALIDATIONS.Load_Sub_Types (p_group_id,
253 p_parent_header_id,
254 cur_job.wip_entity_id,
255 cur_job.organization_id);
256
257 WIP_JOB_DTLS_VALIDATIONS.Last_Updated_By(P_Group_Id,
258 p_parent_header_id,
259 cur_job.wip_entity_id,
260 cur_job.organization_id);
261 WIP_JOB_DTLS_VALIDATIONS.Created_By(P_Group_Id,
262 p_parent_header_id,
263 cur_job.wip_entity_id,
264 cur_job.organization_id);
265
266 FOR l_cur IN OPER_CUR (cur_job.wip_entity_id,
267 cur_job.organization_id) LOOP
268
269 BEGIN
270
271 WIP_OPERATION_DEFAULT.Default_Operations
272 (p_group_id,
273 p_parent_header_id,
274 l_cur.wip_entity_id,
275 l_cur.organization_id,
276 l_cur.substitution_type ,
277 x_err_code ,
278 x_err_msg ,
279 x_return_status );
280 exception
281 when others then
282 wip_logger.log('Error in WIP_OPERATION_DEFAULT.Default_Operations,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
283 wip_logger.log(SQLERRM,l_dummy2);
284 raise;
285 end;
286
287
288 /* default operation records */
289
290
291 IF l_cur.substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
292
293 BEGIN
294
295 WIP_OPERATION_VALIDATE.Add_Operation(p_group_id,
296 p_parent_header_id,
297 l_cur.wip_entity_id,
298 l_cur.organization_id,
299 x_err_code, x_err_msg,
300 x_return_status);
301 exception
302 when others then
303 wip_logger.log('Error in WIP_OPERATION_VALIDATE.Add_Operation,l_cur.wip_entity_id='||l_cur.wip_entity_id||' , substitution_type= '||WIP_JOB_DETAILS.WIP_ADD,l_dummy2);
304 wip_logger.log(SQLERRM,l_dummy2);
305 raise;
306 end;
307 /* validate operation records */
308
309 ELSIF l_cur.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
310
311 BEGIN
312 WIP_OPERATION_VALIDATE.Change_Operation(p_group_id,
313 p_parent_header_id,
314 l_cur.wip_entity_id,
315 l_cur.organization_id,
316 x_err_code, x_err_msg,
317 x_return_status);
318
319 EXCEPTION
320 when others then
321 wip_logger.log('Error in WIP_OPERATION_VALIDATE.Change_Operation,l_cur.wip_entity_id='||l_cur.wip_entity_id||' , substitution_type= '||WIP_JOB_DETAILS.WIP_CHANGE,l_dummy2);
322 wip_logger.log(SQLERRM,l_dummy2);
323 raise;
324 end;
325
326 END IF; /* end of operation validation */
327 END LOOP;
328
329 /**** Error out nocopy the whole job if any validations failed ****/
330 BEGIN
331 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
332 p_parent_header_id,
333 cur_job.wip_entity_id,
334 cur_job.organization_id);
335 EXCEPTION
336 when others then
337 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
338 wip_logger.log(SQLERRM,l_dummy2);
339 raise;
340 end;
341
342
343 BEGIN
344 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_OPERATION
345 (p_group_id,
346 cur_job.wip_entity_id,
347 cur_job.organization_id,
348 x_err_code ,
349 x_err_msg,
350 x_return_status);
351 EXCEPTION
352 when others then
353 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.ADD_OPERATION,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
354 wip_logger.log(SQLERRM,l_dummy2);
355 raise;
356 end;
357
358 begin
359 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_OPERATION
360 (p_group_id,
361 cur_job.wip_entity_id,
365 x_return_status);
362 cur_job.organization_id,
363 x_err_code ,
364 x_err_msg,
366 EXCEPTION
367 when others then
368 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_OPERATION,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
369 wip_logger.log(SQLERRM,l_dummy2);
370 raise;
371 end;
372
373 /*************END PROCESSING OPERATIONS********************/
374 BEGIN
375 WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num ( p_group_id,
376 p_parent_header_id,
377 cur_job.wip_entity_id,
378 cur_job.organization_id);
379 EXCEPTION
380 when others then
381 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
382 wip_logger.log(SQLERRM,l_dummy2);
383 raise;
384 end;
385
386 FOR l_cur IN RES_CUR (cur_job.wip_entity_id,
387 cur_job.organization_id) LOOP
388
389 IF l_cur.substitution_type = WIP_DELETE THEN
390 BEGIN
391 WIP_RESOURCE_VALIDATIONS.Delete_Resource(
392 p_group_id,
393 l_cur.wip_entity_id,
394 l_cur.organization_id,
395 l_cur.substitution_type);
396
397 EXCEPTION
398 when others then
399 wip_logger.log('Error in WIP_RESOURCE_VALIDATIONS.Delete_Resource,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
400 wip_logger.log(SQLERRM,l_dummy2);
401 raise;
402 end;
403
404 ELSIF l_cur.substitution_type = WIP_ADD THEN
405 BEGIN
406 WIP_RESOURCE_VALIDATIONS.Add_Resource(
407 p_group_id,
408 l_cur.wip_entity_id,
409 l_cur.organization_id,
410 l_cur.substitution_type);
411
412 EXCEPTION
413 when others then
414 wip_logger.log('Error in WIP_RESOURCE_VALIDATIONS.Add_Resource,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
415 wip_logger.log(SQLERRM,l_dummy2);
416 raise;
417 end;
418
419 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
420 BEGIN
421 WIP_RESOURCE_VALIDATIONS.Change_Resource(
422 p_group_id,
423 l_cur.wip_entity_id,
424 l_cur.organization_id,
425 l_cur.substitution_type);
426 EXCEPTION
427 when others then
428 wip_logger.log('Error in WIP_RESOURCE_VALIDATIONS.Change_Resource,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
429 wip_logger.log(SQLERRM,l_dummy2);
430 raise;
431 end;
432
433 END IF;
434 END LOOP;
435
436 /**** Error out nocopy the whole job if any validations failed ****/
437
438 BEGIN
439 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
440 p_parent_header_id,
441 cur_job.wip_entity_id,
442 cur_job.organization_id);
443
444 EXCEPTION
445 when others then
446 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
447 wip_logger.log(SQLERRM,l_dummy2);
448 raise;
449 end;
450
451
452 BEGIN
453 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE(
454 p_group_id,
455 cur_job.wip_entity_id,
456 cur_job.organization_id,
457 x_err_code,
458 x_err_msg);
459
460 EXCEPTION
461 when others then
462 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
463 wip_logger.log(SQLERRM,l_dummy2);
464 raise;
465 end;
466
467 BEGIN
468 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_RESOURCE(
469 p_group_id,
470 cur_job.wip_entity_id,
471 cur_job.organization_id,
472 x_err_code,
473 x_err_msg);
474
475 EXCEPTION
476 when others then
477 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.ADD_RESOURCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
478 wip_logger.log(SQLERRM,l_dummy2);
479 raise;
480 end;
481
482 BEGIN
483 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_RESOURCE(
484 p_group_id,
485 cur_job.wip_entity_id,
486 cur_job.organization_id,
487 x_err_code,
488 x_err_msg);
489 EXCEPTION
490 when others then
494 end;
491 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_RESOURCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
492 wip_logger.log(SQLERRM,l_dummy2);
493 raise;
495
496 FOR l_cur IN RESINST_CUR (cur_job.wip_entity_id,
497 cur_job.organization_id) LOOP
498
499 IF l_cur.substitution_type = WIP_DELETE THEN
500 BEGIN
501 WIP_RES_INST_VALIDATIONS.Delete_Resource_Instance(
502 p_group_id,
503 l_cur.wip_entity_id,
504 l_cur.organization_id,
505 l_cur.substitution_type,
506 x_err_code,
507 x_err_msg);
508
509 EXCEPTION
510 when others then
511 wip_logger.log('Error in WIP_RES_INST_VALIDATIONS.Delete_Resource_Instance,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
512 wip_logger.log(SQLERRM,l_dummy2);
513 raise;
514 end;
515 if (l_logLevel <= wip_constants.trace_logging) then
516 IF x_err_code IS NOT NULL THEN
517 wip_logger.log(x_err_code, l_dummy2);
518 wip_logger.log(x_err_msg, l_dummy2);
519 end if;
520 end if;
521
522 ELSIF l_cur.substitution_type = WIP_ADD THEN
523 WIP_RES_INST_VALIDATIONS.Add_Resource_Instance(
524 p_group_id,
525 l_cur.wip_entity_id,
526 l_cur.organization_id,
527 l_cur.substitution_type,
528 x_err_code,
529 x_err_msg);
530 if (l_logLevel <= wip_constants.trace_logging) then
531 IF x_err_code IS NOT NULL THEN
532 wip_logger.log(x_err_code, l_dummy2);
533 wip_logger.log(x_err_msg, l_dummy2);
534 end if;
535 end if;
536
537 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
538 WIP_RES_INST_VALIDATIONS.Change_Resource_Instance(
539 p_group_id,
540 l_cur.wip_entity_id,
541 l_cur.organization_id,
542 l_cur.substitution_type,
543 x_err_code,
544 x_err_msg);
545 if (l_logLevel <= wip_constants.trace_logging) then
546 IF x_err_code IS NOT NULL THEN
547 wip_logger.log(x_err_code, l_dummy2);
548 wip_logger.log(x_err_msg, l_dummy2);
549 end if;
550 end if;
551 END IF;
552 END LOOP;
553
554 /**** Error out nocopy the whole job if any validations failed ****/
555
556 BEGIN
557 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
558 p_parent_header_id,
559 cur_job.wip_entity_id,
560 cur_job.organization_id);
561
562 EXCEPTION
563 when others then
564 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
565 wip_logger.log(SQLERRM,l_dummy2);
566 raise;
567 end;
568
569
570 BEGIN
571 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE_INSTANCE(
572 p_group_id,
573 cur_job.wip_entity_id,
574 cur_job.organization_id,
575 WIP_JOB_DETAILS.WIP_DELETE,
576 x_err_code,
577 x_err_msg);
578 EXCEPTION
579 when others then
580 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE_INSTANCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
581 wip_logger.log(SQLERRM,l_dummy2);
582 raise;
583 end;
584
585 BEGIN
586 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_RESOURCE_INSTANCE(
587 p_group_id,
588 cur_job.wip_entity_id,
589 cur_job.organization_id,
590 x_err_code,
591 x_err_msg);
592 EXCEPTION
593 when others then
594 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.ADD_RESOURCE_INSTANCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
595 wip_logger.log(SQLERRM,l_dummy2);
596 raise;
597 end;
598
599 BEGIN
600 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_RESOURCE_INSTANCE(
601 p_group_id,
602 cur_job.wip_entity_id,
603 cur_job.organization_id,
604 x_err_code,
605 x_err_msg);
606 EXCEPTION
607 when others then
608 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_RESOURCE_INSTANCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
609 wip_logger.log(SQLERRM,l_dummy2);
610 raise;
611 end;
612
613 BEGIN
614
615 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
619 EXCEPTION
616 p_parent_header_id,
617 cur_job.wip_entity_id,
618 cur_job.organization_id);
620 when others then
621 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
622 wip_logger.log(SQLERRM,l_dummy2);
623 raise;
624 end;
625
626 /****** MATERAIL REQUIREMENTS processing ******************/
627 FOR l_cur IN REQ_CUR (cur_job.wip_entity_id,
628 cur_job.organization_id) LOOP
629
630 IF l_cur.substitution_type = WIP_DELETE THEN
631
632 BEGIN
633
634 WIP_REQUIREMENT_VALIDATIONS.Delete_Req(
635 p_group_id,
636 l_cur.wip_entity_id,
637 l_cur.organization_id,
638 l_cur.substitution_type);
639
640 EXCEPTION
641 when others then
642 wip_logger.log('Error in WIP_REQUIREMENT_VALIDATIONS.Delete_Req,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
643 wip_logger.log(SQLERRM,l_dummy2);
644 raise;
645 end;
646 ELSIF l_cur.substitution_type = WIP_ADD THEN
647
648 BEGIN
649 WIP_REQUIREMENT_VALIDATIONS.Add_Req(
650 p_group_id,
651 l_cur.wip_entity_id,
652 l_cur.organization_id,
653 l_cur.substitution_type);
654
655 EXCEPTION
656 when others then
657 wip_logger.log('Error in WIP_REQUIREMENT_VALIDATIONS.Add_Req,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
658 wip_logger.log(SQLERRM,l_dummy2);
659 raise;
660 end;
661 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
662
663 BEGIN
664 WIP_REQUIREMENT_VALIDATIONS.Change_Req(
665 p_group_id,
666 l_cur.wip_entity_id,
667 l_cur.organization_id,
668 l_cur.substitution_type);
669
670 EXCEPTION
671 when others then
672 wip_logger.log('Error in WIP_REQUIREMENT_VALIDATIONS.Change_Req,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
673 wip_logger.log(SQLERRM,l_dummy2);
674 raise;
675 end;
676
677 END IF; /* End requirement processing */
678 END LOOP;
679
680 /**** Error out nocopy the whole job if any validations failed ****/
681 BEGIN
682 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
683 p_parent_header_id,
684 cur_job.wip_entity_id,
685 cur_job.organization_id);
686 EXCEPTION
687 when others then
688 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
689 wip_logger.log(SQLERRM,l_dummy2);
690 raise;
691 end;
692
693
694 BEGIN
695 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_REQUIREMENT(
696 p_group_id,
697 cur_job.wip_entity_id,
698 cur_job.organization_id,
699 x_err_code,
700 x_err_msg);
701 EXCEPTION
702 when others then
703 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_REQUIREMENT,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
704 wip_logger.log(SQLERRM,l_dummy2);
705 raise;
706 end;
707
708
709 BEGIN
710 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_REQUIREMENT(
711 p_group_id,
712 cur_job.wip_entity_id,
713 cur_job.organization_id,
714 x_err_code,
715 x_err_msg);
716
717 EXCEPTION
718 when others then
719 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.ADD_REQUIREMENT,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
720 wip_logger.log(SQLERRM,l_dummy2);
721 raise;
722 end;
723
724 BEGIN
725 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_REQUIREMENT(
726 p_group_id,
727 cur_job.wip_entity_id,
728 cur_job.organization_id,
729 x_err_code,
730 x_err_msg);
731 EXCEPTION
732 when others then
733 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_REQUIREMENT,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
734 wip_logger.log(SQLERRM,l_dummy2);
735 raise;
736 end;
737
738
739 /************** Processing resource usage **********************/
740 FOR l_cur IN RES_USAGE_CUR (cur_job.wip_entity_id,
741 cur_job.organization_id) LOOP
742
743 BEGIN
744 WIP_RES_USAGE_DEFAULT.Default_Resource_Usages
745 (p_group_id,
749 x_err_code,
746 p_parent_header_id,
747 l_cur.wip_entity_id,
748 l_cur.organization_id,
750 x_err_msg,
751 x_return_status);
752
753 EXCEPTION
754 when others then
755 wip_logger.log('Error in WIP_RES_USAGE_DEFAULT.Default_Resource_Usages,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
756 wip_logger.log(SQLERRM,l_dummy2);
757 raise;
758 end;
759 BEGIN
760
761 WIP_RES_USAGE_VALIDATE.Validate_Usage(p_group_id,
762 l_cur.wip_entity_id,
763 l_cur.organization_id,
764 x_err_code,
765 x_err_msg,
766 x_return_status);
767
768 EXCEPTION
769 when others then
770 wip_logger.log('Error in WIP_RES_USAGE_VALIDATE.Validate_Usage,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
771 wip_logger.log(SQLERRM,l_dummy2);
772 raise;
773 end;
774 END LOOP; /* End req_cur loop for validation */
775
776 /**** Error out nocopy the whole job if any validations failed ****/
777
778 BEGIN
779 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
780 p_parent_header_id,
781 cur_job.wip_entity_id,
782 cur_job.organization_id);
783
784 EXCEPTION
785 when others then
786 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
787 wip_logger.log(SQLERRM,l_dummy2);
788 raise;
789 end;
790
791 BEGIN
792
793 WIP_JOB_DTLS_SUBSTITUTIONS.Substitution_Res_Usages
794 (p_group_id,
795 cur_job.wip_entity_id,
796 cur_job.organization_id,
797 x_err_code ,
798 x_err_msg,
799 x_return_status);
800
801 EXCEPTION
802 when others then
803 wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.Substitution_Res_Usages,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
804 wip_logger.log(SQLERRM,l_dummy2);
805 raise;
806 end;
807
808
809 /********* Substitute Resources ********/
810
811 BEGIN
812
813 WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num ( p_group_id,
814 p_parent_header_id,
815 cur_job.wip_entity_id,
816 cur_job.organization_id);
817
818 EXCEPTION
819 when others then
820 wip_logger.log('Error in WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
821 wip_logger.log(SQLERRM,l_dummy2);
822 raise;
823 end;
824
825 FOR l_cur IN SUBRES_CUR (cur_job.wip_entity_id,
826 cur_job.organization_id) LOOP
827
828
829 IF l_cur.substitution_type = WIP_DELETE THEN
830 WIP_RESOURCE_VALIDATIONS.Delete_Sub_Resource(
831 p_group_id,
832 l_cur.wip_entity_id,
833 l_cur.organization_id,
834 l_cur.substitution_type);
835
836 ELSIF l_cur.substitution_type = WIP_ADD THEN
837 WIP_RESOURCE_VALIDATIONS.Add_Sub_Resource(
838 p_group_id,
839 l_cur.wip_entity_id,
840 l_cur.organization_id,
841 l_cur.substitution_type);
842
843 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
844 WIP_RESOURCE_VALIDATIONS.Change_Sub_Resource(
845 p_group_id,
846 l_cur.wip_entity_id,
847 l_cur.organization_id,
848 l_cur.substitution_type);
849 END IF;
850 END LOOP;
851
852 /**** Error out nocopy the whole job if any validations failed ****/
853 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
854 p_parent_header_id,
855 cur_job.wip_entity_id,
856 cur_job.organization_id);
857
858
859
860
861 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_SUB_RESOURCE(
862 p_group_id,
863 cur_job.wip_entity_id,
864 cur_job.organization_id,
865 x_err_code,
866 x_err_msg);
867
868
869
870 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_SUB_RESOURCE(
871 p_group_id,
872 cur_job.wip_entity_id,
873 cur_job.organization_id,
874 x_err_code,
875 x_err_msg);
876
877 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_SUB_RESOURCE(
881 x_err_code,
878 p_group_id,
879 cur_job.wip_entity_id,
880 cur_job.organization_id,
882 x_err_msg);
883
884
885
886 /** At this point, both resource and sub res changes have been done.
887 See if the sum of these changes created any violations of the
888 rules regarding sub groups **/
889 WIP_RESOURCE_VALIDATIONS.Check_Sub_Groups(p_group_id,
890 cur_job.organization_id,
891 cur_job.wip_entity_id);
892
893
894
895 /****** begin OPERATION LINKS processing ******************/
896 FOR l_cur IN OP_LINK_CUR (cur_job.wip_entity_id,
897 cur_job.organization_id) LOOP
898 /* WIP_JOB_DTLS_VALIDATIONS.OP_Seq_Num ( p_group_id,
899 p_parent_header_id,
900 cur_job.wip_entity_id,
901 cur_job.organization_id);
902 */
903
904
905 IF l_cur.substitution_type = WIP_DELETE THEN
906
907 WIP_OP_LINK_VALIDATIONS.Delete_Op_Link(
908 p_group_id,
909 l_cur.wip_entity_id,
910 l_cur.organization_id,
911 l_cur.substitution_type,
912 x_err_code,
913 x_err_msg,
914 x_return_status);
915
916 ELSIF l_cur.substitution_type = WIP_ADD THEN
917
918 WIP_OP_LINK_VALIDATIONS.Add_Op_Link(
919 p_group_id,
920 l_cur.wip_entity_id,
921 l_cur.organization_id,
922 l_cur.substitution_type,
923 x_err_code,
924 x_err_msg,
925 x_return_status);
926 END IF;
927 END LOOP;
928
929 /**** Error out nocopy the whole job if any validations failed ****/
930 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
931 p_parent_header_id,
932 cur_job.wip_entity_id,
933 cur_job.organization_id);
934
935
936 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_OP_LINK(
937 p_group_id,
938 cur_job.wip_entity_id,
939 cur_job.organization_id,
940 x_err_code,
941 x_err_msg);
942
943
944 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_OP_LINK(
945 p_group_id,
946 cur_job.wip_entity_id,
947 cur_job.organization_id,
948 x_err_code,
949 x_err_msg);
950
951
952 /****** end of OPERATION LINKS processing ******************/
953
954 /********* Associate Serial Numbers ********/
955 FOR l_cur IN SERIALS_CUR (cur_job.wip_entity_id,
956 cur_job.organization_id) LOOP
957
958 IF l_cur.substitution_type = WIP_DELETE THEN
959 WIP_SERIAL_ASSOC_VALIDATIONS.Delete_Serial(
960 p_group_id,
961 l_cur.wip_entity_id,
962 l_cur.organization_id,
963 l_cur.substitution_type);
964
965 ELSIF l_cur.substitution_type = WIP_ADD THEN
966 WIP_SERIAL_ASSOC_VALIDATIONS.Add_Serial(
967 p_group_id,
968 l_cur.wip_entity_id,
969 l_cur.organization_id,
970 l_cur.substitution_type);
971
972 ELSIF l_cur.substitution_type = WIP_CHANGE THEN
973 WIP_SERIAL_ASSOC_VALIDATIONS.Change_Serial(
974 p_group_id,
975 l_cur.wip_entity_id,
976 l_cur.organization_id,
977 l_cur.substitution_type);
978 END IF;
979 END LOOP;
980
981 /**** Error out nocopy the whole job if any validations failed ****/
982 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
983 p_parent_header_id,
984 cur_job.wip_entity_id,
985 cur_job.organization_id);
986
987 WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_SERIAL_ASSOCIATION(
988 p_group_id,
989 cur_job.wip_entity_id,
990 cur_job.organization_id,
991 x_err_code,
992 x_err_msg,
993 x_return_status);
994
995 WIP_JOB_DTLS_SUBSTITUTIONS.ADD_SERIAL_ASSOCIATION(
996 p_group_id,
997 cur_job.wip_entity_id,
998 cur_job.organization_id,
1002
999 x_err_code,
1000 x_err_msg,
1001 x_return_status);
1003 WIP_JOB_DTLS_SUBSTITUTIONS.CHANGE_SERIAL_ASSOCIATION(
1004 p_group_id,
1005 cur_job.wip_entity_id,
1006 cur_job.organization_id,
1007 x_err_code,
1008 x_err_msg,
1009 x_return_status);
1010
1011 if (l_logLevel <= wip_constants.trace_logging) then
1012 wip_logger.log('wip_operations',l_dummy2);
1013 for wo_rec in (select * from wip_operations where wip_entity_id = cur_job.wip_entity_id and organization_id = cur_job.organization_id order by operation_seq_num) loop
1014 wip_logger.log('Op Seq '||wo_rec.operation_seq_num||' FUSD '||to_date(wo_rec.first_unit_start_date,WIP_CONSTANTS.DATETIME_FMT)||
1015 ' FUCD '||to_date(wo_rec.first_unit_completion_date,WIP_CONSTANTS.DATETIME_FMT) ||
1016 ' LUSD '||to_date(wo_rec.last_unit_start_date,WIP_CONSTANTS.DATETIME_FMT) ||
1017 ' LUCD '||to_date(wo_rec.last_unit_completion_date,WIP_CONSTANTS.DATETIME_FMT) ,l_dummy2);
1018 end loop;
1019 wip_logger.log('wip_operation_resources',l_dummy2);
1020 for wor_rec in (select * from wip_operation_resources where wip_entity_id = cur_job.wip_entity_id and organization_id = cur_job.organization_id order by operation_seq_num) loop
1021 wip_logger.log('Op Seq '||wor_rec.operation_seq_num||' Res Seq '||wor_rec.resource_seq_num||
1022 ' St Dt '||to_date(wor_rec.start_date,WIP_CONSTANTS.DATETIME_FMT) ||
1023 ' Cm Dt '||to_date(wor_rec.completion_date,WIP_CONSTANTS.DATETIME_FMT) ,l_dummy2);
1024 end loop;
1025 end if;
1026
1027 WIP_JOB_DTLS_SUBSTITUTIONS.VERIFY_OPERATION
1028 (p_group_id,
1029 cur_job.wip_entity_id,
1030 cur_job.organization_id,
1031 x_err_code ,
1032 x_err_msg,
1033 x_return_status);
1034
1035 /**** Error out the whole job if any validations failed ****/
1036 WIP_JOB_DTLS_VALIDATIONS.Error_All_If_Any(p_group_id,
1037 p_parent_header_id,
1038 cur_job.wip_entity_id,
1039 cur_job.organization_id);
1040
1041
1042 SELECT count(*)
1043 INTO x_count
1044 from WIP_JOB_DTLS_INTERFACE
1045 WHERE group_id = p_group_id
1046 AND parent_header_id = p_parent_header_id
1047 AND wip_entity_id = cur_job.wip_entity_id
1048 AND organization_id = cur_job.organization_id
1049 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1050 AND process_status = WIP_CONSTANTS.ERROR ;
1051
1052 IF x_count <> 0 THEN
1053 x_err_code := -20239;
1054 x_err_msg := 'VALIDATION ERROR HAPPENED!';
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056
1057 END IF; /* end processing resource usage*/
1058
1059 /*************** CLEAN UP ************************/
1060
1061 IF x_err_code IS NULL THEN
1062
1063 /* set process_status = COMPLETED */
1064 UPDATE wip_job_dtls_interface
1065 SET process_status = WIP_CONSTANTS.COMPLETED
1066 WHERE group_id = p_group_id
1067 AND wip_entity_id = cur_job.wip_entity_id
1068 AND (p_parent_header_id IS NULL OR
1069 (p_parent_header_id IS NOT NULL AND
1070 parent_header_id = p_parent_header_id))
1071 AND organization_id = cur_job.organization_id
1072 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1073 AND process_status = WIP_CONSTANTS.RUNNING ;
1074
1075 /* DELETE THE COMPLETED ROWS FROM INTERFACE TABLE */
1076 DELETE from wip_job_dtls_interface
1077 WHERE group_id = p_group_id
1078 AND parent_header_id = p_parent_header_id
1079 AND wip_entity_id = cur_job.wip_entity_id
1080 AND organization_id = cur_job.organization_id
1081 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1082 AND process_status = WIP_CONSTANTS.COMPLETED;
1083
1084 END IF;
1085
1086 WIP_JDI_Utils.end_processing_request(cur_job.wip_entity_id,
1087 cur_job.organization_id);
1088
1089 END LOOP; /* end job_cursor in given group */
1090
1091 exception
1092 when others then
1093 if x_err_msg is null then
1094 x_err_msg := 'WIPJDLDB load all details ' || SQLERRM;
1095 x_err_code := SQLCODE;
1096 end if;
1097 x_return_status := FND_API.G_RET_STS_ERROR;
1098 IF std_alone = 1 THEN
1099 rollback; /* rollback if there''s any error */
1100 END IF;
1101 end;
1102 END Load_All_Details;
1103
1104
1105 procedure default_wip_entity_id(p_group_id number,
1106 p_parent_header_id number,
1107 x_err_code out nocopy varchar2,
1108 x_err_msg out nocopy varchar2,
1109 x_return_status out nocopy varchar2) IS
1110
1111 l_wip_entity_id number;
1112 l_organization_id number;
1113 x_statement varchar(500);
1114
1115 cursor c_invalid_rows is
1116 select interface_id
1117 from wip_job_dtls_interface wjdi
1118 where wjdi.group_id = p_group_id
1119 and wjdi.parent_header_id = p_parent_header_id
1120 and wjdi.process_phase = wip_constants.ml_validation
1121 and wjdi.process_status = wip_constants.running
1122 and wjdi.parent_header_id is not null
1123 and ( wjdi.wip_entity_id is not null
1124 or wjdi.organization_id is not null);
1125
1126 l_err_msg VARCHAR2(30);
1127 l_error_exists boolean := false;
1128 Begin
1129
1130 if(std_alone = 1) then
1131 l_err_msg := 'WIP_HEADER_IGNORED';
1132 else
1133 l_err_msg := 'WIP_WEI_IGNORED';
1134 end if;
1135
1136 for l_inv_row in c_invalid_rows loop
1137 l_error_exists := true;
1138 fnd_message.set_name('WIP', l_err_msg);
1139 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1140 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1141 p_text => substr(fnd_message.get,1,500),
1142 p_error_type => wip_jdi_utils.msg_error);
1143 end loop;
1144 if(l_error_exists) then
1145 update wip_job_dtls_interface wjdi
1146 set process_status = wip_constants.warning
1147 where group_id = p_group_id
1148 and parent_header_id = p_parent_header_id
1149 and process_phase = wip_constants.ml_validation
1150 and process_status = wip_constants.running
1151 and wjdi.parent_header_id is not null
1152 and ( wjdi.wip_entity_id is not null
1153 or wjdi.organization_id is not null);
1154
1155 end if;
1156
1157 begin
1158 IF p_parent_header_id IS NOT NULL AND
1159 std_alone = 0 THEN
1160 select wip_entity_id , organization_id
1161 into l_wip_entity_id, l_organization_id
1162 from wip_job_schedule_interface
1163 where header_id = p_parent_header_id
1164 and group_id = p_group_id
1165 and process_status IN (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
1166
1167 Update wip_job_dtls_interface
1168 Set wip_entity_id = l_wip_entity_id,
1169 organization_id = l_organization_id
1170 where group_id = p_group_id
1171 and parent_header_id = p_parent_header_id
1172 and process_phase = 2
1173 and process_status in (2,5);
1174 END IF;
1175
1176 exception
1177 when no_data_found then --could not find the ML row
1178 fnd_message.set_name('WIP', 'WIP_JOB_DOES_NOT_EXIST');
1179 fnd_message.set_token('INTERFACE', to_char(wip_jsi_utils.current_interface_id));
1180 x_err_code := SQLCODE;
1181 x_err_msg := substr(fnd_message.get, 1, 500);
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 when others then
1184 x_err_code := SQLCODE;
1185 x_err_msg := 'WIPJDLDB default wip_entity_id '||SQLERRM;
1186 x_return_status := FND_API.G_RET_STS_ERROR;
1187 end;
1188
1189 END default_wip_entity_id;
1190
1191 End WIP_JOB_DETAILS;