1 PACKAGE BODY PAY_PAYSUDAP_PKG AS
2 /* $Header: pyappab.pkb 115.1 99/07/17 05:42:13 porting ship $ */
3 --
4 --
5 --
6 PROCEDURE BAND_OVERLAP( p_accrual_plan_id IN number,
7 p_accrual_band_id IN number,
8 p_lower_limit IN number,
9 p_upper_limit IN number) IS
10 --
11 l_comb_exists VARCHAR2(2);
12 --
13 CURSOR dup_rec IS
14 select 'Y'
15 from pay_accrual_bands
16 where (p_lower_limit between lower_limit and upper_limit
17 or
18 p_upper_limit between lower_limit and upper_limit
19 or
20 lower_limit between p_lower_limit and p_upper_limit
21 or
22 upper_limit between p_lower_limit and p_upper_limit)
23 and ((p_accrual_band_id is null)
24 or (p_accrual_band_id is not null
25 and accrual_band_id <> p_accrual_band_id))
26 and accrual_plan_id = p_accrual_plan_id;
27 --
28 BEGIN
29 --
30 l_comb_exists := 'N';
31 --
32 -- open fetch and close the cursor - if a record is found then the local
33 -- variable will be set to 'Y', otherwise it will remain 'N'
34 --
35 OPEN dup_rec;
36 FETCH dup_rec INTO l_comb_exists;
37 CLOSE dup_rec;
38 --
39 -- go ahead and check the value of the local variable - if it's 'Y' then this
40 -- record is duplicated
41 --
42 IF (l_comb_exists = 'Y') THEN
43 hr_utility.set_message(801, 'HR_13161_PTO_BAND_OVERLAP');
44 hr_utility.raise_error;
45 END IF;
46 --
47 --
48 END BAND_OVERLAP;
49 --
50 PROCEDURE CEILING_CHECK( p_accrual_band_id IN number,
51 p_accrual_plan_id IN number,
52 p_ceiling IN number) IS
53 --
54 l_record_exists VARCHAR2(2);
55 --
56 CURSOR dup_rec IS
57 select 'Y'
58 from pay_accrual_bands
59 where accrual_plan_id = p_accrual_plan_id
60 and (p_accrual_band_id is null
61 or p_accrual_band_id is not null
62 and accrual_band_id <> p_accrual_band_id)
63 and ((p_ceiling is null
64 and ceiling is not null)
65 or (p_ceiling is not null
66 and ceiling is null));
67 --
68 BEGIN
69 --
70 l_record_exists := 'N';
71 --
72 -- open fetch and close the cursor - if a record is found then the local
73 -- variable will be set to 'Y', otherwise it will remain 'N'
74 --
75 OPEN dup_rec;
76 FETCH dup_rec INTO l_record_exists;
77 CLOSE dup_rec;
78 --
79 -- go ahead and check the value of the local variable - if it's 'Y' then this
80 -- record is duplicated
81 --
82 IF (l_record_exists = 'Y') THEN
83 hr_utility.set_message(801, 'HR_13165_PTO_INVALID_CEILING');
84 hr_utility.raise_error;
85 END IF;
86 --
87 --
88 END CEILING_CHECK;
89 --
90 --
91 --
92 END PAY_PAYSUDAP_PKG;
[Home] [Help]