[Home] [Help]
PACKAGE BODY: APPS.PER_BUDGET_VALUES_PKG
Source
1 PACKAGE BODY PER_BUDGET_VALUES_PKG as
2 /* $Header: pebgv01t.pkb 115.9 2004/02/16 10:20:27 nsanghal ship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 g_package varchar2(33) := ' per_budget_values_pkg.'; -- Global package name
7
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< Chk_Unique >---------------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE Chk_Unique(X_Rowid VARCHAR2,
12 X_Business_Group_Id NUMBER,
13 X_Budget_Element_Id NUMBER,
14 X_Time_Period_Id NUMBER) is
15 l_result VARCHAR2(255);
16 l_proc VARCHAR2(72) := g_package||'Chk_Unique';
17 BEGIN
18 --
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 SELECT NULL
22 INTO l_result
23 FROM per_budget_values bv
24 WHERE X_Budget_Element_Id = bv.budget_element_id
25 AND X_Time_Period_Id = bv.time_period_id
26 AND X_Business_group_Id = bv.Business_Group_Id + 0
27 AND (bv.Rowid <> X_Rowid or X_Rowid is null);
28
29 IF (SQL%FOUND) then
30 hr_utility.set_message(801,'PER_7228_BUDGET_ONE_PER_PERIOD');
31 hr_utility.raise_error;
32 end if;
33 --
34 hr_utility.set_location(' Leaving:'||l_proc, 10);
35 --
36 EXCEPTION
37 when NO_DATA_FOUND then
38 null;
39 END Chk_Unique;
40
41 -- ----------------------------------------------------------------------------
42 -- |--------------------< chk_budget_element_id >-----------------------------|
43 -- ----------------------------------------------------------------------------
44 PROCEDURE chk_budget_element_id(x_budget_element_id IN NUMBER,
45 x_business_group_id IN NUMBER,
46 x_budget_version_id OUT NOCOPY NUMBER) IS
47 l_result VARCHAR2(255);
48 l_proc VARCHAR2(72) := g_package||'chk_budget_element_id';
49 BEGIN
50 --
51 hr_utility.set_location('Entering:'||l_proc, 5);
52 --
53 SELECT budget_version_id
54 INTO x_budget_version_id
55 FROM per_budget_elements pbe
56 WHERE pbe.budget_element_id = x_budget_element_id
57 AND pbe.business_group_id = x_business_group_id;
58 --
59 hr_utility.set_location(' Leaving:'||l_proc, 10);
60 --
61 EXCEPTION
62 WHEN NO_DATA_FOUND THEN
63 hr_utility.set_message(800,'PER_52866_INV_BUD_ELE');
64 hr_utility.raise_error;
65 END chk_budget_element_id;
66
67 -- ----------------------------------------------------------------------------
68 -- |----------------------< chk_budget_value_id >-------------------------------|
69 -- ----------------------------------------------------------------------------
70 PROCEDURE chk_budget_value_id(X_budget_value_id IN NUMBER,
71 X_rowid IN VARCHAR2) IS
72 l_result VARCHAR2(255);
73 l_proc VARCHAR2(72) := g_package||'chk_budget_value_id';
74 BEGIN
75 --
76 hr_utility.set_location('Entering:'||l_proc, 5);
77 --
78 SELECT NULL
79 INTO l_result
80 FROM per_budget_values pbv
81 WHERE pbv.budget_value_id = x_budget_value_id
82 AND (pbv.rowid <> X_rowid OR X_rowid IS NULL);
83 --
84 hr_utility.set_location(' Leaving:'||l_proc, 10);
85 --
86 IF SQL%FOUND THEN
87 hr_utility.set_message(800,'PER_52888_BUD_VAL_EXISTS');
88 hr_utility.raise_error;
89 END IF;
90 --
91 EXCEPTION
92 when NO_DATA_FOUND then
93 null;
94 END chk_budget_value_id;
95
96 -- ----------------------------------------------------------------------------
97 -- ----------------------------------------------------------------------------
98 -- |--------------------< get_many_budget_values >-----------------------------|
99 -- ----------------------------------------------------------------------------
100 FUNCTION get_many_budget_values(x_business_group_id IN NUMBER,
101 x_budget_version_id IN NUMBER) RETURN BOOLEAN IS
102
103 l_proc VARCHAR2(72) := g_package||'get_many_budget_values';
104 l_status varchar2(30);
105 l_industry varchar2(30);
106 l_owner varchar2(30);
107 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('OTA', l_status,
108 l_industry, l_owner);
109
110 -- dynamic sql statments to check if the training plan measurement table exists
111 --
112 l_stmt_chk_tpc_exist varchar2(32000) := --
113 'select ''Y'' from all_tables where table_name = ''OTA_TP_MEASUREMENT_TYPES''
114 and owner = '''||l_owner||'''';
115 --
116 -- dynamic sql statment to check if a row exists in ota_tp_measurement_types
117 --
118 l_stmt_get_tpc_rows varchar2(32000) := 'select ''Y'' from OTA_TP_MEASUREMENT_TYPES ota
119 where UPPER(ota.tp_measurement_code) = (select UPPER(unit)
120 FROM per_budgets
121 WHERE budget_id = (SELECT budget_id
122 FROM per_budget_versions
123 WHERE budget_version_id = '
124 ||x_budget_version_id || '))
125 AND ota.many_budget_values_flag = ''Y''
126 AND ota.business_group_id = ' ||x_business_group_id;
127 --
128 l_dyn_curs integer;
129 l_dyn_rows integer;
130
131 BEGIN
132 --
133 hr_utility.set_location('Entering:'||l_proc, 5);
134 --
135 -- if OTA is installed,
136 -- check if training measurement type record exists with multiple values
137 l_dyn_curs := dbms_sql.open_cursor;
138 --
139 -- Determine if the OTA_TP_MEASUREMENT_TYPES table exists
140 --
141 dbms_sql.parse(l_dyn_curs,l_stmt_chk_tpc_exist,dbms_sql.v7);
142 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
143 --
144 if dbms_sql.last_row_count > 0 then
145 -- Check that the training measurement type record exists with multiple values
146 dbms_sql.parse(l_dyn_curs,l_stmt_get_tpc_rows,dbms_sql.v7);
147 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
148 --
149 if dbms_sql.last_row_count > 0 then
150 if dbms_sql.is_open(l_dyn_curs) then
151 dbms_sql.close_cursor(l_dyn_curs);
152 end if;
153 RETURN TRUE;
154 end if;
155 end if;
156 if dbms_sql.is_open(l_dyn_curs) then
157 dbms_sql.close_cursor(l_dyn_curs);
158 end if;
159 --
160 RETURN FALSE;
161
162 hr_utility.set_location(' Leaving:'||l_proc, 10);
163 --
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 RETURN FALSE;
167 WHEN TOO_MANY_ROWS THEN
168 RETURN TRUE;
169 --
170 END get_many_budget_values;
171 -- ----------------------------------------------------------------------------
172 -- |----------------------------< Chk_df >------------------------------------|
173 -- ----------------------------------------------------------------------------
174 PROCEDURE Chk_df(x_attribute_category varchar2
175 ,x_attribute1 varchar2
176 ,x_attribute2 varchar2
177 ,x_attribute3 varchar2
178 ,x_attribute4 varchar2
179 ,x_attribute5 varchar2
180 ,x_attribute6 varchar2
181 ,x_attribute7 varchar2
182 ,x_attribute8 varchar2
183 ,x_attribute9 varchar2
184 ,x_attribute10 varchar2
185 ,x_attribute11 varchar2
186 ,x_attribute12 varchar2
187 ,x_attribute13 varchar2
188 ,x_attribute14 varchar2
189 ,x_attribute15 varchar2
190 ,x_attribute16 varchar2
191 ,x_attribute17 varchar2
192 ,x_attribute18 varchar2
193 ,x_attribute19 varchar2
194 ,x_attribute20 varchar2
195 ,x_attribute21 varchar2
196 ,x_attribute22 varchar2
197 ,x_attribute23 varchar2
198 ,x_attribute24 varchar2
199 ,x_attribute25 varchar2
200 ,x_attribute26 varchar2
201 ,x_attribute27 varchar2
202 ,x_attribute28 varchar2
203 ,x_attribute29 varchar2
204 ,x_attribute30 varchar2) IS
205
206 l_proc VARCHAR2(72) := g_package||'Chk_df';
207
208 BEGIN
209 --
210 hr_utility.set_location('Entering:'||l_proc, 5);
211 --
212 hr_dflex_utility.ins_or_upd_descflex_attribs
213 (p_appl_short_name => 'PER'
214 ,p_descflex_name => 'PER_BUDGET_VALUES'
215 ,p_attribute_category => x_attribute_category
216 ,p_attribute1_name => 'ATTRIBUTE1'
217 ,p_attribute1_value => x_attribute1
218 ,p_attribute2_name => 'ATTRIBUTE2'
219 ,p_attribute2_value => x_attribute2
220 ,p_attribute3_name => 'ATTRIBUTE3'
221 ,p_attribute3_value => x_attribute3
222 ,p_attribute4_name => 'ATTRIBUTE4'
223 ,p_attribute4_value => x_attribute4
224 ,p_attribute5_name => 'ATTRIBUTE5'
225 ,p_attribute5_value => x_attribute5
226 ,p_attribute6_name => 'ATTRIBUTE6'
227 ,p_attribute6_value => x_attribute6
228 ,p_attribute7_name => 'ATTRIBUTE7'
229 ,p_attribute7_value => x_attribute7
230 ,p_attribute8_name => 'ATTRIBUTE8'
231 ,p_attribute8_value => x_attribute8
232 ,p_attribute9_name => 'ATTRIBUTE9'
233 ,p_attribute9_value => x_attribute9
234 ,p_attribute10_name => 'ATTRIBUTE10'
235 ,p_attribute10_value => x_attribute10
236 ,p_attribute11_name => 'ATTRIBUTE11'
237 ,p_attribute11_value => x_attribute11
238 ,p_attribute12_name => 'ATTRIBUTE12'
239 ,p_attribute12_value => x_attribute12
240 ,p_attribute13_name => 'ATTRIBUTE13'
241 ,p_attribute13_value => x_attribute13
242 ,p_attribute14_name => 'ATTRIBUTE14'
243 ,p_attribute14_value => x_attribute14
244 ,p_attribute15_name => 'ATTRIBUTE15'
245 ,p_attribute15_value => x_attribute15
246 ,p_attribute16_name => 'ATTRIBUTE16'
247 ,p_attribute16_value => x_attribute16
248 ,p_attribute17_name => 'ATTRIBUTE17'
249 ,p_attribute17_value => x_attribute17
250 ,p_attribute18_name => 'ATTRIBUTE18'
251 ,p_attribute18_value => x_attribute18
252 ,p_attribute19_name => 'ATTRIBUTE19'
253 ,p_attribute19_value => x_attribute19
254 ,p_attribute20_name => 'ATTRIBUTE20'
255 ,p_attribute20_value => x_attribute20
256 ,p_attribute21_name => 'ATTRIBUTE21'
257 ,p_attribute21_value => x_attribute21
258 ,p_attribute22_name => 'ATTRIBUTE22'
259 ,p_attribute22_value => x_attribute22
260 ,p_attribute23_name => 'ATTRIBUTE23'
261 ,p_attribute23_value => x_attribute23
262 ,p_attribute24_name => 'ATTRIBUTE24'
263 ,p_attribute24_value => x_attribute24
264 ,p_attribute25_name => 'ATTRIBUTE25'
265 ,p_attribute25_value => x_attribute25
266 ,p_attribute26_name => 'ATTRIBUTE26'
267 ,p_attribute26_value => x_attribute26
268 ,p_attribute27_name => 'ATTRIBUTE27'
269 ,p_attribute27_value => x_attribute27
270 ,p_attribute28_name => 'ATTRIBUTE28'
271 ,p_attribute28_value => x_attribute28
272 ,p_attribute29_name => 'ATTRIBUTE29'
273 ,p_attribute29_value => x_attribute29
274 ,p_attribute30_name => 'ATTRIBUTE30'
275 ,p_attribute30_value => x_attribute30);
276 --
277 hr_utility.set_location(' Leaving:'||l_proc, 10);
278 --
279 END Chk_df;
280
281 -- ----------------------------------------------------------------------------
282 -- |----------------------------< Chk_ddf >------------------------------------|
283 -- ----------------------------------------------------------------------------
284 PROCEDURE Chk_ddf(x_information_category varchar2
285 ,x_information1 varchar2
286 ,x_information2 varchar2
287 ,x_information3 varchar2
288 ,x_information4 varchar2
289 ,x_information5 varchar2
290 ,x_information6 varchar2
291 ,x_information7 varchar2
292 ,x_information8 varchar2
293 ,x_information9 varchar2
294 ,x_information10 varchar2
295 ,x_information11 varchar2
296 ,x_information12 varchar2
297 ,x_information13 varchar2
298 ,x_information14 varchar2
299 ,x_information15 varchar2
300 ,x_information16 varchar2
301 ,x_information17 varchar2
302 ,x_information18 varchar2
303 ,x_information19 varchar2
304 ,x_information20 varchar2
305 ,x_information21 varchar2
306 ,x_information22 varchar2
307 ,x_information23 varchar2
308 ,x_information24 varchar2
309 ,x_information25 varchar2
310 ,x_information26 varchar2
311 ,x_information27 varchar2
312 ,x_information28 varchar2
313 ,x_information29 varchar2
314 ,x_information30 varchar2) IS
315
316 l_proc VARCHAR2(72) := g_package||'Chk_ddf';
317
318 BEGIN
319 --
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 hr_dflex_utility.ins_or_upd_descflex_attribs
323 (p_appl_short_name => 'PER'
324 ,p_descflex_name => 'Budget Values Developer DF'
325 ,p_attribute_category => x_information_category
326 ,p_attribute1_name => 'BUDGET_INFORMATION1'
327 ,p_attribute1_value => x_information1
328 ,p_attribute2_name => 'BUDGET_INFORMATION2'
329 ,p_attribute2_value => x_information2
330 ,p_attribute3_name => 'BUDGET_INFORMATION3'
331 ,p_attribute3_value => x_information3
332 ,p_attribute4_name => 'BUDGET_INFORMATION4'
333 ,p_attribute4_value => x_information4
334 ,p_attribute5_name => 'BUDGET_INFORMATION5'
335 ,p_attribute5_value => x_information5
336 ,p_attribute6_name => 'BUDGET_INFORMATION6'
337 ,p_attribute6_value => x_information6
338 ,p_attribute7_name => 'BUDGET_INFORMATION7'
339 ,p_attribute7_value => x_information7
340 ,p_attribute8_name => 'BUDGET_INFORMATION8'
341 ,p_attribute8_value => x_information8
342 ,p_attribute9_name => 'BUDGET_INFORMATION9'
343 ,p_attribute9_value => x_information9
344 ,p_attribute10_name => 'BUDGET_INFORMATION10'
345 ,p_attribute10_value => x_information10
346 ,p_attribute11_name => 'BUDGET_INFORMATION11'
347 ,p_attribute11_value => x_information11
348 ,p_attribute12_name => 'BUDGET_INFORMATION12'
349 ,p_attribute12_value => x_information12
350 ,p_attribute13_name => 'BUDGET_INFORMATION13'
351 ,p_attribute13_value => x_information13
352 ,p_attribute14_name => 'BUDGET_INFORMATION14'
353 ,p_attribute14_value => x_information14
354 ,p_attribute15_name => 'BUDGET_INFORMATION15'
355 ,p_attribute15_value => x_information15
356 ,p_attribute16_name => 'BUDGET_INFORMATION16'
357 ,p_attribute16_value => x_information16
358 ,p_attribute17_name => 'BUDGET_INFORMATION17'
362 ,p_attribute19_name => 'BUDGET_INFORMATION19'
359 ,p_attribute17_value => x_information17
360 ,p_attribute18_name => 'BUDGET_INFORMATION18'
361 ,p_attribute18_value => x_information18
363 ,p_attribute19_value => x_information19
364 ,p_attribute20_name => 'BUDGET_INFORMATION20'
365 ,p_attribute20_value => x_information20
366 ,p_attribute21_name => 'BUDGET_INFORMATION21'
367 ,p_attribute21_value => x_information21
368 ,p_attribute22_name => 'BUDGET_INFORMATION22'
369 ,p_attribute22_value => x_information22
370 ,p_attribute23_name => 'BUDGET_INFORMATION23'
371 ,p_attribute23_value => x_information23
372 ,p_attribute24_name => 'BUDGET_INFORMATION24'
373 ,p_attribute24_value => x_information24
374 ,p_attribute25_name => 'BUDGET_INFORMATION25'
375 ,p_attribute25_value => x_information25
376 ,p_attribute26_name => 'BUDGET_INFORMATION26'
377 ,p_attribute26_value => x_information26
378 ,p_attribute27_name => 'BUDGET_INFORMATION27'
379 ,p_attribute27_value => x_information27
380 ,p_attribute28_name => 'BUDGET_INFORMATION28'
381 ,p_attribute28_value => x_information28
382 ,p_attribute29_name => 'BUDGET_INFORMATION29'
383 ,p_attribute29_value => x_information29
384 ,p_attribute30_name => 'BUDGET_INFORMATION30'
385 ,p_attribute30_value => x_information30);
386 --
387 hr_utility.set_location(' Leaving:'||l_proc, 10);
388 --
389 END Chk_ddf;
390
391
392 -- ----------------------------------------------------------------------------
393 -- |---------------------------< Insert_Row >---------------------------------|
394 -- ----------------------------------------------------------------------------
395 PROCEDURE Insert_Row(x_Rowid IN OUT NOCOPY VARCHAR2,
396 x_Budget_Value_Id IN OUT NOCOPY NUMBER,
397 x_Business_Group_Id IN NUMBER,
398 x_Budget_Element_Id IN NUMBER,
399 x_Time_Period_Id IN NUMBER,
400 x_Value IN NUMBER,
401 x_attribute_category IN VARCHAR2,
402 x_attribute1 IN VARCHAR2,
403 x_attribute2 IN VARCHAR2,
404 x_attribute3 IN VARCHAR2,
405 x_attribute4 IN VARCHAR2,
406 x_attribute5 IN VARCHAR2,
407 x_attribute6 IN VARCHAR2,
408 x_attribute7 IN VARCHAR2,
409 x_attribute8 IN VARCHAR2,
410 x_attribute9 IN VARCHAR2,
411 x_attribute10 IN VARCHAR2,
412 x_attribute11 IN VARCHAR2,
413 x_attribute12 IN VARCHAR2,
414 x_attribute13 IN VARCHAR2,
415 x_attribute14 IN VARCHAR2,
416 x_attribute15 IN VARCHAR2,
417 x_attribute16 IN VARCHAR2,
418 x_attribute17 IN VARCHAR2,
419 x_attribute18 IN VARCHAR2,
420 x_attribute19 IN VARCHAR2,
421 x_attribute20 IN VARCHAR2,
422 x_attribute21 IN VARCHAR2,
423 x_attribute22 IN VARCHAR2,
424 x_attribute23 IN VARCHAR2,
425 x_attribute24 IN VARCHAR2,
426 x_attribute25 IN VARCHAR2,
427 x_attribute26 IN VARCHAR2,
428 x_attribute27 IN VARCHAR2,
429 x_attribute28 IN VARCHAR2,
430 x_attribute29 IN VARCHAR2,
431 x_attribute30 IN VARCHAR2,
432 x_information_category IN VARCHAR2,
433 x_information1 IN VARCHAR2,
434 x_information2 IN VARCHAR2,
435 x_information3 IN VARCHAR2,
436 x_information4 IN VARCHAR2,
437 x_information5 IN VARCHAR2,
438 x_information6 IN VARCHAR2,
439 x_information7 IN VARCHAR2,
440 x_information8 IN VARCHAR2,
441 x_information9 IN VARCHAR2,
442 x_information10 IN VARCHAR2,
443 x_information11 IN VARCHAR2,
444 x_information12 IN VARCHAR2,
445 x_information13 IN VARCHAR2,
446 x_information14 IN VARCHAR2,
447 x_information15 IN VARCHAR2,
448 x_information16 IN VARCHAR2,
449 x_information17 IN VARCHAR2,
453 x_information21 IN vARCHAR2,
450 x_information18 IN VARCHAR2,
451 x_information19 IN VARCHAR2,
452 x_information20 IN VARCHAR2,
454 x_information22 IN VARCHAR2,
455 x_information23 IN VARCHAR2,
456 x_information24 IN VARCHAR2,
457 x_information25 IN VARCHAR2,
458 x_information26 IN VARCHAR2,
459 x_information27 IN VARCHAR2,
460 x_information28 IN VARCHAR2,
461 x_information29 IN VARCHAR2,
462 x_information30 IN VARCHAR2
463 ) IS
464 CURSOR C1 IS SELECT rowid FROM per_budget_values
465 WHERE budget_value_id = X_Budget_Value_Id;
466
467 CURSOR C2 IS SELECT per_budget_values_s.nextval FROM dual;
468
469 CURSOR C3 IS
470 SELECT null
471 from per_budget_values pb
472 where pb.budget_element_id = x_budget_element_id;
473
474 l_proc VARCHAR2(72) := g_package||'Insert_Row';
475 l_budget_version_id NUMBER(15);
476 l_result VARCHAR2(255);
477 BEGIN
478 --
479 hr_utility.set_location('Entering:'||l_proc, 5);
480 --
481 -- validate mandatory business_group
482 hr_api.validate_bus_grp_id(X_Business_Group_Id);
483 --
484 -- validate mandatory budget_element_id, and return parent budget_version_id value.
485 hr_api.mandatory_arg_error
486 (p_api_name => l_proc,
487 p_argument => 'budget_element_id',
488 p_argument_value => x_Budget_Element_Id);
489
490 chk_budget_element_id(x_budget_element_id,x_business_group_id,l_budget_version_id);
491 -- validate mandatory time_period_id
492 hr_api.mandatory_arg_error
493 (p_api_name => l_proc,
494 p_argument => 'time_period_id',
495 p_argument_value => x_time_period_id);
496 --
497 -- Is the parent per_budgets rec 'OTA_BUDGET'
498 -- If so, does a per_budget_values rec already exist for the budget_element_id
499 -- If so, check many_budget_values flag to see if another per_budget_values record
500 -- should be created otherwise raise error.
501 IF per_budgets_pkg.chk_OTA_budget_type(NULL,l_budget_version_id,NULL) THEN
502 OPEN C3;
503 FETCH C3 into l_result;
504 IF C3%FOUND THEN
505 IF get_many_budget_values(x_business_group_id, l_budget_version_id) = FALSE THEN
506 hr_utility.set_message(800,'PER_52865_SINGLE_BDGT_VAL');
507 hr_utility.raise_error;
508 END IF;
509 END IF;
510 ELSE
511 -- only allow 1 record to exist for the business_group, budget_element_id, and time_period
512 -- parent per_budgets record is 'HR_BUDGET' budget_type_code.
513
514 chk_unique(X_Rowid,
515 X_Business_Group_id,
516 X_Budget_Element_Id,
517 X_Time_Period_Id);
518 --
519 END IF;
520 --
521
522 -- validate developer desc flex
523 Chk_ddf(x_information_category,
524 x_information1,
525 x_information2,
526 x_information3,
527 x_information4,
528 x_information5,
529 x_information6,
530 x_information7,
531 x_information8,
532 x_information9,
533 x_information10,
534 x_information11,
535 x_information12,
536 x_information13,
537 x_information14,
538 x_information15,
539 x_information16,
540 x_information17,
541 x_information18,
542 x_information19,
543 x_information20,
544 x_information21,
545 x_information22,
546 x_information23,
547 x_information24,
548 x_information25,
549 x_information26,
550 x_information27,
551 x_information28,
552 x_information29,
553 x_information30);
554
555 -- validate desc flex
556 Chk_df(x_Attribute_Category,
557 x_Attribute1,
558 x_Attribute2,
559 x_Attribute3,
560 x_Attribute4,
561 x_Attribute5,
562 x_Attribute6,
563 x_Attribute7,
564 x_Attribute8,
565 x_Attribute9,
566 x_Attribute10,
567 x_Attribute11,
568 x_Attribute12,
569 x_Attribute13,
570 x_Attribute14,
571 x_Attribute15,
572 x_Attribute16,
573 x_Attribute17,
574 x_Attribute18,
575 x_Attribute19,
576 x_Attribute20,
577 x_Attribute21,
578 x_Attribute22,
579 x_Attribute23,
580 x_Attribute24,
581 x_Attribute25,
582 x_Attribute26,
583 x_Attribute27,
584 x_Attribute28,
585 x_Attribute29,
586 x_Attribute30);
587
588 OPEN C2;
589 FETCH C2 INTO X_Budget_Value_Id;
590 CLOSE C2;
591
592 INSERT INTO per_budget_values(
596 time_period_id,
593 budget_value_id,
594 business_group_id,
595 budget_element_id,
597 value,
598 information_category,
599 budget_information1,
600 budget_information2,
601 budget_information3,
602 budget_information4,
603 budget_information5,
604 budget_information6,
605 budget_information7,
606 budget_information8,
607 budget_information9,
608 budget_information10,
609 budget_information11,
610 budget_information12,
611 budget_information13,
612 budget_information14,
613 budget_information15,
614 budget_information16,
615 budget_information17,
616 budget_information18,
617 budget_information19,
618 budget_information20,
619 budget_information21,
620 budget_information22,
621 budget_information23,
622 budget_information24,
623 budget_information25,
624 budget_information26,
625 budget_information27,
626 budget_information28,
627 budget_information29,
628 budget_information30,
629 attribute_category,
630 attribute1,
631 attribute2,
632 attribute3,
633 attribute4,
634 attribute5,
635 attribute6,
636 attribute7,
637 attribute8,
638 attribute9,
639 attribute10,
640 attribute11,
641 attribute12,
642 attribute13,
643 attribute14,
644 attribute15,
645 attribute16,
646 attribute17,
647 attribute18,
648 attribute19,
649 attribute20,
650 attribute21,
651 attribute22,
652 attribute23,
653 attribute24,
654 attribute25,
655 attribute26,
656 attribute27,
657 attribute28,
658 attribute29,
659 attribute30
660 ) VALUES (
661 x_budget_value_id,
662 x_business_group_id,
663 x_budget_element_id,
664 x_time_period_id,
665 x_value,
666 x_information_category,
667 x_information1,
668 x_information2,
669 x_information3,
670 x_information4,
671 x_information5,
672 x_information6,
673 x_information7,
674 x_information8,
675 x_information9,
676 x_information10,
677 x_information11,
678 x_information12,
679 x_information13,
680 x_information14,
681 x_information15,
682 x_information16,
683 x_information17,
684 x_information18,
685 x_information19,
686 x_information20,
687 x_information21,
688 x_information22,
689 x_information23,
690 x_information24,
691 x_information25,
692 x_information26,
693 x_information27,
694 x_information28,
695 x_information29,
696 x_information30,
697 x_attribute_category,
698 x_attribute1,
699 x_attribute2,
700 x_attribute3,
701 x_attribute4,
702 x_attribute5,
703 x_attribute6,
704 x_attribute7,
705 x_attribute8,
706 x_attribute9,
707 x_attribute10,
708 x_attribute11,
709 x_attribute12,
710 x_attribute13,
711 x_attribute14,
712 x_attribute15,
713 x_attribute16,
714 x_attribute17,
715 x_attribute18,
716 x_attribute19,
717 x_attribute20,
718 x_attribute21,
719 x_attribute22,
720 x_attribute23,
721 x_attribute24,
722 x_attribute25,
723 x_Attribute26,
724 x_attribute27,
725 x_attribute28,
726 x_attribute29,
727 x_attribute30);
728
729 OPEN C1;
730 FETCH C1 INTO X_Rowid;
731 if (C1%NOTFOUND) then
732 CLOSE C1;
733 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
734 hr_utility.set_message_token('PROCEDURE','Insert_Row');
735 hr_utility.set_message_token('STEP','1');
736 hr_utility.raise_error;
737 end if;
738 CLOSE C1;
739 --
740 hr_utility.set_location(' Leaving:'||l_proc, 10);
741 --
742 END Insert_Row;
743 --
744 -- ----------------------------------------------------------------------------
745 -- |-----------------------------< Lock_Row >---------------------------------|
746 -- ----------------------------------------------------------------------------
747 PROCEDURE Lock_Row(X_Rowid IN VARCHAR2,
748 X_Budget_Value_Id IN NUMBER,
749 X_Business_Group_Id IN NUMBER,
750 X_Budget_Element_Id IN NUMBER,
754 X_attribute1 IN VARCHAR2,
751 X_Time_Period_Id IN NUMBER,
752 X_Value IN NUMBER,
753 X_attribute_category IN VARCHAR2,
755 X_attribute2 IN VARCHAR2,
756 X_attribute3 IN VARCHAR2,
757 X_attribute4 IN VARCHAR2,
758 X_attribute5 IN VARCHAR2,
759 X_attribute6 IN VARCHAR2,
760 X_attribute7 IN VARCHAR2,
761 X_attribute8 IN VARCHAR2,
762 X_attribute9 IN VARCHAR2,
763 X_attribute10 IN VARCHAR2,
764 X_attribute11 IN VARCHAR2,
765 X_attribute12 IN VARCHAR2,
766 X_attribute13 IN VARCHAR2,
767 X_attribute14 IN VARCHAR2,
768 X_attribute15 IN VARCHAR2,
769 X_attribute16 IN VARCHAR2,
770 X_attribute17 IN VARCHAR2,
771 X_attribute18 IN VARCHAR2,
772 X_attribute19 IN VARCHAR2,
773 X_attribute20 IN VARCHAR2,
774 X_attribute21 IN VARCHAR2,
775 X_attribute22 IN VARCHAR2,
776 X_attribute23 IN VARCHAR2,
777 X_attribute24 IN VARCHAR2,
778 X_attribute25 IN VARCHAR2,
779 X_attribute26 IN VARCHAR2,
780 X_attribute27 IN VARCHAR2,
781 X_attribute28 IN VARCHAR2,
782 X_attribute29 IN VARCHAR2,
783 X_attribute30 IN VARCHAR2,
784 X_information_category IN VARCHAR2,
785 X_information1 IN VARCHAR2,
786 X_information2 IN VARCHAR2,
787 X_information3 IN VARCHAR2,
788 X_information4 IN VARCHAR2,
789 X_information5 IN VARCHAR2,
790 X_information6 IN VARCHAR2,
791 X_information7 IN VARCHAR2,
792 X_information8 IN VARCHAR2,
793 X_information9 IN VARCHAR2,
794 X_information10 IN VARCHAR2,
795 X_information11 IN VARCHAR2,
796 X_information12 IN VARCHAR2,
797 X_information13 IN VARCHAR2,
798 X_information14 IN VARCHAR2,
799 X_information15 IN VARCHAR2,
800 X_information16 IN VARCHAR2,
801 X_information17 IN VARCHAR2,
802 X_information18 IN VARCHAR2,
803 X_information19 IN VARCHAR2,
804 X_information20 IN VARCHAR2,
805 X_information21 IN vARCHAR2,
806 X_information22 IN VARCHAR2,
807 X_information23 IN VARCHAR2,
808 X_information24 IN VARCHAR2,
809 X_information25 IN VARCHAR2,
810 X_information26 IN VARCHAR2,
811 X_information27 IN VARCHAR2,
812 X_information28 IN VARCHAR2,
813 X_information29 IN VARCHAR2,
814 X_information30 IN VARCHAR2
815 ) IS
816 CURSOR C IS
817 SELECT *
818 FROM per_budget_values
819 WHERE rowid = X_Rowid
820 FOR UPDATE of Budget_Element_Id NOWAIT;
821 Recinfo C%ROWTYPE;
822 l_proc VARCHAR2(72) := g_package||'Lock_Row';
823 BEGIN
824 --
825 hr_utility.set_location('Entering:'||l_proc, 5);
826 --
827 OPEN C;
828 FETCH C INTO Recinfo;
829 if (C%NOTFOUND) then
830 CLOSE C;
831 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
832 hr_utility.set_message_token('PROCEDURE','Lock_Row');
833 hr_utility.set_message_token('STEP','1');
834 hr_utility.raise_error;
835 end if;
836 CLOSE C;
837 if (
838 ( (Recinfo.budget_value_id = X_Budget_Value_Id)
839 OR ( (Recinfo.budget_value_id IS NULL)
840 AND (X_Budget_Value_Id IS NULL)))
841 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
845 OR ( (Recinfo.budget_element_id IS NULL)
842 OR ( (Recinfo.business_group_id IS NULL)
843 AND (X_Business_Group_Id IS NULL)))
844 AND ( (Recinfo.budget_element_id = X_Budget_Element_Id)
846 AND (X_Budget_Element_Id IS NULL)))
847 AND ( (Recinfo.time_period_id = X_Time_Period_Id)
848 OR ( (Recinfo.time_period_id IS NULL)
849 AND (X_Time_Period_Id IS NULL)))
850 AND ( (Recinfo.value = X_Value)
851 OR ( (Recinfo.value IS NULL)
852 AND (X_Value IS NULL)))
853 AND ( (Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
854 OR ( (Recinfo.ATTRIBUTE_CATEGORY IS NULL)
855 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
856 AND ( (Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
857 OR ( (Recinfo.ATTRIBUTE1 IS NULL)
858 AND (X_ATTRIBUTE1 IS NULL)))
859 AND ( (Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
860 OR ( (Recinfo.ATTRIBUTE2 IS NULL)
861 AND (X_ATTRIBUTE2 IS NULL)))
862 AND ( (Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
863 OR ( (Recinfo.ATTRIBUTE3 IS NULL)
864 AND (X_ATTRIBUTE3 IS NULL)))
865 AND ( (Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
866 OR ( (Recinfo.ATTRIBUTE4 IS NULL)
867 AND (X_ATTRIBUTE4 IS NULL)))
868 AND ( (Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
869 OR ( (Recinfo.ATTRIBUTE5 IS NULL)
870 AND (X_ATTRIBUTE5 IS NULL)))
871 AND ( (Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
872 OR ( (Recinfo.ATTRIBUTE6 IS NULL)
873 AND (X_ATTRIBUTE6 IS NULL)))
874 AND ( (Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
875 OR ( (Recinfo.ATTRIBUTE7 IS NULL)
876 AND (X_ATTRIBUTE7 IS NULL)))
877 AND ( (Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
878 OR ( (Recinfo.ATTRIBUTE8 IS NULL)
879 AND (X_ATTRIBUTE8 IS NULL)))
880 AND ( (Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
881 OR ( (Recinfo.ATTRIBUTE9 IS NULL)
882 AND (X_ATTRIBUTE9 IS NULL)))
883 AND ( (Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
884 OR ( (Recinfo.ATTRIBUTE10 IS NULL)
885 AND (X_ATTRIBUTE10 IS NULL)))
886 AND ( (Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
887 OR ( (Recinfo.ATTRIBUTE11 IS NULL)
888 AND (X_ATTRIBUTE11 IS NULL)))
889 AND ( (Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
890 OR ( (Recinfo.ATTRIBUTE12 IS NULL)
891 AND (X_ATTRIBUTE12 IS NULL)))
892 AND ( (Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
893 OR ( (Recinfo.ATTRIBUTE13 IS NULL)
894 AND (X_ATTRIBUTE13 IS NULL)))
895 AND ( (Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
896 OR ( (Recinfo.ATTRIBUTE14 IS NULL)
897 AND (X_ATTRIBUTE14 IS NULL)))
898 AND ( (Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
899 OR ( (Recinfo.ATTRIBUTE15 IS NULL)
900 AND (X_ATTRIBUTE15 IS NULL)))
901 AND ( (Recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
902 OR ( (Recinfo.ATTRIBUTE16 IS NULL)
903 AND (X_ATTRIBUTE16 IS NULL)))
904 AND ( (Recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
905 OR ( (Recinfo.ATTRIBUTE17 IS NULL)
906 AND (X_ATTRIBUTE17 IS NULL)))
907 AND ( (Recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
908 OR ( (Recinfo.ATTRIBUTE18 IS NULL)
909 AND (X_ATTRIBUTE18 IS NULL)))
910 AND ( (Recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
911 OR ( (Recinfo.ATTRIBUTE19 IS NULL)
912 AND (X_ATTRIBUTE19 IS NULL)))
913 AND ( (Recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
914 OR ( (Recinfo.ATTRIBUTE20 IS NULL)
915 AND (X_ATTRIBUTE20 IS NULL)))
916 AND ( (Recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
917 OR ( (Recinfo.ATTRIBUTE21 IS NULL)
918 AND (X_ATTRIBUTE21 IS NULL)))
919 AND ( (Recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
920 OR ( (Recinfo.ATTRIBUTE22 IS NULL)
921 AND (X_ATTRIBUTE22 IS NULL)))
922 AND ( (Recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
923 OR ( (Recinfo.ATTRIBUTE23 IS NULL)
924 AND (X_ATTRIBUTE23 IS NULL)))
925 AND ( (Recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
926 OR ( (Recinfo.ATTRIBUTE24 IS NULL)
927 AND (X_ATTRIBUTE24 IS NULL)))
928 AND ( (Recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
929 OR ( (Recinfo.ATTRIBUTE25 IS NULL)
930 AND (X_ATTRIBUTE25 IS NULL)))
931 AND ( (Recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
932 OR ( (Recinfo.ATTRIBUTE26 IS NULL)
933 AND (X_ATTRIBUTE26 IS NULL)))
934 AND ( (Recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
935 OR ( (Recinfo.ATTRIBUTE27 IS NULL)
936 AND (X_ATTRIBUTE27 IS NULL)))
937 AND ( (Recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
938 OR ( (Recinfo.ATTRIBUTE28 IS NULL)
939 AND (X_ATTRIBUTE28 IS NULL)))
940 AND ( (Recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
941 OR ( (Recinfo.ATTRIBUTE29 IS NULL)
942 AND (X_ATTRIBUTE29 IS NULL)))
943 AND ( (Recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
944 OR ( (Recinfo.ATTRIBUTE30 IS NULL)
945 AND (X_ATTRIBUTE30 IS NULL)))
949 AND ( (Recinfo.BUDGET_INFORMATION1 = X_INFORMATION1 )
946 AND ( (Recinfo.INFORMATION_CATEGORY = X_INFORMATION_CATEGORY)
947 OR ( (Recinfo.INFORMATION_CATEGORY IS NULL)
948 AND (X_INFORMATION_CATEGORY IS NULL)))
950 OR ( (Recinfo.BUDGET_INFORMATION1 IS NULL)
951 AND (X_INFORMATION1 IS NULL)))
952 AND ( (Recinfo.BUDGET_INFORMATION2 = X_INFORMATION2)
953 OR ( (Recinfo.BUDGET_INFORMATION2 IS NULL)
954 AND (X_INFORMATION2 IS NULL)))
955 AND ( (Recinfo.BUDGET_INFORMATION3 = X_INFORMATION3)
956 OR ( (Recinfo.BUDGET_INFORMATION3 IS NULL)
957 AND (X_INFORMATION3 IS NULL)))
958 AND ( (Recinfo.BUDGET_INFORMATION4 = X_INFORMATION4)
959 OR ( (Recinfo.BUDGET_INFORMATION4 IS NULL)
960 AND (X_INFORMATION4 IS NULL)))
961 AND ( (Recinfo.BUDGET_INFORMATION5 = X_INFORMATION5)
962 OR ( (Recinfo.BUDGET_INFORMATION5 IS NULL)
963 AND (X_INFORMATION5 IS NULL)))
964 AND ( (Recinfo.BUDGET_INFORMATION6 = X_INFORMATION6)
965 OR ( (Recinfo.BUDGET_INFORMATION6 IS NULL)
966 AND (X_INFORMATION6 IS NULL)))
967 AND ( (Recinfo.BUDGET_INFORMATION7 = X_INFORMATION7)
968 OR ( (Recinfo.BUDGET_INFORMATION7 IS NULL)
969 AND (X_INFORMATION7 IS NULL)))
970 AND ( (Recinfo.BUDGET_INFORMATION8 = X_INFORMATION8)
971 OR ( (Recinfo.BUDGET_INFORMATION8 IS NULL)
972 AND (X_INFORMATION8 IS NULL)))
973 AND ( (Recinfo.BUDGET_INFORMATION9 = X_INFORMATION9)
974 OR ( (Recinfo.BUDGET_INFORMATION9 IS NULL)
975 AND (X_INFORMATION9 IS NULL)))
976 AND ( (Recinfo.BUDGET_INFORMATION10 = X_INFORMATION10)
977 OR ( (Recinfo.BUDGET_INFORMATION10 IS NULL)
978 AND (X_INFORMATION10 IS NULL)))
979 AND ( (Recinfo.BUDGET_INFORMATION11 = X_INFORMATION11)
980 OR ( (Recinfo.BUDGET_INFORMATION11 IS NULL)
981 AND (X_INFORMATION11 IS NULL)))
982 AND ( (Recinfo.BUDGET_INFORMATION12 = X_INFORMATION12 )
983 OR ( (Recinfo.BUDGET_INFORMATION12 IS NULL)
984 AND (X_INFORMATION12 IS NULL)))
985 AND ( (Recinfo.BUDGET_INFORMATION13 = X_INFORMATION13)
986 OR ( (Recinfo.BUDGET_INFORMATION13 IS NULL)
987 AND (X_INFORMATION13 IS NULL)))
988 AND ( (Recinfo.BUDGET_INFORMATION14 = X_INFORMATION14)
989 OR ( (Recinfo.BUDGET_INFORMATION14 IS NULL)
990 AND (X_INFORMATION14 IS NULL)))
991 AND ( (Recinfo.BUDGET_INFORMATION15 = X_INFORMATION15)
992 OR ( (Recinfo.BUDGET_INFORMATION15 IS NULL)
993 AND (X_INFORMATION15 IS NULL)))
994 AND ( (Recinfo.BUDGET_INFORMATION16 = X_INFORMATION16)
995 OR ( (Recinfo.BUDGET_INFORMATION16 IS NULL)
996 AND (X_INFORMATION16 IS NULL)))
997 AND ( (Recinfo.BUDGET_INFORMATION17 = X_INFORMATION17)
998 OR ( (Recinfo.BUDGET_INFORMATION17 IS NULL)
999 AND (X_INFORMATION17 IS NULL)))
1000 AND ( (Recinfo.BUDGET_INFORMATION18 = X_INFORMATION18)
1001 OR ( (Recinfo.BUDGET_INFORMATION18 IS NULL)
1002 AND (X_INFORMATION18 IS NULL)))
1003 AND ( (Recinfo.BUDGET_INFORMATION19 = X_INFORMATION19)
1004 OR ( (Recinfo.BUDGET_INFORMATION19 IS NULL)
1005 AND (X_INFORMATION19 IS NULL)))
1006 AND ( (Recinfo.BUDGET_INFORMATION20 = X_INFORMATION20)
1007 OR ( (Recinfo.BUDGET_INFORMATION20 IS NULL)
1008 AND (X_INFORMATION20 IS NULL)))
1009 AND ( (Recinfo.BUDGET_INFORMATION21 = X_INFORMATION21)
1010 OR ( (Recinfo.BUDGET_INFORMATION21 IS NULL)
1011 AND (X_INFORMATION21 IS NULL)))
1012 AND ( (Recinfo.BUDGET_INFORMATION22 = X_INFORMATION22 )
1013 OR ( (Recinfo.BUDGET_INFORMATION22 IS NULL)
1014 AND (X_INFORMATION22 IS NULL)))
1015 AND ( (Recinfo.BUDGET_INFORMATION23 = X_INFORMATION23)
1016 OR ( (Recinfo.BUDGET_INFORMATION23 IS NULL)
1017 AND (X_INFORMATION23 IS NULL)))
1018 AND ( (Recinfo.BUDGET_INFORMATION24 = X_INFORMATION24)
1019 OR ( (Recinfo.BUDGET_INFORMATION24 IS NULL)
1020 AND (X_INFORMATION24 IS NULL)))
1021 AND ( (Recinfo.BUDGET_INFORMATION25 = X_INFORMATION25)
1022 OR ( (Recinfo.BUDGET_INFORMATION25 IS NULL)
1023 AND (X_INFORMATION25 IS NULL)))
1024 AND ( (Recinfo.BUDGET_INFORMATION26 = X_INFORMATION26)
1025 OR ( (Recinfo.BUDGET_INFORMATION26 IS NULL)
1026 AND (X_INFORMATION26 IS NULL)))
1027 AND ( (Recinfo.BUDGET_INFORMATION27 = X_INFORMATION27)
1028 OR ( (Recinfo.BUDGET_INFORMATION27 IS NULL)
1029 AND (X_INFORMATION27 IS NULL)))
1030 AND ( (Recinfo.BUDGET_INFORMATION28 = X_INFORMATION28)
1031 OR ( (Recinfo.BUDGET_INFORMATION28 IS NULL)
1032 AND (X_INFORMATION28 IS NULL)))
1033 AND ( (Recinfo.BUDGET_INFORMATION29 = X_INFORMATION29)
1034 OR ( (Recinfo.BUDGET_INFORMATION29 IS NULL)
1035 AND (X_INFORMATION29 IS NULL)))
1036 AND ( (Recinfo.BUDGET_INFORMATION30 = X_INFORMATION30)
1037 OR ( (Recinfo.BUDGET_INFORMATION30 IS NULL)
1038 AND (X_INFORMATION30 IS NULL)))
1039 ) then
1040 return;
1041 else
1042 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1043 APP_EXCEPTION.RAISE_EXCEPTION;
1044 end if;
1045 --
1046 hr_utility.set_location(' Leaving:'||l_proc, 10);
1047 --
1051 -- |---------------------------< Update_Row >---------------------------------|
1048 END Lock_Row;
1049
1050 -- ----------------------------------------------------------------------------
1052 -- ----------------------------------------------------------------------------
1053 PROCEDURE Update_Row(X_Rowid IN VARCHAR2,
1054 X_Budget_Value_Id IN NUMBER,
1055 X_Business_Group_Id IN NUMBER,
1056 X_Budget_Element_Id IN NUMBER,
1057 X_Time_Period_Id IN NUMBER,
1058 X_Value IN NUMBER,
1059 X_attribute_category IN VARCHAR2,
1060 X_attribute1 IN VARCHAR2,
1061 X_attribute2 IN VARCHAR2,
1062 X_attribute3 IN VARCHAR2,
1063 X_attribute4 IN VARCHAR2,
1064 X_attribute5 IN VARCHAR2,
1065 X_attribute6 IN VARCHAR2,
1066 X_attribute7 IN VARCHAR2,
1067 X_attribute8 IN VARCHAR2,
1068 X_attribute9 IN VARCHAR2,
1069 X_attribute10 IN VARCHAR2,
1070 X_attribute11 IN VARCHAR2,
1071 X_attribute12 IN VARCHAR2,
1072 X_attribute13 IN VARCHAR2,
1073 X_attribute14 IN VARCHAR2,
1074 X_attribute15 IN VARCHAR2,
1075 X_attribute16 IN VARCHAR2,
1076 X_attribute17 IN VARCHAR2,
1077 X_attribute18 IN VARCHAR2,
1078 X_attribute19 IN VARCHAR2,
1079 X_attribute20 IN VARCHAR2,
1080 X_attribute21 IN VARCHAR2,
1081 X_attribute22 IN VARCHAR2,
1082 X_attribute23 IN VARCHAR2,
1083 X_attribute24 IN VARCHAR2,
1084 X_attribute25 IN VARCHAR2,
1085 X_attribute26 IN VARCHAR2,
1086 X_attribute27 IN VARCHAR2,
1087 X_attribute28 IN VARCHAR2,
1088 X_attribute29 IN VARCHAR2,
1089 X_attribute30 IN VARCHAR2,
1090 X_information_category IN VARCHAR2,
1091 X_information1 IN VARCHAR2,
1092 X_information2 IN VARCHAR2,
1093 X_information3 IN VARCHAR2,
1094 X_information4 IN VARCHAR2,
1095 X_information5 IN VARCHAR2,
1096 X_information6 IN VARCHAR2,
1097 X_information7 IN VARCHAR2,
1098 X_information8 IN VARCHAR2,
1099 X_information9 IN VARCHAR2,
1100 X_information10 IN VARCHAR2,
1101 X_information11 IN VARCHAR2,
1102 X_information12 IN VARCHAR2,
1103 X_information13 IN VARCHAR2,
1104 X_information14 IN VARCHAR2,
1105 X_information15 IN VARCHAR2,
1106 X_information16 IN VARCHAR2,
1107 X_information17 IN VARCHAR2,
1108 X_information18 IN VARCHAR2,
1109 X_information19 IN VARCHAR2,
1110 X_information20 IN VARCHAR2,
1111 X_information21 IN vARCHAR2,
1112 X_information22 IN VARCHAR2,
1113 X_information23 IN VARCHAR2,
1114 X_information24 IN VARCHAR2,
1115 X_information25 IN VARCHAR2,
1116 X_information26 IN VARCHAR2,
1117 X_information27 IN VARCHAR2,
1118 X_information28 IN VARCHAR2,
1119 X_information29 IN VARCHAR2,
1120 X_information30 IN VARCHAR2
1121 ) IS
1122
1123 CURSOR C3 IS
1124 SELECT null
1125 from per_budget_values pb
1126 where pb.budget_element_id = x_budget_element_id;
1127
1128 l_proc VARCHAR2(72) := g_package||'Update_Row';
1129 l_result VARCHAR2(255);
1130 l_budget_version_id NUMBER(15);
1131 BEGIN
1132 --
1133 hr_utility.set_location('Entering:'||l_proc, 5);
1134 --
1135 -- validate mandatory business_group
1136 hr_api.validate_bus_grp_id(X_Business_Group_Id);
1137 --
1138 -- validate mandatory rowid
1142 p_argument_value => x_rowid);
1139 hr_api.mandatory_arg_error
1140 (p_api_name => l_proc,
1141 p_argument => 'rowid',
1143
1144 -- validate mandatory budget_value_id
1145 hr_api.mandatory_arg_error
1146 (p_api_name => l_proc,
1147 p_argument => 'budget_value_id',
1148 p_argument_value => x_budget_value_id);
1149
1150 chk_budget_value_id(x_budget_value_id,x_rowid);
1151
1152 -- validate mandatory budget_element_id, and return parent budget_version_id value.
1153 hr_api.mandatory_arg_error
1154 (p_api_name => l_proc,
1155 p_argument => 'budget_element_id',
1156 p_argument_value => x_Budget_Element_Id);
1157
1158 chk_budget_element_id(x_budget_element_id,x_business_group_id,l_budget_version_id);
1159 -- validate mandatory time_period_id
1160 hr_api.mandatory_arg_error
1161 (p_api_name => l_proc,
1162 p_argument => 'time_period_id',
1163 p_argument_value => x_time_period_id);
1164 --
1165 -- Is the parent per_budgets rec 'OTA_BUDGET'
1166 -- If so, does a per_budget_values rec already exist for the budget_element_id
1167 -- If so, check many_budget_values flag to see if another per_budget_values record
1168 -- should be created otherwise raise error.
1169 IF per_budgets_pkg.chk_OTA_budget_type(NULL,l_budget_version_id,NULL) THEN
1170 OPEN C3;
1171 FETCH C3 into l_result;
1172 IF C3%FOUND THEN
1173 null;
1174 END IF;
1175 ELSE
1176 -- only allow 1 record to exist for the business_group, budget_element_id, and time_period
1177 -- parent per_budgets record is 'HR_BUDGET' budget_type_code.
1178
1179 chk_unique(X_Rowid,
1180 X_Business_Group_id,
1181 X_Budget_Element_Id,
1182 X_Time_Period_Id);
1183 --
1184 END IF;
1185
1186
1187 -- validate developer desc flex
1188 Chk_ddf(x_information_category,
1189 x_information1,
1190 x_information2,
1191 x_information3,
1192 x_information4,
1193 x_information5,
1194 x_information6,
1195 x_information7,
1196 x_information8,
1197 x_information9,
1198 x_information10,
1199 x_information11,
1200 x_information12,
1201 x_information13,
1202 x_information14,
1203 x_information15,
1204 x_information16,
1205 x_information17,
1206 x_information18,
1207 x_information19,
1208 x_information20,
1209 x_information21,
1210 x_information22,
1211 x_information23,
1212 x_information24,
1213 x_information25,
1214 x_information26,
1215 x_information27,
1216 x_information28,
1217 x_information29,
1218 x_information30);
1219
1220 -- validate desc flex
1221 Chk_df(x_Attribute_Category,
1222 x_Attribute1,
1223 x_Attribute2,
1224 x_Attribute3,
1225 x_Attribute4,
1226 x_Attribute5,
1227 x_Attribute6,
1228 x_Attribute7,
1229 x_Attribute8,
1230 x_Attribute9,
1231 x_Attribute10,
1232 x_Attribute11,
1233 x_Attribute12,
1234 x_Attribute13,
1235 x_Attribute14,
1236 x_Attribute15,
1237 x_Attribute16,
1238 x_Attribute17,
1239 x_Attribute18,
1240 x_Attribute19,
1241 x_Attribute20,
1242 x_Attribute21,
1243 x_Attribute22,
1244 x_Attribute23,
1245 x_Attribute24,
1246 x_Attribute25,
1247 x_Attribute26,
1248 x_Attribute27,
1249 x_Attribute28,
1250 x_Attribute29,
1251 x_Attribute30);
1252
1253
1254 UPDATE per_budget_values
1255 SET
1256 budget_value_id = X_Budget_Value_Id,
1257 business_group_id = X_Business_Group_Id,
1258 budget_element_id = X_Budget_Element_Id,
1259 time_period_id = X_Time_Period_Id,
1260 value = X_Value,
1261 attribute_category = X_attribute_category,
1262 attribute1 = X_attribute1,
1263 attribute2 = X_attribute2,
1264 attribute3 = X_attribute3,
1265 attribute4 = X_attribute4,
1266 attribute5 = X_attribute5,
1267 attribute6 = X_attribute6,
1268 attribute7 = X_attribute7,
1269 attribute8 = X_attribute8,
1270 attribute9 = X_attribute9,
1271 attribute10 = X_attribute10,
1272 attribute11 = X_attribute11,
1273 attribute12 = X_attribute12,
1274 attribute13 = X_attribute13,
1275 attribute14 = X_attribute14,
1276 attribute15 = X_attribute15,
1280 attribute19 = X_attribute19,
1277 attribute16 = X_attribute16,
1278 attribute17 = X_attribute17,
1279 attribute18 = X_attribute18,
1281 attribute20 = X_attribute20,
1282 attribute21 = X_attribute21,
1283 attribute22 = X_attribute22,
1284 attribute23 = X_attribute23,
1285 attribute24 = X_attribute24,
1286 attribute25 = X_attribute25,
1287 attribute26 = X_attribute26,
1288 attribute27 = X_attribute27,
1289 attribute28 = X_attribute28,
1290 attribute29 = X_attribute29,
1291 attribute30 = X_attribute30,
1292 information_category = X_information_category,
1293 budget_information1 = X_information1,
1294 budget_information2 = X_information2,
1295 budget_information3 = X_information3,
1296 budget_information4 = X_information4,
1297 budget_information5 = X_information5,
1298 budget_information6 = X_information6,
1299 budget_information7 = X_information7,
1300 budget_information8 = X_information8,
1301 budget_information9 = X_information9,
1302 budget_information10 = X_information10,
1303 budget_information11 = X_information11,
1304 budget_information12 = X_information12,
1305 budget_information13 = X_information13,
1306 budget_information14 = X_information14,
1307 budget_information15 = X_information15,
1308 budget_information16 = X_information16,
1309 budget_information17 = X_information17,
1310 budget_information18 = X_information18,
1311 budget_information19 = X_information19,
1312 budget_information20 = X_information20,
1313 budget_information21 = X_information21,
1314 budget_information22 = X_information22,
1315 budget_information23 = X_information23,
1316 budget_information24 = X_information24,
1317 budget_information25 = X_information25,
1318 budget_information26 = X_information26,
1319 budget_information27 = X_information27,
1320 budget_information28 = X_information28,
1321 budget_information29 = X_information29,
1322 budget_information30 = X_information30
1323 WHERE rowid = X_rowid;
1324
1325 if (SQL%NOTFOUND) then
1326 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1327 hr_utility.set_message_token('PROCEDURE','Update_Row');
1328 hr_utility.set_message_token('STEP','1');
1329 hr_utility.raise_error;
1330 end if;
1331 --
1332 hr_utility.set_location(' Leaving:'||l_proc, 10);
1333 --
1334 END Update_Row;
1335
1336 -- ----------------------------------------------------------------------------
1337 -- |---------------------------< Delete_Row >---------------------------------|
1338 -- ----------------------------------------------------------------------------
1339 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1340 l_proc VARCHAR2(72) := g_package||'Delete_Row';
1341 l_count NUMBER(7);
1342 l_budget_version_id PER_BUDGET_ELEMENTS.BUDGET_VERSION_ID%TYPE;
1343 l_ele_rowid VARCHAR2(255) := NULL;
1344
1345 BEGIN
1346 --
1347 hr_utility.set_location('Entering:'||l_proc, 5);
1348 --
1349 -- Determine the budget_type of the parent record
1350 SELECT budget_version_id
1351 INTO l_budget_version_id
1352 FROM per_budget_elements
1353 WHERE budget_element_id = (SELECT budget_element_id
1354 FROM per_budget_values
1355 WHERE rowid = X_Rowid);
1356 --
1357 -- As this is an OTA_BUDGET record, determine how many value
1358 -- records exist for a parent budget element
1359 -- If this is the last, then prepare to delete parent budget element.
1360 IF per_budgets_pkg.chk_OTA_Budget_Type(NULL, l_budget_version_id, NULL) = TRUE THEN
1361 BEGIN
1362 SELECT count(rowid)
1363 INTO l_count
1364 FROM per_budget_values
1365 WHERE budget_element_id = (SELECT budget_element_id
1366 FROM per_budget_values
1367 WHERE rowid = X_Rowid);
1368 --
1369 IF l_count = 1 THEN
1370 SELECT rowid
1371 INTO l_ele_rowid
1372 FROM per_budget_elements
1373 WHERE budget_element_id = (SELECT budget_element_id
1374 FROM per_budget_values
1375 WHERE rowid = X_Rowid);
1376 END IF;
1377 --
1378 EXCEPTION
1379 WHEN NO_DATA_FOUND THEN
1380 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1381 hr_utility.set_message_token('PROCEDURE','Delete_Row');
1382 hr_utility.set_message_token('STEP','1');
1383 hr_utility.raise_error;
1384 END;
1385 END IF;
1386 --
1387 -- delete the value record
1388 DELETE FROM per_budget_values
1389 WHERE rowid = X_Rowid;
1390 --
1391 if (SQL%NOTFOUND) then
1392 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1393 hr_utility.set_message_token('PROCEDURE','Delete_Row');
1394 hr_utility.set_message_token('STEP','1');
1395 hr_utility.raise_error;
1396 end if;
1397 --
1398 -- delete the parent record also
1399 IF l_ele_rowid IS NOT NULL THEN
1400 --
1401 DELETE FROM per_budget_elements
1402 WHERE rowid = l_ele_rowid;
1403 --
1404 IF (SQL%NOTFOUND) then
1405 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1406 hr_utility.set_message_token('PROCEDURE','Delete_Row');
1407 hr_utility.set_message_token('STEP','1');
1408 hr_utility.raise_error;
1409 END IF;
1410 END IF;
1411 --
1412 hr_utility.set_location(' Leaving:'||l_proc, 10);
1413 --
1414 END Delete_Row;
1415
1416 END PER_BUDGET_VALUES_PKG;