[Home] [Help]
PACKAGE BODY: APPS.PQH_TAT_BUS
Source
1 Package Body pqh_tat_bus as
2 /* $Header: pqtatrhi.pkb 120.3 2011/04/28 09:35:56 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tat_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_template_attribute_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_attribute_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_attribute_id(p_template_attribute_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_template_attribute_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_tat_shd.api_updating
47 (p_template_attribute_id => p_template_attribute_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_template_attribute_id,hr_api.g_number)
52 <> pqh_tat_shd.g_old_rec.template_attribute_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_tat_shd.constraint_error('PQH_TEMPLATE_ATTRIBUTES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_template_attribute_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_tat_shd.constraint_error('PQH_TEMPLATE_ATTRIBUTES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_template_attribute_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_template_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_template_attribute_id PK
89 -- p_template_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_template_id (p_template_attribute_id in number,
102 p_template_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_template_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 l_freeze_status_cd pqh_templates.freeze_status_cd%type;
109 --
110 cursor c1 is
111 select freeze_status_cd
112 from pqh_templates a
113 where a.template_id = p_template_id;
114 --
115 Begin
116 --
117 hr_utility.set_location('Entering:'||l_proc,5);
118 --
119 l_api_updating := pqh_tat_shd.api_updating
120 (p_template_attribute_id => p_template_attribute_id,
121 p_object_version_number => p_object_version_number);
122 --
123 if (l_api_updating
124 and nvl(p_template_id,hr_api.g_number)
125 <> nvl(pqh_tat_shd.g_old_rec.template_id,hr_api.g_number)
126 or not l_api_updating) then
127 --
128 -- check if template_id value exists in pqh_templates table
129 --
130 open c1;
131 --
132 -- fetch c1 into l_dummy;
133 fetch c1 into l_freeze_status_cd;
134 if c1%notfound then
135 --
136 close c1;
137 --
138 -- raise error as FK does not relate to PK in pqh_templates
139 -- table.
140 --
141 pqh_tat_shd.constraint_error('PQH_TEMPLATE_ATTRIBUTES_FK2');
142 --
143 end if;
144 --
145 close c1;
146 --
147 end if;
148 --
149 if l_freeze_status_cd = 'FREEZE_TEMPLATE' then
150 hr_utility.set_message(8302,'PQH_INVALID_TAT_OPERATION');
151 hr_utility.raise_error;
155 --
152 end if;
153 --
154 hr_utility.set_location('Leaving:'||l_proc,10);
156 End chk_template_id;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |------< chk_attribute_id >------|
160 -- ----------------------------------------------------------------------------
161 --
162 -- Description
163 -- This procedure checks that a referenced foreign key actually exists
164 -- in the referenced table.
165 --
166 -- Pre-Conditions
167 -- None.
168 --
169 -- In Parameters
170 -- p_template_attribute_id PK
171 -- p_attribute_id ID of FK column
172 -- p_object_version_number object version number
173 --
174 -- Post Success
175 -- Processing continues
176 --
177 -- Post Failure
178 -- Error raised.
179 --
180 -- Access Status
181 -- Internal table handler use only.
182 --
183 Procedure chk_attribute_id (p_template_attribute_id in number,
184 p_template_id in number,
185 p_attribute_id in number,
186 p_object_version_number in number) is
187 --
188 l_api_updating boolean;
189 l_dummy varchar2(1);
190 l_enable_flag pqh_attributes.enable_flag%type;
191 l_attribute_name pqh_attributes_vl.attribute_name%type;
192 --
193 cursor c1 is
194 select nvl(enable_flag,'N')
195 from pqh_attributes a
196 where a.attribute_id = p_attribute_id;
197 --
198 Cursor c2 is
199 select att.attribute_name
200 from pqh_attributes_vl att,pqh_template_attributes tat
201 where tat.attribute_id = p_attribute_id
202 and tat.template_id = p_template_id
203 and tat.attribute_id = att.attribute_id;
204 --
205 l_proc varchar2(72) := g_package||'chk_attribute_id';
206 --
207 Begin
208 --
209 hr_utility.set_location('Entering:'||l_proc,5);
210 --
211 l_api_updating := pqh_tat_shd.api_updating
212 (p_template_attribute_id => p_template_attribute_id,
213 p_object_version_number => p_object_version_number);
214 --
215 if (l_api_updating
216 and nvl(p_attribute_id,hr_api.g_number)
217 <> nvl(pqh_tat_shd.g_old_rec.attribute_id,hr_api.g_number)
218 or not l_api_updating) then
219 --
220 -- check if attribute_id value exists in pqh_attributes table
221 --
222 open c1;
223 --
224 fetch c1 into l_enable_flag;
225 if c1%notfound then
226 --
227 close c1;
228 --
229 -- raise error as FK does not relate to PK in pqh_attributes
230 -- table.
231 --
232 pqh_tat_shd.constraint_error('PQH_TEMPLATE_ATTRIBUTES_FK1');
233 --
234 end if;
235 --
236 close c1;
237 --
238 --
239 -- When trying to insert a template attribute or when modifying the
240 -- attribute id, check if the is enabled.
241 -- Cannot add a attribute that is not enabled to a template.
242 --
243 If l_enable_flag <> 'Y' then
244 hr_utility.set_message(8302,'PQH_ATTRIBUTE_NOT_ENABLED');
245 hr_utility.raise_error;
246 End if;
247 --
248 -- When trying to insert a template attribute or when modifying the
249 -- the attribute id , check if that attribute is not already attached
250 -- to the template.
251 --
252 Open c2;
253 Fetch c2 into l_attribute_name;
254 If c2%found then
255 --
256 -- Should not allow same attribute to be added twice to the template.
257 --
258 hr_utility.set_message(8302,'PQH_ATTR_ATTACHED_TO_TEM');
259 hr_utility.set_message_token('ATTRIBUTE', l_attribute_name);
260 hr_utility.raise_error;
261 --
262 End if;
263 Close c2;
264 --
265 End if;
266 --
267 hr_utility.set_location('Leaving:'||l_proc,10);
268 --
269 End chk_attribute_id;
270 --
271 -- ----------------------------------------------------------------------------
272 -- |------< chk_att_tct >------|
273 -- ----------------------------------------------------------------------------
274 -- Description
275 -- This procedure checks if the template and attribute have the same
276 -- transaction category id.
277 --
278 -- Pre-Conditions
279 -- None.
280 --
281 -- In Parameters
282 -- p_template_attribute_id PK
283 -- p_template_id ID of FK column
284 -- p_attribute_id ID of FK column
285 -- p_object_version_number object version number
286 --
287 -- Post Success
288 -- Processing continues
289 --
290 -- Post Failure
291 -- Error raised.
292 --
293 -- Access Status
294 -- Internal table handler use only.
295 --
296 Procedure chk_att_tct (p_template_attribute_id in number,
297 p_template_id in number,
298 p_attribute_id in number,
299 p_object_version_number in number) is
300 --
301 l_proc varchar2(72) := g_package||'chk_att_tct';
302 l_api_updating boolean;
303 l_dummy varchar2(1);
304 l_tem_tct pqh_templates.transaction_category_id%type;
305 l_select_flag pqh_txn_category_attributes.select_flag%type;
306 --
307 cursor c1 is
308 select transaction_category_id
309 from pqh_templates a
313 select nvl(tca.select_flag,'N')
310 where a.template_id = p_template_id;
311 --
312 cursor c2 is
314 from pqh_attributes att , pqh_txn_category_attributes tca
315 where att.attribute_id = p_attribute_id
316 AND att.attribute_id = tca.attribute_id
317 AND tca.transaction_category_id = l_tem_tct;
318 Begin
319 --
320 hr_utility.set_location('Entering:'||l_proc,5);
321 --
322 l_api_updating := pqh_tat_shd.api_updating
323 (p_template_attribute_id => p_template_attribute_id,
324 p_object_version_number => p_object_version_number);
325 --
326 /**
327 if (l_api_updating
328 and nvl(p_attribute_id,hr_api.g_number)
329 <> nvl(pqh_tat_shd.g_old_rec.attribute_id,hr_api.g_number)
330 or not l_api_updating) then
331 **/
332 --
333 --
334 open c1;
335 fetch c1 into l_tem_tct;
336 close c1;
337 --
338 open c2;
339 fetch c2 into l_select_flag;
340 --
341 If c2%notfound then
342 Close c2;
343 hr_utility.set_message(8302,'PQH_TEM_ATTR_TCT_MISMATCH');
344 hr_utility.raise_error;
345 End if;
346 --
347 close c2;
348 --
349 -- Cannot add an attribute that is not selectable , to a template
350 --
351 If l_select_flag <> 'Y' then
352 hr_utility.set_message(8302,'PQH_ATTRIBUTE_NOT_SELECTABLE');
353 hr_utility.raise_error;
354 End if;
355 --
356 --
357
358 hr_utility.set_location('Leaving:'||l_proc,10);
359 --
360 End chk_att_tct;
361 --
362 -- Verifies that the template and attribute Legislations are compatible
363 --
364 Procedure chk_legislation_code (p_template_attribute_id in number,
365 p_template_id in number,
366 p_attribute_id in varchar2,
367 p_object_version_number in number) is
368 --
369 l_proc varchar2(72) := g_package||'chk_legislation_code';
370 l_api_updating boolean;
371 l_dummy varchar2(1);
372 l_template_leg_code varchar2(30);
373 l_template_name varchar2(100);
374 l_attribute_leg_code varchar2(30);
375 l_attribute_name varchar2(100);
376 --
377 cursor c_template_leg_code is
378 select legislation_code, template_name
379 from pqh_templates_vl
380 where template_id = p_template_id;
381 --
382 cursor c_attribute_leg_code is
383 select legislation_code, attribute_name
384 from pqh_attributes_vl
385 where attribute_id = p_attribute_id;
386 --
387 Begin
388 --
389 hr_utility.set_location('Entering:'||l_proc,5);
390 --
391
392 l_api_updating := pqh_tat_shd.api_updating
393 (p_template_attribute_id => p_template_attribute_id,
394 p_object_version_number => p_object_version_number);
395 --
396 if (l_api_updating
397 and nvl(p_attribute_id,hr_api.g_number)
398 <> nvl(pqh_tat_shd.g_old_rec.attribute_id,hr_api.g_number)
399 or not l_api_updating) then
400 --
401 open c_template_leg_code;
402 fetch c_template_leg_code into l_template_leg_code, l_template_name;
403 close c_template_leg_code;
404 open c_attribute_leg_code;
405 fetch c_attribute_leg_code into l_attribute_leg_code, l_attribute_name;
406 close c_attribute_leg_code;
407 --
408 if l_template_leg_code is null then
409 if l_attribute_leg_code is not null then
410 hr_utility.set_message(8302,'PQH_NO_LEG_TEM_ATT');
411 hr_utility.set_message_token('TEMPLATE', l_template_name);
412 hr_utility.set_message_token('ATTRIBUTE', l_attribute_name);
413 hr_utility.raise_error;
414 end if;
415 else
416 if nvl(l_attribute_leg_code,l_template_leg_code) <> l_template_leg_code then
417 hr_utility.set_message(8302,'PQH_TEM_ATT_LEG_NE_TEM');
418 hr_utility.set_message_token('TEMPLATE', l_template_name);
419 hr_utility.set_message_token('ATTRIBUTE', l_attribute_name);
420 hr_utility.raise_error;
421 end if;
422 end if;
423 --
424 --
425 end if;
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 End chk_legislation_code;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |------< chk_edit_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 -- template_attribute_id PK of record being inserted or updated.
443 -- edit_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_edit_flag(p_template_attribute_id in number,
458 p_edit_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_edit_flag';
466 --
463 l_api_updating boolean;
464 --
465 Begin
467 hr_utility.set_location('Entering:'||l_proc, 5);
468 --
469 l_api_updating := pqh_tat_shd.api_updating
470 (p_template_attribute_id => p_template_attribute_id,
471 p_object_version_number => p_object_version_number);
472 --
473 if (l_api_updating
474 and p_edit_flag
475 <> nvl(pqh_tat_shd.g_old_rec.edit_flag,hr_api.g_varchar2)
476 or not l_api_updating)
477 and p_edit_flag is not null then
478 --
479 -- check if value of lookup falls within lookup type.
480 --
481 if hr_api.not_exists_in_hr_lookups
482 (p_lookup_type => 'YES_NO',
483 p_lookup_code => p_edit_flag,
484 p_effective_date => p_effective_date) then
485 --
486 -- raise error as does not exist as lookup
487 --
488 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
489 hr_utility.raise_error;
490 --
491 end if;
492 --
493 end if;
494 --
495 hr_utility.set_location('Leaving:'||l_proc,10);
496 --
497 end chk_edit_flag;
498 --
499 -- ----------------------------------------------------------------------------
500 -- |------< chk_view_flag >------|
501 -- ----------------------------------------------------------------------------
502 --
503 -- Description
504 -- This procedure is used to check that the lookup value is valid.
505 --
506 -- Pre Conditions
507 -- None.
508 --
509 -- In Parameters
510 -- template_attribute_id PK of record being inserted or updated.
511 -- view_flag Value of lookup code.
512 -- effective_date effective date
513 -- object_version_number Object version number of record being
514 -- inserted or updated.
515 --
516 -- Post Success
517 -- Processing continues
518 --
519 -- Post Failure
520 -- Error handled by procedure
521 --
522 -- Access Status
523 -- Internal table handler use only.
524 --
525 Procedure chk_view_flag(p_template_attribute_id in number,
526 p_view_flag in varchar2,
527 p_effective_date in date,
528 p_object_version_number in number) is
529 --
530 l_proc varchar2(72) := g_package||'chk_view_flag';
531 l_api_updating boolean;
532 --
533 Begin
534 --
535 hr_utility.set_location('Entering:'||l_proc, 5);
536 --
537 l_api_updating := pqh_tat_shd.api_updating
538 (p_template_attribute_id => p_template_attribute_id,
539 p_object_version_number => p_object_version_number);
540 --
541 if (l_api_updating
542 and p_view_flag
543 <> nvl(pqh_tat_shd.g_old_rec.view_flag,hr_api.g_varchar2)
544 or not l_api_updating)
545 and p_view_flag is not null then
546 --
547 -- check if value of lookup falls within lookup type.
548 --
549 if hr_api.not_exists_in_hr_lookups
550 (p_lookup_type => 'YES_NO',
551 p_lookup_code => p_view_flag,
552 p_effective_date => p_effective_date) then
553 --
554 -- raise error as does not exist as lookup
555 --
556 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
557 hr_utility.raise_error;
558 --
559 end if;
560 --
561 end if;
562 --
563 hr_utility.set_location('Leaving:'||l_proc,10);
564 --
565 end chk_view_flag;
566 --
567 -- ----------------------------------------------------------------------------
568 -- |------< chk_required_flag >------|
569 -- ----------------------------------------------------------------------------
570 --
571 -- Description
572 -- This procedure is used to check that the lookup value is valid.
573 --
574 -- Pre Conditions
575 -- None.
576 --
577 -- In Parameters
578 -- template_attribute_id PK of record being inserted or updated.
579 -- required_flag Value of lookup code.
580 -- effective_date effective date
581 -- object_version_number Object version number of record being
582 -- inserted or updated.
583 --
584 -- Post Success
585 -- Processing continues
586 --
587 -- Post Failure
588 -- Error handled by procedure
589 --
590 -- Access Status
591 -- Internal table handler use only.
592 --
593 Procedure chk_required_flag(p_template_attribute_id in number,
594 p_required_flag in varchar2,
595 p_effective_date in date,
596 p_object_version_number in number) is
597 --
598 l_proc varchar2(72) := g_package||'chk_required_flag';
599 l_api_updating boolean;
600 --
601 Begin
602 --
603 hr_utility.set_location('Entering:'||l_proc, 5);
604 --
605 l_api_updating := pqh_tat_shd.api_updating
606 (p_template_attribute_id => p_template_attribute_id,
607 p_object_version_number => p_object_version_number);
608 --
609 if (l_api_updating
610 and p_required_flag
611 <> nvl(pqh_tat_shd.g_old_rec.required_flag,hr_api.g_varchar2)
612 or not l_api_updating)
613 and p_required_flag is not null then
614 --
615 -- check if value of lookup falls within lookup type.
616 --
617 if hr_api.not_exists_in_hr_lookups
618 (p_lookup_type => 'YES_NO',
619 p_lookup_code => p_required_flag,
620 p_effective_date => p_effective_date) then
621 --
622 -- raise error as does not exist as lookup
623 --
624 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
625 hr_utility.raise_error;
626 --
627 end if;
628 --
629 end if;
630 --
631 hr_utility.set_location('Leaving:'||l_proc,10);
632 --
633 end chk_required_flag;
634 --
635 -- ----------------------------------------------------------------------------
636 -- |------< chk_flags_mismatch >------|
637 -- ----------------------------------------------------------------------------
638 -- Description
639 -- This procedure checks if there is any mismatch in the value of
640 -- view , edit and Required flags.
641 --
642 -- Pre-Conditions
643 -- None.
644 --
645 -- In Parameters
646 -- p_template_attribute_id PK
647 -- p_view_flag View Flag
648 -- p_edit_flag Edit Flag
649 -- p_Required_flag Required Flag
650 -- p_object_version_number object version number
651 --
652 -- Post Success
653 -- Processing continues
654 --
655 -- Post Failure
656 -- Error raised.
657 --
658 -- Access Status
659 -- Internal table handler use only.
660 --
661 Procedure chk_flags_mismatch (p_template_attribute_id in number,
662 p_view_flag in varchar2,
663 p_edit_flag in varchar2,
664 p_required_flag in varchar2,
665 p_object_version_number in number) is
666 --
667 l_proc varchar2(72) := g_package||'chk_flags_mismatch';
668 --
669 --
670 Begin
671 --
672 hr_utility.set_location('Entering:'||l_proc,5);
673 --
674 if p_required_flag = 'Y' then
675 if p_edit_flag = 'Y' then
676 if p_view_flag = 'Y' then
677 null;
678 else
679 hr_utility.set_message(8302,'PQH_VIEW_EDIT_MISMATCH');
680 hr_utility.raise_error;
681 end if;
682 else
683 hr_utility.set_message(8302,'PQH_EDIT_REQD_MISMATCH');
684 hr_utility.raise_error;
685 end if;
686 end if;
687 --
688 if p_view_flag = 'N' then
689 if p_edit_flag = 'N' then
690 if p_required_flag = 'N' then
691 null;
692 else
693 hr_utility.set_message(8302,'PQH_EDIT_REQD_MISMATCH');
694 hr_utility.raise_error;
695 end if;
696 else
697 hr_utility.set_message(8302,'PQH_VIEW_EDIT_MISMATCH');
698 hr_utility.raise_error;
699 end if;
700 end if;
701
702 hr_utility.set_location('Leaving:'||l_proc,10);
703 --
704 End chk_flags_mismatch;
705 -- ----------------------------------------------------------------------------
706 -- |---------------------------< insert_validate >----------------------------|
707 -- ----------------------------------------------------------------------------
708 Procedure insert_validate(p_rec in pqh_tat_shd.g_rec_type
709 ,p_effective_date in date) is
710 --
711 l_proc varchar2(72) := g_package||'insert_validate';
712 --
713 Begin
714 hr_utility.set_location('Entering:'||l_proc, 5);
715 --
716 -- Call all supporting business operations
717 --
718 chk_template_attribute_id
719 (p_template_attribute_id => p_rec.template_attribute_id,
720 p_object_version_number => p_rec.object_version_number);
721 --
722 chk_template_id
723 (p_template_attribute_id => p_rec.template_attribute_id,
724 p_template_id => p_rec.template_id,
725 p_object_version_number => p_rec.object_version_number);
726 --
727 chk_attribute_id
728 (p_template_attribute_id => p_rec.template_attribute_id,
729 p_template_id => p_rec.template_id,
730 p_attribute_id => p_rec.attribute_id,
731 p_object_version_number => p_rec.object_version_number);
732 --
733 chk_att_tct
734 (p_template_attribute_id => p_rec.template_attribute_id,
735 p_template_id => p_rec.template_id,
736 p_attribute_id => p_rec.attribute_id,
737 p_object_version_number => p_rec.object_version_number);
738 --
739 chk_legislation_code
740 (p_template_attribute_id => p_rec.template_attribute_id,
744 --
741 p_template_id => p_rec.template_id,
742 p_attribute_id => p_rec.attribute_id,
743 p_object_version_number => p_rec.object_version_number);
745 chk_edit_flag
746 (p_template_attribute_id => p_rec.template_attribute_id,
747 p_edit_flag => p_rec.edit_flag,
748 p_effective_date => p_effective_date,
749 p_object_version_number => p_rec.object_version_number);
750 --
751 chk_view_flag
752 (p_template_attribute_id => p_rec.template_attribute_id,
753 p_view_flag => p_rec.view_flag,
754 p_effective_date => p_effective_date,
755 p_object_version_number => p_rec.object_version_number);
756 --
757 chk_required_flag
758 (p_template_attribute_id => p_rec.template_attribute_id,
759 p_required_flag => p_rec.required_flag,
760 p_effective_date => p_effective_date,
761 p_object_version_number => p_rec.object_version_number);
762 --
763 chk_flags_mismatch
764 (p_template_attribute_id => p_rec.template_attribute_id,
765 p_view_flag => p_rec.view_flag,
766 p_edit_flag => p_rec.edit_flag,
767 p_required_flag => p_rec.required_flag,
768 p_object_version_number => p_rec.object_version_number);
769 --
770 --
771 hr_utility.set_location(' Leaving:'||l_proc, 10);
772 End insert_validate;
773 --
774 -- ----------------------------------------------------------------------------
775 -- |---------------------------< update_validate >----------------------------|
776 -- ----------------------------------------------------------------------------
777 Procedure update_validate(p_rec in pqh_tat_shd.g_rec_type
778 ,p_effective_date in date) is
779 --
780 l_proc varchar2(72) := g_package||'update_validate';
781 --
782 Begin
783 hr_utility.set_location('Entering:'||l_proc, 5);
784 --
785 -- Call all supporting business operations
786 --
787 chk_template_attribute_id
788 (p_template_attribute_id => p_rec.template_attribute_id,
789 p_object_version_number => p_rec.object_version_number);
790 --
791 chk_template_id
792 (p_template_attribute_id => p_rec.template_attribute_id,
793 p_template_id => p_rec.template_id,
794 p_object_version_number => p_rec.object_version_number);
795 --
796 chk_attribute_id
797 (p_template_attribute_id => p_rec.template_attribute_id,
798 p_template_id => p_rec.template_id,
799 p_attribute_id => p_rec.attribute_id,
800 p_object_version_number => p_rec.object_version_number);
801 --
802 chk_att_tct
803 (p_template_attribute_id => p_rec.template_attribute_id,
804 p_template_id => p_rec.template_id,
805 p_attribute_id => p_rec.attribute_id,
806 p_object_version_number => p_rec.object_version_number);
807 --
808 chk_legislation_code
809 (p_template_attribute_id => p_rec.template_attribute_id,
810 p_template_id => p_rec.template_id,
811 p_attribute_id => p_rec.attribute_id,
812 p_object_version_number => p_rec.object_version_number);
813 --
814 chk_edit_flag
815 (p_template_attribute_id => p_rec.template_attribute_id,
816 p_edit_flag => p_rec.edit_flag,
817 p_effective_date => p_effective_date,
818 p_object_version_number => p_rec.object_version_number);
819 --
820 chk_view_flag
821 (p_template_attribute_id => p_rec.template_attribute_id,
822 p_view_flag => p_rec.view_flag,
823 p_effective_date => p_effective_date,
824 p_object_version_number => p_rec.object_version_number);
825 --
826 chk_required_flag
827 (p_template_attribute_id => p_rec.template_attribute_id,
828 p_required_flag => p_rec.required_flag,
829 p_effective_date => p_effective_date,
830 p_object_version_number => p_rec.object_version_number);
831 --
832 chk_flags_mismatch
833 (p_template_attribute_id => p_rec.template_attribute_id,
834 p_view_flag => p_rec.view_flag,
835 p_edit_flag => p_rec.edit_flag,
836 p_required_flag => p_rec.required_flag,
837 p_object_version_number => p_rec.object_version_number);
838 --
839 --
840 hr_utility.set_location(' Leaving:'||l_proc, 10);
841 End update_validate;
842 --
843 -- ----------------------------------------------------------------------------
844 -- |---------------------------< delete_validate >----------------------------|
845 -- ----------------------------------------------------------------------------
846 Procedure delete_validate(p_rec in pqh_tat_shd.g_rec_type
847 ,p_effective_date in date) is
848 --
849 l_proc varchar2(72) := g_package||'delete_validate';
850 --
851 Begin
852 hr_utility.set_location('Entering:'||l_proc, 5);
853 --
854 -- Call all supporting business operations
855 --
856 hr_utility.set_location(' Leaving:'||l_proc, 10);
857 End delete_validate;
858 --
859 --
860 --
861 Procedure fetch_attribute_name(
862 p_attribute_id in pqh_attributes.attribute_id%type,
863 p_attribute_name out nocopy pqh_attributes.attribute_name%type) is
864 --
865 Cursor csr_attr_name is
866 Select attribute_name
867 From pqh_attributes
868 Where attribute_id = p_attribute_id;
869 --
870 l_proc varchar2(72) := g_package||'fetch_attribute_name';
871 --
872 Begin
873 --
874 hr_utility.set_location('Entering:'||l_proc, 5);
875 --
876 p_attribute_name := NULL;
877 --
878 Open csr_attr_name;
879 --
880 Fetch csr_attr_name into p_attribute_name;
881 --
882 Close csr_attr_name;
883 --
884 hr_utility.set_location(' Leaving:'||l_proc, 10);
885 --
886 End fetch_attribute_name;
887 --
888 --
889 -- ----------------------------------------------------------------------------
890 -- |------------------< populate_attribute_name >----------------------------|
891 -- ----------------------------------------------------------------------------
892 Procedure populate_attribute_name(
893 p_attr_table in pqh_prvcalc.t_attid_priv,
894 p_attr_name_table out nocopy pqh_prvcalc.t_attname_priv) is
895 --
896 l_attribute_name pqh_attributes.attribute_name%type;
897 l_mode_flag pqh_template_attributes.view_flag%type;
898 l_reqd_flag pqh_template_attributes.required_flag%type;
899 --
900 l_proc varchar2(72) := g_package||'populate_attribute_name';
901 --
902 Begin
903 --
904 hr_utility.set_location('Entering:'||l_proc, 5);
905 --
906 If p_attr_table.count > 0 then
907 --
908 For cnt in p_attr_table.FIRST .. p_attr_table.LAST loop
909 --
910 fetch_attribute_name(p_attribute_id => p_attr_table(cnt).attribute_id,
911 p_attribute_name => l_attribute_name);
912 --
913 p_attr_name_table(cnt).form_column_name := l_attribute_name;
914 p_attr_name_table(cnt).mode_flag := p_attr_table(cnt).mode_flag;
915 p_attr_name_table(cnt).reqd_flag := p_attr_table(cnt).reqd_flag;
916 --
917 End loop;
918 --
919 End if;
920 --
921 -- Sort the table by attribute name .
922 --
923 For cnt in 1..p_attr_name_table.COUNT loop
924
925 For j in 1..p_attr_name_table.COUNT-1 loop
926 --
927 If p_attr_name_table(j).form_column_name >
928 p_attr_name_table(j+1).form_column_name then
929
930 l_attribute_name := p_attr_name_table(j).form_column_name;
931 l_mode_flag := p_attr_name_table(j).mode_flag;
932 l_reqd_flag := p_attr_name_table(j).reqd_flag;
933
934 p_attr_name_table(j).form_column_name := p_attr_name_table(j+1).form_column_name;
935 p_attr_name_table(j).mode_flag := p_attr_name_table(j+1).mode_flag;
936 p_attr_name_table(j).reqd_flag := p_attr_name_table(j+1).reqd_flag;
937
938 p_attr_name_table(j+1).form_column_name := l_attribute_name;
939 p_attr_name_table(j+1).mode_flag := l_mode_flag;
940 p_attr_name_table(j+1).reqd_flag := l_reqd_flag;
941
942 End if;
943
944 End loop;
945
946 End loop;
947 --
948 --
949 hr_utility.set_location(' Leaving:'||l_proc, 10);
950 --
951 End populate_attribute_name;
952 --
953 end pqh_tat_bus;