[Home] [Help]
PACKAGE BODY: APPS.PAY_PWR_BUS
Source
1 Package Body pay_pwr_bus as
2 /* $Header: pypwrrhi.pkb 115.2 2002/12/05 15:39:29 swinton noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pwr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_rate_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- rate_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_rate_id(p_rate_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_rate_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pay_pwr_shd.api_updating
47 (p_rate_id => p_rate_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_rate_id,hr_api.g_number)
52 <> pay_pwr_shd.g_old_rec.rate_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pay_pwr_shd.constraint_error('PAY_WCI_RATES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_rate_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pay_pwr_shd.constraint_error('PAY_WCI_RATES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_rate_id;
75 --
76 -- ----------------------------------------------------------------------------
77 --|----------------------------<chk_rate_code>--------------------------------|
78 -- ----------------------------------------------------------------------------
79 -- Description : This function is used to validate a new Rate Code (just code in
80 -- the table pay_wci_rates.
81 -- Validation : A rate code must be unique within an Account Number.
82 -- On Failure : Raise message 'The Rate Code you have entered already exists
83 -- within the current Account Number. Enter a different Rate
84 -- Code.
85 -- ----------------------------------------------------------------------------
86 FUNCTION chk_valid_rate_code (p_rate_code in varchar2
87 ,p_account_id in number
88 ,p_business_group_id in number)
89 RETURN BOOLEAN IS
90 --
91 CURSOR get_rate_code (p_rate_code varchar2
92 ,p_account_id number
93 ,p_business_group_id number)
94 IS
95 SELECT code
96 FROM pay_wci_rates
97 WHERE code = p_rate_code
98 AND account_id = p_account_id
99 AND business_group_id = p_business_group_id;
100 --
101 l_proc varchar2(72) := g_package||'chk_rate_code';
102 l_exists varchar2(30);
103 v_return_value boolean;
104 --
105 BEGIN
106 --
107 hr_utility.set_location('Entering:'||l_proc, 5);
108 --
109 OPEN get_rate_code (p_rate_code, p_account_id, p_business_group_id);
110 FETCH get_rate_code INTO l_exists;
111 --
112 IF get_rate_code%NOTFOUND THEN
113 --
114 hr_utility.set_location('Returning TRUE: '||l_proc, 10);
115 v_return_value := TRUE;
116 --
117 ELSE
118 --
119 hr_utility.set_location('Returning FALSE: '||l_proc, 15);
120 v_return_value := FALSE;
121 --
122 END IF;
123 --
124 CLOSE get_rate_code;
125 --
126 RETURN v_return_value;
127 --
128 hr_utility.set_location('Leaving: '||l_proc, 20);
129 --
130 END chk_valid_rate_code;
131 -- ----------------------------------------------------------------------------
132 -- |---------------------------< insert_validate >----------------------------|
133 -- ----------------------------------------------------------------------------
134 Procedure insert_validate(p_rec in pay_pwr_shd.g_rec_type) is
135 --
136 l_proc varchar2(72) := g_package||'insert_validate';
137 --
138 Begin
139 hr_utility.set_location('Entering:'||l_proc, 5);
140 --
141 -- Call all supporting business operations
142 --
143 chk_rate_id
144 (p_rate_id => p_rec.rate_id,
145 p_object_version_number => p_rec.object_version_number);
146 --
147 IF NOT chk_valid_rate_code (p_rate_code => p_rec.code
148 ,p_account_id => p_rec.account_id
149 ,p_business_group_id => p_rec.business_group_id)
150 THEN
151 --
152 hr_utility.set_message(801, 'PAY_74035_DUPLICATE_RATE_CODE');
153 hr_utility.raise_error;
154 --
155 ELSE
156 --
157 hr_utility.trace('Valid Rate Code');
158 --
159 END IF;
160 --
161 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
162 --
163 hr_utility.set_location(' Leaving:'||l_proc, 10);
164 End insert_validate;
165 --
166 -- ----------------------------------------------------------------------------
167 -- |---------------------------< update_validate >----------------------------|
168 -- ----------------------------------------------------------------------------
169 Procedure update_validate(p_rec in pay_pwr_shd.g_rec_type) is
170 --
171 l_proc varchar2(72) := g_package||'update_validate';
172 --
173 Begin
174 hr_utility.set_location('Entering:'||l_proc, 5);
175 --
176 -- Call all supporting business operations
177 --
178 chk_rate_id
179 (p_rate_id => p_rec.rate_id,
180 p_object_version_number => p_rec.object_version_number);
181 --
182 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
183 --
184 hr_utility.set_location(' Leaving:'||l_proc, 10);
185 End update_validate;
186 --
187 -- ----------------------------------------------------------------------------
188 -- |---------------------------< delete_validate >----------------------------|
189 -- ----------------------------------------------------------------------------
190 Procedure delete_validate(p_rec in pay_pwr_shd.g_rec_type) is
191 --
192 l_proc varchar2(72) := g_package||'delete_validate';
193 --
194 Begin
195 hr_utility.set_location('Entering:'||l_proc, 5);
196 --
197 -- Call all supporting business operations
198 --
199 hr_utility.set_location(' Leaving:'||l_proc, 10);
200 End delete_validate;
201 --
202 --
203 -- ---------------------------------------------------------------------------
204 -- |---------------------< return_legislation_code >-------------------------|
205 -- ---------------------------------------------------------------------------
206 --
207 function return_legislation_code
208 (p_rate_id in number) return varchar2 is
209 --
210 -- Declare cursor
211 --
212 cursor csr_leg_code is
213 select a.legislation_code
214 from per_business_groups a,
215 pay_wci_rates b
216 where b.rate_id = p_rate_id
217 and a.business_group_id = b.business_group_id;
218 --
219 -- Declare local variables
220 --
221 l_legislation_code varchar2(150);
222 l_proc varchar2(72) := g_package||'return_legislation_code';
223 --
224 begin
225 --
226 hr_utility.set_location('Entering:'|| l_proc, 10);
227 --
228 -- Ensure that all the mandatory parameter are not null
229 --
230 hr_api.mandatory_arg_error(p_api_name => l_proc,
231 p_argument => 'rate_id',
232 p_argument_value => p_rate_id);
233 --
234 open csr_leg_code;
235 --
236 fetch csr_leg_code into l_legislation_code;
237 --
238 if csr_leg_code%notfound then
239 --
240 close csr_leg_code;
241 --
242 -- The primary key is invalid therefore we must error
243 --
244 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
245 hr_utility.raise_error;
246 --
247 end if;
248 --
249 close csr_leg_code;
250 --
251 hr_utility.set_location(' Leaving:'|| l_proc, 20);
252 --
253 return l_legislation_code;
254 --
255 end return_legislation_code;
256 --
257 end pay_pwr_bus;