1 PACKAGE BODY AMW_PROC_APPROVAL_PKG as
2 /*$Header: amwapprb.pls 120.3 2006/04/04 09:15:14 appldev noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PROC_APPROVAL_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwapprb.pls';
6 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
7 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
8
9 -- process submitted for approval
10 -- change status to PA and lock subprocesses
11 --NPANANDI 11.26.2004, ADDED P_WEBADI_CALL PARAMETER
12 --BECAUSE WHEN THIS IS CALLED FROM WEBADI, WE DON'T WANT TO CALL
13 --CHECK_HIER_APPROVED PROCEDURE
14 procedure sub_for_approval (
15 p_process_id in number
16 ,p_webadi_call in varchar2 := NULL) is
17 approv_choice varchar2(10);
18 dummy1 varchar2(1);
19 dummy2 varchar2(1000);
20 begin
21
22 -- get the approval parameter for risk library
23 approv_choice := amw_utility_pvt.get_parameter(-1, 'PROCESS_APPROVAL_OPTION');
24
25 -- error out if at least one risk or control associated with this process
26 -- does not have any approved revision.
27 prod_err_unapr_obj_ass_ex (p_process_id, approv_choice, 'Y', dummy1, dummy2);
28
29 -- assuming that the process is in Draft status, otherwise you can't submit it for approval.
30 update amw_process
31 set approval_status = 'PA'
32 where process_id = p_process_id
33 and end_date is null;
34
35 -- approval choice cases:
36 -- (1) approve everything below
37 -- lock the process and all processes below,
38 -- note that the status of the downward processes do not change
39 -- (2) approve the process independently
40 -- lock only the process and its children
41 -- (3) don't approve unless everything below is approved.
42 -- same as (1)
43
44 insert into amw_process_locks
45 (organization_id,
46 locking_process_id,
47 locked_process_id,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN,
51 CREATION_DATE,
52 CREATED_BY,
53 object_version_number)
54 values
55 (
56 -1,
57 p_process_id,
58 p_process_id,
59 sysdate,
60 G_USER_ID,
61 G_LOGIN_ID,
62 sysdate,
63 G_USER_ID,
64 1
65 );
66
67 if (approv_choice = 'B') then
68
69 insert into amw_process_locks
70 (organization_id,
71 locking_process_id,
72 locked_process_id,
73 LAST_UPDATE_DATE,
74 LAST_UPDATED_BY,
75 LAST_UPDATE_LOGIN,
76 CREATION_DATE,
77 CREATED_BY,
78 object_version_number)
79 (select -1, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID,
80 sysdate, G_USER_ID, 1
81 from amw_proc_hierarchy_denorm
82 where process_id = p_process_id
83 and up_down_ind = 'D'
84 and hierarchy_type = 'L');
85
86 elsif (approv_choice = 'A') then
87
88 insert into amw_process_locks
89 (organization_id,
90 locking_process_id,
91 locked_process_id,
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 LAST_UPDATE_LOGIN,
95 CREATION_DATE,
96 CREATED_BY,
97 object_version_number)
98 (select -1, p_process_id, child_process_id, sysdate, G_USER_ID, G_LOGIN_ID,
99 sysdate, G_USER_ID, 1
100 from amw_latest_hierarchy_rl_v
101 where parent_process_id = p_process_id);
102
103 elsif (approv_choice = 'C') then
104
105 --NPANANDI 11.26.2004, ADDED P_WEBADI_CALL PARAMETER
106 --BECAUSE WHEN THIS IS CALLED FROM WEBADI, WE DON'T WANT TO CALL
107 --CHECK_HIER_APPROVED PROCEDURE
108 IF(P_WEBADI_CALL IS NULL) THEN
109 check_hier_approved(p_process_id);
110 END IF;
111
112 insert into amw_process_locks
113 (organization_id,
114 locking_process_id,
115 locked_process_id,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN,
119 CREATION_DATE,
120 CREATED_BY,
121 object_version_number)
122 (select -1, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID,
123 sysdate, G_USER_ID, 1
124 from amw_proc_hierarchy_denorm
125 where process_id = p_process_id
126 and up_down_ind = 'D'
127 and hierarchy_type = 'L');
128 end if;
129
130 end sub_for_approval;
131
132
133 -- process approved: update amw_process, change status to A
134 -- unlock process(es)
135 -- write into amw_approved_hierarchy
136 -- update the association_tables
137 -- update amw_proc_hierarchy_denorm table where hierarchy_type = 'A'
138 procedure approve(p_process_id in number) is
139
140 rev_num number;
141 approv_choice varchar2(10);
142 curr_app_status varchar2(10);
143
144 cursor c1 (p_process_id number) is
145 /*
146 * ko .. We need to update only draft children
147 * select parent_child_id
148 from amw_proc_hierarchy_denorm
149 where process_id = p_process_id
150 and up_down_ind = 'D'
151 and hierarchy_type = 'L';
152 */
153 select ah.parent_child_id
154 from amw_proc_hierarchy_denorm ah,
155 amw_process ap
156 where ah.process_id = p_process_id
157 and ah.up_down_ind = 'D'
158 and ah.hierarchy_type = 'L'
159 and ah.parent_child_id = ap.process_id
160 and ap.end_date is null
161 and ap.approval_date is null;
162
163 c1_rec c1%rowtype;
164
165 begin
166
167 APPROV_TXN_DATE := sysdate;
168 -- check if the process is already approved, in that case, return
169 select approval_status
170 into curr_app_status
171 from amw_process
172 where process_id = p_process_id
173 and end_date is null;
174
175 if curr_app_status = 'A' then
176 return;
177 end if;
178
179
180 -- release locks
181 approv_choice := amw_utility_pvt.get_parameter(-1, 'PROCESS_APPROVAL_OPTION');
182
183 delete from amw_process_locks
184 where organization_id = -1
185 and locking_process_id = p_process_id;
186
187 update amw_process
188 set approval_status = 'A',
189 approval_date = APPROV_TXN_DATE
190 where process_id = p_process_id
191 and end_date is null
192 returning revision_number into rev_num;
193
194 if rev_num > 1 then
195 update amw_process
196 set approval_end_date = APPROV_TXN_DATE
197 where process_id = p_process_id
198 and revision_number = (rev_num-1);
199 end if;
200
201
202 --kosriniv ..Need to update the org count...
203 AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => p_process_id);
204 approve_associations(p_process_id);
205 write_approved_hierarchy(p_process_id, 2);
206
207
208 if (approv_choice = 'B') then
209
210 for c1_rec in c1(p_process_id) loop
211 exit when c1%notfound;
212 update amw_process
213 set approval_status = 'A',
214 approval_date = APPROV_TXN_DATE
215 where process_id = c1_rec.parent_child_id
216 and end_date is null
217 returning revision_number into rev_num;
218
219 if rev_num > 1 then
220 update amw_process
221 set approval_end_date = APPROV_TXN_DATE
222 where process_id = c1_rec.parent_child_id
223 and revision_number = (rev_num-1);
224 end if;
225 --kosriniv ..Need to update the org count...
226 AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => c1_rec.parent_child_id );
227 approve_associations(c1_rec.parent_child_id);
228 write_approved_hierarchy(p_process_id, 2);
229 end loop;
230
231 end if;
232
233 write_approved_hierarchy(p_process_id, 1);
234 amw_rl_hierarchy_pkg.update_approved_denorm(-1);
235 amw_rl_hierarchy_pkg.update_appr_control_counts;
236 amw_rl_hierarchy_pkg.update_appr_risk_counts;
237
238 end approve;
239
240
241 -- process approval rejected
242 -- change status D and unlock
243 procedure reject (p_process_id in number) is
244
245 begin
246 update amw_process
247 set approval_status = 'D'
248 where process_id = p_process_id
249 and end_date is null;
250
251 delete from amw_process_locks
252 where organization_id = -1
253 and locking_process_id = p_process_id;
254
255 end reject;
256
257
258 -- check that all processes below are approved, else produce error
259 procedure check_hier_approved(p_process_id in number) is
260
261 unappr_xst_excpt exception;
262 err_msg varchar2(4000);
263 l_dummy number;
264
265 begin
266
267 begin
268 select 1 --parent_child_id, a.approval_status
269 into l_dummy
270 from amw_proc_hierarchy_denorm d, amw_process a
271 where d.process_id = p_process_id
272 and up_down_ind = 'D'
273 and hierarchy_type = 'L'
274 and a.process_id = d.parent_child_id
275 and a.end_date is null
276 and a.approval_status <> 'A';
277
278 raise unappr_xst_excpt;
279 exception
280 when no_data_found then
281 null;
282
283 when too_many_rows then
284 raise unappr_xst_excpt;
285 end;
286
287 exception
288
289 when unappr_xst_excpt then
290 rollback;
291 fnd_message.set_name('AMW','AMW_UNAPPROV_PROC_DOWN');
292 err_msg := fnd_message.get;
293 fnd_msg_pub.add_exc_msg(p_pkg_name => 'amw_proc_approval_pkg',
294 p_procedure_name => 'check_hier_approved',
295 p_error_text => err_msg);
296 raise;
297
298 end check_hier_approved;
299
300
301 procedure approve_associations(p_process_id in number) is
302
303 begin
304 update amw_risk_associations
305 set approval_date = APPROV_TXN_DATE
306 where pk1 = p_process_id
307 and object_type = 'PROCESS'
308 and approval_date is null;
309
310 update amw_risk_associations
311 set deletion_approval_date = APPROV_TXN_DATE
312 where pk1 = p_process_id
313 and object_type = 'PROCESS'
314 and deletion_date is not null
315 and deletion_approval_date is null;
316
317 update amw_control_associations
318 set approval_date = APPROV_TXN_DATE
319 where pk1 = p_process_id
320 and object_type = 'RISK'
321 and approval_date is null;
322
323 update amw_control_associations
324 set deletion_approval_date = APPROV_TXN_DATE
325 where pk1 = p_process_id
326 and object_type = 'RISK'
327 and deletion_date is not null
328 and deletion_approval_date is null;
329
330 update amw_acct_associations
331 set approval_date = APPROV_TXN_DATE
332 where pk1 = p_process_id
333 and object_type = 'PROCESS'
334 and approval_date is null;
335
336 update amw_acct_associations
337 set deletion_approval_date = APPROV_TXN_DATE
338 where pk1 = p_process_id
339 and object_type = 'PROCESS'
340 and deletion_date is not null
341 and deletion_approval_date is null;
342
343 update amw_objective_associations
344 set approval_date = APPROV_TXN_DATE
345 where pk1 = p_process_id
346 and object_type in ('PROCESS', 'CONTROL')
347 and approval_date is null;
348
349 update amw_objective_associations
350 set deletion_approval_date = APPROV_TXN_DATE
351 where pk1 = p_process_id
352 and object_type in ('PROCESS', 'CONTROL')
353 and deletion_date is not null
357 set approval_date = APPROV_TXN_DATE
354 and deletion_approval_date is null;
355
356 update amw_significant_elements
358 where pk1 = p_process_id
359 and object_type = 'PROCESS'
360 and approval_date is null;
361
362 update amw_significant_elements
363 set deletion_approval_date = APPROV_TXN_DATE
364 where pk1 = p_process_id
365 and object_type = 'PROCESS'
366 and deletion_date is not null
367 and deletion_approval_date is null;
368
369 end approve_associations;
370
371
372
373 -- this procedure has three steps, for execution of three cursors that are explained below.
374 procedure write_approved_hierarchy(
375 p_process_id in number,
376 p_step in number) is
377
378 -- "approved" links that are in the latest hierarchy but not in the approved
379 -- hierarchy => links to be transferred to the approved hierarchy.
380 -- the assumption is that we do not manipulate the order_number data,
381 -- i.e. we faithfully store the exact number the sequence generates or user enters
382 -- and copy that number to the approved hierarchy.
383 -- executed when step = 1
384 CURSOR c1 is
385 (select parent_process_id,
386 child_process_id,
387 child_order_number
388 from (select *
389 from amw_latest_hierarchy_rl_v
390 where parent_approval_status = 'A'
391 and child_approval_status = 'A')
392 start with parent_process_id = -1
393 connect by prior child_process_id = parent_process_id)
394 MINUS
395 (select parent_process_id,
396 child_process_id,
397 child_order_number
398 from AMW_CURR_APP_HIERARCHY_RL_V);
399
400 -- these links must be deleted from the approved hierarchy
401 -- executed when step = 2
402 CURSOR c2 is
403 (select parent_process_id,
404 child_process_id
405 from AMW_CURR_APP_HIERARCHY_RL_V
406 where parent_process_id = p_process_id)
407 MINUS
408 (select parent_process_id,
409 child_process_id
410 from amw_latest_hierarchy_rl_v
411 where parent_process_id = p_process_id);
412
413
414 -- 05.11.2005 npanandi: added below cursor for WebADI call
415 -- c2_1 is the same as c2 above, exception that it does not
416 -- take pProcessId as a bind variable
417 CURSOR c2_1 is
418 (select parent_process_id,
419 child_process_id
420 from AMW_CURR_APP_HIERARCHY_RL_V)
421 MINUS
422 (select parent_process_id,
423 child_process_id
424 from amw_latest_hierarchy_rl_v);
425
426
427 -- as a result of the children being removed in c2 some links may become
428 -- defunct in the approved hierarchy ... we must remove those links
429 -- executed when step = 1
430 CURSOR c3 is
431 (select parent_process_id,
432 child_process_id
433 from AMW_CURR_APP_HIERARCHY_RL_V
434 where parent_process_id is not null)
435 MINUS
436 (select parent_process_id,
437 child_process_id
438 from AMW_CURR_APP_HIERARCHY_RL_V
439 start with parent_process_id = -1
440 connect by prior child_process_id = parent_process_id);
441 BEGIN
442 if ( (p_step = 1) or (p_step = 0) ) then
443 for a_link in c1 loop
444 insert into amw_approved_hierarchies(
445 organization_id,
446 parent_id,
447 child_id,
448 start_date,
449 child_order_number,
450 LAST_UPDATE_DATE,
451 LAST_UPDATED_BY,
452 LAST_UPDATE_LOGIN,
453 CREATION_DATE,
454 CREATED_BY,
455 object_version_number
456 )values(
457 -1,
458 a_link.parent_process_id,
459 a_link.child_process_id,
460 APPROV_TXN_DATE,
461 a_link.child_order_number,
462 sysdate,
463 G_USER_ID,
464 G_LOGIN_ID,
465 sysdate,
466 G_USER_ID,
467 1
468 );
469 end loop;
470 end if;
471
472 if p_step = 2 then
473 for defunct_link in c2 loop
474 update amw_approved_hierarchies
475 set end_date = APPROV_TXN_DATE,
476 object_version_number = object_version_number + 1
477 where organization_id = -1
478 and parent_id = defunct_link.parent_process_id
479 and child_id = defunct_link.child_process_id
480 and end_date is null;
481 end loop;
482 end if;
483
484 if ( (p_step = 1) or (p_step = 3) ) then
485 for defunct_link in c3 loop
486 update amw_approved_hierarchies
487 set end_date = APPROV_TXN_DATE,
488 object_version_number = object_version_number + 1
489 where organization_id = -1
490 and parent_id = defunct_link.parent_process_id
491 and child_id = defunct_link.child_process_id
492 and end_date is null;
493 end loop;
494 end if;
495
496 ---05.11.2005 npanandi: added below step to club the above actions
497 ---when calling this procedure from WebADI
498 if(p_step=4) then
499 for a_link in c1 loop
500 insert into amw_approved_hierarchies(
501 organization_id,
502 parent_id,
503 child_id,
504 start_date,
505 child_order_number,
506 LAST_UPDATE_DATE,
507 LAST_UPDATED_BY,
508 LAST_UPDATE_LOGIN,
509 CREATION_DATE,
510 CREATED_BY,
511 object_version_number
512 )values(
513 -1,
514 a_link.parent_process_id,
518 sysdate,
515 a_link.child_process_id,
516 APPROV_TXN_DATE,
517 a_link.child_order_number,
519 G_USER_ID,
520 G_LOGIN_ID,
521 sysdate,
522 G_USER_ID,
523 1
524 );
525 end loop;
526
527 for defunct_link in c2_1 loop
528 update amw_approved_hierarchies
529 set end_date = APPROV_TXN_DATE
530 ,object_version_number = object_version_number + 1
531 ,last_update_date = sysdate
532 ,last_updated_by = G_USER_ID
533 ,last_update_login = G_LOGIN_ID
534 where organization_id = -1
535 and parent_id = defunct_link.parent_process_id
536 and child_id = defunct_link.child_process_id
537 and end_date is null;
538 end loop;
539
540 for defunct_link in c3 loop
541 update amw_approved_hierarchies
542 set end_date = APPROV_TXN_DATE,
543 object_version_number = object_version_number + 1
544 ,last_update_date = sysdate
545 ,last_updated_by = G_USER_ID
546 ,last_update_login = G_LOGIN_ID
547 where organization_id = -1
548 and parent_id = defunct_link.parent_process_id
549 and child_id = defunct_link.child_process_id
550 and end_date is null;
551 end loop;
552 end if; --end of check for step = 4
553 end write_approved_hierarchy;
554
555
556 procedure prod_err_unapr_obj_ass_ex (p_process_id in number,
557 approve_option in varchar2,
558 raise_ex in varchar2,
559 p_result out nocopy varchar2,
560 p_out_mesg out nocopy varchar2 ) is
561
562 cursor process_list (pid number) is
563 select parent_child_id
564 from amw_proc_hierarchy_denorm
565 where process_id = pid
566 and up_down_ind = 'D'
567 and hierarchy_type = 'L'
568 union
569 select pid from dual;
570
571 cursor ass_risks (pid number) is
572 select risk_id from amw_risk_associations where pk1 = pid and object_type = 'PROCESS';
573
574 cursor ass_controls (pid number) is
575 select control_id from amw_control_associations where pk1 = pid and object_type = 'RISK';
576
577 l_dummy number;
578 unappr_obj_exception exception;
579 err_msg varchar2(4000);
580
581 begin
582
583 p_result := 'Y';
584 p_out_mesg := null;
585
586 if approve_option = 'B' then
587
588 for process_list_rec in process_list(p_process_id) loop
589
590 for ass_risks_rec in ass_risks(process_list_rec.parent_child_id) loop
591
592 begin
593 select 1
594 into l_dummy
595 from amw_risks_b
596 where risk_id = ass_risks_rec.risk_id
597 and approval_status = 'A';
598
599 exception
600 when too_many_rows then
601 null;
602 when no_data_found then
603 raise unappr_obj_exception;
604 end;
605
606 end loop;
607
608
609 for ass_controls_rec in ass_controls(process_list_rec.parent_child_id) loop
610
611 begin
612 select 1
613 into l_dummy
614 from amw_controls_b
615 where control_id = ass_controls_rec.control_id
616 and approval_status = 'A';
617
618 exception
619 when too_many_rows then
620 null;
621 when no_data_found then
622 raise unappr_obj_exception;
623 end;
624
625 end loop;
626
627 end loop;
628 else
629 for ass_risks_rec in ass_risks(p_process_id) loop
630
631 begin
632 select 1
633 into l_dummy
634 from amw_risks_b
635 where risk_id = ass_risks_rec.risk_id
636 and approval_status = 'A';
637
638 exception
639 when too_many_rows then
640 null;
641 when no_data_found then
642 raise unappr_obj_exception;
643 end;
644
645 end loop;
646
647
648 for ass_controls_rec in ass_controls(p_process_id) loop
649
650 begin
651 select 1
652 into l_dummy
653 from amw_controls_b
654 where control_id = ass_controls_rec.control_id
655 and approval_status = 'A';
656
657 exception
658 when too_many_rows then
659 null;
660 when no_data_found then
661 raise unappr_obj_exception;
662 end;
663
664 end loop;
665
666 end if;
667
668 exception
669
670 when unappr_obj_exception then
671 fnd_message.set_name('AMW','AMW_UNAPPRV_ASSOC');
672 err_msg := fnd_message.get;
673 if raise_ex = 'Y' then
674 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_PROC_APPROVAL_PKG',
675 p_procedure_name => 'prod_err_unapr_obj_ass_ex',
676 p_error_text => err_msg);
677 raise;
678 else
679 p_result := 'N';
680 p_out_mesg := err_msg;
681 end if;
682
683 end prod_err_unapr_obj_ass_ex;
684
685
686
687 procedure autoapprove(
688 p_process_id in number,
689 p_commit in varchar2 := FND_API.G_FALSE,
690 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
691 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
692 x_return_status out nocopy varchar2,
693 x_msg_count out nocopy number,
694 x_msg_data out nocopy varchar2 )
695
696 is
697
698 L_API_NAME CONSTANT VARCHAR2(30) := 'autoapprove';
699 l_return_status varchar2(10);
700 l_msg_count number;
701 l_msg_data varchar2(4000);
702
703 begin
704
705 x_return_status := FND_API.G_RET_STS_SUCCESS;
706 IF FND_API.to_Boolean( p_init_msg_list ) THEN
707 FND_MSG_PUB.initialize;
708 END IF;
709 IF FND_GLOBAL.User_Id IS NULL THEN
710 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713
714 sub_for_approval (p_process_id);
715 approve(p_process_id);
716
717 exception
718
719 WHEN FND_API.G_EXC_ERROR THEN
720 ROLLBACK;
721 x_return_status := FND_API.G_RET_STS_ERROR;
722 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count =>
723 x_msg_count,p_data => x_msg_data);
724
725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
726 ROLLBACK;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count =>
729 x_msg_count,p_data => x_msg_data);
730
731 WHEN OTHERS THEN
732 ROLLBACK;
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
734 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
735 THEN
736 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
737 END IF;
738 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,
739 p_data => x_msg_data);
740
741 end autoapprove;
742
743
744 -- check that all processes below are approved, else produce error
745 -- overloaded so as not to produce an expection
746 procedure check_hier_approved(p_process_id in number,
747 p_result out nocopy varchar2,
748 p_out_mesg out nocopy varchar2) is
749
750 unappr_xst_excpt exception;
751 err_msg varchar2(4000);
752 l_dummy number;
753
754 begin
755 p_result := 'Y';
756 p_out_mesg := null;
757 begin
758 select 1 --parent_child_id, a.approval_status
759 into l_dummy
760 from amw_proc_hierarchy_denorm d, amw_process a
761 where d.process_id = p_process_id
762 and up_down_ind = 'D'
763 and hierarchy_type = 'L'
764 and a.process_id = d.parent_child_id
765 and a.end_date is null
766 and a.approval_status <> 'A';
767
768 raise unappr_xst_excpt;
769 exception
770 when no_data_found then
771 null;
772
773 when too_many_rows then
774 raise unappr_xst_excpt;
775 end;
776
777 exception
778
779 when unappr_xst_excpt then
780 fnd_message.set_name('AMW','AMW_UNAPPROV_PROC_DOWN');
781 err_msg := fnd_message.get;
782 p_result := 'N';
783 p_out_mesg := err_msg;
784
785 end check_hier_approved;
786
787 --port
788 /*
789 Produce error if:
790 1. approval option says "Don't approve this unless everything below is approved". There's at least one process below that is NOT approved.
791 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.
792 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.
793 4. the process is non-standard, and the standard variation does not have an approved revision.
794 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.
795 */
796 procedure check_approval_subm_eligib(
797 p_process_id in number,
798 p_result out nocopy varchar2,
799 p_out_mesg out nocopy varchar2,
800 p_commit in varchar2 := FND_API.G_FALSE,
801 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
802 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
803 x_return_status out nocopy varchar2,
804 x_msg_count out nocopy number,
805 x_msg_data out nocopy varchar2 )
806
807 is
808
809 L_API_NAME CONSTANT VARCHAR2(30) := 'check_approval_subm_eligib';
810 l_return_status varchar2(10);
811 l_msg_count number;
812 l_msg_data varchar2(4000);
813 approv_choice varchar2(1);
814 std_process varchar2(1);
815 st_var_pid number;
816
817 begin
818
819 x_return_status := FND_API.G_RET_STS_SUCCESS;
820 IF FND_API.to_Boolean( p_init_msg_list ) THEN
821 FND_MSG_PUB.initialize;
822 END IF;
823 IF FND_GLOBAL.User_Id IS NULL THEN
824 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827
828 p_result := 'Y';
829 p_out_mesg := null;
830
831 approv_choice := amw_utility_pvt.get_parameter(-1, 'PROCESS_APPROVAL_OPTION');
832
833 if (approv_choice = 'C') then
834 check_hier_approved(p_process_id, p_result, p_out_mesg);
835 if p_result = 'N' then
836 return;
837 end if;
838 end if;
839
840 prod_err_unapr_obj_ass_ex (p_process_id, approv_choice, 'N', p_result, p_out_mesg);
841 if p_result = 'N' then
842 return;
843 end if;
844
845 prod_err_unappr_nsvar (p_process_id, approv_choice, p_result, p_out_mesg);
846 if p_result = 'N' then
847 return;
848 end if;
849
850 prod_err_modified_nschildlist (p_process_id, approv_choice, p_result, p_out_mesg);
851 if p_result = 'N' then
852 return;
853 end if;
854
855 exception
856
857 WHEN FND_API.G_EXC_ERROR THEN
858 ROLLBACK;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
861
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 ROLLBACK;
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
865 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
866
867 WHEN OTHERS THEN
868 ROLLBACK;
869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
870 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
871 THEN
872 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
873 END IF;
874 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
875
876 end check_approval_subm_eligib;
877
878
879
880 procedure prod_err_unappr_nsvar(p_process_id in number,
881 approve_option in varchar2,
882 p_result out nocopy varchar2,
883 p_out_mesg out nocopy varchar2) is
884
885 cursor process_list (pid number) is
886 select parent_child_id
887 from amw_proc_hierarchy_denorm
888 where process_id = pid
889 and up_down_ind = 'D'
890 and hierarchy_type = 'L'
891 union
892 select pid from dual;
893
894 std_process varchar2(1);
895 st_var_pid number;
896 l_dummy number;
897
898 begin
899 -- standard variation treatment
900 -- I could probably have made good use for cursor variables here
901
902 if approve_option = 'B' then
903 for process_list_rec in process_list(p_process_id) loop
904
905 begin
906
907 select a.standard_process_flag, b.process_id
908 into std_process, st_var_pid
909 from amw_process a, amw_process b
910 where a.process_id = process_list_rec.parent_child_id
911 and a.end_date is null
912 and b.process_rev_id = a.standard_variation;
913
914 exception
915 when no_data_found then --process is standard/non-std but no var defined
916 std_process := 'Y';
917 end;
918
919 if std_process = 'N' then -- if process is non-standard
920 begin
921 select 1
922 into l_dummy
923 from amw_process
924 where process_id = st_var_pid
925 and approval_status = 'A';
926
927 exception
928 when too_many_rows then
929 null;
930
931 when no_data_found then
932 fnd_message.set_name('AMW','AMW_NS_VAR_UNAPPROVED');
933 p_out_mesg := fnd_message.get;
934 p_result := 'N';
935 return;
936 end;
937 end if; -- if process is non-standard
938
939 end loop;
940 else
941 begin
942 select a.standard_process_flag, b.process_id
943 into std_process, st_var_pid
944 from amw_process a, amw_process b
945 where a.process_id = p_process_id
946 and a.end_date is null
947 and b.process_rev_id = a.standard_variation;
948 exception
949 when no_data_found then --process is standard/non-std but no var defined
950 std_process := 'Y';
951 end;
952
953 if std_process = 'N' then -- if process is non-standard
954
955 begin
956 select 1
957 into l_dummy
958 from amw_process
959 where process_id = st_var_pid
960 and approval_status = 'A';
961
962 exception
963 when too_many_rows then
964 null;
965
966 when no_data_found then
967 fnd_message.set_name('AMW','AMW_NS_VAR_UNAPPROVED');
968 p_out_mesg := fnd_message.get;
969 p_result := 'N';
970 return;
971 end;
972
973 end if; -- if process is non-standard
974
975 end if;
976
977 end prod_err_unappr_nsvar;
978
979
980 procedure prod_err_modified_nschildlist(p_process_id in number,
981 approve_option in varchar2,
982 p_result out nocopy varchar2,
983 p_out_mesg out nocopy varchar2) is
984
985 cursor process_list (pid number) is
986 select parent_child_id
987 from amw_proc_hierarchy_denorm
988 where process_id = pid
989 and up_down_ind = 'D'
990 and hierarchy_type = 'L'
991 union
992 select pid from dual;
993
994 std_process varchar2(1);
995 l_std_variation amw_process.STANDARD_VARIATION%TYPE;
996 l_dummy number;
997
998 begin
999 -- standard variation treatment
1000 -- I could probably have made good use for cursor variables here
1001
1002 if approve_option = 'B' then
1003 for process_list_rec in process_list(p_process_id) loop
1004
1005 select standard_process_flag,standard_variation
1006 into std_process,l_std_variation
1007 from amw_process
1008 where process_id = process_list_rec.parent_child_id
1009 and end_date is null;
1010
1011 if std_process = 'N' and l_std_variation is not null then
1012
1013 begin
1014 select parent_child_id
1015 into l_dummy
1016 from amw_proc_hierarchy_denorm
1017 where process_id = process_list_rec.parent_child_id
1018 and up_down_ind = 'D'
1019 and hierarchy_type = 'L'
1020 and parent_child_id not in
1021 (select NON_STD_CHILD_ID
1022 from AMW_NONSTANDARD_VARIATIONS_B
1023 where NON_STD_PROCESS_ID = process_list_rec.parent_child_id
1024 and NON_STD_PROCESS_REV_NUM = (select revision_number
1025 from amw_process
1026 where process_id = process_list_rec.parent_child_id
1027 and end_date is null)
1028 and END_DATE is null);
1029
1030 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1031 p_out_mesg := fnd_message.get;
1032 p_result := 'N';
1033 return;
1034
1035 exception
1036 when no_data_found then
1037 null;
1038
1039 when too_many_rows then
1040 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1041 p_out_mesg := fnd_message.get;
1042 p_result := 'N';
1043 return;
1044 end;
1045
1046 begin
1047 select NON_STD_CHILD_ID
1048 into l_dummy
1049 from AMW_NONSTANDARD_VARIATIONS_B
1050 where NON_STD_PROCESS_ID = process_list_rec.parent_child_id
1051 and NON_STD_PROCESS_REV_NUM = (select revision_number
1052 from amw_process
1053 where process_id = process_list_rec.parent_child_id
1054 and end_date is null)
1055 and END_DATE is null
1056 and NON_STD_CHILD_ID not in
1057 (select parent_child_id
1058 from amw_proc_hierarchy_denorm
1059 where process_id = process_list_rec.parent_child_id
1060 and up_down_ind = 'D'
1061 and hierarchy_type = 'L');
1062
1063 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1064 p_out_mesg := fnd_message.get;
1065 p_result := 'N';
1066 return;
1067
1068 exception
1069 when no_data_found then
1070 null;
1071
1072 when too_many_rows then
1073 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1074 p_out_mesg := fnd_message.get;
1075 p_result := 'N';
1076 return;
1077 end;
1078
1079 end if; -- if process is non-standard
1080
1081 end loop;
1082 else
1083 select standard_process_flag,standard_variation
1084 into std_process,l_std_variation
1085 from amw_process
1086 where process_id = p_process_id
1087 and end_date is null;
1088
1089 if std_process = 'N' and l_std_variation is not null then
1090
1091 begin
1092 select parent_child_id
1093 into l_dummy
1094 from amw_proc_hierarchy_denorm
1095 where process_id = p_process_id
1096 and up_down_ind = 'D'
1097 and hierarchy_type = 'L'
1098 and parent_child_id not in
1099 (select NON_STD_CHILD_ID
1100 from AMW_NONSTANDARD_VARIATIONS_B
1101 where NON_STD_PROCESS_ID = p_process_id
1102 and NON_STD_PROCESS_REV_NUM = (select revision_number
1103 from amw_process
1104 where process_id = p_process_id
1105 and end_date is null)
1106 and END_DATE is null);
1107
1108 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1109 p_out_mesg := fnd_message.get;
1110 p_result := 'N';
1111 return;
1112
1113 exception
1114 when no_data_found then
1115 null;
1116
1117 when too_many_rows then
1118 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1119 p_out_mesg := fnd_message.get;
1120 p_result := 'N';
1121 return;
1122 end;
1123
1124 begin
1125 select NON_STD_CHILD_ID
1126 into l_dummy
1127 from AMW_NONSTANDARD_VARIATIONS_B
1128 where NON_STD_PROCESS_ID = p_process_id
1129 and NON_STD_PROCESS_REV_NUM = (select revision_number
1130 from amw_process
1131 where process_id = p_process_id
1132 and end_date is null)
1133 and END_DATE is null
1134 and NON_STD_CHILD_ID not in
1135 (select parent_child_id
1136 from amw_proc_hierarchy_denorm
1137 where process_id = p_process_id
1138 and up_down_ind = 'D'
1139 and hierarchy_type = 'L');
1140
1141 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1142 p_out_mesg := fnd_message.get;
1143 p_result := 'N';
1144 return;
1145
1146 exception
1147 when no_data_found then
1148 null;
1149
1150 when too_many_rows then
1151 fnd_message.set_name('AMW','AMW_NS_CHILDLIST_DIFF');
1152 p_out_mesg := fnd_message.get;
1153 p_result := 'N';
1154 return;
1155 end;
1156
1157 end if; -- if process is non-standard
1158
1159 end if; -- approval option B
1160
1161 end prod_err_modified_nschildlist;
1162
1163 ---05.11.2005 npanandi: added below procedure for handling
1164 ---webadi approvals
1165 procedure webadi_approve(
1166 p_process_id in number
1167 ,p_approv_choice in varchar2)
1168 is
1169 rev_num number;
1170 approv_choice varchar2(10);
1171 curr_app_status varchar2(10);
1172
1173 dummy1 varchar2(1);
1174 dummy2 varchar2(1000);
1175
1176 cursor c1 (p_process_id number) is
1177 select parent_child_id
1178 from amw_proc_hierarchy_denorm
1179 where process_id = p_process_id
1180 and up_down_ind = 'D'
1181 and hierarchy_type = 'L';
1182 c1_rec c1%rowtype;
1183 begin
1184 -- error out if at least one risk or control associated with this process
1185 -- does not have any approved revision.
1186 prod_err_unapr_obj_ass_ex (p_process_id,p_approv_choice,'Y',dummy1,dummy2);
1187
1188
1189 APPROV_TXN_DATE := sysdate;
1190 -- check if the process is already approved, in that case, return
1191 select approval_status
1192 into curr_app_status
1193 from amw_process
1194 where process_id = p_process_id
1195 and end_date is null;
1196
1197 if curr_app_status = 'A' then
1198 return;
1199 end if;
1200
1201 /**
1202 delete from amw_process_locks
1203 where organization_id = -1
1204 and locking_process_id = p_process_id;
1205 **/
1206
1207 update amw_process
1208 set approval_status = 'A',
1209 approval_date = APPROV_TXN_DATE
1210 where process_id = p_process_id
1211 and end_date is null
1212 returning revision_number into rev_num;
1213
1214 if rev_num > 1 then
1215 update amw_process
1216 set approval_end_date = APPROV_TXN_DATE
1217 where process_id = p_process_id
1218 and revision_number = (rev_num-1);
1219 end if;
1220
1221 --kosriniv ..Need to update the org count...
1222 AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => p_process_id);
1223
1224 approve_associations(p_process_id);
1225 ---05.11.2005 npanandi: per Amit, not needed here, since we will be calling
1226 ---write_approved_hierarchy with step # = 4
1227 ---write_approved_hierarchy(p_process_id, 2);
1228
1229
1230 if (p_approv_choice = 'B') then
1231 for c1_rec in c1(p_process_id) loop
1232 exit when c1%notfound;
1233 update amw_process
1234 set approval_status = 'A',
1235 approval_date = APPROV_TXN_DATE
1236 where process_id = c1_rec.parent_child_id
1237 and end_date is null
1238 returning revision_number into rev_num;
1239
1240 if rev_num > 1 then
1241 update amw_process
1242 set approval_end_date = APPROV_TXN_DATE
1243 where process_id = c1_rec.parent_child_id
1244 and revision_number = (rev_num-1);
1245 end if;
1246
1247 --kosriniv ..Need to update the org count...
1248 AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => c1_rec.parent_child_id);
1249
1250 approve_associations(c1_rec.parent_child_id);
1251 ---05.11.2005 npanandi: per Amit, not needed here, since we will be calling
1252 ---write_approved_hierarchy with step # = 4
1253 ---write_approved_hierarchy(p_process_id, 2);
1254 end loop;
1255 end if;
1256
1257
1258 ---05.11.2005 npanandi: calling this once from the main API, hence
1259 ---commenting out here
1260 /**
1261 write_approved_hierarchy(p_process_id, 1);
1262 amw_rl_hierarchy_pkg.update_approved_denorm(-1);
1263 amw_rl_hierarchy_pkg.update_appr_control_counts;
1264 amw_rl_hierarchy_pkg.update_appr_risk_counts;
1265 **/
1266 end webadi_approve;
1267
1268
1269 end AMW_PROC_APPROVAL_PKG;
[Home] [Help]