[Home] [Help]
PACKAGE BODY: APPS.PQH_TEM_BUS
Source
1 Package Body pqh_tem_bus as
2 /* $Header: pqtemrhi.pkb 120.2.12000000.2 2007/04/19 12:48:53 brsinha noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tem_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_template_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 -- template_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_template_id(p_template_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_template_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_tem_shd.api_updating
47 (p_template_id => p_template_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_template_id,hr_api.g_number)
52 <> pqh_tem_shd.g_old_rec.template_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_tem_shd.constraint_error('PQH_TEMPLATES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_template_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_tem_shd.constraint_error('PQH_TEMPLATES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_template_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_tem_dml_allowed >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure allows any updates to the template record only if it
82 -- is unfrozen.
83 --
84 -- Pre Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- template_id PK of record being inserted or updated.
89 -- object_version_number Object version number of record being
90 -- inserted or updated.
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Errors handled by the procedure
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_tem_dml_allowed(p_template_id in number,
102 p_freeze_status_cd in varchar2,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_tem_dml_allowed';
106 l_api_updating boolean;
107 Begin
108 --
109 hr_utility.set_location('Entering:'||l_proc, 5);
110 --
111 l_api_updating := pqh_tem_shd.api_updating
112 (p_template_id => p_template_id,
113 p_object_version_number => p_object_version_number);
114 --
115 /** Updating but not the freeze status cd **/
116 --
117 if (l_api_updating
118 and nvl(p_freeze_status_cd,hr_api.g_varchar2)
119 = nvl(pqh_tem_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2)) then
120 --
121 if nvl(pqh_tem_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2)
122 = 'FREEZE_TEMPLATE' then
123 hr_utility.set_message(8302,'PQH_NO_UPD_FROZEN_TEM');
124 hr_utility.raise_error;
125 End if;
126 --
127 end if;
128 --
129 hr_utility.set_location('Leaving:'||l_proc, 10);
130 --
131 End chk_tem_dml_allowed;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |------< chk_tct_upd_allowed >------|
135 -- ----------------------------------------------------------------------------
136 --
137 -- Description
138 -- This procedure checks if the transaction category for the template
139 -- can be updated
140 --
141 -- Pre-Conditions
142 -- None.
143 --
144 -- In Parameters
145 -- p_template_id PK
146 -- p_transaction_category_id ID of FK column
147 -- p_object_version_number object version number
148 --
149 -- Post Success
150 -- Processing continues
151 --
152 -- Post Failure
153 -- Error raised.
154 --
155 -- Access Status
156 -- Internal table handler use only.
157 --
158 Procedure chk_tct_upd_allowed (p_template_id in number,
159 p_transaction_category_id in number,
160 p_object_version_number in number) is
161 --
162 l_proc varchar2(72) := g_package||'chk_tct_upd_allowed';
163 l_api_updating boolean;
164 l_dummy1 varchar2(1);
165 l_dummy2 varchar2(1);
166 --
167 cursor c1 is
168 select null
169 from pqh_template_attributes a
170 where a.template_id = p_template_id;
171 --
172 cursor c2 is
173 select null
174 from pqh_ref_templates a
175 where a.base_template_id = p_template_id;
176 --
177 Begin
178 --
179 hr_utility.set_location('Entering:'||l_proc,5);
180 --
181
182 l_api_updating := pqh_tem_shd.api_updating
183 (p_template_id => p_template_id,
184 p_object_version_number => p_object_version_number);
185 --
186 if (l_api_updating
187 and nvl(p_transaction_category_id,hr_api.g_number)
188 <> nvl(pqh_tem_shd.g_old_rec.transaction_category_id,hr_api.g_number)) then
189
190 open c1;
191 Fetch c1 into l_dummy1;
192 if c1%found then
193 close c1;
194 hr_utility.set_message(8302,'PQH_TEM_DETAILS_EXIST');
195 hr_utility.raise_error;
196 End if;
197 close c1;
198
199 open c2;
200 Fetch c2 into l_dummy2;
201 if c2%found then
202 close c2;
203 hr_utility.set_message(8302,'PQH_TEM_DETAILS_EXIST');
204 hr_utility.raise_error;
205 End if;
206 close c2;
207
208 End if;
209 --
210 hr_utility.set_location('Leaving:'||l_proc,10);
211 --
212 End chk_tct_upd_allowed;
213
214 -- ----------------------------------------------------------------------------
215 -- |------< chk_transaction_category_id >------|
216 -- ----------------------------------------------------------------------------
217 --
218 -- Description
219 -- This procedure checks that a referenced foreign key actually exists
220 -- in the referenced table.
221 --
222 -- Pre-Conditions
223 -- None.
224 --
225 -- In Parameters
226 -- p_template_id PK
227 -- p_transaction_category_id ID of FK column
228 -- p_object_version_number object version number
229 --
230 -- Post Success
231 -- Processing continues
232 --
233 -- Post Failure
234 -- Error raised.
235 --
236 -- Access Status
237 -- Internal table handler use only.
238 --
239 Procedure chk_transaction_category_id (p_template_id in number,
240 p_transaction_category_id in number,
241 p_object_version_number in number) is
242 --
243 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
244 l_api_updating boolean;
245 l_dummy varchar2(1);
246 l_freeze_status_cd pqh_transaction_categories.freeze_status_cd%type;
247 --
248 cursor c1 is
249 select nvl(freeze_status_cd,hr_api.g_varchar2)
250 from pqh_transaction_categories_vl a
251 where a.transaction_category_id = p_transaction_category_id;
252 --
253 Begin
254 --
255 hr_utility.set_location('Entering:'||l_proc,5);
256 --
257
258 l_api_updating := pqh_tem_shd.api_updating
259 (p_template_id => p_template_id,
260 p_object_version_number => p_object_version_number);
261 --
262 if (l_api_updating
263 and nvl(p_transaction_category_id,hr_api.g_number)
264 <> nvl(pqh_tem_shd.g_old_rec.transaction_category_id,hr_api.g_number)
265 or not l_api_updating) then
266 --
267 -- check if transaction_category_id value exists in
268 -- pqh_transaction_categories table
269 --
270 open c1;
271 --
272 fetch c1 into l_freeze_status_cd;
273 -- fetch c1 into l_dummy;
274 if c1%notfound then
275 --
276 close c1;
277 --
278 -- raise error as FK does not relate to PK in pqh_transaction_categories
279 -- table.
280 --
281 pqh_tem_shd.constraint_error('PQH_TEMPLATES_FK');
282 --
283 end if;
284 --
285 close c1;
286
287 --
288 --
289 end if;
290 --
291 hr_utility.set_location('Leaving:'||l_proc,10);
292 --
293 End chk_transaction_category_id;
294 --
295 -- ----------------------------------------------------------------------------
296 -- |------< chk_legislation_code >------|
297 -- ----------------------------------------------------------------------------
298 --
299 -- Description
300 -- This procedure checks that a referenced foreign key actually exists
301 -- in the referenced table.
302 --
303 -- Pre-Conditions
304 -- None.
305 --
306 -- In Parameters
307 -- p_template_id PK
308 -- p_legislation_code Legislation code of FK column
309 -- p_object_version_number object version number
310 --
311 -- Post Success
312 -- Processing continues
313 --
314 -- Post Failure
315 -- Error raised.
316 --
317 -- Access Status
318 -- Internal table handler use only.
319 --
320 Procedure chk_legislation_code (p_template_id in number,
321 p_legislation_code in varchar2,
322 p_object_version_number in number) is
323 --
324 l_proc varchar2(72) := g_package||'chk_legislation_code';
325 l_api_updating boolean;
326 l_dummy varchar2(1);
327 l_tat_leg_code varchar2(10);
328 --
329 cursor c1 is
330 select 'x'
331 from fnd_territories_vl a
332 where a.territory_code = p_legislation_code;
333 --
334 cursor c2(p_template_id number) is
335 select legislation_code
336 from pqh_template_attributes tat, pqh_attributes att
337 where tat.template_id = p_template_id
338 and tat.attribute_id= att.attribute_id
339 and att.legislation_code is not null;
340 --
341 Begin
342 --
343 hr_utility.set_location('Entering:'||l_proc,5);
344 --
345
346 l_api_updating := pqh_tem_shd.api_updating
347 (p_template_id => p_template_id,
348 p_object_version_number => p_object_version_number);
349 --
350 if (l_api_updating
351 and nvl(p_legislation_code,hr_api.g_varchar2)
352 <> nvl(pqh_tem_shd.g_old_rec.legislation_code,hr_api.g_varchar2)
353 or not l_api_updating) then
354 --
355 -- check if legislation_code value exists in
356 -- fnd_territories_vl table
357 --
358 if p_legislation_code is not null then
359 open c1;
360 --
361 fetch c1 into l_dummy;
362 if c1%notfound then
363 --
364 close c1;
365 --
366 -- raise error as FK does not relate to PK in fnd_territories_vl
367 -- table.
368 --
369 pqh_tem_shd.constraint_error('PQH_TEMPLATES_FK2');
370 --
371 end if;
372 --
373 close c1;
374 --
375 end if;
376 --
377 open c2(p_template_id);
378 fetch c2 into l_tat_leg_code;
379 if c2%found then
380 close c2;
381 if p_legislation_code is null then
382 hr_utility.set_message(8302,'PQH_CANT_CHG_TEM_LEG_NULL');
383 hr_utility.raise_error;
384 end if;
385 end if;
386 --
387 end if;
388 --
389 hr_utility.set_location('Leaving:'||l_proc,10);
390 --
391 End chk_legislation_code;
392 --
393 --
394 -- ----------------------------------------------------------------------------
395 -- |------< chk_template_name >------|
396 -- ----------------------------------------------------------------------------
397 --
398 -- Description
399 -- This procedure is used to check that the template name is unique
400 --
401 -- Pre Conditions
402 -- None.
403 --
404 -- In Parameters
405 -- template_id PK of record being inserted or updated.
406 -- object_version_number Object version number of record being
407 -- inserted or updated.
408 -- template_name
409 --
410 -- Post Success
411 -- Processing continues
412 --
413 -- Post Failure
414 -- Errors handled by the procedure
415 --
416 -- Access Status
417 -- Internal table handler use only.
418 --
419 Procedure chk_template_name(p_template_id in number,
420 p_template_name in varchar2,
424 l_proc varchar2(72) := g_package||'chk_template_name';
421 p_transaction_category_id in number,
422 p_object_version_number in number) is
423 --
425 l_api_updating boolean;
426 --
427 Begin
428 --
429 hr_utility.set_location('Entering:'||l_proc, 5);
430 --
431 l_api_updating := pqh_tem_shd.api_updating
432 (p_template_id => p_template_id,
433 p_object_version_number => p_object_version_number);
434 --
435 if (l_api_updating
436 and nvl(p_template_name,hr_api.g_varchar2)
437 <> pqh_tem_shd.g_old_rec.template_name)
438 or not l_api_updating then
439 --
440 chk_template_name_unique
441 (p_template_id => p_template_id,
442 p_template_name => p_template_name,
443 p_transaction_category_id => p_transaction_category_id);
444 --
445 End if;
446 --
447 hr_utility.set_location('Leaving:'||l_proc, 10);
448 --
449 End chk_template_name;
450 --
451 --
452 Procedure chk_template_name_unique
453 (p_template_id in number,
454 p_template_name in varchar2,
455 p_transaction_category_id in number) is
456 --
457 l_dummy varchar2(1) ;
458 --
459 Cursor csr_tem_name is
460 select null
461 from pqh_templates_vl
462 where template_name = p_template_name
463 and transaction_category_id = p_transaction_category_id
464 and template_id <> nvl(p_template_id,0);
465 --
466 l_proc varchar2(72) := g_package||'chk_template_name_unique';
467 l_api_updating boolean;
468 --
469 Begin
470 --
471 hr_utility.set_location('Entering:'||l_proc, 5);
472 --
473 Open csr_tem_name;
474 Fetch csr_tem_name into l_dummy;
475 --
476 If csr_tem_name%found then
477 Close csr_tem_name;
478 hr_utility.set_message(8302,'PQH_DUPLICATE_TEM_NAME');
479 hr_utility.raise_error;
480 End if;
481 --
482 Close csr_tem_name;
483 --
484 hr_utility.set_location('Leaving:'||l_proc, 10);
485 --
486 End chk_template_name_unique;
487 --
488
489 -- ----------------------------------------------------------------------------
490 -- |------< chk_under_review_flag >------|
491 -- ----------------------------------------------------------------------------
492 --
493 -- Description
494 -- This procedure is used to check that the lookup value is valid.
495 --
496 -- Pre Conditions
497 -- None.
498 --
499 -- In Parameters
500 -- template_id PK of record being inserted or updated.
501 -- under_review_flag Value of lookup code.
502 -- effective_date effective date
503 -- object_version_number Object version number of record being
504 -- inserted or updated.
505 --
506 -- Post Success
507 -- Processing continues
508 --
509 -- Post Failure
510 -- Error handled by procedure
511 --
512 -- Access Status
513 -- Internal table handler use only.
514 --
515 Procedure chk_under_review_flag(p_template_id in number,
516 p_under_review_flag in varchar2,
517 p_template_type_cd in varchar2,
518 p_create_flag in varchar2,
519 p_effective_date in date,
520 p_object_version_number in number) is
521 --
522 l_proc varchar2(72) := g_package||'chk_under_review_flag';
523 l_api_updating boolean;
524 --
525 Begin
526 --
527 hr_utility.set_location('Entering:'||l_proc, 5);
528 --
529 l_api_updating := pqh_tem_shd.api_updating
530 (p_template_id => p_template_id,
531 p_object_version_number => p_object_version_number);
532 --
533 if (l_api_updating
534 and p_under_review_flag
535 <> nvl(pqh_tem_shd.g_old_rec.under_review_flag,hr_api.g_varchar2)
536 or not l_api_updating)
537 and p_under_review_flag is not null then
538 --
539 -- check if value of lookup falls within lookup type.
540 --
541 if hr_api.not_exists_in_hr_lookups
542 (p_lookup_type => 'YES_NO',
543 p_lookup_code => p_under_review_flag,
544 p_effective_date => p_effective_date) then
545 --
546 -- raise error as does not exist as lookup
547 --
548 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
549 hr_utility.raise_error;
550 --
551 end if;
552 --
553 end if;
554 --
555 If p_under_review_flag = 'Y' then
556 --
557 if p_template_type_cd = 'DOMAIN' then
558 --
559 hr_utility.set_message(8302,'PQH_NO_REVIEW_FOR_DOMAIN_TEM');
560 hr_utility.raise_error;
561 --
562 End if;
563 --
564 If p_create_flag = 'Y' then
565 --
569 End if;
566 hr_utility.set_message(8302,'PQH_NO_REVIEW_FOR_CREATE_TEM');
567 hr_utility.raise_error;
568 --
570 --
571 End if;
572 --
573 --
574 hr_utility.set_location('Leaving:'||l_proc,10);
575 --
576 end chk_under_review_flag;
577 --
578 -- ----------------------------------------------------------------------------
579 -- |------< chk_if_template_applied >------|
580 -- ----------------------------------------------------------------------------
581 --
582 Function chk_if_template_applied(p_template_id in number)
583 RETURN varchar2 IS
584 --
585 l_proc varchar2(72) := g_package||'chk_if_template_applied';
586 l_dummy varchar2(1);
587 --
588 Cursor csr_tem_appl is
589 Select null
590 from pqh_transaction_templates
591 Where template_id = p_template_id;
592 --
593 Begin
594 --
595 hr_utility.set_location('Entering:'||l_proc,5);
596 --
597 Open csr_tem_appl;
598 Fetch csr_tem_appl into l_dummy;
599 --
600 If csr_tem_appl%notfound then
601 Close csr_tem_appl;
602 RETURN 'N';
603 End if;
604 --
605 Close csr_tem_appl;
606 --
607 RETURN 'Y';
608 --
609 hr_utility.set_location('Leaving:'||l_proc,10);
610 --
611 End;
612 --
613 -- ----------------------------------------------------------------------------
614 -- |------< chk_create_flag >------|
615 -- ----------------------------------------------------------------------------
616 --
617 -- Description
618 -- This procedure is used to check that the lookup value is valid.
619 --
620 -- Pre Conditions
621 -- None.
622 --
623 -- In Parameters
624 -- template_id PK of record being inserted or updated.
625 -- create_flag Value of lookup code.
626 -- effective_date effective date
627 -- object_version_number Object version number of record being
628 -- inserted or updated.
629 --
630 -- Post Success
631 -- Processing continues
632 --
633 -- Post Failure
634 -- Error handled by procedure
635 --
636 -- Access Status
637 -- Internal table handler use only.
638 --
639 Procedure chk_create_flag(p_template_id in number,
640 p_create_flag in varchar2,
641 p_template_type_cd in varchar2,
642 p_effective_date in date,
643 p_object_version_number in number) is
644 --
645 l_proc varchar2(72) := g_package||'chk_create_flag';
646 l_api_updating boolean;
647 l_applied_flag varchar2(10);
648 --
649 Begin
650 --
651 hr_utility.set_location('Entering:'||l_proc, 5);
652 --
653 l_api_updating := pqh_tem_shd.api_updating
654 (p_template_id => p_template_id,
655 p_object_version_number => p_object_version_number);
656 --
657 if (l_api_updating
658 and p_create_flag
659 <> nvl(pqh_tem_shd.g_old_rec.create_flag,hr_api.g_varchar2)
660 or not l_api_updating) and
661 p_create_flag is not null then
662 --
663 -- check if value of lookup falls within lookup type.
664 --
665 if hr_api.not_exists_in_hr_lookups
666 (p_lookup_type => 'YES_NO',
667 p_lookup_code => p_create_flag,
668 p_effective_date => p_effective_date) then
669 --
670 -- raise error as does not exist as lookup
671 --
672 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
673 hr_utility.raise_error;
674 --
675 end if;
676 --
677 End if; /* If update or insert */
678 --
679 -- Check if create flag can be updated.Should not allow update if the
680 -- template was used in any transaction.
681 --
682 if l_api_updating AND
683 nvl(p_create_flag,hr_api.g_varchar2)
684 <> nvl(pqh_tem_shd.g_old_rec.create_flag,hr_api.g_varchar2) then
685 --
686 l_applied_flag := chk_if_template_applied(p_template_id => p_template_id);
687 --
688 If l_applied_flag = 'Y' then
689 --
690 hr_utility.set_message(8302,'PQH_NO_UPD_TEM_TASK_TYPE');
691 hr_utility.raise_error;
692 --
693 End if;
694 --
695 End if;
696 --
697 --
698 If p_create_flag is not null then
699 --
700 -- Raise error if create flag has a value for a domain template
701 --
702 If p_template_type_cd = 'DOMAIN' then
703 --
704 hr_utility.set_message(8302,'PQH_NO_TASK_FOR_DOMAIN_TEM');
705 hr_utility.raise_error;
706 --
707 End if;
708 --
709 Else
710 --
711 -- Raise error if create flag has no value for a task template
712 --
713 if p_template_type_cd = 'TASK' then
714 --
715 hr_utility.set_message(8302,'PQH_INVALID_TEMPLATE_TASK');
716 hr_utility.raise_error;
717 --
718 End if;
719 --
720 End if; /*If p_create_flag is not null*/
721 --
722 --
726 --
723 hr_utility.set_location('Leaving:'||l_proc,10);
724 --
725 end chk_create_flag;
727 -- ----------------------------------------------------------------------------
728 -- |------< chk_enable_flag >------|
729 -- ----------------------------------------------------------------------------
730 --
731 -- Description
732 -- This procedure is used to check that the lookup value is valid.
733 --
734 -- Pre Conditions
735 -- None.
736 --
737 -- In Parameters
738 -- template_id PK of record being inserted or updated.
739 -- enable_flag Value of lookup code.
740 -- effective_date effective date
741 -- object_version_number Object version number of record being
742 -- inserted or updated.
743 --
744 -- Post Success
745 -- Processing continues
746 --
747 -- Post Failure
748 -- Error handled by procedure
749 --
750 -- Access Status
751 -- Internal table handler use only.
752 --
753 Procedure chk_enable_flag(p_template_id in number,
754 p_enable_flag in varchar2,
755 p_effective_date in date,
756 p_object_version_number in number) is
757 --
758 l_proc varchar2(72) := g_package||'chk_enable_flag';
759 l_api_updating boolean;
760 --
761 Begin
762 --
763 hr_utility.set_location('Entering:'||l_proc, 5);
764 --
765 l_api_updating := pqh_tem_shd.api_updating
766 (p_template_id => p_template_id,
767 p_object_version_number => p_object_version_number);
768 --
769 if (l_api_updating
770 and p_enable_flag
771 <> nvl(pqh_tem_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
772 or not l_api_updating)
773 and p_enable_flag is not null then
774 --
775 -- check if value of lookup falls within lookup type.
776 --
777 if hr_api.not_exists_in_hr_lookups
778 (p_lookup_type => 'YES_NO',
779 p_lookup_code => p_enable_flag,
780 p_effective_date => p_effective_date) then
781 --
782 -- raise error as does not exist as lookup
783 --
784 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
785 hr_utility.raise_error;
786 --
787 end if;
788 --
789 If p_enable_flag = 'N' then
790 --
791 disable_role_templates(p_template_id => p_template_id);
792 --
793 End if;
794 --
795 End if;
796 --
797 hr_utility.set_location('Leaving:'||l_proc,10);
798 --
799 end chk_enable_flag;
800 --
801 --
802 Procedure disable_role_templates(p_template_id in number) is
803 --
804 l_proc varchar2(72) := g_package||'disable_role_templates';
805 --
806 Cursor csr_role_templates is
807 Select role_template_id,object_version_number
808 From pqh_role_templates
809 Where template_id = p_template_id;
810 --
811 l_object_version_number pqh_role_templates.object_version_number%type;
812 --
813 Begin
814 --
815 hr_utility.set_location('Entering:'||l_proc, 5);
816 --
817 For rec in csr_role_templates loop
818 --
819 l_object_version_number := rec.object_version_number;
820 --
821 pqh_role_templates_api.update_role_template(
822 p_validate => false
823 ,p_role_template_id => rec.role_template_id
824 ,p_enable_flag => 'N'
825 ,p_object_version_number => l_object_version_number
826 ,p_effective_date => sysdate );
827 --
828 End loop;
829 --
830 hr_utility.set_location('Leaving:'||l_proc,10);
831 --
832 End disable_role_templates;
833 --
834 --
835 -- ----------------------------------------------------------------------------
836 -- |------< chk_attribute_only_flag >------|
837 -- ----------------------------------------------------------------------------
838 --
839 -- Description
840 -- This procedure is used to check that the lookup value is valid.
841 --
842 -- Pre Conditions
843 -- None.
844 --
845 -- In Parameters
846 -- template_id PK of record being inserted or updated.
847 -- attribute_only_flag Value of lookup code.
848 -- effective_date effective date
849 -- object_version_number Object version number of record being
850 -- inserted or updated.
851 --
852 -- Post Success
853 -- Processing continues
854 --
855 -- Post Failure
856 -- Error handled by procedure
857 --
858 -- Access Status
859 -- Internal table handler use only.
860 --
861 Procedure chk_attribute_only_flag(p_template_id in number,
862 p_attribute_only_flag in varchar2,
863 p_effective_date in date,
864 p_object_version_number in number) is
865 --
866 l_proc varchar2(72) := g_package||'chk_attribute_only_flag';
867 l_api_updating boolean;
868 l_dummy varchar2(1);
869 --
870 Cursor c1 is
871 select null
872 from pqh_ref_templates a
876 --
873 where a.parent_template_id = p_template_id
874 and a.reference_type_cd = 'REFERENCE';
875 Begin
877 hr_utility.set_location('Entering:'||l_proc, 5);
878 --
879 l_api_updating := pqh_tem_shd.api_updating
880 (p_template_id => p_template_id,
881 p_object_version_number => p_object_version_number);
882 --
883 if (l_api_updating
884 and p_attribute_only_flag
885 <> nvl(pqh_tem_shd.g_old_rec.attribute_only_flag,hr_api.g_varchar2)
886 or not l_api_updating)
887 and p_attribute_only_flag is not null then
888 --
889 -- check if value of lookup falls within lookup type.
890 --
891 if hr_api.not_exists_in_hr_lookups
892 (p_lookup_type => 'YES_NO',
893 p_lookup_code => p_attribute_only_flag,
894 p_effective_date => p_effective_date) then
895 --
896 -- raise error as does not exist as lookup
897 --
898 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
899 hr_utility.raise_error;
900 --
901 end if;
902 --
903 end if;
904 --
905 -- Raise error if attribute_only_flag is updated to Y and reference templates
906 -- exist for the current template.
907 --
908 if (l_api_updating
909 and nvl(p_attribute_only_flag,hr_api.g_varchar2)
910 <> nvl(pqh_tem_shd.g_old_rec.attribute_only_flag,hr_api.g_varchar2)) then
911 --
912 if nvl(p_attribute_only_flag,hr_api.g_varchar2) = 'Y' then
913 --
914 -- Check if the attribute_only_flag is being updated to 'Y'
915 open c1;
916 fetch c1 into l_dummy;
917 if c1%found then
918 Close c1;
919 hr_utility.set_message(8302,'PQH_REF_TEMPLATES_EXIST');
920 hr_utility.raise_error;
921 End if;
922 close c1;
923 -- Raise error if reference records exist in pqh_ref_templates
924 --
925 --
926 End if;
927 --
928 End if;
929 --
930 hr_utility.set_location('Leaving:'||l_proc,10);
931 --
932 end chk_attribute_only_flag;
933 --
934 -- ----------------------------------------------------------------------------
935 -- |------< chk_freeze_status_cd >------|
936 -- ----------------------------------------------------------------------------
937 --
938 -- Description
939 -- This procedure is used to check that the lookup value is valid.
940 --
941 -- Pre Conditions
942 -- None.
943 --
944 -- In Parameters
945 -- template_id PK of record being inserted or updated.
946 -- freeze_status_cd Value of lookup code.
947 -- effective_date effective date
948 -- object_version_number Object version number of record being
949 -- inserted or updated.
950 --
951 -- Post Success
952 -- Processing continues
953 --
954 -- Post Failure
955 -- Error handled by procedure
956 --
957 -- Access Status
958 -- Internal table handler use only.
959 --
960 Procedure chk_freeze_status_cd(p_template_id in number,
961 p_freeze_status_cd in varchar2,
962 p_effective_date in date,
963 p_object_version_number in number) is
964 --
965 l_proc varchar2(72) := g_package||'chk_freeze_status_cd';
966 l_api_updating boolean;
967 --
968 Begin
969 --
970 hr_utility.set_location('Entering:'||l_proc, 5);
971 --
972 l_api_updating := pqh_tem_shd.api_updating
973 (p_template_id => p_template_id,
974 p_object_version_number => p_object_version_number);
975 --
976 if (l_api_updating
977 and p_freeze_status_cd
978 <> nvl(pqh_tem_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2)
979 or not l_api_updating)
980 and p_freeze_status_cd is not null then
981 --
982 -- check if value of lookup falls within lookup type.
983 --
984 if hr_api.not_exists_in_hr_lookups
985 (p_lookup_type => 'PQH_TEMPLATE_FREEZE_STATUS',
986 p_lookup_code => p_freeze_status_cd,
987 p_effective_date => p_effective_date) then
988 --
989 -- raise error as does not exist as lookup
990 --
991 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
992 hr_utility.raise_error;
993 --
994 end if;
995 --
996 end if;
997 --
998 hr_utility.set_location('Leaving:'||l_proc,10);
999 --
1000 end chk_freeze_status_cd;
1001 --
1002 -- ADDITIONAL CHKS
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |------< chk_template_type_cd >------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 -- Description
1009 -- This procedure is used to check that the lookup value is valid.
1010 --
1011 -- Pre Conditions
1012 -- None.
1013 --
1014 -- In Parameters
1015 -- template_id PK of record being inserted or updated.
1016 -- template_type__cd Value of lookup code.
1017 -- effective_date effective date
1021 -- Post Success
1018 -- object_version_number Object version number of record being
1019 -- inserted or updated.
1020 --
1022 -- Processing continues
1023 --
1024 -- Post Failure
1025 -- Error handled by procedure
1026 --
1027 -- Access Status
1028 -- Internal table handler use only.
1029 --
1030 Procedure chk_template_type_cd(p_template_id in number,
1031 p_template_type_cd in varchar2,
1032 p_effective_date in date,
1033 p_object_version_number in number) is
1034 --
1035 l_proc varchar2(72) := g_package||'chk_template_type_cd';
1036 l_api_updating boolean;
1037 l_applied_flag varchar2(10);
1038 --
1039 Begin
1040 --
1041 hr_utility.set_location('Entering:'||l_proc, 5);
1042 --
1043 l_api_updating := pqh_tem_shd.api_updating
1044 (p_template_id => p_template_id,
1045 p_object_version_number => p_object_version_number);
1046 --
1047 if (l_api_updating
1048 and p_template_type_cd
1049 <> nvl(pqh_tem_shd.g_old_rec.template_type_cd,hr_api.g_varchar2)
1050 or not l_api_updating)
1051 and p_template_type_cd is not null then
1052 --
1053 -- check if value of lookup falls within lookup type.
1054 --
1055 if hr_api.not_exists_in_hr_lookups
1056 (p_lookup_type => 'PQH_TEMPLATE_TYPE',
1057 p_lookup_code => p_template_type_cd,
1058 p_effective_date => p_effective_date) then
1059 --
1060 -- raise error as does not exist as lookup
1061 --
1062 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1063 hr_utility.raise_error;
1064 --
1065 end if;
1066 --
1067 end if;
1068 --
1069 --
1070 -- Check if template type can be updated.Should not allow update if the
1071 -- template was used in any transaction.
1072 --
1073 if l_api_updating AND
1074 nvl(p_template_type_cd,hr_api.g_varchar2)
1075 <> nvl(pqh_tem_shd.g_old_rec.template_type_cd,hr_api.g_varchar2) then
1076 --
1077 l_applied_flag := chk_if_template_applied(p_template_id => p_template_id);
1078 --
1079 If l_applied_flag = 'Y' then
1080 --
1081 hr_utility.set_message(8302,'PQH_NO_UPD_TEMPLATE_TYPE');
1082 hr_utility.raise_error;
1083 --
1084 End if;
1085 --
1086 End if;
1087 --
1088 --
1089 hr_utility.set_location('Leaving:'||l_proc,10);
1090 --
1091 end chk_template_type_cd;
1092 --
1093 --
1094 -- ----------------------------------------------------------------------------
1095 -- |------< chk_master_child_attributes >------|
1096 -- ----------------------------------------------------------------------------
1097 --
1098 -- Description
1099 -- This procedure is used to check if master and all its child
1100 -- attributes have been associated with the template before freezing
1101 -- the template.
1102 --
1103 -- Pre Conditions
1104 -- None.
1105 --
1106 -- In Parameters
1107 -- template_id PK of record being inserted or updated.
1108 -- transaction_category FK column
1109 -- object_version_number Object version number of record being
1110 -- inserted or updated.
1111 --
1112 -- Post Success
1113 -- Processing continues
1114 --
1115 -- Post Failure
1116 -- Errors handled by the procedure
1117 --
1118 -- Access Status
1119 -- Internal table handler use and Called within form.
1120 --
1121 Procedure chk_master_child_attributes(p_transaction_category_id IN number,
1122 p_template_id IN number) is
1123 --
1124 type master_child is record
1125 (master_attribute_id pqh_attributes.master_attribute_id%type,
1126 attribute_id pqh_attributes.attribute_id%type,
1127 attribute_name pqh_attributes.attribute_name%type,
1128 tat_attribute_id pqh_template_attributes.attribute_id%type,
1129 view_flag pqh_template_attributes.view_flag%type,
1130 edit_flag pqh_template_attributes.edit_flag%type);
1131 --
1132 attr_rec master_child;
1133 --
1134 Cursor c1 is
1135 Select nvl(att.master_attribute_id,att.attribute_id),
1136 tca.attribute_id,rtrim(att.attribute_name),nvl(tat.attribute_id,-1),
1137 nvl(tat.view_flag,'N') , nvl(tat.edit_flag,'N')
1138 From pqh_txn_category_attributes tca,pqh_attributes_vl att,
1139 pqh_template_attributes tat
1140 Where tca.transaction_category_id = p_transaction_category_id
1141 AND tca.attribute_id = att.attribute_id
1142 AND att.attribute_id = tat.attribute_id(+)
1143 AND tat.template_id(+) = p_template_id
1144 Order by 1,2;
1145 --
1146 Cursor csr_master_attr(p_attribute_id in number) is
1147 Select rtrim(attribute_name)
1148 from pqh_attributes_vl
1149 Where attribute_id = p_attribute_id;
1150 --
1151 --
1152 -- Declaring local variables.
1153 --
1154 child_attr_list varchar2(2000) := NULL;
1155 view_edit_list varchar2(2000) := NULL;
1156 --
1157 view_flag_mismatch varchar2(1) := 'N';
1161 master_attached varchar2(1) := 'Y';
1158 edit_flag_mismatch varchar2(1) := 'N';
1159 match number(5) := 0;
1160 no_match number(5) := 0;
1162 --
1163 master_id pqh_attributes.attribute_id%type := -1;
1164 l_master_attribute_name pqh_attributes.attribute_name%type := NULL;
1165 master_view_flag pqh_template_attributes.view_flag%type := 'N';
1166 master_edit_flag pqh_template_attributes.edit_flag%type := 'N';
1167 --
1168 check_reqd_flag varchar2(1) := 'N';
1169 --
1170 l_proc varchar2(72) := g_package||'chk_master_child_attributes';
1171 --
1172 Begin
1173 --
1174 hr_utility.set_location('Entering:'||l_proc, 5);
1175 --
1176 Open c1;
1177 loop
1178 --
1179 Fetch c1 into attr_rec;
1180 --
1181 Exit when c1%notfound;
1182 --
1183 --
1184 -- Check if there has been a change in master_attribute_id
1185 --
1186 if master_id <> attr_rec.master_attribute_id then
1187 --
1188 -- If there has been a change in master attribute id , perform
1189 -- all the checks for the set of attributes under the previous
1190 -- master attribute.
1191 --
1192 If check_reqd_flag = 'Y' then
1193 --
1194 -- Few attributes are attached to the template and the rest are not
1195 --
1196 Open csr_master_attr(p_attribute_id => master_id);
1197 Fetch csr_master_attr into l_master_attribute_name;
1198 Close csr_master_attr;
1199 --
1200 --
1201 if match > 0 and no_match > 0 then
1202 --
1203 If master_attached = 'N' then
1204 --
1205 hr_utility.set_message(8302,'PQH_ATTACH_MASTER_ATTRIBUTE');
1206 hr_utility.set_message_token('ATTRIBUTE_NAME',l_master_attribute_name);
1207 hr_utility.raise_error;
1208 --
1209 Else
1210 --
1211 hr_utility.set_message(8302,'PQH_MISMATCH_MASTER_CHILD_ATTR');
1212 hr_utility.set_message_token('CHILD',substr(child_attr_list,1,lengthb(child_attr_list)-1));
1213 hr_utility.set_message_token('MASTER',l_master_attribute_name);
1214 hr_utility.raise_error;
1215 --
1216 End if; /* If master_attached = 'N'*/
1217 --
1218 End if; /* if match > 0 and no_match > 0 */
1219 --
1220 If view_flag_mismatch = 'Y' then
1221 --
1222 hr_utility.set_message(8302,'PQH_ATTR_VIEW_FLAG_MISMATCH');
1223 hr_utility.set_message_token('CHILD',substr(view_edit_list,1,lengthb(view_edit_list)-1));
1224 hr_utility.raise_error;
1225 --
1226 end if;
1227 --
1228 If edit_flag_mismatch = 'Y' then
1229 --
1230 hr_utility.set_message(8302,'PQH_ATTR_EDIT_FLAG_MISMATCH');
1231 hr_utility.set_message_token('CHILD',substr(view_edit_list,1,lengthb(view_edit_list)-1));
1232 hr_utility.raise_error;
1233 --
1234 end if; /* edit_flag_mismatch = 'Y' */
1235 --
1236 End if; /* check_reqd_flag = 'Y'*/
1237 --
1238 -- Reset defaults.
1239 --
1240 check_reqd_flag := 'N';
1241 match := 0;
1242 no_match := 0;
1243 view_flag_mismatch := 'N';
1244 edit_flag_mismatch := 'N';
1245 master_attached := 'Y';
1246 child_attr_list := NULL;
1247 --
1248 view_edit_list := attr_rec.attribute_name||',';
1249 --
1250 -- The current record has the master for the next set of attributes
1251 -- and all validations should be made against the master values
1252 --
1253 master_id := attr_rec.master_attribute_id;
1254 hr_utility.set_location('Master is '||to_char(master_id),101);
1255 master_view_flag := attr_rec.view_flag;
1256 master_edit_flag := attr_rec.edit_flag;
1257 --
1258 Else
1259 --
1260 --
1261 If (nvl(lengthb(attr_rec.attribute_name),0) + nvl(lengthb(view_edit_list),0))
1262 <= 500 then
1263 --
1264 view_edit_list := view_edit_list||attr_rec.attribute_name||',';
1265 --
1266 End if;
1267 --
1268 --
1269 hr_utility.set_location('Child is '||to_char(attr_rec.attribute_id),102);
1270 if master_view_flag <> attr_rec.view_flag then
1271 --
1272 view_flag_mismatch := 'Y';
1273 --
1274 End if; /* if master_view_flag <> attr_rec.view_flag */
1275 --
1276 --
1277 --
1278 if master_edit_flag <> attr_rec.edit_flag then
1279 --
1280 edit_flag_mismatch := 'Y';
1281 --
1282 --
1283 End if; /* master_edit_flag <> attr_rec.edit_flag */
1284 --
1288 -- Check if the attribute has been added to the template
1285 End if; /* if master_id <> attr_rec.master_attribute_id */
1286 --
1287 --
1289 --
1290 if attr_rec.attribute_id = attr_rec.tat_attribute_id then
1291 --
1292 hr_utility.set_location('Added Child is '||to_char(attr_rec.tat_attribute_id),103);
1293 -- attribute has been added to the template
1294 match := match + 1;
1295 --
1296 else
1297 --
1298 If attr_rec.attribute_id = attr_rec.master_attribute_id then
1299 --
1300 hr_utility.set_location('Not Added Master is '||to_char(attr_rec.master_attribute_id),104);
1301 -- master attribute has NOT been added to the template
1302 --
1303 master_attached := 'N';
1304 no_match := no_match + 1;
1305 --
1306 Else
1307 --
1308 -- child attribute has NOT been added to the template
1309 -- Save the attribute id that has not been attached.
1310 --
1311 If (nvl(lengthb(attr_rec.attribute_name),0) + nvl(lengthb(child_attr_list),0))
1312 <= 500 then
1313 --
1314 child_attr_list:=child_attr_list||attr_rec.attribute_name||',';
1315 --
1316 hr_utility.set_location('Not Added Child is '||attr_rec.attribute_name,105);
1317 End if;
1318 --
1319 no_match := no_match + 1;
1320 --
1321 End if;
1322
1323 end if; /* attr_rec.attribute_id = attr_rec.tat_attribute_id */
1324 --
1325 -- The master attribute id may be from a different transaction category.
1326 -- Ideally this should not be the case . But if such a case occurs then
1327 -- we should not perform the master child attributes check.
1328 --
1329 if attr_rec.attribute_id = attr_rec.master_attribute_id then
1330 --
1331 check_reqd_flag := 'Y' ;
1332 --
1333 End if; /* if attr_rec.attribute_id = attr_rec.master_attribute_id */
1334 --
1335 End loop;
1336 --
1337 -- Repeating validations for the last record
1338 --
1339 If check_reqd_flag = 'Y' then
1340 --
1341 -- Few attributes are attached to the template and the rest are not
1342 --
1343 Open csr_master_attr(p_attribute_id => master_id);
1344 Fetch csr_master_attr into l_master_attribute_name;
1345 Close csr_master_attr;
1346 --
1347 if match > 0 and no_match > 0 then
1348 --
1349 If master_attached = 'N' then
1350 --
1351 hr_utility.set_message(8302,'PQH_ATTACH_MASTER_ATTRIBUTE');
1352 hr_utility.set_message_token('ATTRIBUTE_NAME',l_master_attribute_name);
1353 hr_utility.raise_error;
1354 --
1355 Else
1356 --
1357 hr_utility.set_message(8302,'PQH_MISMATCH_MASTER_CHILD_ATTR');
1358 hr_utility.set_message_token('CHILD',substr(child_attr_list,1,lengthb(child_attr_list)-1));
1359 hr_utility.set_message_token('MASTER',l_master_attribute_name);
1360 hr_utility.raise_error;
1361 --
1362 End if; /* If master_attached = 'N */
1363 --
1364 End if; /* if match > 0 and no_match > 0 */
1365 --
1366 If view_flag_mismatch = 'Y' then
1367 --
1368 hr_utility.set_message(8302,'PQH_ATTR_VIEW_FLAG_MISMATCH');
1369 hr_utility.set_message_token('CHILD',substr(view_edit_list,1,lengthb(view_edit_list)-1));
1370 hr_utility.raise_error;
1371 --
1372 End if;
1373 --
1374 If edit_flag_mismatch = 'Y' then
1375 --
1376 hr_utility.set_message(8302,'PQH_ATTR_EDIT_FLAG_MISMATCH');
1377 hr_utility.set_message_token('CHILD',substr(view_edit_list,1,lengthb(view_edit_list)-1));
1378 hr_utility.raise_error;
1379 --
1380 End if;
1381 --
1382 End if;
1383 --
1384 --
1385 hr_utility.set_location('Leaving:'||l_proc, 10);
1386 --
1387 End;
1388 --
1389 --
1390 -- ----------------------------------------------------------------------------
1391 -- |------< chk_rout_hist_exist >------|
1392 -- ----------------------------------------------------------------------------
1393 --
1394 -- Description
1395 -- This procedure is used to check if Routing history exists for the template
1396 -- This procedure in turn calls another procedure which would return an
1397 -- error code to signify if any routing history exists or not.
1398 --
1399 -- Pre Conditions
1400 -- None.
1401 --
1402 -- In Parameters
1403 -- template_id PK of record being inserted or updated.
1404 --
1405 -- Post Success
1406 -- Processing continues
1407 --
1408 -- Post Failure
1409 -- Errors handled by the procedure
1410 --
1411 -- Access Status
1412 -- Internal table handler use and Called within form.
1413 --
1417 Cursor c1(p_template_id in number) is
1414 Procedure chk_rout_hist_exist( p_template_id IN number) is
1415 --
1416 --
1418 Select null
1419 from pqh_transaction_templates tt
1420 Where tt.template_id = p_template_id
1421 AND tt.transaction_id in (Select ptx.position_transaction_id
1422 From pqh_position_transactions ptx
1423 Where nvl(ptx.transaction_status,'PENDING') in
1424 ('APPROVED','SUBMITTED','PENDING'));
1425 --
1426 l_dummy varchar2(1);
1427 l_proc varchar2(72) := g_package||'chk_rout_hist_exist';
1428 --
1429 Begin
1430 --
1431 hr_utility.set_location('Entering:'||l_proc, 5);
1432 --
1433 Open c1(p_template_id => p_template_id);
1434 --
1435 Fetch c1 into l_dummy;
1436 --
1437 If c1%found then
1438 --
1439 Close c1;
1440 hr_utility.set_message(8302,'PQH_TEMPLATE_ROUT_HIST_EXISTS');
1441 hr_utility.raise_error;
1442 --
1443 End if;
1444 --
1445 Close c1;
1446 --
1447 hr_utility.set_location('Leaving:'||l_proc,10);
1448 --
1449 End chk_rout_hist_exist;
1450 --
1451 --
1452 ---------------------------------------------------------------------------
1453 --
1454 Procedure chk_attr_or_reference_exists(p_template_id in number,
1455 p_reference_mode in varchar2) is
1456 --
1457 Cursor csr_attr_exist is
1458 Select null from pqh_template_attributes
1459 Where template_id = p_template_id;
1460 --
1461 --
1462 Cursor csr_attr_with_req is
1463 Select null from pqh_template_attributes
1464 Where template_id = p_template_id
1465 AND (view_flag is NOT NULL OR
1466 edit_flag IS NOT NULL OR
1467 required_flag IS NOT NULL);
1468 --
1469 Cursor csr_copy_exist is
1470 Select null from pqh_ref_templates
1471 Where parent_template_id = p_template_id
1472 and reference_type_cd = 'COPY';
1473 --
1474 Cursor csr_ref_exist is
1475 Select null from pqh_ref_templates
1476 Where parent_template_id = p_template_id
1477 and reference_type_cd = 'REFERENCE';
1478 --
1479 --
1480 l_dummy varchar2(1);
1481 l_proc varchar2(72) := g_package||'chk_attr_or_reference_exists';
1482 --
1483 Begin
1484 --
1485 hr_utility.set_location('Entering:'||l_proc, 5);
1486 --
1487 Open csr_attr_exist;
1488 Fetch csr_attr_exist into l_dummy;
1489 --
1490 -- No attributes for the template
1491 --
1492 If csr_attr_exist%notfound then
1493 --
1494 -- Check if any template was referenced if this is the reference mode.
1495 --
1496 If p_reference_mode <> 'Y' then
1497 --
1498 Open csr_ref_exist;
1499 Fetch csr_ref_exist into l_dummy;
1500 If csr_ref_exist%notfound then
1501 --
1502 Close csr_attr_exist;
1503 Close csr_ref_exist;
1504 --
1505 hr_utility.set_message(8302,'PQH_NO_TEMP_ATTR_OR_REF');
1506 hr_utility.raise_error;
1507 --
1508 End if;
1509 Close csr_ref_exist;
1510 --
1511 Else
1512 --
1513 -- If copy mode and no attributes exist for template raise error.
1514 --
1515 hr_utility.set_message(8302,'PQH_NO_TEMP_ATTR_OR_COPY');
1516 hr_utility.raise_error;
1517 --
1518 /**
1519 Open csr_copy_exist;
1520 Fetch csr_copy_exist into l_dummy;
1521 If csr_copy_exist%notfound then
1522 --
1523 Close csr_attr_exist;
1524 Close csr_copy_exist;
1525 --
1526 hr_utility.set_message(8302,'PQH_NO_TEMP_ATTR_OR_COPY');
1527 hr_utility.raise_error;
1528 --
1529 End if;
1530 Close csr_copy_exist;
1531 --
1532 **/
1533 --
1534 End if;
1535 --
1536 Else
1537 --
1538 -- Copy mode
1539 --
1540 If p_reference_mode = 'Y' then
1541 --
1542 -- Attributes exist , but do not have any requirements setup
1543 --
1544 Open csr_attr_with_req;
1545 Fetch csr_attr_with_req into l_dummy;
1546 If csr_attr_with_req%notfound then
1547 --
1548 Close csr_attr_exist;
1549 Close csr_attr_with_req;
1550 --
1551 hr_utility.set_message(8302,'PQH_NO_TEMP_ATTR_WITH_REQ');
1552 hr_utility.raise_error;
1553 --
1554 End if;
1555 Close csr_attr_with_req;
1556 --
1557 End if;
1558 --
1559 End if;
1560 --
1561 --
1562 Close csr_attr_exist;
1563 --
1564 hr_utility.set_location('Leaving:'||l_proc,10);
1565 --
1566 end chk_attr_or_reference_exists;
1567 --
1568 -- ----------------------------------------------------------------------------
1569 -- |------< chk_invalid_freeze >------|
1570 -- ----------------------------------------------------------------------------
1571 --
1572 -- Description
1573 -- This procedure makes validations before a freeze or unfreeze
1577 -- None.
1574 -- is allowed.This check is needed only on updation of freeze_status_cd
1575 --
1576 -- Pre Conditions
1578 --
1579 -- In Parameters
1580 -- template_id PK of record being inserted or updated.
1581 -- freeze_status_cd Value of lookup code.
1582 -- effective_date effective date
1583 -- object_version_number Object version number of record being
1584 -- inserted or updated.
1585 --
1586 -- Post Success
1587 -- Processing continues
1588 --
1589 -- Post Failure
1590 -- Error handled by procedure
1591 --
1592 -- Access Status
1593 -- Internal table handler use only.
1594 --
1595 Procedure chk_invalid_freeze(p_template_id in number,
1596 p_transaction_category_id in number,
1597 p_freeze_status_cd in varchar2,
1598 p_effective_date in date,
1599 p_object_version_number in number) is
1600 --
1601 l_proc varchar2(72) := g_package||'chk_invalid_freeze';
1602 l_reference_mode varchar2(10);
1603 l_api_updating boolean;
1604 --
1605 Begin
1606 --
1607 hr_utility.set_location('Entering:'||l_proc, 5);
1608 --
1609 l_api_updating := pqh_tem_shd.api_updating
1610 (p_template_id => p_template_id,
1611 p_object_version_number => p_object_version_number);
1612 --
1613 if (l_api_updating
1614 and nvl(p_freeze_status_cd,hr_api.g_varchar2)
1615 <> nvl(pqh_tem_shd.g_old_rec.freeze_status_cd,hr_api.g_varchar2)
1616 ) then
1617 --
1618 --
1619 if p_freeze_status_cd = 'FREEZE_TEMPLATE' then
1620 --
1621 -- On freeze check if all master and child attributes are
1622 -- attached to the template.
1623 --
1624 l_reference_mode := NULL;
1625 l_reference_mode := fnd_profile.value('PQH_DISALLOW_TEMPLATE_REFERENCE');
1626 --
1627 chk_attr_or_reference_exists
1628 (p_template_id => p_template_id,
1629 p_reference_mode => nvl(l_reference_mode,'Y'));
1630 --
1631 chk_master_child_attributes
1632 (p_transaction_category_id => p_transaction_category_id,
1633 p_template_id => p_template_id);
1634 --
1635 elsif p_freeze_status_cd IS NULL then
1636 --
1637 chk_rout_hist_exist(
1638 p_template_id => p_template_id);
1639 --
1640 --
1641 end if;
1642 --
1643 --
1644 End if;
1645 --
1646 hr_utility.set_location('Leaving:'||l_proc,10);
1647 --
1648 end chk_invalid_freeze;
1649 --
1650 --
1651 -- END ADDITIONAL CHKS
1652 --
1653 -- ----------------------------------------------------------------------------
1654 -- |---------------------------< insert_validate >----------------------------|
1655 -- ----------------------------------------------------------------------------
1656 Procedure insert_validate(p_rec in pqh_tem_shd.g_rec_type
1657 ,p_effective_date in date) is
1658 --
1659 l_proc varchar2(72) := g_package||'insert_validate';
1660 --
1661 Begin
1662 hr_utility.set_location('Entering:'||l_proc, 5);
1663 --
1664 -- Call all supporting business operations
1665 --
1666 chk_template_id
1667 (p_template_id => p_rec.template_id,
1668 p_object_version_number => p_rec.object_version_number);
1669 --
1670 chk_transaction_category_id
1671 (p_template_id => p_rec.template_id,
1672 p_transaction_category_id => p_rec.transaction_category_id,
1673 p_object_version_number => p_rec.object_version_number);
1674 --
1675 chk_legislation_code
1676 (p_template_id => p_rec.template_id,
1677 p_legislation_code => p_rec.legislation_code,
1678 p_object_version_number => p_rec.object_version_number);
1679 --
1680 chk_template_type_cd
1681 (p_template_id => p_rec.template_id,
1682 p_template_type_cd => p_rec.template_type_cd,
1683 p_effective_date => p_effective_date,
1684 p_object_version_number => p_rec.object_version_number);
1685 --
1686 chk_create_flag
1687 (p_template_id => p_rec.template_id,
1688 p_create_flag => p_rec.create_flag,
1689 p_template_type_cd => p_rec.template_type_cd,
1690 p_effective_date => p_effective_date,
1691 p_object_version_number => p_rec.object_version_number);
1692 --
1693 chk_under_review_flag
1694 (p_template_id => p_rec.template_id,
1695 p_create_flag => p_rec.create_flag,
1696 p_under_review_flag => p_rec.under_review_flag,
1697 p_template_type_cd => p_rec.template_type_cd,
1698 p_effective_date => p_effective_date,
1699 p_object_version_number => p_rec.object_version_number);
1700 --
1701 chk_enable_flag
1702 (p_template_id => p_rec.template_id,
1703 p_enable_flag => p_rec.enable_flag,
1704 p_effective_date => p_effective_date,
1705 p_object_version_number => p_rec.object_version_number);
1706 --
1707 chk_attribute_only_flag
1711 p_object_version_number => p_rec.object_version_number);
1708 (p_template_id => p_rec.template_id,
1709 p_attribute_only_flag => p_rec.attribute_only_flag,
1710 p_effective_date => p_effective_date,
1712 --
1713 chk_freeze_status_cd
1714 (p_template_id => p_rec.template_id,
1715 p_freeze_status_cd => p_rec.freeze_status_cd,
1716 p_effective_date => p_effective_date,
1717 p_object_version_number => p_rec.object_version_number);
1718 --
1719 chk_template_name
1720 (p_template_id => p_rec.template_id,
1721 p_transaction_category_id => p_rec.transaction_category_id,
1722 p_template_name => p_rec.template_name,
1723 p_object_version_number => p_rec.object_version_number);
1724 --
1725 --
1726 --
1727 --
1728 hr_utility.set_location(' Leaving:'||l_proc, 10);
1729 End insert_validate;
1730 --
1731 -- ----------------------------------------------------------------------------
1732 -- |---------------------------< update_validate >----------------------------|
1733 -- ----------------------------------------------------------------------------
1734 Procedure update_validate(p_rec in pqh_tem_shd.g_rec_type
1735 ,p_effective_date in date) is
1736 --
1737 l_proc varchar2(72) := g_package||'update_validate';
1738 --
1739 Begin
1740 hr_utility.set_location('Entering:'||l_proc, 5);
1741 --
1742 -- Call all supporting business operations
1743 --
1744 chk_template_id
1745 (p_template_id => p_rec.template_id,
1746 p_object_version_number => p_rec.object_version_number);
1747 --
1748 chk_tem_dml_allowed
1749 (p_template_id => p_rec.template_id,
1750 p_freeze_status_cd => p_rec.freeze_status_cd,
1751 p_object_version_number => p_rec.object_version_number);
1752 --
1753 chk_tct_upd_allowed
1754 (p_template_id => p_rec.template_id,
1755 p_transaction_category_id => p_rec.transaction_category_id,
1756 p_object_version_number => p_rec.object_version_number);
1757 --
1758 chk_transaction_category_id
1759 (p_template_id => p_rec.template_id,
1760 p_transaction_category_id => p_rec.transaction_category_id,
1761 p_object_version_number => p_rec.object_version_number);
1762 --
1763 chk_legislation_code
1764 (p_template_id => p_rec.template_id,
1765 p_legislation_code => p_rec.legislation_code,
1766 p_object_version_number => p_rec.object_version_number);
1767 --
1768 chk_template_type_cd
1769 (p_template_id => p_rec.template_id,
1770 p_template_type_cd => p_rec.template_type_cd,
1771 p_effective_date => p_effective_date,
1772 p_object_version_number => p_rec.object_version_number);
1773 --
1774 chk_create_flag
1775 (p_template_id => p_rec.template_id,
1776 p_create_flag => p_rec.create_flag,
1777 p_template_type_cd => p_rec.template_type_cd,
1778 p_effective_date => p_effective_date,
1779 p_object_version_number => p_rec.object_version_number);
1780 --
1781 chk_under_review_flag
1782 (p_template_id => p_rec.template_id,
1783 p_create_flag => p_rec.create_flag,
1784 p_under_review_flag => p_rec.under_review_flag,
1785 p_template_type_cd => p_rec.template_type_cd,
1786 p_effective_date => p_effective_date,
1787 p_object_version_number => p_rec.object_version_number);
1788 --
1789 chk_enable_flag
1790 (p_template_id => p_rec.template_id,
1791 p_enable_flag => p_rec.enable_flag,
1792 p_effective_date => p_effective_date,
1793 p_object_version_number => p_rec.object_version_number);
1794 --
1795 chk_attribute_only_flag
1796 (p_template_id => p_rec.template_id,
1797 p_attribute_only_flag => p_rec.attribute_only_flag,
1798 p_effective_date => p_effective_date,
1799 p_object_version_number => p_rec.object_version_number);
1800 --
1801 chk_freeze_status_cd
1802 (p_template_id => p_rec.template_id,
1803 p_freeze_status_cd => p_rec.freeze_status_cd,
1804 p_effective_date => p_effective_date,
1805 p_object_version_number => p_rec.object_version_number);
1806 --
1807 chk_invalid_freeze
1808 (p_template_id => p_rec.template_id,
1809 p_transaction_category_id => p_rec.transaction_category_id,
1810 p_freeze_status_cd => p_rec.freeze_status_cd,
1811 p_effective_date => p_effective_date,
1812 p_object_version_number => p_rec.object_version_number);
1813 --
1814 chk_template_name
1815 (p_template_id => p_rec.template_id,
1816 p_transaction_category_id => p_rec.transaction_category_id,
1817 p_template_name => p_rec.template_name,
1818 p_object_version_number => p_rec.object_version_number);
1819 --
1820 --
1821 hr_utility.set_location(' Leaving:'||l_proc, 10);
1822 End update_validate;
1823 --
1824 -- ----------------------------------------------------------------------------
1825 -- |---------------------------< delete_validate >----------------------------|
1826 -- ----------------------------------------------------------------------------
1827 Procedure delete_validate(p_rec in pqh_tem_shd.g_rec_type
1828 ,p_effective_date in date) is
1829 --
1830 l_proc varchar2(72) := g_package||'delete_validate';
1831 --
1832 Begin
1833 hr_utility.set_location('Entering:'||l_proc, 5);
1834 --
1835 -- Call all supporting business operations
1836 --
1837 --
1838 hr_utility.set_location(' Leaving:'||l_proc, 10);
1839 End delete_validate;
1840 --
1841 end pqh_tem_bus;