1 PACKAGE BODY AHL_PRD_VISITS_PVT AS
2 /* $Header: AHLVPSVB.pls 120.1.12020000.2 2012/12/07 13:29:35 sareepar ship $*/
3 -----------------------------------------------------------------------
4 -- PACKAGE
5 -- AHL_PRD_VISITS_PVT
6 --
7 -- PURPOSE
8 -- This package body is a Private API for managing PRD Visit procedures
9 -- in Complex Maintainance, Repair and Overhauling(CMRO).
10 -- It defines global constants, various local functions and procedures.
11 --
12 -- PROCEDURES
13 -- Get_Visit_Details
14 -- Get_Unit_Name
15 --
16 -- NOTES
17 --
18 --
19 -- HISTORY
20 -- 30-APR-2004 RROY Created.
21 -----------------------------------------------------------------
22 -- Define Global CONSTANTS --
23 -----------------------------------------------------------------
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_PRD_VISIT_PVT';
25 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
26
27 -- To find out Due_by_Date for the visit update screen.
28 PROCEDURE Get_Due_by_Date(
29 p_visit_id IN NUMBER,
30 x_Due_by_Date OUT NOCOPY DATE
31 );
32
33
34 -------------------------------------------------------------------
35 -- PROCEDURE
36 -- Get_Due_by_Date
37 --
38 -- PURPOSE
39 -- To find out least due by date among all tasks of a visit
40 --------------------------------------------------------------------
41
42
43
44 PROCEDURE Get_Due_by_Date(
45 p_visit_id IN NUMBER,
46 x_due_by_date OUT NOCOPY DATE)
47 IS
48 -- Define local variables
49 l_count1 NUMBER;
50 l_count2 NUMBER;
51 l_date DATE;
52
53 -- Define local Cursors
54 -- To find whether a visit exists
55 CURSOR c_visit (x_id IN NUMBER) IS
56 SELECT COUNT(*)
57 FROM Ahl_Visit_Tasks_B
58 WHERE VISIT_ID = x_id
59 AND NVL(STATUS_CODE,'X') <> 'DELETED';
60
61 -- To find the total number of tasks for a visit
62 CURSOR c_visit_task (x_id IN NUMBER) IS
63 SELECT COUNT(*)
64 FROM Ahl_Visit_Tasks_B
65 WHERE VISIT_ID = x_id
66 AND UNIT_EFFECTIVITY_ID IS NOT NULL
67 AND NVL(STATUS_CODE,'X') <> 'DELETED';
68
69 -- To find due date for a visit related with tasks
70 CURSOR c_due_date (x_id IN NUMBER) IS
71 SELECT MIN(T1.due_date)
72 FROM ahl_unit_effectivities_vl T1, ahl_visit_tasks_b T2
73 WHERE T1.unit_effectivity_id = T2.unit_effectivity_id
74 AND T1.due_date IS NOT NULL AND T2.visit_id = x_id;
75
76 BEGIN
77
78 OPEN c_visit(p_visit_id);
79 FETCH c_visit INTO l_count1;
80 IF c_visit%FOUND THEN --Tasks found for visit
81 CLOSE c_visit;
82
83 OPEN c_visit_task(p_visit_id);
84 FETCH c_visit_task INTO l_count2;
85 IF c_visit_task%FOUND THEN --Tasks found for visit checking for unit_effectivity_id
86 CLOSE c_visit_task;
87 OPEN c_due_date(p_visit_id);
88 FETCH c_due_date INTO x_due_by_date;
89 IF c_due_date%FOUND THEN --Tasks found for visit
90 CLOSE c_due_date;
91 END IF;
92 ELSE
93 CLOSE c_visit_task;
94 END IF;
95
96 ELSE
97 CLOSE c_visit;
98 END IF;
99 RETURN;
100 END Get_Due_by_Date;
101
102 -----------------------------------------------------------------------
103 -- PROCEDURE
104 -- get_unitName
105 --
106 -- PURPOSE
107 -- Function to get unit configuration name for a given item instance.
108 -----------------------------------------------------------------------
109 FUNCTION Get_UnitName (p_csi_item_instance_id IN NUMBER)
110 RETURN VARCHAR2
111 IS
112 -- Define local Cursors
113 -- Get unit name for component.
114 CURSOR get_unit_name_csr (p_csi_item_instance_id IN NUMBER) IS
115 SELECT name
116 FROM ahl_unit_config_headers uc
117 WHERE csi_item_instance_id in ( SELECT object_id
118 FROM csi_ii_relationships
119 START WITH object_id = p_csi_item_instance_id
120 AND relationship_type_code = 'COMPONENT-OF'
121 CONNECT BY PRIOR subject_id = object_id
122 AND relationship_type_code = 'COMPONENT-OF'
123 )
124 and unit_config_status_code = 'COMPLETE'
125 and (active_end_date is null or active_end_date > sysdate);
126
127 -- For top node.
128 CURSOR get_unit_name_csr1 (p_csi_item_instance_id IN NUMBER) IS
129 SELECT name
130 FROM ahl_unit_config_headers uc
131 WHERE csi_item_instance_id = p_csi_item_instance_id
132 and unit_config_status_code = 'COMPLETE' and (active_end_date is null or active_end_date > sysdate);
133
134 l_name ahl_unit_config_headers.name%TYPE;
135
136 BEGIN
137 --Check for top node.
138 OPEN get_unit_name_csr1(p_csi_item_instance_id);
139 FETCH get_unit_name_csr1 INTO l_name;
140 IF (get_unit_name_csr1%NOTFOUND) THEN
141
142 -- Check for component.
143 OPEN get_unit_name_csr(p_csi_item_instance_id);
144 FETCH get_unit_name_csr INTO l_name;
145 IF (get_unit_name_csr%NOTFOUND) THEN
146 l_name := null;
147 END IF;
148 CLOSE get_unit_name_csr;
149
150 END IF;
151 CLOSE get_unit_name_csr1;
152 RETURN l_name;
153 END get_unitName;
154
155
156 --------------------------------------------------------------------
157 -- PROCEDURE
158 -- Get_Visit_Details
159 --
160 -- PURPOSE
161 -- Get a particular Visit Records with all details
162 --------------------------------------------------------------------
163 PROCEDURE Get_Visit_Details (
164 p_api_version IN NUMBER,
165 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
166 p_commit IN VARCHAR2 := Fnd_Api.g_false,
167 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
168 p_module_type IN VARCHAR2 := 'JSP',
169 p_visit_id IN NUMBER,
170
171 x_Visit_rec OUT NOCOPY Visit_Rec_Type,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2
175 )
176 IS
177 -- Define local Variables
178 L_API_VERSION CONSTANT NUMBER := 1.0;
179 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Details';
180 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
181
182 l_msg_data VARCHAR2(2000);
183 l_project_flag VARCHAR2(80);
184 l_simulation_plan_name VARCHAR2(80);
185 l_unit_name VARCHAR2(80);
186 l_hour VARCHAR2(30);
187 l_hour_close VARCHAR2(30);
188 l_default VARCHAR2(30);
189 l_proj_temp_name VARCHAR2(30);
190 l_return_status VARCHAR2(1);
191 l_valid_flag VARCHAR2(1);
192
193 l_visit_id NUMBER:= p_visit_id;
194 l_count NUMBER;
195 l_duration NUMBER;
196 l_visit_end_hour NUMBER;
197 l_proj_temp_id NUMBER;
198 l_workorder_id NUMBER;
199 i NUMBER;
200 x NUMBER;
201
202 l_due_date DATE;
203 x_due_by_date DATE;
204 l_workorder_name VARCHAR2(80); -- Added in 11.5.10
205
206 -- Define local record datatypes
207 l_visit_rec Visit_Rec_Type;
208
209 -- Define local cursors
210 -- To find out required search visit details
211 CURSOR c_visit (x_id IN NUMBER) IS
212 SELECT * FROM AHL_PRD_VISITS_V
213 WHERE VISIT_ID = x_id;
214 c_visit_rec c_visit%ROWTYPE;
215
216 -- To find out all visit/template details
217 CURSOR c_visit_details (x_id IN NUMBER) IS
218 SELECT * FROM AHL_VISITS_VL
219 WHERE VISIT_ID = x_id;
220 visit_rec c_visit_details%ROWTYPE;
221
222 -- Cursor to find master workorder name for the given visit
223 CURSOR c_workorder_csr (x_id IN NUMBER) IS
224 SELECT WORKORDER_NAME, WORKORDER_ID FROM AHL_WORKORDERS
225 WHERE MASTER_WORKORDER_FLAG = 'Y' AND VISIT_ID = x_id;
226
227 -- CURSOR added to get the Project Template Name
228 -- Post 11.5.10
229 CURSOR c_proj_template(p_proj_temp_id IN NUMBER)
230 IS
231 SELECT name
232 FROM PA_PROJECTS
233 WHERE project_id = p_proj_temp_id;
234 BEGIN
235 -------------------------------- Initialize -----------------------
236 -- Standard start of API savepoint
237 SAVEPOINT Get_Visit_Details;
238
239 -- Check if API is called in debug mode. If yes, enable debug.
240 IF G_DEBUG='Y' THEN
241 Ahl_Debug_Pub.enable_debug;
242 END IF;
243
244 -- Debug info.
245 IF G_DEBUG='Y' THEN
246 AHL_DEBUG_PUB.Debug( 'Get Visit Details' ||': Start');
247 END IF;
248
249 -- Initialize message list if p_init_msg_list is set to TRUE.
250 IF Fnd_Api.to_boolean(p_init_msg_list)
251 THEN
252 Fnd_Msg_Pub.initialize;
253 END IF;
254
255 -- Initialize API return status to success
256 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
257
258 -- Standard call to check for call compatibility.
259 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
260 p_api_version,
261 l_api_name,G_PKG_NAME)
262 THEN
263 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265 ------------------------------Start of API Body------------------------------------
266
267 ----------------------------------------- Cursor ----------------------------------
268 OPEN c_visit_details(p_visit_id);
269 FETCH c_visit_details INTO visit_rec;
270 CLOSE c_visit_details;
271
272 OPEN c_Visit(p_visit_id);
273 FETCH c_visit INTO c_visit_rec;
274 CLOSE c_Visit;
275
276 ------------------------------------------ Start -----------------------------------
277 -- Debug info.
278 IF G_DEBUG='Y' THEN
279 AHL_DEBUG_PUB.Debug( 'Visit Id= ' || p_visit_id);
280 END IF;
281
282 -- get workorder name and Id added in 11.5.10
283 OPEN c_workorder_csr(p_visit_id);
284 FETCH c_workorder_csr INTO l_workorder_name, l_workorder_id;
285
286 IF c_workorder_csr%FOUND THEN
287 l_visit_rec.job_number := l_workorder_name;
288 --l_visit_rec.workorder_id := l_workorder_id;
289 END IF;
290 CLOSE c_workorder_csr;
291
292 -- To find meaning for fnd_lookups code
293 IF (visit_rec.project_flag IS NOT NULL) THEN
294 SELECT meaning
295 INTO l_project_flag
296 FROM FND_LOOKUP_VALUES_VL
297 WHERE lookup_code = visit_rec.project_flag
298 AND LOOKUP_TYPE = 'AHL_YES_NO_TYPE';
299 END IF;
300
301 ----------------------------------- FOR VISITS --------------------------------------
302 --IF UPPER(c_visit_rec.template_flag) = 'N' THEN
303 -- To find Unit Name on basis of Instance Id
304 IF visit_rec.item_instance_id IS NOT NULL THEN
305 AHL_DEBUG_PUB.Debug(L_FULL_NAME || 'UNIT NAME - item instance' || visit_rec.item_instance_id);
306 -- Call Get_UnitName to get unit name for a particular instance_id
307 -- Fix for Bug #9260723
308 -- Now getting Unit Name from AHL_UTILITY_PVT.Get_Unit_Name instead of local function
309 -- Get_UnitName.
310 l_unit_name := AHL_UTILITY_PVT.Get_Unit_Name(visit_rec.item_instance_id);
311 AHL_DEBUG_PUB.Debug(L_FULL_NAME || 'UNIT NAME - l_unit_name' || l_unit_name);
312
313 -- Compare unit name entered from unit name derived
314 IF l_unit_name IS NOT NULL THEN
315 l_unit_name := l_unit_name;
316 ELSE
317 l_unit_name := Null;
318 END IF;
319 END IF;
320
321 -- To find simulation plan name for the simulation id from LTP view
322 /*IF (visit_rec.simulation_plan_id IS NOT NULL) THEN
323 SELECT SIMULATION_PLAN_NAME
324 INTO l_simulation_plan_name
325 FROM AHL_SIMULATION_PLANS_VL
326 WHERE SIMULATION_PLAN_ID = visit_rec.simulation_plan_id;
327 ELSE
328 l_simulation_plan_name := NULL;
329 END IF;*/
330
331 -- Post 11.5.10
332 -- Added l_min and l_min_close
333 -- Reema Start
334 -- To check if visit starttime is not null then store time in HH4 format
335 IF (c_visit_rec.START_DATE_TIME IS NOT NULL AND c_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
336 l_hour := TO_NUMBER(TO_CHAR(c_visit_rec.START_DATE_TIME , 'HH24'));
337 ELSE
338 l_hour := NULL;
339 c_visit_rec.START_DATE_TIME := NULL;
340 END IF;
341
342 -- To check if visit closetime is not null then store time in HH4 format
343 IF (visit_rec.CLOSE_DATE_TIME IS NOT NULL AND visit_rec.CLOSE_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
344 l_hour_close := TO_NUMBER(TO_CHAR(visit_rec.CLOSE_DATE_TIME , 'HH24'));
345 ELSE
346 l_hour_close := NULL;
347 visit_rec.CLOSE_DATE_TIME := Null;
348 END IF;
349
350 -- Call local procedure to retrieve Due by Date of the visit
351 Get_Due_by_Date(p_visit_id => l_visit_id, x_due_by_date => l_due_date);
352
353
354 IF (c_visit_rec.START_DATE_TIME IS NOT NULL
355 AND c_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
356
357
358
359 --END IF;
360
361 -- Post 11.5.10
362 -- get the project template name from cursor
363 IF visit_rec.project_template_id IS NOT NULL THEN
364 OPEN c_proj_template(visit_rec.project_template_id);
365 FETCH c_proj_template INTO l_visit_rec.proj_template_name;
366 IF c_proj_template%NOTFOUND THEN
367 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
368 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_PROTEM');
369 Fnd_Msg_Pub.ADD;
370 RAISE Fnd_Api.G_EXC_ERROR;
371 END IF;
372 END IF;
373 CLOSE c_proj_template;
374 END IF;
375
376 -- Assigning all visits field to visit record attributes meant for display
377 l_visit_rec.visit_id := c_visit_rec.visit_id ;
378 l_visit_rec.visit_name := c_visit_rec.visit_name ;
379 l_visit_rec.visit_number := c_visit_rec.visit_number ;
380
381 l_visit_rec.status_code := c_visit_rec.status_code;
382 l_visit_rec.status_name := c_visit_rec.status;
383
384 l_visit_rec.visit_type_code := c_visit_rec.visit_type_code ;
385 l_visit_rec.visit_type_name := c_visit_rec.VISIT_TYPE ;
386
387 l_visit_rec.object_version_number := c_visit_rec.object_version_number ;
388
389 l_visit_rec.inventory_item_id := c_visit_rec.inventory_item_id ;
390 l_visit_rec.item_organization_id := c_visit_rec.item_organization_id ;
391 l_visit_rec.item_name := c_visit_rec.ITEM_DESCRIPTION ;
392
393 l_visit_rec.unit_name := l_unit_name ;
394 l_visit_rec.item_instance_id := c_visit_rec.item_instance_id ;
395 l_visit_rec.serial_number := c_visit_rec.serial_number ;
396
397 -- l_visit_rec.service_request_id := c_visit_rec.service_request_id;
398 -- l_visit_rec.service_request_number:= c_visit_rec.incident_number;
399
400 -- l_visit_rec.space_category_code := c_visit_rec.space_category_code;
401 -- l_visit_rec.space_category_name := c_visit_rec.space_category_mean;
402
403 l_visit_rec.organization_id := c_visit_rec.organization_id ;
404 l_visit_rec.org_name := c_visit_rec.ORGANIZATION_NAME ;
405
406 l_visit_rec.department_id := c_visit_rec.department_id ;
407 l_visit_rec.dept_name := c_visit_rec.DEPARTMENT_NAME ;
408
409
410 l_visit_rec.start_date := c_visit_rec.START_DATE_TIME;
411 l_visit_rec.start_hour := l_hour;
412
413 l_visit_rec.PLAN_END_DATE := visit_rec.CLOSE_DATE_TIME;
414 l_visit_rec.PLAN_END_HOUR := l_hour_close;
415
416 l_visit_rec.project_flag := l_project_flag;
417 l_visit_rec.project_flag_code := visit_rec.project_flag;
418
419 l_visit_rec.end_date := AHL_VWP_TIMES_PVT.get_visit_end_time(c_visit_rec.visit_id);
420 l_visit_rec.due_by_date := l_due_date ;
421 l_visit_rec.duration := NULL ;
422
423 -- l_visit_rec.simulation_plan_id := visit_rec.simulation_plan_id ;
424 -- l_visit_rec.simulation_plan_name := l_simulation_plan_name ;
425
426 -- l_visit_rec.template_flag := c_visit_rec.template_flag ;
427 l_visit_rec.description := visit_rec.description ;
428 l_visit_rec.last_update_date := visit_rec.last_update_date;
429
430 l_visit_rec.project_id := visit_rec.project_id;
431 l_visit_rec.project_number := visit_rec.visit_number;
432 l_visit_rec.outside_party_flag := visit_rec.outside_party_flag;
433
434 -- Post 11.5.10
435 -- Reema Start
436 l_visit_rec.priority_code := visit_rec.priority_code;
437 l_visit_rec.proj_template_id := visit_rec.project_template_id;
438 -- l_visit_rec.priority_value := c_visit_rec.priority_mean;
439 -- Reema End
440 x_visit_rec := l_visit_rec;
441 END IF;
442
443 IF G_DEBUG='Y' THEN
444 AHL_DEBUG_PUB.Debug( l_full_name ||': End of Get Visit Details**********************');
445 END IF;
446
447 ------------------------End of API Body------------------------------------
448 -- Standard call to get message count and if count is 1, get message info
449 Fnd_Msg_Pub.Count_And_Get
450 ( p_count => x_msg_count,
451 p_data => x_msg_data,
452 p_encoded => Fnd_Api.g_false);
453
454 -- Check if API is called in debug mode. If yes, enable debug.
455 IF G_DEBUG='Y' THEN
456 Ahl_Debug_Pub.enable_debug;
457 END IF;
458
459 -- Debug info.
460 IF G_DEBUG='Y' THEN
461 AHL_DEBUG_PUB.Debug( 'AHL_VWP_VISITS_PVT - End');
462 END IF;
463
464 -- Check if API is called in debug mode. If yes, disable debug.
465 IF G_DEBUG='Y' THEN
466 Ahl_Debug_Pub.disable_debug;
467 END IF;
468 RETURN;
469
470 EXCEPTION
471 WHEN Fnd_Api.G_EXC_ERROR THEN
472 x_return_status := Fnd_Api.G_RET_STS_ERROR;
473 ROLLBACK TO Get_Visit_Details;
474 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
475 p_data => x_msg_data,
476 p_encoded => Fnd_Api.g_false);
477
478
479 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
480 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
481 ROLLBACK TO Get_Visit_Details;
482 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
483 p_data => x_msg_data,
484 p_encoded => Fnd_Api.g_false);
485
486 WHEN OTHERS THEN
487 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
488 ROLLBACK TO Get_Visit_Details;
489 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
490 p_procedure_name => 'Get_Visit_Details',
491 p_error_text => SQLERRM);
492
493 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
494 p_data => x_msg_data,
495 p_encoded => Fnd_Api.g_false);
496 END Get_Visit_Details;
497
498
499
500 ----------------------------------------------------------------------
501 -- END: Defining procedures BODY, which are called from UI screen --
502 ----------------------------------------------------------------------
503
504 END AHL_PRD_VISITS_PVT;
[Home] [Help]