[Home] [Help]
PACKAGE BODY: APPS.PNRX_LEASE_OPTIONS
Source
1 PACKAGE BODY pnrx_lease_options AS
2 /* $Header: PNRXLOPB.pls 120.2 2005/12/01 14:37:22 appldev ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : PN_LEASE_OPTIONS
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_lease_options(
13 lease_number_low IN VARCHAR2,
14 lease_number_high IN VARCHAR2,
15 location_code_low IN VARCHAR2,
16 location_code_high IN VARCHAR2,
17 lease_responsible_user IN VARCHAR2,
18 option_type IN VARCHAR2,
19 exer_window_termination_from IN DATE,
20 exer_window_termination_to IN DATE,
21 lease_termination_from IN DATE,
22 lease_termination_to IN DATE,
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_loc_pn CUR_TYP;
33 c_options_pn CUR_TYP;
34 c_currency_pn CUR_TYP;
35 query_str VARCHAR2(20000);
36 --declare the 'where clauses here........'
37 lease_number_where_clause VARCHAR2(4000);
38 location_code_where_clause VARCHAR2(4000);
39 l_location_date_clause VARCHAR2(4000);
40 les_resp_user_where_clause VARCHAR2(4000);
41 option_type_where_clause VARCHAR2(4000);
42 lease_date_where_clause VARCHAR2(4000);
43 exer_date_where_clause VARCHAR2(4000);
44 l_one NUMBER default 1;
45 l_two NUMBER default 2;
46 l_three NUMBER default 3;
47 l_four NUMBER default 4;
48 l_five NUMBER default 5;
49 l_six NUMBER default 6;
50 --declare all columns as variables here
51 V_LEASE_ID NUMBER;
52 V_LEASE_NAME VARCHAR2(50);
53 V_LEASE_NUMBER VARCHAR2(30);
54 V_LEASE_COM_DATE DATE;
55 V_LEASE_TERM_DATE DATE;
56 V_LEASE_EXE_DATE DATE;
57 V_LEASE_TERM NUMBER;
58 V_LEASE_CLASS VARCHAR2(80);
59 V_LEASE_RESP_USER VARCHAR2(100);
60 V_LEASE_STATUS VARCHAR2(80);
61 V_LEASE_TYPE VARCHAR2(80);
62 V_ESTIMATED_OCCUPANCY_DATE DATE;
63 V_ACTUAL_OCCUPANCY_DATE DATE;
64 V_LOCATION_ID NUMBER;
65 V_LOCATION_ID_1 NUMBER;
66 V_LOCATION_TYPE VARCHAR2(80);
67 V_LOCATION_NAME VARCHAR2(30);
68 V_LOCATION_CODE VARCHAR2(90);
69 V_SPACE_TYPE VARCHAR2(30);
70 V_PROPERTY_CODE VARCHAR2(90);
71 V_REGION_NAME VARCHAR2(50);
72 V_ADDRESS VARCHAR2(1500);
73 V_COUNTY VARCHAR2(60);
74 V_CITY VARCHAR2(60);
75 V_STATE VARCHAR2(60);
76 V_PROVINCE VARCHAR2(60);
77 V_ZIP_CODE VARCHAR2(60);
78 V_COUNTRY VARCHAR2(60);
79 V_RENTABLE_AREA NUMBER;
80 V_USABLE_AREA NUMBER;
81 V_GROSS_AREA NUMBER;
82 V_TENURE VARCHAR2(80);
83 V_OPTION_ID NUMBER;
84 V_OPTION_TYPE VARCHAR2(80);
85 V_OPTION_STATUS VARCHAR2(80);
86 V_OPTION_TERM NUMBER;
87 V_REFERENCE VARCHAR2(15);
88 V_OPTION_NOTICE_REQD VARCHAR2(1);
89 V_OPTION_ACTION_DATE DATE;
90 V_OPTION_SIZE NUMBER;
91 V_OPTION_COST VARCHAR2(15);
92 V_UOM_CODE VARCHAR2(3);
93 V_OPTION_AREA_CHANGE NUMBER;
94 V_OPTION_CURRENCY VARCHAR2(15);
95 V_OPTION_EXER_START_DATE DATE;
96 V_OPTION_EXER_END_DATE DATE;
97 V_OPTION_COMM_DATE DATE;
98 V_OPTION_EXP_DATE DATE;
99 V_OPTION_COMMENTS VARCHAR2(2000);
100 V_ATTRIBUTE_CATEGORY VARCHAR2(30);
101 V_ATTRIBUTE1 VARCHAR2(150);
102 V_ATTRIBUTE2 VARCHAR2(150);
103 V_ATTRIBUTE3 VARCHAR2(150);
104 V_ATTRIBUTE4 VARCHAR2(150);
105 V_ATTRIBUTE5 VARCHAR2(150);
106 V_ATTRIBUTE6 VARCHAR2(150);
107 V_ATTRIBUTE7 VARCHAR2(150);
108 V_ATTRIBUTE8 VARCHAR2(150);
109 V_ATTRIBUTE9 VARCHAR2(150);
110 V_ATTRIBUTE10 VARCHAR2(150);
111 V_ATTRIBUTE11 VARCHAR2(150);
112 V_ATTRIBUTE12 VARCHAR2(150);
113 V_ATTRIBUTE13 VARCHAR2(150);
114 V_ATTRIBUTE14 VARCHAR2(150);
115 V_ATTRIBUTE15 VARCHAR2(150);
116 V_LAST_UPDATE_DATE DATE;
117 V_LAST_UPDATED_BY NUMBER;
118 V_LAST_UPDATE_LOGIN NUMBER;
119 V_CREATION_DATE DATE;
120 V_CREATED_BY NUMBER;
121
122 l_lease_number_low VARCHAR2(30);
123 l_lease_number_high VARCHAR2(30);
124 l_location_code_low VARCHAR2(90);
125 l_location_code_high VARCHAR2(90);
126
127 l_statement VARCHAR2(10000);
128 l_cursor INTEGER;
129 l_rows INTEGER;
130 l_count INTEGER;
131 l_lease_responsible_user VARCHAR2(100);
132 l_option_type VARCHAR2(30);
133 l_exer_window_termination_from DATE;
134 l_exer_window_termination_to DATE;
135 l_lease_termination_from DATE;
136 l_lease_termination_to DATE;
137
138
139
140 --declare the record type for the function here.........
141 v_code_data PNP_UTIL_FUNC.location_name_rec := NULL;
142 -- declare cursors.....
143 CURSOR pterm (V_LEASE_ID IN NUMBER) IS
144 SELECT payment_term_id,start_date,end_date,payment_term_type_code,vendor_id,
145 vendor_site_id, customer_id, customer_site_use_id
146 FROM pn_payment_terms_all
147 WHERE lease_id = V_LEASE_ID
148 AND payment_term_type_code in ('BASE','DEP','OEXP');
149
150
151 CURSOR psched (V_LEASE_ID IN NUMBER) IS
152 SELECT schedule_date, payment_schedule_id
153 FROM pn_payment_schedules_all
154 WHERE lease_id = V_LEASE_ID;
155 -- AND to_char(schedule_date, 'YYYY') = to_char(sysdate, 'YYYY');
156
157
158 CURSOR pitem (V_PAYMENT_TERM_ID IN NUMBER, V_PAYMENT_SCHEDULE_ID IN NUMBER) IS
159 SELECT actual_amount
160 FROM pn_payment_items_all
161 WHERE payment_term_id = V_PAYMENT_TERM_ID
162 AND payment_schedule_id = V_PAYMENT_SCHEDULE_ID
163 AND payment_item_type_lookup_code = 'CASH';
164
165
166 BEGIN
167
168 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_leaseConditions(+)');
169 --Initialise status parameters...
170 retcode:=0;
171 errbuf:='';
172 fnd_profile.get('LOGIN_ID', l_login_id);
173
174 l_cursor := dbms_sql.open_cursor;
175
176 --lease number conditions.....
177 l_lease_number_low := lease_number_low;
178 l_lease_number_high := lease_number_high;
179
180 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
181 lease_number_where_clause := ' AND les.lease_number BETWEEN
182 :l_lease_number_low AND :l_lease_number_high';
183 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
184 lease_number_where_clause := ' AND les.lease_number = :l_lease_number_high';
185 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
186 lease_number_where_clause := ' AND les.lease_number = :l_lease_number_low';
187 ELSE lease_number_where_clause := ' AND 1=1 ';
188 END IF;
189
190 --location code conditions.....
191 l_location_code_low := location_code_low;
192 l_location_code_high := location_code_high;
193
194 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
195 location_code_where_clause := ' WHERE loc.location_code BETWEEN
196 :l_location_code_low AND :l_location_code_high';
197 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
198 location_code_where_clause := ' WHERE loc.location_code =
199 :l_location_code_high';
200 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
201 location_code_where_clause := ' WHERE loc.location_code =
202 :l_location_code_low';
203 ELSE location_code_where_clause := ' WHERE 2=2 ';
204 END IF;
205
206
207 l_lease_responsible_user := lease_responsible_user;
208 --lease responsible user conditions....
209 IF lease_responsible_user IS NOT NULL THEN les_resp_user_where_clause := '
210 AND les.user_responsible = :l_lease_responsible_user';
211 ELSE les_resp_user_where_clause:=' AND 3=3 ';
212 END IF;
213
214
215 --option type conditions....
216 l_option_type := option_type;
217 IF option_type IS NOT NULL THEN option_type_where_clause:= ' AND opt.option_type_code =
218 :l_option_type';
219 ELSE option_type_where_clause:=' AND 4=4 ';
220 END IF;
221
222
223 --exer window termination date conditions.....
224 l_exer_window_termination_from := exer_window_termination_from;
225 l_exer_window_termination_to := exer_window_termination_to;
226 IF exer_window_termination_from IS NOT NULL AND exer_window_termination_to IS NOT NULL THEN
227 exer_date_where_clause := ' AND opt.option_exer_end_date BETWEEN
228 :l_exer_window_termination_from AND :l_exer_window_termination_to';
229 ELSIF exer_window_termination_from IS NULL AND exer_window_termination_to IS NOT NULL THEN
230 exer_date_where_clause := ' AND opt.option_exer_end_date =
231 :l_exer_window_termination_to';
232 ELSIF exer_window_termination_from IS NOT NULL AND exer_window_termination_to IS NULL THEN
233 exer_date_where_clause := ' AND opt.option_exer_end_date =
234 :l_exer_window_termination_from';
235 ELSE exer_date_where_clause := ' AND 5=5 ';
236 END IF;
237
238
239 --lease date conditions.....
240 l_lease_termination_from := lease_termination_from;
241 l_lease_termination_to := lease_termination_to;
242 IF lease_termination_from IS NOT NULL AND lease_termination_to IS NOT NULL THEN
243 lease_date_where_clause := ' AND les.lease_termination_date BETWEEN :l_lease_termination_from
244 AND :l_lease_termination_to';
245 ELSIF lease_termination_from IS NULL AND lease_termination_to IS NOT NULL THEN
246 lease_date_where_clause := ' AND les.lease_termination_date =
247 :l_lease_termination_to';
248 ELSIF lease_termination_from IS NOT NULL AND lease_termination_to IS NULL THEN
249 lease_date_where_clause := ' AND les.lease_termination_date =
250 :l_lease_termination_from';
251 ELSE lease_date_where_clause := ' AND 6=6 ';
252 END IF;
253
254 l_location_date_clause := 'AND ((( loc.active_start_date between les.lease_commencement_date'
255 || ' AND les.lease_Termination_date) OR (loc.active_end_date between'
256 ||' les.lease_commencement_date AND les.lease_Termination_date))'
257 ||' OR ( loc.active_start_date < les.lease_commencement_date '
258 || ' AND loc.active_end_date > les.lease_Termination_date)) ' ;
259
260 --lease cursor.....
261
262 l_statement :=
263 'SELECT
264 distinct
265 ten.location_id LOCATION_ID,
266 ten.tenancy_usage_lookup_code USAGE_TYPE,
267 les.lease_id LEASE_ID,
268 les.lease_name LEASE_NAME,
269 les.lease_number LEASE_NUMBER,
270 les.lease_commencement_date LEASE_COMMENCEMENT_DATE,
271 les.lease_termination_date LEASE_TERMINATION_DATE,
272 les.lease_execution_date LEASE_EXECUTION_DATE,
273 les.lease_term LEASE_TERM,
274 fl1.meaning LEASE_CLASS,
275 les.user_responsible LEASE_RESPONSIBLE_USER,
276 fnd.meaning LEASE_STATUS,
277 fl2.meaning LEASE_TYPE,
278 ten.estimated_occupancy_date ESTIMATED_OCCUPANCY_DATE,
279 ten.occupancy_date OCCUPANCY_DATE,
280 ten.attribute_category ATTRIBUTE_CATEGORY,
281 ten.attribute1 ATTRIBUTE1,
282 ten.attribute2 ATTRIBUTE2,
283 ten.attribute3 ATTRIBUTE3,
284 ten.attribute4 ATTRIBUTE4,
285 ten.attribute5 ATTRIBUTE5,
286 ten.attribute6 ATTRIBUTE6,
287 ten.attribute7 ATTRIBUTE7,
288 ten.attribute8 ATTRIBUTE8,
289 ten.attribute9 ATTRIBUTE9,
290 ten.attribute10 ATTRIBUTE10,
291 ten.attribute11 ATTRIBUTE11,
292 ten.attribute12 ATTRIBUTE12,
293 ten.attribute13 ATTRIBUTE13,
294 ten.attribute14 ATTRIBUTE14,
295 ten.attribute15 ATTRIBUTE15,
296 les.last_update_date LAST_UPDATE_DATE,
297 les.last_updated_by LAST_UPDATED_BY,
298 les.last_update_login LAST_UPDATE_LOGIN,
299 les.creation_date CREATION_DATE,
300 les.created_by CREATED_BY
301 FROM pn_leases_v les,
302 pn_tenancies ten,
303 fnd_lookups fnd,
304 fnd_lookups fl1,
305 fnd_lookups fl2,
306 pn_options opt
307 WHERE ten.lease_id = les.lease_id
308 AND ten.primary_flag ='||''''||'Y'||''''||'
309 AND les.status = fnd.lookup_code
310 AND fnd.lookup_type = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
311 AND fl1.lookup_code = les.lease_class_code
312 AND fl1.lookup_type = '||''''||'PN_LEASE_CLASS'||''''||'
313 AND fl2.lookup_code = les.lease_type_code
314 AND fl2.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
315 AND ten.location_id IN(SELECT loc.location_id FROM pn_locations loc'
316 ||location_code_where_clause|| ' '|| l_location_date_clause|| ')'
317 ||lease_number_where_clause||les_resp_user_where_clause||option_type_where_clause
318 ||exer_date_where_clause||lease_date_where_clause;
319
320
321
322 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
323
324
325 --lease number conditions.....
326 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
327 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
328 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
329 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
330 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
331 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
332 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
333 END IF;
334
335 --location code conditions.....
336 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
337 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
338 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
339 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
340 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
341 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
342 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
343 END IF;
344
345 --lease responsible user conditions....
346 IF lease_responsible_user IS NOT NULL THEN
347 dbms_sql.bind_variable(l_cursor,'l_lease_responsible_user',l_lease_responsible_user);
348 END IF;
349
350 --option type conditions....
351 IF option_type IS NOT NULL THEN
352 dbms_sql.bind_variable(l_cursor,'l_option_type',l_option_type);
353 END IF;
354
355
356 --exer window termination date conditions.....
357 IF exer_window_termination_from IS NOT NULL AND exer_window_termination_to IS NOT NULL THEN
358 dbms_sql.bind_variable(l_cursor,'l_exer_window_termination_from',l_exer_window_termination_from);
359 dbms_sql.bind_variable(l_cursor,'l_exer_window_termination_to',l_exer_window_termination_to);
360 ELSIF exer_window_termination_from IS NULL AND exer_window_termination_to IS NOT NULL THEN
361 dbms_sql.bind_variable(l_cursor,'l_exer_window_termination_to',l_exer_window_termination_to);
362 ELSIF exer_window_termination_from IS NOT NULL AND exer_window_termination_to IS NULL THEN
363 dbms_sql.bind_variable(l_cursor,'l_exer_window_termination_from',l_exer_window_termination_from);
364 END IF;
365
366
367 --lease date conditions.....
368 IF lease_termination_from IS NOT NULL AND lease_termination_to IS NOT NULL THEN
369 dbms_sql.bind_variable(l_cursor,'l_lease_termination_from',l_lease_termination_from);
370 dbms_sql.bind_variable(l_cursor,'l_lease_termination_to',l_lease_termination_to);
371 ELSIF lease_termination_from IS NULL AND lease_termination_to IS NOT NULL THEN
372 dbms_sql.bind_variable(l_cursor,'l_lease_termination_to',l_lease_termination_to);
373 ELSIF lease_termination_from IS NOT NULL AND lease_termination_to IS NULL THEN
374 dbms_sql.bind_variable(l_cursor,'l_lease_termination_from',l_lease_termination_from);
375 END IF;
376
377
378 dbms_sql.define_column (l_cursor,1,V_LOCATION_ID_1);
379 dbms_sql.define_column (l_cursor,2,V_SPACE_TYPE,30);
380 dbms_sql.define_column (l_cursor,3,V_LEASE_ID);
381 dbms_sql.define_column (l_cursor,4,V_LEASE_NAME,50);
382 dbms_sql.define_column (l_cursor,5,V_LEASE_NUMBER,30);
383 dbms_sql.define_column (l_cursor,6,V_LEASE_COM_DATE);
384 dbms_sql.define_column (l_cursor,7,V_LEASE_TERM_DATE);
385 dbms_sql.define_column (l_cursor,8,V_LEASE_EXE_DATE);
386 dbms_sql.define_column (l_cursor,9,V_LEASE_TERM);
387 dbms_sql.define_column (l_cursor,10,V_LEASE_CLASS,80);
388 dbms_sql.define_column (l_cursor,11,V_LEASE_RESP_USER,100);
389 dbms_sql.define_column (l_cursor,12,V_LEASE_STATUS,80);
390 dbms_sql.define_column (l_cursor,13,V_LEASE_TYPE,80);
391 dbms_sql.define_column (l_cursor,14,V_ESTIMATED_OCCUPANCY_DATE);
392 dbms_sql.define_column (l_cursor,15,V_ACTUAL_OCCUPANCY_DATE);
393 dbms_sql.define_column (l_cursor,16,V_ATTRIBUTE_CATEGORY,30);
394 dbms_sql.define_column (l_cursor,17,V_ATTRIBUTE1,150);
395 dbms_sql.define_column (l_cursor,18,V_ATTRIBUTE2,150);
396 dbms_sql.define_column (l_cursor,19,V_ATTRIBUTE3,150);
397 dbms_sql.define_column (l_cursor,20,V_ATTRIBUTE4,150);
398 dbms_sql.define_column (l_cursor,21,V_ATTRIBUTE5,150);
399 dbms_sql.define_column (l_cursor,22,V_ATTRIBUTE6,150);
400 dbms_sql.define_column (l_cursor,23,V_ATTRIBUTE7,150);
401 dbms_sql.define_column (l_cursor,24,V_ATTRIBUTE8,150);
402 dbms_sql.define_column (l_cursor,25,V_ATTRIBUTE9,150);
403 dbms_sql.define_column (l_cursor,26,V_ATTRIBUTE10,150);
404 dbms_sql.define_column (l_cursor,27,V_ATTRIBUTE11,150);
405 dbms_sql.define_column (l_cursor,28,V_ATTRIBUTE12,150);
406 dbms_sql.define_column (l_cursor,29,V_ATTRIBUTE13,150);
407 dbms_sql.define_column (l_cursor,30,V_ATTRIBUTE14,150);
408 dbms_sql.define_column (l_cursor,31,V_ATTRIBUTE15,150);
409 dbms_sql.define_column (l_cursor,32,V_LAST_UPDATE_DATE);
410 dbms_sql.define_column (l_cursor,33,V_LAST_UPDATED_BY);
411 dbms_sql.define_column (l_cursor,34,V_LAST_UPDATE_LOGIN);
412 dbms_sql.define_column (l_cursor,35,V_CREATION_DATE);
413 dbms_sql.define_column (l_cursor,36,V_CREATED_BY);
414
415
416
417 l_rows := dbms_sql.execute(l_cursor);
418
419 PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_lesLoop(+)');
420 LOOP --start lease loop....
421
422 l_count := dbms_sql.fetch_rows( l_cursor );
423
424 EXIT WHEN l_count <> 1;
425
426 dbms_sql.column_value (l_cursor,1,V_LOCATION_ID_1);
427 dbms_sql.column_value (l_cursor,2,V_SPACE_TYPE);
428 dbms_sql.column_value (l_cursor,3,V_LEASE_ID);
429 dbms_sql.column_value (l_cursor,4,V_LEASE_NAME);
430 dbms_sql.column_value (l_cursor,5,V_LEASE_NUMBER);
431 dbms_sql.column_value (l_cursor,6,V_LEASE_COM_DATE);
432 dbms_sql.column_value (l_cursor,7,V_LEASE_TERM_DATE);
433 dbms_sql.column_value (l_cursor,8,V_LEASE_EXE_DATE);
434 dbms_sql.column_value (l_cursor,9,V_LEASE_TERM);
435 dbms_sql.column_value (l_cursor,10,V_LEASE_CLASS);
436 dbms_sql.column_value (l_cursor,11,V_LEASE_RESP_USER);
437 dbms_sql.column_value (l_cursor,12,V_LEASE_STATUS);
438 dbms_sql.column_value (l_cursor,13,V_LEASE_TYPE);
439 dbms_sql.column_value (l_cursor,14,V_ESTIMATED_OCCUPANCY_DATE);
440 dbms_sql.column_value (l_cursor,15,V_ACTUAL_OCCUPANCY_DATE);
441 dbms_sql.column_value (l_cursor,16,V_ATTRIBUTE_CATEGORY);
442 dbms_sql.column_value (l_cursor,17,V_ATTRIBUTE1);
443 dbms_sql.column_value (l_cursor,18,V_ATTRIBUTE2);
444 dbms_sql.column_value (l_cursor,19,V_ATTRIBUTE3);
445 dbms_sql.column_value (l_cursor,20,V_ATTRIBUTE4);
446 dbms_sql.column_value (l_cursor,21,V_ATTRIBUTE5);
447 dbms_sql.column_value (l_cursor,22,V_ATTRIBUTE6);
448 dbms_sql.column_value (l_cursor,23,V_ATTRIBUTE7);
449 dbms_sql.column_value (l_cursor,24,V_ATTRIBUTE8);
450 dbms_sql.column_value (l_cursor,25,V_ATTRIBUTE9);
451 dbms_sql.column_value (l_cursor,26,V_ATTRIBUTE10);
452 dbms_sql.column_value (l_cursor,27,V_ATTRIBUTE11);
453 dbms_sql.column_value (l_cursor,28,V_ATTRIBUTE12);
454 dbms_sql.column_value (l_cursor,29,V_ATTRIBUTE13);
455 dbms_sql.column_value (l_cursor,30,V_ATTRIBUTE14);
456 dbms_sql.column_value (l_cursor,31,V_ATTRIBUTE15);
457 dbms_sql.column_value (l_cursor,32,V_LAST_UPDATE_DATE);
458 dbms_sql.column_value (l_cursor,33,V_LAST_UPDATED_BY);
459 dbms_sql.column_value (l_cursor,34,V_LAST_UPDATE_LOGIN);
460 dbms_sql.column_value (l_cursor,35,V_CREATION_DATE);
461 dbms_sql.column_value (l_cursor,36,V_CREATED_BY);
462
463
464 OPEN c_loc_pn FOR
465 SELECT
466 distinct
467 loc.location_id LOCATION_ID,
468 fl1.meaning LOCATION_TYPE,
469 NVL(NVL(loc.building, loc.FLOOR), loc.office) LOCATION_NAME,
470 loc.location_code LOCATION_CODE,
471 pa.address_line1||pa.address_line2||pa.address_line3||pa.address_line4
472 ||pa.county||pa.city||pa.state||pa.province||pa.zip_code||pa.country ADDRESS,
473 pa.county COUNTY,
474 pa.city CITY,
475 pa.state STATE,
476 pa.province PROVINCE,
477 pa.zip_code ZIP_CODE,
478 pa.country COUNTRY,
479 DECODE (location_type_lookup_code,'OFFICE',loc.rentable_area,
480 'FLOOR',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id, loc.active_start_date),
481 'PARCEL',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id, loc.active_start_date),
482 'BUILDING',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id, loc.active_start_date),
483 'LAND',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id, loc.active_start_date),
484 rentable_area) RENTABLE_AREA,
485 DECODE (location_type_lookup_code,'OFFICE',loc.usable_area,
486 'FLOOR',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id, loc.active_start_date),
487 'PARCEL',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id, loc.active_start_date),
488 'BUILDING',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id, loc.active_start_date),
489 'LAND',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id, loc.active_start_date),
490 usable_area) USABLE_AREA,
491 loc.gross_area GROSS_AREA,
492 fnd.meaning TENURE
493 FROM pn_locations_all loc,
494 pn_addresses_all pa,
495 fnd_lookups fnd,
496 fnd_lookups fl1
497 WHERE pa.address_id(+) = loc.address_id
498 AND loc.lease_or_owned = fnd.lookup_code(+)
499 AND fnd.lookup_type(+) = 'PN_LEASED_OR_OWNED'
500 AND fl1.lookup_code(+) = loc.location_type_lookup_code
501 AND fl1.lookup_type(+) = 'PN_LOCATION_TYPE'
502 AND loc.location_id = V_LOCATION_ID_1;
503
504
505 LOOP --start location loop
506 FETCH c_loc_pn into V_LOCATION_ID ,
507 V_LOCATION_TYPE ,
508 V_LOCATION_NAME ,
509 V_LOCATION_CODE ,
510 V_ADDRESS ,
511 V_COUNTY ,
512 V_CITY ,
513 V_STATE ,
514 V_PROVINCE ,
515 V_ZIP_CODE ,
516 V_COUNTRY ,
517 V_RENTABLE_AREA ,
518 V_USABLE_AREA ,
519 V_GROSS_AREA ,
520 V_TENURE ;
521 EXIT WHEN c_loc_pn%notfound;
522 OPEN c_options_pn FOR
523 SELECT
524 distinct
525 opt.option_id OPTION_ID,
526 fl.meaning OPTION_TYPE,
527 fl1.meaning OPTION_STATUS,
528 (trunc(opt.expiration_date) - trunc(opt.start_date)+1) OPTION_TERM,
529 opt.option_reference REFERENCE,
530 opt.option_notice_reqd OPTION_NOTICE_REQD,
531 opt.option_action_date OPTION_ACTION_DATE,
532 opt.option_size OPTION_SIZE,
533 opt.option_cost OPTION_COST,
534 opt.uom_code UOM_CODE,
535 opt.option_area_change OPTION_AREA_CHANGE,
536 opt.option_exer_start_date OPTION_EXER_START_DATE,
537 opt.option_exer_end_date OPTION_EXER_END_DATE,
538 opt.start_date OPTION_COMM_DATE,
539 opt.expiration_date OPTION_EXP_DATE,
540 opt.option_comments OPTION_COMMENTS
541 FROM pn_options_all opt,
542 fnd_lookups fl,
543 fnd_lookups fl1
544 WHERE opt.lease_id = V_LEASE_ID
545 AND fl.lookup_code(+)= opt.option_type_code
546 AND fl.lookup_type(+)= 'PN_LEASE_OPTION_TYPE'
547 AND fl1.lookup_code(+)= opt.option_status_lookup_code
548 AND fl1.lookup_type(+)= 'PN_OPTION_STATUS_TYPE';
549 LOOP
550 FETCH c_options_pn into V_OPTION_ID,
551 V_OPTION_TYPE,
552 V_OPTION_STATUS,
553 V_OPTION_TERM,
554 V_REFERENCE,
555 V_OPTION_NOTICE_REQD,
556 V_OPTION_ACTION_DATE,
557 V_OPTION_SIZE,
558 V_OPTION_COST,
559 V_UOM_CODE,
560 V_OPTION_AREA_CHANGE,
561 V_OPTION_EXER_START_DATE,
562 V_OPTION_EXER_END_DATE,
563 V_OPTION_COMM_DATE,
564 V_OPTION_EXP_DATE,
565 V_OPTION_COMMENTS;
566
567 EXIT WHEN c_options_pn%notfound;
568
569 OPEN c_currency_pn FOR
570 SELECT currency_code
571 FROM gl_sets_of_books
572 WHERE set_of_books_id = to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
573 pn_mo_cache_utils.get_current_org_id));
574 LOOP
575 FETCH c_currency_pn into V_OPTION_CURRENCY;
576 EXIT WHEN c_currency_pn%notfound;
577 END LOOP;
578
579 v_code_data:=pnp_util_func.get_location_name(V_LOCATION_ID, V_OPTION_COMM_DATE);
580
581 PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_inserting(+)');
582 INSERT INTO pn_lease_options_itf
583 (LEASE_ID ,
584 LEASE_NAME ,
585 LEASE_NUMBER ,
586 LEASE_COMMENCEMENT_DATE ,
587 LEASE_TERMINATION_DATE ,
588 LEASE_EXECUTION_DATE ,
589 LEASE_TERM ,
590 LEASE_CLASS ,
591 LEASE_RESPONSIBLE_USER ,
592 LEASE_STATUS ,
593 LEASE_TYPE ,
594 ESTIMATED_OCCUPANCY_DATE ,
595 ACTUAL_OCCUPANCY_DATE ,
596 LOCATION_ID ,
597 LOCATION_TYPE ,
598 LOCATION_NAME ,
599 LOCATION_CODE ,
600 SPACE_TYPE ,
601 REGION ,
602 PROPERTY_NAME ,
603 BUILDING_OR_LAND_NAME ,
604 FLOOR_OR_PARCEL_NAME ,
605 OFFICE_OR_SECTION_NAME ,
606 ADDRESS ,
607 COUNTY ,
608 CITY ,
609 STATE ,
610 PROVINCE ,
611 ZIP_CODE ,
612 COUNTRY ,
613 GROSS_AREA ,
614 RENTABLE_AREA ,
615 USABLE_AREA ,
616 TENURE ,
617 OPTION_ID ,
618 OPTION_TYPE ,
619 OPTION_STATUS ,
620 OPTION_TERM ,
621 REFERENCE ,
622 OPTION_NOTICE_REQD ,
623 OPTION_ACTION_DATE ,
624 OPTION_SIZE ,
625 OPTION_COST ,
626 UOM_CODE ,
627 OPTION_AREA_CHANGE ,
628 OPTION_CURRENCY ,
629 OPTION_EXER_START_DATE ,
630 OPTION_EXER_END_DATE ,
631 OPTION_COMM_DATE ,
632 OPTION_EXP_DATE ,
633 OPTION_COMMENTS ,
634 TEN_ATTRIBUTE_CATEGORY ,
635 TEN_ATTRIBUTE1 ,
636 TEN_ATTRIBUTE2 ,
637 TEN_ATTRIBUTE3 ,
638 TEN_ATTRIBUTE4 ,
639 TEN_ATTRIBUTE5 ,
640 TEN_ATTRIBUTE6 ,
641 TEN_ATTRIBUTE7 ,
642 TEN_ATTRIBUTE8 ,
643 TEN_ATTRIBUTE9 ,
644 TEN_ATTRIBUTE10 ,
645 TEN_ATTRIBUTE11 ,
646 TEN_ATTRIBUTE12 ,
647 TEN_ATTRIBUTE13 ,
648 TEN_ATTRIBUTE14 ,
649 TEN_ATTRIBUTE15 ,
650 LAST_UPDATE_DATE ,
651 LAST_UPDATED_BY ,
652 LAST_UPDATE_LOGIN ,
653 CREATION_DATE ,
654 CREATED_BY ,
655 REQUEST_ID )
656 VALUES
657 (V_LEASE_ID ,
658 V_LEASE_NAME ,
659 V_LEASE_NUMBER ,
660 V_LEASE_COM_DATE ,
661 V_LEASE_TERM_DATE ,
662 V_LEASE_EXE_DATE ,
663 V_LEASE_TERM ,
664 V_LEASE_CLASS ,
665 V_LEASE_RESP_USER ,
666 V_LEASE_STATUS ,
667 V_LEASE_TYPE ,
668 V_ESTIMATED_OCCUPANCY_DATE ,
669 V_ACTUAL_OCCUPANCY_DATE ,
670 V_LOCATION_ID ,
671 V_LOCATION_TYPE ,
672 V_LOCATION_NAME ,
673 V_LOCATION_CODE ,
674 V_SPACE_TYPE ,
675 v_code_data.REGION_NAME ,
676 v_code_data.PROPERTY_NAME ,
677 v_code_data.BUILDING ,
678 v_code_data.FLOOR ,
679 v_code_data.OFFICE ,
680 V_ADDRESS ,
681 V_COUNTY ,
682 V_CITY ,
683 V_STATE ,
684 V_PROVINCE ,
685 V_ZIP_CODE ,
686 V_COUNTRY ,
687 V_GROSS_AREA ,
688 V_RENTABLE_AREA ,
689 V_USABLE_AREA ,
690 V_TENURE ,
691 V_OPTION_ID ,
692 V_OPTION_TYPE ,
693 V_OPTION_STATUS ,
694 V_OPTION_TERM ,
695 V_REFERENCE ,
696 V_OPTION_NOTICE_REQD ,
697 V_OPTION_ACTION_DATE ,
698 V_OPTION_SIZE ,
699 V_OPTION_COST ,
700 V_UOM_CODE ,
701 V_OPTION_AREA_CHANGE ,
702 V_OPTION_CURRENCY ,
703 V_OPTION_EXER_START_DATE ,
704 V_OPTION_EXER_END_DATE ,
705 V_OPTION_COMM_DATE ,
706 V_OPTION_EXP_DATE ,
707 V_OPTION_COMMENTS ,
708 V_ATTRIBUTE_CATEGORY ,
709 V_ATTRIBUTE1 ,
710 V_ATTRIBUTE2 ,
711 V_ATTRIBUTE3 ,
712 V_ATTRIBUTE4 ,
713 V_ATTRIBUTE5 ,
714 V_ATTRIBUTE6 ,
715 V_ATTRIBUTE7 ,
716 V_ATTRIBUTE8 ,
717 V_ATTRIBUTE9 ,
718 V_ATTRIBUTE10 ,
719 V_ATTRIBUTE11 ,
720 V_ATTRIBUTE12 ,
721 V_ATTRIBUTE13 ,
722 V_ATTRIBUTE14 ,
723 V_ATTRIBUTE15 ,
724 V_LAST_UPDATE_DATE ,
725 V_LAST_UPDATED_BY ,
726 V_LAST_UPDATE_LOGIN ,
727 V_CREATION_DATE ,
728 V_CREATED_BY ,
729 l_request_id );
730 PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_inserting(-)');
731 END LOOP; --end option loop
732 END LOOP; --end location loop...
733 END LOOP; --end lease loop...
734 IF dbms_sql.is_open (l_cursor) THEN
735 dbms_sql.close_cursor (l_cursor);
736 END IF;
737
738 PNP_DEBUG_PKG.put_log_msg('pn_lease_optionsLoop(-)');
739 --If there is something amiss...
740 EXCEPTION
741 WHEN OTHERS THEN
742 retcode:=2;
743 errbuf:=substr(SQLERRM,1,235);
744 RAISE;
745 END pn_lease_options;
746 END pnrx_lease_options;