[Home] [Help]
PACKAGE BODY: APPS.PQH_RTM_BUS
Source
1 Package Body pqh_rtm_bus as
2 /* $Header: pqrtmrhi.pkb 120.2 2006/01/05 15:29:54 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rtm_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_role_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 -- role_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_role_template_id(p_role_template_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_role_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_rtm_shd.api_updating
47 (p_role_template_id => p_role_template_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_role_template_id,hr_api.g_number)
52 <> pqh_rtm_shd.g_old_rec.role_template_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_rtm_shd.constraint_error('RTM_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_role_template_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_rtm_shd.constraint_error('RTM_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_role_template_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_role_template_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_role_template_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 --
109 cursor c1 is
110 select null
111 from pqh_templates a
112 where a.template_id = p_template_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_rtm_shd.api_updating
119 (p_role_template_id => p_role_template_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_template_id,hr_api.g_number)
124 <> nvl(pqh_rtm_shd.g_old_rec.template_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_template_id is not null 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 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in pqh_templates
138 -- table.
139 --
140 pqh_rtm_shd.constraint_error('PQH_ROLE_TEMPLATES_FK3');
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_template_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_transaction_category_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_role_template_id PK
165 -- p_transaction_category_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_transaction_category_id (p_role_template_id in number,
178 p_transaction_category_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_transaction_categories a
188 where a.transaction_category_id = p_transaction_category_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_rtm_shd.api_updating
195 (p_role_template_id => p_role_template_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_transaction_category_id,hr_api.g_number)
200 <> nvl(pqh_rtm_shd.g_old_rec.transaction_category_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if transaction_category_id value exists in pqh_transaction_categories table
204 --
205 open c1;
206 --
207 fetch c1 into l_dummy;
208 if c1%notfound then
209 --
210 close c1;
211 --
212 -- raise error as FK does not relate to PK in pqh_transaction_categories
213 -- table.
214 --
215 pqh_rtm_shd.constraint_error('PQH_ROLE_TEMPLATES_FK2');
216 --
217 end if;
218 --
219 close c1;
220 --
221 end if;
222 --
223 hr_utility.set_location('Leaving:'||l_proc,10);
224 --
225 End chk_transaction_category_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |------< chk_role_id >------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description
232 -- This procedure checks that a referenced foreign key actually exists
233 -- in the referenced table.
234 --
235 -- Pre-Conditions
236 -- None.
237 --
238 -- In Parameters
239 -- p_role_template_id PK
240 -- p_role_id ID of FK column
241 -- p_object_version_number object version number
242 --
243 -- Post Success
244 -- Processing continues
245 --
246 -- Post Failure
247 -- Error raised.
248 --
249 -- Access Status
250 -- Internal table handler use only.
251 --
252 Procedure chk_role_id (p_role_template_id in number,
253 p_role_id in number,
254 p_object_version_number in number) is
255 --
256 l_proc varchar2(72) := g_package||'chk_role_id';
257 l_api_updating boolean;
258 l_dummy varchar2(1);
259 --
260 cursor c1 is
261 select null
262 from pqh_roles a
263 where a.role_id = p_role_id;
264 --
265 Begin
266 --
267 hr_utility.set_location('Entering:'||l_proc,5);
268 --
269 l_api_updating := pqh_rtm_shd.api_updating
270 (p_role_template_id => p_role_template_id,
271 p_object_version_number => p_object_version_number);
272 --
273 if (l_api_updating
274 and nvl(p_role_id,hr_api.g_number)
275 <> nvl(pqh_rtm_shd.g_old_rec.role_id,hr_api.g_number)
276 or not l_api_updating) then
277 --
278 -- check if role_id value exists in pqh_roles table
279 --
280 open c1;
281 --
282 fetch c1 into l_dummy;
283 if c1%notfound then
284 --
285 close c1;
286 --
287 -- raise error as FK does not relate to PK in pqh_roles
288 -- table.
289 --
290 pqh_rtm_shd.constraint_error('PQH_ROLE_TEMPLATES_FK1');
291 --
292 end if;
293 --
294 close c1;
295 --
296 end if;
297 --
298 hr_utility.set_location('Leaving:'||l_proc,10);
299 --
300 End chk_role_id;
301 --
302 -- ----------------------------------------------------------------------------
303 -- |------< chk_enable_flag >------|
304 -- ----------------------------------------------------------------------------
305 --
306 -- Description
307 -- This procedure is used to check that the lookup value is valid.
308 --
309 -- Pre Conditions
310 -- None.
311 --
312 -- In Parameters
313 -- role_template_id PK of record being inserted or updated.
314 -- enable_flag Value of lookup code.
315 -- effective_date effective date
316 -- object_version_number Object version number of record being
317 -- inserted or updated.
318 --
319 -- Post Success
320 -- Processing continues
321 --
322 -- Post Failure
323 -- Error handled by procedure
324 --
325 -- Access Status
326 -- Internal table handler use only.
327 --
328 Procedure chk_enable_flag(p_role_template_id in number,
329 p_role_id in number,
330 p_enable_flag in varchar2,
331 p_effective_date in date,
332 p_object_version_number in number) is
333 --
334 l_proc varchar2(72) := g_package||'chk_enable_flag';
335 l_api_updating boolean;
336 l_role_enable_flag varchar2(10);
337 --
338 cursor c_role_enable_flag(p_role_id number) is
339 select enable_flag
340 from pqh_roles
341 where role_id=p_role_id;
342 Begin
343 --
344 hr_utility.set_location('Entering:'||l_proc, 5);
345 --
346 l_api_updating := pqh_rtm_shd.api_updating
347 (p_role_template_id => p_role_template_id,
348 p_object_version_number => p_object_version_number);
349 --
350 if (l_api_updating
351 and p_enable_flag
352 <> nvl(pqh_rtm_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
353 or not l_api_updating) then
354 --
355 -- check if value of lookup falls within lookup type.
356 --
357 --
358 if hr_api.not_exists_in_hr_lookups
359 (p_lookup_type => 'YES_NO',
360 p_lookup_code => p_enable_flag,
361 p_effective_date => p_effective_date) then
362 --
363 -- raise error as does not exist as lookup
364 --
365 hr_utility.set_message(8302,'PQH_ENABLE_LOOKUP_DOES_NOT_EXIST');
366 hr_utility.raise_error;
367 --
368 if p_enable_flag = 'Y' then
369 open c_role_enable_flag(p_role_id);
370 fetch c_role_enable_flag into l_role_enable_flag;
371 close c_role_enable_flag;
372 if nvl(l_role_enable_flag,'N') = 'N' then
373 hr_utility.set_message(8302,'PQH_ROLE_DIS_CANT_ENABLE_RTM');
374 hr_utility.raise_error;
375 end if;
376 end if;
377 end if;
378 --
379 end if;
380 --
381 hr_utility.set_location('Leaving:'||l_proc,10);
382 --
383 end chk_enable_flag;
384 --
385 --
386 -- ---------------------------------------------------------------------------
387 -- |----------------------< chk_for_pending_txns >---------------------------|
388 -- ---------------------------------------------------------------------------
389 --
390 --
391 Function chk_for_pending_txns(p_role_id in number,
392 p_txn_cat out nocopy varchar2,
393 p_bg out nocopy varchar2)
394 return varchar2
395 is
396 --
397 l_proc varchar2(72) := g_package||'chk_for_pending_txns';
398 l_api_updating boolean;
399 l_bus_grp_name varchar2(240);
400 --
401 cursor c_txn_cats(p_role_id number) is
402 select distinct ptc.transaction_category_id, ptc.name transaction_category,
403 ptc.business_group_id
404 from pqh_routing_list_members rlm, pqh_routing_categories rct,
405 pqh_transaction_categories ptc
406 where rlm.routing_list_id = rct.routing_list_id
407 and rlm.role_id = p_role_id
408 and rct.transaction_category_id=ptc.transaction_category_id;
409 --
410 Begin
411 --
412 hr_utility.set_location('Entering:'||l_proc, 5);
413 --
414 for r_txn_cat in c_txn_cats(p_role_id)
415 loop
416 --
417 if nvl(pqh_tct_bus.chk_active_transaction_exists(r_txn_cat.transaction_category_id),'N')
418 = 'Y' then
419 --
420 p_txn_cat := r_txn_cat.transaction_category;
421 if (r_txn_cat.business_group_id is not null) then
422 l_bus_grp_name := hr_general.DECODE_ORGANIZATION(r_txn_cat.business_group_id);
423 else
424 l_bus_grp_name := hr_general.decode_lookup('PQH_TCT_SCOPE', 'GLOBAL');
425 end if;
426 --
427 p_bg := l_bus_grp_name;
428 --
429 return 'Y';
430 end if;
431 --
432 end loop;
433 --
434 hr_utility.set_location('Leaving:'||l_proc,10);
435 --
436 return 'N';
437 end chk_for_pending_txns;
438 --
439 --
440 -- ----------------------------------------------------------------------------
441 -- |------< chk_category_template_id >------|
445 -- This procedure checks that a referenced foreign key actually exists
442 -- ----------------------------------------------------------------------------
443 --
444 -- Description
446 -- in the referenced table.
447 --
448 -- Pre-Conditions
449 -- None.
450 --
451 -- In Parameters
452 -- p_role_template_id PK
453 -- p_template_id ID of FK column
454 -- p_object_version_number object version number
455 --
456 -- Post Success
457 -- Processing continues
458 --
459 -- Post Failure
460 -- Error raised.
461 --
462 -- Access Status
463 -- Internal table handler use only.
464 --
465 Procedure chk_category_template_id (p_role_template_id in number,
466 p_transaction_category_id in number,
467 p_template_id in number,
468 p_object_version_number in number) is
469 --
470 l_proc varchar2(72) := g_package||'chk_category_template_id';
471 l_api_updating boolean;
472 l_dummy varchar2(1);
473 --
474 cursor c1 is
475 select null
476 from pqh_templates a
477 where a.transaction_category_id=p_transaction_category_id
478 and a.template_id = p_template_id;
479 --
480 Begin
481 --
482 hr_utility.set_location('Entering:'||l_proc,5);
483 --
484 l_api_updating := pqh_rtm_shd.api_updating
485 (p_role_template_id => p_role_template_id,
486 p_object_version_number => p_object_version_number);
487 --
488 if (l_api_updating
489 and (nvl(p_transaction_category_id,hr_api.g_number)
490 <> nvl(pqh_rtm_shd.g_old_rec.transaction_category_id,hr_api.g_number)
491 or nvl(p_template_id,hr_api.g_number)
492 <> nvl(pqh_rtm_shd.g_old_rec.template_id,hr_api.g_number) )
493 or not l_api_updating) and
494 (p_transaction_category_id is not null
495 or p_template_id is not null) then
496 --
497 -- check if transaction_category_id and template_id value exists in pqh_templates table
498 --
499 open c1;
500 --
501 fetch c1 into l_dummy;
502 if c1%notfound then
503 --
504 close c1;
505 --
506 -- raise error as FK does not relate to PK in pqh_templates
507 -- table.
508 --
509 pqh_rtm_shd.constraint_error('RTM_TCT_TEM_FK');
510 --
511 end if;
512 --
513 close c1;
514 --
515 end if;
516 --
517 hr_utility.set_location('Leaving:'||l_proc,10);
518 --
519 End chk_category_template_id;
520 --
521 --
522 ----------------------------------------------------------------------------
523 -- |------< chk_non_updateable_args >------|
524 -- ----------------------------------------------------------------------------
525 --
526 -- Description
527 -- This procedure checks that a referenced foreign key actually exists
528 -- in the referenced table.
529 --
530 -- Pre-Conditions
531 -- None.
532 --
533 -- In Parameters
534 -- p_rec pqh_rtm_asd.g_rec_type
535 --
536 -- Post Success
537 -- Processing continues
538 --
539 -- Post Failure
540 -- Error raised.
541 --
542 -- Access Status
543 -- Internal table handler use only.
544 --
545 Procedure chk_non_updateable_args (p_rec in pqh_rtm_shd.g_rec_type ) is
546 --
547 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
548 l_api_updating boolean;
549 l_error exception;
550 l_argument varchar2(30);
551 l_dummy varchar2(1);
552 --
553 Begin
554 --
555 hr_utility.set_location('Entering:'||l_proc,5);
556 --
557 l_api_updating := pqh_rtm_shd.api_updating
558 (p_role_template_id => p_rec.role_template_id,
559 p_object_version_number => p_rec.object_version_number);
560 --
561 if (not l_api_updating ) then
562 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
563 hr_utility.set_message('PROCEDURE', l_proc);
564 hr_utility.set_message('STEP', '10');
565 end if;
566 --
567 hr_utility.set_location('Leaving:'||l_proc,20);
568
569 if nvl(p_rec.transaction_category_id,hr_api.g_number) <>
570 nvl(pqh_rtm_shd.g_old_rec.transaction_category_id,hr_api.g_number) then
571 l_argument := 'transaction_category_id';
572 raise l_error;
573 end if;
574 hr_utility.set_location(l_proc,30);
575 exception
576 when l_error then
577 hr_api.argument_changed_error
578 (p_api_name => l_proc
579 ,p_argument => l_argument
580 );
581 when others then
582 raise;
583 hr_utility.set_location(' Leaving:'||l_proc,50);
584 --
585 End chk_non_updateable_args;
586 --
587 --
588 -- ----------------------------------------------------------------------------
589 -- |---------------------------< insert_validate >----------------------------|
590 -- ----------------------------------------------------------------------------
591 Procedure insert_validate(p_rec in pqh_rtm_shd.g_rec_type
592 ,p_effective_date in date) is
593 --
594 l_proc varchar2(72) := g_package||'insert_validate';
595 --
596 Begin
597 hr_utility.set_location('Entering:'||l_proc, 5);
598 --
599 -- Call all supporting business operations
600 --
601 chk_role_template_id
602 (p_role_template_id => p_rec.role_template_id,
603 p_object_version_number => p_rec.object_version_number);
604 --
605 chk_template_id
606 (p_role_template_id => p_rec.role_template_id,
607 p_template_id => p_rec.template_id,
608 p_object_version_number => p_rec.object_version_number);
609 --
610 chk_transaction_category_id
611 (p_role_template_id => p_rec.role_template_id,
612 p_transaction_category_id => p_rec.transaction_category_id,
613 p_object_version_number => p_rec.object_version_number);
614 --
615 chk_category_template_id
616 (p_role_template_id => p_rec.role_template_id,
617 p_transaction_category_id => p_rec.transaction_category_id,
618 p_template_id => p_rec.template_id,
619 p_object_version_number => p_rec.object_version_number);
620 --
621 chk_role_id
622 (p_role_template_id => p_rec.role_template_id,
623 p_role_id => p_rec.role_id,
624 p_object_version_number => p_rec.object_version_number);
625 --
626 chk_enable_flag
627 (p_role_template_id => p_rec.role_template_id,
628 p_role_id => p_rec.role_id,
629 p_enable_flag => p_rec.enable_flag,
630 p_effective_date => p_effective_date,
631 p_object_version_number => p_rec.object_version_number);
632 --
633 --
634 --
635 hr_utility.set_location(' Leaving:'||l_proc, 10);
636 End insert_validate;
637 --
638 -- ----------------------------------------------------------------------------
639 -- |---------------------------< update_validate >----------------------------|
640 -- ----------------------------------------------------------------------------
641 Procedure update_validate(p_rec in pqh_rtm_shd.g_rec_type
642 ,p_effective_date in date) is
643 --
644 l_proc varchar2(72) := g_package||'update_validate';
645 --
646 Begin
647 hr_utility.set_location('Entering:'||l_proc, 5);
648 --
649 -- Call all supporting business operations
650 --
651 chk_non_updateable_args(p_rec);
652 --
653 chk_role_template_id
654 (p_role_template_id => p_rec.role_template_id,
655 p_object_version_number => p_rec.object_version_number);
656 --
657 chk_template_id
658 (p_role_template_id => p_rec.role_template_id,
659 p_template_id => p_rec.template_id,
660 p_object_version_number => p_rec.object_version_number);
661 --
662 chk_transaction_category_id
663 (p_role_template_id => p_rec.role_template_id,
664 p_transaction_category_id => p_rec.transaction_category_id,
665 p_object_version_number => p_rec.object_version_number);
666 --
667 chk_category_template_id
668 (p_role_template_id => p_rec.role_template_id,
669 p_transaction_category_id => p_rec.transaction_category_id,
670 p_template_id => p_rec.template_id,
671 p_object_version_number => p_rec.object_version_number);
672 --
673 chk_role_id
674 (p_role_template_id => p_rec.role_template_id,
675 p_role_id => p_rec.role_id,
676 p_object_version_number => p_rec.object_version_number);
677 --
678 chk_enable_flag
679 (p_role_template_id => p_rec.role_template_id,
680 p_role_id => p_rec.role_id,
681 p_enable_flag => p_rec.enable_flag,
682 p_effective_date => p_effective_date,
683 p_object_version_number => p_rec.object_version_number);
684 --
685 --
686 hr_utility.set_location(' Leaving:'||l_proc, 10);
687 End update_validate;
688 --
689 -- ----------------------------------------------------------------------------
690 -- |---------------------------< delete_validate >----------------------------|
691 -- ----------------------------------------------------------------------------
692 Procedure delete_validate(p_rec in pqh_rtm_shd.g_rec_type
693 ,p_effective_date in date) is
694 --
695 l_proc varchar2(72) := g_package||'delete_validate';
696 --
697 Begin
698 hr_utility.set_location('Entering:'||l_proc, 5);
699 --
700 -- Call all supporting business operations
701 --
702 hr_utility.set_location(' Leaving:'||l_proc, 10);
703 End delete_validate;
704 --
705 end pqh_rtm_bus;