[Home] [Help]
PACKAGE BODY: APPS.PER_BUDGET_VERSION_RULES_PKG
Source
1 PACKAGE BODY PER_BUDGET_VERSION_RULES_PKG as
2 /* $Header: pebgr01t.pkb 115.3 2002/12/09 14:37:09 raranjan ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 g_package varchar2(33) := ' per_budget_version_rules_pkg.'; -- Global package name
8
9
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< Delete_Row >---------------------------------|
12 -- ----------------------------------------------------------------------------
13 PROCEDURE Delete_Row(X_Rowid VARCHAR2) is
14 CURSOR C_Elements is
15 SELECT pbe.Rowid
16 FROM per_budget_elements pbe
17 WHERE pbe.budget_version_id = (SELECT pbv.budget_version_id
18 FROM per_budget_versions pbv
19 WHERE pbv.Rowid = X_Rowid);
20 --
21 l_ele_rowid VARCHAR2(30);
22 l_budget_id NUMBER(15);
23 l_proc VARCHAR2(72) := g_package||'Delete_Row';
24 --
25 BEGIN
26 --
27 hr_utility.set_location('Entering:'||l_proc, 5);
28 --
29 SELECT pbv.budget_id
30 INTO l_budget_id
31 FROM per_budget_versions pbv
32 WHERE pbv.Rowid = X_Rowid;
33 --
34 OPEN C_Elements;
35 -- Cascade delete the appropriate child budget_elements recs if
36 -- parent per_budgets budget_type_code is 'HR_BUDGET';
37 -- (use budget_id in chk_ota_budget_type)
38 IF per_budgets_pkg.chk_OTA_Budget_Type(l_budget_id, NULL, NULL) = FALSE THEN
39 LOOP
40 FETCH C_Elements into l_ele_rowid;
41 EXIT when (C_Elements%NOTFOUND);
42 PER_BUDGET_ELEMENTS_PKG.Delete_Row(X_Rowid => l_ele_rowid);
43 END LOOP;
44 ELSE
45 FETCH C_Elements into l_ele_rowid;
46 IF C_Elements%FOUND THEN
47 CLOSE C_Elements;
48 --raise error as child record has been found
49 hr_utility.set_message(800,'PER_52876_BUD_VER_DELETE_FAIL');
50 hr_utility.raise_error;
51 END IF;
52 END IF;
53 CLOSE C_Elements;
54
55 --now delete the version
56 DELETE FROM per_budget_versions
57 WHERE Rowid = X_Rowid;
58 --
59 hr_utility.set_location(' Leaving:'||l_proc, 10);
60 --
61 END Delete_Row;
62 --
63 -- ----------------------------------------------------------------------------
64 -- |---------------------------< Vers_Exists >---------------------------------|
65 -- ----------------------------------------------------------------------------
66 FUNCTION Vers_Exists(X_Budget_Id NUMBER,
67 X_Rowid VARCHAR2) return BOOLEAN is
68 -- PRIVATE FUNCTION used by Chk_Prev_Rec.
69 --
70 l_result VARCHAR2(255);
71 l_proc VARCHAR2(72) := g_package||'Vers_Exists';
72 --
73 Begin
74 --
75 hr_utility.set_location('Entering:'||l_proc, 5);
76 --
77 SELECT null
78 INTO l_result
79 FROM per_budget_versions pbv
80 WHERE pbv.budget_id = X_Budget_Id
81 AND (pbv.rowid <> X_Rowid
82 OR X_Rowid is Null);
83 If (SQL%FOUND) then
84 Return TRUE;
85 End if;
86 --
87 hr_utility.set_location(' Leaving:'||l_proc, 10);
88 --
89 Exception
90 when no_data_found then
91 Return FALSE;
92 when too_many_rows then
93 Return TRUE;
94 End Vers_Exists;
95 --
96 --
97 -- ----------------------------------------------------------------------------
98 -- |---------------------------< Gap_Exists >---------------------------------|
99 -- ----------------------------------------------------------------------------
100 FUNCTION Gap_Exists(X_Budget_Id NUMBER,
101 X_Rowid VARCHAR2,
102 X_Date_From DATE,
103 X_Date_To DATE) return BOOLEAN is
104 -- PRIVATE FUNCTION used by Chk_Prev_Rec.
105 --
106 CURSOR Before is
107 select pbv.date_to,pbv.date_from
108 from per_budget_versions pbv
109 where pbv.budget_id = X_Budget_Id
110 and (pbv.rowid <> X_Rowid
111 OR X_Rowid is null)
112 and pbv.date_to = (select max(pbv2.date_to)
113 from per_budget_versions pbv2
114 where pbv2.budget_id = X_Budget_Id
115 and (pbv2.rowid <> X_Rowid
116 OR X_Rowid is null)
117 and pbv2.date_to < X_Date_From);
118 --
119 CURSOR After is
120 select pbv.date_to,pbv.date_from
121 from per_budget_versions pbv
122 where pbv.budget_id = X_Budget_Id
123 and (pbv.rowid <> X_Rowid
124 OR X_Rowid is null)
125 and pbv.date_from = (select min(pbv2.date_from)
126 from per_budget_versions pbv2
127 where pbv2.budget_id = X_Budget_Id
128 and (pbv2.rowid <> X_Rowid
129 OR X_Rowid is null)
130 and pbv2.date_from > X_Date_To);
131 --
132 l_date_to DATE;
133 l_date_from DATE;
134 l_proc VARCHAR2(72) := g_package||'Gap_Exists';
135 --
136
137 Begin
138 --
139 hr_utility.set_location('Entering:'||l_proc, 5);
140 --
141 Begin
142 OPEN Before;
143 FETCH Before into l_date_to,l_date_from;
144 If (Before%FOUND) then
145 -- There is a preceding version, so is there a gap between it
146 -- and the present version.
147 if (X_Date_From - 1 > l_date_to) then
148 CLOSE Before;
149 RETURN TRUE;
150 end if;
151 CLOSE Before;
152 end if;
153 --
154 exception
155 when no_data_found then
156 CLOSE Before;
157 End;
158 --
159 Begin
160 OPEN After;
161 FETCH After into l_date_to,l_date_from;
162 If (After%FOUND) then
163 If (l_date_from - 1 > X_Date_To) then
164 -- There is a succeeding version with a gap between it
165 -- and the present version
166 CLOSE After;
167 RETURN TRUE;
168 End if;
169 CLOSE After;
170 End if;
171 --
172 --
173 hr_utility.set_location(' Leaving:'||l_proc, 10);
174 --
175 exception
176 when no_data_found then
177 CLOSE After;
178 End;
179 --
180 RETURN FALSE;
181 End Gap_Exists;
182 --
183 -- ----------------------------------------------------------------------------
184 -- |---------------------------< Overlap_Exists >-----------------------------|
185 -- ----------------------------------------------------------------------------
186 FUNCTION Overlap_Exists(X_Budget_Id NUMBER,
187 X_Rowid VARCHAR2,
188 X_Date_From DATE,
189 X_Date_To DATE) return BOOLEAN is
190 --
191 CURSOR Before is
192 select pbv.date_to,pbv.date_from
193 from per_budget_versions pbv
194 where pbv.budget_id = X_Budget_Id
195 and (pbv.rowid <> X_Rowid
196 OR X_Rowid is null)
197 and pbv.date_to = (select max(pbv2.date_to)
198 from per_budget_versions pbv2
199 where pbv2.budget_id = X_Budget_Id
200 and (pbv2.rowid <> X_Rowid
201 OR X_Rowid is null)
202 and pbv2.date_from < X_Date_From);
203 --
204 --
205 CURSOR After is
206 select pbv.date_to,pbv.date_from
207 from per_budget_versions pbv
208 where pbv.budget_id = X_Budget_Id
209 and (pbv.rowid <> X_Rowid
210 OR X_Rowid is null)
211 and pbv.date_from = (select min(pbv2.date_from)
212 from per_budget_versions pbv2
213 where pbv2.budget_id = X_Budget_Id
214 and (pbv2.rowid <> X_Rowid
215 OR X_Rowid is null)
216 and pbv2.date_from > X_Date_From);
217 --
218 l_date_to DATE;
219 l_date_from DATE;
220 l_proc VARCHAR2(72) := g_package||'Overlap_Exists';
221 --
222 Begin
223 --
224 hr_utility.set_location('Entering:'||l_proc, 5);
225 --
226 --
227 Begin
228 OPEN Before;
229 FETCH Before into l_date_to,l_date_from;
230 If (Before%FOUND) then
231 If (X_Date_From < l_date_to) then
232 CLOSE Before;
233 RETURN TRUE;
234 End If;
235 CLOSE Before;
236 End if;
237 Exception
238 when no_data_found then
239 CLOSE Before;
240 End;
241 --
242 Begin
243 OPEN After;
244 FETCH After into l_date_to,l_date_from;
245 If (After%FOUND) then
246 If (X_Date_To > l_date_from) then
247 CLOSE After;
248 RETURN TRUE;
249 End If;
250 CLOSE After;
251 End if;
252
253 --
254 hr_utility.set_location(' Leaving:'||l_proc, 10);
255 --
256 Exception
257 when no_data_found then
258 CLOSE After;
259 End;
260 --
261 RETURN FALSE;
262 --
263 End Overlap_Exists;
264 --
265 -- ----------------------------------------------------------------------------
266 -- |---------------------------< Update_Versions >----------------------------|
267 -- ----------------------------------------------------------------------------
268 PROCEDURE Update_Versions(X_Budget_Id NUMBER
269 ,X_Rowid VARCHAR2
270 ,X_Date_From DATE
271 ,X_Date_To IN OUT NOCOPY DATE) is
272 --
273 CURSOR C is select pbv1.date_from, pbv1.date_to
274 from per_budget_versions pbv1
275 where pbv1.budget_id = X_Budget_Id
276 and (pbv1.rowid <> X_Rowid
277 OR X_Rowid is NULL)
278 and pbv1.date_from = (select max(pbv2.date_from)
279 from per_budget_versions pbv2
280 where pbv2.date_from < X_Date_From
281 and pbv2.budget_id = X_Budget_Id
282 and (pbv2.rowid <> X_Rowid
283 OR X_Rowid is NULL))
284 FOR UPDATE;
285 --
286 CURSOR C2 is select pbv1.date_from, pbv1.date_to
287 from per_budget_versions pbv1
288 where pbv1.budget_id = X_Budget_Id
289 and (pbv1.rowid <> X_Rowid
290 OR X_Rowid is NULL)
291 and pbv1.date_from = (select min(pbv2.date_from)
292 from per_budget_versions pbv2
293 where pbv2.date_from > X_Date_From
294 and pbv2.budget_id = X_Budget_Id
295 and (pbv2.rowid <> X_Rowid
296 OR X_Rowid is NULL))
297 FOR UPDATE;
298 --
299 l_date_from DATE;
300 l_date_to DATE;
301 l_proc VARCHAR2(72) := g_package||'Update_Versions';
302 --
303 Begin
304 --
305 hr_utility.set_location('Entering:'||l_proc, 5);
306 --
307 --
308 OPEN C;
309 FETCH C into l_date_from,l_date_to;
310 IF (C%FOUND) then
311 --A preceding version exists
312 IF (l_date_to is null) then
313 --which runs to the end of time and commences before the new version
314 --so close the old version down on the day preceding the new version
315 --
316 UPDATE per_budget_versions
317 SET date_to = X_Date_From - 1
318 WHERE current of C;
319 --
320 End if;
321 CLOSE C;
322 Elsif (C%NOTFOUND) then
323 CLOSE C;
324 OPEN C2;
325 FETCH C2 into l_date_from,l_date_to;
326 IF (C2%FOUND) then
327 -- A succeeding version exists
328 if (X_Date_To is null) and (l_date_from > X_Date_From) then
329 -- set the date_to of the present record to the day
330 -- before the succeeding version
331 X_Date_To := l_date_from - 1;
332 end if;
333 CLOSE C2;
334 ELSE
335 CLOSE C2;
336 End if;
337 end if;
338
339 --
340 hr_utility.set_location(' Leaving:'||l_proc, 10);
341 --
342 Exception
343 when no_data_found then
344 CLOSE C;
345 null;
346 End Update_Versions;
347
348 -- ----------------------------------------------------------------------------
349 -- |---------------------------< Chk_Prev_Rec >-------------------------------|
350 -- ----------------------------------------------------------------------------
351 PROCEDURE Chk_Prev_Rec(X_Budget_Id NUMBER,
352 X_Date_From DATE,
353 X_Date_To IN OUT NOCOPY DATE,
354 X_Rowid VARCHAR2,
355 X_Result IN OUT NOCOPY VARCHAR2) is
356 --
357 l_proc VARCHAR2(72) := g_package||'Chk_Prev_Rec';
358 Begin
359 --
360 hr_utility.set_location('Entering:'||l_proc, 5);
361 --
362 --
363 -- First check if there are any other versions apart from the present one.
364 --
365 IF vers_exists(X_Budget_Id,X_Rowid) then
366 --
367 -- now check for any overlap.
368 --
369 If Overlap_Exists(X_Budget_Id,X_Rowid,X_Date_From,X_Date_To) then
370 --
371 -- prevent the operation
372 --
373 hr_utility.set_message('801','HR_6105_BUD_OVERLAP');
374 hr_utility.raise_error;
375 End if;
376 --
377 -- now update the other version if neccessary.
378 --
379 Update_Versions(X_Budget_Id,X_Rowid,X_Date_From,X_Date_To);
380 --
381 -- check if there is a gap between them
382 --
383 --
384 If Gap_Exists(X_Budget_Id,X_Rowid,X_Date_From,X_Date_To) then
385 --
386 -- then warn the user.
387 X_Result := 'Warn';
388 Else
389 X_Result := 'Success';
390 End If;
391 --
392 End if;
393 --
394 hr_utility.set_location(' Leaving:'||l_proc, 10);
395 --
396 --
397 END Chk_Prev_Rec;
398 --
399 -- ----------------------------------------------------------------------------
400 -- |-------------------------------< Get_Id >---------------------------------|
401 -- ----------------------------------------------------------------------------
402 PROCEDURE Get_Id(X_Budget_Version_Id IN OUT NOCOPY NUMBER) IS
403 l_proc VARCHAR2(72) := g_package||'Get_Id';
404 BEGIN
405 --
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 SELECT per_budget_versions_s.nextval
409 INTO X_Budget_Version_Id
410 FROM dual;
411
412 --
413 hr_utility.set_location(' Leaving:'||l_proc, 10);
414 --
415 End Get_Id;
416
417 -- ----------------------------------------------------------------------------
418 -- |-------------------------< Chk_Version_Number >---------------------------|
419 -- ----------------------------------------------------------------------------
420 PROCEDURE Chk_Version_Number(X_Version_Number IN VARCHAR2) IS
421 l_proc VARCHAR2(72) := g_package||'Chk_Version_Number';
422 BEGIN
423 --
424 hr_utility.set_location('Entering:'||l_proc, 5);
425 --
426 IF X_Version_Number <> '1' THEN
427 hr_utility.set_message(800,'PER_52875_INV_VERSION_NUM');
428 hr_utility.raise_error;
429 END IF;
430 --
431 hr_utility.set_location(' Leaving:'||l_proc, 10);
432 --
433 End Chk_Version_Number;
434
435 -- ----------------------------------------------------------------------------
436 -- |------------------------< Chk_Budget_Version_Id >-------------------------|
437 -- ----------------------------------------------------------------------------
438 PROCEDURE Chk_Budget_Version_Id (X_Budget_Version_Id IN NUMBER,
439 X_Rowid IN VARCHAR2) IS
440
441 CURSOR c_bdv IS
442 SELECT null
443 FROM per_budget_versions pbv
444 WHERE pbv.budget_version_id = x_budget_version_id
445 AND (pbv.rowid <> X_ROWID or X_Rowid IS NULL);
446
447 l_result VARCHAR2(255);
448 l_proc VARCHAR2(72) := g_package||'Chk_Budget_Version_Id';
449
450 BEGIN
451 --
452 hr_utility.set_location('Entering:'||l_proc, 5);
453 --
454 OPEN c_bdv;
455 FETCH c_bdv INTO l_result;
456 IF c_bdv%FOUND THEN
457 CLOSE c_bdv;
458 hr_utility.set_message(801,'HR_6107_BUD_VER_EXISTS');
459 hr_utility.raise_error;
460 END IF;
461 CLOSE c_bdv;
462 --
463 hr_utility.set_location(' Leaving:'||l_proc, 10);
464 --
465 End Chk_Budget_Version_Id;
466
467 -- ----------------------------------------------------------------------------
468 -- |----------------------------< Chk_df >------------------------------------|
469 -- ----------------------------------------------------------------------------
470 PROCEDURE Chk_df(x_attribute_category varchar2
471 ,x_attribute1 varchar2
472 ,x_attribute2 varchar2
473 ,x_attribute3 varchar2
474 ,x_attribute4 varchar2
475 ,x_attribute5 varchar2
476 ,x_attribute6 varchar2
477 ,x_attribute7 varchar2
478 ,x_attribute8 varchar2
479 ,x_attribute9 varchar2
480 ,x_attribute10 varchar2
481 ,x_attribute11 varchar2
482 ,x_attribute12 varchar2
483 ,x_attribute13 varchar2
484 ,x_attribute14 varchar2
485 ,x_attribute15 varchar2
486 ,x_attribute16 varchar2
487 ,x_attribute17 varchar2
488 ,x_attribute18 varchar2
489 ,x_attribute19 varchar2
490 ,x_attribute20 varchar2) IS
491
492 l_proc VARCHAR2(72) := g_package||'Chk_df';
493
494 BEGIN
495 --
496 hr_utility.set_location('Entering:'||l_proc, 5);
497 --
498 hr_dflex_utility.ins_or_upd_descflex_attribs
499 (p_appl_short_name => 'PER'
500 ,p_descflex_name => 'PER_BUDGET_VERSIONS'
501 ,p_attribute_category => x_attribute_category
502 ,p_attribute1_name => 'ATTRIBUTE1'
503 ,p_attribute1_value => x_attribute1
504 ,p_attribute2_name => 'ATTRIBUTE2'
505 ,p_attribute2_value => x_attribute2
506 ,p_attribute3_name => 'ATTRIBUTE3'
507 ,p_attribute3_value => x_attribute3
508 ,p_attribute4_name => 'ATTRIBUTE4'
509 ,p_attribute4_value => x_attribute4
510 ,p_attribute5_name => 'ATTRIBUTE5'
511 ,p_attribute5_value => x_attribute5
512 ,p_attribute6_name => 'ATTRIBUTE6'
513 ,p_attribute6_value => x_attribute6
514 ,p_attribute7_name => 'ATTRIBUTE7'
515 ,p_attribute7_value => x_attribute7
516 ,p_attribute8_name => 'ATTRIBUTE8'
517 ,p_attribute8_value => x_attribute8
518 ,p_attribute9_name => 'ATTRIBUTE9'
519 ,p_attribute9_value => x_attribute9
520 ,p_attribute10_name => 'ATTRIBUTE10'
521 ,p_attribute10_value => x_attribute10
522 ,p_attribute11_name => 'ATTRIBUTE11'
523 ,p_attribute11_value => x_attribute11
524 ,p_attribute12_name => 'ATTRIBUTE12'
525 ,p_attribute12_value => x_attribute12
526 ,p_attribute13_name => 'ATTRIBUTE13'
527 ,p_attribute13_value => x_attribute13
528 ,p_attribute14_name => 'ATTRIBUTE14'
529 ,p_attribute14_value => x_attribute14
530 ,p_attribute15_name => 'ATTRIBUTE15'
531 ,p_attribute15_value => x_attribute15
532 ,p_attribute16_name => 'ATTRIBUTE16'
533 ,p_attribute16_value => x_attribute16
534 ,p_attribute17_name => 'ATTRIBUTE17'
535 ,p_attribute17_value => x_attribute17
536 ,p_attribute18_name => 'ATTRIBUTE18'
537 ,p_attribute18_value => x_attribute18
538 ,p_attribute19_name => 'ATTRIBUTE19'
539 ,p_attribute19_value => x_attribute19
540 ,p_attribute20_name => 'ATTRIBUTE20'
541 ,p_attribute20_value => x_attribute20);
542 --
543 hr_utility.set_location(' Leaving:'||l_proc, 10);
544 --
545 END Chk_df;
546
547 -- ----------------------------------------------------------------------------
548 -- |---------------------------< Chk_Unique >---------------------------------|
549 -- ----------------------------------------------------------------------------
550 PROCEDURE Chk_Unique(X_Rowid VARCHAR2,
551 X_Business_Group_Id NUMBER,
552 X_Version_Number VARCHAR2,
553 X_Budget_Id NUMBER) is
554 l_result VARCHAR2(255);
555 l_proc VARCHAR2(72) := g_package||'Chk_Unique';
556 Begin
557 --
558 hr_utility.set_location('Entering:'||l_proc, 5);
559 --
560 SELECT NULL
561 INTO l_result
562 FROM per_budget_versions bver
563 WHERE UPPER(X_Version_Number) = UPPER(bver.Version_number)
564 AND X_Business_group_Id = bver.Business_Group_Id
565 AND X_Budget_Id = bver.Budget_Id
566 AND (bver.Rowid <> X_Rowid or X_Rowid is null);
567
568 IF (SQL%FOUND) then
569 hr_utility.set_message(801,'HR_6107_BUD_VER_EXISTS');
570 hr_utility.raise_error;
571 end if;
572
573 --
574 hr_utility.set_location(' Leaving:'||l_proc, 10);
575 --
576 EXCEPTION
577 when NO_DATA_FOUND then
578 null;
579 end Chk_Unique;
580
581
582 -- ----------------------------------------------------------------------------
583 -- |---------------------------< Default_Date_From >--------------------------|
584 -- ----------------------------------------------------------------------------
585 PROCEDURE Default_Date_From(X_Date_From IN OUT NOCOPY DATE,
586 X_Session_Date DATE,
587 X_Budget_Id NUMBER) IS
588
589 /*CURSOR C IS SELECT MAX(DATE_FROM + 1)
590 FROM PER_BUDGET_VERSIONS
591 WHERE BUDGET_ID = X_Budget_Id
592 AND DATE_TO IS NULL;*/
593 CURSOR C is select pbv1.date_from,pbv1.date_to
594 from per_budget_versions pbv1
595 where pbv1.budget_id = X_Budget_Id
596 and pbv1.date_from = (select max(pbv2.date_from)
597 from per_budget_versions pbv2
598 where pbv2.budget_id = X_Budget_Id);
599 --
600 l_date_from DATE;
601 l_date_to DATE;
602 l_proc VARCHAR2(72) := g_package||'Default_Date_From';
603 --
604 Begin
605 --
606 hr_utility.set_location('Entering:'||l_proc, 5);
607 --
608 OPEN C;
609 FETCH C INTO l_date_from,l_date_to;
610 IF (C%NOTFOUND) then
611 X_Date_From := X_Session_Date;
612 ELSIF (C%FOUND) then
613 if l_date_to is null then
614 X_Date_From := l_date_from + 1;
615 else
616 X_Date_From := l_date_to + 1;
617 end if;
618 --
619 END IF;
620 CLOSE C;
621 --
622 --
623 hr_utility.set_location(' Leaving:'||l_proc, 10);
624 --
625 End Default_Date_From;
626 --
627 -- ----------------------------------------------------------------------------
628 -- |---------------------------< Insert_Row >---------------------------------|
629 -- ----------------------------------------------------------------------------
630 --
631 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2
632 ,X_Budget_version_id IN OUT NOCOPY NUMBER
633 ,X_Business_group_id NUMBER
634 ,X_Budget_id NUMBER
635 ,X_Date_from DATE
636 ,X_Version_number VARCHAR2
637 ,X_Comments VARCHAR2
638 ,X_Date_to DATE
639 ,X_Request_id NUMBER
640 ,X_Program_application_id NUMBER
641 ,X_Program_id NUMBER
642 ,X_Program_update_date DATE
643 ,X_Attribute_category VARCHAR2
644 ,X_Attribute1 VARCHAR2
645 ,X_Attribute2 VARCHAR2
646 ,X_Attribute3 VARCHAR2
647 ,X_Attribute4 VARCHAR2
648 ,X_Attribute5 VARCHAR2
649 ,X_Attribute6 VARCHAR2
650 ,X_Attribute7 VARCHAR2
651 ,X_Attribute8 VARCHAR2
652 ,X_Attribute9 VARCHAR2
653 ,X_Attribute10 VARCHAR2
654 ,X_Attribute11 VARCHAR2
655 ,X_Attribute12 VARCHAR2
656 ,X_Attribute13 VARCHAR2
657 ,X_Attribute14 VARCHAR2
658 ,X_Attribute15 VARCHAR2
659 ,X_Attribute16 VARCHAR2
660 ,X_Attribute17 VARCHAR2
661 ,X_Attribute18 VARCHAR2
662 ,X_Attribute19 VARCHAR2
663 ,X_Attribute20 VARCHAR2
664 ) IS
665
666 CURSOR C1 IS SELECT rowid FROM PER_BUDGET_VERSIONS
667 WHERE budget_version_id = X_budget_version_id;
668
669 l_proc VARCHAR2(72) := g_package||'Insert_Row';
670
671 BEGIN
672 --
673 hr_utility.set_location('Entering:'||l_proc, 5);
674
675 -- validate mandatory business_group
676 hr_api.validate_bus_grp_id(X_Business_Group_Id);
677
678 -- validate mandatory version number
679 hr_api.mandatory_arg_error
680 (p_api_name => l_proc,
681 p_argument => 'version_number',
682 p_argument_value => X_Version_Number);
683
684 -- validate mandatory budget_id
685 hr_api.mandatory_arg_error
686 (p_api_name => l_proc,
687 p_argument => 'budget_id',
688 p_argument_value => X_Budget_Id);
689
690 -- If the parent record is OTA_BUDGET type, and no budget version exists
691 -- for the budget_id then continue, else raise error.
692 IF per_budgets_pkg.Chk_OTA_Budget_Type(X_Budget_id,NULL,NULL) THEN
693 IF vers_exists(X_Budget_id,NULL) THEN
694 hr_utility.set_message(800,'HR_52873_OTA_BUD_EXISTS');
695 hr_utility.raise_error;
696 ELSE
697 -- call chk routine to validate value TP
698 chk_version_number(x_version_number);
699 END IF;
700 ELSE
701 hr_utility.set_message(800,'PER_52874_NOT_OTA_VERSION');
702 hr_utility.raise_error;
703 END IF;
704
705 -- validate mandatory date_from is not null
706 hr_api.mandatory_arg_error
707 (p_api_name => l_proc,
708 p_argument => 'date_from',
709 p_argument_value => X_Date_From);
710
711
712 -- validate per_budget_versions df
713 Chk_df(X_Attribute_Category,
714 X_Attribute1,
715 X_Attribute2,
716 X_Attribute3,
717 X_Attribute4,
718 X_Attribute5,
719 X_Attribute6,
720 X_Attribute7,
721 X_Attribute8,
722 X_Attribute9,
723 X_Attribute10,
724 X_Attribute11,
725 X_Attribute12,
726 X_Attribute13,
727 X_Attribute14,
728 X_Attribute15,
729 X_Attribute16,
730 X_Attribute17,
731 X_Attribute18,
732 X_Attribute19,
733 X_Attribute20);
734
735 -- Get new budget_version_id
736 per_budget_version_rules_pkg.get_id(X_budget_version_id);
737
738 INSERT INTO PER_BUDGET_VERSIONS(budget_version_id
739 ,business_group_id
740 ,budget_id
741 ,date_from
742 ,version_number
743 ,comments
744 ,date_to
745 ,request_id
746 ,program_application_id
747 ,program_id
748 ,program_update_date
749 ,attribute_category
750 ,attribute1
751 ,attribute2
752 ,attribute3
753 ,attribute4
754 ,attribute5
755 ,attribute6
756 ,attribute7
757 ,attribute8
758 ,attribute9
759 ,attribute10
760 ,attribute11
761 ,attribute12
762 ,attribute13
763 ,attribute14
764 ,attribute15
765 ,attribute16
766 ,attribute17
767 ,attribute18
768 ,attribute19
769 ,attribute20
770 ) VALUES (
771 X_Budget_Version_Id
772 ,X_Business_Group_Id
773 ,X_Budget_Id
774 ,X_Date_from
775 ,X_Version_number
776 ,X_Comments
777 ,X_Date_to
778 ,X_Request_id
779 ,X_Program_application_id
780 ,X_Program_id
781 ,X_Program_update_date
782 ,X_Attribute_Category
783 ,X_Attribute1
784 ,X_Attribute2
785 ,X_Attribute3
786 ,X_Attribute4
787 ,X_Attribute5
788 ,X_Attribute6
789 ,X_Attribute7
790 ,X_Attribute8
791 ,X_Attribute9
792 ,X_Attribute10
793 ,X_Attribute11
794 ,X_Attribute12
795 ,X_Attribute13
796 ,X_Attribute14
797 ,X_Attribute15
798 ,X_Attribute16
799 ,X_Attribute17
800 ,X_Attribute18
801 ,X_Attribute19
802 ,X_Attribute20);
803
804 OPEN C1;
805 FETCH C1 INTO X_Rowid;
806 IF (C1%NOTFOUND) THEN
807 CLOSE C1;
808 RAISE NO_DATA_FOUND;
809 END IF;
810 CLOSE C1;
811 --
812 --
813 hr_utility.set_location(' Leaving:'||l_proc, 10);
814 --
815 END Insert_Row;
816 --
817 --
818 -- ----------------------------------------------------------------------------
819 -- |-----------------------------< Lock_Row >---------------------------------|
820 -- ----------------------------------------------------------------------------
821 PROCEDURE Lock_Row(X_Rowid VARCHAR2
822 ,X_Budget_Version_id NUMBER
823 ,X_Business_Group_Id NUMBER
824 ,X_Budget_Id NUMBER
825 ,X_Date_from DATE
826 ,X_Version_number VARCHAR2
827 ,X_Comments VARCHAR2
828 ,X_Date_to DATE
829 ,X_Request_id NUMBER
830 ,X_Program_application_id NUMBER
831 ,X_Program_id NUMBER
832 ,X_Program_update_date DATE
833 ,X_Attribute_Category VARCHAR2
834 ,X_Attribute1 VARCHAR2
835 ,X_Attribute2 VARCHAR2
836 ,X_Attribute3 VARCHAR2
837 ,X_Attribute4 VARCHAR2
838 ,X_Attribute5 VARCHAR2
839 ,X_Attribute6 VARCHAR2
840 ,X_Attribute7 VARCHAR2
841 ,X_Attribute8 VARCHAR2
842 ,X_Attribute9 VARCHAR2
843 ,X_Attribute10 VARCHAR2
844 ,X_Attribute11 VARCHAR2
845 ,X_Attribute12 VARCHAR2
846 ,X_Attribute13 VARCHAR2
847 ,X_Attribute14 VARCHAR2
848 ,X_Attribute15 VARCHAR2
849 ,X_Attribute16 VARCHAR2
850 ,X_Attribute17 VARCHAR2
851 ,X_Attribute18 VARCHAR2
852 ,X_Attribute19 VARCHAR2
853 ,X_Attribute20 VARCHAR2) IS
854 CURSOR C IS
855 SELECT *
856 FROM PER_BUDGET_VERSIONS
857 WHERE rowid = X_Rowid
858 FOR UPDATE of budget_version_id NOWAIT;
859 Recinfo C%ROWTYPE;
860 l_proc VARCHAR2(72) := g_package||'Lock_Row';
861 BEGIN
862 --
863 hr_utility.set_location('Entering:'||l_proc, 5);
864 --
865
866 OPEN C;
867 FETCH C INTO Recinfo;
868 if (C%NOTFOUND) then
869 CLOSE C;
870 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
871 hr_utility.set_message_token('PROCEDURE','Lock_Row');
872 hr_utility.set_message_token('STEP','1');
873 hr_utility.raise_error;
874 end if;
875 CLOSE C;
876 --
877 Recinfo.Budget_version_id := rtrim(Recinfo.Budget_version_id);
878 Recinfo.Business_group_id := rtrim(Recinfo.Business_group_id);
879 Recinfo.Budget_id := rtrim(Recinfo.Budget_id);
880 Recinfo.Date_from := rtrim(Recinfo.Date_from);
881 Recinfo.Version_number := rtrim(Recinfo.Version_number);
882 Recinfo.Comments := rtrim(Recinfo.Comments);
883 Recinfo.Date_to := rtrim(Recinfo.Date_to);
884 Recinfo.Request_id := rtrim(Recinfo.Request_id);
885 Recinfo.Program_application_id := rtrim(Recinfo.Program_application_id);
886 Recinfo.Program_id := rtrim(Recinfo.Program_id);
887 Recinfo.Program_update_date := rtrim(Recinfo.Program_update_date);
888 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
889 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
890 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
891 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
892 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
893 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
894 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
895 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
896 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
897 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
898 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
899 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
900 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
901 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
902 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
903 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
904 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
905 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
906 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
907 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
908 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
909 if (
910 ( (Recinfo.Budget_version_id = X_Budget_version_id)
911 OR ( (Recinfo.Budget_version_id IS NULL)
912 AND (X_Budget_version_id IS NULL)))
913 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
914 OR ( (Recinfo.business_group_id IS NULL)
915 AND (X_Business_Group_Id IS NULL)))
916 AND ( (Recinfo.Budget_id = X_Budget_id)
917 OR ( (Recinfo.Budget_id IS NULL)
918 AND (X_Budget_id IS NULL)))
919 AND ( (Recinfo.Date_from = X_Date_from)
920 OR ( (Recinfo.Date_from IS NULL)
921 AND (X_Date_from IS NULL)))
922 AND ( (Recinfo.Version_number = X_Version_number)
923 OR ( (Recinfo.Version_number IS NULL)
924 AND (X_Version_number IS NULL)))
925 AND ( (Recinfo.Comments = X_Comments)
926 OR ( (Recinfo.Comments IS NULL)
927 AND (X_Comments IS NULL)))
928 AND ( (Recinfo.Date_to = X_Date_to)
929 OR ( (Recinfo.Date_to IS NULL)
930 AND (X_Date_to IS NULL)))
931 AND ( (Recinfo.Request_id = X_Request_id)
932 OR ( (Recinfo.Request_id IS NULL)
933 AND (X_Request_id IS NULL)))
934 AND ( (Recinfo.Program_application_id = X_Program_application_id)
935 OR ( (Recinfo.Program_application_id IS NULL)
936 AND (X_Program_application_id IS NULL)))
937 AND ( (Recinfo.Program_id = X_Program_id)
938 OR ( (Recinfo.Program_id IS NULL)
939 AND (X_Program_id IS NULL)))
940 AND ( (Recinfo.Program_update_date = X_Program_update_date)
941 OR ( (Recinfo.Program_update_date IS NULL)
942 AND (X_Program_update_date IS NULL)))
943 AND ( (Recinfo.attribute_category = X_Attribute_Category)
944 OR ( (Recinfo.attribute_category IS NULL)
945 AND (X_Attribute_Category IS NULL)))
946 AND ( (Recinfo.attribute1 = X_Attribute1)
947 OR ( (Recinfo.attribute1 IS NULL)
948 AND (X_Attribute1 IS NULL)))
949 AND ( (Recinfo.attribute2 = X_Attribute2)
950 OR ( (Recinfo.attribute2 IS NULL)
951 AND (X_Attribute2 IS NULL)))
952 AND ( (Recinfo.attribute3 = X_Attribute3)
953 OR ( (Recinfo.attribute3 IS NULL)
954 AND (X_Attribute3 IS NULL)))
955 AND ( (Recinfo.attribute4 = X_Attribute4)
956 OR ( (Recinfo.attribute4 IS NULL)
957 AND (X_Attribute4 IS NULL)))
958 AND ( (Recinfo.attribute5 = X_Attribute5)
959 OR ( (Recinfo.attribute5 IS NULL)
960 AND (X_Attribute5 IS NULL)))
961 AND ( (Recinfo.attribute6 = X_Attribute6)
962 OR ( (Recinfo.attribute6 IS NULL)
963 AND (X_Attribute6 IS NULL)))
964 AND ( (Recinfo.attribute7 = X_Attribute7)
965 OR ( (Recinfo.attribute7 IS NULL)
966 AND (X_Attribute7 IS NULL)))
967 AND ( (Recinfo.attribute8 = X_Attribute8)
968 OR ( (Recinfo.attribute8 IS NULL)
969 AND (X_Attribute8 IS NULL)))
970 AND ( (Recinfo.attribute9 = X_Attribute9)
971 OR ( (Recinfo.attribute9 IS NULL)
972 AND (X_Attribute9 IS NULL)))
973 AND ( (Recinfo.attribute10 = X_Attribute10)
974 OR ( (Recinfo.attribute10 IS NULL)
975 AND (X_Attribute10 IS NULL)))
976 AND ( (Recinfo.attribute11 = X_Attribute11)
977 OR ( (Recinfo.attribute11 IS NULL)
978 AND (X_Attribute11 IS NULL)))
979 AND ( (Recinfo.attribute12 = X_Attribute12)
980 OR ( (Recinfo.attribute12 IS NULL)
981 AND (X_Attribute12 IS NULL)))
982 AND ( (Recinfo.attribute13 = X_Attribute13)
983 OR ( (Recinfo.attribute13 IS NULL)
984 AND (X_Attribute13 IS NULL)))
985 AND ( (Recinfo.attribute14 = X_Attribute14)
986 OR ( (Recinfo.attribute14 IS NULL)
987 AND (X_Attribute14 IS NULL)))
988 AND ( (Recinfo.attribute15 = X_Attribute15)
989 OR ( (Recinfo.attribute15 IS NULL)
990 AND (X_Attribute15 IS NULL)))
991 AND ( (Recinfo.attribute16 = X_Attribute16)
992 OR ( (Recinfo.attribute16 IS NULL)
993 AND (X_Attribute16 IS NULL)))
994 AND ( (Recinfo.attribute17 = X_Attribute17)
995 OR ( (Recinfo.attribute17 IS NULL)
996 AND (X_Attribute17 IS NULL)))
997 AND ( (Recinfo.attribute18 = X_Attribute18)
998 OR ( (Recinfo.attribute18 IS NULL)
999 AND (X_Attribute18 IS NULL)))
1000 AND ( (Recinfo.attribute19 = X_Attribute19)
1001 OR ( (Recinfo.attribute19 IS NULL)
1002 AND (X_Attribute19 IS NULL)))
1003 AND ( (Recinfo.attribute20 = X_Attribute20)
1004 OR ( (Recinfo.attribute20 IS NULL)
1005 AND (X_Attribute20 IS NULL)))
1006 ) then
1007 return;
1008 else
1009 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1010 APP_EXCEPTION.RAISE_EXCEPTION;
1011 end if;
1012 --
1013 hr_utility.set_location(' Leaving:'||l_proc, 10);
1014 --
1015 END Lock_Row;
1016
1017 -- ----------------------------------------------------------------------------
1018 -- |---------------------------< Update_Row >---------------------------------|
1019 -- ----------------------------------------------------------------------------
1020 PROCEDURE Update_Row(X_Rowid VARCHAR2
1021 ,X_Budget_Version_id NUMBER
1022 ,X_Business_Group_Id NUMBER
1023 ,X_Budget_Id NUMBER
1024 ,X_Date_from DATE
1025 ,X_Version_number VARCHAR2
1026 ,X_Comments VARCHAR2
1027 ,X_Date_to DATE
1028 ,X_Request_id NUMBER
1029 ,X_Program_application_id NUMBER
1030 ,X_Program_id NUMBER
1031 ,X_Program_update_date DATE
1032 ,X_Attribute_Category VARCHAR2
1033 ,X_Attribute1 VARCHAR2
1034 ,X_Attribute2 VARCHAR2
1035 ,X_Attribute3 VARCHAR2
1036 ,X_Attribute4 VARCHAR2
1037 ,X_Attribute5 VARCHAR2
1038 ,X_Attribute6 VARCHAR2
1039 ,X_Attribute7 VARCHAR2
1040 ,X_Attribute8 VARCHAR2
1041 ,X_Attribute9 VARCHAR2
1042 ,X_Attribute10 VARCHAR2
1043 ,X_Attribute11 VARCHAR2
1044 ,X_Attribute12 VARCHAR2
1045 ,X_Attribute13 VARCHAR2
1046 ,X_Attribute14 VARCHAR2
1047 ,X_Attribute15 VARCHAR2
1048 ,X_Attribute16 VARCHAR2
1049 ,X_Attribute17 VARCHAR2
1050 ,X_Attribute18 VARCHAR2
1051 ,X_Attribute19 VARCHAR2
1052 ,X_Attribute20 VARCHAR2) IS
1053 l_proc VARCHAR2(72) := g_package||'Update_Row';
1054 BEGIN
1055 --
1056 hr_utility.set_location('Entering:'||l_proc, 5);
1057 --
1058 -- validate mandatory business_group
1059 hr_api.validate_bus_grp_id(X_Business_Group_Id);
1060
1061 -- validate mandatory version_number
1062 hr_api.mandatory_arg_error
1063 (p_api_name => l_proc,
1064 p_argument => 'version_number',
1065 p_argument_value => X_Version_Number);
1066
1067 -- validate mandatory rowid
1068 hr_api.mandatory_arg_error
1069 (p_api_name => l_proc,
1070 p_argument => 'rowid',
1071 p_argument_value => X_Rowid);
1072
1073 -- validate budget_version_id
1074 hr_api.mandatory_arg_error
1075 (p_api_name => l_proc,
1076 p_argument => 'budget_version_id',
1077 p_argument_value => X_Budget_Version_Id);
1078
1079 chk_budget_version_id (X_Budget_Version_Id,X_Rowid);
1080
1081 -- validate mandatory date_from is not null
1082 hr_api.mandatory_arg_error
1083 (p_api_name => l_proc,
1084 p_argument => 'date_from',
1085 p_argument_value => X_Date_From);
1086
1087 -- validate mandatory date_from is not null
1088 hr_api.mandatory_arg_error
1089 (p_api_name => l_proc,
1090 p_argument => 'budget_id',
1091 p_argument_value => X_Budget_Id);
1092
1093 -- If the parent record is OTA_BUDGET type, and no budget version exists
1094 -- for the budget_id then continue, else raise error.
1095 IF per_budgets_pkg.Chk_OTA_Budget_Type(X_Budget_id,NULL,NULL) THEN
1096 IF vers_exists(X_Budget_id,X_Rowid) THEN
1097 hr_utility.set_message(800,'PER_52873_OTA_BUD_EXISTS');
1098 hr_utility.raise_error;
1099 ELSE
1100 -- call chk routine to validate value TP
1101 chk_version_number(x_version_number);
1102 END IF;
1103 ELSE
1104 hr_utility.set_message(800,'PER_52874_NOT_OTA_VERSION');
1105 hr_utility.raise_error;
1106 END IF;
1107
1108 -- validate per_budget_versions df
1109 Chk_df(X_Attribute_Category,
1110 X_Attribute1,
1111 X_Attribute2,
1112 X_Attribute3,
1113 X_Attribute4,
1114 X_Attribute5,
1115 X_Attribute6,
1116 X_Attribute7,
1117 X_Attribute8,
1118 X_Attribute9,
1119 X_Attribute10,
1120 X_Attribute11,
1121 X_Attribute12,
1122 X_Attribute13,
1123 X_Attribute14,
1124 X_Attribute15,
1125 X_Attribute16,
1126 X_Attribute17,
1127 X_Attribute18,
1128 X_Attribute19,
1129 X_Attribute20);
1130
1131
1132 -- Get new budget_version_id
1133 UPDATE PER_BUDGET_VERSIONS
1134 SET
1135 budget_version_id = X_Budget_Version_id
1136 ,business_group_id = X_Business_Group_Id
1137 ,budget_id = X_Budget_Id
1138 ,date_from = X_Date_from
1139 ,version_number = X_Version_number
1140 ,comments = X_Comments
1141 ,date_to = X_Date_to
1142 ,request_id = X_Request_id
1143 ,program_application_id = X_Program_application_id
1144 ,program_id = X_Program_id
1145 ,program_update_date = X_Program_update_date
1146 ,attribute_category = X_Attribute_Category
1147 ,attribute1 = X_Attribute1
1148 ,attribute2 = X_Attribute2
1149 ,attribute3 = X_Attribute3
1150 ,attribute4 = X_Attribute4
1151 ,attribute5 = X_Attribute5
1152 ,attribute6 = X_Attribute6
1153 ,attribute7 = X_Attribute7
1154 ,attribute8 = X_Attribute8
1155 ,attribute9 = X_Attribute9
1156 ,attribute10 = X_Attribute10
1157 ,attribute11 = X_Attribute11
1158 ,attribute12 = X_Attribute12
1159 ,attribute13 = X_Attribute13
1160 ,attribute14 = X_Attribute14
1161 ,attribute15 = X_Attribute15
1162 ,attribute16 = X_Attribute16
1163 ,attribute17 = X_Attribute17
1164 ,attribute18 = X_Attribute18
1165 ,attribute19 = X_Attribute19
1166 ,attribute20 = X_Attribute20
1167 WHERE rowid = X_rowid;
1168
1169 if (SQL%NOTFOUND) then
1170 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1171 hr_utility.set_message_token('PROCEDURE','Update_Row');
1172 hr_utility.set_message_token('STEP','1');
1173 hr_utility.raise_error;
1174 end if;
1175 --
1176 hr_utility.set_location(' Leaving:'||l_proc, 10);
1177 --
1178 END Update_Row;
1179
1180 end PER_BUDGET_VERSION_RULES_PKG;