1 Package Body ben_cpr_bus as
2 /* $Header: becprrhi.pkb 115.12 2002/12/13 06:21:26 hmani ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cpr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_popl_org_role_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 -- popl_org_role_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_popl_org_role_id(p_popl_org_role_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_popl_org_role_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_cpr_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_popl_org_role_id => p_popl_org_role_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_popl_org_role_id,hr_api.g_number)
55 <> ben_cpr_shd.g_old_rec.popl_org_role_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_cpr_shd.constraint_error('BEN_POPL_ORG_ROLE_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_popl_org_role_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_cpr_shd.constraint_error('BEN_POPL_ORG_ROLE_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_popl_org_role_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |-----------------------< chk_org_role_typ_cd >-------------------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the lookup value is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- popl_org_role_id PK of record being inserted or updated.
91 -- org_role_typ_cd Value of lookup code.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_org_role_typ_cd(p_popl_org_role_id in number,
106 p_org_role_typ_cd in varchar2,
107 p_popl_org_id in number,
108 p_effective_date in date,
109 p_object_version_number in number) is
110 --
111 l_proc varchar2(72) := g_package||'chk_org_role_typ_cd';
112 l_api_updating boolean;
113
114 l_plnip varchar2(1);
115 cursor c1 is
116 select 'x'
117 from ben_popl_org_f pof
118 ,ben_pl_f pln
119 Where pof.popl_org_id = p_popl_org_id
120 and p_effective_date between pof.effective_start_date
121 and pof.effective_end_date
122 and pof.pl_id = pln.pl_id
123 and p_effective_date between pln.effective_start_date
124 and pln.effective_end_date
125 and pln.pl_cd = 'MSTBPGM'; -- must be in program
126 Begin
127 --
128 hr_utility.set_location('Entering:'||l_proc, 5);
129 --
130 l_api_updating := ben_cpr_shd.api_updating
131 (p_popl_org_role_id => p_popl_org_role_id,
132 p_effective_date => p_effective_date,
133 p_object_version_number => p_object_version_number);
134 --
135 if (l_api_updating
136 and p_org_role_typ_cd
137 <> nvl(ben_cpr_shd.g_old_rec.org_role_typ_cd,hr_api.g_varchar2)
138 or not l_api_updating)
139 and p_org_role_typ_cd is not null then
140 --
141 -- check if value of lookup falls within lookup type.
142 --
143 if hr_api.not_exists_in_hr_lookups
144 (p_lookup_type => 'BEN_ORG_ROLE_TYP',
145 p_lookup_code => p_org_role_typ_cd,
146 p_effective_date => p_effective_date) then
147 --
148 -- raise error as does not exist as lookup
149 --
150 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
151 fnd_message.set_token('FIELD','p_org_role_typ_cd');
152 fnd_message.set_token('VALUE', p_org_role_typ_cd);
153 fnd_message.set_token('TYPE','BEN_ORG_ROLE_TYP');
154 fnd_message.raise_error;
155 end if;
156
157 -- This code is used to check that only program and plan-not-in-program
158 -- can be attached with a popl-org-role of POPLOWNR
159 if p_org_role_typ_cd = 'POPLOWNR' then
160 open c1;
161 fetch c1 into l_plnip;
162 if c1%found then
163 close c1;
164 -- raise error as Plan is in Program
165 fnd_message.set_name('BEN','BEN_92611_POPLOWNR_NO_PLIPS');
166 fnd_message.raise_error;
167 end if;
168 close c1;
169 end if;
170
171 end if;
172 --
173 hr_utility.set_location('Leaving:'||l_proc,10);
174 --
175 end chk_org_role_typ_cd;
176 -- --
177 --
178 -- ----------------------------------------------------------------------------
179 -- |------< chk_uniq_org_role_typ >------|
180 -- ----------------------------------------------------------------------------
181 --
182 -- Description
183 -- This procedure is used to check that only one popl_org can be created for a plan
184 -- with a popl-org-role of PCP
185 --
186 -- Pre Conditions
187 -- None.
188 --
189 -- In Parameters
190 -- p_popl_org_id
191 -- p_effective_date
192 -- Post Success
193 -- Processing continues
194 --
195 -- Post Failure
196 -- Errors handled by the procedure
197 --
198 -- Access Status
199 -- Internal table handler use only.
200 --
201
202 Procedure chk_uniq_org_role_typ
203 ( p_popl_org_id in number,
204 p_popl_org_role_id in number,
205 p_org_role_typ_cd in varchar2,
206 p_effective_date in date,
207 p_business_group_id in number
208 ) is
209
210 l_proc varchar2(72) := g_package|| ' chk_uniq_org_role_typ';
211
212 l_pl_id number;
213 l_num_of_pcps number;
214 l_plcy_r_grp ben_popl_org_f.plcy_r_grp%TYPE; -- UTF8 varchar2(30);
215
216 -- get data for this popl-org
217 cursor get_grp_and_pl_id is
218 select cpo.pl_id, cpo.plcy_r_grp
219 from ben_popl_org_f cpo
220 where cpo.popl_org_id = p_popl_org_id
221 and cpo.business_group_id = p_business_group_id
222 and p_effective_date between
223 cpo.effective_start_date and cpo.effective_end_date;
224
225 -- how many pcp role rows exist for this plan?
226 cursor num_of_pcps (p_pl_id number) is
227 select count('x')
228 from ben_popl_org_f cpo
229 ,ben_popl_org_role_f cpr
230 Where cpo.popl_org_id = cpr.popl_org_id
231 and cpo.pl_id = p_pl_id
232 and cpo.business_group_id = p_business_group_id
233 and cpr.org_role_typ_cd = 'PCP'
234 and (cpr.popl_org_role_id <> p_popl_org_role_id
235 or p_popl_org_role_id is null)
236 and p_effective_date between cpo.effective_start_date
237 and cpo.effective_end_date
238 and p_effective_date between cpr.effective_start_date
239 and cpr.effective_end_date;
240
241 Begin
242 hr_utility.set_location('Entering:'||l_proc, 5);
243
244 if p_org_role_typ_cd = 'PCP' then
245 open get_grp_and_pl_id;
246 fetch get_grp_and_pl_id into l_pl_id, l_plcy_r_grp;
247 close get_grp_and_pl_id;
248
249 if l_plcy_r_grp is null then
250 -- cannot have a pcp locator role without the product code for the
251 -- pcp's external repository filled in the policy-or-group field.
252 -- it's what the search page uses to pass to the external repository
253 -- for the plan.
254 fnd_message.set_name('BEN','BEN_92610_PCP_LOCATOR');
255 fnd_message.raise_error;
256 end if;
257
258 -- no need to check for program rows....we should not have pcp roles for
259 -- popl-orgs attached to programs.
260 if l_pl_id is not null then
261 open num_of_pcps(p_pl_id => l_pl_id);
262 fetch num_of_pcps into l_num_of_pcps;
263 close num_of_pcps;
264
265 -- should not be any rows returned from cursor
266 if l_num_of_pcps > 0 then
267 fnd_message.set_name('BEN','BEN_92578_POPL_ORG_ROLE');
268 fnd_message.raise_error;
269 end if;
270 else
271 -- can only attach pcp locator rows to plans
272 fnd_message.set_name('BEN','BEN_92609_PCP_ONLY_PLNS');
273 fnd_message.raise_error;
274 end if;
275 end if;
276
277 hr_utility.set_location('Leaving:'||l_proc, 15);
278
279 End chk_uniq_org_role_typ;
280 --
281 --
282 -- ----------------------------------------------------------------------------
283 -- |--------------------------< dt_update_validate >--------------------------|
284 -- ----------------------------------------------------------------------------
285 -- {Start Of Comments}
286 --
287 -- Description:
288 -- This procedure is used for referential integrity of datetracked
289 -- parent entities when a datetrack update operation is taking place
290 -- and where there is no cascading of update defined for this entity.
291 --
292 -- Prerequisites:
293 -- This procedure is called from the update_validate.
294 --
295 -- In Parameters:
296 --
297 -- Post Success:
298 -- Processing continues.
299 --
300 -- Post Failure:
301 --
302 -- Developer Implementation Notes:
303 -- This procedure should not need maintenance unless the HR Schema model
304 -- changes.
305 --
306 -- Access Status:
307 -- Internal Row Handler Use Only.
308 --
309 -- {End Of Comments}
310 -- ----------------------------------------------------------------------------
311 Procedure dt_update_validate
312 (
313 p_datetrack_mode in varchar2,
314 p_validation_start_date in date,
315 p_validation_end_date in date) Is
316 --
317 l_proc varchar2(72) := g_package||'dt_update_validate';
318 l_integrity_error Exception;
319 l_table_name all_tables.table_name%TYPE;
320 --
321 Begin
322 hr_utility.set_location('Entering:'||l_proc, 5);
323 --
324 -- Ensure that the p_datetrack_mode argument is not null
325 --
326 hr_api.mandatory_arg_error
327 (p_api_name => l_proc,
328 p_argument => 'datetrack_mode',
329 p_argument_value => p_datetrack_mode);
330 --
331 -- Only perform the validation if the datetrack update mode is valid
332 --
333 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
334 --
335 --
336 -- Ensure the arguments are not null
337 --
338 hr_api.mandatory_arg_error
339 (p_api_name => l_proc,
340 p_argument => 'validation_start_date',
341 p_argument_value => p_validation_start_date);
342 --
343 hr_api.mandatory_arg_error
344 (p_api_name => l_proc,
345 p_argument => 'validation_end_date',
346 p_argument_value => p_validation_end_date);
347 --
348 --
349 --
350 End If;
351 --
352 hr_utility.set_location(' Leaving:'||l_proc, 10);
353 Exception
354 When l_integrity_error Then
355 --
356 -- A referential integrity check was violated therefore
357 -- we must error
358 --
359 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
360 fnd_message.set_token('TABLE_NAME', l_table_name);
361 fnd_message.raise_error;
362 When Others Then
363 --
364 -- An unhandled or unexpected error has occurred which
365 -- we must report
366 --
367 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
368 fnd_message.set_token('PROCEDURE', l_proc);
369 fnd_message.set_token('STEP','15');
370 fnd_message.raise_error;
371 End dt_update_validate;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |--------------------------< dt_delete_validate >--------------------------|
375 -- ----------------------------------------------------------------------------
376 -- {Start Of Comments}
377 --
378 -- Description:
379 -- This procedure is used for referential integrity of datetracked
380 -- child entities when either a datetrack DELETE or ZAP is in operation
381 -- and where there is no cascading of delete defined for this entity.
382 -- For the datetrack mode of DELETE or ZAP we must ensure that no
383 -- datetracked child rows exist between the validation start and end
384 -- dates.
385 --
386 -- Prerequisites:
387 -- This procedure is called from the delete_validate.
388 --
389 -- In Parameters:
390 --
391 -- Post Success:
392 -- Processing continues.
393 --
394 -- Post Failure:
395 -- If a row exists by determining the returning Boolean value from the
396 -- generic dt_api.rows_exist function then we must supply an error via
397 -- the use of the local exception handler l_rows_exist.
398 --
399 -- Developer Implementation Notes:
403 -- Access Status:
400 -- This procedure should not need maintenance unless the HR Schema model
401 -- changes.
402 --
404 -- Internal Row Handler Use Only.
405 --
406 -- {End Of Comments}
407 -- ----------------------------------------------------------------------------
408 Procedure dt_delete_validate
409 (p_popl_org_role_id in number,
410 p_datetrack_mode in varchar2,
411 p_validation_start_date in date,
412 p_validation_end_date in date) Is
413 --
414 l_proc varchar2(72) := g_package||'dt_delete_validate';
415 l_rows_exist Exception;
416 l_table_name all_tables.table_name%TYPE;
417 --
418 Begin
419 hr_utility.set_location('Entering:'||l_proc, 5);
420 --
421 -- Ensure that the p_datetrack_mode argument is not null
422 --
423 hr_api.mandatory_arg_error
424 (p_api_name => l_proc,
425 p_argument => 'datetrack_mode',
426 p_argument_value => p_datetrack_mode);
427 --
428 -- Only perform the validation if the datetrack mode is either
429 -- DELETE or ZAP
430 --
431 If (p_datetrack_mode = 'DELETE' or
432 p_datetrack_mode = 'ZAP') then
433 --
434 --
435 -- Ensure the arguments are not null
436 --
437 hr_api.mandatory_arg_error
438 (p_api_name => l_proc,
439 p_argument => 'validation_start_date',
440 p_argument_value => p_validation_start_date);
441 --
442 hr_api.mandatory_arg_error
443 (p_api_name => l_proc,
444 p_argument => 'validation_end_date',
445 p_argument_value => p_validation_end_date);
446 --
447 hr_api.mandatory_arg_error
448 (p_api_name => l_proc,
449 p_argument => 'popl_org_role_id',
450 p_argument_value => p_popl_org_role_id);
451 --
452 End If;
453 --
454 hr_utility.set_location(' Leaving:'||l_proc, 10);
455 Exception
456 When l_rows_exist Then
457 --
458 -- A referential integrity check was violated therefore
459 -- we must error
460 --
461 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
462 fnd_message.set_token('TABLE_NAME', l_table_name);
463 fnd_message.raise_error;
464 When Others Then
465 --
466 -- An unhandled or unexpected error has occurred which
467 -- we must report
468 --
469 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
470 fnd_message.set_token('PROCEDURE', l_proc);
471 fnd_message.set_token('STEP','15');
472 fnd_message.raise_error;
473 End dt_delete_validate;
474 --
475 -- ----------------------------------------------------------------------------
476 -- |---------------------------< insert_validate >----------------------------|
477 -- ----------------------------------------------------------------------------
478 Procedure insert_validate
479 (p_rec in ben_cpr_shd.g_rec_type,
480 p_effective_date in date,
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||'insert_validate';
486 --
487 Begin
488 hr_utility.set_location('Entering:'||l_proc, 5);
489 --
490 -- Call all supporting business operations
491 --
492 --
493 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
494 --
495 chk_popl_org_role_id
496 (p_popl_org_role_id => p_rec.popl_org_role_id,
497 p_effective_date => p_effective_date,
498 p_object_version_number => p_rec.object_version_number);
499 --
500 chk_org_role_typ_cd(p_popl_org_role_id => p_rec.popl_org_role_id,
501 p_org_role_typ_cd => p_rec.org_role_typ_cd,
502 p_popl_org_id => p_rec.popl_org_id,
503 p_effective_date => p_effective_date,
504 p_object_version_number => p_rec.object_version_number);
505
506 --
507 chk_uniq_org_role_typ
508 ( p_popl_org_id => p_rec.popl_org_id,
509 p_popl_org_role_id => p_rec.popl_org_role_id,
510 p_org_role_typ_cd => p_rec.org_role_typ_cd,
511 p_business_group_id => p_rec.business_group_id,
512 p_effective_date => p_effective_date);
513
514 hr_utility.set_location(' Leaving:'||l_proc, 10);
515 End insert_validate;
516 --
517 -- ----------------------------------------------------------------------------
518 -- |---------------------------< update_validate >----------------------------|
519 -- ----------------------------------------------------------------------------
520 Procedure update_validate
521 (p_rec in ben_cpr_shd.g_rec_type,
522 p_effective_date in date,
523 p_datetrack_mode in varchar2,
524 p_validation_start_date in date,
525 p_validation_end_date in date) is
526 --
527 l_proc varchar2(72) := g_package||'update_validate';
528 --
529 Begin
530 hr_utility.set_location('Entering:'||l_proc, 5);
531 --
532 -- Call all supporting business operations
533 --
534 --
535 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
536 --
540 p_object_version_number => p_rec.object_version_number);
537 chk_popl_org_role_id
538 (p_popl_org_role_id => p_rec.popl_org_role_id,
539 p_effective_date => p_effective_date,
541 --
542 chk_org_role_typ_cd(p_popl_org_role_id => p_rec.popl_org_role_id,
543 p_org_role_typ_cd => p_rec.org_role_typ_cd,
544 p_popl_org_id => p_rec.popl_org_id,
545 p_effective_date => p_effective_date,
546 p_object_version_number => p_rec.object_version_number);
547 --
548 chk_uniq_org_role_typ
549 ( p_popl_org_id => p_rec.popl_org_id,
550 p_popl_org_role_id => p_rec.popl_org_role_id,
551 p_org_role_typ_cd => p_rec.org_role_typ_cd,
552 p_business_group_id => p_rec.business_group_id,
553 p_effective_date => p_effective_date);
554
555 -- Call the datetrack update integrity operation
556 --
557 dt_update_validate
558 (
559 p_datetrack_mode => p_datetrack_mode,
560 p_validation_start_date => p_validation_start_date,
561 p_validation_end_date => p_validation_end_date);
562 --
563 hr_utility.set_location(' Leaving:'||l_proc, 10);
564 End update_validate;
565 --
566 -- ----------------------------------------------------------------------------
567 -- |---------------------------< delete_validate >----------------------------|
568 -- ----------------------------------------------------------------------------
569 Procedure delete_validate
570 (p_rec in ben_cpr_shd.g_rec_type,
571 p_effective_date in date,
572 p_datetrack_mode in varchar2,
573 p_validation_start_date in date,
574 p_validation_end_date in date) is
575 --
576 l_proc varchar2(72) := g_package||'delete_validate';
577 --
578 Begin
579 hr_utility.set_location('Entering:'||l_proc, 5);
580 --
581 -- Call all supporting business operations
582 --
583 dt_delete_validate
584 (p_datetrack_mode => p_datetrack_mode,
585 p_validation_start_date => p_validation_start_date,
586 p_validation_end_date => p_validation_end_date,
587 p_popl_org_role_id => p_rec.popl_org_role_id);
588 --
589 hr_utility.set_location(' Leaving:'||l_proc, 10);
590 End delete_validate;
591 --
592 --
593 -- ---------------------------------------------------------------------------
594 -- |---------------------< return_legislation_code >-------------------------|
595 -- ---------------------------------------------------------------------------
596 --
597 function return_legislation_code
598 (p_popl_org_role_id in number) return varchar2 is
599 --
600 -- Declare cursor
601 --
602 cursor csr_leg_code is
603 select a.legislation_code
604 from per_business_groups a,
605 ben_popl_org_role_f b
606 where b.popl_org_role_id = p_popl_org_role_id
607 and a.business_group_id = b.business_group_id;
608 --
609 -- Declare local variables
610 --
611 l_legislation_code varchar2(150);
612 l_proc varchar2(72) := g_package||'return_legislation_code';
613 --
614 begin
615 --
616 hr_utility.set_location('Entering:'|| l_proc, 10);
617 --
618 -- Ensure that all the mandatory parameter are not null
619 --
620 hr_api.mandatory_arg_error(p_api_name => l_proc,
621 p_argument => 'popl_org_role_id',
622 p_argument_value => p_popl_org_role_id);
623 --
624 open csr_leg_code;
625 --
626 fetch csr_leg_code into l_legislation_code;
627 --
628 if csr_leg_code%notfound then
629 --
630 close csr_leg_code;
631 --
632 -- The primary key is invalid therefore we must error
633 --
634 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
635 fnd_message.raise_error;
636 --
637 end if;
638 --
639 close csr_leg_code;
640 --
641 hr_utility.set_location(' Leaving:'|| l_proc, 20);
642 --
643 return l_legislation_code;
644 --
645 end return_legislation_code;
646 --
647 end ben_cpr_bus;
[Home] [Help]