[Home] [Help]
PACKAGE BODY: APPS.PNRX_MILESTONES
Source
1 PACKAGE BODY pnrx_milestones AS
2 /* $Header: PNRXMSTB.pls 120.3 2006/06/16 01:44:01 kkhegde ship $ */
3
4
5 -------------------------------------------------------------------------------
6 -- PROCDURE : PN_MILESTONES
7 -- INVOKED FROM :
8 -- PURPOSE :
9 -- HISTORY :
10 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_distributions with _ALL.
11 -- 21-OCT-05 Hareesha o ATG mandated changes for SQL literals using dbms_sql.
12 -- 16-Jun-06 Kiran o Bug # 5334793 - removed stray ) from l_statement that
13 -- was causing the query to fail.
14 -------------------------------------------------------------------------------
15 PROCEDURE pn_milestones(
16 lease_number_low IN VARCHAR2,
17 lease_number_high IN VARCHAR2,
18 location_code_low IN VARCHAR2,
19 location_code_high IN VARCHAR2,
20 lease_termination_from IN DATE,
21 lease_termination_to IN DATE,
22 responsible_user IN VARCHAR2,
23 action_due_date_from IN DATE,
24 action_due_date_to IN DATE,
25 milestone_type IN VARCHAR2,
26 l_request_id IN NUMBER,
27 l_user_id IN NUMBER,
28 retcode OUT NOCOPY VARCHAR2,
29 errbuf OUT NOCOPY VARCHAR2
30 )
31 IS
32 l_login_id NUMBER;
33 l_one NUMBER default 1;
34 l_two NUMBER default 2;
35 l_three NUMBER default 3;
36 l_four NUMBER default 4;
37 l_five NUMBER default 5;
38 l_six NUMBER default 6;
39 --declare all columns as variables here
40 V_LEASE_ID NUMBER;
41 V_LEASE_NAME VARCHAR2(50);
42 V_LEASE_NUMBER VARCHAR2(30);
43 V_LEASE_COM_DATE DATE;
44 V_LEASE_TERM_DATE DATE;
45 V_LEASE_EXE_DATE DATE;
46 V_LEASE_TERM NUMBER;
47 V_LEASE_CLASS VARCHAR2(80);
48 V_LEASE_RESP_USER VARCHAR2(100);
49 V_LEASE_STATUS VARCHAR2(80);
50 V_LEASE_TYPE VARCHAR2(80);
51 V_ESTIMATED_OCCUPANCY_DATE DATE;
52 V_ACTUAL_OCCUPANCY_DATE DATE;
53 V_ATTRIBUTE_CATEGORY VARCHAR2(30);
54 V_ATTRIBUTE1 VARCHAR2(150);
55 V_ATTRIBUTE2 VARCHAR2(150);
56 V_ATTRIBUTE3 VARCHAR2(150);
57 V_ATTRIBUTE4 VARCHAR2(150);
58 V_ATTRIBUTE5 VARCHAR2(150);
59 V_ATTRIBUTE6 VARCHAR2(150);
60 V_ATTRIBUTE7 VARCHAR2(150);
61 V_ATTRIBUTE8 VARCHAR2(150);
62 V_ATTRIBUTE9 VARCHAR2(150);
63 V_ATTRIBUTE10 VARCHAR2(150);
64 V_ATTRIBUTE11 VARCHAR2(150);
65 V_ATTRIBUTE12 VARCHAR2(150);
66 V_ATTRIBUTE13 VARCHAR2(150);
67 V_ATTRIBUTE14 VARCHAR2(150);
68 V_ATTRIBUTE15 VARCHAR2(150);
69 V_LOCATION_ID NUMBER;
70 V_OCCUPANCY_DATE DATE;
71 V_LOCATION_ID_1 NUMBER;
72 V_LOCATION_TYPE VARCHAR2(80);
73 V_LOCATION_NAME VARCHAR2(30);
74 V_LOCATION_CODE VARCHAR2(90);
75 V_SPACE_TYPE VARCHAR2(80);
76 V_PROPERTY_CODE VARCHAR2(90);
77 V_REGION_NAME VARCHAR2(50);
78 V_ADDRESS VARCHAR2(1500);
79 V_COUNTY VARCHAR2(60);
80 V_CITY VARCHAR2(60);
81 V_STATE VARCHAR2(60);
82 V_PROVINCE VARCHAR2(60);
83 V_ZIP_CODE VARCHAR2(60);
84 V_COUNTRY VARCHAR2(60);
85 V_RENTABLE_AREA NUMBER;
86 V_USABLE_AREA NUMBER;
87 V_GROSS_AREA NUMBER;
88 V_TENURE VARCHAR2(80);
89 V_LEASE_MILESTONE_ID NUMBER;
90 V_MILESTONE_TYPE VARCHAR2(80);
91 V_RESPONSIBLE_USER VARCHAR2(100);
92 V_ACTION_DATE DATE;
93 V_MILESTONE_DATE DATE;
94 V_ACTION_TAKEN VARCHAR2(50);
95 V_LAST_UPDATE_DATE DATE;
96 V_LAST_UPDATED_BY NUMBER;
97 V_LAST_UPDATE_LOGIN NUMBER;
98 V_CREATION_DATE DATE;
99 V_CREATED_BY NUMBER;
100 l_cursor INTEGER;
101 l_statement VARCHAR2(10000);
102 l_rows INTEGER;
103 l_count INTEGER;
104 l_lease_number_low VARCHAR2(30);
105 l_lease_number_high VARCHAR2(30);
106 l_location_code_low VARCHAR2(90);
107 l_location_code_high VARCHAR2(90);
108 l_responsible_user VARCHAR2(100);
109 l_action_due_date_from DATE;
110 l_action_due_date_to DATE;
111 l_lease_termination_from DATE;
112 l_lease_termination_to DATE;
113 l_milestone_type VARCHAR2(30);
114 --declare the record type for the function here.........
115 v_code_data PNP_UTIL_FUNC.location_name_rec := NULL;
116 -- declare cursors.....
117 BEGIN
118 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_leaseConditiond(+)');
119 --Initialise status parameters...
120 retcode:=0;
121 errbuf:='';
122 fnd_profile.get('LOGIN_ID', l_login_id);
123
124 l_cursor := dbms_sql.open_cursor;
125 l_statement :=
126 'SELECT
127 distinct
128 ten.location_id LOCATION_ID,
129 NVL(ten.occupancy_date, ten.estimated_occupancy_date) OCCUPANCY_DATE,
130 les.lease_id LEASE_ID,
131 les.lease_name LEASE_NAME,
132 les.lease_number LEASE_NUMBER,
133 les.lease_commencement_date LEASE_COMMENCEMENT_DATE,
134 les.lease_termination_date LEASE_TERMINATION_DATE,
135 les.lease_execution_date LEASE_EXECUTION_DATE,
136 (TRUNC(les.lease_termination_date)- TRUNC(les.lease_commencement_date)+1) LEASE_TERM,
137 fnd4.meaning LEASE_CLASS,
138 les.user_responsible LEASE_RESPONSIBLE_USER,
139 fnd1.meaning LEASE_STATUS,
140 fnd6.meaning LEASE_TYPE,
141 ten.estimated_occupancy_date ESTIMATED_OCCUPANCY_DATE,
142 ten.occupancy_date OCCUPANCY_DATE,
143 ten.attribute_category ATTRIBUTE_CATEGORY,
144 ten.attribute1 ATTRIBUTE1,
145 ten.attribute2 ATTRIBUTE2,
146 ten.attribute3 ATTRIBUTE3,
147 ten.attribute4 ATTRIBUTE4,
148 ten.attribute5 ATTRIBUTE5,
149 ten.attribute6 ATTRIBUTE6,
150 ten.attribute7 ATTRIBUTE7,
151 ten.attribute8 ATTRIBUTE8,
152 ten.attribute9 ATTRIBUTE9,
153 ten.attribute10 ATTRIBUTE10,
154 ten.attribute11 ATTRIBUTE11,
155 ten.attribute12 ATTRIBUTE12,
156 ten.attribute13 ATTRIBUTE13,
157 ten.attribute14 ATTRIBUTE14,
158 ten.attribute15 ATTRIBUTE15,
159 les.last_update_date LAST_UPDATE_DATE,
160 les.last_updated_by LAST_UPDATED_BY,
161 les.last_update_login LAST_UPDATE_LOGIN,
162 les.creation_date CREATION_DATE,
163 les.created_by CREATED_BY,
164 fnd5.meaning LOCATION_TYPE,
165 NVL(NVL(loc.building, loc.FLOOR), loc.office) LOCATION_NAME,
166 loc.location_code LOCATION_CODE,
167 fnd7.meaning USAGE_TYPE,
168 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,
169 pa.county COUNTY,
170 pa.city CITY,
171 pa.state STATE,
172 pa.province PROVINCE,
173 pa.zip_code ZIP_CODE,
174 pa.country COUNTRY,
175 DECODE (location_type_lookup_code,'||''''||'OFFICE'||''''||',loc.rentable_area,
176 '||''''||'FLOOR'||''''||',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id),
177 '||''''||'PARCEL'||''''||',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id),
178 '||''''||'BUILDING'||''''||',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id),
179 '||''''||'LAND'||''''||',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id),
180 rentable_area) RENTABLE_AREA,
181 DECODE (location_type_lookup_code,'||''''||'OFFICE'||''''||',loc.usable_area,
182 '||''''||'FLOOR'||''''||',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id),
183 '||''''||'PARCEL'||''''||',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id),
184 '||''''||'BUILDING'||''''||',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id),
185 '||''''||'LAND'||''''||',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id),
186 usable_area) USABLE_AREA,
187 loc.gross_area GROSS_AREA,
188 fnd2.meaning TENURE,
189 mil.lease_milestone_id LEASE_MILESTONE_ID,
190 fnd3.meaning MILESTONE_TYPE,
191 fnd.user_name RESPONSIBLE_USER,
192 mil.milestone_date ACTION_DATE,
193 mil.milestone_date-mil.lead_days MILESTONE_DATE,
194 mil.action_taken ACTION_TAKEN
195 FROM pn_leases_v les,
196 pn_tenancies ten,
197 pn_locations loc,
198 pn_lease_milestones mil,
199 fnd_user fnd,
200 pn_addresses pa,
201 fnd_lookups fnd1,
202 fnd_lookups fnd2,
203 fnd_lookups fnd3,
204 fnd_lookups fnd4,
205 fnd_lookups fnd5,
206 fnd_lookups fnd6,
207 fnd_lookups fnd7
208 WHERE ten.lease_id = les.lease_id
209 AND ten.location_id = loc.location_id
210 AND ( (( loc.active_start_date BETWEEN les.lease_commencement_date AND les.lease_Termination_date) OR
211 (loc.active_end_date BETWEEN les.lease_commencement_date AND les.lease_Termination_date)) OR ( loc.active_start_date < les.lease_commencement_date AND loc.active_end_date
212 > les.lease_Termination_date))
213 AND mil.lease_id = les.lease_id
214 AND mil.user_id = fnd.user_id
215 AND ten.primary_flag ='||''''||'Y'||''''||'
216 AND les.status = fnd1.lookup_code (+)
217 AND loc.lease_or_owned = fnd2.lookup_code (+)
218 AND fnd3.lookup_code = mil.milestone_type_code
219 AND fnd4.lookup_code (+) = les.lease_class_code
220 AND fnd5.lookup_code = loc.location_type_lookup_code
221 AND fnd6.lookup_code = les.lease_type_code
222 AND fnd7.lookup_code = ten.tenancy_usage_lookup_code
223 AND fnd1.lookup_type (+) = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
224 AND fnd2.lookup_type (+) = '||''''||'PN_LEASED_OR_OWNED'||''''||'
225 AND fnd3.lookup_type = '||''''||'PN_MILESTONES_TYPE'||''''||'
226 AND fnd4.lookup_type (+) = '||''''||'PN_LEASE_CLASS'||''''||'
227 AND fnd5.lookup_type = '||''''||'PN_LOCATION_TYPE'||''''||'
228 AND fnd6.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
229 AND fnd7.lookup_type = '||''''||'PN_TENANCY_USAGE_TYPE'||''''||'
230 AND pa.address_id(+) = loc.address_id';
231
232 --lease number conditions.....
233 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
234 l_lease_number_low := lease_number_low;
235 l_lease_number_high := lease_number_high;
236 l_statement :=
237 l_statement || ' AND les.lease_number BETWEEN :l_lease_number_low AND :l_lease_number_high ';
238
239 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
240 l_lease_number_high := lease_number_high;
241 l_statement :=
242 l_statement || ' AND les.lease_number = :l_lease_number_high ';
243
244 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
245 l_lease_number_low := lease_number_low;
246 l_statement :=
247 l_statement || ' AND les.lease_number = :l_lease_number_low ';
248
249 ELSE
250 l_statement :=
251 l_statement || ' AND 1=1 ';
252
253 END IF;
254
255
256 --location code conditions.....
257 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
258 l_location_code_low := location_code_low;
259 l_location_code_high := location_code_high;
260 l_statement :=
261 l_statement || ' AND loc.location_code BETWEEN :l_location_code_low AND :l_location_code_high ';
262
263 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
264 l_location_code_high := location_code_high;
265 l_statement :=
266 l_statement || ' AND loc.location_code = :l_location_code_high ';
267
268 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
269 l_location_code_low := location_code_low;
270 l_statement :=
271 l_statement || ' AND loc.location_code = :l_location_code_low ';
272
273 ELSE
274 l_statement :=
275 l_statement || ' AND 2=2 ';
276
277 END IF;
278
279 --responsible user conditions....
280 IF responsible_user IS NOT NULL THEN
281 l_responsible_user := responsible_user;
282 l_statement :=
283 l_statement || ' AND fnd.user_name = :l_responsible_user ';
284
285 ELSE
286 l_statement :=
287 l_statement || ' AND 3=3 ';
288
289 END IF;
290
291
292 --action due date conditions.....
293 IF action_due_date_from IS NOT NULL AND action_due_date_to IS NOT NULL THEN
294 l_action_due_date_from := action_due_date_from;
295 l_action_due_date_to := action_due_date_to;
296 l_statement :=
297 l_statement || ' AND mil.milestone_date BETWEEN :l_action_due_date_from AND :l_action_due_date_to ';
298
299 ELSIF action_due_date_from IS NULL AND action_due_date_to IS NOT NULL THEN
300 l_action_due_date_to := action_due_date_to;
301 l_statement :=
302 l_statement || ' AND mil.milestone_date = :l_action_due_date_to ';
303
304 ELSIF action_due_date_from IS NOT NULL AND action_due_date_to IS NULL THEN
305 l_action_due_date_from := action_due_date_from;
306 l_statement :=
307 l_statement || ' AND mil.milestone_date = :l_action_due_date_from ';
308
309 ELSE
310 l_statement :=
311 l_statement || ' AND 4=4 ';
312
313 END IF;
314
315
316 --lease date conditions.....
317 IF lease_termination_from IS NOT NULL AND lease_termination_to IS NOT NULL THEN
318 l_lease_termination_from := lease_termination_from;
319 l_lease_termination_to := lease_termination_to;
320 l_statement :=
321 l_statement || ' AND les.lease_termination_date
322 BETWEEN :l_lease_termination_from AND :l_lease_termination_to ';
323
324 ELSIF lease_termination_from IS NULL AND lease_termination_to IS NOT NULL THEN
325 l_lease_termination_to := lease_termination_to;
326 l_statement :=
327 l_statement || ' AND les.lease_termination_date = :l_lease_termination_to ';
328
329 ELSIF lease_termination_from IS NOT NULL AND lease_termination_to IS NULL THEN
330 l_lease_termination_from := lease_termination_from;
331 l_statement :=
332 l_statement || ' AND les.lease_termination_date = :l_lease_termination_from ';
333
334 ELSE
335 l_statement :=
336 l_statement || ' AND 5=5 ';
337
338 END IF;
339
340
341 --milestone type conditions....
342 IF milestone_type IS NOT NULL THEN
343 l_milestone_type := milestone_type;
344 l_statement :=
345 l_statement || ' AND mil.milestone_type_code = :l_milestone_type ';
346
347 ELSE
348 l_statement :=
349 l_statement || ' AND 6=6 ';
350
351 END IF;
352
353 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
354
355 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
356 dbms_sql.bind_variable
357 (l_cursor, 'l_lease_number_low', l_lease_number_low );
358 dbms_sql.bind_variable
359 (l_cursor, 'l_lease_number_high', l_lease_number_high );
360
361 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
362 dbms_sql.bind_variable
363 (l_cursor, 'l_lease_number_high', l_lease_number_high );
364
365 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
366 dbms_sql.bind_variable
367 (l_cursor, 'l_lease_number_low', l_lease_number_low );
368 END IF;
369
370 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
371 dbms_sql.bind_variable
372 (l_cursor, 'l_location_code_low', l_location_code_low );
373 dbms_sql.bind_variable
374 (l_cursor, 'l_location_code_high', l_location_code_high );
375
376 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
377 dbms_sql.bind_variable
378 (l_cursor, 'l_location_code_high', l_location_code_high );
379
380 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
381 dbms_sql.bind_variable
382 (l_cursor, 'l_location_code_low', l_location_code_low );
383 END IF;
384
385 IF responsible_user IS NOT NULL THEN
386 dbms_sql.bind_variable
387 (l_cursor, 'l_responsible_user', l_responsible_user );
388 END IF;
389
390 IF action_due_date_from IS NOT NULL AND action_due_date_to IS NOT NULL THEN
391 dbms_sql.bind_variable
392 (l_cursor, 'l_action_due_date_from', l_action_due_date_from );
393 dbms_sql.bind_variable
394 (l_cursor, 'l_action_due_date_to', l_action_due_date_to );
395
396 ELSIF action_due_date_from IS NULL AND action_due_date_to IS NOT NULL THEN
397 dbms_sql.bind_variable
398 (l_cursor, 'l_action_due_date_to', l_action_due_date_to );
399 ELSIF action_due_date_from IS NOT NULL AND action_due_date_to IS NULL THEN
400 dbms_sql.bind_variable
401 (l_cursor, 'l_action_due_date_from', l_action_due_date_from );
402 END IF;
403
404 IF lease_termination_from IS NOT NULL AND lease_termination_to IS NOT NULL THEN
405 dbms_sql.bind_variable
406 (l_cursor, 'l_lease_termination_from', l_lease_termination_from );
407 dbms_sql.bind_variable
408 (l_cursor, 'l_lease_termination_to', l_lease_termination_to );
409
410 ELSIF lease_termination_from IS NULL AND lease_termination_to IS NOT NULL THEN
411 dbms_sql.bind_variable
412 (l_cursor, 'l_lease_termination_to', l_lease_termination_to );
413 ELSIF lease_termination_from IS NOT NULL AND lease_termination_to IS NULL THEN
414 dbms_sql.bind_variable
415 (l_cursor, 'l_lease_termination_from', l_lease_termination_from );
416 END IF;
417
418 IF milestone_type IS NOT NULL THEN
419 dbms_sql.bind_variable
420 (l_cursor, 'l_milestone_type', l_milestone_type );
421 END IF;
422
423 dbms_sql.define_column (l_cursor, 1,V_LOCATION_ID);
424 dbms_sql.define_column (l_cursor, 2,V_OCCUPANCY_DATE);
425 dbms_sql.define_column (l_cursor, 3,V_LEASE_ID);
426 dbms_sql.define_column (l_cursor, 4,V_LEASE_NAME,50);
427 dbms_sql.define_column (l_cursor, 5,V_LEASE_NUMBER,30);
428 dbms_sql.define_column (l_cursor, 6,V_LEASE_COM_DATE);
429 dbms_sql.define_column (l_cursor, 7,V_LEASE_TERM_DATE);
430 dbms_sql.define_column (l_cursor, 8,V_LEASE_EXE_DATE);
431 dbms_sql.define_column (l_cursor, 9,V_LEASE_TERM);
432 dbms_sql.define_column (l_cursor, 10,V_LEASE_CLASS,80);
433 dbms_sql.define_column (l_cursor, 11,V_LEASE_RESP_USER,100);
434 dbms_sql.define_column (l_cursor, 12,V_LEASE_STATUS,80);
435 dbms_sql.define_column (l_cursor, 13,V_LEASE_TYPE,80);
436 dbms_sql.define_column (l_cursor, 14,V_ESTIMATED_OCCUPANCY_DATE);
437 dbms_sql.define_column (l_cursor, 15,V_ACTUAL_OCCUPANCY_DATE);
438 dbms_sql.define_column (l_cursor, 16,V_ATTRIBUTE_CATEGORY,30);
439 dbms_sql.define_column (l_cursor, 17,V_ATTRIBUTE1,150);
440 dbms_sql.define_column (l_cursor, 18,V_ATTRIBUTE2,150);
441 dbms_sql.define_column (l_cursor, 19,V_ATTRIBUTE3,150);
442 dbms_sql.define_column (l_cursor, 20,V_ATTRIBUTE4,150);
443 dbms_sql.define_column (l_cursor, 21,V_ATTRIBUTE5,150);
444 dbms_sql.define_column (l_cursor, 22,V_ATTRIBUTE6,150);
445 dbms_sql.define_column (l_cursor, 23,V_ATTRIBUTE7,150);
446 dbms_sql.define_column (l_cursor, 24,V_ATTRIBUTE8,150);
447 dbms_sql.define_column (l_cursor, 25,V_ATTRIBUTE9,150);
448 dbms_sql.define_column (l_cursor, 26,V_ATTRIBUTE10,150);
449 dbms_sql.define_column (l_cursor, 27,V_ATTRIBUTE11,150);
450 dbms_sql.define_column (l_cursor, 28,V_ATTRIBUTE12,150);
451 dbms_sql.define_column (l_cursor, 29,V_ATTRIBUTE13,150);
452 dbms_sql.define_column (l_cursor, 30,V_ATTRIBUTE14,150);
453 dbms_sql.define_column (l_cursor, 31,V_ATTRIBUTE15,150);
454 dbms_sql.define_column (l_cursor, 32,V_LAST_UPDATE_DATE);
455 dbms_sql.define_column (l_cursor, 33,V_LAST_UPDATED_BY);
456 dbms_sql.define_column (l_cursor, 34,V_LAST_UPDATE_LOGIN);
457 dbms_sql.define_column (l_cursor, 35,V_CREATION_DATE);
458 dbms_sql.define_column (l_cursor, 36,V_CREATED_BY);
459 dbms_sql.define_column (l_cursor, 37,V_LOCATION_TYPE,80);
460 dbms_sql.define_column (l_cursor, 38,V_LOCATION_NAME,30);
461 dbms_sql.define_column (l_cursor, 39,V_LOCATION_CODE,90);
462 dbms_sql.define_column (l_cursor, 40,V_SPACE_TYPE,80);
463 dbms_sql.define_column (l_cursor, 41,V_ADDRESS,1500);
464 dbms_sql.define_column (l_cursor, 42,V_COUNTY,60);
465 dbms_sql.define_column (l_cursor, 43,V_CITY,60);
466 dbms_sql.define_column (l_cursor, 44,V_STATE,60);
467 dbms_sql.define_column (l_cursor, 45,V_PROVINCE,60);
468 dbms_sql.define_column (l_cursor, 46,V_ZIP_CODE,60);
469 dbms_sql.define_column (l_cursor, 47,V_COUNTRY,60);
470 dbms_sql.define_column (l_cursor, 48,V_RENTABLE_AREA);
471 dbms_sql.define_column (l_cursor, 49,V_USABLE_AREA);
472 dbms_sql.define_column (l_cursor, 50,V_GROSS_AREA);
473 dbms_sql.define_column (l_cursor, 51,V_TENURE,80);
474 dbms_sql.define_column (l_cursor, 52,V_LEASE_MILESTONE_ID);
475 dbms_sql.define_column (l_cursor, 53,V_MILESTONE_TYPE,80);
476 dbms_sql.define_column (l_cursor, 54,V_RESPONSIBLE_USER,100);
477 dbms_sql.define_column (l_cursor, 55,V_ACTION_DATE);
478 dbms_sql.define_column (l_cursor, 56,V_MILESTONE_DATE);
479 dbms_sql.define_column (l_cursor, 57,V_ACTION_TAKEN,50);
480
481 l_rows := dbms_sql.execute(l_cursor);
482
483 LOOP
484
485 l_count := dbms_sql.fetch_rows( l_cursor );
486 EXIT WHEN l_count <> 1;
487
488 dbms_sql.column_value (l_cursor, 1,V_LOCATION_ID);
489 dbms_sql.column_value (l_cursor, 2,V_OCCUPANCY_DATE);
490 dbms_sql.column_value (l_cursor, 3,V_LEASE_ID);
491 dbms_sql.column_value (l_cursor, 4,V_LEASE_NAME);
492 dbms_sql.column_value (l_cursor, 5,V_LEASE_NUMBER);
493 dbms_sql.column_value (l_cursor, 6,V_LEASE_COM_DATE);
494 dbms_sql.column_value (l_cursor, 7,V_LEASE_TERM_DATE);
495 dbms_sql.column_value (l_cursor, 8,V_LEASE_EXE_DATE);
496 dbms_sql.column_value (l_cursor, 9,V_LEASE_TERM);
497 dbms_sql.column_value (l_cursor, 10,V_LEASE_CLASS);
498 dbms_sql.column_value (l_cursor, 11,V_LEASE_RESP_USER);
499 dbms_sql.column_value (l_cursor, 12,V_LEASE_STATUS);
500 dbms_sql.column_value (l_cursor, 13,V_LEASE_TYPE);
501 dbms_sql.column_value (l_cursor, 14,V_ESTIMATED_OCCUPANCY_DATE);
502 dbms_sql.column_value (l_cursor, 15,V_ACTUAL_OCCUPANCY_DATE);
503 dbms_sql.column_value (l_cursor, 16,V_ATTRIBUTE_CATEGORY);
504 dbms_sql.column_value (l_cursor, 17,V_ATTRIBUTE1);
505 dbms_sql.column_value (l_cursor, 18,V_ATTRIBUTE2);
506 dbms_sql.column_value (l_cursor, 19,V_ATTRIBUTE3);
507 dbms_sql.column_value (l_cursor, 20,V_ATTRIBUTE4);
508 dbms_sql.column_value (l_cursor, 21,V_ATTRIBUTE5);
509 dbms_sql.column_value (l_cursor, 22,V_ATTRIBUTE6);
510 dbms_sql.column_value (l_cursor, 23,V_ATTRIBUTE7);
511 dbms_sql.column_value (l_cursor, 24,V_ATTRIBUTE8);
512 dbms_sql.column_value (l_cursor, 25,V_ATTRIBUTE9);
513 dbms_sql.column_value (l_cursor, 26,V_ATTRIBUTE10);
514 dbms_sql.column_value (l_cursor, 27,V_ATTRIBUTE11);
515 dbms_sql.column_value (l_cursor, 28,V_ATTRIBUTE12);
516 dbms_sql.column_value (l_cursor, 29,V_ATTRIBUTE13);
517 dbms_sql.column_value (l_cursor, 30,V_ATTRIBUTE14);
518 dbms_sql.column_value (l_cursor, 31,V_ATTRIBUTE15);
519 dbms_sql.column_value (l_cursor, 32,V_LAST_UPDATE_DATE);
520 dbms_sql.column_value (l_cursor, 33,V_LAST_UPDATED_BY);
521 dbms_sql.column_value (l_cursor, 34,V_LAST_UPDATE_LOGIN);
522 dbms_sql.column_value (l_cursor, 35,V_CREATION_DATE);
523 dbms_sql.column_value (l_cursor, 36,V_CREATED_BY);
524 dbms_sql.column_value (l_cursor, 37,V_LOCATION_TYPE);
525 dbms_sql.column_value (l_cursor, 38,V_LOCATION_NAME);
526 dbms_sql.column_value (l_cursor, 39,V_LOCATION_CODE);
527 dbms_sql.column_value (l_cursor, 40,V_SPACE_TYPE);
528 dbms_sql.column_value (l_cursor, 41,V_ADDRESS);
529 dbms_sql.column_value (l_cursor, 42,V_COUNTY);
530 dbms_sql.column_value (l_cursor, 43,V_CITY);
531 dbms_sql.column_value (l_cursor, 44,V_STATE);
532 dbms_sql.column_value (l_cursor, 45,V_PROVINCE);
533 dbms_sql.column_value (l_cursor, 46,V_ZIP_CODE);
534 dbms_sql.column_value (l_cursor, 47,V_COUNTRY);
535 dbms_sql.column_value (l_cursor, 48,V_RENTABLE_AREA);
536 dbms_sql.column_value (l_cursor, 49,V_USABLE_AREA);
537 dbms_sql.column_value (l_cursor, 50,V_GROSS_AREA);
538 dbms_sql.column_value (l_cursor, 51,V_TENURE);
539 dbms_sql.column_value (l_cursor, 52,V_LEASE_MILESTONE_ID);
540 dbms_sql.column_value (l_cursor, 53,V_MILESTONE_TYPE);
541 dbms_sql.column_value (l_cursor, 54,V_RESPONSIBLE_USER);
542 dbms_sql.column_value (l_cursor, 55,V_ACTION_DATE);
543 dbms_sql.column_value (l_cursor, 56,V_MILESTONE_DATE);
544 dbms_sql.column_value (l_cursor, 57,V_ACTION_TAKEN);
545
546 v_code_data:=pnp_util_func.get_location_name(V_LOCATION_ID, V_OCCUPANCY_DATE);
547
548 PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_les: insert(+)');
549
550 INSERT INTO pn_milestones_itf
551 (LEASE_MILESTONE_ID ,
552 MILESTONE_TYPE_CODE ,
553 RESPONSIBLE_USER ,
554 ACTION_DATE ,
555 NOTIFICATION_DATE ,
556 ACTION_TAKEN ,
557 LEASE_ID ,
558 LEASE_NAME ,
559 LEASE_NUMBER ,
560 LEASE_COMMENCEMENT_DATE ,
561 LEASE_TERMINATION_DATE ,
562 LEASE_EXECUTION_DATE ,
563 LEASE_TERM ,
564 LEASE_CLASS ,
565 LEASE_RESPONSIBLE_USER ,
566 LEASE_STATUS ,
567 LEASE_TYPE ,
568 ESTIMATED_OCCUPANCY_DATE ,
569 ACTUAL_OCCUPANCY_DATE ,
570 LOCATION_ID ,
571 LOCATION_TYPE ,
572 LOCATION_NAME ,
573 LOCATION_CODE ,
574 SPACE_TYPE ,
575 REGION ,
576 PROPERTY_NAME ,
577 BUILDING_OR_LAND_NAME ,
578 FLOOR_OR_PARCEL_NAME ,
579 OFFICE_OR_SECTION_NAME ,
580 ADDRESS ,
581 COUNTY ,
582 CITY ,
583 STATE ,
584 PROVINCE ,
585 ZIP_CODE ,
586 COUNTRY ,
587 GROSS_AREA ,
588 RENTABLE_AREA ,
589 USABLE_AREA ,
590 TENURE ,
591 TEN_ATTRIBUTE_CATEGORY ,
592 TEN_ATTRIBUTE1 ,
593 TEN_ATTRIBUTE2 ,
594 TEN_ATTRIBUTE3 ,
595 TEN_ATTRIBUTE4 ,
596 TEN_ATTRIBUTE5 ,
597 TEN_ATTRIBUTE6 ,
598 TEN_ATTRIBUTE7 ,
599 TEN_ATTRIBUTE8 ,
600 TEN_ATTRIBUTE9 ,
601 TEN_ATTRIBUTE10 ,
602 TEN_ATTRIBUTE11 ,
603 TEN_ATTRIBUTE12 ,
604 TEN_ATTRIBUTE13 ,
605 TEN_ATTRIBUTE14 ,
606 TEN_ATTRIBUTE15 ,
607 LAST_UPDATE_DATE ,
608 LAST_UPDATED_BY ,
609 LAST_UPDATE_LOGIN ,
610 CREATION_DATE ,
611 CREATED_BY ,
612 REQUEST_ID )
613 VALUES
614 (V_LEASE_MILESTONE_ID ,
615 V_MILESTONE_TYPE ,
616 V_RESPONSIBLE_USER ,
617 V_ACTION_DATE ,
618 V_MILESTONE_DATE ,
619 V_ACTION_TAKEN ,
620 V_LEASE_ID ,
621 V_LEASE_NAME ,
622 V_LEASE_NUMBER ,
623 V_LEASE_COM_DATE ,
624 V_LEASE_TERM_DATE ,
625 V_LEASE_EXE_DATE ,
626 V_LEASE_TERM ,
627 V_LEASE_CLASS ,
628 V_LEASE_RESP_USER ,
629 V_LEASE_STATUS ,
630 V_LEASE_TYPE ,
631 V_ESTIMATED_OCCUPANCY_DATE ,
632 V_ACTUAL_OCCUPANCY_DATE ,
633 V_LOCATION_ID ,
634 V_LOCATION_TYPE ,
635 V_LOCATION_NAME ,
636 V_LOCATION_CODE ,
637 V_SPACE_TYPE ,
638 v_code_data.REGION_NAME ,
639 v_code_data.PROPERTY_NAME ,
640 v_code_data.BUILDING ,
641 v_code_data.FLOOR ,
642 v_code_data.OFFICE ,
643 V_ADDRESS ,
644 V_COUNTY ,
645 V_CITY ,
646 V_STATE ,
647 V_PROVINCE ,
648 V_ZIP_CODE ,
649 V_COUNTRY ,
650 V_GROSS_AREA ,
651 V_RENTABLE_AREA ,
652 V_USABLE_AREA ,
653 V_TENURE ,
654 V_ATTRIBUTE_CATEGORY ,
655 V_ATTRIBUTE1 ,
656 V_ATTRIBUTE2 ,
657 V_ATTRIBUTE3 ,
658 V_ATTRIBUTE4 ,
659 V_ATTRIBUTE5 ,
660 V_ATTRIBUTE6 ,
661 V_ATTRIBUTE7 ,
662 V_ATTRIBUTE8 ,
663 V_ATTRIBUTE9 ,
664 V_ATTRIBUTE10 ,
665 V_ATTRIBUTE11 ,
666 V_ATTRIBUTE12 ,
667 V_ATTRIBUTE13 ,
668 V_ATTRIBUTE14 ,
669 V_ATTRIBUTE15 ,
670 V_LAST_UPDATE_DATE ,
671 V_LAST_UPDATED_BY ,
672 V_LAST_UPDATE_LOGIN ,
673 V_CREATION_DATE ,
674 V_CREATED_BY ,
675 l_request_id );
676
677 PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_les: insert(-)');
678
679 END LOOP;
680
681 IF dbms_sql.is_open (l_cursor) THEN
682 dbms_sql.close_cursor (l_cursor);
683 END IF;
684
685 PNP_DEBUG_PKG.put_log_msg('pn_lease_optionsLoop(-)');
686
687 --If there is something amiss...
688 EXCEPTION
689 WHEN OTHERS THEN
690 retcode:=2;
691 errbuf:=SUBSTR(SQLERRM,1,235);
692 RAISE;
693 COMMIT;
694 END pn_milestones;
695 END pnrx_milestones;