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