[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_BKDT_DEFAULTS_PKG
Source
1 PACKAGE BODY PN_VAR_BKDT_DEFAULTS_PKG AS
2 /* $Header: PNVRBDDB.pls 120.1 2011/11/22 11:45:35 ppenumar ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 22-Nov-2011 ppenumar o Bug-13413545 Fwd Port- Added the 'attributes' column list in the insert query
10 -------------------------------------------------------------------------------
11 procedure INSERT_ROW (
12 X_ROWID in out NOCOPY VARCHAR2,
13 X_BKDT_DEFAULT_ID in out NOCOPY NUMBER,
14 X_BKDT_DETAIL_NUM in out NOCOPY NUMBER,
15 X_BKHD_DEFAULT_ID in NUMBER,
16 X_BKDT_START_DATE in DATE,
17 X_BKDT_END_DATE in DATE,
18 X_PERIOD_BKPT_VOL_START in NUMBER,
19 X_PERIOD_BKPT_VOL_END in NUMBER,
20 X_GROUP_BKPT_VOL_START in NUMBER,
21 X_GROUP_BKPT_VOL_END in NUMBER,
22 X_BKPT_RATE in NUMBER,
23 X_PROCESSED_FLAG in NUMBER,
24 X_VAR_RENT_ID in NUMBER,
25 X_CREATION_DATE in DATE,
26 X_CREATED_BY in NUMBER,
27 X_LAST_UPDATE_DATE in DATE,
28 X_LAST_UPDATED_BY in NUMBER,
29 X_LAST_UPDATE_LOGIN in NUMBER,
30 X_ORG_ID in NUMBER,
31 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL,
32 X_ATTRIBUTE_CATEGORY in VARCHAR2,
33 X_ATTRIBUTE1 in VARCHAR2,
34 X_ATTRIBUTE2 in VARCHAR2,
35 X_ATTRIBUTE3 in VARCHAR2,
36 X_ATTRIBUTE4 in VARCHAR2,
37 X_ATTRIBUTE5 in VARCHAR2,
38 X_ATTRIBUTE6 in VARCHAR2,
39 X_ATTRIBUTE7 in VARCHAR2,
40 X_ATTRIBUTE8 in VARCHAR2,
41 X_ATTRIBUTE9 in VARCHAR2,
42 X_ATTRIBUTE10 in VARCHAR2,
43 X_ATTRIBUTE11 in VARCHAR2,
44 X_ATTRIBUTE12 in VARCHAR2,
45 X_ATTRIBUTE13 in VARCHAR2,
46 X_ATTRIBUTE14 in VARCHAR2,
47 X_ATTRIBUTE15 in VARCHAR2
48 ) is
49
50 CURSOR C IS
51 SELECT ROWID
52 FROM PN_VAR_BKDT_DEFAULTS_ALL
53 WHERE BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID;
54
55 BEGIN
56
57 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW (+)');
58
59 -------------------------------------------------------
60 -- We need to generate the line number
61 -------------------------------------------------------
62 SELECT nvl(max(bkdt.BKDT_DETAIL_NUM),0)
63 INTO X_BKDT_DETAIL_NUM
64 FROM PN_VAR_BKDT_DEFAULTS_ALL bkdt
65 WHERE bkdt.BKHD_DEFAULT_ID = X_BKHD_DEFAULT_ID;
66
67 X_BKDT_DETAIL_NUM := X_BKDT_DETAIL_NUM + 1;
68
69 -------------------------------------------------------
70 -- Select the nextval for bkpt detail default id
71 -------------------------------------------------------
72 IF ( X_BKDT_DEFAULT_ID IS NULL) THEN
73 SELECT PN_VAR_BKDT_DEFAULTS_S.nextval
74 INTO X_BKDT_DEFAULT_ID
75 FROM dual;
76 END IF;
77
78 INSERT INTO PN_VAR_BKDT_DEFAULTS_ALL
79 (
80 BKDT_DETAIL_NUM,
81 BKHD_DEFAULT_ID,
82 LAST_UPDATE_DATE,
83 LAST_UPDATED_BY,
84 CREATION_DATE,
85 CREATED_BY,
86 LAST_UPDATE_LOGIN,
87 BKDT_START_DATE,
88 BKDT_END_DATE,
89 PERIOD_BKPT_VOL_START,
90 PERIOD_BKPT_VOL_END,
91 GROUP_BKPT_VOL_START,
92 GROUP_BKPT_VOL_END,
93 BKPT_RATE,
94 BKDT_DEFAULT_ID,
95 PROCESSED_FLAG,
96 VAR_RENT_ID,
97 ORG_ID,
98 ANNUAL_BASIS_AMOUNT,
99 ATTRIBUTE_CATEGORY,
100 ATTRIBUTE1,
101 ATTRIBUTE2,
102 ATTRIBUTE3,
103 ATTRIBUTE4,
104 ATTRIBUTE5,
105 ATTRIBUTE6,
106 ATTRIBUTE7,
107 ATTRIBUTE8,
108 ATTRIBUTE9,
109 ATTRIBUTE10,
110 ATTRIBUTE11,
111 ATTRIBUTE12,
112 ATTRIBUTE13,
113 ATTRIBUTE14,
114 ATTRIBUTE15
115 )
116 values
117 (
118 X_BKDT_DETAIL_NUM,
119 X_BKHD_DEFAULT_ID,
120 X_LAST_UPDATE_DATE,
121 X_LAST_UPDATED_BY,
122 X_CREATION_DATE,
123 X_CREATED_BY,
124 X_LAST_UPDATE_LOGIN,
125 X_BKDT_START_DATE,
126 X_BKDT_END_DATE,
127 X_PERIOD_BKPT_VOL_START,
128 X_PERIOD_BKPT_VOL_END,
129 X_GROUP_BKPT_VOL_START,
130 X_GROUP_BKPT_VOL_END,
131 X_BKPT_RATE,
132 X_BKDT_DEFAULT_ID,
133 X_PROCESSED_FLAG,
134 X_VAR_RENT_ID,
135 X_ORG_ID,
136 X_ANNUAL_BASIS_AMOUNT,
137 X_ATTRIBUTE_CATEGORY,
138 X_ATTRIBUTE1,
139 X_ATTRIBUTE2,
140 X_ATTRIBUTE3,
141 X_ATTRIBUTE4,
142 X_ATTRIBUTE5,
143 X_ATTRIBUTE6,
144 X_ATTRIBUTE7,
145 X_ATTRIBUTE8,
146 X_ATTRIBUTE9,
147 X_ATTRIBUTE10,
148 X_ATTRIBUTE11,
149 X_ATTRIBUTE12,
150 X_ATTRIBUTE13,
151 X_ATTRIBUTE14,
152 X_ATTRIBUTE15
153 );
154
155 OPEN c;
156 FETCH c INTO X_ROWID;
157 IF (c%NOTFOUND) THEN
158 CLOSE c;
159 RAISE NO_DATA_FOUND;
160 END IF;
161 CLOSE c;
162
163 UPDATE pn_var_bkhd_defaults_all
164 SET bkpt_update_flag = 'Y'
165 WHERE bkhd_default_id = x_bkhd_default_id;
166
167 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW (-)');
168
169 EXCEPTION
170
171 WHEN OTHERS THEN
172 pnp_debug_pkg.debug(sqlerrm);
173 RAISE;
174 END INSERT_ROW;
175
176 -------------------------------------------------------------------------------
177 -- PROCDURE : LOCK_ROW
178 -- INVOKED FROM : LOCK_ROW procedure
179 -- PURPOSE : locks the row
180 -- HISTORY :
181
182 -------------------------------------------------------------------------------
183 procedure LOCK_ROW (
184 X_BKDT_DEFAULT_ID in NUMBER,
185 X_BKDT_DETAIL_NUM in NUMBER,
186 X_BKHD_DEFAULT_ID in NUMBER,
187 X_BKDT_START_DATE in DATE,
188 X_BKDT_END_DATE in DATE,
189 X_PERIOD_BKPT_VOL_START in NUMBER,
190 X_PERIOD_BKPT_VOL_END in NUMBER,
191 X_GROUP_BKPT_VOL_START in NUMBER,
192 X_GROUP_BKPT_VOL_END in NUMBER,
193 X_BKPT_RATE in NUMBER,
194 X_VAR_RENT_ID in NUMBER,
195 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL,
196 X_ATTRIBUTE_CATEGORY in VARCHAR2,
197 X_ATTRIBUTE1 in VARCHAR2,
198 X_ATTRIBUTE2 in VARCHAR2,
199 X_ATTRIBUTE3 in VARCHAR2,
200 X_ATTRIBUTE4 in VARCHAR2,
201 X_ATTRIBUTE5 in VARCHAR2,
202 X_ATTRIBUTE6 in VARCHAR2,
203 X_ATTRIBUTE7 in VARCHAR2,
204 X_ATTRIBUTE8 in VARCHAR2,
205 X_ATTRIBUTE9 in VARCHAR2,
206 X_ATTRIBUTE10 in VARCHAR2,
207 X_ATTRIBUTE11 in VARCHAR2,
208 X_ATTRIBUTE12 in VARCHAR2,
209 X_ATTRIBUTE13 in VARCHAR2,
210 X_ATTRIBUTE14 in VARCHAR2,
211 X_ATTRIBUTE15 in VARCHAR2
212 ) is
213
214 cursor c1 is select
215 *
216 from PN_VAR_BKDT_DEFAULTS_ALL
217 where BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID
218 for update of BKDT_DEFAULT_ID nowait;
219 begin
220
221 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.LOCK_ROW (+)');
222
223 for tlinfo in c1 loop
224 /*
225 if ( (tlinfo.BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID)
226 AND (tlinfo.BKDT_DETAIL_NUM = X_BKDT_DETAIL_NUM)
227 AND (tlinfo.BKHD_DEFAULT_ID = X_BKHD_DEFAULT_ID)
228 AND ((tlinfo.BKDT_START_DATE = X_BKDT_START_DATE)
229 OR ((tlinfo.BKDT_START_DATE is null) AND (X_BKDT_START_DATE is null)))
230 AND ((tlinfo.BKDT_END_DATE = X_BKDT_END_DATE)
231 OR ((tlinfo.BKDT_END_DATE is null) AND (X_BKDT_END_DATE is null)))
232 AND ((tlinfo.PERIOD_BKPT_VOL_START = X_PERIOD_BKPT_VOL_START)
233 OR ((tlinfo.PERIOD_BKPT_VOL_START is null) AND (X_PERIOD_BKPT_VOL_START is null)))
234 AND ((tlinfo.PERIOD_BKPT_VOL_END = X_PERIOD_BKPT_VOL_END)
235 OR ((tlinfo.PERIOD_BKPT_VOL_END is null) AND (X_PERIOD_BKPT_VOL_END is null)))
236 AND ((tlinfo.GROUP_BKPT_VOL_START = X_GROUP_BKPT_VOL_START)
237 OR ((tlinfo.GROUP_BKPT_VOL_START is null) AND (X_GROUP_BKPT_VOL_START is null)))
238 AND ((tlinfo.GROUP_BKPT_VOL_END = X_GROUP_BKPT_VOL_END)
239 OR ((tlinfo.GROUP_BKPT_VOL_END is null) AND (X_GROUP_BKPT_VOL_END is null)))
240 AND ((tlinfo.BKPT_RATE = X_BKPT_RATE)
241 OR ((tlinfo.BKPT_RATE is null) AND (X_BKPT_RATE is null)))
242 AND ((tlinfo.VAR_RENT_ID = X_VAR_RENT_ID)
243 OR ((tlinfo.VAR_RENT_ID is null) AND (X_VAR_RENT_ID is null)))
244 ) then
245 null;
246 else
247 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
248 app_exception.raise_exception;
249 end if;
250
251 */
252 IF NOT (tlinfo.BKDT_START_DATE = X_BKDT_START_DATE) THEN
253 pn_var_rent_pkg.lock_row_exception('BKDT_START_DATE',tlinfo.BKDT_START_DATE);
254 END IF;
255 IF NOT (tlinfo.BKDT_END_DATE = X_BKDT_END_DATE) THEN
256 pn_var_rent_pkg.lock_row_exception('BKDT_END_DATE',tlinfo.BKDT_END_DATE);
257 END IF;
258 IF NOT (tlinfo.PERIOD_BKPT_VOL_START = X_PERIOD_BKPT_VOL_START) THEN
259 pn_var_rent_pkg.lock_row_exception('PERIOD_BKPT_VOL_START',tlinfo.PERIOD_BKPT_VOL_START);
260 END IF;
261 IF NOT (tlinfo.PERIOD_BKPT_VOL_END = X_PERIOD_BKPT_VOL_END) THEN
262 pn_var_rent_pkg.lock_row_exception('PERIOD_BKPT_VOL_END',tlinfo.PERIOD_BKPT_VOL_END);
263 END IF;
264 IF NOT (tlinfo.GROUP_BKPT_VOL_START = X_GROUP_BKPT_VOL_START) THEN
265 pn_var_rent_pkg.lock_row_exception('GROUP_BKPT_VOL_START',tlinfo.GROUP_BKPT_VOL_START);
266 END IF;
267 IF NOT (tlinfo.GROUP_BKPT_VOL_END = X_GROUP_BKPT_VOL_END) THEN
268 pn_var_rent_pkg.lock_row_exception('GROUP_BKPT_VOL_END',tlinfo.GROUP_BKPT_VOL_END);
269 END IF;
270 IF NOT (tlinfo.BKPT_RATE = X_BKPT_RATE) THEN
271 pn_var_rent_pkg.lock_row_exception('BKPT_RATE',tlinfo.BKPT_RATE);
272 END IF;
273 IF NOT (tlinfo.ANNUAL_BASIS_AMOUNT = X_ANNUAL_BASIS_AMOUNT) THEN
274 pn_var_rent_pkg.lock_row_exception('ANNUAL_BASIS_AMOUNT',tlinfo.ANNUAL_BASIS_AMOUNT);
275 END IF;
276 IF NOT (tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY) THEN
277 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
278 END IF;
279 IF NOT (tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1) THEN
280 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
281 END IF;
282 IF NOT (tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2) THEN
283 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
284 END IF;
285 IF NOT (tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3) THEN
286 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
287 END IF;
288 IF NOT (tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4) THEN
289 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
290 END IF;
291 IF NOT (tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5) THEN
292 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
293 END IF;
294 IF NOT (tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6) THEN
295 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
296 END IF;
297 IF NOT (tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7) THEN
298 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
299 END IF;
300 IF NOT (tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8) THEN
301 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
302 END IF;
303 IF NOT (tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9) THEN
304 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
305 END IF;
306 IF NOT (tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10) THEN
307 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
308 END IF;
309 IF NOT (tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11) THEN
310 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
311 END IF;
312 IF NOT (tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12) THEN
313 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
314 END IF;
315 IF NOT (tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13) THEN
316 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
317 END IF;
318 IF NOT (tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14) THEN
319 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
320 END IF;
321 IF NOT (tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15) THEN
322 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
323 END IF;
324
325 end loop;
326 return;
327
328 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.LOCK_ROW (-)');
329
330 end LOCK_ROW;
331
332 -------------------------------------------------------------------------------
333 -- PROCDURE : UPDATE_ROW
334 -- INVOKED FROM : UPDATE_ROW procedure
335 -- PURPOSE : updates the row
336 -- HISTORY :
337
338 -------------------------------------------------------------------------------
339 procedure UPDATE_ROW (
340 X_BKDT_DEFAULT_ID in NUMBER,
341 X_BKDT_DETAIL_NUM in NUMBER,
342 X_BKHD_DEFAULT_ID in NUMBER,
343 X_BKDT_START_DATE in DATE,
344 X_BKDT_END_DATE in DATE,
345 X_PERIOD_BKPT_VOL_START in NUMBER,
346 X_PERIOD_BKPT_VOL_END in NUMBER,
347 X_GROUP_BKPT_VOL_START in NUMBER,
348 X_GROUP_BKPT_VOL_END in NUMBER,
349 X_BKPT_RATE in NUMBER,
350 X_PROCESSED_FLAG in NUMBER,
351 X_VAR_RENT_ID in NUMBER,
352 X_LAST_UPDATE_DATE in DATE,
353 X_LAST_UPDATED_BY in NUMBER,
354 X_LAST_UPDATE_LOGIN in NUMBER,
355 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL,
356 X_ATTRIBUTE_CATEGORY in VARCHAR2,
357 X_ATTRIBUTE1 in VARCHAR2,
358 X_ATTRIBUTE2 in VARCHAR2,
359 X_ATTRIBUTE3 in VARCHAR2,
360 X_ATTRIBUTE4 in VARCHAR2,
361 X_ATTRIBUTE5 in VARCHAR2,
362 X_ATTRIBUTE6 in VARCHAR2,
363 X_ATTRIBUTE7 in VARCHAR2,
364 X_ATTRIBUTE8 in VARCHAR2,
365 X_ATTRIBUTE9 in VARCHAR2,
366 X_ATTRIBUTE10 in VARCHAR2,
367 X_ATTRIBUTE11 in VARCHAR2,
368 X_ATTRIBUTE12 in VARCHAR2,
369 X_ATTRIBUTE13 in VARCHAR2,
370 X_ATTRIBUTE14 in VARCHAR2,
371 X_ATTRIBUTE15 in VARCHAR2
372 ) IS
373
374 BEGIN
375
376 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.UPDATE_ROW (+)');
377
378 UPDATE PN_VAR_BKDT_DEFAULTS_ALL SET
379 BKDT_DETAIL_NUM = X_BKDT_DETAIL_NUM,
380 BKHD_DEFAULT_ID = X_BKHD_DEFAULT_ID,
381 BKDT_START_DATE = X_BKDT_START_DATE,
382 BKDT_END_DATE = X_BKDT_END_DATE,
383 PERIOD_BKPT_VOL_START = X_PERIOD_BKPT_VOL_START,
384 PERIOD_BKPT_VOL_END = X_PERIOD_BKPT_VOL_END,
385 GROUP_BKPT_VOL_START = X_GROUP_BKPT_VOL_START,
386 GROUP_BKPT_VOL_END = X_GROUP_BKPT_VOL_END,
387 BKPT_RATE = X_BKPT_RATE,
388 PROCESSED_FLAG = X_PROCESSED_FLAG,
389 VAR_RENT_ID = X_VAR_RENT_ID,
390 BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID,
391 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
392 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
393 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
397 ATTRIBUTE2 = X_ATTRIBUTE2,
394 ANNUAL_BASIS_AMOUNT = X_ANNUAL_BASIS_AMOUNT,
395 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
396 ATTRIBUTE1 = X_ATTRIBUTE1,
398 ATTRIBUTE3 = X_ATTRIBUTE3,
399 ATTRIBUTE4 = X_ATTRIBUTE4,
400 ATTRIBUTE5 = X_ATTRIBUTE5,
401 ATTRIBUTE6 = X_ATTRIBUTE6,
402 ATTRIBUTE7 = X_ATTRIBUTE7,
403 ATTRIBUTE8 = X_ATTRIBUTE8,
404 ATTRIBUTE9 = X_ATTRIBUTE9,
405 ATTRIBUTE10 = X_ATTRIBUTE10,
406 ATTRIBUTE11 = X_ATTRIBUTE11,
407 ATTRIBUTE12 = X_ATTRIBUTE12,
408 ATTRIBUTE13 = X_ATTRIBUTE13,
409 ATTRIBUTE14 = X_ATTRIBUTE14,
410 ATTRIBUTE15 = X_ATTRIBUTE15
411 WHERE BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID;
412
413 IF (SQL%NOTFOUND) THEN
414 RAISE NO_DATA_FOUND;
415 END IF;
416
417 UPDATE pn_var_bkhd_defaults_all
418 SET bkpt_update_flag = 'Y'
419 WHERE bkhd_default_id = x_bkhd_default_id;
420
421 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.UPDATE_ROW (-)');
422
423 END UPDATE_ROW;
424
425 -----------------------------------------------------------------------
426 -- PROCDURE : DELETE_ROW
427 -----------------------------------------------------------------------
428 procedure DELETE_ROW ( X_BKDT_DEFAULT_ID in NUMBER)
429 IS
430 CURSOR bkhd_default IS
431 SELECT bkhd_default_id
432 FROM pn_var_bkdt_defaults_all
433 WHERE bkdt_default_id = x_bkdt_default_id;
434 BEGIN
435
436 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.DELETE_ROW (+)');
437
438 FOR rec IN bkhd_default LOOP
439 UPDATE pn_var_bkhd_defaults_all
440 SET bkpt_update_flag = 'Y'
441 WHERE bkhd_default_id = rec.bkhd_default_id;
442 END LOOP;
443
444 DELETE FROM pn_var_bkdt_defaults_all
445 WHERE bkdt_default_id = x_bkdt_default_id;
446
447 IF (SQL%NOTFOUND) THEN
448 RAISE NO_DATA_FOUND;
449 END IF;
450
451 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.DELETE_ROW (-)');
452 END DELETE_ROW;
453
454
455 -------------------------------------------------------------------------------
456 -- PROCDURE : MODIFY_ROW
457 -- INVOKED FROM : MODIFY_ROW procedure
458 -- PURPOSE : updates the row
459 -- HISTORY :
460
461 -------------------------------------------------------------------------------
462 procedure MODIFY_ROW (
463 X_BKDT_DEFAULT_ID in NUMBER,
464 X_BKDT_DETAIL_NUM in NUMBER,
465 X_BKHD_DEFAULT_ID in NUMBER,
466 X_BKDT_START_DATE in DATE,
467 X_BKDT_END_DATE in DATE,
468 X_PERIOD_BKPT_VOL_START in NUMBER,
469 X_PERIOD_BKPT_VOL_END in NUMBER,
470 X_GROUP_BKPT_VOL_START in NUMBER,
471 X_GROUP_BKPT_VOL_END in NUMBER,
472 X_BKPT_RATE in NUMBER,
473 X_PROCESSED_FLAG in NUMBER,
474 X_VAR_RENT_ID in NUMBER,
475 X_LAST_UPDATE_DATE in DATE,
476 X_LAST_UPDATED_BY in NUMBER,
477 X_LAST_UPDATE_LOGIN in NUMBER,
478 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL,
479 X_ATTRIBUTE_CATEGORY in VARCHAR2,
480 X_ATTRIBUTE1 in VARCHAR2,
481 X_ATTRIBUTE2 in VARCHAR2,
482 X_ATTRIBUTE3 in VARCHAR2,
483 X_ATTRIBUTE4 in VARCHAR2,
484 X_ATTRIBUTE5 in VARCHAR2,
485 X_ATTRIBUTE6 in VARCHAR2,
486 X_ATTRIBUTE7 in VARCHAR2,
487 X_ATTRIBUTE8 in VARCHAR2,
488 X_ATTRIBUTE9 in VARCHAR2,
489 X_ATTRIBUTE10 in VARCHAR2,
490 X_ATTRIBUTE11 in VARCHAR2,
491 X_ATTRIBUTE12 in VARCHAR2,
492 X_ATTRIBUTE13 in VARCHAR2,
493 X_ATTRIBUTE14 in VARCHAR2,
494 X_ATTRIBUTE15 in VARCHAR2
495 ) IS
496
497 /* Get the details of breakpoint details default */
498 CURSOR bkdt_defaults_cur IS
499 SELECT *
500 FROM pn_var_bkdt_defaults_all
501 WHERE bkdt_default_id = x_bkdt_default_id;
502
503 BEGIN
504
505 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.MODIFY_ROW (+)');
506
507 FOR rec IN bkdt_defaults_cur LOOP
508 UPDATE PN_VAR_BKDT_DEFAULTS_ALL SET
509 BKDT_DETAIL_NUM = NVL( x_bkdt_detail_num, rec.bkdt_detail_num),
510 BKHD_DEFAULT_ID = x_bkhd_default_id,
511 BKDT_START_DATE = NVL( x_bkdt_start_date, rec.bkdt_start_date),
512 BKDT_END_DATE = NVL( x_bkdt_end_date, rec.bkdt_end_date),
513 PERIOD_BKPT_VOL_START = NVL( x_period_bkpt_vol_start, rec.period_bkpt_vol_start),
514 PERIOD_BKPT_VOL_END = NVL( x_period_bkpt_vol_end, rec.period_bkpt_vol_end),
515 GROUP_BKPT_VOL_START = NVL( x_group_bkpt_vol_start, rec.group_bkpt_vol_start),
516 GROUP_BKPT_VOL_END = NVL( x_group_bkpt_vol_end, rec.group_bkpt_vol_end),
517 BKPT_RATE = NVL( x_bkpt_rate, rec.bkpt_rate),
518 PROCESSED_FLAG = NVL( x_processed_flag, rec.processed_flag),
519 VAR_RENT_ID = NVL( x_var_rent_id, rec.var_rent_id),
520 BKDT_DEFAULT_ID = NVL( x_bkdt_default_id, rec.bkdt_default_id),
521 LAST_UPDATE_DATE = NVL( x_last_update_date, rec.last_update_date),
525 ATTRIBUTE_CATEGORY = NVL( x_attribute_category, rec.attribute_category),
522 LAST_UPDATED_BY = NVL( x_last_updated_by, rec.last_updated_by),
523 LAST_UPDATE_LOGIN = NVL( x_last_update_login, rec.last_update_login),
524 ANNUAL_BASIS_AMOUNT = NVL( x_annual_basis_amount, rec.annual_basis_amount),
526 ATTRIBUTE1 = NVL( x_attribute1, rec.attribute1),
527 ATTRIBUTE2 = NVL( x_attribute2, rec.attribute2),
528 ATTRIBUTE3 = NVL( x_attribute3, rec.attribute3),
529 ATTRIBUTE4 = NVL( x_attribute4, rec.attribute4),
530 ATTRIBUTE5 = NVL( x_attribute5, rec.attribute5),
531 ATTRIBUTE6 = NVL( x_attribute6, rec.attribute6),
532 ATTRIBUTE7 = NVL( x_attribute7, rec.attribute7),
533 ATTRIBUTE8 = NVL( x_attribute8, rec.attribute8),
534 ATTRIBUTE9 = NVL( x_attribute9, rec.attribute9),
535 ATTRIBUTE10 = NVL( x_attribute10, rec.attribute10),
536 ATTRIBUTE11 = NVL( x_attribute11, rec.attribute11),
537 ATTRIBUTE12 = NVL( x_attribute12, rec.attribute12),
538 ATTRIBUTE13 = NVL( x_attribute13, rec.attribute13),
539 ATTRIBUTE14 = NVL( x_attribute14, rec.attribute14),
540 ATTRIBUTE15 = NVL( x_attribute15, rec.attribute15)
541 WHERE BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID;
542
543 IF (SQL%NOTFOUND) THEN
544 RAISE NO_DATA_FOUND;
545 END IF;
546
547 UPDATE pn_var_bkhd_defaults_all
548 SET bkpt_update_flag = 'Y'
549 WHERE bkhd_default_id = x_bkhd_default_id;
550
551 END LOOP;
552
553 PNP_DEBUG_PKG.debug ('PN_VAR_BKDT_DEFAULTS_PKG.MODIFY_ROW (-)');
554
555 END MODIFY_ROW;
556
557 end PN_VAR_BKDT_DEFAULTS_PKG;