[Home] [Help]
PACKAGE BODY: APPS.PQH_PTX_BUS
Source
1 Package Body pqh_ptx_bus as
2 /* $Header: pqptxrhi.pkb 120.0.12010000.2 2008/08/05 13:41:09 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_ptx_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_position_transaction_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 -- position_transaction_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_position_transaction_id(p_position_transaction_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_position_transaction_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_ptx_shd.api_updating
47 (p_position_transaction_id => p_position_transaction_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_position_transaction_id,hr_api.g_number)
52 <> pqh_ptx_shd.g_old_rec.position_transaction_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_position_transaction_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_position_transaction_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_organization_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_position_transaction_id PK
89 -- p_organization_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_organization_id (p_position_transaction_id in number,
102 p_organization_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_organization_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from hr_all_organization_units a
112 where a.organization_id = p_organization_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_ptx_shd.api_updating
119 (p_position_transaction_id => p_position_transaction_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_organization_id,hr_api.g_number)
124 <> nvl(pqh_ptx_shd.g_old_rec.organization_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_organization_id is not null then
127 --
128 -- check if organization_id value exists in hr_all_organization_units table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in hr_all_organization_units
138 -- table.
139 --
140 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_FK9');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_organization_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_position_definition_id >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure checks that a referenced foreign key actually exists
158 -- in the referenced table.
159 --
160 -- Pre-Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- p_position_transaction_id PK
165 -- p_position_definition_id ID of FK column
166 -- p_object_version_number object version number
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error raised.
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_position_definition_id (p_position_transaction_id in number,
178 p_position_definition_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_position_definition_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from per_position_definitions a
188 where a.position_definition_id = p_position_definition_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_ptx_shd.api_updating
195 (p_position_transaction_id => p_position_transaction_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_position_definition_id,hr_api.g_number)
200 <> nvl(pqh_ptx_shd.g_old_rec.position_definition_id,hr_api.g_number)
201 or not l_api_updating) and
202 p_position_definition_id is not null then
203 --
204 -- check if position_definition_id value exists in per_position_definitions table
205 --
206 open c1;
207 --
208 fetch c1 into l_dummy;
209 if c1%notfound then
210 --
211 close c1;
212 --
213 -- raise error as FK does not relate to PK in per_position_definitions
214 -- table.
215 --
216 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_FK6');
217 --
218 end if;
219 --
220 close c1;
221 --
222 end if;
223 --
224 hr_utility.set_location('Leaving:'||l_proc,10);
225 --
226 End chk_position_definition_id;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |------< chk_job_id >------|
230 -- ----------------------------------------------------------------------------
231 --
232 -- Description
233 -- This procedure checks that a referenced foreign key actually exists
234 -- in the referenced table.
235 --
236 -- Pre-Conditions
237 -- None.
238 --
239 -- In Parameters
240 -- p_position_transaction_id PK
241 -- p_job_id ID of FK column
242 -- p_object_version_number object version number
243 --
244 -- Post Success
245 -- Processing continues
246 --
247 -- Post Failure
248 -- Error raised.
249 --
250 -- Access Status
251 -- Internal table handler use only.
252 --
253 Procedure chk_job_id (p_position_transaction_id in number,
254 p_job_id in number,
255 p_object_version_number in number) is
256 --
257 l_proc varchar2(72) := g_package||'chk_job_id';
258 l_api_updating boolean;
259 l_dummy varchar2(1);
260 --
261 cursor c1 is
262 select null
263 from per_jobs a
264 where a.job_id = p_job_id;
265 --
266 Begin
267 --
268 hr_utility.set_location('Entering:'||l_proc,5);
269 --
270 l_api_updating := pqh_ptx_shd.api_updating
271 (p_position_transaction_id => p_position_transaction_id,
272 p_object_version_number => p_object_version_number);
273 --
274 if (l_api_updating
275 and nvl(p_job_id,hr_api.g_number)
276 <> nvl(pqh_ptx_shd.g_old_rec.job_id,hr_api.g_number)
277 or not l_api_updating) and
278 p_job_id is not null then
279 --
280 -- check if job_id value exists in per_jobs table
281 --
282 open c1;
283 --
284 fetch c1 into l_dummy;
285 if c1%notfound then
286 --
287 close c1;
288 --
289 -- raise error as FK does not relate to PK in per_jobs
290 -- table.
291 --
292 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_FK5');
293 --
294 end if;
295 --
296 close c1;
297 --
298 end if;
299 --
300 hr_utility.set_location('Leaving:'||l_proc,10);
301 --
302 End chk_job_id;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |------< chk_location_id >------|
306 -- ----------------------------------------------------------------------------
307 --
308 -- Description
309 -- This procedure checks that a referenced foreign key actually exists
310 -- in the referenced table.
311 --
312 -- Pre-Conditions
313 -- None.
314 --
315 -- In Parameters
316 -- p_position_transaction_id PK
317 -- p_location_id ID of FK column
318 -- p_object_version_number object version number
319 --
320 -- Post Success
321 -- Processing continues
322 --
323 -- Post Failure
324 -- Error raised.
325 --
326 -- Access Status
327 -- Internal table handler use only.
328 --
329 Procedure chk_location_id (p_position_transaction_id in number,
330 p_location_id in number,
331 p_object_version_number in number) is
332 --
333 l_proc varchar2(72) := g_package||'chk_location_id';
334 l_api_updating boolean;
335 l_dummy varchar2(1);
336 --
337 cursor c1 is
338 select null
339 from hr_locations_all a
340 where a.location_id = p_location_id;
341 --
342 Begin
343 --
344 hr_utility.set_location('Entering:'||l_proc,5);
345 --
346 l_api_updating := pqh_ptx_shd.api_updating
347 (p_position_transaction_id => p_position_transaction_id,
348 p_object_version_number => p_object_version_number);
349 --
350 if (l_api_updating
351 and nvl(p_location_id,hr_api.g_number)
352 <> nvl(pqh_ptx_shd.g_old_rec.location_id,hr_api.g_number)
353 or not l_api_updating) and
354 p_location_id is not null then
355 --
356 -- check if location_id value exists in hr_locations_all table
357 --
358 open c1;
359 --
360 fetch c1 into l_dummy;
361 if c1%notfound then
362 --
363 close c1;
364 --
365 -- raise error as FK does not relate to PK in hr_locations_all
366 -- table.
367 --
368 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_FK4');
369 --
370 end if;
371 --
372 close c1;
373 --
374 end if;
375 --
376 hr_utility.set_location('Leaving:'||l_proc,10);
377 --
378 End chk_location_id;
379 --
380 -- ----------------------------------------------------------------------------
381 -- |------< chk_availability_status_id >------|
382 -- ----------------------------------------------------------------------------
383 --
384 -- Description
385 -- This procedure checks that a referenced foreign key actually exists
386 -- in the referenced table.
387 --
388 -- Pre-Conditions
389 -- None.
390 --
391 -- In Parameters
392 -- p_position_transaction_id PK
393 -- p_availability_status_id ID of FK column
394 -- p_object_version_number object version number
395 --
396 -- Post Success
397 -- Processing continues
398 --
399 -- Post Failure
400 -- Error raised.
401 --
402 -- Access Status
403 -- Internal table handler use only.
404 --
405 Procedure chk_availability_status_id (p_position_transaction_id in number,
406 p_availability_status_id in number,
407 p_object_version_number in number) is
408 --
409 l_proc varchar2(72) := g_package||'chk_availability_status_id';
410 l_api_updating boolean;
411 l_dummy varchar2(1);
412 --
413 cursor c1 is
414 select null
415 from per_shared_types a
416 where a.shared_type_id = p_availability_status_id;
417 --
418 Begin
419 --
420 hr_utility.set_location('Entering:'||l_proc,5);
421 --
422 l_api_updating := pqh_ptx_shd.api_updating
423 (p_position_transaction_id => p_position_transaction_id,
424 p_object_version_number => p_object_version_number);
425 --
426 if (l_api_updating
427 and nvl(p_availability_status_id,hr_api.g_number)
428 <> nvl(pqh_ptx_shd.g_old_rec.availability_status_id,hr_api.g_number)
429 or not l_api_updating) and
430 p_availability_status_id is not null then
431 --
432 -- check if availability_status_id value exists in per_shared_types table
433 --
434 open c1;
435 --
436 fetch c1 into l_dummy;
437 if c1%notfound then
438 --
439 close c1;
440 --
441 -- raise error as FK does not relate to PK in per_shared_types
442 -- table.
443 --
444 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_FK12');
445 --
446 end if;
447 --
448 close c1;
449 --
450 end if;
451 --
452 hr_utility.set_location('Leaving:'||l_proc,10);
453 --
454 End chk_availability_status_id;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |------< chk_entry_grade_id >------|
458 -- ----------------------------------------------------------------------------
459 --
460 -- Description
461 -- This procedure checks that a referenced foreign key actually exists
462 -- in the referenced table.
463 --
464 -- Pre-Conditions
465 -- None.
466 --
467 -- In Parameters
468 -- p_position_transaction_id PK
469 -- p_entry_grade_id ID of FK column
470 -- p_object_version_number object version number
471 --
472 -- Post Success
473 -- Processing continues
474 --
475 -- Post Failure
476 -- Error raised.
477 --
478 -- Access Status
479 -- Internal table handler use only.
480 --
481 Procedure chk_entry_grade_id (p_position_transaction_id in number,
482 p_entry_grade_id in number,
483 p_object_version_number in number) is
484 --
485 l_proc varchar2(72) := g_package||'chk_entry_grade_id';
486 l_api_updating boolean;
487 l_dummy varchar2(1);
488 --
489 cursor c1 is
490 select null
491 from per_grades a
492 where a.grade_id = p_entry_grade_id;
493 --
494 Begin
495 --
496 hr_utility.set_location('Entering:'||l_proc,5);
497 --
498 l_api_updating := pqh_ptx_shd.api_updating
499 (p_position_transaction_id => p_position_transaction_id,
500 p_object_version_number => p_object_version_number);
501 --
502 if (l_api_updating
503 and nvl(p_entry_grade_id,hr_api.g_number)
504 <> nvl(pqh_ptx_shd.g_old_rec.entry_grade_id,hr_api.g_number)
505 or not l_api_updating) and
506 p_entry_grade_id is not null then
507 --
508 -- check if entry_grade_id value exists in per_grades table
509 --
510 open c1;
511 --
512 fetch c1 into l_dummy;
513 if c1%notfound then
514 --
515 close c1;
516 --
517 -- raise error as FK does not relate to PK in per_grades
518 -- table.
519 --
520 pqh_ptx_shd.constraint_error('PQH_POSITION_TRANSACTIONS_FK11');
521 --
522 end if;
523 --
524 close c1;
525 --
526 end if;
527 --
528 hr_utility.set_location('Leaving:'||l_proc,10);
529 --
530 End chk_entry_grade_id;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |------< chk_work_term_end_month_cd >------|
534 -- ----------------------------------------------------------------------------
535 --
536 -- Description
537 -- This procedure is used to check that the lookup value is valid.
538 --
539 -- Pre Conditions
540 -- None.
541 --
542 -- In Parameters
543 -- position_transaction_id PK of record being inserted or updated.
544 -- work_term_end_month_cd Value of lookup code.
545 -- effective_date effective date
546 -- object_version_number Object version number of record being
547 -- inserted or updated.
548 --
549 -- Post Success
550 -- Processing continues
551 --
552 -- Post Failure
553 -- Error handled by procedure
554 --
555 -- Access Status
556 -- Internal table handler use only.
557 --
558 Procedure chk_work_term_end_month_cd(p_position_transaction_id in number,
559 p_work_term_end_month_cd in varchar2,
560 p_effective_date in date,
561 p_object_version_number in number) is
562 --
563 l_proc varchar2(72) := g_package||'chk_work_term_end_month_cd';
564 l_api_updating boolean;
565 --
566 Begin
567 --
568 hr_utility.set_location('Entering:'||l_proc, 5);
569 --
570 l_api_updating := pqh_ptx_shd.api_updating
571 (p_position_transaction_id => p_position_transaction_id,
572 p_object_version_number => p_object_version_number);
573 --
574 if (l_api_updating
575 and p_work_term_end_month_cd
576 <> nvl(pqh_ptx_shd.g_old_rec.work_term_end_month_cd,hr_api.g_varchar2)
577 or not l_api_updating)
578 and p_work_term_end_month_cd is not null then
579 --
580 -- check if value of lookup falls within lookup type.
581 --
582 if hr_api.not_exists_in_hr_lookups
583 (p_lookup_type => 'MONTH_CODE',
584 p_lookup_code => p_work_term_end_month_cd,
585 p_effective_date => p_effective_date) then
586 --
587 -- raise error as does not exist as lookup
588 --
589 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
590 hr_utility.raise_error;
591 --
592 end if;
593 --
594 end if;
595 --
596 hr_utility.set_location('Leaving:'||l_proc,10);
597 --
598 end chk_work_term_end_month_cd;
599 --
600 -- ----------------------------------------------------------------------------
601 -- |------< chk_work_term_end_day_cd >------|
602 -- ----------------------------------------------------------------------------
603 --
604 -- Description
605 -- This procedure is used to check that the lookup value is valid.
606 --
607 -- Pre Conditions
608 -- None.
609 --
610 -- In Parameters
611 -- position_transaction_id PK of record being inserted or updated.
612 -- work_term_end_day_cd Value of lookup code.
613 -- effective_date effective date
614 -- object_version_number Object version number of record being
615 -- inserted or updated.
616 --
617 -- Post Success
618 -- Processing continues
619 --
620 -- Post Failure
621 -- Error handled by procedure
622 --
623 -- Access Status
624 -- Internal table handler use only.
625 --
626 Procedure chk_work_term_end_day_cd(p_position_transaction_id in number,
627 p_work_term_end_day_cd in varchar2,
628 p_effective_date in date,
629 p_object_version_number in number) is
630 --
631 l_proc varchar2(72) := g_package||'chk_work_term_end_day_cd';
632 l_api_updating boolean;
633 --
634 Begin
635 --
636 hr_utility.set_location('Entering:'||l_proc, 5);
637 --
638 l_api_updating := pqh_ptx_shd.api_updating
639 (p_position_transaction_id => p_position_transaction_id,
640 p_object_version_number => p_object_version_number);
641 --
642 if (l_api_updating
643 and p_work_term_end_day_cd
644 <> nvl(pqh_ptx_shd.g_old_rec.work_term_end_day_cd,hr_api.g_varchar2)
645 or not l_api_updating)
646 and p_work_term_end_day_cd is not null then
647 --
648 -- check if value of lookup falls within lookup type.
649 --
650 if hr_api.not_exists_in_hr_lookups
651 (p_lookup_type => 'DAY_CODE',
652 p_lookup_code => p_work_term_end_day_cd,
653 p_effective_date => p_effective_date) then
654 --
655 -- raise error as does not exist as lookup
656 --
657 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
658 hr_utility.raise_error;
659 --
660 end if;
661 --
662 end if;
663 --
664 hr_utility.set_location('Leaving:'||l_proc,10);
665 --
666 end chk_work_term_end_day_cd;
667 --
668 -- ----------------------------------------------------------------------------
669 -- |------< chk_work_period_type_cd >------|
670 -- ----------------------------------------------------------------------------
671 --
672 -- Description
673 -- This procedure is used to check that the lookup value is valid.
674 --
675 -- Pre Conditions
676 -- None.
677 --
678 -- In Parameters
679 -- position_transaction_id PK of record being inserted or updated.
680 -- work_period_type_cd Value of lookup code.
681 -- effective_date effective date
682 -- object_version_number Object version number of record being
683 -- inserted or updated.
684 --
685 -- Post Success
686 -- Processing continues
687 --
688 -- Post Failure
689 -- Error handled by procedure
690 --
691 -- Access Status
692 -- Internal table handler use only.
693 --
694 Procedure chk_work_period_type_cd(p_position_transaction_id in number,
695 p_work_period_type_cd in varchar2,
696 p_effective_date in date,
697 p_object_version_number in number) is
698 --
699 l_proc varchar2(72) := g_package||'chk_work_period_type_cd';
700 l_api_updating boolean;
701 --
702 Begin
703 --
704 hr_utility.set_location('Entering:'||l_proc, 5);
705 --
706 l_api_updating := pqh_ptx_shd.api_updating
707 (p_position_transaction_id => p_position_transaction_id,
708 p_object_version_number => p_object_version_number);
709 --
710 if (l_api_updating
711 and p_work_period_type_cd
712 <> nvl(pqh_ptx_shd.g_old_rec.work_period_type_cd,hr_api.g_varchar2)
713 or not l_api_updating)
714 and p_work_period_type_cd is not null then
715 --
716 -- check if value of lookup falls within lookup type.
717 --
718 if hr_api.not_exists_in_hr_lookups
719 (p_lookup_type => 'YES_NO',
720 p_lookup_code => p_work_period_type_cd,
721 p_effective_date => p_effective_date) then
722 --
723 -- raise error as does not exist as lookup
724 --
725 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
726 hr_utility.raise_error;
727 --
728 end if;
729 --
730 end if;
731 --
732 hr_utility.set_location('Leaving:'||l_proc,10);
733 --
734 end chk_work_period_type_cd;
735 --
736 -- ----------------------------------------------------------------------------
737 -- |------< chk_works_council_approval_flg >------|
738 -- ----------------------------------------------------------------------------
739 --
740 -- Description
741 -- This procedure is used to check that the lookup value is valid.
742 --
743 -- Pre Conditions
744 -- None.
745 --
746 -- In Parameters
747 -- position_transaction_id PK of record being inserted or updated.
748 -- works_council_approval_flag Value of lookup code.
749 -- effective_date effective date
750 -- object_version_number Object version number of record being
751 -- inserted or updated.
752 --
753 -- Post Success
754 -- Processing continues
755 --
756 -- Post Failure
757 -- Error handled by procedure
758 --
759 -- Access Status
760 -- Internal table handler use only.
761 --
762 Procedure chk_works_council_approval_flg(p_position_transaction_id in number,
763 p_works_council_approval_flag in varchar2,
764 p_effective_date in date,
765 p_object_version_number in number) is
766 --
767 l_proc varchar2(72) := g_package||'chk_works_council_approval_flg';
768 l_api_updating boolean;
769 --
770 Begin
771 --
772 hr_utility.set_location('Entering:'||l_proc, 5);
773 --
774 l_api_updating := pqh_ptx_shd.api_updating
775 (p_position_transaction_id => p_position_transaction_id,
776 p_object_version_number => p_object_version_number);
777 --
778 if (l_api_updating
779 and p_works_council_approval_flag
780 <> nvl(pqh_ptx_shd.g_old_rec.works_council_approval_flag,hr_api.g_varchar2)
781 or not l_api_updating)
782 and p_works_council_approval_flag is not null then
783 --
784 -- check if value of lookup falls within lookup type.
785 --
786 if hr_api.not_exists_in_hr_lookups
787 (p_lookup_type => 'YES_NO',
788 p_lookup_code => p_works_council_approval_flag,
789 p_effective_date => p_effective_date) then
790 --
791 -- raise error as does not exist as lookup
792 --
793 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
794 hr_utility.raise_error;
795 --
796 end if;
797 --
798 end if;
799 --
800 hr_utility.set_location('Leaving:'||l_proc,10);
801 --
802 end chk_works_council_approval_flg;
803 --
804 -- ----------------------------------------------------------------------------
805 -- |------< chk_term_start_month_cd >------|
806 -- ----------------------------------------------------------------------------
807 --
808 -- Description
809 -- This procedure is used to check that the lookup value is valid.
810 --
811 -- Pre Conditions
812 -- None.
813 --
814 -- In Parameters
815 -- position_transaction_id PK of record being inserted or updated.
816 -- term_start_month_cd Value of lookup code.
817 -- effective_date effective date
818 -- object_version_number Object version number of record being
819 -- inserted or updated.
820 --
821 -- Post Success
822 -- Processing continues
823 --
824 -- Post Failure
825 -- Error handled by procedure
826 --
827 -- Access Status
828 -- Internal table handler use only.
829 --
830 Procedure chk_term_start_month_cd(p_position_transaction_id in number,
831 p_term_start_month_cd in varchar2,
832 p_effective_date in date,
833 p_object_version_number in number) is
834 --
835 l_proc varchar2(72) := g_package||'chk_term_start_month_cd';
836 l_api_updating boolean;
837 --
838 Begin
839 --
840 hr_utility.set_location('Entering:'||l_proc, 5);
841 --
842 l_api_updating := pqh_ptx_shd.api_updating
843 (p_position_transaction_id => p_position_transaction_id,
844 p_object_version_number => p_object_version_number);
845 --
846 if (l_api_updating
847 and p_term_start_month_cd
848 <> nvl(pqh_ptx_shd.g_old_rec.term_start_month_cd,hr_api.g_varchar2)
849 or not l_api_updating)
850 and p_term_start_month_cd is not null then
851 --
852 -- check if value of lookup falls within lookup type.
853 --
854 if hr_api.not_exists_in_hr_lookups
855 (p_lookup_type => 'MONTH_CODE',
856 p_lookup_code => p_term_start_month_cd,
857 p_effective_date => p_effective_date) then
858 --
859 -- raise error as does not exist as lookup
860 --
861 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
862 hr_utility.raise_error;
863 --
864 end if;
865 --
866 end if;
867 --
868 hr_utility.set_location('Leaving:'||l_proc,10);
869 --
870 end chk_term_start_month_cd;
871 --
872 -- ----------------------------------------------------------------------------
873 -- |------< chk_term_start_day_cd >------|
874 -- ----------------------------------------------------------------------------
875 --
876 -- Description
877 -- This procedure is used to check that the lookup value is valid.
878 --
879 -- Pre Conditions
880 -- None.
881 --
882 -- In Parameters
883 -- position_transaction_id PK of record being inserted or updated.
884 -- term_start_day_cd Value of lookup code.
885 -- effective_date effective date
886 -- object_version_number Object version number of record being
887 -- inserted or updated.
888 --
889 -- Post Success
890 -- Processing continues
891 --
892 -- Post Failure
893 -- Error handled by procedure
894 --
895 -- Access Status
896 -- Internal table handler use only.
897 --
898 Procedure chk_term_start_day_cd(p_position_transaction_id in number,
899 p_term_start_day_cd in varchar2,
900 p_effective_date in date,
901 p_object_version_number in number) is
902 --
903 l_proc varchar2(72) := g_package||'chk_term_start_day_cd';
904 l_api_updating boolean;
905 --
906 Begin
907 --
908 hr_utility.set_location('Entering:'||l_proc, 5);
909 --
910 l_api_updating := pqh_ptx_shd.api_updating
911 (p_position_transaction_id => p_position_transaction_id,
912 p_object_version_number => p_object_version_number);
913 --
914 if (l_api_updating
915 and p_term_start_day_cd
916 <> nvl(pqh_ptx_shd.g_old_rec.term_start_day_cd,hr_api.g_varchar2)
917 or not l_api_updating)
918 and p_term_start_day_cd is not null then
919 --
920 -- check if value of lookup falls within lookup type.
921 --
922 if hr_api.not_exists_in_hr_lookups
923 (p_lookup_type => 'DAY_CODE',
924 p_lookup_code => p_term_start_day_cd,
925 p_effective_date => p_effective_date) then
926 --
927 -- raise error as does not exist as lookup
928 --
929 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
930 hr_utility.raise_error;
931 --
932 end if;
933 --
934 end if;
935 --
936 hr_utility.set_location('Leaving:'||l_proc,10);
937 --
938 end chk_term_start_day_cd;
939 --
940 -- ----------------------------------------------------------------------------
941 -- |------< chk_seasonal_flag >------|
942 -- ----------------------------------------------------------------------------
943 --
944 -- Description
945 -- This procedure is used to check that the lookup value is valid.
946 --
947 -- Pre Conditions
948 -- None.
949 --
950 -- In Parameters
951 -- position_transaction_id PK of record being inserted or updated.
952 -- seasonal_flag Value of lookup code.
953 -- effective_date effective date
954 -- object_version_number Object version number of record being
955 -- inserted or updated.
956 --
957 -- Post Success
958 -- Processing continues
959 --
960 -- Post Failure
961 -- Error handled by procedure
962 --
963 -- Access Status
964 -- Internal table handler use only.
965 --
966 Procedure chk_seasonal_flag(p_position_transaction_id in number,
967 p_seasonal_flag in varchar2,
968 p_effective_date in date,
969 p_object_version_number in number) is
970 --
971 l_proc varchar2(72) := g_package||'chk_seasonal_flag';
972 l_api_updating boolean;
973 --
974 Begin
975 --
976 hr_utility.set_location('Entering:'||l_proc, 5);
977 --
978 l_api_updating := pqh_ptx_shd.api_updating
979 (p_position_transaction_id => p_position_transaction_id,
980 p_object_version_number => p_object_version_number);
981 --
982 if (l_api_updating
983 and p_seasonal_flag
984 <> nvl(pqh_ptx_shd.g_old_rec.seasonal_flag,hr_api.g_varchar2)
985 or not l_api_updating)
986 and p_seasonal_flag is not null then
987 --
988 -- check if value of lookup falls within lookup type.
989 --
990 if hr_api.not_exists_in_hr_lookups
991 (p_lookup_type => 'YES_NO',
992 p_lookup_code => p_seasonal_flag,
993 p_effective_date => p_effective_date) then
994 --
995 -- raise error as does not exist as lookup
996 --
997 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
998 hr_utility.raise_error;
999 --
1000 end if;
1001 --
1002 end if;
1003 --
1004 hr_utility.set_location('Leaving:'||l_proc,10);
1005 --
1006 end chk_seasonal_flag;
1007 --
1008 -- ----------------------------------------------------------------------------
1009 -- |------< chk_review_flag >------|
1010 -- ----------------------------------------------------------------------------
1011 --
1012 -- Description
1013 -- This procedure is used to check that the lookup value is valid.
1014 --
1015 -- Pre Conditions
1016 -- None.
1017 --
1018 -- In Parameters
1019 -- position_transaction_id PK of record being inserted or updated.
1020 -- review_flag Value of lookup code.
1021 -- effective_date effective date
1022 -- object_version_number Object version number of record being
1023 -- inserted or updated.
1024 --
1025 -- Post Success
1026 -- Processing continues
1027 --
1028 -- Post Failure
1029 -- Error handled by procedure
1030 --
1031 -- Access Status
1032 -- Internal table handler use only.
1033 --
1034 Procedure chk_review_flag(p_position_transaction_id in number,
1035 p_review_flag in varchar2,
1036 p_position_id in number,
1037 p_effective_date in date,
1038 p_object_version_number in number) is
1039 --
1040 l_proc varchar2(72) := g_package||'chk_review_flag';
1041 l_api_updating boolean;
1042 l_dummy varchar2(10);
1043 --
1044 cursor c_position_transactions(p_position_id number) is
1045 select 'x'
1046 from pqh_position_transactions
1047 where position_id = p_position_id
1048 and position_transaction_id <> p_position_transaction_id
1049 and transaction_status not in ('TERMINATE', 'APPLIED', 'REJECT');
1050 Begin
1051 --
1052 hr_utility.set_location('Entering:'||l_proc, 5);
1053 --
1054 l_api_updating := pqh_ptx_shd.api_updating
1055 (p_position_transaction_id => p_position_transaction_id,
1056 p_object_version_number => p_object_version_number);
1057 --
1058 if (l_api_updating
1059 and p_review_flag
1060 <> nvl(pqh_ptx_shd.g_old_rec.review_flag,hr_api.g_varchar2)
1061 or not l_api_updating)
1062 and p_review_flag is not null then
1063 --
1064 -- check if value of lookup falls within lookup type.
1065 --
1066 if hr_api.not_exists_in_hr_lookups
1067 (p_lookup_type => 'YES_NO',
1068 p_lookup_code => p_review_flag,
1069 p_effective_date => p_effective_date) then
1070 --
1071 -- raise error as does not exist as lookup
1072 --
1073 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1074 hr_utility.raise_error;
1075 --
1076 end if;
1077 --
1078 if p_position_id is not null then
1079 open c_position_transactions(p_position_id);
1080 fetch c_position_transactions into l_dummy;
1081 --
1082 if c_position_transactions%found then
1083 hr_utility.set_message(8302,'PQH_PTX_EXISTS_CANT_REVIEW');
1084 hr_utility.raise_error;
1085 end if;
1086 end if;
1087 end if;
1088 --
1089 hr_utility.set_location('Leaving:'||l_proc,10);
1090 --
1091 end chk_review_flag;
1092 --
1093 -- -------------------------------------------------------------------
1094 --
1095 -- ----------------------------------------------------------------------------
1096 -- |------< chk_replacement_required_flag >------|
1097 -- ----------------------------------------------------------------------------
1098 --
1099 -- Description
1100 -- This procedure is used to check that the lookup value is valid.
1101 --
1102 -- Pre Conditions
1103 -- None.
1104 --
1105 -- In Parameters
1106 -- position_transaction_id PK of record being inserted or updated.
1107 -- replacement_required_flag Value of lookup code.
1108 -- effective_date effective date
1109 -- object_version_number Object version number of record being
1110 -- inserted or updated.
1111 --
1112 -- Post Success
1113 -- Processing continues
1114 --
1115 -- Post Failure
1116 -- Error handled by procedure
1117 --
1118 -- Access Status
1119 -- Internal table handler use only.
1120 --
1121 Procedure chk_replacement_required_flag(p_position_transaction_id in number,
1122 p_replacement_required_flag in varchar2,
1123 p_effective_date in date,
1124 p_object_version_number in number) is
1125 --
1126 l_proc varchar2(72) := g_package||'chk_replacement_required_flag';
1127 l_api_updating boolean;
1128 --
1129 Begin
1130 --
1131 hr_utility.set_location('Entering:'||l_proc, 5);
1132 --
1133 l_api_updating := pqh_ptx_shd.api_updating
1134 (p_position_transaction_id => p_position_transaction_id,
1135 p_object_version_number => p_object_version_number);
1136 --
1137 if (l_api_updating
1138 and p_replacement_required_flag
1139 <> nvl(pqh_ptx_shd.g_old_rec.replacement_required_flag,hr_api.g_varchar2)
1140 or not l_api_updating)
1141 and p_replacement_required_flag is not null then
1142 --
1143 -- check if value of lookup falls within lookup type.
1144 --
1145 if hr_api.not_exists_in_hr_lookups
1146 (p_lookup_type => 'YES_NO',
1147 p_lookup_code => p_replacement_required_flag,
1148 p_effective_date => p_effective_date) then
1149 --
1150 -- raise error as does not exist as lookup
1151 --
1152 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1153 hr_utility.raise_error;
1154 --
1155 end if;
1156 --
1157 end if;
1158 --
1159 hr_utility.set_location('Leaving:'||l_proc,10);
1160 --
1161 end chk_replacement_required_flag;
1162 --
1163 -- ----------------------------------------------------------------------------
1164 -- |------< chk_probation_period_unit_cd >------|
1165 -- ----------------------------------------------------------------------------
1166 --
1167 -- Description
1168 -- This procedure is used to check that the lookup value is valid.
1169 --
1170 -- Pre Conditions
1171 -- None.
1172 --
1173 -- In Parameters
1174 -- position_transaction_id PK of record being inserted or updated.
1175 -- probation_period_unit_cd Value of lookup code.
1176 -- effective_date effective date
1177 -- object_version_number Object version number of record being
1178 -- inserted or updated.
1179 --
1180 -- Post Success
1181 -- Processing continues
1182 --
1183 -- Post Failure
1184 -- Error handled by procedure
1185 --
1186 -- Access Status
1187 -- Internal table handler use only.
1188 --
1189 Procedure chk_probation_period_unit_cd(p_position_transaction_id in number,
1190 p_probation_period_unit_cd in varchar2,
1191 p_effective_date in date,
1192 p_object_version_number in number) is
1193 --
1194 l_proc varchar2(72) := g_package||'chk_probation_period_unit_cd';
1195 l_api_updating boolean;
1196 --
1197 Begin
1198 --
1199 hr_utility.set_location('Entering:'||l_proc, 5);
1200 --
1201 l_api_updating := pqh_ptx_shd.api_updating
1202 (p_position_transaction_id => p_position_transaction_id,
1203 p_object_version_number => p_object_version_number);
1204 --
1205 if (l_api_updating
1206 and p_probation_period_unit_cd
1207 <> nvl(pqh_ptx_shd.g_old_rec.probation_period_unit_cd,hr_api.g_varchar2)
1208 or not l_api_updating)
1209 and p_probation_period_unit_cd is not null then
1210 --
1211 -- check if value of lookup falls within lookup type.
1212 --
1213 if hr_api.not_exists_in_hr_lookups
1214 (p_lookup_type => 'FREQUENCY',
1215 p_lookup_code => p_probation_period_unit_cd,
1216 p_effective_date => p_effective_date) then
1217 --
1218 -- raise error as does not exist as lookup
1219 --
1220 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1221 hr_utility.raise_error;
1222 --
1223 end if;
1224 --
1225 end if;
1226 --
1227 hr_utility.set_location('Leaving:'||l_proc,10);
1228 --
1229 end chk_probation_period_unit_cd;
1230 --
1231 -- ----------------------------------------------------------------------------
1232 -- |------< chk_permit_recruitment_flag >------|
1233 -- ----------------------------------------------------------------------------
1234 --
1235 -- Description
1236 -- This procedure is used to check that the lookup value is valid.
1237 --
1238 -- Pre Conditions
1239 -- None.
1240 --
1241 -- In Parameters
1242 -- position_transaction_id PK of record being inserted or updated.
1243 -- permit_recruitment_flag Value of lookup code.
1244 -- effective_date effective date
1245 -- object_version_number Object version number of record being
1246 -- inserted or updated.
1247 --
1248 -- Post Success
1249 -- Processing continues
1250 --
1251 -- Post Failure
1252 -- Error handled by procedure
1253 --
1254 -- Access Status
1255 -- Internal table handler use only.
1256 --
1257 Procedure chk_permit_recruitment_flag(p_position_transaction_id in number,
1258 p_permit_recruitment_flag in varchar2,
1259 p_effective_date in date,
1260 p_object_version_number in number) is
1261 --
1262 l_proc varchar2(72) := g_package||'chk_permit_recruitment_flag';
1263 l_api_updating boolean;
1264 --
1265 Begin
1266 --
1267 hr_utility.set_location('Entering:'||l_proc, 5);
1268 --
1269 l_api_updating := pqh_ptx_shd.api_updating
1270 (p_position_transaction_id => p_position_transaction_id,
1271 p_object_version_number => p_object_version_number);
1272 --
1273 if (l_api_updating
1274 and p_permit_recruitment_flag
1275 <> nvl(pqh_ptx_shd.g_old_rec.permit_recruitment_flag,hr_api.g_varchar2)
1276 or not l_api_updating)
1277 and p_permit_recruitment_flag is not null then
1278 --
1279 -- check if value of lookup falls within lookup type.
1280 --
1281 if hr_api.not_exists_in_hr_lookups
1282 (p_lookup_type => 'YES_NO',
1283 p_lookup_code => p_permit_recruitment_flag,
1284 p_effective_date => p_effective_date) then
1285 --
1286 -- raise error as does not exist as lookup
1287 --
1288 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1289 hr_utility.raise_error;
1290 --
1291 end if;
1292 --
1293 end if;
1294 --
1295 hr_utility.set_location('Leaving:'||l_proc,10);
1296 --
1297 end chk_permit_recruitment_flag;
1298 --
1299 -- ----------------------------------------------------------------------------
1300 -- |------< chk_permanent_temporary_flag >------|
1301 -- ----------------------------------------------------------------------------
1302 --
1303 -- Description
1304 -- This procedure is used to check that the lookup value is valid.
1305 --
1306 -- Pre Conditions
1307 -- None.
1308 --
1309 -- In Parameters
1310 -- position_transaction_id PK of record being inserted or updated.
1311 -- permanent_temporary_flag Value of lookup code.
1312 -- effective_date effective date
1313 -- object_version_number Object version number of record being
1314 -- inserted or updated.
1315 --
1316 -- Post Success
1317 -- Processing continues
1318 --
1319 -- Post Failure
1320 -- Error handled by procedure
1321 --
1322 -- Access Status
1323 -- Internal table handler use only.
1324 --
1325 Procedure chk_permanent_temporary_flag(p_position_transaction_id in number,
1326 p_permanent_temporary_flag in varchar2,
1327 p_effective_date in date,
1328 p_object_version_number in number) is
1329 --
1330 l_proc varchar2(72) := g_package||'chk_permanent_temporary_flag';
1331 l_api_updating boolean;
1332 --
1333 Begin
1334 --
1335 hr_utility.set_location('Entering:'||l_proc, 5);
1336 --
1337 l_api_updating := pqh_ptx_shd.api_updating
1338 (p_position_transaction_id => p_position_transaction_id,
1339 p_object_version_number => p_object_version_number);
1340 --
1341 if (l_api_updating
1342 and p_permanent_temporary_flag
1343 <> nvl(pqh_ptx_shd.g_old_rec.permanent_temporary_flag,hr_api.g_varchar2)
1344 or not l_api_updating)
1345 and p_permanent_temporary_flag is not null then
1346 --
1347 -- check if value of lookup falls within lookup type.
1348 --
1349 if hr_api.not_exists_in_hr_lookups
1350 (p_lookup_type => 'YES_NO',
1351 p_lookup_code => p_permanent_temporary_flag,
1352 p_effective_date => p_effective_date) then
1353 --
1354 -- raise error as does not exist as lookup
1355 --
1356 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1357 hr_utility.raise_error;
1358 --
1359 end if;
1360 --
1361 end if;
1362 --
1363 hr_utility.set_location('Leaving:'||l_proc,10);
1364 --
1365 end chk_permanent_temporary_flag;
1366 --
1367 -- ----------------------------------------------------------------------------
1368 -- |------< chk_pay_term_end_month_cd >------|
1369 -- ----------------------------------------------------------------------------
1370 --
1371 -- Description
1372 -- This procedure is used to check that the lookup value is valid.
1373 --
1374 -- Pre Conditions
1375 -- None.
1376 --
1377 -- In Parameters
1378 -- position_transaction_id PK of record being inserted or updated.
1379 -- pay_term_end_month_cd Value of lookup code.
1380 -- effective_date effective date
1381 -- object_version_number Object version number of record being
1382 -- inserted or updated.
1383 --
1384 -- Post Success
1385 -- Processing continues
1386 --
1387 -- Post Failure
1388 -- Error handled by procedure
1389 --
1390 -- Access Status
1391 -- Internal table handler use only.
1392 --
1393 Procedure chk_pay_term_end_month_cd(p_position_transaction_id in number,
1394 p_pay_term_end_month_cd in varchar2,
1395 p_effective_date in date,
1396 p_object_version_number in number) is
1397 --
1398 l_proc varchar2(72) := g_package||'chk_pay_term_end_month_cd';
1399 l_api_updating boolean;
1400 --
1401 Begin
1402 --
1403 hr_utility.set_location('Entering:'||l_proc, 5);
1404 --
1405 l_api_updating := pqh_ptx_shd.api_updating
1406 (p_position_transaction_id => p_position_transaction_id,
1407 p_object_version_number => p_object_version_number);
1408 --
1409 if (l_api_updating
1410 and p_pay_term_end_month_cd
1411 <> nvl(pqh_ptx_shd.g_old_rec.pay_term_end_month_cd,hr_api.g_varchar2)
1412 or not l_api_updating)
1413 and p_pay_term_end_month_cd is not null then
1414 --
1415 -- check if value of lookup falls within lookup type.
1416 --
1417 if hr_api.not_exists_in_hr_lookups
1418 (p_lookup_type => 'MONTH_CODE',
1419 p_lookup_code => p_pay_term_end_month_cd,
1420 p_effective_date => p_effective_date) then
1421 --
1422 -- raise error as does not exist as lookup
1423 --
1424 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1425 hr_utility.raise_error;
1426 --
1427 end if;
1428 --
1429 end if;
1430 --
1431 hr_utility.set_location('Leaving:'||l_proc,10);
1432 --
1433 end chk_pay_term_end_month_cd;
1434 --
1435 -- ----------------------------------------------------------------------------
1436 -- |------< chk_pay_term_end_day_cd >------|
1437 -- ----------------------------------------------------------------------------
1438 --
1439 -- Description
1440 -- This procedure is used to check that the lookup value is valid.
1441 --
1442 -- Pre Conditions
1443 -- None.
1444 --
1445 -- In Parameters
1446 -- position_transaction_id PK of record being inserted or updated.
1447 -- pay_term_end_day_cd Value of lookup code.
1448 -- effective_date effective date
1449 -- object_version_number Object version number of record being
1450 -- inserted or updated.
1451 --
1452 -- Post Success
1453 -- Processing continues
1454 --
1455 -- Post Failure
1456 -- Error handled by procedure
1457 --
1458 -- Access Status
1459 -- Internal table handler use only.
1460 --
1461 Procedure chk_pay_term_end_day_cd(p_position_transaction_id in number,
1462 p_pay_term_end_day_cd in varchar2,
1463 p_effective_date in date,
1464 p_object_version_number in number) is
1465 --
1466 l_proc varchar2(72) := g_package||'chk_pay_term_end_day_cd';
1467 l_api_updating boolean;
1468 --
1469 Begin
1470 --
1471 hr_utility.set_location('Entering:'||l_proc, 5);
1472 --
1473 l_api_updating := pqh_ptx_shd.api_updating
1474 (p_position_transaction_id => p_position_transaction_id,
1475 p_object_version_number => p_object_version_number);
1476 --
1477 if (l_api_updating
1478 and p_pay_term_end_day_cd
1479 <> nvl(pqh_ptx_shd.g_old_rec.pay_term_end_day_cd,hr_api.g_varchar2)
1480 or not l_api_updating)
1481 and p_pay_term_end_day_cd is not null then
1482 --
1483 -- check if value of lookup falls within lookup type.
1484 --
1485 if hr_api.not_exists_in_hr_lookups
1486 (p_lookup_type => 'DAY_CODE',
1487 p_lookup_code => p_pay_term_end_day_cd,
1488 p_effective_date => p_effective_date) then
1489 --
1490 -- raise error as does not exist as lookup
1491 --
1492 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1493 hr_utility.raise_error;
1494 --
1495 end if;
1496 --
1497 end if;
1498 --
1499 hr_utility.set_location('Leaving:'||l_proc,10);
1500 --
1501 end chk_pay_term_end_day_cd;
1502 --
1503 -- ----------------------------------------------------------------------------
1504 -- |------< chk_overlap_unit_cd >------|
1505 -- ----------------------------------------------------------------------------
1506 --
1507 -- Description
1508 -- This procedure is used to check that the lookup value is valid.
1509 --
1510 -- Pre Conditions
1511 -- None.
1512 --
1513 -- In Parameters
1514 -- position_transaction_id PK of record being inserted or updated.
1515 -- overlap_unit_cd Value of lookup code.
1516 -- effective_date effective date
1517 -- object_version_number Object version number of record being
1518 -- inserted or updated.
1519 --
1520 -- Post Success
1521 -- Processing continues
1522 --
1523 -- Post Failure
1524 -- Error handled by procedure
1525 --
1526 -- Access Status
1527 -- Internal table handler use only.
1528 --
1529 Procedure chk_overlap_unit_cd(p_position_transaction_id in number,
1530 p_overlap_unit_cd in varchar2,
1531 p_effective_date in date,
1532 p_object_version_number in number) is
1533 --
1534 l_proc varchar2(72) := g_package||'chk_overlap_unit_cd';
1535 l_api_updating boolean;
1536 --
1537 Begin
1538 --
1539 hr_utility.set_location('Entering:'||l_proc, 5);
1540 --
1541 l_api_updating := pqh_ptx_shd.api_updating
1542 (p_position_transaction_id => p_position_transaction_id,
1543 p_object_version_number => p_object_version_number);
1544 --
1545 if (l_api_updating
1546 and p_overlap_unit_cd
1547 <> nvl(pqh_ptx_shd.g_old_rec.overlap_unit_cd,hr_api.g_varchar2)
1548 or not l_api_updating)
1549 and p_overlap_unit_cd is not null then
1550 --
1551 -- check if value of lookup falls within lookup type.
1552 --
1553 if hr_api.not_exists_in_hr_lookups
1554 (p_lookup_type => 'FREQUENCY',
1555 p_lookup_code => p_overlap_unit_cd,
1556 p_effective_date => p_effective_date) then
1557 --
1558 -- raise error as does not exist as lookup
1559 --
1560 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1561 hr_utility.raise_error;
1562 --
1563 end if;
1564 --
1565 end if;
1566 --
1567 hr_utility.set_location('Leaving:'||l_proc,10);
1568 --
1569 end chk_overlap_unit_cd;
1570 --
1571 -- ----------------------------------------------------------------------------
1572 -- |------< chk_bargaining_unit_cd >------|
1573 -- ----------------------------------------------------------------------------
1574 --
1575 -- Description
1576 -- This procedure is used to check that the lookup value is valid.
1577 --
1578 -- Pre Conditions
1579 -- None.
1580 --
1581 -- In Parameters
1582 -- position_transaction_id PK of record being inserted or updated.
1583 -- bargaining_unit_cd Value of lookup code.
1584 -- effective_date effective date
1585 -- object_version_number Object version number of record being
1586 -- inserted or updated.
1587 --
1588 -- Post Success
1589 -- Processing continues
1590 --
1591 -- Post Failure
1592 -- Error handled by procedure
1593 --
1594 -- Access Status
1595 -- Internal table handler use only.
1596 --
1597 Procedure chk_bargaining_unit_cd(p_position_transaction_id in number,
1598 p_bargaining_unit_cd in varchar2,
1599 p_effective_date in date,
1600 p_object_version_number in number) is
1601 --
1602 l_proc varchar2(72) := g_package||'chk_bargaining_unit_cd';
1603 l_api_updating boolean;
1604 --
1605 Begin
1606 --
1607 hr_utility.set_location('Entering:'||l_proc, 5);
1608 --
1609 l_api_updating := pqh_ptx_shd.api_updating
1610 (p_position_transaction_id => p_position_transaction_id,
1611 p_object_version_number => p_object_version_number);
1612 --
1613 if (l_api_updating
1614 and p_bargaining_unit_cd
1615 <> nvl(pqh_ptx_shd.g_old_rec.bargaining_unit_cd,hr_api.g_varchar2)
1616 or not l_api_updating)
1617 and p_bargaining_unit_cd is not null then
1618 --
1619 -- check if value of lookup falls within lookup type.
1620 --
1621 if hr_api.not_exists_in_hr_lookups
1622 (p_lookup_type => 'BARGAINING_UNIT_CODE',
1623 p_lookup_code => p_bargaining_unit_cd,
1624 p_effective_date => p_effective_date) then
1625 --
1626 -- raise error as does not exist as lookup
1627 --
1628 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1629 hr_utility.raise_error;
1630 --
1631 end if;
1632 --
1633 end if;
1634 --
1635 hr_utility.set_location('Leaving:'||l_proc,10);
1636 --
1637 end chk_bargaining_unit_cd;
1638 --
1639 -- ----------------------------------------------------------------------------
1640 -- |--------------------------< chk_extended_pay >--------------------------|
1641 -- ----------------------------------------------------------------------------
1642 Procedure chk_extended_pay
1643 (p_position_transaction_id in number
1644 ,p_work_period_type_cd in varchar2
1645 ,p_term_start_day_cd in varchar2
1646 ,p_term_start_month_cd in varchar2
1647 ,p_pay_term_end_day_cd in varchar2
1648 ,p_pay_term_end_month_cd in varchar2
1649 ,p_work_term_end_day_cd in varchar2
1650 ,p_work_term_end_month_cd in varchar2 ) is
1651 --
1652 l_proc varchar2(30):='chk_extended_flag';
1653 Begin
1654 --
1655 hr_utility.set_location('Entering:'||l_proc, 5);
1656 --
1657 if p_work_period_type_cd = 'Y' then
1658 /*
1659 if p_pay_term_end_day_cd is null or
1660 p_pay_term_end_month_cd is null or
1661 p_work_term_end_day_cd is null or
1662 p_work_term_end_month_cd is null then
1663 --
1664 hr_utility.set_message(800,'HR_PAY_WORK_TERM_MUST_BE_ENTR');
1665 hr_utility.raise_error;
1666 */
1667 if (( (p_pay_term_end_day_cd is null and
1668 p_pay_term_end_month_cd is not null) or
1669 (p_pay_term_end_day_cd is not null and
1670 p_pay_term_end_month_cd is null)) or
1671 ( (p_term_start_day_cd is null and
1672 p_term_start_month_cd is not null) or
1673 (p_term_start_day_cd is not null and
1674 p_term_start_month_cd is null))) then
1675 --
1676 hr_utility.set_message(800,'HR_INVALID_PAY_TERM');
1677 hr_utility.raise_error;
1678 end if;
1679 if ( (p_work_term_end_day_cd is null and
1680 p_work_term_end_month_cd is not null) or
1681 (p_work_term_end_day_cd is not null and
1682 p_work_term_end_month_cd is null)) then
1683 --
1684 hr_utility.set_message(800,'HR_INVALID_WORK_TERM');
1685 hr_utility.raise_error;
1686 end if;
1687 else
1688 if p_pay_term_end_day_cd is not null or
1689 p_pay_term_end_month_cd is not null or
1690 p_term_start_day_cd is not null or
1691 p_term_start_month_cd is not null or
1692 p_work_term_end_day_cd is not null or
1693 p_work_term_end_month_cd is not null then
1694 --
1695 hr_utility.set_message(800,'HR_PAY_WORK_TERM_MUST_BE_NULL');
1696 hr_utility.raise_error;
1697 end if;
1698 end if;
1699 --
1700 end chk_extended_pay;
1701 --
1702 -- ----------------------------------------------------------------------------
1703 -- |--------------------------< permit_extended_pay >-------------------------|
1704 -- ----------------------------------------------------------------------------
1705 function permit_extended_pay(p_position_transaction_id varchar2) return boolean is
1706 l_proc varchar2(100) :='PERMIT_EXTENDED_PAY';
1707 l_position_family varchar2(100);
1708 l_chk boolean := false;
1709 cursor c1 is
1710 select information3
1711 from pqh_ptx_extra_info
1712 where position_transaction_id = p_position_transaction_id
1713 and information_type = 'PER_FAMILY'
1714 and information3 in ('ACADEMIC','FACULTY');
1715 begin
1716 hr_utility.set_location('Entering:'||l_proc,10);
1717 if p_position_transaction_id is not null then
1718 open c1;
1719 fetch c1 into l_position_family;
1720 if c1%found then
1721 hr_utility.set_location('Academic/Faculty Position Extra info Found:'||l_proc,10);
1722 close c1;
1723 return true;
1724 else
1725 close c1;
1726 hr_utility.set_location('Academic/Faculty Position Extra info not Found:'||l_proc,10);
1727 return false;
1728 end if;
1729 else
1730 return(false);
1731 end if;
1732 hr_utility.set_location('Leaving:'||l_proc,20);
1733 end;
1734
1735 --
1736 -- ----------------------------------------------------------------------------
1737 -- |--------------------------< chk_extended_pay_permit >---------------------|
1738 -- ----------------------------------------------------------------------------
1739 procedure chk_extended_pay_permit
1740 (p_position_transaction_id in number
1741 ,p_work_period_type_cd in varchar2
1742 ,p_object_version_number in number
1743 ) is
1744 l_proc varchar2(100):='chk_extended_pay_permit';
1745 l_api_updating boolean;
1746 l_permit_extended_pay boolean;
1747 begin
1748 hr_utility.set_location('Entering:'||l_proc,10);
1749 l_api_updating := pqh_ptx_shd.api_updating
1750 (p_position_transaction_id => p_position_transaction_id
1751 ,p_object_version_number => p_object_version_number);
1752
1753 if (((l_api_updating and p_work_period_type_cd
1754 <> nvl(pqh_ptx_shd.g_old_rec.work_period_type_cd,hr_api.g_varchar2)) or not l_api_updating)
1755 and nvl(p_WORK_PERIOD_TYPE_CD,'N') = 'Y') then
1756 hr_utility.set_location('Check permit_extended_pay:'||l_proc,10);
1757 l_permit_extended_pay := permit_extended_pay(p_position_transaction_id => p_position_transaction_id);
1758 hr_utility.set_location('Checking permit_extended_pay complete:'||l_proc,10);
1759 if (l_permit_extended_pay = false) then
1760 --Position family is neither Academic nor Faculty, so Extended pay cannot be permitted.
1761 fnd_message.set_name(800,'HR_INV_EXTD_PAY_PERMIT');
1762 fnd_message.raise_error;
1763 end if;
1764 end if;
1765 hr_utility.set_location('Leaving:'||l_proc,20);
1766 end;
1767 --
1768 --
1769 -- ----------------------------------------------------------------------------
1770 -- |--------------------------< chk_end_dates >---------------------|
1771 -- ----------------------------------------------------------------------------
1772 --
1773 procedure chk_end_dates
1774 (
1775 p_position_transaction_id in number
1776 ,position_id in number
1777 ,availability_status_id in number
1778 ,p_effective_date in date
1779 ,current_org_prop_end_date in date
1780 ,current_job_prop_end_date in date
1781 ,avail_status_prop_end_date in date
1782 ,earliest_hire_date in date
1783 ,fill_by_date in date
1784 ,proposed_date_for_layoff in date
1785 ,date_effective in date
1786 ,p_object_version_number in number)
1787 is
1788 l_avail_status_start_date date;
1789 l_proc varchar2(100) := 'pqh_ptx_bus.chk_end_dates';
1790 l_api_updating boolean;
1791 begin
1792
1793 --
1794 hr_utility.set_location('Entering:'||l_proc,5);
1795 --
1796 l_api_updating := pqh_ptx_shd.api_updating
1797 (p_position_transaction_id => p_position_transaction_id,
1798 p_object_version_number => p_object_version_number);
1799 --
1800 if (l_api_updating
1801 and nvl(date_effective,hr_api.g_date)
1802 <> nvl(pqh_ptx_shd.g_old_rec.date_effective,hr_api.g_date)
1803 or not l_api_updating) and
1804 date_effective is not null then
1805 --
1806 if current_org_prop_end_date < date_effective then
1807 hr_utility.set_message('800','PER_INVALID_ORG_PROP_END_DATE');
1808 hr_utility.raise_error;
1809 end if;
1810 --
1811 if current_job_prop_end_date < date_effective then
1812 hr_utility.set_message('800','PER_INVALID_JOB_PROP_END_DATE');
1813 hr_utility.raise_error;
1814 end if;
1815
1816 l_avail_status_start_date := hr_general.DECODE_AVAIL_STATUS_START_DATE (
1817 position_id
1818 ,availability_status_id
1819 ,p_effective_date ) ;
1820 if avail_status_prop_end_date < nvl(l_avail_status_start_date,date_effective) then
1821 hr_utility.set_message('800','PER_INVALID_STATUS_PROP_END_DT');
1822 hr_utility.raise_error;
1823 end if;
1824
1825 if earliest_hire_date < date_effective then
1826 hr_utility.set_message('800','PER_INVALID_EARLIEST_HIRE_DATE');
1827 hr_utility.raise_error;
1828 end if;
1829 if fill_by_date < nvl(earliest_hire_date, date_effective) then
1830 hr_utility.set_message('800','PER_INVALID_FILL_BY_DATE');
1831 hr_utility.set_message_token('VALID_DATE',nvl(earliest_hire_date, date_effective));
1832 hr_utility.raise_error;
1833 end if;
1834 if proposed_date_for_layoff <= date_effective then
1835 hr_utility.set_message('800','PER_INVALID_PROP_DT_FOR_LAYOFF');
1836 hr_utility.raise_error;
1837 end if;
1838 end if;
1839 --
1840 if (l_api_updating
1841 and nvl(current_org_prop_end_date,hr_api.g_date)
1842 <> nvl(pqh_ptx_shd.g_old_rec.current_org_prop_end_date,hr_api.g_date)
1843 or not l_api_updating) and
1844 current_org_prop_end_date is not null then
1845 --
1846 if current_org_prop_end_date < date_effective then
1847 hr_utility.set_message('800','PER_INVALID_ORG_PROP_END_DATE');
1848 hr_utility.raise_error;
1849 end if;
1850 end if;
1851 --
1852 if (l_api_updating
1853 and nvl(current_job_prop_end_date,hr_api.g_date)
1854 <> nvl(pqh_ptx_shd.g_old_rec.current_job_prop_end_date,hr_api.g_date)
1855 or not l_api_updating) and
1856 current_job_prop_end_date is not null then
1857 --
1858 if current_job_prop_end_date < date_effective then
1859 hr_utility.set_message('800','PER_INVALID_JOB_PROP_END_DATE');
1860 hr_utility.raise_error;
1861 end if;
1862 end if;
1863 --
1864 if (l_api_updating
1865 and nvl(avail_status_prop_end_date,hr_api.g_date)
1866 <> nvl(pqh_ptx_shd.g_old_rec.avail_status_prop_end_date,hr_api.g_date)
1867 or not l_api_updating) and
1868 avail_status_prop_end_date is not null then
1869 --
1870 l_avail_status_start_date := hr_general.DECODE_AVAIL_STATUS_START_DATE (
1871 position_id
1872 ,availability_status_id
1873 ,p_effective_date ) ;
1874 if avail_status_prop_end_date < nvl(l_avail_status_start_date,date_effective) then
1875 hr_utility.set_message('800','PER_INVALID_STATUS_PROP_END_DT');
1876 hr_utility.raise_error;
1877 end if;
1878 end if;
1879 --
1880 if (l_api_updating
1881 and nvl(earliest_hire_date,hr_api.g_date)
1882 <> nvl(pqh_ptx_shd.g_old_rec.earliest_hire_date,hr_api.g_date)
1883 or not l_api_updating) and
1884 earliest_hire_date is not null then
1885 --
1886 if earliest_hire_date < date_effective then
1887 hr_utility.set_message('800','PER_INVALID_EARLIEST_HIRE_DATE');
1888 hr_utility.raise_error;
1889 end if;
1890 --
1891 if fill_by_date < nvl(earliest_hire_date, date_effective) then
1892 hr_utility.set_message('800','PER_INVALID_FILL_BY_DATE');
1893 hr_utility.set_message_token('VALID_DATE',nvl(earliest_hire_date, date_effective));
1894 hr_utility.raise_error;
1895 end if;
1896 end if;
1897 --
1898 if (l_api_updating
1899 and nvl(fill_by_date,hr_api.g_date)
1900 <> nvl(pqh_ptx_shd.g_old_rec.fill_by_date,hr_api.g_date)
1901 or not l_api_updating) and
1902 fill_by_date is not null then
1903 --
1904 if fill_by_date < nvl(earliest_hire_date, date_effective) then
1905 hr_utility.set_message('800','PER_INVALID_FILL_BY_DATE');
1906 hr_utility.set_message_token('VALID_DATE',nvl(earliest_hire_date, date_effective));
1907 hr_utility.raise_error;
1908 end if;
1909 end if;
1910 --
1911 if (l_api_updating
1912 and nvl(fill_by_date,hr_api.g_date)
1913 <> nvl(pqh_ptx_shd.g_old_rec.fill_by_date,hr_api.g_date)
1914 or not l_api_updating) and
1915 fill_by_date is not null then
1916 --
1917 if proposed_date_for_layoff <= date_effective then
1918 hr_utility.set_message('800','PER_INVALID_PROP_DT_FOR_LAYOFF');
1919 hr_utility.raise_error;
1920 end if;
1921 end if;
1922 end chk_end_dates;
1923 --
1924 --
1925 -- ----------------------------------------------------------------------------
1926 -- |--------------------------< chk_seasonal_poi >--------------------------|
1927 -- ----------------------------------------------------------------------------
1928 procedure chk_seasonal_poi
1929 (p_position_transaction_id in number
1930 ,p_seasonal_flag in varchar2
1931 ,p_object_version_number in number) is
1932 l_dummy varchar2(1);
1933 l_api_updating boolean;
1934
1935 cursor c_seasonal is
1936 select 'X'
1937 from pqh_ptx_extra_info
1938 where position_transaction_id = nvl(p_position_transaction_id,-1)
1939 and information_type = 'PER_SEASONAL';
1940 begin
1941 l_api_updating := pqh_ptx_shd.api_updating
1942 (p_position_transaction_id => p_position_transaction_id
1943 ,p_object_version_number => p_object_version_number);
1944
1945 if (l_api_updating
1946 and p_seasonal_flag
1947 <> nvl(pqh_ptx_shd.g_old_rec.seasonal_flag,hr_api.g_varchar2)
1948 and (p_seasonal_flag='N' or p_seasonal_flag is null)) then
1949 open c_seasonal;
1950 fetch c_seasonal into l_dummy;
1951 if c_seasonal%found then
1952 close c_seasonal;
1953 hr_utility.set_message(800,'HR_INV_SEASONAL_FLAG');
1954 hr_utility.raise_error;
1955 end if;
1956 close c_seasonal;
1957 end if;
1958 end;
1959 -- ----------------------------------------------------------------------------
1960 -- |--------------------------< chk_overlap_poi >--------------------------|
1961 -- ----------------------------------------------------------------------------
1962 procedure chk_overlap_poi
1963 (p_position_transaction_id in number
1964 ,p_overlap_period in number
1965 ,p_object_version_number in number) is
1966 l_proc varchar2(100) :='chk_overlap_poi';
1967 l_dummy varchar2(1);
1968 l_api_updating boolean;
1969 --
1970 cursor c_overlap is
1971 select 'X'
1972 from pqh_ptx_extra_info
1973 where position_transaction_id = p_position_transaction_id
1974 and information_type = 'PER_OVERLAP';
1975 begin
1976 hr_utility.set_location('Entering:'||l_proc,10);
1977 l_api_updating := pqh_ptx_shd.api_updating
1978 (p_position_transaction_id => p_position_transaction_id
1979 ,p_object_version_number => p_object_version_number);
1980
1981 if (l_api_updating
1982 and nvl(p_overlap_period,-1)
1983 <> nvl(pqh_ptx_shd.g_old_rec.overlap_period,hr_api.g_number)
1984 and p_overlap_period is null) then
1985 hr_utility.set_location('Checking for Overlap Dates in Position Extra Info:'||l_proc,20);
1986 open c_overlap;
1987 fetch c_overlap into l_dummy;
1988 hr_utility.set_location('Checked for Overlap Dates in Position Extra Info:'||l_proc,30);
1989 if c_overlap%found then
1990 hr_utility.set_location('Overlap Dates Found in Position Extra Info:'||l_proc,40);
1991 close c_overlap;
1992 hr_utility.set_message(800,'HR_INV_OVERLAP_PERIOD');
1993 hr_utility.raise_error;
1994 end if;
1995 hr_utility.set_location('Overlap Dates not Found in Position Extra Info:'||l_proc,40);
1996 close c_overlap;
1997 end if;
1998 hr_utility.set_location('Leaving:'||l_proc,20);
1999 end;
2000 --
2001 --
2002 -- ----------------------------------------------------------------------------
2003 -- |---------------------------< insert_validate >----------------------------|
2004 -- ----------------------------------------------------------------------------
2005 Procedure insert_validate(p_rec in pqh_ptx_shd.g_rec_type
2006 ,p_effective_date in date) is
2007 --
2008 l_proc varchar2(72) := g_package||'insert_validate';
2009 --
2010 Begin
2011 hr_utility.set_location('Entering:'||l_proc, 5);
2012 --
2013 -- Call all supporting business operations
2014 --
2015 chk_position_transaction_id
2016 (p_position_transaction_id => p_rec.position_transaction_id,
2017 p_object_version_number => p_rec.object_version_number);
2018 --
2019 chk_organization_id
2020 (p_position_transaction_id => p_rec.position_transaction_id,
2021 p_organization_id => p_rec.organization_id,
2022 p_object_version_number => p_rec.object_version_number);
2023 --
2024 chk_position_definition_id
2025 (p_position_transaction_id => p_rec.position_transaction_id,
2026 p_position_definition_id => p_rec.position_definition_id,
2027 p_object_version_number => p_rec.object_version_number);
2028 --
2029 chk_job_id
2030 (p_position_transaction_id => p_rec.position_transaction_id,
2031 p_job_id => p_rec.job_id,
2032 p_object_version_number => p_rec.object_version_number);
2033 --
2034 chk_location_id
2035 (p_position_transaction_id => p_rec.position_transaction_id,
2036 p_location_id => p_rec.location_id,
2037 p_object_version_number => p_rec.object_version_number);
2038 --
2039 chk_availability_status_id
2040 (p_position_transaction_id => p_rec.position_transaction_id,
2041 p_availability_status_id => p_rec.availability_status_id,
2042 p_object_version_number => p_rec.object_version_number);
2043 --
2044 chk_end_dates
2045 (
2046 p_position_transaction_id => p_rec.position_transaction_id
2047 ,position_id =>p_rec.position_id
2048 ,availability_status_id =>p_rec.availability_status_id
2049 ,p_effective_date =>p_rec.action_date
2050 ,current_org_prop_end_date =>p_rec.current_org_prop_end_date
2051 ,current_job_prop_end_date =>p_rec.current_job_prop_end_date
2052 ,avail_status_prop_end_date =>p_rec.avail_status_prop_end_date
2053 ,earliest_hire_date =>p_rec.earliest_hire_date
2054 ,fill_by_date =>p_rec.fill_by_date
2055 ,proposed_date_for_layoff =>p_rec.proposed_date_for_layoff
2056 ,date_effective =>p_rec.date_effective
2057 ,p_object_version_number => p_rec.object_version_number);
2058 --
2059 chk_entry_grade_id
2060 (p_position_transaction_id => p_rec.position_transaction_id,
2061 p_entry_grade_id => p_rec.entry_grade_id,
2062 p_object_version_number => p_rec.object_version_number);
2063 --
2064 chk_work_term_end_month_cd
2065 (p_position_transaction_id => p_rec.position_transaction_id,
2066 p_work_term_end_month_cd => p_rec.work_term_end_month_cd,
2067 p_effective_date => p_effective_date,
2068 p_object_version_number => p_rec.object_version_number);
2069 --
2070 chk_work_term_end_day_cd
2071 (p_position_transaction_id => p_rec.position_transaction_id,
2072 p_work_term_end_day_cd => p_rec.work_term_end_day_cd,
2073 p_effective_date => p_effective_date,
2074 p_object_version_number => p_rec.object_version_number);
2075 --
2076 chk_work_period_type_cd
2077 (p_position_transaction_id => p_rec.position_transaction_id,
2078 p_work_period_type_cd => p_rec.work_period_type_cd,
2079 p_effective_date => p_effective_date,
2080 p_object_version_number => p_rec.object_version_number);
2081 --
2082 chk_works_council_approval_flg
2083 (p_position_transaction_id => p_rec.position_transaction_id,
2084 p_works_council_approval_flag => p_rec.works_council_approval_flag,
2085 p_effective_date => p_effective_date,
2086 p_object_version_number => p_rec.object_version_number);
2087 --
2088 chk_term_start_month_cd
2089 (p_position_transaction_id => p_rec.position_transaction_id,
2090 p_term_start_month_cd => p_rec.term_start_month_cd,
2091 p_effective_date => p_effective_date,
2092 p_object_version_number => p_rec.object_version_number);
2093 --
2094 chk_term_start_day_cd
2095 (p_position_transaction_id => p_rec.position_transaction_id,
2096 p_term_start_day_cd => p_rec.term_start_day_cd,
2097 p_effective_date => p_effective_date,
2098 p_object_version_number => p_rec.object_version_number);
2099 --
2100 chk_seasonal_flag
2101 (p_position_transaction_id => p_rec.position_transaction_id,
2102 p_seasonal_flag => p_rec.seasonal_flag,
2103 p_effective_date => p_effective_date,
2104 p_object_version_number => p_rec.object_version_number);
2105 --
2106 chk_review_flag
2107 (p_position_transaction_id => p_rec.position_transaction_id,
2108 p_review_flag => p_rec.review_flag,
2109 p_position_id => p_rec.position_id,
2110 p_effective_date => p_effective_date,
2111 p_object_version_number => p_rec.object_version_number);
2112 --
2113 chk_replacement_required_flag
2114 (p_position_transaction_id => p_rec.position_transaction_id,
2115 p_replacement_required_flag => p_rec.replacement_required_flag,
2116 p_effective_date => p_effective_date,
2117 p_object_version_number => p_rec.object_version_number);
2118 --
2119 chk_probation_period_unit_cd
2120 (p_position_transaction_id => p_rec.position_transaction_id,
2121 p_probation_period_unit_cd => p_rec.probation_period_unit_cd,
2122 p_effective_date => p_effective_date,
2123 p_object_version_number => p_rec.object_version_number);
2124 --
2125 chk_permit_recruitment_flag
2126 (p_position_transaction_id => p_rec.position_transaction_id,
2127 p_permit_recruitment_flag => p_rec.permit_recruitment_flag,
2128 p_effective_date => p_effective_date,
2129 p_object_version_number => p_rec.object_version_number);
2130 --
2131 chk_permanent_temporary_flag
2132 (p_position_transaction_id => p_rec.position_transaction_id,
2133 p_permanent_temporary_flag => p_rec.permanent_temporary_flag,
2134 p_effective_date => p_effective_date,
2135 p_object_version_number => p_rec.object_version_number);
2136 --
2137 chk_pay_term_end_month_cd
2138 (p_position_transaction_id => p_rec.position_transaction_id,
2139 p_pay_term_end_month_cd => p_rec.pay_term_end_month_cd,
2140 p_effective_date => p_effective_date,
2141 p_object_version_number => p_rec.object_version_number);
2142 --
2143 chk_pay_term_end_day_cd
2144 (p_position_transaction_id => p_rec.position_transaction_id,
2145 p_pay_term_end_day_cd => p_rec.pay_term_end_day_cd,
2146 p_effective_date => p_effective_date,
2147 p_object_version_number => p_rec.object_version_number);
2148 --
2149 chk_overlap_unit_cd
2150 (p_position_transaction_id => p_rec.position_transaction_id,
2151 p_overlap_unit_cd => p_rec.overlap_unit_cd,
2152 p_effective_date => p_effective_date,
2153 p_object_version_number => p_rec.object_version_number);
2154 --
2155 chk_bargaining_unit_cd
2156 (p_position_transaction_id => p_rec.position_transaction_id,
2157 p_bargaining_unit_cd => p_rec.bargaining_unit_cd,
2158 p_effective_date => p_effective_date,
2159 p_object_version_number => p_rec.object_version_number);
2160 --
2161 --
2162 chk_extended_pay
2163 (p_position_transaction_id => p_rec.position_transaction_id
2164 ,p_work_period_type_cd => p_rec.work_period_type_cd
2165 ,p_term_start_day_cd => p_rec.term_start_day_cd
2166 ,p_term_start_month_cd => p_rec.term_start_month_cd
2167 ,p_pay_term_end_day_cd => p_rec.pay_term_end_day_cd
2168 ,p_pay_term_end_month_cd => p_rec.pay_term_end_month_cd
2169 ,p_work_term_end_day_cd => p_rec.work_term_end_day_cd
2170 ,p_work_term_end_month_cd => p_rec.work_term_end_month_cd
2171 );
2172 --
2173 /*
2174 chk_extended_pay_permit
2175 (p_position_transaction_id => p_rec.position_transaction_id
2176 ,p_work_period_type_cd => p_rec.work_period_type_cd
2177 ,p_object_version_number => p_rec.object_version_number
2178 );
2179 */
2180 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2181 --
2182 hr_utility.set_location(' Leaving:'||l_proc, 10);
2183 End insert_validate;
2184 --
2185 -- ----------------------------------------------------------------------------
2186 -- |---------------------------< update_validate >----------------------------|
2187 -- ----------------------------------------------------------------------------
2188 Procedure update_validate(p_rec in pqh_ptx_shd.g_rec_type
2189 ,p_effective_date in date) is
2190 --
2191 l_proc varchar2(72) := g_package||'update_validate';
2192 --
2193 Begin
2194 hr_utility.set_location('Entering:'||l_proc, 5);
2195 --
2196 -- Call all supporting business operations
2197 --
2198 chk_position_transaction_id
2199 (p_position_transaction_id => p_rec.position_transaction_id,
2200 p_object_version_number => p_rec.object_version_number);
2201 --
2202 chk_organization_id
2203 (p_position_transaction_id => p_rec.position_transaction_id,
2204 p_organization_id => p_rec.organization_id,
2205 p_object_version_number => p_rec.object_version_number);
2206 --
2207 chk_position_definition_id
2208 (p_position_transaction_id => p_rec.position_transaction_id,
2209 p_position_definition_id => p_rec.position_definition_id,
2210 p_object_version_number => p_rec.object_version_number);
2211 --
2212 chk_job_id
2213 (p_position_transaction_id => p_rec.position_transaction_id,
2214 p_job_id => p_rec.job_id,
2215 p_object_version_number => p_rec.object_version_number);
2216 --
2217 chk_location_id
2218 (p_position_transaction_id => p_rec.position_transaction_id,
2219 p_location_id => p_rec.location_id,
2220 p_object_version_number => p_rec.object_version_number);
2221 --
2222 chk_availability_status_id
2223 (p_position_transaction_id => p_rec.position_transaction_id,
2224 p_availability_status_id => p_rec.availability_status_id,
2225 p_object_version_number => p_rec.object_version_number);
2226 --
2227 chk_end_dates
2228 (
2229 p_position_transaction_id => p_rec.position_transaction_id
2230 ,position_id =>p_rec.position_id
2231 ,availability_status_id =>p_rec.availability_status_id
2232 ,p_effective_date =>p_rec.action_date
2233 ,current_org_prop_end_date =>p_rec.current_org_prop_end_date
2234 ,current_job_prop_end_date =>p_rec.current_job_prop_end_date
2235 ,avail_status_prop_end_date =>p_rec.avail_status_prop_end_date
2236 ,earliest_hire_date =>p_rec.earliest_hire_date
2237 ,fill_by_date =>p_rec.fill_by_date
2238 ,proposed_date_for_layoff =>p_rec.proposed_date_for_layoff
2239 ,date_effective =>p_rec.date_effective
2240 ,p_object_version_number => p_rec.object_version_number);
2241 --
2242 chk_entry_grade_id
2243 (p_position_transaction_id => p_rec.position_transaction_id,
2244 p_entry_grade_id => p_rec.entry_grade_id,
2245 p_object_version_number => p_rec.object_version_number);
2246 --
2247 chk_work_term_end_month_cd
2248 (p_position_transaction_id => p_rec.position_transaction_id,
2249 p_work_term_end_month_cd => p_rec.work_term_end_month_cd,
2250 p_effective_date => p_effective_date,
2251 p_object_version_number => p_rec.object_version_number);
2252 --
2253 chk_work_term_end_day_cd
2254 (p_position_transaction_id => p_rec.position_transaction_id,
2255 p_work_term_end_day_cd => p_rec.work_term_end_day_cd,
2256 p_effective_date => p_effective_date,
2257 p_object_version_number => p_rec.object_version_number);
2258 --
2259 chk_work_period_type_cd
2260 (p_position_transaction_id => p_rec.position_transaction_id,
2261 p_work_period_type_cd => p_rec.work_period_type_cd,
2262 p_effective_date => p_effective_date,
2263 p_object_version_number => p_rec.object_version_number);
2264 --
2265 chk_works_council_approval_flg
2266 (p_position_transaction_id => p_rec.position_transaction_id,
2267 p_works_council_approval_flag => p_rec.works_council_approval_flag,
2268 p_effective_date => p_effective_date,
2269 p_object_version_number => p_rec.object_version_number);
2270 --
2271 chk_term_start_month_cd
2272 (p_position_transaction_id => p_rec.position_transaction_id,
2273 p_term_start_month_cd => p_rec.term_start_month_cd,
2274 p_effective_date => p_effective_date,
2275 p_object_version_number => p_rec.object_version_number);
2276 --
2277 chk_term_start_day_cd
2278 (p_position_transaction_id => p_rec.position_transaction_id,
2279 p_term_start_day_cd => p_rec.term_start_day_cd,
2280 p_effective_date => p_effective_date,
2281 p_object_version_number => p_rec.object_version_number);
2282 --
2283 chk_seasonal_flag
2284 (p_position_transaction_id => p_rec.position_transaction_id,
2285 p_seasonal_flag => p_rec.seasonal_flag,
2286 p_effective_date => p_effective_date,
2287 p_object_version_number => p_rec.object_version_number);
2288 --
2289 chk_review_flag
2290 (p_position_transaction_id => p_rec.position_transaction_id,
2291 p_review_flag => p_rec.review_flag,
2292 p_position_id => p_rec.position_id,
2293 p_effective_date => p_effective_date,
2294 p_object_version_number => p_rec.object_version_number);
2295 --
2296 chk_replacement_required_flag
2297 (p_position_transaction_id => p_rec.position_transaction_id,
2298 p_replacement_required_flag => p_rec.replacement_required_flag,
2299 p_effective_date => p_effective_date,
2300 p_object_version_number => p_rec.object_version_number);
2301 --
2302 chk_probation_period_unit_cd
2303 (p_position_transaction_id => p_rec.position_transaction_id,
2304 p_probation_period_unit_cd => p_rec.probation_period_unit_cd,
2305 p_effective_date => p_effective_date,
2306 p_object_version_number => p_rec.object_version_number);
2307 --
2308 chk_permit_recruitment_flag
2309 (p_position_transaction_id => p_rec.position_transaction_id,
2310 p_permit_recruitment_flag => p_rec.permit_recruitment_flag,
2311 p_effective_date => p_effective_date,
2312 p_object_version_number => p_rec.object_version_number);
2313 --
2314 chk_permanent_temporary_flag
2315 (p_position_transaction_id => p_rec.position_transaction_id,
2316 p_permanent_temporary_flag => p_rec.permanent_temporary_flag,
2317 p_effective_date => p_effective_date,
2318 p_object_version_number => p_rec.object_version_number);
2319 --
2320 chk_pay_term_end_month_cd
2321 (p_position_transaction_id => p_rec.position_transaction_id,
2322 p_pay_term_end_month_cd => p_rec.pay_term_end_month_cd,
2323 p_effective_date => p_effective_date,
2324 p_object_version_number => p_rec.object_version_number);
2325 --
2326 chk_pay_term_end_day_cd
2327 (p_position_transaction_id => p_rec.position_transaction_id,
2328 p_pay_term_end_day_cd => p_rec.pay_term_end_day_cd,
2329 p_effective_date => p_effective_date,
2330 p_object_version_number => p_rec.object_version_number);
2331 --
2332 chk_overlap_unit_cd
2333 (p_position_transaction_id => p_rec.position_transaction_id,
2334 p_overlap_unit_cd => p_rec.overlap_unit_cd,
2335 p_effective_date => p_effective_date,
2336 p_object_version_number => p_rec.object_version_number);
2337 --
2338 chk_bargaining_unit_cd
2339 (p_position_transaction_id => p_rec.position_transaction_id,
2340 p_bargaining_unit_cd => p_rec.bargaining_unit_cd,
2341 p_effective_date => p_effective_date,
2342 p_object_version_number => p_rec.object_version_number);
2343 --
2344 chk_extended_pay
2345 (p_position_transaction_id => p_rec.position_transaction_id
2346 ,p_work_period_type_cd => p_rec.work_period_type_cd
2347 ,p_term_start_day_cd => p_rec.term_start_day_cd
2348 ,p_term_start_month_cd => p_rec.term_start_month_cd
2349 ,p_pay_term_end_day_cd => p_rec.pay_term_end_day_cd
2350 ,p_pay_term_end_month_cd => p_rec.pay_term_end_month_cd
2351 ,p_work_term_end_day_cd => p_rec.work_term_end_day_cd
2352 ,p_work_term_end_month_cd => p_rec.work_term_end_month_cd
2353 );
2354 --
2355 /*
2356 chk_seasonal_poi
2357 (p_position_transaction_id => p_rec.position_transaction_id
2358 ,p_seasonal_flag => p_rec.seasonal_flag
2359 ,p_object_version_number => p_rec.object_version_number);
2360 --
2361 hr_utility.set_location(l_proc, 480);
2362 --
2363 chk_overlap_poi
2364 (p_position_transaction_id => p_rec.position_transaction_id
2365 ,p_overlap_period => p_rec.overlap_period
2366 ,p_object_version_number => p_rec.object_version_number);
2367 --
2368 */
2369 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2370 --
2371 hr_utility.set_location(' Leaving:'||l_proc, 10);
2372 End update_validate;
2373 --
2374 -- ----------------------------------------------------------------------------
2375 -- |---------------------------< delete_validate >----------------------------|
2376 -- ----------------------------------------------------------------------------
2377 Procedure delete_validate(p_rec in pqh_ptx_shd.g_rec_type
2378 ,p_effective_date in date) is
2379 --
2380 l_proc varchar2(72) := g_package||'delete_validate';
2381 --
2382 Begin
2383 hr_utility.set_location('Entering:'||l_proc, 5);
2384 --
2385 -- Call all supporting business operations
2386 --
2387 hr_utility.set_location(' Leaving:'||l_proc, 10);
2388 End delete_validate;
2389 --
2390 --
2391 -- ---------------------------------------------------------------------------
2392 -- |---------------------< return_legislation_code >-------------------------|
2393 -- ---------------------------------------------------------------------------
2394 --
2395 function return_legislation_code
2396 (p_position_transaction_id in number) return varchar2 is
2397 --
2398 -- Declare cursor
2399 --
2400 cursor csr_leg_code is
2401 select a.legislation_code
2402 from per_business_groups a,
2403 pqh_position_transactions b
2404 where b.position_transaction_id = p_position_transaction_id
2405 and a.business_group_id = b.business_group_id;
2406 --
2407 -- Declare local variables
2408 --
2409 l_legislation_code varchar2(150);
2410 l_proc varchar2(72) := g_package||'return_legislation_code';
2411 --
2412 begin
2413 --
2414 hr_utility.set_location('Entering:'|| l_proc, 10);
2415 --
2416 -- Ensure that all the mandatory parameter are not null
2417 --
2418 hr_api.mandatory_arg_error(p_api_name => l_proc,
2419 p_argument => 'position_transaction_id',
2420 p_argument_value => p_position_transaction_id);
2421 --
2422 open csr_leg_code;
2423 --
2424 fetch csr_leg_code into l_legislation_code;
2425 --
2426 if csr_leg_code%notfound then
2427 --
2428 close csr_leg_code;
2429 --
2430 -- The primary key is invalid therefore we must error
2431 --
2432 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
2433 hr_utility.raise_error;
2434 --
2435 end if;
2436 --
2437 close csr_leg_code;
2438 --
2439 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2440 --
2441 return l_legislation_code;
2442 --
2443 end return_legislation_code;
2444 --
2445 end pqh_ptx_bus;