1 PACKAGE BODY AMW_PROC_ORG_APPROVAL_PKG as
2 /*$Header: amwapogb.pls 120.3.12000000.4 2007/04/26 18:26:06 npanandi ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PROC_APPROVAL_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwapogb.pls';
6 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
7 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
8
9
10
11
12 procedure init is
13 begin
14
15 x_index_tbl.delete;
16 x_t1.delete;
17 x_t2.delete;
18
19 end init;
20
21
22
23 -- process submitted for approval
24 -- change status to PA and lock subprocesses
25 procedure sub_for_approval (p_process_id in number, p_org_id in number) is
26 approv_choice varchar2(10);
27 dummy1 varchar2(1);
28 dummy2 varchar2(1000);
29
30 begin
31
32 -- get the approval parameter for risk library
33 approv_choice := amw_utility_pvt.get_parameter(p_org_id, 'PROCESS_APPROVAL_OPTION');
34
35 -- error out if at least one risk or control associated with this process
36 -- does not have any approved revision.
37 prod_err_unapr_obj_ass_ex (p_process_id, p_org_id, approv_choice, 'Y', dummy1, dummy2);
38
39 -- assuming that the process is in Draft status, otherwise you can't submit it for approval.
40 update amw_process_organization
41 set approval_status = 'PA'
42 where process_id = p_process_id
43 and organization_id = p_org_id
44 and end_date is null;
45
46
47 -- approval choice cases:
48 -- (1) approve everything below
49 -- lock the process and all processes below,
50 -- note that the status of the downward processes do not change
51 -- (2) approve the process independently
52 -- lock only the process and its children
53 -- (3) don't approve unless everything below is approved.
54 -- same as (1)
55
56 insert into amw_process_locks
57 (organization_id,
58 locking_process_id,
59 locked_process_id,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_LOGIN,
63 CREATION_DATE,
64 CREATED_BY,
65 object_version_number
66 )
67 values
68 (
69 p_org_id,
70 p_process_id,
71 p_process_id,
72 sysdate,
73 G_USER_ID,
74 G_LOGIN_ID,
75 sysdate,
76 G_USER_ID,
77 1
78 );
79
80 if (approv_choice = 'B') then
81
82 insert into amw_process_locks
83 (organization_id,
84 locking_process_id,
85 locked_process_id,
86 LAST_UPDATE_DATE,
87 LAST_UPDATED_BY,
88 LAST_UPDATE_LOGIN,
89 CREATION_DATE,
90 CREATED_BY,
91 object_version_number
92 )
93 (select distinct p_org_id, p_process_id, child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
94 from amw_latest_hierarchies
95 start with parent_id = p_process_id and organization_id = p_org_id
96 connect by prior child_id = parent_id and organization_id = p_org_id );
97 --ko replacing the below clause...
98 /* (select p_org_id, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
99 from amw_org_hierarchy_denorm
100 where process_id = p_process_id
101 and up_down_ind = 'D'
102 and organization_id = p_org_id
103 and hierarchy_type = 'L');
104 */
105 elsif (approv_choice = 'A') then
106
107 insert into amw_process_locks
108 (organization_id,
109 locking_process_id,
110 locked_process_id,
111 LAST_UPDATE_DATE,
112 LAST_UPDATED_BY,
113 LAST_UPDATE_LOGIN,
114 CREATION_DATE,
115 CREATED_BY,
116 object_version_number
117 )
118 (select p_org_id, p_process_id, child_process_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
119 from amw_latest_hierarchy_ORG_V
120 where parent_process_id = p_process_id
121 and child_organization_id = p_org_id);
122
123 elsif (approv_choice = 'C') then
124
125 check_hier_approved(p_process_id, p_org_id);
126
127 insert into amw_process_locks
128 (organization_id,
129 locking_process_id,
130 locked_process_id,
131 LAST_UPDATE_DATE,
132 LAST_UPDATED_BY,
133 LAST_UPDATE_LOGIN,
134 CREATION_DATE,
135 CREATED_BY,
136 object_version_number
137 )(select distinct p_org_id, p_process_id, child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
138 from amw_latest_hierarchies
139 start with parent_id = p_process_id and organization_id = p_org_id
140 connect by prior child_id = parent_id and organization_id = p_org_id );
141 --ko replacing the below clause...
142 /* (select p_org_id, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1
143 from amw_org_hierarchy_denorm
144 where process_id = p_process_id
145 and up_down_ind = 'D'
146 and organization_id = p_org_id
147 and hierarchy_type = 'L');
148 */
149 end if;
150
151 end sub_for_approval;
152
153
154 -- process approved: update amw_process, change status to A
155 -- unlock process(es)
156 -- write into amw_approved_hierarchies
157 -- update the association_tables
158 -- update amw_proc_hierarchy_denorm table where hierarchy_type = 'A'
159 procedure approve(p_process_id in number, p_org_id in number,
160 p_update_count in varchar2 := FND_API.G_TRUE) is
161
162 rev_num number;
163 approv_choice varchar2(10);
164 curr_app_status varchar2(10);
165
166 cursor c1 (p_process_id number, p_org_id number) is
167 select distinct child_id parent_child_id
168 from amw_latest_hierarchies
169 start with parent_id = p_process_id and organization_id = p_org_id
170 connect by prior child_id = parent_id and organization_id = p_org_id;
171
172 cursor c2 (p_process_id number, p_org_id number) is
173 select distinct child_id parent_child_id,parent_id
174 from amw_latest_hierarchies
175 start with parent_id = p_process_id and organization_id = (-1*p_org_id)
176 connect by prior child_id = parent_id and organization_id = (-1*p_org_id);
177
178
179 --ko replacing the below clause...
180 /* select parent_child_id
181 from amw_org_hierarchy_denorm
182 where process_id = p_process_id
183 and up_down_ind = 'D'
184 and organization_id = p_org_id
185 and hierarchy_type = 'L';
186 */
187 c1_rec c1%rowtype;
188 c2_rec c2%rowtype;
189 pex varchar2(1);
190
191 begin
192
193 APPROV_TXN_DATE := sysdate;
194 -- check if the process is already approved, in that case, return
195 select approval_status
196 into curr_app_status
197 from amw_process_organization
198 where process_id = p_process_id
199 and organization_id = p_org_id
200 and end_date is null;
201
202 if curr_app_status = 'A' then
203 return;
204 end if;
205
206
207 -- release locks
208 approv_choice := amw_utility_pvt.get_parameter(p_org_id, 'PROCESS_APPROVAL_OPTION' );
209
210 delete from amw_process_locks
211 where organization_id = p_org_id
212 and locking_process_id = p_process_id;
213
214 update amw_process_organization
215 set approval_status = 'A',
216 approval_date = APPROV_TXN_DATE
217 where process_id = p_process_id
218 and organization_id = p_org_id
219 and end_date is null
220 returning revision_number into rev_num;
221
222 if rev_num > 1 then
223 update amw_process_organization
224 set approval_end_date = APPROV_TXN_DATE
225 where process_id = p_process_id
226 and organization_id = p_org_id
227 and revision_number = (rev_num-1);
228 end if;
229
230 approve_associations(p_process_id, p_org_id);
231 write_approved_hierarchy(p_process_id, 2, p_org_id);
232 -- kosriniv.. Approve the Exceptions..
233 approve_exceptions(p_org_id, p_process_id);
234 IF p_update_count = FND_API.G_TRUE THEN
235 -- Now updat the Org Count......
236 amw_rl_hierarchy_pkg.update_org_count(p_process_id);
237 END IF;
238
239 /**04.26.2007 npanandi: fix for bug 6017644, the below was not properly
240 commented out, leading to compilation errors
241 **/
242 --change for bug fix 5671087 starts here
243 pex := AMW_ORG_HIERARCHY_PKG.does_process_exist_in_org(p_process_id, p_org_id);
244
245
246 if (pex = 'D' and approv_choice = 'B') then
247
248 for c2_rec in c2(p_process_id, p_org_id) loop
249 exit when c2%notfound;
250
251 update amw_process_organization
252 set approval_status = 'A',
253 approval_date = APPROV_TXN_DATE
254 where process_id = c2_rec.parent_child_id
255 and organization_id = p_org_id
256 and end_date is null
257 returning revision_number into rev_num;
258
259 if rev_num > 1 then
260 update amw_process_organization
261 set approval_end_date = APPROV_TXN_DATE
262 where process_id = c2_rec.parent_child_id
263 and organization_id = p_org_id
264 and revision_number = (rev_num-1);
265 end if;
266
267 approve_associations(c2_rec.parent_child_id, p_org_id);
268 write_approved_hierarchy(c2_rec.parent_child_id, 2, p_org_id);
269 IF p_update_count = FND_API.G_TRUE THEN
270 -- Update the Org Count
271 amw_rl_hierarchy_pkg.update_org_count(c2_rec.parent_child_id);
272 END IF;
273
274 delete from amw_latest_hierarchies
275 where child_id = c2_rec.parent_child_id
276 and parent_id = c2_rec.parent_id
277 and organization_id = -p_org_id;
278
279
280
281 end loop;
282 write_approved_hierarchy(p_process_id,0 , p_org_id);
283
284 IF p_update_count = FND_API.G_TRUE THEN
285 amw_org_hierarchy_pkg.upd_appr_control_count(p_org_id, null); --ko, commenting this.. -2);
286 amw_org_hierarchy_pkg.upd_appr_risk_count(p_org_id, null); --ko the count api is currently aggregating in the upward direction -2);
287 END IF;
288
289 return;
290
291 end if;
292
293
294
295
296 --change for bug fix 5671087 ends here
297
298
299 if (approv_choice = 'B') then
300
301 for c1_rec in c1(p_process_id, p_org_id) loop
302 exit when c1%notfound;
303 update amw_process_organization
304 set approval_status = 'A',
305 approval_date = APPROV_TXN_DATE
306 where process_id = c1_rec.parent_child_id
307 and organization_id = p_org_id
308 and end_date is null
309 returning revision_number into rev_num;
310
311 if rev_num > 1 then
312 update amw_process_organization
313 set approval_end_date = APPROV_TXN_DATE
314 where process_id = c1_rec.parent_child_id
315 and organization_id = p_org_id
316 and revision_number = (rev_num-1);
317 end if;
318
319 approve_associations(c1_rec.parent_child_id, p_org_id);
320 write_approved_hierarchy(c1_rec.parent_child_id, 2, p_org_id);
321 IF p_update_count = FND_API.G_TRUE THEN
322 -- Update the Org Count
323 amw_rl_hierarchy_pkg.update_org_count(c1_rec.parent_child_id);
324 END IF;
325
326 end loop;
327
328 end if;
329
330 write_approved_hierarchy(p_process_id, 1, p_org_id);
331 --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
332 /*
333 amw_rl_hierarchy_pkg.update_approved_denorm(p_org_id);
334 */
335 IF p_update_count = FND_API.G_TRUE THEN
336 amw_org_hierarchy_pkg.upd_appr_control_count(p_org_id, null); --ko, commenting this.. -2);
337 amw_org_hierarchy_pkg.upd_appr_risk_count(p_org_id, null); --ko the count api is currently aggregating in the upward direction -2);
338 END IF;
339
340 end approve;
341
342
343 -- process approval rejected
344 -- change status D and unlock
345 procedure reject (p_process_id in number, p_org_id in number) is
346
347 begin
348 update amw_process_organization
349 set approval_status = 'D'
350 where process_id = p_process_id
351 and organization_id = p_org_id
352 and end_date is null;
353
354 delete from amw_process_locks
355 where organization_id = p_org_id
356 and locking_process_id = p_process_id;
357
358 end reject;
359
360
361 -- check that all processes below are approved, else produce error
362 procedure check_hier_approved(p_process_id in number, p_org_id in number) is
363
364 unappr_xst_excpt exception;
365 err_msg varchar2(4000);
366 l_dummy number;
367
368 begin
369
370 begin
371 select 1 --parent_child_id, a.approval_status
372 into l_dummy
373 from amw_process_organization a
374 where a.organization_id = p_org_id
375 and a.end_date is null
376 and a.approval_status <> 'A'
377 and a.process_id in ( select alh.child_id
378 from amw_latest_hierarchies alh
379 start with alh.parent_id = p_process_id and alh.organization_id = p_org_id
380 connect by prior alh.child_id = alh.parent_id and alh.organization_id = p_org_id);
381 --ko replacing the below clause...
382 /*
383 select 1 --parent_child_id, a.approval_status
384 into l_dummy
385 from amw_org_hierarchy_denorm d, amw_process_organization a
386 where d.process_id = p_process_id
387 and d.organization_id = p_org_id
391 and a.organization_id = p_org_id
388 and up_down_ind = 'D'
389 and hierarchy_type = 'L'
390 and a.process_id = d.parent_child_id
392 and a.end_date is null
393 and a.approval_status <> 'A';
394 */
395 raise unappr_xst_excpt;
396 exception
397 when no_data_found then
398 null;
399
400 when too_many_rows then
401 raise unappr_xst_excpt;
402 end;
403
404 exception
405
406 when unappr_xst_excpt then
407 rollback;
408 fnd_message.set_name('AMW','AMW_UNAPPROV_PROC_DOWN');
409 err_msg := fnd_message.get;
410 fnd_msg_pub.add_exc_msg(p_pkg_name => 'amw_proc_org_approval_pkg',
411 p_procedure_name => 'check_hier_approved',
412 p_error_text => err_msg);
413 raise;
414
415 end check_hier_approved;
416
417
418
419 -- check that all processes below are approved, else produce error
420 -- overloaded so as not to produce an expection
421 procedure check_hier_approved(p_process_id in number,
422 p_org_id in number,
423 p_result out nocopy varchar2,
424 p_out_mesg out nocopy varchar2) is
425
426 unappr_xst_excpt exception;
427 err_msg varchar2(4000);
428 l_dummy number;
429
430 begin
431 p_result := 'Y';
432 p_out_mesg := null;
433 begin
434 select 1 --parent_child_id, a.approval_status
435 into l_dummy
436 from amw_process_organization a
437 where a.organization_id = p_org_id
438 and a.end_date is null
439 and a.approval_status <> 'A'
440 and a.process_id in ( select alh.child_id
441 from amw_latest_hierarchies alh
442 start with alh.parent_id = p_process_id and alh.organization_id = p_org_id
443 connect by prior alh.child_id = alh.parent_id and alh.organization_id = p_org_id);
444 --ko replacing the below clause...
445 /*
446 select 1 --parent_child_id, a.approval_status
447 into l_dummy
448 from amw_org_hierarchy_denorm d, amw_process_organization a
449 where d.process_id = p_process_id
450 and d.organization_id = p_org_id
451 and up_down_ind = 'D'
452 and hierarchy_type = 'L'
453 and a.process_id = d.parent_child_id
454 and a.organization_id = p_org_id
455 and a.end_date is null
456 and a.approval_status <> 'A';
457 */
458 raise unappr_xst_excpt;
459 exception
460 when no_data_found then
461 null;
462
463 when too_many_rows then
464 raise unappr_xst_excpt;
465 end;
466
467 exception
468
469 when unappr_xst_excpt then
470 fnd_message.set_name('AMW','AMW_UNAPPROV_PROC_DOWN');
471 err_msg := fnd_message.get;
472 p_result := 'N';
473 p_out_mesg := err_msg;
474
475 end check_hier_approved;
476
477
478
479 procedure approve_associations(p_process_id in number, p_org_id in number) is
480
481 begin
482 update amw_risk_associations
483 set approval_date = APPROV_TXN_DATE
484 where pk2 = p_process_id
485 and pk1 = p_org_id
486 and object_type = 'PROCESS_ORG'
487 and approval_date is null;
488
489 update amw_risk_associations
490 set deletion_approval_date = APPROV_TXN_DATE
491 where pk2 = p_process_id
492 and pk1 = p_org_id
493 and object_type = 'PROCESS_ORG'
494 and deletion_date is not null
495 and deletion_approval_date is null;
496
497 update amw_control_associations
498 set approval_date = APPROV_TXN_DATE
499 where pk2 = p_process_id
500 and pk1 = p_org_id
501 and object_type = 'RISK_ORG'
502 and approval_date is null;
503
504 update amw_control_associations
505 set deletion_approval_date = APPROV_TXN_DATE
506 where pk2 = p_process_id
507 and pk1 = p_org_id
508 and object_type = 'RISK_ORG'
509 and deletion_date is not null
510 and deletion_approval_date is null;
511
512 update amw_acct_associations
513 set approval_date = APPROV_TXN_DATE
514 where pk2 = p_process_id
515 and pk1 = p_org_id
516 and object_type = 'PROCESS_ORG'
517 and approval_date is null;
518
519 update amw_acct_associations
520 set deletion_approval_date = APPROV_TXN_DATE
521 where pk2 = p_process_id
522 and pk1 = p_org_id
523 and object_type = 'PROCESS_ORG'
524 and deletion_date is not null
525 and deletion_approval_date is null;
526 --ko Approve Process Objectives and Control Objectives Associations..
527 update amw_objective_associations
528 set approval_date = APPROV_TXN_DATE
529 where pk1 = p_org_id
530 and pk2 = p_process_id
531 and object_type in ( 'PROCESS_ORG' , 'CONTROL_ORG')
532 and approval_date is null;
533
534 update amw_objective_associations
538 and object_type in ( 'PROCESS_ORG' , 'CONTROL_ORG')
535 set deletion_approval_date = APPROV_TXN_DATE
536 where pk1 = p_org_id
537 and pk2 = p_process_id
539 and deletion_date is not null
540 and deletion_approval_date is null;
541
542 -- Set the Start date of AP Associations...
543 update amw_ap_associations
544 set association_creation_date = sysdate
545 where pk1 = p_org_id
546 and pk2 = p_process_id
547 and object_type = 'CTRL_ORG'
548 and association_creation_date is null;
549
550 update AMW_AP_ASSOCIATIONS
551 set deletion_date = sysdate
552 WHERE object_type = 'CTRL_ORG'
553 and pk1 = p_org_id
554 and pk2 = p_process_id
555 and association_creation_date is not null
556 and pk3 not in ( SELECT control_id from
557 amw_control_associations
558 where object_type = 'RISK_ORG'
559 AND PK1 = p_org_id
560 AND PK2 = p_process_id
561 AND approval_date is not null
562 and deletion_approval_date is null);
563
564 /* update amw_objective_associations
565 set approval_date = APPROV_TXN_DATE
566 where pk1 = p_process_id
567 and object_type = 'PROCESS_ORG'
568 and approval_date is null;
569
570 update amw_objective_associations
571 set deletion_approval_date = APPROV_TXN_DATE
572 where pk1 = p_process_id
573 and object_type = 'PROCESS_ORG'
574 and deletion_date is not null
575 and deletion_approval_date is null;
576
577 update amw_significant_elements
578 set approval_date = APPROV_TXN_DATE
579 where pk1 = p_process_id
580 and object_type = 'PROCESS_ORG'
581 and approval_date is null;
582
583 update amw_significant_elements
584 set deletion_approval_date = APPROV_TXN_DATE
585 where pk1 = p_process_id
586 and object_type = 'PROCESS_ORG'
587 and deletion_date is not null
588 and deletion_approval_date is null; */
589
590 end approve_associations;
591
592
593
594 -- this procedure has three steps, for execution of three cursors that are explained below.
595 procedure write_approved_hierarchy(p_process_id in number, p_step in number, p_org_id in number,
596 p_appr_date in DATE := NULL) is
597
598 -- "approved" links that are in the latest hierarchy but not in the approved
599 -- hierarchy => links to be transferred to the approved hierarchy.
600 -- the assumption is that we do not manipulate the order_number data,
601 -- i.e. we faithfully store the exact number the sequence generates or user enters
602 -- and copy that number to the approved hierarchy.
603 -- executed when step = 1
604 /*ksr commenting
605 CURSOR c1 is
606 (select parent_process_id, child_process_id, child_order_number
607 from (select * from amw_latest_hierarchy_ORG_V where child_organization_id = p_org_id and parent_approval_status = 'A' and child_approval_status = 'A')
608 start with parent_process_id = -2
609 connect by prior child_process_id = parent_process_id)
610 MINUS
611 (select parent_process_id, child_process_id, child_order_number
612 from AMW_CURR_APP_HIERARCHY_ORG_V
613 where child_organization_id = p_org_id);
614 */
615
616 -- these links must be deleted from the approved hierarchy
617 -- executed when step = 2
618 CURSOR c2 is
619 (select parent_process_id, child_process_id from AMW_CURR_APP_HIERARCHY_ORG_V
620 where parent_process_id = p_process_id
621 and child_organization_id = p_org_id)
622 MINUS
623 (select parent_process_id, child_process_id from amw_latest_hierarchy_ORG_V
624 where parent_process_id = p_process_id
625 and child_organization_id = p_org_id);
626
627 -- as a result of the children being removed in c2 some links may become
628 -- defunct in the approved hierarchy ... we must remove those links
629 -- executed when step = 1
630 /* ksr commenting
631 CURSOR c3 is
632 (select parent_process_id, child_process_id from AMW_CURR_APP_HIERARCHY_ORG_V
633 where parent_process_id is not null
634 and child_organization_id = p_org_id)
635 MINUS
636 (select parent_process_id, child_process_id from
637 (select * from AMW_CURR_APP_HIERARCHY_ORG_V where child_organization_id = p_org_id)
638 start with parent_process_id = -2
639 connect by prior child_process_id = parent_process_id);
640 */
641
642
643 BEGIN
644 if p_appr_date is not null then
645 APPROV_TXN_DATE := p_appr_date;
646 end if;
647 if ( (p_step = 1) or (p_step = 0) ) then
648 /* kosriniv
649 for a_link in c1 loop
650 insert into amw_approved_hierarchies
651 (organization_id,
652 parent_id,
653 child_id,
654 start_date,
655 child_order_number,
656 LAST_UPDATE_DATE,
657 LAST_UPDATED_BY,
658 LAST_UPDATE_LOGIN,
659 CREATION_DATE,
663 (p_org_id,
660 CREATED_BY,
661 object_version_number )
662 values
664 a_link.parent_process_id,
665 a_link.child_process_id,
666 APPROV_TXN_DATE,
667 a_link.child_order_number,
668 sysdate,
669 G_USER_ID,
670 G_LOGIN_ID,
671 sysdate,
672 G_USER_ID,
673 1
674 );
675 end loop;
676 kosriniv */
677 added_rows(p_org_id);
678 if(x_parent_tbl.exists(1)) then
679
680 forall i in x_parent_tbl.first .. x_parent_tbl.last
681 insert into amw_approved_hierarchies
682 (organization_id,
683 parent_id,
684 child_id,
685 start_date,
686 child_order_number,
687 LAST_UPDATE_DATE,
688 LAST_UPDATED_BY,
689 LAST_UPDATE_LOGIN,
690 CREATION_DATE,
691 CREATED_BY,
692 object_version_number )
693 values
694 (p_org_id,
695 x_parent_tbl(i),
696 x_child_tbl(i),
697 APPROV_TXN_DATE,
698 x_child_ord_tbl(i),
699 sysdate,
700 G_USER_ID,
701 G_LOGIN_ID,
702 sysdate,
703 G_USER_ID,
704 1
705 );
706
707 end if;
708
709 end if;
710
711 -- I understand that this is not the most perfect use of object_version_number.
712 -- Ideally I should have obtained this number at the beginning of the procedure
713 -- and checked for concurrency here.
714
715 if p_step = 2 then
716 for defunct_link in c2 loop
717 update amw_approved_hierarchies
718 set end_date = APPROV_TXN_DATE,
719 object_version_number = object_version_number + 1
720 where organization_id = p_org_id
721 and parent_id = defunct_link.parent_process_id
722 and child_id = defunct_link.child_process_id
723 and end_date is null;
724 end loop;
725 end if;
726
727 if ( (p_step = 1) or (p_step = 3) ) then
728 /* kosriniv commenting
729 for defunct_link in c3 loop
730 update amw_approved_hierarchies
731 set end_date = APPROV_TXN_DATE,
732 object_version_number = object_version_number + 1
733 where organization_id = p_org_id
734 and parent_id = defunct_link.parent_process_id
735 and child_id = defunct_link.child_process_id
736 and end_date is null;
737 end loop;
738 */ invalid_rows(p_org_id);
739 if(x_parent_tbl.exists(1)) then
740 forall i in x_parent_tbl.first .. x_parent_tbl.last
741 update amw_approved_hierarchies
742 set end_date = APPROV_TXN_DATE,
743 object_version_number = object_version_number + 1
744 where organization_id = p_org_id
745 and parent_id = x_parent_tbl(i)
746 and child_id = x_child_tbl(i)
747 and end_date is null;
748 end if;
749 end if;
750
751 end write_approved_hierarchy;
752
753
754
755 procedure prod_err_unapr_obj_ass_ex (p_process_id in number,
756 p_org_id in number,
757 approve_option in varchar2,
758 raise_ex in varchar2,
759 p_result out nocopy varchar2,
760 p_out_mesg out nocopy varchar2 ) is
761
762 cursor process_list (pid number, p_org_id number) is
763 select distinct alh.child_id parent_child_id
764 from amw_latest_hierarchies alh
765 start with alh.child_id = p_process_id and alh.organization_id = p_org_id
766 connect by prior alh.child_id = alh.parent_id and alh.organization_id = p_org_id;
767 --ko replacing the below clause...
768 /*
769 select parent_child_id
770 from amw_org_hierarchy_denorm
771 where process_id = pid
772 and up_down_ind = 'D'
773 and hierarchy_type = 'L'
774 and organization_id = p_org_id
775 union
776 select pid from dual;
777 */
778
779 cursor ass_risks (pid number, poid number) is
780 select risk_id from amw_risk_associations where pk2 = pid and pk1 = poid and object_type = 'PROCESS_ORG';
781
782 cursor ass_controls (pid number, poid number) is
783 select control_id from amw_control_associations where pk2 = pid and pk1 = poid and object_type = 'RISK_ORG';
784
785 l_dummy number;
786 unappr_obj_exception exception;
787 err_msg varchar2(4000);
788
789 begin
790 p_result := 'Y';
791 p_out_mesg := null;
792
793 if approve_option = 'B' then
794
795 for process_list_rec in process_list(p_process_id, p_org_id) loop
796
797 for ass_risks_rec in ass_risks(process_list_rec.parent_child_id, p_org_id) loop
798
802 from amw_risks_b
799 begin
800 select 1
801 into l_dummy
803 where risk_id = ass_risks_rec.risk_id
804 and approval_status = 'A';
805
806 exception
807 when too_many_rows then
808 null;
809 when no_data_found then
810 raise unappr_obj_exception;
811 end;
812
813 end loop;
814
815
816 for ass_controls_rec in ass_controls(process_list_rec.parent_child_id, p_org_id) loop
817
818 begin
819 select 1
820 into l_dummy
821 from amw_controls_b
822 where control_id = ass_controls_rec.control_id
823 and approval_status = 'A';
824
825 exception
826 when too_many_rows then
827 null;
828 when no_data_found then
829 raise unappr_obj_exception;
830 end;
831
832 end loop;
833
834 end loop;
835 else
836 for ass_risks_rec in ass_risks(p_process_id, p_org_id) loop
837
838 begin
839 select 1
840 into l_dummy
841 from amw_risks_b
842 where risk_id = ass_risks_rec.risk_id
843 and approval_status = 'A';
844
845 exception
846 when too_many_rows then
847 null;
848 when no_data_found then
849 raise unappr_obj_exception;
850 end;
851
852 end loop;
853
854
855 for ass_controls_rec in ass_controls(p_process_id, p_org_id) loop
856
857 begin
858 select 1
859 into l_dummy
860 from amw_controls_b
861 where control_id = ass_controls_rec.control_id
862 and approval_status = 'A';
863
864 exception
865 when too_many_rows then
866 null;
867 when no_data_found then
868 raise unappr_obj_exception;
869 end;
870
871 end loop;
872 end if;
873
874
875 exception
876
877 when unappr_obj_exception then
878 fnd_message.set_name('AMW','AMW_UNAPPRV_ASSOC');
879 err_msg := fnd_message.get;
880 if raise_ex = 'Y' then
881 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_PROC_ORG_APPROVAL_PKG',
882 p_procedure_name => 'prod_err_unapr_obj_ass_ex',
883 p_error_text => err_msg);
884 raise;
885 else
886 p_result := 'N';
887 p_out_mesg := err_msg;
888 end if;
889
890 end prod_err_unapr_obj_ass_ex;
891
892
893
894 procedure autoapprove(
895 p_process_id in number,
896 p_org_id in number,
897 p_commit in varchar2 := FND_API.G_FALSE,
898 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
899 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
900 x_return_status out nocopy varchar2,
901 x_msg_count out nocopy number,
902 x_msg_data out nocopy varchar2 )
903
904 is
905
906 L_API_NAME CONSTANT VARCHAR2(30) := 'autoapprove';
907 l_return_status varchar2(10);
908 l_msg_count number;
909 l_msg_data varchar2(4000);
910
911 begin
912
913 x_return_status := FND_API.G_RET_STS_SUCCESS;
914 IF FND_API.to_Boolean( p_init_msg_list ) THEN
915 FND_MSG_PUB.initialize;
916 END IF;
917 IF FND_GLOBAL.User_Id IS NULL THEN
918 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
919 RAISE FND_API.G_EXC_ERROR;
920 END IF;
921
922 sub_for_approval (p_process_id, p_org_id);
923 approve(p_process_id, p_org_id);
924
925 exception
926
927 WHEN FND_API.G_EXC_ERROR THEN
928 ROLLBACK;
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
931
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933 ROLLBACK;
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
936
937 WHEN OTHERS THEN
938 ROLLBACK;
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
941 THEN
942 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
943 END IF;
947
944 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
945
946 end autoapprove;
948
949 /*
950 Produce error if:
951 1. approval option says "Don't approve this unless everything below is approved". There's at least one process below that is NOT approved.
952 2. there's at least one risk associated (to this process / any process in the downward hierarchy, depending on the approval option) that does not have an approved revision.
953 3. there's at least one control associated (to this process / any process in the downward hierarchy, depending on the approval option) that does not have an approved revision.
954 4. the process is non-standard, and the standard variation does not have an approved revision.
955 5. the process is non-standard and the list of children in the latest hierarchy is not the same as the list of children recorded in the variations table.
956 */
957 procedure check_approval_subm_eligib(
958 p_process_id in number,
959 p_org_id in number,
960 p_result out nocopy varchar2,
961 p_out_mesg out nocopy varchar2,
962 p_commit in varchar2 := FND_API.G_FALSE,
963 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
964 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
965 x_return_status out nocopy varchar2,
966 x_msg_count out nocopy number,
967 x_msg_data out nocopy varchar2 )
968
969 is
970
971 L_API_NAME CONSTANT VARCHAR2(30) := 'check_approval_subm_eligib';
972 l_return_status varchar2(10);
973 l_msg_count number;
974 l_msg_data varchar2(4000);
975 approv_choice varchar2(1);
976 std_process varchar2(1);
977 st_var_pid number;
978
979 begin
980
981 x_return_status := FND_API.G_RET_STS_SUCCESS;
982 IF FND_API.to_Boolean( p_init_msg_list ) THEN
983 FND_MSG_PUB.initialize;
984 END IF;
985 IF FND_GLOBAL.User_Id IS NULL THEN
986 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
987 RAISE FND_API.G_EXC_ERROR;
988 END IF;
989
990 p_result := 'Y';
991 p_out_mesg := null;
992
993 approv_choice := amw_utility_pvt.get_parameter(p_org_id, 'PROCESS_APPROVAL_OPTION');
994
995 if (approv_choice = 'C') then
996 check_hier_approved(p_process_id, p_org_id, p_result, p_out_mesg);
997 if p_result = 'N' then
998 return;
999 end if;
1000 end if;
1001
1002 prod_err_unapr_obj_ass_ex (p_process_id, p_org_id, approv_choice, 'N', p_result, p_out_mesg);
1003 if p_result = 'N' then
1004 return;
1005 end if;
1006
1007 exception
1008
1009 WHEN FND_API.G_EXC_ERROR THEN
1010 ROLLBACK;
1011 x_return_status := FND_API.G_RET_STS_ERROR;
1012 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1013
1014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1015 ROLLBACK;
1016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1017 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
1018
1019 WHEN OTHERS THEN
1020 ROLLBACK;
1021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1022 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1023 THEN
1024 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1025 END IF;
1026 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
1027
1028 end check_approval_subm_eligib;
1029
1030 PROCEDURE approve_exceptions(p_org_id IN NUMBER, p_process_id IN NUMBER) IS
1031 /* ======================================================================================================
1032 * What to do? *
1033 * 1. Find out all the new Exceptions of the process in cotext. *
1034 * 2. For each of the Exception, Examine whether an approved exception already exists with opposite action. *
1035 * If Exists , then End date both the old exception and newly created exception. *
1036 * 3. Approve all the remaining Exceptions. *
1037 ===================================================================================================== */
1038 CURSOR deleted_exceptions(org_id NUMBER, process_id NUMBER) IS
1039 SELECT exception_object_id,
1040 old_pk1,
1041 old_pk2,
1042 old_pk3,
1043 old_pk4,
1044 old_pk5,
1045 old_pk6,
1046 object_type
1047 FROM amw_exceptions_b
1048 WHERE approved_flag = 'N'
1049 AND old_PK1 = org_Id
1050 AND old_PK2 = process_id
1051 AND transaction_type = 'DEL'
1052 AND object_type IN ('PROCESS', 'RISK', 'CTRL')
1053 AND end_date IS NULL;
1054
1055 CURSOR added_exceptions(org_id NUMBER, process_id NUMBER) IS
1056 SELECT exception_object_id,
1057 new_pk1,
1058 new_pk2,
1059 new_pk3,
1060 new_pk4,
1061 new_pk5,
1062 new_pk6,
1063 object_type
1067 AND new_pk2 = process_id
1064 FROM amw_exceptions_b
1065 WHERE approved_flag = 'N'
1066 AND new_pk1 = org_Id
1068 AND transaction_type = 'ADD'
1069 AND object_type IN ('PROCESS', 'RISK', 'CTRL')
1070 AND end_date IS NULL;
1071
1072
1073 CURSOR past_appr_addd_ex(p_pk1 VARCHAR2, p_pk2 VARCHAR2, p_pk3 VARCHAR2,p_pk4 VARCHAR2,p_pk5 VARCHAR2,p_pk6 VARCHAR2, p_obj_type VARCHAR2)
1074 IS
1075 SELECT exception_object_id
1076 FROM amw_exceptions_b
1077 WHERE NVL(new_pk1, -99) = NVL(p_pk1, -99)
1078 AND NVL(new_pk2, -99) = NVL(p_pk2, -99)
1079 AND NVL(new_pk3, -99) = NVL(p_pk3, -99)
1080 AND NVL(new_pk4, -99) = NVL(p_pk4, -99)
1081 AND NVL(new_pk5, -99) = NVL(p_pk5, -99)
1082 AND NVL(new_pk6, -99) = NVL(p_pk6, -99)
1083 AND object_type = p_obj_type
1084 AND transaction_type = 'ADD'
1085 AND end_date IS NULL;
1086
1087
1088 CURSOR past_appr_del_ex(p_pk1 VARCHAR2, p_pk2 VARCHAR2, p_pk3 VARCHAR2,p_pk4 VARCHAR2,p_pk5 VARCHAR2,p_pk6 VARCHAR2, p_obj_type VARCHAR2)
1089 IS
1090 SELECT exception_object_id
1091 FROM amw_exceptions_b
1092 WHERE NVL(old_pk1, -99) = NVL(p_pk1, -99)
1093 AND NVL(old_pk2, -99) = NVL(p_pk2, -99)
1094 AND NVL(old_pk3, -99) = NVL(p_pk3, -99)
1095 AND NVL(old_pk4, -99) = NVL(p_pk4, -99)
1096 AND NVL(old_pk5, -99) = NVL(p_pk5, -99)
1097 AND NVL(old_pk6, -99) = NVL(p_pk6, -99)
1098 AND object_type = p_obj_type
1099 AND transaction_type = 'DEL'
1100 AND end_date IS NULL;
1101
1102 l_exception_exists BOOLEAN;
1103 deleted_exceptions_rec deleted_exceptions%ROWTYPE;
1104 added_exceptions_rec added_exceptions%ROWTYPE;
1105 past_appr_addd_ex_rec past_appr_addd_ex%ROWTYPE;
1106 past_appr_del_ex_rec past_appr_del_ex%ROWTYPE;
1107
1108 BEGIN
1109
1110 -- Handle All the Open Exceptions.. Ending if any previous exceptions Exists for it..
1111 for added_exceptions_rec in added_exceptions(p_org_id, p_process_id) LOOP
1112 EXIT WHEN added_exceptions%NOTFOUND;
1113 l_exception_exists := FALSE;
1114 FOR past_appr_del_ex_rec IN past_appr_del_ex(
1115 added_exceptions_rec.NEW_PK1,
1116 added_exceptions_rec.NEW_PK2,
1117 added_exceptions_rec.NEW_PK3,
1118 added_exceptions_rec.NEW_PK4,
1119 added_exceptions_rec.NEW_PK5,
1120 added_exceptions_rec.NEW_PK6,
1121 added_exceptions_rec.OBJECT_TYPE) LOOP
1122 EXIT WHEN past_appr_del_ex%NOTFOUND;
1123 l_exception_exists := TRUE;
1124 -- We have an past exception for the opposite action.. So end date that exception...
1125 UPDATE amw_exceptions_b SET end_date = SYSDATE where exception_object_id = past_appr_del_ex_rec.exception_object_id;
1126 END LOOP;
1127 IF l_exception_exists = TRUE THEN
1128 UPDATE amw_exceptions_b SET end_date = SYSDATE, approved_flag = 'Y' where exception_object_id = added_exceptions_rec.exception_object_id;
1129 END IF;
1130 END LOOP;
1131
1132 -- Similarly Check For the Delete Exceptions .... End Date if any previous exceptions exists..
1133 for deleted_exceptions_rec in deleted_exceptions(p_org_id, p_process_id) LOOP
1134 EXIT WHEN deleted_exceptions%NOTFOUND;
1135 l_exception_exists := FALSE;
1136 FOR past_appr_addd_ex_rec IN past_appr_addd_ex(
1137 deleted_exceptions_rec.OLD_PK1,
1138 deleted_exceptions_rec.OLD_PK2,
1139 deleted_exceptions_rec.OLD_PK3,
1140 deleted_exceptions_rec.OLD_PK4,
1141 deleted_exceptions_rec.OLD_PK5,
1142 deleted_exceptions_rec.OLD_PK6,
1143 deleted_exceptions_rec.OBJECT_TYPE) LOOP
1144 EXIT WHEN past_appr_addd_ex%NOTFOUND;
1145 l_exception_exists := TRUE;
1146 -- We have an past exception for the opposite action.. So end date that exception...
1147 UPDATE amw_exceptions_b SET end_date = SYSDATE where exception_object_id = past_appr_addd_ex_rec.exception_object_id;
1148 END LOOP;
1149 IF l_exception_exists = TRUE THEN
1150 UPDATE amw_exceptions_b SET end_date = SYSDATE, approved_flag = 'Y' where exception_object_id = deleted_exceptions_rec.exception_object_id;
1151 END IF;
1152 END LOOP;
1153
1154 -- After end dated those exceptions which have previous exceptions entered.. Approved the remaining excepitons..
1155
1156 UPDATE amw_exceptions_b
1157 SET approved_flag = 'Y'
1158 WHERE end_date is null
1159 AND ( (old_pk1 = p_org_id AND old_pk2 = p_process_id AND transaction_type = 'DEL')
1160 OR (new_pk1 = p_org_id AND new_pk2 = p_process_id AND transaction_type = 'ADD') )
1161 AND object_type IN ('PROCESS' , 'RISK' , 'CTRL');
1162
1163 END approve_exceptions;
1164
1165
1166 procedure add_child(p_process_id IN NUMBER)
1167 is
1168 temp1 tn;
1169 temp2 tn;
1170 str varchar2(50);
1171 BEGIN
1172 str := to_char(p_process_id);
1173 if(x_t1.exists(str)) then
1174 temp1 := x_t1(str);
1175 temp2 := x_t2(str);
1176 else
1177 temp1 := tn();
1178 temp2 := tn();
1179 end if;
1180 /* check to avoid numeric error */
1181 if(temp1.exists(1)) then
1182 --for each child (of the process on which the procedure is called)
1183 for i in temp1.first .. temp1.last loop
1184 add_child( p_process_id => temp1(i));
1185 x_valid_links.extend;
1186 x_valid_links(x_valid_links.last) := t_valid_link(p_process_id, temp1(i),temp2(i));
1187 end loop;
1188 end if ;
1189
1190 end add_child;
1191
1192 procedure encode_links(p_process_id IN NUMBER)
1193 is
1194 temp1 tn;
1195 str varchar2(50);
1196 BEGIN
1197 str := to_char(p_process_id);
1198 if(x_t1.exists(str)) then
1199 temp1 := x_t1(str);
1200 else
1201 temp1 := tn();
1202 end if;
1203 /* check to avoid numeric error */
1204 if(temp1.exists(1)) then
1205 --for each child (of the process on which the procedure is called)
1206 for i in temp1.first .. temp1.last loop
1207 encode_links( p_process_id => temp1(i));
1208 x_index_tbl(to_char(p_process_id) || ':' || to_char(temp1(i))) := 1;
1209 end loop;
1210 end if ;
1211
1212 end encode_links;
1213
1214
1215 procedure added_rows(p_org_id IN NUMBER)
1216 is
1217
1218 cursor c_all_latest_links_org(l_org_id in number) is
1219 select ah.parent_id , ah.child_id, AH.CHILD_ORDER_NUMBER
1220 from amw_latest_hierarchies ah
1221 where ah.parent_id =(select pp.process_id
1222 from amw_process_organization pp
1223 where pp.organization_id = ah.organization_id
1224 and pp.process_id = ah.parent_id
1225 and pp.end_date is null
1226 and pp.APPROVAL_STATUS ='A')
1227 and ah.child_id = ( select Cp.process_id
1228 from amw_process_organization Cp
1229 where Cp.organization_id = ah.organization_id
1230 and Cp.process_id = ah.child_id
1231 and Cp.end_date is null
1232 and Cp.APPROVAL_STATUS ='A')
1233 and ah.organization_id = l_org_id;
1234
1235 cursor c_all_approved_links_org(l_org_id in number) is
1236 select ah.parent_id , ah.child_id
1237 from amw_approved_hierarchies ah
1238 where ah.parent_id =(select pp.process_id
1239 from amw_process_organization pp
1240 where pp.organization_id = ah.organization_id
1241 and pp.process_id = ah.parent_id
1242 and pp.approval_date is not null
1243 and pp.approval_end_date is null
1244 and pp.deletion_date is null)
1245 and ah.child_id = ( select Cp.process_id
1246 from amw_process_organization Cp
1247 where Cp.organization_id = ah.organization_id
1248 and Cp.process_id = ah.child_id
1249 and Cp.approval_date is not null
1250 and Cp.approval_end_date is null
1251 and Cp.deletion_date is null)
1252 and ah.start_date is not null
1253 and ah.end_date is null
1254 and ah.organization_id = l_org_id;
1255
1256
1257 str varchar2(50);
1258 p_ltst_links_tbl ltst_links_tbl;
1259 p_appr_links_tbl appr_links_tbl;
1260
1261 begin
1262 init;
1263 open c_all_latest_links_org(p_org_id);
1264 fetch c_all_latest_links_org bulk collect into p_ltst_links_tbl;
1265 close c_all_latest_links_org;
1266
1267
1268 if (p_ltst_links_tbl.exists(1)) then
1269 for ctr in p_ltst_links_tbl.first .. p_ltst_links_tbl.last loop
1270 str := to_char(p_ltst_links_tbl(ctr).parent_id);
1271 x_t1(str) := tn();
1272 x_t2(str) := tn();
1273 end loop;
1274
1275 --put in all the links
1276 for ctr in p_ltst_links_tbl.first .. p_ltst_links_tbl.last loop
1277 str := to_char(p_ltst_links_tbl(ctr).parent_id);
1278 x_t1(str).extend;
1279 x_t2(str).extend;
1280 x_t1(str)(x_t1(str).last) := p_ltst_links_tbl(ctr).child_id;
1281 x_t2(str)(x_t2(str).last) := p_ltst_links_tbl(ctr).child_order_number;
1282 end loop;
1283 end if;
1284
1285 open c_all_approved_links_org(p_org_id);
1286 fetch c_all_approved_links_org bulk collect into p_appr_links_tbl;
1287 close c_all_approved_links_org;
1288
1289 if (p_appr_links_tbl.exists(1)) then
1290 for ctr in p_appr_links_tbl.first .. p_appr_links_tbl.last loop
1291 x_index_tbl(to_char(p_appr_links_tbl(ctr).parent_id) || ':' || to_char(p_appr_links_tbl(ctr).child_id)) := 1;
1292 end loop;
1293 end if;
1294 x_valid_links := t_valid_lt();
1295 add_child(-2);
1296 x_parent_tbl := tn();
1297 x_child_tbl := tn();
1298 x_child_ord_tbl := tn();
1299 if(x_valid_links.exists(1)) then
1300 for i in x_valid_links.first .. x_valid_links.last loop
1301 if (x_index_tbl.exists(to_char(x_valid_links(i)(1)) || ':' || to_char(x_valid_links(i)(2)))) then
1302 null;
1303 else
1304 x_parent_tbl.extend;
1305 x_child_tbl.extend;
1306 x_child_ord_tbl.extend;
1307
1308 x_parent_tbl(x_parent_tbl.last) := x_valid_links(i)(1);
1309 x_child_tbl(x_child_tbl.last) := x_valid_links(i)(2);
1310 x_child_ord_tbl(x_child_ord_tbl.last) := x_valid_links(i)(3);
1311 x_index_tbl(to_char(x_valid_links(i)(1)) || ':' || to_char(x_valid_links(i)(2))) := 1;
1312 end if;
1313 end loop;
1314 end if;
1315
1316 end added_rows;
1317
1318 procedure invalid_rows(p_org_id IN NUMBER) IS
1319
1320 cursor c_all_approved_links_org(l_org_id in number) is
1321 select ah.parent_id , ah.child_id
1322 from amw_approved_hierarchies ah
1323 where ah.parent_id =(select pp.process_id
1324 from amw_process_organization pp
1325 where pp.organization_id = ah.organization_id
1326 and pp.process_id = ah.parent_id
1327 and pp.approval_date is not null
1328 and pp.approval_end_date is null
1329 and pp.deletion_date is null)
1330 and ah.child_id = ( select Cp.process_id
1331 from amw_process_organization Cp
1332 where Cp.organization_id = ah.organization_id
1333 and Cp.process_id = ah.child_id
1334 and Cp.approval_date is not null
1335 and Cp.approval_end_date is null
1336 and Cp.deletion_date is null)
1337 and ah.start_date is not null
1338 and ah.end_date is null
1339 and ah.organization_id = l_org_id;
1340
1341 str varchar2(50);
1342 p_appr_links_tbl appr_links_tbl;
1343 begin
1344 init;
1345 open c_all_approved_links_org(p_org_id);
1346 fetch c_all_approved_links_org bulk collect into p_appr_links_tbl;
1347 close c_all_approved_links_org;
1348
1349
1350 if (p_appr_links_tbl.exists(1)) then
1351
1352 for ctr in p_appr_links_tbl.first .. p_appr_links_tbl.last loop
1353 str := to_char(p_appr_links_tbl(ctr).parent_id);
1354 x_t1(str) := tn();
1355 end loop;
1356
1357 --put in all the links
1358 for ctr in p_appr_links_tbl.first .. p_appr_links_tbl.last loop
1359 str := to_char(p_appr_links_tbl(ctr).parent_id);
1360 x_t1(str).extend;
1361 x_t1(str)(x_t1(str).last) := p_appr_links_tbl(ctr).child_id;
1362 end loop;
1363 encode_links(-2);
1364 x_parent_tbl := tn();
1365 x_child_tbl := tn();
1366 for ctr in p_appr_links_tbl.first .. p_appr_links_tbl.last loop
1367 if (x_index_tbl.exists(to_char(p_appr_links_tbl(ctr).parent_id) || ':' || to_char(p_appr_links_tbl(ctr).child_id))) then
1368 null;
1369 else
1370 x_parent_tbl.extend;
1371 x_child_tbl.extend;
1372 x_parent_tbl(x_parent_tbl.last) := p_appr_links_tbl(ctr).parent_id;
1373 x_child_tbl(x_child_tbl.last) := p_appr_links_tbl(ctr).child_id;
1374 end if;
1375 end loop;
1376 end if;
1377 end invalid_rows;
1378
1379 end AMW_PROC_ORG_APPROVAL_PKG;
[Home] [Help]