[Home] [Help]
PACKAGE BODY: APPS.BEN_SVA_BUS
Source
1 Package Body ben_sva_bus as
2 /* $Header: besvarhi.pkb 120.1 2008/02/05 08:24:00 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_sva_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_svc_area_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 -- svc_area_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 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_svc_area_id(p_svc_area_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_svc_area_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_sva_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_svc_area_id => p_svc_area_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_svc_area_id,hr_api.g_number)
55 <> ben_sva_shd.g_old_rec.svc_area_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_sva_shd.constraint_error('BEN_SVC_AREA_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_svc_area_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_sva_shd.constraint_error('BEN_SVC_AREA_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_svc_area_id;
78 --
79 -- ---------------------------------------------------------------------------
80 -- |------------------------< chk_name_unique >-------------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- ensure that the Service Area Name is unique
85 -- within business_group
86 --
87 -- Pre Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_name is Service Area name
92 -- p_svc_area_id is svc_area_id
93 -- p_business_group_id
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 --
104 -- ----------------------------------------------------------------------------
105 Procedure chk_name_unique
106 ( p_svc_area_id in varchar2
107 ,p_name in varchar2
108 ,p_business_group_id in number)
109 is
110 l_proc varchar2(72) := g_package||'chk_name_unique';
111 l_dummy char(1);
112 cursor c1 is select null
113 from ben_svc_area_f
114 Where svc_area_id <> nvl(p_svc_area_id,-1)
115 and name = p_name
116 and business_group_id = p_business_group_id;
117 --
118 Begin
119 hr_utility.set_location('Entering:'||l_proc, 5);
120 --
121 open c1;
122 fetch c1 into l_dummy;
123 if c1%found then
124 close c1;
125 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
126 fnd_message.raise_error;
127 end if;
128 --
129 hr_utility.set_location('Leaving:'||l_proc, 15);
130 End chk_name_unique;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |------< chk_org_unit_prdct >------|
134 -- ----------------------------------------------------------------------------
135 --
136 -- Description
137 -- This procedure is used to check that the lookup value is valid.
138 --
139 -- Pre Conditions
140 -- None.
141 --
142 -- In Parameters
143 -- svc_area_id PK of record being inserted or updated.
144 -- org_unit_prdct Value of lookup code.
145 -- effective_date effective date
146 -- object_version_number Object version number of record being
147 -- inserted or updated.
148 --
149 -- Post Success
150 -- Processing continues
151 --
152 -- Post Failure
153 -- Error handled by procedure
154 --
155 -- Access Status
156 -- Internal table handler use only.
157 --
158 Procedure chk_org_unit_prdct(p_svc_area_id in number,
159 p_org_unit_prdct in varchar2,
160 p_effective_date in date,
161 p_object_version_number in number) is
162 --
163 l_proc varchar2(72) := g_package||'chk_org_unit_prdct';
164 l_api_updating boolean;
165 --
166 Begin
167 --
168 hr_utility.set_location('Entering:'||l_proc, 5);
169 --
170 l_api_updating := ben_sva_shd.api_updating
171 (p_svc_area_id => p_svc_area_id,
172 p_effective_date => p_effective_date,
173 p_object_version_number => p_object_version_number);
174 --
175 if (l_api_updating
176 and p_org_unit_prdct
177 <> nvl(ben_sva_shd.g_old_rec.org_unit_prdct,hr_api.g_varchar2)
178 or not l_api_updating)
179 and p_org_unit_prdct is not null then
180 --
181 -- check if value of lookup falls within lookup type.
182 --
183 if hr_api.not_exists_in_hr_lookups
184 (p_lookup_type => 'BEN_PRDCT',
185 p_lookup_code => p_org_unit_prdct,
186 p_effective_date => p_effective_date) then
187 --
188 -- raise error as does not exist as lookup
189 --
190 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
194 fnd_message.raise_error;
191 fnd_message.set_token('FIELD','p_org_unit_prdct');
192 fnd_message.set_token('VALUE',p_org_unit_prdct);
193 fnd_message.set_token('TYPE','BEN_PRDCT');
195 --
196 end if;
197 --
198 end if;
199 --
200 hr_utility.set_location('Leaving:'||l_proc,10);
201 --
202 end chk_org_unit_prdct;
203 --
204 -- ----------------------------------------------------------------------------
205 -- |--------------------------< dt_update_validate >--------------------------|
206 -- ----------------------------------------------------------------------------
207 -- {Start Of Comments}
208 --
209 -- Description:
210 -- This procedure is used for referential integrity of datetracked
211 -- parent entities when a datetrack update operation is taking place
212 -- and where there is no cascading of update defined for this entity.
213 --
214 -- Prerequisites:
215 -- This procedure is called from the update_validate.
216 --
217 -- In Parameters:
218 --
219 -- Post Success:
220 -- Processing continues.
221 --
222 -- Post Failure:
223 --
224 -- Developer Implementation Notes:
225 -- This procedure should not need maintenance unless the HR Schema model
226 -- changes.
227 --
228 -- Access Status:
229 -- Internal Row Handler Use Only.
230 --
231 -- {End Of Comments}
232 -- ----------------------------------------------------------------------------
233 Procedure dt_update_validate
234 (
235 p_datetrack_mode in varchar2,
236 p_validation_start_date in date,
237 p_validation_end_date in date) Is
238 --
239 l_proc varchar2(72) := g_package||'dt_update_validate';
240 l_integrity_error Exception;
241 l_table_name all_tables.table_name%TYPE;
242 --
243 Begin
244 hr_utility.set_location('Entering:'||l_proc, 5);
245 --
246 -- Ensure that the p_datetrack_mode argument is not null
247 --
248 hr_api.mandatory_arg_error
249 (p_api_name => l_proc,
250 p_argument => 'datetrack_mode',
251 p_argument_value => p_datetrack_mode);
252 --
253 -- Only perform the validation if the datetrack update mode is valid
254 --
255 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
256 --
257 --
258 -- Ensure the arguments are not null
259 --
260 hr_api.mandatory_arg_error
261 (p_api_name => l_proc,
262 p_argument => 'validation_start_date',
263 p_argument_value => p_validation_start_date);
264 --
265 hr_api.mandatory_arg_error
266 (p_api_name => l_proc,
267 p_argument => 'validation_end_date',
268 p_argument_value => p_validation_end_date);
269 --
270 End If;
271 --
272 hr_utility.set_location(' Leaving:'||l_proc, 10);
273 Exception
274 When l_integrity_error Then
275 --
276 -- A referential integrity check was violated therefore
277 -- we must error
278 --
279 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
280 fnd_message.set_token('TABLE_NAME', l_table_name);
281 fnd_message.raise_error;
282 When Others Then
283 --
284 -- An unhandled or unexpected error has occurred which
285 -- we must report
286 --
287 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
288 fnd_message.set_token('PROCEDURE', l_proc);
289 fnd_message.set_token('STEP','15');
290 fnd_message.raise_error;
291 End dt_update_validate;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |--------------------------< dt_delete_validate >--------------------------|
295 -- ----------------------------------------------------------------------------
296 -- {Start Of Comments}
297 --
298 -- Description:
299 -- This procedure is used for referential integrity of datetracked
300 -- child entities when either a datetrack DELETE or ZAP is in operation
301 -- and where there is no cascading of delete defined for this entity.
302 -- For the datetrack mode of DELETE or ZAP we must ensure that no
303 -- datetracked child rows exist between the validation start and end
304 -- dates.
305 --
306 -- Prerequisites:
307 -- This procedure is called from the delete_validate.
308 --
309 -- In Parameters:
310 --
311 -- Post Success:
312 -- Processing continues.
313 --
314 -- Post Failure:
315 -- If a row exists by determining the returning Boolean value from the
316 -- generic dt_api.rows_exist function then we must supply an error via
317 -- the use of the local exception handler l_rows_exist.
318 --
319 -- Developer Implementation Notes:
320 -- This procedure should not need maintenance unless the HR Schema model
321 -- changes.
322 --
323 -- Access Status:
324 -- Internal Row Handler Use Only.
325 --
326 -- {End Of Comments}
327 -- ----------------------------------------------------------------------------
328 Procedure dt_delete_validate
329 (p_svc_area_id in number,
330 p_datetrack_mode in varchar2,
331 p_validation_start_date in date,
332 p_validation_end_date in date) Is
333 --
334 l_proc varchar2(72) := g_package||'dt_delete_validate';
335 l_rows_exist Exception;
336 l_table_name all_tables.table_name%TYPE;
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 -- Ensure that the p_datetrack_mode argument is not null
342 --
343 hr_api.mandatory_arg_error
344 (p_api_name => l_proc,
345 p_argument => 'datetrack_mode',
346 p_argument_value => p_datetrack_mode);
347 --
348 -- Only perform the validation if the datetrack mode is either
349 -- DELETE or ZAP
350 --
351 If (p_datetrack_mode = 'DELETE' or
352 p_datetrack_mode = 'ZAP') then
353 --
354 --
355 -- Ensure the arguments are not null
356 --
357 hr_api.mandatory_arg_error
358 (p_api_name => l_proc,
359 p_argument => 'validation_start_date',
360 p_argument_value => p_validation_start_date);
361 --
362 hr_api.mandatory_arg_error
363 (p_api_name => l_proc,
364 p_argument => 'validation_end_date',
365 p_argument_value => p_validation_end_date);
366 --
367 hr_api.mandatory_arg_error
368 (p_api_name => l_proc,
369 p_argument => 'svc_area_id',
370 p_argument_value => p_svc_area_id);
371 --
372 If (dt_api.rows_exist
373 (p_base_table_name => 'ben_elig_svc_area_prte_f',
374 p_base_key_column => 'svc_area_id',
375 p_base_key_value => p_svc_area_id,
376 p_from_date => p_validation_start_date,
377 p_to_date => p_validation_end_date)) Then
378 l_table_name := 'ben_elig_svc_area_prte_f';
379 Raise l_rows_exist;
380 End If;
381 If (dt_api.rows_exist
382 (p_base_table_name => 'ben_svc_area_pstl_zip_rng_f',
383 p_base_key_column => 'svc_area_id',
384 p_base_key_value => p_svc_area_id,
385 p_from_date => p_validation_start_date,
386 p_to_date => p_validation_end_date)) Then
387 l_table_name := 'ben_svc_area_pstl_zip_rng_f';
388 Raise l_rows_exist;
389 End If;
390 If (dt_api.rows_exist
391 (p_base_table_name => 'ben_svc_area_rt_f',
392 p_base_key_column => 'svc_area_id',
393 p_base_key_value => p_svc_area_id,
394 p_from_date => p_validation_start_date,
395 p_to_date => p_validation_end_date)) Then
396 l_table_name := 'ben_svc_area_rt_f';
397 Raise l_rows_exist;
398 End If;
399 --
400 End If;
401 --
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 Exception
404 When l_rows_exist Then
405 --
406 -- A referential integrity check was violated therefore
407 -- we must error
408 --
409 ben_utility.child_exists_error(p_table_name => l_table_name);
410 --
411 When Others Then
412 --
413 -- An unhandled or unexpected error has occurred which
414 -- we must report
415 --
416 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
417 fnd_message.set_token('PROCEDURE', l_proc);
418 fnd_message.set_token('STEP','15');
419 fnd_message.raise_error;
420 End dt_delete_validate;
421 --
422 -- ----------------------------------------------------------------------------
423 -- |---------------------------< insert_validate >----------------------------|
424 -- ----------------------------------------------------------------------------
425 Procedure insert_validate
426 (p_rec in ben_sva_shd.g_rec_type,
427 p_effective_date in date,
428 p_datetrack_mode in varchar2,
429 p_validation_start_date in date,
430 p_validation_end_date in date) is
431 --
432 l_proc varchar2(72) := g_package||'insert_validate';
433 --
434 Begin
435 hr_utility.set_location('Entering:'||l_proc, 5);
436 --
437 -- Call all supporting business operations
438 --
439 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
440 --
441 chk_svc_area_id
442 (p_svc_area_id => p_rec.svc_area_id,
443 p_effective_date => p_effective_date,
444 p_object_version_number => p_rec.object_version_number);
445 --
446 chk_name_unique
447 (p_svc_area_id => p_rec.svc_area_id,
448 p_name => p_rec.name,
449 p_business_group_id => p_rec.business_group_id);
450 --
451 chk_org_unit_prdct
452 (p_svc_area_id => p_rec.svc_area_id,
453 p_org_unit_prdct => p_rec.org_unit_prdct,
454 p_effective_date => p_effective_date,
455 p_object_version_number => p_rec.object_version_number);
456 --
457 hr_utility.set_location(' Leaving:'||l_proc, 10);
458 End insert_validate;
459 --
460 -- ----------------------------------------------------------------------------
461 -- |---------------------------< update_validate >----------------------------|
465 p_effective_date in date,
462 -- ----------------------------------------------------------------------------
463 Procedure update_validate
464 (p_rec in ben_sva_shd.g_rec_type,
466 p_datetrack_mode in varchar2,
467 p_validation_start_date in date,
468 p_validation_end_date in date) is
469 --
470 l_proc varchar2(72) := g_package||'update_validate';
471 --
472 Begin
473 hr_utility.set_location('Entering:'||l_proc, 5);
474 --
475 -- Call all supporting business operations
476 --
477 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
478 --
479 chk_svc_area_id
480 (p_svc_area_id => p_rec.svc_area_id,
481 p_effective_date => p_effective_date,
482 p_object_version_number => p_rec.object_version_number);
483 --
484 chk_name_unique
485 (p_svc_area_id => p_rec.svc_area_id,
486 p_name => p_rec.name,
487 p_business_group_id => p_rec.business_group_id);
488 --
489 chk_org_unit_prdct
490 (p_svc_area_id => p_rec.svc_area_id,
491 p_org_unit_prdct => p_rec.org_unit_prdct,
492 p_effective_date => p_effective_date,
493 p_object_version_number => p_rec.object_version_number);
494 --
495 -- Call the datetrack update integrity operation
496 --
497 dt_update_validate
498 (
499 p_datetrack_mode => p_datetrack_mode,
500 p_validation_start_date => p_validation_start_date,
501 p_validation_end_date => p_validation_end_date);
502 --
503 hr_utility.set_location(' Leaving:'||l_proc, 10);
504 End update_validate;
505 --
506 -- ----------------------------------------------------------------------------
507 -- |---------------------------< delete_validate >----------------------------|
508 -- ----------------------------------------------------------------------------
509 Procedure delete_validate
510 (p_rec in ben_sva_shd.g_rec_type,
511 p_effective_date in date,
512 p_datetrack_mode in varchar2,
513 p_validation_start_date in date,
514 p_validation_end_date in date) is
515 --
516 l_proc varchar2(72) := g_package||'delete_validate';
517 --
518 Begin
519 hr_utility.set_location('Entering:'||l_proc, 5);
520 --
521 -- Call all supporting business operations
522 --
523 dt_delete_validate
524 (p_datetrack_mode => p_datetrack_mode,
525 p_validation_start_date => p_validation_start_date,
526 p_validation_end_date => p_validation_end_date,
527 p_svc_area_id => p_rec.svc_area_id);
528 --
529 hr_utility.set_location(' Leaving:'||l_proc, 10);
530 End delete_validate;
531 --
532 --
533 -- ---------------------------------------------------------------------------
534 -- |---------------------< return_legislation_code >-------------------------|
535 -- ---------------------------------------------------------------------------
536 --
537 function return_legislation_code
538 (p_svc_area_id in number) return varchar2 is
539 --
540 -- Declare cursor
541 --
542 cursor csr_leg_code is
543 select a.legislation_code
544 from per_business_groups a,
545 ben_svc_area_f b
546 where b.svc_area_id = p_svc_area_id
547 and a.business_group_id = b.business_group_id;
548 --
549 -- Declare local variables
550 --
551 l_legislation_code per_business_groups.legislation_code%type ; --varchar2(150);
552 l_proc varchar2(72) := g_package||'return_legislation_code';
553 --
554 begin
555 --
556 hr_utility.set_location('Entering:'|| l_proc, 10);
557 --
558 -- Ensure that all the mandatory parameter are not null
559 --
560 hr_api.mandatory_arg_error(p_api_name => l_proc,
561 p_argument => 'svc_area_id',
562 p_argument_value => p_svc_area_id);
563 --
564 open csr_leg_code;
565 --
566 fetch csr_leg_code into l_legislation_code;
567 --
568 if csr_leg_code%notfound then
569 --
570 close csr_leg_code;
571 --
572 -- The primary key is invalid therefore we must error
573 --
574 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
575 fnd_message.raise_error;
576 --
577 end if;
578 --
579 close csr_leg_code;
580 --
581 hr_utility.set_location(' Leaving:'|| l_proc, 20);
582 --
583 return l_legislation_code;
584 --
585 end return_legislation_code;
586 --
587 end ben_sva_bus;