[Home] [Help]
PACKAGE BODY: APPS.PNRX_SP_UTIL_BY_LEASE
Source
1 PACKAGE BODY pnrx_sp_util_by_lease AS
2 /* $Header: PNRXSLUB.pls 120.2 2005/12/01 15:03:52 appldev ship $ */
3
4 ---------------------------------------------------------------------------------
5 -- PROCDURE : PN_SPACE_UTIL_LEASE
6 -- INVOKED FROM :
7 -- PURPOSE :
8 -- HISTORY :
9 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_locations,pn_tenancies
10 -- with _ALL table
11 -- 24-OCT-05 Hareesha o ATG mandated changes for SQL literals using dbms_sql.
12 ----------------------------------------------------------------------------------
13
14 PROCEDURE pn_space_util_lease(
15 lease_number_low IN VARCHAR2,
16 lease_number_high IN VARCHAR2,
17 as_of_date IN DATE,
18 l_request_id IN NUMBER,
19 l_user_id IN NUMBER,
20 retcode OUT NOCOPY VARCHAR2,
21 errbuf OUT NOCOPY VARCHAR2
22 )
23 IS
24 l_login_id NUMBER;
25 --declare the 'where clauses here........'
26 lease_number_where_clause VARCHAR2(4000);
27 l_one NUMBER default 1;
28 --declare all columns as variables here
29 V_LEASE_ID NUMBER;
30 V_LEASE_NAME VARCHAR2(50);
31 V_LEASE_NUMBER VARCHAR2(30);
32 V_LEASE_COM_DATE DATE;
33 V_LEASE_TERM_DATE DATE;
34 V_LOCATION_ID NUMBER;
35 V_LOCATION_ID_1 NUMBER;
36 V_LOCATION_TYPE VARCHAR2(80);
37 V_LOCATION_NAME VARCHAR2(30);
38 V_LOCATION_CODE VARCHAR2(90);
39 V_RENTABLE_AREA NUMBER;
40 V_USABLE_AREA NUMBER;
41 V_PROPERTY_CODE VARCHAR2(90);
42 V_ASSIGNABLE_AREA NUMBER;
43 V_COMMON_AREA NUMBER;
44 V_MAXIMUM_OCCUPANCY NUMBER;
45 V_OPTIMUM_OCCUPANCY NUMBER;
46 V_MAXIMUM_VACANCY NUMBER;
47 V_OPTIMUM_VACANCY NUMBER;
48 V_USAGE_TYPE VARCHAR2(80);
49 V_VACANT_AREA NUMBER;
50 V_ASSIGNED_AREA NUMBER;
51 V_UTILIZED NUMBER;
52 V_EMP_START_DATE DATE;
53 V_EMP_END_DATE DATE;
54 V_CUST_START_DATE DATE;
55 V_CUST_END_DATE DATE;
56 V_ATTRIBUTE_CATEGORY VARCHAR2(30);
57 V_ATTRIBUTE1 VARCHAR2(150);
58 V_ATTRIBUTE2 VARCHAR2(150);
59 V_ATTRIBUTE3 VARCHAR2(150);
60 V_ATTRIBUTE4 VARCHAR2(150);
61 V_ATTRIBUTE5 VARCHAR2(150);
62 V_ATTRIBUTE6 VARCHAR2(150);
63 V_ATTRIBUTE7 VARCHAR2(150);
64 V_ATTRIBUTE8 VARCHAR2(150);
65 V_ATTRIBUTE9 VARCHAR2(150);
66 V_ATTRIBUTE10 VARCHAR2(150);
67 V_ATTRIBUTE11 VARCHAR2(150);
68 V_ATTRIBUTE12 VARCHAR2(150);
69 V_ATTRIBUTE13 VARCHAR2(150);
70 V_ATTRIBUTE14 VARCHAR2(150);
71 V_ATTRIBUTE15 VARCHAR2(150);
72 V_LAST_UPDATE_DATE DATE;
73 V_LAST_UPDATED_BY NUMBER;
74 V_LAST_UPDATE_LOGIN NUMBER;
75 V_CREATION_DATE DATE;
76 V_CREATED_BY NUMBER;
77 V_REQUEST_ID NUMBER;
78 l_cursor INTEGER;
79 l_statement VARCHAR2(10000);
80 l_rows INTEGER;
81 l_count INTEGER;
82 l_lease_number_low VARCHAR2(30);
83 l_lease_number_high VARCHAR2(30);
84 l_as_of_date DATE;
85 l_cursor_2 INTEGER;
86 l_statement_2 VARCHAR2(10000);
87 l_rows_2 INTEGER;
88 l_count_2 INTEGER;
89 l_location_id_1 NUMBER;
90 l_cursor_3 INTEGER;
91 l_statement_3 VARCHAR2(10000);
92 l_rows_3 INTEGER;
93 l_count_3 INTEGER;
94
95 v_code_data PNP_UTIL_FUNC.location_name_rec := NULL;
96
97 v_compare BOOLEAN;
98
99
100 BEGIN
101 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_leaseConditions(+)');
102 --Initialise status parameters...
103 retcode:=0;
104 errbuf:='';
105 fnd_profile.get('LOGIN_ID', l_login_id);
106
107 l_cursor := dbms_sql.open_cursor;
108 l_statement :=
109 'SELECT
110 loc.location_id LOCATION_ID
111 FROM pn_locations_all loc,
112 pn_tenancies ten,
113 pn_leases_all les
114 WHERE ten.location_id = loc.location_id
115 AND ten.lease_id = les.lease_id ' ;
116
117
118 l_as_of_date := as_of_date;
119 l_statement :=
120 l_statement || ' AND loc.active_start_date <= :l_as_of_date AND loc.active_end_date >= :l_as_of_date ';
121
122 --lease number conditions.....
123 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
124 l_lease_number_low := lease_number_low;
125 l_lease_number_high := lease_number_high;
126 lease_number_where_clause :=
127 lease_number_where_clause || ' AND les.lease_num BETWEEN :l_lease_number_low AND :l_lease_number_high ';
128
129 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
130 l_lease_number_high := lease_number_high;
131 lease_number_where_clause :=
132 lease_number_where_clause || ' AND les.lease_num = :l_lease_number_high ';
133
134 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
135 l_lease_number_low := lease_number_low;
136 lease_number_where_clause :=
137 lease_number_where_clause || ' AND les.lease_num = :l_lease_number_low ';
138
139 ELSE
140 lease_number_where_clause :=
141 lease_number_where_clause || ' AND 1 = 1 ';
142
143 END IF;
144
145 l_statement :=
146 l_statement || lease_number_where_clause ;
147
148 pnp_debug_pkg.log(' l_statement:'||l_statement);
149 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
150
151 dbms_sql.bind_variable
152 (l_cursor,'l_as_of_date',l_as_of_date );
153
154 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
155 dbms_sql.bind_variable
156 (l_cursor,'l_lease_number_low',l_lease_number_low );
157 dbms_sql.bind_variable
158 (l_cursor,'l_lease_number_high',l_lease_number_high );
159
160 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
161 dbms_sql.bind_variable
162 (l_cursor,'l_lease_number_high',l_lease_number_high );
163 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
164 dbms_sql.bind_variable
165 (l_cursor,'l_lease_number_low',l_lease_number_low );
166 END IF;
167
168 dbms_sql.define_column (l_cursor, 1,V_LOCATION_ID_1);
169 l_rows := dbms_sql.execute(l_cursor);
170
171 LOOP
172
173 l_count := dbms_sql.fetch_rows( l_cursor );
174 EXIT WHEN l_count <> 1;
175
176 dbms_sql.column_value (l_cursor, 1,V_LOCATION_ID_1);
177
178 l_cursor_2 := dbms_sql.open_cursor;
179 l_statement_2 :=
180 'SELECT distinct
181 loc.location_id LOCATION_ID,
182 fl1.meaning LOCATION_TYPE,
183 NVL(NVL(loc.building, loc.floor), loc.office) LOCATION_NAME,
184 loc.location_code LOCATION_CODE,
185 DECODE (location_type_lookup_code,''OFFICE'',loc.rentable_area,
186 ''FLOOR'',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id,:l_as_of_date),
187 ''PARCEL'',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id,:l_as_of_date),
188 ''BUILDING'',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id,:l_as_of_date),
189 ''LAND'',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id,:l_as_of_date),
190 rentable_area) RENTABLE_AREA,
191 DECODE (location_type_lookup_code,''OFFICE'',loc.usable_area,
192 ''FLOOR'',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id,:l_as_of_date),
193 ''PARCEL'',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id,:l_as_of_date),
194 ''BUILDING'',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id,:l_as_of_date),
195 ''LAND'',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id,:l_as_of_date),
196 usable_area) USABLE_AREA,
197 DECODE (location_type_lookup_code,''OFFICE'',loc.assignable_area,
198 ''FLOOR'',PNP_UTIL_FUNC.get_floor_assignable_area(loc.location_id,:l_as_of_date),
199 ''PARCEL'',PNP_UTIL_FUNC.get_floor_assignable_area(loc.location_id,:l_as_of_date),
200 ''BUILDING'',PNP_UTIL_FUNC.get_building_assignable_area(loc.location_id,:l_as_of_date),
201 ''LAND'',PNP_UTIL_FUNC.get_building_assignable_area(loc.location_id,:l_as_of_date),
202 assignable_area) ASSIGNABLE_AREA,
203 DECODE (location_type_lookup_code,''OFFICE'',loc.common_area,
204 ''FLOOR'',PNP_UTIL_FUNC.get_floor_common_area(loc.location_id,:l_as_of_date),
205 ''PARCEL'',PNP_UTIL_FUNC.get_floor_common_area(loc.location_id,:l_as_of_date),
206 ''BUILDING'',PNP_UTIL_FUNC.get_building_common_area(loc.location_id,:l_as_of_date),
207 ''LAND'',PNP_UTIL_FUNC.get_building_common_area(loc.location_id,:l_as_of_date),
208 common_area) COMMON_AREA,
209 DECODE (location_type_lookup_code,''OFFICE'',loc.max_capacity,
210 ''FLOOR'',PNP_UTIL_FUNC.get_floor_max_capacity(loc.location_id,:l_as_of_date),
211 ''PARCEL'',PNP_UTIL_FUNC.get_floor_max_capacity(loc.location_id,:l_as_of_date),
212 ''BUILDING'',PNP_UTIL_FUNC.get_building_max_capacity(loc.location_id,:l_as_of_date),
213 ''LAND'',PNP_UTIL_FUNC.get_building_max_capacity(loc.location_id,:l_as_of_date),
214 max_capacity) MAXIMUM_OCCUPANCY,
215 DECODE (location_type_lookup_code,''OFFICE'',loc.optimum_capacity,
216 ''FLOOR'',PNP_UTIL_FUNC.get_floor_optimum_capacity(loc.location_id,:l_as_of_date),
217 ''PARCEL'',PNP_UTIL_FUNC.get_floor_optimum_capacity(loc.location_id,:l_as_of_date),
218 ''BUILDING'',PNP_UTIL_FUNC.get_building_optimum_capacity(loc.location_id,:l_as_of_date),
219 ''LAND'',PNP_UTIL_FUNC.get_building_optimum_capacity(loc.location_id,:l_as_of_date),
220 optimum_capacity) OPTIMUM_OCCUPANCY,
221 fl.meaning USAGE_TYPE,
222 loc.attribute_category ATTRIBUTE_CATEGORY,
223 loc.attribute1 ATTRIBUTE1,
224 loc.attribute2 ATTRIBUTE2,
225 loc.attribute3 ATTRIBUTE3,
226 loc.attribute4 ATTRIBUTE4,
227 loc.attribute5 ATTRIBUTE5,
228 loc.attribute6 ATTRIBUTE6,
229 loc.attribute7 ATTRIBUTE7,
230 loc.attribute8 ATTRIBUTE8,
231 loc.attribute9 ATTRIBUTE9,
232 loc.attribute10 ATTRIBUTE10,
233 loc.attribute11 ATTRIBUTE11,
234 loc.attribute12 ATTRIBUTE12,
235 loc.attribute13 ATTRIBUTE13,
236 loc.attribute14 ATTRIBUTE14,
237 loc.attribute15 ATTRIBUTE15,
238 loc.last_update_date LAST_UPDATE_DATE,
239 loc.last_updated_by LAST_UPDATED_BY,
240 loc.last_update_login LAST_UPDATE_LOGIN,
241 loc.creation_date CREATION_DATE,
242 loc.created_by CREATED_BY
243 FROM pn_locations_all loc,
244 fnd_lookups fl,
245 fnd_lookups fl1
246 WHERE fl.lookup_code(+) = loc.space_type_lookup_code
247 AND fl.lookup_type(+) = ''PN_SPACE_TYPE''
248 AND fl1.lookup_code(+)= loc.location_type_lookup_code
249 AND fl1.lookup_type(+)= ''PN_LOCATION_TYPE''
250 AND loc.location_id IN (SELECT loc.location_id FROM pn_locations_all loc
251 WHERE loc.active_start_date <= :l_as_of_date
252 AND loc.active_end_date >= :l_as_of_date
253 START WITH loc.location_id = :l_LOCATION_ID_1
254 CONNECT BY PRIOR loc.location_id = loc.parent_location_id
255 AND :l_as_of_date BETWEEN prior active_start_date AND prior active_end_date) ';
256
257 dbms_sql.parse(l_cursor_2, l_statement_2, dbms_sql.native);
258
259 dbms_sql.bind_variable
260 (l_cursor_2,'l_as_of_date',l_as_of_date );
261 dbms_sql.bind_variable
262 (l_cursor_2,'l_LOCATION_ID_1',v_LOCATION_ID_1 );
263
264 dbms_sql.define_column (l_cursor_2, 1,V_LOCATION_ID);
265 dbms_sql.define_column (l_cursor_2, 2,V_LOCATION_TYPE,80);
266 dbms_sql.define_column (l_cursor_2, 3,V_LOCATION_NAME,30);
267 dbms_sql.define_column (l_cursor_2, 4,V_LOCATION_CODE,90);
268 dbms_sql.define_column (l_cursor_2, 5,V_RENTABLE_AREA);
269 dbms_sql.define_column (l_cursor_2, 6,V_USABLE_AREA);
270 dbms_sql.define_column (l_cursor_2, 7,V_ASSIGNABLE_AREA);
271 dbms_sql.define_column (l_cursor_2, 8,V_COMMON_AREA);
272 dbms_sql.define_column (l_cursor_2, 9,V_MAXIMUM_OCCUPANCY);
273 dbms_sql.define_column (l_cursor_2, 10,V_OPTIMUM_OCCUPANCY);
274 dbms_sql.define_column (l_cursor_2, 11,V_USAGE_TYPE,80);
275 dbms_sql.define_column (l_cursor_2, 12,V_ATTRIBUTE_CATEGORY,30);
276 dbms_sql.define_column (l_cursor_2, 13,V_ATTRIBUTE1,150);
277 dbms_sql.define_column (l_cursor_2, 14,V_ATTRIBUTE2,150);
278 dbms_sql.define_column (l_cursor_2, 15,V_ATTRIBUTE3,150);
279 dbms_sql.define_column (l_cursor_2, 16,V_ATTRIBUTE4,150);
280 dbms_sql.define_column (l_cursor_2, 17,V_ATTRIBUTE5,150);
281 dbms_sql.define_column (l_cursor_2, 18,V_ATTRIBUTE6,150);
282 dbms_sql.define_column (l_cursor_2, 19,V_ATTRIBUTE7,150);
283 dbms_sql.define_column (l_cursor_2, 20,V_ATTRIBUTE8,150);
284 dbms_sql.define_column (l_cursor_2, 21,V_ATTRIBUTE9,150);
285 dbms_sql.define_column (l_cursor_2, 22,V_ATTRIBUTE10,150);
286 dbms_sql.define_column (l_cursor_2, 23,V_ATTRIBUTE11,150);
287 dbms_sql.define_column (l_cursor_2, 24,V_ATTRIBUTE12,150);
288 dbms_sql.define_column (l_cursor_2, 25,V_ATTRIBUTE13,150);
289 dbms_sql.define_column (l_cursor_2, 26,V_ATTRIBUTE14,150);
290 dbms_sql.define_column (l_cursor_2, 27,V_ATTRIBUTE15,150);
291 dbms_sql.define_column (l_cursor_2, 28,V_LAST_UPDATE_DATE);
292 dbms_sql.define_column (l_cursor_2, 29,V_LAST_UPDATED_BY);
293 dbms_sql.define_column (l_cursor_2, 30,V_LAST_UPDATE_LOGIN);
294 dbms_sql.define_column (l_cursor_2, 31,V_CREATION_DATE);
295 dbms_sql.define_column (l_cursor_2, 32,V_CREATED_BY);
296
297 l_rows_2 := dbms_sql.execute(l_cursor_2);
298
299 LOOP
300
301 l_count_2 := dbms_sql.fetch_rows( l_cursor_2 );
302 EXIT WHEN l_count_2 <> 1;
303
304 dbms_sql.column_value (l_cursor_2, 1,V_LOCATION_ID);
305 dbms_sql.column_value (l_cursor_2, 2,V_LOCATION_TYPE);
306 dbms_sql.column_value (l_cursor_2, 3,V_LOCATION_NAME);
307 dbms_sql.column_value (l_cursor_2, 4,V_LOCATION_CODE);
308 dbms_sql.column_value (l_cursor_2, 5,V_RENTABLE_AREA);
309 dbms_sql.column_value (l_cursor_2, 6,V_USABLE_AREA);
310 dbms_sql.column_value (l_cursor_2, 7,V_ASSIGNABLE_AREA);
311 dbms_sql.column_value (l_cursor_2, 8,V_COMMON_AREA);
312 dbms_sql.column_value (l_cursor_2, 9,V_MAXIMUM_OCCUPANCY);
313 dbms_sql.column_value (l_cursor_2, 10,V_OPTIMUM_OCCUPANCY);
314 dbms_sql.column_value (l_cursor_2, 11,V_USAGE_TYPE);
315 dbms_sql.column_value (l_cursor_2, 12,V_ATTRIBUTE_CATEGORY);
316 dbms_sql.column_value (l_cursor_2, 13,V_ATTRIBUTE1);
317 dbms_sql.column_value (l_cursor_2, 14,V_ATTRIBUTE2);
318 dbms_sql.column_value (l_cursor_2, 15,V_ATTRIBUTE3);
319 dbms_sql.column_value (l_cursor_2, 16,V_ATTRIBUTE4);
320 dbms_sql.column_value (l_cursor_2, 17,V_ATTRIBUTE5);
321 dbms_sql.column_value (l_cursor_2, 18,V_ATTRIBUTE6);
322 dbms_sql.column_value (l_cursor_2, 19,V_ATTRIBUTE7);
323 dbms_sql.column_value (l_cursor_2, 20,V_ATTRIBUTE8);
324 dbms_sql.column_value (l_cursor_2, 21,V_ATTRIBUTE9);
325 dbms_sql.column_value (l_cursor_2, 22,V_ATTRIBUTE10);
326 dbms_sql.column_value (l_cursor_2, 23,V_ATTRIBUTE11);
327 dbms_sql.column_value (l_cursor_2, 24,V_ATTRIBUTE12);
328 dbms_sql.column_value (l_cursor_2, 25,V_ATTRIBUTE13);
329 dbms_sql.column_value (l_cursor_2, 26,V_ATTRIBUTE14);
330 dbms_sql.column_value (l_cursor_2, 27,V_ATTRIBUTE15);
331 dbms_sql.column_value (l_cursor_2, 28,V_LAST_UPDATE_DATE);
332 dbms_sql.column_value (l_cursor_2, 29,V_LAST_UPDATED_BY);
333 dbms_sql.column_value (l_cursor_2, 30,V_LAST_UPDATE_LOGIN);
334 dbms_sql.column_value (l_cursor_2, 31,V_CREATION_DATE);
335 dbms_sql.column_value (l_cursor_2, 32,V_CREATED_BY);
336
337 l_cursor_3 := dbms_sql.open_cursor;
338 l_statement_3 :=
339 'SELECT
340 distinct
341 les.lease_id LEASE_ID,
342 les.name LEASE_NAME,
343 les.lease_num LEASE_NUMBER,
344 lda.lease_commencement_date LEASE_COMMENCEMENT_DATE,
345 lda.lease_termination_date LEASE_TERMINATION_DATE,
346 p.property_code PROPERTY_CODE
347 FROM pn_locations_all loc,
348 pn_properties_all p,
349 pn_leases les,
350 pn_lease_details_all lda,
351 pn_tenancies_all ten
352 WHERE les.lease_id = ten.lease_id
353 AND lda.lease_id = ten.lease_id
354 AND ten.location_id = loc.location_id
355 AND loc.location_id IN (SELECT distinct loc.location_id FROM pn_locations_all loc
356 START WITH loc.location_id =:l_LOCATION_ID_1
357 CONNECT BY PRIOR loc.location_id = loc.parent_location_id)
358 AND p.property_id(+) = loc.property_id';
359
360 l_statement_3 := l_statement_3 || lease_number_where_clause ;
361
362 dbms_sql.parse(l_cursor_3, l_statement_3, dbms_sql.native);
363
364 dbms_sql.bind_variable
365 (l_cursor_3,'l_LOCATION_ID_1',v_LOCATION_ID_1 );
366
367 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
368 dbms_sql.bind_variable
369 (l_cursor_3,'l_lease_number_low',l_lease_number_low );
370 dbms_sql.bind_variable
371 (l_cursor_3,'l_lease_number_high',l_lease_number_high );
372
373 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
374 dbms_sql.bind_variable
375 (l_cursor_3,'l_lease_number_high',l_lease_number_high );
376 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
377 dbms_sql.bind_variable
378 (l_cursor_3,'l_lease_number_low',l_lease_number_low );
379 END IF;
380
381 dbms_sql.define_column (l_cursor_3, 1,V_LEASE_ID);
382 dbms_sql.define_column (l_cursor_3, 2,V_LEASE_NAME,50);
383 dbms_sql.define_column (l_cursor_3, 3,V_LEASE_NUMBER,30);
384 dbms_sql.define_column (l_cursor_3, 4,V_LEASE_COM_DATE);
385 dbms_sql.define_column (l_cursor_3, 5,V_LEASE_TERM_DATE);
386 dbms_sql.define_column (l_cursor_3, 6,V_PROPERTY_CODE,90);
387
388 l_rows_3 := dbms_sql.execute(l_cursor_3);
389
390 LOOP
391
392 l_count_3 := dbms_sql.fetch_rows( l_cursor_3 );
393
394 EXIT WHEN l_count_3 <> 1;
395
396 dbms_sql.column_value (l_cursor_3, 1,V_LEASE_ID);
397 dbms_sql.column_value (l_cursor_3, 2,V_LEASE_NAME);
398 dbms_sql.column_value (l_cursor_3, 3,V_LEASE_NUMBER);
399 dbms_sql.column_value (l_cursor_3, 4,V_LEASE_COM_DATE);
400 dbms_sql.column_value (l_cursor_3, 5,V_LEASE_TERM_DATE);
401 dbms_sql.column_value (l_cursor_3, 6,V_PROPERTY_CODE);
402
403 v_code_data:=pnp_util_func.get_location_name(V_LOCATION_ID, as_of_date);
404 INSERT INTO pn_space_util_lease_itf
405 (LEASE_ID ,
406 LEASE_NAME ,
407 LEASE_NUMBER ,
408 LEASE_COMMENCEMENT_DATE ,
409 LEASE_TERMINATION_DATE ,
410 LOCATION_ID ,
411 LOCATION_TYPE ,
412 LOCATION_NAME ,
413 LOCATION_CODE ,
414 RENTABLE_AREA ,
415 USABLE_AREA ,
416 ASSIGNABLE_AREA ,
417 COMMON_AREA ,
418 MAXIMUM_OCCUPANCY ,
419 OPTIMUM_OCCUPANCY ,
420 MAXIMUM_VACANCY ,
421 OPTIMUM_VACANCY ,
422 USAGE_TYPE ,
423 VACANT_AREA ,
424 ASSIGNED_AREA ,
425 UTILIZED ,
426 PROPERTY_CODE ,
427 PROPERTY_NAME ,
428 BUILDING_LOCATION_CODE ,
429 BUILDING_OR_LAND_NAME ,
430 FLOOR_LOCATION_CODE ,
431 FLOOR_OR_PARCEL_NAME ,
432 OFFICE_LOCATION_CODE ,
433 OFFICE_OR_SECTION_NAME ,
434 LOC_ATTRIBUTE_CATEGORY ,
435 LOC_ATTRIBUTE1 ,
436 LOC_ATTRIBUTE2 ,
437 LOC_ATTRIBUTE3 ,
438 LOC_ATTRIBUTE4 ,
439 LOC_ATTRIBUTE5 ,
440 LOC_ATTRIBUTE6 ,
441 LOC_ATTRIBUTE7 ,
442 LOC_ATTRIBUTE8 ,
443 LOC_ATTRIBUTE9 ,
444 LOC_ATTRIBUTE10 ,
445 LOC_ATTRIBUTE11 ,
446 LOC_ATTRIBUTE12 ,
447 LOC_ATTRIBUTE13 ,
448 LOC_ATTRIBUTE14 ,
449 LOC_ATTRIBUTE15 ,
450 LAST_UPDATE_DATE ,
451 LAST_UPDATED_BY ,
452 LAST_UPDATE_LOGIN ,
453 CREATION_DATE ,
454 CREATED_BY ,
455 REQUEST_ID )
456 VALUES
457 (V_LEASE_ID ,
458 V_LEASE_NAME ,
459 V_LEASE_NUMBER ,
460 V_LEASE_COM_DATE ,
461 V_LEASE_TERM_DATE ,
462 V_LOCATION_ID ,
463 V_LOCATION_TYPE ,
464 V_LOCATION_NAME ,
465 V_LOCATION_CODE ,
466 V_RENTABLE_AREA ,
467 V_USABLE_AREA ,
468 V_ASSIGNABLE_AREA ,
469 V_COMMON_AREA ,
470 V_MAXIMUM_OCCUPANCY ,
471 V_OPTIMUM_OCCUPANCY ,
472 decode(SIGN(V_MAXIMUM_OCCUPANCY-pnp_util_func.get_utilized_capacity(V_LOCATION_ID, as_of_date)),-1,0,V_MAXIMUM_OCCUPANCY-pnp_util_func.get_utilized_capacity(V_LOCATION_ID, as_of_date)),
473 decode(SIGN(V_OPTIMUM_OCCUPANCY-pnp_util_func.get_utilized_capacity(V_LOCATION_ID, as_of_date)),-1,0,V_OPTIMUM_OCCUPANCY-pnp_util_func.get_utilized_capacity(V_LOCATION_ID, as_of_date)),
474 V_USAGE_TYPE ,
475 pnp_util_func.get_vacant_area(V_LOCATION_ID, as_of_date),
476 V_ASSIGNABLE_AREA-pnp_util_func.get_vacant_area(V_LOCATION_ID, as_of_date),
477 pnp_util_func.get_utilized_capacity(V_LOCATION_ID, as_of_date),
478 v_code_data.PROPERTY_CODE ,
479 v_code_data.PROPERTY_NAME ,
480 v_code_data.BUILDING_LOCATION_CODE ,
481 v_code_data.BUILDING ,
482 v_code_data.FLOOR_LOCATION_CODE ,
483 v_code_data.FLOOR ,
484 v_code_data.OFFICE_LOCATION_CODE ,
485 v_code_data.OFFICE ,
486 V_ATTRIBUTE_CATEGORY ,
487 V_ATTRIBUTE1 ,
488 V_ATTRIBUTE2 ,
489 V_ATTRIBUTE3 ,
490 V_ATTRIBUTE4 ,
491 V_ATTRIBUTE5 ,
492 V_ATTRIBUTE6 ,
493 V_ATTRIBUTE7 ,
494 V_ATTRIBUTE8 ,
495 V_ATTRIBUTE9 ,
496 V_ATTRIBUTE10 ,
497 V_ATTRIBUTE11 ,
498 V_ATTRIBUTE12 ,
499 V_ATTRIBUTE13 ,
500 V_ATTRIBUTE14 ,
501 V_ATTRIBUTE15 ,
502 V_LAST_UPDATE_DATE ,
503 V_LAST_UPDATED_BY ,
504 V_LAST_UPDATE_LOGIN ,
505 V_CREATION_DATE ,
506 V_CREATED_BY ,
507 l_request_id );
508
509 END LOOP;
510
511 IF dbms_sql.is_open (l_cursor_3) THEN
512 dbms_sql.close_cursor (l_cursor_3);
513 END IF;
514
515 END LOOP;
516
517 IF dbms_sql.is_open (l_cursor_2) THEN
518 dbms_sql.close_cursor (l_cursor_2);
519 END IF;
520
521 END LOOP;
522
523 IF dbms_sql.is_open (l_cursor) THEN
524 dbms_sql.close_cursor (l_cursor);
525 END IF;
526
527 COMMIT;
528
529 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locLoop(-)');
530 --If there is something amiss...
531 EXCEPTION
532 WHEN OTHERS THEN
533 retcode:=2;
534 errbuf:=SUBSTR(SQLERRM,1,235);
535
536 RAISE;
537 COMMIT;
538 END pn_space_util_lease;
539 END pnrx_sp_util_by_lease;