1 Package Body ame_gpi_bus as
2 /* $Header: amgpirhi.pkb 120.6 2012/01/19 06:12:55 rpahune ship $ */
3 --
4 -- ----------------------------------------------------------------------------
8 g_package varchar2(33) := ' ame_gpi_bus.'; -- Global package name
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
9
10 --
11
12
13 --
14 -- ----------------------------------------------------------------------------
15 -- |-------------------------< IS_NESTING_ALLOWED >---------------|
16 -- ----------------------------------------------------------------------------
17 --(Start of Comments)
18 --This procedure finds if the group p_nest can be nested inside the group
19 --p_group by finding all ancestors and descendents of p_group and ensuring
20 --that p_nest is not in the generated list. The ancestors are found in order
21 --to ensure that the nesting will not form a loop. The descendents are found
22 --in order to ensure that the p_nest is not already present in p_group.
23 --
24 function is_nesting_allowed(p_group in number
25 ,p_nest in number) return boolean as
26 --The following cursor finds all ancestors and descendants of
27 --p_group. The first part of the cursor will find all
28 --descendants of p_group. The second part finds all ancestors of
29 --p_group.
30 l_group_id varchar2(10);
31 l_grpid_num number;
32 cursor CSel1 is
33 select grp from
34 (
35 select distinct parameter grp
36 from (select *
37 from ame_approval_group_items
38 where sysdate >= start_date
39 and sysdate < (end_date - ame_util.oneSecond)
40 )
41 where parameter_name = 'OAM_group_id'
42 start with approval_group_id =p_group
43 connect by prior decode(parameter_name,'OAM_group_id',parameter,null)
44 = to_char(approval_group_id)
45
46 union
47
48 select to_char(approval_group_id) grp
49 from (select *
50 from ame_approval_group_items
51 where sysdate >= start_date
52 and sysdate < (end_date - ame_util.oneSecond)
53 )
54 where parameter_name = 'OAM_group_id'
55 start with parameter = to_char(p_group)
56 connect by prior to_char(approval_group_id) = parameter
57 );
58
59 begin
60 if p_group = p_nest then
61 return false;
62 end if;
63 for rec in CSel1
64 loop
65 l_group_id := rec.grp;
66 l_grpid_num := to_number(l_group_id);
67 if l_grpid_num = p_nest then
68 return false;
69 end if;
70 end loop;
71 return true;
72 end is_nesting_allowed;
73
74
75
76 --
77 -- ----------------------------------------------------------------------------
78 -- |-------------------------< GROUP_IS_IN_GROUP >---------------|
79 -- ----------------------------------------------------------------------------
80 --
81
82 function group_is_in_group(p_approval_group_id in number,
83 possiblyNestedGroupIdIn in number) return boolean as
84 cursor groupMemberCursor(approvalGroupIdIn in number) is
85 select
86 parameter,
87 parameter_name
88 from ame_approval_group_items
89 where
90 approval_group_id = approvalGroupIdIn and
91 sysdate between start_date and (end_date - ame_util.oneSecond);
92 l_proc varchar2(72) := g_package||'group_is_in_group';
93 tempGroupId number;
94 begin
95 hr_utility.set_location('Entering:'|| l_proc, 10);
96 for tempGroup in groupMemberCursor(approvalGroupIdIn => p_approval_group_id) loop
97 if(tempGroup.parameter_name = ame_util.approverOamGroupId) then
98 tempGroupId := to_number(tempGroup.parameter);
99 if(tempGroupId = possiblyNestedGroupIdIn) then
100 return(true);
101 elsif(group_is_in_group(p_approval_group_id => tempGroupId,
102 possiblyNestedGroupIdIn => possiblyNestedGroupIdIn)) then
103 return(true);
104 end if;
105 end if;
106 end loop;
107 return(false);
108 hr_utility.set_location(' Leaving:'||l_proc, 70);
109 end group_is_in_group;
110 --
111 --
112 -- ----------------------------------------------------------------------------
113 -- |-------------------------< GET_ALLOWED_NESTED_GROUPS >---------------|
114 -- ----------------------------------------------------------------------------
115 --
116 procedure get_allowed_nested_groups
117 (p_approval_group_id in number
118 ,allowedNestedGroupIdsOut out nocopy ame_util.stringList
119 ,allowedNestedGroupNamesOut out nocopy ame_util.stringList) as
120 cursor groupCursor is
121 select
122 approval_group_id
123 ,name
124 from ame_approval_groups
125 where
126 sysdate between start_date and (end_date - ame_util.oneSecond);
127 l_proc varchar2(72) := g_package||'get_allowed_nested_groups';
128 tempIndex number;
129 begin
130 hr_utility.set_location('Entering:'|| l_proc, 10);
131 tempIndex := 0; /* pre-increment */
132 for tempGroup in groupCursor loop
133 /*
134 Check whether the group identified by groupIdIn G is nested in
135 the group identified by tempGroup P. If so, we would have a loop in
136 the groups: P contains G, and G would contain P, which would then
137 contain G, . . . Also check whether P is already in G.
138 */
139 if(p_approval_group_id <> tempGroup.approval_group_id and
143 possiblyNestedGroupIdIn => tempGroup.approval_group_id)) then
140 not group_is_in_group(p_approval_group_id => tempGroup.approval_group_id,
141 possiblyNestedGroupIdIn => p_approval_group_id) and
142 not group_is_in_group(p_approval_group_id => p_approval_group_id,
144 tempIndex := tempIndex + 1;
145 allowedNestedGroupIdsOut(tempIndex) := to_char(tempGroup.approval_group_id);
146 allowedNestedGroupNamesOut(tempIndex) := tempGroup.name;
147 end if;
148 end loop;
149 hr_utility.set_location(' Leaving:'||l_proc, 70);
150 end get_allowed_nested_groups;
151
152 -- ----------------------------------------------------------------------------
153 -- |-------------------------< CHK_APPROVER_TYPE >----------------------------|
154 -- ----------------------------------------------------------------------------
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- This procedure checks if the group item to be added has a member of
159 -- approver type ,which is not allowed in one or more transaction types using
160 -- the approver group
161 --
162 -- Pre-Requisites:
163 -- None
164 --
165 -- In Parameters:
166 -- p_approval_group_id
167 -- p_parameter_name
168 -- p_parameter
169 --
170 -- Post Success:
171 -- Processing continues if a valid approval_group_id is existing for the item.
172 --
173 -- Post Failure:
174 -- An application error is raised.
175 --
176 -- Access Status:
177 -- Internal Row Handler Use Only.
178 --
179 -- {End Of Comments}
180 -- ----------------------------------------------------------------------------
181 procedure chk_approver_type(p_approval_group_id in number
182 ,p_parameter_name in varchar2
183 ,p_parameter in varchar2
184 ) IS
185 --
186 l_proc varchar2(72) := g_package || 'CHK_APPROVER_TYPE';
187 l_count number;
188 l_config_value ame_config_vars.variable_value%type;
189 l_application_id number;
190 --
191 -- cursor to find the transaction types using the approver group
192 --
193 cursor C_Sel1 is
194 select apgc.application_id
195 from ame_approval_group_config apgc
196 ,ame_calling_apps aca
197 where approval_group_id = p_approval_group_id
198 and aca.application_id = apgc.application_id
199 and sysdate between apgc.start_date and
200 nvl(apgc.end_date-ame_util.oneSecond,SYSDATE)
201 and sysdate between aca.start_date and
202 nvl(aca.end_date-ame_util.oneSecond,SYSDATE);
203 --
204 -- cursor to find the value of allowAllApproverTypes config variable for the
205 -- current transaction type.
206 --
207 cursor C_Sel2 is
208 select variable_value
209 from ame_config_vars
210 where variable_name like 'allowAllApproverTypes'
211 and application_id = l_application_id
212 and sysdate between start_date and
213 nvl(end_date-ame_util.oneSecond,SYSDATE);
214
215 Begin
216 hr_utility.set_location('Entering:'||l_proc,10);
217 hr_api.mandatory_arg_error(p_api_name => l_proc
218 ,p_argument => 'APPROVAL_GROUP_ID'
219 ,p_argument_value => p_approval_group_id
220 );
221 -- Check if the group item has members of approver type other than 'PER'
222 -- and 'FND_USR'
223 if p_parameter_name = 'wf_roles_name' then
224 select count(*) into l_count
225 from wf_roles
226 where name=p_parameter
227 and orig_system not in ('PER','FND_USR')
228 and sysdate between nvl(start_date,sysdate) and
229 nvl(expiration_date,sysdate);
230 elsif p_parameter_name = 'OAM_group_id' then
231 select count(*) into l_count
232 from ame_approval_group_members
233 where approval_group_id = p_parameter
234 and orig_system not in ('FND_USR','PER');
235 end if;
236 -- If group item has members of approver type other than 'PER' and 'FND_USR'
237 -- then find if any transaction type using the approver group has the config
238 -- variable allowAllApproverTypes set to 'no'
239 if l_count <> 0 then
240 open C_Sel1;
241 loop
242 fetch C_Sel1 into l_application_id;
243 exit when C_Sel1%NOTFOUND;
244 -- find the value of config variable allowAllApproverTypes for the current
245 -- transaction type
246 open C_Sel2;
247 fetch C_Sel2 into l_config_value;
248 if C_Sel2%notfound then
249 -- if the config variable is not defined for the current transaction type
250 -- use the global value
251 select variable_value into l_config_value
252 from ame_config_vars
253 where variable_name like 'allowAllApproverTypes'
254 and application_id = 0
255 and sysdate between start_date and
256 nvl(end_date-ame_util.oneSecond,SYSDATE);
257 end if;
258 close C_Sel2;
259 -- if all approver types are allowed for the current transaction ,then return
260 if l_config_value = 'no' then
261 fnd_message.set_name('PER','AME_400618_INV_APG_MEM_TXNTYP');
262 fnd_message.raise_error;
263 end if;
264 end loop;
265 end if;
266 hr_utility.set_location(' Leaving:'||l_proc,30);
267 exception
268 when app_exception.application_exception then
269 if hr_multi_message.exception_add
273 raise;
270 (p_associated_column1 => 'AME_APPROVAL_GROUP_ITEMS.APPROVAL_GROUP_ID'
271 ) then
272 hr_utility.set_location(' Leaving:'||l_proc, 40);
274 end if;
275 hr_utility.set_location( ' Leaving:'||l_proc,50 );
276 End chk_approver_type;
277
278 -- ----------------------------------------------------------------------------
279 -- |-------------------------< CHK_APPROVAL_GROUP_ID >---------------------------|
280 -- ----------------------------------------------------------------------------
281 -- {Start Of Comments}
282 --
283 -- Description:
284 -- This procedure validates the approval_group_id and also makes sure that
285 -- the group has is_static = 'Y'.
286 --
287 -- Pre-Requisites:
288 -- None
289 --
290 -- In Parameters:
291 -- p_approval_group_id
292 -- p_effective_date
293 --
294 -- Post Success:
295 -- Processing continues if a valid approval_group_id is existing for the item.
296 --
297 -- Post Failure:
298 -- An application error is raised.
299 --
300 -- Access Status:
301 -- Internal Row Handler Use Only.
302 --
303 -- {End Of Comments}
304 -- ----------------------------------------------------------------------------
305 procedure chk_approval_group_id(p_approval_group_id in number
306 ,p_effective_date in date
307 ) IS
308 --
309 l_proc varchar2(72) := g_package || 'CHK_APPROVAL_GROUP_ID';
310 l_count number;
311 --
312 -- Cursor to find number of groups with approval_group_id = p_approval_Group_id and is_static='Y'
313 --
314 Cursor C_Sel1 Is
315 select count(t.approval_group_id)
316 from ame_approval_groups t
317 where t.approval_group_id = p_approval_group_id
318 and p_effective_date between t.start_date
319 and (t.end_date - ame_util.oneSecond);
320 Cursor C_Sel2 Is
321 select count(t.approval_group_id)
322 from ame_approval_groups t
323 where t.approval_group_id = p_approval_group_id
324 and t.is_static = 'Y'
325 and p_effective_date between t.start_date
326 and (t.end_date - ame_util.oneSecond);
327 Begin
328 hr_utility.set_location('Entering:'||l_proc,10);
329 hr_api.mandatory_arg_error(p_api_name => l_proc
330 ,p_argument => 'APPROVAL_GROUP_ID'
331 ,p_argument_value => p_approval_group_id
332 );
333 -- Check if the approval_group exists
334 open C_Sel1;
335 fetch C_Sel1 into l_count;
336 close C_Sel1;
337 if l_count = 0 then
338 fnd_message.set_name('PER','AME_400557_INVALID_APG_ID');
339 fnd_message.raise_error;
340 end if;
341 -- Check if the approval_group exists and has is_static = 'Y'
342 open C_Sel2;
343 fetch C_Sel2 into l_count;
344 close C_Sel2;
345 if l_count = 0 then
346 fnd_message.set_name('PER','AME_400801_INV_STATIC_APG');
347 fnd_message.raise_error;
348 end if;
349 hr_utility.set_location(' Leaving:'||l_proc,30);
350 exception
351 when app_exception.application_exception then
352 if hr_multi_message.exception_add
353 (p_associated_column1 => 'AME_APPROVAL_GROUP_ITEMS.APPROVAL_GROUP_ID'
354 ) then
355 hr_utility.set_location(' Leaving:'||l_proc, 40);
356 raise;
357 end if;
358 hr_utility.set_location( ' Leaving:'||l_proc,50 );
359 End chk_approval_group_id;
360
361
362 -- ----------------------------------------------------------------------------
363 -- |-------------------------< CHK_PARAMETER_NAME >---------------------------|
364 -- ----------------------------------------------------------------------------
365 -- {Start Of Comments}
366 --
367 -- Description:
368 -- This procedure validates the parameter_name. Its value should be
369 -- in ('OAM_group_id','wf_roles_name') .
370 --
371 -- Pre-Requisites:
372 -- None
373 --
374 -- In Parameters:
375 -- p_parameter_name
376 --
377 -- Post Success:
378 -- Processing continues if parameter_name has valid values
379 --
380 -- Post Failure:
381 -- An application error is raised.
382 --
383 -- Access Status:
384 -- Internal Row Handler Use Only.
385 --
386 -- {End Of Comments}
387 -- ----------------------------------------------------------------------------
388 procedure chk_parameter_name(p_parameter_name in varchar2
389 ) IS
390 --
391 l_proc varchar2(72) := g_package || 'CHK_PARAMETER_NAME';
392
393 Begin
394 hr_utility.set_location('Entering:'||l_proc,10);
395 hr_api.mandatory_arg_error(p_api_name => l_proc
396 ,p_argument => 'PARAMETER_NAME'
397 ,p_argument_value => p_parameter_name
398 );
399 -- Check if the parameter_name is in ('OAM_group_id','wf_roles_name')
400 if p_parameter_name not in ('OAM_group_id','wf_roles_name') then
401 fnd_message.set_name('PER','AME_400567_INV_APG_ITM_PAR_NAM');
402 fnd_message.raise_error;
403 end if;
404 hr_utility.set_location(' Leaving:'||l_proc,30);
405 exception
406 when app_exception.application_exception then
407 if hr_multi_message.exception_add
408 (p_associated_column1 => 'AME_APPROVAL_GROUP_ITEMS.PARAMETER_NAME'
409 ) then
410 hr_utility.set_location(' Leaving:'||l_proc, 40);
411 raise;
412 end if;
416
413 hr_utility.set_location( ' Leaving:'||l_proc,50 );
414 End chk_parameter_name;
415
417 -- ----------------------------------------------------------------------------
418 -- |-------------------------< CHK_PARAMETER >---------------------------|
419 -- ----------------------------------------------------------------------------
420 -- {Start Of Comments}
421 --
422 -- Description:
423 -- This procedure validates the parameter value. Its value should be
424 -- a valid approval_group if parameter_name is 'OAM_group_id'.
425 --
426 -- Pre-Requisites:
427 -- None
428 --
429 -- In Parameters:
430 -- p_parameter_name
431 -- p_parameter
432 -- p_effective_date
433 --
434 -- Post Success:
435 -- Processing continues if parameter has valid values
436 --
437 -- Post Failure:
438 -- An application error is raised.
439 --
440 -- Access Status:
441 -- Internal Row Handler Use Only.
442 --
443 -- {End Of Comments}
444 -- ----------------------------------------------------------------------------
445 procedure chk_parameter(
446 p_approval_group_id in number
447 ,p_parameter_name in varchar2
448 ,p_parameter in varchar2
449 ,p_effective_date in date
450 ) IS
451 --
452 l_proc varchar2(72) := g_package || 'CHK_PARAMETER';
453 l_count number;
454 l_parameter_allowed boolean;
455 l_allowed_nested_group_ids ame_util.stringList;
456 l_allowed_nested_group_names ame_util.stringList;
457 --
458 -- Cursor to find number of groups with approval_group_id = p_parameter
459 --
460 Cursor C_Sel1 Is
461 select count(t.approval_group_id)
462 from ame_approval_groups t
463 where to_char(t.approval_group_id) = p_parameter
464 and p_effective_date between t.start_date and (t.end_date - ame_util.oneSecond);
465
466 Cursor C_Sel2 Is
467 select count(name)
468 from wf_roles
469 where name = p_parameter
470 and status = 'ACTIVE'
471 and (expiration_date is null or
472 sysdate < expiration_date)
473 and rownum < 2;
474
475 Begin
476 hr_utility.set_location('Entering:'||l_proc,10);
477 hr_api.mandatory_arg_error(p_api_name => l_proc
478 ,p_argument => 'PARAMETER'
479 ,p_argument_value => p_parameter
480 );
481 -- Check if the parameter corresponds to a valid group in ame_approval_groups
482 -- whenever parameter_name = 'OAM_group_id'
483 -- If parameter_name = 'wf_roles_name' then validate the wf_roles_name.
484 --
485 if p_parameter_name = ame_util.approverOamGroupId then
486 open C_Sel1;
487 fetch C_Sel1 into l_count;
488 close C_Sel1;
489 if (l_count = 0) then
490 fnd_message.set_name('PER','AME_400568_INV_APG_ITM_PARAM');
491 fnd_message.raise_error;
492 end if;
493 /*get_allowed_nested_groups
494 (
495 p_approval_group_id => p_approval_group_id
496 ,allowedNestedGroupIdsOut => l_allowed_nested_group_ids
497 ,allowedNestedGroupNamesOut => l_allowed_nested_group_names
498 );
499 l_parameter_allowed := false;
500 for allowedGroupId in 1 .. l_allowed_nested_group_ids.count
501 loop
502 if l_allowed_nested_group_ids(allowedGroupId)
503 = p_parameter then
504 l_parameter_allowed := true;
505 exit;
506 end if;
507 end loop;
508 if l_parameter_allowed = false then
509 hr_utility.set_location('Leaving:'|| l_proc, 20);
510 fnd_message.set_name('AME','NESTED_GROUP_INVALID');
511 fnd_message.raise_error;
512 end if;
513 */
514 if not ( is_nesting_allowed(p_group => p_approval_group_id
515 ,p_nest => to_number(p_parameter)
516 )
517 ) then
518 hr_utility.set_location('Leaving:'|| l_proc, 20);
519 fnd_message.set_name('PER','AME_400569_NEST_APG_NOT_ALLOW');
520 fnd_message.raise_error;
521 end if;
522
523 elsif p_parameter_name = 'wf_roles_name' then
524 open C_Sel2;
525 fetch C_Sel2 into l_count;
526 close C_Sel2;
527 if (l_count = 0) then
528 fnd_message.set_name('PER','AME_400568_INV_APG_ITM_PARAM');
529 fnd_message.raise_error;
530 end if;
531 end if;
532 hr_utility.set_location(' Leaving:'||l_proc,30);
533 exception
534 when app_exception.application_exception then
535 if hr_multi_message.exception_add
536 (p_associated_column1 => 'AME_APPROVAL_GROUP_ITEMS.PARAMETER'
537 ) then
538 hr_utility.set_location(' Leaving:'||l_proc, 40);
539 raise;
540 end if;
541 hr_utility.set_location( ' Leaving:'||l_proc,50 );
542 End chk_parameter;
543 -- ----------------------------------------------------------------------------
544 -- |-------------------------< CHK_ORDER_NUMBER>---------------------------|
545 -- ----------------------------------------------------------------------------
546 -- {Start Of Comments}
547 --
548 -- Description:
549 -- This procedure validates order_number which should be positive integer.
550 --
551 -- Pre-Requisites:
552 -- None
553 --
554 -- In Parameters:
558 -- Processing continues if order_number has valid value.
555 -- p_order_number
556 --
557 -- Post Success:
559 --
560 -- Post Failure:
561 -- An application error is raised.
562 --
563 -- Access Status:
564 -- Internal Row Handler Use Only.
565 --
566 -- {End Of Comments}
567 -- ----------------------------------------------------------------------------
568 procedure chk_order_number(
569 p_order_number in number
570 ) IS
571 --
572 l_proc varchar2(72) := g_package || 'CHK_ORDER_NUMBER';
573 Begin
574 hr_utility.set_location('Entering:'||l_proc,10);
575 hr_api.mandatory_arg_error(p_api_name => l_proc
576 ,p_argument => 'ORDER_NUMBER'
577 ,p_argument_value => p_order_number
578 );
579 -- check if order_number is negative
580 --
581 if p_order_number <=0 then
582 fnd_message.set_name('PER','AME_400565_INVALID_ORDER_NUM');
583 fnd_message.raise_error;
584 end if;
585 hr_utility.set_location(' Leaving:'||l_proc,30);
586 exception
587 when app_exception.application_exception then
588 if hr_multi_message.exception_add
589 (p_associated_column1 => 'AME_APPROVAL_GROUP_ITEMS.ORDER_NUMBER'
590 ) then
591 hr_utility.set_location(' Leaving:'||l_proc, 40);
592 raise;
593 end if;
594 hr_utility.set_location( ' Leaving:'||l_proc,50 );
595 End chk_order_number;
596 -- ----------------------------------------------------------------------------
597 -- |-------------------------< CHK_UNIQUE >---------------------------|
598 -- ----------------------------------------------------------------------------
599 -- {Start Of Comments}
600 --
601 -- Description:
602 -- This procedure checks the uniqueness of approval_Group_id, parameter,parameter_name
603 --
604 -- Pre-Requisites:
605 -- None
606 --
607 -- In Parameters:
608 -- p_approval_group_id
609 -- p_parameter_name
610 -- p_parameter
611 -- p_effective_date
612 --
613 -- Post Success:
614 -- Processing continues if the row is unique.
615 --
616 -- Post Failure:
617 -- An application error is raised.
618 --
619 -- Access Status:
620 -- Internal Row Handler Use Only.
621 --
622 -- {End Of Comments}
623 -- ----------------------------------------------------------------------------
624 procedure chk_unique(p_approval_group_id in number
625 ,p_parameter_name in varchar2
626 ,p_parameter in varchar2
627 ,p_effective_date in date
628 ) IS
629 --
630 l_proc varchar2(72) := g_package || 'CHK_UNIQUE';
631 l_count number;
632 --
633 -- Cursor to find if the item is already existing in the group
634 --
635 Cursor C_Sel1 Is
636 select count(t.approval_group_item_id)
637 from ame_approval_group_items t
638 where t.approval_group_id = p_approval_group_id
639 and t.parameter_name = p_parameter_name
640 and t.parameter = p_parameter
641 and p_effective_date between t.start_date and (t.end_date - ame_util.oneSecond);
642 Begin
643 hr_utility.set_location('Entering:'||l_proc,10);
644 hr_api.mandatory_arg_error(p_api_name => l_proc
645 ,p_argument => 'APPROVAL_GROUP_ID'
646 ,p_argument_value => p_approval_group_id
647 );
648 hr_api.mandatory_arg_error(p_api_name => l_proc
649 ,p_argument => 'PARAMETER_NAME'
650 ,p_argument_value => p_parameter_name
651 );
652 hr_api.mandatory_arg_error(p_api_name => l_proc
653 ,p_argument => 'PARAMETER'
654 ,p_argument_value => p_parameter
655 );
656 -- check if the row represents a unique member for a group.
657 open C_Sel1;
658 fetch C_Sel1 into l_count;
659 close C_Sel1;
660 if (l_count <> 0) then
661 fnd_message.set_name('PER','AME_400570_APG_ITM_NON_UNIQ');
662 fnd_message.raise_error;
663 end if;
664 hr_utility.set_location(' Leaving:'||l_proc,30);
665 exception
666 when app_exception.application_exception then
667 if hr_multi_message.exception_add
668 (p_associated_column1 => 'AME_APPROVAL_GROUP_ITEMS.PARAMETER'
669 ) then
670 hr_utility.set_location(' Leaving:'||l_proc, 40);
671 raise;
672 end if;
673 hr_utility.set_location( ' Leaving:'||l_proc,50 );
674 End chk_unique;
675
676 --
677 -- ----------------------------------------------------------------------------
678 -- |-----------------------< chk_non_updateable_args >------------------------|
679 -- ----------------------------------------------------------------------------
680 -- {Start Of Comments}
681 --
682 -- Description:
683 -- This procedure is used to ensure that non updateable attributes have
684 -- not been updated. If an attribute has been updated an error is generated.
685 --
686 -- Pre Conditions:
687 -- g_old_rec has been populated with details of the values currently in
688 -- the database.
689 --
690 -- In Arguments:
691 -- p_rec has been populated with the updated values the user would like the
692 -- record set to.
693 --
694 -- Post Success:
698 -- Post Failure:
695 -- Processing continues if all the non updateable attributes have not
696 -- changed.
697 --
699 -- An application error is raised if any of the non updatable attributes
700 -- have been altered.
701 --
702 -- {End Of Comments}
703 -- ----------------------------------------------------------------------------
704 Procedure chk_non_updateable_args
705 (p_effective_date in date
706 ,p_rec in ame_gpi_shd.g_rec_type
707 ) IS
708 --
709 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
710 l_created_by ame_approval_group_items.created_by%TYPE;
711 --
712 -- Cursor to find created_by value for the row
713 --
714 Cursor C_Sel1 Is
715 select t.created_by
716 from ame_approval_group_items t
717 where t.approval_group_item_id = p_rec.approval_group_item_id
718 and p_effective_date between t.start_date and (t.end_date - ame_util.oneSecond);
719 Begin
720 --
721 -- Only proceed with the validation if a row exists for the current
722 -- record in the HR Schema.
723 --
724 IF NOT ame_gpi_shd.api_updating
725 (p_approval_group_item_id => p_rec.approval_group_item_id
726 ,p_effective_date => p_effective_date
727 ,p_object_version_number => p_rec.object_version_number
728 ) THEN
729 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
730 fnd_message.set_token('PROCEDURE ', l_proc);
731 fnd_message.set_token('STEP ', '5');
732 fnd_message.raise_error;
733 END IF;
734 --
735 -- EDIT_HERE: Add checks to ensure non-updateable args have
736 -- not been updated.
737 --
738 -- If the group is seeded, do not allow updation of the group item's order number
739 --
740 open C_Sel1;
741 fetch C_Sel1 into l_created_by;
742 close C_Sel1;
743 --
744 -- ORDER_NUMBER is non-updateable if the group is seeded
745
746 if ame_utility_pkg.is_seed_user(l_created_by) = ame_util.seededDataCreatedById and
747 nvl(p_rec.order_number, hr_api.g_number) <>
748 nvl(ame_gpi_shd.g_old_rec.order_number,hr_api.g_number)then
749 hr_api.argument_changed_error
750 (p_api_name => l_proc
751 ,p_argument => 'ORDER_NUMBER'
752 ,p_base_table => ame_gpi_shd.g_tab_nam
753 );
754 end if;
755 -- APPROVAL_GROUP_ID is non-updateable.
756
757 if nvl(p_rec.approval_group_id, hr_api.g_number) <>
758 nvl(ame_gpi_shd.g_old_rec.approval_group_id,hr_api.g_number)then
759 hr_api.argument_changed_error
760 (p_api_name => l_proc
761 ,p_argument => 'APPROVAL_GROUP_ID'
762 ,p_base_table => ame_gpi_shd.g_tab_nam
763 );
764 end if;
765
766 -- PARAMETER_NAME is non-updateable.
767
768 if nvl(p_rec.parameter_name, hr_api.g_varchar2) <>
769 nvl(ame_gpi_shd.g_old_rec.parameter_name,hr_api.g_varchar2)then
770 hr_api.argument_changed_error
771 (p_api_name => l_proc
772 ,p_argument => 'PARAMETER_NAME'
773 ,p_base_table => ame_gpi_shd.g_tab_nam
774 );
775 end if;
776
777 -- PARAMETER is non-updateable.
778
779 if nvl(p_rec.parameter, hr_api.g_varchar2) <>
780 nvl(ame_gpi_shd.g_old_rec.parameter,hr_api.g_varchar2)then
781 hr_api.argument_changed_error
782 (p_api_name => l_proc
783 ,p_argument => 'PARAMETER'
784 ,p_base_table => ame_gpi_shd.g_tab_nam
785 );
786 end if;
787
788
789
790 End chk_non_updateable_args;
791 --
792 -- ----------------------------------------------------------------------------
793 -- |--------------------------< dt_update_validate >--------------------------|
794 -- ----------------------------------------------------------------------------
795 -- {Start Of Comments}
796 --
797 -- Description:
798 -- This procedure is used for referential integrity of datetracked
799 -- parent entities when a datetrack update operation is taking place
800 -- and where there is no cascading of update defined for this entity.
801 --
802 -- Prerequisites:
803 -- This procedure is called from the update_validate.
804 --
805 -- In Parameters:
806 --
807 -- Post Success:
808 -- Processing continues.
809 --
810 -- Post Failure:
811 --
812 -- Developer Implementation Notes:
813 -- This procedure should not need maintenance unless the HR Schema model
814 -- changes.
815 --
816 -- Access Status:
817 -- Internal Row Handler Use Only.
818 --
819 -- {End Of Comments}
820 -- ----------------------------------------------------------------------------
821 Procedure dt_update_validate
822 (p_approval_group_id in number default hr_api.g_number
823 ,p_datetrack_mode in varchar2
824 ,p_validation_start_date in date
825 ,p_validation_end_date in date
826 ) Is
827 --
828 l_proc varchar2(72) := g_package||'dt_update_validate';
829 --
830 Begin
831 --
832 -- Ensure that the p_datetrack_mode argument is not null
833 --
834 hr_api.mandatory_arg_error
835 (p_api_name => l_proc
836 ,p_argument => 'datetrack_mode'
837 ,p_argument_value => p_datetrack_mode
838 );
839 --
840 -- Mode will be valid, as this is checked at the start of the upd.
841 --
842 -- Ensure the arguments are not null
843 --
844 hr_api.mandatory_arg_error
845 (p_api_name => l_proc
846 ,p_argument => 'validation_start_date'
847 ,p_argument_value => p_validation_start_date
848 );
849 --
850 /*hr_api.mandatory_arg_error
851 (p_api_name => l_proc
852 ,p_argument => 'validation_end_date'
853 ,p_argument_value => p_validation_end_date
854 );*/
855 --
856 Exception
857 When Others Then
858 --
859 -- An unhandled or unexpected error has occurred which
860 -- we must report
861 --
862 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
863 fnd_message.set_token('PROCEDURE', l_proc);
864 fnd_message.set_token('STEP','15');
865 fnd_message.raise_error;
866 End dt_update_validate;
867 --
868 -- ----------------------------------------------------------------------------
869 -- |--------------------------< dt_delete_validate >--------------------------|
870 -- ----------------------------------------------------------------------------
871 -- {Start Of Comments}
872 --
873 -- Description:
874 -- This procedure is used for referential integrity of datetracked
875 -- child entities when either a datetrack DELETE or ZAP is in operation
876 -- and where there is no cascading of delete defined for this entity.
877 -- For the datetrack mode of DELETE or ZAP we must ensure that no
878 -- datetracked child rows exist between the validation start and end
879 -- dates.
880 --
881 -- Prerequisites:
882 -- This procedure is called from the delete_validate.
883 --
884 -- In Parameters:
885 --
886 -- Post Success:
887 -- Processing continues.
888 --
889 -- Post Failure:
890 -- If a row exists by determining the returning Boolean value from the
891 -- generic dt_api.rows_exist function then we must supply an error via
892 -- the use of the local exception handler l_rows_exist.
893 --
894 -- Developer Implementation Notes:
895 -- This procedure should not need maintenance unless the HR Schema model
896 -- changes.
897 --
898 -- Access Status:
899 -- Internal Row Handler Use Only.
900 --
901 -- {End Of Comments}
902 -- ----------------------------------------------------------------------------
903 Procedure dt_delete_validate
904 (p_approval_group_item_id in number
905 ,p_datetrack_mode in varchar2
906 ,p_validation_start_date in date
910 l_proc varchar2(72) := g_package||'dt_delete_validate';
907 ,p_validation_end_date in date
908 ) Is
909 --
911 --
912 Begin
913 --
914 -- Ensure that the p_datetrack_mode argument is not null
915 --
916 hr_api.mandatory_arg_error
917 (p_api_name => l_proc
918 ,p_argument => 'datetrack_mode'
919 ,p_argument_value => p_datetrack_mode
920 );
921 --
922 -- Only perform the validation if the datetrack mode is either
923 -- DELETE or ZAP
924 --
925 If (p_datetrack_mode = hr_api.g_delete or
926 p_datetrack_mode = hr_api.g_zap) then
927 --
928 --
929 -- Ensure the arguments are not null
930 --
931 hr_api.mandatory_arg_error
932 (p_api_name => l_proc
933 ,p_argument => 'validation_start_date'
934 ,p_argument_value => p_validation_start_date
935 );
936 --
937 /*hr_api.mandatory_arg_error
938 (p_api_name => l_proc
939 ,p_argument => 'validation_end_date'
940 ,p_argument_value => p_validation_end_date
941 );*/
942 --
943 hr_api.mandatory_arg_error
944 (p_api_name => l_proc
945 ,p_argument => 'approval_group_item_id'
946 ,p_argument_value => p_approval_group_item_id
947 );
948 --
949 --
950 --
951 End If;
952 --
953 Exception
954 When Others Then
955 --
956 -- An unhandled or unexpected error has occurred which
957 -- we must report
958 --
959 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
960 fnd_message.set_token('PROCEDURE', l_proc);
961 fnd_message.set_token('STEP','15');
962 fnd_message.raise_error;
963 --
964 End dt_delete_validate;
965 --
966 -- ----------------------------------------------------------------------------
967 -- |---------------------------< insert_validate >----------------------------|
968 -- ----------------------------------------------------------------------------
969 Procedure insert_validate
970 (p_rec in ame_gpi_shd.g_rec_type
971 ,p_effective_date in date
972 ,p_datetrack_mode in varchar2
973 ,p_validation_start_date in date
974 ,p_validation_end_date in date
975 ) is
976 --
977 l_proc varchar2(72) := g_package||'insert_validate';
978 --
979 Begin
980 hr_utility.set_location('Entering:'||l_proc, 5);
981 --
982 -- Validate Dependent Attributes
983 --
984 --
985 -- User Entered calls to validate procedures
986 chk_approval_group_id (
987 p_approval_group_id => p_rec.approval_group_id
988 ,p_effective_date => p_effective_date
989 );
990
991 chk_parameter_name (
992 p_parameter_name => p_rec.parameter_name
993 );
994
995 chk_parameter (
996 p_approval_group_id => p_rec.approval_group_id
997 ,p_parameter => p_rec.parameter
998 ,p_parameter_name => p_rec.parameter_name
999 ,p_effective_date => p_effective_date
1000 );
1001
1002 chk_order_number (
1003 p_order_number => p_rec.order_number
1004 );
1005
1006 chk_unique (
1007 p_approval_group_id => p_rec.approval_group_id
1008 ,p_parameter => p_rec.parameter
1009 ,p_parameter_name => p_rec.parameter_name
1010 ,p_effective_date => p_effective_date
1011 );
1012
1013 chk_approver_type (
1014 p_approval_group_id => p_rec.approval_group_id
1015 ,p_parameter_name => p_rec.parameter_name
1016 ,p_parameter => p_rec.parameter
1017 );
1018
1019 hr_utility.set_location(' Leaving:'||l_proc, 10);
1020 End insert_validate;
1021 --
1022 -- ----------------------------------------------------------------------------
1023 -- |---------------------------< update_validate >----------------------------|
1024 -- ----------------------------------------------------------------------------
1025 Procedure update_validate
1026 (p_rec in ame_gpi_shd.g_rec_type
1027 ,p_effective_date in date
1028 ,p_datetrack_mode in varchar2
1029 ,p_validation_start_date in date
1030 ,p_validation_end_date in date
1031 ) is
1032 --
1033 l_proc varchar2(72) := g_package||'update_validate';
1034 --
1035 Begin
1036 hr_utility.set_location('Entering:'||l_proc, 5);
1037 --
1038 -- Validate Dependent Attributes
1039 --
1040 -- Call the datetrack update integrity operation
1041 --
1042 dt_update_validate
1043 (p_approval_group_id => p_rec.approval_group_id
1044 ,p_datetrack_mode => p_datetrack_mode
1045 ,p_validation_start_date => p_validation_start_date
1046 ,p_validation_end_date => p_validation_end_date
1047 );
1048 --
1049 chk_non_updateable_args
1050 (p_effective_date => p_effective_date
1051 ,p_rec => p_rec
1052 );
1053 --
1054 --
1055 -- User Entered calls to validate procedures
1056 chk_order_number (
1057 p_order_number => p_rec.order_number
1058 );
1059 hr_utility.set_location(' Leaving:'||l_proc, 10);
1060 End update_validate;
1061 --
1062 -- ----------------------------------------------------------------------------
1066 (p_rec in ame_gpi_shd.g_rec_type
1063 -- |---------------------------< delete_validate >----------------------------|
1064 -- ----------------------------------------------------------------------------
1065 Procedure delete_validate
1067 ,p_effective_date in date
1068 ,p_datetrack_mode in varchar2
1069 ,p_validation_start_date in date
1070 ,p_validation_end_date in date
1071 ) is
1072 --
1073 l_proc varchar2(72) := g_package||'delete_validate';
1074 --
1075 Begin
1076 hr_utility.set_location('Entering:'||l_proc, 5);
1077 --
1078 -- Call all supporting business operations
1079 --
1080 dt_delete_validate
1081 (p_datetrack_mode => p_datetrack_mode
1082 ,p_validation_start_date => p_validation_start_date
1083 ,p_validation_end_date => p_validation_end_date
1084 ,p_approval_group_item_id => p_rec.approval_group_item_id
1085 );
1086 --
1087 hr_utility.set_location(' Leaving:'||l_proc, 10);
1088 End delete_validate;
1089 --
1090 end ame_gpi_bus;
[Home] [Help]