[Home] [Help]
PACKAGE BODY: APPS.PER_BUDGET_ELEMENTS_PKG
Source
1 PACKAGE BODY PER_BUDGET_ELEMENTS_PKG as
2 /* $Header: pebge01t.pkb 115.10 2004/02/16 10:19:58 nsanghal ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 g_package varchar2(33) := ' per_budget_elements_pkg.'; -- Global package name
8
9 -- ----------------------------------------------------------------------------
10 -- |---------------------------< Chk_Unique >---------------------------------|
11 -- ----------------------------------------------------------------------------
12 PROCEDURE Chk_Unique(X_Rowid VARCHAR2,
13 X_Budget_Version_Id NUMBER,
14 X_Organization_Id NUMBER,
15 X_Job_Id NUMBER,
16 X_Position_Id NUMBER,
17 X_Grade_Id NUMBER,
18 X_Training_Plan_Id NUMBER,
19 X_Training_Plan_Member_Id NUMBER,
20 X_Event_Id NUMBER) is
21
22 l_result VARCHAR2(255);
23 l_proc VARCHAR2(72) := g_package||'Chk_Unique';
24
25 Begin
26 --
27 hr_utility.set_location('Entering:'||l_proc, 5);
28 --
29 SELECT NULL
30 INTO l_result
31 FROM PER_BUDGET_ELEMENTS E
32 WHERE E.BUDGET_VERSION_ID = X_Budget_Version_Id
33 AND (E.ROWID <> X_Rowid OR X_Rowid IS NULL)
34 AND NVL(E.ORGANIZATION_ID,-1) = NVL(X_Organization_Id,-1)
35 AND NVL(E.JOB_ID,-1) = NVL(X_Job_Id,-1)
36 AND NVL(E.POSITION_ID,-1) = NVL(X_Position_Id,-1)
37 AND NVL(E.GRADE_ID,-1) = NVL(X_Grade_Id,-1)
38 AND NVL(E.TRAINING_PLAN_ID,-1) = NVL(X_Training_Plan_Id,-1)
39 AND NVL(E.TRAINING_PLAN_MEMBER_ID,-1) = NVL(X_Training_Plan_Member_Id,-1)
40 AND NVL(E.EVENT_ID,-1) = NVL(X_Event_Id,-1);
41
42 IF (SQL%FOUND) then
43 hr_utility.set_message(801,'PER_7231_BUDGET_UNIQUE_COMB');
44 hr_utility.raise_error;
45 end if;
46 --
47 hr_utility.set_location(' Leaving:'||l_proc, 10);
48 --
49 EXCEPTION
50 when NO_DATA_FOUND then
51 null;
52
53 end Chk_Unique;
54 --
55 --
56 -- ----------------------------------------------------------------------------
57 -- |----------------------< Chk_Training_Plan_Id >----------------------------|
58 -- ----------------------------------------------------------------------------
59 PROCEDURE Chk_Training_Plan_Id(X_Training_Plan_Id IN NUMBER,
60 X_Business_Group IN NUMBER) IS
61 l_proc VARCHAR2(72) := g_package||'Chk_Training_Plan_Id';
62 l_status varchar2(30);
63 l_industry varchar2(30);
64 l_owner varchar2(30);
65 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('OTA', l_status,
66 l_industry, l_owner);
67 l_result VARCHAR2(255);
68
69 l_stmt_chk_tpc_exist varchar2(32000) :=
70 'select ''Y'' from all_tables where table_name = ''OTA_TRAINING_PLANS''
71 and owner = '''||l_owner||'''';
72
73 l_stmt_get_tpc_rows varchar2(32000) :=
74 'select ''Y'' from OTA_TRAINING_PLANS where training_plan_id = ' --
75 ||X_Training_Plan_Id ||' and Business_Group_Id = '||X_Business_Group;
76
77 l_dyn_curs integer;
78 l_dyn_rows integer;
79
80 --
81
82 BEGIN
83
84 hr_utility.set_location(' Entering:'|| l_proc, 5);
85
86 --
87 l_dyn_curs := dbms_sql.open_cursor;
88 --
89 -- Determine if the OTA_TRAINING_PLANS table exists
90 --
91 dbms_sql.parse(l_dyn_curs,l_stmt_chk_tpc_exist,dbms_sql.v7);
92 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
93 --
94 if dbms_sql.last_row_count > 0
95 then
96 -- Check if the training plan is referenced in training plans table
97 dbms_sql.parse(l_dyn_curs,l_stmt_get_tpc_rows,dbms_sql.v7);
98 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
99 --
100 if dbms_sql.last_row_count < 1 then
101 dbms_sql.close_cursor(l_dyn_curs);
102 hr_utility.set_message(800,'PER_52877_TPID_NOT_EXISTS');
103 hr_utility.raise_error;
104 end if;
105 end if;
106 if dbms_sql.is_open(l_dyn_curs) then
107 dbms_sql.close_cursor(l_dyn_curs);
108 end if;
109 --
110 hr_utility.set_location(' Leaving:'|| l_proc, 10);
111 --
112 END Chk_Training_Plan_Id;
113 --
114 --
115 -- ----------------------------------------------------------------------------
116 -- |----------------< Chk_Training_Plan_Member_Id>----------------------------|
117 -- ----------------------------------------------------------------------------
118 PROCEDURE Chk_Training_Plan_Member_Id(X_Training_Plan_Id IN NUMBER,
119 X_Training_Plan_Member_Id IN NUMBER,
120 X_Business_Group IN NUMBER) IS
121
122 l_proc VARCHAR2(72) := g_package||'Chk_Training_Plan_Member_Id';
123 l_status varchar2(30);
124 l_industry varchar2(30);
125 l_owner varchar2(30);
126 l_ret boolean := FND_INSTALLATION.GET_APP_INFO ('OTA', l_status,
127 l_industry, l_owner);
128
129 l_result VARCHAR2(255);
130 -- dynamic sql statments to check if the training plan member table exists
131 --
132 l_stmt_chk_tpc_exist varchar2(32000) := 'select ''Y'' from all_tables
133 where table_name = ''OTA_TRAINING_PLAN_MEMBERS''
134 and owner = '''||l_owner||'''';
135 --
136 -- dynamic sql statment to check if a row exists in training plan members
137 --
138 l_stmt_get_tpc_rows varchar2(32000) := 'select ''Y'' from OTA_TRAINING_PLAN_MEMBERS
139 where training_plan_member_id = '||X_Training_Plan_Member_id
140 ||' and training_plan_id = '||X_Training_Plan_Id
141 ||' and business_group_id = '||X_Business_Group;
142
143 --
144 l_dyn_curs integer;
145 l_dyn_rows integer;
146 --
147 --
148 --
149 BEGIN
150 --
151 hr_utility.set_location('Entering:'||l_proc, 5);
152
153 --
154 l_dyn_curs := dbms_sql.open_cursor;
155 --
156 -- Determine if the OTA_TRAINING_PLAN_MEMBERS table exists
157 --
158 dbms_sql.parse(l_dyn_curs,l_stmt_chk_tpc_exist,dbms_sql.v7);
159 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
160 --
161 if dbms_sql.last_row_count > 0 then
162 -- Check that the training plan member record exists
163 dbms_sql.parse(l_dyn_curs,l_stmt_get_tpc_rows,dbms_sql.v7);
164 l_dyn_rows := dbms_sql.execute_and_fetch(l_dyn_curs);
165 --
166 if dbms_sql.last_row_count < 1 then
167 dbms_sql.close_cursor(l_dyn_curs);
168 hr_utility.set_message(800,'PER_52878_TPMID_NOT_EXISTS');
169 hr_utility.raise_error;
170 end if;
171 end if;
172 if dbms_sql.is_open(l_dyn_curs) then
173 dbms_sql.close_cursor(l_dyn_curs);
174 end if;
175 --
176 hr_utility.set_location(' Leaving:'|| l_proc, 10);
177 --
178 END Chk_Training_Plan_Member_Id;
179 --
180 -- ----------------------------------------------------------------------------
181 -- |----------------------------< Chk_Event_Id >------------------------------|
182 -- ----------------------------------------------------------------------------
183 PROCEDURE Chk_Event_Id(X_Event_Id IN NUMBER,
184 X_Business_Group IN NUMBER) IS
185 l_result VARCHAR2(255);
186 l_proc VARCHAR2(72) := g_package||'Chk_Event_Id';
187 BEGIN
188 --
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 SELECT NULL
192 INTO l_result
193 FROM OTA_EVENTS OE
194 WHERE OE.Event_Id = X_Event_Id
195 AND OE.Business_Group_Id = X_Business_Group;
196 --
197 hr_utility.set_location(' Leaving:'||l_proc, 10);
198 --
199 EXCEPTION
200 when NO_DATA_FOUND then
201 hr_utility.set_message(800,'PER_52879_EID_NOT_EXISTS');
202 hr_utility.raise_error;
203
204 END Chk_Event_Id;
205
206 -- ----------------------------------------------------------------------------
207 -- |----------------------< chk_budget_element_id >-------------------------------|
208 -- ----------------------------------------------------------------------------
209 PROCEDURE chk_budget_element_id(X_budget_element_id IN NUMBER,
210 X_rowid IN VARCHAR2) IS
211 l_result VARCHAR2(255);
212 l_proc VARCHAR2(72) := g_package||'chk_budget_element_id';
213 BEGIN
214 --
215 hr_utility.set_location('Entering:'||l_proc, 5);
216 --
217 SELECT NULL
218 INTO l_result
219 FROM per_budget_elements pge
220 WHERE pge.budget_element_id = x_budget_element_id
221 AND (pge.rowid <> X_rowid OR X_rowid IS NULL);
222 --
223 hr_utility.set_location(' Leaving:'||l_proc, 10);
224 --
225 IF SQL%FOUND THEN
226 hr_utility.set_message(800,'PER_52880_BUD_ELE_EXISTS');
227 hr_utility.raise_error;
228 END IF;
229 --
230 EXCEPTION
231 when NO_DATA_FOUND then
232 null;
233 END chk_budget_element_id;
234
235 -- ----------------------------------------------------------------------------
236 -- |----------------------< chk_grade_id >-------------------------------|
237 -- ----------------------------------------------------------------------------
238 PROCEDURE chk_grade_id(X_grade_id IN NUMBER,
239 X_Business_Group IN NUMBER) IS
240 l_result VARCHAR2(255);
241 l_proc VARCHAR2(72) := g_package||'chk_grade_id';
242 BEGIN
243 --
244 hr_utility.set_location('Entering:'||l_proc, 5);
245 --
246
247 SELECT NULL
248 INTO l_result
249 FROM per_grades pg
250 WHERE pg.grade_id = X_Grade_Id
251 AND pg.business_group_id = X_Business_Group;
252 --
253 hr_utility.set_location(' Leaving:'||l_proc, 10);
254 --
255
256 EXCEPTION
257 when NO_DATA_FOUND then
258 hr_utility.set_message(801,'HR_51082_GRADE_INVALID_BG');
259 hr_utility.raise_error;
260 END chk_grade_id;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |----------------------< chk_job_id >-------------------------------|
264 -- ----------------------------------------------------------------------------
265 PROCEDURE chk_job_id(X_Job_Id IN NUMBER,
266 X_Business_Group IN NUMBER) IS
267
268 l_result VARCHAR2(255);
269 l_proc VARCHAR2(72) := g_package||'chk_job_id';
270 BEGIN
271 --
272 hr_utility.set_location('Entering:'||l_proc, 5);
273 --
274
275 SELECT NULL
276 INTO l_result
277 FROM per_jobs_v job
278 WHERE job.job_id = X_job_id
279 AND job.Business_Group_Id = X_Business_Group;
280 --
281 hr_utility.set_location(' Leaving:'||l_proc, 10);
282 --
283
284 EXCEPTION
285 when NO_DATA_FOUND then
286 hr_utility.set_message(801,'HR_51090_JOB_NOT_EXIST');
287 hr_utility.raise_error;
288 END chk_job_id;
289
290 -- ----------------------------------------------------------------------------
291 -- |----------------------< chk_position_id >-------------------------------|
292 -- ----------------------------------------------------------------------------
293 PROCEDURE chk_position_id(X_position_id IN NUMBER,
294 X_Business_Group IN NUMBER) IS
295 l_result VARCHAR2(255);
296 l_proc VARCHAR2(72) := g_package||'Chk_position_id';
297
298 BEGIN
299 --
300 hr_utility.set_location('Entering:'||l_proc, 5);
301 --
302
303 SELECT NULL
304 INTO l_result
305 FROM per_positions pos
306 WHERE pos.position_id = X_position_id
307 AND pos.Business_Group_Id = X_Business_Group;
308
309 --
310 hr_utility.set_location(' Leaving:'||l_proc, 10);
311 --
312
313 EXCEPTION
314 when NO_DATA_FOUND then
315 hr_utility.set_message(801,'HR_51093_POS_INVALID_BG');
316 hr_utility.raise_error;
317
318 END chk_position_id;
319
320 -- ----------------------------------------------------------------------------
321 -- |----------------------< chk_organization_id >-----------------------------|
322 -- ----------------------------------------------------------------------------
323 PROCEDURE chk_organization_id(X_organization_id IN NUMBER,
324 X_Business_Group IN NUMBER,
325 X_Budget_Version_Id NUMBER) IS
326 l_result VARCHAR2(255);
327 l_proc VARCHAR2(72) := g_package||'Chk_Organization_Id';
328
329 BEGIN
330 --
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333
334 SELECT NULL
335 INTO l_result
336 FROM per_all_organization_units org
337 WHERE org.organization_id = X_organization_id
338 AND org.business_group_id = X_business_group
339 AND org.internal_external_flag = 'INT'
340 AND org.date_from <= (select date_from
341 from per_budget_versions pbv
342 where pbv.budget_version_id = X_Budget_Version_Id)
343 AND nvl(org.date_to,hr_general.end_of_time) >= (select nvl(date_to,hr_general.end_of_time)
344 from per_budget_versions pbv
345 where pbv.budget_version_id = X_Budget_Version_Id);
346
347 --
348 hr_utility.set_location(' Leaving:'||l_proc, 10);
349 --
350
351 EXCEPTION
352 when NO_DATA_FOUND then
353 hr_utility.set_message(801,'HR_51371_POS_ORG_NOT_EXIST');
354 hr_utility.raise_error;
355
356 END chk_organization_id;
357
358 -- ----------------------------------------------------------------------------
359 -- |--------------------< chk_budget_version_id >-----------------------------|
360 -- ----------------------------------------------------------------------------
361 PROCEDURE chk_budget_version_id(X_budget_version_id IN NUMBER,
362 X_business_group IN NUMBER) IS
363
364 l_result VARCHAR2(255);
365 l_proc VARCHAR2(72) := g_package||'chk_budget_version_id';
366 BEGIN
367 --
368 hr_utility.set_location('Entering:'||l_proc, 5);
369 --
370 SELECT null
371 INTO l_result
372 FROM per_budget_versions pbv
373 WHERE pbv.budget_version_id = X_budget_version_id
374 AND pbv.business_group_id = X_Business_Group;
375 --
376 hr_utility.set_location(' Leaving:'||l_proc, 10);
377 --
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 hr_utility.set_message(800,'PER_52881_BUD_VER_NOT_EXISTS');
381 hr_utility.raise_error;
382 WHEN TOO_MANY_ROWS THEN
383 null;
384
385 END chk_budget_version_id;
386
387 -- ----------------------------------------------------------------------------
388 -- |---------------------------< Insert_Row >---------------------------------|
389 -- ----------------------------------------------------------------------------
390 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
391 X_Budget_Element_Id IN OUT NOCOPY NUMBER,
392 X_Business_Group_Id NUMBER,
393 X_Grade_Id NUMBER,
394 X_Job_Id NUMBER,
395 X_Position_Id NUMBER,
396 X_Organization_Id NUMBER,
397 X_Budget_Version_Id NUMBER,
398 X_Training_Plan_Id NUMBER,
399 X_Training_Plan_Member_Id NUMBER,
400 X_Event_Id NUMBER
401 ) IS
402
403 CURSOR C1 IS SELECT rowid FROM per_budget_elements
404 WHERE X_budget_element_id = X_Budget_Element_Id;
405
406 CURSOR C2 IS SELECT per_budget_elements_s.nextval
407 FROM dual;
408
409 l_proc VARCHAR2(72) := g_package||'Insert_Row';
410
411 BEGIN
412 --
413 hr_utility.set_location('Entering:'||l_proc, 5);
414 --
415 -- validate mandatory business_group
416 hr_api.validate_bus_grp_id(X_Business_Group_Id);
417
418 -- validate mandatory budget_version_id
419 hr_api.mandatory_arg_error
420 (p_api_name => l_proc,
421 p_argument => 'budget_version_id',
422 p_argument_value => X_budget_version_id);
423
424 chk_budget_version_id(x_budget_version_id,x_business_group_id);
425
426
427 -- validate grade_id
428 IF x_grade_id IS NOT NULL THEN
429 chk_grade_id(x_grade_id,x_business_group_id);
430 END IF;
431
432 -- validate job_id
433 IF x_job_id IS NOT NULL THEN
434 chk_job_id(x_job_id,x_business_group_id);
435 END IF;
436
437 -- validate position_id
438 IF x_position_id IS NOT NULL THEN
439 chk_position_id(x_position_id,x_business_group_id);
440 END IF;
441
442 -- validate organization_id
443 IF x_organization_id IS NOT NULL THEN
444 chk_organization_id(x_organization_id,x_business_group_id,x_budget_version_id);
445 END IF;
446 -- validate training_plan_id, training_plan_member_id, event_id
447 -- and enforce associated business rules if parent 'OTA_BUDGET'
448 -- per_budgets record exists
449 IF per_budgets_pkg.chk_ota_budget_type(null,x_budget_version_id,null) THEN
450
451 -- ensure one of training_plan_id, training_plan_member_id, event_id are set if OTA_BUDGET
452 IF x_training_plan_id IS NULL AND x_training_plan_member_id IS NULL AND x_event_id IS NULL THEN
453 hr_utility.set_message(800,'PER_52882_TP_NOT_NULL');
454 hr_utility.raise_error;
455 END IF;
456
457 -- ensure x_training_plan_member_id is not null if x_event_id is set
458 IF x_event_id IS NOT NULL AND x_training_plan_member_id IS NULL THEN
459 hr_utility.set_message(800,'PER_52883_TPMID_NOT_NULL');
460 hr_utility.raise_error;
461 END IF;
462
463 -- ensure x_training_plan_id is not null if x_training_plan_member_id is set
464 IF x_training_plan_member_id IS NOT NULL AND x_training_plan_id IS NULL THEN
465 hr_utility.set_message(800,'PER_52884_TPID_NOT_NULL');
466 hr_utility.raise_error;
467 END IF;
468
469 -- validate event_id
470 IF x_event_id IS NOT NULL THEN
471 chk_event_id(x_event_id,x_business_group_id);
472 END IF;
473
474 -- validate x_training_plan_id
475 IF x_training_plan_id IS NOT NULL THEN
476 chk_training_plan_id(x_training_plan_id,x_business_group_id);
477 END IF;
478
479 -- validate x_training_plan_member_id
480 IF x_training_plan_member_id IS NOT NULL THEN
481 chk_training_plan_member_id(x_training_plan_id,x_training_plan_member_id,x_business_group_id);
482 END IF;
483
484 -- raise error as these should not be set if 'HR_BUDGET' per_budgets is parent.
485 ELSIF x_training_plan_id IS NOT NULL OR x_training_plan_member_id IS NOT NULL OR x_event_id IS NOT NULL THEN
486 hr_utility.set_message(800,'PER_52885_TP_NULL');
487 hr_utility.raise_error;
488 END IF;
489
490 -- check uniqueness of the record
491 Chk_Unique(X_Rowid,
492 X_Budget_Version_Id,
493 X_Organization_Id,
494 X_Job_Id,
495 X_Position_Id,
496 X_Grade_Id,
497 X_Training_Plan_Id,
498 X_Training_Plan_Member_Id,
499 X_Event_Id);
500
501 -- generate new budget_element_id from sequence.
502 OPEN C2;
503 FETCH C2 INTO X_Budget_Element_Id;
504 CLOSE C2;
505
506 INSERT INTO per_budget_elements(
507 budget_element_id,
508 business_group_id,
509 grade_id,
510 job_id,
511 position_id,
512 organization_id,
513 budget_version_id,
514 training_plan_id,
515 training_plan_member_id,
516 event_id
517 ) VALUES (
518 X_Budget_Element_Id,
519 X_Business_Group_Id,
520 X_Grade_Id,
521 X_Job_Id,
522 X_Position_Id,
523 X_Organization_Id,
524 X_Budget_Version_Id,
525 X_Training_Plan_Id,
526 X_Training_Plan_Member_Id,
527 X_Event_Id);
528
529 OPEN C1;
530 FETCH C1 INTO X_Rowid;
531 if (C1%NOTFOUND) then
532 CLOSE C1;
533 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
534 hr_utility.set_message_token('PROCEDURE','Insert_Row');
535 hr_utility.set_message_token('STEP','1');
536 hr_utility.raise_error;
537 end if;
538 CLOSE C1;
539 --
540 hr_utility.set_location(' Leaving:'||l_proc, 10);
541 --
542 END Insert_Row;
543 --
544 -- ----------------------------------------------------------------------------
545 -- |-----------------------------< Lock_Row >---------------------------------|
546 -- ----------------------------------------------------------------------------
547 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
548 X_Budget_Element_Id NUMBER,
549 X_Business_Group_Id NUMBER,
550 X_Grade_Id NUMBER,
551 X_Job_Id NUMBER,
552 X_Position_Id NUMBER,
553 X_Organization_Id NUMBER,
554 X_Budget_Version_Id NUMBER,
555 X_Training_Plan_Id NUMBER,
556 X_Training_Plan_Member_Id NUMBER,
557 X_Event_Id NUMBER
558 ) IS
559 CURSOR C IS
560 SELECT *
561 FROM per_budget_elements
562 WHERE rowid = X_Rowid
563 FOR UPDATE of Budget_Element_Id NOWAIT;
564 Recinfo C%ROWTYPE;
565 l_proc VARCHAR2(72) := g_package||'Lock_Row';
566 BEGIN
567 --
568 hr_utility.set_location('Entering:'||l_proc, 5);
569 --
570 OPEN C;
571 FETCH C INTO Recinfo;
572 if (C%NOTFOUND) then
573 CLOSE C;
574 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
575 hr_utility.set_message_token('PROCEDURE','Lock_Row');
576 hr_utility.set_message_token('STEP','1');
577 hr_utility.raise_error;
578 end if;
579 CLOSE C;
580 if (
581 ( (Recinfo.budget_element_id = X_Budget_Element_Id)
582 OR ( (Recinfo.budget_element_id IS NULL)
583 AND (X_Budget_Element_Id IS NULL)))
584 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
585 OR ( (Recinfo.business_group_id IS NULL)
586 AND (X_Business_Group_Id IS NULL)))
587 AND ( (Recinfo.grade_id = X_Grade_Id)
588 OR ( (Recinfo.grade_id IS NULL)
589 AND (X_Grade_Id IS NULL)))
590 AND ( (Recinfo.job_id = X_Job_Id)
591 OR ( (Recinfo.job_id IS NULL)
592 AND (X_Job_Id IS NULL)))
593 AND ( (Recinfo.position_id = X_Position_Id)
594 OR ( (Recinfo.position_id IS NULL)
595 AND (X_Position_Id IS NULL)))
596 AND ( (Recinfo.organization_id = X_Organization_Id)
597 OR ( (Recinfo.organization_id IS NULL)
598 AND (X_Organization_Id IS NULL)))
599 AND ( (Recinfo.budget_version_id = X_Budget_Version_Id)
600 OR ( (Recinfo.budget_version_id IS NULL)
601 AND (X_Budget_Version_Id IS NULL)))
602 AND ( (Recinfo.training_plan_id = X_Training_Plan_Id)
603 OR ( (Recinfo.training_plan_id IS NULL)
604 AND (X_Training_Plan_Id IS NULL)))
605 AND ( (Recinfo.training_plan_member_id = X_Training_Plan_Member_Id)
606 OR ( (Recinfo.training_plan_member_id IS NULL)
607 AND (X_Training_Plan_Member_Id IS NULL)))
608 AND ( (Recinfo.event_id = X_Event_Id)
609 OR ( (Recinfo.event_id IS NULL)
610 AND (X_Event_Id IS NULL)))
611 ) then
612 return;
613 else
614 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
615 APP_EXCEPTION.RAISE_EXCEPTION;
616 end if;
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 10);
619 --
620 END Lock_Row;
621 --
622 -- ----------------------------------------------------------------------------
623 -- |---------------------------< Update_Row >---------------------------------|
624 -- ----------------------------------------------------------------------------
625 PROCEDURE Update_Row(X_Rowid VARCHAR2,
626 X_Budget_Element_Id NUMBER,
627 X_Business_Group_Id NUMBER,
628 X_Grade_Id NUMBER,
629 X_Job_Id NUMBER,
630 X_Position_Id NUMBER,
631 X_Organization_Id NUMBER,
632 X_Budget_Version_Id NUMBER,
633 X_Training_Plan_Id NUMBER,
634 X_Training_Plan_Member_Id NUMBER,
635 X_Event_Id NUMBER
636 ) IS
637
638 l_proc VARCHAR2(72) := g_package||'Update_Row';
639
640 BEGIN
641 --
642 hr_utility.set_location('Entering:'||l_proc, 5);
643 --
644 -- validate mandatory business_group
645 hr_api.validate_bus_grp_id(X_Business_Group_Id);
646
647 -- validate mandatory budget_version_id
648 hr_api.mandatory_arg_error
649 (p_api_name => l_proc,
650 p_argument => 'budget_version_id',
651 p_argument_value => X_budget_version_id);
652
653 chk_budget_version_id(x_budget_version_id,x_business_group_id);
654
655 -- validate mandatory x_rowid
656 hr_api.mandatory_arg_error
657 (p_api_name => l_proc,
658 p_argument => 'rowid',
659 p_argument_value => X_rowid);
660
661 -- validate mandatory x_budget_element_id
662 hr_api.mandatory_arg_error
663 (p_api_name => l_proc,
664 p_argument => 'budget_element_id',
665 p_argument_value => x_budget_element_id);
666
667 chk_budget_element_id(x_budget_element_id,x_rowid);
668 -- validate grade_id
669 IF x_grade_id IS NOT NULL THEN
670 chk_grade_id(x_grade_id,x_business_group_id);
671 END IF;
672
673 -- validate job_id
674 IF x_job_id IS NOT NULL THEN
675 chk_job_id(x_job_id,x_business_group_id);
676 END IF;
677
678 -- validate position_id
679 IF x_position_id IS NOT NULL THEN
680 chk_position_id(x_position_id,x_business_group_id);
681 END IF;
682
683 -- validate organization_id
684 IF x_organization_id IS NOT NULL THEN
685 chk_organization_id(x_organization_id,x_business_group_id,x_budget_version_id);
686 END IF;
687
688 -- validate training_plan_id, training_plan_member_id, event_id
689 -- and enforce associated business rules if parent 'OTA_BUDGET'
690 -- per_budgets record exists
691 IF per_budgets_pkg.chk_ota_budget_type(null,x_budget_version_id,null) THEN
692
693 -- ensure one of training_plan_id, training_plan_member_id, event_id are set if OTA_BUDGET
694 IF x_training_plan_id IS NULL AND x_training_plan_member_id IS NULL AND x_event_id IS NULL THEN
695 hr_utility.set_message(800,'PER_52882_TP_NOT_NULL');
696 hr_utility.raise_error;
697 END IF;
698
699 -- ensure x_training_plan_member_id is not null if x_event_id is set
700 IF x_event_id IS NOT NULL AND x_training_plan_member_id IS NULL THEN
701 hr_utility.set_message(800,'PER_52883_TPMID_NOT_NULL');
702 hr_utility.raise_error;
703 END IF;
704
705 -- ensure x_training_plan_id is not null if x_training_plan_member_id is set
706 IF x_training_plan_member_id IS NOT NULL AND x_training_plan_id IS NULL THEN
707 hr_utility.set_message(800,'PER_52884_TPID_NOT_NULL');
708 hr_utility.raise_error;
709 END IF;
710
711 -- validate event_id
712 IF x_event_id IS NOT NULL THEN
713 chk_event_id(x_event_id,x_business_group_id);
714 END IF;
715
716 -- validate x_training_plan_id
717 IF x_training_plan_id IS NOT NULL THEN
718 chk_training_plan_id(x_training_plan_id,x_business_group_id);
719 END IF;
720
721 -- validate x_training_plan_member_id
722 IF x_training_plan_member_id IS NOT NULL THEN
723 chk_training_plan_member_id(x_training_plan_id,x_training_plan_member_id,x_business_group_id);
724 END IF;
725
726 -- raise error as these should not be set if 'HR_BUDGET' per_budgets is parent.
727 ELSIF x_training_plan_id IS NOT NULL OR x_training_plan_member_id IS NOT NULL OR x_event_id IS NOT NULL THEN
728 hr_utility.set_message(800,'PER_52885_TP_NULL');
729 hr_utility.raise_error;
730 END IF;
731
732 -- check uniqueness of the record
733 Chk_Unique(X_Rowid,
734 X_Budget_Version_Id,
735 X_Organization_Id,
736 X_Job_Id,
737 X_Position_Id,
738 X_Grade_Id,
739 X_Training_Plan_Id,
740 X_Training_Plan_Member_Id,
741 X_Event_Id);
742
743
744 UPDATE per_budget_elements
745 SET
746 budget_element_id = X_Budget_Element_Id,
747 business_group_id = X_Business_Group_Id,
748 grade_id = X_Grade_Id,
749 job_id = X_Job_Id,
750 position_id = X_Position_Id,
751 organization_id = X_Organization_Id,
752 budget_version_id = X_Budget_Version_Id,
753 training_plan_id = X_Training_Plan_Id,
754 training_plan_member_id = X_Training_Plan_Member_Id,
755 event_id = X_Event_Id
756 WHERE rowid = X_rowid;
757
758 if (SQL%NOTFOUND) then
759 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
760 hr_utility.set_message_token('PROCEDURE','Update_Row');
761 hr_utility.set_message_token('STEP','1');
762 hr_utility.raise_error;
763 end if;
764 --
765 hr_utility.set_location(' Leaving:'||l_proc, 10);
766 --
767 END Update_Row;
768
769 -- ----------------------------------------------------------------------------
770 -- |---------------------------< Delete_Row >---------------------------------|
771 -- ----------------------------------------------------------------------------
772 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
773
774 CURSOR C_Values is
775 SELECT pbv.Rowid
776 FROM per_budget_values pbv
777 WHERE pbv.budget_element_id = (SELECT pbe.budget_element_id
778 FROM per_budget_elements pbe
779 WHERE pbe.rowid = X_Rowid);
780
781 l_val_rowid VARCHAR2(30);
782 l_proc VARCHAR2(72) := g_package||'Delete_Row';
783 l_budget_version_id NUMBER(15);
784 --
785 BEGIN
786 --
787 hr_utility.set_location('Entering:'||l_proc, 5);
788 --
789 SELECT budget_version_id into l_budget_version_id
790 FROM per_budget_elements pbe
791 WHERE pbe.rowid = x_rowid;
792 --
793 OPEN C_Values;
794 -- Cascade delete the appropriate child budget_elements recs if
795 -- parent per_budgets budget_type_code is 'HR_BUDGET';
796 IF per_budgets_pkg.chk_OTA_Budget_Type(NULL, l_budget_version_id, NULL) = FALSE THEN
797 LOOP
798 FETCH C_Values into l_val_rowid;
799 EXIT when (C_Values%NOTFOUND);
800 PER_BUDGET_VALUES_PKG.Delete_Row(X_Rowid => l_val_rowid);
801 END LOOP;
802 ELSE
803 FETCH C_Values into l_val_rowid;
804 IF C_Values%FOUND THEN
805 CLOSE C_Values;
806 --raise error as child record has been found
807 hr_utility.set_message(800,'PER_52886_BUD_VAL_DELETE_FAIL');
808 hr_utility.raise_error;
809 END IF;
810 END IF;
811 CLOSE C_Values;
812 --
813 --now delete the parent element
814 DELETE FROM PER_BUDGET_ELEMENTS
815 WHERE rowid = X_Rowid;
816 --
817 hr_utility.set_location(' Leaving:'||l_proc, 10);
818 --
819 EXCEPTION
820 WHEN NO_DATA_FOUND THEN
821 hr_utility.set_message(800,'PER_52881_BUD_VER_NOT_EXISTS');
822 hr_utility.raise_error;
823 END Delete_Row;
824
825 END PER_BUDGET_ELEMENTS_PKG;