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