1 PACKAGE BODY pn_index_lease_periods_pkg AS
2 -- $Header: PNTINLPB.pls 120.4 2007/03/14 12:58:14 pseeram ship $
3
4 /*============================================================================+
5 | Copyright (c) 2001 Oracle Corporation
6 | Redwood Shores, California, USA
7 | All rights reserved.
8 | DESCRIPTION
9 |
10 | These procedures consist are used a table handlers for the
11 | PN_INDEX_LEASE_PERIODS table.
12 | They include:
13 | INSERT_ROW - insert a row into PN_INDEX_LEASE_PERIODS.
14 | DELETE_ROW - deletes a row from PN_INDEX_LEASE_PERIODS.
15 | UPDATE_ROW - updates a row from PN_INDEX_LEASE_PERIODS.
16 | LOCKS_ROW - will check if a row has been modified since
17 | being queried by form.
18 |
19 |
20 | HISTORY
21 | 21-MAY-2001 jbreyes o Created
22 | 13-DEC-2001 Mrinal Misra o Added dbdrv command.
23 | 15-JAN-2002 Mrinal Misra o In dbdrv command changed phase=pls to phase=plb
24 | Added checkfile.Ref. Bug# 2184724.
25 | 09-JUL-2002 ftanudja o added x_org_id param in insert_row for
26 | shared services enh.
27 | 23-JUL-2002 ftanudja o changed lock_row comply with new standards.
28 | 02-Aug-2002 psidhu o added parameters op_constraint_applied_amount
29 | and op_carry_forward_amount in call to
30 | pn_index_amount_pkg.calculate_period. Added call
31 | to pn_index_amount_pkg.calculate_subsequent_periods.
32 | 20-Oct-2002 psidhu o added parameters op_constraint_applied_percent
33 | and op_carry_forward_percent in call to
34 | procedure pn_index_amount_pkg.calculate_period.
35 | 05-Jul-2005 hrodda o overloaded delete_row proc to take PK as parameter
36 | 09-NOV-2006 Prabhakar o Added index_multiplier to insert/update/lock.
37 +============================================================================*/
38
39 -------------------------------------------------------------------------------
40 -- PROCDURE : INSERT_ROW
41 -- INVOKED FROM : insert_row procedure
42 -- PURPOSE : inserts the row
43 -- HISTORY :
44 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with
45 -- _ALL table.
46 -- 09-NOV-06 Prabhakar o Added index_multiplier to insert_row.
47 -------------------------------------------------------------------------------
48 PROCEDURE insert_row
49 (
50 x_rowid IN OUT NOCOPY VARCHAR2
51 ,x_org_id IN NUMBER
52 ,x_index_period_id IN OUT NOCOPY NUMBER
53 ,x_index_lease_id IN NUMBER
54 ,x_line_number IN OUT NOCOPY NUMBER
55 ,x_assessment_date IN DATE
56 ,x_last_update_date IN DATE
57 ,x_last_updated_by IN NUMBER
58 ,x_creation_date IN DATE
59 ,x_created_by IN NUMBER
60 ,x_basis_start_date IN DATE
61 ,x_basis_end_date IN DATE
62 ,x_index_finder_date IN DATE
63 ,x_current_index_line_id IN NUMBER
64 ,x_current_index_line_value IN NUMBER
65 ,x_previous_index_line_id IN NUMBER
66 ,x_previous_index_line_value IN NUMBER
67 ,x_current_basis IN NUMBER
68 ,x_relationship IN VARCHAR2
69 ,x_index_percent_change IN NUMBER
70 ,x_basis_percent_change IN NUMBER
71 ,x_unconstraint_rent_due IN NUMBER
72 ,x_constraint_rent_due IN NUMBER
73 ,x_last_update_login IN NUMBER
74 ,x_attribute_category IN VARCHAR2
75 ,x_attribute1 IN VARCHAR2
76 ,x_attribute2 IN VARCHAR2
77 ,x_attribute3 IN VARCHAR2
78 ,x_attribute4 IN VARCHAR2
79 ,x_attribute5 IN VARCHAR2
80 ,x_attribute6 IN VARCHAR2
81 ,x_attribute7 IN VARCHAR2
82 ,x_attribute8 IN VARCHAR2
83 ,x_attribute9 IN VARCHAR2
84 ,x_attribute10 IN VARCHAR2
85 ,x_attribute11 IN VARCHAR2
86 ,x_attribute12 IN VARCHAR2
87 ,x_attribute13 IN VARCHAR2
88 ,x_attribute14 IN VARCHAR2
89 ,x_attribute15 IN VARCHAR2
90 ,x_index_multiplier IN NUMBER)
91 IS
92 CURSOR c IS
93 SELECT ROWID
94 FROM pn_index_lease_periods_all
95 WHERE index_period_id = x_index_period_id;
96
97 l_return_status VARCHAR2 (30) := NULL;
98 l_rowid VARCHAR2 (18) := NULL;
99
100 CURSOR org_cur IS
101 SELECT org_id FROM pn_index_leases_all WHERE index_lease_id = x_index_lease_id;
102 l_org_ID NUMBER;
103
104 BEGIN
105
106 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.insert_row (+)');
107 /* If no INDEX_PERIOD_ID is provided, get one from sequence */
108 IF (x_index_period_id IS NULL) THEN
109 SELECT pn_index_lease_periods_s.NEXTVAL
110 INTO x_index_period_id
111 FROM DUAL;
112 END IF;
113
114
115 /* if no line number is passed derived on from existing list. */
116
117 IF x_line_number IS NULL THEN
118 SELECT NVL(MAX (line_number),0) + 1
119 INTO x_line_number
120 FROM pn_index_lease_periods_all a
121 WHERE index_lease_id = x_index_lease_id
122 AND a.org_id = x_org_id;
123 END IF;
124
125 IF x_org_id IS NULL THEN
126 FOR rec IN org_cur LOOP
127 l_org_id := rec.org_id;
128 END LOOP;
129 ELSE
130 l_org_id := x_org_id;
131 END IF;
132
133 INSERT INTO pn_index_lease_periods_all
134 (
135 index_period_id
136 ,org_id
137 ,index_lease_id
138 ,line_number
139 ,assessment_date
140 ,last_update_date
141 ,last_updated_by
142 ,creation_date
143 ,created_by
144 ,basis_start_date
145 ,basis_end_date
146 ,index_finder_date
147 ,current_index_line_id
148 ,current_index_line_value
149 ,previous_index_line_id
150 ,previous_index_line_value
151 ,current_basis
152 ,relationship
153 ,index_percent_change
154 ,basis_percent_change
155 ,unconstraint_rent_due
156 ,constraint_rent_due
157 ,last_update_login
158 ,attribute_category
159 ,attribute1
160 ,attribute2
161 ,attribute3
162 ,attribute4
163 ,attribute5
164 ,attribute6
165 ,attribute7
166 ,attribute8
167 ,attribute9
168 ,attribute10
169 ,attribute11
170 ,attribute12
171 ,attribute13
172 ,attribute14
173 ,attribute15
174 ,index_multiplier)
175 VALUES
176 (
177 x_index_period_id
178 ,l_org_id
179 ,x_index_lease_id
180 ,x_line_number
181 ,x_assessment_date
182 ,x_last_update_date
183 ,x_last_updated_by
184 ,x_creation_date
185 ,x_created_by
186 ,x_basis_start_date
187 ,x_basis_end_date
188 ,x_index_finder_date
189 ,x_current_index_line_id
190 ,x_current_index_line_value
191 ,x_previous_index_line_id
192 ,x_previous_index_line_value
193 ,x_current_basis
194 ,x_relationship
195 ,x_index_percent_change
196 ,x_basis_percent_change
197 ,x_unconstraint_rent_due
198 ,x_constraint_rent_due
199 ,x_last_update_login
200 ,x_attribute_category
201 ,x_attribute1
202 ,x_attribute2
203 ,x_attribute3
204 ,x_attribute4
205 ,x_attribute5
206 ,x_attribute6
207 ,x_attribute7
208 ,x_attribute8
209 ,x_attribute9
210 ,x_attribute10
211 ,x_attribute11
212 ,x_attribute12
213 ,x_attribute13
214 ,x_attribute14
215 ,x_attribute15
216 ,x_index_multiplier);
217
218
219 /* Check if a valid record was created. */
220 OPEN c;
221 FETCH c INTO x_rowid;
222 IF (c%NOTFOUND)
223 THEN
224 CLOSE c;
225 RAISE NO_DATA_FOUND;
226 END IF;
227 CLOSE c;
228 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.insert_row (-)');
229 END insert_row;
230
231
232 -------------------------------------------------------------------------------
233 -- PROCDURE : update_row
234 -- INVOKED FROM : update_row procedure
235 -- PURPOSE : updates the row
236 -- HISTORY :
237 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with
238 -- _ALL table.Also changed the where clause
239 -- 09-NOV-06 Prabhakar o Added index_multiplier to update_row.
240 -------------------------------------------------------------------------------
241 PROCEDURE update_row (
242 x_rowid IN VARCHAR2
243 ,x_index_period_id IN NUMBER
244 ,x_index_lease_id IN NUMBER
245 ,x_line_number IN NUMBER
246 ,x_assessment_date IN DATE
247 ,x_last_update_date IN DATE
248 ,x_last_updated_by IN NUMBER
249 ,x_basis_start_date IN DATE
250 ,x_basis_end_date IN DATE
251 ,x_index_finder_date IN DATE
252 ,x_current_index_line_id IN NUMBER
253 ,x_current_index_line_value IN NUMBER
254 ,x_previous_index_line_id IN NUMBER
255 ,x_previous_index_line_value IN NUMBER
256 ,x_current_basis IN NUMBER
257 ,x_relationship IN VARCHAR2
258 ,x_index_percent_change IN NUMBER
259 ,x_basis_percent_change IN NUMBER
260 ,x_unconstraint_rent_due IN NUMBER
261 ,x_constraint_rent_due IN NUMBER
262 ,x_last_update_login IN NUMBER
263 ,x_attribute_category IN VARCHAR2
264 ,x_attribute1 IN VARCHAR2
265 ,x_attribute2 IN VARCHAR2
266 ,x_attribute3 IN VARCHAR2
267 ,x_attribute4 IN VARCHAR2
268 ,x_attribute5 IN VARCHAR2
269 ,x_attribute6 IN VARCHAR2
270 ,x_attribute7 IN VARCHAR2
271 ,x_attribute8 IN VARCHAR2
272 ,x_attribute9 IN VARCHAR2
273 ,x_attribute10 IN VARCHAR2
274 ,x_attribute11 IN VARCHAR2
275 ,x_attribute12 IN VARCHAR2
276 ,x_attribute13 IN VARCHAR2
277 ,x_attribute14 IN VARCHAR2
278 ,x_attribute15 IN VARCHAR2
279 ,x_index_multiplier IN NUMBER
280 ,x_constraint_applied_amount IN NUMBER
281 ,x_constraint_applied_percent IN NUMBER)
282 IS
283 l_return_status VARCHAR2 (30) := NULL;
284 BEGIN
285
286 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.update_row (+)');
287 IF (l_return_status IS NOT NULL)
288 THEN
289 app_exception.raise_exception;
290 END IF;
291
292 UPDATE pn_index_lease_periods_all
293 SET index_lease_id = x_index_lease_id
294 ,line_number = x_line_number
295 ,assessment_date = x_assessment_date
296 ,last_update_date = x_last_update_date
297 ,last_updated_by = x_last_updated_by
298 ,basis_start_date = x_basis_start_date
299 ,basis_end_date = x_basis_end_date
300 ,index_finder_date = x_index_finder_date
301 ,current_index_line_id = x_current_index_line_id
302 ,current_index_line_value = x_current_index_line_value
303 ,previous_index_line_id = x_previous_index_line_id
304 ,previous_index_line_value = x_previous_index_line_value
305 ,current_basis = x_current_basis
306 ,relationship = x_relationship
307 ,index_percent_change = x_index_percent_change
308 ,basis_percent_change = x_basis_percent_change
309 ,unconstraint_rent_due = x_unconstraint_rent_due
310 ,constraint_rent_due = x_constraint_rent_due
311 ,last_update_login = x_last_update_login
312 ,attribute_category = x_attribute_category
313 ,attribute1 = x_attribute1
314 ,attribute2 = x_attribute2
315 ,attribute3 = x_attribute3
316 ,attribute4 = x_attribute4
317 ,attribute5 = x_attribute5
318 ,attribute6 = x_attribute6
319 ,attribute7 = x_attribute7
320 ,attribute8 = x_attribute8
321 ,attribute9 = x_attribute9
322 ,attribute10 = x_attribute10
323 ,attribute11 = x_attribute11
324 ,attribute12 = x_attribute12
325 ,attribute13 = x_attribute13
326 ,attribute14 = x_attribute14
327 ,attribute15 = x_attribute15
328 ,index_multiplier = x_index_multiplier
329 ,constraint_applied_amount = x_constraint_applied_amount
330 ,constraint_applied_percent = x_constraint_applied_percent
331 WHERE pn_index_lease_periods_all.index_period_id = x_index_period_id;
332
333
334 IF (SQL%NOTFOUND)
335 THEN
336 RAISE NO_DATA_FOUND;
337 END IF;
338
339 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.update_row (-)');
340 END update_row;
341
342 -------------------------------------------------------------------------------
343 -- PROCDURE : update_row_calc
344 -- INVOKED FROM : update_row_calc procedure
345 -- PURPOSE :
346 -- HISTORY :
347 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with
348 -- _ALL table.Also changed the where clause
349 -- 09-NOV-06 Prabhakar o added index_multiplier.
350 -------------------------------------------------------------------------------
351 PROCEDURE update_row_calc (
352 x_rowid IN VARCHAR2
353 ,x_calculate IN VARCHAR2
354 ,x_updated_flag IN VARCHAR2
355 ,x_index_period_id IN NUMBER
356 ,x_index_lease_id IN NUMBER
357 ,x_line_number IN NUMBER
358 ,x_assessment_date IN DATE
359 ,x_last_update_date IN DATE
360 ,x_last_updated_by IN NUMBER
361 ,x_basis_start_date IN DATE
362 ,x_basis_end_date IN DATE
363 ,x_index_finder_date IN DATE
364 ,x_current_index_line_id IN OUT NOCOPY NUMBER
365 ,x_current_index_line_value IN OUT NOCOPY NUMBER
366 ,x_previous_index_line_id IN OUT NOCOPY NUMBER
367 ,x_previous_index_line_value IN OUT NOCOPY NUMBER
368 ,x_current_basis IN OUT NOCOPY NUMBER
369 ,x_relationship IN VARCHAR2
370 ,x_index_percent_change IN OUT NOCOPY NUMBER
371 ,x_basis_percent_change IN NUMBER
372 ,x_unconstraint_rent_due IN OUT NOCOPY NUMBER
373 ,x_constraint_rent_due IN OUT NOCOPY NUMBER
374 ,x_last_update_login IN NUMBER
375 ,x_attribute_category IN VARCHAR2
376 ,x_attribute1 IN VARCHAR2
377 ,x_attribute2 IN VARCHAR2
378 ,x_attribute3 IN VARCHAR2
379 ,x_attribute4 IN VARCHAR2
380 ,x_attribute5 IN VARCHAR2
381 ,x_attribute6 IN VARCHAR2
382 ,x_attribute7 IN VARCHAR2
383 ,x_attribute8 IN VARCHAR2
384 ,x_attribute9 IN VARCHAR2
385 ,x_attribute10 IN VARCHAR2
386 ,x_attribute11 IN VARCHAR2
387 ,x_attribute12 IN VARCHAR2
388 ,x_attribute13 IN VARCHAR2
389 ,x_attribute14 IN VARCHAR2
390 ,x_attribute15 IN VARCHAR2
391 ,x_carry_forward_flag IN VARCHAR2
392 ,x_index_multiplier IN NUMBER
393 ,x_constraint_applied_amount IN NUMBER
394 ,x_constraint_applied_percent IN NUMBER)
395 IS
396 l_return_status VARCHAR2 (30) := NULL;
397 l_calc_exists NUMBER := NULL;
398 l_msg VARCHAR2(2000);
399 l_previous_index_amount NUMBER := NULL;
400 l_previous_asmt_date DATE := NULL;
401 l_carry_forward_amount pn_index_lease_periods.carry_forward_amount%type := null;
402 l_constraint_applied_amount pn_index_lease_periods.constraint_applied_amount%type := null;
403 l_constraint_applied_percent pn_index_lease_periods.constraint_applied_percent%type :=null;
404 l_carry_forward_percent pn_index_lease_periods.carry_forward_percent%type;
405
406 BEGIN
407
408 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.update_row_calc (+)');
409 IF (l_return_status IS NOT NULL)
410 THEN
411 app_exception.raise_exception;
412 END IF;
413
414
415 UPDATE pn_index_lease_periods_all
416 SET index_lease_id = x_index_lease_id
417 ,line_number = x_line_number
418 ,assessment_date = x_assessment_date
419 ,last_update_date = x_last_update_date
420 ,last_updated_by = x_last_updated_by
421 ,basis_start_date = x_basis_start_date
422 ,basis_end_date = x_basis_end_date
423 ,index_finder_date = x_index_finder_date
424 ,current_index_line_id = x_current_index_line_id
425 ,current_index_line_value = x_current_index_line_value
426 ,previous_index_line_id = x_previous_index_line_id
427 ,previous_index_line_value = x_previous_index_line_value
428 ,current_basis = x_current_basis
429 ,relationship = x_relationship
430 ,index_percent_change = x_index_percent_change
431 ,basis_percent_change = x_basis_percent_change
432 ,unconstraint_rent_due = x_unconstraint_rent_due
433 ,constraint_rent_due = x_constraint_rent_due
434 ,last_update_login = x_last_update_login
435 ,attribute_category = x_attribute_category
436 ,attribute1 = x_attribute1
437 ,attribute2 = x_attribute2
438 ,attribute3 = x_attribute3
439 ,attribute4 = x_attribute4
440 ,attribute5 = x_attribute5
441 ,attribute6 = x_attribute6
442 ,attribute7 = x_attribute7
443 ,attribute8 = x_attribute8
444 ,attribute9 = x_attribute9
445 ,attribute10 = x_attribute10
446 ,attribute11 = x_attribute11
447 ,attribute12 = x_attribute12
448 ,attribute13 = x_attribute13
449 ,attribute14 = x_attribute14
450 ,attribute15 = x_attribute15
451 ,index_multiplier = x_index_multiplier
452 ,constraint_applied_amount = x_constraint_applied_amount
453 ,constraint_applied_percent = x_constraint_applied_percent
454 WHERE pn_index_lease_periods_all.index_period_id = x_index_period_id;
455
456
457 IF (SQL%NOTFOUND)
458 THEN
459 RAISE NO_DATA_FOUND;
460 END IF;
461
462
463
464 IF x_calculate <> 'CALCULATE' THEN
465
466 l_calc_exists := pn_index_lease_common_pkg.find_if_calc_exists(x_index_lease_id);
467
468 IF l_calc_exists IS NOT NULL THEN
469
470 pn_index_amount_pkg.calculate_period
471 (
472 ip_index_lease_id => x_index_lease_id,
473 ip_index_lease_period_id => x_index_period_id,
474 ip_recalculate => 'Y',
475 op_current_basis => x_current_basis,
476 op_unconstraint_rent_due => x_unconstraint_rent_due,
477 op_constraint_rent_due => x_constraint_rent_due,
478 op_index_percent_change => x_index_percent_change,
479 op_current_index_line_id => x_current_index_line_id,
480 op_current_index_line_value => x_current_index_line_value,
481 op_previous_index_line_id => x_previous_index_line_id,
482 op_previous_index_line_value => x_previous_index_line_value,
483 op_previous_index_amount => l_previous_index_amount,
484 op_previous_asmt_date => l_previous_asmt_date,
485 op_constraint_applied_amount => l_constraint_applied_amount,
486 op_carry_forward_amount => l_carry_forward_amount,
487 op_constraint_applied_percent => l_constraint_applied_percent,
488 op_carry_forward_percent => l_carry_forward_percent,
489 op_msg => l_msg
490 );
491
492
493 UPDATE pn_index_lease_periods_all
494 SET index_lease_id = x_index_lease_id
495 ,line_number = x_line_number
496 ,assessment_date = x_assessment_date
497 ,last_update_date = x_last_update_date
498 ,last_updated_by = x_last_updated_by
499 ,basis_start_date = x_basis_start_date
500 ,basis_end_date = x_basis_end_date
501 ,index_finder_date = x_index_finder_date
502 ,current_index_line_id = x_current_index_line_id
503 ,current_index_line_value = x_current_index_line_value
504 ,previous_index_line_id = x_previous_index_line_id
505 ,previous_index_line_value = x_previous_index_line_value
506 ,current_basis = x_current_basis
507 ,relationship = x_relationship
508 ,index_percent_change = x_index_percent_change
509 ,basis_percent_change = x_basis_percent_change
510 ,unconstraint_rent_due = x_unconstraint_rent_due
511 ,constraint_rent_due = x_constraint_rent_due
512 ,constraint_applied_amount = l_constraint_applied_amount
513 ,carry_forward_amount = l_carry_forward_amount
514 ,constraint_applied_percent = l_constraint_applied_percent
515 ,carry_forward_percent = l_carry_forward_percent
516 ,last_update_login = x_last_update_login
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 ,index_multiplier = x_index_multiplier
534 WHERE index_period_id = x_index_period_id;
535
536
537
538 IF (SQL%NOTFOUND)
539 THEN
540 RAISE NO_DATA_FOUND;
541 END IF;
542
543 IF x_carry_forward_flag = 'Y' THEN
544 pn_index_amount_pkg.calculate_subsequent_periods(
545 p_index_lease_id => x_index_lease_id ,
546 p_assessment_date => x_assessment_date);
547 END IF;
548
549 END IF;
550
551 END IF;
552
553 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.update_row_calc (-)');
554 END update_row_calc;
555
556
557 -------------------------------------------------------------------------------
558 -- PROCDURE : lock_row
559 -- INVOKED FROM : lock_row procedure
560 -- PURPOSE : locks the row
561 -- HISTORY :
562 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with
563 -- _ALL table.Also changed the where clause
564 -- 09-NOV-2006 Prabhakar o Added index_multiplier to lock_row.
565 -------------------------------------------------------------------------------
566 PROCEDURE lock_row (
567 x_rowid IN VARCHAR2
568 ,x_index_period_id IN NUMBER
569 ,x_index_lease_id IN NUMBER
570 ,x_line_number IN NUMBER
571 ,x_assessment_date IN DATE
572 ,x_basis_start_date IN DATE
573 ,x_basis_end_date IN DATE
574 ,x_index_finder_date IN DATE
575 ,x_current_index_line_id IN NUMBER
576 ,x_current_index_line_value IN NUMBER
577 ,x_previous_index_line_id IN NUMBER
578 ,x_previous_index_line_value IN NUMBER
579 ,x_current_basis IN NUMBER
580 ,x_relationship IN VARCHAR2
581 ,x_index_percent_change IN NUMBER
582 ,x_basis_percent_change IN NUMBER
583 ,x_unconstraint_rent_due IN NUMBER
584 ,x_constraint_rent_due IN NUMBER
585 ,x_attribute_category IN VARCHAR2
586 ,x_attribute1 IN VARCHAR2
587 ,x_attribute2 IN VARCHAR2
588 ,x_attribute3 IN VARCHAR2
589 ,x_attribute4 IN VARCHAR2
590 ,x_attribute5 IN VARCHAR2
591 ,x_attribute6 IN VARCHAR2
592 ,x_attribute7 IN VARCHAR2
593 ,x_attribute8 IN VARCHAR2
594 ,x_attribute9 IN VARCHAR2
595 ,x_attribute10 IN VARCHAR2
596 ,x_attribute11 IN VARCHAR2
597 ,x_attribute12 IN VARCHAR2
598 ,x_attribute13 IN VARCHAR2
599 ,x_attribute14 IN VARCHAR2
600 ,x_attribute15 IN VARCHAR2
601 ,x_index_multiplier IN NUMBER
602 ,x_constraint_applied_amount IN NUMBER
603 ,x_constraint_applied_percent IN NUMBER)
604 IS
605 CURSOR c1 IS
606 SELECT *
607 FROM pn_index_lease_periods_all
608 WHERE index_period_id = x_index_period_id
609 FOR UPDATE OF index_period_id NOWAIT;
610
611 tlinfo c1%ROWTYPE;
612 BEGIN
613
614 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.lock_row (+)');
615 OPEN c1;
616 FETCH c1 INTO tlinfo;
617 IF (c1%NOTFOUND)
618 THEN
619 CLOSE c1;
620 RETURN;
621 END IF;
622 CLOSE c1;
623
624 IF NOT (tlinfo.index_period_id = x_index_period_id) THEN
625 pn_var_rent_pkg.lock_row_exception('INDEX_PERIOD_ID',tlinfo.index_period_id);
626 END IF;
627
628 IF NOT (tlinfo.index_lease_id = x_index_lease_id) THEN
629 pn_var_rent_pkg.lock_row_exception('INDEX_LEASE_ID',tlinfo.index_lease_id);
630 END IF;
631
632 IF NOT (tlinfo.line_number = x_line_number) THEN
633 pn_var_rent_pkg.lock_row_exception('LINE_NUMBER',tlinfo.line_number);
634 END IF;
635
636 IF NOT (tlinfo.assessment_date = x_assessment_date) THEN
637 pn_var_rent_pkg.lock_row_exception('ASSESSMENT_DATE',tlinfo.assessment_date);
638 END IF;
639
640 IF NOT ((tlinfo.basis_start_date = x_basis_start_date)
641 OR ((tlinfo.basis_start_date IS NULL) AND x_basis_start_date IS NULL)) THEN
642 pn_var_rent_pkg.lock_row_exception('BASIS_START_DATE',tlinfo.basis_start_date);
643 END IF;
644
645 IF NOT ((tlinfo.basis_end_date = x_basis_end_date)
646 OR ((tlinfo.basis_end_date IS NULL) AND x_basis_end_date IS NULL)) THEN
647 pn_var_rent_pkg.lock_row_exception('BASIS_END_DATE',tlinfo.basis_end_date);
648 END IF;
649
650 IF NOT ((tlinfo.index_finder_date = x_index_finder_date)
651 OR ((tlinfo.index_finder_date IS NULL) AND x_index_finder_date IS NULL)) THEN
652 pn_var_rent_pkg.lock_row_exception('INDEX_FINDER_DATE',tlinfo.index_finder_date);
653 END IF;
654
655 IF NOT ((tlinfo.current_index_line_id = x_current_index_line_id)
656 OR ((tlinfo.current_index_line_id IS NULL) AND x_current_index_line_id IS NULL)) THEN
657 pn_var_rent_pkg.lock_row_exception('CURRENT_INDEX_LINE_ID',tlinfo.current_index_line_id);
658 END IF;
659
660 IF NOT ((tlinfo.current_index_line_value = x_current_index_line_value)
661 OR ((tlinfo.current_index_line_value IS NULL) AND x_current_index_line_value IS NULL)) THEN
662 pn_var_rent_pkg.lock_row_exception('CURRENT_INDEX_LINE_VALUE',tlinfo.current_index_line_value);
663 END IF;
664
665 IF NOT ((tlinfo.previous_index_line_id = x_previous_index_line_id)
666 OR ((tlinfo.previous_index_line_id IS NULL) AND x_previous_index_line_id IS NULL)) THEN
667 pn_var_rent_pkg.lock_row_exception('PREVIOUS_INDEX_LINE_ID',tlinfo.previous_index_line_id);
668 END IF;
669
670 IF NOT ((tlinfo.previous_index_line_value = x_previous_index_line_value)
671 OR ((tlinfo.previous_index_line_value IS NULL) AND x_previous_index_line_value IS NULL)) THEN
672 pn_var_rent_pkg.lock_row_exception('PREVIOUS_INDEX_LINE_VALUE',tlinfo.previous_index_line_value);
673 END IF;
674
675 IF NOT ((tlinfo.current_basis = x_current_basis)
676 OR ((tlinfo.current_basis IS NULL) AND x_current_basis IS NULL)) THEN
677 pn_var_rent_pkg.lock_row_exception('CURRENT_BASIS',tlinfo.current_basis);
678 END IF;
679
680 IF NOT ((tlinfo.relationship = x_relationship)
681 OR ((tlinfo.relationship IS NULL) AND x_relationship IS NULL)) THEN
682 pn_var_rent_pkg.lock_row_exception('RELATIONSHIP',tlinfo.relationship);
683 END IF;
684
685 IF NOT ((tlinfo.index_percent_change = x_index_percent_change)
686 OR ((tlinfo.index_percent_change IS NULL) AND x_index_percent_change IS NULL)) THEN
687 pn_var_rent_pkg.lock_row_exception('INDEX_PERCENT_CHANGE',tlinfo.index_percent_change);
688 END IF;
689
690 IF NOT ((tlinfo.basis_percent_change = x_basis_percent_change)
691 OR ((tlinfo.basis_percent_change IS NULL) AND x_basis_percent_change IS NULL)) THEN
692 pn_var_rent_pkg.lock_row_exception('BASIS_PERCENT_CHANGE',tlinfo.basis_percent_change);
693 END IF;
694
695 IF NOT ((tlinfo.unconstraint_rent_due = x_unconstraint_rent_due)
696 OR ((tlinfo.unconstraint_rent_due IS NULL) AND x_unconstraint_rent_due IS NULL)) THEN
697 pn_var_rent_pkg.lock_row_exception('UNCONSTRAINT_RENT_DUE',tlinfo.unconstraint_rent_due);
698 END IF;
699
700 IF NOT ((tlinfo.constraint_rent_due = x_constraint_rent_due)
701 OR ((tlinfo.constraint_rent_due IS NULL) AND x_constraint_rent_due IS NULL)) THEN
702 pn_var_rent_pkg.lock_row_exception('CONSTRAINT_RENT_DUE',tlinfo.constraint_rent_due);
703 END IF;
704
705 IF NOT ((tlinfo.index_multiplier = x_index_multiplier)
706 OR ((tlinfo.index_multiplier IS NULL) AND x_index_multiplier IS NULL)) THEN
707 pn_var_rent_pkg.lock_row_exception('CONSTRAINT_RENT_DUE',tlinfo.index_multiplier);
708 END IF;
709
710 IF NOT ((tlinfo.constraint_applied_amount = x_constraint_applied_amount)
711 OR ((tlinfo.constraint_applied_amount IS NULL) AND x_constraint_applied_amount IS NULL)) THEN
712 pn_var_rent_pkg.lock_row_exception('CONSTRAINT_RENT_DUE',tlinfo.constraint_applied_amount);
713 END IF;
714
715 IF NOT ((tlinfo.constraint_applied_percent = x_constraint_applied_percent)
716 OR ((tlinfo.constraint_applied_percent IS NULL) AND x_constraint_applied_percent IS NULL)) THEN
717 pn_var_rent_pkg.lock_row_exception('CONSTRAINT_RENT_DUE',tlinfo.constraint_applied_percent);
718 END IF;
719
720 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.lock_row (-)');
721 END lock_row;
722
723
724 -------------------------------------------------------------------------------
725 -- PROCDURE : delete_row
726 -- INVOKED FROM : delete_row procedure
727 -- PURPOSE : deletes the row
728 -- HISTORY :
729 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with
730 -- _ALL table.
731 -------------------------------------------------------------------------------
732 PROCEDURE delete_row (x_rowid IN VARCHAR2)
733 IS
734 BEGIN
735 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.delete_row (+)');
736 DELETE FROM pn_index_lease_periods_all
737 WHERE ROWID = x_rowid;
738
739 IF (SQL%NOTFOUND)
740 THEN
741 RAISE NO_DATA_FOUND;
742 END IF;
743
744 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.delete_row (-)');
745 END delete_row;
746
747 -------------------------------------------------------------------------------
748 -- PROCDURE : delete_row
749 -- INVOKED FROM : delete_row procedure
750 -- PURPOSE : deletes the row
751 -- NOTE : overloaded this procedure to take PK as In parameter
752 -- HISTORY :
753 -- 04-JUL-05 hrodda o Bug 4284035 - Created
754 -------------------------------------------------------------------------------
755 PROCEDURE delete_row (x_index_period_id IN NUMBER)
756 IS
757 BEGIN
758 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.delete_row (+)');
759 DELETE FROM pn_index_lease_periods_all
760 WHERE index_period_id = x_index_period_id;
761
762 IF (SQL%NOTFOUND)
763 THEN
764 RAISE NO_DATA_FOUND;
765 END IF;
766
767 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_PERIODS_PKG.delete_row (-)');
768 END delete_row;
769
770
771
772 END pn_index_lease_periods_pkg;
[Home] [Help]