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