[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_VISITS_PVT
Source
1 PACKAGE BODY AHL_VWP_VISITS_PVT AS
2 /* $Header: AHLVVSTB.pls 120.20 2008/02/14 19:28:24 jaramana ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_VISITS_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 ------------------------------------
8 -- Common constants and variables --
9 ------------------------------------
10 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
11 l_log_statement NUMBER := fnd_log.level_statement;
12 l_log_procedure NUMBER := fnd_log.level_procedure;
13 l_log_error NUMBER := fnd_log.level_error;
14 l_log_unexpected NUMBER := fnd_log.level_unexpected;
15 -----------------------------------------------------------------
16
17 ---------------------------------------------------------------------
18 -- Define Record Types for record structures needed by the APIs --
19 ---------------------------------------------------------------------
20 -- NO RECORD TYPES *************
21
22 --------------------------------------------------------------------
23 -- Define Table Type for Records Structures --
24 --------------------------------------------------------------------
25 -- NO TABLE TYPES **************
26
27 --------------------------------------------------------------------
28 -- START: Defining local functions and procedures SIGNATURES --
29 --------------------------------------------------------------------
30 -- To find out the Visit_Id for the AHL_Visits_B and TL tables
31 FUNCTION Get_Visit_Id
32 RETURN NUMBER;
33
34 -- To find out the Visit_Number for the AHL_Visits_B table
35 FUNCTION Get_Visit_Number
36 RETURN NUMBER;
37
38 -- To find out the Visit_Task_Number for the AHL_Visit_Tasks_B table
39 FUNCTION Get_Visit_Task_Number (p_visit_id IN NUMBER)
40 RETURN NUMBER;
41
42 -- To find out Due_by_Date for the visit update screen.
43 PROCEDURE Get_Due_by_Date(
44 p_visit_id IN NUMBER,
45 x_Due_by_Date OUT NOCOPY DATE
46 );
47
48 -- To assign Null to missing attributes of visit while creation/updation.
49 PROCEDURE Default_Missing_Attribs(
50 p_x_visit_rec IN OUT NOCOPY Visit_Rec_Type
51 );
52
53 -- To validate visit for creation/updation of visit
54 PROCEDURE Validate_Visit (
55 p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
57 p_commit IN VARCHAR2 := Fnd_Api.g_false,
58 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
59 p_Visit_rec IN visit_rec_type,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2
63 );
64
65 -- To Check all Visit's Items
66 PROCEDURE Check_Visit_Items (
67 p_Visit_rec IN visit_rec_type,
68 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
69 x_return_status OUT NOCOPY VARCHAR2
70 );
71
72 -- To Check all Visit's Required Items
73 PROCEDURE Check_Visit_Req_Items (
74 p_Visit_rec IN Visit_Rec_Type,
75 x_return_status OUT NOCOPY VARCHAR2
76 );
77
78 -- To Check all Visit's Unique items
79 PROCEDURE Check_Visit_UK_Items (
80 p_Visit_rec IN Visit_Rec_Type,
81 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
82 x_return_status OUT NOCOPY VARCHAR2
83 );
84
85 -- To Create a Maintenance Visit
86 PROCEDURE Create_Visit (
87 p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
89 p_commit IN VARCHAR2 := Fnd_Api.g_false,
90 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
91 p_module_type IN VARCHAR2 := 'JSP',
92 p_x_visit_rec IN OUT NOCOPY visit_rec_type,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2
96 );
97
98 -- To Update a Maintenance Visit
99 PROCEDURE Update_Visit (
100 p_api_version IN NUMBER,
101 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
102 p_commit IN VARCHAR2 := Fnd_Api.g_false,
103 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
104 p_module_type IN VARCHAR2 := 'JSP',
105 p_x_Visit_Rec IN OUT NOCOPY visit_rec_type,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2
109 );
110
111 -- To Delete a Maintenance Visit
112 PROCEDURE Delete_Visit (
113 p_api_version IN NUMBER,
114 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
115 p_commit IN VARCHAR2 := Fnd_Api.g_false,
116 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
117 p_visit_id IN NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2
121 );
122
123
124 -- Transit Check Visit Change
125 -- yazhou start
126
127 PROCEDURE Synchronize_Visit (
128 p_api_version IN NUMBER,
129 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
130 p_commit IN VARCHAR2 := Fnd_Api.g_false,
131 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
132 p_module_type IN VARCHAR2 := NULL,
133 p_x_Visit_Rec IN OUT NOCOPY visit_rec_type,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2
137 );
138
139 -- yazhou end
140
141 -- AnRaj added for ACL changes in VWP
142 -- Bug number 4297066
143 -------------------------------------------------------------------
144 -- Procedure name : check_unit_quarantined
145 -- Type : Private
146 -- Function : To check whether the Unit is quarantined
147 -- Parameters : p_visit_id
148 -- Parameters : item_instance_id
149 ----------------------------------------------------------------------
150 PROCEDURE check_unit_quarantined(
151 p_visit_id IN NUMBER,
152 item_instance_id IN NUMBER
153 );
154
155 --------------------------------------------------------------------
156 -- END: Defining local functions and procedures SIGNATURES --
157 --------------------------------------------------------------------
158
159 -- ****************************************************************
160
161 --------------------------------------------------------------------
162 -- START: Defining local functions and procedures BODY --
163 --------------------------------------------------------------------
164
165 -------------------------------------------------------------------
166 -- PROCEDURE
167 -- Get_Due_by_Date
168 --
169 -- PURPOSE
170 -- To find out least due by date among all tasks of a visit
171 --------------------------------------------------------------------
172
173 PROCEDURE Get_Due_by_Date(
174 p_visit_id IN NUMBER,
175 x_due_by_date OUT NOCOPY DATE)
176 IS
177 -- Define local variables
178 l_count1 NUMBER;
179 l_count2 NUMBER;
180 l_date DATE;
181
182 -- Define local Cursors
183 -- To find whether a visit exists
184 CURSOR c_visit (x_id IN NUMBER) IS
185 SELECT COUNT(*)
186 FROM Ahl_Visit_Tasks_B
187 WHERE VISIT_ID = x_id
188 AND NVL(STATUS_CODE,'X') <> 'DELETED';
189
190 -- To find the total number of tasks for a visit
191 CURSOR c_visit_task (x_id IN NUMBER) IS
192 SELECT COUNT(*)
193 FROM Ahl_Visit_Tasks_B
194 WHERE VISIT_ID = x_id
195 AND UNIT_EFFECTIVITY_ID IS NOT NULL
196 AND NVL(STATUS_CODE,'X') <> 'DELETED';
197
198 -- To find due date for a visit related with tasks
199 CURSOR c_due_date (x_id IN NUMBER) IS
200 SELECT MIN(T1.due_date)
201 FROM ahl_unit_effectivities_vl T1, ahl_visit_tasks_b T2
202 WHERE T1.unit_effectivity_id = T2.unit_effectivity_id
203 AND T1.due_date IS NOT NULL AND T2.visit_id = x_id;
204
205 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Due_by_Date';
206 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
207 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
208
209 BEGIN
210 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
211 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
212 END IF;
213
214 OPEN c_visit(p_visit_id);
215 FETCH c_visit INTO l_count1;
216 IF c_visit%FOUND THEN --Tasks found for visit
217 CLOSE c_visit;
218 OPEN c_visit_task(p_visit_id);
219 FETCH c_visit_task INTO l_count2;
220 IF c_visit_task%FOUND THEN --Tasks found for visit checking for unit_effectivity_id
221 CLOSE c_visit_task;
222 OPEN c_due_date(p_visit_id);
223 FETCH c_due_date INTO x_due_by_date;
224 IF c_due_date%FOUND THEN --Tasks found for visit
225 CLOSE c_due_date;
226 END IF;
227 ELSE
228 CLOSE c_visit_task;
229 END IF;
230 ELSE
231 CLOSE c_visit;
232 END IF;
233
234 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
235 fnd_log.string(fnd_log.level_procedure,L_DEBUG,'x_due_by_date - '||x_due_by_date);
236 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
237 END IF;
238
239 RETURN;
240 END Get_Due_by_Date;
241
242 --------------------------------------------------------------------
243 -- PROCEDURE
244 -- Default_Missing_Attribs
245 --
246 -- PURPOSE
247 -- For all optional fields check if its g_miss_num/g_miss_char/
248 -- g_miss_date then Null else the value
249
250 --------------------------------------------------------------------
251 PROCEDURE Default_Missing_Attribs
252 ( p_x_visit_rec IN OUT NOCOPY Visit_Rec_Type)
253 AS
254 BEGIN
255 -- Post 11.5.10 Enhancements
256 -- Removing defaulting of Visit Name since it is a mandatory field
257 /*-- VISIT NAME
258 IF p_x_visit_rec.visit_name = Fnd_Api.G_MISS_char THEN
259 p_x_visit_rec.visit_name := NULL;
260 ELSE
261 p_x_visit_rec.visit_name := p_x_visit_rec.visit_name;
262 END IF;
263 */
264
265 -- ORGANIZATION ID
266 IF p_x_visit_rec.organization_id = Fnd_Api.G_MISS_NUM THEN
267 p_x_visit_rec.organization_id := NULL;
268 ELSE
269 p_x_visit_rec.organization_id := p_x_visit_rec.organization_id;
270 END IF;
271
272 -- DEPARTMENT ID
273 IF p_x_visit_rec.department_id = Fnd_Api.G_MISS_NUM THEN
274 p_x_visit_rec.department_id := NULL;
275 ELSE
276 p_x_visit_rec.department_id := p_x_visit_rec.department_id;
277 END IF;
278
279 -- START DATE
280 IF p_x_visit_rec.start_date = Fnd_Api.G_MISS_DATE THEN
281 p_x_visit_rec.start_date := NULL;
282 ELSE
283 p_x_visit_rec.start_date := p_x_visit_rec.start_date;
284 END IF;
285
286 -- PLAN END DATE
287 IF p_x_visit_rec.plan_end_date = Fnd_Api.G_MISS_DATE THEN
288 p_x_visit_rec.plan_end_date := NULL;
289 ELSE
290 p_x_visit_rec.plan_end_date := p_x_visit_rec.plan_end_date;
291 END IF;
292
293 -- SIMULATION_PLAN_ID
294 IF p_x_visit_rec.SIMULATION_PLAN_ID = Fnd_Api.G_MISS_NUM THEN
295 p_x_visit_rec.SIMULATION_PLAN_ID := NULL;
296 ELSE
297 p_x_visit_rec.SIMULATION_PLAN_ID := p_x_visit_rec.SIMULATION_PLAN_ID;
298 END IF;
299
300 -- ITEM_INSTANCE_ID
301 IF p_x_visit_rec.ITEM_INSTANCE_ID = Fnd_Api.G_MISS_NUM THEN
302 p_x_visit_rec.ITEM_INSTANCE_ID := NULL;
303 ELSE
304 p_x_visit_rec.ITEM_INSTANCE_ID := p_x_visit_rec.ITEM_INSTANCE_ID;
305 END IF;
306
307 -- ASSO_PRIMARY_VISIT_ID
308 IF p_x_visit_rec.ASSO_PRIMARY_VISIT_ID = Fnd_Api.G_MISS_NUM THEN
309 p_x_visit_rec.ASSO_PRIMARY_VISIT_ID := NULL;
310 ELSE
311 p_x_visit_rec.ASSO_PRIMARY_VISIT_ID := p_x_visit_rec.ASSO_PRIMARY_VISIT_ID;
312 END IF;
313
314 -- SIMULATION_DELETE_FLAG
315 IF p_x_visit_rec.SIMULATION_DELETE_FLAG = Fnd_Api.G_MISS_CHAR THEN
316 p_x_visit_rec.SIMULATION_DELETE_FLAG := NULL;
317 ELSE
318 p_x_visit_rec.SIMULATION_DELETE_FLAG := p_x_visit_rec.SIMULATION_DELETE_FLAG;
319 END IF;
320
321 -- OUT_OF_SYNC_FLAG
322 IF p_x_visit_rec.OUT_OF_SYNC_FLAG = Fnd_Api.G_MISS_CHAR THEN
323 p_x_visit_rec.OUT_OF_SYNC_FLAG := NULL;
324 ELSE
325 p_x_visit_rec.OUT_OF_SYNC_FLAG := p_x_visit_rec.OUT_OF_SYNC_FLAG;
326 END IF;
327
328 -- PROJECT_ID
329 IF p_x_visit_rec.PROJECT_ID = Fnd_Api.G_MISS_NUM THEN
330 p_x_visit_rec.PROJECT_ID := NULL;
331 ELSE
332 p_x_visit_rec.PROJECT_ID := p_x_visit_rec.PROJECT_ID;
333 END IF;
334
335 -- space_category_code
336 IF p_x_visit_rec.space_category_code = Fnd_Api.G_MISS_CHAR THEN
337 p_x_visit_rec.space_category_code := NULL;
338 ELSE
339 p_x_visit_rec.space_category_code := p_x_visit_rec.space_category_code;
340 END IF;
341
342 -- description
343 IF p_x_visit_rec.description = Fnd_Api.G_MISS_CHAR THEN
344 p_x_visit_rec.description := NULL;
345 ELSE
346 p_x_visit_rec.description := p_x_visit_rec.description;
347 END IF;
348
349 -- Post 11.5.10 Enhancements
350 -- Adding priority and project template
351 IF p_x_visit_rec.priority_code = Fnd_Api.G_MISS_CHAR THEN
352 p_x_visit_rec.priority_code := NULL;
353 ELSE
354 p_x_visit_rec.priority_code := p_x_visit_rec.priority_code;
355 END IF;
356
357 IF p_x_visit_rec.proj_template_id = Fnd_Api.G_MISS_NUM THEN
358 p_x_visit_rec.proj_template_id := NULL;
359 ELSE
360 p_x_visit_rec.proj_template_id := p_x_visit_rec.proj_template_id;
361 END IF;
362
363 -- Post 11.5.10 Enhancements
364 -- Adding item id and visit type code.
365 -- since these fields are not mandatory in Post 11.5.10
366 -- serial number check already exists (item_instance_id)
367 -- ITEM ID
368 IF p_x_visit_rec.inventory_item_id = Fnd_Api.G_MISS_NUM THEN
369 p_x_visit_rec.inventory_item_id := NULL;
370 ELSE
371 p_x_visit_rec.inventory_item_id := p_x_visit_rec.inventory_item_id;
372 END IF;
373
374 -- VISIT TYPE CODE
375 IF p_x_visit_rec.visit_type_code = Fnd_Api.G_MISS_CHAR THEN
376 p_x_visit_rec.visit_type_code := NULL;
377 ELSE
378 p_x_visit_rec.visit_type_code := p_x_visit_rec.visit_type_code;
379 END IF;
380
381 IF p_x_visit_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
382 p_x_visit_rec.attribute_category := NULL;
383 ELSE
384 p_x_visit_rec.attribute_category := p_x_visit_rec.attribute_category;
385 END IF;
386
387 IF p_x_visit_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
388 p_x_visit_rec.attribute1 := NULL;
389 ELSE
390 p_x_visit_rec.attribute1 := p_x_visit_rec.attribute1;
391 END IF;
392
393 IF p_x_visit_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
394 p_x_visit_rec.attribute2 := NULL;
395 ELSE
396 p_x_visit_rec.attribute2 := p_x_visit_rec.attribute2;
397 END IF;
398
399 IF p_x_visit_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
400 p_x_visit_rec.attribute3 := NULL;
401 ELSE
402 p_x_visit_rec.attribute3 := p_x_visit_rec.attribute3;
403 END IF;
404
405 IF p_x_visit_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
406 p_x_visit_rec.attribute4 := NULL;
407 ELSE
408 p_x_visit_rec.attribute4 := p_x_visit_rec.attribute4;
409 END IF;
410
411 IF p_x_visit_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
412 p_x_visit_rec.attribute5 := NULL;
413 ELSE
414 p_x_visit_rec.attribute5 := p_x_visit_rec.attribute5;
415 END IF;
416
417 IF p_x_visit_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
418 p_x_visit_rec.attribute6 := NULL;
419 ELSE
420 p_x_visit_rec.attribute6 := p_x_visit_rec.attribute6;
421 END IF;
422
423 IF p_x_visit_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
424 p_x_visit_rec.attribute7 := NULL;
425 ELSE
426 p_x_visit_rec.attribute7 := p_x_visit_rec.attribute7;
427 END IF;
428
429 IF p_x_visit_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
430 p_x_visit_rec.attribute8 := NULL;
431 ELSE
432 p_x_visit_rec.attribute8 := p_x_visit_rec.attribute8;
433 END IF;
434
435 IF p_x_visit_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
436 p_x_visit_rec.attribute9 := NULL;
437 ELSE
438 p_x_visit_rec.attribute9 := p_x_visit_rec.attribute9;
439 END IF;
440
441 IF p_x_visit_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
442 p_x_visit_rec.attribute10 := NULL;
443 ELSE
444 p_x_visit_rec.attribute10 := p_x_visit_rec.attribute10;
445 END IF;
446
447 IF p_x_visit_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
448 p_x_visit_rec.attribute11 := NULL;
449 ELSE
450 p_x_visit_rec.attribute11 := p_x_visit_rec.attribute11;
451 END IF;
452
453 IF p_x_visit_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
454 p_x_visit_rec.attribute12 := NULL;
455 ELSE
456 p_x_visit_rec.attribute12 := p_x_visit_rec.attribute12;
457 END IF;
458
459 IF p_x_visit_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
460 p_x_visit_rec.attribute13 := NULL;
461 ELSE
462 p_x_visit_rec.attribute13 := p_x_visit_rec.attribute13;
463 END IF;
464
465 IF p_x_visit_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
466 p_x_visit_rec.attribute14 := NULL;
467 ELSE
468 p_x_visit_rec.attribute14 := p_x_visit_rec.attribute14;
469 END IF;
470
471 IF p_x_visit_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
472 p_x_visit_rec.attribute15 := NULL;
473 ELSE
474 p_x_visit_rec.attribute15 := p_x_visit_rec.attribute15;
475 END IF;
476
477 END Default_Missing_Attribs;
478
479 --------------------------------------------------------------------
480 -- FUNCTION
481 -- Get_Visit_Id
482 --
483 --------------------------------------------------------------------
484 FUNCTION Get_Visit_Id RETURN NUMBER IS
485
486 -- To find the next id value from visit sequence
487 CURSOR c_seq IS
488 SELECT Ahl_Visits_B_S.NEXTVAL
489 FROM dual;
490
491 -- To find whether id already exists
492 CURSOR c_id_exists (x_id IN NUMBER) IS
493 SELECT 1 FROM Ahl_Visits_VL
494 WHERE Visit_id = x_id;
495
496 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Id';
497 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
498 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
499
500 x_Visit_Id NUMBER;
501 l_dummy NUMBER;
502 BEGIN
503 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
504 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
505 END IF;
506
507 LOOP
508 -- If the ID is not passed into the API, then
509 -- grab a value from the sequence.
510 OPEN c_seq;
511 FETCH c_seq INTO x_Visit_Id;
512 CLOSE c_seq;
513 -- Check to be sure that the sequence does not exist.
514 OPEN c_id_exists (x_Visit_Id);
515 FETCH c_id_exists INTO l_dummy;
516 CLOSE c_id_exists;
517 -- If the value for the ID already exists, then
518 -- l_dummy would be populated with '1', otherwise, it receives NULL.
519 EXIT WHEN l_dummy = null ;
520 END LOOP;
521
522 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
523 fnd_log.string(fnd_log.level_procedure,L_DEBUG,'New visit id : ' || x_Visit_Id);
524 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
525 END IF;
526
527 RETURN x_Visit_Id;
528
529 END Get_Visit_Id;
530
531 --------------------------------------------------------------------
532 -- FUNCTION
533 -- Get_Visit_Number
534 --
535 --------------------------------------------------------------------
536 FUNCTION Get_Visit_Number RETURN NUMBER IS
537 x_visit_number NUMBER;
538
539 -- To find maximum visit number among all visits
540 CURSOR c_visit_number IS
541 SELECT MAX(visit_number)
542 FROM Ahl_Visits_B;
543
544 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Number';
545 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
546 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
547 BEGIN
548
549 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
550 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
551 END IF;
552
553 -- Check for Visit Number
554 OPEN c_visit_number;
555 FETCH c_visit_number INTO x_visit_number;
556 CLOSE c_visit_number;
557
558 IF x_visit_number IS NOT NULL THEN
559 x_visit_number := x_visit_number + 1;
560 ELSE
561 x_visit_number := 1;
562 END IF;
563
564 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
565 fnd_log.string(fnd_log.level_procedure,L_DEBUG,'New visit number : ' || x_visit_number);
566 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
567 END IF;
568
569 RETURN x_visit_number;
570
571 END Get_Visit_Number;
572 --------------------------------------------------------------------
573 -- FUNCTION
574 -- Get_Visit_Task_Number
575 --
576 --------------------------------------------------------------------
577 FUNCTION Get_Visit_Task_Number(p_visit_id IN NUMBER)
578 RETURN NUMBER IS
579 x_Visit_Task_Number NUMBER ;
580
581 -- To find maximum visit task nubmer among all tasks for a particular visit
582 CURSOR c_task_number IS
583 SELECT MAX(visit_task_number)
584 FROM Ahl_Visit_Tasks_B
585 WHERE Visit_Id = p_visit_id;
586
587 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Number';
588 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
589 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
590
591 BEGIN
592 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
593 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of function');
594 END IF;
595
596 -- Check for Visit Number
597 OPEN c_Task_Number;
598 FETCH c_Task_Number INTO x_Visit_Task_Number;
599 CLOSE c_Task_Number;
600
601 IF x_Visit_Task_Number IS NOT NULL THEN
602 x_Visit_Task_Number := x_Visit_Task_Number + 1;
603 ELSE
604 x_Visit_Task_Number := 1;
605 END IF;
606
607 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
608 fnd_log.string(fnd_log.level_procedure,L_DEBUG,'Visit Task Number - ' || x_Visit_Task_Number);
609 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of function');
610 END IF;
611
612 RETURN x_Visit_Task_Number;
613 END Get_Visit_Task_Number;
614 --------------------------------------------------------------------
615 -- END: Defining local functions and procedures BODY --
616 --------------------------------------------------------------------
617
618 ----------------------------------------------------------------------
619 -- START: Defining procedures BODY, which are called from UI screen --
620 ----------------------------------------------------------------------
621
622 --------------------------------------------------------------------
623 -- PROCEDURE
624 -- Process_Visit
625 --
626 -- PURPOSE
627 -- Process Visit Records from front end screen intermediate step
628 -- between API's and frontend.
629 --------------------------------------------------------------------
630 PROCEDURE Process_Visit (
631 p_api_version IN NUMBER,
632 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
633 p_commit IN VARCHAR2 := FND_API.g_false,
634 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
635 p_module_type IN VARCHAR2 := 'JSP',
636 p_x_Visit_tbl IN OUT NOCOPY Visit_Tbl_Type,
637 x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639 x_msg_data OUT NOCOPY VARCHAR2
640 )
641 IS
642 -- Define local variables
643 l_api_name CONSTANT VARCHAR2(30) := 'Process_Visit';
644 l_api_version CONSTANT NUMBER := 1.0;
645 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
646 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
647
648 l_msg_count NUMBER;
649 l_visit_id NUMBER;
650 p_visit_id NUMBER;
651
652 l_return_status VARCHAR2(1);
653 l_msg_data VARCHAR2(2000);
654 -- Transit Visit Change
655 -- yazhou start
656
657 l_visit_status VARCHAR2(30);
658
659 -- To find out visit status
660 CURSOR c_visit_status (x_visit_id IN NUMBER) IS
661 SELECT STATUS_CODE FROM AHL_VISITS_B
662 WHERE VISIT_ID = x_visit_id;
663
664 -- yazhou end
665 --
666 BEGIN
667 -- Standard Start of API savepoint
668 SAVEPOINT Process_Visit;
669
670 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
671 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
672 END IF;
673
674 -- Initialize API return status to success
675 -- Initialize message list if p_init_msg_list is set to TRUE.
676 IF FND_API.to_boolean(p_init_msg_list) THEN
677 FND_MSG_PUB.initialize;
678 END IF;
679
680 x_return_status := FND_API.G_RET_STS_SUCCESS;
681
682 -- Standard call to check for call compatibility.
683 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
684 p_api_version,
685 l_api_name,G_PKG_NAME) THEN
686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
687 END IF;
688
689 --------------------Start of API Body-----------------------------------
690 IF p_x_Visit_tbl.COUNT > 0 THEN
691 FOR i IN p_x_Visit_tbl.first..p_x_Visit_tbl.LAST
692 LOOP
693 -- For Create
694 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
695 fnd_log.string(fnd_log.level_statement,L_DEBUG,'p_x_Visit_tbl(i).operation_flag : '||p_x_Visit_tbl(i).operation_flag);
696 END IF;
697
698 IF p_x_Visit_tbl(i).operation_flag = 'I' or p_x_Visit_tbl(i).operation_flag = 'i' THEN
699
700 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
701 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Create_Visit');
702 END IF;
703
704 Create_Visit
705 (
706 p_api_version => l_api_version,
707 p_init_msg_list => p_init_msg_list,
708 p_commit => Fnd_Api.g_false,
709 p_validation_level => p_validation_level,
710 p_module_type => p_module_type,
711 p_x_visit_rec => p_x_Visit_tbl(i),
712 x_return_status => l_return_status,
713 x_msg_count => l_msg_count,
714 x_msg_data => l_msg_data
715 );
716
717 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
718 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Create_Visit - l_return_status : '||l_return_status);
719 END IF;
720
721 --For Update
722 ELSIF p_x_Visit_tbl(i).operation_flag = 'U' or p_x_Visit_tbl(i).operation_flag = 'u' THEN
723
724 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
725 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Update_Visit');
726 END IF;
727
728 Update_Visit
729 (
730 p_api_version => l_api_version,
731 p_init_msg_list => p_init_msg_list,
732 p_commit => Fnd_Api.g_false,
733 p_validation_level => p_validation_level,
734 p_module_type => p_module_type,
735 p_x_Visit_rec => p_x_Visit_tbl(i),
736 x_return_status => l_return_status,
737 x_msg_count => l_msg_count,
738 x_msg_data => l_msg_data
739 );
740
741 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
742 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Update_Visit - l_return_status : '||l_return_status);
743 END IF;
744
745 --For Delete
746 ELSIF p_x_Visit_tbl(i).operation_flag = 'D' or p_x_Visit_tbl(i).operation_flag = 'd' THEN
747
748 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
749 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Delete_Visit');
750 END IF;
751
752 Delete_Visit
753 (
754 p_api_version => l_api_version,
755 p_init_msg_list => p_init_msg_list,
756 p_commit => Fnd_Api.g_false,
757 p_validation_level => p_validation_level,
758 p_Visit_id => p_x_visit_tbl(i).visit_id,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data
762 );
763
764 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
765 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Delete_Visit - l_return_status : '||l_return_status);
766 END IF;
767
768 -- Transit Visit Change
769 -- yazhou start
770 -- Will be called from UA
771 -- To Synchronize visit with flight schedule change
772 ELSIF p_x_Visit_tbl(i).operation_flag = 'S' or p_x_Visit_tbl(i).operation_flag = 's' THEN
773
774 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
775 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Synchronize_Visit');
776 END IF;
777
778 Synchronize_Visit
779 (
780 p_api_version => l_api_version,
781 p_init_msg_list => p_init_msg_list,
782 p_commit => Fnd_Api.g_false,
783 p_validation_level => p_validation_level,
784 p_x_Visit_rec => p_x_visit_tbl(i),
785 x_return_status => l_return_status,
786 x_msg_count => l_msg_count,
787 x_msg_data => l_msg_data
788 );
789
790 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
791 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Synchronize_Visit - l_return_status : '||l_return_status);
792 END IF;
793
794 -- Will be called from UA
795 -- Delete the visit if visit is in Planning status
796 -- Cancel the visit if visit is in Released or Partially Released status
797 ELSIF p_x_Visit_tbl(i).operation_flag = 'X' or p_x_Visit_tbl(i).operation_flag = 'x' THEN
798
799 OPEN c_visit_status(p_x_Visit_tbl(i).visit_id);
800 FETCH c_visit_status INTO l_visit_status;
801 IF c_visit_status%NOTFOUND THEN
802 CLOSE c_visit_status;
803 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
804 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
805 Fnd_Msg_Pub.ADD;
806 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
807 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit status not found for visit : ' ||p_x_Visit_tbl(i).visit_id );
808 END IF;
809 END IF;
810 RAISE Fnd_Api.g_exc_error;
811 ELSE
812 CLOSE c_visit_status;
813 END IF;
814
815 IF l_visit_status = 'PLANNING' THEN
816 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
817 fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_visit_status : '||l_visit_status);
818 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Delete_Visit');
819 END IF;
820
821 Delete_Visit
822 (
823 p_api_version => l_api_version,
824 p_init_msg_list => p_init_msg_list,
825 p_commit => Fnd_Api.g_false,
826 p_validation_level => p_validation_level,
827 p_Visit_id => p_x_visit_tbl(i).visit_id,
828 x_return_status => l_return_status,
829 x_msg_count => l_msg_count,
830 x_msg_data => l_msg_data
831 );
832
833 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
834 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Delete_Visit - l_return_status : '|| l_return_status);
835 END IF;
836 ELSIF l_visit_status = 'RELEASED' OR l_visit_status = 'PARTIALLY RELEASED' THEN
837 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
838 fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_visit_status : '||l_visit_status);
839 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Cancel_Visit');
840 END IF;
841
842 Cancel_Visit (
843 p_api_version => l_api_version,
844 p_init_msg_list => p_init_msg_list,
845 p_commit => Fnd_Api.g_false,
846 p_validation_level => p_validation_level,
847 p_module_type => p_module_type,
848 p_visit_id => p_x_visit_tbl(i).visit_id,
849 p_obj_ver_num => p_x_visit_tbl(i).object_version_number,
850 x_return_status => l_return_status,
851 x_msg_count => l_msg_count,
852 x_msg_data => l_msg_data );
853
854 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
855 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Cancel_Visit - l_return_status : '|| l_return_status);
856 END IF;
857 END IF;
858 -- yazhou end
859 END IF;
860 END LOOP;
861 END IF;
862
863 ------------------------End of API Body---------------------------------------
864 --Standard check to count messages
865 l_msg_count := Fnd_Msg_Pub.count_msg;
866
867 IF l_msg_count > 0 THEN
868 x_msg_count := l_msg_count;
869 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
870 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
871 END IF;
872
873 --Standard check for commit
874 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
875 COMMIT;
876 END IF;
877
878 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
879 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
880 END IF;
881
882 EXCEPTION
883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
884 ROLLBACK TO Process_Visit;
885 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
887 p_count => x_msg_count,
888 p_data => x_msg_data);
889
890 WHEN FND_API.G_EXC_ERROR THEN
891 ROLLBACK TO Process_Visit;
892 X_return_status := FND_API.G_RET_STS_ERROR;
893 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
894 p_count => x_msg_count,
895 p_data => X_msg_data);
896 WHEN OTHERS THEN
897 ROLLBACK TO Process_Visit;
898 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
900 THEN
901 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_VWP_VISITS_PVT',
902 p_procedure_name => 'Process_Visit',
903 p_error_text => SUBSTR(SQLERRM,1,240));
904 END IF;
905 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
906 p_count => x_msg_count,
907 p_data => x_msg_data);
908 END Process_Visit;
909
910 --------------------------------------------------------------------
911 -- PROCEDURE
912 -- Get_Visit_Details
913 --
914 -- PURPOSE
915 -- Get a particular Visit Records with all details
916 --------------------------------------------------------------------
917 PROCEDURE Get_Visit_Details (
918 p_api_version IN NUMBER,
919 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
920 p_commit IN VARCHAR2 := Fnd_Api.g_false,
921 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
922 p_module_type IN VARCHAR2 := 'JSP',
923 p_visit_id IN NUMBER,
924
925 x_Visit_rec OUT NOCOPY Visit_Rec_Type,
926 x_return_status OUT NOCOPY VARCHAR2,
927 x_msg_count OUT NOCOPY NUMBER,
928 x_msg_data OUT NOCOPY VARCHAR2
929 )
930 IS
931 -- Define local Variables
932 L_API_VERSION CONSTANT NUMBER := 1.0;
933 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Details';
934 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
935 L_DEBUG CONSTANT VARCHAR2(90) := G_PKG_NAME || '.' || L_API_NAME;
936
937 l_msg_data VARCHAR2(2000);
938 l_project_flag VARCHAR2(80);
939 l_simulation_plan_name VARCHAR2(80);
940 l_unit_name VARCHAR2(80);
941 l_unit_header_id NUMBER;
942 l_hour VARCHAR2(30);
943 l_hour_close VARCHAR2(30);
944 l_default VARCHAR2(30);
945 l_proj_temp_name VARCHAR2(30);
946 l_return_status VARCHAR2(1);
947 l_valid_flag VARCHAR2(1);
948
949 l_visit_id NUMBER:= p_visit_id;
950 l_count NUMBER;
951 l_duration NUMBER;
952 l_visit_end_hour NUMBER;
953 l_proj_temp_id NUMBER;
954 l_workorder_id NUMBER;
955 i NUMBER;
956 x NUMBER;
957
958 l_due_date DATE;
959 x_due_by_date DATE;
960 l_visit_end_date DATE;
961 l_minute NUMBER(2);
962 l_minute_close NUMBER(2);
963
964 l_workorder_name VARCHAR2(80); -- Added in 11.5.10
965
966 ---Arvind changes for FlightNumber
967 l_flight_number VARCHAR2(30);
968 ---End
969
970 -- Define local record datatypes
971 l_visit_rec Visit_Rec_Type;
972
973 -- Define local cursors
974 --Arvind changes for FlightNumber
975 -- To retreive FlightNumber based on UnitScheduleID
976 CURSOR c_flight_number (x_id IN NUMBER) IS
977 select flight_number
978 from ahl_unit_schedules
979 where unit_schedule_id=x_id;
980 --end
981
982 -- To find out required search visit details
983 -- Fix for ADS bug# 4357001.
984 -- Modified query so that Visits retrieved are not OU stripped.
985 -- 'View Visit' should show visit details accross OU.
986 -- Following query is copied from ahl_search_visits_v without the CLIENT_INFO.
987 CURSOR c_visit (x_id IN NUMBER) IS
988 --SELECT * FROM AHL_SEARCH_VISITS_V
989 --WHERE VISIT_ID = x_id;
990 --AnRaj:Changed query, Perf Bug:4919502
991 /* SELECT AVTS.VISIT_ID , AVTS.VISIT_NUMBER, AVTS.VISIT_NAME,
992 AVTS.ORGANIZATION_ID , HROU.NAME ORGANIZATION_NAME, AVTS.DEPARTMENT_ID ,
993 BDPT.DESCRIPTION DEPARTMENT_NAME , AVTS.OBJECT_VERSION_NUMBER,
994 AVTS.START_DATE_TIME,
995 AVTS.STATUS_CODE, FLVT1.MEANING STATUS_MEAN, AVTS.TEMPLATE_FLAG,
996 AVTS.ITEM_INSTANCE_ID , CSIS.SERIAL_NUMBER , AVTS.INVENTORY_ITEM_ID ,
997 AVTS.ITEM_ORGANIZATION_ID , MTSB.CONCATENATED_SEGMENTS ITEM_DESCRIPTION,
998 AVTS.VISIT_TYPE_CODE , FLVT.MEANING VISIT_TYPE_MEAN, AVTS.SIMULATION_PLAN_ID,
999 ASPV.SIMULATION_PLAN_NAME, NVL(ASPV.PRIMARY_PLAN_FLAG,'Y') ,
1000 AVTS.SPACE_CATEGORY_CODE, FLVT2.MEANING SPACE_CATEGORY_MEAN,
1001 AVTS.SERVICE_REQUEST_ID,
1002 AVTS.CLOSE_DATE_TIME, CSAB.INCIDENT_NUMBER, UC.NAME UNIT_NAME,
1003 AVTS.PRIORITY_CODE,
1004 FLVT3.MEANING PRIORITY_MEAN, AVTS.PROJECT_TEMPLATE_ID,
1005 PA.NAME PROJECT_TEMPLATE_NAME ,
1006 AVTS.UNIT_SCHEDULE_ID, AVTS.ASSO_PRIMARY_VISIT_ID
1007 FROM AHL_VISITS_VL AVTS, AHL_SIMULATION_PLANS_VL ASPV,
1008 CSI_ITEM_INSTANCES CSIS, HR_ALL_ORGANIZATION_UNITS HROU,
1009 BOM_DEPARTMENTS BDPT, MTL_SYSTEM_ITEMS_B_KFV MTSB,
1010 FND_LOOKUP_VALUES_VL FLVT, FND_LOOKUP_VALUES_VL FLVT1,
1011 FND_LOOKUP_VALUES_VL FLVT2, FND_LOOKUP_VALUES_VL FLVT3,
1012 PA_PROJECTS_ALL PA, AHL_UNIT_CONFIG_HEADERS UC, CS_INCIDENTS_ALL_B CSAB
1013 WHERE AVTS.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID(+)
1014 AND AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+)
1015 AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
1016 AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
1017 AND AVTS.SIMULATION_PLAN_ID = ASPV.SIMULATION_PLAN_ID(+)
1018 AND AVTS. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
1019 AND AVTS. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
1020 AND FLVT.LOOKUP_TYPE(+) ='AHL_PLANNING_VISIT_TYPE'
1021 AND FLVT.LOOKUP_CODE(+) = AVTS.VISIT_TYPE_CODE
1022 AND FLVT1.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
1023 AND FLVT1.LOOKUP_CODE(+) = AVTS.STATUS_CODE
1024 AND FLVT2.LOOKUP_TYPE(+) = 'AHL_LTP_SPACE_CATEGORY'
1025 AND FLVT2.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
1026 AND FLVT3.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_PRIORITY'
1027 AND FLVT3.LOOKUP_CODE(+) = AVTS.PRIORITY_CODE
1028 AND PA.PROJECT_ID(+) = AVTS.PROJECT_TEMPLATE_ID
1029 AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+)
1030 AND AVTS.TEMPLATE_FLAG = 'N' AND AVTS.STATUS_CODE <> 'DELETED'
1031 AND UC.active_end_date is null
1032 AND VISIT_ID = x_id;*/
1033 -- AnRaj: Replaced HR_ALL_ORGANIZATION_UNITS with HR_ALL_ORGANIZATION_UNITS_TL, Fix for Bug# 5367598
1034 SELECT AVTS.VISIT_ID , AVTS.VISIT_NUMBER,
1035 AVTSTL.VISIT_NAME,AVTS.ORGANIZATION_ID ,
1036 AVTS.START_DATE_TIME, AVTS.CLOSE_DATE_TIME,AVTS.VISIT_TYPE_CODE ,
1037 AVTS.DEPARTMENT_ID ,AVTS.STATUS_CODE,AVTS.OBJECT_VERSION_NUMBER,
1038 HROU.NAME ORGANIZATION_NAME,
1039 BDPT.DESCRIPTION DEPARTMENT_NAME ,
1040 FLVT1.MEANING STATUS_MEAN,
1041 AVTS.TEMPLATE_FLAG,AVTS.ITEM_INSTANCE_ID ,
1042 AVTS.INVENTORY_ITEM_ID,AVTS.ITEM_ORGANIZATION_ID,
1043 AVTS.SIMULATION_PLAN_ID,AVTS.SERVICE_REQUEST_ID,
1044 AVTS.PRIORITY_CODE,AVTS.SPACE_CATEGORY_CODE,
1045 AVTS.PROJECT_TEMPLATE_ID,AVTS.UNIT_SCHEDULE_ID,AVTS.ASSO_PRIMARY_VISIT_ID,
1046 CSIS.SERIAL_NUMBER ,
1047 MTSB.CONCATENATED_SEGMENTS ITEM_DESCRIPTION,
1048 FLVT.MEANING VISIT_TYPE_MEAN,FLVT3.MEANING
1049 PRIORITY_MEAN,FLVT2.MEANING SPACE_CATEGORY_MEAN,
1050 ASPVTL.SIMULATION_PLAN_NAME, NVL(ASPV.PRIMARY_PLAN_FLAG,'Y') ,
1051 CSAB.INCIDENT_NUMBER, UC.NAME UNIT_NAME
1052 ,PA.NAME PROJECT_TEMPLATE_NAME,
1053 AVTS.INV_LOCATOR_ID -- Added by sowsubra
1054 FROM AHL_VISITS_B AVTS,AHL_VISITS_TL AVTSTL,
1055 AHL_SIMULATION_PLANS_B ASPV,AHL_SIMULATION_PLANS_TL ASPVTL,
1056 CSI_ITEM_INSTANCES CSIS, HR_ALL_ORGANIZATION_UNITS_TL HROU,
1057 BOM_DEPARTMENTS BDPT, MTL_SYSTEM_ITEMS_B_KFV MTSB,
1058 FND_LOOKUP_VALUES FLVT, FND_LOOKUP_VALUES FLVT1,
1059 FND_LOOKUP_VALUES FLVT2, FND_LOOKUP_VALUES FLVT3,
1060 PA_PROJECTS_ALL PA,
1061 AHL_UNIT_CONFIG_HEADERS UC, CS_INCIDENTS_ALL_B CSAB
1062 WHERE AVTS.VISIT_ID = AVTSTL.VISIT_ID
1063 AND AVTSTL.LANGUAGE = USERENV('LANG')
1064 AND ASPV.SIMULATION_PLAN_ID = ASPVTL.SIMULATION_PLAN_ID(+)
1065 AND ASPVTL.LANGUAGE(+) = USERENV('LANG')
1066 AND AVTS.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID(+)
1067 AND AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+)
1068 AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
1069 AND HROU.LANGUAGE(+) = USERENV('LANG')
1070 AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
1071 AND AVTS.SIMULATION_PLAN_ID = ASPV.SIMULATION_PLAN_ID(+)
1072 AND AVTS. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
1073 AND AVTS. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
1074 AND FLVT.LOOKUP_TYPE(+) ='AHL_PLANNING_VISIT_TYPE'
1075 AND FLVT.LOOKUP_CODE(+) = AVTS.VISIT_TYPE_CODE
1076 AND FLVT.LANGUAGE(+) = userenv('LANG')
1077 AND FLVT1.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
1078 AND FLVT1.LOOKUP_CODE(+) = AVTS.STATUS_CODE
1079 AND FLVT1.LANGUAGE(+) = userenv('LANG')
1080 AND FLVT2.LOOKUP_TYPE(+) = 'AHL_LTP_SPACE_CATEGORY'
1081 AND FLVT2.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
1082 AND FLVT2.LANGUAGE(+) = userenv('LANG')
1083 AND FLVT3.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_PRIORITY'
1084 AND FLVT3.LOOKUP_CODE(+) = AVTS.PRIORITY_CODE
1085 AND FLVT3.LANGUAGE(+) = userenv('LANG')
1086 AND PA.PROJECT_ID = AVTS.PROJECT_TEMPLATE_ID
1087 AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+)
1088 AND AVTS.TEMPLATE_FLAG = 'N'
1089 AND AVTS.STATUS_CODE <> 'DELETED'
1090 AND UC.active_end_date is null
1091 AND AVTS.VISIT_ID = x_id;
1092
1093 c_visit_rec c_visit%ROWTYPE;
1094
1095 -- To find out all visit/template details
1096 CURSOR c_visit_details (x_id IN NUMBER) IS
1097 SELECT * FROM AHL_VISITS_VL
1098 WHERE VISIT_ID = x_id;
1099
1100 visit_rec c_visit_details%ROWTYPE;
1101
1102 -- Cursor to find master workorder name for the given visit
1103 CURSOR c_workorder_csr (x_id IN NUMBER) IS
1104 SELECT WORKORDER_NAME, WORKORDER_ID FROM AHL_WORKORDERS
1105 WHERE MASTER_WORKORDER_FLAG = 'Y' AND VISIT_ID = x_id
1106 /*B6512777 - sowsubra - there is no task associated with visit master wo, hence included the check below to get the visit master wo name*/
1107 AND VISIT_TASK_ID IS NULL;
1108
1109 -- CURSOR added to get the Project Template Name
1110 -- Post 11.5.10
1111 -- Fix for ADS bug# 4357001. Changed to use PA_PROJECTS_ALL table.
1112 CURSOR c_proj_template(p_proj_temp_id IN NUMBER) IS
1113 SELECT name FROM PA_PROJECTS_ALL
1114 WHERE project_id = p_proj_temp_id;
1115
1116 CURSOR c_uc_header(x_instance_id IN NUMBER) IS
1117 SELECT name, UNIT_CONFIG_HEADER_ID FROM ahl_unit_config_headers
1118 WHERE CSI_ITEM_INSTANCE_ID = x_instance_id
1119 AND active_end_date is null;
1120
1121 /*Added by sowsubra*/
1122 CURSOR c_get_subinv_loc_dtls(p_inv_locator_id IN NUMBER, p_org_id IN NUMBER) IS
1123 SELECT SUBINVENTORY_CODE, CONCATENATED_SEGMENTS
1124 FROM mtl_item_locations_kfv
1125 WHERE inventory_location_id = p_inv_locator_id;
1126
1127 l_sub_code VARCHAR2(10) := NULL;
1128 l_locator_code VARCHAR2(240) := NULL;
1129
1130 BEGIN
1131 -- Standard start of API savepoint
1132 SAVEPOINT Get_Visit_Details;
1133
1134 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1135 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure, p_visit_id - '||p_visit_id);
1136 END IF;
1137
1138 -- Initialize message list if p_init_msg_list is set to TRUE.
1139 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
1140 Fnd_Msg_Pub.initialize;
1141 END IF;
1142
1143 -- Initialize API return status to success
1144 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1145
1146 -- Standard call to check for call compatibility.
1147 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
1148 p_api_version,
1149 l_api_name,G_PKG_NAME) THEN
1150 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1151 END IF;
1152 ------------------------------Start of API Body------------------------------------
1153
1154 ----------------------------------------- Cursor ----------------------------------
1155 OPEN c_visit_details(p_visit_id);
1156 FETCH c_visit_details INTO visit_rec;
1157 CLOSE c_visit_details;
1158
1159 OPEN c_Visit(p_visit_id);
1160 FETCH c_visit INTO c_visit_rec;
1161 CLOSE c_Visit;
1162 ------------------------------------------ Start -----------------------------------
1163 -- get workorder name and Id added in 11.5.10
1164 OPEN c_workorder_csr(p_visit_id);
1165 FETCH c_workorder_csr INTO l_workorder_name, l_workorder_id;
1166 IF c_workorder_csr%FOUND THEN
1167 l_visit_rec.job_number := l_workorder_name;
1168 --l_visit_rec.workorder_id := l_workorder_id;
1169 END IF;
1170 CLOSE c_workorder_csr;
1171
1172 -- To find meaning for fnd_lookups code
1173 IF (visit_rec.project_flag IS NOT NULL) THEN
1174 SELECT MEANING INTO l_project_flag
1175 FROM FND_LOOKUP_VALUES_VL
1176 WHERE LOOKUP_CODE = visit_rec.project_flag
1177 AND LOOKUP_TYPE = 'AHL_YES_NO_TYPE';
1178 END IF;
1179 ----------------------------------- FOR VISITS --------------------------------------
1180 IF UPPER(c_visit_rec.template_flag) = 'N' THEN
1181 -- To find Unit Name on basis of Instance Id
1182 IF visit_rec.item_instance_id IS NOT NULL THEN
1183 OPEN c_uc_header(visit_rec.item_instance_id);
1184 FETCH c_uc_header INTO l_unit_name, l_unit_header_id;
1185 CLOSE c_uc_header;
1186
1187 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1188 fnd_log.string(fnd_log.level_statement,L_DEBUG,'item instance : '|| visit_rec.item_instance_id);
1189 fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_unit_name : '|| l_unit_name);
1190 fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_unit_header_id : '|| l_unit_header_id);
1191 END IF;
1192 END IF;
1193
1194 -- To find simulation plan name for the simulation id from LTP view
1195 IF (visit_rec.simulation_plan_id IS NOT NULL) THEN
1196 SELECT SIMULATION_PLAN_NAME INTO l_simulation_plan_name
1197 FROM AHL_SIMULATION_PLANS_VL
1198 WHERE SIMULATION_PLAN_ID = visit_rec.simulation_plan_id;
1199 ELSE
1200 l_simulation_plan_name := NULL;
1201 END IF;
1202
1203 -- Post 11.5.10
1204 -- Reema Start
1205 -- To check if visit starttime is not null then store time in HH4 format
1206 IF (c_visit_rec.START_DATE_TIME IS NOT NULL AND c_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
1207 l_hour := TO_NUMBER(TO_CHAR(c_visit_rec.START_DATE_TIME , 'HH24'));
1208 l_minute := TO_NUMBER(TO_CHAR(c_visit_rec.START_DATE_TIME , 'MI'));
1209 ELSE
1210 l_hour := NULL;
1211 c_visit_rec.START_DATE_TIME := NULL;
1212 END IF;
1213
1214 -- To check if visit closetime is not null then store time in HH4 format
1215 IF (visit_rec.CLOSE_DATE_TIME IS NOT NULL AND visit_rec.CLOSE_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
1216 l_hour_close := TO_NUMBER(TO_CHAR(visit_rec.CLOSE_DATE_TIME , 'HH24'));
1217 l_minute_close := TO_NUMBER(TO_CHAR(c_visit_rec.CLOSE_DATE_TIME , 'MI'));
1218 ELSE
1219 l_hour_close := NULL;
1220 visit_rec.CLOSE_DATE_TIME := Null;
1221 END IF;
1222
1223 -- Call local procedure to retrieve Due by Date of the visit
1224 Get_Due_by_Date(p_visit_id => l_visit_id, x_due_by_date => l_due_date);
1225
1226 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1227 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Due by date : '|| l_due_date);
1228 END IF;
1229
1230 -- Derive the visit end date
1231 IF (c_visit_rec.START_DATE_TIME IS NOT NULL
1232 AND c_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE
1233 AND c_visit_rec.DEPARTMENT_ID IS NOT NULL
1234 AND c_visit_rec.DEPARTMENT_ID <> FND_API.G_MISS_NUM) THEN
1235 l_visit_end_date:= AHL_VWP_TIMES_PVT.get_visit_end_time(p_visit_id);
1236 END IF;
1237
1238 -- Post 11.5.10
1239 -- get the project template name from cursor
1240 IF visit_rec.project_template_id IS NOT NULL THEN
1241 OPEN c_proj_template(visit_rec.project_template_id);
1242 FETCH c_proj_template INTO l_visit_rec.proj_template_name;
1243 IF c_proj_template%NOTFOUND THEN
1244 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1245 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_PROTEM');
1246 Fnd_Msg_Pub.ADD;
1247 RAISE Fnd_Api.G_EXC_ERROR;
1248 END IF;
1249 END IF;
1250 CLOSE c_proj_template;
1251 END IF;
1252
1253 /*Added by sowsubra*/
1254 IF (c_visit_rec.inv_locator_id IS NOT NULL) THEN
1255 OPEN c_get_subinv_loc_dtls(c_visit_rec.inv_locator_id,c_visit_rec.organization_id);
1256 FETCH c_get_subinv_loc_dtls into l_sub_code, l_locator_code;
1257 CLOSE c_get_subinv_loc_dtls;
1258 END IF;
1259
1260 -- Assigning all visits field to visit record attributes meant for display
1261 l_visit_rec.visit_id := c_visit_rec.visit_id ;
1262 l_visit_rec.visit_name := c_visit_rec.visit_name ;
1263 l_visit_rec.visit_number := c_visit_rec.visit_number ;
1264 l_visit_rec.status_code := c_visit_rec.status_code;
1265 l_visit_rec.status_name := c_visit_rec.status_mean;
1266 l_visit_rec.visit_type_code := c_visit_rec.visit_type_code ;
1267 l_visit_rec.visit_type_name := c_visit_rec.VISIT_TYPE_MEAN ;
1268 l_visit_rec.object_version_number := c_visit_rec.object_version_number ;
1269 l_visit_rec.inventory_item_id := c_visit_rec.inventory_item_id ;
1270 l_visit_rec.item_organization_id := c_visit_rec.item_organization_id ;
1271 l_visit_rec.item_name := c_visit_rec.ITEM_DESCRIPTION ;
1272 l_visit_rec.unit_name := l_unit_name ;
1273 l_visit_rec.unit_header_id := l_unit_header_id;
1274 l_visit_rec.item_instance_id := c_visit_rec.item_instance_id ;
1275 l_visit_rec.serial_number := c_visit_rec.serial_number ;
1276 l_visit_rec.service_request_id := c_visit_rec.service_request_id;
1277 l_visit_rec.service_request_number:= c_visit_rec.incident_number;
1278 l_visit_rec.space_category_code := c_visit_rec.space_category_code;
1279 l_visit_rec.space_category_name := c_visit_rec.space_category_mean;
1280 l_visit_rec.organization_id := c_visit_rec.organization_id ;
1281 l_visit_rec.org_name := c_visit_rec.ORGANIZATION_NAME ;
1282 l_visit_rec.department_id := c_visit_rec.department_id ;
1283 l_visit_rec.dept_name := c_visit_rec.DEPARTMENT_NAME ;
1284 l_visit_rec.start_date := c_visit_rec.START_DATE_TIME;
1285 l_visit_rec.start_hour := l_hour;
1286 l_visit_rec.START_MIN := l_minute;
1287 l_visit_rec.PLAN_END_DATE := visit_rec.CLOSE_DATE_TIME;
1288 l_visit_rec.PLAN_END_HOUR := l_hour_close;
1289 l_visit_rec.PLAN_END_MIN := l_minute_close;
1290 l_visit_rec.project_flag := l_project_flag;
1291 l_visit_rec.project_flag_code := visit_rec.project_flag;
1292 l_visit_rec.end_date := l_visit_end_date ;
1293 l_visit_rec.due_by_date := TRUNC(l_due_date);
1294 l_visit_rec.duration := NULL ;
1295 l_visit_rec.simulation_plan_id := visit_rec.simulation_plan_id ;
1296 l_visit_rec.simulation_plan_name := l_simulation_plan_name ;
1297 l_visit_rec.template_flag := c_visit_rec.template_flag ;
1298 l_visit_rec.description := visit_rec.description ;
1299 l_visit_rec.last_update_date := visit_rec.last_update_date;
1300 l_visit_rec.project_id := visit_rec.project_id;
1301 l_visit_rec.project_number := visit_rec.visit_number;
1302 l_visit_rec.outside_party_flag := visit_rec.outside_party_flag;
1303 -- Post 11.5.10
1304 -- Reema Start
1305 l_visit_rec.priority_code := visit_rec.priority_code;
1306 l_visit_rec.proj_template_id := visit_rec.project_template_id;
1307 l_visit_rec.priority_value := c_visit_rec.priority_mean;
1308 -- Reema End
1309 l_visit_rec.unit_schedule_id := visit_rec.unit_schedule_id;
1310
1311 /*Added by sowsubra*/
1312 l_visit_rec.subinventory := l_sub_code;
1313 l_visit_rec.LOCATOR_SEGMENT := l_locator_code;
1314
1315 --Arvind Rupakula -Flight Number changes
1316 IF (visit_rec.unit_schedule_id IS NOT NULL) THEN
1317 OPEN c_flight_number(visit_rec.unit_schedule_id);
1318 FETCH c_flight_number INTO l_flight_number;
1319 CLOSE c_flight_number;
1320 l_visit_rec.flight_number := l_flight_number;
1321 END IF;
1322 --End Changes
1323 x_visit_rec := l_visit_rec;
1324 END IF;
1325 ------------------------End of API Body------------------------------------
1326 -- Standard call to get message count and if count is 1, get message info
1327 Fnd_Msg_Pub.Count_And_Get
1328 ( p_count => x_msg_count,
1329 p_data => x_msg_data,
1330 p_encoded => Fnd_Api.g_false);
1331
1332 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1333 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
1334 END IF;
1335
1336 RETURN;
1337 EXCEPTION
1338 WHEN Fnd_Api.G_EXC_ERROR THEN
1339 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1340 ROLLBACK TO Get_Visit_Details;
1341 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1342 p_data => x_msg_data,
1343 p_encoded => Fnd_Api.g_false);
1344
1345 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1346 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1347 ROLLBACK TO Get_Visit_Details;
1348 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1349 p_data => x_msg_data,
1350 p_encoded => Fnd_Api.g_false);
1351
1352 WHEN OTHERS THEN
1353 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1354 ROLLBACK TO Get_Visit_Details;
1355 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1356 p_procedure_name => 'Get_Visit_Details',
1357 p_error_text => SQLERRM);
1358 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1359 p_data => x_msg_data,
1360 p_encoded => Fnd_Api.g_false);
1361 END Get_Visit_Details;
1362
1363 --------------------------------------------------------------------
1364 -- PROCEDURE
1365 --
1366 --
1367 -- PURPOSE
1368 -- To create a Maintainance Visit
1369 --------------------------------------------------------------------
1370 PROCEDURE Create_Visit (
1371 p_api_version IN NUMBER,
1372 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1373 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1374 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1375 p_module_type IN VARCHAR2 := 'JSP',
1376 p_x_visit_rec IN OUT NOCOPY Visit_Rec_Type,
1377 x_return_status OUT NOCOPY VARCHAR2,
1378 x_msg_count OUT NOCOPY NUMBER,
1379 x_msg_data OUT NOCOPY VARCHAR2
1380 )
1381 IS
1382 -- Define local Variables
1383 L_API_VERSION CONSTANT NUMBER := 1.0;
1384 L_API_NAME CONSTANT VARCHAR2(30) := 'Create Visit';
1385 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1386 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1387
1388 l_msg_data VARCHAR2(2000);
1389 l_unit_name VARCHAR2(80);
1390 l_item_name VARCHAR2(40);
1391 l_rowid VARCHAR2(30);
1392 l_date VARCHAR2(30);
1393 l_return_status VARCHAR2(1);
1394
1395 l_proj_temp_Id NUMBER;
1396 l_msg_count NUMBER;
1397 l_count NUMBER;
1398 l_dummy NUMBER;
1399 l_organization_id NUMBER;
1400 l_department_id NUMBER;
1401 l_item_id NUMBER;
1402 l_serial_id NUMBER;
1403 l_visit_number NUMBER;
1404 l_org_id NUMBER;
1405 l_simulation_plan_id NUMBER;
1406 l_service_id NUMBER;
1407 l_date_time DATE;
1408
1409 l_time VARCHAR2(30);
1410 --TC Changes
1411 l_release_flag VARCHAR2(1) := NULL;
1412 -- Post 11.5.10 Enhancements
1413 l_priority_code VARCHAR2(30);
1414 l_proj_template_id NUMBER;
1415 -- Define local record datatypes
1416 l_Visit_rec Visit_Rec_Type := p_x_Visit_rec;
1417 -- Define local Cursors
1418 -- To find the next id value from visit sequence
1419 CURSOR c_seq IS
1420 SELECT Ahl_Visits_B_S.NEXTVAL
1421 FROM dual;
1422
1423 -- To find whether id already exists
1424 CURSOR c_id_exists (x_id IN NUMBER) IS
1425 SELECT 1 FROM Ahl_Visits_VL
1426 WHERE Visit_id = x_id;
1427
1428 -- To find the maximum visit number among all visits
1429 CURSOR c_visit_number IS
1430 SELECT MAX(visit_number) FROM Ahl_Visits_B;
1431
1432 -- Fix for ADS bug# 4357001.
1433 -- Changed cursor to not use ahl_mtl_items_ou_v and replaced with mtl_system_items_kfv
1434 -- and added OU stripping so that Master Org does not belong to the current user's OU.
1435 CURSOR c_unit_det (p_unit_name IN VARCHAR2) IS
1436 /* SELECT uc.name ,
1437 csis.serial_number ,
1438 csis.instance_id,
1439 mtl.inventory_item_id ,
1440 mtl.inventory_org_id
1441 FROM ahl_mtl_items_ou_v mtl,
1442 ahl_unit_config_headers uc,
1443 csi_item_instances csis
1444 WHERE uc.name = p_unit_name
1445 AND uc.csi_item_instance_id=csis.instance_id
1446 AND AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(uc.UNIT_CONFIG_HEADER_ID) in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
1447 AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
1448 AND csis.inventory_item_id = mtl.inventory_item_id
1449 AND csis.inv_master_organization_id = mtl.inventory_org_id
1450 AND csis.serial_number IS NOT NULL
1451 AND csis.ACTIVE_START_DATE <= sysdate AND (csis.ACTIVE_END_DATE >= sysdate OR csis.ACTIVE_END_DATE IS NULL); */
1452
1453 /* SELECT uc.name ,
1454 csis.serial_number ,
1455 csis.instance_id,
1456 mtl.inventory_item_id ,
1457 --mtl.inventory_org_id
1458 csis.inv_master_organization_id
1459 FROM mtl_system_items_kfv mtl,
1460 ahl_unit_config_headers uc,
1461 csi_item_instances csis
1462 WHERE uc.name = p_unit_name
1463 AND uc.csi_item_instance_id=csis.instance_id
1464 AND upper(AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(uc.UNIT_CONFIG_HEADER_ID))in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
1465 AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
1466 AND csis.inventory_item_id = mtl.inventory_item_id
1467 AND csis.inv_master_organization_id = mtl.organization_id
1468 AND csis.serial_number IS NOT NULL
1469 AND csis.ACTIVE_START_DATE <= sysdate AND (csis.ACTIVE_END_DATE >= sysdate OR csis.ACTIVE_END_DATE
1470 IS NULL)
1471 AND csis.inv_master_organization_id IN ( SELECT mp.master_organization_id FROM org_organization_definitions org
1472 , mtl_parameters mp WHERE org.organization_id = mp.organization_id
1473 AND NVL(operating_unit, mo_global.get_current_org_id())
1474 = mo_global.get_current_org_id()); */
1475
1476 -- AnRaj: Changed for fixing the perf bug 4919502
1477 SELECT uc.name ,
1478 csis.serial_number ,
1479 csis.instance_id,
1480 mtl.inventory_item_id ,
1481 csis.inv_master_organization_id
1482 FROM mtl_system_items mtl,
1483 ahl_unit_config_headers uc,
1484 csi_item_instances csis
1485 WHERE uc.name = p_unit_name
1486 AND uc.csi_item_instance_id=csis.instance_id
1487 -- Fix by jaramana on June 27, 2006 for Bug 5360066
1488 -- AND upper(AHL_UTIL_UC_PKG.GET_UC_STATUS(uc.UNIT_CONFIG_HEADER_ID))in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
1489 AND AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(uc.UNIT_CONFIG_HEADER_ID)in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
1490 AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
1491 AND csis.inventory_item_id = mtl.inventory_item_id
1492 AND csis.inv_master_organization_id = mtl.organization_id
1493 AND csis.serial_number IS NOT NULL
1494 AND csis.ACTIVE_START_DATE <= sysdate
1495 AND (csis.ACTIVE_END_DATE >= sysdate OR csis.ACTIVE_END_DATE IS NULL)
1496 AND csis.inv_master_organization_id IN
1497 ( SELECT mp.master_organization_id
1498 FROM inv_organization_info_v org, mtl_parameters mp
1499 WHERE org.organization_id = mp.organization_id
1500 AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()
1501 );
1502
1503 /*Added by sowsubra*/
1504 CURSOR c_subinv_validate (p_subinv_code IN VARCHAR2, p_org_id IN VARCHAR2) IS
1505 SELECT status_id
1506 FROM mtl_secondary_inventories
1507 WHERE secondary_inventory_name = p_subinv_code
1508 AND organization_id = p_org_id;
1509
1510 /*Added by sowsubra*/
1511 CURSOR c_loc_validate (p_org_id IN NUMBER, p_subinv_code IN VARCHAR2, p_loc_seg IN VARCHAR2) IS
1512 -- jaramana on Feb 14, 2008 for bug 6819370
1513 -- Made segment19 and segment20 refer to base table
1514 SELECT mil.inventory_location_id
1515 from mtl_item_locations mil, mtl_item_locations_kfv milk
1516 where mil.organization_id = p_org_id
1517 and mil.subinventory_code = p_subinv_code
1518 and milk.concatenated_segments = p_loc_seg
1519 and mil.segment19 is NULL
1520 and mil.segment20 is NULL
1521 and mil.inventory_location_id = milk.inventory_location_id;
1522
1523 l_inv_loc_id NUMBER := 0;
1524 l_status_id NUMBER;
1525
1526 BEGIN
1527 --------------------- Initialize -----------------------
1528 SAVEPOINT Create_Visit;
1529
1530 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1531 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
1532 END IF;
1533
1534 -- Initialize message list if p_init_msg_list is set to TRUE.
1535 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
1536 Fnd_Msg_Pub.initialize;
1537 END IF;
1538
1539 -- Initialize API return status to success
1540 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1541
1542 -- Standard call to check for call compatibility.
1543 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
1544 p_api_version,
1545 l_api_name,G_PKG_NAME) THEN
1546 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1547 END IF;
1548 ------------------------Start of API Body------------------------------------
1549
1550 --------------------Value OR ID conversion---------------------------
1551 IF p_module_type = 'JSP' THEN
1552 l_Visit_rec.organization_id := NULL;
1553 l_Visit_rec.department_id := NULL;
1554 l_Visit_rec.item_instance_id := NULL;
1555 l_Visit_rec.service_request_id := NULL;
1556 END IF;
1557
1558 -- For VISIT STATUS
1559 -- To check visit status by default is Planning if not entered as input
1560 IF l_visit_rec.status_code IS NULL OR l_visit_rec.status_code = Fnd_Api.G_MISS_CHAR THEN
1561 l_Visit_rec.status_code := 'PLANNING';
1562 END IF;
1563
1564 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1565 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Status Code = ' || l_Visit_rec.status_code);
1566 END IF;
1567
1568 ----------- Starts defining all Dropdowns on Create Visit UI Screen-------------
1569 -- For SPACE CATEGORY CODE
1570 IF l_Visit_rec.space_category_code = Fnd_Api.G_MISS_CHAR THEN
1571 l_Visit_rec.space_category_code := Null;
1572 END IF;
1573
1574 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1575 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Space Category Code = ' || l_Visit_rec.space_category_code);
1576 END IF;
1577 ----------- End defining all Dropdowns on Create Visit UI Screen-------------
1578 ----------- Start defining and validate all LOVs on Create Visit UI Screen---
1579 -- For VISIT TYPE
1580 -- To find Visit Type Code when Visit Type Name has input values
1581 IF l_Visit_rec.visit_type_name IS NOT NULL AND
1582 l_Visit_rec.visit_type_name <> Fnd_Api.G_MISS_CHAR THEN
1583
1584 AHL_VWP_RULES_PVT.Check_Lookup_Name_Or_Id (
1585 p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
1586 p_lookup_code => NULL,
1587 p_meaning => l_Visit_rec.visit_type_name,
1588 p_check_id_flag => 'Y',
1589 x_lookup_code => l_Visit_rec.visit_type_code,
1590 x_return_status => l_return_status);
1591
1592 IF NVL(l_return_status, 'X') <> 'S' THEN
1593 Fnd_Message.SET_NAME('AHL','AHL_VWP_TYPE_CODE_NOT_EXISTS');
1594 Fnd_Msg_Pub.ADD;
1595 RAISE Fnd_Api.G_EXC_ERROR;
1596 END IF;
1597 END IF;
1598
1599 IF l_Visit_rec.unit_name IS NOT NULL AND l_Visit_rec.unit_name <> Fnd_Api.G_MISS_CHAR THEN
1600 OPEN c_unit_det(l_Visit_rec.unit_name);
1601 FETCH c_unit_det INTO l_Visit_rec.unit_name,l_Visit_rec.serial_number,l_Visit_rec.item_instance_id,
1602 l_Visit_rec.inventory_item_id,l_Visit_rec.item_organization_id;
1603 IF c_unit_det%NOTFOUND THEN
1604 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1605 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Unit does not exists..');
1606 END IF;
1607 Fnd_Message.SET_NAME('AHL','AHL_VWP_UNIT_NOT_EXISTS');
1608 Fnd_Msg_Pub.ADD;
1609 CLOSE c_unit_det;
1610 RAISE Fnd_Api.G_EXC_ERROR;
1611 END IF;
1612 CLOSE c_unit_det;
1613
1614 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1615 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Serial Id found ' || l_Visit_rec.item_instance_id);
1616 END IF;
1617 ELSE
1618 l_Visit_rec.item_instance_id := NULL;
1619 l_Visit_rec.inventory_item_id := NULL;
1620 l_Visit_rec.item_organization_id := NULL;
1621 l_Visit_rec.serial_number := NULL;
1622 END IF;
1623
1624 -- For ORGANIZATION
1625 -- To Convert Organization Name to Organization Id
1626 IF (l_Visit_rec.org_name IS NOT NULL AND
1627 l_Visit_rec.org_name <> Fnd_Api.G_MISS_CHAR) THEN
1628
1629 AHL_VWP_RULES_PVT.Check_Org_Name_Or_Id
1630 (p_organization_id => l_Visit_rec.organization_id,
1631 p_org_name => l_Visit_rec.org_name,
1632 x_organization_id => l_organization_id,
1633 x_return_status => l_return_status,
1634 x_error_msg_code => l_msg_data);
1635
1636 IF NVL(l_return_status,'x') <> 'S' THEN
1637 Fnd_Message.SET_NAME('AHL','AHL_VWP_ORG_NOT_EXISTS');
1638 Fnd_Msg_Pub.ADD;
1639 RAISE Fnd_Api.G_EXC_ERROR;
1640 END IF;
1641 END IF;
1642
1643 IF (l_Visit_rec.org_name IS NOT NULL AND
1644 l_Visit_rec.org_name <> Fnd_Api.G_MISS_CHAR ) THEN
1645 --Assign the returned value
1646 l_Visit_rec.organization_id := l_organization_id;
1647 /* ELSE
1648 l_Visit_rec.organization_id := NULL;*/
1649 END IF;
1650
1651 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1652 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Org Name/ID' || l_Visit_rec.org_name || '-' || l_Visit_rec.organization_id );
1653 END IF;
1654
1655 -- For DEPARTMENT
1656 -- To convert Department Name to Department Id
1657 IF (l_Visit_rec.dept_name IS NOT NULL AND
1658 l_Visit_rec.dept_name <> Fnd_Api.G_MISS_CHAR ) OR
1659 (l_Visit_rec.department_id IS NOT NULL AND
1660 l_Visit_rec.department_id <> Fnd_Api.G_MISS_NUM) THEN
1661
1662 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
1663 (p_organization_id => l_Visit_rec.organization_id,
1664 p_dept_name => l_Visit_rec.dept_name,
1665 p_department_id => l_Visit_rec.department_id,
1666 x_department_id => l_department_id,
1667 x_return_status => l_return_status,
1668 x_error_msg_code => l_msg_data);
1669
1670 IF NVL(l_return_status,'x') <> 'S' THEN
1671 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
1672 Fnd_Msg_Pub.ADD;
1673 RAISE Fnd_Api.G_EXC_ERROR;
1674 END IF;
1675
1676 AHL_VWP_RULES_PVT.CHECK_DEPARTMENT_SHIFT
1677 (p_dept_id => l_department_id,
1678 x_return_status => l_return_status);
1679
1680 IF NVL(l_return_status,'x') <> 'S' THEN
1681 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_DEPT_SHIFT');
1682 Fnd_Msg_Pub.ADD;
1683 RAISE Fnd_Api.G_EXC_ERROR;
1684 END IF;
1685 END IF;
1686
1687 IF (l_Visit_rec.dept_name IS NOT NULL AND l_Visit_rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
1688 --Assign the returned value
1689 l_Visit_rec.department_id := l_department_id;
1690 /* ELSE
1691 l_Visit_rec.department_id := NULL;*/
1692 END IF;
1693
1694 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1695 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Dept Id/Name = ' || l_Visit_rec.department_id || ' - ' || l_Visit_rec.dept_name);
1696 END IF;
1697
1698 -- For SERVICE REQUEST
1699 -- To Convert Service Request Number to Service Request Id
1700 IF (l_Visit_rec.service_request_number IS NOT NULL AND l_Visit_rec.service_request_number <> Fnd_Api.G_MISS_CHAR ) THEN
1701 AHL_VWP_RULES_PVT.Check_SR_Request_Number_Or_Id
1702 (p_service_id => l_Visit_rec.service_request_id,
1703 p_service_number => l_Visit_rec.service_request_number,
1704 x_service_id => l_service_id,
1705 x_return_status => l_return_status,
1706 x_error_msg_code => l_msg_data);
1707
1708 IF NVL(l_return_status,'x') <> 'S' THEN
1709 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERVICE_REQ_NOT_EXISTS');
1710 Fnd_Msg_Pub.ADD;
1711 RAISE Fnd_Api.g_exc_error;
1712 END IF;
1713
1714 --Assign the returned value
1715 l_Visit_rec.service_request_id := l_service_id;
1716
1717 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1718 fnd_log.string(fnd_log.level_statement,L_DEBUG,'SR Id/Number = ' || l_Visit_rec.service_request_id || '-' || l_Visit_rec.service_request_number );
1719 END IF;
1720 END IF;
1721
1722 ----------- End defining and validate all LOVs on Create Visit UI Screen---
1723 -- For VISIT START DATE TIME
1724 -- Convert time stamp for start date time
1725 IF l_Visit_rec.START_DATE IS NOT NULL AND l_Visit_rec.START_DATE <> fnd_api.g_miss_date THEN
1726 IF (l_Visit_rec.START_HOUR IS NOT NULL AND l_visit_rec.START_MIN IS NOT NULL)THEN
1727 l_date := TO_CHAR(l_Visit_rec.START_DATE, 'DD-MM-YYYY ') || l_Visit_rec.start_hour ||':'|| l_visit_rec.start_min;
1728 l_Visit_rec.START_DATE := TO_DATE(l_date, 'DD-MM-YYYY HH24:MI');
1729 ELSIF l_Visit_rec.START_HOUR IS NOT NULL THEN
1730 l_date := TO_CHAR(l_Visit_rec.START_DATE, 'DD-MM-YYYY ') || l_Visit_rec.start_hour || ':00';
1731 l_Visit_rec.START_DATE := TO_DATE(l_date, 'DD-MM-YYYY HH24:MI');
1732 ELSE
1733 l_date := TO_CHAR(l_Visit_rec.START_DATE, 'DD-MM-YYYY ') || '00' || ':00';
1734 l_Visit_rec.START_DATE := TO_DATE(l_date, 'DD-MM-YYYY HH24:MI');
1735 END IF;
1736 END IF;
1737
1738 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1739 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Start Date' || l_Visit_rec.START_DATE);
1740 END IF;
1741
1742 --Added by amagrawa for Transit Visit
1743 -- For VISIT PLANNED END DATE TIME
1744 -- Convert time stamp for planned end date time
1745 IF l_Visit_rec.plan_end_date IS NOT NULL AND l_Visit_rec.plan_end_date <> fnd_api.g_miss_date THEN
1746 IF (l_Visit_rec.plan_end_HOUR IS NOT NULL AND l_visit_rec.plan_end_MIN IS NOT NULL)THEN
1747 l_date := TO_CHAR(l_Visit_rec.plan_end_date, 'DD-MM-YYYY ') || l_Visit_rec.plan_end_hour || ':'||l_visit_rec.plan_end_min;
1748 l_Visit_rec.plan_end_date := TO_DATE(l_date, 'DD-MM-YYYY HH24:MI');
1749 ELSIF l_Visit_rec.plan_end_hour IS NOT NULL THEN
1750 l_date := TO_CHAR(l_Visit_rec.plan_end_date, 'DD-MM-YYYY ') || l_Visit_rec.plan_end_hour || ':00';
1751 l_Visit_rec.plan_end_date := TO_DATE(l_date, 'DD-MM-YYYY HH24:MI');
1752 ELSE
1753 l_date := TO_CHAR(l_Visit_rec.plan_end_date, 'DD-MM-YYYY ') || '00' || ':00';
1754 l_Visit_rec.plan_end_date := TO_DATE(l_date, 'DD-MM-YYYY HH24:MI');
1755 END IF;
1756 END IF;
1757
1758 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1759 fnd_log.string(fnd_log.level_statement,L_DEBUG,'End Date' || l_Visit_rec.plan_end_date);
1760 END IF;
1761
1762 -- To validate visit start date should be less than plan end date
1763 IF l_Visit_rec.START_DATE IS NOT NULL AND l_Visit_rec.plan_end_date IS NOT NULL THEN
1764 IF (l_Visit_rec.START_DATE > l_Visit_rec.plan_end_date) THEN
1765 Fnd_Message.SET_NAME('AHL','AHL_VWP_START_DT_GTR_CLOSE_DT');
1766 Fnd_Msg_Pub.ADD;
1767 RAISE Fnd_Api.G_EXC_ERROR;
1768 END IF;
1769 END IF;
1770 -- End of changes by amagrawa
1771 -- For SIMULATION PLAN
1772 -- To check whether any primary plan exists in LTP
1773 IF l_visit_rec.SIMULATION_PLAN_ID = Fnd_Api.G_MISS_NUM THEN
1774 l_visit_rec.SIMULATION_PLAN_ID := NULL;
1775 END IF;
1776
1777 IF (l_Visit_rec.SIMULATION_PLAN_ID IS NULL) THEN
1778 SELECT SIMULATION_PLAN_ID INTO l_simulation_plan_id
1779 FROM AHL_SIMULATION_PLANS_VL WHERE primary_plan_flag = 'Y';
1780
1781 l_Visit_rec.SIMULATION_PLAN_ID := l_simulation_plan_id;
1782
1783 IF l_simulation_plan_id IS NULL THEN
1784 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRI_PLN_NOT_EXIST');
1785 Fnd_Msg_Pub.ADD;
1786 RAISE Fnd_Api.G_EXC_ERROR;
1787 END IF;
1788 END IF;
1789
1790 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1791 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Simulation Id' || l_Visit_rec.SIMULATION_PLAN_ID);
1792 END IF;
1793
1794 -- Post 11.5.10 Enhancements
1795 -- For PRIORITY
1796 IF l_Visit_rec.priority_value IS NOT NULL AND l_Visit_rec.priority_value <> Fnd_Api.G_MISS_CHAR THEN
1797 AHL_VWP_RULES_PVT.Check_Lookup_Name_Or_Id
1798 (p_lookup_type => 'AHL_VWP_VISIT_PRIORITY',
1799 p_lookup_code => l_Visit_rec.priority_code,
1800 p_meaning => l_Visit_rec.priority_value,
1801 p_check_id_flag => 'Y',
1802 x_lookup_code => l_priority_code,
1803 x_return_status => l_return_status);
1804
1805 IF NVL(l_return_status, 'X') <> 'S' THEN
1806 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRI_NOT_EXISTS');
1807 Fnd_Msg_Pub.ADD;
1808 RAISE Fnd_Api.G_EXC_ERROR;
1809 END IF;
1810 l_visit_rec.priority_code := l_priority_code;
1811 END IF;
1812
1813 -- Post 11.5.10 Enhancements
1814 -- For PROJECT TEMPLATE
1815 IF l_visit_rec.proj_template_name IS NOT NULL THEN
1816 AHL_VWP_RULES_PVT.Check_Project_Template_Or_Id
1817 ( p_proj_temp_name => l_visit_rec.proj_template_name,
1818 x_project_id => l_proj_template_id,
1819 x_return_status => l_return_status,
1820 x_error_msg_code => l_msg_data);
1821
1822 IF NVL(l_return_status, 'X') <> 'S' THEN
1823 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_PROTEM');
1824 Fnd_Msg_Pub.ADD;
1825 RAISE Fnd_Api.G_EXC_ERROR;
1826 END IF;
1827 l_visit_rec.proj_template_id := l_proj_template_id;
1828 ELSE
1829 -- If Project Template Name is null
1830 -- then use the profile value
1831 l_visit_rec.proj_template_id := FND_PROFILE.VALUE('AHL_DEFAULT_PA_TEMPLATE_ID');
1832 END IF;
1833
1834 /*Added by sowsubra - starts - Issue#86 changes*/
1835 l_dummy := NULL;
1836
1837 IF ((l_visit_rec.subinventory IS NOT NULL) AND (l_visit_rec.locator_segment IS NULL)) THEN
1838 Fnd_Message.SET_NAME('AHL','AHL_VWP_LOCATOR_NULL');
1839 Fnd_Msg_Pub.ADD;
1840 RAISE Fnd_Api.G_EXC_ERROR;
1841 ELSIF ((l_visit_rec.subinventory IS NULL) AND (l_visit_rec.locator_segment IS NOT NULL))THEN
1842 Fnd_Message.SET_NAME('AHL','AHL_VWP_SUBINVENTORY_NULL');
1843 Fnd_Msg_Pub.ADD;
1844 RAISE Fnd_Api.G_EXC_ERROR;
1845 ELSIF ((l_visit_rec.subinventory IS NOT NULL) AND (l_visit_rec.locator_segment IS NOT NULL)) THEN
1846
1847 OPEN c_subinv_validate (l_visit_rec.subinventory, l_visit_rec.organization_id);
1848 FETCH c_subinv_validate INTO l_status_id;
1849 IF (c_subinv_validate%NOTFOUND) THEN
1850 CLOSE c_subinv_validate;
1851 Fnd_Message.SET_NAME('AHL','AHL_VWP_SUBINV_NOT_EXISTS');
1852 Fnd_Msg_Pub.ADD;
1853 RAISE Fnd_Api.G_EXC_ERROR;
1854 ELSE
1855 IF l_status_id in (NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'), -1), NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'), -1)) THEN
1856 CLOSE c_subinv_validate;
1857 FND_MESSAGE.SET_NAME('AHL', 'AHL_SUBINVENTORY_NOT_SVC');
1858 FND_MESSAGE.Set_Token('INV', l_visit_rec.subinventory);
1859 FND_MSG_PUB.ADD;
1860 RAISE Fnd_Api.G_EXC_ERROR;
1861 END IF;
1862 END IF;
1863 CLOSE c_subinv_validate;
1864
1865 l_dummy := NULL;
1866
1867 OPEN c_loc_validate (l_visit_rec.organization_id, l_visit_rec.subinventory,l_visit_rec.locator_segment );
1868 FETCH c_loc_validate INTO l_inv_loc_id;
1869 IF c_loc_validate%NOTFOUND THEN
1870 Fnd_Message.SET_NAME('AHL','AHL_VWP_PHY_LOCATOR_NOT_EXISTS');
1871 Fnd_Msg_Pub.ADD;
1872 CLOSE c_loc_validate;
1873 RAISE Fnd_Api.G_EXC_ERROR;
1874 END IF;
1875 CLOSE c_loc_validate;
1876
1877 l_visit_rec.inv_locator_id := l_inv_loc_id;
1878 ELSE
1879 l_visit_rec.inv_locator_id := NULL;
1880 END IF;
1881 /*Added by sowsubra - end - FP Issue#86 changes*/
1882
1883 -------------------------------- Validate -----------------------------------------
1884 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1885 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Validate_Visit');
1886 END IF;
1887
1888 Validate_Visit (
1889 p_api_version => l_api_version,
1890 p_init_msg_list => p_init_msg_list,
1891 p_commit => Fnd_Api.g_false,
1892 p_validation_level => p_validation_level,
1893 p_Visit_rec => l_Visit_rec,
1894 x_return_status => l_return_status,
1895 x_msg_count => x_msg_count,
1896 x_msg_data => x_msg_data
1897 );
1898
1899 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1900 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Validate_Visit - l_return_status : '||l_return_status);
1901 END IF;
1902
1903 -- Check for the ID.
1904 IF (l_Visit_rec.VISIT_ID = Fnd_Api.g_miss_num OR l_Visit_rec.VISIT_ID IS Null) THEN
1905 -- If the ID is not passed into the API, then
1906 -- grab a value from the sequence.
1907 OPEN c_seq;
1908 FETCH c_seq INTO l_Visit_rec.VISIT_ID;
1909 CLOSE c_seq;
1910
1911 -- Check to be sure that the sequence does not exist.
1912 OPEN c_id_exists (l_Visit_rec.VISIT_ID);
1913 FETCH c_id_exists INTO l_dummy;
1914 CLOSE c_id_exists;
1915
1916 -- If the value for the ID already exists, then
1917 -- l_dummy would be populated with '1', otherwise, it receives NULL.
1918 IF l_dummy IS NOT NULL THEN
1919 Fnd_Message.SET_NAME('AHL','AHL_VWP_SEQUENCE_NOT_EXISTS');
1920 Fnd_Msg_Pub.ADD;
1921 END IF;
1922
1923 -- For all optional fields check if its g_miss_num/g_miss_char/g_miss_date
1924 -- then Null else the value call Default_Missing_Attribs procedure
1925 Default_Missing_Attribs
1926 ( p_x_visit_rec => l_Visit_rec );
1927
1928 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1929 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit ID : '||l_Visit_rec.VISIT_ID);
1930 END IF;
1931 END IF;
1932
1933 --Standard check to count messages
1934 l_msg_count := Fnd_Msg_Pub.count_msg;
1935
1936 IF l_msg_count > 0 THEN
1937 x_msg_count := l_msg_count;
1938 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1939 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1940 END IF;
1941
1942 -- Check for Visit Number
1943 OPEN c_visit_number;
1944 FETCH c_visit_number INTO l_visit_number;
1945 CLOSE c_visit_number;
1946
1947 IF l_visit_number IS NOT NULL THEN
1948 l_visit_number := l_visit_number + 1;
1949 ELSE
1950 l_visit_number := 1;
1951 END IF;
1952
1953 l_Visit_rec.VISIT_NUMBER := l_visit_number;
1954
1955 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1956 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit ID =' || l_Visit_rec.VISIT_ID);
1957 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Number =' || l_Visit_rec.VISIT_NUMBER);
1958 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Name =' || l_Visit_rec.VISIT_Name);
1959 END IF;
1960
1961 -- Transit Check Changes Senthil.
1962 IF l_Visit_rec.unit_schedule_id IS NOT NULL
1963 AND l_Visit_rec.unit_schedule_id <> FND_API.G_MISS_NUM THEN
1964
1965 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1966 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_UA_FLIGHT_SCHEDULES_PVT.Validate_Flight_Schedule');
1967 END IF;
1968
1969 -- Validate with UMP API
1970 AHL_UA_FLIGHT_SCHEDULES_PVT.Validate_Flight_Schedule
1971 (
1972 P_API_VERSION => 1.0,
1973 X_RETURN_STATUS => l_return_status,
1974 X_MSG_COUNT => l_msg_count,
1975 X_MSG_DATA => l_msg_data,
1976 P_UNIT_CONFIG_ID => l_Visit_rec.unit_header_id,
1977 P_UNIT_SCHEDULE_ID => l_Visit_rec.unit_schedule_id
1978 );
1979
1980 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1981 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_UA_FLIGHT_SCHEDULES_PVT.Validate_Flight_Schedule - l_return_status : '||l_return_status);
1982 END IF;
1983
1984 IF l_msg_count > 0 THEN
1985 X_msg_count := l_msg_count;
1986 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1987 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1988 END IF;
1989
1990 -- Instance ID is madatory for transit visit
1991 IF l_Visit_rec.item_instance_id IS NULL
1992 OR l_Visit_rec.item_instance_id = FND_API.G_MISS_NUM THEN
1993 Fnd_Message.SET_NAME('AHL','AHL_VWP_TC_UNIT_REQ');
1994 Fnd_Msg_Pub.ADD;
1995 END IF;
1996
1997 -- Planned Start Date is madatory for transit visit
1998 IF l_visit_rec.START_DATE IS NULL OR l_visit_rec.START_DATE = FND_API.g_miss_date THEN
1999 Fnd_Message.SET_NAME('AHL','AHL_VWP_TC_ST_DT_REQ');
2000 Fnd_Msg_Pub.ADD;
2001 END IF;
2002
2003 -- Planned End Date is madatory for transit visit
2004 /*
2005 IF p_module_type = 'JSP' AND ( l_visit_rec.PLAN_END_DATE IS NULL
2006 OR l_visit_rec.PLAN_END_DATE = FND_API.g_miss_date) THEN
2007 l_visit_rec.PLAN_END_DATE:= l_visit_rec.START_DATE + (FND_PROFILE.VALUE('AHL_TRANSIT_VISIT_DEFAULT_DURATION')/1440);
2008 END IF;
2009 */
2010 ELSE
2011 l_Visit_rec.unit_schedule_id := NULL;
2012 END IF;
2013
2014 --Standard check to count messages
2015 l_msg_count := Fnd_Msg_Pub.count_msg;
2016
2017 IF l_msg_count > 0 THEN
2018 x_msg_count := l_msg_count;
2019 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2020 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2021 END IF;
2022
2023 -------------------------- Insert --------------------------
2024 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2025 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Ahl_Visits_Pkg.Insert_Row');
2026 END IF;
2027 -- Invoke the table handler to create a record
2028 -- Post 11.5.10 Enhancements
2029 -- Added Priority and Project Template Id
2030 -- Added Unit Schedule Id.
2031
2032 Ahl_Visits_Pkg.Insert_Row (
2033 X_ROWID => l_rowid,
2034 X_VISIT_ID => l_Visit_rec.VISIT_ID,
2035 X_VISIT_NUMBER => l_visit_number,
2036 X_VISIT_TYPE_CODE => l_Visit_rec.VISIT_TYPE_CODE,
2037 X_SIMULATION_PLAN_ID => l_Visit_rec.SIMULATION_PLAN_ID,
2038 X_ITEM_INSTANCE_ID => l_Visit_rec.ITEM_INSTANCE_ID,
2039 X_INVENTORY_ITEM_ID => l_Visit_rec.INVENTORY_ITEM_ID,
2040 X_ITEM_ORGANIZATION_ID => l_Visit_rec.ITEM_ORGANIZATION_ID,
2041 X_ASSO_PRIMARY_VISIT_ID => l_Visit_rec.ASSO_PRIMARY_VISIT_ID,
2042 X_SIMULATION_DELETE_FLAG => 'N',
2043 X_TEMPLATE_FLAG => 'N',
2044 X_OUT_OF_SYNC_FLAG => l_Visit_rec.OUT_OF_SYNC_FLAG,
2045 X_PROJECT_FLAG => 'Y',
2046 X_PROJECT_ID => l_Visit_rec.PROJECT_ID,
2047 X_SERVICE_REQUEST_ID => l_Visit_rec.SERVICE_REQUEST_ID,
2048 X_SPACE_CATEGORY_CODE => l_Visit_rec.SPACE_CATEGORY_CODE,
2049 X_SCHEDULE_DESIGNATOR => NULL,
2050 X_ATTRIBUTE_CATEGORY => l_Visit_rec.ATTRIBUTE_CATEGORY,
2051 X_ATTRIBUTE1 => l_Visit_rec.ATTRIBUTE1,
2052 X_ATTRIBUTE2 => l_Visit_rec.ATTRIBUTE2,
2053 X_ATTRIBUTE3 => l_Visit_rec.ATTRIBUTE3,
2054 X_ATTRIBUTE4 => l_Visit_rec.ATTRIBUTE4,
2055 X_ATTRIBUTE5 => l_Visit_rec.ATTRIBUTE5,
2056 X_ATTRIBUTE6 => l_Visit_rec.ATTRIBUTE6,
2057 X_ATTRIBUTE7 => l_Visit_rec.ATTRIBUTE7,
2058 X_ATTRIBUTE8 => l_Visit_rec.ATTRIBUTE8,
2059 X_ATTRIBUTE9 => l_Visit_rec.ATTRIBUTE9,
2060 X_ATTRIBUTE10 => l_Visit_rec.ATTRIBUTE10,
2061 X_ATTRIBUTE11 => l_Visit_rec.ATTRIBUTE11,
2062 X_ATTRIBUTE12 => l_Visit_rec.ATTRIBUTE12,
2063 X_ATTRIBUTE13 => l_Visit_rec.ATTRIBUTE13,
2064 X_ATTRIBUTE14 => l_Visit_rec.ATTRIBUTE14,
2065 X_ATTRIBUTE15 => l_Visit_rec.ATTRIBUTE15,
2066 X_OBJECT_VERSION_NUMBER => 1,
2067 X_ORGANIZATION_ID => l_Visit_rec.ORGANIZATION_ID,
2068 X_DEPARTMENT_ID => l_Visit_rec.DEPARTMENT_ID,
2069 X_STATUS_CODE => l_Visit_rec.STATUS_CODE,
2070 X_START_DATE_TIME => l_visit_rec.START_DATE,
2071 X_CLOSE_DATE_TIME => l_visit_rec.PLAN_END_DATE,
2072 X_PRICE_LIST_ID => NULL,
2073 X_ESTIMATED_PRICE => NULL,
2074 X_ACTUAL_PRICE => NULL,
2075 X_OUTSIDE_PARTY_FLAG => 'N',
2076 X_ANY_TASK_CHG_FLAG => 'N',
2077 X_VISIT_NAME => l_Visit_rec.VISIT_NAME,
2078 X_DESCRIPTION => l_Visit_rec.DESCRIPTION,
2079 X_CREATION_DATE => SYSDATE,
2080 X_CREATED_BY => Fnd_Global.USER_ID,
2081 X_LAST_UPDATE_DATE => SYSDATE,
2082 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2083 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
2084 X_PRIORITY_CODE => l_visit_rec.priority_code,
2085 X_PROJECT_TEMPLATE_ID => l_visit_rec.proj_template_id,
2086 X_UNIT_SCHEDULE_ID => l_Visit_rec.unit_schedule_id,
2087 X_INV_LOCATOR_ID => l_Visit_rec.inv_locator_id /*Added by sowsubra*/
2088 );
2089
2090 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2091 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Ahl_Visits_Pkg.Insert_Row');
2092 END IF;
2093
2094 -- set OUT value
2095 p_x_visit_rec.VISIT_ID := l_Visit_rec.VISIT_ID;
2096
2097 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2098 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_VISITS_STAGES_PVT.CREATE_STAGES');
2099 END IF;
2100
2101 -----CREATE STAGES IF VISIT HAS BEEN CREATED SUCESSFULLY--------
2102 AHL_VWP_VISITS_STAGES_PVT.CREATE_STAGES
2103 ( p_api_version => p_api_version,
2104 p_init_msg_list => p_init_msg_list,
2105 p_commit => Fnd_Api.g_false,
2106 p_validation_level => p_validation_level,
2107 p_module_type => p_module_type,
2108 p_visit_id => l_Visit_rec.VISIT_ID,
2109 x_return_status => x_return_status,
2110 x_msg_count => x_msg_count,
2111 x_msg_data => x_msg_data
2112 );
2113
2114 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2115 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_VISITS_STAGES_PVT.CREATE_STAGES - x_return_status : '||x_return_status);
2116 END IF;
2117
2118 IF l_Visit_rec.visit_type_code IS NOT NULL
2119 AND l_Visit_rec.visit_type_code <> FND_API.G_MISS_CHAR THEN
2120
2121 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2122 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TASKS_PVT.ASSOCIATE_DEFAULT_MRS');
2123 END IF;
2124
2125 AHL_VWP_TASKS_PVT.ASSOCIATE_DEFAULT_MRS
2126 (
2127 p_api_version => 1.0,
2128 x_return_status => x_return_status,
2129 x_msg_count => x_msg_count,
2130 x_msg_data => x_msg_data,
2131 p_visit_rec => l_Visit_rec
2132 );
2133
2134 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2135 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_TASKS_PVT.ASSOCIATE_DEFAULT_MRS - x_return_status : '||x_return_status);
2136 END IF;
2137
2138 --Standard check to count messages
2139 l_msg_count := Fnd_Msg_Pub.count_msg;
2140
2141 IF l_msg_count > 0 THEN
2142 X_msg_count := l_msg_count;
2143 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2144 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2145 END IF;
2146 END IF;
2147
2148 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2149 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TIMES_PVT.Calculate_Task_Times');
2150 END IF;
2151
2152 --Now adjust the times derivation for task
2153 AHL_VWP_TIMES_PVT.Calculate_Task_Times(p_api_version => 1.0,
2154 p_init_msg_list => Fnd_Api.G_FALSE,
2155 p_commit => Fnd_Api.G_FALSE,
2156 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2157 x_return_status => l_return_status,
2158 x_msg_count => l_msg_count,
2159 x_msg_data => l_msg_data,
2160 p_visit_id => l_visit_rec.visit_id);
2161
2162 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2163 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_TIMES_PVT.Calculate_Task_Times - l_return_status : '||l_return_status);
2164 END IF;
2165
2166 --Standard check to count messages
2167 l_msg_count := Fnd_Msg_Pub.count_msg;
2168
2169 IF l_msg_count > 0 THEN
2170 X_msg_count := l_msg_count;
2171 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2172 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2173 END IF;
2174
2175 -- Added by sjayacha as per Shailaja mail
2176 IF l_Visit_rec.unit_schedule_id IS NOT NULL
2177 AND l_Visit_rec.unit_schedule_id <> FND_API.G_MISS_NUM THEN
2178 l_date_time := NVL(NVL(l_visit_rec.PLAN_END_DATE,
2179 AHL_VWP_TIMES_PVT.get_visit_end_time(l_Visit_rec.visit_id)),
2180 l_visit_rec.START_DATE + (FND_PROFILE.VALUE('AHL_TRANSIT_VISIT_DEFAULT_DURATION')/1440));
2181
2182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2183 fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_date_time - '||l_date_time);
2184 END IF;
2185
2186 UPDATE ahl_visits_b
2187 SET close_date_time = l_date_time
2188 WHERE visit_id = l_Visit_rec.visit_id ;
2189 END IF;
2190
2191 IF l_Visit_rec.visit_create_type IS NOT NULL
2192 AND l_Visit_rec.visit_create_type <> FND_API.G_MISS_CHAR THEN
2193 IF l_Visit_rec.visit_create_type = 'PRODUCTION_RELEASED' THEN
2194 l_release_flag := 'Y';
2195 ELSIF l_Visit_rec.visit_create_type = 'PRODUCTION_UNRELEASED' THEN
2196 l_release_flag := 'N';
2197 END IF;
2198
2199 IF l_release_flag IS NOT NULL THEN
2200 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2201 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_PROJ_PROD_PVT.Release_Visit');
2202 END IF;
2203
2204 AHL_VWP_PROJ_PROD_PVT.Release_Visit (
2205 p_api_version => 1.0,
2206 /*p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2207 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2208 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FU */
2209 p_module_type => 'VWP',
2210 p_visit_id => l_Visit_rec.VISIT_ID,
2211 p_release_flag => l_release_flag,
2212 x_return_status => x_return_status,
2213 x_msg_count => x_msg_count,
2214 x_msg_data => x_msg_data);
2215
2216 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2217 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_PROJ_PROD_PVT.Release_Visit - x_return_status : '||x_return_status);
2218 END IF;
2219 END IF;
2220 END IF; -- l_Visit_rec.visit_create_type IS NOT NULL
2221 ---------------------------End of API Body---------------------------------------
2222 --Standard check to count messages
2223 l_msg_count := Fnd_Msg_Pub.count_msg;
2224
2225 IF l_msg_count > 0 THEN
2226 X_msg_count := l_msg_count;
2227 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2228 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2229 END IF;
2230
2231 --Standard check for commit
2232 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2233 COMMIT;
2234 END IF;
2235
2236 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2237 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
2238 END IF;
2239 EXCEPTION
2240 WHEN Fnd_Api.g_exc_error THEN
2241 ROLLBACK TO Create_Visit;
2242 x_return_status := Fnd_Api.g_ret_sts_error;
2243 Fnd_Msg_Pub.count_and_get(
2244 p_encoded => Fnd_Api.g_false,
2245 p_count => x_msg_count,
2246 p_data => x_msg_data
2247 );
2248 WHEN Fnd_Api.g_exc_unexpected_error THEN
2249 ROLLBACK TO Create_Visit;
2250 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2251 Fnd_Msg_Pub.count_and_get (
2252 p_encoded => Fnd_Api.g_false,
2253 p_count => x_msg_count,
2254 p_data => x_msg_data
2255 );
2256 WHEN OTHERS THEN
2257 ROLLBACK TO Create_Visit;
2258 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2259 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2260 THEN
2261 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2262 END IF;
2263 Fnd_Msg_Pub.count_and_get (
2264 p_encoded => Fnd_Api.g_false,
2265 p_count => x_msg_count,
2266 p_data => x_msg_data
2267 );
2268 END Create_Visit;
2269
2270 --------------------------------------------------------------------
2271 -- PROCEDURE
2272 --
2273 --
2274 -- PURPOSE
2275 -- To copy to Visit/Template from a Visit/Template
2276 --------------------------------------------------------------------
2277 PROCEDURE Copy_Visit (
2278 p_api_version IN NUMBER,
2279 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2280 p_commit IN VARCHAR2 := Fnd_Api.g_false,
2281 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2282 p_module_type IN VARCHAR2 := 'JSP',
2283 p_visit_id IN NUMBER,
2284 p_x_visit_rec IN OUT NOCOPY Visit_Rec_Type,
2285 x_return_status OUT NOCOPY VARCHAR2,
2286 x_msg_count OUT NOCOPY NUMBER,
2287 x_msg_data OUT NOCOPY VARCHAR2
2288 )
2289 IS
2290 -- Define local Variables
2291 L_API_VERSION CONSTANT NUMBER := 1.0;
2292 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Visit';
2293 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2294 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
2295
2296 l_msg_data VARCHAR2(2000);
2297 l_return_status VARCHAR2(1);
2298 l_msg_count NUMBER;
2299
2300 l_rowid VARCHAR2(30);
2301
2302 l_planned_order_flag VARCHAR2(1);
2303
2304 l_visit_id NUMBER;
2305 l_visit_number NUMBER;
2306
2307 l_price_changed VARCHAR2(1) := 'N';
2308 l_actual_price NUMBER;
2309 l_estimate_price NUMBER;
2310
2311 l_task_department_id NUMBER;
2312 l_visit_task_id NUMBER;
2313 l_parent_task_id NUMBER;
2314 l_new_parent_task_id NUMBER;
2315 l_new_task_id NUMBER;
2316
2317
2318 -- Define local record datatypes
2319 l_visit_rec Visit_Rec_Type := p_x_visit_rec;
2320
2321 -- Define local Cursors
2322 -- To find visit related information
2323 CURSOR c_visit (x_visit_id IN NUMBER) IS
2324 SELECT * FROM AHL_VISITS_VL
2325 WHERE VISIT_ID = x_visit_id;
2326 c_visit_rec c_visit%ROWTYPE;
2327
2328 -- To find task related information for a visit
2329 -- Dont copy deleted tasks to new visit.
2330 CURSOR c_task (x_visit_id IN NUMBER) IS
2331 SELECT * FROM AHL_VISIT_TASKS_VL
2332 WHERE VISIT_ID = x_visit_id AND NVL(STATUS_CODE,'X') <> 'DELETED';
2333 c_task_rec c_task%ROWTYPE;
2334
2335 -- To find task link related information for a visit
2336 CURSOR c_visit_task_links(x_visit_id IN NUMBER) IS
2337 SELECT VISIT_TASK_ID,
2338 PARENT_TASK_ID,
2339 --SECURITY_GROUP_ID,
2340 ATTRIBUTE_CATEGORY,
2341 ATTRIBUTE1,
2342 ATTRIBUTE2,
2343 ATTRIBUTE3,
2344 ATTRIBUTE4,
2345 ATTRIBUTE5,
2346 ATTRIBUTE6,
2347 ATTRIBUTE7,
2348 ATTRIBUTE8,
2349 ATTRIBUTE9,
2350 ATTRIBUTE10,
2351 ATTRIBUTE11,
2352 ATTRIBUTE12,
2353 ATTRIBUTE13,
2354 ATTRIBUTE14,
2355 ATTRIBUTE15
2356 FROM AHL_TASK_LINKS
2357 WHERE visit_task_id in ( SELECT VISIT_TASK_ID
2358 FROM AHL_VISIT_TASKS_B
2359 WHERE visit_id = x_visit_id);
2360
2361 l_task_link_rec c_visit_task_links%ROWTYPE;
2362
2363 -- To find the coresponding task id in the new visit
2364 CURSOR c_new_task_ID(x_visit_task_id IN NUMBER, x_new_visit_id IN NUMBER) IS
2365 SELECT b.VISIT_TASK_ID
2366 FROM AHL_VISIT_TASKS_B a, AHL_VISIT_TASKS_B b
2367 WHERE a.visit_task_id = x_visit_task_id
2368 AND a.visit_task_number = b.visit_task_number
2369 AND b.visit_id = x_new_visit_id;
2370
2371 BEGIN
2372 --------------------- Initialize -----------------------
2373 SAVEPOINT Copy_Visit;
2374
2375 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2376 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
2377 END IF;
2378
2379 -- Initialize message list if p_init_msg_list is set to TRUE.
2380 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
2381 Fnd_Msg_Pub.initialize;
2382 END IF;
2383
2384 -- Initialize API return status to success
2385 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2386
2387 -- Standard call to check for call compatibility.
2388 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
2389 p_api_version,
2390 l_api_name,G_PKG_NAME) THEN
2391 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2392 END IF;
2393 ------------------------Start of API Body------------------------------------
2394 -----------------------Value/Id conversions ----------------------
2395 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2396 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Id - '||p_visit_id);
2397 END IF;
2398
2399 OPEN c_visit(p_visit_id);
2400 FETCH c_visit INTO c_visit_rec;
2401 IF c_Visit%NOTFOUND THEN
2402 CLOSE c_Visit;
2403 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
2404 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
2405 Fnd_Msg_Pub.ADD;
2406 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2407 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit not found for' ||l_visit_rec.visit_id );
2408 END IF;
2409 END IF;
2410 RAISE Fnd_Api.g_exc_error;
2411 ELSE
2412 CLOSE c_Visit;
2413 END IF;
2414
2415 -- Check if the visit status is deleted.display error message if so. Added in 11.5.10
2416 IF UPPER(c_visit_rec.status_code) = 'DELETED' THEN
2417 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_STATUS');
2418 Fnd_Msg_Pub.ADD;
2419 RAISE Fnd_Api.G_EXC_ERROR;
2420 END IF;
2421
2422 -- org/dept/start date/end date are passed from UA
2423 -- Null check and org/dept/dates validation is already done from calling API Synchronize_Visit
2424 -- If organization/Department/Start Date changed then clear up price
2425 IF l_visit_rec.organization_id <> c_Visit_rec.organization_id
2426 OR l_visit_rec.department_id <> c_Visit_rec.department_id
2427 OR l_visit_rec.START_DATE <> c_Visit_rec.START_DATE_TIME THEN
2428 l_price_changed := 'Y';
2429 l_actual_price := NULL;
2430 l_estimate_price := NULL;
2431 ELSE
2432 l_actual_price := c_Visit_rec.ACTUAL_PRICE;
2433 l_estimate_price := c_Visit_rec.ESTIMATED_PRICE;
2434 END IF;
2435
2436 -- Get ID and Number for the new visit
2437 l_visit_id := Get_Visit_Id();
2438 l_visit_number := Get_Visit_Number();
2439
2440 -- Create New Visit
2441 Ahl_Visits_Pkg.Insert_Row (
2442 X_ROWID => l_rowid,
2443 X_VISIT_ID => l_Visit_ID,
2444 X_VISIT_NUMBER => l_visit_number,
2445 X_VISIT_TYPE_CODE => c_Visit_rec.VISIT_TYPE_CODE,
2446 X_SIMULATION_PLAN_ID => c_Visit_rec.simulation_plan_id,
2447 X_ITEM_INSTANCE_ID => c_Visit_rec.item_instance_id,
2448 X_INVENTORY_ITEM_ID => c_visit_rec.INVENTORY_ITEM_ID,
2449 X_ITEM_ORGANIZATION_ID => c_Visit_rec.ITEM_ORGANIZATION_ID,
2450 X_ASSO_PRIMARY_VISIT_ID => c_Visit_rec.asso_primary_visit_id,
2451 X_SIMULATION_DELETE_FLAG=> 'N',
2452 X_TEMPLATE_FLAG => c_Visit_rec.TEMPLATE_FLAG,
2453 X_OUT_OF_SYNC_FLAG => NULL,
2454 X_PROJECT_FLAG => c_Visit_rec.PROJECT_FLAG,
2455 X_PROJECT_ID => NULL,
2456 X_SERVICE_REQUEST_ID => c_Visit_rec.SERVICE_REQUEST_ID,
2457 X_SCHEDULE_DESIGNATOR => c_Visit_rec.SCHEDULE_DESIGNATOR,
2458 X_SPACE_CATEGORY_CODE => c_Visit_rec.SPACE_CATEGORY_CODE,
2459 X_ATTRIBUTE_CATEGORY => c_visit_rec.ATTRIBUTE_CATEGORY,
2460 X_ATTRIBUTE1 => c_visit_rec.ATTRIBUTE1,
2461 X_ATTRIBUTE2 => c_visit_rec.ATTRIBUTE2,
2462 X_ATTRIBUTE3 => c_Visit_rec.ATTRIBUTE3,
2463 X_ATTRIBUTE4 => c_Visit_rec.ATTRIBUTE4,
2464 X_ATTRIBUTE5 => c_Visit_rec.ATTRIBUTE5,
2465 X_ATTRIBUTE6 => c_Visit_rec.ATTRIBUTE6,
2466 X_ATTRIBUTE7 => c_Visit_rec.ATTRIBUTE7,
2467 X_ATTRIBUTE8 => c_Visit_rec.ATTRIBUTE8,
2468 X_ATTRIBUTE9 => c_Visit_rec.ATTRIBUTE9,
2469 X_ATTRIBUTE10 => c_Visit_rec.ATTRIBUTE10,
2470 X_ATTRIBUTE11 => c_Visit_rec.ATTRIBUTE11,
2471 X_ATTRIBUTE12 => c_Visit_rec.ATTRIBUTE12,
2472 X_ATTRIBUTE13 => c_Visit_rec.ATTRIBUTE13,
2473 X_ATTRIBUTE14 => c_Visit_rec.ATTRIBUTE14,
2474 X_ATTRIBUTE15 => c_Visit_rec.ATTRIBUTE15,
2475 X_OBJECT_VERSION_NUMBER => 1,
2476 X_ORGANIZATION_ID => l_Visit_rec.ORGANIZATION_ID,
2477 X_DEPARTMENT_ID => l_Visit_rec.DEPARTMENT_ID,
2478 X_STATUS_CODE => 'PLANNING',
2479 X_START_DATE_TIME => l_Visit_rec.START_DATE,
2480 X_CLOSE_DATE_TIME => l_Visit_rec.PLAN_END_DATE,
2481 X_PRICE_LIST_ID => c_Visit_rec.PRICE_LIST_ID,
2482 X_ESTIMATED_PRICE => l_estimate_price,
2483 X_ACTUAL_PRICE => l_actual_price,
2484 X_OUTSIDE_PARTY_FLAG => c_Visit_rec.OUTSIDE_PARTY_FLAG,
2485 X_ANY_TASK_CHG_FLAG => 'N',
2486 X_VISIT_NAME => c_Visit_rec.VISIT_NAME,
2487 X_DESCRIPTION => c_Visit_rec.DESCRIPTION,
2488 X_CREATION_DATE => SYSDATE,
2489 X_CREATED_BY => Fnd_Global.USER_ID,
2490 X_LAST_UPDATE_DATE => SYSDATE,
2491 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2492 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
2493 X_PRIORITY_CODE => c_Visit_rec.PRIORITY_CODE,
2494 X_PROJECT_TEMPLATE_ID => c_Visit_rec.PROJECT_TEMPLATE_ID,
2495 X_UNIT_SCHEDULE_ID => c_Visit_rec.UNIT_SCHEDULE_ID,
2496 X_INV_LOCATOR_ID => l_Visit_rec.inv_locator_id --Added by sowsubra
2497 );
2498
2499 -- Copy Tasks from originating visit
2500 OPEN c_task(p_visit_id);
2501 LOOP
2502 FETCH c_task INTO c_task_rec;
2503 EXIT WHEN c_task%NOTFOUND;
2504
2505 c_task_rec.visit_task_id := AHL_VWP_RULES_PVT.Get_Visit_Task_Id();
2506
2507 -- if visit org/dept/dates are changed, then clear up task price
2508 IF l_price_changed = 'Y' THEN
2509 l_actual_price := NULL;
2510 l_estimate_price := NULL;
2511 ELSE
2512 l_actual_price := c_task_rec.ACTUAL_PRICE;
2513 l_estimate_price := c_task_rec.ESTIMATED_PRICE;
2514 END IF;
2515
2516 -- if visit organization changed, then clear up task department
2517 IF l_visit_rec.organization_id <> c_Visit_rec.organization_id THEN
2518 l_task_department_id := NULL;
2519 ELSE
2520 l_task_department_id := c_task_rec.department_id;
2521 END IF;
2522
2523 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2524 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before AHL_VISIT_TASKS_PKG.INSERT_ROW');
2525 END IF;
2526
2527 -- Create task in new visit
2528 AHL_VISIT_TASKS_PKG.INSERT_ROW
2529 ( X_ROWID => l_rowid,
2530 X_VISIT_TASK_ID => c_task_rec.visit_task_id,
2531 X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
2532 X_OBJECT_VERSION_NUMBER => 1,
2533 X_VISIT_ID => l_visit_id,
2534 X_PROJECT_TASK_ID => NULL,
2535 X_COST_PARENT_ID => c_task_rec.cost_parent_id,
2536 X_MR_ROUTE_ID => c_task_rec.MR_ID,
2537 X_MR_ID => c_task_rec.MR_ROUTE_ID,
2538 X_DURATION => c_task_rec.duration,
2539 X_UNIT_EFFECTIVITY_ID => c_task_rec.UNIT_EFFECTIVITY_ID,
2540 X_START_FROM_HOUR => c_task_rec.start_from_hour,
2541 X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
2542 X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
2543 X_INSTANCE_ID => c_task_rec.instance_id,
2544 X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
2545 X_ORIGINATING_TASK_ID => c_task_rec.originating_task_id,
2546 X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
2547 X_TASK_TYPE_CODE => c_task_rec.task_type_code,
2548 X_DEPARTMENT_ID => l_task_department_id,
2549 X_SUMMARY_TASK_FLAG => c_task_rec.SUMMARY_TASK_FLAG,
2550 X_PRICE_LIST_ID => c_task_rec.PRICE_LIST_ID,
2551 X_STATUS_CODE => 'PLANNING',
2552 X_ESTIMATED_PRICE => l_estimate_price,
2553 X_ACTUAL_PRICE => l_actual_price,
2554 X_ACTUAL_COST => c_task_rec.ACTUAL_COST,
2555 X_STAGE_ID => c_task_rec.STAGE_ID,
2556 X_START_DATE_TIME => NULL,
2557 X_END_DATE_TIME => NULL,
2558 X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
2559 X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
2560 X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
2561 X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
2562 X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
2563 X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
2564 X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
2565 X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
2566 X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
2567 X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
2568 X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
2569 X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
2570 X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
2571 X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
2572 X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
2573 X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
2574 X_VISIT_TASK_NAME => c_task_rec.visit_task_name,
2575 X_DESCRIPTION => c_task_rec.description,
2576 X_QUANTITY => c_task_rec.quantity, -- Added by rnahata for Issue 105
2577 X_CREATION_DATE => SYSDATE,
2578 X_CREATED_BY => Fnd_Global.USER_ID,
2579 X_LAST_UPDATE_DATE => SYSDATE,
2580 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2581 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
2582
2583 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2584 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VISIT_TASKS_PKG.INSERT_ROW ');
2585 END IF;
2586
2587 -- Create Planned Material if task type is planned or unplanned
2588 IF c_task_rec.task_type_code in ('PLANNED','UNPLANNED') THEN
2589 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2590 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
2591 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Value of Visit Task ID : ' || c_task_rec.visit_task_id);
2592 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Value of Visit ID : ' || l_visit_id);
2593 END IF;
2594
2595 -- To call LTP Process Materials API for APS Integration
2596 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
2597 ( p_api_version => 1.0,
2598 p_init_msg_list => FND_API.g_false,
2599 p_commit => FND_API.g_false,
2600 p_validation_level => FND_API.g_valid_level_full,
2601 p_visit_id => l_Visit_Id,
2602 p_visit_task_id => c_task_rec.visit_task_id,
2603 p_org_id => NULL,
2604 p_start_date => NULL,
2605 p_operation_flag => 'C',
2606 x_planned_order_flag => l_planned_order_flag ,
2607 x_return_status => l_return_status,
2608 x_msg_count => l_msg_count,
2609 x_msg_data => l_msg_data );
2610
2611 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2612 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials : l_return_status - '||l_return_status);
2613 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Planned Order Flag : ' || l_planned_order_flag);
2614 END IF;
2615
2616 IF l_return_status <> 'S' THEN
2617 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2618 END IF;
2619 END IF;
2620 END LOOP;
2621 CLOSE c_task;
2622
2623 -- Copy task links from originating visit
2624 OPEN c_visit_task_links(p_visit_id);
2625 LOOP
2626 --FETCH c_visit_task_links INTO l_visit_task_id, l_parent_task_id;
2627 FETCH c_visit_task_links INTO l_task_link_rec;
2628 EXIT WHEN c_visit_task_links%NOTFOUND;
2629
2630 -- Find coresponding task id in new visit
2631 --OPEN c_new_task_ID(l_visit_task_id,l_visit_id);
2632 OPEN c_new_task_ID(l_task_link_rec.visit_task_id,l_visit_id);
2633 FETCH c_new_task_ID INTO l_new_task_id;
2634 CLOSE c_new_task_ID;
2635
2636 --OPEN c_new_task_ID(l_parent_task_id,l_visit_id);
2637 OPEN c_new_task_ID(l_task_link_rec.parent_task_id,l_visit_id);
2638 FETCH c_new_task_ID INTO l_new_parent_task_id;
2639 CLOSE c_new_task_ID;
2640
2641 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2642 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before inserting into AHL_TASK_LINKS');
2643 END IF;
2644
2645 -- Create task link
2646 INSERT INTO AHL_TASK_LINKS
2647 ( TASK_LINK_ID,
2648 OBJECT_VERSION_NUMBER,
2649 LAST_UPDATE_DATE,
2650 LAST_UPDATED_BY,
2651 CREATION_DATE,
2652 CREATED_BY,
2653 LAST_UPDATE_LOGIN,
2654 VISIT_TASK_ID,
2655 PARENT_TASK_ID,
2656 --SECURITY_GROUP_ID,
2657 ATTRIBUTE_CATEGORY,
2658 ATTRIBUTE1,
2659 ATTRIBUTE2,
2660 ATTRIBUTE3,
2661 ATTRIBUTE4,
2662 ATTRIBUTE5,
2663 ATTRIBUTE6,
2664 ATTRIBUTE7,
2665 ATTRIBUTE8,
2666 ATTRIBUTE9,
2667 ATTRIBUTE10,
2668 ATTRIBUTE11,
2669 ATTRIBUTE12,
2670 ATTRIBUTE13,
2671 ATTRIBUTE14,
2672 ATTRIBUTE15
2673 )
2674 VALUES
2675 (
2676 ahl_task_links_s.nextval,
2677 1,
2678 SYSDATE,
2679 Fnd_Global.USER_ID,
2680 SYSDATE,
2681 Fnd_Global.USER_ID,
2682 Fnd_Global.USER_ID,
2683 l_new_task_id ,
2684 l_new_parent_task_id,
2685 --l_task_link_rec.SECURITY_GROUP_ID,
2686 l_task_link_rec.ATTRIBUTE_CATEGORY,
2687 l_task_link_rec.ATTRIBUTE1,
2688 l_task_link_rec.ATTRIBUTE2,
2689 l_task_link_rec.ATTRIBUTE3,
2690 l_task_link_rec.ATTRIBUTE4,
2691 l_task_link_rec.ATTRIBUTE5,
2692 l_task_link_rec.ATTRIBUTE6,
2693 l_task_link_rec.ATTRIBUTE7,
2694 l_task_link_rec.ATTRIBUTE8,
2695 l_task_link_rec.ATTRIBUTE9,
2696 l_task_link_rec.ATTRIBUTE10,
2697 l_task_link_rec.ATTRIBUTE11,
2698 l_task_link_rec.ATTRIBUTE12,
2699 l_task_link_rec.ATTRIBUTE13,
2700 l_task_link_rec.ATTRIBUTE14,
2701 l_task_link_rec.ATTRIBUTE15
2702 );
2703
2704 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2705 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After inserting into AHL_TASK_LINKS');
2706 END IF;
2707
2708 END LOOP;
2709 CLOSE c_visit_task_links;
2710
2711 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2712 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TIMES_PVT.Calculate_Task_Times');
2713 END IF;
2714
2715 --Now adjust the times derivation for visit.
2716 AHL_VWP_TIMES_PVT.Calculate_Task_Times
2717 (p_api_version => 1.0,
2718 p_init_msg_list => Fnd_Api.G_FALSE,
2719 p_commit => Fnd_Api.G_FALSE,
2720 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
2721 x_return_status => l_return_status,
2722 x_msg_count => l_msg_count,
2723 x_msg_data => l_msg_data,
2724 p_visit_id => l_visit_id);
2725
2726 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2727 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_TIMES_PVT.Calculate_Task_Times - l_return_status : '||l_return_status);
2728 END IF;
2729
2730 -- Return ID of the new visit
2731 p_x_Visit_rec.visit_id := l_visit_id;
2732
2733 ---------------------------End of API Body---------------------------------------
2734 --Standard check to count messages
2735 l_msg_count := Fnd_Msg_Pub.count_msg;
2736
2737 IF l_msg_count > 0 THEN
2738 X_msg_count := l_msg_count;
2739 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2740 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2741 END IF;
2742
2743 --Standard check for commit
2744 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2745 COMMIT;
2746 END IF;
2747
2748 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2749 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
2750 END IF;
2751 EXCEPTION
2752 WHEN Fnd_Api.g_exc_error THEN
2753 ROLLBACK TO Copy_Visit;
2754 x_return_status := Fnd_Api.g_ret_sts_error;
2755 Fnd_Msg_Pub.count_and_get (
2756 p_encoded => Fnd_Api.g_false,
2757 p_count => x_msg_count,
2758 p_data => x_msg_data
2759 );
2760 WHEN Fnd_Api.g_exc_unexpected_error THEN
2761 ROLLBACK TO Copy_Visit;
2762 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2763 Fnd_Msg_Pub.count_and_get (
2764 p_encoded => Fnd_Api.g_false,
2765 p_count => x_msg_count,
2766 p_data => x_msg_data
2767 );
2768 WHEN OTHERS THEN
2769 ROLLBACK TO Copy_Visit;
2770 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2771 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2772 THEN
2773 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2774 END IF;
2775 Fnd_Msg_Pub.count_and_get (
2776 p_encoded => Fnd_Api.g_false,
2777 p_count => x_msg_count,
2778 p_data => x_msg_data
2779 );
2780 END Copy_Visit;
2781
2782 --------------------------------------------------------------------
2783 -- PROCEDURE
2784 -- Update_Visit
2785 --
2786 -- PURPOSE
2787 -- To update a Maintainance Visit.
2788 --------------------------------------------------------------------
2789 PROCEDURE Update_Visit (
2790 p_api_version IN NUMBER,
2791 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2792 p_commit IN VARCHAR2 := Fnd_Api.g_false,
2793 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2794 p_module_type IN VARCHAR2 := 'JSP',
2795 p_x_visit_rec IN OUT NOCOPY Visit_Rec_Type,
2796 x_return_status OUT NOCOPY VARCHAR2,
2797 x_msg_count OUT NOCOPY NUMBER,
2798 x_msg_data OUT NOCOPY VARCHAR2
2799 )
2800 IS
2801 -- Define local Variables
2802 L_API_VERSION CONSTANT NUMBER := 1.0;
2803 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Visit';
2804 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2805 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
2806
2807 l_msg_data VARCHAR2(2000);
2808 l_return_status VARCHAR2(1);
2809 l_msg_count NUMBER;
2810
2811 l_planned_order_flag VARCHAR2(1);
2812
2813
2814 l_count NUMBER;
2815 l_service_id NUMBER;
2816 l_organization_id NUMBER;
2817 l_department_id NUMBER;
2818
2819 l_visit_end_date DATE; --The visit end date
2820
2821 l_date_time_end DATE;
2822 l_date_time_start DATE;
2823
2824 space_changed_flag VARCHAR2(1):= 'N';
2825
2826 -- Post 11.5.10 Enhancements
2827 l_priority_code VARCHAR2(30);
2828 l_proj_template_id NUMBER;
2829
2830 -- Define local record datatypes
2831 l_visit_rec Visit_Rec_Type := p_x_visit_rec;
2832 l_workorder_rec AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_REC;
2833 l_workoper_tbl AHL_PRD_WORKORDER_PVT.PRD_WORKOPER_TBL;
2834 l_Space_Assignment_Rec ahl_ltp_space_assign_pub.Space_Assignment_Rec;
2835
2836 -- Define local Cursors
2837 -- To find visit related information
2838 CURSOR c_Visit(x_id IN NUMBER) IS
2839 SELECT * FROM Ahl_Visits_VL
2840 WHERE VISIT_ID = x_id;
2841
2842 c_Visit_rec c_Visit%ROWTYPE;
2843
2844 -- To find all tasks under this current visit related information
2845 CURSOR c_Task (x_id IN NUMBER) IS
2846 SELECT * FROM Ahl_Visit_Tasks_VL
2847 WHERE VISIT_ID = x_id
2848 and NVL(status_code, 'X') not in('DELETED', 'CANCELLED');
2849
2850 c_Task_rec c_Task%ROWTYPE;
2851
2852 -- To find if WIP job in Draft Status is created for the Visit
2853 CURSOR c_job(x_id IN NUMBER) IS
2854 SELECT count(*) FROM AHL_WORKORDERS
2855 WHERE VISIT_ID = x_id
2856 AND MASTER_WORKORDER_FLAG = 'Y'
2857 AND STATUS_CODE = 17;
2858
2859 CURSOR c_Visit_WO(x_id IN NUMBER) IS
2860 SELECT * FROM AHL_WORKORDERS
2861 WHERE VISIT_ID = x_id
2862 AND MASTER_WORKORDER_FLAG = 'Y'
2863 AND STATUS_CODE = 17;
2864
2865 l_workrec c_Visit_WO%ROWTYPE;
2866
2867 CURSOR c_Task_WO(x_task_id IN NUMBER) IS
2868 SELECT * FROM AHL_WORKORDERS
2869 WHERE VISIT_TASK_ID = x_task_id
2870 AND STATUS_CODE = 17;
2871
2872 l_task_workrec c_Task_WO%ROWTYPE;
2873
2874 /*Added by sowsubra*/
2875 CURSOR c_subinv_validate (p_subinv_code IN VARCHAR2, p_org_id IN VARCHAR2) IS
2876 SELECT status_id
2877 FROM mtl_secondary_inventories
2878 WHERE secondary_inventory_name = p_subinv_code
2879 AND organization_id = p_org_id;
2880
2881 /*Added by sowsubra*/
2882 CURSOR c_loc_validate (p_org_id IN NUMBER, p_subinv_code IN VARCHAR2, p_loc_seg IN VARCHAR2) IS
2883 -- jaramana on Feb 14, 2008 for bug 6819370
2884 -- Made segment19 and segment20 refer to base table
2885 SELECT mil.inventory_location_id
2886 from mtl_item_locations mil, mtl_item_locations_kfv milk
2887 where mil.organization_id = p_org_id
2888 and mil.subinventory_code = p_subinv_code
2889 and milk.concatenated_segments = p_loc_seg
2890 and mil.segment19 is NULL
2891 and mil.segment20 is NULL
2892 and mil.inventory_location_id = milk.inventory_location_id;
2893
2894 l_inv_loc_id NUMBER := 0;
2895 l_dummy NUMBER := 0;
2896 l_status_id NUMBER;
2897
2898 BEGIN
2899
2900 --------------------- Initialize -----------------------
2901 SAVEPOINT Update_Visit;
2902
2903 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2904 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure - Visit Id - '||l_visit_rec.visit_id ||'p_module_tyoe - '|| p_module_type);
2905 END IF;
2906
2907 -- Initialize message list if p_init_msg_list is set to TRUE.
2908 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
2909 Fnd_Msg_Pub.initialize;
2910 END IF;
2911
2912 -- Initialize API return status to success
2913 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2914
2915 -- Standard call to check for call compatibility.
2916 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
2917 p_api_version,
2918 l_api_name,G_PKG_NAME)
2919 THEN
2920 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2921 END IF;
2922
2923 ------------------------Start of API Body------------------------------------
2924 OPEN c_Visit(l_Visit_rec.visit_id);
2925 FETCH c_Visit INTO c_Visit_rec;
2926 IF c_Visit%NOTFOUND THEN
2927 CLOSE c_Visit;
2928 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
2929 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
2930 Fnd_Msg_Pub.ADD;
2931 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2932 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit not found for' ||l_visit_rec.visit_id );
2933 END IF;
2934 END IF;
2935 RAISE Fnd_Api.g_exc_error;
2936 ELSE
2937 CLOSE c_Visit;
2938 END IF;
2939
2940 -- To validate Object version number.
2941 IF (c_visit_rec.object_version_number <> l_visit_rec.object_version_number) THEN
2942 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2943 Fnd_Msg_Pub.ADD;
2944 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2945 END IF;
2946 --------------------Value OR ID conversion---------------------------
2947 -- Commented for TC changes by Senthil
2948 -- Uncommented by yazhou
2949 IF p_module_type = 'JSP' THEN
2950 l_Visit_rec.organization_id := NULL;
2951 l_Visit_rec.department_id := NULL;
2952 l_Visit_rec.item_instance_id := NULL;
2953 l_Visit_rec.service_request_id := NULL;
2954 l_visit_rec.proj_template_id := NULL;
2955
2956 IF l_Visit_rec.START_DATE IS NOT NULL AND l_Visit_rec.START_DATE <> fnd_api.g_miss_date THEN
2957 l_date_time_start := TO_DATE(TO_CHAR(l_visit_rec.START_DATE, 'DD-MM-YYYY ') ||
2958 TO_CHAR(NVL(l_visit_rec.START_HOUR,'00')) || ':'||TO_CHAR(NVL(l_visit_rec.START_MIN,'00')),'DD-MM-YYYY HH24:MI');
2959 ELSE
2960 l_Visit_rec.START_DATE:= null;
2961 l_visit_rec.START_HOUR:= null;
2962 l_date_time_start := null;
2963 END IF;
2964
2965 IF l_Visit_rec.PLAN_END_DATE IS NOT NULL AND l_Visit_rec.PLAN_END_DATE <> fnd_api.g_miss_date THEN
2966 l_date_time_end := TO_DATE(TO_CHAR(l_visit_rec.PLAN_END_DATE, 'DD-MM-YYYY ') ||
2967 TO_CHAR(NVL(l_visit_rec.PLAN_END_HOUR,'00')) || ':'|| TO_CHAR(NVL(l_visit_rec.PLAN_END_MIN,'00')) ,'DD-MM-YYYY HH24:MI');
2968 ELSE
2969 l_Visit_rec.PLAN_END_DATE:= null;
2970 l_visit_rec.PLAN_END_HOUR:= null;
2971 l_date_time_end :=null;
2972 END IF;
2973 ELSE
2974 IF l_Visit_rec.START_DATE IS NOT NULL AND l_Visit_rec.START_DATE <> fnd_api.g_miss_date THEN
2975 l_date_time_start := l_visit_rec.START_DATE;
2976 ELSE
2977 l_Visit_rec.START_DATE:= null;
2978 l_date_time_start := null;
2979 END IF;
2980
2981 IF l_Visit_rec.PLAN_END_DATE IS NOT NULL AND l_Visit_rec.PLAN_END_DATE <> fnd_api.g_miss_date THEN
2982 l_date_time_end := l_visit_rec.PLAN_END_DATE;
2983 ELSE
2984 l_Visit_rec.START_DATE:= null;
2985 l_date_time_end := null;
2986 END IF;
2987 END IF;
2988
2989 -------------------- UPDATE FOR VISIT ----------------
2990 -- Transit Visit change
2991 -- yazhou start
2992 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2993 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Status Code = ' || l_Visit_rec.status_code);
2994 END IF;
2995
2996 -- not allowed to update if status code is not planning, released or partially released.
2997 IF c_visit_rec.status_code NOT IN ('PLANNING','RELEASED', 'PARTIALLY RELEASED') THEN
2998 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_STATUS_NO_EDIT');
2999 Fnd_Msg_Pub.ADD;
3000 RAISE Fnd_Api.G_EXC_ERROR;
3001 END IF;
3002
3003 -- Process fields that are only updateable when visit is in Planning status
3004 IF c_visit_rec.status_code = 'PLANNING' THEN
3005 ----- Dropdowns on Update Visit UI Screen-------------
3006 -- For SPACE CATEGORY CODE
3007 IF l_Visit_rec.space_category_code = Fnd_Api.G_MISS_CHAR THEN
3008 l_Visit_rec.space_category_code := Null;
3009 END IF;
3010
3011 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3012 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Space Category Code = ' || l_Visit_rec.space_category_code);
3013 END IF;
3014
3015 ----------- LOVs on Update Visit UI Screen---
3016 -- For VISIT TYPE
3017 -- To find Visit Type Code when Visit Type Name has input values
3018 IF l_Visit_rec.visit_type_name IS NOT NULL AND l_Visit_rec.visit_type_name <> Fnd_Api.G_MISS_CHAR THEN
3019 AHL_VWP_RULES_PVT.Check_Lookup_Name_Or_Id
3020 ( p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
3021 p_lookup_code => NULL,
3022 p_meaning => l_Visit_rec.visit_type_name,
3023 p_check_id_flag => 'Y',
3024 x_lookup_code => l_Visit_rec.visit_type_code,
3025 x_return_status => l_return_status
3026 );
3027
3028 IF NVL(l_return_status, 'X') <> 'S' THEN
3029 Fnd_Message.SET_NAME('AHL','AHL_VWP_TYPE_CODE_NOT_EXISTS');
3030 Fnd_Msg_Pub.ADD;
3031 RAISE Fnd_Api.G_EXC_ERROR;
3032 END IF;
3033 END IF;
3034
3035 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3036 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Type' || l_Visit_rec.visit_type_name || '-' || l_Visit_rec.visit_type_code);
3037 END IF;
3038
3039 -- For SERVICE REQUEST
3040 -- To Convert Service Request Number to Service Request Id
3041 IF (l_Visit_rec.service_request_number IS NOT NULL AND
3042 l_Visit_rec.service_request_number <> Fnd_Api.G_MISS_CHAR ) THEN
3043
3044 AHL_VWP_RULES_PVT.Check_SR_Request_Number_Or_Id
3045 ( p_service_id => l_Visit_rec.service_request_id,
3046 p_service_number => l_Visit_rec.service_request_number,
3047 x_service_id => l_service_id,
3048 x_return_status => l_return_status,
3049 x_error_msg_code => l_msg_data);
3050
3051 IF NVL(l_return_status,'x') <> 'S' THEN
3052 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERVICE_REQ_NOT_EXISTS');
3053 Fnd_Msg_Pub.ADD;
3054 RAISE Fnd_Api.g_exc_error;
3055 END IF;
3056
3057 --Assign the returned value
3058 l_Visit_rec.service_request_id := l_service_id;
3059
3060 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3061 fnd_log.string(fnd_log.level_statement,L_DEBUG,'SR Id/Number = ' || l_Visit_rec.service_request_id || '-' || l_Visit_rec.service_request_number );
3062 END IF;
3063 END IF;
3064 ----------- End defining and validate all LOVs on Update Visit UI Screen---
3065 -- For Priority
3066 -- To Convert Priority Value to Code
3067 IF l_Visit_rec.priority_value IS NOT NULL AND l_Visit_rec.priority_value <> Fnd_Api.G_MISS_CHAR THEN
3068 AHL_VWP_RULES_PVT.Check_Lookup_Name_Or_Id
3069 (p_lookup_type => 'AHL_VWP_VISIT_PRIORITY',
3070 p_lookup_code => l_Visit_rec.priority_code,
3071 p_meaning => l_Visit_rec.priority_value,
3072 p_check_id_flag => 'Y',
3073 x_lookup_code => l_priority_code,
3074 x_return_status => l_return_status);
3075
3076 IF NVL(l_return_status, 'X') <> 'S' THEN
3077 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRI_NOT_EXISTS');
3078 Fnd_Msg_Pub.ADD;
3079 RAISE Fnd_Api.G_EXC_ERROR;
3080 END IF;
3081 l_visit_rec.priority_code := l_priority_code;
3082 END IF;
3083
3084 -- For Organization
3085 -- To Convert Organization Name to ID
3086 IF (l_visit_rec.ORG_NAME IS NOT NULL AND l_visit_rec.ORG_NAME <> Fnd_Api.G_MISS_CHAR ) OR
3087 (l_visit_rec.organization_id IS NOT NULL AND l_visit_rec.organization_id <> Fnd_Api.G_MISS_NUM )
3088 THEN
3089 AHL_VWP_RULES_PVT.Check_Org_Name_Or_Id
3090 (p_organization_id => l_visit_rec.organization_id,
3091 p_org_name => l_visit_rec.ORG_NAME,
3092 x_organization_id => l_organization_id,
3093 x_return_status => l_return_status,
3094 x_error_msg_code => l_msg_data);
3095
3096 IF NVL(l_return_status,'x') <> 'S' THEN
3097 Fnd_Message.SET_NAME('AHL','AHL_VWP_ORG_NOT_EXISTS');
3098 Fnd_Msg_Pub.ADD;
3099 RAISE Fnd_Api.G_EXC_ERROR;
3100 END IF;
3101
3102 l_visit_rec.organization_id := l_organization_id;
3103
3104 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3105 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Organization ID' || l_visit_rec.organization_id);
3106 END IF;
3107 END IF;
3108
3109 IF l_visit_rec.organization_id IS NULL OR l_visit_rec.organization_id = Fnd_Api.G_MISS_NUM THEN
3110 --Assign the department to Null if organization id is null
3111 l_visit_rec.department_id := NULL;
3112 l_visit_rec.organization_id:= NULL;
3113 ELSE
3114 IF (l_visit_rec.DEPT_NAME IS NOT NULL AND l_visit_rec.DEPT_NAME <> Fnd_Api.G_MISS_CHAR )OR
3115 (l_visit_rec.department_id IS NOT NULL AND l_visit_rec.department_id <> Fnd_Api.G_MISS_NUM )
3116 THEN
3117 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3118 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Inside Dept Name/ID - '||l_visit_rec.DEPT_NAME ||' - '||l_visit_rec.department_id );
3119 END IF;
3120
3121 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
3122 ( p_organization_id => l_visit_rec.organization_id,
3123 p_dept_name => l_visit_rec.DEPT_NAME,
3124 p_department_id => l_visit_rec.department_id,
3125 x_department_id => l_department_id,
3126 x_return_status => l_return_status,
3127 x_error_msg_code => l_msg_data);
3128
3129 IF NVL(l_return_status,'x') <> 'S' THEN
3130 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
3131 Fnd_Msg_Pub.ADD;
3132 RAISE Fnd_Api.G_EXC_ERROR;
3133 END IF;
3134
3135 l_visit_rec.department_id := l_department_id;
3136 AHL_VWP_RULES_PVT.CHECK_DEPARTMENT_SHIFT
3137 (p_dept_id => l_visit_rec.department_id,
3138 x_return_status => l_return_status);
3139
3140 IF NVL(l_return_status,'x') <> 'S' THEN
3141 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_DEPT_SHIFT');
3142 Fnd_Msg_Pub.ADD;
3143 RAISE Fnd_Api.G_EXC_ERROR;
3144 END IF;
3145
3146 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3147 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Department ID' || l_visit_rec.department_id);
3148 END IF;
3149 ELSE
3150 l_visit_rec.department_id := NULL;
3151 END IF ; --Dept Not Null
3152 END IF ; --Org Not Null
3153
3154 -- For Project Template
3155 -- To Convert Project Template Name to ID
3156 IF l_visit_rec.proj_template_name IS NOT NULL THEN
3157 AHL_VWP_RULES_PVT.Check_Project_Template_Or_Id
3158 ( p_proj_temp_name => l_visit_rec.proj_template_name,
3159 x_project_id => l_proj_template_id,
3160 x_return_status => l_return_status,
3161 x_error_msg_code => l_msg_data);
3162
3163 IF NVL(l_return_status, 'X') <> 'S' THEN
3164 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_PROTEMP');
3165 Fnd_Msg_Pub.ADD;
3166 RAISE Fnd_Api.G_EXC_ERROR;
3167 END IF;
3168 ELSE
3169 -- If Project Template Name is null
3170 -- then use the profile value
3171 l_proj_template_id := FND_PROFILE.VALUE('AHL_DEFAULT_PA_TEMPLATE_ID');
3172 END IF;
3173
3174 IF (l_proj_template_id <> c_visit_rec.project_template_id) AND (c_visit_rec.project_id IS NOT NULL) THEN
3175 -- Project Template cannot be updated if Project has been created for Visit
3176 Fnd_Message.SET_NAME('AHL','AHL_VWP_PROJ_CRTD');
3177 Fnd_Msg_Pub.ADD;
3178 RAISE Fnd_Api.G_EXC_ERROR;
3179 ELSE
3180 l_visit_rec.proj_template_id := l_proj_template_id;
3181 END IF;
3182
3183 /*BB5854712 - sowsubra - starts*/
3184 l_dummy := NULL;
3185
3186 IF ((l_visit_rec.subinventory IS NOT NULL) AND (l_visit_rec.locator_segment IS NULL)) THEN
3187 Fnd_Message.SET_NAME('AHL','AHL_VWP_LOCATOR_NULL');
3188 Fnd_Msg_Pub.ADD;
3189 RAISE Fnd_Api.G_EXC_ERROR;
3190 ELSIF ((l_visit_rec.subinventory IS NULL) AND (l_visit_rec.locator_segment IS NOT NULL))THEN
3191 Fnd_Message.SET_NAME('AHL','AHL_VWP_SUBINVENTORY_NULL');
3192 Fnd_Msg_Pub.ADD;
3193 RAISE Fnd_Api.G_EXC_ERROR;
3194 ELSIF ((l_visit_rec.subinventory IS NOT NULL) AND (l_visit_rec.locator_segment IS NOT NULL)) THEN
3195
3196 OPEN c_subinv_validate (l_visit_rec.subinventory, l_visit_rec.organization_id);
3197 FETCH c_subinv_validate INTO l_status_id;
3198 IF c_subinv_validate%NOTFOUND THEN
3199 CLOSE c_subinv_validate;
3200 Fnd_Message.SET_NAME('AHL','AHL_VWP_SUBINV_NOT_EXISTS');
3201 Fnd_Msg_Pub.ADD;
3202 RAISE Fnd_Api.G_EXC_ERROR;
3203 ELSE
3204 IF l_status_id in (NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'), -1), NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'), -1)) THEN
3205 CLOSE c_subinv_validate;
3206 FND_MESSAGE.SET_NAME('AHL', 'AHL_SUBINVENTORY_NOT_SVC');
3207 FND_MESSAGE.Set_Token('INV', l_visit_rec.subinventory);
3208 FND_MSG_PUB.ADD;
3209 RAISE Fnd_Api.G_EXC_ERROR;
3210 END IF;
3211 END IF;
3212 CLOSE c_subinv_validate;
3213
3214 l_dummy := NULL;
3215
3216 OPEN c_loc_validate (l_visit_rec.organization_id, l_visit_rec.subinventory,l_visit_rec.locator_segment );
3217 FETCH c_loc_validate INTO l_inv_loc_id;
3218 IF c_loc_validate%NOTFOUND THEN
3219 Fnd_Message.SET_NAME('AHL','AHL_VWP_PHY_LOCATOR_NOT_EXISTS');
3220 Fnd_Msg_Pub.ADD;
3221 CLOSE c_loc_validate;
3222 RAISE Fnd_Api.G_EXC_ERROR;
3223 END IF;
3224 CLOSE c_loc_validate;
3225
3226 l_visit_rec.inv_locator_id := l_inv_loc_id;
3227
3228 ELSE
3229 l_visit_rec.inv_locator_id := null;
3230 END IF;
3231 /*BB5854712 - sowsubra - ends*/
3232
3233 ELSE -- Visit in Released or Partially Released Status
3234 l_visit_rec.proj_template_id := c_visit_rec.project_template_id;
3235 l_visit_rec.priority_code := c_visit_rec.priority_code;
3236 l_Visit_rec.VISIT_TYPE_CODE :=c_Visit_rec.VISIT_TYPE_CODE;
3237 l_Visit_rec.SERVICE_REQUEST_ID := c_Visit_rec.SERVICE_REQUEST_ID;
3238 l_Visit_rec.VISIT_NAME := c_Visit_rec.VISIT_NAME;
3239 l_Visit_rec.DESCRIPTION := c_Visit_rec.DESCRIPTION;
3240 l_Visit_rec.ORGANIZATION_ID := c_Visit_rec.ORGANIZATION_ID;
3241 -------- R12 changes For Serial Number Reservations Start------------
3242 -------- AnRaj added condition on 17th June 2005 ------------
3243 IF p_module_type = 'JSP' THEN
3244 l_Visit_rec.DEPARTMENT_ID := c_Visit_rec.DEPARTMENT_ID;
3245 END IF;
3246 -------- R12 changes For Serial Number Reservations End---------------
3247 END IF; -- Visit in Planning Status
3248
3249 -- For Planned Start/End Date
3250 --- Planned start/end dates are madatory for transit visit
3251 --- AnRaj added
3252 --- Planned Start/End Dates are mandatory for visits which are in status Released/Partially Released
3253 --- as per the updates by Jay and Yan in the CMRO Forum, issue number 169
3254 IF c_visit_rec.status_code IN ('RELEASED', 'PARTIALLY RELEASED') THEN
3255 IF l_date_time_start IS NULL THEN
3256 Fnd_Message.SET_NAME('AHL','AHL_VWP_ST_DT_REQ');
3257 Fnd_Msg_Pub.ADD;
3258 RAISE FND_Api.G_EXC_ERROR;
3259 END IF;
3260
3261 IF l_date_time_end IS NULL THEN
3262 Fnd_Message.SET_NAME('AHL','AHL_VWP_END_DT_REQ');
3263 Fnd_Msg_Pub.ADD;
3264 RAISE FND_Api.G_EXC_ERROR;
3265 END IF;
3266 END IF;
3267
3268 IF c_Visit_rec.unit_schedule_id IS NOT NULL AND c_Visit_rec.unit_schedule_id <> FND_API.G_MISS_NUM
3269 THEN
3270 -- Planned Start Date is madatory for transit visit
3271 IF l_date_time_start IS NULL THEN
3272 Fnd_Message.SET_NAME('AHL','AHL_VWP_TC_ST_DT_REQ');
3273 Fnd_Msg_Pub.ADD;
3274 RAISE FND_Api.G_EXC_ERROR;
3275 END IF;
3276
3277 -- Planned End Date is madatory for transit visit
3278 IF l_date_time_end IS NULL THEN
3279 Fnd_Message.SET_NAME('AHL','AHL_VWP_TC_END_DT_REQ');
3280 Fnd_Msg_Pub.ADD;
3281 RAISE FND_Api.G_EXC_ERROR;
3282 END IF;
3283 END IF;
3284
3285 -- To validate visit start date should be less than plan end date
3286 IF l_date_time_end IS NOT NULL AND l_date_time_start IS NOT NULL THEN
3287 IF (l_date_time_start > l_date_time_end) THEN
3288 Fnd_Message.SET_NAME('AHL','AHL_VWP_START_DT_GTR_CLOSE_DT');
3289 Fnd_Msg_Pub.ADD;
3290 RAISE Fnd_Api.G_EXC_ERROR;
3291 END IF;
3292 END IF;
3293 l_visit_rec.STATUS_CODE := c_Visit_rec.STATUS_CODE;
3294
3295 -- If Visit dates are changed for a visit in released status
3296 -- then visit status needs to be changed to Partially Released
3297 IF NVL(TO_CHAR(l_date_time_start,'DD-MM-YYYY HH24:MI'),'XXX') <> NVL(TO_CHAR(c_Visit_rec.START_DATE_TIME,'DD-MM-YYYY HH24:MI'),'XXX')
3298 OR NVL(TO_CHAR(l_date_time_end,'DD-MM-YYYY HH24:MI'),'XXX') <> NVL(TO_CHAR(c_Visit_rec.CLOSE_DATE_TIME,'DD-MM-YYYY HH24:MI'),'XXX') THEN
3299 IF c_Visit_rec.STATUS_CODE = 'RELEASED' THEN
3300 l_visit_rec.STATUS_CODE := 'PARTIALLY RELEASED';
3301 END IF;
3302 END IF;
3303
3304 -- For all optional fields check if its g_miss_num/g_miss_char/g_miss_date
3305 -- then Null else the value call Default_Missing_Attribs procedure
3306 Default_Missing_Attribs
3307 (
3308 p_x_visit_rec => l_Visit_rec
3309 );
3310 ----------------------- Validate ----------------------
3311 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
3312 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3313 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Check_Visit_Items');
3314 END IF;
3315
3316 Check_Visit_Items (
3317 p_Visit_rec => l_visit_rec,
3318 p_validation_mode => Jtf_Plsql_Api.g_update,
3319 x_return_status => l_return_status
3320 );
3321
3322 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3323 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Check_Visit_Items : l_return_status - '|| l_return_status);
3324 END IF;
3325
3326 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3327 RAISE Fnd_Api.g_exc_unexpected_error;
3328 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
3329 RAISE Fnd_Api.g_exc_error;
3330 END IF;
3331 END IF;
3332
3333 -- if organization is changed, then reset all the task department and space changed flag
3334 -- and cancel all the workorders in production
3335 IF (NVL(l_visit_rec.organization_id,-777) <> NVL(c_visit_rec.organization_id,-777)) THEN
3336 -- To set department_id to NULL in case if the visit's organization is changed
3337 OPEN c_task(l_visit_rec.visit_id);
3338 LOOP
3339 FETCH c_task INTO c_task_rec;
3340 EXIT WHEN c_task%NOTFOUND;
3341 -- Tasks found for visit
3342 -- To update department_id to NULL when visit's organization is changed
3343 UPDATE AHL_VISIT_TASKS_B
3344 SET DEPARTMENT_ID = NULL,
3345 OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
3346 WHERE VISIT_TASK_ID = c_task_rec.visit_task_id
3347 AND DEPARTMENT_ID IS NOT NULL;
3348 END LOOP;
3349 CLOSE c_task;
3350
3351 SPACE_CHANGED_FLAG := 'Y';
3352 -- To find out if visit has workorder in production store this info in rec type
3353 OPEN c_Visit_WO (l_visit_rec.visit_id);
3354 FETCH c_visit_WO INTO l_workrec;
3355 IF c_visit_WO%FOUND THEN
3356 CLOSE c_visit_WO;
3357
3358 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3359 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_PRD_WORKORDER_PVT.cancel_visit_jobs');
3360 END IF;
3361
3362 AHL_PRD_WORKORDER_PVT.cancel_visit_jobs
3363 (p_api_version => 1.0,
3364 p_init_msg_list => FND_API.G_TRUE,
3365 p_commit => FND_API.G_FALSE,
3366 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3367 p_default => FND_API.G_FALSE,
3368 p_module_type => NULL,
3369 x_return_status => l_return_status,
3370 x_msg_count => l_msg_count,
3371 x_msg_data => l_msg_data,
3372 p_visit_id => l_Visit_rec.visit_id,
3373 p_unit_effectivity_id => NULL,
3374 p_workorder_id => NULL);
3375
3376 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3377 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_PRD_WORKORDER_PVT.cancel_visit_jobs - l_return_status : '||l_return_status);
3378 END IF;
3379
3380 IF l_return_status <> 'S' THEN
3381 RAISE Fnd_Api.G_EXC_ERROR;
3382 END IF;
3383 ELSE
3384 CLOSE c_visit_WO;
3385 END IF;
3386 END IF;
3387
3388 -- if department is changed, then set space changed flag
3389 --All the wip jobs for this visit to be updated with new department and start/end time;
3390 IF space_changed_flag <> 'Y' THEN
3391 IF (NVL(l_visit_rec.department_id,-777) <> NVL(c_visit_rec.department_id,-777)) THEN
3392 space_changed_flag := 'Y';
3393 -- cancel all the workorders for the visit if visit department is cleared up.
3394 IF l_visit_rec.department_id is null and c_visit_rec.department_id is not null THEN
3395 OPEN c_Visit_WO (l_visit_rec.visit_id);
3396 FETCH c_visit_WO INTO l_workrec;
3397 IF c_visit_WO%found THEN
3398 close c_visit_WO;
3399
3400 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3401 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_PRD_WORKORDER_PVT.cancel_visit_jobs');
3402 END IF;
3403
3404 AHL_PRD_WORKORDER_PVT.cancel_visit_jobs
3405 (p_api_version => 1.0,
3406 p_init_msg_list => FND_API.G_TRUE,
3407 p_commit => FND_API.G_FALSE,
3408 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3409 p_default => FND_API.G_FALSE,
3410 p_module_type => NULL,
3411 x_return_status => l_return_status,
3412 x_msg_count => l_msg_count,
3413 x_msg_data => l_msg_data,
3414 p_visit_id => l_Visit_rec.visit_id,
3415 p_unit_effectivity_id => NULL,
3416 p_workorder_id => NULL);
3417
3418 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3419 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_PRD_WORKORDER_PVT.cancel_visit_jobs - l_return_status : '||l_return_status);
3420 END IF;
3421
3422 IF l_return_status <> 'S' THEN
3423 RAISE Fnd_Api.G_EXC_ERROR;
3424 END IF;
3425 ELSE
3426 close c_visit_WO;
3427 END IF;
3428 ELSIF l_visit_rec.department_id is not null
3429 and c_visit_rec.department_id is not null
3430 and l_visit_rec.department_id <>c_visit_rec.department_id THEN
3431
3432 l_visit_end_date:= AHL_VWP_TIMES_PVT.get_visit_end_time(l_visit_rec.visit_id);
3433 -- To find out if visit has workorder in production store this info in rec type
3434 OPEN c_Visit_WO (l_visit_rec.visit_id);
3435 FETCH c_visit_WO INTO l_workrec;
3436
3437 IF c_visit_WO%found THEN
3438 l_workorder_rec.WORKORDER_ID := l_workrec.workorder_id;
3439 l_workorder_rec.OBJECT_VERSION_NUMBER := l_workrec.object_version_number;
3440 l_workorder_rec.DEPARTMENT_ID := l_visit_rec.department_id;
3441 -- l_workorder_rec.SCHEDULED_START_DATE := l_date_time_start;
3442 -- l_workorder_rec.SCHEDULED_END_DATE := l_visit_end_date;
3443
3444 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3445 fnd_log.string(fnd_log.level_statement,L_DEBUG,'VISIT - Before calling AHL_PRD_WORKORDER_PVT.update_job API');
3446 END IF;
3447
3448 AHL_PRD_WORKORDER_PVT.update_job
3449 ( p_api_version =>1.0,
3450 p_init_msg_list =>fnd_api.g_false,
3451 p_commit =>fnd_api.g_false,
3452 p_validation_level =>p_validation_level,
3453 p_default =>fnd_api.g_false,
3454 p_module_type =>'API',
3455 x_return_status =>l_return_status,
3456 x_msg_count =>x_msg_count,
3457 x_msg_data =>x_msg_data,
3458 p_wip_load_flag =>'Y',
3459 p_x_prd_workorder_rec =>l_workorder_rec,
3460 P_X_PRD_WORKOPER_TBL =>l_workoper_tbl
3461 );
3462
3463 IF l_return_status <> 'S' THEN
3464 CLOSE c_visit_WO;
3465 RAISE Fnd_Api.G_EXC_ERROR;
3466 END IF;
3467
3468 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3469 fnd_log.string(fnd_log.level_statement,L_DEBUG,'VISIT - After calling AHL_PRD_WORKORDER_PVT.update_job API - l_return_status : '||l_return_status);
3470 END IF;
3471 END IF; -- End of visit workorder found
3472 CLOSE c_visit_WO;
3473
3474 -- To find all tasks for the visit so as to update task start and end datetime
3475 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3476 fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - To update task start and end date time');
3477 END IF;
3478
3479 OPEN c_task(l_visit_rec.visit_id);
3480 LOOP
3481 FETCH c_task INTO c_task_rec;
3482 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3483 fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - ID = ' || c_task_rec.visit_task_id);
3484 END IF;
3485
3486 -- Update workorder for the task only if task is using visit department
3487 IF c_task_rec.department_id is null OR c_task_rec.department_id = FND_API.g_miss_num THEN
3488 OPEN c_Task_WO(c_task_rec.visit_task_id);
3489 FETCH c_Task_WO into l_task_workrec;
3490
3491 IF c_Task_WO%found THEN
3492 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3493 fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - Workorder found ');
3494 END IF;
3495 --Update with new times from table
3496 --l_workorder_rec.SCHEDULED_START_DATE := c_task_rec.START_DATE_TIME;
3497 --l_workorder_rec.SCHEDULED_END_DATE := c_task_rec.END_DATE_TIME;
3498 l_workorder_rec.DEPARTMENT_ID := l_visit_rec.department_id;
3499 l_workorder_rec.WORKORDER_ID := l_task_workrec.workorder_id;
3500 l_workorder_rec.OBJECT_VERSION_NUMBER := l_task_workrec.object_version_number;
3501
3502 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3503 fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - Workorder Id = ' || l_workorder_rec.WORKORDER_ID);
3504 fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - Before calling AHL_PRD_WORKORDER_PVT.update_job');
3505 END IF;
3506
3507 AHL_PRD_WORKORDER_PVT.update_job
3508 ( p_api_version =>1.0,
3509 p_init_msg_list =>fnd_api.g_false,
3510 p_commit =>fnd_api.g_false,
3511 p_validation_level =>p_validation_level,
3512 p_default =>fnd_api.g_false,
3513 p_module_type =>'API',
3514 x_return_status =>l_return_status,
3515 x_msg_count =>x_msg_count,
3516 x_msg_data =>x_msg_data,
3517 p_wip_load_flag =>'Y',
3518 p_x_prd_workorder_rec =>l_workorder_rec,
3519 P_X_PRD_WORKOPER_TBL =>l_workoper_tbl
3520 );
3521
3522 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3523 fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - After calling AHL_PRD_WORKORDER_PVT.update_job - l_return_status : '||l_return_status);
3524 END IF;
3525
3526 IF l_return_status <> 'S' THEN
3527 CLOSE c_Task_WO;
3528 CLOSE c_task;
3529 RAISE Fnd_Api.G_EXC_ERROR;
3530 END IF;
3531 END IF;
3532 CLOSE c_Task_WO;
3533 END IF;
3534 EXIT WHEN c_task%NOTFOUND;
3535 END LOOP;
3536 CLOSE c_task;
3537 END IF;
3538 END IF;
3539 END IF;
3540
3541 -- if start date (hour change is not considered) is changed, then set space changed flag
3542 IF space_changed_flag <> 'Y' THEN
3543 IF NVL(TO_CHAR(l_date_time_start,'DD-MM-YYYY'),'XXX') <> NVL(TO_CHAR(c_Visit_rec.START_DATE_TIME,'DD-MM-YYYY'),'XXX') THEN
3544 space_changed_flag := 'Y';
3545
3546 END IF;
3547 END IF;
3548 -------------------------- Update --------------------
3549 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3550 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Ahl_Visits_Pkg.UPDATE_ROW');
3551 END IF;
3552
3553 Ahl_Visits_Pkg.UPDATE_ROW (
3554 X_VISIT_ID => l_Visit_rec.VISIT_ID,
3555 X_VISIT_NUMBER => c_visit_rec.VISIT_NUMBER,
3556 X_VISIT_TYPE_CODE => l_Visit_rec.VISIT_TYPE_CODE,
3557 X_SIMULATION_PLAN_ID => c_Visit_rec.SIMULATION_PLAN_ID,
3558 X_ITEM_INSTANCE_ID => c_Visit_rec.ITEM_INSTANCE_ID,
3559 X_ITEM_ORGANIZATION_ID => c_Visit_rec.ITEM_ORGANIZATION_ID,
3560 X_INVENTORY_ITEM_ID => c_Visit_rec.INVENTORY_ITEM_ID,
3561 X_ASSO_PRIMARY_VISIT_ID => c_Visit_rec.ASSO_PRIMARY_VISIT_ID,
3562 X_SIMULATION_DELETE_FLAG => c_Visit_rec.SIMULATION_DELETE_FLAG,
3563 X_TEMPLATE_FLAG => c_Visit_rec.TEMPLATE_FLAG,
3564 X_OUT_OF_SYNC_FLAG => c_Visit_rec.OUT_OF_SYNC_FLAG,
3565 X_PROJECT_FLAG => 'Y',
3566 X_PROJECT_ID => c_Visit_rec.PROJECT_ID,
3567 X_SERVICE_REQUEST_ID => l_Visit_rec.SERVICE_REQUEST_ID,
3568 X_SPACE_CATEGORY_CODE => l_Visit_rec.SPACE_CATEGORY_CODE,
3569 X_SCHEDULE_DESIGNATOR => c_Visit_rec.SCHEDULE_DESIGNATOR,
3570 X_ATTRIBUTE_CATEGORY => c_Visit_rec.ATTRIBUTE_CATEGORY,
3571 X_ATTRIBUTE1 => c_Visit_rec.ATTRIBUTE1,
3572 X_ATTRIBUTE2 => c_Visit_rec.ATTRIBUTE2,
3573 X_ATTRIBUTE3 => c_Visit_rec.ATTRIBUTE3,
3574 X_ATTRIBUTE4 => c_Visit_rec.ATTRIBUTE4,
3575 X_ATTRIBUTE5 => c_Visit_rec.ATTRIBUTE5,
3576 X_ATTRIBUTE6 => c_Visit_rec.ATTRIBUTE6,
3577 X_ATTRIBUTE7 => c_Visit_rec.ATTRIBUTE7,
3578 X_ATTRIBUTE8 => c_Visit_rec.ATTRIBUTE8,
3579 X_ATTRIBUTE9 => c_Visit_rec.ATTRIBUTE9,
3580 X_ATTRIBUTE10 => c_Visit_rec.ATTRIBUTE10,
3581 X_ATTRIBUTE11 => c_Visit_rec.ATTRIBUTE11,
3582 X_ATTRIBUTE12 => c_Visit_rec.ATTRIBUTE12,
3583 X_ATTRIBUTE13 => c_Visit_rec.ATTRIBUTE13,
3584 X_ATTRIBUTE14 => c_Visit_rec.ATTRIBUTE14,
3585 X_ATTRIBUTE15 => c_Visit_rec.ATTRIBUTE15,
3586 X_OBJECT_VERSION_NUMBER => l_Visit_rec.OBJECT_VERSION_NUMBER + 1,
3587 X_ORGANIZATION_ID => l_Visit_rec.ORGANIZATION_ID,
3588 X_DEPARTMENT_ID => l_Visit_rec.DEPARTMENT_ID,
3589 X_STATUS_CODE => l_Visit_rec.STATUS_CODE,
3590 X_START_DATE_TIME => l_date_time_start,
3591 X_CLOSE_DATE_TIME => l_date_time_end,
3592 X_PRICE_LIST_ID => c_Visit_rec.PRICE_LIST_ID,
3593 X_ESTIMATED_PRICE => c_Visit_rec.ESTIMATED_PRICE,
3594 X_ACTUAL_PRICE => c_Visit_rec.ACTUAL_PRICE,
3595 X_OUTSIDE_PARTY_FLAG => c_Visit_rec.OUTSIDE_PARTY_FLAG,
3596 X_ANY_TASK_CHG_FLAG => c_Visit_rec.ANY_TASK_CHG_FLAG,
3597 X_VISIT_NAME => l_Visit_rec.VISIT_NAME,
3598 X_DESCRIPTION => l_Visit_rec.DESCRIPTION,
3599 X_PRIORITY_CODE => l_visit_rec.PRIORITY_CODE,
3600 X_PROJECT_TEMPLATE_ID => l_visit_rec.PROJ_TEMPLATE_ID,
3601 X_LAST_UPDATE_DATE => SYSDATE,
3602 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
3603 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
3604 X_UNIT_SCHEDULE_ID => c_Visit_rec.unit_schedule_id,
3605 X_INV_LOCATOR_ID => l_visit_rec.INV_LOCATOR_ID --Added by sowsubra
3606 );
3607
3608 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3609 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Ahl_Visits_Pkg.UPDATE_ROW');
3610 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TIMES_PVT.Calculate_Task_Times');
3611 END IF;
3612
3613 -- Added cxcheng POST11510--------------
3614 AHL_VWP_TIMES_PVT.Calculate_Task_Times
3615 ( p_api_version => 1.0,
3616 p_init_msg_list => Fnd_Api.G_FALSE,
3617 p_commit => Fnd_Api.G_FALSE,
3618 p_validation_level=> Fnd_Api.G_VALID_LEVEL_FULL,
3619 x_return_status => l_return_status,
3620 x_msg_count => l_msg_count,
3621 x_msg_data => l_msg_data,
3622 p_visit_id => l_visit_rec.visit_id);
3623
3624 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3625 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_TIMES_PVT.Calculate_Task_Times - l_return_status : '||l_return_status);
3626 END IF;
3627
3628 IF l_return_status <> 'S' THEN
3629 RAISE Fnd_Api.G_EXC_ERROR;
3630 END IF;
3631
3632 -- To call LTP process to delete or adjust space assignments if space changed flag is set to "Y"
3633 IF space_changed_flag = 'Y' THEN
3634 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3635 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_SPACE_ASSIGN_PVT.Delete_Space_assignment');
3636 END IF;
3637
3638 l_Space_Assignment_Rec.VISIT_ID := l_Visit_rec.VISIT_ID;
3639 AHL_LTP_SPACE_ASSIGN_PVT.Delete_Space_assignment
3640 ( p_api_version => 1.0,
3641 p_init_msg_list => FND_API.g_false,
3642 p_commit => FND_API.g_false,
3643 p_validation_level => FND_API.g_valid_level_full,
3644 p_space_assign_rec => l_Space_Assignment_Rec,
3645 x_return_status => l_return_status,
3646 x_msg_count => x_msg_count,
3647 x_msg_data => x_msg_data
3648 );
3649
3650 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3651 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_SPACE_ASSIGN_PVT.Delete_Space_assignment - l_return_status : '||l_return_status);
3652 END IF;
3653
3654 IF l_return_status <> 'S' THEN
3655 RAISE Fnd_Api.G_EXC_ERROR;
3656 END IF;
3657 END IF;
3658
3659 -- If Visit DEPARTMENT or ORGANIZATION or Start Date is changed after price/cost is estimated,
3660 -- the prices associated to Visit and all the Tasks in the visit will be cleared up
3661 If space_changed_flag = 'Y' THEN
3662 OPEN c_job(l_Visit_rec.visit_id);
3663 FETCH c_job INTO l_count;
3664 CLOSE c_job;
3665
3666 IF l_count <> 0 THEN
3667 -- To update visit's prices
3668 UPDATE AHL_VISITS_B
3669 SET ACTUAL_PRICE = NULL,
3670 ESTIMATED_PRICE = NULL,
3671 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3672 WHERE VISIT_ID = l_Visit_rec.visit_id;
3673
3674 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3675 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After updating actual and estimated price for the visit');
3676 END IF;
3677
3678 -- To update all tasks prices of tasks under this visit
3679 OPEN c_task(l_visit_rec.visit_id);
3680 LOOP
3681 FETCH c_task INTO c_task_rec;
3682 EXIT WHEN c_task%NOTFOUND;
3683 -- Tasks found for visit
3684 -- To set prices to NULL in case if the visit's department is changed
3685 UPDATE AHL_VISIT_TASKS_B
3686 SET ACTUAL_PRICE = NULL,
3687 ESTIMATED_PRICE = NULL,
3688 OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
3689 WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
3690 END LOOP;
3691 CLOSE c_task;
3692
3693 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3694 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After updating actual and estimated price for all the tasks in the visit');
3695 END IF;
3696 END IF;
3697 END IF;
3698
3699 -- To call LTP Process Materials API for APS Integration by Shbhanda 04-Dec-03
3700 -- changed the condition for fixing the issue 144 , in the CMRO Forum
3701 -- if any of start date,organization, or department is not
3702 -- is invoked with operation flag 'D'
3703 -- else if any of them is updated then AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials is invoked with operation flag 'U' as earlier.
3704
3705 IF (( c_visit_rec.organization_id IS NOT NULL AND c_visit_rec.department_id IS NOT NULL AND
3706 c_Visit_rec.START_DATE_TIME IS NOT NULL )
3707 AND ( l_date_time_start IS NULL OR l_date_time_start = Fnd_Api.G_MISS_DATE OR
3708 l_visit_rec.organization_id IS NULL OR l_visit_rec.organization_id = Fnd_Api.G_MISS_NUM OR
3709 l_visit_rec.department_id IS NULL OR l_visit_rec.department_id = Fnd_Api.G_MISS_NUM ))
3710 THEN
3711 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3712 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
3713 END IF;
3714
3715 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
3716 p_api_version => 1.0,
3717 p_init_msg_list => FND_API.g_false,
3718 p_commit => FND_API.g_false,
3719 p_validation_level => FND_API.g_valid_level_full,
3720 p_visit_id => l_visit_rec.Visit_Id,
3721 p_visit_task_id => NULL,
3722 p_org_id => l_visit_rec.organization_id,
3723 p_start_date => l_date_time_start,
3724 p_operation_flag => 'D',
3725 x_planned_order_flag => l_planned_order_flag ,
3726 x_return_status => l_return_status,
3727 x_msg_count => x_msg_count,
3728 x_msg_data => x_msg_data );
3729
3730 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3731 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials - l_return_status : '||l_return_status);
3732 END IF;
3733
3734 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3735 RAISE FND_API.G_EXC_ERROR;
3736 END IF;
3737 ELSIF ( NVL(l_date_time_start,TO_DATE('01-01-1947','DD-MM-YYYY')) <> NVL(c_Visit_rec.START_DATE_TIME,TO_DATE('01-01-1947','DD-MM-YYYY'))
3738 OR NVL(l_visit_rec.organization_id,-777) <> NVL(c_visit_rec.organization_id,-777)
3739 OR NVL(l_visit_rec.department_id,-777) <> NVL(c_visit_rec.department_id,-777))
3740 AND ( l_date_time_start IS NOT NULL AND l_date_time_start <> Fnd_Api.G_MISS_DATE
3741 AND l_visit_rec.organization_id IS NOT NULL AND l_visit_rec.organization_id <> Fnd_Api.G_MISS_NUM
3742 AND l_visit_rec.department_id IS NOT NULL AND l_visit_rec.department_id <> Fnd_Api.G_MISS_NUM )
3743 THEN
3744 /*AHL_DEBUG_PUB.Debug( l_full_name ||': VISIT UPDATED - Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
3745 ELSIF (
3746 ( l_date_time_start <> c_Visit_rec.START_DATE_TIME)
3747 OR (l_visit_rec.organization_id <> c_visit_rec.organization_id)
3748 OR (l_visit_rec.department_id <> c_visit_rec.department_id)
3749 )*/
3750 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3751 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
3752 END IF;
3753
3754 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
3755 p_api_version => 1.0,
3756 p_init_msg_list => FND_API.g_false,
3757 p_commit => FND_API.g_false,
3758 p_validation_level => FND_API.g_valid_level_full,
3759 p_visit_id => l_visit_rec.Visit_Id,
3760 p_visit_task_id => NULL,
3761 p_org_id => l_visit_rec.organization_id,
3762 p_start_date => l_date_time_start,
3763 p_operation_flag => 'U',
3764 x_planned_order_flag => l_planned_order_flag ,
3765 x_return_status => l_return_status,
3766 x_msg_count => x_msg_count,
3767 x_msg_data => x_msg_data );
3768
3769 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3770 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials - l_return_status : '||l_return_status);
3771 END IF;
3772
3773 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3774 RAISE FND_API.G_EXC_ERROR;
3775 END IF;
3776 END IF;
3777
3778 -- Set any task changed flag to "Y" if start/end date changed
3779 IF NVL(TO_CHAR(l_date_time_start,'DD-MM-YYYY HH24:MI'),'XXX') <> NVL(TO_CHAR(c_Visit_rec.START_DATE_TIME,'DD-MM-YYYY HH24:MI'),'XXX')
3780 OR NVL(TO_CHAR(l_date_time_end,'DD-MM-YYYY HH24:MI'),'XXX') <> NVL(TO_CHAR(c_Visit_rec.CLOSE_DATE_TIME,'DD-MM-YYYY HH24:MI'),'XXX') THEN
3781 AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
3782 (
3783 p_visit_id => l_Visit_rec.visit_id,
3784 p_flag => 'Y',
3785 x_return_status => l_return_status);
3786
3787 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3788 RAISE FND_API.G_EXC_ERROR;
3789 END IF;
3790 END IF;
3791
3792 l_msg_count := Fnd_Msg_Pub.count_msg;
3793 IF l_msg_count > 0 THEN
3794 X_msg_count := l_msg_count;
3795 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3796 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3797 END IF;
3798
3799 --Standard check for commit
3800 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3801 COMMIT;
3802 END IF;
3803
3804 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3805 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
3806 END IF;
3807 EXCEPTION
3808 WHEN Fnd_Api.g_exc_error THEN
3809 ROLLBACK TO Update_Visit;
3810 x_return_status := Fnd_Api.g_ret_sts_error;
3811 Fnd_Msg_Pub.count_and_get (
3812 p_encoded => Fnd_Api.g_false,
3813 p_count => x_msg_count,
3814 p_data => x_msg_data
3815 );
3816 WHEN Fnd_Api.g_exc_unexpected_error THEN
3817 ROLLBACK TO Update_Visit;
3818 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
3819 Fnd_Msg_Pub.count_and_get (
3820 p_encoded => Fnd_Api.g_false,
3821 p_count => x_msg_count,
3822 p_data => x_msg_data
3823 );
3824 WHEN OTHERS THEN
3825 ROLLBACK TO Update_Visit;
3826 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
3827 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
3828 THEN
3829 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
3830 END IF;
3831 Fnd_Msg_Pub.count_and_get (
3832 p_encoded => Fnd_Api.g_false,
3833 p_count => x_msg_count,
3834 p_data => x_msg_data
3835 );
3836 END Update_Visit;
3837 /*--------------------------------------------------------------------
3838 -- PROCEDURE
3839 -- Delete Task
3840 -- Internal procedure for deleting links for the task to be deleted.
3841
3842 --------------------------------------------------------------------
3843
3844 PROCEDURE Delete_Task (
3845 p_api_version IN NUMBER,
3846 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
3847 p_commit IN VARCHAR2 := Fnd_Api.g_false,
3848 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
3849 p_module_type IN VARCHAR2 := Null,
3850 p_Visit_Task_Id IN NUMBER,
3851 x_return_status OUT NOCOPY VARCHAR2,
3852 x_msg_count OUT NOCOPY NUMBER,
3853 x_msg_data OUT NOCOPY VARCHAR2
3854 )
3855 IS
3856 l_api_version CONSTANT NUMBER := 1.0;
3857 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Task';
3858 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
3859 l_task_id NUMBER;
3860 l_count NUMBER;
3861
3862 BEGIN
3863 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3864 fnd_log.string
3865 (
3866 fnd_log.level_procedure,
3867 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||'.begin',
3868 'At the start of Delete_Visit -> Delete Task'
3869 );
3870 END IF;
3871
3872 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3873 fnd_log.string
3874 (
3875 fnd_log.level_statement,
3876 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
3877 'p_visit_task_id : '||p_Visit_Task_Id
3878 );
3879
3880 END IF;
3881
3882 -- Remove the originating task association of the deleted task
3883 UPDATE
3884 AHL_VISIT_TASKS_B
3885 SET
3886 ORIGINATING_TASK_ID=NULL,
3887 OBJECT_VERSION_NUMBER = object_version_number + 1
3888 WHERE
3889 ORIGINATING_TASK_ID = p_Visit_Task_Id and NVL(STATUS_CODE,'X') <> 'DELETED';
3890
3891
3892 -- Remove Cost parent associations for this task
3893 UPDATE
3894 AHL_VISIT_TASKS_B
3895 SET
3896 COST_PARENT_ID = NULL,
3897 OBJECT_VERSION_NUMBER = object_version_number + 1
3898 WHERE
3899 COST_PARENT_ID = p_Visit_Task_Id AND NVL(STATUS_CODE,'X') <> 'DELETED';
3900
3901
3902 -- Remove Primary Task Associations in simulation visit for the deleted Task
3903 UPDATE
3904 AHL_VISIT_TASKS_B
3905 SET
3906 PRIMARY_VISIT_TASK_ID = NULL,
3907 OBJECT_VERSION_NUMBER = object_version_number + 1
3908 WHERE
3909 PRIMARY_VISIT_TASK_ID = p_Visit_Task_Id AND NVL(STATUS_CODE,'X') <> 'DELETED';
3910
3911
3912 -- Remove task links of the deleted task
3913 DELETE
3914 AHL_TASK_LINKS
3915 WHERE
3916 VISIT_TASK_ID = p_Visit_Task_Id
3917 OR
3918 PARENT_TASK_ID = p_Visit_Task_Id;
3919
3920 AHL_VWP_PROJ_PROD_PVT.Delete_Task_To_project(
3921 p_api_version => p_api_version,
3922 p_init_msg_list => p_init_msg_list,
3923 p_commit => Fnd_Api.g_false,
3924 p_validation_level => p_validation_level,
3925 p_module_type => p_module_type,
3926 p_visit_task_id => p_Visit_Task_Id,
3927 x_return_status => x_return_status,
3928 x_msg_count => x_msg_count,
3929 x_msg_data => x_msg_data);
3930
3931 IF NVL(x_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS
3932 THEN
3933 -- Method call was not successful, raise error
3934 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRJ_TASK_FAILED');
3935 Fnd_Msg_Pub.ADD;
3936 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3937 fnd_log.string
3938 (
3939 fnd_log.level_error,
3940 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
3941 'Failed to delete project task association for task '||p_visit_task_id
3942 );
3943 END IF;
3944 RAISE Fnd_Api.G_EXC_ERROR;
3945 END IF;
3946
3947 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3948 fnd_log.string
3949 (
3950 fnd_log.level_procedure,
3951 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME||'.end',
3952 'At the end of Delete_Visit -> Delete Task'
3953 );
3954 END IF;
3955
3956
3957 END Delete_Task;*/
3958 --------------------------------------------------------------------
3959 -- PROCEDURE
3960 -- Delete_Visit
3961 --
3962 -- PURPOSE
3963 -- To delete a Maintainanace Visit.
3964 --------------------------------------------------------------------
3965
3966 PROCEDURE Delete_Visit (
3967 p_api_version IN NUMBER,
3968 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
3969 p_commit IN VARCHAR2 := Fnd_Api.g_false,
3970 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
3971 p_visit_id IN NUMBER,
3972 x_return_status OUT NOCOPY VARCHAR2,
3973 x_msg_count OUT NOCOPY NUMBER,
3974 x_msg_data OUT NOCOPY VARCHAR2
3975 )
3976 IS
3977 -- Define local Variables
3978 l_api_version CONSTANT NUMBER := 1.0;
3979 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Visit';
3980 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
3981 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
3982
3983 l_msg_data VARCHAR2(2000);
3984 l_return_status VARCHAR2(1);
3985 l_msg_count NUMBER;
3986
3987 l_default VARCHAR2(30);
3988 l_visit_id NUMBER;
3989 l_commit VARCHAR2(1) := 'F';
3990 l_init_msg_list VARCHAR2(1) := 'F';
3991
3992 l_soft_delete_flag VARCHAR2(1) := 'N';
3993 l_planned_order_flag VARCHAR2(1);
3994
3995 -- Define local Cursors
3996 -- To find visit related information
3997 CURSOR c_Visit(x_id IN NUMBER) IS
3998 SELECT * FROM Ahl_Visits_VL
3999 WHERE VISIT_ID = x_id;
4000
4001 l_visit_rec c_Visit%ROWTYPE;
4002
4003 -- To find whether the visit has any materials
4004 CURSOR c_Material(x_visit_id IN NUMBER) IS
4005 /* SELECT 'X' FROM DUAL
4006 WHERE exists (select 'X' from AHL_SCHEDULE_MATERIALS_V where VISIT_ID = x_visit_id);*/
4007
4008 -- AnRaj: Changed for fixing the perf bug 4919502
4009 SELECT 'x' FROM ahl_schedule_materials
4010 WHERE status <> 'DELETED'
4011 AND visit_id = x_visit_id;
4012
4013 c_Material_rec c_Material%ROWTYPE;
4014
4015 -- To find Master Workorder associated with the given Visit in production
4016 CURSOR c_workorder_csr(x_visit_id IN NUMBER) IS
4017 SELECT workorder_id, object_version_number, status_code
4018 FROM AHL_WORKORDERS
4019 WHERE VISIT_ID = x_visit_id
4020 AND MASTER_WORKORDER_FLAG = 'Y'
4021 AND VISIT_TASK_ID IS NULL;
4022
4023 l_workorder_rec c_workorder_csr%ROWTYPE;
4024
4025 -- To find active Master Workorder associated with the given Visit in production
4026 CURSOR c_active_workorder(x_visit_id IN NUMBER) IS
4027 SELECT workorder_id, object_version_number, status_code
4028 FROM AHL_WORKORDERS
4029 WHERE VISIT_ID = x_visit_id
4030 AND MASTER_WORKORDER_FLAG = 'Y'
4031 AND VISIT_TASK_ID IS NULL
4032 AND STATUS_CODE not in ('22','7'); -- deleted, cancelled
4033
4034 l_active_workorder_rec c_active_workorder%ROWTYPE;
4035
4036 -- cursor for finding all information about the tasks
4037 CURSOR c_Tasks_csr(x_id IN NUMBER) IS
4038 -- Merge process for 11.5.10 Bug fix
4039 SELECT visit_task_id,object_version_number,visit_task_number
4040 FROM Ahl_Visit_Tasks_VL
4041 WHERE VISIT_ID = x_id AND NVL(STATUS_CODE,'X') <> 'DELETED'
4042 AND ((TASK_TYPE_CODE = 'SUMMARY' AND ORIGINATING_TASK_ID IS NULL)
4043 OR TASK_TYPE_CODE = 'UNASSOCIATED'
4044 OR (TASK_TYPE_CODE = 'SUMMARY' AND MR_ID IS NULL));
4045
4046 l_tasks_rec c_tasks_csr%ROWTYPE;
4047
4048 -- Local record of Workorder used while calling update job method.
4049 l_prd_workorder_rec AHL_PRD_WORKORDER_PVT.PRD_WORKORDER_REC;
4050 l_workorder_tbl AHL_PRD_WORKORDER_PVT.PRD_WORKOPER_TBL;
4051
4052 BEGIN
4053 --------------------- Initialize -----------------------
4054 SAVEPOINT Delete_Visit;
4055
4056 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4057 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
4058 END IF;
4059
4060 -- Initialize message list if p_init_msg_list is set to TRUE.
4061 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
4062 Fnd_Msg_Pub.initialize;
4063 END IF;
4064
4065 --Initialize API return status to success
4066 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4067
4068 -- Standard call to check for call compatibility.
4069 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4070 p_api_version,
4071 l_api_name,G_PKG_NAME) THEN
4072 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4073 END IF;
4074 ------------------------Start of API Body------------------------------------
4075 ------------------------ Delete ------------------------
4076 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4077 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit ID - ' ||p_visit_id );
4078 END IF;
4079
4080 OPEN c_Visit(p_visit_id);
4081 FETCH c_Visit INTO l_Visit_rec;
4082 IF c_Visit%NOTFOUND THEN
4083 CLOSE c_Visit;
4084 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
4085 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
4086 Fnd_Msg_Pub.ADD;
4087 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4088 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Record not found for the visit with ID'||p_visit_id);
4089 END IF;
4090 END IF;
4091 RAISE Fnd_Api.g_exc_error;
4092 END IF;
4093 CLOSE c_Visit;
4094
4095 -- Check the status of visit, if 'planning' then only delete
4096 IF UPPER(l_visit_rec.status_code) <> 'PLANNING' THEN
4097 Fnd_Message.SET_NAME('AHL','AHL_VWP_STATUS_NOT_DELETE');
4098 Fnd_Msg_Pub.ADD;
4099 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4100 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit is in invalid status for deletion. Visit ID: '||p_visit_id);
4101 END IF;
4102 RAISE Fnd_Api.G_EXC_ERROR;
4103 END IF;
4104
4105 -- To Check if any materials are schedueled for the visit
4106 OPEN c_Material(p_visit_id);
4107 FETCH c_Material INTO c_Material_rec;
4108 IF c_Material%FOUND THEN
4109 l_soft_delete_flag := 'Y';
4110
4111 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4112 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
4113 END IF;
4114
4115 -- Removing planned materials for the visit
4116 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
4117 (p_api_version => p_api_version,
4118 p_init_msg_list => Fnd_Api.G_FALSE,
4119 p_commit => Fnd_Api.G_FALSE,
4120 p_visit_id => p_visit_id,
4121 p_visit_task_id => NULL,
4122 p_org_id => NULL,
4123 p_start_date => NULL,
4124 p_operation_flag => 'R',
4125 x_planned_order_flag => l_planned_order_flag ,
4126 x_return_status => l_return_status,
4127 x_msg_count => x_msg_count,
4128 x_msg_data => x_msg_data);
4129
4130 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4131 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials - l_return_status : '||l_return_status);
4132 END IF;
4133
4134 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4135 CLOSE c_Material;
4136 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4137 END IF;
4138 END IF;
4139 CLOSE c_Material;
4140
4141 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4142 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits');
4143 END IF;
4144 -- Delete all associated simulated visits if the visit to be deleted is a primary visit
4145 AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits
4146 (p_api_version => l_api_version,
4147 p_init_msg_list => l_init_msg_list,
4148 p_commit => l_commit,
4149 p_validation_level => p_validation_level,
4150 p_visit_id => p_visit_id,
4151 x_return_status => l_return_status,
4152 x_msg_count => l_msg_count,
4153 x_msg_data => l_msg_data);
4154
4155 IF(fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4156 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits - l_return_status : '||l_return_status);
4157 END IF;
4158
4159 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4160 -- Check Error Message stack.
4161 x_msg_count := FND_MSG_PUB.count_msg;
4162 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4163 fnd_log.string (fnd_log.level_statement,L_DEBUG,'Errors from delete_simul_visits: ' || x_msg_count );
4164 END IF;
4165 RAISE Fnd_Api.g_exc_error;
4166 END IF;
4167
4168 -- Check for to delete the visit's tasks
4169 OPEN c_Tasks_csr(p_visit_id);
4170 LOOP
4171 FETCH c_Tasks_csr INTO l_tasks_rec;
4172 EXIT WHEN c_Tasks_csr%NOTFOUND;
4173
4174 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4175 fnd_log.string (fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TASKS_PVT.Delete_Task - visit_task_id : '||l_tasks_rec.visit_task_id);
4176 END IF;
4177
4178 l_soft_delete_flag := 'Y'; -- If tasks are set only soft delete needs to be done.
4179
4180 -- Call Delete_Task to remove all the task associations for the deleted task
4181 AHL_VWP_TASKS_PVT.Delete_Task
4182 ( p_api_version => p_api_version,
4183 p_init_msg_list => l_init_msg_list,
4184 p_commit => l_commit,
4185 p_validation_level => p_validation_level,
4186 p_module_type => NULL,
4187 p_visit_task_id => l_tasks_rec.visit_task_id,
4188 x_return_status => l_return_status,
4189 x_msg_count => x_msg_count,
4190 x_msg_data => x_msg_data);
4191
4192 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4193 fnd_log.string (fnd_log.level_statement,L_DEBUG,'After calling Delete_Task - l_return_status : '||l_return_status);
4194 END IF;
4195
4196 IF NVL(l_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
4197 CLOSE c_Tasks_csr;
4198 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_DEL_FAILED');
4199 Fnd_Message.SET_TOKEN('TASK_NAME',l_tasks_rec.visit_task_number);
4200 Fnd_Msg_Pub.ADD;
4201 RAISE Fnd_Api.G_EXC_ERROR;
4202 END IF;
4203 END LOOP;
4204 CLOSE c_Tasks_csr;
4205
4206 -- 11.5.10 Change starts here
4207 -- Check if the Visit to be deleted has Master Workorder in production
4208 OPEN c_workorder_csr(p_visit_id);
4209 FETCH c_workorder_csr INTO l_workorder_rec;
4210 -- Master workorder not found
4211 IF c_workorder_csr%FOUND THEN
4212 l_soft_delete_flag := 'Y';
4213 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4214 fnd_log.string (fnd_log.level_statement,L_DEBUG,'Visit Has workorder associated with status code '||l_workorder_rec.STATUS_CODE);
4215 END IF;
4216
4217 OPEN c_active_workorder(p_visit_id);
4218 FETCH c_active_workorder INTO l_active_workorder_rec;
4219 -- Found Active Master workorder, then cancel all visit jobs.
4220 IF c_active_workorder%FOUND THEN
4221 -- delete visit master workorder
4222 l_prd_workorder_rec.workorder_id := l_active_workorder_rec.workorder_id;
4223 l_prd_workorder_rec.object_version_number := l_active_workorder_rec.object_version_number;
4224 l_prd_workorder_rec.STATUS_CODE:='22'; --Deleted Status Refer DLD to Verify.
4225
4226 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4227 fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_PRD_WORKORDER_PVT.update_job - Workorder Id : '||l_prd_workorder_rec.workorder_id);
4228 END IF;
4229
4230 AHL_PRD_WORKORDER_PVT.update_job
4231 ( p_api_version =>1.0,
4232 p_init_msg_list =>fnd_api.g_false,
4233 p_commit =>fnd_api.g_false,
4234 p_validation_level =>p_validation_level,
4235 p_default =>fnd_api.g_false,
4236 p_module_type =>NULL,
4237 x_return_status =>l_return_status,
4238 x_msg_count =>x_msg_count,
4239 x_msg_data =>x_msg_data,
4240 p_wip_load_flag =>'Y',
4241 p_x_prd_workorder_rec =>l_prd_workorder_rec,
4242 p_x_prd_workoper_tbl =>l_workorder_tbl
4243 );
4244
4245 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4246 fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_PRD_WORKORDER_PVT.update_job - l_return_status : '||l_return_status);
4247 END IF;
4248
4249 IF NVL(l_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
4250 CLOSE c_active_workorder;
4251 CLOSE c_workorder_csr;
4252 RAISE Fnd_Api.G_EXC_ERROR;
4253 END IF;
4254 END IF;
4255 CLOSE c_active_workorder;
4256 END IF;
4257 CLOSE c_workorder_csr;
4258
4259 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4260 fnd_log.string (fnd_log.level_statement,L_DEBUG,'PROJECT_ID : '||l_visit_rec.PROJECT_ID);
4261 END IF;
4262
4263 -- Delete or cancel project and project tasks
4264 IF l_visit_rec.PROJECT_ID IS NOT NULL THEN
4265 IF l_soft_delete_flag = 'Y' THEN
4266 -- Update the project status to 'Rejected'
4267 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4268 fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_VWP_PROJ_PROD_PVT.Update_project');
4269 END IF;
4270
4271 AHL_VWP_PROJ_PROD_PVT.Update_project
4272 ( p_api_version => p_api_version,
4273 p_init_msg_list => l_init_msg_list,
4274 p_commit => l_commit,
4275 p_validation_level => p_validation_level,
4276 p_module_type => 'DEL',
4277 p_visit_id => p_visit_id,
4278 x_return_status => l_return_status,
4279 x_msg_count => x_msg_count,
4280 x_msg_data => x_msg_data
4281 );
4282
4283 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4284 fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_VWP_PROJ_PROD_PVT.Update_project - l_return_status : '||l_return_status);
4285 END IF;
4286
4287 IF NVL(l_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
4288 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRJ_UPDATE_FAILED'); -- Failed to update job
4289 Fnd_Msg_Pub.ADD;
4290 RAISE Fnd_Api.G_EXC_ERROR;
4291 END IF;
4292 ELSE
4293 -- When a visit is hard deleted than the related projects is also deleted
4294 -- Call Delete_Project local procedure to delete project and its tasks
4295 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4296 fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_VWP_PROJ_PROD_PVT.Delete_Project ');
4297 END IF;
4298
4299 AHL_VWP_PROJ_PROD_PVT.Delete_Project
4300 ( p_api_version => p_api_version,
4301 p_init_msg_list => l_init_msg_list,
4302 p_commit => l_commit,
4303 p_validation_level => p_validation_level,
4304 p_module_type => NULL,
4305 p_visit_id => p_visit_id,
4306 x_return_status => l_return_status,
4307 x_msg_count => x_msg_count,
4308 x_msg_data => x_msg_data);
4309
4310 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4311 fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_VWP_PROJ_PROD_PVT.Delete_Project - l_return_status : '||l_return_status);
4312 END IF;
4313
4314 IF NVL(l_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
4315 -- Method call was not successful, raise error
4316 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRJ_DEL_FAILED');
4317 Fnd_Msg_Pub.ADD;
4318 RAISE Fnd_Api.G_EXC_ERROR;
4319 END IF;
4320 END IF; -- soft delete flag
4321 END IF; -- project id not null
4322
4323 -- delete stages in the case of physical delete
4324 IF l_soft_delete_flag = 'N' THEN
4325 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4326 fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_VWP_VISITS_STAGES_PVT.DELETE_STAGES ');
4327 END IF;
4328
4329 AHL_VWP_VISITS_STAGES_PVT.DELETE_STAGES
4330 ( p_api_version => p_api_version,
4331 p_init_msg_list => l_init_msg_list,
4332 p_commit => l_commit,
4333 p_validation_level => p_validation_level,
4334 p_module_type => NULL,
4335 p_visit_id => l_visit_rec.visit_id,
4336 x_return_status => l_return_status,
4337 x_msg_count => x_msg_count,
4338 x_msg_data => x_msg_data
4339 );
4340
4341 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4342 fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_VWP_VISITS_STAGES_PVT.DELETE_STAGES - l_return_status : '||l_return_status );
4343 END IF;
4344
4345 IF NVL(l_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
4346 RAISE Fnd_Api.G_EXC_ERROR;
4347 END IF;
4348
4349 DELETE FROM ahl_visit_tasks_b
4350 WHERE visit_id = p_visit_id;
4351
4352 --Delete the visit
4353 AHL_VISITS_PKG.Delete_Row( x_visit_id => p_visit_id);
4354 ELSE
4355 -- Soft Delete
4356 UPDATE AHL_VISITS_B
4357 SET STATUS_CODE = 'DELETED',
4358 SIMULATION_PLAN_ID = NULL,
4359 OBJECT_VERSION_NUMBER =OBJECT_VERSION_NUMBER + 1
4360 WHERE VISIT_ID = l_visit_rec.visit_id;
4361 END IF; -- soft delete flag
4362 --------------------------End of API Body---------------------------------------
4363 --Standard check to count messages
4364 l_msg_count := Fnd_Msg_Pub.count_msg;
4365
4366 IF l_msg_count > 0 THEN
4367 X_msg_count := l_msg_count;
4368 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4369 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4370 END IF;
4371
4372 --Standard check for commit
4373 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4374 COMMIT;
4375 END IF;
4376
4377 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4378 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
4379 END IF;
4380 EXCEPTION
4381 WHEN Fnd_Api.g_exc_error THEN
4382 ROLLBACK TO Delete_Visit;
4383 x_return_status := Fnd_Api.g_ret_sts_error;
4384 Fnd_Msg_Pub.count_and_get (
4385 p_encoded => Fnd_Api.g_false,
4386 p_count => x_msg_count,
4387 p_data => x_msg_data
4388 );
4389 WHEN Fnd_Api.g_exc_unexpected_error THEN
4390 ROLLBACK TO Delete_Visit;
4391 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4392 Fnd_Msg_Pub.count_and_get (
4393 p_encoded => Fnd_Api.g_false,
4394 p_count => x_msg_count,
4395 p_data => x_msg_data
4396 );
4397 WHEN OTHERS THEN
4398 ROLLBACK TO Delete_Visit;
4399 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4400 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
4401 THEN
4402 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
4403 END IF;
4404 Fnd_Msg_Pub.count_and_get (
4405 p_encoded => Fnd_Api.g_false,
4406 p_count => x_msg_count,
4407 p_data => x_msg_data
4408 );
4409 END Delete_Visit;
4410 --------------------------------------------------------------------
4411 -- PROCEDURE
4412 -- Validate_Visit
4413 --
4414 --------------------------------------------------------------------
4415 PROCEDURE Validate_Visit (
4416 p_api_version IN NUMBER,
4417 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4418 p_commit IN VARCHAR2 := Fnd_Api.g_false,
4419 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4420 p_Visit_rec IN Visit_Rec_Type,
4421 x_return_status OUT NOCOPY VARCHAR2,
4422 x_msg_count OUT NOCOPY NUMBER,
4423 x_msg_data OUT NOCOPY VARCHAR2
4424 )
4425 IS
4426 -- Define local Variables
4427 L_API_VERSION CONSTANT NUMBER := 1.0;
4428 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Visit';
4429 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
4430 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
4431
4432 l_return_status VARCHAR2(1);
4433 BEGIN
4434 --------------------- Initialize -----------------------
4435 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4436 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
4437 END IF;
4438
4439 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
4440 Fnd_Msg_Pub.initialize;
4441 END IF;
4442
4443 IF NOT Fnd_Api.compatible_api_call (
4444 l_api_version,
4445 p_api_version,
4446 l_api_name,
4447 G_PKG_NAME
4448 ) THEN
4449 RAISE Fnd_Api.g_exc_unexpected_error;
4450 END IF;
4451 x_return_status := Fnd_Api.g_ret_sts_success;
4452
4453 ---------------------- validate ------------------------
4454 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4455 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Calling Check_Visit_Items');
4456 END IF;
4457
4458 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
4459 Check_Visit_Items (
4460 p_Visit_rec => p_Visit_rec,
4461 p_validation_mode => Jtf_Plsql_Api.g_create,
4462 x_return_status => l_return_status
4463 );
4464
4465 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4466 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Check_Visit_Items - l_return_status : '||l_return_status);
4467 END IF;
4468
4469 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4470 RAISE Fnd_Api.g_exc_unexpected_error;
4471 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
4472 RAISE Fnd_Api.g_exc_error;
4473 END IF;
4474 END IF;
4475 -------------------- finish --------------------------
4476 Fnd_Msg_Pub.count_and_get (
4477 p_encoded => Fnd_Api.g_false,
4478 p_count => x_msg_count,
4479 p_data => x_msg_data
4480 );
4481
4482 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4483 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
4484 END IF;
4485
4486 EXCEPTION
4487 WHEN Fnd_Api.g_exc_error THEN
4488 x_return_status := Fnd_Api.g_ret_sts_error;
4489 Fnd_Msg_Pub.count_and_get (
4490 p_encoded => Fnd_Api.g_false,
4491 p_count => x_msg_count,
4492 p_data => x_msg_data
4493 );
4494 WHEN Fnd_Api.g_exc_unexpected_error THEN
4495 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4496 Fnd_Msg_Pub.count_and_get (
4497 p_encoded => Fnd_Api.g_false,
4498 p_count => x_msg_count,
4499 p_data => x_msg_data
4500 );
4501 WHEN OTHERS THEN
4502 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4503 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
4504 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
4505 END IF;
4506 Fnd_Msg_Pub.count_and_get (
4507 p_encoded => Fnd_Api.g_false,
4508 p_count => x_msg_count,
4509 p_data => x_msg_data
4510 );
4511 END Validate_Visit;
4512
4513 ---------------------------------------------------------------------
4514 -- PROCEDURE
4515 -- Check_Visit_Items
4516 --
4517 ---------------------------------------------------------------------
4518 PROCEDURE Check_Visit_Items (
4519 p_Visit_rec IN Visit_Rec_Type,
4520 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
4521 x_return_status OUT NOCOPY VARCHAR2
4522 )
4523 IS
4524 BEGIN
4525 -- Validate required items.
4526 Check_Visit_Req_Items (
4527 p_Visit_rec => p_Visit_rec,
4528 x_return_status => x_return_status
4529 );
4530
4531 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
4532 RETURN;
4533 END IF;
4534
4535 -- Validate uniqueness.
4536 Check_Visit_UK_Items (
4537 p_Visit_rec => p_Visit_rec,
4538 p_validation_mode => p_validation_mode,
4539 x_return_status => x_return_status
4540 );
4541
4542 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
4543 RETURN;
4544 END IF;
4545
4546 END Check_Visit_Items;
4547
4548 ---------------------------------------------------------------------
4549 -- PROCEDURE
4550 -- Check_Visit_Req_Items
4551 ---------------------------------------------------------------------
4552 PROCEDURE Check_Visit_Req_Items (
4553 p_Visit_rec IN Visit_Rec_Type,
4554 x_return_status OUT NOCOPY VARCHAR2
4555 )
4556 IS
4557 BEGIN
4558 -- Post 11.5.10 Enhancements
4559 -- Only visit name is mandatory on create/update visit pages
4560 -- VISIT NAME
4561 IF (p_Visit_rec.visit_name IS NULL OR p_Visit_rec.visit_name = Fnd_Api.G_MISS_CHAR) THEN
4562 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
4563 Fnd_Message.set_name ('AHL', 'AHL_VWP_VST_NAME_MISSING');
4564 Fnd_Msg_Pub.ADD;
4565 END IF;
4566 x_return_status := Fnd_Api.g_ret_sts_error;
4567 RETURN;
4568 END IF;
4569
4570 /* -- VISIT TYPE ==== VISIT_TYPE_CODE
4571 IF (p_Visit_rec.visit_type_code IS NULL OR p_Visit_rec.visit_type_code = Fnd_Api.G_MISS_CHAR)THEN
4572 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
4573 Fnd_Message.set_name ('AHL', 'AHL_VWP_TYPE_MISSING');
4574 Fnd_Msg_Pub.ADD;
4575 END IF;
4576 x_return_status := Fnd_Api.g_ret_sts_error;
4577 RETURN;
4578 END IF;
4579 */
4580 /* IF p_Visit_rec.VISIT_ID IS NULL THEN
4581 -- ITEM ==== INVENTORY_ITEM_ID
4582 IF (p_Visit_rec.ITEM_NAME IS NULL OR p_Visit_rec.ITEM_NAME = Fnd_Api.G_MISS_CHAR) THEN
4583 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
4584 Fnd_Message.set_name ('AHL', 'AHL_VWP_ITEM_MISSING');
4585 Fnd_Msg_Pub.ADD;
4586 END IF;
4587 x_return_status := Fnd_Api.g_ret_sts_error;
4588 RETURN;
4589 END IF;
4590 */
4591 /* -- SERIAL NUMBER ==== ITEM_INSTANCE_ID
4592 IF (p_Visit_rec.SERIAL_NUMBER IS NULL OR p_Visit_rec.SERIAL_NUMBER = Fnd_Api.G_MISS_CHAR) THEN
4593
4594 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
4595 Fnd_Message.set_name ('AHL', 'AHL_VWP_SERIAL_MISSING');
4596 Fnd_Msg_Pub.ADD;
4597 END IF;
4598 x_return_status := Fnd_Api.g_ret_sts_error;
4599 RETURN;
4600 END IF;
4601
4602 END IF;
4603 */
4604
4605 END Check_Visit_Req_Items;
4606
4607 ---------------------------------------------------------------------
4608 -- PROCEDURE
4609 -- Check_Visit_UK_Items
4610 ---------------------------------------------------------------------
4611 PROCEDURE Check_Visit_UK_Items (
4612 p_Visit_rec IN Visit_Rec_Type,
4613 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
4614 x_return_status OUT NOCOPY VARCHAR2
4615 )
4616 IS
4617 l_valid_flag VARCHAR2(1);
4618 BEGIN
4619 x_return_status := Fnd_Api.g_ret_sts_success;
4620 --
4621 -- For Create_Visit, when ID is passed in, we need to
4622 -- check if this ID is unique.
4623 IF UPPER(p_Visit_rec.operation_flag) <> 'C' THEN
4624
4625 IF p_validation_mode = Jtf_Plsql_Api.g_create AND p_Visit_rec.VISIT_ID IS NOT NULL
4626 THEN
4627
4628 IF Ahl_Utility_Pvt.check_uniqueness(
4629 'Ahl_Visits_VL',
4630 'VISIT_ID = ' || p_Visit_rec.VISIT_ID
4631 ) = Fnd_Api.g_false THEN
4632
4633 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
4634 Fnd_Message.set_name ('AHL', 'AHL_VWP_DUPLICATE_VISIT_ID');
4635 Fnd_Msg_Pub.ADD;
4636 END IF;
4637 x_return_status := Fnd_Api.g_ret_sts_error;
4638 RETURN;
4639
4640 END IF;
4641 END IF;
4642 END IF;
4643
4644 -- check if VISIT NUMBER is UNIQUE
4645 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
4646 IF (p_Visit_rec.VISIT_NUMBER IS NOT NULL) THEN
4647 l_valid_flag := Ahl_Utility_Pvt.Check_Uniqueness (
4648 'Ahl_Visits_VL',
4649 'VISIT_NUMBER = ''' || p_Visit_rec.VISIT_NUMBER || ''''
4650 );
4651 END IF;
4652 ELSE
4653 IF (p_Visit_rec.VISIT_NUMBER IS NOT NULL) THEN
4654 l_valid_flag := Ahl_Utility_Pvt.Check_Uniqueness (
4655 'Ahl_Visits_VL',
4656 'VISIT_NUMBER = ''' || p_Visit_rec.VISIT_NUMBER ||
4657 ''' AND VISIT_ID <> ' || p_Visit_rec.VISIT_ID
4658 );
4659 END IF;
4660 END IF;
4661
4662 IF l_valid_flag = Fnd_Api.g_false THEN
4663 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
4664 Fnd_Message.set_name ('AHL', 'AHL_VWP_DUPLI_VISIT_NUMBER');
4665 Fnd_Msg_Pub.ADD;
4666 END IF;
4667 x_return_status := Fnd_Api.g_ret_sts_error;
4668 RETURN;
4669 END IF;
4670 END Check_Visit_UK_Items;
4671
4672
4673 --------------------------------------------------------------------
4674 -- PROCEDURE
4675 -- Close_Visit
4676 --
4677 -- PURPOSE
4678 -- To check all validations before changing status of a Visit to Close
4679 --------------------------------------------------------------------
4680 PROCEDURE Close_Visit(
4681 p_api_version IN NUMBER,
4682 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4683 p_commit IN VARCHAR2 := Fnd_Api.g_false,
4684 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4685 p_module_type IN VARCHAR2 := Null,
4686 p_visit_id IN NUMBER,
4687 p_x_cost_session_id IN OUT NOCOPY NUMBER,
4688 p_x_mr_session_id IN OUT NOCOPY NUMBER,
4689 x_return_status OUT NOCOPY VARCHAR2,
4690 x_msg_count OUT NOCOPY NUMBER,
4691 x_msg_data OUT NOCOPY VARCHAR2
4692 )
4693 IS
4694 -- Define local Variables
4695 L_API_VERSION CONSTANT NUMBER := 1.0;
4696 L_API_NAME CONSTANT VARCHAR2(30) := 'Close Visit';
4697 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
4698 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
4699
4700 l_msg_data VARCHAR2(2000);
4701 l_status_name VARCHAR2(80);
4702 l_status_code VARCHAR2(30);
4703 l_chr_date VARCHAR2(30);
4704 l_chk_flag VARCHAR2(1);
4705 l_return_status VARCHAR2(1);
4706
4707 l_min NUMBER;
4708 l_hour NUMBER;
4709 l_msg_count NUMBER;
4710 l_count NUMBER;
4711 i NUMBER;
4712
4713 l_Date DATE;
4714 l_planned_order_flag VARCHAR2(1);
4715 G_EXC_ERROR EXCEPTION;
4716
4717 l_cost_price_rec AHL_VWP_VISIT_CST_PR_PVT.Cost_price_rec_type;
4718 -- Define local Cursors
4719 -- To find visit related information
4720 CURSOR c_visit (x_id IN NUMBER) IS
4721 SELECT * FROM AHL_VISITS_VL
4722 WHERE VISIT_ID = x_id;
4723 c_visit_rec c_visit%ROWTYPE;
4724
4725 -- To find task related information
4726 CURSOR c_task (x_id IN NUMBER) IS
4727 SELECT * FROM AHL_VISIT_TASKS_VL
4728 WHERE VISIT_ID = x_id
4729 AND NVL(STATUS_CODE,'X') <> 'DELETED';
4730 c_task_rec c_task%ROWTYPE;
4731
4732 --yazhou 08Sep2005 starts
4733 -- 1) To find task id which has its corresponding job in shop floor
4734 -- and the job is not in complete, cancelled, closed and deferred status
4735 -- 4--->Complete; 7--->Cancelled; 12 --> Closed; 18 --> Deferred
4736 -- Filter out all Summary tasks, because Master WO will not have statuses updated
4737 --If found, then can not close job.
4738 CURSOR get_open_job_task_csr (p_visit_id IN NUMBER) IS
4739 SELECT A.VISIT_TASK_ID
4740 FROM AHL_VISIT_TASKS_VL A, AHL_WORKORDERS B
4741 WHERE A.VISIT_TASK_ID = B.VISIT_TASK_ID
4742 AND A.VISIT_ID = p_visit_id
4743 AND NVL(A.STATUS_CODE,'X') <> 'DELETED'
4744 -- Balaji added statuses complete_no_charge and deleted to the list of statuses to be
4745 -- checked for.
4746 -- COMPELTE_NO_CHARGE - 5, DELETED - 22
4747 --('4','7','12','18')
4748 AND B.STATUS_CODE NOT IN ('4','5','7','12','18','22')
4749 AND A.TASK_TYPE_CODE <> 'SUMMARY';
4750 --yazhou 08Sep2005 ends
4751
4752 --yazhou 29Sep2005 starts
4753 -- bug fix #4614587
4754 -- 2) Cursor to check that the unit effectivities are updated properly
4755 -- If there are any which is in wrong status, can not close visit
4756 -- PLANNED tasks can not be canncelled. UMP enforcing that rule
4757 CURSOR get_ue_tasks_csr (p_visit_id IN NUMBER) IS
4758 SELECT UE.unit_effectivity_id
4759 FROM ahl_unit_effectivities_b UE, ahl_visit_tasks_b VT
4760 WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
4761 AND nvl(UE.status_code,'x') NOT IN
4762 ('ACCOMPLISHED','DEFERRED','TERMINATED','CANCELLED')
4763 AND (VT.task_type_code = 'UNPLANNED' OR VT.task_type_code = 'PLANNED')
4764 AND VT.visit_id = p_visit_id
4765 AND NVL(VT.STATUS_CODE,'X') <> 'DELETED';
4766 --yazhou 29Sep2005 ends
4767
4768 --Added by Srini
4769 CURSOR c_wip_entity(c_visit_id IN NUMBER) IS
4770 SELECT A.visit_task_id,workorder_id,wip_entity_id,a.object_version_number
4771 FROM ahl_visit_tasks_vl a, ahl_workorders b
4772 WHERE a.visit_task_id = b.visit_task_id
4773 AND a.visit_id = C_VISIT_ID
4774 AND NVL(A.STATUS_CODE,'X') <> 'DELETED'
4775 AND B.STATUS_CODE <> '7';
4776
4777 -- Get summary task without mr
4778 CURSOR c_summ_task (c_visit_id IN NUMBER) IS
4779 SELECT visit_task_id,object_version_number
4780 FROM ahl_visit_tasks_vl
4781 WHERE visit_id = c_visit_id
4782 AND mr_id IS NULL
4783 AND task_type_code = 'SUMMARY'
4784 AND NVL(STATUS_CODE,'X') <> 'DELETED';
4785
4786 l_wip_entity_rec c_wip_entity%ROWTYPE;
4787 l_summ_task_rec c_summ_task%ROWTYPE;
4788 l_actual_cost NUMBER;
4789 l_estimated_cost NUMBER;
4790 l_task_id NUMBER;
4791 l_ue_id NUMBER;
4792
4793 BEGIN
4794 --------------------- initialize -----------------------
4795 SAVEPOINT Close_Visit;
4796
4797 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4798 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure : Visit Id = ' || p_visit_id);
4799 END IF;
4800
4801 -- Initialize message list if p_init_msg_list is set to TRUE.
4802 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
4803 Fnd_Msg_Pub.initialize;
4804 END IF;
4805
4806 -- Initialize API return status to success
4807 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4808
4809 -- Standard call to check for call compatibility.
4810 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4811 p_api_version,
4812 l_api_name,G_PKG_NAME) THEN
4813 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4814 END IF;
4815
4816 ------------------------Start of API Body------------------------------------
4817 OPEN c_visit (p_visit_id);
4818 FETCH c_visit INTO c_visit_rec;
4819 CLOSE c_visit;
4820
4821 -- To check if the unit is quarantined
4822 -- AnRaj added for R 12.0 ACL changes in VWP, Start
4823 CHECK_UNIT_QUARANTINED( p_visit_id => p_visit_id,
4824 item_instance_id => c_visit_rec.Item_Instance_Id);
4825 -- Check Error Message stack.
4826 l_msg_count := FND_MSG_PUB.count_msg;
4827 IF l_msg_count > 0 THEN
4828 x_return_status := FND_API.G_RET_STS_ERROR;
4829 RAISE FND_API.G_EXC_ERROR;
4830 END IF;
4831
4832 -- AnRaj added for R 12.0 ACL changes in VWP, End
4833 IF c_visit_rec.status_code = 'RELEASED' THEN
4834 l_chk_flag := 'Y';
4835 --Step 1) Check if there are any workorders which has not been completed/cancelled
4836 OPEN get_open_job_task_csr(p_visit_id);
4837 FETCH get_open_job_task_csr INTO l_task_id;
4838 IF get_open_job_task_csr%FOUND THEN
4839 l_chk_flag := 'N';
4840 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_JOB_STATUS');
4841 Fnd_Msg_Pub.ADD;
4842 CLOSE get_open_job_task_csr;
4843 RAISE Fnd_Api.g_exc_error;
4844 END IF;
4845 CLOSE get_open_job_task_csr;
4846
4847 --2) Check that the corresponding UE records are in
4848 -- 'ACCOMPLISHED','DEFERRED','TERMINATED','CANCELLED'
4849 OPEN get_ue_tasks_csr(p_visit_id);
4850 FETCH get_ue_tasks_csr INTO l_ue_id;
4851 IF get_ue_tasks_csr%FOUND THEN
4852 l_chk_flag :='N';
4853 Fnd_Message.SET_NAME('AHL','AHL_VWP_UE_CLOSE_INV');
4854 Fnd_Msg_Pub.ADD;
4855 CLOSE get_ue_tasks_csr;
4856 RAISE Fnd_Api.g_exc_error;
4857 END IF;
4858 CLOSE get_ue_tasks_csr;
4859
4860 --Modified by srini Sep 24/2003
4861 l_cost_price_rec.visit_id := c_visit_rec.visit_id;
4862 l_cost_price_rec.cost_session_id := p_x_cost_session_id;
4863 l_cost_price_rec.mr_session_id := p_x_mr_session_id;
4864
4865 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4866 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost visit ID:' || l_cost_price_rec.VISIT_ID);
4867 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost Session ID: ' || l_cost_price_rec.COST_SESSION_ID);
4868 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Mr Session ID:' || l_cost_price_rec.MR_SESSION_ID);
4869 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before Calling AHL_VWP_COST_PVT.Calculate_WO_Cost');
4870 END IF;
4871
4872 --Call ahl_vwp_cost_pvt.calculate_wo_cost
4873 AHL_VWP_COST_PVT.Calculate_WO_Cost(
4874 p_api_version => p_api_version,
4875 p_init_msg_list => p_init_msg_list,
4876 p_commit => Fnd_Api.g_false,
4877 p_validation_level => p_validation_level,
4878 p_x_cost_price_rec => l_cost_price_rec,
4879 x_return_status => l_return_status);
4880
4881 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4882 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After Calling AHL_VWP_COST_PVT.Calculate_WO_Cost : l_return_status - '||l_return_status);
4883 END IF;
4884
4885 -- Check Error Message stack.
4886 l_msg_count := FND_MSG_PUB.count_msg;
4887 IF l_msg_count > 0 THEN
4888 x_return_status := FND_API.G_RET_STS_ERROR;
4889 RAISE FND_API.G_EXC_ERROR;
4890 END IF;
4891
4892 --Assign to out paramenter if null
4893 p_x_cost_session_id := l_cost_price_rec.cost_session_id;
4894 p_x_mr_session_id := l_cost_price_rec.mr_session_id;
4895
4896 OPEN c_wip_entity(c_visit_rec.visit_id);
4897 LOOP
4898 FETCH c_wip_entity INTO l_wip_entity_rec;
4899 EXIT WHEN c_wip_entity%NOTFOUND;
4900
4901 -- Call ahl_vwp_cost_pvt.get_wo_cost
4902 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4903 fnd_log.string(fnd_log.level_statement,L_DEBUG,'wip_entity_id - '||l_wip_entity_rec.wip_entity_id||' - '||'Visit task Id - '||l_wip_entity_rec.visit_task_id);
4904 END IF;
4905
4906 IF l_wip_entity_rec.wip_entity_id IS NOT NULL THEN
4907
4908 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4909 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before Calling AHL_VWP_COST_PVT.Get_WO_Cost for all workorers');
4910 END IF;
4911
4912 AHL_VWP_COST_PVT.Get_WO_Cost(
4913 p_Session_Id => l_cost_price_rec.mr_session_id,
4914 p_Id => l_wip_entity_rec.wip_entity_id,
4915 p_program_id => fnd_global.PROG_APPL_ID,
4916 x_actual_cost => l_actual_cost,
4917 x_estimated_cost => l_estimated_cost,
4918 x_return_status => l_return_status);
4919
4920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4921 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After Calling AHL_VWP_COST_PVT.Get_WO_Cost : l_return_status - '||l_return_status);
4922 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating the actual cost of WO to - '||l_actual_cost);
4923 END IF;
4924
4925 -- Update with actual cost
4926 UPDATE ahl_visit_tasks_b
4927 SET actual_cost = l_actual_cost,
4928 object_version_number = l_wip_entity_rec.object_version_number + 1
4929 WHERE visit_task_id = l_wip_entity_rec.visit_task_id;
4930
4931 END IF;
4932 END LOOP;
4933 CLOSE c_wip_entity;
4934
4935 -- Check Error Message stack.
4936 l_msg_count := FND_MSG_PUB.count_msg;
4937 IF l_msg_count > 0 THEN
4938 x_return_status := FND_API.G_RET_STS_ERROR;
4939 RAISE FND_API.G_EXC_ERROR;
4940 END IF;
4941
4942 --Check for summary tasks without mr
4943 OPEN c_summ_task(c_visit_rec.visit_id);
4944 LOOP
4945 FETCH c_summ_task INTO l_summ_task_rec;
4946 EXIT WHEN c_summ_task%NOTFOUND;
4947 IF l_summ_task_rec.visit_task_id IS NOT NULL THEN
4948
4949 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4950 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before Calling AHL_VWP_COST_PVT.Get_WO_Cost for summary tasks without mr - l_summ_task_rec.visit_task_id : '||l_summ_task_rec.visit_task_id);
4951 END IF;
4952
4953 AHL_VWP_COST_PVT.Get_WO_Cost(
4954 p_Session_Id => l_cost_price_rec.mr_session_id,
4955 p_Id => l_summ_task_rec.visit_task_id,
4956 p_program_id => fnd_global.PROG_APPL_ID,
4957 x_actual_cost => l_actual_cost,
4958 x_estimated_cost => l_estimated_cost,
4959 x_return_status => l_return_status);
4960
4961 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4962 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After Calling AHL_VWP_COST_PVT.Get_WO_Cost : l_return_status - '||l_return_status||' : l_actual_cost - '||l_actual_cost);
4963 END IF;
4964
4965 --Update task record with actual cost
4966 UPDATE ahl_visit_tasks_b
4967 SET actual_cost = l_actual_cost,
4968 object_version_number = l_summ_task_rec.object_version_number + 1
4969 WHERE visit_task_id = l_summ_task_rec.visit_task_id;
4970
4971 END IF;
4972 END LOOP;
4973 CLOSE c_summ_task;
4974
4975 IF l_chk_flag = 'Y' THEN
4976 -- yazhou 28Sept2005 starts
4977 -- bug fix #4626717
4978 /* Call Update_Project procedure to update project status to CLOSED
4979 AHL_VWP_PROJ_PROD_PVT.Update_Project (
4980 p_api_version => p_api_version,
4981 p_init_msg_list => p_init_msg_list,
4982 p_commit => Fnd_Api.g_false,
4983 p_validation_level => p_validation_level,
4984 p_module_type => p_module_type,
4985 p_visit_id => p_visit_id,
4986 x_return_status => x_return_status,
4987 x_msg_count => x_msg_count,
4988 x_msg_data => x_msg_data);*/
4989
4990 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
4991 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before Calling AHL_COMPLETIONS_PVT.complete_master_wo');
4992 END IF;
4993
4994 x_return_status := AHL_COMPLETIONS_PVT.complete_master_wo
4995 (
4996 p_visit_id => p_visit_id,
4997 p_workorder_id => null,
4998 p_ue_id => null
4999 );
5000
5001 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5002 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After Calling AHL_COMPLETIONS_PVT.complete_master_wo');
5003 END IF;
5004
5005 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5006 -- Check Error Message stack.
5007 x_msg_count := FND_MSG_PUB.count_msg;
5008 RAISE Fnd_Api.g_exc_error;
5009 END IF;
5010 -- yazhou 28Sept2005 ends
5011
5012 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5013 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating Visit Status to CLOSED..');
5014 END IF;
5015
5016 -- To update visit to CLOSED status
5017 UPDATE AHL_VISITS_B
5018 SET STATUS_CODE = 'CLOSED',
5019 SIMULATION_PLAN_ID = NULL,
5020 OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1
5021 WHERE VISIT_ID = p_visit_id;
5022
5023 -- To update all tasks to CLOSED status
5024 OPEN c_task(p_visit_id);
5025 LOOP
5026 FETCH c_task INTO c_task_rec;
5027 EXIT WHEN c_task%NOTFOUND;
5028
5029 UPDATE AHL_VISIT_TASKS_B
5030 SET STATUS_CODE = 'CLOSED',
5031 OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
5032 WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
5033 END LOOP;
5034 CLOSE c_task;
5035
5036 -- Call Process_Planned_Materials API for APS Integration
5037 -- Start Code on 17th Feb 2004 by shbhanda
5038 OPEN c_visit (p_visit_id);
5039 FETCH c_visit INTO c_visit_rec;
5040 CLOSE c_visit;
5041
5042 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5043 fnd_log.string(fnd_log.level_statement,L_DEBUG,'VISIT STATUS - ' || c_visit_rec.status_code);
5044 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
5045 END IF;
5046
5047 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
5048 p_api_version => 1.0,
5049 p_init_msg_list => FND_API.g_false,
5050 p_commit => FND_API.g_false,
5051 p_validation_level => FND_API.g_valid_level_full,
5052 p_visit_id => p_visit_id,
5053 p_visit_task_id => NULL,
5054 p_org_id => NULL,
5055 p_start_date => NULL,
5056 p_visit_status => c_visit_rec.status_code,
5057 p_operation_flag => NULL,
5058 x_planned_order_flag => l_planned_order_flag ,
5059 x_return_status => x_return_status,
5060 x_msg_count => x_msg_count,
5061 x_msg_data => x_msg_data );
5062
5063 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5064 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials : x_return_status - '||x_return_status);
5065 END IF;
5066
5067 l_msg_count := FND_MSG_PUB.count_msg;
5068 IF l_msg_count > 0 THEN
5069 X_msg_count := l_msg_count;
5070 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5071 RAISE FND_API.G_EXC_ERROR;
5072 END IF;
5073
5074 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5075 fnd_log.string(fnd_log.level_statement,L_DEBUG,'l_planned_order_flag - '||l_planned_order_flag);
5076 END IF;
5077 -- End Code on 17th Feb 2004 by shbhanda
5078 END IF;
5079 ELSE
5080 Fnd_Message.SET_NAME('AHL','AHL_VWP_STATUS_NOT_RELEASED');
5081 Fnd_Msg_Pub.ADD;
5082 RAISE Fnd_Api.g_exc_error;
5083 END IF;
5084
5085 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5086 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost Session ID: ' || p_x_cost_session_id);
5087 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Mr Session ID:' || p_x_mr_session_id);
5088 END IF;
5089 ---------------------------End of API Body-------------------------------------
5090
5091 -- Standard check of p_commit.
5092 IF Fnd_Api.To_Boolean ( p_commit ) THEN
5093 COMMIT WORK;
5094 END IF;
5095
5096 Fnd_Msg_Pub.count_and_get(
5097 p_encoded => Fnd_Api.g_false,
5098 p_count => x_msg_count,
5099 p_data => x_msg_data);
5100
5101 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5102 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
5103 END IF;
5104 EXCEPTION
5105 WHEN Fnd_Api.g_exc_error THEN
5106 ROLLBACK TO Close_Visit;
5107 x_return_status := Fnd_Api.g_ret_sts_error;
5108 Fnd_Msg_Pub.count_and_get (
5109 p_encoded => Fnd_Api.g_false,
5110 p_count => x_msg_count,
5111 p_data => x_msg_data
5112 );
5113 WHEN Fnd_Api.g_exc_unexpected_error THEN
5114 ROLLBACK TO Close_Visit;
5115 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
5116 Fnd_Msg_Pub.count_and_get (
5117 p_encoded => Fnd_Api.g_false,
5118 p_count => x_msg_count,
5119 p_data => x_msg_data
5120 );
5121 WHEN OTHERS THEN
5122 ROLLBACK TO Close_Visit;
5123 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
5124 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
5125 THEN
5126 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
5127 END IF;
5128 Fnd_Msg_Pub.count_and_get (
5129 p_encoded => Fnd_Api.g_false,
5130 p_count => x_msg_count,
5131 p_data => x_msg_data
5132 );
5133 END Close_Visit;
5134
5135 --------------------------------------------------------------------
5136 -- PROCEDURE
5137 -- Cancel_Visit
5138 --
5139 -- Post 11.5.10 Reema
5140 -- Transit check changes by shbhanda on Jun 25rd 2004
5141 -- Transit Check chagnes by yazhou Aug-06-2004
5142 --
5143 -- PURPOSE
5144 -- To check all validations before changing status of a Visit to Cancel
5145 --------------------------------------------------------------------
5146 PROCEDURE Cancel_Visit(
5147 p_api_version IN NUMBER := 1.0,
5148 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
5149 p_commit IN VARCHAR2 := Fnd_Api.g_false,
5150 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
5151 p_module_type IN VARCHAR2 := Null,
5152 p_visit_id IN NUMBER,
5153 p_obj_ver_num IN NUMBER,
5154 x_return_status OUT NOCOPY VARCHAR2,
5155 x_msg_count OUT NOCOPY NUMBER,
5156 x_msg_data OUT NOCOPY VARCHAR2)
5157 IS
5158 -- Define local Variables
5159 L_API_VERSION CONSTANT NUMBER := 1.0;
5160 L_API_NAME CONSTANT VARCHAR2(30) := 'Cancel_Visit';
5161 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
5162 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
5163
5164 l_chk_project VARCHAR2(1);
5165 l_error_flag VARCHAR2(1) := 'N';
5166
5167 l_return_status VARCHAR2(1);
5168 l_msg_count NUMBER;
5169 l_msg_data VARCHAR2(2000);
5170
5171 l_planned_order_flag VARCHAR2(1);
5172 l_service_request_id NUMBER;
5173 l_interaction_id NUMBER;
5174
5175 l_dummy VARCHAR2(1);
5176
5177 -- Define local Cursors
5178 -- To find visit related information
5179 CURSOR c_visit (x_visit_id IN NUMBER) IS
5180 SELECT * FROM AHL_VISITS_VL
5181 WHERE VISIT_ID = x_visit_id;
5182 c_visit_rec c_visit%ROWTYPE;
5183
5184 -- To find task related information for a visit
5185 CURSOR c_task (x_visit_id IN NUMBER) IS
5186 SELECT * FROM AHL_VISIT_TASKS_VL
5187 WHERE VISIT_ID = x_visit_id
5188 AND NVL(STATUS_CODE,'X') <> 'DELETED';
5189 c_task_rec c_task%ROWTYPE;
5190
5191 -- transit check change
5192 -- yazhou start
5193 -- To find all the SRs associated to a visit
5194 -- Modified by Sjayacha to check if its not null.
5195 CURSOR c_service_request (x_visit_id IN NUMBER) IS
5196 SELECT distinct service_request_id FROM AHL_VISIT_TASKS_B
5197 WHERE VISIT_ID = x_visit_id
5198 AND service_request_id IS NOT NULL
5199 AND NVL(STATUS_CODE,'X') <> 'DELETED';
5200
5201 -- To check if any other active visits have this SR associated
5202 CURSOR c_check_SR (x_visit_id IN NUMBER, x_sr_id IN NUMBER) IS
5203 SELECT 'X' FROM DUAL
5204 WHERE exists ( select a.visit_id
5205 from ahl_visits_b a, ahl_visit_tasks_b b
5206 where a.visit_id <> x_visit_id
5207 and a.visit_id = b.visit_id
5208 and b.visit_id <> x_visit_id
5209 AND NVL(a.STATUS_CODE,'X') not in ('DELETED','CLOSED')
5210 AND b.service_request_id = x_sr_id);
5211
5212 CURSOR c_sr_ovn(x_sr_id IN NUMBER) IS
5213 SELECT object_version_number, incident_number
5214 FROM cs_incidents_all_b
5215 WHERE INCIDENT_ID = x_sr_id;
5216 c_sr_ovn_rec c_sr_ovn%ROWTYPE;
5217 -- yazhou end
5218
5219 BEGIN
5220 --------------------- initialize -----------------------
5221 SAVEPOINT Cancel_Visit;
5222
5223 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5224 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure : Visit Id = ' || p_visit_id);
5225 END IF;
5226
5227 -- Initialize message list if p_init_msg_list is set to TRUE.
5228 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
5229 Fnd_Msg_Pub.initialize;
5230 END IF;
5231
5232 -- Initialize API return status to success
5233 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5234
5235 -- Standard call to check for call compatibility.
5236 IF NOT Fnd_Api.COMPATIBLE_API_CALL( l_api_version,
5237 p_api_version,
5238 l_api_name,G_PKG_NAME) THEN
5239 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5240 END IF;
5241
5242 ---------------------------Start of API Body-------------------------------------
5243 OPEN c_visit(p_visit_id);
5244 FETCH c_visit INTO c_visit_rec;
5245 CLOSE c_visit;
5246
5247 IF c_visit_rec.OBJECT_VERSION_NUMBER <> p_obj_ver_num THEN
5248 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
5249 Fnd_Msg_Pub.ADD;
5250 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5251 END IF;
5252
5253 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5254 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_PRD_WORKORDER_PVT.cancel_visit_jobs');
5255 END IF;
5256 -- Added by shbhanda for Transit check changes on 06/25/2004
5257 -- Code Start
5258 -- Call Cancel_Visit_Jobs API
5259 AHL_PRD_WORKORDER_PVT.cancel_visit_jobs
5260 (p_api_version => 1.0,
5261 p_init_msg_list => FND_API.G_TRUE,
5262 p_commit => FND_API.G_FALSE,
5263 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5264 p_default => FND_API.G_FALSE,
5265 p_module_type => NULL,
5266 x_return_status => l_return_status,
5267 x_msg_count => l_msg_count,
5268 x_msg_data => l_msg_data,
5269 p_visit_id => p_visit_id,
5270 p_unit_effectivity_id => NULL,
5271 p_workorder_id => NULL);
5272
5273 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5274 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_PRD_WORKORDER_PVT.cancel_visit_jobs - l_return_status : '||l_return_status);
5275 END IF;
5276
5277 -- Check return status.
5278 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5279 RAISE FND_API.G_EXC_ERROR;
5280 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5282 END IF;
5283
5284 -- set unit_effectivity to null
5285 UPDATE AHL_VISIT_TASKS_B
5286 SET UNIT_EFFECTIVITY_ID = NULL,
5287 OBJECT_VERSION_NUMBER = object_version_number + 1
5288 WHERE VISIT_ID = p_visit_id
5289 AND UNIT_EFFECTIVITY_ID is not null;
5290
5291 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5292 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
5293 END IF;
5294
5295 -- Call AHL_LTP_REQST_MATRL_PVT.Process_Planned_Material
5296 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
5297 (p_api_version => 1.0,
5298 p_init_msg_list => FND_API.g_false,
5299 p_commit => FND_API.g_false,
5300 p_validation_level => FND_API.g_valid_level_full,
5301 p_visit_id => p_visit_id,
5302 p_visit_task_id => NULL,
5303 p_org_id => NULL,
5304 p_start_date => NULL,
5305 p_visit_status => 'CANCELLED',
5306 p_operation_flag => NULL,
5307 x_planned_order_flag => l_planned_order_flag,
5308 x_return_status => l_return_status,
5309 x_msg_count => l_msg_count,
5310 x_msg_data => l_msg_data);
5311
5312 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5313 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials - l_return_status : '||l_return_status);
5314 END IF;
5315
5316 -- Check return status.
5317 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5318 RAISE FND_API.G_EXC_ERROR;
5319 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5321 END IF;
5322
5323 -- update task status
5324 OPEN c_task(p_visit_id);
5325 LOOP
5326 FETCH c_task INTO c_task_rec;
5327 EXIT WHEN c_task%NOTFOUND;
5328 -- update the task status to cancelled
5329
5330 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5331 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating the task status to CANCELLED for task ID - c_task_rec.visit_task_id : '||c_task_rec.visit_task_id);
5332 END IF;
5333
5334 UPDATE AHL_VISIT_TASKS_B
5335 SET STATUS_CODE = 'CANCELLED',
5336 OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
5337 WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
5338 END LOOP;
5339 CLOSE c_task;
5340
5341 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5342 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Updating the visit status to CANCELLED..');
5343 END IF;
5344 -- update visit status
5345 UPDATE AHL_VISITS_B
5346 SET STATUS_CODE = 'CANCELLED',
5347 OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1
5348 WHERE VISIT_ID = p_visit_id;
5349
5350 -- ****** cancelled check has to be added to production api
5351 -- Check if visit project id is null
5352 -- if not null then update PA_PROJECTS_ALL and set the
5353 -- project status code to 'CLOSED'
5354 IF c_visit_rec.PROJECT_ID IS NOT NULL THEN
5355 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5356 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_PROJ_PROD_PVT.Update_Project');
5357 END IF;
5358
5359 AHL_VWP_PROJ_PROD_PVT.Update_Project(
5360 p_api_version => p_api_version,
5361 p_init_msg_list => p_init_msg_list,
5362 p_commit => Fnd_Api.g_false,
5363 p_validation_level => p_validation_level,
5364 p_module_type => p_module_type,
5365 p_visit_id => p_visit_id,
5366 x_return_status => x_return_status,
5367 x_msg_count => x_msg_count,
5368 x_msg_data => x_msg_data);
5369 --The following END IF was commented out by jeli on 07/27/04, otherwise it couldn't pass the compilation.
5370
5371 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5372 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_PROJ_PROD_PVT.Update_Project - x_return_status : '||x_return_status);
5373 END IF;
5374
5375 IF NVL(x_return_status, 'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
5376 -- Method call was not successful, raise error
5377 Fnd_Message.SET_NAME('AHL','AHL_VWP_PRJ_UPDATE_FAILED'); -- Failed to update job
5378 Fnd_Msg_Pub.ADD;
5379 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
5380 fnd_log.string( fnd_log.level_error,'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
5381 'Cant update the project to Rejected status');
5382 END IF;
5383 RAISE Fnd_Api.G_EXC_ERROR;
5384 END IF;
5385 END IF;
5386
5387 -- transit check change
5388 -- yazhou start
5389 -- Set SR status back to OPEN if no other active visit has reference to it
5390 OPEN c_service_request(p_visit_id);
5391 LOOP
5392 FETCH c_service_request INTO l_service_request_id;
5393 EXIT WHEN c_service_request%NOTFOUND;
5394 -- Check if any other active visits have reference to this SR
5395 OPEN c_check_SR(p_visit_id,l_service_request_id);
5396 FETCH c_check_SR into l_dummy;
5397 IF c_check_SR %NOTFOUND THEN
5398 -- Set SR status back to OPEN
5399 OPEN c_sr_ovn(l_service_request_id);
5400 FETCH c_sr_ovn into c_sr_ovn_rec;
5401 CLOSE c_sr_ovn;
5402
5403 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5404 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling CS_ServiceRequest_PUB.Update_Status - SR Id : '||l_service_request_id);
5405 END IF;
5406 -- yazhou 29-Jun-2006 starts
5407 -- bug#5359943
5408 -- Pass p_status_id as 1 (OPEN)
5409 CS_ServiceRequest_PUB.Update_Status
5410 ( p_api_version => 2.0,
5411 p_init_msg_list => p_init_msg_list,
5412 p_commit => FND_API.G_FALSE,
5413 p_resp_appl_id => NULL,
5414 p_resp_id => NULL,
5415 p_user_id => NULL,
5416 p_login_id => NULL,
5417 p_status_id => 1,
5418 p_closed_date => NULL,
5419 p_audit_comments => NULL,
5420 p_called_by_workflow => FND_API.G_FALSE,
5421 p_workflow_process_id => NULL,
5422 p_comments => NULL,
5423 p_public_comment_flag => FND_API.G_FALSE,
5424 p_validate_sr_closure => 'N',
5425 p_auto_close_child_entities => 'N',
5426 p_request_id => NULL,
5427 p_request_number => c_sr_ovn_rec.incident_number,
5428 x_return_status => l_return_status,
5429 x_msg_count => l_msg_count,
5430 x_msg_data => l_msg_data,
5431 p_object_version_number => c_sr_ovn_rec.object_version_number,
5432 -- p_status => 'OPEN',
5433 x_interaction_id => l_interaction_id
5434 );
5435
5436 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5437 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling CS_ServiceRequest_PUB.Update_Status - l_return_status : '||l_return_status);
5438 END IF;
5439 -- yazhou 29-Jun-2006 ends
5440 -- Check return status.
5441 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5442 CLOSE c_check_SR;
5443 CLOSE c_service_request;
5444 RAISE FND_API.G_EXC_ERROR;
5445 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5446 CLOSE c_check_SR;
5447 CLOSE c_service_request;
5448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5449 END IF;
5450 END IF;
5451 CLOSE c_check_SR;
5452 END LOOP;
5453 CLOSE c_service_request;
5454
5455
5456 ---------------------------End of API Body-------------------------------------
5457 -- Standard check of p_commit.
5458
5459 IF Fnd_Api.To_Boolean ( p_commit ) THEN
5460 COMMIT WORK;
5461 END IF;
5462
5463 Fnd_Msg_Pub.count_and_get(
5464 p_encoded => Fnd_Api.g_false,
5465 p_count => x_msg_count,
5466 p_data => x_msg_data);
5467
5468 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5469 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
5470 END IF;
5471 EXCEPTION
5472 WHEN Fnd_Api.g_exc_error THEN
5473 ROLLBACK TO Cancel_Visit;
5474 x_return_status := Fnd_Api.g_ret_sts_error;
5475 Fnd_Msg_Pub.count_and_get (
5476 p_encoded => Fnd_Api.g_false,
5477 p_count => x_msg_count,
5478 p_data => x_msg_data
5479 );
5480 WHEN Fnd_Api.g_exc_unexpected_error THEN
5481 ROLLBACK TO Cancel_Visit;
5482 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
5483 Fnd_Msg_Pub.count_and_get (
5484 p_encoded => Fnd_Api.g_false,
5485 p_count => x_msg_count,
5486 p_data => x_msg_data
5487 );
5488 WHEN OTHERS THEN
5489 ROLLBACK TO Cancel_Visit;
5490 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
5491 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
5492 THEN
5493 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
5494 END IF;
5495 Fnd_Msg_Pub.count_and_get (
5496 p_encoded => Fnd_Api.g_false,
5497 p_count => x_msg_count,
5498 p_data => x_msg_data
5499 );
5500
5501 END Cancel_Visit;
5502
5503 ----------------------------------------------------------------------
5504 -- END: Defining procedures BODY, which are called from UI screen --
5505 ----------------------------------------------------------------------
5506
5507 -- Transit Visit Change
5508 -- yazhou start
5509
5510 --------------------------------------------------------------------
5511 -- PROCEDURE
5512 -- Synchronize_Visit
5513 --
5514 -- PURPOSE
5515 -- Will be called from UA
5516 -- To Synchronize visit with flight schedule change
5517 --------------------------------------------------------------------
5518 PROCEDURE Synchronize_Visit (
5519 p_api_version IN NUMBER,
5520 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
5521 p_commit IN VARCHAR2 := Fnd_Api.g_false,
5522 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
5523 p_module_type IN VARCHAR2 := NULL,
5524 p_x_visit_rec IN OUT NOCOPY Visit_Rec_Type,
5525 x_return_status OUT NOCOPY VARCHAR2,
5526 x_msg_count OUT NOCOPY NUMBER,
5527 x_msg_data OUT NOCOPY VARCHAR2
5528 )
5529 IS
5530 -- Define local Variables
5531 L_API_VERSION CONSTANT NUMBER := 1.0;
5532 L_API_NAME CONSTANT VARCHAR2(30) := 'Synchronize_Visit';
5533 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
5534 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
5535
5536 l_msg_data VARCHAR2(2000);
5537 l_return_status VARCHAR2(1);
5538 l_msg_count NUMBER;
5539
5540 l_visit_rec Visit_Rec_Type := p_x_visit_rec;
5541
5542 -- Define local Cursors
5543
5544 -- To find visit related information
5545 CURSOR c_Visit(x_visit_id IN NUMBER) IS
5546 SELECT * FROM Ahl_Visits_VL
5547 WHERE VISIT_ID = x_visit_id;
5548
5549 c_Visit_rec c_Visit%ROWTYPE;
5550 l_org_id NUMBER;
5551
5552 BEGIN
5553 --------------------- Initialize -----------------------
5554 SAVEPOINT Synchronize_Visit;
5555
5556 -- Check if API is called in debug mode. If yes, enable debug.
5557 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5558 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure : Visit Id - '||l_visit_rec.visit_id);
5559 END IF;
5560
5561 -- Initialize message list if p_init_msg_list is set to TRUE.
5562 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
5563 Fnd_Msg_Pub.initialize;
5564 END IF;
5565
5566 -- Initialize API return status to success
5567 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5568
5569 -- Standard call to check for call compatibility.
5570 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
5571 p_api_version,
5572 l_api_name,G_PKG_NAME)
5573 THEN
5574 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5575 END IF;
5576
5577 ------------------------Start of API Body------------------------------------
5578 OPEN c_Visit(l_visit_rec.visit_id);
5579 FETCH c_Visit INTO c_Visit_rec;
5580 IF c_Visit%NOTFOUND THEN
5581 CLOSE c_Visit;
5582 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
5583 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
5584 Fnd_Msg_Pub.ADD;
5585 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5586 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit not found for - ' ||l_visit_rec.visit_id );
5587 END IF;
5588 END IF;
5589 RAISE Fnd_Api.g_exc_error;
5590 ELSE
5591 CLOSE c_Visit;
5592 END IF;
5593
5594 -- Complete Visit Record
5595 p_x_visit_rec.VISIT_TYPE_CODE := c_Visit_rec.VISIT_TYPE_CODE;
5596 p_x_visit_rec.SERVICE_REQUEST_ID := c_Visit_rec.SERVICE_REQUEST_ID;
5597 p_x_visit_rec.SPACE_CATEGORY_CODE := c_Visit_rec.SPACE_CATEGORY_CODE;
5598 p_x_visit_rec.OBJECT_VERSION_NUMBER := c_Visit_rec.OBJECT_VERSION_NUMBER;
5599 p_x_visit_rec.VISIT_NAME := c_Visit_rec.VISIT_NAME;
5600 p_x_visit_rec.DESCRIPTION := c_Visit_rec.DESCRIPTION;
5601 p_x_visit_rec.PRIORITY_CODE := c_Visit_rec.PRIORITY_CODE;
5602 p_x_visit_rec.PROJ_TEMPLATE_ID := c_Visit_rec.PROJECT_TEMPLATE_ID;
5603
5604 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5605 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Status : '||c_Visit_rec.status_code);
5606 END IF;
5607
5608 IF c_Visit_rec.status_code = 'PLANNING' THEN
5609 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5610 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Update_Visit');
5611 END IF;
5612
5613 Update_Visit
5614 (
5615 p_api_version => l_api_version,
5616 p_init_msg_list => p_init_msg_list,
5617 p_commit => Fnd_Api.g_false,
5618 p_validation_level => p_validation_level,
5619 p_module_type => p_module_type,
5620 p_x_Visit_rec => p_x_visit_rec,
5621 x_return_status => l_return_status,
5622 x_msg_count => l_msg_count,
5623 x_msg_data => l_msg_data
5624 );
5625
5626 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5627 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Update_Visit - l_return_status : '||l_return_status);
5628 END IF;
5629
5630 IF l_return_status <> 'S' THEN
5631 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5632 END IF;
5633 ELSIF c_Visit_rec.status_code = 'RELEASED' OR c_Visit_rec.status_code = 'PARTIALLY RELEASED' THEN
5634 -------------- R12 changes For Serial Number Reservations Start-------------------
5635 ---------------AnRaj added on 19th June 2005-------------------
5636 -- R12: Department is made mandatory
5637 IF l_visit_rec.department_id IS NULL OR l_visit_rec.department_id = Fnd_Api.G_MISS_NUM THEN
5638 Fnd_Message.SET_NAME('AHL','AHL_VWP_RELSD_DEPT_MAND');
5639 Fnd_Msg_Pub.ADD;
5640 RAISE Fnd_Api.G_EXC_ERROR;
5641 END IF;
5642
5643 -- R12: Department change does not require to cancel current visit and Create a new visit
5644 -- R12: Removed the Department change check here
5645 -- If orgnization changed, then cancel the visit and create a new one.
5646 IF l_visit_rec.organization_id IS NOT NULL
5647 AND l_visit_rec.organization_id <> Fnd_Api.G_MISS_NUM
5648 AND l_visit_rec.organization_id <> c_Visit_rec.organization_id
5649 THEN
5650 -- Validate org/dept/dates
5651 -- Planned Start Date is madatory for transit visit
5652 IF l_visit_rec.START_DATE IS NULL
5653 OR l_visit_rec.START_DATE = FND_API.g_miss_date
5654 THEN
5655 Fnd_Message.SET_NAME('AHL','AHL_VWP_TC_ST_DT_REQ');
5656 Fnd_Msg_Pub.ADD;
5657 RAISE Fnd_Api.G_EXC_ERROR;
5658 END IF;
5659
5660 -- Planned End Date is madatory for transit visit
5661 IF l_visit_rec.PLAN_END_DATE IS NULL
5662 OR l_visit_rec.PLAN_END_DATE = FND_API.g_miss_date
5663 THEN
5664 Fnd_Message.SET_NAME('AHL','AHL_VWP_TC_END_DT_REQ');
5665 Fnd_Msg_Pub.ADD;
5666 RAISE Fnd_Api.G_EXC_ERROR;
5667 END IF;
5668
5669 -- Planned End Date must be greater than or equal to Planned Start Date
5670 IF (l_visit_rec.START_DATE > l_visit_rec.PLAN_END_DATE) THEN
5671 Fnd_Message.SET_NAME('AHL','AHL_VWP_START_DT_GTR_CLOSE_DT');
5672 Fnd_Msg_Pub.ADD;
5673 RAISE Fnd_Api.G_EXC_ERROR;
5674 END IF;
5675
5676 -- If visit start date is earlier than SYSDATE then use sysdate to create new visit
5677 IF (l_visit_rec.START_DATE < SYSDATE ) THEN
5678 l_visit_rec.START_DATE := SYSDATE;
5679 END IF;
5680
5681 -- Organization belongs to user's operating unit
5682 AHL_VWP_RULES_PVT.Check_Org_Name_Or_Id
5683 ( p_organization_id => l_visit_rec.organization_id,
5684 p_org_name => null,
5685 x_organization_id => l_org_id,
5686 x_return_status => l_return_status,
5687 x_error_msg_code => l_msg_data);
5688
5689 l_visit_rec.organization_id := l_org_id;
5690
5691 IF NVL(l_return_status,'x') <> 'S' THEN
5692 Fnd_Message.SET_NAME('AHL','AHL_VWP_ORG_NOT_EXISTS');
5693 Fnd_Msg_Pub.ADD;
5694 RAISE Fnd_Api.G_EXC_ERROR;
5695 END IF;
5696
5697 -- Department must have shift defined
5698 AHL_VWP_RULES_PVT.CHECK_DEPARTMENT_SHIFT
5699 (p_dept_id => l_visit_rec.department_id,
5700 x_return_status => l_return_status);
5701
5702 IF NVL(l_return_status,'x') <> 'S' THEN
5703 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_DEPT_SHIFT');
5704 Fnd_Msg_Pub.ADD;
5705 RAISE Fnd_Api.G_EXC_ERROR;
5706 END IF;
5707
5708 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5709 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling COPY_VISIT');
5710 END IF;
5711
5712 COPY_VISIT(
5713 p_api_version => l_api_version,
5714 p_init_msg_list => p_init_msg_list,
5715 p_commit => Fnd_Api.g_false,
5716 p_validation_level => p_validation_level,
5717 p_module_type => p_module_type,
5718 P_VISIT_ID => c_visit_rec.visit_id,
5719 p_x_Visit_rec => l_visit_rec,
5720 x_return_status => l_return_status,
5721 x_msg_count => l_msg_count,
5722 x_msg_data => l_msg_data
5723 );
5724
5725 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5726 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling COPY_VISIT l_return_status - '||l_return_status);
5727 END IF;
5728
5729 IF l_return_status <> 'S' THEN
5730 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5731 END IF;
5732
5733 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5734 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_PROJ_PROD_PVT.Release_Visit');
5735 END IF;
5736
5737 AHL_VWP_PROJ_PROD_PVT.Release_Visit (
5738 p_api_version => l_api_version,
5739 p_visit_id => l_visit_rec.visit_id,
5740 p_module_type => 'VWP',
5741 p_release_flag => NUll,
5742 p_orig_visit_id => c_visit_rec.visit_id,
5743 X_RETURN_STATUS => l_return_status,
5744 X_MSG_COUNT => l_msg_count,
5745 X_MSG_DATA => l_msg_data
5746 );
5747
5748 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5749 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_PROJ_PROD_PVT.Release_Visit : l_return_status - '||l_return_status);
5750 END IF;
5751
5752 IF l_return_status <> 'S' THEN
5753 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5754 END IF;
5755
5756 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5757 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Cancel_Visit');
5758 END IF;
5759
5760 Cancel_Visit
5761 (
5762 p_Visit_id => c_visit_rec.visit_id,
5763 p_obj_ver_num => c_visit_rec.object_version_number,
5764 x_return_status => l_return_status,
5765 x_msg_count => l_msg_count,
5766 x_msg_data => l_msg_data
5767 );
5768
5769 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5770 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Cancel_Visit : l_return_status - '||l_return_status);
5771 END IF;
5772
5773 IF l_return_status <> 'S' THEN
5774 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5775 END IF;
5776 -- The Organization did not change
5777 -- Only there is change in the date and time of the visit
5778 -- R12 OR the department has changed., added the condition
5779 -- R12 AnRaj
5780 ELSIF l_visit_rec.START_DATE IS NOT NULL
5781 AND l_visit_rec.START_DATE <> FND_API.g_miss_date
5782 AND l_visit_rec.PLAN_END_DATE IS NOT NULL
5783 AND l_visit_rec.PLAN_END_DATE <> FND_API.g_miss_date
5784 AND ( l_visit_rec.START_DATE <> c_Visit_rec.START_DATE_TIME
5785 OR l_visit_rec.PLAN_END_DATE <> c_Visit_rec.CLOSE_DATE_TIME
5786 OR l_visit_rec.department_id <> c_Visit_rec.department_id
5787 )
5788 THEN
5789 ------------ R12 changes For Serial Number Reservations End-----------------
5790
5791 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5792 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Update_Visit');
5793 END IF;
5794
5795 Update_Visit
5796 (
5797 p_api_version => l_api_version,
5798 p_init_msg_list => p_init_msg_list,
5799 p_commit => Fnd_Api.g_false,
5800 p_validation_level => p_validation_level,
5801 p_module_type => p_module_type,
5802 p_x_Visit_rec => p_x_visit_rec,
5803 x_return_status => l_return_status,
5804 x_msg_count => l_msg_count,
5805 x_msg_data => l_msg_data
5806 );
5807
5808 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5809 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Update_Visit - l_return_status : '||l_return_status);
5810 END IF;
5811
5812 IF l_return_status <> 'S' THEN
5813 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5814 END IF;
5815
5816 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5817 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_PRD_WORKORDER_PVT.RESCHEDULE_VISIT_JOBS');
5818 END IF;
5819
5820 AHL_PRD_WORKORDER_PVT.RESCHEDULE_VISIT_JOBS(
5821 P_API_VERSION => l_api_version,
5822 X_RETURN_STATUS => l_return_status,
5823 X_MSG_COUNT => l_msg_count,
5824 X_MSG_DATA => l_msg_data,
5825 P_VISIT_ID => c_visit_rec.visit_id,
5826 p_x_scheduled_start_date => l_visit_rec.START_DATE,
5827 p_x_scheduled_end_date => l_visit_rec.PLAN_END_DATE);
5828
5829 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5830 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_PRD_WORKORDER_PVT.RESCHEDULE_VISIT_JOBS - l_return_status : '||l_return_status);
5831 END IF;
5832
5833 IF l_return_status <> 'S' THEN
5834 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5835 END IF;
5836 END IF;
5837 END IF;
5838
5839 l_msg_count := Fnd_Msg_Pub.count_msg;
5840
5841 IF l_msg_count > 0 THEN
5842 X_msg_count := l_msg_count;
5843 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5844 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5845 END IF;
5846 --Standard check for commit
5847 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
5848 COMMIT;
5849 END IF;
5850
5851 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5852 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
5853 END IF;
5854 EXCEPTION
5855 WHEN Fnd_Api.g_exc_error THEN
5856 ROLLBACK TO Synchronize_Visit;
5857 x_return_status := Fnd_Api.g_ret_sts_error;
5858 Fnd_Msg_Pub.count_and_get (
5859 p_encoded => Fnd_Api.g_false,
5860 p_count => x_msg_count,
5861 p_data => x_msg_data
5862 );
5863 WHEN Fnd_Api.g_exc_unexpected_error THEN
5864 ROLLBACK TO Synchronize_Visit;
5865 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
5866 Fnd_Msg_Pub.count_and_get (
5867 p_encoded => Fnd_Api.g_false,
5868 p_count => x_msg_count,
5869 p_data => x_msg_data
5870 );
5871 WHEN OTHERS THEN
5872 ROLLBACK TO Synchronize_Visit;
5873 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
5874 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
5875 THEN
5876 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
5877 END IF;
5878 Fnd_Msg_Pub.count_and_get (
5879 p_encoded => Fnd_Api.g_false,
5880 p_count => x_msg_count,
5881 p_data => x_msg_data
5882 );
5883 END Synchronize_Visit;
5884
5885 -- yazhou end
5886
5887 ---------------------------------------------------------------------
5888 -- pbarman begin
5889 -- procedure to delete the Unit Schedule Id from Visits records
5890 --when the Flight schedule is deleted.
5891 ---------------------------------------------------------------------
5892 PROCEDURE DELETE_FLIGHT_ASSOC(
5893 p_unit_schedule_id IN NUMBER,
5894 x_return_status OUT NOCOPY VARCHAR2
5895 )
5896 IS
5897 BEGIN
5898 SAVEPOINT delete_flight_assoc;
5899
5900 -- Initialize return status to success initially
5901 x_return_status:=FND_API.G_RET_STS_SUCCESS;
5902
5903 UPDATE AHL_VISITS_B
5904 SET UNIT_SCHEDULE_ID = NULL
5905 WHERE UNIT_SCHEDULE_ID = p_unit_schedule_id ;
5906
5907 EXCEPTION
5908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5909 ROLLBACK TO delete_flight_assoc;
5910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5911 WHEN FND_API.G_EXC_ERROR THEN
5912 ROLLBACK TO delete_flight_assoc;
5913 x_return_status := FND_API.G_RET_STS_ERROR;
5914 WHEN OTHERS THEN
5915 ROLLBACK TO delete_flight_assoc;
5916 x_return_status := FND_API.G_RET_STS_ERROR;
5917 END DELETE_FLIGHT_ASSOC;
5918
5919 -- AnRaj added for R 12.0 ACL changes in VWP
5920 -- Bug number 4297066
5921 -------------------------------------------------------------------
5922 -- Procedure name : check_unit_quarantined
5923 -- Type : Private
5924 -- Function : To check whether the Unit is quarantined
5925 -- Parameters : item_instance_id
5926 ----------------------------------------------------------------------
5927 PROCEDURE check_unit_quarantined(
5928 p_visit_id IN NUMBER,
5929 item_instance_id IN NUMBER
5930 )
5931 IS
5932 l_api_name CONSTANT VARCHAR2(30) := 'check_unit_quarantined';
5933 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
5934 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
5935
5936 l_unit_name VARCHAR2(80);
5937 l_task_number NUMBER(15);
5938 l_instance_id NUMBER;
5939 l_quarantined VARCHAR2(1);
5940
5941 CURSOR c_get_tasknumbers (x_visit_id IN NUMBER) IS
5942 SELECT visit_task_number,instance_id
5943 FROM ahl_visit_tasks_vl
5944 WHERE visit_id = x_visit_id
5945 AND NVL(STATUS_CODE,'X') NOT IN ('DELETED','RELEASED')
5946 AND TASK_TYPE_CODE <> 'SUMMARY';
5947
5948 BEGIN
5949 -- log at the begining of the procedure
5950 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
5951 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure - Visit Id : '||p_visit_id||' - '||'item_instance_id : '||item_instance_id);
5952 END IF;
5953
5954 IF item_instance_id IS NOT NULL THEN
5955 -- If the Visit header has an instance id, check for the corresponding Unit
5956 l_quarantined := ahl_util_uc_pkg.is_unit_quarantined(null,item_instance_id);
5957 IF l_quarantined = FND_API.G_TRUE THEN
5958 l_unit_name := ahl_util_uc_pkg.get_unit_name(item_instance_id);
5959 Fnd_Message.SET_NAME('AHL','AHL_VWP_CLOSE_HDR_UNIT_QRNT');
5960 -- The Unit for this Visit (UNIT_NAME-1) is quarantined.
5961 Fnd_Message.Set_Token('UNIT_NAME',l_unit_name);
5962 Fnd_Msg_Pub.ADD;
5963 -- log message
5964 IF (l_log_statement >= l_log_current_level)THEN
5965 fnd_log.string(fnd_log.level_statement,L_DEBUG,l_unit_name || 'Unit is quarantined, Error message added');
5966 END IF; -- log
5967 END IF; -- l_quarantined not true
5968 ELSE -- instance id is null
5969 -- If the visit does not have a unit at the header , then check for the units of all tasks
5970 OPEN c_get_tasknumbers (p_visit_id);
5971 LOOP
5972 FETCH c_get_tasknumbers INTO l_task_number,l_instance_id;
5973 EXIT WHEN c_get_tasknumbers%NOTFOUND;
5974 l_quarantined := ahl_util_uc_pkg.is_unit_quarantined(null,l_instance_id);
5975 IF l_quarantined = FND_API.G_TRUE THEN
5976 Fnd_Message.SET_NAME('AHL','AHL_VWP_CLOSE_TSK_UNIT_QRNT');
5977 -- The Unit for the Task (UNIT_NAME-1) is quarantined.
5978 Fnd_Message.Set_Token('TASK_NUMBER',l_task_number);
5979 Fnd_Msg_Pub.ADD;
5980 -- log message
5981 IF (l_log_statement >= l_log_current_level)THEN
5982 fnd_log.string(fnd_log.level_statement,L_DEBUG,l_task_number || 'Unit for this task is quarantined.');
5983 END IF; -- log
5984 END IF; -- l_quarantined not true
5985 END LOOP; -- c_get_tasknumbers
5986 END IF;
5987
5988 -- log at the end of the procedure
5989 IF (l_log_procedure >= l_log_current_level) THEN
5990 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PL SQL procedure');
5991 END IF;
5992 END check_unit_quarantined;
5993 END AHL_VWP_VISITS_PVT;