[Home] [Help]
PACKAGE BODY: APPS.PQH_RHT_BUS
Source
1 Package Body pqh_rht_bus as
2 /* $Header: pqrhtrhi.pkb 115.7 2002/12/06 18:08:02 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rht_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_routing_history_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 -- routing_history_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_routing_history_id(p_routing_history_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_routing_history_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_rht_shd.api_updating
47 (p_routing_history_id => p_routing_history_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_routing_history_id,hr_api.g_number)
52 <> pqh_rht_shd.g_old_rec.routing_history_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_routing_history_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_routing_history_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_pos_structure_version_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_routing_history_id PK
89 -- p_pos_structure_version_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_pos_structure_version_id (p_routing_history_id in number,
102 p_pos_structure_version_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_pos_structure_version_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from per_pos_structure_versions a
112 where a.pos_structure_version_id = p_pos_structure_version_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_rht_shd.api_updating
119 (p_routing_history_id => p_routing_history_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_pos_structure_version_id,hr_api.g_number)
124 <> nvl(pqh_rht_shd.g_old_rec.pos_structure_version_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_pos_structure_version_id is not null then
127 --
128 -- check if pos_structure_version_id value exists in per_pos_structure_versions 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 per_pos_structure_versions
138 -- table.
139 --
140 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_FK7');
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_pos_structure_version_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_forwarded_to_member_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_routing_history_id PK
165 -- p_forwarded_to_member_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_forwarded_to_member_id (p_routing_history_id in number,
178 p_forwarded_to_member_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_forwarded_to_member_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_routing_list_members a
188 where a.routing_list_member_id = p_forwarded_to_member_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_rht_shd.api_updating
195 (p_routing_history_id => p_routing_history_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_forwarded_to_member_id,hr_api.g_number)
200 <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_member_id,hr_api.g_number)
201 or not l_api_updating) and
202 p_forwarded_to_member_id is not null then
203 --
204 -- check if forwarded_to_member_id value exists in pqh_routing_list_members 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 pqh_routing_list_members
214 -- table.
215 --
216 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_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_forwarded_to_member_id;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |------< chk_forwarded_by_member_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_routing_history_id PK
241 -- p_forwarded_by_member_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_forwarded_by_member_id (p_routing_history_id in number,
254 p_forwarded_by_member_id in number,
255 p_object_version_number in number) is
256 --
257 l_proc varchar2(72) := g_package||'chk_forwarded_by_member_id';
258 l_api_updating boolean;
259 l_dummy varchar2(1);
260 --
261 cursor c1 is
262 select null
263 from pqh_routing_list_members a
264 where a.routing_list_member_id = p_forwarded_by_member_id;
265 --
266 Begin
267 --
268 hr_utility.set_location('Entering:'||l_proc,5);
269 --
270 l_api_updating := pqh_rht_shd.api_updating
271 (p_routing_history_id => p_routing_history_id,
272 p_object_version_number => p_object_version_number);
273 --
274 if (l_api_updating
275 and nvl(p_forwarded_by_member_id,hr_api.g_number)
276 <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_member_id,hr_api.g_number)
277 or not l_api_updating) and
278 p_forwarded_by_member_id is not null then
279 --
280 -- check if forwarded_by_member_id value exists in pqh_routing_list_members 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 pqh_routing_list_members
290 -- table.
291 --
292 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_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_forwarded_by_member_id;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |------< chk_transaction_category_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_routing_history_id PK
317 -- p_transaction_category_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_transaction_category_id (p_routing_history_id in number,
330 p_transaction_category_id in number,
331 p_object_version_number in number) is
332 --
333 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
334 l_api_updating boolean;
335 l_dummy varchar2(1);
336 --
337 cursor c1 is
338 select null
339 from pqh_transaction_categories a
340 where a.transaction_category_id = p_transaction_category_id;
341 --
342 Begin
343 --
344 hr_utility.set_location('Entering:'||l_proc,5);
345 --
346 l_api_updating := pqh_rht_shd.api_updating
347 (p_routing_history_id => p_routing_history_id,
348 p_object_version_number => p_object_version_number);
349 --
350 if (l_api_updating
351 and nvl(p_transaction_category_id,hr_api.g_number)
352 <> nvl(pqh_rht_shd.g_old_rec.transaction_category_id,hr_api.g_number)
353 or not l_api_updating) then
354 --
355 -- check if transaction_category_id value exists in pqh_transaction_categories table
356 --
357 open c1;
358 --
359 fetch c1 into l_dummy;
360 if c1%notfound then
361 --
362 close c1;
363 --
364 -- raise error as FK does not relate to PK in pqh_transaction_categories
365 -- table.
366 --
367 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_FK4');
368 --
369 end if;
370 --
371 close c1;
372 --
373 end if;
374 --
375 hr_utility.set_location('Leaving:'||l_proc,10);
376 --
377 End chk_transaction_category_id;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |------< chk_forwarded_to_position_id >------|
381 -- ----------------------------------------------------------------------------
382 --
383 -- Description
384 -- This procedure checks that a referenced foreign key actually exists
385 -- in the referenced table.
386 --
387 -- Pre-Conditions
388 -- None.
389 --
390 -- In Parameters
391 -- p_routing_history_id PK
392 -- p_forwarded_to_position_id ID of FK column
393 -- p_object_version_number object version number
394 --
395 -- Post Success
396 -- Processing continues
397 --
398 -- Post Failure
399 -- Error raised.
400 --
401 -- Access Status
402 -- Internal table handler use only.
403 --
404 Procedure chk_forwarded_to_position_id (p_routing_history_id in number,
405 p_forwarded_to_position_id in number,
406 p_object_version_number in number) is
407 --
408 l_proc varchar2(72) := g_package||'chk_forwarded_to_position_id';
409 l_api_updating boolean;
410 l_dummy varchar2(1);
411 --
412 cursor c1 is
413 select null
414 from per_all_positions a
415 where a.position_id = p_forwarded_to_position_id;
416 --
417 Begin
418 --
419 hr_utility.set_location('Entering:'||l_proc,5);
420 --
421 l_api_updating := pqh_rht_shd.api_updating
422 (p_routing_history_id => p_routing_history_id,
423 p_object_version_number => p_object_version_number);
424 --
425 if (l_api_updating
426 and nvl(p_forwarded_to_position_id,hr_api.g_number)
427 <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_position_id,hr_api.g_number)
428 or not l_api_updating) and
429 p_forwarded_to_position_id is not null then
430 --
431 -- check if forwarded_to_position_id value exists in per_all_positions table
432 --
433 open c1;
434 --
435 fetch c1 into l_dummy;
436 if c1%notfound then
437 --
438 close c1;
439 --
440 -- raise error as FK does not relate to PK in per_all_positions
441 -- table.
442 --
443 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_FK3');
444 --
445 end if;
446 --
447 close c1;
448 --
449 end if;
450 --
451 hr_utility.set_location('Leaving:'||l_proc,10);
452 --
453 End chk_forwarded_to_position_id;
454 --
455 -- ----------------------------------------------------------------------------
456 -- |------< chk_forwarded_by_position_id >------|
457 -- ----------------------------------------------------------------------------
458 --
459 -- Description
460 -- This procedure checks that a referenced foreign key actually exists
461 -- in the referenced table.
462 --
463 -- Pre-Conditions
464 -- None.
465 --
466 -- In Parameters
467 -- p_routing_history_id PK
468 -- p_forwarded_by_position_id ID of FK column
469 -- p_object_version_number object version number
470 --
471 -- Post Success
472 -- Processing continues
473 --
474 -- Post Failure
475 -- Error raised.
476 --
477 -- Access Status
478 -- Internal table handler use only.
479 --
480 Procedure chk_forwarded_by_position_id (p_routing_history_id in number,
481 p_forwarded_by_position_id in number,
482 p_object_version_number in number) is
483 --
484 l_proc varchar2(72) := g_package||'chk_forwarded_by_position_id';
485 l_api_updating boolean;
486 l_dummy varchar2(1);
487 --
488 cursor c1 is
489 select null
490 from per_all_positions a
491 where a.position_id = p_forwarded_by_position_id;
492 --
493 Begin
494 --
495 hr_utility.set_location('Entering:'||l_proc,5);
496 --
497 l_api_updating := pqh_rht_shd.api_updating
498 (p_routing_history_id => p_routing_history_id,
499 p_object_version_number => p_object_version_number);
500 --
501 if (l_api_updating
502 and nvl(p_forwarded_by_position_id,hr_api.g_number)
503 <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_position_id,hr_api.g_number)
504 or not l_api_updating) and
505 p_forwarded_by_position_id is not null then
506 --
507 -- check if forwarded_by_position_id value exists in per_all_positions table
508 --
509 open c1;
510 --
511 fetch c1 into l_dummy;
512 if c1%notfound then
513 --
514 close c1;
515 --
516 -- raise error as FK does not relate to PK in per_all_positions
517 -- table.
518 --
519 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_FK2');
520 --
521 end if;
522 --
523 close c1;
524 --
525 end if;
526 --
527 hr_utility.set_location('Leaving:'||l_proc,10);
528 --
529 End chk_forwarded_by_position_id;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |------< chk_routing_category_id >------|
533 -- ----------------------------------------------------------------------------
534 --
535 -- Description
536 -- This procedure checks that a referenced foreign key actually exists
537 -- in the referenced table.
538 --
539 -- Pre-Conditions
540 -- None.
541 --
542 -- In Parameters
543 -- p_routing_history_id PK
544 -- p_routing_category_id ID of FK column
545 -- p_object_version_number object version number
546 --
547 -- Post Success
548 -- Processing continues
549 --
550 -- Post Failure
551 -- Error raised.
552 --
553 -- Access Status
554 -- Internal table handler use only.
555 --
556 Procedure chk_routing_category_id (p_routing_history_id in number,
557 p_routing_category_id in number,
558 p_object_version_number in number) is
559 --
560 l_proc varchar2(72) := g_package||'chk_routing_category_id';
561 l_api_updating boolean;
562 l_dummy varchar2(1);
563 --
564 cursor c1 is
565 select null
566 from pqh_routing_categories a
567 where a.routing_category_id = p_routing_category_id;
568 --
569 Begin
570 --
571 hr_utility.set_location('Entering:'||l_proc,5);
572 --
573 l_api_updating := pqh_rht_shd.api_updating
574 (p_routing_history_id => p_routing_history_id,
575 p_object_version_number => p_object_version_number);
576 --
577 if (l_api_updating
578 and nvl(p_routing_category_id,hr_api.g_number)
579 <> nvl(pqh_rht_shd.g_old_rec.routing_category_id,hr_api.g_number)
580 or not l_api_updating)
581 and p_routing_category_id is not null then
582 --
583 -- check if routing_category_id value exists in pqh_routing_categories table
584 --
585 open c1;
586 --
587 fetch c1 into l_dummy;
588 if c1%notfound then
589 --
590 close c1;
591 --
592 -- raise error as FK does not relate to PK in pqh_routing_categories
593 -- table.
594 --
595 pqh_rht_shd.constraint_error('PQH_ROUTING_HISTORY_FK1');
596 --
597 end if;
598 --
599 close c1;
600 --
601 end if;
602 --
603 hr_utility.set_location('Leaving:'||l_proc,10);
604 --
605 End chk_routing_category_id;
606 --
607 -- ----------------------------------------------------------------------------
608 -- |------< chk_approval_cd >------|
609 -- ----------------------------------------------------------------------------
610 --
611 -- Description
612 -- This procedure is used to check that the lookup value is valid.
613 --
614 -- Pre Conditions
615 -- None.
616 --
617 -- In Parameters
618 -- routing_history_id PK of record being inserted or updated.
619 -- approval_cd Value of lookup code.
620 -- effective_date effective date
621 -- object_version_number Object version number of record being
622 -- inserted or updated.
623 --
624 -- Post Success
625 -- Processing continues
626 --
627 -- Post Failure
628 -- Error handled by procedure
629 --
630 -- Access Status
631 -- Internal table handler use only.
632 --
633 Procedure chk_approval_cd(p_routing_history_id in number,
634 p_approval_cd in varchar2,
635 p_effective_date in date,
636 p_object_version_number in number) is
637 --
638 l_proc varchar2(72) := g_package||'chk_approval_cd';
639 l_api_updating boolean;
640 --
641 Begin
642 --
643 hr_utility.set_location('Entering:'||l_proc, 5);
644 --
645 l_api_updating := pqh_rht_shd.api_updating
646 (p_routing_history_id => p_routing_history_id,
647 p_object_version_number => p_object_version_number);
648 --
649 if (l_api_updating
650 and p_approval_cd
651 <> nvl(pqh_rht_shd.g_old_rec.approval_cd,hr_api.g_varchar2)
652 or not l_api_updating)
653 and p_approval_cd is not null then
654 --
655 -- check if value of lookup falls within lookup type.
656 --
657 if hr_api.not_exists_in_hr_lookups
658 (p_lookup_type => 'PQH_APPROVAL_CD',
659 p_lookup_code => p_approval_cd,
660 p_effective_date => p_effective_date) then
661 --
662 -- raise error as does not exist as lookup
663 --
664 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
665 hr_utility.raise_error;
666 --
667 end if;
668 --
669 end if;
670 --
671 hr_utility.set_location('Leaving:'||l_proc,10);
672 --
673 end chk_approval_cd;
674 --
675 -- ----------------------------------------------------------------------------
676 -- |------< chk_user_action_cd >------|
677 -- ----------------------------------------------------------------------------
678 --
679 -- Description
680 -- This procedure is used to check that the lookup value is valid.
681 --
682 -- Pre Conditions
683 -- None.
684 --
685 -- In Parameters
686 -- routing_history_id PK of record being inserted or updated.
687 -- user_action_cd Value of lookup code.
688 -- effective_date effective date
689 -- object_version_number Object version number of record being
690 -- inserted or updated.
691 --
692 -- Post Success
693 -- Processing continues
694 --
695 -- Post Failure
696 -- Error handled by procedure
697 --
698 -- Access Status
699 -- Internal table handler use only.
700 --
701 Procedure chk_user_action_cd(p_routing_history_id in number,
702 p_user_action_cd in varchar2,
703 p_effective_date in date,
704 p_object_version_number in number) is
705 --
706 l_proc varchar2(72) := g_package||'chk_user_action_cd';
707 l_api_updating boolean;
708 --
709 Begin
710 --
711 hr_utility.set_location('Entering:'||l_proc, 5);
712 --
713 l_api_updating := pqh_rht_shd.api_updating
714 (p_routing_history_id => p_routing_history_id,
715 p_object_version_number => p_object_version_number);
716 --
717 if (l_api_updating
718 and p_user_action_cd
719 <> nvl(pqh_rht_shd.g_old_rec.user_action_cd,hr_api.g_varchar2)
720 or not l_api_updating) then
721 --
722 -- check if value of lookup falls within lookup type.
723 --
724 --
725 if hr_api.not_exists_in_hr_lookups
726 (p_lookup_type => 'PQH_USER_ACTION_CD',
727 p_lookup_code => p_user_action_cd,
728 p_effective_date => p_effective_date) then
729 --
730 -- raise error as does not exist as lookup
731 --
732 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
733 hr_utility.raise_error;
734 --
735 end if;
736 --
737 end if;
738 --
739 hr_utility.set_location('Leaving:'||l_proc,10);
740 --
741 end chk_user_action_cd;
742 --
743 --
744 -- ----------------------------------------------------------------------------
745 -- |------< chk_forwarded_to_assignment_id >------|
746 -- ----------------------------------------------------------------------------
747 --
748 -- Description
749 -- This procedure checks that a referenced foreign key actually exists
750 -- in the referenced table.
751 --
752 -- Pre-Conditions
753 -- None.
754 --
755 -- In Parameters
756 -- p_routing_history_id PK
757 -- p_forwarded_to_assignment_id ID of FK column
758 -- p_object_version_number object version number
759 --
760 -- Post Success
761 -- Processing continues
762 --
763 -- Post Failure
764 -- Error raised.
765 --
766 -- Access Status
767 -- Internal table handler use only.
768 --
769 Procedure chk_forwarded_to_assignment_id (p_routing_history_id in number,
770 p_forwarded_to_assignment_id in number,
771 p_effective_date in date,
772 p_object_version_number in number) is
773 --
774 l_proc varchar2(72) := g_package||'chk_forwarded_to_assignment_id';
775 l_api_updating boolean;
776 l_dummy varchar2(1);
777 --
778 cursor c1 is
779 select null
780 from per_all_assignments_f a
781 where a.assignment_id = p_forwarded_to_assignment_id
782 and p_effective_date between a.effective_start_date and a.effective_end_date;
783 --
784 Begin
785 --
786 hr_utility.set_location('Entering:'||l_proc,5);
787 --
788 l_api_updating := pqh_rht_shd.api_updating
789 (p_routing_history_id => p_routing_history_id,
790 p_object_version_number => p_object_version_number);
791 --
792 if (l_api_updating
793 and nvl(p_forwarded_to_assignment_id,hr_api.g_number)
794 <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_assignment_id,hr_api.g_number)
795 or not l_api_updating) and
796 p_forwarded_to_assignment_id is not null then
797 --
798 -- check if forwarded_to_assignment_id value exists in per_all_assignments table
799 --
800 open c1;
801 --
802 fetch c1 into l_dummy;
803 if c1%notfound then
804 --
805 close c1;
806 --
807 -- raise error as FK does not relate to PK in per_all_assignments table
808 -- table.
809 --
810 hr_utility.set_message(8302,'PQH_INVALID_ASSIGNMENT');
811 hr_utility.raise_error;
812 --
813 end if;
814 --
815 close c1;
816 --
817 end if;
818 --
819 hr_utility.set_location('Leaving:'||l_proc,10);
820 --
821 End chk_forwarded_to_assignment_id;
822 --
823 -- ----------------------------------------------------------------------------
824 -- |------< chk_forwarded_by_assignment_id >------|
825 -- ----------------------------------------------------------------------------
826 --
827 -- Description
828 -- This procedure checks that a referenced foreign key actually exists
829 -- in the referenced table.
830 --
831 -- Pre-Conditions
832 -- None.
833 --
834 -- In Parameters
835 -- p_routing_history_id PK
836 -- p_forwarded_by_assignment_id ID of FK column
837 -- p_object_version_number object version number
838 --
839 -- Post Success
840 -- Processing continues
841 --
842 -- Post Failure
843 -- Error raised.
844 --
845 -- Access Status
846 -- Internal table handler use only.
847 --
848 Procedure chk_forwarded_by_assignment_id (p_routing_history_id in number,
849 p_forwarded_by_assignment_id in number,
850 p_effective_date in date,
851 p_object_version_number in number) is
852 --
853 l_proc varchar2(72) := g_package||'chk_forwarded_by_assignment_id';
854 l_api_updating boolean;
855 l_dummy varchar2(1);
856 --
857 cursor c1 is
858 select null
859 from per_all_assignments_f a
860 where a.assignment_id = p_forwarded_by_assignment_id
861 and p_effective_date between a.effective_start_date and a.effective_end_date;
862 --
863 Begin
864 --
865 hr_utility.set_location('Entering:'||l_proc,5);
866 --
867 l_api_updating := pqh_rht_shd.api_updating
868 (p_routing_history_id => p_routing_history_id,
869 p_object_version_number => p_object_version_number);
870 --
871 if (l_api_updating
872 and nvl(p_forwarded_by_assignment_id,hr_api.g_number)
873 <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_assignment_id,hr_api.g_number)
874 or not l_api_updating) and
875 p_forwarded_by_assignment_id is not null then
876 --
877 -- check if forwarded_by_assignment_id value exists in per_all_assignments table
878 --
879 open c1;
880 --
881 fetch c1 into l_dummy;
882 if c1%notfound then
883 --
884 close c1;
885 --
886 -- raise error as FK does not relate to PK in per_all_assignments table
887 -- table.
888 --
889 hr_utility.set_message(8302,'PQH_INVALID_ASSIGNMENT');
890 hr_utility.raise_error;
891 --
892 end if;
893 --
894 close c1;
895 --
896 end if;
897 --
898 hr_utility.set_location('Leaving:'||l_proc,10);
899 --
900 End chk_forwarded_by_assignment_id;
901 --
902 -- ----------------------------------------------------------------------------
903 -- |------< chk_forwarded_to_role_id >------|
904 -- ----------------------------------------------------------------------------
905 --
906 -- Description
907 -- This procedure checks that a referenced foreign key actually exists
908 -- in the referenced table.
909 --
910 -- Pre-Conditions
911 -- None.
912 --
913 -- In Parameters
914 -- p_routing_history_id PK
915 -- p_forwarded_to_role_id ID of FK column
916 -- p_object_version_number object version number
917 --
918 -- Post Success
919 -- Processing continues
920 --
921 -- Post Failure
922 -- Error raised.
923 --
924 -- Access Status
925 -- Internal table handler use only.
926 --
927 Procedure chk_forwarded_to_role_id (p_routing_history_id in number,
928 p_forwarded_to_role_id in number,
929 p_object_version_number in number) is
930 --
931 l_proc varchar2(72) := g_package||'chk_forwarded_to_role_id';
932 l_api_updating boolean;
933 l_dummy varchar2(1);
934 --
935 cursor c1 is
936 select null
937 from pqh_roles a
938 where a.role_id = p_forwarded_to_role_id
939 and nvl(a.enable_flag,'X') ='Y';
940 --
941 Begin
942 --
943 hr_utility.set_location('Entering:'||l_proc,5);
944 --
945 l_api_updating := pqh_rht_shd.api_updating
946 (p_routing_history_id => p_routing_history_id,
947 p_object_version_number => p_object_version_number);
948 --
949 if (l_api_updating
950 and nvl(p_forwarded_to_role_id,hr_api.g_number)
951 <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_role_id,hr_api.g_number)
952 or not l_api_updating) and
953 p_forwarded_to_role_id is not null then
954 --
955 -- check if forwarded_to_role_id value exists in pqh_roles table
956 hr_utility.set_location('checking forwarded_to_role '||p_forwarded_to_role_id||l_proc,5);
957 --
958 open c1;
959 --
960 fetch c1 into l_dummy;
961 if c1%notfound then
962 --
963 close c1;
964 --
965 -- raise error as FK does not relate to PK in pqh_roles table
966 -- table.
967 --
968 hr_utility.set_message(8302,'PQH_INVALID_ROLE');
969 hr_utility.raise_error;
970 --
971 end if;
972 --
973 close c1;
974 --
975 end if;
976 --
977 hr_utility.set_location('Leaving:'||l_proc,10);
978 --
979 End chk_forwarded_to_role_id;
980 --
981 --
982 -- ----------------------------------------------------------------------------
983 -- |------< chk_forwarded_by_role_id >------|
984 -- ----------------------------------------------------------------------------
985 --
986 -- Description
987 -- This procedure checks that a referenced foreign key actually exists
988 -- in the referenced table.
989 --
990 -- Pre-Conditions
991 -- None.
992 --
993 -- In Parameters
994 -- p_routing_history_id PK
995 -- p_forwarded_by_role_id ID of FK column
996 -- p_object_version_number object version number
997 --
998 -- Post Success
999 -- Processing continues
1000 --
1001 -- Post Failure
1002 -- Error raised.
1003 --
1004 -- Access Status
1005 -- Internal table handler use only.
1006 --
1007 Procedure chk_forwarded_by_role_id (p_routing_history_id in number,
1008 p_forwarded_by_role_id in number,
1009 p_object_version_number in number) is
1010 --
1011 l_proc varchar2(72) := g_package||'chk_forwarded_by_role_id';
1012 l_api_updating boolean;
1013 l_dummy varchar2(1);
1014 --
1015 cursor c1 is
1016 select null
1017 from pqh_roles a
1018 where a.role_id = p_forwarded_by_role_id
1019 and nvl(a.enable_flag,'X') ='Y';
1020 --
1021 Begin
1022 --
1023 hr_utility.set_location('Entering:'||l_proc,5);
1024 --
1025 l_api_updating := pqh_rht_shd.api_updating
1026 (p_routing_history_id => p_routing_history_id,
1027 p_object_version_number => p_object_version_number);
1028 --
1029 if (l_api_updating
1030 and nvl(p_forwarded_by_role_id,hr_api.g_number)
1031 <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_role_id,hr_api.g_number)
1032 or not l_api_updating) and
1033 p_forwarded_by_role_id is not null then
1034 --
1035 -- check if forwarded_by_role_id value exists in pqh_roles table
1036 hr_utility.set_location('checking forwarded_by_role '||p_forwarded_by_role_id||l_proc,5);
1037 --
1038 open c1;
1039 --
1040 fetch c1 into l_dummy;
1041 if c1%notfound then
1042 --
1043 close c1;
1044 --
1045 -- raise error as FK does not relate to PK in fnd_user table
1046 -- table.
1047 --
1048 hr_utility.set_message(8302,'PQH_INVALID_ROLE');
1049 hr_utility.raise_error;
1050 --
1051 end if;
1052 --
1053 close c1;
1054 --
1055 end if;
1056 --
1057 hr_utility.set_location('Leaving:'||l_proc,10);
1058 --
1059 End chk_forwarded_by_role_id;
1060 --
1061 -- ----------------------------------------------------------------------------
1062 -- |------< chk_forwarded_to_user_id >------|
1063 -- ----------------------------------------------------------------------------
1064 --
1065 -- Description
1066 -- This procedure checks that a referenced foreign key actually exists
1067 -- in the referenced table.
1068 --
1069 -- Pre-Conditions
1070 -- None.
1071 --
1072 -- In Parameters
1073 -- p_routing_history_id PK
1074 -- p_forwarded_to_user_id ID of FK column
1075 -- p_object_version_number object version number
1076 --
1077 -- Post Success
1078 -- Processing continues
1079 --
1080 -- Post Failure
1081 -- Error raised.
1082 --
1083 -- Access Status
1084 -- Internal table handler use only.
1085 --
1086 Procedure chk_forwarded_to_user_id (p_routing_history_id in number,
1087 p_forwarded_to_user_id in number,
1088 p_object_version_number in number) is
1089 --
1090 l_proc varchar2(72) := g_package||'chk_forwarded_to_user_id';
1091 l_api_updating boolean;
1092 l_dummy varchar2(1);
1093 --
1094 cursor c1 is
1095 select null
1096 from fnd_user a
1097 where a.user_id = p_forwarded_to_user_id;
1098 --
1099 Begin
1100 --
1101 hr_utility.set_location('Entering:'||l_proc,5);
1102 --
1103 l_api_updating := pqh_rht_shd.api_updating
1104 (p_routing_history_id => p_routing_history_id,
1105 p_object_version_number => p_object_version_number);
1106 --
1107 if (l_api_updating
1108 and nvl(p_forwarded_to_user_id,hr_api.g_number)
1109 <> nvl(pqh_rht_shd.g_old_rec.forwarded_to_user_id,hr_api.g_number)
1110 or not l_api_updating) and
1111 p_forwarded_to_user_id is not null then
1112 --
1113 -- check if forwarded_to_user_id value exists in fnd_user table
1114 --
1115 open c1;
1116 --
1117 fetch c1 into l_dummy;
1118 if c1%notfound then
1119 --
1120 close c1;
1121 --
1122 -- raise error as FK does not relate to PK in fnd_user table
1123 -- table.
1124 --
1125 hr_utility.set_message(8302,'PQH_INVALID_USER');
1126 hr_utility.raise_error;
1127 --
1128 end if;
1129 --
1130 close c1;
1131 --
1132 end if;
1133 --
1134 hr_utility.set_location('Leaving:'||l_proc,10);
1135 --
1136 End chk_forwarded_to_user_id;
1137 --
1138 --
1139 -- ----------------------------------------------------------------------------
1140 -- |------< chk_forwarded_by_user_id >------|
1141 -- ----------------------------------------------------------------------------
1142 --
1143 -- Description
1144 -- This procedure checks that a referenced foreign key actually exists
1145 -- in the referenced table.
1146 --
1147 -- Pre-Conditions
1148 -- None.
1149 --
1150 -- In Parameters
1151 -- p_routing_history_id PK
1152 -- p_forwarded_by_user_id ID of FK column
1153 -- p_object_version_number object version number
1154 --
1155 -- Post Success
1156 -- Processing continues
1157 --
1158 -- Post Failure
1159 -- Error raised.
1160 --
1161 -- Access Status
1162 -- Internal table handler use only.
1163 --
1164 Procedure chk_forwarded_by_user_id (p_routing_history_id in number,
1165 p_forwarded_by_user_id in number,
1166 p_object_version_number in number) is
1167 --
1168 l_proc varchar2(72) := g_package||'chk_forwarded_by_user_id';
1169 l_api_updating boolean;
1170 l_dummy varchar2(1);
1171 --
1172 cursor c1 is
1173 select null
1174 from fnd_user a
1175 where a.user_id = p_forwarded_by_user_id;
1176 --
1177 Begin
1178 --
1179 hr_utility.set_location('Entering:'||l_proc,5);
1180 --
1181 l_api_updating := pqh_rht_shd.api_updating
1182 (p_routing_history_id => p_routing_history_id,
1183 p_object_version_number => p_object_version_number);
1184 --
1185 if (l_api_updating
1186 and nvl(p_forwarded_by_user_id,hr_api.g_number)
1187 <> nvl(pqh_rht_shd.g_old_rec.forwarded_by_user_id,hr_api.g_number)
1188 or not l_api_updating) and
1189 p_forwarded_by_user_id is not null then
1190 --
1191 -- check if forwarded_by_user_id value exists in fnd_user table
1192 --
1193 open c1;
1194 --
1195 fetch c1 into l_dummy;
1196 if c1%notfound then
1197 --
1198 close c1;
1199 --
1200 -- raise error as FK does not relate to PK in fnd_user table
1201 -- table.
1202 --
1203 hr_utility.set_message(8302,'PQH_INVALID_USER');
1204 hr_utility.raise_error;
1205 --
1206 end if;
1207 --
1208 close c1;
1209 --
1210 end if;
1211 --
1212 hr_utility.set_location('Leaving:'||l_proc,10);
1213 --
1214 End chk_forwarded_by_user_id;
1215 --
1216 -- ----------------------------------------------------------------------------
1217 -- |---------------------------< insert_validate >----------------------------|
1218 -- ----------------------------------------------------------------------------
1219 Procedure insert_validate(p_rec in pqh_rht_shd.g_rec_type
1220 ,p_effective_date in date) is
1221 --
1222 l_proc varchar2(72) := g_package||'insert_validate';
1223 --
1224 Begin
1225 hr_utility.set_location('Entering:'||l_proc, 5);
1226 --
1227 -- Call all supporting business operations
1228 --
1229 chk_routing_history_id
1230 (p_routing_history_id => p_rec.routing_history_id,
1231 p_object_version_number => p_rec.object_version_number);
1232 --
1233 chk_pos_structure_version_id
1234 (p_routing_history_id => p_rec.routing_history_id,
1235 p_pos_structure_version_id => p_rec.pos_structure_version_id,
1236 p_object_version_number => p_rec.object_version_number);
1237 --
1238 chk_forwarded_to_member_id
1239 (p_routing_history_id => p_rec.routing_history_id,
1240 p_forwarded_to_member_id => p_rec.forwarded_to_member_id,
1241 p_object_version_number => p_rec.object_version_number);
1242 --
1243 chk_forwarded_by_member_id
1244 (p_routing_history_id => p_rec.routing_history_id,
1245 p_forwarded_by_member_id => p_rec.forwarded_by_member_id,
1246 p_object_version_number => p_rec.object_version_number);
1247 --
1248 chk_transaction_category_id
1249 (p_routing_history_id => p_rec.routing_history_id,
1250 p_transaction_category_id => p_rec.transaction_category_id,
1251 p_object_version_number => p_rec.object_version_number);
1252 --
1253 chk_forwarded_to_position_id
1254 (p_routing_history_id => p_rec.routing_history_id,
1255 p_forwarded_to_position_id => p_rec.forwarded_to_position_id,
1256 p_object_version_number => p_rec.object_version_number);
1257 --
1258 chk_forwarded_by_position_id
1259 (p_routing_history_id => p_rec.routing_history_id,
1260 p_forwarded_by_position_id => p_rec.forwarded_by_position_id,
1261 p_object_version_number => p_rec.object_version_number);
1262 --
1263 chk_routing_category_id
1264 (p_routing_history_id => p_rec.routing_history_id,
1265 p_routing_category_id => p_rec.routing_category_id,
1266 p_object_version_number => p_rec.object_version_number);
1267 --
1268 chk_approval_cd
1269 (p_routing_history_id => p_rec.routing_history_id,
1270 p_approval_cd => p_rec.approval_cd,
1271 p_effective_date => p_effective_date,
1272 p_object_version_number => p_rec.object_version_number);
1273 --
1274 chk_user_action_cd
1275 (p_routing_history_id => p_rec.routing_history_id,
1276 p_user_action_cd => p_rec.user_action_cd,
1277 p_effective_date => p_effective_date,
1278 p_object_version_number => p_rec.object_version_number);
1279 --
1280 chk_forwarded_to_assignment_id
1281 (p_routing_history_id => p_rec.routing_history_id,
1282 p_forwarded_to_assignment_id => p_rec.forwarded_to_assignment_id,
1283 p_effective_date => p_effective_date,
1284 p_object_version_number => p_rec.object_version_number);
1285 --
1286 chk_forwarded_by_assignment_id
1287 (p_routing_history_id => p_rec.routing_history_id,
1288 p_forwarded_by_assignment_id => p_rec.forwarded_by_assignment_id,
1289 p_effective_date => p_effective_date,
1290 p_object_version_number => p_rec.object_version_number);
1291 --
1292 chk_forwarded_by_user_id
1293 (p_routing_history_id => p_rec.routing_history_id,
1294 p_forwarded_by_user_id => p_rec.forwarded_by_user_id,
1295 p_object_version_number => p_rec.object_version_number);
1296 --
1297 chk_forwarded_to_user_id
1298 (p_routing_history_id => p_rec.routing_history_id,
1299 p_forwarded_to_user_id => p_rec.forwarded_to_user_id,
1300 p_object_version_number => p_rec.object_version_number);
1301 --
1302 chk_forwarded_by_role_id
1303 (p_routing_history_id => p_rec.routing_history_id,
1304 p_forwarded_by_role_id => p_rec.forwarded_by_role_id,
1305 p_object_version_number => p_rec.object_version_number);
1306 --
1307 chk_forwarded_to_role_id
1308 (p_routing_history_id => p_rec.routing_history_id,
1309 p_forwarded_to_role_id => p_rec.forwarded_to_role_id,
1310 p_object_version_number => p_rec.object_version_number);
1311 --
1312 --
1313 hr_utility.set_location(' Leaving:'||l_proc, 10);
1314 End insert_validate;
1315 --
1316 -- ----------------------------------------------------------------------------
1317 -- |---------------------------< update_validate >----------------------------|
1318 -- ----------------------------------------------------------------------------
1319 Procedure update_validate(p_rec in pqh_rht_shd.g_rec_type
1320 ,p_effective_date in date) is
1321 --
1322 l_proc varchar2(72) := g_package||'update_validate';
1323 --
1324 Begin
1325 hr_utility.set_location('Entering:'||l_proc, 5);
1326 --
1327 -- Call all supporting business operations
1328 --
1329 chk_routing_history_id
1330 (p_routing_history_id => p_rec.routing_history_id,
1331 p_object_version_number => p_rec.object_version_number);
1332 --
1333 chk_pos_structure_version_id
1334 (p_routing_history_id => p_rec.routing_history_id,
1335 p_pos_structure_version_id => p_rec.pos_structure_version_id,
1336 p_object_version_number => p_rec.object_version_number);
1337 --
1338 chk_forwarded_to_member_id
1339 (p_routing_history_id => p_rec.routing_history_id,
1340 p_forwarded_to_member_id => p_rec.forwarded_to_member_id,
1341 p_object_version_number => p_rec.object_version_number);
1342 --
1343 chk_forwarded_by_member_id
1344 (p_routing_history_id => p_rec.routing_history_id,
1345 p_forwarded_by_member_id => p_rec.forwarded_by_member_id,
1346 p_object_version_number => p_rec.object_version_number);
1347 --
1348 chk_transaction_category_id
1349 (p_routing_history_id => p_rec.routing_history_id,
1350 p_transaction_category_id => p_rec.transaction_category_id,
1351 p_object_version_number => p_rec.object_version_number);
1352 --
1353 chk_forwarded_to_position_id
1354 (p_routing_history_id => p_rec.routing_history_id,
1355 p_forwarded_to_position_id => p_rec.forwarded_to_position_id,
1356 p_object_version_number => p_rec.object_version_number);
1357 --
1358 chk_forwarded_by_position_id
1359 (p_routing_history_id => p_rec.routing_history_id,
1360 p_forwarded_by_position_id => p_rec.forwarded_by_position_id,
1361 p_object_version_number => p_rec.object_version_number);
1362 --
1363 chk_routing_category_id
1364 (p_routing_history_id => p_rec.routing_history_id,
1365 p_routing_category_id => p_rec.routing_category_id,
1366 p_object_version_number => p_rec.object_version_number);
1367 --
1368 chk_approval_cd
1369 (p_routing_history_id => p_rec.routing_history_id,
1370 p_approval_cd => p_rec.approval_cd,
1371 p_effective_date => p_effective_date,
1372 p_object_version_number => p_rec.object_version_number);
1373 --
1374 chk_user_action_cd
1375 (p_routing_history_id => p_rec.routing_history_id,
1376 p_user_action_cd => p_rec.user_action_cd,
1377 p_effective_date => p_effective_date,
1378 p_object_version_number => p_rec.object_version_number);
1379 --
1380 chk_forwarded_to_assignment_id
1381 (p_routing_history_id => p_rec.routing_history_id,
1382 p_forwarded_to_assignment_id => p_rec.forwarded_to_assignment_id,
1383 p_effective_date => p_effective_date,
1384 p_object_version_number => p_rec.object_version_number);
1385 --
1386 chk_forwarded_by_assignment_id
1387 (p_routing_history_id => p_rec.routing_history_id,
1388 p_forwarded_by_assignment_id => p_rec.forwarded_by_assignment_id,
1389 p_effective_date => p_effective_date,
1390 p_object_version_number => p_rec.object_version_number);
1391 --
1392 chk_forwarded_to_user_id
1393 (p_routing_history_id => p_rec.routing_history_id,
1394 p_forwarded_to_user_id => p_rec.forwarded_to_user_id,
1395 p_object_version_number => p_rec.object_version_number);
1396 --
1397 chk_forwarded_by_user_id
1398 (p_routing_history_id => p_rec.routing_history_id,
1399 p_forwarded_by_user_id => p_rec.forwarded_by_user_id,
1400 p_object_version_number => p_rec.object_version_number);
1401 --
1402 chk_forwarded_to_role_id
1403 (p_routing_history_id => p_rec.routing_history_id,
1404 p_forwarded_to_role_id => p_rec.forwarded_to_role_id,
1405 p_object_version_number => p_rec.object_version_number);
1406 --
1407 chk_forwarded_by_role_id
1408 (p_routing_history_id => p_rec.routing_history_id,
1409 p_forwarded_by_role_id => p_rec.forwarded_by_role_id,
1410 p_object_version_number => p_rec.object_version_number);
1411 --
1412 --
1413 --
1414 --
1415 hr_utility.set_location(' Leaving:'||l_proc, 10);
1416 End update_validate;
1417 --
1418 -- ----------------------------------------------------------------------------
1419 -- |---------------------------< delete_validate >----------------------------|
1420 -- ----------------------------------------------------------------------------
1421 Procedure delete_validate(p_rec in pqh_rht_shd.g_rec_type
1422 ,p_effective_date in date) is
1423 --
1424 l_proc varchar2(72) := g_package||'delete_validate';
1425 --
1426 Begin
1427 hr_utility.set_location('Entering:'||l_proc, 5);
1428 --
1429 -- Call all supporting business operations
1430 --
1431 hr_utility.set_location(' Leaving:'||l_proc, 10);
1432 End delete_validate;
1433 --
1434 end pqh_rht_bus;