1 PACKAGE BODY AHL_VWP_VISITS_STAGES_PVT AS
2 /* $Header: AHLVSTGB.pls 120.0 2005/05/26 01:54:14 appldev noship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AHL_VWP_VISITS_STAGES_PVT
7 --
8 -- PURPOSE
9 -- This package specification is a Private API for managing
10 -- Planning --> Visit Work Package --> VISITS --> STAGES
11 -- related procedures in Complex Maintainance, Repair and Overhauling(CMRO).
12 --
13 -- It defines used pl/sql records and tables datatypes
14 --
15 --
16 -- NOTES
17 --
18 --
19 -- HISTORY
20 -- 04-FEB-2004 ADHARIA POST 11.5.10 Created.
21
22 -----------------------------------------------------------------
23 -- Define Global CONSTANTS --
24 -----------------------------------------------------------------
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_VISIT_STAGES_PVT';
26 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
27 ---------------------------------------------------------------------
28 -- Define Record Types for record structures needed by the APIs --
29 ---------------------------------------------------------------------
30 -- NO RECORD TYPES *************
31
32 --------------------------------------------------------------------
33 -- Define Table Type for Records Structures --
34 --------------------------------------------------------------------
35 TYPE Dept_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
36
37 --------------------------------------------------------------------
38 -- START: Defining local functions and procedures SIGNATURES --
39 --------------------------------------------------------------------
40 FUNCTION Get_Stage_Id
41 RETURN NUMBER;
42
43 PROCEDURE VALIDATE_STAGES(
44 p_visit_id in number,
45 p_stages_rec IN Visit_Stages_Rec_Type,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2
49 );
50
51 procedure default_missing_attributes(
52 p_x_stages_rec IN OUT NOCOPY Visit_Stages_Rec_Type
53 );
54
55 --Added by amagrawa
56 PROCEDURE Validate_bef_Times_Derive
57 ( p_visit_id IN NUMBER,
58 x_valid_flag OUT NOCOPY VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_error_msg_code OUT NOCOPY VARCHAR2
61 );
62 --End of changes by amagrawa
63 --------------------------------------------------------------------
64 -- END: Defining local functions and procedures SIGNATURES --
65 --------------------------------------------------------------------
66
67 -- ****************************************************************
68
69 --------------------------------------------------------------------
70 -- START: Defining local functions and procedures BODY --
71 --------------------------------------------------------------------
72
73 --------------------------------------------------------------------
74 -- END: Defining local functions and procedures BODY --
75 --------------------------------------------------------------------
76
77 -- *************************************************************
78
79 ----------------------------------------------------------------------
80 -- START: Defining procedures BODY, which are called from UI screen --
81 ----------------------------------------------------------------------
82
83 --------------------------------------------------------------------
84 -- PROCEDURE
85 -- Get_Stages_Details
86 --
87 -- PURPOSE
88 -- Get a particular Stage Records with all details
89 --------------------------------------------------------------------
90 PROCEDURE Get_Stages_Details (
91 p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
93 p_commit IN VARCHAR2 := Fnd_Api.g_false,
94 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
95 p_module_type IN VARCHAR2 := 'JSP',
96 p_visit_id IN NUMBER,
97 p_start_row IN NUMBER,
98 p_rows_per_page IN NUMBER,
99
100
101 x_stages_tbl OUT NOCOPY Visit_stages_tbl_Type,
102 x_row_count OUT NOCOPY NUMBER,
103
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2
107 )
108 IS
109
110 L_API_VERSION CONSTANT NUMBER := 1.0;
111 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Stages_Details';
112 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
113
114 l_msg_data VARCHAR2(2000);
115 l_return_status VARCHAR2(1);
116 l_valid_flag VARCHAR2(1) := 'N';
117 l_stages_tbl Visit_stages_tbl_Type;
118 l_st_ind number := 0;
119 l_dept_id number;
120 l_visit_start_date DATE;
121
122 l_cum_duration NUMBER :=0;
123
124
125 -- To find visit related information
126 CURSOR c_visit (x_id IN NUMBER)
127 IS
128 SELECT START_DATE_TIME , department_id FROM AHL_VISITS_VL
129 WHERE VISIT_ID = x_id;
130 --
131 CURSOR C_STAGE(C_VISIT_ID number)
132 is
133 select s.stage_id, s.stage_num, s.stage_name,
134 s.object_version_number, s.duration
135 from ahl_vwp_stages_vl s
136 where s.visit_id = c_visit_id
137 order by s.stage_num;
138
139 l_stage_rec C_STAGE%rowtype;
140
141 CURSOR C_STAGE_DATE(C_STAGE_ID number)
142 is
143 select vt.stage_id,
144 -- sum(s.duration) over(order by s.stage_num) CUMUL_DURATION,
145 -- min(vt.start_date_time) start_date_time,
146 max(vt.end_date_time) end_date_time
147 from ahl_visit_tasks_b vt
148 where vt.stage_id = C_STAGE_ID
149 AND nvl(vt.status_code,'X') <> 'DELETED'
150 group by vt.stage_id;
151
152 l_stage_date_rec c_stage_date%rowtype;
153
154 BEGIN
155 -- Standard start of API savepoint
156 SAVEPOINT Get_Stages_Details;
157
158 -- Check if API is called in debug mode. If yes, enable debug.
159 IF G_DEBUG='Y' THEN
160 AHL_DEBUG_PUB.enable_debug;
161 END IF;
162
163 -- Debug info.
164 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
165 IF G_DEBUG='Y' THEN
166 AHL_DEBUG_PUB.debug( l_full_name ||':*****Start*****');
167 END IF;
168 END IF;
169
170 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
171 Fnd_Msg_Pub.initialize;
172 END IF;
173
174 -- Initialize API return status to success
175 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
176
177 -- Standard call to check for call compatibility.
178 IF NOT Fnd_Api.compatible_api_call(
179 l_api_version,
180 p_api_version,
181 l_api_name,
182 G_PKG_NAME
183 ) THEN
184 RAISE Fnd_Api.g_exc_unexpected_error;
185 END IF;
186
187
188
189 ------------------------Start of API ----------------------
190 x_row_count := 0;
191 -- Cursor to find visit start time
192 OPEN c_visit (p_visit_id);
193 FETCH c_visit INTO l_visit_start_date,l_dept_id;
194 CLOSE c_visit;
195
196 --Added by amagrawa
197 Validate_bef_Times_Derive
198 (p_visit_id => p_visit_id,
199 x_valid_flag => l_valid_flag,
200 x_return_status => l_return_status,
201 x_error_msg_code => l_msg_data);
202 -- To check if
203 -- Modified by amagrawa as per review comments.
204 IF(l_valid_flag ='N') THEN
205
206 FOR l_stage_rec IN C_STAGE(P_VISIT_ID)
207 LOOP
208
209 l_st_ind := l_stage_rec.stage_num -1;
210
211 l_stages_tbl(l_st_ind).stage_id := l_stage_rec.stage_id;
212 l_stages_tbl(l_st_ind).stage_num := l_stage_rec.stage_num;
213 l_stages_tbl(l_st_ind).stage_name := l_stage_rec.stage_name;
214 l_stages_tbl(l_st_ind).object_version_number := l_stage_rec.object_version_number;
215 l_stages_tbl(l_st_ind).duration := l_stage_rec.duration;
216 l_stages_tbl(l_st_ind).stage_planned_start_time :=null;
217 l_stages_tbl(l_st_ind).stage_planned_end_time := null;
218 l_stages_tbl(l_st_ind).Stage_Actual_End_Time := null;
219
220 END LOOP;
221 ELSE --Return Status = 'S'
222 -- End of changes by amagrawa
223 FOR l_stage_rec IN C_STAGE(P_VISIT_ID)
224 LOOP
225
226 l_st_ind := l_stage_rec.stage_num -1;
227 l_cum_duration := l_cum_duration + l_stage_rec.duration;
228
229 l_stages_tbl(l_st_ind).stage_id := l_stage_rec.stage_id;
230 l_stages_tbl(l_st_ind).stage_num := l_stage_rec.stage_num;
231 l_stages_tbl(l_st_ind).stage_name := l_stage_rec.stage_name;
232 l_stages_tbl(l_st_ind).object_version_number := l_stage_rec.object_version_number;
233 l_stages_tbl(l_st_ind).duration := l_stage_rec.duration;
234 l_stages_tbl(l_st_ind).stage_planned_start_time :=
235 AHL_VWP_TIMES_PVT.compute_date(l_visit_start_date, l_dept_id, l_cum_duration - l_stage_rec.duration);
236 l_stages_tbl(l_st_ind).stage_planned_end_time :=
237 AHL_VWP_TIMES_PVT.compute_date(l_visit_start_date, l_dept_id, l_cum_duration );
238
239 l_stage_date_rec := null;
240
241 OPEN C_STAGE_DATE(l_stage_rec.stage_id);
242 FETCH C_STAGE_DATE INTO l_stage_date_rec;
243 CLOSE C_STAGE_DATE;
244
245 l_stages_tbl(l_st_ind).Stage_Actual_End_Time := l_stage_date_rec.end_date_time;
246
247 END LOOP;
248 END IF; --start and dept not null
249 -- Modified by amagrawa as per review comments.
250 x_row_count := l_stages_tbl.count;
251 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
252
253 --------------------------
254 x_stages_tbl := l_stages_tbl;
255
256 ------------------------End of API Body------------------------------------
257 -- Standard call to get message count and if count is 1, get message info
258 Fnd_Msg_Pub.Count_And_Get
259 ( p_count => x_msg_count,
260 p_data => x_msg_data,
261 p_encoded => Fnd_Api.g_false);
262
263 -- Check if API is called in debug mode. If yes, enable debug.
264 IF G_DEBUG='Y' THEN
265 AHL_DEBUG_PUB.enable_debug;
266 END IF;
267
268 -- Debug info.
269 -- Commented by amagrawa as per review comments.
270 -- IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
271 IF G_DEBUG='Y' THEN
272 AHL_DEBUG_PUB.debug(L_FULL_NAME||'AHL_VWP_Tasks_PVT - End');
273 END IF;
274 -- END IF;
275
276 -- Check if API is called in debug mode. If yes, disable debug.
277 IF G_DEBUG='Y' THEN
278 AHL_DEBUG_PUB.disable_debug;
279 END IF;
280 RETURN;
281
282 EXCEPTION
283 WHEN Fnd_Api.G_EXC_ERROR THEN
284 x_return_status := Fnd_Api.G_RET_STS_ERROR;
285 ROLLBACK TO Get_Stages_Details;
286 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
287 p_data => x_msg_data,
288 p_encoded => Fnd_Api.g_false);
289 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
290 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
291 ROLLBACK TO Get_Stages_Details;
292 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
293 p_data => x_msg_data,
294 p_encoded => Fnd_Api.g_false);
295 WHEN OTHERS THEN
296 ROLLBACK TO Get_Stages_Details;
297 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
298 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
299 THEN
300 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
301 END IF;
302 Fnd_Msg_Pub.count_and_get (
303 p_encoded => Fnd_Api.g_false,
304 p_count => x_msg_count,
305 p_data => x_msg_data );
306
307
308 END Get_Stages_Details;
309
310
311 --------------------------------------------------------------------
312 -- PROCEDURE
313 -- Create_Stages
314 --
315 -- PURPOSE
316 -- To create a Stage for visit based on the profile value set.
317 -- this procedure defaults the stage_num and stage_name to count and duration to 0.
318 -- will be called by create_visit and only the visit_id is passed.
319 --------------------------------------------------------------------
320 PROCEDURE Create_Stages (
321 p_api_version IN NUMBER,
322 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
323 p_commit IN VARCHAR2 := Fnd_Api.g_false,
324 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
325 p_module_type IN VARCHAR2 := 'JSP',
326
327 p_visit_id IN NUMBER,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2
331 )
332 IS
333 -- Define local Variables
334 L_API_VERSION CONSTANT NUMBER := 1.0;
335 L_API_NAME CONSTANT VARCHAR2(30) := 'Create Stages';
336 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
337
338 l_msg_data VARCHAR2(2000);
339 l_msg_count NUMBER;
340
341 l_stage_count NUMBER;
342 l_rowid ROWID;
343
344
345 BEGIN
346 --------------------- Initialize -----------------------
347 SAVEPOINT Create_Stage;
348
349 -- Check if API is called in debug mode. If yes, enable debug.
350 IF G_DEBUG='Y' THEN
351 Ahl_Debug_Pub.enable_debug;
352 END IF;
353
354 -- Debug info.
355 IF G_DEBUG='Y' THEN
356 AHL_DEBUG_PUB.Debug( l_full_name ||': Start');
357 END IF;
358
359 -- Initialize message list if p_init_msg_list is set to TRUE.
360 IF Fnd_Api.to_boolean(p_init_msg_list)
361 THEN
362 Fnd_Msg_Pub.initialize;
363 END IF;
364
365 -- Initialize API return status to success
366 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
367
368 -- Standard call to check for call compatibility.
369 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
370 p_api_version,
371 l_api_name,G_PKG_NAME)
372 THEN
373 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 ------------------------Start of API Body------------------------------------
377
378 --------------------Value OR ID conversion---------------------------
379 /* not reqd;
380 IF p_module_type = 'JSP'
381 THEN
382 -- do nothing;
383 END IF;
384 */
385
386 -------------------------------- Validate -----------------------------------------
387
388 IF G_DEBUG='Y' THEN
389 AHL_DEBUG_PUB.Debug( l_full_name ||':START VALIDATE');
390 END IF;
391
392 --
393 -- Check for the ID.
394 --
395 IF (P_VISIT_ID = Fnd_Api.g_miss_num OR P_VISIT_ID IS Null)
396 THEN
397 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_INVALID');
398 Fnd_Msg_Pub.ADD;
399 END IF;
400
401 --
402 -- Check profile
403 --
404 l_stage_count := FND_PROFILE.value('AHL_NUMBER_OF_STAGES');
405 -- Modified by amagrawa as per review comments.
406 If l_stage_count is null
407 then
408 Fnd_Message.SET_NAME('AHL','AHL_VWP_ST_PROFILE_NOT_DEF');
409 Fnd_Msg_Pub.ADD;
410 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
411 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
412 ELSIF l_stage_count <= 0
413 then
414 Fnd_Message.SET_NAME('AHL','AHL_VWP_ST_PROFILE_GT_ZERO');
415 Fnd_Msg_Pub.ADD;
416 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
417 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
418 ELSIF l_stage_count <> floor(l_stage_count)
419 then
420 Fnd_Message.SET_NAME('AHL','AHL_VWP_ST_PROFILE_NOT_INT');
421 Fnd_Msg_Pub.ADD;
422 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
423 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425
426
427 /* --Standard check to count messages
428 l_msg_count := Fnd_Msg_Pub.count_msg;
429
430 IF l_msg_count > 0 THEN
431 x_msg_count := l_msg_count;
432 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
433 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
434 END IF;
435 */
436 IF G_DEBUG='Y' THEN
437 AHL_DEBUG_PUB.Debug( l_full_name ||':END VALIDATE');
438 END IF;
439
440 -------------------------- Insert --------------------------
441 IF G_DEBUG='Y' THEN
442 AHL_DEBUG_PUB.Debug( l_full_name ||':Insert');
443 END IF;
444
445
446 FOR I IN 1..L_STAGE_COUNT
447 LOOP
448
449 -- Invoke the table handler to create a record
450 --
451
452 Ahl_VWP_Stages_Pkg.Insert_Row (
453 X_ROWID => l_rowid,
454 X_VISIT_ID => P_VISIT_ID,
455 X_STAGE_ID => Get_Stage_Id,
456 X_STAGE_NUM => i,
457 X_STAGE_NAME => i,
458 X_DURATION => 0,
459 X_OBJECT_VERSION_NUMBER => 1,
460
461 X_ATTRIBUTE_CATEGORY => NULL,
462 X_ATTRIBUTE1 => NULL ,
463 X_ATTRIBUTE2 => NULL ,
464 X_ATTRIBUTE3 => NULL ,
465 X_ATTRIBUTE4 => NULL ,
466 X_ATTRIBUTE5 => NULL ,
467 X_ATTRIBUTE6 => NULL ,
468 X_ATTRIBUTE7 => NULL ,
469 X_ATTRIBUTE8 => NULL ,
470 X_ATTRIBUTE9 => NULL ,
471 X_ATTRIBUTE10 => NULL ,
472 X_ATTRIBUTE11 => NULL ,
473 X_ATTRIBUTE12 => NULL ,
474 X_ATTRIBUTE13 => NULL ,
475 X_ATTRIBUTE14 => NULL ,
476 X_ATTRIBUTE15 => NULL ,
477
478 X_CREATION_DATE => SYSDATE,
479 X_CREATED_BY => Fnd_Global.USER_ID,
480 X_LAST_UPDATE_DATE => SYSDATE,
481 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
482 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
483
484 IF G_DEBUG='Y' THEN
485 AHL_DEBUG_PUB.Debug( l_full_name ||': Visit ID =' || P_VISIT_ID);
486 AHL_DEBUG_PUB.Debug( l_full_name ||': STAGE Number =' || I);
487 END IF;
488 END LOOP;
489
490 ---------------------------End of API Body---------------------------------------
491 --Standard check to count messages
492 l_msg_count := Fnd_Msg_Pub.count_msg;
493
494 IF l_msg_count > 0 THEN
495 X_msg_count := l_msg_count;
496 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
497 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499
500 --Standard check for commit
501 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
502 COMMIT;
503 END IF;
504
505 IF G_DEBUG='Y' THEN
506 AHL_DEBUG_PUB.Debug( l_full_name ||':End');
507 END IF;
508
509 -- Check if API is called in debug mode. If yes, disable debug.
510 IF G_DEBUG='Y' THEN
511 Ahl_Debug_Pub.disable_debug;
512 END IF;
513 EXCEPTION
514 WHEN Fnd_Api.g_exc_error THEN
515 ROLLBACK TO Create_Stage;
516 x_return_status := Fnd_Api.g_ret_sts_error;
517 Fnd_Msg_Pub.count_and_get(
518 p_encoded => Fnd_Api.g_false,
519 p_count => x_msg_count,
520 p_data => x_msg_data
521 );
522 WHEN Fnd_Api.g_exc_unexpected_error THEN
523 ROLLBACK TO Create_Stage;
524 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
525 Fnd_Msg_Pub.count_and_get (
526 p_encoded => Fnd_Api.g_false,
527 p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530 WHEN OTHERS THEN
531 ROLLBACK TO Create_Stage;
532 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
533 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
534 THEN
535 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
536 END IF;
537 Fnd_Msg_Pub.count_and_get (
538 p_encoded => Fnd_Api.g_false,
539 p_count => x_msg_count,
540 p_data => x_msg_data
541 );
542 END Create_StageS;
543
544
545 --------------------------------------------------------------------
546 -- PROCEDURE
547 -- Update_Stages
548 --
549 -- PURPOSE
550 -- To create a Stage for visit based on the profile value set.
551 -- this procedure defaults the stage_num and stage_name to count and duration to 0.
552 -- will be called by create_visit and only the visit_id is passed.
553 --------------------------------------------------------------------
554 PROCEDURE Update_Stages (
555 p_api_version IN NUMBER,
556 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
557 p_commit IN VARCHAR2 := Fnd_Api.g_false,
558 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
559 p_module_type IN VARCHAR2 := 'JSP',
560
561 p_visit_id IN NUMBER,
562 p_x_stages_tbl IN OUT NOCOPY Visit_Stages_Tbl_Type,
563
564 x_return_status OUT NOCOPY VARCHAR2,
565 x_msg_count OUT NOCOPY NUMBER,
566 x_msg_data OUT NOCOPY VARCHAR2
567 )
568 IS
569 -- Define local Variables
570 L_API_VERSION CONSTANT NUMBER := 1.0;
571 L_API_NAME CONSTANT VARCHAR2(30) := 'Update Stages';
572 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
573
574 l_msg_data VARCHAR2(2000);
575 l_msg_count NUMBER;
576 l_dummy varchar2(1);
577 l_visit_status varchar2(30);
578 l_return_status varchar2(1);
579 l_validate_status varchar2(1);
580
581 l_planned_order_flag VARCHAR2(1);
582
583 CURSOR c_check_visit_status(C_VISIT_ID NUMBER) IS
584 SELECT status_code FROM AHL_VISITS_B
585 WHERE VISIT_ID = C_VISIT_ID
586 AND STATUS_CODE IN ('PLANNING', 'PARTIALLY RELEASED', 'RELEASED' );
587
588 -- Commented based on review comments
589 /*CURSOR C_JOB(C_VISIT_ID NUMBER , C_STAGE_NUM NUMBER )
590 IS
591 select 'x' from ahl_workorders_v
592 where visit_task_id in
593 (select DISTINCT VISIT_TASK_ID from AHL_VISIT_TASKS_B
594 where visit_id = C_VISIT_ID
595 and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM
596 AND VISIT_ID = C_VISIT_ID))
597 and ( job_status_code =3 or firm_planned_flag = 1 );
598 */
599
600 -- To find task related information
601
602 l_visit_end_date DATE;
603
604 BEGIN
605 --------------------- Initialize -----------------------
606 SAVEPOINT Update_Stages;
607
608 -- Check if API is called in debug mode. If yes, enable debug.
609 IF G_DEBUG='Y' THEN
610 Ahl_Debug_Pub.enable_debug;
611 END IF;
612
613 -- Debug info.
614 IF G_DEBUG='Y' THEN
615 AHL_DEBUG_PUB.Debug( l_full_name ||': Start');
616 END IF;
617
618 -- Initialize message list if p_init_msg_list is set to TRUE.
619 IF Fnd_Api.to_boolean(p_init_msg_list)
620 THEN
621 Fnd_Msg_Pub.initialize;
622 END IF;
623
624 -- Initialize API return status to success
625 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
626
627 -- Standard call to check for call compatibility.
628 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
629 p_api_version,
630 l_api_name,G_PKG_NAME)
631 THEN
632 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
633 END IF;
634
635 ------------------------Start of API Body------------------------------------
636
637 --
638 -- Check for the ID.
639 --
640 IF (P_VISIT_ID = Fnd_Api.g_miss_num OR P_VISIT_ID IS Null)
641 THEN
642 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_NOT_FOUND');
643 Fnd_Msg_Pub.ADD;
644 -- Added by amagrawa based on review commenst
645 RAISE Fnd_Api.G_EXC_ERROR;
646 END IF;
647
648
649 --verify if visit status is planning or released or partially_released
650 open c_check_visit_status(p_visit_id);
651 fetch c_check_visit_status into l_visit_status;
652 if c_check_visit_status%notfound
653 then
654 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_STATUS_INV');
655 Fnd_Msg_Pub.ADD;
656 -- Added by amagrawa based on review commenst
657 close c_check_visit_status;
658 RAISE Fnd_Api.G_EXC_ERROR;
659 end if;
660 close c_check_visit_status;
661
662 -- Commented by amagrawa as per review commenst.
663 /* --Standard check to count messages
664 l_msg_count := Fnd_Msg_Pub.count_msg;
665
666 IF l_msg_count > 0 THEN
667 x_msg_count := l_msg_count;
668 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
669 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
670 END IF;
671 */
672 -------------------------- Update --------------------------
673 IF G_DEBUG='Y' THEN
674 AHL_DEBUG_PUB.Debug( l_full_name ||':Insert');
675 END IF;
676
677
678 FOR i IN p_x_stages_tbl.FIRST..p_x_stages_tbl.LAST
679 loop
680
681 IF G_DEBUG='Y' THEN
682 AHL_DEBUG_PUB.Debug( l_full_name ||':START VALIDATE');
683 END IF;
684
685 -------------------------------- Validate -----------------------------------------
686 l_validate_status := Fnd_Api.G_RET_STS_SUCCESS;
687
688 default_missing_attributes(p_x_stages_tbl(i));
689
690
691
692 VALIDATE_STAGES(
693 p_visit_id => p_visit_id,
694 p_stages_rec => p_x_stages_tbl(i),
695 x_return_status => l_validate_status,
696 x_msg_count => x_msg_count,
697 x_msg_data => x_msg_data
698 );
699 -- Added be amagrawa based on review comments
700 IF l_validate_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
701 RAISE Fnd_Api.G_EXC_ERROR;
702 END IF;
703
704 IF G_DEBUG='Y' THEN
705 AHL_DEBUG_PUB.Debug( l_full_name ||':END VALIDATE');
706 END IF;
707
708 -- Commented by amagrawa based on review comments
709 /* -- VALIDATE IF JOB IN SUBSEQUENT STAGE IS FIRMED OR RELEASED
710 IF l_visit_status IN ('RELEASED' , 'PARTIALLY RELEASED')
711 THEN
712 OPEN C_JOB(P_VISIT_ID, p_x_stages_tbl(i).STAGE_NUM );
713 FETCH C_JOB INTO L_DUMMY;
714 IF C_JOB%FOUND
715 THEN
716 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_JOB_FIRM_REL');
717 Fnd_Message.SET_TOKEN('STAGE_NAME', p_x_stages_tbl(i).stage_name);
718 Fnd_Msg_Pub.ADD;
719 l_validate_status := Fnd_Api.G_RET_STS_ERROR;
720 END IF;
721 CLOSE C_JOB;
722 END IF;
723 /* Uncommented as per Stages test case STG14 : Removed by Senthil for TC */
724
725 ----------------------------- IF NO ERRORS UPDATE-----------------------------------------
726 if l_validate_status = Fnd_Api.G_RET_STS_SUCCESS
727 then
728 -- Invoke the table handler to update the record
729 --
730 Ahl_VWP_stages_Pkg.Update_Row (
731 X_VISIT_ID => P_VISIT_ID,
732 X_STAGE_ID => p_x_stages_tbl(i).STAGE_ID,
733 X_STAGE_NUM => p_x_stages_tbl(i).STAGE_NUM,
734 X_STAGE_NAME => p_x_stages_tbl(i).STAGE_NAME,
735 X_DURATION => p_x_stages_tbl(i).DURATION,
736 X_OBJECT_VERSION_NUMBER => p_x_stages_tbl(i).OBJECT_VERSION_NUMBER+1,
737 X_ATTRIBUTE_CATEGORY => p_x_stages_tbl(i).ATTRIBUTE_CATEGORY,
738 X_ATTRIBUTE1 => p_x_stages_tbl(i).ATTRIBUTE1,
739 X_ATTRIBUTE2 => p_x_stages_tbl(i).ATTRIBUTE2,
740 X_ATTRIBUTE3 => p_x_stages_tbl(i).ATTRIBUTE3,
741 X_ATTRIBUTE4 => p_x_stages_tbl(i).ATTRIBUTE4,
742 X_ATTRIBUTE5 => p_x_stages_tbl(i).ATTRIBUTE5,
743 X_ATTRIBUTE6 => p_x_stages_tbl(i).ATTRIBUTE6,
744 X_ATTRIBUTE7 => p_x_stages_tbl(i).ATTRIBUTE7,
745 X_ATTRIBUTE8 => p_x_stages_tbl(i).ATTRIBUTE8,
746 X_ATTRIBUTE9 => p_x_stages_tbl(i).ATTRIBUTE9,
747 X_ATTRIBUTE10 => p_x_stages_tbl(i).ATTRIBUTE10,
748 X_ATTRIBUTE11 => p_x_stages_tbl(i).ATTRIBUTE11,
749 X_ATTRIBUTE12 => p_x_stages_tbl(i).ATTRIBUTE12,
750 X_ATTRIBUTE13 => p_x_stages_tbl(i).ATTRIBUTE13,
751 X_ATTRIBUTE14 => p_x_stages_tbl(i).ATTRIBUTE14,
752 X_ATTRIBUTE15 => p_x_stages_tbl(i).ATTRIBUTE15,
753 X_LAST_UPDATE_DATE => SYSDATE,
754 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
755 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
756
757 IF G_DEBUG='Y' THEN
758 AHL_DEBUG_PUB.Debug( l_full_name ||': Visit ID =' || P_VISIT_ID);
759 AHL_DEBUG_PUB.Debug( l_full_name ||': STAGE Number =' || p_x_stages_tbl(i).stage_num);
760 END IF;
761 -- Added by amagrawa after review comments.
762 ELSE -- If validate_status is <> 'S'
763 RAISE Fnd_Api.G_EXC_ERROR;
764 END IF; -- end check of validate_status
765 END LOOP;
766
767 ---------------------------End of API Body---------------------------------------
768
769 -- Added cxcheng POST11510--------------
770 --Now adjust the times derivation for task
771 AHL_VWP_TIMES_PVT.Calculate_Task_Times(p_api_version => 1.0,
772 p_init_msg_list => Fnd_Api.G_FALSE,
773 p_commit => Fnd_Api.G_FALSE,
774 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
775 x_return_status => l_return_status,
776 x_msg_count => l_msg_count,
777 x_msg_data => l_msg_data,
778 p_visit_id => p_visit_id);
779 -- Added by amagrawa based on review comments.
780 IF l_return_Status <>'S'
781 THEN
782 IF l_return_Status = FND_API.G_RET_STS_ERROR
783 THEN
784 RAISE FND_API.G_EXC_ERROR;
785 ELSIF l_return_Status = FND_API.G_RET_STS_UNEXP_ERROR
786 THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789
790 END IF;
791
792
793 l_visit_end_date:= AHL_VWP_TIMES_PVT.get_visit_end_time(p_visit_id);
794
795 IF l_visit_end_date IS NOT NULL THEN
796
797 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
798 (p_api_version => p_api_version,
799 p_init_msg_list => Fnd_Api.G_FALSE,
800 p_commit => Fnd_Api.G_FALSE,
801 p_visit_id => p_visit_id,
802 p_visit_task_id => NULL,
803 p_org_id => NULL,
804 p_start_date => NULL,
805 p_operation_flag => 'U',
806
807 x_planned_order_flag => l_planned_order_flag ,
808 x_return_status => l_return_status,
809 x_msg_count => l_msg_count,
810 x_msg_data => l_msg_data);
811
812 IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
813 X_msg_count := l_msg_count;
814 X_return_status := Fnd_Api.G_RET_STS_ERROR;
815 RAISE Fnd_Api.G_EXC_ERROR;
816 END IF;
817
818
819 END IF;
820
821
822 --Standard check to count messages
823 l_msg_count := Fnd_Msg_Pub.count_msg;
824
825 IF l_msg_count > 0 THEN
826 X_msg_count := l_msg_count;
827 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
828 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
829 END IF;
830
831 --Standard check for commit
832 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
833 COMMIT;
834 END IF;
835
836 IF G_DEBUG='Y' THEN
837 AHL_DEBUG_PUB.Debug( l_full_name ||':End');
838 END IF;
839
840 -- Check if API is called in debug mode. If yes, disable debug.
841 IF G_DEBUG='Y' THEN
842 Ahl_Debug_Pub.disable_debug;
843 END IF;
844 EXCEPTION
845 WHEN Fnd_Api.g_exc_error THEN
846 ROLLBACK TO Update_Stages;
847 x_return_status := Fnd_Api.g_ret_sts_error;
848 Fnd_Msg_Pub.count_and_get(
849 p_encoded => Fnd_Api.g_false,
850 p_count => x_msg_count,
851 p_data => x_msg_data
852 );
853 WHEN Fnd_Api.g_exc_unexpected_error THEN
854 ROLLBACK TO Update_Stages;
855 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
856 Fnd_Msg_Pub.count_and_get (
857 p_encoded => Fnd_Api.g_false,
858 p_count => x_msg_count,
859 p_data => x_msg_data
860 );
861 WHEN OTHERS THEN
862 ROLLBACK TO Update_Stages;
863 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
864 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
865 THEN
866 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
867 END IF;
868 Fnd_Msg_Pub.count_and_get (
869 p_encoded => Fnd_Api.g_false,
870 p_count => x_msg_count,
871 p_data => x_msg_data
872 );
873 END Update_Stages;
874
875 --------------------------------------------------------------------
876 -- PROCEDURE
877 -- Delete_Stages
878 --
879 -- PURPOSE
880 -- To delete a Stage for visit.
881 -- will be called from delete visit and requires only visit_id
882 --------------------------------------------------------------------
883 PROCEDURE Delete_Stages (
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
886 p_commit IN VARCHAR2 := Fnd_Api.g_false,
887 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
888 p_module_type IN VARCHAR2 := 'JSP',
889
890 p_visit_id IN NUMBER,
891
892 x_return_status OUT NOCOPY VARCHAR2,
893 x_msg_count OUT NOCOPY NUMBER,
894 x_msg_data OUT NOCOPY VARCHAR2
895 )
896 is
897
898 -- Define local Variables
899 L_API_VERSION CONSTANT NUMBER := 1.0;
900 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete Stages';
901 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
902 l_msg_count NUMBER;
903
904 begin
905 --------------------- Initialize -----------------------
906 SAVEPOINT Delete_Stages;
907
908 -- Check if API is called in debug mode. If yes, enable debug.
909 IF G_DEBUG='Y' THEN
910 Ahl_Debug_Pub.enable_debug;
911 END IF;
912
913 -- Debug info.
914 IF G_DEBUG='Y' THEN
915 AHL_DEBUG_PUB.Debug( l_full_name ||': Start');
916 END IF;
917
918 -- Initialize message list if p_init_msg_list is set to TRUE.
919 IF Fnd_Api.to_boolean(p_init_msg_list)
920 THEN
921 Fnd_Msg_Pub.initialize;
922 END IF;
923
924 -- Initialize API return status to success
925 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
926
927 -- Standard call to check for call compatibility.
928 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
929 p_api_version,
930 l_api_name,G_PKG_NAME)
931 THEN
932 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934
935 ------------------------Start of API Body------------------------------------
936 -- directly delete as we need to delete all stages for the visit
937
938 delete from AHL_VWP_STAGES_TL
939 where stage_id
940 in (select stage_id from ahl_vwp_stages_b
941 where visit_id = p_visit_id);
942
943 delete from AHL_VWP_STAGES_B
944 where visit_id = p_visit_id;
945
946 -- directly delete as we need to delete all stages for the visit
947
948 ---------------------------End of API Body---------------------------------------
949 --Standard check to count messages
950 l_msg_count := Fnd_Msg_Pub.count_msg;
951
952 IF l_msg_count > 0 THEN
953 X_msg_count := l_msg_count;
954 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
955 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
956 END IF;
957
958 --Standard check for commit
959 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
960 COMMIT;
961 END IF;
962
963 IF G_DEBUG='Y' THEN
964 AHL_DEBUG_PUB.Debug( l_full_name ||':End');
965 END IF;
966
967 -- Check if API is called in debug mode. If yes, disable debug.
968 IF G_DEBUG='Y' THEN
969 Ahl_Debug_Pub.disable_debug;
970 END IF;
971 EXCEPTION
972 WHEN Fnd_Api.g_exc_error THEN
973 ROLLBACK TO Delete_Stages;
974 x_return_status := Fnd_Api.g_ret_sts_error;
975 Fnd_Msg_Pub.count_and_get(
976 p_encoded => Fnd_Api.g_false,
977 p_count => x_msg_count,
978 p_data => x_msg_data
979 );
980 WHEN Fnd_Api.g_exc_unexpected_error THEN
981 ROLLBACK TO Delete_Stages;
982 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
983 Fnd_Msg_Pub.count_and_get (
984 p_encoded => Fnd_Api.g_false,
985 p_count => x_msg_count,
986 p_data => x_msg_data
987 );
988 WHEN OTHERS THEN
989 ROLLBACK TO Delete_Stages;
990 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
991 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
992 THEN
993 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
994 END IF;
995 Fnd_Msg_Pub.count_and_get (
996 p_encoded => Fnd_Api.g_false,
997 p_count => x_msg_count,
998 p_data => x_msg_data
999 );
1000
1001
1002 end delete_stages;
1003
1004
1005
1006 ----------------------------------------------------------------------
1007 -- END: Defining procedures BODY, which are called from UI screen --
1008 ----------------------------------------------------------------------
1009
1010
1011 PROCEDURE VALIDATE_STAGES(
1012 p_visit_id in number,
1013 p_stages_rec IN Visit_Stages_Rec_Type,
1014 x_return_status OUT NOCOPY VARCHAR2,
1015 x_msg_count OUT NOCOPY NUMBER,
1016 x_msg_data OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019 -- Define local Variables
1020 L_API_VERSION CONSTANT NUMBER := 1.0;
1021 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate Stages';
1022 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1023
1024 l_msg_data VARCHAR2(2000);
1025 l_stage_duration NUMBER;
1026 l_dummy varchar2(1);
1027
1028 cursor c_stage_name(C_VISIT_ID number, C_STAGE_NAME varchar2, C_STAGE_ID number)
1029 IS
1030 SELECT 'x' FROM AHL_VWP_STAGES_VL
1031 WHERE VISIT_ID = C_VISIT_ID AND
1032 STAGE_ID <> C_STAGE_ID AND
1033 STAGE_NAME = C_STAGE_NAME;
1034
1035 cursor c_stage_data(c_stage_id number)
1036 IS
1037 select stage_name, duration, object_version_number
1038 from AHL_VWP_STAGES_VL
1039 where stage_id = c_stage_id;
1040
1041 l_stage_rec c_stage_data%rowtype;
1042
1043 cursor c_stage_task(c_stage_id number)
1044 IS
1045 select 'x' from ahl_visit_tasks_b
1046 where stage_id = c_stage_id
1047 and nvl(status_code,'X')<>'DELETED';
1048
1049
1050 BEGIN
1051
1052 /* --------------------- initialize -----------------------
1053 -- Check if API is called in debug mode. If yes, enable debug.
1054 IF G_DEBUG='Y' THEN
1055 AHL_DEBUG_PUB.enable_debug;
1056 END IF;
1057
1058 -- Debug info.
1059 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
1060 IF G_DEBUG='Y' THEN
1061 AHL_DEBUG_PUB.debug( l_full_name ||'********************************START******************************* ');
1062 END IF;
1063 END IF;
1064
1065 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1066 Fnd_Msg_Pub.initialize;
1067 END IF;
1068
1069 IF NOT Fnd_Api.compatible_api_call (
1070 l_api_version,
1071 p_api_version,
1072 l_api_name,
1073 G_PKG_NAME
1074 ) THEN
1075 RAISE Fnd_Api.g_exc_unexpected_error;
1076 END IF;
1077 */
1078 x_return_status := Fnd_Api.g_ret_sts_success;
1079
1080 open c_stage_data(p_stages_rec.stage_id);
1081 fetch c_stage_data into l_stage_rec;
1082 if c_stage_data%notfound
1083 then
1084 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NOT_FOUND_NEW'||p_stages_rec.stage_id);
1085 Fnd_Msg_Pub.ADD;
1086 x_return_status := Fnd_Api.g_ret_sts_error;
1087 return;
1088 end if;
1089 close c_stage_data;
1090
1091 -- obj version number validation
1092 IF l_stage_rec.OBJECT_VERSION_NUMBER <> p_stages_rec.OBJECT_VERSION_NUMBER
1093 THEN
1094 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_MOD');
1095 Fnd_Msg_Pub.ADD;
1096 x_return_status := Fnd_Api.g_ret_sts_error;
1097 return;
1098 end if;
1099
1100 IF p_stages_rec.STAGE_NUM IS NULL
1101 THEN
1102 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NUM_NULL');
1103 Fnd_Msg_Pub.ADD;
1104 x_return_status := Fnd_Api.g_ret_sts_error;
1105 return;
1106 end if;
1107
1108
1109 IF p_stages_rec.stage_name is null or p_stages_rec.stage_name <> l_stage_rec.STAGE_NAME
1110 THEN
1111 -- stage name is mandatory
1112 if p_stages_rec.stage_name is null or p_stages_rec.stage_name = ''
1113 then
1114 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NAME_NULL');
1115 FND_MESSAGE.SET_TOKEN('STAGE_NUM',p_stages_rec.STAGE_NUM);
1116 Fnd_Msg_Pub.ADD;
1117 x_return_status := Fnd_Api.g_ret_sts_error;
1118 return;
1119 else
1120 -- stage name is unique
1121 open c_stage_name(p_visit_id, p_stages_rec.stage_name, p_stages_rec.stage_id);
1122 fetch c_stage_name into l_dummy;
1123 if c_stage_name%found
1124 then
1125 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NAME_UNIQUE');
1126 Fnd_Message.SET_TOKEN('STAGE_NAME', p_stages_rec.stage_name);
1127 Fnd_Msg_Pub.ADD;
1128 x_return_status := Fnd_Api.g_ret_sts_error;
1129 close c_stage_name;
1130 return;
1131 end if;
1132 close c_stage_name;
1133 END IF;
1134 END IF;
1135
1136 l_stage_duration := p_stages_rec.DURATION;
1137 IF p_stages_rec.DURATION is null or p_stages_rec.DURATION <> l_stage_rec.DURATION
1138 THEN
1139 -- STAGE DURATION is mandatory
1140 IF p_stages_rec.DURATION IS NULL
1141 then
1142 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_DUR_NULL');
1143 Fnd_Message.SET_TOKEN('STAGE_NAME', p_stages_rec.stage_name);
1144 Fnd_Msg_Pub.ADD;
1145 x_return_status := Fnd_Api.g_ret_sts_error;
1146 -- duration must be positive number
1147 elsif p_stages_rec.duration < 0
1148 then
1149 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_DURN_INV');
1150 Fnd_Message.SET_TOKEN('STAGE_NAME', p_stages_rec.stage_name);
1151 Fnd_Msg_Pub.ADD;
1152 x_return_status := Fnd_Api.g_ret_sts_error;
1153 elsif p_stages_rec.duration = 0
1154 then
1155 open c_stage_task( p_stages_rec.stage_id);
1156 fetch c_stage_task into l_dummy;
1157 if c_stage_task%found
1158 THEN
1159 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_HAS_TASKS');
1160 Fnd_Message.SET_TOKEN('STAGE_NAME', p_stages_rec.stage_name);
1161 Fnd_Msg_Pub.ADD;
1162 x_return_status := Fnd_Api.g_ret_sts_error;
1163 end if;
1164 close c_stage_task;
1165 elsif p_stages_rec.DURATION > trunc(l_stage_duration,0)
1166 THEN
1167 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_DUR_NON_INT');
1168 Fnd_Msg_Pub.ADD;
1169 x_return_status := Fnd_Api.g_ret_sts_error;
1170
1171 end if;
1172
1173 END IF;
1174
1175
1176 END VALIDATE_STAGES;
1177
1178
1179 --------------------------------------------------------------------
1180 -- FUNCTION
1181 -- Get_Stage_Id
1182 --
1183 --------------------------------------------------------------------
1184 FUNCTION Get_Stage_Id
1185 RETURN NUMBER
1186 IS
1187
1188 -- To find the next id value from visit sequence
1189 CURSOR c_seq IS
1190 SELECT Ahl_vwp_stages_B_S.NEXTVAL
1191 FROM dual;
1192
1193 -- To find whether id already exists
1194 CURSOR c_id_exists (x_id IN NUMBER) IS
1195 SELECT 1
1196 FROM Ahl_vwp_stages_b
1197 WHERE stage_id = x_id;
1198
1199 x_stage_Id NUMBER;
1200 l_dummy NUMBER;
1201 BEGIN
1202 -- Modified by amagrawa according to review comments.
1203 -- If the ID is not passed into the API, then
1204 -- grab a value from the sequence.
1205 LOOP
1206 OPEN c_seq;
1207 FETCH c_seq INTO x_stage_Id;
1208 CLOSE c_seq;
1209 --
1210 -- Check to be sure that the sequence does not exist.
1211 OPEN c_id_exists (x_stage_Id);
1212 FETCH c_id_exists INTO l_dummy;
1213 -- If Sequence does not exist exit from loop
1214 IF c_id_exists%NOTFOUND
1215 THEN
1216 close c_id_exists;
1217 EXIT;
1218 END IF;
1219 CLOSE c_id_exists;
1220 END LOOP;
1221
1222 RETURN x_stage_Id ;
1223
1224 END Get_Stage_Id;
1225
1226
1227 PROCEDURE VALIDATE_STAGE_UPDATES(
1228 p_api_version IN NUMBER,
1229 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1230 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1231 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1232 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
1233 p_module_type IN VARCHAR2 := NULL,
1234
1235 p_visit_id IN NUMBER,
1236 p_visit_task_id IN NUMBER,
1237 p_stage_name IN VARCHAR2 := NULL, -- defaulted as u may pass id or num
1238
1239 x_stage_id OUT NOCOPY NUMBER ,
1240 x_return_status OUT NOCOPY VARCHAR2,
1241 x_msg_count OUT NOCOPY NUMBER,
1242 x_msg_data OUT NOCOPY VARCHAR2
1243 )
1244 is
1245
1246 L_MAX_PARENT NUMBER;
1247 L_MIN_CHILD NUMBER;
1248 L_STAGE_NUM NUMBER;
1249 L_STAGE_ID NUMBER;
1250
1251 l_max_stage_num number := FND_PROFILE.value('AHL_NUMBER_OF_STAGES');
1252
1253 L_API_VERSION CONSTANT NUMBER := 1.0;
1254 L_API_NAME CONSTANT VARCHAR2(30) := 'Update Stages';
1255 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1256
1257
1258 CURSOR C_valid_stage_num(P_VISIT_TASK_ID NUMBER)
1259 is
1260 SELECT
1261 max_parent_stage_num,
1262 min_child_stage_num
1263 FROM
1264 ( SELECT
1265 nvl(max(stage_num),1) max_parent_stage_num
1266 FROM
1267 ahl_vwp_stages_b s,
1268 AHL_VISIT_TASKS_b t
1269 WHERE
1270 s.stage_id = t.stage_id and
1271 t.VISIT_task_id IN
1272 ( SELECT PARENT_TASK_ID FROM AHL_TASK_LINKS WHERE VISIT_TASK_ID = P_VISIT_TASK_ID )) ,
1273 ( SELECT
1274 nvl(min(stage_num),l_max_stage_num) min_child_stage_num
1275 FROM
1276 ahl_vwp_stages_b s,
1277 AHL_VISIT_TASKS_b t
1278 WHERE
1279 s.stage_id = t.stage_id and
1280 t.VISIT_task_id IN
1281 ( SELECT visit_TASK_ID FROM AHL_TASK_LINKS WHERE parent_TASK_ID = P_VISIT_TASK_ID ));
1282
1283
1284 CURSOR C_STAGE_NUM(P_VISIT_ID NUMBER, P_STAGE_NAME VARCHAR2)
1285 is
1286 SELECT STAGE_NUM, stage_id FROM AHL_VWP_STAGES_VL
1287 WHERE STAGE_NAME = P_STAGE_NAME AND VISIT_ID = P_VISIT_ID;
1288
1289 begin
1290
1291 --------------------- initialize -----------------------
1292 -- Check if API is called in debug mode. If yes, enable debug.
1293 IF G_DEBUG='Y' THEN
1294 AHL_DEBUG_PUB.enable_debug;
1295 END IF;
1296
1297 -- Debug info.
1298 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
1299 IF G_DEBUG='Y' THEN
1300 AHL_DEBUG_PUB.debug( l_full_name ||'********************************START******************************* ');
1301 END IF;
1302 END IF;
1303
1304 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1305 Fnd_Msg_Pub.initialize;
1306 END IF;
1307
1308 IF NOT Fnd_Api.compatible_api_call (
1309 l_api_version,
1310 p_api_version,
1311 l_api_name,
1312 G_PKG_NAME
1313 ) THEN
1314 RAISE Fnd_Api.g_exc_unexpected_error;
1315 END IF;
1316
1317 x_return_status := Fnd_Api.g_ret_sts_success;
1318
1319
1320 -------------------- basic check for stage num or id------------------------------------------
1321 -- test if passed stage num > max num for all its parents and less that min num for all children--
1322
1323 open C_valid_stage_num(p_visit_task_id);
1324 fetch C_valid_stage_num into l_max_parent, l_min_child;
1325 CLOSE C_VALID_STAGE_NUM;
1326
1327
1328 IF (P_STAGE_NAME IS NOT NULL AND P_STAGE_NAME <> FND_API.G_MISS_CHAR)
1329 THEN
1330
1331 OPEN C_STAGE_NUM(p_visit_id, p_stage_name);
1332 FETCH C_STAGE_NUM INTO L_STAGE_NUM, L_STAGE_ID;
1333 IF C_STAGE_NUM%NOTFOUND THEN
1334 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NOT_FOUND');
1335 Fnd_Msg_Pub.ADD;
1336 x_return_status := Fnd_Api.g_ret_sts_error;
1337 ELSE
1338 -- Stage number should be between Parent Stage Number - L_MAX_PARENT, and Child Stage Number - L_MIN_CHILD
1339 IF ( L_MAX_PARENT IS NOT NULL AND L_STAGE_NUM < L_MAX_PARENT)
1340 OR ( L_MIN_CHILD IS NOT NULL AND L_STAGE_NUM > L_MIN_CHILD)
1341 THEN
1342 Fnd_Message.SET_NAME('AHL','AHL_VWP_ST_NUM_INV');
1343 Fnd_Message.SET_TOKEN('STAGE_NUM', l_stage_NUM);
1344 Fnd_Msg_Pub.ADD;
1345 x_return_status := Fnd_Api.g_ret_sts_error;
1346 END IF;
1347 -- SET OUT PARAM
1348 X_STAGE_ID := L_STAGE_ID;
1349
1350 END IF;
1351 CLOSE C_STAGE_NUM;
1352 END IF;
1353 -------------------- basic check for stage num or id------------------------------------------
1354
1355 end VALIDATE_STAGE_UPDATES;
1356
1357
1358 procedure default_missing_attributes(
1359 p_x_stages_rec IN OUT NOCOPY Visit_Stages_Rec_Type
1360 )
1361 is
1362 cursor C_get_stage_data(c_stage_id number)
1363 is
1364 select * from ahl_vwp_stages_vl where stage_id = c_stage_id;
1365 l_stage_REC C_get_stage_data%rowtype;
1366
1367 begin
1368 OPEN C_get_stage_data(p_x_stages_rec.STAGE_ID);
1369 FETCH C_get_stage_data INTO L_STAGE_REC;
1370 CLOSE C_get_stage_data;
1371
1372
1373 IF NVL(p_x_stages_rec.STAGE_NUM, 99) = FND_API.G_MISS_NUM
1374 THEN
1375 p_x_stages_rec.STAGE_NUM := L_stage_rec.STAGE_NUM;
1376 END IF;
1377
1378 IF NVL(p_x_stages_rec.STAGE_NAME, 'A') = FND_API.G_MISS_CHAR
1379 THEN
1380 p_x_stages_rec.STAGE_NAME := L_stage_rec.STAGE_NAME;
1381 END IF;
1382
1383 IF NVL(p_x_stages_rec.DURATION, 99) = FND_API.G_MISS_NUM
1384 THEN
1385 p_x_stages_rec.DURATION := L_stage_rec.DURATION;
1386 END IF;
1387
1388 end default_missing_attributes;
1389
1390 --------------------------------------------------------------------
1391 -- PROCEDURE
1392 -- Check_Stage_Name_Or_Id
1393 --
1394 -- PURPOSE
1395 -- Converts Stage Name to Stage ID
1396 --------------------------------------------------------------------
1397 PROCEDURE Check_Stage_Name_Or_Id
1398 (p_visit_id IN NUMBER,
1399 p_Stage_Name IN VARCHAR2,
1400 x_Stage_id OUT NOCOPY NUMBER,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_error_msg_code OUT NOCOPY VARCHAR2
1403 )
1404 IS
1405 -- Define local variables
1406 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Stage_Name_Or_Id';
1407 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1408 BEGIN
1409 IF (p_Stage_Name IS NOT NULL) THEN
1410 SELECT Stage_Id INTO x_Stage_id
1411 FROM AHL_VWP_STAGES_VL
1412 WHERE Visit_Id = p_visit_id AND Stage_Name = p_Stage_Name;
1413 ELSE
1414 x_Stage_id := null;
1415 END IF;
1416
1417 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1418 -- Debug info.
1419 IF G_DEBUG='Y' THEN
1420 Ahl_Debug_Pub.debug( 'API Return Status = ' ||L_FULL_NAME||':'|| x_return_status);
1421 END IF;
1422
1423 EXCEPTION
1424 WHEN NO_DATA_FOUND THEN
1425 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
1426 x_error_msg_code:= 'AHL_VWP_STAGE_NOT_EXISTS';
1427 WHEN TOO_MANY_ROWS THEN
1428 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
1429 x_error_msg_code:= 'AHL_VWP_STAGE_NOT_EXISTS';
1430 WHEN OTHERS THEN
1431 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
1432 RAISE;
1433 END Check_Stage_Name_Or_Id;
1434
1435 --------------------------------------------------------------------
1436 -- PROCEDURE
1437 -- Validate_bef_Times_Derive
1438 --
1439 -- PURPOSE
1440 -- To validate visit and tasks before deriving their start and end datetimes
1441 --------------------------------------------------------------------
1442 PROCEDURE Validate_bef_Times_Derive
1443 ( p_visit_id IN NUMBER,
1444 x_valid_flag OUT NOCOPY VARCHAR2,
1445 x_return_status OUT NOCOPY VARCHAR2,
1446 x_error_msg_code OUT NOCOPY VARCHAR2)
1447 IS
1448 -- Define local variables
1449 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_bef_Times_Derive';
1450 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1451
1452 l_dept NUMBER;
1453 l_count NUMBER:=0;
1454 l_dummy NUMBER;
1455 -- i NUMBER;
1456 -- x NUMBER;
1457
1458 -- Define local cursors
1459 -- To find out all visit/template details
1460 CURSOR c_visit(x_id IN NUMBER) IS
1461 SELECT * FROM AHL_VISITS_VL
1462 WHERE VISIT_ID = x_id;
1463 c_visit_rec c_visit%ROWTYPE;
1464
1465 -- To find whether dept shifts exist for the dept
1466 CURSOR c_dept (x_id IN NUMBER) IS
1467 SELECT COUNT(*) FROM AHL_DEPARTMENT_SHIFTS
1468 WHERE DEPARTMENT_ID = x_id;
1469
1470 -- Commented by amagrawa based on review comments.
1471 -- To find all departments from a visit's tasks table
1472 /* CURSOR c_task (x_id IN NUMBER) IS
1473 SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B WHERE VISIT_ID = x_id
1474 AND NVL(STATUS_CODE,'X') <> 'DELETED' AND DEPARTMENT_ID IS NOT NULL;
1475 c_task_rec c_task%ROWTYPE;
1476 */
1477 -- To find only those routes which are there in tasks table but not in route table for a visit
1478 -- Changed by amagrawa to improve performance.
1479 CURSOR c_route_chk(x_id IN NUMBER) IS
1480 SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_VISIT_TASKS_B TSK
1481 WHERE VISIT_ID = x_id AND MR_Route_ID IS NOT NULL
1482 AND NOT EXISTS
1483 (SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_MR_ROUTES_V MR
1484 where MR.mr_route_id =TSK.mr_route_id) and rownum=1;
1485
1486 c_route_chk_rec c_route_chk%ROWTYPE;
1487
1488 -- Added by amagrawa based on review comments
1489 -- To find if the all visit tasks dept has department shifts defined
1490 CURSOR c_task_dep_exist (x_visit_id IN NUMBER) IS
1491 SELECT 1 from dual WHERE exists(
1492 SELECT visit_task_id from ahl_visit_tasks_b
1493 Where department_id is not null
1494 and visit_id = x_visit_id
1495 and nvl(status_code,'X')<>'DELETED'
1496 and department_id not in (select department_id from ahl_department_shifts)
1497 );
1498
1499
1500
1501 BEGIN
1502 -- Initialize API return status to success
1503 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1504 x_valid_flag := 'Y';
1505 OPEN c_Visit(p_visit_id);
1506 FETCH c_visit INTO c_visit_rec;
1507 CLOSE c_Visit;
1508
1509 IF(c_visit_rec.START_DATE_TIME IS NULL OR c_visit_rec.START_DATE_TIME = Fnd_Api.g_miss_date)
1510 THEN
1511 Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_NO_ST_DATE');
1512 Fnd_Msg_Pub.ADD;
1513 x_valid_flag := 'N';
1514 END IF;
1515
1516 IF(c_visit_rec.DEPARTMENT_ID IS NULL OR c_visit_rec.DEPARTMENT_ID = Fnd_Api.g_miss_num)
1517 THEN
1518 Fnd_Message.SET_NAME('AHL','AHL_VWP_VST_NO_DEP');
1519 Fnd_Msg_Pub.ADD;
1520 x_valid_flag := 'N';
1521 ELSE
1522 -- Modified by amagrawa based on review comments
1523 -- To find if the visit dept has department shifts defined
1524 OPEN c_dept (c_visit_rec.department_id);
1525 FETCH c_dept INTO l_count;
1526 CLOSE c_dept;
1527 IF l_count=0
1528 THEN
1529 Fnd_Message.SET_NAME('AHL','AHL_VWP_VNO_DEP_SFT');
1530 Fnd_Msg_Pub.ADD;
1531 x_valid_flag := 'N';
1532 END IF;
1533 END IF;
1534
1535 -- Added by amagrawa based on review comments
1536 open c_task_dep_exist(p_visit_id);
1537 FETCH c_task_dep_exist into l_dummy;
1538 IF(c_task_dep_exist%FOUND)
1539 THEN
1540 Fnd_Message.SET_NAME('AHL','AHL_VWP_TNO_DEP_SFT');
1541 Fnd_Msg_Pub.ADD;
1542 x_valid_flag := 'N';
1543 END IF;
1544 CLOSE c_task_dep_exist;
1545
1546 -- To check routes present in visits exists in MRRoutes table
1547 -- Modified by amagrawa based on review comments
1548 OPEN c_route_chk (p_visit_id);
1549 FETCH c_route_chk INTO c_route_chk_rec;
1550 IF c_route_chk%FOUND THEN
1551 Fnd_Message.SET_NAME('AHL','AHL_VWP_TSK_MR_NOT_VAL');
1552 Fnd_Msg_Pub.ADD;
1553 x_valid_flag := 'N';
1554 END IF;
1555 CLOSE c_route_chk;
1556
1557 END Validate_bef_Times_Derive;
1558
1559
1560
1561 END AHL_VWP_VISITS_STAGES_PVT;
[Home] [Help]