[Home] [Help]
PACKAGE BODY: APPS.PQH_BGT_BUS
Source
1 Package Body pqh_bgt_bus as
2 /* $Header: pqbgtrhi.pkb 120.1 2005/09/21 03:11:10 hmehta noship $ */
3 --
4 -- ----------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------+
7 --
8 g_package varchar2(33) := ' pqh_bgt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------+
11 -- |------< chk_budget_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_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_id(p_budget_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_budget_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_bgt_shd.api_updating
47 (p_budget_id => p_budget_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_budget_id,hr_api.g_number)
52 <> pqh_bgt_shd.g_old_rec.budget_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_bgt_shd.constraint_error('PQH_BUDGETS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_budget_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_bgt_shd.constraint_error('PQH_BUDGETS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_budget_id;
75 --
76 -- ----------------------------------------------------------------------------+
77 -- |------< chk_budget_unit3_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_id PK
89 -- p_budget_unit3_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 -- CANNOT Change Budget UOM for budgets whose STATUS = FROZEN
102 --
103 Procedure chk_budget_unit3_id (p_budget_id in number,
104 p_status in varchar2,
105 p_budget_unit3_id in number,
106 p_object_version_number in number) is
107 --
108 l_proc varchar2(72) := g_package||'chk_budget_unit3_id';
109 l_api_updating boolean;
110 l_dummy varchar2(1);
111 --
112 cursor c1 is
113 select null
114 from per_shared_types a
115 where a.shared_type_id = p_budget_unit3_id;
116 --
117 Begin
118 --
119 hr_utility.set_location('Entering:'||l_proc,5);
120 --
121 l_api_updating := pqh_bgt_shd.api_updating
122 (p_budget_id => p_budget_id,
123 p_object_version_number => p_object_version_number);
124 --
125 -- If STATUS = FROZEN and UOM is changed then ERROR as you cannot change
126 -- UOM for FROZEN budgets
127 --
128 if (l_api_updating
129 and nvl(p_budget_unit3_id,hr_api.g_number)
130 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit3_id,hr_api.g_number)) then
131
132 -- UOM is changed
133 --
134 if NVL(p_status,'X') = 'FROZEN' then
135 --
136 hr_utility.set_message(8302,'PQH_BUDGET_UOM_CHANGED');
137 hr_utility.raise_error;
138 --
139 end if;
140
141 end if;
142 --
143 if (l_api_updating
144 and nvl(p_budget_unit3_id,hr_api.g_number)
145 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit3_id,hr_api.g_number)
146 or not l_api_updating) and
147 p_budget_unit3_id is not null then
148 --
149 -- check if budget_unit3_id value exists in per_shared_types table
150 --
151 open c1;
152 --
153 fetch c1 into l_dummy;
154 if c1%notfound then
155 --
156 close c1;
157 --
158 -- raise error as FK does not relate to PK in per_shared_types
159 -- table.
160 --
161 pqh_bgt_shd.constraint_error('PQH_BUDGETS_FK4');
162 --
163 end if;
164 --
165 close c1;
166 --
167 end if;
168 --
169 hr_utility.set_location('Leaving:'||l_proc,10);
170 --
171 End chk_budget_unit3_id;
172 --
173 -- ----------------------------------------------------------------------------+
174 -- |------< chk_budget_unit2_id >------|
175 -- ----------------------------------------------------------------------------+
176 --
177 -- Description
178 -- This procedure checks that a referenced foreign key actually exists
179 -- in the referenced table.
180 --
181 -- Pre-Conditions
182 -- None.
183 --
184 -- In Parameters
185 -- p_budget_id PK
186 -- p_budget_unit2_id ID of FK column
187 -- p_object_version_number object version number
188 --
189 -- Post Success
190 -- Processing continues
191 --
192 -- Post Failure
193 -- Error raised.
194 --
195 -- Access Status
196 -- Internal table handler use only.
197 --
198 Procedure chk_budget_unit2_id (p_budget_id in number,
199 p_status in varchar2,
200 p_budget_unit2_id in number,
201 p_object_version_number in number) is
202 --
203 l_proc varchar2(72) := g_package||'chk_budget_unit2_id';
204 l_api_updating boolean;
205 l_dummy varchar2(1);
206 --
207 cursor c1 is
208 select null
209 from per_shared_types a
210 where a.shared_type_id = p_budget_unit2_id;
211 --
212 Begin
213 --
214 hr_utility.set_location('Entering:'||l_proc,5);
215 --
216 l_api_updating := pqh_bgt_shd.api_updating
217 (p_budget_id => p_budget_id,
218 p_object_version_number => p_object_version_number);
219 --
220 -- If STATUS = FROZEN and UOM is changed then ERROR as you cannot change
221 -- UOM for FROZEN budgets
222 --
223 if (l_api_updating
224 and nvl(p_budget_unit2_id,hr_api.g_number)
225 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit2_id,hr_api.g_number)) then
226
227 -- UOM is changed
228 --
229 --
230 if NVL(p_status,'X') = 'FROZEN' then
231 --
232 --
233 hr_utility.set_message(8302,'PQH_BUDGET_UOM_CHANGED');
234 hr_utility.raise_error;
235 --
236 end if;
237
238 end if;
239
240 --
241 if (l_api_updating
242 and nvl(p_budget_unit2_id,hr_api.g_number)
243 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit2_id,hr_api.g_number)
244 or not l_api_updating) and
245 p_budget_unit2_id is not null then
246 --
247 -- check if budget_unit2_id value exists in per_shared_types table
248 --
249 open c1;
250 --
251 fetch c1 into l_dummy;
252 if c1%notfound then
253 --
254 close c1;
255 --
256 -- raise error as FK does not relate to PK in per_shared_types
257 -- table.
258 --
259 pqh_bgt_shd.constraint_error('PQH_BUDGETS_FK3');
260 --
261 end if;
262 --
263 close c1;
264 --
265 end if;
266 --
267 hr_utility.set_location('Leaving:'||l_proc,10);
268 --
269 End chk_budget_unit2_id;
270 --
271 -- ----------------------------------------------------------------------------+
272 -- |------< chk_budget_unit1_id >------|
273 -- ----------------------------------------------------------------------------+
274 --
275 -- Description
276 -- This procedure checks that a referenced foreign key actually exists
277 -- in the referenced table.
278 --
279 -- Pre-Conditions
280 -- None.
281 --
282 -- In Parameters
283 -- p_budget_id PK
284 -- p_budget_unit1_id ID of FK column
285 -- p_object_version_number object version number
286 --
287 -- Post Success
288 -- Processing continues
289 --
290 -- Post Failure
291 -- Error raised.
292 --
293 -- Access Status
294 -- Internal table handler use only.
295 --
296 Procedure chk_budget_unit1_id (p_budget_id in number,
297 p_status in varchar2,
298 p_budget_unit1_id in number,
299 p_object_version_number in number) is
300 --
301 l_proc varchar2(72) := g_package||'chk_budget_unit1_id';
302 l_api_updating boolean;
303 l_dummy varchar2(1);
304 --
305 cursor c1 is
306 select null
307 from per_shared_types a
308 where a.shared_type_id = p_budget_unit1_id;
309 --
310 Begin
311 --
312 hr_utility.set_location('Entering:'||l_proc,5);
313 --
314 l_api_updating := pqh_bgt_shd.api_updating
315 (p_budget_id => p_budget_id,
316 p_object_version_number => p_object_version_number);
317 --
318 -- If STATUS = FROZEN and UOM is changed then ERROR as you cannot change
319 -- UOM for FROZEN budgets
320 --
321 if (l_api_updating
322 and nvl(p_budget_unit1_id,hr_api.g_number)
323 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit1_id,hr_api.g_number)) then
324
325 -- UOM is changed
326 --
327 --
328 if NVL(p_status,'X') = 'FROZEN' then
329 --
330 --
331 hr_utility.set_message(8302,'PQH_BUDGET_UOM_CHANGED');
332 hr_utility.raise_error;
333 --
334 end if;
335
336 end if;
337
338 --
339 if (l_api_updating
340 and nvl(p_budget_unit1_id,hr_api.g_number)
341 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit1_id,hr_api.g_number)
342 or not l_api_updating) and
343 p_budget_unit1_id is not null then
344 --
345 -- check if budget_unit1_id value exists in per_shared_types table
346 --
347 open c1;
348 --
349 fetch c1 into l_dummy;
350 if c1%notfound then
351 --
352 close c1;
353 --
354 -- raise error as FK does not relate to PK in per_shared_types
355 -- table.
356 --
357 pqh_bgt_shd.constraint_error('PQH_BUDGETS_FK2');
358 --
359 end if;
360 --
361 close c1;
362 --
363 end if;
364 --
365 hr_utility.set_location('Leaving:'||l_proc,10);
366 --
367 End chk_budget_unit1_id;
368 --
369 -- ----------------------------------------------------------------------------+
370 -- |------< chk_pos_control_budget >------|
371 -- ----------------------------------------------------------------------------+
372 --
373 -- Description
374 -- This procedure checks that there does not exist a position control budget
375 -- in the same period for the same unit type
376 --
377 -- Pre-Conditions
378 -- None.
379 --
380 -- In Parameters
381 -- p_budget_id PK
382 -- p_budget_start_date
383 -- p_budget_end_date
384 -- budget unit id's
385 --
386 -- Post Success
387 -- Processing continues
388 --
389 -- Post Failure
390 -- Error raised.
391 --
392 -- Access Status
393 -- Internal table handler use only.
394 --
395 Procedure chk_pos_control_budget (p_budget_id in number,
396 p_business_group_id in number ,
397 p_budget_unit1_id in number,
398 p_budget_unit2_id in number,
399 p_budget_unit3_id in number,
400 p_budgeted_entity_cd in varchar2,
401 p_budget_start_date in date,
402 p_budget_end_date in date) is
403 --
404 l_proc varchar2(72) := g_package||'chk_pos_control_budget';
405 l_api_updating boolean;
406 l_dummy varchar2(1);
407 l_bgt_unit1_type varchar2(30);
408 l_bgt_unit2_type varchar2(30);
409 l_bgt_unit3_type varchar2(30);
410 --
411 l_unit1_type varchar2(30);
412 l_unit2_type varchar2(30);
413 l_unit3_type varchar2(30);
414 l_unit_desc varchar2(80);
415 l_units varchar2(200);
416
417 cursor c1 is
421 and (business_group_id = p_business_group_id and business_group_id is not null)
418 select budget_unit1_id,budget_unit2_id,budget_unit3_id,budget_name,budget_start_date,budget_end_date
419 from pqh_budgets
420 where position_control_flag ='Y'
422 and budget_start_date <p_budget_end_date
423 and budget_end_date>p_budget_start_date
424 and budget_id <> nvl(p_budget_id,0);
425 --
426 Begin
427 --
428 hr_utility.set_location('Entering:'||l_proc,5);
429 --
430 if p_budget_unit1_id is not null then
431 l_bgt_unit1_type := pqh_wks_budget.get_unit_type(p_budget_unit1_id);
432 end if;
433 if p_budget_unit2_id is not null then
434 l_bgt_unit2_type := pqh_wks_budget.get_unit_type(p_budget_unit2_id);
435 end if;
436 if p_budget_unit3_id is not null then
437 l_bgt_unit3_type := pqh_wks_budget.get_unit_type(p_budget_unit3_id);
438 end if;
439 if p_budgeted_entity_cd ='OPEN' then
440 hr_utility.set_message(8302,'PQH_BGT_OPEN_CTRL');
441 hr_utility.raise_error;
442 end if;
443 for i in c1 loop
444 l_units := '' ;
445 if i.budget_unit1_id is not null then
446 l_unit1_type := pqh_wks_budget.get_unit_type(i.budget_unit1_id);
447 l_unit_desc := pqh_wks_budget.get_unit_desc(i.budget_unit1_id);
448 l_units := l_unit_desc;
449 end if;
450 if i.budget_unit2_id is not null then
451 l_unit2_type := pqh_wks_budget.get_unit_type(i.budget_unit2_id);
452 l_unit_desc := pqh_wks_budget.get_unit_desc(i.budget_unit2_id);
453 l_units := l_units||','||l_unit_desc;
454 end if;
455 if i.budget_unit3_id is not null then
456 l_unit3_type := pqh_wks_budget.get_unit_type(i.budget_unit3_id);
457 l_unit_desc := pqh_wks_budget.get_unit_desc(i.budget_unit3_id);
458 l_units := l_units||','||l_unit_desc;
459 end if;
460 if (l_unit1_type = nvl(l_bgt_unit1_type,'-1')) or
461 (l_unit1_type = nvl(l_bgt_unit2_type,'-1')) or
462 (l_unit1_type = nvl(l_bgt_unit3_type,'-1')) or
463 (nvl(l_unit2_type,'-2') = nvl(l_bgt_unit1_type,'-1')) or
464 (nvl(l_unit2_type,'-2') = nvl(l_bgt_unit2_type,'-1')) or
465 (nvl(l_unit2_type,'-2') = nvl(l_bgt_unit3_type,'-1')) or
466 (nvl(l_unit3_type,'-2') = nvl(l_bgt_unit1_type,'-1')) or
467 (nvl(l_unit3_type,'-2') = nvl(l_bgt_unit2_type,'-1')) or
468 (nvl(l_unit3_type,'-2') = nvl(l_bgt_unit3_type,'-1')) then
469 hr_utility.set_message(8302,'PQH_BGV_POS_CTRL');
470 hr_utility.set_message_token('BUDGET_NAME',i.budget_name);
471 hr_utility.set_message_token('START_DATE',i.budget_start_date);
472 hr_utility.set_message_token('END_DATE',i.budget_end_date);
473 hr_utility.set_message_token('UNITS',l_units);
474 hr_utility.raise_error;
475 end if;
476 end loop;
477 hr_utility.set_location('Leaving:'||l_proc,10);
478 --
479 End chk_pos_control_budget;
480 --
481 -- ----------------------------------------------------------------------------+
482 -- |------< chk_period_set_name >------|
483 -- ----------------------------------------------------------------------------+
484 --
485 -- Description
486 -- This procedure checks that a referenced foreign key actually exists
487 -- in the referenced table.
488 --
489 -- Pre-Conditions
490 -- None.
491 --
492 -- In Parameters
493 -- p_budget_id PK
494 -- p_period_set_name ID of FK column
495 -- p_object_version_number object version number
496 --
497 -- Post Success
498 -- Processing continues
499 --
500 -- Post Failure
501 -- Error raised.
502 --
503 -- Access Status
504 -- Internal table handler use only.
505 --
506 Procedure chk_period_set_name (p_budget_id in number,
507 p_period_set_name in varchar2,
508 p_object_version_number in number) is
509 --
510 l_proc varchar2(72) := g_package||'chk_period_set_name';
511 l_api_updating boolean;
512 l_dummy varchar2(1);
513 --
514 cursor c1 is
515 select null
516 from pay_calendars a
517 where a.period_set_name = p_period_set_name;
518 --
519 Begin
520 --
521 hr_utility.set_location('Entering:'||l_proc,5);
522 --
523 l_api_updating := pqh_bgt_shd.api_updating
524 (p_budget_id => p_budget_id,
525 p_object_version_number => p_object_version_number);
526 --
527 if (l_api_updating
528 and nvl(p_period_set_name,hr_api.g_varchar2)
529 <> nvl(pqh_bgt_shd.g_old_rec.period_set_name,hr_api.g_varchar2)
530 or not l_api_updating) then
531 --
532 -- check if period_set_name value exists in pay_calendars table
533 --
534 open c1;
535 --
536 fetch c1 into l_dummy;
537 if c1%notfound then
538 --
539 close c1;
540 --
541 -- raise error as FK does not relate to PK in pay_calendars
542 -- table.
543 --
544 pqh_bgt_shd.constraint_error('PQH_BUDGETS_FK1');
545 --
546 end if;
547 --
548 close c1;
549 --
550 end if;
551 --
552 hr_utility.set_location('Leaving:'||l_proc,10);
553 --
557 -- |------< chk_transfer_to_gl >------|
554 End chk_period_set_name;
555 --
556 -- ----------------------------------------------------------------------------+
558 -- ----------------------------------------------------------------------------+
559 --
560 -- Description
561 -- This procedure is used to check that the lookup value is valid.
562 --
563 -- Pre Conditions
564 -- None.
565 --
566 -- In Parameters
567 -- budget_id PK of record being inserted or updated.
568 -- transfer_to_gl_flag Value of lookup code.
569 -- effective_date effective date
570 -- object_version_number Object version number of record being
571 -- inserted or updated.
572 --
573 -- Post Success
574 -- Processing continues
575 --
576 -- Post Failure
577 -- Error handled by procedure
578 --
579 -- Access Status
580 -- Internal table handler use only.
581 --
582 Procedure chk_transfer_to_gl(p_budget_id in number,
583 p_gl_budget_name in varchar2,
584 p_gl_set_of_books_id in number,
585 p_budget_start_date in date,
586 p_budget_end_date in date,
587 p_effective_date in date,
588 p_position_control_flag in varchar2,
589 p_object_version_number in number) is
590 --
591 l_proc varchar2(72) := g_package||'chk_transfer_to_gl';
592 -- l_api_updating boolean;
593 l_dummy varchar2(30);
594 l_gl_budget_rec gl_budgets%ROWTYPE;
595 l_gl_bgt_start_date date;
596 l_gl_bgt_end_date date;
597 l_gl_budget_range varchar2(30);
598 l_budget_range varchar2(30);
599 -- check if the budget is defined in gl_budgets and the budget
600 -- has been opened i.e latest_opened_year IS NOT NULL
601
602 CURSOR csr_gl_budgets_rec IS
603 SELECT *
604 FROM gl_budgets
605 WHERE budget_name = p_gl_budget_name ;
606
607 CURSOR csr_gl_budget_dates IS
608 SELECT start_date,end_date
609 FROM gl_budgets_v
610 WHERE budget_name = p_gl_budget_name ;
611
612 Cursor csr_set_of_books is
613 SELECT 'X'
614 FROM gl_sets_of_books a
615 WHERE a.set_of_books_id = nvl(p_gl_set_of_books_id,-9999);
616 --
617 Begin
618 --
619 hr_utility.set_location('Entering:'||l_proc, 5);
620 --
621 /* commented out nocopy by kmullapu to remove limitation that controlled budget name should be
622 similar to GL budget name for transfering to GL. A controll Bugdted can be transfered
623 to any GL budget provided that controll budget falls with in GL budget date range.
624 As a result of this we will not be using transfer_to_gl_flag
625
626 l_api_updating := pqh_bgt_shd.api_updating
627 (p_budget_id => p_budget_id,
628 p_object_version_number => p_object_version_number);
629
630 if (l_api_updating
631 and p_transfer_to_gl_flag <> nvl(pqh_bgt_shd.g_old_rec.transfer_to_gl_flag,hr_api.g_varchar2)
632 or not l_api_updating)
633 and p_transfer_to_gl_flag is not null then
634 --
635 -- check if value of lookup falls within lookup type.
636 --
637 if hr_api.not_exists_in_hr_lookups
638 (p_lookup_type => 'YES_NO',
639 p_lookup_code => p_transfer_to_gl_flag,
640 p_effective_date => p_effective_date) then
641 --
642 -- raise error as does not exist as lookup
643 --
644 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
645 hr_utility.raise_error;
646 --
647 end if;
648 end if;
649 */
650 IF (p_gl_budget_name is not null) THEN
651 -- check if this Bugdet falls with in GL budget date Range and
652 -- get the set_of_books_id for this budget
653 IF nvl(p_position_control_flag,'N') = 'Y' THEN
654 OPEN csr_gl_budget_dates;
655 FETCH csr_gl_budget_dates INTO l_gl_bgt_start_date,l_gl_bgt_end_date;
656 IF (csr_gl_budget_dates%FOUND) THEN
657 IF (p_budget_start_date < l_gl_bgt_start_date OR
658 p_budget_start_date >= l_gl_bgt_end_date) THEN
659 -- GL budget doest not cover Control Budget range
660 l_gl_budget_range := fnd_date.date_to_displaydate(l_gl_bgt_start_date)||' - '||fnd_date.date_to_displaydate(l_gl_bgt_end_date);
661 l_budget_range := fnd_date.date_to_displaydate(p_budget_start_date)||' - '||fnd_date.date_to_displaydate(p_budget_end_date);
662 hr_utility.set_message(8302,'PQH_GL_BUDGET_INVLD_DATE_RANGE');
663 hr_utility.set_message_token('GL_BUDGET_DATE_RANGE',l_gl_budget_range);
664 hr_utility.set_message_token('BUDGET_DATE_RANGE',l_budget_range);
665 hr_utility.raise_error;
666 END IF;
667 ELSE
668 -- budget Not defined in GL
669 hr_utility.set_message(8302,'PQH_GL_BUDGET_INVALID');
670 hr_utility.raise_error;
671 END IF;
672 OPEN csr_gl_budgets_rec;
673 FETCH csr_gl_budgets_rec INTO l_gl_budget_rec;
674 CLOSE csr_gl_budgets_rec;
675 IF l_gl_budget_rec.budget_name IS NULL THEN
676 -- budget Not defined in GL
680 -- GL Budget is already closed
677 hr_utility.set_message(8302,'PQH_GL_BUDGET_INVALID');
678 hr_utility.raise_error;
679 ELSIF NVL(l_gl_budget_rec.status,'Z') = 'C' THEN
681 -- raise error
682 hr_utility.set_message(8302,'PQH_GL_BUDGET_CLOSED');
683 hr_utility.raise_error;
684 ELSIF l_gl_budget_rec.latest_opened_year IS NULL THEN
685 -- latest open year for the GL budget is null
686 -- raise error
687 hr_utility.set_message(8302,'PQH_GL_BUDGET_YEAR');
688 hr_utility.raise_error;
689 -- hmehta Changed set_of_books_id to ledger_id for bug4602435
690 ELSIF l_gl_budget_rec.ledger_id <> p_gl_set_of_books_id THEN
691 -- Budget set of books does not match with GL budget set of books
692 -- raise error
693 hr_utility.set_message(8302,'PQH_GL_SOB_DIFFERENT');
694 hr_utility.raise_error;
695 else
696 open csr_set_of_books;
697 fetch csr_set_of_books into l_dummy;
698 if csr_set_of_books%notfound then
699 hr_utility.set_message(8302,'PQH_INVALID_SET_OF_BOOKS');
700 hr_utility.raise_error;
701 end if;
702 END IF;
703 else
704 -- Budget must be a Controlled budget to be marked transfer to gl
705 -- raise error
706 hr_utility.set_message(8302,'PQH_TRANSFER_TO_GL');
707 hr_utility.raise_error;
708 END IF;
709 END IF;
710 --
711 --
712 hr_utility.set_location('Leaving:'||l_proc,10);
713 --
714 end chk_transfer_to_gl;
715 --
716 -- ----------------------------------------------------------------------------+
717 -- |------< chk_transfer_to_grants_flag >------|
718 -- ----------------------------------------------------------------------------+
719 --
720 -- Description
721 -- This procedure is used to check that the lookup value is valid.
722 --
723 -- Pre Conditions
724 -- None.
725 --
726 -- In Parameters
727 -- budget_id PK of record being inserted or updated.
728 -- transfer_to_grants_flag Value of lookup code.
729 -- effective_date effective date
730 -- object_version_number Object version number of record being
731 -- inserted or updated.
732 --
733 -- Post Success
734 -- Processing continues
735 --
736 -- Post Failure
737 -- Error handled by procedure
738 --
739 -- Access Status
740 -- Internal table handler use only.
741 --
742 Procedure chk_transfer_to_grants_flag(p_budget_id in number,
743 p_transfer_to_grants_flag in varchar2,
744 p_position_control_flag in varchar2,
745 p_effective_date in date,
746 p_object_version_number in number) is
747 --
748 l_proc varchar2(72) := g_package||'chk_transfer_to_grants_flag';
749 l_api_updating boolean;
750 --
751 Begin
752 --
753 hr_utility.set_location('Entering:'||l_proc, 5);
754 --
755 l_api_updating := pqh_bgt_shd.api_updating
756 (p_budget_id => p_budget_id,
757 p_object_version_number => p_object_version_number);
758 --
759 if (l_api_updating
760 and p_transfer_to_grants_flag
761 <> nvl(pqh_bgt_shd.g_old_rec.transfer_to_grants_flag,hr_api.g_varchar2)
762 or not l_api_updating)
763 and p_transfer_to_grants_flag is not null then
764 --
765 -- check if value of lookup falls within lookup type.
766 --
767 if hr_api.not_exists_in_hr_lookups
768 (p_lookup_type => 'YES_NO',
769 p_lookup_code => p_transfer_to_grants_flag,
770 p_effective_date => p_effective_date) then
771 --
772 -- raise error as does not exist as lookup
773 --
774 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
775 hr_utility.raise_error;
776 --
777 end if;
778 --
779 end if;
780 if nvl(p_transfer_to_grants_flag,'N') ='Y' then
781 if nvl(p_position_control_flag,'N') ='N' then
782 -- Budget must be a Controlled budget to be marked transfer to grants
783 -- raise error
784 hr_utility.set_message(8302,'PQH_TRANSFER_TO_GRANTS');
785 hr_utility.raise_error;
786 end if;
787 end if;
788 --
789 hr_utility.set_location('Leaving:'||l_proc,10);
790 --
791 end chk_transfer_to_grants_flag;
792 --
793 -- ---------------------------------------------------------------------------+
794 -- |------< chk_status >------|
795 -- ---------------------------------------------------------------------------+
796 --
797 Procedure chk_status(p_budget_id in number,
798 p_status in varchar2,
799 p_effective_date in date,
800 p_object_version_number in number) is
801 --
802 l_proc varchar2(72) := g_package||'chk_status';
803 l_api_updating boolean;
804 --
805 Begin
806 --
807 hr_utility.set_location('Entering:'||l_proc, 5);
808 --
812 --
809 l_api_updating := pqh_bgt_shd.api_updating
810 (p_budget_id => p_budget_id,
811 p_object_version_number => p_object_version_number);
813 if (l_api_updating
814 and p_status
815 <> nvl(pqh_bgt_shd.g_old_rec.status,hr_api.g_varchar2)
816 or not l_api_updating)
817 and p_status is not null then
818 --
819 -- check if value of lookup falls within lookup type.
820 --
821 if p_status <> 'FROZEN' then
822 --
823 -- raise error as does not exist as lookup
824 --
825 hr_utility.set_message(8302,'PQH_INVAILD_BUDGET_STATUS');
826 hr_utility.raise_error;
827 --
828 end if;
829 --
830 end if;
831 --
832 hr_utility.set_location('Leaving:'||l_proc,10);
833 --
834 end chk_status;
835 --
836 -- ----------------------------------------------------------------------------+
837 -- |------< chk_budget_style_cd >------|
838 -- ----------------------------------------------------------------------------+
839 --
840 -- Description
841 -- This procedure is used to check that the lookup value is valid.
842 --
843 -- Pre Conditions
844 -- None.
845 --
846 -- In Parameters
847 -- budget_id PK of record being inserted or updated.
848 -- budget_style_cd Value of lookup code.
849 -- effective_date effective date
850 -- object_version_number Object version number of record being
851 -- inserted or updated.
852 --
853 -- Post Success
854 -- Processing continues
855 --
856 -- Post Failure
857 -- Error handled by procedure
858 --
859 -- Access Status
860 -- Internal table handler use only.
861 --
862 Procedure chk_budget_style_cd(p_budget_id in number,
863 p_budget_style_cd in varchar2,
864 p_effective_date in date,
865 p_object_version_number in number) is
866 --
867 l_proc varchar2(72) := g_package||'chk_budget_style_cd';
868 l_api_updating boolean;
869 --
870 Begin
871 --
872 hr_utility.set_location('Entering:'||l_proc, 5);
873 --
874 l_api_updating := pqh_bgt_shd.api_updating
875 (p_budget_id => p_budget_id,
876 p_object_version_number => p_object_version_number);
877 --
878 if (l_api_updating
879 and p_budget_style_cd
880 <> nvl(pqh_bgt_shd.g_old_rec.budget_style_cd,hr_api.g_varchar2)
881 or not l_api_updating) then
882 --
883 -- check if value of lookup falls within lookup type.
884 --
885 --
886 if hr_api.not_exists_in_hr_lookups
887 (p_lookup_type => 'PQH_BUDGET_STYLE',
888 p_lookup_code => p_budget_style_cd,
889 p_effective_date => p_effective_date) then
890 --
891 -- raise error as does not exist as lookup
892 --
893 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
894 hr_utility.raise_error;
895 --
896 end if;
897 --
898 end if;
899 --
900 hr_utility.set_location('Leaving:'||l_proc,10);
901 --
902 end chk_budget_style_cd;
903 --
904 -- ----------------------------------------------------------------------------+
905 -- |------< chk_budgeted_entity_cd >------|
906 -- ----------------------------------------------------------------------------+
907 --
908 -- Description
909 -- This procedure is used to check that the lookup value is valid.
910 --
911 -- Pre Conditions
912 -- None.
913 --
914 -- In Parameters
915 -- budget_id PK of record being inserted or updated.
916 -- budgeted_entity_cd Value of lookup code.
917 -- effective_date effective date
918 -- object_version_number Object version number of record being
919 -- inserted or updated.
920 --
921 -- Post Success
922 -- Processing continues
923 --
924 -- Post Failure
925 -- Error handled by procedure
926 --
927 -- Access Status
928 -- Internal table handler use only.
929 --
930 Procedure chk_budgeted_entity_cd(p_budget_id in number,
931 p_budgeted_entity_cd in varchar2,
932 p_effective_date in date,
933 p_object_version_number in number) is
934 --
935 l_proc varchar2(72) := g_package||'chk_budgeted_entity_cd';
936 l_api_updating boolean;
937 --
938 Begin
939 --
940 hr_utility.set_location('Entering:'||l_proc, 5);
941 --
942 l_api_updating := pqh_bgt_shd.api_updating
943 (p_budget_id => p_budget_id,
944 p_object_version_number => p_object_version_number);
945 --
946 if (l_api_updating
947 and p_budgeted_entity_cd
948 <> nvl(pqh_bgt_shd.g_old_rec.budgeted_entity_cd,hr_api.g_varchar2)
949 or not l_api_updating)
950 and p_budgeted_entity_cd is not null then
951 --
952 -- check if value of lookup falls within lookup type.
953 --
954 if hr_api.not_exists_in_hr_lookups
955 (p_lookup_type => 'PQH_BUDGET_ENTITY',
959 -- raise error as does not exist as lookup
956 p_lookup_code => p_budgeted_entity_cd,
957 p_effective_date => p_effective_date) then
958 --
960 --
961 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
962 hr_utility.raise_error;
963 --
964 end if;
965 --
966 end if;
967 --
968 hr_utility.set_location('Leaving:'||l_proc,10);
969 --
970 end chk_budgeted_entity_cd;
971 --
972 -- ---------------------------------------------------------------------------+
973 -- |------< chk_budget_start_date >------|
974 -- ---------------------------------------------------------------------------+
975 --
976 -- Description
977 -- This procedure is used to check that the lookup value is valid.
978 --
979 -- Pre Conditions
980 -- None.
981 --
982 -- In Parameters
983 -- budget_id PK of record being inserted or updated.
984 -- budget_start_date Value of lookup code.
985 -- effective_date effective date
986 -- object_version_number Object version number of record being
987 -- inserted or updated.
988 --
989 -- Post Success
990 -- Processing continues
991 --
992 -- Post Failure
993 -- Error handled by procedure
994 --
995 -- Access Status
996 -- Internal table handler use only.
997 --
998 Procedure chk_budget_start_date(p_budget_id in number,
999 p_budget_start_date in date,
1000 p_budget_end_date in date,
1001 p_period_set_name in varchar2,
1002 p_object_version_number in number) is
1003 --
1004 l_proc varchar2(72) := g_package||'chk_budget_start_date';
1005 l_api_updating boolean;
1006 l_min_budget_dt date;
1007 l_min_wks_dt date;
1008 l_min_cal_dt date;
1009 l_cnt_periods number(9);
1010 --
1011 --
1012 --
1013 -- cursor to check if atleast one period exists
1014
1015 CURSOR cnt_periods IS
1016 SELECT COUNT(*)
1017 FROM per_time_periods
1018 WHERE period_set_name = p_period_set_name
1019 AND start_date BETWEEN p_budget_start_date AND p_budget_end_date
1020 AND end_date BETWEEN p_budget_start_date AND p_budget_end_date ;
1021
1022 -- cursor to check start_date in per_time_periods table
1023 CURSOR per_cal_start_dt_cur IS
1024 SELECT MIN(start_date)
1025 FROM per_time_periods
1026 WHERE period_set_name = p_period_set_name;
1027
1028 -- cursor to check in budget tables
1029 CURSOR budget_date_cur IS
1030 SELECT MIN(start_date)
1031 FROM per_time_periods tp,
1032 pqh_budget_periods bpr,
1033 pqh_budget_details bdt,
1034 pqh_budget_versions bvr
1035 WHERE time_period_id = bpr.start_time_period_id
1036 and bpr.budget_detail_id = bdt.budget_detail_id
1037 AND bdt.budget_version_id = bvr.budget_version_id
1038 and bvr.budget_id = p_budget_id;
1039
1040 -- cursor to check in worksheet tables
1041 CURSOR wks_date_cur IS
1042 SELECT MIN(start_date)
1043 FROM per_time_periods tp,
1044 pqh_worksheet_periods bpr,
1045 pqh_worksheet_details bdt,
1046 pqh_worksheets bvr
1047 WHERE time_period_id = bpr.start_time_period_id
1048 and bpr.worksheet_detail_id = bdt.worksheet_detail_id
1049 AND bdt.worksheet_id = bvr.worksheet_id
1050 AND nvl(bvr.transaction_status,'PENDING') = 'PENDING'
1051 and bvr.budget_id = p_budget_id;
1052
1053 Begin
1054 --
1055 hr_utility.set_location('Entering:'||l_proc, 5);
1056 --
1057
1058 l_api_updating := pqh_bgt_shd.api_updating
1059 (p_budget_id => p_budget_id,
1060 p_object_version_number => p_object_version_number);
1061 --
1062 if (l_api_updating
1063 and p_budget_start_date
1064 <> nvl(pqh_bgt_shd.g_old_rec.budget_start_date,hr_api.g_date)
1065 or not l_api_updating)
1066 and p_budget_start_date is not null then
1067 --
1068 -- check if value of lookup falls within lookup type.
1069 --
1070 OPEN budget_date_cur;
1071 FETCH budget_date_cur INTO l_min_budget_dt;
1072 CLOSE budget_date_cur;
1073 --
1074 OPEN wks_date_cur;
1075 FETCH wks_date_cur INTO l_min_wks_dt;
1076 CLOSE wks_date_cur;
1077
1078 if (l_min_budget_dt IS NOT NULL) THEN
1079 if (p_budget_start_date > l_min_budget_dt) THEN
1080 --
1081 -- raise error as does not exist as lookup
1082 --
1083 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_ST_DT');
1084 hr_utility.set_message_token('STARTDATE',to_char(l_min_budget_dt,'DD-MM-RRRR'));
1085 hr_utility.raise_error;
1086 --
1087 end if;
1088 elsif (l_min_wks_dt IS NOT NULL) THEN
1089 if (p_budget_start_date > l_min_wks_dt) THEN
1090 --
1091 -- raise error as does not exist as lookup
1092 --
1093 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_ST_DT');
1094 hr_utility.set_message_token('STARTDATE',to_char(l_min_budget_dt,'DD-MM-RRRR'));
1095 hr_utility.raise_error;
1096 --
1097 end if;
1098
1099 end if;
1100 --
1104 hr_utility.set_message(8302,'PQH_INVALID_END_DT');
1101 -- check if start_date is less then end date
1102 if p_budget_start_date > p_budget_end_date then
1103 -- raise error as invalid date
1105 hr_utility.set_message_token('STARTDATE',to_char(p_budget_start_date,'DD-MM-RRRR'));
1106 hr_utility.set_message_token('ENDDATE',to_char(p_budget_end_date,'DD-MM-RRRR'));
1107 hr_utility.raise_error;
1108 end if;
1109 --
1110 --
1111 /*
1112 check if the budget_start_dt is >= the minimum date (start_date) in per_time_periods
1113 where period_set_name = period_set_name of the current budget
1114 */
1115 OPEN per_cal_start_dt_cur;
1116 FETCH per_cal_start_dt_cur INTO l_min_cal_dt;
1117 CLOSE per_cal_start_dt_cur;
1118
1119 if p_budget_start_date < l_min_cal_dt then
1120 --
1121 hr_utility.set_message(8302,'PQH_BUDGET_ST_DT_CAL');
1122 hr_utility.set_message_token('CALSTART',to_char(l_min_cal_dt,'DD-MM-RRRR'));
1123 hr_utility.raise_error;
1124 --
1125 end if;
1126 --
1127 --
1128 --
1129 -- check if atleast one period exists between the budget start and end date
1130 OPEN cnt_periods;
1131 FETCH cnt_periods INTO l_cnt_periods;
1132 CLOSE cnt_periods;
1133
1134 IF NVL(l_cnt_periods,0) = 0 THEN
1135 -- error as no periods
1136 --
1137 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_DTS');
1138 hr_utility.raise_error;
1139 --
1140 END IF;
1141 --
1142 --
1143 end if;
1144 --
1145 hr_utility.set_location('Leaving:'||l_proc,10);
1146 --
1147 end chk_budget_start_date;
1148 --
1149 -- ---------------------------------------------------------------------------+
1150 -- |------< chk_budget_end_date >------|
1151 -- ---------------------------------------------------------------------------+
1152 --
1153 -- Description
1154 -- This procedure is used to check that the lookup value is valid.
1155 --
1156 -- Pre Conditions
1157 -- None.
1158 --
1159 -- In Parameters
1160 -- budget_id PK of record being inserted or updated.
1161 -- budget_end_date Value of lookup code.
1162 -- effective_date effective date
1163 -- object_version_number Object version number of record being
1164 -- inserted or updated.
1165 --
1166 -- Post Success
1167 -- Processing continues
1168 --
1169 -- Post Failure
1170 -- Error handled by procedure
1171 --
1172 -- Access Status
1173 -- Internal table handler use only.
1174 --
1175 Procedure chk_budget_end_date(p_budget_id in number,
1176 p_budget_start_date in date,
1177 p_budget_end_date in date,
1178 p_period_set_name in varchar2,
1179 p_object_version_number in number) is
1180 --
1181 l_proc varchar2(72) := g_package||'chk_budget_end_date';
1182 l_api_updating boolean;
1183 l_max_budget_dt date;
1184 l_max_wks_dt date;
1185 l_max_cal_dt date;
1186 l_cnt_periods number(9);
1187 --
1188 --
1189 --
1190 -- cursor to check if atleast one period exists
1191
1192 CURSOR cnt_periods IS
1193 SELECT COUNT(*)
1194 FROM per_time_periods
1195 WHERE period_set_name = p_period_set_name
1196 AND start_date BETWEEN p_budget_start_date AND p_budget_end_date
1197 AND end_date BETWEEN p_budget_start_date AND p_budget_end_date ;
1198 --
1199
1200 -- cursor to check end_date in per_time_periods table
1201 CURSOR per_cal_end_dt_cur IS
1202 SELECT MAX(end_date)
1203 FROM per_time_periods
1204 WHERE period_set_name = p_period_set_name;
1205 --
1206 -- cursor to check in budget tables
1207 CURSOR budget_date_cur IS
1208 SELECT MIN(start_date)
1209 FROM per_time_periods tp,
1210 pqh_budget_periods bpr,
1211 pqh_budget_details bdt,
1212 pqh_budget_versions bvr
1213 WHERE time_period_id = bpr.start_time_period_id
1214 and bpr.budget_detail_id = bdt.budget_detail_id
1215 AND bdt.budget_version_id = bvr.budget_version_id
1216 and bvr.budget_id = p_budget_id;
1217
1218 -- cursor to check in worksheet tables
1219 CURSOR wks_date_cur IS
1220 SELECT MIN(start_date)
1221 FROM per_time_periods tp,
1222 pqh_worksheet_periods bpr,
1223 pqh_worksheet_details bdt,
1224 pqh_worksheets bvr
1225 WHERE time_period_id = bpr.start_time_period_id
1226 and bpr.worksheet_detail_id = bdt.worksheet_detail_id
1227 AND bdt.worksheet_id = bvr.worksheet_id
1228 AND nvl(bvr.transaction_status,'PENDING') = 'PENDING'
1229 and bvr.budget_id = p_budget_id;
1230
1231 Begin
1232 --
1233 hr_utility.set_location('Entering:'||l_proc, 5);
1234 --
1235
1236 l_api_updating := pqh_bgt_shd.api_updating
1237 (p_budget_id => p_budget_id,
1238 p_object_version_number => p_object_version_number);
1239 --
1240 if (l_api_updating
1241 and p_budget_end_date
1242 <> nvl(pqh_bgt_shd.g_old_rec.budget_end_date,hr_api.g_date)
1243 or not l_api_updating)
1244 and p_budget_end_date is not null then
1245 --
1249 FETCH budget_date_cur INTO l_max_budget_dt;
1246 -- check if value of lookup falls within lookup type.
1247 --
1248 OPEN budget_date_cur;
1250 CLOSE budget_date_cur;
1251 --
1252 OPEN wks_date_cur;
1253 FETCH wks_date_cur INTO l_max_wks_dt;
1254 CLOSE wks_date_cur;
1255
1256 if (l_max_budget_dt IS NOT NULL) THEN
1257 if (p_budget_end_date < l_max_budget_dt) THEN
1258 --
1259 -- raise error as does not exist as lookup
1260 --
1261 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_END_DT');
1262 hr_utility.set_message_token('ENDDATE',to_char(l_max_budget_dt,'DD-MM-RRRR'));
1263 hr_utility.raise_error;
1264 --
1265 end if;
1266 elsif (l_max_wks_dt IS NOT NULL) THEN
1267 if (p_budget_end_date < l_max_wks_dt) THEN
1268 --
1269 -- raise error as does not exist as lookup
1270 --
1271 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_END_DT');
1272 hr_utility.set_message_token('ENDDATE',to_char(l_max_budget_dt,'DD-MM-RRRR'));
1273 hr_utility.raise_error;
1274 --
1275 end if;
1276 end if;
1277 --
1278 -- check if start_date is less then end date
1279 if p_budget_start_date > p_budget_end_date then
1280 -- raise error as invalid date
1281 hr_utility.set_message(8302,'PQH_INVALID_END_DT');
1282 hr_utility.set_message_token('STARTDATE',to_char(p_budget_start_date,'DD-MM-RRRR'));
1283 hr_utility.set_message_token('ENDDATE',to_char(p_budget_end_date,'DD-MM-RRRR'));
1284 hr_utility.raise_error;
1285 end if;
1286 --
1287 --
1288 /*
1289 check if the budget_end_dt is <= the maximum date (end_date) in per_time_periods
1290 where period_set_name = period_set_name of the current budget
1291 */
1292 OPEN per_cal_end_dt_cur;
1293 FETCH per_cal_end_dt_cur INTO l_max_cal_dt;
1294 CLOSE per_cal_end_dt_cur;
1295
1296 if p_budget_end_date > l_max_cal_dt then
1297 --
1298 hr_utility.set_message(8302,'PQH_BUDGET_END_DT_CAL');
1299 hr_utility.set_message_token('CALENDT',to_char(l_max_cal_dt,'DD-MM-RRRR'));
1300 hr_utility.raise_error;
1301 --
1302 end if;
1303 --
1304 --
1305 -- check if atleast one period exists between the budget start and end date
1306 OPEN cnt_periods;
1307 FETCH cnt_periods INTO l_cnt_periods;
1308 CLOSE cnt_periods;
1309
1310 IF NVL(l_cnt_periods,0) = 0 THEN
1311 -- error as no periods
1312 --
1313 hr_utility.set_message(8302,'PQH_INVALID_BUDGET_DTS');
1314 hr_utility.raise_error;
1315 --
1316 END IF;
1317 --
1318 --
1319 end if;
1320 --
1321 hr_utility.set_location('Leaving:'||l_proc,10);
1322 --
1323 end chk_budget_end_date;
1324 --
1325 -- ----------------------------------------------------------------------------+
1326 -- |---------------------------< chk_budget_unit_id >----------------------------|
1327 -- ----------------------------------------------------------------------------+
1328 Procedure chk_budget_unit_id(p_budget_unit1_id in number,
1329 p_budget_unit2_id in number,
1330 p_budget_unit3_id in number,
1331 p_position_control_flag in varchar2) is
1332 --
1333 l_proc varchar2(72) := g_package||'chk_budget_unit_id';
1334
1335 cursor csr_lookup_cd(p_shared_type_id in number) is
1336 select system_type_cd
1337 from per_shared_types
1338 where shared_type_id = p_shared_type_id;
1339
1340 l_system_type_cd1 VARCHAR2(50);
1341 l_system_type_cd2 VARCHAR2(50);
1342 l_system_type_cd3 VARCHAR2(50);
1343 --
1344 Begin
1345 --
1346 hr_utility.set_location('Entering:'||l_proc, 5);
1347 --
1348 -- check for duplicate UOM
1349 -- compare 1 and 2
1350
1351 if p_budget_unit1_id IS NOT NULL and p_budget_unit2_id IS NOT NULL then
1352 if p_budget_unit1_id = p_budget_unit2_id then
1353 -- we have duplicate so error message
1354 --
1355 hr_utility.set_message(8302,'PQH_DUPLICATE_UOM');
1356 hr_utility.raise_error;
1357 --
1358
1359 end if;
1360
1361 -- get the system_type_cd for both and compare the system_type_cd i.e lookup_cd
1362 OPEN csr_lookup_cd(p_shared_type_id => p_budget_unit1_id);
1363 FETCH csr_lookup_cd INTO l_system_type_cd1;
1364 CLOSE csr_lookup_cd;
1365
1366 OPEN csr_lookup_cd(p_shared_type_id => p_budget_unit2_id);
1367 FETCH csr_lookup_cd INTO l_system_type_cd2;
1368 CLOSE csr_lookup_cd;
1369
1370 if NVL(p_position_control_flag,'N') = 'Y' then
1371 -- compare the system_type_cd
1372 if l_system_type_cd1 = l_system_type_cd2 then
1373 -- we have duplicate lookup code for PC budget, so error message
1374 --
1375 hr_utility.set_message(8302,'PQH_DUP_SYSTEM_TYPE_CD');
1376 hr_utility.set_message_token('UNIT_ONE','Unit1');
1380
1377 hr_utility.set_message_token('UNIT_TWO','Unit2');
1378 hr_utility.raise_error;
1379 --
1381 end if; -- end compare the system_type_cd for PC budget
1382 end if; -- p_position_control_flag is Y
1383
1384
1385 end if; -- either is null so no compare
1386
1387 --
1388 -- compare 2 and 3
1389
1390 if p_budget_unit2_id IS NOT NULL and p_budget_unit3_id IS NOT NULL then
1391 if p_budget_unit2_id = p_budget_unit3_id then
1392 -- we have duplicate so error message
1393 --
1394 hr_utility.set_message(8302,'PQH_DUPLICATE_UOM');
1395 hr_utility.raise_error;
1396 --
1397
1398 end if;
1399
1400 -- get the system_type_cd for both and compare the system_type_cd i.e lookup_cd
1401 OPEN csr_lookup_cd(p_shared_type_id => p_budget_unit2_id);
1402 FETCH csr_lookup_cd INTO l_system_type_cd2;
1403 CLOSE csr_lookup_cd;
1404
1405 OPEN csr_lookup_cd(p_shared_type_id => p_budget_unit3_id);
1406 FETCH csr_lookup_cd INTO l_system_type_cd3;
1407 CLOSE csr_lookup_cd;
1408
1409 if NVL(p_position_control_flag,'N') = 'Y' then
1410 -- compare the system_type_cd
1411 if l_system_type_cd2 = l_system_type_cd3 then
1412 -- we have duplicate lookup code for PC budget, so error message
1413 --
1414 hr_utility.set_message(8302,'PQH_DUP_SYSTEM_TYPE_CD');
1415 hr_utility.set_message_token('UNIT_ONE','Unit2');
1416 hr_utility.set_message_token('UNIT_TWO','Unit3');
1417 hr_utility.raise_error;
1418 --
1419
1420 end if; -- end compare the system_type_cd for PC budget
1421 end if; -- p_position_control_flag is Y
1422
1423 end if; -- either is null so no compare
1424
1425 --
1426 -- compare 3 and 1
1427
1428 if p_budget_unit3_id IS NOT NULL and p_budget_unit1_id IS NOT NULL then
1429 if p_budget_unit3_id = p_budget_unit1_id then
1430 -- we have duplicate so error message
1431 --
1432 hr_utility.set_message(8302,'PQH_DUPLICATE_UOM');
1433 hr_utility.raise_error;
1434 --
1435
1436 end if;
1437
1438 -- get the system_type_cd for both and compare the system_type_cd i.e lookup_cd
1439 OPEN csr_lookup_cd(p_shared_type_id => p_budget_unit3_id);
1440 FETCH csr_lookup_cd INTO l_system_type_cd3;
1441 CLOSE csr_lookup_cd;
1442
1443 OPEN csr_lookup_cd(p_shared_type_id => p_budget_unit1_id);
1444 FETCH csr_lookup_cd INTO l_system_type_cd1;
1445 CLOSE csr_lookup_cd;
1446
1447 if NVL(p_position_control_flag,'N') = 'Y' then
1448 -- compare the system_type_cd
1449 if l_system_type_cd3 = l_system_type_cd1 then
1450 -- we have duplicate lookup code for PC budget, so error message
1451 --
1452 hr_utility.set_message(8302,'PQH_DUP_SYSTEM_TYPE_CD');
1453 hr_utility.set_message_token('UNIT_ONE','Unit1');
1454 hr_utility.set_message_token('UNIT_TWO','Unit3');
1455 hr_utility.raise_error;
1456 --
1457
1458 end if; -- end compare the system_type_cd for PC budget
1459 end if; -- p_position_control_flag is Y
1460
1461
1462 end if; -- either is null so no compare
1463
1464 --
1465 hr_utility.set_location('Leaving:'||l_proc,10);
1466 --
1467 end chk_budget_unit_id;
1468
1469 --
1470 -- ---------------------------------------------------------------------------+
1471 -- |---------------------------< chk_budget_name >----------------------------|
1472 -- ---------------------------------------------------------------------------+
1473 Procedure chk_budget_name (p_budget_id in number,
1474 p_budget_name in varchar2) is
1475 --
1476 l_proc varchar2(72) := g_package||'chk_budget_name';
1477 --
1478 l_dummy varchar2(1) ;
1479
1480 cursor csr_budget_name is
1481 select 'X'
1482 from pqh_budgets
1483 where budget_name = p_budget_name
1484 and budget_id <> nvl(p_budget_id,0);
1485
1486 Begin
1487 --
1488 hr_utility.set_location('Entering:'||l_proc, 5);
1489 --
1490 open csr_budget_name;
1491 fetch csr_budget_name into l_dummy;
1492 close csr_budget_name;
1493
1494 if nvl(l_dummy ,'Y') = 'X' then
1495 --
1496 hr_utility.set_message(8302,'PQH_DUPLICATE_BUDGET_NAME');
1497 hr_utility.raise_error;
1498 --
1499 end if;
1500
1501 --
1502 hr_utility.set_location('Leaving:'||l_proc,10);
1503 --
1504 end chk_budget_name;
1505
1506 --
1507 -- ---------------------------------------------------------------------------+
1508 -- |------< chk_budget_unit1_aggregate >------|
1509 -- ---------------------------------------------------------------------------+
1510 --
1511 Procedure chk_budget_unit1_aggregate(p_budget_id in number,
1512 p_budget_unit1_id in number,
1513 p_budget_unit1_aggregate in varchar2,
1514 p_effective_date in date,
1518 l_api_updating boolean;
1515 p_object_version_number in number) is
1516 --
1517 l_proc varchar2(72) := g_package||'chk_budget_unit1_aggregate';
1519 l_uom_cd varchar2(50);
1520 l_shared_type_name varchar2(50);
1521
1522 cursor csr_unit is
1523 select a.system_type_cd,
1524 a.shared_type_name
1525 from per_shared_types a
1526 where a.shared_type_id = p_budget_unit1_id;
1527
1528 --
1529 Begin
1530 --
1531 hr_utility.set_location('Entering:'||l_proc, 5);
1532 --
1533 l_api_updating := pqh_bgt_shd.api_updating
1534 (p_budget_id => p_budget_id,
1535 p_object_version_number => p_object_version_number);
1536 --
1537 if (l_api_updating
1538 and
1539 (
1540 p_budget_unit1_aggregate
1541 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit1_aggregate,hr_api.g_varchar2)
1542 or
1543 p_budget_unit1_id
1544 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit1_id,hr_api.g_number)
1545 )
1546 or not l_api_updating) then
1547 --
1548 -- check if value of lookup falls within lookup type.
1549 --
1550 --
1551 if hr_api.not_exists_in_hr_lookups
1552 (p_lookup_type => 'PQH_BGT_UOM_AGGREGATE',
1553 p_lookup_code => p_budget_unit1_aggregate,
1554 p_effective_date => p_effective_date) then
1555 --
1556 -- raise error as does not exist as lookup
1557 --
1558 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1559 hr_utility.raise_error;
1560 --
1561 end if; -- invalid lookup
1562 --
1563 -- check if value is valid
1564 -- either both unit1_id and unit1_aggregate must be null or both not null
1565 --
1566 if (p_budget_unit1_id is not null and p_budget_unit1_aggregate is null ) or
1567 (p_budget_unit1_id is null and p_budget_unit1_aggregate is not null )then
1568 --
1569 -- invalid values
1570 --
1571 hr_utility.set_message(8302,'PQH_INVALID_UNIT_AGGREGATE');
1572 hr_utility.raise_error;
1573 --
1574
1575 end if; -- both null or not null
1576 --
1577 -- if unit1 is money then aggregate must be Accumulate
1578 --
1579 if p_budget_unit1_id is not null then
1580 open csr_unit;
1581 fetch csr_unit into l_uom_cd, l_shared_type_name;
1582 close csr_unit;
1583
1584 if l_uom_cd = 'MONEY' and p_budget_unit1_aggregate <> 'ACCUMULATE' then
1585 --
1586 -- invalid values
1587 --
1588 hr_utility.set_message(8302,'PQH_INVALID_AGGREGATE_VAL');
1589 hr_utility.set_message_token('UOM',l_shared_type_name);
1590 hr_utility.raise_error;
1591 --
1592 end if; -- invalid aggregate value for money
1593 end if; -- p_budget_unit1_id is not null
1594
1595 end if;
1596 --
1597 hr_utility.set_location('Leaving:'||l_proc,10);
1598 --
1599 end chk_budget_unit1_aggregate;
1600 --
1601 --
1602 -- ----------------------------------------------------------------------------
1603 -- |------< chk_budget_unit2_aggregate >------|
1604 -- ----------------------------------------------------------------------------
1605 --
1606 Procedure chk_budget_unit2_aggregate(p_budget_id in number,
1607 p_budget_unit2_id in number,
1608 p_budget_unit2_aggregate in varchar2,
1609 p_effective_date in date,
1610 p_object_version_number in number) is
1611 --
1612 l_proc varchar2(72) := g_package||'chk_budget_unit2_aggregate';
1613 l_api_updating boolean;
1614 l_uom_cd varchar2(50);
1615 l_shared_type_name varchar2(50);
1616
1617 cursor csr_unit is
1618 select a.system_type_cd,
1619 a.shared_type_name
1620 from per_shared_types a
1621 where a.shared_type_id = p_budget_unit2_id;
1622
1623 --
1624 Begin
1625 --
1626 hr_utility.set_location('Entering:'||l_proc, 5);
1627 --
1628 l_api_updating := pqh_bgt_shd.api_updating
1629 (p_budget_id => p_budget_id,
1630 p_object_version_number => p_object_version_number);
1631 --
1632 if (l_api_updating
1633 and
1634 (
1635 p_budget_unit2_aggregate
1636 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit2_aggregate,hr_api.g_varchar2)
1637 or
1638 p_budget_unit2_id
1639 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit2_id,hr_api.g_number)
1640 )
1641 or ( not l_api_updating and p_budget_unit2_aggregate IS NOT NULL)) then
1642 --
1643 -- check if value of lookup falls within lookup type.
1644 --
1645 --
1646 if hr_api.not_exists_in_hr_lookups
1647 (p_lookup_type => 'PQH_BGT_UOM_AGGREGATE',
1648 p_lookup_code => p_budget_unit2_aggregate,
1649 p_effective_date => p_effective_date) then
1650 --
1651 -- raise error as does not exist as lookup
1652 --
1653 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1657 --
1654 hr_utility.raise_error;
1655 --
1656 end if; -- invalid lookup
1658 -- check if value is valid
1659 -- either both unit2_id and unit2_aggregate must be null or both not null
1660 --
1661 if (p_budget_unit2_id is not null and p_budget_unit2_aggregate is null ) or
1662 (p_budget_unit2_id is null and p_budget_unit2_aggregate is not null )then
1663 --
1664 -- invalid values
1665 --
1666 hr_utility.set_message(8302,'PQH_INVALID_UNIT_AGGREGATE');
1667 hr_utility.raise_error;
1668 --
1669
1670 end if; -- both null or not null
1671 --
1672 -- if unit2 is money then aggregate must be Accumulate
1673 --
1674 if p_budget_unit2_id is not null then
1675 open csr_unit;
1676 fetch csr_unit into l_uom_cd, l_shared_type_name;
1677 close csr_unit;
1678
1679 if l_uom_cd = 'MONEY' and p_budget_unit2_aggregate <> 'ACCUMULATE' then
1680 --
1681 -- invalid values
1682 --
1683 hr_utility.set_message(8302,'PQH_INVALID_AGGREGATE_VAL');
1684 hr_utility.set_message_token('UOM',l_shared_type_name);
1685 hr_utility.raise_error;
1686 --
1687 end if; -- invalid aggregate value for money
1688 end if; -- p_budget_unit2_id is not null
1689
1690
1691
1692
1693
1694 end if;
1695 --
1696 hr_utility.set_location('Leaving:'||l_proc,10);
1697 --
1698 end chk_budget_unit2_aggregate;
1699 --
1700 --
1701 -- ----------------------------------------------------------------------------
1702 -- |------< chk_budget_unit3_aggregate >------|
1703 -- ----------------------------------------------------------------------------
1704 --
1705 Procedure chk_budget_unit3_aggregate(p_budget_id in number,
1706 p_budget_unit3_id in number,
1707 p_budget_unit3_aggregate in varchar2,
1708 p_effective_date in date,
1709 p_object_version_number in number) is
1710 --
1711 l_proc varchar2(72) := g_package||'chk_budget_unit3_aggregate';
1712 l_api_updating boolean;
1713 l_uom_cd varchar2(50);
1714 l_shared_type_name varchar2(50);
1715
1716 cursor csr_unit is
1717 select a.system_type_cd,
1718 a.shared_type_name
1719 from per_shared_types a
1720 where a.shared_type_id = p_budget_unit3_id;
1721
1722 --
1723 Begin
1724 --
1725 hr_utility.set_location('Entering:'||l_proc, 5);
1726 --
1727 l_api_updating := pqh_bgt_shd.api_updating
1728 (p_budget_id => p_budget_id,
1729 p_object_version_number => p_object_version_number);
1730 --
1731 if (l_api_updating
1732 and
1733 (
1734 p_budget_unit3_aggregate
1735 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit3_aggregate,hr_api.g_varchar2)
1736 or
1737 p_budget_unit3_id
1738 <> nvl(pqh_bgt_shd.g_old_rec.budget_unit3_id,hr_api.g_number)
1739 )
1740 or ( not l_api_updating and p_budget_unit3_aggregate IS NOT NULL)) then
1741 --
1742 -- check if value of lookup falls within lookup type.
1743 --
1744 --
1745 if hr_api.not_exists_in_hr_lookups
1746 (p_lookup_type => 'PQH_BGT_UOM_AGGREGATE',
1747 p_lookup_code => p_budget_unit3_aggregate,
1748 p_effective_date => p_effective_date) then
1749 --
1750 -- raise error as does not exist as lookup
1751 --
1752 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1753 hr_utility.raise_error;
1754 --
1755 end if; -- invalid lookup
1756 --
1757 -- check if value is valid
1758 -- either both unit3_id and unit3_aggregate must be null or both not null
1759 --
1760 if (p_budget_unit3_id is not null and p_budget_unit3_aggregate is null ) or
1761 (p_budget_unit3_id is null and p_budget_unit3_aggregate is not null )then
1762 --
1763 -- invalid values
1764 --
1765 hr_utility.set_message(8302,'PQH_INVALID_UNIT_AGGREGATE');
1766 hr_utility.raise_error;
1767 --
1768
1769 end if; -- both null or not null
1770 --
1771 -- if unit3 is money then aggregate must be Accumulate
1772 --
1773 if p_budget_unit3_id is not null then
1774 open csr_unit;
1775 fetch csr_unit into l_uom_cd, l_shared_type_name;
1776 close csr_unit;
1777
1778 if l_uom_cd = 'MONEY' and p_budget_unit3_aggregate <> 'ACCUMULATE' then
1779 --
1780 -- invalid values
1781 --
1782 hr_utility.set_message(8302,'PQH_INVALID_AGGREGATE_VAL');
1783 hr_utility.set_message_token('UOM',l_shared_type_name);
1784 hr_utility.raise_error;
1785 --
1786 end if; -- invalid aggregate value for money
1787 end if; -- p_budget_unit3_id is not null
1788
1789
1790
1791
1792
1793 end if;
1794 --
1795 hr_utility.set_location('Leaving:'||l_proc,10);
1799 -- ----------------------------------------------------------------------------
1796 --
1797 end chk_budget_unit3_aggregate;
1798 --
1800 -- |------< chk_position_control_flag >------|
1801 -- ----------------------------------------------------------------------------
1802 --
1803 Procedure chk_position_control_flag(p_budget_id in number,
1804 p_position_control_flag in varchar2,
1805 p_budgeted_entity_cd in varchar2,
1806 p_effective_date in date,
1807 p_object_version_number in number) is
1808 --
1809 l_proc varchar2(72) := g_package||'chk_position_control_flag';
1810 l_api_updating boolean;
1811 --
1812 Begin
1813 --
1814 hr_utility.set_location('Entering:'||l_proc, 5);
1815 --
1816 l_api_updating := pqh_bgt_shd.api_updating
1817 (p_budget_id => p_budget_id,
1818 p_object_version_number => p_object_version_number);
1819 --
1820 if (l_api_updating
1821 and p_position_control_flag
1822 <> nvl(pqh_bgt_shd.g_old_rec.position_control_flag,hr_api.g_varchar2)
1823 or not l_api_updating)
1824 and p_position_control_flag is not null then
1825 --
1826 -- check if value of lookup falls within lookup type.
1827 --
1828 if hr_api.not_exists_in_hr_lookups
1829 (p_lookup_type => 'YES_NO',
1830 p_lookup_code => p_position_control_flag,
1831 p_effective_date => p_effective_date) then
1832 --
1833 -- raise error as does not exist as lookup
1834 --
1835 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1836 hr_utility.raise_error;
1837 --
1838 end if;
1839 --
1840 --
1841 /*
1842 -- commented out by sgoyal to make controlled budget span across any entity and
1843 -- not restricted by Position as entity.
1844 -- rest of things remain the same. At a given day we can have only one controlled
1845 -- budget for a BG and a unit type and it can have any entity.
1846 -- check if budgeted_entity_cd is POSITION for posn ctrl flag Y
1847 --
1848 if NVL(p_position_control_flag,'N') = 'Y' then
1849 if NVL(p_budgeted_entity_cd,'X') <> 'POSITION' then
1850 --
1851 -- raise error
1852 --
1853 hr_utility.set_message(8302,'PQH_INVALID_BDGT_ENTITY');
1854 hr_utility.raise_error;
1855
1856 end if;
1857 end if; -- pos ctl flag Y
1858 --
1859 --
1860 */
1861
1862 end if;
1863 --
1864 hr_utility.set_location('Leaving:'||l_proc,10);
1865 --
1866 end chk_position_control_flag;
1867
1868 --
1869 --
1870 -- ----------------------------------------------------------------------------
1871 -- |------< chk_currency_code >------|
1872 -- ----------------------------------------------------------------------------
1873 --
1874 Procedure chk_currency_code (p_budget_id in number,
1875 p_currency_code in varchar2,
1876 p_object_version_number in number) is
1877 --
1878 l_proc varchar2(72) := g_package||'chk_currency_code';
1879 l_api_updating boolean;
1880 l_dummy varchar2(1);
1881 --
1882 cursor c1 is
1883 select null
1884 from fnd_currencies a
1885 where a.currency_code = p_currency_code;
1886 --
1887 Begin
1888 --
1889 hr_utility.set_location('Entering:'||l_proc,5);
1890 --
1891 l_api_updating := pqh_bgt_shd.api_updating
1892 (p_budget_id => p_budget_id,
1893 p_object_version_number => p_object_version_number);
1894 --
1895 if (l_api_updating
1896 and nvl(p_currency_code,hr_api.g_varchar2)
1897 <> nvl(pqh_bgt_shd.g_old_rec.currency_code,hr_api.g_varchar2)
1898 or not l_api_updating) and
1899 p_currency_code is not null then
1900 --
1901 -- check if currency_code value exists in fnd_currencies table
1902 --
1903 open c1;
1904 --
1905 fetch c1 into l_dummy;
1906 if c1%notfound then
1907 --
1908 close c1;
1909 --
1910 -- raise error as FK does not relate to PK in fnd_currencies
1911 -- table.
1912 --
1913 pqh_bgt_shd.constraint_error('PQH_BUDGETS_FK5');
1914 --
1915 end if;
1916 --
1917 --
1918 close c1;
1919 --
1920 end if;
1921 --
1922 hr_utility.set_location('Leaving:'||l_proc,10);
1923 --
1924 End chk_currency_code;
1925
1926 --
1927 --
1928 -- ----------------------------------------------------------------------------
1929 -- |------< chk_psb_budget_flag >------|
1930 -- ----------------------------------------------------------------------------
1931 --
1932 -- Description
1933 -- This procedure is used to check that the lookup value is valid.
1934 --
1935 -- Pre Conditions
1936 -- None.
1937 --
1938 -- In Parameters
1939 -- budget_id PK of record being inserted or updated.
1940 -- psb_budget_flag Value of lookup code.
1941 -- effective_date effective date
1942 -- object_version_number Object version number of record being
1943 -- inserted or updated.
1947 --
1944 --
1945 -- Post Success
1946 -- Processing continues
1948 -- Post Failure
1949 -- Error handled by procedure
1950 --
1951 -- Access Status
1952 -- Internal table handler use only.
1953 --
1954 Procedure chk_psb_budget_flag(p_budget_id in number,
1955 p_psb_budget_flag in varchar2,
1956 p_gl_budget_name in varchar2,
1957 p_effective_date in date,
1958 p_object_version_number in number) is
1959 --
1960 l_proc varchar2(72) := g_package||'chk_psb_budget_flag';
1961 l_api_updating boolean;
1962 --
1963 Begin
1964 --
1965 hr_utility.set_location('Entering:'||l_proc, 5);
1966 --
1967 l_api_updating := pqh_bgt_shd.api_updating
1968 (p_budget_id => p_budget_id,
1969 p_object_version_number => p_object_version_number);
1970 --
1971 if (l_api_updating
1972 and (p_psb_budget_flag <> nvl(pqh_bgt_shd.g_old_rec.psb_budget_flag,hr_api.g_varchar2)
1973 or p_gl_budget_name <> nvl(pqh_bgt_shd.g_old_rec.gl_budget_name,hr_api.g_varchar2))
1974 or not l_api_updating)
1975 and p_psb_budget_flag is not null then
1976 hr_utility.set_location('check is being fired'||l_proc, 7);
1977 --
1978 -- check if value of lookup falls within lookup type.
1979 --
1980 if hr_api.not_exists_in_hr_lookups
1981 (p_lookup_type => 'YES_NO',
1982 p_lookup_code => p_psb_budget_flag,
1983 p_effective_date => p_effective_date) then
1984 --
1985 -- raise error as does not exist as lookup
1986 --
1987 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1988 hr_utility.raise_error;
1989 --
1990 end if;
1991 --
1992 if NVL(p_psb_budget_flag,'N') = 'Y' AND p_gl_budget_name is NULL then
1993 --
1994 -- raise error
1995 --
1996 hr_utility.set_message(8302,'PQH_NOT_A_GL_BUDGET');
1997 hr_utility.raise_error;
1998 end if;
1999 end if;
2000 --
2001 hr_utility.set_location('Leaving:'||l_proc,10);
2002 --
2003 end chk_psb_budget_flag;
2004
2005 --
2006 --
2007 -- ----------------------------------------------------------------------------
2008 -- |---------------------------< insert_validate >----------------------------|
2009 -- ----------------------------------------------------------------------------
2010 Procedure insert_validate(p_rec in pqh_bgt_shd.g_rec_type
2011 ,p_effective_date in date) is
2012 --
2013 l_proc varchar2(72) := g_package||'insert_validate';
2014 --
2015 Begin
2016 hr_utility.set_location('Entering:'||l_proc, 5);
2017 --
2018 -- Call all supporting business operations
2019 --
2020 chk_budget_id
2021 (p_budget_id => p_rec.budget_id,
2022 p_object_version_number => p_rec.object_version_number);
2023 --
2024 chk_budget_unit3_id
2025 (p_budget_id => p_rec.budget_id,
2026 p_status => p_rec.status,
2027 p_budget_unit3_id => p_rec.budget_unit3_id,
2028 p_object_version_number => p_rec.object_version_number);
2029 --
2030 chk_budget_unit2_id
2031 (p_budget_id => p_rec.budget_id,
2032 p_status => p_rec.status,
2033 p_budget_unit2_id => p_rec.budget_unit2_id,
2034 p_object_version_number => p_rec.object_version_number);
2035 --
2036 chk_budget_unit1_id
2037 (p_budget_id => p_rec.budget_id,
2038 p_status => p_rec.status,
2039 p_budget_unit1_id => p_rec.budget_unit1_id,
2040 p_object_version_number => p_rec.object_version_number);
2041 --
2042 chk_period_set_name
2043 (p_budget_id => p_rec.budget_id,
2044 p_period_set_name => p_rec.period_set_name,
2045 p_object_version_number => p_rec.object_version_number);
2046 --
2047 chk_transfer_to_gl
2048 (p_budget_id => p_rec.budget_id,
2049 p_gl_budget_name => p_rec.gl_budget_name,
2050 p_gl_set_of_books_id => p_rec.gl_set_of_books_id,
2051 p_budget_start_date => p_rec.budget_start_date,
2052 p_budget_end_date => p_rec.budget_end_date,
2053 p_position_control_flag => p_rec.position_control_flag,
2054 p_effective_date => p_effective_date,
2055 p_object_version_number => p_rec.object_version_number);
2056 --
2057 chk_transfer_to_grants_flag
2058 (p_budget_id => p_rec.budget_id,
2059 p_transfer_to_grants_flag => p_rec.transfer_to_grants_flag,
2060 p_position_control_flag => p_rec.position_control_flag,
2061 p_effective_date => p_effective_date,
2062 p_object_version_number => p_rec.object_version_number);
2063 --
2064 chk_status
2065 (p_budget_id => p_rec.budget_id,
2066 p_status => p_rec.status,
2067 p_effective_date => p_effective_date,
2068 p_object_version_number => p_rec.object_version_number);
2069 --
2070 chk_budget_style_cd
2071 (p_budget_id => p_rec.budget_id,
2072 p_budget_style_cd => p_rec.budget_style_cd,
2073 p_effective_date => p_effective_date,
2074 p_object_version_number => p_rec.object_version_number);
2075 --
2076 chk_budgeted_entity_cd
2080 p_object_version_number => p_rec.object_version_number);
2077 (p_budget_id => p_rec.budget_id,
2078 p_budgeted_entity_cd => p_rec.budgeted_entity_cd,
2079 p_effective_date => p_effective_date,
2081 --
2082 chk_budget_start_date
2083 (p_budget_id => p_rec.budget_id,
2084 p_budget_start_date => p_rec.budget_start_date,
2085 p_budget_end_date => p_rec.budget_end_date,
2086 p_period_set_name => p_rec.period_set_name,
2087 p_object_version_number => p_rec.object_version_number);
2088 --
2089 chk_budget_end_date
2090 (p_budget_id => p_rec.budget_id,
2091 p_budget_start_date => p_rec.budget_start_date,
2092 p_budget_end_date => p_rec.budget_end_date,
2093 p_period_set_name => p_rec.period_set_name,
2094 p_object_version_number => p_rec.object_version_number);
2095 --
2096 chk_budget_unit_id
2097 (p_budget_unit1_id => p_rec.budget_unit1_id,
2098 p_budget_unit2_id => p_rec.budget_unit2_id,
2099 p_budget_unit3_id => p_rec.budget_unit3_id,
2100 p_position_control_flag => p_rec.position_control_flag);
2101 --
2102 chk_budget_name
2103 (p_budget_id => p_rec.budget_id,
2104 p_budget_name => p_rec.budget_name );
2105 --
2106 chk_budget_unit1_aggregate
2107 (p_budget_id => p_rec.budget_id,
2108 p_budget_unit1_id => p_rec.budget_unit1_id,
2109 p_budget_unit1_aggregate => p_rec.budget_unit1_aggregate,
2110 p_effective_date => p_effective_date,
2111 p_object_version_number => p_rec.object_version_number);
2112 --
2113 chk_budget_unit2_aggregate
2114 (p_budget_id => p_rec.budget_id,
2115 p_budget_unit2_id => p_rec.budget_unit2_id,
2116 p_budget_unit2_aggregate => p_rec.budget_unit2_aggregate,
2117 p_effective_date => p_effective_date,
2118 p_object_version_number => p_rec.object_version_number);
2119 --
2120 chk_budget_unit3_aggregate
2121 (p_budget_id => p_rec.budget_id,
2122 p_budget_unit3_id => p_rec.budget_unit3_id,
2123 p_budget_unit3_aggregate => p_rec.budget_unit3_aggregate,
2124 p_effective_date => p_effective_date,
2125 p_object_version_number => p_rec.object_version_number);
2126 --
2127 if nvl(p_rec.position_control_flag,'X') ='Y' then
2128 chk_pos_control_budget (p_budget_id => p_rec.budget_id,
2129 p_budgeted_entity_cd => p_rec.budgeted_entity_cd,
2130 p_business_group_id => p_rec.business_group_id,
2131 p_budget_unit1_id => p_rec.budget_unit1_id,
2132 p_budget_unit2_id => p_rec.budget_unit2_id,
2133 p_budget_unit3_id => p_rec.budget_unit3_id,
2134 p_budget_start_date => p_rec.budget_start_date,
2135 p_budget_end_date => p_rec.budget_end_date);
2136 end if;
2137 --
2138 chk_position_control_flag
2139 (p_budget_id => p_rec.budget_id,
2140 p_position_control_flag => p_rec.position_control_flag,
2141 p_budgeted_entity_cd => p_rec.budgeted_entity_cd,
2142 p_effective_date => p_effective_date,
2143 p_object_version_number => p_rec.object_version_number);
2144 --
2145 chk_currency_code
2146 (p_budget_id => p_rec.budget_id,
2147 p_currency_code => p_rec.currency_code,
2148 p_object_version_number => p_rec.object_version_number);
2149 --
2150 chk_psb_budget_flag(p_budget_id => p_rec.budget_id,
2151 p_psb_budget_flag => p_rec.psb_budget_flag,
2152 p_gl_budget_name => p_rec.gl_budget_name,
2153 p_effective_date => p_effective_date,
2154 p_object_version_number => p_rec.object_version_number);
2155
2156 --
2157 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2158 --
2159 hr_utility.set_location(' Leaving:'||l_proc, 10);
2160 End insert_validate;
2161 --
2162 -- ----------------------------------------------------------------------------
2163 -- |---------------------------< update_validate >----------------------------|
2164 -- ----------------------------------------------------------------------------
2165 Procedure update_validate(p_rec in pqh_bgt_shd.g_rec_type
2166 ,p_effective_date in date) is
2167 --
2168 l_proc varchar2(72) := g_package||'update_validate';
2169 --
2170 Begin
2171 hr_utility.set_location('Entering:'||l_proc, 5);
2172 --
2173 -- Call all supporting business operations
2174 --
2175 chk_budget_id
2176 (p_budget_id => p_rec.budget_id,
2177 p_object_version_number => p_rec.object_version_number);
2178 --
2179 chk_budget_unit3_id
2180 (p_budget_id => p_rec.budget_id,
2181 p_status => p_rec.status,
2182 p_budget_unit3_id => p_rec.budget_unit3_id,
2183 p_object_version_number => p_rec.object_version_number);
2184 --
2185 chk_budget_unit2_id
2186 (p_budget_id => p_rec.budget_id,
2187 p_status => p_rec.status,
2188 p_budget_unit2_id => p_rec.budget_unit2_id,
2189 p_object_version_number => p_rec.object_version_number);
2190 --
2191 chk_budget_unit1_id
2192 (p_budget_id => p_rec.budget_id,
2196 --
2193 p_status => p_rec.status,
2194 p_budget_unit1_id => p_rec.budget_unit1_id,
2195 p_object_version_number => p_rec.object_version_number);
2197 chk_period_set_name
2198 (p_budget_id => p_rec.budget_id,
2199 p_period_set_name => p_rec.period_set_name,
2200 p_object_version_number => p_rec.object_version_number);
2201 --
2202 chk_transfer_to_gl
2203 (p_budget_id => p_rec.budget_id,
2204 p_gl_budget_name => p_rec.gl_budget_name,
2205 p_gl_set_of_books_id => p_rec.gl_set_of_books_id,
2206 p_budget_start_date => p_rec.budget_start_date,
2207 p_budget_end_date => p_rec.budget_end_date,
2208 p_position_control_flag => p_rec.position_control_flag,
2209 p_effective_date => p_effective_date,
2210 p_object_version_number => p_rec.object_version_number);
2211 --
2212 chk_transfer_to_grants_flag
2213 (p_budget_id => p_rec.budget_id,
2214 p_transfer_to_grants_flag => p_rec.transfer_to_grants_flag,
2215 p_position_control_flag => p_rec.position_control_flag,
2216 p_effective_date => p_effective_date,
2217 p_object_version_number => p_rec.object_version_number);
2218 --
2219 chk_status
2220 (p_budget_id => p_rec.budget_id,
2221 p_status => p_rec.status,
2222 p_effective_date => p_effective_date,
2223 p_object_version_number => p_rec.object_version_number);
2224 --
2225 chk_budget_style_cd
2226 (p_budget_id => p_rec.budget_id,
2227 p_budget_style_cd => p_rec.budget_style_cd,
2228 p_effective_date => p_effective_date,
2229 p_object_version_number => p_rec.object_version_number);
2230 --
2231 chk_budgeted_entity_cd
2232 (p_budget_id => p_rec.budget_id,
2233 p_budgeted_entity_cd => p_rec.budgeted_entity_cd,
2234 p_effective_date => p_effective_date,
2235 p_object_version_number => p_rec.object_version_number);
2236 --
2237 --
2238 chk_budget_start_date
2239 (p_budget_id => p_rec.budget_id,
2240 p_budget_start_date => p_rec.budget_start_date,
2241 p_budget_end_date => p_rec.budget_end_date,
2242 p_period_set_name => p_rec.period_set_name,
2243 p_object_version_number => p_rec.object_version_number);
2244 --
2245 chk_budget_end_date
2246 (p_budget_id => p_rec.budget_id,
2247 p_budget_start_date => p_rec.budget_start_date,
2248 p_budget_end_date => p_rec.budget_end_date,
2249 p_period_set_name => p_rec.period_set_name,
2250 p_object_version_number => p_rec.object_version_number);
2251 --
2252 chk_budget_unit_id
2253 (p_budget_unit1_id => p_rec.budget_unit1_id,
2254 p_budget_unit2_id => p_rec.budget_unit2_id,
2255 p_budget_unit3_id => p_rec.budget_unit3_id,
2256 p_position_control_flag => p_rec.position_control_flag);
2257 --
2258 chk_budget_name
2259 (p_budget_id => p_rec.budget_id,
2260 p_budget_name => p_rec.budget_name );
2261 --
2262 chk_budget_unit1_aggregate
2263 (p_budget_id => p_rec.budget_id,
2264 p_budget_unit1_id => p_rec.budget_unit1_id,
2265 p_budget_unit1_aggregate => p_rec.budget_unit1_aggregate,
2266 p_effective_date => p_effective_date,
2267 p_object_version_number => p_rec.object_version_number);
2268 --
2269 chk_budget_unit2_aggregate
2270 (p_budget_id => p_rec.budget_id,
2271 p_budget_unit2_id => p_rec.budget_unit2_id,
2272 p_budget_unit2_aggregate => p_rec.budget_unit2_aggregate,
2273 p_effective_date => p_effective_date,
2274 p_object_version_number => p_rec.object_version_number);
2275 --
2276 chk_budget_unit3_aggregate
2277 (p_budget_id => p_rec.budget_id,
2278 p_budget_unit3_id => p_rec.budget_unit3_id,
2279 p_budget_unit3_aggregate => p_rec.budget_unit3_aggregate,
2280 p_effective_date => p_effective_date,
2281 p_object_version_number => p_rec.object_version_number);
2282 --
2283 chk_position_control_flag
2284 (p_budget_id => p_rec.budget_id,
2285 p_position_control_flag => p_rec.position_control_flag,
2286 p_budgeted_entity_cd => p_rec.budgeted_entity_cd,
2287 p_effective_date => p_effective_date,
2288 p_object_version_number => p_rec.object_version_number);
2289 --
2290 if nvl(p_rec.position_control_flag,'X') = 'Y' then
2291 chk_pos_control_budget (p_budget_id => p_rec.budget_id,
2292 p_budgeted_entity_cd => p_rec.budgeted_entity_cd,
2293 p_business_group_id => p_rec.business_group_id,
2294 p_budget_unit1_id => p_rec.budget_unit1_id,
2295 p_budget_unit2_id => p_rec.budget_unit2_id,
2296 p_budget_unit3_id => p_rec.budget_unit3_id,
2297 p_budget_start_date => p_rec.budget_start_date,
2298 p_budget_end_date => p_rec.budget_end_date);
2299 end if;
2300 --
2301 chk_currency_code
2302 (p_budget_id => p_rec.budget_id,
2303 p_currency_code => p_rec.currency_code,
2304 p_object_version_number => p_rec.object_version_number);
2305 --
2306 chk_psb_budget_flag(p_budget_id => p_rec.budget_id,
2307 p_psb_budget_flag => p_rec.psb_budget_flag,
2308 p_gl_budget_name => p_rec.gl_budget_name,
2309 p_effective_date => p_effective_date,
2310 p_object_version_number => p_rec.object_version_number);
2311 --
2312
2313 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2314 --
2315 hr_utility.set_location(' Leaving:'||l_proc, 10);
2316 End update_validate;
2317 --
2318 -- ----------------------------------------------------------------------------
2319 -- |---------------------------< delete_validate >----------------------------|
2320 -- ----------------------------------------------------------------------------
2321 Procedure delete_validate(p_rec in pqh_bgt_shd.g_rec_type
2322 ,p_effective_date in date) is
2323 --
2324 l_proc varchar2(72) := g_package||'delete_validate';
2325 --
2326 Begin
2327 hr_utility.set_location('Entering:'||l_proc, 5);
2328 --
2329 -- Call all supporting business operations
2330 --
2331 hr_utility.set_location(' Leaving:'||l_proc, 10);
2332 End delete_validate;
2333 --
2334 --
2335 -- ---------------------------------------------------------------------------
2336 -- |---------------------< return_legislation_code >-------------------------|
2337 -- ---------------------------------------------------------------------------
2338 --
2339 function return_legislation_code
2340 (p_budget_id in number) return varchar2 is
2341 --
2342 -- Declare cursor
2343 --
2344 cursor csr_leg_code is
2345 select a.legislation_code
2346 from per_business_groups a,
2347 pqh_budgets b
2348 where b.budget_id = p_budget_id
2349 and a.business_group_id = b.business_group_id;
2350 --
2351 -- Declare local variables
2352 --
2353 l_legislation_code varchar2(150);
2354 l_proc varchar2(72) := g_package||'return_legislation_code';
2355 --
2356 begin
2357 --
2358 hr_utility.set_location('Entering:'|| l_proc, 10);
2359 --
2360 -- Ensure that all the mandatory parameter are not null
2361 --
2362 hr_api.mandatory_arg_error(p_api_name => l_proc,
2363 p_argument => 'budget_id',
2364 p_argument_value => p_budget_id);
2365 --
2366 open csr_leg_code;
2367 --
2368 fetch csr_leg_code into l_legislation_code;
2369 --
2370 if csr_leg_code%notfound then
2371 --
2372 close csr_leg_code;
2373 --
2374 -- The primary key is invalid therefore we must error
2375 --
2376 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
2377 hr_utility.raise_error;
2378 --
2379 end if;
2380 --
2381 close csr_leg_code;
2382 --
2383 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2384 --
2385 return l_legislation_code;
2386 --
2387 end return_legislation_code;
2388 --
2389 end pqh_bgt_bus;