[Home] [Help]
PACKAGE BODY: APPS.PQH_BST_BUS
Source
1 Package Body pqh_bst_bus as
2 /* $Header: pqbstrhi.pkb 115.7 2002/12/05 19:30:15 rpasapul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bst_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_budget_set_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_set_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_set_id(p_budget_set_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_budget_set_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_bst_shd.api_updating
47 (p_budget_set_id => p_budget_set_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_budget_set_id,hr_api.g_number)
52 <> pqh_bst_shd.g_old_rec.budget_set_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_bst_shd.constraint_error('PQH_BUDGET_SETS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_budget_set_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_bst_shd.constraint_error('PQH_BUDGET_SETS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_budget_set_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_budget_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_set_id PK
89 -- p_budget_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_budget_period_id (p_budget_set_id in number,
102 p_budget_period_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_budget_period_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_budget_periods a
112 where a.budget_period_id = p_budget_period_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_bst_shd.api_updating
119 (p_budget_set_id => p_budget_set_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_budget_period_id,hr_api.g_number)
124 <> nvl(pqh_bst_shd.g_old_rec.budget_period_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if budget_period_id value exists in pqh_budget_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 pqh_budget_periods
137 -- table.
138 --
139 pqh_bst_shd.constraint_error('PQH_BUDGET_SETS_FK2');
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_budget_period_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_dflt_budget_set_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_set_id PK
164 -- p_dflt_budget_set_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_dflt_budget_set_id (p_budget_set_id in number,
177 p_dflt_budget_set_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_dflt_budget_set_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from pqh_dflt_budget_sets a
187 where a.dflt_budget_set_id = p_dflt_budget_set_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := pqh_bst_shd.api_updating
194 (p_budget_set_id => p_budget_set_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_dflt_budget_set_id,hr_api.g_number)
199 <> nvl(pqh_bst_shd.g_old_rec.dflt_budget_set_id,hr_api.g_number)
200 or not l_api_updating) and
201 p_dflt_budget_set_id is not null then
202 --
203 -- check if dflt_budget_set_id value exists in pqh_dflt_budget_sets 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_dflt_budget_sets
213 -- table.
214 --
215 pqh_bst_shd.constraint_error('PQH_BUDGET_SETS_FK1');
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_dflt_budget_set_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |------< chk_budget_unit3_value_type_cd >------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description
232 -- This procedure is used to check that the lookup value is valid.
233 --
234 -- Pre Conditions
235 -- None.
236 --
237 -- In Parameters
238 -- budget_set_id PK of record being inserted or updated.
239 -- budget_unit3_value_type_cd Value of lookup code.
240 -- effective_date effective date
241 -- object_version_number Object version number of record being
242 -- inserted or updated.
243 --
244 -- Post Success
245 -- Processing continues
246 --
247 -- Post Failure
248 -- Error handled by procedure
249 --
250 -- Access Status
251 -- Internal table handler use only.
252 --
253 Procedure chk_budget_unit3_value_type_cd(p_budget_set_id in number,
254 p_budget_unit3_value_type_cd in varchar2,
255 p_effective_date in date,
256 p_object_version_number in number) is
257 --
258 l_proc varchar2(72) := g_package||'chk_budget_unit3_value_type_cd';
259 l_api_updating boolean;
260 --
261 Begin
262 --
263 hr_utility.set_location('Entering:'||l_proc, 5);
264 --
265 l_api_updating := pqh_bst_shd.api_updating
266 (p_budget_set_id => p_budget_set_id,
267 p_object_version_number => p_object_version_number);
268 --
269 if (l_api_updating
270 and p_budget_unit3_value_type_cd
271 <> nvl(pqh_bst_shd.g_old_rec.budget_unit3_value_type_cd,hr_api.g_varchar2)
272 or not l_api_updating)
273 and p_budget_unit3_value_type_cd is not null then
274 --
275 -- check if value of lookup falls within lookup type.
276 --
277 if hr_api.not_exists_in_hr_lookups
278 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
279 p_lookup_code => p_budget_unit3_value_type_cd,
280 p_effective_date => p_effective_date) then
281 --
282 -- raise error as does not exist as lookup
283 --
284 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
285 hr_utility.raise_error;
286 --
287 end if;
288 --
289 end if;
290 --
291 hr_utility.set_location('Leaving:'||l_proc,10);
292 --
293 end chk_budget_unit3_value_type_cd;
294 --
295 -- ----------------------------------------------------------------------------
296 -- |------< chk_budget_unit2_value_type_cd >------|
297 -- ----------------------------------------------------------------------------
298 --
299 -- Description
300 -- This procedure is used to check that the lookup value is valid.
301 --
302 -- Pre Conditions
303 -- None.
304 --
305 -- In Parameters
306 -- budget_set_id PK of record being inserted or updated.
307 -- budget_unit2_value_type_cd Value of lookup code.
308 -- effective_date effective date
309 -- object_version_number Object version number of record being
310 -- inserted or updated.
311 --
312 -- Post Success
313 -- Processing continues
314 --
315 -- Post Failure
316 -- Error handled by procedure
317 --
318 -- Access Status
319 -- Internal table handler use only.
320 --
321 Procedure chk_budget_unit2_value_type_cd(p_budget_set_id in number,
322 p_budget_unit2_value_type_cd in varchar2,
323 p_effective_date in date,
324 p_object_version_number in number) is
325 --
326 l_proc varchar2(72) := g_package||'chk_budget_unit2_value_type_cd';
327 l_api_updating boolean;
328 --
329 Begin
330 --
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333 l_api_updating := pqh_bst_shd.api_updating
334 (p_budget_set_id => p_budget_set_id,
335 p_object_version_number => p_object_version_number);
336 --
337 if (l_api_updating
338 and p_budget_unit2_value_type_cd
339 <> nvl(pqh_bst_shd.g_old_rec.budget_unit2_value_type_cd,hr_api.g_varchar2)
340 or not l_api_updating)
341 and p_budget_unit2_value_type_cd is not null then
342 --
343 -- check if value of lookup falls within lookup type.
344 --
345 if hr_api.not_exists_in_hr_lookups
346 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
347 p_lookup_code => p_budget_unit2_value_type_cd,
348 p_effective_date => p_effective_date) then
349 --
350 -- raise error as does not exist as lookup
351 --
352 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
353 hr_utility.raise_error;
354 --
355 end if;
356 --
357 end if;
358 --
359 hr_utility.set_location('Leaving:'||l_proc,10);
360 --
361 end chk_budget_unit2_value_type_cd;
362 --
363 -- ----------------------------------------------------------------------------
364 -- |------< chk_budget_unit1_value_type_cd >------|
365 -- ----------------------------------------------------------------------------
366 --
367 -- Description
368 -- This procedure is used to check that the lookup value is valid.
369 --
370 -- Pre Conditions
371 -- None.
372 --
373 -- In Parameters
374 -- budget_set_id PK of record being inserted or updated.
375 -- budget_unit1_value_type_cd Value of lookup code.
376 -- effective_date effective date
377 -- object_version_number Object version number of record being
378 -- inserted or updated.
379 --
380 -- Post Success
381 -- Processing continues
382 --
383 -- Post Failure
384 -- Error handled by procedure
385 --
386 -- Access Status
387 -- Internal table handler use only.
388 --
389 Procedure chk_budget_unit1_value_type_cd(p_budget_set_id in number,
390 p_budget_unit1_value_type_cd in varchar2,
391 p_effective_date in date,
392 p_object_version_number in number) is
393 --
394 l_proc varchar2(72) := g_package||'chk_budget_unit1_value_type_cd';
395 l_api_updating boolean;
396 --
397 Begin
398 --
399 hr_utility.set_location('Entering:'||l_proc, 5);
400 --
401 l_api_updating := pqh_bst_shd.api_updating
402 (p_budget_set_id => p_budget_set_id,
403 p_object_version_number => p_object_version_number);
404 --
405 if (l_api_updating
406 and p_budget_unit1_value_type_cd
407 <> nvl(pqh_bst_shd.g_old_rec.budget_unit1_value_type_cd,hr_api.g_varchar2)
408 or not l_api_updating)
409 and p_budget_unit1_value_type_cd is not null then
410 --
411 -- check if value of lookup falls within lookup type.
412 --
413 if hr_api.not_exists_in_hr_lookups
414 (p_lookup_type => 'PQH_BUDGET_UNIT_VALUE_TYPE',
415 p_lookup_code => p_budget_unit1_value_type_cd,
416 p_effective_date => p_effective_date) then
417 --
418 -- raise error as does not exist as lookup
419 --
420 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
421 hr_utility.raise_error;
422 --
423 end if;
424 --
425 end if;
426 --
427 hr_utility.set_location('Leaving:'||l_proc,10);
428 --
429 end chk_budget_unit1_value_type_cd;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |---------------------------< insert_validate >----------------------------|
433 -- ----------------------------------------------------------------------------
434 Procedure insert_validate(p_rec in pqh_bst_shd.g_rec_type
435 ,p_effective_date in date) is
436 --
437 l_proc varchar2(72) := g_package||'insert_validate';
438 --
439 Begin
440 hr_utility.set_location('Entering:'||l_proc, 5);
441 --
442 -- Call all supporting business operations
443 --
444 chk_budget_set_id
445 (p_budget_set_id => p_rec.budget_set_id,
446 p_object_version_number => p_rec.object_version_number);
447 --
448 chk_budget_period_id
449 (p_budget_set_id => p_rec.budget_set_id,
450 p_budget_period_id => p_rec.budget_period_id,
451 p_object_version_number => p_rec.object_version_number);
452 --
453 chk_dflt_budget_set_id
454 (p_budget_set_id => p_rec.budget_set_id,
455 p_dflt_budget_set_id => p_rec.dflt_budget_set_id,
456 p_object_version_number => p_rec.object_version_number);
457 --
458 chk_budget_unit3_value_type_cd
459 (p_budget_set_id => p_rec.budget_set_id,
460 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
461 p_effective_date => p_effective_date,
462 p_object_version_number => p_rec.object_version_number);
463 --
464 chk_budget_unit2_value_type_cd
465 (p_budget_set_id => p_rec.budget_set_id,
466 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
467 p_effective_date => p_effective_date,
468 p_object_version_number => p_rec.object_version_number);
469 --
470 chk_budget_unit1_value_type_cd
471 (p_budget_set_id => p_rec.budget_set_id,
472 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
473 p_effective_date => p_effective_date,
474 p_object_version_number => p_rec.object_version_number);
475 --
476 --
477 --
478 hr_utility.set_location(' Leaving:'||l_proc, 10);
479 End insert_validate;
480 --
481 -- ----------------------------------------------------------------------------
482 -- |---------------------------< update_validate >----------------------------|
483 -- ----------------------------------------------------------------------------
484 Procedure update_validate(p_rec in pqh_bst_shd.g_rec_type
485 ,p_effective_date in date) is
486 --
487 l_proc varchar2(72) := g_package||'update_validate';
488 --
489 Begin
490 hr_utility.set_location('Entering:'||l_proc, 5);
491 --
492 -- Call all supporting business operations
493 --
494 chk_budget_set_id
495 (p_budget_set_id => p_rec.budget_set_id,
496 p_object_version_number => p_rec.object_version_number);
497 --
498 chk_budget_period_id
499 (p_budget_set_id => p_rec.budget_set_id,
500 p_budget_period_id => p_rec.budget_period_id,
501 p_object_version_number => p_rec.object_version_number);
502 --
503 chk_dflt_budget_set_id
504 (p_budget_set_id => p_rec.budget_set_id,
505 p_dflt_budget_set_id => p_rec.dflt_budget_set_id,
506 p_object_version_number => p_rec.object_version_number);
507 --
508 chk_budget_unit3_value_type_cd
509 (p_budget_set_id => p_rec.budget_set_id,
510 p_budget_unit3_value_type_cd => p_rec.budget_unit3_value_type_cd,
511 p_effective_date => p_effective_date,
512 p_object_version_number => p_rec.object_version_number);
513 --
514 chk_budget_unit2_value_type_cd
515 (p_budget_set_id => p_rec.budget_set_id,
516 p_budget_unit2_value_type_cd => p_rec.budget_unit2_value_type_cd,
517 p_effective_date => p_effective_date,
518 p_object_version_number => p_rec.object_version_number);
519 --
520 chk_budget_unit1_value_type_cd
521 (p_budget_set_id => p_rec.budget_set_id,
522 p_budget_unit1_value_type_cd => p_rec.budget_unit1_value_type_cd,
523 p_effective_date => p_effective_date,
524 p_object_version_number => p_rec.object_version_number);
525 --
526 --
527 --
528 hr_utility.set_location(' Leaving:'||l_proc, 10);
529 End update_validate;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |---------------------------< delete_validate >----------------------------|
533 -- ----------------------------------------------------------------------------
534 Procedure delete_validate(p_rec in pqh_bst_shd.g_rec_type
535 ,p_effective_date in date) is
536 --
537 l_proc varchar2(72) := g_package||'delete_validate';
538 --
539 Begin
540 hr_utility.set_location('Entering:'||l_proc, 5);
541 --
542 -- Call all supporting business operations
543 --
544 hr_utility.set_location(' Leaving:'||l_proc, 10);
545 End delete_validate;
546 --
547 end pqh_bst_bus;