[Home] [Help]
PACKAGE BODY: APPS.WIP_OP_LINK_VALIDATIONS
Source
1 PACKAGE BODY WIP_OP_LINK_VALIDATIONS AS
2 /* $Header: wipolvdb.pls 120.0 2005/05/25 08:11:31 appldev noship $ */
3
4 TYPE link_type IS RECORD (
5 id NUMBER,
6 from_id NUMBER,
7 to_id NUMBER,
8 status NUMBER);
9
10 TYPE link_table IS TABLE OF link_type
11 INDEX BY BINARY_INTEGER;
12
13 links link_table;
14 next_link_id number := 0;
15
16 procedure insert_error(P_INTERFACE_ID NUMBER, P_ERROR VARCHAR2, P_ERROR_TYPE NUMBER) IS
17 BEGIN
18 /** Bug 2728127 -- removing GROUP_ID from insert as table 'wip_interface_errors'
19 does not have the column--this causes package to be INVALID **/
20 insert into wip_interface_errors (INTERFACE_ID, ERROR_TYPE, ERROR)
21 values (P_INTERFACE_ID, P_ERROR_TYPE, P_ERROR);
22 /** Not removing the 'commit' statement here as i don't know the impact of
23 removing right now
24 Also, not introducing 'PRAGMA AUTONOMOUS...' as it is not used anywhere
25 as per ID tool serach and filesystem grep -- not sure why this is present
26 here
27 **/
28 commit;
29 END insert_error;
30
31 procedure Exist_Op_Link(p_group_id in number, p_wip_entity_id in number, p_organization_id in number,
32 p_subst_type in number, x_err_code out nocopy varchar2,
33 x_err_msg out nocopy varchar2, x_return_status out nocopy varchar2);
34
35 function IS_Error(p_group_id number,
36 p_wip_entity_id number,
37 p_organization_id number,
38 p_substitution_type number) return number IS
39
40 x_count number := 0;
41
42 BEGIN
43
44 SELECT count(*)
45 INTO x_count
46 FROM WIP_JOB_DTLS_INTERFACE
47 WHERE group_id = p_group_id
48 AND process_status = WIP_CONSTANTS.ERROR
49 AND wip_entity_id = p_wip_entity_id
50 AND organization_id = p_organization_id
51 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
52 AND substitution_type= p_substitution_type;
53
54
55 IF x_count <> 0 THEN
56 return 1;
57 ELSE return 0;
58 END IF;
59
60 END IS_Error;
61
62
63 procedure Create_Link_Table(p_wip_entity_id in number, p_organization_id in number,
64 x_err_code out nocopy varchar2, x_err_msg out nocopy varchar2,
65 x_return_status out nocopy varchar2) is
66 CURSOR c_link_rows IS
67 SELECT prior_operation, next_operation
68 FROM wip_operation_networks
69 WHERE wip_entity_id = p_wip_entity_id
70 AND organization_id = p_organization_id;
71
72 l_id number;
73 next_link_id number := 1;
74
75 Begin
76 FOR cur_row in c_link_rows LOOP
77
78 l_id := next_link_id;
79 next_link_id := next_link_id + 1;
80 links(l_id).id := l_id;
81 links(l_id).from_id := to_number(cur_row.prior_operation);
82 links(l_id).to_id := to_number(cur_row.next_operation);
83 END LOOP;
84
85 /*
86 x_err_msg := 'ERROR: Create Link TAble.';
87 x_err_code := l_id;
88 x_return_status := FND_API.G_RET_STS_ERROR;
89 */
90 END Create_Link_Table;
91
92 /************************************************
93 * Check whether there are links leading *
94 * from_id to to_id. *
95 * This is used by function Is_Op_Completed *
96 * to detect link loop. *
97 ************************************************/
98 FUNCTION reachable(from_id number, to_id number) RETURN BOOLEAN IS
99 l_index number;
100
101 BEGIN
102
103 if from_id = to_id then
104 return true;
105 end if;
106 if links.count > 0 then
107 l_index := links.first;
108 loop
109 if links(l_index).from_id = from_id then
110 if reachable(links(l_index).to_id, to_id) then
111 return true;
112 end if;
113 end if;
114 exit when l_index = links.last;
115 l_index := links.next(l_index);
116 end loop;
117 end if;
118 return false;
119 END reachable;
120
121 procedure Loop_Exists(p_group_id in number, p_wip_entity_id in number, p_organization_id in number,
122 p_subst_type in number, x_err_code out nocopy varchar2, x_err_msg out nocopy varchar2,
123 x_return_status out nocopy varchar2) is
124 CURSOR c_link_rows IS
125 select interface_id, operation_seq_num, next_network_op_seq_num
126 FROM WIP_JOB_DTLS_INTERFACE wjdi
127 WHERE wjdi.group_id = p_group_id
128 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
129 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
130 AND wip_entity_id = p_wip_entity_id
131 AND organization_id = p_organization_id
132 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
133 AND substitution_type = p_subst_type;
134 /* AND exists (select 1
135 FROM WIP_OPERATION_NETWORKS
136 WHERE wip_entity_id = wjdi.wip_entity_id
137 AND organization_id = wjdi.organization_id
138 AND prior_operation = wjdi.operation_seq_num
139 AND next_operation = wjdi.next_network_op_seq_num);
140 */
141 l_error_exists boolean := false;
142 l_interface_id number;
143
144 Begin
145 FOR cur_row in c_link_rows LOOP
146
147 if (reachable(cur_row.operation_seq_num, cur_row.next_network_op_seq_num)) then
148 l_error_exists := true;
149 fnd_message.set_name('WIP', 'WIP_INV_OP_LINK');
150 fnd_message.set_token('INTERFACE', to_char(cur_row.interface_id));
151 wip_interface_err_Utils.add_error(p_interface_id => cur_row.interface_id,
152 p_text => substr(fnd_message.get,1,500),
153 p_error_type => wip_jdi_utils.msg_error);
154 end if;
155
156 END LOOP;
157
158 if(l_error_exists) then
159 update wip_job_dtls_interface wjdi
160 set process_status = wip_constants.error
161 where wjdi.group_id = p_group_id
162 and process_phase = wip_constants.ml_validation
163 and process_status in (wip_constants.running,
164 wip_constants.pending,
165 wip_constants.warning)
166 and wip_entity_id = p_wip_entity_id
167 and organization_id = p_organization_id
168 and load_type = wip_job_details.wip_op_link
169 and substitution_type = p_subst_type;
170 end if;
171
172 End Loop_Exists;
173
174 Procedure Validate_Op_Seq_Num(p_group_id in number,
175 p_wip_entity_id in number,
176 p_organization_id in number,
177 p_subst_type in number,
178 p_operation_seq_num in number) IS
179
180 CURSOR c_invalid_op_seq_num IS
181 select interface_id
182 FROM WIP_JOB_DTLS_INTERFACE wjdi
183 WHERE wjdi.group_id = p_group_id
184 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
185 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
186 AND wip_entity_id = p_wip_entity_id
187 AND organization_id = p_organization_id
188 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
189 AND substitution_type = p_subst_type
190 AND not exists (select 1
191 FROM WIP_OPERATIONS
192 WHERE wip_entity_id = p_wip_entity_id
193 AND organization_id = p_organization_id
194 AND operation_seq_num = p_operation_seq_num);
195
196 l_error_exists boolean := false;
197 l_op_seq_num number;
198
199 BEGIN
200 Open c_invalid_op_seq_num;
201 fetch c_invalid_op_seq_num into l_op_seq_num;
202 if c_invalid_op_seq_num%FOUND then
203 l_error_exists := true;
204 fnd_message.set_name('WIP', 'WIP_OP_DOES_NOT_EXIST');
205 fnd_message.set_token('INTERFACE', to_char(l_op_seq_num));
206 wip_interface_err_Utils.add_error(p_interface_id => l_op_seq_num,
207 p_text => substr(fnd_message.get,1,500),
208 p_error_type => wip_jdi_utils.msg_error);
209 end if;
210 close c_invalid_op_seq_num;
211
212 if(l_error_exists) then
213 update wip_job_dtls_interface wjdi
214 set process_status = wip_constants.error
215 where wjdi.group_id = p_group_id
216 and process_phase = wip_constants.ml_validation
217 and process_status in (wip_constants.running,
218 wip_constants.pending,
219 wip_constants.warning)
220 and wip_entity_id = p_wip_entity_id
221 and organization_id = p_organization_id
222 and load_type = wip_job_details.wip_op_link
223 and substitution_type = p_subst_type;
224 end if;
225
226 END Validate_Op_Seq_Num;
227
228 procedure Exist_Op_Seq_Num(p_group_id in number, p_wip_entity_id in number, p_organization_id in number,
229 p_subst_type in number) IS
230
231 CURSOR op_link_info IS
232 SELECT distinct operation_seq_num,
233 next_network_op_seq_num,
234 last_update_date, last_updated_by, creation_date, created_by,
235 last_update_login, request_id, program_application_id,
236 program_id, program_update_date,
237 attribute_category, attribute1,
238 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
239 attribute8,attribute9,attribute10,attribute11,attribute12,
240 attribute13,attribute14,attribute15
241 FROM WIP_JOB_DTLS_INTERFACE
242 WHERE group_id = p_group_id
243 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
244 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
245 AND wip_entity_id = p_wip_entity_id
246 AND organization_id = p_organization_id
247 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
248 AND substitution_type = p_subst_type;
249
250 l_operation_seq_num number;
251 from_id number;
252 to_id number;
253 l_error_exists boolean := false;
254 l_interface_id number;
255
256 BEGIN
257
258 FOR cur_row in op_link_info LOOP
259 from_id := cur_row.operation_seq_num;
260 to_id := cur_row.next_network_op_seq_num;
261 Validate_Op_Seq_Num(p_group_id, p_wip_entity_id, p_organization_id,
262 p_subst_type, cur_row.operation_seq_num);
263 Validate_Op_Seq_Num(p_group_id, p_wip_entity_id, p_organization_id,
264 p_subst_type, cur_row.next_network_op_seq_num);
265 END LOOP;
266 end;
267
268 procedure Is_Op_Completed(p_group_id in number, p_wip_entity_id in number, p_organization_id in number,
269 p_subst_type in number) IS
270
271 CURSOR op_link_info IS
272 SELECT distinct operation_seq_num,
273 next_network_op_seq_num,
274 last_update_date, last_updated_by, creation_date, created_by,
275 last_update_login, request_id, program_application_id,
276 program_id, program_update_date,
277 attribute_category, attribute1,
278 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
279 attribute8,attribute9,attribute10,attribute11,attribute12,
280 attribute13,attribute14,attribute15
281 FROM WIP_JOB_DTLS_INTERFACE
282 WHERE group_id = p_group_id
283 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
284 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
285 AND wip_entity_id = p_wip_entity_id
286 AND organization_id = p_organization_id
287 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
288 AND substitution_type = p_subst_type;
289
290 l_completed varchar2(30) := 'N';
291 l_prev_op_complete varchar2(30) := 'N';
292 l_next_op_start_date date := sysdate;
293 l_previous_op_completion_date date := sysdate;
294 l_operation_seq_num number;
295 from_id number;
296 to_id number;
297 l_error_exists boolean := false;
298 l_interface_id number;
299
300 BEGIN
301
302 FOR cur_row in op_link_info LOOP
303 from_id := cur_row.operation_seq_num;
304 to_id := cur_row.next_network_op_seq_num;
305 l_operation_seq_num := to_id;
306
307 if (p_wip_entity_id is not null and to_id is not null and from_id is not null) then
308 begin
309 select operation_completed,first_unit_start_date
310 into l_completed, l_next_op_start_date
311 from wip_operations
312 where wip_entity_id = p_wip_entity_id
313 and operation_seq_num = l_operation_seq_num;
314 exception
315 when others then
316 null;
317 end;
318
319 l_operation_seq_num := from_id;
320 begin
321 select operation_completed, last_unit_completion_date
322 into l_prev_op_complete, l_previous_op_completion_date
323 from wip_operations
324 where wip_entity_id = p_wip_entity_id
325 and operation_seq_num = l_operation_seq_num;
326 exception
327 when others then
328 null;
329 end;
330
331 select interface_id into l_interface_id
332 FROM WIP_JOB_DTLS_INTERFACE wjdi
333 WHERE wjdi.group_id = p_group_id
334 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
335 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
336 AND wip_entity_id = p_wip_entity_id
337 AND organization_id = p_organization_id
338 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
339 AND operation_seq_num = from_id
340 AND next_network_op_seq_num = to_id
341 AND substitution_type = p_subst_type;
342
343 if (nvl(l_completed, 'N') = 'Y' and nvl(l_prev_op_complete,'N') = 'N') then
344 l_error_exists := true;
345 FND_MESSAGE.SET_NAME('EAM', 'EAM_OP_TO_COMPLETE');
346 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
347 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
348 p_text => substr(fnd_message.get,1,500),
349 p_error_type => wip_jdi_utils.msg_error);
350
351 end if;
352
353 if (nvl(l_prev_op_complete,'N') = 'Y') then
354 l_error_exists := true;
355 FND_MESSAGE.SET_NAME('EAM', 'EAM_OP_FROM_COMPLETE');
356 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
357 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
358 p_text => substr(fnd_message.get,1,500),
359 p_error_type => wip_jdi_utils.msg_error);
360
361 end if;
362
363 if (l_next_op_start_date < l_previous_op_completion_date ) then
364 l_error_exists := true;
365 FND_MESSAGE.SET_NAME('EAM', 'EAM_DEP_OP_START_DATE_INVALID');
366 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
367 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
368 p_text => substr(fnd_message.get,1,500),
369 p_error_type => wip_jdi_utils.msg_error);
370 end if;
371 end if;
372
373 if(l_error_exists) then
374 update wip_job_dtls_interface wjdi
375 set process_status = wip_constants.error
376 where wjdi.group_id = p_group_id
377 and process_phase = wip_constants.ml_validation
378 and process_status in (wip_constants.running,
379 wip_constants.pending,
380 wip_constants.warning)
381 and wip_entity_id = p_wip_entity_id
382 and organization_id = p_organization_id
383 and load_type = wip_job_details.wip_op_link
384 and substitution_type = p_subst_type;
385
386 end if;
387 END Loop;
388 /*
389 wip_interface_err_Utils.load_errors;
390
391 fnd_message.set_name('WIP', 'WIP_OP_LINK_NOT_FOUND');
392 fnd_message.set_token('INTERFACE', 4567);
393 insert into wip_interface_errors (
394 interface_id,
395 error_type,
396 error,
397 last_update_date,
398 creation_date
399 ) values (
400 4567,
401 1,
402 substr(fnd_message.get,1,500),
403 sysdate,
404 sysdate
405 );
406 commit;
407 */
408 END Is_Op_Completed;
409
410 procedure Exist_Op_Link(p_group_id in number, p_wip_entity_id in number, p_organization_id in number,
411 p_subst_type in number, x_err_code out nocopy varchar2, x_err_msg out nocopy varchar2,
412 x_return_status out nocopy varchar2) is
413
414 CURSOR c_op_link_rows IS
415 select interface_id
416 FROM WIP_JOB_DTLS_INTERFACE wjdi
417 WHERE wjdi.group_id = p_group_id
418 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
419 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
420 AND wip_entity_id = p_wip_entity_id
421 AND organization_id = p_organization_id
422 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
423 AND substitution_type = p_subst_type
424 AND exists (select 1
425 FROM WIP_OPERATION_NETWORKS
426 WHERE wip_entity_id = wjdi.wip_entity_id
427 AND organization_id = wjdi.organization_id
428 AND prior_operation = wjdi.operation_seq_num
429 AND next_operation = wjdi.next_network_op_seq_num);
430
431 l_error_exists boolean := false;
432 l_interface_id number;
433
434 begin
435
436 Open c_op_link_rows;
437 fetch c_op_link_rows into l_interface_id;
438 if (p_subst_type = WIP_JOB_DETAILS.WIP_DELETE) then
439 if c_op_link_rows%NOTFOUND then
440 l_error_exists := true;
441 fnd_message.set_name('WIP', 'WIP_OP_LINK_NOT_FOUND');
442 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
443 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
444 p_text => substr(fnd_message.get,1,500),
445 p_error_type => wip_jdi_utils.msg_error);
446 end if;
447 elsif (p_subst_type = WIP_JOB_DETAILS.WIP_ADD) then
448 if c_op_link_rows%FOUND then
449 l_error_exists := true;
450 fnd_message.set_name('WIP', 'WIP_OP_LINK_EXISTS');
451 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
452 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
453 p_text => substr(fnd_message.get,1,500),
454 p_error_type => wip_jdi_utils.msg_error);
455 end if;
456 end if;
457 close c_op_link_rows;
458
459 if(l_error_exists) then
460 update wip_job_dtls_interface wjdi
461 set process_status = wip_constants.error
462 where wjdi.group_id = p_group_id
463 and process_phase = wip_constants.ml_validation
464 and process_status in (wip_constants.running,
465 wip_constants.pending,
466 wip_constants.warning)
467 and wip_entity_id = p_wip_entity_id
468 and organization_id = p_organization_id
469 and load_type = wip_job_details.wip_op_link
470 and substitution_type = p_subst_type;
471 end if;
472
473 /* x_return_status := FND_API.G_RET_STS_ERROR;
474 x_err_msg := 'ERROR IN WIPOPVDB.ADD_OPERATION: Deleting a non-existing operation link' ;
475 x_err_code := -9999;
476 */
477
478 end;
479
480
481 /* main delete, call the above. If any validation fail, it won't go on
482 with the next validations */
483 Procedure Delete_Op_Link(p_group_id in number,
484 p_wip_entity_id in number,
485 p_organization_id in number,
486 p_substitution_type in number,
487 x_err_code out nocopy varchar2,
488 x_err_msg out nocopy varchar2,
489 x_return_status out nocopy varchar2) IS
490
491 BEGIN
492
493 Exist_Op_Link(p_group_id, p_wip_entity_id, p_organization_id, p_substitution_type,
494 x_err_code , x_err_msg, x_return_status);
495
496 Exception
497
498 when others then
499 x_return_status := FND_API.G_RET_STS_ERROR;
500 x_err_msg := 'ERROR IN WIPOLVDB.DELETE_OP_LINK: ' || SQLERRM;
501 x_err_code := to_char(SQLCODE);
502
503 return;
504
505 END Delete_Op_Link;
506
507 /* main add, call the above */
508 Procedure Add_Op_Link(p_group_id in number,
509 p_wip_entity_id in number,
510 p_organization_id in number,
511 p_substitution_type in number,
512 x_err_code out nocopy varchar2,
513 x_err_msg out nocopy varchar2,
514 x_return_status out nocopy varchar2) IS
515
516 CURSOR op_link_info(p_group_Id number,
517 p_wip_entity_id number,
518 p_organization_id number,
519 p_substitution_type number) IS
520 SELECT distinct operation_seq_num,
521 next_network_op_seq_num,
522 last_update_date, last_updated_by, creation_date, created_by,
523 last_update_login, request_id, program_application_id,
524 program_id, program_update_date,
525 attribute_category, attribute1,
526 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
527 attribute8,attribute9,attribute10,attribute11,attribute12,
528 attribute13,attribute14,attribute15
529 FROM WIP_JOB_DTLS_INTERFACE
530 WHERE group_id = p_group_id
531 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
532 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
533 AND wip_entity_id = p_wip_entity_id
534 AND organization_id = p_organization_id
535 AND load_type = WIP_JOB_DETAILS.WIP_OP_LINK
536 AND substitution_type = p_substitution_type;
537
538 BEGIN
539
540 Exist_Op_Link(p_group_id, p_wip_entity_id, p_organization_id, p_substitution_type,
541 x_err_code , x_err_msg, x_return_status);
542 IF IS_Error(p_group_id,
543 p_wip_entity_id,
544 p_organization_id,
545 p_substitution_type) = 0 then
546
547 Is_Op_Completed(p_group_id, p_wip_entity_id, p_organization_id, p_substitution_type);
548 IF IS_Error(p_group_id,
549 p_wip_entity_id,
550 p_organization_id,
551 p_substitution_type) = 0 then
552
553 Exist_Op_Seq_Num(p_group_id, p_wip_entity_id, p_organization_id,
554 p_substitution_type);
555 IF IS_Error(p_group_id,
556 p_wip_entity_id,
557 p_organization_id,
558 p_substitution_type) = 0 then
559
560 Create_Link_Table( p_wip_entity_id, p_organization_id,
561 x_err_code , x_err_msg, x_return_status);
562 Loop_Exists(p_group_id, p_wip_entity_id, p_organization_id, p_substitution_type,
563 x_err_code , x_err_msg, x_return_status);
564 End If;
565 End If;
566 End If;
567
568 --wip_interface_err_Utils.load_errors;
569
570 Exception
571
572 when others then
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 x_err_msg := 'ERROR IN WIPOLVDB.ADD_OP_LINK: ' || SQLERRM;
575 x_err_code := to_char(SQLCODE);
576
577 return;
578
579 END Add_Op_Link;
580
581 END WIP_OP_LINK_VALIDATIONS;