[Home] [Help]
PACKAGE BODY: APPS.PQH_RCT_BUS
Source
1 Package Body pqh_rct_bus as
2 /* $Header: pqrctrhi.pkb 115.24 2004/02/19 13:29:18 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rct_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_routing_category_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_category_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_category_id
37 (p_routing_category_id in number,
38 p_object_version_number in number) is
39 --
40 l_proc varchar2(72) := g_package||'chk_routing_category_id';
41 l_api_updating boolean;
42 --
43 Begin
44 --
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 l_api_updating := pqh_rct_shd.api_updating
48 (p_routing_category_id => p_routing_category_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_routing_category_id,hr_api.g_number)
53 <> pqh_rct_shd.g_old_rec.routing_category_id) then
54 --
55 -- raise error as PK has changed
56 --
57 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_routing_category_id is not null then
64 --
65 -- raise error as PK is not null
66 --
67 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_PK');
68 --
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_routing_category_id;
76 --
77 --
78 -- ----------------------------------------------------------------------------
82 -- Description
79 -- |------< chk_transaction_category_id >------|
80 -- ----------------------------------------------------------------------------
81 --
83 -- This procedure checks that a referenced foreign key actually exists
84 -- in the referenced table.
85 --
86 -- Pre-Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- p_routing_category_id PK
91 -- p_transaction_category_id ID of FK column
92 -- p_object_version_number object version number
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Error raised.
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 Procedure chk_transaction_category_id
104 (p_routing_category_id in number,
105 p_transaction_category_id in number,
106 p_object_version_number in number) is
107 --
108 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
109 l_api_updating boolean;
110 l_dummy varchar2(1);
111 --
112 cursor c1 is
113 select null
114 from pqh_transaction_categories a
115 where a.transaction_category_id = p_transaction_category_id;
116 --
117 Begin
118 --
119 hr_utility.set_location('Entering:'||l_proc,5);
120 --
121 l_api_updating := pqh_rct_shd.api_updating
122 (p_routing_category_id => p_routing_category_id,
123 p_object_version_number => p_object_version_number);
124 --
125 if (l_api_updating AND
126 nvl(p_transaction_category_id,hr_api.g_number)
127 = nvl(pqh_rct_shd.g_old_rec.transaction_category_id,hr_api.g_number)
128 or not l_api_updating) then
129 --
130 -- check if transaction_category_id value exists in
131 -- pqh_transaction_categories table
132 --
133 open c1;
134 --
135 fetch c1 into l_dummy;
136 if c1%notfound then
137 --
138 close c1;
139 --
140 -- raise error as FK does not relate to PK in
141 -- pqh_transaction_categories table.
142 --
143 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_FK2');
144 --
145 end if;
146 --
147 close c1;
148 --
149 --
150 End if;
151 --
152 hr_utility.set_location('Leaving:'||l_proc,10);
153 --
154 End chk_transaction_category_id;
155 --
156 --
157 -- ----------------------------------------------------------------------------
158 -- |------< chk_enable_flag >------|
159 -- ----------------------------------------------------------------------------
160 --
161 -- Description
162 -- This procedure is used to check that the lookup value is valid.
163 --
164 -- Pre Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- routing_category_id PK of record being inserted or updated.
169 -- enable_flag Value of lookup code.
170 -- effective_date effective date
171 -- object_version_number Object version number of record being
172 -- inserted or updated.
173 --
174 -- Post Success
175 -- Processing continues
176 --
177 -- Post Failure
178 -- Error handled by procedure
179 --
180 -- Access Status
181 -- Internal table handler use only.
182 --
183 Procedure chk_enable_flag (p_routing_category_id in number,
184 p_enable_flag in varchar2,
185 p_transaction_category_id in number,
186 p_effective_date in date,
187 p_object_version_number in number) is
188 --
189 l_error_code number(10);
190 l_error_range_name pqh_attribute_ranges.range_name%type;
191 l_overlap_range_name pqh_attribute_ranges.range_name%type;
192 l_error_routing_category varchar2(200);
193 --
194 l_proc varchar2(72) := g_package||'chk_enable_flag';
195 l_api_updating boolean;
196 --
197 Begin
198 --
199 hr_utility.set_location('Entering:'||l_proc, 5);
200 --
201 l_api_updating := pqh_rct_shd.api_updating
202 (p_routing_category_id => p_routing_category_id,
203 p_object_version_number => p_object_version_number);
204 --
205 if (l_api_updating
206 and p_enable_flag
207 <> nvl(pqh_rct_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
208 or not l_api_updating)
209 and p_enable_flag is not null then
210 --
211 -- check if value of lookup falls within lookup type.
212 --
213 if hr_api.not_exists_in_hr_lookups
214 (p_lookup_type => 'YES_NO',
215 p_lookup_code => p_enable_flag,
216 p_effective_date => p_effective_date) then
217 --
218 -- raise error as does not exist as lookup
219 --
220 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
221 hr_utility.raise_error;
222 --
223 end if;
224 --
225 End if;
226 --
227 /**
228 -- If we are trying to enable a previously disabled routing category then
229 -- we need to make sure that the rules under this routing category do not
230 -- overlap with any other rule in the same transaction category.
231 --
232 if l_api_updating and
233 p_enable_flag = 'Y' and
234 p_enable_flag <> nvl(pqh_rct_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
235 then
239 l_error_code := pqh_ATTRIBUTE_RANGES_pkg.chk_enable_routing_category
236 --
237 hr_utility.set_location('Enable allow :'||l_proc,8);
238 --
240 (p_transaction_category_id => p_transaction_category_id,
241 p_routing_category_id => p_routing_category_id,
242 p_overlap_range_name => l_overlap_range_name,
243 p_error_routing_category => l_error_routing_category,
244 p_error_range_name => l_error_range_name
245 );
246 --
247 If l_error_code = 1 then
248 --
249 hr_utility.set_message(8302,'PQH_CANNOT_ENABLE_ROUTING_CAT');
250 hr_utility.set_message_token('RANGE_NAME',l_overlap_range_name);
251 hr_utility.set_message_token('ERR_RANGE',l_error_range_name);
252 hr_utility.set_message_token('ERR_ROUTING',l_error_routing_category);
253 hr_utility.raise_error;
254 --
255 End if;
256 --
257 hr_utility.set_location('Allowed:'||l_proc,9);
258 --
259 End if;
260 --
261 **/
262 hr_utility.set_location('Leaving:'||l_proc,10);
263 --
264 end chk_enable_flag;
265 --
266 -- ----------------------------------------------------------------------------
267 -- |------< chk_default_flag >------|
268 -- ----------------------------------------------------------------------------
269 --
270 -- Description
271 -- This procedure is used to check that the lookup value is valid.
272 --
273 -- Pre Conditions
274 -- None.
275 --
276 -- In Parameters
277 -- routing_category_id PK of record being inserted or updated.
278 -- default_flag Value of lookup code.
279 -- effective_date effective date
280 -- object_version_number Object version number of record being
281 -- inserted or updated.
282 --
283 -- Post Success
284 -- Processing continues
285 --
286 -- Post Failure
287 -- Error handled by procedure
288 --
289 -- Access Status
290 -- Internal table handler use only.
291 --
292 Procedure chk_default_flag (p_routing_category_id in number,
293 p_default_flag in varchar2,
294 p_effective_date in date,
295 p_object_version_number in number) is
296 --
297 --
298 l_proc varchar2(72) := g_package||'chk_default_flag';
299 l_api_updating boolean;
300 --
301 Begin
302 --
303 hr_utility.set_location('Entering:'||l_proc, 5);
304 --
305 l_api_updating := pqh_rct_shd.api_updating
306 (p_routing_category_id => p_routing_category_id,
307 p_object_version_number => p_object_version_number);
308 --
309 if (l_api_updating
310 and p_default_flag
311 <> nvl(pqh_rct_shd.g_old_rec.default_flag,hr_api.g_varchar2)
312 or not l_api_updating)
313 and p_default_flag is not null then
314 --
315 -- check if value of lookup falls within lookup type.
316 --
317 if hr_api.not_exists_in_hr_lookups
318 (p_lookup_type => 'YES_NO',
319 p_lookup_code => p_default_flag,
320 p_effective_date => p_effective_date) then
321 --
322 -- raise error as does not exist as lookup
323 --
324 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
325 hr_utility.raise_error;
326 --
327 end if;
328 --
329 End if;
330 --
331 --
332 hr_utility.set_location('Leaving:'||l_proc,10);
333 --
334 end chk_default_flag;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |------< chk_delete_flag >------|
338 -- ----------------------------------------------------------------------------
339 --
340 -- Description
341 -- This procedure is used to check that the lookup value is valid.
342 --
343 -- Pre Conditions
344 -- None.
345 --
346 -- In Parameters
347 -- routing_category_id PK of record being inserted or updated.
348 -- delete_flag Value of lookup code.
349 -- effective_date effective date
350 -- object_version_number Object version number of record being
351 -- inserted or updated.
352 --
353 -- Post Success
354 -- Processing continues
355 --
356 -- Post Failure
357 -- Error handled by procedure
358 --
359 -- Access Status
360 -- Internal table handler use only.
361 --
362 Procedure chk_delete_flag (p_routing_category_id in number,
363 p_delete_flag in varchar2,
364 p_effective_date in date,
365 p_object_version_number in number) is
366 --
367 --
368 l_proc varchar2(72) := g_package||'chk_delete_flag';
369 l_api_updating boolean;
370 --
371 Begin
372 --
373 hr_utility.set_location('Entering:'||l_proc, 5);
374 --
375 l_api_updating := pqh_rct_shd.api_updating
376 (p_routing_category_id => p_routing_category_id,
377 p_object_version_number => p_object_version_number);
378 --
379 if (l_api_updating
380 and p_delete_flag
381 <> nvl(pqh_rct_shd.g_old_rec.delete_flag,hr_api.g_varchar2)
382 or not l_api_updating)
383 and p_delete_flag is not null then
384 --
388 (p_lookup_type => 'YES_NO',
385 -- check if value of lookup falls within lookup type.
386 --
387 if hr_api.not_exists_in_hr_lookups
389 p_lookup_code => p_delete_flag,
390 p_effective_date => p_effective_date) then
391 --
392 -- raise error as does not exist as lookup
393 --
394 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
395 hr_utility.raise_error;
396 --
397 end if;
398 --
399 End if;
400 --
401 --
402 hr_utility.set_location('Leaving:'||l_proc,10);
403 --
404 end chk_delete_flag;
405 --
406 ---------------------------------------------------------------------------
407 --
408 -- Description : The foll procedure disallows any update of a transaction
409 -- category of a routing category .
410 -- Allows routing categories to be added or updated only if the transaction
411 -- categories attributes are frozen.
412 -- Allows a routing category to be disabled at any time though .
413 --
414 Procedure chk_ins_upd_routing_category
415 (p_routing_category_id in number,
416 p_transaction_category_id in number,
417 p_enable_flag in varchar2,
418 p_object_version_number in number) is
419 --
420 l_proc varchar2(72) := g_package||'chk_ins_upd_routing_category';
421 l_api_updating boolean;
422 l_dummy varchar2(1);
423 l_freeze_status_cd pqh_transaction_categories.freeze_status_cd%type;
424 --
425 cursor c1 is
426 select nvl(freeze_status_cd,hr_api.g_varchar2)
427 from pqh_transaction_categories a
428 where a.transaction_category_id = p_transaction_category_id;
429 --
430 Begin
431 --
432 hr_utility.set_location('Entering:'||l_proc,5);
433 --
434 l_api_updating := pqh_rct_shd.api_updating
435 (p_routing_category_id => p_routing_category_id,
436 p_object_version_number => p_object_version_number);
437 --
438 -- Raise Error if trying to update the transaction category of the
439 -- routing category.
440 --
441 if (l_api_updating AND
442 nvl(p_transaction_category_id,hr_api.g_number)
443 <> nvl(pqh_rct_shd.g_old_rec.transaction_category_id,hr_api.g_number)) then
444 --
445 -- Cannot update the transaction category id
446 --
447 hr_utility.set_message(8302,'PQH_NO_UPD_TRAN_CAT_OF_RCAT');
448 hr_utility.raise_error;
449 --
450 End if;
451
452 --
453 open c1;
454 --
455 fetch c1 into l_freeze_status_cd;
456 if c1%notfound then
457 --
458 close c1;
459 --
460 -- raise error as FK does not relate to PK in
461 -- pqh_transaction_categories table.
462 --
463 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_FK2');
464 --
465 end if;
466 --
467 close c1;
468 --
469 -- Check the freeze status cd. Do not allow any updates or inserts if
470 -- transaction category is already been frozen.
471 --
472 If l_freeze_status_cd = 'FREEZE_CATEGORY' then
473 --
474 if l_api_updating then
475 --
476 If nvl(pqh_rct_shd.g_old_rec.enable_flag,'Y') = 'Y' then
477 --
478 hr_utility.set_message(8302,'PQH_NO_RCT_UPD_FROZEN_TCT');
479 hr_utility.raise_error;
480 --
481 End if;
482 --
483 Else
484 --
485 hr_utility.set_message(8302,'PQH_NO_RCT_INS_FROZEN_TCT');
486 hr_utility.raise_error;
487 --
488 End if;
489
490 /**
491 --
492 -- If transaction category is in unfrozen state
493 --
494 Elsif l_freeze_status_cd IS NULL then
495 --
496 -- Raise Error,if trying to update details of a routing category other
497 -- than its enable flag.
498 --
499 if l_api_updating AND
500 nvl(p_enable_flag,hr_api.g_varchar2)
501 = nvl(pqh_rct_shd.g_old_rec.enable_flag,hr_api.g_varchar2) then
502 --
503 hr_utility.set_message(8302,'PQH_NO_UPDATE_ROUT_CAT');
504 hr_utility.raise_error;
505 --
506 End if;
507 --
508 -- Disallow any inserts when the transaction category is in unfrozen
509 -- state
510 --
511 If not l_api_updating then
512 --
513 hr_utility.set_message(8302,'PQH_NO_INSERT_ROUT_CAT');
514 hr_utility.raise_error;
515 --
516 End if;
517 --
518 **/
519 End if;
520 --
521 hr_utility.set_location('Leaving:'||l_proc,10);
522 --
523 End chk_ins_upd_routing_category;
524 --
525 --
526 -- ----------------------------------------------------------------------------
527 -- |------< chk_list_id >------|
528 -- ----------------------------------------------------------------------------
529 --
530 -- Description
531 -- This procedure checks that if a valid list id is entered
532 --
533 -- Pre-Conditions
534 -- None.
535 --
536 -- In Parameters
537 -- p_routing_category_id PK
538 -- p_routing_list_id ID of FK column
539 -- p_position_structure_id ID of FK column
540 -- p_transaction_category_id ID of FK column
544 -- Processing continues
541 -- p_object_version_number object version number
542 --
543 -- Post Success
545 --
546 -- Post Failure
547 -- Error raised.
548 --
549 -- Access Status
550 -- Internal table handler use only.
551 --
552 Procedure chk_list_id (p_routing_category_id in number,
553 p_routing_list_id in number,
554 p_position_structure_id in number,
555 p_transaction_category_id in number,
556 p_object_version_number in number) as
557 --
558 l_proc varchar2(72) := g_package||'chk_list_id';
559 l_api_updating boolean;
560 l_member_cd pqh_transaction_categories.member_cd%type;
561 l_dummy varchar2(1);
562 --
563 Cursor c1 is
564 select a.member_cd
565 from pqh_transaction_categories a
566 where a.transaction_category_id = p_transaction_category_id;
567
568 Cursor c2 is
569 select null
570 from pqh_attribute_ranges
571 where routing_category_id = p_routing_category_id;
572
573 Begin
574 --
575 hr_utility.set_location('Entering:'||l_proc,5);
576 --
577 l_api_updating := pqh_rct_shd.api_updating
578 (p_routing_category_id => p_routing_category_id,
579 p_object_version_number => p_object_version_number);
580
581 open c1;
582 Fetch c1 into l_member_cd;
583 close c1;
584 --
585 /**
586 -- If the member cd is R only routing list id may be entered .
587 -- Raise error if Position structure id is not null .
588 --
589 if l_member_cd = 'R' then
590 --
591 -- Raise error if the routing list id is null
592 --
593 if p_routing_list_id IS NULL then
594 hr_utility.set_message(8302,'PQH_ROUTING_LIST_ID_IS_NULL');
595 hr_utility.raise_error;
596 Else
597 if (l_api_updating AND
598 nvl(p_routing_list_id,hr_api.g_number)
599 <> nvl(pqh_rct_shd.g_old_rec.routing_list_id,hr_api.g_number)) then
600 --
601 -- Disallow updates to routing list id field, if detail recs exist
602 --
603 open c2;
604 Fetch c2 into l_dummy;
605 if c2%found then
606 close c2;
607 hr_utility.set_message(8302,'PQH_CANNOT_UPD_LIST_ID');
608 hr_utility.raise_error;
609 End if;
610 close c2;
611 --
612 End if;
613 End if;
614 --
615 -- Raise Error if position structure id is not null
616 --
617 if p_position_structure_id IS NOT NULL then
618 hr_utility.set_message(8302,'PQH_LIST_ID_NOT_MATCH_MEM_CD');
619 hr_utility.raise_error;
620 End if;
621 --
622 Elsif l_member_cd = 'P' then
623 if p_position_structure_id IS NULL then
624 hr_utility.set_message(8302,'PQH_POS_STRUCT_ID_IS_NULL');
625 hr_utility.raise_error;
626 Else
627 if (l_api_updating AND
628 nvl(p_position_structure_id,hr_api.g_number)
629 <> nvl(pqh_rct_shd.g_old_rec.position_structure_id,hr_api.g_number)) then
630 --
631 -- Disallow updates to position_structure_id field, if detail recs exist
632 --
633 open c2;
634 Fetch c2 into l_dummy;
635 if c2%found then
636 close c2;
637 hr_utility.set_message(8302,'PQH_CANNOT_UPD_LIST_ID');
638 hr_utility.raise_error;
639 End if;
640 close c2;
641 --
642 End if;
643 End if;
644 --
645 -- Raise Error if routing list id is not null
646 --
647 if p_routing_list_id IS NOT NULL then
648 hr_utility.set_message(8302,'PQH_LIST_ID_NOT_MATCH_MEM_CD');
649 hr_utility.raise_error;
650 End if;
651 --
652 Else
653 if p_routing_list_id IS NOT NULL or p_position_structure_id IS NOT NULL then
654 hr_utility.set_message(8302,'PQH_LIST_ID_NOT_MATCH_MEM_CD');
655 hr_utility.raise_error;
656 End if;
657 End if;
658 --
659 **/
660
661 --
662 if l_api_updating and nvl(p_routing_list_id,hr_api.g_number)
663 <> nvl(pqh_rct_shd.g_old_rec.routing_list_id,hr_api.g_number) then
664 --
665 -- Disallow any updates to the routing list id if there are any
666 -- child records for this routing category.
667 --
668 open c2;
669 Fetch c2 into l_dummy;
670 if c2%found then
671 close c2;
672 hr_utility.set_message(8302,'PQH_CANNOT_UPD_LIST_ID');
673 hr_utility.raise_error;
674 End if;
675 close c2;
676 --
677 End if;
678 --
679 --
680 -- Inserting a new routing category or updating the list id of an existing
681 -- category.
682 --
683 if (l_api_updating and nvl(p_routing_list_id,hr_api.g_number)
684 <> nvl(pqh_rct_shd.g_old_rec.routing_list_id,hr_api.g_number))
685 OR NOT l_api_updating then
686 --
687 -- Validate if the member_cd matches the list id and a list_id that
688 -- mismatches the member_cd is not supplied
689 --
690 if l_member_cd = 'R' then
691 --
692 If p_routing_list_id IS NULL then
693 hr_utility.set_message(8302,'PQH_ROUTING_LIST_ID_IS_NULL');
697 if p_position_structure_id IS NOT NULL then
694 hr_utility.raise_error;
695 End if;
696 --
698 hr_utility.set_message(8302,'PQH_LIST_ID_NOT_MATCH_MEM_CD');
699 hr_utility.raise_error;
700 End if;
701 --
702 Elsif l_member_cd = 'P' then
703 If p_position_structure_id IS NULL then
704 --
705 hr_utility.set_message(8302,'PQH_POS_STRUCT_ID_IS_NULL');
706 hr_utility.raise_error;
707 --
708 End if;
709 --
710 if p_routing_list_id IS NOT NULL then
711 hr_utility.set_message(8302,'PQH_LIST_ID_NOT_MATCH_MEM_CD');
712 hr_utility.raise_error;
713 End if;
714 Else
715 if p_routing_list_id IS NOT NULL or
716 p_position_structure_id IS NOT NULL then
717 --
718 hr_utility.set_message(8302,'PQH_LIST_ID_NOT_MATCH_MEM_CD');
719 hr_utility.raise_error;
720 --
721 End if;
722 --
723 End if;
724 --
725 End if;
726 --
727 hr_utility.set_location('Leaving:'||l_proc,10);
728 --
729 End chk_list_id;
730 --
731 --
732 -- ----------------------------------------------------------------------------
733 -- |------< chk_routing_list_id >------|
734 -- ----------------------------------------------------------------------------
735 --
736 -- Description
737 -- This procedure checks that a referenced foreign key actually exists
738 -- in the referenced table.
739 --
740 -- Pre-Conditions
741 -- None.
742 --
743 -- In Parameters
744 -- p_routing_category_id PK
745 -- p_routing_list_id ID of FK column
746 -- p_object_version_number object version number
747 --
748 -- Post Success
749 -- Processing continues
750 --
751 -- Post Failure
752 -- Error raised.
753 --
754 -- Access Status
755 -- Internal table handler use only.
756 --
757 Procedure chk_routing_list_id (p_routing_category_id in number,
758 p_routing_list_id in number,
759 p_object_version_number in number) is
760 --
761 l_proc varchar2(72) := g_package||'chk_routing_list_id';
762 l_api_updating boolean;
763 l_enable_flag pqh_routing_lists.enable_flag%TYPE;
764 --
765 l_dummy varchar2(1);
766 --
767 cursor c1 is
768 select nvl(enable_flag,'N')
769 from pqh_routing_lists a
770 where a.routing_list_id = p_routing_list_id;
771 --
772 Begin
773 --
774 hr_utility.set_location('Entering:'||l_proc,5);
775 --
776 l_api_updating := pqh_rct_shd.api_updating
777 (p_routing_category_id => p_routing_category_id,
778 p_object_version_number => p_object_version_number);
779 --
780 if (l_api_updating
781 and nvl(p_routing_list_id,hr_api.g_number)
782 <> nvl(pqh_rct_shd.g_old_rec.routing_list_id,hr_api.g_number)
783 or not l_api_updating) and
784 p_routing_list_id is not null then
785 --
786 -- check if routing_list_id value exists in pqh_routing_lists table
787 --
788 open c1;
789 --
790 fetch c1 into l_enable_flag;
791 if c1%notfound then
792 --
793 close c1;
794 --
795 -- raise error as FK does not relate to PK in pqh_routing_lists
796 -- table.
797 --
798 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_FK1');
799 --
800 end if;
801 --
802 close c1;
803 --
804 -- Check if the Routing List is enabled .
805 --
806 if nvl(l_enable_flag,'N') = 'N' then
807 --
808 hr_utility.set_message(8302,'PQH_ROUTING_LIST_NOT_ENABLED');
809 hr_utility.raise_error;
810 --
811 End if;
812 --
813 end if;
814 --
815 hr_utility.set_location('Leaving:'||l_proc,10);
816 --
817 End chk_routing_list_id;
818 --
819 -- ----------------------------------------------------------------------------
820 -- |------< chk_override_role_id >------|
821 -- ----------------------------------------------------------------------------
822 --
823 -- Description
824 -- This procedure checks that a referenced foreign key actually exists
825 -- in the referenced table.
826 --
827 -- Pre-Conditions
828 -- None.
829 --
830 -- In Parameters
831 -- p_routing_category_id PK
832 -- p_override_role_id ID of FK column
833 -- p_object_version_number object version number
834 --
835 -- Post Success
836 -- Processing continues
837 --
838 -- Post Failure
839 -- Error raised.
840 --
841 -- Access Status
842 -- Internal table handler use only.
843 --
844 Procedure chk_override_role_id (p_routing_category_id in number,
845 p_override_role_id in number,
846 p_object_version_number in number) is
847 --
848 l_enable_flag pqh_roles.enable_flag%type;
849 --
850 l_proc varchar2(72) := g_package||'chk_override_role_id';
851 l_api_updating boolean;
852 --
853 cursor c1 is
854 select nvl(enable_flag,'N')
855 from pqh_roles a
856 where a.role_id = p_override_role_id;
857 --
858 Begin
859 --
860 hr_utility.set_location('Entering:'||l_proc,5);
864 p_object_version_number => p_object_version_number);
861 --
862 l_api_updating := pqh_rct_shd.api_updating
863 (p_routing_category_id => p_routing_category_id,
865 --
866 if (l_api_updating
867 and nvl(p_override_role_id,hr_api.g_number)
868 <> nvl(pqh_rct_shd.g_old_rec.override_role_id,hr_api.g_number)
869 or not l_api_updating) and
870 p_override_role_id is not null then
871 --
872 -- check if override_role_id value exists in
873 -- pqh_roles table
874 --
875 open c1;
876 --
877 fetch c1 into l_enable_flag;
878 if c1%notfound then
879 --
880 close c1;
881 --
882 -- raise error as FK does not relate to PK in pqh_routing_list_members
883 -- table.
884 --
885 hr_utility.set_message(8302,'PQH_INVALID_OVERRIDE_ROLE');
886 hr_utility.raise_error;
887 --
888 end if;
889 --
890 close c1;
891 --
892 -- When adding a new routing list or updating the override member,
893 -- Check if the override member is a enabled member.
894 --
895 if nvl(l_enable_flag,'N') <> 'Y' then
896 --
897 hr_utility.set_message(8302,'PQH_OVERRIDE_ROLE_NOT_ENABLED');
898 hr_utility.raise_error;
899 --
900 End if;
901 --
902 end if;
903 --
904 --
905 hr_utility.set_location('Leaving:'||l_proc,10);
906 --
907 End chk_override_role_id;
908 --
909 -- ----------------------------------------------------------------------------
910 -- |------< chk_override_user_id >------|
911 -- ----------------------------------------------------------------------------
912 --
913 -- Description
914 -- This procedure checks that a referenced foreign key actually exists
915 -- in the referenced table.
916 --
917 -- Pre-Conditions
918 -- None.
919 --
920 -- In Parameters
921 -- p_routing_category_id PK
922 -- p_override_user_id ID of FK column
923 -- p_object_version_number object version number
924 --
925 -- Post Success
926 -- Processing continues
927 --
928 -- Post Failure
929 -- Error raised.
930 --
931 -- Access Status
932 -- Internal table handler use only.
933 --
934 Procedure chk_override_user_id (p_routing_category_id in number,
935 p_override_role_id in number,
936 p_override_user_id in number,
937 p_object_version_number in number) is
938 --
939 l_dummy varchar2(1);
940 --
941 l_proc varchar2(72) := g_package||'chk_override_user_id';
942 l_api_updating boolean;
943 --
944 cursor c1 is
945 select null
946 from fnd_user a
947 where a.user_id = p_override_user_id;
948 --
949 cursor c2 is
950 select null
951 from pqh_role_users_v
952 Where user_id = p_override_user_id
953 and role_id = p_override_role_id;
954 Begin
955 --
956 hr_utility.set_location('Entering:'||l_proc,5);
957 --
958 l_api_updating := pqh_rct_shd.api_updating
959 (p_routing_category_id => p_routing_category_id,
960 p_object_version_number => p_object_version_number);
961 --
962 if (l_api_updating
963 and nvl(p_override_user_id,hr_api.g_number)
964 <> nvl(pqh_rct_shd.g_old_rec.override_user_id,hr_api.g_number)
965 or not l_api_updating) and
966 p_override_user_id is not null then
967 --
968 -- check if override_user_id value exists in
969 -- pqh_roles table
970 --
971 open c1;
972 --
973 fetch c1 into l_dummy;
974 if c1%notfound then
975 --
976 close c1;
977 --
978 -- raise error as FK does not relate to PK in pqh_routing_list_members
979 -- table.
980 --
981 hr_utility.set_message(8302,'PQH_INVALID_OVERRIDE_USER');
982 hr_utility.raise_error;
983 --
984 end if;
985 --
986 close c1;
987 --
988 end if;
989 --
990 If p_override_user_id IS NOT NULL then
991 --
992 If p_override_role_id IS NULL then
993 --
994 -- Should not be able to enter user without entering override role.
995 --
996 hr_utility.set_message(8302,'PQH_NO_OVERRIDE_ROLE_FOR_USER');
997 hr_utility.raise_error;
998 --
999 Else
1000 --
1001 -- Check if user belongs to the override role specified.
1002 --
1003 open c2;
1004 --
1005 fetch c2 into l_dummy;
1006 if c2%notfound then
1007 --
1008 close c2;
1009 --
1010 -- raise error
1011 --
1012 hr_utility.set_message(8302,'PQH_USER_NOT_IN_OVERRIDE_ROLE');
1013 hr_utility.raise_error;
1014 --
1015 end if;
1016 --
1017 close c2;
1018 --
1019 End if;
1020 --
1021 End if;
1022 --
1023 --
1024 hr_utility.set_location('Leaving:'||l_proc,10);
1025 --
1026 End chk_override_user_id;
1027 --
1028 -- ----------------------------------------------------------------------------
1032 -- Description
1029 -- |------< chk_position_structure_id >------|
1030 -- ----------------------------------------------------------------------------
1031 --
1033 -- This procedure checks that a referenced foreign key actually exists
1034 -- in the referenced table.
1035 --
1036 -- Pre-Conditions
1037 -- None.
1038 --
1039 -- In Parameters
1040 -- p_routing_category_id PK
1041 -- p_position_structure_id ID of FK column
1042 -- p_object_version_number object version number
1043 --
1044 -- Post Success
1045 -- Processing continues
1046 --
1047 -- Post Failure
1048 -- Error raised.
1049 --
1050 -- Access Status
1051 -- Internal table handler use only.
1052 --
1053 Procedure chk_position_structure_id (p_routing_category_id in number,
1054 p_position_structure_id in number,
1055 p_object_version_number in number) is
1056 --
1057 l_proc varchar2(72) := g_package||'chk_position_structure_id';
1058 l_api_updating boolean;
1059 l_dummy varchar2(1);
1060 --
1061 cursor c1 is
1062 select null
1063 from per_position_structures a
1064 where a.position_structure_id = p_position_structure_id;
1065 --
1066 Begin
1067 --
1068 hr_utility.set_location('Entering:'||l_proc,5);
1069 --
1070 l_api_updating := pqh_rct_shd.api_updating
1071 (p_routing_category_id => p_routing_category_id,
1072 p_object_version_number => p_object_version_number);
1073 --
1074 if (l_api_updating
1075 and nvl(p_position_structure_id,hr_api.g_number)
1076 <> nvl(pqh_rct_shd.g_old_rec.position_structure_id,hr_api.g_number)
1077 or not l_api_updating) and
1078 p_position_structure_id is not null then
1079 --
1080 -- check if position_structure_id value exists in per_position_structures table
1081 --
1082 open c1;
1083 --
1084 fetch c1 into l_dummy;
1085 if c1%notfound then
1086 --
1087 close c1;
1088 --
1089 -- raise error as FK does not relate to PK in per_position_structures
1090 -- table.
1091 --
1092 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_FK3');
1093 --
1094 end if;
1095 --
1096 close c1;
1097 --
1098 end if;
1099 --
1100 hr_utility.set_location('Leaving:'||l_proc,10);
1101 --
1102 End chk_position_structure_id;
1103 --
1104 -- ----------------------------------------------------------------------------
1105 -- |------< chk_override_position_id >------|
1106 -- ----------------------------------------------------------------------------
1107 --
1108 -- Description
1109 -- This procedure checks that a referenced foreign key actually exists
1110 -- in the referenced table.
1111 --
1112 -- Pre-Conditions
1113 -- None.
1114 --
1115 -- In Parameters
1116 -- p_routing_category_id PK
1117 -- p_override_position_id ID of FK column
1118 -- p_object_version_number object version number
1119 --
1120 -- Post Success
1121 -- Processing continues
1122 --
1123 -- Post Failure
1124 -- Error raised.
1125 --
1126 -- Access Status
1127 -- Internal table handler use only.
1128 --
1129 Procedure chk_override_position_id (p_routing_category_id in number,
1130 p_override_position_id in number,
1131 p_object_version_number in number) is
1132 --
1133 l_proc varchar2(72) := g_package||'chk_override_position_id';
1134 l_api_updating boolean;
1135 l_dummy varchar2(1);
1136 --
1137 cursor c1 is
1138 select null
1139 from hr_all_positions_f a
1140 where a.position_id = p_override_position_id;
1141 --
1142 /**
1143 Cursor csr_pos_in_pos_hier is
1144 select null
1145 from per_pos_structure_versions v, per_pos_structure_elements e
1146 where v.position_structure_id = p_position_structure_id
1147 and sysdate between v.date_from and
1148 nvl(v.date_to,to_date('31-12-4712','dd-mm-RRRR'))
1149 and v.pos_structure_version_id = e.pos_structure_version_id
1150 and (e.subordinate_position_id = p_override_position_id or
1151 e.parent_position_id = p_override_position_id);
1152 --
1153 **/
1154 --
1155 Begin
1156 --
1157 hr_utility.set_location('Entering:'||l_proc,5);
1158 --
1159 l_api_updating := pqh_rct_shd.api_updating
1160 (p_routing_category_id => p_routing_category_id,
1161 p_object_version_number => p_object_version_number);
1162 --
1163 if (l_api_updating
1164 and nvl(p_override_position_id,hr_api.g_number)
1165 <> nvl(pqh_rct_shd.g_old_rec.override_position_id,hr_api.g_number)
1166 or not l_api_updating) and
1167 p_override_position_id is not null then
1168 --
1169 -- check if override_position_id value exists in hr_all_positions_f table
1170 --
1171 open c1;
1172 --
1173 fetch c1 into l_dummy;
1174 if c1%notfound then
1175 --
1176 close c1;
1177 --
1178 -- raise error as FK does not relate to PK in hr_all_positions_f
1179 -- table.
1180 --
1181 pqh_rct_shd.constraint_error('PQH_ROUTING_CATEGORIES_FK4');
1182 --
1183 end if;
1184 --
1185 close c1;
1186 --
1187 end if;
1188 --
1189 /**
1190 -- If updating either position_strcuture or override_position
1191 -- or if inserting a new routing category using a position hierarchy ,
1192 -- Check if the override position is within the position hierarchy
1193 --
1194 if p_override_position_id is not null then
1195 --
1196 -- Check if the position belongs to the position structure
1197 --
1198 open csr_pos_in_pos_hier;
1199 --
1200 fetch csr_pos_in_pos_hier into l_dummy;
1201 --
1202 if csr_pos_in_pos_hier%notfound then
1203 --
1204 -- raise error
1205 --
1206 close csr_pos_in_pos_hier;
1207 hr_utility.set_message(8302,'PQH_POS_NOT_IN_POS_HIER');
1208 hr_utility.raise_error;
1209 --
1210 end if;
1211 --
1212 Close csr_pos_in_pos_hier;
1213 --
1214 End if;
1215 --
1216 **/
1217 hr_utility.set_location('Leaving:'||l_proc,10);
1218 --
1219 End chk_override_position_id;
1220 --
1221 --------------------------------------------------------------------------
1222 --
1223 -- Disallow creating routing category if there already exists a routing
1224 -- category with null rule under it
1225 --
1226 Procedure chk_universal_routing_exists(p_transaction_category_id in number,
1227 p_default_flag in varchar2)
1228 is
1229 --
1230 --
1231 TYPE cur_type IS REF CURSOR;
1232 csr_univ_routing cur_type;
1233 sql_stmt varchar2(1000);
1234 --
1235 Cursor csr_routing_type is
1236 Select member_cd
1237 From pqh_transaction_categories
1238 Where transaction_category_id = p_transaction_category_id;
1239 --
1240 --
1241 l_routing_type pqh_transaction_categories.member_cd%type;
1242 --
1243 l_prev_routing_cat pqh_routing_categories.routing_category_id%type;
1244 l_prev_range_name pqh_attribute_ranges.range_name%type;
1245 l_prev_list_name varchar2(250);
1246 null_rule_flag varchar2(1);
1247 --
1248 l_routing_category_id pqh_routing_categories.routing_category_id%type;
1249 l_list_name varchar2(250);
1250 l_range_name pqh_attribute_ranges.range_name%type;
1251 l_attribute_id pqh_attribute_ranges.attribute_id%type;
1252 l_from_char pqh_attribute_ranges.from_char%type;
1253 l_to_char pqh_attribute_ranges.to_char%type;
1254 l_from_number pqh_attribute_ranges.from_number%type;
1255 l_to_number pqh_attribute_ranges.to_number%type;
1256 l_from_date pqh_attribute_ranges.from_date%type;
1257 l_to_date pqh_attribute_ranges.to_date%type;
1258 --
1259 --
1260 l_proc varchar2(72) := g_package||'chk_universal_routing_exists';
1261 --
1262 --
1263 Begin
1264 --
1265 --
1266 hr_utility.set_location('Entering:'||l_proc,10);
1267 --
1268 -- We need to do the below validations , only if we are trying to create
1269 -- a non-default routing category.
1270 --
1271 If nvl(p_default_flag,'N') <> 'Y' then
1272 --
1273 Open csr_routing_type;
1274 Fetch csr_routing_type into l_routing_type;
1275 Close csr_routing_type;
1276 --
1277 l_prev_routing_cat := NULL;
1278 l_prev_list_name := NULL;
1279 l_prev_range_name := hr_api.g_varchar2; -- some random value
1280 null_rule_flag := 'N';
1281 --
1282 If l_routing_type = 'R' then
1283 sql_stmt := 'select b.routing_category_id,RLT.routing_list_name list_name,';
1284 elsif l_routing_type = 'P' then
1285 sql_stmt := 'select b.routing_category_id,PPS.name list_name,';
1286 Else
1287 sql_stmt := 'select b.routing_category_id,hr_general.decode_lookup('
1288 ||''''||'PQH_SUPERVISORY_HIERARCHY'||''''||','
1289 ||''''||'SUPERVISORY_HIERARCHY'||''''||'),';
1290 End if;
1291
1292 sql_stmt := sql_stmt ||'a.range_name, a.attribute_id, a.from_char, a.to_char, a.from_number, a.to_number, a.from_date, a.to_date from pqh_attribute_ranges a,pqh_routing_categories b, pqh_routing_lists RLT, per_position_structures PPS ';
1293
1294 sql_stmt := sql_stmt ||' Where b.transaction_category_id = :p_transaction_category_id and b.enable_flag = :p_enable_flag '
1295 ||' and nvl(b.default_flag,:null_val) <> :p_default_flag and nvl(b.delete_flag,:null_val2) <> :p_delete_flag and b.routing_category_id = a.routing_category_id(+) ';
1296
1297
1298 If l_routing_type = 'R' then
1299 sql_stmt := sql_stmt ||' AND b.routing_list_id = RLT.routing_list_id ';
1303 sql_stmt := sql_stmt ||' AND b.routing_list_id is null AND b.position_structure_id is null ';
1300 elsif l_routing_type = 'P' then
1301 sql_stmt := sql_stmt ||' AND b.position_structure_id = PPS.position_structure_id ';
1302 Else
1304 End if;
1305
1306 sql_stmt := sql_stmt || ' and a.routing_list_member_id(+) is null and a.position_id(+) is null and a.assignment_id(+) is null order by 1,2,3';
1307 --
1308 Open csr_univ_routing for sql_stmt using p_transaction_category_id,
1309 'Y','N','Y','N','Y';
1310 --
1311 Loop
1312 --
1313 Fetch csr_univ_routing into l_routing_category_id,l_list_name,l_range_name,
1314 l_attribute_id,l_from_char,l_to_char,
1315 l_from_number,l_to_number,l_from_date,l_to_date;
1316 --
1317 If csr_univ_routing%notfound then
1318 Exit;
1319 End if;
1320 --
1321 -- Check If we are in the same rule under a routing category
1322 --
1323 If l_prev_routing_cat = l_routing_category_id AND
1324 l_prev_range_name = l_range_name then
1325 --
1326 If l_from_char IS NOT NULL or l_to_char IS NOT NULL or
1327 l_from_number IS NOT NULL or l_to_number IS NOT NULL or
1328 l_from_date IS NOT NULL or l_to_date IS NOT NULL then
1329 --
1330 null_rule_flag := 'N';
1331 --
1332 End if;
1333 --
1334 Else
1335 --
1336 -- If this is new rule , check if the previous was a null rule.
1337 -- Also check if the prev routing category had any rule at all .
1338 -- Depending on the case , raise a meaningful message.
1339 --
1340 If null_rule_flag = 'Y' then
1341 --
1342 Close csr_univ_routing;
1343 --
1344 If l_prev_range_name IS NULL then
1345 --
1346 hr_utility.set_message(8302,'PQH_RCT_WITH_NO_RULE_EXISTS');
1347 hr_utility.set_message_token('LIST_NAME',l_prev_list_name);
1348 hr_utility.raise_error;
1349 --
1350 Else
1351 --
1352 hr_utility.set_message(8302,'PQH_NULL_RULE_EXISTS');
1353 hr_utility.set_message_token('LIST_NAME',l_prev_list_name);
1354 hr_utility.raise_error;
1355 --
1356 End if;
1357 --
1358 End if;
1359 --
1360 -- If it was not a null rule , then re-initialise variables
1361 -- and proceed processing next rule .
1362 --
1363 l_prev_routing_cat := l_routing_category_id;
1364 l_prev_list_name := l_list_name;
1365 l_prev_range_name := l_range_name;
1366 null_rule_flag := 'Y';
1367 --
1368 --
1369 If l_from_char IS NOT NULL or l_to_char IS NOT NULL or
1370 l_from_number IS NOT NULL or l_to_number IS NOT NULL or
1371 l_from_date IS NOT NULL or l_to_date IS NOT NULL then
1372 --
1373 null_rule_flag := 'N';
1374 --
1375 End if;
1376 --
1377 End if;
1378 --
1379 End loop;
1380 --
1381 Close csr_univ_routing;
1382 --
1383 --
1384 -- check if the last rule was a null rule.
1385 --
1386 If null_rule_flag = 'Y' then
1387 --
1388 If l_prev_range_name IS NULL then
1389 --
1390 hr_utility.set_message(8302,'PQH_RCT_WITH_NO_RULE_EXISTS');
1391 hr_utility.set_message_token('LIST_NAME',l_prev_list_name);
1392 hr_utility.raise_error;
1393 --
1394 Else
1395 --
1396 hr_utility.set_message(8302,'PQH_NULL_RULE_EXISTS');
1397 hr_utility.set_message_token('LIST_NAME',l_prev_list_name);
1398 hr_utility.raise_error;
1399 --
1400 End if;
1401 --
1402 End if;
1403 --
1404 End if;
1405 --
1406 --
1407 hr_utility.set_location('Leaving:'||l_proc,10);
1408 --
1409 --
1410 End;
1411 --
1412 ------------------------------------------------------------------------------
1413 --
1414 Function chk_if_routing_cat_exists
1415 (p_transaction_category_id in pqh_transaction_categories.transaction_category_id%type,
1416 p_routing_type in pqh_transaction_categories.member_cd%type)
1417 --
1418 RETURN BOOLEAN is
1419 --
1420 -- Declare cursors and local variables
1421 --
1422 type cur_type IS REF CURSOR;
1423 rct_cur cur_type;
1424 sql_stmt varchar2(1000);
1425 --
1426 l_routing_category_id pqh_routing_categories.routing_category_id%type;
1427 --
1428 l_proc varchar2(72) := g_package||'chk_if_routing_cat_exists';
1429 --
1430 Begin
1431 --
1432 hr_utility.set_location('Entering:'|| l_proc, 10);
1433 --
1434 sql_stmt := 'Select routing_category_id from pqh_routing_categories where transaction_category_id = :t';
1435 --
1436 -- Select only the routing categories that belong to the current routing
1437 -- type of the transaction category.
1438 --
1439 If p_routing_type = 'R' then
1440 --
1441 sql_stmt := sql_stmt ||' and routing_list_id is not null';
1442 --
1443 Elsif p_routing_type = 'P' then
1444 --
1445 sql_stmt := sql_stmt ||' and position_structure_id is not null';
1446 --
1447 Else
1448 --
1449 sql_stmt := sql_stmt ||' and routing_list_id is null and position_structure_id is null';
1450 --
1451 End if;
1452 --
1453 Open rct_cur for sql_stmt using p_transaction_category_id;
1454 --
1455 Fetch rct_cur into l_routing_category_id;
1456 --
1457 If rct_cur%found then
1458 RETURN TRUE;
1459 End if;
1460 --
1461 hr_utility.set_location('Leaving:'|| l_proc, 70);
1462 --
1463 RETURN FALSE;
1464 --
1465 End;
1466
1467
1468 -- ----------------------------------------------------------------------------
1469 -- |---------------------------< insert_validate >----------------------------|
1470 -- ----------------------------------------------------------------------------
1471 Procedure insert_validate(p_rec in pqh_rct_shd.g_rec_type
1472 ,p_effective_date in date) is
1473 --
1474 l_proc varchar2(72) := g_package||'insert_validate';
1475 --
1476 Begin
1477 hr_utility.set_location('Entering:'||l_proc, 5);
1478 --
1479 -- Call all supporting business operations
1480 --
1481 --
1482 chk_routing_category_id
1483 (p_routing_category_id => p_rec.routing_category_id,
1484 p_object_version_number => p_rec.object_version_number);
1485 --
1486 chk_transaction_category_id
1487 (p_routing_category_id => p_rec.routing_category_id,
1488 p_transaction_category_id => p_rec.transaction_category_id,
1489 p_object_version_number => p_rec.object_version_number);
1490 --
1491 chk_ins_upd_routing_category
1492 (p_routing_category_id => p_rec.routing_category_id,
1493 p_transaction_category_id => p_rec.transaction_category_id,
1494 p_enable_flag => p_rec.enable_flag,
1495 p_object_version_number => p_rec.object_version_number);
1496 --
1497 chk_list_id
1498 (p_routing_category_id => p_rec.routing_category_id,
1499 p_routing_list_id => p_rec.routing_list_id,
1500 p_position_structure_id => p_rec.position_structure_id,
1501 p_transaction_category_id => p_rec.transaction_category_id,
1502 p_object_version_number => p_rec.object_version_number);
1503 --
1504 /**
1505 chk_universal_routing_exists
1506 (p_transaction_category_id => p_rec.transaction_category_id,
1507 p_default_flag => p_rec.default_flag);
1508 **/
1509 --
1510 chk_enable_flag
1511 (p_routing_category_id => p_rec.routing_category_id,
1512 p_enable_flag => p_rec.enable_flag,
1513 p_transaction_category_id => p_rec.transaction_category_id,
1514 p_effective_date => p_effective_date,
1515 p_object_version_number => p_rec.object_version_number);
1516 --
1517 chk_default_flag
1518 (p_routing_category_id => p_rec.routing_category_id,
1519 p_default_flag => p_rec.default_flag,
1520 p_effective_date => p_effective_date,
1521 p_object_version_number => p_rec.object_version_number);
1522 --
1523 chk_delete_flag
1524 (p_routing_category_id => p_rec.routing_category_id,
1525 p_delete_flag => p_rec.delete_flag,
1526 p_effective_date => p_effective_date,
1527 p_object_version_number => p_rec.object_version_number);
1528 --
1529 chk_routing_list_id
1530 (p_routing_category_id => p_rec.routing_category_id,
1531 p_routing_list_id => p_rec.routing_list_id,
1532 p_object_version_number => p_rec.object_version_number);
1533 --
1534 chk_override_role_id
1535 (p_routing_category_id => p_rec.routing_category_id,
1536 p_override_role_id => p_rec.override_role_id,
1537 p_object_version_number => p_rec.object_version_number);
1538 --
1539 chk_override_user_id
1540 (p_routing_category_id => p_rec.routing_category_id,
1541 p_override_role_id => p_rec.override_role_id,
1542 p_override_user_id => p_rec.override_user_id,
1543 p_object_version_number => p_rec.object_version_number);
1544 --
1545 chk_position_structure_id
1546 (p_routing_category_id => p_rec.routing_category_id,
1547 p_position_structure_id => p_rec.position_structure_id,
1548 p_object_version_number => p_rec.object_version_number);
1549 --
1550 chk_override_position_id
1551 (p_routing_category_id => p_rec.routing_category_id,
1552 p_override_position_id => p_rec.override_position_id,
1553 p_object_version_number => p_rec.object_version_number);
1554 --
1555 --
1556 hr_utility.set_location(' Leaving:'||l_proc, 10);
1557 End insert_validate;
1558 --
1559 -- ----------------------------------------------------------------------------
1560 -- |---------------------------< update_validate >----------------------------|
1561 -- ----------------------------------------------------------------------------
1562 Procedure update_validate(p_rec in pqh_rct_shd.g_rec_type
1563 ,p_effective_date in date) is
1564 --
1565 l_proc varchar2(72) := g_package||'update_validate';
1566 --
1567 Begin
1568 hr_utility.set_location('Entering:'||l_proc, 5);
1569 --
1570 -- Call all supporting business operations
1571 --
1572 chk_routing_category_id
1573 (p_routing_category_id => p_rec.routing_category_id,
1574 p_object_version_number => p_rec.object_version_number);
1575 --
1576 chk_transaction_category_id
1577 (p_routing_category_id => p_rec.routing_category_id,
1578 p_transaction_category_id => p_rec.transaction_category_id,
1579 p_object_version_number => p_rec.object_version_number);
1580 --
1581 chk_ins_upd_routing_category
1582 (p_routing_category_id => p_rec.routing_category_id,
1583 p_transaction_category_id => p_rec.transaction_category_id,
1584 p_enable_flag => p_rec.enable_flag,
1585 p_object_version_number => p_rec.object_version_number);
1586 --
1587 chk_list_id
1588 (p_routing_category_id => p_rec.routing_category_id,
1589 p_routing_list_id => p_rec.routing_list_id,
1590 p_position_structure_id => p_rec.position_structure_id,
1591 p_transaction_category_id => p_rec.transaction_category_id,
1592 p_object_version_number => p_rec.object_version_number);
1593 --
1594 chk_enable_flag
1595 (p_routing_category_id => p_rec.routing_category_id,
1596 p_enable_flag => p_rec.enable_flag,
1597 p_transaction_category_id => p_rec.transaction_category_id,
1598 p_effective_date => p_effective_date,
1599 p_object_version_number => p_rec.object_version_number);
1600 --
1601 chk_default_flag
1602 (p_routing_category_id => p_rec.routing_category_id,
1603 p_default_flag => p_rec.default_flag,
1604 p_effective_date => p_effective_date,
1605 p_object_version_number => p_rec.object_version_number);
1606 --
1607 chk_delete_flag
1608 (p_routing_category_id => p_rec.routing_category_id,
1609 p_delete_flag => p_rec.delete_flag,
1610 p_effective_date => p_effective_date,
1611 p_object_version_number => p_rec.object_version_number);
1612 --
1613 chk_routing_list_id
1614 (p_routing_category_id => p_rec.routing_category_id,
1615 p_routing_list_id => p_rec.routing_list_id,
1616 p_object_version_number => p_rec.object_version_number);
1617 --
1618 chk_override_role_id
1619 (p_routing_category_id => p_rec.routing_category_id,
1620 p_override_role_id => p_rec.override_role_id,
1621 p_object_version_number => p_rec.object_version_number);
1622 --
1623 chk_override_user_id
1624 (p_routing_category_id => p_rec.routing_category_id,
1625 p_override_role_id => p_rec.override_role_id,
1626 p_override_user_id => p_rec.override_user_id,
1627 p_object_version_number => p_rec.object_version_number);
1628 --
1629 chk_position_structure_id
1630 (p_routing_category_id => p_rec.routing_category_id,
1631 p_position_structure_id => p_rec.position_structure_id,
1632 p_object_version_number => p_rec.object_version_number);
1633 --
1634 chk_override_position_id
1635 (p_routing_category_id => p_rec.routing_category_id,
1636 p_override_position_id => p_rec.override_position_id,
1637 p_object_version_number => p_rec.object_version_number);
1638 --
1639 --
1640 hr_utility.set_location(' Leaving:'||l_proc, 10);
1641 --
1642 End update_validate;
1643 --
1644 -- ----------------------------------------------------------------------------
1645 -- |---------------------------< delete_validate >----------------------------|
1646 -- ----------------------------------------------------------------------------
1647 Procedure delete_validate(p_rec in pqh_rct_shd.g_rec_type
1648 ,p_effective_date in date) is
1649 --
1650 l_proc varchar2(72) := g_package||'delete_validate';
1651 --
1652 Begin
1653 hr_utility.set_location('Entering:'||l_proc, 5);
1654 --
1655 -- Call all supporting business operations
1656 --
1657 hr_utility.set_location(' Leaving:'||l_proc, 10);
1658 End delete_validate;
1659 --
1660 end pqh_rct_bus;
1661