[Home] [Help]
PACKAGE BODY: APPS.PNRX_RENT_INCREASE_DETAILACC
Source
1 PACKAGE BODY pnrx_rent_increase_detailacc AS
2 /* $Header: PNRXADRB.pls 120.0 2007/10/03 14:25:27 rthumma noship $ */
3 PROCEDURE rent_increase_detailacc (
4 p_lease_number_low IN VARCHAR2 DEFAULT NULL,
5 p_lease_number_high IN VARCHAR2 DEFAULT NULL,
6 p_ri_number_low IN VARCHAR2 DEFAULT NULL,
7 p_ri_number_high IN VARCHAR2 DEFAULT NULL,
8 p_assess_date_from IN DATE,
9 p_assess_date_to IN DATE,
10 p_lease_class IN VARCHAR2 DEFAULT NULL,
11 p_property_id IN NUMBER DEFAULT NULL,
12 p_building_id IN NUMBER DEFAULT NULL,
13 p_location_id IN NUMBER DEFAULT NULL,
14 p_include_draft IN VARCHAR2 DEFAULT NULL,
15 p_rent_type IN VARCHAR2 DEFAULT NULL,
16 p_account_class IN VARCHAR2 DEFAULT NULL,
17 p_set_of_books_id IN NUMBER DEFAULT NULL,
18 p_chart_of_accounts_id IN NUMBER DEFAULT NULL,
19 l_request_id IN NUMBER,
20 l_user_id IN NUMBER,
21 retcode OUT NOCOPY VARCHAR2,
22 errbuf OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_login_id NUMBER;
26 l_org_id NUMBER;
27 l_lease_num VARCHAR2 (30);
28 l_assess_date VARCHAR2 (10);
29 l_precision NUMBER; -- slk
30 l_ext_precision NUMBER; -- slk
31 l_min_acct_unit NUMBER; -- slk
32 l_vendor_rec pnrx_rent_increase_detail.vendor_rec;
33 l_customer_rec pnrx_rent_increase_detail.customer_rec;
34 l_lease_detail pnrx_rent_increase_detail.lease_detail;
35 l_lease_detail_null pnrx_rent_increase_detail.lease_detail;
36 v_status VARCHAR2 (30) :='APPROVED';
37
38 TYPE cur_typ IS REF CURSOR;
39
40 c_lease_pn cur_typ;
41 -- term_Rec c_lease_pn%ROWTYPE;
42 term_rec pn_rent_increase_detailacc_itf%ROWTYPE;
43 term_rec_null pn_rent_increase_detailacc_itf%ROWTYPE;
44 query_str VARCHAR2 (20000);
45 --declare the 'where clauses here........'
46 lease_number_where_clause VARCHAR2 (4000) := NULL;
47 ri_number_where_clause VARCHAR2 (4000) := NULL;
48 lease_date_where_clause VARCHAR2 (4000) := NULL;
49 location_code_where VARCHAR2 (4000) := NULL;
50 lease_class_where VARCHAR2 (4000) := NULL;
51 account_class_where VARCHAR2 (4000) := NULL;
52 account_id_where VARCHAR2 (4000) := NULL;
53 rent_type_where VARCHAR2 (4000) := NULL;
54 property_name_where VARCHAR2 (4000) := NULL;
55 building_name_where VARCHAR2 (4000) := NULL;
56 location_code_from VARCHAR2 (4000) := NULL;
57 property_name_from VARCHAR2 (4000) := NULL;
58 building_name_from VARCHAR2 (4000) := NULL;
59 location_code_field VARCHAR2 (4000) := NULL;
60 include_draft_where VARCHAR2 (4000) := NULL;
61 l_get_gl_segments BOOLEAN;
62 l_gl_concatenated_segments VARCHAR2 (240);
63 --declare all columns as variables here
64
65 -- declare cursors.....
66 CURSOR cur_carry_forward
67 (p_index_lease_id IN pn_index_lease_periods_all.index_lease_id%TYPE,
68 p_l_precision IN NUMBER) IS
69 SELECT ROUND(carry_forward_amount, p_l_precision) carry_forward_amount,
70 carry_forward_percent,
71 ROUND (constraint_rent_due, p_l_precision) old_rent
72 FROM pn_index_lease_periods_all
73 WHERE index_lease_id = p_index_lease_id
74 AND assessment_date =
75 ADD_MONTHS (
76 term_rec.ilp_assessment_date,
77 -12 * term_rec.assessment_interval
78 );
79
80
81 BEGIN
82 pnp_debug_pkg.put_log_msg ('pn_rentincdeta_where_cond_set(+)');
83
84 --Initialise status parameters...
85 retcode := 0;
86 errbuf := '';
87 fnd_profile.get ('LOGIN_ID', l_login_id);
88
89 --SELECT Fnd_Profile.value('ORG_ID') INTO l_org_id
90 --FROM dual;
91
92 l_org_id := TO_NUMBER (fnd_profile.VALUE ('ORG_ID'));
93
94 --lease number conditions.....
95 --lease_number_where_clause := 'AND l.lease_num = '||'''NE213142''';
96
97 IF p_lease_number_low IS NOT NULL
98 AND p_lease_number_high IS NOT NULL
99 THEN
100 lease_number_where_clause := ' AND l.lease_num BETWEEN '
101 || ''''
102 || p_lease_number_low
103 || ''''
104 || ' AND '
105 || ''''
106 || p_lease_number_high
107 || '''';
108 ELSIF p_lease_number_low IS NULL
109 AND p_lease_number_high IS NOT NULL
110 THEN
111 lease_number_where_clause :=
112 ' AND l.lease_num = ' || '''' || p_lease_number_high || '''';
113 ELSIF p_lease_number_low IS NOT NULL
114 AND p_lease_number_high IS NULL
115 THEN
116 lease_number_where_clause :=
117 ' AND l.lease_num = ' || '''' || p_lease_number_low || '''';
118 ELSE
119 lease_number_where_clause := ' AND 4=4 ';
120 END IF;
121
122 IF p_ri_number_low IS NOT NULL
123 AND p_ri_number_high IS NOT NULL
124 THEN
125 ri_number_where_clause := ' AND il.index_lease_number BETWEEN '
126 || ''''
127 || p_ri_number_low
128 || ''''
129 || ' AND '
130 || ''''
131 || p_ri_number_high
132 || '''';
133 ELSIF p_ri_number_low IS NULL
134 AND p_ri_number_high IS NOT NULL
135 THEN
136 ri_number_where_clause :=
137 ' AND il.index_lease_number = ' || '''' || p_ri_number_high || '''';
138 ELSIF p_ri_number_low IS NOT NULL
139 AND p_ri_number_high IS NULL
140 THEN
141 ri_number_where_clause :=
142 ' AND il.index_lease_number = ' || '''' || p_ri_number_low || '''';
143 ELSE
144 ri_number_where_clause := ' AND 4=4 ';
145 END IF;
146
147 IF p_assess_date_from IS NOT NULL
148 THEN
149 lease_date_where_clause := ' AND ilp.ASSESSMENT_DATE BETWEEN '
150 || ''''
151 || p_assess_date_from
152 || ''''
153 || ' AND '
154 || ''''
155 || p_assess_date_to
156 || '''';
157 END IF;
158
159 IF p_include_draft='N' OR p_include_draft IS NULL
160 THEN
161 include_draft_where :=
162 ' AND it.status='
163 || ''''
164 || v_status
165 || '''';
166 END IF;
167
168 IF p_lease_class IS NOT NULL
169 THEN
170 lease_class_where :=
171 ' AND l.lease_class_code = ' || '''' || p_lease_class || '''';
172 END IF;
173
174 IF p_rent_type IS NOT NULL
175 THEN
176 rent_type_where :=
177 ' AND it.PAYMENT_TERM_TYPE_CODE = ' || '''' || p_rent_type || '''';
178 END IF;
179
180 IF p_account_class IS NOT NULL
181 THEN
182 account_class_where :=
183 ' AND dist.ACCOUNT_CLASS = ' || '''' || p_account_class || '''';
184 END IF;
185
186 IF p_location_id IS NOT NULL
187 THEN
188 location_code_from :=
189 ', PN_TENANCIES_ALL ten , PN_LOCATIONS_ALL loc';
190 location_code_where :=
191 ' AND l.lease_id = ten.lease_id
192 AND ten.location_id = loc.location_id
193 AND loc.rowid = (select max(loc1.rowid) from pn_locations_all loc1
194 where loc.location_id = loc1.location_id)
195 AND ten.location_id = '
196 || ''''
197 || p_location_id
198 || '''';
199 END IF;
200
201 IF p_property_id IS NOT NULL
202 THEN
203 property_name_from :=
204 ', PN_TENANCIES_ALL ten, (select bld.location_id, bld.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
205 pn_properties_all prop,
206 pn_locations_all bld
207 WHERE
208 bld.location_type_lookup_code IN ('
209 || ''''
210 || 'BUILDING'
211 || ''''
212 || ','
213 || ''''
214 || 'LAND'
215 || ''''
216 || ') AND
217 bld.property_id = prop.property_id (+)
218 UNION
219 select flr.location_id, flr.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
220 pn_properties_all prop,
221 pn_locations_all bld,
222 pn_locations_all flr
223 WHERE flr.location_type_lookup_code IN ('
224 || ''''
225 || 'FLOOR'
226 || ''''
227 || ','
228 || ''''
229 || 'PARCEL'
230 || ''''
231 || ') AND
232 bld.property_id = prop.property_id (+) AND
233 bld.location_id = flr.parent_location_id AND
234 ((flr.active_start_date BETWEEN bld.active_start_date AND
235 bld.active_end_date) OR (flr.active_end_date BETWEEN bld.active_start_date AND
236 bld.active_end_date))
237 UNION
238 select off.location_id, off.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
239 pn_properties_all prop,
240 pn_locations_all bld,
241 pn_locations_all flr,
242 pn_locations_all off
243 WHERE off.location_type_lookup_code IN ('
244 || ''''
245 || 'OFFICE'
246 || ''''
247 || ','
248 || ''''
249 || 'SECTION'
250 || ''''
251 || ') AND
252 bld.property_id = prop.property_id (+) AND
253 flr.location_id = off.parent_location_id AND
254 bld.location_id = flr.parent_location_id AND
255 ((off.active_start_date BETWEEN flr.active_start_date AND
256 flr.active_end_date) OR (off.active_end_date BETWEEN flr.active_start_date AND
257 flr.active_end_date)) ) loc';
258 property_name_where :=
259 ' AND l.lease_id = ten.lease_id
260 AND ten.location_id = loc.location_id
261 AND loc.property_id = '
262 || ''''
263 || p_property_id
264 || '''';
265 END IF;
266
267 IF p_building_id IS NOT NULL
268 THEN
269 building_name_from :=
270 ', PN_TENANCIES_ALL ten, (select bld.location_id, bld.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
271 pn_properties_all prop,
272 pn_locations_all bld
273 WHERE
274 bld.location_type_lookup_code IN ('
275 || ''''
276 || 'BUILDING'
277 || ''''
278 || ','
279 || ''''
280 || 'LAND'
281 || ''''
282 || ') AND
283 bld.property_id = prop.property_id (+)
284 UNION
285 select flr.location_id, flr.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
286 pn_properties_all prop,
287 pn_locations_all bld,
288 pn_locations_all flr
289 WHERE flr.location_type_lookup_code IN ('
290 || ''''
291 || 'FLOOR'
292 || ''''
293 || ','
294 || ''''
295 || 'PARCEL'
296 || ''''
297 || ') AND
298 bld.property_id = prop.property_id (+) AND
299 bld.location_id = flr.parent_location_id AND
300 ((flr.active_start_date BETWEEN bld.active_start_date AND
301 bld.active_end_date) OR (flr.active_end_date BETWEEN bld.active_start_date AND
302 bld.active_end_date))
303 UNION
304 select off.location_id, off.location_type_lookup_code , bld.location_id building_id, prop.property_id FROM
305 pn_properties_all prop,
306 pn_locations_all bld,
307 pn_locations_all flr,
308 pn_locations_all off
309 WHERE off.location_type_lookup_code IN ('
310 || ''''
311 || 'OFFICE'
312 || ''''
313 || ','
314 || ''''
315 || 'SECTION'
316 || ''''
317 || ') AND
318 bld.property_id = prop.property_id (+) AND
319 flr.location_id = off.parent_location_id AND
320 bld.location_id = flr.parent_location_id AND
321 ((off.active_start_date BETWEEN flr.active_start_date AND
322 flr.active_end_date) OR (off.active_end_date BETWEEN flr.active_start_date AND
323 flr.active_end_date)) ) loc';
324 building_name_where :=
325 ' AND l.lease_id = ten.lease_id
326 AND ten.location_id = loc.location_id
327 AND loc.building_id = '
328 || ''''
329 || p_building_id
330 || '''';
331 END IF;
332
333 IF p_location_id IS NOT NULL
334 OR p_property_id IS NOT NULL
335 OR p_building_id IS NOT NULL
336 THEN
337 location_code_field :=
338 ',ten.location_id , loc.location_type_lookup_code ';
339 ELSE
340 location_code_from :=
341 ', PN_TENANCIES_ALL ten , PN_LOCATIONS_ALL loc';
342 location_code_where :=
343 ' AND l.lease_id = ten.lease_id
344 AND ten.location_id = loc.location_id
345 AND loc.rowid = (select max(loc1.rowid) from pn_locations_all loc1
346 where loc.location_id = loc1.location_id) ';
347 location_code_field :=
348 ',ten.location_id , loc.location_type_lookup_code ';
349 END IF;
350
351 IF p_location_id IS NOT NULL
352 THEN
353 building_name_from := NULL;
354 building_name_where := NULL;
355 property_name_from := NULL;
356 property_name_where := NULL;
357 ELSIF p_building_id IS NOT NULL
358 THEN
359 property_name_from := NULL;
360 property_name_where := NULL;
361 END IF;
362
363 pnp_debug_pkg.put_log_msg ('pn_rentincdeta_where_cond_set(-)');
364
365 --lease cursor.....
366 OPEN c_lease_pn FOR 'SELECT
367 l.name --2
368 ,l.lease_num --3
369 ,l.payment_term_proration_rule --5
370 ,l.abstracted_by_user --6
371 ,l.lease_class_code --9
372 ,ld.lease_commencement_date --12
373 ,ld.lease_termination_date --13
374 ,ld.lease_execution_date --14
375 ,ld.lease_extension_end_date
376 ,it.payment_purpose_code --16
377 ,it.payment_term_type_code --17
378 ,it.frequency_code --18
379 ,it.start_date --19
380 ,it. end_date --20
381 ,it.vendor_id --21
382 ,it.vendor_site_id --22
383 ,it.target_date --23
384 ,it.actual_amount --24
385 ,it.estimated_amount --25
386 ,it.currency_code --27
387 ,it.rate --28
388 ,it.customer_id --29
389 ,it.customer_site_use_id --30
390 ,it.normalize'
391 || location_code_field
392 || ',it.schedule_day
393 ,it.cust_ship_site_id --34
394 ,it.ap_ar_term_id --35
395 ,it.cust_trx_type_id --36
396 ,it.project_id --37
397 ,it.task_id --38
398 ,it.organization_id --39
399 ,it.expenditure_type --40
400 ,it.expenditure_item_date --41
401 ,it.inv_rule_id --42
402 ,it.account_rule_id --43
403 ,it.salesrep_id --44
404 ,it.status payterm_status --45
405 ,it.index_term_indicator --47
406 ,il.index_lease_id --53
407 ,il.index_id --54
408 ,il.commencement_date il_commencement_date --55
409 ,il.termination_date il_termination_date --56
410 ,il.index_lease_number --57
411 ,il.assessment_date il_assessment_date --58
412 ,il.assessment_interval --59
413 ,il.spread_frequency --60
414 ,il.basis_percent_default --62
415 ,il.initial_basis --63
416 ,il.base_index --64
417 ,il.index_finder_method --66
418 ,il.index_finder_months --67
419 ,il.negative_rent_type --68
420 ,il.increase_on --69
421 ,il.basis_type --70
422 ,il.reference_period --71
423 ,il.base_year --72
424 ,il.index_multiplier -- slk
425 ,il.proration_rule -- slk
426 ,ilp.assessment_date ilp_assessment_date --73
427 ,ilp.basis_start_date --74
428 ,ilp.basis_end_date --75
429 ,ilp.index_finder_date --76
430 ,ilp.current_basis --77
431 ,ilp.relationship --78
432 ,ilp.index_percent_change --79
433 ,ilp.basis_percent_change --80
434 ,ilp.unconstraint_rent_due --81
435 ,ilp.constraint_rent_due --82
436 ,ilp.carry_forward_amount
437 ,ilp.carry_forward_percent
438 ,ilp.constraint_applied_amount
439 ,ilp.constraint_applied_percent
440 ,ilp.current_index_line_value
441 ,ilp.previous_index_line_value
442 ,dist.account_id
443 ,dist.account_class
444 ,dist.percentage
445 ,it.approved_by
446 ,it.po_header_id
447 ,it.receipt_method_id
448 ,it.tax_code_id
449 ,it.tax_group_id
450 ,it.tax_included
451 ,it.include_in_var_rent -- slk
452 FROM
453 pn_payment_terms_all it,
454 pn_leases_all l,
455 pn_lease_details_all ld,
456 pn_index_leases il ,
457 pn_distributions_all dist,
458 pn_index_lease_periods_all ilp'
459 || location_code_from
460 || property_name_from
461 || building_name_from
462 || ' WHERE it.lease_id = l.lease_id
463 AND l.lease_id = ld.lease_id
464 AND it.lease_id = il.lease_id
465 AND l.lease_id = il.lease_id
466 AND dist.payment_term_id = it.payment_term_id
467 AND it.index_period_id IS NOT NULL
468 AND ilp.index_lease_id = il.index_lease_id
469 AND ilp.index_period_id = it.index_period_id '
470 || lease_number_where_clause
471 || ri_number_where_clause
472 || lease_date_where_clause
473 || lease_class_where
474 || location_code_where
475 || property_name_where
476 || building_name_where
477 || account_class_where
478 || account_id_where
479 || rent_type_where
480 || include_draft_where;
481 pnp_debug_pkg.put_log_msg ('pn_rentincdet_open_cursor(+)');
482
483 LOOP --start lease loop....
484 term_rec := term_rec_null;
485 l_lease_detail := l_lease_detail_null;
486 term_rec.creation_date := SYSDATE;
487 term_rec.created_by := l_user_id;
488 term_rec.last_update_date := SYSDATE;
489 term_rec.last_updated_by := l_user_id;
490 term_rec.last_update_login := l_user_id;
491 term_rec.request_id := l_request_id;
492
493 FETCH c_lease_pn INTO term_rec.name, --2
494 term_rec.lease_num, --3
495 term_rec.payment_term_proration_rule, --5
496 l_lease_detail.abstracted_by_user, --6
497 l_lease_detail.lease_class_code, --9
498 term_rec.lease_commencement_date, --12
499 term_rec.lease_termination_date, --13
500 term_rec.lease_execution_date, --14
501 term_rec.lease_extension_end_date,
502 l_lease_detail.payment_purpose_code, --16
503 l_lease_detail.payment_term_type_code, --17
504 l_lease_detail.frequency_code, --18
505 term_rec.start_date, --19
506 term_rec.end_date, --20
507 l_lease_detail.vendor_id, --21
508 l_lease_detail.vendor_site_id, --22
509 term_rec.target_date, --23
510 term_rec.actual_amount, --24
511 term_rec.estimated_amount, --25
512 term_rec.currency_code, --27
513 term_rec.rate, --28
514 l_lease_detail.customer_id, --29
515 l_lease_detail.customer_site_use_id, --30
516 term_rec.normalize, --31
517 l_lease_detail.location_id, --32
518 term_rec.location_type_lookup_code,
519 term_rec.schedule_day, --33
520 l_lease_detail.cust_ship_site_id, --34
521 l_lease_detail.ap_ar_term_id, --35
522 l_lease_detail.cust_trx_type_id, --36
523 l_lease_detail.project_id, --37
524 l_lease_detail.task_id, --38
525 l_lease_detail.organization_id, --39
526 term_rec.expenditure_type, --40
527 term_rec.expenditure_item_date, --41
528 l_lease_detail.inv_rule_id, --42
529 l_lease_detail.account_rule_id, --43
530 l_lease_detail.salesrep_id, --44
531 term_rec.payterm_status, --45
532 term_rec.index_term_indicator, --47
533 l_lease_detail.index_lease_id, --53
534 l_lease_detail.index_id,--54
535 term_rec.il_commencement_date,--55
536 term_rec.il_termination_date, --56
537 term_rec.index_lease_number, --57
538 term_rec.il_assessment_date, --58
539 term_rec.assessment_interval, --59
540 l_lease_detail.spread_frequency, --60
541 term_rec.basis_percent_default, --62
542 term_rec.initial_basis, --63
543 term_rec.base_index, --64
544 l_lease_detail.index_finder_method, --66
545 term_rec.index_finder_months, --67
546 l_lease_detail.negative_rent_type, --68
547 l_lease_detail.increase_on, --69
548 l_lease_detail.basis_type, --70
549 l_lease_detail.reference_period, --71
550 term_rec.base_year, --72
551 term_rec.index_multiplier, -- slk
552 l_lease_detail.proration_rule, -- slk
553 term_rec.ilp_assessment_date, --73
554 term_rec.basis_start_date, --74
555 term_rec.basis_end_date, --75
556 term_rec.index_finder_date, --76
557 term_rec.current_basis, --77
558 l_lease_detail.relationship, --78
559 term_rec.index_percent_change, --79
560 term_rec.basis_percent_change, --80
561 term_rec.unconstraint_rent_due, --81
562 term_rec.constraint_rent_due, --82
563 term_rec.carry_forward_amount,
564 term_rec.carry_forward_percent,
565 term_rec.constraint_applied_amount,
566 term_rec.constraint_applied_percent,
567 term_rec.current_index_line_value,
568 term_rec.previous_index_line_value,
569 l_lease_detail.account_id,
570 l_lease_detail.account_class,
571 term_rec.distribution_percentage,
572 l_lease_detail.approved_by,
573 l_lease_detail.po_header_id,
574 l_lease_detail.receipt_method_id,
575 l_lease_detail.tax_code_id,
576 l_lease_detail.tax_group_id,
577 term_rec.tax_included,
578 term_rec.include_in_var_rent; -- slk
579
580 EXIT WHEN c_lease_pn%NOTFOUND;
581
582 pnp_debug_pkg.put_log_msg ('pn_rentincdet_get_details(+)');
583 term_rec.abstracted_by_user_name :=
584 pn_index_lease_common_pkg.get_approver (
585 l_lease_detail.abstracted_by_user
586 );
587 l_vendor_rec :=
588 pnrx_rent_increase_detail.get_vendor (l_lease_detail.vendor_id);
589 term_rec.vendor_name := l_vendor_rec.vendor_name;
590 term_rec.supplier_number := l_vendor_rec.vendor_number;
591 term_rec.vendor_site_code :=
592 pnrx_rent_increase_detail.get_vendor_site (
593 l_lease_detail.vendor_site_id
594 );
595 l_customer_rec :=
596 pnrx_rent_increase_detail.get_customer (
597 l_lease_detail.customer_id
598 );
599 term_rec.customer_name := l_customer_rec.customer_name;
600 term_rec.customer_number := l_customer_rec.customer_number;
601 term_rec.customer_bill_site :=
602 pnrx_rent_increase_detail.get_customer_bill_site (
603 l_lease_detail.customer_site_use_id
604 );
605 term_rec.customer_ship_site :=
606 pnrx_rent_increase_detail.get_customer_ship_site (
607 l_lease_detail.cust_ship_site_id
608 );
609 term_rec.account_rule_name :=
610 pn_index_lease_common_pkg.get_accounting_rule (
611 l_lease_detail.account_rule_id
612 );
613 term_rec.index_name :=
614 pnrx_rent_increase_detail.get_index_name (
615 l_lease_detail.index_id
616 );
617 term_rec.location_code :=
618 pnrx_rent_increase_detail.get_location_code (
619 l_lease_detail.location_id
620 );
621 term_rec.project_number :=
622 pnrx_rent_increase_detail.get_project_number (
623 l_lease_detail.project_id
624 );
625 term_rec.task_number :=
626 pnrx_rent_increase_detail.get_task_number (
627 l_lease_detail.task_id
628 );
629 term_rec.organization_name :=
630 pnp_util_func.get_ap_organization_name (
631 l_lease_detail.organization_id
632 );
633
634 IF l_lease_detail.vendor_id > 0
635 THEN
636 term_rec.ap_ar_term_name :=
637 pnp_util_func.get_ap_payment_term (
638 l_lease_detail.ap_ar_term_id
639 );
640 term_rec.tax_code :=
641 pn_r12_util_pkg.get_ap_tax_code_name (
642 l_lease_detail.tax_code_id
643 );
644 term_rec.account_class_meaning :=
645 pnrx_rent_increase_detail.get_lookup_meaning (
646 l_lease_detail.account_class,
647 'PN_PAY_ACCOUNT_TYPE'
648 );
649 END IF;
650
651 IF l_lease_detail.customer_id > 0
652 THEN
653 term_rec.ap_ar_term_name :=
654 pnp_util_func.get_ar_payment_term (
655 l_lease_detail.ap_ar_term_id
656 );
657 term_rec.tax_code :=
658 pn_r12_util_pkg.get_ar_tax_code_name (
659 l_lease_detail.tax_code_id
660 );
661 term_rec.account_class_meaning :=
662 pnrx_rent_increase_detail.get_lookup_meaning (
663 l_lease_detail.account_class,
664 'PN_REC_ACCOUNT_TYPE'
665 );
666 END IF;
667
668 term_rec.salesrep_name :=
669 pnp_util_func.get_salesrep_name (l_lease_detail.salesrep_id,l_org_id);
670 term_rec.lease_class_meaning :=
671 pnrx_rent_increase_detail.get_lookup_meaning (
672 l_lease_detail.lease_class_code,
673 'PN_LEASE_CLASS'
674 );
675 term_rec.increase_on_meaning :=
676 pnrx_rent_increase_detail.get_lookup_meaning (
677 l_lease_detail.increase_on,
678 'PN_PAYMENT_TERM_TYPE'
679 );
680 term_rec.reference_period_meaning :=
681 pnrx_rent_increase_detail.get_lookup_meaning (
682 l_lease_detail.reference_period,
683 'PN_INDEX_REF_PERIOD'
684 );
685 term_rec.negative_rent_type_meaning :=
686 pnrx_rent_increase_detail.get_lookup_meaning (
687 l_lease_detail.negative_rent_type,
688 'PN_INDEX_NEGATIVE_RENT'
689 );
690 term_rec.relationship_meaning :=
691 pnrx_rent_increase_detail.get_lookup_meaning (
692 l_lease_detail.relationship,
693 'PN_INDEX_RELATION'
694 );
695 term_rec.frequency_meaning :=
696 pnrx_rent_increase_detail.get_lookup_meaning (
697 l_lease_detail.frequency_code,
698 'PN_PAYMENT_FREQUENCY_TYPE'
699 );
700
701 term_rec.spread_frequency_meaning :=
702 pnrx_rent_increase_detail.get_lookup_meaning (
703 l_lease_detail.spread_frequency,
704 'PN_PAYMENT_FREQUENCY_TYPE'
705 );
706
707 term_rec.payment_purpose_meaning :=
708 pnrx_rent_increase_detail.get_lookup_meaning (
709 l_lease_detail.payment_purpose_code,
710 'PN_PAYMENT_PURPOSE_TYPE'
711 );
712 term_rec.payment_term_type_meaning :=
713 pnrx_rent_increase_detail.get_lookup_meaning (
714 l_lease_detail.payment_term_type_code,
715 'PN_PAYMENT_TERM_TYPE'
716 );
717 term_rec.area :=
718 pnp_util_func.get_rentable_area (
719 term_rec.location_type_lookup_code,
720 l_lease_detail.location_id,
721 term_rec.ilp_assessment_date
722 );
723 term_rec.approved_by_name :=
724 pn_index_lease_common_pkg.get_approver (
725 l_lease_detail.approved_by
726 );
727 term_rec.tax_group :=
728 pn_r12_util_pkg.get_tax_group (
729 l_lease_detail.tax_group_id
730 );
731 term_rec.po_number :=
732 pn_index_lease_common_pkg.get_po_number (
733 l_lease_detail.po_header_id
734 );
735 term_rec.payment_method :=
736 pn_index_lease_common_pkg.get_receipt_method (
737 l_lease_detail.receipt_method_id
738 );
739 term_rec.cust_trx_type_name :=
740 pn_index_lease_common_pkg.get_ar_trx_type (
741 l_lease_detail.cust_trx_type_id
742 );
743 term_rec.inv_rule_name :=
744 pn_index_lease_common_pkg.get_invoicing_rule (
745 l_lease_detail.inv_rule_id
746 );
747 term_rec.gl_concatenated_segments :=
748 fnd_flex_ext.get_segs (
749 'SQLGL',
750 'GL#',
751 p_chart_of_accounts_id,
752 l_lease_detail.account_id
753 );
754 term_rec.constraint_proration :=
755 pnrx_rent_increase_detail.get_lookup_meaning (
756 l_lease_detail.proration_rule,
757 'PN_CONSTRAINT_PRORATION'
758 );
759 term_rec.index_finder_method :=
760 pnrx_rent_increase_detail.get_lookup_meaning (
761 l_lease_detail.index_finder_method,
762 'PN_INDEX_FINDER_METHOD'
763 );
764 term_rec.basis_type :=
765 pnrx_rent_increase_detail.get_lookup_meaning (
766 l_lease_detail.basis_type,
767 'PN_INDEX_BASIS_TYPE'
768 );
769 term_rec.include_in_var_rent :=
770 pnrx_rent_increase_detail.get_lookup_meaning (
771 term_rec.include_in_var_rent,
772 'PN_PAYMENT_BKPT_BASIS_TYPE'
773 );
774 pnp_debug_pkg.put_log_msg ('pn_rentincdet_get_details(-)');
775
776 -- slk start
777 fnd_currency.get_info (term_rec.currency_code,
778 l_precision,
779 l_ext_precision,
780 l_min_acct_unit);
781 -- slk end
782
783
784 FOR rec_cur_carry_forward
785 IN cur_carry_forward(l_lease_detail.index_lease_id,
786 l_precision)
787 LOOP
788 term_rec.open_carry_forward_amount := rec_cur_carry_forward.carry_forward_amount;
789 term_rec.open_carry_forward_percent := rec_cur_carry_forward.carry_forward_percent;
790 term_rec.old_rent := rec_cur_carry_forward.old_rent;
791 END LOOP;
792
793
794 BEGIN
795 SELECT DECODE (
796 term_rec.frequency_meaning,
797 'Monthly', 12,
798 'Quarterly', 4,
799 'Semiannually', 2,
800 'Annually', 1,
801 1
802 ) * ROUND(NVL(term_rec.actual_amount,0), l_precision)
803 INTO term_rec.annual
804 FROM DUAL;
805 EXCEPTION
806 WHEN OTHERS THEN
807 NULL;
808 END;
809
810 INSERT INTO pn_rent_increase_detailacc_itf
811 (name,
812 lease_num,
813 payment_term_proration_rule,
814 abstracted_by_user_name,
815 lease_class_meaning,
816 lease_commencement_date,
817 lease_termination_date,
818 lease_execution_date,
819 lease_extension_end_date,
820 payment_purpose_meaning,
821 payment_term_type_meaning,
822 frequency_meaning,
823 start_date,
824 end_date,
825 vendor_name,
826 supplier_number,
827 vendor_site_code,
828 target_date,
829 actual_amount,
830 estimated_amount,
831 currency_code,
832 rate,
833 customer_name,
834 customer_number,
835 customer_bill_site,
836 normalize,
837 location_code,
838 location_type_lookup_code,
839 schedule_day,
840 customer_ship_site,
841 ap_ar_term_name,
842 cust_trx_type_name,
843 project_number,
844 task_number,
845 organization_name,
846 expenditure_type,
847 expenditure_item_date,
848 inv_rule_name,
849 account_rule_name,
850 salesrep_name,
851 payterm_status,
852 index_term_indicator,
853 index_name,
854 il_commencement_date,
855 il_termination_date,
856 index_lease_number,
857 il_assessment_date,
858 assessment_interval,
859 spread_frequency_meaning,
860 basis_percent_default,
861 initial_basis,
862 base_index,
863 index_finder_method,
864 index_finder_months,
865 negative_rent_type_meaning,
866 increase_on_meaning,
867 basis_type,
868 reference_period_meaning,
869 base_year,
870 ilp_assessment_date,
871 basis_start_date,
872 basis_end_date,
873 index_finder_date,
874 current_basis,
875 relationship_meaning,
876 index_percent_change,
877 basis_percent_change,
878 unconstraint_rent_due,
879 constraint_rent_due,
880 carry_forward_amount,
881 carry_forward_percent,
882 constraint_applied_amount,
883 constraint_applied_percent,
884 open_carry_forward_amount,
885 open_carry_forward_percent,
886 app_carry_forward_amount,
887 app_carry_forward_percent,
888 old_rent,
889 new_rent,
890 change_rent,
891 current_index_line_value,
892 previous_index_line_value,
893 account_class_meaning,
894 distribution_percentage,
895 distribution_amount,
896 gl_concatenated_segments,
897 approved_by_name,
898 po_number,
899 payment_method,
900 tax_code,
901 tax_group,
902 tax_included,
903 annual,
904 area,
905 annual_area,
906 constraint_proration, -- slk
907 index_multiplier, -- slk
908 unadjusted_index_change, -- slk
909 adjusted_index_change_percent,
910 include_in_var_rent, -- slk
911 last_update_date,
912 last_updated_by,
913 creation_date,
914 created_by,
915 last_update_login,
916 request_id
917 )
918 VALUES (term_rec.name,
919 term_rec.lease_num,
920 term_rec.payment_term_proration_rule,
921 term_rec.abstracted_by_user_name,
922 term_rec.lease_class_meaning,
923 term_rec.lease_commencement_date,
924 term_rec.lease_termination_date,
925 term_rec.lease_execution_date,
926 term_rec.lease_extension_end_date,
927 term_rec.payment_purpose_meaning,
928 term_rec.payment_term_type_meaning,
929 term_rec.frequency_meaning,
930 term_rec.start_date,
931 term_rec.end_date,
932 term_rec.vendor_name,
933 term_rec.supplier_number,
934 term_rec.vendor_site_code,
935 term_rec.target_date,
936 ROUND (term_rec.actual_amount, l_precision), -- slk
937 ROUND (term_rec.estimated_amount, l_precision), -- slk
938 term_rec.currency_code,
939 term_rec.rate,
940 term_rec.customer_name,
941 term_rec.customer_number,
942 term_rec.customer_bill_site,
943 term_rec.normalize,
944 term_rec.location_code,
945 term_rec.location_type_lookup_code,
946 term_rec.schedule_day,
947 term_rec.customer_ship_site,
948 term_rec.ap_ar_term_name,
949 term_rec.cust_trx_type_name,
950 term_rec.project_number,
951 term_rec.task_number,
952 term_rec.organization_name,
953 term_rec.expenditure_type,
954 term_rec.expenditure_item_date,
955 term_rec.inv_rule_name,
956 term_rec.account_rule_name,
957 term_rec.salesrep_name,
958 term_rec.payterm_status,
959 term_rec.index_term_indicator,
960 term_rec.index_name,
961 term_rec.il_commencement_date,
962 term_rec.il_termination_date,
963 term_rec.index_lease_number,
964 term_rec.il_assessment_date,
965 term_rec.assessment_interval,
966 term_rec.spread_frequency_meaning,
967 term_rec.basis_percent_default,
968 ROUND (term_rec.initial_basis, l_precision), -- slk
969 term_rec.base_index,
970 term_rec.index_finder_method,
971 term_rec.index_finder_months,
972 term_rec.negative_rent_type_meaning,
973 term_rec.increase_on_meaning,
974 term_rec.basis_type,
975 term_rec.reference_period_meaning,
976 term_rec.base_year,
977 term_rec.ilp_assessment_date,
978 term_rec.basis_start_date,
979 term_rec.basis_end_date,
980 term_rec.index_finder_date,
981 ROUND (term_rec.current_basis, l_precision), -- slk
982 term_rec.relationship_meaning,
983 term_rec.index_percent_change,
984 term_rec.basis_percent_change,
985 ROUND (term_rec.unconstraint_rent_due, l_precision), -- slk
986 ROUND (term_rec.constraint_rent_due, l_precision), -- slk
987 ROUND (term_rec.carry_forward_amount, l_precision), -- slk
988 term_rec.carry_forward_percent,
989 ROUND (term_rec.constraint_applied_amount, l_precision), -- slk
990 term_rec.constraint_applied_percent,
991 term_rec.open_carry_forward_amount,
992 term_rec.open_carry_forward_percent,
993 NVL (term_rec.open_carry_forward_amount, 0) - NVL (term_rec.carry_forward_amount, 0),
994 NVL (term_rec.open_carry_forward_percent, 0) - NVL (term_rec.carry_forward_percent, 0),
995 term_rec.old_rent,
996 ROUND (term_rec.constraint_rent_due, l_precision),
997 NVL (term_rec.constraint_rent_due, 0) - NVL (term_rec.old_rent, 0),
998 term_rec.current_index_line_value,
999 term_rec.previous_index_line_value,
1000 term_rec.account_class_meaning,
1001 term_rec.distribution_percentage,
1002 ((ROUND (term_rec.actual_amount, l_precision) * term_rec.distribution_percentage) / 100),
1003 term_rec.gl_concatenated_segments,
1004 term_rec.approved_by_name,
1005 term_rec.po_number,
1006 term_rec.payment_method,
1007 term_rec.tax_code,
1008 term_rec.tax_group,
1009 term_rec.tax_included,
1010 term_rec.annual,
1011 term_rec.area,
1012 DECODE (NVL (term_rec.area, 0), 0, 0, ROUND ((term_rec.annual / term_rec.area), 10)),
1013 term_rec.constraint_proration, -- slk
1014 term_rec.index_multiplier, -- slk
1015 term_rec.index_percent_change,
1016 term_rec.index_percent_change * NVL (term_rec.index_multiplier, 1), -- slk
1017 term_rec.include_in_var_rent, -- slk
1018 term_rec.last_update_date,
1019 term_rec.last_updated_by,
1020 term_rec.creation_date,
1021 term_rec.created_by,
1022 term_rec.last_update_login,
1023 term_rec.request_id
1024 );
1025
1026 pnp_debug_pkg.put_log_msg ('pn_rentincdet_insert(-)');
1027
1028 END LOOP; --end lease loop...
1029
1030 pnp_debug_pkg.put_log_msg ('pn_rentincdet_open_cursor(-)');
1031
1032 EXCEPTION
1033 WHEN OTHERS
1034 THEN
1035 retcode := 2;
1036 errbuf := SUBSTR (SQLERRM, 1, 235);
1037 RAISE;
1038 COMMIT;
1039 END rent_increase_detailacc;
1040 END pnrx_rent_increase_detailacc;