1 PACKAGE BODY pn_space_assign_cust_pkg AS
2 /* $Header: PNSPCUSB.pls 120.12 2007/02/14 12:31:55 rdonthul ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCEDURE : Insert_Row
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 05-MAR-04 ftanudja o Replaced check_dupcust.. w/ chk_dup_cust..
10 -- 14-DEC-04 STripath o Modified for Portfolio Status Enh BUG# 4030816. Added
11 -- code to check loc is contigious assignable betn assign
12 -- start and end dates.
13 -- 30-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_cust
14 -- with _ALL table.
15 -- 08-SEP-o5 hrodda o Modified insert statement to include org_id.
16 -- 28-NOV-05 pikhar o fetched org_id using cursor.
17 -- 08-Feb-07 rdonthul o Removed the check fro duplicate space assignments
18 -- for bug fix 5864468
19 -------------------------------------------------------------------------------
20
21 PROCEDURE Insert_Row (
22 X_ROWID IN OUT NOCOPY VARCHAR2,
23 X_CUST_SPACE_ASSIGN_ID IN OUT NOCOPY NUMBER,
24 X_LOCATION_ID IN NUMBER,
25 X_CUST_ACCOUNT_ID IN NUMBER,
26 X_SITE_USE_ID IN NUMBER,
27 X_EXPENSE_ACCOUNT_ID IN NUMBER,
28 X_PROJECT_ID IN NUMBER,
29 X_TASK_ID IN NUMBER,
30 X_CUST_ASSIGN_START_DATE IN DATE,
31 X_CUST_ASSIGN_END_DATE IN DATE,
32 X_ALLOCATED_AREA_PCT IN NUMBER,
33 X_ALLOCATED_AREA IN NUMBER,
34 X_UTILIZED_AREA IN NUMBER,
35 X_CUST_SPACE_COMMENTS IN VARCHAR2,
36 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
37 X_ATTRIBUTE1 IN VARCHAR2,
38 X_ATTRIBUTE2 IN VARCHAR2,
39 X_ATTRIBUTE3 IN VARCHAR2,
40 X_ATTRIBUTE4 IN VARCHAR2,
41 X_ATTRIBUTE5 IN VARCHAR2,
42 X_ATTRIBUTE6 IN VARCHAR2,
43 X_ATTRIBUTE7 IN VARCHAR2,
44 X_ATTRIBUTE8 IN VARCHAR2,
45 X_ATTRIBUTE9 IN VARCHAR2,
46 X_ATTRIBUTE10 IN VARCHAR2,
47 X_ATTRIBUTE11 IN VARCHAR2,
48 X_ATTRIBUTE12 IN VARCHAR2,
49 X_ATTRIBUTE13 IN VARCHAR2,
50 X_ATTRIBUTE14 IN VARCHAR2,
51 X_ATTRIBUTE15 IN VARCHAR2,
52 X_CREATION_DATE IN DATE,
53 X_CREATED_BY IN NUMBER,
54 X_LAST_UPDATE_DATE IN DATE,
55 X_LAST_UPDATED_BY IN NUMBER,
56 X_LAST_UPDATE_LOGIN IN NUMBER,
57 X_ORG_ID IN NUMBER,
58 X_LEASE_ID IN NUMBER,
59 X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
60 X_RECOVERY_TYPE_CODE IN VARCHAR2,
61 X_FIN_OBLIG_END_DATE IN DATE,
62 X_TENANCY_ID IN NUMBER,
63 X_RETURN_STATUS OUT NOCOPY VARCHAR2
64 )
65 IS
66 CURSOR c IS
67 SELECT ROWID
68 FROM pn_space_assign_cust_all
69 WHERE cust_space_assign_id = x_cust_space_assign_id;
70
71 l_status VARCHAR2(100);
72 l_err_msg VARCHAR2(30);
73
74 CURSOR org_cur IS
75 SELECT org_id
76 FROM pn_locations_all
77 WHERE location_id = x_location_id;
78
79 l_org_id NUMBER;
80
81 BEGIN
82
83 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.INSERT_ROW (+) SpcAsgnId: '
84 ||x_cust_space_assign_id||', LocId: '||x_location_id
85 ||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
86 ||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
87 ||', CustId: '||x_cust_account_id);
88
89 -- Check if location is contigious Customer Assignable betn assign start and end dates.
90 pnt_locations_pkg.Check_Location_Gaps(
91 p_loc_id => x_location_id
92 ,p_str_dt => x_cust_assign_start_date
93 ,p_end_dt => NVL(x_cust_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'))
94 ,p_asgn_mode => 'CUST'
95 ,p_err_msg => l_err_msg
96 );
97
98 IF l_err_msg IS NOT NULL THEN
99 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
100 x_return_status := 'INVALID_LOC_DATE';
101 return;
102 END IF;
103
104 /* pn_space_assign_cust_pkg.chk_dup_cust_assign(
105 p_cust_acnt_id => x_cust_account_id
106 ,p_loc_id => x_location_id
107 ,p_assgn_str_dt => x_cust_assign_start_date
108 ,p_assgn_end_dt => x_cust_assign_end_date
109 ,p_return_status => l_status);
110
111 IF l_status = 'DUP_ASSIGN' THEN
112 fnd_message.set_name('PN', 'PN_SPASGN_CUSTOMER_OVRLAP_MSG');
113 x_return_status := 'DUP_ASSIGN';
114 return;
115 END IF; */
116
117 -------------------------------------------------------
118 -- Select the nextval for cust space assign id
119 -------------------------------------------------------
120 IF x_org_id IS NULL THEN
121 FOR rec IN org_cur LOOP
122 l_org_id := rec.org_id;
123 END LOOP;
124 ELSE
125 l_org_id := x_org_id;
126 END IF;
127
128 IF ( X_CUST_SPACE_ASSIGN_ID IS NULL) THEN
129 SELECT pn_space_assign_cust_s.NEXTVAL
130 INTO x_cust_space_assign_id
131 FROM DUAL;
132 END IF;
133
134 INSERT INTO pn_space_assign_cust_all
135 ( CUST_SPACE_ASSIGN_ID,
136 LOCATION_ID,
137 CUST_ACCOUNT_ID,
138 SITE_USE_ID,
139 EXPENSE_ACCOUNT_ID,
140 PROJECT_ID,
141 TASK_ID,
142 CUST_ASSIGN_START_DATE,
143 CUST_ASSIGN_END_DATE,
144 ALLOCATED_AREA_PCT,
145 ALLOCATED_AREA,
146 UTILIZED_AREA,
147 CUST_SPACE_COMMENTS,
148 LAST_UPDATE_DATE,
149 LAST_UPDATED_BY,
150 CREATION_DATE,
151 CREATED_BY,
152 LAST_UPDATE_LOGIN,
153 ATTRIBUTE_CATEGORY,
154 ATTRIBUTE1,
155 ATTRIBUTE2,
156 ATTRIBUTE3,
157 ATTRIBUTE4,
158 ATTRIBUTE5,
159 ATTRIBUTE6,
160 ATTRIBUTE7,
161 ATTRIBUTE8,
162 ATTRIBUTE9,
163 ATTRIBUTE10,
164 ATTRIBUTE11,
165 ATTRIBUTE12,
166 ATTRIBUTE13,
167 ATTRIBUTE14,
168 ATTRIBUTE15,
169 LEASE_ID,
170 RECOVERY_SPACE_STD_CODE,
171 RECOVERY_TYPE_CODE,
172 FIN_OBLIG_END_DATE,
173 TENANCY_ID,
174 ORG_ID
175 )
176 VALUES
177 (
178 X_CUST_SPACE_ASSIGN_ID,
179 X_LOCATION_ID,
180 X_CUST_ACCOUNT_ID,
181 X_SITE_USE_ID,
182 X_EXPENSE_ACCOUNT_ID,
183 X_PROJECT_ID,
184 X_TASK_ID,
185 X_CUST_ASSIGN_START_DATE,
186 X_CUST_ASSIGN_END_DATE,
187 X_ALLOCATED_AREA_PCT,
188 X_ALLOCATED_AREA,
189 X_UTILIZED_AREA,
190 X_CUST_SPACE_COMMENTS,
191 X_LAST_UPDATE_DATE,
192 X_LAST_UPDATED_BY,
193 X_CREATION_DATE,
194 X_CREATED_BY,
195 X_LAST_UPDATE_LOGIN,
196 X_ATTRIBUTE_CATEGORY,
197 X_ATTRIBUTE1,
198 X_ATTRIBUTE2,
199 X_ATTRIBUTE3,
200 X_ATTRIBUTE4,
201 X_ATTRIBUTE5,
202 X_ATTRIBUTE6,
203 X_ATTRIBUTE7,
204 X_ATTRIBUTE8,
205 X_ATTRIBUTE9,
206 X_ATTRIBUTE10,
207 X_ATTRIBUTE11,
208 X_ATTRIBUTE12,
209 X_ATTRIBUTE13,
210 X_ATTRIBUTE14,
211 X_ATTRIBUTE15,
212 X_LEASE_ID,
213 X_RECOVERY_SPACE_STD_CODE,
214 X_RECOVERY_TYPE_CODE,
215 X_FIN_OBLIG_END_DATE,
216 X_TENANCY_ID,
217 l_org_id
218 );
219
220 OPEN c;
221 FETCH c INTO x_rowid;
222 IF (c%NOTFOUND) THEN
223 CLOSE c;
224 RAISE NO_DATA_FOUND;
225 END IF;
226 CLOSE c;
227
228 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.INSERT_ROW (-) SpcAsgnId: '
229 ||x_cust_space_assign_id||', LocId: '||x_location_id
230 ||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
231 ||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
232 ||', CustId: '||x_cust_account_id);
233
234 END Insert_Row;
235
236 -----------------------------------------------------------------------
237 -- PROCDURE : LOCK_ROW
238 -----------------------------------------------------------------------
239 PROCEDURE Lock_Row (
240 X_CUST_SPACE_ASSIGN_ID IN NUMBER,
241 X_LOCATION_ID IN NUMBER,
242 X_CUST_ACCOUNT_ID IN NUMBER,
243 X_SITE_USE_ID IN NUMBER,
244 X_EXPENSE_ACCOUNT_ID IN NUMBER,
245 X_PROJECT_ID IN NUMBER,
246 X_TASK_ID IN NUMBER,
247 X_CUST_ASSIGN_START_DATE IN DATE,
248 X_CUST_ASSIGN_END_DATE IN DATE,
249 X_ALLOCATED_AREA_PCT IN NUMBER,
250 X_ALLOCATED_AREA IN NUMBER,
251 X_UTILIZED_AREA IN NUMBER,
252 X_CUST_SPACE_COMMENTS IN VARCHAR2,
253 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
254 X_ATTRIBUTE1 IN VARCHAR2,
255 X_ATTRIBUTE2 IN VARCHAR2,
256 X_ATTRIBUTE3 IN VARCHAR2,
257 X_ATTRIBUTE4 IN VARCHAR2,
258 X_ATTRIBUTE5 IN VARCHAR2,
259 X_ATTRIBUTE6 IN VARCHAR2,
260 X_ATTRIBUTE7 IN VARCHAR2,
261 X_ATTRIBUTE8 IN VARCHAR2,
262 X_ATTRIBUTE9 IN VARCHAR2,
263 X_ATTRIBUTE10 IN VARCHAR2,
264 X_ATTRIBUTE11 IN VARCHAR2,
265 X_ATTRIBUTE12 IN VARCHAR2,
266 X_ATTRIBUTE13 IN VARCHAR2,
267 X_ATTRIBUTE14 IN VARCHAR2,
268 X_ATTRIBUTE15 IN VARCHAR2,
269 X_LEASE_ID IN NUMBER,
270 X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
271 X_RECOVERY_TYPE_CODE IN VARCHAR2,
272 X_FIN_OBLIG_END_DATE IN DATE,
273 X_TENANCY_ID IN NUMBER
274 )
275 IS
276 CURSOR c1 IS
277 SELECT *
278 FROM pn_space_assign_cust_all
279 WHERE cust_space_assign_id = x_cust_space_assign_id
280 FOR UPDATE OF cust_space_assign_id NOWAIT;
281
282 BEGIN
283
284 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.LOCK_ROW (+) SpcAsgnId: '
285 ||x_cust_space_assign_id);
286
287 OPEN c1;
288 FETCH c1 INTO tlcustinfo;
289 IF (c1%NOTFOUND) THEN
290 CLOSE c1;
291 RETURN;
292 END IF;
293 CLOSE c1;
294
295 IF NOT (tlcustinfo.CUST_SPACE_ASSIGN_ID = X_CUST_SPACE_ASSIGN_ID) THEN
296 pn_var_rent_pkg.lock_row_exception('CUST_SPACE_ASSIGN_ID',tlcustinfo.CUST_SPACE_ASSIGN_ID);
297 END IF;
298
299 IF NOT ((tlcustinfo.LOCATION_ID = X_LOCATION_ID)
300 OR ((tlcustinfo.LOCATION_ID is null) AND (X_LOCATION_ID is null))) THEN
301 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',tlcustinfo.LOCATION_ID);
302 END IF;
303
304 IF NOT (tlcustinfo.CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID) THEN
305 pn_var_rent_pkg.lock_row_exception('CUST_ACCOUNT_ID',tlcustinfo.CUST_ACCOUNT_ID);
306 END IF;
307
308 IF NOT ((tlcustinfo.SITE_USE_ID = X_SITE_USE_ID)
309 OR ((tlcustinfo.SITE_USE_ID is null) AND (X_SITE_USE_ID is null))) THEN
310 pn_var_rent_pkg.lock_row_exception('SITE_USE_ID',tlcustinfo.SITE_USE_ID);
311 END IF;
312
313 IF NOT ((tlcustinfo.EXPENSE_ACCOUNT_ID = X_EXPENSE_ACCOUNT_ID)
314 OR ((tlcustinfo.EXPENSE_ACCOUNT_ID is null) AND (X_EXPENSE_ACCOUNT_ID is null))) THEN
315 pn_var_rent_pkg.lock_row_exception('EXPENSE_ACCOUNT_ID',tlcustinfo.EXPENSE_ACCOUNT_ID);
316 END IF;
317
318 IF NOT ((tlcustinfo.PROJECT_ID = X_PROJECT_ID)
319 OR ((tlcustinfo.PROJECT_ID is null) AND (X_PROJECT_ID is null))) THEN
320 pn_var_rent_pkg.lock_row_exception('PROJECT_ID',tlcustinfo.PROJECT_ID);
321 END IF;
322
323 IF NOT ((tlcustinfo.TASK_ID = X_TASK_ID)
324 OR ((tlcustinfo.TASK_ID is null) AND (X_TASK_ID is null))) THEN
325 pn_var_rent_pkg.lock_row_exception('TASK_ID',tlcustinfo.TASK_ID);
326 END IF;
327
328 IF NOT ((tlcustinfo.CUST_ASSIGN_START_DATE = X_CUST_ASSIGN_START_DATE)
329 OR ((tlcustinfo.CUST_ASSIGN_START_DATE is null) AND (X_CUST_ASSIGN_START_DATE is null))) THEN
330 pn_var_rent_pkg.lock_row_exception('CUST_ASSIGN_START_DATE',tlcustinfo.CUST_ASSIGN_START_DATE);
331 END IF;
332
333 IF NOT ((tlcustinfo.CUST_ASSIGN_END_DATE = X_CUST_ASSIGN_END_DATE)
334 OR ((tlcustinfo.CUST_ASSIGN_END_DATE is null) AND (X_CUST_ASSIGN_END_DATE is null))) THEN
335 pn_var_rent_pkg.lock_row_exception('CUST_ASSIGN_END_DATE',tlcustinfo.CUST_ASSIGN_END_DATE);
336 END IF;
337
338 IF NOT ((tlcustinfo.ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT)
339 OR ((tlcustinfo.ALLOCATED_AREA_PCT is null) AND (X_ALLOCATED_AREA_PCT is null))) THEN
340 pn_var_rent_pkg.lock_row_exception('ALLOCATED_AREA_PCT',tlcustinfo.ALLOCATED_AREA_PCT);
341 END IF;
342
343 IF NOT ((tlcustinfo.ALLOCATED_AREA = X_ALLOCATED_AREA)
344 OR ((tlcustinfo.ALLOCATED_AREA is null) AND (X_ALLOCATED_AREA is null))) THEN
345 pn_var_rent_pkg.lock_row_exception('ALLOCATED_AREA',tlcustinfo.ALLOCATED_AREA);
346 END IF;
347
348 IF NOT ((tlcustinfo.UTILIZED_AREA = X_UTILIZED_AREA)
349 OR ((tlcustinfo.UTILIZED_AREA is null) AND (X_UTILIZED_AREA is null))) THEN
350 pn_var_rent_pkg.lock_row_exception('UTILIZED_AREA',tlcustinfo.UTILIZED_AREA);
351 END IF;
352
353 IF NOT ((tlcustinfo.CUST_SPACE_COMMENTS = X_CUST_SPACE_COMMENTS)
354 OR ((tlcustinfo.CUST_SPACE_COMMENTS is null) AND (X_CUST_SPACE_COMMENTS is null))) THEN
355 pn_var_rent_pkg.lock_row_exception('CUST_SPACE_COMMENTS',tlcustinfo.CUST_SPACE_COMMENTS);
356 END IF;
357
358 IF NOT ((tlcustinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
359 OR ((tlcustinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) THEN
360 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlcustinfo.ATTRIBUTE_CATEGORY);
361 END IF;
362
363 IF NOT ((tlcustinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
364 OR ((tlcustinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) THEN
365 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlcustinfo.ATTRIBUTE1);
366 END IF;
367
368 IF NOT ((tlcustinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
369 OR ((tlcustinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) THEN
370 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlcustinfo.ATTRIBUTE2);
371 END IF;
372
373 IF NOT ((tlcustinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
374 OR ((tlcustinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) THEN
375 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlcustinfo.ATTRIBUTE3);
376 END IF;
377
378 IF NOT ((tlcustinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
379 OR ((tlcustinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) THEN
380 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlcustinfo.ATTRIBUTE4);
381 END IF;
382
383 IF NOT ((tlcustinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
384 OR ((tlcustinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) THEN
385 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlcustinfo.ATTRIBUTE5);
386 END IF;
387
388 IF NOT ((tlcustinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
389 OR ((tlcustinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) THEN
390 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlcustinfo.ATTRIBUTE6);
391 END IF;
392
393 IF NOT ((tlcustinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
394 OR ((tlcustinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) THEN
395 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlcustinfo.ATTRIBUTE7);
396 END IF;
397
398 IF NOT ((tlcustinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
399 OR ((tlcustinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) THEN
400 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlcustinfo.ATTRIBUTE8);
401 END IF;
402
403 IF NOT ((tlcustinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
404 OR ((tlcustinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) THEN
405 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlcustinfo.ATTRIBUTE9);
406 END IF;
407
408 IF NOT ((tlcustinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
409 OR ((tlcustinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) THEN
410 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlcustinfo.ATTRIBUTE10);
411 END IF;
412
413 IF NOT ((tlcustinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
414 OR ((tlcustinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) THEN
415 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlcustinfo.ATTRIBUTE11);
416 END IF;
417
418 IF NOT ((tlcustinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
419 OR ((tlcustinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) THEN
420 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlcustinfo.ATTRIBUTE12);
421 END IF;
422
423 IF NOT ((tlcustinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
424 OR ((tlcustinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) THEN
425 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlcustinfo.ATTRIBUTE13);
426 END IF;
427
428 IF NOT ((tlcustinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
429 OR ((tlcustinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) THEN
430 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlcustinfo.ATTRIBUTE14);
431 END IF;
432
433 IF NOT ((tlcustinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
434 OR ((tlcustinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) THEN
435 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlcustinfo.ATTRIBUTE15);
436 END IF;
437
438 IF NOT ((tlcustinfo.LEASE_ID = X_LEASE_ID)
439 OR ((tlcustinfo.LEASE_ID is null) AND (X_LEASE_ID is null))) THEN
440 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlcustinfo.LEASE_ID);
441 END IF;
442
443 IF NOT ((tlcustinfo.RECOVERY_SPACE_STD_CODE = X_RECOVERY_SPACE_STD_CODE)
444 OR ((tlcustinfo.RECOVERY_SPACE_STD_CODE is null) AND (X_RECOVERY_SPACE_STD_CODE is null))) THEN
445 pn_var_rent_pkg.lock_row_exception('RECOVERY_SPACE_STD_CODE',tlcustinfo.RECOVERY_SPACE_STD_CODE);
446 END IF;
447
448 IF NOT ((tlcustinfo.RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE)
449 OR ((tlcustinfo.RECOVERY_TYPE_CODE is null) AND (X_RECOVERY_TYPE_CODE is null))) THEN
450 pn_var_rent_pkg.lock_row_exception('RECOVERY_TYPE_CODE',tlcustinfo.RECOVERY_TYPE_CODE);
451 END IF;
452
453 IF NOT ((tlcustinfo.FIN_OBLIG_END_DATE = X_FIN_OBLIG_END_DATE)
454 OR ((tlcustinfo.FIN_OBLIG_END_DATE is null) AND (X_FIN_OBLIG_END_DATE is null))) THEN
455 pn_var_rent_pkg.lock_row_exception('FIN_OBLIG_END_DATE',tlcustinfo.FIN_OBLIG_END_DATE);
456 END IF;
457
458 IF NOT ((tlcustinfo.TENANCY_ID = X_TENANCY_ID)
459 OR ((tlcustinfo.TENANCY_ID is null) AND (X_TENANCY_ID is null))) THEN
460 pn_var_rent_pkg.lock_row_exception('TENANCY_ID',tlcustinfo.TENANCY_ID);
461 END IF;
462
463 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.LOCK_ROW (-) SpcAsgnId: '
464 ||x_cust_space_assign_id);
465
466 END Lock_Row;
467
468 -------------------------------------------------------------------------------
469 -- PROCEDURE : UPDATE_ROW
470 -- INVOKED FROM : UPDATE_ROW procedure
471 -- PURPOSE : updates the row
472 -- HISTORY :
473 -- 05-MAR-04 ftanudja o Replaced check_dupcust.. w/ chk_dup_cust..
474 -- 14-DEC-04 STripath o Modified for Portfolio Status Enh BUG# 4030816. Added
475 -- code to check loc is contigious assignable betn assign
476 -- start and end dates.
477 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_cust
478 -- with _ALL table.
479 -- 08-SEP-05 Hareesha o Modified insert statement to include org_id.
480 -- 08-Feb-07 Ram kumar o Removed the check for duplicate Space assignments
481 -- for bug fix 5864468
482 -------------------------------------------------------------------------------
483
484 PROCEDURE Update_Row (
485 X_CUST_SPACE_ASSIGN_ID IN NUMBER,
486 X_LOCATION_ID IN NUMBER,
487 X_CUST_ACCOUNT_ID IN NUMBER,
488 X_SITE_USE_ID IN NUMBER,
489 X_EXPENSE_ACCOUNT_ID IN NUMBER,
490 X_PROJECT_ID IN NUMBER,
491 X_TASK_ID IN NUMBER,
492 X_CUST_ASSIGN_START_DATE IN DATE,
493 X_CUST_ASSIGN_END_DATE IN DATE,
494 X_ALLOCATED_AREA_PCT IN NUMBER,
495 X_ALLOCATED_AREA IN NUMBER,
496 X_UTILIZED_AREA IN NUMBER,
497 X_CUST_SPACE_COMMENTS IN VARCHAR2,
498 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
499 X_ATTRIBUTE1 IN VARCHAR2,
500 X_ATTRIBUTE2 IN VARCHAR2,
501 X_ATTRIBUTE3 IN VARCHAR2,
502 X_ATTRIBUTE4 IN VARCHAR2,
503 X_ATTRIBUTE5 IN VARCHAR2,
504 X_ATTRIBUTE6 IN VARCHAR2,
505 X_ATTRIBUTE7 IN VARCHAR2,
506 X_ATTRIBUTE8 IN VARCHAR2,
507 X_ATTRIBUTE9 IN VARCHAR2,
508 X_ATTRIBUTE10 IN VARCHAR2,
509 X_ATTRIBUTE11 IN VARCHAR2,
510 X_ATTRIBUTE12 IN VARCHAR2,
511 X_ATTRIBUTE13 IN VARCHAR2,
512 X_ATTRIBUTE14 IN VARCHAR2,
513 X_ATTRIBUTE15 IN VARCHAR2,
514 X_LAST_UPDATE_DATE IN DATE,
515 X_LAST_UPDATED_BY IN NUMBER,
516 X_LAST_UPDATE_LOGIN IN NUMBER,
517 X_UPDATE_CORRECT_OPTION IN VARCHAR2,
518 X_CHANGED_START_DATE OUT NOCOPY DATE,
519 X_LEASE_ID IN NUMBER,
520 X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
521 X_RECOVERY_TYPE_CODE IN VARCHAR2,
522 X_FIN_OBLIG_END_DATE IN DATE,
523 X_TENANCY_ID IN NUMBER,
524 X_RETURN_STATUS OUT NOCOPY VARCHAR2
525 )
526 IS
527
528 l_cust_space_assign_id NUMBER;
529 l_fin_oblig_end_date DATE;
530 l_status VARCHAR2(100);
531 l_err_msg VARCHAR2(30);
532
533 BEGIN
534 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW (+) SpcAsgnId: '
535 ||x_cust_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
536 ||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
537 ||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
538 ||', CustId: '||x_cust_account_id);
539
540 -- Check if location is contigious Customer Assignable betn assign start and end dates.
541 IF (x_location_id <> tlcustinfo.location_id) OR
542 (x_cust_assign_start_date <> tlcustinfo.cust_assign_start_date) OR
543 (NVL(x_cust_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) <>
544 NVL(tlcustinfo.cust_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')))
545 THEN
546 pnt_locations_pkg.Check_Location_Gaps(
547 p_loc_id => x_location_id
548 ,p_str_dt => x_cust_assign_start_date
549 ,p_end_dt => NVL(x_cust_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'))
550 ,p_asgn_mode => 'CUST'
551 ,p_err_msg => l_err_msg
552 );
553
554 IF l_err_msg IS NOT NULL THEN
555 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
556 x_return_status := 'INVALID_LOC_DATE';
557 return;
558 END IF;
559 END IF;
560
561 /*IF x_cust_account_id <> tlcustinfo.cust_account_id THEN
562
563 pn_space_assign_cust_pkg.chk_dup_cust_assign(
564 p_cust_acnt_id => x_cust_account_id
565 ,p_loc_id => x_location_id
566 ,p_assgn_str_dt => x_cust_assign_start_date
567 ,p_assgn_end_dt => x_cust_assign_end_date
568 ,p_return_status => l_status);
569
570 IF l_status = 'DUP_ASSIGN' THEN
571 fnd_message.set_name('PN', 'PN_SPASGN_CUSTOMER_OVRLAP_MSG');
572 x_return_status := 'DUP_ASSIGN';
573 return;
574 END IF;
575
576 END IF; */
577
578 IF X_UPDATE_CORRECT_OPTION = 'UPDATE' THEN
579
580 SELECT pn_space_assign_cust_s.NEXTVAL
581 INTO l_cust_space_assign_id
582 FROM DUAL;
583
584 IF X_FIN_OBLIG_END_DATE IS NOT NULL THEN
585 l_fin_oblig_end_date := X_CUST_ASSIGN_START_DATE - 1;
586 ELSE
587 l_fin_oblig_end_date := NULL;
588 END IF;
589
590
591 INSERT INTO pn_space_assign_cust_all
592 (CUST_SPACE_ASSIGN_ID,
593 LOCATION_ID,
594 CUST_ACCOUNT_ID,
595 SITE_USE_ID,
596 EXPENSE_ACCOUNT_ID,
597 PROJECT_ID,
598 TASK_ID,
599 CUST_ASSIGN_START_DATE,
600 CUST_ASSIGN_END_DATE,
601 ALLOCATED_AREA_PCT,
602 ALLOCATED_AREA,
603 UTILIZED_AREA,
604 CUST_SPACE_COMMENTS,
605 LAST_UPDATE_DATE,
606 LAST_UPDATED_BY,
607 CREATION_DATE,
608 CREATED_BY,
609 LAST_UPDATE_LOGIN,
610 ATTRIBUTE_CATEGORY,
611 ATTRIBUTE1,
612 ATTRIBUTE2,
613 ATTRIBUTE3,
614 ATTRIBUTE4,
615 ATTRIBUTE5,
616 ATTRIBUTE6,
617 ATTRIBUTE7,
618 ATTRIBUTE8,
619 ATTRIBUTE9,
620 ATTRIBUTE10,
621 ATTRIBUTE11,
622 ATTRIBUTE12,
623 ATTRIBUTE13,
624 ATTRIBUTE14,
625 ATTRIBUTE15,
626 LEASE_ID,
627 RECOVERY_SPACE_STD_CODE,
628 RECOVERY_TYPE_CODE,
629 FIN_OBLIG_END_DATE,
630 TENANCY_ID,
631 ORG_ID)
632 VALUES
633 (l_cust_space_assign_id,
634 tlcustinfo.LOCATION_ID,
635 tlcustinfo.CUST_ACCOUNT_ID,
636 tlcustinfo.SITE_USE_ID,
637 tlcustinfo.EXPENSE_ACCOUNT_ID,
638 tlcustinfo.PROJECT_ID,
639 tlcustinfo.TASK_ID,
640 tlcustinfo.CUST_ASSIGN_START_DATE,
641 X_CUST_ASSIGN_START_DATE - 1,
642 tlcustinfo.ALLOCATED_AREA_PCT,
643 tlcustinfo.ALLOCATED_AREA,
644 tlcustinfo.UTILIZED_AREA,
645 tlcustinfo.CUST_SPACE_COMMENTS,
646 tlcustinfo.LAST_UPDATE_DATE,
647 tlcustinfo.LAST_UPDATED_BY,
648 tlcustinfo.CREATION_DATE,
649 tlcustinfo.CREATED_BY,
650 tlcustinfo.LAST_UPDATE_LOGIN,
651 tlcustinfo.ATTRIBUTE_CATEGORY,
652 tlcustinfo.ATTRIBUTE1,
653 tlcustinfo.ATTRIBUTE2,
654 tlcustinfo.ATTRIBUTE3,
655 tlcustinfo.ATTRIBUTE4,
656 tlcustinfo.ATTRIBUTE5,
657 tlcustinfo.ATTRIBUTE6,
658 tlcustinfo.ATTRIBUTE7,
659 tlcustinfo.ATTRIBUTE8,
660 tlcustinfo.ATTRIBUTE9,
661 tlcustinfo.ATTRIBUTE10,
662 tlcustinfo.ATTRIBUTE11,
663 tlcustinfo.ATTRIBUTE12,
664 tlcustinfo.ATTRIBUTE13,
665 tlcustinfo.ATTRIBUTE14,
666 tlcustinfo.ATTRIBUTE15,
667 tlcustinfo.LEASE_ID,
668 tlcustinfo.RECOVERY_SPACE_STD_CODE,
669 tlcustinfo.RECOVERY_TYPE_CODE,
670 l_fin_oblig_end_date,
671 tlcustinfo.TENANCY_ID,
672 tlcustinfo.org_id
673 );
674
675 END IF;
676
677
678 UPDATE PN_SPACE_ASSIGN_CUST_ALL SET
679 LOCATION_ID = X_LOCATION_ID,
680 CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID,
681 SITE_USE_ID = X_SITE_USE_ID,
682 EXPENSE_ACCOUNT_ID = X_EXPENSE_ACCOUNT_ID,
683 PROJECT_ID = X_PROJECT_ID,
684 TASK_ID = X_TASK_ID,
685 CUST_ASSIGN_START_DATE = X_CUST_ASSIGN_START_DATE,
686 CUST_ASSIGN_END_DATE = X_CUST_ASSIGN_END_DATE,
687 ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT,
688 ALLOCATED_AREA = X_ALLOCATED_AREA,
689 UTILIZED_AREA = X_UTILIZED_AREA,
690 CUST_SPACE_COMMENTS = X_CUST_SPACE_COMMENTS,
691 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
692 ATTRIBUTE1 = X_ATTRIBUTE1,
693 ATTRIBUTE2 = X_ATTRIBUTE2,
694 ATTRIBUTE3 = X_ATTRIBUTE3,
695 ATTRIBUTE4 = X_ATTRIBUTE4,
696 ATTRIBUTE5 = X_ATTRIBUTE5,
697 ATTRIBUTE6 = X_ATTRIBUTE6,
698 ATTRIBUTE7 = X_ATTRIBUTE7,
699 ATTRIBUTE8 = X_ATTRIBUTE8,
700 ATTRIBUTE9 = X_ATTRIBUTE9,
701 ATTRIBUTE10 = X_ATTRIBUTE10,
702 ATTRIBUTE11 = X_ATTRIBUTE11,
703 ATTRIBUTE12 = X_ATTRIBUTE12,
704 ATTRIBUTE13 = X_ATTRIBUTE13,
705 ATTRIBUTE14 = X_ATTRIBUTE14,
706 ATTRIBUTE15 = X_ATTRIBUTE15,
707 CUST_SPACE_ASSIGN_ID = X_CUST_SPACE_ASSIGN_ID,
708 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
709 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
710 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
711 LEASE_ID = X_LEASE_ID,
712 RECOVERY_SPACE_STD_CODE = X_RECOVERY_SPACE_STD_CODE,
713 RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE,
714 FIN_OBLIG_END_DATE = X_FIN_OBLIG_END_DATE,
715 TENANCY_ID = X_TENANCY_ID
716 WHERE CUST_SPACE_ASSIGN_ID = X_CUST_SPACE_ASSIGN_ID;
717
718 X_CHANGED_START_DATE := X_CUST_ASSIGN_START_DATE;
719
720 IF (SQL%NOTFOUND) THEN
721 RAISE NO_DATA_FOUND;
722 END IF;
723
724 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW (-) SpcAsgnId: '
725 ||x_cust_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
726 ||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
727 ||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
728 ||', CustId: '||x_cust_account_id);
729
730 END Update_Row;
731
732 -------------------------------------------------------------------------------
733 -- PROCEDURE : Delete_Row
734 -- INVOKED FROM : Delete_Row procedure
735 -- PURPOSE : deletes the row
736 -- HISTORY :
737 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_cust
738 -- with _ALL table.
739 -------------------------------------------------------------------------------
740 PROCEDURE Delete_Row (
741 X_CUST_SPACE_ASSIGN_ID IN NUMBER
742 )
743 IS
744
745 BEGIN
746
747 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.DELETE_ROW (+) SpcAsgnId: '
748 ||x_cust_space_assign_id);
749
750
751 DELETE FROM pn_space_assign_cust_all
752 WHERE cust_space_assign_id = x_cust_space_assign_id;
753
754 IF (SQL%NOTFOUND) THEN
755 RAISE NO_DATA_FOUND;
756 END IF;
757
758 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.DELETE_ROW (-) SpcAsgnId: '
759 ||x_cust_space_assign_id);
760
761 END Delete_Row;
762
763 -------------------------------------------------------------------------------
764 -- PROCEDURE : CHK_DUP_CUST_ASSIGN
765 -- PURPOSE : The procedure checks to see if there exists another assignment
766 -- record for the same customer, for the same date range.
767 -- If there exists one then it stops user from doing the assignment.
768 -- HISTORY :
769 -- 05-MAR-04 ftanudja o Copied from pn_tenancies_pkg v115.50
770 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_cust
771 -- with _ALL table.
772 -------------------------------------------------------------------------------
773
774 PROCEDURE chk_dup_cust_assign(
775 p_cust_acnt_id IN NUMBER
776 ,p_loc_id IN NUMBER
777 ,p_assgn_str_dt IN DATE
778 ,p_assgn_end_dt IN DATE
779 ,p_return_status OUT NOCOPY VARCHAR2
780 )
781 IS
782 l_err_flag VARCHAR2(1) := 'N';
783
784 CURSOR check_cust_assignment IS
785 SELECT 'Y'
786 FROM DUAL
787 WHERE EXISTS (SELECT NULL
788 FROM pn_space_assign_cust_all
789 WHERE cust_account_id = p_cust_acnt_id
790 AND location_id = p_loc_id
791 AND cust_assign_start_date <= p_assgn_end_dt
792 AND NVL(cust_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
793 >= p_assgn_str_dt);
794 BEGIN
795 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.CHK_DUP_CUST_ASSIGN (+)');
796
797 OPEN check_cust_assignment;
798 FETCH check_cust_assignment INTO l_err_flag;
799 CLOSE check_cust_assignment;
800
801 IF NVL(l_err_flag,'N') = 'Y' THEN
802 p_return_status := 'DUP_ASSIGN';
803 END IF;
804
805 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.CHK_DUP_CUST_ASSIGN (-) '||p_return_status);
806 END chk_dup_cust_assign;
807
808 -------------------------------------------------------------------
809 -- PROCEDURE : GET_DUP_CUST_ASSIGN_COUNT
810 -- DESCRIPTION: Counts number of assignments for a given parameter
811 -- 05-MAR-2004 Satish Tripathi o Created.
812 -- 08-MAR-2004 Satish Tripathi o Added parameter p_dup_assign_count.
813 -------------------------------------------------------------------
814 PROCEDURE get_dup_cust_assign_count(
815 p_cust_acnt_id IN NUMBER
816 ,p_loc_id IN NUMBER
817 ,p_assgn_str_dt IN DATE
818 ,p_assgn_end_dt IN DATE
819 ,p_assign_count OUT NOCOPY NUMBER
820 ,p_dup_assign_count OUT NOCOPY NUMBER
821 )
822 IS
823 l_assign_count NUMBER := 0;
824 l_dup_assign_count NUMBER := 0;
825
826 CURSOR get_cust_assignment IS
827 SELECT cust_space_assign_id, cust_account_id
828 FROM pn_space_assign_cust_all
829 WHERE location_id = p_loc_id
830 AND cust_assign_start_date <= p_assgn_end_dt
831 AND NVL(cust_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
832 >= p_assgn_str_dt;
833 BEGIN
834 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.GET_DUP_CUST_ASSIGN_COUNT (+)');
835
836 FOR cust_assign IN get_cust_assignment
837 LOOP
838 l_assign_count := l_assign_count + 1;
839 IF cust_assign.cust_account_id = p_cust_acnt_id THEN
840 l_dup_assign_count := l_dup_assign_count + 1;
841 END IF;
842 END LOOP;
843
844 p_assign_count := l_assign_count;
845 p_dup_assign_count := l_dup_assign_count;
846
847 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.GET_DUP_CUST_ASSIGN_COUNT (-) '||p_assign_count);
848 END get_dup_cust_assign_count;
849
850 -----------------------------------------------------------------------
851 -- FUNCTION : check_assign_arcl_line
852 -- PURPOSE : This function checks to see if there exists an area class
853 -- detail line having customer space assignment id in question.
854 --
855 -- IN PARAM : Customer Space Assignment Id.
856 -- History :
857 -- 26-JUN-2003 Mrinal Misra o Created.
858 -----------------------------------------------------------------------
859 FUNCTION check_assign_arcl_line(p_cust_space_assign_id IN NUMBER)
860 RETURN BOOLEAN IS
861
862 l_exists VARCHAR2(1);
863
864 CURSOR cust_arcl_cur IS
865 SELECT 'Y'
866 FROM DUAL
867 WHERE EXISTS (SELECT NULL
868 FROM pn_rec_arcl_dtlln_all
869 WHERE cust_space_assign_id = p_cust_space_assign_id);
870 BEGIN
871
872 l_exists := 'N';
873
874 OPEN cust_arcl_cur;
875 FETCH cust_arcl_cur INTO l_exists;
876 CLOSE cust_arcl_cur;
877
878 IF l_exists = 'Y' THEN
879 RETURN TRUE;
880 ELSE
881 RETURN FALSE;
882 END IF;
883
884 END check_assign_arcl_line;
885
886 -------------------------------------------------------------------------------
887 -- FUNCTION : assignment_split
888 -- PURPOSE : This function splits assignment records based on dates
889 -- of existing assignments for re-distribution when profile
890 -- PN_AUTO_SPACE_ASSIGN is set to YES.
891 -- IF location L1 exists as below with an assignable space of 1200
892 -- from 1/1/01 to end of time(eot)
893 -- If space assignment for S1 for customer C1 for location L1 is
894 -- assigned from 1/1/01 to eot as below
895 --
896 -- 1200 1200.. 100%
897 -- L1 |--------------------eot L1,S1,C1 |------------------------eot
898 -- 1/1 1/1
899 --
900 -- and then another assignment S2 for customer C2 for the same
901 -- location is made from 2/1/01 to 3/31/01 then the following
902 -- assignments will be created and area re-distributed
903 --
904 -- 1200 1200.. 100%
905 -- L1 |--------------------eot L1,S1,C1 |----|
906 -- 1/1 1/1 1/31
907 -- 600..50%
908 -- L1,S2,C2 |------|
909 -- 2/1 3/31
910 -- 600..50%
911 -- L1,S2,C1 |------|
912 -- 2/1 3/31
913 -- 1200..100%
914 -- L1,S2,C1 |---------------------eot
915 -- 4/1
916 -- Now if the location area is changed from 1200 to 1201 as of
917 -- 2/15/01 then the following assignments will be created and area
918 -- re-distributed
919 --
920 -- 1200 1201 1200.. 100%
921 -- L1 |-------|------------eot L1,S1,C1 |----|
922 -- 1/1 2/15 1/1 1/31
923 -- 600..50%
924 -- L1,S2,C2 |----|
925 -- 2/1 2/14
926 -- 601.5---50%
927 -- L1,S2,C2 |----|
928 -- 2/15 3/31
929 -- 601.5---50%
930 -- L1,S2,C1 |----|
931 -- 2/15 3/31
932 -- 600..50%
933 -- L1,S2,C1 |----|
934 -- 2/1 2/14
935 -- 1201..100%
936 -- L1,S2,C1 |---------------------eot
937 -- 4/1
938 --
939 --
940 -- IN PARAM : Location Id.
941 -- History :
942 -- 20-OCT-03 DThota o Created. Fix for bug # 3234403
943 -- 05-NOV-03 DThota o Initialized pn_space_assign_emp_pkg.tlempinfo
944 -- := emp_split_rec
945 -- 06-NOV-03 DThota o Aliased assignable_area to allocated_area
946 -- in csr_main.
947 -- 07-NOV-03 DThota o Used min on active_start_date and max on
948 -- active_end_date and used grouping logic in
949 -- cursor csr_main in the last UNION ALL so that
950 -- in the event that a location is split without
951 -- change in the assignable_area only those start
952 -- and end dates will be picked up where the
953 -- assignable_area has changed. bug # 3243309.
954 -- Changed the WHERE clause of cursor
955 -- csr_location_area
956 -- 02-JUN-04 STripathi o Call Defrag_Contig_Assign at end.
957 -- 30-SEP-04 STripathi o Distribute area_pct_and_area only when Auto
958 -- Space Dist= Y.
959 -- 08-OCT-04 STripathi o For Auto Space Dist= Y, update area of split records.
960 -- 22-FEB-05 MMisra o Fixed Bug # 4194998. Added ORDER BY clause in
961 -- csr_main cursor query.
962 -- 07-MAR-05 ftanudja o #4199297 - Added start and end date to
963 -- assignment_split().
964 -- 19-MAY-05 ftanudja o #4349490 - Changed csr_emp and csr_cust to increase
965 -- range by +/- 1 day so that split assignments are
966 -- included.
967 -- 21-JUN-05 hareesha o Bug 4284035 - Replaced pn_space_assign_cust,
968 -- pn_space_assign_emp and pn_loactions with _ALL
969 -- table.
970 -- 25-Aug-05 hareesha o Bug 4551557 - Modified csr_main cursor query to
971 -- include space assignments starting after the specified
972 -- end_date.
973 -- 28-NOV-05 pikhar o passed org_id in pn_mo_cache_utils.get_profile_value
974 -- 04-APR-06 Hareesha o Bug #5202023 Fetched org_id from pn_locations_all
975 -- instead of pn_space_assign_cust_all because
976 -- assignment_split could be called for
977 -- employee space assignment too.
978 -------------------------------------------------------------------------------
979
980 -- 102403 -- date track space assignment
981
982 PROCEDURE assignment_split(p_location_id IN PN_LOCATIONS_ALL.location_id%TYPE,
983 p_start_date IN pn_locations_all.active_start_date%TYPE,
984 p_end_date IN pn_locations_all.active_end_date%TYPE
985 ) IS
986
987 -------------------------------------------------------------------------------------
988 -- This cursor is used to get customers and employees assigned to a location and their date info
989 -- to get dates for which the assignment records need to be split using the procedure
990 -- process vacancy
991 -------------------------------------------------------------------------------------
992 CURSOR csr_main IS
993 SELECT cust_assign_start_date start_date
994 ,NVL(cust_assign_end_date , to_date('12/31/4712','MM/DD/YYYY')) end_date
995 ,allocated_area
996 ,location_id
997 ,'CUST: '||cust_space_assign_id assign_type_id
998 FROM pn_space_assign_cust_all
999 WHERE location_id = p_location_id
1000 AND cust_assign_start_date <= p_end_date
1001 AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= p_start_date
1002 UNION ALL
1003 SELECT cust_assign_start_date start_date
1004 ,NVL(cust_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
1005 ,allocated_area
1006 ,location_id
1007 ,'CUST: '||cust_space_assign_id assign_type_id
1008 FROM pn_space_assign_cust_all
1009 WHERE location_id = p_location_id
1010 AND cust_assign_start_date > p_end_date
1011 UNION ALL
1012 SELECT emp_assign_start_date start_date
1013 ,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
1014 ,allocated_area
1015 ,location_id
1016 ,'EMP: '||emp_space_assign_id assign_type_id
1017 FROM pn_space_assign_emp_all
1018 WHERE location_id = p_location_id
1019 AND emp_assign_start_date <= p_end_date
1020 AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= p_start_date
1021 UNION ALL
1022 SELECT emp_assign_start_date start_date
1023 ,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
1024 ,allocated_area
1025 ,location_id
1026 ,'EMP: '||emp_space_assign_id assign_type_id
1027 FROM pn_space_assign_emp_all
1028 WHERE location_id = p_location_id
1029 AND emp_assign_start_date > p_end_date
1030 UNION ALL
1031 SELECT min(active_start_date) start_date
1032 ,max(NVL(active_end_date, to_date('12/31/4712','MM/DD/YYYY'))) end_date
1033 ,assignable_area allocated_area
1034 ,location_id
1035 ,'LOCN.' assign_type_id
1036 FROM pn_locations_all
1037 WHERE location_id = p_location_id
1038 AND active_start_date <= p_end_date
1039 AND active_end_date >= p_start_date
1040 GROUP BY assignable_area,location_id
1041 ORDER BY start_date;
1042
1043 -------------------------------------------------------------------------------------
1044 -- This cursor is used to get customers assigned to a location and their date info
1045 -- to get dates for which the assignment records need to be redistributed
1046 -------------------------------------------------------------------------------------
1047 CURSOR csr_cust IS
1048 SELECT cust_assign_start_date
1049 ,NVL(cust_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) cust_assign_end_date
1050 ,allocated_area
1051 ,allocated_area_pct
1052 ,location_id
1053 ,ROWID
1054 FROM pn_space_assign_cust_all
1055 WHERE location_id = p_location_id
1056 AND cust_assign_start_date <= (p_end_date + 1)
1057 AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= (p_start_date - 1)
1058 ;
1059
1060 -------------------------------------------------------------------------------------
1061 -- This cursor is used to get employees assigned to a location and their date info
1062 -- to get dates for which the assignment records need to be redistributed
1063 -------------------------------------------------------------------------------------
1064 CURSOR csr_emp IS
1065 SELECT emp_assign_start_date
1066 ,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) emp_assign_end_date
1067 ,allocated_area
1068 ,allocated_area_pct
1069 ,location_id
1070 ,ROWID
1071 FROM pn_space_assign_emp_all
1072 WHERE location_id = p_location_id
1073 AND emp_assign_start_date <= (p_end_date + 1)
1074 AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= (p_start_date - 1);
1075
1076 -------------------------------------------------------------------------------------
1077 -- This cursor is used to get all customer assignment records whose start date is less than
1078 -- and end date greater than the split date returned by 'process vacancy'.
1079 -------------------------------------------------------------------------------------
1080 CURSOR csr_cust_split(p_as_of_date PN_SPACE_ASSIGN_CUST_ALL.cust_assign_start_date%TYPE) IS
1081 SELECT *
1082 FROM pn_space_assign_cust_all
1083 WHERE location_id = p_location_id
1084 AND cust_assign_start_date < p_as_of_date
1085 AND NVL(cust_assign_end_date,to_date('12/31/4712','MM/DD/YYYY')) >= p_as_of_date
1086 ORDER BY cust_assign_start_date,cust_assign_end_date;
1087
1088
1089 -------------------------------------------------------------------------------------
1090 -- This cursor is used to get all employee assignment records whose start date is less than
1091 -- and end date greater than the split date returned by 'process vacancy'.
1092 -------------------------------------------------------------------------------------
1093 CURSOR csr_emp_split(p_as_of_date PN_SPACE_ASSIGN_EMP_ALL.emp_assign_start_date%TYPE) IS
1094 SELECT *
1095 FROM pn_space_assign_emp_all
1096 WHERE location_id = p_location_id
1097 AND emp_assign_start_date < p_as_of_date
1098 AND NVL(emp_assign_end_date,to_date('12/31/4712','MM/DD/YYYY')) >= p_as_of_date
1099 ORDER BY emp_assign_start_date, emp_assign_end_date;
1100
1101 -------------------------------------------------------------------------------------
1102 -- This cursor is used to get the latest area for a location based on the assignment dates
1103 -- after the split to update the assignments with the right area
1104 -------------------------------------------------------------------------------------
1105 CURSOR csr_location_area(p_location_id PN_LOCATIONS_ALL.location_id%TYPE
1106 ,p_start_date PN_LOCATIONS_ALL.active_start_date%TYPE
1107 ,p_end_date PN_LOCATIONS_ALL.active_end_date%TYPE) IS
1108 SELECT assignable_area
1109 FROM pn_locations_all
1110 WHERE location_id = p_location_id
1111 AND p_start_date between active_start_date
1112 AND NVL(active_end_date,to_date('12/31/4712','MM/DD/YYYY'))
1113 ;
1114
1115 l_num_table pn_recovery_extract_pkg.number_table_TYPE;
1116 l_date_table pn_recovery_extract_pkg.date_table_TYPE;
1117 l_date DATE := NULL;
1118 p_date1 DATE := NULL;
1119 l_start_date DATE := NULL;
1120 l_end_date DATE := NULL;
1121 i NUMBER := 0;
1122 l_assignable_area PN_LOCATIONS_ALL.assignable_area%TYPE := 0;
1123 l_allocated_area NUMBER;
1124 l_return_status VARCHAR2(100) := NULL;
1125 l_profile VARCHAR2(1);
1126
1127 CURSOR org_cur IS
1128 SELECT org_id
1129 FROM pn_locations_all
1130 WHERE location_id = p_location_id;
1131
1132 /* S.N. Bug 456550 */
1133 CURSOR office_sec_cur(b_location_id number) IS
1134 SELECT 1 FROM DUAL
1135 WHERE EXISTS
1136 (SELECT '1'
1137 FROM pn_locations_all loc
1138 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1139 AND loc.location_id = b_location_id
1140 );
1141 /* E.N. Bug 456550 */
1142
1143 l_org_id NUMBER;
1144
1145
1146 BEGIN
1147
1148 FOR rec IN org_cur LOOP
1149 l_org_id := rec.org_id;
1150 EXIT;
1151 END LOOP;
1152
1153 l_profile := pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id);
1154
1155 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.Assignment_Split (+) Loc Id: '||p_location_id
1156 ||', l_profile: '||l_profile);
1157
1158 i := 1;
1159 FOR main_rec IN csr_main
1160
1161 LOOP
1162
1163 pnp_debug_pkg.debug('Csr_Main i: '||i
1164 ||', Assign Start Dt: '||main_rec.start_date
1165 ||', End Dt: '||main_rec.end_date
1166 ||', Area: '||main_rec.allocated_area
1167 ||', Type/Id: '||main_rec.assign_type_id
1168 );
1169
1170 -- Populates the date table with the dates needed to spilt the employee assignments
1171 pn_recovery_extract_pkg.process_vacancy(
1172 p_start_date => main_rec.start_date,
1173 p_end_date => main_rec.end_date,
1174 p_area => NVL(main_rec.allocated_area, 0),
1175 p_date_table => l_date_table,
1176 p_number_table => l_num_table,
1177 p_add => TRUE);
1178 i := i + 1;
1179 END LOOP;
1180
1181 i := 1;
1182
1183 -- do not want any record to be split/created with the 0th date or with
1184 -- the last date
1185
1186 FOR i IN 1 .. l_date_table.count-1
1187 LOOP
1188
1189 pnp_debug_pkg.debug('counter i= '||i||', date table= '||l_date_table(i));
1190 IF l_date_table(i) > to_date('12/31/4712','MM/DD/YYYY') THEN
1191 pnp_debug_pkg.debug('date table.......exit '|| l_date_table(i));
1192 EXIT;
1193 END IF;
1194
1195 p_date1:= l_date_table(i);
1196
1197 FOR cust_split_rec IN csr_cust_split(l_date_table(i))
1198 LOOP
1199
1200 pnp_debug_pkg.debug('Update Cust Row Assign_Id: '|| cust_split_rec.cust_space_assign_id
1201 ||', Loc_Id: '|| cust_split_rec.location_id
1202 ||', Cust_Id: '|| cust_split_rec.cust_account_id);
1203
1204 tlcustinfo := NULL;
1205 tlcustinfo := cust_split_rec;
1206 ---------------------------------------------------------------------------------------
1207 -- Splits the existing assignment with the (date-1) passed from the date table returned
1208 -- by process vacancy and creates a new assignment with the split date
1209 ---------------------------------------------------------------------------------------
1210 PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW(
1211 X_CUST_SPACE_ASSIGN_ID => cust_split_rec.CUST_SPACE_ASSIGN_ID
1212 ,X_LOCATION_ID => cust_split_rec.LOCATION_ID
1213 ,X_CUST_ACCOUNT_ID => cust_split_rec.CUST_ACCOUNT_ID
1214 ,X_SITE_USE_ID => NULL
1215 ,X_EXPENSE_ACCOUNT_ID => cust_split_rec.EXPENSE_ACCOUNT_ID
1216 ,X_PROJECT_ID => cust_split_rec.PROJECT_ID
1217 ,X_TASK_ID => cust_split_rec.TASK_ID
1218 ,X_CUST_ASSIGN_START_DATE => l_date_table(i)
1219 ,X_CUST_ASSIGN_END_DATE => cust_split_rec.CUST_ASSIGN_END_DATE
1220 ,X_ALLOCATED_AREA_PCT => cust_split_rec.ALLOCATED_AREA_PCT
1221 ,X_ALLOCATED_AREA => cust_split_rec.ALLOCATED_AREA
1222 ,X_UTILIZED_AREA => cust_split_rec.UTILIZED_AREA
1223 ,X_CUST_SPACE_COMMENTS => cust_split_rec.CUST_SPACE_COMMENTS
1224 ,X_ATTRIBUTE_CATEGORY => cust_split_rec.ATTRIBUTE_CATEGORY
1225 ,X_ATTRIBUTE1 => cust_split_rec.ATTRIBUTE1
1226 ,X_ATTRIBUTE2 => cust_split_rec.ATTRIBUTE2
1227 ,X_ATTRIBUTE3 => cust_split_rec.ATTRIBUTE3
1228 ,X_ATTRIBUTE4 => cust_split_rec.ATTRIBUTE4
1229 ,X_ATTRIBUTE5 => cust_split_rec.ATTRIBUTE5
1230 ,X_ATTRIBUTE6 => cust_split_rec.ATTRIBUTE6
1231 ,X_ATTRIBUTE7 => cust_split_rec.ATTRIBUTE7
1232 ,X_ATTRIBUTE8 => cust_split_rec.ATTRIBUTE8
1233 ,X_ATTRIBUTE9 => cust_split_rec.ATTRIBUTE9
1234 ,X_ATTRIBUTE10 => cust_split_rec.ATTRIBUTE10
1235 ,X_ATTRIBUTE11 => cust_split_rec.ATTRIBUTE11
1236 ,X_ATTRIBUTE12 => cust_split_rec.ATTRIBUTE12
1237 ,X_ATTRIBUTE13 => cust_split_rec.ATTRIBUTE13
1238 ,X_ATTRIBUTE14 => cust_split_rec.ATTRIBUTE14
1239 ,X_ATTRIBUTE15 => cust_split_rec.ATTRIBUTE15
1240 ,X_LEASE_ID => cust_split_rec.LEASE_ID
1241 ,X_TENANCY_ID => cust_split_rec.TENANCY_ID
1242 ,X_RECOVERY_SPACE_STD_CODE => cust_split_rec.RECOVERY_SPACE_STD_CODE
1243 ,X_RECOVERY_TYPE_CODE => cust_split_rec.RECOVERY_TYPE_CODE
1244 ,X_FIN_OBLIG_END_DATE => cust_split_rec.FIN_OBLIG_END_DATE
1245 ,X_LAST_UPDATE_DATE => SYSDATE
1246 ,X_LAST_UPDATED_BY => 1
1247 ,X_LAST_UPDATE_LOGIN => 1
1248 ,X_UPDATE_CORRECT_OPTION => 'UPDATE'
1249 ,X_CHANGED_START_DATE => l_date
1250 ,X_RETURN_STATUS => l_return_status
1251 );
1252
1253 END LOOP;
1254
1255 FOR emp_split_rec IN csr_emp_split(l_date_table(i))
1256 LOOP
1257
1258 pnp_debug_pkg.debug('Update Emp Row Assign_Id: '|| emp_split_rec.emp_space_assign_id
1259 ||', Loc_Id: '|| emp_split_rec.location_id
1260 ||', Emp_Id: '|| emp_split_rec.person_id);
1261
1262 pn_space_assign_emp_pkg.tlempinfo := emp_split_rec;
1263 ---------------------------------------------------------------------------------------
1264 -- Splits the existing assignment with the (date-1) passed from the date table returned
1265 -- by process vacancy and creates a new assignment with the split date
1266 ---------------------------------------------------------------------------------------
1267 PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW(
1268 X_EMP_SPACE_ASSIGN_ID => emp_split_rec.EMP_SPACE_ASSIGN_ID
1269 ,X_ATTRIBUTE1 => emp_split_rec.ATTRIBUTE1
1270 ,X_ATTRIBUTE2 => emp_split_rec.ATTRIBUTE2
1271 ,X_ATTRIBUTE3 => emp_split_rec.ATTRIBUTE3
1272 ,X_ATTRIBUTE4 => emp_split_rec.ATTRIBUTE4
1273 ,X_ATTRIBUTE5 => emp_split_rec.ATTRIBUTE5
1274 ,X_ATTRIBUTE6 => emp_split_rec.ATTRIBUTE6
1275 ,X_ATTRIBUTE7 => emp_split_rec.ATTRIBUTE7
1276 ,X_ATTRIBUTE8 => emp_split_rec.ATTRIBUTE8
1277 ,X_ATTRIBUTE9 => emp_split_rec.ATTRIBUTE9
1278 ,X_ATTRIBUTE10 => emp_split_rec.ATTRIBUTE10
1279 ,X_ATTRIBUTE11 => emp_split_rec.ATTRIBUTE11
1280 ,X_ATTRIBUTE12 => emp_split_rec.ATTRIBUTE12
1281 ,X_ATTRIBUTE13 => emp_split_rec.ATTRIBUTE13
1282 ,X_ATTRIBUTE14 => emp_split_rec.ATTRIBUTE14
1283 ,X_ATTRIBUTE15 => emp_split_rec.ATTRIBUTE15
1284 ,X_LOCATION_ID => emp_split_rec.LOCATION_ID
1285 ,X_PERSON_ID => emp_split_rec.PERSON_ID
1286 ,X_PROJECT_ID => emp_split_rec.PROJECT_ID
1287 ,X_TASK_ID => emp_split_rec.TASK_ID
1288 ,X_EMP_ASSIGN_START_DATE => l_date_table(i)
1289 ,X_EMP_ASSIGN_END_DATE => emp_split_rec.EMP_ASSIGN_END_DATE
1290 ,X_COST_CENTER_CODE => emp_split_rec.COST_CENTER_CODE
1291 ,X_ALLOCATED_AREA_PCT => emp_split_rec.ALLOCATED_AREA_PCT
1292 ,X_ALLOCATED_AREA => emp_split_rec.ALLOCATED_AREA
1293 ,X_UTILIZED_AREA => emp_split_rec.UTILIZED_AREA
1294 ,X_EMP_SPACE_COMMENTS => emp_split_rec.EMP_SPACE_COMMENTS
1295 ,X_ATTRIBUTE_CATEGORY => emp_split_rec.ATTRIBUTE_CATEGORY
1296 ,X_LAST_UPDATE_DATE => SYSDATE
1297 ,X_LAST_UPDATED_BY => 1
1298 ,X_LAST_UPDATE_LOGIN => 1
1299 ,X_UPDATE_CORRECT_OPTION => 'UPDATE'
1300 ,X_CHANGED_START_DATE => l_date
1301 );
1302
1303 END LOOP;
1304
1305 END LOOP;
1306
1307 pnp_debug_pkg.debug('To distribute Area_Pct and Area... (+)');
1308 FOR cust_rec IN csr_cust
1309
1310 LOOP
1311
1312 OPEN csr_location_area(p_location_id => cust_rec.location_id
1313 ,p_start_date => cust_rec.cust_assign_start_date
1314 ,p_end_date => cust_rec.cust_assign_end_date);
1315 FETCH csr_location_area INTO l_assignable_area;
1316 CLOSE csr_location_area;
1317 IF l_profile = 'Y' THEN
1318 -- Call to re-distribute area among all customers for the location
1319 FOR office_sec_rec IN office_sec_cur(cust_rec.location_id) /*Bug4565550*/
1320 LOOP
1321
1322 pn_space_assign_cust_pkg.area_pct_and_area(
1323 x_usable_area => l_assignable_area
1324 ,x_location_id => cust_rec.location_id
1325 ,x_start_date => cust_rec.cust_assign_start_date
1326 ,x_end_date => cust_rec.cust_assign_end_date
1327 );
1328
1329 END LOOP; /*Bug4565550*/
1330
1331 ELSE
1332 l_allocated_area := TRUNC((l_assignable_area * cust_rec.allocated_area_pct)/100, 2); /*4533091*/
1333 IF l_allocated_area <> cust_rec.allocated_area THEN
1334 UPDATE pn_space_assign_cust_all
1335 SET allocated_area = l_allocated_area
1336 WHERE ROWID = cust_rec.ROWID;
1337 END IF;
1338 END IF;
1339 END LOOP;
1340
1341 FOR emp_rec IN csr_emp
1342
1343 LOOP
1344
1345 OPEN csr_location_area(p_location_id => emp_rec.location_id
1346 ,p_start_date => emp_rec.emp_assign_start_date
1347 ,p_end_date => emp_rec.emp_assign_end_date);
1348 FETCH csr_location_area INTO l_assignable_area;
1349 CLOSE csr_location_area;
1350
1351 IF l_profile = 'Y' THEN
1352 -- Call to re-distribute area among all employees for the location
1353 FOR office_sec_rec IN office_sec_cur(emp_rec.location_id) /*Bug4565550*/
1354 LOOP
1355
1356 PN_SPACE_ASSIGN_CUST_PKG.area_pct_and_area(
1357 x_usable_area => l_assignable_area
1358 ,x_location_id => emp_rec.location_id
1359 ,x_start_date => emp_rec.emp_assign_start_date
1360 ,x_end_date => emp_rec.emp_assign_end_date
1361 );
1362
1363 END LOOP; /*Bug4565550*/
1364
1365 ELSE
1366 l_allocated_area := TRUNC((l_assignable_area * emp_rec.allocated_area_pct)/100, 2); /*4533091*/
1367 IF l_allocated_area <> emp_rec.allocated_area THEN
1368 UPDATE pn_space_assign_emp_all
1369 SET allocated_area = l_allocated_area
1370 WHERE ROWID = emp_rec.ROWID;
1371 END IF;
1372 END IF;
1373 END LOOP;
1374 pnp_debug_pkg.debug('Done distributing Area_Pct and Area... (-)');
1375
1376 pn_space_assign_cust_pkg.Defrag_Contig_Assign(p_location_id);
1377
1378 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.Assignment_Split (-) Loc Id: '||p_location_id);
1379
1380 END assignment_split;
1381
1382 -------------------------------------------------------------------------------
1383 -- PROCEDURE : AREA_PCT_AND_AREA
1384 -- INVOKED FROM :
1385 -- PURPOSE : Added condition to select/update only locations of type
1386 -- office/section.
1387 -- HISTORY
1388 -- 02-JUN-01 dthota o Fix for bug # 1609377
1389 -- 08-FEB-02 kkhegde o Bug#2168629 - changed the procedure definition
1390 -- replaced x_as_of_date_emp and x_as_of_date_cust
1391 -- with x_start_date and x_end_date
1392 -- 07-NOV-02 dthota o bug # 2434352 - Modified the procedure to
1393 -- add/subtract the fractional remainder after space
1394 -- redistribution to one record.
1395 -- 20-OCT-03 dthota o bug # 3234403 - Copied this procedure from PNTSPACE.pld
1396 -- 14-DEC-04 STripath o Fixed for bug# 4092157. If l_total_pct <> 100, update
1397 -- either pn_space_assign_emp or pn_space_assign_cust.
1398 -- 22-FEB-05 MMisra o Bug # 4198937. Merged IF conditions where area and
1399 -- percentage differnce was being checked.
1400 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,pn_locations
1401 -- pn_space_assign_cust with _ALL table.
1402 -------------------------------------------------------------------------------
1403 PROCEDURE area_pct_and_area(x_usable_area NUMBER,
1404 x_location_id NUMBER,
1405 x_start_date DATE,
1406 x_end_date DATE
1407 ) IS
1408
1409 l_utilized NUMBER;
1410 l_total_pct NUMBER;
1411 l_total_area NUMBER;
1412 l_diff_pct NUMBER;
1413 l_diff_area NUMBER;
1414 l_emp_updated NUMBER := 0;
1415 l_cust_updated NUMBER := 0;
1416 l_alloc_area_pct pn_space_assign_emp_all.allocated_area_pct%TYPE;
1417 l_alloc_area pn_space_assign_emp_all.allocated_area%TYPE;
1418
1419 BEGIN
1420
1421 l_utilized := PN_SPACE_ASSIGN_CUST_PKG.assignment_count(x_location_id,x_start_date,x_end_date);
1422 l_alloc_area_pct := TRUNC(100 / l_utilized, 2); /*4533091*/
1423 l_alloc_area := TRUNC(x_usable_area / l_utilized, 2); /*4533091*/
1424
1425 pnp_debug_pkg.debug('Area_Pct_And_Area (+) Loc Id: '||x_location_id
1426 ||', Area: '||x_usable_area
1427 ||', StrDt: '||TO_CHAR(x_start_date,'MM/DD/YYYY')
1428 ||', EndDt: '||TO_CHAR(x_end_date, 'MM/DD/YYYY')
1429 ||', lUtil: '||l_utilized
1430 ||', lArea: '||l_alloc_area
1431 ||', lPct: '||l_alloc_area_pct);
1432
1433 IF l_utilized <> 0 THEN
1434
1435
1436 UPDATE pn_space_assign_emp_all emp
1437 SET emp.allocated_area_pct = l_alloc_area_pct,
1438 emp.allocated_area = l_alloc_area
1439 WHERE emp.location_id = x_location_id
1440 AND (emp.emp_assign_start_date <= x_end_date AND
1441 NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1442 AND EXISTS (SELECT '1'
1443 FROM pn_locations_all loc
1444 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1445 AND loc.location_id = emp.location_id);
1446
1447 l_emp_updated := SQL%ROWCOUNT;
1448
1449
1450 UPDATE pn_space_assign_cust_all cust
1451 SET cust.allocated_area_pct = l_alloc_area_pct,
1452 cust.allocated_area = l_alloc_area
1453 WHERE cust.location_id = x_location_id
1454 AND (cust.cust_assign_start_date <= x_end_date AND
1455 NVL(cust.cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1456 AND EXISTS (SELECT '1'
1457 FROM pn_locations_all loc
1458 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1459 AND loc.location_id = cust.location_id);
1460
1461 l_cust_updated := SQL%ROWCOUNT;
1462
1463 l_total_pct := l_alloc_area_pct * l_utilized;
1464 l_total_area := l_alloc_area * l_utilized;
1465
1466 IF l_total_pct <> 100 OR l_total_area <> x_usable_area THEN
1467
1468 l_diff_pct := 100 - l_total_pct;
1469 l_diff_area := x_usable_area - l_total_area;
1470
1471
1472 IF NVL(l_emp_updated, 0) > 0 THEN
1473 UPDATE pn_space_assign_emp_all emp
1474 SET emp.allocated_area_pct = (emp.allocated_area_pct + l_diff_pct),
1475 emp.allocated_area = (emp.allocated_area + l_diff_area)
1476 WHERE emp.location_id = x_location_id
1477 AND (emp.emp_assign_start_date <= x_end_date AND
1478 NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1479 AND EXISTS (SELECT '1'
1480 FROM pn_locations_all loc
1481 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1482 AND loc.location_id = emp.location_id)
1483 AND ROWNUM < 2;
1484
1485
1486 ELSIF NVL(l_cust_updated, 0) > 0 THEN
1487 UPDATE pn_space_assign_cust_all cust
1488 SET cust.allocated_area_pct = (cust.allocated_area_pct + l_diff_pct),
1489 cust.allocated_area = (cust.allocated_area + l_diff_area)
1490 WHERE cust.location_id = x_location_id
1491 AND (cust.cust_assign_start_date <= x_end_date AND
1492 NVL(cust.cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1493 AND EXISTS (SELECT '1'
1494 FROM pn_locations_all loc
1495 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1496 AND loc.location_id = cust.location_id)
1497 AND ROWNUM < 2;
1498
1499 END IF;
1500 END IF;
1501 END IF;
1502
1503 pnp_debug_pkg.debug('Area_Pct_And_Area (-) Loc Id: '||x_location_id);
1504 END area_pct_and_area;
1505
1506 /*===========================================================================+
1507 -- NAME : assignment_count
1508 -- DESCRIPTION : This function returns the number of assignments for a given
1509 -- location within the given start date and end date. Earlier
1510 -- this code was a part of procedure area_pct_and_area, made a
1511 -- separate function so that it can be called from
1512 -- INSERT_ROW to get the count of assignments.
1513 -- SCOPE : PRIVATE
1514 -- INVOKED FROM :
1515 -- ARGUMENTS : IN : x_location_id,x_start_date,x_end_date.
1516 -- OUT: l_utilized
1517 -- REFERENCE : PN_COMMON.debug()
1518 -- RETURNS : No. of assignments for the location with in start date and
1519 -- end date.
1520 -- HISTORY :
1521 -- 16-ARP-02 MMisra o Created
1522 -- 20-OCT-03 DThota o bug 3234403 - Copied this procedure from PNTSPACE.pld
1523 -- 07-MAR-05 ftanudja o Used subquery referencing for performance. #4199297.
1524 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,
1525 -- pn_space_assign_cust, pn_locations with _ALL table.
1526 +============================================================================*/
1527
1528 FUNCTION assignment_count(x_location_id IN NUMBER,
1529 x_start_date IN DATE,
1530 x_end_date IN DATE)
1531 RETURN NUMBER IS
1532
1533 l_utilized NUMBER;
1534 l_utilized_emp NUMBER;
1535 l_utilized_cust NUMBER;
1536
1537 BEGIN
1538
1539 l_utilized_emp := 0;
1540 l_utilized_cust := 0;
1541
1542 SELECT COUNT(*)
1543 INTO l_utilized_emp
1544 FROM pn_space_assign_emp_all emp
1545 WHERE emp.location_id = x_location_id
1546 AND (emp.emp_assign_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
1547 NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1548 AND EXISTS (SELECT '1'
1549 FROM pn_locations_all loc
1550 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1551 AND loc.location_id = emp.location_id);
1552
1553 SELECT COUNT(*)
1554 INTO l_utilized_cust
1555 FROM pn_space_assign_cust_all cust
1556 WHERE cust.location_id = x_location_id
1557 AND (cust.cust_assign_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
1558 NVL(cust.cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1559 AND EXISTS (SELECT '1'
1560 FROM pn_locations_all loc
1561 WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
1562 AND loc.location_id = cust.location_id);
1563
1564 l_utilized := NVL(l_utilized_emp,0) + NVL(l_utilized_cust,0);
1565
1566 RETURN l_utilized;
1567
1568 END assignment_count;
1569
1570 /*===========================================================================+
1571 | FUNCTION
1572 | location_count
1573 |
1574 | DESCRIPTION
1575 | This function returns the number of location splits for a given location within
1576 | the given start date and end date.
1577 |
1578 | SCOPE - PRIVATE
1579 |
1580 | ARGUMENTS:
1581 | IN: x_location_id,x_start_date,x_end_date.
1582 | OUT: l_location_count
1583 |
1584 | RETURNS: No. of location splits for the location with in start date and end date.
1585 |
1586 | MODIFICATION HISTORY
1587 | 06-Nov-2003 Daniel Thota o Created Fix for bug # 3240216.
1588 | Gets a count of split locations for a date range if they exist
1589 | to be used in the event that a location has undergone
1590 | splits as result of location attribute changes before
1591 | an assignment is made for that location
1592 +===========================================================================*/
1593
1594 FUNCTION location_count(x_location_id IN NUMBER,
1595 x_start_date IN DATE,
1596 x_end_date IN DATE)
1597 RETURN NUMBER IS
1598
1599 l_location_count NUMBER := 0;
1600
1601 BEGIN
1602
1603 SELECT COUNT(*)
1604 INTO l_location_count
1605 FROM pn_locations_all
1606 WHERE location_id = x_location_id
1607 AND (active_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
1608 NVL(active_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1609 ;
1610 RETURN l_location_count;
1611
1612 END location_count;
1613
1614 -- 102403 -- date track space assignment
1615
1616
1617 -------------------------------------------------------------------------------
1618 -- PROCEDURE : DEFRAG_CONTIG_ASSIGN
1619 -- PURPOSE : This procedure removes fragments of the similar contiguous assignment
1620 -- record after assignments are split and re-distributed.
1621 -- Consider lease 1803, Tenancy 1985 for location 3045 assigned to Customer 5225
1622 -- from 1/1/00 to 12/31/06....the foll. assignment will be created
1623 --
1624 -- LOC_ID START_DT END_DATE ALLOC_PCT ALLOC_AREA LEASE_ID TENANCY_ID CUST_ID
1625 -- ------ --------- --------- --------- ---------- -------- ---------- -------
1626 -- 3045 01-JAN-00 31-DEC-06 100 750 1803 1985 5225
1627 --
1628 -- Consider lease 1804, Tenancy 1986 for location 3045 assigned to Customer 4780
1629 -- from 1/1/02 to 12/31/04....the foll. assignment will be created/redistributed
1630 --
1631 -- LOC_ID START_DT END_DATE ALLOC_PCT ALLOC_AREA LEASE_ID TENANCY_ID CUST_ID
1632 -- ------ --------- --------- --------- ---------- -------- ---------- -------
1633 -- 3045 01-JAN-00 31-DEC-01 100 750 1803 1985 5225
1634 -- 3045 01-JAN-02 31-DEC-04 50 375 1804 1986 4780
1635 -- 3045 01-JAN-02 31-DEC-04 50 375 1803 1985 5225
1636 -- 3045 01-JAN-05 31-DEC-06 100 750 1803 1985 5225
1637 --
1638 -- If tenancy is updated to start on 1/1/03 and end on 12/31/03 w/o changing
1639 -- location/customer info the foll assignment was being created/redistributed
1640 --
1641 -- LOC_ID START_DT END_DATE ALLOC_PCT ALLOC_AREA LEASE_ID TENANCY_ID CUST_ID
1642 -- ------ --------- --------- --------- ---------- -------- ---------- -------
1643 -- 3045 01-JAN-00 31-DEC-01 100 750 1803 1985 5225
1644 -- 3045 01-JAN-02 31-DEC-03 100 750 1803 1985 5225
1645 -- 3045 01-JAN-03 31-DEC-03 50 375 1804 1986 4780
1646 -- 3045 01-JAN-03 31-DEC-03 50 375 1803 1985 5225
1647 -- 3045 01-JAN-04 31-DEC-05 100 750 1803 1985 5225
1648 -- 3045 01-JAN-05 31-DEC-06 100 750 1803 1985 5225
1649 --
1650 -- This procedure now merges the similar contiguous assignments as the following
1651 --
1652 -- LOC_ID START_DT END_DATE ALLOC_PCT ALLOC_AREA LEASE_ID TENANCY_ID CUST_ID
1653 -- ------ --------- --------- --------- ---------- -------- ---------- -------
1654 -- 3045 01-JAN-00 31-DEC-03 100 750 1803 1985 5225
1655 -- 3045 01-JAN-03 31-DEC-03 50 375 1804 1986 4780
1656 -- 3045 01-JAN-03 31-DEC-03 50 375 1803 1985 5225
1657 -- 3045 01-JAN-04 31-DEC-06 100 750 1803 1985 5225
1658 -- This is done only for those records which have all other attributes of the
1659 -- record to be similar except having contiguity in the end date and start date
1660 -- as in the first and the last 2 records in the example shown above
1661 --
1662 -- IN PARAM : Location Id.
1663 -- History :
1664 -- 09-DEC-03 DThota o Created for BUG# 3308225.
1665 -- 02-JUN-04 STripathi o Changed name from clean_up to Defrag_Contig_Assign.
1666 -- Changed logic; added comparing attribute1 - 15 of
1667 -- cintiguous assignments also to check if they are similar
1668 -- assignments, update the assignment instead of calling
1669 -- Update_Row in Correct mode, and other changes.
1670 -- 30-SEP-04 STripathi o Modified for Update and Delete for Emp_Tab.
1671 -- Update i+1 row BUT DELETE i th row.
1672 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,
1673 -- pn_space_assign_cust with _ALL table.
1674 -------------------------------------------------------------------------------
1675
1676 PROCEDURE Defrag_Contig_Assign (
1677 p_location_id IN pn_locations_all.location_id%TYPE )
1678 IS
1679 -------------------------------------------------------------------------------------
1680 -- The foll. cursors are used to get customers and employees assigned to a location
1681 -- after split and redistribution ordered in such a way that records with contiguous
1682 -- dates are consecutively ordered.
1683 -------------------------------------------------------------------------------------
1684
1685 CURSOR csr_cust IS
1686 SELECT CUST_SPACE_ASSIGN_ID
1687 ,LOCATION_ID
1688 ,CUST_ACCOUNT_ID
1689 ,SITE_USE_ID
1690 ,EXPENSE_ACCOUNT_ID
1691 ,PROJECT_ID
1692 ,TASK_ID
1693 ,CUST_ASSIGN_START_DATE
1694 ,CUST_ASSIGN_END_DATE
1695 ,ALLOCATED_AREA_PCT
1696 ,ALLOCATED_AREA
1697 ,UTILIZED_AREA
1698 ,CUST_SPACE_COMMENTS
1699 ,LAST_UPDATE_DATE
1700 ,LAST_UPDATED_BY
1701 ,CREATION_DATE
1702 ,CREATED_BY
1703 ,LAST_UPDATE_LOGIN
1704 ,ATTRIBUTE_CATEGORY
1705 ,ATTRIBUTE1
1706 ,ATTRIBUTE2
1707 ,ATTRIBUTE3
1708 ,ATTRIBUTE4
1709 ,ATTRIBUTE5
1710 ,ATTRIBUTE6
1711 ,ATTRIBUTE7
1712 ,ATTRIBUTE8
1713 ,ATTRIBUTE9
1714 ,ATTRIBUTE10
1715 ,ATTRIBUTE11
1716 ,ATTRIBUTE12
1717 ,ATTRIBUTE13
1718 ,ATTRIBUTE14
1719 ,ATTRIBUTE15
1720 ,ORG_ID
1721 ,LEASE_ID
1722 ,RECOVERY_SPACE_STD_CODE
1723 ,RECOVERY_TYPE_CODE
1724 ,FIN_OBLIG_END_DATE
1725 ,TENANCY_ID
1726 FROM pn_space_assign_cust_all
1727 WHERE location_id = p_location_id
1728 ORDER BY cust_account_id,tenancy_id,lease_id,cust_assign_start_date,cust_assign_end_date
1729 ;
1730
1731 CURSOR csr_emp IS
1732 SELECT EMP_SPACE_ASSIGN_ID
1733 ,LOCATION_ID
1734 ,PERSON_ID
1735 ,PROJECT_ID
1736 ,TASK_ID
1737 ,EMP_ASSIGN_START_DATE
1738 ,EMP_ASSIGN_END_DATE
1739 ,COST_CENTER_CODE
1740 ,ALLOCATED_AREA_PCT
1741 ,ALLOCATED_AREA
1742 ,UTILIZED_AREA
1743 ,EMP_SPACE_COMMENTS
1744 ,LAST_UPDATE_DATE
1745 ,LAST_UPDATED_BY
1746 ,CREATION_DATE
1747 ,CREATED_BY
1748 ,LAST_UPDATE_LOGIN
1749 ,ATTRIBUTE_CATEGORY
1750 ,ATTRIBUTE1
1751 ,ATTRIBUTE2
1752 ,ATTRIBUTE3
1753 ,ATTRIBUTE4
1754 ,ATTRIBUTE5
1755 ,ATTRIBUTE6
1756 ,ATTRIBUTE7
1757 ,ATTRIBUTE8
1758 ,ATTRIBUTE9
1759 ,ATTRIBUTE10
1760 ,ATTRIBUTE11
1761 ,ATTRIBUTE12
1762 ,ATTRIBUTE13
1763 ,ATTRIBUTE14
1764 ,ATTRIBUTE15
1765 ,ORG_ID
1766 ,SOURCE
1767 FROM pn_space_assign_emp_all
1768 WHERE location_id = p_location_id
1769 ORDER BY person_id,emp_assign_start_date,emp_assign_end_date
1770 ;
1771
1772 --------------------------------------------------------------------------
1773 -- Define a record of PN_SPACE_CUST_ASSIGN_ALL and PN_SPACE_ASSIGN_EMP_ALL
1774 --------------------------------------------------------------------------
1775
1776 TYPE cust_rec_type IS RECORD(
1777 CUST_SPACE_ASSIGN_ID pn_space_assign_cust_all.CUST_SPACE_ASSIGN_ID%TYPE
1778 ,LOCATION_ID pn_space_assign_cust_all.LOCATION_ID%TYPE
1779 ,CUST_ACCOUNT_ID pn_space_assign_cust_all.CUST_ACCOUNT_ID%TYPE
1780 ,SITE_USE_ID pn_space_assign_cust_all.SITE_USE_ID%TYPE
1781 ,EXPENSE_ACCOUNT_ID pn_space_assign_cust_all.EXPENSE_ACCOUNT_ID%TYPE
1782 ,PROJECT_ID pn_space_assign_cust_all.PROJECT_ID%TYPE
1783 ,TASK_ID pn_space_assign_cust_all.TASK_ID%TYPE
1784 ,CUST_ASSIGN_START_DATE pn_space_assign_cust_all.CUST_ASSIGN_START_DATE%TYPE
1785 ,CUST_ASSIGN_END_DATE pn_space_assign_cust_all.CUST_ASSIGN_END_DATE%TYPE
1786 ,ALLOCATED_AREA_PCT pn_space_assign_cust_all.ALLOCATED_AREA_PCT%TYPE
1787 ,ALLOCATED_AREA pn_space_assign_cust_all.ALLOCATED_AREA%TYPE
1788 ,UTILIZED_AREA pn_space_assign_cust_all.UTILIZED_AREA%TYPE
1789 ,CUST_SPACE_COMMENTS pn_space_assign_cust_all.CUST_SPACE_COMMENTS%TYPE
1790 ,LAST_UPDATE_DATE pn_space_assign_cust_all.LAST_UPDATE_DATE%TYPE
1791 ,LAST_UPDATED_BY pn_space_assign_cust_all.LAST_UPDATED_BY%TYPE
1792 ,CREATION_DATE pn_space_assign_cust_all.CREATION_DATE%TYPE
1793 ,CREATED_BY pn_space_assign_cust_all.CREATED_BY%TYPE
1794 ,LAST_UPDATE_LOGIN pn_space_assign_cust_all.LAST_UPDATE_LOGIN%TYPE
1795 ,ATTRIBUTE_CATEGORY pn_space_assign_cust_all.ATTRIBUTE_CATEGORY%TYPE
1796 ,ATTRIBUTE1 pn_space_assign_cust_all.ATTRIBUTE1%TYPE
1797 ,ATTRIBUTE2 pn_space_assign_cust_all.ATTRIBUTE2%TYPE
1798 ,ATTRIBUTE3 pn_space_assign_cust_all.ATTRIBUTE3%TYPE
1799 ,ATTRIBUTE4 pn_space_assign_cust_all.ATTRIBUTE4%TYPE
1800 ,ATTRIBUTE5 pn_space_assign_cust_all.ATTRIBUTE5%TYPE
1801 ,ATTRIBUTE6 pn_space_assign_cust_all.ATTRIBUTE6%TYPE
1802 ,ATTRIBUTE7 pn_space_assign_cust_all.ATTRIBUTE7%TYPE
1803 ,ATTRIBUTE8 pn_space_assign_cust_all.ATTRIBUTE8%TYPE
1804 ,ATTRIBUTE9 pn_space_assign_cust_all.ATTRIBUTE9%TYPE
1805 ,ATTRIBUTE10 pn_space_assign_cust_all.ATTRIBUTE10%TYPE
1806 ,ATTRIBUTE11 pn_space_assign_cust_all.ATTRIBUTE11%TYPE
1807 ,ATTRIBUTE12 pn_space_assign_cust_all.ATTRIBUTE12%TYPE
1808 ,ATTRIBUTE13 pn_space_assign_cust_all.ATTRIBUTE13%TYPE
1809 ,ATTRIBUTE14 pn_space_assign_cust_all.ATTRIBUTE14%TYPE
1810 ,ATTRIBUTE15 pn_space_assign_cust_all.ATTRIBUTE15%TYPE
1811 ,ORG_ID pn_space_assign_cust_all.ORG_ID%TYPE
1812 ,LEASE_ID pn_space_assign_cust_all.LEASE_ID%TYPE
1813 ,RECOVERY_SPACE_STD_CODE pn_space_assign_cust_all.RECOVERY_SPACE_STD_CODE%TYPE
1814 ,RECOVERY_TYPE_CODE pn_space_assign_cust_all.RECOVERY_TYPE_CODE%TYPE
1815 ,FIN_OBLIG_END_DATE pn_space_assign_cust_all.FIN_OBLIG_END_DATE%TYPE
1816 ,TENANCY_ID pn_space_assign_cust_all.TENANCY_ID%TYPE
1817 );
1818
1819 TYPE emp_rec_type IS RECORD(
1820 EMP_SPACE_ASSIGN_ID pn_space_assign_emp_all.EMP_SPACE_ASSIGN_ID%TYPE
1821 ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822 ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823 ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824 ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825 ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826 ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827 ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828 ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829 ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830 ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831 ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832 ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833 ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834 ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835 ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836 ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837 ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838 ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839 ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840 ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841 ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842 ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843 ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844 ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845 ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846 ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847 ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848 ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849 ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850 ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851 ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852 ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853 ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1854 ,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
1855 );
1856
1857 ----------------------------------------------------------
1858 -- Define a PL/SQL table for employee and customer records
1859 ----------------------------------------------------------
1860 TYPE emp IS
1861 TABLE OF emp_rec_type
1862 INDEX BY BINARY_INTEGER;
1863
1864 TYPE cust IS
1865 TABLE OF cust_rec_type
1866 INDEX BY BINARY_INTEGER;
1867
1868 emp_tab emp;
1869 cust_tab cust;
1870 l_rec_num NUMBER;
1871 l_diff NUMBER;
1872 l_date DATE := NULL;
1873 l_err_flag VARCHAR2(1);
1874 l_err_msg VARCHAR2(1) := NULL;
1875 l_return_status VARCHAR2(100) := NULL;
1876
1877
1878 BEGIN
1879
1880 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.Defrag_Contig_Assign (+) Loc Id: '||p_location_id);
1881
1882 cust_tab.delete;
1883 l_rec_num := 0;
1884
1885 --------------------------------------
1886 -- Populate the customer PL/SQL table
1887 --------------------------------------
1888 FOR cust_rec IN csr_cust LOOP
1889 l_rec_num := NVL(cust_tab.count,0) + 1;
1890 cust_tab(l_rec_num) := cust_rec;
1891
1892 pnp_debug_pkg.debug('Cust_Tab i= '||l_rec_num
1893 ||', cust: '||cust_rec.cust_account_id
1894 ||', str: '||cust_rec.cust_assign_start_date
1895 ||', end: '||cust_rec.cust_assign_end_date
1896 ||', Id: '||cust_rec.cust_space_assign_id
1897 ||', area= '||cust_rec.allocated_area);
1898 END LOOP;
1899
1900 pnp_debug_pkg.debug('Defrag_Contig_Assign Cust_Tab... l_rec_num: '||l_rec_num);
1901
1902 IF NVL(l_rec_num,0) > 1 THEN
1903 FOR i in 1..CUST_TAB.count-1 LOOP
1904
1905 pnp_debug_pkg.debug('Defrag_Contig_Assign Cust_Tab... l_rec_num>1 Start_LOOP i= '||i
1906 ||', cust_asgn_id= '||NVL(cust_tab(i).cust_space_assign_id,0));
1907
1908 IF cust_tab(i+1).location_id = cust_tab(i).location_id
1909 AND cust_tab(i+1).cust_account_id = cust_tab(i).cust_account_id
1910 AND NVL(cust_tab(i+1).site_use_id,0) = NVL(cust_tab(i).site_use_id,0)
1911 AND NVL(cust_tab(i+1).expense_account_id,0) = NVL(cust_tab(i).expense_account_id,0)
1912 AND NVL(cust_tab(i+1).project_id,0) = NVL(cust_tab(i).project_id,0)
1913 AND NVL(cust_tab(i+1).task_id,0) = NVL(cust_tab(i).task_id,0)
1914 AND NVL(cust_tab(i+1).utilized_area,0) = NVL(cust_tab(i).utilized_area,0)
1915 AND NVL(cust_tab(i+1).allocated_area,0) = NVL(cust_tab(i).allocated_area,0)
1916 AND NVL(cust_tab(i+1).allocated_area_pct,0) = NVL(cust_tab(i).allocated_area_pct,0)
1917 AND NVL(cust_tab(i+1).cust_space_comments,'X') = NVL(cust_tab(i).cust_space_comments,'X')
1918 AND NVL(cust_tab(i+1).lease_id,0) = NVL(cust_tab(i).lease_id,0)
1919 AND NVL(cust_tab(i+1).tenancy_id,0) = NVL(cust_tab(i).tenancy_id,0)
1920 AND NVL(cust_tab(i+1).recovery_space_std_code,'X') = NVL(cust_tab(i).recovery_space_std_code,'X')
1921 AND NVL(cust_tab(i+1).recovery_type_code,'X') = NVL(cust_tab(i).recovery_type_code,'X')
1922 AND NVL(cust_tab(i+1).attribute_category,'X') = NVL(cust_tab(i).attribute_category,'X')
1923 AND NVL(cust_tab(i+1).attribute1,'X') = NVL(cust_tab(i).attribute1,'X')
1924 AND NVL(cust_tab(i+1).attribute2,'X') = NVL(cust_tab(i).attribute2,'X')
1925 AND NVL(cust_tab(i+1).attribute3,'X') = NVL(cust_tab(i).attribute3,'X')
1926 AND NVL(cust_tab(i+1).attribute4,'X') = NVL(cust_tab(i).attribute4,'X')
1927 AND NVL(cust_tab(i+1).attribute5,'X') = NVL(cust_tab(i).attribute5,'X')
1928 AND NVL(cust_tab(i+1).attribute6,'X') = NVL(cust_tab(i).attribute6,'X')
1929 AND NVL(cust_tab(i+1).attribute7,'X') = NVL(cust_tab(i).attribute7,'X')
1930 AND NVL(cust_tab(i+1).attribute8,'X') = NVL(cust_tab(i).attribute8,'X')
1931 AND NVL(cust_tab(i+1).attribute9,'X') = NVL(cust_tab(i).attribute9,'X')
1932 AND NVL(cust_tab(i+1).attribute10,'X') = NVL(cust_tab(i).attribute10,'X')
1933 AND NVL(cust_tab(i+1).attribute11,'X') = NVL(cust_tab(i).attribute11,'X')
1934 AND NVL(cust_tab(i+1).attribute12,'X') = NVL(cust_tab(i).attribute12,'X')
1935 AND NVL(cust_tab(i+1).attribute13,'X') = NVL(cust_tab(i).attribute13,'X')
1936 AND NVL(cust_tab(i+1).attribute14,'X') = NVL(cust_tab(i).attribute14,'X')
1937 AND NVL(cust_tab(i+1).attribute15,'X') = NVL(cust_tab(i).attribute15,'X')
1938 THEN
1939
1940 l_diff := cust_tab(i+1).cust_assign_start_date -
1941 cust_tab(i).cust_assign_end_date;
1942
1943 pnp_debug_pkg.debug('Defrag_Contig_Assign Cust_Tab... l_diff: '||l_diff);
1944
1945 IF l_diff = 1 THEN
1946
1947 ---------------------------------------------------------------------------
1948 -- If stepping thru cust PL/SQL table records finds contigous records with
1949 -- consecutive dates update the (i+1) record with the start date of the ith
1950 -- record and .......
1951 ---------------------------------------------------------------------------
1952 UPDATE pn_space_assign_cust_all
1953 SET cust_assign_start_date = cust_tab(i).cust_assign_start_date
1954 ,last_update_date = SYSDATE
1955 ,last_updated_by = NVL(FND_GLOBAL.USER_ID,'-1')
1956 ,last_update_login = NVL(FND_GLOBAL.LOGIN_ID,'-1')
1957 WHERE cust_space_assign_id = cust_tab(i+1).cust_space_assign_id;
1958
1959 ---------------------------------------------------------------------------
1960 -- ....... update the (i+1) record with the start date of the ith record
1961 -- in the PL/SQL table as well and delete the ith record from the DB
1962 ---------------------------------------------------------------------------
1963 cust_tab(i+1).cust_assign_start_date := cust_tab(i).cust_assign_start_date;
1964 pn_space_assign_cust_pkg.delete_row(cust_tab(i).cust_space_assign_id);
1965
1966 END IF;
1967 END IF;
1968 pnp_debug_pkg.debug('Defrag_Contig_Assign Cust_Tab... l_rec_num>1 End_LOOP i= '||i);
1969 END LOOP;
1970 END IF;
1971
1972 emp_tab.delete;
1973 l_rec_num := 0;
1974
1975 --------------------------------------
1976 -- Populate the customer PL/SQL table
1977 --------------------------------------
1978
1979 FOR emp_rec IN csr_emp LOOP
1980 l_rec_num := NVL(emp_tab.count,0) + 1;
1981 emp_tab(l_rec_num) := emp_rec;
1982
1983 pnp_debug_pkg.debug('Emp_Tab i= '||l_rec_num
1984 ||', Emp: '||emp_rec.person_id
1985 ||', str: '||emp_rec.emp_assign_start_date
1986 ||', end: '||emp_rec.emp_assign_end_date
1987 ||', Id: '||emp_rec.emp_space_assign_id
1988 ||', area= '||emp_rec.allocated_area);
1989 END LOOP;
1990
1991 pnp_debug_pkg.debug('Defrag_Contig_Assign Emp_Tab... l_rec_num: '||l_rec_num);
1992
1993 IF NVL(l_rec_num,0) > 1 THEN
1994 FOR i in 1..EMP_TAB.count-1 LOOP
1995
1996 pnp_debug_pkg.debug('Defrag_Contig_Assign Emp_Tab... l_rec_num>1 Start_LOOP i= '||i);
1997
1998 IF emp_tab(i+1).location_id = emp_tab(i).location_id
1999 AND emp_tab(i+1).person_id = emp_tab(i).person_id
2000 AND NVL(emp_tab(i+1).cost_center_code,0) = NVL(emp_tab(i).cost_center_code,0)
2001 AND NVL(emp_tab(i+1).project_id,0) = NVL(emp_tab(i).project_id,0)
2002 AND NVL(emp_tab(i+1).task_id,0) = NVL(emp_tab(i).task_id,0)
2003 AND NVL(emp_tab(i+1).utilized_area,0) = NVL(emp_tab(i).utilized_area,0)
2004 AND NVL(emp_tab(i+1).allocated_area,0) = NVL(emp_tab(i).allocated_area,0)
2005 AND NVL(emp_tab(i+1).allocated_area_pct,0) = NVL(emp_tab(i).allocated_area_pct,0)
2006 AND NVL(emp_tab(i+1).emp_space_comments,'X') = NVL(emp_tab(i).emp_space_comments,'X')
2007 AND NVL(emp_tab(i+1).attribute_category,'X') = NVL(emp_tab(i).attribute_category,'X')
2008 AND NVL(emp_tab(i+1).attribute1,'X') = NVL(emp_tab(i).attribute1,'X')
2009 AND NVL(emp_tab(i+1).attribute2,'X') = NVL(emp_tab(i).attribute2,'X')
2010 AND NVL(emp_tab(i+1).attribute3,'X') = NVL(emp_tab(i).attribute3,'X')
2011 AND NVL(emp_tab(i+1).attribute4,'X') = NVL(emp_tab(i).attribute4,'X')
2012 AND NVL(emp_tab(i+1).attribute5,'X') = NVL(emp_tab(i).attribute5,'X')
2013 AND NVL(emp_tab(i+1).attribute6,'X') = NVL(emp_tab(i).attribute6,'X')
2014 AND NVL(emp_tab(i+1).attribute7,'X') = NVL(emp_tab(i).attribute7,'X')
2015 AND NVL(emp_tab(i+1).attribute8,'X') = NVL(emp_tab(i).attribute8,'X')
2016 AND NVL(emp_tab(i+1).attribute9,'X') = NVL(emp_tab(i).attribute9,'X')
2017 AND NVL(emp_tab(i+1).attribute10,'X') = NVL(emp_tab(i).attribute10,'X')
2018 AND NVL(emp_tab(i+1).attribute11,'X') = NVL(emp_tab(i).attribute11,'X')
2019 AND NVL(emp_tab(i+1).attribute12,'X') = NVL(emp_tab(i).attribute12,'X')
2020 AND NVL(emp_tab(i+1).attribute13,'X') = NVL(emp_tab(i).attribute13,'X')
2021 AND NVL(emp_tab(i+1).attribute14,'X') = NVL(emp_tab(i).attribute14,'X')
2022 AND NVL(emp_tab(i+1).attribute15,'X') = NVL(emp_tab(i).attribute15,'X')
2023 THEN
2024
2025 l_diff := emp_tab(i+1).emp_assign_start_date -
2026 emp_tab(i).emp_assign_end_date;
2027
2028 pnp_debug_pkg.debug('Defrag_Contig_Assign Emp_Tab... l_diff: '||l_diff);
2029
2030 IF l_diff = 1 THEN
2031 ---------------------------------------------------------------------------
2032 -- If stepping thru emp PL/SQL table records finds contigous records with
2033 -- consecutive dates update the (i+1) record with the start date of the ith
2034 -- record and .......
2035 ---------------------------------------------------------------------------
2036 UPDATE pn_space_assign_emp_all
2037 SET emp_assign_start_date = emp_tab(i).emp_assign_start_date
2038 ,last_update_date = SYSDATE
2039 ,last_updated_by = NVL(FND_GLOBAL.USER_ID,'-1')
2040 ,last_update_login = NVL(FND_GLOBAL.LOGIN_ID,'-1')
2041 WHERE emp_space_assign_id = emp_tab(i+1).emp_space_assign_id;
2042
2043 ---------------------------------------------------------------------------
2044 -- ....... update the (i+1) record with the start date of the ith record
2045 -- in the PL/SQL table as well and delete the ith record from the DB
2046 ---------------------------------------------------------------------------
2047 emp_tab(i+1).emp_assign_start_date := emp_tab(i).emp_assign_start_date;
2048 pn_space_assign_emp_pkg.delete_row(emp_tab(i).emp_space_assign_id);
2049
2050 END IF;
2051 END IF;
2052 pnp_debug_pkg.debug('Defrag_Contig_Assign Emp_Tab... l_rec_num>1 End_LOOP i= '||i);
2053 END LOOP;
2054 END IF;
2055
2056 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.Defrag_Contig_Assign (-) Loc Id: '||p_location_id);
2057
2058 END Defrag_Contig_Assign;
2059
2060 -------------------------------------------------------------------------------
2061 -- PROCEDURE : merge_tables
2062 -- DESCRIPTION : Merges base table with a new table. Extract only new location
2063 -- not already in the base table
2064 -- NOTE : counting starts from 1, not 0 !
2065 -- HISTORY :
2066 -- 04-APR-05 ftanudja o Created. #4270051.
2067 -------------------------------------------------------------------------------
2068
2069 PROCEDURE merge_tables(
2070 p_base_table IN OUT NOCOPY loc_id_tbl,
2071 p_new_table IN loc_id_tbl
2072 ) IS
2073 l_exists BOOLEAN;
2074
2075 BEGIN
2076
2077 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.merge_tables (+)');
2078
2079 -- do simple bubble search to identify new tables.
2080 FOR i IN 1..p_new_table.COUNT LOOP
2081
2082 l_exists := FALSE;
2083
2084 FOR j IN 1..p_base_table.COUNT LOOP
2085 IF p_base_table(j) = p_new_table(i) THEN l_exists := TRUE; exit; END IF;
2086 END LOOP;
2087
2088 IF NOT l_exists THEN
2089 p_base_table(p_base_table.COUNT + 1) := p_new_table(i);
2090 END IF;
2091
2092 END LOOP;
2093
2094 pnp_debug_pkg.debug('PN_SPACE_ASSIGN_CUST_PKG.merge_tables (-)');
2095
2096 END;
2097
2098 -------------------------------------------------------------------------------
2099 -- NAME : DELETE_OTHER_ASSIGNMENTS_EMP
2100 -- DESCRIPTION :
2101 -- INVOKED FROM :
2102 -- ARGUMENTS : IN : x_person_id, x_cost_center_code, x_emp_assign_start_date
2103 -- x_emp_space_assign_id, x_loc_id_tbl
2104 -- REFERENCE : PN_COMMON.debug()
2105 -- HISTORY :
2106 -- 06-FEB-01 dthota o bug # 1609377 - Added code to update PN_SPACE_ASSIGN_EMP.
2107 -- emp_assign_end_date of all employee assignments whose
2108 -- start date is less than the start date of the assignmemnt
2109 -- record being entered with (emp_assign_start_date -1) and
2110 -- all employee assignments whose start date is equal to the
2111 -- start date of the assignmemnt record being entered with
2112 -- (emp_assign_start_date)
2113 -- 09-MAR-04 ftanudj o added parameter x_emp_space_assign_id.
2114 -- 06-APR-05 ftanudja o Moved from PNTSPACE library. #4270051.
2115 -- o Added parameter x_loc_id_tbl, x_cost_center_code.
2116 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp
2117 -- with _ALL table.
2118 -- 30-JUN-05 MMisra o Removed UPDATE for cost center assignments.
2119 -- o Removed input param. x_cost_center_code.
2120 -------------------------------------------------------------------------------
2121 PROCEDURE delete_other_assignments_emp(
2122 x_person_id IN pn_space_assign_emp.person_id%TYPE,
2123 x_emp_assign_start_date IN pn_space_assign_emp.emp_assign_start_date%TYPE,
2124 x_emp_space_assign_id IN pn_space_assign_emp.emp_space_assign_id%TYPE,
2125 x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
2126 ) IS
2127 -- one set of tables for cost center, the other for person
2128
2129 l_loc_tbl_past_cc loc_id_tbl;
2130 l_loc_tbl_conc_cc loc_id_tbl;
2131 l_loc_tbl_past_ps loc_id_tbl;
2132 l_loc_tbl_conc_ps loc_id_tbl;
2133
2134 l_result_tbl loc_id_tbl;
2135
2136 BEGIN
2137
2138 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_emp (+)');
2139
2140 IF x_emp_space_assign_id IS NULL THEN
2141
2142 UPDATE pn_space_assign_emp_all
2143 SET emp_assign_end_date = (TRUNC(x_emp_assign_start_date) - 1)
2144 WHERE person_id = x_person_id
2145 AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
2146 AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
2147 RETURNING location_id BULK COLLECT INTO l_loc_tbl_past_ps;
2148
2149 UPDATE pn_space_assign_emp_all
2150 SET emp_assign_end_date = TRUNC(x_emp_assign_start_date)
2151 WHERE person_id = x_person_id
2152 AND TRUNC(emp_assign_start_date) = TRUNC(x_emp_assign_start_date)
2153 RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc_ps;
2154
2155 ELSE
2156
2157 UPDATE pn_space_assign_emp_all
2158 SET emp_assign_end_date = (TRUNC(x_emp_assign_start_date) - 1)
2159 WHERE person_id = x_person_id
2160 AND emp_space_assign_id <> x_emp_space_assign_id
2161 AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
2162 AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
2163 RETURNING location_id BULK COLLECT INTO l_loc_tbl_past_ps;
2164
2165 UPDATE pn_space_assign_emp_all
2166 SET emp_assign_end_date = TRUNC(x_emp_assign_start_date)
2167 WHERE person_id = x_person_id
2168 AND emp_space_assign_id <> x_emp_space_assign_id
2169 AND TRUNC(emp_assign_start_date) = TRUNC(x_emp_assign_start_date)
2170 RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc_ps;
2171
2172 END IF;
2173
2174 merge_tables(p_base_table => l_result_tbl, p_new_table => l_loc_tbl_past_ps);
2175 merge_tables(p_base_table => l_result_tbl, p_new_table => l_loc_tbl_conc_ps);
2176 merge_tables(p_base_table => l_result_tbl, p_new_table => l_loc_tbl_past_cc);
2177 merge_tables(p_base_table => l_result_tbl, p_new_table => l_loc_tbl_conc_cc);
2178
2179 x_loc_id_tbl := l_result_tbl;
2180
2181 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_emp (-)');
2182
2183 END delete_other_assignments_emp;
2184
2185
2186 -------------------------------------------------------------------------------
2187 -- NAME : DELETE_OTHER_ASSIGNMENTS_CUST
2188 -- DESCRIPTION :
2189 -- INVOKED FROM :
2190 -- ARGUMENTS : IN : x_cust_account_id, x_cust_assign_start_date,
2191 -- x_cust_space_assign_id, x_loc_id_tbl
2192 -- REFERENCE : PN_COMMON.debug()
2193 -- HISTORY :
2194 -- 06-FEB-01 dthota o bug # 1609377 - Added code to update PN_SPACE_ASSIGN_CUST.
2195 -- cust_assign_end_date of all customer assignments whose
2196 -- start date is less than the start date of the assignmemnt
2197 -- record being entered with (cust_assign_start_date -1) and
2198 -- all customer assignments whose start date is equal to the
2199 -- start date of the assignmemnt record being entered with
2200 -- (cust_assign_start_date)
2201 -- 09-MAR-04 ftanudj o added parameter x_cust_space_assign_id.
2202 -- 06-APR-05 ftanudja o Moved from PNTSPACE library. #4270051.
2203 -- o Added parameter x_loc_id_tbl
2204 -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_cust
2205 -- with _ALL table.
2206 -------------------------------------------------------------------------------
2207
2208 PROCEDURE delete_other_assignments_cust(
2209 x_cust_account_id IN pn_space_assign_cust.cust_account_id%TYPE,
2210 x_cust_assign_start_date IN pn_space_assign_cust.cust_assign_start_date%TYPE,
2211 x_cust_space_assign_id IN pn_space_assign_cust.cust_space_assign_id%TYPE,
2212 x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
2213 ) IS
2214
2215 l_loc_tbl_past loc_id_tbl;
2216 l_loc_tbl_conc loc_id_tbl;
2217
2218 l_result_tbl loc_id_tbl;
2219
2220 BEGIN
2221
2222 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_cust (+)');
2223
2224 IF x_cust_space_assign_id IS NULL THEN
2225
2226 UPDATE pn_space_assign_cust_all
2227 SET cust_assign_end_date = (TRUNC(x_cust_assign_start_date) - 1)
2228 WHERE cust_account_id = x_cust_account_id
2229 AND cust_assign_start_date < TRUNC(x_cust_assign_start_date)
2230 AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_cust_assign_start_date)
2231 RETURNING location_id BULK COLLECT INTO l_loc_tbl_past;
2232
2233 UPDATE pn_space_assign_cust_all
2234 SET cust_assign_end_date = TRUNC(x_cust_assign_start_date)
2235 WHERE cust_account_id = x_cust_account_id
2236 AND cust_assign_start_date = TRUNC(x_cust_assign_start_date)
2237 RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc;
2238
2239 ELSE
2240
2241 UPDATE pn_space_assign_cust_all
2242 SET cust_assign_end_date = (TRUNC(x_cust_assign_start_date) - 1)
2243 WHERE cust_account_id = x_cust_account_id
2244 AND cust_space_assign_id <> x_cust_space_assign_id
2245 AND cust_assign_start_date < TRUNC(x_cust_assign_start_date)
2246 AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_cust_assign_start_date)
2247 RETURNING location_id BULK COLLECT INTO l_loc_tbl_past;
2248
2249 UPDATE pn_space_assign_cust_all
2250 SET cust_assign_end_date = TRUNC(x_cust_assign_start_date)
2251 WHERE cust_account_id = x_cust_account_id
2252 AND cust_space_assign_id <> x_cust_space_assign_id
2253 AND cust_assign_start_date = TRUNC(x_cust_assign_start_date)
2254 RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc;
2255
2256 END IF;
2257
2258 merge_tables(p_base_table => l_result_tbl, p_new_table => l_loc_tbl_past);
2259 merge_tables(p_base_table => l_result_tbl, p_new_table => l_loc_tbl_conc);
2260
2261 x_loc_id_tbl := l_result_tbl;
2262
2263 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_cust (+)');
2264
2265 END delete_other_assignments_cust;
2266
2267
2268 END PN_SPACE_ASSIGN_CUST_PKG;
[Home] [Help]