[Home] [Help]
PACKAGE BODY: APPS.ASO_APR_RESOURCE_HANDLER
Source
1 PACKAGE BODY aso_apr_resource_handler AS
2 /* $Header: asoiarhb.pls 120.1 2005/06/29 12:32:21 appldev ship $ */
3 g_last_approver_group_id NUMBER := 0;
4 g_approval_type_id INTEGER := 0;
5
6 FUNCTION get_manager (
7 p_group_id IN NUMBER
8 )
9 RETURN NUMBER IS
10
11 /*
12 Logic has been commented OUT as per bug 3405904
13
14
15
16 l_manager_resource_id NUMBER;
17 l_unique_role_code VARCHAR2 (240);
18
19 CURSOR get_manager (
20 c_group_id NUMBER
21 ) IS
22 SELECT parent_resource_id
23 FROM jtf_rs_rep_managers mgr, jtf_rs_group_usages u
24 WHERE u.usage = 'SALES'
25 AND u.GROUP_ID = mgr.GROUP_ID
26 AND resource_id = parent_resource_id
27 AND par_role_relate_id = child_role_relate_id
28 AND SYSDATE BETWEEN start_date_active
29 AND NVL (
30 end_date_active,
31 SYSDATE
32 )
33 AND mgr.GROUP_ID = c_group_id
34 AND hierarchy_type IN ('MGR_TO_MGR');
35
36 CURSOR get_manager_with_role (
37 c_group_id NUMBER,
38 c_role_code VARCHAR2
39 ) IS
40 SELECT mgr.parent_resource_id
41 FROM jtf_rs_rep_managers mgr,
42 jtf_rs_group_usages u,
43 jtf_rs_role_relations rel,
44 jtf_rs_roles_b rol
45 WHERE u.usage = 'SALES'
46 AND u.GROUP_ID = mgr.GROUP_ID
47 AND mgr.resource_id = mgr.parent_resource_id
48 AND par_role_relate_id = child_role_relate_id
49 AND SYSDATE BETWEEN mgr.start_date_active
50 AND NVL (
51 mgr.end_date_active,
52 SYSDATE
53 )
54 AND mgr.GROUP_ID = c_group_id
55 AND hierarchy_type IN ('MGR_TO_MGR')
56 AND rol.role_id = rel.role_id
57 AND rol.role_code = c_role_code
58 AND rel.role_resource_type = 'RS_INDIVIDUAL'
59 AND rel.role_resource_id = mgr.parent_resource_id
60 AND SYSDATE BETWEEN rel.start_date_active
61 AND NVL (
62 rel.end_date_active,
63 SYSDATE
64 )
65 AND delete_flag <> 'Y';
66
67 */
68
69 BEGIN
70 NULL;
71
72 /*
73 Logic has been commented OUT as per bug 3405904
74
75
76
77 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
78 aso_debug_pub.ADD (
79 'Start of Get_Manager',
80 1,
81 'N'
82 );
83 END IF;
84
85 FOR i IN get_manager (
86 p_group_id
87 )
88 LOOP
89 l_manager_resource_id := i.parent_resource_id;
90
91 -- If there are more than one managers in the group
92 IF ((get_manager%ROWCOUNT) > 1)
93 THEN
94 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
95 aso_debug_pub.ADD (
96 'Get_Manager: More than one manager in group : ' || p_group_id,
97 1,
98 'N'
99 );
100 END IF;
101 -- get the unique role code used to identify the correct manager
102 l_unique_role_code :=
103 ame_engine.getattributevaluebyname (
104 attributenamein => 'UNIQUE_APPROVER_IDENTIFICATION_ROLE_CODE'
105 );
106 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
107 aso_debug_pub.ADD (
108 'Get_Manager: Unique Role Code : ' || l_unique_role_code,
109 1,
110 'N'
111 );
112 END IF;
113
114 IF l_unique_role_code IS NOT NULL
115 THEN
116 -- get the manger with that role code
117 OPEN get_manager_with_role (
118 p_group_id,
119 l_unique_role_code
120 );
121 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
122 aso_debug_pub.ADD (
123 'Get_Manager: Trying to get manager with the unique role code',
124 1,
125 'N'
126 );
127 END IF;
128 FETCH get_manager_with_role INTO l_manager_resource_id;
129 CLOSE get_manager_with_role;
130 END IF;
131
132 EXIT;
133 END IF;
134 END LOOP;
135
136 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
137 aso_debug_pub.ADD (
138 'Get_Manager: Manager Resource ID : ' || l_manager_resource_id,
139 1,
140 'N'
141 );
142 aso_debug_pub.ADD (
143 'End of Get_Manager',
144 1,
145 'N'
146 );
147 END IF;
148 RETURN l_manager_resource_id;
149
150 */
151 RETURN NULL;
152 END get_manager;
153
154 FUNCTION check_approver_exists (
155 p_approver_user_id IN NUMBER
156 )
157 RETURN BOOLEAN IS
158 BEGIN
159
160 null;
161
162 /*
163 Logic has been commented OUT as per bug 3405904
164
165
166
167
168 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
169 aso_debug_pub.ADD (
170 'Start of check_approver_exists',
171 1,
172 'N'
173 );
174 END IF;
175
176 FOR i IN 1 .. ame_engine.tempapproverlist.COUNT
177 LOOP
178 IF (ame_engine.tempapproverlist (
179 i
180 ).user_id = p_approver_user_id
181 )
182 THEN
183 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
184 aso_debug_pub.ADD (
185 'check_approver_exists : Approver Exists in list',
186 1,
187 'N'
188 );
189 END IF;
190 RETURN (TRUE );
191 END IF;
192 END LOOP;
193
194 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
195 aso_debug_pub.ADD (
196 'check_approver_exists : Approver does not Exists in list',
197 1,
198 'N'
199 );
200 aso_debug_pub.ADD (
201 'End of check_approver_exists',
202 1,
203 'N'
204 );
205 END IF;
206 RETURN (FALSE );
207 */
208 RETURN NULL;
209 END check_approver_exists;
210
211 PROCEDURE getfirstapprover (
212 approvaltypeidin IN INTEGER,
213 parametersin IN VARCHAR2,
214 /* parametersIn not used, IN this case */
215 sourceruleidlistin IN VARCHAR2,
216 firstapproverout OUT NOCOPY /* file.sql.39 change */ VARCHAR2
217 ) AS
218
219 /*
220 has been commented OUT as per bug 3405904
221
222
223 approver ame_util.approverrecord;
224 errorcode INTEGER;
225 errormessage VARCHAR2 (2000);
226 nvl_user_or_groupid EXCEPTION;
227 nvlusrperidexception EXCEPTION;
228 requester_res_id_not_found EXCEPTION;
229 no_parent_group_found EXCEPTION;
230 unending_loop EXCEPTION;
231 l_user_id NUMBER;
232 l_resource_id NUMBER;
233 l_manager_resource_id NUMBER := 0;
234 l_resource_name VARCHAR2 (240);
235 l_group_name VARCHAR2 (240);
236 l_user_name VARCHAR2 (240);
237 l_parent_group_id NUMBER;
238 l_loop_counter NUMBER := 0;
239
240 CURSOR get_user_resource_id (
241 c_user_id NUMBER
242 ) IS
243 SELECT resource_id
244 FROM jtf_rs_resource_extns
245 WHERE user_id = c_user_id
246 AND SYSDATE BETWEEN start_date_active
247 AND NVL (
248 end_date_active,
249 SYSDATE
250 );
251
252 CURSOR get_user_id (
253 c_manager_resource_id NUMBER
254 ) IS
255 SELECT user_id
256 FROM jtf_rs_resource_extns
257 WHERE resource_id = c_manager_resource_id
258 AND SYSDATE BETWEEN start_date_active
259 AND NVL (
260 end_date_active,
261 SYSDATE
262 );
263
264 CURSOR get_person_id (
265 c_manager_resource_id NUMBER
266 ) IS
267 SELECT source_id
268 FROM jtf_rs_resource_extns
269 WHERE resource_id = c_manager_resource_id
270 AND CATEGORY = 'EMPLOYEE'
271 AND SYSDATE BETWEEN start_date_active
272 AND NVL (
273 end_date_active,
274 SYSDATE
275 );
276
277 CURSOR get_resource_name (
278 c_resource_id NUMBER
279 ) IS
280 SELECT resource_name
281 FROM jtf_rs_resource_extns_vl
282 WHERE resource_id = c_resource_id
283 AND SYSDATE BETWEEN start_date_active
284 AND NVL (
285 end_date_active,
286 SYSDATE
287 );
288
289 CURSOR get_group_name (
290 c_group_id NUMBER
291 ) IS
292 SELECT group_name
293 FROM jtf_rs_groups_tl
294 WHERE GROUP_ID = c_group_id
295 AND LANGUAGE = USERENV (
296 'LANG'
297 );
298
299 CURSOR get_user_name (
300 c_user_id NUMBER
301 ) IS
302 SELECT user_name
303 FROM fnd_user
304 WHERE user_id = c_user_id
305 AND SYSDATE BETWEEN start_date AND NVL (
306 end_date,
307 SYSDATE
308 );
309
310 CURSOR get_parent_group_id (
311 c_group_id NUMBER
312 ) IS
313 SELECT grp.parent_group_id
314 FROM jtf_rs_groups_denorm grp, jtf_rs_group_usages u
315 WHERE u.usage = 'SALES'
316 AND u.GROUP_ID = grp.GROUP_ID
317 AND grp.GROUP_ID = c_group_id
318 AND SYSDATE BETWEEN start_date_active
319 AND NVL (
320 end_date_active,
321 SYSDATE
322 )
323 AND grp.immediate_parent_flag = 'Y';
324
325 */
326
327
328 BEGIN
329 NULL;
330
331 /*
332 Logic has been commented OUT as per bug 3405904
333
334
335
336
337 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
338 aso_debug_pub.ADD (
339 'Begin getFirstApprover PROCEDURE ',
340 1,
341 'N'
342 );
343 END IF;
344 -- get the user id AND group id FROM the atrributes
345
346 l_user_id :=
347 TO_NUMBER (
348 ame_engine.getattributevaluebyname (
349 attributenamein => ame_util.transactionrequserattribute
350 )
351 );
352 g_last_approver_group_id :=
353 TO_NUMBER (
354 ame_engine.getattributevaluebyname (
355 attributenamein => 'TRANSACTION_REQUESTOR_GROUP_ID'
356 )
357 );
358 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
359 aso_debug_pub.ADD (
360 'getFirstApprover : Requester UserID IS : ' || l_user_id,
361 1,
362 'N'
363 );
364 aso_debug_pub.ADD (
365 'getFirstApprover : Requester GroupID IS : ' || g_last_approver_group_id,
366 1,
367 'N'
368 );
369 END IF;
370
371 -- Check IF the values fetched FROM attributes exists or not
372 IF ((l_user_id IS NULL)
373 OR (g_last_approver_group_id IS NULL)
374 )
375 THEN
376 RAISE nvl_user_or_groupid;
377 END IF;
378
379 -- get the resource id based upon the user id
380 OPEN get_user_resource_id (
381 l_user_id
382 );
383 FETCH get_user_resource_id INTO l_resource_id;
384 CLOSE get_user_resource_id;
385 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
386 aso_debug_pub.ADD (
387 'getFirstApprover : Requester ResourceID IS : ' || l_resource_id,
388 1,
389 'N'
390 );
391 END IF;
392
393 IF l_resource_id IS NULL
394 THEN
395 RAISE requester_res_id_not_found;
396 END IF;
397
398 -- Initialize the approver record
399 approver.person_id := NULL;
400 approver.user_id := NULL;
401 approver.first_name := NULL;
402 approver.last_name := NULL;
403 approver.authority := ame_util.authorityapprover;
404 approver.api_insertion := ame_util.oamgenerated;
405 approver.approval_status := ame_util.exceptionstatus;
406 -- approver.approval_type_id := G_APPROVAL_TYPE_ID;
407 approver.approval_type_id := 0;
408 approver.group_or_chain_id := 1;
409 approver.occurrence := NULL;
410 approver.SOURCE := NULL;
411 -- call the function to get the manager
412 l_manager_resource_id :=
413 aso_apr_resource_handler.get_manager (
414 g_last_approver_group_id
415 );
416 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
417 aso_debug_pub.ADD (
418 'getFirstApprover : Manager ResourceID IS : ' || l_manager_resource_id,
419 1,
420 'N'
421 );
422 END IF;
423
424 IF (l_resource_id = l_manager_resource_id)
425 OR (l_manager_resource_id IS NULL)
426 THEN
427 WHILE TRUE
428 LOOP
429 -- this means that the resource IS a manager
430 -- get the manager of the parent group for first approver
431
432 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
433 aso_debug_pub.ADD (
434 'getFirstApprover : Resource IS a manager',
435 1,
436 'N'
437 );
438 END IF;
439 -- get the parent group
440 OPEN get_parent_group_id (
441 g_last_approver_group_id
442 );
443 FETCH get_parent_group_id INTO l_parent_group_id;
444
445 IF (get_parent_group_id%ROWCOUNT = 0)
446 THEN -- No Parent Group Found
447 CLOSE get_parent_group_id;
448 RAISE no_parent_group_found;
449 END IF;
450
451 CLOSE get_parent_group_id;
452 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
453 aso_debug_pub.ADD (
454 'getFirstApprover : Parent GroupId IS : ' || l_parent_group_id,
455 1,
459 -- Store the group id in a global variable
456 'N'
457 );
458 END IF;
460 g_last_approver_group_id := l_parent_group_id;
461 -- call the function to get the manager
462 l_manager_resource_id :=
463 aso_apr_resource_handler.get_manager (
464 l_parent_group_id
465 );
466 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
467 aso_debug_pub.ADD (
468 'getFirstApprover : Manager ResourceID IS : ' || l_manager_resource_id,
469 1,
470 'N'
471 );
472 END IF;
473
474 IF (l_resource_id <> l_manager_resource_id)
475 AND (l_manager_resource_id IS NOT NULL)
476 THEN
477 EXIT;
478 END IF;
479
480 l_loop_counter := l_loop_counter + 1;
481
482 IF (l_loop_counter > 20)
483 THEN
484 RAISE unending_loop;
485 END IF;
486 END LOOP;
487 END IF;
488
489 -- get the user id FROM the resource id
490 OPEN get_user_id (
491 l_manager_resource_id
492 );
493 FETCH get_user_id INTO approver.user_id;
494 CLOSE get_user_id;
495 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
496 aso_debug_pub.ADD (
497 'getFirstApprover : Manager UserID IS : ' || approver.user_id,
498 1,
499 'N'
500 );
501 END IF;
502 OPEN get_person_id (
503 l_manager_resource_id
504 );
505 FETCH get_person_id INTO approver.person_id;
506 CLOSE get_person_id;
507
508 -- IF both are null, we can not proceed
509 IF (approver.person_id IS NULL
510 AND approver.user_id IS NULL
511 )
512 THEN
513 RAISE nvlusrperidexception;
514 END IF;
515
516 -- Set the approval status to null for the approver record
517 -- approver.approval_status := null;
518 approver.occurrence :=
519 ame_engine.getnextapproveroccurrence (
520 approverin => approver,
521 excludeapproverindexin => NULL
522 );
523 approver.SOURCE := sourceruleidlistin;
524 approver.approval_status :=
525 ame_engine.getoldapproverstatus (
526 approverin => approver
527 );
528 -- convert Approver Record FROM PL/SQL record format to comma seperated format
529
530 firstapproverout :=
531 ame_util.serializeapproverrecord (
532 approverrecordin => approver
533 );
534 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
535 aso_debug_pub.ADD (
536 'END getFirstApprover PROCEDURE ',
537 1,
538 'N'
539 );
540 END IF;
541
542
543
544 EXCEPTION
545
546 WHEN nvl_user_or_groupid
547 THEN
548 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
549 aso_debug_pub.ADD (
550 'getFirstApprover: Exception nvl_user_or_groupid raised in getFirstApprover procedure ',
551 1,
552 'N'
553 );
554 aso_debug_pub.ADD (
555 'getFirstApprover: user_id : ' || l_user_id,
556 1,
557 'N'
558 );
559 aso_debug_pub.ADD (
560 'getFirstApprover: group_id : ' || g_last_approver_group_id,
561 1,
562 'N'
563 );
564 END IF;
565 errorcode := -20001;
566 errormessage :=
567 'This transaction''s requestor does not have a user id or group id';
568 ame_util.runtimeexception (
569 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
570 routinenamein => 'getFirstApprover',
571 exceptionnumberin => errorcode,
572 exceptionstringin => errormessage,
573 transactionidin => ame_engine.temptransactionid,
574 applicationidin => ame_engine.tempameapplicationid,
575 localerrorin => FALSE
576 );
577 firstapproverout :=
578 ame_util.serializeapproverrecord (
579 approverrecordin => approver
580 );
581 raise_application_error (
582 errorcode,
583 errormessage
584 );
585 WHEN nvlusrperidexception
586 THEN
587 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
588 aso_debug_pub.ADD (
589 'getFirstApprover: Exception nvlusrperIdException raised in getFirstApprover procedure ',
590 1,
591 'N'
592 );
593 END IF;
594 OPEN get_resource_name (
595 l_manager_resource_id
596 );
597 FETCH get_resource_name INTO l_resource_name;
598 CLOSE get_resource_name;
599 errorcode := -20002;
600 errormessage := 'The resource: '
601 || l_resource_name
605 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
602 || ' does not have a user id AND person id.'
603 || ' Unable to traverse heirarchy for this resource ';
604 ame_util.runtimeexception (
606 routinenamein => 'getFirstApprover',
607 exceptionnumberin => errorcode,
608 exceptionstringin => errormessage,
609 transactionidin => ame_engine.temptransactionid,
610 applicationidin => ame_engine.tempameapplicationid,
611 localerrorin => FALSE
612 );
613 firstapproverout :=
614 ame_util.serializeapproverrecord (
615 approverrecordin => approver
616 );
617 raise_application_error (
618 errorcode,
619 errormessage
620 );
621 WHEN requester_res_id_not_found
622 THEN
623 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
624 aso_debug_pub.ADD (
625 'getFirstApprover : Exception requester_res_id_not_found raised in getFirstApprover procedure ',
626 1,
627 'N'
628 );
629 END IF;
630 OPEN get_user_name (
631 l_user_id
632 );
633 FETCH get_user_name INTO l_user_name;
634 CLOSE get_user_name;
635 errorcode := -20003;
636 errormessage := 'The transaction''s requester ( username = '
637 || l_user_name
638 || ' ) does not have a resource id.'
639 || ' Unable to traverse heirarchy for this resource ';
640 ame_util.runtimeexception (
641 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
642 routinenamein => 'getFirstApprover',
643 exceptionnumberin => errorcode,
644 exceptionstringin => errormessage,
645 transactionidin => ame_engine.temptransactionid,
646 applicationidin => ame_engine.tempameapplicationid,
647 localerrorin => FALSE
648 );
649 firstapproverout :=
650 ame_util.serializeapproverrecord (
651 approverrecordin => approver
652 );
653 raise_application_error (
654 errorcode,
655 errormessage
656 );
657 WHEN no_parent_group_found
658 THEN
659 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
660 aso_debug_pub.ADD (
661 'getFirstApprover : Exception no_parent_group_found raised in getFirstApprover procedure ',
662 1,
663 'N'
664 );
665 END IF;
666 OPEN get_group_name (
667 g_last_approver_group_id
668 );
669 FETCH get_group_name INTO l_group_name;
670 CLOSE get_group_name;
671 errorcode := -20004;
672 errormessage := 'Cannot find parent group for group: '
673 || l_group_name;
674 ame_util.runtimeexception (
675 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
676 routinenamein => 'getFirstApprover',
677 exceptionnumberin => errorcode,
678 exceptionstringin => errormessage,
679 transactionidin => ame_engine.temptransactionid,
680 applicationidin => ame_engine.tempameapplicationid,
681 localerrorin => FALSE
682 );
683 firstapproverout :=
684 ame_util.serializeapproverrecord (
685 approverrecordin => approver
686 );
687 raise_application_error (
688 errorcode,
689 errormessage
690 );
691 WHEN unending_loop
692 THEN
693 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
694 aso_debug_pub.ADD (
695 'getFirstApprover : Exception Unending_Loop raised in getFirstApprover procedure ',
696 1,
697 'N'
698 );
699 END IF;
700 errorcode := -20005;
701 errormessage := 'Please check the group heirarchy';
702 ame_util.runtimeexception (
703 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
704 routinenamein => 'getFirstApprover',
705 exceptionnumberin => SQLCODE,
706 exceptionstringin => SQLERRM,
707 transactionidin => ame_engine.temptransactionid,
708 applicationidin => ame_engine.tempameapplicationid,
709 localerrorin => FALSE
710 );
711 firstapproverout :=
712 ame_util.serializeapproverrecord (
713 approverrecordin => approver
714 );
715 raise_application_error (
716 errorcode,
717 errormessage
718 );
719 WHEN OTHERS
720 THEN
721 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
722 aso_debug_pub.ADD (
723 'getFirstApprover : Exception WHEN others raised in getFirstApprover procedure ',
724 1,
725 'N'
729 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
726 );
727 END IF;
728 ame_util.runtimeexception (
730 routinenamein => 'getFirstApprover',
731 exceptionnumberin => SQLCODE,
732 exceptionstringin => SQLERRM,
733 transactionidin => ame_engine.temptransactionid,
734 applicationidin => ame_engine.tempameapplicationid,
735 localerrorin => FALSE
736 );
737 firstapproverout :=
738 ame_util.serializeapproverrecord (
739 approverrecordin => approver
740 );
741 raise_application_error (
742 errorcode,
743 errormessage
744 );
745
746 */
747
748 END getfirstapprover;
749
750 PROCEDURE getnextapprover (
751 approvaltypeidin IN INTEGER,
752 approverin IN VARCHAR2,
753 parametersin IN VARCHAR2,
754 /* not used IN this case, but a required argument */
755 sourceruleidlistin IN VARCHAR2,
756 nextapproverout OUT NOCOPY /* file.sql.39 change */ VARCHAR2
757 ) IS
758
759 /*
760 Has been commented OUT as per bug 3405904
761
762
763
764 nextapprover ame_util.approverrecord;
765 errorcode INTEGER;
766 errormessage VARCHAR2 (2000);
767 nvlusrperidexception EXCEPTION;
768 no_parent_group_found EXCEPTION;
769 unending_loop EXCEPTION;
770 l_user_id NUMBER;
771 l_resource_id NUMBER;
772 l_manager_resource_id NUMBER;
773 l_parent_group_id NUMBER;
774 l_resource_name VARCHAR2 (240);
775 l_group_name VARCHAR2 (240);
776 l_user_name VARCHAR2 (240);
777 l_loop_counter NUMBER := 0;
778
779 CURSOR get_user_resource_id (
780 c_user_id NUMBER
781 ) IS
782 SELECT resource_id
783 FROM jtf_rs_resource_extns
784 WHERE user_id = c_user_id
785 AND SYSDATE BETWEEN start_date_active
786 AND NVL (
787 end_date_active,
788 SYSDATE
789 );
790
791 CURSOR get_person_resource_id (
792 c_person_id NUMBER
793 ) IS
794 SELECT resource_id
795 FROM jtf_rs_resource_extns
796 WHERE source_id = c_person_id
797 AND CATEGORY = 'EMPLOYEE'
798 AND SYSDATE BETWEEN start_date_active
799 AND NVL (
800 end_date_active,
801 SYSDATE
802 );
803
804 CURSOR get_user_id (
805 c_manager_resource_id NUMBER
806 ) IS
807 SELECT user_id
808 FROM jtf_rs_resource_extns
809 WHERE resource_id = c_manager_resource_id
810 AND SYSDATE BETWEEN start_date_active
811 AND NVL (
812 end_date_active,
813 SYSDATE
814 );
815
816 CURSOR get_person_id (
817 c_manager_resource_id NUMBER
818 ) IS
819 SELECT source_id
820 FROM jtf_rs_resource_extns
821 WHERE resource_id = c_manager_resource_id
822 AND CATEGORY = 'EMPLOYEE'
823 AND SYSDATE BETWEEN start_date_active
824 AND NVL (
825 end_date_active,
826 SYSDATE
827 );
828
829 CURSOR get_parent_group_id (
830 c_group_id NUMBER
831 ) IS
832 SELECT grp.parent_group_id
833 FROM jtf_rs_groups_denorm grp, jtf_rs_group_usages u
834 WHERE u.usage = 'SALES'
835 AND u.GROUP_ID = grp.GROUP_ID
836 AND grp.GROUP_ID = c_group_id
837 AND SYSDATE BETWEEN start_date_active
838 AND NVL (
839 end_date_active,
840 SYSDATE
841 )
842 AND grp.immediate_parent_flag = 'Y';
843
844 CURSOR get_resource_name (
845 c_resource_id NUMBER
846 ) IS
847 SELECT resource_name
848 FROM jtf_rs_resource_extns_vl
849 WHERE resource_id = c_resource_id
850 AND SYSDATE BETWEEN start_date_active
851 AND NVL (
852 end_date_active,
853 SYSDATE
854 );
855
856 CURSOR get_group_name (
857 c_group_id NUMBER
858 ) IS
859 SELECT group_name
860 FROM jtf_rs_groups_tl
864 );
861 WHERE GROUP_ID = c_group_id
862 AND LANGUAGE = USERENV (
863 'LANG'
865
866 CURSOR get_user_name (
867 c_user_id NUMBER
868 ) IS
869 SELECT user_name
870 FROM fnd_user
871 WHERE user_id = c_user_id
872 AND SYSDATE BETWEEN start_date AND NVL (
873 end_date,
874 SYSDATE
875 );
876 */
877
878 BEGIN
879
880 NULL;
881
882 /*
883 Logic has been commented OUT as per bug 3405904
884
885
886
887 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
888 aso_debug_pub.ADD (
889 ' Begin getNextApprover',
890 1,
891 'N'
892 );
893 END IF;
894 -- Intialize the rest of the columns for the next approver
895 nextapprover.person_id := NULL;
896 nextapprover.user_id := NULL;
897 nextapprover.first_name := NULL;
898 nextapprover.last_name := NULL;
899 nextapprover.authority := ame_util.authorityapprover;
900 nextapprover.api_insertion := ame_util.oamgenerated;
901 nextapprover.approval_status := ame_util.exceptionstatus;
902
903 IF approvaltypeidin IS NULL
904 THEN
905 nextapprover.approval_type_id := 0;
906 ELSE
907 nextapprover.approval_type_id := approvaltypeidin;
908 END IF;
909
910 nextapprover.group_or_chain_id := 1;
911 nextapprover.occurrence := NULL;
912 nextapprover.SOURCE := NULL;
913
914 WHILE TRUE
915 LOOP
916 OPEN get_parent_group_id (
917 g_last_approver_group_id
918 );
919 FETCH get_parent_group_id INTO l_parent_group_id;
920
921 IF (get_parent_group_id%ROWCOUNT = 0)
922 THEN -- No Parent Group Found
923 CLOSE get_parent_group_id;
924 RAISE no_parent_group_found;
925 END IF;
926
927 CLOSE get_parent_group_id;
928 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
929 aso_debug_pub.ADD (
930 'getNextApprover : Parent GroupId IS : ' || l_parent_group_id,
931 1,
932 'N'
933 );
934 END IF;
935 -- Store the group id in a global variable
936 g_last_approver_group_id := l_parent_group_id;
937 -- call the function to get the manager
938 l_manager_resource_id :=
939 aso_apr_resource_handler.get_manager (
940 l_parent_group_id
941 );
942 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
943 aso_debug_pub.ADD (
944 'getNextApprover : Manager ResourceID IS : ' || l_manager_resource_id,
945 1,
946 'N'
947 );
948 END IF;
949
950 IF (l_manager_resource_id IS NOT NULL)
951 THEN
952 -- get the user id FROM the resource id
953 OPEN get_user_id (
954 l_manager_resource_id
955 );
956 FETCH get_user_id INTO nextapprover.user_id;
957 CLOSE get_user_id;
958 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
959 aso_debug_pub.ADD (
960 'getNextApprover : Manager UserID IS : ' || nextapprover.user_id,
961 1,
962 'N'
963 );
964 END IF;
965
966 -- Check to see if the approver already exists
967 IF (check_approver_exists (
968 nextapprover.user_id
969 ) = FALSE
970 )
971 THEN
972 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
973 aso_debug_pub.ADD (
974 'getNextApprover : Approver does not exists in the list',
975 1,
976 'N'
977 );
978 END IF;
979 EXIT;
980 END IF;
981 END IF; -- No Manager Found Condition
982 l_loop_counter := l_loop_counter + 1;
983
984 IF (l_loop_counter > 20)
985 THEN
986 RAISE unending_loop;
987 END IF;
988 END LOOP;
989
990 OPEN get_person_id (
991 l_manager_resource_id
992 );
993 FETCH get_person_id INTO nextapprover.person_id;
994 CLOSE get_person_id;
995 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
996 aso_debug_pub.ADD (
997 'getNextApprover : Manager PersonId IS : ' || nextapprover.person_id,
998 1,
999 'N'
1000 );
1001 END IF;
1002
1003 -- IF both are null; we can not proceed
1004 IF (nextapprover.person_id IS NULL
1005 AND nextapprover.user_id IS NULL
1006 )
1007 THEN
1008 RAISE nvlusrperidexception;
1009 END IF;
1010
1011 -- Intialize approval status for the next approver
1012 -- nextApprover.approval_status := null;
1013 nextapprover.occurrence :=
1014 ame_engine.getnextapproveroccurrence (
1015 approverin => nextapprover,
1019 nextapprover.approval_status :=
1016 excludeapproverindexin => NULL
1017 );
1018 nextapprover.SOURCE := sourceruleidlistin;
1020 ame_engine.getoldapproverstatus (
1021 approverin => nextapprover
1022 );
1023 -- Convert the nextapprover record FROM PL/SQL Record to flat structure
1024 nextapproverout :=
1025 ame_util.serializeapproverrecord (
1026 approverrecordin => nextapprover
1027 );
1028 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1029 aso_debug_pub.ADD (
1030 'END getNextApprover',
1031 1,
1032 'N'
1033 );
1034 END IF;
1035 EXCEPTION
1036 WHEN nvlusrperidexception
1037 THEN
1038 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1039 aso_debug_pub.ADD (
1040 'Exception nvlusrperIdException raised in getNextApprover procedure',
1041 1,
1042 'N'
1043 );
1044 END IF;
1045 OPEN get_resource_name (
1046 l_manager_resource_id
1047 );
1048 FETCH get_resource_name INTO l_resource_name;
1049 CLOSE get_resource_name;
1050 errorcode := -20001;
1051 errormessage := 'The resource :'
1052 || l_resource_name
1053 || ' does not have a user id AND person id.'
1054 || ' Unable to traverse heirarchy for this resource ';
1055 ame_util.runtimeexception (
1056 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1057 routinenamein => 'getNextApprover',
1058 exceptionnumberin => errorcode,
1059 exceptionstringin => errormessage,
1060 transactionidin => ame_engine.temptransactionid,
1061 applicationidin => ame_engine.tempameapplicationid,
1062 localerrorin => FALSE
1063 );
1064 nextapproverout :=
1065 ame_util.serializeapproverrecord (
1066 approverrecordin => nextapprover
1067 );
1068 raise_application_error (
1069 errorcode,
1070 errormessage
1071 );
1072 WHEN no_parent_group_found
1073 THEN
1074 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1075 aso_debug_pub.ADD (
1076 'Exception no_parent_group_found raised in getNextApprover procedure ',
1077 1,
1078 'N'
1079 );
1080 END IF;
1081 OPEN get_group_name (
1082 g_last_approver_group_id
1083 );
1084 FETCH get_group_name INTO l_group_name;
1085 CLOSE get_group_name;
1086 errorcode := -20001;
1087 errormessage := 'Cannot find parent group for group: '
1088 || l_group_name;
1089 ame_util.runtimeexception (
1090 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1091 routinenamein => 'getNextApprover',
1092 exceptionnumberin => errorcode,
1093 exceptionstringin => errormessage,
1094 transactionidin => ame_engine.temptransactionid,
1095 applicationidin => ame_engine.tempameapplicationid,
1096 localerrorin => FALSE
1097 );
1098 nextapproverout :=
1099 ame_util.serializeapproverrecord (
1100 approverrecordin => nextapprover
1101 );
1102 raise_application_error (
1103 errorcode,
1104 errormessage
1105 );
1106 WHEN unending_loop
1107 THEN
1108 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1109 aso_debug_pub.ADD (
1110 'Exception Unending_Loop raised in getNextApprover procedure ',
1111 1,
1112 'N'
1113 );
1114 END IF;
1115 errorcode := -20003;
1116 errormessage := 'Please check the group heirarchy';
1117 ame_util.runtimeexception (
1118 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1119 routinenamein => 'getNextApprover',
1120 exceptionnumberin => SQLCODE,
1121 exceptionstringin => SQLERRM,
1122 transactionidin => ame_engine.temptransactionid,
1123 applicationidin => ame_engine.tempameapplicationid,
1124 localerrorin => FALSE
1125 );
1126 nextapproverout :=
1127 ame_util.serializeapproverrecord (
1128 approverrecordin => nextapprover
1129 );
1130 raise_application_error (
1131 errorcode,
1132 errormessage
1133 );
1134 WHEN OTHERS
1135 THEN
1136 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1137 aso_debug_pub.ADD (
1138 'Exception WHEN others raised in getNextApprover procedure ',
1139 1,
1140 'N'
1141 );
1142 END IF;
1143 ame_util.runtimeexception (
1144 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1148 transactionidin => ame_engine.temptransactionid,
1145 routinenamein => 'getNextApprover',
1146 exceptionnumberin => SQLCODE,
1147 exceptionstringin => SQLERRM,
1149 applicationidin => ame_engine.tempameapplicationid,
1150 localerrorin => FALSE
1151 );
1152 nextapproverout :=
1153 ame_util.serializeapproverrecord (
1154 approverrecordin => nextapprover
1155 );
1156 raise_application_error (
1157 errorcode,
1158 errormessage
1159 );
1160 */
1161
1162 END getnextapprover;
1163
1164 PROCEDURE getsurrogate (
1165 approverin IN VARCHAR2,
1166 parametersin IN VARCHAR2,
1167 surrogateout OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1168 ) AS
1169 -- surrogate ame_util.approverrecord;
1170 -- approver ame_util.approverrecord;
1171 BEGIN
1172
1173 NULL;
1174 /*
1175 Logic has been commented OUT as per bug 3405904
1176
1177
1178 ame_util.deserializeapproverrecord (
1179 approverrecordin => approverin,
1180 approverrecordout => approver
1181 );
1182 getnextapprover (
1183 approvaltypeidin => approver.approval_type_id,
1184 approverin => approverin,
1185 parametersin => parametersin,
1186 sourceruleidlistin => approver.SOURCE,
1187 nextapproverout => surrogateout
1188 );
1189 EXCEPTION
1190 WHEN OTHERS
1191 THEN
1192 ame_util.runtimeexception (
1193 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1194 routinenamein => 'getSurrogate',
1195 exceptionnumberin => SQLCODE,
1196 exceptionstringin => SQLERRM,
1197 transactionidin => ame_engine.temptransactionid,
1198 applicationidin => ame_engine.tempameapplicationid,
1199 localerrorin => FALSE
1200 );
1201 surrogate.person_id := NULL;
1202 surrogate.user_id := NULL;
1203 surrogate.first_name := NULL;
1204 surrogate.last_name := NULL;
1205 surrogate.authority := ame_util.authorityapprover;
1206 surrogate.api_insertion := ame_util.oamgenerated;
1207 surrogate.approval_status := ame_util.exceptionstatus;
1208 surrogate.approval_type_id := approver.approval_type_id;
1209 surrogate.group_or_chain_id := approver.group_or_chain_id;
1210 surrogate.occurrence := NULL;
1211 surrogate.SOURCE := NULL;
1212 surrogateout :=
1213 ame_util.serializeapproverrecord (
1214 approverrecordin => surrogate
1215 );
1216 RAISE;
1217 */
1218
1219 END getsurrogate;
1220
1221 PROCEDURE hasfinalauthority (
1222 approverin IN VARCHAR2,
1223 parametersin IN VARCHAR2,
1224 hasfinalauthorityynout OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1225 ) AS
1226
1227 /*
1228 badparameterexception EXCEPTION;
1229 no_resource_id_for_usr_id EXCEPTION;
1230 no_resource_id_for_per_id EXCEPTION;
1231 deserializedapproverin ame_util.approverrecord;
1232 errorcode INTEGER;
1233 errormessage VARCHAR2 (2000);
1234 PARAMETERS ame_util.parameterstable;
1235 parametercount INTEGER;
1236 l_role_code VARCHAR2 (240);
1237 l_level NUMBER := 0;
1238 l_temp_role_code VARCHAR2 (240);
1239 l_temp_level NUMBER := 0;
1240 l_coloncount NUMBER := 0;
1241 l_resource_id NUMBER;
1242 l_role_exist NUMBER;
1243 l_subordinate_role_exists NUMBER;
1244 l_user_name VARCHAR2 (240);
1245
1246 CURSOR get_user_resource_id (
1247 c_user_id NUMBER
1248 ) IS
1249 SELECT resource_id
1250 FROM jtf_rs_resource_extns
1251 WHERE user_id = c_user_id
1252 AND SYSDATE BETWEEN start_date_active
1253 AND NVL (
1254 end_date_active,
1255 SYSDATE
1256 );
1257
1258 CURSOR get_person_resource_id (
1259 c_person_id NUMBER
1260 ) IS
1261 SELECT resource_id
1262 FROM jtf_rs_resource_extns
1263 WHERE source_id = c_person_id
1264 AND CATEGORY = 'EMPLOYEE'
1265 AND SYSDATE BETWEEN start_date_active
1266 AND NVL (
1267 end_date_active,
1268 SYSDATE
1269 );
1273 c_role_code VARCHAR2
1270
1271 CURSOR check_role (
1272 c_resource_id NUMBER,
1274 ) IS
1275 SELECT COUNT (
1276 *
1277 )
1278 FROM jtf_rs_role_relations rel, jtf_rs_roles_b rol
1279 WHERE rol.role_id = rel.role_id
1280 AND rol.role_code = c_role_code
1281 AND rel.role_resource_type = 'RS_INDIVIDUAL'
1282 AND rel.role_resource_id = c_resource_id
1283 AND SYSDATE BETWEEN rel.start_date_active
1284 AND NVL (
1285 rel.end_date_active,
1286 SYSDATE
1287 )
1288 AND delete_flag <> 'Y';
1289
1290 CURSOR check_subordinates_role (
1291 c_role_code VARCHAR2,
1292 c_resource_id NUMBER
1293 ) IS
1294
1295 */
1296 --SELECT /*+ ordered */ COUNT (*)
1297 /*
1298 FROM jtf_rs_rep_managers mgr,
1299 jtf_rs_group_usages u,
1300 jtf_rs_role_relations rel,
1301 jtf_rs_roles_b rol
1302 WHERE u.usage = 'SALES'
1303 AND u.GROUP_ID = mgr.GROUP_ID
1304 AND SYSDATE BETWEEN rel.start_date_active
1305 AND NVL ( rel.end_date_active, SYSDATE)
1306 AND delete_flag <> 'Y'
1307 AND rol.role_id = rel.role_id
1308 AND rol.role_code = c_role_code
1309 AND rel.role_resource_type = 'RS_INDIVIDUAL'
1310 AND rel.role_resource_id = mgr.resource_id
1311 AND SYSDATE BETWEEN mgr.start_date_active
1312 AND NVL ( mgr.end_date_active, SYSDATE)
1313 AND mgr.parent_resource_id = c_resource_id
1314 AND mgr.hierarchy_type = 'MGR_TO_MGR';
1315
1316 CURSOR get_user_name (
1317 c_user_id NUMBER
1318 ) IS
1319 SELECT user_name
1320 FROM fnd_user
1321 WHERE user_id = c_user_id
1322 AND SYSDATE BETWEEN start_date AND NVL (
1323 end_date,
1324 SYSDATE
1325 );
1326 */
1327
1328 BEGIN
1329
1330 NULL;
1331
1332 /*
1333 Logic has been commented OUT as per bug 3405904
1334
1335
1336
1337 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1338 aso_debug_pub.ADD (
1339 'Begin hasFinalAuthority',
1340 1,
1341 'N'
1342 );
1343 END IF;
1344 -- convert the approver record FROM flat data structure to PL/SQL Record
1345
1346 ame_util.deserializeapproverrecord (
1347 approverrecordin => approverin,
1348 approverrecordout => deserializedapproverin
1349 );
1350 ame_util.deserializeparameterstable (
1351 parameterstablein => parametersin,
1352 parameterstableout => PARAMETERS
1353 );
1354 g_approval_type_id := deserializedapproverin.approval_type_id;
1355
1356 --The action parameters for jtf resource handler are of the form
1357 --<role code>:<level>where role id IS the resource role to look for
1358 --WHERE <level> IS a positive INTEGER, identifies authority level
1359 -- Find higher authority role code
1360 parametercount := PARAMETERS.COUNT;
1361
1362 FOR i IN 1 .. parametercount
1363 LOOP
1364 l_coloncount := INSTR (
1365 PARAMETERS (
1366 i
1367 ),
1368 ':',
1369 1,
1370 1
1371 );
1372 l_role_code := SUBSTR (
1373 PARAMETERS (
1374 i
1375 ),
1376 1,
1377 l_coloncount - 1
1378 );
1379 l_level := TO_NUMBER (
1380 SUBSTR (
1381 PARAMETERS (
1382 i
1383 ),
1384 l_coloncount + 1
1385 )
1386 );
1387
1388 IF ((l_coloncount = 0)
1389 OR (l_role_code IS NULL)
1390 OR (l_level = 0)
1391 )
1392 THEN
1393 RAISE badparameterexception;
1394 END IF;
1395
1396 IF (l_level > l_temp_level)
1397 THEN
1398 l_temp_level := l_level;
1399 l_temp_role_code := l_role_code;
1400 END IF;
1401 END LOOP; -- End of Parameter extraction Loop
1402 -- get the resource id based upon the user id
1403
1404 OPEN get_user_resource_id (
1405 deserializedapproverin.user_id
1406 );
1407 FETCH get_user_resource_id INTO l_resource_id;
1408 CLOSE get_user_resource_id;
1409
1410 -- Check IF the resource id IS null
1411 IF l_resource_id IS NULL
1412 THEN
1416 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1413 RAISE no_resource_id_for_usr_id;
1414 END IF;
1415
1417 aso_debug_pub.ADD (
1418 'hasFinalAuthority : Resource ID IS : ' || l_resource_id,
1419 1,
1420 'N'
1421 );
1422 aso_debug_pub.ADD (
1423 'hasFinalAuthority : Role Code IS : ' || l_temp_role_code,
1424 1,
1425 'N'
1426 );
1427 END IF;
1428 -- Check whether the apporverin has the highest authoriy role ?
1429
1430 OPEN check_role (
1431 l_resource_id,
1432 l_temp_role_code
1433 );
1434 FETCH check_role INTO l_role_exist;
1435 CLOSE check_role;
1436 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1437 aso_debug_pub.ADD (
1438 'hasFinalAuthority : Role Exists : ' || l_role_exist,
1439 1,
1440 'N'
1441 );
1442 END IF;
1443
1444 IF (l_role_exist > 0)
1445 THEN
1446 hasfinalauthorityynout := ame_util.booleantrue;
1447 ELSE
1448 -- Checking to see IF one of the subordinates has this role, IF so, honour it
1449 IF (ame_engine.tempapproverlist.COUNT = 0)
1450 THEN -- Only first time we check
1451 OPEN check_subordinates_role (
1452 l_temp_role_code,
1453 l_resource_id
1454 );
1455 FETCH check_subordinates_role INTO l_subordinate_role_exists;
1456 CLOSE check_subordinates_role;
1457 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1458 aso_debug_pub.ADD (
1459 'Subordinate Role Exists : ' || l_subordinate_role_exists,
1460 1,
1461 'N'
1462 );
1463 END IF;
1464
1465 IF l_subordinate_role_exists > 0
1466 THEN
1467 hasfinalauthorityynout := ame_util.booleantrue; -- Some subordinate has the Role
1468 ELSE
1469 hasfinalauthorityynout := ame_util.booleanfalse; -- No subordinate has the Role
1470 END IF;
1471 ELSE
1472 hasfinalauthorityynout := ame_util.booleanfalse; -- Not first time
1473 END IF;
1474 END IF; -- End if for l_role_Exist
1475 RETURN;
1476 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1477 aso_debug_pub.ADD (
1478 'END hasFinalAuthority',
1479 1,
1480 'N'
1481 );
1482 END IF;
1483 EXCEPTION
1484 WHEN badparameterexception
1485 THEN
1486 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1487 aso_debug_pub.ADD (
1488 'Exception badParameterException raised in hasFinalAuthority procedure ',
1489 1,
1490 'N'
1491 );
1492 END IF;
1493 errorcode := -20001;
1494 errormessage := 'Parameters for approval IS wrong';
1495 ame_util.runtimeexception (
1496 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1497 routinenamein => 'hasFinalAuthority',
1498 exceptionnumberin => errorcode,
1499 exceptionstringin => errormessage,
1500 transactionidin => ame_engine.temptransactionid,
1501 applicationidin => ame_engine.tempameapplicationid,
1502 localerrorin => FALSE
1503 );
1504 raise_application_error (
1505 errorcode,
1506 errormessage
1507 );
1508 hasfinalauthorityynout := ame_util.booleanfalse;
1509 WHEN no_resource_id_for_usr_id
1510 THEN
1511 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1512 aso_debug_pub.ADD (
1513 'Exception no_resource_id_for_usr_id raised in hasFinalAuthority procedure ',
1514 1,
1515 'N'
1516 );
1517 END IF;
1518 OPEN get_user_name (
1519 deserializedapproverin.user_id
1520 );
1521 FETCH get_user_name INTO l_user_name;
1522 CLOSE get_user_name;
1523 errorcode := -20002;
1524 errormessage := 'Cannot get resource id for Username : '
1525 || l_user_name;
1526 ame_util.runtimeexception (
1527 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1528 routinenamein => 'hasFinalAuthority',
1529 exceptionnumberin => errorcode,
1530 exceptionstringin => errormessage,
1531 transactionidin => ame_engine.temptransactionid,
1532 applicationidin => ame_engine.tempameapplicationid,
1533 localerrorin => FALSE
1534 );
1535 raise_application_error (
1536 errorcode,
1537 errormessage
1538 );
1539 hasfinalauthorityynout := ame_util.booleanfalse;
1540 WHEN OTHERS
1541 THEN
1542 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1543 aso_debug_pub.ADD (
1544 'Exception WHEN others raised in hasFinalAuthority procedure ',
1545 1,
1546 'N'
1547 );
1548 END IF;
1549 ame_util.runtimeexception (
1550 packagenamein => 'ASO_APR_RESOURCE_HANDLER',
1551 routinenamein => 'hasFinalAuthority',
1552 exceptionnumberin => SQLCODE,
1553 exceptionstringin => SQLERRM,
1554 transactionidin => ame_engine.temptransactionid,
1558 raise_application_error (
1555 applicationidin => ame_engine.tempameapplicationid,
1556 localerrorin => FALSE
1557 );
1559 errorcode,
1560 errormessage
1561 );
1562 hasfinalauthorityynout := ame_util.booleanfalse;
1563
1564 */
1565
1566 END hasfinalauthority;
1567
1568 PROCEDURE isasubordinate (
1569 subordinatein IN VARCHAR2,
1570 supervisorin IN VARCHAR2,
1571 isasubordinateynout OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1572 ) AS
1573 BEGIN
1574
1575 NULL;
1576 /*
1577 Logic has been commented as per bug 3405904
1578
1579
1580 isasubordinateynout := ame_util.booleanfalse;
1581
1582 */
1583 END isasubordinate;
1584 END aso_apr_resource_handler;