[Home] [Help]
PACKAGE BODY: APPS.PQP_DET_BUS
Source
1 Package Body pqp_det_bus as
2 /* $Header: pqdetrhi.pkb 115.8 2003/02/17 22:14:03 tmehra ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqp_det_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_analyzed_data_details_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 -- analyzed_data_details_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_analyzed_data_details_id(p_analyzed_data_details_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_analyzed_data_details_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqp_det_shd.api_updating
47 (p_analyzed_data_details_id => p_analyzed_data_details_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_analyzed_data_details_id,hr_api.g_number)
52 <> pqp_det_shd.g_old_rec.analyzed_data_details_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqp_det_shd.constraint_error('PQP_ANALYZED_ALIEN_DETAILS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_analyzed_data_details_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqp_det_shd.constraint_error('PQP_ANALYZED_ALIEN_DETAILS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_analyzed_data_details_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_analyzed_data_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_analyzed_data_details_id PK
89 -- p_analyzed_data_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_analyzed_data_id (p_analyzed_data_details_id in number,
102 p_analyzed_data_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_analyzed_data_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqp_analyzed_alien_data a
112 where a.analyzed_data_id = p_analyzed_data_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqp_det_shd.api_updating
119 (p_analyzed_data_details_id => p_analyzed_data_details_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_analyzed_data_id,hr_api.g_number)
124 <> nvl(pqp_det_shd.g_old_rec.analyzed_data_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if analyzed_data_id value exists in pqp_analyzed_alien_data 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 pqp_analyzed_alien_data
137 -- table.
138 --
139 pqp_det_shd.constraint_error('PQP_ANALYZED_ALIEN_DATA_FK1');
140 --
141 end if;
142 --
143 close c1;
147 hr_utility.set_location('Leaving:'||l_proc,10);
144 --
145 end if;
146 --
148 --
149 End chk_analyzed_data_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_retro_lose_ben_amt_flag >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure is used to check that the lookup value is valid.
157 --
158 -- Pre Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- analyzed_data_details_id PK of record being inserted or updated.
163 -- retro_lose_ben_amt_flag Value of lookup code.
164 -- effective_date effective date
165 -- object_version_number Object version number of record being
166 -- inserted or updated.
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error handled by procedure
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_retro_lose_ben_amt_flag(p_analyzed_data_details_id in number,
178 p_retro_lose_ben_amt_flag in varchar2,
179 p_effective_date in date,
180 p_object_version_number in number) is
181 --
182 l_proc varchar2(72) := g_package||'chk_retro_lose_ben_amt_flag';
183 l_api_updating boolean;
184 --
185 Begin
186 --
187 hr_utility.set_location('Entering:'||l_proc, 5);
188 --
189 l_api_updating := pqp_det_shd.api_updating
190 (p_analyzed_data_details_id => p_analyzed_data_details_id,
191 p_object_version_number => p_object_version_number);
192 --
193 if (l_api_updating
194 and p_retro_lose_ben_amt_flag
195 <> nvl(pqp_det_shd.g_old_rec.retro_lose_ben_amt_flag,hr_api.g_varchar2)
196 or not l_api_updating)
197 and p_retro_lose_ben_amt_flag is not null then
198 --
199 -- check if value of lookup falls within lookup type.
200 --
201 if hr_api.not_exists_in_hr_lookups
202 (p_lookup_type => 'PQH_YES_NO' ,
203 p_lookup_code => p_retro_lose_ben_amt_flag,
204 p_effective_date => p_effective_date) then
205 --
206 -- raise error as does not exist as lookup
207 --
208 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
209 hr_utility.set_message_token('COLUMN','retro_lose_ben_amt_flag' );
210 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
211 hr_utility.raise_error;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 end chk_retro_lose_ben_amt_flag;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |------< chk_treaty_ben_allowed_flag >------|
223 -- ----------------------------------------------------------------------------
224 --
225 -- Description
226 -- This procedure is used to check that the lookup value is valid.
227 --
228 -- Pre Conditions
229 -- None.
230 --
231 -- In Parameters
232 -- analyzed_data_details_id PK of record being inserted or updated.
233 -- treaty_ben_allowed_flag Value of lookup code.
234 -- effective_date effective date
235 -- object_version_number Object version number of record being
236 -- inserted or updated.
237 --
238 -- Post Success
239 -- Processing continues
240 --
241 -- Post Failure
242 -- Error handled by procedure
243 --
244 -- Access Status
245 -- Internal table handler use only.
246 --
247 Procedure chk_treaty_ben_allowed_flag(p_analyzed_data_details_id in number,
248 p_treaty_ben_allowed_flag in varchar2,
249 p_effective_date in date,
250 p_object_version_number in number) is
251 --
252 l_proc varchar2(72) := g_package||'chk_treaty_ben_allowed_flag';
253 l_api_updating boolean;
254 --
255 Begin
256 --
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 l_api_updating := pqp_det_shd.api_updating
260 (p_analyzed_data_details_id => p_analyzed_data_details_id,
261 p_object_version_number => p_object_version_number);
262 --
263 if (l_api_updating
264 and p_treaty_ben_allowed_flag
265 <> nvl(pqp_det_shd.g_old_rec.treaty_ben_allowed_flag,hr_api.g_varchar2)
266 or not l_api_updating)
267 and p_treaty_ben_allowed_flag is not null then
268 --
269 -- check if value of lookup falls within lookup type.
270 --
271 if hr_api.not_exists_in_hr_lookups
272 (p_lookup_type => 'PQH_YES_NO' ,
273 p_lookup_code => p_treaty_ben_allowed_flag,
274 p_effective_date => p_effective_date) then
275 --
276 -- raise error as does not exist as lookup
277 --
278 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
279 hr_utility.set_message_token('COLUMN','treaty_ben_allowed_flag' );
280 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
281 hr_utility.raise_error;
282 --
283 end if;
284 --
285 end if;
289 end chk_treaty_ben_allowed_flag;
286 --
287 hr_utility.set_location('Leaving:'||l_proc,10);
288 --
290 --
291 -- ----------------------------------------------------------------------------
292 -- |------< chk_addl_withholding_flag >------|
293 -- ----------------------------------------------------------------------------
294 --
295 -- Description
296 -- This procedure is used to check that the lookup value is valid.
297 --
298 -- Pre Conditions
299 -- None.
300 --
301 -- In Parameters
302 -- analyzed_data_details_id PK of record being inserted or updated.
303 -- addl_withholding_flag Value of lookup code.
304 -- effective_date effective date
305 -- object_version_number Object version number of record being
306 -- inserted or updated.
307 --
308 -- Post Success
309 -- Processing continues
310 --
311 -- Post Failure
312 -- Error handled by procedure
313 --
314 -- Access Status
315 -- Internal table handler use only.
316 --
317 Procedure chk_addl_withholding_flag(p_analyzed_data_details_id in number,
318 p_addl_withholding_flag in varchar2,
319 p_effective_date in date,
320 p_object_version_number in number) is
321 --
322 l_proc varchar2(72) := g_package||'chk_addl_withholding_flag';
323 l_api_updating boolean;
324 --
325 Begin
326 --
327 hr_utility.set_location('Entering:'||l_proc, 5);
328 --
329 l_api_updating := pqp_det_shd.api_updating
330 (p_analyzed_data_details_id => p_analyzed_data_details_id,
331 p_object_version_number => p_object_version_number);
332 --
333 if (l_api_updating
334 and p_addl_withholding_flag
335 <> nvl(pqp_det_shd.g_old_rec.addl_withholding_flag,hr_api.g_varchar2)
336 or not l_api_updating)
337 and p_addl_withholding_flag is not null then
338 --
339 -- check if value of lookup falls within lookup type.
340 --
341 if hr_api.not_exists_in_hr_lookups
342 (p_lookup_type => 'PQH_YES_NO' ,
343 p_lookup_code => p_addl_withholding_flag,
344 p_effective_date => p_effective_date) then
345 --
346 -- raise error as does not exist as lookup
347 --
348 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
349 hr_utility.set_message_token('COLUMN','addl_withholding_flag' );
350 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
351 hr_utility.raise_error;
352 --
353 end if;
354 --
355 end if;
356 --
357 hr_utility.set_location('Leaving:'||l_proc,10);
358 --
359 end chk_addl_withholding_flag;
360 --
361 -- ----------------------------------------------------------------------------
362 -- |------< chk_retro_lose_ben_date_flag >------|
363 -- ----------------------------------------------------------------------------
364 --
365 -- Description
366 -- This procedure is used to check that the lookup value is valid.
367 --
368 -- Pre Conditions
369 -- None.
370 --
371 -- In Parameters
372 -- analyzed_data_details_id PK of record being inserted or updated.
373 -- retro_lose_ben_date_flag Value of lookup code.
374 -- effective_date effective date
375 -- object_version_number Object version number of record being
376 -- inserted or updated.
377 --
378 -- Post Success
379 -- Processing continues
380 --
381 -- Post Failure
382 -- Error handled by procedure
383 --
384 -- Access Status
385 -- Internal table handler use only.
386 --
387 Procedure chk_retro_lose_ben_date_flag(p_analyzed_data_details_id in number,
388 p_retro_lose_ben_date_flag in varchar2,
389 p_effective_date in date,
390 p_object_version_number in number) is
391 --
392 l_proc varchar2(72) := g_package||'chk_retro_lose_ben_date_flag';
393 l_api_updating boolean;
394 --
395 Begin
396 --
397 hr_utility.set_location('Entering:'||l_proc, 5);
398 --
399 l_api_updating := pqp_det_shd.api_updating
400 (p_analyzed_data_details_id => p_analyzed_data_details_id,
401 p_object_version_number => p_object_version_number);
402 --
403 if (l_api_updating
404 and p_retro_lose_ben_date_flag
405 <> nvl(pqp_det_shd.g_old_rec.retro_lose_ben_date_flag,hr_api.g_varchar2)
406 or not l_api_updating)
407 and p_retro_lose_ben_date_flag is not null then
408 --
409 -- check if value of lookup falls within lookup type.
410 --
411 if hr_api.not_exists_in_hr_lookups
412 (p_lookup_type => 'PQH_YES_NO' ,
413 p_lookup_code => p_retro_lose_ben_date_flag,
414 p_effective_date => p_effective_date) then
415 --
416 -- raise error as does not exist as lookup
417 --
418 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
419 hr_utility.set_message_token('COLUMN','retro_lose_ben_date_flag' );
420 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
421 hr_utility.raise_error;
422 --
423 end if;
424 --
425 end if;
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 end chk_retro_lose_ben_date_flag;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |------< chk_student_exempt_from_ss >------|
433 -- ----------------------------------------------------------------------------
434 --
435 -- Description
436 -- This procedure is used to check that the lookup value is valid.
437 --
438 -- Pre Conditions
439 -- None.
440 --
441 -- In Parameters
442 -- analyzed_data_details_id PK of record being inserted or updated.
443 -- student_exempt_from_ss Value of lookup code.
444 -- effective_date effective date
445 -- object_version_number Object version number of record being
446 -- inserted or updated.
447 --
448 -- Post Success
449 -- Processing continues
450 --
451 -- Post Failure
452 -- Error handled by procedure
453 --
454 -- Access Status
455 -- Internal table handler use only.
456 --
457 PROCEDURE chk_student_exempt_from_ss(p_analyzed_data_details_id in number ,
458 p_student_exempt_from_ss in varchar2 ,
459 p_effective_date in date ,
460 p_object_version_number in number ) IS
461 --
462 l_proc VARCHAR2(72) := g_package||'chk_student_exempt_from_ss';
463 l_api_updating BOOLEAN;
464 --
465 BEGIN
466 --
467 hr_utility.set_location('Entering:'||l_proc, 5);
468 --
469 l_api_updating := pqp_det_shd.api_updating
470 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
471 p_object_version_number => p_object_version_number);
472 --
473 IF (l_api_updating AND
474 p_student_exempt_from_ss
475 <> NVL(pqp_det_shd.g_old_rec.student_exempt_from_ss,hr_api.g_varchar2)
476 OR NOT l_api_updating)
477 AND p_student_exempt_from_ss IS NOT NULL THEN
478 --
479 -- check if value of lookup falls within lookup type.
480 --
481 IF hr_api.not_exists_in_hr_lookups
482 (p_lookup_type => 'PQH_YES_NO' ,
483 p_lookup_code => p_student_exempt_from_ss ,
484 p_effective_date => p_effective_date ) THEN
485 --
486 -- raise error as does not exist as lookup
487 --
488 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
489 hr_utility.set_message_token('COLUMN','student_exempt_from_ss' );
490 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
491 hr_utility.raise_error;
492 --
493 END IF;
494 --
495 END IF;
496 --
497 hr_utility.set_location('Leaving:'||l_proc,10);
498 --
499 END chk_student_exempt_from_ss;
500 --
501 -- ----------------------------------------------------------------------------
502 -- |------< chk_nra_exempt_from_ss >------|
503 -- ----------------------------------------------------------------------------
504 --
505 -- Description
506 -- This procedure is used to check that the lookup value is valid.
507 --
508 -- Pre Conditions
509 -- None.
510 --
511 -- In Parameters
512 -- analyzed_data_details_id PK of record being inserted or updated.
513 -- nra_exempt_from_ss Value of lookup code.
514 -- effective_date effective date
515 -- object_version_number Object version number of record being
516 -- inserted or updated.
517 --
518 -- Post Success
519 -- Processing continues
520 --
521 -- Post Failure
522 -- Error handled by procedure
523 --
524 -- Access Status
525 -- Internal table handler use only.
526 --
527 PROCEDURE chk_nra_exempt_from_ss(p_analyzed_data_details_id in number ,
528 p_nra_exempt_from_ss in varchar2 ,
529 p_effective_date in date ,
530 p_object_version_number in number ) IS
531 --
532 l_proc VARCHAR2(72) := g_package||'chk_nra_exempt_from_ss';
533 l_api_updating BOOLEAN;
534 --
535 BEGIN
536 --
537 hr_utility.set_location('Entering:'||l_proc, 5);
538 --
539 l_api_updating := pqp_det_shd.api_updating
540 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
541 p_object_version_number => p_object_version_number);
542 --
543 IF (l_api_updating AND
544 p_nra_exempt_from_ss
545 <> NVL(pqp_det_shd.g_old_rec.nra_exempt_from_ss,hr_api.g_varchar2)
546 OR NOT l_api_updating)
547 AND p_nra_exempt_from_ss IS NOT NULL THEN
548 --
549 -- check if value of lookup falls within lookup type.
550 --
551 IF hr_api.not_exists_in_hr_lookups
552 (p_lookup_type => 'PQH_YES_NO' ,
553 p_lookup_code => p_nra_exempt_from_ss ,
554 p_effective_date => p_effective_date ) THEN
555 --
556 -- raise error as does not exist as lookup
557 --
558 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
559 hr_utility.set_message_token('COLUMN','nra_exempt_from_ss' );
560 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
561 hr_utility.raise_error;
562 --
563 END IF;
564 --
565 END IF;
566 --
567 hr_utility.set_location('Leaving:'||l_proc,10);
568 --
569 END chk_nra_exempt_from_ss;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |------< chk_nra_exempt_from_medicare >------|
573 -- ----------------------------------------------------------------------------
574 --
575 -- Description
576 -- This procedure is used to check that the lookup value is valid.
577 --
578 -- Pre Conditions
579 -- None.
580 --
581 -- In Parameters
582 -- analyzed_data_details_id PK of record being inserted or updated.
583 -- nra_exempt_from_medicare Value of lookup code.
584 -- effective_date effective date
585 -- object_version_number Object version number of record being
586 -- inserted or updated.
587 --
588 -- Post Success
589 -- Processing continues
590 --
591 -- Post Failure
592 -- Error handled by procedure
593 --
594 -- Access Status
595 -- Internal table handler use only.
596 --
597 PROCEDURE chk_nra_exempt_from_medicare(p_analyzed_data_details_id in number ,
598 p_nra_exempt_from_medicare in varchar2 ,
599 p_effective_date in date ,
600 p_object_version_number in number ) IS
601 --
602 l_proc VARCHAR2(72) := g_package||'chk_nra_exempt_from_medicare';
603 l_api_updating BOOLEAN;
604 --
605 BEGIN
606 --
607 hr_utility.set_location('Entering:'||l_proc, 5);
608 --
609 l_api_updating := pqp_det_shd.api_updating
610 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
611 p_object_version_number => p_object_version_number);
612 --
613 IF (l_api_updating AND
614 p_nra_exempt_from_medicare
615 <> NVL(pqp_det_shd.g_old_rec.nra_exempt_from_medicare,hr_api.g_varchar2)
616 OR NOT l_api_updating)
617 AND p_nra_exempt_from_medicare IS NOT NULL THEN
618 --
619 -- check if value of lookup falls within lookup type.
620 --
621 IF hr_api.not_exists_in_hr_lookups
622 (p_lookup_type => 'PQH_YES_NO' ,
623 p_lookup_code => p_nra_exempt_from_medicare ,
624 p_effective_date => p_effective_date ) THEN
625 --
626 -- raise error as does not exist as lookup
627 --
628 hr_utility.set_message(801,'HR_52966_INVALID_LOOKUP' );
629 hr_utility.set_message_token('COLUMN','nra_exempt_from_medicare' );
630 hr_utility.set_message_token('LOOKUP_TYPE', 'PQH_YES_NO' );
631 hr_utility.raise_error;
632 --
633 END IF;
634 --
635 END IF;
636 --
637 hr_utility.set_location('Leaving:'||l_proc,10);
638 --
639 END chk_nra_exempt_from_medicare;
640 --
641 -- ----------------------------------------------------------------------------
642 -- |------< chk_income_code >------|
643 -- ----------------------------------------------------------------------------
644 --
645 -- Description
646 -- This procedure is used to check that the lookup value is valid.
647 --
648 -- Pre Conditions
649 -- None.
650 --
651 -- In Parameters
652 -- analyzed_data_details_id PK of record being inserted or updated.
653 -- income_code Value of lookup code.
654 -- effective_date effective date
655 -- object_version_number Object version number of record being
656 -- inserted or updated.
657 --
658 -- Post Success
659 -- Processing continues
660 --
661 -- Post Failure
662 -- Error handled by procedure
663 --
664 -- Access Status
665 -- Internal table handler use only.
666 --
667 PROCEDURE chk_income_code(p_analyzed_data_details_id in number ,
668 p_income_code in varchar2 ,
669 p_effective_date in date ,
670 p_object_version_number in number ) IS
671 --
672 l_proc VARCHAR2(72) := g_package||'chk_income_code';
673 l_api_updating BOOLEAN;
674 --
675 BEGIN
676 --
677 hr_utility.set_location('Entering:'||l_proc, 5);
678 --
679 l_api_updating := pqp_det_shd.api_updating
680 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
681 p_object_version_number => p_object_version_number);
682 --
683 IF (l_api_updating AND
684 p_income_code
685 <> NVL(pqp_det_shd.g_old_rec.income_code,hr_api.g_varchar2)
686 OR NOT l_api_updating)
687 AND p_income_code IS NOT NULL THEN
688 --
689 -- check if value of lookup falls within lookup type.
690 --
691 IF hr_api.not_exists_in_hr_lookups
692 (p_lookup_type => 'PER_US_INCOME_TYPES' ,
693 p_lookup_code => p_income_code ,
694 p_effective_date => p_effective_date ) THEN
695 --
696 -- raise error as does not exist as lookup
697 --
698 hr_utility.set_message(801, 'HR_52966_INVALID_LOOKUP' );
699 hr_utility.set_message_token('COLUMN', 'income_code' );
700 hr_utility.set_message_token('LOOKUP_TYPE',
701 'PER_US_INCOME_TYPES' );
702 hr_utility.raise_error;
703 --
704 END IF;
705 --
706 END IF;
707 --
708 hr_utility.set_location('Leaving:'||l_proc,10);
709 --
710 END chk_income_code;
711 --
712 -- |------< chk_income_code_sub_type >------|
713 -- ----------------------------------------------------------------------------
714 --
715 -- Description
716 -- This procedure is used to check that the lookup value is valid.
717 --
718 -- Pre Conditions
719 -- None.
720 --
721 -- In Parameters
722 -- analyzed_data_details_id PK of record being inserted or updated.
723 -- income_code_sub_type Value of lookup code.
724 -- effective_date effective date
725 -- object_version_number Object version number of record being
726 -- inserted or updated.
727 --
728 -- Post Success
729 -- Processing continues
730 --
731 -- Post Failure
732 -- Error handled by procedure
733 --
734 -- Access Status
735 -- Internal table handler use only.
736 --
737 PROCEDURE chk_income_code_sub_type(p_analyzed_data_details_id in number ,
738 p_income_code_sub_type in varchar2 ,
739 p_effective_date in date ,
740 p_object_version_number in number ) IS
741 --
742 l_proc VARCHAR2(72) := g_package||'chk_income_code_sub_type';
743 l_api_updating BOOLEAN;
744 --
745 BEGIN
746 --
747 hr_utility.set_location('Entering:'||l_proc, 5);
748 --
749 l_api_updating := pqp_det_shd.api_updating
750 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
751 p_object_version_number => p_object_version_number);
752 --
753 IF (l_api_updating AND
754 p_income_code_sub_type
755 <> NVL(pqp_det_shd.g_old_rec.income_code_sub_type,hr_api.g_varchar2)
756 OR NOT l_api_updating)
757 AND p_income_code_sub_type IS NOT NULL THEN
758 --
759 -- check if value of lookup falls within lookup type.
760 --
761 IF hr_api.not_exists_in_hr_lookups
762 (p_lookup_type => 'PQP_US_INCOME_CODE_SUB_TYPE' ,
763 p_lookup_code => p_income_code_sub_type ,
764 p_effective_date => p_effective_date ) THEN
765 --
766 -- raise error as does not exist as lookup
767 --
768 hr_utility.set_message(801, 'HR_52966_INVALID_LOOKUP' );
769 hr_utility.set_message_token('COLUMN', 'income_code_sub_type' );
770 hr_utility.set_message_token('LOOKUP_TYPE',
771 'PQP_US_INCOME_CODE_SUB_TYPE' );
772 hr_utility.raise_error;
773 --
774 END IF;
775 --
776 END IF;
777 --
778 hr_utility.set_location('Leaving:'||l_proc,10);
779 --
780 END chk_income_code_sub_type;
781 --
782 -- ----------------------------------------------------------------------------
783 -- |------< chk_exemption_code >------|
784 -- ----------------------------------------------------------------------------
785 --
786 -- Description
787 -- This procedure is used to check that the lookup value is valid.
788 --
789 -- Pre Conditions
790 -- None.
791 --
792 -- In Parameters
793 -- analyzed_data_details_id PK of record being inserted or updated.
794 -- exemption_code Value of lookup code.
795 -- effective_date effective date
796 -- object_version_number Object version number of record being
797 -- inserted or updated.
798 --
799 -- Post Success
800 -- Processing continues
801 --
802 -- Post Failure
803 -- Error handled by procedure
804 --
805 -- Access Status
806 -- Internal table handler use only.
807 --
808 PROCEDURE chk_exemption_code(p_analyzed_data_details_id in number ,
809 p_exemption_code in varchar2 ,
810 p_effective_date in date ,
811 p_object_version_number in number ) IS
812 --
813 l_proc VARCHAR2(72) := g_package||'chk_exemption_code';
814 l_api_updating BOOLEAN;
815 --
816 BEGIN
817 --
818 hr_utility.set_location('Entering:'||l_proc, 5);
819 --
820 l_api_updating := pqp_det_shd.api_updating
821 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
822 p_object_version_number => p_object_version_number);
823 --
824 IF (l_api_updating AND
825 p_exemption_code
826 <> NVL(pqp_det_shd.g_old_rec.exemption_code,hr_api.g_varchar2)
827 OR NOT l_api_updating)
828 AND p_exemption_code IS NOT NULL THEN
829 --
830 -- check if value of lookup falls within lookup type.
831 --
832 IF hr_api.not_exists_in_hr_lookups
833 (p_lookup_type => 'PQP_US_EXEMPTION_CODE' ,
834 p_lookup_code => p_exemption_code ,
835 p_effective_date => p_effective_date ) THEN
836 --
837 -- raise error as does not exist as lookup
838 --
839 hr_utility.set_message(801, 'HR_52966_INVALID_LOOKUP' );
840 hr_utility.set_message_token('COLUMN', 'exemption_code' );
841 hr_utility.set_message_token('LOOKUP_TYPE',
842 'PQP_US_EXEMPTION_CODE' );
843 hr_utility.raise_error;
844 --
845 END IF;
846 --
847 END IF;
848 --
849 hr_utility.set_location('Leaving:'||l_proc,10);
850 --
851 END chk_exemption_code;
852 --
853 -- ----------------------------------------------------------------------------
854 -- |------< chk_addl_wthldng_amt_period >------|
855 -- ----------------------------------------------------------------------------
856 --
857 -- Description
858 -- This procedure is used to check that the lookup value is valid.
859 --
860 -- Pre Conditions
861 -- None.
862 --
863 -- In Parameters
864 -- analyzed_data_details_id PK of record being inserted or updated.
865 -- addl_wthldng_amt_period_type Value of lookup code.
866 -- effective_date effective date
867 -- object_version_number Object version number of record being
868 -- inserted or updated.
869 --
870 -- Post Success
871 -- Processing continues
872 --
873 -- Post Failure
874 -- Error handled by procedure
875 --
876 -- Access Status
877 -- Internal table handler use only.
878 --
879 PROCEDURE chk_addl_wthldng_amt_period(p_analyzed_data_details_id in number ,
880 p_addl_wthldng_amt_period_type in varchar2 ,
881 p_effective_date in date ,
882 p_object_version_number in number ) IS
883 --
884 l_proc VARCHAR2(72) := g_package||'chk_addl_wthldng_amt_period_type';
885 l_api_updating BOOLEAN;
886 --
887 BEGIN
888 --
889 hr_utility.set_location('Entering:'||l_proc, 5);
890 --
891 l_api_updating := pqp_det_shd.api_updating
892 (p_analyzed_data_details_id=>p_analyzed_data_details_id,
893 p_object_version_number => p_object_version_number);
894 --
895 IF (l_api_updating AND
896 p_addl_wthldng_amt_period_type
897 <> NVL(pqp_det_shd.g_old_rec.addl_wthldng_amt_period_type,hr_api.g_varchar2)
898 OR NOT l_api_updating)
899 AND p_addl_wthldng_amt_period_type IS NOT NULL THEN
900 --
901 -- check if value of lookup falls within lookup type.
902 --
903 IF hr_api.not_exists_in_hr_lookups
904 (p_lookup_type => 'PROC_PERIOD_TYPE' ,
905 p_lookup_code => p_addl_wthldng_amt_period_type ,
906 p_effective_date => p_effective_date ) THEN
907 --
908 -- raise error as does not exist as lookup
909 --
910 hr_utility.set_message(801, 'HR_52966_INVALID_LOOKUP' );
911 hr_utility.set_message_token('COLUMN',
912 'addl_wthldng_amt_period_type' );
913 hr_utility.set_message_token('LOOKUP_TYPE',
914 'PROC_PERIOD_TYPE' );
915 hr_utility.raise_error;
916 --
917 END IF;
918 --
919 END IF;
920 --
921 hr_utility.set_location('Leaving:'||l_proc,10);
922 --
923 END chk_addl_wthldng_amt_period;
924
925 -- ----------------------------------------------------------------------------
926 -- |---------------------------< insert_validate >----------------------------|
927 -- ----------------------------------------------------------------------------
928 Procedure insert_validate(p_rec in pqp_det_shd.g_rec_type
929 ,p_effective_date in date) is
930 --
931 l_proc varchar2(72) := g_package||'insert_validate';
932 --
933 Begin
934 hr_utility.set_location('Entering:'||l_proc, 5);
935 --
936 -- Call all supporting business operations
937 --
938 chk_analyzed_data_details_id
939 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
940 p_object_version_number => p_rec.object_version_number);
941 --
942 chk_analyzed_data_id
943 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
944 p_analyzed_data_id => p_rec.analyzed_data_id,
945 p_object_version_number => p_rec.object_version_number);
946 --
947 chk_retro_lose_ben_amt_flag
948 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
949 p_retro_lose_ben_amt_flag => p_rec.retro_lose_ben_amt_flag,
950 p_effective_date => p_effective_date,
951 p_object_version_number => p_rec.object_version_number);
952 --
953 chk_treaty_ben_allowed_flag
954 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
955 p_treaty_ben_allowed_flag => p_rec.treaty_ben_allowed_flag,
956 p_effective_date => p_effective_date,
957 p_object_version_number => p_rec.object_version_number);
958 --
959 chk_addl_withholding_flag
960 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
961 p_addl_withholding_flag => p_rec.addl_withholding_flag,
962 p_effective_date => p_effective_date,
963 p_object_version_number => p_rec.object_version_number);
964 --
965 chk_retro_lose_ben_date_flag
966 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
967 p_retro_lose_ben_date_flag => p_rec.retro_lose_ben_date_flag,
968 p_effective_date => p_effective_date,
969 p_object_version_number => p_rec.object_version_number);
970 --
971 chk_student_exempt_from_ss
972 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
973 p_student_exempt_from_ss => p_rec.student_exempt_from_ss,
974 p_effective_date => p_effective_date ,
975 p_object_version_number => p_rec.object_version_number );
976 --
977 chk_nra_exempt_from_ss
978 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
979 p_nra_exempt_from_ss => p_rec.nra_exempt_from_ss ,
980 p_effective_date => p_effective_date ,
981 p_object_version_number => p_rec.object_version_number );
982 --
983 chk_nra_exempt_from_medicare
984 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
985 p_nra_exempt_from_medicare => p_rec.nra_exempt_from_medicare,
986 p_effective_date => p_effective_date ,
987 p_object_version_number => p_rec.object_version_number );
988 --
989 chk_income_code
990 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
991 p_income_code => p_rec.income_code ,
992 p_effective_date => p_effective_date ,
993 p_object_version_number => p_rec.object_version_number );
994 --
995 chk_income_code_sub_type
996 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
997 p_income_code_sub_type => p_rec.income_code_sub_type ,
998 p_effective_date => p_effective_date ,
999 p_object_version_number => p_rec.object_version_number );
1000 --
1001 chk_exemption_code
1002 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1006 --
1003 p_exemption_code => p_rec.exemption_code ,
1004 p_effective_date => p_effective_date ,
1005 p_object_version_number => p_rec.object_version_number );
1007 chk_addl_wthldng_amt_period
1008 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id ,
1009 p_addl_wthldng_amt_period_type => p_rec.addl_wthldng_amt_period_type,
1010 p_effective_date => p_effective_date ,
1011 p_object_version_number => p_rec.object_version_number );
1012 --
1013 --
1014 --
1015 hr_utility.set_location(' Leaving:'||l_proc, 10);
1016 End insert_validate;
1017 --
1018 -- ----------------------------------------------------------------------------
1019 -- |---------------------------< update_validate >----------------------------|
1020 -- ----------------------------------------------------------------------------
1021 Procedure update_validate(p_rec in pqp_det_shd.g_rec_type
1022 ,p_effective_date in date) is
1023 --
1024 l_proc varchar2(72) := g_package||'update_validate';
1025 --
1026 Begin
1027 hr_utility.set_location('Entering:'||l_proc, 5);
1028 --
1029 -- Call all supporting business operations
1030 --
1031 chk_analyzed_data_details_id
1032 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1033 p_object_version_number => p_rec.object_version_number);
1034 --
1035 chk_analyzed_data_id
1036 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1037 p_analyzed_data_id => p_rec.analyzed_data_id,
1038 p_object_version_number => p_rec.object_version_number);
1039 --
1040 chk_retro_lose_ben_amt_flag
1041 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1042 p_retro_lose_ben_amt_flag => p_rec.retro_lose_ben_amt_flag,
1043 p_effective_date => p_effective_date,
1044 p_object_version_number => p_rec.object_version_number);
1045 --
1046 chk_treaty_ben_allowed_flag
1047 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1048 p_treaty_ben_allowed_flag => p_rec.treaty_ben_allowed_flag,
1049 p_effective_date => p_effective_date,
1050 p_object_version_number => p_rec.object_version_number);
1051 --
1052 chk_addl_withholding_flag
1053 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1054 p_addl_withholding_flag => p_rec.addl_withholding_flag,
1055 p_effective_date => p_effective_date,
1056 p_object_version_number => p_rec.object_version_number);
1057 --
1058 chk_retro_lose_ben_date_flag
1059 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1060 p_retro_lose_ben_date_flag => p_rec.retro_lose_ben_date_flag,
1061 p_effective_date => p_effective_date,
1062 p_object_version_number => p_rec.object_version_number);
1063 --
1064 chk_student_exempt_from_ss
1065 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1066 p_student_exempt_from_ss => p_rec.student_exempt_from_ss,
1067 p_effective_date => p_effective_date ,
1068 p_object_version_number => p_rec.object_version_number );
1069 --
1070 chk_nra_exempt_from_ss
1071 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1072 p_nra_exempt_from_ss => p_rec.nra_exempt_from_ss ,
1073 p_effective_date => p_effective_date ,
1074 p_object_version_number => p_rec.object_version_number );
1075 --
1076 chk_nra_exempt_from_medicare
1077 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1078 p_nra_exempt_from_medicare => p_rec.nra_exempt_from_medicare,
1079 p_effective_date => p_effective_date ,
1080 p_object_version_number => p_rec.object_version_number );
1081 --
1082 chk_income_code
1083 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1084 p_income_code => p_rec.income_code ,
1085 p_effective_date => p_effective_date ,
1086 p_object_version_number => p_rec.object_version_number );
1087 --
1088 chk_income_code_sub_type
1089 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1090 p_income_code_sub_type => p_rec.income_code_sub_type ,
1091 p_effective_date => p_effective_date ,
1092 p_object_version_number => p_rec.object_version_number );
1093 --
1094 chk_exemption_code
1095 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id,
1096 p_exemption_code => p_rec.exemption_code ,
1097 p_effective_date => p_effective_date ,
1098 p_object_version_number => p_rec.object_version_number );
1099 --
1100 chk_addl_wthldng_amt_period
1101 (p_analyzed_data_details_id => p_rec.analyzed_data_details_id ,
1102 p_addl_wthldng_amt_period_type => p_rec.addl_wthldng_amt_period_type,
1103 p_effective_date => p_effective_date ,
1104 p_object_version_number => p_rec.object_version_number );
1105 --
1106 --
1107 --
1108 hr_utility.set_location(' Leaving:'||l_proc, 10);
1109 End update_validate;
1110 --
1111 -- ----------------------------------------------------------------------------
1112 -- |---------------------------< delete_validate >----------------------------|
1113 -- ----------------------------------------------------------------------------
1114 Procedure delete_validate(p_rec in pqp_det_shd.g_rec_type
1115 ,p_effective_date in date) is
1116 --
1117 l_proc varchar2(72) := g_package||'delete_validate';
1118 --
1119 Begin
1123 --
1120 hr_utility.set_location('Entering:'||l_proc, 5);
1121 --
1122 -- Call all supporting business operations
1124 hr_utility.set_location(' Leaving:'||l_proc, 10);
1125 End delete_validate;
1126 --
1127 end pqp_det_bus;