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