[Home] [Help]
PACKAGE BODY: APPS.BEN_APC_BUS
Source
1 Package Body ben_apc_bus as
2 /* $Header: beapcrhi.pkb 120.1 2008/01/10 09:29:11 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_apc_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_acrs_ptip_cvg_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 -- acrs_ptip_cvg_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
37 Procedure chk_acrs_ptip_cvg_id(p_acrs_ptip_cvg_id in number,
34 -- Access Status
35 -- Internal table handler use only.
36 --
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_acrs_ptip_cvg_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_apc_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_acrs_ptip_cvg_id => p_acrs_ptip_cvg_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_acrs_ptip_cvg_id,hr_api.g_number)
55 <> ben_apc_shd.g_old_rec.acrs_ptip_cvg_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_apc_shd.constraint_error('BEN_ACRS_PTIP_CVG_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_acrs_ptip_cvg_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_apc_shd.constraint_error('BEN_ACRS_PTIP_CVG_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_acrs_ptip_cvg_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |---------------------< chk_min_or_max_not_null >--------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that either the Minimum Coverage Allowed
85 -- Amount or the Maximum Coverage Allowed Amount is not null.
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- acrs_ptip_cvg_id Measures Allowed Code
92 -- mn_cvg_alwd_amt Minimum Coverage Allowed Amount
93 -- mx_cvg_alwd_amt Maximum Coverage Allowed Amount
94 --
95 -- Post Success
96 -- Processing continues
97 --
98 -- Post Failure
99 -- Errors handled by the procedure
100 --
101 -- Access Status
102 -- Internal table handler use only.
103 Procedure chk_min_or_max_not_null(p_mn_cvg_alwd_amt in number,
104 p_mx_cvg_alwd_amt in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_min_or_max_not_null';
107 --
108 Begin
109 --
110 hr_utility.set_location('Entering:'||l_proc, 5);
111 --
112 --
113 if p_mn_cvg_alwd_amt is null and p_mx_cvg_alwd_amt is null then
114 --
115 -- raise an error as one of the coverage amounts must have a value
116 --
117 fnd_message.set_name('BEN','BEN_93265_MIN_OR_MAX_NOT_NULL');
118 fnd_message.raise_error;
119 --
120 end if;
121 --
122 --
123 hr_utility.set_location('Leaving:'||l_proc, 10);
124 --
125 End chk_min_or_max_not_null;
126 --
127 -- ----------------------------------------------------------------------------
128 -- |--------------------------< dt_update_validate >--------------------------|
129 -- ----------------------------------------------------------------------------
130 -- {Start Of Comments}
131 --
132 -- Description:
133 -- This procedure is used for referential integrity of datetracked
134 -- parent entities when a datetrack update operation is taking place
135 -- and where there is no cascading of update defined for this entity.
136 --
137 -- Prerequisites:
138 -- This procedure is called from the update_validate.
139 --
140 -- In Parameters:
141 --
142 -- Post Success:
143 -- Processing continues.
144 --
145 -- Post Failure:
146 --
147 -- Developer Implementation Notes:
148 -- This procedure should not need maintenance unless the HR Schema model
149 -- changes.
150 --
151 -- Access Status:
152 -- Internal Row Handler Use Only.
153 --
154 -- {End Of Comments}
155 -- ----------------------------------------------------------------------------
156 Procedure dt_update_validate
157 (
158 p_datetrack_mode in varchar2,
159 p_validation_start_date in date,
160 p_validation_end_date in date) Is
161 --
162 l_proc varchar2(72) := g_package||'dt_update_validate';
163 l_integrity_error Exception;
164 l_table_name all_tables.table_name%TYPE;
165 --
166 Begin
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 --
169 -- Ensure that the p_datetrack_mode argument is not null
170 --
171 hr_api.mandatory_arg_error
172 (p_api_name => l_proc,
173 p_argument => 'datetrack_mode',
174 p_argument_value => p_datetrack_mode);
175 --
176 -- Only perform the validation if the datetrack update mode is valid
177 --
178 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
179 --
180 --
181 -- Ensure the arguments are not null
182 --
183 hr_api.mandatory_arg_error
184 (p_api_name => l_proc,
185 p_argument => 'validation_start_date',
186 p_argument_value => p_validation_start_date);
187 --
188 hr_api.mandatory_arg_error
189 (p_api_name => l_proc,
193 --
190 p_argument => 'validation_end_date',
191 p_argument_value => p_validation_end_date);
192 --
194 --
195 End If;
196 --
197 hr_utility.set_location(' Leaving:'||l_proc, 10);
198 Exception
199 When l_integrity_error Then
200 --
201 -- A referential integrity check was violated therefore
202 -- we must error
203 --
204 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
205 fnd_message.set_token('TABLE_NAME', l_table_name);
206 fnd_message.raise_error;
207 When Others Then
208 --
209 -- An unhandled or unexpected error has occurred which
210 -- we must report
211 --
212 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
213 fnd_message.set_token('PROCEDURE', l_proc);
214 fnd_message.set_token('STEP','15');
215 fnd_message.raise_error;
216 End dt_update_validate;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |--------------------------< dt_delete_validate >--------------------------|
220 -- ----------------------------------------------------------------------------
221 -- {Start Of Comments}
222 --
223 -- Description:
224 -- This procedure is used for referential integrity of datetracked
225 -- child entities when either a datetrack DELETE or ZAP is in operation
226 -- and where there is no cascading of delete defined for this entity.
227 -- For the datetrack mode of DELETE or ZAP we must ensure that no
228 -- datetracked child rows exist between the validation start and end
229 -- dates.
230 --
231 -- Prerequisites:
232 -- This procedure is called from the delete_validate.
233 --
234 -- In Parameters:
235 --
236 -- Post Success:
237 -- Processing continues.
238 --
239 -- Post Failure:
240 -- If a row exists by determining the returning Boolean value from the
241 -- generic dt_api.rows_exist function then we must supply an error via
242 -- the use of the local exception handler l_rows_exist.
243 --
244 -- Developer Implementation Notes:
245 -- This procedure should not need maintenance unless the HR Schema model
246 -- changes.
247 --
248 -- Access Status:
249 -- Internal Row Handler Use Only.
250 --
251 -- {End Of Comments}
252 -- ----------------------------------------------------------------------------
253 Procedure dt_delete_validate
254 (p_acrs_ptip_cvg_id in number,
255 p_datetrack_mode in varchar2,
256 p_validation_start_date in date,
257 p_validation_end_date in date) Is
258 --
259 l_proc varchar2(72) := g_package||'dt_delete_validate';
260 l_rows_exist Exception;
261 l_table_name all_tables.table_name%TYPE;
262 --
263 Begin
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 -- Ensure that the p_datetrack_mode argument is not null
267 --
268 hr_api.mandatory_arg_error
269 (p_api_name => l_proc,
270 p_argument => 'datetrack_mode',
271 p_argument_value => p_datetrack_mode);
272 --
273 -- Only perform the validation if the datetrack mode is either
274 -- DELETE or ZAP
275 --
276 If (p_datetrack_mode = 'DELETE' or
277 p_datetrack_mode = 'ZAP') then
278 --
279 --
280 -- Ensure the arguments are not null
281 --
282 hr_api.mandatory_arg_error
283 (p_api_name => l_proc,
284 p_argument => 'validation_start_date',
285 p_argument_value => p_validation_start_date);
286 --
287 hr_api.mandatory_arg_error
288 (p_api_name => l_proc,
289 p_argument => 'validation_end_date',
290 p_argument_value => p_validation_end_date);
291 --
292 hr_api.mandatory_arg_error
293 (p_api_name => l_proc,
294 p_argument => 'acrs_ptip_cvg_id',
295 p_argument_value => p_acrs_ptip_cvg_id);
296 --
297 --
298 --
299 End If;
300 --
301 hr_utility.set_location(' Leaving:'||l_proc, 10);
302 Exception
303 When l_rows_exist Then
304 --
305 -- A referential integrity check was violated therefore
306 -- we must error
307 --
308 ben_utility.child_exists_error(p_table_name => l_table_name);
309 --
310 When Others Then
311 --
312 -- An unhandled or unexpected error has occurred which
313 -- we must report
314 --
315 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
316 fnd_message.set_token('PROCEDURE', l_proc);
317 fnd_message.set_token('STEP','15');
318 fnd_message.raise_error;
319 End dt_delete_validate;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |---------------------------< insert_validate >----------------------------|
323 -- ----------------------------------------------------------------------------
324 Procedure insert_validate
325 (p_rec in ben_apc_shd.g_rec_type,
326 p_effective_date in date,
327 p_datetrack_mode in varchar2,
328 p_validation_start_date in date,
329 p_validation_end_date in date) is
330 --
331 l_proc varchar2(72) := g_package||'insert_validate';
332 --
333 Begin
334 hr_utility.set_location('Entering:'||l_proc, 5);
335 --
336 -- Call all supporting business operations
337 --
338 --
339 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
340 --
341 chk_acrs_ptip_cvg_id
345 --
342 (p_acrs_ptip_cvg_id => p_rec.acrs_ptip_cvg_id,
343 p_effective_date => p_effective_date,
344 p_object_version_number => p_rec.object_version_number);
346 chk_min_or_max_not_null
347 (p_mn_cvg_alwd_amt => p_rec.mn_cvg_alwd_amt,
348 p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt);
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 End insert_validate;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------< update_validate >----------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure update_validate
357 (p_rec in ben_apc_shd.g_rec_type,
358 p_effective_date in date,
359 p_datetrack_mode in varchar2,
360 p_validation_start_date in date,
361 p_validation_end_date in date) is
362 --
363 l_proc varchar2(72) := g_package||'update_validate';
364 --
365 Begin
366 hr_utility.set_location('Entering:'||l_proc, 5);
367 --
368 -- Call all supporting business operations
369 --
370 --
371 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
372 --
373 chk_acrs_ptip_cvg_id
374 (p_acrs_ptip_cvg_id => p_rec.acrs_ptip_cvg_id,
375 p_effective_date => p_effective_date,
376 p_object_version_number => p_rec.object_version_number);
377 --
378 chk_min_or_max_not_null
379 (p_mn_cvg_alwd_amt => p_rec.mn_cvg_alwd_amt,
380 p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt);
381 --
382 -- Call the datetrack update integrity operation
383 --
384 dt_update_validate
385 (
386 p_datetrack_mode => p_datetrack_mode,
387 p_validation_start_date => p_validation_start_date,
388 p_validation_end_date => p_validation_end_date);
389 --
390 hr_utility.set_location(' Leaving:'||l_proc, 10);
391 End update_validate;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |---------------------------< delete_validate >----------------------------|
395 -- ----------------------------------------------------------------------------
396 Procedure delete_validate
397 (p_rec in ben_apc_shd.g_rec_type,
398 p_effective_date in date,
399 p_datetrack_mode in varchar2,
400 p_validation_start_date in date,
401 p_validation_end_date in date) is
402 --
403 l_proc varchar2(72) := g_package||'delete_validate';
404 --
405 Begin
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 -- Call all supporting business operations
409 --
410 dt_delete_validate
411 (p_datetrack_mode => p_datetrack_mode,
412 p_validation_start_date => p_validation_start_date,
413 p_validation_end_date => p_validation_end_date,
414 p_acrs_ptip_cvg_id => p_rec.acrs_ptip_cvg_id);
415 --
416 hr_utility.set_location(' Leaving:'||l_proc, 10);
417 End delete_validate;
418 --
419 --
420 -- ---------------------------------------------------------------------------
421 -- |---------------------< return_legislation_code >-------------------------|
422 -- ---------------------------------------------------------------------------
423 --
424 function return_legislation_code
425 (p_acrs_ptip_cvg_id in number) return varchar2 is
426 --
427 -- Declare cursor
428 --
429 cursor csr_leg_code is
430 select a.legislation_code
431 from per_business_groups a,
432 ben_acrs_ptip_cvg_f b
433 where b.acrs_ptip_cvg_id = p_acrs_ptip_cvg_id
434 and a.business_group_id = b.business_group_id;
435 --
436 -- Declare local variables
437 --
438 l_legislation_code varchar2(150);
439 l_proc varchar2(72) := g_package||'return_legislation_code';
440 --
441 begin
442 --
443 hr_utility.set_location('Entering:'|| l_proc, 10);
444 --
445 -- Ensure that all the mandatory parameter are not null
446 --
447 hr_api.mandatory_arg_error(p_api_name => l_proc,
448 p_argument => 'acrs_ptip_cvg_id',
449 p_argument_value => p_acrs_ptip_cvg_id);
450 --
451 open csr_leg_code;
452 --
453 fetch csr_leg_code into l_legislation_code;
454 --
455 if csr_leg_code%notfound then
456 --
457 close csr_leg_code;
458 --
459 -- The primary key is invalid therefore we must error
460 --
461 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
462 fnd_message.raise_error;
463 --
464 end if;
465 --
466 close csr_leg_code;
467 --
468 hr_utility.set_location(' Leaving:'|| l_proc, 20);
469 --
470 return l_legislation_code;
471 --
472 end return_legislation_code;
473 --
474 end ben_apc_bus;