[Home] [Help]
PACKAGE BODY: APPS.AME_APPROVER_GROUP_API
Source
1 Package Body AME_APPROVER_GROUP_API as
2 /* $Header: amapgapi.pkb 120.3 2011/09/21 14:50:35 prasashe ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'AME_APPROVER_GROUP_API.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |-------------------------< REFRESH_GROUP_DEPENDENTS >--------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure refresh_group_dependents
14 (p_approval_group_id number
15 ,p_delete_group boolean default false)
16 is
17
18 --
19 -- Get all the parent groups for grp p_grp_id
20 --
21
22 cursor c_parent_grps(p_grp_id number) is
23 select distinct approval_group_id
24 from ame_approval_group_items
25 where parameter_name = 'OAM_group_id'
26 and sysdate >= start_date and sysdate < end_date
27 start with parameter = to_char(p_grp_id)
28 connect by prior to_char(approval_group_id) = parameter
29 union
30 select to_number(p_grp_id) from dual;
31 --
32 -- given a group_id, this will find all members including the members within the nested groups too.
33 --
34 cursor c_expanded_nested_grps(p_grp_id number) is
35 select group_id
36 ,param_name
37 ,param
38 ,ord_no
39 from (select distinct
40 approval_group_id group_id
41 ,parameter_name param_name
42 ,parameter param
43 ,order_number ord_no
44 from (select *
45 from ame_approval_group_items
46 where sysdate between start_date
47 and nvl(end_date - (1/86400), sysdate)
48 )
49 start with approval_group_id = p_grp_id
50 connect by prior parameter = to_char(approval_group_id)
51 )
52 where not exists ( select approval_group_id
53 from ame_approval_groups
54 where to_char(approval_group_id) = param
55 and param_name = 'OAM_group_id'
56 and is_static = 'Y'
57 and sysdate between start_date
58 and nvl(end_date - (1/86400), sysdate)
59 )
60 union
61 select approval_group_id
62 ,'OAM_group_id'
63 ,to_char(approval_group_id)
64 ,1
65 from ame_approval_Groups
66 where approval_group_id=p_grp_id
67 and is_static = 'N'
68 and sysdate between start_date and nvl(end_date - (1/86400), sysdate);
69
70 l_grp_id number;
71 l_query_string varchar2(4000);
72 l_orig_system varchar2(100);
73 l_orig_system_id number;
74 l_grp_id_list ame_util.idList;
75 l_count number;
76
77 begin
78
79 /*
80 For each parent group, delete the rows from group_members table and populate them again
81 with correct info.
82 */
83 l_count :=1;
84 for grp in c_parent_grps(p_approval_group_id) loop
85 l_grp_id_list(l_count) := grp.approval_group_id;
86 l_count :=l_count+1;
87 end loop;
88
89 if p_delete_group = true then
90 delete
91 from ame_approval_group_items
92 where parameter = to_char(p_approval_group_id)
93 and parameter_name = 'OAM_group_id'
94 and sysdate >= start_date and sysdate < end_date;
95 end if;
96
97 for idx in 1 .. l_grp_id_list.count loop
98
99 -- delete from ame_approval_group_members where approval_group_id = l_grp_id_list(idx);
100
101 if p_delete_group = false or l_grp_id_list(idx) <> p_approval_group_id then
102 update ame_approval_group_members
103 set approval_group_id = hr_api.g_number
104 where approval_group_id = l_grp_id_list(idx);
105
106 for r2 in c_expanded_nested_grps( l_grp_id_list(idx)) loop
107 l_orig_system := null;
108 l_orig_system_id := null;
109 l_query_string := null;
110
111 if r2.param_name = 'wf_roles_name' then
112 begin
113 select orig_system, orig_system_id
114 into l_orig_system, l_orig_system_id
115 from wf_roles
116 where name = r2.param
117 and status = 'ACTIVE'
118 and (expiration_date is null or
119 sysdate < expiration_date)
120 and rownum < 2;
121 exception
122 when no_data_found then
123 select orig_system, orig_system_id
124 into l_orig_system, l_orig_system_id
125 from ame_approval_group_members
126 where approval_group_id = hr_api.g_number
127 and parameter_name = 'wf_roles_name'
128 and parameter =r2.param
129 and rownum <2;
130 end;
131 else
132 select query_string
133 into l_query_string
134 from ame_approval_groups
135 where to_char(approval_group_id) = r2.param
136 and sysdate between start_date and nvl(end_date - ame_util.oneSecond, sysdate);
137 end if;
138
139 insert into ame_approval_group_members
140 (
141 approval_group_id
142 ,parameter_name
143 ,parameter
144 ,query_string
145 ,order_number
146 ,security_group_id
147 ,orig_system
148 ,orig_system_id
149 ,approval_group_members_id
150 ) values
151 (
152 l_grp_id_list(idx)
153 ,r2.param_name
154 ,r2.param
155 ,l_query_string
156 ,r2.ord_no
157 ,null
158 ,l_orig_system
159 ,l_orig_system_id
160 ,ame_approval_group_members_s.nextval
161 );
162 end loop;
163 delete from ame_approval_group_members where approval_group_id = hr_api.g_number;
164 else
165 delete from ame_approval_group_members where approval_group_id = l_grp_id_list(idx);
166 end if;
167 end loop;
168 exception
169 when others then
170 --this point can be reached when group_members table too does not contain the approver detail
171 fnd_message.set_name('PER','AME_400631_GRP_INVALID_MEMBERS');
172 fnd_message.raise_error;
173 end refresh_group_dependents;
174 --
175 --
176 -- ----------------------------------------------------------------------------
177 -- |-------------------------< CREATE_AME_APPROVER_GROUP >--------------------|
178 -- ----------------------------------------------------------------------------
179 --
180 procedure create_ame_approver_group
181 (p_validate in boolean default false
182 ,p_language_code in varchar2 default
183 hr_api.userenv_lang
184 ,p_name in varchar2
185 ,p_description in varchar2
186 ,p_is_static in varchar2
187 ,p_query_string in varchar2 default null
188 ,p_approval_group_id out nocopy number
189 ,p_start_date out nocopy date
190 ,p_end_date out nocopy date
191 ,p_object_version_number out nocopy number
192 ) is
193 --
194 -- Declare cursors and local variables
195 --
196 l_proc varchar2(72)
197 := g_package||'create_ame_approver_group';
198 l_swi_pkg_name varchar2(72) := 'AME_APPROVER_GROUP_SWI';
199 l_approval_group_id number;
200 l_object_version_number number;
201 l_object_version_number_conf number;
202 l_object_version_number_act number;
203 l_start_date date;
204 l_start_date_conf date;
205 l_start_date_act date;
206 l_end_date date;
207 l_end_date_conf date;
208 l_end_date_act date;
209 l_action_id number;
210 --
211 --Cursor to find all application ids
212 --
213 Cursor C_Sel1 is
214 select action_type_id
215 ,name
216 from ame_action_types
217 where name in ( ame_util.preApprovalTypeName
218 ,ame_util.postApprovalTypeName
219 ,ame_util.groupChainApprovalTypeName
220 )
221 and sysdate >= start_date and sysdate < end_date;
222
223 begin
224 hr_utility.set_location('Entering:'|| l_proc, 10);
225 --
226 -- Issue a savepoint
227 --
228 savepoint create_ame_approver_group;
229 --
230 -- Remember IN OUT parameter IN values. None here.
231 --
232 -- Call Before Process User Hook
233 --
234 begin
235 ame_approver_group_bk1.create_ame_approver_group_b
236 (p_name => p_name
237 ,p_description => p_description
238 ,p_is_static => p_is_static
239 ,p_query_string => p_query_string
240 );
241 exception
242 when hr_api.cannot_find_prog_unit then
243 hr_api.cannot_find_prog_unit_error
244 (p_module_name => 'create_ame_approver_group'
245 ,p_hook_type => 'BP'
246 );
247 end;
248
249 --
250 -- Process Logic
251 --
252 ame_apg_ins.ins(p_effective_date => sysdate
253 ,p_name => p_name
254 ,p_description => p_description
255 ,p_query_string => p_query_string
256 ,p_is_static => p_is_static
257 ,p_security_group_id => null
258 ,p_approval_group_id => l_approval_group_id
259 ,p_object_version_number => l_object_version_number
260 ,p_start_date => l_start_date
261 ,p_end_date => l_end_date
262 );
263 ame_agl_ins.ins_tl
264 (p_language_code => p_language_code
265 ,p_approval_group_id => l_approval_group_id
266 ,p_user_approval_group_name => p_name
267 ,p_description => p_description
268 );
269 --
270 -- Create group based actions.
271 --
272 for rec in C_Sel1
273 loop
274 if(rec.name = ame_util.preApprovalTypeName) then
275 fnd_message.set_name('PER', 'AME_400571_PRE_APG_ACT_DESC');
276 elsif (rec.name = ame_util.postApprovalTypeName) then
277 fnd_message.set_name('PER', 'AME_400572_POST_APG_ACT_DESC');
278 elsif (rec.name = ame_util.groupChainApprovalTypeName) then
279 fnd_message.set_name('PER', 'AME_400573_COA_APG_ACT_DESC');
280 end if;
281 fnd_message.set_token('GROUP_NAME', p_name);
282 ame_action_api.create_ame_action
283 (p_action_type_id => rec.action_type_id
284 ,p_parameter => to_char(l_approval_group_id)
285 ,p_description => fnd_message.get
286 ,p_parameter_two => null
287 ,p_action_id => l_action_id
288 ,p_object_version_number => l_object_version_number
289 ,p_start_date => l_start_date
290 ,p_end_date => l_end_date
291 );
292 end loop;
293
294 --
295 --if the created group is dynamic, then we have to update the
296 --ame_approval_group_members table using refresh_group_dependents
297 --
298 if p_is_static = 'N' then
299 refresh_group_dependents(p_approval_group_id => l_approval_group_id);
300 end if;
301
302 --
303 -- Call After Process User Hook
304 --
305 begin
306 ame_approver_group_bk1.create_ame_approver_group_a
307 (p_name => p_name
308 ,p_description => p_description
309 ,p_is_static => p_is_static
310 ,p_query_string => p_query_string
311 ,p_approval_group_id => l_approval_group_id
312 ,p_object_version_number => l_object_version_number
313 ,p_start_date => l_start_date
314 ,p_end_date => l_end_date
315 );
316 exception
317 when hr_api.cannot_find_prog_unit then
318 hr_api.cannot_find_prog_unit_error
319 (p_module_name => 'create_ame_approver_group'
320 ,p_hook_type => 'AP'
321 );
322 end;
323 --
324 -- When in validation only mode raise the Validate_Enabled exception
325 --
326 if p_validate then
327 raise hr_api.validate_enabled;
328 end if;
329 --
330 -- Set all IN OUT and OUT parameters with out values
331 --
332 p_approval_group_id := l_approval_group_id;
333 p_object_version_number := l_object_version_number;
334 p_start_date := l_start_date;
335 p_end_date := l_end_date;
336
337 --
338 hr_utility.set_location(' Leaving:'||l_proc, 70);
339 exception
340 when hr_api.validate_enabled then
341 --
342 -- As the Validate_Enabled exception has been raised
343 -- we must rollback to the savepoint
344 --
345 rollback to create_ame_approver_group;
346 --
347 -- Reset IN OUT parameters and set OUT parameters
348 -- (Any key or derived arguments must be set to null
349 -- when validation only mode is being used.)
350 p_approval_group_id := null;
351 p_object_version_number := null;
352 p_start_date := null;
353 p_end_date := null;
354 --
355 hr_utility.set_location(' Leaving:'||l_proc, 80);
356 when others then
357 --
358 -- A validation or unexpected error has occured
359 --
360 rollback to create_ame_approver_group;
361 --
362 -- Reset IN OUT parameters and set all
363 -- OUT parameters, including warnings, to null
364 --
365 p_approval_group_id := null;
366 p_object_version_number := null;
367 p_start_date := null;
368 p_end_date := null;
369 --
370 hr_utility.set_location(' Leaving:'||l_proc, 90);
371 raise;
372 end create_ame_approver_group;
373 --
374 --
375 -- ----------------------------------------------------------------------------
376 -- |--------------------------< UPDATE_AME_APPROVER_GROUP >-------------------|
377 -- ----------------------------------------------------------------------------
378 --
379 procedure update_ame_approver_group
380 (p_validate in boolean default false
381 ,p_approval_group_id in number
382 ,p_language_code in varchar2 default
383 hr_api.userenv_lang
384 ,p_description in varchar2 default hr_api.g_varchar2
385 ,p_is_static in varchar2 default hr_api.g_varchar2
386 ,p_query_string in varchar2 default hr_api.g_varchar2
387 ,p_object_version_number in out nocopy number
388 ,p_start_date out nocopy date
389 ,p_end_date out nocopy date
390 ) is
391 --
392 -- Declare cursors and local variables
393 --
394 l_proc varchar2(72) := g_package||'update_ame_approver_group';
395 l_object_version_number number;
396 l_start_date date;
397 l_end_date date;
398 l_old_is_static varchar2(1);
399 l_object_version_number_item number;
400 l_start_date_item date;
401 l_end_date_item date;
402 --cursor to find if the group has static members.
403 cursor Csel1 is
404 select is_static
405 from ame_approval_groups
406 where approval_group_id = p_approval_group_id
407 and sysdate >= start_date and sysdate < end_date;
408 --cursor to find the group's members.
409 cursor CSel3 is
410 select approval_group_item_id
411 ,object_version_number
412 from ame_approval_group_items
413 where approval_group_id = p_approval_group_id
414 and sysdate >= start_date and sysdate < end_date;
415
416 --
417 begin
418 hr_utility.set_location('Entering:'|| l_proc, 10);
419 --
420 -- Issue a savepoint
421 --
422 savepoint update_ame_approver_group;
423 --
424 -- Remember IN OUT parameter IN values
425 --
426 l_object_version_number := p_object_version_number;
427 --
428 -- Call Before Process User Hook
429 --
430 begin
431 ame_approver_group_bk2.update_ame_approver_group_b
432 (p_approval_group_id => p_approval_group_id
433 ,p_language_code => p_language_code
434 ,p_description => p_description
435 ,p_is_static => p_is_static
436 ,p_query_string => p_query_string
437 ,p_object_version_number => p_object_version_number
438 );
439 exception
440 when hr_api.cannot_find_prog_unit then
441 hr_api.cannot_find_prog_unit_error
442 (p_module_name => 'update_ame_approver_group'
443 ,p_hook_type => 'BP'
444 );
445 end;
446 --
447 -- Process Logic
448 --
449 --find the current is_static value.
450 open Csel1;
451 fetch Csel1 into l_old_is_static;
452 close Csel1;
453 --call row handler update procedure
454 ame_apg_upd.upd(p_effective_date => sysdate
455 ,p_datetrack_mode => hr_api.g_update
456 ,p_approval_group_id => p_approval_group_id
457 ,p_object_version_number => p_object_version_number
458 ,p_description => p_description
459 ,p_is_static => p_is_static
460 ,p_query_string => p_query_string
461 ,p_security_group_id => hr_api.g_number
462 ,p_start_date => l_start_date
463 ,p_end_date => l_end_date
464 );
465 --call tl table update procedure
466 ame_agl_upd.upd_tl
467 (p_approval_group_id => p_approval_group_id
468 ,p_language_code => p_language_code
469 ,p_description => p_description
470 );
471
472 --
473 -- When is_static is changed from 'Y' to 'N',
474 -- delete all static members of the group from ame_approval_group_items
475 --
476 if l_old_is_static ='Y' and p_is_static = 'N' and instrb(DBMS_UTILITY.FORMAT_CALL_STACK,'AME_APPROVER_GROUP_SWI') = 0 then
477 for rec in CSel3
478 loop
479 l_object_version_number_item := rec.object_version_number;
480 ame_gpi_del.del
481 (p_effective_date => sysdate
482 ,p_datetrack_mode => hr_api.g_delete
483 ,p_approval_group_item_id => rec.approval_group_item_id
484 ,p_object_version_number => l_object_version_number_item
485 ,p_start_date => l_start_date_item
486 ,p_end_date => l_end_date_item
487 );
488 end loop;
489 end if;
490 --
491 --since we are updating the group, values like query_string,
492 --need to be updated in ame_approval_group_members table also.
493 --Also in case a static group is updated into a dynamic group,
494 --we need to update members table.
495 --
496 refresh_group_dependents(p_approval_group_id => p_approval_group_id);
497
498 --
499 -- Call After Process User Hook
500 --
501 begin
502 ame_approver_group_bk2.update_ame_approver_group_a
503 (p_approval_group_id => p_approval_group_id
504 ,p_language_code => p_language_code
505 ,p_description => p_description
506 ,p_is_static => p_is_static
507 ,p_query_string => p_query_string
508 ,p_object_version_number => p_object_version_number
509 ,p_start_date => l_start_date
510 ,p_end_date => l_end_date
511 );
512 exception
513 when hr_api.cannot_find_prog_unit then
514 hr_api.cannot_find_prog_unit_error
515 (p_module_name => 'update_ame_approver_group'
516 ,p_hook_type => 'AP'
517 );
518 end;
519 --
520 -- When in validation only mode raise the Validate_Enabled exception
521 --
522 if p_validate then
523 raise hr_api.validate_enabled;
524 end if;
525 --
526 -- Set all IN OUT and OUT parameters with out values.
527 --
528 p_start_date := l_start_date;
529 p_end_date := l_end_date;
530 --
531 hr_utility.set_location(' Leaving:'||l_proc, 70);
532 exception
533 when hr_api.validate_enabled then
534 --
535 -- As the Validate_Enabled exception has been raised
536 -- we must rollback to the savepoint
537 --
538 rollback to update_ame_approver_group;
539 --
540 -- Reset IN OUT parameters and set OUT parameters
541 -- (Any key or derived arguments must be set to null
542 -- when validation only mode is being used.)
543 --
544 p_object_version_number := l_object_version_number;
545 p_start_date := null;
546 p_end_date := null;
547 --
548 hr_utility.set_location(' Leaving:'||l_proc, 80);
549 when others then
550 --
551 -- A validation or unexpected error has occured
552 --
553 rollback to update_ame_approver_group;
554 --
555 -- Reset IN OUT parameters and set all
556 -- OUT parameters, including warnings, to null
557 --
558 p_object_version_number := l_object_version_number;
559 p_start_date := null;
560 p_end_date := null;
561 --
562 hr_utility.set_location(' Leaving:'||l_proc, 90);
563 raise;
564 end update_ame_approver_group;
565 --
566 --
567 -- ----------------------------------------------------------------------------
568 -- |--------------------------< DELETE_AME_APPROVER_GROUP >-----------------------|
569 -- ----------------------------------------------------------------------------
570 --
571 procedure delete_ame_approver_group
572 (p_validate in boolean default false
573 ,p_approval_group_id in number
574 ,p_object_version_number in out nocopy number
575 ,p_start_date out nocopy date
576 ,p_end_date out nocopy date
577 ) is
578 --
579 -- Declare cursors and local variables
580 Cursor C_Sel2 is
581 select act.action_id
582 ,act.action_type_id
583 ,act.object_version_number
584 from ame_actions act
585 ,ame_action_types aty
586 where aty.name in ( ame_util.preApprovalTypeName
587 ,ame_util.postApprovalTypeName
588 ,ame_util.groupChainApprovalTypeName
589 )
590 and act.action_type_id = aty.action_type_id
591 and act.parameter = to_char(p_approval_group_id)
592 and sysdate >= act.start_date and sysdate < act.end_date
593 and sysdate >= aty.start_date and sysdate < aty.end_date;
594 --cursor to find all members for this approval group.
595 cursor CSel3 is
596 select approval_group_item_id
597 ,object_version_number
598 from ame_approval_group_items
599 where approval_group_id = p_approval_group_id
600 and sysdate >= start_date and sysdate < end_date;
601 --cursor to find the number of rules using the group(01-03-2005)
602 cursor CSel4 is
603 select count(*)
604 from ame_action_usages actu
605 ,ame_actions act
606 ,ame_action_types acty
607 where act.parameter = to_char(p_approval_group_id)
608 and actu.action_id = act.action_id
609 and act.action_type_id = acty.action_type_id
610 and acty.name in (
611 'approval-group chain of authority'
612 ,'pre-chain-of-authority approvals'
613 ,'post-chain-of-authority approvals')
614 and sysdate between actu.start_date and
615 nvl(actu.end_Date,sysdate)
616 and sysdate between act.start_date and
617 nvl(act.end_date-(1/86400),sysdate)
618 and sysdate between acty.start_date and
619 nvl(acty.end_date-(1/86400),sysdate);
620
621 l_swi_pkg_name varchar2(72) := 'AME_APPROVER_GROUP_SWI';
622 l_proc varchar2(72) := g_package||'delete_ame_approver_group';
623 l_object_version_number number;
624 l_start_date date;
625 l_end_date date;
626 l_object_version_number_conf number;
627 l_start_date_conf date;
628 l_end_date_conf date;
629 l_rule_count number;
630 l_config_count number;
631 --
632 begin
633 hr_utility.set_location('Entering:'|| l_proc, 10);
634 --
635 -- Issue a savepoint
636 --
637 savepoint delete_approver_group;
638 --
639 -- Remember IN OUT parameter IN values
640 --
641 l_object_version_number := p_object_version_number;
642 --
643 -- Call Before Process User Hook
644 --
645 begin
646 ame_approver_group_bk3.delete_ame_approver_group_b
647 (p_approval_group_id => p_approval_group_id
648 ,p_object_version_number => p_object_version_number
649 );
650 exception
651 when hr_api.cannot_find_prog_unit then
652 hr_api.cannot_find_prog_unit_error
653 (p_module_name => 'delete_ame_approver_group'
654 ,p_hook_type => 'BP'
655 );
656 end;
657 --
658 -- Process Logic
659 --
660 --check needs to be done if any rule is using this group
661 open CSel4;
662 fetch CSel4 into l_rule_count;
663 close CSel4;
664 if(l_rule_count <> 0) then
665 --there are rules using actions on this group
666 fnd_message.set_name('PER', 'AME_400558_RULES_EXIST_FOR_APG');
667 fnd_message.raise_error;
668 end if;
669
670 --throw an error if config is existing for this group
671 select count(*)
672 into l_config_count
673 from ame_approval_group_config a
674 ,ame_calling_apps aca
675 where a.approval_group_id = p_approval_group_id
676 and a.application_id = aca.application_id
677 and sysdate between aca.start_date and nvl(aca.end_date - (1/86400), sysdate)
678 and sysdate between a.start_date and nvl(a.end_date - (1/86400), sysdate);
679 if(l_config_count <> 0) then
680 fnd_message.set_name('PER', 'AME_400559_CFG_EXIST_FOR_APG');
681 fnd_message.raise_error;
682 end if;
683
684 --delete group actions for group related action_types
685 for rec in C_Sel2
686 loop
687 ame_action_api.delete_ame_action
688 (p_action_id => rec.action_id
689 ,p_action_type_id => rec.action_type_id
690 ,p_object_version_number => rec.object_version_number
691 ,p_start_date => l_start_date
692 ,p_end_date => l_end_date
693 );
694 end loop;
695 --
696 --delete items from ame_approval_group_items if this group is static.
697 --
698 for rec in CSel3
699 loop
700 l_object_version_number_conf :=rec.object_version_number;
701 ame_gpi_del.del
702 (p_effective_date => sysdate
703 ,p_datetrack_mode => hr_api.g_delete
704 ,p_approval_group_item_id => rec.approval_group_item_id
705 ,p_object_version_number => l_object_version_number_conf
706 ,p_start_date => l_start_date_conf
707 ,p_end_date => l_end_date_conf
708 );
709 end loop;
710 ame_apg_del.del(p_effective_date => sysdate
711 ,p_datetrack_mode => hr_api.g_delete
712 ,p_approval_group_id => p_approval_group_id
713 ,p_object_version_number => p_object_version_number
714 ,p_start_date => l_start_date
715 ,p_end_date => l_end_date
716 );
717 --if this group is used as a nested group by other groups
718 --delete the entry from items table and also refresh
719 --members table.
720 refresh_group_dependents(p_approval_group_id => p_approval_group_id
721 ,p_delete_group => true
722 );
723
724 --
725 -- Call After Process User Hook
726 --
727 begin
728 ame_approver_group_bk3.delete_ame_approver_group_a
729 (p_approval_group_id => p_approval_group_id
730 ,p_object_version_number => p_object_version_number
731 ,p_start_date => l_start_date
732 ,p_end_date => l_end_date
733 );
734 exception
735 when hr_api.cannot_find_prog_unit then
736 hr_api.cannot_find_prog_unit_error
737 (p_module_name => 'delete_ame_approver_group'
738 ,p_hook_type => 'AP'
739 );
740 end;
741 --
742 -- When in validation only mode raise the Validate_Enabled exception
743 --
744 if p_validate then
745 raise hr_api.validate_enabled;
746 end if;
747 --
748 -- Set all IN OUT and OUT parameters with out values.
749 --
750 p_start_date := l_start_date;
751 p_end_date := l_end_date;
752 --
753 hr_utility.set_location(' Leaving:'||l_proc, 70);
754 exception
755 when hr_api.validate_enabled then
756 --
757 -- As the Validate_Enabled exception has been raised
758 -- we must rollback to the savepoint
759 --
760 rollback to delete_approver_group;
761 --
762 -- Reset IN OUT parameters and set OUT parameters
763 -- (Any key or derived arguments must be set to null
764 -- when validation only mode is being used.)
765 --
766 p_object_version_number := l_object_version_number;
767 p_start_date := null;
768 p_end_date := null;
769 --
770 hr_utility.set_location(' Leaving:'||l_proc, 80);
771 when others then
772 --
773 -- A validation or unexpected error has occured
774 --
775 rollback to delete_approver_group;
776 --
777 -- Reset IN OUT parameters and set all
778 -- OUT parameters, including warnings, to null
779 --
780 p_object_version_number := l_object_version_number;
781 p_start_date := null;
782 p_end_date := null;
783 --
784 hr_utility.set_location(' Leaving:'||l_proc, 90);
785 raise;
786 end delete_ame_approver_group;
787 --
788 --
789 -- ----------------------------------------------------------------------------
790 -- |-------------------------< CREATE_APPROVER_GROUP_CONFIG >--------------------|
791 -- ----------------------------------------------------------------------------
792 --
793
794
795 procedure create_approver_group_config
796 (
797 p_validate in boolean default false
798 ,p_approval_group_id in number
799 ,p_application_id in number
800 ,p_voting_regime in varchar2
801 ,p_order_number in number default null
802 ,p_object_version_number out nocopy number
803 ,p_start_date out nocopy date
804 ,p_end_date out nocopy date
805 ) IS
806 --
807 -- Declare cursors and local variables
808 --
809 l_proc varchar2(72) := g_package||'create_approver_group_config';
810 l_object_version_number number;
811 l_start_date date;
812 l_end_date date;
813 --
814 begin
815 hr_utility.set_location('Entering:'|| l_proc, 10);
816 --
817 -- Issue a savepoint
818 --
819 savepoint add_approver_group_config;
820 --
821 -- Remember IN OUT parameter IN values
822 --
823
824 --
825 -- Call Before Process User Hook
826 --
827 begin
828 ame_approver_group_bk4.create_approver_group_config_b
829 (p_approval_group_id => p_approval_group_id
830 ,p_application_id => p_application_id
831 ,p_voting_regime => p_voting_regime
832 ,p_order_number => p_order_number
833 );
834 exception
835 when hr_api.cannot_find_prog_unit then
836 hr_api.cannot_find_prog_unit_error
837 (p_module_name => 'create_approver_group_config'
838 ,p_hook_type => 'BP'
839 );
840 end;
841 --
842 -- Process Logic
843 --
844 ame_gcf_ins.ins(p_effective_date => sysdate
845 ,p_approval_group_id => p_approval_group_id
846 ,p_application_id => p_application_id
847 ,p_voting_regime => p_voting_regime
848 ,p_order_number => p_order_number
849 ,p_object_version_number => l_object_version_number
850 ,p_start_date => l_start_date
851 ,p_end_date => l_end_date
852 );
853 --
854 -- Call After Process User Hook
855 --
856 begin
857 ame_approver_group_bk4.create_approver_group_config_a
858 (p_approval_group_id => p_approval_group_id
859 ,p_application_id => p_application_id
860 ,p_voting_regime => p_voting_regime
861 ,p_order_number => p_order_number
862 ,p_object_version_number => l_object_version_number
863 ,p_start_date => l_start_date
864 ,p_end_date => l_end_date
865 );
866 exception
867 when hr_api.cannot_find_prog_unit then
868 hr_api.cannot_find_prog_unit_error(p_module_name => 'create_approver_group_config'
869 ,p_hook_type => 'AP'
870 );
871 end;
872 --
873 -- When in validation only mode raise the Validate_Enabled exception
874 --
875 if p_validate then
876 raise hr_api.validate_enabled;
877 end if;
878 --
879 -- Set all IN OUT and OUT parameters with out values.
880 --
881 p_start_date := l_start_date;
882 p_end_date := l_end_date;
883 p_object_version_number := l_object_version_number;
884 --
885 hr_utility.set_location(' Leaving:'||l_proc, 70);
886 exception
887 when hr_api.validate_enabled then
888 --
889 -- As the Validate_Enabled exception has been raised
890 -- we must rollback to the savepoint
891 --
892 rollback to add_approver_group_config;
893 --
894 -- Reset IN OUT parameters and set OUT parameters
895 -- (Any key or derived arguments must be set to null
896 -- when validation only mode is being used.)
897 --
898 p_object_version_number := null;
899 p_start_date := null;
900 p_end_date := null;
901 --
902 hr_utility.set_location(' Leaving:'||l_proc, 80);
903 when others then
904 --
905 -- A validation or unexpected error has occured
906 --
907 rollback to add_approver_group_config;
908 --
909 -- Reset IN OUT parameters and set all
910 -- OUT parameters, including warnings, to null
911 --
912 p_object_version_number := null;
913 p_start_date := null;
914 p_end_date := null;
915 --
916 hr_utility.set_location(' Leaving:'||l_proc, 90);
917 raise;
918
919 end create_approver_group_config;
920
921 --
922 --
923 -- ----------------------------------------------------------------------------
924 -- |-------------------------< DELETE_APPROVER_GROUP_CONFIG >---------------|
925 -- ----------------------------------------------------------------------------
926 --
927
928 procedure delete_approver_group_config
929 (
930 p_validate in boolean default false
931 ,p_approval_group_id in number
932 ,p_application_id in number
933 ,p_object_version_number in out nocopy number
934 ,p_start_date out nocopy date
935 ,p_end_date out nocopy date
936 ) IS
937 --
938 -- Declare cursors and local variables
939 --
940 l_proc varchar2(72) := g_package||'delete_approver_group_config';
941 l_object_version_number number;
942 l_start_date date;
943 l_end_date date;
944 begin
945 hr_utility.set_location('Entering:'|| l_proc, 10);
946 --
947 -- Issue a savepoint
948 --
949 savepoint remove_approver_group_config;
950 --
951 -- Remember IN OUT parameter IN values
952 --
953 l_object_version_number := p_object_version_number;
954 --
955 -- Call Before Process User Hook
956 --
957 begin
958 ame_approver_group_bk5.delete_approver_group_config_b
959 (p_approval_group_id => p_approval_group_id
960 ,p_application_id => p_application_id
961 ,p_object_version_number => p_object_version_number
962 );
963 exception
964 when hr_api.cannot_find_prog_unit then
965 hr_api.cannot_find_prog_unit_error
966 (p_module_name => 'delete_approver_group_config'
967 ,p_hook_type => 'BP'
968 );
969 end;
970 --
971 -- Process Logic
972 --
973 ame_gcf_del.del(p_effective_date => sysdate
974 ,p_datetrack_mode => hr_api.g_delete
975 ,p_approval_group_id => p_approval_group_id
976 ,p_application_id => p_application_id
977 ,p_object_version_number => p_object_version_number
978 ,p_start_date => l_start_date
979 ,p_end_date => l_end_date
980 );
981 --
982 -- Call After Process User Hook
983 --
984 begin
985 ame_approver_group_bk5.delete_approver_group_config_a
986 (p_approval_group_id => p_approval_group_id
987 ,p_application_id => p_application_id
988 ,p_object_version_number => p_object_version_number
989 ,p_start_date => l_start_date
990 ,p_end_date => l_end_date
991 );
992 exception
993 when hr_api.cannot_find_prog_unit then
994 hr_api.cannot_find_prog_unit_error
995 (p_module_name => 'delete_approver_group_config'
996 ,p_hook_type => 'AP'
997 );
998 end;
999 --
1000 -- When in validation only mode raise the Validate_Enabled exception
1001 --
1002 if p_validate then
1003 raise hr_api.validate_enabled;
1004 end if;
1005 --
1006 -- Set all IN OUT and OUT parameters with out values.
1007 --
1008 p_start_date := l_start_date;
1009 p_end_date := l_end_date;
1010 --
1011 hr_utility.set_location(' Leaving:'||l_proc, 70);
1012 exception
1013 when hr_api.validate_enabled then
1014 --
1015 -- As the Validate_Enabled exception has been raised
1016 -- we must rollback to the savepoint
1017 --
1018 rollback to remove_approver_group_config;
1019 --
1020 -- Reset IN OUT parameters and set OUT parameters
1021 -- (Any key or derived arguments must be set to null
1022 -- when validation only mode is being used.)
1023 --
1024 p_object_version_number := l_object_version_number;
1025 p_start_date := null;
1026 p_end_date := null;
1027 --
1028 hr_utility.set_location(' Leaving:'||l_proc, 80);
1029 when others then
1030 --
1031 -- A validation or unexpected error has occured
1032 --
1033 rollback to remove_approver_group_config;
1034 --
1035 -- Reset IN OUT parameters and set all
1036 -- OUT parameters, including warnings, to null
1037 --
1038 p_object_version_number := l_object_version_number;
1039 p_start_date := null;
1040 p_end_date := null;
1041 --
1042 hr_utility.set_location(' Leaving:'||l_proc, 90);
1043 raise;
1044
1045 end delete_approver_group_config;
1046 --
1047 --
1048 -- ----------------------------------------------------------------------------
1049 -- |-------------------------< UPDATE_APPROVER_GROUP_CONFIG >---------------|
1050 -- ----------------------------------------------------------------------------
1051 --
1052 procedure update_approver_group_config
1053 (
1054 p_validate in boolean default false
1055 ,p_approval_group_id in number
1056 ,p_application_id in number
1057 ,p_voting_regime in varchar2 default hr_api.g_varchar2
1058 ,p_order_number in varchar2 default hr_api.g_number
1059 ,p_object_version_number in out nocopy number
1060 ,p_start_date out nocopy date
1061 ,p_end_date out nocopy date
1062 ) IS
1063 --
1064 -- Declare cursors and local variables
1065 --
1066 l_proc varchar2(72) := g_package||'update_approver_group_config';
1067 l_object_version_number number;
1068 l_start_date date;
1069 l_end_date date;
1070 --
1071 begin
1072 hr_utility.set_location('Entering:'|| l_proc, 10);
1073 --
1074 -- Issue a savepoint
1075 --
1076 savepoint update_approver_group_config;
1077 --
1078 -- Remember IN OUT parameter IN values
1079 --
1080 l_object_version_number := p_object_version_number;
1081 --
1082 -- Call Before Process User Hook
1083 --
1084 begin
1085 ame_approver_group_bk6.update_approver_group_config_b
1086 (p_approval_group_id => p_approval_group_id
1087 ,p_application_id => p_application_id
1088 ,p_voting_regime => p_voting_regime
1089 ,p_order_number => p_order_number
1090 ,p_object_version_number =>p_object_version_number
1091 );
1092 exception
1093 when hr_api.cannot_find_prog_unit then
1094 hr_api.cannot_find_prog_unit_error
1095 (p_module_name => 'update_approver_group_config'
1096 ,p_hook_type => 'BP'
1097 );
1098 end;
1099 --
1100 -- Process Logic
1101 --
1102 ame_gcf_upd.upd(p_effective_date => sysdate
1103 ,p_datetrack_mode => hr_api.g_update
1104 ,p_approval_group_id => p_approval_group_id
1105 ,p_application_id => p_application_id
1106 ,p_voting_regime => p_voting_regime
1107 ,p_order_number => p_order_number
1108 ,p_object_version_number => p_object_version_number
1109 ,p_start_date => l_start_date
1110 ,p_end_date => l_end_date
1111 );
1112 --
1113 -- Call After Process User Hook
1114 --
1115 begin
1116 ame_approver_group_bk6.update_approver_group_config_a
1117 (p_approval_group_id => p_approval_group_id
1118 ,p_application_id => p_application_id
1119 ,p_voting_regime => p_voting_regime
1120 ,p_order_number => p_order_number
1121 ,p_object_version_number => p_object_version_number
1122 ,p_start_date => l_start_date
1123 ,p_end_date => l_end_date
1124 );
1125 exception
1126 when hr_api.cannot_find_prog_unit then
1127 hr_api.cannot_find_prog_unit_error
1128 (p_module_name => 'update_approver_group_config'
1129 ,p_hook_type => 'AP'
1130 );
1131 end;
1132 --
1133 -- When in validation only mode raise the Validate_Enabled exception
1134 --
1135 if p_validate then
1136 raise hr_api.validate_enabled;
1137 end if;
1138 --
1139 -- Set all IN OUT and OUT parameters with out values.
1140 --
1141 p_start_date := l_start_date;
1142 p_end_date := l_end_date;
1143 --
1144 hr_utility.set_location(' Leaving:'||l_proc, 70);
1145 exception
1146 when hr_api.validate_enabled then
1147 --
1148 -- As the Validate_Enabled exception has been raised
1149 -- we must rollback to the savepoint
1150 --
1151 rollback to update_approver_group_config;
1152 --
1153 -- Reset IN OUT parameters and set OUT parameters
1154 -- (Any key or derived arguments must be set to null
1155 -- when validation only mode is being used.)
1156 --
1157 p_object_version_number := l_object_version_number;
1158 p_start_date := null;
1159 p_end_date := null;
1160 --
1161 hr_utility.set_location(' Leaving:'||l_proc, 80);
1162 when others then
1163 --
1164 -- A validation or unexpected error has occured
1165 --
1166 rollback to update_approver_group_config;
1167 --
1168 -- Reset IN OUT parameters and set all
1169 -- OUT parameters, including warnings, to null
1170 --
1171 p_object_version_number := l_object_version_number;
1172 p_start_date := null;
1173 p_end_date := null;
1174 --
1175 hr_utility.set_location(' Leaving:'||l_proc, 90);
1176 raise;
1177
1178 end update_approver_group_config;
1179 --
1180 --
1181 -- ----------------------------------------------------------------------------
1182 -- |-------------------------< CREATE_APPROVER_GROUP_ITEM >---------------|
1183 -- ----------------------------------------------------------------------------
1184 --
1185 procedure create_approver_group_item
1186 (
1187 p_validate in boolean default false
1188 ,p_approval_group_id in number
1189 ,p_parameter_name in varchar2
1190 ,p_parameter in varchar2
1191 ,p_order_number in number
1192 ,p_approval_group_item_id out nocopy number
1193 ,p_object_version_number out nocopy number
1194 ,p_start_date out nocopy date
1195 ,p_end_date out nocopy date
1196 ) IS
1197 --
1198 -- Declare cursors and local variables
1199 --
1200 l_proc varchar2(72) := g_package||'create_approver_group_item';
1201 l_object_version_number number;
1202 l_start_date date;
1203 l_end_date date;
1204 l_parameter_allowed boolean;
1205 --
1206 begin
1207 hr_utility.set_location('Entering:'|| l_proc, 10);
1208 --
1209 -- Issue a savepoint
1210 --
1211 savepoint add_approver_group_item;
1212 --
1213 -- Remember IN OUT parameter IN values
1214 --
1215
1216 --
1217 -- Call Before Process User Hook
1218 --
1219 begin
1220 ame_approver_group_bk7.create_approver_group_item_b
1221 (p_approval_group_id => p_approval_group_id
1222 ,p_parameter_name => p_parameter_name
1223 ,p_parameter => p_parameter
1224 ,p_order_number => p_order_number
1225 );
1226 exception
1227 when hr_api.cannot_find_prog_unit then
1228 hr_api.cannot_find_prog_unit_error
1229 (p_module_name => 'create_approver_group_item'
1230 ,p_hook_type => 'BP'
1231 );
1232 end;
1233 --
1234 -- Process Logic
1235 --
1236
1237 ame_gpi_ins.ins(p_effective_date => sysdate
1238 ,p_approval_group_id => p_approval_group_id
1239 ,p_parameter_name => p_parameter_name
1240 ,p_parameter => p_parameter
1241 ,p_order_number => p_order_number
1242 ,p_approval_group_item_id => p_approval_group_item_id
1243 ,p_object_version_number => l_object_version_number
1244 ,p_start_date => l_start_date
1245 ,p_end_date => l_end_date
1246 );
1247 refresh_group_dependents(p_approval_group_id => p_approval_group_id);
1248 --
1249 -- Call After Process User Hook
1250 --
1251 begin
1252 ame_approver_group_bk7.create_approver_group_item_a
1253 (p_approval_group_id => p_approval_group_id
1254 ,p_parameter_name => p_parameter_name
1255 ,p_parameter => p_parameter
1256 ,p_order_number => p_order_number
1257 ,p_object_version_number => l_object_version_number
1258 ,p_start_date => l_start_date
1259 ,p_end_date => l_end_date
1260 );
1261 exception
1262 when hr_api.cannot_find_prog_unit then
1263 hr_api.cannot_find_prog_unit_error
1264 (p_module_name => 'create_approver_group_item'
1265 ,p_hook_type => 'AP'
1266 );
1267 end;
1268 --
1269 -- When in validation only mode raise the Validate_Enabled exception
1270 --
1271 if p_validate then
1272 raise hr_api.validate_enabled;
1273 end if;
1274 --
1275 -- Set all IN OUT and OUT parameters with out values.
1276 --
1277 p_start_date := l_start_date;
1278 p_end_date := l_end_date;
1279 p_object_version_number := l_object_version_number;
1280 --
1281 hr_utility.set_location(' Leaving:'||l_proc, 70);
1282 exception
1283 when hr_api.validate_enabled then
1284 --
1285 -- As the Validate_Enabled exception has been raised
1286 -- we must rollback to the savepoint
1287 --
1288 rollback to add_approver_group_item;
1289 --
1290 -- Reset IN OUT parameters and set OUT parameters
1291 -- (Any key or derived arguments must be set to null
1292 -- when validation only mode is being used.)
1293 --
1294 p_object_version_number := null;
1295 p_start_date := null;
1296 p_end_date := null;
1297 --
1298 hr_utility.set_location(' Leaving:'||l_proc, 80);
1299 when others then
1300 --
1301 -- A validation or unexpected error has occured
1302 --
1303 rollback to add_approver_group_item;
1304 --
1305 -- Reset IN OUT parameters and set all
1306 -- OUT parameters, including warnings, to null
1307 --
1308 p_object_version_number := null;
1309 p_start_date := null;
1310 p_end_date := null;
1311 --
1312 hr_utility.set_location(' Leaving:'||l_proc, 90);
1313 raise;
1314
1315 end create_approver_group_item;
1316 --
1317 --
1318 -- ----------------------------------------------------------------------------
1319 -- |-------------------------< DELETE_APPROVER_GROUP_ITEM >---------------|
1320 -- ----------------------------------------------------------------------------
1321 --
1322 procedure delete_approver_group_item
1323 (
1324 p_validate in boolean default false
1325 ,p_approval_group_item_id in number
1326 ,p_object_version_number in out nocopy number
1327 ,p_start_date out nocopy date
1328 ,p_end_date out nocopy date
1329 ) IS
1330 --
1331 -- Declare cursors and local variables
1332 --
1333 l_proc varchar2(72) := g_package||'delete_approver_group_item';
1334 l_object_version_number number;
1335 l_start_date date;
1336 l_end_date date;
1337 l_approval_group_id number;
1338
1339 cursor Csel1 is
1340 select approval_group_id
1341 from ame_approval_group_items
1342 where approval_group_item_id = p_approval_group_item_id
1343 and sysdate >= start_date and sysdate < end_date;
1344 --
1345 begin
1346 hr_utility.set_location('Entering:'|| l_proc, 10);
1347 --
1348 -- Issue a savepoint
1349 --
1350 savepoint remove_approver_group_item;
1351 --
1352 -- Remember IN OUT parameter IN values
1353 --
1354 l_object_version_number := p_object_version_number;
1355 --
1356 -- Call Before Process User Hook
1357 --
1358 begin
1359 ame_approver_group_bk8.delete_approver_group_item_b
1360 (p_approval_group_item_id => p_approval_group_item_id
1361 ,p_object_version_number => p_object_version_number
1362 );
1363 exception
1364 when hr_api.cannot_find_prog_unit then
1365 hr_api.cannot_find_prog_unit_error
1366 (p_module_name => 'delete_approver_group_item'
1367 ,p_hook_type => 'BP'
1368 );
1369 end;
1370 --
1371 -- Process Logic
1372 --
1373 open Csel1;
1374 fetch Csel1 into l_approval_group_id;
1375 --handle case where item_id is invalid
1376 if Csel1%notfound then
1377 close Csel1;
1378 fnd_message.set_name('PER', 'AME_400560_APG_ITEM_IS_INVALID');
1379 fnd_message.raise_error;
1380 else
1381 close Csel1;
1382 end if;
1383 ame_gpi_del.del(p_effective_date => sysdate
1384 ,p_datetrack_mode => hr_api.g_delete
1385 ,p_approval_group_item_id => p_approval_group_item_id
1386 ,p_object_version_number => p_object_version_number
1387 ,p_start_date => l_start_date
1388 ,p_end_date => l_end_date
1389 );
1390 --
1391 --Update all dependent groups of p_approval_group_id
1392 --
1393 refresh_group_dependents(p_approval_group_id => l_approval_group_id);
1394 --
1395 -- Call After Process User Hook
1396 --
1397 begin
1398 ame_approver_group_bk8.delete_approver_group_item_a
1399 (p_approval_group_item_id => p_approval_group_item_id
1400 ,p_object_version_number => p_object_version_number
1401 ,p_start_date => l_start_date
1402 ,p_end_date => l_end_date
1403 );
1404 exception
1405 when hr_api.cannot_find_prog_unit then
1406 hr_api.cannot_find_prog_unit_error
1407 (p_module_name => 'delete_approver_group_item'
1408 ,p_hook_type => 'AP'
1409 );
1410 end;
1411 --
1412 -- When in validation only mode raise the Validate_Enabled exception
1413 --
1414 if p_validate then
1415 raise hr_api.validate_enabled;
1416 end if;
1417 --
1418 -- Set all IN OUT and OUT parameters with out values.
1419 --
1420 p_start_date := l_start_date;
1421 p_end_date := l_end_date;
1422 --
1423 hr_utility.set_location(' Leaving:'||l_proc, 70);
1424 exception
1425 when hr_api.validate_enabled then
1426 --
1427 -- As the Validate_Enabled exception has been raised
1428 -- we must rollback to the savepoint
1429 --
1430 rollback to remove_approver_group_item;
1431 --
1432 -- Reset IN OUT parameters and set OUT parameters
1433 -- (Any key or derived arguments must be set to null
1434 -- when validation only mode is being used.)
1435 --
1436 p_object_version_number := l_object_version_number;
1437 p_start_date := null;
1438 p_end_date := null;
1439 --
1440 hr_utility.set_location(' Leaving:'||l_proc, 80);
1441 when others then
1442 --
1443 -- A validation or unexpected error has occured
1444 --
1445 rollback to remove_approver_group_item;
1446 --
1447 -- Reset IN OUT parameters and set all
1448 -- OUT parameters, including warnings, to null
1449 --
1450 p_object_version_number := l_object_version_number;
1451 p_start_date := null;
1452 p_end_date := null;
1453 --
1454 hr_utility.set_location(' Leaving:'||l_proc, 90);
1455 raise;
1456
1457 end delete_approver_group_item;
1458
1459 --
1460 --
1461 -- ----------------------------------------------------------------------------
1462 -- |-------------------------< UPDATE_APPROVER_GROUP_ITEM >---------------|
1463 -- ----------------------------------------------------------------------------
1464 --
1465 procedure update_approver_group_item
1466 (
1467 p_validate in boolean default false
1468 ,p_approval_group_item_id in number
1469 ,p_order_number in varchar2 default hr_api.g_number
1470 ,p_object_version_number in out nocopy number
1471 ,p_start_date out nocopy date
1472 ,p_end_date out nocopy date
1473 ) IS
1474 --
1475 -- Declare cursors and local variables
1476 --
1477 l_proc varchar2(72) := g_package||'update_approver_group_item';
1478 l_approval_group_id number;
1479 l_object_version_number number;
1480 l_start_date date;
1481 l_end_date date;
1482
1483 cursor Csel1 is
1484 select approval_group_id
1485 from ame_approval_group_items
1486 where approval_group_item_id = p_approval_group_item_id
1487 and sysdate between start_date and end_date;
1488 --
1489 begin
1490 hr_utility.set_location('Entering:'|| l_proc, 10);
1491 --
1492 -- Issue a savepoint
1493 --
1494 savepoint update_approver_group_item;
1495 --
1496 -- Remember IN OUT parameter IN values
1497 --
1498 l_object_version_number := p_object_version_number;
1499 --
1500 -- Call Before Process User Hook
1501 --
1502 begin
1503 ame_approver_group_bk9.update_approver_group_item_b
1504 (p_approval_group_item_id => p_approval_group_item_id
1505 ,p_order_number => p_order_number
1506 ,p_object_version_number =>p_object_version_number
1507 );
1508 exception
1509 when hr_api.cannot_find_prog_unit then
1510 hr_api.cannot_find_prog_unit_error
1511 (p_module_name => 'update_approver_group_item'
1512 ,p_hook_type => 'BP'
1513 );
1514 end;
1515 --
1516 -- Process Logic
1517 --
1518 open Csel1;
1519 fetch Csel1 into l_approval_group_id;
1520 --handle case when item_id is invalid
1521 if Csel1%notfound then
1522 close Csel1;
1523 fnd_message.set_name('PER', 'AME_400560_APG_ITEM_IS_INVALID');
1524 fnd_message.raise_error;
1525 else
1526 close Csel1;
1527 end if;
1528 ame_gpi_upd.upd(p_effective_date => sysdate
1529 ,p_datetrack_mode => hr_api.g_update
1530 ,p_approval_group_item_id => p_approval_group_item_id
1531 ,p_order_number => p_order_number
1532 ,p_object_version_number => p_object_version_number
1533 ,p_start_date => l_start_date
1534 ,p_end_date => l_end_date
1535 );
1536 --update ame_approval_Group_members table to update order_number
1537 --of dependent groups
1538
1539 refresh_group_dependents(p_approval_group_id => l_approval_group_id);
1540 --
1541 -- Call After Process User Hook
1542 --
1543 begin
1544 ame_approver_group_bk9.update_approver_group_item_a
1545 (p_approval_group_item_id => p_approval_group_item_id
1546 ,p_order_number => p_order_number
1547 ,p_object_version_number => p_object_version_number
1548 ,p_start_date => l_start_date
1549 ,p_end_date => l_end_date
1550 );
1551 exception
1552 when hr_api.cannot_find_prog_unit then
1553 hr_api.cannot_find_prog_unit_error
1554 (p_module_name => 'update_approver_group_item'
1555 ,p_hook_type => 'AP'
1556 );
1557 end;
1558 --
1559 -- When in validation only mode raise the Validate_Enabled exception
1560 --
1561 if p_validate then
1562 raise hr_api.validate_enabled;
1563 end if;
1564 --
1565 -- Set all IN OUT and OUT parameters with out values.
1566 --
1567 p_start_date := l_start_date;
1568 p_end_date := l_end_date;
1569 --
1570 hr_utility.set_location(' Leaving:'||l_proc, 70);
1571 exception
1572 when hr_api.validate_enabled then
1573 --
1574 -- As the Validate_Enabled exception has been raised
1575 -- we must rollback to the savepoint
1576 --
1577 rollback to update_approver_group_item;
1578 --
1579 -- Reset IN OUT parameters and set OUT parameters
1580 -- (Any key or derived arguments must be set to null
1581 -- when validation only mode is being used.)
1582 --
1583 p_object_version_number := l_object_version_number;
1584 p_start_date := null;
1585 p_end_date := null;
1586 --
1587 hr_utility.set_location(' Leaving:'||l_proc, 80);
1588 when others then
1589 --
1590 -- A validation or unexpected error has occured
1591 --
1592 rollback to update_approver_group_item;
1593 --
1594 -- Reset IN OUT parameters and set all
1595 -- OUT parameters, including warnings, to null
1596 --
1597 p_object_version_number := l_object_version_number;
1598 p_start_date := null;
1599 p_end_date := null;
1600 --
1601 hr_utility.set_location(' Leaving:'||l_proc, 90);
1602 raise;
1603
1604 end update_approver_group_item;
1605
1606 end AME_APPROVER_GROUP_API;