1 PACKAGE BODY PN_VAR_LINE_DEFAULTS_PKG AS
2 /* $Header: PNVRLDFB.pls 120.0 2007/10/03 14:29:08 rthumma noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9
10 -------------------------------------------------------------------------------
11
12 PROCEDURE INSERT_ROW (
13 X_ROWID in out NOCOPY VARCHAR2,
14 X_LINE_DEFAULT_ID in out NOCOPY NUMBER,
15 X_LINE_NUM in out NOCOPY NUMBER,
16 X_VAR_RENT_ID in NUMBER,
17 X_SALES_TYPE_CODE in VARCHAR2,
18 X_ITEM_CATEGORY_CODE in VARCHAR2,
19 X_LINE_TEMPLATE_ID in NUMBER,
20 X_AGREEMENT_TEMPLATE_ID in NUMBER,
21 X_LINE_START_DATE in DATE,
22 X_LINE_END_DATE in DATE,
23 X_PROCESSED_FLAG in NUMBER,
24 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_DATE in DATE,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER,
29 X_ORG_ID in NUMBER,
30 X_ATTRIBUTE_CATEGORY in VARCHAR2,
31 X_ATTRIBUTE1 in VARCHAR2,
32 X_ATTRIBUTE2 in VARCHAR2,
33 X_ATTRIBUTE3 in VARCHAR2,
34 X_ATTRIBUTE4 in VARCHAR2,
35 X_ATTRIBUTE5 in VARCHAR2,
36 X_ATTRIBUTE6 in VARCHAR2,
37 X_ATTRIBUTE7 in VARCHAR2,
38 X_ATTRIBUTE8 in VARCHAR2,
39 X_ATTRIBUTE9 in VARCHAR2,
40 X_ATTRIBUTE10 in VARCHAR2,
41 X_ATTRIBUTE11 in VARCHAR2,
42 X_ATTRIBUTE12 in VARCHAR2,
43 X_ATTRIBUTE13 in VARCHAR2,
44 X_ATTRIBUTE14 in VARCHAR2,
45 X_ATTRIBUTE15 in VARCHAR2
46 ) IS
47
48 CURSOR C is
49 select ROWID
50 from PN_VAR_LINE_DEFAULTS_ALL
51 where LINE_DEFAULT_ID = X_LINE_DEFAULT_ID
52 ;
53
54 begin
55
56
57 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.INSERT_ROW (+)');
58
59 -------------------------------------------------------
60 -- We need to generate the line number
61 -------------------------------------------------------
62 select nvl(max(lines.LINE_NUM),0)
63 into X_LINE_NUM
64 from PN_VAR_LINE_DEFAULTS_ALL lines
65 where lines.VAR_RENT_ID = X_VAR_RENT_ID;
66
67 X_LINE_NUM := X_LINE_NUM + 1;
68
69 -------------------------------------------------------
70 -- Select the nextval for line_bkpt default id
71 -------------------------------------------------------
72 IF ( X_LINE_DEFAULT_ID IS NULL) THEN
73 select PN_VAR_LINE_DEFAULTS_S.nextval
74 into X_LINE_DEFAULT_ID
75 from dual;
76 END IF;
77
78 insert into PN_VAR_LINE_DEFAULTS_ALL
79 (
80 LINE_DEFAULT_ID,
81 LINE_NUM,
82 VAR_RENT_ID,
83 SALES_TYPE_CODE,
84 ITEM_CATEGORY_CODE,
85 LINE_TEMPLATE_ID,
86 AGREEMENT_TEMPLATE_ID,
87 LINE_START_DATE,
88 LINE_END_DATE,
89 PROCESSED_FLAG,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_LOGIN,
95 ORG_ID,
96 ATTRIBUTE_CATEGORY,
97 ATTRIBUTE1,
98 ATTRIBUTE2,
99 ATTRIBUTE3,
100 ATTRIBUTE4,
101 ATTRIBUTE5,
102 ATTRIBUTE6,
103 ATTRIBUTE7,
104 ATTRIBUTE8,
105 ATTRIBUTE9,
106 ATTRIBUTE10,
107 ATTRIBUTE11,
108 ATTRIBUTE12,
109 ATTRIBUTE13,
110 ATTRIBUTE14,
111 ATTRIBUTE15
112 )
113 values
114 (
115 X_LINE_DEFAULT_ID,
116 X_LINE_NUM,
117 X_VAR_RENT_ID,
118 X_SALES_TYPE_CODE,
119 X_ITEM_CATEGORY_CODE,
120 X_LINE_TEMPLATE_ID,
121 X_AGREEMENT_TEMPLATE_ID,
122 X_LINE_START_DATE,
123 X_LINE_END_DATE,
124 X_PROCESSED_FLAG,
125 X_LAST_UPDATE_DATE,
126 X_LAST_UPDATED_BY,
127 X_CREATION_DATE,
128 X_CREATED_BY,
129 X_LAST_UPDATE_LOGIN,
130 X_ORG_ID,
131 X_ATTRIBUTE_CATEGORY,
132 X_ATTRIBUTE1,
133 X_ATTRIBUTE2,
134 X_ATTRIBUTE3,
135 X_ATTRIBUTE4,
136 X_ATTRIBUTE5,
137 X_ATTRIBUTE6,
138 X_ATTRIBUTE7,
139 X_ATTRIBUTE8,
140 X_ATTRIBUTE9,
141 X_ATTRIBUTE10,
142 X_ATTRIBUTE11,
143 X_ATTRIBUTE12,
144 X_ATTRIBUTE13,
145 X_ATTRIBUTE14,
146 X_ATTRIBUTE15
147 );
148
149 open c;
150 fetch c into X_ROWID;
151 if (c%notfound) then
152 close c;
153 raise no_data_found;
154 end if;
155 close c;
156
157 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.INSERT_ROW (-)');
158 exception
159 when others then
160 /*dbms_output.put_line(sqlerrm);*/
161 null;
162 end INSERT_ROW;
163
164 -------------------------------------------------------------------------------
165 -- PROCDURE : LOCK_ROW
166 -- INVOKED FROM : LOCK_ROW procedure
167 -- PURPOSE : locks the row
168 -- HISTORY :
169
170 -------------------------------------------------------------------------------
171 procedure LOCK_ROW (
172 X_LINE_DEFAULT_ID in NUMBER,
173 X_LINE_NUM in NUMBER,
174 X_VAR_RENT_ID in NUMBER,
175 X_SALES_TYPE_CODE in VARCHAR2,
176 X_ITEM_CATEGORY_CODE in VARCHAR2,
177 X_LINE_TEMPLATE_ID in NUMBER,
178 X_AGREEMENT_TEMPLATE_ID in NUMBER,
179 X_LINE_START_DATE in DATE,
180 X_LINE_END_DATE in DATE,
181 X_ATTRIBUTE_CATEGORY in VARCHAR2,
182 X_ATTRIBUTE1 in VARCHAR2,
183 X_ATTRIBUTE2 in VARCHAR2,
184 X_ATTRIBUTE3 in VARCHAR2,
185 X_ATTRIBUTE4 in VARCHAR2,
186 X_ATTRIBUTE5 in VARCHAR2,
187 X_ATTRIBUTE6 in VARCHAR2,
188 X_ATTRIBUTE7 in VARCHAR2,
189 X_ATTRIBUTE8 in VARCHAR2,
190 X_ATTRIBUTE9 in VARCHAR2,
191 X_ATTRIBUTE10 in VARCHAR2,
192 X_ATTRIBUTE11 in VARCHAR2,
193 X_ATTRIBUTE12 in VARCHAR2,
194 X_ATTRIBUTE13 in VARCHAR2,
195 X_ATTRIBUTE14 in VARCHAR2,
196 X_ATTRIBUTE15 in VARCHAR2
197 ) is
198
199 cursor c1 is select
200 *
201 from PN_VAR_LINE_DEFAULTS_ALL
202 where LINE_DEFAULT_ID = X_LINE_DEFAULT_ID
203 for update of LINE_DEFAULT_ID nowait;
204
205 begin
206
207 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.LOCK_ROW (+)');
208
209 for tlinfo in c1 loop
210
211 if ((tlinfo.SALES_TYPE_CODE = X_SALES_TYPE_CODE)
212 OR ((tlinfo.SALES_TYPE_CODE is null) AND (X_SALES_TYPE_CODE is null))) then
213 null;
214 else
215 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('SALES_TYPE_CODE', tlinfo.SALES_TYPE_CODE);
216 end if;
217
218 if ((tlinfo.ITEM_CATEGORY_CODE = X_ITEM_CATEGORY_CODE)
219 OR ((tlinfo.ITEM_CATEGORY_CODE is null) AND (X_ITEM_CATEGORY_CODE is null))) then
220 null;
221 else
222 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ITEM_CATEGORY_CODE', tlinfo.ITEM_CATEGORY_CODE);
223 End if;
224
225 if ((tlinfo.LINE_TEMPLATE_ID = X_LINE_TEMPLATE_ID)
226 OR ((tlinfo.LINE_TEMPLATE_ID is null) AND (X_LINE_TEMPLATE_ID is null))) then
227 null;
228 else
229 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('LINE_TEMPLATE_ID', to_char(tlinfo.LINE_TEMPLATE_ID));
230 end if;
231
232 if ((tlinfo.AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID)
233 OR ((tlinfo.AGREEMENT_TEMPLATE_ID is null) AND (X_AGREEMENT_TEMPLATE_ID is null))) then
234 null;
235 else
236 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('AGREEMENT_TEMPLATE_ID', to_char(tlinfo.AGREEMENT_TEMPLATE_ID));
237 end if;
238
239 if ((tlinfo.LINE_START_DATE = X_LINE_START_DATE)
240 OR ((tlinfo.LINE_START_DATE is null) AND (X_LINE_START_DATE is null))) then
241 null;
242 else
243 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('LINE_START_DATE', to_char(tlinfo.LINE_START_DATE));
244 end if;
245
246 if ((tlinfo.LINE_END_DATE = X_LINE_END_DATE)
247 OR ((tlinfo.LINE_END_DATE is null) AND (X_LINE_END_DATE is null))) then
248 null;
249 else
250 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('LINE_END_DATE', to_char(tlinfo.LINE_END_DATE));
251 end if;
252
253 if ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
254 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) then
255 null;
256 else
257 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY', to_char(tlinfo.ATTRIBUTE_CATEGORY));
258 end if;
259
260
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', to_char(tlinfo.ATTRIBUTE1));
266 end if;
267
268 if ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
269 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) then
270 null;
271 else
272 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2', to_char(tlinfo.ATTRIBUTE2));
273 end if;
274
275 if ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
276 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) then
277 null;
278 else
279 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3', to_char(tlinfo.ATTRIBUTE3));
280 end if;
281
282 if ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
283 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) then
284 null;
285 else
286 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4', to_char(tlinfo.ATTRIBUTE4));
287 end if;
288
289 if ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
290 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) then
291 null;
292 else
293 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5', to_char(tlinfo.ATTRIBUTE5));
294 end if;
295
296 if ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
297 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) then
298 null;
299 else
300 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6', to_char(tlinfo.ATTRIBUTE6));
301 end if;
302
303 if ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
304 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) then
305 null;
306 else
307 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7', to_char(tlinfo.ATTRIBUTE7));
308 end if;
309
310 if ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
311 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) then
312 null;
313 else
314 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8', to_char(tlinfo.ATTRIBUTE8));
315 end if;
316
317 if ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
318 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) then
319 null;
320 else
321 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9', to_char(tlinfo.ATTRIBUTE9));
322 end if;
323
324 if ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
325 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) then
326 null;
327 else
328 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10', to_char(tlinfo.ATTRIBUTE10));
329 end if;
330
331 if ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
332 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) then
333 null;
334 else
335 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11', to_char(tlinfo.ATTRIBUTE11));
336 end if;
337
338 if ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
339 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) then
340 null;
341 else
342 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12', to_char(tlinfo.ATTRIBUTE12));
343 end if;
344
345 if ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
346 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) then
347 null;
348 else
349 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13', to_char(tlinfo.ATTRIBUTE13));
350 end if;
351
352 if ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
353 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) then
354 null;
355 else
356 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14', to_char(tlinfo.ATTRIBUTE14));
357 end if;
358
359 if ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
360 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) then
361 null;
362 else
363 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15', to_char(tlinfo.ATTRIBUTE15));
364 end if;
365
366 END LOOP;
367
368 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.LOCK_ROW (-)');
369
370 end LOCK_ROW;
371
372 -------------------------------------------------------------------------------
373 -- PROCDURE : UPDATE_ROW
374 -- INVOKED FROM : UPDATE_ROW procedure
375 -- PURPOSE : updates the row
376 -- HISTORY :
377
378 -------------------------------------------------------------------------------
379 procedure UPDATE_ROW (
380 X_LINE_DEFAULT_ID in NUMBER,
381 X_LINE_NUM in NUMBER,
382 X_VAR_RENT_ID in NUMBER,
383 X_SALES_TYPE_CODE in VARCHAR2,
384 X_ITEM_CATEGORY_CODE in VARCHAR2,
385 X_LINE_TEMPLATE_ID in NUMBER,
386 X_AGREEMENT_TEMPLATE_ID in NUMBER,
387 X_LINE_START_DATE in DATE,
388 X_LINE_END_DATE in DATE,
389 X_PROCESSED_FLAG in NUMBER,
390 X_LAST_UPDATE_DATE in DATE,
391 X_LAST_UPDATED_BY in NUMBER,
392 X_LAST_UPDATE_LOGIN in NUMBER,
393 X_ATTRIBUTE_CATEGORY in VARCHAR2,
394 X_ATTRIBUTE1 in VARCHAR2,
395 X_ATTRIBUTE2 in VARCHAR2,
396 X_ATTRIBUTE3 in VARCHAR2,
397 X_ATTRIBUTE4 in VARCHAR2,
398 X_ATTRIBUTE5 in VARCHAR2,
399 X_ATTRIBUTE6 in VARCHAR2,
400 X_ATTRIBUTE7 in VARCHAR2,
401 X_ATTRIBUTE8 in VARCHAR2,
402 X_ATTRIBUTE9 in VARCHAR2,
403 X_ATTRIBUTE10 in VARCHAR2,
404 X_ATTRIBUTE11 in VARCHAR2,
405 X_ATTRIBUTE12 in VARCHAR2,
406 X_ATTRIBUTE13 in VARCHAR2,
407 X_ATTRIBUTE14 in VARCHAR2,
408 X_ATTRIBUTE15 in VARCHAR2
409 ) is
410
411 begin
412
413 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.UPDATE_ROW (+)');
414
415 update PN_VAR_LINE_DEFAULTS_ALL set
416 LINE_NUM = X_LINE_NUM,
417 VAR_RENT_ID = X_VAR_RENT_ID,
418 SALES_TYPE_CODE = X_SALES_TYPE_CODE,
419 ITEM_CATEGORY_CODE = X_ITEM_CATEGORY_CODE,
420 LINE_TEMPLATE_ID = X_LINE_TEMPLATE_ID,
421 AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID,
422 LINE_START_DATE = X_LINE_START_DATE,
423 LINE_END_DATE = X_LINE_END_DATE,
424 LINE_DEFAULT_ID = X_LINE_DEFAULT_ID,
425 PROCESSED_FLAG = X_PROCESSED_FLAG,
426 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
427 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
428 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
429 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
430 ATTRIBUTE1 = X_ATTRIBUTE1,
431 ATTRIBUTE2 = X_ATTRIBUTE2,
432 ATTRIBUTE3 = X_ATTRIBUTE3,
433 ATTRIBUTE4 = X_ATTRIBUTE4,
434 ATTRIBUTE5 = X_ATTRIBUTE5,
435 ATTRIBUTE6 = X_ATTRIBUTE6,
436 ATTRIBUTE7 = X_ATTRIBUTE7,
437 ATTRIBUTE8 = X_ATTRIBUTE8,
438 ATTRIBUTE9 = X_ATTRIBUTE9,
439 ATTRIBUTE10 = X_ATTRIBUTE10,
440 ATTRIBUTE11 = X_ATTRIBUTE11,
441 ATTRIBUTE12 = X_ATTRIBUTE12,
442 ATTRIBUTE13 = X_ATTRIBUTE13,
443 ATTRIBUTE14 = X_ATTRIBUTE14,
444 ATTRIBUTE15 = X_ATTRIBUTE15
445 where LINE_DEFAULT_ID = X_LINE_DEFAULT_ID;
446
447 if (sql%notfound) then
448 raise no_data_found;
449 end if;
450
451 --Srini
452 UPDATE pn_var_lines_all
453 SET sales_type_code = X_SALES_TYPE_CODE,
454 item_category_code = X_ITEM_CATEGORY_CODE
455 WHERE var_rent_id = X_VAR_RENT_ID
456 AND line_default_id = X_LINE_DEFAULT_ID;
457
458 /* Might not have var_lines created
459 IF (SQL%NOTFOUND) THEN
460 RAISE NO_DATA_FOUND;
461 END IF;
462 */
463
464 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.UPDATE_ROW (-)');
465
466 end UPDATE_ROW;
467
468 -----------------------------------------------------------------------
469 -- PROCDURE : DELETE_ROW
470 -----------------------------------------------------------------------
471 procedure DELETE_ROW ( X_LINE_DEFAULT_ID in NUMBER )
472 IS
473
474 CURSOR line_items IS
475 SELECT line_item_id
476 FROM pn_var_lines_all
477 WHERE line_default_id = x_line_default_id;
478
479 BEGIN
480
481 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.DELETE_ROW (+)');
482
483 FOR rec IN line_items LOOP
484 pn_var_lines_pkg.delete_row (rec.line_item_id);
485 END LOOP;
486
487 DELETE FROM PN_VAR_LINE_DEFAULTS_ALL
488 WHERE LINE_DEFAULT_ID = X_LINE_DEFAULT_ID;
489
490 IF (sql%notfound) THEN
491 RAISE NO_DATA_FOUND;
492 END IF;
493
494 PNP_DEBUG_PKG.debug ('PN_VAR_LINE_DEFAULTS_PKG.DELETE_ROW (-)');
495
496 END DELETE_ROW;
497
498 end PN_VAR_LINE_DEFAULTS_PKG;
[Home] [Help]