[Home] [Help]
PACKAGE BODY: APPS.PNRX_RENT_LES
Source
1 PACKAGE BODY pnrx_rent_les AS
2 /* $Header: PNRXRRLB.pls 120.2 2005/12/01 14:39:03 appldev ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : PN_RENT_LES
6 -- INVOKED FROM :
7 -- PURPOSE :
8 -- HISTORY :
9 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_payment_terms with _ALL table
10 -- 27-OCT-05 sdmahesh o ATG Mandated changes for SQL literals
11 -------------------------------------------------------------------------------
12 PROCEDURE pn_rent_les(
13 lease_resp_user IN VARCHAR2,
14 location_code_low IN VARCHAR2,
15 location_code_high IN VARCHAR2,
16 lease_type IN VARCHAR2,
17 lease_number_low IN VARCHAR2,
18 lease_number_high IN VARCHAR2,
19 lease_termination_from IN DATE,
20 lease_termination_to IN DATE,
21 lease_status IN VARCHAR2,
22 lease_class IN VARCHAR2, --bug#2099864
23 l_request_id IN NUMBER,
24 l_user_id IN NUMBER,
25 retcode OUT NOCOPY VARCHAR2,
26 errbuf OUT NOCOPY VARCHAR2
27 )
28 IS
29 l_login_id NUMBER;
30 type cur_typ is ref cursor;
31 c_lease_pn CUR_TYP;
32 c_tlease_pn CUR_TYP;
33 c_loc_pn CUR_TYP;
34 c_tloc_pn CUR_TYP;
35 c_landlord_pn CUR_TYP;
36 c_rent_pn CUR_TYP;
37 c_deposit_pn CUR_TYP;
38 c_oe_pn CUR_TYP;
39 c_tenant_pn CUR_TYP;
40 c_pay_pn CUR_TYP;
41 c_schedule_pn CUR_TYP;
42 query_str VARCHAR2(20000);
43
44 --declare the 'where clauses here........'
45 lease_number_where_clause VARCHAR2(4000);
46 location_code_where_clause VARCHAR2(4000);
47 les_resp_user_where_clause VARCHAR2(4000);
48 lease_type_where_clause VARCHAR2(4000);
49 lease_date_where_clause VARCHAR2(4000);
50 lease_status_where_clause VARCHAR2(4000);
51 lease_class_where_clause VARCHAR2(4000); --bug#2099864
52 l_location_date_clause VARCHAR2(4000);
53
54 --declare all columns as variables here
55 V_LEASE_ID NUMBER;
56 V_LEASE_NAME VARCHAR2(50);
57 V_LEASE_NUMBER VARCHAR2(30);
58 V_LEASE_COM_DATE DATE;
59 V_LEASE_TERM_DATE DATE;
60 V_LEASE_EXE_DATE DATE;
61 V_LEASE_TERM NUMBER;
62 V_LEASE_CLASS VARCHAR2(80);
63 V_LEASE_CLASS_CODE VARCHAR2(80); --bug#2099864
64 V_LEASE_RESP_USER VARCHAR2(100);
65 V_LEASE_STATUS VARCHAR2(80);
66 V_LEASE_TYPE VARCHAR2(80);
67 V_ESTIMATED_OCCUPANCY_DATE DATE;
68 V_ACTUAL_OCCUPANCY_DATE DATE;
69 V_LOCATION_ID NUMBER;
70 V_LOCATION_ID_1 NUMBER;
71 V_LOCATION_ID_2 NUMBER;
72 V_ACTIVE_START_DATE DATE;
73 V_LOCATION_TYPE VARCHAR2(80);
74 V_LOCATION_NAME VARCHAR2(80);
75 V_LOCATION_CODE VARCHAR2(90);
76 V_SPACE_TYPE VARCHAR2(80);
77 V_PROPERTY_CODE VARCHAR2(90);
78 V_REGION_NAME VARCHAR2(50);
79 V_ADDRESS VARCHAR2(1500);
80 V_COUNTY VARCHAR2(60);
81 V_CITY VARCHAR2(60);
82 V_STATE VARCHAR2(60);
83 V_PROVINCE VARCHAR2(60);
84 V_ZIP_CODE VARCHAR2(60);
85 V_COUNTRY VARCHAR2(60);
86 V_RENTABLE_AREA NUMBER;
87 V_USABLE_AREA NUMBER;
88 V_GROSS_AREA NUMBER;
89 V_TENURE VARCHAR2(80);
90 V_VENDOR_ID NUMBER;
91 V_VENDOR_SITE_ID NUMBER;
92 V_VENDOR_SITE VARCHAR2(15);
93 V_ANNUAL_BASE_RENT NUMBER := 0;
94 V_MONTHLY_BASE_RENT NUMBER := 0;
95 V_VENDOR_NAME VARCHAR2(360);
96 V_INVOICING_ADDRESS VARCHAR2(1500);
97 V_CUSTOMER_ID NUMBER;
98 V_CUSTOMER_SITE VARCHAR2(40);
99 V_CUSTOMER_SITE_USE_ID NUMBER;
100 V_CUSTOMER_NAME VARCHAR2(360);
101 V_ACTUAL_AMOUNT NUMBER;
102 V_DEPOSIT NUMBER:=0;
103 V_MONTHLY_OPERATING_EXPENSES NUMBER:=0;
104 V_ANNUAL_OPERATING_EXPENSES NUMBER:=0;
105 V_TOTAL_LEASE_LIABILITY NUMBER:=0;
106 V_AMOUNT_EXPORTED NUMBER:=0;
107 V_REMAINING_LEASE_LIABILITY NUMBER:=0;
108 V_ANNUAL_RENT_PER_RENT_AREA NUMBER:=0;
109 V_PAYMENT_TERM_ID NUMBER;
110 V_PAYMENT_TERM_TYPE_CODE VARCHAR2(30);
111 V_PAYMENT_SCHEDULE_ID NUMBER;
112 V_ATTRIBUTE_CATEGORY VARCHAR2(30);
113 V_ATTRIBUTE1 VARCHAR2(150);
114 V_ATTRIBUTE2 VARCHAR2(150);
115 V_ATTRIBUTE3 VARCHAR2(150);
116 V_ATTRIBUTE4 VARCHAR2(150);
117 V_ATTRIBUTE5 VARCHAR2(150);
118 V_ATTRIBUTE6 VARCHAR2(150);
119 V_ATTRIBUTE7 VARCHAR2(150);
120 V_ATTRIBUTE8 VARCHAR2(150);
121 V_ATTRIBUTE9 VARCHAR2(150);
122 V_ATTRIBUTE10 VARCHAR2(150);
123 V_ATTRIBUTE11 VARCHAR2(150);
124 V_ATTRIBUTE12 VARCHAR2(150);
125 V_ATTRIBUTE13 VARCHAR2(150);
126 V_ATTRIBUTE14 VARCHAR2(150);
127 V_ATTRIBUTE15 VARCHAR2(150);
128 V_LAST_UPDATE_DATE DATE;
129 V_LAST_UPDATED_BY NUMBER;
130 V_LAST_UPDATE_LOGIN NUMBER;
131 V_CREATION_DATE DATE;
132 V_CREATED_BY NUMBER;
133 V_PRORATION_RULE NUMBER; --added for BUG#2102098
134 V_NO_OF_MONTHS NUMBER ; --added for BUG#2102098
135 V_AVG_ANNUAL_BASE_RENT NUMBER := 0; --added for BUG#2102098
136 V_AVG_MONTHLY_BASE_RENT NUMBER := 0; --added for BUG#2102098
137 V_AVG_MONTHLY_OPERATING_EXP NUMBER:=0; --added for BUG#2102098
138 V_AVG_ANNUAL_OPERATING_EXP NUMBER:=0; --added for BUG#2102098
139
140 l_cursor INTEGER;
141 l_rows INTEGER;
142 l_count INTEGER;
143 l_lease_resp_user VARCHAR2(100);
144 l_location_code_low VARCHAR2(90);
145 l_location_code_high VARCHAR2(90);
146 l_lease_type VARCHAR2(30);
147 l_lease_number_low VARCHAR2(30);
148 l_lease_number_high VARCHAR2(30);
149 l_lease_termination_from DATE;
150 l_lease_termination_to DATE;
151 l_lease_status VARCHAR2(1);
152 l_lease_class VARCHAR2(30);
153 l_statement VARCHAR2(10000);
154
155
156
157 --declare the record type for the function here.........
158 v_code_data PNP_UTIL_FUNC.location_name_rec := NULL;
159 -- declare cursors.....
160 CURSOR pterm (V_LEASE_ID IN NUMBER) IS
161 SELECT payment_term_id,start_date,END_date,payment_term_type_code,vENDor_id,vENDor_site_id,
162 customer_id, customer_site_use_id
163 FROM pn_payment_terms_all
164 WHERE lease_id = V_LEASE_ID;
165
166 CURSOR pitem(V_LEASE_ID IN NUMBER) IS
167 SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
168 FROM pn_payment_items_all ppi, pn_payment_terms_all ppt, pn_payment_schedules_all pps
169 WHERE ppi.payment_term_id = ppt.payment_term_id
170 AND ppi.payment_schedule_id = pps.payment_schedule_id
171 AND ppt.lease_id = V_LEASE_ID
172 AND pps.lease_id = V_LEASE_ID
173 AND ppi.payment_item_type_lookup_code = 'CASH'
174 AND ppt.payment_term_type_code = 'BASER';
175
176 CURSOR pitem1(V_LEASE_ID IN NUMBER) IS
177 SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
178 FROM pn_payment_items_all ppi, pn_payment_terms_all ppt, pn_payment_schedules_all pps
179 WHERE ppi.payment_term_id = ppt.payment_term_id
180 AND ppi.payment_schedule_id = pps.payment_schedule_id
181 AND ppt.lease_id = V_LEASE_ID
182 AND pps.lease_id = V_LEASE_ID
183 AND ppi.payment_item_type_lookup_code = 'CASH'
184 AND ppt.payment_term_type_code = 'DEP';
185
186 CURSOR pitem2(V_LEASE_ID IN NUMBER) IS
187 SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
188 FROM pn_payment_items_all ppi, pn_payment_terms_all ppt, pn_payment_schedules_all pps
189 WHERE ppi.payment_term_id = ppt.payment_term_id
190 AND ppi.payment_schedule_id = pps.payment_schedule_id
191 AND ppt.lease_id = V_LEASE_ID
192 AND pps.lease_id = V_LEASE_ID
193 AND ppi.payment_item_type_lookup_code = 'CASH'
194 AND ppt.payment_term_type_code = 'OEXP';
195
196 CURSOR pitem3(V_LEASE_ID IN NUMBER) IS
197 SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
198 FROM pn_payment_items_all ppi, pn_payment_terms_all ppt
199 WHERE ppi.payment_term_id = ppt.payment_term_id
200 AND ppt.lease_id = V_LEASE_ID
201 AND ppi.payment_item_type_lookup_code = 'CASH';
202
203 CURSOR pitem4(V_LEASE_ID IN NUMBER) IS
204 SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
205 FROM pn_payment_items_all ppi, pn_payment_terms_all ppt
206 WHERE ppi.payment_term_id = ppt.payment_term_id
207 AND ppt.lease_id = V_LEASE_ID
208 AND ppi.payment_item_type_lookup_code = 'CASH'
209 AND (ppi.transferred_to_ap_flag = 'Y' OR
210 ppi.transferred_to_ar_flag = 'Y') ; -- Bug # 1671866
211
212 BEGIN
213
214 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_where_cond_set(+)');
215
216 --Initialise status parameters...
217 retcode:=0;
218 errbuf:='';
219 fnd_profile.get('LOGIN_ID', l_login_id);
220
221 l_cursor := dbms_sql.open_cursor;
222 --lease responsible user conditions....
223 l_lease_resp_user := lease_resp_user;
224 IF lease_resp_user IS NOT NULL THEN
225 les_resp_user_where_clause := ' AND fnd4.user_name = :l_lease_resp_user';
226 ELSE les_resp_user_where_clause:=' AND 1=1 ';
227 END IF;
228
229 --location code conditions.....
230 l_location_code_low := location_code_low;
231 l_location_code_high := location_code_high;
232
233 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
234 location_code_where_clause := ' WHERE loc.location_code BETWEEN
235 :l_location_code_low AND :l_location_code_high';
236 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
237 location_code_where_clause := ' WHERE loc.location_code =
238 :l_location_code_high';
239 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
240 location_code_where_clause := ' WHERE loc.location_code =
241 :l_location_code_low';
242 ELSE location_code_where_clause := ' WHERE 2=2 ';
243 END IF;
244
245 --lease type conditions....
246 l_lease_type := lease_type;
247 IF lease_type IS NOT NULL THEN
248 lease_type_where_clause:= ' AND les.lease_type_code = :l_lease_type';
249 ELSE lease_type_where_clause:=' AND 3=3 ';
250 END IF;
251
252 --lease number conditions.....
253
254 l_lease_number_low := lease_number_low;
255 l_lease_number_high := lease_number_high;
256
257 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
258 lease_number_where_clause := ' AND les.lease_num BETWEEN
259 :l_lease_number_low AND :l_lease_number_high';
260 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
261 lease_number_where_clause := ' AND les.lease_num = :l_lease_number_high';
262 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
263 lease_number_where_clause := ' AND les.lease_num = :l_lease_number_low';
264 ELSE lease_number_where_clause := ' AND 4=4 ';
265
266 END IF;
267
268 --lease date conditions.....
269 l_lease_termination_from := lease_termination_from;
270 l_lease_termination_to := lease_termination_to;
271 IF lease_termination_from IS NOT NULL AND lease_termination_to IS NOT NULL THEN
272 lease_date_where_clause := ' AND les.lease_termination_date BETWEEN :l_lease_termination_from
273 AND :l_lease_termination_to';
274 ELSIF lease_termination_from IS NULL AND lease_termination_to IS NOT NULL THEN
275 lease_date_where_clause := ' AND les.lease_termination_date =
276 :l_lease_termination_to';
277 ELSIF lease_termination_from IS NOT NULL AND lease_termination_to IS NULL THEN
278 lease_date_where_clause := ' AND les.lease_termination_date =
279 :l_lease_termination_from';
280 ELSE lease_date_where_clause := ' AND 5=5 ';
281 END IF;
282
283 --lease status conditions.....
284 l_lease_status := lease_status;
285 IF lease_status IS NOT NULL THEN
286 lease_status_where_clause := 'AND les.status = :l_lease_status';
287 ELSE lease_status_where_clause := ' AND 6=6 ';
288 END IF;
289
290 --lease class conditions..... bug#2099864
291 l_lease_class := lease_class;
292 IF lease_class IS NOT NULL THEN
293 lease_class_where_clause := 'AND les.lease_class_code = :l_lease_class';
294 ELSE lease_class_where_clause := ' AND 7=7 ';
295 END IF;
296
297 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_where_cond_set(-)');
298 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_open_c_lease_pn(+)');
299
300
301 -- Adding to where clause in pn_locations in order to select
302 -- the location that is active
303 -- for that lease period
304
305 l_location_date_clause := 'AND loc.active_start_date <= ldet.lease_commencement_date ' ;
306 l_location_date_clause := l_location_date_clause ||
307 ' AND loc.active_END_date >= ldet.lease_termination_date ';
308
309 --lease cursor.....
310
311 l_statement :=
312 'SELECT
313 distinct
314 ten.location_id LOCATION_ID,
315 fnd2.meaning USAGE_TYPE,
316 les.lease_id LEASE_ID,
317 les.name LEASE_NAME,
318 les.lease_num LEASE_NUMBER,
319 ldet.lease_commencement_date LEASE_COMMENCEMENT_DATE,
320 ldet.lease_termination_date LEASE_TERMINATION_DATE,
321 ldet.lease_execution_date LEASE_EXECUTION_DATE,
322 (TRUNC(ldet.lease_termination_date)- TRUNC(ldet.lease_commencement_date)+1) LEASE_TERM,
323 fnd1.meaning LEASE_CLASS,
324 les.lease_class_code LEASE_CLASS_1, --bug#2099864
325 fnd4.user_name LEASE_RESPONSIBLE_USER,
326 fnd.meaning LEASE_STATUS,
327 fnd3.meaning LEASE_TYPE,
328 ten.estimated_occupancy_date ESTIMATED_OCCUPANCY_DATE,
329 ten.occupancy_date OCCUPANCY_DATE,
330 ten.attribute_category ATTRIBUTE_CATEGORY,
331 ten.attribute1 ATTRIBUTE1,
332 ten.attribute2 ATTRIBUTE2,
333 ten.attribute3 ATTRIBUTE3,
334 ten.attribute4 ATTRIBUTE4,
335 ten.attribute5 ATTRIBUTE5,
336 ten.attribute6 ATTRIBUTE6,
337 ten.attribute7 ATTRIBUTE7,
338 ten.attribute8 ATTRIBUTE8,
339 ten.attribute9 ATTRIBUTE9,
340 ten.attribute10 ATTRIBUTE10,
341 ten.attribute11 ATTRIBUTE11,
342 ten.attribute12 ATTRIBUTE12,
343 ten.attribute13 ATTRIBUTE13,
344 ten.attribute14 ATTRIBUTE14,
345 ten.attribute15 ATTRIBUTE15,
346 ldet.last_update_date LAST_UPDATE_DATE,
347 ldet.last_updated_by LAST_UPDATED_BY,
348 ldet.last_update_login LAST_UPDATE_LOGIN,
349 ldet.creation_date CREATION_DATE,
350 ldet.created_by CREATED_BY,
351 les.payment_term_proration_rule PRORATION_RULE -- added for bug#2102098
352 FROM pn_lease_details ldet,
353 pn_tenancies_all ten,
354 fnd_lookups fnd,
355 fnd_lookups fnd1,
356 fnd_lookups fnd2,
357 fnd_lookups fnd3,
358 fnd_user fnd4,
359 pn_leases_all les
360 WHERE ten.lease_id = les.lease_id
361 AND les.lease_id = ldet.lease_id
362 AND ten.primary_flag = '||'''' || 'Y'||''''||'
363 AND les.status = fnd.lookup_code
364 AND fnd.lookup_type = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
365 AND fnd1.lookup_code = les.lease_class_code
366 AND fnd1.lookup_type = '||''''||'PN_LEASE_CLASS'||''''||'
367 AND fnd2.lookup_code = ten.tenancy_usage_lookup_code
368 AND fnd2.lookup_type = '||''''||'PN_TENANCY_USAGE_TYPE'||''''||'
369 AND fnd3.lookup_code = les.lease_type_code
370 AND fnd3.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
371 AND fnd4.user_id = ldet.responsible_user
372 AND ten.location_id IN(SELECT loc.location_id from pn_locations loc'||location_code_where_clause||' ' ||
373 l_location_date_clause ||')'
374 ||les_resp_user_where_clause
375 ||lease_type_where_clause
376 ||lease_number_where_clause
377 ||lease_date_where_clause
378 ||lease_status_where_clause
379 ||lease_class_where_clause
380 ||'ORDER BY les.lease_class_code';
381
382
383 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_open_c_lease_pn(-)');
384
385 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
386
387 --lease responsible user conditions....
388
389 IF lease_resp_user IS NOT NULL THEN
390 dbms_sql.bind_variable(l_cursor,'l_lease_resp_user',l_lease_resp_user);
391 END IF;
392
393
394 --location code conditions.....
395 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
396 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
397 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
398 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
399 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
400 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
401 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
402 END IF;
403
404 --lease type conditions....
405 IF lease_type IS NOT NULL THEN
406 dbms_sql.bind_variable(l_cursor,'l_lease_type',l_lease_type);
407 END IF;
408
409 --lease number conditions.....
410 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
411 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
412 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
413 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
414 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
415 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
416 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
417 END IF;
418
419 --lease date conditions.....
420 IF lease_termination_from IS NOT NULL AND lease_termination_to IS NOT NULL THEN
421 dbms_sql.bind_variable(l_cursor,'l_lease_termination_from',l_lease_termination_from);
422 dbms_sql.bind_variable(l_cursor,'l_lease_termination_to',l_lease_termination_to);
423 ELSIF lease_termination_from IS NULL AND lease_termination_to IS NOT NULL THEN
424 dbms_sql.bind_variable(l_cursor,'l_lease_termination_to',l_lease_termination_to);
425 ELSIF lease_termination_from IS NOT NULL AND lease_termination_to IS NULL THEN
426 dbms_sql.bind_variable(l_cursor,'l_lease_termination_from',l_lease_termination_from);
427 END IF;
428
429
430 --lease status conditions.....
431 IF lease_status IS NOT NULL THEN
432 dbms_sql.bind_variable(l_cursor,'l_lease_status',l_lease_status);
433 END IF;
434
435 --lease class conditions..... bug#2099864
436 IF lease_class IS NOT NULL THEN
437 dbms_sql.bind_variable(l_cursor,'l_lease_class',l_lease_class);
438 END IF;
439
440 dbms_sql.define_column (l_cursor,1,V_LOCATION_ID_1);
441 dbms_sql.define_column (l_cursor,2,V_SPACE_TYPE,80);
442 dbms_sql.define_column (l_cursor,3,V_LEASE_ID);
443 dbms_sql.define_column (l_cursor,4,V_LEASE_NAME,50);
444 dbms_sql.define_column (l_cursor,5,V_LEASE_NUMBER,30);
445 dbms_sql.define_column (l_cursor,6,V_LEASE_COM_DATE);
446 dbms_sql.define_column (l_cursor,7,V_LEASE_TERM_DATE);
447 dbms_sql.define_column (l_cursor,8,V_LEASE_EXE_DATE);
448 dbms_sql.define_column (l_cursor,9,V_LEASE_TERM);
449 dbms_sql.define_column (l_cursor,10,V_LEASE_CLASS,80);
450 dbms_sql.define_column (l_cursor,11,V_LEASE_CLASS_CODE,30);
451 dbms_sql.define_column (l_cursor,12,V_LEASE_RESP_USER,100);
452 dbms_sql.define_column (l_cursor,13,V_LEASE_STATUS,80);
453 dbms_sql.define_column (l_cursor,14,V_LEASE_TYPE,80);
454 dbms_sql.define_column (l_cursor,15,V_ESTIMATED_OCCUPANCY_DATE);
455 dbms_sql.define_column (l_cursor,16,V_ACTUAL_OCCUPANCY_DATE);
456 dbms_sql.define_column (l_cursor,17,V_ATTRIBUTE_CATEGORY,30);
457 dbms_sql.define_column (l_cursor,18,V_ATTRIBUTE1,150);
458 dbms_sql.define_column (l_cursor,19,V_ATTRIBUTE2,150);
459 dbms_sql.define_column (l_cursor,20,V_ATTRIBUTE3,150);
460 dbms_sql.define_column (l_cursor,21,V_ATTRIBUTE4,150);
461 dbms_sql.define_column (l_cursor,22,V_ATTRIBUTE5,150);
462 dbms_sql.define_column (l_cursor,23,V_ATTRIBUTE6,150);
463 dbms_sql.define_column (l_cursor,24,V_ATTRIBUTE7,150);
464 dbms_sql.define_column (l_cursor,25,V_ATTRIBUTE8,150);
465 dbms_sql.define_column (l_cursor,26,V_ATTRIBUTE9,150);
466 dbms_sql.define_column (l_cursor,27,V_ATTRIBUTE10,150);
467 dbms_sql.define_column (l_cursor,28,V_ATTRIBUTE11,150);
468 dbms_sql.define_column (l_cursor,29,V_ATTRIBUTE12,150);
469 dbms_sql.define_column (l_cursor,30,V_ATTRIBUTE13,150);
470 dbms_sql.define_column (l_cursor,31,V_ATTRIBUTE14,150);
471 dbms_sql.define_column (l_cursor,32,V_ATTRIBUTE15,150);
472 dbms_sql.define_column (l_cursor,33,V_LAST_UPDATE_DATE);
473 dbms_sql.define_column (l_cursor,34,V_LAST_UPDATED_BY);
474 dbms_sql.define_column (l_cursor,35,V_LAST_UPDATE_LOGIN);
475 dbms_sql.define_column (l_cursor,36,V_CREATION_DATE);
476 dbms_sql.define_column (l_cursor,37,V_CREATED_BY);
477 dbms_sql.define_column (l_cursor,38,V_PRORATION_RULE);
478
479 l_rows := dbms_sql.execute(l_cursor);
480
481
482
483 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_c_lease_pn_loop(+)');
484 LOOP --start lease loop....
485
486 l_count := dbms_sql.fetch_rows( l_cursor );
487
488 EXIT WHEN l_count <> 1;
489
490 dbms_sql.column_value (l_cursor,1,V_LOCATION_ID_1);
491 dbms_sql.column_value (l_cursor,2,V_SPACE_TYPE);
492 dbms_sql.column_value (l_cursor,3,V_LEASE_ID);
493 dbms_sql.column_value (l_cursor,4,V_LEASE_NAME);
494 dbms_sql.column_value (l_cursor,5,V_LEASE_NUMBER);
495 dbms_sql.column_value (l_cursor,6,V_LEASE_COM_DATE);
496 dbms_sql.column_value (l_cursor,7,V_LEASE_TERM_DATE);
497 dbms_sql.column_value (l_cursor,8,V_LEASE_EXE_DATE);
498 dbms_sql.column_value (l_cursor,9,V_LEASE_TERM);
499 dbms_sql.column_value (l_cursor,10,V_LEASE_CLASS);
500 dbms_sql.column_value (l_cursor,11,V_LEASE_CLASS_CODE);
501 dbms_sql.column_value (l_cursor,12,V_LEASE_RESP_USER);
502 dbms_sql.column_value (l_cursor,13,V_LEASE_STATUS);
503 dbms_sql.column_value (l_cursor,14,V_LEASE_TYPE);
504 dbms_sql.column_value (l_cursor,15,V_ESTIMATED_OCCUPANCY_DATE);
505 dbms_sql.column_value (l_cursor,16,V_ACTUAL_OCCUPANCY_DATE);
506 dbms_sql.column_value (l_cursor,17,V_ATTRIBUTE_CATEGORY);
507 dbms_sql.column_value (l_cursor,18,V_ATTRIBUTE1);
508 dbms_sql.column_value (l_cursor,19,V_ATTRIBUTE2);
509 dbms_sql.column_value (l_cursor,20,V_ATTRIBUTE3);
510 dbms_sql.column_value (l_cursor,21,V_ATTRIBUTE4);
511 dbms_sql.column_value (l_cursor,22,V_ATTRIBUTE5);
512 dbms_sql.column_value (l_cursor,23,V_ATTRIBUTE6);
513 dbms_sql.column_value (l_cursor,24,V_ATTRIBUTE7);
514 dbms_sql.column_value (l_cursor,25,V_ATTRIBUTE8);
515 dbms_sql.column_value (l_cursor,26,V_ATTRIBUTE9);
516 dbms_sql.column_value (l_cursor,27,V_ATTRIBUTE10);
517 dbms_sql.column_value (l_cursor,28,V_ATTRIBUTE11);
518 dbms_sql.column_value (l_cursor,29,V_ATTRIBUTE12);
519 dbms_sql.column_value (l_cursor,30,V_ATTRIBUTE13);
520 dbms_sql.column_value (l_cursor,31,V_ATTRIBUTE14);
521 dbms_sql.column_value (l_cursor,32,V_ATTRIBUTE15);
522 dbms_sql.column_value (l_cursor,33,V_LAST_UPDATE_DATE);
523 dbms_sql.column_value (l_cursor,34,V_LAST_UPDATED_BY);
524 dbms_sql.column_value (l_cursor,35,V_LAST_UPDATE_LOGIN);
525 dbms_sql.column_value (l_cursor,36,V_CREATION_DATE);
526 dbms_sql.column_value (l_cursor,37,V_CREATED_BY);
527 dbms_sql.column_value (l_cursor,38,V_PRORATION_RULE);
528
529 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_open_c_loc_pn(+)');
530 OPEN c_loc_pn FOR
531 SELECT
532 distinct
533 loc.location_id LOCATION_ID,
534 loc.active_start_date ACTIVE_START_DATE,
535 fnd1.meaning LOCATION_TYPE,
536 NVL(NVL(loc.building, loc.FLOOR), loc.office) LOCATION_NAME,
537 loc.location_code LOCATION_CODE,
538 pa.address_line1||pa.address_line2||pa.address_line3||pa.address_line4||pa.county||pa.city||pa.state||pa.province||pa.zip_code||pa.country ADDRESS,
539 pa.county COUNTY,
540 pa.city CITY,
541 pa.state STATE,
542 pa.province PROVINCE,
543 pa.zip_code ZIP_CODE,
544 pa.country COUNTRY,
545 DECODE (location_type_lookup_code,'OFFICE',loc.rentable_area,
546 'FLOOR',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id, loc.active_start_date),
547 'PARCEL',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id, loc.active_start_date),
548 'BUILDING',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id, loc.active_start_date),
549 'LAND',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id, loc.active_start_date),
550 rentable_area) RENTABLE_AREA,
551 DECODE (location_type_lookup_code,'OFFICE',loc.usable_area,
552 'FLOOR',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id, loc.active_start_date),
553 'PARCEL',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id, loc.active_start_date),
554 'BUILDING',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id, loc.active_start_date),
555 'LAND',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id, loc.active_start_date),
556 usable_area) USABLE_AREA,
557 loc.gross_area GROSS_AREA,
558 fnd.meaning TENURE
559 FROM pn_locations_all loc,
560 pn_addresses_all pa,
561 fnd_lookups fnd,
562 fnd_lookups fnd1
563 WHERE pa.address_id(+) = loc.address_id
564 AND loc.lease_or_owned = fnd.lookup_code(+)
565 AND fnd.lookup_type(+) = 'PN_LEASED_OR_OWNED'
566 AND fnd1.lookup_code(+) = loc.location_type_lookup_code
567 AND fnd1.lookup_type(+) = 'PN_LOCATION_TYPE'
568 AND loc.location_id = V_LOCATION_ID_1;
569
570 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_open_c_loc_pn(-)');
571 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_c_loc_pn_loop(+)');
572 LOOP --start location loop
573 FETCH c_loc_pn INTO V_LOCATION_ID ,
574 V_ACTIVE_START_DATE ,
575 V_LOCATION_TYPE ,
576 V_LOCATION_NAME ,
577 V_LOCATION_CODE ,
578 V_ADDRESS ,
579 V_COUNTY ,
580 V_CITY ,
581 V_STATE ,
582 V_PROVINCE ,
583 V_ZIP_CODE ,
584 V_COUNTRY ,
585 V_RENTABLE_AREA ,
586 V_USABLE_AREA ,
587 V_GROSS_AREA ,
588 V_TENURE ;
589 EXIT WHEN c_loc_pn%notfound;
590
591 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_payment_term_loop(+)');
592
593 --bug#2099864
594 /* This IF condition eliminates the need for the earlier existing 2 loops -
595 one for 'DIRECT' leases and the other for not 'DIRECT'.
596 IF Lease Class is 'DIRECT', get Customer info,
597 ELSE get VENDor info.
598 */
599
600 IF ( V_LEASE_CLASS_CODE = 'DIRECT' ) THEN
601
602 V_CUSTOMER_ID := NULL;
603 V_CUSTOMER_SITE := NULL;
604 V_CUSTOMER_NAME := NULL;
605 FOR c IN pterm(V_LEASE_ID)
606 LOOP --Payment Term Loop
607 SELECT
608 pv.vENDor_id VENDOR_ID,
609 pvs.vENDor_site_code VENDOR_SITE,
610 pv.vENDor_name VENDOR_NAME,
611 pvs.address_line1||pvs.address_line2||pvs.address_line3||pvs.address_line4||pvs.county||pvs.city||pvs.state||pvs.province||pvs.zip||pvs.country INVOICING_ADDRESS
612 INTO V_VENDOR_ID ,
613 V_VENDOR_SITE ,
614 V_VENDOR_NAME ,
615 V_INVOICING_ADDRESS
616 FROM po_vENDors pv,
617 po_vENDor_sites pvs
618 WHERE pv.vENDor_id = pvs.vENDor_id
619 AND pvs.vENDor_id = c.vENDor_id
620 AND pvs.vENDor_site_id = c.vENDor_site_id;
621 END LOOP; -- END Payment Term Loop
622
623 ELSIF ( V_LEASE_CLASS_CODE IN ( 'SUB_LEASE', 'THIRD_PARTY' )) THEN
624
625 V_VENDOR_ID := NULL;
626 V_VENDOR_SITE := NULL;
627 V_VENDOR_NAME := NULL;
628 FOR t IN pterm(V_LEASE_ID)
629 LOOP --Payment Term Loop
630 SELECT
631 hca.cust_account_id CUSTOMER_ID,
632 hcs.location CUSTOMER_SITE_ID,
633 hp.party_name CUSTOMER_NAME,
634 hp.address1||hp.address2||hp.address3||hp.address4||hp.county||hp.city||hp.state||hp.province||hp.postal_code||hp.country INVOICING_ADDRESS
635 INTO V_CUSTOMER_ID,
636 V_CUSTOMER_SITE,
637 V_CUSTOMER_NAME,
638 V_INVOICING_ADDRESS
639 FROM hz_parties hp,
640 hz_cust_accounts hca,
641 hz_cust_site_uses hcs
642 WHERE hca.cust_account_id = t.customer_id
643 AND hcs.site_use_id = t.customer_site_use_id
644 AND hca.party_id = hp.party_id;
645 END LOOP; -- END Payment Term Loop
646
647 END IF;
648
649 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_payment_term_loop(-)');
650
651 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_pop_vari(+)');
652
653
654 FOR w IN pitem(V_LEASE_ID)
655 LOOP
656 V_ANNUAL_BASE_RENT:= w.amount;
657 END LOOP;
658
659 FOR x IN pitem1(V_LEASE_ID)
660 LOOP
661 V_DEPOSIT:= x.amount;
662 END LOOP;
663
664 FOR y IN pitem2(V_LEASE_ID)
665 LOOP
666 V_ANNUAL_OPERATING_EXPENSES:= y.amount;
667 END LOOP;
668
669 FOR z IN pitem3(V_LEASE_ID)
670 LOOP
671 V_TOTAL_LEASE_LIABILITY:= z.amount;
672 END LOOP;
673
674 FOR z1 in pitem4(V_LEASE_ID)
675 LOOP
676 V_AMOUNT_EXPORTED:= NVL(z1.amount,0);
677 END LOOP;
678
679 --BUG#2102098 calculate the effective no of months
680
681
682 IF V_PRORATION_RULE = 999 THEN
683 select MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(V_LEASE_TERM_DATE,-1)),LAST_DAY(V_LEASE_COM_DATE)) +
684 ROUND(TO_CHAR(TO_DATE(V_LEASE_TERM_DATE,'DD/MM/YY'),'DD')/TO_CHAR(TO_DATE(LAST_DAY(V_LEASE_TERM_DATE),'DD/MM/YY'),'DD'),3)
685 + ROUND((TO_CHAR(TO_DATE(LAST_DAY(V_LEASE_COM_DATE),'DD/MM/YY'),'DD')-TO_CHAR(TO_DATE(V_LEASE_COM_DATE,'DD/MM/YY'),'DD')+1)/
686 TO_CHAR(TO_DATE(LAST_DAY(V_LEASE_COM_DATE),'DD/MM/YY'),'DD'),3)
687 into v_no_of_months from dual;
688
689 ELSIF V_PRORATION_RULE IN ( 365, 360 ) THEN
690 IF v_lease_term_date = v_lease_com_date THEN
691 SELECT ROUND(1/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
692 ELSIF TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd') =1
693 AND LAST_DAY(v_lease_term_date) = v_lease_term_date THEN
694 SELECT ROUND(MONTHS_BETWEEN(v_lease_term_date,v_lease_com_date),0) INTO v_no_of_months FROM dual;
695 ELSIF TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd') =1 AND
696 TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'mmyy') <TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'mmyy') THEN
697 SELECT ROUND(MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(v_lease_term_date,-1)),v_lease_com_date),0)
698 +ROUND((TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'dd'))/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
699 ELSIF LAST_DAY(v_lease_term_date) =v_lease_term_date AND
700 TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'mmyy') < TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'mmyy') THEN
701 SELECT MONTHS_BETWEEN(v_lease_term_date,LAST_DAY(v_lease_com_date))+
702 ROUND((TO_CHAR(TO_DATE(LAST_DAY(v_lease_com_date),'dd/mm/yy'),'dd')
703 -TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd')+1)/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
704 ELSIF TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'mmyy') =TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'mmyy') THEN
705 SELECT ROUND((TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'dd') - TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd') +1)/
706 (V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
707 ELSE
708 SELECT ROUND(MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(v_lease_term_date,-1)),LAST_DAY(v_lease_com_date)),0)
709 +ROUND((TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'dd'))/(V_PRORATION_RULE/12),3)
710 + ROUND((TO_CHAR(TO_DATE(LAST_DAY(v_lease_com_date),'dd/mm/yy'),'dd')-TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd')+1)
711 /(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
712 END IF;
713
714 END IF;
715
716 V_AVG_ANNUAL_BASE_RENT := ROUND(V_ANNUAL_BASE_RENT *12/V_NO_OF_MONTHS,2) ; --ADDED FOR BUG#2102098
717 V_AVG_ANNUAL_OPERATING_EXP := ROUND(V_ANNUAL_OPERATING_EXPENSES *12/V_NO_OF_MONTHS,2) ; --ADDED FOR BUG#2102098
718 V_AVG_MONTHLY_BASE_RENT := ROUND(V_AVG_ANNUAL_BASE_RENT/12,2); --BUG#2102098
719 V_AVG_MONTHLY_OPERATING_EXP := ROUND(V_AVG_ANNUAL_OPERATING_EXP/12,2); --BUG#2102098
720 IF NVL(V_RENTABLE_AREA,0) <> 0 then
721 V_ANNUAL_RENT_PER_RENT_AREA := TRUNC((V_ANNUAL_BASE_RENT)/(V_RENTABLE_AREA),3);
722 ELSE
723 V_ANNUAL_RENT_PER_RENT_AREA := NULL;
724 END IF;
725
726 v_code_data :=pnp_util_func.get_location_name(V_LOCATION_ID, V_ACTIVE_START_DATE);
727 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_pop_vari(-)');
728 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_insert1(+)');
729
730
731 INSERT INTO pn_rent_roll_lease_exp_itf
732 (LEASE_ID ,
733 LEASE_NAME ,
734 LEASE_NUMBER ,
735 LEASE_COMMENCEMENT_DATE ,
736 LEASE_TERMINATION_DATE ,
737 LEASE_EXECUTION_DATE ,
738 LEASE_TERM ,
739 LEASE_CLASS ,
740 LEASE_RESPONSIBLE_USER ,
741 LEASE_STATUS ,
742 LEASE_TYPE ,
743 ESTIMATED_OCCUPANCY_DATE ,
744 ACTUAL_OCCUPANCY_DATE ,
745 TENANT_NAME ,
746 TENANT_SITE ,
747 LANDLORD_NAME ,
748 LANDLORD_SITE ,
749 ANNUAL_BASE_RENT ,
750 MONTHLY_BASE_RENT ,
751 DEPOSIT ,
752 MONTHLY_OPERATING_EXPENSE ,
753 TOTAL_LEASE_LIABILITY ,
754 REMAINING_LEASE_LIABILITY ,
755 ANNUAL_RENT_PER_RENTABLE_AREA ,
756 INVOICING_ADDRESS ,
757 LOCATION_ID ,
758 LOCATION_TYPE ,
759 LOCATION_NAME ,
760 LOCATION_CODE ,
761 SPACE_TYPE ,
762 REGION ,
763 PROPERTY_NAME ,
764 BUILDING_OR_LAND_NAME ,
765 FLOOR_OR_PARCEL_NAME ,
766 OFFICE_OR_SECTION_NAME ,
767 ADDRESS ,
768 COUNTY ,
769 CITY ,
770 STATE ,
771 PROVINCE ,
772 ZIP_CODE ,
773 COUNTRY ,
774 GROSS_AREA ,
775 RENTABLE_AREA ,
776 USABLE_AREA ,
777 TENURE ,
778 TEN_ATTRIBUTE_CATEGORY ,
779 TEN_ATTRIBUTE1 ,
780 TEN_ATTRIBUTE2 ,
781 TEN_ATTRIBUTE3 ,
782 TEN_ATTRIBUTE4 ,
783 TEN_ATTRIBUTE5 ,
784 TEN_ATTRIBUTE6 ,
785 TEN_ATTRIBUTE7 ,
786 TEN_ATTRIBUTE8 ,
787 TEN_ATTRIBUTE9 ,
788 TEN_ATTRIBUTE10 ,
789 TEN_ATTRIBUTE11 ,
790 TEN_ATTRIBUTE12 ,
791 TEN_ATTRIBUTE13 ,
792 TEN_ATTRIBUTE14 ,
793 TEN_ATTRIBUTE15 ,
794 LAST_UPDATE_DATE ,
795 LAST_UPDATED_BY ,
796 LAST_UPDATE_LOGIN ,
797 CREATION_DATE ,
798 CREATED_BY ,
799 REQUEST_ID )
800 VALUES
801 (V_LEASE_ID ,
802 V_LEASE_NAME ,
803 V_LEASE_NUMBER ,
804 V_LEASE_COM_DATE ,
805 V_LEASE_TERM_DATE ,
806 V_LEASE_EXE_DATE ,
807 V_LEASE_TERM ,
808 V_LEASE_CLASS ,
809 V_LEASE_RESP_USER ,
810 V_LEASE_STATUS ,
811 V_LEASE_TYPE ,
812 V_ESTIMATED_OCCUPANCY_DATE ,
813 V_ACTUAL_OCCUPANCY_DATE ,
814 V_CUSTOMER_NAME , --bug#2099864
815 V_CUSTOMER_SITE , --bug#2099864
816 V_VENDOR_NAME ,
817 V_VENDOR_SITE ,
818 V_AVG_ANNUAL_BASE_RENT , ---BUG#2102098 CHANGED FROM ANNUAL TO AVG
819 V_AVG_MONTHLY_BASE_RENT , --- BUG #2102098 CHANGED FROM ANNUAL TO AVG
820 V_DEPOSIT ,
821 V_AVG_MONTHLY_OPERATING_EXP , ---BUG #2102098 CHANGED FROM ANNUAL TO AVG
822 V_TOTAL_LEASE_LIABILITY ,
823 (V_TOTAL_LEASE_LIABILITY)-(V_AMOUNT_EXPORTED) ,
824 V_ANNUAL_RENT_PER_RENT_AREA ,
825 V_INVOICING_ADDRESS ,
826 V_LOCATION_ID ,
827 V_LOCATION_TYPE ,
828 V_LOCATION_NAME ,
829 V_LOCATION_CODE ,
830 V_SPACE_TYPE ,
831 v_code_data.REGION_NAME ,
832 v_code_data.PROPERTY_NAME ,
833 v_code_data.BUILDING ,
834 v_code_data.FLOOR ,
835 v_code_data.OFFICE ,
836 V_ADDRESS ,
837 V_COUNTY ,
838 V_CITY ,
839 V_STATE ,
840 V_PROVINCE ,
841 V_ZIP_CODE ,
842 V_COUNTRY ,
843 V_GROSS_AREA ,
844 V_RENTABLE_AREA ,
845 V_USABLE_AREA ,
846 V_TENURE ,
847 V_ATTRIBUTE_CATEGORY ,
848 V_ATTRIBUTE1 ,
849 V_ATTRIBUTE2 ,
850 V_ATTRIBUTE3 ,
851 V_ATTRIBUTE4 ,
852 V_ATTRIBUTE5 ,
853 V_ATTRIBUTE6 ,
854 V_ATTRIBUTE7 ,
855 V_ATTRIBUTE8 ,
856 V_ATTRIBUTE9 ,
857 V_ATTRIBUTE10 ,
858 V_ATTRIBUTE11 ,
859 V_ATTRIBUTE12 ,
860 V_ATTRIBUTE13 ,
861 V_ATTRIBUTE14 ,
862 V_ATTRIBUTE15 ,
863 V_LAST_UPDATE_DATE ,
864 V_LAST_UPDATED_BY ,
865 V_LAST_UPDATE_LOGIN ,
866 V_CREATION_DATE ,
867 V_CREATED_BY ,
868 l_request_id );
869
870 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_insert1(-)');
871 END LOOP; --END location loop...
872
873 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_c_loc_pn_loop(-)');
874 END LOOP; --END lease loop...
875 PNP_DEBUG_PKG.put_log_msg('pn_rentroll_c_lease_pn_loop(-)');
876
877 IF dbms_sql.is_open (l_cursor) THEN
878 dbms_sql.close_cursor (l_cursor);
879 END IF;
880
881
882 --IF there is something amiss...
883 EXCEPTION
884 WHEN OTHERS THEN
885 retcode:=2;
886 errbuf:=SUBSTR(SQLERRM,1,235);
887 RAISE;
888 COMMIT;
889 END pn_rent_les;
890 END pnrx_rent_les;