[Home] [Help]
PACKAGE BODY: APPS.PN_MTM_ROLLFORWARD_PKG
Source
1 PACKAGE BODY PN_MTM_ROLLFORWARD_PKG AS
2 /* $Header: PNRLFWDB.pls 120.4 2011/08/23 10:48:27 acprakas ship $ */
3 g_reqId_sched NUMBER := NULL;
4 -------------------------------------------------------------------------------
5 -- PROCEDURE : ROLLFORWARD_LEASES
6 -- DESCRIPTION: Called from Concurrent program Month to Month Rollforward Process
7 --
8 -- 19-OCT-06 Hareesha o Created
9 -------------------------------------------------------------------------------
10
11 PROCEDURE rollforward_leases( errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY VARCHAR2,
13 p_lease_no_low VARCHAR2,
14 p_lease_no_high VARCHAR2,
15 p_lease_ext_end_dt VARCHAR2,
16 p_lease_option VARCHAR2)
17 IS
18
19 INCORECT_LEASE_EXCEPTION EXCEPTION;
20 INCORRECT_EXTENSION_END_DATE EXCEPTION;
21 INCORRECT_LEASE_OPTION EXCEPTION;
22 INVALID_LEASE_RECORD EXCEPTION;
23
24 l_lease_ext_end_dt DATE;
25 l_errbuf VARCHAR2(100);
26 l_retcode VARCHAR2(100);
27 l_extend_ri VARCHAR2(1) := NULL;
28 l_requestId NUMBER := NULL;
29
30 l_total NUMBER := 0;
31 l_success NUMBER := 0;
32 l_fail NUMBER := 0;
33
34 /* variables for dbms_sql */
35 l_cursor INTEGER;
36 l_rows INTEGER;
37 l_count INTEGER;
38 l_where_clause VARCHAR2(2000) := NULL;
39 l_lease_no_low VARCHAR2(30);
40 l_lease_no_high VARCHAR2(30);
41 Q_lease_details VARCHAR2(1000);
42 l_lease_id NUMBER;
43 l_lease_status VARCHAR2(30);
44 l_status VARCHAR2(1);
45 l_old_ext_dt DATE;
46 l_lease_change_id NUMBER;
47
48 BEGIN
49
50 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_leases +Start+ (+)');
51 pnp_debug_pkg.log('Lease Number Low : '||p_lease_no_low);
52 pnp_debug_pkg.log('Lease Number Low : '||p_lease_no_high);
53 pnp_debug_pkg.log('Lease extension end date : '||p_lease_ext_end_dt);
54 pnp_debug_pkg.log('Lease Option : '||p_lease_option);
55
56 l_lease_ext_end_dt := fnd_date.canonical_to_date(p_lease_ext_end_dt);
57
58 IF l_lease_ext_end_dt IS NULL THEN
59 RAISE INCORRECT_EXTENSION_END_DATE;
60 END IF;
61
62 IF p_lease_option IS NULL OR
63 p_lease_option NOT IN ('A','L')
64 THEN
65 RAISE INCORRECT_LEASE_OPTION;
66 END IF;
67
68 l_cursor := dbms_sql.open_cursor;
69
70 IF p_lease_no_low IS NOT NULL AND p_lease_no_high IS NOT NULL THEN
71 l_lease_no_low := p_lease_no_low;
72 l_lease_no_high := p_lease_no_high;
73 l_where_clause := l_where_clause ||' AND lease_num BETWEEN :l_lease_no_low AND :l_lease_no_high ';
74
75 ELSIF p_lease_no_low IS NOT NULL AND p_lease_no_high IS NULL THEN
76 l_lease_no_low := p_lease_no_low;
77 l_where_clause := l_where_clause ||' AND lease_num >= :l_lease_no_low ';
78
79 ELSIF p_lease_no_high IS NOT NULL AND p_lease_no_low IS NULL THEN
80 l_lease_no_high := p_lease_no_high;
81 l_where_clause := l_where_clause ||' AND lease_num <= :l_lease_no_high ';
82
83 ELSE
84 l_where_clause := NULL;
85 END IF;
86
87 Q_lease_details :=
88 ' SELECT
89 lease.lease_id lease_id,
90 lease.lease_status lease_status,
91 lease.status status,
92 det.lease_extension_end_date lease_extension_end_date,
93 det.lease_change_id lease_change_id
94 FROM pn_leases_all lease,
95 pn_lease_details_all det
96 WHERE lease.lease_id = det.lease_id ';
97
98 Q_lease_details := Q_lease_details || l_where_clause;
99
100 /*pnp_debug_pkg.log(' Q_lease_details :'||Q_lease_details);*/
101
102 dbms_sql.parse(l_cursor, Q_lease_details, dbms_sql.native);
103
104 IF p_lease_no_low IS NOT NULL AND p_lease_no_high IS NOT NULL THEN
105
106 dbms_sql.bind_variable
107 (l_cursor,'l_lease_no_low',l_lease_no_low );
108 dbms_sql.bind_variable
109 (l_cursor,'l_lease_no_high',l_lease_no_high );
110
111 ELSIF p_lease_no_low IS NOT NULL AND p_lease_no_high IS NULL THEN
112 dbms_sql.bind_variable
113 (l_cursor,'l_lease_no_low',l_lease_no_low );
114
115 ELSIF p_lease_no_high IS NOT NULL AND p_lease_no_low IS NULL THEN
116 dbms_sql.bind_variable
117 (l_cursor,'l_lease_no_high',l_lease_no_high );
118 END IF;
119
120 dbms_sql.define_column (l_cursor, 1,l_lease_id);
121 dbms_sql.define_column (l_cursor, 2,l_lease_status,30);
122 dbms_sql.define_column (l_cursor, 3,l_status,1);
123 dbms_sql.define_column (l_cursor, 4,l_old_ext_dt);
124 dbms_sql.define_column (l_cursor, 5,l_lease_change_id);
125
126 l_rows := dbms_sql.execute(l_cursor);
127
128 LOOP
129
130 l_count := dbms_sql.fetch_rows( l_cursor );
131 EXIT WHEN l_count <> 1;
132
133 dbms_sql.column_value (l_cursor, 1,l_lease_id);
134 dbms_sql.column_value (l_cursor, 2,l_lease_status);
135 dbms_sql.column_value (l_cursor, 3,l_status);
136 dbms_sql.column_value (l_cursor, 4,l_old_ext_dt);
137 dbms_sql.column_value (l_cursor, 5,l_lease_change_id);
138
139 BEGIN
140
141 l_total := l_total + 1;
142
143 IF l_lease_status NOT IN ('MTM','HLD') THEN
144 RAISE INVALID_LEASE_RECORD;
145
146 ELSIF ( l_old_ext_dt > l_lease_ext_end_dt OR
147 l_old_ext_dt IS NULL) THEN
148 RAISE INVALID_LEASE_RECORD;
149
150 ELSIF l_status <> 'F' THEN
151 RAISE INVALID_LEASE_RECORD;
152
153 ELSIF l_lease_status IN ('MTM','HLD') AND
154 l_old_ext_dt < l_lease_ext_end_dt AND
155 l_status = 'F'
156 THEN
157
158 IF p_lease_option = 'A' THEN
159 l_extend_ri := 'Y';
160 END IF;
161
162 create_amendment(
163 p_lease_id => l_lease_id
164 ,p_lease_ext_end_dt => l_lease_ext_end_dt
165 ,p_leaseChangeId => l_lease_change_id);
166
167 rollforward_tenancies(
168 p_lease_id => l_lease_id
169 ,p_lease_ext_end_dt => l_lease_ext_end_dt
170 ,p_old_ext_end_dt => l_old_ext_dt);
171
172 rollforward_terms (
173 p_lease_id => l_lease_id
174 ,p_lease_ext_end_dt => l_lease_ext_end_dt
175 ,p_extend_ri => l_extend_ri );
176
177 IF p_lease_option = 'A' THEN
178 rollforward_var_rent (
179 p_lease_id => l_lease_id
180 ,p_lease_ext_end_dt => l_lease_ext_end_dt
181 ,p_old_ext_end_dt => l_old_ext_dt
182 ,p_lease_change_id => l_lease_change_id );
183 END IF;
184
185 print_output (
186 p_lease_id => l_lease_id);
187
188 END IF;
189
190 EXCEPTION
191 WHEN INVALID_LEASE_RECORD THEN
192 pnp_debug_pkg.log(' invalid lease record ');
193 l_fail := l_fail + 1;
194 END;
195 END LOOP;
196
197 IF l_total <> 0 THEN
198 l_success := l_total - l_fail;
199
200 fnd_message.set_name('PN', 'PN_CAFM_SPACE_SUCCESS');
201 fnd_message.set_token('SUCCESS', l_success);
202 pnp_debug_pkg.put_log_msg(fnd_message.get);
203
204 fnd_message.set_name('PN', 'PN_CAFM_SPACE_FAILURE');
205 fnd_message.set_token('FAILURE', l_fail);
206 pnp_debug_pkg.put_log_msg(fnd_message.get);
207
208 fnd_message.set_name('PN', 'PN_CAFM_SPACE_TOTAL');
209 fnd_message.set_token('TOTAL', l_total);
210 pnp_debug_pkg.put_log_msg(fnd_message.get);
211
212 END IF;
213
214 IF dbms_sql.is_open (l_cursor) THEN
215 dbms_sql.close_cursor (l_cursor);
216 END IF;
217
218 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_leases +End+ (-)');
219 EXCEPTION
220 WHEN INCORECT_LEASE_EXCEPTION THEN
221 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
222 errbuf := fnd_message.get;
223 pnp_debug_pkg.put_log_msg (errbuf);
224 retcode := 2;
225
226 WHEN INCORRECT_EXTENSION_END_DATE THEN
227 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
228 errbuf := fnd_message.get;
229 pnp_debug_pkg.put_log_msg (errbuf);
230 retcode := 2;
231
232 WHEN INCORRECT_LEASE_OPTION THEN
233 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
234 errbuf := fnd_message.get;
235 pnp_debug_pkg.put_log_msg (errbuf);
236 retcode := 2;
237
238 WHEN INVALID_LEASE_RECORD THEN
239 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
240 errbuf := fnd_message.get;
241 pnp_debug_pkg.put_log_msg (errbuf);
242 retcode := 2;
243
244 WHEN OTHERS THEN
245 Errbuf := SQLERRM;
246 Retcode := 2;
247 ROLLBACK;
248
249 END rollforward_leases;
250
251
252 -------------------------------------------------------------------------------
253 -- PROCEDURE : CREATE_AMENDMENT
254 -- DESCRIPTION: Procedure to create amendment for rollforward.
255 --
256 -- 19-OCT-06 Hareesha o Created
257 -------------------------------------------------------------------------------
258 PROCEDURE CREATE_AMENDMENT( p_lease_id NUMBER,
259 p_lease_ext_end_dt DATE,
260 p_leaseChangeId OUT NOCOPY NUMBER)
261 IS
262
263 CURSOR get_lease_details (p_lease_id NUMBER) IS
264 SELECT lease_num,
265 responsible_user,
266 GREATEST(lease_termination_date,lease_extension_end_date) old_ext_dt,
267 SYSDATE,
268 lease.org_id org_id,
269 lease_detail_id,
270 expense_account_id,
271 accrual_account_id,
272 receivable_account_id,
273 term_template_id,
274 grouping_rule_id,
275 lease_commencement_date,
276 lease_termination_date,
277 lease_execution_date,
278 attribute_category,
279 attribute1,
280 attribute2,
281 attribute3,
282 attribute4,
283 attribute5,
284 attribute6,
285 attribute7,
286 attribute8,
287 attribute9,
288 attribute10,
289 attribute11,
290 attribute12,
291 attribute13,
292 attribute14,
293 attribute15
294 FROM pn_leases_all lease,pn_lease_details_all det
295 WHERE lease.lease_id = p_lease_id
296 AND det.lease_id = lease.lease_id;
297
298 l_rowid VARCHAR2(18) := NULL;
299 l_leaseChangeId NUMBER := NULL;
300 l_leaseChangeNumber NUMBER := NULL;
301 l_leaseChangeName VARCHAR2(50) ;
302 l_lease_num VARCHAR2(30);
303 l_user_id NUMBER := NVL(fnd_profile.value ('USER_ID'), 0);
304 l_last_updated_by NUMBER := NVL(fnd_profile.value ('USER_ID'), 0);
305 l_last_update_login NUMBER := NVL(fnd_profile.value ('LOGIN_ID'),0);
306 l_changeCommencementDate DATE;
307 l_changeTerminationdate DATE;
308 l_changeExecutionDate DATE;
309 l_responsibleUser NUMBER;
310 l_creationDate DATE;
311 l_org_id NUMBER;
312 l_lease_detail_id NUMBER;
313 l_expense_account_id NUMBER;
314 l_accrual_account_id NUMBER(15);
315 l_receivable_account_id NUMBER(15);
316 l_term_template_id NUMBER(15);
317 l_grouping_rule_id NUMBER(15);
318 l_leaseCommencementDate DATE;
319 l_leaseTerminationDate DATE;
320 l_leaseExecutionDate DATE;
321 l_attribute_category VARCHAR2(250);
322 l_attribute1 VARCHAR2(250);
323 l_attribute2 VARCHAR2(250);
324 l_attribute3 VARCHAR2(250);
325 l_attribute4 VARCHAR2(250);
326 l_attribute5 VARCHAR2(250);
327 l_attribute6 VARCHAR2(250);
328 l_attribute7 VARCHAR2(250);
329 l_attribute8 VARCHAR2(250);
330 l_attribute9 VARCHAR2(250);
331 l_attribute10 VARCHAR2(250);
332 l_attribute11 VARCHAR2(250);
333 l_attribute12 VARCHAR2(250);
334 l_attribute13 VARCHAR2(250);
335 l_attribute14 VARCHAR2(250);
336 l_attribute15 VARCHAR2(250);
337
338 BEGIN
339
340 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.create_amendment +Start+ (+)');
341
342 FOR rec IN get_lease_details( p_lease_id ) LOOP
343 l_lease_num := rec.lease_num;
344 l_responsibleUser := rec.responsible_user;
345 l_changeCommencementDate := rec.old_ext_dt + 1;
346 l_creationDate := rec.sysdate;
347 l_org_id := rec.org_id;
348 l_lease_detail_id := rec.lease_detail_id;
349 l_expense_account_id := rec.expense_account_id;
350 l_accrual_account_id := rec.accrual_account_id;
351 l_receivable_account_id := rec.receivable_account_id;
352 l_term_template_id := rec.term_template_id;
353 l_grouping_rule_id := rec.grouping_rule_id;
354 l_leaseCommencementDate := rec.lease_commencement_date;
355 l_leaseTerminationDate := rec.lease_termination_date;
356 l_leaseExecutionDate := rec.lease_execution_date;
357 l_attribute_category := rec.attribute_category ;
358 l_attribute1 := rec.attribute1;
359 l_attribute2 := rec.attribute2;
360 l_attribute3 := rec.attribute3;
361 l_attribute4 := rec.attribute4;
362 l_attribute5 := rec.attribute5;
363 l_attribute6 := rec.attribute6;
364 l_attribute7 := rec.attribute7;
365 l_attribute8 := rec.attribute8;
366 l_attribute9 := rec.attribute9;
367 l_attribute10 := rec.attribute10;
368 l_attribute11 := rec.attribute11;
369 l_attribute12 := rec.attribute12;
370 l_attribute13 := rec.attribute13;
371 l_attribute14 := rec.attribute14;
372 l_attribute15 := rec.attribute15;
373
374 END LOOP;
375
376 l_leaseChangeName := 'Amendment to Lease '||l_lease_num;
377
378 l_changeTerminationdate := p_lease_ext_end_dt;
379 l_changeExecutionDate := l_changeCommencementDate;
380
381 pn_lease_changes_pkg.Insert_Row
382 (
383 x_rowid => l_rowid
384 ,x_lease_change_id => l_leaseChangeId
385 ,x_lease_id => p_lease_id
386 ,x_lease_change_number => l_leaseChangeNumber
387 ,x_lease_change_name => l_leaseChangeName
388 ,x_responsible_user => l_user_id
389 ,x_change_commencement_date => l_changeCommencementDate
390 ,x_change_termination_date => l_changeTerminationdate
391 ,x_change_type_lookup_code => 'AMEND'
392 ,x_change_execution_date => l_changeExecutionDate
393 ,x_attribute_category => NULL
394 ,x_attribute1 => NULL
395 ,x_attribute2 => NULL
396 ,x_attribute3 => NULL
397 ,x_attribute4 => NULL
398 ,x_attribute5 => NULL
399 ,x_attribute6 => NULL
400 ,x_attribute7 => NULL
401 ,x_attribute8 => NULL
402 ,x_attribute9 => NULL
403 ,x_attribute10 => NULL
404 ,x_attribute11 => NULL
405 ,x_attribute12 => NULL
406 ,x_attribute13 => NULL
407 ,x_attribute14 => NULL
408 ,x_attribute15 => NULL
409 ,x_abstracted_by_user => l_responsibleUser
410 ,x_creation_date => l_creationDate
411 ,x_created_by => l_user_id
412 ,x_last_update_date => l_creationDate
413 ,x_last_updated_by => l_last_updated_by
414 ,x_last_update_login => l_last_update_login
415 ,x_org_id => l_org_id
416 ,x_cutoff_date => NULL
417 );
418
419 p_leaseChangeId := l_leaseChangeId;
420
421 pn_lease_details_pkg.Update_Row
422 (
423 x_lease_detail_id => l_lease_detail_id
424 ,x_lease_change_id => l_leaseChangeId
425 ,x_lease_id => p_lease_id
426 ,x_responsible_user => l_user_id
427 ,x_expense_account_id => l_expense_account_id
428 ,x_lease_commencement_date => l_leaseCommencementDate
429 ,x_lease_termination_date => l_leaseTerminationDate
430 ,x_lease_extension_end_date => p_lease_ext_end_dt
431 ,x_lease_execution_date => l_leaseExecutionDate
432 ,x_last_update_date => l_creationDate
433 ,x_last_updated_by => l_last_updated_by
434 ,x_last_update_login => l_last_update_login
435 ,x_accrual_account_id => l_accrual_account_id
436 ,x_receivable_account_id => l_receivable_account_id
437 ,x_term_template_id => l_term_template_id
438 ,x_grouping_rule_id => l_grouping_rule_id
439 ,x_attribute_category => l_attribute_category
440 ,x_attribute1 => l_attribute1
441 ,x_attribute2 => l_attribute2
442 ,x_attribute3 => l_attribute3
443 ,x_attribute4 => l_attribute4
444 ,x_attribute5 => l_attribute5
445 ,x_attribute6 => l_attribute6
446 ,x_attribute7 => l_attribute7
447 ,x_attribute8 => l_attribute8
448 ,x_attribute9 => l_attribute9
449 ,x_attribute10 => l_attribute10
450 ,x_attribute11 => l_attribute11
451 ,x_attribute12 => l_attribute12
452 ,x_attribute13 => l_attribute13
453 ,x_attribute14 => l_attribute14
454 ,x_attribute15 => l_attribute15
455 );
456
457 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.create_amendment +End+ (-)');
458
459 END create_amendment;
460
461
462 -------------------------------------------------------------------------------
463 -- PROCEDURE : ROLLFORWARD_TENANCIES
464 -- DESCRIPTION: Procedure to rollforward the tenancies associated with the lease.
465 --
466 -- 19-OCT-06 Hareesha o Created
467 -- 08-MAY-07 Hareesha o Bug #6034970 Check for space conflics when
468 -- auto-space-distribution is set to 'N', and display
469 -- msg accordingly.
470 -- 22-JAN-2010 kmaddi o Bug#9059684. Modified cursor chk_conflicts
471 -------------------------------------------------------------------------------
472 PROCEDURE ROLLFORWARD_TENANCIES(p_lease_id NUMBER,
473 p_lease_ext_end_dt DATE,
474 p_old_ext_end_dt DATE default NULL)
475 IS
476
477 CURSOR get_expandable_tenancies( p_lease_id NUMBER,p_lease_ext_end_dt DATE) IS
478 SELECT pta.tenancy_id,
479 pta.location_id,
480 NVL(pta.occupancy_date,pta.estimated_occupancy_date) st_date,
481 pta.org_id
482 FROM pn_tenancies_all pta, pn_lease_details_all plda
483 WHERE pta.lease_id = p_lease_id
484 AND pta.lease_id = plda.lease_id
485 AND pta.expiration_date = NVL(p_old_ext_end_dt, plda.lease_termination_date);
486
487 CURSOR get_lease_class_code (p_lease_id NUMBER) IS
488 SELECT lease_class_code
489 FROM pn_leases_all
490 WHERE lease_id = p_lease_id;
491
492 CURSOR get_loc_type_code (p_location_id NUMBER) IS
493 SELECT location_type_lookup_code
494 FROM pn_locations_all
495 WHERE location_id = p_location_id;
496
497 CURSOR chk_conflicts(p_lease_id NUMBER,
498 p_ten_st_dt DATE,
499 p_ten_end_dt DATE,
500 p_location_id NUMBER)
501 IS
502 SELECT 'Y'
503 FROM DUAl
504 WHERE 100 < (SELECT sum(allocated_area_pct)
505 FROM pn_leases_all pnl,
506 pn_tenancies_all ten
507 WHERE pnl.lease_class_code <> 'DIRECT'
508 AND pnl.lease_id = ten.lease_id
509 AND ten.location_id = p_location_id
510 AND NVL(ten.estimated_occupancy_date, ten.occupancy_date)
511 <= p_ten_end_dt
512 AND ten.expiration_date >= p_ten_st_dt
513 );
514
515 l_lease_class_code VARCHAR2(30);
516 l_loc_type_code VARCHAR2(30);
517
518 BEGIN
519 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_tenancies +Start+ (+)');
520
521 IF fnd_profile.value('PN_CHG_TEN_WHEN_LEASE_CHG') = 'Y' THEN
522
523 <<outer_loop>>
524 FOR rec IN get_expandable_tenancies(p_lease_id, p_lease_ext_end_dt) LOOP
525
526 FOR rec2 IN get_lease_class_code( p_lease_id) LOOP
527 l_lease_class_code := rec2.lease_class_code;
528 END LOOP;
529
530 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION', rec.org_id),'N') = 'N'
531 AND l_lease_class_code <> 'DIRECT'
532 THEN
533 FOR conflict_rec IN chk_conflicts(p_lease_id,
534 rec.st_date,
535 p_lease_ext_end_dt,
536 rec.location_id)
537 LOOP
538 EXIT outer_loop;
539 END LOOP;
540 END IF;
541
542 UPDATE pn_tenancies_all
543 SET expiration_date = p_lease_ext_end_dt
544 ,fin_oblig_end_date = p_lease_ext_end_dt
545 WHERE tenancy_id = rec.tenancy_id;
546
547 IF l_lease_class_code <> 'DIRECT' THEN
548 UPDATE pn_space_assign_cust_all
549 SET cust_assign_end_date = p_lease_ext_end_dt
550 ,fin_oblig_end_date = p_lease_ext_end_dt
551 WHERE tenancy_id = rec.tenancy_id;
552
553 FOR rec3 IN get_loc_type_code(rec.location_id) LOOP
554 l_loc_type_code := rec3.location_type_lookup_code;
555 END LOOP;
556
557 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION', rec.org_id),'N') = 'Y'
558 AND l_loc_type_code IN ('OFFICE', 'SECTION')
559 THEN
560 PN_SPACE_ASSIGN_CUST_PKG.assignment_split(
561 p_location_id => rec.location_id,
562 p_start_date => rec.st_date,
563 p_end_date => p_lease_ext_end_dt);
564
565 END IF;
566 END IF;
567
568 END LOOP outer_loop;
569 END IF;
570
571 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_tenancies +End+ (-)');
572
573 END ROLLFORWARD_TENANCIES;
574
575 -------------------------------------------------------------------------------
576 -- PROCEDURE : ROLLFORWARD_VAR_RENT
577 -- DESCRIPTION: Procedure to rollforward the VR agreements associated
578 -- with the lease.
579 --
580 -- 19-OCT-06 Hareesha o Created
581 -------------------------------------------------------------------------------
582 PROCEDURE ROLLFORWARD_VAR_RENT( p_lease_id NUMBER,
583 p_lease_ext_end_dt DATE,
584 p_old_ext_end_dt DATE,
585 p_lease_change_id NUMBER)
586 IS
587
588 l_requestId NUMBER := NULL;
589 l_lease_ext_dt_can VARCHAR2(100) := fnd_date.date_to_canonical(p_lease_ext_end_dt);
590 l_old_ext_dt_can VARCHAR2(100) := fnd_date.date_to_canonical(p_old_ext_end_dt);
591
592 BEGIN
593 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_var_rent +Start+ (+)');
594
595 l_requestId := fnd_request.submit_request ( 'PN',
596 'PNVREXCO',
597 NULL,
598 NULL,
599 FALSE,
600 p_lease_id, p_lease_change_id,
601 l_old_ext_dt_can,l_lease_ext_dt_can,
602 'EXP','Y','Y',g_reqId_sched,chr(0),
603 '', '', '', '', '', '',
604 '', '', '', '', '', '', '', '', '', '',
605 '', '', '', '', '', '', '', '', '', '',
606 '', '', '', '', '', '', '', '', '', '',
607 '', '', '', '', '', '', '', '', '', '',
608 '', '', '', '', '', '', '', '', '', '',
609 '', '', '', '', '', '', '', '', '', '',
610 '', '', '', '', '', '', '', '', '', '',
611 '', '', '', '', '', '', '', '', '', '',
612 '', '', '', '', ''
613 );
614
615 IF (l_requestId = 0 ) THEN
616 fnd_message.set_name('PN', 'PN_SCHIT_CONC_FAIL');
617 pnp_debug_pkg.put_log_msg(fnd_message.get);
618 ELSE
619 fnd_message.set_name ( 'PN', 'PN_REQUEST_SUBMITTED' );
620 fnd_message.set_token ( 'REQUEST_ID', TO_CHAR(l_requestId), FALSE);
621 pnp_debug_pkg.put_log_msg(fnd_message.get);
622 END IF;
623
624 g_reqId_sched := null;
625 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_var_rent +End+ (-)');
626
627 END ROLLFORWARD_VAR_RENT;
628
629
630 -------------------------------------------------------------------------------
631 -- PROCEDURE : ROLLFORWARD_TERMS
632 -- DESCRIPTION: Procedure to rollforward the main lease terms and
633 -- RI if needed.
634 --
635 -- 19-OCT-06 Hareesha o Created
636 -- 08-MAY-07 Hareesha o Bug#6031123 Passed ten_trm_context as 'Y' to PNSCHITM
637 -------------------------------------------------------------------------------
638 PROCEDURE ROLLFORWARD_TERMS ( p_lease_id NUMBER,
639 p_lease_ext_end_dt DATE,
640 p_extend_ri VARCHAR2)
641 IS
642 l_requestId NUMBER := NULL;
643
644 BEGIN
645 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_terms +Start+ (+)');
646
647 l_requestId := fnd_request.submit_request ( 'PN',
648 'PNSCHITM',
649 NULL,
650 NULL,
651 FALSE,
652 p_lease_id,'ROLLOVER','MAIN',
653 null, null, 'N', null, p_extend_ri,'Y',chr(0),
654 '', '', '', '',
655 '', '', '', '', '', '', '', '', '', '',
656 '', '', '', '', '', '', '', '', '', '',
657 '', '', '', '', '', '', '', '', '', '',
658 '', '', '', '', '', '', '', '', '', '',
659 '', '', '', '', '', '', '', '', '', '',
660 '', '', '', '', '', '', '', '', '', '',
661 '', '', '', '', '', '', '', '', '', '',
662 '', '', '', '', '', '', '', '', '', '',
663 '', '', '', '', '', ''
664 );
665
666 IF (l_requestId = 0 ) THEN
667 fnd_message.set_name('PN', 'PN_SCHIT_CONC_FAIL');
668 pnp_debug_pkg.put_log_msg(fnd_message.get);
669 ELSE
670 fnd_message.set_name ( 'PN', 'PN_REQUEST_SUBMITTED' );
671 fnd_message.set_token ( 'REQUEST_ID', TO_CHAR(l_requestId), FALSE);
672 pnp_debug_pkg.put_log_msg(fnd_message.get);
673 END IF;
674
675 g_reqId_sched := l_requestId;
676 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_terms +End+ (-)');
677
678 END ROLLFORWARD_TERMS;
679
680
681 -------------------------------------------------------------------------------
682 -- PROCEDURE : PRINT_OUTPUT
683 -- DESCRIPTION: Procedure to print output of Rollforward concurrent process
684 --
685 -- 19-OCT-06 Hareesha o Created
686 -------------------------------------------------------------------------------
687 PROCEDURE PRINT_OUTPUT ( p_lease_id NUMBER)
688 IS
689
690 CURSOR get_lease_details( p_lease_id NUMBER) IS
691 SELECT prop.property_name property_name,
692 NVL(loc.building,NVL(loc.floor,loc.office)) location_name,
693 lease.lease_num lease_num,
694 lease_det.lease_termination_date lease_termination_date,
695 lease_det.lease_extension_end_date lease_extension_end_date,
696 /*SUM(NVL(terms.estimated_amount,terms.actual_amount))*/
697 NULL charge,
698 ilease.index_lease_number index_rent_num,
699 var.rent_num var_rent_num
700 FROM pn_leases_all lease,
701 pn_lease_details_all lease_det,
702 pn_tenancies_all ten,
703 pn_locations_all loc,
704 pn_properties_all prop,
705 pn_index_leases_all ilease,
706 pn_var_rents_all var,
707 pn_payment_terms_all terms
708 WHERE lease.lease_id = p_lease_id
709 AND ten.primary_flag = 'Y'
710 AND ten.location_id = loc.location_id
711 AND ten.occupancy_date <= loc.active_start_date
712 AND ten.expiration_date <= loc.active_end_date
713 AND lease.lease_id = terms.lease_id
714 AND loc.property_id = prop.property_id (+)
715 AND lease.lease_id = ten.lease_id
716 AND lease.lease_id = lease_det.lease_id
717 AND lease.lease_id = ilease.lease_id (+)
718 AND lease.lease_id = var.lease_id (+)
719 ORDER BY property_name,
720 location_name,
721 lease_num,
722 index_rent_num,
723 var_rent_num;
724
725 l_message VARCHAR2(5000) := NULL;
726
727
728 BEGIN
729
730 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.print_output +Start+ (+)');
731
732 fnd_message.set_name ('PN','PN_ROLFWD_PROP');
733 l_message := fnd_message.get||' ';
734 fnd_message.set_name ('PN','PN_ROLFWD_LOC');
735 l_message := l_message||fnd_message.get||' ';
736 fnd_message.set_name ('PN','PN_ROLFWD_LEASE_NUM');
737 l_message := l_message||fnd_message.get||' ';
738 fnd_message.set_name ('PN','PN_ROLFWD_TRM_DATE');
739 l_message := l_message||fnd_message.get||' ';
740 fnd_message.set_name ('PN','PN_ROLFWD_EXT_DATE');
741 l_message := l_message||fnd_message.get||' ';
742 fnd_message.set_name ('PN','PN_ROLFWD_CHARGE');
743 l_message := l_message||fnd_message.get||' ';
744 fnd_message.set_name ('PN','PN_ROLFWD_INDEX_NUM');
745 l_message := l_message||fnd_message.get||' ';
746 fnd_message.set_name ('PN','PN_ROLFWD_VAR_NUM');
747 l_message := l_message||fnd_message.get||' ';
748 pnp_debug_pkg.put_log_msg(l_message);
749
750 pnp_debug_pkg.put_log_msg
751 ('============== '
752 ||' ============= '
753 ||' ========= '
754 ||' ====================== '
755 ||' ======================== '
756 ||' ========'
757 ||' ================= '
758 ||' =================== '
759 );
760
761 FOR rec IN get_lease_details (p_lease_id) LOOP
762
763 pnp_debug_pkg.put_log_msg(rec.property_name || ' '
764 ||rec.location_name|| ' '
765 ||rec.lease_num || ' '
766 ||TO_CHAR(rec.lease_termination_date) || ' '
767 ||TO_CHAR(rec.lease_extension_end_date)|| ' '
768 ||rec.charge ||' '
769 ||rec.index_rent_num || ' '
770 ||rec.var_rent_num || ' '
771 );
772
773 END LOOP;
774
775 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.print_output +End+ (-)');
776
777 END PRINT_OUTPUT;
778
779
780 END PN_MTM_ROLLFORWARD_PKG;