[Home] [Help]
PACKAGE BODY: APPS.PER_BUDGETS_PKG
Source
1 PACKAGE BODY PER_BUDGETS_PKG as
2 /* $Header: pebgt01t.pkb 115.6 2004/02/16 10:20:09 nsanghal ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 g_package varchar2(33) := ' per_budgets_pkg.'; -- Global package name
8
9
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< Chk_Unique >---------------------------------|
12 -- ----------------------------------------------------------------------------
13 PROCEDURE Chk_Unique(X_Rowid VARCHAR2,
14 X_Business_Group_Id NUMBER,
15 X_Name VARCHAR2,
16 X_Budget_Type_Code VARCHAR2) is
17 l_result VARCHAR2(255);
18 l_proc VARCHAR2(72) := g_package||'Chk_Unique';
19
20 BEGIN
21 --
22 hr_utility.set_location('Entering:'||l_proc, 5);
23 --
24 SELECT NULL
25 INTO l_result
26 FROM per_budgets b
27 WHERE UPPER(X_Name) = UPPER(b.Name)
28 AND UPPER(X_Budget_Type_Code) = UPPER(b.Budget_Type_Code)
29 AND X_Business_Group_Id = b.Business_Group_Id + 0
30 AND (b.Rowid <> X_Rowid or X_Rowid is null);
31
32 IF (SQL%FOUND) then
33 hr_utility.set_message(801,'PER_7852_DEF_BUDGET_EXISTS');
34 hr_utility.raise_error;
35 END IF;
36 --
37 hr_utility.set_location(' Leaving:'||l_proc, 10);
38 --
39 EXCEPTION
40 when NO_DATA_FOUND then
41 null;
42
43 END Chk_Unique;
44
45 -- ----------------------------------------------------------------------------
46 -- |-------------------------< Chk_OTA_Budget_Type >--------------------------|
47 -- ----------------------------------------------------------------------------
48 -- Determine budget_type_code of per_budgets rec.
49 -- Accepts Budget_id or Budget_Version_Id or Rowid
50 --
51 FUNCTION Chk_OTA_Budget_Type(X_Budget_Id IN NUMBER,
52 X_Budget_Version_Id IN NUMBER,
53 X_Rowid in VARCHAR2) RETURN BOOLEAN IS
54
55 --
56 CURSOR c_bdv IS
57 SELECT NULL
58 FROM per_budget_versions bdv, per_budgets bd
59 WHERE bdv.budget_id = bd.budget_id
60 AND bdv.budget_version_id = X_Budget_Version_Id
61 AND bd.budget_type_code = 'OTA_BUDGET';
62 --
63 CURSOR c_bdg1 IS
64 SELECT NULL
65 FROM per_budgets pb
66 WHERE pb.budget_id = X_Budget_Id
67 AND budget_type_code = 'OTA_BUDGET';
68 --
69 CURSOR c_bdg2 IS
70 SELECT NULL
71 FROM per_budgets pb
72 WHERE pb.rowid = X_Rowid
73 AND budget_type_code = 'OTA_BUDGET';
74 --
75 l_result VARCHAR2(255);
76 l_proc VARCHAR2(72) := g_package||'Chk_OTA_Budget_Type';
77 --
78 BEGIN
79 --
80 hr_utility.set_location('Entering:'||l_proc, 5);
81 --
82 IF X_Budget_Id IS NOT NULL AND X_Budget_Version_Id IS NULL AND X_Rowid IS NULL THEN
83 OPEN c_bdg1;
84 FETCH c_bdg1 into l_result;
85 IF c_bdg1%NOTFOUND THEN
86 CLOSE c_bdg1;
87 RETURN FALSE;
88 ELSE
89 CLOSE c_bdg1;
90 RETURN TRUE;
91 END IF;
92 ELSIF X_rowid IS NOT NULL AND X_Budget_Id IS NULL AND X_budget_Version_Id IS NULL THEN
93 OPEN c_bdg2;
94 FETCH c_bdg2 into l_result;
95 IF c_bdg2%NOTFOUND THEN
96 CLOSE c_bdg2;
97 RETURN FALSE;
98 ELSE
99 CLOSE c_bdg2;
100 RETURN TRUE;
101 END IF;
102 ELSIF X_Budget_Version_Id IS NOT NULL AND X_Budget_Id IS NULL AND X_Rowid IS NULL THEN
103 OPEN c_bdv;
104 FETCH c_bdv into l_result;
105 IF c_bdv%NOTFOUND THEN
106 CLOSE c_bdv;
107 RETURN FALSE;
108 ELSE
109 CLOSE c_bdv;
110 RETURN TRUE;
111 END IF;
112 ELSIF X_Budget_Id IS NULL AND X_Budget_Version_Id IS NULL AND X_Rowid IS NULL THEN
113 hr_utility.set_message(800,'PER_52867_INV_ARGS');
114 hr_utility.raise_error;
115 ELSE
116 hr_utility.set_message(800,'PER_52868_TOO_MANY_ARGS');
117 hr_utility.raise_error;
118 END IF;
119 --
120 hr_utility.set_location(' Leaving:'||l_proc, 10);
121 --
122 END Chk_OTA_Budget_Type;
123
124 -- ----------------------------------------------------------------------------
125 -- |---------------------< Chk_Measurement_Type_Exists >----------------------|
126 -- ----------------------------------------------------------------------------
127 -- Check there is a measurement_type for the UNIT within the business_group
128 -- (used for OTA_BUDGET budget_type_code records)
129 --
130 PROCEDURE Chk_Measurement_Type_Exists(X_Business_Group_Id NUMBER,
131 X_Unit VARCHAR2) IS
132 --
133 l_status varchar2(30);
134 l_industry varchar2(30);
135 l_owner varchar2(30);
136 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('OTA', l_status,
137 l_industry, l_owner);
138
139 -- dynamic sql statments to check if the training plan measurement types exit
140 --
141 l_stmt_chk_tpc_exist varchar2(32000) := 'select ''Y'' from all_tables
142 where table_name = ''OTA_TP_MEASUREMENT_TYPES''
143 and owner = '''||l_owner||'''';
144 --
145 -- dynamic sql statment to check if a row exists in training plan members
146 --
147 l_stmt_get_tpc_rows varchar2(32000) := 'select ''Y'' from OTA_TP_MEASUREMENT_TYPES --
148 where upper(tp_measurement_code) = upper('''||X_unit ||''') --
149 and business_group_id = '||X_Business_Group_id;
150 --
151 l_dyn_curs integer;
152 l_dyn_rows integer;
153 l_exists VARCHAR2(255);
154
155
156 l_proc VARCHAR2(72) := g_package||'Chk_Measurement_Type_Exists';
157
158 --
159 BEGIN
160 --
161 hr_utility.set_location('Entering:'||l_proc, 5);
162
163 l_dyn_curs := dbms_sql.open_cursor;
164 --
165 -- Determine if the OTA_TP_MEASUREEMNT_TYPES table exists
166 --
167 dbms_sql.parse(l_dyn_curs,l_stmt_chk_tpc_exist,dbms_sql.v7);
168 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
169 --
170 if dbms_sql.last_row_count > 0 then
171 -- Check that the training plan measurement record exists
172 dbms_sql.parse(l_dyn_curs,l_stmt_get_tpc_rows,dbms_sql.v7);
173 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
174 --
175 if dbms_sql.last_row_count < 1 then
176 dbms_sql.close_cursor(l_dyn_curs);
177 hr_utility.set_message(800,'PER_52869_MEASURE_NOT_EXISTS');
178 hr_utility.raise_error;
179 end if;
180 end if;
181 if dbms_sql.is_open(l_dyn_curs) then
182 dbms_sql.close_cursor(l_dyn_curs);
183 end if;
184
185 hr_utility.set_location(' Leaving:'|| l_proc, 10);
186 --
187 END Chk_Measurement_Type_Exists;
188 -- ----------------------------------------------------------------------------
189 -- |------------------------< Chk_Budget_Type_Code >--------------------------|
190 -- ----------------------------------------------------------------------------
191 PROCEDURE Chk_Budget_Type_Code(X_Budget_Type_Code IN VARCHAR2) IS
192 --
193 l_proc VARCHAR2(72) := g_package||'Chk_Budget_Type_Code';
194 --
195 BEGIN
196 --
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 IF (UPPER(X_Budget_Type_Code) <> 'HR_BUDGET' AND UPPER(X_Budget_Type_Code) <> 'OTA_BUDGET') OR X_Budget_Type_Code IS NULL THEN
200 hr_utility.set_message(800,'PER_52870_INV_BUD_TYPE');
201 hr_utility.raise_error;
202 END IF;
203 --
204 hr_utility.set_location(' Leaving:'||l_proc, 10);
205 --
206 END Chk_Budget_Type_Code;
207
208
209 -- ----------------------------------------------------------------------------
210 -- |------------------------< Chk_Period_Set_Name >---------------------------|
211 -- ----------------------------------------------------------------------------
212 PROCEDURE Chk_Period_Set_Name(X_Period_Set_Name IN VARCHAR2) IS
213 --
214 CURSOR c_ptp IS
215 SELECT null
216 FROM pay_calendars pc
217 WHERE pc.period_set_name = X_Period_Set_Name;
218 --
219 l_exists VARCHAR2(255);
220 l_proc VARCHAR2(72) := g_package||'Chk_Period_Set_Name';
221 --
222 BEGIN
223 --
224 hr_utility.set_location('Entering:'||l_proc, 5);
225 --
226 OPEN c_ptp;
227 FETCH c_ptp INTO l_exists;
228 IF c_ptp%NOTFOUND THEN
229 hr_utility.set_message(800,'PER_52871_CAL_NOT_EXIST');
230 hr_utility.raise_error;
231 END IF;
232 CLOSE c_ptp;
233 --
234 hr_utility.set_location(' Leaving:'||l_proc, 10);
235 --
236 END Chk_Period_Set_Name;
237
238
239 -- ----------------------------------------------------------------------------
240 -- |----------------------------< Chk_df >------------------------------------|
241 -- ----------------------------------------------------------------------------
242 PROCEDURE Chk_df(x_attribute_category varchar2
243 ,x_attribute1 varchar2
244 ,x_attribute2 varchar2
245 ,x_attribute3 varchar2
246 ,x_attribute4 varchar2
247 ,x_attribute5 varchar2
248 ,x_attribute6 varchar2
249 ,x_attribute7 varchar2
250 ,x_attribute8 varchar2
251 ,x_attribute9 varchar2
252 ,x_attribute10 varchar2
253 ,x_attribute11 varchar2
254 ,x_attribute12 varchar2
255 ,x_attribute13 varchar2
256 ,x_attribute14 varchar2
257 ,x_attribute15 varchar2
258 ,x_attribute16 varchar2
259 ,x_attribute17 varchar2
260 ,x_attribute18 varchar2
261 ,x_attribute19 varchar2
262 ,x_attribute20 varchar2) IS
263
264 l_proc VARCHAR2(72) := g_package||'Chk_df';
265
266 BEGIN
267 --
268 hr_utility.set_location('Entering:'||l_proc, 5);
269 --
270 hr_dflex_utility.ins_or_upd_descflex_attribs
271 (p_appl_short_name => 'PER'
272 ,p_descflex_name => 'PER_BUDGETS'
273 ,p_attribute_category => x_attribute_category
274 ,p_attribute1_name => 'ATTRIBUTE1'
275 ,p_attribute1_value => x_attribute1
276 ,p_attribute2_name => 'ATTRIBUTE2'
277 ,p_attribute2_value => x_attribute2
278 ,p_attribute3_name => 'ATTRIBUTE3'
279 ,p_attribute3_value => x_attribute3
280 ,p_attribute4_name => 'ATTRIBUTE4'
281 ,p_attribute4_value => x_attribute4
282 ,p_attribute5_name => 'ATTRIBUTE5'
283 ,p_attribute5_value => x_attribute5
284 ,p_attribute6_name => 'ATTRIBUTE6'
285 ,p_attribute6_value => x_attribute6
286 ,p_attribute7_name => 'ATTRIBUTE7'
287 ,p_attribute7_value => x_attribute7
288 ,p_attribute8_name => 'ATTRIBUTE8'
289 ,p_attribute8_value => x_attribute8
290 ,p_attribute9_name => 'ATTRIBUTE9'
291 ,p_attribute9_value => x_attribute9
292 ,p_attribute10_name => 'ATTRIBUTE10'
293 ,p_attribute10_value => x_attribute10
294 ,p_attribute11_name => 'ATTRIBUTE11'
295 ,p_attribute11_value => x_attribute11
296 ,p_attribute12_name => 'ATTRIBUTE12'
297 ,p_attribute12_value => x_attribute12
298 ,p_attribute13_name => 'ATTRIBUTE13'
299 ,p_attribute13_value => x_attribute13
300 ,p_attribute14_name => 'ATTRIBUTE14'
301 ,p_attribute14_value => x_attribute14
302 ,p_attribute15_name => 'ATTRIBUTE15'
303 ,p_attribute15_value => x_attribute15
304 ,p_attribute16_name => 'ATTRIBUTE16'
305 ,p_attribute16_value => x_attribute16
306 ,p_attribute17_name => 'ATTRIBUTE17'
307 ,p_attribute17_value => x_attribute17
308 ,p_attribute18_name => 'ATTRIBUTE18'
309 ,p_attribute18_value => x_attribute18
310 ,p_attribute19_name => 'ATTRIBUTE19'
311 ,p_attribute19_value => x_attribute19
312 ,p_attribute20_name => 'ATTRIBUTE20'
313 ,p_attribute20_value => x_attribute20);
314 --
315 hr_utility.set_location(' Leaving:'||l_proc, 10);
316 --
317 END Chk_df;
318
319
320 -- ----------------------------------------------------------------------------
321 -- |----------------------------< Insert_Row >--------------------------------|
322 -- ----------------------------------------------------------------------------
323 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
324 X_Budget_Id IN OUT NOCOPY NUMBER,
325 X_Business_Group_Id NUMBER,
326 X_Period_Set_Name VARCHAR2,
327 X_Name VARCHAR2,
328 X_Comments VARCHAR2,
329 X_Unit VARCHAR2,
330 X_Attribute_Category VARCHAR2,
331 X_Attribute1 VARCHAR2,
332 X_Attribute2 VARCHAR2,
336 X_Attribute6 VARCHAR2,
333 X_Attribute3 VARCHAR2,
334 X_Attribute4 VARCHAR2,
335 X_Attribute5 VARCHAR2,
337 X_Attribute7 VARCHAR2,
338 X_Attribute8 VARCHAR2,
339 X_Attribute9 VARCHAR2,
340 X_Attribute10 VARCHAR2,
341 X_Attribute11 VARCHAR2,
342 X_Attribute12 VARCHAR2,
343 X_Attribute13 VARCHAR2,
344 X_Attribute14 VARCHAR2,
345 X_Attribute15 VARCHAR2,
346 X_Attribute16 VARCHAR2,
347 X_Attribute17 VARCHAR2,
348 X_Attribute18 VARCHAR2,
349 X_Attribute19 VARCHAR2,
350 X_Attribute20 VARCHAR2,
351 X_Budget_Type_Code VARCHAR2
352 ) IS
353
354 CURSOR C1 IS SELECT rowid FROM PER_BUDGETS
355 WHERE budget_id = X_budget_id;
356
357 CURSOR C2 IS SELECT per_budgets_s.nextval FROM dual;
358
359 l_proc VARCHAR2(72) := g_package||'Insert_Row';
360 l_name PER_BUDGETS.NAME%TYPE;
361
362 BEGIN
363 --
364 --hr_utility.trace_on;
365 hr_utility.set_location('Entering:'||l_proc, 5);
366 --
367 -- validate mandatory business_group
368 hr_api.validate_bus_grp_id(X_Business_Group_Id);
369 --
370 -- validate mandatory period_set_name
371 hr_api.mandatory_arg_error
372 (p_api_name => l_proc,
373 p_argument => 'period_set_name',
374 p_argument_value => X_Period_Set_Name);
375
376 chk_period_set_name(X_Period_Set_Name);
377 --
378 -- validate mandatory name
379 hr_api.mandatory_arg_error
380 (p_api_name => l_proc,
381 p_argument => 'name',
382 p_argument_value => X_Name);
383 --
384 -- check budget type code is valid
385 chk_budget_type_code(X_budget_Type_Code);
386
387 -- get X_budget_id from sequence now, (used in name for OTA_BUDGET recs)
388 OPEN C2;
389 FETCH C2 INTO X_budget_id;
390 CLOSE C2;
391 --
392 -- check measurement_type exists if OTA_BUDGET record
393 -- nb. UNIT should not be null if OTA_BUDGET type
394 IF UPPER(X_Budget_Type_Code) = 'OTA_BUDGET' THEN
395
396 -- Set name field to x_name ||'-'|| budget_id for OTA.
397 l_name := X_Name ||'-'||to_char(X_budget_id);
398
399 -- validate mandatory unit
400 hr_api.mandatory_arg_error
401 (p_api_name => l_proc,
402 p_argument => 'unit',
403 p_argument_value => X_Unit);
404
405 Chk_Measurement_Type_Exists(X_Business_Group_Id,X_Unit);
406 ELSE
407 l_name := X_Name;
408 END IF;
409 --
410 -- check per_budgets record does not exists for
411 -- this name+bg+btc combination.
412 Chk_Unique(NULL,
413 X_Business_Group_Id,
414 X_Name,
415 X_Budget_Type_Code);
416
417 -- validate desc flex
418 Chk_df(X_Attribute_Category,
419 X_Attribute1,
420 X_Attribute2,
421 X_Attribute3,
422 X_Attribute4,
423 X_Attribute5,
424 X_Attribute6,
425 X_Attribute7,
426 X_Attribute8,
427 X_Attribute9,
428 X_Attribute10,
429 X_Attribute11,
430 X_Attribute12,
431 X_Attribute13,
432 X_Attribute14,
433 X_Attribute15,
434 X_Attribute16,
435 X_Attribute17,
436 X_Attribute18,
437 X_Attribute19,
438 X_Attribute20);
439
440
441
442
443 INSERT INTO PER_BUDGETS(
444 budget_id,
445 business_group_id,
446 period_set_name,
447 name,
448 comments,
449 unit,
450 attribute_category,
451 attribute1,
452 attribute2,
453 attribute3,
454 attribute4,
455 attribute5,
456 attribute6,
457 attribute7,
458 attribute8,
459 attribute9,
460 attribute10,
461 attribute11,
462 attribute12,
463 attribute13,
464 attribute14,
465 attribute15,
466 attribute16,
467 attribute17,
468 attribute18,
469 attribute19,
470 attribute20,
471 budget_type_code
472 ) VALUES (
473 X_budget_id,
474 X_Business_Group_Id,
475 X_Period_Set_Name,
476 l_name,
477 X_Comments,
478 X_Unit,
479 X_Attribute_Category,
483 X_Attribute4,
480 X_Attribute1,
481 X_Attribute2,
482 X_Attribute3,
484 X_Attribute5,
485 X_Attribute6,
486 X_Attribute7,
487 X_Attribute8,
488 X_Attribute9,
489 X_Attribute10,
490 X_Attribute11,
491 X_Attribute12,
492 X_Attribute13,
493 X_Attribute14,
494 X_Attribute15,
495 X_Attribute16,
496 X_Attribute17,
497 X_Attribute18,
498 X_Attribute19,
499 X_Attribute20,
500 X_Budget_Type_Code
501 );
502
503 OPEN C1;
504 FETCH C1 INTO X_Rowid;
505 if (C1%NOTFOUND) then
506 CLOSE C1;
507 /* hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
508 hr_utility.set_message_token('PROCEDURE','Insert_Row');
509 hr_utility.set_message_token('STEP','1');
510 hr_utility.raise_error;*/
511 RAISE NO_DATA_FOUND;
512 end if;
513 CLOSE C1;
514 --
515 hr_utility.set_location(' Leaving:'||l_proc, 10);
516 --
517 END Insert_Row;
518
519
520 -- ----------------------------------------------------------------------------
521 -- |----------------------------< Lock_Row >----------------------------------|
522 -- ----------------------------------------------------------------------------
523 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
524 X_Budget_Id NUMBER,
525 X_Business_Group_Id NUMBER,
526 X_Period_Set_Name VARCHAR2,
527 X_Name VARCHAR2,
528 X_Comments VARCHAR2,
529 X_Unit VARCHAR2,
530 X_Attribute_Category VARCHAR2,
531 X_Attribute1 VARCHAR2,
532 X_Attribute2 VARCHAR2,
533 X_Attribute3 VARCHAR2,
534 X_Attribute4 VARCHAR2,
535 X_Attribute5 VARCHAR2,
536 X_Attribute6 VARCHAR2,
537 X_Attribute7 VARCHAR2,
538 X_Attribute8 VARCHAR2,
539 X_Attribute9 VARCHAR2,
540 X_Attribute10 VARCHAR2,
541 X_Attribute11 VARCHAR2,
542 X_Attribute12 VARCHAR2,
543 X_Attribute13 VARCHAR2,
544 X_Attribute14 VARCHAR2,
545 X_Attribute15 VARCHAR2,
546 X_Attribute16 VARCHAR2,
547 X_Attribute17 VARCHAR2,
548 X_Attribute18 VARCHAR2,
549 X_Attribute19 VARCHAR2,
550 X_Attribute20 VARCHAR2,
551 X_Budget_Type_Code VARCHAR2
552 ) IS
553 CURSOR C IS
554 SELECT *
555 FROM PER_BUDGETS
556 WHERE rowid = X_Rowid
557 FOR UPDATE of Budget_Id NOWAIT;
558 Recinfo C%ROWTYPE;
559 l_proc VARCHAR2(72) := g_package||'Lock_Row';
560 BEGIN
561 --
562 hr_utility.set_location('Entering:'||l_proc, 5);
563 --
564 OPEN C;
565 FETCH C INTO Recinfo;
566 if (C%NOTFOUND) then
567 CLOSE C;
568 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
569 hr_utility.set_message_token('PROCEDURE','Lock_Row');
570 hr_utility.set_message_token('STEP','1');
571 hr_utility.raise_error;
572 end if;
573 CLOSE C;
574 --
575 Recinfo.budget_id := rtrim(Recinfo.budget_id);
576 Recinfo.business_group_id := rtrim(Recinfo.business_group_id);
577 Recinfo.period_set_name := rtrim(Recinfo.period_set_name);
578 Recinfo.name := rtrim(Recinfo.name);
579 Recinfo.comments := rtrim(Recinfo.comments);
580 Recinfo.unit := rtrim(Recinfo.unit);
581 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
582 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
583 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
584 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
585 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
586 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
587 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
588 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
589 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
590 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
591 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
592 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
593 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
594 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
595 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
596 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
597 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
598 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
599 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
600 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
604 ( (Recinfo.budget_id = X_Budget_Id)
601 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
602 Recinfo.budget_type_code := rtrim(Recinfo.budget_type_code);
603 if (
605 OR ( (Recinfo.budget_id IS NULL)
606 AND (X_Budget_Id IS NULL)))
607 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
608 OR ( (Recinfo.business_group_id IS NULL)
609 AND (X_Business_Group_Id IS NULL)))
610 AND ( (Recinfo.period_set_name = X_Period_Set_Name)
611 OR ( (Recinfo.period_set_name IS NULL)
612 AND (X_Period_Set_Name IS NULL)))
613 AND ( (Recinfo.name = X_Name)
614 OR ( (Recinfo.name IS NULL)
615 AND (X_Name IS NULL)))
616 AND ( (Recinfo.comments = X_Comments)
617 OR ( (Recinfo.comments IS NULL)
618 AND (X_Comments IS NULL)))
619 AND ( (Recinfo.unit = X_Unit)
620 OR ( (Recinfo.unit IS NULL)
621 AND (X_Unit IS NULL)))
622 AND ( (Recinfo.attribute_category = X_Attribute_Category)
623 OR ( (Recinfo.attribute_category IS NULL)
624 AND (X_Attribute_Category IS NULL)))
625 AND ( (Recinfo.attribute1 = X_Attribute1)
626 OR ( (Recinfo.attribute1 IS NULL)
627 AND (X_Attribute1 IS NULL)))
628 AND ( (Recinfo.attribute2 = X_Attribute2)
629 OR ( (Recinfo.attribute2 IS NULL)
630 AND (X_Attribute2 IS NULL)))
631 AND ( (Recinfo.attribute3 = X_Attribute3)
632 OR ( (Recinfo.attribute3 IS NULL)
633 AND (X_Attribute3 IS NULL)))
634 AND ( (Recinfo.attribute4 = X_Attribute4)
635 OR ( (Recinfo.attribute4 IS NULL)
636 AND (X_Attribute4 IS NULL)))
637 AND ( (Recinfo.attribute5 = X_Attribute5)
638 OR ( (Recinfo.attribute5 IS NULL)
639 AND (X_Attribute5 IS NULL)))
640 AND ( (Recinfo.attribute6 = X_Attribute6)
641 OR ( (Recinfo.attribute6 IS NULL)
642 AND (X_Attribute6 IS NULL)))
643 AND ( (Recinfo.attribute7 = X_Attribute7)
644 OR ( (Recinfo.attribute7 IS NULL)
645 AND (X_Attribute7 IS NULL)))
646 AND ( (Recinfo.attribute8 = X_Attribute8)
647 OR ( (Recinfo.attribute8 IS NULL)
648 AND (X_Attribute8 IS NULL)))
649 AND ( (Recinfo.attribute9 = X_Attribute9)
650 OR ( (Recinfo.attribute9 IS NULL)
651 AND (X_Attribute9 IS NULL)))
652 AND ( (Recinfo.attribute10 = X_Attribute10)
653 OR ( (Recinfo.attribute10 IS NULL)
654 AND (X_Attribute10 IS NULL)))
655 AND ( (Recinfo.attribute11 = X_Attribute11)
656 OR ( (Recinfo.attribute11 IS NULL)
657 AND (X_Attribute11 IS NULL)))
658 AND ( (Recinfo.attribute12 = X_Attribute12)
659 OR ( (Recinfo.attribute12 IS NULL)
660 AND (X_Attribute12 IS NULL)))
661 AND ( (Recinfo.attribute13 = X_Attribute13)
662 OR ( (Recinfo.attribute13 IS NULL)
663 AND (X_Attribute13 IS NULL)))
664 AND ( (Recinfo.attribute14 = X_Attribute14)
665 OR ( (Recinfo.attribute14 IS NULL)
666 AND (X_Attribute14 IS NULL)))
667 AND ( (Recinfo.attribute15 = X_Attribute15)
668 OR ( (Recinfo.attribute15 IS NULL)
669 AND (X_Attribute15 IS NULL)))
670 AND ( (Recinfo.attribute16 = X_Attribute16)
671 OR ( (Recinfo.attribute16 IS NULL)
672 AND (X_Attribute16 IS NULL)))
673 AND ( (Recinfo.attribute17 = X_Attribute17)
674 OR ( (Recinfo.attribute17 IS NULL)
675 AND (X_Attribute17 IS NULL)))
676 AND ( (Recinfo.attribute18 = X_Attribute18)
677 OR ( (Recinfo.attribute18 IS NULL)
678 AND (X_Attribute18 IS NULL)))
679 AND ( (Recinfo.attribute19 = X_Attribute19)
680 OR ( (Recinfo.attribute19 IS NULL)
681 AND (X_Attribute19 IS NULL)))
682 AND ( (Recinfo.attribute20 = X_Attribute20)
683 OR ( (Recinfo.attribute20 IS NULL)
684 AND (X_Attribute20 IS NULL)))
685 AND ( (Recinfo.budget_type_code = X_budget_type_code)
686 OR ( (Recinfo.budget_type_code IS NULL)
687 AND (X_budget_type_code IS NULL)))
688 ) then
689 return;
690 else
691 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
692 APP_EXCEPTION.RAISE_EXCEPTION;
693 end if;
694 --
695 hr_utility.set_location(' Leaving:'||l_proc, 10);
696 --
697 END Lock_Row;
698
699 -- ----------------------------------------------------------------------------
700 -- |---------------------------< Update_Row >---------------------------------|
701 -- ----------------------------------------------------------------------------
702 PROCEDURE Update_Row(X_Rowid VARCHAR2,
703 X_Budget_Id NUMBER,
704 X_Business_Group_Id NUMBER,
705 X_Period_Set_Name VARCHAR2,
706 X_Name VARCHAR2,
707 X_Comments VARCHAR2,
708 X_Unit VARCHAR2,
712 X_Attribute3 VARCHAR2,
709 X_Attribute_Category VARCHAR2,
710 X_Attribute1 VARCHAR2,
711 X_Attribute2 VARCHAR2,
713 X_Attribute4 VARCHAR2,
714 X_Attribute5 VARCHAR2,
715 X_Attribute6 VARCHAR2,
716 X_Attribute7 VARCHAR2,
717 X_Attribute8 VARCHAR2,
718 X_Attribute9 VARCHAR2,
719 X_Attribute10 VARCHAR2,
720 X_Attribute11 VARCHAR2,
721 X_Attribute12 VARCHAR2,
722 X_Attribute13 VARCHAR2,
723 X_Attribute14 VARCHAR2,
724 X_Attribute15 VARCHAR2,
725 X_Attribute16 VARCHAR2,
726 X_Attribute17 VARCHAR2,
727 X_Attribute18 VARCHAR2,
728 X_Attribute19 VARCHAR2,
729 X_Attribute20 VARCHAR2,
730 X_Budget_Type_Code VARCHAR2
731 ) IS
732 l_proc VARCHAR2(72) := g_package||'Update_Row';
733 BEGIN
734 --
735 hr_utility.set_location('Entering:'||l_proc, 5);
736 --
737 -- validate mandatory business_group
738 hr_api.validate_bus_grp_id(X_Business_Group_Id);
739
740 -- validate mandatory budget_id
741 hr_api.mandatory_arg_error
742 (p_api_name => l_proc,
743 p_argument => 'budget_id',
744 p_argument_value => X_Budget_Id);
745
746 -- validate mandatory rowid
747 hr_api.mandatory_arg_error
748 (p_api_name => l_proc,
749 p_argument => 'rowid',
750 p_argument_value => X_rowid);
751
752 -- validate mandatory period_set_name
753 hr_api.mandatory_arg_error
754 (p_api_name => l_proc,
755 p_argument => 'period_set_name',
756 p_argument_value => X_Period_Set_Name);
757
758 chk_period_set_name(X_Period_Set_Name);
759
760 -- validate mandatory name
761
762 hr_api.mandatory_arg_error
763 (p_api_name => l_proc,
764 p_argument => 'name',
765 p_argument_value => X_Name);
766
767 --
768 -- check budget type code is valid
769 chk_budget_type_code(X_budget_Type_Code);
770 --
771
772 -- check measurement_type exists if OTA_BUDGET record
773 -- nb. UNIT should not be null if OTA_BUDGET type
774 IF UPPER(X_Budget_Type_Code) = 'OTA_BUDGET' THEN
775
776 hr_api.mandatory_arg_error
777 (p_api_name => l_proc,
778 p_argument => 'unit',
779 p_argument_value => X_Unit);
780
781 Chk_Measurement_Type_Exists(X_Business_Group_Id,X_Unit);
782
783 END IF;
784
785 -- check per_budgets record does not exists for
786 -- this name+bg+btc combination.
787 Chk_Unique(x_Rowid,
788 X_Business_Group_Id,
789 X_Name,
790 X_Budget_Type_Code);
791
792 -- validate desc flex
793 Chk_df(X_Attribute_Category,
794 X_Attribute1,
795 X_Attribute2,
796 X_Attribute3,
797 X_Attribute4,
798 X_Attribute5,
799 X_Attribute6,
800 X_Attribute7,
801 X_Attribute8,
802 X_Attribute9,
803 X_Attribute10,
804 X_Attribute11,
805 X_Attribute12,
806 X_Attribute13,
807 X_Attribute14,
808 X_Attribute15,
809 X_Attribute16,
810 X_Attribute17,
811 X_Attribute18,
812 X_Attribute19,
813 X_Attribute20);
814
815 -- now perform update
816 UPDATE PER_BUDGETS
817 SET
818 budget_id = X_Budget_Id,
819 business_group_id = X_Business_Group_Id,
820 period_set_name = X_Period_Set_Name,
821 name = X_Name,
822 comments = X_Comments,
823 unit = X_Unit,
824 attribute_category = X_Attribute_Category,
825 attribute1 = X_Attribute1,
826 attribute2 = X_Attribute2,
827 attribute3 = X_Attribute3,
828 attribute4 = X_Attribute4,
829 attribute5 = X_Attribute5,
830 attribute6 = X_Attribute6,
831 attribute7 = X_Attribute7,
832 attribute8 = X_Attribute8,
833 attribute9 = X_Attribute9,
834 attribute10 = X_Attribute10,
835 attribute11 = X_Attribute11,
836 attribute12 = X_Attribute12,
837 attribute13 = X_Attribute13,
838 attribute14 = X_Attribute14,
839 attribute15 = X_Attribute15,
840 attribute16 = X_Attribute16,
841 attribute17 = X_Attribute17,
842 attribute18 = X_Attribute18,
843 attribute19 = X_Attribute19,
844 attribute20 = X_Attribute20,
845 budget_type_code = X_Budget_Type_Code
846 WHERE rowid = X_rowid;
847
848 if (SQL%NOTFOUND) then
849 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
850 hr_utility.set_message_token('PROCEDURE','Update_Row');
851 hr_utility.set_message_token('STEP','1');
852 hr_utility.raise_error;
853 end if;
854 --
855 hr_utility.set_location(' Leaving:'||l_proc, 10);
856 --
857 END Update_Row;
858
859 -- ----------------------------------------------------------------------------
860 -- |---------------------------< Delete_Row >---------------------------------|
861 -- ----------------------------------------------------------------------------
862 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
863 --
864 CURSOR C_Version is SELECT Rowid
865 FROM per_budget_versions pbv
866 where pbv.budget_id = (SELECT pb.budget_id
867 FROM per_budgets pb
868 WHERE pb.Rowid = X_Rowid);
869 --
870 l_ver_rowid VARCHAR2(30);
871 l_proc VARCHAR2(72) := g_package||'Delete_Row';
872 --
873 BEGIN
874 --
875 hr_utility.set_location('Entering:'||l_proc, 5);
876 --
877
878 OPEN C_Version;
879 -- Cascade delete the appropriate child budget_versions recs if
880 -- budget_type_code is 'HR_BUDGET';
881 IF chk_OTA_Budget_Type(NULL, NULL, X_Rowid) = FALSE THEN
882 LOOP
883 FETCH C_Version into l_ver_rowid;
884 EXIT WHEN (C_Version%NOTFOUND);
885 PER_BUDGET_VERSION_RULES_PKG.Delete_Row(X_Rowid => l_ver_rowid);
886 END LOOP;
887 ELSE
888 FETCH C_Version into l_ver_rowid;
889 IF C_Version%FOUND THEN
890 CLOSE C_Version;
891 --raise error as child record has been found
892 hr_utility.set_message(800,'PER_52872_BUD_DELETE_FAIL');
893 hr_utility.raise_error;
894 END IF;
895 END IF;
896 CLOSE C_Version;
897
898 --now delete the parent budget
899 DELETE FROM PER_BUDGETS
900 WHERE rowid = X_Rowid;
901
902 if (SQL%NOTFOUND) then
903 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
904 hr_utility.set_message_token('PROCEDURE','Delete_Row');
905 hr_utility.set_message_token('STEP','1');
906 hr_utility.raise_error;
907 end if;
908 --
909 hr_utility.set_location(' Leaving:'||l_proc, 10);
910 --
911 END Delete_Row;
912
913 END PER_BUDGETS_PKG;