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