[Home] [Help]
PACKAGE BODY: APPS.AHL_APPR_SPACE_UNAVL_PVT
Source
1 PACKAGE BODY AHL_APPR_SPACE_UNAVL_PVT AS
2 /* $Header: AHLVSUAB.pls 115.17 2003/11/04 10:43:21 rroy noship $ */
3 --
4
5 G_PKG_NAME VARCHAR2(30) := 'AHL_APPR_SPACE_UNAVL_PVT';
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7 --
8 -----------------------------------------------------------
9 -- PACKAGE
10 -- AHL_APPR_SPACE_UNAVL_PVT
11 --
12 -- PURPOSE
13 -- This package is a Private API for managing Space Unavailable information in
14 -- Advanced Services Online. It contains specification for pl/sql records and tables
15 --
16 -- AHL_SPACE_UNAVIALABLE_VL:
17 -- Create_Space_Restriction (see below for specification)
18 -- Update_Space_Restriction (see below for specification)
19 -- Delete_Space_Restriction (see below for specification)
20 -- Validate_Space_Restriction (see below for specification)
21 --
22 --
23 -- NOTES
24 --
25 --
26 -- HISTORY
27 -- 17-Apr-2002 ssurapan Created.
28
29 --------------------------------------------------------------------
30 -- PROCEDURE
31 -- CHECK_ORG_NAME_OR_ID
32 --
33 -- PURPOSE
34 -- Converts Org Name to ID or Vice versa
35 --
36 -- PARAMETERS
37 --
38 -- NOTES
39 --------------------------------------------------------------------
40 PROCEDURE Check_org_name_Or_Id
41 (p_organization_id IN NUMBER,
42 p_org_name IN VARCHAR2,
43 x_organization_id OUT NOCOPY NUMBER,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_error_msg_code OUT NOCOPY VARCHAR2
46 )
47 IS
48 BEGIN
49 IF (p_organization_id IS NOT NULL)
50 THEN
51 SELECT organization_id
52 INTO x_organization_id
53 FROM HR_ALL_ORGANIZATION_UNITS
54 WHERE organization_id = p_organization_id;
55 ELSE
56 SELECT organization_id
57 INTO x_organization_id
58 FROM HR_ALL_ORGANIZATION_UNITS
59 WHERE NAME = p_org_name;
60 END IF;
61 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
65 x_error_msg_code:= 'AHL_APPR_ORG_NT_EXISTS';
66 WHEN TOO_MANY_ROWS THEN
67 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
68 x_error_msg_code:= 'AHL_APPR_ORG_NT_EXISTS';
69 WHEN OTHERS THEN
70 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
71 RAISE;
72 END Check_org_name_Or_Id;
73 --------------------------------------------------------------------
74 -- PROCEDURE
75 -- CHECK_DEPT_DESC_OR_ID
76 --
77 -- PURPOSE
78 -- Converts Dept description to ID or Vice Versa
79 --
80 -- PARAMETERS
81 --
82 -- NOTES
83 --------------------------------------------------------------------
84 PROCEDURE Check_dept_desc_Or_Id
85 (p_organization_id IN NUMBER,
86 p_org_name IN VARCHAR2,
87 p_department_id IN NUMBER,
88 p_dept_description IN VARCHAR2,
89 x_department_id OUT NOCOPY NUMBER,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_error_msg_code OUT NOCOPY VARCHAR2
92 )
93 IS
94 BEGIN
95 IF (p_organization_id IS NOT NULL)
96 THEN
97 IF (p_dept_description IS NOT NULL)
98 THEN
99
100 IF G_DEBUG='Y' THEN
101 AHL_DEBUG_PUB.debug( 'Org id'||p_organization_id);
102 END IF;
103
104 SELECT department_id
105 INTO x_department_id
106 FROM HR_ALL_ORGANIZATION_UNITS HAOU, BOM_DEPARTMENTS BMDP
107 WHERE HAOU.organization_id = BMDP.organization_id
108 AND BMDP.organization_id = p_organization_id
109 AND BMDP.description = p_dept_description;
110 END IF;
111 ELSE
112 SELECT department_id
113 INTO x_department_id
114 FROM HR_ALL_ORGANIZATION_UNITS HAOU, BOM_DEPARTMENTS BMDP
115 WHERE HAOU.organization_id = BMDP.organization_id
116 AND BMDP.organization_id = (SELECT organization_id
117 FROM HR_ALL_ORGANIZATION_UNITS
118 WHERE NAME = p_org_name)
119 AND BMDP.description = p_dept_description;
120 END IF;
121 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
125 x_error_msg_code:= 'AHL_APPR_DPT_NOT_EXISTS';
126 WHEN TOO_MANY_ROWS THEN
127 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
128 x_error_msg_code:= 'AHL_APPR_DPT_NOT_EXISTS';
129 WHEN OTHERS THEN
130 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
131 RAISE;
132 END Check_dept_desc_Or_Id;
133
134 --------------------------------------------------------------------
135 -- PROCEDURE
136 -- CHECK_SPACE_NAME_OR_ID
137 --
138 -- PURPOSE
139 -- Converts Space Name to ID or Vice versa
140 --
141 -- PARAMETERS
142 --
143 -- NOTES
144 --------------------------------------------------------------------
145 PROCEDURE Check_space_name_Or_Id
146 (p_space_id IN NUMBER,
147 p_space_name IN VARCHAR2,
148 x_space_id OUT NOCOPY NUMBER,
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_error_msg_code OUT NOCOPY VARCHAR2
151 )
152 IS
153 BEGIN
154 IF (p_space_id IS NOT NULL)
155 THEN
156 SELECT space_id
157 INTO x_space_id
158 FROM AHL_SPACES_VL
159 WHERE space_id = p_space_id;
160 ELSE
161 SELECT space_id
162 INTO x_space_id
163 FROM AHL_SPACES_VL
164 WHERE SPACE_NAME = p_space_name;
165 END IF;
166 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
170 x_error_msg_code:= 'AHL_APPR_SPACE_NOT_EXISTS';
171 WHEN TOO_MANY_ROWS THEN
172 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
173 x_error_msg_code:= 'AHL_APPR_SPACE_NOT_EXISTS';
174 WHEN OTHERS THEN
175 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
176 RAISE;
177 END Check_space_name_Or_Id;
178
179 ---------------------------------------------------------------------
180 -- PROCEDURE
181 -- Assign_Space_Restic_Rec
182 --
183 ---------------------------------------------------------------------
184 PROCEDURE Assign_Space_Restric_Rec (
185 p_space_restriction_rec IN AHL_APPR_SPACE_UNAVL_PUB.Space_restriction_rec,
186 x_space_restriction_rec OUT NOCOPY Space_restriction_rec
187 )
188 IS
189
190 BEGIN
191 x_space_restriction_rec.space_unavailability_id := p_space_restriction_rec.space_unavailability_id;
192 x_space_restriction_rec.organization_id := p_space_restriction_rec.organization_id;
193 x_space_restriction_rec.org_name := p_space_restriction_rec.org_name;
194 x_space_restriction_rec.department_id := p_space_restriction_rec.department_id;
195 x_space_restriction_rec.dept_Description := p_space_restriction_rec.dept_description;
196 x_space_restriction_rec.space_id := p_space_restriction_rec.space_id;
197 x_space_restriction_rec.space_name := p_space_restriction_rec.space_name;
198 x_space_restriction_rec.start_date := p_space_restriction_rec.start_date;
199 x_space_restriction_rec.end_date := p_space_restriction_rec.end_date;
200 x_space_restriction_rec.description := p_space_restriction_rec.description;
201 x_space_restriction_rec.object_version_number := p_space_restriction_rec.object_version_number;
202 x_space_restriction_rec.attribute_category := p_space_restriction_rec.attribute_category;
203 x_space_restriction_rec.attribute1 := p_space_restriction_rec.attribute1;
204 x_space_restriction_rec.attribute2 := p_space_restriction_rec.attribute2;
205 x_space_restriction_rec.attribute3 := p_space_restriction_rec.attribute3;
206 x_space_restriction_rec.attribute4 := p_space_restriction_rec.attribute4;
207 x_space_restriction_rec.attribute5 := p_space_restriction_rec.attribute5;
208 x_space_restriction_rec.attribute6 := p_space_restriction_rec.attribute6;
209 x_space_restriction_rec.attribute7 := p_space_restriction_rec.attribute7;
210 x_space_restriction_rec.attribute8 := p_space_restriction_rec.attribute8;
211 x_space_restriction_rec.attribute9 := p_space_restriction_rec.attribute9;
212 x_space_restriction_rec.attribute10 := p_space_restriction_rec.attribute10;
213 x_space_restriction_rec.attribute11 := p_space_restriction_rec.attribute11;
214 x_space_restriction_rec.attribute12 := p_space_restriction_rec.attribute12;
215 x_space_restriction_rec.attribute13 := p_space_restriction_rec.attribute13;
216 x_space_restriction_rec.attribute14 := p_space_restriction_rec.attribute14;
217 x_space_restriction_rec.attribute15 := p_space_restriction_rec.attribute15;
218
219 END Assign_Space_Restric_Rec;
220
221
222 ---------------------------------------------------------------------
223 -- PROCEDURE
224 -- Complete_Space_Restic_Rec
225 --
226 ---------------------------------------------------------------------
227 PROCEDURE Complete_Space_Restric_Rec (
228 p_space_restriction_rec IN Space_restriction_rec,
229 x_space_restriction_rec OUT NOCOPY Space_restriction_rec
230 )
231 IS
232 CURSOR c_space_restriction_rec
233 IS
234 SELECT ROW_ID,
235 SPACE_ID,
236 START_DATE,
237 END_DATE,
238 DESCRIPTION,
239 OBJECT_VERSION_NUMBER,
240 ATTRIBUTE_CATEGORY,
241 ATTRIBUTE1,
242 ATTRIBUTE2,
243 ATTRIBUTE3,
244 ATTRIBUTE4,
245 ATTRIBUTE5,
246 ATTRIBUTE6,
247 ATTRIBUTE7,
248 ATTRIBUTE8,
249 ATTRIBUTE9,
250 ATTRIBUTE10,
251 ATTRIBUTE11,
252 ATTRIBUTE12,
253 ATTRIBUTE13,
254 ATTRIBUTE14,
255 ATTRIBUTE15
256 FROM ahl_space_unavailable_vl
257 WHERE space_unavailability_id = p_space_restriction_rec.space_unavailability_id
258 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
259 --
260 -- This is the only exception for using %ROWTYPE.
261 l_space_restriction_rec c_space_restriction_rec%ROWTYPE;
262 BEGIN
263 x_space_restriction_rec := p_space_restriction_rec;
264 OPEN c_space_restriction_rec;
265 FETCH c_space_restriction_rec INTO l_space_restriction_rec;
266 IF c_space_restriction_rec%NOTFOUND THEN
267 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
268 FND_MESSAGE.set_name('AHL', 'AHL_APPR_RECORD_NOT_FOUND');
269 FND_MSG_PUB.add;
270 RAISE Fnd_Api.G_EXC_ERROR;
271 END IF;
272 END IF;
273 CLOSE c_space_restriction_rec;
274 --Check for object version number
275 IF (l_space_restriction_rec.object_version_number <> p_space_restriction_rec.object_version_number)
276 THEN
277 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
278 Fnd_Msg_Pub.ADD;
279 RAISE Fnd_Api.G_EXC_ERROR;
280 END IF;
281 -- SPACE ID
282 IF p_space_restriction_rec.space_id <> FND_API.g_miss_num THEN
283 x_space_restriction_rec.space_id := p_space_restriction_rec.space_id;
284 ELSE
285 x_space_restriction_rec.space_id := l_space_restriction_rec.space_id;
286 END IF;
287 -- DESCRIPTION
288 IF nvl(p_space_restriction_rec.description, 'x') <> FND_API.g_miss_char THEN
289 x_space_restriction_rec.description := p_space_restriction_rec.description;
290 ELSE
291 x_space_restriction_rec.description := l_space_restriction_rec.description;
292 END IF;
293 -- ATTRIBUTE CATEGORY
294 IF nvl(p_space_restriction_rec.attribute_category,'x') <> FND_API.g_miss_char THEN
295 x_space_restriction_rec.attribute_category := p_space_restriction_rec.attribute_category;
296 ELSE
297 x_space_restriction_rec.attribute_category := l_space_restriction_rec.attribute_category;
298 END IF;
299 -- ATTRIBUTE 1
300 IF nvl(p_space_restriction_rec.attribute1,'x') = FND_API.g_miss_char THEN
301 x_space_restriction_rec.attribute1 := p_space_restriction_rec.attribute1;
302 ELSE
303 x_space_restriction_rec.attribute1 := l_space_restriction_rec.attribute1;
304 END IF;
305 -- ATTRIBUTE 2
306 IF nvl(p_space_restriction_rec.attribute2,'x') = FND_API.g_miss_char THEN
307 x_space_restriction_rec.attribute2 := p_space_restriction_rec.attribute2;
308 END IF;
309 -- ATTRIBUTE 3
310 IF nvl(p_space_restriction_rec.attribute3,'x') = FND_API.g_miss_char THEN
311 x_space_restriction_rec.attribute3 := l_space_restriction_rec.attribute3;
312 END IF;
313 -- ATTRIBUTE 4
314 IF nvl(p_space_restriction_rec.attribute4,'x') = FND_API.g_miss_char THEN
315 x_space_restriction_rec.attribute4 := l_space_restriction_rec.attribute4;
316 END IF;
317 -- ATTRIBUTE 5
318 IF nvl(p_space_restriction_rec.attribute5,'x') = FND_API.g_miss_char THEN
319 x_space_restriction_rec.attribute5 := l_space_restriction_rec.attribute5;
320 END IF;
321 -- ATTRIBUTE 6
322 IF nvl(p_space_restriction_rec.attribute6,'x') = FND_API.g_miss_char THEN
323 x_space_restriction_rec.attribute6 := l_space_restriction_rec.attribute6;
324 END IF;
325 -- ATTRIBUTE 7
326 IF nvl(p_space_restriction_rec.attribute7,'x') = FND_API.g_miss_char THEN
327 x_space_restriction_rec.attribute7 := l_space_restriction_rec.attribute7;
328 END IF;
329 -- ATTRIBUTE 8
330 IF nvl(p_space_restriction_rec.attribute8,'x') = FND_API.g_miss_char THEN
331 x_space_restriction_rec.attribute8 := l_space_restriction_rec.attribute8;
332 END IF;
333 -- ATTRIBUTE 9
334 IF nvl(p_space_restriction_rec.attribute9,'x') = FND_API.g_miss_char THEN
335 x_space_restriction_rec.attribute9 := l_space_restriction_rec.attribute9;
336 END IF;
337 -- ATTRIBUTE 10
338 IF nvl(p_space_restriction_rec.attribute10,'x') = FND_API.g_miss_char THEN
339 x_space_restriction_rec.attribute10 := l_space_restriction_rec.attribute10;
340 END IF;
341 -- ATTRIBUTE 11
342 IF nvl(p_space_restriction_rec.attribute11,'x') = FND_API.g_miss_char THEN
343 x_space_restriction_rec.attribute11 := l_space_restriction_rec.attribute11;
344 END IF;
345 -- ATTRIBUTE 12
346 IF nvl(p_space_restriction_rec.attribute12,'x') = FND_API.g_miss_char THEN
347 x_space_restriction_rec.attribute12 := l_space_restriction_rec.attribute12;
348 END IF;
349 -- ATTRIBUTE 13
350 IF nvl(p_space_restriction_rec.attribute13,'x') = FND_API.g_miss_char THEN
351 x_space_restriction_rec.attribute13 := l_space_restriction_rec.attribute13;
352 END IF;
353 -- ATTRIBUTE 14
354 IF nvl(p_space_restriction_rec.attribute14,'x') = FND_API.g_miss_char THEN
355 x_space_restriction_rec.attribute14 := l_space_restriction_rec.attribute14;
356 END IF;
357 -- ATTRIBUTE 15
358 IF nvl(p_space_restriction_rec.attribute15,'x') = FND_API.g_miss_char THEN
359 x_space_restriction_rec.attribute15 := l_space_restriction_rec.attribute15;
360 END IF;
361
362 END Complete_Space_Restric_Rec;
363
364 ------------------------------------------------------------------------------
365 --
366 -- NAME
367 -- Validate_Space_Restrict_Items
368 --
369 -- PURPOSE
370 -- This procedure is to validate Space Unavailability attributes
371 -- End of Comments
372 -------------------------------------------------------------------------------
373 PROCEDURE Validate_Space_Restrict_Items
374 ( p_space_restriction_rec IN space_restriction_rec,
375 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
376 x_return_status OUT NOCOPY VARCHAR2
377 ) IS
378 l_table_name VARCHAR2(30);
379 l_pk_name VARCHAR2(30);
380 l_pk_value VARCHAR2(30);
381 l_where_clause VARCHAR2(2000);
382
383
384 BEGIN
385 -- Initialize API/Procedure return status to success
386 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
387 -- Check required parameters
388 IF (p_space_restriction_rec.ORGANIZATION_ID IS NULL
389 OR
390 p_space_restriction_rec.ORGANIZATION_ID = FND_API.G_MISS_NUM)
391 --
392 THEN
393 -- missing required fields
394 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
395 THEN
396 Fnd_Message.set_name('AHL', 'AHL_APPR_ORG_REQUIRED');
397 Fnd_Msg_Pub.ADD;
398 END IF;
399 x_return_status := Fnd_Api.G_RET_STS_ERROR;
400 END IF;
401 --
402 IF (p_space_restriction_rec.DEPARTMENT_ID IS NULL
403 OR
404 p_space_restriction_rec.DEPARTMENT_ID = FND_API.G_MISS_NUM)
405 --
406 THEN
407 -- missing required fields
408 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
409 THEN
410 Fnd_Message.set_name('AHL', 'AHL_APPR_DEPT_REQUIRED');
411 Fnd_Msg_Pub.ADD;
412 END IF;
413 x_return_status := Fnd_Api.G_RET_STS_ERROR;
414 END IF;
415 --
416 IF (p_space_restriction_rec.SPACE_ID IS NULL
417 OR
418 p_space_restriction_rec.SPACE_ID = FND_API.G_MISS_NUM)
419 --
420 THEN
421 -- missing required fields
422 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
423 THEN
424 Fnd_Message.set_name('AHL', 'AHL_APPR_SPACE_ID_NOT_EXIST');
425 Fnd_Msg_Pub.ADD;
426 END IF;
427 x_return_status := Fnd_Api.G_RET_STS_ERROR;
428 END IF;
429 -- START_DATE
430 IF (p_space_restriction_rec.START_DATE = Fnd_Api.G_MISS_DATE OR
431 p_space_restriction_rec.START_DATE IS NULL)
432 THEN
433 -- missing required fields
434 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
435 THEN
436 Fnd_Message.set_name('AHL', 'AHL_APPR_START_DATE_NOT_EXIST');
437 Fnd_Msg_Pub.ADD;
438 END IF;
439 x_return_status := Fnd_Api.G_RET_STS_ERROR;
440 END IF;
441 --END DATE
442 IF (p_space_restriction_rec.END_DATE = Fnd_Api.G_MISS_DATE OR
443 p_space_restriction_rec.END_DATE IS NULL)
444 THEN
445 -- missing required fields
446 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
447 THEN
448 Fnd_Message.set_name('AHL', 'AHL_APPR_END_DATE_NOT_EXIST');
449 Fnd_Msg_Pub.ADD;
450 END IF;
451 x_return_status := Fnd_Api.G_RET_STS_ERROR;
452 END IF;
453
454 -- Check FK parameter: SPACE_ID
455 IF p_space_restriction_rec.SPACE_ID <> Fnd_Api.g_miss_num
456 THEN
457 l_table_name := 'AHL_SPACES_VL';
458 l_pk_name := 'SPACE_ID';
459 l_pk_value := p_space_restriction_rec.SPACE_ID;
460 IF Ahl_Utility_Pvt.Check_FK_Exists (
461 p_table_name => l_table_name,
462 p_pk_name => l_pk_name,
463 p_pk_value => l_pk_value
464 ) = Fnd_Api.G_FALSE
465 THEN
466 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
467 THEN
468 Fnd_Message.set_name('AHL', 'AHL_APPR_INVALID_SPACE_ID');
469 Fnd_Msg_Pub.ADD;
470 END IF;
471 x_return_status := Fnd_Api.G_RET_STS_ERROR;
472 END IF; -- check_fk_exists
473 END IF;
474
475 END Validate_Space_Restrict_Items;
476 ----------------------------------------------------------------------------
477 -- NAME
478 -- Validate_Space_Restrict_Record
479 --
480 -- PURPOSE
481 -- This procedure is to validate Space Restriction record
482 --
483 -- NOTES
484 -- End of Comments
485 -----------------------------------------------------------------------------
486 PROCEDURE Validate_Space_Restrict_Record(
487 p_space_restriction_rec IN space_restriction_rec,
488 x_return_status OUT NOCOPY VARCHAR2
489 ) IS
490 --
491 CURSOR space_visit_cur (c_space_id IN NUMBER)
492 IS
493 SELECT a.visit_id,
494 space_id,
495 trunc(start_date_time) start_date_time
496 FROM ahl_space_Assignments a,
497 ahl_visits_b b
498 WHERE a.visit_id = b.visit_id
499 AND a.space_id = c_space_id;
500 --
501 CURSOR space_restirct_date_cur
502 (c_space_unavailability_id IN NUMBER)
503 IS
504 SELECT start_date, end_date
505 FROM ahl_space_unavailable_b
506 WHERE space_unavailability_id = c_space_unavailability_id;
507 --
508 -- Status Local Variables
509 l_return_status VARCHAR2(1);
510 l_visit_id NUMBER;
511 l_space_id NUMBER;
512 l_start_date_time DATE;
513 l_start_date DATE;
514 l_end_date DATE;
515 BEGIN
516 -- Initialize API return status to success
517 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
518 --
519 IF (p_space_restriction_rec.space_unavailability_id = fnd_api.g_miss_num
520 or
521 p_space_restriction_rec.space_unavailability_id IS NULL )THEN
522 IF p_space_restriction_rec.START_DATE IS NOT NULL
523 THEN
524 IF p_space_restriction_rec.START_DATE < TRUNC(SYSDATE)
525 THEN
526 Fnd_Message.set_name('AHL', 'AHL_APPR_INVALID_START_DATE');
527 Fnd_Msg_Pub.ADD;
528 x_return_status := Fnd_Api.G_RET_STS_ERROR;
529 END IF;
530 END IF;
531
532 --
533 IF (p_space_restriction_rec.END_DATE IS NOT NULL AND
534 p_space_restriction_rec.START_DATE IS NOT NULL)
535 THEN
536 IF( p_space_restriction_rec.END_DATE < p_space_restriction_rec.START_DATE )
537 THEN
538 Fnd_Message.set_name('AHL', 'AHL_APPR_INV_START_END_DATE');
539 Fnd_Msg_Pub.ADD;
540 x_return_status := Fnd_Api.G_RET_STS_ERROR;
541 END IF;
542 END IF;
543
544 --
545 IF (p_space_restriction_rec.START_DATE IS NULL AND
546 p_space_restriction_rec.END_DATE IS NOT NULL) THEN
547 Fnd_Message.set_name('AHL', 'AHL_APPR_INVALID_START_DATE');
548 Fnd_Msg_Pub.ADD;
549 END IF;
550 --
551 ELSE
552 OPEN space_restirct_date_cur(p_space_restriction_rec.space_unavailability_id);
553 FETCH space_restirct_date_cur INTO l_start_date, l_end_date;
554 CLOSE space_restirct_date_cur;
555 IF (p_space_restriction_rec.START_DATE IS NOT NULL AND
556 p_space_restriction_rec.START_DATE <> l_start_date)
557 THEN
558 IF p_space_restriction_rec.START_DATE < TRUNC(SYSDATE)
559 THEN
560 Fnd_Message.set_name('AHL', 'AHL_APPR_INVALID_START_DATE');
561 Fnd_Msg_Pub.ADD;
562 END IF;
563 END IF;
564 --
565 IF ((p_space_restriction_rec.END_DATE IS NOT NULL AND
566 p_space_restriction_rec. END_DATE <> l_end_date) AND
567 (p_space_restriction_rec.START_DATE IS NOT NULL AND
568 p_space_restriction_rec.START_DATE <> l_start_date))
569 THEN
570 IF( p_space_restriction_rec.END_DATE < p_space_restriction_rec.START_DATE )
571 THEN
572 Fnd_Message.set_name('AHL', 'AHL_APPR_INV_START_END_DATE');
573 Fnd_Msg_Pub.ADD;
574 x_return_status := Fnd_Api.G_RET_STS_ERROR;
575 END IF;
576 END IF;
577 --
578 IF ((p_space_restriction_rec.END_DATE IS NOT NULL AND
579 p_space_restriction_rec. END_DATE <> l_end_date) AND
580 (p_space_restriction_rec.START_DATE IS NOT NULL AND
581 p_space_restriction_rec.START_DATE = l_start_date))
582 THEN
583 IF( p_space_restriction_rec.END_DATE < p_space_restriction_rec.START_DATE )
584 THEN
585 Fnd_Message.set_name('AHL', 'AHL_APPR_INV_START_END_DATE');
586 Fnd_Msg_Pub.ADD;
587 x_return_status := Fnd_Api.G_RET_STS_ERROR;
588 END IF;
589 END IF;
590 --
591 IF ((p_space_restriction_rec.END_DATE IS NOT NULL AND
592 p_space_restriction_rec. END_DATE = l_end_date) AND
593 (p_space_restriction_rec.START_DATE IS NOT NULL AND
594 p_space_restriction_rec.START_DATE <> l_start_date))
595 THEN
596 IF( p_space_restriction_rec.END_DATE < p_space_restriction_rec.START_DATE )
597 THEN
598 Fnd_Message.set_name('AHL', 'AHL_APPR_INV_START_END_DATE');
599 Fnd_Msg_Pub.ADD;
600 x_return_status := Fnd_Api.G_RET_STS_ERROR;
601 END IF;
602 END IF;
603 --
604
605 IF ((p_space_restriction_rec.END_DATE IS NOT NULL AND
606 p_space_restriction_rec. END_DATE = l_end_date) AND
607 (p_space_restriction_rec.START_DATE IS NOT NULL AND
608 p_space_restriction_rec.START_DATE = l_start_date))
609 THEN
610 IF( p_space_restriction_rec.END_DATE < p_space_restriction_rec.START_DATE )
611 THEN
612 Fnd_Message.set_name('AHL', 'AHL_APPR_INV_START_END_DATE');
613 Fnd_Msg_Pub.ADD;
614 x_return_status := Fnd_Api.G_RET_STS_ERROR;
615 END IF;
616 END IF;
617
618
619 /*
620 --
621 IF ((p_space_restriction_rec.START_DATE IS NOT NULL AND
622 p_space_restriction_rec.START_DATE <> l_start_date) AND
623 (p_space_restriction_rec.END_DATE IS NOT NULL AND
624 p_space_restriction_rec.END_DATE <> l_end_date ))
625 THEN
626 IF p_space_restriction_rec.END_DATE < trunc(sysdate) THEN
627 Fnd_Message.set_name('AHL', 'AHL_APPR_INVALID_END_DATE');
628 Fnd_Msg_Pub.ADD;
629 x_return_status := Fnd_Api.G_RET_STS_ERROR;
630 END IF;
631 END IF; */
632 --
633 IF (p_space_restriction_rec.END_DATE IS NOT NULL AND
634 p_space_restriction_rec.END_DATE <> l_end_date )
635 THEN
636 IF p_space_restriction_rec.END_DATE < trunc(sysdate) THEN
637 Fnd_Message.set_name('AHL', 'AHL_APPR_INVALID_END_DATE');
638 Fnd_Msg_Pub.ADD;
639 x_return_status := Fnd_Api.G_RET_STS_ERROR;
640 END IF;
641 END IF;
642
643 END IF;
644 IF G_DEBUG='Y' THEN
645 AHL_DEBUG_PUB.debug( 'date 3:'||p_space_restriction_rec.start_date);
646 END IF;
647 --Check for any visits exist during start and end dates
648 OPEN space_visit_cur(p_space_restriction_rec.space_id);
649 LOOP
650 FETCH space_visit_cur INTO l_visit_id,l_space_id,l_start_date_time;
651 EXIT WHEN space_visit_cur%NOTFOUND;
652 IF l_visit_id IS NOT NULL THEN
653 IF (l_start_date_time >= p_space_restriction_rec.START_DATE
654 AND l_start_date_time <= p_space_restriction_rec.END_DATE)
655 THEN
656 Fnd_Message.set_name('AHL', 'AHL_APPR_SPACE_VISITS_EXIST');
657 Fnd_Msg_Pub.ADD;
658 END IF;
659 END IF;
660 END LOOP;
661 CLOSE space_visit_cur;
662 --
663
664 --
665 END Validate_Space_Restrict_Record;
666 --------------------------------------------------------------------
667 -- PROCEDURE
668 -- Validate_Space_Restriction
669 --
670 -- PURPOSE
671 -- Validate space restriction attributes
672 --
673 -- PARAMETERS
674 --
675 -- NOTES
676 --
677 --------------------------------------------------------------------
678 PROCEDURE Validate_Space_Restriction
679 ( p_api_version IN NUMBER,
680 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
681 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
682 p_space_restriction_rec IN space_restriction_rec,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2
686 )
687 IS
688 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Space_Restriction';
689 l_api_version CONSTANT NUMBER := 1.0;
690 l_full_name CONSTANT VARCHAR2(70) := G_PKG_NAME || '.' || l_api_name;
691 l_return_status VARCHAR2(1);
692 l_space_restriction_rec space_restriction_rec;
693 BEGIN
694 -- Standard call to check for call compatibility.
695 IF NOT Fnd_Api.Compatible_API_Call ( l_api_version,
696 p_api_version,
697 l_api_name,
698 G_PKG_NAME)
699 THEN
700 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
701 END IF;
702 -- Initialize message list if p_init_msg_list is set to TRUE.
703 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
704 Fnd_Msg_Pub.initialize;
705 END IF;
706 -- Initialize API return status to success
707 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
708 --
709 -- API body
710 --
711 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item
712 THEN
713 Validate_Space_Restrict_Items
714 ( p_space_restriction_rec => p_space_restriction_rec,
715 p_validation_mode => Jtf_Plsql_Api.g_create,
716 x_return_status => l_return_status
717 );
718 -- If any errors happen abort API.
719 IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
720 THEN
721 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
722 ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
723 THEN
724 RAISE Fnd_Api.G_EXC_ERROR;
725 END IF;
726 END IF;
727 -- Perform cross attribute validation and missing attribute checks. Record
728 -- level validation.
729 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record
730 THEN
731 Validate_Space_Restrict_Record(
732 p_space_restriction_rec => p_space_restriction_rec,
733 x_return_status => l_return_status
734 );
735 IF l_return_status = Fnd_Api.G_RET_STS_ERROR
736 THEN
737 RAISE Fnd_Api.G_EXC_ERROR;
738 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
739 THEN
740 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
741 END IF;
742 END IF;
743 --
744 -- END of API body.
745 --
746 -------------------- finish --------------------------
747
748 Fnd_Msg_Pub.count_and_get(
749 p_encoded => Fnd_Api.g_false,
750 p_count => x_msg_count,
751 p_data => x_msg_data);
752 EXCEPTION
753 WHEN Fnd_Api.G_EXC_ERROR THEN
754 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
755 Fnd_Msg_Pub.Count_AND_Get
756 ( p_count => x_msg_count,
757 p_data => x_msg_data,
758 p_encoded => Fnd_Api.G_FALSE
759 );
760 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
761 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
762 Fnd_Msg_Pub.Count_AND_Get
763 ( p_count => x_msg_count,
764 p_data => x_msg_data,
765 p_encoded => Fnd_Api.G_FALSE
766 );
767 WHEN OTHERS THEN
768 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
769 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
770 THEN
771 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
772 END IF;
773 Fnd_Msg_Pub.Count_AND_Get
774 ( p_count => x_msg_count,
775 p_data => x_msg_data,
776 p_encoded => Fnd_Api.G_FALSE
777 );
778 END Validate_Space_Restriction;
779
780 --------------------------------------------------------------------
781 -- PROCEDURE
782 -- Create_Space_Restriction
783 --
784 -- PURPOSE
785 -- Create Space Restriction Record
786 --
787 -- PARAMETERS
788 -- p_space_restriction_rec: the record representing AHL_SPACE_UNAVAILABLE_VL view..
789 -- x_space_unavailability_id: the space_unavailability_id.
790 --
791 -- NOTES
792 --------------------------------------------------------------------
793
794 PROCEDURE Create_Space_Restriction (
795 p_api_version IN NUMBER,
796 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
797 p_commit IN VARCHAR2 := FND_API.g_false,
798 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
799 p_module_type IN VARCHAR2 := 'JSP',
800 p_x_space_restriction_rec IN OUT NOCOPY ahl_appr_space_unavl_pub.Space_Restriction_Rec,
801 x_return_status OUT NOCOPY VARCHAR2,
802 x_msg_count OUT NOCOPY NUMBER,
803 x_msg_data OUT NOCOPY VARCHAR2
804 )
805 IS
806 --
807 CURSOR c_seq
808 IS
809 SELECT AHL_SPACE_UNAVAILABLE_B_S.NEXTVAL
810 FROM dual;
811 --
812 CURSOR c_id_exists (x_id IN NUMBER) IS
813 SELECT 1
814 FROM dual
815 WHERE EXISTS (SELECT 1
816 FROM ahl_space_unavailable_b
817 WHERE space_unavailability_id = x_id);
818 --
819 CURSOR check_duplicate_cur (c_space_id IN NUMBER,
820 c_start_date IN DATE,
821 c_end_date IN DATE)
822 IS
823 SELECT space_id
824 FROM AHL_SPACE_UNAVAILABLE_VL
825 WHERE space_id = c_space_id
826 AND trunc(start_date) = c_start_date
827 AND trunc(end_date) = c_end_date;
828 --
829 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SPACE_RESTRICTION';
830 l_api_version CONSTANT NUMBER := 1.0;
831 l_msg_count NUMBER;
832 l_return_status VARCHAR2(1);
833 l_msg_data VARCHAR2(2000);
834 l_dummy NUMBER;
835 l_dup_id NUMBER;
836 l_rowid VARCHAR2(30);
837 l_organization_id NUMBER;
838 l_department_id NUMBER;
839 l_space_id NUMBER;
840 l_space_unavaialability_id NUMBER;
841 l_start_date DATE := trunc(p_x_space_restriction_rec.start_date);
842 l_end_date DATE := trunc(p_x_space_restriction_rec.end_date);
843 l_space_restriction_rec Space_Restriction_Rec;
844
845 BEGIN
846 --------------------Initialize ----------------------------------
847 -- Standard Start of API savepoint
848 SAVEPOINT create_space_restriction;
849 -- Check if API is called in debug mode. If yes, enable debug.
850 IF G_DEBUG='Y' THEN
851 AHL_DEBUG_PUB.enable_debug;
852 END IF;
853 -- Debug info.
854 IF G_DEBUG='Y' THEN
855 AHL_DEBUG_PUB.debug( 'enter ahl_appr_space_unavl_pvt.Create Space Restriction','+SUAVL+');
856 END IF;
857 -- Standard call to check for call compatibility.
858 IF FND_API.to_boolean(p_init_msg_list)
859 THEN
860 FND_MSG_PUB.initialize;
861 END IF;
862 -- Initialize API return status to success
863 x_return_status := FND_API.G_RET_STS_SUCCESS;
864 -- Initialize message list if p_init_msg_list is set to TRUE.
865 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
866 p_api_version,
867 l_api_name,G_PKG_NAME)
868 THEN
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870 END IF;
871
872 --------------------Value OR ID conversion---------------------------
873 --Start API Body
874 IF p_module_type = 'JSP'
875 THEN
876 p_x_space_restriction_rec.organization_id := null;
877 p_x_space_restriction_rec.department_id := null;
878 p_x_space_restriction_rec.space_id := null;
879 END IF;
880 -- Convert org name to organization id
881 IF (p_x_space_restriction_rec.org_name IS NOT NULL AND
882 p_x_space_restriction_rec.org_name <> FND_API.G_MISS_CHAR ) OR
883 (p_x_space_restriction_rec.organization_id IS NOT NULL AND
884 p_x_space_restriction_rec.organization_id <> FND_API.G_MISS_NUM) THEN
885
886 Check_org_name_Or_Id
887 (p_organization_id => p_x_space_restriction_rec.organization_id,
888 p_org_name => p_x_space_restriction_rec.org_name,
889 x_organization_id => l_organization_id,
890 x_return_status => l_return_status,
891 x_error_msg_code => l_msg_data);
892
893 IF NVL(l_return_status,'x') <> 'S'
894 THEN
895 Fnd_Message.SET_NAME('AHL','AHL_APPR_ORG_NT_EXISTS');
896 Fnd_Message.SET_TOKEN('ORGID',p_x_space_restriction_rec.org_name);
897 Fnd_Msg_Pub.ADD;
898 END IF;
899 END IF;
900 --Assign the returned value
901 p_x_space_restriction_rec.organization_id := l_organization_id;
902
903 IF G_DEBUG='Y' THEN
904 AHL_DEBUG_PUB.debug( 'Org id'||p_x_space_restriction_rec.organization_id);
905 END IF;
906
907 -- Get dept code using dept description
908 IF (p_x_space_restriction_rec.dept_description IS NOT NULL AND
909 p_x_space_restriction_rec.dept_description <> FND_API.G_MISS_CHAR ) OR
910 (p_x_space_restriction_rec.department_id IS NOT NULL AND
911 p_x_space_restriction_rec.department_id <> FND_API.G_MISS_NUM) THEN
912
913 Check_dept_desc_Or_Id
914 (p_organization_id => p_x_space_restriction_rec.organization_id,
915 p_org_name => p_x_space_restriction_rec.org_name,
916 p_dept_description => p_x_space_restriction_rec.dept_description,
917 p_department_id => p_x_space_restriction_rec.department_id,
918 x_department_id => l_department_id,
919 x_return_status => l_return_status,
920 x_error_msg_code => l_msg_data);
921
922 IF NVL(l_return_status,'x') <> 'S'
923 THEN
924 Fnd_Message.SET_NAME('AHL','AHL_APPR_DPT_NOT_EXISTS');
925 Fnd_Message.SET_TOKEN('DEPTID',p_x_space_restriction_rec.dept_description);
926 Fnd_Msg_Pub.ADD;
927 END IF;
928 END IF;
929 --Assign the returned value
930 p_x_space_restriction_rec.department_id := l_department_id;
931
932 IF G_DEBUG='Y' THEN
933 AHL_DEBUG_PUB.debug( 'Dept id'||p_x_space_restriction_rec.department_id);
934 END IF;
935 -- Convert Space name to space id
936 IF (p_x_space_restriction_rec.space_name IS NOT NULL AND
937 p_x_space_restriction_rec.space_name <> FND_API.G_MISS_CHAR ) OR
938 (p_x_space_restriction_rec.space_id IS NOT NULL AND
939 p_x_space_restriction_rec.space_id <> FND_API.G_MISS_NUM) THEN
940
941 Check_space_name_Or_Id
942 (p_space_id => p_x_space_restriction_rec.space_id,
943 p_space_name => p_x_space_restriction_rec.space_name,
944 x_space_id => l_space_id,
945 x_return_status => l_return_status,
946 x_error_msg_code => l_msg_data);
947
948 IF NVL(l_return_status,'x') <> 'S'
949 THEN
950 Fnd_Message.SET_NAME('AHL','AHL_APPR_SPACE_NOT_EXISTS');
951 Fnd_Message.SET_TOKEN('SPACEID',p_x_space_restriction_rec.space_name);
952 Fnd_Msg_Pub.ADD;
953 END IF;
954 END IF;
955 --Assign the returned value
956 p_x_space_restriction_rec.space_id := l_space_id;
957
958 IF G_DEBUG='Y' THEN
959 AHL_DEBUG_PUB.debug( 'Space id'||p_x_space_restriction_rec.space_id);
960 END IF;
961
962 --Standard check to count messages
963 l_msg_count := Fnd_Msg_Pub.count_msg;
964
965 IF l_msg_count > 0 THEN
966 X_msg_count := l_msg_count;
967 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
968 RAISE Fnd_Api.G_EXC_ERROR;
969 END IF;
970
971 --------------------------------Validation ---------------------------
972 --Assign to local variable
973 Assign_Space_Restric_Rec (
974 p_space_restriction_rec => p_x_space_restriction_rec,
975 x_space_restriction_rec => l_Space_restriction_rec);
976
977 -- Call Validate space rec input attributes
978 Validate_Space_Restriction
979 ( p_api_version => l_api_version,
980 p_init_msg_list => p_init_msg_list,
981 p_validation_level => p_validation_level,
982 p_space_restriction_rec => l_Space_restriction_rec,
983 x_return_status => l_return_status,
984 x_msg_count => l_msg_count,
985 x_msg_data => l_msg_data );
986
987 IF (p_x_space_restriction_rec.space_unavailability_id = Fnd_Api.G_MISS_NUM OR
988 p_x_space_restriction_rec.space_unavailability_id IS NULL )
989 THEN
990 --
991 -- If the ID is not passed into the API, then
992 -- grab a value from the sequence.
993 OPEN c_seq;
994 FETCH c_seq INTO l_space_unavaialability_id;
995 CLOSE c_seq;
996 --
997 -- Check to be sure that the sequence does not exist.
998 OPEN c_id_exists (l_space_unavaialability_id);
999 FETCH c_id_exists INTO l_dummy;
1000 CLOSE c_id_exists;
1001 --
1002 -- If the value for the ID already exists, then
1003 -- l_dummy would be populated with '1', otherwise,
1004 -- it receives NULL.
1005 IF l_dummy IS NOT NULL THEN
1006 Fnd_Message.SET_NAME('AHL','AHL_APPR_SEQUENCE_NOT_EXISTS');
1007 Fnd_Msg_Pub.ADD;
1008 END IF;
1009
1010 -- For optional fields
1011 IF p_x_space_restriction_rec.description = FND_API.G_MISS_CHAR
1012 THEN
1013 l_space_restriction_rec.description := NULL;
1014 ELSE
1015 l_space_restriction_rec.description := p_x_space_restriction_rec.description;
1016 END IF;
1017 --
1018 IF p_x_space_restriction_rec.attribute_category = FND_API.G_MISS_CHAR
1019 THEN
1020 l_space_restriction_rec.attribute_category := NULL;
1021 ELSE
1022 l_space_restriction_rec.attribute_category := p_x_space_restriction_rec.attribute_category;
1023 END IF;
1024 --
1025 IF p_x_space_restriction_rec.attribute1 = FND_API.G_MISS_CHAR
1026 THEN
1027 l_space_restriction_rec.attribute1 := NULL;
1028 ELSE
1029 l_space_restriction_rec.attribute1 := p_x_space_restriction_rec.attribute1;
1030 END IF;
1031 --
1032 IF p_x_space_restriction_rec.attribute2 = FND_API.G_MISS_CHAR
1033 THEN
1034 l_space_restriction_rec.attribute2 := NULL;
1035 ELSE
1036 l_space_restriction_rec.attribute2 := p_x_space_restriction_rec.attribute2;
1037 END IF;
1038 --
1039 IF p_x_space_restriction_rec.attribute3 = FND_API.G_MISS_CHAR
1040 THEN
1041 l_space_restriction_rec.attribute3 := NULL;
1042 ELSE
1043 l_space_restriction_rec.attribute3 := p_x_space_restriction_rec.attribute3;
1044 END IF;
1045 --
1046 IF p_x_space_restriction_rec.attribute4 = FND_API.G_MISS_CHAR
1047 THEN
1048 l_space_restriction_rec.attribute4 := NULL;
1049 ELSE
1050 l_space_restriction_rec.attribute4 := p_x_space_restriction_rec.attribute4;
1051 END IF;
1052 --
1053 IF p_x_space_restriction_rec.attribute5 = FND_API.G_MISS_CHAR
1054 THEN
1055 l_space_restriction_rec.attribute5 := NULL;
1056 ELSE
1057 l_space_restriction_rec.attribute5 := p_x_space_restriction_rec.attribute5;
1058 END IF;
1059 --
1060 IF p_x_space_restriction_rec.attribute6 = FND_API.G_MISS_CHAR
1061 THEN
1062 l_space_restriction_rec.attribute6 := NULL;
1063 ELSE
1064 l_space_restriction_rec.attribute6 := p_x_space_restriction_rec.attribute6;
1065 END IF;
1066 --
1067 IF p_x_space_restriction_rec.attribute7 = FND_API.G_MISS_CHAR
1068 THEN
1069 l_space_restriction_rec.attribute7 := NULL;
1070 ELSE
1071 l_space_restriction_rec.attribute7 := p_x_space_restriction_rec.attribute7;
1072 END IF;
1073 --
1074 IF p_x_space_restriction_rec.attribute8 = FND_API.G_MISS_CHAR
1075 THEN
1076 l_space_restriction_rec.attribute8 := NULL;
1077 ELSE
1078 l_space_restriction_rec.attribute8 := p_x_space_restriction_rec.attribute8;
1079 END IF;
1080 --
1081 IF p_x_space_restriction_rec.attribute9 = FND_API.G_MISS_CHAR
1082 THEN
1083 l_space_restriction_rec.attribute9 := NULL;
1084 ELSE
1085 l_space_restriction_rec.attribute9 := p_x_space_restriction_rec.attribute9;
1086 END IF;
1087 --
1088 IF p_x_space_restriction_rec.attribute10 = FND_API.G_MISS_CHAR
1089 THEN
1090 l_space_restriction_rec.attribute10 := NULL;
1091 ELSE
1092 l_space_restriction_rec.attribute10 := p_x_space_restriction_rec.attribute10;
1093 END IF;
1094 --
1095 IF p_x_space_restriction_rec.attribute11 = FND_API.G_MISS_CHAR
1096 THEN
1097 l_space_restriction_rec.attribute11 := NULL;
1098 ELSE
1099 l_space_restriction_rec.attribute11 := p_x_space_restriction_rec.attribute11;
1100 END IF;
1101 --
1102 IF p_x_space_restriction_rec.attribute12 = FND_API.G_MISS_CHAR
1103 THEN
1104 l_space_restriction_rec.attribute12 := NULL;
1105 ELSE
1106 l_space_restriction_rec.attribute12 := p_x_space_restriction_rec.attribute12;
1107 END IF;
1108 --
1109 IF p_x_space_restriction_rec.attribute13 = FND_API.G_MISS_CHAR
1110 THEN
1111 l_space_restriction_rec.attribute13 := NULL;
1112 ELSE
1113 l_space_restriction_rec.attribute13 := p_x_space_restriction_rec.attribute13;
1114 END IF;
1115 --
1116 IF p_x_space_restriction_rec.attribute14 = FND_API.G_MISS_CHAR
1117 THEN
1118 l_space_restriction_rec.attribute14 := NULL;
1119 ELSE
1120 l_space_restriction_rec.attribute14 := p_x_space_restriction_rec.attribute14;
1121 END IF;
1122 --
1123 IF p_x_space_restriction_rec.attribute15 = FND_API.G_MISS_CHAR
1124 THEN
1125 l_space_restriction_rec.attribute15 := NULL;
1126 ELSE
1127 l_space_restriction_rec.attribute15 := p_x_space_restriction_rec.attribute15;
1128 END IF;
1129 END IF;
1130 --Assign it to local variable
1131 --
1132 OPEN check_duplicate_cur(p_x_space_restriction_rec.space_id,
1133 l_start_date,
1134 l_end_date);
1135 FETCH check_duplicate_cur INTO l_dup_id;
1136 CLOSE check_duplicate_cur;
1137 IF l_dup_id IS NOT NULL
1138 THEN
1139 Fnd_Message.SET_NAME('AHL','AHL_APPR_RECORD_EXISTS');
1140 Fnd_Msg_Pub.ADD;
1141 END IF;
1142
1143 --Standard check to count messages
1144 l_msg_count := Fnd_Msg_Pub.count_msg;
1145
1146 IF l_msg_count > 0 THEN
1147 X_msg_count := l_msg_count;
1148 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1149 RAISE Fnd_Api.G_EXC_ERROR;
1150 END IF;
1151
1152 ----------------------------DML Operation---------------------------------
1153 --Call table handler generated package to insert a record
1154 AHL_SPACE_UNAVAILABLE_PKG.INSERT_ROW (
1155 X_ROWID => l_rowid,
1156 X_SPACE_UNAVAILABILITY_ID => l_space_unavaialability_id,
1157 X_SPACE_ID => p_x_space_restriction_rec.space_id,
1158 X_START_DATE => p_x_space_restriction_rec.start_date,
1159 X_END_DATE => p_x_space_restriction_rec.end_date,
1160 X_OBJECT_VERSION_NUMBER => 1,
1161 X_ATTRIBUTE_CATEGORY => l_space_restriction_rec.attribute_category,
1162 X_ATTRIBUTE1 => l_space_restriction_rec.attribute1,
1163 X_ATTRIBUTE2 => l_space_restriction_rec.attribute2,
1164 X_ATTRIBUTE3 => l_space_restriction_rec.attribute3,
1165 X_ATTRIBUTE4 => l_space_restriction_rec.attribute4,
1166 X_ATTRIBUTE5 => l_space_restriction_rec.attribute5,
1167 X_ATTRIBUTE6 => l_space_restriction_rec.attribute6,
1168 X_ATTRIBUTE7 => l_space_restriction_rec.attribute7,
1169 X_ATTRIBUTE8 => l_space_restriction_rec.attribute8,
1170 X_ATTRIBUTE9 => l_space_restriction_rec.attribute9,
1171 X_ATTRIBUTE10 => l_space_restriction_rec.attribute10,
1172 X_ATTRIBUTE11 => l_space_restriction_rec.attribute11,
1173 X_ATTRIBUTE12 => l_space_restriction_rec.attribute12,
1174 X_ATTRIBUTE13 => l_space_restriction_rec.attribute13,
1175 X_ATTRIBUTE14 => l_space_restriction_rec.attribute14,
1176 X_ATTRIBUTE15 => l_space_restriction_rec.attribute15,
1177 X_DESCRIPTION => l_space_restriction_rec.description,
1178 X_CREATION_DATE => SYSDATE,
1179 X_CREATED_BY => Fnd_Global.USER_ID,
1180 X_LAST_UPDATE_DATE => SYSDATE,
1181 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1182 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1183
1184 p_x_space_restriction_rec.space_unavailability_id := l_space_unavaialability_id;
1185 ---------------------------End of Body---------------------------------------
1186 --Standard check to count messages
1187 l_msg_count := Fnd_Msg_Pub.count_msg;
1188
1189 IF l_msg_count > 0 THEN
1190 X_msg_count := l_msg_count;
1191 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1192 RAISE Fnd_Api.G_EXC_ERROR;
1193 END IF;
1194
1195 --Standard check for commit
1196 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1197 COMMIT;
1198 END IF;
1199 -- Debug info
1200 IF G_DEBUG='Y' THEN
1201 Ahl_Debug_Pub.debug( 'End of private api Create Space Restriction','+SUAVL+');
1202 -- Check if API is called in debug mode. If yes, disable debug.
1203 Ahl_Debug_Pub.disable_debug;
1204 END IF;
1205
1206 EXCEPTION
1207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1208 ROLLBACK TO create_space_restriction;
1209 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1211 p_count => x_msg_count,
1212 p_data => x_msg_data);
1213 IF G_DEBUG='Y' THEN
1214 AHL_DEBUG_PUB.log_app_messages (
1215 x_msg_count, x_msg_data, 'ERROR' );
1216 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Create Space Restriction','+SUAVL+');
1217 -- Check if API is called in debug mode. If yes, disable debug.
1218 AHL_DEBUG_PUB.disable_debug;
1219 END IF;
1220 WHEN FND_API.G_EXC_ERROR THEN
1221 ROLLBACK TO create_space_restriction;
1222 X_return_status := FND_API.G_RET_STS_ERROR;
1223 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1224 p_count => x_msg_count,
1225 p_data => X_msg_data);
1226 IF G_DEBUG='Y' THEN
1227 -- Debug info.
1228 AHL_DEBUG_PUB.log_app_messages (
1229 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1230 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Create Space Restriction','+SUAVL+');
1231 -- Check if API is called in debug mode. If yes, disable debug.
1232 AHL_DEBUG_PUB.disable_debug;
1233 END IF;
1234
1235 WHEN OTHERS THEN
1236 ROLLBACK TO create_space_restriction;
1237 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1239 THEN
1240 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_SPACE_UNAVL_PVT',
1241 p_procedure_name => 'CREATE_SPACE_RESTRICTION',
1242 p_error_text => SUBSTR(SQLERRM,1,240));
1243 END IF;
1244 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1245 p_count => x_msg_count,
1246 p_data => X_msg_data);
1247 IF G_DEBUG='Y' THEN
1248 -- Debug info.
1249 AHL_DEBUG_PUB.log_app_messages (
1250 x_msg_count, x_msg_data, 'SQL ERROR' );
1251 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Create Space Restriction','+SUAVL+');
1252 -- Check if API is called in debug mode. If yes, disable debug.
1253 AHL_DEBUG_PUB.disable_debug;
1254 END IF;
1255
1256 END Create_Space_Restriction;
1257
1258
1259
1260 --------------------------------------------------------------------
1261 -- PROCEDURE
1262 -- Update_Space_Restriction
1263 --
1264 -- PURPOSE
1265 -- Update Space Restriction Record.
1266 --
1267 -- PARAMETERS
1268 -- p_space_restriction_rec: the record representing AHL_SPACE_UNAVAILABLE_VL
1269 --
1270 -- NOTES
1271 --------------------------------------------------------------------
1272 PROCEDURE Update_Space_Restriction (
1273 p_api_version IN NUMBER,
1274 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1275 p_commit IN VARCHAR2 := FND_API.g_false,
1276 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1277 p_module_type IN VARCHAR2 := 'JSP',
1278 p_space_restriction_rec IN ahl_appr_space_unavl_pub.Space_Restriction_Rec,
1279 x_return_status OUT NOCOPY VARCHAR2,
1280 x_msg_count OUT NOCOPY NUMBER,
1281 x_msg_data OUT NOCOPY VARCHAR2
1282 )
1283 IS
1284 --
1285 CURSOR check_duplicate_cur (c_space_id IN NUMBER,
1286 c_start_date IN DATE,
1287 c_end_date IN DATE)
1288 IS
1289 SELECT space_id
1290 FROM AHL_SPACE_UNAVAILABLE_VL
1291 WHERE space_id = c_space_id
1292 AND trunc(start_date) = c_start_date
1293 AND trunc(end_date) = c_end_date;
1294 --
1295 CURSOR get_space_capb_cur (c_space_unavabl_id IN NUMBER)
1296 IS
1297 SELECT start_date,end_date
1298 FROM AHL_SPACE_UNAVAILABLE_VL
1299 WHERE space_unavailability_id = c_space_unavabl_id;
1300 --
1301 CURSOR get_space_detail_cur (c_space_id IN NUMBER)
1302 IS
1303 SELECT organization_id,bom_department_id
1304 FROM ahl_spaces_b
1305 WHERE space_id = c_space_id;
1306
1307 --
1308 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SPACE_RESTRICTION';
1309 l_api_version CONSTANT NUMBER := 1.0;
1310 l_msg_count NUMBER;
1311 l_return_status VARCHAR2(1);
1312 l_msg_data VARCHAR2(2000);
1313 l_description VARCHAR2(2000);
1314 l_dummy NUMBER;
1315 l_rowid VARCHAR2(30);
1316 l_organization_id NUMBER;
1317 l_department_id NUMBER;
1318 l_space_id NUMBER;
1319 l_dup_id NUMBER;
1320 l_start_date DATE;
1321 l_end_date DATE;
1322 l_space_unavaialability_id NUMBER;
1323 l_space_restriction_rec Space_Restriction_Rec;
1324 l_Aspace_restriction_rec Space_Restriction_Rec;
1325
1326 BEGIN
1327
1328
1329 --------------------Initialize ----------------------------------
1330 -- Standard Start of API savepoint
1331 SAVEPOINT update_space_restriction;
1332 -- Check if API is called in debug mode. If yes, enable debug.
1333 IF G_DEBUG='Y' THEN
1334 AHL_DEBUG_PUB.enable_debug;
1335 -- Debug info.
1336 AHL_DEBUG_PUB.debug( 'enter ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
1337 END IF;
1338 -- Standard call to check for call compatibility.
1339 IF FND_API.to_boolean(p_init_msg_list)
1340 THEN
1341 FND_MSG_PUB.initialize;
1342 END IF;
1343 -- Initialize API return status to success
1344 x_return_status := FND_API.G_RET_STS_SUCCESS;
1345 -- Initialize message list if p_init_msg_list is set to TRUE.
1346 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1347 p_api_version,
1348 l_api_name,G_PKG_NAME)
1349 THEN
1350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1351 END IF;
1352 --------------------Value OR ID conversion---------------------------
1353 --Assign to local variable
1354 Assign_Space_Restric_Rec (
1355 p_space_restriction_rec => p_space_restriction_rec,
1356 x_space_restriction_rec => l_Space_restriction_rec);
1357 --Start API Body
1358 IF p_module_type = 'JSP'
1359 THEN
1360 l_space_restriction_rec.organization_id := null;
1361 l_space_restriction_rec.department_id := null;
1362 END IF;
1363 -- Convert Space name to space id
1364 IF (p_space_restriction_rec.space_name IS NOT NULL AND
1365 p_space_restriction_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1366 (l_space_restriction_rec.space_id IS NOT NULL AND
1367 l_space_restriction_rec.space_id <> FND_API.G_MISS_NUM) THEN
1368
1369 Check_space_name_Or_Id
1370 (p_space_id => l_space_restriction_rec.space_id,
1371 p_space_name => p_space_restriction_rec.space_name,
1372 x_space_id => l_space_id,
1373 x_return_status => l_return_status,
1374 x_error_msg_code => l_msg_data);
1375
1376 IF NVL(l_return_status,'x') <> 'S'
1377 THEN
1378 Fnd_Message.SET_NAME('AHL','AHL_APPR_SPACE_NOT_EXISTS');
1379 Fnd_Message.SET_TOKEN('ORGID',p_space_restriction_rec.space_name);
1380 Fnd_Msg_Pub.ADD;
1381 END IF;
1382 END IF;
1383 -- Get organization , department id
1384 OPEN get_space_detail_cur (l_space_id);
1385 FETCH get_space_detail_cur INTO l_space_restriction_rec.organization_id,
1386 l_space_restriction_rec.department_id;
1387 CLOSE get_space_detail_cur;
1388
1389 --Assign the returned value
1390 l_space_restriction_rec.space_id := l_space_id;
1391 IF G_DEBUG='Y' THEN
1392 AHL_DEBUG_PUB.debug( 'start date'||l_space_restriction_rec.start_date);
1393 AHL_DEBUG_PUB.debug( 'end date'||l_space_restriction_rec.end_date);
1394 AHL_DEBUG_PUB.debug( 'org id'||l_space_restriction_rec.organization_id);
1395 AHL_DEBUG_PUB.debug( 'dept id'||l_space_restriction_rec.department_id);
1396 END IF;
1397 --------------------------------Validation ---------------------------
1398 -- get existing values and compare
1399 Complete_Space_Restric_Rec (
1400 p_space_restriction_rec => l_space_restriction_rec,
1401 x_space_restriction_rec => l_Aspace_restriction_rec);
1402 -- Call Validate space rec attributes
1403 Validate_Space_Restriction
1404 ( p_api_version => l_api_version,
1405 p_init_msg_list => p_init_msg_list,
1406 p_validation_level => p_validation_level,
1407 p_space_restriction_rec => l_Aspace_restriction_rec,
1408 x_return_status => l_return_status,
1409 x_msg_count => l_msg_count,
1410 x_msg_data => l_msg_data );
1411
1412 -- Check for Duplicate Records
1413 OPEN check_duplicate_cur(l_Aspace_restriction_rec.space_id,
1414 trunc(p_space_restriction_rec.start_date),
1415 trunc(p_space_restriction_rec.end_date));
1416 FETCH check_duplicate_cur INTO l_dup_id;
1417 CLOSE check_duplicate_cur;
1418 --
1419 IF l_dup_id IS NOT NULL THEN
1420 OPEN get_space_capb_cur(p_space_restriction_rec.space_unavailability_id);
1421 FETCH get_space_capb_cur INTO l_start_date,l_end_date;
1422 CLOSE get_space_capb_cur;
1423 --
1424 IF (p_space_restriction_rec.start_date <> l_start_date OR
1425 p_space_restriction_rec.end_date <> l_end_date ) THEN
1426 Fnd_Message.SET_NAME('AHL','AHL_APPR_RECORD_EXISTS');
1427 Fnd_Msg_Pub.ADD;
1428 END IF;
1429 END IF;
1430 --Standard check to count messages
1431 l_msg_count := Fnd_Msg_Pub.count_msg;
1432
1433 IF l_msg_count > 0 THEN
1434 X_msg_count := l_msg_count;
1435 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1436 RAISE Fnd_Api.G_EXC_ERROR;
1437 END IF;
1438
1439
1440 ----------------------------DML Operation---------------------------------
1441 --Call table handler generated package to update a record
1442 AHL_SPACE_UNAVAILABLE_PKG.UPDATE_ROW
1443 (
1444 X_SPACE_UNAVAILABILITY_ID => l_Aspace_restriction_rec.space_unavailability_id,
1445 X_SPACE_ID => l_Aspace_restriction_rec.space_id,
1446 X_START_DATE => l_Aspace_restriction_rec.start_date,
1447 X_END_DATE => l_Aspace_restriction_rec.end_date,
1448 X_OBJECT_VERSION_NUMBER => l_Aspace_restriction_rec.object_version_number+1,
1449 X_ATTRIBUTE_CATEGORY => l_Aspace_restriction_rec.attribute_category,
1450 X_ATTRIBUTE1 => l_Aspace_restriction_rec.attribute1,
1451 X_ATTRIBUTE2 => l_Aspace_restriction_rec.attribute2,
1452 X_ATTRIBUTE3 => l_Aspace_restriction_rec.attribute3,
1453 X_ATTRIBUTE4 => l_Aspace_restriction_rec.attribute4,
1454 X_ATTRIBUTE5 => l_Aspace_restriction_rec.attribute5,
1455 X_ATTRIBUTE6 => l_Aspace_restriction_rec.attribute6,
1456 X_ATTRIBUTE7 => l_Aspace_restriction_rec.attribute7,
1457 X_ATTRIBUTE8 => l_Aspace_restriction_rec.attribute8,
1458 X_ATTRIBUTE9 => l_Aspace_restriction_rec.attribute9,
1459 X_ATTRIBUTE10 => l_Aspace_restriction_rec.attribute10,
1460 X_ATTRIBUTE11 => l_Aspace_restriction_rec.attribute11,
1461 X_ATTRIBUTE12 => l_Aspace_restriction_rec.attribute12,
1462 X_ATTRIBUTE13 => l_Aspace_restriction_rec.attribute13,
1463 X_ATTRIBUTE14 => l_Aspace_restriction_rec.attribute14,
1464 X_ATTRIBUTE15 => l_Aspace_restriction_rec.attribute15,
1465 X_DESCRIPTION => l_Aspace_restriction_rec.description,
1466 X_LAST_UPDATE_DATE => SYSDATE,
1467 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1468 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1469
1470
1471 ---------------------------End of Body---------------------------------------
1472 --Standard check to count messages
1473 l_msg_count := Fnd_Msg_Pub.count_msg;
1474
1475 IF l_msg_count > 0 THEN
1476 X_msg_count := l_msg_count;
1477 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1478 RAISE Fnd_Api.G_EXC_ERROR;
1479 END IF;
1480
1481 --Standard check for commit
1482 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1483 COMMIT;
1484 END IF;
1485 IF G_DEBUG='Y' THEN
1486 -- Debug info
1487 Ahl_Debug_Pub.debug( 'End of private api Update Space Restriction','+SUAVL+');
1488 -- Check if API is called in debug mode. If yes, disable debug.
1489 Ahl_Debug_Pub.disable_debug;
1490 END IF;
1491
1492 EXCEPTION
1493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1494 ROLLBACK TO update_space_restriction;
1495 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1496 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1497 p_count => x_msg_count,
1498 p_data => x_msg_data);
1499 IF G_DEBUG='Y' THEN
1500 AHL_DEBUG_PUB.log_app_messages (
1501 x_msg_count, x_msg_data, 'ERROR' );
1502 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
1503 -- Check if API is called in debug mode. If yes, disable debug.
1504 AHL_DEBUG_PUB.disable_debug;
1505 END IF;
1506
1507 WHEN FND_API.G_EXC_ERROR THEN
1508 ROLLBACK TO update_space_restriction;
1509 X_return_status := FND_API.G_RET_STS_ERROR;
1510 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1511 p_count => x_msg_count,
1512 p_data => X_msg_data);
1513 IF G_DEBUG='Y' THEN
1514 -- Debug info.
1515 AHL_DEBUG_PUB.log_app_messages (
1516 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1517 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
1518 -- Check if API is called in debug mode. If yes, disable debug.
1519 AHL_DEBUG_PUB.disable_debug;
1520 END IF;
1521
1522 WHEN OTHERS THEN
1523 ROLLBACK TO update_space_restriction;
1524 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1526 THEN
1527 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_SPACE_UNAVL_PVT',
1528 p_procedure_name => 'UPDATE_SPACE_RESTRICTION',
1529 p_error_text => SUBSTR(SQLERRM,1,240));
1530 END IF;
1531 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1532 p_count => x_msg_count,
1533 p_data => X_msg_data);
1534 IF G_DEBUG='Y' THEN
1535 -- Debug info.
1536 AHL_DEBUG_PUB.log_app_messages (
1537 x_msg_count, x_msg_data, 'SQL ERROR' );
1538 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
1539 -- Check if API is called in debug mode. If yes, disable debug.
1540 AHL_DEBUG_PUB.disable_debug;
1541 END IF;
1542 END Update_Space_Restriction;
1543
1544 --------------------------------------------------------------------
1545 -- PROCEDURE
1546 -- Delete_Space_Restriction
1547 --
1548 -- PURPOSE
1549 -- Delete Space Restriction Record.
1550 --
1551 -- PARAMETERS
1552 --
1553 -- ISSUES
1554 --
1555 -- NOTES
1556 -- 1. Raise exception if the object_version_number doesn't match.
1557 --------------------------------------------------------------------
1558 PROCEDURE Delete_Space_Restriction (
1559 p_api_version IN NUMBER,
1560 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1561 p_commit IN VARCHAR2 := FND_API.g_false,
1562 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1563 p_space_restriction_rec IN ahl_appr_space_unavl_pub.Space_Restriction_Rec,
1564 x_return_status OUT NOCOPY VARCHAR2,
1565 x_msg_count OUT NOCOPY NUMBER,
1566 x_msg_data OUT NOCOPY VARCHAR2
1567
1568 )
1569 IS
1570 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SPACE_RESTRICTION';
1571 l_api_version CONSTANT NUMBER := 1.0;
1572 l_msg_count NUMBER;
1573 l_return_status VARCHAR2(1);
1574 l_msg_data VARCHAR2(2000);
1575 l_dummy NUMBER;
1576 l_space_unavailability_id NUMBER;
1577 l_object_version_number NUMBER;
1578
1579 CURSOR c_space_restrict_cur
1580 (c_space_unavailability_id IN NUMBER)
1581 IS
1582 SELECT space_unavailability_id,object_version_number
1583 FROM ahl_space_unavailable_vl
1584 WHERE space_unavailability_id = c_space_unavailability_id
1585 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1586
1587 BEGIN
1588 --------------------Initialize ----------------------------------
1589 -- Standard Start of API savepoint
1590 SAVEPOINT delete_space_restriction;
1591 -- Check if API is called in debug mode. If yes, enable debug.
1592 IF G_DEBUG='Y' THEN
1593 AHL_DEBUG_PUB.enable_debug;
1594 END IF;
1595 -- Debug info.
1596 IF G_DEBUG='Y' THEN
1597 AHL_DEBUG_PUB.debug( 'enter ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
1598 END IF;
1599 -- Standard call to check for call compatibility.
1600 IF FND_API.to_boolean(p_init_msg_list)
1601 THEN
1602 FND_MSG_PUB.initialize;
1603 END IF;
1604 -- Initialize API return status to success
1605 x_return_status := FND_API.G_RET_STS_SUCCESS;
1606 -- Initialize message list if p_init_msg_list is set to TRUE.
1607 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1608 p_api_version,
1609 l_api_name,G_PKG_NAME)
1610 THEN
1611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1612 END IF;
1613 -----------------------Start of API Body-----------------------------
1614 -- Check for Record exists
1615 OPEN c_space_restrict_cur(p_space_restriction_rec.space_unavailability_id);
1616 FETCH c_space_restrict_cur INTO l_space_unavailability_id,
1617 l_object_version_number;
1618 IF c_space_restrict_cur%NOTFOUND THEN
1619 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1620 FND_MESSAGE.set_name('AHL', 'AHL_APPR_RECORD_NOT_FOUND');
1621 FND_MSG_PUB.add;
1622 END IF;
1623 CLOSE c_space_restrict_cur;
1624 RAISE FND_API.g_exc_error;
1625 END IF;
1626 CLOSE c_space_restrict_cur;
1627 --Check for object version number
1628 IF l_object_version_number <> p_space_restriction_rec.object_version_number
1629 THEN
1630 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1631 FND_MSG_PUB.add;
1632 RAISE FND_API.g_exc_error;
1633 END IF;
1634 -------------------Call Table handler generated procedure------------
1635 AHL_SPACE_UNAVAILABLE_PKG.DELETE_ROW (
1636 X_SPACE_UNAVAILABILITY_ID => l_space_unavailability_id
1637 );
1638 ---------------------------End of Body---------------------------------------
1639 --Standard check to count messages
1640 l_msg_count := Fnd_Msg_Pub.count_msg;
1641
1642 IF l_msg_count > 0 THEN
1643 X_msg_count := l_msg_count;
1644 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1645 RAISE Fnd_Api.G_EXC_ERROR;
1646 END IF;
1647
1648 --Standard check for commit
1649 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1650 COMMIT;
1651 END IF;
1652 IF G_DEBUG='Y' THEN
1653 -- Debug info
1654 Ahl_Debug_Pub.debug( 'End of private api Delete Space Restriction','+SUAVL+');
1655 -- Check if API is called in debug mode. If yes, disable debug.
1656 Ahl_Debug_Pub.disable_debug;
1657 END IF;
1658
1659 EXCEPTION
1660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1661 ROLLBACK TO delete_space_restriction;
1662 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1663 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1664 p_count => x_msg_count,
1665 p_data => x_msg_data);
1666 IF G_DEBUG='Y' THEN
1667 AHL_DEBUG_PUB.log_app_messages (
1668 x_msg_count, x_msg_data, 'ERROR' );
1669 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
1670 -- Check if API is called in debug mode. If yes, disable debug.
1671 AHL_DEBUG_PUB.disable_debug;
1672 END IF;
1673 WHEN FND_API.G_EXC_ERROR THEN
1674 ROLLBACK TO delete_space_restriction;
1675 X_return_status := FND_API.G_RET_STS_ERROR;
1676 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1677 p_count => x_msg_count,
1678 p_data => X_msg_data);
1679 IF G_DEBUG='Y' THEN
1680 -- Debug info.
1681 AHL_DEBUG_PUB.log_app_messages (
1682 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1683 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
1684 -- Check if API is called in debug mode. If yes, disable debug.
1685 AHL_DEBUG_PUB.disable_debug;
1686 END IF;
1687
1688 WHEN OTHERS THEN
1689 ROLLBACK TO delete_space_restriction;
1690 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1692 THEN
1693 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_SPACE_UNAVL_PVT',
1694 p_procedure_name => 'DELETE_SPACE_RESTRICTION',
1695 p_error_text => SUBSTR(SQLERRM,1,240));
1696 END IF;
1697 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1698 p_count => x_msg_count,
1699 p_data => X_msg_data);
1700 IF G_DEBUG='Y' THEN
1701 -- Debug info.
1702 AHL_DEBUG_PUB.log_app_messages (
1703 x_msg_count, x_msg_data, 'SQL ERROR' );
1704 AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
1705 -- Check if API is called in debug mode. If yes, disable debug.
1706 AHL_DEBUG_PUB.disable_debug;
1707 END IF;
1708
1709 END Delete_Space_Restriction;
1710
1711 END AHL_APPR_SPACE_UNAVL_PVT;