[Home] [Help]
PACKAGE BODY: APPS.PN_INDEX_RENT_PERIODS_PKG
Source
1 PACKAGE BODY pn_index_rent_periods_pkg AS
2 -- $Header: PNINRPRB.pls 120.38.12020000.3 2013/03/11 13:07:25 ppenumar ship $
3
4 -- +===========================================================================+
5 -- | Copyright (c) 2001 Oracle Corporation
6 -- | Redwood Shores, California, USA
7 -- | All rights reserved.
8 -- +===========================================================================+
9 -- | Name
10 -- | pn_index_rent_periods_pkg
11 -- |
12 -- | Description
13 -- | This package contains procedures used to maintain index rent periods
14 -- |
15 -- |
16 -- | History
17 -- | 27-MAR-01 jreyes Created
18 -- | 22-jul-01 psidhu Added procedure PROCESS_PAYMENT_TERM_AMENDMENT.
19 -- | Added condition 'ALL' to procedure DELETE_PERIODS.
20 -- | 20-Sep-01 psidhu Added procedure RECALC_OT_PAYMENT_TERMS.
21 -- | Added parameters p_old_main_lease_term_date and
22 -- | p_lease_context to procedure PROCESS_MAIN_LEASE_TERM_DATE.
23 -- | 05-dec-01 achauhan In the call to create_payment_term_record added the parameter
24 -- | op_payment_term_id.
25 -- | 06-dec-01 achauhan In delete_periods added the code to delete from
26 -- | pn_index_lease_terms
27 -- | 07-Mar-02 lkatputu Bug Fix for #2254491.
28 -- | For the generate_periods_BATCH Procedure the datatype of
29 -- | p_index_lease_num has been changed from NUMBER to VARCHAR2
30 -- | in the Cursor index_lease_periods.
31 -- | 08-Mar-02 lkatputu Added the following lines at the beginning.
32 -- | Added for ARU db drv auto generation
33 -- | 17-May-04 vmmehta Changed procedure process_payment_term_amendment
34 -- | Check for retain_initial_basis_flag and overwrite initial_basis only if flag is not set.
35 -- |
36 -- | 24-AUG-04 ftanudja o Added logic to check profile option value before
37 -- | extending index rent term in process_main_lease_term_date().
38 -- | #3756208.
39 -- | o Changed instances of updation using nvl(fnd_profile(),0)
40 -- | to use fnd_global.user_id|login_id.
41 -- | 14-JUL-05 SatyaDeep o Replaced base views with their respective _ALL tables.
42 -- | 19-JAN-06 piagrawa o Bug#4931780 - Modified signature of
43 -- | process_main_lease_term_date, recalc_ot_payment_terms
44 -- | 24-NOV-06 Prabhakar o Added parameter index_multiplier to create_periods.
45 -- | 08-FEB-11 PSTAWAR o Commented 'AND end_date = p_old_termination_date' statement for bug 10053283
46 -- | 08-FEB-11 jlraj o Bug#10630670. Modified procedure pn_index_rent_pe
47 -- | 14-FEB-11 pstawar o Bug #11671285.
48 -- | 21-FEB-11 pstawar o Bug #11671285.
49 -- | 16-sep-11 krkondur o Bug #12755368.
50 -- +===========================================================================+
51
52
53 ------------------------------------------------------------------------
54 -- PROCEDURE : put_log
55 -- DESCRIPTION: This procedure will display the text in the log file
56 -- of a concurrent program
57 --
58 ------------------------------------------------------------------------
59
60 PROCEDURE put_log (
61 p_string IN VARCHAR2) IS
62 BEGIN
63 pn_index_lease_common_pkg.put_log (p_string);
64 END put_log;
65
66
67 ------------------------------------------------------------------------
68 -- PROCEDURE : put_output
69 -- DESCRIPTION: This procedure will display the text in the log file
70 -- of a concurrent program
71 --
72 ------------------------------------------------------------------------
73
74 PROCEDURE put_output (
75 p_string IN VARCHAR2) IS
76 BEGIN
77 pn_index_lease_common_pkg.put_output (p_string);
78 END put_output;
79
80
81 ------------------------------------------------------------------------
82 -- PROCEDURE : display_error_messages
83 -- DESCRIPTION: This procedure will parse a string of error message codes
84 -- delimited of with a comma. It will lookup each code using
85 -- fnd_messages routine.
86 ------------------------------------------------------------------------
87
88 PROCEDURE display_error_messages (
89 ip_message_string IN VARCHAR2) IS
90 message_string VARCHAR2 (4000);
91 msg_len NUMBER;
92 ind_message VARCHAR2 (40);
93 comma_loc NUMBER;
94 BEGIN
95 message_string := ip_message_string;
96
97 IF message_string IS NOT NULL THEN
98 -- append a comma to the end of the string.
99 message_string := message_string
100 || ',';
101 -- get location of the first comma
102 comma_loc := INSTR (message_string, ',', 1, 1);
103 -- get length of message
104 msg_len := LENGTH (message_string);
105 ELSE
106 comma_loc := 0;
107 END IF;
108
109 fnd_message.clear;
110
111 --
112 -- loop will cycle thru each occurrence of delimted text
113 -- and display message with its code..
114 --
115 WHILE comma_loc <> 0
116 LOOP
117 --
118 -- get error message to process
119 --
120 ind_message := SUBSTR (message_string, 1, comma_loc
121 - 1);
122
123 --
124 -- check the length of error message code
125 --
126 --
127 IF LENGTH (ind_message) > 30 THEN
128 put_log ( '**** MESSAGE CODE '
129 || ind_message
130 || ' TOO LONG');
131 ELSE
132 --put_log ( 'Message Code='
133 -- || ind_message);
134
135 --
136 -- Convert error message code to its 'user-friendly' message;
137 --
138 fnd_message.set_name ('PN', ind_message);
139 --
140 -- Display message to the output log
141 --
142 put_output ( '-->'
143 || fnd_message.get
144 || ' ('
145 || ind_message
146 || ')');
147 --
148 -- delete the current message from string of messges
149 -- e.g.
150 -- before: message_string = "message1, message2, message3,"
151 -- after: message_string = "message2, message3,"
152 --
153 message_string := SUBSTR (
154 message_string
155 , comma_loc
156 + 1
157 , LENGTH (message_string)
158 - comma_loc);
159 --
160 -- locate the first occurrence of a comma
161 --
162 comma_loc := INSTR (message_string, ',', 1, 1);
163 END IF; --LENGTH (ind_message) > 30
164 END LOOP;
165 END display_error_messages;
166
167
168 ------------------------------------------------------------------------
169 -- PROCEDURE : get_basis_dates
170 -- DESCRIPTION: This procedure will derive an index rent period's basis start and
171 -- end dates.
172 --
173 ------------------------------------------------------------------------
174 PROCEDURE get_basis_dates (
175 p_prev_asmt_dt IN DATE
176 ,p_curr_asmt_dt IN DATE
177 ,p_ml_start_dt IN DATE
178 ,p_basis_start_date OUT NOCOPY DATE
179 ,p_basis_end_date OUT NOCOPY DATE) IS
180 v_temp_assmt_dt DATE;
181 BEGIN
182
183 --
184 -- Derive Basis End Date
185 -- General Rule, Basis ends the day before assesment date.
186 p_basis_end_date := p_curr_asmt_dt
187 - 1;
188
189 --
190 -- Derive Basis Start Date
191 -- The basis start date will be the latest date of the following:
192 --
193 -- . main lease start date
194 -- . the prev. periods assessment date
195 -- . date a year before the basis end date
196
197 -- v_temp_assmt_dt is the day a year before the basis end-date.
198 --
199 v_temp_assmt_dt := ADD_MONTHS (p_basis_end_date, -12)
200 + 1;
201 --p_basis_start_date :=
202 -- GREATEST (NVL (p_prev_asmt_dt, v_temp_assmt_dt), v_temp_assmt_dt, p_ml_start_dt);
203 p_basis_start_date := GREATEST (v_temp_assmt_dt, p_ml_start_dt);
204
205
206 --
207 -- When Main Lease Start and Index Rent are equal, leave basis start and basis end blank.
208 --
209 IF p_ml_start_dt = p_curr_asmt_dt THEN
210 p_basis_start_date := NULL;
211 p_basis_end_date := NULL;
212 END IF;
213 END get_basis_dates;
214
215
216 ------------------------------------------------------------------------
217 -- PROCEDURE : generate_basis_data_check
218 -- DESCRIPTION: This procedure will check that all business rules are enforced before
219 -- generating periods.
220 -- -
221 -- ARGUEMENTS : p_index_lease_id - Index Lease ID
222 --
223 ------------------------------------------------------------------------
224 PROCEDURE generate_basis_data_check (
225 p_index_lease_id IN NUMBER
226 ,p_msg OUT NOCOPY VARCHAR2) AS
227 CURSOR il_rec (
228 ip_index_lease_id IN NUMBER) IS
229 SELECT pld.lease_commencement_date
230 ,pil.index_lease_number
231 ,pil.assessment_date
232 ,pil.commencement_date
233 ,pil.termination_date
234 ,pil.assessment_interval
235 ,pil.relationship_default
236 ,pil.basis_percent_default
237 FROM pn_index_leases_all pil, pn_lease_details_all pld
238 WHERE pld.lease_id = pil.lease_id
239 AND pil.index_lease_id = ip_index_lease_id;
240
241 tlinfo il_rec%ROWTYPE;
242 v_msg VARCHAR2 (200);
243 BEGIN
244 OPEN il_rec (p_index_lease_id);
245 FETCH il_rec INTO tlinfo;
246
247 IF (il_rec%NOTFOUND) THEN
248 CLOSE il_rec;
249 v_msg := 'PN_LEASE_NOT_FOUND';
250 put_log (' Error: Index or Main Lease not found');
251 RETURN;
252 END IF;
253
254 --Business Rule: The following fields are required
255 -- - Index Rent Commencement and Termination Date
256 -- - Main Lease Start Date
257 -- - Date Assessed
258 -- - Assessment Frequency
259 -- as of 5/17 removed relationship default and basis % default are optional
260 -- will keep code, just in case.
261 -- - Relationship Default
262 -- - Basis Percent Default
263 IF ( tlinfo.lease_commencement_date IS NULL
264 OR tlinfo.assessment_date IS NULL
265 OR tlinfo.commencement_date IS NULL
266 OR tlinfo.termination_date IS NULL
267 OR tlinfo.assessment_interval IS NULL --OR tlinfo.relationship_default IS NULL
268 --OR tlinfo.basis_percent_default IS NULL
269 ) THEN
270 put_log (
271 'tlinfo.lease_commencement_date '
272 || NVL (TO_CHAR (tlinfo.lease_commencement_date, 'DD-MON-YYYY'), 'NOT FOUND'));
273 put_log (
274 'tlinfo.assessment_date '
275 || NVL (TO_CHAR (tlinfo.assessment_date, 'DD-MON-YYYY'), 'NOT FOUND'));
276 put_log (
277 'tlinfo.commencement_date '
278 || NVL (TO_CHAR (tlinfo.commencement_date, 'DD-MON-YYYY'), 'NOT FOUND'));
279 put_log (
280 'tlinfo.termination_date '
281 || NVL (TO_CHAR (tlinfo.termination_date, 'DD-MON-YYYY'), 'NOT FOUND'));
282 put_log (
283 'tlinfo.assessment_interval '
284 || NVL (TO_CHAR (tlinfo.assessment_interval), 'NOT FOUND'));
285 -- as of 5/17 removed relationship default and basis % default are optional
286 -- will keep code, just in case.
287 --
288 --put_log (
289 -- 'tlinfo.relationship_default '
290 -- || NVL (tlinfo.relationship_default, 'NOT FOUND')
291 --);
292 --put_log (
293 -- 'tlinfo.basis_percent_default '
294 -- || NVL (TO_CHAR (tlinfo.basis_percent_default), 'NOT FOUND')
295 --);
296 v_msg := 'PN_REQ_FIELD_MISSING_GEN_BASIS';
297 put_log (' ERROR: Missing one or more required fields');
298 --Business Rule: The following fields are required
299 -- - Assessment Frequency must be greater than 0
300 ELSIF (NVL (tlinfo.assessment_interval, 0) <= 0) THEN
301 v_msg := 'PN_ASMT_FREQ_MIN';
302 put_log (' ERROR: Assessment Frequency must be greater than 0');
303 ELSE
304 v_msg := 'PN_FOUND_ALL_REQD_FLDS';
305 END IF;
306
307 CLOSE il_rec;
308 p_msg := v_msg;
309 END generate_basis_data_check;
310
311
312 ------------------------------------------------------------------------
313 -- PROCEDURE : DELETE_PERIODS
314 -- DESCRIPTION: This procedure will create periods for an index rent
315 --
316 -- 20-FEB-07 Hareesha o Bug #5884029 Delete terms and items,schedules
317 -- only when it has no approved schedules.
318 -- Else disassociate the term with the period.
319 -- 11-MAY-07 Hareesha o Bug6042299 Added parameter p_new_termination_date
320 -- When a period containing approved terms gets deleted,
321 -- populate those terms index_period_id with latest
322 -- index_period_id.
323 ------------------------------------------------------------------------
324 PROCEDURE delete_periods (
325 p_index_lease_id IN NUMBER
326 ,p_index_period_id IN NUMBER
327 ,p_ignore_approved_terms IN VARCHAR2
328 ,p_new_termination_date IN DATE) AS
329
330 CURSOR index_lease_periods (
331 ip_index_lease_id IN NUMBER
332 ,ip_index_period_id IN NUMBER) IS
333 SELECT pilp.index_lease_id,
334 pilp.index_period_id
335 FROM pn_index_lease_periods_all pilp
336 WHERE pilp.index_lease_id = ip_index_lease_id
337 AND ( pilp.index_period_id = ip_index_period_id
338 OR ip_index_period_id IS NULL);
339
340
341 CURSOR index_leases_payments (
342 ip_index_lease_id IN NUMBER
343 ,ip_index_period_id IN NUMBER) IS
344 SELECT pilp.index_period_id
345 ,ppt.payment_term_id
346 ,ppt.lease_id
347 FROM pn_index_lease_periods_all pilp, pn_payment_terms_all ppt
348 WHERE pilp.index_lease_id = ip_index_lease_id
349 AND pilp.index_period_id = ppt.index_period_id
350 AND pilp.index_period_id = ip_index_period_id;
351 var number;
352
353 CURSOR exists_approved_schedule( p_payment_term_id IN NUMBER) IS
354 SELECT 'Y'
355 FROM DUAL
356 WHERE EXISTS( SELECT payment_item_id
357 FROM pn_payment_items_all items,
358 pn_payment_schedules_all sched
359 WHERE sched.payment_schedule_id = items.payment_schedule_id
360 AND items.payment_term_id = p_payment_term_id
361 AND sched.payment_status_lookup_code = 'APPROVED');
362
363 CURSOR get_latest_period IS
364 SELECT index_period_id
365 FROM pn_index_lease_periods_all
366 WHERE index_lease_id = p_index_lease_id
367 AND assessment_date <= p_new_termination_date
368 ORDER BY assessment_date DESC;
369
370 l_latest_period_id NUMBER := NULL;
371 l_exists_appr_schedule BOOLEAN := FALSE;
372
373 BEGIN
374 --
375 -- When deleting index rent periods.
376 -- You need to delete:
377 -- All payment items
378 -- All index rent periods
379
380
381 FOR il_rec_periods IN index_lease_periods (p_index_lease_id, p_index_period_id)
382 LOOP
383 --DBMS_OUTPUT.put_line ('il_rec_periods.index_lease_id=' || il_rec_periods.index_lease_id);
384
385
386 --DBMS_OUTPUT.put_line ('il_rec_periods.index_period_id=' ||il_rec_periods.index_period_id );
387
388
389
390 FOR il_rec IN index_leases_payments (il_rec_periods.index_lease_id, il_rec_periods.index_period_id)
391 LOOP
392
393 --
394 -- delete payment terms..
395 --
396 -- if p_ignore_approved_terms = 'Y', only delete payment
397 -- terms that are not of status 'APPROVED'
398 --
399 IF p_ignore_approved_terms = 'Y' THEN
400 --DBMS_OUTPUT.put_line ('deleting only non-approved payment terms...');
401 DELETE FROM pn_distributions_all
402 WHERE payment_term_id IN
403 (SELECT payment_term_id
404 FROM pn_payment_terms_all
405 WHERE payment_term_id = il_rec.payment_term_id
406 AND status<>
407 pn_index_amount_pkg.c_payment_term_status_approved);
408
409 DELETE FROM pn_payment_terms_all
410 WHERE payment_term_id = il_rec.payment_term_id
411 AND status <> pn_index_amount_pkg.c_payment_term_status_approved;
412
413 DELETE FROM pn_index_lease_terms_all
414 WHERE rent_increase_term_id = il_rec.payment_term_id
415 AND approved_flag <> pn_index_amount_pkg.c_payment_term_status_approved;
416
417 ELSIF p_ignore_approved_terms = 'ALL' THEN
418 --since we are also deleting approved payment terms, schedules and items
419 --associated with those approved terms would have to be deleted too.
420 put_log(' delete periods : payment term id ='||il_rec.payment_term_id);
421
422 FOR appr_sched IN exists_approved_schedule(il_rec.payment_term_id) LOOP
423 l_exists_appr_schedule := TRUE;
424 END LOOP;
425
426 IF l_exists_appr_schedule THEN
427
428 FOR last_period_rec IN get_latest_period LOOP
429 l_latest_period_id := last_period_rec.index_period_id;
430 EXIT;
431 END LOOP;
432
433 UPDATE pn_payment_terms_all
434 SET index_period_id = l_latest_period_id,
435 index_term_indicator = 'REVERSED'
436 WHERE payment_term_id = il_rec.payment_term_id;
437
438 ELSE
439 BEGIN
440
441 DELETE FROM pn_payment_items_all
442 WHERE payment_term_id =il_rec.payment_term_id;
443
444 DELETE FROM pn_payment_schedules_all pps
445 WHERE not exists(SELECT 1
446 FROM PN_PAYMENT_ITEMS_ALL ppi
447 WHERE ppi.payment_schedule_id=pps.payment_schedule_id)
448 AND pps.lease_id=il_rec.lease_id;
449
450
451 EXCEPTION
452 When others then null;
453 END;
454
455
456
457
458 DELETE FROM pn_distributions_all
459 WHERE payment_term_id = il_rec.payment_term_id;
460
461
462 DELETE FROM pn_payment_terms_all
463 WHERE payment_term_id = il_rec.payment_term_id;
464
465 DELETE FROM pn_index_lease_terms_all
466 where rent_increase_term_id = il_rec.payment_term_id;
467 END IF;
468
469 ELSE
470
471 DELETE FROM pn_payment_terms_all
472 WHERE payment_term_id = il_rec.payment_term_id;
473
474 DELETE FROM pn_distributions_all
475 WHERE payment_term_id = il_rec.payment_term_id;
476
477 DELETE FROM pn_index_lease_terms_all
478 WHERE rent_increase_term_id = il_rec.payment_term_id;
479 END IF;
480
481 END LOOP;
482
483 --
484 -- deleting index rent period record.
485 --
486 DELETE pn_index_lease_periods_all
487 WHERE index_lease_id = p_index_lease_id
488 AND index_period_id = il_rec_periods.index_period_id;
489 --DBMS_OUTPUT.put_line ('deleting periods...'||sql%rowcount);
490 end loop;
491 END delete_periods;
492
493 -------------------------------------------------------------------------------
494 -- PROCDURE : create_periods
495 -- INVOKED FROM :
496 -- PURPOSE :
497 -- HISTORY :
498 -- 29-JUL-05 piagrawa o Bug 4284035 - Passed org id in call to
499 -- pn_index_lease_periods_pkg.insert_row
500 -- 24-NOV-06 Prabhakar o Added index_multiplier parameter.
501 -- 03-NOV-11 ppenumar o Bug#12379367. Modified to create period as long as
502 -- the assessment date is earlier or same as RI end date.
503
504 -------------------------------------------------------------------------------
505 PROCEDURE create_periods (
506 p_index_lease_id NUMBER
507 ,p_ir_start_dt DATE
508 ,p_ir_end_dt DATE
509 ,p_ml_start_dt DATE
510 ,p_date_assessed DATE
511 ,p_assessment_freq_years NUMBER
512 ,p_index_finder_months NUMBER
513 ,p_relationship_default pn_index_leases.relationship_default%TYPE
514 ,p_basis_percent_default pn_index_leases.basis_percent_default%TYPE
515 ,p_starting_period_num NUMBER
516 ,p_index_multiplier NUMBER)
517 IS
518 v_basis_start_date DATE;
519 v_basis_end_date DATE;
520 v_x_index_finder_date DATE;
521 v_prev_asmt_dt DATE;
522 v_curr_asmt_dt DATE;
523 v_next_asmt_dt DATE;
524 v_period_number NUMBER := 0;
525 v_x_rowid VARCHAR2 (100);
526 v_period_id NUMBER;
527 v_assessment_freq_month NUMBER;
528 v_next_assessment_year VARCHAR2 (20);
529 v_org_id NUMBER;
530
531 CURSOR c IS
532 SELECT ORG_ID
533 FROM pn_index_leases_all
534 WHERE index_lease_id = p_index_lease_id;
535 BEGIN
536 -- all date calculation uses month
537 -- convert assessment frequency from years to month
538 --
539 v_assessment_freq_month := p_assessment_freq_years * 12;
540 -- First assessment date will always be the IR Commencement Date
541 --
542 v_curr_asmt_dt := p_ir_start_dt;
543
544 -- Derive Next Assessment Date:
545 -- if Indx Rent Comm Month and Day equal to or after Date Assessed Month and Day
546 -- use MM-DD of date assessed and Indx Rent Comm year + p_assessment_freq_years
547 -- otherwise
548 -- use MM-DD of date assessed and Indx Rent Comm + p_assessment_freq_years -1 ;
549 --
550
551 -- Deriving the year of next assessment date.
552 IF TO_NUMBER (TO_CHAR (p_ir_start_dt, 'MMDD')) <
553 TO_NUMBER (TO_CHAR (p_date_assessed, 'MMDD')) THEN
554 v_next_assessment_year :=
555 TO_NUMBER (TO_CHAR (p_ir_start_dt, 'YYYY'))
556 + p_assessment_freq_years
557 - 1;
558 ELSE
559 v_next_assessment_year :=
560 TO_NUMBER (TO_CHAR (p_ir_start_dt, 'YYYY'))
561 + p_assessment_freq_years;
562 END IF;
563
564 FOR c1 IN c LOOP
565 v_org_id := c1.org_id;
566 END LOOP;
567
568 -- Derive the next assessment date by gettin the mon-day of assesment date
569 -- and the year as calculated above.
570 v_next_asmt_dt := TO_DATE (
571 TO_CHAR (p_date_assessed, 'DD-MON-')
572 || v_next_assessment_year
573 ,'DD-MON-YYYY');
574 -- reset the period counter
575 v_period_number := p_starting_period_num;
576
577 --
578 -- Generate periods incrementing anniv_dt
579 --
580 WHILE v_curr_asmt_dt <= p_ir_end_dt
581 LOOP
582 -- reset period_id
583 v_period_id := NULL;
584 -- Derive Basis Start and End Dates
585 --
586 get_basis_dates (
587 v_prev_asmt_dt
588 ,v_curr_asmt_dt
589 ,p_ml_start_dt
590 ,v_basis_start_date
591 ,v_basis_end_date);
592 -- Derive Index Finder Date
593 -- Finder Date is adjusted based on the index finder months field on
594 -- the defaults tab...
595 --
596 v_x_index_finder_date :=
597 ADD_MONTHS (v_curr_asmt_dt, NVL (p_index_finder_months, 0));
598 -- Add a record to pn_index_lease_periods table
599 --
600 pn_index_lease_periods_pkg.insert_row (
601 x_rowid => v_x_rowid
602 ,x_org_id => v_org_id
603 ,x_index_period_id => v_period_id
604 -- should not be in out??
605 ,x_index_lease_id => p_index_lease_id
606 ,x_line_number => v_period_number
607 ,x_assessment_date => v_curr_asmt_dt
608 ,x_basis_start_date => v_basis_start_date
609 ,x_basis_end_date => v_basis_end_date
610 ,x_last_update_date => SYSDATE
611 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
612 ,x_creation_date => SYSDATE
613 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
614 ,x_index_finder_date => v_x_index_finder_date
615 ,x_current_index_line_id => NULL
616 ,x_current_index_line_value => NULL
617 ,x_previous_index_line_id => NULL
618 ,x_previous_index_line_value => NULL
619 ,x_current_basis => NULL
620 ,x_relationship => p_relationship_default
621 ,x_index_percent_change => NULL
622 ,x_basis_percent_change => p_basis_percent_default
623 ,x_unconstraint_rent_due => NULL
624 ,x_constraint_rent_due => NULL
625 ,x_last_update_login => NULL
626 ,x_attribute_category => NULL
627 ,x_attribute1 => NULL
628 ,x_attribute2 => NULL
629 ,x_attribute3 => NULL
630 ,x_attribute4 => NULL
631 ,x_attribute5 => NULL
632 ,x_attribute6 => NULL
633 ,x_attribute7 => NULL
634 ,x_attribute8 => NULL
635 ,x_attribute9 => NULL
636 ,x_attribute10 => NULL
637 ,x_attribute11 => NULL
638 ,x_attribute12 => NULL
639 ,x_attribute13 => NULL
640 ,x_attribute14 => NULL
641 ,x_attribute15 => NULL
642 ,x_index_multiplier => p_index_multiplier);
643 --
644 -- set dates to be used for next period
645 --
646 v_period_number := v_period_number
647 + 1;
648 v_prev_asmt_dt := v_curr_asmt_dt;
649 v_curr_asmt_dt := v_next_asmt_dt;
650 v_next_asmt_dt := ADD_MONTHS (v_curr_asmt_dt, v_assessment_freq_month);
651 END LOOP; --curr_aniv_dt < index_rent_end_dt
652 END create_periods;
653
654
655 ------------------------------------------------------------------------
656 -- PROCEDURE : CREATE_PERIOD
657 -- DESCRIPTION: This procedure will create periods for an index rent
658 --
659 ------------------------------------------------------------------------
660
661
662 /*===========================================================================+
663 | PROCEDURE
664 | print_basis_periods
665 |
666 | DESCRIPTION
667 | Sends report to output log of basis periods:
668 Sample:
669
670 Index Lease Number : 1020
671 Commencement Date : 31-MAY-99
672 End Date : 28-JUN-10
673 Assement Frequency : every 2 year(s)
674
675
676 Period Basis Basis Index Index
677 Number Start Date End Date Finder Date Assessment Date
678 ------- ----------- ----------- ----------- ---------------
679 1 01-MAY-1999 30-MAY-1999 31-JAN-1999 31-MAY-1999
680 2 22-MAR-2000 22-MAR-2001 23-NOV-2000 23-MAR-2001
681 3 22-MAR-2002 22-MAR-2003 23-NOV-2002 23-MAR-2003
682 4 22-MAR-2004 22-MAR-2005 23-NOV-2004 23-MAR-2005
683 5 22-MAR-2006 22-MAR-2007 23-NOV-2006 23-MAR-2007
684 6 22-MAR-2008 22-MAR-2009 23-NOV-2008 23-MAR-2009
685
686
687 |
688 | ARGUMENTS: p_index_lease_id
689 |
690 | NOTES:
691 | Called at all Debug points spread across this file
692 |
693 | MODIFICATION HISTORY
694 |
695 +===========================================================================*/
696
697 PROCEDURE print_basis_periods (
698 p_index_lease_id NUMBER) IS
699 CURSOR index_leases (
700 ip_index_lease_id IN NUMBER) IS
701 SELECT pil.index_lease_number
702 ,pil.commencement_date
703 ,pil.termination_date
704 ,pil.assessment_date "DATE_ASSESSED"
705 ,pil.assessment_interval
706 ,pil.relationship_default
707 ,pil.basis_percent_default
708 FROM pn_index_leases_all pil
709 WHERE pil.index_lease_id = ip_index_lease_id;
710
711 CURSOR index_lease_periods (
712 ip_index_lease_id IN NUMBER) IS
713 SELECT pilp.line_number
714 ,pilp.basis_start_date
715 ,pilp.basis_end_date
716 ,pilp.index_finder_date
717 ,pilp.assessment_date
718 FROM pn_index_lease_periods_all pilp
719 WHERE pilp.index_lease_id = ip_index_lease_id
720 ORDER BY pilp.line_number;
721
722 v_line_count NUMBER;
723 l_message VARCHAR2(2000) := NULL;
724
725 BEGIN
726 FOR il_rec IN index_leases (p_index_lease_id)
727 LOOP
728 -- Print the Header info of report
729 -- Will only be done once
730 fnd_message.set_name ('PN','PN_RICAL_LSNO');
731 fnd_message.set_token ('NUM', il_rec.index_lease_number);
732 put_output(fnd_message.get);
733
734 fnd_message.set_name ('PN','PN_MRIP_CM_DATE');
735 fnd_message.set_token ('DATE', il_rec.commencement_date);
736 put_output(fnd_message.get);
737
738 fnd_message.set_name ('PN','PN_MRIP_TM_DATE');
739 fnd_message.set_token ('DATE', il_rec.termination_date);
740 put_output(fnd_message.get);
741
742 fnd_message.set_name ('PN','PN_RICAL_ASS_DATE');
743 fnd_message.set_token ('DATE', TO_CHAR (il_rec.date_assessed, 'DD-MON'));
744 put_output(fnd_message.get);
745
746 fnd_message.set_name ('PN','PN_MRIP_ASM_FREQ');
747 fnd_message.set_token ('FREQ', il_rec.assessment_interval);
748 put_output(fnd_message.get);
749
750 fnd_message.set_name ('PN','PN_MRIP_PRD');
751 l_message := fnd_message.get;
752 fnd_message.set_name ('PN','PN_RICAL_BAS');
753 l_message := l_message||' '||fnd_message.get;
754 fnd_message.set_name ('PN','PN_RICAL_BAS');
755 l_message := l_message||' '||fnd_message.get;
756 fnd_message.set_name ('PN','PN_RICAL_INDX');
757 l_message := l_message||' '||fnd_message.get;
758 fnd_message.set_name ('PN','PN_RICAL_INDX');
759 l_message := l_message||' '||fnd_message.get;
760 put_output(l_message);
761
762 fnd_message.set_name ('PN','PN_MRIP_NUM');
763 l_message := fnd_message.get;
764 fnd_message.set_name ('PN','PN_MRIP_ST_DATE');
765 l_message := l_message||' '||fnd_message.get;
766 fnd_message.set_name ('PN','PN_MRIP_END_DATE');
767 l_message := l_message||' '||fnd_message.get;
768 fnd_message.set_name ('PN','PN_MRIP_FND_DATE');
769 l_message := l_message||' '||fnd_message.get;
770 fnd_message.set_name ('PN','PN_MRIP_ASM_DATE');
771 l_message := l_message||' '||fnd_message.get;
772 put_output(l_message);
773
774 put_output (
775 '------- ----------- ----------- ----------- ---------------');
776 -- Reset line counter for periods.
777 v_line_count := 0;
778
779 FOR ilp_rec IN index_lease_periods (p_index_lease_id)
780 LOOP
781 -- Print the Period Details
782 put_output (
783 LPAD (ilp_rec.line_number, 7, ' ')
784 || LPAD (
785 NVL (TO_CHAR (ilp_rec.basis_start_date, 'DD-MON-RRRR'), ' ')
786 ,13
787 ,' ')
788 || LPAD (NVL (TO_CHAR (ilp_rec.basis_end_date, 'DD-MON-RRRR'), ' '), 15, ' ')
789 || LPAD (TO_CHAR (ilp_rec.index_finder_date, 'MON-RRRR'), 13, ' ')
790 || LPAD (TO_CHAR (ilp_rec.assessment_date, 'DD-MON-RRRR'), 17, ' '));
791 v_line_count := v_line_count
792 + 1;
793 END LOOP;
794
795 --
796 -- Print Message if no basis periods found
797 --
798 IF v_line_count = 0 THEN
799 put_output ('**************************************');
800 fnd_message.set_name ('PN','PN_RICAL_NO_PRDS');
801 put_output(fnd_message.get);
802 put_output ('**************************************');
803 END IF;
804
805 END LOOP;
806 EXCEPTION
807 WHEN OTHERS THEN
808 RAISE;
809 END print_basis_periods;
810
811
812 /*
813 Does
814 Get all periods that have an assessment date after the termination date.
815
816 For each period:
817 Check if period is approved,
818 If found, display message: "New termination date is not valid."
819 exit loop;
820
821
822
823
824 */
825
826 ------------------------------------------------------------------------
827 -- PROCEDURE : undo_periods
828 -- DESCRIPTION: This procedure will undo index periods of an certain
829 -- index rent.
830 -- Periods can only be deleted if, no invoices has been exported
831 -- for any periods.
832 --
833 ------------------------------------------------------------------------
834 PROCEDURE undo_periods (
835 p_index_lease_id IN NUMBER
836 ,p_msg OUT NOCOPY VARCHAR2) AS
837 v_msg VARCHAR2 (100);
838 BEGIN
839
840 -----------------------------------------------------
841 --
842 -- Periods can only be deleted if no periods are approved, the following steps are taken:
843 -- - check that no index rent period has payment terms that have been approved.
844 --
845 --
846 pn_index_lease_common_pkg.chk_for_approved_index_periods (
847 p_index_lease_id => p_index_lease_id
848 ,p_index_lease_period_id => NULL
849 ,p_msg => v_msg);
850
851 IF v_msg = 'PN_APPROVED_PERIODS_NOT_FOUND' THEN
852 v_msg := NULL;
853 delete_periods (
854 p_index_lease_id => p_index_lease_id
855 ,p_index_period_id => NULL
856 ,p_ignore_approved_terms => 'N');
857 --v_msg := 'PN_UNDO_PRDS_SUCCESS';
858 ELSE
859 v_msg := 'PN_UNDO_PRDS_FAIL_APPROVE_PRDS';
860 END IF;
861
862 p_msg := v_msg;
863 END undo_periods;
864
865
866 --------------------------------------------------------------------------------
867 -- PROCEDURE : generate_periods_BATCH
868 -- DESCRIPTION: This procedure will get all the index rent to be processed
869 -- ARGUEMENTS : ip_index_lease_low_num - Index Number (From)
870 -- ip_index_lease_high_num - Index Number (To)
871 --
872 -- 07-Mar-02 lkatputu o Bug#2254491. datatype of p_index_lease_num changed from
873 -- NUMBER to VARCHAR2 in the Cursor index_lease_periods.
874 -- 25-NOV-05 Kiran o replaced pn_index_leases_all with pn_index_leases
875 -- 24-NOV-06 Prabhakar o Added index_multiplier in cursor attributes.
876 --------------------------------------------------------------------------------
877 PROCEDURE generate_periods_batch (
878 errbuf OUT NOCOPY VARCHAR2
879 ,retcode OUT NOCOPY VARCHAR2
880 ,ip_index_lease_num IN VARCHAR2
881 ,ip_regenerate_yn IN VARCHAR2) AS
882 CURSOR index_lease_periods (
883 p_index_lease_num IN VARCHAR2) IS
884 SELECT pil.index_lease_id
885 ,pil.index_lease_number
886 ,pld.lease_commencement_date
887 ,pil.assessment_date
888 ,pil.commencement_date
889 ,pil.termination_date
890 ,pil.assessment_interval
891 ,pil.relationship_default
892 ,pil.basis_percent_default
893 ,pil.index_finder_months
894 ,nvl (pil.index_multiplier,1) "INDEX_MULTIPLIER"
895 FROM pn_index_leases pil
896 ,pn_lease_details_all pld
897 WHERE pld.lease_id = pil.lease_id
898 AND ( pil.index_lease_number = p_index_lease_num
899 OR p_index_lease_num IS NULL)
900 ORDER BY pil.index_lease_number;
901
902 v_msg VARCHAR2 (100);
903 v_counter NUMBER := 0; -- no. of index leases found
904 v_periods_found BOOLEAN;
905 --
906 BEGIN
907 FOR ilp IN index_lease_periods (ip_index_lease_num)
908 LOOP
909 put_output ('****************************************');
910 fnd_message.set_name ('PN','PN_RICAL_PROC');
911 put_output(fnd_message.get||' ...');
912 fnd_message.set_name ('PN','PN_RICAL_LSNO');
913 fnd_message.set_token ('NUM', ilp.index_lease_number);
914 put_output(fnd_message.get);
915 put_output ('****************************************');
916 v_msg := NULL;
917
918 --
919 -- check if periods already exist for this index lease
920 --
921 -- results '1' - periods exists
922 -- NULL - no periods found
923
924 IF NVL (
925 pn_index_lease_common_pkg.find_if_period_exists (
926 p_index_lease_id => ilp.index_lease_id)
927 ,0) = 1 -- OR ip_regenerate_YN ='Y'
928 THEN
929 v_periods_found := TRUE;
930 ELSE
931 v_periods_found := FALSE;
932 END IF;
933
934 --
935 -- Generate periods if
936 -- - no periods found OR
937 -- - regenerate periods ="Y"
938 --
939 IF (NOT v_periods_found)
940 OR ip_regenerate_yn = 'Y' THEN
941 --
942 -- if periods were found then delete existing periods first..
943 --
944 IF v_periods_found THEN
945 pn_index_rent_periods_pkg.undo_periods (
946 p_index_lease_id => ilp.index_lease_id
947 ,p_msg => v_msg);
948 END IF;
949
950 --
951 --
952 --
953 IF v_msg IS NULL THEN
954 -- Verify that all required fields to generate periods
955 -- are available
956 generate_basis_data_check (ilp.index_lease_id, v_msg);
957
958 IF v_msg = 'PN_FOUND_ALL_REQD_FLDS' THEN
959 --create_periods (ilp.index_lease_id);
960 create_periods (
961 p_index_lease_id => ilp.index_lease_id
962 ,p_ir_start_dt => ilp.commencement_date
963 ,p_ir_end_dt => ilp.termination_date
964 ,p_ml_start_dt => ilp.lease_commencement_date
965 ,p_date_assessed => ilp.assessment_date
966 ,p_assessment_freq_years => ilp.assessment_interval
967 ,p_index_finder_months => ilp.index_finder_months
968 ,p_relationship_default => ilp.relationship_default
969 ,p_basis_percent_default => ilp.basis_percent_default
970 ,p_starting_period_num => 1
971 ,p_index_multiplier => ilp.index_multiplier);
972 v_msg := 'PN_GEN_PRDS_SUCCESS';
973 ELSE
974 v_msg := 'PN_GEN_PRDS_FAIL_REQD_FLDS';
975 END IF; -- v_msg is null;
976
977 print_basis_periods (ilp.index_lease_id);
978 v_counter := v_counter
979 + 1;
980 ELSE
981 --
982 -- cannot delete existing periods because approved periods were found
983 --
984
985 v_msg := 'PN_UNDO_PRDS_FAIL_APPROVE_PRDS';
986 END IF; --v_msg = 'PN_UNDO_PRDS_SUCCESS'
987 ELSE
988 v_msg := 'PN_INDX_PERIODS_EXISTS';
989 END IF; --!v_periods_found OR ip_regenerate_yn = 'Y'
990
991 fnd_message.set_name ('PN','PN_RICAL_MSG');
992 put_output (fnd_message.get);
993
994 fnd_message.set_name ('PN',v_msg);
995 put_log (fnd_message.get);
996
997 display_error_messages (ip_message_string => v_msg);
998 END LOOP;
999
1000 IF v_counter = 0 THEN
1001 put_log ('***********************************');
1002 put_log ('No Index Rent to process was found.');
1003 put_log ('***********************************');
1004 END IF;
1005 --op_msg := v_msg;
1006 END generate_periods_batch;
1007
1008
1009 ------------------------------------------------------------------------
1010 -- PROCEDURE : generate_periods
1011 -- DESCRIPTION: This procedure will get all the index rent to be processed
1012 -- ARGUEMENTS : ip_index_lease_low_num - Index Number (From)
1013 -- ip_index_lease_high_num - Index Number (To)
1014 --
1015 -- 24-NOV-2006 Prabhakar o Added index_multiplier in cursor attributes.
1016 ------------------------------------------------------------------------
1017 PROCEDURE generate_periods (
1018 ip_index_lease_id IN NUMBER
1019 ,op_msg OUT NOCOPY VARCHAR2) AS
1020 CURSOR index_lease_periods (
1021 p_index_lease_id IN NUMBER) IS
1022 SELECT pil.index_lease_id
1023 ,pil.index_lease_number
1024 ,pld.lease_commencement_date
1025 ,pil.assessment_date
1026 ,pil.commencement_date
1027 ,pil.termination_date
1028 ,pil.assessment_interval
1029 ,pil.relationship_default
1030 ,pil.basis_percent_default
1031 ,pil.index_finder_months
1032 ,nvl (pil.index_multiplier, 1) "INDEX_MULTIPLIER"
1033 FROM pn_index_leases_all pil, pn_lease_details_all pld
1034 WHERE pld.lease_id = pil.lease_id
1035 AND pil.index_lease_id = p_index_lease_id;
1036
1037 v_msg VARCHAR2 (100);
1038 v_counter NUMBER := 0; -- no. of index leases found
1039 --
1040 BEGIN
1041 --
1042 -- do not create periods if periods exists.
1043 --
1044
1045 IF NVL (
1046 pn_index_lease_common_pkg.find_if_period_exists (
1047 p_index_lease_id => ip_index_lease_id)
1048 ,0) <> 1 THEN
1049 FOR ilp IN index_lease_periods (ip_index_lease_id)
1050 LOOP
1051 put_log ( 'Processing Index Lease Number :'
1052 || ilp.index_lease_number);
1053 v_msg := NULL;
1054 -- Verify that all required fields to generate periods
1055 -- are available
1056 generate_basis_data_check (ilp.index_lease_id, v_msg);
1057
1058 IF v_msg = 'PN_FOUND_ALL_REQD_FLDS' THEN
1059 v_msg := NULL;
1060 create_periods (
1061 p_index_lease_id => ilp.index_lease_id
1062 ,p_ir_start_dt => ilp.commencement_date
1063 ,p_ir_end_dt => ilp.termination_date
1064 ,p_ml_start_dt => ilp.lease_commencement_date
1065 ,p_date_assessed => ilp.assessment_date
1066 ,p_assessment_freq_years => ilp.assessment_interval
1067 ,p_index_finder_months => ilp.index_finder_months
1068 ,p_relationship_default => ilp.relationship_default
1069 ,p_basis_percent_default => ilp.basis_percent_default
1070 ,p_starting_period_num => 1
1071 ,p_index_multiplier => ilp.index_multiplier);
1072 --v_msg := 'PN_GEN_PRDS_SUCCESS';
1073 ELSE
1074 v_msg := 'PN_GEN_PRDS_FAIL_REQD_FLDS';
1075 END IF; -- v_msg is null;
1076
1077 put_log (v_msg);
1078 print_basis_periods (ilp.index_lease_id);
1079 v_counter := v_counter
1080 + 1;
1081 --v_msg := NULL;
1082 END LOOP;
1083
1084 IF v_counter = 0 THEN
1085 put_log ('***********************************');
1086 put_log ('No Index Rent to process was found.');
1087 put_log ('***********************************');
1088 END IF;
1089 ELSE
1090 v_msg := 'PN_GEN_PRDS_FAIL_APPRV_PAY_FND';
1091 END IF;
1092
1093 op_msg := v_msg;
1094 END generate_periods;
1095
1096 -------------------------------------------------------------------------------
1097 -- PROCEDURE remove_agreement
1098 -- DESCRIPTION: This procedure will delete the future dated RI agreements
1099 -- with no approved schedules when a lease is early terminated.
1100 -- HISTORY:
1101 -- 17-OCT-06 Hareesha o Created.
1102 -------------------------------------------------------------------------------
1103 PROCEDURE remove_agreement (
1104 p_index_lease_id IN NUMBER
1105 ,p_new_termination_date IN DATE)
1106 IS
1107
1108 CURSOR approved_sched_exist(p_index_lease_id IN NUMBER) IS
1109 SELECT term.payment_term_id
1110 FROM pn_payment_schedules_all sched,
1111 pn_index_leases_all ilease,
1112 pn_payment_terms_all term,
1113 pn_payment_items_all item,
1114 pn_index_lease_periods_all period
1115 WHERE sched.lease_id = ilease.lease_id
1116 AND sched.payment_schedule_id = item.payment_schedule_id
1117 AND item.payment_term_id = term.payment_term_id
1118 AND term.lease_id = ilease.lease_id
1119 AND ilease.index_lease_id = p_index_lease_id
1120 AND period.index_lease_id = ilease.index_lease_id
1121 AND term.index_period_id = period.index_period_id
1122 AND term.index_period_id IS NOT NULL
1123 AND sched.payment_status_lookup_code = 'APPROVED'
1124 AND ilease.commencement_date > p_new_termination_date
1125 AND ilease.termination_date > p_new_termination_date ;
1126
1127 l_appr_sched_exists BOOLEAN := FALSE;
1128
1129 BEGIN
1130 put_log('remove_agreement (+) ');
1131
1132 FOR rec IN approved_sched_exist(p_index_lease_id) LOOP
1133 l_appr_sched_exists := TRUE;
1134 END LOOP;
1135
1136 IF NOT(l_appr_sched_exists) THEN
1137
1138 DELETE FROM pn_index_exclude_term_all
1139 WHERE index_lease_id = p_index_lease_id ;
1140
1141 DELETE FROM pn_index_lease_terms_all
1142 WHERE index_lease_id = p_index_lease_id ;
1143
1144 DELETE FROM pn_index_lease_constraints_all
1145 WHERE index_lease_id = p_index_lease_id ;
1146
1147 DELETE FROM pn_payment_items_all
1148 WHERE payment_term_id IN ( SELECT term.payment_term_id
1149 FROM pn_payment_terms_all term,pn_index_lease_periods_all iperiod
1150 WHERE term.index_period_id = iperiod.index_period_id
1151 AND iperiod.index_lease_id = p_index_lease_id
1152 AND term.index_period_id IS NOT NULL);
1153
1154 DELETE FROM pn_payment_schedules_all sched
1155 WHERE sched.payment_status_lookup_code = 'DRAFT'
1156 AND NOT EXISTS( SELECT payment_schedule_id
1157 FROM pn_payment_items_all item
1158 WHERE item.payment_schedule_id = sched.payment_schedule_id);
1159
1160 DELETE FROM pn_distributions_all
1161 WHERE payment_term_id IN (SELECT payment_term_id
1162 FROM pn_payment_terms_all term,pn_index_lease_periods_all period
1163 WHERE term.index_period_id = period.index_period_id
1164 AND period.index_lease_id = p_index_lease_id);
1165
1166 DELETE FROM pn_payment_terms_all
1167 WHERE index_period_id IN (SELECT index_period_id
1168 FROM pn_index_lease_periods_all
1169 WHERE index_lease_id = p_index_lease_id);
1170
1171 DELETE FROM pn_index_lease_periods_all
1172 WHERE index_lease_id = p_index_lease_id ;
1173
1174 DELETE FROM pn_index_leases_all
1175 WHERE index_lease_id = p_index_lease_id ;
1176
1177 put_log(' deleted agreements');
1178
1179 END IF;
1180
1181 put_log('remove_agreement (-) ');
1182
1183 EXCEPTION
1184 WHEN OTHERS THEN RAISE;
1185 END remove_agreement;
1186
1187
1188 ------------------------------------------------------------------------
1189 -- PROCEDURE : process_new_termination_date
1190 -- DESCRIPTION: This procedure will create periods for an index rent
1191 --
1192 -- 24-NOV-2006 Prabhakar o added index_multiplier in cursor attributes.
1193 -- 14-DEC-06 Hareesha o M28#19 Remove future-dated RI agreements who have
1194 -- no approved schedules when lease is early terminated.
1195 -- delete the RI agreement if there exist no periods for it
1196 ------------------------------------------------------------------------
1197 PROCEDURE process_new_termination_date (
1198 p_index_lease_id IN NUMBER
1199 ,p_new_termination_date IN DATE
1200 ,p_ignore_approved_terms IN VARCHAR2 --DEFAULT 'N'
1201 ,p_msg OUT NOCOPY VARCHAR2) AS
1202
1203 /*Given a new termination date;
1204 Get the latest date of index of assessment.
1205
1206 if termination date > latest assessment date
1207 POSSIBLE EXTENSION..
1208 if duration between termination date and latest assessment date > assesment frequency
1209 we have an index rent extension.
1210 Generate basis periods:
1211 - index number - start from last one + 1
1212 - start date (latest assessment date + assessment freq)
1213 - termination date (new date)
1214 else
1215 EARLY TERMINATE...
1216
1217 end if;*/
1218
1219 CURSOR il_rec (ip_index_lease_id IN NUMBER)
1220 IS
1221 SELECT pld.lease_commencement_date
1222 ,pil.index_lease_number
1223 ,pil.assessment_date
1224 ,pil.commencement_date
1225 ,pil.termination_date
1226 ,pil.assessment_interval
1227 ,pil.relationship_default
1228 ,pil.basis_percent_default
1229 ,pil.index_finder_months
1230 ,nvl (pil.index_multiplier, 1) "INDEX_MULTIPLIER"
1231 FROM pn_index_leases_all pil, pn_lease_details_all pld
1232 WHERE pld.lease_id = pil.lease_id
1233 AND pil.index_lease_id = ip_index_lease_id;
1234
1235 tlinfo il_rec%ROWTYPE;
1236
1237 CURSOR il_recs_to_delete (ip_index_lease_id IN NUMBER
1238 ,ip_new_termination_date IN DATE)
1239 IS
1240 SELECT pilp.index_period_id
1241 FROM pn_index_lease_periods_all pilp
1242 WHERE pilp.index_lease_id = ip_index_lease_id
1243 AND pilp.assessment_date > ip_new_termination_date;
1244
1245 v_latest_assessment_date DATE;
1246 v_months_bet_term_assmt_dt NUMBER;
1247 v_new_termination_date DATE;
1248 v_msg VARCHAR2 (1000);
1249 v_new_commencement_date DATE; -- used only for debugging...
1250 BEGIN
1251 -- getting the latest assessment date for this index rent period
1252 SELECT MAX (assessment_date)
1253 INTO v_latest_assessment_date
1254 FROM pn_index_lease_periods_all
1255 WHERE index_lease_id = p_index_lease_id;
1256 -- retreive index lease information.
1257
1258 OPEN il_rec (p_index_lease_id);
1259 FETCH il_rec INTO tlinfo;
1260
1261 IF (il_rec%NOTFOUND) THEN
1262 CLOSE il_rec;
1263 v_msg := 'PN_LEASE_NOT_FOUND';
1264 put_log (' Error: Index or Main Lease not found');
1265 p_msg := v_msg;
1266 RETURN;
1267 END IF;
1268
1269 IF tlinfo.commencement_date > p_new_termination_date THEN
1270 remove_agreement(p_index_lease_id,p_new_termination_date);
1271 END IF;
1272
1273 -- get the date to be used as new termination date.
1274 -- use the date passed in as a parameter or if none is passed,
1275 -- use what is saved in the database.
1276 IF p_new_termination_date IS NOT NULL THEN
1277 v_new_termination_date := p_new_termination_date;
1278 ELSE
1279 v_new_termination_date := tlinfo.termination_date;
1280 END IF;
1281
1282 -- determine if an early termination or extension of index rent.
1283 v_months_bet_term_assmt_dt :=MONTHS_BETWEEN (v_new_termination_date,
1284 v_latest_assessment_date);
1285
1286
1287 -- If months between terminate date and latest assessment date is positive,
1288 -- Processing an index extension,
1289 -- if negative, Processing an early termination.
1290
1291
1292 IF v_months_bet_term_assmt_dt > 0 THEN
1293
1294 --PROCESSING AN INDEX RENT EXTENSION
1295 put_log ('Processing an index rent extension');
1296
1297
1298 -- Only extend if the duration between the termination date and
1299 -- assessment date is larger than the assessment interval.
1300 --
1301
1302 IF v_months_bet_term_assmt_dt > (tlinfo.assessment_interval * 12) THEN
1303
1304 put_log (' Definitely Process extension');
1305 -- getting the greatest index period_number
1306
1307 create_periods (p_index_lease_id => p_index_lease_id
1308 ,p_ir_start_dt => ADD_MONTHS (v_latest_assessment_date
1309 , (tlinfo.assessment_interval * 12))
1310 ,p_ir_end_dt => v_new_termination_date
1311 ,p_ml_start_dt => tlinfo.lease_commencement_date
1312 ,p_date_assessed => tlinfo.assessment_date
1313 ,p_assessment_freq_years => tlinfo.assessment_interval
1314 ,p_index_finder_months => tlinfo.index_finder_months
1315 ,p_relationship_default => tlinfo.relationship_default
1316 ,p_basis_percent_default => tlinfo.basis_percent_default
1317 ,p_starting_period_num => NULL
1318 ,p_index_multiplier => tlinfo.index_multiplier);
1319 v_msg := 'PN_TERM_DATE_EXTENSION';
1320 END IF;
1321 ELSE
1322 ----PROCESSING AN INDEX RENT TERMINATION
1323 put_log ('Processing an index rent early termination request');
1324
1325 --
1326 -- check if any of the periods to be deleted have approved payment terms
1327 --
1328
1329
1330 /*IF p_ignore_approved_terms = 'N' THEN
1331 FOR il_rec IN il_recs_to_delete (p_index_lease_id, v_new_termination_date)
1332 LOOP
1333 pn_index_lease_common_pkg.chk_for_approved_index_periods (
1334 p_index_lease_id => p_index_lease_id
1335 ,p_index_lease_period_id => il_rec.index_period_id
1336 ,p_msg => v_msg);
1337 DBMS_OUTPUT.put_line ( '*******V_MSG='
1338 || v_msg);
1339 EXIT WHEN v_msg = 'PN_APPROVED_PERIODS_FOUND';
1340 END LOOP;
1341 ELSE
1342 v_msg := NULL;
1343 END IF; */
1344
1345 --
1346 -- if at least one approved payment term is found...
1347 --
1348 --IF v_msg = 'PN_APPROVED_PERIODS_FOUND' THEN
1349 --
1350 -- set error message
1351 --
1352 -- v_msg := 'PN_TERM_DATE_INV_APPR_PRDS';
1353 --ELSE
1354
1355
1356 --
1357 -- for early termination, delete all periods that have an assessment date,
1358 -- after the termination date
1359 --
1360 FOR il_rec IN il_recs_to_delete (p_index_lease_id, v_new_termination_date)
1361 LOOP
1362 put_log('process new termination date'||il_rec.index_period_id);
1363
1364 -- IF p_ignore_approved_terms = 'Y' THEN
1365 delete_periods (
1366 p_index_lease_id => p_index_lease_id
1367 ,p_index_period_id => il_rec.index_period_id
1368 ,p_ignore_approved_terms => 'ALL'
1369 ,p_new_termination_date => p_new_termination_date);
1370
1371 /* ELSE
1372 delete_periods (
1373 p_index_lease_id => p_index_lease_id
1374 ,p_index_period_id => il_rec.index_period_id
1375 ,p_ignore_approved_terms => 'N');
1376 END IF; */
1377 END LOOP;
1378
1379 v_msg := 'PN_TERM_DATE_EARLY_TERMINATION';
1380 --END IF; --V_MSG = 'PN_APPROVED_PERIODS_FOUND'
1381 END IF; --v_months_bet_term_assmt_dt > 0
1382
1383 CLOSE il_rec;
1384 print_basis_periods (p_index_lease_id);
1385 --p_msg := 'Comm Date:'||TO_CHAR( v_new_commencement_date, 'dd-Mon-yyyy ...' ) ||v_msg;
1386 p_msg := v_msg;
1387 END process_new_termination_date;
1388
1389
1390 --------------------------------------------------------------------------------
1391 -- PROCEDURE : recalc_ot_payment_terms
1392 -- DESCRIPTION: This procedure will recalculate payment terms amounts
1393 -- for one time recurring/atleast index rent payment terms
1394 -- when the main lease is expanded.
1395 -- 19-OCT-04 STripathi o BUG# 3961117 - get calculate_date and pass it for not
1396 -- to create backbills if Assessment Date <= CutOff Date
1397 -- 19-SEP-05 piagrawa o Modified to pass org id in call to Get_Calculate_Date
1398 -- 04-Jan-06 Kiran o Bug # 4922324 - fixed query
1399 -- SELECT '1' INTO v_approved_sch.. for perf.
1400 -- 19-JAN-06 piagrawa o Bug#4931780 - Modified signature and did handling to
1401 -- make sure that recalculation done only if norm end
1402 -- date > cut off date or term end date > cut off date
1403 -- 16-NOV-06 Prabhakar o Added p_end_date parameter in calls to the procedure
1404 -- create_payment_record.
1405 --------------------------------------------------------------------------------
1406
1407 PROCEDURE recalc_ot_payment_terms (ip_lease_id IN NUMBER,
1408 ip_index_lease_id IN NUMBER,
1409 ip_old_main_lease_term_date IN DATE,
1410 ip_new_main_lease_term_date IN DATE,
1411 ip_context IN VARCHAR2,
1412 ip_rounding_flag IN VARCHAR2,
1413 ip_relationship IN VARCHAR2,
1414 ip_cutoff_date IN DATE)
1415 IS
1416
1417 CURSOR cur_payment_terms(p_index_lease_id IN NUMBER)
1418 IS
1419 SELECT pilp.index_period_id,
1420 pilp.assessment_date,
1421 pilp.basis_start_date,
1422 ppt.payment_term_id,
1423 ppt.start_date,
1424 ppt.location_id,
1425 ppt.payment_purpose_code,
1426 ppt.frequency_code,
1427 ppt.normalize,
1428 ppt.index_term_indicator,
1429 ppt.status,
1430 ppt.currency_code,
1431 ppt.actual_amount,
1432 ppt.estimated_amount,
1433 decode(ppt.actual_amount,null,ppt.estimated_amount,ppt.actual_amount) term_amount,
1434 ppt.org_id,
1435 ppt.norm_end_date
1436 FROM pn_payment_terms_all ppt,
1437 pn_index_lease_periods_all pilp
1438 WHERE ppt.index_period_id = pilp.index_period_id
1439 AND pilp.index_lease_id = p_index_lease_id
1440 AND ppt.frequency_code = pn_index_amount_pkg.c_spread_frequency_one_time
1441 AND ppt.index_term_indicator not in(pn_index_amount_pkg.c_index_pay_term_type_atlst_bb,
1442 pn_index_amount_pkg.c_index_pay_term_type_backbill)
1443 AND ppt.start_date = ppt.end_date
1444 AND NVL( decode(ppt.actual_amount,null,ppt.estimated_amount,ppt.actual_amount),0 ) <> 0;
1445
1446 CURSOR cur_total_amount(p_payment_term_id IN NUMBER)
1447 IS
1448 SELECT sum(ppi.actual_amount) total_amount
1449 FROM pn_payment_items_all ppi
1450 WHERE ppi.payment_term_id = p_payment_term_id
1451 AND ppi.payment_item_type_lookup_code = 'CASH';
1452
1453 v_old_num_months NUMBER;
1454 v_new_num_months NUMBER;
1455 v_total_amount NUMBER := 0;
1456 v_prorated_amount NUMBER := 0;
1457 v_new_amount NUMBER := 0;
1458 v_exp_amount NUMBER := 0;
1459 v_con_amount NUMBER := 0;
1460 v_start_date PN_PAYMENT_TERMS.start_date%type := null;
1461 v_term_start_date DATE := null;
1462 v_term_day NUMBER;
1463 v_term_mth_year VARCHAR2(20);
1464 l_precision NUMBER;
1465 l_ext_precision NUMBER;
1466 l_min_acct_unit NUMBER;
1467 v_approved_sch NUMBER := 0;
1468 v_msg VARCHAR2(2000);
1469 l_payment_term_id PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE;
1470 l_calculate_date DATE;
1471
1472 BEGIN
1473 put_log('Recalculating one time index rent payment terms ..');
1474 put_log('Context :'||ip_context);
1475 put_log('Index Lease id :'||ip_index_lease_id);
1476 put_log('---------------');
1477
1478 FOR rec_payment_terms in cur_payment_terms(ip_index_lease_id)
1479 LOOP
1480 put_log('Index Period id : '||rec_payment_terms.index_period_id);
1481 put_log('Payment term id : '||rec_payment_terms.payment_term_id);
1482
1483 v_total_amount := 0;
1484 v_new_amount := 0;
1485 IF rec_payment_terms.status = pn_index_amount_pkg.c_payment_term_status_approved THEN
1486
1487 FOR rec_total_amount in cur_total_amount(rec_payment_terms.payment_term_id)
1488 LOOP
1489 v_total_amount := rec_total_amount.total_amount;
1490 END LOOP;
1491 ELSE
1492 v_total_amount := rec_payment_terms.term_amount;
1493 END IF;
1494
1495 l_calculate_date := pn_index_amount_pkg.Get_Calculate_Date(
1496 p_assessment_date => rec_payment_terms.assessment_date
1497 ,p_period_str_date => rec_payment_terms.basis_start_date
1498 ,p_org_id => rec_payment_terms.org_id
1499 );
1500
1501 IF trunc(rec_payment_terms.assessment_date) < trunc(rec_payment_terms.start_date) THEN
1502 IF to_char(rec_payment_terms.assessment_date,'DD') >
1503 to_char(rec_payment_terms.start_date,'DD') THEN
1504 v_term_mth_year := to_char( rec_payment_terms.start_date,'mm/yyyy');
1505 ELSE
1506 v_term_mth_year := to_char( last_day(rec_payment_terms.start_date)+1,'mm/yyyy');
1507 END IF;
1508
1509 v_term_start_date := TO_DATE( to_char(rec_payment_terms.assessment_date,'dd')||'/'||
1510 v_term_mth_year,'dd/mm/yyyy');
1511 ELSE
1512 v_term_start_date := rec_payment_terms.start_date;
1513 END IF;
1514
1515 v_old_num_months := CEIL (MONTHS_BETWEEN (ip_old_main_lease_term_date,
1516 v_term_start_date));
1517 v_new_num_months := CEIL (MONTHS_BETWEEN (ip_new_main_lease_term_date,
1518 v_term_start_date));
1519
1520 v_prorated_amount := v_total_amount / v_old_num_months;
1521 v_new_amount := v_prorated_amount * v_new_num_months;
1522
1523 IF ip_context = 'EXP' THEN
1524
1525 IF (( rec_payment_terms.normalize = 'Y'
1526 AND NVL(rec_payment_terms.norm_end_date, rec_payment_terms.start_date) > ip_cutoff_date)
1527 OR ( NVL(rec_payment_terms.normalize,'N') = 'N' AND rec_payment_terms.start_date > ip_cutoff_date))
1528 THEN
1529 v_exp_amount := v_new_amount - v_total_amount;
1530 v_start_date := trunc(ip_old_main_lease_term_date) + 1;
1531
1532 IF v_exp_amount <> 0 THEN
1533 pn_index_amount_pkg.create_payment_term_record (
1534 p_lease_id => ip_lease_id
1535 ,p_location_id => rec_payment_terms.location_id
1536 ,p_purpose_code => rec_payment_terms.payment_purpose_code
1537 ,p_index_period_id => rec_payment_terms.index_period_id
1538 ,p_term_template_id => NULL
1539 ,p_spread_frequency => rec_payment_terms.frequency_code
1540 ,p_rounding_flag => ip_rounding_flag
1541 ,p_payment_amount => v_exp_amount
1542 ,p_normalized => rec_payment_terms.normalize
1543 ,p_start_date => rec_payment_terms.start_date
1544 ,p_index_term_indicator => rec_payment_terms.index_term_indicator
1545 ,p_payment_term_id => rec_payment_terms.payment_term_id
1546 ,p_basis_relationship => ip_relationship
1547 ,p_called_from => 'MAIN'
1548 ,p_calculate_date => l_calculate_date
1549 ,op_payment_term_id => l_payment_term_id
1550 ,op_msg => v_msg
1551 ,p_end_date => NULL);
1552 END IF;
1553 END IF;
1554 ELSIF ip_context = 'CON' THEN
1555 IF v_new_amount <> v_total_amount AND
1556 NVL(v_new_amount,0) <> 0 THEN
1557
1558 BEGIN
1559
1560 SELECT '1'
1561 INTO v_approved_sch
1562 FROM pn_payment_schedules_all pps
1563 WHERE pps.payment_schedule_id IN
1564 (SELECT ppt.payment_schedule_id
1565 FROM pn_payment_items_all ppt
1566 WHERE ppt.payment_term_id = rec_payment_terms.payment_term_id
1567 AND ppt.export_currency_amount <> 0
1568 AND ppt.payment_item_type_lookup_code = 'CASH')
1569 AND pps.payment_status_lookup_code = 'APPROVED'
1570 AND ROWNUM < 2;
1571
1572 EXCEPTION
1573 WHEN no_data_found THEN null;
1574 WHEN others THEN put_log ( 'Unknow Error:' || SQLERRM);
1575 END;
1576
1577 IF NVL(v_approved_sch,0) <> 1 THEN
1578 fnd_currency.get_info(rec_payment_terms.currency_code, l_precision,l_ext_precision,
1579 l_min_acct_unit);
1580 v_new_amount := ROUND(v_new_amount, l_precision);
1581
1582 IF rec_payment_terms.actual_amount IS NOT NULL THEN
1583 UPDATE pn_payment_terms_all
1584 SET actual_amount = v_new_amount,
1585 last_update_date = SYSDATE,
1586 last_updated_by = fnd_global.user_id,
1587 last_update_login = fnd_global.login_id
1588 WHERE payment_term_id = rec_payment_terms.payment_term_id;
1589
1590 BEGIN
1591 UPDATE pn_payment_items_all
1592 SET actual_amount = v_new_amount,
1593 export_currency_amount = v_new_amount,
1594 last_update_date = SYSDATE,
1595 last_updated_by = fnd_global.user_id,
1596 last_update_login = fnd_global.login_id
1597 WHERE payment_term_id = rec_payment_terms.payment_term_id
1598 AND export_currency_amount <> 0
1599 AND payment_item_type_lookup_code = 'CASH';
1600
1601 EXCEPTION
1602 WHEN no_data_found THEN null;
1603 END;
1604
1605 ELSIF rec_payment_terms.estimated_amount IS NOT NULL THEN
1606
1607 UPDATE pn_payment_terms_all
1608 SET estimated_amount = v_new_amount,
1609 last_update_date = SYSDATE,
1610 last_updated_by = fnd_global.user_id,
1611 last_update_login = fnd_global.login_id
1612 WHERE payment_term_id = rec_payment_terms.payment_term_id;
1613
1614 BEGIN
1615 UPDATE pn_payment_items_all
1616 SET actual_amount = v_new_amount,
1617 estimated_amount = v_new_amount,
1618 export_currency_amount = v_new_amount,
1619 last_update_date = SYSDATE,
1620 last_updated_by = fnd_global.user_id,
1621 last_update_login = fnd_global.login_id
1622 WHERE payment_term_id = rec_payment_terms.payment_term_id
1623 AND export_currency_amount <> 0
1624 AND payment_item_type_lookup_code = 'CASH';
1625
1626 EXCEPTION
1627 WHEN no_data_found THEN null;
1628 END;
1629
1630 END IF; -- v_actual_amount is not null
1631 ELSE
1632 v_con_amount := v_new_amount - v_total_amount ;
1633
1634 pn_index_amount_pkg.create_payment_term_record (
1635 p_lease_id => ip_lease_id
1636 ,p_location_id => rec_payment_terms.location_id
1637 ,p_purpose_code => rec_payment_terms.payment_purpose_code
1638 ,p_index_period_id => rec_payment_terms.index_period_id
1639 ,p_term_template_id => NULL
1640 ,p_spread_frequency => rec_payment_terms.frequency_code
1641 ,p_rounding_flag => ip_rounding_flag
1642 ,p_payment_amount => v_con_amount
1643 ,p_normalized => rec_payment_terms.normalize
1644 ,p_start_date => rec_payment_terms.start_date
1645 ,p_index_term_indicator => rec_payment_terms.index_term_indicator
1646 ,p_payment_term_id => rec_payment_terms.payment_term_id
1647 ,p_basis_relationship => ip_relationship
1648 ,p_called_from => 'MAIN'
1649 ,p_calculate_date => l_calculate_date
1650 ,op_payment_term_id => l_payment_term_id
1651 ,op_msg => v_msg
1652 ,p_end_date => NULL);
1653
1654 END IF; -- v_draft_sch = 1
1655
1656 END IF;
1657 END IF; -- ip_context = 'EXP' ...
1658 END LOOP;
1659 EXCEPTION
1660 WHEN OTHERS then
1661 put_log ( 'Unknow Error:' || SQLERRM);
1662
1663 END recalc_ot_payment_terms;
1664
1665
1666 -------------------------------------------------------------------------------------
1667 -- PROCEDURE : Process_main_lease_term_date
1668 -- DESCRIPTION: This procedure will be called every time a new termination
1669 -- create periods for an index rent
1670 --
1671 -- HISTORY:
1672 -- 24-AUG-04 ftanudja o Added logic to check profile option value before
1673 -- extending index rent term. #3756208.
1674 -- 23-NOV-05 pikhar o Passed org_id in pn_mo_cache_utils.get_profile_value
1675 -- 19-JAN-06 piagrawa o Bug#4931780 - Modified signature and did handling to
1676 -- make sure that recalculation done only if norm end
1677 -- date > cut off date or term end date > cut off date
1678 -- 01-NOV-2006 prabhakar o added basis type and reference type records to il_rec
1679 -- and added p_end_date in call to create_payment_term_record
1680 -- for term length option.
1681 -- 09-OCT-06 Hareesha o Added handling to extend RI agreements when lease
1682 -- extended due to MTM/HLD.
1683 -- 03-JAN-07 Hareesha o Bug #5738834 when sys-opt to extend RI on lease expansion is
1684 -- set to Yes and context is 'EXP', then extend only terms
1685 -- ending on old effective lease end date.
1686 -- 09-JAN-07 lbala o Removed code which changes schedule_day to the value
1687 -- returned by procedure get_schedule_date for M28 item#11
1688 -- 07-APR-07 Hareesha o Added handling of expansion of RI terms upon the profile-option
1689 -- PN_RENT_INCREASE_TERM_END_DATE
1690 -- 18-APR-07 sdmahesh o Bug # 5985779. Enhancement for new profile
1691 -- option for lease early termination.
1692 -- Added p_term_end_dt. For DRAFT terms contraction,set the end
1693 -- date as NVL(P_TERM_END_DT,P_NEW_MAIN_LEASE_TERM_DATE)
1694 -- 22-Jan-10 jsundara o Bug#8839033. Modified Submission of CALNDX to send
1695 -- "Recalculate Index" parameter as 'N'
1696 --20-sep-11 krkondur Bug#12406073
1697 --------------------------------------------------------------------------------------
1698 PROCEDURE process_main_lease_term_date (
1699 p_lease_id IN NUMBER
1700 ,p_new_main_lease_term_date IN DATE
1701 ,p_old_main_lease_term_date IN DATE
1702 ,p_lease_context IN VARCHAR2
1703 ,p_msg OUT NOCOPY VARCHAR2
1704 ,p_cutoff_date IN DATE
1705 ,p_term_end_dt IN DATE) AS
1706 /*
1707
1708 A new lease termination date can impact index rent. It can:
1709 - decrease the number of index rent assessment periods
1710 - extend or early terminate the unapproved index rent payment terms:
1711
1712
1713 get all index rents for a given lease
1714
1715 for each index rent lease
1716
1717 if the IR termination date > ML termination date then
1718 - early terminate the index rent
1719 end if;
1720
1721 update of the end date of any index rent payment term that were:
1722 - that are non-approved.
1723 - whose frequency is NOT one time...
1724
1725 end for
1726
1727 */
1728 CURSOR il_rec (
1729 ip_lease_id IN NUMBER) IS
1730 SELECT pil.index_lease_id
1731 ,pil.index_lease_number
1732 ,pil.assessment_date
1733 ,pil.commencement_date
1734 ,pil.termination_date
1735 ,pil.assessment_interval
1736 ,pil.relationship_default
1737 ,pil.basis_percent_default
1738 ,pil.rounding_flag
1739 ,pil.org_id
1740 ,pil.basis_type
1741 ,pil.reference_period
1742 FROM pn_index_leases_all pil
1743 WHERE pil.lease_id = ip_lease_id;
1744
1745 tlinfo il_rec%ROWTYPE;
1746 v_msg VARCHAR2 (1000);
1747 l_profile_value pn_system_setup_options.extend_indexrent_term_flag%TYPE;
1748 v_max_index_period_id NUMBER;
1749 v_max_assessment_date DATE;
1750 v_last_period_assess_end_date DATE;
1751
1752 CURSOR get_last_index_period_cur(p_index_lease_id NUMBER) IS
1753 SELECT max(index_period_id) last_index_period_id, max(assessment_date) last_assessment_date
1754 FROM pn_index_lease_periods_all
1755 WHERE index_lease_id = p_index_lease_id;
1756
1757 CURSOR extendable_index_cur(p_old_ls_end_date IN DATE) IS
1758 SELECT index_lease_id
1759 FROM pn_index_leases_all
1760 WHERE lease_id = p_lease_id
1761 AND termination_date = p_old_ls_end_date;
1762
1763 CURSOR index_periods_cur (p_index_lease_id NUMBER ) IS
1764 SELECT index_period_id
1765 FROM pn_index_lease_periods_all
1766 WHERE index_lease_id = p_index_lease_id
1767 AND assessment_date > p_old_main_lease_term_date;
1768
1769 CURSOR get_term_4_mtm_update(p_index_lease_id NUMBER) IS
1770 SELECT term.payment_term_id payment_term_id,
1771 NVL(term.normalize,'N') normalize
1772 FROM pn_payment_terms_all term,pn_index_lease_periods_all period
1773 WHERE term.index_period_id = period.index_period_id
1774 AND period.index_lease_id = p_index_lease_id
1775 AND term.frequency_code <> pn_index_amount_pkg.c_spread_frequency_one_time
1776 AND term.end_date = p_old_main_lease_term_date;
1777
1778 CURSOR get_term_details ( p_term_id NUMBER) IS
1779 SELECT *
1780 FROM pn_payment_terms_all
1781 WHERE payment_term_id = p_term_id;
1782
1783 CURSOR get_old_ls_end_date IS
1784 SELECT NVL(plh.lease_extension_end_date, plh.lease_termination_date) old_ls_end_date
1785 FROM pn_lease_details_history plh,
1786 pn_lease_details_all pld
1787 WHERE pld.lease_change_id = plh.new_lease_change_id
1788 AND pld.lease_id = p_lease_id;
1789
1790 CURSOR get_lease_comm_date IS
1791 SELECT lease_commencement_date
1792 FROM pn_lease_details_all
1793 WHERE lease_id = p_lease_id;
1794
1795 CURSOR get_lease_num IS
1796 SELECT lease_num
1797 FROM pn_leases_all
1798 WHERE lease_id = p_lease_id;
1799
1800 CURSOR get_appr_terms_to_extend(p_index_lease_id IN NUMBER) IS
1801 SELECT *
1802 FROM pn_payment_terms_all
1803 WHERE payment_term_id IN ( SELECT payment_term_id
1804 FROM pn_payment_terms_all terms, pn_index_lease_periods_all period
1805 WHERE terms.index_period_id = period.index_period_id
1806 AND period.index_lease_id = p_index_lease_id)
1807 AND status ='APPROVED'
1808 AND frequency_code <>'OT'
1809 AND end_date = p_old_main_lease_term_date;
1810
1811 CURSOR get_ri_terms_to_extend(p_index_lease_id IN NUMBER) IS --Bug16243330
1812 SELECT ppt.*
1813 FROM pn_payment_terms_all ppt, pn_index_lease_periods_all pilp
1814 WHERE pilp.index_period_id = ppt.index_period_id
1815 AND pilp.index_lease_id = p_index_lease_id
1816 AND p_lease_context <> 'CON'
1817 AND ppt.frequency_code <>
1818 pn_index_amount_pkg.c_spread_frequency_one_time
1819 AND ppt.end_date > p_cutoff_date
1820 AND ppt.end_date = p_old_main_lease_term_date;
1821
1822
1823 l_term_rec pn_payment_terms_all%ROWTYPE;
1824 l_return_status VARCHAR2(100);
1825 l_schd_date DATE := NULL;
1826 l_schd_day NUMBER := NULL;
1827 l_extended BOOLEAN := FALSE;
1828 INVALID_SCHD_DATE EXCEPTION;
1829 l_old_ls_end_date DATE := NULL;
1830 l_ls_comm_dt DATE ;
1831 l_mths NUMBER;
1832 l_requestId NUMBER := NULL;
1833 l_lease_num PN_LEASES_ALL.lease_num%TYPE;
1834 l_ri_term_rec pn_payment_terms_all%ROWTYPE;
1835 v_end_date DATE; -- added for bug #11671285
1836 l_period_id pn_payment_terms_all.index_period_id%TYPE; /* 9263195 */ -- added for bug #11671285
1837 l_basis_type PN_INDEX_LEASES_ALL.BASIS_TYPE%TYPE; -- added for bug #11671285
1838 l_ref_period PN_INDEX_LEASES_ALL.reference_period%TYPE; -- added for bug #11671285
1839 TYPE rec is record(index_id NUMBER,term_id NUMBER,period_id NUMBER); -- added for bug #11671285
1840 TYPE tab is table of rec INDEX BY BINARY_INTEGER; -- added for bug #11671285
1841 l_term tab; -- added for bug #11671285
1842 l_term_index NUMBER := 1; -- added for bug #11671285
1843 l_status pn_payment_terms_all.status%TYPE; -- added for bug 12406073
1844
1845 BEGIN
1846 put_log('Processing Main Lease Termination Date for Index Rent increases');
1847 put_log('Parameters ');
1848 put_log('---------------------------------');
1849 put_log('lease_id : '||p_lease_id);
1850 put_log('new termination date : '||p_new_main_lease_term_date);
1851 put_log('old termination date : '||p_old_main_lease_term_date);
1852 put_log('Lease context : '||p_lease_context);
1853
1854 FOR lease_comm_dt_rec IN get_lease_comm_date LOOP
1855 l_ls_comm_dt := lease_comm_dt_rec.lease_commencement_date;
1856 END LOOP;
1857
1858 l_mths := ROUND(MONTHS_BETWEEN(p_new_main_lease_term_date,l_ls_comm_dt))+1;
1859
1860 FOR c_rec IN il_rec (p_lease_id)
1861 LOOP
1862 --
1863 -- if the index rent termination date is later than
1864 -- the new main lease termination date
1865 --
1866 put_log(' index lease id '|| c_rec.index_lease_id);
1867 IF trunc(c_rec.termination_date) > trunc(p_new_main_lease_term_date) THEN
1868 --
1869 -- adjust index rent periods with the new termination date
1870 --
1871 put_log(' processing new term date ');
1872 process_new_termination_date (
1873 p_index_lease_id => c_rec.index_lease_id
1874 ,p_new_termination_date => p_new_main_lease_term_date
1875 ,p_ignore_approved_terms => 'Y'
1876 ,p_msg => v_msg);
1877
1878 --
1879 -- update index rent with a new termination date
1880 --
1881 UPDATE pn_index_leases_all
1882 SET termination_date = GREATEST(p_new_main_lease_term_date,commencement_date)
1883 ,last_update_date = SYSDATE
1884 ,last_updated_by = fnd_global.user_id
1885 ,last_update_login = fnd_global.login_id
1886 WHERE index_lease_id = c_rec.index_lease_id;
1887 END IF; -- c_rec.termination_date > p_new_main_lease_term_date
1888
1889 IF p_lease_context IN ('EXP') AND
1890 NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') ='END_LEASE'
1891 THEN
1892 handle_MTM_ACT ( p_lease_id => p_lease_id,
1893 p_extended => l_extended,
1894 x_return_status => l_return_status);
1895
1896 END IF;
1897
1898 l_profile_value
1899 := nvl(pn_mo_cache_utils.get_profile_value('PN_EXTEND_INDEXRENT_TERM', c_rec.org_id),'Y');
1900
1901 IF (l_profile_value = 'Y' AND p_lease_context='EXP') OR
1902 p_lease_context IN ('CON','ROLLOVER','ROLLOVER_RI') THEN
1903
1904 IF trunc(p_new_main_lease_term_date) <> trunc(p_old_main_lease_term_date) THEN
1905 --
1906 -- update one-time payment terms which are not created
1907 -- as part of a backbill, in effect extending the term
1908 -- end date
1909 --
1910
1911 recalc_ot_payment_terms (
1912 ip_lease_id => p_lease_id,
1913 ip_index_lease_id => c_rec.index_lease_id,
1914 ip_old_main_lease_term_date => p_old_main_lease_term_date,
1915 ip_new_main_lease_term_date => p_new_main_lease_term_date,
1916 ip_context => p_lease_context,
1917 ip_rounding_flag => c_rec.rounding_flag,
1918 ip_relationship => c_rec.relationship_default,
1919 ip_cutoff_date => p_cutoff_date);
1920 END IF;
1921
1922 --
1923 -- updating the end date of any non-approved index rent
1924 -- payment terms..
1925 --
1926 IF nvl(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'LEASE_END' OR
1927 (c_rec.basis_type <> 'FIXED' AND c_rec.reference_period <> 'BASE_YEAR') THEN
1928
1929 IF p_lease_context='EXP' AND NOT(l_extended) AND
1930 NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') ='END_LEASE'
1931 THEN
1932
1933 /*--Bug16243330 */
1934 For appr_terms_rec in get_ri_terms_to_extend(c_rec.index_lease_id)
1935 loop
1936 l_ri_term_rec := appr_terms_rec;
1937
1938 UPDATE pn_payment_terms_all
1939 SET end_date = p_new_main_lease_term_date,
1940 last_update_date = SYSDATE,
1941 last_updated_by = fnd_global.user_id,
1942 last_update_login = fnd_global.login_id
1943 WHERE payment_term_id = l_ri_term_rec.payment_term_id;
1944
1945
1946 select status into l_status
1947 from pn_payment_terms_all
1948 where payment_term_id = l_ri_term_rec.payment_term_id;
1949
1950 IF l_status <> 'DRAFT' then
1951
1952 pn_schedules_items.Extend_Payment_Term
1953 (p_payment_term_rec => l_ri_term_rec,
1954 p_new_lea_comm_dt => l_ls_comm_dt,
1955 p_new_lea_term_dt => p_new_main_lease_term_date, /* bug # 11671285*/
1956 p_mths => l_mths,
1957 p_new_start_date => p_old_main_lease_term_date + 1,
1958 p_new_end_date => p_new_main_lease_term_date,
1959 x_return_status => l_return_status,
1960 x_return_message => v_msg);
1961 END IF;
1962
1963 END LOOP;
1964
1965 /*--Bug16243330 */
1966
1967 ELSIF p_lease_context='CON' THEN
1968 UPDATE pn_payment_terms_all
1969 SET end_date = NVL(p_term_end_dt,p_new_main_lease_term_date),
1970 last_update_date = SYSDATE,
1971 last_updated_by = fnd_global.user_id,
1972 last_update_login = fnd_global.login_id
1973 WHERE payment_term_id IN
1974 (SELECT ppt.payment_term_id
1975 FROM pn_payment_terms_all ppt, pn_index_lease_periods_all pilp
1976 WHERE pilp.index_period_id = ppt.index_period_id
1977 AND pilp.index_lease_id = c_rec.index_lease_id
1978 AND (ppt.status = pn_index_amount_pkg.c_payment_term_status_draft AND
1979 p_lease_context = 'CON')
1980 AND ppt.frequency_code <>
1981 pn_index_amount_pkg.c_spread_frequency_one_time);
1982 END IF;
1983
1984 ELSIF nvl(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'PERIOD_END' AND
1985 (c_rec.basis_type = 'FIXED' AND c_rec.reference_period = 'BASE_YEAR') AND
1986 p_lease_context='CON' THEN
1987
1988 FOR get_last_index_period_rec IN get_last_index_period_cur(c_rec.index_lease_id) LOOP
1989 v_max_index_period_id := get_last_index_period_rec.last_index_period_id;
1990 v_max_assessment_date := get_last_index_period_rec.last_assessment_date;
1991 END LOOP;
1992 v_last_period_assess_end_date := add_months(v_max_assessment_date, 12*(c_rec.assessment_interval)) -1;
1993 UPDATE pn_payment_terms_all
1994 SET end_date = least(NVL(p_term_end_dt,p_new_main_lease_term_date), v_last_period_assess_end_date),
1995 last_update_date = SYSDATE,
1996 last_updated_by = fnd_global.user_id,
1997 last_update_login = fnd_global.login_id
1998 WHERE payment_term_id IN
1999 (SELECT ppt.payment_term_id
2000 FROM pn_payment_terms_all ppt, pn_index_lease_periods_all pilp
2001 WHERE pilp.index_period_id = ppt.index_period_id
2002 AND pilp.index_lease_id = c_rec.index_lease_id
2003 AND (ppt.status = pn_index_amount_pkg.c_payment_term_status_draft AND
2004 p_lease_context = 'CON')
2005 AND ppt.frequency_code <>
2006 pn_index_amount_pkg.c_spread_frequency_one_time)
2007 AND index_period_id = v_max_index_period_id;
2008
2009 END IF;
2010 END IF;
2011
2012 IF (p_lease_context IN ('ROLLOVER','ROLLOVER_RI') AND
2013 (nvl(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'LEASE_END' OR
2014 (c_rec.basis_type <> 'FIXED' AND c_rec.reference_period <> 'BASE_YEAR')) AND
2015 NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') ='END_LEASE' ) OR
2016 ( p_lease_context IN ('ROLLOVER_RI') AND
2017 NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') ='END_AGRMNT')
2018 THEN
2019 FOR terms_rec IN get_term_4_mtm_update(c_rec.index_lease_id) LOOP
2020
2021 FOR term_details_rec IN get_term_details(terms_rec.payment_term_id) LOOP
2022 l_term_rec := term_details_rec;
2023 END LOOP;
2024
2025 IF terms_rec.normalize = 'Y' THEN
2026
2027 l_term_rec.start_date := p_old_main_lease_term_date + 1;
2028 l_term_rec.end_date := p_new_main_lease_term_date;
2029 l_term_rec.normalize := 'N';
2030 l_term_rec.parent_term_id := terms_rec.payment_term_id;
2031 l_term_rec.index_norm_flag := 'Y';
2032 l_term_rec.lease_status := 'MTM';
2033 l_term_rec.status := 'DRAFT';
2034
2035 pn_schedules_items.Create_Payment_Term
2036 (p_payment_term_rec => l_term_rec,
2037 p_lease_end_date => p_new_main_lease_term_date,
2038 p_term_start_date => l_term_rec.start_date,
2039 p_term_end_date => l_term_rec.end_date ,
2040 p_new_lea_term_dt => p_new_main_lease_term_date,
2041 p_new_lea_comm_dt => l_ls_comm_dt,
2042 p_mths => l_mths,
2043 x_return_status => l_return_status,
2044 x_return_message => v_msg);
2045
2046
2047 ELSIF terms_rec.normalize = 'N' THEN
2048
2049 l_schd_date := pn_schedules_items.Get_Schedule_Date (
2050 p_lease_id => p_lease_id,
2051 p_day => l_term_rec.schedule_day,
2052 p_start_date => p_old_main_lease_term_date + 1,
2053 p_end_date => p_new_main_lease_term_date,
2054 p_freq => pn_schedules_items.get_frequency(l_term_rec.frequency_code)
2055 );
2056
2057 l_schd_day := TO_NUMBER(TO_CHAR(l_schd_date,'DD'));
2058 IF l_schd_day <> l_term_rec.schedule_day THEN
2059 l_term_rec.start_date := p_old_main_lease_term_date + 1;
2060 l_term_rec.end_date := p_new_main_lease_term_date;
2061 l_term_rec.status := 'DRAFT';
2062
2063 pn_schedules_items.Create_Payment_Term
2064 (p_payment_term_rec => l_term_rec,
2065 p_lease_end_date => p_new_main_lease_term_date,
2066 p_term_start_date => l_term_rec.start_date,
2067 p_term_end_date => l_term_rec.end_date ,
2068 p_new_lea_term_dt => p_new_main_lease_term_date,
2069 p_new_lea_comm_dt => l_ls_comm_dt,
2070 p_mths => l_mths,
2071 x_return_status => l_return_status,
2072 x_return_message => v_msg);
2073 ELSE
2074
2075 l_term_rec.end_date := p_new_main_lease_term_date;
2076 /* added below if condition for bug #11671285 */
2077 IF nvl(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'PERIOD_END' AND
2078 (c_rec.basis_type = 'FIXED' AND c_rec.reference_period = 'BASE_YEAR') THEN
2079
2080 l_term(l_term_index).index_id := c_rec.index_lease_id;
2081 l_term(l_term_index).term_id := l_term_rec.payment_term_id;
2082 l_term(l_term_index).period_id := l_term_rec.index_period_id;
2083 l_term_index := l_term_index + 1;
2084 ELSE
2085 UPDATE pn_payment_terms_all
2086 SET end_date = p_new_main_lease_term_date,
2087 last_update_date = SYSDATE,
2088 last_updated_by = fnd_global.user_id,
2089 last_update_login = fnd_global.login_id
2090 WHERE payment_term_id = terms_rec.payment_term_id;
2091 select status into l_status from pn_payment_terms_all where payment_term_id = terms_rec.payment_term_id;
2092 if l_status <> 'DRAFT' then
2093
2094 pn_schedules_items.Extend_Payment_Term
2095 (p_payment_term_rec => l_term_rec,
2096 p_new_lea_comm_dt => l_ls_comm_dt,
2097 p_new_lea_term_dt => p_new_main_lease_term_date,
2098 p_mths => l_mths,
2099 p_new_start_date => p_old_main_lease_term_date + 1,
2100 p_new_end_date => p_new_main_lease_term_date,
2101 x_return_status => l_return_status,
2102 x_return_message => v_msg);
2103 end if;
2104
2105 END IF; /* end of the added if condion for bug #11671285*/
2106 END IF;
2107 END IF;
2108 END LOOP;
2109
2110 END IF;
2111 END LOOP;
2112
2113 /* Extend RI agreements and create/expand periods when lease
2114 extended due to MTM/HLD */
2115 IF p_lease_context IN ('ROLLOVER_RI','EXP_RI') THEN
2116
2117 FOR rec IN get_old_ls_end_date LOOP
2118 l_old_ls_end_date := rec.old_ls_end_date;
2119 END LOOP;
2120
2121 FOR lease_num_rec IN get_lease_num LOOP
2122 l_lease_num := lease_num_rec.lease_num;
2123 END LOOP;
2124
2125
2126 FOR index_leases_rec IN extendable_index_cur(l_old_ls_end_date)
2127 LOOP
2128 UPDATE pn_index_leases_all
2129 SET termination_date = p_new_main_lease_term_date,
2130 last_update_date = SYSDATE,
2131 last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
2132 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
2133 WHERE index_lease_id = index_leases_rec.index_lease_id;
2134
2135
2136 -- This will create new period for index rent for extended period
2137 pn_index_rent_periods_pkg.process_new_termination_date (
2138 p_index_lease_id => index_leases_rec.index_lease_id
2139 ,p_new_termination_date => p_new_main_lease_term_date
2140 ,p_ignore_approved_terms => 'N'
2141 ,p_msg => v_msg);
2142
2143 /* added below loop for bug #11671285 */
2144 FOR i in 1..l_term.count
2145 LOOP
2146
2147 IF l_term(i).index_id = index_leases_rec.index_lease_id THEN
2148
2149 pn_index_amount_pkg.derive_term_end_date(
2150 ip_index_lease_id => index_leases_rec.index_lease_id
2151 ,ip_index_period_id => l_term(i).period_id
2152 ,ip_main_lease_termination_date => p_new_main_lease_term_date
2153 ,op_term_end_date => v_end_date);
2154
2155
2156 UPDATE pn_payment_terms_all
2157 SET end_date = v_end_date, --p_new_main_lease_term_date,
2158 last_update_date = SYSDATE,
2159 last_updated_by = fnd_global.user_id,
2160 last_update_login = fnd_global.login_id
2161 WHERE payment_term_id = l_term(i).term_id;
2162
2163 select *
2164 into l_ri_term_rec
2165 from pn_payment_terms_all
2166 where payment_term_id = l_term(i).term_id;
2167
2168 select status into l_status from pn_payment_terms_all where payment_term_id = l_term(i).term_id;
2169 if l_status <> 'DRAFT' then
2170
2171 pn_schedules_items.Extend_Payment_Term
2172 (p_payment_term_rec => l_ri_term_rec,
2173 p_new_lea_comm_dt => l_ls_comm_dt,
2174 p_new_lea_term_dt => v_end_date, --p_new_main_lease_term_date,
2175 p_mths => l_mths,
2176 p_new_start_date => p_old_main_lease_term_date + 1,
2177 p_new_end_date => v_end_date,
2178 x_return_status => l_return_status,
2179 x_return_message => v_msg);
2180 end if;
2181 END IF;
2182 END LOOP; /* end of the added loop for bug #11671285 */
2183
2184 IF p_lease_context = 'EXP_RI' AND
2185 NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') ='END_AGRMNT'
2186 THEN
2187 v_end_date := p_new_main_lease_term_date; -- bug #11671285
2188
2189 FOR appr_terms_rec IN get_appr_terms_to_extend( index_leases_rec.index_lease_id) LOOP
2190
2191 l_ri_term_rec := appr_terms_rec;
2192 /* added for bug #11671285*/
2193 select INDEX_PERIOD_ID,basis_type,reference_period
2194 into l_period_id,l_basis_type,l_ref_period
2195 FROM PN_PAYMENT_TERMS_ALL PP,PN_INDEX_LEASES_ALL PI
2196 WHERE PP.PAYMENT_TERM_ID = l_ri_term_rec.payment_term_id
2197 and pp.LEASE_ID = pi.LEASE_ID
2198 and rownum=1;
2199
2200
2201 IF nvl(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'PERIOD_END' AND
2202 (l_basis_type = 'FIXED' AND l_ref_period = 'BASE_YEAR') THEN
2203 pn_index_amount_pkg.derive_term_end_date(
2204 ip_index_lease_id => index_leases_rec.index_lease_id
2205 ,ip_index_period_id => l_period_id
2206 ,ip_main_lease_termination_date => p_new_main_lease_term_date
2207 ,op_term_end_date => v_end_date);
2208
2209 END IF; /* added for bug #11671285*/
2210 UPDATE pn_payment_terms_all
2211 SET end_date = v_end_date, --p_new_main_lease_term_date, /* bug # 11671285*/
2212 last_update_date = SYSDATE,
2213 last_updated_by = fnd_global.user_id,
2214 last_update_login = fnd_global.login_id
2215 WHERE payment_term_id = l_ri_term_rec.payment_term_id;
2216
2217 select status into l_status from pn_payment_terms_all where payment_term_id = l_ri_term_rec.payment_term_id;
2218 if l_status <> 'DRAFT' then
2219
2220 pn_schedules_items.Extend_Payment_Term
2221 (p_payment_term_rec => l_ri_term_rec,
2222 p_new_lea_comm_dt => l_ls_comm_dt,
2223 p_new_lea_term_dt => v_end_date, --p_new_main_lease_term_date, /* bug # 11671285*/
2224 p_mths => l_mths,
2225 p_new_start_date => p_old_main_lease_term_date + 1,
2226 p_new_end_date => v_end_date,
2227 x_return_status => l_return_status,
2228 x_return_message => v_msg);
2229 end if;
2230
2231 END LOOP;
2232 END IF;
2233
2234 END LOOP;
2235
2236 IF p_lease_context = 'EXP_RI' AND
2237 NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') ='END_AGRMNT'
2238 THEN
2239
2240 handle_MTM_ACT ( p_lease_id => p_lease_id,
2241 p_extended => l_extended,
2242 x_return_status => l_return_status);
2243 END IF; /* added for bug #11671285*/
2244 IF (p_lease_context = 'EXP_RI' AND NOT(l_extended)) /* added for bug #11671285*/
2245 OR (p_lease_context = 'ROLLOVER_RI') THEN /* added for bug #11671285*/
2246
2247 l_requestId := fnd_request.submit_request
2248 ('PN',
2249 'PNCALNDX',
2250 NULL,
2251 NULL,
2252 FALSE,
2253 null,null,null,null, null,l_lease_num,null,null,'N',
2254 chr(0), '', '', '',
2255 '', '', '', '', '', '', '', '', '', '',
2256 '', '', '', '', '', '', '', '', '', '',
2257 '', '', '', '', '', '', '', '', '', '',
2258 '', '', '', '', '', '', '', '', '', '',
2259 '', '', '', '', '', '', '', '', '', '',
2260 '', '', '', '', '', '', '', '', '', '',
2261 '', '', '', '', '', '', '', '', '', '',
2262 '', '', '', '', '', '', '', '', '', '',
2263 '', '', '', '', '', '', ''
2264 );
2265
2266 IF (l_requestId = 0 ) THEN
2267 pnp_debug_pkg.log(' ');
2268 pnp_debug_pkg.log('Could not submit Concurrent Request PNCALNDX'
2269 ||' (PN - Calculate Index Rent)');
2270 fnd_message.set_name('PN', 'PN_SCHIT_CONC_FAIL');
2271 pnp_debug_pkg.put_log_msg(fnd_message.get);
2272
2273 ELSE -- Got a request Id
2274 pnp_debug_pkg.log(' ');
2275 pnp_debug_pkg.log('Concurrent Request '||TO_CHAR(l_requestId)
2276 ||' has been submitted for: PN - Calculate Index Rent');
2277 fnd_message.set_name('PN', 'PN_SCHIT_CONC_SUCC');
2278 pnp_debug_pkg.put_log_msg(fnd_message.get);
2279 END IF;
2280 END IF;
2281 END IF;
2282
2283
2284 EXCEPTION
2285 WHEN INVALID_SCHD_DATE THEN
2286 v_msg := FND_API.G_RET_STS_ERROR;
2287
2288 END process_main_lease_term_date;
2289
2290
2291 ------------------------------------------------------------------------
2292 -- PROCEDURE : process_payment_term_amendment
2293 -- DESCRIPTION: This procedure is used by the PNTLEASE form to recalculate index
2294 -- rent amount when a payment term is added from the main lease.
2295 --
2296 -- History:
2297 --
2298 -- 22-jul-2001 psidhu
2299 -- Created.
2300 ------------------------------------------------------------------------
2301
2302 PROCEDURE process_payment_term_amendment (
2303 p_lease_id IN NUMBER
2304 ,p_payment_type_code IN VARCHAR2 --payment_fdr_blk.payment_term_type_code
2305 ,p_payment_start_date IN DATE
2306 ,p_payment_end_date IN DATE
2307 ,p_msg OUT NOCOPY VARCHAR2)
2308 IS
2309 CURSOR cur1 (ip_lease_id IN NUMBER
2310 ,ip_payment_type_code IN VARCHAR2
2311 ,ip_payment_start_date IN DATE
2312 ,ip_payment_end_date IN DATE)
2313 IS
2314 SELECT pil.index_lease_id
2315 ,pilp.index_period_id
2316 ,pilp.basis_start_date
2317 ,pilp.basis_end_date
2318 ,pilp.line_number
2319 ,pil.initial_basis
2320 ,pil.retain_initial_basis_flag
2321 FROM pn_leases_all pl, pn_index_leases_all pil, pn_index_lease_periods_all pilp
2322 WHERE pl.lease_id = pil.lease_id
2323 AND pil.index_lease_id = pilp.index_lease_id
2324 AND pl.lease_id=p_lease_id
2325 AND exists(SELECT 'x'
2326 FROM pn_index_leases_all pilx
2327 WHERE ip_payment_end_date >=(SELECT min(pilpx.basis_start_date)
2328 FROM pn_index_lease_periods_all pilpx
2329 WHERE pilpx.index_lease_id=pilx.index_lease_id)
2330 AND pilx.termination_date >= ip_payment_start_date
2331 AND pilx.index_lease_id=pil.index_lease_id
2332 AND (pilx.increase_on=ip_payment_type_code OR
2333 pilx.gross_flag='Y')
2334 );
2335
2336 v_msg VARCHAR2 (1000);
2337 v_initial_basis_amt NUMBER := NULL;
2338
2339 BEGIN
2340
2341 put_log ( 'p_lease_id '|| p_lease_id);
2342 put_log ('Processing the Following Lease Periods:');
2343
2344 -- get all index rent periods to process
2345
2346 FOR rec1 IN cur1 (p_lease_id
2347 ,p_payment_type_code
2348 ,p_payment_start_date
2349 ,p_payment_end_date)
2350
2351 LOOP
2352 put_log ('Index Lease ID: '|| rec1.index_lease_id||
2353 ' Period ID: '|| rec1.index_period_id );
2354 --
2355 -- call calculate routine to process this index rent period
2356 --
2357
2358 IF rec1.basis_start_date IS NOT NULL AND
2359 rec1.basis_end_date IS NOT NULL AND
2360 ((rec1.initial_basis IS NULL) OR (nvl(rec1.retain_initial_basis_flag,'N') = 'N')) AND
2361 rec1.line_number = 1 THEN
2362
2363 pn_index_amount_pkg.calculate_initial_basis (
2364 p_index_lease_id => rec1.index_lease_id
2365 ,op_basis_amount => v_initial_basis_amt
2366 ,op_msg => v_msg);
2367
2368 UPDATE pn_index_leases_all
2369 SET initial_basis = v_initial_basis_amt
2370 ,last_update_date = SYSDATE
2371 ,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
2372 WHERE index_lease_id = rec1.index_lease_id;
2373 END IF;
2374
2375 pn_index_amount_pkg.calculate (
2376 ip_index_lease_id => rec1.index_lease_id
2377 ,ip_index_lease_period_id => rec1.index_period_id
2378 ,ip_recalculate => 'Y'
2379 ,op_msg => v_msg
2380 );
2381 END LOOP index_lease_period;
2382 END process_payment_term_amendment;
2383
2384
2385 -------------------------------------------------------------------------------
2386 -- PROCEDURE handle_MTM_ACT
2387 -- DESCRIPTION: This procedure handling of RI terms when lease changes from
2388 -- MTM/HLD to ACT and lease is extended.
2389 -- HISTORY:
2390 -- 17-OCT-06 Hareesha o Created.
2391 -- 09-JAN-07 lbala o Removed code to change schedule_date to value returned
2392 -- by get_schedule_date for M28 item# 11
2393 -------------------------------------------------------------------------------
2394 PROCEDURE handle_MTM_ACT (
2395 p_lease_id IN NUMBER
2396 ,p_extended IN OUT NOCOPY BOOLEAN
2397 ,x_return_status OUT NOCOPY VARCHAR2)
2398 IS
2399
2400 l_lease_change_id NUMBER;
2401 l_lease_status_old VARCHAR2(30);
2402 l_lease_status_new VARCHAR2(30);
2403 l_lease_comm_date DATE;
2404 l_lease_term_date DATE;
2405 l_lease_ext_end_date DATE;
2406 l_amd_comm_date DATE;
2407 l_term_rec pn_payment_terms_all%ROWTYPE;
2408 l_term_st_date DATE;
2409 v_msg VARCHAR2(100);
2410 l_schd_date DATE;
2411 l_schd_day NUMBER := NULL;
2412 INVALID_SCHD_DATE EXCEPTION;
2413
2414 CURSOR get_lease_details(p_lease_id NUMBER) IS
2415 SELECT details.lease_change_id lease_change_id,
2416 det_history.lease_status lease_status_old,
2417 lease.lease_status lease_status_new,
2418 details.lease_commencement_date lease_comm_date,
2419 details.lease_termination_date lease_term_date,
2420 det_history.lease_extension_end_date lease_ext_end_date,
2421 changes.change_commencement_date amd_comm_date
2422 FROM pn_lease_details_all details,
2423 pn_lease_details_history det_history,
2424 pn_lease_changes_all changes,
2425 pn_leases_all lease
2426 WHERE details.lease_id = p_lease_id
2427 AND det_history.lease_id = p_lease_id
2428 AND changes.lease_id = p_lease_id
2429 AND lease.lease_id = p_lease_id
2430 AND details.lease_change_id = det_history.new_lease_change_id
2431 AND changes.lease_change_id = details.lease_change_id;
2432
2433 CURSOR get_last_appr_schd_dt (p_lease_id NUMBER) IS
2434 SELECT MAX(pps.schedule_date) lst_schedule_date
2435 FROM pn_payment_schedules_all pps
2436 WHERE pps.payment_status_lookup_code = 'APPROVED'
2437 AND pps.lease_id = p_lease_id;
2438
2439 CURSOR get_mtm_terms( p_lease_id NUMBER,p_term_end_date DATE) IS
2440 SELECT *
2441 FROM pn_payment_terms_all terms
2442 WHERE index_period_id IN ( SELECT index_period_id
2443 FROM pn_index_leases_all
2444 WHERE lease_id = p_lease_id)
2445 AND end_date = p_term_end_date
2446 AND lease_id = p_lease_id
2447 AND frequency_code <> 'OT';
2448
2449 CURSOR get_index_lease_details( p_payment_term_id NUMBER) Is
2450 SELECT basis_type,
2451 reference_period
2452 FROM pn_index_leases_all ileases,
2453 pn_index_lease_periods_all periods,
2454 pn_payment_terms_all terms
2455 WHERE ileases.index_lease_id = periods.index_lease_id
2456 AND periods.index_period_id = terms.index_period_id
2457 AND terms.payment_term_id = p_payment_term_id;
2458
2459 l_basis_type VARCHAR2(30);
2460 l_reference_period VARCHAR2(30);
2461
2462
2463 BEGIN
2464 put_log('handle_MTM_ACT (+) ');
2465
2466 FOR rec IN get_lease_details(p_lease_id) LOOP
2467 l_lease_change_id := rec.lease_change_id;
2468 l_lease_status_old := rec.lease_status_old;
2469 l_lease_status_new := rec.lease_status_new;
2470 l_lease_comm_date := rec.lease_comm_date;
2471 l_lease_term_date := rec.lease_term_date;
2472 l_lease_ext_end_date :=rec.lease_ext_end_date;
2473 l_amd_comm_date := rec.amd_comm_date;
2474 END LOOP;
2475
2476 IF l_lease_status_new = 'ACT' AND ( l_lease_status_old = 'MTM' OR l_lease_status_old ='HLD')
2477 AND l_lease_term_date > l_lease_ext_end_date
2478 THEN
2479
2480 FOR term_details IN get_mtm_terms( p_lease_id,l_lease_ext_end_date) LOOP
2481
2482 l_term_rec := term_details;
2483
2484 FOR ilease_details IN get_index_lease_details(l_term_rec.payment_term_id) LOOP
2485 l_basis_type := ilease_details.basis_type;
2486 l_reference_period := ilease_details.reference_period;
2487 END LOOP;
2488
2489 IF NVL(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'PERIOD_END' AND
2490 l_basis_type = 'FIXED' AND l_reference_period = 'BASE_YEAR'
2491 THEN
2492 EXIT;
2493 END IF;
2494
2495 l_schd_date := pn_schedules_items.get_schedule_date
2496 ( p_lease_id => p_lease_id,
2497 p_day => l_term_rec.schedule_day,
2498 p_start_date => l_lease_ext_end_date + 1,
2499 p_end_date => l_lease_term_date,
2500 p_freq => pn_schedules_items.get_frequency(l_term_rec.frequency_code)
2501 );
2502
2503 l_schd_day := TO_NUMBER(TO_CHAR(l_schd_date,'DD'));
2504
2505 IF NVL(term_details.index_norm_flag,'N') = 'Y' AND term_details.parent_term_id IS NOT NULL
2506 THEN
2507
2508 FOR lst_appr_sched IN get_last_appr_schd_dt ( p_lease_id) LOOP
2509 l_term_rec.norm_start_date := lst_appr_sched.lst_schedule_date;
2510 END LOOP;
2511
2512 IF l_amd_comm_date > l_term_st_date THEN
2513 l_term_rec.norm_start_date := l_amd_comm_date;
2514 END IF;
2515
2516 l_term_rec.normalize := 'Y';
2517 l_term_rec.start_date := l_lease_ext_end_date + 1;
2518 l_term_rec.end_date := l_lease_term_date;
2519 l_term_rec.norm_end_date := l_lease_term_date;
2520 l_term_rec.parent_term_id := NVL(l_term_rec.parent_term_id,
2521 l_term_rec.payment_term_id);
2522 l_term_rec.lease_status := l_lease_status_new;
2523 l_term_rec.index_norm_flag := NULL;
2524 l_term_rec.lease_change_id := l_lease_change_id;
2525 l_term_rec.status := 'DRAFT';
2526
2527 pn_schedules_items.Insert_Payment_Term
2528 ( p_payment_term_rec => l_term_rec,
2529 x_return_status => x_return_status,
2530 x_return_message => v_msg );
2531
2532 p_extended := TRUE;
2533
2534 ELSE
2535
2536 IF l_schd_day <> l_term_rec.schedule_day THEN
2537 l_term_rec.start_date := l_lease_ext_end_date + 1;
2538 l_term_rec.end_date := l_lease_term_date;
2539 l_term_rec.lease_change_id := l_lease_change_id;
2540 l_term_rec.status := 'DRAFT';
2541
2542 pn_schedules_items.Insert_Payment_Term
2543 ( p_payment_term_rec => l_term_rec,
2544 x_return_status => x_return_status,
2545 x_return_message => v_msg );
2546
2547 p_extended := TRUE;
2548
2549 ELSE
2550
2551 UPDATE pn_payment_terms_all
2552 SET end_date = l_lease_term_date,
2553 lease_change_id = l_lease_change_id,
2554 last_update_date = SYSDATE,
2555 last_updated_by = fnd_global.user_id,
2556 last_update_login = fnd_global.login_id
2557 WHERE payment_term_id = l_term_rec.payment_term_id;
2558
2559 p_extended := TRUE;
2560
2561 END IF;
2562
2563 END IF;
2564 END LOOP;
2565
2566 END IF;
2567
2568 put_log('handle_MTM_ACT (-) ');
2569
2570 EXCEPTION
2571 WHEN INVALID_SCHD_DATE THEN
2572 x_return_status := FND_API.G_RET_STS_ERROR;
2573 WHEN OTHERS THEN
2574 x_return_status := SQLERRM;
2575 put_log(' handle_MTM_ACT :'||SQLERRM);
2576 RAISE;
2577 END handle_MTM_ACT;
2578
2579 -------------------------------------------------------------------------------
2580 -- PROCEDURE handle_term_date_change
2581 -- DESCRIPTION: This procedure handles Term-end-dates of RI terms on change of
2582 -- agreement termination date.
2583 -- HISTORY:
2584 -- 03-APR-07 Hareesha o Created.
2585 -- 04-MAY-07 Pikhar o Added handling for Updating Natural Breakpoints
2586 -- 16-sep-11 krkondur o Bug #12755368
2587 -- 24-Oct-11 ppenumar o Bug #10365516- Fwd port for Bug #10153419
2588 -- 15-feb-13 pcehruvu o Bug #16223906- commented index_period_id IS NULL
2589 -------------------------------------------------------------------------------
2590 PROCEDURE handle_term_date_change (
2591 p_index_lease_id IN NUMBER
2592 ,p_old_termination_date IN DATE
2593 ,p_new_termination_date IN DATE
2594 ,p_msg OUT NOCOPY VARCHAR2)
2595 IS
2596
2597 l_payment_term_rec pn_payment_terms_all%ROWTYPE;
2598 x_return_message VARCHAR2(2000);
2599 x_return_status VARCHAR2(2000);
2600 l_lease_comm_date DATE;
2601 l_lease_term_date DATE;
2602 l_mths NUMBER;
2603 l_appr_sched_exists BOOLEAN := FALSE;
2604 l_sched_tbl pn_retro_adjustment_pkg.payment_item_tbl_type;
2605 l_lease_id NUMBER;
2606 l_payment_item_id pn_payment_items_all.payment_item_id%TYPE;
2607 l_lst_cash_sch_dt DATE;
2608 l_var_rent_id NUMBER;
2609
2610 CURSOR get_lease_id IS
2611 SELECT lease_id FROM pn_index_leases_all
2612 WHERE index_lease_id = p_index_lease_id;
2613
2614 CURSOR get_ri_terms_to_modify IS
2615 SELECT *
2616 FROM pn_payment_terms_all
2617 WHERE ((index_period_id IN (SELECT index_period_id
2618 FROM pn_index_lease_periods_all
2619 WHERE index_lease_id = p_index_lease_id))
2620 /* -- OR commented for bug 16223906
2621 -- index_period_id IS NULL */
2622 )
2623 -- AND end_date = p_old_termination_date commented for bug 10053283
2624 AND frequency_code <>'OT'
2625 AND status = 'APPROVED';
2626
2627 CURSOR get_ri_terms_to_exp IS
2628 SELECT *
2629 FROM pn_payment_terms_all
2630 WHERE ((index_period_id IN (SELECT index_period_id
2631 FROM pn_index_lease_periods_all
2632 WHERE index_lease_id = p_index_lease_id))
2633 /* -- OR commented for bug 16223906
2634 -- index_period_id IS NULL */
2635 )
2636 AND frequency_code <>'OT'
2637 AND status = 'APPROVED'
2638 and end_date = p_old_termination_date;
2639
2640
2641 CURSOR get_lease_details IS
2642 SELECT lease_commencement_date,
2643 lease_termination_date,
2644 ROUND(MONTHS_BETWEEN(lease_termination_date,lease_commencement_date))+1 p_mts
2645 FROM pn_lease_details_all
2646 WHERE lease_id IN (SELECT lease_id
2647 FROM pn_index_leases_all
2648 WHERE index_lease_id = p_index_lease_id);
2649
2650 CURSOR approved_sched_exist_cur(p_payment_term_id IN NUMBER)
2651 IS
2652 SELECT payment_schedule_id
2653 FROM pn_payment_schedules_all
2654 WHERE lease_id IN ( SELECT lease_id
2655 FROM pn_payment_terms_all
2656 WHERE payment_term_id = p_payment_term_id)
2657 AND payment_status_lookup_code = 'APPROVED'
2658 AND payment_schedule_id IN (SELECT payment_schedule_id
2659 FROM pn_payment_items_all
2660 WHERE payment_term_id = p_payment_term_id);
2661
2662 CURSOR total_amt_old_term_cur(p_term_id IN NUMBER) IS
2663 SELECT SUM(ppi.actual_amount) AS total_amount
2664 FROM pn_payment_items_all ppi
2665 WHERE ppi.payment_term_id = p_term_id
2666 AND ppi.payment_item_type_lookup_code = 'CASH';
2667
2668 CURSOR draft_schedule_exists_cur (p_sched_date DATE) IS
2669 SELECT pps.payment_schedule_id
2670 FROM pn_payment_schedules_all pps
2671 WHERE pps.schedule_date = p_sched_date
2672 AND pps.lease_id IN (SELECT lease_id FROM pn_index_leases_all
2673 WHERE index_lease_id = p_index_lease_id)
2674 AND pps.payment_status_lookup_code = 'DRAFT';
2675
2676 CURSOR cash_item_exist_cur(p_sched_id NUMBER,p_payment_term_id IN NUMBER) IS
2677 SELECT payment_item_id
2678 FROM pn_payment_items_all
2679 WHERE payment_item_type_lookup_code = 'CASH'
2680 AND payment_schedule_id = p_sched_id
2681 AND payment_term_id = p_payment_term_id;
2682
2683
2684 CURSOR var_cur(p1_lease_id IN NUMBER)
2685 IS
2686 SELECT var_rent_id
2687 FROM pn_var_rents_all
2688 WHERE lease_id = p1_lease_id;
2689
2690 CURSOR terms_cur (p1_lease_id IN NUMBER)
2691 IS
2692 SELECT UPDATE_NBP_FLAG
2693 FROM PN_PAYMENT_TERMS_ALL
2694 WHERE lease_id = p1_lease_id
2695 FOR UPDATE NOWAIT;
2696
2697 CURSOR bkhd_exists_cur
2698 IS
2699 select 'x'
2700 FROM DUAL
2701 where exists (select BKHD_DEFAULT_ID
2702 from pn_var_bkpts_head_all
2703 where period_id IN (select PERIOD_ID
2704 FROM pn_var_periods_all
2705 where VAR_RENT_ID = l_var_rent_id)
2706 AND BKHD_DEFAULT_ID IS NOT NULL);
2707
2708 l_amt_due_to_term NUMBER :=0;
2709 l_amt_due_to_old_term NUMBER;
2710 l_cash_act_amt NUMBER;
2711 l_last_sched_draft VARCHAR2(1);
2712 l_payment_schedule_id pn_payment_items_all.payment_schedule_id%TYPE;
2713 l_lease_change_id NUMBER;
2714 l_errbuf VARCHAR2(80);
2715 l_retcode VARCHAR2(80);
2716 l_update_nbp_flag VARCHAR2(1);
2717 l_dummy VARCHAR2(1);
2718
2719 BEGIN
2720
2721 put_log('handle_term_date_change (+) ');
2722
2723 IF NVL(fnd_profile.value('PN_RENT_INCREASE_TERM_END_DATE'),'END_LEASE') = 'END_AGRMNT' AND
2724 p_old_termination_date <> p_new_termination_date
2725 THEN
2726
2727 /* RI agreement expansion */
2728
2729 FOR lease_id_rec IN get_lease_id LOOP
2730 l_lease_id := lease_id_rec.lease_id;
2731 END LOOP;
2732
2733 IF p_old_termination_date < p_new_termination_date THEN
2734 p_msg := 'RI agreement expansion';
2735
2736 IF nvl(fnd_profile.value('PN_IR_TERM_END_DATE'),'LEASE_END') = 'LEASE_END' THEN
2737 FOR ri_terms_rec IN get_ri_terms_to_exp LOOP
2738 l_payment_term_rec := ri_terms_rec;
2739
2740 UPDATE pn_payment_terms_all
2741 SET end_date = p_new_termination_date
2742 WHERE payment_term_id = l_payment_term_rec.payment_term_id;
2743
2744 UPDATE pn_payment_terms_all
2745 SET UPDATE_NBP_FLAG = 'Y'
2746 WHERE payment_term_id = l_payment_term_rec.payment_term_id
2747 AND INCLUDE_IN_VAR_RENT IN ('BASETERM', 'INCLUDE_RI');
2748
2749 FOR rec IN get_lease_details LOOP
2750 l_lease_comm_date := rec.lease_commencement_date;
2751 l_lease_term_date := rec.lease_termination_date;
2752 l_mths := rec.p_mts;
2753 END LOOP;
2754
2755 pn_schedules_items.extend_payment_term(
2756 p_payment_term_rec => l_payment_term_rec,
2757 p_new_lea_comm_dt => l_lease_comm_date,
2758 p_new_lea_term_dt => l_lease_term_date,
2759 p_mths => l_mths,
2760 p_new_start_date => p_old_termination_date + 1,
2761 p_new_end_date => p_new_termination_date,
2762 x_return_status => x_return_status,
2763 x_return_message => x_return_message);
2764
2765 END LOOP;
2766 END IF;
2767
2768
2769 /* RI agreement contraction */
2770 ELSIF p_old_termination_date > p_new_termination_date THEN
2771 p_msg := 'RI agreement contraction';
2772
2773 FOR lease_id_rec IN get_lease_id LOOP
2774 l_lease_id := lease_id_rec.lease_id;
2775 END LOOP;
2776
2777 FOR ri_terms_rec IN get_ri_terms_to_modify LOOP
2778
2779 l_payment_term_rec := ri_terms_rec;
2780
2781 IF l_payment_term_rec.start_date > p_new_termination_date AND
2782 l_payment_term_rec.start_date <= p_old_termination_date
2783 THEN
2784
2785 FOR rec IN approved_sched_exist_cur(l_payment_term_rec.payment_term_id) LOOP
2786 l_appr_sched_exists := TRUE;
2787 END LOOP;
2788
2789 IF l_appr_sched_exists THEN
2790
2791 UPDATE pn_payment_terms_all
2792 SET end_date = p_new_termination_date,
2793 start_date = p_new_termination_date,
2794 actual_amount = 0
2795 WHERE payment_term_id = l_payment_term_rec.payment_term_id;
2796
2797 UPDATE pn_payment_terms_all
2798 SET UPDATE_NBP_FLAG = 'Y'
2799 WHERE payment_term_id = l_payment_term_rec.payment_term_id
2800 AND INCLUDE_IN_VAR_RENT IN ('BASETERM', 'INCLUDE_RI');
2801
2802 DELETE pn_payment_items_all
2803 WHERE payment_schedule_id IN
2804 (SELECT payment_schedule_id
2805 FROM pn_payment_schedules_all
2806 WHERE lease_id IN (SELECT lease_id FROM pn_payment_terms_all
2807 WHERE payment_term_id = l_payment_term_rec.payment_term_id)
2808 AND schedule_date > p_new_termination_date
2809 AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
2810 AND payment_term_id = l_payment_term_rec.payment_term_id;
2811
2812 l_sched_tbl.DELETE;
2813
2814 pn_retro_adjustment_pkg.create_virtual_schedules
2815 (p_start_date => l_payment_term_rec.start_date,
2816 p_end_date => p_new_termination_date,
2817 p_sch_day => l_payment_term_rec.schedule_day,
2818 p_amount => nvl(l_payment_term_rec.actual_amount,l_payment_term_rec.estimated_amount),
2819 p_term_freq => l_payment_term_rec.frequency_code,
2820 p_payment_term_id => l_payment_term_rec.payment_term_id,
2821 x_sched_tbl => l_sched_tbl);
2822
2823 l_amt_due_to_term := 0;
2824
2825 IF l_sched_tbl.COUNT > 0 THEN
2826 FOR i IN 0..l_sched_tbl.COUNT - 1 LOOP
2827 l_amt_due_to_term := l_amt_due_to_term + l_sched_tbl(i).amount ;
2828 END LOOP;
2829 END IF;
2830
2831 l_amt_due_to_old_term := 0;
2832
2833 FOR rec IN total_amt_old_term_cur(l_payment_term_rec.payment_term_id) LOOP
2834 l_amt_due_to_old_term := rec.total_amount;
2835 END LOOP;
2836
2837 l_cash_act_amt := l_amt_due_to_term - NVL(l_amt_due_to_old_term, 0);
2838
2839 IF l_cash_act_amt <> 0 THEN
2840
2841 l_last_sched_draft := 'N';
2842
2843 FOR rec IN draft_schedule_exists_cur(l_sched_tbl(l_sched_tbl.LAST).schedule_date) LOOP
2844 l_last_sched_draft := 'Y';
2845 l_payment_schedule_id := rec.payment_schedule_id;
2846 l_lst_cash_sch_dt := l_sched_tbl(l_sched_tbl.LAST).schedule_date;
2847 END LOOP;
2848
2849 IF l_last_sched_draft = 'N' THEN
2850
2851 l_lst_cash_sch_dt
2852 := TO_DATE(TO_CHAR(l_payment_term_rec.schedule_day)
2853 ||'/'||TO_CHAR(l_payment_term_rec.end_date,'MM/YYYY')
2854 ,'DD/MM/YYYY');
2855 l_lease_change_id := pn_schedules_items.Get_Lease_Change_Id(l_lease_id);
2856
2857 pn_retro_adjustment_pkg.find_schedule( l_lease_id
2858 ,l_lease_change_id
2859 ,l_payment_term_rec.payment_term_id
2860 ,l_lst_cash_sch_dt
2861 ,l_payment_schedule_id);
2862 END IF;
2863
2864 l_payment_item_id := NULL;
2865 FOR rec IN cash_item_exist_cur(l_payment_schedule_id, l_payment_term_rec.payment_term_id) LOOP
2866 l_payment_item_id := rec.payment_item_id;
2867 END LOOP;
2868
2869 IF l_payment_item_id IS NOT NULL THEN
2870 pn_schedules_items.update_cash_item
2871 (p_item_id => l_payment_item_id
2872 ,p_term_id => l_payment_term_rec.payment_term_id
2873 ,p_sched_id => l_payment_schedule_id
2874 ,p_act_amt => l_cash_act_amt);
2875
2876 ELSE
2877 pn_schedules_items.create_cash_items
2878 (p_est_amt => l_cash_act_amt,
2879 p_act_amt => l_cash_act_amt,
2880 p_sch_dt => l_lst_cash_sch_dt,
2881 p_sch_id => l_payment_schedule_id,
2882 p_term_id => l_payment_term_rec.payment_term_id,
2883 p_vendor_id => l_payment_term_rec.vendor_id,
2884 p_cust_id => l_payment_term_rec.customer_id,
2885 p_vendor_site_id => l_payment_term_rec.vendor_site_id,
2886 p_cust_site_use_id => l_payment_term_rec.customer_site_use_id,
2887 p_cust_ship_site_id => l_payment_term_rec.cust_ship_site_id,
2888 p_sob_id => l_payment_term_rec.set_of_books_id,
2889 p_curr_code => l_payment_term_rec.currency_code,
2890 p_rate => l_payment_term_rec.rate);
2891
2892 END IF;
2893 END IF;
2894
2895 ELSE
2896
2897 DELETE pn_payment_items_all
2898 WHERE payment_term_id = l_payment_term_rec.payment_term_id;
2899
2900 DELETE pn_distributions_all
2901 WHERE payment_term_id = l_payment_term_rec.payment_term_id;
2902
2903 DELETE pn_payment_terms_all
2904 WHERE payment_term_id = l_payment_term_rec.payment_term_id;
2905
2906 END IF;
2907
2908 ELSIF l_payment_term_rec.start_date <= p_new_termination_date AND
2909 l_payment_term_rec.end_date >= p_new_termination_date
2910 THEN
2911
2912 UPDATE pn_payment_terms_all
2913 SET end_date = p_new_termination_date
2914 WHERE payment_term_id = l_payment_term_rec.payment_term_id;
2915
2916 DELETE pn_payment_items_all
2917 WHERE payment_schedule_id IN
2918 (SELECT payment_schedule_id
2919 FROM pn_payment_schedules_all
2920 WHERE lease_id IN (SELECT lease_id FROM pn_payment_terms_all
2921 WHERE payment_term_id = l_payment_term_rec.payment_term_id)
2922 AND schedule_date > p_new_termination_date
2923 AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
2924 AND payment_term_id = l_payment_term_rec.payment_term_id;
2925
2926 l_sched_tbl.DELETE;
2927
2928 pn_retro_adjustment_pkg.create_virtual_schedules
2929 (p_start_date => l_payment_term_rec.start_date,
2930 p_end_date => p_new_termination_date,
2931 p_sch_day => l_payment_term_rec.schedule_day,
2932 p_amount => nvl(l_payment_term_rec.actual_amount,l_payment_term_rec.estimated_amount),
2933 p_term_freq => l_payment_term_rec.frequency_code,
2934 p_payment_term_id => l_payment_term_rec.payment_term_id,
2935 x_sched_tbl => l_sched_tbl);
2936
2937 l_amt_due_to_term := 0;
2938
2939 IF l_sched_tbl.COUNT > 0 THEN
2940 FOR i IN 0..l_sched_tbl.COUNT - 1 LOOP
2941 l_amt_due_to_term := l_amt_due_to_term + l_sched_tbl(i).amount ;
2942 END LOOP;
2943 END IF;
2944
2945 l_amt_due_to_old_term := 0;
2946
2947 FOR rec IN total_amt_old_term_cur(l_payment_term_rec.payment_term_id) LOOP
2948 l_amt_due_to_old_term := rec.total_amount;
2949 END LOOP;
2950
2951 l_cash_act_amt := l_amt_due_to_term - NVL(l_amt_due_to_old_term, 0);
2952
2953 IF l_cash_act_amt <> 0 THEN
2954
2955 l_last_sched_draft := 'N';
2956
2957 FOR rec IN draft_schedule_exists_cur(l_sched_tbl(l_sched_tbl.LAST).schedule_date) LOOP
2958 l_last_sched_draft := 'Y';
2959 l_payment_schedule_id := rec.payment_schedule_id;
2960 l_lst_cash_sch_dt := l_sched_tbl(l_sched_tbl.LAST).schedule_date;
2961 END LOOP;
2962
2963 IF l_last_sched_draft = 'N' THEN
2964
2965 l_lst_cash_sch_dt
2966 := TO_DATE(TO_CHAR(l_payment_term_rec.schedule_day)
2967 ||'/'||TO_CHAR(p_new_termination_date,'MM/YYYY')
2968 ,'DD/MM/YYYY');
2969
2970 l_lease_change_id := pn_schedules_items.Get_Lease_Change_Id(l_lease_id);
2971
2972 pn_retro_adjustment_pkg.find_schedule( l_lease_id
2973 ,l_lease_change_id
2974 ,l_payment_term_rec.payment_term_id
2975 ,l_lst_cash_sch_dt
2976 ,l_payment_schedule_id);
2977 END IF;
2978
2979 l_payment_item_id := NULL;
2980 FOR rec IN cash_item_exist_cur(l_payment_schedule_id, l_payment_term_rec.payment_term_id) LOOP
2981 l_payment_item_id := rec.payment_item_id;
2982 END LOOP;
2983
2984 IF l_payment_item_id IS NOT NULL THEN
2985 pn_schedules_items.update_cash_item
2986 ( p_item_id => l_payment_item_id
2987 ,p_term_id => l_payment_term_rec.payment_term_id
2988 ,p_sched_id => l_payment_schedule_id
2989 ,p_act_amt => l_cash_act_amt);
2990
2991 ELSE
2992 pn_schedules_items.create_cash_items
2993 (p_est_amt => l_cash_act_amt,
2994 p_act_amt => l_cash_act_amt,
2995 p_sch_dt => l_lst_cash_sch_dt,
2996 p_sch_id => l_payment_schedule_id,
2997 p_term_id => l_payment_term_rec.payment_term_id,
2998 p_vendor_id => l_payment_term_rec.vendor_id,
2999 p_cust_id => l_payment_term_rec.customer_id,
3000 p_vendor_site_id => l_payment_term_rec.vendor_site_id,
3001 p_cust_site_use_id => l_payment_term_rec.customer_site_use_id,
3002 p_cust_ship_site_id => l_payment_term_rec.cust_ship_site_id,
3003 p_sob_id => l_payment_term_rec.set_of_books_id,
3004 p_curr_code => l_payment_term_rec.currency_code,
3005 p_rate => l_payment_term_rec.rate);
3006
3007 END IF;
3008 END IF;
3009
3010 END IF;
3011 END LOOP;
3012
3013 pn_retro_adjustment_pkg.cleanup_schedules(l_lease_id);
3014
3015 END IF;
3016
3017 --Recalculate Natural Breakpoint if any changes in Lease Payment Terms
3018
3019 l_update_nbp_flag := NULL;
3020 FOR terms_rec IN terms_cur(p1_lease_id => l_lease_id)
3021 LOOP
3022 IF terms_rec.UPDATE_NBP_FLAG = 'Y' THEN
3023 l_update_nbp_flag := 'Y';
3024 EXIT;
3025 END IF;
3026 END LOOP;
3027
3028 IF l_update_nbp_flag = 'Y' THEN
3029 FOR var_rec in var_cur(p1_lease_id => l_lease_id)
3030 LOOP
3031
3032 l_var_rent_id := var_rec.var_rent_id;
3033
3034 OPEN bkhd_exists_cur;
3035 FETCH bkhd_exists_cur INTO l_dummy;
3036 CLOSE bkhd_exists_cur;
3037
3038 pn_var_natural_bp_pkg.build_bkpt_details_main(errbuf => l_errbuf,
3039 retcode => l_retcode,
3040 p_var_rent_id => var_rec.var_rent_id);
3041
3042 IF l_dummy IS NOT NULL THEN
3043 pn_var_defaults_pkg.create_setup_data (x_var_rent_id => var_rec.var_rent_id);
3044 END IF;
3045
3046 pnp_debug_pkg.log('Updated Natural Breakpoints for VR - '||var_rec.var_rent_id);
3047
3048
3049 END LOOP;
3050
3051 UPDATE pn_payment_terms_all
3052 SET UPDATE_NBP_FLAG = NULL
3053 WHERE lease_id = l_lease_id;
3054
3055 END IF;
3056
3057 -- Finished Recalculating Natural Breakpoint if any changes in Lease Payment Terms
3058
3059 END IF;
3060
3061 put_log('handle_term_date_change (-) ');
3062
3063 EXCEPTION
3064 WHEN OTHERS THEN NULL;
3065
3066 END handle_term_date_change;
3067
3068
3069 END pn_index_rent_periods_pkg;