[Home] [Help]
PACKAGE BODY: APPS.PAY_NCR_BUS
Source
1 Package Body pay_ncr_bus as
2 /* $Header: pyncrrhi.pkb 120.0 2005/05/29 06:52:12 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ncr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_net_calculation_rule_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 -- net_calculation_rule_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_net_calculation_rule_id(p_net_calculation_rule_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_net_calculation_rule_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_ncr_shd.api_updating
47 (p_net_calculation_rule_id => p_net_calculation_rule_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_net_calculation_rule_id,hr_api.g_number)
52 <> pay_ncr_shd.g_old_rec.net_calculation_rule_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pay_ncr_shd.constraint_error('PAY_NET_CALCULATION_RULES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_net_calculation_rule_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pay_ncr_shd.constraint_error('PAY_NET_CALCULATION_RULES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_net_calculation_rule_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_accrual_plan_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_net_calculation_rule_id PK
89 -- p_accrual_plan_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_accrual_plan_id (p_net_calculation_rule_id in number,
102 p_accrual_plan_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_accrual_plan_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pay_accrual_plans a
112 where a.accrual_plan_id = p_accrual_plan_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pay_ncr_shd.api_updating
119 (p_net_calculation_rule_id => p_net_calculation_rule_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_accrual_plan_id,hr_api.g_number)
124 <> nvl(pay_ncr_shd.g_old_rec.accrual_plan_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if accrual_plan_id value exists in pay_accrual_plans table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in pay_accrual_plans
137 -- table.
138 --
139 pay_ncr_shd.constraint_error('PAY_NET_CALCULATION_RULES_FK1');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_accrual_plan_id;
150 --
151 -- ---------------------------------------------------------------------------
152 -- |----------------------< chk_date_input_value >----------------------------|
153 -- ---------------------------------------------------------------------------
154 --
155 -- Description:
156 -- Validates the date input value for a net calculation rule -
157 -- this input value must be present for all rules on an accrual plan,
158 -- with the exception of the absence element's rule.
159 --
160 -- Prerequisites:
161 --
162 -- In Arguments:
163 -- p_accrual_plan_id
164 -- p_input_value_id
165 -- p_date_input_value_id
166 --
167 -- Post Success:
168 -- If date input value is present, processing continues.
169 --
170 -- Post Failure:
171 -- An error is raised if date input value is null.
172 --
173 -- Access Status:
174 -- Internal Development Use Only.
175 --
176 procedure chk_date_input_value (p_accrual_plan_id in number,
177 p_input_value_id in number,
178 p_date_input_value_id in number ) is
179 --
180 l_proc varchar2(72) := g_package||'chk_date_input_value';
181 l_pto_input_value_id number;
182
183 cursor c_get_absence_iv is
184 select pto_input_value_id
185 from pay_accrual_plans
186 where accrual_plan_id = p_accrual_plan_id;
187 --
188 begin
189 --
190 hr_utility.set_location('Entering:'||l_proc, 5);
191
192 open c_get_absence_iv;
193 fetch c_get_absence_iv into l_pto_input_value_id;
194 close c_get_absence_iv;
195
196 if (nvl(l_pto_input_value_id, -1) <> p_input_value_id) and
197 (p_date_input_value_id is null) then
198 --
199 fnd_message.set_name('PER', 'PER_52857_DATE_IV_MANDATORY');
200 fnd_message.raise_error;
201 --
202 end if;
203
204 hr_utility.set_location('Entering:'||l_proc, 5);
205 --
206 end chk_date_input_value;
207 --
208 -- ---------------------------------------------------------------------------
209 -- |----------------------< chk_duplicate_rule >-----------------------------|
210 -- ---------------------------------------------------------------------------
211 --
212 -- Description:
213 -- Checks the rule is not a duplicate for a particular plan.
214 --
215 -- Prerequisites:
216 --
217 -- In Arguments:
218 -- p_accrual_plan_id
219 -- p_net_calc_rule_id
220 -- p_input_value_id
221 -- p_date_input_value_id
222 --
223 -- Post Success:
224 -- If duplicate not found, processing continues.
225 --
226 -- Post Failure:
227 -- An error is raised if duplicate is found
228 --
229 -- Access Status:
230 -- Internal Development Use Only.
231 --
232 procedure chk_duplicate_rule (p_accrual_plan_id in number,
233 p_net_calc_rule_id in number,
234 p_input_value_id in number,
235 p_date_input_value_id in number ) is
236 --
237 l_proc varchar2(72) := g_package||'chk_duplicate_rule';
238 l_dummy number;
239
240 cursor c_get_duplicate is
241 select 1
242 from pay_net_calculation_rules
243 where accrual_plan_id = p_accrual_plan_id
244 and input_value_id = p_input_value_id
245 and date_input_value_id = p_date_input_value_id
246 and net_calculation_rule_id <> nvl(p_net_calc_rule_id, -1);
247 --
248 begin
249 --
250 hr_utility.set_location('Entering:'||l_proc, 5);
251
252 open c_get_duplicate;
253 fetch c_get_duplicate into l_dummy;
254
255 if c_get_duplicate%found then
256 --
257 close c_get_duplicate;
258
259 fnd_message.set_name('PER', 'HR_74022_PAP_NCR_DUPLICATE');
260 fnd_message.raise_error;
261 --
262 else
263 --
264 close c_get_duplicate;
265 --
266 end if;
267
268 hr_utility.set_location('Leaving:'||l_proc, 10);
269 --
270 end chk_duplicate_rule;
271 --
272 -- ----------------------------------------------------------------------------
273 -- |---------------------------< insert_validate >----------------------------|
274 -- ----------------------------------------------------------------------------
275 Procedure insert_validate(p_rec in pay_ncr_shd.g_rec_type) is
276 --
277 l_proc varchar2(72) := g_package||'insert_validate';
278 --
279 Begin
280 hr_utility.set_location('Entering:'||l_proc, 5);
281
282 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
283 --
284 -- Call all supporting business operations
285 --
286 chk_net_calculation_rule_id
287 (p_net_calculation_rule_id => p_rec.net_calculation_rule_id,
288 p_object_version_number => p_rec.object_version_number);
289 --
290 chk_accrual_plan_id
291 (p_net_calculation_rule_id => p_rec.net_calculation_rule_id,
292 p_accrual_plan_id => p_rec.accrual_plan_id,
293 p_object_version_number => p_rec.object_version_number);
294 --
295 chk_date_input_value(p_accrual_plan_id => p_rec.accrual_plan_id,
296 p_input_value_id => p_rec.input_value_id,
297 p_date_input_value_id => p_rec.date_input_value_id);
298 --
299 chk_duplicate_rule(p_accrual_plan_id => p_rec.accrual_plan_id,
300 p_net_calc_rule_id => p_rec.net_calculation_rule_id,
301 p_input_value_id => p_rec.input_value_id,
302 p_date_input_value_id => p_rec.date_input_value_id);
303 --
304 hr_utility.set_location(' Leaving:'||l_proc, 10);
305 End insert_validate;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |---------------------------< update_validate >----------------------------|
309 -- ----------------------------------------------------------------------------
310 Procedure update_validate(p_rec in pay_ncr_shd.g_rec_type) is
311 --
312 l_proc varchar2(72) := g_package||'update_validate';
313 --
314 Begin
315 hr_utility.set_location('Entering:'||l_proc, 5);
316
317 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
318 --
319 -- Call all supporting business operations
320 --
321 chk_net_calculation_rule_id
322 (p_net_calculation_rule_id => p_rec.net_calculation_rule_id,
323 p_object_version_number => p_rec.object_version_number);
324 --
325 chk_accrual_plan_id
326 (p_net_calculation_rule_id => p_rec.net_calculation_rule_id,
327 p_accrual_plan_id => p_rec.accrual_plan_id,
328 p_object_version_number => p_rec.object_version_number);
329 --
330 chk_date_input_value(p_accrual_plan_id => p_rec.accrual_plan_id,
331 p_input_value_id => p_rec.input_value_id,
332 p_date_input_value_id => p_rec.date_input_value_id);
333 --
334 chk_duplicate_rule(p_accrual_plan_id => p_rec.accrual_plan_id,
335 p_net_calc_rule_id => p_rec.net_calculation_rule_id,
336 p_input_value_id => p_rec.input_value_id,
337 p_date_input_value_id => p_rec.date_input_value_id);
338 --
339 hr_utility.set_location(' Leaving:'||l_proc, 10);
340 End update_validate;
341 --
342 -- ----------------------------------------------------------------------------
343 -- |---------------------------< delete_validate >----------------------------|
344 -- ----------------------------------------------------------------------------
345 Procedure delete_validate(p_rec in pay_ncr_shd.g_rec_type) is
346 --
347 l_proc varchar2(72) := g_package||'delete_validate';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- Call all supporting business operations
353 --
354 hr_utility.set_location(' Leaving:'||l_proc, 10);
355 End delete_validate;
356 --
357 --
358 -- ---------------------------------------------------------------------------
359 -- |---------------------< return_legislation_code >-------------------------|
360 -- ---------------------------------------------------------------------------
361 --
362 function return_legislation_code
363 (p_net_calculation_rule_id in number) return varchar2 is
364 --
365 -- Declare cursor
366 --
367 cursor csr_leg_code is
368 select a.legislation_code
369 from per_business_groups a,
370 pay_net_calculation_rules b
371 where b.net_calculation_rule_id = p_net_calculation_rule_id
372 and a.business_group_id = b.business_group_id;
373 --
374 -- Declare local variables
375 --
376 l_legislation_code varchar2(150);
377 l_proc varchar2(72) := g_package||'return_legislation_code';
378 --
379 begin
380 --
381 hr_utility.set_location('Entering:'|| l_proc, 10);
382 --
383 -- Ensure that all the mandatory parameter are not null
384 --
385 hr_api.mandatory_arg_error(p_api_name => l_proc,
386 p_argument => 'net_calculation_rule_id',
387 p_argument_value => p_net_calculation_rule_id);
388 --
389 open csr_leg_code;
390 --
391 fetch csr_leg_code into l_legislation_code;
392 --
393 if csr_leg_code%notfound then
394 --
395 close csr_leg_code;
396 --
397 -- The primary key is invalid therefore we must error
398 --
399 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
400 hr_utility.raise_error;
401 --
402 end if;
403 --
404 close csr_leg_code;
405 --
406 hr_utility.set_location(' Leaving:'|| l_proc, 20);
407 --
408 return l_legislation_code;
409 --
410 end return_legislation_code;
411 --
412 end pay_ncr_bus;