1 PACKAGE BODY pn_tenancies_pkg AS
2 -- $Header: PNTTENTB.pls 120.9 2007/07/06 07:34:05 rdonthul ship $
3
4 TYPE loc_info_rec IS
5 RECORD (active_start_date pn_locations.active_start_date%TYPE,
6 active_end_date pn_locations.active_end_date%TYPE,
7 assignable_area pn_locations.assignable_area%TYPE);
8
9 TYPE loc_info_type IS
10 TABLE OF loc_info_rec
11 INDEX BY BINARY_INTEGER;
12
13 loc_info_tbl loc_info_type;
14
15 ------------------------------------------------------------------------------------
16 -- 22-AUG-2003 Satish Tripathi o Fixed for BUG# 3085758, Added fin_oblig_end_date.
17 ------------------------------------------------------------------------------------
18 TYPE space_assign_info_rec IS
19 RECORD (cust_assign_start_date pn_space_assign_cust.cust_assign_start_date%TYPE,
20 cust_assign_end_date pn_space_assign_cust.cust_assign_end_date%TYPE,
21 fin_oblig_end_date pn_space_assign_cust.fin_oblig_end_date%TYPE,
22 allocated_area pn_space_assign_cust.allocated_area%TYPE,
23 allocated_area_pct pn_space_assign_cust_all.allocated_area_pct%TYPE);
24
25 TYPE space_assign_info_type IS
26 TABLE OF space_assign_info_rec
27 INDEX BY BINARY_INTEGER;
28
29 space_assign_info_tbl space_assign_info_type;
30
31 -------------------------------------------------------------------------------
32 -- PROCDURE : INSERT_ROW
33 -- INVOKED FROM : insert_row procedure
34 -- PURPOSE : inserts the row
35 -- HISTORY :
36 -- 11-SEP-02 STripathi o If returnStatus = 'W', return parameter
37 -- x_tenancy_ovelap_wrn 'Y' for Multi-Tenancy-Lease changes.
38 -- 16-JAN-02 PSidhu o bug#2730279 - Removed call to
39 -- pn_tenancies_pkg.check_unique_primary_location.
40 -- 04-DEC-04 ftanudja o Added 8 parameters for lease rentable area. 3257508.
41 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_tenancies with _ALL table.
42 -- 28-NOV-05 pikhar o fetched org_id using cursor
43 -------------------------------------------------------------------------------
44 PROCEDURE Insert_Row
45 (
46 X_ROWID IN OUT NOCOPY VARCHAR2,
47 X_TENANCY_ID IN OUT NOCOPY NUMBER,
48 X_LOCATION_ID IN NUMBER,
49 X_LEASE_ID IN NUMBER,
50 X_LEASE_CHANGE_ID IN NUMBER,
51 X_TENANCY_USAGE_LOOKUP_CODE IN VARCHAR2,
52 X_PRIMARY_FLAG IN VARCHAR2,
53 X_ESTIMATED_OCCUPANCY_DATE IN DATE,
54 X_OCCUPANCY_DATE IN DATE,
55 X_EXPIRATION_DATE IN DATE,
56 X_ASSIGNABLE_FLAG IN VARCHAR2,
57 X_SUBLEASEABLE_FLAG IN VARCHAR2,
58 X_TENANTS_PROPORTIONATE_SHARE IN NUMBER,
59 X_ALLOCATED_AREA_PCT IN NUMBER,
60 X_ALLOCATED_AREA IN NUMBER,
61 X_STATUS IN VARCHAR2,
62 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
63 X_ATTRIBUTE1 IN VARCHAR2,
64 X_ATTRIBUTE2 IN VARCHAR2,
65 X_ATTRIBUTE3 IN VARCHAR2,
66 X_ATTRIBUTE4 IN VARCHAR2,
67 X_ATTRIBUTE5 IN VARCHAR2,
68 X_ATTRIBUTE6 IN VARCHAR2,
69 X_ATTRIBUTE7 IN VARCHAR2,
70 X_ATTRIBUTE8 IN VARCHAR2,
71 X_ATTRIBUTE9 IN VARCHAR2,
72 X_ATTRIBUTE10 IN VARCHAR2,
73 X_ATTRIBUTE11 IN VARCHAR2,
74 X_ATTRIBUTE12 IN VARCHAR2,
75 X_ATTRIBUTE13 IN VARCHAR2,
76 X_ATTRIBUTE14 IN VARCHAR2,
77 X_ATTRIBUTE15 IN VARCHAR2,
78 X_CREATION_DATE IN DATE,
79 X_CREATED_BY IN NUMBER,
80 X_LAST_UPDATE_DATE IN DATE,
81 X_LAST_UPDATED_BY IN NUMBER,
82 X_LAST_UPDATE_LOGIN IN NUMBER,
83 X_ORG_ID IN NUMBER,
84 X_TENANCY_OVELAP_WRN OUT NOCOPY VARCHAR2,
85 X_RECOVERY_TYPE_CODE IN VARCHAR2,
86 X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
87 X_FIN_OBLIG_END_DATE IN DATE,
88 X_CUSTOMER_ID IN NUMBER,
89 X_CUSTOMER_SITE_USE_ID IN NUMBER,
90 X_LEASE_RENTABLE_AREA IN NUMBER,
91 X_LEASE_USABLE_AREA IN NUMBER,
92 X_LEASE_ASSIGNABLE_AREA IN NUMBER,
93 X_LEASE_LOAD_FACTOR IN NUMBER,
94 X_LOCATION_RENTABLE_AREA IN NUMBER,
95 X_LOCATION_USABLE_AREA IN NUMBER,
96 X_LOCATION_ASSIGNABLE_AREA IN NUMBER,
97 X_LOCATION_LOAD_FACTOR IN NUMBER
98 )
99 IS
100 CURSOR C IS
101 SELECT ROWID
102 FROM pn_tenancies_all
103 WHERE tenancy_id = x_tenancy_id;
104
105 l_returnStatus VARCHAR2(30) := NULL;
106
107 CURSOR org_cur IS
108 SELECT org_id
109 FROM pn_leases_all
110 WHERE lease_id = x_lease_id;
111
112 l_org_id NUMBER;
113
114
115
116 BEGIN
117
118 pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_ROW (+)');
119
120
121 IF x_org_id IS NULL THEN
122 FOR rec IN org_cur LOOP
123 l_org_id := rec.org_id;
124 END LOOP;
125 ELSE
126 l_org_id := x_org_id;
127 END IF;
128
129 x_tenancy_ovelap_wrn := NULL;
130
131 ---------------------------------------------------
132 -- Check tenancy dates
133 ---------------------------------------------------
134 l_returnStatus := NULL;
135 PN_TENANCIES_PKG.CHECK_TENANCY_DATES
136 (
137 l_returnStatus
138 ,X_ESTIMATED_OCCUPANCY_DATE
139 ,X_OCCUPANCY_DATE
140 ,X_EXPIRATION_DATE
141 );
142 IF (l_returnStatus IS NOT NULL) THEN
143 app_exception.Raise_Exception;
144 END IF;
145
146 ---------------------------------------------------
147 -- Check IF the tenancy dates overlap
148 ---------------------------------------------------
149 l_returnStatus := NULL;
150 PN_TENANCIES_PKG.CHECK_FOR_OVELAP_OF_TENANCY
151 (
152 l_returnStatus
153 ,X_TENANCY_ID
154 ,X_LOCATION_ID
155 ,X_LEASE_ID
156 ,X_ESTIMATED_OCCUPANCY_DATE
157 ,X_OCCUPANCY_DATE
158 ,X_EXPIRATION_DATE
159 );
160 IF (l_returnStatus = 'W') THEN
161 x_tenancy_ovelap_wrn := 'Y';
162 ELSIF (l_returnStatus IS NOT NULL) THEN
163 app_exception.Raise_Exception;
164 END IF;
165
166
167 ---------------------------------------------------
168 -- Assign Nextval WHEN argument value IS passed
169 -- as NULL
170 ---------------------------------------------------
171 IF x_tenancy_ID IS NULL THEN
172
173 SELECT pn_tenancies_s.NEXTVAL
174 INTO x_tenancy_id
175 FROM DUAL;
176
177 END IF;
178
179 INSERT INTO pn_tenancies_all
180 (
181 TENANCY_ID,
182 LAST_UPDATE_DATE,
183 LAST_UPDATED_BY,
184 CREATION_DATE,
185 CREATED_BY,
186 LAST_UPDATE_LOGIN,
187 LOCATION_ID,
188 LEASE_ID,
189 LEASE_CHANGE_ID,
190 TENANCY_USAGE_LOOKUP_CODE,
191 PRIMARY_FLAG,
192 ESTIMATED_OCCUPANCY_DATE,
193 OCCUPANCY_DATE,
194 EXPIRATION_DATE,
195 ASSIGNABLE_FLAG,
196 SUBLEASEABLE_FLAG,
197 TENANTS_PROPORTIONATE_SHARE,
198 ALLOCATED_AREA_PCT,
199 ALLOCATED_AREA,
200 STATUS,
201 ATTRIBUTE_CATEGORY,
202 ATTRIBUTE1,
203 ATTRIBUTE2,
204 ATTRIBUTE3,
205 ATTRIBUTE4,
206 ATTRIBUTE5,
207 ATTRIBUTE6,
208 ATTRIBUTE7,
209 ATTRIBUTE8,
210 ATTRIBUTE9,
211 ATTRIBUTE10,
212 ATTRIBUTE11,
213 ATTRIBUTE12,
214 ATTRIBUTE13,
215 ATTRIBUTE14,
216 ATTRIBUTE15,
217 ORG_ID,
218 RECOVERY_TYPE_CODE,
219 RECOVERY_SPACE_STD_CODE,
220 FIN_OBLIG_END_DATE,
221 CUSTOMER_ID,
222 CUSTOMER_SITE_USE_ID,
223 LEASE_RENTABLE_AREA,
224 LEASE_USABLE_AREA,
225 LEASE_ASSIGNABLE_AREA,
226 LEASE_LOAD_FACTOR,
227 LOCATION_RENTABLE_AREA,
228 LOCATION_USABLE_AREA,
229 LOCATION_ASSIGNABLE_AREA,
230 LOCATION_LOAD_FACTOR
231 )
232 VALUES
233 (
234 X_TENANCY_ID,
235 X_LAST_UPDATE_DATE,
236 X_LAST_UPDATED_BY,
237 X_CREATION_DATE,
238 X_CREATED_BY,
239 X_LAST_UPDATE_LOGIN,
240 X_LOCATION_ID,
241 X_LEASE_ID,
242 X_LEASE_CHANGE_ID,
243 X_TENANCY_USAGE_LOOKUP_CODE,
244 X_PRIMARY_FLAG,
245 X_ESTIMATED_OCCUPANCY_DATE,
246 X_OCCUPANCY_DATE,
247 X_EXPIRATION_DATE,
248 X_ASSIGNABLE_FLAG,
249 X_SUBLEASEABLE_FLAG,
250 X_TENANTS_PROPORTIONATE_SHARE,
251 X_ALLOCATED_AREA_PCT,
252 X_ALLOCATED_AREA,
253 X_STATUS,
254 X_ATTRIBUTE_CATEGORY,
255 X_ATTRIBUTE1,
256 X_ATTRIBUTE2,
257 X_ATTRIBUTE3,
258 X_ATTRIBUTE4,
259 X_ATTRIBUTE5,
260 X_ATTRIBUTE6,
261 X_ATTRIBUTE7,
262 X_ATTRIBUTE8,
263 X_ATTRIBUTE9,
264 X_ATTRIBUTE10,
265 X_ATTRIBUTE11,
266 X_ATTRIBUTE12,
267 X_ATTRIBUTE13,
268 X_ATTRIBUTE14,
269 X_ATTRIBUTE15,
270 l_ORG_ID,
271 X_RECOVERY_TYPE_CODE,
272 X_RECOVERY_SPACE_STD_CODE,
273 X_FIN_OBLIG_END_DATE,
274 X_CUSTOMER_ID,
275 X_CUSTOMER_SITE_USE_ID,
276 X_LEASE_RENTABLE_AREA,
277 X_LEASE_USABLE_AREA,
278 X_LEASE_ASSIGNABLE_AREA,
279 X_LEASE_LOAD_FACTOR,
280 X_LOCATION_RENTABLE_AREA,
281 X_LOCATION_USABLE_AREA,
282 X_LOCATION_ASSIGNABLE_AREA,
283 X_LOCATION_LOAD_FACTOR
284 );
285
286 OPEN c;
287 FETCH c INTO X_ROWID;
288 IF (c%NOTFOUND) THEN
289 CLOSE c;
290 RAISE NO_DATA_FOUND;
291 END IF;
292 CLOSE c;
293
294 pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_ROW (-)');
295
296 END Insert_Row;
297
298 -------------------------------------------------------------------------------
299 -- PROCDURE : Lock_Row
300 -- INVOKED FROM : Lock_Row procedure
301 -- PURPOSE : locks the row
302 -- HISTORY :
303 -- 04-DEC-04 ftanudja o Added 8 parameters for lease rentable area. 3257508.
304 -- 10-FEB-04 ftanudja o Removed locn areas (4 params).
305 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_tenancies with _ALL table.
306 -------------------------------------------------------------------------------
307 PROCEDURE Lock_Row
308 (
309 X_TENANCY_ID IN NUMBER,
310 X_LOCATION_ID IN NUMBER,
311 X_LEASE_ID IN NUMBER,
312 X_LEASE_CHANGE_ID IN NUMBER,
313 X_TENANCY_USAGE_LOOKUP_CODE IN VARCHAR2,
314 X_PRIMARY_FLAG IN VARCHAR2,
315 X_ESTIMATED_OCCUPANCY_DATE IN DATE,
316 X_OCCUPANCY_DATE IN DATE,
317 X_EXPIRATION_DATE IN DATE,
318 X_ASSIGNABLE_FLAG IN VARCHAR2,
319 X_SUBLEASEABLE_FLAG IN VARCHAR2,
320 X_TENANTS_PROPORTIONATE_SHARE IN NUMBER,
321 X_ALLOCATED_AREA_PCT IN NUMBER,
322 X_ALLOCATED_AREA IN NUMBER,
323 X_STATUS IN VARCHAR2,
324 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
325 X_ATTRIBUTE1 IN VARCHAR2,
326 X_ATTRIBUTE2 IN VARCHAR2,
327 X_ATTRIBUTE3 IN VARCHAR2,
328 X_ATTRIBUTE4 IN VARCHAR2,
329 X_ATTRIBUTE5 IN VARCHAR2,
330 X_ATTRIBUTE6 IN VARCHAR2,
331 X_ATTRIBUTE7 IN VARCHAR2,
332 X_ATTRIBUTE8 IN VARCHAR2,
333 X_ATTRIBUTE9 IN VARCHAR2,
334 X_ATTRIBUTE10 IN VARCHAR2,
335 X_ATTRIBUTE11 IN VARCHAR2,
336 X_ATTRIBUTE12 IN VARCHAR2,
337 X_ATTRIBUTE13 IN VARCHAR2,
338 X_ATTRIBUTE14 IN VARCHAR2,
339 X_ATTRIBUTE15 IN VARCHAR2,
340 X_RECOVERY_TYPE_CODE IN VARCHAR2,
341 X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
342 X_FIN_OBLIG_END_DATE IN DATE,
343 X_CUSTOMER_ID IN NUMBER,
347 X_LEASE_ASSIGNABLE_AREA IN NUMBER,
344 X_CUSTOMER_SITE_USE_ID IN NUMBER,
345 X_LEASE_RENTABLE_AREA IN NUMBER,
346 X_LEASE_USABLE_AREA IN NUMBER,
348 X_LEASE_LOAD_FACTOR IN NUMBER
349 )
350 IS
351 CURSOR c1 IS
352 SELECT *
353 FROM pn_tenancies_all
354 WHERE tenancy_id = x_tenancy_id
355 FOR UPDATE OF tenancy_id NOWAIT;
356
357 tlinfo c1%ROWTYPE;
358
359 BEGIN
360 pnp_debug_pkg.debug('PN_TENANCIES_PKG.LOCK_ROW (+)');
361
362 OPEN c1;
363 FETCH c1 INTO tlinfo;
364 IF (c1%NOTFOUND) THEN
365 CLOSE c1;
366 RETURN;
367 END IF;
368 CLOSE c1;
369
370 IF NOT (tlinfo.TENANCY_ID = X_TENANCY_ID) THEN
371 pn_var_rent_pkg.lock_row_exception('TENANCY_ID',tlinfo.TENANCY_ID);
372 END IF;
373
374 IF NOT (tlinfo.LOCATION_ID = X_LOCATION_ID) THEN
375 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',tlinfo.LOCATION_ID);
376 END IF;
377
378 IF NOT (tlinfo.LEASE_ID = X_LEASE_ID) THEN
379 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.LEASE_ID);
380 END IF;
381
382 IF NOT (tlinfo.LEASE_CHANGE_ID = X_LEASE_CHANGE_ID) THEN
383 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_ID',tlinfo.LEASE_CHANGE_ID);
384 END IF;
385
386 IF NOT (tlinfo.TENANCY_USAGE_LOOKUP_CODE = X_TENANCY_USAGE_LOOKUP_CODE) THEN
387 pn_var_rent_pkg.lock_row_exception('TENANCY_USAGE_LOOKUP_CODE',tlinfo.TENANCY_USAGE_LOOKUP_CODE);
388 END IF;
389
390 IF NOT (tlinfo.PRIMARY_FLAG = X_PRIMARY_FLAG) THEN
391 pn_var_rent_pkg.lock_row_exception('PRIMARY_FLAG',tlinfo.PRIMARY_FLAG);
392 END IF;
393
394 IF NOT ((tlinfo.ESTIMATED_OCCUPANCY_DATE = X_ESTIMATED_OCCUPANCY_DATE)
395 OR ((tlinfo.ESTIMATED_OCCUPANCY_DATE IS NULL) AND (X_ESTIMATED_OCCUPANCY_DATE IS NULL))) THEN
396 pn_var_rent_pkg.lock_row_exception('ESTIMATED_OCCUPANCY_DATE',tlinfo.ESTIMATED_OCCUPANCY_DATE);
397 END IF;
398
399 IF NOT ((tlinfo.OCCUPANCY_DATE = X_OCCUPANCY_DATE)
400 OR ((tlinfo.OCCUPANCY_DATE IS NULL) AND (X_OCCUPANCY_DATE IS NULL))) THEN
401 pn_var_rent_pkg.lock_row_exception('OCCUPANCY_DATE',tlinfo.OCCUPANCY_DATE);
402 END IF;
403
404 IF NOT ((tlinfo.EXPIRATION_DATE = X_EXPIRATION_DATE)
405 OR ((tlinfo.EXPIRATION_DATE IS NULL) AND (X_EXPIRATION_DATE IS NULL))) THEN
406 pn_var_rent_pkg.lock_row_exception('EXPIRATION_DATE',tlinfo.EXPIRATION_DATE);
407 END IF;
408
409 IF NOT ((tlinfo.ASSIGNABLE_FLAG = X_ASSIGNABLE_FLAG)
410 OR ((tlinfo.ASSIGNABLE_FLAG IS NULL) AND (X_ASSIGNABLE_FLAG IS NULL))) THEN
411 pn_var_rent_pkg.lock_row_exception('ASSIGNABLE_FLAG',tlinfo.ASSIGNABLE_FLAG);
412 END IF;
413
414 IF NOT ((tlinfo.SUBLEASEABLE_FLAG = X_SUBLEASEABLE_FLAG)
415 OR ((tlinfo.SUBLEASEABLE_FLAG IS NULL) AND (X_SUBLEASEABLE_FLAG IS NULL))) THEN
416 pn_var_rent_pkg.lock_row_exception('SUBLEASEABLE_FLAG',tlinfo.SUBLEASEABLE_FLAG);
417 END IF;
418
419 IF NOT ((tlinfo.TENANTS_PROPORTIONATE_SHARE = X_TENANTS_PROPORTIONATE_SHARE)
420 OR ((tlinfo.TENANTS_PROPORTIONATE_SHARE IS NULL) AND (X_TENANTS_PROPORTIONATE_SHARE IS NULL))) THEN
421 pn_var_rent_pkg.lock_row_exception('TENANTS_PROPORTIONATE_SHARE',tlinfo.TENANTS_PROPORTIONATE_SHARE);
422 END IF;
423
424 IF NOT ((tlinfo.STATUS = X_STATUS)
425 OR ((tlinfo.STATUS IS NULL) AND (X_STATUS IS NULL))) THEN
426 pn_var_rent_pkg.lock_row_exception('STATUS',tlinfo.STATUS);
427 END IF;
428
429 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
430 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL))) THEN
431 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
432 END IF;
433
434 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
435 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL))) THEN
436 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
437 END IF;
438
439 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
440 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL))) THEN
441 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
442 END IF;
443
444 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
445 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL))) THEN
446 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
447 END IF;
448
449 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
450 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL))) THEN
451 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
452 END IF;
453
454 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
455 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL))) THEN
456 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
457 END IF;
458
459 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
460 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL))) THEN
461 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
462 END IF;
463
464 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
465 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) THEN
466 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
467 END IF;
468
469 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
473
470 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) THEN
471 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
472 END IF;
474 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
475 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) THEN
476 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
477 END IF;
478
479 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
480 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) THEN
481 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
482 END IF;
483
484 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
485 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) THEN
486 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
487 END IF;
488
489 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
490 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) THEN
491 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
492 END IF;
493
494 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
495 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) THEN
496 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
497 END IF;
498
499 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
500 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) THEN
501 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
502 END IF;
503
504 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
505 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) THEN
506 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
507 END IF;
508
509 IF NOT ((tlinfo.RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE)
510 OR ((tlinfo.RECOVERY_TYPE_CODE IS NULL) AND (X_RECOVERY_TYPE_CODE IS NULL))) THEN
511 pn_var_rent_pkg.lock_row_exception('RECOVERY_TYPE_CODE',tlinfo.RECOVERY_TYPE_CODE);
512 END IF;
513
514 IF NOT ((tlinfo.RECOVERY_SPACE_STD_CODE = X_RECOVERY_SPACE_STD_CODE)
515 OR ((tlinfo.RECOVERY_SPACE_STD_CODE IS NULL) AND (X_RECOVERY_SPACE_STD_CODE IS NULL))) THEN
516 pn_var_rent_pkg.lock_row_exception('RECOVERY_SPACE_STD_CODE',tlinfo.RECOVERY_SPACE_STD_CODE);
517 END IF;
518
519 IF NOT ((tlinfo.FIN_OBLIG_END_DATE = X_FIN_OBLIG_END_DATE)
520 OR ((tlinfo.FIN_OBLIG_END_DATE IS NULL) AND (X_FIN_OBLIG_END_DATE IS NULL))) THEN
521 pn_var_rent_pkg.lock_row_exception('FIN_OBLIG_END_DATE',tlinfo.FIN_OBLIG_END_DATE);
522 END IF;
523
524 IF NOT ((tlinfo.CUSTOMER_ID = X_CUSTOMER_ID)
525 OR ((tlinfo.CUSTOMER_ID IS NULL) AND (X_CUSTOMER_ID IS NULL))) THEN
526 pn_var_rent_pkg.lock_row_exception('CUSTOMER_ID',tlinfo.CUSTOMER_ID);
527 END IF;
528
529 IF NOT ((tlinfo.CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID)
530 OR ((tlinfo.CUSTOMER_SITE_USE_ID IS NULL) AND (X_CUSTOMER_SITE_USE_ID IS NULL))) THEN
531 pn_var_rent_pkg.lock_row_exception('CUSTOMER_SITE_USE_ID',tlinfo.CUSTOMER_SITE_USE_ID);
532 END IF;
533
534 IF NOT ((tlinfo.LEASE_RENTABLE_AREA = X_LEASE_RENTABLE_AREA)
535 OR ((tlinfo.LEASE_RENTABLE_AREA IS NULL) AND (X_LEASE_RENTABLE_AREA IS NULL))) THEN
536 pn_var_rent_pkg.lock_row_exception('LEASE_RENTABLE_AREA',tlinfo.LEASE_RENTABLE_AREA);
537 END IF;
538
539 IF NOT ((tlinfo.LEASE_USABLE_AREA = X_LEASE_USABLE_AREA)
540 OR ((tlinfo.LEASE_USABLE_AREA IS NULL) AND (X_LEASE_USABLE_AREA IS NULL))) THEN
541 pn_var_rent_pkg.lock_row_exception('LEASE_USABLE_AREA',tlinfo.LEASE_USABLE_AREA);
542 END IF;
543
544 IF NOT ((tlinfo.LEASE_ASSIGNABLE_AREA = X_LEASE_ASSIGNABLE_AREA)
545 OR ((tlinfo.LEASE_ASSIGNABLE_AREA IS NULL) AND (X_LEASE_ASSIGNABLE_AREA IS NULL))) THEN
546 pn_var_rent_pkg.lock_row_exception('LEASE_ASSIGNABLE_AREA',tlinfo.LEASE_ASSIGNABLE_AREA);
547 END IF;
548
549 IF NOT ((tlinfo.LEASE_LOAD_FACTOR = X_LEASE_LOAD_FACTOR)
550 OR ((tlinfo.LEASE_LOAD_FACTOR IS NULL) AND (X_LEASE_LOAD_FACTOR IS NULL))) THEN
551 pn_var_rent_pkg.lock_row_exception('LEASE_LOAD_FACTOR',tlinfo.LEASE_LOAD_FACTOR);
552 END IF;
553
554 pnp_debug_pkg.debug('PN_TENANCIES_PKG.LOCK_ROW (-)');
555
556 END Lock_Row;
557
558 -------------------------------------------------------------------------------
559 -- PROCDURE : Update_Row
560 -- INVOKED FROM : Update_Row procedure
561 -- PURPOSE : updates the row
562 -- HISTORY :
563 -- 11-SEP-02 STripathi o If returnStatus = 'W', return parameter
564 -- x_tenancy_ovelap_wrn 'Y' for Multi-Tenancy-Lease changes.
565 -- 16-JAN-02 Pooja Sidhu o bug#2730279 - Removed call to
566 -- pn_tenancies_pkg.check_unique_primary_location.
567 -- 04-DEC-04 ftanudja o Added 8 parameters for lease rentable area. 3257508.
568 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_tenancies with _ALL table.
569 -------------------------------------------------------------------------------
570 PROCEDURE Update_Row
571 (
572 X_TENANCY_ID IN NUMBER,
573 X_LOCATION_ID IN NUMBER,
574 X_LEASE_ID IN NUMBER,
575 X_LEASE_CHANGE_ID IN NUMBER,
576 X_TENANCY_USAGE_LOOKUP_CODE IN VARCHAR2,
577 X_PRIMARY_FLAG IN VARCHAR2,
578 X_ESTIMATED_OCCUPANCY_DATE IN DATE,
579 X_OCCUPANCY_DATE IN DATE,
580 X_EXPIRATION_DATE IN DATE,
581 X_ASSIGNABLE_FLAG IN VARCHAR2,
585 X_ALLOCATED_AREA IN NUMBER,
582 X_SUBLEASEABLE_FLAG IN VARCHAR2,
583 X_TENANTS_PROPORTIONATE_SHARE IN NUMBER,
584 X_ALLOCATED_AREA_PCT IN NUMBER,
586 X_STATUS IN VARCHAR2,
587 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
588 X_ATTRIBUTE1 IN VARCHAR2,
589 X_ATTRIBUTE2 IN VARCHAR2,
590 X_ATTRIBUTE3 IN VARCHAR2,
591 X_ATTRIBUTE4 IN VARCHAR2,
592 X_ATTRIBUTE5 IN VARCHAR2,
593 X_ATTRIBUTE6 IN VARCHAR2,
594 X_ATTRIBUTE7 IN VARCHAR2,
595 X_ATTRIBUTE8 IN VARCHAR2,
596 X_ATTRIBUTE9 IN VARCHAR2,
597 X_ATTRIBUTE10 IN VARCHAR2,
598 X_ATTRIBUTE11 IN VARCHAR2,
599 X_ATTRIBUTE12 IN VARCHAR2,
600 X_ATTRIBUTE13 IN VARCHAR2,
601 X_ATTRIBUTE14 IN VARCHAR2,
602 X_ATTRIBUTE15 IN VARCHAR2,
603 X_LAST_UPDATE_DATE IN DATE,
604 X_LAST_UPDATED_BY IN NUMBER,
605 X_LAST_UPDATE_LOGIN IN NUMBER,
606 X_TENANCY_OVELAP_WRN OUT NOCOPY VARCHAR2,
607 X_RECOVERY_TYPE_CODE IN VARCHAR2,
608 X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
609 X_FIN_OBLIG_END_DATE IN DATE,
610 X_CUSTOMER_ID IN NUMBER,
611 X_CUSTOMER_SITE_USE_ID IN NUMBER,
612 X_LEASE_RENTABLE_AREA IN NUMBER,
613 X_LEASE_USABLE_AREA IN NUMBER,
614 X_LEASE_ASSIGNABLE_AREA IN NUMBER,
615 X_LEASE_LOAD_FACTOR IN NUMBER,
616 X_LOCATION_RENTABLE_AREA IN NUMBER,
617 X_LOCATION_USABLE_AREA IN NUMBER,
618 X_LOCATION_ASSIGNABLE_AREA IN NUMBER,
619 X_LOCATION_LOAD_FACTOR IN NUMBER
620 )
621 IS
622
623 CURSOR c2 IS
624 SELECT *
625 FROM pn_tenancies_all
626 WHERE tenancy_id = x_tenancy_id;
627
628 recInfoForHist c2%ROWTYPE;
629
630 l_leaseStatus VARCHAR2(30) := NULL;
631 l_returnStatus VARCHAR2(30) := NULL;
632 BEGIN
633
634 pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_ROW (+)');
635
636 x_tenancy_ovelap_wrn := NULL;
637
638 ---------------------------------------------------
639 -- Check tenancy dates
640 ---------------------------------------------------
641 l_returnStatus := NULL;
642 PN_TENANCIES_PKG.CHECK_TENANCY_DATES
643 (
644 l_returnStatus
645 ,X_ESTIMATED_OCCUPANCY_DATE
646 ,X_OCCUPANCY_DATE
647 ,X_EXPIRATION_DATE
648 );
649 IF (l_returnStatus IS NOT NULL) THEN
650 app_exception.Raise_Exception;
651 END IF;
652
653 ---------------------------------------------------
654 -- Check IF the tenancy dates overlap
655 ---------------------------------------------------
656 l_returnStatus := NULL;
657 PN_TENANCIES_PKG.CHECK_FOR_OVELAP_OF_TENANCY
658 (
659 l_returnStatus
660 ,X_TENANCY_ID
661 ,X_LOCATION_ID
662 ,X_LEASE_ID
663 ,X_ESTIMATED_OCCUPANCY_DATE
664 ,X_OCCUPANCY_DATE
665 ,X_EXPIRATION_DATE
666 );
667 IF (l_returnStatus = 'W') THEN
668 x_tenancy_ovelap_wrn := 'Y';
669 ELSIF (l_returnStatus IS NOT NULL) THEN
670 app_exception.Raise_Exception;
671 END IF;
672
673 ----------------------------------------------------
674 -- get the lease status
675 ----------------------------------------------------
676 l_leaseStatus := PNP_UTIL_FUNC.GET_LEASE_STATUS (X_LEASE_ID);
677
678 ---------------------------------------------------------------
679 -- We need to INsert the history row IF the lease IS finalised
680 ---------------------------------------------------------------
681 IF (l_leaseStatus = 'F') THEN
682
683 OPEN c2;
684 FETCH c2 INTO recInfoForHist;
685 IF (c2%NOTFOUND) THEN
686 CLOSE c2;
687 RAISE NO_DATA_FOUND;
688 END IF;
689 CLOSE c2;
690
691 IF (recInfoForHist.lease_change_id <> x_lease_change_id) THEN
692
693 INSERT INTO PN_TENANCIES_HISTORY
694 (
695 TENANCY_HISTORY_ID,
696 TENANCY_ID,
697 LAST_UPDATE_DATE,
698 LAST_UPDATED_BY,
699 CREATION_DATE,
700 CREATED_BY,
701 LAST_UPDATE_LOGIN,
702 LOCATION_ID,
703 LEASE_ID,
704 LEASE_CHANGE_ID,
705 NEW_LEASE_CHANGE_ID,
706 TENANCY_USAGE_LOOKUP_CODE,
707 PRIMARY_FLAG,
708 ESTIMATED_OCCUPANCY_DATE,
709 OCCUPANCY_DATE,
710 EXPIRATION_DATE,
711 ASSIGNABLE_FLAG,
712 SUBLEASEABLE_FLAG,
713 TENANTS_PROPORTIONATE_SHARE,
714 STATUS,
715 ATTRIBUTE_CATEGORY,
716 ATTRIBUTE1,
717 ATTRIBUTE2,
718 ATTRIBUTE3,
719 ATTRIBUTE4,
720 ATTRIBUTE5,
721 ATTRIBUTE6,
725 ATTRIBUTE10,
722 ATTRIBUTE7,
723 ATTRIBUTE8,
724 ATTRIBUTE9,
726 ATTRIBUTE11,
727 ATTRIBUTE12,
728 ATTRIBUTE13,
729 ATTRIBUTE14,
730 ATTRIBUTE15,
731 ORG_ID,
732 RECOVERY_TYPE_CODE,
733 RECOVERY_SPACE_STD_CODE,
734 FIN_OBLIG_END_DATE,
735 CUSTOMER_ID,
736 CUSTOMER_SITE_USE_ID,
737 LEASE_RENTABLE_AREA,
738 LEASE_USABLE_AREA,
739 LEASE_ASSIGNABLE_AREA,
740 LEASE_LOAD_FACTOR,
741 LOCATION_RENTABLE_AREA,
742 LOCATION_USABLE_AREA,
743 LOCATION_ASSIGNABLE_AREA,
744 LOCATION_LOAD_FACTOR
745 )
746 VALUES
747 (
748 pn_tenancies_history_s.NEXTVAL,
749 recInfoForHist.TENANCY_ID,
750 recInfoForHist.LAST_UPDATE_DATE,
751 recInfoForHist.LAST_UPDATED_BY,
752 recInfoForHist.CREATION_DATE,
753 recInfoForHist.CREATED_BY,
754 recInfoForHist.LAST_UPDATE_LOGIN,
755 recInfoForHist.LOCATION_ID,
756 recInfoForHist.LEASE_ID,
757 recInfoForHist.LEASE_CHANGE_ID,
758 X_LEASE_CHANGE_ID,
759 recInfoForHist.TENANCY_USAGE_LOOKUP_CODE,
760 recInfoForHist.PRIMARY_FLAG,
761 recInfoForHist.ESTIMATED_OCCUPANCY_DATE,
762 recInfoForHist.OCCUPANCY_DATE,
763 recInfoForHist.EXPIRATION_DATE,
764 recInfoForHist.ASSIGNABLE_FLAG,
765 recInfoForHist.SUBLEASEABLE_FLAG,
766 recInfoForHist.TENANTS_PROPORTIONATE_SHARE,
767 recInfoForHist.STATUS,
768 recInfoForHist.ATTRIBUTE_CATEGORY,
769 recInfoForHist.ATTRIBUTE1,
770 recInfoForHist.ATTRIBUTE2,
771 recInfoForHist.ATTRIBUTE3,
772 recInfoForHist.ATTRIBUTE4,
773 recInfoForHist.ATTRIBUTE5,
774 recInfoForHist.ATTRIBUTE6,
775 recInfoForHist.ATTRIBUTE7,
776 recInfoForHist.ATTRIBUTE8,
777 recInfoForHist.ATTRIBUTE9,
778 recInfoForHist.ATTRIBUTE10,
779 recInfoForHist.ATTRIBUTE11,
780 recInfoForHist.ATTRIBUTE12,
781 recInfoForHist.ATTRIBUTE13,
782 recInfoForHist.ATTRIBUTE14,
783 recInfoForHist.ATTRIBUTE15,
784 recInfoForHist.ORG_ID,
785 recInfoForHist.RECOVERY_TYPE_CODE,
786 recInfoForHist.RECOVERY_SPACE_STD_CODE,
787 recInfoForHist.FIN_OBLIG_END_DATE,
788 recInfoForHist.CUSTOMER_ID,
789 recInfoForHist.CUSTOMER_SITE_USE_ID,
790 recInfoForHist.LEASE_RENTABLE_AREA,
791 recInfoForHist.LEASE_USABLE_AREA,
792 recInfoForHist.LEASE_ASSIGNABLE_AREA,
793 recInfoForHist.LEASE_LOAD_FACTOR,
794 recInfoForHist.LOCATION_RENTABLE_AREA,
795 recInfoForHist.LOCATION_USABLE_AREA,
796 recInfoForHist.LOCATION_ASSIGNABLE_AREA,
797 recInfoForHist.LOCATION_LOAD_FACTOR
798 );
799 END IF;
800 END IF;
801
802 UPDATE pn_tenancies_all
803 SET LOCATION_ID = X_LOCATION_ID,
804 LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
805 TENANCY_USAGE_LOOKUP_CODE = X_TENANCY_USAGE_LOOKUP_CODE,
806 PRIMARY_FLAG = X_PRIMARY_FLAG,
807 ESTIMATED_OCCUPANCY_DATE = X_ESTIMATED_OCCUPANCY_DATE,
808 OCCUPANCY_DATE = X_OCCUPANCY_DATE,
809 EXPIRATION_DATE = X_EXPIRATION_DATE,
810 ASSIGNABLE_FLAG = X_ASSIGNABLE_FLAG,
811 SUBLEASEABLE_FLAG = X_SUBLEASEABLE_FLAG,
812 TENANTS_PROPORTIONATE_SHARE = X_TENANTS_PROPORTIONATE_SHARE,
813 ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT,
814 ALLOCATED_AREA = X_ALLOCATED_AREA,
815 STATUS = X_STATUS,
816 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
817 ATTRIBUTE1 = X_ATTRIBUTE1,
818 ATTRIBUTE2 = X_ATTRIBUTE2,
819 ATTRIBUTE3 = X_ATTRIBUTE3,
820 ATTRIBUTE4 = X_ATTRIBUTE4,
821 ATTRIBUTE5 = X_ATTRIBUTE5,
822 ATTRIBUTE6 = X_ATTRIBUTE6,
823 ATTRIBUTE7 = X_ATTRIBUTE7,
824 ATTRIBUTE8 = X_ATTRIBUTE8,
825 ATTRIBUTE9 = X_ATTRIBUTE9,
826 ATTRIBUTE10 = X_ATTRIBUTE10,
827 ATTRIBUTE11 = X_ATTRIBUTE11,
828 ATTRIBUTE12 = X_ATTRIBUTE12,
829 ATTRIBUTE13 = X_ATTRIBUTE13,
830 ATTRIBUTE14 = X_ATTRIBUTE14,
831 ATTRIBUTE15 = X_ATTRIBUTE15,
832 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
833 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
834 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
835 RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE,
836 RECOVERY_SPACE_STD_CODE = X_RECOVERY_SPACE_STD_CODE,
837 FIN_OBLIG_END_DATE = X_FIN_OBLIG_END_DATE,
841 LEASE_USABLE_AREA = X_LEASE_USABLE_AREA,
838 CUSTOMER_ID = X_CUSTOMER_ID,
839 CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID,
840 LEASE_RENTABLE_AREA = X_LEASE_RENTABLE_AREA,
842 LEASE_ASSIGNABLE_AREA = X_LEASE_ASSIGNABLE_AREA,
843 LEASE_LOAD_FACTOR = X_LEASE_LOAD_FACTOR,
844 LOCATION_RENTABLE_AREA = X_LOCATION_RENTABLE_AREA,
845 LOCATION_USABLE_AREA = X_LOCATION_USABLE_AREA,
846 LOCATION_ASSIGNABLE_AREA = X_LOCATION_ASSIGNABLE_AREA,
847 LOCATION_LOAD_FACTOR = X_LOCATION_LOAD_FACTOR
848 WHERE TENANCY_ID = X_TENANCY_ID ;
849
850 IF (SQL%NOTFOUND) THEN
851 RAISE NO_DATA_FOUND;
852 END IF;
853
854 pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_ROW (-)');
855 END Update_Row;
856
857 -------------------------------------------------------------------------------
858 -- PROCDURE : Delete_Row
859 -- INVOKED FROM : Delete_Row procedure
860 -- PURPOSE : deletes the row
861 -- HISTORY :
862 -------------------------------------------------------------------------------
863 PROCEDURE Delete_Row
864 (
865 X_TENANCY_ID IN NUMBER
866 )
867 IS
868 BEGIN
869 pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_ROW (+)');
870
871 DELETE FROM pn_tenancies_all
872 WHERE tenancy_id = x_tenancy_id;
873
874 IF (SQL%NOTFOUND) THEN
875 RAISE NO_DATA_FOUND;
876 END IF;
877
878 pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_ROW (-)');
879 END Delete_Row;
880
881
882 -------------------------------------------------------------------------------
883 -- PROCDURE : CHECK_UNIQUE_PRIMARY_LOCATION
884 -- INVOKED FROM :
885 -- PURPOSE : checks unique primary location
886 -- HISTORY :
887 -------------------------------------------------------------------------------
888 PROCEDURE CHECK_UNIQUE_PRIMARY_LOCATION (
889 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2
890 ,X_LEASE_ID IN NUMBER
891 ,X_TENANCY_ID IN NUMBER
892 )
893 IS
894 l_dummy NUMBER;
895 BEGIN
896 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHECK_UNIQUE_PRIMARY_LOCATION (+)');
897 BEGIN
898 SELECT 1
899 INTO l_dummy
900 FROM DUAL
901 WHERE NOT EXISTS
902 (SELECT 1
903 FROM pn_tenancies_all pnt
904 WHERE pnt.lease_id = x_lease_id
905 AND pnt.status = 'A'
906 AND pnt.primary_flag = 'Y'
907 AND ((x_tenancy_id IS NULL) or (pnt.tenancy_id <> x_tenancy_id))
908 );
909
910 EXCEPTION
911 WHEN NO_DATA_FOUND THEN
912 fnd_message.set_name ('PN', 'PN_DUPLEASE_PRIMARY_TENANCY');
913 x_return_status := 'E';
914 END;
915
916 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHECK_UNIQUE_PRIMARY_LOCATION (-)');
917 END CHECK_UNIQUE_PRIMARY_LOCATION;
918
919 -------------------------------------------------------------------------------
920 -- PROCDURE : CHECK_FOR_OVELAP_OF_TENANCY
921 -- INVOKED FROM :
922 -- PURPOSE : checks for overlap of tenancy
923 -- HISTORY :
924 -- 11-SEP-02 STripathi o If profile option PN_MULTIPLE_LEASE_FOR_LOCATION
925 -- is true, return_status should be W (warn) else
926 -- return_status is E (error) in Overlap exception
927 -- for Multi-Tenancy-Lease changes.
928 -- 30 OCT-02 graghuna o added group by to location code select.
929 -- 08-JUL-03 AKumar o Replaced calls to fnd_profile.get_value with
930 -- pn_mo_cache_utils.get_profile_value
931 -- 28-nov-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
932 -------------------------------------------------------------------------------
933 PROCEDURE CHECK_FOR_OVELAP_OF_TENANCY
934 (
935 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2
936 ,X_TENANCY_ID IN NUMBER
937 ,X_LOCATION_ID IN NUMBER
938 ,X_LEASE_ID IN NUMBER
939 ,X_ESTIMATED_OCCUPANCY_DATE IN DATE
940 ,X_OCCUPANCY_DATE IN DATE
941 ,X_EXPIRATION_DATE IN DATE
942 )
943 IS
944
945 l_dummy NUMBER;
946 l_locationCode VARCHAR2(255) := NULL;
947 l_leaseNumber VARCHAR2(30) := NULL;
948 l_parentLeaseId NUMBER := NULL;
949 l_LeaseId NUMBER := NULL;
950
951 CURSOR org_cur IS
952 SELECT org_id
953 FROM pn_leases_all pnl
954 WHERE pnl.lease_id = x_lease_id;
955
956 l_org_id NUMBER;
957
958 BEGIN
959 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHECK_FOR_OVELAP_OF_TENANCY (+)');
960
961 FOR rec IN org_cur LOOP
962 l_org_id := rec.org_id;
963 END LOOP;
964
965 -- we are selecting the location code for err condition
966 SELECT location_code
967 INTO l_locationCode
968 FROM pn_locations_all
969 WHERE location_id = x_location_id
970 AND ROWNUM < 2;
971
972 -- we need to find IF the lease has a master lease defined
976 WHERE pnl.lease_id = x_lease_id;
973 SELECT pnl.parent_lease_id
974 INTO l_parentLeaseId
975 FROM pn_leases_all pnl
977
978 ----------------------------------------------------------------
979 -- i.e. no master lease has been defined THEN it's a vanilla lease
980 ----------------------------------------------------------------
981
982 IF (l_parentLeaseId IS NULL) THEN
983
984 --------------------------------------------------------------
985 -- Check IF a parent location IS already tied to the lease IN
986 -- question AND the user tries to hookup a child location to the
987 -- same lease for the same time period. Issue an error. Bug: 920404
988 ---------------------------------------------------------------
989 BEGIN
990
991 SELECT pnt.lease_id
992 INTO l_LeaseId
993 FROM pn_tenancies_all pnt
994 WHERE pnt.status = 'A'
995 AND pnt.lease_id = x_lease_id
996 AND pnt.location_id IN
997 (SELECT b.parent_location_id
998 FROM pn_locations_all b
999 CONNECT BY b.location_id = PRIOR parent_location_id
1000 START WITH b.location_id = x_location_id
1001 )
1002 AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1003 BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1004 AND TRUNC (x_expiration_date)
1005 OR TRUNC(pnt.expiration_date)
1006 BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1007 AND TRUNC (x_expiration_date)
1008 )
1009 AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id)
1010 )
1011 AND ROWNUM < 2;
1012
1013 fnd_message.set_name ('PN', 'PN_PARENT_LOC_IN_LEASE');
1014 x_return_status := 'E';
1015
1016 EXCEPTION
1017 WHEN NO_DATA_FOUND THEN -- IF no data was found THEN we don't worry
1018 NULL;
1019
1020 END;
1021
1022 ------------------------------------------------------------
1023 -- now check IF the dates clash for the location under any
1024 -- other lease other than this lease's sub-leases
1025 ------------------------------------------------------------
1026 BEGIN
1027
1028 SELECT lease_num
1029 INTO l_leaseNumber
1030 FROM pn_leases_all
1031 WHERE lease_id <> x_lease_id
1032 AND parent_lease_id IS NULL
1033 AND lease_id =
1034 (SELECT lease_id
1035 FROM pn_tenancies_all pnt
1036 WHERE pnt.status = 'A'
1037 AND pnt.location_id IN
1038 (SELECT a.location_id
1039 FROM pn_locations_all a
1040 CONNECT BY PRIOR a.parent_location_id = a.location_id
1041 START WITH a.location_id = x_location_id
1042 UNION ALL
1043 SELECT b.location_id
1044 FROM pn_locations_all b
1045 CONNECT BY PRIOR b.location_id = b.parent_location_id
1046 START WITH b.location_id = x_location_id
1047 )
1048 AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1049 BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1050 AND TRUNC (x_expiration_date)
1051 OR TRUNC(pnt.expiration_date)
1052 BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1053 AND TRUNC (x_expiration_date)
1054 )
1055 AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id))
1056 AND ROWNUM < 2
1057 );
1058
1059
1060 IF nvl(pn_mo_cache_utils.get_profile_value('PN_MULTIPLE_LEASE_FOR_LOCATION',l_org_id),'N') <> 'Y' THEN
1061 fnd_message.set_name ('PN', 'PN_LEASE_TENANCY_OVERLAP');
1062 fnd_message.set_token ('LOCATION_CODE', l_locationCode);
1063 fnd_message.set_token ('LEASE_NUMBER', l_leaseNumber);
1064 x_return_status := 'E';
1065 ELSE
1066 x_return_status := 'W';
1067 END IF;
1068
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND THEN
1071 -- IF no data was found THEN we don't worry
1072 NULL;
1073 END;
1074
1075 IF (x_return_status IS NULL) THEN
1076 BEGIN
1077 -- now check IF the new dates will create problem
1078 -- with dates IN su-leases for this lease
1079
1080 SELECT lease_num
1081 INTO l_leaseNumber
1082 FROM pn_leases_all
1083 WHERE parent_lease_id = x_lease_id
1084 AND lease_id =
1085 (SELECT lease_id
1086 FROM pn_tenancies_all pnt
1087 WHERE pnt.status = 'A'
1088 AND pnt.location_id IN
1089 (SELECT b.location_id
1090 FROM pn_locations_all b
1091 CONNECT BY PRIOR b.location_id = b.parent_location_id
1095 < TRUNC(NVL(x_occupancy_date, x_estimated_occupancy_date))
1092 START WITH b.location_id = x_location_id
1093 )
1094 AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1096 OR TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1097 > TRUNC (x_expiration_date)
1098 OR TRUNC(pnt.expiration_date)
1099 < TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1100 OR TRUNC(pnt.expiration_date)
1101 > TRUNC (x_expiration_date)
1102 )
1103 AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id)
1104 )
1105 AND ROWNUM < 2
1106 );
1107
1108
1109 fnd_message.set_name ('PN', 'PN_LEASE_TENANCY_SUBLEASE_DT');
1110 fnd_message.set_token ('LOCATION_CODE', l_locationCode);
1111 fnd_message.set_token ('LEASE_NUMBER', l_leaseNumber);
1112 x_return_status := 'E';
1113
1114 EXCEPTION
1115 WHEN NO_DATA_FOUND THEN
1116 -- IF no data was found THEN we don't worry
1117 NULL;
1118 END;
1119 END IF;
1120
1121 -- i.e. master lease IS defined
1122 ELSE
1123 -- this SELECT will verify that the location EXISTS IN the
1124 -- parent lease AND the dates are within range
1125 BEGIN
1126 SELECT location_id
1127 INTO l_leaseNumber
1128 FROM pn_locations_all
1129 WHERE location_id = x_location_id
1130 AND location_id IN
1131 (SELECT b.location_id
1132 FROM pn_locations_all b
1133 CONNECT BY PRIOR b.location_id = b.parent_location_id
1134 START WITH b.location_id IN
1135 (SELECT pnt.location_id
1136 FROM pn_tenancies_all pnt
1137 WHERE pnt.status = 'A'
1138 AND pnt.lease_id = l_parentLeaseId
1139 AND (TRUNC(NVL(x_occupancy_date, x_estimated_occupancy_date))
1140 BETWEEN TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1141 AND TRUNC(pnt.expiration_date)
1142 AND TRUNC (x_expiration_date)
1143 BETWEEN TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1144 AND TRUNC(pnt.expiration_date)
1145 )
1146 )
1147 )
1148 AND ROWNUM < 2;
1149
1150 -- IF data was found THEN we don't worry
1151
1152 EXCEPTION
1153 WHEN NO_DATA_FOUND THEN
1154 fnd_message.set_name('PN', 'PN_LEASE_TENANCY_MASTER_NEXIST');
1155 fnd_message.set_token('LOCATION_CODE', l_locationCode);
1156 x_return_status := 'E';
1157 END;
1158
1159 -- only IF the location EXISTS IN the master and dates are
1160 -- perfect, now we need to check that it shouldn't overlap
1161 -- with any other lease or sublease
1162 IF (x_return_status IS NULL) THEN
1163
1164 BEGIN
1165 SELECT lease_num
1166 INTO l_leaseNumber
1167 FROM pn_leases_all
1168 WHERE lease_id =
1169 (SELECT lease_id
1170 FROM pn_tenancies_all pnt
1171 WHERE pnt.status = 'A'
1172 AND pnt.lease_id <> l_parentLeaseId
1173 AND pnt.location_id IN
1174 (SELECT a.location_id
1175 FROM pn_locations_all a
1176 CONNECT BY PRIOR a.parent_location_id = a.location_id
1177 START WITH a.location_id = x_location_id
1178 UNION ALL
1179 SELECT b.location_id
1180 FROM pn_locations_all b
1181 CONNECT BY PRIOR b.location_id = b.parent_location_id
1182 START WITH b.location_id = x_location_id
1183 )
1184 AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
1185 BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1186 AND TRUNC (x_expiration_date)
1187 OR TRUNC(pnt.expiration_date)
1188 BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
1189 AND TRUNC (x_expiration_date)
1190 )
1191 AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id))
1192 AND ROWNUM < 2
1193 );
1194
1195
1196 IF nvl(pn_mo_cache_utils.get_profile_value('PN_MULTIPLE_LEASE_FOR_LOCATION',l_org_id),'N') <> 'Y' THEN
1197 fnd_message.set_name ('PN', 'PN_LEASE_TENANCY_OVERLAP');
1198 fnd_message.set_token ('LOCATION_CODE', l_locationCode);
1199 fnd_message.set_token ('LEASE_NUMBER', l_leaseNumber);
1200 x_return_status := 'E';
1204
1201 ELSE
1202 x_return_status := 'W';
1203 END IF;
1205 EXCEPTION
1206 WHEN NO_DATA_FOUND THEN
1207 -- IF no data was found THEN we don't worry
1208 NULL;
1209 END;
1210 END IF;
1211
1212 END IF;
1213
1214 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHECK_FOR_OVELAP_OF_TENANCY (-) X_RETURN_STATUS: '||X_RETURN_STATUS);
1215
1216 END check_for_ovelap_of_tenancy;
1217
1218 -------------------------------------------------------------------------------
1219 -- PROCDURE : check_tenancy_dates
1220 -- INVOKED FROM :
1221 -- PURPOSE : checks the tenancy dates
1222 -- HISTORY :
1223 -------------------------------------------------------------------------------
1224 PROCEDURE check_tenancy_dates
1225 (
1226 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2
1227 ,X_ESTIMATED_OCCUPANCY_DATE IN DATE
1228 ,X_OCCUPANCY_DATE IN DATE
1229 ,X_EXPIRATION_DATE IN DATE
1230 )
1231 IS
1232 BEGIN
1233 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHECK_TENANCY_DATES (+)');
1234
1235 IF (X_ESTIMATED_OCCUPANCY_DATE IS NULL) THEN
1236 fnd_message.set_name('PN', 'PN_LEASE_TENANCY_EST_DT_NULL');
1237 x_return_status := 'E';
1238
1239 ELSIF (X_EXPIRATION_DATE IS NULL) THEN
1240 fnd_message.set_name ('PN', 'PN_LEASE_TENANCY_EXP_DT_NULL');
1241 x_return_status := 'E';
1242
1243 ELSIF (TRUNC(NVL(x_occupancy_date, x_expiration_date)) > TRUNC(x_expiration_date)) THEN
1244
1245 fnd_message.set_name ('PN', 'PN_LEASE_TENANCY_EXP_GT_OCP_DT');
1246 x_return_status := 'E';
1247 END IF;
1248
1249
1250 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHECK_TENANCY_DATES (-)');
1251 END check_tenancy_dates;
1252
1253 -------------------------------------------------------------------------------
1254 -- FUNCTION : get_loc_type_code
1255 -- INVOKED FROM :
1256 -- PURPOSE : Retrieves location code type
1257 -- HISTORY :
1258 -- 05-DEC-2003 Satish Tripathi o Created for BUG# 3300697.
1259 -------------------------------------------------------------------
1260 FUNCTION get_loc_type_code
1261 (
1262 p_location_id IN NUMBER
1263 ,p_start_date IN DATE
1264 )
1265 RETURN VARCHAR2
1266 IS
1267 CURSOR get_location_type_csr IS
1268 SELECT location_code,
1269 location_type_lookup_code
1270 FROM pn_locations_all pnl
1271 WHERE pnl.location_id = p_location_id
1272 AND p_start_date BETWEEN pnl.active_start_date AND pnl.active_end_date;
1273
1274 l_location_code pn_locations_all.location_code%TYPE;
1275 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
1276
1277 BEGIN
1278 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_LOC_TYPE_CODE (+)');
1279
1280 OPEN get_location_type_csr;
1281 FETCH get_location_type_csr INTO l_location_code, l_loc_type_code;
1282 CLOSE get_location_type_csr;
1283
1284 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_LOC_TYPE_CODE (-) Loc_Type_Code: '||l_loc_type_code);
1285
1286 RETURN l_loc_type_code;
1287
1288 END get_loc_type_code;
1289
1290 -------------------------------------------------------------------
1291 -- FUNCTION: cust_assign_assoc_exp_area_dt
1292 -- 08-APR-2004 Satish Tripathi o Fixed for BUG# 3284799, Modified CURSOR chk_locked_area_exp_det
1293 -- where clause of pn_space_assign_cust_all to select all space
1294 -- assignments between the p_start/end date to check if its locked.
1295 -------------------------------------------------------------------
1296 FUNCTION cust_assign_assoc_exp_area_dt(
1297 p_tenancy_id IN NUMBER
1298 ,p_chk_locked IN BOOLEAN
1299 ,p_cust_assign_start_dt IN DATE DEFAULT NULL
1300 ,p_cust_assign_end_dt IN DATE DEFAULT NULL
1301 )
1302 RETURN BOOLEAN
1303 IS
1304 CURSOR chk_assoc_exp_area_dtl IS
1305 SELECT 'Y'
1306 FROM DUAL
1307 WHERE EXISTS (SELECT NULL
1308 FROM pn_space_assign_cust_all spc
1309 WHERE spc.tenancy_id = p_tenancy_id
1310 AND (EXISTS (SELECT NULL
1311 FROM pn_rec_arcl_dtl_all mst,
1312 pn_rec_arcl_dtlln_all dtl
1313 WHERE mst.area_class_dtl_id = dtl.area_class_dtl_id
1314 AND dtl.cust_space_assign_id = spc.cust_space_assign_id) OR
1315 EXISTS (SELECT NULL
1316 FROM pn_rec_expcl_dtl_all mst,
1317 pn_rec_expcl_dtlln_all dtl
1318 WHERE mst.expense_class_dtl_id = dtl.expense_class_dtl_id
1319 AND dtl.cust_space_assign_id = spc.cust_space_assign_id))
1320 );
1321
1322 CURSOR chk_locked_area_exp_det IS
1323 SELECT 'Y'
1324 FROM DUAL
1325 WHERE EXISTS (SELECT NULL
1326 FROM pn_space_assign_cust_all spc
1327 WHERE spc.tenancy_id = p_tenancy_id
1331 FROM pn_rec_arcl_dtl_all mst,
1328 AND (NVL(spc.cust_assign_end_date,p_cust_assign_end_dt) >= p_cust_assign_start_dt OR
1329 spc.cust_assign_start_date <= p_cust_assign_end_dt)
1330 AND (EXISTS (SELECT NULL
1332 pn_rec_arcl_dtlln_all dtl
1333 WHERE mst.area_class_dtl_id = dtl.area_class_dtl_id
1334 AND mst.status = 'LOCKED'
1335 AND dtl.cust_space_assign_id = spc.cust_space_assign_id) OR
1336 EXISTS (SELECT NULL
1337 FROM pn_rec_expcl_dtl_all mst,
1338 pn_rec_expcl_dtlln_all dtl
1339 WHERE mst.expense_class_dtl_id = dtl.expense_class_dtl_id
1340 AND mst.status = 'LOCKED'
1341 AND dtl.cust_space_assign_id = spc.cust_space_assign_id))
1342 );
1343
1344 l_exists VARCHAR2(1) :='N';
1345 l_return BOOLEAN := FALSE;
1346
1347 BEGIN
1348 IF p_chk_locked THEN
1349 OPEN chk_locked_area_exp_det;
1350 FETCH chk_locked_area_exp_det INTO l_exists;
1351 CLOSE chk_locked_area_exp_det;
1352 ELSE
1353 OPEN chk_assoc_exp_area_dtl;
1354 FETCH chk_assoc_exp_area_dtl INTO l_exists;
1355 CLOSE chk_assoc_exp_area_dtl;
1356 END IF;
1357
1358 IF l_exists = 'Y' THEN
1359 l_return := TRUE;
1360 END IF;
1361 RETURN l_return;
1362
1363 END cust_assign_assoc_exp_area_dt;
1364
1365 -------------------------------------------------------------------
1366 -- PROCEDURE : GET_LOC_INFO
1367 -- DESCRIPTION: o populate loc_info_tbl with rows from pn_locations_all for a given location
1368 -- o if the assignable area for a location has not changed but the location
1369 -- was split the location records would be treated as a single record.
1370 -- Example:
1371 -- Rows in pn_locations_all
1372 -- active_st_dt active_end_dt assignable_area
1373 -- 01-JAN-00 31-DEC-00 1000
1374 -- 01-JAN-01 30-JUN-01 1000
1375 -- 01-JUL-00 31-DEC-01 2000
1376 --
1377 -- The following rows will be inserted in loc_info_tbl
1378 -- active_st_dt active_end_dt assignable_area
1379 -- 01-JAN-00 30-JUN-01 1000
1380 -- 01-JUL-00 31-DEC-01 2000
1381 --
1382 -- 10-JUN-2003 Pooja Sidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
1383 -- 26-AUG-2003 Satish Tripathi o Fixed for BUG# 3085758, Modified Where clause of CURSOR csr_loc_info
1384 -- with <= and >= instead of < and > to pick all locations within
1385 -- p_from_date and p_to_date.
1386 -------------------------------------------------------------------
1387
1388 PROCEDURE get_loc_info(
1389 p_location_id IN NUMBER
1390 ,p_from_date IN DATE
1391 ,p_to_date IN DATE
1392 ,p_loc_type_code OUT NOCOPY VARCHAR2
1393 )
1394 IS
1395 CURSOR csr_loc_info IS
1396 SELECT active_start_date,
1397 NVL(active_end_date, p_to_date) active_end_date,
1398 assignable_area,
1399 location_type_lookup_code
1400 FROM pn_locations_all
1401 WHERE location_id = p_location_id
1402 AND active_start_date <= p_to_date
1403 AND NVL(active_end_date, p_to_date) >= p_from_date
1404 ORDER BY active_start_date;
1405
1406 l_prior_assignable_area pn_locations_all.assignable_area%TYPE:=0;
1407 i NUMBER := 0;
1408
1409 BEGIN
1410 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_LOC_INFO (+)');
1411
1412 loc_info_tbl.delete;
1413
1414 FOR rec_loc_info IN csr_loc_info
1415 LOOP
1416 p_loc_type_code := rec_loc_info.location_type_lookup_code;
1417
1418 IF csr_loc_info%ROWCOUNT = 1 THEN
1419 loc_info_tbl(i).active_start_date := rec_loc_info.active_start_date;
1420 loc_info_tbl(i).active_end_date := rec_loc_info.active_end_date;
1421 loc_info_tbl(i).assignable_area := rec_loc_info.assignable_area;
1422 ELSE
1423 IF rec_loc_info.assignable_area = l_prior_assignable_area THEN
1424 loc_info_tbl(i).active_end_date := rec_loc_info.active_end_date;
1425 ELSE
1426 i := i + 1;
1427 loc_info_tbl(i).active_start_date := rec_loc_info.active_start_date;
1428 loc_info_tbl(i).active_end_date := rec_loc_info.active_end_date;
1429 loc_info_tbl(i).assignable_area := rec_loc_info.assignable_area;
1430 END IF;
1431 END IF;
1432 l_prior_assignable_area := rec_loc_info.assignable_area;
1433
1434 END LOOP;
1435 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_LOC_INFO (-)');
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 pnp_debug_pkg.log('Get_loc_info - Errmsg: ' || sqlerrm);
1440 RAISE;
1441
1442 END get_loc_info;
1443
1444 -------------------------------------------------------------------
1448 -- 10-JUN-2003 Pooja Sidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
1445 -- PROCEDURE : GET_ALLOCATED_AREA_PCT
1446 -- DESCRIPTION:
1447 --
1449 -------------------------------------------------------------------
1450 PROCEDURE get_allocated_area_pct(
1451 p_cust_assign_start_date IN DATE
1452 ,p_cust_assign_end_date IN DATE
1453 ,p_allocated_area IN NUMBER
1454 ,p_alloc_area_pct OUT NOCOPY NUMBER
1455 )
1456 IS
1457 i NUMBER := 0;
1458 l_min_area NUMBER := -1;
1459 BEGIN
1460 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_ALLOCATED_AREA_PCT (+)');
1461
1462 p_alloc_area_pct := -1;
1463 FOR i IN 0 .. loc_info_tbl.count-1
1464 LOOP
1465 IF (p_cust_assign_start_date >= loc_info_tbl(i).active_start_date AND
1466 p_cust_assign_end_date <= loc_info_tbl(i).active_end_date)
1467 OR
1468 (p_cust_assign_start_date <= loc_info_tbl(i).active_end_date AND
1469 p_cust_assign_end_date >= loc_info_tbl(i).active_start_date)
1470 THEN
1471 IF p_allocated_area = 0 and loc_info_tbl(i).assignable_area = 0 THEN
1472 p_alloc_area_pct:= 100;
1473 ELSE
1474
1475 IF i = 0 OR loc_info_tbl(i).assignable_area < l_min_area THEN
1476 l_min_area := loc_info_tbl(i).assignable_area;
1477 END IF;
1478 END IF;
1479 END IF;
1480 END LOOP;
1481
1482 IF p_alloc_area_pct < 0 THEN
1483 p_alloc_area_pct := ROUND(((p_allocated_area * 100 )/l_min_area),2);
1484 END IF;
1485
1486 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_ALLOCATED_AREA_PCT (-)');
1487 END get_allocated_area_pct;
1488
1489 -------------------------------------------------------------------
1490 -- PROCEDURE : POPULATE_SPACE_ASSIGN_INFO
1491 -- DESCRIPTION: o Calls procedure pn_recovery_extract_pkg.process_vacancy to
1492 -- get available allocable area. The assignment record could be split
1493 -- based on the number of space and emp assignments existing for
1494 -- the location and if the assignable area has changed.
1495 -- o Populates space_assign_info_tbl with the start date, end date and
1496 -- allocated area that will be used to create space assignment record.
1497 --
1498 -- 10-JUN-2003 Pooja Sidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
1499 -- 05-AUG-2003 Satish Tripathi o Fixed for BUG# 3082056. Populate
1500 -- populate_space_assign_info for SECTION also.
1501 -- 22-AUG-2003 Satish Tripathi o Fixed for BUG# 3085758, Added parameter p_fin_oblig_end_date.
1502 -- For last space assignment record fin_oblig_end_date is
1503 -- p_fin_oblig_end_date, for others it will be cust_assign_end_date.
1504 -- 07-NOV-2003 Daniel Thota o Fix for bug # 3242535
1505 -- assigned l_loc_type_code to p_loc_type_code
1506 -------------------------------------------------------------------
1507 PROCEDURE populate_space_assign_info(
1508 p_location_id IN NUMBER
1509 ,p_from_date IN DATE
1510 ,p_to_date IN DATE
1511 ,p_fin_oblig_end_date IN DATE
1512 ,p_loc_type_code OUT NOCOPY VARCHAR2
1513 )
1514 IS
1515 CURSOR csr_cust_info IS
1516 SELECT cust_assign_start_date,
1517 NVL(cust_assign_end_date, p_to_date) cust_assign_end_date,
1518 allocated_area
1519 FROM pn_space_assign_cust_all
1520 WHERE location_id = p_location_id
1521 AND cust_assign_start_date <= p_to_date
1522 AND NVL(cust_assign_end_date, p_to_date) >= p_from_date;
1523
1524 CURSOR csr_emp_info IS
1525 SELECT emp_assign_start_date,
1526 NVL(emp_assign_end_date, p_to_date) emp_assign_end_date,
1527 allocated_area
1528 FROM pn_space_assign_emp_all
1529 WHERE location_id = p_location_id
1530 AND emp_assign_start_date <= p_to_date
1531 AND NVL(emp_assign_end_date, p_to_date) >= p_from_date;
1532
1533 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
1534 l_num_table pn_recovery_extract_pkg.number_table_TYPE;
1535 l_date_table pn_recovery_extract_pkg.date_table_TYPE;
1536 l_start_date DATE := NULL;
1537 l_end_date DATE := NULL;
1538 i NUMBER := 0;
1539 j NUMBER := 0;
1540
1541 BEGIN
1542 pnp_debug_pkg.debug('PN_TENANCIES_PKG.POPULATE_SPACE_ASSIGN_INFO (+)');
1543
1544 get_loc_info(p_location_id => p_location_id,
1545 p_from_date => p_from_date,
1546 p_to_date => p_to_date,
1547 p_loc_type_code => l_loc_type_code);
1548
1549 IF l_loc_type_code IN ('OFFICE', 'SECTION') THEN
1550
1551 FOR i IN 0 .. loc_info_tbl.count-1
1552 LOOP
1553 pn_recovery_extract_pkg.process_vacancy(
1554 p_start_date => loc_info_tbl(i).active_start_date,
1555 p_end_date => loc_info_tbl(i).active_end_date,
1556 p_area => loc_info_tbl(i).assignable_area,
1557 p_date_table => l_date_table,
1558 p_number_table => l_num_table,
1559 p_add => TRUE);
1560 END LOOP;
1564 pn_recovery_extract_pkg.process_vacancy(
1561
1562 FOR rec_cust_info IN csr_cust_info
1563 LOOP
1565 p_start_date => rec_cust_info.cust_assign_start_date,
1566 p_end_date => rec_cust_info.cust_assign_end_date,
1567 p_area => rec_cust_info.allocated_area,
1568 p_date_table => l_date_table,
1569 p_number_table => l_num_table,
1570 p_add => FALSE);
1571 END LOOP;
1572
1573 FOR rec_emp_info IN csr_emp_info
1574 LOOP
1575 pn_recovery_extract_pkg.process_vacancy(
1576 p_start_date => rec_emp_info.emp_assign_start_date,
1577 p_end_date => rec_emp_info.emp_assign_end_date,
1578 p_area => rec_emp_info.allocated_area,
1579 p_date_table => l_date_table,
1580 p_number_table => l_num_table,
1581 p_add => FALSE);
1582 END LOOP;
1583
1584 i := 0;
1585 space_assign_info_tbl.delete;
1586
1587 FOR i IN 0 .. l_date_table.count-1
1588 LOOP
1589 IF i = 0 THEN
1590 l_start_date := l_date_table(i);
1591 ELSE
1592 l_end_date := l_date_table(i)-1;
1593 IF l_end_date >= p_from_date and l_start_date <= p_to_date THEN
1594 space_assign_info_tbl(j).cust_assign_start_date := GREATEST(p_from_date, l_start_date);
1595 space_assign_info_tbl(j).cust_assign_end_date := LEAST(p_to_date, l_end_date);
1596
1597 IF i = l_date_table.count-1 THEN
1598 space_assign_info_tbl(j).fin_oblig_end_date := p_fin_oblig_end_date;
1599 ELSE
1600 space_assign_info_tbl(j).fin_oblig_end_date := LEAST(p_to_date, l_end_date);
1601 END IF;
1602
1603 space_assign_info_tbl(j).allocated_area := l_num_table(i-1);
1604 get_allocated_area_pct(
1605 p_cust_assign_start_date => space_assign_info_tbl(j).cust_assign_start_date,
1606 p_cust_assign_end_date => space_assign_info_tbl(j).cust_assign_end_date,
1607 p_allocated_area => space_assign_info_tbl(j).allocated_area,
1608 p_alloc_area_pct => space_assign_info_tbl(j).allocated_area_pct);
1609 l_start_date := l_date_table(i);
1610 j := j + 1;
1611 END IF;
1612 END IF;
1613 END LOOP;
1614
1615 ELSE
1616 space_assign_info_tbl(j).cust_assign_start_date := p_from_date;
1617 space_assign_info_tbl(j).cust_assign_end_date := p_to_date;
1618 space_assign_info_tbl(j).fin_oblig_end_date := p_fin_oblig_end_date;
1619 space_assign_info_tbl(j).allocated_area := NULL;
1620 space_assign_info_tbl(j).allocated_area_pct := NULL;
1621 END IF;
1622 p_loc_type_code := l_loc_type_code; -- 3242535
1623
1624 pnp_debug_pkg.debug('PN_TENANCIES_PKG.POPULATE_SPACE_ASSIGN_INFO (-)');
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 pnp_debug_pkg.log('Populate_space_assign_info - Errmsg: ' || sqlerrm);
1628 RAISE;
1629
1630 END populate_space_assign_info;
1631
1632 --------------------------------------------------------------------------------
1633 -- PROCEDURE : INSERT_SPACE_ASSIGN_ROW
1634 -- DESCRIPTION:
1635 --
1636 -- 10-JUN-03 PSidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
1637 -- 18-AUG-03 STripathi o Fixed for BUG# 3083849. Populate X_UTILIZED_AREA
1638 -- with default value 1.
1639 -- 22-AUG-03 STripathi o Fixed for BUG# 3085758, pass fin_oblig_end_date of PL/SQL
1640 -- table to x_fin_oblig_end_date.
1641 -- 08-NOV-03 STripathi o Fixed for BUG# 3242651. Call chk_dup_cust_assign to check
1642 -- duplicate assignment before insert_row pass DUP_ASSIGN in p_action.
1643 -- 05-MAR-04 ftanudja o Replaced call to chk_dup_cust_assign w/ exception handling.
1644 -- 28-Apr-04 vmmehta o BUG#3197182. Changed call to pn_space_assign_cust_pkg.insert_row
1645 -- Added parameter x_return_status and checking
1646 -- return_status rather than duplicate_exception.
1647 -------------------------------------------------------------------------------
1648 PROCEDURE insert_space_assign_row(
1649 p_location_id IN NUMBER
1650 ,p_lease_id IN NUMBER
1651 ,p_customer_id IN NUMBER
1652 ,p_cust_site_use_id IN NUMBER
1653 ,p_recovery_space_std_code IN VARCHAR2
1654 ,p_recovery_type_code IN VARCHAR2
1655 ,p_fin_oblig_end_date IN DATE
1656 ,p_tenancy_id IN NUMBER
1657 ,p_org_id IN NUMBER
1658 ,p_space_assign_info_tbl IN space_assign_info_type
1659 ,p_return_status OUT NOCOPY VARCHAR2
1660 )
1661 IS
1662 l_rowid ROWID := NULL;
1663 l_cust_space_assign_id NUMBER := NULL;
1664 i NUMBER := 0;
1665 BEGIN
1666 pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_SPACE_ASSIGN_ROW (+)');
1667
1668 i := 0;
1669 FOR i IN 0 .. p_space_assign_info_tbl.count-1
1670 LOOP
1671
1672 pn_space_assign_cust_pkg.insert_row(
1676 x_cust_account_id => p_customer_id,
1673 x_rowid => l_rowId,
1674 x_cust_space_assign_id => l_cust_space_assign_id,
1675 x_location_id => p_location_id,
1677 x_site_use_id => p_cust_site_use_id,
1678 x_expense_account_id => NULL,
1679 x_project_id => NULL,
1680 x_task_id => NULL,
1681 x_cust_assign_start_date => p_space_assign_info_tbl(i).cust_assign_start_date,
1682 x_cust_assign_end_date => p_space_assign_info_tbl(i).cust_assign_end_date,
1683 x_allocated_area_pct => p_space_assign_info_tbl(i).allocated_area_pct,
1684 x_allocated_area => p_space_assign_info_tbl(i).allocated_area,
1685 x_utilized_area => 1,
1686 x_fin_oblig_end_date => p_space_assign_info_tbl(i).fin_oblig_end_date,
1687 x_cust_space_comments => NULL,
1688 x_attribute_category => NULL,
1689 x_attribute1 => NULL,
1690 x_attribute2 => NULL,
1691 x_attribute3 => NULL,
1692 x_attribute4 => NULL,
1693 x_attribute5 => NULL,
1694 x_attribute6 => NULL,
1695 x_attribute7 => NULL,
1696 x_attribute8 => NULL,
1697 x_attribute9 => NULL,
1698 x_attribute10 => NULL,
1699 x_attribute11 => NULL,
1700 x_attribute12 => NULL,
1701 x_attribute13 => NULL,
1702 x_attribute14 => NULL,
1703 x_attribute15 => NULL,
1704 x_creation_date => SYSDATE,
1705 x_created_by => NVL(fnd_profile.value('USER_ID'),-1),
1706 x_last_update_date => SYSDATE,
1707 x_last_updated_by => NVL(fnd_profile.value('USER_ID'),-1),
1708 x_last_update_login => NVL(fnd_profile.value('USER_ID'),-1),
1709 x_org_id => p_org_id,
1710 x_lease_id => p_lease_id,
1711 x_recovery_space_std_code => p_recovery_space_std_code,
1712 x_recovery_type_code => p_recovery_type_code,
1713 x_tenancy_id => p_tenancy_id,
1714 x_return_status => p_return_status);
1715
1716 IF p_return_status = 'DUP_ASSIGN' THEN
1717 EXIT;
1718 END IF;
1719
1720 l_rowid := NULL;
1721 l_cust_space_assign_id := NULL;
1722 END LOOP;
1723
1724 pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_SPACE_ASSIGN_ROW (-)');
1725 END insert_space_assign_row;
1726
1727 -------------------------------------------------------------------
1728 -- PROCEDURE : GET_ALLOCATED_AREA
1729 -- DESCRIPTION:
1730 --
1731 -- 12-DEC-2006 Ram kumar o Created
1732 -------------------------------------------------------------------
1733 PROCEDURE get_allocated_area(
1734 p_cust_assign_start_date IN DATE
1735 ,p_cust_assign_end_date IN DATE
1736 ,p_allocated_area_pct IN NUMBER
1737 ,p_allocated_area OUT NOCOPY NUMBER
1738 )
1739 IS
1740 i NUMBER := 0;
1741 l_min_area NUMBER := -1;
1742 BEGIN
1743 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_ALLOCATED_AREA (+)');
1744
1745 p_allocated_area := -1;
1746 FOR i IN 0 .. loc_info_tbl.count-1
1747 LOOP
1748 IF p_cust_assign_start_date >= loc_info_tbl(i).active_start_date AND
1749 p_cust_assign_end_date <= loc_info_tbl(i).active_end_date
1750 THEN
1751 p_allocated_area:= ROUND(((p_allocated_area_pct * loc_info_tbl(i).assignable_area)/100),2);
1752 ELSIF (p_cust_assign_start_date <= loc_info_tbl(i).active_end_date AND
1753 p_cust_assign_end_date >= loc_info_tbl(i).active_start_date) THEN
1754
1755 IF i = 0 OR loc_info_tbl(i).assignable_area < l_min_area THEN
1756 l_min_area := loc_info_tbl(i).assignable_area;
1757 END IF;
1758 END IF;
1759 END LOOP;
1760 IF p_allocated_area < 0 THEN
1761 p_allocated_area := ROUND(((p_allocated_area_pct * l_min_area)/100),2);
1762 END IF;
1763 pnp_debug_pkg.debug('PN_TENANCIES_PKG.GET_ALLOCATED_AREA (-)');
1764 END get_allocated_area;
1765
1766 -------------------------------------------------------------------
1767 -- PROCEDURE : Manual_space_assign
1768 -- DESCRIPTION:
1769 --
1770 -- 12-DEC-03 Ram kumar o Created
1771 -------------------------------------------------------------------
1772
1773 PROCEDURE manual_space_assign(
1774 p_location_id IN NUMBER
1775 ,p_from_date IN DATE
1776 ,p_to_date IN DATE
1777 ,p_fin_oblig_end_date IN DATE
1778 ,p_allocated_pct IN NUMBER
1779 ,p_loc_type_code OUT NOCOPY VARCHAR2
1780 )
1781 IS
1782
1783 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
1784 l_num_table pn_recovery_extract_pkg.number_table_TYPE;
1788 i NUMBER := 0;
1785 l_date_table pn_recovery_extract_pkg.date_table_TYPE;
1786 l_start_date DATE := NULL;
1787 l_end_date DATE := NULL;
1789 j NUMBER := 0;
1790
1791 BEGIN
1792 pnp_debug_pkg.debug('PN_TENANCIES_PKG.MANUAL_SPACE_ASSIGN (+)');
1793
1794 get_loc_info(p_location_id => p_location_id,
1795 p_from_date => p_from_date,
1796 p_to_date => p_to_date,
1797 p_loc_type_code => l_loc_type_code);
1798
1799 IF l_loc_type_code IN ('OFFICE', 'SECTION') THEN
1800
1801 FOR i IN 0 .. loc_info_tbl.count-1
1802 LOOP
1803 pn_recovery_extract_pkg.process_vacancy(
1804 p_start_date => loc_info_tbl(i).active_start_date,
1805 p_end_date => loc_info_tbl(i).active_end_date,
1806 p_area => loc_info_tbl(i).assignable_area,
1807 p_date_table => l_date_table,
1808 p_number_table => l_num_table,
1809 p_add => TRUE);
1810 END LOOP;
1811
1812 i := 0;
1813 space_assign_info_tbl.delete;
1814
1815 FOR i IN 0 .. l_date_table.count-1
1816 LOOP
1817 IF i = 0 THEN
1818 l_start_date := l_date_table(i);
1819
1820 ELSE
1821 l_end_date := l_date_table(i)-1;
1822 IF l_end_date >= p_from_date and l_start_date <= p_to_date THEN
1823 space_assign_info_tbl(j).cust_assign_start_date := GREATEST(p_from_date, l_start_date);
1824 space_assign_info_tbl(j).cust_assign_end_date := LEAST(p_to_date, l_end_date);
1825
1826 IF i = l_date_table.count-1 THEN
1827 space_assign_info_tbl(j).fin_oblig_end_date := p_fin_oblig_end_date;
1828 ELSE
1829 space_assign_info_tbl(j).fin_oblig_end_date := LEAST(p_to_date, l_end_date);
1830 END IF;
1831 space_assign_info_tbl(j).allocated_area_pct := p_allocated_pct;
1832 get_allocated_area(
1833 p_cust_assign_start_date => space_assign_info_tbl(j).cust_assign_start_date,
1834 p_cust_assign_end_date => space_assign_info_tbl(j).cust_assign_end_date,
1835 p_allocated_area_pct => space_assign_info_tbl(j).allocated_area_pct,
1836 p_allocated_area => space_assign_info_tbl(j).allocated_area);
1837 j := j + 1;
1838 l_start_date := l_date_table(i);
1839 END IF;
1840 END IF;
1841 END LOOP;
1842
1843 ELSE
1844 space_assign_info_tbl(j).cust_assign_start_date := p_from_date;
1845 space_assign_info_tbl(j).cust_assign_end_date := p_to_date;
1846 space_assign_info_tbl(j).fin_oblig_end_date := p_fin_oblig_end_date;
1847 space_assign_info_tbl(j).allocated_area := NULL;
1848 space_assign_info_tbl(j).allocated_area_pct := NULL;
1849 END IF;
1850 p_loc_type_code := l_loc_type_code;
1851
1852 pnp_debug_pkg.debug('PN_TENANCIES_PKG.MANUAL_SPACE_ASSIGN (-)');
1853 EXCEPTION
1854 WHEN OTHERS THEN
1855 pnp_debug_pkg.log('Manual_space_assign - Errmsg: ' || sqlerrm);
1856 RAISE;
1857
1858 END manual_space_assign;
1859
1860 -------------------------------------------------------------------
1861 -- PROCEDURE : CHK_MULTI_TENANCY_PROFILE
1862 -- DESCRIPTION:
1863 --
1864 -- 10-JUN-03 PSidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
1865 -- 05-AUG-03 STripathi o Fixed for BUG# 3082056. Populate
1866 -- populate_space_assign_info for SECTION also.
1867 -- 22-AUG-03 STripathi o Fixed for BUG# 3085758, Added parameter p_fin_oblig_end_date.
1868 -- 04-NOV-03 DThota o Checking for profile option PN_AUTOMATIC_SPACE_DISTRIBUTION
1869 -- for split and redistribute of assignment records
1870 -- 05-NOV-03 STripathi o Modified CURSOR csr_assign_exists to check for any
1871 -- tenancy for the same location in other non-Direct lease
1872 -- 07-NOV-03 DThota o Fix for bug # 3242535
1873 -- assigned l_loc_type_code to p_loc_type_code
1874 -- 10-NOV-03 DThota o Fix for bug # 3194380
1875 -- New cusrsor csr_space_exists checks to see if assignable area
1876 -- in pn_locations_all is non-zero for a given assignment time period
1877 -- Returning if there is no vacancy, overlap or assignable_area in locations is
1878 -- non-zero regardless of PN_AUTOMATIC_SPACE_DISTRIBUTION setting.
1879 -- 21-NOV-03 STripathi o Fixed BUG# 3263503, Removed return; CLOSE csr_space_exists; in
1880 -- csr_space_exists and EXIT; in space_assign_info_tbl for NOVACANT.
1881 -- 14-JAN-04 STripathi o Fixed BUG# 3359371, Call pnp_util_func.Get_Location_Type_Lookup_Code
1882 -- with p_cust_assign_start_dt as as_of_date.
1883 -- 08-APR-04 STripathi o Fixed for BUG# 3533405, Modified CURSOR csr_assign_exists
1884 -- added ten.str_dt <= p_assgn_end_dt and ten.end_dt >= p_assgn_str_dt
1885 -- to check tenancy other exist betn assgn str and end dt.
1886 -- 28-NOV-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
1887 -------------------------------------------------------------------
1888 PROCEDURE chk_multi_tenancy_profile(
1889 p_location_id IN NUMBER
1890 ,p_lease_id IN NUMBER
1891 ,p_cust_assign_start_dt IN DATE
1895 ,p_fin_oblig_end_date IN DATE
1892 ,p_cust_assign_end_dt IN DATE
1893 ,p_old_cust_assign_start_dt IN DATE
1894 ,p_old_cust_assign_end_dt IN DATE
1896 ,p_chk_vacancy IN BOOLEAN
1897 ,p_count IN NUMBER
1898 ,p_action OUT NOCOPY VARCHAR2
1899 ,p_loc_type_code OUT NOCOPY VARCHAR2
1900 )
1901 IS
1902 CURSOR csr_assign_exists IS
1903 SELECT 'Y'
1904 FROM DUAl
1905 WHERE EXISTS (SELECT NULL
1906 FROM pn_leases_all pnl,
1907 pn_tenancies_all ten
1908 WHERE pnl.lease_id <> p_lease_id
1909 AND pnl.lease_class_code <> 'DIRECT'
1910 AND pnl.lease_id = ten.lease_id
1911 AND ten.location_id = p_location_id
1912 AND NVL(ten.estimated_occupancy_date, ten.occupancy_date)
1913 <= p_cust_assign_end_dt
1914 AND ten.expiration_date >= p_cust_assign_start_dt
1915 );
1916
1917 CURSOR csr_space_exists IS
1918 SELECT 'Y'
1919 FROM DUAl
1920 WHERE EXISTS (SELECT NULL
1921 from pn_locations_all pl
1922 where pl.location_id = p_location_id
1923 and pl.assignable_area = 0
1924 and pl.active_start_date <= nvl(p_cust_assign_end_dt,to_date('12/31/4712','MM/DD/YYYY'))
1925 and pl.active_end_date >= p_cust_assign_start_dt);
1926
1927 l_multi_tenancy_profile VARCHAR2(100);
1928 l_exists VARCHAR2(1) := 'N';
1929 l_loc_type_lookup_code VARCHAR2(30) := pnp_util_func.Get_Location_Type_Lookup_Code(p_location_id, p_cust_assign_start_dt);
1930 i NUMBER := 0;
1931 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
1932 l_auto_space_assign VARCHAR2(30);
1933
1934 CURSOR org_cur IS
1935 SELECT org_id
1936 FROM pn_leases_all pnl
1937 WHERE pnl.lease_id = p_lease_id;
1938
1939 l_org_id NUMBER;
1940
1941
1942 BEGIN
1943 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHK_MULTI_TENANCY_PROFILE (+)');
1944
1945 FOR rec IN org_cur LOOP
1946 l_org_id := rec.org_id;
1947 END LOOP;
1948
1949 l_multi_tenancy_profile := NVL( pn_mo_cache_utils.get_profile_value('PN_MULTIPLE_LEASE_FOR_LOCATION',l_org_id),'N');
1950 l_auto_space_assign := NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N');
1951
1952 p_action := NULL;
1953 space_assign_info_tbl.delete;
1954
1955 IF l_multi_tenancy_profile = 'N' THEN
1956 OPEN csr_assign_exists;
1957 FETCH csr_assign_exists INTO l_exists;
1958 CLOSE csr_assign_exists;
1959 IF l_exists = 'Y' THEN
1960 p_action := 'OVERLAP';
1961 return;
1962 END IF;
1963 END IF;
1964
1965 /* Check to see whether location assignable area is zero for the duration of the space assignment */
1966
1967 OPEN csr_space_exists;
1968 FETCH csr_space_exists INTO l_exists;
1969 IF csr_space_exists%FOUND THEN
1970 p_action := 'NOVACANT';
1971 END IF;
1972 CLOSE csr_space_exists;
1973
1974 IF l_auto_space_assign = 'N' AND p_chk_vacancy AND
1975 l_loc_type_lookup_code IN ('OFFICE', 'SECTION')
1976 THEN
1977 populate_space_assign_info(
1978 p_location_id => p_location_id
1979 ,p_from_date => p_cust_assign_start_dt
1980 ,p_to_date => p_cust_assign_end_dt
1981 ,p_fin_oblig_end_date => p_fin_oblig_end_date
1982 ,p_loc_type_code => l_loc_type_code
1983 );
1984
1985 FOR i IN 0..space_assign_info_tbl.count-1
1986 LOOP
1987 IF space_assign_info_tbl(i).allocated_area = 0 THEN
1988 IF p_count = 0 THEN
1989 p_action := 'NOVACANT';
1990 ELSIF (p_old_cust_assign_start_dt > space_assign_info_tbl(i).cust_assign_start_date AND
1991 p_old_cust_assign_end_dt > space_assign_info_tbl(i).cust_assign_end_date) OR
1992 (p_old_cust_assign_end_dt < space_assign_info_tbl(i).cust_assign_end_date AND
1993 p_old_cust_assign_start_dt < space_assign_info_tbl(i).cust_assign_start_date) THEN --???
1994 p_action := 'NOVACANT';
1995 END IF;
1996 END IF;
1997 END LOOP;
1998
1999 END IF;
2000 p_loc_type_code := l_loc_type_code; -- 3242535
2001
2002 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CHK_MULTI_TENANCY_PROFILE (-)');
2003 END chk_multi_tenancy_profile;
2004
2005 -------------------------------------------------------------------------------
2006 -- PROCEDURE : CREATE_AUTO_SPACE_ASSIGN
2007 -- DESCRIPTION:
2008 --
2009 -- 10-JUN-03 PSidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
2010 -- 22-AUG-03 STripathi o Fixed for BUG# 3085758, Added parameter p_fin_oblig_end_date
2011 -- when calling chk_multi_tenancy_profile and populate_space_assign_info.
2012 -- 04-NOV-03 DThota o Checking for profile option PN_AUTOMATIC_SPACE_DISTRIBUTION
2016 -- 21-NOV-03 STripathi o Fixed BUG# 3263503, IN IF clause, Removed NOVACANT to return;
2013 -- for split and redistribute of assignment records
2014 -- 06-NOV-03 STripathi o Call assignment_split only for OFFICE and SECTION.
2015 -- 08-NOV-03 STripathi o Fixed for BUG# 3242651. Pass DUP_ASSIGN for dup assign in p_action.
2017 -- Now return only for OVERLAP instead of OVERLAP and NOVACANT.
2018 -- 09-MAR-05 ftanudja o Added start and end date for assignment_split. #4199297
2019 -- 28-NOV-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
2020 -------------------------------------------------------------------------------
2021 PROCEDURE create_auto_space_assign(
2022 p_location_id IN NUMBER
2023 ,p_lease_id IN NUMBER
2024 ,p_customer_id IN NUMBER
2025 ,p_cust_site_use_id IN NUMBER
2026 ,p_cust_assign_start_dt IN DATE
2027 ,p_cust_assign_end_dt IN DATE
2028 ,p_recovery_space_std_code IN VARCHAR2
2029 ,p_recovery_type_code IN VARCHAR2
2030 ,p_fin_oblig_end_date IN DATE
2031 ,p_allocated_pct IN NUMBER
2032 ,p_tenancy_id IN NUMBER
2033 ,p_org_id IN NUMBER
2034 ,p_action OUT NOCOPY VARCHAR2
2035 ,p_msg OUT NOCOPY VARCHAR2
2036 )
2037 IS
2038
2039 i NUMBER :=0;
2040 l_rowid ROWID := NULL;
2041 l_cust_space_assign_id NUMBER := NULL;
2042 space_assign_tbl space_assign_info_type;
2043 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
2044 l_return_status VARCHAR2(20) := NULL;
2045 l_auto_space_dist VARCHAR2(20) := NULL;
2046
2047 CURSOR org_cur IS
2048 SELECT org_id
2049 FROM pn_leases_all pnl
2050 WHERE pnl.lease_id = p_lease_id;
2051
2052 l_org_id NUMBER;
2053
2054 BEGIN
2055
2056 FOR rec IN org_cur LOOP
2057 l_org_id := rec.org_id;
2058 END LOOP;
2059
2060 l_auto_space_dist := NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N');
2061 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CREATE_AUTO_SPACE_ASSIGN (+) Auto_Space_Dist: '
2062 ||l_auto_space_dist);
2063
2064 IF p_customer_id IS NULL THEN
2065 RETURN;
2066 END IF;
2067
2068 chk_multi_tenancy_profile(
2069 p_location_id => p_location_id
2070 ,p_lease_id => p_lease_id
2071 ,p_cust_assign_start_dt => p_cust_assign_start_dt
2072 ,p_cust_assign_end_dt => p_cust_assign_end_dt
2073 ,p_old_cust_assign_start_dt => p_cust_assign_start_dt
2074 ,p_old_cust_assign_end_dt => p_cust_assign_end_dt
2075 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2076 ,p_chk_vacancy => TRUE
2077 ,p_count => 0
2078 ,p_action => p_action
2079 ,p_loc_type_code => l_loc_type_code
2080 );
2081
2082 IF p_action IN ('OVERLAP') THEN
2083 RETURN;
2084 END IF;
2085
2086 IF space_assign_info_tbl.count = 0 THEN
2087 populate_space_assign_info(
2088 p_location_id => p_location_id
2089 ,p_from_date => p_cust_assign_start_dt
2090 ,p_to_date => p_cust_assign_end_dt
2091 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2092 ,p_loc_type_code => l_loc_type_code
2093 );
2094 END IF;
2095
2096 space_assign_tbl := space_assign_info_tbl;
2097
2098 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N') = 'N' THEN
2099
2100 manual_space_assign (
2101 p_location_id => p_location_id
2102 ,p_from_date => p_cust_assign_start_dt
2103 ,p_to_date => p_cust_assign_end_dt
2104 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2105 ,p_allocated_pct => p_allocated_pct
2106 ,p_loc_type_code => l_loc_type_code
2107 );
2108 space_assign_tbl := space_assign_info_tbl;
2109
2110 END IF;
2111
2112 insert_space_assign_row (
2113 p_location_id => p_location_id
2114 ,p_lease_id => p_lease_id
2115 ,p_customer_id => p_customer_id
2116 ,p_cust_site_use_id => p_cust_site_use_id
2117 ,p_recovery_space_std_code => p_recovery_space_std_code
2118 ,p_recovery_type_code => p_recovery_type_code
2119 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2120 ,p_tenancy_id => p_tenancy_id
2124 );
2121 ,p_org_id => p_org_id
2122 ,p_space_assign_info_tbl => space_assign_info_tbl
2123 ,p_return_status => l_return_status
2125
2126 IF l_return_status IS NOT NULL THEN
2127 p_action := l_return_status;
2128 RETURN;
2129 END IF;
2130
2131 -- 110403
2132 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N') = 'Y' AND
2133 l_loc_type_code IN ('OFFICE', 'SECTION')
2134 THEN
2135
2136 PN_SPACE_ASSIGN_CUST_PKG.assignment_split(
2137 p_location_id => p_location_id,
2138 p_start_date => p_cust_assign_start_dt,
2139 p_end_date => p_cust_assign_end_dt);
2140
2141 END IF;
2142
2143 pnp_debug_pkg.debug('PN_TENANCIES_PKG.CREATE_AUTO_SPACE_ASSIGN (-)');
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 p_msg := sqlerrm;
2147 pnp_debug_pkg.log('Create_auto_space_assign - Errmsg: ' || p_msg);
2148 RAISE;
2149
2150 END create_auto_space_assign;
2151
2152 -------------------------------------------------------------------------------
2153 -- PROCEDURE : DELETE_AUTO_SPACE_ASSIGN
2154 -- DESCRIPTION:
2155 --
2156 -- 10-JUN-03 PSidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
2157 -- 25-Nov-03 DThota o Added 2 parameters p_location_id and p_loc_type_code to
2158 -- delete_auto_space_assign. Added call to
2159 -- PN_SPACE_ASSIGN_CUST_PKG.assignment_split
2160 -- Fix for bug # 3282064
2161 -- 23-FEB-04 STripathi o Fixed for BUG# 3425167. Removed code for IF p_cust_assign_start_date
2162 -- IS NOT NULL AND p_cust_assign_end_date IS NOT NULL THEN.
2163 -- For a tenancy id, delete all space assgn records.
2164 -- 09-MAR-05 ftanudja o Added start and end date for assignment_split.#4199297
2165 -- 28-NOV-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
2166 -------------------------------------------------------------------------------
2167 PROCEDURE delete_auto_space_assign (
2168 p_tenancy_id IN NUMBER
2169 ,p_cust_assign_start_date IN DATE
2170 ,p_cust_assign_end_date IN DATE
2171 ,p_action OUT NOCOPY VARCHAR2
2172 ,p_location_id IN pn_locations_all.location_id%TYPE DEFAULT NULL
2173 ,p_loc_type_code IN pn_locations_all.location_type_lookup_code%TYPE DEFAULT NULL
2174 )
2175 IS
2176 l_count NUMBER := 0;
2177 l_del_count NUMBER := 0;
2178 l_auto_space_dist VARCHAR2(20) := NULL;
2179
2180 CURSOR org_cur IS
2181 SELECT org_id
2182 FROM pn_tenancies_all
2183 WHERE tenancy_id = p_tenancy_id;
2184
2185 l_org_id NUMBER;
2186
2187 BEGIN
2188
2189 FOR rec IN org_cur LOOP
2190 l_org_id := rec.org_id;
2191 END LOOP;
2192
2193 l_auto_space_dist := NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N');
2194 pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_AUTO_SPACE_ASSIGN (+) Auto_Space_Dist: '
2195 ||l_auto_space_dist||', TenancyId: '||p_tenancy_id);
2196
2197 DELETE FROM pn_rec_expcl_dtlln_all
2198 WHERE cust_space_assign_id IN (SELECT cust_space_assign_id
2199 FROM pn_space_assign_cust_all
2200 WHERE tenancy_id = p_tenancy_id);
2201 l_count := SQL%ROWCOUNT;
2202
2203 DELETE FROM pn_rec_arcl_dtlln_all
2204 WHERE cust_space_assign_id IN (SELECT cust_space_assign_id
2205 FROM pn_space_assign_cust_all
2206 WHERE tenancy_id = p_tenancy_id);
2207 l_count := SQL%ROWCOUNT + l_count;
2208
2209 DELETE FROM pn_space_assign_cust_all
2210 WHERE tenancy_id = p_tenancy_id;
2211 l_del_count := SQL%ROWCOUNT;
2212
2213 -- 3282064
2214 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N') = 'Y' AND
2215 p_loc_type_code IN ('OFFICE', 'SECTION')
2216 THEN
2217
2218 PN_SPACE_ASSIGN_CUST_PKG.assignment_split(
2219 p_location_id => p_location_id,
2220 p_start_date => p_cust_assign_start_date,
2221 p_end_date => p_cust_assign_end_date);
2222
2223 END IF;
2224
2225 IF l_count > 0 THEN
2226 p_action := 'R';
2227 END IF;
2228
2229 pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_AUTO_SPACE_ASSIGN (-) Deleted '||l_del_count||
2230 ' Space Assgn Rows');
2231 EXCEPTION
2232 WHEN OTHERS THEN
2233 pnp_debug_pkg.log('Delete_auto_space_assign - Errmsg: ' || sqlerrm);
2234 RAISE;
2235 END delete_auto_space_assign;
2236
2237 --------------------------------------------------------------------------------
2238 -- PROCEDURE : UPDATE_AUTO_SPACE_ASSIGN
2239 -- DESCRIPTION:
2240 --
2241 -- 10-JUN-03 PSidhu o Created for Recovery (CAM) impact on Leases and Space Assignments.
2242 -- 22-AUG-03 STripathi o Rewritten procedure to fix BUG# 3085758. Added 5 parameters.
2243 -- 04-NOV-03 DThota o Checking for profile option PN_AUTOMATIC_SPACE_DISTRIBUTION
2244 -- for split and redistribute of assignment records
2245 -- 05-NOV-03 STripathi o Added check# 0, if no space assignment exists for the
2249 -- in Check# 3,4. Fix for BUG# 3242651, added return_status
2246 -- tenancy then Create new Space Assignment for that tenancy.
2247 -- 06-NOV-03 STripathi o Call assignment_split only for OFFICE and SECTION.
2248 -- 08-NOV-03 STripathi o Fixed for BUG# 3242617. Use NVL for the _old items
2250 -- for passing back DUP_ASSIGN for dup assign in p_action.
2251 -- 21-NOV-03 STripathi o Fixed BUG# 3263503, IN IF clause, Removed NOVACANT to return;
2252 -- Now return only for OVERLAP instead of OVERLAP and NOVACANT.
2253 -- 25-NOV-03 STripathi o Fix BUG# 3282064, Check chk_dup_cust_assign if customer is
2254 -- changed when updating space_assign (Check# 4).
2255 -- 25-NOV-03 STripathi o Fix BUG# 3300697, if loc_type_code is null, call get_loc_type_code.
2256 -- 09-MAR-05 ftanudja o Added start and end date for assignment_split. #4199297
2257 -- 28-NOV-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
2258 --------------------------------------------------------------------------------
2259 PROCEDURE update_auto_space_assign(
2260 p_location_id IN NUMBER
2261 ,p_lease_id IN NUMBER
2262 ,p_customer_id IN NUMBER
2263 ,p_cust_site_use_id IN NUMBER
2264 ,p_cust_assign_start_dt IN DATE
2265 ,p_cust_assign_end_dt IN DATE
2266 ,p_recovery_space_std_code IN VARCHAR2
2267 ,p_recovery_type_code IN VARCHAR2
2268 ,p_fin_oblig_end_date IN DATE
2269 ,p_allocated_pct IN NUMBER
2270 ,p_tenancy_id IN NUMBER
2271 ,p_org_id IN NUMBER
2272 ,p_location_id_old IN NUMBER
2273 ,p_customer_id_old IN NUMBER
2274 ,p_cust_site_use_id_old IN NUMBER
2275 ,p_cust_assign_start_dt_old IN DATE
2276 ,p_cust_assign_end_dt_old IN DATE
2277 ,p_recovery_space_std_code_old IN VARCHAR2
2278 ,p_recovery_type_code_old IN VARCHAR2
2279 ,p_fin_oblig_end_date_old IN DATE
2280 ,p_allocated_pct_old IN NUMBER
2281 ,p_action OUT NOCOPY VARCHAR2
2282 ,p_msg OUT NOCOPY VARCHAR2
2283 )
2284 IS
2285 CURSOR csr_min_cust_assign IS
2286 SELECT cust_space_assign_id,
2287 cust_assign_start_date,
2288 cust_assign_end_date,
2289 allocated_area
2290 FROM pn_space_assign_cust_all
2291 WHERE tenancy_id = p_tenancy_id
2292 AND cust_assign_start_date = (SELECT MIN(cust_assign_start_date)
2293 FROM pn_space_assign_cust_all
2294 WHERE tenancy_id = p_tenancy_id);
2295
2296 CURSOR csr_max_cust_assign IS
2297 SELECT cust_space_assign_id,
2298 cust_assign_start_date,
2299 cust_assign_end_date,
2300 allocated_area
2301 FROM pn_space_assign_cust_all
2302 WHERE tenancy_id = p_tenancy_id
2303 AND cust_assign_end_date = (SELECT MAX(cust_assign_end_date)
2304 FROM pn_space_assign_cust_all
2305 WHERE tenancy_id = p_tenancy_id);
2306
2307 CURSOR csr_spc_assign_exists IS
2308 SELECT 'Y'
2309 FROM DUAL
2310 WHERE EXISTS (SELECT NULL
2311 FROM pn_space_assign_cust_all
2312 WHERE tenancy_id = p_tenancy_id);
2313
2314 i NUMBER := 0;
2315 j NUMBER := 0;
2316 l_count NUMBER := 0;
2317 l_extend_assgn BOOLEAN := FALSE;
2318 l_allocated_area NUMBER := NULL;
2319 l_fin_oblig_end_date DATE := NULL;
2320 l_min_cust_start_date DATE := NULL;
2321 l_min_cust_end_date DATE := NULL;
2322 l_min_cust_assign_id NUMBER := NULL;
2323 l_max_cust_start_date DATE := NULL;
2324 l_max_cust_end_date DATE := NULL;
2325 l_max_cust_assign_id NUMBER := NULL;
2326 space_assign_tbl space_assign_info_type;
2327 l_exists VARCHAR2(1) := 'N';
2328 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
2329 l_StartOfTime DATE := TO_DATE('01010001','MMDDYYYY');
2330 l_return_status VARCHAR2(20) := NULL;
2331 l_auto_space_dist VARCHAR2(20) := NULL;
2332 l_cust_assign_start_date DATE := NULL;
2333 l_cust_assign_end_date DATE := NULL;
2334
2335
2336 CURSOR org_cur IS
2337 SELECT org_id
2338 FROM pn_tenancies_all
2339 WHERE tenancy_id = p_tenancy_id;
2340
2341 CURSOR cur_alloc_area IS
2345 WHERE tenancy_id = p_tenancy_id;
2342 SELECT cust_assign_start_date,
2343 cust_assign_end_date
2344 FROM pn_space_assign_cust_all
2346
2347 l_org_id NUMBER;
2348
2349 BEGIN
2350
2351 FOR rec IN org_cur LOOP
2352 l_org_id := rec.org_id;
2353 END LOOP;
2354
2355 l_auto_space_dist := NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N');
2356 pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_AUTO_SPACE_ASSIGN (+) Auto_Space_Dist: '
2357 ||l_auto_space_dist);
2358 p_action := NULL;
2359
2360 OPEN csr_spc_assign_exists;
2361 FETCH csr_spc_assign_exists INTO l_exists;
2362 CLOSE csr_spc_assign_exists;
2363
2364 ----------------------------------------------------------------------------------------
2365 -- Check# 0. If Space Assignment for the tenancy do not exists,
2366 -- Create new Space Assignment for that tenancy.
2367 ----------------------------------------------------------------------------------------
2368 IF NVL(l_exists, 'N') = 'N' THEN
2369 create_auto_space_assign(
2370 p_location_id => p_location_id
2371 ,p_lease_id => p_lease_id
2372 ,p_customer_id => p_customer_id
2373 ,p_cust_site_use_id => p_cust_site_use_id
2374 ,p_cust_assign_start_dt => p_cust_assign_start_dt
2375 ,p_cust_assign_end_dt => p_cust_assign_end_dt
2376 ,p_recovery_space_std_code => p_recovery_space_std_code
2377 ,p_recovery_type_code => p_recovery_type_code
2378 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2379 ,p_allocated_pct => p_allocated_pct
2380 ,p_tenancy_id => p_tenancy_id
2381 ,p_org_id => p_org_id
2382 ,p_action => p_action
2383 ,p_msg => p_msg
2384 );
2385
2386 ----------------------------------------------------------------------------------------
2387 -- Check# 1. If Location is changed, check if assignment is associated with a locked
2388 -- Area or Expense Class in Recoveries. If yes then Stop.
2389 -- If not delete the assignment with old location and create a new assignment
2390 -- with the new location.
2391 ----------------------------------------------------------------------------------------
2392 ELSIF (p_location_id <> p_location_id_old) THEN
2393
2394 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 1: Location is changed.');
2395 ---------------------------------------------------------------------------------
2396 -- Check if the assignment is associated with a locked Area or Expense Class in
2397 -- Recoveries. If yes then Stop.
2398 ---------------------------------------------------------------------------------
2399 IF cust_assign_assoc_exp_area_dt(
2400 p_tenancy_id => p_tenancy_id
2401 ,p_chk_locked => TRUE
2402 ,p_cust_assign_start_dt => p_cust_assign_start_dt_old
2403 ,p_cust_assign_end_dt => p_cust_assign_end_dt_old
2404 )
2405 THEN
2406 p_action := 'S';
2407 RETURN;
2408 ELSE
2409
2410 ---------------------------------------------------------------------------------
2411 -- Check if the assignment is associated with a any Area or Expense Class in
2412 -- Recoveries. If yes then need to show message to regenerate Area/Expense class.
2413 ---------------------------------------------------------------------------------
2414 IF cust_assign_assoc_exp_area_dt(
2415 p_tenancy_id => p_tenancy_id
2416 ,p_chk_locked => FALSE
2417 )
2418 THEN
2419 p_action :='R';
2420 END IF;
2421
2422 ------------------------------------------------------------------------
2423 -- Delete the assignment with old location and create a new assignment.
2424 ------------------------------------------------------------------------
2425 delete_auto_space_assign(
2426 p_tenancy_id => p_tenancy_id
2427 ,p_cust_assign_start_date => p_cust_assign_start_dt_old
2428 ,p_cust_assign_end_date => p_cust_assign_end_dt_old
2429 ,p_action => p_action
2430 );
2431
2432 ------------------------------------------------------------------------
2433 -- Create a new assignment with the new location.
2434 ------------------------------------------------------------------------
2435 create_auto_space_assign(
2436 p_location_id => p_location_id
2437 ,p_lease_id => p_lease_id
2441 ,p_cust_assign_end_dt => p_cust_assign_end_dt
2438 ,p_customer_id => p_customer_id
2439 ,p_cust_site_use_id => p_cust_site_use_id
2440 ,p_cust_assign_start_dt => p_cust_assign_start_dt
2442 ,p_recovery_space_std_code => p_recovery_space_std_code
2443 ,p_recovery_type_code => p_recovery_type_code
2444 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2445 ,p_allocated_pct => p_allocated_pct
2446 ,p_tenancy_id => p_tenancy_id
2447 ,p_org_id => p_org_id
2448 ,p_action => p_action
2449 ,p_msg => p_msg
2450 );
2451 END IF;
2452
2453 ELSE
2454 ----------------------------------------------------------------------------------------
2455 -- Location is not changed.
2456 -- Check# 2. Are the tenancy start and end dates are changed. Following 4 case apply:
2457 -- 1. Tenancy Start date brought in.
2458 -- 2. Tenancy Start date expanded out.
2459 -- 3. Tenancy End date brought in.
2460 -- 4. Tenancy End date expanded out.
2461 ----------------------------------------------------------------------------------------
2462 IF (p_cust_assign_start_dt <> p_cust_assign_start_dt_old) OR
2463 (p_cust_assign_end_dt <> p_cust_assign_end_dt_old)
2464 THEN
2465
2466 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: Tenancy start/end dates are changed.');
2467 ---------------------------------------------------------------------------------
2468 -- Check if the assignment is associated with a any Area or Expense Class in
2469 -- Recoveries. If yes then need to show message to regenerate Area/Expense class.
2470 ---------------------------------------------------------------------------------
2471 IF cust_assign_assoc_exp_area_dt(
2472 p_tenancy_id => p_tenancy_id
2473 ,p_chk_locked => FALSE
2474 )
2475 THEN
2476 p_action :='R';
2477 END IF;
2478
2479 ---------------------------------------------------------------------------------
2480 -- 1. Tenancy Start date brought in.
2481 -- If the assignment is NOT associated with a any Area or Expense Class in
2482 -- Recoveries, remove all the assignments prior to new tenancy start date.
2483 ---------------------------------------------------------------------------------
2484 IF p_cust_assign_start_dt > p_cust_assign_start_dt_old THEN
2485
2486 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 1. Tenancy Start date brought in.');
2487
2488 IF cust_assign_assoc_exp_area_dt(
2489 p_tenancy_id => p_tenancy_id
2490 ,p_chk_locked => TRUE
2491 ,p_cust_assign_start_dt => p_cust_assign_start_dt_old
2492 ,p_cust_assign_end_dt => p_cust_assign_end_dt_old
2493 )
2494 THEN
2495 p_action := 'S';
2496 RETURN;
2497 ELSE
2498
2499 DELETE FROM pn_space_assign_cust_all
2500 WHERE tenancy_id = p_tenancy_id
2501 AND cust_assign_end_date < p_cust_assign_start_dt;
2502 l_count := 0;
2503 l_count := SQL%ROWCOUNT;
2504 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 1. Rows Deleted: '||l_count);
2505
2506 UPDATE pn_space_assign_cust_all
2507 SET cust_assign_start_date = p_cust_assign_start_dt
2508 ,last_update_date = SYSDATE
2509 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
2510 WHERE tenancy_id = p_tenancy_id
2511 AND cust_assign_start_date < p_cust_assign_start_dt
2512 AND cust_assign_end_date >= p_cust_assign_start_dt;
2513 l_count := 0;
2514 l_count := SQL%ROWCOUNT;
2515 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 1. Rows Updated: '||l_count);
2516
2517 END IF;
2518
2519 ---------------------------------------------------------------------------------
2520 -- 2. Tenancy Start date expanded out.
2521 -- Need to extend the 1st assignment.
2522 ---------------------------------------------------------------------------------
2523 ELSIF p_cust_assign_start_dt < p_cust_assign_start_dt_old THEN
2524 l_extend_assgn := TRUE;
2525 END IF;
2526
2527 ---------------------------------------------------------------------------------
2528 -- 3. Tenancy End date brought in.
2529 -- If the assignment is NOT associated with a any Area or Expense Class in
2530 -- Recoveries, remove all the assignments after the new tenancy end date.
2531 -- Note: Update the fin_oblig_end_date for the last assignment.
2535 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 3. Tenancy End date brought in.');
2532 ---------------------------------------------------------------------------------
2533 IF p_cust_assign_end_dt < p_cust_assign_end_dt_old THEN
2534
2536
2537 IF cust_assign_assoc_exp_area_dt(
2538 p_tenancy_id => p_tenancy_id
2539 ,p_chk_locked => TRUE
2540 ,p_cust_assign_start_dt => p_cust_assign_start_dt_old
2541 ,p_cust_assign_end_dt => p_cust_assign_end_dt_old
2542 )
2543 THEN
2544 p_action := 'S';
2545 RETURN;
2546 ELSE
2547
2548 DELETE FROM pn_space_assign_cust_all
2549 WHERE tenancy_id = p_tenancy_id
2550 AND cust_assign_start_date > p_cust_assign_end_dt;
2551 l_count := 0;
2552 l_count := SQL%ROWCOUNT;
2553 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 3. Rows Deleted: '||l_count);
2554
2555 UPDATE pn_space_assign_cust_all
2556 SET cust_assign_end_date = p_cust_assign_end_dt
2557 ,fin_oblig_end_date = p_fin_oblig_end_date
2558 ,last_update_date = SYSDATE
2559 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
2560 WHERE tenancy_id = p_tenancy_id
2561 AND cust_assign_start_date <= p_cust_assign_end_dt
2562 AND cust_assign_end_date > p_cust_assign_end_dt;
2563 l_count := 0;
2564 l_count := SQL%ROWCOUNT;
2565 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 3. Rows Updated: '||l_count);
2566
2567 END IF;
2568
2569 ---------------------------------------------------------------------------------
2570 -- 4. Tenancy End date expanded out.
2571 -- Need to extend the last assignment.
2572 ---------------------------------------------------------------------------------
2573 ELSIF p_cust_assign_end_dt > p_cust_assign_end_dt_old THEN
2574 l_extend_assgn := TRUE;
2575 END IF;
2576
2577 ---------------------------------------------------------------------------------
2578 -- 2. Tenancy Start date expanded out: Need to extend the 1st assignment.
2579 -- 4. Tenancy End date expanded out: Need to extend the last assignment.
2580 ---------------------------------------------------------------------------------
2581 IF l_extend_assgn THEN
2582
2583 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 2/4. Tenancy Start/End'
2584 ||' expanded out.');
2585 ---------------------------------------------------------------------------------
2586 -- Initialize the PL/SQL table space_assign_tbl.
2587 ---------------------------------------------------------------------------------
2588 space_assign_tbl.delete;
2589
2590 ---------------------------------------------------------------------------------
2591 -- Check the Multi-Tenancy-Profile, and space the available.
2592 ---------------------------------------------------------------------------------
2593 chk_multi_tenancy_profile(
2594 p_location_id => p_location_id
2595 ,p_lease_id => p_lease_id
2596 ,p_cust_assign_start_dt => p_cust_assign_start_dt
2597 ,p_cust_assign_end_dt => p_cust_assign_end_dt
2598 ,p_old_cust_assign_start_dt => p_cust_assign_start_dt_old
2599 ,p_old_cust_assign_end_dt => p_cust_assign_end_dt_old
2600 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2601 ,p_chk_vacancy => TRUE
2602 ,p_count => 1
2603 ,p_action => p_action
2604 ,p_loc_type_code => l_loc_type_code
2605 );
2606
2607 IF p_action IN ('OVERLAP') THEN
2608 RETURN;
2609 ELSE
2610
2611 ---------------------------------------------------------------------------------
2612 -- If PL/SQL table space_assign_info_tbl is not populated, populate it.
2613 ---------------------------------------------------------------------------------
2614 IF space_assign_info_tbl.count = 0 THEN
2615 populate_space_assign_info(
2616 p_location_id => p_location_id
2617 ,p_from_date => p_cust_assign_start_dt
2618 ,p_to_date => p_cust_assign_end_dt
2619 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2620 ,p_loc_type_code => l_loc_type_code
2621 );
2622 END IF;
2623
2627 IF p_cust_assign_start_dt < p_cust_assign_start_dt_old THEN
2624 ---------------------------------------------------------------------------------
2625 -- 2. Tenancy Start date expanded out: Need to extend the 1st assignment.
2626 ---------------------------------------------------------------------------------
2628
2629 OPEN csr_min_cust_assign;
2630 FETCH csr_min_cust_assign
2631 INTO l_min_cust_assign_id,
2632 l_min_cust_start_date,
2633 l_min_cust_end_date,
2634 l_allocated_area;
2635 CLOSE csr_min_cust_assign;
2636
2637 FOR i IN 0..space_assign_info_tbl.count-1
2638 LOOP
2639 IF l_min_cust_start_date > space_assign_info_tbl(i).cust_assign_start_date THEN
2640
2641 IF l_min_cust_start_date = space_assign_info_tbl(i).cust_assign_end_date+1 AND
2642 l_allocated_area = space_assign_info_tbl(i).allocated_area
2643 THEN
2644
2645 pnp_debug_pkg.debug(' Case: 2. Update Space Assgn... i: '||i
2646 ||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
2647 ||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
2648 ||', area: '||space_assign_info_tbl(i).allocated_area
2649 ||',');
2650
2651 UPDATE pn_space_assign_cust_all
2652 SET cust_assign_start_date = space_assign_info_tbl(i).cust_assign_start_date
2653 WHERE cust_space_assign_id = l_min_cust_assign_id;
2654 ELSE
2655
2656 pnp_debug_pkg.debug(' Case: 2. Create Space Assgn... i: '||i
2657 ||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
2658 ||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
2659 ||', area: '||space_assign_info_tbl(i).allocated_area
2660 ||',');
2661
2662 space_assign_tbl(j).cust_assign_start_date :=
2663 space_assign_info_tbl(i).cust_assign_start_date;
2664 space_assign_tbl(j).cust_assign_end_date :=
2665 space_assign_info_tbl(i).cust_assign_end_date;
2666 space_assign_tbl(j).fin_oblig_end_date :=
2667 space_assign_info_tbl(i).fin_oblig_end_date;
2668 space_assign_tbl(j).allocated_area :=
2669 space_assign_info_tbl(i).allocated_area;
2670 space_assign_tbl(j).allocated_area_pct :=
2671 space_assign_info_tbl(i).allocated_area_pct;
2672 j := j + 1;
2673 END IF;
2674
2675 ELSE
2676 pnp_debug_pkg.debug(' Case: 2. no space assgn... i: '||i
2677 ||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
2678 ||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
2679 ||', area: '||space_assign_info_tbl(i).allocated_area
2680 ||',');
2681
2682 END IF;
2683 END LOOP;
2684 END IF;
2685
2686 ---------------------------------------------------------------------------------
2687 -- 4. Tenancy End date expanded out: Need to extend the last assignment.
2688 -- Note: Update the fin_oblig_end_date for the last assignment.
2689 ---------------------------------------------------------------------------------
2690 IF p_cust_assign_end_dt > p_cust_assign_end_dt_old THEN
2691 OPEN csr_max_cust_assign;
2692 FETCH csr_max_cust_assign
2693 INTO l_max_cust_assign_id,
2694 l_max_cust_start_date,
2695 l_max_cust_end_date,
2696 l_allocated_area;
2697 CLOSE csr_max_cust_assign;
2698
2699 pnp_debug_pkg.debug('Case: 4. Tenancy End expanded out. '
2700 ||', l_max_assign_id: '||l_max_cust_assign_id
2701 ||', l_max_start_date: '||l_max_cust_start_date
2702 ||', l_max_end_date: '||l_max_cust_end_date
2703 ||', l_area: '||l_allocated_area
2704 ||',');
2705
2706 FOR i IN 0 .. space_assign_info_tbl.count-1
2707 LOOP
2708
2709 IF NVL(l_max_cust_end_date, space_assign_info_tbl(i).cust_assign_end_date) <
2710 space_assign_info_tbl(i).cust_assign_end_date
2711 THEN
2712
2713 IF NVL(l_max_cust_end_date+1, space_assign_info_tbl(i).cust_assign_start_date) =
2714 space_assign_info_tbl(i).cust_assign_start_date AND
2715 l_allocated_area = space_assign_info_tbl(i).allocated_area
2716 THEN
2717
2718 pnp_debug_pkg.debug(' Case: 4. Update Space Assgn... i: '||i
2719 ||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
2723
2720 ||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
2721 ||', area: '||space_assign_info_tbl(i).allocated_area
2722 ||',');
2724 -----------------------------------------------------------------------
2725 -- Determine the correct fin_oblig_end_date.
2726 -----------------------------------------------------------------------
2727 IF i = space_assign_info_tbl.count-1 THEN
2728 l_fin_oblig_end_date := p_fin_oblig_end_date;
2729 ELSE
2730 l_fin_oblig_end_date := space_assign_info_tbl(i).cust_assign_end_date;
2731 END IF;
2732
2733 UPDATE pn_space_assign_cust_all
2734 SET cust_assign_end_date = space_assign_info_tbl(i).cust_assign_end_date
2735 ,fin_oblig_end_date = l_fin_oblig_end_date
2736 WHERE cust_space_assign_id = l_max_cust_assign_id;
2737
2738 ELSE
2739
2740 pnp_debug_pkg.debug(' Case: 4. Create Space Assgn... i: '||i
2741 ||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
2742 ||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
2743 ||', area: '||space_assign_info_tbl(i).allocated_area
2744 ||',');
2745
2746 -----------------------------------------------------------------------
2747 -- Determine and update the correct fin_oblig_end_date.
2748 -----------------------------------------------------------------------
2749 IF NVL(l_max_cust_end_date+1, space_assign_info_tbl(i).cust_assign_start_date) =
2750 space_assign_info_tbl(i).cust_assign_start_date
2751 THEN
2752 UPDATE pn_space_assign_cust_all
2753 SET fin_oblig_end_date = cust_assign_end_date
2754 WHERE cust_space_assign_id = l_max_cust_assign_id;
2755 END IF;
2756
2757 space_assign_tbl(j).cust_assign_start_date :=
2758 space_assign_info_tbl(i).cust_assign_start_date;
2759 space_assign_tbl(j).cust_assign_end_date :=
2760 space_assign_info_tbl(i).cust_assign_end_date;
2761 space_assign_tbl(j).fin_oblig_end_date :=
2762 space_assign_info_tbl(i).fin_oblig_end_date;
2763 space_assign_tbl(j).allocated_area :=
2764 space_assign_info_tbl(i).allocated_area;
2765 space_assign_tbl(j).allocated_area_pct :=
2766 space_assign_info_tbl(i).allocated_area_pct;
2767 j := j + 1;
2768
2769 END IF;
2770
2771 ELSE
2772 pnp_debug_pkg.debug(' Case: 4. no space assgn... i: '||i
2773 ||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
2774 ||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
2775 ||', area: '||space_assign_info_tbl(i).allocated_area
2776 ||',');
2777
2778 END IF;
2779 END LOOP;
2780 END IF;
2781
2782 ---------------------------------------------------------------------------------
2783 -- If required insert new space assignment rows to Expand Out tenancy dates.
2784 ---------------------------------------------------------------------------------
2785 IF space_assign_tbl.count > 0 THEN
2786 insert_space_assign_row(
2787 p_location_id => p_location_id
2788 ,p_lease_id => p_lease_id
2789 ,p_customer_id => p_customer_id
2790 ,p_cust_site_use_id => p_cust_site_use_id
2791 ,p_recovery_space_std_code => p_recovery_space_std_code
2792 ,p_recovery_type_code => p_recovery_type_code
2793 ,p_fin_oblig_end_date => p_fin_oblig_end_date
2794 ,p_tenancy_id => p_tenancy_id
2795 ,p_org_id => p_org_id
2796 ,p_space_assign_info_tbl => space_assign_tbl
2797 ,p_return_status => l_return_status
2798 );
2799
2800 END IF;
2801
2802 IF l_return_status IS NOT NULL THEN
2803 p_action := l_return_status;
2804 RETURN;
2805 END IF;
2806
2807 END IF;
2808
2809 ---------------------------------------------------------------------------------
2810 -- End expanding out Tenancy Dates.
2811 ---------------------------------------------------------------------------------
2812 END IF;
2813 END IF;
2814
2818 -- Update the fin_oblig_end_date for the last assignment.
2815 ----------------------------------------------------------------------------------------
2816 -- Location is not changed.
2817 -- Check# 3. Is the fin_oblig_end_date changed.
2819 ----------------------------------------------------------------------------------------
2820 IF (NVL(p_fin_oblig_end_date, l_StartOfTime) <>
2821 NVL(p_fin_oblig_end_date_old, l_StartOfTime)) THEN
2822
2823 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 3: fin_oblig_end_date is changed.');
2824
2825 IF p_fin_oblig_end_date_old IS NOT NULL THEN
2826 UPDATE pn_space_assign_cust_all
2827 SET fin_oblig_end_date = p_fin_oblig_end_date
2828 ,last_update_date = SYSDATE
2829 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
2830 WHERE tenancy_id = p_tenancy_id
2831 AND fin_oblig_end_date = p_fin_oblig_end_date_old;
2832 ELSE
2833 UPDATE pn_space_assign_cust_all
2834 SET fin_oblig_end_date = p_fin_oblig_end_date
2835 ,last_update_date = SYSDATE
2836 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
2837 WHERE tenancy_id = p_tenancy_id
2838 AND cust_assign_end_date = p_cust_assign_end_dt;
2839 END IF;
2840
2841 END IF;
2842
2843 ----------------------------------------------------------------------------------------
2844 -- Location is not changed.
2845 -- Check# 4. Is any of customer_id, cust_site_use_id, recovery_space_std_code OR
2846 -- recovery_type_code changed:
2847 -- Update all space assignment records with new values.
2848 ----------------------------------------------------------------------------------------
2849 IF (NVL(p_customer_id, -99) <>
2850 NVL(p_customer_id_old, -99)) OR
2851 (NVL(p_cust_site_use_id, -99) <>
2852 NVL(p_cust_site_use_id_old, -99)) OR
2853 (NVL(p_recovery_space_std_code, ' ') <>
2854 NVL(p_recovery_space_std_code_old, ' ')) OR
2855 (NVL(p_recovery_type_code, ' ') <>
2856 NVL(p_recovery_type_code_old, ' '))
2857 THEN
2858
2859 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 4: customer_id/cust_site_use_id/'
2860 ||'space_std/recovery_type is changed.');
2861
2862 /*IF (NVL(p_customer_id, -99) <> NVL(p_customer_id_old, -99)) THEN
2863
2864 pn_space_assign_cust_pkg.chk_dup_cust_assign(
2865 p_cust_acnt_id => p_customer_id
2866 ,p_loc_id => p_location_id
2867 ,p_assgn_str_dt => p_cust_assign_start_dt
2868 ,p_assgn_end_dt => p_cust_assign_end_dt
2869 ,p_return_status => l_return_status
2870 );
2871
2872 IF l_return_status IS NOT NULL THEN
2873 p_action := l_return_status;
2874 RETURN;
2875 END IF;
2876 END IF;*/
2877
2878 UPDATE pn_space_assign_cust_all
2879 SET cust_account_id = p_customer_id
2880 ,site_use_id = p_cust_site_use_id
2881 ,recovery_space_std_code = p_recovery_space_std_code
2882 ,recovery_type_code = p_recovery_type_code
2883 ,last_update_date = SYSDATE
2884 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
2885 WHERE tenancy_id = p_tenancy_id;
2886
2887 END IF;
2888
2889 ----------------------------------------------------------------------------------------
2890 -- Location is not changed.
2891 -- Check# 5. Is Allocated_area_pct is changed
2892 -- Update all space assignment records with new value for that tenanct_id.
2893 ----------------------------------------------------------------------------------------
2894 IF (nvl(p_allocated_pct,-1) <> nvl(p_allocated_pct_old,-1)) THEN
2895
2896 pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 5: allocated_area_pct is changed.');
2897
2898 IF p_allocated_pct IS NOT NULL THEN
2899 FOR rec_alloc_area IN cur_alloc_area LOOP
2900 l_cust_assign_start_date := rec_alloc_area.cust_assign_start_date;
2901 l_cust_assign_end_date := rec_alloc_area.cust_assign_end_date;
2902
2903 get_allocated_area(
2904 p_cust_assign_start_date => l_cust_assign_start_date,
2905 p_cust_assign_end_date => l_cust_assign_end_date,
2906 p_allocated_area_pct => p_allocated_pct,
2907 p_allocated_area => l_allocated_area);
2908
2909 UPDATE pn_space_assign_cust_all
2910 SET allocated_area_pct = p_allocated_pct
2911 ,allocated_area = l_allocated_area
2912 WHERE tenancy_id = p_tenancy_id
2913 AND cust_assign_start_date = l_cust_assign_start_date;
2914
2915 END LOOP;
2916 END IF;
2917
2918 END IF;
2919
2920 END IF;
2921
2922 pnp_debug_pkg.debug('l_auto_space_dist: '||l_auto_space_dist||', l_loc_type_code: '||l_loc_type_code);
2923 IF l_loc_type_code IS NULL THEN
2927 -- 110403
2924 l_loc_type_code := get_loc_type_code(p_location_id, p_cust_assign_start_dt);
2925 END IF;
2926
2928 IF NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N') = 'Y' AND
2929 l_loc_type_code IN ('OFFICE', 'SECTION')
2930 THEN
2931
2932 PN_SPACE_ASSIGN_CUST_PKG.assignment_split(
2933 p_location_id => p_location_id,
2934 p_start_date => p_cust_assign_start_dt,
2935 p_end_date => p_cust_assign_end_dt);
2936
2937 END IF;
2938
2939 pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_AUTO_SPACE_ASSIGN (-)');
2940 EXCEPTION
2941 WHEN OTHERS THEN
2942 pnp_debug_pkg.log('Update_auto_space_assign - Errmsg: ' || sqlerrm);
2943 RAISE;
2944 END update_auto_space_assign;
2945
2946
2947 --------------------------------------------------------------------------------
2948 -- PROCEDURE : UPDATE_DUP_SPACE_ASSIGN
2949 -- DESCRIPTION:
2950 --
2951 -- 05-MAR-04 STripathi o Created for ENH# 3485730. If Only one duplicate
2952 -- assign exist, update that assign with lease and
2953 -- tenancy details.
2954 -- 08-MAR-04 STripathi o Do not update start and end date while updating
2955 -- the assign. Call update_auto_space_assign to
2956 -- update start date, end date and fin_oblog_end_dt.
2957 -- 10-MAR-04 STripathi o Added NVL to cust_assign_end_date and start date
2958 -- since it can have null value from PNTSPACE.
2959 -- 28-NOV-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
2960 --------------------------------------------------------------------------------
2961 PROCEDURE Update_Dup_Space_Assign(
2962 p_location_id IN NUMBER
2963 ,p_customer_id IN NUMBER
2964 ,p_lease_id IN NUMBER
2965 ,p_tenancy_id IN NUMBER
2966 ,p_cust_site_use_id IN NUMBER
2967 ,p_cust_assign_start_dt IN DATE
2968 ,p_cust_assign_end_dt IN DATE
2969 ,p_recovery_space_std_code IN VARCHAR2
2970 ,p_recovery_type_code IN VARCHAR2
2971 ,p_fin_oblig_end_date IN DATE
2972 ,p_allocated_pct IN NUMBER
2973 ,p_org_id IN NUMBER
2974 ,p_action OUT NOCOPY VARCHAR2
2975 ,p_msg OUT NOCOPY VARCHAR2
2976 )
2977 IS
2978 CURSOR get_cust_space_assign_id IS
2979 SELECT cust_space_assign_id,
2980 NVL(cust_assign_start_date, pnt_locations_pkg.g_start_of_time) cust_assign_start_date,
2981 NVL(cust_assign_end_date, pnt_locations_pkg.g_end_of_time) cust_assign_end_date
2982 FROM pn_space_assign_cust_all
2983 WHERE cust_account_id = p_customer_id
2984 AND location_id = p_location_id
2985 AND cust_assign_start_date <= p_cust_assign_end_dt
2986 AND NVL(cust_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
2987 >= p_cust_assign_start_dt;
2988
2989
2990 l_cust_space_assign_id NUMBER;
2991 l_cust_assign_start_date DATE;
2992 l_cust_assign_end_date DATE;
2993 l_auto_space_dist VARCHAR2(20) := NULL;
2994
2995 CURSOR org_cur IS
2996 SELECT org_id
2997 FROM pn_locations_all pnl
2998 WHERE pnl.location_id = p_location_id
2999 AND ROWNUM < 2;
3000
3001 l_org_id NUMBER;
3002
3003
3004 BEGIN
3005
3006 FOR rec IN org_cur LOOP
3007 l_org_id := rec.org_id;
3008 END LOOP;
3009
3010 l_auto_space_dist := NVL(pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id),'N');
3011 pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_DUP_SPACE_ASSIGN (+) Auto_Space_Dist: '
3012 ||l_auto_space_dist);
3013 p_action := NULL;
3014
3015 OPEN get_cust_space_assign_id;
3016 FETCH get_cust_space_assign_id
3017 INTO l_cust_space_assign_id,
3018 l_cust_assign_start_date,
3019 l_cust_assign_end_date;
3020 CLOSE get_cust_space_assign_id;
3021
3022 IF l_cust_space_assign_id IS NOT NULL THEN
3023
3024 UPDATE pn_space_assign_cust_all
3025 SET lease_id = p_lease_id
3026 ,tenancy_id = p_tenancy_id
3027 ,cust_assign_start_date = NVL(cust_assign_start_date, pnt_locations_pkg.g_start_of_time)
3028 ,cust_assign_end_date = NVL(cust_assign_end_date, pnt_locations_pkg.g_end_of_time)
3029 ,site_use_id = p_cust_site_use_id
3030 ,recovery_space_std_code = p_recovery_space_std_code
3031 ,recovery_type_code = p_recovery_type_code
3032 ,fin_oblig_end_date = NVL(cust_assign_end_date, pnt_locations_pkg.g_end_of_time)
3033 ,last_update_date = SYSDATE
3034 ,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
3035 WHERE cust_space_assign_id = l_cust_space_assign_id;
3036
3037
3041 ,p_lease_id => p_lease_id
3038 pn_tenancies_pkg.update_auto_space_assign
3039 (
3040 p_location_id => p_location_id
3042 ,p_customer_id => p_customer_id
3043 ,p_cust_site_use_id => p_cust_site_use_id
3044 ,p_cust_assign_start_dt => p_cust_assign_start_dt
3045 ,p_cust_assign_end_dt => p_cust_assign_end_dt
3046 ,p_recovery_space_std_code => p_recovery_space_std_code
3047 ,p_recovery_type_code => p_recovery_type_code
3048 ,p_fin_oblig_end_date => p_fin_oblig_end_date
3049 ,p_allocated_pct => p_allocated_pct
3050 ,p_tenancy_id => p_tenancy_id
3051 ,p_org_id => p_org_id
3052 ,p_location_id_old => p_location_id
3053 ,p_customer_id_old => p_customer_id
3054 ,p_cust_site_use_id_old => p_cust_site_use_id
3055 ,p_cust_assign_start_dt_old => l_cust_assign_start_date
3056 ,p_cust_assign_end_dt_old => l_cust_assign_end_date
3057 ,p_recovery_space_std_code_old => p_recovery_space_std_code
3058 ,p_recovery_type_code_old => p_recovery_type_code
3059 ,p_fin_oblig_end_date_old => p_fin_oblig_end_date
3060 ,p_allocated_pct_old => p_allocated_pct
3061 ,p_action => p_action
3062 ,p_msg => p_msg
3063 );
3064
3065
3066 END IF;
3067
3068 pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_DUP_SPACE_ASSIGN (-)');
3069
3070 END Update_Dup_Space_Assign;
3071
3072 -------------------------------------------------------------------------------
3073 -- FUNCTION : Auto_Allocated_Area_Pct
3074 -- INVOKED FROM :
3075 -- PURPOSE : Retrieves allocated area % for the particular Tenancy_id
3076 -- HISTORY :
3077 -- 07-DEC-2006 Ram Kumar o created
3078 -------------------------------------------------------------------
3079 FUNCTION Auto_Allocated_Area_Pct
3080 (
3081 p_tenancy_id IN NUMBER
3082 )
3083 RETURN NUMBER
3084 IS
3085 l_allocated_area_pct NUMBER;
3086 l_lease_class_code VARCHAR2(30);
3087
3088 CURSOR cur_allocated_area_pct IS
3089 SELECT min(allocated_area_pct) min_area_pct
3090 FROM pn_space_assign_cust_all
3091 WHERE tenancy_id = p_tenancy_id;
3092
3093 CURSOR cur_lease_code IS
3094 SELECT leases.lease_class_code lease_code,
3095 allocated_area_pct
3096 FROM pn_leases_all leases,
3097 pn_tenancies_all tenant
3098 WHERE leases.lease_id = tenant.lease_id
3099 AND tenant.tenancy_id = p_tenancy_id;
3100
3101 BEGIN
3102 pnp_debug_pkg.debug('PN_TENANCIES_PKG.Auto_Allocated_Area_Pct (+)');
3103
3104 --
3105 FOR rec_allocated_area_pct IN cur_allocated_area_pct LOOP
3106 l_allocated_area_pct := rec_allocated_area_pct.min_area_pct;
3107 END LOOP;
3108
3109 FOR rec_lease_code IN cur_lease_code LOOP
3110 l_lease_class_code := rec_lease_code.lease_code;
3111 l_allocated_area_pct := nvl(l_allocated_area_pct,rec_lease_code.allocated_area_pct);
3112 END LOOP;
3113
3114 IF l_lease_class_code = 'DIRECT' THEN
3115 l_allocated_area_pct := NULL;
3116 END IF;
3117
3118 pnp_debug_pkg.debug('PN_TENANCIES_PKG.Auto_Allocated_Area_Pct (-)');
3119
3120 RETURN l_allocated_area_pct;
3121
3122 END Auto_Allocated_Area_Pct;
3123
3124 -------------------------------------------------------------------------------
3125 -- FUNCTION : Auto_Allocated_Area
3126 -- INVOKED FROM :
3127 -- PURPOSE : Retrieves allocated area for the particular Tenancy_id
3128 -- HISTORY :
3129 -- 07-DEC-2006 Ram Kumar o created
3130 -------------------------------------------------------------------
3131 FUNCTION Auto_Allocated_Area
3132 (
3133 p_tenancy_id IN NUMBER
3134 )
3135 RETURN NUMBER
3136 IS
3137 l_allocated_area NUMBER := NULL;
3138 l_lease_class_code VARCHAR2(30);
3139
3140 CURSOR cur_allocated_area IS
3141 SELECT min(allocated_area) min_area
3142 FROM pn_space_assign_cust_all
3143 WHERE tenancy_id = p_tenancy_id;
3144
3145 CURSOR get_lease_class IS
3146 SELECT leases.lease_class_code lease_code,
3147 allocated_area
3148 FROM pn_leases_all leases,
3149 pn_tenancies_all tenant
3150 WHERE leases.lease_id = tenant.lease_id
3151 AND tenant.tenancy_id = p_tenancy_id;
3152
3153
3154 BEGIN
3155 pnp_debug_pkg.debug('PN_TENANCIES_PKG.Auto_Allocated_Area (+)');
3156
3157 --
3158 FOR rec_allocated_area IN cur_allocated_area LOOP
3159 l_allocated_area := rec_allocated_area.min_area;
3160 END LOOP;
3161
3162 FOR rec_lease_code IN get_lease_class LOOP
3163 l_lease_class_code := rec_lease_code.lease_code;
3164 l_allocated_area := nvl(l_allocated_area,rec_lease_code.allocated_area);
3165 END LOOP;
3166
3167 IF l_lease_class_code = 'DIRECT' THEN
3168 l_allocated_area := NULL;
3169 END IF;
3170
3171 pnp_debug_pkg.debug('PN_TENANCIES_PKG.Auto_Allocated_Area (-)');
3172
3173 RETURN l_allocated_area;
3174
3175 END Auto_Allocated_Area;
3176
3177 -------------------------------------------------------------------------------
3178 -- FUNCTION : Availaible_Space
3179 -- INVOKED FROM :
3180 -- PURPOSE : Retrieves minimum allowable area % for the particular Tenancy_id
3181 -- HISTORY :
3182 -- 07-DEC-2006 Ram Kumar o created
3183 -------------------------------------------------------------------
3184 PROCEDURE Availaible_Space(
3188 ,p_min_pct OUT NOCOPY NUMBER
3185 p_location_id IN NUMBER
3186 ,p_from_date IN DATE
3187 ,p_to_date IN DATE
3189 )
3190 IS
3191 p_min_area NUMBER;
3192 CURSOR csr_cust_info IS
3193 SELECT cust_assign_start_date,
3194 NVL(cust_assign_end_date, p_to_date) cust_assign_end_date,
3195 nvl(allocated_area,0) allocated_area
3196 FROM pn_space_assign_cust_all
3197 WHERE location_id = p_location_id
3198 AND cust_assign_start_date <= p_to_date
3199 AND NVL(cust_assign_end_date, p_to_date) >= p_from_date;
3200
3201 CURSOR csr_emp_info IS
3202 SELECT emp_assign_start_date,
3203 NVL(emp_assign_end_date, p_to_date) emp_assign_end_date,
3204 nvl(allocated_area,0) allocated_area
3205 FROM pn_space_assign_emp_all
3206 WHERE location_id = p_location_id
3207 AND emp_assign_start_date <= p_to_date
3208 AND NVL(emp_assign_end_date, p_to_date) >= p_from_date;
3209
3210 l_loc_type_code pn_locations_all.location_type_lookup_code%TYPE;
3211 l_num_table pn_recovery_extract_pkg.number_table_TYPE;
3212 l_date_table pn_recovery_extract_pkg.date_table_TYPE;
3213 i NUMBER := 0;
3214 j NUMBER := 0;
3215
3216 BEGIN
3217 pnp_debug_pkg.debug('PN_TENANCIES_PKG.Availaible_Space (+)');
3218
3219 get_loc_info(p_location_id => p_location_id,
3220 p_from_date => p_from_date,
3221 p_to_date => p_to_date,
3222 p_loc_type_code => l_loc_type_code);
3223
3224 IF l_loc_type_code IN ('OFFICE', 'SECTION') THEN
3225
3226 FOR i IN 0 .. loc_info_tbl.count-1
3227 LOOP
3228 pn_recovery_extract_pkg.process_vacancy(
3229 p_start_date => loc_info_tbl(i).active_start_date,
3230 p_end_date => loc_info_tbl(i).active_end_date,
3231 p_area => loc_info_tbl(i).assignable_area,
3232 p_date_table => l_date_table,
3233 p_number_table => l_num_table,
3234 p_add => TRUE);
3235 END LOOP;
3236
3237 FOR rec_cust_info IN csr_cust_info
3238 LOOP
3239 pn_recovery_extract_pkg.process_vacancy(
3240 p_start_date => rec_cust_info.cust_assign_start_date,
3241 p_end_date => rec_cust_info.cust_assign_end_date,
3242 p_area => rec_cust_info.allocated_area,
3243 p_date_table => l_date_table,
3244 p_number_table => l_num_table,
3245 p_add => FALSE);
3246 END LOOP;
3247
3248 FOR rec_emp_info IN csr_emp_info
3249 LOOP
3250 pn_recovery_extract_pkg.process_vacancy(
3251 p_start_date => rec_emp_info.emp_assign_start_date,
3252 p_end_date => rec_emp_info.emp_assign_end_date,
3253 p_area => rec_emp_info.allocated_area,
3254 p_date_table => l_date_table,
3255 p_number_table => l_num_table,
3256 p_add => FALSE);
3257 END LOOP;
3258
3259 p_min_area := l_num_table(0);
3260 FOR i IN 0 .. l_num_table.count-1
3261 LOOP
3262 IF(p_min_area > l_num_table(i)) THEN
3263 p_min_area := l_num_table(i);
3264 END IF;
3265 END LOOP;
3266
3267 get_allocated_area_pct(
3268 p_cust_assign_start_date => p_from_date,
3269 p_cust_assign_end_date => p_to_date,
3270 p_allocated_area => p_min_area,
3271 p_alloc_area_pct => p_min_pct);
3272
3273 END IF;
3274
3275 pnp_debug_pkg.debug('PN_TENANCIES_PKG.Availaible_Space (-)');
3276 EXCEPTION
3277 WHEN OTHERS THEN
3278 pnp_debug_pkg.log('Availaible_Space - Errmsg: ' || sqlerrm);
3279 RAISE;
3280
3281 END Availaible_Space;
3282
3283 END pn_tenancies_pkg;
[Home] [Help]