[Home] [Help]
PACKAGE BODY: APPS.PER_VALID_GRADES_PKG2
Source
1 PACKAGE BODY PER_VALID_GRADES_PKG2 as
2 /* $Header: pevgr02t.pkb 120.1 2005/10/03 12:04:46 hsajja noship $ */
3
4 Procedure check_duplicate_valid_grade (X_Rowid VARCHAR2,
5 X_Business_Group_Id NUMBER,
6 X_Position_Id NUMBER,
7 X_Grade_Id NUMBER,
8 X_Date_from DATE,
9 X_Date_to DATE,
10 x_end_of_time DATE) is
11
12 l_exists boolean;
13
14 l_dummy varchar2(1);
15
16 cursor csr_exists is
17 SELECT '1'
18 FROM PER_VALID_GRADES VG
19 WHERE (ROWID <> X_Rowid
20 OR X_Rowid IS NULL)
21 AND VG.business_group_id + 0 = X_Business_Group_Id
22 AND VG.POSITION_ID = X_Position_Id
23 AND VG.GRADE_ID = X_Grade_Id
24 AND (nvl(vg.date_to,x_end_of_time) >= X_Date_from
25 and vg.date_from <= nvl(X_Date_to,x_end_of_time));
26 begin
27 open csr_exists;
28 fetch csr_exists into l_dummy;
29 l_exists := csr_exists%found;
30 close csr_exists;
31 if l_exists then
32 hr_utility.set_message(800,'PER_POS_GRD_DATES_EXISTS');
33 hr_utility.raise_error;
34 end if;
35
36 end check_duplicate_valid_grade;
37
38 procedure check_valid_grade_date_to (x_end_of_time date,
39 X_Date_To date,
40 x_pst1_date_end date,
41 x_grade_id number) is
42
43 l_exists boolean;
44 l_date_to date;
45
46 cursor csr_exists is
47 select g.date_to
48 from per_grades g
49 where g.grade_id = x_grade_id
50 and nvl(X_Date_To,x_end_of_time) >
51 nvl(g.date_to, x_end_of_time);
52
53 begin
54
55
56 -- valid grade date_to can not exceed position date_to.
57
58 if nvl(X_Date_To,x_end_of_time) > nvl(x_pst1_date_end,x_end_of_time) then
59 hr_utility.set_message(801,'PER_7826_DEF_GRD_JOB_END_JOB');
60 hr_utility.set_message_token('DATE',x_pst1_date_end);
61 hr_utility.raise_error;
62 end if;
63
64 -- valid grade date_to can not exceed grade date_to.
65
66 open csr_exists;
67 fetch csr_exists into l_date_to;
68 l_exists := csr_exists%found;
69 close csr_exists;
70 if l_exists then
71 hr_utility.set_message(801,'PER_7872_DEF_GRD_POS_END_POS');
72 hr_utility.set_message_token('DATE',l_date_to);
73 hr_utility.raise_error;
74 end if;
75
76 end check_valid_grade_date_to;
77
78
79 procedure check_valid_grade_date_from (x_Date_From date,
80 x_pst1_date_effective date,
81 x_grade_id number) is
82
83 l_exists boolean;
84 l_date_from date;
85
86 cursor csr_exists is
87 select g.date_from
88 from per_grades g
89 where g.grade_id = x_grade_id
90 and x_Date_from < g.date_from;
91
92 begin
93
94 if x_Date_From >= x_pst1_date_effective then
95 open csr_exists;
96 fetch csr_exists into l_date_from;
97 l_exists := csr_exists%found;
98 close csr_exists;
99
100 -- valid grade date_from can not preceed grade date_from.
101
102 if l_exists then
103 hr_utility.set_message(801,'PER_7871_DEF_POS_GRD_START_GRD');
104 hr_utility.set_message_token('DATE',l_date_from);
105 hr_utility.raise_error;
106 end if;
107
108 -- valid grade date_from can not preceed position date_from.
109
110 else
111 hr_utility.set_message(801,'PER_7870_DEF_POS_GRD_START_POS');
112 hr_utility.set_message_token('DATE',x_pst1_date_effective);
113 hr_utility.raise_error;
114 end if;
115
116 end check_valid_grade_date_from;
117
118 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
119 X_Valid_Grade_Id IN OUT NOCOPY NUMBER,
120 X_Business_Group_Id NUMBER,
121 X_Grade_Id NUMBER,
122 X_Date_From DATE,
123 X_Comments VARCHAR2,
124 X_Date_To DATE,
125 X_Job_Id NUMBER,
126 X_Position_Id NUMBER,
127 X_Attribute_Category VARCHAR2,
128 X_Attribute1 VARCHAR2,
129 X_Attribute2 VARCHAR2,
130 X_Attribute3 VARCHAR2,
131 X_Attribute4 VARCHAR2,
132 X_Attribute5 VARCHAR2,
133 X_Attribute6 VARCHAR2,
134 X_Attribute7 VARCHAR2,
135 X_Attribute8 VARCHAR2,
136 X_Attribute9 VARCHAR2,
137 X_Attribute10 VARCHAR2,
138 X_Attribute11 VARCHAR2,
139 X_Attribute12 VARCHAR2,
140 X_Attribute13 VARCHAR2,
141 X_Attribute14 VARCHAR2,
142 X_Attribute15 VARCHAR2,
143 X_Attribute16 VARCHAR2,
144 X_Attribute17 VARCHAR2,
145 X_Attribute18 VARCHAR2,
146 X_Attribute19 VARCHAR2,
147 X_Attribute20 VARCHAR2,
148 x_end_of_time DATE,
149 x_pst1_date_end DATE,
150 x_pst1_date_effective DATE
151 ) IS
152 CURSOR C IS SELECT rowid FROM PER_VALID_GRADES
153
154 WHERE valid_grade_id = X_Valid_Grade_Id;
155
156
157
158
159
160 CURSOR C2 IS SELECT per_valid_grades_s.nextval FROM sys.dual;
161 BEGIN
162
163 -- date to must be greater than date from.
164
165 if x_date_from > nvl(x_date_to,x_end_of_time) then
166 hr_utility.set_message(801,'HR_6021_ALL_START_END_DATE');
167 hr_utility.raise_error;
168 end if;
169
170 check_duplicate_valid_grade(X_Rowid ,
171 X_Business_Group_Id ,
172 X_Position_Id ,
173 X_Grade_Id,
174 X_Date_From,
175 X_Date_To,
176 x_end_of_time );
177
178 check_valid_grade_date_to(x_end_of_time ,
179 X_Date_To ,
180 x_pst1_date_end ,
181 x_grade_id );
182
183 check_valid_grade_date_from(x_Date_From ,
184 x_pst1_date_effective ,
185 x_grade_id);
186
187 if (X_Valid_Grade_Id is NULL) then
188 OPEN C2;
189 FETCH C2 INTO X_Valid_Grade_Id;
190 CLOSE C2;
191 end if;
192 INSERT INTO PER_VALID_GRADES(
193 valid_grade_id,
194 business_group_id,
195 grade_id,
196 date_from,
197 comments,
198 date_to,
199 job_id,
200 position_id,
201 attribute_category,
202 attribute1,
203 attribute2,
204 attribute3,
205 attribute4,
206 attribute5,
207 attribute6,
208 attribute7,
209 attribute8,
210 attribute9,
211 attribute10,
212 attribute11,
213 attribute12,
214 attribute13,
215 attribute14,
216 attribute15,
217 attribute16,
218 attribute17,
219 attribute18,
220 attribute19,
221 attribute20
222 ) VALUES (
223 X_Valid_Grade_Id,
224 X_Business_Group_Id,
225 X_Grade_Id,
226 X_Date_From,
227 X_Comments,
228 X_Date_To,
229 X_Job_Id,
230 X_Position_Id,
231 X_Attribute_Category,
232 X_Attribute1,
233 X_Attribute2,
234 X_Attribute3,
235 X_Attribute4,
236 X_Attribute5,
237 X_Attribute6,
238 X_Attribute7,
239 X_Attribute8,
240 X_Attribute9,
241 X_Attribute10,
242 X_Attribute11,
243 X_Attribute12,
244 X_Attribute13,
245 X_Attribute14,
246 X_Attribute15,
247 X_Attribute16,
248 X_Attribute17,
249 X_Attribute18,
250 X_Attribute19,
251 X_Attribute20
252 );
253
254 OPEN C;
255 FETCH C INTO X_Rowid;
256 if (C%NOTFOUND) then
257 CLOSE C;
258 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
259 hr_utility.set_message_token('PROCEDURE','Insert_row');
260 hr_utility.set_message_token('STEP','1');
261 hr_utility.raise_error;
262 end if;
263 CLOSE C;
264 END Insert_Row;
265 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
266
267 X_Valid_Grade_Id NUMBER,
268 X_Business_Group_Id NUMBER,
269 X_Grade_Id NUMBER,
270 X_Date_From DATE,
271 X_Comments VARCHAR2,
272 X_Date_To DATE,
273 X_Job_Id NUMBER,
274 X_Position_Id NUMBER,
275 X_Attribute_Category VARCHAR2,
276 X_Attribute1 VARCHAR2,
277 X_Attribute2 VARCHAR2,
278 X_Attribute3 VARCHAR2,
279 X_Attribute4 VARCHAR2,
280 X_Attribute5 VARCHAR2,
281 X_Attribute6 VARCHAR2,
282 X_Attribute7 VARCHAR2,
283 X_Attribute8 VARCHAR2,
284 X_Attribute9 VARCHAR2,
285 X_Attribute10 VARCHAR2,
286 X_Attribute11 VARCHAR2,
287 X_Attribute12 VARCHAR2,
288 X_Attribute13 VARCHAR2,
289 X_Attribute14 VARCHAR2,
290 X_Attribute15 VARCHAR2,
291 X_Attribute16 VARCHAR2,
292 X_Attribute17 VARCHAR2,
293 X_Attribute18 VARCHAR2,
294 X_Attribute19 VARCHAR2,
295 X_Attribute20 VARCHAR2
296 ) IS
297 CURSOR C IS
298 SELECT *
299 FROM PER_VALID_GRADES
300 WHERE rowid = X_Rowid
301 FOR UPDATE of Valid_Grade_Id NOWAIT;
302 Recinfo C%ROWTYPE;
303 BEGIN
304 OPEN C;
305 FETCH C INTO Recinfo;
306 if (C%NOTFOUND) then
307 CLOSE C;
308 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
309 hr_utility.set_message_token('PROCEDURE','lock_row');
310 hr_utility.set_message_token('STEP','1');
311 hr_utility.raise_error;
312 end if;
313 CLOSE C;
314 --
315 Recinfo.comments := rtrim(Recinfo.comments);
316 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
317 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
318 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
319 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
320 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
321 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
322 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
323 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
324 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
325 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
326 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
327 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
328 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
329 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
330 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
331 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
332 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
333 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
334 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
335 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
336 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
337 --
338 if (
339 ( (Recinfo.valid_grade_id = X_Valid_Grade_Id)
340 OR ( (Recinfo.valid_grade_id IS NULL)
341 AND (X_Valid_Grade_Id IS NULL)))
342 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
343 OR ( (Recinfo.business_group_id IS NULL)
344 AND (X_Business_Group_Id IS NULL)))
345 AND ( (Recinfo.grade_id = X_Grade_Id)
346 OR ( (Recinfo.grade_id IS NULL)
347 AND (X_Grade_Id IS NULL)))
348 AND ( (Recinfo.date_from = X_Date_From)
349 OR ( (Recinfo.date_from IS NULL)
350 AND (X_Date_From IS NULL)))
351 AND ( (Recinfo.comments = X_Comments)
352 OR ( (Recinfo.comments IS NULL)
353 AND (X_Comments IS NULL)))
354 AND ( (Recinfo.date_to = X_Date_To)
355 OR ( (Recinfo.date_to IS NULL)
356 AND (X_Date_To IS NULL)))
357 AND ( (Recinfo.job_id = X_Job_Id)
358 OR ( (Recinfo.job_id IS NULL)
359 AND (X_Job_Id IS NULL)))
360 AND ( (Recinfo.position_id = X_Position_Id)
361 OR ( (Recinfo.position_id IS NULL)
362 AND (X_Position_Id IS NULL)))
363 AND ( (Recinfo.attribute_category = X_Attribute_Category)
364 OR ( (Recinfo.attribute_category IS NULL)
365 AND (X_Attribute_Category IS NULL)))
366 AND ( (Recinfo.attribute1 = X_Attribute1)
367 OR ( (Recinfo.attribute1 IS NULL)
368 AND (X_Attribute1 IS NULL)))
369 AND ( (Recinfo.attribute2 = X_Attribute2)
370 OR ( (Recinfo.attribute2 IS NULL)
371 AND (X_Attribute2 IS NULL)))
372 AND ( (Recinfo.attribute3 = X_Attribute3)
373 OR ( (Recinfo.attribute3 IS NULL)
374 AND (X_Attribute3 IS NULL)))
375 AND ( (Recinfo.attribute4 = X_Attribute4)
376 OR ( (Recinfo.attribute4 IS NULL)
377 AND (X_Attribute4 IS NULL)))
378 AND ( (Recinfo.attribute5 = X_Attribute5)
379 OR ( (Recinfo.attribute5 IS NULL)
380 AND (X_Attribute5 IS NULL)))
381 AND ( (Recinfo.attribute6 = X_Attribute6)
382 OR ( (Recinfo.attribute6 IS NULL)
386 AND (X_Attribute7 IS NULL)))
383 AND (X_Attribute6 IS NULL)))
384 AND ( (Recinfo.attribute7 = X_Attribute7)
385 OR ( (Recinfo.attribute7 IS NULL)
387 AND ( (Recinfo.attribute8 = X_Attribute8)
388 OR ( (Recinfo.attribute8 IS NULL)
389 AND (X_Attribute8 IS NULL)))
390 AND ( (Recinfo.attribute9 = X_Attribute9)
391 OR ( (Recinfo.attribute9 IS NULL)
392 AND (X_Attribute9 IS NULL)))
393 AND ( (Recinfo.attribute10 = X_Attribute10)
394 OR ( (Recinfo.attribute10 IS NULL)
395 AND (X_Attribute10 IS NULL)))
396 AND ( (Recinfo.attribute11 = X_Attribute11)
397 OR ( (Recinfo.attribute11 IS NULL)
398 AND (X_Attribute11 IS NULL)))
399 AND ( (Recinfo.attribute12 = X_Attribute12)
400 OR ( (Recinfo.attribute12 IS NULL)
401 AND (X_Attribute12 IS NULL)))
402 AND ( (Recinfo.attribute13 = X_Attribute13)
403 OR ( (Recinfo.attribute13 IS NULL)
404 AND (X_Attribute13 IS NULL)))
405 AND ( (Recinfo.attribute14 = X_Attribute14)
406 OR ( (Recinfo.attribute14 IS NULL)
407 AND (X_Attribute14 IS NULL)))
408 AND ( (Recinfo.attribute15 = X_Attribute15)
409 OR ( (Recinfo.attribute15 IS NULL)
410 AND (X_Attribute15 IS NULL)))
411 AND ( (Recinfo.attribute16 = X_Attribute16)
412 OR ( (Recinfo.attribute16 IS NULL)
413 AND (X_Attribute16 IS NULL)))
414 AND ( (Recinfo.attribute17 = X_Attribute17)
415 OR ( (Recinfo.attribute17 IS NULL)
416 AND (X_Attribute17 IS NULL)))
417 AND ( (Recinfo.attribute18 = X_Attribute18)
418 OR ( (Recinfo.attribute18 IS NULL)
419 AND (X_Attribute18 IS NULL)))
420 AND ( (Recinfo.attribute19 = X_Attribute19)
421 OR ( (Recinfo.attribute19 IS NULL)
422 AND (X_Attribute19 IS NULL)))
423 AND ( (Recinfo.attribute20 = X_Attribute20)
424 OR ( (Recinfo.attribute20 IS NULL)
425 AND (X_Attribute20 IS NULL)))
426 ) then
427 return;
428 else
429 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
430 APP_EXCEPTION.RAISE_EXCEPTION;
431 end if;
432 END Lock_Row;
433
434 PROCEDURE Update_Row(X_Rowid VARCHAR2,
435 X_Valid_Grade_Id NUMBER,
436 X_Business_Group_Id NUMBER,
437 X_Grade_Id NUMBER,
438 X_Date_From DATE,
439 X_Comments VARCHAR2,
440 X_Date_To DATE,
441 X_Job_Id NUMBER,
442 X_Position_Id NUMBER,
443 X_Attribute_Category VARCHAR2,
444 X_Attribute1 VARCHAR2,
445 X_Attribute2 VARCHAR2,
446 X_Attribute3 VARCHAR2,
447 X_Attribute4 VARCHAR2,
448 X_Attribute5 VARCHAR2,
449 X_Attribute6 VARCHAR2,
450 X_Attribute7 VARCHAR2,
451 X_Attribute8 VARCHAR2,
452 X_Attribute9 VARCHAR2,
453 X_Attribute10 VARCHAR2,
454 X_Attribute11 VARCHAR2,
455 X_Attribute12 VARCHAR2,
456 X_Attribute13 VARCHAR2,
457 X_Attribute14 VARCHAR2,
458 X_Attribute15 VARCHAR2,
459 X_Attribute16 VARCHAR2,
460 X_Attribute17 VARCHAR2,
461 X_Attribute18 VARCHAR2,
462 X_Attribute19 VARCHAR2,
463 X_Attribute20 VARCHAR2,
464 x_end_of_time DATE,
465 x_pst1_date_end DATE,
466 x_pst1_date_effective DATE
467 ) IS
468 BEGIN
469
470 -- date to must be greater than date from.
471
472 if x_date_from > nvl(x_date_to,x_end_of_time) then
473 hr_utility.set_message(801,'HR_6021_ALL_START_END_DATE');
474 hr_utility.raise_error;
475 end if;
476
477 check_duplicate_valid_grade(X_Rowid ,
478 X_Business_Group_Id ,
479 X_Position_Id ,
480 X_Grade_Id,
481 X_Date_From,
482 X_Date_To,
483 x_end_of_time );
484
485 check_valid_grade_date_to(x_end_of_time ,
486 X_Date_To ,
487 x_pst1_date_end ,
491 x_pst1_date_effective ,
488 x_grade_id );
489
490 check_valid_grade_date_from(x_Date_From ,
492 x_grade_id);
493
494 UPDATE PER_VALID_GRADES
495 SET
496
497 valid_grade_id = X_Valid_Grade_Id,
498 business_group_id = X_Business_Group_Id,
499 grade_id = X_Grade_Id,
500 date_from = X_Date_From,
501 comments = X_Comments,
502 date_to = X_Date_To,
503 job_id = X_Job_Id,
504 position_id = X_Position_Id,
505 attribute_category = X_Attribute_Category,
506 attribute1 = X_Attribute1,
507 attribute2 = X_Attribute2,
508 attribute3 = X_Attribute3,
509 attribute4 = X_Attribute4,
510 attribute5 = X_Attribute5,
511 attribute6 = X_Attribute6,
512 attribute7 = X_Attribute7,
513 attribute8 = X_Attribute8,
514 attribute9 = X_Attribute9,
515 attribute10 = X_Attribute10,
516 attribute11 = X_Attribute11,
517 attribute12 = X_Attribute12,
518 attribute13 = X_Attribute13,
519 attribute14 = X_Attribute14,
520 attribute15 = X_Attribute15,
521 attribute16 = X_Attribute16,
522 attribute17 = X_Attribute17,
523 attribute18 = X_Attribute18,
524 attribute19 = X_Attribute19,
525 attribute20 = X_Attribute20
526 WHERE rowid = X_rowid;
527
528 if (SQL%NOTFOUND) then
529 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
530 hr_utility.set_message_token('PROCEDURE','update_row');
531 hr_utility.set_message_token('STEP','1');
532 hr_utility.raise_error;
533 end if;
534
535 END Update_Row;
536
537 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
538 BEGIN
539 DELETE FROM PER_VALID_GRADES
540 WHERE rowid = X_Rowid;
541
542 if (SQL%NOTFOUND) then
543 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
544 hr_utility.set_message_token('PROCEDURE','delete_row');
545 hr_utility.set_message_token('STEP','1');
546 hr_utility.raise_error;
547 end if;
548 END Delete_Row;
549
550 END PER_VALID_GRADES_PKG2;