[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_SPACE_CAPB_PVT
Source
1 PACKAGE BODY AHL_LTP_SPACE_CAPB_PVT AS
2 /* $Header: AHLVSPCB.pls 115.10 2002/12/04 19:12:08 ssurapan noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_SPACE_CAPB_PVT';
5 G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
6 --
7 -----------------------------------------------------------
8 -- PACKAGE
9 -- AHL_LTP_SPACE_CAPB_PVT
10 --
11 -- PURPOSE
12 -- This package is a Private API for managing Space and space capabilities information in
13 -- Advanced Services Online. It contains specification for pl/sql records and tables
14 --
15 -- AHL_SPACE_UNAVIALABLE_VL:
16 -- Create_Space (see below for specification)
17 -- Update_Space (see below for specification)
18 -- Delete_Space (see below for specification)
19 -- Validate_Space (see below for specification)
20 -- Create_Space_capblts (see below for specification)
21 -- Update_Space_capblts (see below for specification)
22 -- Delete_Space_capblts (see below for specification)
23 -- Validate_Space_capblts (see below for specification)
24 --
25 --
26 -- NOTES
27 --
28 --
29 -- HISTORY
30 -- 19-Apr-2002 ssurapan Created
31
32 -------------------------------------------------------------
33 -- procedure name: Check_lookup_name_Or_Id(private procedure)
34 -- description : used to retrieve lookup code
35 --
36 --------------------------------------------------------------
37
38 PROCEDURE Check_lookup_name_Or_Id
39 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
40 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
41 p_meaning IN FND_LOOKUPS.meaning%TYPE,
42 p_check_id_flag IN VARCHAR2,
43 x_lookup_code OUT NOCOPY VARCHAR2,
44 x_return_status OUT NOCOPY VARCHAR2)
45 IS
46
47 BEGIN
48 --
49 IF (p_lookup_code IS NOT NULL) THEN
50 IF (p_check_id_flag = 'Y') THEN
51 SELECT lookup_code INTO x_lookup_code
52 FROM FND_LOOKUP_VALUES_VL
53 WHERE lookup_type = p_lookup_type
54 AND lookup_code = p_lookup_code
55 AND SYSDATE BETWEEN start_date_active
56 AND NVL(end_date_active,SYSDATE);
57 ELSE
58 x_lookup_code := p_lookup_code;
59 END IF;
60 ELSE
61 SELECT lookup_code INTO x_lookup_code
62 FROM FND_LOOKUP_VALUES_VL
63 WHERE lookup_type = p_lookup_type
64 AND meaning = p_meaning
65 AND SYSDATE BETWEEN start_date_active
66 AND NVL(end_date_active,SYSDATE);
67 END IF;
68 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
69
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 x_return_status := Fnd_Api.G_RET_STS_ERROR;
73 WHEN TOO_MANY_ROWS THEN
74 x_return_status := Fnd_Api.G_RET_STS_ERROR;
75 WHEN OTHERS THEN
76 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
77 RAISE;
78 END;
79
80
81 --------------------------------------------------------------------
82 -- PROCEDURE
83 -- CHECK_ORG_NAME_OR_ID
84 --
85 -- PURPOSE
86 -- Converts Org Name to ID or Vice versa
87 --
88 -- PARAMETERS
89 --
90 -- NOTES
91 --------------------------------------------------------------------
92 PROCEDURE Check_org_name_Or_Id
93 (p_organization_id IN NUMBER,
94 p_org_name IN VARCHAR2,
95 x_organization_id OUT NOCOPY NUMBER,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_error_msg_code OUT NOCOPY VARCHAR2
98 )
99 IS
100 BEGIN
101 IF (p_organization_id IS NOT NULL)
102 THEN
103 SELECT organization_id
104 INTO x_organization_id
105 FROM HR_ALL_ORGANIZATION_UNITS
106 WHERE organization_id = p_organization_id;
107 ELSE
108 SELECT organization_id
109 INTO x_organization_id
110 FROM HR_ALL_ORGANIZATION_UNITS
111 WHERE NAME = p_org_name;
112 END IF;
113 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
117 x_error_msg_code:= 'AHL_LTP_ORG_ID_NOT_EXISTS';
118 WHEN TOO_MANY_ROWS THEN
119 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
120 x_error_msg_code:= 'AHL_LTP_ORG_ID_NOT_EXISTS';
121 WHEN OTHERS THEN
122 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
123 RAISE;
124 END Check_org_name_Or_Id;
125 --------------------------------------------------------------------
126 -- PROCEDURE
127 -- CHECK_DEPT_DESC_OR_ID
128 --
129 -- PURPOSE
130 -- Converts Dept description to ID or Vice Versa
131 --
132 -- PARAMETERS
133 --
134 -- NOTES
135 --------------------------------------------------------------------
136 PROCEDURE Check_dept_desc_Or_Id
137 (p_organization_id IN NUMBER,
138 p_org_name IN VARCHAR2,
139 p_department_id IN NUMBER,
140 p_dept_description IN VARCHAR2,
141 x_department_id OUT NOCOPY NUMBER,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_error_msg_code OUT NOCOPY VARCHAR2
144 )
145 IS
146 BEGIN
147
148 IF (p_department_id IS NOT NULL)
149 THEN
150 SELECT department_id
151 INTO x_department_id
152 FROM BOM_DEPARTMENTS
153 WHERE organization_id = p_organization_id
154 AND department_id = p_department_id;
155 END IF;
156 --
157 IF(p_dept_description IS NOT NULL) THEN
158 SELECT department_id
159 INTO x_department_id
160 FROM BOM_DEPARTMENTS
161 WHERE organization_id = p_organization_id
162 AND description = p_dept_description;
163 END IF;
164 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
168 x_error_msg_code:= 'AHL_LTP_DEPT_ID_NOT_EXIST';
169 WHEN TOO_MANY_ROWS THEN
170 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
171 x_error_msg_code:= 'AHL_LTP_DEPT_ID_NOT_EXIST';
172 WHEN OTHERS THEN
173 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
174 RAISE;
175 END Check_dept_desc_Or_Id;
176
177 --------------------------------------------------------------------
178 -- PROCEDURE
179 -- CHECK_SPACE_NAME_OR_ID
180 --
181 -- PURPOSE
182 -- Converts Space Name to ID or Vice versa
183 --
184 -- PARAMETERS
185 --
186 -- NOTES
187 --------------------------------------------------------------------
188 PROCEDURE Check_space_name_Or_Id
189 (p_space_id IN NUMBER,
190 p_space_name IN VARCHAR2,
191 x_space_id OUT NOCOPY NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_error_msg_code OUT NOCOPY VARCHAR2
194 )
195 IS
196 BEGIN
197 IF (p_space_id IS NOT NULL)
198 THEN
199 SELECT space_id
200 INTO x_space_id
201 FROM AHL_SPACES_VL
202 WHERE space_id = p_space_id;
203 ELSE
204 SELECT space_id
205 INTO x_space_id
206 FROM AHL_SPACES_VL
207 WHERE SPACE_NAME = p_space_name;
208 END IF;
209 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
213 x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
214 WHEN TOO_MANY_ROWS THEN
215 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
216 x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
217 WHEN OTHERS THEN
218 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
219 RAISE;
220 END Check_space_name_Or_Id;
221
222 ---------------------------------------------------------------------
223 -- PROCEDURE
224 -- Assign_Space_Rec
225 --
226 ---------------------------------------------------------------------
227 PROCEDURE Assign_Space_Rec (
228 p_space_rec IN AHL_LTP_SPACE_CAPB_PUB.Space_rec,
229 x_space_rec OUT NOCOPY Space_rec
230 )
231 IS
232
233 BEGIN
234 x_space_rec.space_id := p_space_rec.space_id;
235 x_space_rec.organization_id := p_space_rec.organization_id;
236 x_space_rec.org_name := p_space_rec.org_name;
237 x_space_rec.department_id := p_space_rec.department_id;
238 x_space_rec.dept_Description := p_space_rec.dept_description;
239 x_space_rec.space_name := p_space_rec.space_name;
240 x_space_rec.space_category_code := p_space_rec.space_category_code;
241 x_space_rec.space_category_mean := p_space_rec.space_category_mean;
242 x_space_rec.inactive_flag_code := p_space_rec.inactive_flag_code;
243 x_space_rec.inactive_flag_mean := p_space_rec.inactive_flag_mean;
244 x_space_rec.object_version_number := p_space_rec.object_version_number;
245 x_space_rec.attribute_category := p_space_rec.attribute_category;
246 x_space_rec.attribute1 := p_space_rec.attribute1;
247 x_space_rec.attribute2 := p_space_rec.attribute2;
248 x_space_rec.attribute3 := p_space_rec.attribute3;
249 x_space_rec.attribute4 := p_space_rec.attribute4;
250 x_space_rec.attribute5 := p_space_rec.attribute5;
251 x_space_rec.attribute6 := p_space_rec.attribute6;
252 x_space_rec.attribute7 := p_space_rec.attribute7;
253 x_space_rec.attribute8 := p_space_rec.attribute8;
254 x_space_rec.attribute9 := p_space_rec.attribute9;
255 x_space_rec.attribute10 := p_space_rec.attribute10;
256 x_space_rec.attribute11 := p_space_rec.attribute11;
257 x_space_rec.attribute12 := p_space_rec.attribute12;
258 x_space_rec.attribute13 := p_space_rec.attribute13;
259 x_space_rec.attribute14 := p_space_rec.attribute14;
260 x_space_rec.attribute15 := p_space_rec.attribute15;
261
262 END Assign_Space_Rec;
263
264 ---------------------------------------------------------------------
265 -- PROCEDURE
266 -- Complete_Space_Rec
267 --
268 ---------------------------------------------------------------------
269 PROCEDURE Complete_Space_Rec (
270 p_space_rec IN Space_rec,
271 x_space_rec OUT NOCOPY Space_rec
272 )
273 IS
274 CURSOR c_space_rec
275 IS
276 SELECT ROW_ID,
277 SPACE_ID,
278 SPACE_NAME,
279 BOM_DEPARTMENT_ID,
280 ORGANIZATION_ID,
281 SPACE_CATEGORY,
282 INACTIVE_FLAG,
283 OBJECT_VERSION_NUMBER,
284 ATTRIBUTE_CATEGORY,
285 ATTRIBUTE1,
286 ATTRIBUTE2,
287 ATTRIBUTE3,
288 ATTRIBUTE4,
289 ATTRIBUTE5,
290 ATTRIBUTE6,
291 ATTRIBUTE7,
292 ATTRIBUTE8,
293 ATTRIBUTE9,
294 ATTRIBUTE10,
295 ATTRIBUTE11,
296 ATTRIBUTE12,
297 ATTRIBUTE13,
298 ATTRIBUTE14,
299 ATTRIBUTE15
300 FROM ahl_spaces_vl
301 WHERE space_id = p_space_rec.space_id;
302 --
303 CURSOR check_dup_name (c_space_name IN VARCHAR2,
304 c_dept_id IN NUMBER)
305 IS
306 SELECT space_id FROM
307 AHL_SPACES_VL
308 WHERE space_name = c_space_name
309 AND bom_department_id = c_dept_id;
310 -- This is the only exception for using %ROWTYPE.
311 l_space_rec c_space_rec%ROWTYPE;
312 l_dummy NUMBER;
313 BEGIN
314 x_space_rec := p_space_rec;
315 OPEN c_space_rec;
316 FETCH c_space_rec INTO l_space_rec;
317 IF c_space_rec%NOTFOUND THEN
318 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
319 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
320 FND_MSG_PUB.add;
321 RAISE Fnd_Api.G_EXC_ERROR;
322 END IF;
323 END IF;
324 CLOSE c_space_rec;
325 --
326 --Check for object version number
327 IF (l_space_rec.object_version_number <> p_space_rec.object_version_number)
328 THEN
329 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
330 Fnd_Msg_Pub.ADD;
331 RAISE Fnd_Api.G_EXC_ERROR;
332 END IF;
333 --
334 IF p_space_rec.space_name <> l_space_rec.space_name THEN
335 OPEN check_dup_name(p_space_rec.space_name,l_space_rec.bom_department_id);
336 FETCH check_dup_name INTO l_dummy;
337 CLOSE check_dup_name;
338 IF G_DEBUG='Y' THEN
339 AHL_DEBUG_PUB.debug( 'DUMMY'||l_dummy);
340 END IF;
341 IF l_dummy IS NOT NULL THEN
342 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_NAME_EXISTS');
343 Fnd_Msg_Pub.ADD;
344 RAISE Fnd_Api.G_EXC_ERROR;
345 END IF;
346 END IF;
347
348 -- BOM_DEPARTMENT ID
349 IF (
350 p_space_rec.department_id <> FND_API.g_miss_num) THEN
351 x_space_rec.department_id := p_space_rec.department_id;
352 ELSE
353 x_space_rec.department_id := l_space_rec.bom_department_id;
354 END IF;
355 -- ORGANIZATION_ID
356 IF (p_space_rec.organization_id <> FND_API.g_miss_num) THEN
357 x_space_rec.organization_id := p_space_rec.organization_id;
358 ELSE
359 x_space_rec.organization_id := l_space_rec.organization_id;
360 END IF;
361 -- SPACE_NAME
362 IF p_space_rec.space_name IS NULL
363 THEN
364 x_space_rec.space_name := l_space_rec.space_name;
365 ELSE
366 x_space_rec.space_name := p_space_rec.space_name;
367 END IF;
368 -- SPACE_CATEGORY
369 IF (p_space_rec.space_category_code <> FND_API.g_miss_char) THEN
370 x_space_rec.space_category_code := p_space_rec.space_category_code;
371 ELSE
372 x_space_rec.space_category_code := l_space_rec.space_category;
373 END IF;
374 -- INACTIVE_FLAG
375 IF (
376 p_space_rec.inactive_flag_code <> FND_API.g_miss_char) THEN
377 x_space_rec.inactive_flag_code := p_space_rec.inactive_flag_code;
378 ELSE
379 x_space_rec.inactive_flag_code := l_space_rec.inactive_flag;
380 END IF;
381 -- ATTRIBUTE CATEGORY
382 IF p_space_rec.attribute_category <> FND_API.g_miss_char THEN
383 x_space_rec.attribute_category := p_space_rec.attribute_category;
384 ELSE
385 x_space_rec.attribute_category := l_space_rec.attribute_category;
386 END IF;
387 -- ATTRIBUTE 1
388 IF p_space_rec.attribute1 <> FND_API.g_miss_char THEN
389 x_space_rec.attribute1 := l_space_rec.attribute1;
390 ELSE
391 x_space_rec.attribute1 := p_space_rec.attribute1;
392 END IF;
393 -- ATTRIBUTE 2
394 IF p_space_rec.attribute2 <> FND_API.g_miss_char THEN
395 x_space_rec.attribute2 := l_space_rec.attribute2;
396 ELSE
397 x_space_rec.attribute2 := p_space_rec.attribute2;
398 END IF;
399 -- ATTRIBUTE 3
400 IF p_space_rec.attribute3 <> FND_API.g_miss_char THEN
401 x_space_rec.attribute3 := l_space_rec.attribute3;
402 ELSE
403 x_space_rec.attribute3 := p_space_rec.attribute3;
404 END IF;
405 -- ATTRIBUTE 4
406 IF p_space_rec.attribute4 <> FND_API.g_miss_char THEN
407 x_space_rec.attribute4 := l_space_rec.attribute4;
408 ELSE
409 x_space_rec.attribute4 := p_space_rec.attribute4;
410 END IF;
411 -- ATTRIBUTE 5
412 IF p_space_rec.attribute5 <> FND_API.g_miss_char THEN
413 x_space_rec.attribute5 := l_space_rec.attribute5;
414 ELSE
415 x_space_rec.attribute5 := p_space_rec.attribute5;
416 END IF;
417 -- ATTRIBUTE 6
418 IF p_space_rec.attribute6 <> FND_API.g_miss_char THEN
419 x_space_rec.attribute6 := l_space_rec.attribute6;
420 ELSE
421 x_space_rec.attribute6 := p_space_rec.attribute6;
422 END IF;
423 -- ATTRIBUTE 7
424 IF p_space_rec.attribute7 <> FND_API.g_miss_char THEN
425 x_space_rec.attribute7 := l_space_rec.attribute7;
426 ELSE
427 x_space_rec.attribute7 := p_space_rec.attribute7;
428 END IF;
429 -- ATTRIBUTE 8
430 IF p_space_rec.attribute8 <> FND_API.g_miss_char THEN
431 x_space_rec.attribute8 := l_space_rec.attribute8;
432 ELSE
433 x_space_rec.attribute8 := p_space_rec.attribute8;
434 END IF;
435 -- ATTRIBUTE 9
436 IF p_space_rec.attribute9 <> FND_API.g_miss_char THEN
437 x_space_rec.attribute9 := l_space_rec.attribute9;
438 ELSE
439 x_space_rec.attribute9 := p_space_rec.attribute9;
440 END IF;
441 -- ATTRIBUTE 10
442 IF p_space_rec.attribute10 <> FND_API.g_miss_char THEN
443 x_space_rec.attribute10 := l_space_rec.attribute10;
444 ELSE
445 x_space_rec.attribute10 := p_space_rec.attribute10;
446 END IF;
447 -- ATTRIBUTE 11
448 IF p_space_rec.attribute11 <> FND_API.g_miss_char THEN
449 x_space_rec.attribute11 := l_space_rec.attribute11;
450 ELSE
451 x_space_rec.attribute11 := p_space_rec.attribute11;
452 END IF;
453 -- ATTRIBUTE 12
454 IF p_space_rec.attribute12 <> FND_API.g_miss_char THEN
455 x_space_rec.attribute12 := l_space_rec.attribute12;
456 ELSE
457 x_space_rec.attribute12 := p_space_rec.attribute12;
458 END IF;
459 -- ATTRIBUTE 13
460 IF p_space_rec.attribute13 <> FND_API.g_miss_char THEN
461 x_space_rec.attribute13 := l_space_rec.attribute13;
462 ELSE
463 x_space_rec.attribute13 := p_space_rec.attribute13;
464 END IF;
465 -- ATTRIBUTE 14
466 IF p_space_rec.attribute14 <> FND_API.g_miss_char THEN
467 x_space_rec.attribute14 := l_space_rec.attribute14;
468 ELSE
469 x_space_rec.attribute14 := p_space_rec.attribute14;
470 END IF;
471 -- ATTRIBUTE 15
472 IF p_space_rec.attribute15 <> FND_API.g_miss_char THEN
473 x_space_rec.attribute15 := l_space_rec.attribute15;
474 ELSE
475 x_space_rec.attribute15 := p_space_rec.attribute15;
476 END IF;
477
478
479 END Complete_Space_Rec;
480
481 ------------------------------------------------------------------------------
482 --
483 -- NAME
484 -- Validate_Space_Items
485 --
486 -- PURPOSE
487 -- This procedure is to validate Space attributes
488 -- End of Comments
489 -------------------------------------------------------------------------------
490 PROCEDURE Validate_Space_Items
491 ( p_space_rec IN space_rec,
492 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
493 x_return_status OUT NOCOPY VARCHAR2
494 ) IS
495 --
496 CURSOR space_name_cur (c_space_name IN VARCHAR2,
497 c_dept_id IN NUMBER)
498 IS
499 SELECT space_name
500 FROM AHL_SPACES_VL
501 WHERE space_name = c_space_name
502 AND bom_department_id = c_dept_id;
503 --
504 l_table_name VARCHAR2(30);
505 l_pk_name VARCHAR2(30);
506 l_pk_value VARCHAR2(30);
507 l_where_clause VARCHAR2(2000);
508 l_space_name VARCHAR2(30);
509 l_space_id NUMBER;
510 BEGIN
511 -- Initialize API/Procedure return status to success
512 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
513 -- Check required parameters
514 IF (p_space_rec.ORGANIZATION_ID IS NULL OR
515 p_space_rec.ORGANIZATION_ID = Fnd_Api.G_MISS_NUM
516 )
517 --
518 THEN
519 -- missing required fields
520 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
521 THEN
522 Fnd_Message.set_name('AHL', 'AHL_LTP_ORG_REQUIRED');
523 Fnd_Msg_Pub.ADD;
524 END IF;
525 x_return_status := Fnd_Api.G_RET_STS_ERROR;
526 END IF;
527 -- BOM_DEPARTMENT_ID
528 IF (p_space_rec.DEPARTMENT_ID = Fnd_Api.G_MISS_NUM OR
529 p_space_rec.DEPARTMENT_ID IS NULL)
530 THEN
531 -- missing required fields
532 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
533 THEN
534 Fnd_Message.set_name('AHL', 'AHL_LTP_DEPT_REQUIRED');
535 Fnd_Msg_Pub.ADD;
536 END IF;
537 x_return_status := Fnd_Api.G_RET_STS_ERROR;
538 END IF;
539 --SPACE CATEGORY
540 IF (p_space_rec.SPACE_CATEGORY_CODE = Fnd_Api.G_MISS_CHAR OR
541 p_space_rec.SPACE_CATEGORY_CODE IS NULL)
542 THEN
543 -- missing required fields
544 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
545 THEN
546 Fnd_Message.set_name('AHL', 'AHL_LTP_SP_CATEGORY_NOT_EXIST');
547 Fnd_Msg_Pub.ADD;
548 END IF;
549 x_return_status := Fnd_Api.G_RET_STS_ERROR;
550 END IF;
551
552 --SPACE_NAME
553 IF (p_space_rec.space_name = Fnd_Api.G_MISS_CHAR OR
554 p_space_rec.SPACE_NAME IS NULL)
555 THEN
556 -- missing required fields
557 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
558 THEN
559 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_NAME_NOT_EXIST');
560 Fnd_Msg_Pub.ADD;
561 END IF;
562 x_return_status := Fnd_Api.G_RET_STS_ERROR;
563 END IF;
564 -- Validate uniqueness
565 IF p_validation_mode = Jtf_Plsql_Api.g_create
566 AND (p_space_rec.space_id = FND_API.G_MISS_NUM OR
567 p_space_rec.space_id IS NULL)
568 THEN
569 IF Ahl_Utility_Pvt.check_uniqueness(
570 'ahl_spaces_vl',
571 'space_name = ' || p_space_rec.space_name
572 ) = Fnd_Api.g_false
573 THEN
574 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error)
575 THEN
576 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_NAME_EXISTS');
577 Fnd_Msg_Pub.ADD;
578 END IF;
579 x_return_status := Fnd_Api.g_ret_sts_error;
580 END IF;
581 END IF;
582 --
583 IF (p_space_rec.space_id = FND_API.G_MISS_NUM OR
584 p_space_rec.space_id IS NULL) THEN
585 OPEN space_name_cur(p_space_rec.space_name,p_space_rec.department_id);
586 FETCH space_name_cur INTO l_space_name;
587 IF l_space_name IS NOT NULL THEN
588 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_NAME_EXISTS');
589 Fnd_Msg_Pub.ADD;
590 END IF;
591 CLOSE space_name_cur;
592 END IF;
593
594 /*
595 --
596 IF (p_space_rec.space_id <> FND_API.G_MISS_NUM AND
597 p_space_rec.space_id IS NOT NULL) THEN
598 --
599 SELECT space_id INTO l_space_id
600 FROM AHL_SPACE_ASSIGNMENTS
601 WHERE space_id = p_space_rec.space_id;
602 --
603 IF l_space_id IS NOT NULL THEN
604 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_ASSIGN_EXISTS');
605 Fnd_Msg_Pub.ADD;
606 ELSE
607 OPEN space_name_cur(p_space_rec.space_name,p_space_rec.department_id);
608 FETCH space_name_cur INTO l_space_name;
609 IF l_space_name IS NOT NULL THEN
610 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_NAME_EXISTS');
611 Fnd_Msg_Pub.ADD;
612 END IF;
613 CLOSE space_name_cur;
614 END IF;
615 END IF;
616 */
617 -- END IF;
618
619 END Validate_Space_Items;
620 ----------------------------------------------------------------------------
621 -- NAME
622 -- Validate_Space_Record
623 --
624 -- PURPOSE
625 -- This procedure is to validate Space Restriction record
626 --
627 -- NOTES
628 -- End of Comments
629 -----------------------------------------------------------------------------
630 PROCEDURE Validate_Space_Record(
631 p_space_rec IN space_rec,
632 x_return_status OUT NOCOPY VARCHAR2
633 ) IS
634 -- Status Local Variables
635 l_return_status VARCHAR2(1);
636 BEGIN
637 -- Initialize API return status to success
638 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
639 --
640 NULL;
641 --
642 END Validate_Space_Record;
643
644 --------------------------------------------------------------------
645 -- PROCEDURE
646 -- Validate_Space
647 --
648 -- PURPOSE
649 -- Validate space attributes
650 --
651 -- PARAMETERS
652 --
653 -- NOTES
654 --
655 --------------------------------------------------------------------
656 PROCEDURE Validate_Space
657 ( p_api_version IN NUMBER,
658 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
659 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
660 p_space_rec IN space_rec,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_count OUT NOCOPY NUMBER,
663 x_msg_data OUT NOCOPY VARCHAR2
664 )
665 IS
666 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Space';
667 l_api_version CONSTANT NUMBER := 1.0;
668 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
669 l_return_status VARCHAR2(1);
670 l_space_rec space_rec;
671 BEGIN
672 -- Standard call to check for call compatibility.
673 IF NOT Fnd_Api.Compatible_API_Call ( l_api_version,
674 p_api_version,
675 l_api_name,
676 G_PKG_NAME)
677 THEN
678 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
679 END IF;
680 -- Initialize message list if p_init_msg_list is set to TRUE.
681 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
682 Fnd_Msg_Pub.initialize;
683 END IF;
684 -- Initialize API return status to success
685 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
686 --
687 -- API body
688 --
689 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item
690 THEN
691 Validate_Space_Items
692 ( p_space_rec => p_space_rec,
693 p_validation_mode => Jtf_Plsql_Api.g_create,
694 x_return_status => l_return_status
695 );
696 -- If any errors happen abort API.
697 IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
698 THEN
699 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
700 ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
701 THEN
702 RAISE Fnd_Api.G_EXC_ERROR;
703 END IF;
704 END IF;
705 -- Perform cross attribute validation and missing attribute checks. Record
706 -- level validation.
707 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record
708 THEN
709 Validate_Space_Record(
710 p_space_rec => p_space_rec,
711 x_return_status => l_return_status
712
713 );
714 IF l_return_status = Fnd_Api.G_RET_STS_ERROR
715 THEN
716 RAISE Fnd_Api.G_EXC_ERROR;
717 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
718 THEN
719 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
720 END IF;
721 END IF;
722 --
723 -- END of API body.
724 --
725 -------------------- finish --------------------------
726 Fnd_Msg_Pub.count_and_get(
727 p_encoded => Fnd_Api.g_false,
728 p_count => x_msg_count,
729 p_data => x_msg_data);
730 EXCEPTION
731 WHEN Fnd_Api.G_EXC_ERROR THEN
732 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
733 Fnd_Msg_Pub.Count_AND_Get
734 ( p_count => x_msg_count,
735 p_data => x_msg_data,
736 p_encoded => Fnd_Api.G_FALSE
737 );
738 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
739 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
740 Fnd_Msg_Pub.Count_AND_Get
741 ( p_count => x_msg_count,
742 p_data => x_msg_data,
743 p_encoded => Fnd_Api.G_FALSE
744 );
745 WHEN OTHERS THEN
746 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
747 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
748 THEN
749 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
750 END IF;
751 Fnd_Msg_Pub.Count_AND_Get
752 ( p_count => x_msg_count,
753 p_data => x_msg_data,
754 p_encoded => Fnd_Api.G_FALSE
755 );
756 END Validate_Space;
757
758 --------------------------------------------------------------------
759 -- PROCEDURE
760 -- Create_Space
761 --
762 -- PURPOSE
763 -- Create Space Record
764 --
765 -- PARAMETERS
766 -- p_x_space_rec: the record representing AHL_SPACES_VL view..
767 --
768 -- NOTES
769 --------------------------------------------------------------------
770
771 PROCEDURE Create_Space (
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
774 p_commit IN VARCHAR2 := FND_API.g_false,
775 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
776 p_module_type IN VARCHAR2 := 'JSP',
777 p_x_space_rec IN OUT NOCOPY ahl_ltp_space_capb_pub.Space_Rec,
778 x_return_status OUT NOCOPY VARCHAR2,
779 x_msg_count OUT NOCOPY NUMBER,
780 x_msg_data OUT NOCOPY VARCHAR2
781 )
782 IS
783 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SPACE';
784 l_api_version CONSTANT NUMBER := 1.0;
785 l_msg_count NUMBER;
786 l_return_status VARCHAR2(1);
787 l_msg_data VARCHAR2(2000);
788 l_dummy NUMBER;
789 l_rowid VARCHAR2(30);
790 l_organization_id NUMBER;
791 l_department_id NUMBER;
792 l_space_id NUMBER;
793 l_space_unavaialability_id NUMBER;
794 l_space_rec Space_Rec;
795 --
796 CURSOR c_seq
797 IS
798 SELECT AHL_SPACES_B_S.NEXTVAL
799 FROM dual;
800 --
801 CURSOR c_id_exists (x_id IN NUMBER) IS
802 SELECT 1
803 FROM dual
804 WHERE EXISTS (SELECT 1
805 FROM ahl_spaces_b
806 WHERE space_id = x_id);
807 --
808 BEGIN
809 --------------------Initialize ----------------------------------
810 -- Standard Start of API savepoint
811 SAVEPOINT create_space;
812 -- Check if API is called in debug mode. If yes, enable debug.
813 IF G_DEBUG='Y' THEN
814 AHL_DEBUG_PUB.enable_debug;
815 END IF;
816 -- Debug info.
817 IF G_DEBUG='Y' THEN
818 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Create Space ','+SPCBL+');
819 END IF;
820 -- Standard call to check for call compatibility.
821 IF FND_API.to_boolean(p_init_msg_list)
822 THEN
823 FND_MSG_PUB.initialize;
824 END IF;
825 -- Initialize API return status to success
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827 -- Initialize message list if p_init_msg_list is set to TRUE.
828 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
829 p_api_version,
830 l_api_name,G_PKG_NAME)
831 THEN
832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
833 END IF;
834 --------------------Value OR ID conversion---------------------------
835 --Start API Body
836 IF p_module_type = 'JSP'
837 THEN
838 p_x_space_rec.organization_id := null;
839 p_x_space_rec.department_id := null;
840 END IF;
841
842 -- Convert org name to organization id
843 IF (p_x_space_rec.org_name IS NOT NULL AND
844 p_x_space_rec.org_name <> FND_API.G_MISS_CHAR ) OR
845 (p_x_space_rec.organization_id IS NOT NULL AND
846 p_x_space_rec.organization_id <> FND_API.G_MISS_NUM) THEN
847
848 Check_org_name_Or_Id
849 (p_organization_id => p_x_space_rec.organization_id,
850 p_org_name => p_x_space_rec.org_name,
851 x_organization_id => l_organization_id,
852 x_return_status => l_return_status,
853 x_error_msg_code => l_msg_data);
854
855 IF NVL(l_return_status,'x') <> 'S'
856 THEN
857 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_ID_NOT_EXISTS');
858 Fnd_Msg_Pub.ADD;
859 END IF;
860 ELSE
861 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_REQUIRED');
862 Fnd_Msg_Pub.ADD;
863 END IF;
864
865 --Assign the returned value
866 p_x_space_rec.organization_id := l_organization_id;
867
868 -- Get dept code using dept description
869 IF (p_x_space_rec.dept_description IS NOT NULL AND
870 p_x_space_rec.dept_description <> FND_API.G_MISS_CHAR ) OR
871 (p_x_space_rec.department_id IS NOT NULL AND
872 p_x_space_rec.department_id <> FND_API.G_MISS_NUM) THEN
873
874 Check_dept_desc_Or_Id
875 (p_organization_id => p_x_space_rec.organization_id,
876 p_org_name => p_x_space_rec.org_name,
877 p_dept_description => p_x_space_rec.dept_description,
878 p_department_id => p_x_space_rec.department_id,
879 x_department_id => l_department_id,
880 x_return_status => l_return_status,
881 x_error_msg_code => l_msg_data);
882
883 IF NVL(l_return_status,'x') <> 'S'
884 THEN
885 Fnd_Message.SET_NAME('AHL','AHL_LTP_DEPT_ID_NOT_EXIST');
886 Fnd_Msg_Pub.ADD;
887 END IF;
888 ELSE
889 Fnd_Message.SET_NAME('AHL','AHL_LTP_DEPT_REQUIRED');
890 Fnd_Msg_Pub.ADD;
891 END IF;
892
893
894 --Assign the returned value
895 p_x_space_rec.department_id := l_department_id;
896
897 --For Space Category
898 IF p_x_space_rec.space_category_mean IS NOT NULL AND
899 p_x_space_rec.space_category_mean <> Fnd_Api.G_MISS_CHAR
900 THEN
901 Check_lookup_name_Or_Id (
902 p_lookup_type => 'AHL_SPACE_CATEGORY',
903 p_lookup_code => NULL,
904 p_meaning => p_x_space_rec.space_category_mean,
905 p_check_id_flag => 'Y',
906 x_lookup_code => l_space_rec.space_category_code,
907 x_return_status => l_return_status);
908
909 IF NVL(l_return_status, 'X') <> 'S'
910 THEN
911 Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
912 Fnd_Msg_Pub.ADD;
913 END IF;
914 END IF;
915 -- Id presents
916 IF p_x_space_rec.space_category_code IS NOT NULL AND
917 p_x_space_rec.space_category_code <> Fnd_Api.G_MISS_CHAR
918 THEN
919 l_space_rec.space_category_code := p_x_space_rec.space_category_code;
920 ELSE
921 Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
922 Fnd_Msg_Pub.ADD;
923 END IF;
924 --
925 --SPACE_NAME
926 IF (p_x_space_rec.space_name = Fnd_Api.G_MISS_CHAR OR
927 p_x_space_rec.SPACE_NAME IS NULL)
928 THEN
929 -- missing required fields
930 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_NAME_NOT_EXIST');
931 Fnd_Msg_Pub.ADD;
932 END IF;
933
934 --Standard check to count messages
935 l_msg_count := Fnd_Msg_Pub.count_msg;
936
937 IF l_msg_count > 0 THEN
938 X_msg_count := l_msg_count;
939 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
940 RAISE Fnd_Api.G_EXC_ERROR;
941 END IF;
942
943 --------------------------------Validation ---------------------------
944 --Assign to local variable
945 Assign_Space_Rec (
946 p_space_rec => p_x_space_rec,
947 x_space_rec => l_Space_rec);
948
949 -- Call Validate space rec input attributes
950
951 Validate_Space
952 ( p_api_version => l_api_version,
953 p_init_msg_list => p_init_msg_list,
954 p_validation_level => p_validation_level,
955 p_space_rec => l_space_rec,
956 x_return_status => l_return_status,
957 x_msg_count => l_msg_count,
958 x_msg_data => l_msg_data );
959
960 IF (p_x_space_rec.space_id = Fnd_Api.G_MISS_NUM OR
961 p_x_space_rec.space_id IS NULL )
962 THEN
963 --
964 -- If the ID is not passed into the API, then
965 -- grab a value from the sequence.
966 OPEN c_seq;
967 FETCH c_seq INTO l_space_id;
968 CLOSE c_seq;
969 --
970
971 -- Check to be sure that the sequence does not exist.
972 OPEN c_id_exists (l_space_id);
973 FETCH c_id_exists INTO l_dummy;
974 CLOSE c_id_exists;
975 --
976 -- If the value for the ID already exists, then
977 -- l_dummy would be populated with '1', otherwise,
978 -- it receives NULL.
979 IF l_dummy IS NOT NULL THEN
980 Fnd_Message.SET_NAME('AHL','AHL_LTP_SEQUENCE_NOT_EXISTS');
981 Fnd_Msg_Pub.ADD;
982 END IF;
983 -- For optional fields
984 IF p_x_space_rec.description = FND_API.G_MISS_CHAR
985 THEN
986 l_space_rec.description := NULL;
987 ELSE
988 l_space_rec.description := p_x_space_rec.description;
989 END IF;
990 --
991 IF p_x_space_rec.attribute_category = FND_API.G_MISS_CHAR
992 THEN
993 l_space_rec.attribute_category := NULL;
994 ELSE
995 l_space_rec.attribute_category := p_x_space_rec.attribute_category;
996 END IF;
997 --
998 IF p_x_space_rec.attribute1 = FND_API.G_MISS_CHAR
999 THEN
1000 l_space_rec.attribute1 := NULL;
1001 ELSE
1002 l_space_rec.attribute1 := p_x_space_rec.attribute1;
1003 END IF;
1004 --
1005 IF p_x_space_rec.attribute2 = FND_API.G_MISS_CHAR
1006 THEN
1007 l_space_rec.attribute2 := NULL;
1008 ELSE
1009 l_space_rec.attribute2 := p_x_space_rec.attribute2;
1010 END IF;
1011 --
1012 IF p_x_space_rec.attribute3 = FND_API.G_MISS_CHAR
1013 THEN
1014 l_space_rec.attribute3 := NULL;
1015 ELSE
1016 l_space_rec.attribute3 := p_x_space_rec.attribute3;
1017 END IF;
1018 --
1019 IF p_x_space_rec.attribute4 = FND_API.G_MISS_CHAR
1020 THEN
1021 l_space_rec.attribute4 := NULL;
1022 ELSE
1023 l_space_rec.attribute4 := p_x_space_rec.attribute4;
1024 END IF;
1025 --
1026 IF p_x_space_rec.attribute5 = FND_API.G_MISS_CHAR
1027 THEN
1028 l_space_rec.attribute5 := NULL;
1029 ELSE
1030 l_space_rec.attribute5 := p_x_space_rec.attribute5;
1031 END IF;
1032 --
1033 IF p_x_space_rec.attribute6 = FND_API.G_MISS_CHAR
1034 THEN
1035 l_space_rec.attribute6 := NULL;
1036 ELSE
1037 l_space_rec.attribute6 := p_x_space_rec.attribute6;
1038 END IF;
1039 --
1040 IF p_x_space_rec.attribute7 = FND_API.G_MISS_CHAR
1041 THEN
1042 l_space_rec.attribute7 := NULL;
1043 ELSE
1044 l_space_rec.attribute7 := p_x_space_rec.attribute7;
1045 END IF;
1046 --
1047 IF p_x_space_rec.attribute8 = FND_API.G_MISS_CHAR
1048 THEN
1049 l_space_rec.attribute8 := NULL;
1050 ELSE
1051 l_space_rec.attribute8 := p_x_space_rec.attribute8;
1052 END IF;
1053 --
1054 IF p_x_space_rec.attribute9 = FND_API.G_MISS_CHAR
1055 THEN
1056 l_space_rec.attribute9 := NULL;
1057 ELSE
1058 l_space_rec.attribute9 := p_x_space_rec.attribute9;
1059 END IF;
1060 --
1061 IF p_x_space_rec.attribute10 = FND_API.G_MISS_CHAR
1062 THEN
1063 l_space_rec.attribute10 := NULL;
1064 ELSE
1065 l_space_rec.attribute10 := p_x_space_rec.attribute10;
1066 END IF;
1067 --
1068 IF p_x_space_rec.attribute11 = FND_API.G_MISS_CHAR
1069 THEN
1070 l_space_rec.attribute11 := NULL;
1071 ELSE
1072 l_space_rec.attribute11 := p_x_space_rec.attribute11;
1073 END IF;
1074 --
1075 IF p_x_space_rec.attribute12 = FND_API.G_MISS_CHAR
1076 THEN
1077 l_space_rec.attribute12 := NULL;
1078 ELSE
1079 l_space_rec.attribute12 := p_x_space_rec.attribute12;
1080 END IF;
1081 --
1082 IF p_x_space_rec.attribute13 = FND_API.G_MISS_CHAR
1083 THEN
1084 l_space_rec.attribute13 := NULL;
1085 ELSE
1086 l_space_rec.attribute13 := p_x_space_rec.attribute13;
1087 END IF;
1088 --
1089 IF p_x_space_rec.attribute14 = FND_API.G_MISS_CHAR
1090 THEN
1091 l_space_rec.attribute14 := NULL;
1092 ELSE
1093 l_space_rec.attribute14 := p_x_space_rec.attribute14;
1094 END IF;
1095 --
1096 IF p_x_space_rec.attribute15 = FND_API.G_MISS_CHAR
1097 THEN
1098 l_space_rec.attribute15 := NULL;
1099 ELSE
1100 l_space_rec.attribute15 := p_x_space_rec.attribute15;
1101 END IF;
1102 END IF;
1103
1104 --Standard check to count messages
1105 l_msg_count := Fnd_Msg_Pub.count_msg;
1106
1107 IF l_msg_count > 0 THEN
1108 X_msg_count := l_msg_count;
1109 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1110 RAISE Fnd_Api.G_EXC_ERROR;
1111 END IF;
1112
1113 ----------------------------DML Operation---------------------------------
1114 --Call table handler generated package to insert a record
1115 AHL_SPACES_PKG.INSERT_ROW (
1116 X_ROWID => l_rowid,
1117 X_SPACE_ID => l_space_id,
1118 X_BOM_DEPARTMENT_ID => l_space_rec.department_id,
1119 X_ORGANIZATION_ID => l_space_rec.organization_id,
1120 X_INACTIVE_FLAG => 'Y',
1121 X_SPACE_CATEGORY => p_x_space_rec.space_category_code,
1122 X_SPACE_NAME => p_x_space_rec.space_name,
1123 X_OBJECT_VERSION_NUMBER => 1,
1124 X_ATTRIBUTE_CATEGORY => l_space_rec.attribute_category,
1125 X_ATTRIBUTE1 => l_space_rec.attribute1,
1126 X_ATTRIBUTE2 => l_space_rec.attribute2,
1127 X_ATTRIBUTE3 => l_space_rec.attribute3,
1128 X_ATTRIBUTE4 => l_space_rec.attribute4,
1129 X_ATTRIBUTE5 => l_space_rec.attribute5,
1130 X_ATTRIBUTE6 => l_space_rec.attribute6,
1131 X_ATTRIBUTE7 => l_space_rec.attribute7,
1132 X_ATTRIBUTE8 => l_space_rec.attribute8,
1133 X_ATTRIBUTE9 => l_space_rec.attribute9,
1134 X_ATTRIBUTE10 => l_space_rec.attribute10,
1135 X_ATTRIBUTE11 => l_space_rec.attribute11,
1136 X_ATTRIBUTE12 => l_space_rec.attribute12,
1137 X_ATTRIBUTE13 => l_space_rec.attribute13,
1138 X_ATTRIBUTE14 => l_space_rec.attribute14,
1139 X_ATTRIBUTE15 => l_space_rec.attribute15,
1140 X_CREATION_DATE => SYSDATE,
1141 X_CREATED_BY => Fnd_Global.USER_ID,
1142 X_LAST_UPDATE_DATE => SYSDATE,
1143 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1144 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1145
1146 p_x_space_rec.space_id := l_space_id;
1147 ---------------------------End of Body---------------------------------------
1148 --Standard check to count messages
1149 l_msg_count := Fnd_Msg_Pub.count_msg;
1150
1151 IF l_msg_count > 0 THEN
1152 X_msg_count := l_msg_count;
1153 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1154 RAISE Fnd_Api.G_EXC_ERROR;
1155 END IF;
1156
1157 --Standard check for commit
1158 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1159 COMMIT;
1160 END IF;
1161 -- Debug info
1162 IF G_DEBUG='Y' THEN
1163 Ahl_Debug_Pub.debug( 'End of private api Create Space ','+SPCBL+');
1164 -- Check if API is called in debug mode. If yes, disable debug.
1165 Ahl_Debug_Pub.disable_debug;
1166 END IF;
1167 EXCEPTION
1168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1169 ROLLBACK TO create_space;
1170 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1171 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1172 p_count => x_msg_count,
1173 p_data => x_msg_data);
1174 IF G_DEBUG='Y' THEN
1175 AHL_DEBUG_PUB.log_app_messages (
1176 x_msg_count, x_msg_data, 'ERROR' );
1177 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Create Space ','+SPCBL+');
1178 -- Check if API is called in debug mode. If yes, disable debug.
1179 AHL_DEBUG_PUB.disable_debug;
1180 END IF;
1181 WHEN FND_API.G_EXC_ERROR THEN
1182 ROLLBACK TO create_space;
1183 X_return_status := FND_API.G_RET_STS_ERROR;
1184 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1185 p_count => x_msg_count,
1186 p_data => X_msg_data);
1187 IF G_DEBUG='Y' THEN
1188 -- Debug info.
1189 AHL_DEBUG_PUB.log_app_messages (
1190 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1191 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Create Space','+SPCBL+');
1192 -- Check if API is called in debug mode. If yes, disable debug.
1193 AHL_DEBUG_PUB.disable_debug;
1194 END IF;
1195 WHEN OTHERS THEN
1196 ROLLBACK TO create_space;
1197 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1198 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1199 THEN
1200 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_CAPB_PVT',
1201 p_procedure_name => 'CREATE_SPACE',
1202 p_error_text => SUBSTR(SQLERRM,1,240));
1203 END IF;
1204 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1205 p_count => x_msg_count,
1206 p_data => X_msg_data);
1207 IF G_DEBUG='Y' THEN
1208 -- Debug info.
1209 AHL_DEBUG_PUB.log_app_messages (
1210 x_msg_count, x_msg_data, 'SQL ERROR' );
1211 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Create Space','+SPCBL+');
1212 -- Check if API is called in debug mode. If yes, disable debug.
1213 AHL_DEBUG_PUB.disable_debug;
1214 END IF;
1215
1216 END Create_Space;
1217
1218 --------------------------------------------------------------------
1219 -- PROCEDURE
1220 -- Update_Space
1221 --
1222 -- PURPOSE
1223 -- Update Space Record.
1224 --
1225 -- PARAMETERS
1226 -- p_space_rec: the record representing AHL_SPACES_VL
1227 --
1228 -- NOTES
1229 --------------------------------------------------------------------
1230 PROCEDURE Update_Space (
1231 p_api_version IN NUMBER,
1232 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1233 p_commit IN VARCHAR2 := FND_API.g_false,
1234 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1235 p_module_type IN VARCHAR2 := 'JSP',
1236 p_space_rec IN ahl_ltp_space_capb_pub.Space_Rec,
1237 x_return_status OUT NOCOPY VARCHAR2,
1238 x_msg_count OUT NOCOPY NUMBER,
1239 x_msg_data OUT NOCOPY VARCHAR2
1240 )
1241 IS
1242 CURSOR space_name_cur (c_space_id IN NUMBER,
1243 c_dept_id IN NUMBER)
1244 IS
1245 SELECT space_name, space_id
1246 FROM AHL_SPACES_VL
1247 WHERE space_id = c_space_id
1248 AND bom_department_id = c_dept_id;
1249 --
1250
1251 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SPACE';
1252 l_api_version CONSTANT NUMBER := 1.0;
1253 l_msg_count NUMBER;
1254 l_return_status VARCHAR2(1);
1255 l_msg_data VARCHAR2(2000);
1256 l_dummy NUMBER;
1257 l_rowid VARCHAR2(30);
1258 l_organization_id NUMBER;
1259 l_department_id NUMBER;
1260 l_space_id NUMBER;
1261 l_space_rec Space_Rec;
1262 l_Aspace_rec Space_Rec;
1263 l_space_name VARCHAR2(30);
1264 l_dup_space_id NUMBER;
1265 BEGIN
1266
1267
1268 --------------------Initialize ----------------------------------
1269 -- Standard Start of API savepoint
1270 SAVEPOINT update_space;
1271 -- Check if API is called in debug mode. If yes, enable debug.
1272 IF G_DEBUG='Y' THEN
1273 AHL_DEBUG_PUB.enable_debug;
1274 END IF;
1275 -- Debug info.
1276 IF G_DEBUG='Y' THEN
1277 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Update Space ','+SPCBL+');
1278 END IF;
1279 -- Standard call to check for call compatibility.
1280 IF FND_API.to_boolean(p_init_msg_list)
1281 THEN
1282 FND_MSG_PUB.initialize;
1283 END IF;
1284 -- Initialize API return status to success
1285 x_return_status := FND_API.G_RET_STS_SUCCESS;
1286 -- Initialize message list if p_init_msg_list is set to TRUE.
1287 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1288 p_api_version,
1289 l_api_name,G_PKG_NAME)
1290 THEN
1291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1292 END IF;
1293
1294 --------------------Value OR ID conversion---------------------------
1295 IF G_DEBUG='Y' THEN
1296 Ahl_Debug_Pub.debug( 'space id'||p_space_rec.space_id);
1297 Ahl_Debug_Pub.debug( 'space id'||p_space_rec.space_name);
1298 END IF;
1299 --Assign to local variable
1300 Assign_Space_Rec (
1301 p_space_rec => p_space_rec,
1302 x_space_rec => l_Space_rec);
1303
1304 IF p_module_type = 'JSP'
1305 THEN
1306 l_space_rec.organization_id := null;
1307 l_space_rec.department_id := null;
1308 END IF;
1309
1310 -- Convert Space name to space id
1311 IF (l_space_rec.space_name IS NOT NULL AND
1312 l_space_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1313 (l_space_rec.space_id IS NOT NULL AND
1314 l_space_rec.space_id <> FND_API.G_MISS_NUM) THEN
1315
1316 Check_space_name_Or_Id
1317 (p_space_id => l_space_rec.space_id,
1318 p_space_name => l_space_rec.space_name,
1319 x_space_id => l_space_id,
1320 x_return_status => l_return_status,
1321 x_error_msg_code => l_msg_data);
1322
1323 IF NVL(l_return_status,'x') <> 'S'
1324 THEN
1325 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_NOT_EXISTS');
1326 Fnd_Message.SET_TOKEN('SPACEID',l_space_rec.space_name);
1327 Fnd_Msg_Pub.ADD;
1328 END IF;
1329 ELSE
1330 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_ID_NOT_EXIST');
1331 Fnd_Msg_Pub.ADD;
1332 RAISE Fnd_Api.G_EXC_ERROR;
1333 END IF;
1334
1335 --Assign the returned value
1336 l_space_rec.space_id := l_space_id;
1337
1338 --Check for space name
1339 IF (l_space_rec.space_name IS NULL OR
1340 l_space_rec.space_name = fnd_api.g_miss_char)
1341 THEN
1342 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_NAME_NOT_EXIST');
1343 Fnd_Msg_Pub.ADD;
1344 RAISE Fnd_Api.G_EXC_ERROR;
1345 END IF;
1346
1347 --For Space Category
1348 IF l_space_rec.space_category_mean IS NOT NULL AND
1349 l_space_rec.space_category_mean <> Fnd_Api.G_MISS_CHAR
1350 THEN
1351 Check_lookup_name_Or_Id (
1352 p_lookup_type => 'AHL_SPACE_CATEGORY',
1353 p_lookup_code => NULL,
1354 p_meaning => l_space_rec.space_category_mean,
1355 p_check_id_flag => 'Y',
1356 x_lookup_code => l_space_rec.space_category_code,
1357 x_return_status => l_return_status);
1358
1359 IF NVL(l_return_status, 'X') <> 'S'
1360 THEN
1361 Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
1362 Fnd_Msg_Pub.ADD;
1363 END IF;
1364 END IF;
1365 -- Id presents
1366 IF l_space_rec.space_category_code IS NOT NULL AND
1367 l_space_rec.space_category_code <> Fnd_Api.G_MISS_CHAR
1368 THEN
1369 l_space_rec.space_category_code := l_space_rec.space_category_code;
1370 END IF;
1371
1372 --For Inactive Flag
1373 IF p_space_rec.inactive_flag_mean IS NOT NULL AND
1374 p_space_rec.inactive_flag_mean <> Fnd_Api.G_MISS_CHAR
1375 THEN
1376 Check_lookup_name_Or_Id (
1377 p_lookup_type => 'AHL_SPACE_STATUS',
1378 p_lookup_code => NULL,
1379 p_meaning => p_space_rec.inactive_flag_mean,
1380 p_check_id_flag => 'Y',
1381 x_lookup_code => l_space_rec.inactive_flag_code,
1382 x_return_status => l_return_status);
1383
1384 IF NVL(l_return_status, 'X') <> 'S'
1385 THEN
1386 Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_STATUS_NOT_EXIST');
1387 Fnd_Msg_Pub.ADD;
1388 END IF;
1389 END IF;
1390 -- Id presents
1391 IF p_space_rec.inactive_flag_code IS NOT NULL AND
1392 p_space_rec.inactive_flag_code <> Fnd_Api.G_MISS_CHAR
1393 THEN
1394 l_space_rec.inactive_flag_code := p_space_rec.inactive_flag_code;
1395 END IF;
1396
1397 --------------------------------Validation ---------------------------
1398 -- get existing values and compare
1399 Complete_Space_Rec (
1400 p_space_rec => l_space_rec,
1401 x_space_rec => l_Aspace_rec);
1402
1403 -- Call Validate space rec attributes
1404 Validate_Space
1405 ( p_api_version => l_api_version,
1406 p_init_msg_list => p_init_msg_list,
1407 p_validation_level => p_validation_level,
1408 p_space_rec => l_Aspace_rec,
1409 x_return_status => l_return_status,
1410 x_msg_count => l_msg_count,
1411 x_msg_data => l_msg_data );
1412
1413 --Standard check to count messages
1414 l_msg_count := Fnd_Msg_Pub.count_msg;
1415
1416 IF l_msg_count > 0 THEN
1417 X_msg_count := l_msg_count;
1418 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1419 RAISE Fnd_Api.G_EXC_ERROR;
1420 END IF;
1421 --
1422 IF G_DEBUG='Y' THEN
1423 Ahl_Debug_Pub.debug( 'before update space id'||p_space_rec.space_id);
1424 END IF;
1425 ----------------------------DML Operation---------------------------------
1426 --Call table handler generated package to update a record
1427 AHL_SPACES_PKG.UPDATE_ROW
1428 (
1429 X_SPACE_ID => l_Aspace_rec.space_id,
1430 X_BOM_DEPARTMENT_ID => l_Aspace_rec.department_id,
1431 X_ORGANIZATION_ID => l_Aspace_rec.organization_id,
1432 X_SPACE_NAME => l_Aspace_rec.space_name,
1433 X_SPACE_CATEGORY => l_Aspace_rec.space_category_code,
1434 X_INACTIVE_FLAG => l_Aspace_rec.inactive_flag_code,
1435 X_OBJECT_VERSION_NUMBER => l_Aspace_rec.object_version_number+1,
1436 X_ATTRIBUTE_CATEGORY => l_Aspace_rec.attribute_category,
1437 X_ATTRIBUTE1 => l_Aspace_rec.attribute1,
1438 X_ATTRIBUTE2 => l_Aspace_rec.attribute2,
1439 X_ATTRIBUTE3 => l_Aspace_rec.attribute3,
1440 X_ATTRIBUTE4 => l_Aspace_rec.attribute4,
1441 X_ATTRIBUTE5 => l_Aspace_rec.attribute5,
1442 X_ATTRIBUTE6 => l_Aspace_rec.attribute6,
1443 X_ATTRIBUTE7 => l_Aspace_rec.attribute7,
1444 X_ATTRIBUTE8 => l_Aspace_rec.attribute8,
1445 X_ATTRIBUTE9 => l_Aspace_rec.attribute9,
1446 X_ATTRIBUTE10 => l_Aspace_rec.attribute10,
1447 X_ATTRIBUTE11 => l_Aspace_rec.attribute11,
1448 X_ATTRIBUTE12 => l_Aspace_rec.attribute12,
1449 X_ATTRIBUTE13 => l_Aspace_rec.attribute13,
1450 X_ATTRIBUTE14 => l_Aspace_rec.attribute14,
1451 X_ATTRIBUTE15 => l_Aspace_rec.attribute15,
1452 X_LAST_UPDATE_DATE => SYSDATE,
1453 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1454 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1455
1456
1457 ---------------------------End of Body---------------------------------------
1458 --Standard check to count messages
1459 l_msg_count := Fnd_Msg_Pub.count_msg;
1460
1461 IF l_msg_count > 0 THEN
1462 X_msg_count := l_msg_count;
1463 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1464 RAISE Fnd_Api.G_EXC_ERROR;
1465 END IF;
1466
1467 --Standard check for commit
1468 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1469 COMMIT;
1470 END IF;
1471 -- Debug info
1472 IF G_DEBUG='Y' THEN
1473 Ahl_Debug_Pub.debug( 'End of private api Update Space ','+SPCBL+');
1474 -- Check if API is called in debug mode. If yes, disable debug.
1475 Ahl_Debug_Pub.disable_debug;
1476 END IF;
1477 EXCEPTION
1478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1479 ROLLBACK TO update_space;
1480 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1482 p_count => x_msg_count,
1483 p_data => x_msg_data);
1484 IF G_DEBUG='Y' THEN
1485 AHL_DEBUG_PUB.log_app_messages (
1486 x_msg_count, x_msg_data, 'ERROR' );
1487 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space ','+SPCBL+');
1488 -- Check if API is called in debug mode. If yes, disable debug.
1489 AHL_DEBUG_PUB.disable_debug;
1490 END IF;
1491 WHEN FND_API.G_EXC_ERROR THEN
1492 ROLLBACK TO update_space;
1493 X_return_status := FND_API.G_RET_STS_ERROR;
1494 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1495 p_count => x_msg_count,
1496 p_data => X_msg_data);
1497 -- Debug info.
1498 IF G_DEBUG='Y' THEN
1499 AHL_DEBUG_PUB.log_app_messages (
1500 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1501 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space ','+SPCBL+');
1502 -- Check if API is called in debug mode. If yes, disable debug.
1503 AHL_DEBUG_PUB.disable_debug;
1504 END IF;
1505 WHEN OTHERS THEN
1506 ROLLBACK TO update_space;
1507 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1509 THEN
1510 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_CAPB_PVT',
1511 p_procedure_name => 'UPDATE_SPACE',
1512 p_error_text => SUBSTR(SQLERRM,1,240));
1513 END IF;
1514 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1515 p_count => x_msg_count,
1516 p_data => X_msg_data);
1517
1518 -- Debug info.
1519 IF G_DEBUG='Y' THEN
1520 AHL_DEBUG_PUB.log_app_messages (
1521 x_msg_count, x_msg_data, 'SQL ERROR' );
1522 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_unavl_pvt.Update Space ','+SPCBL+');
1523 -- Check if API is called in debug mode. If yes, disable debug.
1524 AHL_DEBUG_PUB.disable_debug;
1525 END IF;
1526 END Update_Space;
1527
1528 --------------------------------------------------------------------
1529 -- PROCEDURE
1530 -- Delete_Space
1531 --
1532 -- PURPOSE
1533 -- Delete Space Record.
1534 --
1535 -- PARAMETERS
1536 --
1537 -- ISSUES
1538 --
1539 -- NOTES
1540 -- 1. Raise exception if the object_version_number doesn't match.
1541 --------------------------------------------------------------------
1542 PROCEDURE Delete_Space (
1543 p_api_version IN NUMBER,
1544 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1545 p_commit IN VARCHAR2 := FND_API.g_false,
1546 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1547 p_space_rec IN ahl_ltp_space_capb_pub.Space_Rec,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER,
1550 x_msg_data OUT NOCOPY VARCHAR2
1551
1552 )
1553 IS
1554 --
1555 CURSOR c_space_cur
1556 (c_space_id IN NUMBER)
1557 IS
1558 SELECT space_id,object_version_number
1559 FROM ahl_spaces_vl
1560 WHERE space_id = c_space_id
1561 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1562 --
1563 CURSOR c_space_visit_cur (c_space_id IN NUMBER)
1564 IS
1565 SELECT visit_id FROM
1566 AHL_SPACE_ASSIGNMENTS
1567 WHERE space_id = c_space_id;
1568 --
1569 CURSOR get_status_cur(c_visit_id IN NUMBER)
1570 IS
1571 SELECT status_code FROM AHL_VISITS_B
1572 WHERE visit_id = c_visit_id;
1573
1574 --
1575 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SPACE';
1576 l_api_version CONSTANT NUMBER := 1.0;
1577 l_msg_count NUMBER;
1578 l_return_status VARCHAR2(1);
1579 l_msg_data VARCHAR2(2000);
1580 l_dummy NUMBER;
1581 l_space_id NUMBER;
1582 l_object_version_number NUMBER;
1583 l_visit_status_code VARCHAR2(30);
1584 l_visit_id NUMBER;
1585 --
1586 BEGIN
1587 --------------------Initialize ----------------------------------
1588 -- Standard Start of API savepoint
1589 SAVEPOINT delete_space;
1590 -- Check if API is called in debug mode. If yes, enable debug.
1591 IF G_DEBUG='Y' THEN
1592 AHL_DEBUG_PUB.enable_debug;
1593 END IF;
1594 -- Debug info.
1595 IF G_DEBUG='Y' THEN
1596 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Delete Space','+SPCBL+');
1597 END IF;
1598 -- Standard call to check for call compatibility.
1599 IF FND_API.to_boolean(p_init_msg_list)
1600 THEN
1601 FND_MSG_PUB.initialize;
1602 END IF;
1603 -- Initialize API return status to success
1604 x_return_status := FND_API.G_RET_STS_SUCCESS;
1605 -- Initialize message list if p_init_msg_list is set to TRUE.
1606 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1607 p_api_version,
1608 l_api_name,G_PKG_NAME)
1609 THEN
1610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1611 END IF;
1612 -----------------------Start of API Body-----------------------------
1613 -- Check for Record exists
1614 OPEN c_space_cur(p_space_rec.space_id);
1615 FETCH c_space_cur INTO l_space_id,
1616 l_object_version_number;
1617 IF c_space_cur%NOTFOUND THEN
1618 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1619 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
1620 FND_MSG_PUB.add;
1621 END IF;
1622 CLOSE c_space_cur;
1623 RAISE FND_API.g_exc_error;
1624 END IF;
1625 CLOSE c_space_cur;
1626 --Check for object version number
1627 IF l_object_version_number <> p_space_rec.object_version_number
1628 THEN
1629 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1630 FND_MSG_PUB.add;
1631 RAISE FND_API.g_exc_error;
1632 END IF;
1633 --Ckeck for any visits assigned
1634 OPEN c_space_visit_cur(l_space_id);
1635 LOOP
1636 FETCH c_space_visit_cur INTO l_visit_id;
1637 EXIT WHEN c_space_visit_cur%NOTFOUND;
1638 IF l_visit_id IS NOT NULL THEN
1639 --
1640 OPEN get_status_cur(l_visit_id);
1641 FETCH get_status_cur INTO l_visit_status_code;
1642 CLOSE get_status_cur;
1643 --
1644 IF l_visit_status_code <> 'CLOSED' THEN
1645 FND_MESSAGE.set_name('AHL', 'AHL_LTP_SP_VISITS_ASSIGNED');
1646 FND_MSG_PUB.add;
1647 RAISE Fnd_Api.G_EXC_ERROR;
1648 ELSE
1649 UPDATE AHL_SPACES_B
1650 SET INACTIVE_FLAG = 'N'
1651 WHERE space_id = l_space_id;
1652 END IF;
1653 END IF;
1654 END LOOP;
1655 CLOSE c_space_visit_cur;
1656 --
1657 --Standard check to count messages
1658 l_msg_count := Fnd_Msg_Pub.count_msg;
1659
1660 IF l_msg_count > 0 THEN
1661 X_msg_count := l_msg_count;
1662 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1663 RAISE Fnd_Api.G_EXC_ERROR;
1664 END IF;
1665
1666 -------------------Call Table handler generated procedure------------
1667 IF l_visit_id IS NULL THEN
1668 AHL_SPACES_PKG.DELETE_ROW (
1669 X_SPACE_ID => l_space_id
1670 );
1671 --
1672 DELETE FROM AHL_SPACE_CAPABILITIES
1673 WHERE SPACE_ID = l_space_id;
1674
1675 END IF;
1676 ---------------------------End of Body---------------------------------------
1677 --Standard check to count messages
1678 l_msg_count := Fnd_Msg_Pub.count_msg;
1679
1680 IF l_msg_count > 0 THEN
1681 X_msg_count := l_msg_count;
1682 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1683 RAISE Fnd_Api.G_EXC_ERROR;
1684 END IF;
1685
1686 --Standard check for commit
1687 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1688 COMMIT;
1689 END IF;
1690 -- Debug info
1691 IF G_DEBUG='Y' THEN
1692 Ahl_Debug_Pub.debug( 'End of private api Delete Space ','+SPCBL+');
1693 -- Check if API is called in debug mode. If yes, disable debug.
1694 Ahl_Debug_Pub.disable_debug;
1695 END IF;
1696 --
1697 EXCEPTION
1698 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1699 ROLLBACK TO delete_space;
1700 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1702 p_count => x_msg_count,
1703 p_data => x_msg_data);
1704 IF G_DEBUG='Y' THEN
1705 AHL_DEBUG_PUB.log_app_messages (
1706 x_msg_count, x_msg_data, 'ERROR' );
1707 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space ','+SPCBL+');
1708 -- Check if API is called in debug mode. If yes, disable debug.
1709 AHL_DEBUG_PUB.disable_debug;
1710 END IF;
1711 WHEN FND_API.G_EXC_ERROR THEN
1712 ROLLBACK TO delete_space;
1713 X_return_status := FND_API.G_RET_STS_ERROR;
1714 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1715 p_count => x_msg_count,
1716 p_data => X_msg_data);
1717 -- Debug info.
1718 IF G_DEBUG='Y' THEN
1719 AHL_DEBUG_PUB.log_app_messages (
1720 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1721 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space ','+SPCBL+');
1722 -- Check if API is called in debug mode. If yes, disable debug.
1723 AHL_DEBUG_PUB.disable_debug;
1724 END IF;
1725 WHEN OTHERS THEN
1726 ROLLBACK TO delete_space;
1727 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1729 THEN
1730 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_CAPB_PVT',
1731 p_procedure_name => 'DELETE_SPACE',
1732 p_error_text => SUBSTR(SQLERRM,1,240));
1733 END IF;
1734 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1735 p_count => x_msg_count,
1736 p_data => X_msg_data);
1737
1738 -- Debug info.
1739 IF G_DEBUG='Y' THEN
1740 AHL_DEBUG_PUB.log_app_messages (
1741 x_msg_count, x_msg_data, 'SQL ERROR' );
1742 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space','+SPCBL+');
1743 -- Check if API is called in debug mode. If yes, disable debug.
1744 AHL_DEBUG_PUB.disable_debug;
1745 END IF;
1746 END Delete_Space;
1747
1748 --------------------------------------------------------------------
1749 -- PROCEDURE
1750 -- CHECK_INVENTORY_DESC_OR_ID
1751 --
1752 -- PURPOSE
1753 -- Converts Inventory Item description to ID or Vice Versa
1754 --
1755 -- PARAMETERS
1756 --
1757 -- NOTES
1758 --------------------------------------------------------------------
1759 PROCEDURE Check_inventory_desc_Or_Id
1760 (p_organization_id IN NUMBER,
1761 p_org_name IN VARCHAR2,
1762 p_inventory_item_id IN NUMBER,
1763 p_item_description IN VARCHAR2,
1764 x_inventory_item_id OUT NOCOPY NUMBER,
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 x_error_msg_code OUT NOCOPY VARCHAR2
1767 )
1768 IS
1769 BEGIN
1770
1771 IF p_inventory_item_id IS NOT NULL
1772 THEN
1773 SELECT DISTINCT(inventory_item_id)
1774 INTO x_inventory_item_id
1775 FROM MTL_SYSTEM_ITEMS_B_KFV
1776 WHERE inventory_item_id = p_inventory_item_id;
1777 END IF;
1778 --
1779 IF p_item_description IS NOT NULL THEN
1780 SELECT DISTINCT(inventory_item_id)
1781 INTO x_inventory_item_id
1782 FROM MTL_SYSTEM_ITEMS_B_KFV
1783 WHERE concatenated_segments = p_item_description;
1784 END IF;
1785 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1786 IF G_DEBUG='Y' THEN
1787 AHL_DEBUG_PUB.debug( 'inside conevrt orgid'||p_organization_id);
1788 AHL_DEBUG_PUB.debug( 'iten id inside convert'||x_inventory_item_id);
1789 END IF;
1790 EXCEPTION
1791 WHEN NO_DATA_FOUND THEN
1792 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
1793 x_error_msg_code:= 'AHL_LTP_ITEM_NOT_EXISTS';
1794 WHEN TOO_MANY_ROWS THEN
1795 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
1796 x_error_msg_code:= 'AHL_LTP_ITEM_NOT_EXISTS';
1797 WHEN OTHERS THEN
1798 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
1799 RAISE;
1800 END Check_inventory_desc_Or_Id;
1801
1802 ---------------------------------------------------------------------
1803 -- PROCEDURE
1804 -- Assign_Space_Capbl_Rec
1805 --
1806 ---------------------------------------------------------------------
1807 PROCEDURE Assign_Space_Capbl_Rec (
1808 p_space_capbl_rec IN AHL_LTP_SPACE_CAPB_PUB.Space_capbl_rec,
1809 x_space_capbl_rec OUT NOCOPY Space_capbl_rec
1810 )
1811 IS
1812
1813 BEGIN
1814 x_space_capbl_rec.space_capability_id := p_space_capbl_rec.space_capability_id;
1815 x_space_capbl_rec.organization_id := p_space_capbl_rec.organization_id;
1816 x_space_capbl_rec.org_name := p_space_capbl_rec.org_name;
1817 x_space_capbl_rec.inventory_item_id := p_space_capbl_rec.inventory_item_id;
1818 x_space_capbl_rec.item_Description := p_space_capbl_rec.item_description;
1819 x_space_capbl_rec.space_name := p_space_capbl_rec.space_name;
1820 x_space_capbl_rec.space_id := p_space_capbl_rec.space_id;
1821 x_space_capbl_rec.visit_type_code := p_space_capbl_rec.visit_type_code;
1822 x_space_capbl_rec.visit_type_mean := p_space_capbl_rec.visit_type_mean;
1823 x_space_capbl_rec.object_version_number := p_space_capbl_rec.object_version_number;
1824 x_space_capbl_rec.attribute_category := p_space_capbl_rec.attribute_category;
1825 x_space_capbl_rec.attribute1 := p_space_capbl_rec.attribute1;
1826 x_space_capbl_rec.attribute2 := p_space_capbl_rec.attribute2;
1827 x_space_capbl_rec.attribute3 := p_space_capbl_rec.attribute3;
1828 x_space_capbl_rec.attribute4 := p_space_capbl_rec.attribute4;
1829 x_space_capbl_rec.attribute5 := p_space_capbl_rec.attribute5;
1830 x_space_capbl_rec.attribute6 := p_space_capbl_rec.attribute6;
1831 x_space_capbl_rec.attribute7 := p_space_capbl_rec.attribute7;
1832 x_space_capbl_rec.attribute8 := p_space_capbl_rec.attribute8;
1833 x_space_capbl_rec.attribute9 := p_space_capbl_rec.attribute9;
1834 x_space_capbl_rec.attribute10 := p_space_capbl_rec.attribute10;
1835 x_space_capbl_rec.attribute11 := p_space_capbl_rec.attribute11;
1836 x_space_capbl_rec.attribute12 := p_space_capbl_rec.attribute12;
1837 x_space_capbl_rec.attribute13 := p_space_capbl_rec.attribute13;
1838 x_space_capbl_rec.attribute14 := p_space_capbl_rec.attribute14;
1839 x_space_capbl_rec.attribute15 := p_space_capbl_rec.attribute15;
1840
1841 END Assign_Space_capbl_Rec;
1842 ---------------------------------------------------------------------
1843 -- PROCEDURE
1844 -- Complete_Space_Capbl_Rec
1845 --
1846 ---------------------------------------------------------------------
1847 PROCEDURE Complete_Space_Capbl_Rec (
1848 p_space_capbl_rec IN Space_capbl_rec,
1849 x_space_capbl_rec OUT NOCOPY Space_capbl_rec
1850 )
1851 IS
1852 CURSOR c_space_capbl_rec
1853 IS
1854 SELECT SPACE_CAPABILITY_ID,
1855 ORGANIZATION_ID,
1856 VISIT_TYPE,
1857 INVENTORY_ITEM_ID,
1858 SPACE_ID,
1859 OBJECT_VERSION_NUMBER,
1860 ATTRIBUTE_CATEGORY,
1861 ATTRIBUTE1,
1862 ATTRIBUTE2,
1863 ATTRIBUTE3,
1864 ATTRIBUTE4,
1865 ATTRIBUTE5,
1866 ATTRIBUTE6,
1867 ATTRIBUTE7,
1868 ATTRIBUTE8,
1869 ATTRIBUTE9,
1870 ATTRIBUTE10,
1871 ATTRIBUTE11,
1872 ATTRIBUTE12,
1873 ATTRIBUTE13,
1874 ATTRIBUTE14,
1875 ATTRIBUTE15
1876 FROM ahl_space_capabilities
1877 WHERE space_capability_id = p_space_capbl_rec.space_capability_id
1878 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1879 --
1880 -- This is the only exception for using %ROWTYPE.
1881 l_space_capbl_rec c_space_capbl_rec%ROWTYPE;
1882 BEGIN
1883 x_space_capbl_rec := p_space_capbl_rec;
1884 OPEN c_space_capbl_rec;
1885 FETCH c_space_capbl_rec INTO l_space_capbl_rec;
1886 IF c_space_capbl_rec%NOTFOUND THEN
1887 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1888 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
1889 FND_MSG_PUB.add;
1890 RAISE Fnd_Api.G_EXC_ERROR;
1891 END IF;
1892 END IF;
1893 CLOSE c_space_capbl_rec;
1894
1895
1896
1897 IF G_DEBUG='Y' THEN
1898 AHL_DEBUG_PUB.debug( 'inside complete orgid :'||p_space_capbl_rec.organization_id);
1899 AHL_DEBUG_PUB.debug( 'inside complete itemid :'||p_space_capbl_rec.inventory_item_id);
1900 AHL_DEBUG_PUB.debug( 'inside complete visit :'||p_space_capbl_rec.inventory_item_id);
1901 AHL_DEBUG_PUB.debug( 'inside complete itemid :'||p_space_capbl_rec.visit_type_code);
1902 AHL_DEBUG_PUB.debug( 'inside complete spaceid :'||p_space_capbl_rec.space_id);
1903 END IF;
1904
1905 --Check for object version number
1906 IF (l_space_capbl_rec.object_version_number <> p_space_capbl_rec.object_version_number)
1907 THEN
1908 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1909 Fnd_Msg_Pub.ADD;
1910 RAISE Fnd_Api.G_EXC_ERROR;
1911 END IF;
1912 -- SPACE ID
1913 IF p_space_capbl_rec.space_id <> FND_API.g_miss_num THEN
1914 x_space_capbl_rec.space_id := p_space_capbl_rec.space_id;
1915 ELSE
1916 x_space_capbl_rec.space_id := l_space_capbl_rec.space_id;
1917 END IF;
1918 -- ORGANIZATION_ID
1919 IF p_space_capbl_rec.organization_id <> FND_API.g_miss_num THEN
1920 x_space_capbl_rec.organization_id := p_space_capbl_rec.organization_id;
1921 ELSE
1922 x_space_capbl_rec.organization_id := l_space_capbl_rec.organization_id;
1923 END IF;
1924 -- VISIT TYPE
1925 IF p_space_capbl_rec.visit_type_code <> FND_API.g_miss_char THEN
1926 x_space_capbl_rec.visit_type_code := p_space_capbl_rec.visit_type_code;
1927 ELSE
1928 x_space_capbl_rec.visit_type_code := l_space_capbl_rec.visit_type;
1929 END IF;
1930 -- INVENTORY ITEM
1931 IF p_space_capbl_rec.inventory_item_id <> FND_API.g_miss_num THEN
1932 x_space_capbl_rec.inventory_item_id := p_space_capbl_rec.inventory_item_id;
1933 ELSE
1934 x_space_capbl_rec.inventory_item_id := l_space_capbl_rec.inventory_item_id;
1935 END IF;
1936 -- ATTRIBUTE CATEGORY
1937 IF p_space_capbl_rec.attribute_category = FND_API.g_miss_char THEN
1938 x_space_capbl_rec.attribute_category := l_space_capbl_rec.attribute_category;
1939 END IF;
1940 -- ATTRIBUTE 1
1941 IF p_space_capbl_rec.attribute1 = FND_API.g_miss_char THEN
1942 x_space_capbl_rec.attribute1 := l_space_capbl_rec.attribute1;
1943 END IF;
1944 -- ATTRIBUTE 2
1945 IF p_space_capbl_rec.attribute2 = FND_API.g_miss_char THEN
1946 x_space_capbl_rec.attribute2 := l_space_capbl_rec.attribute2;
1947 END IF;
1948 -- ATTRIBUTE 3
1949 IF p_space_capbl_rec.attribute3 = FND_API.g_miss_char THEN
1950 x_space_capbl_rec.attribute3 := l_space_capbl_rec.attribute3;
1951 END IF;
1952 -- ATTRIBUTE 4
1953 IF p_space_capbl_rec.attribute4 = FND_API.g_miss_char THEN
1954 x_space_capbl_rec.attribute4 := l_space_capbl_rec.attribute4;
1955 END IF;
1956 -- ATTRIBUTE 5
1957 IF p_space_capbl_rec.attribute5 = FND_API.g_miss_char THEN
1958 x_space_capbl_rec.attribute5 := l_space_capbl_rec.attribute5;
1959 END IF;
1960 -- ATTRIBUTE 6
1961 IF p_space_capbl_rec.attribute6 = FND_API.g_miss_char THEN
1962 x_space_capbl_rec.attribute6 := l_space_capbl_rec.attribute6;
1963 END IF;
1964 -- ATTRIBUTE 7
1965 IF p_space_capbl_rec.attribute7 = FND_API.g_miss_char THEN
1966 x_space_capbl_rec.attribute7 := l_space_capbl_rec.attribute7;
1967 END IF;
1968 -- ATTRIBUTE 8
1969 IF p_space_capbl_rec.attribute8 = FND_API.g_miss_char THEN
1970 x_space_capbl_rec.attribute8 := l_space_capbl_rec.attribute8;
1971 END IF;
1972 -- ATTRIBUTE 9
1973 IF p_space_capbl_rec.attribute9 = FND_API.g_miss_char THEN
1974 x_space_capbl_rec.attribute9 := l_space_capbl_rec.attribute9;
1975 END IF;
1976 -- ATTRIBUTE 10
1977 IF p_space_capbl_rec.attribute10 = FND_API.g_miss_char THEN
1978 x_space_capbl_rec.attribute10 := l_space_capbl_rec.attribute10;
1979 END IF;
1980 -- ATTRIBUTE 11
1981 IF p_space_capbl_rec.attribute11 = FND_API.g_miss_char THEN
1982 x_space_capbl_rec.attribute11 := l_space_capbl_rec.attribute11;
1983 END IF;
1984 -- ATTRIBUTE 12
1985 IF p_space_capbl_rec.attribute12 = FND_API.g_miss_char THEN
1986 x_space_capbl_rec.attribute12 := l_space_capbl_rec.attribute12;
1987 END IF;
1988 -- ATTRIBUTE 13
1989 IF p_space_capbl_rec.attribute13 = FND_API.g_miss_char THEN
1990 x_space_capbl_rec.attribute13 := l_space_capbl_rec.attribute13;
1991 END IF;
1992 -- ATTRIBUTE 14
1993 IF p_space_capbl_rec.attribute14 = FND_API.g_miss_char THEN
1994 x_space_capbl_rec.attribute14 := l_space_capbl_rec.attribute14;
1995 END IF;
1996 -- ATTRIBUTE 15
1997 IF p_space_capbl_rec.attribute15 = FND_API.g_miss_char THEN
1998 x_space_capbl_rec.attribute15 := l_space_capbl_rec.attribute15;
1999 END IF;
2000
2001 IF G_DEBUG='Y' THEN
2002 AHL_DEBUG_PUB.debug( 'end complete orgid :'||x_space_capbl_rec.organization_id);
2003 AHL_DEBUG_PUB.debug( 'end complete itemid :'||x_space_capbl_rec.inventory_item_id);
2004 AHL_DEBUG_PUB.debug( 'end complete visit :'||x_space_capbl_rec.inventory_item_id);
2005 AHL_DEBUG_PUB.debug( 'end complete itemid :'||x_space_capbl_rec.visit_type_code);
2006 AHL_DEBUG_PUB.debug( 'end complete spaceid :'||x_space_capbl_rec.space_id);
2007 END IF;
2008
2009 END Complete_Space_Capbl_Rec;
2010
2011 ------------------------------------------------------------------------------
2012 --
2013 -- NAME
2014 -- Validate_Space_Capbl_Items
2015 --
2016 -- PURPOSE
2017 -- This procedure is to validate Space Capability attributes
2018 -- End of Comments
2019 -------------------------------------------------------------------------------
2020 PROCEDURE Validate_Space_Capbl_Items
2021 ( p_space_capbl_rec IN space_capbl_rec,
2022 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
2023 x_return_status OUT NOCOPY VARCHAR2
2024 ) IS
2025 l_table_name VARCHAR2(30);
2026 l_pk_name VARCHAR2(30);
2027 l_pk_value VARCHAR2(30);
2028 l_where_clause VARCHAR2(2000);
2029 l_dummy VARCHAR2(10);
2030
2031
2032 CURSOR check_unique_cur (c_organization_id IN NUMBER,
2033 c_visit_type IN VARCHAR2,
2034 c_inventory_item_code IN VARCHAR2,
2035 c_space_id IN NUMBER)
2036 IS
2037 SELECT 'X'
2038 FROM AHL_SPACE_CAPABILITIES
2039 WHERE ORGANIZATION_ID = c_organization_id
2040 AND VISIT_TYPE = c_visit_type
2041 AND INVENTORY_ITEM_ID = c_inventory_item_code
2042 AND SPACE_ID = c_space_id;
2043
2044
2045 BEGIN
2046 -- Initialize API/Procedure return status to success
2047 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2048 -- Check required parameters
2049 IF (p_space_capbl_rec.ORGANIZATION_ID IS NULL OR
2050 p_space_capbl_rec.ORGANIZATION_ID = Fnd_Api.G_MISS_NUM )
2051 THEN
2052 -- missing required fields
2053 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2054 THEN
2055 Fnd_Message.set_name('AHL', 'AHL_LTP_ORG_ID_NOT_EXISTS');
2056 Fnd_Msg_Pub.ADD;
2057 END IF;
2058 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2059 END IF;
2060 -- SPACE_ID
2061 IF (p_space_capbl_rec.SPACE_ID = Fnd_Api.G_MISS_NUM OR
2062 p_space_capbl_rec.SPACE_ID IS NULL)
2063 THEN
2064 -- missing required fields
2065 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2066 THEN
2067 Fnd_Message.set_name('AHL', 'AHL_LTP_DEPT_ID_NOT_EXIST');
2068 Fnd_Msg_Pub.ADD;
2069 END IF;
2070 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2071 END IF;
2072 --VISIT TYPE
2073 IF (p_space_capbl_rec.VISIT_TYPE_CODE = Fnd_Api.G_MISS_CHAR OR
2074 p_space_capbl_rec.VISIT_TYPE_CODE IS NULL)
2075 THEN
2076 -- missing required fields
2077 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2078 THEN
2079 Fnd_Message.set_name('AHL', 'AHL_LTP_VISIT_TYPE_NOT_EXIST');
2080 Fnd_Msg_Pub.ADD;
2081 END IF;
2082 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2083 END IF;
2084
2085 --INVENTORY ITEM
2086 IF (p_space_capbl_rec.INVENTORY_ITEM_ID = Fnd_Api.G_MISS_NUM OR
2087 p_space_capbl_rec.INVENTORY_ITEM_ID IS NULL)
2088 THEN
2089 -- missing required fields
2090 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
2091 THEN
2092 Fnd_Message.set_name('AHL', 'AHL_LTP_INV_ITEM_NOT_EXIST');
2093 Fnd_Msg_Pub.ADD;
2094 END IF;
2095 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2096 END IF;
2097
2098 -- Validate uniqueness
2099 /* IF p_validation_mode = Jtf_Plsql_Api.g_create
2100 AND p_space_capbl_rec.visit_type_code IS NOT NULL
2101 THEN
2102 IF Ahl_Utility_Pvt.check_uniqueness(
2103 'ahl_space_capabilities',
2104 'visit_type = ' || p_space_capbl_rec.visit_type_code ||
2105 ''' AND inventory_item_id = ''' || p_space_capbl_rec.inventory_item_id
2106 ) = Fnd_Api.g_false
2107 THEN
2108 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error)
2109 THEN
2110 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_CAPBL_DUPL_ID');
2111 Fnd_Msg_Pub.ADD;
2112 END IF;
2113 x_return_status := Fnd_Api.g_ret_sts_error;
2114 END IF;
2115 END IF; */
2116
2117 IF G_DEBUG='Y' THEN
2118 AHL_DEBUG_PUB.debug( 'org id invalidation:'||p_space_capbl_rec.organization_id);
2119 AHL_DEBUG_PUB.debug( 'item id valid 1:'||p_space_capbl_rec.inventory_item_id);
2120 AHL_DEBUG_PUB.debug( 'visit type 1:'||p_space_capbl_rec.visit_type_code);
2121 AHL_DEBUG_PUB.debug( 'space valid 1:'||p_space_capbl_rec.space_id);
2122 END IF;
2123
2124 --Check for Unique Record
2125 IF p_space_capbl_rec.visit_type_code IS NOT NULL THEN
2126 IF p_space_capbl_rec.inventory_item_id IS NOT NULL THEN
2127
2128 OPEN check_unique_cur(p_space_capbl_rec.organization_id,
2129 p_space_capbl_rec.visit_type_code,
2130 p_space_capbl_rec.inventory_item_id,
2131 p_space_capbl_rec.space_id);
2132 FETCH check_unique_cur INTO l_dummy;
2133 IF l_dummy is NOT NULL THEN
2134 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_CAPBL_DUPL_ID');
2135 Fnd_Msg_Pub.ADD;
2136 END IF;
2137 x_return_status := Fnd_Api.g_ret_sts_error;
2138 END IF;
2139 END IF;
2140
2141
2142 END Validate_Space_Capbl_Items;
2143 ----------------------------------------------------------------------------
2144 -- NAME
2145 -- Validate_Space_Capbl_Record
2146 --
2147 -- PURPOSE
2148 -- This procedure is to validate Space Restriction record
2149 --
2150 -- NOTES
2151 -- End of Comments
2152 -----------------------------------------------------------------------------
2153 PROCEDURE Validate_Space_Capbl_Record(
2154 p_space_capbl_rec IN space_capbl_rec,
2155 x_return_status OUT NOCOPY VARCHAR2
2156 ) IS
2157 -- Status Local Variables
2158 l_return_status VARCHAR2(1);
2159 BEGIN
2160 -- Initialize API return status to success
2161 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2162 --
2163 NULL;
2164 --
2165 END Validate_Space_Capbl_Record;
2166
2167 --------------------------------------------------------------------
2168 -- PROCEDURE
2169 -- Validate_Space_Capblts
2170 --
2171 -- PURPOSE
2172 -- Validate space capabilities attributes
2173 --
2174 -- PARAMETERS
2175 --
2176 -- NOTES
2177 --
2178 --------------------------------------------------------------------
2179 PROCEDURE Validate_Space_Capblts
2180 ( p_api_version IN NUMBER,
2181 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2182 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2183 p_space_capblts_rec IN space_capbl_rec,
2184 x_return_status OUT NOCOPY VARCHAR2,
2185 x_msg_count OUT NOCOPY NUMBER,
2186 x_msg_data OUT NOCOPY VARCHAR2
2187 )
2188 IS
2189 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Space_Capblts';
2190 l_api_version CONSTANT NUMBER := 1.0;
2191 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2192 l_return_status VARCHAR2(1);
2193 l_space_capblts_rec space_capbl_rec;
2194 BEGIN
2195 -- Standard call to check for call compatibility.
2196 IF NOT Fnd_Api.Compatible_API_Call ( l_api_version,
2197 p_api_version,
2198 l_api_name,
2199 G_PKG_NAME)
2200 THEN
2201 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2202 END IF;
2203 -- Initialize message list if p_init_msg_list is set to TRUE.
2204 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
2205 Fnd_Msg_Pub.initialize;
2206 END IF;
2207 -- Initialize API return status to success
2208 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2209 --
2210 -- API body
2211 --
2212 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item
2213 THEN
2214 Validate_Space_capbl_Items
2215 ( p_space_capbl_rec => p_space_capblts_rec,
2216 p_validation_mode => Jtf_Plsql_Api.g_create,
2217 x_return_status => l_return_status
2218 );
2219 -- If any errors happen abort API.
2220 IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
2221 THEN
2222 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2223 ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
2224 THEN
2225 RAISE Fnd_Api.G_EXC_ERROR;
2226 END IF;
2227 END IF;
2228 -- Perform cross attribute validation and missing attribute checks. Record
2229 -- level validation.
2230 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record
2231 THEN
2232 Validate_Space_Capbl_Record(
2233 p_space_capbl_rec => p_space_capblts_rec,
2234 x_return_status => l_return_status
2235
2236 );
2237 IF l_return_status = Fnd_Api.G_RET_STS_ERROR
2238 THEN
2239 RAISE Fnd_Api.G_EXC_ERROR;
2240 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
2241 THEN
2242 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2243 END IF;
2244 END IF;
2245 --
2246 -- END of API body.
2247 --
2248 -------------------- finish --------------------------
2249 Fnd_Msg_Pub.count_and_get(
2250 p_encoded => Fnd_Api.g_false,
2251 p_count => x_msg_count,
2252 p_data => x_msg_data);
2253 EXCEPTION
2254 WHEN Fnd_Api.G_EXC_ERROR THEN
2255 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
2256 Fnd_Msg_Pub.Count_AND_Get
2257 ( p_count => x_msg_count,
2258 p_data => x_msg_data,
2259 p_encoded => Fnd_Api.G_FALSE
2260 );
2261 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2262 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
2263 Fnd_Msg_Pub.Count_AND_Get
2264 ( p_count => x_msg_count,
2265 p_data => x_msg_data,
2266 p_encoded => Fnd_Api.G_FALSE
2267 );
2268 WHEN OTHERS THEN
2269 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
2270 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2271 THEN
2272 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2273 END IF;
2274 Fnd_Msg_Pub.Count_AND_Get
2275 ( p_count => x_msg_count,
2276 p_data => x_msg_data,
2277 p_encoded => Fnd_Api.G_FALSE
2278 );
2279 END Validate_Space_Capblts;
2280
2281 --------------------------------------------------------------------
2282 -- PROCEDURE
2283 -- Create_Space_Capblts
2284 --
2285 -- PURPOSE
2286 -- Create Space Capabilities Record
2287 --
2288 -- PARAMETERS
2289 -- p_x_space_capblts_rec: the record representing AHL_SPACE_CAPABILITIES table..
2290 --
2291 -- NOTES
2292 --------------------------------------------------------------------
2293 PROCEDURE Create_Space_Capblts (
2294 p_api_version IN NUMBER,
2295 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2296 p_commit IN VARCHAR2 := FND_API.g_false,
2297 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2298 p_module_type IN VARCHAR2 := 'JSP',
2299 p_x_space_capblts_rec IN OUT NOCOPY ahl_ltp_space_capb_pub.Space_Capbl_Rec,
2300 x_return_status OUT NOCOPY VARCHAR2,
2301 x_msg_count OUT NOCOPY NUMBER,
2302 x_msg_data OUT NOCOPY VARCHAR2
2303 )
2304 IS
2305
2306 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SPACE_CAPBLTS';
2307 l_api_version CONSTANT NUMBER := 1.0;
2308 l_msg_count NUMBER;
2309 l_return_status VARCHAR2(1);
2310 l_msg_data VARCHAR2(2000);
2311 l_dummy NUMBER;
2312 l_rowid VARCHAR2(30);
2313 l_organization_id NUMBER;
2314 l_inventory_item_id NUMBER;
2315 l_space_id NUMBER;
2316 l_space_capability_id NUMBER;
2317 l_space_capblts_rec Space_Capbl_Rec;
2318 --
2319 CURSOR c_seq
2320 IS
2321 SELECT AHL_SPACE_CAPABILITIES_S.NEXTVAL
2322 FROM dual;
2323 --
2324 CURSOR c_id_exists (x_id IN NUMBER) IS
2325 SELECT 1
2326 FROM dual
2327 WHERE EXISTS (SELECT 1
2328 FROM ahl_space_capabilities
2329 WHERE space_capability_id = x_id);
2330 --Get organization id
2331 CURSOR get_org_cur (c_space_id IN NUMBER)
2332 IS
2333 SELECT organization_id
2334 FROM AHL_SPACES_B
2335 WHERE SPACE_ID = c_space_id;
2336
2337 BEGIN
2338
2339 --------------------Initialize ----------------------------------
2340 -- Standard Start of API savepoint
2341 SAVEPOINT create_space_capblts;
2342 -- Check if API is called in debug mode. If yes, enable debug.
2343 IF G_DEBUG='Y' THEN
2344 AHL_DEBUG_PUB.enable_debug;
2345 END IF;
2346 -- Debug info.
2347 IF G_DEBUG='Y' THEN
2348 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Create Space Capblts ','+SPCBL+');
2349 END IF;
2350 -- Standard call to check for call compatibility.
2351 IF FND_API.to_boolean(p_init_msg_list)
2352 THEN
2353 FND_MSG_PUB.initialize;
2354 END IF;
2355 -- Initialize API return status to success
2356 x_return_status := FND_API.G_RET_STS_SUCCESS;
2357 -- Initialize message list if p_init_msg_list is set to TRUE.
2358 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2359 p_api_version,
2360 l_api_name,G_PKG_NAME)
2361 THEN
2362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2363 END IF;
2364
2365 IF G_DEBUG='Y' THEN
2366 AHL_DEBUG_PUB.debug( 'org name 1:'||p_x_space_capblts_rec.org_name);
2367 AHL_DEBUG_PUB.debug( 'item desc 1:'||p_x_space_capblts_rec.item_Description);
2368 AHL_DEBUG_PUB.debug( 'visit type 1:'||p_x_space_capblts_rec.visit_type_code);
2369 AHL_DEBUG_PUB.debug( 'space 1:'||p_x_space_capblts_rec.space_name);
2370 END IF;
2371
2372 --------------------Value OR ID conversion---------------------------
2373 --Start API Body
2374 IF p_module_type = 'JSP'
2375 THEN
2376 p_x_space_capblts_rec.organization_id := null;
2377 p_x_space_capblts_rec.inventory_item_id := null;
2378 END IF;
2379
2380
2381 -- Convert Space name to space id
2382 IF (p_x_space_capblts_rec.space_name IS NOT NULL AND
2383 p_x_space_capblts_rec.space_name <> FND_API.G_MISS_CHAR ) OR
2384 (p_x_space_capblts_rec.space_id IS NOT NULL AND
2385 p_x_space_capblts_rec.space_id <> FND_API.G_MISS_NUM) THEN
2386
2387 Check_space_name_Or_Id
2388 (p_space_id => p_x_space_capblts_rec.space_id,
2389 p_space_name => p_x_space_capblts_rec.space_name,
2390 x_space_id => l_space_id,
2391 x_return_status => l_return_status,
2392 x_error_msg_code => l_msg_data);
2393
2394 IF NVL(l_return_status,'x') <> 'S'
2395 THEN
2396 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_NOT_EXISTS');
2397 Fnd_Message.SET_TOKEN('ORGID',p_x_space_capblts_rec.space_name);
2398 Fnd_Msg_Pub.ADD;
2399 END IF;
2400 END IF;
2401 --Assign the returned value
2402 p_x_space_capblts_rec.space_id := l_space_id;
2403 /*
2404 -- Convert org name to organization id
2405 IF (p_x_space_capblts_rec.org_name IS NOT NULL AND
2406 p_x_space_capblts_rec.org_name <> FND_API.G_MISS_CHAR ) OR
2407 (p_x_space_capblts_rec.organization_id IS NOT NULL AND
2408 p_x_space_capblts_rec.organization_id <> FND_API.G_MISS_NUM) THEN
2409
2410 Check_org_name_Or_Id
2411 (p_organization_id => p_x_space_capblts_rec.organization_id,
2412 p_org_name => p_x_space_capblts_rec.org_name,
2413 x_organization_id => l_organization_id,
2414 x_return_status => l_return_status,
2415 x_error_msg_code => l_msg_data);
2416
2417 IF NVL(l_return_status,'x') <> 'S'
2418 THEN
2419 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_NOT_EXISTS');
2420 Fnd_Message.SET_TOKEN('ORGID',p_x_space_capblts_rec.org_name);
2421 Fnd_Msg_Pub.ADD;
2422 END IF;
2423 END IF;
2424 --Assign the returned value
2425 p_x_space_capblts_rec.organization_id := l_organization_id;
2426 */
2427
2428 IF G_DEBUG='Y' THEN
2429 AHL_DEBUG_PUB.debug( 'org id '||p_x_space_capblts_rec.organization_id);
2430 AHL_DEBUG_PUB.debug( 'ietm desc '||p_x_space_capblts_rec.item_description);
2431 AHL_DEBUG_PUB.debug( 'space id '||p_x_space_capblts_rec.space_id);
2432 END IF;
2433 --Get organization id
2434 OPEN get_org_cur(l_space_id);
2435 FETCH get_org_cur INTO l_organization_id;
2436 CLOSE get_org_cur;
2437
2438 --Assign the returned value
2439 p_x_space_capblts_rec.organization_id := l_organization_id;
2440
2441 -- Get Inventory item id
2442 IF (p_x_space_capblts_rec.item_description IS NOT NULL AND
2443 p_x_space_capblts_rec.item_description <> FND_API.G_MISS_CHAR ) OR
2444 (p_x_space_capblts_rec.inventory_item_id IS NOT NULL AND
2445 p_x_space_capblts_rec.inventory_item_id <> FND_API.G_MISS_NUM) THEN
2446
2447 Check_inventory_desc_Or_Id
2448 (p_organization_id => null, --p_x_space_capblts_rec.organization_id,
2449 p_org_name => null, --p_x_space_capblts_rec.org_name,
2450 p_item_description => p_x_space_capblts_rec.item_description,
2451 p_inventory_item_id => p_x_space_capblts_rec.inventory_item_id,
2452 x_inventory_item_id => l_inventory_item_id,
2453 x_return_status => l_return_status,
2454 x_error_msg_code => l_msg_data);
2455
2456 IF NVL(l_return_status,'x') <> 'S'
2457 THEN
2458 Fnd_Message.SET_NAME('AHL','AHL_LTP_ITEM_NOT_EXISTS');
2459 Fnd_Message.SET_TOKEN('ITEM',p_x_space_capblts_rec.item_description);
2460 Fnd_Msg_Pub.ADD;
2461 END IF;
2462 --Assign the returned value
2463 p_x_space_capblts_rec.inventory_item_id := l_inventory_item_id;
2464
2465 END IF;
2466 --Assign the returned value
2467 -- p_x_space_capblts_rec.inventory_item_id := l_inventory_item_id;
2468
2469 IF G_DEBUG='Y' THEN
2470 AHL_DEBUG_PUB.debug( 'item id '||l_inventory_item_id);
2471 AHL_DEBUG_PUB.debug( 'visit type '||p_x_space_capblts_rec.visit_type_code);
2472 END IF;
2473
2474 --For Visit Type
2475 IF p_x_space_capblts_rec.visit_type_mean IS NOT NULL AND
2476 p_x_space_capblts_rec.visit_type_mean <> Fnd_Api.G_MISS_CHAR
2477 THEN
2478 Check_lookup_name_Or_Id (
2479 p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
2480 p_lookup_code => NULL,
2481 p_meaning => p_x_space_capblts_rec.visit_type_mean,
2482 p_check_id_flag => 'Y',
2483 x_lookup_code => l_space_capblts_rec.visit_type_code,
2484 x_return_status => l_return_status);
2485
2486 IF NVL(l_return_status, 'X') <> 'S'
2487 THEN
2488 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_NOT_EXISTS');
2489 Fnd_Message.SET_TOKEN('VISIT',p_x_space_capblts_rec.visit_type_mean);
2490 Fnd_Msg_Pub.ADD;
2491 END IF;
2492 -- END IF;
2493 -- Id presents
2494 ELSIF p_x_space_capblts_rec.visit_type_code IS NOT NULL AND
2495 p_x_space_capblts_rec.visit_type_code <> Fnd_Api.G_MISS_CHAR
2496 THEN
2497 l_space_capblts_rec.visit_type_code := p_x_space_capblts_rec.visit_type_code;
2498 ELSE
2499 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_NOT_EXIST');
2500 Fnd_Msg_Pub.ADD;
2501 END IF;
2502 -- Assign return value
2503 p_x_space_capblts_rec.visit_type_code := l_space_capblts_rec.visit_type_code;
2504
2505 IF G_DEBUG='Y' THEN
2506 AHL_DEBUG_PUB.debug( 'after space id '||p_x_space_capblts_rec.space_id);
2507 AHL_DEBUG_PUB.debug( 'after visit type '||l_space_capblts_rec.visit_type_code);
2508 AHL_DEBUG_PUB.debug( 'after item id '||p_x_space_capblts_rec.inventory_item_id);
2509
2510 END IF;
2511
2512 --Standard check to count messages
2513 l_msg_count := Fnd_Msg_Pub.count_msg;
2514
2515 IF l_msg_count > 0 THEN
2516 X_msg_count := l_msg_count;
2517 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2518 RAISE Fnd_Api.G_EXC_ERROR;
2519 END IF;
2520
2521 --------------------------------Validation ---------------------------
2522 --Assign to local variable
2523 Assign_Space_capbl_Rec (
2524 p_space_capbl_rec => p_x_space_capblts_rec,
2525 x_space_capbl_rec => l_space_capblts_rec
2526 );
2527
2528 IF G_DEBUG='Y' THEN
2529 AHL_DEBUG_PUB.debug( 'after assign space id '||l_space_capblts_rec.space_id);
2530 AHL_DEBUG_PUB.debug( 'after visit type '||l_space_capblts_rec.visit_type_code);
2531 AHL_DEBUG_PUB.debug( 'after assign item '||l_space_capblts_rec.inventory_item_id);
2532 END IF;
2533
2534 -- Call Validate space rec input attributes
2535
2536 Validate_Space_Capblts
2537 ( p_api_version => l_api_version,
2538 p_init_msg_list => p_init_msg_list,
2539 p_validation_level => p_validation_level,
2540 p_space_capblts_rec => l_space_capblts_rec,
2541 x_return_status => l_return_status,
2542 x_msg_count => l_msg_count,
2543 x_msg_data => l_msg_data );
2544
2545 IF (p_x_space_capblts_rec.space_capability_id = Fnd_Api.G_MISS_NUM OR
2546 p_x_space_capblts_rec.space_capability_id IS NULL )
2547 THEN
2548 --
2549 -- If the ID is not passed into the API, then
2550 -- grab a value from the sequence.
2551 OPEN c_seq;
2552 FETCH c_seq INTO l_space_capability_id;
2553 CLOSE c_seq;
2554 --
2555 -- Check to be sure that the sequence does not exist.
2556 OPEN c_id_exists (l_space_capability_id);
2557 FETCH c_id_exists INTO l_dummy;
2558 CLOSE c_id_exists;
2559 --
2560 -- If the value for the ID already exists, then
2561 -- l_dummy would be populated with '1', otherwise,
2562 -- it receives NULL.
2563 IF l_dummy IS NOT NULL THEN
2564 Fnd_Message.SET_NAME('AHL','AHL_LTP_SEQUENCE_NOT_EXISTS');
2565 Fnd_Msg_Pub.ADD;
2566 END IF;
2567 -- For optional fields
2568 --
2569 IF p_x_space_capblts_rec.attribute_category = FND_API.G_MISS_CHAR
2570 THEN
2571 l_space_capblts_rec.attribute_category := NULL;
2572 ELSE
2573 l_space_capblts_rec.attribute_category := p_x_space_capblts_rec.attribute_category;
2574 END IF;
2575 --
2576 IF p_x_space_capblts_rec.attribute1 = FND_API.G_MISS_CHAR
2577 THEN
2578 l_space_capblts_rec.attribute1 := NULL;
2579 ELSE
2580 l_space_capblts_rec.attribute1 := p_x_space_capblts_rec.attribute1;
2581 END IF;
2582 --
2583 IF p_x_space_capblts_rec.attribute2 = FND_API.G_MISS_CHAR
2584 THEN
2585 l_space_capblts_rec.attribute2 := NULL;
2586 ELSE
2587 l_space_capblts_rec.attribute2 := p_x_space_capblts_rec.attribute2;
2588 END IF;
2589 --
2590 IF p_x_space_capblts_rec.attribute3 = FND_API.G_MISS_CHAR
2591 THEN
2592 l_space_capblts_rec.attribute3 := NULL;
2593 ELSE
2594 l_space_capblts_rec.attribute3 := p_x_space_capblts_rec.attribute3;
2595 END IF;
2596 --
2597 IF p_x_space_capblts_rec.attribute4 = FND_API.G_MISS_CHAR
2598 THEN
2599 l_space_capblts_rec.attribute4 := NULL;
2600 ELSE
2601 l_space_capblts_rec.attribute4 := p_x_space_capblts_rec.attribute4;
2602 END IF;
2603 --
2604 IF p_x_space_capblts_rec.attribute5 = FND_API.G_MISS_CHAR
2605 THEN
2606 l_space_capblts_rec.attribute5 := NULL;
2607 ELSE
2608 l_space_capblts_rec.attribute5 := p_x_space_capblts_rec.attribute5;
2609 END IF;
2610 --
2611 IF p_x_space_capblts_rec.attribute6 = FND_API.G_MISS_CHAR
2612 THEN
2613 l_space_capblts_rec.attribute6 := NULL;
2614 ELSE
2615 l_space_capblts_rec.attribute6 := p_x_space_capblts_rec.attribute6;
2616 END IF;
2617 --
2618 IF p_x_space_capblts_rec.attribute7 = FND_API.G_MISS_CHAR
2619 THEN
2620 l_space_capblts_rec.attribute7 := NULL;
2621 ELSE
2622 l_space_capblts_rec.attribute7 := p_x_space_capblts_rec.attribute7;
2623 END IF;
2624 --
2625 IF p_x_space_capblts_rec.attribute8 = FND_API.G_MISS_CHAR
2626 THEN
2627 l_space_capblts_rec.attribute8 := NULL;
2628 ELSE
2629 l_space_capblts_rec.attribute8 := p_x_space_capblts_rec.attribute8;
2630 END IF;
2631 --
2632 IF p_x_space_capblts_rec.attribute9 = FND_API.G_MISS_CHAR
2633 THEN
2634 l_space_capblts_rec.attribute9 := NULL;
2635 ELSE
2636 l_space_capblts_rec.attribute9 := p_x_space_capblts_rec.attribute9;
2637 END IF;
2638 --
2639 IF p_x_space_capblts_rec.attribute10 = FND_API.G_MISS_CHAR
2640 THEN
2641 l_space_capblts_rec.attribute10 := NULL;
2642 ELSE
2643 l_space_capblts_rec.attribute10 := p_x_space_capblts_rec.attribute10;
2644 END IF;
2645 --
2646 IF p_x_space_capblts_rec.attribute11 = FND_API.G_MISS_CHAR
2647 THEN
2648 l_space_capblts_rec.attribute11 := NULL;
2649 ELSE
2650 l_space_capblts_rec.attribute11 := p_x_space_capblts_rec.attribute11;
2651 END IF;
2652 --
2653 IF p_x_space_capblts_rec.attribute12 = FND_API.G_MISS_CHAR
2654 THEN
2655 l_space_capblts_rec.attribute12 := NULL;
2656 ELSE
2657 l_space_capblts_rec.attribute12 := p_x_space_capblts_rec.attribute12;
2658 END IF;
2659 --
2660 IF p_x_space_capblts_rec.attribute13 = FND_API.G_MISS_CHAR
2661 THEN
2662 l_space_capblts_rec.attribute13 := NULL;
2663 ELSE
2664 l_space_capblts_rec.attribute13 := p_x_space_capblts_rec.attribute13;
2665 END IF;
2666 --
2667 IF p_x_space_capblts_rec.attribute14 = FND_API.G_MISS_CHAR
2668 THEN
2669 l_space_capblts_rec.attribute14 := NULL;
2670 ELSE
2671 l_space_capblts_rec.attribute14 := p_x_space_capblts_rec.attribute14;
2672 END IF;
2673 --
2674 IF p_x_space_capblts_rec.attribute15 = FND_API.G_MISS_CHAR
2675 THEN
2676 l_space_capblts_rec.attribute15 := NULL;
2677 ELSE
2678 l_space_capblts_rec.attribute15 := p_x_space_capblts_rec.attribute15;
2679 END IF;
2680 END IF;
2681
2682 --Standard check to count messages
2683 l_msg_count := Fnd_Msg_Pub.count_msg;
2684
2685 IF l_msg_count > 0 THEN
2686 X_msg_count := l_msg_count;
2687 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2688 RAISE Fnd_Api.G_EXC_ERROR;
2689 END IF;
2690
2691 ----------------------------DML Operation---------------------------------
2692 --insert space capabilities record
2693
2694 INSERT INTO AHL_SPACE_CAPABILITIES
2695 (
2696 SPACE_CAPABILITY_ID,
2697 VISIT_TYPE,
2698 INVENTORY_ITEM_ID,
2699 ORGANIZATION_ID,
2700 SPACE_ID,
2701 OBJECT_VERSION_NUMBER,
2702 ATTRIBUTE_CATEGORY,
2703 ATTRIBUTE1,
2704 ATTRIBUTE2,
2705 ATTRIBUTE3,
2706 ATTRIBUTE4,
2707 ATTRIBUTE5,
2708 ATTRIBUTE6,
2709 ATTRIBUTE7,
2710 ATTRIBUTE8,
2711 ATTRIBUTE9,
2712 ATTRIBUTE10,
2713 ATTRIBUTE11,
2714 ATTRIBUTE12,
2715 ATTRIBUTE13,
2716 ATTRIBUTE14,
2717 ATTRIBUTE15,
2718 LAST_UPDATE_DATE,
2719 LAST_UPDATED_BY,
2720 CREATION_DATE,
2721 CREATED_BY,
2722 LAST_UPDATE_LOGIN
2723 )
2724 VALUES
2725 (
2726 l_space_capability_id,
2727 l_space_capblts_rec.visit_type_code,
2728 l_space_capblts_rec.inventory_item_id,
2729 p_x_space_capblts_rec.organization_id,
2730 l_space_capblts_rec.space_id,
2731 1,
2732 l_space_capblts_rec.attribute_category,
2733 l_space_capblts_rec.attribute1,
2734 l_space_capblts_rec.attribute2,
2735 l_space_capblts_rec.attribute3,
2736 l_space_capblts_rec.attribute4,
2737 l_space_capblts_rec.attribute5,
2738 l_space_capblts_rec.attribute6,
2739 l_space_capblts_rec.attribute7,
2740 l_space_capblts_rec.attribute8,
2741 l_space_capblts_rec.attribute9,
2742 l_space_capblts_rec.attribute10,
2743 l_space_capblts_rec.attribute11,
2744 l_space_capblts_rec.attribute12,
2745 l_space_capblts_rec.attribute13,
2746 l_space_capblts_rec.attribute14,
2747 l_space_capblts_rec.attribute15,
2748 SYSDATE,
2749 Fnd_Global.user_id,
2750 SYSDATE,
2751 Fnd_Global.user_id,
2752 Fnd_Global.login_id
2753 );
2754
2755 p_x_space_capblts_rec.space_capability_id := l_space_capability_id;
2756 ---------------------------End of Body---------------------------------------
2757 --Standard check to count messages
2758 l_msg_count := Fnd_Msg_Pub.count_msg;
2759
2760 IF l_msg_count > 0 THEN
2761 X_msg_count := l_msg_count;
2762 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2763 RAISE Fnd_Api.G_EXC_ERROR;
2764 END IF;
2765
2766 --Standard check for commit
2767 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2768 COMMIT;
2769 END IF;
2770 -- Debug info
2771 IF G_DEBUG='Y' THEN
2772 Ahl_Debug_Pub.debug( 'End of private api Create Space Capblts ','+SPCBL+');
2773 -- Check if API is called in debug mode. If yes, disable debug.
2774 Ahl_Debug_Pub.disable_debug;
2775 END IF;
2776
2777 EXCEPTION
2778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2779 ROLLBACK TO create_space_capblts;
2780 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2781 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2782 p_count => x_msg_count,
2783 p_data => x_msg_data);
2784
2785 IF G_DEBUG='Y' THEN
2786 AHL_DEBUG_PUB.log_app_messages (
2787 x_msg_count, x_msg_data, 'ERROR' );
2788 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Create Space Capblts','+SPCBL+');
2789 -- Check if API is called in debug mode. If yes, disable debug.
2790 AHL_DEBUG_PUB.disable_debug;
2791 END IF;
2792 WHEN FND_API.G_EXC_ERROR THEN
2793 ROLLBACK TO create_space_capblts;
2794 X_return_status := FND_API.G_RET_STS_ERROR;
2795 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2796 p_count => x_msg_count,
2797 p_data => X_msg_data);
2798 -- Debug info.
2799 IF G_DEBUG='Y' THEN
2800 AHL_DEBUG_PUB.log_app_messages (
2801 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2802 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Create Space Capblts','+SPCBL+');
2803 END IF;
2804 -- Check if API is called in debug mode. If yes, disable debug.
2805 AHL_DEBUG_PUB.disable_debug;
2806
2807 WHEN OTHERS THEN
2808 ROLLBACK TO create_space_capblts;
2809 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2810 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2811 THEN
2812 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_CAPB_PVT',
2813 p_procedure_name => 'CREATE_SPACE_CAPBLTS',
2814 p_error_text => SUBSTR(SQLERRM,1,240));
2815 END IF;
2816 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2817 p_count => x_msg_count,
2818 p_data => X_msg_data);
2819
2820 -- Debug info.
2821 IF G_DEBUG='Y' THEN
2822 AHL_DEBUG_PUB.log_app_messages (
2823 x_msg_count, x_msg_data, 'SQL ERROR' );
2824 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Create Space Capblts','+SPCBL+');
2825 END IF;
2826 -- Check if API is called in debug mode. If yes, disable debug.
2827 AHL_DEBUG_PUB.disable_debug;
2828
2829 END Create_Space_Capblts;
2830
2831 --------------------------------------------------------------------
2832 -- PROCEDURE
2833 -- Update_Space_Capblts
2834 --
2835 -- PURPOSE
2836 -- Update Space Capblities Record.
2837 --
2838 -- PARAMETERS
2839 -- p_space_capblts_rec: the record representing AHL_SPACE_CAPBLITIES table
2840 --
2841 -- NOTES
2842 --------------------------------------------------------------------
2843 PROCEDURE Update_Space_Capblts (
2844 p_api_version IN NUMBER,
2845 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2846 p_commit IN VARCHAR2 := FND_API.g_false,
2847 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2848 p_module_type IN VARCHAR2 := 'JSP',
2849 p_space_capblts_rec IN ahl_ltp_space_capb_pub.Space_Capbl_Rec,
2850 x_return_status OUT NOCOPY VARCHAR2,
2851 x_msg_count OUT NOCOPY NUMBER,
2852 x_msg_data OUT NOCOPY VARCHAR2
2853 )
2854 IS
2855
2856 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SPACE_CAPBLTS';
2857 l_api_version CONSTANT NUMBER := 1.0;
2858 l_msg_count NUMBER;
2859 l_return_status VARCHAR2(1);
2860 l_msg_data VARCHAR2(2000);
2861 l_dummy NUMBER;
2862 l_rowid VARCHAR2(30);
2863 l_organization_id NUMBER;
2864 l_inventory_item_id NUMBER;
2865 l_space_id NUMBER;
2866 l_space_capability_id NUMBER;
2867 l_space_capblts_rec Space_Capbl_Rec;
2868
2869 --Get organization id
2870 CURSOR get_org_cur (c_space_id IN NUMBER)
2871 IS
2872 SELECT organization_id
2873 FROM AHL_SPACES_B
2874 WHERE SPACE_ID = c_space_id;
2875
2876 BEGIN
2877
2878 --------------------Initialize ----------------------------------
2879 -- Standard Start of API savepoint
2880 SAVEPOINT update_space_capblts;
2881 -- Check if API is called in debug mode. If yes, enable debug.
2882 IF G_DEBUG='Y' THEN
2883 AHL_DEBUG_PUB.enable_debug;
2884 END IF;
2885 -- Debug info.
2886 IF G_DEBUG='Y' THEN
2887 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');
2888 END IF;
2889 -- Standard call to check for call compatibility.
2890 IF FND_API.to_boolean(p_init_msg_list)
2891 THEN
2892 FND_MSG_PUB.initialize;
2893 END IF;
2894 -- Initialize API return status to success
2895 x_return_status := FND_API.G_RET_STS_SUCCESS;
2896 -- Initialize message list if p_init_msg_list is set to TRUE.
2897 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2898 p_api_version,
2899 l_api_name,G_PKG_NAME)
2900 THEN
2901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2902 END IF;
2903
2904 ---------------------art API Body------------------------------------
2905
2906 IF G_DEBUG='Y' THEN
2907 AHL_DEBUG_PUB.debug( 'org name 1:'||p_space_capblts_rec.org_name);
2908 AHL_DEBUG_PUB.debug( 'item desc 1:'||p_space_capblts_rec.item_Description);
2909 AHL_DEBUG_PUB.debug( 'visit type 1:'||p_space_capblts_rec.visit_type_mean);
2910
2911 END IF;
2912
2913 --------------------Value OR ID conversion---------------------------
2914 --Assign to local variable
2915 Assign_Space_capbl_Rec (
2916 p_space_capbl_rec => p_space_capblts_rec,
2917 x_space_capbl_rec => l_space_capblts_rec
2918 );
2919 IF p_module_type = 'JSP'
2920 THEN
2921 l_space_capblts_rec.organization_id := null;
2922 l_space_capblts_rec.inventory_item_id := null;
2923 l_space_capblts_rec.space_id := null;
2924 END IF;
2925 /* -- Convert org name to organization id
2926 IF (p_space_capblts_rec.org_name IS NOT NULL AND
2927 p_space_capblts_rec.org_name <> FND_API.G_MISS_CHAR ) OR
2928 (l_space_capblts_rec.organization_id IS NOT NULL AND
2929 l_space_capblts_rec.organization_id <> FND_API.G_MISS_NUM) THEN
2930
2931 Check_org_name_Or_Id
2932 (p_organization_id => l_space_capblts_rec.organization_id,
2933 p_org_name => p_space_capblts_rec.org_name,
2934 x_organization_id => l_organization_id,
2935 x_return_status => l_return_status,
2936 x_error_msg_code => l_msg_data);
2937
2938 IF NVL(l_return_status,'x') <> 'S'
2939 THEN
2940 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_NOT_EXISTS');
2941 Fnd_Message.SET_TOKEN('ORGID',p_space_capblts_rec.org_name);
2942 Fnd_Msg_Pub.ADD;
2943 END IF;
2944 END IF;
2945 --Assign the returned value
2946 l_space_capblts_rec.organization_id := l_organization_id;
2947 */
2948
2949 -- Convert Space name to space id
2950 IF (p_space_capblts_rec.space_name IS NOT NULL AND
2951 p_space_capblts_rec.space_name <> FND_API.G_MISS_CHAR ) OR
2952 (p_space_capblts_rec.space_id IS NOT NULL AND
2953 p_space_capblts_rec.space_id <> FND_API.G_MISS_NUM) THEN
2954
2955 Check_space_name_Or_Id
2956 (p_space_id => p_space_capblts_rec.space_id,
2957 p_space_name => p_space_capblts_rec.space_name,
2958 x_space_id => l_space_id,
2959 x_return_status => l_return_status,
2960 x_error_msg_code => l_msg_data);
2961
2962 IF NVL(l_return_status,'x') <> 'S'
2963 THEN
2964 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_NOT_EXISTS');
2965 Fnd_Message.SET_TOKEN('SPACEID',p_space_capblts_rec.space_name);
2966 Fnd_Msg_Pub.ADD;
2967 END IF;
2968 END IF;
2969 --Assign the returned value
2970 l_space_capblts_rec.space_id := l_space_id;
2971
2972 --Get organization id
2973 OPEN get_org_cur(l_space_id);
2974 FETCH get_org_cur INTO l_organization_id;
2975 CLOSE get_org_cur;
2976
2977 --Assign the returned value
2978 l_space_capblts_rec.organization_id := l_organization_id;
2979
2980 -- Get Inventory item id
2981 IF (p_space_capblts_rec.item_description IS NOT NULL AND
2982 p_space_capblts_rec.item_description <> FND_API.G_MISS_CHAR ) OR
2983 (p_space_capblts_rec.inventory_item_id IS NOT NULL AND
2984 p_space_capblts_rec.inventory_item_id <> FND_API.G_MISS_NUM) THEN
2985
2986 Check_inventory_desc_Or_Id
2987 (p_organization_id => l_organization_id,
2988 p_org_name => p_space_capblts_rec.org_name,
2989 p_item_description => p_space_capblts_rec.item_description,
2990 p_inventory_item_id => p_space_capblts_rec.inventory_item_id,
2991 x_inventory_item_id => l_inventory_item_id,
2992 x_return_status => l_return_status,
2993 x_error_msg_code => l_msg_data);
2994
2995 IF NVL(l_return_status,'x') <> 'S'
2996 THEN
2997 Fnd_Message.SET_NAME('AHL','AHL_LTP_ITEM_NOT_EXISTS');
2998 Fnd_Message.SET_TOKEN('DEPTID',p_space_capblts_rec.item_description);
2999 Fnd_Msg_Pub.ADD;
3000 END IF;
3001 END IF;
3002 --Assign the returned value
3003 l_space_capblts_rec.inventory_item_id := l_inventory_item_id;
3004
3005 --For Visit Type
3006 IF p_space_capblts_rec.visit_type_mean IS NOT NULL AND
3007 p_space_capblts_rec.visit_type_mean <> Fnd_Api.G_MISS_CHAR
3008 THEN
3009 Check_lookup_name_Or_Id (
3010 p_lookup_type => 'AHL_VISIT_TYPE',
3011 p_lookup_code => NULL,
3012 p_meaning => p_space_capblts_rec.visit_type_mean,
3013 p_check_id_flag => 'Y',
3014 x_lookup_code => l_space_capblts_rec.visit_type_code,
3015 x_return_status => l_return_status);
3016
3017 IF NVL(l_return_status, 'X') <> 'S'
3018 THEN
3019 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_NOT_EXIST');
3020 Fnd_Msg_Pub.ADD;
3021 END IF;
3022 END IF;
3023 -- Id presents
3024 IF p_space_capblts_rec.visit_type_code IS NOT NULL AND
3025 p_space_capblts_rec.visit_type_code <> Fnd_Api.G_MISS_CHAR
3026 THEN
3027 l_space_capblts_rec.visit_type_code := p_space_capblts_rec.visit_type_code;
3028 ELSE
3029 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_NOT_EXIST');
3030 Fnd_Msg_Pub.ADD;
3031 END IF;
3032
3033
3034 --------------------------------Validation ---------------------------
3035 -- get existing values and compare
3036 Complete_Space_Capbl_Rec (
3037 p_space_capbl_rec => l_space_capblts_rec,
3038 x_space_capbl_rec => l_space_capblts_rec);
3039 -- Call Validate space Capability attributes
3040 Validate_Space_Capblts
3041 ( p_api_version => l_api_version,
3042 p_init_msg_list => p_init_msg_list,
3043 p_validation_level => p_validation_level,
3044 p_space_capblts_rec => l_space_capblts_rec,
3045 x_return_status => l_return_status,
3046 x_msg_count => l_msg_count,
3047 x_msg_data => l_msg_data );
3048
3049
3050 ----------------------------DML Operation---------------------------------
3051 --Call table handler generated package to update a record
3052
3053 UPDATE AHL_SPACE_CAPABILITIES
3054 SET organization_id = l_space_capblts_rec.organization_id,
3055 space_id = l_space_capblts_rec.space_id,
3056 visit_type = l_space_capblts_rec.visit_type_code,
3057 inventory_item_id = l_space_capblts_rec.inventory_item_id,
3058 object_version_number = l_space_capblts_rec.object_version_number+1,
3059 attribute_category = l_space_capblts_rec.attribute_category,
3060 attribute1 = l_space_capblts_rec.attribute1,
3061 attribute2 = l_space_capblts_rec.attribute2,
3062 attribute3 = l_space_capblts_rec.attribute3,
3063 attribute4 = l_space_capblts_rec.attribute4,
3064 attribute5 = l_space_capblts_rec.attribute5,
3065 attribute6 = l_space_capblts_rec.attribute6,
3066 attribute7 = l_space_capblts_rec.attribute7,
3067 attribute8 = l_space_capblts_rec.attribute8,
3068 attribute9 = l_space_capblts_rec.attribute9,
3069 attribute10 = l_space_capblts_rec.attribute10,
3070 attribute11 = l_space_capblts_rec.attribute11,
3071 attribute12 = l_space_capblts_rec.attribute12,
3072 attribute13 = l_space_capblts_rec.attribute13,
3073 attribute14 = l_space_capblts_rec.attribute14,
3074 attribute15 = l_space_capblts_rec.attribute15,
3075 last_update_date = SYSDATE,
3076 last_updated_by = Fnd_Global.user_id,
3077 last_update_login = Fnd_Global.login_id
3078 WHERE space_capability_id = p_space_capblts_rec.space_capability_id;
3079
3080
3081 ---------------------------End of Body---------------------------------------
3082 --Standard check to count messages
3083 l_msg_count := Fnd_Msg_Pub.count_msg;
3084
3085 IF l_msg_count > 0 THEN
3086 X_msg_count := l_msg_count;
3087 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3088 RAISE Fnd_Api.G_EXC_ERROR;
3089 END IF;
3090
3091 --Standard check for commit
3092 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3093 COMMIT;
3094 END IF;
3095 -- Debug info
3096 IF G_DEBUG='Y' THEN
3097 Ahl_Debug_Pub.debug( 'End of private api Update Space Capblts','+SPCBL+');
3098 -- Check if API is called in debug mode. If yes, disable debug.
3099 Ahl_Debug_Pub.disable_debug;
3100 END IF;
3101
3102 EXCEPTION
3103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3104 ROLLBACK TO update_space_capblts;
3105 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3106 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3107 p_count => x_msg_count,
3108 p_data => x_msg_data);
3109
3110 IF G_DEBUG='Y' THEN
3111 AHL_DEBUG_PUB.log_app_messages (
3112 x_msg_count, x_msg_data, 'ERROR' );
3113 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');
3114 END IF;
3115 -- Check if API is called in debug mode. If yes, disable debug.
3116 AHL_DEBUG_PUB.disable_debug;
3117
3118 WHEN FND_API.G_EXC_ERROR THEN
3119 ROLLBACK TO update_space_capblts;
3120 X_return_status := FND_API.G_RET_STS_ERROR;
3121 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3122 p_count => x_msg_count,
3123 p_data => X_msg_data);
3124 -- Debug info.
3125 IF G_DEBUG='Y' THEN
3126 AHL_DEBUG_PUB.log_app_messages (
3127 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3128 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');
3129 END IF;
3130 -- Check if API is called in debug mode. If yes, disable debug.
3131 AHL_DEBUG_PUB.disable_debug;
3132
3133 WHEN OTHERS THEN
3134 ROLLBACK TO update_space_capblts;
3135 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3136 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3137 THEN
3138 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_CAPB_PVT',
3139 p_procedure_name => 'UPDATE_SPACE_CAPBLTS',
3140 p_error_text => SUBSTR(SQLERRM,1,240));
3141 END IF;
3142 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3143 p_count => x_msg_count,
3144 p_data => X_msg_data);
3145
3146 -- Debug info.
3147 IF G_DEBUG='Y' THEN
3148 AHL_DEBUG_PUB.log_app_messages (
3149 x_msg_count, x_msg_data, 'SQL ERROR' );
3150 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Update Space Capblts','+SPCBL+');
3151 END IF;
3152 -- Check if API is called in debug mode. If yes, disable debug.
3153 AHL_DEBUG_PUB.disable_debug;
3154
3155 END Update_Space_Capblts;
3156
3157 --------------------------------------------------------------------
3158 -- PROCEDURE
3159 -- Delete_Space_Capblts
3160 --
3161 -- PURPOSE
3162 -- Delete Space Capabilities Record.
3163 --
3164 -- PARAMETERS
3165 -- p_space_capblts_rec: the record representing AHL_SPACE_CAPABILITIES table
3166 --
3167 -- ISSUES
3168 --
3169 -- NOTES
3170 -- 1. Raise exception if the object_version_number doesn't match.
3171 --------------------------------------------------------------------
3172 PROCEDURE Delete_Space_Capblts (
3173 p_api_version IN NUMBER,
3174 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3175 p_commit IN VARCHAR2 := FND_API.g_false,
3176 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3177 p_space_capblts_rec IN ahl_ltp_space_capb_pub.Space_Capbl_Rec,
3178 x_return_status OUT NOCOPY VARCHAR2,
3179 x_msg_count OUT NOCOPY NUMBER,
3180 x_msg_data OUT NOCOPY VARCHAR2
3181
3182 )
3183 IS
3184
3185 CURSOR c_space_capb_cur
3186 (c_space_capability_id IN NUMBER)
3187 IS
3188 SELECT space_capability_id,object_version_number,
3189 visit_type,inventory_item_id
3190 FROM ahl_space_capabilities
3191 WHERE space_capability_id = c_space_capability_id
3192 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3193 -- Get space assignment
3194 CURSOR space_assign_cur (c_space_id IN NUMBER)
3195 IS
3196 SELECT visit_id
3197 FROM ahl_space_assignments
3198 WHERE space_id = c_space_id;
3199 --
3200 CURSOR check_space_capb_cur (c_visit_id IN NUMBER,
3201 c_visit_type IN VARCHAR2,
3202 c_inventory_item_id IN NUMBER)
3203 IS
3204 SELECT 1 FROM ahl_visits_b
3205 WHERE visit_id = c_visit_id
3206 AND visit_type_code = c_visit_type
3207 AND inventory_item_id = c_inventory_item_id;
3208 --
3209 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SPACE_CAPBLTS';
3210 l_api_version CONSTANT NUMBER := 1.0;
3211 l_msg_count NUMBER;
3212 l_return_status VARCHAR2(1);
3213 l_msg_data VARCHAR2(2000);
3214 l_dummy NUMBER;
3215 l_space_capability_id NUMBER;
3216 l_object_version_number NUMBER;
3217 l_visit_type VARCHAR2(30);
3218 l_visit_id NUMBER;
3219 l_inventory_item_id NUMBER;
3220 --
3221 BEGIN
3222 --------------------Initialize ----------------------------------
3223 -- Standard Start of API savepoint
3224 SAVEPOINT delete_space_capblts;
3225 -- Check if API is called in debug mode. If yes, enable debug.
3226 IF G_DEBUG='Y' THEN
3227 AHL_DEBUG_PUB.enable_debug;
3228 END IF;
3229 -- Debug info.
3230 IF G_DEBUG='Y' THEN
3231 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');
3232 END IF;
3233 -- Standard call to check for call compatibility.
3234 IF FND_API.to_boolean(p_init_msg_list)
3235 THEN
3236 FND_MSG_PUB.initialize;
3237 END IF;
3238 -- Initialize API return status to success
3239 x_return_status := FND_API.G_RET_STS_SUCCESS;
3240 -- Initialize message list if p_init_msg_list is set to TRUE.
3241 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3242 p_api_version,
3243 l_api_name,G_PKG_NAME)
3244 THEN
3245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246 END IF;
3247 -----------------------Start of API Body-----------------------------
3248 -- Check for Record exists
3249 OPEN c_space_capb_cur(p_space_capblts_rec.space_capability_id);
3250 FETCH c_space_capb_cur INTO l_space_capability_id,
3251 l_object_version_number,l_visit_type,
3252 l_inventory_item_id;
3253 IF c_space_capb_cur%NOTFOUND THEN
3254 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3255 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
3256 FND_MSG_PUB.add;
3257 END IF;
3258 CLOSE c_space_capb_cur;
3259 RAISE FND_API.g_exc_error;
3260 END IF;
3261 CLOSE c_space_capb_cur;
3262
3263 --Check for object version number
3264 IF l_object_version_number <> p_space_capblts_rec.object_version_number
3265 THEN
3266 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
3267 FND_MSG_PUB.add;
3268 RAISE FND_API.g_exc_error;
3269 END IF;
3270 -- Check for space assignment exists, If so any visit has same capability
3271 OPEN space_assign_cur(p_space_capblts_rec.space_id);
3272 LOOP
3273 FETCH space_assign_cur INTO l_visit_id;
3274 EXIT WHEN space_assign_cur%NOTFOUND;
3275 IF l_visit_id IS NOT NULL THEN
3276 OPEN check_space_capb_cur(l_visit_id,l_visit_type,l_inventory_item_id);
3277 FETCH check_space_capb_cur INTO l_dummy;
3278 IF check_space_capb_cur%FOUND THEN
3279 FND_MESSAGE.set_name('AHL', 'AHL_CAPBL_ASIGN_TO_VIIST');
3280 FND_MSG_PUB.add;
3281 CLOSE check_space_capb_cur;
3282 RAISE FND_API.g_exc_error;
3283 END IF;
3284 CLOSE check_space_capb_cur;
3285 END IF;
3286 --
3287 END LOOP;
3288 CLOSE space_assign_cur;
3289
3290 --
3291 IF G_DEBUG='Y' THEN
3292 AHL_DEBUG_PUB.debug( 'before delete capabilities');
3293 END IF;
3294 -------------------Remove the reocrd from space capabilities------------
3295 DELETE FROM AHL_SPACE_CAPABILITIES
3296 WHERE SPACE_CAPABILITY_ID = l_space_capability_id;
3297
3298 ---------------------------End of Body---------------------------------------
3299 --Standard check to count messages
3300 l_msg_count := Fnd_Msg_Pub.count_msg;
3301
3302 IF l_msg_count > 0 THEN
3303 X_msg_count := l_msg_count;
3304 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3305 RAISE Fnd_Api.G_EXC_ERROR;
3306 END IF;
3307
3308 --Standard check for commit
3309 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3310 COMMIT;
3311 END IF;
3312 -- Debug info
3313 IF G_DEBUG='Y' THEN
3314 Ahl_Debug_Pub.debug( 'End of private api Delete Space Capblts','+SPCBL+');
3315 -- Check if API is called in debug mode. If yes, disable debug.
3316 Ahl_Debug_Pub.disable_debug;
3317 END IF;
3318
3319 EXCEPTION
3320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3321 ROLLBACK TO delete_space_capblts;
3322 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3323 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3324 p_count => x_msg_count,
3325 p_data => x_msg_data);
3326 IF G_DEBUG='Y' THEN
3327 AHL_DEBUG_PUB.log_app_messages (
3328 x_msg_count, x_msg_data, 'ERROR' );
3329 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');
3330 -- Check if API is called in debug mode. If yes, disable debug.
3331 AHL_DEBUG_PUB.disable_debug;
3332 END IF;
3333 WHEN FND_API.G_EXC_ERROR THEN
3334 ROLLBACK TO delete_space_capblts;
3335 X_return_status := FND_API.G_RET_STS_ERROR;
3336 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3337 p_count => x_msg_count,
3338 p_data => X_msg_data);
3339 IF G_DEBUG='Y' THEN
3340 -- Debug info.
3341 AHL_DEBUG_PUB.log_app_messages (
3342 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3343 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');
3344 -- Check if API is called in debug mode. If yes, disable debug.
3345 AHL_DEBUG_PUB.disable_debug;
3346 END IF;
3347 WHEN OTHERS THEN
3348 ROLLBACK TO delete_space_capblts;
3349 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3350 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3351 THEN
3352 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_CAPB_PVT',
3353 p_procedure_name => 'DELETE_SPACE_CAPBLTS',
3354 p_error_text => SUBSTR(SQLERRM,1,240));
3355 END IF;
3356 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3357 p_count => x_msg_count,
3358 p_data => X_msg_data);
3359
3360 IF G_DEBUG='Y' THEN
3361 -- Debug info.
3362 AHL_DEBUG_PUB.log_app_messages (
3363 x_msg_count, x_msg_data, 'SQL ERROR' );
3364 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_capb_pvt.Delete Space Capblts','+SPCBL+');
3365 -- Check if API is called in debug mode. If yes, disable debug.
3366 AHL_DEBUG_PUB.disable_debug;
3367 END IF;
3368 END Delete_Space_Capblts;
3369
3370 END AHL_LTP_SPACE_CAPB_PVT;