1 package body PN_VAR_ABAT_DEFAULTS_PKG as
2 /* $Header: PNVRABDB.pls 120.0 2007/10/03 14:27:20 rthumma noship $ */
3 -------------------------------------------------------------------------------
4 -- PROCDURE : INSERT_ROW
5 -- INVOKED FROM : insert_row procedure
6 -- PURPOSE : inserts the row
7 -- HISTORY :
8 -- 12-SEP-06 piagrawa o Created
9 -------------------------------------------------------------------------------
10 procedure INSERT_ROW (
11 X_ROWID in out NOCOPY VARCHAR2
12 ,X_ABATEMENT_ID in out NOCOPY NUMBER
13 ,X_VAR_RENT_ID in NUMBER
14 ,X_START_DATE in DATE
15 ,X_END_DATE in DATE
16 ,X_TYPE_CODE in VARCHAR2
17 ,X_AMOUNT in NUMBER
18 ,X_DESCRIPTION in VARCHAR2
19 ,X_LAST_UPDATE_DATE in DATE
20 ,X_LAST_UPDATED_BY in NUMBER
21 ,X_CREATION_DATE in DATE
22 ,X_CREATED_BY in NUMBER
23 ,X_LAST_UPDATE_LOGIN in NUMBER
24 ,X_COMMENTS in VARCHAR2
25 ,X_ATTRIBUTE_CATEGORY in VARCHAR2
26 ,X_ATTRIBUTE1 in VARCHAR2
27 ,X_ATTRIBUTE2 in VARCHAR2
28 ,X_ATTRIBUTE3 in VARCHAR2
29 ,X_ATTRIBUTE4 in VARCHAR2
30 ,X_ATTRIBUTE5 in VARCHAR2
31 ,X_ATTRIBUTE6 in VARCHAR2
32 ,X_ATTRIBUTE7 in VARCHAR2
33 ,X_ATTRIBUTE8 in VARCHAR2
34 ,X_ATTRIBUTE9 in VARCHAR2
35 ,X_ATTRIBUTE10 in VARCHAR2
36 ,X_ATTRIBUTE11 in VARCHAR2
37 ,X_ATTRIBUTE12 in VARCHAR2
38 ,X_ATTRIBUTE13 in VARCHAR2
39 ,X_ATTRIBUTE14 in VARCHAR2
40 ,X_ATTRIBUTE15 in VARCHAR2
41 ,X_ORG_ID in NUMBER )
42 IS
43 CURSOR C IS
44 SELECT ROWID
45 FROM PN_VAR_ABAT_DEFAULTS_ALL
46 WHERE ABATEMENT_ID = X_ABATEMENT_ID;
47
48 BEGIN
49
50 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.INSERT_ROW (+)');
51
52 -------------------------------------------------------
53 -- Select the nextval for abatement/allowance id
54 -------------------------------------------------------
55
56 IF ( X_ABATEMENT_ID IS NULL) THEN
57 SELECT pn_var_abat_defaults_s.nextval
58 INTO X_ABATEMENT_ID
59 FROM dual;
60 END IF;
61
62 INSERT INTO PN_VAR_ABAT_DEFAULTS_ALL
63 (
64 ABATEMENT_ID
65 ,VAR_RENT_ID
66 ,START_DATE
67 ,END_DATE
68 ,TYPE_CODE
69 ,AMOUNT
70 ,DESCRIPTION
71 ,LAST_UPDATE_DATE
72 ,LAST_UPDATED_BY
73 ,CREATION_DATE
74 ,CREATED_BY
75 ,LAST_UPDATE_LOGIN
76 ,COMMENTS
77 ,ATTRIBUTE_CATEGORY
78 ,ATTRIBUTE1
79 ,ATTRIBUTE2
80 ,ATTRIBUTE3
81 ,ATTRIBUTE4
82 ,ATTRIBUTE5
83 ,ATTRIBUTE6
84 ,ATTRIBUTE7
85 ,ATTRIBUTE8
86 ,ATTRIBUTE9
87 ,ATTRIBUTE10
88 ,ATTRIBUTE11
89 ,ATTRIBUTE12
90 ,ATTRIBUTE13
91 ,ATTRIBUTE14
92 ,ATTRIBUTE15
93 ,ORG_ID
94 )
95 VALUES
96 (
97 X_ABATEMENT_ID
98 ,X_VAR_RENT_ID
99 ,X_START_DATE
100 ,X_END_DATE
101 ,X_TYPE_CODE
102 ,X_AMOUNT
103 ,X_DESCRIPTION
104 ,X_LAST_UPDATE_DATE
105 ,X_LAST_UPDATED_BY
106 ,X_CREATION_DATE
107 ,X_CREATED_BY
108 ,X_LAST_UPDATE_LOGIN
109 ,X_COMMENTS
110 ,X_ATTRIBUTE_CATEGORY
111 ,X_ATTRIBUTE1
112 ,X_ATTRIBUTE2
113 ,X_ATTRIBUTE3
114 ,X_ATTRIBUTE4
115 ,X_ATTRIBUTE5
116 ,X_ATTRIBUTE6
117 ,X_ATTRIBUTE7
118 ,X_ATTRIBUTE8
119 ,X_ATTRIBUTE9
120 ,X_ATTRIBUTE10
121 ,X_ATTRIBUTE11
122 ,X_ATTRIBUTE12
123 ,X_ATTRIBUTE13
124 ,X_ATTRIBUTE14
125 ,X_ATTRIBUTE15
126 ,X_ORG_ID
127 ) ;
128
129 OPEN c;
130 FETCH c INTO X_ROWID;
131 IF (c%NOTFOUND) THEN
132 CLOSE c;
133 RAISE NO_DATA_FOUND;
134 END IF;
135 CLOSE c;
136
137 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.INSERT_ROW (-)');
138
139 END INSERT_ROW;
140
141 -------------------------------------------------------------------------------
142 -- PROCDURE : LOCK_ROW
143 -- INVOKED FROM : LOCK_ROW procedure
144 -- PURPOSE : locks the row
145 -- HISTORY :
146 -- 12-SEP-06 piagrawa o Created
147 -------------------------------------------------------------------------------
148 procedure LOCK_ROW (
149 X_ABATEMENT_ID in NUMBER
150 ,X_VAR_RENT_ID in NUMBER
151 ,X_START_DATE in DATE
152 ,X_END_DATE in DATE
153 ,X_TYPE_CODE in VARCHAR2
154 ,X_AMOUNT in NUMBER
155 ,X_DESCRIPTION in VARCHAR2
156 ,X_LAST_UPDATE_DATE in DATE
157 ,X_LAST_UPDATED_BY in NUMBER
158 ,X_CREATION_DATE in DATE
159 ,X_CREATED_BY in NUMBER
160 ,X_LAST_UPDATE_LOGIN in NUMBER
161 ,X_COMMENTS in VARCHAR2
162 ,X_ATTRIBUTE_CATEGORY in VARCHAR2
163 ,X_ATTRIBUTE1 in VARCHAR2
164 ,X_ATTRIBUTE2 in VARCHAR2
165 ,X_ATTRIBUTE3 in VARCHAR2
166 ,X_ATTRIBUTE4 in VARCHAR2
167 ,X_ATTRIBUTE5 in VARCHAR2
168 ,X_ATTRIBUTE6 in VARCHAR2
169 ,X_ATTRIBUTE7 in VARCHAR2
170 ,X_ATTRIBUTE8 in VARCHAR2
171 ,X_ATTRIBUTE9 in VARCHAR2
172 ,X_ATTRIBUTE10 in VARCHAR2
173 ,X_ATTRIBUTE11 in VARCHAR2
174 ,X_ATTRIBUTE12 in VARCHAR2
175 ,X_ATTRIBUTE13 in VARCHAR2
176 ,X_ATTRIBUTE14 in VARCHAR2
177 ,X_ATTRIBUTE15 in VARCHAR2
178 ,X_ORG_ID in NUMBER)
179 IS
180
181 CURSOR c1 IS
182 SELECT *
183 FROM PN_VAR_ABAT_DEFAULTS_ALL
184 WHERE ABATEMENT_ID = X_ABATEMENT_ID
185 FOR UPDATE OF ABATEMENT_ID NOWAIT;
186
187 tlinfo c1%rowtype;
188
189 BEGIN
190
191 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.LOCK_ROW (+)');
192
193 OPEN c1;
194 FETCH c1 INTO tlinfo;
195 IF (c1%notfound) THEN
196 CLOSE c1;
197 return;
198 END IF;
199 CLOSE c1;
200
201 if (tlinfo.ABATEMENT_ID = X_ABATEMENT_ID) then
202 null;
203 else
204 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ABATEMENT_ID', to_char(tlinfo.ABATEMENT_ID));
205 end if;
206
207 if ((tlinfo.VAR_RENT_ID = X_VAR_RENT_ID)
208 OR ((tlinfo.VAR_RENT_ID is null) AND (X_VAR_RENT_ID is null))) then
209 null;
210 else
211 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VAR_RENT_ID', to_char(tlinfo.VAR_RENT_ID));
212 end if;
213
214 if ((tlinfo.START_DATE = X_START_DATE)
215 OR ((tlinfo.START_DATE is null) AND (X_START_DATE is null))) then
216 null;
217 else
218 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('START_DATE', to_char(tlinfo.START_DATE));
219 end if;
220
221 if ((tlinfo.END_DATE = X_END_DATE)
222 OR ((tlinfo.END_DATE is null) AND (X_END_DATE is null))) then
223 null;
224 else
225 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('END_DATE', to_char(tlinfo.END_DATE));
226 end if;
227
228 if ((tlinfo.TYPE_CODE = X_TYPE_CODE)
229 OR ((tlinfo.TYPE_CODE is null) AND (X_TYPE_CODE is null))) then
230 null;
231 else
232 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('TYPE_CODE', tlinfo.TYPE_CODE);
233 end if;
234
235 if ((tlinfo.AMOUNT = X_AMOUNT)
236 OR ((tlinfo.AMOUNT is null) AND (X_AMOUNT is null))) then
237 null;
238 else
239 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('AMOUNT', to_char(tlinfo.AMOUNT));
240 end if;
241 if ((tlinfo.DESCRIPTION = X_DESCRIPTION)
242 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null))) then
243 null;
244 else
245 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('DESCRIPTION', to_char(tlinfo.DESCRIPTION));
246 end if;
247
248 if ((tlinfo.COMMENTS = X_COMMENTS)
249 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null))) then
250 null;
251 else
252 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('COMMENTS', to_char(tlinfo.COMMENTS));
253 end if;
254
255 if ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
256 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) then
257 null;
258 else
259 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
260 end if;
261 if ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
262 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) then
263 null;
264 else
265 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
266 end if;
267 if ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
268 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) then
269 null;
270 else
271 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
272 end if;
273 if ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
274 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) then
275 null;
276 else
277 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
278 end if;
279 if ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
280 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) then
281 null;
282 else
283 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
284 end if;
285 if ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
286 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) then
287 null;
288 else
289 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
290 end if;
291 if ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
292 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) then
293 null;
294 else
295 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
296 end if;
297 if ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
298 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) then
299 null;
300 else
301 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
302 end if;
303 if ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
304 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) then
305 null;
306 else
307 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
308 end if;
309 if ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
310 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) then
311 null;
312 else
313 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
314 end if;
315 if ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
316 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) then
317 null;
318 else
319 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10', tlinfo.ATTRIBUTE10);
320 end if;
321 if ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
322 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) then
323 null;
324 else
325 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
326 end if;
327 if ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
328 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) then
329 null;
330 else
331 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
332 end if;
333 if ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
334 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) then
335 null;
336 else
337 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
338 end if;
339 if ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
340 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) then
341 null;
342 else
346 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) then
343 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
344 end if;
345 if ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
347 null;
348 else
349 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
350 end if;
351
352 if ((tlinfo.ORG_ID = X_ORG_ID)
353 OR ((tlinfo.ORG_ID is null) AND (X_ORG_ID is null))) then
354 null;
355 else
356 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ORG_ID', to_char(tlinfo.ORG_ID));
357 end if;
358
359 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.LOCK_ROW (-)');
360
361 END LOCK_ROW;
362
363 -------------------------------------------------------------------------------
364 -- PROCDURE : UPDATE_ROW
365 -- INVOKED FROM : UPDATE_ROW procedure
366 -- PURPOSE : updates the row
367 -- HISTORY :
368 -- 12-SEP-06 piagrawa o Created
369 -------------------------------------------------------------------------------
370 procedure UPDATE_ROW (
371 X_ABATEMENT_ID in NUMBER
372 ,X_VAR_RENT_ID in NUMBER
373 ,X_START_DATE in DATE
374 ,X_END_DATE in DATE
375 ,X_TYPE_CODE in VARCHAR2
376 ,X_AMOUNT in NUMBER
377 ,X_DESCRIPTION in VARCHAR2
378 ,X_LAST_UPDATE_DATE in DATE
379 ,X_LAST_UPDATED_BY in NUMBER
380 ,X_CREATION_DATE in DATE
381 ,X_CREATED_BY in NUMBER
382 ,X_LAST_UPDATE_LOGIN in NUMBER
383 ,X_COMMENTS in VARCHAR2
384 ,X_ATTRIBUTE_CATEGORY in VARCHAR2
385 ,X_ATTRIBUTE1 in VARCHAR2
386 ,X_ATTRIBUTE2 in VARCHAR2
387 ,X_ATTRIBUTE3 in VARCHAR2
388 ,X_ATTRIBUTE4 in VARCHAR2
389 ,X_ATTRIBUTE5 in VARCHAR2
390 ,X_ATTRIBUTE6 in VARCHAR2
391 ,X_ATTRIBUTE7 in VARCHAR2
392 ,X_ATTRIBUTE8 in VARCHAR2
393 ,X_ATTRIBUTE9 in VARCHAR2
394 ,X_ATTRIBUTE10 in VARCHAR2
395 ,X_ATTRIBUTE11 in VARCHAR2
396 ,X_ATTRIBUTE12 in VARCHAR2
397 ,X_ATTRIBUTE13 in VARCHAR2
398 ,X_ATTRIBUTE14 in VARCHAR2
399 ,X_ATTRIBUTE15 in VARCHAR2
400 ,X_ORG_ID in NUMBER)
401 IS
402 BEGIN
403
404 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.UPDATE_ROW (+)');
405
406 UPDATE PN_VAR_ABAT_DEFAULTS_ALL SET
407 ABATEMENT_ID = X_ABATEMENT_ID
408 ,VAR_RENT_ID = X_VAR_RENT_ID
409 ,START_DATE = X_START_DATE
410 ,END_DATE = X_END_DATE
411 ,TYPE_CODE = X_TYPE_CODE
412 ,AMOUNT = X_AMOUNT
413 ,DESCRIPTION = X_DESCRIPTION
414 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
415 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
416 ,CREATION_DATE = X_CREATION_DATE
417 ,CREATED_BY = X_CREATED_BY
418 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
419 ,COMMENTS = X_COMMENTS
420 ,ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY
421 ,ATTRIBUTE1 = X_ATTRIBUTE1
422 ,ATTRIBUTE2 = X_ATTRIBUTE2
423 ,ATTRIBUTE3 = X_ATTRIBUTE3
424 ,ATTRIBUTE4 = X_ATTRIBUTE4
425 ,ATTRIBUTE5 = X_ATTRIBUTE5
426 ,ATTRIBUTE6 = X_ATTRIBUTE6
427 ,ATTRIBUTE7 = X_ATTRIBUTE7
428 ,ATTRIBUTE8 = X_ATTRIBUTE8
429 ,ATTRIBUTE9 = X_ATTRIBUTE9
430 ,ATTRIBUTE10 = X_ATTRIBUTE10
431 ,ATTRIBUTE11 = X_ATTRIBUTE11
432 ,ATTRIBUTE12 = X_ATTRIBUTE12
433 ,ATTRIBUTE13 = X_ATTRIBUTE13
434 ,ATTRIBUTE14 = X_ATTRIBUTE14
435 ,ATTRIBUTE15 = X_ATTRIBUTE15
436 ,ORG_ID = X_ORG_ID
437 WHERE ABATEMENT_ID = X_ABATEMENT_ID;
438
439 IF (SQL%NOTFOUND) THEN
440 RAISE NO_DATA_FOUND;
441 END IF;
442
443 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.UPDATE_ROW (-)');
444
445 END UPDATE_ROW;
446
447 -------------------------------------------------------------------------------
448 -- PROCDURE : DELETE_ROW
449 -- INVOKED FROM : DELETE_ROW procedure
450 -- PURPOSE : deletes the row
451 -- HISTORY :
452 -- 12-SEP-06 piagrawa o Created
453 -------------------------------------------------------------------------------
454 procedure DELETE_ROW ( X_ABATEMENT_ID in NUMBER)
455 IS
456 BEGIN
457
458 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.DELETE_ROW (+)');
459
460 DELETE FROM PN_VAR_ABAT_DEFAULTS_ALL
461 WHERE ABATEMENT_ID = X_ABATEMENT_ID;
462
463 IF (SQL%NOTFOUND) THEN
464 RAISE NO_DATA_FOUND;
465 END IF;
466
467 PNP_DEBUG_PKG.debug ('PN_VAR_ABAT_DEFAULTS_PKG.DELETE_ROW (-)');
468
469 END DELETE_ROW;
470
471 END PN_VAR_ABAT_DEFAULTS_PKG;
[Home] [Help]