[Home] [Help]
PACKAGE BODY: APPS.PER_SPINAL_POINT_STEPS_PKG
Source
1 package body PER_SPINAL_POINT_STEPS_PKG as
2 /* $Header: pesps01t.pkb 120.0 2005/05/31 21:35:53 appldev noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Step_Id IN OUT NOCOPY NUMBER,
6 X_Effective_Start_Date DATE,
7 X_Effective_End_Date DATE,
8 X_Business_Group_Id NUMBER,
9 X_Spinal_Point_Id NUMBER,
10 X_Grade_Spine_Id NUMBER,
11 X_Sequence NUMBER,
12 X_request_id NUMBER,
13 X_program_application_id NUMBER,
14 X_program_id NUMBER,
15 X_program_update_date DATE,
16 X_Information1 VARCHAR2,
17 X_Information2 VARCHAR2,
18 X_Information3 VARCHAR2,
19 X_Information4 VARCHAR2,
20 X_Information5 VARCHAR2,
21 X_Information6 VARCHAR2,
22 X_Information7 VARCHAR2,
23 X_Information8 VARCHAR2,
24 X_Information9 VARCHAR2,
25 X_Information10 VARCHAR2,
26 X_Information11 VARCHAR2,
27 X_Information12 VARCHAR2,
28 X_Information13 VARCHAR2,
29 X_Information14 VARCHAR2,
30 X_Information15 VARCHAR2,
31 X_Information16 VARCHAR2,
32 X_Information17 VARCHAR2,
33 X_Information18 VARCHAR2,
34 X_Information19 VARCHAR2,
35 X_Information20 VARCHAR2,
36 X_Information21 VARCHAR2,
37 X_Information22 VARCHAR2,
38 X_Information23 VARCHAR2,
39 X_Information24 VARCHAR2,
40 X_Information25 VARCHAR2,
41 X_Information26 VARCHAR2,
42 X_Information27 VARCHAR2,
43 X_Information28 VARCHAR2,
44 X_Information29 VARCHAR2,
45 X_Information30 VARCHAR2,
46 X_Information_category VARCHAR2
47 ) IS
48 CURSOR C IS SELECT rowid FROM per_spinal_point_steps
49 WHERE step_id = X_step_id;
50
51 CURSOR C2 IS SELECT per_spinal_point_steps_s.nextval FROM sys.dual;
52 BEGIN
53 if (X_step_id is NULL) then
54 OPEN C2;
55 FETCH C2 INTO X_step_id;
56 CLOSE C2;
57 end if;
58 INSERT INTO per_spinal_point_steps(
59 step_id,
60 effective_start_date,
61 effective_end_date,
62 business_group_id,
63 spinal_point_id,
64 grade_spine_id,
65 sequence,
66 request_id,
67 program_application_id,
68 program_id,
69 program_update_date,
70 information1,
71 information2,
72 information3,
73 information4,
74 information5,
75 information6,
76 information7,
77 information8,
78 information9,
79 information10,
80 information11,
81 information12,
82 information13,
83 information14,
84 information15,
85 information16,
86 information17,
87 information18,
88 information19,
89 information20,
90 information21,
91 information22,
92 information23,
93 information24,
94 information25,
95 information26,
96 information27,
97 information28,
98 information29,
99 information30,
100 information_category
101 ) VALUES (
102 X_Step_Id,
103 X_Effective_Start_Date,
104 X_Effective_End_Date,
105 X_Business_Group_Id,
106 X_Spinal_Point_Id,
107 X_Grade_Spine_Id,
108 X_Sequence,
109 X_request_id,
110 X_program_application_id,
111 X_program_id,
112 X_program_update_date,
113 X_Information1,
114 X_Information2,
115 X_Information3,
116 X_Information4,
117 X_Information5,
118 X_Information6,
119 X_Information7,
120 X_Information8,
121 X_Information9,
122 X_Information10,
123 X_Information11,
124 X_Information12,
125 X_Information13,
126 X_Information14,
127 X_Information15,
128 X_Information16,
129 X_Information17,
130 X_Information18,
131 X_Information19,
132 X_Information20,
133 X_Information21,
134 X_Information22,
135 X_Information23,
136 X_Information24,
137 X_Information25,
138 X_Information26,
139 X_Information27,
140 X_Information28,
141 X_Information29,
142 X_Information30,
143 X_Information_category
144 );
145
146 OPEN C;
147 FETCH C INTO X_Rowid;
148 if (C%NOTFOUND) then
149 CLOSE C;
150 RAISE NO_DATA_FOUND;
151 end if;
152 CLOSE C;
153 END Insert_Row;
154
155
156
157 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
158 X_Step_Id NUMBER,
159 X_Effective_Start_Date DATE,
160 X_Effective_End_Date DATE,
161 X_Business_Group_Id NUMBER,
162 X_Spinal_Point_Id NUMBER,
163 X_Grade_Spine_Id NUMBER,
164 X_Sequence NUMBER,
165 X_request_id NUMBER,
166 X_program_application_id NUMBER,
167 X_program_id NUMBER,
168 X_program_update_date DATE,
169 X_Information1 VARCHAR2,
170 X_Information2 VARCHAR2,
171 X_Information3 VARCHAR2,
172 X_Information4 VARCHAR2,
173 X_Information5 VARCHAR2,
174 X_Information6 VARCHAR2,
175 X_Information7 VARCHAR2,
176 X_Information8 VARCHAR2,
177 X_Information9 VARCHAR2,
178 X_Information10 VARCHAR2,
179 X_Information11 VARCHAR2,
180 X_Information12 VARCHAR2,
181 X_Information13 VARCHAR2,
182 X_Information14 VARCHAR2,
183 X_Information15 VARCHAR2,
184 X_Information16 VARCHAR2,
185 X_Information17 VARCHAR2,
186 X_Information18 VARCHAR2,
187 X_Information19 VARCHAR2,
188 X_Information20 VARCHAR2,
189 X_Information21 VARCHAR2,
190 X_Information22 VARCHAR2,
191 X_Information23 VARCHAR2,
192 X_Information24 VARCHAR2,
193 X_Information25 VARCHAR2,
194 X_Information26 VARCHAR2,
195 X_Information27 VARCHAR2,
196 X_Information28 VARCHAR2,
197 X_Information29 VARCHAR2,
198 X_Information30 VARCHAR2,
199 X_Information_category VARCHAR2
200 ) IS
201 CURSOR C IS
202 SELECT *
203 FROM per_spinal_point_steps
204 WHERE rowid = chartorowid(X_Rowid)
205 FOR UPDATE of step_id NOWAIT;
206 Recinfo C%ROWTYPE;
207 BEGIN
208 OPEN C;
209 FETCH C INTO Recinfo;
210 if (C%NOTFOUND) then
211 CLOSE C;
212 RAISE NO_DATA_FOUND;
213 end if;
214 CLOSE C;
215 if ( ( (Recinfo.step_id = X_Step_Id)
216 OR ( (Recinfo.step_id IS NULL)
217 AND (X_Step_Id IS NULL)))
218 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
219 OR ( (Recinfo.effective_start_date IS NULL)
220 AND (X_Effective_Start_Date IS NULL)))
221 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
222 OR ( (Recinfo.effective_end_date IS NULL)
223 AND (X_Effective_End_Date IS NULL)))
224 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
225 OR ( (Recinfo.business_group_id IS NULL)
226 AND (X_Business_Group_Id IS NULL)))
227 AND ( (Recinfo.spinal_point_id = X_Spinal_Point_Id)
228 OR ( (Recinfo.spinal_point_id IS NULL)
229 AND (X_Spinal_Point_Id IS NULL)))
230 AND ( (Recinfo.grade_spine_id = X_Grade_Spine_Id)
231 OR ( (Recinfo.grade_spine_id IS NULL)
232 AND (X_Grade_Spine_Id IS NULL)))
233 AND ( (Recinfo.sequence = X_Sequence)
234 OR ( (Recinfo.sequence IS NULL)
235 AND (X_Sequence IS NULL)))
236 AND ( (Recinfo.request_id = X_request_id)
237 OR ( (Recinfo.request_id IS NULL)
238 AND (X_request_id IS NULL)))
239 AND ( (Recinfo.program_application_id = X_program_application_id)
240 OR ( (Recinfo.program_application_id IS NULL)
241 AND (X_program_application_id IS NULL)))
242 AND ( (Recinfo.program_id = X_program_id)
243 OR ( (Recinfo.program_id IS NULL)
244 AND (X_program_id IS NULL)))
245 AND ( (Recinfo.program_update_date = X_program_update_date)
246 OR ( (Recinfo.program_update_date IS NULL)
247 AND (X_program_update_date IS NULL)))
248 ) then
249 return;
250 else
251 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
252 APP_EXCEPTION.RAISE_EXCEPTION;
253 end if;
254 END Lock_Row;
255
256
257
258 PROCEDURE Update_Row(X_Rowid VARCHAR2,
259 X_Step_Id NUMBER,
260 X_Effective_Start_Date DATE,
261 X_Effective_End_Date DATE,
262 X_Business_Group_Id NUMBER,
263 X_Spinal_Point_Id NUMBER,
264 X_Grade_Spine_Id NUMBER,
265 X_Sequence NUMBER,
266 X_request_id NUMBER,
267 X_program_application_id NUMBER,
268 X_program_id NUMBER,
269 X_program_update_date DATE,
270 X_Information1 VARCHAR2,
271 X_Information2 VARCHAR2,
272 X_Information3 VARCHAR2,
273 X_Information4 VARCHAR2,
274 X_Information5 VARCHAR2,
275 X_Information6 VARCHAR2,
276 X_Information7 VARCHAR2,
277 X_Information8 VARCHAR2,
278 X_Information9 VARCHAR2,
279 X_Information10 VARCHAR2,
280 X_Information11 VARCHAR2,
281 X_Information12 VARCHAR2,
282 X_Information13 VARCHAR2,
283 X_Information14 VARCHAR2,
284 X_Information15 VARCHAR2,
285 X_Information16 VARCHAR2,
286 X_Information17 VARCHAR2,
287 X_Information18 VARCHAR2,
288 X_Information19 VARCHAR2,
289 X_Information20 VARCHAR2,
290 X_Information21 VARCHAR2,
291 X_Information22 VARCHAR2,
292 X_Information23 VARCHAR2,
293 X_Information24 VARCHAR2,
294 X_Information25 VARCHAR2,
295 X_Information26 VARCHAR2,
296 X_Information27 VARCHAR2,
297 X_Information28 VARCHAR2,
298 X_Information29 VARCHAR2,
299 X_Information30 VARCHAR2,
300 X_Information_category VARCHAR2
301 ) IS
302 BEGIN
303 UPDATE per_spinal_point_steps
304 SET
305 step_id = X_Step_Id,
306 effective_start_date = X_Effective_Start_Date,
307 effective_end_date = X_Effective_End_Date,
308 business_group_id = X_Business_Group_Id,
309 spinal_point_id = X_Spinal_Point_Id,
310 grade_spine_id = X_Grade_Spine_Id,
311 sequence = X_Sequence,
312 request_id = X_request_id,
313 program_application_id = X_program_application_id,
314 program_id = X_program_id,
315 program_update_date = X_program_update_date,
316 information1 = X_Information1,
317 information2 = X_Information2,
318 information3 = X_Information3,
319 information4 = X_Information4,
320 information5 = X_Information5,
321 information6 = X_Information6,
322 information7 = X_Information7,
323 information8 = X_Information8,
324 information9 = X_Information9,
325 information10 = X_Information10,
326 information11 = X_Information11,
330 information15 = X_Information15,
327 information12 = X_Information12,
328 information13 = X_Information13,
329 information14 = X_Information14,
331 information16 = X_Information16,
332 information17 = X_Information17,
333 information18 = X_Information18,
334 information19 = X_Information19,
335 information20 = X_Information20,
336 information21 = X_Information21,
337 information22 = X_Information22,
338 information23 = X_Information23,
339 information24 = X_Information24,
340 information25 = X_Information25,
341 information26 = X_Information26,
342 information27 = X_Information27,
343 information28 = X_Information28,
344 information29 = X_Information29,
345 information30 = X_Information30,
346 information_category = X_Information_category
347 WHERE rowid = chartorowid(X_rowid);
348
349 if (SQL%NOTFOUND) then
350 RAISE NO_DATA_FOUND;
351 end if;
352
353 END Update_Row;
354
355
356
357 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
358 BEGIN
359 DELETE FROM per_spinal_point_steps
360 WHERE rowid = chartorowid(X_Rowid);
361
362 if (SQL%NOTFOUND) then
363 RAISE NO_DATA_FOUND;
364 end if;
365 END Delete_Row;
366
367
368
369 procedure del_chks_del(p_step_id IN NUMBER,
370 p_sess IN DATE) is
371 l_exists1 VARCHAR2(1);
372 l_exists2 VARCHAR2(1);
373 --
374 -- Fix for bug 3404138.
375 -- Check that thisstep is used on or after the p_sess date.
376 --
377 cursor c1 is
378 select 'x'
379 from per_spinal_point_placements_f
380 where step_id = p_step_id
381 and p_sess < effective_end_date;
382 --
383 cursor c2 is
384 select 'x'
385 from per_all_assignments_f
386 where special_ceiling_step_id = p_step_id
387 and p_sess < effective_end_date;
388 --
389 begin
390 --
391 hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_del',1);
392 --
393 open c1;
394 --
395 fetch c1 into l_exists1;
396 IF c1%found THEN
397 hr_utility.set_message(801, 'PER_7938_DEL_STEP_PLACE');
398 close c1;
399 hr_utility.raise_error;
400 END IF;
401 --
402 close c1;
403 --
404 hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_del',2);
405 --
406 open c2;
407 --
408 fetch c2 into l_exists2;
409 IF c2%found THEN
410 hr_utility.set_message(801, 'PER_7939_DEL_STEP_ASS');
411 close c2;
412 hr_utility.raise_error;
413 END IF;
414 --
415 close c2;
416 --
417 end del_chks_del;
418
419
420
421 procedure del_chks_zap(p_step_id IN NUMBER) is
422 l_exists VARCHAR2(1);
423 l_exists2 VARCHAR2(1);
424
425 cursor c3 is
426 select 'x'
427 from per_spinal_point_placements_f
428 where step_id = p_step_id;
429 --
430 cursor c4 is
431 select 'x'
432 from per_all_assignments_f
433 where special_ceiling_step_id = p_step_id
434 and special_ceiling_step_id is not null;
435 --
436 begin
437 --
438 hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_zap',1);
439 --
440 open c3;
441 --
442 fetch c3 into l_exists;
443 IF c3%found THEN
444 hr_utility.set_message(801, 'PER_7938_DEL_STEP_PLACE');
445 close c3;
446 hr_utility.raise_error;
447 END IF;
448 --
449 close c3;
450 --
451 hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_zap',2);
452 --
453 open c4;
454 --
455 fetch c4 into l_exists2;
456 IF c4%found THEN
457 hr_utility.set_message(801, 'PER_7939_DEL_STEP_ASS');
458 close c4;
459 hr_utility.raise_error;
460 END IF;
461 --
462 close c4;
463 --
464 end del_chks_zap;
465
466
467
468 procedure chk_unq_step_point(p_gspine_id IN NUMBER,
469 p_spoint_id IN NUMBER,
470 p_step_id IN NUMBER) is
471 l_exists VARCHAR2(1);
472
473 cursor c5 is
474 select 'x'
475 from sys.dual
476 where exists
477 (select null
478 from per_spinal_point_steps_f sp
479 -- per_grade_spines_f gs Bug fix:3648542
480 where sp.grade_spine_id = p_gspine_id
481 and sp.spinal_point_id = p_spoint_id
482 and sp.step_id <> p_step_id);
483 --
484 begin
485 --
486 hr_utility.set_location('per_spinal_point_steps_pkg.chk_unq_step_point',1);
487 --
488 open c5;
489 --
490 fetch c5 into l_exists;
491 IF c5%found THEN
492 hr_utility.set_message(801, 'PER_7936_GRDSPN_POINT_EXISTS');
493 close c5;
494 hr_utility.raise_error;
495 END IF;
496 --
497 close c5;
498 --
499 end chk_unq_step_point;
500
501
502
503 procedure pop_flds(p_d_step IN OUT NOCOPY NUMBER,
504 p_sess IN DATE,
505 p_spoint_id IN NUMBER,
506 p_gspine_id IN NUMBER) is
507
508 cursor c6 is
509 select count(*)
510 from per_spinal_points p1,
511 per_spinal_points p2,
512 per_spinal_point_steps_f s2
513 where s2.spinal_point_id = p2.spinal_point_id
514 and p1.sequence >= p2.sequence
515 and p_sess between
516 s2.effective_start_date and s2.effective_end_date
517 and p1.spinal_point_id = p_spoint_id
518 and s2.grade_spine_id = p_gspine_id
519 group by p1.sequence,p1.spinal_point;
520 --
521 l_count NUMBER;
522 --
523 begin
524 --
525 hr_utility.set_location('per_spinal_point_steps_pkg.pop_flds',1);
526 --
527 open c6;
528 --
529 fetch c6 into l_count;
530 --
531 close c6;
532 --
533 p_d_step := l_count +
534 (hr_grade_scale_api.get_grade_scale_starting_step
535 (p_gspine_id
536 ,p_sess)
537 -1);
538 --
539 end pop_flds;
540
541
542
543
544
545 end PER_SPINAL_POINT_STEPS_PKG;