[Home] [Help]
PACKAGE BODY: APPS.PAY_GRADE_RULES_PKG
Source
1 PACKAGE BODY PAY_GRADE_RULES_PKG AS
2 /* $Header: pygrr01t.pkb 120.0 2005/05/29 05:33:51 appldev noship $ */
3
4
5 PROCEDURE CHECK_UNIQUENESS(P_GRADE_RULE_ID_2 IN OUT NOCOPY NUMBER,
6 P_GRADE_OR_SPINAL_POINT_ID NUMBER,
7 P_RATE_TYPE VARCHAR2,
8 P_RATE_ID NUMBER,
9 P_BUSINESS_GROUP_ID NUMBER,
10 P_MODE VARCHAR2) IS
11 L_DUMMY varchar2(1);
12
13
14 CURSOR G1 IS
15 SELECT NULL
16 FROM PAY_GRADE_RULES_F GR
17 WHERE (GR.GRADE_RULE_ID <> P_GRADE_RULE_ID_2
18 OR P_GRADE_RULE_ID_2 IS NULL)
19 AND GR.GRADE_OR_SPINAL_POINT_ID = P_GRADE_OR_SPINAL_POINT_ID
20 AND GR.RATE_TYPE = P_RATE_TYPE
21 AND GR.RATE_ID = P_RATE_ID
22 AND GR.business_group_id + 0 = P_BUSINESS_GROUP_ID;
23
24 CURSOR c1 IS
25 SELECT PAY_GRADE_RULES_S.NEXTVAL
26 FROM SYS.DUAL;
27
28
29 BEGIN
30
31 OPEN G1;
32 FETCH G1 INTO L_DUMMY;
33
34
35 IF G1%FOUND THEN
36 CLOSE G1;
37 IF P_RATE_TYPE = 'G' THEN
38 HR_UTILITY.SET_MESSAGE('801','PAY_6701_DEF_GRD_RULE_EXISTS');
39 HR_UTILITY.RAISE_ERROR;
40 ELSE
41 HR_UTILITY.SET_MESSAGE('801','PAY_6705_DEF_RATE_POINT_EXISTS');
42 HR_UTILITY.RAISE_ERROR;
43 END IF;
44
45 ELSE
46
47 CLOSE G1;
48
49 IF P_MODE = 'U' THEN
50
51 NULL;
52 ELSE
53
54 /*
55 -- ***TEMP, I had a call to a procedure to get next value of the sequence
56 -- over here but for some reason it gave me an ORA-03113 'End
57 -- of file communicaton channel' error. Replaced the procedure
58 -- call with the following
59 */
60
61 OPEN c1;
62 FETCH c1 INTO P_GRADE_RULE_ID_2;
63 CLOSE c1;
64
65 END IF;
66
67 END IF;
68
69 END CHECK_UNIQUENESS;
70
71 --procedure inserted for use by the spinal point placements form KLS
72
73 procedure pop_flds(p_name IN OUT NOCOPY VARCHAR2,
74 p_rt_id IN NUMBER,
75 p_mean IN OUT NOCOPY VARCHAR2,
76 p_bgroup_id IN NUMBER) is
77
78 cursor c10 is
79 select r.name,
80 u.meaning
81 from hr_lookups u,
82 pay_rates r
83 where u.lookup_type = 'UNITS'
84 and u.lookup_code = r.rate_uom
85 and r.rate_id = p_rt_id
86 and r.business_group_id + 0 = p_bgroup_id;
87 --
88 begin
89 --
90 hr_utility.set_location('pay_grade_rules_pkg.pop_flds',1);
91 --
92 open c10;
93 --
94 fetch c10 into p_name,
95 p_mean;
96 --
97 close c10;
98 --
99 end pop_flds;
100
101
102
103 PROCEDURE INSERT_ROW(P_ROWID IN OUT NOCOPY VARCHAR2,
104 P_GRADE_RULE_ID NUMBER,
105 P_EFFECTIVE_START_DATE DATE,
106 P_EFFECTIVE_END_DATE DATE,
107 P_BUSINESS_GROUP_ID NUMBER,
108 P_RATE_TYPE VARCHAR2,
109 P_GRADE_OR_SPINAL_POINT_ID NUMBER,
110 P_RATE_ID NUMBER,
111 P_MAXIMUM VARCHAR2,
112 P_MID_VALUE VARCHAR2,
113 P_MINIMUM VARCHAR2,
114 P_SEQUENCE NUMBER,
115 P_VALUE VARCHAR2,
116 P_REQUEST_ID NUMBER,
117 P_PROGRAM_APPLICATION_ID NUMBER,
118 P_PROGRAM_ID NUMBER,
119 P_PROGRAM_UPDATE_DATE DATE,
120 P_CURRENCY_CODE VARCHAR2)
121 IS
122
123 -- Fix for bug 2400465
124 P_DATE_TO DATE;
125 P_END_DATE DATE;
126 --
127 CURSOR c2 IS
128 SELECT ROWID
129 FROM PAY_GRADE_RULES_F
130 WHERE GRADE_RULE_ID = P_GRADE_RULE_ID;
131
132 -- Fix for bug 2400465
133 CURSOR GRADE_DATE_TO IS
134 SELECT DATE_TO
135 FROM PER_GRADES P
136 WHERE P.GRADE_ID = P_GRADE_OR_SPINAL_POINT_ID;
137 --
138
139 BEGIN
140
141 -- Fix for bug 2400465
142 OPEN GRADE_DATE_TO;
143 FETCH GRADE_DATE_TO INTO P_DATE_TO;
144 CLOSE GRADE_DATE_TO;
145 IF TRIM(P_DATE_TO) IS NOT NULL THEN
146 P_END_DATE := P_DATE_TO;
147 ELSE
148 P_END_DATE := P_EFFECTIVE_END_DATE;
149 END IF;
150 -- End of fix
151
152
153 INSERT INTO PAY_GRADE_RULES_F(GRADE_RULE_ID, EFFECTIVE_START_DATE,
154 EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
155 RATE_ID, GRADE_OR_SPINAL_POINT_ID, RATE_TYPE,
156 MAXIMUM, MID_VALUE, MINIMUM, SEQUENCE, VALUE,
157 REQUEST_ID, PROGRAM_APPLICATION_ID,
158 PROGRAM_ID, PROGRAM_UPDATE_DATE, CURRENCY_CODE)
159 VALUES (P_GRADE_RULE_ID, P_EFFECTIVE_START_DATE, P_END_DATE,
160 P_BUSINESS_GROUP_ID, P_RATE_ID, P_GRADE_OR_SPINAL_POINT_ID,
161 P_RATE_TYPE, P_MAXIMUM, P_MID_VALUE, P_MINIMUM, P_SEQUENCE,
162 P_VALUE, P_REQUEST_ID, P_PROGRAM_APPLICATION_ID, P_PROGRAM_ID,
163 P_PROGRAM_UPDATE_DATE, P_CURRENCY_CODE);
164 OPEN c2;
165 FETCH c2 INTO P_ROWID;
166 CLOSE c2;
167
168
169 END INSERT_ROW;
170 --
171 PROCEDURE UPDATE_ROW( P_ROWID VARCHAR2,
172 P_GRADE_RULE_ID NUMBER,
173 P_EFFECTIVE_START_DATE DATE,
174 P_EFFECTIVE_END_DATE DATE,
175 P_BUSINESS_GROUP_ID NUMBER,
176 P_RATE_TYPE VARCHAR2,
177 P_GRADE_OR_SPINAL_POINT_ID NUMBER,
178 P_RATE_ID NUMBER,
179 P_MAXIMUM VARCHAR2,
180 P_MID_VALUE VARCHAR2,
181 P_MINIMUM VARCHAR2,
182 P_SEQUENCE NUMBER,
183 P_VALUE VARCHAR2,
184 P_REQUEST_ID NUMBER,
185 P_PROGRAM_APPLICATION_ID NUMBER,
186 P_PROGRAM_ID NUMBER,
187 P_PROGRAM_UPDATE_DATE DATE,
188 P_CURRENCY_CODE VARCHAR2)
189 IS
190 BEGIN
191 UPDATE PAY_GRADE_RULES_F
192 SET GRADE_RULE_ID = P_GRADE_RULE_ID,
193 EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE, EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE,
194 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
195 RATE_TYPE = P_RATE_TYPE,
196 GRADE_OR_SPINAL_POINT_ID = P_GRADE_OR_SPINAL_POINT_ID,
197 RATE_ID = P_RATE_ID,
198 MAXIMUM = P_MAXIMUM,
199 MID_VALUE = P_MID_VALUE,
200 MINIMUM = P_MINIMUM,
201 SEQUENCE = P_SEQUENCE,
202 VALUE = P_VALUE,
203 REQUEST_ID = P_REQUEST_ID,
204 PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID,
205 PROGRAM_ID = P_PROGRAM_ID,
206 PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE,
207 CURRENCY_CODE = P_CURRENCY_CODE
208
209 WHERE ROWID = chartorowid(P_ROWID);
210 END UPDATE_ROW;
211 --
212 PROCEDURE DELETE_ROW(P_ROWID VARCHAR2) IS
213 BEGIN
214 DELETE FROM PAY_GRADE_RULES_F
215 WHERE PAY_GRADE_RULES_F.ROWID = chartorowid(P_ROWID);
216 END DELETE_ROW;
217 --
218 PROCEDURE LOCK_ROW( P_ROWID VARCHAR2,
219 P_GRADE_RULE_ID NUMBER,
220 P_EFFECTIVE_START_DATE DATE,
221 P_EFFECTIVE_END_DATE DATE,
222 P_BUSINESS_GROUP_ID NUMBER,
223 P_RATE_TYPE VARCHAR2,
224 P_GRADE_OR_SPINAL_POINT_ID NUMBER,
225 P_RATE_ID NUMBER,
226 P_MAXIMUM VARCHAR2,
227 P_MID_VALUE VARCHAR2,
228 P_MINIMUM VARCHAR2,
229 P_SEQUENCE NUMBER,
230 P_VALUE VARCHAR2,
231 P_REQUEST_ID NUMBER,
232 P_PROGRAM_APPLICATION_ID NUMBER,
233 P_PROGRAM_ID NUMBER,
234 P_PROGRAM_UPDATE_DATE DATE,
235 P_CURRENCY_CODE VARCHAR2)
236 IS
237 CURSOR C IS SELECT * FROM PAY_GRADE_RULES_F
238 WHERE ROWID = chartorowid(P_ROWID)
239 FOR UPDATE OF GRADE_RULE_ID NOWAIT;
240 RECINFO C%ROWTYPE;
241 BEGIN
242 OPEN C;
243 FETCH C INTO RECINFO;
244
245 CLOSE C;
246
247 RECINFO.rate_type := rtrim(RECINFO.rate_type);
248 RECINFO.maximum := rtrim(RECINFO.maximum);
249 RECINFO.mid_value := rtrim(RECINFO.mid_value);
250 RECINFO.minimum := rtrim(RECINFO.minimum);
251 RECINFO.value := rtrim(RECINFO.value);
252
253 IF (((RECINFO.GRADE_RULE_ID = P_GRADE_RULE_ID)
254 OR (RECINFO.GRADE_RULE_ID IS NULL AND P_GRADE_RULE_ID IS NULL))
255 AND((RECINFO.EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE)
256 OR (RECINFO.EFFECTIVE_START_DATE IS NULL AND P_EFFECTIVE_START_DATE IS NULL))
257 AND((RECINFO.EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE)
258 OR (RECINFO.EFFECTIVE_END_DATE IS NULL AND P_EFFECTIVE_END_DATE IS NULL))
259 AND((RECINFO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
260 OR(RECINFO.BUSINESS_GROUP_ID IS NULL AND P_BUSINESS_GROUP_ID IS NULL))
261 AND((RECINFO.RATE_TYPE = P_RATE_TYPE)
262 OR(RECINFO.RATE_TYPE IS NULL AND P_RATE_TYPE IS NULL))
263 AND((RECINFO.GRADE_OR_SPINAL_POINT_ID = P_GRADE_OR_SPINAL_POINT_ID)
264 OR(RECINFO.GRADE_OR_SPINAL_POINT_ID IS NULL AND P_GRADE_OR_SPINAL_POINT_ID IS NULL))
265 AND((RECINFO.RATE_ID = P_RATE_ID)
266 OR(RECINFO.RATE_ID IS NULL AND P_RATE_ID IS NULL))
267 AND((RECINFO.MAXIMUM = P_MAXIMUM)
268 OR(RECINFO.MAXIMUM IS NULL AND P_MAXIMUM IS NULL))
269 AND((RECINFO.MID_VALUE = P_MID_VALUE)
270 OR(RECINFO.MID_VALUE IS NULL AND P_MID_VALUE IS NULL))
271 AND((RECINFO.MINIMUM = P_MINIMUM )
272 OR(RECINFO.MINIMUM IS NULL AND P_MINIMUM IS NULL))
273 AND((RECINFO.SEQUENCE = P_SEQUENCE)
274 OR(RECINFO.SEQUENCE IS NULL AND P_SEQUENCE IS NULL))
275 AND((RECINFO.VALUE = P_VALUE )
276 OR(RECINFO.VALUE IS NULL AND P_VALUE IS NULL))
277 AND((RECINFO.REQUEST_ID = P_REQUEST_ID)
278 OR(RECINFO.REQUEST_ID IS NULL AND P_REQUEST_ID IS NULL))
279 AND((RECINFO.PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID)
280 OR(RECINFO.PROGRAM_APPLICATION_ID IS NULL AND P_PROGRAM_APPLICATION_ID IS NULL))
281 AND((RECINFO.PROGRAM_ID = P_PROGRAM_ID)
282 OR(RECINFO.PROGRAM_ID IS NULL AND P_PROGRAM_ID IS NULL))
283 AND((RECINFO.PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE)
284 OR(RECINFO.PROGRAM_UPDATE_DATE IS NULL AND P_PROGRAM_UPDATE_DATE IS NULL))
285 AND((RECINFO.CURRENCY_CODE = P_CURRENCY_CODE)
286 OR(RECINFO.CURRENCY_CODE IS NULL AND P_CURRENCY_CODE IS NULL)))
287 THEN
288 RETURN;
289 ELSE
290 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
291 APP_EXCEPTION.RAISE_EXCEPTION;
292 END IF;
293
294 END LOCK_ROW;
295
296 FUNCTION POPULATE_RATE (p_spinal_point_id IN NUMBER, p_effective_date IN DATE)
297
298 RETURN VARCHAR IS
299
300 l_name pay_rates.name%TYPE;
301 l_spinal_point_id number := p_spinal_point_id;
302
303 begin
304
305 select pr.name
306 into l_name
307 from pay_rates pr, pay_grade_rules_f g
308 where pr.rate_id = g.rate_id
309 and g.grade_or_spinal_point_id = l_spinal_point_id
310 and p_effective_date between g.effective_start_date and g.effective_end_date
311 and g.rate_type = 'SP'; -- Fix 3401079
312
313 return l_name;
314
315 exception
316
317 when too_many_rows then
318
319 fnd_message.set_name('PER','HR_289938_MULTIPLE_RATES');
320 l_name := fnd_message.get;
321 return l_name;
322
323 when no_data_found then
324
325 fnd_message.set_name('PER','HR_289939_NO_RATES');
326 l_name := fnd_message.get;
327 return l_name;
328
329 END POPULATE_RATE;
330
331 FUNCTION POPULATE_VALUE(p_spinal_point_id IN NUMBER, p_effective_date IN DATE)
332
333 RETURN VARCHAR IS
334
335 l_value pay_grade_rules_f.value%TYPE;
336 l_rate_uom pay_rates.rate_uom%TYPE;
337 l_currency_code pay_grade_rules_f.currency_code%TYPE;
338 l_format_value varchar2(500);
339 l_spinal_point_id number := p_spinal_point_id;
340
341 begin
342
343 select g.value, r.rate_uom, g.currency_code
344 into l_value, l_rate_uom, l_currency_code
345 from pay_grade_rules_f g, pay_rates r
346 where g.rate_id = r.rate_id
347 and g.grade_or_spinal_point_id = l_spinal_point_id
348 and p_effective_date between g.effective_start_date and g.effective_end_date
349 and g.rate_type = 'SP'; -- Fix 3401079
350
351 hr_chkfmt.changeformat
352 (l_value
353 ,l_format_value
354 ,l_rate_uom
355 ,l_currency_code
356 );
357
358 return l_format_value;
359
360 exception
361
362 when too_many_rows then
363
364 fnd_message.set_name('PER','HR_289930_POINT_VALUE_RETURNED');
365 l_format_value := fnd_message.get;
366 return l_format_value;
367
368 when no_data_found then
369
370 fnd_message.set_name('PER','HR_289937_NO_VALUES');
371 l_format_value := fnd_message.get;
372 return l_format_value;
373
374 END POPULATE_VALUE;
375
376 FUNCTION POPULATE_UNITS(p_spinal_point_id IN NUMBER, p_effective_date IN DATE)
377
378 RETURN VARCHAR IS
379
380 l_meaning hr_lookups.meaning%TYPE;
381 l_bg_id pay_rates.business_group_id%TYPE;
382 l_rate_id pay_rates.rate_id%TYPE;
383 l_spinal_point_id number := p_spinal_point_id;
384
385 begin
386
387 select r.rate_id, r.business_group_id
388 into l_rate_id, l_bg_id
389 from pay_grade_rules_f g, pay_rates r
390 where r.rate_id = g.rate_id
391 and GRADE_OR_SPINAL_POINT_ID = l_spinal_point_id
392 and p_effective_date between g.effective_start_date and g.effective_end_date
393 and g.rate_type = 'SP'; -- Fix 3401079
394
395 select u.meaning
396 into l_meaning
397 from hr_lookups u,
398 pay_rates r
399 where u.lookup_type = 'UNITS'
400 and u.lookup_code = r.rate_uom
401 and r.rate_id = l_rate_id
402 and r.business_group_id + 0 = l_bg_id;
403
404 return l_meaning;
405
406 exception
407
408 when too_many_rows then
409
413
410 fnd_message.set_name('PER','HR_289940_MULTIPLE_UNITS');
411 l_meaning := fnd_message.get;
412 return l_meaning;
414 when no_data_found then
415
416 fnd_message.set_name('PER','HR_289941_NO_UNITS');
417 l_meaning := fnd_message.get;
418 return l_meaning;
419
420 END POPULATE_UNITS;
421
422 -- Bug fix 2651173
423 procedure chk_emp_asgmnt_bef_del(p_spinal_point_id in number,
424 p_parent_spine_id in number,
425 p_effective_date in date,
426 p_point_used out nocopy varchar2) is
427
428 l_exists varchar2(1);
429 -- Start of fix 3774889
430 /*
431 cursor emp_asgmnt_point is
432 select 'x'
433 from per_spinal_points psp,
434 pay_grade_rules_f pgr,
435 per_spinal_point_steps_f psps,
436 per_spinal_point_placements_f pspp
437 where psp.spinal_point_id = pgr.grade_or_spinal_point_id
438 and psp.parent_spine_id = pspp.parent_spine_id
439 and psp.spinal_point_id = psps.spinal_point_id
440 and psps.step_id = pspp.step_id
441 and pgr.rate_type = 'SP'
442 and nvl(p_effective_date, hr_api.g_sot)
443 between pspp.effective_start_date
444 and pspp.effective_end_date
445 and psp.spinal_point_id = p_spinal_point_id
446 and psp.parent_spine_id = p_parent_spine_id;
447 */
448 -- End of fix 3774889
449 cursor emp_asgmnt_point_used is
450 select 'x'
451 from per_spinal_points psp,
452 pay_grade_rules_f pgr,
453 per_spinal_point_steps_f psps,
454 per_spinal_point_placements_f pspp
455 where psp.spinal_point_id = pgr.grade_or_spinal_point_id
456 and psp.parent_spine_id = pspp.parent_spine_id
457 and psp.spinal_point_id = psps.spinal_point_id
458 and psps.step_id = pspp.step_id
459 and pgr.rate_type = 'SP'
460 and psp.spinal_point_id = p_spinal_point_id
461 and psp.parent_spine_id = p_parent_spine_id;
462
463 begin
464 --
465 hr_utility.set_location('per_grade_rules_pkg.chk_emp_asgmnt_bef_del', 1);
466 --
467 -- Start of fix 3774889
468 /*
469 open emp_asgmnt_point;
470 fetch emp_asgmnt_point into l_exists;
471 IF emp_asgmnt_point%found THEN
472 hr_utility.set_message(800, 'PER_289570_ASGMNT_POINT_VALUE');
473 close emp_asgmnt_point;
474 hr_utility.raise_error;
475 END IF;
476 --
477 close emp_asgmnt_point;
478 --
479 */
480 -- End of fix 3774889
481 open emp_asgmnt_point_used;
482 fetch emp_asgmnt_point_used into l_exists;
483 IF emp_asgmnt_point_used%found THEN
484 p_point_used := 'Y';
485 close emp_asgmnt_point_used;
486 ELSE
487 p_point_used := 'N';
488 close emp_asgmnt_point_used;
489 END IF;
490 --
491 end chk_emp_asgmnt_bef_del;
492 --
493
494 END PAY_GRADE_RULES_PKG;