1 package body PN_VAR_BKPTS_DET_PKG as
2 /* $Header: PNVRBKDB.pls 120.2 2006/12/20 09:21:26 pseeram noship $ */
3 -------------------------------------------------------------------------------
4 -- PROCDURE : INSERT_ROW
5 -- INVOKED FROM : insert_row procedure
6 -- PURPOSE : inserts the row
7 -- HISTORY :
8 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced PN_VAR_BKPTS_DET with _ALL table.
9 -- 29-SEP-06 pikhar o Bug 5573913 - Commented call to
10 -- PN_VAR_BKPTS_DET_PKG.CHECK_VOL_START
11 -------------------------------------------------------------------------------
12 procedure INSERT_ROW (
13 X_ROWID in out NOCOPY VARCHAR2,
14 X_BKPT_DETAIL_ID in out NOCOPY NUMBER,
15 X_BKPT_DETAIL_NUM in out NOCOPY NUMBER,
16 X_BKPT_HEADER_ID in NUMBER,
17 X_BKPT_START_DATE in DATE,
18 X_BKPT_END_DATE in DATE,
19 X_PERIOD_BKPT_VOL_START in NUMBER,
20 X_PERIOD_BKPT_VOL_END in NUMBER,
21 X_GROUP_BKPT_VOL_START in NUMBER,
22 X_GROUP_BKPT_VOL_END in NUMBER,
23 X_BKPT_RATE in NUMBER,
24 X_BKDT_DEFAULT_ID in NUMBER,
25 X_VAR_RENT_ID in NUMBER,
26 X_COMMENTS in VARCHAR2,
27 X_ATTRIBUTE_CATEGORY in VARCHAR2,
28 X_ATTRIBUTE1 in VARCHAR2,
29 X_ATTRIBUTE2 in VARCHAR2,
30 X_ATTRIBUTE3 in VARCHAR2,
31 X_ATTRIBUTE4 in VARCHAR2,
32 X_ATTRIBUTE5 in VARCHAR2,
33 X_ATTRIBUTE6 in VARCHAR2,
34 X_ATTRIBUTE7 in VARCHAR2,
35 X_ATTRIBUTE8 in VARCHAR2,
36 X_ATTRIBUTE9 in VARCHAR2,
37 X_ATTRIBUTE10 in VARCHAR2,
38 X_ATTRIBUTE11 in VARCHAR2,
39 X_ATTRIBUTE12 in VARCHAR2,
40 X_ATTRIBUTE13 in VARCHAR2,
41 X_ATTRIBUTE14 in VARCHAR2,
42 X_ATTRIBUTE15 in VARCHAR2,
43 X_ORG_ID in NUMBER,
44 X_CREATION_DATE in DATE,
45 X_CREATED_BY in NUMBER,
46 X_LAST_UPDATE_DATE in DATE,
47 X_LAST_UPDATED_BY in NUMBER,
48 X_LAST_UPDATE_LOGIN in NUMBER,
49 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL --03-NOV-2003
50 )
51 IS
52
53 CURSOR C IS
54 SELECT ROWID
55 FROM PN_VAR_BKPTS_DET_ALL
56 WHERE BKPT_DETAIL_ID = X_BKPT_DETAIL_ID
57 ;
58
59 l_return_status VARCHAR2(30) := NULL;
60
61 BEGIN
62
63 PNP_DEBUG_PKG.debug ('PN_VAR_BKPTS_DET_PKG.INSERT_ROW (+)');
64
65 -------------------------------------------------------
66 -- We need to generate the breakpoints details number
67 -------------------------------------------------------
68 SELECT nvl(max(bkdetails.BKPT_DETAIL_NUM),0)
69 INTO X_BKPT_DETAIL_NUM
70 FROM PN_VAR_BKPTS_DET_ALL bkdetails
71 WHERE bkdetails.BKPT_HEADER_ID = X_BKPT_HEADER_ID;
72
73 X_BKPT_DETAIL_NUM := X_BKPT_DETAIL_NUM + 1;
74
75 -------------------------------------------------------
76 -- Select the nextval for breakpoints details id
77 -------------------------------------------------------
78 IF ( X_BKPT_DETAIL_ID IS NULL) THEN
79 SELECT pn_var_bkpts_det_s.nextval
80 INTO X_BKPT_DETAIL_ID
81 FROM dual;
82 END IF;
83
84 -- Check for breakpoint volume ranges
85 /* l_return_status := NULL;
86 PN_VAR_BKPTS_DET_PKG.CHECK_VOL_START
87 (
88 l_return_status,
89 x_bkpt_header_id,
90 x_bkpt_detail_id,
91 X_PERIOD_BKPT_VOL_START
92 );
93
94 IF (l_return_status IS NOT NULL) THEN
95 APP_EXCEPTION.Raise_Exception;
96 END IF;*/
97
98 INSERT INTO PN_VAR_BKPTS_DET_ALL
99 (
100 BKPT_DETAIL_ID,
101 BKPT_DETAIL_NUM,
102 LAST_UPDATE_DATE,
103 LAST_UPDATED_BY,
104 CREATION_DATE,
105 CREATED_BY,
106 LAST_UPDATE_LOGIN,
107 BKPT_HEADER_ID,
108 BKPT_START_DATE,
109 BKPT_END_DATE,
110 PERIOD_BKPT_VOL_START,
111 PERIOD_BKPT_VOL_END,
112 GROUP_BKPT_VOL_START,
113 GROUP_BKPT_VOL_END,
114 BKPT_RATE,
115 BKDT_DEFAULT_ID,
116 VAR_RENT_ID,
117 COMMENTS,
118 ATTRIBUTE_CATEGORY,
119 ATTRIBUTE1,
120 ATTRIBUTE2,
121 ATTRIBUTE3,
122 ATTRIBUTE4,
123 ATTRIBUTE5,
124 ATTRIBUTE6,
125 ATTRIBUTE7,
126 ATTRIBUTE8,
127 ATTRIBUTE9,
128 ATTRIBUTE10,
129 ATTRIBUTE11,
130 ATTRIBUTE12,
131 ATTRIBUTE13,
132 ATTRIBUTE14,
133 ATTRIBUTE15,
134 ORG_ID,
135 ANNUAL_BASIS_AMOUNT --03-NOV-2003
136 )
137 VALUES
138 (
139 X_BKPT_DETAIL_ID,
140 X_BKPT_DETAIL_NUM,
141 X_LAST_UPDATE_DATE,
142 X_LAST_UPDATED_BY,
143 X_CREATION_DATE,
144 X_CREATED_BY,
145 X_LAST_UPDATE_LOGIN,
146 X_BKPT_HEADER_ID,
147 X_BKPT_START_DATE,
148 X_BKPT_END_DATE,
149 X_PERIOD_BKPT_VOL_START,
150 X_PERIOD_BKPT_VOL_END,
151 X_GROUP_BKPT_VOL_START,
152 X_GROUP_BKPT_VOL_END,
153 X_BKPT_RATE,
154 X_BKDT_DEFAULT_ID,
155 X_VAR_RENT_ID,
156 X_COMMENTS,
157 X_ATTRIBUTE_CATEGORY,
158 X_ATTRIBUTE1,
159 X_ATTRIBUTE2,
160 X_ATTRIBUTE3,
161 X_ATTRIBUTE4,
162 X_ATTRIBUTE5,
163 X_ATTRIBUTE6,
164 X_ATTRIBUTE7,
165 X_ATTRIBUTE8,
166 X_ATTRIBUTE9,
167 X_ATTRIBUTE10,
168 X_ATTRIBUTE11,
169 X_ATTRIBUTE12,
170 X_ATTRIBUTE13,
171 X_ATTRIBUTE14,
172 X_ATTRIBUTE15,
173 X_ORG_ID,
174 X_ANNUAL_BASIS_AMOUNT --03-NOV-2003
175 );
176
177 OPEN c;
178 FETCH c INTO X_ROWID;
179 IF (c%notfound) THEN
180 CLOSE c;
181 RAISE NO_DATA_FOUND;
182 END IF;
183 CLOSE c;
184
185 IF x_bkdt_default_id IS NULL THEN
186 UPDATE pn_var_lines_all
187 SET bkpt_update_flag = 'Y'
188 WHERE line_item_id IN (SELECT line_item_id
189 FROM pn_var_bkpts_head_all
190 WHERE bkpt_header_id = x_bkpt_header_id);
191 END IF;
192
193 PNP_DEBUG_PKG.debug ('PN_VAR_BKPTS_DET_PKG.INSERT_ROW (-)');
194
195 END INSERT_ROW;
196
197 -------------------------------------------------------------------------------
198 -- PROCDURE : LOCK_ROW
199 -- INVOKED FROM : LOCK_ROW procedure
200 -- PURPOSE : locks the row
201 -- HISTORY :
202 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced PN_VAR_BKPTS_DET with _ALL table.
203 -------------------------------------------------------------------------------
204 procedure LOCK_ROW (
205 X_BKPT_DETAIL_ID in NUMBER,
206 X_BKPT_DETAIL_NUM in NUMBER,
207 X_BKPT_HEADER_ID in NUMBER,
208 X_BKPT_START_DATE in DATE,
209 X_BKPT_END_DATE in DATE,
210 X_PERIOD_BKPT_VOL_START in NUMBER,
211 X_PERIOD_BKPT_VOL_END in NUMBER,
212 X_GROUP_BKPT_VOL_START in NUMBER,
213 X_GROUP_BKPT_VOL_END in NUMBER,
214 X_BKPT_RATE in NUMBER,
215 X_BKDT_DEFAULT_ID in NUMBER,
216 X_VAR_RENT_ID in NUMBER,
217 X_COMMENTS in VARCHAR2,
218 X_ATTRIBUTE_CATEGORY in VARCHAR2,
219 X_ATTRIBUTE1 in VARCHAR2,
220 X_ATTRIBUTE2 in VARCHAR2,
221 X_ATTRIBUTE3 in VARCHAR2,
222 X_ATTRIBUTE4 in VARCHAR2,
223 X_ATTRIBUTE5 in VARCHAR2,
224 X_ATTRIBUTE6 in VARCHAR2,
225 X_ATTRIBUTE7 in VARCHAR2,
226 X_ATTRIBUTE8 in VARCHAR2,
227 X_ATTRIBUTE9 in VARCHAR2,
228 X_ATTRIBUTE10 in VARCHAR2,
229 X_ATTRIBUTE11 in VARCHAR2,
230 X_ATTRIBUTE12 in VARCHAR2,
231 X_ATTRIBUTE13 in VARCHAR2,
232 X_ATTRIBUTE14 in VARCHAR2,
233 X_ATTRIBUTE15 in VARCHAR2,
234 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL --03-NOV-2003
235 )
236 IS
237
238 CURSOR c1 IS
239 SELECT *
240 FROM PN_VAR_BKPTS_DET_ALL
241 WHERE BKPT_DETAIL_ID = X_BKPT_DETAIL_ID
242 FOR UPDATE OF BKPT_DETAIL_ID nowait;
243
244 tlinfo c1%rowtype;
245
246 BEGIN
247
248 PNP_DEBUG_PKG.debug ('PN_VAR_BKPTS_DET_PKG.LOCK_ROW (+)');
249
250 OPEN c1;
251 FETCH c1 into tlinfo;
252 IF (c1%notfound) then
253 close c1;
254 return;
255 END IF;
256 CLOSE c1;
257
258 if (tlinfo.BKPT_DETAIL_ID = X_BKPT_DETAIL_ID) then
259 null;
260 else
261 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_DETAIL_ID',to_char(tlinfo.BKPT_DETAIL_ID));
262 end if;
263 if ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
264 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) then
265 null;
266 else
267 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
268 end if;
269 if (tlinfo.BKPT_DETAIL_NUM = X_BKPT_DETAIL_NUM) then
270 null;
271 else
272 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_DETAIL_NUM',to_char(tlinfo.BKPT_DETAIL_NUM));
273 end if;
274 if ((tlinfo.BKPT_HEADER_ID = X_BKPT_HEADER_ID)
275 OR ((tlinfo.BKPT_HEADER_ID is null) AND (X_BKPT_HEADER_ID is null))) then
276 null;
277 else
278 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_HEADER_ID',to_char(tlinfo.BKPT_HEADER_ID));
279 end if;
280 if ((tlinfo.BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID)
281 OR ((tlinfo.BKDT_DEFAULT_ID is null) AND (X_BKDT_DEFAULT_ID is null))) then
282 null;
283 else
284 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_HEADER_ID',to_char(tlinfo.BKPT_HEADER_ID));
285 end if;
286 if ((tlinfo.VAR_RENT_ID = X_VAR_RENT_ID)
287 OR ((tlinfo.VAR_RENT_ID is null) AND (X_VAR_RENT_ID is null))) then
288 null;
289 else
290 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VAR_RENT_ID',to_char(tlinfo.VAR_RENT_ID));
291 end if;
292 if ((tlinfo.BKPT_START_DATE = X_BKPT_START_DATE)
293 OR ((tlinfo.BKPT_START_DATE is null) AND (X_BKPT_START_DATE is null))) then
294 null;
295 else
296 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_START_DATE',to_char(tlinfo.BKPT_START_DATE));
297 end if;
298 if ((tlinfo.BKPT_END_DATE = X_BKPT_END_DATE)
299 OR ((tlinfo.BKPT_END_DATE is null) AND (X_BKPT_END_DATE is null))) then
300 null;
301 else
302 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_END_DATE',to_char(tlinfo.BKPT_END_DATE));
303 end if;
304 if ((tlinfo.PERIOD_BKPT_VOL_START = X_PERIOD_BKPT_VOL_START)
305 OR ((tlinfo.PERIOD_BKPT_VOL_START is null) AND (X_PERIOD_BKPT_VOL_START is null))) then
306 null;
307 else
308 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PERIOD_BKPT_VOL_START',to_char(tlinfo.PERIOD_BKPT_VOL_START));
309 end if;
310 if ((tlinfo.PERIOD_BKPT_VOL_END = X_PERIOD_BKPT_VOL_END)
311 OR ((tlinfo.PERIOD_BKPT_VOL_END is null) AND (X_PERIOD_BKPT_VOL_END is null))) then
312 null;
313 else
314 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PERIOD_BKPT_VOL_END',to_char(tlinfo.PERIOD_BKPT_VOL_END));
315 end if;
316 if (tlinfo.GROUP_BKPT_VOL_START = X_GROUP_BKPT_VOL_START) then
317 null;
318 else
319 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('GROUP_BKPT_VOL_START',to_char(tlinfo.GROUP_BKPT_VOL_START));
320 end if;
321 if ((tlinfo.GROUP_BKPT_VOL_END = X_GROUP_BKPT_VOL_END)
322 OR ((tlinfo.GROUP_BKPT_VOL_END is null) AND (X_GROUP_BKPT_VOL_END is null))) then
323 null;
324 else
325 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('GROUP_BKPT_VOL_END',to_char(tlinfo.GROUP_BKPT_VOL_END));
326 end if;
327 if (tlinfo.BKPT_RATE = X_BKPT_RATE) then
328 null;
329 else
330 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('BKPT_RATE',to_char(tlinfo.BKPT_RATE));
331 end if;
332 if ((tlinfo.COMMENTS = X_COMMENTS)
333 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null))) then
334 null;
335 else
336 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('COMMENTS',tlinfo.COMMENTS);
337 end if;
338 if ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
339 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) then
340 null;
341 else
342 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
343 end if;
344 if ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
345 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) then
346 null;
347 else
348 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
349 end if;
350 if ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
351 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) then
352 null;
353 else
354 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
355 end if;
356 if ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
357 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) then
358 null;
359 else
360 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
361 end if;
365 else
362 if ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
363 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) then
364 null;
366 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
367 end if;
368 if ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
369 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) then
370 null;
371 else
372 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
373 end if;
374 if ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
375 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) then
376 null;
377 else
378 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
379 end if;
380 if ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
381 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) then
382 null;
383 else
384 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
385 end if;
386 if ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
387 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) then
388 null;
389 else
390 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
391 end if;
392 if ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
393 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) then
394 null;
395 else
396 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
397 end if;
398 if ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
399 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) then
400 null;
401 else
402 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
403 end if;
404 if ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
405 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) then
406 null;
407 else
408 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
409 end if;
410 if ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
411 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) then
412 null;
413 else
414 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
415 end if;
416 if ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
417 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) then
418 null;
419 else
420 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
421 end if;
422 if ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
423 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) then
424 null;
425 else
426 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
427 end if;
428 --03-NOV-2003 Start
429 if ((tlinfo.ANNUAL_BASIS_AMOUNT = X_ANNUAL_BASIS_AMOUNT)
430 OR ((tlinfo.ANNUAL_BASIS_AMOUNT is null) AND (X_ANNUAL_BASIS_AMOUNT is null))) then
431 null;
432 else
433 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ANNUAL_BASIS_AMOUNT',tlinfo.ANNUAL_BASIS_AMOUNT);
434 end if;
435 --03-NOV-2003 End
436
437 PNP_DEBUG_PKG.debug ('PN_VAR_BKPTS_DET_PKG.LOCK_ROW (-)');
438
439 END LOCK_ROW;
440
441 -------------------------------------------------------------------------------
442 -- PROCDURE : UPDATE_ROW
443 -- INVOKED FROM : UPDATE_ROW procedure
444 -- PURPOSE : updates the row
445 -- HISTORY :
446 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced PN_VAR_BKPTS_DET with _ALL table.
447 -------------------------------------------------------------------------------
448 procedure UPDATE_ROW (
449 X_BKPT_DETAIL_ID in NUMBER,
450 X_BKPT_DETAIL_NUM in NUMBER,
451 X_BKPT_HEADER_ID in NUMBER,
452 X_BKPT_START_DATE in DATE,
453 X_BKPT_END_DATE in DATE,
454 X_PERIOD_BKPT_VOL_START in NUMBER,
455 X_PERIOD_BKPT_VOL_END in NUMBER,
456 X_GROUP_BKPT_VOL_START in NUMBER,
457 X_GROUP_BKPT_VOL_END in NUMBER,
458 X_BKPT_RATE in NUMBER,
459 X_BKDT_DEFAULT_ID in NUMBER,
460 X_VAR_RENT_ID in NUMBER,
461 X_COMMENTS in VARCHAR2,
462 X_ATTRIBUTE_CATEGORY in VARCHAR2,
463 X_ATTRIBUTE1 in VARCHAR2,
464 X_ATTRIBUTE2 in VARCHAR2,
465 X_ATTRIBUTE3 in VARCHAR2,
466 X_ATTRIBUTE4 in VARCHAR2,
467 X_ATTRIBUTE5 in VARCHAR2,
468 X_ATTRIBUTE6 in VARCHAR2,
469 X_ATTRIBUTE7 in VARCHAR2,
470 X_ATTRIBUTE8 in VARCHAR2,
471 X_ATTRIBUTE9 in VARCHAR2,
472 X_ATTRIBUTE10 in VARCHAR2,
473 X_ATTRIBUTE11 in VARCHAR2,
474 X_ATTRIBUTE12 in VARCHAR2,
475 X_ATTRIBUTE13 in VARCHAR2,
476 X_ATTRIBUTE14 in VARCHAR2,
477 X_ATTRIBUTE15 in VARCHAR2,
478 X_LAST_UPDATE_DATE in DATE,
479 X_LAST_UPDATED_BY in NUMBER,
480 X_LAST_UPDATE_LOGIN in NUMBER,
481 X_ANNUAL_BASIS_AMOUNT in NUMBER DEFAULT NULL --03-NOV-2003
482 )
483 IS
484
485 l_return_status VARCHAR2(30) := NULL;
486
487 BEGIN
488
489 PNP_DEBUG_PKG.debug ('PN_VAR_BKPTS_DET_PKG.UPDATE_ROW (+)');
490
494 (
491 -- Check for breakpoint volume ranges
492 l_return_status := NULL;
493 /*PN_VAR_BKPTS_DET_PKG.CHECK_VOL_START
495 l_return_status,
496 x_bkpt_header_id,
497 x_bkpt_detail_id,
498 X_PERIOD_BKPT_VOL_START
499 );
500 IF (l_return_status IS NOT NULL) THEN
501 APP_EXCEPTION.Raise_Exception;
502 END IF;*/
503
504 UPDATE PN_VAR_BKPTS_DET_ALL SET
505 BKPT_DETAIL_NUM = X_BKPT_DETAIL_NUM,
506 BKPT_HEADER_ID = X_BKPT_HEADER_ID,
507 BKPT_START_DATE = X_BKPT_START_DATE,
508 BKPT_END_DATE = X_BKPT_END_DATE,
509 PERIOD_BKPT_VOL_START = X_PERIOD_BKPT_VOL_START,
510 PERIOD_BKPT_VOL_END = X_PERIOD_BKPT_VOL_END,
511 GROUP_BKPT_VOL_START = X_GROUP_BKPT_VOL_START,
512 GROUP_BKPT_VOL_END = X_GROUP_BKPT_VOL_END,
513 BKPT_RATE = X_BKPT_RATE,
514 BKDT_DEFAULT_ID = X_BKDT_DEFAULT_ID,
515 VAR_RENT_ID = X_VAR_RENT_ID,
516 COMMENTS = X_COMMENTS,
517 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
518 ATTRIBUTE1 = X_ATTRIBUTE1,
519 ATTRIBUTE2 = X_ATTRIBUTE2,
520 ATTRIBUTE3 = X_ATTRIBUTE3,
521 ATTRIBUTE4 = X_ATTRIBUTE4,
522 ATTRIBUTE5 = X_ATTRIBUTE5,
523 ATTRIBUTE6 = X_ATTRIBUTE6,
524 ATTRIBUTE7 = X_ATTRIBUTE7,
525 ATTRIBUTE8 = X_ATTRIBUTE8,
526 ATTRIBUTE9 = X_ATTRIBUTE9,
527 ATTRIBUTE10 = X_ATTRIBUTE10,
528 ATTRIBUTE11 = X_ATTRIBUTE11,
529 ATTRIBUTE12 = X_ATTRIBUTE12,
530 ATTRIBUTE13 = X_ATTRIBUTE13,
531 ATTRIBUTE14 = X_ATTRIBUTE14,
532 ATTRIBUTE15 = X_ATTRIBUTE15,
533 BKPT_DETAIL_ID = X_BKPT_DETAIL_ID,
534 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
535 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
536 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
537 ANNUAL_BASIS_AMOUNT = X_ANNUAL_BASIS_AMOUNT --03-NOV-2003
538 WHERE BKPT_DETAIL_ID = X_BKPT_DETAIL_ID ;
539
540 IF (SQL%NOTFOUND) THEN
541 RAISE NO_DATA_FOUND;
542 END IF;
543
544 UPDATE pn_var_lines_all
545 SET bkpt_update_flag = 'Y'
546 WHERE line_item_id IN (SELECT line_item_id
547 FROM pn_var_bkpts_head_all
548 WHERE bkpt_header_id = x_bkpt_header_id);
549
550 PNP_DEBUG_PKG.debug ('PN_VAR_BKPTS_DET_PKG.UPDATE_ROW (+)');
551
552 END UPDATE_ROW;
553
554 -------------------------------------------------------------------------------
555 -- PROCDURE : DELETE_ROW
556 -- INVOKED FROM : DELETE_ROW procedure
557 -- PURPOSE : deletes the row
558 -- HISTORY :
559 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced PN_VAR_BKPTS_DET with _ALL table.
560 -------------------------------------------------------------------------------
561 procedure DELETE_ROW ( X_BKPT_DETAIL_ID in NUMBER)
562 IS
563
564 CURSOR line_item_cur IS
565 SELECT line_item_id
566 FROM pn_var_bkpts_head_all
567 WHERE Bkpt_Header_id = (SELECT bkpt_header_id
568 FROM pn_var_bkpts_det_all
569 WHERE bkpt_detail_id = x_bkpt_detail_id);
570
571 BEGIN
572
573 PNP_DEBUG_PKG.debug ('PN_VAR_LINES_PKG.DELETE_ROW (+)');
574
575 FOR rec IN line_item_cur LOOP
576 UPDATE pn_var_lines_all
577 SET bkpt_update_flag = 'Y'
578 WHERE line_item_id = rec.line_item_id;
579 END LOOP;
580
581 DELETE FROM PN_VAR_BKPTS_DET_ALL
582 WHERE BKPT_DETAIL_ID = X_BKPT_DETAIL_ID;
583
584 IF (SQL%NOTFOUND) THEN
585 RAISE NO_DATA_FOUND;
586 END IF;
587
588 PNP_DEBUG_PKG.debug ('PN_VAR_LINES_PKG.DELETE_ROW (-)');
589
590 END DELETE_ROW;
591
592 -------------------------------------------------------------------------------
593 -- PROCDURE : CHECK_VOL_START
594 -- INVOKED FROM :
595 -- PURPOSE :
596 -- HISTORY :
597 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced PN_VAR_BKPTS_DET with _ALL table.
598 -------------------------------------------------------------------------------
599 PROCEDURE CHECK_VOL_START
600 (
601 x_return_status in out NOCOPY varchar2,
602 x_bkpt_header_id in number,
603 x_bkpt_detail_id in number,
604 x_period_vol_start in number
605 )
606 IS
607 l_dummy NUMBER;
608 BEGIN
609 PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.CHECK_VOL_START (+)');
610
611 SELECT 1
612 INTO l_dummy
613 FROM dual
614 WHERE not exists
615 (
616 SELECT 1
617 FROM pn_var_bkpts_det_all bkd
618 WHERE NVL(bkd.PERIOD_BKPT_VOL_END,(x_period_vol_start+1)) > (x_period_vol_start)
619 AND ((x_bkpt_detail_id is null) or
620 (bkd.bkpt_detail_id <> x_bkpt_detail_id))
621 AND bkd.bkpt_header_id = x_bkpt_header_id
622 );
623
624 PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.CHECK_VOL_START (-)');
625
626 EXCEPTION
627 when NO_DATA_FOUND then
628 fnd_message.set_name ('PN','PN_VAR_WRONG_RANGE');
629 --fnd_message.set_token('RENT_NUMBER',
630 --x_rent_num);
631 x_return_status := 'E';
632 END CHECK_VOL_START;
633
634 END PN_VAR_BKPTS_DET_PKG;
[Home] [Help]