[Home] [Help]
PACKAGE BODY: APPS.PQH_TCT_BUS
Source
1 Package Body pqh_tct_bus as
2 /* $Header: pqtctrhi.pkb 120.4.12000000.2 2007/04/19 12:48:04 brsinha noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tct_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_transaction_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 -- transaction_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_transaction_category_id(p_transaction_category_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_transaction_category_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_tct_shd.api_updating
47 (p_transaction_category_id => p_transaction_category_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_transaction_category_id,hr_api.g_number)
52 <> pqh_tct_shd.g_old_rec.transaction_category_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_tct_shd.constraint_error('PQH_TRANSACTION_CATEGORIES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_transaction_category_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_tct_shd.constraint_error('PQH_TRANSACTION_CATEGORIES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_transaction_category_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_consolid_table_route_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_transaction_category_id PK
89 -- p_consolidated_table_route_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_consolid_table_route_id (p_transaction_category_id in number,
102 p_consolidated_table_route_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_consolid_table_route_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_table_route a
112 where a.table_route_id = p_consolidated_table_route_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_tct_shd.api_updating
119 (p_transaction_category_id => p_transaction_category_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_consolidated_table_route_id,hr_api.g_number)
124 <> nvl(pqh_tct_shd.g_old_rec.consolidated_table_route_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if consolidated_table_route_id value exists in pqh_table_route table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in pqh_table_route
137 -- table.
138 --
139 pqh_tct_shd.constraint_error('PQH_TRANSACTION_CATEGORIES_FK1');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_consolid_table_route_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_master_table_route_id >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_transaction_category_id PK
164 -- p_master_table_route_id ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_master_table_route_id
177 (p_transaction_category_id in number,
178 p_master_table_route_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_master_table_route_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_table_route a
188 where a.table_route_id = p_master_table_route_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_tct_shd.api_updating
195 (p_transaction_category_id => p_transaction_category_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_master_table_route_id,hr_api.g_number)
200 <> nvl(pqh_tct_shd.g_old_rec.master_table_route_id,hr_api.g_number)
201 or not l_api_updating)
202 and p_master_table_route_id IS NOT NULL then
203 --
204 -- check if master_table_route_id value exists in pqh_table_route 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_table_route
214 -- table.
215 --
216 pqh_tct_shd.constraint_error('PQH_TRANSACTION_CATEGORIES_FK2');
217 --
218 --
219 end if;
220 --
221 close c1;
222 --
223 end if;
224 --
225 hr_utility.set_location('Leaving:'||l_proc,10);
226 --
227 End chk_master_table_route_id;
228 --
229 --
230 -- ----------------------------------------------------------------------------
231 -- |------< chk_business_group_id >------|
232 -- ----------------------------------------------------------------------------
233 --
234 -- Description
235 -- This procedure checks that a referenced foreign key actually exists
236 -- in the referenced table.
237 --
238 -- Pre-Conditions
239 -- None.
240 --
241 --
242 Procedure chk_business_group_id
243 (p_transaction_category_id in number,
244 p_business_group_id in number,
245 p_object_version_number in number) is
246 --
247 l_proc varchar2(72) := g_package||'chk_business_group_id';
248 l_api_updating boolean;
249 l_dummy varchar2(1);
250 --
251 cursor c1 is
252 select null
253 from hr_all_organization_units a
254 where a.organization_id = p_business_group_id;
255 --
256 Begin
257 --
258 hr_utility.set_location('Entering:'||l_proc,5);
259 --
260 l_api_updating := pqh_tct_shd.api_updating
261 (p_transaction_category_id => p_transaction_category_id,
262 p_object_version_number => p_object_version_number);
263 --
264 if (l_api_updating
265 and nvl(p_business_group_id,hr_api.g_number)
266 <> nvl(pqh_tct_shd.g_old_rec.business_group_id,hr_api.g_number)
267 or not l_api_updating)
268 and p_business_group_id is not null then
269 --
270 -- check if business_group_id exists in hr_all_organization_units table
271 --
272 open c1;
273 --
274 fetch c1 into l_dummy;
275 if c1%notfound then
276 --
277 close c1;
278 --
279 hr_utility.set_message(8302,'PQH_INVALID_BUSINESS_GROUP');
280 hr_utility.raise_error;
281 --
282 End if;
283 --
284 Close c1;
285 --
286 end if;
287 --
288 hr_utility.set_location('Leaving:'||l_proc,10);
289 --
290 End chk_business_group_id;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |------< chk_setup_type_cd >------|
294 -- ----------------------------------------------------------------------------
295 --
296 -- Description
297 -- This procedure is used to check that the lookup value is valid.
298 --
299 -- Pre Conditions
300 -- None.
301 --
302 -- In Parameters
303 -- transaction_category_id PK of record being inserted or updated.
304 -- setup_type_cd Value of lookup code.
305 -- effective_date effective date
306 -- object_version_number Object version number of record being
307 -- inserted or updated.
308 --
309 -- Post Success
310 -- Processing continues
311 --
312 -- Post Failure
313 -- Error handled by procedure
314 --
315 -- Access Status
316 -- Internal table handler use only.
317 --
318 Procedure chk_setup_type_cd
319 (p_transaction_category_id in number,
320 p_setup_type_cd in varchar2,
321 p_effective_date in date,
322 p_object_version_number in number) is
323 --
324 l_proc varchar2(72) := g_package||'chk_setup_type_cd';
325 l_api_updating boolean;
326 --
327 Begin
328 --
329 hr_utility.set_location('Entering:'||l_proc, 5);
330 --
331 l_api_updating := pqh_tct_shd.api_updating
332 (p_transaction_category_id => p_transaction_category_id,
333 p_object_version_number => p_object_version_number);
334 --
335 if (l_api_updating
336 and p_setup_type_cd
337 <> nvl(pqh_tct_shd.g_old_rec.setup_type_cd,hr_api.g_varchar2)
338 or not l_api_updating)
339 and p_setup_type_cd is not null then
340 --
341 -- check if value of lookup falls within lookup type.
342 --
343 if hr_api.not_exists_in_hr_lookups
344 (p_lookup_type => 'PQH_TXN_CAT_SETUP_TYPE',
345 p_lookup_code => p_setup_type_cd,
346 p_effective_date => p_effective_date) then
347 --
348 -- raise error as does not exist as lookup
349 --
350 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
351 hr_utility.raise_error;
352 --
353 End if;
354 --
355 End if;
356 --
357 --
358 hr_utility.set_location('Leaving:'||l_proc,10);
359 --
360 End chk_setup_type_cd;
361 --
362 --
363 -- ----------------------------------------------------------------------------
364 -- |------< chk_route_validated_txn_flag >------|
365 -- ----------------------------------------------------------------------------
366 --
367 -- Description
368 -- This procedure is used to check that the lookup value is valid.
369 --
370 -- Pre Conditions
371 -- None.
372 --
373 -- In Parameters
374 -- transaction_category_id PK of record being inserted or updated.
375 -- route_validated_txn_flag Value of lookup code.
376 -- effective_date effective date
377 -- object_version_number Object version number of record being
378 -- inserted or updated.
379 --
380 -- Post Success
381 -- Processing continues
382 --
383 -- Post Failure
384 -- Error handled by procedure
385 --
386 -- Access Status
387 -- Internal table handler use only.
388 --
389 Procedure chk_route_validated_txn_flag(p_transaction_category_id in number,
390 p_route_validated_txn_flag in varchar2,
391 p_effective_date in date,
392 p_object_version_number in number) is
393 --
394 l_proc varchar2(72) := g_package||'chk_route_validated_txn_flag';
395 l_api_updating boolean;
396 --
397 Begin
398 --
399 hr_utility.set_location('Entering:'||l_proc, 5);
400 --
401 l_api_updating := pqh_tct_shd.api_updating
402 (p_transaction_category_id => p_transaction_category_id,
403 p_object_version_number => p_object_version_number);
404 --
405 if (l_api_updating
406 and p_route_validated_txn_flag
407 <> nvl(pqh_tct_shd.g_old_rec.route_validated_txn_flag,hr_api.g_varchar2)
408 or not l_api_updating) then
409 --
410 -- check if value of lookup falls within lookup type.
411 --
412 --
413 if hr_api.not_exists_in_hr_lookups
414 (p_lookup_type => 'YES_NO',
415 p_lookup_code => p_route_validated_txn_flag,
416 p_effective_date => p_effective_date) then
417 --
418 -- raise error as does not exist as lookup
419 --
420 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
421 hr_utility.raise_error;
422 --
423 end if;
424 --
425 end if;
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 end chk_route_validated_txn_flag;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |------< chk_workflow_enable_flag >------|
433 -- ----------------------------------------------------------------------------
434 --
435 -- Description
436 -- This procedure is used to check that the lookup value is valid.
437 --
438 -- Pre Conditions
439 -- None.
440 --
441 -- In Parameters
442 -- transaction_category_id PK of record being inserted or updated.
443 -- route_validated_txn_flag Value of lookup code.
444 -- effective_date effective date
445 -- object_version_number Object version number of record being
446 -- inserted or updated.
447 --
448 -- Post Success
449 -- Processing continues
450 --
451 -- Post Failure
452 -- Error handled by procedure
453 --
454 -- Access Status
455 -- Internal table handler use only.
456 --
457 Procedure chk_workflow_enable_flag(p_transaction_category_id in number,
458 p_workflow_enable_flag in varchar2,
459 p_effective_date in date,
460 p_object_version_number in number) is
461 --
462 l_proc varchar2(72) := g_package||'chk_workflow_enable_flag';
463 l_api_updating boolean;
464 --
465 Begin
466 --
467 hr_utility.set_location('Entering:'||l_proc, 5);
468 --
469 l_api_updating := pqh_tct_shd.api_updating
470 (p_transaction_category_id => p_transaction_category_id,
471 p_object_version_number => p_object_version_number);
472 --
473 if (l_api_updating
474 and p_workflow_enable_flag
475 <> nvl(pqh_tct_shd.g_old_rec.workflow_enable_flag,hr_api.g_varchar2)
476 or not l_api_updating) and
477 p_workflow_enable_flag is NOT NULL then
478 --
479 -- check if value of lookup falls within lookup type.
480 --
481 --
482 if hr_api.not_exists_in_hr_lookups
483 (p_lookup_type => 'YES_NO',
484 p_lookup_code => p_workflow_enable_flag,
485 p_effective_date => p_effective_date) then
486 --
487 -- raise error as does not exist as lookup
488 --
489 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
490 hr_utility.raise_error;
491 --
492 end if;
493 --
494 end if;
495 --
496 hr_utility.set_location('Leaving:'||l_proc,10);
497 --
498 end chk_workflow_enable_flag;
499 --
500 -- ----------------------------------------------------------------------------
501 -- |------< chk_enable_flag >------|
502 -- ----------------------------------------------------------------------------
503 --
504 -- Description
505 -- This procedure is used to check that the lookup value is valid.
506 --
507 -- Pre Conditions
508 -- None.
509 --
510 -- In Parameters
511 -- transaction_category_id PK of record being inserted or updated.
512 -- route_validated_txn_flag Value of lookup code.
513 -- effective_date effective date
514 -- object_version_number Object version number of record being
515 -- inserted or updated.
516 --
517 -- Post Success
518 -- Processing continues
519 --
520 -- Post Failure
521 -- Error handled by procedure
522 --
523 -- Access Status
524 -- Internal table handler use only.
525 --
526 Procedure chk_enable_flag(p_transaction_category_id in number,
527 p_enable_flag in varchar2,
528 p_effective_date in date,
529 p_object_version_number in number) is
530 --
531 l_proc varchar2(72) := g_package||'chk_enable_flag';
532 l_api_updating boolean;
533 --
534 Begin
535 --
536 hr_utility.set_location('Entering:'||l_proc, 5);
537 --
538 l_api_updating := pqh_tct_shd.api_updating
539 (p_transaction_category_id => p_transaction_category_id,
540 p_object_version_number => p_object_version_number);
541 --
542 if (l_api_updating
543 and p_enable_flag
544 <> nvl(pqh_tct_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
545 or not l_api_updating) and
546 p_enable_flag is NOT NULL then
547 --
548 -- check if value of lookup falls within lookup type.
549 --
550 --
551 if hr_api.not_exists_in_hr_lookups
552 (p_lookup_type => 'YES_NO',
553 p_lookup_code => p_enable_flag,
554 p_effective_date => p_effective_date) then
555 --
556 -- raise error as does not exist as lookup
557 --
558 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
559 hr_utility.raise_error;
560 --
561 end if;
562 --
563 end if;
564 --
565 hr_utility.set_location('Leaving:'||l_proc,10);
566 --
567 end chk_enable_flag;
568 --
569 -- ----------------------------------------------------------------------------
570 -- |------< chk_post_style_cd >------|
571 -- ----------------------------------------------------------------------------
572 --
573 -- Description
574 -- This procedure is used to check that the lookup value is valid.
575 --
576 -- Pre Conditions
577 -- None.
578 --
579 -- In Parameters
580 -- transaction_category_id PK of record being inserted or updated.
581 -- post_style_cd Value of lookup code.
582 -- effective_date effective date
583 -- object_version_number Object version number of record being
584 -- inserted or updated.
585 --
586 -- Post Success
587 -- Processing continues
588 --
589 -- Post Failure
590 -- Error handled by procedure
591 --
592 -- Access Status
593 -- Internal table handler use only.
594 --
595 Procedure chk_post_style_cd(p_transaction_category_id in number,
596 p_post_style_cd in varchar2,
597 p_effective_date in date,
598 p_object_version_number in number) is
599 --
600 l_proc varchar2(72) := g_package||'chk_post_style_cd';
601 l_api_updating boolean;
602 --
603 Begin
604 --
605 hr_utility.set_location('Entering:'||l_proc, 5);
606 --
607 l_api_updating := pqh_tct_shd.api_updating
608 (p_transaction_category_id => p_transaction_category_id,
609 p_object_version_number => p_object_version_number);
610 --
611 if (l_api_updating
612 and p_post_style_cd
613 <> nvl(pqh_tct_shd.g_old_rec.post_style_cd,hr_api.g_varchar2)
614 or not l_api_updating) then
615 --
616 -- check if value of lookup falls within lookup type.
617 --
618 --
619 if hr_api.not_exists_in_hr_lookups
620 (p_lookup_type => 'PQH_POST_STYLE',
621 p_lookup_code => p_post_style_cd,
622 p_effective_date => p_effective_date) then
623 --
624 -- raise error as does not exist as lookup
625 --
626 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
627 hr_utility.raise_error;
628 --
629 end if;
630 --
631 end if;
632 --
633 hr_utility.set_location('Leaving:'||l_proc,10);
634 --
635 end chk_post_style_cd;
636 --
637 --
638 --
639 Procedure chk_member_cd_upd_allowed(p_transaction_category_id in number)
640 is
641 --
642 l_dummy varchar2(1);
643 l_proc varchar2(72) := g_package||'chk_member_cd_upd_allowed';
644 --
645 Cursor c1 is
646 Select null
647 from pqh_routing_categories a
648 where a.transaction_category_id = p_transaction_category_id
649 and nvl(a.enable_flag,'N') = 'Y';
650 Begin
651 --
652 hr_utility.set_location('Entering:'||l_proc, 5);
653 --
654 Open c1;
655 --
656 Fetch c1 into l_dummy;
657 if c1%found then
658 --
659 Close c1;
660 hr_utility.set_message(8302,'PQH_INVALID_MEMBER_CD_UPD');
661 hr_utility.raise_error;
662 --
663 End if;
664 Close c1;
665 --
666 hr_utility.set_location('Leaving:'||l_proc,10);
667 --
668 End chk_member_cd_upd_allowed;
669 --
670 -- ----------------------------------------------------------------------------
671 -- |------< chk_member_cd >------|
672 -- ----------------------------------------------------------------------------
673 --
674 -- Description
675 -- This procedure is used to check that the lookup value is valid.
676 --
677 -- Pre Conditions
678 -- None.
679 --
680 -- In Parameters
681 -- transaction_category_id PK of record being inserted or updated.
682 -- member_cd Value of lookup code.
683 -- effective_date effective date
684 -- object_version_number Object version number of record being
685 -- inserted or updated.
686 --
687 -- Post Success
688 -- Processing continues
689 --
690 -- Post Failure
691 -- Error handled by procedure
692 --
693 -- Access Status
694 -- Internal table handler use only.
695 --
696 Procedure chk_member_cd(p_transaction_category_id in number,
697 p_member_cd in varchar2,
698 p_freeze_status_cd in varchar2,
699 p_effective_date in date,
700 p_object_version_number in number) is
701 --
702 l_proc varchar2(72) := g_package||'chk_member_cd';
703 l_api_updating boolean;
704 --
705 Begin
706 --
707 hr_utility.set_location('Entering:'||l_proc, 5);
708 --
709 l_api_updating := pqh_tct_shd.api_updating
710 (p_transaction_category_id => p_transaction_category_id,
711 p_object_version_number => p_object_version_number);
712 --
713 if (l_api_updating
714 and p_member_cd
715 <> nvl(pqh_tct_shd.g_old_rec.member_cd,hr_api.g_varchar2)
716 or not l_api_updating) then
717 --
718 -- check if value of lookup falls within lookup type.
719 --
720 --
721 if hr_api.not_exists_in_hr_lookups
722 (p_lookup_type => 'PQH_MEMBER_CD',
723 p_lookup_code => p_member_cd,
724 p_effective_date => p_effective_date) then
725 --
726 -- raise error as does not exist as lookup
727 --
728 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
729 hr_utility.raise_error;
730 --
731 end if;
732 --
733 End if;
734 --
735 -- Allow member code to be updated only if there are no enabled routing
736 -- categories under the transaction category.
737 --
738 /****
739 if l_api_updating
740 and p_member_cd
741 <> nvl(pqh_tct_shd.g_old_rec.member_cd,hr_api.g_varchar2) then
742 --
743 chk_member_cd_upd_allowed
744 (p_transaction_category_id => p_transaction_category_id);
745 --
746 End if;
747 ****/
748 --
749 hr_utility.set_location('Leaving:'||l_proc,10);
750 --
751 end chk_member_cd;
752 --
753 -- ----------------------------------------------------------------------------
754 -- |------< chk_future_action_cd >------|
755 -- ----------------------------------------------------------------------------
756 --
757 -- Description
758 -- This procedure is used to check that the lookup value is valid.
759 --
760 -- Pre Conditions
761 -- None.
762 --
763 -- In Parameters
764 -- transaction_category_id PK of record being inserted or updated.
765 -- future_action_cd Value of lookup code.
766 -- effective_date effective date
767 -- object_version_number Object version number of record being
768 -- inserted or updated.
769 --
770 -- Post Success
771 -- Processing continues
772 --
773 -- Post Failure
774 -- Error handled by procedure
775 --
776 -- Access Status
777 -- Internal table handler use only.
778 --
779 Procedure chk_future_action_cd(p_transaction_category_id in number,
780 p_future_action_cd in varchar2,
781 p_effective_date in date,
782 p_object_version_number in number) is
783 --
784 l_proc varchar2(72) := g_package||'chk_future_action_cd';
785 l_api_updating boolean;
786 --
787 Begin
788 --
789 hr_utility.set_location('Entering:'||l_proc, 5);
790 --
791 l_api_updating := pqh_tct_shd.api_updating
792 (p_transaction_category_id => p_transaction_category_id,
793 p_object_version_number => p_object_version_number);
794 --
795 if (l_api_updating
796 and p_future_action_cd
797 <> nvl(pqh_tct_shd.g_old_rec.future_action_cd,hr_api.g_varchar2)
798 or not l_api_updating) then
799 --
800 -- check if value of lookup falls within lookup type.
801 --
802 --
803 if hr_api.not_exists_in_hr_lookups
804 (p_lookup_type => 'PQH_FUTURE_ACTION',
805 p_lookup_code => p_future_action_cd,
806 p_effective_date => p_effective_date) then
807 --
808 -- raise error as does not exist as lookup
809 --
810 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
811 hr_utility.raise_error;
812 --
813 end if;
814 --
815 end if;
816 --
817 hr_utility.set_location('Leaving:'||l_proc,10);
818 --
819 end chk_future_action_cd;
820 --
821 -- ----------------------------------------------------------------------------
822 -- | ------< chk_valid_routing_exists >------|
823 -- ----------------------------------------------------------------------------
824 --
825 -- Description
826 -- This procedure is used to check if there is at least one routing
827 -- category under a transaction_category .
828 -- If there are any list identifiers defined , then there must be at least
829 -- 1 routing rule defined under each routing category .
830 -- If there are no list identfiers defined , there must be one routing
831 -- category with no rules . All the above checks are performed on trying to
832 -- freeze the transaction category.
833 --
834 --
835 Procedure chk_valid_routing_exists
836 (p_transaction_category_id in number,
837 p_routing_type in varchar2) is
838 --
839 -- The foll cursor checks if any list identifers have been defined
840 -- for a transaction category.
841 --
842 Cursor csr_list_ident_defined is
843 Select count(*)
844 From pqh_txn_category_attributes
845 Where transaction_category_id = p_transaction_category_id
846 AND list_identifying_flag = 'Y';
847 --
848 --
849 TYPE cur_type IS REF CURSOR;
850 csr_routing cur_type;
851 sql_stmt varchar2(1000);
852 --
853 csr_auth1 cur_type;
854 csr_auth2 cur_type;
855 sql_stmt1 varchar2(1000);
856 sql_stmt2 varchar2(1000);
857 --
858 l_no_of_list_ident number(10) := 0;
859 l_rec_count number(10) := 0;
860 l_no_of_rules number(10) := 0;
861 --
862 l_routing_category_id pqh_routing_categories.routing_category_id%type;
863 l_list_name varchar2(200);
864 --
865 l_dummy varchar2(1);
866 --
867 l_proc varchar2(72) := g_package||'chk_valid_routing_exists';
868 --
869 Begin
870 --
871 hr_utility.set_location('Entering:'||l_proc, 5);
872 --
873 -- Select the number of list identifiers.
874 --
875 Open csr_list_ident_defined;
876 --
877 Fetch csr_list_ident_defined into l_no_of_list_ident;
878 --
879 Close csr_list_ident_defined;
880 --
881 --
882 -- The foll cursor selects the no of enabled routing categories exist for a
883 -- transaction category,and how many routing rules exists under each routing
884 -- category.
885 --
886 sql_stmt := 'Select rct.routing_category_id, count(rng.range_name)'
887 || ' from pqh_routing_categories rct,pqh_attribute_ranges rng'
888 || ' Where rct.transaction_category_id = :p_transaction_category_id'
889 || ' and rct.enable_flag = :p_enable_flag'
890 || ' and nvl(rct.default_flag,:dummy1) <> :yes_flag1 '
891 || ' and nvl(rct.delete_flag,:dummy2) <> :yes_flag2 ';
892 --
893 If p_routing_type = 'R' then
894 sql_stmt := sql_stmt || ' and rct.routing_list_id IS NOT NULL';
895 Elsif p_routing_type = 'P' then
896 sql_stmt := sql_stmt || ' and rct.position_structure_id IS NOT NULL';
897 Else
898 sql_stmt := sql_stmt || ' and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL';
899 End if;
900 --
901 sql_stmt := sql_stmt || ' and rct.routing_category_id = rng.routing_category_id(+)'
902 || ' and rng.enable_flag(+) = :p_rule_enable'
903 || ' and nvl(rng.delete_flag(+),:dummy3) <> :yes_flag3'
904 || ' and rng.routing_list_member_id(+) IS NULL'
905 || ' and rng.position_id(+) IS NULL'
906 || ' and rng.assignment_id(+) IS NULL'
907 || ' group by rct.routing_category_id'
908 || ' order by rct.routing_category_id';
909 --
910 -- Select the no of routing categories and no of rules under the routing
911 -- category.
912 --
913 --
914 Open csr_routing for sql_stmt using p_transaction_category_id,
915 'Y','N','Y','N','Y','Y','N','Y';
916 --
917 l_rec_count := 0;
918 --
919 Loop
920 --
921 Fetch csr_routing into l_routing_category_id,l_no_of_rules;
922 --
923 If csr_routing%notfound then
924 exit;
925 End if;
926 --
927 l_rec_count := l_rec_count + 1;
928 --
929 -- List identfiers exist , but no rules were defined for this routing
930 -- category. We need to make this check only if the routing type is
931 -- routing list though ..
932 --
933 -- If p_routing_type = 'R' and
934 If l_no_of_rules = 0 and l_no_of_list_ident > 0 then
935 --
936 Close csr_routing;
937 --
938 get_routing_category_name
939 (p_routing_category_id =>l_routing_category_id,
940 p_routing_category_name=> l_list_name);
941 --
942 hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
943 hr_utility.set_message_token('LIST_NAME', l_list_name);
944 hr_utility.raise_error;
945 --
946 End if;
947 --
948 End loop;
949 --
950 Close csr_routing;
951 --
952 -- The transaction category must have at least one routing category though
953 --
954 If l_rec_count = 0 then
955 hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
956 hr_utility.raise_error;
957 End if;
958 --
959 -- Position and Supervisory hierarchy must have at least one member rule
960 -- with approver flag set to 'Y'.
961 --
962 If p_routing_type = 'P' or p_routing_type = 'S' then
963 --
964 sql_stmt1 :='Select rct.routing_category_id'
965 ||' from pqh_routing_categories rct'
966 ||' Where rct.transaction_category_id=:p_transaction_category_id'
967 ||' and rct.enable_flag = :p_enable_flag'
968 ||' and nvl(rct.default_flag,:dummy1) <> :p_default_flag'
969 ||' and nvl(rct.delete_flag,:dummy2) <> :p_delete_flag';
970 --
971 sql_stmt2 := 'Select null from pqh_attribute_ranges'
972 ||' Where routing_category_id = :p_routing_category_id'
973 ||' and enable_flag = :p_enable_flag'
974 ||' and approver_flag = :p_approver_flag';
975 --
976 If p_routing_type = 'P' then
977 --
978 sql_stmt1 := sql_stmt1 || ' and rct.position_structure_id IS NOT NULL';
979 sql_stmt2 := sql_stmt2 || ' and position_id IS NOT NULL';
980 --
981 Elsif p_routing_type = 'S' then
982 --
983 sql_stmt1 := sql_stmt1 || ' and rct.routing_list_id IS NULL and rct.position_structure_id IS NULL';
984 sql_stmt2 := sql_stmt2 || ' and assignment_id IS NOT NULL';
985 --
986 End if;
987 --
988 --
989 Open csr_auth1 for sql_stmt1 using p_transaction_category_id,
990 'Y','N','Y','N','Y';
991 --
992 Loop
993 --
994 Fetch csr_auth1 into l_routing_category_id;
995 --
996 If csr_auth1%notfound then
997 exit;
998 End if;
999 --
1000 Open csr_auth2 for sql_stmt2 using l_routing_category_id,'Y','Y';
1001 --
1002 Fetch csr_auth2 into l_dummy;
1003 --
1004 If csr_auth2%notfound then
1005 --
1006 Close csr_auth2;
1007 --
1008 get_routing_category_name
1009 (p_routing_category_id =>l_routing_category_id,
1010 p_routing_category_name=> l_list_name);
1011 --
1012 hr_utility.set_message(8302,'PQH_NO_MEM_RULE_IN_ROUT_CAT');
1013 hr_utility.set_message_token('LIST_NAME', l_list_name);
1014 hr_utility.raise_error;
1015 --
1016 End if;
1017 --
1018 Close csr_auth2;
1019 --
1020 End loop;
1021 --
1022 Close csr_auth1;
1023 --
1024 --
1025 End if;
1026 --
1027 hr_utility.set_location('Leaving:'||l_proc, 10);
1028 --
1029 End;
1030 --
1031 --
1032 Procedure get_routing_category_name(p_routing_category_id in number,
1033 p_routing_category_name out nocopy varchar2) is
1034 --
1035 l_proc varchar2(72) := g_package||'get_routing_list_name';
1036 --
1037 -- The foll cursor returns the name of the routing category.
1038 --
1039 Cursor csr_routing_name(p_routing_category_id in number) is
1040 Select decode(RCT.routing_list_id,NULL,decode(RCT.position_structure_id,NULL,hr_general.decode_lookup('PQH_SUPERVISORY_HIERARCHY','SUPERVISORY_HIERARCHY'),PPS.name),RLT.routing_list_name) list_name
1041 From pqh_routing_categories RCT ,
1042 pqh_routing_lists RLT,
1043 per_position_structures PPS
1044 WHERE RCT.routing_category_id = p_routing_category_id AND
1045 RCT.routing_list_id = RLT.routing_list_id(+) and
1046 RCT.position_structure_id = PPS.position_structure_id(+);
1047 --
1048 Begin
1049 --
1050 hr_utility.set_location('Entering:'||l_proc, 5);
1051 --
1052 --
1053 Open csr_routing_name(p_routing_category_id => p_routing_category_id);
1054 Fetch csr_routing_name into p_routing_category_name;
1055 Close csr_routing_name;
1056 --
1057 hr_utility.set_location('Leaving:'||l_proc, 10);
1058 --
1059 End;
1060 --
1061 -- ----------------------------------------------------------------------------
1062 -- |------< chk_freeze_status_cd >------|
1063 -- ----------------------------------------------------------------------------
1064 --
1065 -- Description
1066 -- This procedure is used to check that the lookup value is valid.
1067 --
1068 -- Pre Conditions
1069 -- None.
1070 --
1071 -- In Parameters
1072 -- transaction_category_id PK of record being inserted or updated.
1073 -- freeze_status_cd Value of lookup code.
1074 -- effective_date effective date
1075 -- object_version_number Object version number of record being
1076 -- inserted or updated.
1077 --
1078 -- Post Success
1079 -- Processing continues
1080 --
1081 -- Post Failure
1082 -- Error handled by procedure
1083 --
1084 -- Access Status
1085 -- Internal table handler use only.
1086 --
1087 Procedure chk_freeze_status_cd
1088 (p_transaction_category_id in number,
1089 p_freeze_status_cd in varchar2,
1090 p_routing_type in varchar2,
1091 p_effective_date in date,
1092 p_object_version_number in number) is
1093 --
1094 l_proc varchar2(72) := g_package||'chk_freeze_status_cd';
1095 l_api_updating boolean;
1096 l_active_txn_exists_flag varchar2(1) := 'N';
1097 --
1098 Begin
1099 --
1100 hr_utility.set_location('Entering:'||l_proc, 5);
1101 --
1102 l_api_updating := pqh_tct_shd.api_updating
1103 (p_transaction_category_id => p_transaction_category_id,
1104 p_object_version_number => p_object_version_number);
1105 --
1106 if (l_api_updating
1107 and p_freeze_status_cd
1108 <> nvl(pqh_tct_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2)
1109 or not l_api_updating)
1110 and p_freeze_status_cd is not null then
1111 --
1112 -- check if value of lookup falls within lookup type.
1113 --
1114 if hr_api.not_exists_in_hr_lookups
1115 (p_lookup_type => 'PQH_CATEGORY_FREEZE_STATUS',
1116 p_lookup_code => p_freeze_status_cd,
1117 p_effective_date => p_effective_date) then
1118 --
1119 -- raise error as does not exist as lookup
1120 --
1121 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1122 hr_utility.raise_error;
1123 --
1124 -- Check if there exists at least one routing category under
1125 -- the transaction category and if each routing category has
1126 -- at least one rule before freezing the category.
1127 --
1128 If p_freeze_status_cd = 'FREEZE_CATEGORY' then
1129 --
1130 chk_valid_routing_exists
1131 (p_transaction_category_id => p_transaction_category_id,
1132 p_routing_type => p_routing_type);
1133 --
1134 pqh_attribute_ranges_pkg.chk_rout_overlap_on_freeze
1135 (p_transaction_category_id => p_transaction_category_id);
1136 --
1137 pqh_attribute_ranges_pkg.chk_mem_overlap_on_freeze
1138 (p_transaction_category_id => p_transaction_category_id);
1139 --
1140 End if;
1141 --
1142 end if;
1143 --
1144 end if;
1145 --
1146 -- ADDED VALIDATIONS
1147 --
1148 /*** This check is available in PQHWSTCT. Removing from api as they are not
1149 applicable for the wizard.
1150 --
1151 if (l_api_updating AND
1152 nvl(p_freeze_status_cd,hr_api.g_varchar2)
1153 <> nvl(pqh_tct_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2))
1154 or NOT l_api_updating then
1155 --
1156 --
1157 --
1158 -- Disallow updation of freeze_status_cd to NULL if active transactions
1159 -- exists for the transaction category only if the attributes were
1160 -- previously frozen.
1161 --
1162 if p_freeze_status_cd IS NULL then
1163 --
1164 If nvl(pqh_tct_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2) =
1165 'FREEZE_ATTRIBUTES' then
1166 --
1167 l_active_txn_exists_flag := chk_active_transaction_exists
1168 (p_transaction_category_id => p_transaction_category_id);
1169 --
1170 If l_active_txn_exists_flag = 'Y' then
1171 --
1172 hr_utility.set_message(8302, 'PQH_CATEGORY_ROUT_HIST_EXISTS');
1173 hr_utility.raise_error;
1174 --
1175 End if;
1176 End if;
1177 --
1178 Else
1179 --
1180 -- Check if there exists at least one routing category under
1181 -- the transaction category and if each routing category has
1182 -- at least one rule before freezing the category.
1183 --
1184 If p_freeze_status_cd = 'FREEZE_CATEGORY' then
1185 --
1186 chk_valid_routing_exists
1187 (p_transaction_category_id => p_transaction_category_id,
1188 p_routing_type => p_routing_type);
1189 --
1190 pqh_attribute_ranges_pkg.chk_rout_overlap_on_freeze
1191 (p_transaction_category_id => p_transaction_category_id);
1192 --
1193 pqh_attribute_ranges_pkg.chk_mem_overlap_on_freeze
1194 (p_transaction_category_id => p_transaction_category_id);
1195 --
1196 End if;
1197 --
1198 -- If Freezing the transaction category or its attributes , check if
1199 -- identifer counts are satisfied .
1200 --
1201 --
1202 chk_identifiers_count
1203 (p_transaction_category_id => p_transaction_category_id,
1204 p_routing_type => p_routing_type,
1205 p_min_member_identifiers => 1,
1206 p_max_list_identifiers => 3,
1207 p_max_member_identifiers => 5);
1208 --
1209 -- End if;
1210 --
1211 --
1212 End if;
1213 ***/
1214 --
1215 hr_utility.set_location('Leaving:'||l_proc,10);
1216 --
1217 end chk_freeze_status_cd;
1218 --
1219 --------------------------------------------------------------------------------
1220 -- NEW VALIDATIONS
1221 -------------------------------------------------------------------------------
1222 --
1223 -- ----------------------------------------------------------------------------
1224 -- |------< chk_upd_tct_allowed >------|
1225 -- ----------------------------------------------------------------------------
1226 --
1227 -- Description
1228 -- This procedure is used to checks if Transaction category details
1229 -- can be updated.
1230 --
1231 -- Pre Conditions
1232 -- None.
1233 --
1234 -- In Parameters
1235 -- transaction_category_id PK of record being inserted or updated.
1236 -- object_version_number Object version number of record being
1237 -- inserted or updated.
1238 --
1239 -- Post Success
1240 -- Processing continues
1241 --
1242 -- Post Failure
1243 -- Errors handled by the procedure
1244 --
1245 -- Access Status
1246 -- Internal table handler use only.
1247 --
1248 Procedure chk_upd_tct_allowed
1249 (p_transaction_category_id in number,
1250 p_freeze_status_cd in varchar2,
1251 p_object_version_number in number) is
1252 --
1253 l_proc varchar2(72) := g_package||'chk_upd_tct_allowed';
1254 l_api_updating boolean;
1255 --
1256
1257 Begin
1258 --
1259 hr_utility.set_location('Entering:'||l_proc, 5);
1260 --
1261 l_api_updating := pqh_tct_shd.api_updating
1262 (p_transaction_category_id => p_transaction_category_id,
1263 p_object_version_number => p_object_version_number);
1264 --
1265 -- Raise error if Trying to update details of a frozen transaction category ,
1266 -- If we are trying to unfreeze the category then allow updation
1267 --
1268 if l_api_updating AND
1269 (nvl(p_freeze_status_cd,hr_api.g_varchar2)
1270 = nvl(pqh_tct_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2)) then
1271 --
1272 -- Disallow changing details of frozen category
1273 --
1274 if pqh_tct_shd.g_old_rec.freeze_status_cd = 'FREEZE_CATEGORY' then
1275 --
1276 hr_utility.set_message(8302,'PQH_NO_UPD_FROZEN_TCT');
1277 hr_utility.raise_error;
1278 --
1279 End if;
1280 --
1281 End if;
1282 --
1283 --
1284 hr_utility.set_location('Leaving:'||l_proc, 10);
1285 --
1286 End chk_upd_tct_allowed;
1287 --
1288 --
1289 -------------------------------------------------------------------------------
1290 -- chk_identifiers_count
1291 -------------------------------------------------------------------------------
1292 --
1293 -- Description :
1294 -- This function ensures the following Business Rules
1295 -- 1) Transaction Category must have at least p_min_member identifier
1296 -- if the routing type is Position Hierarhcy / Supervisory Hierarchy
1297 -- 2) Transaction Category must have a max of p_max_list_identifiers
1298 -- 3) Transaction Category must have a max of p_max_Member_identifiers
1299 --
1300 --
1301 PROCEDURE chk_identifiers_count(p_transaction_category_id in number,
1302 p_routing_type in varchar2,
1303 p_min_member_identifiers in number,
1304 p_max_list_identifiers in number,
1305 p_max_member_identifiers in number) is
1306 --
1307 Cursor tot_list_identifiers is
1308 Select count(*) from pqh_txn_category_attributes tca
1309 where tca.transaction_category_id = p_transaction_category_id
1310 AND tca.list_identifying_flag = 'Y';
1311 --
1312 Cursor tot_member_identifiers is
1313 Select count(*) from pqh_txn_category_attributes tca
1314 where tca.transaction_category_id = p_transaction_category_id
1315 AND tca.member_identifying_flag = 'Y';
1316 --
1317 l_no_list_identifiers number(5);
1318 l_no_member_identifiers number(5);
1319 --
1320 l_proc varchar2(72) := g_package||'chk_identifiers_count';
1321 --
1322 Begin
1323 hr_utility.set_location('Entering:'||l_proc, 5);
1324 --
1325 -- Check if there a maximum of p_max_list_identifiers
1326 --
1327 Open tot_list_identifiers;
1328 Fetch tot_list_identifiers into l_no_list_identifiers;
1329 Close tot_list_identifiers;
1330 --
1331 If l_no_list_identifiers > p_max_list_identifiers then
1332 hr_utility.set_message(8302, 'PQH_MAX_LIST_IDENTIFIERS');
1333 hr_utility.raise_error;
1334 end if;
1335 --
1336 -- Check if there a maximum of p_member_list_identifiers
1337 -- There maybe no member identifiers at all.
1338 --
1339 Open tot_member_identifiers;
1340 Fetch tot_member_identifiers into l_no_member_identifiers;
1341 Close tot_member_identifiers;
1342 --
1343 if l_no_member_identifiers > p_max_member_identifiers then
1344 hr_utility.set_message(8302, 'PQH_MAX_MEMBER_IDENTIFIERS');
1345 hr_utility.raise_error;
1346 end if;
1347
1348 If ((p_routing_type = 'P' OR p_routing_type = 'S') AND
1349 l_no_member_identifiers < p_min_member_identifiers) then
1350 --
1351 hr_utility.set_message(8302, 'PQH_MIN_MEMBER_IDENTIFIERS');
1352 hr_utility.raise_error;
1353 --
1354 End if;
1355 --
1356 hr_utility.set_location('Leaving:'||l_proc, 10);
1357 --
1358 End;
1359 --
1360 --
1361 -- The following procedure checks if any active transaction for this
1362 -- transaction category exists.
1363 --
1364 FUNCTION chk_active_transaction_exists (p_short_name in VARCHAR2,
1365 p_transaction_category_id in number)
1366 RETURN VARCHAR2
1367 is
1368 --
1369 l_dummy varchar2(1);
1370 l_proc varchar2(72) := g_package||'chk_act_txn_exists';
1371 --
1372 Begin
1373 --
1374 hr_utility.set_location('Entering:'||l_proc, 5);
1375 --
1376 if p_short_name ='BUDGET_WORKSHEET' then
1377 select 'Y'
1378 into l_dummy
1379 from dual
1380 where exists ( select null
1381 from pqh_worksheets
1382 where wf_transaction_category_id = p_transaction_category_id
1383 and transaction_status not in ('REJECT','TERMINATE','APPLIED'));
1384 elsif p_short_name ='PQH_BPR' then
1385 select 'Y'
1386 into l_dummy
1387 from dual
1388 where exists ( select null from pqh_budget_pools
1389 where wf_transaction_category_id = p_transaction_category_id
1390 and approval_status in ('P'));
1391 elsif p_short_name ='POSITION_TRANSACTION' then
1392 select 'Y'
1393 into l_dummy
1394 from dual
1395 where exists ( select null from pqh_position_transactions
1396 where wf_transaction_category_id = p_transaction_category_id
1397 and transaction_status not in ('REJECT','TERMINATE','APPLIED'));
1398 end if;
1399 --
1400 hr_utility.set_location('Leaving with:'||l_dummy,20);
1401 hr_utility.set_location('Leaving:'||l_proc,30);
1402 --
1403 RETURN l_dummy;
1404 --
1405 exception
1406 when others then
1407 return 'N';
1408 End chk_active_transaction_exists;
1409 ----------------------------------------------------------------------------
1410 --
1411 -- The following procedure checks if any active transaction for this
1412 -- transaction category exists.
1413 --
1414 FUNCTION chk_active_transaction_exists (p_transaction_category_id in NUMBER)
1415 RETURN VARCHAR2
1416 is
1417 --
1418 --
1419 --
1420 l_dummy varchar2(1);
1421 l_proc varchar2(72) := g_package||'chk_active_transaction_exists';
1422 l_short_name pqh_transaction_categories.short_name%type;
1423 --
1424 Begin
1425 --
1426 hr_utility.set_location('Entering:'||l_proc, 5);
1427 --
1428 Select short_name
1429 into l_short_name
1430 From pqh_transaction_categories
1431 Where transaction_category_id = p_transaction_category_id;
1432 --
1433 if l_short_name is not null then
1434 l_dummy := chk_active_transaction_exists(p_short_name => l_short_name,
1435 p_transaction_category_id => p_transaction_category_id);
1436 end if;
1437 hr_utility.set_location('Entering:'||l_proc, 5);
1438 return l_dummy;
1439 End chk_active_transaction_exists;
1440 --
1441 --
1442 /**
1443 FUNCTION chk_active_transaction_exists (p_transaction_category_id in NUMBER)
1444 RETURN VARCHAR2
1445 is
1446 --
1447 type cur_type IS REF CURSOR;
1448 c2 cur_type;
1449 sql_stmt varchar2(1000);
1450 --
1451 l_from_clause pqh_table_route.from_clause%type;
1452 l_where_clause pqh_table_route.where_clause%type;
1453 l_table_alias pqh_table_route.table_alias%type;
1454 --
1455 l_new_where pqh_table_route.where_clause%type;
1456 --
1457 Cursor c1 is
1458 Select tr.from_clause,tr.table_alias,tr.where_clause
1459 From pqh_transaction_categories tct,pqh_table_route tr
1460 Where tct.transaction_category_id = p_transaction_category_id
1461 AND tct.consolidated_table_route_id = tr.table_route_id;
1462 --
1463 l_check_flag varchar2(1) := 'N';
1464 l_dummy varchar2(1);
1465 l_proc varchar2(72) := g_package||'chk_active_transaction_exists';
1466 --
1467 Begin
1468 --
1469 hr_utility.set_location('Entering:'||l_proc, 5);
1470 --
1471 Open c1;
1472 --
1473 -- Fetch the from clause for the transaction category id.
1474 --
1475 Fetch c1 into l_from_clause,l_table_alias,l_where_clause;
1476 --
1477 If c1%found then
1478 --
1479 l_check_flag := 'Y';
1480 --
1481 End if;
1482 --
1483 Close c1;
1484 --
1485 If l_check_flag = 'Y' then
1486 --
1487 -- Call replace_where_params to replace part of where clause
1488 --
1489 pqh_refresh_data.replace_where_params
1490 (p_where_clause_in => l_where_clause,
1491 p_txn_tab_flag => 'N',
1492 p_txn_id => '',
1493 p_where_clause_out => l_new_where);
1494 --
1495 -- Form dynamic SQL
1496 --
1497 --
1498 sql_stmt:= 'Select null From '
1499 ||l_from_clause
1500 ||' Where '
1501 ||l_new_where;
1502
1503 If l_new_where IS NOT NULL then
1504 --
1505 sql_stmt := sql_stmt || ' and';
1506 --
1507 End if;
1508
1509 sql_stmt := sql_stmt ||' nvl('
1510 ||l_table_alias
1511 ||'.transaction_status ,:b) not in ('
1512 ||''''||'REJECT'||''''
1513 ||','
1514 ||''''||'TERMINATE'||''''
1515 ||','
1516 ||''''||'APPLIED'||''''
1517 ||')' ;
1518 --
1519 Begin
1520 --
1521 Open c2 for sql_stmt using hr_api.g_varchar2;
1522 --
1523 Fetch c2 into l_dummy;
1524 --
1525 If c2%found then
1526 Close c2;
1527 RETURN 'Y';
1528 End if;
1529 --
1530 Close c2;
1531 --
1532 Exception when others then
1533 Close c2;
1534 Return 'N';
1535 --
1536 End;
1537 --
1538 End if;
1539 --
1540 hr_utility.set_location('Leaving:'||l_proc,10);
1541 --
1542 RETURN 'N';
1543 --
1544 End chk_active_transaction_exists;
1545 **/
1546 --
1547 --
1548 -- ----------------------------------------------------------------------------
1549 -- |---------------------------< insert_validate >----------------------------|
1550 -- ----------------------------------------------------------------------------
1551 Procedure insert_validate(p_rec in pqh_tct_shd.g_rec_type
1552 ,p_effective_date in date) is
1553 --
1554 l_proc varchar2(72) := g_package||'insert_validate';
1555 --
1556 Begin
1557 hr_utility.set_location('Entering:'||l_proc, 5);
1558 --
1559 -- Call all supporting business operations
1560 --
1561 chk_transaction_category_id
1562 (p_transaction_category_id => p_rec.transaction_category_id,
1563 p_object_version_number => p_rec.object_version_number);
1564 --
1565 chk_upd_tct_allowed
1566 (p_transaction_category_id => p_rec.transaction_category_id,
1567 p_freeze_status_cd => p_rec.freeze_status_cd,
1568 p_object_version_number => p_rec.object_version_number);
1569 --
1570 chk_consolid_table_route_id
1571 (p_transaction_category_id => p_rec.transaction_category_id,
1572 p_consolidated_table_route_id => p_rec.consolidated_table_route_id,
1573 p_object_version_number => p_rec.object_version_number);
1574 --
1575 chk_master_table_route_id
1576 (p_transaction_category_id => p_rec.transaction_category_id,
1577 p_master_table_route_id => p_rec.master_table_route_id,
1578 p_object_version_number => p_rec.object_version_number);
1579 --
1580 chk_route_validated_txn_flag
1581 (p_transaction_category_id => p_rec.transaction_category_id,
1582 p_route_validated_txn_flag => p_rec.route_validated_txn_flag,
1583 p_effective_date => p_effective_date,
1584 p_object_version_number => p_rec.object_version_number);
1585 --
1586 chk_workflow_enable_flag
1587 (p_transaction_category_id => p_rec.transaction_category_id,
1588 p_workflow_enable_flag => p_rec.workflow_enable_flag,
1589 p_effective_date => p_effective_date,
1590 p_object_version_number => p_rec.object_version_number);
1591 --
1592 chk_enable_flag
1593 (p_transaction_category_id => p_rec.transaction_category_id,
1594 p_enable_flag => p_rec.enable_flag,
1595 p_effective_date => p_effective_date,
1596 p_object_version_number => p_rec.object_version_number);
1597 --
1598 chk_post_style_cd
1599 (p_transaction_category_id => p_rec.transaction_category_id,
1600 p_post_style_cd => p_rec.post_style_cd,
1601 p_effective_date => p_effective_date,
1602 p_object_version_number => p_rec.object_version_number);
1603 --
1604 chk_member_cd
1605 (p_transaction_category_id => p_rec.transaction_category_id,
1606 p_member_cd => p_rec.member_cd,
1607 p_freeze_status_cd => p_rec.freeze_status_cd,
1608 p_effective_date => p_effective_date,
1609 p_object_version_number => p_rec.object_version_number);
1610 --
1611 chk_future_action_cd
1612 (p_transaction_category_id => p_rec.transaction_category_id,
1613 p_future_action_cd => p_rec.future_action_cd,
1614 p_effective_date => p_effective_date,
1615 p_object_version_number => p_rec.object_version_number);
1616 --
1617 chk_freeze_status_cd
1618 (p_transaction_category_id => p_rec.transaction_category_id,
1619 p_freeze_status_cd => p_rec.freeze_status_cd,
1620 p_routing_type => p_rec.member_cd,
1621 p_effective_date => p_effective_date,
1622 p_object_version_number => p_rec.object_version_number);
1623 --
1624 chk_setup_type_cd
1625 (p_transaction_category_id => p_rec.transaction_category_id,
1626 p_setup_type_cd => p_rec.setup_type_cd,
1627 p_effective_date => p_effective_date,
1628 p_object_version_number => p_rec.object_version_number);
1629 --
1630 chk_business_group_id
1631 (p_transaction_category_id => p_rec.transaction_category_id,
1632 p_business_group_id => p_rec.business_group_id,
1633 p_object_version_number => p_rec.object_version_number);
1634 --
1635 --
1636 --
1637 hr_utility.set_location(' Leaving:'||l_proc, 10);
1638 End insert_validate;
1639 --
1640 -- ----------------------------------------------------------------------------
1641 -- |---------------------------< update_validate >----------------------------|
1642 -- ----------------------------------------------------------------------------
1643 Procedure update_validate(p_rec in pqh_tct_shd.g_rec_type
1644 ,p_effective_date in date) is
1645 --
1646 l_proc varchar2(72) := g_package||'update_validate';
1647 --
1648 Begin
1649 hr_utility.set_location('Entering:'||l_proc, 5);
1650 --
1651 -- Call all supporting business operations
1652 --
1653 chk_transaction_category_id
1654 (p_transaction_category_id => p_rec.transaction_category_id,
1655 p_object_version_number => p_rec.object_version_number);
1656 --
1657 chk_upd_tct_allowed
1658 (p_transaction_category_id => p_rec.transaction_category_id,
1659 p_freeze_status_cd => p_rec.freeze_status_cd,
1660 p_object_version_number => p_rec.object_version_number);
1661 --
1662 chk_consolid_table_route_id
1663 (p_transaction_category_id => p_rec.transaction_category_id,
1664 p_consolidated_table_route_id => p_rec.consolidated_table_route_id,
1665 p_object_version_number => p_rec.object_version_number);
1666 --
1667 chk_master_table_route_id
1668 (p_transaction_category_id => p_rec.transaction_category_id,
1669 p_master_table_route_id => p_rec.master_table_route_id,
1670 p_object_version_number => p_rec.object_version_number);
1671 --
1672 chk_route_validated_txn_flag
1673 (p_transaction_category_id => p_rec.transaction_category_id,
1674 p_route_validated_txn_flag => p_rec.route_validated_txn_flag,
1675 p_effective_date => p_effective_date,
1676 p_object_version_number => p_rec.object_version_number);
1677 --
1678 chk_workflow_enable_flag
1679 (p_transaction_category_id => p_rec.transaction_category_id,
1680 p_workflow_enable_flag => p_rec.workflow_enable_flag,
1681 p_effective_date => p_effective_date,
1682 p_object_version_number => p_rec.object_version_number);
1683 --
1684 chk_enable_flag
1685 (p_transaction_category_id => p_rec.transaction_category_id,
1686 p_enable_flag => p_rec.enable_flag,
1687 p_effective_date => p_effective_date,
1688 p_object_version_number => p_rec.object_version_number);
1689 --
1690 chk_post_style_cd
1691 (p_transaction_category_id => p_rec.transaction_category_id,
1692 p_post_style_cd => p_rec.post_style_cd,
1693 p_effective_date => p_effective_date,
1694 p_object_version_number => p_rec.object_version_number);
1695 --
1696 chk_member_cd
1697 (p_transaction_category_id => p_rec.transaction_category_id,
1698 p_member_cd => p_rec.member_cd,
1699 p_freeze_status_cd => p_rec.freeze_status_cd,
1700 p_effective_date => p_effective_date,
1701 p_object_version_number => p_rec.object_version_number);
1702 --
1703 chk_future_action_cd
1704 (p_transaction_category_id => p_rec.transaction_category_id,
1705 p_future_action_cd => p_rec.future_action_cd,
1706 p_effective_date => p_effective_date,
1707 p_object_version_number => p_rec.object_version_number);
1708 --
1709 chk_freeze_status_cd
1710 (p_transaction_category_id => p_rec.transaction_category_id,
1711 p_freeze_status_cd => p_rec.freeze_status_cd,
1712 p_routing_type => p_rec.member_cd,
1713 p_effective_date => p_effective_date,
1714 p_object_version_number => p_rec.object_version_number);
1715 --
1716 chk_setup_type_cd
1717 (p_transaction_category_id => p_rec.transaction_category_id,
1718 p_setup_type_cd => p_rec.setup_type_cd,
1719 p_effective_date => p_effective_date,
1720 p_object_version_number => p_rec.object_version_number);
1721 --
1722 chk_business_group_id
1723 (p_transaction_category_id => p_rec.transaction_category_id,
1724 p_business_group_id => p_rec.business_group_id,
1725 p_object_version_number => p_rec.object_version_number);
1726 --
1727 --
1728 hr_utility.set_location(' Leaving:'||l_proc, 10);
1729 --
1730 End update_validate;
1731 --
1732 -- ----------------------------------------------------------------------------
1733 -- |---------------------------< delete_validate >----------------------------|
1734 -- ----------------------------------------------------------------------------
1735 Procedure delete_validate(p_rec in pqh_tct_shd.g_rec_type
1736 ,p_effective_date in date) is
1737 --
1738 l_proc varchar2(72) := g_package||'delete_validate';
1739 --
1740 Begin
1741 hr_utility.set_location('Entering:'||l_proc, 5);
1742 --
1743 -- Call all supporting business operations
1744 --
1745 hr_utility.set_location(' Leaving:'||l_proc, 10);
1746 End delete_validate;
1747 --
1748 end pqh_tct_bus;