1 PACKAGE BODY AHL_PRD_VISITS_PVT AS
2 /* $Header: AHLVPSVB.pls 120.0 2005/05/26 01:43:53 appldev noship $*/
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 l_unit_name := Get_UnitName(visit_rec.item_instance_id);
308 AHL_DEBUG_PUB.Debug(L_FULL_NAME || 'UNIT NAME - l_unit_name' || l_unit_name);
309
310 -- Compare unit name entered from unit name derived
311 IF l_unit_name IS NOT NULL THEN
312 l_unit_name := l_unit_name;
313 ELSE
314 l_unit_name := Null;
315 END IF;
316 END IF;
317
318 -- To find simulation plan name for the simulation id from LTP view
319 /*IF (visit_rec.simulation_plan_id IS NOT NULL) THEN
320 SELECT SIMULATION_PLAN_NAME
321 INTO l_simulation_plan_name
322 FROM AHL_SIMULATION_PLANS_VL
323 WHERE SIMULATION_PLAN_ID = visit_rec.simulation_plan_id;
324 ELSE
325 l_simulation_plan_name := NULL;
326 END IF;*/
327
328 -- Post 11.5.10
329 -- Added l_min and l_min_close
330 -- Reema Start
331 -- To check if visit starttime is not null then store time in HH4 format
332 IF (c_visit_rec.START_DATE_TIME IS NOT NULL AND c_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
333 l_hour := TO_NUMBER(TO_CHAR(c_visit_rec.START_DATE_TIME , 'HH24'));
334 ELSE
335 l_hour := NULL;
336 c_visit_rec.START_DATE_TIME := NULL;
337 END IF;
338
339 -- To check if visit closetime is not null then store time in HH4 format
340 IF (visit_rec.CLOSE_DATE_TIME IS NOT NULL AND visit_rec.CLOSE_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
341 l_hour_close := TO_NUMBER(TO_CHAR(visit_rec.CLOSE_DATE_TIME , 'HH24'));
342 ELSE
343 l_hour_close := NULL;
344 visit_rec.CLOSE_DATE_TIME := Null;
345 END IF;
346
350
347 -- Call local procedure to retrieve Due by Date of the visit
348 Get_Due_by_Date(p_visit_id => l_visit_id, x_due_by_date => l_due_date);
349
351 IF (c_visit_rec.START_DATE_TIME IS NOT NULL
352 AND c_visit_rec.START_DATE_TIME <> Fnd_Api.G_MISS_DATE) THEN
353
354
355
356 --END IF;
357
358 -- Post 11.5.10
359 -- get the project template name from cursor
360 IF visit_rec.project_template_id IS NOT NULL THEN
361 OPEN c_proj_template(visit_rec.project_template_id);
362 FETCH c_proj_template INTO l_visit_rec.proj_template_name;
363 IF c_proj_template%NOTFOUND THEN
364 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
365 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_PROTEM');
366 Fnd_Msg_Pub.ADD;
367 RAISE Fnd_Api.G_EXC_ERROR;
368 END IF;
369 END IF;
370 CLOSE c_proj_template;
371 END IF;
372
373 -- Assigning all visits field to visit record attributes meant for display
374 l_visit_rec.visit_id := c_visit_rec.visit_id ;
375 l_visit_rec.visit_name := c_visit_rec.visit_name ;
376 l_visit_rec.visit_number := c_visit_rec.visit_number ;
377
378 l_visit_rec.status_code := c_visit_rec.status_code;
379 l_visit_rec.status_name := c_visit_rec.status;
380
381 l_visit_rec.visit_type_code := c_visit_rec.visit_type_code ;
382 l_visit_rec.visit_type_name := c_visit_rec.VISIT_TYPE ;
383
384 l_visit_rec.object_version_number := c_visit_rec.object_version_number ;
385
386 l_visit_rec.inventory_item_id := c_visit_rec.inventory_item_id ;
387 l_visit_rec.item_organization_id := c_visit_rec.item_organization_id ;
388 l_visit_rec.item_name := c_visit_rec.ITEM_DESCRIPTION ;
389
390 l_visit_rec.unit_name := l_unit_name ;
391 l_visit_rec.item_instance_id := c_visit_rec.item_instance_id ;
392 l_visit_rec.serial_number := c_visit_rec.serial_number ;
393
394 -- l_visit_rec.service_request_id := c_visit_rec.service_request_id;
395 -- l_visit_rec.service_request_number:= c_visit_rec.incident_number;
396
397 -- l_visit_rec.space_category_code := c_visit_rec.space_category_code;
398 -- l_visit_rec.space_category_name := c_visit_rec.space_category_mean;
399
400 l_visit_rec.organization_id := c_visit_rec.organization_id ;
401 l_visit_rec.org_name := c_visit_rec.ORGANIZATION_NAME ;
402
403 l_visit_rec.department_id := c_visit_rec.department_id ;
404 l_visit_rec.dept_name := c_visit_rec.DEPARTMENT_NAME ;
405
406
407 l_visit_rec.start_date := c_visit_rec.START_DATE_TIME;
408 l_visit_rec.start_hour := l_hour;
409
410 l_visit_rec.PLAN_END_DATE := visit_rec.CLOSE_DATE_TIME;
411 l_visit_rec.PLAN_END_HOUR := l_hour_close;
412
413 l_visit_rec.project_flag := l_project_flag;
414 l_visit_rec.project_flag_code := visit_rec.project_flag;
415
416 l_visit_rec.end_date := AHL_VWP_TIMES_PVT.get_visit_end_time(c_visit_rec.visit_id);
417 l_visit_rec.due_by_date := l_due_date ;
418 l_visit_rec.duration := NULL ;
419
420 -- l_visit_rec.simulation_plan_id := visit_rec.simulation_plan_id ;
421 -- l_visit_rec.simulation_plan_name := l_simulation_plan_name ;
422
423 -- l_visit_rec.template_flag := c_visit_rec.template_flag ;
424 l_visit_rec.description := visit_rec.description ;
425 l_visit_rec.last_update_date := visit_rec.last_update_date;
426
427 l_visit_rec.project_id := visit_rec.project_id;
428 l_visit_rec.project_number := visit_rec.visit_number;
429 l_visit_rec.outside_party_flag := visit_rec.outside_party_flag;
430
431 -- Post 11.5.10
432 -- Reema Start
433 l_visit_rec.priority_code := visit_rec.priority_code;
434 l_visit_rec.proj_template_id := visit_rec.project_template_id;
435 -- l_visit_rec.priority_value := c_visit_rec.priority_mean;
436 -- Reema End
437 x_visit_rec := l_visit_rec;
438 END IF;
439
440 IF G_DEBUG='Y' THEN
441 AHL_DEBUG_PUB.Debug( l_full_name ||': End of Get Visit Details**********************');
442 END IF;
443
444 ------------------------End of API Body------------------------------------
445 -- Standard call to get message count and if count is 1, get message info
446 Fnd_Msg_Pub.Count_And_Get
447 ( p_count => x_msg_count,
448 p_data => x_msg_data,
449 p_encoded => Fnd_Api.g_false);
450
451 -- Check if API is called in debug mode. If yes, enable debug.
452 IF G_DEBUG='Y' THEN
453 Ahl_Debug_Pub.enable_debug;
454 END IF;
455
456 -- Debug info.
457 IF G_DEBUG='Y' THEN
458 AHL_DEBUG_PUB.Debug( 'AHL_VWP_VISITS_PVT - End');
459 END IF;
460
461 -- Check if API is called in debug mode. If yes, disable debug.
462 IF G_DEBUG='Y' THEN
463 Ahl_Debug_Pub.disable_debug;
464 END IF;
465 RETURN;
466
467 EXCEPTION
468 WHEN Fnd_Api.G_EXC_ERROR THEN
469 x_return_status := Fnd_Api.G_RET_STS_ERROR;
470 ROLLBACK TO Get_Visit_Details;
471 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
472 p_data => x_msg_data,
473 p_encoded => Fnd_Api.g_false);
474
475
476 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
477 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
478 ROLLBACK TO Get_Visit_Details;
479 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
480 p_data => x_msg_data,
481 p_encoded => Fnd_Api.g_false);
482
483 WHEN OTHERS THEN
484 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
485 ROLLBACK TO Get_Visit_Details;
486 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
487 p_procedure_name => 'Get_Visit_Details',
488 p_error_text => SQLERRM);
489
490 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
491 p_data => x_msg_data,
492 p_encoded => Fnd_Api.g_false);
493 END Get_Visit_Details;
494
495
496
497 ----------------------------------------------------------------------
498 -- END: Defining procedures BODY, which are called from UI screen --
499 ----------------------------------------------------------------------
500
501 END AHL_PRD_VISITS_PVT;
[Home] [Help]