[Home] [Help]
PACKAGE BODY: APPS.PAY_RATES_PKG
Source
1 PACKAGE BODY PAY_RATES_PKG AS
2 /* $Header: pyrat01t.pkb 115.3 2002/12/17 13:55:52 dsaxby ship $ */
3
4 PROCEDURE INSERT_ROW(P_ROWID IN OUT NOCOPY VARCHAR2,
5 P_RATE_ID IN OUT NOCOPY NUMBER,
6 P_BUSINESS_GROUP_ID NUMBER,
7 P_PARENT_SPINE_ID NUMBER,
8 P_NAME VARCHAR2,
9 P_RATE_TYPE VARCHAR2,
10 P_RATE_UOM VARCHAR2,
11 P_COMMENTS VARCHAR2,
12 P_REQUEST_ID NUMBER,
13 P_PROGRAM_APPLICATION_ID NUMBER,
14 P_PROGRAM_ID NUMBER,
15 P_PROGRAM_UPDATE_DATE DATE,
16 P_ATTRIBUTE_CATEGORY VARCHAR2,
17 P_ATTRIBUTE1 VARCHAR2,
18 P_ATTRIBUTE2 VARCHAR2,
19 P_ATTRIBUTE3 VARCHAR2,
20 P_ATTRIBUTE4 VARCHAR2,
21 P_ATTRIBUTE5 VARCHAR2,
22 P_ATTRIBUTE6 VARCHAR2,
23 P_ATTRIBUTE7 VARCHAR2,
24 P_ATTRIBUTE8 VARCHAR2,
25 P_ATTRIBUTE9 VARCHAR2,
26 P_ATTRIBUTE10 VARCHAR2,
27 P_ATTRIBUTE11 VARCHAR2,
28 P_ATTRIBUTE12 VARCHAR2,
29 P_ATTRIBUTE13 VARCHAR2,
30 P_ATTRIBUTE14 VARCHAR2,
31 P_ATTRIBUTE15 VARCHAR2,
32 P_ATTRIBUTE16 VARCHAR2,
33 P_ATTRIBUTE17 VARCHAR2,
34 P_ATTRIBUTE18 VARCHAR2,
35 P_ATTRIBUTE19 VARCHAR2,
36 P_ATTRIBUTE20 VARCHAR2,
37 P_RATE_BASIS VARCHAR2) IS
38
39 CURSOR c1 IS
40
41 SELECT PAY_RATES_S.NEXTVAL
42 FROM SYS.DUAL;
43
44 CURSOR c2 IS
45
46 SELECT rowid
47 FROM PAY_RATES
48 WHERE RATE_ID = P_RATE_ID;
49
50 BEGIN
51
52
53 OPEN c1;
54 FETCH c1 INTO P_RATE_ID;
55 CLOSE c1;
56
57
58
59 INSERT INTO PAY_RATES (RATE_ID, BUSINESS_GROUP_ID, PARENT_SPINE_ID,
60 NAME, RATE_TYPE, RATE_UOM, COMMENTS, REQUEST_ID,
61 PROGRAM_APPLICATION_ID, PROGRAM_ID,
62 PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY,
63 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
64 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
65 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
66 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16,
67 ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20,
68 RATE_BASIS)
69 VALUES (P_RATE_ID, P_BUSINESS_GROUP_ID, P_PARENT_SPINE_ID,
70 P_NAME, P_RATE_TYPE, P_RATE_UOM, P_COMMENTS, P_REQUEST_ID,
71 P_PROGRAM_APPLICATION_ID, P_PROGRAM_ID,
72 P_PROGRAM_UPDATE_DATE, P_ATTRIBUTE_CATEGORY,
73 P_ATTRIBUTE1, P_ATTRIBUTE2, P_ATTRIBUTE3, P_ATTRIBUTE4,
74 P_ATTRIBUTE5, P_ATTRIBUTE6, P_ATTRIBUTE7, P_ATTRIBUTE8,
75 P_ATTRIBUTE9, P_ATTRIBUTE10, P_ATTRIBUTE11, P_ATTRIBUTE12,
76 P_ATTRIBUTE13, P_ATTRIBUTE14, P_ATTRIBUTE15, P_ATTRIBUTE16,
77 P_ATTRIBUTE17, P_ATTRIBUTE18, P_ATTRIBUTE19, P_ATTRIBUTE20,
78 P_RATE_BASIS);
79
80
81 OPEN c2;
82 FETCH c2 INTO P_ROWID;
83 CLOSE c2;
84
85
86
87 /* calling database package to insert database item */
88
89 hrdyndbi.create_grade_spine_dict(P_RATE_ID);
90
91
92 END INSERT_ROW;
93 --
94
95 PROCEDURE UPDATE_ROW(P_ROWID VARCHAR2,
96 P_RATE_ID NUMBER,
97 P_BUSINESS_GROUP_ID NUMBER,
98 P_PARENT_SPINE_ID NUMBER,
99 P_NAME VARCHAR2,
100 P_RATE_TYPE VARCHAR2,
101 P_RATE_UOM VARCHAR2,
102 P_COMMENTS VARCHAR2,
103 P_REQUEST_ID NUMBER,
104 P_PROGRAM_APPLICATION_ID NUMBER,
105 P_PROGRAM_ID NUMBER,
106 P_PROGRAM_UPDATE_DATE DATE,
107 P_ATTRIBUTE_CATEGORY VARCHAR2,
108 P_ATTRIBUTE1 VARCHAR2,
109 P_ATTRIBUTE2 VARCHAR2,
110 P_ATTRIBUTE3 VARCHAR2,
111 P_ATTRIBUTE4 VARCHAR2,
112 P_ATTRIBUTE5 VARCHAR2,
113 P_ATTRIBUTE6 VARCHAR2,
114 P_ATTRIBUTE7 VARCHAR2,
115 P_ATTRIBUTE8 VARCHAR2,
116 P_ATTRIBUTE9 VARCHAR2,
117 P_ATTRIBUTE10 VARCHAR2,
118 P_ATTRIBUTE11 VARCHAR2,
119 P_ATTRIBUTE12 VARCHAR2,
120 P_ATTRIBUTE13 VARCHAR2,
121 P_ATTRIBUTE14 VARCHAR2,
122 P_ATTRIBUTE15 VARCHAR2,
123 P_ATTRIBUTE16 VARCHAR2,
124 P_ATTRIBUTE17 VARCHAR2,
125 P_ATTRIBUTE18 VARCHAR2,
126 P_ATTRIBUTE19 VARCHAR2,
127 P_ATTRIBUTE20 VARCHAR2,
128 P_RATE_BASIS VARCHAR2) IS
129 BEGIN
130 UPDATE PAY_RATES
131 SET RATE_ID = P_RATE_ID ,
132 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID ,
133 PARENT_SPINE_ID = P_PARENT_SPINE_ID ,
134 NAME = P_NAME ,
135 RATE_TYPE = P_RATE_TYPE ,
136 RATE_UOM = P_RATE_UOM ,
137 COMMENTS = P_COMMENTS ,
138 REQUEST_ID = P_REQUEST_ID ,
139 PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID ,
140 PROGRAM_ID = P_PROGRAM_ID ,
141 PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE ,
142 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY ,
143 ATTRIBUTE1 = P_ATTRIBUTE1 ,
144 ATTRIBUTE2 = P_ATTRIBUTE2 ,
145 ATTRIBUTE3 = P_ATTRIBUTE3 ,
146 ATTRIBUTE4 = P_ATTRIBUTE4 ,
147 ATTRIBUTE5 = P_ATTRIBUTE5 ,
148 ATTRIBUTE6 = P_ATTRIBUTE6 ,
149 ATTRIBUTE7 = P_ATTRIBUTE7 ,
150 ATTRIBUTE8 = P_ATTRIBUTE8 ,
151 ATTRIBUTE9 = P_ATTRIBUTE9 ,
152 ATTRIBUTE10 = P_ATTRIBUTE10 ,
153 ATTRIBUTE11 = P_ATTRIBUTE11 ,
154 ATTRIBUTE12 = P_ATTRIBUTE12 ,
155 ATTRIBUTE13 = P_ATTRIBUTE13 ,
156 ATTRIBUTE14 = P_ATTRIBUTE14 ,
157 ATTRIBUTE15 = P_ATTRIBUTE15 ,
158 ATTRIBUTE16 = P_ATTRIBUTE16 ,
159 ATTRIBUTE17 = P_ATTRIBUTE17 ,
160 ATTRIBUTE18 = P_ATTRIBUTE18 ,
161 ATTRIBUTE19 = P_ATTRIBUTE19 ,
162 ATTRIBUTE20 = P_ATTRIBUTE20 ,
163 RATE_BASIS = P_RATE_BASIS
164 WHERE ROWID = P_ROWID;
165
166 /* calling database package to first delete the existing database item
167 and then call the database package to insert the updated database item */
168
169 hrdyndbi.delete_grade_spine_dict(P_RATE_ID);
170 hrdyndbi.create_grade_spine_dict(P_RATE_ID);
171
172 END UPDATE_ROW;
173
174 --
175 PROCEDURE DELETE_ROW(P_ROWID VARCHAR2,P_RATE_ID NUMBER,P_CHILD VARCHAR2) IS
176
177 BEGIN
178
179 IF P_CHILD = 'Y'
180 THEN
181
182 /* delete any children if they exist */
183 DELETE FROM PAY_GRADE_RULES_F
184 WHERE RATE_ID = P_RATE_ID;
185
186 END IF;
187
188 /* now delete the master */
189 DELETE FROM PAY_RATES WHERE PAY_RATES.ROWID = P_ROWID;
190
191 /* calling database package to delete database item */
192
193 hrdyndbi.delete_grade_spine_dict(P_RATE_ID);
194
195 END DELETE_ROW;
196
197
198 --
199 PROCEDURE LOCK_ROW(P_ROWID VARCHAR2,
200 P_RATE_ID NUMBER,
201 P_BUSINESS_GROUP_ID NUMBER,
202 P_PARENT_SPINE_ID NUMBER,
203 P_NAME VARCHAR2,
204 P_RATE_TYPE VARCHAR2,
205 P_RATE_UOM VARCHAR2,
206 P_COMMENTS VARCHAR2,
207 P_REQUEST_ID NUMBER,
208 P_PROGRAM_APPLICATION_ID NUMBER,
209 P_PROGRAM_ID NUMBER,
210 P_PROGRAM_UPDATE_DATE DATE,
211 P_ATTRIBUTE_CATEGORY VARCHAR2,
212 P_ATTRIBUTE1 VARCHAR2,
213 P_ATTRIBUTE2 VARCHAR2,
214 P_ATTRIBUTE3 VARCHAR2,
215 P_ATTRIBUTE4 VARCHAR2,
216 P_ATTRIBUTE5 VARCHAR2,
217 P_ATTRIBUTE6 VARCHAR2,
218 P_ATTRIBUTE7 VARCHAR2,
219 P_ATTRIBUTE8 VARCHAR2,
220 P_ATTRIBUTE9 VARCHAR2,
221 P_ATTRIBUTE10 VARCHAR2,
222 P_ATTRIBUTE11 VARCHAR2,
223 P_ATTRIBUTE12 VARCHAR2,
224 P_ATTRIBUTE13 VARCHAR2,
225 P_ATTRIBUTE14 VARCHAR2,
226 P_ATTRIBUTE15 VARCHAR2,
227 P_ATTRIBUTE16 VARCHAR2,
228 P_ATTRIBUTE17 VARCHAR2,
229 P_ATTRIBUTE18 VARCHAR2,
230 P_ATTRIBUTE19 VARCHAR2,
231 P_ATTRIBUTE20 VARCHAR2,
232 P_CHILD_EXIST VARCHAR2,
233 P_MODE VARCHAR2,
234 P_RATE_BASIS VARCHAR2) IS
235
236
237 CURSOR C IS SELECT * FROM PAY_RATES WHERE ROWID = P_ROWID
238 FOR UPDATE OF RATE_ID NOWAIT;
239
240 CURSOR C2 IS SELECT * FROM PAY_GRADE_RULES_F WHERE RATE_ID = P_RATE_ID
241 FOR UPDATE OF GRADE_RULE_ID NOWAIT;
242
243 RECINFO C%ROWTYPE;
244 BEGIN
245
246 IF (P_CHILD_EXIST = 'Y') AND (P_MODE = 'D')
247 THEN
248
249 /* LOCK ALL THE CHILDREN RECORDS */
250 FOR DUMMY IN C2
251 LOOP
252 NULL;
253 END LOOP;
254
255
256 END IF;
257
258 OPEN C;
259 FETCH C INTO RECINFO;
260 CLOSE C;
261
262 RECINFO.name := rtrim(RECINFO.name);
263 RECINFO.rate_type := rtrim(RECINFO.rate_type);
264 RECINFO.rate_uom := rtrim(RECINFO.rate_uom);
265 RECINFO.comments := rtrim(RECINFO.comments);
266 RECINFO.attribute_category := rtrim(RECINFO.attribute_category);
267 RECINFO.attribute1 := rtrim(RECINFO.attribute1);
268 RECINFO.attribute2 := rtrim(RECINFO.attribute2);
269 RECINFO.attribute3 := rtrim(RECINFO.attribute3);
270 RECINFO.attribute4 := rtrim(RECINFO.attribute4);
271 RECINFO.attribute5 := rtrim(RECINFO.attribute5);
272 RECINFO.attribute6 := rtrim(RECINFO.attribute6);
273 RECINFO.attribute7 := rtrim(RECINFO.attribute7);
274 RECINFO.attribute8 := rtrim(RECINFO.attribute8);
275 RECINFO.attribute9 := rtrim(RECINFO.attribute9);
276 RECINFO.attribute10 := rtrim(RECINFO.attribute10);
277 RECINFO.attribute11 := rtrim(RECINFO.attribute11);
278 RECINFO.attribute12 := rtrim(RECINFO.attribute12);
279 RECINFO.attribute13 := rtrim(RECINFO.attribute13);
280 RECINFO.attribute14 := rtrim(RECINFO.attribute14);
281 RECINFO.attribute15 := rtrim(RECINFO.attribute15);
282 RECINFO.attribute16 := rtrim(RECINFO.attribute16);
283 RECINFO.attribute17 := rtrim(RECINFO.attribute17);
284 RECINFO.attribute18 := rtrim(RECINFO.attribute18);
285 RECINFO.attribute19 := rtrim(RECINFO.attribute19);
286 RECINFO.attribute20 := rtrim(RECINFO.attribute20);
287 RECINFO.rate_basis := rtrim(RECINFO.rate_basis);
288
289 IF (((RECINFO.RATE_ID = P_RATE_ID)
290 OR(RECINFO.RATE_ID IS NULL AND P_RATE_ID IS NULL))
291 AND((RECINFO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
292 OR(RECINFO.BUSINESS_GROUP_ID IS NULL AND P_BUSINESS_GROUP_ID IS NULL))
293 AND((RECINFO.PARENT_SPINE_ID = P_PARENT_SPINE_ID)
294 OR(RECINFO.PARENT_SPINE_ID IS NULL AND P_PARENT_SPINE_ID IS NULL))
295 AND((RECINFO.NAME = P_NAME)
296 OR(RECINFO.NAME IS NULL AND P_NAME IS NULL))
297 AND((RECINFO.RATE_TYPE = P_RATE_TYPE)
298 OR(RECINFO.RATE_TYPE IS NULL AND P_RATE_TYPE IS NULL))
299 AND((RECINFO.RATE_UOM = P_RATE_UOM)
300 OR(RECINFO.RATE_UOM IS NULL AND P_RATE_UOM IS NULL))
301 AND((RECINFO.COMMENTS = P_COMMENTS)
302 OR(RECINFO.COMMENTS IS NULL AND P_COMMENTS IS NULL))
303 AND((RECINFO.REQUEST_ID = P_REQUEST_ID)
304 OR(RECINFO.REQUEST_ID IS NULL AND P_REQUEST_ID IS NULL))
305 AND((RECINFO.PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID)
306 OR(RECINFO.PROGRAM_APPLICATION_ID IS NULL AND P_PROGRAM_APPLICATION_ID IS NULL))
307 AND((RECINFO.PROGRAM_ID = P_PROGRAM_ID)
308 OR(RECINFO.PROGRAM_ID IS NULL AND P_PROGRAM_ID IS NULL))
309 AND((RECINFO.PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE)
310 OR(RECINFO.PROGRAM_UPDATE_DATE IS NULL AND P_PROGRAM_UPDATE_DATE IS NULL))
311 AND((RECINFO.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
312 OR(RECINFO.ATTRIBUTE_CATEGORY IS NULL AND P_ATTRIBUTE_CATEGORY IS NULL))
313 AND((RECINFO.ATTRIBUTE1 = P_ATTRIBUTE1)
314 OR(RECINFO.ATTRIBUTE1 IS NULL AND P_ATTRIBUTE1 IS NULL))
315 AND((RECINFO.ATTRIBUTE2 = P_ATTRIBUTE2)
316 OR(RECINFO.ATTRIBUTE2 IS NULL AND P_ATTRIBUTE2 IS NULL))
317 AND((RECINFO.ATTRIBUTE3 = P_ATTRIBUTE3)
318 OR(RECINFO.ATTRIBUTE3 IS NULL AND P_ATTRIBUTE3 IS NULL))
319 AND((RECINFO.ATTRIBUTE4 = P_ATTRIBUTE4)
320 OR(RECINFO.ATTRIBUTE4 IS NULL AND P_ATTRIBUTE4 IS NULL))
321 AND((RECINFO.ATTRIBUTE5 = P_ATTRIBUTE5)
322 OR(RECINFO.ATTRIBUTE5 IS NULL AND P_ATTRIBUTE5 IS NULL))
323 AND((RECINFO.ATTRIBUTE6 = P_ATTRIBUTE6)
324 OR(RECINFO.ATTRIBUTE6 IS NULL AND P_ATTRIBUTE6 IS NULL))
325 AND((RECINFO.ATTRIBUTE7 = P_ATTRIBUTE7)
326 OR(RECINFO.ATTRIBUTE7 IS NULL AND P_ATTRIBUTE7 IS NULL))
327 AND((RECINFO.ATTRIBUTE8 = P_ATTRIBUTE8)
328 OR(RECINFO.ATTRIBUTE8 IS NULL AND P_ATTRIBUTE8 IS NULL))
329 AND((RECINFO.ATTRIBUTE9 = P_ATTRIBUTE9)
330 OR(RECINFO.ATTRIBUTE9 IS NULL AND P_ATTRIBUTE9 IS NULL))
331 AND((RECINFO.ATTRIBUTE10 = P_ATTRIBUTE10)
332 OR(RECINFO.ATTRIBUTE10 IS NULL AND P_ATTRIBUTE10 IS NULL))
333 AND((RECINFO.ATTRIBUTE11 = P_ATTRIBUTE11)
334 OR(RECINFO.ATTRIBUTE11 IS NULL AND P_ATTRIBUTE11 IS NULL))
335 AND((RECINFO.ATTRIBUTE12 = P_ATTRIBUTE13)
336 OR(RECINFO.ATTRIBUTE12 IS NULL AND P_ATTRIBUTE12 IS NULL))
337 AND((RECINFO.ATTRIBUTE13 = P_ATTRIBUTE13)
338 OR(RECINFO.ATTRIBUTE13 IS NULL AND P_ATTRIBUTE13 IS NULL))
339 AND((RECINFO.ATTRIBUTE14 = P_ATTRIBUTE14)
340 OR(RECINFO.ATTRIBUTE14 IS NULL AND P_ATTRIBUTE14 IS NULL))
341 AND((RECINFO.ATTRIBUTE15 = P_ATTRIBUTE15)
342 OR(RECINFO.ATTRIBUTE15 IS NULL AND P_ATTRIBUTE15 IS NULL))
343 AND((RECINFO.ATTRIBUTE16 = P_ATTRIBUTE16)
344 OR(RECINFO.ATTRIBUTE16 IS NULL AND P_ATTRIBUTE16 IS NULL))
345 AND((RECINFO.ATTRIBUTE17 = P_ATTRIBUTE17)
346 OR(RECINFO.ATTRIBUTE17 IS NULL AND P_ATTRIBUTE17 IS NULL))
347 AND((RECINFO.ATTRIBUTE18 = P_ATTRIBUTE18)
348 OR(RECINFO.ATTRIBUTE18 IS NULL AND P_ATTRIBUTE18 IS NULL))
349 AND((RECINFO.ATTRIBUTE19 = P_ATTRIBUTE19)
350 OR(RECINFO.ATTRIBUTE19 IS NULL AND P_ATTRIBUTE19 IS NULL))
351 AND((RECINFO.ATTRIBUTE20 = P_ATTRIBUTE20)
352 OR(RECINFO.ATTRIBUTE20 IS NULL AND P_ATTRIBUTE20 IS NULL))
353 AND((RECINFO.RATE_BASIS = P_RATE_BASIS)
354 OR(RECINFO.RATE_BASIS IS NULL AND P_RATE_BASIS IS NULL)))
355 THEN
356 RETURN;
357 ELSE
358 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
359 APP_EXCEPTION.RAISE_EXCEPTION;
360
361 END IF;
362 END LOCK_ROW;
363
364 END PAY_RATES_PKG;