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