[Home] [Help]
PACKAGE BODY: APPS.PN_SPACE_ALLOCATIONS_PKG
Source
1 package body PN_SPACE_ALLOCATIONS_PKG as
2 -- $Header: PNTSPALB.pls 120.1 2005/07/26 06:52:58 appldev ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
10 -- table.
11 -------------------------------------------------------------------------------
12 procedure INSERT_ROW (
13 X_ROWID in out NOCOPY VARCHAR2,
14 X_SPACE_ALLOCATION_ID in out NOCOPY NUMBER,
15 X_LOCATION_ID in NUMBER,
16 X_EMPLOYEE_ID in NUMBER,
17 X_COST_CENTER_CODE in VARCHAR2,
18 X_ALLOCATED_AREA_PCT in NUMBER,
19 X_ALLOCATED_AREA in NUMBER,
20 X_ATTRIBUTE_CATEGORY in VARCHAR2,
21 X_ATTRIBUTE1 in VARCHAR2,
22 X_ATTRIBUTE2 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE4 in VARCHAR2,
25 X_ATTRIBUTE5 in VARCHAR2,
26 X_ATTRIBUTE6 in VARCHAR2,
27 X_ATTRIBUTE7 in VARCHAR2,
28 X_ATTRIBUTE8 in VARCHAR2,
29 X_ATTRIBUTE9 in VARCHAR2,
30 X_ATTRIBUTE10 in VARCHAR2,
31 X_ATTRIBUTE11 in VARCHAR2,
32 X_ATTRIBUTE12 in VARCHAR2,
33 X_ATTRIBUTE13 in VARCHAR2,
34 X_ATTRIBUTE14 in VARCHAR2,
35 X_ATTRIBUTE15 in VARCHAR2,
36 X_CREATION_DATE in DATE,
37 X_CREATED_BY in NUMBER,
38 X_LAST_UPDATE_DATE in DATE,
39 X_LAST_UPDATED_BY in NUMBER,
40 X_LAST_UPDATE_LOGIN in NUMBER
41 )
42 IS
43 CURSOR C IS
44 SELECT ROWID
45 FROM PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
46 WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID ;
47
48 BEGIN
49 SELECT PN_SPACE_ALLOCATIONS_S.nextval
50 INTO X_SPACE_ALLOCATION_ID
51 FROM DUAL;
52
53 INSERT INTO PN_SPACE_ALLOCATIONS_ALL
54 ( --sdm_MOAC
55 SPACE_ALLOCATION_ID,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_LOGIN,
61 LOCATION_ID,
62 EMPLOYEE_ID,
63 COST_CENTER_CODE,
64 ALLOCATED_AREA_PCT,
65 ALLOCATED_AREA,
66 ATTRIBUTE_CATEGORY,
67 ATTRIBUTE1,
68 ATTRIBUTE2,
69 ATTRIBUTE3,
70 ATTRIBUTE4,
71 ATTRIBUTE5,
72 ATTRIBUTE6,
73 ATTRIBUTE7,
74 ATTRIBUTE8,
75 ATTRIBUTE9,
76 ATTRIBUTE10,
77 ATTRIBUTE11,
78 ATTRIBUTE12,
79 ATTRIBUTE13,
80 ATTRIBUTE14,
81 ATTRIBUTE15
82 )
83 VALUES
84 (
85 X_SPACE_ALLOCATION_ID,
86 X_LAST_UPDATE_DATE,
87 X_LAST_UPDATED_BY,
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_LAST_UPDATE_LOGIN,
91 X_LOCATION_ID,
92 X_EMPLOYEE_ID,
93 X_COST_CENTER_CODE,
94 X_ALLOCATED_AREA_PCT,
95 X_ALLOCATED_AREA,
96 X_ATTRIBUTE_CATEGORY,
97 X_ATTRIBUTE1,
98 X_ATTRIBUTE2,
99 X_ATTRIBUTE3,
100 X_ATTRIBUTE4,
101 X_ATTRIBUTE5,
102 X_ATTRIBUTE6,
103 X_ATTRIBUTE7,
104 X_ATTRIBUTE8,
105 X_ATTRIBUTE9,
106 X_ATTRIBUTE10,
107 X_ATTRIBUTE11,
108 X_ATTRIBUTE12,
109 X_ATTRIBUTE13,
110 X_ATTRIBUTE14,
111 X_ATTRIBUTE15
112 );
113
114 OPEN c;
115 FETCH c INTO X_ROWID;
116 -- dbms_output.put_line('row line number:'||x_rowid);
117 IF (c%notfound) THEN
118 CLOSE c;
119 RAISE NO_DATA_FOUND;
120 END IF;
121 CLOSE c;
122
123 END INSERT_ROW;
124
125 -------------------------------------------------------------------------------
126 -- PROCDURE : LOCK_ROW
127 -- INVOKED FROM : LOCK_ROW procedure
128 -- PURPOSE : locks the row
129 -- HISTORY :
130 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
131 -- table.
132 -------------------------------------------------------------------------------
133 procedure LOCK_ROW
134 (
135 X_SPACE_ALLOCATION_ID in NUMBER,
136 X_LOCATION_ID in NUMBER,
137 X_EMPLOYEE_ID in NUMBER,
138 X_COST_CENTER_CODE in VARCHAR2,
139 X_ALLOCATED_AREA_PCT in NUMBER,
140 X_ALLOCATED_AREA in NUMBER,
141 X_ATTRIBUTE_CATEGORY in VARCHAR2,
142 X_ATTRIBUTE1 in VARCHAR2,
143 X_ATTRIBUTE2 in VARCHAR2,
144 X_ATTRIBUTE3 in VARCHAR2,
145 X_ATTRIBUTE4 in VARCHAR2,
146 X_ATTRIBUTE5 in VARCHAR2,
147 X_ATTRIBUTE6 in VARCHAR2,
148 X_ATTRIBUTE7 in VARCHAR2,
149 X_ATTRIBUTE8 in VARCHAR2,
150 X_ATTRIBUTE9 in VARCHAR2,
151 X_ATTRIBUTE10 in VARCHAR2,
152 X_ATTRIBUTE11 in VARCHAR2,
153 X_ATTRIBUTE12 in VARCHAR2,
154 X_ATTRIBUTE13 in VARCHAR2,
155 X_ATTRIBUTE14 in VARCHAR2,
156 X_ATTRIBUTE15 in VARCHAR2
157 )
158 IS
159 CURSOR c1 IS SELECT
160 LOCATION_ID,
161 EMPLOYEE_ID,
162 COST_CENTER_CODE,
163 ALLOCATED_AREA_PCT,
164 ALLOCATED_AREA,
165 ATTRIBUTE_CATEGORY,
166 ATTRIBUTE1,
167 ATTRIBUTE2,
168 ATTRIBUTE3,
169 ATTRIBUTE4,
170 ATTRIBUTE5,
171 ATTRIBUTE6,
172 ATTRIBUTE7,
173 ATTRIBUTE8,
174 ATTRIBUTE9,
175 ATTRIBUTE10,
176 ATTRIBUTE11,
177 ATTRIBUTE12,
178 ATTRIBUTE13,
179 ATTRIBUTE14,
180 ATTRIBUTE15,
181 SPACE_ALLOCATION_ID
182 FROM PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
183 WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID
184 FOR UPDATE OF SPACE_ALLOCATION_ID nowait;
185 tlinfo c1%rowtype;
186
187 BEGIN
188 OPEN c1;
189 FETCH c1 into tlinfo;
190 IF (c1%notfound) THEN
191 CLOSE c1;
192 RETURN;
193 END IF;
194 CLOSE c1;
195
196 IF ((tlinfo.SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID)
197 AND (tlinfo.LOCATION_ID = X_LOCATION_ID)
198 AND ((tlinfo.EMPLOYEE_ID = X_EMPLOYEE_ID)
199 OR ((tlinfo.EMPLOYEE_ID is null) AND (X_EMPLOYEE_ID is null)))
200 AND ((tlinfo.COST_CENTER_CODE = X_COST_CENTER_CODE)
201 OR ((tlinfo.COST_CENTER_CODE is null) AND (X_COST_CENTER_CODE is null)))
202 AND ((tlinfo.ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT)
203 OR ((tlinfo.ALLOCATED_AREA_PCT is null) AND (X_ALLOCATED_AREA_PCT is null)))
204 AND ((tlinfo.ALLOCATED_AREA = X_ALLOCATED_AREA)
205 OR ((tlinfo.ALLOCATED_AREA is null) AND (X_ALLOCATED_AREA is null)))
206 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
207 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
208 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
209 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
210 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
211 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
212 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
213 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
214 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
215 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
216 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
217 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
218 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
219 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
220 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
221 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
222 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
223 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
224 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
225 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
226 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
227 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
228 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
229 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
230 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
231 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
232 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
233 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
234 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
235 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
236 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
237 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
238 ) THEN
239 NULL;
240 ELSE
241 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
242 app_exception.raise_exception;
243 END IF;
244 RETURN;
245 END LOCK_ROW;
246
247 -------------------------------------------------------------------------------
248 -- PROCDURE : UPDATE_ROW
249 -- INVOKED FROM : UPDATE_ROW procedure
250 -- PURPOSE : updates the row
251 -- HISTORY :
252 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
253 -- table.
254 -------------------------------------------------------------------------------
255 procedure UPDATE_ROW (
256 X_SPACE_ALLOCATION_ID IN NUMBER,
257 X_LOCATION_ID IN NUMBER,
258 X_EMPLOYEE_ID IN NUMBER,
259 X_COST_CENTER_CODE IN VARCHAR2,
260 X_ALLOCATED_AREA_PCT IN NUMBER,
261 X_ALLOCATED_AREA IN NUMBER,
262 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
263 X_ATTRIBUTE1 IN VARCHAR2,
264 X_ATTRIBUTE2 IN VARCHAR2,
265 X_ATTRIBUTE3 IN VARCHAR2,
266 X_ATTRIBUTE4 IN VARCHAR2,
267 X_ATTRIBUTE5 IN VARCHAR2,
268 X_ATTRIBUTE6 IN VARCHAR2,
269 X_ATTRIBUTE7 IN VARCHAR2,
270 X_ATTRIBUTE8 IN VARCHAR2,
271 X_ATTRIBUTE9 IN VARCHAR2,
272 X_ATTRIBUTE10 IN VARCHAR2,
273 X_ATTRIBUTE11 IN VARCHAR2,
274 X_ATTRIBUTE12 IN VARCHAR2,
275 X_ATTRIBUTE13 IN VARCHAR2,
276 X_ATTRIBUTE14 IN VARCHAR2,
277 X_ATTRIBUTE15 IN VARCHAR2,
278 X_LAST_UPDATE_DATE IN DATE,
279 X_LAST_UPDATED_BY IN NUMBER,
280 X_LAST_UPDATE_LOGIN IN NUMBER
281 ) IS
282 BEGIN
283 UPDATE PN_SPACE_ALLOCATIONS_ALL SET --sdm_MOAC
284 LOCATION_ID = X_LOCATION_ID,
285 EMPLOYEE_ID = X_EMPLOYEE_ID,
286 COST_CENTER_CODE = X_COST_CENTER_CODE,
287 ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT,
288 ALLOCATED_AREA = X_ALLOCATED_AREA,
289 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
290 ATTRIBUTE1 = X_ATTRIBUTE1,
291 ATTRIBUTE2 = X_ATTRIBUTE2,
292 ATTRIBUTE3 = X_ATTRIBUTE3,
293 ATTRIBUTE4 = X_ATTRIBUTE4,
294 ATTRIBUTE5 = X_ATTRIBUTE5,
295 ATTRIBUTE6 = X_ATTRIBUTE6,
296 ATTRIBUTE7 = X_ATTRIBUTE7,
297 ATTRIBUTE8 = X_ATTRIBUTE8,
298 ATTRIBUTE9 = X_ATTRIBUTE9,
299 ATTRIBUTE10 = X_ATTRIBUTE10,
300 ATTRIBUTE11 = X_ATTRIBUTE11,
301 ATTRIBUTE12 = X_ATTRIBUTE12,
302 ATTRIBUTE13 = X_ATTRIBUTE13,
303 ATTRIBUTE14 = X_ATTRIBUTE14,
304 ATTRIBUTE15 = X_ATTRIBUTE15,
305 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
306 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
307 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
308 WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID;
309
310 IF (sql%notfound) THEN
311 raise no_data_found;
312 END IF;
313
314 END UPDATE_ROW;
315
316 -------------------------------------------------------------------------------
317 -- PROCDURE : DELETE_ROW
318 -- INVOKED FROM : DELETE_ROW procedure
319 -- PURPOSE : deletes the row
320 -- HISTORY :
321 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
322 -- table.
323 -------------------------------------------------------------------------------
324 procedure DELETE_ROW
325 (
326 X_SPACE_ALLOCATION_ID in NUMBER
327 )
328 IS
329 BEGIN
330 DELETE FROM PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
331 WHERE SPACE_ALLOCATION_ID = X_SPACE_ALLOCATION_ID;
332
333 IF (sql%notfound) THEN
334 RAISE NO_DATA_FOUND;
335 END IF;
336
337 END DELETE_ROW;
338
339
340 /*-------------------------------------------------------------------------------
341 -- ASSIGNED Function --
342 -------------------------------------------------------------------------------*/
343 FUNCTION ASSIGNED ( X_EMPLOYEE_ID in NUMBER,
344 X_ORG_ID in NUMBER) --sdm_MOAC
345 RETURN Boolean IS
346 l_dummy VARCHAR2(1);
347 BEGIN
348
349 SELECT 'x'
350 INTO l_dummy
351 FROM dual
352 WHERE EXISTS
353 ( SELECT '1'
354 FROM PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
355 WHERE EMPLOYEE_ID = X_EMPLOYEE_ID
356 AND ORG_ID = X_ORG_ID); --sdm_MOAC
357
358 RETURN TRUE;
359
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 RETURN FALSE;
363
364 WHEN TOO_MANY_ROWS THEN
365 RETURN TRUE;
366
367 END ASSIGNED;
368
369
370 /*-------------------------------------------------------------------------------
371 -- DELETE_OTHER_ASSIGNMENTS Procedure --
372 -------------------------------------------------------------------------------*/
373 PROCEDURE DELETE_OTHER_ASSIGNMENTS (
374 X_EMPLOYEE_ID in NUMBER,
375 X_ORG_ID in NUMBER --sdm_MOAC
376 ) IS
377
378 BEGIN
379
380 DELETE FROM PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
381 WHERE EMPLOYEE_ID = X_EMPLOYEE_ID
382 AND ORG_ID = X_ORG_ID; --sdm_MOAC
383
384 IF (sql%notfound) THEN
385 RAISE NO_DATA_FOUND;
386 END IF;
387
388 END DELETE_OTHER_ASSIGNMENTS;
389
390
391 -------------------------------------------------------------------------------
392 -- PROCDURE : VACANT_AREA_SUMMARY
393 -- INVOKED FROM :
394 -- PURPOSE :
395 -- HISTORY :
396 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
397 -- table.
398 -------------------------------------------------------------------------------
399 PROCEDURE VACANT_AREA_SUMMARY
400 (
401 x_location_id number,
402 x_vacant_area in out NOCOPY number,
403 x_vacant_area_rtot_db in out NOCOPY number
404 ) IS
405
406 BEGIN
407
408 SELECT sum(PNP_UTIL_FUNC.get_vacant_area(location_id))
409 INTO x_vacant_area
410 FROM PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
411 WHERE location_id = x_location_id;
412
413 x_vacant_area_rtot_db := x_vacant_area ;
414
415 END VACANT_AREA_SUMMARY;
416
417
418 -------------------------------------------------------------------------------
419 -- PROCDURE : UTILIZED_CAPACITY_SUMMARY
420 -- INVOKED FROM :
421 -- PURPOSE :
422 -- HISTORY :
423 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
424 -- table.
425 -------------------------------------------------------------------------------
426 PROCEDURE UTILIZED_CAPACITY_SUMMARY (
427 x_location_id NUMBER,
428 x_utilized_capacity in OUT NOCOPY NUMBER,
429 x_utilized_capacity_rtot_db IN OUT NOCOPY NUMBER
430 ) IS
431
432 BEGIN
433
434 SELECT sum(PNP_UTIL_FUNC.get_utilized_capacity(location_id))
435 INTO x_utilized_capacity
436 FROM PN_SPACE_ALLOCATIONS_ALL
437 WHERE location_id = x_location_id ;
438
439 x_utilized_capacity_rtot_db := x_utilized_capacity ;
440
441 END UTILIZED_CAPACITY_SUMMARY;
442
443
444 -------------------------------------------------------------------------------
445 -- PROCDURE : AREA_PCT_AND_AREA
446 -- INVOKED FROM :
447 -- PURPOSE :
448 -- HISTORY :
449 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced PN_SPACE_ALLOCATIONS with _ALL
450 -- table.
451 -------------------------------------------------------------------------------
452 PROCEDURE AREA_PCT_AND_AREA (
453 x_usable_area number,
454 x_location_id number
455 ) IS
456
457 l_utilized number;
458
459 BEGIN
460
461 SELECT count(*)
462 INTO l_utilized
463 FROM pn_space_allocations_all --sdm_MOAC
464 WHERE location_id = x_location_id;
465
466
467 UPDATE PN_SPACE_ALLOCATIONS_ALL --sdm_MOAC
468 SET allocated_area_pct = round(100 / l_utilized, 2),
469 allocated_area = round(x_usable_area / l_utilized, 2)
470 WHERE location_id = x_location_id;
471
472
473 END AREA_PCT_AND_AREA;
474
475
476 /*-------------------------------------------------------------------------------
477 -- End of Pkg -- PN_SPACE_ALLOCATIONS_PKG
478 -------------------------------------------------------------------------------*/
479 END PN_SPACE_ALLOCATIONS_PKG;