[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_SIMUL_PLAN_PVT
Source
1 PACKAGE BODY AHL_LTP_SIMUL_PLAN_PVT AS
2 /* $Header: AHLVSPNB.pls 120.5 2008/02/14 19:25:29 jaramana ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_SIMUL_PLAN_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 --
7 -----------------------------------------------------------
8 -- PACKAGE
9 -- AHL_LTP_SIMUL_PLAN_PVT
10 --
11 -- PURPOSE
12 -- This package is a Private API for managing Simulation plans information in
13 -- Advanced Services Online. It contains specification for pl/sql records and tables
14 --
15 -- AHL_SIMULATION_PLANS_VL:
16 -- Create_Simulation_plan (see below for specification)
17 -- Update_Simulation_plan (see below for specification)
18 -- Delete_Simulation_plan (see below for specification)
19 -- Validate_Simulation_plan (see below for specification)
20 --
21 --
22 -- NOTES
23 --
24 --
25 -- HISTORY
26 -- 23-Apr-2002 ssurapan Created.
27 --------------------------------------------------------------------
28 -- PROCEDURE
29 -- CHECK_PLAN_NAME_OR_ID
30 --
31 -- PURPOSE
32 -- Converts Plan Name to ID or Vice versa
33 --
34 -- PARAMETERS
35 --
36 -- NOTES
37 --------------------------------------------------------------------
38 PROCEDURE Check_plan_name_Or_Id
39 (p_simulation_plan_id IN NUMBER,
40 p_plan_name IN VARCHAR2,
41 x_plan_id OUT NOCOPY NUMBER,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_error_msg_code OUT NOCOPY VARCHAR2
44 )
45 IS
46 BEGIN
47 IF (p_simulation_plan_id IS NOT NULL)
48 THEN
49 SELECT simulation_plan_id
50 INTO x_plan_id
51 FROM AHL_SIMULATION_PLANS_VL
52 WHERE simulation_plan_id = p_simulation_plan_id;
53 ELSE
54 SELECT simulation_plan_id
55 INTO x_plan_id
56 FROM AHL_SIMULATION_PLANS_VL
57 WHERE SIMULATION_PLAN_NAME = p_plan_name;
58 END IF;
59 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
63 x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
64 WHEN TOO_MANY_ROWS THEN
65 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
66 x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
67 WHEN OTHERS THEN
68 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
69 RAISE;
70 END Check_plan_name_Or_Id;
71
72 --------------------------------------------------------------------
73 -- FUNCTION
74 -- Get_Visit_Task_Number
75 --
76 -- PURPOSE
77 -- To retrieve visit task's task number with maximum plus one criteria
78 --------------------------------------------------------------------
79
80 FUNCTION Get_Visit_Task_Number(p_visit_id IN NUMBER,p_task_number IN NUMBER)
81 RETURN NUMBER
82 IS
83 -- To find out the maximum task number value in the visit
84 CURSOR c_task_number IS
85 SELECT visit_task_number
86 FROM Ahl_Visit_Tasks_B
87 WHERE Visit_Id = p_visit_id
88 and visit_task_number = p_task_number;
89
90 CURSOR gen_task_number IS
91 SELECT MAX(visit_task_number)
92 FROM Ahl_Visit_Tasks_B
93 WHERE Visit_Id = p_visit_id;
94
95 x_Visit_Task_Number NUMBER;
96 BEGIN
97 -- Check for Visit Number
98 OPEN c_Task_Number;
99 FETCH c_Task_Number INTO x_Visit_Task_Number;
100 CLOSE c_Task_Number;
101 IF x_Visit_Task_Number IS NOT NULL THEN
102 OPEN gen_task_number;
103 FETCH gen_task_number INTO x_Visit_Task_Number;
104 CLOSE gen_task_number;
105 x_Visit_Task_Number := x_Visit_Task_Number + 1;
106 ELSE
107 x_Visit_Task_Number := p_task_number;
108 END IF;
109
110 RETURN x_Visit_Task_Number;
111 END Get_Visit_Task_Number;
112
113 ---------------------------------------------------------------------
114 -- PROCEDURE
115 -- Complete_Simulation_Rec
116 --
117 ---------------------------------------------------------------------
118 PROCEDURE Complete_Simulation_Rec (
119 p_simulation_rec IN Simulation_plan_rec,
120 x_simulation_rec OUT NOCOPY Simulation_plan_rec
121 )
122 IS
123 CURSOR c_simulation_rec
124 IS
125 SELECT ROW_ID,
126 SIMULATION_PLAN_ID,
127 SIMULATION_PLAN_NAME,
128 PRIMARY_PLAN_FLAG,
129 DESCRIPTION,
130 OBJECT_VERSION_NUMBER,
131 ATTRIBUTE_CATEGORY,
132 ATTRIBUTE1,
133 ATTRIBUTE2,
134 ATTRIBUTE3,
135 ATTRIBUTE4,
136 ATTRIBUTE5,
137 ATTRIBUTE6,
138 ATTRIBUTE7,
139 ATTRIBUTE8,
140 ATTRIBUTE9,
141 ATTRIBUTE10,
142 ATTRIBUTE11,
143 ATTRIBUTE12,
144 ATTRIBUTE13,
145 ATTRIBUTE14,
146 ATTRIBUTE15
147 FROM ahl_simulation_plans_vl
148 WHERE simulation_plan_id = p_simulation_rec.simulation_plan_id;
149 --
150 -- This is the only exception for using %ROWTYPE.
151 l_simulation_rec c_simulation_rec%ROWTYPE;
152 BEGIN
153 x_simulation_rec := p_simulation_rec;
154 OPEN c_simulation_rec;
155 FETCH c_simulation_rec INTO l_simulation_rec;
156 IF c_simulation_rec%NOTFOUND THEN
157 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
158 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
159 FND_MSG_PUB.add;
160 RAISE Fnd_Api.G_EXC_ERROR;
161 END IF;
162 END IF;
163 CLOSE c_simulation_rec;
164 --Check for object version number
165 IF (l_simulation_rec.object_version_number <> p_simulation_rec.object_version_number)
166 THEN
167 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
168 Fnd_Msg_Pub.ADD;
169 RAISE Fnd_Api.G_EXC_ERROR;
170 END IF;
171
172 IF G_DEBUG='Y' THEN
173 AHL_DEBUG_PUB.debug( 'inside complete name 1:'||l_simulation_rec.simulation_plan_name);
174 END IF;
175
176 -- SIMULATION_PLAN_NAME
177 IF p_simulation_rec.simulation_plan_name <> FND_API.g_miss_char THEN
178 x_simulation_rec.simulation_plan_name := p_simulation_rec.simulation_plan_name;
179 ELSE
180 x_simulation_rec.simulation_plan_name := l_simulation_rec.simulation_plan_name;
181 END IF;
182 -- DESCRIPTION
183 IF p_simulation_rec.description <> FND_API.g_miss_char THEN
184 x_simulation_rec.description := p_simulation_rec.description;
185 ELSE
186 x_simulation_rec.description := l_simulation_rec.description;
187 END IF;
188 -- ATTRIBUTE CATEGORY
189 IF p_simulation_rec.attribute_category <> FND_API.g_miss_char THEN
190 x_simulation_rec.attribute_category := p_simulation_rec.attribute_category;
191 ELSE
192 x_simulation_rec.attribute_category := l_simulation_rec.attribute_category;
193 END IF;
194 -- ATTRIBUTE 1
195 IF p_simulation_rec.attribute1 <> FND_API.g_miss_char THEN
196 x_simulation_rec.attribute1 := p_simulation_rec.attribute1;
197 ELSE
198 x_simulation_rec.attribute1 := l_simulation_rec.attribute1;
199 END IF;
200 -- ATTRIBUTE 2
201 IF p_simulation_rec.attribute2 <> FND_API.g_miss_char THEN
202 x_simulation_rec.attribute2 := p_simulation_rec.attribute2;
203 ELSE
204 x_simulation_rec.attribute2 := l_simulation_rec.attribute2;
205 END IF;
206 -- ATTRIBUTE 3
207 IF p_simulation_rec.attribute3 <> FND_API.g_miss_char THEN
208 x_simulation_rec.attribute3 := p_simulation_rec.attribute3;
209 ELSE
210 x_simulation_rec.attribute3 := l_simulation_rec.attribute3;
211 END IF;
212 -- ATTRIBUTE 4
213 IF p_simulation_rec.attribute4 <> FND_API.g_miss_char THEN
214 x_simulation_rec.attribute4 := p_simulation_rec.attribute4;
215 ELSE
216 x_simulation_rec.attribute4 := l_simulation_rec.attribute4;
217 END IF;
218 -- ATTRIBUTE 5
219 IF p_simulation_rec.attribute5 <> FND_API.g_miss_char THEN
220 x_simulation_rec.attribute5 := p_simulation_rec.attribute5;
221 ELSE
222 x_simulation_rec.attribute5 := l_simulation_rec.attribute5;
223 END IF;
224 -- ATTRIBUTE 6
225 IF p_simulation_rec.attribute6 <> FND_API.g_miss_char THEN
226 x_simulation_rec.attribute6 := p_simulation_rec.attribute6;
227 ELSE
228 x_simulation_rec.attribute6 := l_simulation_rec.attribute6;
229 END IF;
230 -- ATTRIBUTE 7
231 IF p_simulation_rec.attribute7 <> FND_API.g_miss_char THEN
232 x_simulation_rec.attribute7 := p_simulation_rec.attribute7;
233 ELSE
234 x_simulation_rec.attribute7 := l_simulation_rec.attribute7;
235 END IF;
236 -- ATTRIBUTE 8
237 IF p_simulation_rec.attribute8 <> FND_API.g_miss_char THEN
238 x_simulation_rec.attribute8 := p_simulation_rec.attribute8;
239 ELSE
240 x_simulation_rec.attribute8 := l_simulation_rec.attribute8;
241 END IF;
242 -- ATTRIBUTE 9
243 IF p_simulation_rec.attribute9 <> FND_API.g_miss_char THEN
244 x_simulation_rec.attribute9 := p_simulation_rec.attribute9;
245 ELSE
246 x_simulation_rec.attribute9 := l_simulation_rec.attribute9;
247 END IF;
248 -- ATTRIBUTE 10
249 IF p_simulation_rec.attribute10 <> FND_API.g_miss_char THEN
250 x_simulation_rec.attribute10 := p_simulation_rec.attribute10;
251 ELSE
252 x_simulation_rec.attribute10 := l_simulation_rec.attribute10;
253 END IF;
254 -- ATTRIBUTE 11
255 IF p_simulation_rec.attribute11 <> FND_API.g_miss_char THEN
256 x_simulation_rec.attribute11 := p_simulation_rec.attribute11;
257 ELSE
258 x_simulation_rec.attribute11 := l_simulation_rec.attribute11;
259 END IF;
260 -- ATTRIBUTE 12
261 IF p_simulation_rec.attribute12 <> FND_API.g_miss_char THEN
262 x_simulation_rec.attribute12 := p_simulation_rec.attribute12;
263 ELSE
264 x_simulation_rec.attribute12 := l_simulation_rec.attribute12;
265 END IF;
266 -- ATTRIBUTE 13
267 IF p_simulation_rec.attribute13 <> FND_API.g_miss_char THEN
268 x_simulation_rec.attribute13 := p_simulation_rec.attribute13;
269 ELSE
270 x_simulation_rec.attribute13 := l_simulation_rec.attribute13;
271 END IF;
272 -- ATTRIBUTE 14
273 IF p_simulation_rec.attribute14 <> FND_API.g_miss_char THEN
274 x_simulation_rec.attribute14 := p_simulation_rec.attribute14;
275 ELSE
276 x_simulation_rec.attribute14 := l_simulation_rec.attribute14;
277 END IF;
278 -- ATTRIBUTE 15
279 IF p_simulation_rec.attribute15 <> FND_API.g_miss_char THEN
280 x_simulation_rec.attribute15 := p_simulation_rec.attribute15;
281 ELSE
282 x_simulation_rec.attribute15 := l_simulation_rec.attribute15;
283 END IF;
284
285 END Complete_Simulation_Rec;
286
287 ---------------------------------------------------------------------
288 -- PROCEDURE
289 -- Assign_Simulation_Rec
290 --
291 ---------------------------------------------------------------------
292 PROCEDURE Assign_Simulation_Rec (
293 p_simulation_rec IN AHL_LTP_SIMUL_PLAN_PUB.Simulation_plan_rec,
294 x_simulation_rec OUT NOCOPY Simulation_plan_rec
295 )
296 IS
297
298 BEGIN
299 x_simulation_rec.simulation_plan_id := p_simulation_rec.plan_id;
300 x_simulation_rec.primary_plan_flag := p_simulation_rec.primary_plan_flag;
301 x_simulation_rec.simulation_plan_name := p_simulation_rec.plan_name;
302 x_simulation_rec.description := p_simulation_rec.description;
303 x_simulation_rec.object_version_number := p_simulation_rec.object_version_number;
304 x_simulation_rec.attribute_category := p_simulation_rec.attribute_category;
305 x_simulation_rec.attribute1 := p_simulation_rec.attribute1;
306 x_simulation_rec.attribute2 := p_simulation_rec.attribute2;
307 x_simulation_rec.attribute3 := p_simulation_rec.attribute3;
308 x_simulation_rec.attribute4 := p_simulation_rec.attribute4;
309 x_simulation_rec.attribute5 := p_simulation_rec.attribute5;
310 x_simulation_rec.attribute6 := p_simulation_rec.attribute6;
311 x_simulation_rec.attribute7 := p_simulation_rec.attribute7;
312 x_simulation_rec.attribute8 := p_simulation_rec.attribute8;
313 x_simulation_rec.attribute9 := p_simulation_rec.attribute9;
314 x_simulation_rec.attribute10 := p_simulation_rec.attribute10;
315 x_simulation_rec.attribute11 := p_simulation_rec.attribute11;
316 x_simulation_rec.attribute12 := p_simulation_rec.attribute12;
317 x_simulation_rec.attribute13 := p_simulation_rec.attribute13;
318 x_simulation_rec.attribute14 := p_simulation_rec.attribute14;
319 x_simulation_rec.attribute15 := p_simulation_rec.attribute15;
320
321 END Assign_Simulation_Rec;
322
323 ------------------------------------------------------------------------------
324 --
325 -- NAME
326 -- Validate_Simulation_plan_Items
327 --
328 -- PURPOSE
329 -- This procedure is to validate Simulation plan attributes
330 -- End of Comments
331 -------------------------------------------------------------------------------
332 PROCEDURE Validate_Simulation_plan_Items
333 ( p_simulation_plan_rec IN simulation_plan_rec,
334 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
335 x_return_status OUT NOCOPY VARCHAR2
336 ) IS
337 l_table_name VARCHAR2(30);
338 l_pk_name VARCHAR2(30);
339 l_pk_value VARCHAR2(30);
340 l_where_clause VARCHAR2(2000);
341 l_dummy NUMBER;
342 --
343 CURSOR check_plan_name_cur (c_plan_name IN VARCHAR2)
344 IS
345 SELECT 1 FROM
346 AHL_SIMULATION_PLANS_VL
347 WHERE simulation_plan_name = c_plan_name
348 AND primary_plan_flag = 'N';
349
350
351 BEGIN
352 -- Initialize API/Procedure return status to success
353 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
354 -- Check required parameters
355 -- PLAN_NAME
356 IF (p_simulation_plan_rec.SIMULATION_PLAN_NAME IS NULL
357 OR
358 p_simulation_plan_rec.SIMULATION_PLAN_NAME = FND_API.G_MISS_CHAR)
359 THEN
360
361 -- missing required fields
362 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
363 THEN
364 Fnd_Message.set_name('AHL', 'AHL_LTP_PLAN_NAME_NOT_EXIST');
365 Fnd_Msg_Pub.ADD;
366 END IF;
367 x_return_status := Fnd_Api.G_RET_STS_ERROR;
368 END IF;
369 -- Validate uniqueness
370 OPEN check_plan_name_cur(p_simulation_plan_rec.simulation_plan_name);
371 FETCH check_plan_name_cur INTO l_dummy;
372 IF check_plan_name_cur%FOUND THEN
373 Fnd_Message.set_name('AHL', 'AHL_LTP_SIMUL_DUPLE_NAME');
374 Fnd_Msg_Pub.ADD;
375 END IF;
376 x_return_status := Fnd_Api.g_ret_sts_error;
377 CLOSE check_plan_name_cur;
378 --Check for primary plan
379 IF p_simulation_plan_rec.primary_plan_flag = 'Y' THEN
380 Fnd_Message.set_name('AHL', 'AHL_LTP_SIMUL_DUPLE_NAME');
381 Fnd_Msg_Pub.ADD;
382 x_return_status := Fnd_Api.g_ret_sts_error;
383 END IF;
384
385 END Validate_Simulation_plan_Items;
386 ----------------------------------------------------------------------------
387 -- NAME
388 -- Validate_Simulation_plan_Record
389 --
390 -- PURPOSE
391 -- This procedure is to validate Simulation plans record
392 --
393 -- NOTES
394 -- End of Comments
395 -----------------------------------------------------------------------------
396 PROCEDURE Validate_Simulation_plan_Rec(
397 p_simulation_plan_rec IN simulation_plan_rec,
398 x_return_status OUT NOCOPY VARCHAR2
399 ) IS
400 -- Status Local Variables
401 l_return_status VARCHAR2(1);
402 BEGIN
403 -- Initialize API return status to success
404 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
405 --
406 NULL;
407 --
408 END Validate_Simulation_plan_Rec;
409 --------------------------------------------------------------------
410 -- PROCEDURE
411 -- Validate_Simulation_plan
412 --
413 -- PURPOSE
414 -- Validate simulation plan attributes
415 --
416 -- PARAMETERS
417 --
418 -- NOTES
419 --
420 --------------------------------------------------------------------
421 PROCEDURE Validate_Simulation_plan
422 ( p_api_version IN NUMBER,
423 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
424 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
425 p_simulation_plan_rec IN simulation_plan_rec,
426 x_return_status OUT NOCOPY VARCHAR2,
427 x_msg_count OUT NOCOPY NUMBER,
428 x_msg_data OUT NOCOPY VARCHAR2
429 )
430 IS
431 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Simulation_Plan';
432 l_api_version CONSTANT NUMBER := 1.0;
433 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
434 l_return_status VARCHAR2(1);
435 l_simulation_plan_rec simulation_plan_rec;
436 BEGIN
437 -- Standard call to check for call compatibility.
438 IF NOT Fnd_Api.Compatible_API_Call ( l_api_version,
439 p_api_version,
440 l_api_name,
441 G_PKG_NAME)
442 THEN
443 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
444 END IF;
445 -- Initialize message list if p_init_msg_list is set to TRUE.
446 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
447 Fnd_Msg_Pub.initialize;
448 END IF;
449 -- Initialize API return status to success
450 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
451 --
452 -- API body
453 --
454 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item
455 THEN
456 Validate_Simulation_plan_Items
457 ( p_simulation_plan_rec => p_simulation_plan_rec,
458 p_validation_mode => Jtf_Plsql_Api.g_create,
459 x_return_status => l_return_status
460 );
461 -- If any errors happen abort API.
462 IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
463 THEN
464 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
465 ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
466 THEN
467 RAISE Fnd_Api.G_EXC_ERROR;
468 END IF;
469 END IF;
470 -- Perform cross attribute validation and missing attribute checks. Record
471 -- level validation.
472 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record
473 THEN
474 Validate_Simulation_plan_Rec(
475 p_simulation_plan_rec => p_simulation_plan_rec,
476 x_return_status => l_return_status
477 );
478 IF l_return_status = Fnd_Api.G_RET_STS_ERROR
479 THEN
480 RAISE Fnd_Api.G_EXC_ERROR;
481 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
482 THEN
483 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485 END IF;
486 --
487 -- END of API body.
488 --
489 -------------------- finish --------------------------
490 Fnd_Msg_Pub.count_and_get(
491 p_encoded => Fnd_Api.g_false,
492 p_count => x_msg_count,
493 p_data => x_msg_data);
494 EXCEPTION
495 WHEN Fnd_Api.G_EXC_ERROR THEN
496 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
497 Fnd_Msg_Pub.Count_AND_Get
498 ( p_count => x_msg_count,
499 p_data => x_msg_data,
500 p_encoded => Fnd_Api.G_FALSE
501 );
502 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
503 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
504 Fnd_Msg_Pub.Count_AND_Get
505 ( p_count => x_msg_count,
506 p_data => x_msg_data,
507 p_encoded => Fnd_Api.G_FALSE
508 );
509 WHEN OTHERS THEN
510 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
511 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
512 THEN
513 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
514 END IF;
515 Fnd_Msg_Pub.Count_AND_Get
516 ( p_count => x_msg_count,
517 p_data => x_msg_data,
518 p_encoded => Fnd_Api.G_FALSE
519 );
520 END Validate_Simulation_plan;
521
522 --------------------------------------------------------------------
523 -- PROCEDURE
524 -- Create_Simulation_plan
525 --
526 -- PURPOSE
527 -- Create Simulation plan Record
528 --
529 -- PARAMETERS
530 -- p_x_simulation_plan_rec: the record representing AHL_SIMULATION_PLANS_VL view..
531 --
532 -- NOTES
533 --------------------------------------------------------------------
534
535 PROCEDURE Create_Simulation_plan (
536 p_api_version IN NUMBER,
537 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
538 p_commit IN VARCHAR2 := FND_API.g_false,
539 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
540 p_module_type IN VARCHAR2 := 'JSP',
541 p_x_simulation_plan_rec IN OUT NOCOPY ahl_ltp_simul_plan_pub.Simulation_Plan_Rec,
542 x_return_status OUT NOCOPY VARCHAR2,
543 x_msg_count OUT NOCOPY NUMBER,
544 x_msg_data OUT NOCOPY VARCHAR2
545 )
546 IS
547 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SIMULATION_PLAN';
548 l_api_version CONSTANT NUMBER := 1.0;
549 l_msg_count NUMBER;
550 l_return_status VARCHAR2(1);
551 l_msg_data VARCHAR2(2000);
552 l_dummy NUMBER;
553 l_rowid VARCHAR2(30);
554 l_simulation_plan_id NUMBER;
555 l_simulation_plan_rec Simulation_Plan_Rec;
556 --
557 CURSOR c_seq IS
558 SELECT AHL_SIMULATION_PLANS_B_S.NEXTVAL
559 FROM dual;
560 --
561 CURSOR c_id_exists (x_id IN NUMBER) IS
562 SELECT 1
563 FROM dual
564 WHERE EXISTS (SELECT 1
565 FROM ahl_simulation_plans_b
566 WHERE simulation_plan_id = x_id);
567 --
568 BEGIN
569 --------------------Initialize ----------------------------------
570 -- Standard Start of API savepoint
571 SAVEPOINT create_simulation_plan;
572 -- Check if API is called in debug mode. If yes, enable debug.
573 IF G_DEBUG='Y' THEN
574 AHL_DEBUG_PUB.enable_debug;
575 END IF;
576 -- Debug info.
577 IF G_DEBUG='Y' THEN
578 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Create Simulation plan','+SIMPL+');
579 END IF;
580 -- Standard call to check for call compatibility.
581 IF FND_API.to_boolean(p_init_msg_list)
582 THEN
583 FND_MSG_PUB.initialize;
584 END IF;
585 -- Initialize API return status to success
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587 -- Initialize message list if p_init_msg_list is set to TRUE.
588 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
589 p_api_version,
590 l_api_name,G_PKG_NAME)
591 THEN
592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
593 END IF;
594 --------------------Value OR ID conversion---------------------------
595 --Assign to local variable
596 Assign_Simulation_Rec (
597 p_simulation_rec => p_x_simulation_plan_rec,
598 x_simulation_rec => l_Simulation_plan_rec);
599
600 -- Call Validate space rec input attributes
601 Validate_Simulation_plan
602 (p_api_version => l_api_version,
603 p_init_msg_list => p_init_msg_list,
604 p_validation_level => p_validation_level,
605 p_simulation_plan_rec => l_Simulation_plan_rec,
606 x_return_status => l_return_status,
607 x_msg_count => l_msg_count,
608 x_msg_data => l_msg_data );
609
610
611 IF (p_x_simulation_plan_rec.plan_id = Fnd_Api.G_MISS_NUM OR
612 p_x_simulation_plan_rec.plan_id IS NULL)
613 THEN
614 --
615 -- If the ID is not passed into the API, then
616 -- grab a value from the sequence.
617 OPEN c_seq;
618 FETCH c_seq INTO l_simulation_plan_id;
619 CLOSE c_seq;
620 --
621 -- Check to be sure that the sequence does not exist.
622 OPEN c_id_exists (l_simulation_plan_id);
623 FETCH c_id_exists INTO l_dummy;
624 CLOSE c_id_exists;
625 --
626 -- If the value for the ID already exists, then
627 -- l_dummy would be populated with '1', otherwise,
628 -- it receives NULL.
629 IF l_dummy IS NOT NULL THEN
630 Fnd_Message.SET_NAME('AHL','AHL_LTP_SEQUENCE_NOT_EXISTS');
631 Fnd_Msg_Pub.ADD;
632 END IF;
633 -- For optional fields
634 IF p_x_simulation_plan_rec.description = FND_API.G_MISS_CHAR THEN
635 l_simulation_plan_rec.description := NULL;
636 ELSE
637 l_simulation_plan_rec.description := p_x_simulation_plan_rec.description;
638 END IF;
639 --
640 IF p_x_simulation_plan_rec.attribute_category = FND_API.G_MISS_CHAR THEN
641 l_simulation_plan_rec.attribute_category := NULL;
642 ELSE
643 l_simulation_plan_rec.attribute_category := p_x_simulation_plan_rec.attribute_category;
644 END IF;
645 --
646 IF p_x_simulation_plan_rec.attribute1 = FND_API.G_MISS_CHAR THEN
647 l_simulation_plan_rec.attribute1 := NULL;
648 ELSE
649 l_simulation_plan_rec.attribute1 := p_x_simulation_plan_rec.attribute1;
650 END IF;
651 --
652 IF p_x_simulation_plan_rec.attribute2 = FND_API.G_MISS_CHAR THEN
653 l_simulation_plan_rec.attribute2 := NULL;
654 ELSE
655 l_simulation_plan_rec.attribute2 := p_x_simulation_plan_rec.attribute2;
656 END IF;
657 --
658 IF p_x_simulation_plan_rec.attribute3 = FND_API.G_MISS_CHAR THEN
659 l_simulation_plan_rec.attribute3 := NULL;
660 ELSE
661 l_simulation_plan_rec.attribute3 := p_x_simulation_plan_rec.attribute3;
662 END IF;
663 --
664 IF p_x_simulation_plan_rec.attribute4 = FND_API.G_MISS_CHAR THEN
665 l_simulation_plan_rec.attribute4 := NULL;
666 ELSE
667 l_simulation_plan_rec.attribute4 := p_x_simulation_plan_rec.attribute4;
668 END IF;
669 --
670 IF p_x_simulation_plan_rec.attribute5 = FND_API.G_MISS_CHAR THEN
671 l_simulation_plan_rec.attribute5 := NULL;
672 ELSE
673 l_simulation_plan_rec.attribute5 := p_x_simulation_plan_rec.attribute5;
674 END IF;
675 --
676 IF p_x_simulation_plan_rec.attribute6 = FND_API.G_MISS_CHAR THEN
677 l_simulation_plan_rec.attribute6 := NULL;
678 ELSE
679 l_simulation_plan_rec.attribute6 := p_x_simulation_plan_rec.attribute6;
680 END IF;
681 --
682 IF p_x_simulation_plan_rec.attribute7 = FND_API.G_MISS_CHAR THEN
683 l_simulation_plan_rec.attribute7 := NULL;
684 ELSE
685 l_simulation_plan_rec.attribute7 := p_x_simulation_plan_rec.attribute7;
686 END IF;
687 --
688 IF p_x_simulation_plan_rec.attribute8 = FND_API.G_MISS_CHAR THEN
689 l_simulation_plan_rec.attribute8 := NULL;
690 ELSE
691 l_simulation_plan_rec.attribute8 := p_x_simulation_plan_rec.attribute8;
692 END IF;
693 --
694 IF p_x_simulation_plan_rec.attribute9 = FND_API.G_MISS_CHAR THEN
695 l_simulation_plan_rec.attribute9 := NULL;
696 ELSE
697 l_simulation_plan_rec.attribute9 := p_x_simulation_plan_rec.attribute9;
698 END IF;
699 --
700 IF p_x_simulation_plan_rec.attribute10 = FND_API.G_MISS_CHAR THEN
701 l_simulation_plan_rec.attribute10 := NULL;
702 ELSE
703 l_simulation_plan_rec.attribute10 := p_x_simulation_plan_rec.attribute10;
704 END IF;
705 --
706 IF p_x_simulation_plan_rec.attribute11 = FND_API.G_MISS_CHAR THEN
707 l_simulation_plan_rec.attribute11 := NULL;
708 ELSE
709 l_simulation_plan_rec.attribute11 := p_x_simulation_plan_rec.attribute11;
710 END IF;
711 --
712 IF p_x_simulation_plan_rec.attribute12 = FND_API.G_MISS_CHAR THEN
713 l_simulation_plan_rec.attribute12 := NULL;
714 ELSE
715 l_simulation_plan_rec.attribute12 := p_x_simulation_plan_rec.attribute12;
716 END IF;
717 --
718 IF p_x_simulation_plan_rec.attribute13 = FND_API.G_MISS_CHAR THEN
719 l_simulation_plan_rec.attribute13 := NULL;
720 ELSE
721 l_simulation_plan_rec.attribute13 := p_x_simulation_plan_rec.attribute13;
722 END IF;
723 --
724 IF p_x_simulation_plan_rec.attribute14 = FND_API.G_MISS_CHAR THEN
725 l_simulation_plan_rec.attribute14 := NULL;
726 ELSE
727 l_simulation_plan_rec.attribute14 := p_x_simulation_plan_rec.attribute14;
728 END IF;
729 --
730 IF p_x_simulation_plan_rec.attribute15 = FND_API.G_MISS_CHAR THEN
731 l_simulation_plan_rec.attribute15 := NULL;
732 ELSE
733 l_simulation_plan_rec.attribute15 := p_x_simulation_plan_rec.attribute15;
734 END IF;
735 END IF;
736 --Standard check to count messages
737 l_msg_count := Fnd_Msg_Pub.count_msg;
738
739 IF l_msg_count > 0 THEN
740 X_msg_count := l_msg_count;
741 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
742 RAISE Fnd_Api.G_EXC_ERROR;
743 END IF;
744
745 ----------------------------DML Operation---------------------------------
746 --Call table handler generated package to insert a record
747 AHL_SIMULATION_PLANS_PKG.INSERT_ROW (
748 X_ROWID => l_rowid,
749 X_SIMULATION_PLAN_ID => l_simulation_plan_id,
750 X_PRIMARY_PLAN_FLAG => 'N',
751 X_OBJECT_VERSION_NUMBER => 1,
752 X_ATTRIBUTE_CATEGORY => l_simulation_plan_rec.attribute_category,
753 X_ATTRIBUTE1 => l_simulation_plan_rec.attribute1,
754 X_ATTRIBUTE2 => l_simulation_plan_rec.attribute2,
755 X_ATTRIBUTE3 => l_simulation_plan_rec.attribute3,
756 X_ATTRIBUTE4 => l_simulation_plan_rec.attribute4,
757 X_ATTRIBUTE5 => l_simulation_plan_rec.attribute5,
758 X_ATTRIBUTE6 => l_simulation_plan_rec.attribute6,
759 X_ATTRIBUTE7 => l_simulation_plan_rec.attribute7,
760 X_ATTRIBUTE8 => l_simulation_plan_rec.attribute8,
761 X_ATTRIBUTE9 => l_simulation_plan_rec.attribute9,
762 X_ATTRIBUTE10 => l_simulation_plan_rec.attribute10,
763 X_ATTRIBUTE11 => l_simulation_plan_rec.attribute11,
764 X_ATTRIBUTE12 => l_simulation_plan_rec.attribute12,
765 X_ATTRIBUTE13 => l_simulation_plan_rec.attribute13,
766 X_ATTRIBUTE14 => l_simulation_plan_rec.attribute14,
767 X_ATTRIBUTE15 => l_simulation_plan_rec.attribute15,
768 X_SIMULATION_PLAN_NAME => l_simulation_plan_rec.simulation_plan_name,
769 X_DESCRIPTION => l_simulation_plan_rec.description,
770 X_CREATION_DATE => SYSDATE,
771 X_CREATED_BY => Fnd_Global.USER_ID,
772 X_LAST_UPDATE_DATE => SYSDATE,
773 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
774 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
775
776 p_x_simulation_plan_rec.plan_id := l_simulation_plan_id;
777 ---------------------------End of Body---------------------------------------
778 --Standard check to count messages
779 l_msg_count := Fnd_Msg_Pub.count_msg;
780
781 IF l_msg_count > 0 THEN
782 X_msg_count := l_msg_count;
783 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
784 RAISE Fnd_Api.G_EXC_ERROR;
785 END IF;
786
787 --Standard check for commit
788 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
789 COMMIT;
790 END IF;
791 -- Debug info
792 IF G_DEBUG='Y' THEN
793 Ahl_Debug_Pub.debug( 'End of private api Create Simulation plan','+SMPLN+');
794 END IF;
795 -- Check if API is called in debug mode. If yes, disable debug.
796 IF G_DEBUG='Y' THEN
797 Ahl_Debug_Pub.disable_debug;
798 END IF;
799 EXCEPTION
800 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
801 ROLLBACK TO create_simulation_plan;
802 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
804 p_count => x_msg_count,
805 p_data => x_msg_data);
806 IF G_DEBUG='Y' THEN
807
808 AHL_DEBUG_PUB.log_app_messages (
809 x_msg_count, x_msg_data, 'ERROR' );
810 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Create Simulation plan','+SMPLN+');
811 -- Check if API is called in debug mode. If yes, disable debug.
812 AHL_DEBUG_PUB.disable_debug;
813 END IF;
814 WHEN FND_API.G_EXC_ERROR THEN
815 ROLLBACK TO create_simulation_plan;
816 X_return_status := FND_API.G_RET_STS_ERROR;
817 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
818 p_count => x_msg_count,
819 p_data => X_msg_data);
820 IF G_DEBUG='Y' THEN
821 -- Debug info.
822 AHL_DEBUG_PUB.log_app_messages (
823 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
824 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Create Simulation plan','+SMPLN+');
825 -- Check if API is called in debug mode. If yes, disable debug.
826 AHL_DEBUG_PUB.disable_debug;
827 END IF;
828 WHEN OTHERS THEN
829 ROLLBACK TO create_simulation_plan;
830 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
831 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
832 THEN
833 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
834 p_procedure_name => 'CREATE_SIMULATION_PLAN',
835 p_error_text => SUBSTR(SQLERRM,1,240));
836 END IF;
837 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
838 p_count => x_msg_count,
839 p_data => X_msg_data);
840 IF G_DEBUG='Y' THEN
841
842 -- Debug info.
843 AHL_DEBUG_PUB.log_app_messages (
844 x_msg_count, x_msg_data, 'SQL ERROR' );
845 AHL_DEBUG_PUB.debug( 'ahl_ltp_space_unavl_pvt.Create Simulation plan','+SMPLN+');
846 -- Check if API is called in debug mode. If yes, disable debug.
847 AHL_DEBUG_PUB.disable_debug;
848 END IF;
849 END Create_Simulation_plan;
850
851
852
853 --------------------------------------------------------------------
854 -- PROCEDURE
855 -- Update_Simulation_plan
856 --
857 -- PURPOSE
858 -- Update Simulation plan Record.
859 --
860 -- PARAMETERS
861 -- p_simulation_plan_rec: the record representing AHL_SIMULATION_PLANS_VL
862 --
863 -- NOTES
864 --------------------------------------------------------------------
865 PROCEDURE Update_Simulation_plan (
866 p_api_version IN NUMBER,
867 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
868 p_commit IN VARCHAR2 := FND_API.g_false,
869 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
870 p_module_type IN VARCHAR2 := 'JSP',
871 p_simulation_plan_rec IN ahl_ltp_simul_plan_pub.Simulation_plan_Rec,
872 x_return_status OUT NOCOPY VARCHAR2,
873 x_msg_count OUT NOCOPY NUMBER,
874 x_msg_data OUT NOCOPY VARCHAR2
875 )
876 IS
877 CURSOR primary_plan_cur(c_plan_id IN NUMBER)
878 IS
879 SELECT primary_plan_flag
880 FROM AHL_SIMULATION_PLANS_VL
881 WHERE simulation_plan_id = c_plan_id;
882
883 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SIMULATION_PLAN';
884 l_api_version CONSTANT NUMBER := 1.0;
885 l_msg_count NUMBER;
886 l_return_status VARCHAR2(1);
887 l_msg_data VARCHAR2(2000);
888 l_dummy NUMBER;
889 l_rowid VARCHAR2(30);
890 l_organization_id NUMBER;
891 l_department_id NUMBER;
892 l_space_id NUMBER;
893 l_simulation_plan_id NUMBER;
894 l_simulation_plan_rec Simulation_Plan_Rec;
895 l_Asimulation_plan_rec Simulation_Plan_Rec;
896 l_primary_plan_flag VARCHAR2(1);
897 BEGIN
898
899
900 --------------------Initialize ----------------------------------
901 -- Standard Start of API savepoint
902 SAVEPOINT update_simulation_plan;
903 -- Check if API is called in debug mode. If yes, enable debug.
904 IF G_DEBUG='Y' THEN
905 AHL_DEBUG_PUB.enable_debug;
906 END IF;
907 -- Debug info.
908 IF G_DEBUG='Y' THEN
909 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPNL+');
910 END IF;
911 -- Standard call to check for call compatibility.
912 IF FND_API.to_boolean(p_init_msg_list)
913 THEN
914 FND_MSG_PUB.initialize;
915 END IF;
916 -- Initialize API return status to success
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 -- Initialize message list if p_init_msg_list is set to TRUE.
919 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
920 p_api_version,
921 l_api_name,G_PKG_NAME)
922 THEN
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
924 END IF;
925
926 --------------------Value OR ID conversion---------------------------
927 --Assign to local variable
928 Assign_Simulation_Rec (
929 p_simulation_rec => p_simulation_plan_rec,
930 x_simulation_rec => l_Simulation_plan_rec);
931 --Start API Body
932
933 -- Convert org name to organization id
934 IF (p_simulation_plan_rec.plan_name IS NOT NULL AND
935 p_simulation_plan_rec.plan_name <> FND_API.G_MISS_CHAR ) OR
936 (l_simulation_plan_rec.simulation_plan_id IS NOT NULL AND
937 l_simulation_plan_rec.simulation_plan_id <> FND_API.G_MISS_NUM) THEN
938
939 Check_plan_name_Or_Id
940 (p_simulation_plan_id => l_simulation_plan_rec.simulation_plan_id,
941 p_plan_name => p_simulation_plan_rec.plan_name,
942 x_plan_id => l_simulation_plan_id,
943 x_return_status => l_return_status,
944 x_error_msg_code => l_msg_data);
945
946 IF NVL(l_return_status,'x') <> 'S'
947 THEN
948 Fnd_Message.SET_NAME('AHL','AHL_LTP_PLAN_NOT_EXISTS');
949 Fnd_Message.SET_TOKEN('PLANID',p_simulation_plan_rec.plan_name);
950 Fnd_Msg_Pub.ADD;
951 END IF;
952 END IF;
953 --Assign the returned value
954 l_simulation_plan_rec.simulation_plan_id := l_simulation_plan_id;
955
956 --------------------------------Validation ---------------------------
957 -- get existing values and compare
958 Complete_Simulation_Rec (
959 p_simulation_rec => l_simulation_plan_rec,
960 x_simulation_rec => l_Asimulation_plan_rec);
961
962 -- Call Validate simulation plan attributes
963 Validate_Simulation_plan
964 ( p_api_version => l_api_version,
965 p_init_msg_list => p_init_msg_list,
966 p_validation_level => p_validation_level,
967 p_simulation_plan_rec => l_ASimulation_plan_rec,
968 x_return_status => l_return_status,
969 x_msg_count => l_msg_count,
970 x_msg_data => l_msg_data );
971
972 IF l_Asimulation_plan_rec.simulation_plan_id IS NOT NULL THEN
973 OPEN primary_plan_cur(l_ASimulation_plan_rec.simulation_plan_id);
974 FETCH primary_plan_cur INTO l_primary_plan_flag;
975 CLOSE primary_plan_cur;
976 IF l_primary_plan_flag = 'Y' THEN
977 Fnd_Message.SET_NAME('AHL','AHL_LTP_PRIMARY_PLAN');
978 Fnd_Msg_Pub.ADD;
979 END IF;
980 END IF;
981 --Standard check to count messages
982 l_msg_count := Fnd_Msg_Pub.count_msg;
983
984 IF l_msg_count > 0 THEN
985 X_msg_count := l_msg_count;
986 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
987 RAISE Fnd_Api.G_EXC_ERROR;
988 END IF;
989
990 ----------------------------DML Operation---------------------------------
991 --Call table handler generated package to update a record
992 AHL_SIMULATION_PLANS_PKG.UPDATE_ROW
993 (
994 X_SIMULATION_PLAN_ID => l_Asimulation_plan_rec.simulation_plan_id,
995 X_PRIMARY_PLAN_FLAG => 'N',
996 X_SIMULATION_PLAN_NAME => l_Asimulation_plan_rec.simulation_plan_name,
997 X_DESCRIPTION => l_Asimulation_plan_rec.description,
998 X_OBJECT_VERSION_NUMBER => l_Asimulation_plan_rec.object_version_number+1,
999 X_ATTRIBUTE_CATEGORY => l_Asimulation_plan_rec.attribute_category,
1000 X_ATTRIBUTE1 => l_Asimulation_plan_rec.attribute1,
1001 X_ATTRIBUTE2 => l_Asimulation_plan_rec.attribute2,
1002 X_ATTRIBUTE3 => l_Asimulation_plan_rec.attribute3,
1003 X_ATTRIBUTE4 => l_Asimulation_plan_rec.attribute4,
1004 X_ATTRIBUTE5 => l_Asimulation_plan_rec.attribute5,
1005 X_ATTRIBUTE6 => l_Asimulation_plan_rec.attribute6,
1006 X_ATTRIBUTE7 => l_Asimulation_plan_rec.attribute7,
1007 X_ATTRIBUTE8 => l_Asimulation_plan_rec.attribute8,
1008 X_ATTRIBUTE9 => l_Asimulation_plan_rec.attribute9,
1009 X_ATTRIBUTE10 => l_Asimulation_plan_rec.attribute10,
1010 X_ATTRIBUTE11 => l_Asimulation_plan_rec.attribute11,
1011 X_ATTRIBUTE12 => l_Asimulation_plan_rec.attribute12,
1012 X_ATTRIBUTE13 => l_Asimulation_plan_rec.attribute13,
1013 X_ATTRIBUTE14 => l_Asimulation_plan_rec.attribute14,
1014 X_ATTRIBUTE15 => l_Asimulation_plan_rec.attribute15,
1015 X_LAST_UPDATE_DATE => SYSDATE,
1016 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1017 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1018
1019
1020 ---------------------------End of Body---------------------------------------
1021 --Standard check to count messages
1022 l_msg_count := Fnd_Msg_Pub.count_msg;
1023
1024 IF l_msg_count > 0 THEN
1025 X_msg_count := l_msg_count;
1026 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1027 RAISE Fnd_Api.G_EXC_ERROR;
1028 END IF;
1029
1030 --Standard check for commit
1031 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1032 COMMIT;
1033 END IF;
1034 -- Debug info
1035 IF G_DEBUG='Y' THEN
1036 Ahl_Debug_Pub.debug( 'End of private api Update Simulation plan','+SMPLN+');
1037 -- Check if API is called in debug mode. If yes, disable debug.
1038 Ahl_Debug_Pub.disable_debug;
1039 END IF;
1040 EXCEPTION
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO update_simulation_plan;
1043 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1045 p_count => x_msg_count,
1046 p_data => x_msg_data);
1047 IF G_DEBUG='Y' THEN
1048
1049 AHL_DEBUG_PUB.log_app_messages (
1050 x_msg_count, x_msg_data, 'ERROR' );
1051 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPLN+');
1052 -- Check if API is called in debug mode. If yes, disable debug.
1053 AHL_DEBUG_PUB.disable_debug;
1054 END IF;
1055 WHEN FND_API.G_EXC_ERROR THEN
1056 ROLLBACK TO update_simulation_plan;
1057 X_return_status := FND_API.G_RET_STS_ERROR;
1058 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1059 p_count => x_msg_count,
1060 p_data => X_msg_data);
1061 IF G_DEBUG='Y' THEN
1062
1063 -- Debug info.
1064 AHL_DEBUG_PUB.log_app_messages (
1065 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1066 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPLN+');
1067 -- Check if API is called in debug mode. If yes, disable debug.
1068 AHL_DEBUG_PUB.disable_debug;
1069 END IF;
1070 WHEN OTHERS THEN
1071 ROLLBACK TO update_simulation_plan;
1072 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1074 THEN
1075 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
1076 p_procedure_name => 'UPDATE_SIMULATION_PLAN',
1077 p_error_text => SUBSTR(SQLERRM,1,240));
1078 END IF;
1079 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1080 p_count => x_msg_count,
1081 p_data => X_msg_data);
1082 IF G_DEBUG='Y' THEN
1083
1084 -- Debug info.
1085 AHL_DEBUG_PUB.log_app_messages (
1086 x_msg_count, x_msg_data, 'SQL ERROR' );
1087 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPLN+');
1088 -- Check if API is called in debug mode. If yes, disable debug.
1089 AHL_DEBUG_PUB.disable_debug;
1090 END IF;
1091 END Update_Simulation_plan;
1092
1093
1094 --------------------------------------------------------------------
1095 -- PROCEDURE
1096 -- Delete_Simulation_plan
1097 --
1098 -- PURPOSE
1099 -- Delete Simulation plan Record.
1100 --
1101 -- PARAMETERS
1102 --
1103 -- ISSUES
1104 --
1105 -- NOTES
1106 -- 1. Raise exception if the object_version_number doesn't match.
1107 --------------------------------------------------------------------
1108 PROCEDURE Delete_Simulation_plan (
1109 p_api_version IN NUMBER,
1110 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1111 p_commit IN VARCHAR2 := FND_API.g_false,
1112 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1113 p_simulation_plan_rec IN ahl_ltp_simul_plan_pub.Simulation_plan_Rec,
1114 x_return_status OUT NOCOPY VARCHAR2,
1115 x_msg_count OUT NOCOPY NUMBER,
1116 x_msg_data OUT NOCOPY VARCHAR2
1117
1118 )
1119 IS
1120 CURSOR c_simulation_plan_cur
1121 (c_simulation_plan_id IN NUMBER)
1122 IS
1123 SELECT simulation_plan_id,object_version_number,
1124 primary_plan_flag
1125 FROM ahl_simulation_plans_vl
1126 WHERE simulation_plan_id = c_simulation_plan_id
1127 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1128
1129 -- Added by mpothuku on 12/22/04 to retrieve the associated simulation visits.
1130 --mpothuku begin
1131
1132 CURSOR get_simulation_visits_cur
1133 (c_simulation_plan_id IN NUMBER)
1134 IS
1135 SELECT visit_id
1136 FROM ahl_visits_b
1137 WHERE simulation_plan_id = c_simulation_plan_id;
1138
1139 -- Added by mpothuku on 12/22/04 to retrieve the associated simulation visits.
1140 CURSOR Get_simul_visit_tasks_cur(C_VISIT_ID IN NUMBER)
1141 IS
1142 SELECT visit_task_id
1143 FROM ahl_visit_tasks_vl
1144 WHERE visit_id = C_VISIT_ID;
1145
1146 -- End mpothuku
1147
1148 --
1149 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SIMULATION_PLAN';
1150 l_api_version CONSTANT NUMBER := 1.0;
1151 l_msg_count NUMBER;
1152 l_return_status VARCHAR2(1);
1153 l_msg_data VARCHAR2(2000);
1154 l_dummy NUMBER;
1155 l_simulation_plan_id NUMBER;
1156 l_object_version_number NUMBER;
1157 l_primary_plan_flag VARCHAR2(1);
1158 l_visit_id NUMBER;
1159 l_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
1160 l_visit_count NUMBER := 0;
1161 l_simul_visit_tasks_rec Get_simul_visit_tasks_cur%ROWTYPE;
1162 l_count NUMBER;
1163 l_space_assignment_id NUMBER;
1164
1165 BEGIN
1166 --------------------Initialize ----------------------------------
1167 -- Standard Start of API savepoint
1168 SAVEPOINT delete_simulation_plan;
1169 -- Check if API is called in debug mode. If yes, enable debug.
1170 IF G_DEBUG='Y' THEN
1171 AHL_DEBUG_PUB.enable_debug;
1172 END IF;
1173 -- Debug info.
1174 IF G_DEBUG='Y' THEN
1175 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
1176 END IF;
1177 -- Standard call to check for call compatibility.
1178 IF FND_API.to_boolean(p_init_msg_list)
1179 THEN
1180 FND_MSG_PUB.initialize;
1181 END IF;
1182 -- Initialize API return status to success
1183 x_return_status := FND_API.G_RET_STS_SUCCESS;
1184 -- Initialize message list if p_init_msg_list is set to TRUE.
1185 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1186 p_api_version,
1187 l_api_name,G_PKG_NAME)
1188 THEN
1189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1190 END IF;
1191 -----------------------Start of API Body-----------------------------
1192 -- Check for Record exists
1193 OPEN c_simulation_plan_cur(p_simulation_plan_rec.plan_id);
1194 FETCH c_simulation_plan_cur INTO l_simulation_plan_id,
1195 l_object_version_number,
1196 l_primary_plan_flag;
1197 IF c_simulation_plan_cur%NOTFOUND THEN
1198 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1199 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
1200 FND_MSG_PUB.add;
1201 END IF;
1202 CLOSE c_simulation_plan_cur;
1203 RAISE FND_API.g_exc_error;
1204 END IF;
1205 CLOSE c_simulation_plan_cur;
1206 --
1207 --Check for primary plan
1208 IF l_primary_plan_flag = 'Y'
1209 THEN
1210 FND_MESSAGE.set_name('AHL', 'AHL_LTP_PRIMARY_PLAN');
1211 FND_MSG_PUB.add;
1212 RAISE FND_API.g_exc_error;
1213 END IF;
1214
1215 /* Added by mpothuku on 12/22/04 to delete the associated simulation visits.
1216 */
1217 -- Get all the visits associated
1218 OPEN get_simulation_visits_cur(l_simulation_plan_id);
1219 LOOP
1220 FETCH get_simulation_visits_cur INTO l_visit_id;
1221 EXIT WHEN get_simulation_visits_cur%NOTFOUND;
1222 IF l_visit_id IS NOT NULL THEN
1223 Remove_Visits_FR_Plan (
1224 p_api_version => p_api_version,
1225 p_init_msg_list => FND_API.g_false,--p_init_msg_list,
1226 p_commit => FND_API.g_false, --p_commit,
1227 p_validation_level => p_validation_level,
1228 p_module_type => null,
1229 p_visit_id => l_visit_id,
1230 p_plan_id => null,
1231 p_v_ovn => null,
1232 x_return_status => l_return_status,
1233 x_msg_count => l_msg_count,
1234 x_msg_data => l_msg_data);
1235
1236 -- Check Error Message stack.
1237 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1238 l_msg_count := FND_MSG_PUB.count_msg;
1239 IF l_msg_count > 0 THEN
1240 RAISE FND_API.G_EXC_ERROR;
1241 END IF;
1242 END IF;
1243 END IF; -- If Visit not null
1244 END LOOP;
1245 CLOSE get_simulation_visits_cur;
1246
1247 --Check for object version number
1248 IF l_object_version_number <> p_simulation_plan_rec.object_version_number
1249 THEN
1250 FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_CHANGED');
1251 FND_MSG_PUB.add;
1252 RAISE FND_API.g_exc_error;
1253 END IF;
1254 -------------------Call Table handler generated procedure------------
1255 AHL_SIMULATION_PLANS_PKG.DELETE_ROW (
1256 X_SIMULATION_PLAN_ID => l_simulation_plan_id
1257 );
1258 ---------------------------End of Body---------------------------------------
1259 --Standard check to count messages
1260 l_msg_count := Fnd_Msg_Pub.count_msg;
1261
1262 IF l_msg_count > 0 THEN
1263 X_msg_count := l_msg_count;
1264 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1265 RAISE Fnd_Api.G_EXC_ERROR;
1266 END IF;
1267
1268 --Standard check for commit
1269 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1270 COMMIT;
1271 END IF;
1272 -- Debug info
1273 IF G_DEBUG='Y' THEN
1274 Ahl_Debug_Pub.debug( 'End of private api Delete Simulation plan','+SMPLN+');
1275 END IF;
1276 -- Check if API is called in debug mode. If yes, disable debug.
1277 IF G_DEBUG='Y' THEN
1278 Ahl_Debug_Pub.disable_debug;
1279 END IF;
1280 EXCEPTION
1281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1282 ROLLBACK TO delete_simulation_plan;
1283 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1284 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1285 p_count => x_msg_count,
1286 p_data => x_msg_data);
1287 IF G_DEBUG='Y' THEN
1288
1289 AHL_DEBUG_PUB.log_app_messages (
1290 x_msg_count, x_msg_data, 'ERROR' );
1291 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
1292 -- Check if API is called in debug mode. If yes, disable debug.
1293 AHL_DEBUG_PUB.disable_debug;
1294 END IF;
1295 WHEN FND_API.G_EXC_ERROR THEN
1296 ROLLBACK TO delete_simulation_plan;
1297 X_return_status := FND_API.G_RET_STS_ERROR;
1298 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1299 p_count => x_msg_count,
1300 p_data => X_msg_data);
1301 IF G_DEBUG='Y' THEN
1302 -- Debug info.
1303 AHL_DEBUG_PUB.log_app_messages (
1304 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1305 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
1306 -- Check if API is called in debug mode. If yes, disable debug.
1307 AHL_DEBUG_PUB.disable_debug;
1308 END IF;
1309 WHEN OTHERS THEN
1310 ROLLBACK TO delete_simulation_plan;
1311 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1312 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1313 THEN
1314 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
1315 p_procedure_name => 'DELETE_SIMULATION_PLAN',
1316 p_error_text => SUBSTR(SQLERRM,1,240));
1317 END IF;
1318 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1319 p_count => x_msg_count,
1320 p_data => X_msg_data);
1321 IF G_DEBUG='Y' THEN
1322
1323 -- Debug info.
1324 AHL_DEBUG_PUB.log_app_messages (
1325 x_msg_count, x_msg_data, 'SQL ERROR' );
1326 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
1327 -- Check if API is called in debug mode. If yes, disable debug.
1328 AHL_DEBUG_PUB.disable_debug;
1329 END IF;
1330 END Delete_Simulation_plan;
1331
1332
1333 --------------------------------------------------------------------
1334 -- PROCEDURE
1335 -- Copy_Visits_To_Plan
1336 --
1337 -- PURPOSE
1338 -- Copy Visits from primary plan to Simulation Plan and one simulation plan
1339 -- to another
1340 --
1341 --
1342 -- PARAMETERS
1343 -- p_visit_rec Record representing AHL_VISITS_VL
1344 --
1345 -- NOTES
1346 --------------------------------------------------------------------
1347 PROCEDURE Copy_Visits_To_Plan (
1348 p_api_version IN NUMBER,
1349 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1350 p_commit IN VARCHAR2 := FND_API.g_false,
1351 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1352 p_module_type IN VARCHAR2 := 'JSP',
1353 p_visit_id IN NUMBER ,
1354 p_visit_number IN NUMBER ,
1355 p_plan_id IN NUMBER,
1356 p_v_ovn IN NUMBER,
1357 p_p_ovn IN NUMBER,
1358 x_visit_id OUT NOCOPY NUMBER,
1359 x_return_status OUT NOCOPY VARCHAR2,
1360 x_msg_count OUT NOCOPY NUMBER,
1361 x_msg_data OUT NOCOPY VARCHAR2 )
1362 IS
1363 --
1364
1365 -- yazhou 20-Jul-2006 starts
1366 -- bug fix#5387780
1367 -- Should allow only primary visits in the current OU to be copied
1368
1369 CURSOR get_visit_id_cur (c_visit_number IN NUMBER)
1370 IS
1371 SELECT visit_id,asso_primary_visit_id
1372 FROM ahl_visits_vl
1373 WHERE visit_number = c_visit_number
1374 AND status_code ='PLANNING'
1375 AND (ORGANIZATION_ID is NULL OR ORGANIZATION_ID IN ( SELECT organization_id
1376 FROM org_organization_definitions
1377 WHERE operating_unit = mo_global.get_current_org_id() ));
1378
1379 -- yazhou 20-Jul-2006 starts
1380
1381 --
1382 CURSOR get_visit_num_cur (c_visit_id IN NUMBER)
1383 IS
1384 SELECT visit_id,asso_primary_visit_id
1385 FROM ahl_visits_vl
1386 WHERE visit_id = c_visit_id;
1387 --
1388
1389 CURSOR visit_detail_cur(c_visit_id IN NUMBER)
1390 IS
1391 SELECT VISIT_ID,
1392 VISIT_NAME,
1393 ORGANIZATION_ID,
1394 DEPARTMENT_ID,
1395 STATUS_CODE,
1396 START_DATE_TIME,
1397 VISIT_TYPE_CODE,
1398 SIMULATION_PLAN_ID,
1399 ITEM_INSTANCE_ID,
1400 INVENTORY_ITEM_ID,
1401 ASSO_PRIMARY_VISIT_ID,
1402 SIMULATION_DELETE_FLAG,
1403 TEMPLATE_FLAG,
1404 OUT_OF_SYNC_FLAG,
1405 PROJECT_FLAG,
1406 ITEM_ORGANIZATION_ID,
1407 INV_LOCATOR_ID, --Added by sowsubra
1408 PROJECT_ID,
1409 VISIT_NUMBER,
1410 DESCRIPTION,
1411 SERVICE_REQUEST_ID,
1412 SPACE_CATEGORY_CODE,
1413 SCHEDULE_DESIGNATOR,
1414 CLOSE_DATE_TIME,
1415 PRICE_LIST_ID,
1416 ESTIMATED_PRICE,
1417 ACTUAL_PRICE,
1418 OUTSIDE_PARTY_FLAG,
1419 ANY_TASK_CHG_FLAG,
1420 UNIT_SCHEDULE_ID,
1421 OBJECT_VERSION_NUMBER,
1422 PRIORITY_CODE,
1423 PROJECT_TEMPLATE_ID,
1424 ATTRIBUTE_CATEGORY,
1425 ATTRIBUTE1,
1426 ATTRIBUTE2,
1427 ATTRIBUTE3,
1428 ATTRIBUTE4,
1429 ATTRIBUTE5,
1430 ATTRIBUTE6,
1431 ATTRIBUTE7,
1432 ATTRIBUTE8,
1433 ATTRIBUTE9,
1434 ATTRIBUTE10,
1435 ATTRIBUTE11,
1436 ATTRIBUTE12,
1437 ATTRIBUTE13,
1438 ATTRIBUTE14,
1439 ATTRIBUTE15
1440 FROM AHL_VISITS_VL
1441 WHERE visit_id = c_visit_id;
1442 -- Check for one visit can be copied into simulation plan
1443 CURSOR check_visit_exist_cur (c_plan_id IN NUMBER,
1444 c_visit_id IN NUMBER,
1445 c_asso_visit_id IN NUMBER)
1446 IS
1447 SELECT asso_primary_visit_id
1448 FROM AHL_VISITS_VL
1449 WHERE simulation_plan_id = c_plan_id
1450 AND (asso_primary_visit_id = c_visit_id
1451 OR NVL(asso_primary_visit_id, 0) = c_asso_visit_id);
1452 --
1453 CURSOR get_visit_task_cur
1454 (c_visit_id IN NUMBER)
1455 IS
1456 SELECT VISIT_TASK_ID,
1457 VISIT_TASK_NUMBER,
1458 OBJECT_VERSION_NUMBER,
1459 VISIT_ID,
1460 PROJECT_TASK_ID,
1461 COST_PARENT_ID,
1462 MR_ROUTE_ID,
1463 MR_ID,
1464 DURATION,
1465 UNIT_EFFECTIVITY_ID,
1466 VISIT_TASK_NAME,
1467 DESCRIPTION,
1468 START_FROM_HOUR,
1469 INVENTORY_ITEM_ID,
1470 ITEM_ORGANIZATION_ID,
1471 INSTANCE_ID,
1472 PRIMARY_VISIT_TASK_ID,
1473 SUMMARY_TASK_FLAG,
1474 ORIGINATING_TASK_ID,
1475 SERVICE_REQUEST_ID,
1476 TASK_TYPE_CODE,
1477 DEPARTMENT_ID,
1478 PRICE_LIST_ID,
1479 STATUS_CODE,
1480 ACTUAL_COST,
1481 ESTIMATED_PRICE,
1482 ACTUAL_PRICE,
1483 STAGE_ID,
1484 START_DATE_TIME,
1485 END_DATE_TIME,
1486 QUANTITY, -- Added by rnahata for Issue 105
1487 ATTRIBUTE_CATEGORY,
1488 ATTRIBUTE1,
1489 ATTRIBUTE2,
1490 ATTRIBUTE3,
1491 ATTRIBUTE4,
1492 ATTRIBUTE5,
1493 ATTRIBUTE6,
1494 ATTRIBUTE7,
1495 ATTRIBUTE8,
1496 ATTRIBUTE9,
1497 ATTRIBUTE10,
1498 ATTRIBUTE11,
1499 ATTRIBUTE12,
1500 ATTRIBUTE13,
1501 ATTRIBUTE14,
1502 ATTRIBUTE15
1503 FROM AHL_VISIT_TASKS_VL
1504 WHERE visit_id = c_visit_id
1505 AND STATUS_CODE <> 'DELETED';
1506 --
1507 CURSOR Get_space_Assign_cur (c_visit_id IN NUMBER) IS
1508 SELECT space_id,space_assignment_id
1509 FROM ahl_space_assignments
1510 WHERE visit_id = c_visit_id;
1511
1512 --Added by mpothuku on 12/27/04
1513
1514 -- To find the coresponding task id in the new visit
1515 CURSOR c_new_task_ID(x_visit_task_id IN NUMBER, x_new_visit_id IN NUMBER) IS
1516 SELECT b.VISIT_TASK_ID
1517 FROM AHL_VISIT_TASKS_B a, AHL_VISIT_TASKS_B b
1518 WHERE a.visit_task_id = x_visit_task_id
1519 AND a.visit_task_number = b.visit_task_number
1520 AND b.visit_id = x_new_visit_id;
1521
1522 -- To find task link related information for a visit
1523 CURSOR c_visit_task_links(x_visit_id IN NUMBER) IS
1524 SELECT VISIT_TASK_ID ,
1525 PARENT_TASK_ID,
1526 --SECURITY_GROUP_ID,
1527 ATTRIBUTE_CATEGORY,
1528 ATTRIBUTE1,
1529 ATTRIBUTE2,
1530 ATTRIBUTE3,
1531 ATTRIBUTE4,
1532 ATTRIBUTE5,
1533 ATTRIBUTE6,
1534 ATTRIBUTE7,
1535 ATTRIBUTE8,
1536 ATTRIBUTE9,
1537 ATTRIBUTE10,
1538 ATTRIBUTE11,
1539 ATTRIBUTE12,
1540 ATTRIBUTE13,
1541 ATTRIBUTE14,
1542 ATTRIBUTE15
1543 FROM AHL_TASK_LINKS
1544 WHERE visit_task_id in (SELECT VISIT_TASK_ID
1545 FROM AHL_VISIT_TASKS_B
1546 WHERE visit_id = x_visit_id);
1547
1548 --To get the stages from a visit
1549 CURSOR Get_stages_cur(c_visit_id IN NUMBER) IS
1550 SELECT STAGE_ID,
1551 STAGE_NUM,
1552 VISIT_ID,
1553 DURATION,
1554 OBJECT_VERSION_NUMBER,
1555 STAGE_NAME,
1556 --SECURITY_GROUP_ID,
1557 ATTRIBUTE_CATEGORY,
1558 ATTRIBUTE1,
1559 ATTRIBUTE2,
1560 ATTRIBUTE3,
1561 ATTRIBUTE4,
1562 ATTRIBUTE5,
1563 ATTRIBUTE6,
1564 ATTRIBUTE7,
1565 ATTRIBUTE8,
1566 ATTRIBUTE9,
1567 ATTRIBUTE10,
1568 ATTRIBUTE11,
1569 ATTRIBUTE12,
1570 ATTRIBUTE13,
1571 ATTRIBUTE14,
1572 ATTRIBUTE15
1573 FROM ahl_vwp_stages_vl s
1574 WHERE visit_id = c_visit_id
1575 ORDER BY stage_num;
1576
1577 -- Added by mpothuku on 01/20/05 To find if this Unit has been planned in other visits already
1578 CURSOR chk_unit_effectivities (c_unit_id IN NUMBER, c_plan_id IN NUMBER,c_visit_id IN NUMBER) IS
1579 SELECT VISIT_NUMBER,ASSO_PRIMARY_VISIT_ID FROM AHL_VISITS_B WHERE
1580 VISIT_ID IN (SELECT DISTINCT VISIT_ID FROM AHL_VISIT_TASKS_B WHERE
1581 Unit_Effectivity_Id = c_unit_id)
1582 --The following condition is necessary since the summary task may already have been
1583 --added to the current visit which will have the same UE as the planned task
1584 and visit_id <> c_visit_id
1585 and simulation_plan_id = c_plan_id
1586 and status_code not in ('CANCELLED','DELETED');
1587
1588 /*
1589 CURSOR c_ue_details(c_unit_id IN NUMBER) IS
1590 select ue.title ue_title, ue.part_number, ue.serial_number, MR.title mr_title from ahl_unit_effectivities_v ue,ahl_mr_headers_v MR where MR.mr_header_id = ue.mr_header_id
1591 and ue.unit_effectivity_id = c_unit_id;
1592 */
1593
1594 /*
1595 AnRaj: Added for fixing the performance issues logged in bug#:4919576
1596 */
1597
1598 CURSOR c_ue_mr_sr_id(c_unit_id IN NUMBER) IS
1599 select ue.mr_header_id, ue.cs_incident_id,ue.csi_item_instance_id
1600 from ahl_unit_effectivities_b ue
1601 where ue.unit_effectivity_id = c_unit_id;
1602 ue_mr_sr_rec c_ue_mr_sr_id%ROWTYPE;
1603
1604 CURSOR c_ue_mr_details(c_mr_header_id IN NUMBER,c_item_instance_id IN NUMBER) IS
1605 SELECT mr.title ue_title,
1606 mtl.concatenated_segments part_number,
1607 csi.serial_number serial_number,
1608 mr.title mr_title
1609 FROM ahl_mr_headers_vl mr,
1610 mtl_system_items_kfv mtl,
1611 csi_item_instances csi
1612 WHERE mr.mr_header_id = c_mr_header_id
1613 AND csi.instance_id = c_item_instance_id
1614 AND csi.inventory_item_id = mtl.inventory_item_id
1615 AND csi.inv_master_organization_id = mtl.organization_id ;
1616 ue_mr_details_rec c_ue_mr_details%ROWTYPE;
1617
1618 CURSOR c_ue_sr_details(cs_incident_id IN NUMBER,c_item_instance_id IN NUMBER) IS
1619 SELECT (cit.name || '-' || cs.incident_number) ue_title,
1620 mtl.concatenated_segments part_number,
1621 csi.serial_number serial_number,
1622 null mr_title
1623 FROM cs_incident_types_vl cit,
1624 cs_incidents_all_b cs,
1625 mtl_system_items_kfv mtl,
1626 csi_item_instances csi
1627 WHERE cs.incident_id = cs_incident_id
1628 AND cit.incident_type_id = cs.incident_type_id
1629 AND csi.instance_id = c_item_instance_id
1630 AND csi.inventory_item_id = mtl.inventory_item_id
1631 AND csi.inv_master_organization_id = mtl.organization_id ;
1632 ue_sr_details_rec c_ue_sr_details%ROWTYPE;
1633 /*
1634 AnRaj: End of Fix bug#:4919576
1635 */
1636
1637
1638 CURSOR get_visit_number(c_visit_id IN NUMBER)
1639 IS
1640 SELECT visit_number
1641 FROM ahl_visits_vl
1642 WHERE visit_id = c_visit_id;
1643
1644 CURSOR check_primary_visit(c_visit_id IN NUMBER) IS
1645 SELECT ahlv.visit_id from ahl_visits_b ahlv, ahl_simulation_plans_b ahlsp
1646 where ahlv.visit_id = c_visit_id
1647 and ahlv.simulation_plan_id = ahlsp.simulation_plan_id
1648 and ahlsp.primary_plan_flag = 'Y';
1649
1650 CURSOR c_task(c_task_id IN NUMBER) IS
1651 SELECT *
1652 FROM Ahl_Visit_tasks_vl
1653 WHERE visit_task_id = c_task_id;
1654
1655 -- To find the coresponding Stage id in the new visit
1656 CURSOR c_new_stage_id(c_old_stage_id IN NUMBER, c_new_visit_id IN NUMBER) IS
1657 SELECT NewStage.Stage_Id
1658 FROM ahl_vwp_stages_b OldStage, ahl_vwp_stages_b NewStage
1659 WHERE OldStage.Stage_Id = c_old_stage_id
1660 AND NewStage.visit_id = c_new_visit_id
1661 AND NewStage.Stage_Num = OldStage.Stage_Num;
1662
1663 --mpothuku End
1664 --
1665 l_api_name CONSTANT VARCHAR2(30) := 'COPY_VISITS_TO_PLAN';
1666 l_api_version CONSTANT NUMBER := 1.0;
1667 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1668 l_msg_count NUMBER;
1669 l_return_status VARCHAR2(1);
1670 l_msg_data VARCHAR2(2000);
1671 l_dummy NUMBER;
1672 l_rowid VARCHAR2(30);
1673 l_simulation_plan_id NUMBER;
1674 l_visit_id NUMBER;
1675 l_primary_visit_id NUMBER;
1676 l_new_visit_id NUMBER;
1677 l_visit_number NUMBER;
1678 x_visit_number NUMBER;
1679 l_plan_ovn_number NUMBER;
1680 l_plan_flag VARCHAR2(1);
1681 l_meaning VARCHAR2(80);
1682 l_dup_id NUMBER;
1683 l_visit_detail_rec visit_detail_cur%ROWTYPE;
1684 l_visit_rec AHL_VWP_VISITS_PVT.visit_rec_type;
1685 l_visit_task_rec get_visit_task_cur%ROWTYPE;
1686 l_visit_task_id NUMBER;
1687 l_space_id NUMBER;
1688 l_space_assignment_id NUMBER;
1689 l_pvisit_id NUMBER;
1690 l_new_parent_task_id NUMBER;
1691 l_new_task_id NUMBER;
1692 l_stage_id NUMBER;
1693 l_stage_rec Get_stages_cur%ROWTYPE;
1694 --l_ue_details_rec c_ue_details%ROWTYPE;
1695 l_primary_visit_number NUMBER;
1696 l_asso_prim_visit_id NUMBER;
1697 l_asso_prim_visit_number NUMBER;
1698 l_primary_visit_task_id NUMBER;
1699 l_primary_visit_check NUMBER;
1700 l_originating_task_id NUMBER;
1701 l_cost_parent_id NUMBER;
1702 c_task_rec c_task%ROWTYPE;
1703 l_task_link_rec c_visit_task_links%ROWTYPE;
1704
1705 --
1706 BEGIN
1707 --------------------Initialize ----------------------------------
1708 -- Standard Start of API savepoint
1709 SAVEPOINT copy_visits_to_plan;
1710 -- Check if API is called in debug mode. If yes, enable debug.
1711 IF G_DEBUG='Y' THEN
1712 AHL_DEBUG_PUB.enable_debug;
1713 -- Debug info.
1714 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Copy Visits to Plan','+SMPNL+');
1715 END IF;
1716 -- Standard call to check for call compatibility.
1717 IF FND_API.to_boolean(p_init_msg_list)
1718 THEN
1719 FND_MSG_PUB.initialize;
1720 END IF;
1721 -- Initialize API return status to success
1722 x_return_status := FND_API.G_RET_STS_SUCCESS;
1723 -- Initialize message list if p_init_msg_list is set to TRUE.
1724 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1725 p_api_version,
1726 l_api_name,G_PKG_NAME)
1727 THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730 IF G_DEBUG='Y' THEN
1731 AHL_DEBUG_PUB.debug( 'visit_id'||p_visit_id);
1732 AHL_DEBUG_PUB.debug( 'visit_number'||p_visit_number);
1733 END IF;
1734 ---------------------start API Body------------------------------------
1735 --
1736 IF (p_visit_number IS NOT NULL AND
1737 p_visit_number <> FND_API.G_MISS_NUM) THEN
1738 --
1739 OPEN get_visit_id_cur(p_visit_number);
1740 FETCH get_visit_id_cur INTO l_pvisit_id, l_primary_visit_id;
1741 IF get_visit_id_cur%NOTFOUND THEN
1742 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_VISIT_NUMBER');
1743 Fnd_Msg_Pub.ADD;
1744 CLOSE get_visit_id_cur;
1745 RAISE Fnd_Api.G_EXC_ERROR;
1746 --
1747 END IF;
1748 CLOSE get_visit_id_cur;
1749 END IF;
1750 IF G_DEBUG='Y' THEN
1751 --
1752 AHL_DEBUG_PUB.debug( 'visit_id'||l_pvisit_id);
1753 AHL_DEBUG_PUB.debug( 'visit_number'||l_primary_visit_id);
1754 END IF;
1755 --
1756 IF (p_visit_id IS NOT NULL AND
1757 p_visit_id <> FND_API.G_MISS_NUM) THEN
1758 OPEN get_visit_num_cur(p_visit_id);
1759 FETCH get_visit_num_cur INTO l_pvisit_id, l_primary_visit_id;
1760 IF get_visit_num_cur%NOTFOUND THEN
1761 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_VISIT_NUMBER');
1762 Fnd_Msg_Pub.ADD;
1763 CLOSE get_visit_num_cur;
1764 RAISE Fnd_Api.G_EXC_ERROR;
1765 END IF;
1766 CLOSE get_visit_num_cur;
1767 END IF;
1768 --
1769 --Get simulation plan id
1770 IF (p_plan_id IS NOT NULL AND
1771 p_plan_id <> FND_API.G_MISS_NUM) THEN
1772 SELECT simulation_plan_id,primary_plan_flag
1773 INTO l_simulation_plan_id, l_plan_flag
1774 FROM AHL_SIMULATION_PLANS_VL
1775 WHERE simulation_plan_id = p_plan_id;
1776 ELSE
1777 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_SIMUL_NAME');
1778 Fnd_Msg_Pub.ADD;
1779 RAISE Fnd_Api.G_EXC_ERROR;
1780 END IF;
1781 --Check for copying to priamry plan
1782 IF l_plan_flag = 'Y' THEN
1783 Fnd_message.SET_NAME('AHL','AHL_LTP_NO_COPY_PRIM_PLAN');
1784 Fnd_Msg_Pub.ADD;
1785 RAISE Fnd_Api.G_EXC_ERROR;
1786 END IF;
1787 --
1788 OPEN visit_detail_cur(l_pvisit_id);
1789 FETCH visit_detail_cur INTO l_visit_detail_rec;
1790 CLOSE visit_detail_cur;
1791 --
1792 --Check for duplicate records
1793 IF l_visit_detail_rec.visit_id = l_pvisit_id THEN
1794 IF l_visit_detail_rec.simulation_plan_id = p_plan_id THEN
1795 Fnd_message.SET_NAME('AHL','AHL_LTP_RECORD_EXISTS');
1796 Fnd_Msg_Pub.ADD;
1797 RAISE Fnd_Api.G_EXC_ERROR;
1798 END IF;
1799 END IF;
1800
1801 --Check for Object version number
1802 IF (p_v_ovn IS NOT NULL AND p_v_ovn <> FND_API.G_MISS_NUM )
1803 THEN
1804 IF p_v_ovn <> l_visit_detail_rec.object_version_number THEN
1805 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_VISIT_RECORD');
1806 Fnd_Msg_Pub.ADD;
1807 RAISE Fnd_Api.G_EXC_ERROR;
1808 END IF;
1809 END IF;
1810 --
1811 SELECT object_version_number INTO l_plan_ovn_number FROM
1812 AHL_SIMULATION_PLANS_VL WHERE simulation_plan_id = p_plan_id;
1813 --Check for plan object version number
1814 IF (p_p_ovn IS NOT NULL AND p_p_ovn <> FND_API.G_MISS_NUM )
1815 THEN
1816 IF p_p_ovn <> l_plan_ovn_number THEN
1817 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_PLAN_RECORD');
1818 Fnd_Msg_Pub.ADD;
1819 RAISE Fnd_Api.G_EXC_ERROR;
1820 END IF;
1821 END IF;
1822 --Check for duplicate records
1823 OPEN check_visit_exist_cur(p_plan_id,l_pvisit_id,l_primary_visit_id);
1824 FETCH check_visit_exist_cur INTO l_dup_id;
1825 CLOSE check_visit_exist_cur;
1826 --
1827 IF l_dup_id IS NOT NULL THEN
1828 Fnd_message.SET_NAME('AHL','AHL_LTP_VISIT_NUMBER_EXISTS');
1829 Fnd_Msg_Pub.ADD;
1830 RAISE Fnd_Api.G_EXC_ERROR;
1831 END IF;
1832 IF G_DEBUG='Y' THEN
1833 AHL_DEBUG_PUB.debug( 'plan_id'||p_plan_id);
1834 AHL_DEBUG_PUB.debug( 'visit_id'||l_pvisit_id);
1835 AHL_DEBUG_PUB.debug( 'asso visit id'||l_primary_visit_id);
1836 END IF;
1837
1838 --Change by mpothuku End
1839
1840 IF G_DEBUG='Y' THEN
1841 AHL_DEBUG_PUB.debug( 'visit_type_code'||l_visit_detail_rec.visit_type_code);
1842 AHL_DEBUG_PUB.debug( 'inventory_id'||l_visit_detail_rec.inventory_item_id);
1843 END IF;
1844 --Get visit id
1845 SELECT Ahl_Visits_B_S.NEXTVAL INTO l_visit_id
1846 FROM dual;
1847 --Get visit number
1848 SELECT MAX(visit_number) INTO l_visit_number
1849 FROM Ahl_Visits_B;
1850 --
1851 ahl_visits_pkg.Insert_Row
1852 (
1853 X_ROWID => l_rowid,
1854 X_VISIT_ID => l_visit_id,
1855 X_VISIT_NUMBER => l_visit_number+1,
1856 X_VISIT_TYPE_CODE => l_visit_detail_rec.visit_type_code,
1857 X_SIMULATION_PLAN_ID => p_plan_id,
1858 X_ITEM_INSTANCE_ID => l_visit_detail_rec.item_instance_id,
1859 X_ITEM_ORGANIZATION_ID => l_visit_detail_rec.item_organization_id,
1860 X_INVENTORY_ITEM_ID => l_visit_detail_rec.inventory_item_id,
1861 X_ASSO_PRIMARY_VISIT_ID => nvl(l_visit_detail_rec.asso_primary_visit_id,l_pvisit_id),
1862 X_SIMULATION_DELETE_FLAG => NVL(l_visit_detail_rec.simulation_delete_flag,'N'),
1863 X_TEMPLATE_FLAG => l_visit_detail_rec.template_flag,
1864 X_OUT_OF_SYNC_FLAG => l_visit_detail_rec.out_of_sync_flag,
1865 X_PROJECT_FLAG => l_visit_detail_rec.project_flag,
1866 X_PROJECT_ID => l_visit_detail_rec.project_id,
1867 X_SERVICE_REQUEST_ID => l_visit_detail_rec.service_request_id,
1868 X_SPACE_CATEGORY_CODE => l_visit_detail_rec.space_category_code,
1869 X_SCHEDULE_DESIGNATOR => l_visit_detail_rec.schedule_designator,
1870 X_ATTRIBUTE_CATEGORY => l_visit_detail_rec.attribute_category,
1871 X_ATTRIBUTE1 => l_visit_detail_rec.attribute1,
1872 X_ATTRIBUTE2 => l_visit_detail_rec.attribute2,
1873 X_ATTRIBUTE3 => l_visit_detail_rec.attribute3,
1874 X_ATTRIBUTE4 => l_visit_detail_rec.attribute4,
1875 X_ATTRIBUTE5 => l_visit_detail_rec.attribute5,
1876 X_ATTRIBUTE6 => l_visit_detail_rec.attribute6,
1877 X_ATTRIBUTE7 => l_visit_detail_rec.attribute7,
1878 X_ATTRIBUTE8 => l_visit_detail_rec.attribute8,
1879 X_ATTRIBUTE9 => l_visit_detail_rec.attribute9,
1880 X_ATTRIBUTE10 => l_visit_detail_rec.attribute10,
1881 X_ATTRIBUTE11 => l_visit_detail_rec.attribute11,
1882 X_ATTRIBUTE12 => l_visit_detail_rec.attribute12,
1883 X_ATTRIBUTE13 => l_visit_detail_rec.attribute13,
1884 X_ATTRIBUTE14 => l_visit_detail_rec.attribute14,
1885 X_ATTRIBUTE15 => l_visit_detail_rec.attribute15,
1886 X_OBJECT_VERSION_NUMBER => 1,
1887 X_ORGANIZATION_ID => l_visit_detail_rec.organization_id,
1888 X_DEPARTMENT_ID => l_visit_detail_rec.department_id,
1889 X_STATUS_CODE => l_visit_detail_rec.status_code,
1890 X_START_DATE_TIME => l_visit_detail_rec.start_date_time,
1891 X_CLOSE_DATE_TIME => l_visit_detail_rec.close_date_time,
1892 X_VISIT_NAME => l_visit_detail_rec.visit_name,--'COPY FROM PLAN',
1893 X_DESCRIPTION => l_visit_detail_rec.description,
1894 X_PRICE_LIST_ID => l_visit_detail_rec.price_list_id,
1895 X_ESTIMATED_PRICE => l_visit_detail_rec.estimated_price,
1896 X_ACTUAL_PRICE => l_visit_detail_rec.actual_price,
1897 X_OUTSIDE_PARTY_FLAG => l_visit_detail_rec.outside_party_flag,
1898 X_ANY_TASK_CHG_FLAG => l_visit_detail_rec.any_task_chg_flag,
1899 X_PRIORITY_CODE => l_visit_detail_rec.priority_code,
1900 X_PROJECT_TEMPLATE_ID => l_visit_detail_rec.project_template_id,
1901 X_UNIT_SCHEDULE_ID => l_visit_detail_rec.unit_schedule_id,
1902 X_INV_LOCATOR_ID => l_visit_detail_rec.inv_locator_id, /*Added by sowsubra*/
1903 X_CREATION_DATE => SYSDATE,
1904 X_CREATED_BY => Fnd_Global.USER_ID,
1905 X_LAST_UPDATE_DATE => SYSDATE,
1906 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1907 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID
1908 );
1909 --Assign Out parameter
1910 x_visit_id := l_visit_id;
1911
1912 --Added by mpothuku to copy Visit Stages on 01/13/04
1913 OPEN Get_stages_cur(l_pvisit_id);
1914 LOOP
1915 FETCH Get_stages_cur INTO l_stage_rec;
1916 EXIT WHEN Get_stages_cur%NOTFOUND;
1917 IF G_DEBUG='Y' THEN
1918 AHL_DEBUG_PUB.debug( 'inside loop stage num:'||l_stage_rec.stage_num);
1919 END IF;
1920 -- Get visit task id
1921 /* Have to Confirm with the Stages API */
1922 SELECT Ahl_vwp_stages_B_S.NEXTVAL into l_stage_id
1923 FROM dual;
1924 --
1925 IF G_DEBUG='Y' THEN
1926 AHL_DEBUG_PUB.debug( 'visit call insert stage:'||l_stage_id);
1927 END IF;
1928 /* Copy the details in the Simulation Visit */
1929 -- Invoke the table handler to create a record
1930 Ahl_VWP_Stages_Pkg.Insert_Row (
1931 X_ROWID => l_rowid,
1932 X_VISIT_ID => l_visit_id,
1933 X_STAGE_ID => l_stage_id,
1934 X_STAGE_NUM => l_stage_rec.Stage_Num,
1935 X_STAGE_NAME => l_stage_rec.Stage_Name,
1936 X_DURATION => l_stage_rec.Duration,
1937 X_OBJECT_VERSION_NUMBER => 1,
1938 X_ATTRIBUTE_CATEGORY => l_stage_rec.ATTRIBUTE_CATEGORY,
1939 X_ATTRIBUTE1 => l_stage_rec.ATTRIBUTE1,
1940 X_ATTRIBUTE2 => l_stage_rec.ATTRIBUTE2,
1941 X_ATTRIBUTE3 => l_stage_rec.ATTRIBUTE3,
1942 X_ATTRIBUTE4 => l_stage_rec.ATTRIBUTE4,
1943 X_ATTRIBUTE5 => l_stage_rec.ATTRIBUTE5,
1944 X_ATTRIBUTE6 => l_stage_rec.ATTRIBUTE6,
1945 X_ATTRIBUTE7 => l_stage_rec.ATTRIBUTE7,
1946 X_ATTRIBUTE8 => l_stage_rec.ATTRIBUTE8,
1947 X_ATTRIBUTE9 => l_stage_rec.ATTRIBUTE9 ,
1948 X_ATTRIBUTE10 => l_stage_rec.ATTRIBUTE10,
1949 X_ATTRIBUTE11 => l_stage_rec.ATTRIBUTE11,
1950 X_ATTRIBUTE12 => l_stage_rec.ATTRIBUTE12,
1951 X_ATTRIBUTE13 => l_stage_rec.ATTRIBUTE13,
1952 X_ATTRIBUTE14 => l_stage_rec.ATTRIBUTE14,
1953 X_ATTRIBUTE15 => l_stage_rec.ATTRIBUTE15,
1954 X_CREATION_DATE => SYSDATE,
1955 X_CREATED_BY => Fnd_Global.USER_ID,
1956 X_LAST_UPDATE_DATE => SYSDATE,
1957 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1958 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1959
1960 IF G_DEBUG='Y' THEN
1961 AHL_DEBUG_PUB.Debug( l_full_name ||': Visit ID =' || l_visit_id);
1962 AHL_DEBUG_PUB.Debug( l_full_name ||': Stage Number =' ||l_stage_rec.Stage_Num);
1963 END IF;
1964 END LOOP;
1965 CLOSE Get_stages_cur;
1966 --mpothuku End
1967
1968 /* To find if the visit belongs to the primary plan/simulation plan */
1969 l_primary_visit_check := null;
1970 OPEN check_primary_visit(l_pvisit_id);
1971 FETCH check_primary_visit into l_primary_visit_check;
1972 CLOSE check_primary_visit;
1973
1974 IF G_DEBUG='Y' THEN
1975 AHL_DEBUG_PUB.debug( 'visit id before tasks:'||l_pvisit_id);
1976 END IF;
1977 --Copy the corresponding tasks
1978 OPEN get_visit_task_cur(l_pvisit_id);
1979 LOOP
1980 IF G_DEBUG='Y' THEN
1981 AHL_DEBUG_PUB.debug( 'inside loop task num:'||l_visit_task_rec.visit_task_number);
1982 END IF;
1983 FETCH get_visit_task_cur INTO l_visit_task_rec;
1984 EXIT WHEN get_visit_task_cur%NOTFOUND;
1985 -- Get visit task id
1986 SELECT Ahl_Visit_Tasks_B_S.NEXTVAL INTO
1987 l_visit_task_id FROM dual;
1988 --
1989 IF G_DEBUG='Y' THEN
1990 AHL_DEBUG_PUB.debug( 'visit call insert task:'||l_visit_task_id);
1991 END IF;
1992
1993 /* Added by mpothuku on 01/20/05 to Check if the UE is associated with any of the visits in the plan */
1994 IF(l_visit_task_rec.task_type_code = 'PLANNED' and l_visit_task_rec.unit_effectivity_id IS NOT NULL) THEN
1995
1996 OPEN chk_unit_effectivities (l_visit_task_rec.unit_effectivity_id,l_simulation_plan_id,l_visit_id);
1997 FETCH chk_unit_effectivities INTO l_visit_number,l_asso_prim_visit_id;
1998 IF (chk_unit_effectivities%FOUND) THEN
1999 CLOSE chk_unit_effectivities;
2000
2001 -- ERROR MESSAGE
2002
2003 /*
2004 AnRaj: Added for fixing the performance issues logged in bug#:4919576
2005 Split the query to select MR and SR details seperately
2006 */
2007 /*
2008 OPEN c_ue_details (l_visit_task_rec.unit_effectivity_id);
2009 FETCH c_ue_details INTO l_ue_details_rec;
2010 CLOSE c_ue_details;
2011 */
2012 -- Get the UE's SR and MR details
2013 OPEN c_ue_mr_sr_id(l_visit_task_rec.unit_effectivity_id);
2014 FETCH c_ue_mr_sr_id INTO ue_mr_sr_rec;
2015 CLOSE c_ue_mr_sr_id;
2016
2017 -- If the UE corresponds to a SR
2018 IF ue_mr_sr_rec.cs_incident_id IS NOT NULL THEN
2019 OPEN c_ue_sr_details(ue_mr_sr_rec.cs_incident_id,ue_mr_sr_rec.csi_item_instance_id);
2020 FETCH c_ue_sr_details INTO ue_sr_details_rec;
2021 CLOSE c_ue_sr_details;
2022
2023 Fnd_Message.SET_NAME('AHL','AHL_LTP_SIM_VISIT_UNIT_FOUND');
2024 Fnd_Message.SET_TOKEN('UE_TITLE', ue_sr_details_rec.ue_title);
2025 Fnd_Message.SET_TOKEN('ITEM_NUMBER', ue_sr_details_rec.part_number);
2026 Fnd_Message.SET_TOKEN('SERIAL_NUMBER', ue_sr_details_rec.serial_number);
2027 Fnd_Message.SET_TOKEN('MR_TITLE', ue_sr_details_rec.mr_title);
2028 ELSE
2029 -- Else if UE corresponds to MR
2030 OPEN c_ue_mr_details(ue_mr_sr_rec.mr_header_id,ue_mr_sr_rec.csi_item_instance_id);
2031 FETCH c_ue_mr_details INTO ue_mr_details_rec;
2032 CLOSE c_ue_mr_details;
2033
2034 Fnd_Message.SET_NAME('AHL','AHL_LTP_SIM_VISIT_UNIT_FOUND');
2035 Fnd_Message.SET_TOKEN('UE_TITLE', ue_mr_details_rec.ue_title);
2036 Fnd_Message.SET_TOKEN('ITEM_NUMBER', ue_mr_details_rec.part_number);
2037 Fnd_Message.SET_TOKEN('SERIAL_NUMBER', ue_mr_details_rec.serial_number);
2038 Fnd_Message.SET_TOKEN('MR_TITLE', ue_mr_details_rec.mr_title);
2039 END IF;
2040
2041 OPEN get_visit_number (l_pvisit_id);
2042 FETCH get_visit_number INTO l_primary_visit_number;
2043 CLOSE get_visit_number;
2044
2045 OPEN get_visit_number (l_asso_prim_visit_id);
2046 FETCH get_visit_number INTO l_asso_prim_visit_number;
2047 CLOSE get_visit_number;
2048
2049
2050 x_return_status := Fnd_Api.g_ret_sts_error;
2051
2052 /*
2053 Fnd_Message.SET_NAME('AHL','AHL_LTP_SIM_VISIT_UNIT_FOUND');
2054 Fnd_Message.SET_TOKEN('UE_TITLE', l_ue_details_rec.ue_title);
2055 Fnd_Message.SET_TOKEN('ITEM_NUMBER', l_ue_details_rec.part_number);
2056 Fnd_Message.SET_TOKEN('SERIAL_NUMBER', l_ue_details_rec.serial_number);
2057 Fnd_Message.SET_TOKEN('MR_TITLE', l_ue_details_rec.mr_title);
2058 */
2059 /*
2060 AnRaj: End of Fix bug#:4919576
2061 */
2062
2063
2064 Fnd_Message.SET_TOKEN('VISIT1', l_primary_visit_number);
2065 Fnd_Message.SET_TOKEN('VISIT2', l_asso_prim_visit_number);
2066 Fnd_Msg_Pub.ADD;
2067 RAISE Fnd_Api.G_EXC_ERROR;
2068 ELSE
2069 CLOSE chk_unit_effectivities;
2070 END IF;
2071 END IF;
2072
2073 -- Call to create task
2074 IF(l_primary_visit_check IS NOT NULL) THEN
2075 l_primary_visit_task_id := l_visit_task_rec.visit_task_id;
2076 ELSE
2077 l_primary_visit_task_id := l_visit_task_rec.primary_visit_task_id;
2078 END IF;
2079 Ahl_Visit_Tasks_Pkg.INSERT_ROW (
2080 X_ROWID => l_rowid,
2081 X_VISIT_TASK_ID => l_visit_task_id,
2082 X_VISIT_TASK_NUMBER => l_visit_task_rec.visit_task_number,
2083 X_OBJECT_VERSION_NUMBER => 1,
2084 X_VISIT_ID => l_visit_id,
2085 X_PROJECT_TASK_ID => l_visit_task_rec.project_task_id,
2086 X_COST_PARENT_ID => null,--l_visit_task_rec.cost_parent_id,
2087 X_MR_ROUTE_ID => l_visit_task_rec.mr_route_id,
2088 X_MR_ID => l_visit_task_rec.mr_id,
2089 X_DURATION => l_visit_task_rec.duration,
2090 X_UNIT_EFFECTIVITY_ID => l_visit_task_rec.unit_effectivity_id,
2091 X_START_FROM_HOUR => l_visit_task_rec.start_from_hour,
2092 X_INVENTORY_ITEM_ID => l_visit_task_rec.inventory_item_id,
2093 X_ITEM_ORGANIZATION_ID => l_visit_task_rec.item_organization_id,
2094 X_INSTANCE_ID => l_visit_task_rec.instance_id,
2095 X_PRIMARY_VISIT_TASK_ID => l_primary_visit_task_id,
2096 X_SUMMARY_TASK_FLAG => l_visit_task_rec.summary_task_flag,
2097 X_ORIGINATING_TASK_ID => null,--l_visit_task_rec.originating_task_id,
2098 X_SERVICE_REQUEST_ID => l_visit_task_rec.service_request_id,
2099 X_DEPARTMENT_ID => l_visit_task_rec.department_id,
2100 X_TASK_TYPE_CODE => l_visit_task_rec.task_type_code,
2101 X_PRICE_LIST_ID => l_visit_task_rec.price_list_id,
2102 X_STATUS_CODE => l_visit_task_rec.status_code,
2103 X_ESTIMATED_PRICE => l_visit_task_rec.estimated_price,
2104 X_ACTUAL_PRICE => l_visit_task_rec.actual_price,
2105 X_ACTUAL_COST => l_visit_task_rec.actual_cost,
2106 X_STAGE_ID => null,--l_visit_task_rec.stage_id,
2107 -- Added cxcheng POST11510-- No Calculation Need for Sim---------
2108 X_START_DATE_TIME => l_visit_task_rec.start_date_time,
2109 X_END_DATE_TIME => l_visit_task_rec.end_date_time,
2110 X_ATTRIBUTE_CATEGORY => l_visit_task_rec.ATTRIBUTE_CATEGORY,
2111 X_ATTRIBUTE1 => l_visit_task_rec.ATTRIBUTE1,
2112 X_ATTRIBUTE2 => l_visit_task_rec.ATTRIBUTE2,
2113 X_ATTRIBUTE3 => l_visit_task_rec.ATTRIBUTE3,
2114 X_ATTRIBUTE4 => l_visit_task_rec.ATTRIBUTE4,
2115 X_ATTRIBUTE5 => l_visit_task_rec.ATTRIBUTE5,
2116 X_ATTRIBUTE6 => l_visit_task_rec.ATTRIBUTE6,
2117 X_ATTRIBUTE7 => l_visit_task_rec.ATTRIBUTE7,
2118 X_ATTRIBUTE8 => l_visit_task_rec.ATTRIBUTE8,
2119 X_ATTRIBUTE9 => l_visit_task_rec.ATTRIBUTE9,
2120 X_ATTRIBUTE10 => l_visit_task_rec.ATTRIBUTE10,
2121 X_ATTRIBUTE11 => l_visit_task_rec.ATTRIBUTE11,
2122 X_ATTRIBUTE12 => l_visit_task_rec.ATTRIBUTE12,
2123 X_ATTRIBUTE13 => l_visit_task_rec.ATTRIBUTE13,
2124 X_ATTRIBUTE14 => l_visit_task_rec.ATTRIBUTE14,
2125 X_ATTRIBUTE15 => l_visit_task_rec.ATTRIBUTE15,
2126 X_VISIT_TASK_NAME => l_visit_task_rec.visit_task_name,
2127 X_DESCRIPTION => l_visit_task_rec.description,
2128 X_QUANTITY => l_visit_task_rec.quantity, -- Added by rnahata for Issue 105
2129 X_CREATION_DATE => SYSDATE,
2130 X_CREATED_BY => Fnd_Global.USER_ID,
2131 X_LAST_UPDATE_DATE => SYSDATE,
2132 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2133 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
2134
2135 END LOOP;
2136 CLOSE get_visit_task_cur;
2137
2138 --Get the tasks of the Source Visit
2139 OPEN get_visit_task_cur(l_pvisit_id);
2140 LOOP
2141 FETCH get_visit_task_cur INTO l_visit_task_rec;
2142 EXIT WHEN get_visit_task_cur%NOTFOUND;
2143 l_originating_task_id := null;
2144 l_cost_parent_id := null;
2145 l_stage_id := null;
2146
2147 IF(l_visit_task_rec.originating_task_id is not null OR l_visit_task_rec.cost_parent_id is not null
2148 OR l_visit_task_rec.stage_id is not null) THEN
2149 --Get the corresponding task record from the Simulation visit to update.
2150 OPEN c_new_task_ID(l_visit_task_rec.visit_task_id,l_visit_id);
2151 FETCH c_new_task_ID INTO l_new_task_id;
2152 CLOSE c_new_task_ID;
2153
2154 IF(l_visit_task_rec.originating_task_id is not null) THEN
2155 OPEN c_new_task_ID(l_visit_task_rec.originating_task_id,l_visit_id);
2156 FETCH c_new_task_ID INTO l_originating_task_id;
2157 CLOSE c_new_task_ID;
2158 END IF;
2159
2160 IF(l_visit_task_rec.cost_parent_id is not null) THEN
2161 OPEN c_new_task_ID(l_visit_task_rec.cost_parent_id,l_visit_id);
2162 FETCH c_new_task_ID INTO l_cost_parent_id;
2163 CLOSE c_new_task_ID;
2164 END IF;
2165
2166 IF(l_visit_task_rec.stage_id is not null) THEN
2167 OPEN c_new_stage_id(l_visit_task_rec.stage_id,l_visit_id);
2168 FETCH c_new_stage_id INTO l_stage_id;
2169 CLOSE c_new_stage_id;
2170 END IF;
2171
2172 UPDATE AHL_VISIT_TASKS_B SET
2173 cost_parent_id = l_cost_parent_id,
2174 originating_task_id = l_originating_task_id,
2175 stage_id = l_stage_id
2176 where visit_task_id = l_new_task_id;
2177
2178 END IF;
2179 END LOOP;
2180 CLOSE get_visit_task_cur;
2181 -- Added by mpothuku on 12/27/04 to copy task links
2182 -- Copy task links from originating visit
2183
2184 OPEN c_visit_task_links(l_pvisit_id);
2185 LOOP
2186 FETCH c_visit_task_links INTO l_task_link_rec;
2187 EXIT WHEN c_visit_task_links%NOTFOUND;
2188
2189 -- Find corresponding task id in new visit
2190 OPEN c_new_task_ID(l_task_link_rec.visit_task_id,l_visit_id);
2191 FETCH c_new_task_ID INTO l_new_task_id;
2192 CLOSE c_new_task_ID;
2193
2194 OPEN c_new_task_ID(l_task_link_rec.parent_task_id,l_visit_id);
2195 FETCH c_new_task_ID INTO l_new_parent_task_id;
2196 CLOSE c_new_task_ID;
2197
2198 -- Create task link
2199 INSERT INTO AHL_TASK_LINKS
2200 (
2201 TASK_LINK_ID,
2202 OBJECT_VERSION_NUMBER,
2203 LAST_UPDATE_DATE,
2204 LAST_UPDATED_BY,
2205 CREATION_DATE,
2206 CREATED_BY,
2207 LAST_UPDATE_LOGIN,
2208 VISIT_TASK_ID,
2209 PARENT_TASK_ID,
2210 --SECURITY_GROUP_ID,
2211 ATTRIBUTE_CATEGORY,
2212 ATTRIBUTE1,
2213 ATTRIBUTE2,
2214 ATTRIBUTE3,
2215 ATTRIBUTE4,
2216 ATTRIBUTE5,
2217 ATTRIBUTE6,
2218 ATTRIBUTE7,
2219 ATTRIBUTE8,
2220 ATTRIBUTE9,
2221 ATTRIBUTE10,
2222 ATTRIBUTE11,
2223 ATTRIBUTE12,
2224 ATTRIBUTE13,
2225 ATTRIBUTE14,
2226 ATTRIBUTE15
2227 )
2228 values
2229 (
2230 ahl_task_links_s.nextval,
2231 1,
2232 SYSDATE,
2233 Fnd_Global.USER_ID,
2234 SYSDATE,
2235 Fnd_Global.USER_ID,
2236 Fnd_Global.USER_ID,
2237 l_new_task_id,
2238 l_new_parent_task_id,
2239 --l_task_link_rec.SECURITY_GROUP_ID,
2240 l_task_link_rec.ATTRIBUTE_CATEGORY,
2241 l_task_link_rec.ATTRIBUTE1,
2242 l_task_link_rec.ATTRIBUTE2,
2243 l_task_link_rec.ATTRIBUTE3,
2244 l_task_link_rec.ATTRIBUTE4,
2245 l_task_link_rec.ATTRIBUTE5,
2246 l_task_link_rec.ATTRIBUTE6,
2247 l_task_link_rec.ATTRIBUTE7,
2248 l_task_link_rec.ATTRIBUTE8,
2249 l_task_link_rec.ATTRIBUTE9,
2250 l_task_link_rec.ATTRIBUTE10,
2251 l_task_link_rec.ATTRIBUTE11,
2252 l_task_link_rec.ATTRIBUTE12,
2253 l_task_link_rec.ATTRIBUTE13,
2254 l_task_link_rec.ATTRIBUTE14,
2255 l_task_link_rec.ATTRIBUTE15
2256 );
2257 END LOOP;
2258 CLOSE c_visit_task_links;
2259 --mpothuku End
2260
2261 --Copy any space assignments
2262 OPEN Get_space_Assign_cur(l_pvisit_id);
2263 LOOP
2264 FETCH Get_space_Assign_cur INTO l_space_id,l_space_assignment_id;
2265 EXIT WHEN Get_space_Assign_cur%NOTFOUND;
2266 IF Get_space_Assign_cur%FOUND THEN
2267 --Create record in space assignments with new visit id
2268 --Get space assignment id
2269 SELECT AHL_SPACE_ASSIGNMENTS_S.NEXTVAL INTO l_space_assignment_id
2270 FROM dual;
2271 --insert the record
2272 INSERT INTO AHL_SPACE_ASSIGNMENTS
2273 (
2274 SPACE_ASSIGNMENT_ID,
2275 SPACE_ID,
2276 VISIT_ID,
2277 OBJECT_VERSION_NUMBER,
2278 ATTRIBUTE_CATEGORY,
2279 ATTRIBUTE1,
2280 ATTRIBUTE2,
2281 ATTRIBUTE3,
2282 ATTRIBUTE4,
2283 ATTRIBUTE5,
2284 ATTRIBUTE6,
2285 ATTRIBUTE7,
2286 ATTRIBUTE8,
2287 ATTRIBUTE9,
2288 ATTRIBUTE10,
2289 ATTRIBUTE11,
2290 ATTRIBUTE12,
2291 ATTRIBUTE13,
2292 ATTRIBUTE14,
2293 ATTRIBUTE15,
2294 LAST_UPDATE_DATE,
2295 LAST_UPDATED_BY,
2296 CREATION_DATE,
2297 CREATED_BY,
2298 LAST_UPDATE_LOGIN
2299 )
2300 VALUES
2301 (
2302 l_space_assignment_id,
2303 l_space_id,
2304 l_visit_id,
2305 1,
2306 NULL,
2307 NULL,
2308 NULL,
2309 NULL,
2310 NULL,
2311 NULL,
2312 NULL,
2313 NULL,
2314 NULL,
2315 NULL,
2316 NULL,
2317 NULL,
2318 NULL,
2319 NULL,
2320 NULL,
2321 NULL,
2322 SYSDATE,
2323 Fnd_Global.user_id,
2324 SYSDATE,
2325 Fnd_Global.user_id,
2326 Fnd_Global.login_id
2327 );
2328
2329 END IF;
2330 END LOOP;
2331 CLOSE Get_space_Assign_cur;
2332
2333 ---------------------------End of Body---------------------------------------
2334 --Standard check to count messages
2335 l_msg_count := Fnd_Msg_Pub.count_msg;
2336
2337 IF l_msg_count > 0 THEN
2338 X_msg_count := l_msg_count;
2339 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2340 RAISE Fnd_Api.G_EXC_ERROR;
2341 END IF;
2342
2343 --Standard check for commit
2344 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2345 COMMIT;
2346 END IF;
2347 -- Debug info
2348 IF G_DEBUG='Y' THEN
2349 Ahl_Debug_Pub.debug( 'End of private api Copy visits to plan','+SMPLN+');
2350 -- Check if API is called in debug mode. If yes, disable debug.
2351 Ahl_Debug_Pub.disable_debug;
2352 END IF;
2353
2354 EXCEPTION
2355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2356 ROLLBACK TO copy_visits_to_plan;
2357 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2358 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2359 p_count => x_msg_count,
2360 p_data => x_msg_data);
2361 IF G_DEBUG='Y' THEN
2362
2363 AHL_DEBUG_PUB.log_app_messages (
2364 x_msg_count, x_msg_data, 'ERROR' );
2365 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Copy Visits to plan','+SMPLN+');
2366 -- Check if API is called in debug mode. If yes, disable debug.
2367 AHL_DEBUG_PUB.disable_debug;
2368 END IF;
2369
2370 WHEN FND_API.G_EXC_ERROR THEN
2371 ROLLBACK TO copy_visits_to_plan;
2372 X_return_status := FND_API.G_RET_STS_ERROR;
2373 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2374 p_count => x_msg_count,
2375 p_data => X_msg_data);
2376 IF G_DEBUG='Y' THEN
2377
2378 -- Debug info.
2379 AHL_DEBUG_PUB.log_app_messages (
2380 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2381 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Copy Visits to plan','+SMPLN+');
2382 -- Check if API is called in debug mode. If yes, disable debug.
2383 AHL_DEBUG_PUB.disable_debug;
2384 END IF;
2385
2386 WHEN OTHERS THEN
2387 ROLLBACK TO copy_visits_to_plan;
2388 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2389 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2390 THEN
2391 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
2392 p_procedure_name => 'COPY_VISITS_TO_PLAN',
2393 p_error_text => SUBSTR(SQLERRM,1,240));
2394 END IF;
2395 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2396 p_count => x_msg_count,
2397 p_data => X_msg_data);
2398 IF G_DEBUG='Y' THEN
2399
2400 -- Debug info.
2401 AHL_DEBUG_PUB.log_app_messages (
2402 x_msg_count, x_msg_data, 'SQL ERROR' );
2403 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Copy Visits to plan','+SMPLN+');
2404 -- Check if API is called in debug mode. If yes, disable debug.
2405 AHL_DEBUG_PUB.disable_debug;
2406 END IF;
2407
2408 END Copy_Visits_To_Plan;
2409
2410
2411 --------------------------------------------------------------------
2412 -- PROCEDURE
2413 -- Remove_Visits_FR_Plan
2414 --
2415 -- PURPOSE
2416 -- Remove Visits from Simulation Plan
2417 --
2418 --
2419 -- PARAMETERS
2420 -- p_visit_rec Record representing AHL_VISITS_VL
2421 --
2422 -- NOTES
2423 --------------------------------------------------------------------
2424 PROCEDURE Remove_Visits_FR_Plan (
2425 p_api_version IN NUMBER,
2426 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2427 p_commit IN VARCHAR2 := FND_API.g_false,
2428 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2429 p_module_type IN VARCHAR2 := 'JSP',
2430 p_visit_id IN NUMBER,
2431 p_plan_id IN NUMBER,
2432 p_v_ovn IN NUMBER,
2433 x_return_status OUT NOCOPY VARCHAR2,
2434 x_msg_count OUT NOCOPY NUMBER,
2435 x_msg_data OUT NOCOPY VARCHAR2
2436 )
2437 IS
2438 --
2439 CURSOR get_visit_task_cur (c_visit_id IN NUMBER)
2440 IS
2441 SELECT *
2442 FROM ahl_visit_tasks_vl
2443 WHERE visit_id = c_visit_id;
2444 --
2445 CURSOR Check_space_cur (c_visit_id IN NUMBER)
2446 IS
2447 SELECT space_assignment_id
2448 FROM ahl_space_assignments
2449 WHERE visit_id = c_visit_id;
2450 --
2451 -- Added by mpothuku on 12/27/04 to find any task links for a task
2452 CURSOR c_links (x_id IN NUMBER) IS
2453 SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
2454 WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
2455 AND T.VISIT_TASK_ID = x_id;
2456
2457 --To check if the unplanned tasks UE is associated with any other visits other than itself before its deletion.
2458 CURSOR check_unplanned_ue_assoc(c_ue_id IN NUMBER, c_visit_id IN NUMBER) IS
2459 SELECT 'X' from ahl_visit_tasks_b where unit_effectivity_id = c_ue_id
2460 AND visit_id <> c_visit_id;
2461
2462 CURSOR check_summary_task_unplanned(c_originating_task_id IN NUMBER) IS
2463 SELECT 'X' from ahl_visit_tasks_b where
2464 originating_task_id = c_originating_task_id and task_type_code = 'UNPLANNED';
2465
2466
2467 --
2468 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_VISITS_FR_PLAN';
2469 l_api_version CONSTANT NUMBER := 1.0;
2470 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2471
2472 l_msg_count NUMBER;
2473 l_return_status VARCHAR2(1);
2474 l_msg_data VARCHAR2(2000);
2475 l_visit_task_id NUMBER;
2476 l_space_assignment_id NUMBER;
2477 l_count NUMBER;
2478 l_planned_order_flag VARCHAR2(1);
2479 l_task_rec get_visit_task_cur%ROWTYPE;
2480 l_dummy VARCHAR2(1);
2481
2482 TYPE delete_unit_effectivity_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2483 l_delete_unit_effectivity_tbl delete_unit_effectivity_tbl;
2484 ue_count NUMBER :=0 ;
2485 BEGIN
2486 --------------------Initialize ----------------------------------
2487 -- Standard Start of API savepoint
2488 SAVEPOINT remove_visits_fr_plan;
2489 -- Check if API is called in debug mode. If yes, enable debug.
2490 IF G_DEBUG='Y' THEN
2491 AHL_DEBUG_PUB.enable_debug;
2492 END IF;
2493 -- Debug info.
2494 IF G_DEBUG='Y' THEN
2495 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Remove Visits from Plan','+SMPNL+');
2496 END IF;
2497 -- Standard call to check for call compatibility.
2498 IF FND_API.to_boolean(p_init_msg_list)
2499 THEN
2500 FND_MSG_PUB.initialize;
2501 END IF;
2502 -- Initialize API return status to success
2503 x_return_status := FND_API.G_RET_STS_SUCCESS;
2504 -- Initialize message list if p_init_msg_list is set to TRUE.
2505 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2506 p_api_version,
2507 l_api_name,G_PKG_NAME)
2508 THEN
2509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2510 END IF;
2511
2512 ---------------------start API Body------------------------------------
2513 --Remove tasks
2514 OPEN get_visit_task_cur(p_visit_id);
2515 LOOP
2516 FETCH get_visit_task_cur INTO l_task_rec;
2517 EXIT WHEN get_visit_task_cur%NOTFOUND;
2518 l_visit_task_id := l_task_rec.visit_task_id;
2519
2520 /* Added by mpothuku on 12/28/04 to delete the links */
2521 -- If a task being deleted has associated Children Tasks, tasks that define it as a parent,
2522 -- the association must be removed.
2523 OPEN c_links (l_visit_task_id);
2524 FETCH c_links INTO l_count;
2525 IF l_count > 0 THEN
2526 DELETE Ahl_Task_Links
2527 WHERE VISIT_TASK_ID = l_visit_task_id
2528 OR PARENT_TASK_ID = l_visit_task_id;
2529 END IF;
2530 CLOSE c_links;
2531
2532 /* Change by mpothuku on 02/03/05 to delete the unit effectivities for Unplanned tasks before removing the association */
2533
2534 IF(l_task_rec.TASK_TYPE_CODE = 'SUMMARY' AND l_task_rec.mr_id is not null
2535 AND l_task_rec.originating_task_id is null) THEN
2536 -- Find out if the UE is associated with any other Active Visits
2537 -- Ideally if any are found they should be Simulation Visits only
2538 OPEN check_summary_task_unplanned(l_task_rec.visit_task_id);
2539 FETCH check_summary_task_unplanned into l_dummy;
2540 IF(check_summary_task_unplanned%FOUND) THEN
2541 CLOSE check_summary_task_unplanned;
2542 OPEN check_unplanned_ue_assoc(l_task_rec.UNIT_EFFECTIVITY_ID, l_task_rec.visit_id );
2543 FETCH check_unplanned_ue_assoc INTO l_dummy;
2544 IF (check_unplanned_ue_assoc%NOTFOUND) THEN
2545 CLOSE check_unplanned_ue_assoc;
2546 l_delete_unit_effectivity_tbl(ue_count) := l_task_rec.UNIT_EFFECTIVITY_ID;
2547 ue_count := ue_count + 1;
2548 ELSE
2549 CLOSE check_unplanned_ue_assoc;
2550 END IF;
2551 ELSE
2552 CLOSE check_summary_task_unplanned;
2553 END IF;
2554 END IF;
2555
2556 AHL_VISIT_TASKS_PKG.DELETE_ROW (
2557 X_VISIT_TASK_ID => l_visit_task_id);
2558
2559 END LOOP;
2560 CLOSE get_visit_task_cur;
2561
2562
2563 --Delete the unit effectivites also
2564 if(l_delete_unit_effectivity_tbl.count > 0) THEN
2565 for ue_count in 0..l_delete_unit_effectivity_tbl.count -1
2566 LOOP
2567 IF(l_delete_unit_effectivity_tbl(ue_count) is not null) THEN
2568 AHL_UMP_UNPLANNED_PVT.DELETE_UNIT_EFFECTIVITY
2569 (
2570 P_API_VERSION => p_api_version,
2571 p_init_msg_list => FND_API.G_FALSE,
2572 p_commit => FND_API.G_FALSE,
2573
2574 X_RETURN_STATUS => l_return_status,
2575 X_MSG_COUNT => l_msg_count,
2576 X_MSG_DATA => l_msg_data,
2577 P_UNIT_EFFECTIVITY_ID => l_delete_unit_effectivity_tbl(ue_count)
2578 );
2579
2580 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2581 fnd_log.string
2582 (
2583 fnd_log.level_statement,
2584 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2585 'After Calling ahl Ump Unplanned Pvt status : '|| l_return_status
2586 );
2587 END IF;
2588
2589 IF (l_msg_count > 0) OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
2590 x_return_status := FND_API.G_RET_STS_ERROR;
2591 RAISE FND_API.G_EXC_ERROR;
2592 END IF;
2593 END IF;
2594 END LOOP;
2595 END IF;
2596 --Check for any space assignments
2597 OPEN Check_space_cur(p_visit_id);
2598 LOOP
2599 FETCH Check_space_cur INTO l_space_assignment_id;
2600 EXIT WHEN Check_space_cur%NOTFOUND;
2601 IF Check_space_cur%FOUND THEN
2602 DELETE FROM ahl_space_assignments
2603 WHERE space_assignment_id = l_space_assignment_id;
2604 END IF;
2605 END LOOP;
2606 CLOSE Check_space_cur;
2607 /* Added by mpothuku on 12/28/04 to delete the links */
2608 --Remove the stages before the visit is deleted
2609 ahl_vwp_visits_stages_pvt.delete_stages
2610 (
2611 p_api_version => p_api_version,
2612 p_init_msg_list => p_init_msg_list,
2613 p_commit => p_commit,
2614 p_validation_level => p_validation_level,
2615 p_module_type => NULL,
2616 p_visit_id => p_visit_id,
2617 x_return_status => l_return_status,
2618 x_msg_count => l_msg_count,
2619 x_msg_data => l_msg_data
2620 );
2621
2622 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2623 fnd_log.string
2624 (
2625 fnd_log.level_statement,
2626 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2627 'After Calling ahl Vwp Visits stages Pvt status : '|| l_return_status
2628 );
2629 END IF;
2630
2631 /* Added by mpothuku on 02/07/05. May need this after the enhancement for Scheduling materials
2632 for Simulation Visits */
2633
2634 /*
2635 --Delete any materials that might have been scheduled if new tasks are created.
2636 -- To Check if any materials are schedueled for the visit
2637 OPEN c_Material(p_visit_id);
2638 FETCH c_Material INTO c_Material_rec;
2639
2640 IF c_Material%FOUND THEN
2641 -- Removing planned materials for the visit
2642 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
2643 (
2644 p_api_version => p_api_version,
2645 p_init_msg_list => Fnd_Api.G_FALSE,
2646 p_commit => Fnd_Api.G_FALSE,
2647 p_visit_id => p_visit_id,
2648 p_visit_task_id => NULL,
2649 p_org_id => NULL,
2650 p_start_date => NULL,
2651 p_operation_flag => 'R',
2652
2653 x_planned_order_flag => l_planned_order_flag ,
2654 x_return_status => l_return_status,
2655 x_msg_count => l_msg_count,
2656 x_msg_data => l_msg_data
2657 );
2658
2659 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2660 CLOSE c_Material;
2661 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2662 END IF;
2663
2664 END IF;
2665 CLOSE c_Material;
2666 */
2667 /* mpothuku End */
2668 -- Remove the visit as well
2669 AHL_VISITS_PKG.DELETE_ROW (
2670 X_VISIT_ID => p_visit_id);
2671
2672 ---------------------------End of Body---------------------------------------
2673 --Standard check to count messages
2674 l_msg_count := Fnd_Msg_Pub.count_msg;
2675
2676 IF l_msg_count > 0 THEN
2677 X_msg_count := l_msg_count;
2678 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2679 RAISE Fnd_Api.G_EXC_ERROR;
2680 END IF;
2681
2682 --Standard check for commit
2683 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2684 COMMIT;
2685 END IF;
2686 IF G_DEBUG='Y' THEN
2687 -- Debug info
2688 Ahl_Debug_Pub.debug( 'End of private api Remove visits from plan','+SMPLN+');
2689 -- Check if API is called in debug mode. If yes, disable debug.
2690 Ahl_Debug_Pub.disable_debug;
2691 END IF;
2692
2693 EXCEPTION
2694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2695 ROLLBACK TO remove_visits_fr_plan;
2696 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2697 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2698 p_count => x_msg_count,
2699 p_data => x_msg_data);
2700 IF G_DEBUG='Y' THEN
2701
2702 AHL_DEBUG_PUB.log_app_messages (
2703 x_msg_count, x_msg_data, 'ERROR' );
2704 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Remove Visits from plan','+SMPLN+');
2705 -- Check if API is called in debug mode. If yes, disable debug.
2706 AHL_DEBUG_PUB.disable_debug;
2707 END IF;
2708
2709 WHEN FND_API.G_EXC_ERROR THEN
2710 ROLLBACK TO remove_visits_fr_plan;
2711 X_return_status := FND_API.G_RET_STS_ERROR;
2712 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2713 p_count => x_msg_count,
2714 p_data => X_msg_data);
2715 IF G_DEBUG='Y' THEN
2716
2717 -- Debug info.
2718 AHL_DEBUG_PUB.log_app_messages (
2719 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2720 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Remove Visits from plan','+SMPLN+');
2721 -- Check if API is called in debug mode. If yes, disable debug.
2722 AHL_DEBUG_PUB.disable_debug;
2723 END IF;
2724
2725 WHEN OTHERS THEN
2726 ROLLBACK TO remove_visits_fr_plan;
2727 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2728 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2729 THEN
2730 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
2731 p_procedure_name => 'REMOVE_VISITS_FR_PLAN',
2732 p_error_text => SUBSTR(SQLERRM,1,240));
2733 END IF;
2734 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2735 p_count => x_msg_count,
2736 p_data => X_msg_data);
2737 IF G_DEBUG='Y' THEN
2738
2739 -- Debug info.
2740 AHL_DEBUG_PUB.log_app_messages (
2741 x_msg_count, x_msg_data, 'SQL ERROR' );
2742 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Remove Visits from plan','+SMPLN+');
2743 -- Check if API is called in debug mode. If yes, disable debug.
2744 AHL_DEBUG_PUB.disable_debug;
2745 END IF;
2746
2747 END Remove_Visits_FR_Plan;
2748
2749
2750 --------------------------------------------------------------------
2751 -- PROCEDURE
2752 -- Toggle_Simulation_Delete
2753 --
2754 -- PURPOSE
2755 -- Toggle Simulation Delete/Undelete
2756 --
2757 -- PARAMETERS
2758 -- p_visit_id : Visit Id
2759 -- p_visit_object_version_number : Visit Object Version Number
2760 --
2761 -- NOTES
2762 --------------------------------------------------------------------
2763 PROCEDURE Toggle_Simulation_Delete (
2764 p_api_version IN NUMBER,
2765 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2766 p_commit IN VARCHAR2 := FND_API.g_false,
2767 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2768 p_module_type IN VARCHAR2 := 'JSP',
2769 p_visit_id IN NUMBER,
2770 p_visit_object_version_number IN NUMBER,
2771 x_return_status OUT NOCOPY VARCHAR2,
2772 x_msg_count OUT NOCOPY NUMBER,
2773 x_msg_data OUT NOCOPY VARCHAR2
2774 )
2775 IS
2776 CURSOR visit_detail_cur(c_visit_id IN NUMBER)
2777 IS
2778 SELECT VISIT_ID,
2779 OBJECT_VERSION_NUMBER,
2780 SIMULATION_DELETE_FLAG
2781 FROM AHL_VISITS_VL
2782 WHERE VISIT_ID = c_visit_id;
2783
2784 l_api_name CONSTANT VARCHAR2(30) := 'TOGGLE_SIMULATION_DELETE';
2785 l_api_version CONSTANT NUMBER := 1.0;
2786 l_msg_count NUMBER;
2787 l_return_status VARCHAR2(1);
2788 l_msg_data VARCHAR2(2000);
2789 l_visit_id NUMBER;
2790 l_object_version_number NUMBER;
2791 l_simulation_delete_flag VARCHAR2(1);
2792 BEGIN
2793 --------------------Initialize ----------------------------------
2794 -- Standard Start of API savepoint
2795 SAVEPOINT toggle_simulation_delete;
2796 -- Check if API is called in debug mode. If yes, enable debug.
2797 IF G_DEBUG='Y' THEN
2798 AHL_DEBUG_PUB.enable_debug;
2799 -- Debug info.
2800 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.toggle simulation delete','+SMPNL+');
2801 END IF;
2802 -- Standard call to check for call compatibility.
2803 IF FND_API.to_boolean(p_init_msg_list)
2804 THEN
2805 FND_MSG_PUB.initialize;
2806 END IF;
2807 -- Initialize API return status to success
2808 x_return_status := FND_API.G_RET_STS_SUCCESS;
2809 -- Initialize message list if p_init_msg_list is set to TRUE.
2810 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2811 p_api_version,
2812 l_api_name,G_PKG_NAME)
2813 THEN
2814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2815 END IF;
2816
2817 ---------------------start API Body----------------------------------------
2818 -- Check for Visit ID
2819 IF p_visit_id IS NOT NULL THEN
2820 OPEN visit_detail_cur(p_visit_id);
2821 FETCH visit_detail_cur INTO l_visit_id,
2822 l_object_version_number,
2823 l_simulation_delete_flag;
2824 IF visit_detail_cur%NOTFOUND THEN
2825 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_RECORD');
2826 Fnd_Msg_Pub.ADD;
2827 END IF;
2828 CLOSE visit_detail_cur;
2829 END IF;
2830 --Check for object version number
2831 IF p_visit_object_version_number <> l_object_version_number THEN
2832 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_RECORD');
2833 Fnd_Msg_Pub.ADD;
2834 END IF;
2835 --
2836 IF l_simulation_delete_flag = 'N' THEN
2837 UPDATE AHL_VISITS_B
2838 SET SIMULATION_DELETE_FLAG = 'Y',
2839 -- mpothuku start on 12/22/04
2840 OBJECT_VERSION_NUMBER = l_object_version_number + 1
2841 -- mpothuku End
2842 WHERE visit_id = p_visit_id;
2843 ELSE
2844 UPDATE AHL_VISITS_B
2845 SET SIMULATION_DELETE_FLAG = 'N',
2846 --Added by mpothuku on 12/22/04
2847 OBJECT_VERSION_NUMBER = l_object_version_number + 1
2848 -- mpothuku End
2849 WHERE visit_id = p_visit_id;
2850 END IF;
2851 ---------------------------End of Body---------------------------------------
2852 --Standard check to count messages
2853 l_msg_count := Fnd_Msg_Pub.count_msg;
2854
2855 IF l_msg_count > 0 THEN
2856 X_msg_count := l_msg_count;
2857 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2858 RAISE Fnd_Api.G_EXC_ERROR;
2859 END IF;
2860
2861 --Standard check for commit
2862 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2863 COMMIT;
2864 END IF;
2865 IF G_DEBUG='Y' THEN
2866 -- Debug info
2867 Ahl_Debug_Pub.debug( 'End of private api Toggle Simulation Delete','+SMPLN+');
2868 -- Check if API is called in debug mode. If yes, disable debug.
2869 Ahl_Debug_Pub.disable_debug;
2870 END IF;
2871
2872 EXCEPTION
2873 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2874 ROLLBACK TO toggle_simulation_delete;
2875 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2876 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2877 p_count => x_msg_count,
2878 p_data => x_msg_data);
2879 IF G_DEBUG='Y' THEN
2880
2881 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
2882 AHL_DEBUG_PUB.log_app_messages (
2883 x_msg_count, x_msg_data, 'ERROR' );
2884 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Toggle Simulation Delete','+SMPLN+');
2885 END IF;
2886 -- Check if API is called in debug mode. If yes, disable debug.
2887 AHL_DEBUG_PUB.disable_debug;
2888 END IF;
2889
2890 WHEN FND_API.G_EXC_ERROR THEN
2891 ROLLBACK TO toggle_simulation_delete;
2892 X_return_status := FND_API.G_RET_STS_ERROR;
2893 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2894 p_count => x_msg_count,
2895 p_data => X_msg_data);
2896 IF G_DEBUG='Y' THEN
2897
2898 -- Debug info.
2899 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
2900 AHL_DEBUG_PUB.log_app_messages (
2901 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2902 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Toggle Simulation Delete','+SMPLN+');
2903 END IF;
2904 -- Check if API is called in debug mode. If yes, disable debug.
2905 AHL_DEBUG_PUB.disable_debug;
2906 END IF;
2907
2908 WHEN OTHERS THEN
2909 ROLLBACK TO toggle_simulation_delete;
2910 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2911 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2912 THEN
2913 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
2914 p_procedure_name => 'TOGGLE_SIMULATION_DELETE',
2915 p_error_text => SUBSTR(SQLERRM,1,240));
2916 END IF;
2917 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2918 p_count => x_msg_count,
2919 p_data => X_msg_data);
2920 IF G_DEBUG='Y' THEN
2921
2922 -- Debug info.
2923 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
2924 AHL_DEBUG_PUB.log_app_messages (
2925 x_msg_count, x_msg_data, 'SQL ERROR' );
2926 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Toggle Simulation Delete','+SMPLN+');
2927 END IF;
2928 -- Check if API is called in debug mode. If yes, disable debug.
2929 AHL_DEBUG_PUB.disable_debug;
2930 END IF;
2931
2932 END Toggle_Simulation_Delete;
2933
2934 --------------------------------------------------------------------
2935 -- PROCEDURE
2936 -- Set_Plan_As_Primary
2937 --
2938 -- PURPOSE
2939 -- Set Plan As Primary
2940 --
2941 -- PARAMETERS
2942 -- p_plan_id : Simulation Plan Id
2943 -- p_object_version_number : Plan Object Version Number
2944 --
2945 -- NOTES
2946 --------------------------------------------------------------------
2947 PROCEDURE Set_Plan_As_Primary (
2948 p_api_version IN NUMBER,
2949 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2950 p_commit IN VARCHAR2 := FND_API.g_false,
2951 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2952 p_module_type IN VARCHAR2 := 'JSP',
2953 p_plan_id IN NUMBER,
2954 p_object_version_number IN NUMBER,
2955 x_return_status OUT NOCOPY VARCHAR2,
2956 x_msg_count OUT NOCOPY NUMBER,
2957 x_msg_data OUT NOCOPY VARCHAR2
2958 )
2959 IS
2960 CURSOR plan_cur (c_plan_id IN NUMBER)
2961 IS
2962 SELECT simulation_plan_id,
2963 object_version_number,
2964 primary_plan_flag
2965 FROM AHL_SIMULATION_PLANS_VL
2966 WHERE SIMULATION_PLAN_ID = c_plan_id;
2967 --
2968 CURSOR visit_detail_cur (c_plan_id IN NUMBER)
2969 IS
2970 SELECT visit_id,object_version_number
2971 FROM AHL_VISITS_VL
2972 WHERE SIMULATION_PLAN_ID = c_plan_id;
2973 --
2974 CURSOR check_visit_cur (c_plan_id IN NUMBER)
2975 IS
2976 SELECT visit_id FROM
2977 AHL_VISITS_VL
2978 WHERE SIMULATION_PLAN_ID = c_plan_id;
2979 --
2980 l_api_name CONSTANT VARCHAR2(30) := 'SET_PLAN_AS_PRIMARY';
2981 l_api_version CONSTANT NUMBER := 1.0;
2982 l_msg_count NUMBER;
2983 l_msg_data VARCHAR2(2000);
2984 l_return_status VARCHAR2(1);
2985 l_simulation_plan_id NUMBER;
2986 l_primary_plan_flag VARCHAR2(1);
2987 l_object_version_number NUMBER;
2988 l_visit_id NUMBER;
2989 l_dummy NUMBER;
2990 BEGIN
2991 --------------------Initialize ----------------------------------
2992 -- Standard Start of API savepoint
2993 SAVEPOINT set_plan_as_primary;
2994 -- Check if API is called in debug mode. If yes, enable debug.
2995 IF G_DEBUG='Y' THEN
2996 AHL_DEBUG_PUB.enable_debug;
2997 END IF;
2998 -- Debug info.
2999 IF G_DEBUG='Y' THEN
3000 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.set plan as primary','+SMPNL+');
3001 END IF;
3002 -- Standard call to check for call compatibility.
3003 IF FND_API.to_boolean(p_init_msg_list)
3004 THEN
3005 FND_MSG_PUB.initialize;
3006 END IF;
3007 -- Initialize API return status to success
3008 x_return_status := FND_API.G_RET_STS_SUCCESS;
3009 -- Initialize message list if p_init_msg_list is set to TRUE.
3010 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3011 p_api_version,
3012 l_api_name,G_PKG_NAME)
3013 THEN
3014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3015 END IF;
3016
3017 ---------------------start API Body----------------------------------------
3018 IF p_plan_id IS NULL AND p_plan_id <> FND_API.G_MISS_NUM THEN
3019 OPEN plan_cur(p_plan_id);
3020 FETCH plan_cur INTO l_simulation_plan_id,
3021 l_object_version_number,
3022 l_primary_plan_flag;
3023 IF plan_cur%NOTFOUND THEN
3024 Fnd_message.SET_NAME('AHL','AHL_LTP_RECORD_INVALID');
3025 Fnd_Msg_Pub.ADD;
3026 END IF;
3027 CLOSE plan_cur;
3028 END IF;
3029 --Check for any visits
3030 OPEN check_visit_cur(p_plan_id);
3031 FETCH check_visit_cur INTO l_dummy;
3032 IF check_visit_cur%NOTFOUND THEN
3033 Fnd_message.SET_NAME('AHL','AHL_LTP_SIMULATION_NO_VISITS');
3034 Fnd_Msg_Pub.ADD;
3035 CLOSE check_visit_cur;
3036 RAISE Fnd_Api.G_EXC_ERROR;
3037 END IF;
3038 CLOSE check_visit_cur;
3039 --
3040 --Check for Record change
3041 IF p_object_version_number <> l_object_version_number THEN
3042 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_PLAN_RECORD');
3043 Fnd_Msg_Pub.ADD;
3044 END IF;
3045 --Get all the simulated visits
3046 IF p_plan_id IS NOT NULL AND p_plan_id <> FND_API.G_MISS_NUM
3047 THEN
3048
3049 OPEN visit_detail_cur(p_plan_id);
3050 LOOP
3051 FETCH visit_detail_cur INTO l_visit_id,l_object_version_number;
3052
3053 EXIT WHEN visit_detail_cur%NOTFOUND;
3054 --Call set visit as primary
3055 Set_Visit_As_Primary
3056 ( p_api_version => p_api_version,
3057 p_init_msg_list => FND_API.G_FALSE,--p_init_msg_list,
3058 p_commit => FND_API.G_FALSE, --p_commit,
3059 p_validation_level => p_validation_level,
3060 p_module_type => p_module_type,
3061 p_visit_id => l_visit_id,
3062 p_plan_id => p_plan_id,
3063 p_object_version_number => l_object_version_number,
3064 x_return_status => l_return_status,
3065 x_msg_count => l_msg_count,
3066 x_msg_data => l_msg_data);
3067 END LOOP;
3068 CLOSE visit_detail_cur;
3069 END IF;
3070 --Remove simulation plan
3071 -------------------Call Table handler generated procedure------------
3072 AHL_SIMULATION_PLANS_PKG.DELETE_ROW (
3073 X_SIMULATION_PLAN_ID => p_plan_id);
3074 ---------------------------End of Body---------------------------------------
3075
3076 -- Changes by mpothuku end
3077
3078 --Standard check to count messages
3079 l_msg_count := Fnd_Msg_Pub.count_msg;
3080
3081 IF l_msg_count > 0 THEN
3082 X_msg_count := l_msg_count;
3083 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3084 RAISE Fnd_Api.G_EXC_ERROR;
3085 END IF;
3086
3087 --Standard check for commit
3088 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3089 COMMIT;
3090 END IF;
3091 -- Debug info
3092 IF G_DEBUG='Y' THEN
3093 Ahl_Debug_Pub.debug( 'End of private api Set Plan as Primary','+SMPLN+');
3094 -- Check if API is called in debug mode. If yes, disable debug.
3095 Ahl_Debug_Pub.disable_debug;
3096 END IF;
3097
3098 EXCEPTION
3099 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3100 ROLLBACK TO toggle_simulation_delete;
3101 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3102 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3103 p_count => x_msg_count,
3104 p_data => x_msg_data);
3105 IF G_DEBUG='Y' THEN
3106
3107 AHL_DEBUG_PUB.log_app_messages (
3108 x_msg_count, x_msg_data, 'ERROR' );
3109 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Set Plan As Primary','+SMPLN+');
3110 -- Check if API is called in debug mode. If yes, disable debug.
3111 AHL_DEBUG_PUB.disable_debug;
3112 END IF;
3113
3114 WHEN FND_API.G_EXC_ERROR THEN
3115 ROLLBACK TO set_plan_as_primary;
3116 X_return_status := FND_API.G_RET_STS_ERROR;
3117 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3118 p_count => x_msg_count,
3119 p_data => X_msg_data);
3120 IF G_DEBUG='Y' THEN
3121
3122 -- Debug info.
3123 AHL_DEBUG_PUB.log_app_messages (
3124 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3125 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. Set Plan as Primary','+SMPLN+');
3126 -- Check if API is called in debug mode. If yes, disable debug.
3127 AHL_DEBUG_PUB.disable_debug;
3128 END IF;
3129 WHEN OTHERS THEN
3130 ROLLBACK TO set_plan_as_primary;
3131 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3132 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3133 THEN
3134 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
3135 p_procedure_name => 'SET_PLAN_AS_PRIMARY',
3136 p_error_text => SUBSTR(SQLERRM,1,240));
3137 END IF;
3138 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3139 p_count => x_msg_count,
3140 p_data => X_msg_data);
3141
3142 -- Debug info.
3143 IF G_DEBUG='Y' THEN
3144 AHL_DEBUG_PUB.log_app_messages (
3145 x_msg_count, x_msg_data, 'SQL ERROR' );
3146 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Set Plan as Primary','+SMPLN+');
3147 END IF;
3148 -- Check if API is called in debug mode. If yes, disable debug.
3149 AHL_DEBUG_PUB.disable_debug;
3150 END Set_Plan_As_Primary;
3151
3152 --------------------------------------------------------------------
3153 -- PROCEDURE
3154 -- Set_Visit_As_Primary
3155 --
3156 -- PURPOSE
3157 -- Set Visit As Primary
3158 --
3159 -- PARAMETERS
3160 -- p_visit_id : Simulation Visit Id
3161 -- p_object_version_number : Visit Object Version Number
3162 --
3163 -- NOTES
3164 --------------------------------------------------------------------
3165 PROCEDURE Set_Visit_As_Primary (
3166 p_api_version IN NUMBER,
3167 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3168 p_commit IN VARCHAR2 := FND_API.g_false,
3169 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
3170 p_module_type IN VARCHAR2 := 'JSP',
3171 p_visit_id IN NUMBER,
3172 p_plan_id IN NUMBER,
3173 p_object_version_number IN NUMBER,
3174 x_return_status OUT NOCOPY VARCHAR2,
3175 x_msg_count OUT NOCOPY NUMBER,
3176 x_msg_data OUT NOCOPY VARCHAR2
3177 )
3178 IS
3179 CURSOR simul_visit_cur (c_visit_id IN NUMBER,
3180 c_plan_id IN NUMBER)
3181 IS
3182 SELECT VISIT_ID,
3183 VISIT_NUMBER,
3184 VISIT_TYPE_CODE,
3185 SIMULATION_PLAN_ID,
3186 ITEM_INSTANCE_ID,
3187 ITEM_ORGANIZATION_ID,
3188 INVENTORY_ITEM_ID,
3189 ASSO_PRIMARY_VISIT_ID,
3190 SIMULATION_DELETE_FLAG,
3191 TEMPLATE_FLAG,
3192 OUT_OF_SYNC_FLAG,
3193 PROJECT_FLAG,
3194 PROJECT_ID,
3195 SERVICE_REQUEST_ID,
3196 SPACE_CATEGORY_CODE,
3197 SCHEDULE_DESIGNATOR,
3198 PRIORITY_CODE,
3199 PROJECT_TEMPLATE_ID,
3200 ATTRIBUTE_CATEGORY,
3201 ATTRIBUTE1,
3202 ATTRIBUTE2,
3203 ATTRIBUTE3,
3204 ATTRIBUTE4,
3205 ATTRIBUTE5,
3206 ATTRIBUTE6,
3207 ATTRIBUTE7,
3208 ATTRIBUTE8,
3209 ATTRIBUTE9,
3210 ATTRIBUTE10,
3211 ATTRIBUTE11,
3212 ATTRIBUTE12,
3213 ATTRIBUTE13,
3214 ATTRIBUTE14,
3215 ATTRIBUTE15,
3216 OBJECT_VERSION_NUMBER,
3217 ORGANIZATION_ID,
3218 DEPARTMENT_ID,
3219 STATUS_CODE,
3220 START_DATE_TIME,
3221 CLOSE_DATE_TIME,
3222 PRICE_LIST_ID,
3223 ESTIMATED_PRICE,
3224 ACTUAL_PRICE,
3225 OUTSIDE_PARTY_FLAG,
3226 ANY_TASK_CHG_FLAG,
3227 UNIT_SCHEDULE_ID,
3228 VISIT_NAME,
3229 DESCRIPTION,
3230 LAST_UPDATE_DATE,
3231 LAST_UPDATED_BY,
3232 LAST_UPDATE_LOGIN,
3233 INV_LOCATOR_ID --Added by sowsubra
3234 FROM AHL_VISITS_VL
3235 WHERE VISIT_ID = c_visit_id
3236 AND SIMULATION_PLAN_ID = c_plan_id;
3237
3238 --
3239 CURSOR check_primary_cur (c_plan_id IN NUMBER)
3240 IS
3241 SELECT simulation_plan_id
3242 FROM AHL_SIMULATION_PLANS_VL
3243 WHERE simulation_plan_id = c_plan_id
3244 AND primary_plan_flag = 'Y';
3245
3246 --
3247 -- To get associated visit tasks
3248 CURSOR simul_visit_task_cur (c_visit_id IN NUMBER)
3249 IS
3250 SELECT
3251 ATSK.VISIT_TASK_ID,
3252 ATSK.VISIT_TASK_NUMBER,
3253 ATSK.OBJECT_VERSION_NUMBER,
3254 ATSK.VISIT_ID,
3255 ATSK.PROJECT_TASK_ID,
3256 ATSK.COST_PARENT_ID,
3257 ATSK.MR_ROUTE_ID,
3258 ATSK.MR_ID,
3259 ATSK.DURATION,
3260 ATSK.UNIT_EFFECTIVITY_ID,
3261 ATSK.VISIT_TASK_NAME,
3262 ATSK.DESCRIPTION,
3263 ATSK.START_FROM_HOUR,
3264 ATSK.INVENTORY_ITEM_ID,
3265 ATSK.ITEM_ORGANIZATION_ID,
3266 ATSK.INSTANCE_ID,
3267 ATSK.PRIMARY_VISIT_TASK_ID,
3268 ATSK.SUMMARY_TASK_FLAG,
3269 ATSK.ORIGINATING_TASK_ID,
3270 ATSK.SERVICE_REQUEST_ID,
3271 ATSK.TASK_TYPE_CODE,
3272 ATSK.DEPARTMENT_ID,
3273 ATSK.PRICE_LIST_ID,
3274 ATSK.STATUS_CODE,
3275 ATSK.ACTUAL_COST,
3276 ATSK.ESTIMATED_PRICE,
3277 ATSK.ACTUAL_PRICE,
3278 ATSK.STAGE_ID,
3279 ATSK.START_DATE_TIME,
3280 ATSK.END_DATE_TIME,
3281 ATSK.QUANTITY, -- Added by rnahata for Issue 105
3282 ATSK.ATTRIBUTE_CATEGORY,
3283 ATSK.ATTRIBUTE1,
3284 ATSK.ATTRIBUTE2,
3285 ATSK.ATTRIBUTE3,
3286 ATSK.ATTRIBUTE4,
3287 ATSK.ATTRIBUTE5,
3288 ATSK.ATTRIBUTE6,
3289 ATSK.ATTRIBUTE7,
3290 ATSK.ATTRIBUTE8,
3291 ATSK.ATTRIBUTE9,
3292 ATSK.ATTRIBUTE10,
3293 ATSK.ATTRIBUTE11,
3294 ATSK.ATTRIBUTE12,
3295 ATSK.ATTRIBUTE13,
3296 ATSK.ATTRIBUTE14,
3297 ATSK.ATTRIBUTE15,
3298 MTSB.CONCATENATED_SEGMENTS ITEM_NAME,
3299 CSIS.SERIAL_NUMBER SERIAL_NUMBER
3300 FROM ahl_visit_tasks_vl ATSK,
3301 MTL_SYSTEM_ITEMS_B_KFV MTSB,
3302 CSI_ITEM_INSTANCES CSIS
3303 WHERE visit_id = c_visit_id and
3304 ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+) and
3305 ATSK. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND
3306 ATSK. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+) AND
3307 STATUS_CODE <> 'DELETED';
3308 -- Check for tasks exist in primary visit
3309 CURSOR check_visit_task_cur (c_visit_id IN NUMBER,
3310 c_visit_task_id IN NUMBER)
3311 IS
3312 SELECT visit_task_id
3313 FROM ahl_visit_tasks_vl
3314 WHERE visit_id = c_visit_id
3315 AND visit_task_id = c_visit_task_id
3316 AND status_code <> 'DELETED';
3317
3318 -- Check for tasks exist in primary visit tasks which are not in simulation visit
3319 CURSOR check_exist_visit_task_cur (c_visit_id IN NUMBER)
3320 IS
3321 SELECT visit_task_id
3322 FROM ahl_visit_tasks_vl
3323 WHERE visit_id = c_visit_id;
3324
3325 -- Check for tasks exist in primary visit tasks which are not in simulation visit
3326 CURSOR check_prim_visit_task_cur (c_visit_id IN NUMBER,
3327 c_visit_task_id IN NUMBER)
3328 IS
3329 SELECT primary_visit_task_id
3330 FROM ahl_visit_tasks_vl
3331 WHERE visit_id = c_visit_id
3332 AND primary_visit_task_id = c_visit_task_id
3333 AND status_code <> 'DELETED' ;
3334
3335 --Get tasks that needs deletion
3336 CURSOR get_tasks_delete_csr(x_id IN NUMBER)
3337 IS
3338 SELECT visit_task_id,object_version_number,visit_task_number
3339 FROM Ahl_Visit_Tasks_VL
3340 WHERE VISIT_ID = x_id AND NVL(STATUS_CODE,'X') <> 'DELETED'
3341 AND ((TASK_TYPE_CODE = 'SUMMARY' AND ORIGINATING_TASK_ID IS NULL)
3342 OR TASK_TYPE_CODE = 'UNASSOCIATED'
3343 OR (TASK_TYPE_CODE = 'SUMMARY' AND MR_ID IS NULL));
3344
3345 --Check for space assignments
3346 CURSOR check_space_cur (c_visit_id IN NUMBER)
3347 IS
3348 SELECT space_assignment_id
3349 FROM ahl_space_assignments
3350 WHERE visit_id = c_visit_id;
3351 --
3352 -- Added by mpothuku on 12/27/04
3353 -- To find any task links for a task
3354 CURSOR c_links (x_id IN NUMBER) IS
3355 SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
3356 WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
3357 AND T.VISIT_TASK_ID = x_id;
3358
3359 -- To find task link related information for a visit
3360 CURSOR c_visit_task_links(x_visit_id IN NUMBER) IS
3361 SELECT VISIT_TASK_ID ,
3362 PARENT_TASK_ID,
3363 --SECURITY_GROUP_ID,
3364 ATTRIBUTE_CATEGORY,
3365 ATTRIBUTE1,
3366 ATTRIBUTE2,
3367 ATTRIBUTE3,
3368 ATTRIBUTE4,
3369 ATTRIBUTE5,
3370 ATTRIBUTE6,
3371 ATTRIBUTE7,
3372 ATTRIBUTE8,
3373 ATTRIBUTE9,
3374 ATTRIBUTE10,
3375 ATTRIBUTE11,
3376 ATTRIBUTE12,
3377 ATTRIBUTE13,
3378 ATTRIBUTE14,
3379 ATTRIBUTE15
3380 FROM AHL_TASK_LINKS
3381 WHERE visit_task_id in ( SELECT VISIT_TASK_ID
3382 FROM AHL_VISIT_TASKS_B
3383 WHERE visit_id = x_visit_id);
3384 -- To find the coresponding task id in the new visit
3385 CURSOR c_new_task(x_visit_task_id IN NUMBER, x_new_visit_id IN NUMBER) IS
3386 SELECT b.VISIT_TASK_ID,b.VISIT_TASK_NUMBER
3387 FROM AHL_VISIT_TASKS_B a, AHL_VISIT_TASKS_B b
3388 WHERE a.visit_task_id = x_visit_task_id
3389 AND a.visit_task_number = b.visit_task_number
3390 AND b.visit_id = x_new_visit_id;
3391
3392
3393 --To get the stages from a visit
3394 CURSOR Get_stages_cur(c_visit_id IN NUMBER) IS
3395 SELECT STAGE_ID,
3396 STAGE_NUM,
3397 VISIT_ID,
3398 DURATION,
3399 OBJECT_VERSION_NUMBER,
3400 STAGE_NAME,
3401 --SECURITY_GROUP_ID
3402 ATTRIBUTE_CATEGORY,
3403 ATTRIBUTE1,
3404 ATTRIBUTE2,
3405 ATTRIBUTE3,
3406 ATTRIBUTE4,
3407 ATTRIBUTE5,
3408 ATTRIBUTE6,
3409 ATTRIBUTE7,
3410 ATTRIBUTE8,
3411 ATTRIBUTE9,
3412 ATTRIBUTE10,
3413 ATTRIBUTE11,
3414 ATTRIBUTE12,
3415 ATTRIBUTE13,
3416 ATTRIBUTE14,
3417 ATTRIBUTE15
3418 FROM ahl_vwp_stages_vl s
3419 WHERE visit_id = c_visit_id
3420 ORDER BY stage_num;
3421
3422 -- To find the coresponding Stage id in the new visit
3423 CURSOR c_new_stage(c_old_stage_id IN NUMBER, c_new_visit_id IN NUMBER) IS
3424 SELECT NewStage.Stage_Id, NewStage.Stage_Name
3425 FROM ahl_vwp_stages_vl OldStage, ahl_vwp_stages_vl NewStage
3426 WHERE OldStage.Stage_Id = c_old_stage_id
3427 AND NewStage.visit_id = c_new_visit_id
3428 AND NewStage.Stage_Num = OldStage.Stage_Num;
3429
3430 -- Added by mpothuku on 01/20/05 To find if this Unit has been planned in other visits already
3431 CURSOR chk_unit_effectivities (c_unit_id IN NUMBER,c_visit_id IN NUMBER) IS
3432 SELECT VISIT_NUMBER FROM AHL_VISITS_B ahlv ,AHL_SIMULATION_PLANS_B ahlp WHERE
3433 VISIT_ID IN (SELECT DISTINCT VISIT_ID FROM AHL_VISIT_TASKS_B WHERE
3434 Unit_Effectivity_Id = c_unit_id)
3435 and visit_id <> c_visit_id
3436 and ahlv.simulation_plan_id = ahlp.simulation_plan_id
3437 and ahlp.primary_plan_flag = 'Y'
3438 --The following condition is necessary since the summary task may already have been
3439 --added to the current visit which will have the same UE as the planned task
3440 and status_code not in ('CANCELLED','DELETED');
3441
3442 /*
3443 AnRaj: Added for fixing the performance issues logged in bug#:4919576
3444 */
3445
3446 /* CURSOR c_ue_details(c_unit_id IN NUMBER) IS
3447 select ue.title ue_title, ue.part_number, ue.serial_number, MR.title mr_title from ahl_unit_effectivities_v ue,ahl_mr_headers_v MR where MR.mr_header_id = ue.mr_header_id
3448 and ue.unit_effectivity_id = c_unit_id;
3449 */
3450
3451 CURSOR c_ue_mr_sr_id(c_unit_id IN NUMBER) IS
3452 select ue.mr_header_id, ue.cs_incident_id,ue.csi_item_instance_id
3453 from ahl_unit_effectivities_b ue
3454 where ue.unit_effectivity_id = c_unit_id;
3455 ue_mr_sr_id_rec c_ue_mr_sr_id%ROWTYPE;
3456
3457 CURSOR c_ue_mr_details(c_mr_header_id IN NUMBER,c_item_instance_id IN NUMBER) IS
3458 SELECT mr.title ue_title,
3459 mtl.concatenated_segments part_number,
3460 csi.serial_number serial_number,
3461 mr.title mr_title
3462 FROM ahl_mr_headers_vl mr,
3463 mtl_system_items_kfv mtl,
3464 csi_item_instances csi
3465 WHERE mr.mr_header_id = c_mr_header_id
3466 AND csi.instance_id = c_item_instance_id
3467 AND csi.inventory_item_id = mtl.inventory_item_id
3468 AND csi.inv_master_organization_id = mtl.organization_id ;
3469 ue_mr_details_rec c_ue_mr_details%ROWTYPE;
3470
3471 CURSOR c_ue_sr_details(cs_incident_id IN NUMBER,c_item_instance_id IN NUMBER) IS
3472 SELECT (cit.name || '-' || cs.incident_number) ue_title,
3473 mtl.concatenated_segments part_number,
3474 csi.serial_number serial_number,
3475 null mr_title
3476 FROM cs_incident_types_vl cit,
3477 cs_incidents_all_b cs,
3478 mtl_system_items_kfv mtl,
3479 csi_item_instances csi
3480 WHERE cs.incident_id = cs_incident_id
3481 AND cit.incident_type_id = cs.incident_type_id
3482 AND csi.instance_id = c_item_instance_id
3483 AND csi.inventory_item_id = mtl.inventory_item_id
3484 AND csi.inv_master_organization_id = mtl.organization_id ;
3485 ue_sr_details_rec c_ue_sr_details%ROWTYPE;
3486 /*
3487 AnRaj: End of Fix bug#:4919576
3488 */
3489
3490 CURSOR c_Visit(x_id IN NUMBER) IS
3491 SELECT *
3492 FROM Ahl_Visits_VL
3493 WHERE VISIT_ID = x_id;
3494
3495 CURSOR c_task(c_task_id IN NUMBER) IS
3496 SELECT *
3497 FROM Ahl_Visit_tasks_vl
3498 WHERE visit_task_id = c_task_id;
3499
3500 CURSOR c_new_primary_task (c_simulation_task_id IN NUMBER) IS
3501 SELECT prim.visit_task_id, prim.visit_task_number FROM
3502 ahl_visit_tasks_b prim, ahl_visit_tasks_b sim
3503 WHERE
3504 sim.visit_task_id = c_simulation_task_id and
3505 prim.visit_task_id = sim.primary_visit_task_id;
3506
3507 -- mpothuku End
3508
3509 -- anraj for fixing the issue number 207 in the CMRO Forum
3510 CURSOR c_visit_details_for_materials(c_visit_id IN NUMBER) IS
3511 SELECT organization_id,department_id,start_date_time
3512 FROM ahl_visits_vl
3513 WHERE VISIT_ID = c_visit_id;
3514
3515 /*Added by sowsubra*/
3516 CURSOR c_validate_subinv_loc_dtl(p_inv_locator_id IN NUMBER, p_org_id IN NUMBER) IS
3517 SELECT subinventory_code, CONCATENATED_SEGMENTS
3518 FROM mtl_item_locations_kfv
3519 WHERE inventory_location_id = p_inv_locator_id
3520 -- jaramana on Feb 14, 2008 for bug 6819370
3521 -- Removed null check on segment19 and segment20
3522 AND organization_id = p_org_id;
3523
3524 /*Added by sowsubra*/
3525 CURSOR c_get_default_loc_dtl(p_org_id IN NUMBER, p_dept_id IN NUMBER) IS
3526 SELECT ds.inv_locator_id, mtl.subinventory_code, mtl.CONCATENATED_SEGMENTS
3527 FROM ahl_department_shifts_v ds, hr_organization_units hou, mtl_item_locations_kfv mtl
3528 WHERE hou.organization_id = p_org_id
3529 AND hou.name = ds.organization_name
3530 AND ds.department_id = p_dept_id
3531 AND hou.organization_id = mtl.organization_id
3532 AND ds.inv_locator_id = mtl.inventory_location_id;
3533
3534 l_visit_details_for_materials c_visit_details_for_materials%ROWTYPE;
3535
3536 l_api_name CONSTANT VARCHAR2(30) := 'SET_VISIT_AS_PRIMARY';
3537 l_api_version CONSTANT NUMBER := 1.0;
3538 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3539
3540 l_msg_count NUMBER;
3541 l_msg_data VARCHAR2(2000);
3542 l_return_status VARCHAR2(1);
3543 l_rowid VARCHAR2(30);
3544 l_simulation_plan_id NUMBER;
3545 l_primary_plan_flag VARCHAR2(1);
3546 l_s_object_number NUMBER;
3547 l_simul_visit_rec simul_visit_cur%ROWTYPE;
3548 l_primary_visit_id NUMBER;
3549 l_primary_plan_id NUMBER;
3550 l_primary_visit_number NUMBER;
3551 l_unit_effectivity_id NUMBER;
3552 l_primary_visit_task_id NUMBER;
3553 l_visit_task_id NUMBER;
3554 l_prim_visit_task_id NUMBER;
3555 l_simul_visit_task_rec simul_visit_task_cur%ROWTYPE;
3556 l_exist_prim_visit_task_id NUMBER;
3557 l_sim_prim_visit_task_id NUMBER;
3558 l_space_assignment_id NUMBER;
3559 l_count NUMBER;
3560 l_new_parent_task_id NUMBER;
3561 l_new_task_id NUMBER;
3562 l_new_task_number NUMBER;
3563 l_new_stage_id NUMBER;
3564 l_stage_rec Get_stages_cur%ROWTYPE;
3565 l_visit_number NUMBER;
3566 -- l_ue_details_rec c_ue_details%ROWTYPE;
3567 l_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
3568 l_visit_count NUMBER := 0;
3569 l_prim_visit_rec AHL_VWP_VISITS_PVT.Visit_Rec_Type;
3570 l_prim_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
3571 c_visit_rec c_Visit%ROWTYPE;
3572 l_prim_visit_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type;
3573 c_task_rec c_task%ROWTYPE;
3574 l_hour NUMBER(2);
3575 l_hour_close NUMBER(2);
3576 l_minute NUMBER(2);
3577 l_minute_close NUMBER(2);
3578 l_tasks_delete_rec get_tasks_delete_csr%ROWTYPE;
3579 l_planned_order_flag VARCHAR2(1);
3580 l_new_stage_name VARCHAR2(80);
3581 l_dummy VARCHAR2(1);
3582 l_visit_task_number NUMBER;
3583 l_task_link_rec c_visit_task_links%ROWTYPE;
3584 /*Added by sowsubra*/
3585 l_locator_id NUMBER := 0;
3586
3587 BEGIN
3588 --------------------Initialize ----------------------------------
3589 -- Standard Start of API savepoint
3590 SAVEPOINT set_visit_as_primary;
3591 -- Check if API is called in debug mode. If yes, enable debug.
3592 IF G_DEBUG='Y' THEN
3593 AHL_DEBUG_PUB.enable_debug;
3594 END IF;
3595 -- Debug info.
3596 IF G_DEBUG='Y' THEN
3597 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.set visit as primary','+SMPNL+');
3598 END IF;
3599 -- Standard call to check for call compatibility.
3600 IF FND_API.to_boolean(p_init_msg_list)
3601 THEN
3602 FND_MSG_PUB.initialize;
3603 END IF;
3604 -- Initialize API return status to success
3605 x_return_status := FND_API.G_RET_STS_SUCCESS;
3606 -- Initialize message list if p_init_msg_list is set to TRUE.
3607 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3608 p_api_version,
3609 l_api_name,G_PKG_NAME)
3610 THEN
3611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3612 END IF;
3613
3614 ---------------------start API Body----------------------------------------
3615 --Check for simulation plan is primary
3616 OPEN check_primary_cur(p_plan_id);
3617 FETCH check_primary_cur INTO l_primary_plan_id;
3618 CLOSE check_primary_cur;
3619 --
3620 IF l_primary_plan_id IS NOT NULL THEN
3621 Fnd_message.SET_NAME('AHL','AHL_LTP_PRIMARY_PLAN');
3622 Fnd_Msg_Pub.ADD;
3623 RAISE Fnd_Api.G_EXC_ERROR;
3624 END IF;
3625 --Check for visit belongs to simulation plan
3626 SELECT simulation_plan_id,
3627 primary_plan_flag,
3628 object_version_number INTO
3629 l_simulation_plan_id,l_primary_plan_flag, l_s_object_number
3630 FROM AHL_SIMULATION_PLANS_VL
3631 WHERE simulation_plan_id = p_plan_id
3632 AND primary_plan_flag = 'N';
3633 --
3634 IF l_simulation_plan_id IS NULL THEN
3635 Fnd_message.SET_NAME('AHL','AHL_LTP_PRIMARY_PLAN');
3636 Fnd_Msg_Pub.ADD;
3637 RAISE Fnd_Api.G_EXC_ERROR;
3638 END IF;
3639
3640 --Check for simulation plan
3641 OPEN simul_visit_cur(p_visit_id,p_plan_id);
3642 FETCH simul_visit_cur INTO l_simul_visit_rec;
3643 IF simul_visit_cur%NOTFOUND THEN
3644 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_RECORD');
3645 Fnd_Msg_Pub.ADD;
3646 CLOSE simul_visit_cur;
3647 RAISE Fnd_Api.G_EXC_ERROR;
3648 END IF;
3649 CLOSE simul_visit_cur;
3650
3651 --Check for object version number
3652 IF p_object_version_number <> l_simul_visit_rec.object_version_number
3653 THEN
3654 Fnd_message.SET_NAME('AHL','AHL_LTP_INVALID_PLAN_RECORD');
3655 Fnd_Msg_Pub.ADD;
3656 RAISE Fnd_Api.G_EXC_ERROR;
3657 END IF;
3658 --Get corresponding primary visit
3659
3660 SELECT VISIT_ID,VISIT_NUMBER, a.SIMULATION_PLAN_ID INTO
3661 l_primary_visit_id, l_primary_visit_number,
3662 l_simulation_plan_id
3663 FROM ahl_visits_vl a, ahl_simulation_plans_vl b
3664 WHERE a.visit_id = l_simul_visit_rec.asso_primary_visit_id
3665 and a.simulation_plan_id = b.simulation_plan_id
3666 and b.primary_plan_flag = 'Y';
3667 IF G_DEBUG='Y' THEN
3668
3669 AHL_DEBUG_PUB.debug( 'before update id :'||l_primary_visit_id);
3670 AHL_DEBUG_PUB.debug( 'before update number:'||l_primary_visit_number);
3671 END IF;
3672
3673 --Check for simulation delete flag
3674 IF l_simul_visit_rec.simulation_delete_flag = 'Y' THEN --Remove the Primary Visit
3675 /* Modified by mpothuku on 01/25/05 to delete the primary visit if the Simulation Flag is delete */
3676 /*
3677 Fnd_message.SET_NAME('AHL','AHL_LTP_VISIT_REMOVED');
3678 Fnd_Msg_Pub.ADD;
3679 RAISE Fnd_Api.G_EXC_ERROR;
3680 */
3681 IF l_primary_visit_id IS NOT NULL THEN
3682 l_visit_tbl(l_visit_count).visit_id := l_primary_visit_id;
3683 l_visit_tbl(l_visit_count).operation_flag := 'D';
3684 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3685 fnd_log.string
3686 (
3687 fnd_log.level_statement,
3688 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3689 'Before Calling ahl Vwp Visits Pvt Process Visit Records : '|| l_visit_count
3690 );
3691 END IF;
3692 AHL_VWP_VISITS_PVT.Process_Visit
3693 (
3694 p_api_version => p_api_version,
3695 p_init_msg_list => FND_API.g_false,--p_init_msg_list,
3696 p_commit => FND_API.g_false, --p_commit,
3697 p_validation_level => p_validation_level,
3698 p_module_type => p_module_type,
3699 p_x_Visit_tbl => l_visit_tbl,
3700 x_return_status => l_return_status,
3701 x_msg_count => l_msg_count,
3702 x_msg_data => l_msg_data
3703 );
3704
3705 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3706 THEN
3707 fnd_log.string
3708 (
3709 fnd_log.level_statement,
3710 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3711 'After Calling ahl Vwp Visits Pvt status : '|| l_return_status
3712 );
3713 END IF;
3714 -- Check Error Message stack.
3715 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3716 l_msg_count := FND_MSG_PUB.count_msg;
3717 IF l_msg_count > 0 THEN
3718 RAISE FND_API.G_EXC_ERROR;
3719 END IF;
3720 END IF;
3721 END IF;
3722
3723 ELSE --Modify the visit and its related atributes
3724
3725 IF G_DEBUG='Y' THEN
3726 AHL_DEBUG_PUB.debug( 'after else update id :'||l_primary_visit_id);
3727 AHL_DEBUG_PUB.debug( 'after else update number:'||l_primary_visit_number);
3728 --AHL_DEBUG_PUB.debug( 'after else update date:'||l_simul_visit_rec.START_DATE_TIME);
3729 END IF;
3730 --Replace primary visit attributes with simulation visit attributes
3731 OPEN c_Visit(l_primary_visit_id);
3732 FETCH c_Visit INTO c_visit_rec;
3733 CLOSE c_Visit;
3734
3735 -- To check if visit starttime is not null then store time in HH4 format
3736 IF (l_simul_visit_rec.START_DATE_TIME IS NOT NULL AND l_simul_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
3737 l_hour := TO_NUMBER(TO_CHAR(l_simul_visit_rec.START_DATE_TIME , 'HH24'));
3738 l_minute := TO_NUMBER(TO_CHAR(l_simul_visit_rec.START_DATE_TIME , 'MI'));
3739 ELSE
3740 l_hour := NULL;
3741 l_minute := NULL;
3742 l_simul_visit_rec.START_DATE_TIME := NULL;
3743 END IF;
3744
3745 -- To check if visit closetime is not null then store time in HH4 format
3746 IF (l_simul_visit_rec.CLOSE_DATE_TIME IS NOT NULL AND l_simul_visit_rec.CLOSE_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
3747 l_hour_close := TO_NUMBER(TO_CHAR(l_simul_visit_rec.CLOSE_DATE_TIME , 'HH24'));
3748 l_minute_close := TO_NUMBER(TO_CHAR(l_simul_visit_rec.CLOSE_DATE_TIME , 'MI'));
3749 ELSE
3750 l_hour_close := NULL;
3751 l_minute_close := NULL;
3752 l_simul_visit_rec.CLOSE_DATE_TIME := Null;
3753 END IF;
3754
3755 -- To chk if the subinvneotry/locator information is valid for the organization. If not valid
3756 /*Added by sowsubra - starts*/
3757 OPEN c_validate_subinv_loc_dtl (l_simul_visit_rec.inv_locator_id, l_simul_visit_rec.organization_id);
3758 FETCH c_validate_subinv_loc_dtl INTO l_prim_visit_rec.SUBINVENTORY,l_prim_visit_rec.LOCATOR_SEGMENT;
3759 IF c_validate_subinv_loc_dtl%NOTFOUND THEN
3760 OPEN c_get_default_loc_dtl(l_simul_visit_rec.organization_id, l_simul_visit_rec.department_id);
3761 FETCH c_get_default_loc_dtl INTO l_locator_id,l_prim_visit_rec.SUBINVENTORY,l_prim_visit_rec.LOCATOR_SEGMENT;
3762 IF c_get_default_loc_dtl%NOTFOUND THEN
3763 l_prim_visit_rec.inv_locator_id := NULL;
3764 ELSE
3765 l_prim_visit_rec.inv_locator_id := l_locator_id;
3766 END IF;
3767 CLOSE c_get_default_loc_dtl;
3768 END IF;
3769 CLOSE c_validate_subinv_loc_dtl;
3770 /*Added by sowsubra - ends*/
3771
3772 l_prim_visit_rec.VISIT_ID := l_primary_visit_id;
3773 l_prim_visit_rec.VISIT_NUMBER := l_primary_visit_number;
3774 l_prim_visit_rec.VISIT_TYPE_CODE := l_simul_visit_rec.VISIT_TYPE_CODE;
3775 l_prim_visit_rec.SIMULATION_PLAN_ID := l_simulation_plan_id;
3776 l_prim_visit_rec.ITEM_INSTANCE_ID := l_simul_visit_rec.ITEM_INSTANCE_ID;
3777 l_prim_visit_rec.INVENTORY_ITEM_ID := l_simul_visit_rec.INVENTORY_ITEM_ID;
3778 l_prim_visit_rec.ASSO_PRIMARY_VISIT_ID := NULL;
3779 l_prim_visit_rec.SIMULATION_DELETE_FLAG := 'N';
3780 l_prim_visit_rec.TEMPLATE_FLAG := l_simul_visit_rec.TEMPLATE_FLAG;
3781 l_prim_visit_rec.OUT_OF_SYNC_FLAG := l_simul_visit_rec.OUT_OF_SYNC_FLAG;
3782 l_prim_visit_rec.PROJECT_FLAG := l_simul_visit_rec.PROJECT_FLAG;
3783 l_prim_visit_rec.PROJECT_ID := l_simul_visit_rec.PROJECT_ID;
3784 l_prim_visit_rec.ATTRIBUTE1 := l_simul_visit_rec.ATTRIBUTE1;
3785 l_prim_visit_rec.ATTRIBUTE2 := l_simul_visit_rec.ATTRIBUTE2;
3786 l_prim_visit_rec.ATTRIBUTE3 := l_simul_visit_rec.ATTRIBUTE3;
3787 l_prim_visit_rec.ATTRIBUTE4 := l_simul_visit_rec.ATTRIBUTE4;
3788 l_prim_visit_rec.ATTRIBUTE5 := l_simul_visit_rec.ATTRIBUTE5;
3789 l_prim_visit_rec.ATTRIBUTE6 := l_simul_visit_rec.ATTRIBUTE6;
3790 l_prim_visit_rec.ATTRIBUTE7 := l_simul_visit_rec.ATTRIBUTE7;
3791 l_prim_visit_rec.ATTRIBUTE8 := l_simul_visit_rec.ATTRIBUTE8;
3792 l_prim_visit_rec.ATTRIBUTE9 := l_simul_visit_rec.ATTRIBUTE9;
3793 l_prim_visit_rec.ATTRIBUTE10 := l_simul_visit_rec.ATTRIBUTE10;
3794 l_prim_visit_rec.ATTRIBUTE11 := l_simul_visit_rec.ATTRIBUTE11;
3795 l_prim_visit_rec.ATTRIBUTE12 := l_simul_visit_rec.ATTRIBUTE12;
3796 l_prim_visit_rec.ATTRIBUTE13 := l_simul_visit_rec.ATTRIBUTE13;
3797 l_prim_visit_rec.ATTRIBUTE14 := l_simul_visit_rec.ATTRIBUTE14;
3798 l_prim_visit_rec.ATTRIBUTE15 := l_simul_visit_rec.ATTRIBUTE15;
3799 l_prim_visit_rec.OBJECT_VERSION_NUMBER := c_visit_rec.OBJECT_VERSION_NUMBER;
3800 l_prim_visit_rec.ORGANIZATION_ID := l_simul_visit_rec.ORGANIZATION_ID;
3801 --l_prim_visit_rec.ORG_NAME := l_simul_visit_rec.ORG_NAME;
3802 l_prim_visit_rec.DEPARTMENT_ID := l_simul_visit_rec.DEPARTMENT_ID;
3803 --l_prim_visit_rec.DEPT_NAME := l_simul_visit_rec.DEPT_NAME;
3804 l_prim_visit_rec.STATUS_CODE := l_simul_visit_rec.STATUS_CODE;
3805
3806 l_prim_visit_rec.START_DATE := l_simul_visit_rec.START_DATE_TIME;
3807 l_prim_visit_rec.START_HOUR := l_hour;
3808 l_prim_visit_rec.START_MIN := l_minute;
3809 l_prim_visit_rec.PLAN_END_DATE := l_simul_visit_rec.CLOSE_DATE_TIME;
3810 l_prim_visit_rec.PLAN_END_HOUR := l_hour_close;
3811 l_prim_visit_rec.PLAN_END_MIN := l_minute_close;
3812
3813 l_prim_visit_rec.OUTSIDE_PARTY_FLAG := l_simul_visit_rec.OUTSIDE_PARTY_FLAG;
3814 l_prim_visit_rec.VISIT_NAME := l_simul_visit_rec.VISIT_NAME;
3815 l_prim_visit_rec.DESCRIPTION := l_simul_visit_rec.DESCRIPTION;
3816 l_prim_visit_rec.SERVICE_REQUEST_ID := l_simul_visit_rec.SERVICE_REQUEST_ID;
3817 l_prim_visit_rec.SPACE_CATEGORY_CODE := l_simul_visit_rec.SPACE_CATEGORY_CODE;
3818 l_prim_visit_rec.PRIORITY_CODE := l_simul_visit_rec.priority_code;
3819 l_prim_visit_rec.PROJ_TEMPLATE_ID := l_simul_visit_rec.PROJECT_TEMPLATE_ID;
3820 l_prim_visit_rec.UNIT_SCHEDULE_ID := l_simul_visit_rec.UNIT_SCHEDULE_ID;
3821 l_prim_visit_rec.OPERATION_FLAG := 'U';
3822 l_prim_visit_tbl(0) := l_prim_visit_rec;
3823
3824 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3825 fnd_log.string
3826 (
3827 fnd_log.level_statement,
3828 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3829 'Before Calling ahl Vwp Visits Pvt Process Visit Records for visit : '|| l_primary_visit_id
3830 );
3831
3832 END IF;
3833
3834 AHL_VWP_VISITS_PVT.Process_Visit
3835 (
3836 p_api_version => p_api_version,
3837 p_init_msg_list => FND_API.g_false,
3838 p_commit => FND_API.g_false,
3839 p_validation_level => p_validation_level,
3840 p_module_type => NULL, --p_module_type,
3841 p_x_Visit_tbl => l_prim_visit_tbl,
3842 x_return_status => l_return_status,
3843 x_msg_count => l_msg_count,
3844 x_msg_data => l_msg_data
3845 );
3846
3847 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3848 fnd_log.string
3849 (
3850 fnd_log.level_statement,
3851 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3852 'After Calling ahl Vwp Visits Pvt status : '|| l_return_status
3853 );
3854
3855 END IF;
3856 -- Check Error Message stack.
3857 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3858 l_msg_count := FND_MSG_PUB.count_msg;
3859 IF l_msg_count > 0 THEN
3860 RAISE FND_API.G_EXC_ERROR;
3861 END IF;
3862 END IF;
3863
3864 -- Update the Any_task_chg flag to 'Y'
3865 AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
3866 (
3867 p_visit_id => l_primary_visit_id,
3868 p_flag => 'Y',
3869 x_return_status => l_return_status
3870 );
3871
3872 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3873 RAISE FND_API.G_EXC_ERROR;
3874 END IF;
3875
3876 -- Check for tasks exist in primary visit tasks which are not in simulation visit
3877 OPEN get_tasks_delete_csr (l_primary_visit_id);
3878 LOOP
3879 FETCH get_tasks_delete_csr INTO l_tasks_delete_rec;
3880 EXIT WHEN get_tasks_delete_csr%NOTFOUND;
3881 IF l_tasks_delete_rec.visit_task_id IS NOT NULL THEN
3882 /* Added by mpothuku on 01/11/04 */
3883 l_sim_prim_visit_task_id := null;
3884 /* mpothuku End */
3885 OPEN check_prim_visit_task_cur(p_visit_id,l_tasks_delete_rec.visit_task_id);
3886 FETCH check_prim_visit_task_cur INTO l_sim_prim_visit_task_id;
3887 CLOSE check_prim_visit_task_cur;
3888 IF (l_sim_prim_visit_task_id IS NULL)
3889 THEN
3890 --This will take care of removing the links as well from the primary tasks.
3891 AHL_VWP_TASKS_PVT.Delete_Task
3892 (
3893 p_api_version => p_api_version,
3894 p_init_msg_list => FND_API.g_false,
3895 p_commit => FND_API.g_false,
3896 p_validation_level => p_validation_level,
3897 p_module_type => NULL,
3898 p_visit_task_id => l_tasks_delete_rec.visit_task_id,
3899 x_return_status => l_return_status,
3900 x_msg_count => l_msg_count,
3901 x_msg_data => l_msg_data
3902 );
3903
3904 -- Check Error Message stack.
3905 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3906 l_msg_count := FND_MSG_PUB.count_msg;
3907 IF l_msg_count > 0 THEN
3908 CLOSE get_tasks_delete_csr;
3909 RAISE FND_API.G_EXC_ERROR;
3910 END IF;
3911 END IF;
3912 END IF;
3913 END IF;
3914 END LOOP;
3915 CLOSE get_tasks_delete_csr;
3916
3917 -- Set the corrseponding tasks
3918 OPEN simul_visit_task_cur(p_visit_id);
3919 LOOP
3920 FETCH simul_visit_task_cur INTO l_simul_visit_task_rec;
3921
3922 IF G_DEBUG='Y' THEN
3923 AHL_DEBUG_PUB.debug( 'after fetch'||l_simul_visit_task_rec.primary_visit_task_id);
3924 END IF;
3925
3926 EXIT WHEN simul_visit_task_cur%NOTFOUND;
3927 --
3928 l_primary_visit_task_id := null;
3929 --Check if there is corresponding task in the Primary Visit table.
3930 IF l_simul_visit_task_rec.primary_visit_task_id IS NOT NULL THEN
3931 --Replace simulation visit task attributes with primary task attributes
3932 OPEN check_visit_task_cur(l_primary_visit_id,l_simul_visit_task_rec.primary_visit_task_id);
3933 FETCH check_visit_task_cur INTO l_primary_visit_task_id;
3934 CLOSE check_visit_task_cur;
3935
3936 IF G_DEBUG='Y' THEN
3937 AHL_DEBUG_PUB.debug( 'Primary visit task:'||l_primary_visit_task_id);
3938 END IF;
3939 END IF;
3940
3941 /* Added by mpothuku on 01/20/05 to Check if the UE is associated with any of the visits in the plan */
3942 IF(l_simul_visit_task_rec.task_type_code = 'PLANNED' and l_simul_visit_task_rec.unit_effectivity_id IS NOT NULL) THEN
3943
3944 OPEN chk_unit_effectivities (l_simul_visit_task_rec.unit_effectivity_id,l_primary_visit_id);
3945 FETCH chk_unit_effectivities INTO l_visit_number;
3946 IF (chk_unit_effectivities%FOUND) THEN
3947 CLOSE chk_unit_effectivities;
3948
3949 /*
3950 AnRaj: Added for fixing the performance issues logged in bug#:4919576
3951 */
3952 -- ERROR MESSAGE
3953 /*
3954 OPEN c_ue_details (l_simul_visit_task_rec.unit_effectivity_id);
3955 FETCH c_ue_details INTO l_ue_details_rec;
3956 CLOSE c_ue_details;
3957 */
3958 x_return_status := Fnd_Api.g_ret_sts_error;
3959 OPEN c_ue_mr_sr_id(l_simul_visit_task_rec.unit_effectivity_id);
3960 FETCH c_ue_mr_sr_id INTO ue_mr_sr_id_rec;
3961 CLOSE c_ue_mr_sr_id;
3962
3963 IF ue_mr_sr_id_rec.cs_incident_id IS NOT NULL THEN
3964 OPEN c_ue_sr_details(ue_mr_sr_id_rec.cs_incident_id,ue_mr_sr_id_rec.csi_item_instance_id);
3965 FETCH c_ue_sr_details INTO ue_sr_details_rec;
3966 CLOSE c_ue_sr_details;
3967 Fnd_Message.SET_NAME('AHL','AHL_LTP_PRIM_VISIT_UNIT_FOUND');
3968 Fnd_Message.SET_TOKEN('UE_TITLE', ue_sr_details_rec.ue_title);
3969 Fnd_Message.SET_TOKEN('ITEM_NUMBER', ue_sr_details_rec.part_number);
3970 Fnd_Message.SET_TOKEN('SERIAL_NUMBER', ue_sr_details_rec.serial_number);
3971 Fnd_Message.SET_TOKEN('MR_TITLE', ue_sr_details_rec.mr_title);
3972 Fnd_Message.SET_TOKEN('VISIT1', l_primary_visit_number);
3973 Fnd_Message.SET_TOKEN('VISIT2', l_visit_number);
3974 Fnd_Msg_Pub.ADD;
3975 RAISE Fnd_Api.G_EXC_ERROR;
3976 ELSE
3977 OPEN c_ue_mr_details(ue_mr_sr_id_rec.mr_header_id,ue_mr_sr_id_rec.csi_item_instance_id);
3978 FETCH c_ue_mr_details INTO ue_mr_details_rec;
3979 CLOSE c_ue_mr_details;
3980 Fnd_Message.SET_NAME('AHL','AHL_LTP_PRIM_VISIT_UNIT_FOUND');
3981 Fnd_Message.SET_TOKEN('UE_TITLE', ue_mr_details_rec.ue_title);
3982 Fnd_Message.SET_TOKEN('ITEM_NUMBER', ue_mr_details_rec.part_number);
3983 Fnd_Message.SET_TOKEN('SERIAL_NUMBER', ue_mr_details_rec.serial_number);
3984 Fnd_Message.SET_TOKEN('MR_TITLE', ue_mr_details_rec.mr_title);
3985 Fnd_Message.SET_TOKEN('VISIT1', l_primary_visit_number);
3986 Fnd_Message.SET_TOKEN('VISIT2', l_visit_number);
3987 Fnd_Msg_Pub.ADD;
3988 RAISE Fnd_Api.G_EXC_ERROR;
3989 END IF;
3990 /*
3991 AnRaj: End of Fix bug#:4919576
3992 */
3993 ELSE
3994 CLOSE chk_unit_effectivities;
3995 END IF;
3996 END IF;
3997
3998 IF(l_simul_visit_task_rec.primary_visit_task_id IS NOT NULL and l_primary_visit_task_id IS NOT NULL )
3999 THEN
4000 OPEN c_task(l_simul_visit_task_rec.primary_visit_task_id);
4001 FETCH c_task INTO c_task_rec;
4002 CLOSE c_task;
4003 l_prim_visit_task_rec.VISIT_TASK_ID := l_simul_visit_task_rec.primary_visit_task_id;
4004 l_prim_visit_task_rec.OBJECT_VERSION_NUMBER := c_task_rec.OBJECT_VERSION_NUMBER;
4005 l_prim_visit_task_rec.VISIT_TASK_NUMBER := l_simul_visit_task_rec.visit_task_number;
4006 l_prim_visit_task_rec.VISIT_ID := l_primary_visit_id;
4007 l_prim_visit_task_rec.PROJECT_TASK_ID := l_simul_visit_task_rec.PROJECT_TASK_ID;
4008 l_prim_visit_task_rec.COST_PARENT_ID := null;
4009 l_prim_visit_task_rec.MR_ROUTE_ID := l_simul_visit_task_rec.MR_ROUTE_ID;
4010 l_prim_visit_task_rec.MR_ID := l_simul_visit_task_rec.MR_ID;
4011 l_prim_visit_task_rec.DURATION := l_simul_visit_task_rec.DURATION;
4012 l_prim_visit_task_rec.UNIT_EFFECTIVITY_ID := l_simul_visit_task_rec.UNIT_EFFECTIVITY_ID;
4013 l_prim_visit_task_rec.START_FROM_HOUR := l_simul_visit_task_rec.START_FROM_HOUR;
4014 l_prim_visit_task_rec.INVENTORY_ITEM_ID := l_simul_visit_task_rec.INVENTORY_ITEM_ID;
4015 l_prim_visit_task_rec.ITEM_ORGANIZATION_ID := l_simul_visit_task_rec.ITEM_ORGANIZATION_ID;
4016 l_prim_visit_task_rec.INSTANCE_ID := l_simul_visit_task_rec.INSTANCE_ID;
4017 l_prim_visit_task_rec.PRIMARY_VISIT_TASK_ID := null;
4018 --l_prim_visit_task_rec.SUMMARY_TASK_FLAG := l_simul_visit_task_rec.SUMMARY_TASK_FLAG;
4019 l_prim_visit_task_rec.ORIGINATING_TASK_ID := null;
4020 l_prim_visit_task_rec.SERVICE_REQUEST_ID := l_simul_visit_task_rec.SERVICE_REQUEST_ID;
4021 l_prim_visit_task_rec.TASK_TYPE_CODE := l_simul_visit_task_rec.TASK_TYPE_CODE;
4022 --l_prim_visit_task_rec.PRICE_LIST_ID := l_simul_visit_task_rec.PRICE_LIST_ID ;
4023 --l_prim_visit_task_rec.ESTIMATED_PRICE := l_simul_visit_task_rec.ESTIMATED_PRICE;
4024 --l_prim_visit_task_rec.ACTUAL_PRICE := l_simul_visit_task_rec.ACTUAL_PRICE;
4025 --l_prim_visit_task_rec.ACTUAL_COST := l_simul_visit_task_rec.ACTUAL_COST;
4026 l_prim_visit_task_rec.STAGE_ID := null;--l_simul_visit_task_rec.STAGE_ID;
4027 l_prim_visit_task_rec.TASK_STATUS_CODE := l_simul_visit_task_rec.STATUS_CODE;
4028 l_prim_visit_task_rec.ATTRIBUTE_CATEGORY := l_simul_visit_task_rec.ATTRIBUTE_CATEGORY;
4029 l_prim_visit_task_rec.ATTRIBUTE1 := l_simul_visit_task_rec.attribute1;
4030 l_prim_visit_task_rec.ATTRIBUTE2 := l_simul_visit_task_rec.attribute2;
4031 l_prim_visit_task_rec.ATTRIBUTE3 := l_simul_visit_task_rec.attribute3;
4032 l_prim_visit_task_rec.ATTRIBUTE4 := l_simul_visit_task_rec.attribute4;
4033 l_prim_visit_task_rec.ATTRIBUTE5 := l_simul_visit_task_rec.attribute5;
4034 l_prim_visit_task_rec.ATTRIBUTE6 := l_simul_visit_task_rec.attribute6;
4035 l_prim_visit_task_rec.ATTRIBUTE7 := l_simul_visit_task_rec.attribute7;
4036 l_prim_visit_task_rec.ATTRIBUTE8 := l_simul_visit_task_rec.attribute8;
4037 l_prim_visit_task_rec.ATTRIBUTE9 := l_simul_visit_task_rec.attribute9;
4038 l_prim_visit_task_rec.ATTRIBUTE10 := l_simul_visit_task_rec.attribute10;
4039 l_prim_visit_task_rec.ATTRIBUTE11 := l_simul_visit_task_rec.attribute11;
4040 l_prim_visit_task_rec.ATTRIBUTE12 := l_simul_visit_task_rec.attribute12;
4041 l_prim_visit_task_rec.ATTRIBUTE13 := l_simul_visit_task_rec.attribute13;
4042 l_prim_visit_task_rec.ATTRIBUTE14 := l_simul_visit_task_rec.attribute14;
4043 l_prim_visit_task_rec.ATTRIBUTE15 := l_simul_visit_task_rec.attribute15;
4044 l_prim_visit_task_rec.VISIT_TASK_NAME := l_simul_visit_task_rec.visit_task_name;
4045 l_prim_visit_task_rec.DESCRIPTION := l_simul_visit_task_rec.description;
4046 l_prim_visit_task_rec.DEPARTMENT_ID := l_simul_visit_task_rec.department_id;
4047 l_prim_visit_task_rec.ITEM_NAME := l_simul_visit_task_rec.ITEM_NAME;
4048 l_prim_visit_task_rec.SERIAL_NUMBER := l_simul_visit_task_rec.SERIAL_NUMBER;
4049 l_prim_visit_task_rec.QUANTITY := l_simul_visit_task_rec.QUANTITY; -- Added by rnahata for Issue 105
4050
4051 AHL_VWP_TASKS_PVT.Update_Task
4052 (
4053 p_api_version => p_api_version,
4054 p_init_msg_list => Fnd_Api.g_false,
4055 p_commit => Fnd_Api.g_false,
4056 p_validation_level => p_validation_level,
4057 --passing null here as we dont want the OrigtaskId,
4058 --to be picked up as the value we are passing at this point.
4059 p_module_type => null,
4060 p_x_task_rec => l_prim_visit_task_rec,
4061 x_return_status => l_return_status,
4062 x_msg_count => l_msg_count,
4063 x_msg_data => l_msg_data
4064 );
4065
4066 -- Check Error Message stack.
4067 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4068 l_msg_count := FND_MSG_PUB.count_msg;
4069 IF l_msg_count > 0 THEN
4070 RAISE FND_API.G_EXC_ERROR;
4071 END IF;
4072 END IF;
4073
4074 ELSIF((l_simul_visit_task_rec.primary_visit_task_id IS NULL) OR
4075 (l_simul_visit_task_rec.primary_visit_task_id IS NOT NULL AND l_primary_visit_task_id IS NULL))
4076 /* This clause means that
4077 1. Either the task is deleted from primary visit after copying to simulation visit Or
4078 2. The task is created in the Simulation visit.
4079 */
4080 THEN
4081 /* Added by mpothuku on 01/11/04 to insert new row into the PrimaryVisit */
4082 SELECT Ahl_Visit_Tasks_B_S.NEXTVAL INTO
4083 l_visit_task_id FROM dual;
4084
4085 IF G_DEBUG='Y' THEN
4086 AHL_DEBUG_PUB.debug( 'visit call insert new task created in simulation:'||l_simul_visit_task_rec.visit_task_number);
4087 END IF;
4088
4089 l_visit_task_number := Get_Visit_Task_Number(l_primary_visit_id,l_simul_visit_task_rec.visit_task_number);
4090 Ahl_Visit_Tasks_Pkg.INSERT_ROW
4091 (
4092 X_ROWID => l_rowid,
4093 X_VISIT_TASK_ID => l_visit_task_id,
4094 X_VISIT_TASK_NUMBER => l_visit_task_number,
4095 X_OBJECT_VERSION_NUMBER => 1,
4096 X_VISIT_ID => l_primary_visit_id,
4097 X_PROJECT_TASK_ID => l_simul_visit_task_rec.project_task_id,
4098 X_COST_PARENT_ID => null,
4099 X_MR_ROUTE_ID => l_simul_visit_task_rec.mr_route_id,
4100 X_MR_ID => l_simul_visit_task_rec.mr_id,
4101 X_DURATION => l_simul_visit_task_rec.duration,
4102 X_UNIT_EFFECTIVITY_ID => l_simul_visit_task_rec.unit_effectivity_id,
4103 X_START_FROM_HOUR => l_simul_visit_task_rec.start_from_hour,
4104 X_INVENTORY_ITEM_ID => l_simul_visit_task_rec.inventory_item_id,
4105 X_ITEM_ORGANIZATION_ID => l_simul_visit_task_rec.item_organization_id,
4106 X_INSTANCE_ID => l_simul_visit_task_rec.instance_id,
4107 X_PRIMARY_VISIT_TASK_ID => null,
4108 X_SUMMARY_TASK_FLAG => l_simul_visit_task_rec.summary_task_flag,
4109 X_ORIGINATING_TASK_ID => null,
4110 X_SERVICE_REQUEST_ID => l_simul_visit_task_rec.service_request_id,
4111 X_TASK_TYPE_CODE => l_simul_visit_task_rec.task_type_code,
4112 X_PRICE_LIST_ID => null,
4113 X_STATUS_CODE => l_simul_visit_task_rec.status_code,
4114 X_ESTIMATED_PRICE => null,
4115 X_ACTUAL_PRICE => null,
4116 X_ACTUAL_COST => null,
4117 X_STAGE_ID => null,
4118 -- Added cxcheng POST11510-- No Calculation Need for Sim---------
4119 X_START_DATE_TIME => l_simul_visit_task_rec.start_date_time,
4120 X_END_DATE_TIME => l_simul_visit_task_rec.end_date_time,
4121 X_ATTRIBUTE_CATEGORY => l_simul_visit_task_rec.attribute_category,
4122 X_ATTRIBUTE1 => l_simul_visit_task_rec.attribute1,
4123 X_ATTRIBUTE2 => l_simul_visit_task_rec.attribute2,
4124 X_ATTRIBUTE3 => l_simul_visit_task_rec.attribute3,
4125 X_ATTRIBUTE4 => l_simul_visit_task_rec.attribute4,
4126 X_ATTRIBUTE5 => l_simul_visit_task_rec.attribute5,
4127 X_ATTRIBUTE6 => l_simul_visit_task_rec.attribute6,
4128 X_ATTRIBUTE7 => l_simul_visit_task_rec.attribute7,
4129 X_ATTRIBUTE8 => l_simul_visit_task_rec.attribute8,
4130 X_ATTRIBUTE9 => l_simul_visit_task_rec.attribute9,
4131 X_ATTRIBUTE10 => l_simul_visit_task_rec.attribute10,
4132 X_ATTRIBUTE11 => l_simul_visit_task_rec.attribute11,
4133 X_ATTRIBUTE12 => l_simul_visit_task_rec.attribute12,
4134 X_ATTRIBUTE13 => l_simul_visit_task_rec.attribute13,
4135 X_ATTRIBUTE14 => l_simul_visit_task_rec.attribute14,
4136 X_ATTRIBUTE15 => l_simul_visit_task_rec.attribute15,
4137 X_VISIT_TASK_NAME => l_simul_visit_task_rec.visit_task_name,
4138 X_DESCRIPTION => l_simul_visit_task_rec.description,
4139 X_DEPARTMENT_ID => l_simul_visit_task_rec.department_id,
4140 X_QUANTITY => l_simul_visit_task_rec.quantity, -- Added by rnahata for Issue 105
4141 X_CREATION_DATE => SYSDATE,
4142 X_CREATED_BY => Fnd_Global.USER_ID,
4143 X_LAST_UPDATE_DATE => SYSDATE,
4144 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
4145 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID
4146 );
4147 /* Need to update the simulation_visit's primary_visit_task_id with the Id thats generated here */
4148 UPDATE ahl_visit_tasks_b
4149 SET primary_visit_task_id = l_visit_task_id
4150 WHERE visit_task_id = l_simul_visit_task_rec.visit_task_id;
4151
4152 END IF;
4153 /* mpothuku End */
4154 IF G_DEBUG='Y' THEN
4155 AHL_DEBUG_PUB.debug( 'After insertion of simulation visit task:'||l_simul_visit_task_rec.visit_task_id);
4156 END IF;
4157 END LOOP;
4158 CLOSE simul_visit_task_cur;
4159
4160 -- For updating the cost_parent_id and originating_task_id
4161 OPEN simul_visit_task_cur(p_visit_id);
4162 LOOP
4163 FETCH simul_visit_task_cur INTO l_simul_visit_task_rec;
4164
4165 IF G_DEBUG='Y' THEN
4166 AHL_DEBUG_PUB.debug( 'after fetch'||l_simul_visit_task_rec.primary_visit_task_id);
4167 END IF;
4168
4169 EXIT WHEN simul_visit_task_cur%NOTFOUND;
4170 --Check if the task has an originating_task_id /cost_parent_id
4171 IF(l_simul_visit_task_rec.originating_task_id IS NOT NULL OR
4172 l_simul_visit_task_rec.cost_parent_id IS NOT NULL OR
4173 l_simul_visit_task_rec.stage_id IS NOT NULL )
4174 THEN
4175 --Get the corresponding task record from the primary visit to update.
4176 OPEN c_new_primary_task(l_simul_visit_task_rec.visit_task_id);
4177 FETCH c_new_primary_task INTO l_new_task_id,l_new_task_number;
4178 CLOSE c_new_primary_task;
4179
4180 OPEN c_task(l_new_task_id);
4181 FETCH c_task INTO c_task_rec;
4182 CLOSE c_task;
4183
4184 l_prim_visit_task_rec.VISIT_TASK_ID := c_task_rec.VISIT_TASK_ID;
4185 l_prim_visit_task_rec.VISIT_TASK_NUMBER := c_task_rec.VISIT_TASK_NUMBER;
4186 l_prim_visit_task_rec.VISIT_ID := l_primary_visit_id;
4187 l_prim_visit_task_rec.OBJECT_VERSION_NUMBER := c_task_rec.OBJECT_VERSION_NUMBER;
4188 l_prim_visit_task_rec.PROJECT_TASK_ID := c_task_rec.PROJECT_TASK_ID;
4189 l_prim_visit_task_rec.MR_ROUTE_ID := c_task_rec.MR_ROUTE_ID;
4190 l_prim_visit_task_rec.MR_ID := c_task_rec.MR_ID;
4191 l_prim_visit_task_rec.DURATION := c_task_rec.DURATION;
4192 l_prim_visit_task_rec.UNIT_EFFECTIVITY_ID := c_task_rec.UNIT_EFFECTIVITY_ID;
4193 l_prim_visit_task_rec.START_FROM_HOUR := c_task_rec.START_FROM_HOUR;
4194 l_prim_visit_task_rec.INVENTORY_ITEM_ID := c_task_rec.INVENTORY_ITEM_ID;
4195 l_prim_visit_task_rec.ITEM_ORGANIZATION_ID := c_task_rec.ITEM_ORGANIZATION_ID;
4196 l_prim_visit_task_rec.INSTANCE_ID := c_task_rec.INSTANCE_ID;
4197 l_prim_visit_task_rec.PRIMARY_VISIT_TASK_ID := null;
4198 l_prim_visit_task_rec.SERVICE_REQUEST_ID := c_task_rec.SERVICE_REQUEST_ID;
4199 l_prim_visit_task_rec.TASK_TYPE_CODE := c_task_rec.TASK_TYPE_CODE;
4200 l_prim_visit_task_rec.TASK_STATUS_CODE := c_task_rec.STATUS_CODE;
4201 l_prim_visit_task_rec.ATTRIBUTE_CATEGORY := c_task_rec.ATTRIBUTE_CATEGORY;
4202 l_prim_visit_task_rec.ATTRIBUTE1 := c_task_rec.attribute1;
4203 l_prim_visit_task_rec.ATTRIBUTE2 := c_task_rec.attribute2;
4204 l_prim_visit_task_rec.ATTRIBUTE3 := c_task_rec.attribute3;
4205 l_prim_visit_task_rec.ATTRIBUTE4 := c_task_rec.attribute4;
4206 l_prim_visit_task_rec.ATTRIBUTE5 := c_task_rec.attribute5;
4207 l_prim_visit_task_rec.ATTRIBUTE6 := c_task_rec.attribute6;
4208 l_prim_visit_task_rec.ATTRIBUTE7 := c_task_rec.attribute7;
4209 l_prim_visit_task_rec.ATTRIBUTE8 := c_task_rec.attribute8;
4210 l_prim_visit_task_rec.ATTRIBUTE9 := c_task_rec.attribute9;
4211 l_prim_visit_task_rec.ATTRIBUTE10 := c_task_rec.attribute10;
4212 l_prim_visit_task_rec.ATTRIBUTE11 := c_task_rec.attribute11;
4213 l_prim_visit_task_rec.ATTRIBUTE12 := c_task_rec.attribute12;
4214 l_prim_visit_task_rec.ATTRIBUTE13 := c_task_rec.attribute13;
4215 l_prim_visit_task_rec.ATTRIBUTE14 := c_task_rec.attribute14;
4216 l_prim_visit_task_rec.ATTRIBUTE15 := c_task_rec.attribute15;
4217 l_prim_visit_task_rec.VISIT_TASK_NAME := c_task_rec.visit_task_name;
4218 l_prim_visit_task_rec.DESCRIPTION := c_task_rec.description;
4219 l_prim_visit_task_rec.DEPARTMENT_ID := c_task_rec.department_id;
4220 l_prim_visit_task_rec.ITEM_NAME := l_simul_visit_task_rec.ITEM_NAME;
4221 l_prim_visit_task_rec.SERIAL_NUMBER := l_simul_visit_task_rec.SERIAL_NUMBER;
4222 l_prim_visit_task_rec.ORIGINATING_TASK_ID := null;
4223 l_prim_visit_task_rec.ORGINATING_TASK_NUMBER:= null;
4224 l_prim_visit_task_rec.COST_PARENT_ID := null;
4225 l_prim_visit_task_rec.COST_PARENT_NUMBER := null;
4226 l_prim_visit_task_rec.STAGE_ID := null;
4227 l_prim_visit_task_rec.STAGE_NAME := null;
4228
4229 IF(l_simul_visit_task_rec.ORIGINATING_TASK_ID IS NOT NULL) THEN
4230 OPEN c_new_primary_task(l_simul_visit_task_rec.ORIGINATING_TASK_ID);
4231 FETCH c_new_primary_task INTO l_new_task_id,l_new_task_number;
4232 CLOSE c_new_primary_task;
4233 l_prim_visit_task_rec.ORIGINATING_TASK_ID := l_new_task_id;
4234 l_prim_visit_task_rec.ORGINATING_TASK_NUMBER := l_new_task_number;
4235 END IF;
4236
4237 IF(l_simul_visit_task_rec.COST_PARENT_ID IS NOT NULL) THEN
4238 OPEN c_new_primary_task(l_simul_visit_task_rec.COST_PARENT_ID);
4239 FETCH c_new_primary_task INTO l_new_task_id,l_new_task_number;
4240 CLOSE c_new_primary_task;
4241 l_prim_visit_task_rec.COST_PARENT_ID := l_new_task_id;
4242 l_prim_visit_task_rec.COST_PARENT_NUMBER := l_new_task_number;
4243 END IF;
4244
4245 IF(l_simul_visit_task_rec.STAGE_ID IS NOT NULL) THEN
4246 OPEN c_new_stage(l_simul_visit_task_rec.STAGE_ID,l_primary_visit_id);
4247 FETCH c_new_stage INTO l_new_stage_id,l_new_stage_name;
4248 CLOSE c_new_stage;
4249 l_prim_visit_task_rec.STAGE_ID := l_new_stage_id;
4250 l_prim_visit_task_rec.STAGE_NAME := l_new_stage_name;
4251 END IF;
4252
4253
4254 AHL_VWP_TASKS_PVT.Update_Task
4255 (
4256 p_api_version => p_api_version,
4257 p_init_msg_list => Fnd_Api.g_false,
4258 p_commit => Fnd_Api.g_false,
4259 p_validation_level => p_validation_level,
4260 --passing LTP here as we want the OrigtaskId,
4261 --to be picked up as the value we are passing.
4262 p_module_type => 'LTP',
4263 p_x_task_rec => l_prim_visit_task_rec,
4264 x_return_status => l_return_status,
4265 x_msg_count => l_msg_count,
4266 x_msg_data => l_msg_data
4267 );
4268
4269 -- Check Error Message stack.
4270 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4271 l_msg_count := FND_MSG_PUB.count_msg;
4272 IF l_msg_count > 0 THEN
4273 RAISE FND_API.G_EXC_ERROR;
4274 END IF;
4275 END IF;
4276 END IF;
4277 END LOOP;
4278 CLOSE simul_visit_task_cur;
4279
4280 /*Added by mpothuku to Copy the Task Links back to the Primary Visit */
4281 --Remove task links from the Primary Visit.
4282 OPEN check_exist_visit_task_cur (l_primary_visit_id);
4283 LOOP
4284 FETCH check_exist_visit_task_cur INTO l_exist_prim_visit_task_id;
4285 EXIT WHEN check_exist_visit_task_cur%NOTFOUND;
4286
4287 /* Added by mpothuku on 01/11/04 to delete the existing links */
4288 OPEN c_links (l_exist_prim_visit_task_id);
4289 FETCH c_links INTO l_count;
4290 IF l_count > 0 THEN
4291 DELETE Ahl_Task_Links
4292 WHERE VISIT_TASK_ID = l_exist_prim_visit_task_id
4293 OR PARENT_TASK_ID = l_exist_prim_visit_task_id;
4294 END IF;
4295 CLOSE c_links;
4296 END LOOP;
4297 CLOSE check_exist_visit_task_cur;
4298
4299 /* Copy the Links from Simulation Visit */
4300 OPEN c_visit_task_links(p_visit_id);
4301 LOOP
4302 FETCH c_visit_task_links INTO l_task_link_rec;
4303 EXIT WHEN c_visit_task_links%NOTFOUND;
4304
4305 -- Find coresponding task id in new visit
4306 OPEN c_new_primary_task(l_task_link_rec.visit_task_id);
4307 FETCH c_new_primary_task INTO l_new_task_id,l_new_task_number;
4308 CLOSE c_new_primary_task;
4309
4310 OPEN c_new_primary_task(l_task_link_rec.parent_task_id);
4311 FETCH c_new_primary_task INTO l_new_parent_task_id,l_new_task_number;
4312 CLOSE c_new_primary_task;
4313
4314 -- Create task link
4315 INSERT INTO AHL_TASK_LINKS
4316 (
4317 TASK_LINK_ID,
4318 OBJECT_VERSION_NUMBER,
4319 LAST_UPDATE_DATE,
4320 LAST_UPDATED_BY,
4321 CREATION_DATE,
4322 CREATED_BY,
4323 LAST_UPDATE_LOGIN,
4324 VISIT_TASK_ID,
4325 PARENT_TASK_ID,
4326 --SECURITY_GROUP_ID,
4327 ATTRIBUTE_CATEGORY,
4328 ATTRIBUTE1,
4329 ATTRIBUTE2,
4330 ATTRIBUTE3,
4331 ATTRIBUTE4,
4332 ATTRIBUTE5,
4333 ATTRIBUTE6,
4334 ATTRIBUTE7,
4335 ATTRIBUTE8,
4336 ATTRIBUTE9,
4337 ATTRIBUTE10,
4338 ATTRIBUTE11,
4339 ATTRIBUTE12,
4340 ATTRIBUTE13,
4341 ATTRIBUTE14,
4342 ATTRIBUTE15
4343 )
4344 values
4345 (
4346 ahl_task_links_s.nextval,
4347 1,
4348 SYSDATE,
4349 Fnd_Global.USER_ID,
4350 SYSDATE,
4351 Fnd_Global.USER_ID,
4352 Fnd_Global.USER_ID,
4353 l_new_task_id,
4354 l_new_parent_task_id,
4355 --l_task_link_rec.SECURITY_GROUP_ID,
4356 l_task_link_rec.ATTRIBUTE_CATEGORY,
4357 l_task_link_rec.ATTRIBUTE1,
4358 l_task_link_rec.ATTRIBUTE2,
4359 l_task_link_rec.ATTRIBUTE3,
4360 l_task_link_rec.ATTRIBUTE4,
4361 l_task_link_rec.ATTRIBUTE5,
4362 l_task_link_rec.ATTRIBUTE6,
4363 l_task_link_rec.ATTRIBUTE7,
4364 l_task_link_rec.ATTRIBUTE8,
4365 l_task_link_rec.ATTRIBUTE9,
4366 l_task_link_rec.ATTRIBUTE10,
4367 l_task_link_rec.ATTRIBUTE11,
4368 l_task_link_rec.ATTRIBUTE12,
4369 l_task_link_rec.ATTRIBUTE13,
4370 l_task_link_rec.ATTRIBUTE14,
4371 l_task_link_rec.ATTRIBUTE15
4372 );
4373 END LOOP;
4374 CLOSE c_visit_task_links;
4375
4376 --Copying the Stages Back to the Primary Visit
4377 OPEN Get_stages_cur(p_visit_id);
4378 LOOP
4379 FETCH Get_stages_cur INTO l_stage_rec;
4380 EXIT WHEN Get_stages_cur%NOTFOUND;
4381 IF G_DEBUG='Y' THEN
4382 AHL_DEBUG_PUB.debug( 'inside loop stage num:'||l_stage_rec.stage_num);
4383 END IF;
4384
4385 -- Get Stage id of the primary Visit that has to be updated
4386 OPEN c_new_stage(l_stage_rec.stage_id,l_primary_visit_id);
4387 FETCH c_new_stage INTO l_new_stage_id,l_new_stage_name;
4388 CLOSE c_new_stage;
4389
4390 --
4391 IF G_DEBUG='Y' THEN
4392 AHL_DEBUG_PUB.debug( 'visit call update stage:'||l_stage_rec.stage_id);
4393 END IF;
4394 /* Copy the details in the Simulation Visit */
4395 -- Invoke the table handler to update a record
4396 Ahl_VWP_Stages_Pkg.Update_Row (
4397 X_VISIT_ID => l_primary_visit_id,
4398 X_STAGE_ID => l_new_stage_id,
4399 X_STAGE_NUM => l_stage_rec.Stage_Num,
4400 X_STAGE_NAME => l_stage_rec.Stage_Name,
4401 X_DURATION => l_stage_rec.Duration,
4402 X_OBJECT_VERSION_NUMBER => l_stage_rec.object_version_number+1,
4403 X_ATTRIBUTE_CATEGORY => l_stage_rec.ATTRIBUTE_CATEGORY,
4404 X_ATTRIBUTE1 => l_stage_rec.ATTRIBUTE1,
4405 X_ATTRIBUTE2 => l_stage_rec.ATTRIBUTE2,
4406 X_ATTRIBUTE3 => l_stage_rec.ATTRIBUTE3,
4407 X_ATTRIBUTE4 => l_stage_rec.ATTRIBUTE4,
4408 X_ATTRIBUTE5 => l_stage_rec.ATTRIBUTE5,
4409 X_ATTRIBUTE6 => l_stage_rec.ATTRIBUTE6,
4410 X_ATTRIBUTE7 => l_stage_rec.ATTRIBUTE7,
4411 X_ATTRIBUTE8 => l_stage_rec.ATTRIBUTE8,
4412 X_ATTRIBUTE9 => l_stage_rec.ATTRIBUTE9 ,
4413 X_ATTRIBUTE10 => l_stage_rec.ATTRIBUTE10,
4414 X_ATTRIBUTE11 => l_stage_rec.ATTRIBUTE11,
4415 X_ATTRIBUTE12 => l_stage_rec.ATTRIBUTE12,
4416 X_ATTRIBUTE13 => l_stage_rec.ATTRIBUTE13,
4417 X_ATTRIBUTE14 => l_stage_rec.ATTRIBUTE14,
4418 X_ATTRIBUTE15 => l_stage_rec.ATTRIBUTE15,
4419 X_LAST_UPDATE_DATE => SYSDATE,
4420 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
4421 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
4422
4423 IF G_DEBUG='Y' THEN
4424 AHL_DEBUG_PUB.Debug( l_full_name ||': Visit ID =' || l_primary_visit_id);
4425 AHL_DEBUG_PUB.Debug( l_full_name ||': Stage Number =' || l_stage_rec.Stage_Num);
4426 END IF;
4427 END LOOP;
4428 CLOSE Get_stages_cur;
4429
4430 IF G_DEBUG='Y' THEN
4431 AHL_DEBUG_PUB.debug( 'before delete simulation visit:'||p_visit_id);
4432 END IF;
4433
4434 --To adjust the task times for the inserted/updated tasks
4435 AHL_VWP_TIMES_PVT.Calculate_Task_Times
4436 (
4437 p_api_version => p_api_version,
4438 p_init_msg_list => Fnd_Api.G_FALSE,
4439 p_commit => Fnd_Api.G_FALSE,
4440 p_validation_level => p_validation_level,
4441 x_return_status => l_return_status,
4442 x_msg_count => l_msg_count,
4443 x_msg_data => l_msg_data,
4444 p_visit_id => l_primary_visit_id
4445 );
4446
4447 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4448 fnd_log.string
4449 (
4450 fnd_log.level_statement,
4451 'ahl.plsql.'||L_FULL_NAME,
4452 'After calling AHL_VWP_TIMES_PVT.Calculate_Task_Times'
4453 );
4454 END IF;
4455
4456 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
4457 x_return_status := FND_API.G_RET_STS_ERROR;
4458 RAISE FND_API.G_EXC_ERROR;
4459 END IF;
4460
4461 -- anraj for fixing the issue number 207 in the CMRO Forum
4462 -- If none of the Org,dept and start date are null then Process_Planned_Materials is called with 'U'
4463 -- Else Process_Planned_Materials is called with the 'D' flag.
4464 OPEN c_visit_details_for_materials(l_primary_visit_id);
4465 FETCH c_visit_details_for_materials INTO l_visit_details_for_materials;
4466 CLOSE c_visit_details_for_materials;
4467
4468 IF ( l_visit_details_for_materials.organization_id IS NOT NULL AND
4469 l_visit_details_for_materials.department_id IS NOT NULL AND
4470 l_visit_details_for_materials.start_date_time IS NOT NULL)
4471 THEN
4472 --Schedule material Reqmts in the Primary Visit for tasks created newly.
4473 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
4474 (
4475 p_api_version => p_api_version,
4476 p_init_msg_list => FND_API.g_false,
4477 p_commit => FND_API.g_false,
4478 p_validation_level => p_validation_level,--FND_API.g_valid_level_full,
4479 p_visit_id => l_primary_visit_id,
4480 p_visit_task_id => NULL,
4481 p_org_id => NULL,
4482 p_start_date => NULL,
4483 p_operation_flag => 'U',
4484 x_planned_order_flag => l_planned_order_flag ,
4485 x_return_status => l_return_status,
4486 x_msg_count => l_msg_count,
4487 x_msg_data => l_msg_data
4488 );
4489 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4490 fnd_log.string
4491 (
4492 fnd_log.level_statement,
4493 'ahl.plsql.'||L_FULL_NAME,
4494 ' After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials With p_operation_flag U '
4495 );
4496 END IF;
4497 ELSE
4498 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
4499 (
4500 p_api_version => p_api_version,
4501 p_init_msg_list => FND_API.g_false,
4502 p_commit => FND_API.g_false,
4503 p_validation_level => p_validation_level,--FND_API.g_valid_level_full,
4504 p_visit_id => l_primary_visit_id,
4505 p_visit_task_id => NULL,
4506 p_org_id => NULL,
4507 p_start_date => NULL,
4508 p_operation_flag => 'D',
4509 x_planned_order_flag => l_planned_order_flag ,
4510 x_return_status => l_return_status,
4511 x_msg_count => l_msg_count,
4512 x_msg_data => l_msg_data
4513 );
4514 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4515 fnd_log.string
4516 (
4517 fnd_log.level_statement,
4518 'ahl.plsql.'||L_FULL_NAME,
4519 ' After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials With p_operation_flag D '
4520 );
4521 END IF;
4522 END IF;
4523
4524 -- modification end
4525
4526 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
4527 x_return_status := FND_API.G_RET_STS_ERROR;
4528 RAISE FND_API.G_EXC_ERROR;
4529 END IF;
4530
4531 /* Only if Simulation Flag is not set we ought to delete the Simulation Visit otherwise it
4532 anyway be deleted in process Visit
4533 */
4534
4535 Remove_Visits_FR_Plan
4536 (
4537 p_api_version => p_api_version,
4538 p_init_msg_list => FND_API.g_false,--p_init_msg_list,
4539 p_commit => FND_API.g_false, --p_commit,
4540 p_validation_level => p_validation_level,
4541 p_module_type => p_module_type,
4542 p_visit_id => p_visit_id,
4543 p_plan_id => null,
4544 p_v_ovn => null,
4545 x_return_status => l_return_status,
4546 x_msg_count => l_msg_count,
4547 x_msg_data => l_msg_data
4548 );
4549 END IF;
4550
4551 --mpothuku End
4552
4553 -- Check Error Message stack.
4554 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4555 l_msg_count := FND_MSG_PUB.count_msg;
4556 IF l_msg_count > 0 THEN
4557 RAISE FND_API.G_EXC_ERROR;
4558 END IF;
4559 END IF;
4560 ---------------------------End of Body---------------------------------------
4561 --Standard check to count messages
4562 l_msg_count := Fnd_Msg_Pub.count_msg;
4563
4564 IF l_msg_count > 0 THEN
4565 X_msg_count := l_msg_count;
4566 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4567 RAISE Fnd_Api.G_EXC_ERROR;
4568 END IF;
4569
4570 --Standard check for commit
4571 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4572 COMMIT;
4573 END IF;
4574 -- Debug info
4575 IF G_DEBUG='Y' THEN
4576 Ahl_Debug_Pub.debug( 'End of private api Set visit Primary','+SMPLN+');
4577 -- Check if API is called in debug mode. If yes, disable debug.
4578 Ahl_Debug_Pub.disable_debug;
4579 END IF;
4580 --
4581 EXCEPTION
4582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4583 ROLLBACK TO set_visit_as_primary;
4584 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4585 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4586 p_count => x_msg_count,
4587 p_data => x_msg_data);
4588
4589 IF G_DEBUG='Y' THEN
4590 AHL_DEBUG_PUB.log_app_messages (
4591 x_msg_count, x_msg_data, 'ERROR' );
4592 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. set visit as primary','+SMPLN+');
4593 END IF;
4594 -- Check if API is called in debug mode. If yes, disable debug.
4595 AHL_DEBUG_PUB.disable_debug;
4596
4597 WHEN FND_API.G_EXC_ERROR THEN
4598 ROLLBACK TO set_visit_as_primary;
4599 X_return_status := FND_API.G_RET_STS_ERROR;
4600 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4601 p_count => x_msg_count,
4602 p_data => X_msg_data);
4603 -- Debug info.
4604 IF G_DEBUG='Y' THEN
4605 AHL_DEBUG_PUB.log_app_messages (
4606 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
4607 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.set visit as primary','+SMPLN+');
4608 END IF;
4609 -- Check if API is called in debug mode. If yes, disable debug.
4610 AHL_DEBUG_PUB.disable_debug;
4611
4612 WHEN OTHERS THEN
4613 ROLLBACK TO set_visit_as_primary;
4614 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4615 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4616 THEN
4617 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
4618 p_procedure_name => 'SET_VISIT_AS_PRIMARY',
4619 p_error_text => SUBSTR(SQLERRM,1,240));
4620 END IF;
4621 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4622 p_count => x_msg_count,
4623 p_data => X_msg_data);
4624
4625 -- Debug info.
4626 IF G_DEBUG='Y' THEN
4627 AHL_DEBUG_PUB.log_app_messages (
4628 x_msg_count, x_msg_data, 'SQL ERROR' );
4629 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. set visit as primary','+SMPLN+');
4630 END IF;
4631 -- Check if API is called in debug mode. If yes, disable debug.
4632 AHL_DEBUG_PUB.disable_debug;
4633 END Set_Visit_As_Primary;
4634 --
4635 --------------------------------------------------------------------
4636 -- PROCEDURE
4637 -- Delet_Simul_Visits
4638 --
4639 -- PURPOSE
4640 -- Procedure will be used to remove all the simulated visits. Will be
4641 -- Called from VWP beofre visit has been pushed to production
4642 --
4643 -- PARAMETERS
4644 -- p_visit_id : Primary Visit Id
4645 --
4646 -- NOTES
4647 --------------------------------------------------------------------
4648
4649 PROCEDURE Delete_Simul_Visits (
4650 p_api_version IN NUMBER,
4651 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4652 p_commit IN VARCHAR2 := FND_API.g_false,
4653 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4654 p_visit_id IN NUMBER,
4655 x_return_status OUT NOCOPY VARCHAR2,
4656 x_msg_count OUT NOCOPY NUMBER,
4657 x_msg_data OUT NOCOPY VARCHAR2)
4658 --
4659 IS
4660 -- Get visits belongs to simulation plans
4661 CURSOR Get_simul_visits_cur (C_VISIT_ID IN NUMBER)
4662 IS
4663 SELECT vt.visit_id, vt.visit_number,
4664 vt.asso_primary_visit_id
4665 FROM ahl_visits_vl vt, ahl_simulation_plans_vl sp
4666 WHERE vt.simulation_plan_id = sp.simulation_plan_id
4667 AND sp.primary_plan_flag = 'N'
4668 AND vt.asso_primary_visit_id = C_VISIT_ID;
4669 -- Get all the associated tasks
4670 CURSOR Get_simul_visit_tasks_cur(C_VISIT_ID IN NUMBER)
4671 IS
4672 SELECT visit_task_id
4673 FROM ahl_visit_tasks_vl
4674 WHERE visit_id = C_VISIT_ID;
4675
4676 --
4677
4678 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SIMUL_VISITS';
4679 l_api_version CONSTANT NUMBER := 1.0;
4680 l_msg_count NUMBER;
4681 l_msg_data VARCHAR2(2000);
4682 l_return_status VARCHAR2(1);
4683 l_simul_visits_rec Get_simul_visits_cur%ROWTYPE;
4684 l_simul_visit_tasks_rec Get_simul_visit_tasks_cur%ROWTYPE;
4685 l_visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type;
4686 l_visit_count NUMBER := 0;
4687 l_count NUMBER;
4688 l_space_assignment_id NUMBER;
4689 --
4690 BEGIN
4691 --------------------Initialize ----------------------------------
4692 -- Standard Start of API savepoint
4693 SAVEPOINT Delete_Simul_Visits;
4694 -- Check if API is called in debug mode. If yes, enable debug.
4695 IF G_DEBUG='Y' THEN
4696 AHL_DEBUG_PUB.enable_debug;
4697 END IF;
4698 -- Debug info.
4699 IF G_DEBUG='Y' THEN
4700 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Delete Simul Visits','+SMPNL+');
4701 END IF;
4702 -- Standard call to check for call compatibility.
4703 IF FND_API.to_boolean(p_init_msg_list)
4704 THEN
4705 FND_MSG_PUB.initialize;
4706 END IF;
4707 -- Initialize API return status to success
4708 x_return_status := FND_API.G_RET_STS_SUCCESS;
4709 -- Initialize message list if p_init_msg_list is set to TRUE.
4710 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4711 p_api_version,
4712 l_api_name,G_PKG_NAME)
4713 THEN
4714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4715 END IF;
4716
4717 ---------------------start API Body----------------------------------------
4718 /* Changes made by mpothuku on 12/22/04 to call the VWP API instead of
4719 direct deletion of tasks and Visits */
4720 -- Changes by mpothuku Begin
4721 -- Get all the visits associated
4722 OPEN Get_simul_visits_cur(p_visit_id);
4723 LOOP
4724 FETCH Get_simul_visits_cur INTO l_simul_visits_rec;
4725 EXIT WHEN Get_simul_visits_cur%NOTFOUND;
4726 IF l_simul_visits_rec.visit_id IS NOT NULL THEN
4727 Remove_Visits_FR_Plan (
4728 p_api_version => p_api_version,
4729 p_init_msg_list => FND_API.g_false,--p_init_msg_list,
4730 p_commit => FND_API.g_false, --p_commit,
4731 p_validation_level => p_validation_level,
4732 p_module_type => NULL,
4733 p_visit_id => l_simul_visits_rec.visit_id,
4734 p_plan_id => null,
4735 p_v_ovn => null,
4736 x_return_status => l_return_status,
4737 x_msg_count => l_msg_count,
4738 x_msg_data => l_msg_data);
4739
4740 -- Check Error Message stack.
4741 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4742 l_msg_count := FND_MSG_PUB.count_msg;
4743 IF l_msg_count > 0 THEN
4744 RAISE FND_API.G_EXC_ERROR;
4745 END IF;
4746 END IF;
4747 END IF; -- Visit not null
4748 END LOOP;
4749 CLOSE Get_simul_visits_cur;
4750 --
4751 -- mpothuku End
4752 ---------------------------End of Body---------------------------------------
4753 --Standard check to count messages
4754 l_msg_count := Fnd_Msg_Pub.count_msg;
4755
4756 IF l_msg_count > 0 THEN
4757 X_msg_count := l_msg_count;
4758 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4759 RAISE Fnd_Api.G_EXC_ERROR;
4760 END IF;
4761
4762 --Standard check for commit
4763 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4764 COMMIT;
4765 END IF;
4766 -- Debug info
4767 IF G_DEBUG='Y' THEN
4768 Ahl_Debug_Pub.debug( 'End of private api Delete_Simul_Visits','+SMPLN+');
4769 -- Check if API is called in debug mode. If yes, disable debug.
4770 Ahl_Debug_Pub.disable_debug;
4771 END IF;
4772 --
4773 EXCEPTION
4774 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4775 ROLLBACK TO Delete_Simul_Visits;
4776 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4777 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4778 p_count => x_msg_count,
4779 p_data => x_msg_data);
4780
4781 IF G_DEBUG='Y' THEN
4782 AHL_DEBUG_PUB.log_app_messages (
4783 x_msg_count, x_msg_data, 'ERROR' );
4784 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. Delete Simul Visits','+SMPLN+');
4785 END IF;
4786 -- Check if API is called in debug mode. If yes, disable debug.
4787 AHL_DEBUG_PUB.disable_debug;
4788
4789 WHEN FND_API.G_EXC_ERROR THEN
4790 ROLLBACK TO Delete_Simul_Visits;
4791 X_return_status := FND_API.G_RET_STS_ERROR;
4792 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4793 p_count => x_msg_count,
4794 p_data => X_msg_data);
4795 -- Debug info.
4796 IF G_DEBUG='Y' THEN
4797 AHL_DEBUG_PUB.log_app_messages (
4798 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
4799 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simul Visits','+SMPLN+');
4800 END IF;
4801 -- Check if API is called in debug mode. If yes, disable debug.
4802 AHL_DEBUG_PUB.disable_debug;
4803
4804 WHEN OTHERS THEN
4805 ROLLBACK TO Delete_Simul_Visits;
4806 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4807 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4808 THEN
4809 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SIMUL_PLAN_PVT',
4810 p_procedure_name => 'DELETE_SIMUL_VISITS',
4811 p_error_text => SUBSTR(SQLERRM,1,240));
4812 END IF;
4813 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4814 p_count => x_msg_count,
4815 p_data => X_msg_data);
4816 -- Debug info.
4817 IF G_DEBUG='Y' THEN
4818 AHL_DEBUG_PUB.log_app_messages (
4819 x_msg_count, x_msg_data, 'SQL ERROR' );
4820 AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. Delete Simul Visits','+SMPLN+');
4821 END IF;
4822 -- Check if API is called in debug mode. If yes, disable debug.
4823 AHL_DEBUG_PUB.disable_debug;
4824
4825 END Delete_Simul_Visits;
4826
4827
4828 --
4829 END AHL_LTP_SIMUL_PLAN_PVT;