[Home] [Help]
PACKAGE BODY: APPS.PER_SPINAL_PT_PLCMT_PKG
Source
1 package body PER_SPINAL_PT_PLCMT_PKG as
2 /* $Header: pespp01t.pkb 120.0 2005/05/31 21:28:38 appldev noship $ */
3
4 procedure check_ass_end(p_ass_id IN NUMBER,
5 p_sess IN DATE,
6 p_grd_id IN NUMBER) IS
7 l_exists VARCHAR2(1);
8
9 cursor c20 is
10 select 'x'
11 from per_assignments_f a
12 where a.assignment_id = p_ass_id
13 and a.effective_start_date >= p_sess
14 and a.grade_id <> p_grd_id;
15 --
16 begin
17 --
18 hr_utility.set_location('per_spinal_pt_plcmt_pkg.check_ass_end',1);
19 --
20 open c20;
21 --
22 fetch c20 into l_exists;
23 IF c20%found THEN
24 hr_utility.set_message(801,'HR_51770_GRD_PLC_ASS_EXIST');
25 close c20;
26 hr_utility.raise_error;
27 END IF;
28 --
29 close c20;
30 --
31 end check_ass_end;
32
33
34 procedure b_delete_valid(p_ass_id IN NUMBER,
35 p_pmt_id IN NUMBER,
36 p_eed IN DATE) IS
37 l_exists VARCHAR2(1);
38
39 cursor c1 is
40 select 'x'
41 from per_spinal_point_placements_f
42 where assignment_id = p_ass_id
43 and placement_id <> p_pmt_id
44 and effective_start_date > p_eed;
45 --
46 begin
47 --
48 hr_utility.set_location('per_spinal_pt_plcmt_pkg.b_delete_valid',1);
49 --
50 open c1;
51 --
52 fetch c1 into l_exists;
53 IF c1%found THEN
54 hr_utility.set_message(801, 'PER_7929_SP_PLACE_FUT_EXISTS');
55 close c1;
56 hr_utility.raise_error;
57 END IF;
58 --
59 close c1;
60 --
61 end b_delete_valid;
62
63
64 procedure pop_flds(p_sess IN DATE,
65 p_step_id IN NUMBER,
66 p_step_dsc IN OUT NOCOPY VARCHAR2,
67 p_step_no IN OUT NOCOPY NUMBER,
68 p_spoint_id IN OUT NOCOPY NUMBER,
69 p_rsn IN VARCHAR2,
70 p_rsn_desc IN OUT NOCOPY VARCHAR2) IS
71
72 cursor c2 is
73 select p1.spinal_point,
74 (nvl(gs.starting_step,1) + count(*))-1 count ,
75 p1.spinal_point_id
76 from per_spinal_points p1,
77 per_spinal_point_steps_f s2,
78 per_spinal_point_steps_f s1,
79 per_grade_spines_f gs
80 where p1.spinal_point_id = s1.spinal_point_id
81 and s1.grade_spine_id = s2.grade_spine_id
82 and s1.grade_spine_id = gs.grade_spine_id
83 and s1.sequence >= s2.sequence
84 and p_sess between s1.effective_start_date and s1.effective_end_date
85 and p_sess between s2.effective_start_date and s2.effective_end_date
86 and p_sess between gs.effective_start_date and gs.effective_end_date
87 and s1.step_id = p_step_id
88 group by p1.spinal_point,gs.starting_step, p1.spinal_point_id;
89 --
90 cursor c21 is
91 select meaning
92 from hr_lookups
93 where lookup_type = 'PLACEMENT_REASON'
94 and lookup_code = p_rsn;
95 --
96 l_count NUMBER;
97 begin
98 --
99 hr_utility.set_location('per_spinal_pt_plcmt_pkg.pop_flds',1);
100 --
101 open c2;
102 --
103 fetch c2 into p_step_dsc,
104 p_step_no,
105 p_spoint_id;
106 --
107 close c2;
108
109 --
110
111 --
112 hr_utility.set_location('per_spinal_pt_plcmt_pkg.pop_flds',2);
113 --
114 open c21;
115 --
116 fetch c21 into p_rsn_desc;
117 --
118 close c21;
119 --
120 end pop_flds;
121
122
123
124 procedure chk_exist(p_ass_id IN NUMBER,
125 p_sess IN DATE) IS
126 l_exists VARCHAR2(1);
127
128 cursor c3 is
129 select 'x'
130 from per_spinal_point_placements_f
131 where assignment_id = p_ass_id
132 and effective_end_date >= p_sess;
133 --
134 begin
135 --
136 hr_utility.set_location('per_spinal_pt_plcmt_pkg.chk_exist',1);
137 --
138 open c3;
139 --
140 fetch c3 into l_exists;
141 IF c3%found THEN
142 hr_utility.set_message(801, 'PER_7928_SP_PLACE_EXISTS');
143 close c3;
144 hr_utility.raise_error;
145 END IF;
146 --
147 close c3;
148 --
149 end chk_exist;
150
151
152
153 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
154 X_Placement_Id IN OUT NOCOPY NUMBER,
155 X_Effective_Start_Date DATE,
156 X_Effective_End_Date DATE,
157 X_Business_Group_Id NUMBER,
158 X_Assignment_Id NUMBER,
159 X_Step_Id NUMBER,
160 X_Auto_Increment_Flag VARCHAR2,
161 X_Parent_Spine_Id NUMBER,
162 X_Reason VARCHAR2,
163 X_Information1 VARCHAR2,
164 X_Information2 VARCHAR2,
165 X_Information3 VARCHAR2,
166 X_Information4 VARCHAR2,
167 X_Information5 VARCHAR2,
168 X_Information6 VARCHAR2,
169 X_Information7 VARCHAR2,
170 X_Information8 VARCHAR2,
171 X_Information9 VARCHAR2,
172 X_Information10 VARCHAR2,
173 X_Information11 VARCHAR2,
174 X_Information12 VARCHAR2,
175 X_Information13 VARCHAR2,
176 X_Information14 VARCHAR2,
177 X_Information15 VARCHAR2,
178 X_Information16 VARCHAR2,
179 X_Information17 VARCHAR2,
180 X_Information18 VARCHAR2,
181 X_Information19 VARCHAR2,
182 X_Information20 VARCHAR2,
183 X_Information21 VARCHAR2,
184 X_Information22 VARCHAR2,
185 X_Information23 VARCHAR2,
186 X_Information24 VARCHAR2,
187 X_Information25 VARCHAR2,
188 X_Information26 VARCHAR2,
189 X_Information27 VARCHAR2,
190 X_Information28 VARCHAR2,
191 X_Information29 VARCHAR2,
192 X_Information30 VARCHAR2,
193 X_Information_category VARCHAR2
194 ) IS
195 CURSOR C IS SELECT rowid FROM per_spinal_point_placements_f
196 WHERE placement_id = x_placement_id;
197
198 CURSOR C2 IS SELECT per_spinal_point_placements_s.nextval FROM sys.dual;
199 BEGIN
200 if (X_placement_id is NULL) then
201 OPEN C2;
202 FETCH C2 INTO X_placement_id;
203 CLOSE C2;
204 end if;
205 INSERT INTO per_spinal_point_placements_f(
206 placement_id,
207 effective_start_date,
208 effective_end_date,
209 business_group_id,
210 assignment_id,
211 step_id,
212 auto_increment_flag,
213 parent_spine_id,
214 reason,
215 information1,
216 information2,
217 information3,
218 information4,
219 information5,
220 information6,
221 information7,
222 information8,
223 information9,
224 information10,
225 information11,
226 information12,
227 information13,
228 information14,
229 information15,
230 information16,
231 information17,
232 information18,
233 information19,
234 information20,
235 information21,
236 information22,
237 information23,
238 information24,
239 information25,
240 information26,
241 information27,
242 information28,
243 information29,
244 information30,
245 information_category
246 ) VALUES (
247 X_Placement_Id,
248 X_Effective_Start_Date,
249 X_Effective_End_Date,
250 X_Business_Group_Id,
251 X_Assignment_Id,
252 X_Step_Id,
253 X_Auto_Increment_Flag,
254 X_Parent_Spine_Id,
255 X_Reason,
256 X_Information1,
257 X_Information2,
258 X_Information3,
259 X_Information4,
260 X_Information5,
261 X_Information6,
262 X_Information7,
263 X_Information8,
264 X_Information9,
265 X_Information10,
266 X_Information11,
267 X_Information12,
268 X_Information13,
269 X_Information14,
270 X_Information15,
271 X_Information16,
272 X_Information17,
273 X_Information18,
274 X_Information19,
275 X_Information20,
276 X_Information21,
277 X_Information22,
278 X_Information23,
279 X_Information24,
280 X_Information25,
281 X_Information26,
282 X_Information27,
283 X_Information28,
284 X_Information29,
285 X_Information30,
286 X_Information_category
287 );
288
289 OPEN C;
290 FETCH C INTO X_Rowid;
291 if (C%NOTFOUND) then
292 CLOSE C;
293 RAISE NO_DATA_FOUND;
294 end if;
295 CLOSE C;
296 END Insert_Row;
297
298
299
300 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
301 X_Placement_Id NUMBER,
302 X_Effective_Start_Date DATE,
303 X_Effective_End_Date DATE,
304 X_Business_Group_Id NUMBER,
305 X_Assignment_Id NUMBER,
306 X_Step_Id NUMBER,
307 X_Auto_Increment_Flag VARCHAR2,
308 X_Parent_Spine_Id NUMBER,
309 X_Reason VARCHAR2,
310 X_Information1 VARCHAR2,
311 X_Information2 VARCHAR2,
312 X_Information3 VARCHAR2,
313 X_Information4 VARCHAR2,
314 X_Information5 VARCHAR2,
315 X_Information6 VARCHAR2,
316 X_Information7 VARCHAR2,
317 X_Information8 VARCHAR2,
318 X_Information9 VARCHAR2,
319 X_Information10 VARCHAR2,
320 X_Information11 VARCHAR2,
321 X_Information12 VARCHAR2,
322 X_Information13 VARCHAR2,
323 X_Information14 VARCHAR2,
324 X_Information15 VARCHAR2,
325 X_Information16 VARCHAR2,
326 X_Information17 VARCHAR2,
327 X_Information18 VARCHAR2,
328 X_Information19 VARCHAR2,
329 X_Information20 VARCHAR2,
330 X_Information21 VARCHAR2,
331 X_Information22 VARCHAR2,
332 X_Information23 VARCHAR2,
333 X_Information24 VARCHAR2,
334 X_Information25 VARCHAR2,
335 X_Information26 VARCHAR2,
336 X_Information27 VARCHAR2,
337 X_Information28 VARCHAR2,
338 X_Information29 VARCHAR2,
339 X_Information30 VARCHAR2,
340 X_Information_category VARCHAR2
341 ) IS
342 CURSOR C IS
343 SELECT *
344 FROM per_spinal_point_placements_f
345 WHERE rowid = chartorowid(X_Rowid)
346 FOR UPDATE of placement_id NOWAIT;
347 Recinfo C%ROWTYPE;
348 BEGIN
349 OPEN C;
350 FETCH C INTO Recinfo;
351 if (C%NOTFOUND) then
352 CLOSE C;
353 RAISE NO_DATA_FOUND;
354 end if;
355 CLOSE C;
356
357 recinfo.auto_increment_flag := rtrim(recinfo.auto_increment_flag);
358 recinfo.reason := rtrim(recinfo.reason);
359
360 if (
361 ( (Recinfo.placement_id = X_Placement_Id)
362 OR ( (Recinfo.placement_id IS NULL)
363 AND (X_Placement_Id IS NULL)))
364 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
365 OR ( (Recinfo.effective_start_date IS NULL)
366 AND (X_Effective_Start_Date IS NULL)))
367 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
368 OR ( (Recinfo.effective_end_date IS NULL)
369 AND (X_Effective_End_Date IS NULL)))
370 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
371 OR ( (Recinfo.business_group_id IS NULL)
372 AND (X_Business_Group_Id IS NULL)))
373 AND ( (Recinfo.assignment_id = X_Assignment_Id)
374 OR ( (Recinfo.assignment_id IS NULL)
375 AND (X_Assignment_Id IS NULL)))
376 AND ( (Recinfo.step_id = X_Step_Id)
377 OR ( (Recinfo.step_id IS NULL)
378 AND (X_Step_Id IS NULL)))
379 AND ( (Recinfo.auto_increment_flag = X_Auto_Increment_Flag)
380 OR ( (Recinfo.auto_increment_flag IS NULL)
381 AND (X_Auto_Increment_Flag IS NULL)))
382 AND ( (Recinfo.parent_spine_id = X_Parent_Spine_Id)
383 OR ( (Recinfo.parent_spine_id IS NULL)
384 AND (X_Parent_Spine_Id IS NULL)))
385 AND ( (Recinfo.reason = X_Reason)
386 OR ( (Recinfo.reason IS NULL)
387 AND (X_Reason IS NULL)))
388 ) then
389 return;
390 else
391 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
392 APP_EXCEPTION.RAISE_EXCEPTION;
393 end if;
394 END Lock_Row;
395
396
397
398 PROCEDURE Update_Row(X_Rowid VARCHAR2,
399 X_Placement_Id NUMBER,
400 X_Effective_Start_Date DATE,
401 X_Effective_End_Date DATE,
402 X_Business_Group_Id NUMBER,
403 X_Assignment_Id NUMBER,
404 X_Step_Id NUMBER,
405 X_Auto_Increment_Flag VARCHAR2,
406 X_Parent_Spine_Id NUMBER,
407 X_Reason VARCHAR2,
408 X_Information1 VARCHAR2,
409 X_Information2 VARCHAR2,
410 X_Information3 VARCHAR2,
411 X_Information4 VARCHAR2,
412 X_Information5 VARCHAR2,
413 X_Information6 VARCHAR2,
414 X_Information7 VARCHAR2,
415 X_Information8 VARCHAR2,
416 X_Information9 VARCHAR2,
417 X_Information10 VARCHAR2,
418 X_Information11 VARCHAR2,
419 X_Information12 VARCHAR2,
420 X_Information13 VARCHAR2,
421 X_Information14 VARCHAR2,
422 X_Information15 VARCHAR2,
423 X_Information16 VARCHAR2,
424 X_Information17 VARCHAR2,
425 X_Information18 VARCHAR2,
426 X_Information19 VARCHAR2,
427 X_Information20 VARCHAR2,
428 X_Information21 VARCHAR2,
429 X_Information22 VARCHAR2,
430 X_Information23 VARCHAR2,
431 X_Information24 VARCHAR2,
432 X_Information25 VARCHAR2,
433 X_Information26 VARCHAR2,
434 X_Information27 VARCHAR2,
435 X_Information28 VARCHAR2,
436 X_Information29 VARCHAR2,
437 X_Information30 VARCHAR2,
438 X_Information_category VARCHAR2
439 ) IS
440 BEGIN
441 UPDATE per_spinal_point_placements_f
442 SET
443 placement_id = X_Placement_Id,
444 effective_start_date = X_Effective_Start_Date,
445 effective_end_date = X_Effective_End_Date,
446 business_group_id = X_Business_Group_Id,
447 assignment_id = X_Assignment_Id,
448 step_id = X_Step_Id,
449 auto_increment_flag = X_Auto_Increment_Flag,
450 parent_spine_id = X_Parent_Spine_Id,
451 reason = X_Reason,
452 information1 = X_Information1,
453 information2 = X_Information2,
454 information3 = X_Information3,
455 information4 = X_Information4,
456 information5 = X_Information5,
457 information6 = X_Information6,
458 information7 = X_Information7,
459 information8 = X_Information8,
460 information9 = X_Information9,
461 information10 = X_Information10,
462 information11 = X_Information11,
463 information12 = X_Information12,
464 information13 = X_Information13,
465 information14 = X_Information14,
466 information15 = X_Information15,
467 information16 = X_Information16,
468 information17 = X_Information17,
469 information18 = X_Information18,
470 information19 = X_Information19,
471 information20 = X_Information20,
472 information21 = X_Information21,
473 information22 = X_Information22,
474 information23 = X_Information23,
475 information24 = X_Information24,
476 information25 = X_Information25,
477 information26 = X_Information26,
478 information27 = X_Information27,
479 information28 = X_Information28,
480 information29 = X_Information29,
481 information30 = X_Information30,
482 information_category = X_Information_category
483 WHERE rowid = chartorowid(X_Rowid);
484
485 if (SQL%NOTFOUND) then
486 RAISE NO_DATA_FOUND;
487 end if;
488
489 END Update_Row;
490
491
492
493 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
494 BEGIN
495 DELETE FROM per_spinal_point_placements_f
496 WHERE rowid = chartorowid(X_rowid);
497
498 if (SQL%NOTFOUND) then
499 RAISE NO_DATA_FOUND;
500 end if;
501 END Delete_Row;
502
503
504 --procedure to populate the control block (CTRL) in the form PERWSSPP
505 --this was placed in the 'per_spinal_pt_plcmt_pkg' as there
506 --were none for the control block being as it wasn't based
507 --on a table.
508
509 procedure pop_ctrl(p_ass_id IN NUMBER,
510 p_grd_id IN OUT NOCOPY NUMBER ,
511 p_grd_name IN OUT NOCOPY VARCHAR2 ,
512 p_ceil_sp IN OUT NOCOPY VARCHAR2 ,
513 p_parent_name IN OUT NOCOPY VARCHAR2 ,
514 p_parent_id IN OUT NOCOPY NUMBER ,
515 p_ceil_seq In OUT NOCOPY NUMBER ,
516 p_ceil_step In OUT NOCOPY NUMBER ,
517 p_ass_eed IN OUT NOCOPY DATE ,
518 p_inc_def IN OUT NOCOPY VARCHAR2 ,
519 p_sess In DATE,
520 p_bgroup_id IN NUMBER,
521 p_check IN VARCHAR2,
522 p_grd_ldr_name in out nocopy varchar2) IS
523
524 cursor c5 is
525 select asg.effective_end_date,
526 g.grade_id,
527 g.name,
528 sp.spinal_point,
529 ps.name,
530 ps.parent_spine_id,
531 sp.sequence,
532 pgm.name
533 from per_grades_vl g,
534 per_grade_spines_f gs,
535 per_parent_spines ps,
536 per_spinal_points sp,
537 per_spinal_point_steps_f sps,
538 per_all_assignments_f asg,
539 ben_pgm_f pgm
540 where asg.assignment_id = p_ass_id
541 and asg.grade_id is not null
542 and asg.grade_id = g.grade_id
543 and asg.grade_ladder_pgm_id = pgm.pgm_id(+)
544 and g.grade_id = gs.grade_id
545 and ps.parent_spine_id = gs.parent_spine_id
546 and sps.spinal_point_id = sp.spinal_point_id
547 and nvl(asg.special_ceiling_step_id,gs.ceiling_step_id) = sps.step_id
548 and p_sess between asg.effective_start_date and asg.effective_end_date
549 and p_sess between sps.effective_start_date and sps.effective_end_date
550 and p_sess between gs.effective_start_date and gs.effective_end_date
551 and p_sess between NVL(pgm.effective_start_date,p_sess)
552 and nvl(pgm.effective_end_date,p_sess)
553 and asg.business_group_id + 0 = p_bgroup_id;
554 --
555 cursor c6 is
556 select (nvl(gs.starting_step,1)+count(*))-1
557 from per_spinal_points p2,
558 per_spinal_point_steps_f s2,
559 per_grade_spines_f gs
560 where s2.spinal_point_id = p2.spinal_point_id
561 and p_ceil_seq >= p2.sequence
562 and p_sess between s2.effective_start_date and s2.effective_end_date
563 and p_sess between gs.effective_start_date and gs.effective_end_date
564 and gs.grade_spine_id = s2.grade_spine_id
565 and gs.grade_id = p_grd_id
566 and gs.parent_spine_id = p_parent_id
567 group by gs.starting_step;
568 --
569 cursor c7 is
570 select meaning
571 from fnd_lookups
572 where lookup_type = 'YES_NO'
573 and lookup_code = 'Y';
574 --
575 l_ceil_step NUMBER;
576 begin
577 --
578 hr_utility.set_location('per_spinal_pt_plcmt_pkg.pop_ctrl',1);
579 --
580 open c5;
581 --
582 fetch c5 into p_ass_eed,
583 p_grd_id,
584 p_grd_name,
585 p_ceil_sp,
586 p_parent_name,
587 p_parent_id,
588 p_ceil_seq,
589 p_grd_ldr_name;
590 --
591 IF c5%notfound AND upper(p_check) = 'Y' THEN
592 hr_utility.set_message(801, 'HR_7112_WFLOW_ASSGT_NO_PLAC');
593 close c5;
594 hr_utility.raise_error;
595 END IF;
596 --
597 close c5;
598 --
599 hr_utility.set_location('per_spinal_pt_plcmt_pkg.pop_ctrl',2);
600 --
601 open c6;
602 --
603 fetch c6 into p_ceil_step;
604 --
605 close c6;
606
607 --
608 hr_utility.set_location('per_spinal_pt_plcmt_pkg.pop_ctrl',3);
609 --
610 open c7;
611 --
612 fetch c7 into p_inc_def;
613 --
614 close c7;
615 --
616 end pop_ctrl;
617
618 procedure test_path_to_perwsspp (p_ass_id NUMBER) is
619 --
620 l_grd_id NUMBER;
621 l_grd_name VARCHAR2(240);
622 l_ceil_sp VARCHAR2(30);
623 l_parent_name VARCHAR2(30);
624 l_parent_id NUMBER;
625 l_ceil_seq NUMBER;
626 l_ceil_step NUMBER;
627 l_ass_eed DATE;
628 l_inc_def VARCHAR2(80);
629 l_sess DATE;
630 l_bgroup_id NUMBER;
631 l_proc VARCHAR2(30) := 'test_path_to_perwsspp';
632 l_grd_ldr_name varchar2(240);
633 --
634 cursor c_derive_info is
635 select asg.business_group_id,
636 ses.effective_date
637 from per_assignments_f asg,
638 fnd_sessions ses
639 where asg.assignment_id = p_ass_id
640 and ses.session_id = userenv('SESSIONID')
641 and ses.effective_date between
642 asg.effective_start_date and asg.effective_end_date;
643 --
644 begin
645 --
646 open c_derive_info;
647 --
648 fetch c_derive_info into l_bgroup_id,
649 l_sess;
650 IF c_derive_info%notfound THEN
651 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
652 hr_utility.set_message_token('PROCEDURE',l_proc);
653 hr_utility.set_message_token('STEP','1');
654 hr_utility.raise_error;
655 close c_derive_info;
656 END IF;
657 --
658 close c_derive_info;
659 --
660 per_spinal_pt_plcmt_pkg.pop_ctrl(p_ass_id
661 ,l_grd_id
662 ,l_grd_name
663 ,l_ceil_sp
664 ,l_parent_name
665 ,l_parent_id
666 ,l_ceil_seq
667 ,l_ceil_step
668 ,l_ass_eed
669 ,l_inc_def
670 ,l_sess
671 ,l_bgroup_id
672 ,'Y'
673 ,l_grd_ldr_name);
674 end test_path_to_perwsspp;
675
676 end PER_SPINAL_PT_PLCMT_PKG;