[Home] [Help]
PACKAGE BODY: APPS.PQH_TCA_BUS
Source
1 Package Body pqh_tca_bus as
2 /* $Header: pqtcarhi.pkb 120.2 2005/10/12 20:19:48 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tca_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_txn_category_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 -- txn_category_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_txn_category_attribute_id(p_txn_category_attribute_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_txn_category_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_tca_shd.api_updating
47 (p_txn_category_attribute_id => p_txn_category_attribute_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_txn_category_attribute_id,hr_api.g_number)
52 <> pqh_tca_shd.g_old_rec.txn_category_attribute_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_tca_shd.constraint_error('PQH_TXN_CATEGORY_ATTRIBUTES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_txn_category_attribute_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_tca_shd.constraint_error('PQH_TXN_CATEGORY_ATTRIBUTES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_txn_category_attribute_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_transaction_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_txn_category_attribute_id PK
89 -- p_transaction_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_transaction_table_route_id (p_txn_category_attribute_id in number,
102 p_transaction_table_route_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_transaction_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_transaction_table_route_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_tca_shd.api_updating
119 (p_txn_category_attribute_id => p_txn_category_attribute_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_transaction_table_route_id,hr_api.g_number)
124 <> nvl(pqh_tca_shd.g_old_rec.transaction_table_route_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_transaction_table_route_id is not null then
127 --
128 -- check if transaction_table_route_id value exists in pqh_table_route table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in pqh_table_route
138 -- table.
139 --
140 pqh_tca_shd.constraint_error('PQH_TXN_CAT_ATTRIBUTES_FK4');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_transaction_table_route_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_value_set_id >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure checks that a referenced foreign key actually exists
158 -- in the referenced table.
159 --
160 -- Pre-Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- p_txn_category_attribute_id PK
165 -- p_value_set_id ID of FK column
166 -- p_object_version_number object version number
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error raised.
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_value_set_id (p_txn_category_attribute_id in number,
178 p_value_set_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_value_set_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from fnd_flex_value_sets a
188 where a.flex_value_Set_id = p_value_set_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_tca_shd.api_updating
195 (p_txn_category_attribute_id => p_txn_category_attribute_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_value_set_id,hr_api.g_number)
200 <> nvl(pqh_tca_shd.g_old_rec.value_set_id,hr_api.g_number)
201 or not l_api_updating) and
202 p_value_set_id is not null then
203 --
204 -- check if value_Set_id value exists in fnd_flex_value_Sets 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_tca_shd.constraint_error('PQH_TXN_CAT_ATTRIBUTES_FK5');
217 --
218 end if;
219 --
220 close c1;
221 --
222 end if;
223 --
224 hr_utility.set_location('Leaving:'||l_proc,10);
225 --
226 End chk_value_set_id;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |------< chk_transaction_category_id >------|
230 -- ----------------------------------------------------------------------------
231 --
232 -- Description
233 -- This procedure checks that a referenced foreign key actually exists
234 -- in the referenced table.
235 --
236 -- Pre-Conditions
237 -- None.
238 --
239 -- In Parameters
240 -- p_txn_category_attribute_id PK
241 -- p_transaction_category_id ID of FK column
242 -- p_object_version_number object version number
243 --
244 -- Post Success
245 -- Processing continues
246 --
247 -- Post Failure
248 -- Error raised.
249 --
250 -- Access Status
251 -- Internal table handler use only.
252 --
253 Procedure chk_transaction_category_id (p_txn_category_attribute_id in number,
254 p_transaction_category_id in number,
255 p_object_version_number in number) is
256 --
257 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
258 l_api_updating boolean;
259 l_dummy varchar2(1);
260 --
261 cursor c1 is
262 select null
263 from pqh_transaction_categories a
264 where a.transaction_category_id = p_transaction_category_id;
265 --
266 Begin
267 --
268 hr_utility.set_location('Entering:'||l_proc,5);
269 --
270 l_api_updating := pqh_tca_shd.api_updating
271 (p_txn_category_attribute_id => p_txn_category_attribute_id,
272 p_object_version_number => p_object_version_number);
273 --
274 if (l_api_updating
275 and nvl(p_transaction_category_id,hr_api.g_number)
276 <> nvl(pqh_tca_shd.g_old_rec.transaction_category_id,hr_api.g_number)
277 or not l_api_updating) then
278 --
279 -- check if transaction_category_id value exists in pqh_transaction_categories table
280 --
281 open c1;
282 --
283 fetch c1 into l_dummy;
284 if c1%notfound then
285 --
286 close c1;
287 --
288 -- raise error as FK does not relate to PK in pqh_transaction_categories
289 -- table.
290 --
291 pqh_tca_shd.constraint_error('PQH_TXN_CATEGORY_ATTRIBUTE_FK2');
292 --
293 end if;
294 --
295 close c1;
296 --
297 end if;
298 --
299 hr_utility.set_location('Leaving:'||l_proc,10);
300 --
301 End chk_transaction_category_id;
302 --
303 -- ----------------------------------------------------------------------------
304 -- |------< chk_attribute_id >------|
305 -- ----------------------------------------------------------------------------
306 --
307 -- Description
308 -- This procedure checks that a referenced foreign key actually exists
309 -- in the referenced table.
310 --
311 -- Pre-Conditions
312 -- None.
313 --
314 -- In Parameters
315 -- p_txn_category_attribute_id PK
316 -- p_attribute_id ID of FK column
317 -- p_object_version_number object version number
318 --
319 -- Post Success
320 -- Processing continues
321 --
322 -- Post Failure
323 -- Error raised.
324 --
325 -- Access Status
326 -- Internal table handler use only.
327 --
328 Procedure chk_attribute_id (p_txn_category_attribute_id in number,
329 p_attribute_id in number,
330 p_object_version_number in number) is
331 --
332 l_proc varchar2(72) := g_package||'chk_attribute_id';
333 l_api_updating boolean;
334 l_dummy varchar2(1);
335 --
336 cursor c1 is
337 select null
338 from pqh_attributes a
339 where a.attribute_id = p_attribute_id;
340 --
341 Begin
342 --
343 hr_utility.set_location('Entering:'||l_proc,5);
344 --
345 l_api_updating := pqh_tca_shd.api_updating
346 (p_txn_category_attribute_id => p_txn_category_attribute_id,
347 p_object_version_number => p_object_version_number);
348 --
349 if (l_api_updating
350 and nvl(p_attribute_id,hr_api.g_number)
351 <> nvl(pqh_tca_shd.g_old_rec.attribute_id,hr_api.g_number)
352 or not l_api_updating) then
353 --
354 -- check if attribute_id value exists in pqh_attributes table
355 --
356 open c1;
357 --
358 fetch c1 into l_dummy;
359 if c1%notfound then
360 --
361 close c1;
362 --
363 -- raise error as FK does not relate to PK in pqh_attributes
364 -- table.
365 --
366 pqh_tca_shd.constraint_error('PQH_TXN_CATEGORY_ATTRIBUTE_FK1');
367 --
368 end if;
369 --
370 close c1;
371 --
372 end if;
373 --
374 hr_utility.set_location('Leaving:'||l_proc,10);
375 --
376 End chk_attribute_id;
377 --
378 -- ----------------------------------------------------------------------------
379 -- |------< chk_value_style_cd >------|
380 -- ----------------------------------------------------------------------------
381 --
382 -- Description
383 -- This procedure is used to check that the lookup value is valid.
384 --
385 -- Pre Conditions
386 -- None.
387 --
388 -- In Parameters
389 -- txn_category_attribute_id PK of record being inserted or updated.
390 -- value_style_cd Value of lookup code.
391 -- effective_date effective date
392 -- object_version_number Object version number of record being
393 -- inserted or updated.
394 --
395 -- Post Success
396 -- Processing continues
397 --
398 -- Post Failure
399 -- Error handled by procedure
400 --
401 -- Access Status
402 -- Internal table handler use only.
403 --
404 Procedure chk_value_style_cd(p_txn_category_attribute_id in number,
405 p_value_style_cd in varchar2,
406 p_effective_date in date,
407 p_object_version_number in number) is
408 --
409 l_proc varchar2(72) := g_package||'chk_value_style_cd';
410 l_api_updating boolean;
411 --
412 Begin
413 --
414 hr_utility.set_location('Entering:'||l_proc, 5);
415 --
416 l_api_updating := pqh_tca_shd.api_updating
417 (p_txn_category_attribute_id => p_txn_category_attribute_id,
418 p_object_version_number => p_object_version_number);
419 --
420 if (l_api_updating
421 and p_value_style_cd
422 <> nvl(pqh_tca_shd.g_old_rec.value_style_cd,hr_api.g_varchar2)
423 or not l_api_updating)
424 and p_value_style_cd is not null then
425 --
426 -- check if value of lookup falls within lookup type.
427 --
428 if hr_api.not_exists_in_hr_lookups
429 (p_lookup_type => 'PQH_VALUE_STYLE',
430 p_lookup_code => p_value_style_cd,
431 p_effective_date => p_effective_date) then
432 --
433 -- raise error as does not exist as lookup
434 --
435 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
436 hr_utility.raise_error;
437 --
438 end if;
439 --
440 end if;
441 --
442 hr_utility.set_location('Leaving:'||l_proc,10);
443 --
444 end chk_value_style_cd;
445 --
446 -- ----------------------------------------------------------------------------
447 -- |------< chk_refresh_flag >------|
448 -- ----------------------------------------------------------------------------
449 --
450 -- Description
451 -- This procedure is used to check that the lookup value is valid.
452 --
453 -- Pre Conditions
454 -- None.
455 --
456 -- In Parameters
457 -- txn_category_attribute_id PK of record being inserted or updated.
458 -- refresh_flag Value of lookup code.
459 -- effective_date effective date
460 -- object_version_number Object version number of record being
461 -- inserted or updated.
462 --
463 -- Post Success
464 -- Processing continues
465 --
466 -- Post Failure
467 -- Error handled by procedure
468 --
469 -- Access Status
470 -- Internal table handler use only.
471 --
472 Procedure chk_refresh_flag(p_txn_category_attribute_id in number,
473 p_refresh_flag in varchar2,
474 p_effective_date in date,
475 p_object_version_number in number) is
476 --
477 l_proc varchar2(72) := g_package||'chk_refresh_flag';
478 l_api_updating boolean;
479 --
480 Begin
481 --
482 hr_utility.set_location('Entering:'||l_proc, 5);
483 --
484 l_api_updating := pqh_tca_shd.api_updating
485 (p_txn_category_attribute_id => p_txn_category_attribute_id,
486 p_object_version_number => p_object_version_number);
487 --
488 if (l_api_updating
489 and p_refresh_flag
490 <> nvl(pqh_tca_shd.g_old_rec.refresh_flag,hr_api.g_varchar2)
491 or not l_api_updating)
492 and p_refresh_flag is not null then
493 --
494 -- check if value of lookup falls within lookup type.
495 --
496 if hr_api.not_exists_in_hr_lookups
497 (p_lookup_type => 'YES_NO',
498 p_lookup_code => p_refresh_flag,
499 p_effective_date => p_effective_date) then
500 --
501 -- raise error as does not exist as lookup
502 --
503 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
504 hr_utility.raise_error;
505 --
506 end if;
507 --
508 end if;
509 --
510 hr_utility.set_location('Leaving:'||l_proc,10);
511 --
512 end chk_refresh_flag;
513 --
514 -- ----------------------------------------------------------------------------
515 -- |------< chk_select_flag >------|
516 -- ----------------------------------------------------------------------------
517 --
518 -- Description
519 -- This procedure is used to check that the lookup value is valid.
520 --
521 -- Pre Conditions
522 -- None.
523 --
524 -- In Parameters
525 -- txn_category_attribute_id PK of record being inserted or updated.
526 -- select_flag Value of lookup code.
527 -- effective_date effective date
528 -- object_version_number Object version number of record being
529 -- inserted or updated.
530 --
531 -- Post Success
532 -- Processing continues
533 --
534 -- Post Failure
535 -- Error handled by procedure
536 --
537 -- Access Status
538 -- Internal table handler use only.
539 --
540 Procedure chk_select_flag(p_txn_category_attribute_id in number,
541 p_select_flag in varchar2,
542 p_effective_date in date,
543 p_object_version_number in number) is
544 --
545 l_proc varchar2(72) := g_package||'chk_select_flag';
546 l_api_updating boolean;
547 --
548 Begin
549 --
550 hr_utility.set_location('Entering:'||l_proc, 5);
551 --
552 l_api_updating := pqh_tca_shd.api_updating
553 (p_txn_category_attribute_id => p_txn_category_attribute_id,
554 p_object_version_number => p_object_version_number);
555 --
556 if (l_api_updating
557 and p_select_flag
558 <> nvl(pqh_tca_shd.g_old_rec.select_flag,hr_api.g_varchar2)
559 or not l_api_updating)
560 and p_select_flag is not null then
561 --
562 -- check if value of lookup falls within lookup type.
563 --
564 if hr_api.not_exists_in_hr_lookups
565 (p_lookup_type => 'YES_NO',
566 p_lookup_code => p_select_flag,
567 p_effective_date => p_effective_date) then
568 --
569 -- raise error as does not exist as lookup
570 --
571 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
572 hr_utility.raise_error;
573 --
574 end if;
575 --
576 end if;
577 --
578 hr_utility.set_location('Leaving:'||l_proc,10);
579 --
580 end chk_select_flag;
581 --
582 -- ----------------------------------------------------------------------------
583 -- |------< chk_member_identifying_flag >------|
584 -- ----------------------------------------------------------------------------
585 --
586 -- Description
587 -- This procedure is used to check that the lookup value is valid.
588 --
589 -- Pre Conditions
590 -- None.
591 --
592 -- In Parameters
593 -- txn_category_attribute_id PK of record being inserted or updated.
594 -- member_identifying_flag Value of lookup code.
595 -- effective_date effective date
596 -- object_version_number Object version number of record being
597 -- inserted or updated.
598 --
599 -- Post Success
600 -- Processing continues
601 --
602 -- Post Failure
603 -- Error handled by procedure
604 --
605 -- Access Status
606 -- Internal table handler use only.
607 --
608 Procedure chk_member_identifying_flag(
609 p_txn_category_attribute_id in number,
610 p_attribute_id in number,
611 p_identifier_flag in varchar2,
612 p_delete_attr_ranges_flag in varchar2,
613 p_member_identifying_flag in varchar2,
614 p_effective_date in date,
615 p_object_version_number in number) is
616 --
617 l_proc varchar2(72) := g_package||'chk_member_identifying_flag';
618 l_api_updating boolean;
619 --
620 Begin
621 --
622 hr_utility.set_location('Entering:'||l_proc, 5);
623 --
624 l_api_updating := pqh_tca_shd.api_updating
625 (p_txn_category_attribute_id => p_txn_category_attribute_id,
626 p_object_version_number => p_object_version_number);
627 --
628 --
629 -- Error if child rows exists in pqh_attribute_ranges
630 --
631 if (l_api_updating
632 and nvl(p_member_identifying_flag,hr_api.g_varchar2)
633 <> nvl(pqh_tca_shd.g_old_rec.member_identifying_flag,hr_api.g_varchar2))
634 then
635 --
636 if p_member_identifying_flag = 'N' then
637 --
638 --
639 pqh_ATTRIBUTE_RANGES_pkg.Delete_attribute_ranges
640 (p_attribute_id => p_attribute_id,
641 p_delete_attr_ranges_flag => p_delete_attr_ranges_flag ,
642 p_primary_flag => 'N');
643 --
644 --
645 end if;
646 --
647 end if;
648 --
649
650 if (l_api_updating
651 and p_member_identifying_flag
652 <> nvl(pqh_tca_shd.g_old_rec.member_identifying_flag,hr_api.g_varchar2)
653 or not l_api_updating)
654 and p_member_identifying_flag is not null then
655 --
656 -- check if value of lookup falls within lookup type.
657 --
658 if hr_api.not_exists_in_hr_lookups
659 (p_lookup_type => 'YES_NO',
660 p_lookup_code => p_member_identifying_flag,
661 p_effective_date => p_effective_date) then
662 --
663 -- raise error as does not exist as lookup
664 --
665 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
666 hr_utility.raise_error;
667 --
668 end if;
669 --
670 --
671 -- CHECK if identifier_flag is Y
672 -- member_identifying_flag can be Y only if identifier_flag is Y
673 --
674 if nvl(p_identifier_flag,hr_api.g_varchar2) <> 'Y' and p_member_identifying_flag = 'Y' then
675 --
676 -- raise error
677 --
678 hr_utility.set_message(8302,'PQH_INVALID_MEMBER_IDENTIFIER');
679 hr_utility.raise_error;
680 --
681 end if;
682 --
683
684 end if;
685 --
686 hr_utility.set_location('Leaving:'||l_proc,10);
687 --
688 end chk_member_identifying_flag;
689 --
690 -- ----------------------------------------------------------------------------
691 -- |------< chk_list_identifying_flag >------|
692 -- ----------------------------------------------------------------------------
693 --
694 -- Description
695 -- This procedure is used to check that the lookup value is valid.
696 --
697 -- Pre Conditions
698 -- None.
699 --
700 -- In Parameters
701 -- txn_category_attribute_id PK of record being inserted or updated.
702 -- list_identifying_flag Value of lookup code.
703 -- effective_date effective date
704 -- object_version_number Object version number of record being
705 -- inserted or updated.
706 --
707 -- Post Success
708 -- Processing continues
709 --
710 -- Post Failure
711 -- Error handled by procedure
712 --
713 -- Access Status
714 -- Internal table handler use only.
715 --
716 Procedure chk_list_identifying_flag(
717 p_txn_category_attribute_id in number,
718 p_attribute_id in number,
719 p_identifier_flag in varchar2,
720 p_delete_attr_ranges_flag in varchar2,
721 p_list_identifying_flag in varchar2,
722 p_effective_date in date,
723 p_object_version_number in number) is
724 --
725 l_proc varchar2(72) := g_package||'chk_list_identifying_flag';
726 l_api_updating boolean;
727 --
728 Begin
729 --
730 hr_utility.set_location('Entering:'||l_proc, 5);
731 --
732 l_api_updating := pqh_tca_shd.api_updating
733 (p_txn_category_attribute_id => p_txn_category_attribute_id,
734 p_object_version_number => p_object_version_number);
735 --
736 --
737 -- Error if records exist in pqh_attribute_ranges for this attribute_id
738 --
739 if (l_api_updating
740 and nvl(p_list_identifying_flag,hr_api.g_varchar2)
741 <> nvl(pqh_tca_shd.g_old_rec.list_identifying_flag,hr_api.g_varchar2)) then
742 --
743 if p_list_identifying_flag = 'N' then
744 --
745 pqh_ATTRIBUTE_RANGES_pkg.Delete_attribute_ranges
746 (p_attribute_id => p_attribute_id,
747 p_delete_attr_ranges_flag => p_delete_attr_ranges_flag ,
748 p_primary_flag => 'Y');
749 end if;
750 --
751
752 end if;
753 --
754
755 if (l_api_updating
756 and p_list_identifying_flag
757 <> nvl(pqh_tca_shd.g_old_rec.list_identifying_flag,hr_api.g_varchar2)
758 or not l_api_updating)
759 and p_list_identifying_flag is not null then
760 --
761 -- check if value of lookup falls within lookup type.
762 --
763 if hr_api.not_exists_in_hr_lookups
764 (p_lookup_type => 'YES_NO',
765 p_lookup_code => p_list_identifying_flag,
766 p_effective_date => p_effective_date) then
767 --
768 -- raise error as does not exist as lookup
769 --
770 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
771 hr_utility.raise_error;
772 --
773 end if;
774 --
775 --
776 -- CHECK if identifier_flag is Y
777 -- list_identifying_flag can be Y only if identifier_flag is Y
778 --
779 if nvl(p_identifier_flag,hr_api.g_varchar2) <> 'Y' and p_list_identifying_flag = 'Y' then
780 --
781 -- raise error
782 --
783 hr_utility.set_message(8302,'PQH_INVALID_LIST_IDENTIFIER');
784 hr_utility.raise_error;
785 --
786 end if;
787 --
788
789 end if;
790 --
791 hr_utility.set_location('Leaving:'||l_proc,10);
792 --
793 end chk_list_identifying_flag;
794 --
795 -- ----------------------------------------------------------------------------
796 -- |------< chk_identifier_flag >------|
797 -- ----------------------------------------------------------------------------
798 --
799 -- Description
800 -- This procedure is used to check that the lookup value is valid.
801 --
802 -- Pre Conditions
803 -- None.
804 --
805 -- In Parameters
806 -- txn_category_attribute_id PK of record being inserted or updated.
807 -- identifier_flag Value of lookup code.
808 -- effective_date effective date
809 -- object_version_number Object version number of record being
810 -- inserted or updated.
811 --
812 -- Post Success
813 -- Processing continues
814 --
815 -- Post Failure
816 -- Error handled by procedure
817 --
818 -- Access Status
819 -- Internal table handler use only.
820 --
821 Procedure chk_identifier_flag(p_txn_category_attribute_id in number,
822 p_identifier_flag in varchar2,
823 p_effective_date in date,
824 p_object_version_number in number) is
825 --
826 l_proc varchar2(72) := g_package||'chk_identifier_flag';
827 l_api_updating boolean;
828 --
829 Begin
830 --
831 hr_utility.set_location('Entering:'||l_proc, 5);
832 --
833 l_api_updating := pqh_tca_shd.api_updating
834 (p_txn_category_attribute_id => p_txn_category_attribute_id,
835 p_object_version_number => p_object_version_number);
836 --
837 if (l_api_updating
838 and p_identifier_flag
839 <> nvl(pqh_tca_shd.g_old_rec.identifier_flag,hr_api.g_varchar2)
840 or not l_api_updating)
841 and p_identifier_flag is not null then
842 --
843 -- check if value of lookup falls within lookup type.
844 --
845 if hr_api.not_exists_in_hr_lookups
846 (p_lookup_type => 'YES_NO',
847 p_lookup_code => p_identifier_flag,
848 p_effective_date => p_effective_date) then
849 --
850 -- raise error as does not exist as lookup
851 --
852 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
853 hr_utility.raise_error;
854 --
855 end if;
856 --
857 end if;
858 --
859 hr_utility.set_location('Leaving:'||l_proc,10);
860 --
861 end chk_identifier_flag;
862 --
863 ------------------------------------------------------------------------------
864 -- Additional checks
865 ------------------------------------------------------------------------------
866 --
867 -- ----------------------------------------------------------------------------
868 -- |------< chk_transaction_category_id >------|
869 -- ----------------------------------------------------------------------------
870 --
871 -- Description
872 -- This procedure checks that no attributes are added once its
873 -- associated transaction_category_id is frozen
874 --
875 -- Pre-Conditions
876 -- None.
877 --
878 -- In Parameters
879 -- p_attribute_id PK
880 -- p_object_version_number object version number
881 --
882 -- Post Success
883 -- Processing continues
884 --
885 -- Post Failure
886 -- Error raised.
887 --
888 -- Access Status
889 -- Internal table handler use only.
890 --
891 Procedure chk_transaction_cat_status (p_attribute_id in number,
892 p_object_version_number in number) is
893 --
894 l_proc varchar2(72) := g_package||'chk_transaction_cat_status';
895 l_api_updating boolean;
896 l_freeze_status_cd pqh_transaction_categories.freeze_status_cd%type;
897 --
898 cursor c1 is
899 select nvl(b.freeze_status_cd,hr_api.g_varchar2)
900 from pqh_txn_category_attributes a, pqh_transaction_categories b
901 where a.attribute_id = p_attribute_id
902 AND a.transaction_category_id = b.transaction_category_id;
903 --
904 --
905 Begin
906 --
907 hr_utility.set_location('Entering:'||l_proc,5);
908 --
909 --
910 if l_freeze_status_cd = 'FREEZE_ATTRIBUTES'
911 OR l_freeze_status_cd = 'FREEZE_CATEGORY' then
912 hr_utility.set_message(8302,'PQH_CANNOT_MODIFY_TXN_CAT_ATTR');
913 hr_utility.raise_error;
914 End if;
915
916 hr_utility.set_location('Leaving:'||l_proc,10);
917 --
918 End chk_transaction_cat_status;
919 --
920
921 -- ----------------------------------------------------------------------------
922 -- |---------------------------< insert_validate >----------------------------|
923 -- ----------------------------------------------------------------------------
924 Procedure insert_validate(p_rec in pqh_tca_shd.g_rec_type
925 ,p_effective_date in date) is
926 p_delete_attr_ranges_flag varchar2(10) := 'N';
927 --
928 l_proc varchar2(72) := g_package||'insert_validate';
929
930 --
931 Begin
932 hr_utility.set_location('Entering:'||l_proc, 5);
933 --
934 -- Call all supporting business operations
935 --
936 chk_txn_category_attribute_id
937 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
938 p_object_version_number => p_rec.object_version_number);
939 --
940 chk_transaction_table_route_id
941 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
942 p_transaction_table_route_id => p_rec.transaction_table_route_id,
943 p_object_version_number => p_rec.object_version_number);
944 --
945 chk_value_set_id
946 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
947 p_value_set_id => p_rec.value_set_id,
948 p_object_version_number => p_rec.object_version_number);
949 --
950 chk_transaction_category_id
951 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
952 p_transaction_category_id => p_rec.transaction_category_id,
953 p_object_version_number => p_rec.object_version_number);
954 --
955 chk_attribute_id
956 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
957 p_attribute_id => p_rec.attribute_id,
958 p_object_version_number => p_rec.object_version_number);
959 --
960 chk_value_style_cd
961 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
962 p_value_style_cd => p_rec.value_style_cd,
963 p_effective_date => p_effective_date,
964 p_object_version_number => p_rec.object_version_number);
965 --
966 chk_refresh_flag
967 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
968 p_refresh_flag => p_rec.refresh_flag,
969 p_effective_date => p_effective_date,
970 p_object_version_number => p_rec.object_version_number);
971 --
972 chk_select_flag
973 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
974 p_select_flag => p_rec.select_flag,
975 p_effective_date => p_effective_date,
976 p_object_version_number => p_rec.object_version_number);
977 --
978 chk_member_identifying_flag
979 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
980 p_attribute_id => p_rec.attribute_id,
981 p_identifier_flag => p_rec.identifier_flag,
982 p_delete_attr_ranges_flag => p_delete_attr_ranges_flag,
983 p_member_identifying_flag => p_rec.member_identifying_flag,
984 p_effective_date => p_effective_date,
985 p_object_version_number => p_rec.object_version_number);
986 --
987 chk_list_identifying_flag
988 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
989 p_attribute_id => p_rec.attribute_id,
990 p_identifier_flag => p_rec.identifier_flag,
991 p_delete_attr_ranges_flag => p_delete_attr_ranges_flag,
992 p_list_identifying_flag => p_rec.list_identifying_flag,
993 p_effective_date => p_effective_date,
994 p_object_version_number => p_rec.object_version_number);
995 --
996 chk_identifier_flag
997 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
998 p_identifier_flag => p_rec.identifier_flag,
999 p_effective_date => p_effective_date,
1000 p_object_version_number => p_rec.object_version_number);
1001 --
1002 chk_transaction_cat_status
1003 (p_attribute_id => p_rec.attribute_id,
1004 p_object_version_number => p_rec.object_version_number);
1005 --
1006 --
1007 --
1008 hr_utility.set_location(' Leaving:'||l_proc, 10);
1009 End insert_validate;
1010 --
1011 -- ----------------------------------------------------------------------------
1012 -- |---------------------------< update_validate >----------------------------|
1013 -- ----------------------------------------------------------------------------
1014 Procedure update_validate(p_rec in pqh_tca_shd.g_rec_type
1015 ,p_effective_date in date
1016 ,p_delete_attr_ranges_flag in varchar2) is
1017 --
1018 l_proc varchar2(72) := g_package||'update_validate';
1019 --
1020 Begin
1021 hr_utility.set_location('Entering:'||l_proc, 5);
1022 --
1023 -- Call all supporting business operations
1024 --
1025 chk_txn_category_attribute_id
1026 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1027 p_object_version_number => p_rec.object_version_number);
1028 --
1029 chk_transaction_table_route_id
1030 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1031 p_transaction_table_route_id => p_rec.transaction_table_route_id,
1032 p_object_version_number => p_rec.object_version_number);
1033 --
1034 chk_value_set_id
1035 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1036 p_value_set_id => p_rec.value_set_id,
1037 p_object_version_number => p_rec.object_version_number);
1038 --
1039 chk_transaction_category_id
1040 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1041 p_transaction_category_id => p_rec.transaction_category_id,
1042 p_object_version_number => p_rec.object_version_number);
1043 --
1044 chk_attribute_id
1045 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1046 p_attribute_id => p_rec.attribute_id,
1047 p_object_version_number => p_rec.object_version_number);
1048 --
1049 chk_value_style_cd
1050 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1051 p_value_style_cd => p_rec.value_style_cd,
1052 p_effective_date => p_effective_date,
1053 p_object_version_number => p_rec.object_version_number);
1054 --
1055 chk_refresh_flag
1056 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1057 p_refresh_flag => p_rec.refresh_flag,
1058 p_effective_date => p_effective_date,
1059 p_object_version_number => p_rec.object_version_number);
1060 --
1061 chk_select_flag
1062 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1063 p_select_flag => p_rec.select_flag,
1064 p_effective_date => p_effective_date,
1065 p_object_version_number => p_rec.object_version_number);
1066 --
1067 chk_member_identifying_flag
1068 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1069 p_attribute_id => p_rec.attribute_id,
1070 p_identifier_flag => p_rec.identifier_flag,
1071 p_delete_attr_ranges_flag => p_delete_attr_ranges_flag,
1072 p_member_identifying_flag => p_rec.member_identifying_flag,
1073 p_effective_date => p_effective_date,
1074 p_object_version_number => p_rec.object_version_number);
1075 --
1076 chk_list_identifying_flag
1077 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1078 p_attribute_id => p_rec.attribute_id,
1079 p_identifier_flag => p_rec.identifier_flag,
1080 p_delete_attr_ranges_flag => p_delete_attr_ranges_flag,
1081 p_list_identifying_flag => p_rec.list_identifying_flag,
1082 p_effective_date => p_effective_date,
1083 p_object_version_number => p_rec.object_version_number);
1084 --
1085 chk_identifier_flag
1086 (p_txn_category_attribute_id => p_rec.txn_category_attribute_id,
1087 p_identifier_flag => p_rec.identifier_flag,
1088 p_effective_date => p_effective_date,
1089 p_object_version_number => p_rec.object_version_number);
1090 --
1091 chk_transaction_cat_status
1092 (p_attribute_id => p_rec.attribute_id,
1093 p_object_version_number => p_rec.object_version_number);
1094 --
1095 --
1096 hr_utility.set_location(' Leaving:'||l_proc, 10);
1097 End update_validate;
1098 --
1099 -- ----------------------------------------------------------------------------
1100 -- |---------------------------< delete_validate >----------------------------|
1101 -- ----------------------------------------------------------------------------
1102 Procedure delete_validate(p_rec in pqh_tca_shd.g_rec_type
1103 ,p_effective_date in date) is
1104 --
1105 l_proc varchar2(72) := g_package||'delete_validate';
1106 --
1107 Begin
1108 hr_utility.set_location('Entering:'||l_proc, 5);
1109 --
1110 -- Call all supporting business operations
1111 --
1112 hr_utility.set_location(' Leaving:'||l_proc, 10);
1113 End delete_validate;
1114 --
1115 end pqh_tca_bus;