[Home] [Help]
PACKAGE BODY: APPS.PN_MTM_ROLLFORWARD_PKG
Source
1 PACKAGE BODY PN_MTM_ROLLFORWARD_PKG AS
2 /* $Header: PNRLFWDB.pls 120.0 2007/10/03 14:25:07 rthumma noship $ */
3
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
171 rollforward_terms (
172 p_lease_id => l_lease_id
173 ,p_lease_ext_end_dt => l_lease_ext_end_dt
174 ,p_extend_ri => l_extend_ri );
175
176 IF p_lease_option = 'A' THEN
177 rollforward_var_rent (
178 p_lease_id => l_lease_id
179 ,p_lease_ext_end_dt => l_lease_ext_end_dt
180 ,p_old_ext_end_dt => l_old_ext_dt
181 ,p_lease_change_id => l_lease_change_id );
182 END IF;
183
184 print_output (
185 p_lease_id => l_lease_id);
186
187 END IF;
188
189 EXCEPTION
190 WHEN INVALID_LEASE_RECORD THEN
191 pnp_debug_pkg.log(' invalid lease record ');
192 l_fail := l_fail + 1;
193 END;
194 END LOOP;
195
196 IF l_total <> 0 THEN
197 l_success := l_total - l_fail;
198
199 fnd_message.set_name('PN', 'PN_CAFM_SPACE_SUCCESS');
200 fnd_message.set_token('SUCCESS', l_success);
201 pnp_debug_pkg.put_log_msg(fnd_message.get);
202
203 fnd_message.set_name('PN', 'PN_CAFM_SPACE_FAILURE');
204 fnd_message.set_token('FAILURE', l_fail);
205 pnp_debug_pkg.put_log_msg(fnd_message.get);
206
207 fnd_message.set_name('PN', 'PN_CAFM_SPACE_TOTAL');
208 fnd_message.set_token('TOTAL', l_total);
209 pnp_debug_pkg.put_log_msg(fnd_message.get);
210
211 END IF;
212
213 IF dbms_sql.is_open (l_cursor) THEN
214 dbms_sql.close_cursor (l_cursor);
215 END IF;
216
217 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_leases +End+ (-)');
218 EXCEPTION
219 WHEN INCORECT_LEASE_EXCEPTION THEN
220 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
221 errbuf := fnd_message.get;
222 pnp_debug_pkg.put_log_msg (errbuf);
223 retcode := 2;
224
225 WHEN INCORRECT_EXTENSION_END_DATE THEN
226 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
227 errbuf := fnd_message.get;
228 pnp_debug_pkg.put_log_msg (errbuf);
229 retcode := 2;
230
231 WHEN INCORRECT_LEASE_OPTION THEN
232 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
233 errbuf := fnd_message.get;
234 pnp_debug_pkg.put_log_msg (errbuf);
235 retcode := 2;
236
237 WHEN INVALID_LEASE_RECORD THEN
238 fnd_message.set_name ('PN', 'MISSING_SETUP_CONTEXT');
239 errbuf := fnd_message.get;
240 pnp_debug_pkg.put_log_msg (errbuf);
241 retcode := 2;
242
243 WHEN OTHERS THEN
244 Errbuf := SQLERRM;
245 Retcode := 2;
246 ROLLBACK;
247
248 END rollforward_leases;
249
250
251 -------------------------------------------------------------------------------
252 -- PROCEDURE : CREATE_AMENDMENT
253 -- DESCRIPTION: Procedure to create amendment for rollforward.
254 --
255 -- 19-OCT-06 Hareesha o Created
256 -------------------------------------------------------------------------------
257 PROCEDURE CREATE_AMENDMENT( p_lease_id NUMBER,
258 p_lease_ext_end_dt DATE,
259 p_leaseChangeId OUT NOCOPY NUMBER)
260 IS
261
262 CURSOR get_lease_details (p_lease_id NUMBER) IS
263 SELECT lease_num,
264 responsible_user,
265 GREATEST(lease_termination_date,lease_extension_end_date) old_ext_dt,
266 SYSDATE,
267 lease.org_id org_id,
268 lease_detail_id,
269 expense_account_id,
270 accrual_account_id,
271 receivable_account_id,
272 term_template_id,
273 grouping_rule_id,
274 lease_commencement_date,
275 lease_termination_date,
276 lease_execution_date
277 FROM pn_leases_all lease,pn_lease_details_all det
278 WHERE lease.lease_id = p_lease_id
279 AND det.lease_id = lease.lease_id;
280
281 l_rowid VARCHAR2(18) := NULL;
282 l_leaseChangeId NUMBER := NULL;
283 l_leaseChangeNumber NUMBER := NULL;
284 l_leaseChangeName VARCHAR2(50) ;
285 l_lease_num VARCHAR2(30);
286 l_user_id NUMBER := NVL(fnd_profile.value ('USER_ID'), 0);
287 l_last_updated_by NUMBER := NVL(fnd_profile.value ('USER_ID'), 0);
288 l_last_update_login NUMBER := NVL(fnd_profile.value ('LOGIN_ID'),0);
289 l_changeCommencementDate DATE;
290 l_changeTerminationdate DATE;
291 l_changeExecutionDate DATE;
292 l_responsibleUser NUMBER;
293 l_creationDate DATE;
294 l_org_id NUMBER;
295 l_lease_detail_id NUMBER;
296 l_expense_account_id NUMBER;
297 l_accrual_account_id NUMBER(15);
298 l_receivable_account_id NUMBER(15);
299 l_term_template_id NUMBER(15);
300 l_grouping_rule_id NUMBER(15);
301 l_leaseCommencementDate DATE;
302 l_leaseTerminationDate DATE;
303 l_leaseExecutionDate DATE;
304
305 BEGIN
306
307 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.create_amendment +Start+ (+)');
308
309 FOR rec IN get_lease_details( p_lease_id ) LOOP
310 l_lease_num := rec.lease_num;
311 l_responsibleUser := rec.responsible_user;
312 l_changeCommencementDate := rec.old_ext_dt + 1;
313 l_creationDate := rec.sysdate;
314 l_org_id := rec.org_id;
315 l_lease_detail_id := rec.lease_detail_id;
316 l_expense_account_id := rec.expense_account_id;
317 l_accrual_account_id := rec.accrual_account_id;
318 l_receivable_account_id := rec.receivable_account_id;
319 l_term_template_id := rec.term_template_id;
320 l_grouping_rule_id := rec.grouping_rule_id;
321 l_leaseCommencementDate := rec.lease_commencement_date;
322 l_leaseTerminationDate := rec.lease_termination_date;
323 l_leaseExecutionDate := rec.lease_execution_date;
324
325 END LOOP;
326
327 l_leaseChangeName := 'Amendment to Lease '||l_lease_num;
328
329 l_changeTerminationdate := p_lease_ext_end_dt;
330 l_changeExecutionDate := l_changeCommencementDate;
331
332 pn_lease_changes_pkg.Insert_Row
333 (
334 x_rowid => l_rowid
335 ,x_lease_change_id => l_leaseChangeId
336 ,x_lease_id => p_lease_id
337 ,x_lease_change_number => l_leaseChangeNumber
338 ,x_lease_change_name => l_leaseChangeName
339 ,x_responsible_user => l_user_id
340 ,x_change_commencement_date => l_changeCommencementDate
341 ,x_change_termination_date => l_changeTerminationdate
342 ,x_change_type_lookup_code => 'AMEND'
343 ,x_change_execution_date => l_changeExecutionDate
344 ,x_attribute_category => NULL
345 ,x_attribute1 => NULL
346 ,x_attribute2 => NULL
347 ,x_attribute3 => NULL
348 ,x_attribute4 => NULL
349 ,x_attribute5 => NULL
350 ,x_attribute6 => NULL
351 ,x_attribute7 => NULL
352 ,x_attribute8 => NULL
353 ,x_attribute9 => NULL
354 ,x_attribute10 => NULL
355 ,x_attribute11 => NULL
356 ,x_attribute12 => NULL
357 ,x_attribute13 => NULL
358 ,x_attribute14 => NULL
359 ,x_attribute15 => NULL
360 ,x_abstracted_by_user => l_responsibleUser
361 ,x_creation_date => l_creationDate
362 ,x_created_by => l_user_id
363 ,x_last_update_date => l_creationDate
364 ,x_last_updated_by => l_last_updated_by
365 ,x_last_update_login => l_last_update_login
366 ,x_org_id => l_org_id
367 ,x_cutoff_date => NULL
368 );
369
370 p_leaseChangeId := l_leaseChangeId;
371
372 pn_lease_details_pkg.Update_Row
373 (
374 x_lease_detail_id => l_lease_detail_id
375 ,x_lease_change_id => l_leaseChangeId
376 ,x_lease_id => p_lease_id
377 ,x_responsible_user => l_user_id
378 ,x_expense_account_id => l_expense_account_id
379 ,x_lease_commencement_date => l_leaseCommencementDate
380 ,x_lease_termination_date => l_leaseTerminationDate
381 ,x_lease_extension_end_date => p_lease_ext_end_dt
382 ,x_lease_execution_date => l_leaseExecutionDate
383 ,x_last_update_date => l_creationDate
384 ,x_last_updated_by => l_last_updated_by
385 ,x_last_update_login => l_last_update_login
386 ,x_accrual_account_id => l_accrual_account_id
387 ,x_receivable_account_id => l_receivable_account_id
388 ,x_term_template_id => l_term_template_id
389 ,x_grouping_rule_id => l_grouping_rule_id
393 ,x_attribute3 => NULL
390 ,x_attribute_category => NULL
391 ,x_attribute1 => NULL
392 ,x_attribute2 => NULL
394 ,x_attribute4 => NULL
395 ,x_attribute5 => NULL
396 ,x_attribute6 => NULL
397 ,x_attribute7 => NULL
398 ,x_attribute8 => NULL
399 ,x_attribute9 => NULL
400 ,x_attribute10 => NULL
401 ,x_attribute11 => NULL
402 ,x_attribute12 => NULL
403 ,x_attribute13 => NULL
404 ,x_attribute14 => NULL
405 ,x_attribute15 => NULL
406 );
407
408 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.create_amendment +End+ (-)');
409
410 END create_amendment;
411
412
413 -------------------------------------------------------------------------------
414 -- PROCEDURE : ROLLFORWARD_TENANCIES
415 -- DESCRIPTION: Procedure to rollforward the tenancies associated with the lease.
416 --
417 -- 19-OCT-06 Hareesha o Created
418 -- 08-MAY-07 Hareesha o Bug #6034970 Check for space conflics when
419 -- auto-space-distribution is set to 'N', and display
420 -- msg accordingly.
421 -------------------------------------------------------------------------------
422 PROCEDURE ROLLFORWARD_TENANCIES(p_lease_id NUMBER,
423 p_lease_ext_end_dt DATE)
424 IS
425
426 CURSOR get_expandable_tenancies( p_lease_id NUMBER,p_lease_ext_end_dt DATE) IS
427 SELECT tenancy_id,
428 location_id,
429 NVL(occupancy_date,estimated_occupancy_date) st_date,
430 org_id
431 FROM pn_tenancies_all
432 WHERE lease_id = p_lease_id
433 AND expiration_date < p_lease_ext_end_dt;
434
435 CURSOR get_lease_class_code (p_lease_id NUMBER) IS
436 SELECT lease_class_code
437 FROM pn_leases_all
438 WHERE lease_id = p_lease_id;
439
440 CURSOR get_loc_type_code (p_location_id NUMBER) IS
441 SELECT location_type_lookup_code
442 FROM pn_locations_all
443 WHERE location_id = p_location_id;
444
445 CURSOR chk_conflicts(p_lease_id NUMBER,
446 p_ten_st_dt DATE,
447 p_ten_end_dt DATE,
448 p_location_id NUMBER)
449 IS
450 SELECT 'Y'
451 FROM DUAl
452 WHERE EXISTS (SELECT NULL
453 FROM pn_leases_all pnl,
454 pn_tenancies_all ten
455 WHERE pnl.lease_id <> p_lease_id
456 AND pnl.lease_class_code <> 'DIRECT'
457 AND pnl.lease_id = ten.lease_id
458 AND ten.location_id = p_location_id
459 AND NVL(ten.estimated_occupancy_date, ten.occupancy_date)
460 <= p_ten_end_dt
461 AND ten.expiration_date >= p_ten_st_dt
462 );
463
464 l_lease_class_code VARCHAR2(30);
465 l_loc_type_code VARCHAR2(30);
466
467 BEGIN
468 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_tenancies +Start+ (+)');
469
470 IF fnd_profile.value('PN_CHG_TEN_WHEN_LEASE_CHG') = 'Y' THEN
471
472 <<outer_loop>>
473 FOR rec IN get_expandable_tenancies(p_lease_id, p_lease_ext_end_dt) LOOP
474
475 FOR rec2 IN get_lease_class_code( p_lease_id) LOOP
476 l_lease_class_code := rec2.lease_class_code;
477 END LOOP;
478
479 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION', rec.org_id),'N') = 'N'
480 AND l_lease_class_code <> 'DIRECT'
481 THEN
482 FOR conflict_rec IN chk_conflicts(p_lease_id,
483 rec.st_date,
484 p_lease_ext_end_dt,
485 rec.location_id)
486 LOOP
487 EXIT outer_loop;
488 END LOOP;
489 END IF;
490
491 UPDATE pn_tenancies_all
492 SET expiration_date = p_lease_ext_end_dt
493 ,fin_oblig_end_date = p_lease_ext_end_dt
494 WHERE tenancy_id = rec.tenancy_id;
495
496 IF l_lease_class_code <> 'DIRECT' THEN
497 UPDATE pn_space_assign_cust_all
498 SET cust_assign_end_date = p_lease_ext_end_dt
499 ,fin_oblig_end_date = p_lease_ext_end_dt
500 WHERE tenancy_id = rec.tenancy_id;
501
502 FOR rec3 IN get_loc_type_code(rec.location_id) LOOP
503 l_loc_type_code := rec3.location_type_lookup_code;
504 END LOOP;
505
506 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION', rec.org_id),'N') = 'Y'
507 AND l_loc_type_code IN ('OFFICE', 'SECTION')
508 THEN
509 PN_SPACE_ASSIGN_CUST_PKG.assignment_split(
510 p_location_id => rec.location_id,
511 p_start_date => rec.st_date,
512 p_end_date => p_lease_ext_end_dt);
513
514 END IF;
518 END IF;
515 END IF;
516
517 END LOOP outer_loop;
519
520 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_tenancies +End+ (-)');
521
522 END ROLLFORWARD_TENANCIES;
523
524 -------------------------------------------------------------------------------
525 -- PROCEDURE : ROLLFORWARD_VAR_RENT
526 -- DESCRIPTION: Procedure to rollforward the VR agreements associated
527 -- with the lease.
528 --
529 -- 19-OCT-06 Hareesha o Created
530 -------------------------------------------------------------------------------
531 PROCEDURE ROLLFORWARD_VAR_RENT( p_lease_id NUMBER,
532 p_lease_ext_end_dt DATE,
533 p_old_ext_end_dt DATE,
534 p_lease_change_id NUMBER)
535 IS
536
537 l_requestId NUMBER := NULL;
538 l_lease_ext_dt_can VARCHAR2(100) := fnd_date.date_to_canonical(p_lease_ext_end_dt);
539 l_old_ext_dt_can VARCHAR2(100) := fnd_date.date_to_canonical(p_old_ext_end_dt);
540
541 BEGIN
542 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_var_rent +Start+ (+)');
543
544 l_requestId := fnd_request.submit_request ( 'PN',
545 'PNVREXCO',
546 NULL,
547 NULL,
548 FALSE,
549 p_lease_id, p_lease_change_id,
550 l_old_ext_dt_can,l_lease_ext_dt_can,
551 'EXP','Y','Y',chr(0),
552 '', '', '', '', '', '',
553 '', '', '', '', '', '', '', '', '', '',
554 '', '', '', '', '', '', '', '', '', '',
555 '', '', '', '', '', '', '', '', '', '',
556 '', '', '', '', '', '', '', '', '', '',
557 '', '', '', '', '', '', '', '', '', '',
558 '', '', '', '', '', '', '', '', '', '',
559 '', '', '', '', '', '', '', '', '', '',
560 '', '', '', '', '', '', '', '', '', '',
561 '', '', '', '', '', ''
562 );
563
564 IF (l_requestId = 0 ) THEN
565 fnd_message.set_name('PN', 'PN_SCHIT_CONC_FAIL');
566 pnp_debug_pkg.put_log_msg(fnd_message.get);
567 ELSE
568 fnd_message.set_name ( 'PN', 'PN_REQUEST_SUBMITTED' );
569 fnd_message.set_token ( 'REQUEST_ID', TO_CHAR(l_requestId), FALSE);
570 pnp_debug_pkg.put_log_msg(fnd_message.get);
571 END IF;
572
573 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_var_rent +End+ (-)');
574
575 END ROLLFORWARD_VAR_RENT;
576
577
578 -------------------------------------------------------------------------------
579 -- PROCEDURE : ROLLFORWARD_TERMS
580 -- DESCRIPTION: Procedure to rollforward the main lease terms and
581 -- RI if needed.
582 --
583 -- 19-OCT-06 Hareesha o Created
584 -- 08-MAY-07 Hareesha o Bug#6031123 Passed ten_trm_context as 'Y' to PNSCHITM
585 -------------------------------------------------------------------------------
586 PROCEDURE ROLLFORWARD_TERMS ( p_lease_id NUMBER,
587 p_lease_ext_end_dt DATE,
588 p_extend_ri VARCHAR2)
589 IS
590 l_requestId NUMBER := NULL;
591
592 BEGIN
593 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_terms +Start+ (+)');
594
595 l_requestId := fnd_request.submit_request ( 'PN',
596 'PNSCHITM',
597 NULL,
598 NULL,
599 FALSE,
600 p_lease_id,'ROLLOVER','MAIN',
601 null, null, 'N', null, p_extend_ri,'Y',chr(0),
602 '', '', '', '',
603 '', '', '', '', '', '', '', '', '', '',
604 '', '', '', '', '', '', '', '', '', '',
605 '', '', '', '', '', '', '', '', '', '',
606 '', '', '', '', '', '', '', '', '', '',
607 '', '', '', '', '', '', '', '', '', '',
608 '', '', '', '', '', '', '', '', '', '',
609 '', '', '', '', '', '', '', '', '', '',
610 '', '', '', '', '', '', '', '', '', '',
611 '', '', '', '', '', ''
612 );
613
614 IF (l_requestId = 0 ) THEN
618 fnd_message.set_name ( 'PN', 'PN_REQUEST_SUBMITTED' );
615 fnd_message.set_name('PN', 'PN_SCHIT_CONC_FAIL');
616 pnp_debug_pkg.put_log_msg(fnd_message.get);
617 ELSE
619 fnd_message.set_token ( 'REQUEST_ID', TO_CHAR(l_requestId), FALSE);
620 pnp_debug_pkg.put_log_msg(fnd_message.get);
621 END IF;
622
623 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.rollforward_terms +End+ (-)');
624
625 END ROLLFORWARD_TERMS;
626
627
628 -------------------------------------------------------------------------------
629 -- PROCEDURE : PRINT_OUTPUT
630 -- DESCRIPTION: Procedure to print output of Rollforward concurrent process
631 --
632 -- 19-OCT-06 Hareesha o Created
633 -------------------------------------------------------------------------------
634 PROCEDURE PRINT_OUTPUT ( p_lease_id NUMBER)
635 IS
636
637 CURSOR get_lease_details( p_lease_id NUMBER) IS
638 SELECT prop.property_name property_name,
639 NVL(loc.building,NVL(loc.floor,loc.office)) location_name,
640 lease.lease_num lease_num,
641 lease_det.lease_termination_date lease_termination_date,
642 lease_det.lease_extension_end_date lease_extension_end_date,
643 /*SUM(NVL(terms.estimated_amount,terms.actual_amount))*/
644 NULL charge,
645 ilease.index_lease_number index_rent_num,
646 var.rent_num var_rent_num
647 FROM pn_leases_all lease,
648 pn_lease_details_all lease_det,
649 pn_tenancies_all ten,
650 pn_locations_all loc,
651 pn_properties_all prop,
652 pn_index_leases_all ilease,
653 pn_var_rents_all var,
654 pn_payment_terms_all terms
655 WHERE lease.lease_id = p_lease_id
656 AND ten.primary_flag = 'Y'
657 AND ten.location_id = loc.location_id
658 AND ten.occupancy_date <= loc.active_start_date
659 AND ten.expiration_date <= loc.active_end_date
660 AND lease.lease_id = terms.lease_id
661 AND loc.property_id = prop.property_id (+)
662 AND lease.lease_id = ten.lease_id
663 AND lease.lease_id = lease_det.lease_id
664 AND lease.lease_id = ilease.lease_id (+)
665 AND lease.lease_id = var.lease_id (+)
666 ORDER BY property_name,
667 location_name,
668 lease_num,
669 index_rent_num,
670 var_rent_num;
671
672 l_message VARCHAR2(5000) := NULL;
673
674
675 BEGIN
676
677 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.print_output +Start+ (+)');
678
679 fnd_message.set_name ('PN','PN_ROLFWD_PROP');
680 l_message := fnd_message.get||' ';
681 fnd_message.set_name ('PN','PN_ROLFWD_LOC');
682 l_message := l_message||fnd_message.get||' ';
683 fnd_message.set_name ('PN','PN_ROLFWD_LEASE_NUM');
684 l_message := l_message||fnd_message.get||' ';
685 fnd_message.set_name ('PN','PN_ROLFWD_TRM_DATE');
686 l_message := l_message||fnd_message.get||' ';
687 fnd_message.set_name ('PN','PN_ROLFWD_EXT_DATE');
688 l_message := l_message||fnd_message.get||' ';
689 fnd_message.set_name ('PN','PN_ROLFWD_CHARGE');
690 l_message := l_message||fnd_message.get||' ';
691 fnd_message.set_name ('PN','PN_ROLFWD_INDEX_NUM');
692 l_message := l_message||fnd_message.get||' ';
693 fnd_message.set_name ('PN','PN_ROLFWD_VAR_NUM');
694 l_message := l_message||fnd_message.get||' ';
695 pnp_debug_pkg.put_log_msg(l_message);
696
697 pnp_debug_pkg.put_log_msg
698 ('============== '
699 ||' ============= '
700 ||' ========= '
701 ||' ====================== '
702 ||' ======================== '
703 ||' ========'
704 ||' ================= '
705 ||' =================== '
706 );
707
708 FOR rec IN get_lease_details (p_lease_id) LOOP
709
710 pnp_debug_pkg.put_log_msg(rec.property_name || ' '
711 ||rec.location_name|| ' '
712 ||rec.lease_num || ' '
713 ||TO_CHAR(rec.lease_termination_date) || ' '
714 ||TO_CHAR(rec.lease_extension_end_date)|| ' '
715 ||rec.charge ||' '
716 ||rec.index_rent_num || ' '
717 ||rec.var_rent_num || ' '
718 );
719
720 END LOOP;
721
722 pnp_debug_pkg.log('pn_mtm_rollforward_pkg.print_output +End+ (-)');
723
724 END PRINT_OUTPUT;
725
726
727 END PN_MTM_ROLLFORWARD_PKG;