1 PACKAGE BODY pn_index_leases_pkg AS
2 -- $Header: PNTINLEB.pls 120.4 2007/01/02 07:46:02 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 PN_INDEX_LEASES table.
11 | They include:
12 | INSERT_ROW - insert a row into PN_INDEX_LEASES.
13 | DELETE_ROW - deletes a row from PN_INDEX_LEASES.
14 | UPDATE_ROW - updates a row from PN_INDEX_LEASES.
15 | LOCKS_ROW - will check if a row has been modified since being queried by form.
16 |
17 |
18 | HISTORY
19 | 10-APR-01 jbreyes o Created
20 | 15-JUN-01 jbreyes o Added new column BASE_INDEX_LINE_ID
21 | 21-JUN-01 jbreyes o Added new column INDEX_FINDER_MONTHS
22 | 07-AUG-01 psidhu o Added new columns AGGEGATION_FLAG and GROSS_FLAG
23 | 13-DEC-01 Mrinal o Added dbdrv command.
24 | 15-JAN-02 Mrinal o In dbdrv command changed phase=pls to phase=plb.
25 | Added checkfile.Ref. Bug# 2184724.
26 | 17-Jul-02 Psidhu o Added currency_code as a parameter to insert_row,
27 | update_row and lock_row.
28 | 09-JUL-02 ftanudja o added x_org_id param in insert_row for
29 | shared services enh.
30 | 23-JUL-02 ftanudja o changed lock_row to comply with new standards
31 | 05-AUG-02 psidhu o added x_carry_forward_flag parameter to insert_row,
32 | update_row and lock_row.
33 | 17-MAY-04 vmmehta o added x_retain_initial_flag parameter to insert_row,
34 | update_row and lock_row.
35 | 05-Jul-05 hrodda o overloaded delete_row proc to take PK as parameter
36 | 14-AUG-06 pikhar o Added vr_nbp_flag to insert/update/lock
37 | 09-NOV-06 prabhakar o Added index_multiplier to insert/update/lock
38 +===========================================================================*/
39
40 -------------------------------------------------------------------------------
41 -- PROCDURE : INSERT_ROW
42 -- INVOKED FROM : insert_row procedure
43 -- PURPOSE : inserts the row
44 -- HISTORY :
45 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases with _ALL table.
46 -- 14-AUG-06 pikhar o Added vr_nbp_flag to insert
47 -- 09-NOV-06 prabhakar o Added index_multiplier to insert_row
48 -- 08-DEC-06 Prabhakar o Added proration_rule and proration_period_start_date.
49 -------------------------------------------------------------------------------
50 PROCEDURE insert_row (
51 x_rowid IN OUT NOCOPY VARCHAR2
52 ,x_org_id IN NUMBER
53 ,x_index_lease_id IN OUT NOCOPY NUMBER
54 ,x_lease_id IN NUMBER
55 ,x_index_id IN NUMBER
56 ,x_commencement_date IN DATE
57 ,x_termination_date IN DATE
58 ,x_index_lease_number IN OUT NOCOPY VARCHAR2
59 ,x_last_update_date IN DATE
60 ,x_last_updated_by IN NUMBER
61 ,x_creation_date IN DATE
62 ,x_created_by IN NUMBER
63 ,x_location_id IN NUMBER
64 ,x_term_template_id IN NUMBER
65 ,x_abstracted_by IN NUMBER
66 ,x_assessment_date IN DATE
67 ,x_assessment_interval IN NUMBER
68 ,x_spread_frequency IN VARCHAR2
69 ,x_relationship_default IN VARCHAR2
70 ,x_basis_percent_default IN NUMBER
71 ,x_initial_basis IN NUMBER
72 ,x_base_index IN NUMBER
73 ,x_base_index_line_id IN NUMBER
74 ,x_index_finder_method IN VARCHAR2
75 ,x_index_finder_months IN NUMBER
76 ,x_negative_rent_type IN VARCHAR2
77 ,x_increase_on IN VARCHAR2
78 ,x_basis_type IN VARCHAR2
79 ,x_reference_period IN VARCHAR2
80 ,x_base_year IN DATE
81 ,x_leased_area IN NUMBER
82 ,x_rounding_flag IN VARCHAR2
83 ,x_aggregation_flag IN VARCHAR2
84 ,x_gross_flag IN VARCHAR2
85 ,x_last_update_login IN NUMBER
86 ,x_attribute_category IN VARCHAR2
87 ,x_attribute1 IN VARCHAR2
88 ,x_attribute2 IN VARCHAR2
89 ,x_attribute3 IN VARCHAR2
90 ,x_attribute4 IN VARCHAR2
91 ,x_attribute5 IN VARCHAR2
92 ,x_attribute6 IN VARCHAR2
93 ,x_attribute7 IN VARCHAR2
94 ,x_attribute8 IN VARCHAR2
95 ,x_attribute9 IN VARCHAR2
96 ,x_attribute10 IN VARCHAR2
97 ,x_attribute11 IN VARCHAR2
98 ,x_attribute12 IN VARCHAR2
99 ,x_attribute13 IN VARCHAR2
100 ,x_attribute14 IN VARCHAR2
101 ,x_attribute15 IN VARCHAR2
102 ,x_agreement_category IN VARCHAR2
103 ,x_agreement_attribute1 IN VARCHAR2
104 ,x_agreement_attribute2 IN VARCHAR2
105 ,x_agreement_attribute3 IN VARCHAR2
106 ,x_agreement_attribute4 IN VARCHAR2
107 ,x_agreement_attribute5 IN VARCHAR2
108 ,x_agreement_attribute6 IN VARCHAR2
109 ,x_agreement_attribute7 IN VARCHAR2
110 ,x_agreement_attribute8 IN VARCHAR2
111 ,x_agreement_attribute9 IN VARCHAR2
112 ,x_agreement_attribute10 IN VARCHAR2
113 ,x_agreement_attribute11 IN VARCHAR2
114 ,x_agreement_attribute12 IN VARCHAR2
115 ,x_agreement_attribute13 IN VARCHAR2
116 ,x_agreement_attribute14 IN VARCHAR2
117 ,x_agreement_attribute15 IN VARCHAR2
118 ,x_currency_code IN VARCHAR2
119 ,x_carry_forward_flag IN VARCHAR2
120 ,x_retain_initial_basis_flag IN VARCHAR2
121 ,x_vr_nbp_flag IN VARCHAR2
122 ,x_index_multiplier IN NUMBER
123 ,x_proration_rule IN VARCHAR2
124 ,x_proration_period_start_date IN DATE)
125 IS
126 CURSOR c IS
127 SELECT ROWID
128 FROM pn_index_leases_all
129 WHERE index_lease_id = x_index_lease_id;
130
131 l_return_status VARCHAR2 (30) := NULL;
132 l_rowid VARCHAR2 (18) := NULL;
133
134 CURSOR org_cur IS
135 SELECT org_id FROM pn_leases_all WHERE lease_id = x_lease_id;
136 l_org_ID NUMBER;
137
138 BEGIN
139
140 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.insert_row (+)');
141
142 /* If no INDEX_LEASE_ID is provided, get one from sequence */
143
144 IF (x_index_lease_id IS NULL)
145 THEN
146 SELECT pn_index_leases_s.NEXTVAL
147 INTO x_index_lease_id
148 FROM DUAL;
149 END IF;
150
151
152 /* If no index lease number is provided, use system generated index lease id */
153
154 IF (x_index_lease_number IS NULL)
155 THEN
156 x_index_lease_number := x_index_lease_id;
157 END IF;
158
159 pn_index_leases_pkg.check_unq_index_lease_number (
160 l_return_status
161 ,x_index_lease_id
162 ,x_index_lease_number);
163
164 IF (l_return_status IS NOT NULL)
165 THEN
166 app_exception.raise_exception;
167 END IF;
168
169 IF x_org_id IS NULL THEN
170 FOR rec IN org_cur LOOP
171 l_org_id := rec.org_id;
172 END LOOP;
173 ELSE
174 l_org_id := x_org_id;
175 END IF;
176
177 INSERT INTO pn_index_leases_all
178 (
179 index_lease_id
180 ,org_id
181 ,lease_id
182 ,index_id
183 ,commencement_date
184 ,termination_date
185 ,index_lease_number
186 ,last_update_date
187 ,last_updated_by
188 ,creation_date
189 ,created_by
190 ,location_id
191 ,term_template_id
192 ,abstracted_by
193 ,assessment_date
194 ,assessment_interval
195 ,spread_frequency
196 ,relationship_default
197 ,basis_percent_default
198 ,initial_basis
199 ,base_index
200 ,base_index_line_id
201 ,index_finder_method
202 ,index_finder_months
203 ,negative_rent_type
204 ,increase_on
205 ,basis_type
206 ,reference_period
207 ,base_year
208 ,leased_area
209 ,rounding_flag
210 ,aggregation_flag
211 ,gross_flag
212 ,last_update_login
213 ,attribute_category
214 ,attribute1
215 ,attribute2
216 ,attribute3
217 ,attribute4
218 ,attribute5
219 ,attribute6
220 ,attribute7
221 ,attribute8
222 ,attribute9
223 ,attribute10
224 ,attribute11
225 ,attribute12
226 ,attribute13
227 ,attribute14
228 ,attribute15
229 ,agreement_category
230 ,agreement_attribute1
231 ,agreement_attribute2
232 ,agreement_attribute3
233 ,agreement_attribute4
234 ,agreement_attribute5
235 ,agreement_attribute6
236 ,agreement_attribute7
237 ,agreement_attribute8
238 ,agreement_attribute9
239 ,agreement_attribute10
240 ,agreement_attribute11
241 ,agreement_attribute12
242 ,agreement_attribute13
243 ,agreement_attribute14
244 ,agreement_attribute15
245 ,currency_code
246 ,carry_forward_flag
247 ,retain_initial_basis_flag
248 ,vr_nbp_flag
249 ,index_multiplier
250 ,proration_rule
251 ,proration_period_start_date )
252 VALUES
253 (
254 x_index_lease_id
255 ,l_org_ID
256 ,x_lease_id
257 ,x_index_id
258 ,x_commencement_date
259 ,x_termination_date
260 ,x_index_lease_number
261 ,x_last_update_date
262 ,x_last_updated_by
263 ,x_creation_date
264 ,x_created_by
265 ,x_location_id
266 ,x_term_template_id
267 ,x_abstracted_by
268 ,x_assessment_date
269 ,x_assessment_interval
270 ,x_spread_frequency
271 ,x_relationship_default
272 ,x_basis_percent_default
273 ,x_initial_basis
274 ,x_base_index
275 ,x_base_index_line_id
276 ,x_index_finder_method
277 ,x_index_finder_months
278 ,x_negative_rent_type
279 ,x_increase_on
280 ,x_basis_type
281 ,x_reference_period
282 ,x_base_year
283 ,x_leased_area
284 ,x_rounding_flag
285 ,x_aggregation_flag
286 ,x_gross_flag
287 ,x_last_update_login
288 ,x_attribute_category
289 ,x_attribute1
290 ,x_attribute2
291 ,x_attribute3
292 ,x_attribute4
293 ,x_attribute5
294 ,x_attribute6
295 ,x_attribute7
296 ,x_attribute8
297 ,x_attribute9
298 ,x_attribute10
299 ,x_attribute11
300 ,x_attribute12
301 ,x_attribute13
302 ,x_attribute14
303 ,x_attribute15
304 ,x_agreement_category
305 ,x_agreement_attribute1
306 ,x_agreement_attribute2
307 ,x_agreement_attribute3
308 ,x_agreement_attribute4
309 ,x_agreement_attribute5
310 ,x_agreement_attribute6
314 ,x_agreement_attribute10
311 ,x_agreement_attribute7
312 ,x_agreement_attribute8
313 ,x_agreement_attribute9
315 ,x_agreement_attribute11
316 ,x_agreement_attribute12
317 ,x_agreement_attribute13
318 ,x_agreement_attribute14
319 ,x_agreement_attribute15
320 ,x_currency_code
321 ,x_carry_forward_flag
322 ,x_retain_initial_basis_flag
323 ,x_vr_nbp_flag
324 ,x_index_multiplier
325 ,x_proration_rule
326 ,x_proration_period_start_date );
327
328
329 -- Check if a valid record was created.
330 OPEN c;
331 FETCH c INTO x_rowid;
332 IF (c%NOTFOUND)
333 THEN
334 CLOSE c;
335 RAISE NO_DATA_FOUND;
336 END IF;
337 CLOSE c;
338
339 END insert_row;
340
341
342 -------------------------------------------------------------------------------
343 -- PROCDURE : update_row
344 -- INVOKED FROM : update_row procedure
345 -- PURPOSE : updates the row
346 -- HISTORY :
347 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases with _ALL table
348 -- and changed the where clause.
349 -- 14-AUG-06 pikhar o Added vr_nbp_flag to update
350 -- 09-NOV-06 prabhakar o Added index_multiplier to update_row.
351 -- 08-NOV-06 Prabhakar o Added proration_rule and pn_proration_period_start_date.
352 -------------------------------------------------------------------------------
353 PROCEDURE update_row (
354 x_rowid IN VARCHAR2
355 ,x_index_lease_id IN NUMBER
356 ,x_lease_id IN NUMBER
357 ,x_index_id IN NUMBER
358 ,x_commencement_date IN DATE
359 ,x_termination_date IN DATE
360 ,x_index_lease_number IN VARCHAR2
361 ,x_last_update_date IN DATE
362 ,x_last_updated_by IN NUMBER
363 ,x_location_id IN NUMBER
364 ,x_term_template_id IN NUMBER
365 ,x_abstracted_by IN NUMBER
366 ,x_assessment_date IN DATE
367 ,x_assessment_interval IN NUMBER
368 ,x_spread_frequency IN VARCHAR2
369 ,x_relationship_default IN VARCHAR2
370 ,x_basis_percent_default IN NUMBER
371 ,x_initial_basis IN NUMBER
372 ,x_base_index IN NUMBER
373 ,x_base_index_line_id IN NUMBER
374 ,x_index_finder_method IN VARCHAR2
375 ,x_index_finder_months IN NUMBER
376 ,x_negative_rent_type IN VARCHAR2
377 ,x_increase_on IN VARCHAR2
378 ,x_basis_type IN VARCHAR2
379 ,x_reference_period IN VARCHAR2
380 ,x_base_year IN DATE
381 ,x_leased_area IN NUMBER
382 ,x_rounding_flag IN VARCHAR2
383 ,x_aggregation_flag IN VARCHAR2
384 ,x_gross_flag IN VARCHAR2
385 ,x_last_update_login IN NUMBER
386 ,x_attribute_category IN VARCHAR2
387 ,x_attribute1 IN VARCHAR2
388 ,x_attribute2 IN VARCHAR2
389 ,x_attribute3 IN VARCHAR2
390 ,x_attribute4 IN VARCHAR2
391 ,x_attribute5 IN VARCHAR2
392 ,x_attribute6 IN VARCHAR2
393 ,x_attribute7 IN VARCHAR2
394 ,x_attribute8 IN VARCHAR2
395 ,x_attribute9 IN VARCHAR2
396 ,x_attribute10 IN VARCHAR2
397 ,x_attribute11 IN VARCHAR2
398 ,x_attribute12 IN VARCHAR2
399 ,x_attribute13 IN VARCHAR2
400 ,x_attribute14 IN VARCHAR2
401 ,x_attribute15 IN VARCHAR2
402 ,x_agreement_category IN VARCHAR2
403 ,x_agreement_attribute1 IN VARCHAR2
404 ,x_agreement_attribute2 IN VARCHAR2
405 ,x_agreement_attribute3 IN VARCHAR2
406 ,x_agreement_attribute4 IN VARCHAR2
407 ,x_agreement_attribute5 IN VARCHAR2
408 ,x_agreement_attribute6 IN VARCHAR2
409 ,x_agreement_attribute7 IN VARCHAR2
410 ,x_agreement_attribute8 IN VARCHAR2
411 ,x_agreement_attribute9 IN VARCHAR2
412 ,x_agreement_attribute10 IN VARCHAR2
413 ,x_agreement_attribute11 IN VARCHAR2
414 ,x_agreement_attribute12 IN VARCHAR2
415 ,x_agreement_attribute13 IN VARCHAR2
416 ,x_agreement_attribute14 IN VARCHAR2
417 ,x_agreement_attribute15 IN VARCHAR2
418 ,x_currency_code IN VARCHAR2
419 ,x_carry_forward_flag IN VARCHAR2
420 ,x_retain_initial_basis_flag IN VARCHAR2
421 ,x_vr_nbp_flag IN VARCHAR2
422 ,x_index_multiplier IN NUMBER
423 ,x_proration_rule IN VARCHAR2
424 ,x_proration_period_start_date IN DATE)
425 IS
426 l_return_status VARCHAR2 (30) := NULL;
427 BEGIN
428 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.update_row (+)');
429 pn_index_leases_pkg.check_unq_index_lease_number (
430 l_return_status
431 ,x_index_lease_id
432 ,x_index_lease_number);
433
434 IF (l_return_status IS NOT NULL)
435 THEN
436 app_exception.raise_exception;
437 END IF;
438
439
440 UPDATE pn_index_leases_all
444 ,termination_date = x_termination_date
441 SET lease_id = x_lease_id
442 ,index_id = x_index_id
443 ,commencement_date = x_commencement_date
445 ,index_lease_number = x_index_lease_number
446 ,last_update_date = x_last_update_date
447 ,last_updated_by = x_last_updated_by
448 ,location_id = x_location_id
449 ,term_template_id = x_term_template_id
450 ,abstracted_by = x_abstracted_by
451 ,assessment_date = x_assessment_date
452 ,assessment_interval = x_assessment_interval
453 ,spread_frequency = x_spread_frequency
454 ,relationship_default = x_relationship_default
455 ,basis_percent_default = x_basis_percent_default
456 ,initial_basis = x_initial_basis
457 ,base_index = x_base_index
458 ,base_index_line_id = x_base_index_line_id
459 ,index_finder_method = x_index_finder_method
460 ,index_finder_months = x_index_finder_months
461 ,negative_rent_type = x_negative_rent_type
462 ,increase_on = x_increase_on
463 ,basis_type = x_basis_type
464 ,reference_period = x_reference_period
465 ,base_year = x_base_year
466 ,leased_area = x_leased_area
467 ,rounding_flag = x_rounding_flag
468 ,aggregation_flag = x_aggregation_flag
469 ,gross_flag = x_gross_flag
470 ,last_update_login = x_last_update_login
471 ,attribute_category = x_attribute_category
472 ,attribute1 = x_attribute1
473 ,attribute2 = x_attribute2
474 ,attribute3 = x_attribute3
475 ,attribute4 = x_attribute4
476 ,attribute5 = x_attribute5
477 ,attribute6 = x_attribute6
478 ,attribute7 = x_attribute7
479 ,attribute8 = x_attribute8
480 ,attribute9 = x_attribute9
481 ,attribute10 = x_attribute10
482 ,attribute11 = x_attribute11
483 ,attribute12 = x_attribute12
484 ,attribute13 = x_attribute13
485 ,attribute14 = x_attribute14
486 ,attribute15 = x_attribute15
487 ,agreement_category = x_agreement_category
488 ,agreement_attribute1 = x_agreement_attribute1
489 ,agreement_attribute2 = x_agreement_attribute2
490 ,agreement_attribute3 = x_agreement_attribute3
491 ,agreement_attribute4 = x_agreement_attribute4
492 ,agreement_attribute5 = x_agreement_attribute5
493 ,agreement_attribute6 = x_agreement_attribute6
494 ,agreement_attribute7 = x_agreement_attribute7
495 ,agreement_attribute8 = x_agreement_attribute8
496 ,agreement_attribute9 = x_agreement_attribute9
497 ,agreement_attribute10 = x_agreement_attribute10
498 ,agreement_attribute11 = x_agreement_attribute11
499 ,agreement_attribute12 = x_agreement_attribute12
500 ,agreement_attribute13 = x_agreement_attribute13
501 ,agreement_attribute14 = x_agreement_attribute14
502 ,agreement_attribute15 = x_agreement_attribute15
503 ,currency_code = x_currency_code
504 ,carry_forward_flag = x_carry_forward_flag
505 ,retain_initial_basis_flag = x_retain_initial_basis_flag
506 ,vr_nbp_flag = x_vr_nbp_flag
507 ,index_multiplier = x_index_multiplier
508 ,proration_rule = x_proration_rule
509 ,proration_period_start_date = x_proration_period_start_date
510 WHERE index_lease_id = x_index_lease_id;
511
512 IF (SQL%NOTFOUND)
513 THEN
514 RAISE NO_DATA_FOUND;
515 END IF;
516
517 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.update_row (-)');
518 END update_row;
519
520
521 -------------------------------------------------------------------------------
522 -- PROCDURE : lock_row
523 -- INVOKED FROM : lock_row procedure
524 -- PURPOSE : locks the row
525 -- HISTORY :
526 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases with _ALL table
527 -- and changed the where clause.
528 -- 14-AUG-06 pikhar o Added vr_nbp_flag to lock
529 -- 09-NOV-06 prabhakar o Added index_multiplier to lock_row.
530 -- 08-NOV-06 Prabhakar o Added proration_rule and pn_proration_period_start_date.
531 -------------------------------------------------------------------------------
532 PROCEDURE lock_row (
533 x_rowid IN VARCHAR2
534 ,x_index_lease_id IN NUMBER
535 ,x_lease_id IN NUMBER
536 ,x_index_id IN NUMBER
537 ,x_commencement_date IN DATE
538 ,x_termination_date IN DATE
539 ,x_index_lease_number IN VARCHAR2
540 ,x_location_id IN NUMBER
541 ,x_term_template_id IN NUMBER
542 ,x_abstracted_by IN NUMBER
543 ,x_assessment_date IN DATE
544 ,x_assessment_interval IN NUMBER
545 ,x_spread_frequency IN VARCHAR2
546 ,x_relationship_default IN VARCHAR2
547 ,x_basis_percent_default IN NUMBER
548 ,x_initial_basis IN NUMBER
552 ,x_index_finder_months IN NUMBER
549 ,x_base_index IN NUMBER
550 ,x_base_index_line_id IN NUMBER
551 ,x_index_finder_method IN VARCHAR2
553 ,x_negative_rent_type IN VARCHAR2
554 ,x_increase_on IN VARCHAR2
555 ,x_basis_type IN VARCHAR2
556 ,x_reference_period IN VARCHAR2
557 ,x_base_year IN DATE
558 ,x_leased_area IN NUMBER
559 ,x_rounding_flag IN VARCHAR2
560 ,x_aggregation_flag IN VARCHAR2
561 ,x_gross_flag IN VARCHAR2
562 ,x_attribute_category IN VARCHAR2
563 ,x_attribute1 IN VARCHAR2
564 ,x_attribute2 IN VARCHAR2
565 ,x_attribute3 IN VARCHAR2
566 ,x_attribute4 IN VARCHAR2
567 ,x_attribute5 IN VARCHAR2
568 ,x_attribute6 IN VARCHAR2
569 ,x_attribute7 IN VARCHAR2
570 ,x_attribute8 IN VARCHAR2
571 ,x_attribute9 IN VARCHAR2
572 ,x_attribute10 IN VARCHAR2
573 ,x_attribute11 IN VARCHAR2
574 ,x_attribute12 IN VARCHAR2
575 ,x_attribute13 IN VARCHAR2
576 ,x_attribute14 IN VARCHAR2
577 ,x_attribute15 IN VARCHAR2
578 ,x_agreement_category IN VARCHAR2
579 ,x_agreement_attribute1 IN VARCHAR2
580 ,x_agreement_attribute2 IN VARCHAR2
581 ,x_agreement_attribute3 IN VARCHAR2
582 ,x_agreement_attribute4 IN VARCHAR2
583 ,x_agreement_attribute5 IN VARCHAR2
584 ,x_agreement_attribute6 IN VARCHAR2
585 ,x_agreement_attribute7 IN VARCHAR2
586 ,x_agreement_attribute8 IN VARCHAR2
587 ,x_agreement_attribute9 IN VARCHAR2
588 ,x_agreement_attribute10 IN VARCHAR2
589 ,x_agreement_attribute11 IN VARCHAR2
590 ,x_agreement_attribute12 IN VARCHAR2
591 ,x_agreement_attribute13 IN VARCHAR2
592 ,x_agreement_attribute14 IN VARCHAR2
593 ,x_agreement_attribute15 IN VARCHAR2
594 ,x_currency_code IN VARCHAR2
595 ,x_carry_forward_flag IN VARCHAR2
596 ,x_retain_initial_basis_flag IN VARCHAR2
597 ,x_vr_nbp_flag IN VARCHAR2
598 ,x_index_multiplier IN NUMBER
599 ,x_proration_rule IN VARCHAR2
600 ,x_proration_period_start_date IN DATE)
601 IS
602
603 CURSOR c1 IS
604 SELECT *
605 FROM pn_index_leases_all
606 WHERE INDEX_LEASE_ID = x_index_lease_id
607 FOR UPDATE OF index_lease_id NOWAIT;
608
609 tlinfo c1%ROWTYPE;
610 BEGIN
611
612 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.lock_row (+)');
613 OPEN c1;
614 FETCH c1 INTO tlinfo;
615 IF (c1%NOTFOUND)
616 THEN
617 CLOSE c1;
618 RETURN;
619 END IF;
620 CLOSE c1;
621
622 IF NOT (tlinfo.index_lease_id = x_index_lease_id) THEN
623 pn_var_rent_pkg.lock_row_exception('INDEX_LEASE_ID',tlinfo.index_lease_id);
624 END IF;
625
626 IF NOT (tlinfo.lease_id = x_lease_id) THEN
627 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.lease_id);
628 END IF;
629
630 IF NOT (tlinfo.index_id = x_index_id) THEN
631 pn_var_rent_pkg.lock_row_exception('INDEX_ID',tlinfo.index_id);
632 END IF;
633
634 IF NOT (tlinfo.commencement_date = x_commencement_date) THEN
635 pn_var_rent_pkg.lock_row_exception('COMMENCEMENT_DATE',tlinfo.commencement_date);
636 END IF;
637
638 IF NOT (tlinfo.termination_date = x_termination_date) THEN
639 pn_var_rent_pkg.lock_row_exception('tERMINATION_DATE',tlinfo.termination_date);
640 END IF;
641
642 IF NOT (tlinfo.index_lease_number = x_index_lease_number) THEN
643 pn_var_rent_pkg.lock_row_exception('inDEX_LEASE_NUMBER',tlinfo.index_lease_number);
644 END IF;
645
646 IF NOT ((tlinfo.location_id = x_location_id)
647 OR ((tlinfo.location_id IS NULL) AND x_location_id IS NULL)) THEN
648 pn_var_rent_pkg.lock_row_exception('loCATION_ID',tlinfo.location_id);
649 END IF;
650
651 IF NOT ((tlinfo.term_template_id = x_term_template_id)
652 OR ((tlinfo.term_template_id IS NULL) AND x_term_template_id IS NULL)) THEN
653 pn_var_rent_pkg.lock_row_exception('TERM_TEMPLATE_ID',tlinfo.term_template_id);
654 END IF;
655
656 IF NOT ((tlinfo.abstracted_by = x_abstracted_by)
657 OR ((tlinfo.abstracted_by IS NULL) AND x_abstracted_by IS NULL)) THEN
658 pn_var_rent_pkg.lock_row_exception('ABSTRACTED_BY',tlinfo.abstracted_by);
659 END IF;
660
661 IF NOT ((tlinfo.assessment_date = x_assessment_date)
662 OR ((tlinfo.assessment_date IS NULL) AND x_assessment_date IS NULL)) THEN
663 pn_var_rent_pkg.lock_row_exception('ASSESSMENT_DATE',tlinfo.assessment_date);
664 END IF;
665
666 IF NOT ((tlinfo.assessment_interval = x_assessment_interval)
667 OR ((tlinfo.assessment_interval IS NULL) AND x_assessment_interval IS NULL)) THEN
668 pn_var_rent_pkg.lock_row_exception('ASSESSMENT_INTERVAL',tlinfo.assessment_interval);
669 END IF;
670
671 IF NOT ((tlinfo.spread_frequency = x_spread_frequency)
672 OR ((tlinfo.spread_frequency IS NULL) AND x_spread_frequency IS NULL)) THEN
676 IF NOT ((tlinfo.relationship_default = x_relationship_default)
673 pn_var_rent_pkg.lock_row_exception('SPREAD_FREQUENCY',tlinfo.spread_frequency);
674 END IF;
675
677 OR ((tlinfo.relationship_default IS NULL) AND x_relationship_default IS NULL)) THEN
678 pn_var_rent_pkg.lock_row_exception('RELATIONSHIP_DEFAULT',tlinfo.relationship_default);
679 END IF;
680
681 IF NOT ((tlinfo.basis_percent_default = x_basis_percent_default)
682 OR ((tlinfo.basis_percent_default IS NULL) AND x_basis_percent_default IS NULL)) THEN
683 pn_var_rent_pkg.lock_row_exception('BASIS_PERCENT_DEFAULT',tlinfo.basis_percent_default);
684 END IF;
685
686 IF NOT ((tlinfo.initial_basis = x_initial_basis)
687 OR ((tlinfo.initial_basis IS NULL) AND x_initial_basis IS NULL)) THEN
688 pn_var_rent_pkg.lock_row_exception('INITIAL_BASIS',tlinfo.initial_basis);
689 END IF;
690
691 IF NOT ((tlinfo.base_index = x_base_index)
692 OR ((tlinfo.base_index IS NULL) AND x_base_index IS NULL)) THEN
693 pn_var_rent_pkg.lock_row_exception('BASE_INDEX',tlinfo.base_index);
694 END IF;
695
696 IF NOT ((tlinfo.base_index_line_id = x_base_index_line_id)
697 OR ((tlinfo.base_index_line_id IS NULL) AND x_base_index_line_id IS NULL)) THEN
698 pn_var_rent_pkg.lock_row_exception('BASE_INDEX_LINE_ID',tlinfo.base_index_line_id);
699 END IF;
700
701 IF NOT ((tlinfo.index_finder_method = x_index_finder_method)
702 OR ((tlinfo.index_finder_method IS NULL) AND x_index_finder_method IS NULL)) THEN
703 pn_var_rent_pkg.lock_row_exception('INDEX_FINDER_METHOD',tlinfo.index_finder_method);
704 END IF;
705
706 IF NOT ((tlinfo.index_finder_months = x_index_finder_months)
707 OR ((tlinfo.index_finder_months IS NULL) AND x_index_finder_months IS NULL)) THEN
708 pn_var_rent_pkg.lock_row_exception('INDEX_FINDER_MONTHS',tlinfo.index_finder_months);
709 END IF;
710
711 IF NOT ((tlinfo.negative_rent_type = x_negative_rent_type)
712 OR ((tlinfo.negative_rent_type IS NULL) AND x_negative_rent_type IS NULL)) THEN
713 pn_var_rent_pkg.lock_row_exception('NEGATIVE_RENT_TYPE',tlinfo.negative_rent_type);
714 END IF;
715
716 IF NOT ((tlinfo.increase_on = x_increase_on)
717 OR ((tlinfo.increase_on IS NULL) AND x_increase_on IS NULL)) THEN
718 pn_var_rent_pkg.lock_row_exception('INCREASE_ON',tlinfo.increase_on);
719 END IF;
720
721 IF NOT ((tlinfo.basis_type = x_basis_type)
722 OR ((tlinfo.basis_type IS NULL) AND x_basis_type IS NULL)) THEN
723 pn_var_rent_pkg.lock_row_exception('BASIS_TYPE',tlinfo.basis_type);
724 END IF;
725
726 IF NOT ((tlinfo.reference_period = x_reference_period)
727 OR ((tlinfo.reference_period IS NULL) AND x_reference_period IS NULL)) THEN
728 pn_var_rent_pkg.lock_row_exception('REFERENCE_PERIOD',tlinfo.reference_period);
729 END IF;
730
731 IF NOT ((tlinfo.base_year = x_base_year)
732 OR ((tlinfo.base_year IS NULL) AND x_base_year IS NULL)) THEN
733 pn_var_rent_pkg.lock_row_exception('BASE_YEAR',tlinfo.base_year);
734 END IF;
735
736 IF NOT ((tlinfo.leased_area = x_leased_area)
737 OR ((tlinfo.leased_area IS NULL) AND x_leased_area IS NULL)) THEN
738 pn_var_rent_pkg.lock_row_exception('LEASED_AREA',tlinfo.leased_area);
739 END IF;
740
741 IF NOT ((tlinfo.rounding_flag = x_rounding_flag)
742 OR ((tlinfo.rounding_flag IS NULL) AND x_rounding_flag IS NULL)) THEN
743 pn_var_rent_pkg.lock_row_exception('ROUNDING_FLAG',tlinfo.rounding_flag);
744 END IF;
745
746 IF NOT ((tlinfo.aggregation_flag = x_aggregation_flag)
747 OR ((tlinfo.aggregation_flag IS NULL) AND x_aggregation_flag IS NULL)) THEN
748 pn_var_rent_pkg.lock_row_exception('AGGREGATION_FLAG',tlinfo.aggregation_flag);
749 END IF;
750
751 IF NOT ((tlinfo.gross_flag = x_gross_flag)
752 OR ((tlinfo.gross_flag IS NULL) AND x_gross_flag IS NULL)) THEN
753 pn_var_rent_pkg.lock_row_exception('GROSS_FLAG',tlinfo.gross_flag);
754 END IF;
755
756 IF NOT ((tlinfo.AGREEMENT_CATEGORY = X_AGREEMENT_CATEGORY)
757 OR ((tlinfo.AGREEMENT_CATEGORY IS NULL) AND (X_AGREEMENT_CATEGORY IS NULL))) THEN
758 pn_var_rent_pkg.lock_row_exception('AGREEMENT_CATEGORY',tlinfo.AGREEMENT_CATEGORY);
759 END IF;
760
761 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE1 = X_AGREEMENT_ATTRIBUTE1)
762 OR ((tlinfo.AGREEMENT_ATTRIBUTE1 IS NULL) AND (X_AGREEMENT_ATTRIBUTE1 IS NULL))) THEN
763 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE1',tlinfo.AGREEMENT_ATTRIBUTE1);
764 END IF;
765
766 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE2 = X_AGREEMENT_ATTRIBUTE2)
767 OR ((tlinfo.AGREEMENT_ATTRIBUTE2 IS NULL) AND (X_AGREEMENT_ATTRIBUTE2 IS NULL))) THEN
768 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE2',tlinfo.AGREEMENT_ATTRIBUTE2);
769 END IF;
770
771 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE3 = X_AGREEMENT_ATTRIBUTE3)
772 OR ((tlinfo.AGREEMENT_ATTRIBUTE3 IS NULL) AND (X_AGREEMENT_ATTRIBUTE3 IS NULL))) THEN
773 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE3',tlinfo.AGREEMENT_ATTRIBUTE3);
774 END IF;
775
776 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE4 = X_AGREEMENT_ATTRIBUTE4)
777 OR ((tlinfo.AGREEMENT_ATTRIBUTE4 IS NULL) AND (X_AGREEMENT_ATTRIBUTE4 IS NULL))) THEN
781 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE5 = X_AGREEMENT_ATTRIBUTE5)
778 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE4',tlinfo.AGREEMENT_ATTRIBUTE4);
779 END IF;
780
782 OR ((tlinfo.AGREEMENT_ATTRIBUTE5 IS NULL) AND (X_AGREEMENT_ATTRIBUTE5 IS NULL))) THEN
783 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE5',tlinfo.AGREEMENT_ATTRIBUTE5);
784 END IF;
785
786 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE6 = X_AGREEMENT_ATTRIBUTE6)
787 OR ((tlinfo.AGREEMENT_ATTRIBUTE6 IS NULL) AND (X_AGREEMENT_ATTRIBUTE6 IS NULL))) THEN
788 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE6',tlinfo.AGREEMENT_ATTRIBUTE6);
789 END IF;
790
791 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE7 = X_AGREEMENT_ATTRIBUTE7)
792 OR ((tlinfo.AGREEMENT_ATTRIBUTE7 IS NULL) AND (X_AGREEMENT_ATTRIBUTE7 IS NULL))) THEN
793 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE7',tlinfo.AGREEMENT_ATTRIBUTE7);
794 END IF;
795
796 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE8 = X_AGREEMENT_ATTRIBUTE8)
797 OR ((tlinfo.AGREEMENT_ATTRIBUTE8 IS NULL) AND (X_AGREEMENT_ATTRIBUTE8 IS NULL))) THEN
798 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE8',tlinfo.AGREEMENT_ATTRIBUTE8);
799 END IF;
800
801 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE9 = X_AGREEMENT_ATTRIBUTE9)
802 OR ((tlinfo.AGREEMENT_ATTRIBUTE9 IS NULL) AND (X_AGREEMENT_ATTRIBUTE9 IS NULL))) THEN
803 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE9',tlinfo.AGREEMENT_ATTRIBUTE9);
804 END IF;
805
806 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE10 = X_AGREEMENT_ATTRIBUTE10)
807 OR ((tlinfo.AGREEMENT_ATTRIBUTE10 IS NULL) AND (X_AGREEMENT_ATTRIBUTE10 IS NULL))) THEN
808 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE10',tlinfo.AGREEMENT_ATTRIBUTE10);
809 END IF;
810
811 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE11 = X_AGREEMENT_ATTRIBUTE11)
812 OR ((tlinfo.AGREEMENT_ATTRIBUTE11 IS NULL) AND (X_AGREEMENT_ATTRIBUTE11 IS NULL))) THEN
813 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE11',tlinfo.AGREEMENT_ATTRIBUTE11);
814 END IF;
815
816 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE12 = X_AGREEMENT_ATTRIBUTE12)
817 OR ((tlinfo.AGREEMENT_ATTRIBUTE12 IS NULL) AND (X_AGREEMENT_ATTRIBUTE12 IS NULL))) THEN
818 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE12',tlinfo.AGREEMENT_ATTRIBUTE12);
819 END IF;
820
821 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE13 = X_AGREEMENT_ATTRIBUTE13)
822 OR ((tlinfo.AGREEMENT_ATTRIBUTE13 IS NULL) AND (X_AGREEMENT_ATTRIBUTE13 IS NULL))) THEN
823 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE13',tlinfo.AGREEMENT_ATTRIBUTE13);
824 END IF;
825
826 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE14 = X_AGREEMENT_ATTRIBUTE14)
827 OR ((tlinfo.AGREEMENT_ATTRIBUTE14 IS NULL) AND (X_AGREEMENT_ATTRIBUTE14 IS NULL))) THEN
828 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE14',tlinfo.AGREEMENT_ATTRIBUTE14);
829 END IF;
830
831 IF NOT ((tlinfo.AGREEMENT_ATTRIBUTE15 = X_AGREEMENT_ATTRIBUTE15)
832 OR ((tlinfo.AGREEMENT_ATTRIBUTE15 IS NULL) AND (X_AGREEMENT_ATTRIBUTE15 IS NULL))) THEN
833 pn_var_rent_pkg.lock_row_exception('AGREEMENT_ATTRIBUTE15',tlinfo.AGREEMENT_ATTRIBUTE15);
834 END IF;
835
836 IF NOT ((tlinfo.CURRENCY_CODE = X_CURRENCY_CODE)
837 OR ((tlinfo.CURRENCY_CODE IS NULL) AND (X_CURRENCY_CODE IS NULL))) THEN
838 pn_var_rent_pkg.lock_row_exception('CURRENCY_CODE',tlinfo.CURRENCY_CODE);
839 END IF;
840
841 IF NOT ((tlinfo.CARRY_FORWARD_FLAG = X_CARRY_FORWARD_FLAG)
842 OR ((tlinfo.CARRY_FORWARD_FLAG IS NULL) AND (X_CARRY_FORWARD_FLAG IS NULL))) THEN
843 pn_var_rent_pkg.lock_row_exception('CARRY_FORWARD_FLAG',tlinfo.CARRY_FORWARD_FLAG);
844 END IF;
845
846 IF NOT ((tlinfo.RETAIN_INITIAL_BASIS_FLAG = X_RETAIN_INITIAL_BASIS_FLAG)
847 OR ((tlinfo.RETAIN_INITIAL_BASIS_FLAG IS NULL) AND (X_RETAIN_INITIAL_BASIS_FLAG IS NULL))) THEN
848 pn_var_rent_pkg.lock_row_exception('RETAIN_INITIAL_BASIS_FLAG',tlinfo.RETAIN_INITIAL_BASIS_FLAG);
849 END IF;
850
851 IF NOT ((tlinfo.VR_NBP_FLAG = X_VR_NBP_FLAG)
852 OR ((tlinfo.VR_NBP_FLAG IS NULL) AND (X_VR_NBP_FLAG IS NULL))) THEN
853 pn_var_rent_pkg.lock_row_exception('VR_NBP_FLAG',tlinfo.VR_NBP_FLAG);
854 END IF;
855
856 IF NOT ((tlinfo.INDEX_MULTIPLIER = X_INDEX_MULTIPLIER)
857 OR ((tlinfo.INDEX_MULTIPLIER IS NULL) AND (X_INDEX_MULTIPLIER IS NULL))) THEN
858 pn_var_rent_pkg.lock_row_exception('VR_NBP_FLAG',tlinfo.INDEX_MULTIPLIER);
859 END IF;
860
861 IF NOT ((tlinfo.PRORATION_RULE = X_PRORATION_RULE)
862 OR ((tlinfo.PRORATION_RULE IS NULL) AND (X_PRORATION_RULE IS NULL))) THEN
863 pn_var_rent_pkg.lock_row_exception('VR_NBP_FLAG',tlinfo.PRORATION_RULE);
864 END IF;
865
866 IF NOT ((tlinfo.PRORATION_PERIOD_START_DATE = X_PRORATION_PERIOD_START_DATE)
867 OR ((tlinfo.PRORATION_PERIOD_START_DATE IS NULL) AND (X_PRORATION_PERIOD_START_DATE IS NULL))) THEN
868 pn_var_rent_pkg.lock_row_exception('VR_NBP_FLAG',tlinfo.PRORATION_PERIOD_START_DATE);
869 END IF;
870
871 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.lock_row (-)');
872
873 END lock_row;
874
875
876 -------------------------------------------------------------------------------
877 -- PROCDURE : delete_row
878 -- INVOKED FROM : delete_row procedure
879 -- PURPOSE : deletes the row
880 -- HISTORY :
881 -- 04-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases with _ALL table
882 -- and changed the where clause.
886 IS
883 -------------------------------------------------------------------------------
884 PROCEDURE delete_row (
885 x_rowid IN VARCHAR2)
887 BEGIN
888 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (+)');
889 DELETE FROM pn_index_leases_all
890 WHERE ROWID = x_rowid;
891
892 IF (SQL%NOTFOUND)
893 THEN
894 RAISE NO_DATA_FOUND;
895 END IF;
896 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (-)');
897 END delete_row;
898
899 -------------------------------------------------------------------------------
900 -- PROCDURE : delete_row
901 -- INVOKED FROM : delete_row procedure
902 -- PURPOSE : deletes the row
903 -- NOTE : overload delete_row to take PK as IN paramter instead of ROWID
904 -- HISTORY :
905 -- 04-JUL-05 hrodda o Bug 4284035 - Created
906 -------------------------------------------------------------------------------
907 PROCEDURE delete_row (
908 x_index_lease_id IN NUMBER) IS
909 BEGIN
913
910 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (+)');
911 DELETE FROM pn_index_leases_all
912 WHERE index_lease_id = x_index_lease_id;
914 IF (SQL%NOTFOUND)
915 THEN
916 RAISE NO_DATA_FOUND;
917 END IF;
918
919 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASES_PKG.delete_row (-)');
920 END delete_row;
921
922
923
924 -------------------------------------------------------------------------------
925 -- PROCDURE : check_unq_index_lease_number
926 -- INVOKED FROM : update_row and insert_row procedure
927 -- PURPOSE : checks unique index number
928 -- HISTORY :
929 -- 04-JUL-05 hrodda o Bug 4284035 - Replace pn_index_leases with _ALL tables
930 -------------------------------------------------------------------------------
931 PROCEDURE check_unq_index_lease_number (
932 x_return_status IN OUT NOCOPY VARCHAR2
933 ,x_index_lease_id IN NUMBER
934 ,x_index_lease_number IN VARCHAR2)
935 IS
936 l_dummy NUMBER;
937 BEGIN
938 SELECT 1
939 INTO l_dummy
940 FROM DUAL
941 WHERE NOT EXISTS ( SELECT 1
942 FROM pn_index_leases_all
943 WHERE (index_lease_number = x_index_lease_number)
944 AND ( (x_index_lease_id IS NULL)
945 OR (index_lease_id <> x_index_lease_id)));
946 EXCEPTION
947 WHEN NO_DATA_FOUND
948 THEN
949 fnd_message.set_name ('PN', 'PN_DUP_INDEX_LEASE_NUMBER');
950
951 --fnd_message.set_token('INDEX_LEASE_NUMBER', x_INDEX_LEASE_NUMBER);
952 x_return_status := 'E';
953 END check_unq_index_lease_number;
954
955
956 END pn_index_leases_pkg;
957
958
[Home] [Help]