1 package body PN_VAR_TEMPLATE_PKG as
2 /* $Header: PNVRTEMB.pls 120.3 2006/09/14 04:10:05 pikhar noship $*/
3
4 -----------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -----------------------------------------------------------------------
7
8 procedure INSERT_ROW (
9 X_ROWID in out NOCOPY VARCHAR2,
10 X_AGREEMENT_TEMPLATE_ID in out NOCOPY NUMBER,
11 X_AGREEMENT_TEMPLATE in VARCHAR2,
12 X_PURPOSE_CODE in VARCHAR2,
13 X_TYPE_CODE in VARCHAR2,
14 X_CUMULATIVE_VOL in VARCHAR2,
15 X_INVOICE_ON in VARCHAR2,
16 X_NEGATIVE_RENT in VARCHAR2,
17 X_TERM_TEMPLATE_ID in NUMBER,
18 X_ABATEMENT_AMOUNT in NUMBER,
19 X_PRORATION_RULE in VARCHAR2,
20 X_PERIOD_FREQ_CODE in VARCHAR2,
21 X_USE_GL_CALENDAR in VARCHAR2,
22 X_YEAR_START_DATE in DATE,
23 X_GL_PERIOD_SET_NAME in VARCHAR2,
24 X_PERIOD_TYPE in VARCHAR2,
25 X_REPTG_FREQ_CODE in VARCHAR2,
26 X_REPTG_DAY_OF_MONTH in NUMBER,
27 X_REPTG_DAYS_AFTER in NUMBER,
28 X_INVG_FREQ_CODE in VARCHAR2,
29 X_INVG_SPREAD_CODE in VARCHAR2,
30 X_INVG_DAY_OF_MONTH in NUMBER,
31 X_INVG_DAYS_AFTER in NUMBER,
32 X_COMMENTS in VARCHAR2,
33 X_ORG_ID in NUMBER,
34 X_CREATION_DATE in DATE,
35 X_CREATED_BY in NUMBER,
36 X_LAST_UPDATE_DATE in DATE,
37 X_LAST_UPDATED_BY in NUMBER,
38 X_LAST_UPDATE_LOGIN in NUMBER,
39 X_VRG_REPTG_FREQ_CODE in VARCHAR2,
40 X_ATTRIBUTE_CATEGORY in VARCHAR2,
41 X_ATTRIBUTE1 in VARCHAR2,
42 X_ATTRIBUTE2 in VARCHAR2,
43 X_ATTRIBUTE3 in VARCHAR2,
44 X_ATTRIBUTE4 in VARCHAR2,
45 X_ATTRIBUTE5 in VARCHAR2,
46 X_ATTRIBUTE6 in VARCHAR2,
47 X_ATTRIBUTE7 in VARCHAR2,
48 X_ATTRIBUTE8 in VARCHAR2,
49 X_ATTRIBUTE9 in VARCHAR2,
50 X_ATTRIBUTE10 in VARCHAR2,
51 X_ATTRIBUTE11 in VARCHAR2,
52 X_ATTRIBUTE12 in VARCHAR2,
53 X_ATTRIBUTE13 in VARCHAR2,
54 X_ATTRIBUTE14 in VARCHAR2,
55 X_ATTRIBUTE15 in VARCHAR2
56 ) is
57
58 cursor C is
59 select ROWID
60 from PN_VAR_TEMPLATES_ALL
61 where AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID;
62
63 l_return_status VARCHAR2(30) := NULL;
64
65 BEGIN
66
67 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.INSERT_ROW (+)');
68
69 IF ( X_AGREEMENT_TEMPLATE_ID IS NULL) THEN
70 Select PN_VAR_TEMPLATES_S.nextval
71 into X_AGREEMENT_TEMPLATE_ID
72 FROM DUAL;
73 END IF;
74
75 insert into PN_VAR_TEMPLATES_ALL (
76 AGREEMENT_TEMPLATE_ID,
77 AGREEMENT_TEMPLATE,
78 PURPOSE_CODE,
79 TYPE_CODE,
80 CUMULATIVE_VOL,
81 INVOICE_ON,
82 NEGATIVE_RENT,
83 TERM_TEMPLATE_ID,
84 ABATEMENT_AMOUNT,
85 PRORATION_RULE,
86 PERIOD_FREQ_CODE,
87 USE_GL_CALENDAR,
88 YEAR_START_DATE,
89 GL_PERIOD_SET_NAME,
90 PERIOD_TYPE,
91 REPTG_FREQ_CODE,
92 REPTG_DAY_OF_MONTH,
93 REPTG_DAYS_AFTER,
94 INVG_FREQ_CODE,
95 INVG_SPREAD_CODE,
96 INVG_DAY_OF_MONTH,
97 INVG_DAYS_AFTER,
98 COMMENTS,
99 ORG_ID,
100 LAST_UPDATE_DATE,
101 LAST_UPDATED_BY,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_LOGIN,
105 VRG_REPTG_FREQ_CODE,
106 ATTRIBUTE_CATEGORY,
107 ATTRIBUTE1,
108 ATTRIBUTE2,
109 ATTRIBUTE3,
110 ATTRIBUTE4,
111 ATTRIBUTE5,
112 ATTRIBUTE6,
113 ATTRIBUTE7,
114 ATTRIBUTE8,
115 ATTRIBUTE9,
116 ATTRIBUTE10,
117 ATTRIBUTE11,
118 ATTRIBUTE12,
119 ATTRIBUTE13,
120 ATTRIBUTE14,
121 ATTRIBUTE15
122 )
123 values
124 (
125 X_AGREEMENT_TEMPLATE_ID,
126 X_AGREEMENT_TEMPLATE,
127 X_PURPOSE_CODE,
128 X_TYPE_CODE,
129 X_CUMULATIVE_VOL,
130 X_INVOICE_ON,
131 X_NEGATIVE_RENT,
132 X_TERM_TEMPLATE_ID,
133 X_ABATEMENT_AMOUNT,
134 X_PRORATION_RULE,
135 X_PERIOD_FREQ_CODE,
136 X_USE_GL_CALENDAR,
137 X_YEAR_START_DATE,
138 X_GL_PERIOD_SET_NAME,
139 X_PERIOD_TYPE,
140 X_REPTG_FREQ_CODE,
141 X_REPTG_DAY_OF_MONTH,
142 X_REPTG_DAYS_AFTER,
143 X_INVG_FREQ_CODE,
144 X_INVG_SPREAD_CODE,
145 X_INVG_DAY_OF_MONTH,
146 X_INVG_DAYS_AFTER,
147 X_COMMENTS,
148 X_ORG_ID,
149 X_LAST_UPDATE_DATE,
150 X_LAST_UPDATED_BY,
151 X_CREATION_DATE,
152 X_CREATED_BY,
153 X_LAST_UPDATE_LOGIN,
154 X_VRG_REPTG_FREQ_CODE,
155 X_ATTRIBUTE_CATEGORY,
156 X_ATTRIBUTE1,
157 X_ATTRIBUTE2,
158 X_ATTRIBUTE3,
159 X_ATTRIBUTE4,
160 X_ATTRIBUTE5,
161 X_ATTRIBUTE6,
162 X_ATTRIBUTE7,
163 X_ATTRIBUTE8,
164 X_ATTRIBUTE9,
165 X_ATTRIBUTE10,
166 X_ATTRIBUTE11,
167 X_ATTRIBUTE12,
168 X_ATTRIBUTE13,
169 X_ATTRIBUTE14,
170 X_ATTRIBUTE15);
171
172 open c;
173 fetch c into X_ROWID;
174 if (c%notfound) then
175 close c;
176 raise no_data_found;
177 end if;
178 close c;
179
180 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.INSERT_ROW (-)');
181
182 end INSERT_ROW;
183
184
185 -----------------------------------------------------------------------
186 -- PROCDURE : LOCK_ROW
187 -----------------------------------------------------------------------
188
189 procedure LOCK_ROW (
190 X_AGREEMENT_TEMPLATE_ID in NUMBER,
191 X_AGREEMENT_TEMPLATE in VARCHAR2,
192 X_PURPOSE_CODE in VARCHAR2,
193 X_TYPE_CODE in VARCHAR2,
194 X_CUMULATIVE_VOL in VARCHAR2,
195 X_INVOICE_ON in VARCHAR2,
196 X_NEGATIVE_RENT in VARCHAR2,
197 X_TERM_TEMPLATE_ID in NUMBER,
198 X_ABATEMENT_AMOUNT in NUMBER,
199 X_PRORATION_RULE in VARCHAR2,
200 X_PERIOD_FREQ_CODE in VARCHAR2,
201 X_USE_GL_CALENDAR in VARCHAR2,
202 X_YEAR_START_DATE in DATE,
203 X_GL_PERIOD_SET_NAME in VARCHAR2,
204 X_PERIOD_TYPE in VARCHAR2,
205 X_REPTG_FREQ_CODE in VARCHAR2,
206 X_REPTG_DAY_OF_MONTH in NUMBER,
207 X_REPTG_DAYS_AFTER in NUMBER,
208 X_INVG_FREQ_CODE in VARCHAR2,
209 X_INVG_SPREAD_CODE in VARCHAR2,
210 X_INVG_DAY_OF_MONTH in NUMBER,
211 X_INVG_DAYS_AFTER in NUMBER,
212 X_COMMENTS in VARCHAR2,
213 X_VRG_REPTG_FREQ_CODE in VARCHAR2,
214 X_ATTRIBUTE_CATEGORY in VARCHAR2,
215 X_ATTRIBUTE1 in VARCHAR2,
216 X_ATTRIBUTE2 in VARCHAR2,
217 X_ATTRIBUTE3 in VARCHAR2,
218 X_ATTRIBUTE4 in VARCHAR2,
219 X_ATTRIBUTE5 in VARCHAR2,
220 X_ATTRIBUTE6 in VARCHAR2,
221 X_ATTRIBUTE7 in VARCHAR2,
222 X_ATTRIBUTE8 in VARCHAR2,
223 X_ATTRIBUTE9 in VARCHAR2,
224 X_ATTRIBUTE10 in VARCHAR2,
225 X_ATTRIBUTE11 in VARCHAR2,
226 X_ATTRIBUTE12 in VARCHAR2,
227 X_ATTRIBUTE13 in VARCHAR2,
228 X_ATTRIBUTE14 in VARCHAR2,
229 X_ATTRIBUTE15 in VARCHAR2
230 ) is
231 cursor c1
232 is
233 select *
234 from PN_VAR_TEMPLATES_ALL
235 where AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID
236 for update of AGREEMENT_TEMPLATE_ID nowait;
237
238 tlinfo c1%rowtype;
239
240 BEGIN
241 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.LOCK_ROW (+)');
242
243 open c1;
244 fetch c1 into tlinfo;
245 if (c1%notfound) then
246 close c1;
247 return;
248 end if;
249 close c1;
250
251
252 if ((tlinfo.AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID) OR
253 ((tlinfo.AGREEMENT_TEMPLATE_ID is null) AND (X_AGREEMENT_TEMPLATE_ID is null))) then
254 null;
255 else
256 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('AGREEMENT_TEMPLATE_ID',
257 to_char(tlinfo.AGREEMENT_TEMPLATE_ID));
258 end if;
259
260 if ((tlinfo.AGREEMENT_TEMPLATE = X_AGREEMENT_TEMPLATE) OR
261 ((tlinfo.AGREEMENT_TEMPLATE is null) AND (X_AGREEMENT_TEMPLATE is null))) then
262 null;
263 else
264 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('AGREEMENT_TEMPLATE', tlinfo.AGREEMENT_TEMPLATE);
265 end if;
266
267 if ((tlinfo.PURPOSE_CODE = X_PURPOSE_CODE) OR
268 ((tlinfo.PURPOSE_CODE is null) AND (X_PURPOSE_CODE is null))) then
269 null;
270 else
271 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PURPOSE_CODE', tlinfo.PURPOSE_CODE);
272 end if;
273
274 if ((tlinfo.TYPE_CODE = X_TYPE_CODE) OR
275 ((tlinfo.TYPE_CODE is null) AND (X_TYPE_CODE is null))) then
276 null;
277 else
278 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('TYPE_CODE', tlinfo.TYPE_CODE);
279 end if;
280
281 if ((tlinfo.CUMULATIVE_VOL = X_CUMULATIVE_VOL) OR
282 ((tlinfo.CUMULATIVE_VOL is null) AND (X_CUMULATIVE_VOL is null))) then
283 null;
284 else
285 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('CUMULATIVE_VOL', tlinfo.CUMULATIVE_VOL);
286 end if;
287
288 if ((tlinfo.INVOICE_ON = X_INVOICE_ON) OR
289 ((tlinfo.INVOICE_ON is null) AND (X_INVOICE_ON is null))) then
290 null;
291 else
292 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('INVOICE_ON', tlinfo.INVOICE_ON);
293 end if;
294
295 if ((tlinfo.NEGATIVE_RENT = X_NEGATIVE_RENT) OR
296 ((tlinfo.NEGATIVE_RENT is null) AND (X_NEGATIVE_RENT is null))) then
297 null;
298 else
299 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('NEGATIVE_RENT', tlinfo.NEGATIVE_RENT);
300 end if;
301
302 if ((tlinfo.TERM_TEMPLATE_ID = X_TERM_TEMPLATE_ID) OR
303 ((tlinfo.TERM_TEMPLATE_ID is null) AND
304 (X_TERM_TEMPLATE_ID is null))) then
305 null;
306 else
307 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('TERM_TEMPLATE_ID',
308 to_char(tlinfo.TERM_TEMPLATE_ID));
309 end if;
310
311 if ((tlinfo.ABATEMENT_AMOUNT = X_ABATEMENT_AMOUNT) OR
312 ((tlinfo.ABATEMENT_AMOUNT is null) AND
313 (X_ABATEMENT_AMOUNT is null))) then
314 null;
315 else
316 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ABATEMENT_AMOUNT',
317 to_char(tlinfo.ABATEMENT_AMOUNT));
318 end if;
319
320 if ((tlinfo.PRORATION_RULE = X_PRORATION_RULE) OR
321 ((tlinfo.PRORATION_RULE is null) AND (X_PRORATION_RULE is null))) then
322 null;
323 else
324 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PRORATION_RULE', tlinfo.PRORATION_RULE);
325 end if;
326
327 if ((tlinfo.PERIOD_FREQ_CODE = X_PERIOD_FREQ_CODE) OR
328 ((tlinfo.PERIOD_FREQ_CODE is null) AND (X_PERIOD_FREQ_CODE is null))) then
329 null;
330 else
331 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PERIOD_FREQ_CODE', tlinfo.PERIOD_FREQ_CODE);
332 end if;
333
334 if ((tlinfo.USE_GL_CALENDAR = X_USE_GL_CALENDAR) OR
335 ((tlinfo.USE_GL_CALENDAR is null) AND (X_USE_GL_CALENDAR is null))) then
336 null;
337 else
338 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('USE_GL_CALENDAR', tlinfo.USE_GL_CALENDAR);
339 end if;
340
341 if ((tlinfo.YEAR_START_DATE = X_YEAR_START_DATE) OR
342 ((tlinfo.YEAR_START_DATE is null) AND (X_YEAR_START_DATE is null))) then
343 null;
344 else
345 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('YEAR_START_DATE',
346 to_char(tlinfo.YEAR_START_DATE));
347 end if;
348
349 if ((tlinfo.GL_PERIOD_SET_NAME = X_GL_PERIOD_SET_NAME) OR
350 ((tlinfo.GL_PERIOD_SET_NAME is null) AND (X_GL_PERIOD_SET_NAME is null))) then
351 null;
352 else
353 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('GL_PERIOD_SET_NAME', tlinfo.GL_PERIOD_SET_NAME);
354 end if;
355 if ((tlinfo.PERIOD_TYPE = X_PERIOD_TYPE) OR
356 ((tlinfo.PERIOD_TYPE is null) AND (X_PERIOD_TYPE is null))) then
357 null;
358 else
359 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('PERIOD_TYPE', tlinfo.PERIOD_TYPE);
360 end if;
361 if ((tlinfo.REPTG_FREQ_CODE = X_REPTG_FREQ_CODE) OR
362 ((tlinfo.REPTG_FREQ_CODE is null) AND (X_REPTG_FREQ_CODE is null))) then
363 null;
364 else
365 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('REPTG_FREQ_CODE', tlinfo.REPTG_FREQ_CODE);
366 end if;
367 if ((tlinfo.REPTG_DAY_OF_MONTH = X_REPTG_DAY_OF_MONTH) OR
368 ((tlinfo.REPTG_DAY_OF_MONTH is null) AND (X_REPTG_DAY_OF_MONTH is null))) then
369 null;
370 else
371 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('REPTG_DAY_OF_MONTH',
372 to_char(tlinfo.REPTG_DAY_OF_MONTH));
373 end if;
374 if ((tlinfo.REPTG_DAYS_AFTER = X_REPTG_DAYS_AFTER) OR
375 ((tlinfo.REPTG_DAYS_AFTER is null) AND (X_REPTG_DAYS_AFTER is null))) then
376 null;
377 else
378 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('REPTG_DAYS_AFTER',
379 to_char(tlinfo.REPTG_DAYS_AFTER));
380 end if;
381 if ((tlinfo.INVG_FREQ_CODE = X_INVG_FREQ_CODE) OR
382 ((tlinfo.INVG_FREQ_CODE is null) AND (X_INVG_FREQ_CODE is null))) then
383 null;
384 else
385 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('INVG_FREQ_CODE', tlinfo.INVG_FREQ_CODE);
386 end if;
387
388 if ((tlinfo.INVG_SPREAD_CODE = X_INVG_SPREAD_CODE) OR
389 ((tlinfo.INVG_SPREAD_CODE is null) AND (X_INVG_SPREAD_CODE is null))) then
390 null;
391 else
392 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('INVG_SPREAD_CODE', tlinfo.INVG_SPREAD_CODE);
393 end if;
394
395 if ((tlinfo.INVG_DAY_OF_MONTH = X_INVG_DAY_OF_MONTH) OR
396 ((tlinfo.INVG_DAY_OF_MONTH is null) AND (X_INVG_DAY_OF_MONTH is null))) then
397 null;
398 else
399 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('INVG_DAY_OF_MONTH',
400 to_char(tlinfo.INVG_DAY_OF_MONTH));
401 end if;
402
403 if ((tlinfo.INVG_DAYS_AFTER = X_INVG_DAYS_AFTER) OR
404 ((tlinfo.INVG_DAYS_AFTER is null) AND (X_INVG_DAYS_AFTER is null))) then
405 null;
406 else
407 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('INVG_DAYS_AFTER',
408 to_char(tlinfo.INVG_DAYS_AFTER));
409 end if;
410
411 if ((tlinfo.COMMENTS = X_COMMENTS) OR
412 ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null))) then
413 null;
414 else
415 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('COMMENTS', tlinfo.COMMENTS);
416 end if;
417
418 if ((tlinfo.VRG_REPTG_FREQ_CODE = X_VRG_REPTG_FREQ_CODE) OR
419 ((tlinfo.VRG_REPTG_FREQ_CODE is null) AND (X_VRG_REPTG_FREQ_CODE is null))) then
420 null;
421 else
422 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('VRG_REPTG_FREQ_CODE', tlinfo.VRG_REPTG_FREQ_CODE);
423 end if;
424
425 if ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
426 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) then
427 null;
428 else
429 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY', to_char(tlinfo.ATTRIBUTE_CATEGORY));
430 end if;
431
432
433 if ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
434 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) then
435 null;
436 else
437 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE1', to_char(tlinfo.ATTRIBUTE1));
438 end if;
439
440 if ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
441 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) then
442 null;
443 else
444 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2', to_char(tlinfo.ATTRIBUTE2));
445 end if;
446
447 if ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
448 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) then
449 null;
450 else
451 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3', to_char(tlinfo.ATTRIBUTE3));
452 end if;
453
454 if ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
455 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) then
456 null;
457 else
458 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4', to_char(tlinfo.ATTRIBUTE4));
459 end if;
460
461 if ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
462 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) then
463 null;
464 else
465 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5', to_char(tlinfo.ATTRIBUTE5));
466 end if;
467
468 if ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
469 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) then
470 null;
471 else
472 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6', to_char(tlinfo.ATTRIBUTE6));
473 end if;
474
475 if ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
476 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) then
477 null;
478 else
479 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7', to_char(tlinfo.ATTRIBUTE7));
480 end if;
481
482 if ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
483 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) then
484 null;
485 else
486 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8', to_char(tlinfo.ATTRIBUTE8));
487 end if;
488
489 if ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
490 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) then
491 null;
492 else
493 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9', to_char(tlinfo.ATTRIBUTE9));
494 end if;
495
496 if ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
497 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) then
498 null;
499 else
500 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10', to_char(tlinfo.ATTRIBUTE10));
501 end if;
502
503 if ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
504 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) then
505 null;
506 else
507 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11', to_char(tlinfo.ATTRIBUTE11));
508 end if;
509
510 if ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
511 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) then
512 null;
513 else
514 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12', to_char(tlinfo.ATTRIBUTE12));
515 end if;
516
517 if ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
518 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) then
519 null;
520 else
521 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13', to_char(tlinfo.ATTRIBUTE13));
522 end if;
523
524 if ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
525 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) then
526 null;
527 else
528 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14', to_char(tlinfo.ATTRIBUTE14));
529 end if;
530
531 if ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
532 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) then
533 null;
534 else
535 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15', to_char(tlinfo.ATTRIBUTE15));
536 end if;
537
538 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.LOCK_ROW (-)');
539
540 end LOCK_ROW;
541
542
543 -----------------------------------------------------------------------
544 -- PROCEDURE : UPDATE_ROW
545 -----------------------------------------------------------------------
546
547 procedure UPDATE_ROW (
548 X_AGREEMENT_TEMPLATE_ID in NUMBER,
549 X_AGREEMENT_TEMPLATE in VARCHAR2,
550 X_PURPOSE_CODE in VARCHAR2,
551 X_TYPE_CODE in VARCHAR2,
552 X_CUMULATIVE_VOL in VARCHAR2,
553 X_INVOICE_ON in VARCHAR2,
554 X_NEGATIVE_RENT in VARCHAR2,
555 X_TERM_TEMPLATE_ID in NUMBER,
556 X_ABATEMENT_AMOUNT in NUMBER,
557 X_PRORATION_RULE in VARCHAR2,
558 X_PERIOD_FREQ_CODE in VARCHAR2,
559 X_USE_GL_CALENDAR in VARCHAR2,
560 X_YEAR_START_DATE in DATE,
561 X_GL_PERIOD_SET_NAME in VARCHAR2,
562 X_PERIOD_TYPE in VARCHAR2,
563 X_REPTG_FREQ_CODE in VARCHAR2,
564 X_REPTG_DAY_OF_MONTH in NUMBER,
565 X_REPTG_DAYS_AFTER in NUMBER,
566 X_INVG_FREQ_CODE in VARCHAR2,
567 X_INVG_SPREAD_CODE in VARCHAR2,
568 X_INVG_DAY_OF_MONTH in NUMBER,
569 X_INVG_DAYS_AFTER in NUMBER,
570 X_COMMENTS in VARCHAR2,
571 X_LAST_UPDATE_DATE in DATE,
572 X_LAST_UPDATED_BY in NUMBER,
573 X_LAST_UPDATE_LOGIN in NUMBER,
574 X_VRG_REPTG_FREQ_CODE in VARCHAR2,
575 X_ATTRIBUTE_CATEGORY in VARCHAR2,
576 X_ATTRIBUTE1 in VARCHAR2,
577 X_ATTRIBUTE2 in VARCHAR2,
578 X_ATTRIBUTE3 in VARCHAR2,
579 X_ATTRIBUTE4 in VARCHAR2,
580 X_ATTRIBUTE5 in VARCHAR2,
581 X_ATTRIBUTE6 in VARCHAR2,
582 X_ATTRIBUTE7 in VARCHAR2,
583 X_ATTRIBUTE8 in VARCHAR2,
584 X_ATTRIBUTE9 in VARCHAR2,
585 X_ATTRIBUTE10 in VARCHAR2,
586 X_ATTRIBUTE11 in VARCHAR2,
587 X_ATTRIBUTE12 in VARCHAR2,
588 X_ATTRIBUTE13 in VARCHAR2,
589 X_ATTRIBUTE14 in VARCHAR2,
590 X_ATTRIBUTE15 in VARCHAR2
591 )
592 is
593
594 l_return_status VARCHAR2(30) := NULL;
595
596 BEGIN
597
598 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.UPDATE_ROW (+)');
599
600 update PN_VAR_TEMPLATES_ALL set
601 AGREEMENT_TEMPLATE = X_AGREEMENT_TEMPLATE,
602 PURPOSE_CODE = X_PURPOSE_CODE,
603 TYPE_CODE = X_TYPE_CODE,
604 CUMULATIVE_VOL = X_CUMULATIVE_VOL,
605 INVOICE_ON = X_INVOICE_ON,
606 NEGATIVE_RENT = X_NEGATIVE_RENT,
607 TERM_TEMPLATE_ID = X_TERM_TEMPLATE_ID,
608 ABATEMENT_AMOUNT = X_ABATEMENT_AMOUNT,
609 PRORATION_RULE = X_PRORATION_RULE,
610 PERIOD_FREQ_CODE = X_PERIOD_FREQ_CODE,
611 USE_GL_CALENDAR = X_USE_GL_CALENDAR,
612 YEAR_START_DATE = X_YEAR_START_DATE,
613 GL_PERIOD_SET_NAME = X_GL_PERIOD_SET_NAME,
614 PERIOD_TYPE = X_PERIOD_TYPE,
615 REPTG_FREQ_CODE = X_REPTG_FREQ_CODE,
616 REPTG_DAY_OF_MONTH = X_REPTG_DAY_OF_MONTH,
617 REPTG_DAYS_AFTER = X_REPTG_DAYS_AFTER,
618 INVG_FREQ_CODE = X_INVG_FREQ_CODE,
619 INVG_SPREAD_CODE = X_INVG_SPREAD_CODE,
620 INVG_DAY_OF_MONTH = X_INVG_DAY_OF_MONTH,
621 INVG_DAYS_AFTER = X_INVG_DAYS_AFTER,
622 comments = X_COMMENTS,
623 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
624 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
625 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
626 VRG_REPTG_FREQ_CODE = X_VRG_REPTG_FREQ_CODE,
627 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
628 ATTRIBUTE1 = X_ATTRIBUTE1,
629 ATTRIBUTE2 = X_ATTRIBUTE2,
630 ATTRIBUTE3 = X_ATTRIBUTE3,
631 ATTRIBUTE4 = X_ATTRIBUTE4,
632 ATTRIBUTE5 = X_ATTRIBUTE5,
633 ATTRIBUTE6 = X_ATTRIBUTE6,
634 ATTRIBUTE7 = X_ATTRIBUTE7,
635 ATTRIBUTE8 = X_ATTRIBUTE8,
636 ATTRIBUTE9 = X_ATTRIBUTE9,
637 ATTRIBUTE10 = X_ATTRIBUTE10,
638 ATTRIBUTE11 = X_ATTRIBUTE11,
639 ATTRIBUTE12 = X_ATTRIBUTE12,
640 ATTRIBUTE13 = X_ATTRIBUTE13,
641 ATTRIBUTE14 = X_ATTRIBUTE14,
642 ATTRIBUTE15 = X_ATTRIBUTE15
643 where AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID;
644
645 if (sql%notfound) then
646 raise no_data_found;
647 end if;
648
649 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.UPDATE_ROW (+)');
650
651 end UPDATE_ROW;
652
653 -----------------------------------------------------------------------
654 -- PROCDURE : DELETE_ROW
655 -----------------------------------------------------------------------
656
657 procedure DELETE_ROW (
658 X_AGREEMENT_TEMPLATE_ID in NUMBER
659 ) is
660
661 BEGIN
662
663 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.DELETE_ROW (+)');
664
665 delete from PN_VAR_TEMPLATES_ALL
666 where AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID;
667
668 if (sql%notfound) then
669 raise no_data_found;
670 end if;
671
672 PNP_DEBUG_PKG.debug ('PN_VAR_TEMPLATE_PKG.DELETE_ROW (-)');
673
674 END DELETE_ROW;
675
676 PROCEDURE check_unq_vr_template (
677 x_return_status IN OUT NOCOPY VARCHAR2
678 ,x_template_id IN NUMBER
679 ,x_name IN VARCHAR2
680 ,x_org_id IN NUMBER
681 ) IS
682 l_dummy NUMBER;
683 BEGIN
684 SELECT 1
685 INTO l_dummy
686 FROM DUAL
687 WHERE NOT EXISTS (SELECT 1
688 FROM pn_var_templates_all
689 WHERE agreement_template = x_name
690 AND ((x_template_id IS NULL)
691 OR (agreement_template_id <> x_template_id))
692 AND nvl(org_id, -99) = nvl(x_org_id,nvl(org_id, -99))
693 );
694 EXCEPTION
695 WHEN NO_DATA_FOUND THEN
696 fnd_message.set_name ('PN', 'PN_DUP_TERM_TEMPLATE');
697 x_return_status := 'E';
698 END check_unq_vr_template;
699
700 end PN_VAR_TEMPLATE_PKG;
[Home] [Help]