[Home] [Help]
PACKAGE BODY: APPS.PQH_BPR_BUS
Source
1 Package Body pqh_bpr_bus as
2 /* $Header: pqbprrhi.pkb 115.8 2002/12/05 19:29:59 rpasapul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bpr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_budget_period_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_period_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_period_id(p_budget_period_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_budget_period_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_bpr_shd.api_updating
47 (p_budget_period_id => p_budget_period_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_budget_period_id,hr_api.g_number)
52 <> pqh_bpr_shd.g_old_rec.budget_period_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_bpr_shd.constraint_error('PQH_BUDGET_PERIODS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_budget_period_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_bpr_shd.constraint_error('PQH_BUDGET_PERIODS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_budget_period_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_end_time_period_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_period_id PK
89 -- p_end_time_period_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_end_time_period_id (p_budget_period_id in number,
102 p_end_time_period_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_end_time_period_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from per_time_periods a
112 where a.time_period_id = p_end_time_period_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_bpr_shd.api_updating
119 (p_budget_period_id => p_budget_period_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_end_time_period_id,hr_api.g_number)
124 <> nvl(pqh_bpr_shd.g_old_rec.end_time_period_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if end_time_period_id value exists in per_time_periods table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in per_time_periods
137 -- table.
138 --
139 pqh_bpr_shd.constraint_error('PQH_BUDGET_PERIODS_FK3');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_end_time_period_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_start_time_period_id >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_budget_period_id PK
164 -- p_start_time_period_id ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_start_time_period_id (p_budget_period_id in number,
177 p_start_time_period_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_start_time_period_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from per_time_periods a
187 where a.time_period_id = p_start_time_period_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := pqh_bpr_shd.api_updating
194 (p_budget_period_id => p_budget_period_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_start_time_period_id,hr_api.g_number)
199 <> nvl(pqh_bpr_shd.g_old_rec.start_time_period_id,hr_api.g_number)
200 or not l_api_updating) then
201 --
202 -- check if start_time_period_id value exists in per_time_periods table
203 --
204 open c1;
205 --
206 fetch c1 into l_dummy;
207 if c1%notfound then
208 --
209 close c1;
210 --
211 -- raise error as FK does not relate to PK in per_time_periods
212 -- table.
213 --
214 pqh_bpr_shd.constraint_error('PQH_BUDGET_PERIODS_FK2');
215 --
216 end if;
217 --
218 close c1;
219 --
220 end if;
221 --
222 hr_utility.set_location('Leaving:'||l_proc,10);
223 --
224 End chk_start_time_period_id;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |------< chk_budget_detail_id >------|
228 -- ----------------------------------------------------------------------------
229 --
230 -- Description
231 -- This procedure checks that a referenced foreign key actually exists
232 -- in the referenced table.
233 --
234 -- Pre-Conditions
235 -- None.
236 --
237 -- In Parameters
238 -- p_budget_period_id PK
239 -- p_budget_detail_id ID of FK column
240 -- p_object_version_number object version number
241 --
242 -- Post Success
243 -- Processing continues
244 --
245 -- Post Failure
246 -- Error raised.
247 --
248 -- Access Status
249 -- Internal table handler use only.
250 --
251 Procedure chk_budget_detail_id (p_budget_period_id in number,
252 p_budget_detail_id in number,
253 p_object_version_number in number) is
254 --
255 l_proc varchar2(72) := g_package||'chk_budget_detail_id';
256 l_api_updating boolean;
257 l_dummy varchar2(1);
258 --
259 cursor c1 is
260 select null
261 from pqh_budget_details a
262 where a.budget_detail_id = p_budget_detail_id;
263 --
264 Begin
265 --
266 hr_utility.set_location('Entering:'||l_proc,5);
267 --
268 l_api_updating := pqh_bpr_shd.api_updating
269 (p_budget_period_id => p_budget_period_id,
270 p_object_version_number => p_object_version_number);
271 --
272 if (l_api_updating
273 and nvl(p_budget_detail_id,hr_api.g_number)
274 <> nvl(pqh_bpr_shd.g_old_rec.budget_detail_id,hr_api.g_number)
275 or not l_api_updating) then
276 --
277 -- check if budget_detail_id value exists in pqh_budget_details table
278 --
279 open c1;
280 --
281 fetch c1 into l_dummy;
282 if c1%notfound then
283 --
284 close c1;
285 --
286 -- raise error as FK does not relate to PK in pqh_budget_details
287 -- table.
288 --
289 pqh_bpr_shd.constraint_error('PQH_BUDGET_PERIODS_FK1');
290 --
291 end if;
292 --
293 close c1;
294 --
295 end if;
296 --
297 hr_utility.set_location('Leaving:'||l_proc,10);
298 --
299 End chk_budget_detail_id;
300 --
301 --
302 -- ----------------------------------------------------------------------------
303 -- |------< chk_budget_unit3_value_type_cd >------|
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 -- p_budget_period_id PK of record being inserted or updated.
314 -- budget_unit3_value_type_cd 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_budget_unit3_value_type_cd(p_budget_period_id in number,
329 p_budget_unit3_value_type_cd in varchar2,
330 p_effective_date in date,
331 p_object_version_number in number) is
332 --
333 l_proc varchar2(72) := g_package||'chk_budget_unit3_value_type_cd';
334 l_api_updating boolean;
335 --
336 Begin
337 --
338 hr_utility.set_location('Entering:'||l_proc, 5);
339 --
340 l_api_updating := pqh_bpr_shd.api_updating
341 (p_budget_period_id => p_budget_period_id,
342 p_object_version_number => p_object_version_number);
343 --
344 if (l_api_updating
345 and p_budget_unit3_value_type_cd
346 <> nvl(pqh_bpr_shd.g_old_rec.budget_unit3_value_type_cd,hr_api.g_varchar2)
347 or not l_api_updating)
348 and p_budget_unit3_value_type_cd is not null then
349 --
350 -- check if value of lookup falls within lookup type.
351 --
352 if hr_api.not_exists_in_hr_lookups
353 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
354 p_lookup_code => p_budget_unit3_value_type_cd,
355 p_effective_date => p_effective_date) then
356 --
357 -- raise error as does not exist as lookup
358 --
359 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
360 hr_utility.raise_error;
361 --
362 end if;
363 --
364 end if;
365 --
366 hr_utility.set_location('Leaving:'||l_proc,10);
367 --
368 end chk_budget_unit3_value_type_cd;
369 --
370 -- ----------------------------------------------------------------------------
371 -- |------< chk_budget_unit2_value_type_cd >------|
372 -- ----------------------------------------------------------------------------
373 --
374 -- Description
375 -- This procedure is used to check that the lookup value is valid.
376 --
377 -- Pre Conditions
378 -- None.
379 --
380 -- In Parameters
381 -- p_budget_period_id PK of record being inserted or updated.
382 -- budget_unit2_value_type_cd Value of lookup code.
383 -- effective_date effective date
384 -- object_version_number Object version number of record being
385 -- inserted or updated.
386 --
387 -- Post Success
388 -- Processing continues
389 --
390 -- Post Failure
391 -- Error handled by procedure
392 --
393 -- Access Status
394 -- Internal table handler use only.
395 --
396 Procedure chk_budget_unit2_value_type_cd(p_budget_period_id in number,
397 p_budget_unit2_value_type_cd in varchar2,
398 p_effective_date in date,
399 p_object_version_number in number) is
400 --
401 l_proc varchar2(72) := g_package||'chk_budget_unit2_value_type_cd';
402 l_api_updating boolean;
403 --
404 Begin
405 --
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 l_api_updating := pqh_bpr_shd.api_updating
409 (p_budget_period_id => p_budget_period_id,
410 p_object_version_number => p_object_version_number);
411 --
412 if (l_api_updating
413 and p_budget_unit2_value_type_cd
414 <> nvl(pqh_bpr_shd.g_old_rec.budget_unit2_value_type_cd,hr_api.g_varchar2)
415 or not l_api_updating)
416 and p_budget_unit2_value_type_cd is not null then
417 --
418 -- check if value of lookup falls within lookup type.
419 --
420 if hr_api.not_exists_in_hr_lookups
421 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
422 p_lookup_code => p_budget_unit2_value_type_cd,
423 p_effective_date => p_effective_date) then
424 --
425 -- raise error as does not exist as lookup
426 --
430 end if;
427 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
428 hr_utility.raise_error;
429 --
431 --
432 end if;
433 --
434 hr_utility.set_location('Leaving:'||l_proc,10);
435 --
436 end chk_budget_unit2_value_type_cd;
437 --
438 -- ----------------------------------------------------------------------------
439 -- |------< chk_budget_unit1_value_type_cd >------|
440 -- ----------------------------------------------------------------------------
441 --
442 -- Description
443 -- This procedure is used to check that the lookup value is valid.
444 --
445 -- Pre Conditions
446 -- None.
447 --
448 -- In Parameters
449 -- p_budget_period_id PK of record being inserted or updated.
450 -- budget_unit1_value_type_cd Value of lookup code.
451 -- effective_date effective date
452 -- object_version_number Object version number of record being
453 -- inserted or updated.
454 --
455 -- Post Success
456 -- Processing continues
457 --
458 -- Post Failure
459 -- Error handled by procedure
460 --
461 -- Access Status
462 -- Internal table handler use only.
463 --
464 Procedure chk_budget_unit1_value_type_cd(p_budget_period_id in number,
465 p_budget_unit1_value_type_cd in varchar2,
466 p_effective_date in date,
467 p_object_version_number in number) is
468 --
469 l_proc varchar2(72) := g_package||'chk_budget_unit1_value_type_cd';
470 l_api_updating boolean;
471 --
472 Begin
473 --
474 hr_utility.set_location('Entering:'||l_proc, 5);
475 --
476 l_api_updating := pqh_bpr_shd.api_updating
477 (p_budget_period_id => p_budget_period_id,
478 p_object_version_number => p_object_version_number);
479 --
480 if (l_api_updating
481 and p_budget_unit1_value_type_cd
482 <> nvl(pqh_bpr_shd.g_old_rec.budget_unit1_value_type_cd,hr_api.g_varchar2)
483 or not l_api_updating)
484 and p_budget_unit1_value_type_cd is not null then
485 --
486 -- check if value of lookup falls within lookup type.
487 --
488 if hr_api.not_exists_in_hr_lookups
489 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
490 p_lookup_code => p_budget_unit1_value_type_cd,
491 p_effective_date => p_effective_date) then
492 --
493 -- raise error as does not exist as lookup
494 --
495 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
496 hr_utility.raise_error;
497 --
498 end if;
499 --
500 end if;
501 --
502 hr_utility.set_location('Leaving:'||l_proc,10);
503 --
504 end chk_budget_unit1_value_type_cd;
505 --
506 -- ----------------------------------------------------------------------------
507 -- |---------------------------< insert_validate >----------------------------|
508 -- ----------------------------------------------------------------------------
509 Procedure insert_validate(p_rec in pqh_bpr_shd.g_rec_type) is
510 --
511 l_proc varchar2(72) := g_package||'insert_validate';
512 --
513 Begin
514 hr_utility.set_location('Entering:'||l_proc, 5);
515 --
516 -- Call all supporting business operations
517 --
518 chk_budget_period_id
519 (p_budget_period_id => p_rec.budget_period_id,
520 p_object_version_number => p_rec.object_version_number);
521 --
522 chk_end_time_period_id
523 (p_budget_period_id => p_rec.budget_period_id,
524 p_end_time_period_id => p_rec.end_time_period_id,
525 p_object_version_number => p_rec.object_version_number);
526 --
527 chk_start_time_period_id
528 (p_budget_period_id => p_rec.budget_period_id,
529 p_start_time_period_id => p_rec.start_time_period_id,
530 p_object_version_number => p_rec.object_version_number);
531 --
532 chk_budget_detail_id
533 (p_budget_period_id => p_rec.budget_period_id,
534 p_budget_detail_id => p_rec.budget_detail_id,
535 p_object_version_number => p_rec.object_version_number);
536 --
537 chk_budget_unit1_value_type_cd
538 (p_budget_period_id => p_rec.budget_period_id,
539 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
540 p_effective_date => sysdate,
541 p_object_version_number => p_rec.object_version_number);
542 --
543 chk_budget_unit2_value_type_cd
544 (p_budget_period_id => p_rec.budget_period_id,
545 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
546 p_effective_date => sysdate,
547 p_object_version_number => p_rec.object_version_number);
548 --
549 chk_budget_unit3_value_type_cd
550 (p_budget_period_id => p_rec.budget_period_id,
551 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
552 p_effective_date => sysdate,
553 p_object_version_number => p_rec.object_version_number);
554 --
555 --
556 --
557 hr_utility.set_location(' Leaving:'||l_proc, 10);
558 End insert_validate;
559 --
560 -- ----------------------------------------------------------------------------
561 -- |---------------------------< update_validate >----------------------------|
562 -- ----------------------------------------------------------------------------
563 Procedure update_validate(p_rec in pqh_bpr_shd.g_rec_type) is
564 --
565 l_proc varchar2(72) := g_package||'update_validate';
566 --
567 Begin
568 hr_utility.set_location('Entering:'||l_proc, 5);
569 --
570 -- Call all supporting business operations
571 --
572 chk_budget_period_id
573 (p_budget_period_id => p_rec.budget_period_id,
574 p_object_version_number => p_rec.object_version_number);
575 --
576 chk_end_time_period_id
577 (p_budget_period_id => p_rec.budget_period_id,
578 p_end_time_period_id => p_rec.end_time_period_id,
579 p_object_version_number => p_rec.object_version_number);
580 --
581 chk_start_time_period_id
582 (p_budget_period_id => p_rec.budget_period_id,
583 p_start_time_period_id => p_rec.start_time_period_id,
584 p_object_version_number => p_rec.object_version_number);
585 --
586 chk_budget_detail_id
587 (p_budget_period_id => p_rec.budget_period_id,
588 p_budget_detail_id => p_rec.budget_detail_id,
589 p_object_version_number => p_rec.object_version_number);
590 --
591 chk_budget_unit1_value_type_cd
592 (p_budget_period_id => p_rec.budget_period_id,
593 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
594 p_effective_date => sysdate,
595 p_object_version_number => p_rec.object_version_number);
596 --
597 chk_budget_unit2_value_type_cd
598 (p_budget_period_id => p_rec.budget_period_id,
599 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
600 p_effective_date => sysdate,
601 p_object_version_number => p_rec.object_version_number);
602 --
603 chk_budget_unit3_value_type_cd
604 (p_budget_period_id => p_rec.budget_period_id,
605 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
606 p_effective_date => sysdate,
607 p_object_version_number => p_rec.object_version_number);
608 --
609 --
610 --
611 hr_utility.set_location(' Leaving:'||l_proc, 10);
612 End update_validate;
613 --
614 -- ----------------------------------------------------------------------------
615 -- |---------------------------< delete_validate >----------------------------|
616 -- ----------------------------------------------------------------------------
617 Procedure delete_validate(p_rec in pqh_bpr_shd.g_rec_type) is
618 --
619 l_proc varchar2(72) := g_package||'delete_validate';
620 --
621 Begin
622 hr_utility.set_location('Entering:'||l_proc, 5);
623 --
624 -- Call all supporting business operations
625 --
626 hr_utility.set_location(' Leaving:'||l_proc, 10);
627 End delete_validate;
628 --
629 end pqh_bpr_bus;