[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_SPACE_ASSIGN_PVT
Source
1 PACKAGE BODY AHL_LTP_SPACE_ASSIGN_PVT AS
2 /* $Header: AHLVSANB.pls 120.0 2005/05/26 10:59:45 appldev noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_SPACE_ASSIGN_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 --
7 -- PACKAGE
8 -- AHL_LTP_SPACE_ASSIGN_PVT
9 --
10 -- PURPOSE
11 -- This package is a Private API for assigning Spaces to a visit information in
12 -- Advanced Services Online. It contains specification for pl/sql records and tables
13 --
14 -- AHL_SPACE_ASSIGNMENT:
15 -- Create_Space_Assignment (see below for specification)
16 -- Update_Space_Assignment (see below for specification)
17 -- Delete_Space_Assignment (see below for specification)
18 -- Validate_Space_Assignment (see below for specification)
19 --
20 --
21 -- NOTES
22 --
23 --
24 -- HISTORY
25 -- 02-May-2002 ssurapan Created.
26 --
27 -- PROCEDURE:
28 -- Check_lookup_name_Or_Id(private procedure)
29 -- DESCRIPTION :
30 -- used to retrieve lookup code
31 --
32 PROCEDURE Check_lookup_name_Or_Id
33 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
34 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
35 p_meaning IN FND_LOOKUPS.meaning%TYPE,
36 p_check_id_flag IN VARCHAR2,
37 x_lookup_code OUT NOCOPY VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2)
39 IS
40 BEGIN
41 --
42 IF (p_lookup_code IS NOT NULL) THEN
43 IF (p_check_id_flag = 'Y') THEN
44 SELECT lookup_code INTO x_lookup_code
45 FROM FND_LOOKUP_VALUES_VL
46 WHERE lookup_type = p_lookup_type
47 AND lookup_code = p_lookup_code
48 AND SYSDATE BETWEEN start_date_active
49 AND NVL(end_date_active,SYSDATE);
50 ELSE
51 x_lookup_code := p_lookup_code;
52 END IF;
53 ELSE
54 --
55 SELECT lookup_code INTO x_lookup_code
56 FROM FND_LOOKUP_VALUES_VL
57 WHERE lookup_type = p_lookup_type
58 AND meaning = p_meaning
59 AND SYSDATE BETWEEN start_date_active
60 AND NVL(end_date_active,SYSDATE);
61 END IF;
62 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
63
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 x_return_status := Fnd_Api.G_RET_STS_ERROR;
67 WHEN TOO_MANY_ROWS THEN
68 x_return_status := Fnd_Api.G_RET_STS_ERROR;
69 WHEN OTHERS THEN
70 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
71 RAISE;
72 END;
73 -- Start of Coments
74 -- CHECK_ORG_NAME_OR_ID
75 --
76 -- PURPOSE
77 -- Converts Org Name to ID or Vice versa
78 --
79 -- PARAMETERS
80 --
81 -- NOTES
82 PROCEDURE Check_org_name_Or_Id
83 (p_organization_id IN NUMBER,
84 p_org_name IN VARCHAR2,
85 x_organization_id OUT NOCOPY NUMBER,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_error_msg_code OUT NOCOPY VARCHAR2
88 )
89 IS
90 BEGIN
91 IF (p_organization_id IS NOT NULL)
92 THEN
93 SELECT organization_id
94 INTO x_organization_id
95 FROM HR_ALL_ORGANIZATION_UNITS
96 WHERE organization_id = p_organization_id;
97 ELSE
98 SELECT organization_id
99 INTO x_organization_id
100 FROM HR_ALL_ORGANIZATION_UNITS
101 WHERE NAME = p_org_name;
102 END IF;
103 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
107 x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
108 WHEN TOO_MANY_ROWS THEN
109 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
110 x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
111 WHEN OTHERS THEN
112 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
113 RAISE;
114 END Check_org_name_Or_Id;
115 -- Start of Comments
116 -- PROCEDURE
117 -- CHECK_DEPT_DESC_OR_ID
118 --
119 -- PURPOSE
120 -- Converts Dept description to ID or Vice Versa
121 --
122 -- PARAMETERS
123 --
124 -- NOTES
125 --
126 PROCEDURE Check_dept_desc_Or_Id
127 (p_organization_id IN NUMBER,
128 p_org_name IN VARCHAR2,
129 p_department_id IN NUMBER,
130 p_dept_description IN VARCHAR2,
131 x_department_id OUT NOCOPY NUMBER,
132 x_return_status OUT NOCOPY VARCHAR2,
133 x_error_msg_code OUT NOCOPY VARCHAR2)
134 IS
135 BEGIN
136 --
137 /* Exists clause added by mpothuku on 18/01/05 to consider the depts with shifts only */
138 IF (p_department_id IS NOT NULL)
139 THEN
140 SELECT department_id
141 INTO x_department_id
142 FROM BOM_DEPARTMENTS
143 WHERE organization_id = p_organization_id
144 AND department_id = p_department_id
145 AND EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID);
146 ELSE
147 SELECT department_id
148 INTO x_department_id
149 FROM BOM_DEPARTMENTS
150 WHERE organization_id = p_organization_id
151 AND description = p_dept_description
152 AND EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID);
153 END IF;
154 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
158 x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
159 WHEN TOO_MANY_ROWS THEN
160 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
161 x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
162 WHEN OTHERS THEN
163 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
164 RAISE;
165 END Check_dept_desc_Or_Id;
166 --
167 -- PROCEDURE
168 -- CHECK_SPACE_NAME_OR_ID
169 --
170 -- PURPOSE
171 -- Converts Space Name to ID or Vice versa
172 --
173 -- PARAMETERS
174 --
175 -- NOTES
176 --
177 PROCEDURE Check_space_name_Or_Id
178 (p_space_id IN NUMBER,
179 p_space_name IN VARCHAR2,
180 x_space_id OUT NOCOPY NUMBER,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_error_msg_code OUT NOCOPY VARCHAR2
183 )
184 IS
185 BEGIN
186 --
187 IF (p_space_name IS NOT NULL)
188 THEN
189 SELECT space_id
190 INTO x_space_id
191 FROM AHL_SPACES_VL
192 WHERE space_name = p_space_name;
193 ELSE
194 SELECT space_id
195 INTO x_space_id
196 FROM AHL_SPACES_VL
197 WHERE SPACE_ID = p_space_id;
198 END IF;
199 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
203 x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
204 WHEN TOO_MANY_ROWS THEN
205 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
206 x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
207 WHEN OTHERS THEN
208 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
209 RAISE;
210 END Check_space_name_Or_Id;
211 --
212 -- PROCEDURE
213 -- CHECK_VISIT_NUMBER_OR_ID
214 --
215 -- PURPOSE
216 -- Converts Visit Number to ID or Vice versa
217 --
218 -- PARAMETERS
219 --
220 -- NOTES
221 --
222 PROCEDURE Check_visit_number_Or_Id
223 (p_visit_id IN NUMBER,
224 p_visit_number IN NUMBER,
225 x_visit_id OUT NOCOPY NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_error_msg_code OUT NOCOPY VARCHAR2
228 )
229 IS
230 BEGIN
231 IF (p_visit_id IS NOT NULL)
232 THEN
233 SELECT visit_id
234 INTO x_visit_id
235 FROM AHL_VISITS_VL
236 WHERE visit_id = p_visit_id;
237 ELSE
238 SELECT visit_id
239 INTO x_visit_id
240 FROM AHL_VISITS_VL
241 WHERE visit_number = p_visit_number;
242 END IF;
243 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
247 x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
248 WHEN TOO_MANY_ROWS THEN
249 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
250 x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
251 WHEN OTHERS THEN
252 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
253 RAISE;
254 END Check_visit_number_Or_Id;
255 --
256 -- PROCEDURE
257 -- Assign_Space_Assign_Rec
258 --
259 --
260 PROCEDURE Assign_Space_Assign_Rec (
261 p_space_assign_rec IN AHL_LTP_SPACE_ASSIGN_PUB.Space_assignment_rec,
262 x_space_assign_rec OUT NOCOPY Space_Assignment_rec
263 )
264 IS
265
266 BEGIN
267 x_space_assign_rec.space_assignment_id := p_space_assign_rec.space_assignment_id;
268 x_space_assign_rec.space_id := p_space_assign_rec.space_id;
269 x_space_assign_rec.space_name := p_space_assign_rec.space_name;
270 x_space_assign_rec.visit_id := p_space_assign_rec.visit_id;
271 x_space_assign_rec.object_version_number := p_space_assign_rec.object_version_number;
272 x_space_assign_rec.attribute_category := p_space_assign_rec.attribute_category;
273 x_space_assign_rec.attribute1 := p_space_assign_rec.attribute1;
274 x_space_assign_rec.attribute2 := p_space_assign_rec.attribute2;
275 x_space_assign_rec.attribute3 := p_space_assign_rec.attribute3;
276 x_space_assign_rec.attribute4 := p_space_assign_rec.attribute4;
277 x_space_assign_rec.attribute5 := p_space_assign_rec.attribute5;
278 x_space_assign_rec.attribute6 := p_space_assign_rec.attribute6;
279 x_space_assign_rec.attribute7 := p_space_assign_rec.attribute7;
280 x_space_assign_rec.attribute8 := p_space_assign_rec.attribute8;
281 x_space_assign_rec.attribute9 := p_space_assign_rec.attribute9;
282 x_space_assign_rec.attribute10 := p_space_assign_rec.attribute10;
283 x_space_assign_rec.attribute11 := p_space_assign_rec.attribute11;
284 x_space_assign_rec.attribute12 := p_space_assign_rec.attribute12;
285 x_space_assign_rec.attribute13 := p_space_assign_rec.attribute13;
286 x_space_assign_rec.attribute14 := p_space_assign_rec.attribute14;
287 x_space_assign_rec.attribute15 := p_space_assign_rec.attribute15;
288
289 END Assign_Space_Assign_Rec;
290 --
291 -- PROCEDURE
292 -- Complete_Space_Assign_Rec
293 --
294 --
295 PROCEDURE Complete_Space_Assign_Rec (
296 p_space_assign_rec IN Space_assignment_rec,
297 x_space_assign_rec OUT NOCOPY Space_assignment_rec
298 )
299 IS
300 CURSOR c_space_assign_rec
301 IS
302 SELECT ROWID ROW_ID,
303 SPACE_ASSIGNMENT_ID,
304 SPACE_ID,
305 VISIT_ID,
306 OBJECT_VERSION_NUMBER,
307 ATTRIBUTE_CATEGORY,
308 ATTRIBUTE1,
309 ATTRIBUTE2,
310 ATTRIBUTE3,
311 ATTRIBUTE4,
312 ATTRIBUTE5,
313 ATTRIBUTE6,
314 ATTRIBUTE7,
315 ATTRIBUTE8,
316 ATTRIBUTE9,
317 ATTRIBUTE10,
318 ATTRIBUTE11,
319 ATTRIBUTE12,
320 ATTRIBUTE13,
321 ATTRIBUTE14,
322 ATTRIBUTE15
323 FROM ahl_space_assignments
324 WHERE space_assignment_id = p_space_assign_rec.space_assignment_id;
325 --
326 -- This is the only exception for using %ROWTYPE.
327 l_space_assign_rec c_space_assign_rec%ROWTYPE;
328 BEGIN
329 x_space_assign_rec := p_space_assign_rec;
330 OPEN c_space_assign_rec;
331 FETCH c_space_assign_rec INTO l_space_assign_rec;
332 IF c_space_assign_rec%NOTFOUND THEN
333 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
334 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
335 FND_MSG_PUB.add;
336 CLOSE c_space_assign_rec;
337 RAISE Fnd_Api.G_EXC_ERROR;
338 END IF;
339 END IF;
340 CLOSE c_space_assign_rec;
341 --Check for object version number
342 IF (l_space_assign_rec.object_version_number <> p_space_assign_rec.object_version_number)
343 THEN
344 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
345 Fnd_Msg_Pub.ADD;
346 RAISE Fnd_Api.G_EXC_ERROR;
347 END IF;
348
349 -- SPACE ID
350 IF p_space_assign_rec.space_id <> FND_API.g_miss_num THEN
351 x_space_assign_rec.space_id := p_space_assign_rec.space_id;
352 ELSE
353 x_space_assign_rec.space_id := l_space_assign_rec.space_id;
354 END IF;
355 -- VISIT_ID
356 IF p_space_assign_rec.visit_id <> FND_API.g_miss_num THEN
357 x_space_assign_rec.visit_id := p_space_assign_rec.visit_id;
358 ELSE
359 x_space_assign_rec.visit_id := l_space_assign_rec.visit_id;
360 END IF;
361 -- ATTRIBUTE CATEGORY
362 IF p_space_assign_rec.attribute_category <> FND_API.g_miss_char THEN
363 x_space_assign_rec.attribute_category := p_space_assign_rec.attribute_category;
364 ELSE
365 x_space_assign_rec.attribute_category := l_space_assign_rec.attribute_category;
366 END IF;
367 -- ATTRIBUTE 1
368 IF p_space_assign_rec.attribute1 <> FND_API.g_miss_char THEN
369 x_space_assign_rec.attribute1 := p_space_assign_rec.attribute1;
370 ELSE
371 x_space_assign_rec.attribute1 := l_space_assign_rec.attribute1;
372 END IF;
373 -- ATTRIBUTE 2
374 IF p_space_assign_rec.attribute2 <> FND_API.g_miss_char THEN
375 x_space_assign_rec.attribute2 := p_space_assign_rec.attribute2;
376 ELSE
377 x_space_assign_rec.attribute2 := l_space_assign_rec.attribute2;
378 END IF;
379 -- ATTRIBUTE 3
380 IF p_space_assign_rec.attribute3 <> FND_API.g_miss_char THEN
381 x_space_assign_rec.attribute3 := p_space_assign_rec.attribute3;
382 ELSE
383 x_space_assign_rec.attribute3 := l_space_assign_rec.attribute3;
384 END IF;
385 -- ATTRIBUTE 4
386 IF p_space_assign_rec.attribute4 <> FND_API.g_miss_char THEN
387 x_space_assign_rec.attribute4 := p_space_assign_rec.attribute4;
388 ELSE
389 x_space_assign_rec.attribute4 := l_space_assign_rec.attribute4;
390 END IF;
391 -- ATTRIBUTE 5
392 IF p_space_assign_rec.attribute5 <> FND_API.g_miss_char THEN
393 x_space_assign_rec.attribute5 := p_space_assign_rec.attribute5;
394 ELSE
395 x_space_assign_rec.attribute5 := l_space_assign_rec.attribute5;
396 END IF;
397 -- ATTRIBUTE 6
398 IF p_space_assign_rec.attribute6 <> FND_API.g_miss_char THEN
399 x_space_assign_rec.attribute6 := p_space_assign_rec.attribute6;
400 ELSE
401 x_space_assign_rec.attribute6 := l_space_assign_rec.attribute6;
402 END IF;
403 -- ATTRIBUTE 7
404 IF p_space_assign_rec.attribute7 <> FND_API.g_miss_char THEN
405 x_space_assign_rec.attribute7 := p_space_assign_rec.attribute7;
406 ELSE
407 x_space_assign_rec.attribute7 := l_space_assign_rec.attribute7;
408 END IF;
409 -- ATTRIBUTE 8
410 IF p_space_assign_rec.attribute8 <> FND_API.g_miss_char THEN
411 x_space_assign_rec.attribute8 := p_space_assign_rec.attribute8;
412 ELSE
413 x_space_assign_rec.attribute8 := l_space_assign_rec.attribute8;
414 END IF;
415 -- ATTRIBUTE 9
416 IF p_space_assign_rec.attribute9 <> FND_API.g_miss_char THEN
417 x_space_assign_rec.attribute9 := p_space_assign_rec.attribute9;
418 ELSE
419 x_space_assign_rec.attribute9 := l_space_assign_rec.attribute9;
420 END IF;
421 -- ATTRIBUTE 10
422 IF p_space_assign_rec.attribute10 <> FND_API.g_miss_char THEN
423 x_space_assign_rec.attribute10 := p_space_assign_rec.attribute10;
424 ELSE
425 x_space_assign_rec.attribute10 := l_space_assign_rec.attribute10;
426 END IF;
427 -- ATTRIBUTE 11
428 IF p_space_assign_rec.attribute11 <> FND_API.g_miss_char THEN
429 x_space_assign_rec.attribute11 := p_space_assign_rec.attribute11;
430 ELSE
431 x_space_assign_rec.attribute11 := l_space_assign_rec.attribute11;
432 END IF;
433 -- ATTRIBUTE 12
434 IF p_space_assign_rec.attribute12 <> FND_API.g_miss_char THEN
435 x_space_assign_rec.attribute12 := p_space_assign_rec.attribute12;
436 ELSE
437 x_space_assign_rec.attribute12 := l_space_assign_rec.attribute12;
438 END IF;
439 -- ATTRIBUTE 13
440 IF p_space_assign_rec.attribute13 <> FND_API.g_miss_char THEN
441 x_space_assign_rec.attribute13 := p_space_assign_rec.attribute13;
442 ELSE
443 x_space_assign_rec.attribute13 := l_space_assign_rec.attribute13;
444 END IF;
445 -- ATTRIBUTE 14
446 IF p_space_assign_rec.attribute14 <> FND_API.g_miss_char THEN
447 x_space_assign_rec.attribute14 := p_space_assign_rec.attribute14;
448 ELSE
449 x_space_assign_rec.attribute14 := l_space_assign_rec.attribute14;
450 END IF;
451 -- ATTRIBUTE 15
452 IF p_space_assign_rec.attribute15 <> FND_API.g_miss_char THEN
453 x_space_assign_rec.attribute15 := p_space_assign_rec.attribute15;
454 ELSE
455 x_space_assign_rec.attribute15 := l_space_assign_rec.attribute15;
456 END IF;
457
458 END Complete_Space_Assign_Rec;
459 --
460 --
461 -- NAME
462 -- Validate_Space_Assign_Items
463 --
464 -- PURPOSE
465 -- This procedure is to validate Space Assign attributes
466 --
467 PROCEDURE Validate_Space_Assign_Items
468 ( p_space_assign_rec IN space_assignment_rec,
469 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
470 x_return_status OUT NOCOPY VARCHAR2
471 ) IS
472
473 CURSOR check_unique (c_visit_id IN NUMBER,
474 c_space_id IN NUMBER)
475 IS
476 SELECT space_assignment_id
477 FROM AHL_SPACE_ASSIGNMENTS
478 WHERE VISIT_ID = p_space_assign_rec.visit_id
479 AND SPACE_ID = p_space_assign_rec.space_id;
480 --
481 CURSOR visit_item_cur (c_visit_id IN NUMBER)
482 IS
483 SELECT visit_type_code,
484 inventory_item_id,
485 trunc(start_date_time) start_date_time,
486 trunc(close_date_time)
487 FROM ahl_visits_b
488 WHERE visit_id = c_visit_id;
489 --
490 CURSOR space_available_cur(c_space_id IN NUMBER)
491 IS
492 SELECT trunc(start_date) start_date,
493 trunc(end_date) end_date
494 FROM ahl_space_unavailable_b
495 WHERE space_id = c_space_id;
496 --
497 CURSOR space_capable_cur (c_space_id IN NUMBER,
498 c_visit_type IN VARCHAR2,
499 c_inventory_item_id IN NUMBER)
500 IS
501 SELECT space_capability_id
502 FROM ahl_space_capabilities
503 WHERE space_id = c_space_id
504 AND visit_type = c_visit_type
505 AND inventory_item_id = c_inventory_item_id;
506 --
507
508 CURSOR space_unavailable_cur(c_space_id IN NUMBER,
509 c_start_date IN DATE,
510 c_end_date IN DATE)
511 IS
512 SELECT trunc(start_date),trunc(end_date)
513 FROM ahl_space_unavailable_b
514 WHERE space_id = c_space_id
515 -- AND ((c_start_date between trunc(start_date) and trunc(end_date))
516 -- or (c_end_date between trunc(start_date) and trunc(end_date)));
517 AND ((trunc(start_date) between c_start_date and c_end_date)
518 OR (trunc(end_date) between c_start_date and c_end_date));
519
520 l_table_name VARCHAR2(30);
521 l_pk_name VARCHAR2(30);
522 l_pk_value VARCHAR2(30);
523 l_where_clause VARCHAR2(2000);
524 l_dummy NUMBER;
525 --
526 l_visit_type_code VARCHAR2(80);
527 l_start_date_time DATE;
528 l_end_date_time DATE;
529 l_start_date DATE;
530 l_end_date DATE;
531 l_inventory_item_id NUMBER;
532 l_space_unavailability_id NUMBER;
533 l_space_capability_id NUMBER;
534 --
535 BEGIN
536 -- Initialize API/Procedure return status to success
537 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
538 -- Check required parameters
539 IF (p_space_assign_rec.SPACE_ID IS NULL OR
540 p_space_assign_rec.SPACE_ID = Fnd_Api.G_MISS_NUM
541 )
542 --
543 THEN
544 -- missing required fields
545 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
546 THEN
547 Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_ID_NOT_EXIST');
548 Fnd_Msg_Pub.ADD;
549 RAISE Fnd_Api.G_EXC_ERROR;
550 END IF;
551 x_return_status := Fnd_Api.G_RET_STS_ERROR;
552 END IF;
553 -- VISIT_ID
554 IF (p_space_assign_rec.VISIT_ID = Fnd_Api.G_MISS_NUM OR
555 p_space_assign_rec.VISIT_ID IS NULL)
556 THEN
557 -- missing required fields
558 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
559 THEN
560 Fnd_Message.set_name('AHL', 'AHL_LTP_VISIT_ID_NOT_EXIST');
561 Fnd_Msg_Pub.ADD;
562 END IF;
563 x_return_status := Fnd_Api.G_RET_STS_ERROR;
564 END IF;
565
566 -- Validate uniqueness
567 OPEN check_unique (p_space_assign_rec.visit_id,
568 p_space_assign_rec.space_id);
569 FETCH check_unique INTO l_dummy;
570 CLOSE check_unique;
571 --
572 IF l_dummy IS NOT NULL THEN
573 Fnd_Message.set_name('AHL', 'AHL_LTP_SP_ASSIGN_DUP_RECORD');
574 Fnd_Msg_Pub.ADD;
575 END IF;
576 --- Validation for visit type and inventory item
577 OPEN visit_item_cur(p_space_assign_rec.visit_id);
578 FETCH visit_item_cur INTO l_visit_type_code,
579 l_inventory_item_id,
580 l_start_date_time,
581 l_end_date_time;
582 CLOSE visit_item_cur;
583 --new
584
585 OPEN space_unavailable_cur(p_space_assign_rec.space_id,
586 l_start_date_time,
587 nvl(l_end_date_time,l_start_date_time));
588 LOOP
589 FETCH space_unavailable_cur INTO l_start_date,l_end_date;
590 EXIT WHEN space_unavailable_cur%NOTFOUND;
591 IF space_unavailable_cur%FOUND THEN
592 Fnd_Message.set_name('AHL', 'AHL_LTP_SP_UNAVAL_PERIOD');
593 Fnd_message.set_token( 'PERIOD', l_start_date ||' '||'to'||' '||l_end_date );
594 Fnd_Msg_Pub.ADD;
595 END IF;
596 END LOOP;
597 CLOSE space_unavailable_cur;
598
599 -- new
600 /*
601 --Check for space availability
602 OPEN space_available_cur(p_space_assign_rec.space_id);
603 LOOP
604 FETCH space_available_cur INTO l_start_date,l_end_date;
605 EXIT WHEN space_available_cur%NOTFOUND;
606 IF (l_start_date_time >= l_start_date AND
607 l_start_date_time <= l_end_date) THEN
608 Fnd_Message.set_name('AHL', 'AHL_LTP_SP_UNAVAL_PERIOD');
609 Fnd_message.set_token( 'PERIOD', l_start_date ||' '||'to'||' '||l_end_date );
610 Fnd_Msg_Pub.ADD;
611 END IF;
612 END LOOP;
613 CLOSE space_available_cur;
614 --
615 */
616 --Check for visit type
617 OPEN space_capable_cur(p_space_assign_rec.space_id,
618 l_visit_type_code,
619 l_inventory_item_id);
620 FETCH space_capable_cur INTO l_space_capability_id;
621 IF l_space_capability_id IS NULL THEN
622 Fnd_Message.set_name('AHL', 'AHL_LTP_VISIT_ITEM_NOT_EXIST');
623 Fnd_Msg_Pub.ADD;
624 END IF;
625 CLOSE space_capable_cur;
626
627 --
628 END Validate_Space_Assign_Items;
629 --
630 --
631 -- PROCEDURE
632 -- Validate_Space_Assign
633 --
634 -- PURPOSE
635 -- Validate space Assignment attributes
636 --
637 -- PARAMETERS
638 --
639 -- NOTES
640 --
641 --
642 PROCEDURE Validate_Space_Assign
643 ( p_api_version IN NUMBER,
644 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
645 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
646 p_space_assign_rec IN space_assignment_rec,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2
650 )
651 IS
652 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Space_Assign';
653 l_api_version CONSTANT NUMBER := 1.0;
654 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
655 l_return_status VARCHAR2(1);
656 l_space_assign_rec space_assignment_rec;
657 BEGIN
658 -- Standard call to check for call compatibility.
659 IF NOT Fnd_Api.Compatible_API_Call ( l_api_version,
660 p_api_version,
661 l_api_name,
662 G_PKG_NAME)
663 THEN
664 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
665 END IF;
666 -- Initialize message list if p_init_msg_list is set to TRUE.
667 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
668 Fnd_Msg_Pub.initialize;
669 END IF;
670 -- Initialize API return status to success
671 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
672 --
673 -- API body
674 --
675 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item
676 THEN
677 Validate_Space_assign_Items
678 ( p_space_assign_rec => p_space_assign_rec,
679 p_validation_mode => Jtf_Plsql_Api.g_create,
680 x_return_status => l_return_status
681 );
682 -- If any errors happen abort API.
683 IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
684 THEN
685 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
686 ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
687 THEN
688 RAISE Fnd_Api.G_EXC_ERROR;
689 END IF;
690 END IF;
691 --
692 -- END of API body.
693 --
694 -------------------- finish --------------------------
695 Fnd_Msg_Pub.count_and_get(
696 p_encoded => Fnd_Api.g_false,
697 p_count => x_msg_count,
698 p_data => x_msg_data);
699 EXCEPTION
700 WHEN Fnd_Api.G_EXC_ERROR THEN
701 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
702 Fnd_Msg_Pub.Count_AND_Get
703 ( p_count => x_msg_count,
704 p_data => x_msg_data,
705 p_encoded => Fnd_Api.G_FALSE
706 );
707 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
708 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
709 Fnd_Msg_Pub.Count_AND_Get
710 ( p_count => x_msg_count,
711 p_data => x_msg_data,
712 p_encoded => Fnd_Api.G_FALSE
713 );
714 WHEN OTHERS THEN
715 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
716 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
717 THEN
718 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
719 END IF;
720 Fnd_Msg_Pub.Count_AND_Get
721 ( p_count => x_msg_count,
722 p_data => x_msg_data,
723 p_encoded => Fnd_Api.G_FALSE
724 );
725 END Validate_Space_Assign;
726 --
727 -- PROCEDURE
728 -- Create_Space_Assignment
729 --
730 -- PURPOSE
731 -- Create Space Assignment Record
732 --
733 -- PARAMETERS
734 -- p_x_space_assign_rec: the record representing AHL_SPACE_ASSIGNMENTS..
735 --
736 -- NOTES
737 --
738 PROCEDURE Create_Space_Assignment (
739 p_api_version IN NUMBER,
740 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
741 p_commit IN VARCHAR2 := FND_API.g_false,
742 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
743 p_module_type IN VARCHAR2 := 'JSP',
744 p_x_space_assign_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Space_Assignment_Rec,
745 p_reschedule_flag IN VARCHAR2,
746 x_return_status OUT NOCOPY VARCHAR2,
747 x_msg_count OUT NOCOPY NUMBER,
748 x_msg_data OUT NOCOPY VARCHAR2
749 )
750 IS
751 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SPACE_ASSIGNMENT';
752 l_api_version CONSTANT NUMBER := 1.0;
753 l_msg_count NUMBER;
754 l_return_status VARCHAR2(1);
755 l_msg_data VARCHAR2(2000);
756 l_dummy NUMBER;
757 l_rowid VARCHAR2(30);
758 l_space_id NUMBER;
759 l_visit_id NUMBER;
760 l_space_assignment_id NUMBER;
761 l_space_assign_rec Space_Assignment_Rec;
762 --
763 CURSOR c_seq
764 IS
765 SELECT AHL_SPACE_ASSIGNMENTS_S.NEXTVAL
766 FROM dual;
767 --
768 CURSOR c_id_exists (x_id IN NUMBER) IS
769 SELECT 1
770 FROM dual
771 WHERE EXISTS (SELECT 1
772 FROM ahl_space_assignments
773 WHERE space_assignment_id = x_id);
774 --
775 BEGIN
776 --------------------Initialize ----------------------------------
777 -- Standard Start of API savepoint
778 SAVEPOINT create_space_assignment;
779 -- Check if API is called in debug mode. If yes, enable debug.
780 IF G_DEBUG='Y' THEN
781 AHL_DEBUG_PUB.enable_debug;
782 END IF;
783 -- Debug info.
784 IF G_DEBUG='Y' THEN
785 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Create Space Assignment','+SPASN+');
786 END IF;
787 -- Standard call to check for call compatibility.
788 IF FND_API.to_boolean(p_init_msg_list)
789 THEN
790 FND_MSG_PUB.initialize;
791 END IF;
792 -- Initialize API return status to success
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794 -- Initialize message list if p_init_msg_list is set to TRUE.
795 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
796 p_api_version,
797 l_api_name,G_PKG_NAME)
798 THEN
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801 --------------------Value OR ID conversion---------------------------
802 IF G_DEBUG='Y' THEN
803 AHL_DEBUG_PUB.debug( 'visit_id'||p_x_space_assign_rec.visit_id);
804 AHL_DEBUG_PUB.debug( 'space number'||p_x_space_assign_rec.space_name);
805 AHL_DEBUG_PUB.debug( 'space id'||p_x_space_assign_rec.space_id);
806 AHL_DEBUG_PUB.debug( 'space assign id'||p_x_space_assign_rec.space_assignment_id);
807 END IF;
808 --
809 IF p_reschedule_flag = 'Y' THEN
810 --Check is required during rescheduling
811 IF (p_x_space_assign_rec.visit_id IS NOT NULL AND
812 p_x_space_assign_rec.visit_id <> FND_API.G_MISS_NUM ) THEN
813 --
814 AHL_DEBUG_PUB.debug( 'inside schedule flag:'||p_x_space_assign_rec.space_name);
815 --
816 DELETE FROM AHL_SPACE_ASSIGNMENTS
817 WHERE visit_id = p_x_space_assign_rec.visit_id;
818 --
819 END IF;
820 --
821 END IF; --Reschedule flag
822
823 AHL_DEBUG_PUB.debug( 'number of records space_id'||p_x_space_assign_rec.space_name);
824
825 -- Convert Space name to space id
826 IF (p_x_space_assign_rec.space_name IS NOT NULL AND
827 p_x_space_assign_rec.space_name <> FND_API.G_MISS_CHAR ) OR
828 (p_x_space_assign_rec.space_id IS NOT NULL AND
829 p_x_space_assign_rec.space_id <> FND_API.G_MISS_NUM) THEN
830
831 Check_space_name_Or_Id
832 (p_space_id => null,
833 p_space_name => p_x_space_assign_rec.space_name,
834 x_space_id => l_space_id,
835 x_return_status => l_return_status,
836 x_error_msg_code => l_msg_data);
837
838 IF NVL(l_return_status,'x') <> 'S'
839 THEN
840 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_NOT_EXISTS');
841 Fnd_Message.SET_TOKEN('SPACEID',p_x_space_assign_rec.space_name);
842 Fnd_Msg_Pub.ADD;
843 END IF;
844 END IF;
845 --Assign the returned value
846 p_x_space_assign_rec.space_id := l_space_id;
847
848 -- Convert Visit Number to visit id
849 IF (p_x_space_assign_rec.visit_number IS NOT NULL AND
850 p_x_space_assign_rec.visit_number <> FND_API.G_MISS_NUM ) OR
851 (p_x_space_assign_rec.visit_id IS NOT NULL AND
852 p_x_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
853
854 Check_visit_number_Or_Id
855 (p_visit_id => p_x_space_assign_rec.visit_id,
856 p_visit_number => p_x_space_assign_rec.visit_number,
857 x_visit_id => l_visit_id,
858 x_return_status => l_return_status,
859 x_error_msg_code => l_msg_data);
860
861 IF NVL(l_return_status,'x') <> 'S'
862 THEN
863 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_ID_NOT_EXIST');
864 Fnd_Msg_Pub.ADD;
865 RAISE Fnd_Api.G_EXC_ERROR;
866 END IF;
867 END IF;
868 --Assign the returned value
869 p_x_space_assign_rec.visit_id := l_visit_id;
870
871 --Standard check to count messages
872 l_msg_count := Fnd_Msg_Pub.count_msg;
873
874 IF l_msg_count > 0 THEN
875 X_msg_count := l_msg_count;
876 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
877 RAISE Fnd_Api.G_EXC_ERROR;
878 END IF;
879
880 --------------------------------Validation ---------------------------
881 --Assign to local variable
882 Assign_Space_Assign_Rec (
883 p_space_assign_rec => p_x_space_assign_rec,
884 x_space_assign_rec => l_Space_assign_rec);
885
886 -- Call Validate space rec input attributes
887 Validate_Space_Assign
888 ( p_api_version => l_api_version,
889 p_init_msg_list => p_init_msg_list,
890 p_validation_level => p_validation_level,
891 p_space_assign_rec => l_Space_assign_rec,
892 x_return_status => l_return_status,
893 x_msg_count => l_msg_count,
894 x_msg_data => l_msg_data );
895
896
897 --Standard check to count messages
898 l_msg_count := Fnd_Msg_Pub.count_msg;
899
900 IF l_msg_count > 0 THEN
901 X_msg_count := l_msg_count;
902 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
903 RAISE Fnd_Api.G_EXC_ERROR;
904 END IF;
905
906
907 IF (p_x_space_assign_rec.space_assignment_id = Fnd_Api.G_MISS_NUM or
908 p_x_space_assign_rec.space_assignment_id IS NULL)
909 THEN
910 --
911 -- If the ID is not passed into the API, then
912 -- grab a value from the sequence.
913 OPEN c_seq;
914 FETCH c_seq INTO l_space_assignment_id;
915 CLOSE c_seq;
916 --
917 -- Check to be sure that the sequence does not exist.
918 OPEN c_id_exists (l_space_assignment_id);
919 FETCH c_id_exists INTO l_dummy;
920 CLOSE c_id_exists;
921 --
922 -- If the value for the ID already exists, then
923 -- l_dummy would be populated with '1', otherwise,
924 -- it receives NULL.
925 IF l_dummy IS NOT NULL THEN
926 Fnd_Message.SET_NAME('AHL','AHL_LTP_SEQUENCE_NOT_EXISTS');
927 Fnd_Msg_Pub.ADD;
928 END IF;
929 -- For optional fields
930 --
931 IF p_x_space_assign_rec.attribute_category = FND_API.G_MISS_CHAR
932 THEN
933 l_space_assign_rec.attribute_category := NULL;
934 ELSE
935 l_space_assign_rec.attribute_category := p_x_space_assign_rec.attribute_category;
936 END IF;
937 --
938 IF p_x_space_assign_rec.attribute1 = FND_API.G_MISS_CHAR
939 THEN
940 l_space_assign_rec.attribute1 := NULL;
941 ELSE
942 l_space_assign_rec.attribute1 := p_x_space_assign_rec.attribute1;
943 END IF;
944 --
945 IF p_x_space_assign_rec.attribute2 = FND_API.G_MISS_CHAR
946 THEN
947 l_space_assign_rec.attribute2 := NULL;
948 ELSE
949 l_space_assign_rec.attribute2 := p_x_space_assign_rec.attribute2;
950 END IF;
951 --
952 IF p_x_space_assign_rec.attribute3 = FND_API.G_MISS_CHAR
953 THEN
954 l_space_assign_rec.attribute3 := NULL;
955 ELSE
956 l_space_assign_rec.attribute3 := p_x_space_assign_rec.attribute3;
957 END IF;
958 --
959 IF p_x_space_assign_rec.attribute4 = FND_API.G_MISS_CHAR
960 THEN
961 l_space_assign_rec.attribute4 := NULL;
962 ELSE
963 l_space_assign_rec.attribute4 := p_x_space_assign_rec.attribute4;
964 END IF;
965 --
966 IF p_x_space_assign_rec.attribute5 = FND_API.G_MISS_CHAR
967 THEN
968 l_space_assign_rec.attribute5 := NULL;
969 ELSE
970 l_space_assign_rec.attribute5 := p_x_space_assign_rec.attribute5;
971 END IF;
972 --
973 IF p_x_space_assign_rec.attribute6 = FND_API.G_MISS_CHAR
974 THEN
975 l_space_assign_rec.attribute6 := NULL;
976 ELSE
977 l_space_assign_rec.attribute6 := p_x_space_assign_rec.attribute6;
978 END IF;
979 --
980 IF p_x_space_assign_rec.attribute7 = FND_API.G_MISS_CHAR
981 THEN
982 l_space_assign_rec.attribute7 := NULL;
983 ELSE
984 l_space_assign_rec.attribute7 := p_x_space_assign_rec.attribute7;
985 END IF;
986 --
987 IF p_x_space_assign_rec.attribute8 = FND_API.G_MISS_CHAR
988 THEN
989 l_space_assign_rec.attribute8 := NULL;
990 ELSE
991 l_space_assign_rec.attribute8 := p_x_space_assign_rec.attribute8;
992 END IF;
993 --
994 IF p_x_space_assign_rec.attribute9 = FND_API.G_MISS_CHAR
995 THEN
996 l_space_assign_rec.attribute9 := NULL;
997 ELSE
998 l_space_assign_rec.attribute9 := p_x_space_assign_rec.attribute9;
999 END IF;
1000 --
1001 IF p_x_space_assign_rec.attribute10 = FND_API.G_MISS_CHAR
1002 THEN
1003 l_space_assign_rec.attribute10 := NULL;
1004 ELSE
1005 l_space_assign_rec.attribute10 := p_x_space_assign_rec.attribute10;
1006 END IF;
1007 --
1008 IF p_x_space_assign_rec.attribute11 = FND_API.G_MISS_CHAR
1009 THEN
1010 l_space_assign_rec.attribute11 := NULL;
1011 ELSE
1012 l_space_assign_rec.attribute11 := p_x_space_assign_rec.attribute11;
1013 END IF;
1014 --
1015 IF p_x_space_assign_rec.attribute12 = FND_API.G_MISS_CHAR
1016 THEN
1017 l_space_assign_rec.attribute12 := NULL;
1018 ELSE
1019 l_space_assign_rec.attribute12 := p_x_space_assign_rec.attribute12;
1020 END IF;
1021 --
1022 IF p_x_space_assign_rec.attribute13 = FND_API.G_MISS_CHAR
1023 THEN
1024 l_space_assign_rec.attribute13 := NULL;
1025 ELSE
1026 l_space_assign_rec.attribute13 := p_x_space_assign_rec.attribute13;
1027 END IF;
1028 --
1029 IF p_x_space_assign_rec.attribute14 = FND_API.G_MISS_CHAR
1030 THEN
1031 l_space_assign_rec.attribute14 := NULL;
1032 ELSE
1033 l_space_assign_rec.attribute14 := p_x_space_assign_rec.attribute14;
1034 END IF;
1035 --
1036 IF p_x_space_assign_rec.attribute15 = FND_API.G_MISS_CHAR
1037 THEN
1038 l_space_assign_rec.attribute15 := NULL;
1039 ELSE
1040 l_space_assign_rec.attribute15 := p_x_space_assign_rec.attribute15;
1041 END IF;
1042 END IF;
1043 --Standard check to count messages
1044 l_msg_count := Fnd_Msg_Pub.count_msg;
1045
1046 IF l_msg_count > 0 THEN
1047 X_msg_count := l_msg_count;
1048 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1049 RAISE Fnd_Api.G_EXC_ERROR;
1050 END IF;
1051
1052 Ahl_Debug_Pub.debug( 'Before insert state'||l_space_assignment_id);
1053
1054 ----------------------------DML Operation---------------------------------
1055 --insert the record
1056 INSERT INTO AHL_SPACE_ASSIGNMENTS
1057 (
1058 SPACE_ASSIGNMENT_ID,
1059 SPACE_ID,
1060 VISIT_ID,
1061 OBJECT_VERSION_NUMBER,
1062 ATTRIBUTE_CATEGORY,
1063 ATTRIBUTE1,
1064 ATTRIBUTE2,
1065 ATTRIBUTE3,
1066 ATTRIBUTE4,
1067 ATTRIBUTE5,
1068 ATTRIBUTE6,
1069 ATTRIBUTE7,
1070 ATTRIBUTE8,
1071 ATTRIBUTE9,
1072 ATTRIBUTE10,
1073 ATTRIBUTE11,
1074 ATTRIBUTE12,
1075 ATTRIBUTE13,
1076 ATTRIBUTE14,
1077 ATTRIBUTE15,
1078 LAST_UPDATE_DATE,
1079 LAST_UPDATED_BY,
1080 CREATION_DATE,
1081 CREATED_BY,
1082 LAST_UPDATE_LOGIN
1083 )
1084 VALUES
1085 (
1086 l_space_assignment_id,
1087 l_space_assign_rec.space_id,
1088 l_space_assign_rec.visit_id,
1089 1,
1090 l_space_assign_rec.attribute_category,
1091 l_space_assign_rec.attribute1,
1092 l_space_assign_rec.attribute2,
1093 l_space_assign_rec.attribute3,
1094 l_space_assign_rec.attribute4,
1095 l_space_assign_rec.attribute5,
1096 l_space_assign_rec.attribute6,
1097 l_space_assign_rec.attribute7,
1098 l_space_assign_rec.attribute8,
1099 l_space_assign_rec.attribute9,
1100 l_space_assign_rec.attribute10,
1101 l_space_assign_rec.attribute11,
1102 l_space_assign_rec.attribute12,
1103 l_space_assign_rec.attribute13,
1104 l_space_assign_rec.attribute14,
1105 l_space_assign_rec.attribute15,
1106 SYSDATE,
1107 Fnd_Global.user_id,
1108 SYSDATE,
1109 Fnd_Global.user_id,
1110 Fnd_Global.login_id
1111 );
1112
1113 p_x_space_assign_rec.space_assignment_id := l_space_assignment_id;
1114 ---------------------------End of Body---------------------------------------
1115 --Standard check to count messages
1116 l_msg_count := Fnd_Msg_Pub.count_msg;
1117
1118 IF l_msg_count > 0 THEN
1119 X_msg_count := l_msg_count;
1120 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1121 RAISE Fnd_Api.G_EXC_ERROR;
1122 END IF;
1123
1124 --Standard check for commit
1125 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1126 COMMIT;
1127 END IF;
1128 -- Debug info
1129 IF G_DEBUG='Y' THEN
1130 Ahl_Debug_Pub.debug( 'End of private api Create Space assignment','+SPANS+');
1131 -- Check if API is called in debug mode. If yes, disable debug.
1132 Ahl_Debug_Pub.disable_debug;
1133 END IF;
1134 EXCEPTION
1135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1136 ROLLBACK TO create_space_assignment;
1137 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1139 p_count => x_msg_count,
1140 p_data => x_msg_data);
1141 IF G_DEBUG='Y' THEN
1142
1143 AHL_DEBUG_PUB.log_app_messages (
1144 x_msg_count, x_msg_data, 'ERROR' );
1145 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Create Space assignment','+SPASN+');
1146 -- Check if API is called in debug mode. If yes, disable debug.
1147 AHL_DEBUG_PUB.disable_debug;
1148 END IF;
1149 WHEN FND_API.G_EXC_ERROR THEN
1150 ROLLBACK TO create_space_assignment;
1151 X_return_status := FND_API.G_RET_STS_ERROR;
1152 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1153 p_count => x_msg_count,
1154 p_data => X_msg_data);
1155 IF G_DEBUG='Y' THEN
1156 -- Debug info.
1157 AHL_DEBUG_PUB.log_app_messages (
1158 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1159 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Create Space assignment','+SPASN+');
1160 -- Check if API is called in debug mode. If yes, disable debug.
1161 AHL_DEBUG_PUB.disable_debug;
1162 END IF;
1163 WHEN OTHERS THEN
1164 ROLLBACK TO create_space_assignment;
1165 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1167 THEN
1168 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1169 p_procedure_name => 'CREATE_SPACE_ASSIGNMENT',
1170 p_error_text => SUBSTR(SQLERRM,1,240));
1171 END IF;
1172 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1173 p_count => x_msg_count,
1174 p_data => X_msg_data);
1175 IF G_DEBUG='Y' THEN
1176 -- Debug info.
1177 AHL_DEBUG_PUB.log_app_messages (
1178 x_msg_count, x_msg_data, 'SQL ERROR' );
1179 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Create Space assignment','+SPASN+');
1180 -- Check if API is called in debug mode. If yes, disable debug.
1181 AHL_DEBUG_PUB.disable_debug;
1182 END IF;
1183 END Create_Space_assignment;
1184 --
1185 -- PROCEDURE
1186 -- Update_Space_Assignment
1187 --
1188 -- PURPOSE
1189 -- Update Space Assignment Record.
1190 --
1191 -- PARAMETERS
1192 -- p_space_assign_rec: the record representing AHL_SPACE_ASSIGNMENT
1193 --
1194 -- NOTES
1195 --
1196 PROCEDURE Update_Space_Assignment (
1197 p_api_version IN NUMBER,
1198 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1199 p_commit IN VARCHAR2 := FND_API.g_false,
1200 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1201 p_module_type IN VARCHAR2 := 'JSP',
1202 p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1203 x_return_status OUT NOCOPY VARCHAR2,
1204 x_msg_count OUT NOCOPY NUMBER,
1205 x_msg_data OUT NOCOPY VARCHAR2
1206 )
1207 IS
1208 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SPACE_ASSIGNMENT';
1209 l_api_version CONSTANT NUMBER := 1.0;
1210 l_msg_count NUMBER;
1211 l_return_status VARCHAR2(1);
1212 l_msg_data VARCHAR2(2000);
1213 l_dummy NUMBER;
1214 l_rowid VARCHAR2(30);
1215 l_space_id NUMBER;
1216 l_visit_id NUMBER;
1217 l_space_assignment_id NUMBER;
1218 l_space_assign_rec Space_Assignment_Rec;
1219
1220 BEGIN
1221 --------------------Initialize ----------------------------------
1222 -- Standard Start of API savepoint
1223 SAVEPOINT update_space_assignment;
1224 -- Check if API is called in debug mode. If yes, enable debug.
1225 IF G_DEBUG='Y' THEN
1226 AHL_DEBUG_PUB.enable_debug;
1227 END IF;
1228 -- Debug info.
1229 IF G_DEBUG='Y' THEN
1230 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
1231 END IF;
1232 -- Standard call to check for call compatibility.
1233 IF FND_API.to_boolean(p_init_msg_list)
1234 THEN
1235 FND_MSG_PUB.initialize;
1236 END IF;
1237 -- Initialize API return status to success
1238 x_return_status := FND_API.G_RET_STS_SUCCESS;
1239 -- Initialize message list if p_init_msg_list is set to TRUE.
1240 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1241 p_api_version,
1242 l_api_name,G_PKG_NAME)
1243 THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246
1247 ---------------------start API Body------------------------------------
1248 --Assign to local variable
1249 Assign_Space_Assign_Rec (
1250 p_space_assign_rec => p_space_assign_rec,
1251 x_space_assign_rec => l_Space_assign_rec);
1252 --------------------Value OR ID conversion---------------------------
1253 -- Convert Space name to space id
1254 IF (p_space_assign_rec.space_name IS NOT NULL AND
1255 p_space_assign_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1256 (p_space_assign_rec.space_id IS NOT NULL AND
1257 p_space_assign_rec.space_id <> FND_API.G_MISS_NUM) THEN
1258
1259 Check_space_name_Or_Id
1260 (p_space_id => p_space_assign_rec.space_id,
1261 p_space_name => p_space_assign_rec.space_name,
1262 x_space_id => l_space_id,
1263 x_return_status => l_return_status,
1264 x_error_msg_code => l_msg_data);
1265
1266 IF NVL(l_return_status,'x') <> 'S'
1267 THEN
1268 Fnd_Message.SET_NAME('AHL','AHL_LTP_SPACE_NOT_EXISTS');
1269 Fnd_Message.SET_TOKEN('SPACEID',p_space_assign_rec.space_name);
1270 Fnd_Msg_Pub.ADD;
1271 END IF;
1272 END IF;
1273 --Assign the returned value
1274 l_space_assign_rec.space_id := l_space_id;
1275
1276 -- Convert Visit Number to visit id
1277 IF (p_space_assign_rec.visit_number IS NOT NULL AND
1278 p_space_assign_rec.visit_number <> FND_API.G_MISS_NUM ) OR
1279 (p_space_assign_rec.visit_id IS NOT NULL AND
1280 p_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1281
1282 Check_visit_number_Or_Id
1283 (p_visit_id => p_space_assign_rec.visit_id,
1284 p_visit_number => p_space_assign_rec.visit_number,
1285 x_visit_id => l_visit_id,
1286 x_return_status => l_return_status,
1287 x_error_msg_code => l_msg_data);
1288
1289 IF NVL(l_return_status,'x') <> 'S'
1290 THEN
1291 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_NOT_EXISTS');
1292 Fnd_Message.SET_TOKEN('VISITID',p_space_assign_rec.visit_number);
1293 Fnd_Msg_Pub.ADD;
1294 END IF;
1295 END IF;
1296 --Assign the returned value
1297 l_space_assign_rec.visit_id := l_visit_id;
1298
1299 --------------------------------Validation ---------------------------
1300 -- get existing values and compare
1301 Complete_Space_Assign_Rec (
1302 p_space_assign_rec => l_space_assign_rec,
1303 x_space_assign_rec => l_space_assign_rec);
1304
1305 -- Call Validate space assignment attributes
1306 Validate_Space_Assign
1307 ( p_api_version => l_api_version,
1308 p_init_msg_list => p_init_msg_list,
1309 p_validation_level => p_validation_level,
1310 p_space_assign_rec => l_Space_assign_rec,
1311 x_return_status => l_return_status,
1312 x_msg_count => l_msg_count,
1313 x_msg_data => l_msg_data );
1314
1315 ----------------------------DML Operation---------------------------------
1316 --Call table handler generated package to update a record
1317 UPDATE AHL_SPACE_ASSIGNMENTS
1318 SET visit_id = l_Space_assign_rec.visit_id,
1319 space_id = l_Space_assign_rec.space_id,
1320 object_version_number = l_Space_assign_rec.object_version_number+1,
1321 attribute_category = l_Space_assign_rec.attribute_category,
1322 attribute1 = l_Space_assign_rec.attribute1,
1323 attribute2 = l_Space_assign_rec.attribute2,
1324 attribute3 = l_Space_assign_rec.attribute3,
1325 attribute4 = l_Space_assign_rec.attribute4,
1326 attribute5 = l_Space_assign_rec.attribute5,
1327 attribute6 = l_Space_assign_rec.attribute6,
1328 attribute7 = l_Space_assign_rec.attribute7,
1329 attribute8 = l_Space_assign_rec.attribute8,
1330 attribute9 = l_Space_assign_rec.attribute9,
1331 attribute10 = l_Space_assign_rec.attribute10,
1332 attribute11 = l_Space_assign_rec.attribute11,
1333 attribute12 = l_Space_assign_rec.attribute12,
1334 attribute13 = l_Space_assign_rec.attribute13,
1335 attribute14 = l_Space_assign_rec.attribute14,
1336 attribute15 = l_Space_assign_rec.attribute15,
1337 last_update_date = SYSDATE,
1338 last_updated_by = Fnd_Global.user_id,
1339 last_update_login = Fnd_Global.login_id
1340 WHERE space_assignment_id = p_space_assign_rec.space_assignment_id;
1341
1342
1343 ---------------------------End of Body---------------------------------------
1344 --Standard check to count messages
1345 l_msg_count := Fnd_Msg_Pub.count_msg;
1346
1347 IF l_msg_count > 0 THEN
1348 X_msg_count := l_msg_count;
1349 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1350 RAISE Fnd_Api.G_EXC_ERROR;
1351 END IF;
1352
1353 --Standard check for commit
1354 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1355 COMMIT;
1356 END IF;
1357 -- Debug info
1358 IF G_DEBUG='Y' THEN
1359 Ahl_Debug_Pub.debug( 'End of private api Update Space assignment','+SPANT+');
1360 END IF;
1361 -- Check if API is called in debug mode. If yes, disable debug.
1362 IF G_DEBUG='Y' THEN
1363 Ahl_Debug_Pub.disable_debug;
1364 END IF;
1365
1366 EXCEPTION
1367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1368 ROLLBACK TO update_space_assignment;
1369 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1370 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1371 p_count => x_msg_count,
1372 p_data => x_msg_data);
1373 IF G_DEBUG='Y' THEN
1374
1375 AHL_DEBUG_PUB.log_app_messages (
1376 x_msg_count, x_msg_data, 'ERROR' );
1377 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
1378 -- Check if API is called in debug mode. If yes, disable debug.
1379 AHL_DEBUG_PUB.disable_debug;
1380 END IF;
1381 WHEN FND_API.G_EXC_ERROR THEN
1382 ROLLBACK TO update_space_assignment;
1383 X_return_status := FND_API.G_RET_STS_ERROR;
1384 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1385 p_count => x_msg_count,
1386 p_data => X_msg_data);
1387 IF G_DEBUG='Y' THEN
1388 -- Debug info.
1389 AHL_DEBUG_PUB.log_app_messages (
1390 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1391 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
1392 -- Check if API is called in debug mode. If yes, disable debug.
1393 AHL_DEBUG_PUB.disable_debug;
1394 END IF;
1395 WHEN OTHERS THEN
1396 ROLLBACK TO update_space_assignment;
1397 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1399 THEN
1400 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1401 p_procedure_name => 'UPDATE_SPACE_ASSIGNMENT',
1402 p_error_text => SUBSTR(SQLERRM,1,240));
1403 END IF;
1404 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1405 p_count => x_msg_count,
1406 p_data => X_msg_data);
1407
1408 IF G_DEBUG='Y' THEN
1409 -- Debug info.
1410 AHL_DEBUG_PUB.log_app_messages (
1411 x_msg_count, x_msg_data, 'SQL ERROR' );
1412 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignemnt','+SPANT+');
1413 -- Check if API is called in debug mode. If yes, disable debug.
1414 AHL_DEBUG_PUB.disable_debug;
1415 END IF;
1416 END Update_Space_Assignment;
1417 --
1418 -- PROCEDURE
1419 -- Delete_Space_Assignment
1420 --
1421 -- PURPOSE
1422 -- Delete Space Assignment Record.
1423 --
1424 -- PARAMETERS
1425 --
1426 -- ISSUES
1427 --
1428 -- NOTES
1429 -- 1. Raise exception if the object_version_number doesn't match.
1430 --
1431 PROCEDURE Delete_Space_Assignment (
1432 p_api_version IN NUMBER,
1433 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1434 p_commit IN VARCHAR2 := FND_API.g_false,
1435 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1436 p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1437 x_return_status OUT NOCOPY VARCHAR2,
1438 x_msg_count OUT NOCOPY NUMBER,
1439 x_msg_data OUT NOCOPY VARCHAR2
1440
1441 )
1442 IS
1443 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SPACE_ASSIGNMENT';
1444 l_api_version CONSTANT NUMBER := 1.0;
1445 l_msg_count NUMBER;
1446 l_return_status VARCHAR2(1);
1447 l_msg_data VARCHAR2(2000);
1448 l_dummy NUMBER;
1449 l_space_assignment_id NUMBER;
1450 l_space_id NUMBER;
1451 l_object_version_number NUMBER;
1452
1453 CURSOR c_space_assign_cur
1454 (c_space_assignment_id IN NUMBER)
1455 IS
1456 SELECT space_assignment_id,object_version_number
1457 FROM ahl_space_assignments
1458 WHERE space_assignment_id = c_space_assignment_id;
1459
1460 CURSOR c_visit_spaces_cur
1461 (c_visit_id IN NUMBER)
1462 IS
1463 SELECT sa.space_assignment_id,
1464 sa.space_id,
1465 sa.visit_id,
1466 trunc(vt.start_date_time) start_date_time,
1467 trunc(vt.close_date_time) close_date_time,
1468 vt.organization_id,
1469 vt.department_id,
1470 sp.organization_id sporg_id,
1471 sp.bom_department_id spdept_id
1472 FROM ahl_space_assignments sa,
1473 ahl_visits_vl vt,
1474 ahl_spaces_b sp
1475 WHERE sa.visit_id = vt.visit_id
1476 AND sp.space_id = sa.space_id
1477 AND vt.visit_id = c_visit_id;
1478
1479 CURSOR c_check_unavail_cur
1480 (c_space_id IN NUMBER,
1481 c_start_date IN DATE,
1482 c_end_date IN DATE)
1483 IS
1484 SELECT 1
1485 FROM ahl_space_unavailable_b
1486 WHERE space_id = space_id
1487 AND (c_start_date between trunc(start_date) and trunc(end_date)
1488 OR
1489 c_end_date between trunc(start_date) and trunc(end_date));
1490
1491 l_visit_spaces_rec c_visit_spaces_cur%ROWTYPE;
1492
1493 BEGIN
1494 --------------------Initialize ----------------------------------
1495 -- Standard Start of API savepoint
1496 SAVEPOINT delete_space_assignment;
1497 -- Check if API is called in debug mode. If yes, enable debug.
1498 IF G_DEBUG='Y' THEN
1499 AHL_DEBUG_PUB.enable_debug;
1500 END IF;
1501 -- Debug info.
1502 IF G_DEBUG='Y' THEN
1503 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
1504 END IF;
1505 -- Standard call to check for call compatibility.
1506 IF FND_API.to_boolean(p_init_msg_list)
1507 THEN
1508 FND_MSG_PUB.initialize;
1509 END IF;
1510 -- Initialize API return status to success
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512 -- Initialize message list if p_init_msg_list is set to TRUE.
1513 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1514 p_api_version,
1515 l_api_name,G_PKG_NAME)
1516 THEN
1517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1518 END IF;
1519 -----------------------Start of API Body-----------------------------
1520 IF (p_space_assign_rec.visit_id IS NOT NULL AND
1521 p_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1522 --
1523 IF G_DEBUG='Y' THEN
1524 AHL_DEBUG_PUB.debug( 'visit id'||p_space_assign_rec.visit_id);
1525 END IF;
1526
1527 OPEN c_visit_spaces_cur(p_space_assign_rec.visit_id);
1528 LOOP
1529 FETCH c_visit_spaces_cur INTO l_visit_spaces_rec;
1530 EXIT WHEN c_visit_spaces_cur%NOTFOUND;
1531 IF l_visit_spaces_rec.space_id IS NOT NULL THEN
1532
1533 IF G_DEBUG='Y' THEN
1534 AHL_DEBUG_PUB.debug( 'Space ID'||l_visit_spaces_rec.space_id);
1535 AHL_DEBUG_PUB.debug( 'org ID'||l_visit_spaces_rec.organization_id);
1536 AHL_DEBUG_PUB.debug( 'dept ID'||l_visit_spaces_rec.department_id);
1537 AHL_DEBUG_PUB.debug( 'sorg ID'||l_visit_spaces_rec.sporg_id);
1538 AHL_DEBUG_PUB.debug( 'sdept ID'||l_visit_spaces_rec.spdept_id);
1539 END IF;
1540 --
1541 IF (nvl(l_visit_spaces_rec.organization_id,-1) <> l_visit_spaces_rec.sporg_id
1542 OR nvl(l_visit_spaces_rec.department_id,-1) <> l_visit_spaces_rec.spdept_id )
1543 THEN
1544 --Remove space assignments
1545 DELETE FROM AHL_SPACE_ASSIGNMENTS
1546 WHERE space_assignment_id = l_visit_spaces_rec.space_assignment_id;
1547 ELSE
1548 --Check for space Unnavailabilty condition
1549 OPEN c_check_unavail_cur(l_visit_spaces_rec.space_id,
1550 l_visit_spaces_rec.start_date_time,
1551 nvl(l_visit_spaces_rec.close_date_time,
1552 l_visit_spaces_rec.start_date_time));
1553 FETCH c_check_unavail_cur INTO l_dummy;
1554 IF c_check_unavail_cur%FOUND THEN
1555 --Remove space assignments
1556 DELETE FROM AHL_SPACE_ASSIGNMENTS
1557 WHERE space_Assignment_id = l_visit_spaces_rec.space_assignment_id;
1558 END IF;
1559 CLOSE c_check_unavail_cur;
1560 END IF;
1561 END IF;
1562 END LOOP;
1563 CLOSE c_visit_spaces_cur;
1564 END IF;
1565 --
1566 IF (p_space_assign_rec.space_assignment_id IS NOT NULL AND
1567 p_space_assign_rec.space_assignment_id <> FND_API.G_MISS_NUM )
1568
1569 THEN
1570 -- Check for Record exists
1571 OPEN c_space_assign_cur(p_space_assign_rec.space_assignment_id);
1572 FETCH c_space_assign_cur INTO l_space_assignment_id,
1573 l_object_version_number;
1574 IF c_space_assign_cur%NOTFOUND THEN
1575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1576 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
1577 FND_MSG_PUB.add;
1578 END IF;
1579 CLOSE c_space_assign_cur;
1580 RAISE FND_API.g_exc_error;
1581 END IF;
1582 CLOSE c_space_assign_cur;
1583 IF G_DEBUG='Y' THEN
1584 AHL_DEBUG_PUB.debug( 'space assign id'||l_space_assignment_id);
1585 AHL_DEBUG_PUB.debug( 'l ovn number'||l_object_version_number);
1586 AHL_DEBUG_PUB.debug( 'p ovn number'||p_space_assign_rec.object_version_number);
1587 END IF;
1588 --Check for object version number
1589 IF l_object_version_number <> p_space_assign_rec.object_version_number
1590 THEN
1591 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1592 FND_MSG_PUB.add;
1593 RAISE FND_API.g_exc_error;
1594 END IF;
1595 -------------------Call Table handler generated procedure------------
1596 DELETE FROM AHL_SPACE_ASSIGNMENTS
1597 WHERE SPACE_ASSIGNMENT_ID = p_space_assign_rec.space_assignment_id;
1598 END IF;
1599 ---------------------------End of Body---------------------------------------
1600 --Standard check to count messages
1601 l_msg_count := Fnd_Msg_Pub.count_msg;
1602
1603 IF l_msg_count > 0 THEN
1604 X_msg_count := l_msg_count;
1605 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1606 RAISE Fnd_Api.G_EXC_ERROR;
1607 END IF;
1608
1609 --Standard check for commit
1610 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1611 COMMIT;
1612 END IF;
1613 -- Debug info
1614 IF G_DEBUG='Y' THEN
1615 Ahl_Debug_Pub.debug( 'End of private api Delete Space Assignment','+SPANT+');
1616 END IF;
1617 -- Check if API is called in debug mode. If yes, disable debug.
1618 IF G_DEBUG='Y' THEN
1619 Ahl_Debug_Pub.disable_debug;
1620 END IF;
1621
1622 EXCEPTION
1623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624 ROLLBACK TO delete_space_assignment;
1625 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1627 p_count => x_msg_count,
1628 p_data => x_msg_data);
1629 IF G_DEBUG='Y' THEN
1630
1631 AHL_DEBUG_PUB.log_app_messages (
1632 x_msg_count, x_msg_data, 'ERROR' );
1633 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
1634 -- Check if API is called in debug mode. If yes, disable debug.
1635 AHL_DEBUG_PUB.disable_debug;
1636 END IF;
1637 WHEN FND_API.G_EXC_ERROR THEN
1638 ROLLBACK TO delete_space_assignment;
1639 X_return_status := FND_API.G_RET_STS_ERROR;
1640 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1641 p_count => x_msg_count,
1642 p_data => X_msg_data);
1643 IF G_DEBUG='Y' THEN
1644
1645 -- Debug info.
1646 AHL_DEBUG_PUB.log_app_messages (
1647 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1648 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
1649 -- Check if API is called in debug mode. If yes, disable debug.
1650 AHL_DEBUG_PUB.disable_debug;
1651 END IF;
1652 WHEN OTHERS THEN
1653 ROLLBACK TO delete_space_assignment;
1654 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1655 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1656 THEN
1657 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1658 p_procedure_name => 'DELETE_SPACE_ASSIGNMENT',
1659 p_error_text => SUBSTR(SQLERRM,1,240));
1660 END IF;
1661 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1662 p_count => x_msg_count,
1663 p_data => X_msg_data);
1664 IF G_DEBUG='Y' THEN
1665
1666 -- Debug info.
1667 AHL_DEBUG_PUB.log_app_messages (
1668 x_msg_count, x_msg_data, 'SQL ERROR' );
1669 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
1670 -- Check if API is called in debug mode. If yes, disable debug.
1671 AHL_DEBUG_PUB.disable_debug;
1672 END IF;
1673 END Delete_Space_Assignment;
1674 --
1675 -- PROCEDURE
1676 -- Schedule_Visit
1677 --
1678 -- PURPOSE
1679 -- Schedule_Visit
1680 --
1681 -- PARAMETERS
1682 -- p_schedule_visit_rec : Record Representing Schedule_Visit_Rec
1683 --
1684 -- NOTES
1685 -- anraj: 09-FEB-2005
1686 -- i. The calls to AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Task_Matrls has been commnetd out because
1687 -- it is handled in AHL_VWP_VISITS_PVT.Process_Visit.
1688 -- ii. The code to remove space assignment has been commented because it is handled in AHL_VWP_VISITS_PVT.Process_Visit
1689 -- iii. Commented cursors c_space_assign_cur,c_visit_sched_cur,visit_info_cur
1690 PROCEDURE Schedule_Visit (
1691 p_api_version IN NUMBER,
1692 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1693 p_commit IN VARCHAR2 := FND_API.g_false,
1694 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1695 p_module_type IN VARCHAR2 := 'JSP',
1696 p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
1697 x_return_status OUT NOCOPY VARCHAR2,
1698 x_msg_count OUT NOCOPY NUMBER,
1699 x_msg_data OUT NOCOPY VARCHAR2
1700 )
1701 IS
1702 -- Get the existing visit details
1703 CURSOR schedule_visit_cur (c_visit_id IN NUMBER)
1704 IS
1705 SELECT visit_id,
1706 object_version_number,
1707 status_code
1708 FROM AHL_VISITS_B
1709 WHERE VISIT_ID = c_visit_id;
1710
1711 -- anraj: commented, issue number 144
1712 -- To Check space assignments having different org
1713 /*
1714 CURSOR c_space_assign_cur (c_visit_id IN NUMBER)
1715 IS
1716 SELECT space_assignment_id,
1717 object_version_number
1718 FROM AHL_SPACE_ASSIGNMENTS A
1719 WHERE VISIT_ID = c_visit_id;
1720 */
1721 -- anraj: commented, issue number 144
1722 /*
1723 CURSOR c_visit_sched_cur (c_visit_id IN NUMBER)
1724 IS
1725 SELECT 1
1726 FROM AHL_VISITS_VL
1727 WHERE VISIT_ID = c_visit_id
1728 AND (organization_id IS NULL
1729 OR department_id IS NULL
1730 OR start_date_time IS NULL );
1731 */
1732
1733 CURSOR visit_det_cur IS
1734 SELECT organization_id,
1735 trunc(start_date_time),
1736 visit_name
1737 FROM ahl_visits_vl
1738 WHERE visit_id = p_x_schedule_visit_rec.visit_id;
1739
1740 -- anraj: commented, issue number 144
1741 /*
1742 CURSOR visit_info_cur IS
1743 SELECT organization_id,
1744 organization_name,
1745 department_id,
1746 department_name,
1747 visit_type_code
1748 FROM ahl_visits_info_v
1749 WHERE VISIT_ID = p_x_schedule_visit_rec.visit_id;
1750 */
1751
1752 --
1753 l_api_name CONSTANT VARCHAR2(30) := 'SCHEDULE_VISIT';
1754 l_api_version CONSTANT NUMBER := 1.0;
1755 l_msg_count NUMBER;
1756 l_return_status VARCHAR2(1);
1757 l_msg_data VARCHAR2(2000);
1758 --l_dummy VARCHAR2(10);
1759 l_rowid VARCHAR2(30);
1760 l_organization_id NUMBER;
1761 l_date VARCHAR2(30);
1762 l_department_id NUMBER;
1763 l_org_name VARCHAR2(240);
1764 l_dept_name VARCHAR2(240);
1765 l_visit_id NUMBER;
1766 l_visit_type_code VARCHAR2(30);
1767 l_object_version_number NUMBER;
1768 l_start_date_time DATE;
1769 l_visit_name VARCHAR2(80);
1770 l_visit_status_code VARCHAR2(30);
1771 --
1772 l_schedule_visit_rec schedule_visit_cur%ROWTYPE;
1773 --l_space_assign_rec c_space_assign_cur%ROWTYPE;
1774 --
1775 l_Visit_tbl ahl_vwp_visits_pvt.Visit_Tbl_Type;
1776 i number := 0;
1777 BEGIN
1778
1779 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1780 fnd_log.string
1781 (
1782 fnd_log.level_procedure,
1783 'ahl.plsql.AHL_LTP_SPACE_ASSIGN_PVT.Schedule_Visit',
1784 'At the start of PLSQL procedure'
1785 );
1786 END IF;
1787
1788 --------------------Initialize ----------------------------------
1789 -- Standard Start of API savepoint
1790 SAVEPOINT schedule_visit;
1791 -- Check if API is called in debug mode. If yes, enable debug.
1792 IF G_DEBUG='Y' THEN
1793 AHL_DEBUG_PUB.enable_debug;
1794 END IF;
1795 -- Debug info.
1796 IF G_DEBUG='Y' THEN
1797 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Schedule Visit','+SPANT+');
1798 END IF;
1799 -- Standard call to check for call compatibility.
1800 IF FND_API.to_boolean(p_init_msg_list)
1801 THEN
1802 FND_MSG_PUB.initialize;
1803 END IF;
1804 -- Initialize API return status to success
1805 x_return_status := FND_API.G_RET_STS_SUCCESS;
1806 -- Initialize message list if p_init_msg_list is set to TRUE.
1807 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1808 p_api_version,
1809 l_api_name,G_PKG_NAME)
1810 THEN
1811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1812 END IF;
1813 ---------------------start API Body------------------------------------
1814 IF p_module_type = 'JSP'
1815 THEN
1816 p_x_schedule_visit_rec.org_id := null;
1817 p_x_schedule_visit_rec.dept_id := null;
1818 END IF;
1819
1820 IF G_DEBUG='Y' THEN
1821 AHL_DEBUG_PUB.debug( 'planned end hour'||p_x_schedule_visit_rec.planned_end_hour);
1822 AHL_DEBUG_PUB.debug( 'plan end date'||p_x_schedule_visit_rec.planned_end_date);
1823 END IF;
1824
1825 IF G_DEBUG='Y' THEN
1826 AHL_DEBUG_PUB.debug( 'dept id'||p_x_schedule_visit_rec.org_name);
1827 END IF;
1828
1829 -- moved this block of code up, to get acess to l_visit_id
1830 -- Convert Visit Number to visit id
1831 IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
1832 p_x_schedule_visit_rec.visit_number <> FND_API.G_MISS_NUM ) OR
1833 (p_x_schedule_visit_rec.visit_id IS NOT NULL AND
1834 p_x_schedule_visit_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1835
1836 Check_visit_number_Or_Id
1837 (p_visit_id => p_x_schedule_visit_rec.visit_id,
1838 p_visit_number => p_x_schedule_visit_rec.visit_number,
1839 x_visit_id => l_visit_id,
1840 x_return_status => l_return_status,
1841 x_error_msg_code => l_msg_data);
1842
1843 IF NVL(l_return_status,'x') <> 'S'
1844 THEN
1845 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_NOT_EXISTS');
1846 Fnd_Message.SET_TOKEN('VISITID',p_x_schedule_visit_rec.visit_number);
1847 Fnd_Msg_Pub.ADD;
1848 END IF;
1849 END IF;
1850
1851 --Get the existing Record
1852 OPEN schedule_visit_cur(l_visit_id);
1853 FETCH schedule_visit_cur INTO l_schedule_visit_rec;
1854 CLOSE schedule_visit_cur;
1855
1856
1857 --Assign the returned value
1858 p_x_schedule_visit_rec.visit_id := l_visit_id;
1859
1860
1861 --Convert Value To ID
1862 IF ( p_x_schedule_visit_rec.org_name IS NULL OR
1863 p_x_schedule_visit_rec.org_name = FND_API.G_MISS_CHAR) THEN
1864 -- anraj: if visit is in planning Organization is not mandatory
1865 IF (l_schedule_visit_rec.status_code <> 'PLANNING') THEN
1866 Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_REQUIRED');
1867 Fnd_Msg_Pub.ADD;
1868 END IF;
1869 END IF;
1870
1871 IF G_DEBUG='Y' THEN
1872 AHL_DEBUG_PUB.debug( 'dept name'||p_x_schedule_visit_rec.dept_name);
1873 END IF;
1874 --DEPT ID
1875 IF ( p_x_schedule_visit_rec.dept_name IS NULL OR
1876 p_x_schedule_visit_rec.dept_name = FND_API.G_MISS_CHAR) THEN
1877 -- anraj: if visit is in planning Department is not mandatory
1878 IF (l_schedule_visit_rec.status_code <> 'PLANNING') THEN
1879 Fnd_Message.SET_NAME('AHL','AHL_LTP_DEPT_REQUIRED');
1880 Fnd_Msg_Pub.ADD;
1881 END IF;
1882 END IF;
1883 --
1884
1885 -- Check for visit start date
1886 IF ( p_x_schedule_visit_rec.start_date IS NULL AND
1887 p_x_schedule_visit_rec.start_date = FND_API.G_MISS_DATE)
1888 THEN
1889 Fnd_Message.SET_NAME('AHL','AHL_LTP_START_DATE_INVALID');
1890 Fnd_Msg_Pub.ADD;
1891 RAISE Fnd_Api.G_EXC_ERROR;
1892 END IF;
1893 --
1894
1895 IF G_DEBUG='Y' THEN
1896 AHL_DEBUG_PUB.debug( 'space mean:'||p_x_schedule_visit_rec.space_category_mean);
1897 AHL_DEBUG_PUB.debug( 'space code:'||p_x_schedule_visit_rec.space_category_code);
1898 END IF;
1899
1900 --For Space Category
1901 IF p_x_schedule_visit_rec.space_category_mean IS NOT NULL AND
1902 p_x_schedule_visit_rec.space_category_mean <> Fnd_Api.G_MISS_CHAR
1903 THEN
1904 Check_lookup_name_Or_Id (
1905 p_lookup_type => 'AHL_LTP_SPACE_CATEGORY',
1906 p_lookup_code => NULL,
1907 p_meaning => p_x_schedule_visit_rec.space_category_mean,
1908 p_check_id_flag => 'Y',
1909 x_lookup_code => p_x_schedule_visit_rec.space_category_code,
1910 x_return_status => l_return_status);
1911
1912 IF NVL(l_return_status, 'X') <> 'S'
1913 THEN
1914 Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
1915 Fnd_Msg_Pub.ADD;
1916 RAISE Fnd_Api.G_EXC_ERROR;
1917 END IF;
1918 ELSE
1919 -- Id presents
1920 IF p_x_schedule_visit_rec.space_category_code IS NOT NULL AND
1921 p_x_schedule_visit_rec.space_category_code <> Fnd_Api.G_MISS_CHAR
1922 THEN
1923 p_x_schedule_visit_rec.space_category_code := p_x_schedule_visit_rec.space_category_code;
1924 END IF;
1925 END IF;
1926
1927 IF G_DEBUG='Y' THEN
1928 AHL_DEBUG_PUB.debug( 'mean:'||p_x_schedule_visit_rec.visit_type_mean);
1929 AHL_DEBUG_PUB.debug( 'visit type code:'||p_x_schedule_visit_rec.visit_type_code);
1930 END IF;
1931
1932 -- Visit type code
1933 IF p_x_schedule_visit_rec.visit_type_mean IS NOT NULL AND
1934 p_x_schedule_visit_rec.visit_type_mean <> Fnd_Api.G_MISS_CHAR
1935 THEN
1936 Check_lookup_name_Or_Id (
1937 p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
1938 p_lookup_code => NULL,
1939 p_meaning => p_x_schedule_visit_rec.visit_type_mean,
1940 p_check_id_flag => 'Y',
1941 x_lookup_code => p_x_schedule_visit_rec.visit_type_code,
1942 x_return_status => l_return_status);
1943
1944 IF NVL(l_return_status, 'X') <> 'S'
1945 THEN
1946 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_NOT_EXISTS');
1947 Fnd_Message.SET_TOKEN('VISIT',p_x_schedule_visit_rec.visit_type_mean);
1948 Fnd_Msg_Pub.ADD;
1949 END IF;
1950 ELSE
1951 -- Id presents
1952 IF p_x_schedule_visit_rec.visit_type_code IS NOT NULL AND
1953 p_x_schedule_visit_rec.visit_type_code <> Fnd_Api.G_MISS_CHAR
1954 THEN
1955 p_x_schedule_visit_rec.visit_type_code := p_x_schedule_visit_rec.visit_type_code;
1956 --
1957 --Commented by mpothuku on 02/25/04 as Visit type in not mandatory
1958 /*
1959 ELSIF (l_schedule_visit_rec.status_code <> 'PLANNING' )
1960 THEN
1961 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_REQUIRED');
1962 Fnd_Msg_Pub.ADD;
1963 RAISE Fnd_Api.G_EXC_ERROR;
1964 */
1965 END IF;
1966
1967 END IF;
1968 --
1969 IF p_x_schedule_visit_rec.object_version_number <> l_schedule_visit_rec.object_version_number
1970 THEN
1971 Fnd_Message.SET_NAME('AHL','AHL_LTP_INVALID_RECORD');
1972 Fnd_Msg_Pub.ADD;
1973 END IF;
1974
1975 -- Check for visit status
1976 -- anraj : Commented the following block as Impelmented/Partially Implemented visits can also be updated.
1977 /* IF (l_schedule_visit_rec.status_code <> 'PLANNING' )THEN
1978 Fnd_Message.SET_NAME('AHL','AHL_VISIT_NOT_PLANNED');
1979 Fnd_Msg_Pub.ADD;
1980 RAISE Fnd_Api.G_EXC_ERROR;
1981 END IF;
1982 */
1983
1984 IF G_DEBUG='Y' THEN
1985 AHL_DEBUG_PUB.debug( 'dept id'||p_x_schedule_visit_rec.dept_id);
1986 AHL_DEBUG_PUB.debug( 'visit type'||p_x_schedule_visit_rec.visit_type_code);
1987 END IF;
1988 --
1989 IF G_DEBUG='Y' THEN
1990 AHL_DEBUG_PUB.debug( 'schedule visits schedule_flag'||p_x_schedule_visit_rec.schedule_flag);
1991 END IF;
1992
1993 -- anraj: commented, issue number 144
1994 -- commented since space assigments are taken care of in the AHL_VWP_VISITS_PVT.Process_Visit
1995 /*
1996 IF p_x_schedule_visit_rec.schedule_flag <> 'Y' THEN
1997 -- Check for the visit has been assigned to different org and department
1998 IF ( p_x_schedule_visit_rec.org_id IS NOT NULL AND
1999 p_x_schedule_visit_rec.org_id <> FND_API.G_MISS_NUM)
2000 OR
2001 ( p_x_schedule_visit_rec.org_name IS NOT NULL AND
2002 p_x_schedule_visit_rec.org_name <> FND_API.G_MISS_CHAR)
2003 THEN
2004
2005 -- Check for Org has been changes
2006 OPEN visit_info_cur;
2007 FETCH visit_info_cur INTO l_organization_id,l_org_name,l_department_id,
2008 l_dept_name,l_visit_type_code;
2009 CLOSE visit_info_cur;
2010 --
2011 IF ( p_x_schedule_visit_rec.org_id <> l_organization_id OR
2012 p_x_schedule_visit_rec.org_name <> l_org_name OR
2013 p_x_schedule_visit_rec.dept_id <> l_department_id OR
2014 p_x_schedule_visit_rec.dept_name <> l_dept_name OR
2015 p_x_schedule_visit_rec.visit_type_code <> l_visit_type_code)
2016 THEN
2017
2018 OPEN c_space_assign_cur( l_schedule_visit_rec.visit_id);
2019 LOOP
2020 FETCH c_space_assign_cur INTO l_space_assign_rec;
2021 EXIT WHEN c_space_assign_cur%NOTFOUND;
2022 --
2023 DELETE FROM ahl_space_assignments
2024 WHERE space_assignment_id = l_space_assign_rec.space_assignment_id;
2025 --
2026 END LOOP;
2027 CLOSE c_space_assign_cur;
2028 --
2029 END IF; --dept condtion
2030 END IF; --org condition
2031 END IF; --Schedule flag
2032 */
2033 --Standard check to count messages
2034 l_msg_count := Fnd_Msg_Pub.count_msg;
2035
2036 IF l_msg_count > 0 THEN
2037 X_msg_count := l_msg_count;
2038 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2039 RAISE Fnd_Api.G_EXC_ERROR;
2040 END IF;
2041
2042 OPEN visit_det_cur;
2043 FETCH visit_det_cur INTO l_organization_id,l_start_date_time,l_visit_name;
2044 CLOSE visit_det_cur;
2045
2046 -- ORGANIZATION_ID
2047 IF p_x_schedule_visit_rec.org_id = FND_API.g_miss_num THEN
2048 p_x_schedule_visit_rec.org_id := NULL;
2049 END IF;
2050 -- DEPARTMENT_ID
2051 IF p_x_schedule_visit_rec.dept_id = FND_API.g_miss_num THEN
2052 p_x_schedule_visit_rec.dept_id := NULL;
2053 END IF;
2054 -- START_DATE_TIME
2055 IF p_x_schedule_visit_rec.start_date = FND_API.g_miss_date THEN
2056 p_x_schedule_visit_rec.start_date := NULL;
2057 END IF;
2058 -- PLANNED_DATE_TIME
2059 IF p_x_schedule_visit_rec.planned_end_date = FND_API.g_miss_date THEN
2060 p_x_schedule_visit_rec.planned_end_date := NULL;
2061 END IF;
2062 -- Space Categpry
2063 IF p_x_schedule_visit_rec.space_category_code = FND_API.g_miss_char THEN
2064 p_x_schedule_visit_rec.space_category_code := NULL;
2065 END IF;
2066 -- Visit type Code
2067 IF p_x_schedule_visit_rec.visit_type_code = FND_API.g_miss_char THEN
2068 p_x_schedule_visit_rec.visit_type_code := NULL;
2069 END IF;
2070 -- Planned End Hour
2071 IF p_x_schedule_visit_rec.planned_end_hour = FND_API.g_miss_num THEN
2072 p_x_schedule_visit_rec.planned_end_hour := NULL;
2073 END IF;
2074 -- Start Hour
2075 IF p_x_schedule_visit_rec.start_hour = FND_API.g_miss_num THEN
2076 p_x_schedule_visit_rec.start_hour := NULL;
2077 END IF;
2078
2079 --
2080 IF G_DEBUG='Y' THEN
2081 AHL_DEBUG_PUB.debug( 'start date'||TO_CHAR(p_x_schedule_visit_rec.start_date, 'DD-MM-YYYY ') ||to_char(p_x_schedule_visit_rec.start_hour) ||':00');
2082 AHL_DEBUG_PUB.debug( 'start hour'||p_x_schedule_visit_rec.start_hour);
2083 AHL_DEBUG_PUB.debug( 'plan end date'||p_x_schedule_visit_rec.planned_end_date);
2084 END IF;
2085
2086 --Check for visit scheduled or not
2087 -- anraj: commented, issue number 144
2088 /*
2089 OPEN c_visit_sched_cur(l_visit_id);
2090 FETCH c_visit_sched_cur INTO l_dummy;
2091 CLOSE c_visit_sched_cur;
2092 */
2093
2094 --Standard check to count messages
2095 l_msg_count := Fnd_Msg_Pub.count_msg;
2096
2097 IF l_msg_count > 0 THEN
2098 X_msg_count := l_msg_count;
2099 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2100 RAISE Fnd_Api.G_EXC_ERROR;
2101 END IF;
2102
2103 IF G_DEBUG='Y' THEN
2104 AHL_DEBUG_PUB.debug( 'before assign l_visit_id:'||l_visit_id);
2105 AHL_DEBUG_PUB.debug( 'before assign visit number:'||p_x_schedule_visit_rec.visit_number);
2106 AHL_DEBUG_PUB.debug( 'before assign org id:'||p_x_schedule_visit_rec.org_id);
2107 AHL_DEBUG_PUB.debug( 'before assign dept:'||p_x_schedule_visit_rec.dept_id);
2108 AHL_DEBUG_PUB.debug( 'before assign dept:'||p_x_schedule_visit_rec.dept_id);
2109 AHL_DEBUG_PUB.debug( 'before assign space_category_code:'||p_x_schedule_visit_rec.space_category_code);
2110 AHL_DEBUG_PUB.debug( 'before assign space_category_code:'||p_x_schedule_visit_rec.space_category_code);
2111 AHL_DEBUG_PUB.debug( 'before assign end date:'||p_x_schedule_visit_rec.planned_end_date);
2112 END IF;
2113
2114 l_Visit_tbl(i).VISIT_ID := l_visit_id;
2115 l_Visit_tbl(i).VISIT_NUMBER := p_x_schedule_visit_rec.visit_number;
2116 l_Visit_tbl(i).VISIT_NAME := l_visit_name;
2117 l_Visit_tbl(i).OBJECT_VERSION_NUMBER :=p_x_schedule_visit_rec.object_version_number;
2118 l_Visit_tbl(i).ORG_NAME := p_x_schedule_visit_rec.org_name;
2119 l_Visit_tbl(i).ORGANIZATION_ID := p_x_schedule_visit_rec.org_id;
2120 l_Visit_tbl(i).DEPARTMENT_ID := p_x_schedule_visit_rec.dept_id;
2121 l_Visit_tbl(i).DEPT_NAME := p_x_schedule_visit_rec.dept_name;
2122 l_Visit_tbl(i).SPACE_CATEGORY_CODE := p_x_schedule_visit_rec.space_category_code;
2123 l_Visit_tbl(i).SPACE_CATEGORY_NAME := p_x_schedule_visit_rec.space_category_mean;
2124 l_Visit_tbl(i).START_DATE := p_x_schedule_visit_rec.start_date;
2125 l_Visit_tbl(i).START_HOUR := to_char(to_number(p_x_schedule_visit_rec.start_hour));
2126 l_Visit_tbl(i).START_MIN := null;
2127 l_Visit_tbl(i).PLAN_END_DATE := p_x_schedule_visit_rec.planned_end_date;
2128 l_Visit_tbl(i).PLAN_END_HOUR := to_char(to_number(p_x_schedule_visit_rec.planned_end_hour));
2129 l_Visit_tbl(i).PLAN_END_MIN := null;
2130 l_Visit_tbl(i).VISIT_TYPE_CODE := p_x_schedule_visit_rec.visit_type_code;
2131 l_Visit_tbl(i).VISIT_TYPE_NAME := p_x_schedule_visit_rec.visit_type_mean;
2132 l_Visit_tbl(i).OPERATION_FLAG := 'U';
2133
2134 IF l_Visit_tbl.COUNT > 0 THEN
2135 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2136 fnd_log.string
2137 (
2138 fnd_log.level_statement,
2139 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2140 'Before Calling ahl Vwp Visits Pvt Process Visit Records : '|| l_visit_tbl.count
2141 );
2142 END IF;
2143
2144 AHL_VWP_VISITS_PVT.Process_Visit
2145 (
2146 p_api_version => p_api_version,
2147 p_init_msg_list => p_init_msg_list,
2148 p_commit => p_commit,
2149 p_validation_level => p_validation_level,
2150 p_module_type => p_module_type,
2151 p_x_Visit_tbl => l_visit_tbl,
2152 x_return_status => l_return_status,
2153 x_msg_count => l_msg_count,
2154 x_msg_data => l_msg_data
2155 );
2156 END IF;
2157
2158 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2159 fnd_log.string
2160 (
2161 fnd_log.level_statement,
2162 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2163 'Before Calling ahl Vwp Visits Pvt status : '|| l_return_status
2164 );
2165 END IF;
2166
2167 -- Check Error Message stack.
2168 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2169 l_msg_count := FND_MSG_PUB.count_msg;
2170 IF l_msg_count > 0 THEN
2171 RAISE FND_API.G_EXC_ERROR;
2172 END IF;
2173 END IF;
2174
2175
2176 -- anraj commented as material planning is handled in AHL_VWP_VISITS_PVT.Process_Visit
2177 -- issue number 144, LTP issues , CMRO Forum
2178 /*
2179 IF (p_x_schedule_visit_rec.org_id <> l_organization_id OR
2180 trunc(p_x_schedule_visit_rec.start_date) <> l_start_date_time OR
2181 l_dummy IS NOT NULL ) THEN
2182
2183 IF G_DEBUG='Y' THEN
2184 AHL_DEBUG_PUB.debug( 'before calling when Org Or Start date change AHL_LTP_REQST_MATRL_PVT.Create_Planned_Materials');
2185 AHL_DEBUG_PUB.debug( 'before calling Visit ID:'||l_visit_id);
2186 AHL_DEBUG_PUB.debug( 'before calling Start Date:'||p_x_schedule_visit_rec.start_date);
2187 AHL_DEBUG_PUB.debug( 'before calling Org ID:'||p_x_schedule_visit_rec.org_id);
2188 END IF;
2189
2190 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2191 fnd_log.string
2192 (
2193 fnd_log.level_statement,
2194 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2195 'Before Calling ahl ltp reqst matrl pvt Modify Visit Task Material for Visit Id : '|| l_visit_id
2196 );
2197 END IF;
2198 --
2199
2200
2201 AHL_LTP_REQST_MATRL_PVT.Modify_Visit_Task_Matrls
2202 ( p_api_version => l_api_version,
2203 p_init_msg_list => p_init_msg_list,
2204 p_commit => p_commit,
2205 p_validation_level => p_validation_level,
2206 p_visit_id => l_visit_id,
2207 p_start_time => p_x_schedule_visit_rec.start_date,
2208 p_org_id => p_x_schedule_visit_rec.org_id,
2209 x_return_status => l_return_status,
2210 x_msg_count => l_msg_count,
2211 x_msg_data => l_msg_data);
2212
2213 END IF;
2214 */
2215 --Standard check to count messages
2216 l_msg_count := Fnd_Msg_Pub.count_msg;
2217
2218 IF l_msg_count > 0 THEN
2219 X_msg_count := l_msg_count;
2220 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2221 RAISE Fnd_Api.G_EXC_ERROR;
2222 END IF;
2223
2224 ---------------------------End of Body---------------------------------------
2225
2226 --Standard check for commit
2227 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2228 COMMIT;
2229 END IF;
2230 -- Debug info
2231 IF G_DEBUG='Y' THEN
2232 Ahl_Debug_Pub.debug( 'End of private api Schedule Visit','+SPANT+');
2233 END IF;
2234 -- Check if API is called in debug mode. If yes, disable debug.
2235 IF G_DEBUG='Y' THEN
2236 Ahl_Debug_Pub.disable_debug;
2237 END IF;
2238 EXCEPTION
2239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2240 ROLLBACK TO schedule_visit;
2241 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2242 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2243 p_count => x_msg_count,
2244 p_data => x_msg_data);
2245 IF G_DEBUG='Y' THEN
2246
2247 AHL_DEBUG_PUB.log_app_messages (
2248 x_msg_count, x_msg_data, 'ERROR' );
2249 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Schedule Visit','+SPANT+');
2250 -- Check if API is called in debug mode. If yes, disable debug.
2251 AHL_DEBUG_PUB.disable_debug;
2252 END IF;
2253 WHEN FND_API.G_EXC_ERROR THEN
2254 ROLLBACK TO schedule_visit;
2255 X_return_status := FND_API.G_RET_STS_ERROR;
2256 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2257 p_count => x_msg_count,
2258 p_data => X_msg_data);
2259 IF G_DEBUG='Y' THEN
2260
2261 -- Debug info.
2262 AHL_DEBUG_PUB.log_app_messages (
2263 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2264 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Schedule visit','+SPANT+');
2265 -- Check if API is called in debug mode. If yes, disable debug.
2266 AHL_DEBUG_PUB.disable_debug;
2267 END IF;
2268 WHEN OTHERS THEN
2269 ROLLBACK TO schedule_visit;
2270 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2271 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2272 THEN
2273 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
2274 p_procedure_name => 'SCHEDULE_VISIT',
2275 p_error_text => SUBSTR(SQLERRM,1,240));
2276 END IF;
2277 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2278 p_count => x_msg_count,
2279 p_data => X_msg_data);
2280 IF G_DEBUG='Y' THEN
2281
2282 -- Debug info.
2283 AHL_DEBUG_PUB.log_app_messages (
2284 x_msg_count, x_msg_data, 'SQL ERROR' );
2285 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Schedule Visit','+SPANT+');
2286 -- Check if API is called in debug mode. If yes, disable debug.
2287 AHL_DEBUG_PUB.disable_debug;
2288
2289 END IF;
2290 END Schedule_Visit;
2291 --
2292 -- PROCEDURE
2293 -- Unschedule_Visit
2294 --
2295 -- PURPOSE
2296 -- Unschedule_Visit
2297 --
2298 -- PARAMETERS
2299 -- p_x_schedule_visit_rec : Record Representing Schedule_Visit_Rec
2300 --
2301 -- NOTES
2302 -- anraj: 09-FEB-2005
2303 -- i. Commented the UPDATE of ahl_schedule_materials
2304 -- ii. The code to remove space assignment has been commented because it is handled in AHL_VWP_VISITS_PVT.Process_Visit
2305 -- iii. Commented cursors c_space_assign_cur,c_visit_task_matrl_cur,c_sch_mat_cur
2306 PROCEDURE Unschedule_Visit (
2307 p_api_version IN NUMBER,
2308 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2309 p_commit IN VARCHAR2 := FND_API.g_false,
2310 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2311 p_module_type IN VARCHAR2 := 'JSP',
2312 p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2313 x_return_status OUT NOCOPY VARCHAR2,
2314 x_msg_count OUT NOCOPY NUMBER,
2315 x_msg_data OUT NOCOPY VARCHAR2
2316 )
2317 IS
2318
2319 CURSOR c_schedule_visit_cur (c_visit_id IN NUMBER)
2320 IS
2321 SELECT visit_id, status_code,
2322 object_version_number
2323 FROM AHL_VISITS_B
2324 WHERE VISIT_ID = c_visit_id;
2325 --
2326 /*
2327 CURSOR c_space_assign_cur (c_visit_id IN NUMBER)
2328 IS
2329 SELECT space_assignment_id,
2330 object_version_number
2331 FROM AHL_SPACE_ASSIGNMENTS
2332 WHERE VISIT_ID = c_visit_id;
2333 */
2334 --
2335 /*
2336 CURSOR c_sch_mat_cur (c_visit_id IN NUMBER)
2337 IS
2338 SELECT scheduled_material_id,
2339 object_version_number
2340 FROM ahl_schedule_materials
2341 WHERE visit_id = c_visit_id;
2342 */
2343 --
2344 /*
2345 CURSOR c_visit_task_matrl_cur(c_sch_mat_id IN NUMBER)
2346 IS
2347 SELECT scheduled_date,scheduled_quantity
2348 FROM ahl_visit_task_matrl_v
2349 WHERE schedule_material_id = c_sch_mat_id;
2350 */
2351 l_api_name CONSTANT VARCHAR2(30) := 'UNSCHEDULE_VISIT';
2352 l_api_version CONSTANT NUMBER := 1.0;
2353 l_msg_count NUMBER;
2354 l_return_status VARCHAR2(1);
2355 l_msg_data VARCHAR2(2000);
2356 l_dummy NUMBER;
2357 l_rowid VARCHAR2(30);
2358 l_organization_id NUMBER;
2359 l_department_id NUMBER;
2360 l_visit_id NUMBER;
2361 l_object_version_number NUMBER;
2362 l_start_date_time DATE;
2363 l_space_assignment_id NUMBER;
2364 l_space_version_number NUMBER;
2365 l_visit_status_code VARCHAR2(30);
2366 l_meaning VARCHAR2(80);
2367 --
2368 --l_schedule_material_id NUMBER;
2369 --l_scheduled_date DATE;
2370 --l_scheduled_quantity NUMBER;
2371 --
2372 l_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
2373 i NUMBER := 0;
2374 l_visit_name VARCHAR2(80);
2375 BEGIN
2376 --------------------Initialize ----------------------------------
2377 -- Standard Start of API savepoint
2378 SAVEPOINT unschedule_visit;
2379 -- Check if API is called in debug mode. If yes, enable debug.
2380 IF G_DEBUG='Y' THEN
2381 AHL_DEBUG_PUB.enable_debug;
2382 END IF;
2383 -- Debug info.
2384 IF G_DEBUG='Y' THEN
2385 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Unschedule Visit','+SPANT+');
2386 END IF;
2387 -- Standard call to check for call compatibility.
2388 IF FND_API.to_boolean(p_init_msg_list)
2389 THEN
2390 FND_MSG_PUB.initialize;
2391 END IF;
2392 -- Initialize API return status to success
2393 x_return_status := FND_API.G_RET_STS_SUCCESS;
2394 -- Initialize message list if p_init_msg_list is set to TRUE.
2395 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2396 p_api_version,
2397 l_api_name,G_PKG_NAME)
2398 THEN
2399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2400 END IF;
2401 ---------------------start API Body------------------------------------
2402 -- Convert Visit Number to visit id
2403 IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
2404 p_x_schedule_visit_rec.visit_number <> FND_API.G_MISS_NUM ) OR
2405 (p_x_schedule_visit_rec.visit_id IS NOT NULL AND
2406 p_x_schedule_visit_rec.visit_id <> FND_API.G_MISS_NUM) THEN
2407
2408 Check_visit_number_Or_Id
2409 (p_visit_id => p_x_schedule_visit_rec.visit_id,
2410 p_visit_number => p_x_schedule_visit_rec.visit_number,
2411 x_visit_id => l_visit_id,
2412 x_return_status => l_return_status,
2413 x_error_msg_code => l_msg_data);
2414
2415 IF NVL(l_return_status,'x') <> 'S'
2416 THEN
2417 Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_NOT_EXISTS');
2418 Fnd_Message.SET_TOKEN('VISITID',p_x_schedule_visit_rec.visit_number);
2419 Fnd_Msg_Pub.ADD;
2420 END IF;
2421 END IF;
2422 --Assign the returned value
2423 p_x_schedule_visit_rec.visit_id := l_visit_id;
2424 --Get the existing Record
2425 OPEN c_schedule_visit_cur(l_visit_id);
2426 FETCH c_schedule_visit_cur INTO l_visit_id,l_visit_status_code,
2427 l_object_version_number;
2428 CLOSE c_schedule_visit_cur;
2429 --
2430 IF p_x_schedule_visit_rec.object_version_number <> l_object_version_number
2431 THEN
2432 Fnd_Message.SET_NAME('AHL','AHL_LTP_INAVLID_RECORD');
2433 Fnd_Msg_Pub.ADD;
2434 RAISE Fnd_Api.G_EXC_ERROR;
2435 END IF;
2436 -- Check for visit status
2437 IF (l_visit_status_code <> 'PLANNING' )THEN
2438 Fnd_Message.SET_NAME('AHL','AHL_VISIT_NOT_PLANNED');
2439 Fnd_Msg_Pub.ADD;
2440 RAISE Fnd_Api.G_EXC_ERROR;
2441 END IF;
2442 --
2443 --Check for material scheduling
2444 -- anraj commented because material scheduling is handled in AHL_VWP_VISITS_PVT.Process_Visit
2445 -- issue number 144, LTP issues , CMRO Forum
2446 /*
2447 OPEN c_sch_mat_cur(l_visit_id);
2448 LOOP
2449 FETCH c_sch_mat_cur INTO l_schedule_material_id,
2450 l_object_version_number;
2451 EXIT WHEN c_sch_mat_cur%NOTFOUND;
2452
2453 IF l_schedule_material_id IS NOT NULL THEN
2454 --Check for Item scheduled
2455 OPEN c_visit_task_matrl_cur(l_schedule_material_id);
2456 FETCH c_visit_task_matrl_cur INTO l_scheduled_date,l_scheduled_quantity;
2457 IF l_scheduled_date IS NOT NULL THEN
2458 Fnd_Message.SET_NAME('AHL','AHL_LTP_MRP_SCHEDUl_ITEM');
2459 Fnd_Msg_Pub.ADD;
2460 CLOSE c_visit_task_matrl_cur;
2461 RAISE Fnd_Api.G_EXC_ERROR;
2462 ELSE
2463 UPDATE ahl_schedule_materials
2464 SET requested_quantity = 0,
2465 object_version_number = l_object_version_number + 1,
2466 last_update_date = SYSDATE,
2467 last_updated_by = Fnd_Global.user_id,
2468 last_update_login = Fnd_Global.login_id
2469 WHERE scheduled_material_id = l_schedule_material_id;
2470 --
2471 END IF; --Scheduled date
2472 CLOSE c_visit_task_matrl_cur;
2473 --
2474 END IF;-- Scheduled mat id
2475 END LOOP;
2476 CLOSE c_sch_mat_cur;
2477 */
2478 --
2479 --Check for Record in space assignments
2480 -- anraj: commented, issue number 144
2481 -- commented since space assigments are taken care of in the AHL_VWP_VISITS_PVT.Process_Visit
2482 /*
2483 IF l_visit_id IS NOT NULL THEN
2484 OPEN c_space_assign_cur(l_visit_id);
2485 LOOP
2486 FETCH c_space_assign_cur INTO l_space_assignment_id,l_space_version_number;
2487 EXIT WHEN c_space_assign_cur%NOTFOUND;
2488 -- Remove space assingment record
2489 DELETE FROM AHL_SPACE_ASSIGNMENTS
2490 WHERE space_assignment_id = l_space_assignment_id;
2491 --
2492 END LOOP;
2493 CLOSE c_space_assign_cur;
2494 END IF;
2495 */
2496 --Update visits table
2497 /* changes made by mpothuku on 12/20/04 for calling the VWP API to make the visit update instead of directly
2498 updating the visit. */
2499 -- Changes by mpothuku start
2500 /*
2501 UPDATE AHL_VISITS_B
2502 SET organization_id = NULL,
2503 department_id = NULL,
2504 start_date_time = NULL,
2505 close_date_time = NULL,
2506 any_task_chg_flag = 'Y',
2507 object_version_number = l_object_version_number + 1,
2508 last_update_date = SYSDATE,
2509 last_updated_by = Fnd_Global.user_id,
2510 last_update_login = Fnd_Global.login_id
2511
2512 WHERE visit_id = l_visit_id;
2513 */
2514 -- Visit Name Mandatory for Update
2515 SELECT visit_name INTO l_visit_name
2516 FROM AHL_VISITS_VL WHERE VISIT_ID = l_visit_id;
2517
2518 l_visit_tbl(i).VISIT_NUMBER := p_x_schedule_visit_rec.visit_number;
2519 l_visit_tbl(i).VISIT_NAME := l_visit_name;
2520 l_visit_tbl(i).organization_id := NULL;
2521 l_visit_tbl(i).department_id := NULL;
2522 l_visit_tbl(i).start_date := NULL;
2523 l_visit_tbl(i).start_hour := NULL;
2524 l_visit_tbl(i).START_MIN := NULL;
2525 l_visit_tbl(i).plan_end_date := NULL;
2526 l_visit_tbl(i).plan_end_hour := NULL;
2527 l_visit_tbl(i).plan_end_min := NULL;
2528 l_visit_tbl(i).visit_id := l_visit_id;
2529 l_visit_tbl(i).object_version_number := p_x_schedule_visit_rec.object_version_number;
2530 l_visit_tbl(i).operation_flag := 'U';
2531
2532 IF l_Visit_tbl.COUNT > 0 THEN
2533 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2534 fnd_log.string
2535 (
2536 fnd_log.level_statement,
2537 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2538 'Before Calling ahl Vwp Visits Pvt Process Visit Records : '|| l_visit_tbl.count
2539 );
2540
2541 END IF;
2542
2543 AHL_VWP_VISITS_PVT.Process_Visit
2544 (
2545 p_api_version => p_api_version,
2546 p_init_msg_list => p_init_msg_list,
2547 p_commit => p_commit,
2548 p_validation_level => p_validation_level,
2549 p_module_type => p_module_type,
2550 p_x_Visit_tbl => l_visit_tbl,
2551 x_return_status => l_return_status,
2552 x_msg_count => l_msg_count,
2553 x_msg_data => l_msg_data
2554 );
2555 END IF;
2556
2557 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2558 fnd_log.string
2559 (
2560 fnd_log.level_statement,
2561 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2562 'After Calling ahl Vwp Visits Pvt status : '|| l_return_status
2563 );
2564
2565 END IF;
2566
2567 -- Check Error Message stack.
2568 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2569 l_msg_count := FND_MSG_PUB.count_msg;
2570 IF l_msg_count > 0 THEN
2571 RAISE FND_API.G_EXC_ERROR;
2572 END IF;
2573 END IF;
2574
2575 -- Changes by mpothuku End
2576
2577 ---------------------------End of Body---------------------------------------
2578 --Standard check to count messages
2579 l_msg_count := Fnd_Msg_Pub.count_msg;
2580
2581 IF l_msg_count > 0 THEN
2582 X_msg_count := l_msg_count;
2583 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2584 RAISE Fnd_Api.G_EXC_ERROR;
2585 END IF;
2586
2587 --Standard check for commit
2588 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2589 COMMIT;
2590 END IF;
2591 -- Debug info
2592 IF G_DEBUG='Y' THEN
2593 Ahl_Debug_Pub.debug( 'End of private api Unschedule Visit','+SPANT+');
2594 -- Check if API is called in debug mode. If yes, disable debug.
2595 Ahl_Debug_Pub.disable_debug;
2596 END IF;
2597 EXCEPTION
2598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2599 ROLLBACK TO unschedule_visit;
2600 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2601 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2602 p_count => x_msg_count,
2603 p_data => x_msg_data);
2604 IF G_DEBUG='Y' THEN
2605
2606 AHL_DEBUG_PUB.log_app_messages (
2607 x_msg_count, x_msg_data, 'ERROR' );
2608 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Unschedule Visit','+SPANT+');
2609 -- Check if API is called in debug mode. If yes, disable debug.
2610 AHL_DEBUG_PUB.disable_debug;
2611 END IF;
2612 WHEN FND_API.G_EXC_ERROR THEN
2613 ROLLBACK TO unschedule_visit;
2614 X_return_status := FND_API.G_RET_STS_ERROR;
2615 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2616 p_count => x_msg_count,
2617 p_data => X_msg_data);
2618 IF G_DEBUG='Y' THEN
2619
2620 -- Debug info.
2621 AHL_DEBUG_PUB.log_app_messages (
2622 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2623 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Unschedule visit','+SPANT+');
2624 -- Check if API is called in debug mode. If yes, disable debug.
2625 AHL_DEBUG_PUB.disable_debug;
2626 END IF;
2627 WHEN OTHERS THEN
2628 ROLLBACK TO unschedule_visit;
2629 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2631 THEN
2632 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
2633 p_procedure_name => 'UNSCHEDULE_VISIT',
2634 p_error_text => SUBSTR(SQLERRM,1,240));
2635 END IF;
2636 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2637 p_count => x_msg_count,
2638 p_data => X_msg_data);
2639 IF G_DEBUG='Y' THEN
2640
2641 -- Debug info.
2642 AHL_DEBUG_PUB.log_app_messages (
2643 x_msg_count, x_msg_data, 'SQL ERROR' );
2644 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Unschedule Visit','+SPANT+');
2645 -- Check if API is called in debug mode. If yes, disable debug.
2646 AHL_DEBUG_PUB.disable_debug;
2647 END IF;
2648 END Unschedule_Visit;
2649
2650 END AHL_LTP_SPACE_ASSIGN_PVT;