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