[Home] [Help]
PACKAGE BODY: APPS.BEN_VGS_BUS
Source
1 Package Body ben_vgs_bus as
2 /* $Header: bevgsrhi.pkb 120.0.12010000.2 2008/08/05 15:33:59 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_vgs_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |--------------------------< chk_pl_gd_or_svc_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 -- pl_gd_or_svc_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_pl_gd_or_svc_id(p_pl_gd_or_svc_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_pl_gd_or_svc_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_vgs_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_pl_gd_or_svc_id => p_pl_gd_or_svc_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_pl_gd_or_svc_id,hr_api.g_number)
55 <> ben_vgs_shd.g_old_rec.pl_gd_or_svc_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_vgs_shd.constraint_error('BEN_PL_GD_OR_SVC_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_pl_gd_or_svc_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_vgs_shd.constraint_error('BEN_PL_GD_OR_SVC_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_pl_gd_or_svc_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------------------------< chk_gd_or_svc_typ_id >--------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure checks that a referenced foreign key actually exists
85 -- in the referenced table.
86 --
87 -- Pre-Conditions
88 -- None.
89 --
90 -- In Parameters
91 -- p_pl_gd_or_svc_id PK
92 -- p_gd_or_svc_typ_id ID of FK column
93 -- p_effective_date session date
94 -- p_object_version_number object version number
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error raised.
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_gd_or_svc_typ_id (p_pl_gd_or_svc_id in number,
106 p_gd_or_svc_typ_id in number,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_gd_or_svc_typ_id';
111 l_api_updating boolean;
112 l_dummy varchar2(1);
113 --
114 cursor c1 is
115 select null
116 from ben_gd_or_svc_typ a
117 where a.gd_or_svc_typ_id = p_gd_or_svc_typ_id;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_vgs_shd.api_updating
124 (p_pl_gd_or_svc_id => p_pl_gd_or_svc_id,
125 p_effective_date => p_effective_date,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_gd_or_svc_typ_id,hr_api.g_number)
130 <> nvl(ben_vgs_shd.g_old_rec.gd_or_svc_typ_id,hr_api.g_number)
131 or not l_api_updating) then
132 --
133 -- check if gd_or_svc_typ_id value exists in ben_gd_or_svc_typ table
134 --
135 open c1;
136 --
137 fetch c1 into l_dummy;
138 if c1%notfound then
139 --
140 close c1;
141 --
142 -- raise error as FK does not relate to PK in ben_gd_or_svc_typ
143 -- table.
144 --
145 ben_vgs_shd.constraint_error('BEN_PL_GD_OR_SVC_FK1');
146 --
147 end if;
148 --
149 close c1;
150 --
151 end if;
152 --
153 hr_utility.set_location('Leaving:'||l_proc,10);
154 --
155 End chk_gd_or_svc_typ_id;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |-------------------------< chk_alw_rcrrg_clms_flag >----------------------|
159 -- ----------------------------------------------------------------------------
160 --
161 -- Description
162 -- This procedure is used to check that the lookup value is valid.
163 --
164 -- Pre Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- pl_gd_or_svc_id PK of record being inserted or updated.
169 -- alw_rcrrg_clms_flag Value of lookup code.
170 -- effective_date effective date
171 -- object_version_number Object version number of record being
172 -- inserted or updated.
173 --
174 -- Post Success
175 -- Processing continues
176 --
177 -- Post Failure
178 -- Error handled by procedure
179 --
180 -- Access Status
181 -- Internal table handler use only.
182 --
183 Procedure chk_alw_rcrrg_clms_flag(p_pl_gd_or_svc_id in number,
184 p_alw_rcrrg_clms_flag in varchar2,
185 p_effective_date in date,
186 p_object_version_number in number) is
187 --
188 l_proc varchar2(72) := g_package||'chk_alw_rcrrg_clms_flag';
189 l_api_updating boolean;
190 --
191 Begin
192 --
193 hr_utility.set_location('Entering:'||l_proc, 5);
194 --
195 l_api_updating := ben_vgs_shd.api_updating
196 (p_pl_gd_or_svc_id => p_pl_gd_or_svc_id,
197 p_effective_date => p_effective_date,
198 p_object_version_number => p_object_version_number);
199 --
200 if (l_api_updating
201 and p_alw_rcrrg_clms_flag
202 <> nvl(ben_vgs_shd.g_old_rec.alw_rcrrg_clms_flag,hr_api.g_varchar2)
203 or not l_api_updating)
204 and p_alw_rcrrg_clms_flag is not null then
205 --
206 -- check if value of lookup falls within lookup type.
207 --
208 if hr_api.not_exists_in_hr_lookups
209 (p_lookup_type => 'YES_NO',
210 p_lookup_code => p_alw_rcrrg_clms_flag,
211 p_effective_date => p_effective_date) then
212 --
213 -- raise error as does not exist as lookup
214 --
215 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
216 hr_utility.raise_error;
217 --
218 end if;
219 --
220 end if;
221 --
222 hr_utility.set_location('Leaving:'||l_proc,10);
223 --
224 end chk_alw_rcrrg_clms_flag;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |-------------------------< chk_gd_svc_recd_basis_cd >----------------------|
228 -- ----------------------------------------------------------------------------
229 --
230 -- Description
231 -- This procedure is used to check that the lookup value is valid.
232 -- If it is valid then the dependent columns are populated or not.
233 -- Pre Conditions
234 -- None.
235 --
236 -- In Parameters
237 -- pl_gd_or_svc_id PK of record being inserted or updated.
238 -- p_gd_svc_recd_basis_cd Value of lookup code.
239 -- effective_date effective date
240 -- object_version_number Object version number of record being
241 -- inserted or updated.
242 --
243 -- Post Success
244 -- Processing continues
245 --
246 -- Post Failure
247 -- Error handled by procedure
248 --
249 -- Access Status
250 -- Internal table handler use only.
251 --
252 Procedure chk_gd_svc_recd_basis_cd(p_pl_gd_or_svc_id in number,
253 p_gd_svc_recd_basis_cd in varchar2,
254 p_gd_svc_recd_basis_dt in date ,
255 p_gd_svc_recd_basis_mo in number ,
256 p_effective_date in date,
257 p_object_version_number in number) is
258 --
259 l_proc varchar2(72) := g_package||'chk_gd_svc_recd_basis_cd';
260 l_api_updating boolean;
261 --
262 Begin
263 --
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 l_api_updating := ben_vgs_shd.api_updating
267 (p_pl_gd_or_svc_id => p_pl_gd_or_svc_id,
268 p_effective_date => p_effective_date,
269 p_object_version_number => p_object_version_number);
270 --
271
272 if (l_api_updating
273 and (p_gd_svc_recd_basis_cd
274 <> nvl(ben_vgs_shd.g_old_rec.gd_svc_recd_basis_cd,hr_api.g_varchar2)
275 or p_gd_svc_recd_basis_dt
276 <> nvl(ben_vgs_shd.g_old_rec.gd_svc_recd_basis_dt,hr_api.g_date)
277 or p_gd_svc_recd_basis_mo
278 <> nvl(ben_vgs_shd.g_old_rec.gd_svc_recd_basis_mo,hr_api.g_number)
279 )
280 or not l_api_updating)
281 and ( p_gd_svc_recd_basis_cd is not null
282 and (p_gd_svc_recd_basis_dt is not null
283 or p_gd_svc_recd_basis_mo is not null) )
284 then
285 --
286 -- check if value of lookup falls within lookup type.
287 --
288 hr_utility.set_location('l_api_updating :'||'TRUE', 7);
289 if hr_api.not_exists_in_hr_lookups
290 (p_lookup_type => 'BEN_GD_SVC_RECD_BASIS',
291 p_lookup_code => p_gd_svc_recd_basis_cd,
292 p_effective_date => p_effective_date) then
293 --
294 -- raise error as does not exist as lookup
295 --
296 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
297 hr_utility.raise_error;
298 --
299 end if;
300 --
301 if p_gd_svc_recd_basis_cd = 'DATE' then
302 if p_gd_svc_recd_basis_dt is null then
303 --
304 hr_utility.set_message(801,'BEN_93100_BASIS_DT_MO_INVLD');
305 hr_utility.set_message_token('FIELD', 'Date');
306 hr_utility.set_message_token('CODE','Specified Date');
307 hr_utility.raise_error;
308 --
309 else
310 if p_gd_svc_recd_basis_mo is not null then
311 --
312 hr_utility.set_message(801,'BEN_93100_BASIS_DT_MO_INVLD');
313 hr_utility.set_message_token('FIELD', 'Date');
314 hr_utility.set_message_token('CODE',' Specified Date ');
315 hr_utility.raise_error;
316 --
317 end if;
318 end if;
319 else
320 if p_gd_svc_recd_basis_mo is null then
321 --
322 hr_utility.set_message(801,'BEN_93100_BASIS_DT_MO_INVLD');
323 hr_utility.set_message_token('FIELD', 'Month');
324 hr_utility.set_message_token('CODE',' No of Months from Incurred Date / No of Months from the Plan Year End ' );
325 hr_utility.raise_error;
326 --
327 else
328 if p_gd_svc_recd_basis_dt is not null then
329 --
330 hr_utility.set_message(801,'BEN_93100_BASIS_DT_MO_INVLD');
331 hr_utility.set_message_token('FIELD', 'Month');
332 hr_utility.set_message_token('CODE',' No of Months from Incurred Date / No of Months from the Plan Year End ' );
333 hr_utility.raise_error;
334 --
335 end if;
336 end if;
337 end if;
338 --
339 end if;
340 --
341 hr_utility.set_location('Leaving:'||l_proc,10);
342 --
343 end chk_gd_svc_recd_basis_cd;
344 -- ----------------------------------------------------------------------------
345 -- |--------------------------< dt_update_validate >--------------------------|
346 -- ----------------------------------------------------------------------------
347 -- {Start Of Comments}
348 --
349 -- Description:
350 -- This procedure is used for referential integrity of datetracked
351 -- parent entities when a datetrack update operation is taking place
352 -- and where there is no cascading of update defined for this entity.
353 --
354 -- Prerequisites:
355 -- This procedure is called from the update_validate.
356 --
357 -- In Parameters:
358 --
359 -- Post Success:
360 -- Processing continues.
361 --
362 -- Post Failure:
363 --
364 -- Developer Implementation Notes:
365 -- This procedure should not need maintenance unless the HR Schema model
366 -- changes.
367 --
368 -- Access Status:
369 -- Internal Row Handler Use Only.
370 --
371 -- {End Of Comments}
372 -- ----------------------------------------------------------------------------
373 Procedure dt_update_validate
374 (p_pl_id in number default hr_api.g_number,
375 p_datetrack_mode in varchar2,
376 p_validation_start_date in date,
377 p_validation_end_date in date) Is
378 --
379 l_proc varchar2(72) := g_package||'dt_update_validate';
380 l_integrity_error Exception;
381 l_table_name all_tables.table_name%TYPE;
382 --
383 Begin
384 hr_utility.set_location('Entering:'||l_proc, 5);
385 --
386 -- Ensure that the p_datetrack_mode argument is not null
387 --
388 hr_api.mandatory_arg_error
389 (p_api_name => l_proc,
390 p_argument => 'datetrack_mode',
391 p_argument_value => p_datetrack_mode);
392 --
393 -- Only perform the validation if the datetrack update mode is valid
394 --
395 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
396 --
397 --
398 -- Ensure the arguments are not null
399 --
400 hr_api.mandatory_arg_error
401 (p_api_name => l_proc,
402 p_argument => 'validation_start_date',
403 p_argument_value => p_validation_start_date);
404 --
405 hr_api.mandatory_arg_error
406 (p_api_name => l_proc,
407 p_argument => 'validation_end_date',
408 p_argument_value => p_validation_end_date);
409 --
410 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
411 NOT (dt_api.check_min_max_dates
412 (p_base_table_name => 'ben_pl_f',
413 p_base_key_column => 'pl_id',
414 p_base_key_value => p_pl_id,
415 p_from_date => p_validation_start_date,
416 p_to_date => p_validation_end_date))) Then
417 l_table_name := 'ben_pl_f';
418 Raise l_integrity_error;
419 End If;
420 --
421 End If;
422 --
423 hr_utility.set_location(' Leaving:'||l_proc, 10);
424 Exception
425 When l_integrity_error Then
426 --
427 -- A referential integrity check was violated therefore
428 -- we must error
429 --
430 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
431 hr_utility.set_message_token('TABLE_NAME', l_table_name);
432 hr_utility.raise_error;
433 When Others Then
434 --
435 -- An unhandled or unexpected error has occurred which
436 -- we must report
437 --
438 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
439 hr_utility.set_message_token('PROCEDURE', l_proc);
440 hr_utility.set_message_token('STEP','15');
441 hr_utility.raise_error;
442 End dt_update_validate;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |--------------------------< dt_delete_validate >--------------------------|
446 -- ----------------------------------------------------------------------------
447 -- {Start Of Comments}
448 --
449 -- Description:
450 -- This procedure is used for referential integrity of datetracked
451 -- child entities when either a datetrack DELETE or ZAP is in operation
452 -- and where there is no cascading of delete defined for this entity.
453 -- For the datetrack mode of DELETE or ZAP we must ensure that no
454 -- datetracked child rows exist between the validation start and end
455 -- dates.
456 --
457 -- Prerequisites:
458 -- This procedure is called from the delete_validate.
459 --
460 -- In Parameters:
461 --
462 -- Post Success:
463 -- Processing continues.
464 --
465 -- Post Failure:
466 -- If a row exists by determining the returning Boolean value from the
467 -- generic dt_api.rows_exist function then we must supply an error via
468 -- the use of the local exception handler l_rows_exist.
469 --
470 -- Developer Implementation Notes:
471 -- This procedure should not need maintenance unless the HR Schema model
472 -- changes.
473 --
474 -- Access Status:
475 -- Internal Row Handler Use Only.
476 --
477 -- {End Of Comments}
478 -- ----------------------------------------------------------------------------
479 Procedure dt_delete_validate
480 (p_pl_gd_or_svc_id in number,
481 p_datetrack_mode in varchar2,
482 p_validation_start_date in date,
483 p_validation_end_date in date) Is
484 --
485 l_proc varchar2(72) := g_package||'dt_delete_validate';
486 l_rows_exist Exception;
487 l_table_name all_tables.table_name%TYPE;
488 --
489 Begin
490 hr_utility.set_location('Entering:'||l_proc, 5);
491 --
492 -- Ensure that the p_datetrack_mode argument is not null
493 --
494 hr_api.mandatory_arg_error
495 (p_api_name => l_proc,
496 p_argument => 'datetrack_mode',
497 p_argument_value => p_datetrack_mode);
498 --
499 -- Only perform the validation if the datetrack mode is either
500 -- DELETE or ZAP
501 --
502 If (p_datetrack_mode = 'DELETE' or
503 p_datetrack_mode = 'ZAP') then
504 --
505 --
506 -- Ensure the arguments are not null
507 --
508 hr_api.mandatory_arg_error
509 (p_api_name => l_proc,
510 p_argument => 'validation_start_date',
511 p_argument_value => p_validation_start_date);
512 --
513 hr_api.mandatory_arg_error
514 (p_api_name => l_proc,
515 p_argument => 'validation_end_date',
516 p_argument_value => p_validation_end_date);
517 --
518 hr_api.mandatory_arg_error
519 (p_api_name => l_proc,
520 p_argument => 'pl_gd_or_svc_id',
521 p_argument_value => p_pl_gd_or_svc_id);
522 --
523 If (dt_api.rows_exist
524 (p_base_table_name => 'ben_pl_gd_r_svc_ctfn_f',
525 p_base_key_column => 'pl_gd_or_svc_id',
526 p_base_key_value => p_pl_gd_or_svc_id,
527 p_from_date => p_validation_start_date,
528 p_to_date => p_validation_end_date)) Then
529 l_table_name := 'ben_pl_gd_r_svc_ctfn_f';
530 Raise l_rows_exist;
531 End If;
532 --
533 End If;
534 --
535 hr_utility.set_location(' Leaving:'||l_proc, 10);
536 Exception
537 When l_rows_exist Then
538 --
539 -- A referential integrity check was violated therefore
540 -- we must error
541 --
542 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
543 hr_utility.set_message_token('TABLE_NAME', l_table_name);
544 hr_utility.raise_error;
545 When Others Then
546 --
547 -- An unhandled or unexpected error has occurred which
548 -- we must report
549 --
550 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
551 hr_utility.set_message_token('PROCEDURE', l_proc);
552 hr_utility.set_message_token('STEP','15');
553 hr_utility.raise_error;
554 End dt_delete_validate;
555 --
556 -- ----------------------------------------------------------------------------
557 -- |---------------------------< insert_validate >----------------------------|
558 -- ----------------------------------------------------------------------------
559 Procedure insert_validate
560 (p_rec in ben_vgs_shd.g_rec_type,
561 p_effective_date in date,
562 p_datetrack_mode in varchar2,
563 p_validation_start_date in date,
564 p_validation_end_date in date) is
565 --
566 l_proc varchar2(72) := g_package||'insert_validate';
567 --
568 Begin
569 hr_utility.set_location('Entering:'||l_proc, 5);
570 --
571 -- Call all supporting business operations
572 --
573 --
574 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
575 --
576 chk_pl_gd_or_svc_id
577 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
578 p_effective_date => p_effective_date,
579 p_object_version_number => p_rec.object_version_number);
580 --
581 chk_gd_or_svc_typ_id
582 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
583 p_gd_or_svc_typ_id => p_rec.gd_or_svc_typ_id,
584 p_effective_date => p_effective_date,
585 p_object_version_number => p_rec.object_version_number);
586 --
587 chk_alw_rcrrg_clms_flag
588 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
589 p_alw_rcrrg_clms_flag => p_rec.alw_rcrrg_clms_flag,
590 p_effective_date => p_effective_date,
591 p_object_version_number => p_rec.object_version_number);
592 --
593 chk_gd_svc_recd_basis_cd(p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
594 p_gd_svc_recd_basis_cd => p_rec.gd_svc_recd_basis_cd,
595 p_gd_svc_recd_basis_dt => p_rec.gd_svc_recd_basis_dt,
596 p_gd_svc_recd_basis_mo => p_rec.gd_svc_recd_basis_mo ,
597 p_effective_date => p_effective_date,
598 p_object_version_number => p_rec.object_version_number);
599 hr_utility.set_location(' Leaving:'||l_proc, 10);
600 End insert_validate;
601 --
602 -- ----------------------------------------------------------------------------
603 -- |---------------------------< update_validate >----------------------------|
604 -- ----------------------------------------------------------------------------
605 Procedure update_validate
606 (p_rec in ben_vgs_shd.g_rec_type,
607 p_effective_date in date,
608 p_datetrack_mode in varchar2,
609 p_validation_start_date in date,
610 p_validation_end_date in date) is
611 --
612 l_proc varchar2(72) := g_package||'update_validate';
613 --
614 Begin
615 hr_utility.set_location('Entering:'||l_proc, 5);
616 --
617 -- Call all supporting business operations
618 --
619 --
620 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
621 --
622 chk_pl_gd_or_svc_id
623 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
624 p_effective_date => p_effective_date,
625 p_object_version_number => p_rec.object_version_number);
626 --
627 chk_gd_or_svc_typ_id
628 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
629 p_gd_or_svc_typ_id => p_rec.gd_or_svc_typ_id,
630 p_effective_date => p_effective_date,
631 p_object_version_number => p_rec.object_version_number);
632 --
633 chk_alw_rcrrg_clms_flag
634 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
635 p_alw_rcrrg_clms_flag => p_rec.alw_rcrrg_clms_flag,
636 p_effective_date => p_effective_date,
637 p_object_version_number => p_rec.object_version_number);
638 --
639 chk_gd_svc_recd_basis_cd
640 (p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id,
641 p_gd_svc_recd_basis_cd => p_rec.gd_svc_recd_basis_cd,
642 p_gd_svc_recd_basis_dt => p_rec.gd_svc_recd_basis_dt,
643 p_gd_svc_recd_basis_mo => p_rec.gd_svc_recd_basis_mo ,
644 p_effective_date => p_effective_date,
645 p_object_version_number => p_rec.object_version_number);
646 --
647 -- Call the datetrack update integrity operation
648 --
649 dt_update_validate
650 (p_pl_id => p_rec.pl_id,
651 p_datetrack_mode => p_datetrack_mode,
652 p_validation_start_date => p_validation_start_date,
653 p_validation_end_date => p_validation_end_date);
654 --
655 hr_utility.set_location(' Leaving:'||l_proc, 10);
656 End update_validate;
657 --
658 -- ----------------------------------------------------------------------------
659 -- |---------------------------< delete_validate >----------------------------|
660 -- ----------------------------------------------------------------------------
661 Procedure delete_validate
662 (p_rec in ben_vgs_shd.g_rec_type,
663 p_effective_date in date,
664 p_datetrack_mode in varchar2,
665 p_validation_start_date in date,
666 p_validation_end_date in date) is
667 --
668 l_proc varchar2(72) := g_package||'delete_validate';
669 --
670 Begin
671 hr_utility.set_location('Entering:'||l_proc, 5);
672 --
673 -- Call all supporting business operations
674 --
675 dt_delete_validate
676 (p_datetrack_mode => p_datetrack_mode,
677 p_validation_start_date => p_validation_start_date,
678 p_validation_end_date => p_validation_end_date,
679 p_pl_gd_or_svc_id => p_rec.pl_gd_or_svc_id);
680 --
681 hr_utility.set_location(' Leaving:'||l_proc, 10);
682 End delete_validate;
683 --
684 --
685 -- ---------------------------------------------------------------------------
686 -- |---------------------< return_legislation_code >-------------------------|
687 -- ---------------------------------------------------------------------------
688 --
689 function return_legislation_code
690 (p_pl_gd_or_svc_id in number) return varchar2 is
691 --
692 -- Declare cursor
693 --
694 cursor csr_leg_code is
695 select a.legislation_code
696 from per_business_groups a,
697 ben_pl_gd_or_svc_f b
698 where b.pl_gd_or_svc_id = p_pl_gd_or_svc_id
699 and a.business_group_id = b.business_group_id;
700 --
701 -- Declare local variables
702 --
703 l_legislation_code varchar2(150);
704 l_proc varchar2(72) := g_package||'return_legislation_code';
705 --
706 begin
707 --
708 hr_utility.set_location('Entering:'|| l_proc, 10);
709 --
710 -- Ensure that all the mandatory parameter are not null
711 --
712 hr_api.mandatory_arg_error(p_api_name => l_proc,
713 p_argument => 'pl_gd_or_svc_id',
714 p_argument_value => p_pl_gd_or_svc_id);
715 --
716 open csr_leg_code;
717 --
718 fetch csr_leg_code into l_legislation_code;
719 --
720 if csr_leg_code%notfound then
721 --
722 close csr_leg_code;
723 --
724 -- The primary key is invalid therefore we must error
725 --
726 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
727 hr_utility.raise_error;
728 --
729 end if;
730 --
731 close csr_leg_code;
732 --
733 hr_utility.set_location(' Leaving:'|| l_proc, 20);
734 --
735 return l_legislation_code;
736 --
737 end return_legislation_code;
738 --
739 end ben_vgs_bus;