[Home] [Help]
PACKAGE BODY: APPS.PQH_BDT_BUS
Source
1 Package Body pqh_bdt_bus as
2 /* $Header: pqbdtrhi.pkb 120.0 2005/05/29 01:28:31 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bdt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_budget_detail_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 -- budget_detail_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_budget_detail_id(p_budget_detail_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_budget_detail_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_bdt_shd.api_updating
47 (p_budget_detail_id => p_budget_detail_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_budget_detail_id,hr_api.g_number)
52 <> pqh_bdt_shd.g_old_rec.budget_detail_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_bdt_shd.constraint_error('PQH_BUDGET_DETAILS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_budget_detail_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_bdt_shd.constraint_error('PQH_BUDGET_DETAILS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_budget_detail_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_grade_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_budget_detail_id PK
89 -- p_grade_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_grade_id (p_budget_detail_id in number,
102 p_grade_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_grade_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from per_grades a
112 where a.grade_id = p_grade_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_bdt_shd.api_updating
119 (p_budget_detail_id => p_budget_detail_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_grade_id,hr_api.g_number)
124 <> nvl(pqh_bdt_shd.g_old_rec.grade_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_grade_id is not null then
127 --
128 -- check if grade_id value exists in per_grades 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 per_grades
138 -- table.
139 --
140 pqh_bdt_shd.constraint_error('PQH_BUDGET_DETAILS_FK5');
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_grade_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_job_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_budget_detail_id PK
165 -- p_job_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_job_id (p_budget_detail_id in number,
178 p_job_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_job_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from per_jobs a
188 where a.job_id = p_job_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_bdt_shd.api_updating
195 (p_budget_detail_id => p_budget_detail_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_job_id,hr_api.g_number)
200 <> nvl(pqh_bdt_shd.g_old_rec.job_id,hr_api.g_number)
201 or not l_api_updating) and
202 p_job_id is not null then
203 --
204 -- check if job_id value exists in per_jobs 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 per_jobs
214 -- table.
215 --
216 pqh_bdt_shd.constraint_error('PQH_BUDGET_DETAILS_FK4');
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_job_id;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |------< chk_budget_version_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_budget_detail_id PK
241 -- p_budget_version_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_budget_version_id (p_budget_detail_id in number,
254 p_budget_version_id in number,
255 p_object_version_number in number) is
256 --
257 l_proc varchar2(72) := g_package||'chk_budget_version_id';
258 l_api_updating boolean;
259 l_dummy varchar2(1);
260 --
261 cursor c1 is
262 select null
263 from pqh_budget_versions a
264 where a.budget_version_id = p_budget_version_id;
265 --
266 Begin
267 --
268 hr_utility.set_location('Entering:'||l_proc,5);
269 --
270 l_api_updating := pqh_bdt_shd.api_updating
271 (p_budget_detail_id => p_budget_detail_id,
272 p_object_version_number => p_object_version_number);
273 --
274 if (l_api_updating
275 and nvl(p_budget_version_id,hr_api.g_number)
276 <> nvl(pqh_bdt_shd.g_old_rec.budget_version_id,hr_api.g_number)
277 or not l_api_updating) and
278 p_budget_version_id is not null then
279 --
280 -- check if budget_version_id value exists in pqh_budget_versions table
281 --
282 open c1;
283 --
284 fetch c1 into l_dummy;
285 if c1%notfound then
286 --
287 close c1;
288 --
289 -- raise error as FK does not relate to PK in pqh_budget_versions
290 -- table.
291 --
292 pqh_bdt_shd.constraint_error('PQH_BUDGET_DETAILS_FK3');
293 --
294 end if;
295 --
296 close c1;
297 --
298 end if;
299 --
300 hr_utility.set_location('Leaving:'||l_proc,10);
301 --
302 End chk_budget_version_id;
303 --
304 -- ----------------------------------------------------------------------------
305 -- |------< chk_organization_id >------|
306 -- ----------------------------------------------------------------------------
307 --
308 -- Description
309 -- This procedure checks that a referenced foreign key actually exists
310 -- in the referenced table.
311 --
312 -- Pre-Conditions
313 -- None.
314 --
315 -- In Parameters
316 -- p_budget_detail_id PK
317 -- p_organization_id ID of FK column
318 -- p_object_version_number object version number
319 --
320 -- Post Success
321 -- Processing continues
322 --
323 -- Post Failure
324 -- Error raised.
325 --
326 -- Access Status
327 -- Internal table handler use only.
328 --
329 Procedure chk_organization_id (p_budget_detail_id in number,
330 p_organization_id in number,
331 p_object_version_number in number) is
332 --
333 l_proc varchar2(72) := g_package||'chk_organization_id';
334 l_api_updating boolean;
335 l_dummy varchar2(1);
336 --
337 cursor c1 is
338 select null
339 from hr_all_organization_units a
340 where a.organization_id = p_organization_id;
341 --
342 Begin
343 --
344 hr_utility.set_location('Entering:'||l_proc,5);
345 --
346 l_api_updating := pqh_bdt_shd.api_updating
347 (p_budget_detail_id => p_budget_detail_id,
348 p_object_version_number => p_object_version_number);
349 --
350 if (l_api_updating
351 and nvl(p_organization_id,hr_api.g_number)
352 <> nvl(pqh_bdt_shd.g_old_rec.organization_id,hr_api.g_number)
353 or not l_api_updating) and
354 p_organization_id is not null then
355 --
356 -- check if organization_id value exists in hr_organization_units table
357 --
358 open c1;
359 --
360 fetch c1 into l_dummy;
361 if c1%notfound then
362 --
363 close c1;
364 --
365 -- raise error as FK does not relate to PK in hr_organization_units
366 -- table.
367 --
368 pqh_bdt_shd.constraint_error('PQH_BUDGET_DETAILS_FK2');
369 --
370 end if;
371 --
372 close c1;
373 --
374 end if;
375 --
376 hr_utility.set_location('Leaving:'||l_proc,10);
377 --
378 End chk_organization_id;
379 --
380 --
381 -- ----------------------------------------------------------------------------
382 -- |------< chk_budget_unit3_value_type_cd >------|
383 -- ----------------------------------------------------------------------------
384 --
385 -- Description
386 -- This procedure is used to check that the lookup value is valid.
387 --
388 -- Pre Conditions
389 -- None.
390 --
391 -- In Parameters
392 -- budget_set_id PK of record being inserted or updated.
393 -- budget_unit3_value_type_cd Value of lookup code.
394 -- effective_date effective date
395 -- object_version_number Object version number of record being
396 -- inserted or updated.
397 --
398 -- Post Success
399 -- Processing continues
400 --
401 -- Post Failure
402 -- Error handled by procedure
403 --
404 -- Access Status
405 -- Internal table handler use only.
406 --
407 Procedure chk_budget_unit3_value_type_cd(p_budget_detail_id in number,
408 p_budget_unit3_value_type_cd in varchar2,
409 p_effective_date in date,
410 p_object_version_number in number) is
411 --
412 l_proc varchar2(72) := g_package||'chk_budget_unit3_value_type_cd';
413 l_api_updating boolean;
414 --
415 Begin
416 --
417 hr_utility.set_location('Entering:'||l_proc, 5);
418 --
419 l_api_updating := pqh_bdt_shd.api_updating
420 (p_budget_detail_id => p_budget_detail_id,
421 p_object_version_number => p_object_version_number);
422 --
423 if (l_api_updating
424 and p_budget_unit3_value_type_cd
425 <> nvl(pqh_bdt_shd.g_old_rec.budget_unit3_value_type_cd,hr_api.g_varchar2)
426 or not l_api_updating)
427 and p_budget_unit3_value_type_cd is not null then
428 --
429 -- check if value of lookup falls within lookup type.
430 --
431 if hr_api.not_exists_in_hr_lookups
432 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
433 p_lookup_code => p_budget_unit3_value_type_cd,
434 p_effective_date => p_effective_date) then
435 --
436 -- raise error as does not exist as lookup
437 --
438 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
439 hr_utility.raise_error;
440 --
441 end if;
442 --
443 end if;
444 --
445 hr_utility.set_location('Leaving:'||l_proc,10);
446 --
447 end chk_budget_unit3_value_type_cd;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |------< chk_budget_unit2_value_type_cd >------|
451 -- ----------------------------------------------------------------------------
452 --
453 -- Description
454 -- This procedure is used to check that the lookup value is valid.
455 --
456 -- Pre Conditions
457 -- None.
458 --
459 -- In Parameters
460 -- budget_set_id PK of record being inserted or updated.
461 -- budget_unit2_value_type_cd Value of lookup code.
462 -- effective_date effective date
463 -- object_version_number Object version number of record being
464 -- inserted or updated.
465 --
466 -- Post Success
467 -- Processing continues
468 --
469 -- Post Failure
470 -- Error handled by procedure
471 --
472 -- Access Status
473 -- Internal table handler use only.
474 --
475 Procedure chk_budget_unit2_value_type_cd(p_budget_detail_id in number,
476 p_budget_unit2_value_type_cd in varchar2,
477 p_effective_date in date,
478 p_object_version_number in number) is
479 --
480 l_proc varchar2(72) := g_package||'chk_budget_unit2_value_type_cd';
481 l_api_updating boolean;
482 --
483 Begin
484 --
485 hr_utility.set_location('Entering:'||l_proc, 5);
486 --
487 l_api_updating := pqh_bdt_shd.api_updating
488 (p_budget_detail_id => p_budget_detail_id,
489 p_object_version_number => p_object_version_number);
490 --
491 if (l_api_updating
492 and p_budget_unit2_value_type_cd
493 <> nvl(pqh_bdt_shd.g_old_rec.budget_unit2_value_type_cd,hr_api.g_varchar2)
494 or not l_api_updating)
495 and p_budget_unit2_value_type_cd is not null then
496 --
497 -- check if value of lookup falls within lookup type.
498 --
499 if hr_api.not_exists_in_hr_lookups
500 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
501 p_lookup_code => p_budget_unit2_value_type_cd,
502 p_effective_date => p_effective_date) then
503 --
504 -- raise error as does not exist as lookup
505 --
506 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
507 hr_utility.raise_error;
508 --
509 end if;
510 --
511 end if;
512 --
513 hr_utility.set_location('Leaving:'||l_proc,10);
514 --
515 end chk_budget_unit2_value_type_cd;
516 --
517 -- ----------------------------------------------------------------------------
518 -- |------< chk_budget_unit1_value_type_cd >------|
519 -- ----------------------------------------------------------------------------
520 --
521 -- Description
522 -- This procedure is used to check that the lookup value is valid.
523 --
524 -- Pre Conditions
525 -- None.
526 --
527 -- In Parameters
528 -- budget_set_id PK of record being inserted or updated.
529 -- budget_unit1_value_type_cd Value of lookup code.
530 -- effective_date effective date
531 -- object_version_number Object version number of record being
532 -- inserted or updated.
533 --
534 -- Post Success
535 -- Processing continues
536 --
537 -- Post Failure
538 -- Error handled by procedure
539 --
540 -- Access Status
541 -- Internal table handler use only.
542 --
543 Procedure chk_budget_unit1_value_type_cd(p_budget_detail_id in number,
544 p_budget_unit1_value_type_cd in varchar2,
545 p_effective_date in date,
546 p_object_version_number in number) is
547 --
548 l_proc varchar2(72) := g_package||'chk_budget_unit1_value_type_cd';
549 l_api_updating boolean;
550 --
551 Begin
552 --
553 hr_utility.set_location('Entering:'||l_proc, 5);
554 --
555 l_api_updating := pqh_bdt_shd.api_updating
556 (p_budget_detail_id => p_budget_detail_id,
557 p_object_version_number => p_object_version_number);
558 --
559 if (l_api_updating
560 and p_budget_unit1_value_type_cd
561 <> nvl(pqh_bdt_shd.g_old_rec.budget_unit1_value_type_cd,hr_api.g_varchar2)
562 or not l_api_updating)
563 and p_budget_unit1_value_type_cd is not null then
564 --
565 -- check if value of lookup falls within lookup type.
566 --
567 if hr_api.not_exists_in_hr_lookups
568 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
569 p_lookup_code => p_budget_unit1_value_type_cd,
570 p_effective_date => p_effective_date) then
571 --
572 -- raise error as does not exist as lookup
573 --
574 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
575 hr_utility.raise_error;
576 --
577 end if;
578 --
579 end if;
580 --
581 hr_utility.set_location('Leaving:'||l_proc,10);
582 --
583 end chk_budget_unit1_value_type_cd;
584 --
585 -- ----------------------------------------------------------------------------
586 -- |---------------------------< insert_validate >----------------------------|
587 -- ----------------------------------------------------------------------------
588 Procedure insert_validate(p_rec in pqh_bdt_shd.g_rec_type) is
589 --
590 l_proc varchar2(72) := g_package||'insert_validate';
591 --
592 Begin
593 hr_utility.set_location('Entering:'||l_proc, 5);
594 --
595 -- Call all supporting business operations
596 --
597 chk_budget_detail_id
598 (p_budget_detail_id => p_rec.budget_detail_id,
599 p_object_version_number => p_rec.object_version_number);
600 --
601 chk_grade_id
602 (p_budget_detail_id => p_rec.budget_detail_id,
603 p_grade_id => p_rec.grade_id,
604 p_object_version_number => p_rec.object_version_number);
605 --
606 chk_job_id
607 (p_budget_detail_id => p_rec.budget_detail_id,
608 p_job_id => p_rec.job_id,
609 p_object_version_number => p_rec.object_version_number);
610 --
611 chk_budget_version_id
612 (p_budget_detail_id => p_rec.budget_detail_id,
613 p_budget_version_id => p_rec.budget_version_id,
614 p_object_version_number => p_rec.object_version_number);
615 --
616 chk_organization_id
617 (p_budget_detail_id => p_rec.budget_detail_id,
618 p_organization_id => p_rec.organization_id,
619 p_object_version_number => p_rec.object_version_number);
620 --
621 chk_budget_unit1_value_type_cd
622 (p_budget_detail_id => p_rec.budget_detail_id,
623 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
624 p_effective_date => sysdate,
625 p_object_version_number => p_rec.object_version_number);
626 --
627 chk_budget_unit2_value_type_cd
628 (p_budget_detail_id => p_rec.budget_detail_id,
629 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
630 p_effective_date => sysdate,
631 p_object_version_number => p_rec.object_version_number);
632 --
633 chk_budget_unit3_value_type_cd
634 (p_budget_detail_id => p_rec.budget_detail_id,
635 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
636 p_effective_date => sysdate,
637 p_object_version_number => p_rec.object_version_number);
638 --
639 --
640 --
641 --
642 hr_utility.set_location(' Leaving:'||l_proc, 10);
643 End insert_validate;
644 --
645 -- ----------------------------------------------------------------------------
646 -- |---------------------------< update_validate >----------------------------|
647 -- ----------------------------------------------------------------------------
648 Procedure update_validate(p_rec in pqh_bdt_shd.g_rec_type) is
649 --
650 l_proc varchar2(72) := g_package||'update_validate';
651 --
652 Begin
653 hr_utility.set_location('Entering:'||l_proc, 5);
654 --
655 -- Call all supporting business operations
656 --
657 chk_budget_detail_id
658 (p_budget_detail_id => p_rec.budget_detail_id,
659 p_object_version_number => p_rec.object_version_number);
660 --
661 chk_grade_id
662 (p_budget_detail_id => p_rec.budget_detail_id,
663 p_grade_id => p_rec.grade_id,
664 p_object_version_number => p_rec.object_version_number);
665 --
666 chk_job_id
667 (p_budget_detail_id => p_rec.budget_detail_id,
668 p_job_id => p_rec.job_id,
669 p_object_version_number => p_rec.object_version_number);
670 --
671 chk_budget_version_id
672 (p_budget_detail_id => p_rec.budget_detail_id,
673 p_budget_version_id => p_rec.budget_version_id,
674 p_object_version_number => p_rec.object_version_number);
675 --
676 chk_organization_id
677 (p_budget_detail_id => p_rec.budget_detail_id,
678 p_organization_id => p_rec.organization_id,
679 p_object_version_number => p_rec.object_version_number);
680 --
681 chk_budget_unit1_value_type_cd
682 (p_budget_detail_id => p_rec.budget_detail_id,
683 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
684 p_effective_date => sysdate,
685 p_object_version_number => p_rec.object_version_number);
686 --
687 chk_budget_unit2_value_type_cd
688 (p_budget_detail_id => p_rec.budget_detail_id,
689 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
690 p_effective_date => sysdate,
691 p_object_version_number => p_rec.object_version_number);
692 --
693 chk_budget_unit3_value_type_cd
694 (p_budget_detail_id => p_rec.budget_detail_id,
695 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
696 p_effective_date => sysdate,
697 p_object_version_number => p_rec.object_version_number);
698 --
699 --
700 --
701 --
702 hr_utility.set_location(' Leaving:'||l_proc, 10);
703 End update_validate;
704 --
705 -- ----------------------------------------------------------------------------
706 -- |---------------------------< delete_validate >----------------------------|
707 -- ----------------------------------------------------------------------------
708 Procedure delete_validate(p_rec in pqh_bdt_shd.g_rec_type) is
709 --
710 l_proc varchar2(72) := g_package||'delete_validate';
711 --
712 Begin
713 hr_utility.set_location('Entering:'||l_proc, 5);
714 --
715 -- Call all supporting business operations
716 --
717 hr_utility.set_location(' Leaving:'||l_proc, 10);
718 End delete_validate;
719 --
720 end pqh_bdt_bus;