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