1 PACKAGE BODY PN_PAY_GROUP_RULES_PKG AS
2 --$Header: PNGRPRLB.pls 115.3 2004/02/12 08:33:15 kkhegde noship $
3
4 /*===========================================================================+
5 | PROCEDURE insert_row
6 | DESCRIPTION
7 | inserts row into pn_pay_group_rules
8 | SCOPE - PUBLIC
9 | MODIFICATION HISTORY
10 |
11 | 15-DEC-2003 Kiran o Created.
12 | 03-FEB-2003 Kiran o Removed ORG_ID from insert_row.
13 +===========================================================================*/
14 PROCEDURE insert_row(
15 x_GROUPING_RULE_ID IN OUT NOCOPY NUMBER
16 ,x_NAME IN VARCHAR2
17 ,x_DESCRIPTION IN VARCHAR2
18 ,x_LAST_UPDATE_DATE IN DATE
19 ,x_LAST_UPDATED_BY IN NUMBER
20 ,x_CREATION_DATE IN DATE
21 ,x_CREATED_BY IN NUMBER
22 ,x_LAST_UPDATE_LOGIN IN NUMBER
23 ,x_ATTRIBUTE_CATEGORY IN VARCHAR2
24 ,x_ATTRIBUTE1 IN VARCHAR2
25 ,x_ATTRIBUTE2 IN VARCHAR2
26 ,x_ATTRIBUTE3 IN VARCHAR2
27 ,x_ATTRIBUTE4 IN VARCHAR2
28 ,x_ATTRIBUTE5 IN VARCHAR2
29 ,x_ATTRIBUTE6 IN VARCHAR2
30 ,x_ATTRIBUTE7 IN VARCHAR2
31 ,x_ATTRIBUTE8 IN VARCHAR2
32 ,x_ATTRIBUTE9 IN VARCHAR2
33 ,x_ATTRIBUTE10 IN VARCHAR2
34 ,x_ATTRIBUTE11 IN VARCHAR2
35 ,x_ATTRIBUTE12 IN VARCHAR2
36 ,x_ATTRIBUTE13 IN VARCHAR2
37 ,x_ATTRIBUTE14 IN VARCHAR2
38 ,x_ATTRIBUTE15 IN VARCHAR2) IS
39
40 CURSOR group_rule IS
41 SELECT GROUPING_RULE_ID
42 FROM PN_PAY_GROUP_RULES
43 WHERE GROUPING_RULE_ID = x_GROUPING_RULE_ID;
44
45 BEGIN
46
47 INSERT INTO PN_PAY_GROUP_RULES
48 (GROUPING_RULE_ID
49 ,NAME
50 ,DESCRIPTION
51 ,LAST_UPDATE_DATE
52 ,LAST_UPDATED_BY
53 ,CREATION_DATE
54 ,CREATED_BY
55 ,LAST_UPDATE_LOGIN
56 ,ATTRIBUTE_CATEGORY
57 ,ATTRIBUTE1
58 ,ATTRIBUTE2
59 ,ATTRIBUTE3
60 ,ATTRIBUTE4
61 ,ATTRIBUTE5
62 ,ATTRIBUTE6
63 ,ATTRIBUTE7
64 ,ATTRIBUTE8
65 ,ATTRIBUTE9
66 ,ATTRIBUTE10
67 ,ATTRIBUTE11
68 ,ATTRIBUTE12
69 ,ATTRIBUTE13
70 ,ATTRIBUTE14
71 ,ATTRIBUTE15)
72 VALUES
73 (NVL(x_GROUPING_RULE_ID, PN_PAY_GROUP_RULES_S.NEXTVAL)
74 ,x_NAME
75 ,x_DESCRIPTION
76 ,x_LAST_UPDATE_DATE
77 ,x_LAST_UPDATED_BY
78 ,x_CREATION_DATE
79 ,x_CREATED_BY
80 ,x_LAST_UPDATE_LOGIN
81 ,x_ATTRIBUTE_CATEGORY
82 ,x_ATTRIBUTE1
83 ,x_ATTRIBUTE2
84 ,x_ATTRIBUTE3
85 ,x_ATTRIBUTE4
86 ,x_ATTRIBUTE5
87 ,x_ATTRIBUTE6
88 ,x_ATTRIBUTE7
89 ,x_ATTRIBUTE8
90 ,x_ATTRIBUTE9
91 ,x_ATTRIBUTE10
92 ,x_ATTRIBUTE11
93 ,x_ATTRIBUTE12
94 ,x_ATTRIBUTE13
95 ,x_ATTRIBUTE14
96 ,x_ATTRIBUTE15)
97 RETURNING GROUPING_RULE_ID INTO x_GROUPING_RULE_ID;
98
99 -- Check if a valid row was inserted
100 OPEN group_rule;
101 FETCH group_rule INTO x_GROUPING_RULE_ID;
102
103 IF (group_rule%NOTFOUND) THEN
104 CLOSE group_rule;
105 RAISE NO_DATA_FOUND;
106 END IF;
107
108 CLOSE group_rule;
109
110 EXCEPTION
111 WHEN others THEN
112 RAISE;
113
114 END insert_row;
115
116 /*===========================================================================+
117 | PROCEDURE update_row
118 | DESCRIPTION
119 | updates a row in pn_pay_group_rules
120 | SCOPE - PUBLIC
121 | MODIFICATION HISTORY
122 |
123 | 15-DEC-2003 Kiran o Created.
124 +===========================================================================*/
125 PROCEDURE update_row(
126 x_GROUPING_RULE_ID IN NUMBER
127 ,x_NAME IN VARCHAR2
128 ,x_DESCRIPTION IN VARCHAR2
129 ,x_LAST_UPDATE_DATE IN DATE
130 ,x_LAST_UPDATED_BY IN NUMBER
131 ,x_LAST_UPDATE_LOGIN IN NUMBER
132 ,x_ATTRIBUTE_CATEGORY IN VARCHAR2
133 ,x_ATTRIBUTE1 IN VARCHAR2
134 ,x_ATTRIBUTE2 IN VARCHAR2
135 ,x_ATTRIBUTE3 IN VARCHAR2
136 ,x_ATTRIBUTE4 IN VARCHAR2
137 ,x_ATTRIBUTE5 IN VARCHAR2
138 ,x_ATTRIBUTE6 IN VARCHAR2
139 ,x_ATTRIBUTE7 IN VARCHAR2
140 ,x_ATTRIBUTE8 IN VARCHAR2
141 ,x_ATTRIBUTE9 IN VARCHAR2
142 ,x_ATTRIBUTE10 IN VARCHAR2
143 ,x_ATTRIBUTE11 IN VARCHAR2
144 ,x_ATTRIBUTE12 IN VARCHAR2
145 ,x_ATTRIBUTE13 IN VARCHAR2
146 ,x_ATTRIBUTE14 IN VARCHAR2
147 ,x_ATTRIBUTE15 IN VARCHAR2) IS
148
149 BEGIN
150
151 UPDATE PN_PAY_GROUP_RULES SET
152 NAME = x_NAME
153 ,DESCRIPTION = x_DESCRIPTION
154 ,LAST_UPDATE_DATE = x_LAST_UPDATE_DATE
155 ,LAST_UPDATED_BY = x_LAST_UPDATED_BY
156 ,LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN
157 ,ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY
158 ,ATTRIBUTE1 = x_ATTRIBUTE1
159 ,ATTRIBUTE2 = x_ATTRIBUTE2
160 ,ATTRIBUTE3 = x_ATTRIBUTE3
161 ,ATTRIBUTE4 = x_ATTRIBUTE4
162 ,ATTRIBUTE5 = x_ATTRIBUTE5
163 ,ATTRIBUTE6 = x_ATTRIBUTE6
164 ,ATTRIBUTE7 = x_ATTRIBUTE7
165 ,ATTRIBUTE8 = x_ATTRIBUTE8
166 ,ATTRIBUTE9 = x_ATTRIBUTE9
167 ,ATTRIBUTE10 = x_ATTRIBUTE10
168 ,ATTRIBUTE11 = x_ATTRIBUTE11
169 ,ATTRIBUTE12 = x_ATTRIBUTE12
170 ,ATTRIBUTE13 = x_ATTRIBUTE13
171 ,ATTRIBUTE14 = x_ATTRIBUTE14
172 ,ATTRIBUTE15 = x_ATTRIBUTE15
173 WHERE GROUPING_RULE_ID = x_GROUPING_RULE_ID;
174
175 IF (SQL%NOTFOUND) THEN
176 RAISE NO_DATA_FOUND;
177 END IF;
178
179 EXCEPTION
180 WHEN others THEN
181 RAISE;
182
183 END update_row;
184
185 /*===========================================================================+
186 | PROCEDURE LOCK_ROW_EXCEPTION
187 | DESCRIPTION
188 | Gives the statndard message for the offending column in a LOCK_ROW
189 | raised exception
190 | SCOPE - PUBLIC
191 | ARGUMENTS : IN: p_column_name, p_new_value
192 | RETURNS : None
193 | MODIFICATION HISTORY
194 |
195 | 15-DEC-2003 Kiran o Created. Copied from pn_var_rent_pkg.
196 +===========================================================================*/
197 PROCEDURE LOCK_ROW_EXCEPTION (p_column_name in varchar2,
198 p_new_value in varchar2)
199 is
200 BEGIN
201 PNP_DEBUG_PKG.debug ('PN_PAY_GROUP_RULES_PKG.LOCK_ROW_EXCEPTION (+)');
202
203 fnd_message.set_name ('PN','PN_RECORD_CHANGED');
204 fnd_message.set_token ('COLUMN_NAME',p_column_name);
205 fnd_message.set_token ('NEW_VALUE',p_new_value);
206 app_exception.raise_exception;
207
208 PNP_DEBUG_PKG.debug ('PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION (-)');
209 END lock_row_exception;
210
211 /*===========================================================================+
212 | PROCEDURE lock_row
213 | DESCRIPTION
214 | locks a row in pn_pay_group_rules
215 | SCOPE - PUBLIC
216 | MODIFICATION HISTORY
217 |
218 | 15-DEC-2003 Kiran o Created.
219 +===========================================================================*/
220 PROCEDURE lock_row(
221 x_GROUPING_RULE_ID IN NUMBER
222 ,x_NAME IN VARCHAR2
223 ,x_DESCRIPTION IN VARCHAR2
224 ,x_ATTRIBUTE_CATEGORY IN VARCHAR2
225 ,x_ATTRIBUTE1 IN VARCHAR2
226 ,x_ATTRIBUTE2 IN VARCHAR2
227 ,x_ATTRIBUTE3 IN VARCHAR2
228 ,x_ATTRIBUTE4 IN VARCHAR2
229 ,x_ATTRIBUTE5 IN VARCHAR2
230 ,x_ATTRIBUTE6 IN VARCHAR2
231 ,x_ATTRIBUTE7 IN VARCHAR2
232 ,x_ATTRIBUTE8 IN VARCHAR2
233 ,x_ATTRIBUTE9 IN VARCHAR2
234 ,x_ATTRIBUTE10 IN VARCHAR2
235 ,x_ATTRIBUTE11 IN VARCHAR2
236 ,x_ATTRIBUTE12 IN VARCHAR2
237 ,x_ATTRIBUTE13 IN VARCHAR2
238 ,x_ATTRIBUTE14 IN VARCHAR2
239 ,x_ATTRIBUTE15 IN VARCHAR2) IS
240
241 CURSOR group_rule IS
242 SELECT *
243 FROM PN_PAY_GROUP_RULES
244 WHERE GROUPING_RULE_ID = x_GROUPING_RULE_ID
245 FOR UPDATE OF GROUPING_RULE_ID NOWAIT;
246
247 tlinfo group_rule%ROWTYPE;
248
249 BEGIN
250
251 OPEN group_rule;
252 FETCH group_rule INTO tlinfo;
253
254 IF group_rule%NOTFOUND THEN
255 CLOSE group_rule;
256 RETURN;
257 END IF;
258
259 CLOSE group_rule;
260
261 IF NOT (tlinfo.GROUPING_RULE_ID = x_GROUPING_RULE_ID) THEN
262 lock_row_exception('GROUPING_RULE_ID',tlinfo.GROUPING_RULE_ID);
263 END IF;
264
265 IF NOT (tlinfo.NAME = x_NAME) THEN
266 lock_row_exception('NAME',tlinfo.NAME);
267 END IF;
268
269 IF NOT ((tlinfo.DESCRIPTION = x_DESCRIPTION) OR
270 (tlinfo.DESCRIPTION IS NULL AND x_DESCRIPTION IS NULL)) THEN
271 lock_row_exception('DESCRIPTION',tlinfo.DESCRIPTION);
272 END IF;
273
274 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY) OR
275 (tlinfo.ATTRIBUTE_CATEGORY IS NULL AND x_ATTRIBUTE_CATEGORY IS NULL)) THEN
276 lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
277 END IF;
278
279 IF NOT ((tlinfo.ATTRIBUTE1 = x_ATTRIBUTE1) OR
280 (tlinfo.ATTRIBUTE1 IS NULL AND x_ATTRIBUTE1 IS NULL)) THEN
281 lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
282 END IF;
283
284 IF NOT ((tlinfo.ATTRIBUTE2 = x_ATTRIBUTE2) OR
285 (tlinfo.ATTRIBUTE2 IS NULL AND x_ATTRIBUTE2 IS NULL)) THEN
286 lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
287 END IF;
288
289 IF NOT ((tlinfo.ATTRIBUTE3 = x_ATTRIBUTE3) OR
290 (tlinfo.ATTRIBUTE3 IS NULL AND x_ATTRIBUTE3 IS NULL)) THEN
291 lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
292 END IF;
293
294 IF NOT ((tlinfo.ATTRIBUTE4 = x_ATTRIBUTE4) OR
295 (tlinfo.ATTRIBUTE4 IS NULL AND x_ATTRIBUTE4 IS NULL)) THEN
296 lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
297 END IF;
298
299 IF NOT ((tlinfo.ATTRIBUTE5 = x_ATTRIBUTE5) OR
300 (tlinfo.ATTRIBUTE5 IS NULL AND x_ATTRIBUTE5 IS NULL)) THEN
301 lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
302 END IF;
303
304 IF NOT ((tlinfo.ATTRIBUTE6 = x_ATTRIBUTE6) OR
305 (tlinfo.ATTRIBUTE6 IS NULL AND x_ATTRIBUTE6 IS NULL)) THEN
306 lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
307 END IF;
308
309 IF NOT ((tlinfo.ATTRIBUTE7 = x_ATTRIBUTE7) OR
310 (tlinfo.ATTRIBUTE7 IS NULL AND x_ATTRIBUTE7 IS NULL)) THEN
311 lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
312 END IF;
313
314 IF NOT ((tlinfo.ATTRIBUTE8 = x_ATTRIBUTE8) OR
315 (tlinfo.ATTRIBUTE8 IS NULL AND x_ATTRIBUTE8 IS NULL)) THEN
316 lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
317 END IF;
318
319 IF NOT ((tlinfo.ATTRIBUTE9 = x_ATTRIBUTE9) OR
320 (tlinfo.ATTRIBUTE9 IS NULL AND x_ATTRIBUTE9 IS NULL)) THEN
321 lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
322 END IF;
323
324 IF NOT ((tlinfo.ATTRIBUTE10 = x_ATTRIBUTE10) OR
325 (tlinfo.ATTRIBUTE10 IS NULL AND x_ATTRIBUTE10 IS NULL)) THEN
326 lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
327 END IF;
328
329 IF NOT ((tlinfo.ATTRIBUTE11 = x_ATTRIBUTE11) OR
330 (tlinfo.ATTRIBUTE11 IS NULL AND x_ATTRIBUTE11 IS NULL)) THEN
331 lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
332 END IF;
333
334 IF NOT ((tlinfo.ATTRIBUTE12 = x_ATTRIBUTE12) OR
335 (tlinfo.ATTRIBUTE12 IS NULL AND x_ATTRIBUTE12 IS NULL)) THEN
336 lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
337 END IF;
338
339 IF NOT ((tlinfo.ATTRIBUTE13 = x_ATTRIBUTE13) OR
340 (tlinfo.ATTRIBUTE13 IS NULL AND x_ATTRIBUTE13 IS NULL)) THEN
341 lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
342 END IF;
343
344 IF NOT ((tlinfo.ATTRIBUTE14 = x_ATTRIBUTE14) OR
345 (tlinfo.ATTRIBUTE14 IS NULL AND x_ATTRIBUTE14 IS NULL)) THEN
346 lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
347 END IF;
348
349 IF NOT ((tlinfo.ATTRIBUTE15 = x_ATTRIBUTE15) OR
350 (tlinfo.ATTRIBUTE15 IS NULL AND x_ATTRIBUTE15 IS NULL)) THEN
351 lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
352 END IF;
353
354 EXCEPTION
355 WHEN others THEN
356 RAISE;
357
358 END lock_row;
359
360 /*===========================================================================+
361 | PROCEDURE delete_row
362 | DESCRIPTION
363 | deletes a row from pn_pay_group_rules
364 | SCOPE - PUBLIC
365 | MODIFICATION HISTORY
366 |
367 | 15-DEC-2003 Kiran o Created.
368 +===========================================================================*/
369 PROCEDURE delete_row(
370 x_GROUPING_RULE_ID IN NUMBER) IS
371
372 BEGIN
373
374 DELETE FROM PN_PAY_GROUP_RULES
375 WHERE GROUPING_RULE_ID = x_GROUPING_RULE_ID;
376
377 IF (SQL%NOTFOUND) THEN
378 RAISE NO_DATA_FOUND;
379 END IF;
380
381 EXCEPTION
382 WHEN others THEN
383 RAISE;
384 END delete_row;
385
386 END PN_PAY_GROUP_RULES_PKG;
[Home] [Help]