1 PACKAGE BODY AHL_VWP_RULES_PVT AS
2 /* $Header: AHLVRULB.pls 120.13 2008/04/17 06:01:10 rnahata ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_RULES_PVT
6 --
7 -- PURPOSE
8 -- This package body is a Private API for managing VWP Visit and Tasks
9 -- general utilities procedures in Complex Maintainance, Repair and Overhauling(CMRO).
10 -- It defines few global constants, various local functions and procedures
11 -- used by other Visit Work Package(VWP) APIs
12 --
13 -- PROCEDURES
14 -- Check_Serial_No_by_UConfig -- Check_Item_name_Or_Id
15 -- Check_Serial_Name_Or_Id -- Check_Lookup_name_Or_Id
16 -- Check_Org_Name_Or_Id -- Check_Dept_Desc_Or_Id
17 -- Check_Visit_is_Simulated -- Check_Unit_Name_Valid
18 -- Check_Stage_Number_Or_Id -- Check_SR_request_number_Or_Id
19 -- Check_Visit_Task_Number_Or_Id -- Check_Project_Template_Or_Id
20 -- Check_Proj_Responsibility -- Check_Cost_Parent_Loop
21 -- Check_Origin_Task_Loop -- Check_Price_List_Name_Or_Id
22
23 -- Validate_bef_Times_Derive -- Insert_Task
24 -- Create_Tasks_for_MR -- Tech_Dependency
25
26 -- Get_Visit_Task_Id -- Get_Visit_Task_Number
27 -- Get_Serial_Item_by_Unit -- Get_Cost_Originating_Id
28 -- Get_Summary_Task_Time --
29 --
30 -- Update_Cost_Origin_Task -- Update_Visit_Task_Flag
31 -- Merge_for_Unique_Items -- Merge_for_Unique_Items
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 -- 03-MAR-2003 SHBHANDA Created.
37 -- 06-AUG-2003 SHBHANDA 11.5.10 Changes.
38 -- 09-12-2003 RTADIKON Merge_for_Unique_Items Coded for costing 11.5.10
39 -- Along with the logging mechanism.
40 -- 05-NOV-2007 RNAHATA Replaced all Ahl_Debug_Pub.debug with STATEMENT
41 -- level logs and added more STATEMENT level logs at
42 -- key decision points. Added PROCEDURE level logs
43 -- when entering and exiting a procedure.
44 -----------------------------------------------------------------
45
46 -----------------------------------------------------------------
47 -- Define Global CONSTANTS --
48 -----------------------------------------------------------------
49 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
50 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_RULES_PVT';
51 G_OPER_ASSOC_TYPE CONSTANT VARCHAR2(30) := 'OPERATION';
52 G_RT_ASSOC_TYPE CONSTANT VARCHAR2(30) := 'ROUTE';
53 ------------------------------------
54 -- Common constants and variables --
55 ------------------------------------
56 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
57 l_log_statement NUMBER := fnd_log.level_statement;
58 l_log_procedure NUMBER := fnd_log.level_procedure;
59 l_log_error NUMBER := fnd_log.level_error;
60 l_log_unexpected NUMBER := fnd_log.level_unexpected;
61 ---------------------------------------------------------------------
62 -- Define Record Types for record structures needed by the APIs --
63 ---------------------------------------------------------------------
64 -- Serial record type for validating serial exists in unit-config tree of the visit
65 TYPE Serial_Rec_Type IS RECORD
66 (INSTANCE_ID NUMBER,
67 SERIAL_NUMBER VARCHAR2(30));
68
69 ---------------------------------------------------------------------
70 -- Define Table Types for table structures of records needed by the APIs --
71 ---------------------------------------------------------------------
72 -- Table type for storing 'Serial_Rec_Type' record datatype
73 TYPE Serial_Tbl_Type IS TABLE OF Serial_Rec_Type
74 INDEX BY BINARY_INTEGER;
75
76 --------------------------------------------------------------------
77 -- Define local procedures signature --
78 --------------------------------------------------------------------
79
80 --------------------------------------------------------------------
81 -- Define local functions signature --
82 --------------------------------------------------------------------
83
84 --------------------------------------------------------------------
85 -- Define local procedures body --
86 --------------------------------------------------------------------
87 -----------------------------------------------------------------------
88 -- FUNCTION
89 -- instance_in_config_tree
90 --
91 -- PURPOSE
92 -- Check whether p_instance_id belongs to the instance of p_visit_id
93 -- Return 'Y' for the following cases:
94 -- 1. p_visit_id doesn't have instance_id associated at all
95 -- 2. The instance_id of p_visit_id = p_instance_id regardless whether
96 -- the instance of p_visit_id has components or not
97 -- 3. p_instance_id is a component of the instance of p_visit_id regardless
98 -- whether it is a UC tree or IB tree
99 -- Return 'N' otherwise
100 -----------------------------------------------------------------------
101 FUNCTION instance_in_config_tree(p_visit_id NUMBER, p_instance_id NUMBER) RETURN VARCHAR2
102 IS
103 l_instance_id NUMBER;
104 l_visit_instance_id NUMBER;
105 L_API_NAME CONSTANT VARCHAR2(30) := 'instance_in_config_tree';
106 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
107
108 CURSOR get_visit_instance IS
109 SELECT item_instance_id
110 FROM ahl_visits_b
111 WHERE visit_id = p_visit_id;
112 CURSOR check_instance_in_tree(c_top_instance_id NUMBER, c_instance_id NUMBER) IS
113 SELECT subject_id
114 FROM csi_ii_relationships
115 WHERE subject_id = c_instance_id
116 START WITH object_id = c_top_instance_id
117 AND relationship_type_code = 'COMPONENT-OF'
118 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
119 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
120 CONNECT BY object_id = PRIOR subject_id
121 AND relationship_type_code = 'COMPONENT-OF'
122 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
123 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
124
125 BEGIN
126 IF (l_log_procedure >= l_log_current_level) THEN
127 fnd_log.string(l_log_procedure,
128 L_DEBUG_KEY ||'.begin',
129 'At the start of PL SQL function. Visit Id = ' || p_visit_id ||
130 ', p_instance_id = ' || p_instance_id);
131 END IF;
132 OPEN get_visit_instance;
133 FETCH get_visit_instance INTO l_visit_instance_id;
134 -- Added additional or condition by senthil as the Visit Instance Id can be null.
135 IF get_visit_instance%NOTFOUND OR l_visit_instance_id is NULL THEN
136 CLOSE get_visit_instance;
137 RETURN FND_API.G_RET_STS_SUCCESS;
138 ELSE
139 CLOSE get_visit_instance;
140 IF l_visit_instance_id = p_instance_id THEN
141 RETURN FND_API.G_RET_STS_SUCCESS;
142 ELSE
143 OPEN check_instance_in_tree(l_visit_instance_id, p_instance_id);
144 FETCH check_instance_in_tree INTO l_instance_id;
145 IF check_instance_in_tree%FOUND THEN
146 CLOSE check_instance_in_tree;
147 RETURN FND_API.G_RET_STS_SUCCESS;
148 ELSE
149 CLOSE check_instance_in_tree;
150 RETURN FND_API.G_RET_STS_ERROR;
151 END IF;
152 END IF;
153 END IF;
154 END;
155
156 --------------------------------------------------------------------
157 -- PROCEDURE
158 -- Check_Item_Name_OR_Id
159 --
160 -- PURPOSE
161 -- Converts Item Name and Inventory Org to Inventory Item Id
162 --------------------------------------------------------------------
163 PROCEDURE Check_Item_Name_Or_Id
164 (p_item_id IN NUMBER,
165 p_org_id IN NUMBER,
166 p_item_name IN VARCHAR2,
167
168 x_item_id OUT NOCOPY NUMBER,
169 x_org_id OUT NOCOPY NUMBER,
170 x_item_name OUT NOCOPY VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_error_msg_code OUT NOCOPY VARCHAR2
173 )
174 IS
175 -- Define local variables
176 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Item_Name_Or_Id';
177 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
178 BEGIN
179 IF (l_log_procedure >= l_log_current_level) THEN
180 fnd_log.string(l_log_procedure,
181 L_DEBUG_KEY ||'.begin',
182 'At the start of PL SQL procedure. Item Id = ' ||
183 p_item_id || ', Item Name = ' || p_item_name || ', Org Id = ' || p_org_id);
184 END IF;
185
186 IF p_org_id IS NOT NULL THEN
187 IF p_item_name IS NOT NULL THEN
188 -- SELECT concatenated_segments, inventory_item_id, inventory_org_id
189 -- INTO x_item_name, x_item_id, x_org_id
190 -- FROM AHL_MTL_ITEMS_OU_V
191 -- WHERE concatenated_segments = p_item_name AND inventory_org_id = p_org_id;
192
193 -- Fix for ADS bug# 4357001.
194 -- AnRaj:Changes made for fixing bug#4919353, issue# 1
195 /* SELECT concatenated_segments, inventory_item_id, organization_id
196 INTO x_item_name, x_item_id, x_org_id
197 FROM MTL_SYSTEM_ITEMS_KFV
198 WHERE concatenated_segments = p_item_name AND organization_id = p_org_id
199 AND organization_id IN (Select DISTINCT m.master_organization_id
200 FROM org_organization_definitions org, mtl_parameters m
201 WHERE org.organization_id = m.organization_id
202 AND NVL(org.operating_unit, mo_global.get_current_org_id())
203 = mo_global.get_current_org_id()
204 );*/
205
206 SELECT concatenated_segments, inventory_item_id, organization_id
207 INTO x_item_name, x_item_id, x_org_id
208 FROM MTL_SYSTEM_ITEMS_KFV
209 WHERE concatenated_segments = p_item_name
210 AND organization_id = p_org_id
211 AND organization_id IN
212 (SELECT DISTINCT m.master_organization_id
213 FROM INV_ORGANIZATION_INFO_V org,
214 mtl_parameters m
215 WHERE org.organization_id = m.organization_id
216 AND NVL(org.operating_unit,mo_global.get_current_org_id()) = mo_global.get_current_org_id()
217 ) ;
218 ELSIF p_item_id IS NOT NULL THEN
219 -- SELECT concatenated_segments, inventory_item_id, inventory_org_id
220 -- INTO x_item_name, x_item_id, x_org_id
221 -- FROM AHL_MTL_ITEMS_OU_V
222 -- WHERE inventory_item_id = p_item_id AND inventory_org_id = p_org_id;
223
224 -- Fix for ADS bug# 4357001.
225 /* SELECT concatenated_segments, inventory_item_id, organization_id
226 INTO x_item_name, x_item_id, x_org_id
227 FROM MTL_SYSTEM_ITEMS_KFV
228 WHERE inventory_item_id = p_item_id AND organization_id = p_org_id
229 AND organization_id IN (Select DISTINCT m.master_organization_id
230 FROM org_organization_definitions org, mtl_parameters m
231 WHERE org.organization_id = m.organization_id
232 AND NVL(org.operating_unit, mo_global.get_current_org_id())
233 = mo_global.get_current_org_id()
234 ); */
235 -- AnRaj: Changes made for fixing bug#4919353
236 SELECT concatenated_segments, inventory_item_id, organization_id
237 INTO x_item_name, x_item_id, x_org_id
238 FROM MTL_SYSTEM_ITEMS_KFV
239 WHERE inventory_item_id = p_item_id AND organization_id = p_org_id
240 AND organization_id IN (Select DISTINCT m.master_organization_id
241 FROM inv_organization_info_v org,
242 mtl_parameters m
243 WHERE org.organization_id = m.organization_id
244 AND NVL(org.operating_unit,mo_global.get_current_org_id())
245 = mo_global.get_current_org_id()
246 ) ;
247 END IF;
248 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
249 ELSE
250 x_return_status := Fnd_Api.G_RET_STS_ERROR;
251 END IF;
252
253 IF (l_log_procedure >= l_log_current_level) THEN
254 fnd_log.string(l_log_procedure,
255 L_DEBUG_KEY ||'.end',
256 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
257 '. Item Id = ' || x_item_id || ', Item Name = ' || x_item_name ||
258 ', Org Id = ' || x_org_id);
259 END IF;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
264 x_error_msg_code:= 'AHL_VWP_ITEM_NOT_EXISTS';
265 WHEN TOO_MANY_ROWS THEN
266 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
267 x_error_msg_code:= 'AHL_VWP_ITEM_NOT_EXISTS';
268 WHEN OTHERS THEN
269 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
270 RAISE;
271 END Check_Item_Name_Or_Id;
272
273 -- Post 11.5.10 Enhancements
274 -- Added Procedure for Checking Project Template Name of Id.
275 --------------------------------------------------------------------
276 -- PROCEDURE
277 -- Check_Project_Template_Or_Id
278 --
279 -- PURPOSE
280 -- Procedure to check project template name and retrieve project id
281 --------------------------------------------------------------------
282 PROCEDURE Check_Project_Template_Or_Id
283 (
284 p_proj_temp_name IN VARCHAR2,
285
286 x_project_id OUT NOCOPY NUMBER,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_error_msg_code OUT NOCOPY VARCHAR2
289 )
290 IS
291 -- Define local variables
292 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Project_Template_Or_Id';
293 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
294
295 CURSOR c_proj_template(p_proj_temp_name IN VARCHAR2)
296 IS
297 -- AnRaj:Changes made for fixing bug#4919353, issue# 8
298 SELECT project_id
299 FROM PA_PROJECTS
300 WHERE name = p_proj_temp_name
301 AND TEMPLATE_FLAG = 'Y';
302
303 /*
304 -- Commented by rnahata on June 25, 2007 for Bug 6147752
305 -- Check removed to avoid having to setup Project's
306 -- Carrying-out Org as an Inventory Org
307 AND carrying_out_organization_id IN
308 ( SELECT organization_id
309 FROM INV_ORGANIZATION_INFO_V
310 WHERE NVL(operating_unit,mo_global.get_current_org_id()) =
311 mo_global.get_current_org_id()
312 );
313 */
314 /*
315 SELECT project_id
316 FROM PA_PROJECTS
317 WHERE name = p_proj_temp_name
318 AND TEMPLATE_FLAG = 'Y'
319 AND carrying_out_organization_id IN (SELECT organization_id
320 FROM org_organization_definitions
321 WHERE NVL(operating_unit, mo_global.get_current_org_id()) =
322 mo_global.get_current_org_id());
323 */
324
325 BEGIN
326
327 IF (l_log_procedure >= l_log_current_level) THEN
328 fnd_log.string(l_log_procedure,
329 L_DEBUG_KEY ||'.begin',
330 'At the start of PL SQL procedure. Project Template Name = ' || p_proj_temp_name);
331 END IF;
332
333 IF p_proj_temp_name IS NOT NULL THEN
334 OPEN c_proj_template(p_proj_temp_name);
335 FETCH c_proj_template INTO x_project_id;
336 IF c_proj_template%NOTFOUND
337 THEN
338 x_return_status := Fnd_Api.G_RET_STS_ERROR;
339 ELSE
340 IF (l_log_statement >= l_log_current_level) THEN
341 fnd_log.string(l_log_statement,
342 L_DEBUG_KEY,
343 'Project Id = ' || x_project_id);
344 END IF;
345 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
346 END IF;
347 CLOSE c_proj_template;
348 END IF;
349
350 IF (l_log_procedure >= l_log_current_level) THEN
351 fnd_log.string(l_log_procedure,
352 L_DEBUG_KEY ||'.end',
353 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
354 END IF;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
359 RAISE;
360 END Check_Project_Template_Or_Id;
361
362 --------------------------------------------------------------------
363 -- PROCEDURE
364 -- Check_Serial_Name_Or_Id
365 --
366 -- PURPOSE
367 -- Converts Serial Name to Instance Id
368 --------------------------------------------------------------------
369 PROCEDURE Check_Serial_Name_Or_Id
370 (p_item_id IN NUMBER,
371 p_org_id IN NUMBER,
372 p_serial_id IN NUMBER,
373 p_serial_number IN VARCHAR2,
374
375 x_serial_id OUT NOCOPY NUMBER,
376 x_return_status OUT NOCOPY VARCHAR2,
377 x_error_msg_code OUT NOCOPY VARCHAR2
378 )
379 IS
380 -- Define local variables
381 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Serial_Name_Or_Id';
382 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
383 BEGIN
384 IF (l_log_procedure >= l_log_current_level) THEN
385 fnd_log.string(l_log_procedure,
386 L_DEBUG_KEY ||'.begin',
387 'At the start of PL SQL procedure. Org Id = ' || p_org_id ||
388 ', Item Id = ' || p_item_id ||
389 ', Serial Id = ' || p_serial_id ||
390 ', Serial Number' || p_serial_number);
391 END IF;
392
393 IF (p_serial_id IS NOT NULL AND p_item_id IS NOT NULL AND p_org_id IS NOT NULL) THEN
394 SELECT Instance_Id INTO x_serial_id
395 FROM CSI_ITEM_INSTANCES
396 WHERE Instance_Id = p_serial_id AND Inventory_Item_Id = p_item_id AND Inv_Master_Organization_Id = p_org_id
397 AND ACTIVE_START_DATE <= sysdate AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
398 END IF;
399
400 IF (p_serial_number IS NOT NULL AND p_item_id IS NOT NULL AND p_org_id IS NOT NULL) THEN
401 SELECT Instance_Id INTO x_serial_id
402 FROM CSI_ITEM_INSTANCES
403 WHERE Serial_Number = p_serial_number AND Inventory_Item_Id = p_item_id AND Inv_Master_Organization_Id = p_org_id
404 AND ACTIVE_START_DATE <= sysdate AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
405 END IF;
406
407 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
408
409 IF (l_log_procedure >= l_log_current_level) THEN
410 fnd_log.string(l_log_procedure,
411 L_DEBUG_KEY ||'.end',
412 'At the end of PL SQL procedure. Return Status = ' ||
413 x_return_status || ', Serial Id = ' || x_serial_id);
414 END IF;
415
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
419 x_error_msg_code:= 'AHL_VWP_SERIAL_NOT_EXISTS';
420 WHEN TOO_MANY_ROWS THEN
421 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
422 x_error_msg_code:= 'AHL_VWP_SERIAL_NOT_EXISTS';
423 WHEN OTHERS THEN
424 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
425 RAISE;
426 END Check_Serial_Name_Or_Id;
427
428 --------------------------------------------------------------------
429 -- PROCEDURE
430 -- Check_Dept_Desc_Or_Id
431 --
432 -- PURPOSE
433 -- Converts Deparment Name to Department Id.
434 --------------------------------------------------------------------
435 PROCEDURE Check_Dept_Desc_Or_Id
436 (p_organization_id IN NUMBER,
437 p_department_id IN NUMBER,
438 p_dept_name IN VARCHAR2,
439
440 x_department_id OUT NOCOPY NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_error_msg_code OUT NOCOPY VARCHAR2
443 )
444 IS
445 -- Define local variables
446 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Dept_Desc_Or_Id';
447 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
448 BEGIN
449 IF (l_log_procedure >= l_log_current_level) THEN
450 fnd_log.string(l_log_procedure,
451 L_DEBUG_KEY ||'.begin',
452 'At the start of PL SQL procedure. Org Id = ' || p_organization_id ||
453 ', Dept Id = ' || p_department_id || ', Dept Name = ' || p_dept_name);
454 END IF;
455 IF (p_department_id IS NOT NULL) THEN
456 SELECT department_id INTO x_department_id
457 FROM BOM_DEPARTMENTS
458 WHERE organization_id = p_organization_id
459 AND department_id = p_department_id;
460 END IF;
461
462 IF (p_dept_name IS NOT NULL) THEN
463 SELECT department_id INTO x_department_id
464 FROM BOM_DEPARTMENTS
465 WHERE organization_id = p_organization_id
466 AND description = p_dept_name;
467 END IF;
468
469 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
470
471 IF (l_log_procedure >= l_log_current_level) THEN
472 fnd_log.string(l_log_procedure,
473 L_DEBUG_KEY ||'.end',
474 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
475 '. Department Id = ' || x_department_id);
476 END IF;
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
481 x_error_msg_code:= 'AHL_VWP_DEPT_NOT_EXISTS';
482 WHEN TOO_MANY_ROWS THEN
483 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
484 x_error_msg_code:= 'AHL_VWP_DEPT_NOT_EXISTS';
485 WHEN OTHERS THEN
486 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
487 RAISE;
488 END Check_Dept_Desc_Or_Id;
489
490 --------------------------------------------------------------------
491 -- PROCEDURE
492 -- Check_Org_Name_Or_Id
493 --
494 -- PURPOSE
495 -- Converts Organization Name to Organization Id
496 --------------------------------------------------------------------
497 PROCEDURE Check_Org_Name_Or_Id
498 (p_organization_id IN NUMBER,
499 p_org_name IN VARCHAR2,
500
501 x_organization_id OUT NOCOPY NUMBER,
502 x_return_status OUT NOCOPY VARCHAR2,
503 x_error_msg_code OUT NOCOPY VARCHAR2
504 )
505 IS
506 -- Define local variables
507 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Org_Name_Or_Id';
508 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
509
510 BEGIN
511 IF (l_log_procedure >= l_log_current_level) THEN
512 fnd_log.string(l_log_procedure,
513 L_DEBUG_KEY ||'.begin',
514 'At the start of PL SQL procedure. Organization Id = ' || p_organization_id ||
515 'Organization Name = ' || p_org_name);
516 END IF;
517 IF (p_organization_id IS NOT NULL) THEN
518 -- AnRaj:Changes made for fixing bug#4919353, issue# 6
519 SELECT hou.organization_id
520 INTO x_organization_id
521 FROM hr_organization_units hou, mtl_parameters MP
522 WHERE hou.organization_id = mp.organization_id
523 AND hou.organization_id = p_organization_id
524 AND hou.organization_id IN
525 (SELECT organization_id
526 FROM INV_ORGANIZATION_INFO_V
527 WHERE hou.organization_id = mp.organization_id
528 AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
529 AND MP.EAM_enabled_flag='Y';
530 ELSE
531 -- AnRaj:Changes made for fixing bug#4919353, issue# 5
532 SELECT hou.organization_id
533 INTO x_organization_id
534 FROM hr_organization_units hou, mtl_parameters MP
535 WHERE hou.organization_id = mp.organization_id
536 AND hou.Name = p_org_name
537 AND hou.organization_id IN
538 (SELECT organization_id
539 FROM INV_ORGANIZATION_INFO_V
540 WHERE hou.organization_id = mp.organization_id
541 AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
542 AND MP.EAM_enabled_flag='Y';
543 END IF;
544 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
545 IF (l_log_procedure >= l_log_current_level) THEN
546 fnd_log.string(l_log_procedure,
547 L_DEBUG_KEY ||'.end',
548 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
549 'Organization Id = ' || x_organization_id);
550 END IF;
551
552 EXCEPTION
553 WHEN NO_DATA_FOUND THEN
554 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
555 x_error_msg_code:= 'AHL_VWP_ORG_NOT_EXISTS';
556 WHEN TOO_MANY_ROWS THEN
557 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
558 x_error_msg_code:= 'AHL_VWP_ORG_NOT_EXISTS';
559 WHEN OTHERS THEN
560 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
561 RAISE;
562 END Check_Org_Name_Or_Id;
563
564 --------------------------------------------------------------------
565 -- PROCEDURE
566 -- Check_SR_Request_Number_Or_Id
567 --
568 -- PURPOSE
569 -- Converts Service Request Number to Service Request Id
570 --------------------------------------------------------------------
571 PROCEDURE Check_SR_Request_Number_Or_Id
572 (p_service_id IN NUMBER,
573 p_service_number IN VARCHAR2,
574
575 x_service_id OUT NOCOPY NUMBER,
576 x_return_status OUT NOCOPY VARCHAR2,
577 x_error_msg_code OUT NOCOPY VARCHAR2
578 )
579 IS
580 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_SR_Request_Number_Or_Id';
581 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
582
583 BEGIN
584
585 IF (l_log_procedure >= l_log_current_level) THEN
586 fnd_log.string(l_log_procedure,
587 L_DEBUG_KEY ||'.begin',
588 'At the start of PL SQL procedure. SR Number= ' ||
589 p_service_number || ', SR Id = ' || p_service_id);
590 END IF;
591
592 -- yazhou 19Oct2005 Starts
593 -- Bug fix #4415024
594 IF p_service_id IS NOT NULL THEN
595 SELECT a.Incident_Id INTO x_service_id
596 FROM CS_INCIDENTS_ALL_B a, CS_INCIDENT_TYPES_VL CIT
597 WHERE a.incident_type_id = cit.incident_type_id
598 AND cit.INCIDENT_SUBTYPE = 'INC'
599 AND cit.CMRO_FLAG = 'Y'
600 AND Incident_Id = p_service_id;
601 ELSIF p_service_number IS NOT NULL THEN
602 SELECT a.Incident_Id INTO x_service_id
603 FROM CS_INCIDENTS_ALL_B a, CS_INCIDENT_TYPES_VL CIT
604 WHERE a.incident_type_id = cit.incident_type_id
605 AND cit.INCIDENT_SUBTYPE = 'INC'
606 AND cit.CMRO_FLAG = 'Y'
607 AND Incident_Number = p_service_number;
608 -- yazhou 19Oct2005 ends
609 ELSE
610 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
611 x_error_msg_code:= 'AHL_VWP_SERVICE_REQ_NOT_EXISTS';
612 END IF;
613
614 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
615
616 IF (l_log_procedure >= l_log_current_level) THEN
617 fnd_log.string(l_log_procedure,
618 L_DEBUG_KEY ||'.end',
619 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
620 'Service Id = ' || x_service_id);
621 END IF;
622
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
626 x_error_msg_code:= 'AHL_VWP_SERVICE_REQ_NOT_EXISTS';
627 WHEN TOO_MANY_ROWS THEN
628 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
629 x_error_msg_code:= 'AHL_VWP_SERVICE_REQ_NOT_EXISTS';
630 WHEN OTHERS THEN
631 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
632 RAISE;
633 END Check_SR_Request_Number_Or_Id;
634
635 --------------------------------------------------------------------
636 -- PROCEDURE
637 -- Check_Visit_is_Simulated
638 --
639 -- PURPOSE
640 -- Check if the Visit is Simulated or not
641 --------------------------------------------------------------------
642 PROCEDURE Check_Visit_is_Simulated
643 (p_Visit_id IN NUMBER,
644
645 x_bln_flag OUT NOCOPY VARCHAR2,
646 x_return_status OUT NOCOPY VARCHAR2,
647 x_error_msg_code OUT NOCOPY VARCHAR2
648 )
649 IS
650 -- Define local variables
651 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Visit_is_Simulated';
652 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
653
654 l_simulation_plan NUMBER;
655 l_simulation_plan_id NUMBER;
656 l_dummy VARCHAR2(1);
657
658 -- Define local cursors
659 CURSOR c_visit(x_id IN NUMBER)IS
660 SELECT SIMULATION_PLAN_ID FROM
661 AHL_VISITS_VL WHERE VISIT_ID = x_id;
662 /*
663 CURSOR c_sim_visit(x_id IN NUMBER) IS
664 SELECT 'x'
665 FROM ahl_simulation_plans_vl ASP
666 WHERE primary_plan_flag = 'Y'
667 AND EXISTS ( SELECT 1
668 FROM ahl_visits_b
669 WHERE visit_id = x_id
670 AND NVL(simulation_plan_id,-99) = ASP.simulation_plan_id);
671 */
672 BEGIN
673 IF (l_log_procedure >= l_log_current_level) THEN
674 fnd_log.string(l_log_procedure,
675 L_DEBUG_KEY ||'.begin',
676 'At the start of PL SQL procedure. Visit Id = ' || p_Visit_id);
677 END IF;
678 IF (p_visit_id IS NOT NULL) THEN
679 OPEN c_visit(p_visit_id);
680 FETCH c_visit INTO l_simulation_plan;
681 CLOSE c_visit;
682
683 IF (l_simulation_plan IS NOT NULL) THEN
684
685 IF (l_log_statement >= l_log_current_level)THEN
686 fnd_log.string(l_log_statement,
687 L_DEBUG_KEY,
688 'Simulation Id Check' || l_simulation_plan);
689 END IF;
690 SELECT SIMULATION_PLAN_ID INTO l_simulation_plan_id
691 FROM AHL_SIMULATION_PLANS_VL WHERE primary_plan_flag = 'Y';
692
693 IF l_simulation_plan_id = l_simulation_plan THEN
694 x_bln_flag := 'Y';
695 ELSE
696 x_bln_flag := 'N';
697 END IF;
698 END IF;
699 END IF;
700
701 /*
702 OPEN c_sim_visit(p_visit_id);
703 FETCH c_sim_visit INTO l_dummy;
704 IF c_sim_visit%FOUND THEN
705 x_bln_flag := 'Y';
706 ELSE
707 x_bln_flag := 'N';
708 END IF;
709 CLOSE c_sim_visit;
710 */
711 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
712
713 IF (l_log_procedure >= l_log_current_level) THEN
714 fnd_log.string(l_log_procedure,
715 L_DEBUG_KEY ||'.end',
716 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
717 'x_bln_flag = ' || x_bln_flag);
718 END IF;
719
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 x_return_status := Fnd_Api.G_RET_STS_ERROR;
723 x_error_msg_code:= 'AHL_LTP_SIMULATION_PLAN_NOT_EXISTS';
724 WHEN TOO_MANY_ROWS THEN
725 x_return_status := Fnd_Api.G_RET_STS_ERROR;
726 x_error_msg_code:= 'AHL_LTP_SIMULATION_PLAN_NOT_EXISTS';
727 WHEN OTHERS THEN
728 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
729 RAISE;
730 END Check_Visit_is_Simulated;
731
732 --------------------------------------------------------------------
733 -- PROCEDURE
734 -- Check_Visit_Task_Number_Or_Id
735 --
736 -- PURPOSE
737 -- Converts Visit Task Number to Visit Task Id
738 --------------------------------------------------------------------
739 PROCEDURE Check_Visit_Task_Number_Or_Id
740 (p_visit_task_id IN NUMBER,
741 p_visit_task_number IN NUMBER,
742 p_visit_id IN NUMBER,
743
744 x_visit_task_id OUT NOCOPY NUMBER,
745 x_return_status OUT NOCOPY VARCHAR2,
746 x_error_msg_code OUT NOCOPY VARCHAR2
747 )
748 IS
749 -- Define local variables
750 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Visit_Task_Number_Or_Id';
751 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
752
753 --mpothuku added status_code <> 'DELETED' clause to fix #206 on 03/30/05
754 BEGIN
755 IF (l_log_procedure >= l_log_current_level) THEN
756 fnd_log.string(l_log_procedure,
757 L_DEBUG_KEY ||'.begin',
758 'At the start of PL SQL procedure. Visit Task Id = ' || p_visit_task_id ||
759 ', Visit Task Number = ' || p_visit_task_number || ', Visit Id = ' || p_visit_id);
760 END IF;
761 IF (p_visit_task_id IS NOT NULL) THEN
762 SELECT Visit_Task_Id INTO x_visit_task_id
763 FROM AHL_VISIT_TASKS_B
764 WHERE Visit_Task_Id = p_visit_task_id AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
765 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
766 ELSIF (p_visit_task_number IS NOT NULL) THEN
767 SELECT Visit_Task_Id INTO x_visit_task_id
768 FROM AHL_VISIT_TASKS_B
769 WHERE Visit_Task_Number = p_visit_task_number AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
770 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
771 ELSE
772 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
773 Fnd_Message.set_name ('AHL', 'AHL_VWP_VISIT_TASKS_NOT_EXISTS');
774 Fnd_Msg_Pub.ADD;
775 END IF;
776
777 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
778 IF (l_log_procedure >= l_log_current_level) THEN
779 fnd_log.string(l_log_procedure,
780 L_DEBUG_KEY ||'.end',
781 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
782 'Visit Task Id = ' || x_visit_task_id);
783 END IF;
784 EXCEPTION
785 WHEN NO_DATA_FOUND THEN
786 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
787 x_error_msg_code:= 'AHL_VWP_VISIT_TASKS_NOT_EXISTS';
788 WHEN TOO_MANY_ROWS THEN
789 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
790 x_error_msg_code:= 'AHL_VWP_VISIT_TASKS_NOT_EXISTS';
791 WHEN OTHERS THEN
792 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
793 RAISE;
794 END Check_Visit_Task_Number_OR_ID;
795
796 --------------------------------------------------------------------
797 -- PROCEDURE
798 -- Check_Lookup_Name_Or_Id
799 --
800 -- PURPOSE
801 -- To derive the any of the lookup codes to its lookup values
802 --------------------------------------------------------------------
803 PROCEDURE Check_Lookup_Name_Or_Id
804 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
805 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
806 p_meaning IN FND_LOOKUPS.meaning%TYPE,
807 p_check_id_flag IN VARCHAR2,
808
809 x_lookup_code OUT NOCOPY VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2)
811 IS
812 -- Define local variables
813 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Lookup_Name_Or_Id';
814 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
815 BEGIN
816 IF (l_log_procedure >= l_log_current_level) THEN
817 fnd_log.string(l_log_procedure,
818 L_DEBUG_KEY ||'.begin',
819 'At the start of PL SQL procedure. p_lookup_type = ' || p_lookup_type ||
820 ', p_lookup_code = ' || p_lookup_code ||
821 ', p_meaning = ' || p_meaning ||
822 ', p_check_id_flag = ' || p_check_id_flag);
823 END IF;
824 IF (p_lookup_code IS NOT NULL) THEN
825 IF (p_check_id_flag = 'Y') THEN
826 SELECT Lookup_Code INTO x_lookup_code
827 FROM FND_LOOKUP_VALUES_VL
828 WHERE Lookup_Type = p_lookup_type
829 AND Lookup_Code = p_lookup_code
830 AND enabled_flag = 'Y' --sowsubra FP:Bug#5758829
831 AND SYSDATE BETWEEN nvl(start_date_active,sysdate) --Sowmya Bug#5715342
832 AND NVL(end_date_active,SYSDATE);
833 ELSE
834 x_lookup_code := p_lookup_code;
835 END IF;
836 ELSE
837 SELECT Lookup_Code INTO x_lookup_code
838 FROM FND_LOOKUP_VALUES_VL
839 WHERE Lookup_Type = p_lookup_type
840 AND Meaning = p_meaning
841 AND enabled_flag = 'Y' --sowsubra FP:Bug#5758829
842 AND SYSDATE BETWEEN nvl(start_date_active,sysdate) --sowsubra FP:Bug#5758829
843 AND NVL(end_date_active,SYSDATE);
844 END IF;
845
846 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
847
848 IF (l_log_procedure >= l_log_current_level) THEN
849 fnd_log.string(l_log_procedure,
850 L_DEBUG_KEY ||'.end',
851 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
852 '. x_lookup_code = ' || x_lookup_code);
853 END IF;
854
855 EXCEPTION
856 WHEN NO_DATA_FOUND THEN
857 x_return_status := Fnd_Api.G_RET_STS_ERROR;
858 WHEN TOO_MANY_ROWS THEN
859 x_return_status := Fnd_Api.G_RET_STS_ERROR;
860 WHEN OTHERS THEN
861 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
862 RAISE;
863 END Check_Lookup_Name_Or_Id;
864
865
866 --------------------------------------------------------------------
867 -- PROCEDURE
868 -- Check_Proj_Responsibility
869 --
870 -- PURPOSE
871 -- While integrating with projects,
872 -- VWP need to check for valid project resposibilities
873 --------------------------------------------------------------------
874 PROCEDURE Check_Proj_Responsibility
875 ( x_check_project OUT NOCOPY VARCHAR2,
876 x_return_status OUT NOCOPY VARCHAR2)
877 IS
878 -- Define local variables
879 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Proj_Responsibility';
880 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
881 l_msg_count NUMBER;
882 l_responsibility_id NUMBER;
883 l_return_status VARCHAR2(1);
884 l_msg_data VARCHAR2(2000);
885 G_EXC_ERROR EXCEPTION;
886
887 -- Define local cursors
888 -- To find the responsibiltiy_id for Project Billing Superuser
889 -- Not using project superuser resposibility as the menu associated is changed in 11.5.9
890 CURSOR c_fnd_response IS
891 SELECT RESPONSIBILITY_ID
892 FROM FND_RESPONSIBILITY_VL
893 WHERE RESPONSIBILITY_KEY LIKE 'PROJECT_BILLING_SUPER_USER';
894
895 -- To find the responsibiltiy_id for Project Superuser
896 /*
897 CURSOR c_fnd_user_resp(x_resp_id IN NUMBER) IS
898 SELECT RESPONSIBILITY_ID
899 FROM FND_USER_RESP_GROUPS
900 WHERE USER_ID = Fnd_Global.USER_ID AND RESPONSIBILITY_ID = x_resp_id;
901 */
902 BEGIN
903
904 IF (l_log_procedure >= l_log_current_level) THEN
905 fnd_log.string(l_log_procedure,
906 L_DEBUG_KEY ||'.begin',
907 'At the start of PL SQL procedure.');
908 END IF;
909 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
910 -- Always return x_check_project = 'Y' as we no longer need to check for Project Billing
911 -- Superuser Resp. Part of ADS bug fix 4357001.
912 x_check_project := 'Y';
913
914 /* -- Commented out hardcoding of Responsibility. AMG's function security functions will be
915 -- included into ahlmenu instead. Part of ADS bug fix 4357001.
916 OPEN c_fnd_response;
917 FETCH c_fnd_response INTO l_responsibility_id;
918
919 IF (l_log_procedure >= l_log_current_level) THEN
920 fnd_log.string(l_log_procedure,L_DEBUG_KEY, 'Responsiblity_Id from c_fnd_response = ' || l_responsibility_id);
921 END IF;
922
923 IF c_fnd_response%NOTFOUND THEN
924 x_return_status := Fnd_Api.g_ret_sts_error;
925 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
926 Fnd_Message.set_name ('AHL', 'AHL_VWP_PROJ_NO_SUPERUSER');
927 Fnd_Msg_Pub.ADD;
928 CLOSE c_fnd_response;
929 RAISE G_EXC_ERROR;
930 END IF;
931 END IF;
932 CLOSE c_fnd_response;
933
934 OPEN c_fnd_user_resp(l_responsibility_id);
935 FETCH c_fnd_user_resp INTO l_responsibility_id;
936
937 IF (l_log_procedure >= l_log_current_level) THEN
938 fnd_log.string(l_log_procedure,L_DEBUG_KEY, 'Responsiblity_Id from c_fnd_user_resp = ' || l_responsibility_id);
939 END IF;
940
941 IF c_fnd_user_resp%NOTFOUND THEN
942 x_return_status := Fnd_Api.g_ret_sts_error;
943 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
944 Fnd_Message.set_name ('AHL', 'AHL_VWP_PROJ_NO_USER_GROUPS');
945 Fnd_Msg_Pub.ADD;
946 END IF;
947 END IF;
948 CLOSE c_fnd_user_resp;
949
950 IF x_return_status =Fnd_Api.G_RET_STS_SUCCESS THEN
951
952 IF l_responsibility_id = l_responsibility_id THEN
953 x_check_project := 'Y';
954
955 -- Project Billing Super user
956 PA_INTERFACE_UTILS_PUB.Set_Global_Info
957 ( p_api_version_number => 1.0,
958 p_responsibility_id => l_responsibility_id,
959 p_user_id => Fnd_Global.USER_ID,
960 p_msg_count => l_msg_count,
961 p_msg_data => l_msg_data,
962 */
963
964 /* Fix for Bug 4086726 on Dec 23, 2004 by JR.
965 * Commenting out the calling mode param.
966 * Need to add if required, along with build dependency and a one-off
967 * fix from PA.
968 p_return_status => l_return_status ,
969 p_calling_mode => 'PUBLISH');
970 p_return_status => l_return_status);
971 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
972 RAISE G_EXC_ERROR;
973 END IF;
974
975 ELSE
976 x_check_project := 'N';
977 x_return_status := Fnd_Api.g_ret_sts_error;
978
979 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
980 Fnd_Message.set_name ('AHL', 'AHL_VWP_PROJ_NO_RESPONSIBILITY');
981 Fnd_Msg_Pub.ADD;
982 END IF;
983 END IF;
984 END IF;
985 */
986
987 -- jaramana July 25, 2005
988 /* From Majid Ansari's email dated June 8, 2005:
989 * Before calling any AMG api, its mandatory to call PA_INTERFACE_UTILS_PUB.Set_Global_Info.
990 * This has been mentioned in the AMG doc. You cannot get rid of this call.
991 * None of the APIs will be usable.
992 */
993 -- So, reintroduced the call to PA_INTERFACE_UTILS_PUB.Set_Global_Info.
994 -- However, passing the current responsibilty id instead of the hardcoded
995 -- Project Billing Superuser Responsibility as was done before the ADS Bug 4357001 Fix.
996 PA_INTERFACE_UTILS_PUB.Set_Global_Info( p_api_version_number => 1.0,
997 p_responsibility_id => Fnd_Global.RESP_ID,
998 p_resp_appl_id => Fnd_Global.RESP_APPL_ID,
999 p_user_id => Fnd_Global.USER_ID,
1000 p_operating_unit_id => mo_global.get_current_org_id, -- Yazhou added for MOAC changes on 05Oct2005
1001 p_msg_count => l_msg_count,
1002 p_msg_data => l_msg_data,
1003 p_return_status => x_return_status);
1004 IF (fnd_log.level_event >= l_log_current_level) THEN
1005 fnd_log.string(fnd_log.level_event,
1006 L_DEBUG_KEY,
1007 'After calling PA_INTERFACE_UTILS_PUB.Set_Global_Info. Return Status = ' || x_return_status);
1008 END IF;
1009 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1010 IF (l_log_statement >= l_log_current_level) THEN
1011 fnd_log.string(l_log_statement,
1012 L_DEBUG_KEY,
1013 'Errors from PA_INTERFACE_UTILS_PUB.Set_Global_Info. Message count: ' ||
1014 l_msg_count || ', message data: ' || l_msg_data);
1015 END IF;
1016 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1017 RAISE FND_API.G_EXC_ERROR;
1018 ELSE
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020 END IF;
1021 END IF;
1022 -- End Changes made by jaramana on July 25, 2005
1023
1024 IF (l_log_procedure >= l_log_current_level) THEN
1025 fnd_log.string(l_log_procedure,
1026 L_DEBUG_KEY ||'.end',
1027 'At the end of PL SQL procedure. Return Status = ' ||
1028 x_return_status || '. x_check_project = ' || x_check_project);
1029 END IF;
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1034 WHEN TOO_MANY_ROWS THEN
1035 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1036 WHEN OTHERS THEN
1037 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1038 RAISE;
1039 END Check_Proj_Responsibility;
1040
1041 --------------------------------------------------------------------
1042 -- PROCEDURE
1043 -- Get_Serial_Item_by_Unit
1044 --
1045 -- PURPOSE
1046 -- To retrieve Inventory Item Id, Item Org Id and Instance Id from Unit Name
1047 --------------------------------------------------------------------
1048 PROCEDURE Get_Serial_Item_by_Unit
1049 ( p_unit_name IN VARCHAR2,
1050 x_instance_id OUT NOCOPY NUMBER,
1051 x_item_id OUT NOCOPY NUMBER,
1052 x_item_org_id OUT NOCOPY NUMBER,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_error_msg_code OUT NOCOPY VARCHAR2)
1055 IS
1056 -- Define local variables
1057 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Serial_Item_by_Unit';
1058 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1059
1060 -- Define local cursors
1061 CURSOR c_serial (p_unit_name IN VARCHAR2) IS
1062 SELECT csi_item_instance_id
1063 FROM ahl_unit_config_headers
1064 WHERE name = p_unit_name AND unit_config_status_code = 'COMPLETE'
1065 AND (active_end_date is null or active_end_date > sysdate);
1066
1067 BEGIN
1068 IF (l_log_procedure >= l_log_current_level) THEN
1069 fnd_log.string(l_log_procedure,
1070 L_DEBUG_KEY ||'.begin',
1071 'At the start of PL SQL procedure. Unit Name = ' || p_unit_name);
1072 END IF;
1073 IF (p_unit_name IS NOT NULL) THEN
1074 OPEN c_serial(p_unit_name);
1075 FETCH c_serial INTO x_instance_id;
1076 CLOSE c_serial;
1077
1078 IF (l_log_statement >= l_log_current_level) THEN
1079 fnd_log.string(l_log_statement,
1080 L_DEBUG_KEY,
1081 'Instance Id = ' || x_instance_id);
1082 END IF;
1083
1084 IF x_instance_id IS NOT NULL THEN
1085 SELECT Inventory_Item_Id, Inv_Master_Organization_Id
1086 INTO x_Item_Id, x_Item_Org_Id
1087 FROM CSI_ITEM_INSTANCES
1088 WHERE Instance_Id = x_instance_id;
1089 ELSE
1090 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERIAL_NOT_EXISTS');
1091 Fnd_Msg_Pub.ADD;
1092 RAISE Fnd_Api.G_EXC_ERROR;
1093 END IF;
1094 END IF;
1095
1096 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1097 IF (l_log_procedure >= l_log_current_level) THEN
1098 fnd_log.string(l_log_procedure,
1099 L_DEBUG_KEY ||'.end',
1100 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
1101 '. Item Id = ' || x_item_id || ' Item Org Id' || x_item_org_id);
1102 END IF;
1103 EXCEPTION
1104 WHEN NO_DATA_FOUND THEN
1105 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1106 WHEN TOO_MANY_ROWS THEN
1107 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1108 WHEN OTHERS THEN
1109 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1110 RAISE;
1111 END Get_Serial_Item_by_Unit;
1112
1113 --------------------------------------------------------------------
1114 -- PROCEDURE
1115 -- Insert_Tasks
1116 --
1117 -- PURPOSE
1118 -- To call when inserting planned/unplanned tasks
1119 --------------------------------------------------------------------
1120 PROCEDURE Insert_Tasks (
1121 p_visit_id IN NUMBER,
1122 p_unit_id IN NUMBER,
1123 p_serial_id IN NUMBER,
1124 p_service_id IN NUMBER,
1125 p_dept_id IN NUMBER,
1126 p_item_id IN NUMBER,
1127 p_item_org_id IN NUMBER,
1128 p_mr_id IN NUMBER,
1129 p_mr_route_id IN NUMBER,
1130 p_parent_id IN NUMBER,
1131 p_flag IN VARCHAR2,
1132 p_stage_id IN NUMBER := NULL,
1133 p_quantity IN NUMBER := NULL, -- Added by rnahata for Issue 105
1134 x_task_id OUT NOCOPY NUMBER,
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 x_msg_count OUT NOCOPY NUMBER,
1137 x_msg_data OUT NOCOPY VARCHAR2
1138 )
1139 IS
1140 -- Define local variables
1141 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Tasks';
1142 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1143
1144 -- Added by sjayacha for Servie Request Integration
1145
1146 CURSOR c_mr_title(c_mr_id IN NUMBER) IS
1147 SELECT title, description /*Bug 5758813- Fetch the description of the MR*/
1148 FROM ahl_mr_headers_vl
1149 WHERE mr_header_id = c_mr_id;
1150
1151 CURSOR c_unit_eff_title(c_unit_eff_id IN NUMBER) IS
1152 SELECT substr(title,1,80), description /*Bug 5758813 - Fetch the description.*/
1153 FROM ahl_unit_effectivities_v
1154 WHERE unit_effectivity_id = c_unit_eff_id;
1155
1156 /*Bug 5758813 - rnahata - Route title and route remarks should be passed
1157 to visit as visit task name and visit task description*/
1158 CURSOR c_route_title(c_mr_route_id IN NUMBER) IS
1159 SELECT substr(ar.title,1,80), ar.remarks
1160 FROM ahl_routes_vl ar, ahl_mr_routes mrr
1161 WHERE mrr.mr_route_id = c_mr_route_id
1162 AND mrr.route_id = ar.route_id;
1163
1164 -- Local variables defined for the procedure
1165 l_msg_data VARCHAR2(2000);
1166 l_name VARCHAR2(80);
1167 l_rowid VARCHAR2(30);
1168 l_type VARCHAR2(30);
1169 l_return_status VARCHAR2(1);
1170 l_template_flag VARCHAR2(1);
1171
1172 l_msg_count NUMBER;
1173 l_task_number NUMBER;
1174 l_task_id NUMBER;
1175 l_item_id NUMBER;
1176 l_item_org_id NUMBER;
1177 l_serial_id NUMBER;
1178 l_mr_id NUMBER;
1179 l_mr_route_id NUMBER;
1180 l_description ahl_routes_vl.remarks%TYPE; --Bug 5758813
1181
1182 BEGIN
1183 IF (l_log_procedure >= l_log_current_level) THEN
1184 fnd_log.string(l_log_procedure,
1185 L_DEBUG_KEY ||'.begin',
1186 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
1187 ', parent id = ' || p_parent_id || ', p_serial_id = ' || p_serial_id ||
1188 ', p_item_id = ' || p_item_id || ', p_mr_id = ' || p_mr_id ||
1189 ', p_mr_route_id = ' || p_mr_route_id);
1190 END IF;
1191 IF p_visit_id IS NOT NULL THEN
1192 IF p_unit_id IS NOT NULL THEN
1193 l_type := 'PLANNED';
1194 ELSE
1195 l_type := 'UNPLANNED';
1196 IF p_serial_id IS NULL THEN
1197 x_return_status := Fnd_Api.g_ret_sts_error;
1198 IF (l_log_statement >= l_log_current_level) THEN
1199 fnd_log.string(l_log_statement,
1200 L_DEBUG_KEY,
1201 'Error in Insert_Tasks. Serial Id missing.');
1202 END IF;
1203 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1204 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERIAL_MISSING');
1205 Fnd_Msg_Pub.ADD;
1206 END IF;
1207 END IF;
1208 END IF; -- End of p_unit_id check
1209
1210 IF p_flag = 'Y' THEN
1211 l_type := 'SUMMARY';
1212 IF p_mr_id IS NOT NULL THEN
1213 -- MR Summary Task
1214 /*Bug 5758813 - rnahata*/
1215 OPEN c_mr_title(p_mr_id);
1216 FETCH c_mr_title INTO l_name, l_description;
1217 CLOSE c_mr_title;
1218 IF (l_log_statement >= l_log_current_level) THEN
1219 fnd_log.string(l_log_statement,
1220 L_DEBUG_KEY,
1221 'Summary Task. MR Title = ' || l_name || ', MR Description = ' || l_description);
1222 END IF;
1223 ELSIF p_unit_id IS NOT NULL THEN
1224 -- Added by sjayacha for Servie Request Integration
1225 /*Bug 5758813 - rnahata*/
1226 OPEN c_unit_eff_title(p_unit_id);
1227 FETCH c_unit_eff_title INTO l_name, l_description;
1228 CLOSE c_unit_eff_title;
1229 IF (l_log_statement >= l_log_current_level) THEN
1230 fnd_log.string(l_log_statement,
1231 L_DEBUG_KEY,
1232 'Summary Task. Unit Eff Title = ' || l_name || ', Unit Eff Description = ' || l_description);
1233 END IF;
1234 END IF;
1235 ELSE
1236 -- Not a Summary Task
1237 IF p_mr_route_id IS NOT NULL THEN
1238 /*Bug 5758813 - rnahata*/
1239 OPEN c_route_title(p_mr_route_id);
1240 FETCH c_route_title INTO l_name, l_description;
1241 CLOSE c_route_title;
1242 IF (l_log_statement >= l_log_current_level) THEN
1243 fnd_log.string(l_log_statement,
1244 L_DEBUG_KEY,
1245 'Task. Route Title = ' || l_name || ', Route Description = ' || l_description);
1246 END IF;
1247 ELSIF p_unit_id IS NOT NULL THEN
1248 -- Added by sjayacha for Service Request Integration
1249 /*Bug 5758813 - rnahata*/
1250 OPEN c_unit_eff_title(p_unit_id);
1251 FETCH c_unit_eff_title INTO l_name, l_description;
1252 CLOSE c_unit_eff_title;
1253 IF (l_log_statement >= l_log_current_level) THEN
1254 fnd_log.string(l_log_statement,
1255 L_DEBUG_KEY,
1256 'Task. Unit Eff Title = ' || l_name || ', Unit Eff Description = ' || l_description);
1257 END IF;
1258 END IF;
1259 END IF; -- Summary Task or Not
1260
1261 -- Check for the Visit Task ID and Number.
1262 l_task_ID := Get_Visit_Task_Id();
1263 l_task_number := Get_Visit_Task_Number(p_visit_id);
1264 IF (l_log_statement >= l_log_current_level) THEN
1265 fnd_log.string(l_log_statement,
1266 L_DEBUG_KEY,
1267 'Task ID = ' || l_task_id || ', Task Number = ' || l_task_number);
1268 END IF;
1269
1270 l_msg_count := Fnd_Msg_Pub.count_msg;
1271 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1272 x_msg_count := l_msg_count;
1273 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1274 RAISE Fnd_Api.G_EXC_ERROR;
1275 END IF;
1276
1277 IF (l_log_statement >= l_log_current_level) THEN
1278 fnd_log.string(l_log_statement,
1279 L_DEBUG_KEY,
1280 'Before calling AHL_VISIT_TASKS_PKG.Insert_Row.');
1281 END IF;
1282
1283 -- Insert a record in AHL_VISIT_TASKS base and translational tables
1284 Ahl_Visit_Tasks_Pkg.Insert_Row (
1285 X_ROWID => l_rowid,
1286 X_VISIT_TASK_ID => l_task_ID ,
1287 X_VISIT_TASK_NUMBER => l_task_number,
1288 X_OBJECT_VERSION_NUMBER => 1,
1289 X_VISIT_ID => p_visit_id,
1290 X_PROJECT_TASK_ID => NULL,
1291 X_COST_PARENT_ID => p_parent_id,
1292 X_MR_ROUTE_ID => p_mr_route_id,
1293 X_MR_ID => p_mr_id,
1294 X_DURATION => NULL,
1295 X_UNIT_EFFECTIVITY_ID => p_unit_id,
1296 X_START_FROM_HOUR => NULL,
1297 X_INVENTORY_ITEM_ID => p_item_id,
1298 X_ITEM_ORGANIZATION_ID => p_item_org_id,
1299 X_INSTANCE_ID => p_serial_id,
1300 X_PRIMARY_VISIT_TASK_ID => NULL,
1301 X_ORIGINATING_TASK_ID => p_parent_id,
1302 X_SERVICE_REQUEST_ID => p_service_id,
1303 X_TASK_TYPE_CODE => l_type,
1304 X_DEPARTMENT_ID => p_dept_id,
1305 X_SUMMARY_TASK_FLAG => 'N',
1306 X_PRICE_LIST_ID => NULL,
1307 X_STATUS_CODE => 'PLANNING',
1308 X_ESTIMATED_PRICE => NULL,
1309 X_ACTUAL_PRICE => NULL,
1310 X_ACTUAL_COST => NULL,
1311 X_STAGE_ID => p_stage_id,
1312 -- Added cxcheng POST11510--------------
1313 X_START_DATE_TIME => NULL,
1314 X_END_DATE_TIME => NULL,
1315 X_ATTRIBUTE_CATEGORY => NULL,
1316 X_ATTRIBUTE1 => NULL,
1317 X_ATTRIBUTE2 => NULL,
1318 X_ATTRIBUTE3 => NULL,
1319 X_ATTRIBUTE4 => NULL,
1320 X_ATTRIBUTE5 => NULL,
1321 X_ATTRIBUTE6 => NULL,
1322 X_ATTRIBUTE7 => NULL,
1323 X_ATTRIBUTE8 => NULL,
1324 X_ATTRIBUTE9 => NULL,
1325 X_ATTRIBUTE10 => NULL,
1326 X_ATTRIBUTE11 => NULL,
1327 X_ATTRIBUTE12 => NULL,
1328 X_ATTRIBUTE13 => NULL,
1329 X_ATTRIBUTE14 => NULL,
1330 X_ATTRIBUTE15 => NULL,
1331 X_VISIT_TASK_NAME => l_name, --Bug 5758813
1332 X_DESCRIPTION => l_description, --Bug 5758813
1333 -- Added by rnahata for Issue 105
1334 X_QUANTITY => p_quantity,
1335 X_CREATION_DATE => SYSDATE,
1336 X_CREATED_BY => Fnd_Global.USER_ID,
1337 X_LAST_UPDATE_DATE => SYSDATE,
1338 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1339 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1340
1341 IF (l_log_statement >= l_log_current_level) THEN
1342 fnd_log.string(l_log_statement,
1343 L_DEBUG_KEY,
1344 'After calling AHL_VISIT_TASKS_PKG.Insert_Row.');
1345 END IF;
1346
1347 x_task_id := l_task_id;
1348
1349 -- Added cxcheng POST11510--------------
1350 --Now adjust the times derivation for task
1351 IF (l_log_statement >= l_log_current_level) THEN
1352 fnd_log.string(l_log_statement,
1353 L_DEBUG_KEY,
1354 'Before calling AHL_VWP_TIMES_PVT.Adjust_Task_Times.');
1355 END IF;
1356
1357 AHL_VWP_TIMES_PVT.Adjust_Task_Times
1358 (p_api_version => 1.0,
1359 p_init_msg_list => Fnd_Api.G_FALSE,
1360 p_commit => Fnd_Api.G_FALSE,
1361 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1362 x_return_status => l_return_status,
1363 x_msg_count => l_msg_count,
1364 x_msg_data => l_msg_data,
1365 p_task_id => l_task_id);
1366
1367 IF (l_log_statement >= l_log_current_level) THEN
1368 fnd_log.string(l_log_statement,
1369 L_DEBUG_KEY,
1370 'After calling AHL_VWP_TIMES_PVT.Adjust_Task_Times. Return Status = ' ||
1371 l_return_status);
1372 END IF;
1373
1374 l_msg_count := Fnd_Msg_Pub.count_msg;
1375 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1376 IF (l_log_statement >= l_log_current_level) THEN
1377 fnd_log.string(l_log_statement,
1378 L_DEBUG_KEY,
1379 'Errors from AHL_VWP_TIMES_PVT.Adjust_Task_Times. Message count: ' ||
1380 l_msg_count || ', message data: ' || l_msg_data);
1381 END IF;
1382 x_msg_count := l_msg_count;
1383 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1384 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1385 END IF;
1386
1387 x_return_status := Fnd_Api.g_ret_sts_success;
1388 IF (l_log_procedure >= l_log_current_level) THEN
1389 fnd_log.string(l_log_procedure,
1390 L_DEBUG_KEY || '.end',
1391 'Return Status = ' || x_return_status || '. Task Id = ' || x_task_id);
1392 END IF;
1393 END IF; -- p_visit_id IS NOT NULL
1394 END Insert_Tasks;
1395
1396 --------------------------------------------------------------------
1397 -- Define local functions body --
1398 --------------------------------------------------------------------
1399
1400
1401 --------------------------------------------------------------------
1402 -- FUNCTION
1403 -- Get_Cost_Originating_Id
1404 --
1405 -- PURPOSE
1406 -- To seek Cost Parent Id and Originating Task Id for the
1407 -- planned/unplanned task which has to be created.
1408 --------------------------------------------------------------------
1409 FUNCTION Get_Cost_Originating_Id (p_mr_main_id IN NUMBER, p_mr_header_id IN NUMBER)
1410 RETURN NUMBER
1411 IS
1412 -- Define local variables
1413 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Cost_Originating_Id';
1414 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1415 l_flag1 VARCHAR2(1);
1416 x_parent_id NUMBER;
1417 y NUMBER := 0;
1418 i NUMBER := 0;
1419
1420 -- Define local cursors
1421 -- To find all relationships between various MR_HEADER_IDs
1422 -- under main MR_HEADER_IDs which was retreived on basis of the Unit Effectivity
1423 CURSOR c_relationship(x_MR_id IN NUMBER) IS
1424 SELECT MR_HEADER_ID, RELATED_MR_HEADER_ID
1425 FROM AHL_MR_RELATIONSHIPS
1426 START WITH MR_HEADER_ID = (
1427 SELECT MR_HEADER_ID
1428 FROM AHL_MR_HEADERS_APP_V
1429 WHERE MR_HEADER_ID = x_MR_id)
1430 CONNECT BY PRIOR RELATED_MR_HEADER_ID = MR_HEADER_ID;
1431 relation_rec c_relationship%ROWTYPE;
1432
1433 -- Define local record type for storing MR_Id and Related MR_Header_Id
1434 TYPE Task_Rel_Type IS RECORD
1435 (MR_HEADER_ID NUMBER,
1436 RELATED_MR_HEADER_ID NUMBER);
1437
1438 -- Define local table type for storing MR_Id and Related MR_Header_Id
1439 TYPE relation_tbl IS TABLE OF Task_Rel_Type
1440 INDEX BY BINARY_INTEGER;
1441
1442 relation_rec_tbl relation_tbl;
1443
1444 BEGIN
1445 IF (l_log_procedure >= l_log_current_level) THEN
1446 fnd_log.string(l_log_procedure,
1447 L_DEBUG_KEY ||'.begin',
1448 'At the start of PL SQL function. MAIN HEADER = ' || p_MR_main_id ||
1449 'CURRENT MR HEADER = ' || p_MR_HEADER_id);
1450 END IF;
1451 -- To store all related rows of relationship with main mr_header id in a form of a table datatype
1452 OPEN c_relationship (p_mr_main_id);
1453 LOOP
1454 FETCH c_relationship INTO relation_rec;
1455 EXIT WHEN c_relationship%NOTFOUND;
1456 relation_rec_tbl(i).MR_HEADER_ID:=relation_rec.MR_HEADER_ID;
1457 relation_rec_tbl(i).RELATED_MR_HEADER_ID:=relation_rec.RELATED_MR_HEADER_ID;
1458 i:=i+1;
1459 END LOOP;
1460 CLOSE c_relationship;
1461
1462 -- Find out parent of MR_HEADER_ID as cost and parent ID
1463 IF relation_rec_tbl.COUNT > 0 THEN
1464 y := relation_rec_tbl.FIRST;
1465 LOOP
1466 IF relation_rec_tbl(y).RELATED_MR_HEADER_ID = p_MR_HEADER_id THEN
1467 IF relation_rec_tbl(y).RELATED_MR_HEADER_ID = p_mr_main_id THEN
1468 x_parent_id := NULL;
1469 l_flag1 := 'Y';
1470 EXIT WHEN l_flag1 = 'Y';
1471 ELSE
1472 x_parent_id := relation_rec_tbl(y).MR_HEADER_ID;
1473 l_flag1 := 'Y';
1474 EXIT WHEN l_flag1 = 'Y';
1475 END IF;
1476 END IF;
1477 EXIT WHEN y = relation_rec_tbl.LAST ;
1478 y :=relation_rec_tbl.NEXT(y);
1479 END LOOP;
1480 END IF;
1481
1482 IF (l_log_procedure >= l_log_current_level) THEN
1483 fnd_log.string(l_log_procedure,
1484 L_DEBUG_KEY ||'.end',
1485 'At the end of PL SQL function. Parent_Id' || x_parent_id);
1486 END IF;
1487 RETURN x_parent_id;
1488 END Get_Cost_Originating_Id;
1489
1490 --------------------------------------------------------------------
1491 -- FUNCTION
1492 -- Get_Visit_Task_Id
1493 --
1494 -- PURPOSE
1495 -- To retrieve visit task id from the sequence
1496 --------------------------------------------------------------------
1497 FUNCTION Get_Visit_Task_Id
1498 RETURN NUMBER
1499 IS
1500 -- Define local cursors
1501 CURSOR c_seq_t IS
1502 SELECT Ahl_Visit_Tasks_B_S.NEXTVAL
1503 FROM dual;
1504
1505 CURSOR c_id_exists_t (c_id IN NUMBER) IS
1506 SELECT 1
1507 FROM Ahl_Visit_Tasks_VL
1508 WHERE Visit_Task_id = c_id;
1509
1510 -- Define local variables
1511 x_visit_task_id NUMBER;
1512 l_dummy NUMBER;
1513 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Id';
1514 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1515
1516 BEGIN
1517 IF (l_log_procedure >= l_log_current_level) THEN
1518 fnd_log.string(l_log_procedure,
1519 L_DEBUG_KEY ||'.begin',
1520 'At the start of PL SQL function.');
1521 END IF;
1522
1523 -- If the ID is not passed into the API, then grab a value from the sequence.
1524 OPEN c_seq_t;
1525 FETCH c_seq_t INTO x_visit_task_id;
1526 CLOSE c_seq_t;
1527
1528 -- Check to be sure that the sequence does not exist.
1529 OPEN c_id_exists_t (x_visit_task_id);
1530 FETCH c_id_exists_t INTO l_dummy;
1531 IF c_id_exists_t%FOUND THEN
1532 x_visit_task_id := Get_Visit_Task_Id();
1533 END IF;
1534 CLOSE c_id_exists_t;
1535
1536 IF (l_log_procedure >= l_log_current_level) THEN
1537 fnd_log.string(l_log_procedure,
1538 L_DEBUG_KEY ||'.end',
1539 'At the end of PL SQL function. Visit Task Id = ' || x_visit_task_id);
1540 END IF;
1541 RETURN x_visit_task_id;
1542 END Get_Visit_Task_Id;
1543
1544 --------------------------------------------------------------------
1545 -- FUNCTION
1546 -- Get_Visit_Task_Number
1547 --
1548 -- PURPOSE
1549 -- To retrieve visit task's task number with maximum plus one criteria
1550 --------------------------------------------------------------------
1551
1552 FUNCTION Get_Visit_Task_Number(p_visit_id IN NUMBER)
1553 RETURN NUMBER
1554 IS
1555 -- To find out the maximum task number value in the visit
1556 CURSOR c_task_number IS
1557 SELECT MAX(visit_task_number)
1558 FROM Ahl_Visit_Tasks_B
1559 WHERE Visit_Id = p_visit_id;
1560
1561 x_Visit_Task_Number NUMBER;
1562 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Number';
1563 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1564
1565 BEGIN
1566 IF (l_log_procedure >= l_log_current_level) THEN
1567 fnd_log.string(l_log_procedure,
1568 L_DEBUG_KEY ||'.begin',
1569 'At the start of PL SQL function. Visit Id = ' || p_visit_id);
1570 END IF;
1571
1572 -- Check for Visit Number
1573 OPEN c_Task_Number;
1574 FETCH c_Task_Number INTO x_Visit_Task_Number;
1575 CLOSE c_Task_Number;
1576 IF x_Visit_Task_Number IS NOT NULL THEN
1577 x_Visit_Task_Number := x_Visit_Task_Number + 1;
1578 ELSE
1579 x_Visit_Task_Number := 1;
1580 END IF;
1581
1582 IF (l_log_procedure >= l_log_current_level) THEN
1583 fnd_log.string(l_log_procedure,
1584 L_DEBUG_KEY ||'.end',
1585 'At the end of PL SQL function. Visit Task Number = ' || x_Visit_Task_Number);
1586 END IF;
1587 RETURN x_Visit_Task_Number;
1588 END Get_Visit_Task_Number;
1589
1590 --------------------------------------------------------------------
1591 -- PROCEDURE
1592 -- Tech_Dependency
1593 --
1594 --
1595 --------------------------------------------------------------------
1596 PROCEDURE Tech_Dependency (
1597 p_visit_id IN NUMBER,
1598 p_task_type IN VARCHAR2,
1599 p_MR_Serial_Tbl IN MR_Serial_Tbl_Type,
1600 x_return_status OUT NOCOPY VARCHAR2)
1601 IS
1602 -- Define local variables
1603 L_API_NAME CONSTANT VARCHAR2(30) := 'Tech_Dependency';
1604 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1605 l_task_rec Task_Rec_Type;
1606 l_return_status VARCHAR2(1);
1607 l_flag VARCHAR2(1);
1608 l_tsk_flag VARCHAR2(1);
1609 l_serial_id NUMBER;
1610 l_count NUMBER;
1611 l_task_link_id NUMBER;
1612 l_child_task_id NUMBER;
1613 l_parent_task_id NUMBER;
1614 l_route_id NUMBER;
1615 y NUMBER;
1616
1617 -- Table type for storing Task Ids record type
1618 TYPE Task_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1619 l_parent_Task_Tbl Task_Tbl_Type;
1620 l_child_Task_Tbl Task_Tbl_Type;
1621
1622 -- Define local variables
1623 -- To find out the count for the relationship with MR Route Id
1624 CURSOR c_route_seq_ct (x_route_id IN NUMBER) IS
1625 SELECT COUNT(*) FROM AHL_MR_ROUTE_SEQUENCES_APP_V
1626 WHERE MR_ROUTE_ID = x_route_id;
1627
1628 -- To find out the the relationship between MR Route Id and Related MR Route Id
1629 CURSOR c_route_seq (x_route_id IN NUMBER) IS
1630 SELECT MR_ROUTE_ID, RELATED_MR_ROUTE_ID FROM AHL_MR_ROUTE_SEQUENCES_APP_V
1631 WHERE MR_ROUTE_ID = x_route_id;
1632 c_route_seq_rec c_route_seq%ROWTYPE;
1633
1634 -- To find out the tasks with tasktype code as planned for visits
1635 CURSOR c_route_task (x_route_id IN NUMBER, x_serial_id IN NUMBER,
1636 x_id IN NUMBER, x_type IN VARCHAR2) IS
1637 SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_VL
1638 WHERE MR_ROUTE_ID = x_route_id AND INSTANCE_ID = x_serial_id
1639 AND VISIT_ID = x_id AND TASK_TYPE_CODE = x_type
1640 AND nvl(STATUS_CODE,'x') <> 'DELETED';
1641
1642 -- To find MR_ROUTE_ID for the particular MR_HEADER_ID
1643 CURSOR c_MR_route (x_mr_id IN NUMBER) IS
1644 SELECT T1.MR_ROUTE_ID
1645 FROM AHL_MR_ROUTES_V T1,
1646 AHL_ROUTES_B T2
1647 WHERE T1.MR_HEADER_ID = x_mr_id
1648 AND T1.ROUTE_ID = T2.ROUTE_ID
1649 AND T2.REVISION_STATUS_CODE = 'COMPLETE'
1650 -- Added as of Bug# 3562914
1651 -- By shbhanda 04/22/2004
1652 AND T1.ROUTE_REVISION_NUMBER
1653 IN ( SELECT MAX(T3.ROUTE_REVISION_NUMBER)
1654 FROM AHL_MR_ROUTES_V T3
1655 WHERE T3.MR_HEADER_ID = x_mr_id
1656 AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
1657 GROUP BY T3.ROUTE_NUMBER
1658 );
1659 BEGIN
1660 ---------------------------Start of Body-------------------------------------
1661 IF (l_log_procedure >= l_log_current_level) THEN
1662 fnd_log.string(l_log_procedure,
1663 L_DEBUG_KEY ||'.begin',
1664 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
1665 ', Task Type = ' || p_task_type || ', MR count = ' || p_MR_Serial_tbl.count);
1666 END IF;
1667
1668 IF p_MR_Serial_tbl.count > 0 THEN
1669 y := p_MR_Serial_tbl.FIRST;
1670 LOOP
1671 IF (l_log_statement >= l_log_current_level)THEN
1672 fnd_log.string(l_log_statement,
1673 L_DEBUG_KEY,
1674 'MR ID = ' || p_MR_Serial_tbl(y).MR_ID);
1675 END IF;
1676
1677 -- Cursor to find all MR routes under the current MR
1678 OPEN c_MR_route (p_MR_Serial_tbl(y).MR_ID);
1679 LOOP
1680 FETCH c_MR_route INTO l_route_id;
1681 EXIT WHEN c_MR_route%NOTFOUND;
1682
1683 -- Cursor to find the count of number of MR routes
1684 -- which have parent-child relationship with the current MR Route
1685 OPEN c_route_seq_ct (l_route_id);
1686 FETCH c_route_seq_ct INTO l_count;
1687 CLOSE c_route_seq_ct;
1688
1689 IF (l_log_statement >= l_log_current_level) THEN
1690 fnd_log.string(l_log_statement,
1691 L_DEBUG_KEY,
1692 'Route sequence count = ' || l_count);
1693 END IF;
1694
1695 IF l_count > 0 THEN
1696
1697 -- To know parent-child MR Routes in AHL_MR_ROUTE_SEQUENCES
1698 OPEN c_route_seq (l_route_id);
1699 LOOP
1700 FETCH c_route_seq INTO c_route_seq_rec;
1701 EXIT WHEN c_route_seq%NOTFOUND;
1702 l_parent_task_id := 0;
1703 l_child_task_id := 0;
1704
1705 IF p_task_type = 'UNPLANNED' THEN -- For Unplanned task: Serial Id remains same for all Routes within a MR
1706 l_serial_id := p_MR_Serial_tbl(y).SERIAL_ID ;
1707
1708 IF (l_log_statement >= l_log_current_level) THEN
1709 fnd_log.string(l_log_statement,
1710 L_DEBUG_KEY,
1711 'Task type code = ' || p_task_type || ', MR ROUTE = ' || c_route_seq_rec.MR_ROUTE_ID);
1712 END IF;
1713
1714 OPEN c_route_task (c_route_seq_rec.MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type);
1715 FETCH c_route_task INTO l_parent_task_id;
1716 CLOSE c_route_task;
1717
1718 IF (l_log_statement >= l_log_current_level) THEN
1719 fnd_log.string(l_log_statement,
1720 L_DEBUG_KEY,
1721 'Parent Id = ' || l_parent_task_id || ', RELATED MR ROUTE = ' || c_route_seq_rec.RELATED_MR_ROUTE_ID);
1722 END IF;
1723
1724 OPEN c_route_task (c_route_seq_rec.RELATED_MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type);
1725 FETCH c_route_task INTO l_child_task_id;
1726 CLOSE c_route_task;
1727
1728 IF (l_log_statement >= l_log_current_level) THEN
1729 fnd_log.string(l_log_statement,
1730 L_DEBUG_KEY,
1731 'Child Id = ' || l_child_task_id);
1732 END IF;
1733
1734 IF l_parent_task_id <> 0 AND l_child_task_id <> 0 THEN
1735 IF (l_log_statement >= l_log_current_level) THEN
1736 fnd_log.string(l_log_statement,
1737 L_DEBUG_KEY,
1738 'Parent Id = ' || l_parent_task_id || ', Child Id = ' || l_child_task_id);
1739 END IF;
1740 l_tsk_flag := 'Y';
1741 ELSE
1742 l_tsk_flag := 'N';
1743 END IF;
1744
1745 ELSE -- Else of p_task_type = 'UNPLANNED' check
1746 -- For Planned task Serial Id are different for each MR Route within a MR,
1747 -- because of Unit effectivites relations
1748
1749 l_serial_id := p_MR_Serial_tbl(y).SERIAL_ID ;
1750
1751 OPEN c_route_task (c_route_seq_rec.MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type);
1752 FETCH c_route_task INTO l_parent_task_id;
1753 CLOSE c_route_task;
1754
1755 OPEN c_route_task (c_route_seq_rec.RELATED_MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type);
1756 FETCH c_route_task INTO l_child_task_id;
1757 CLOSE c_route_task;
1758
1759 IF l_parent_task_id <> 0 AND l_child_task_id <> 0 THEN
1760 l_tsk_flag := 'Y';
1761 ELSE
1762 l_tsk_flag := 'N';
1763 END IF;
1764
1765 END IF; -- End of check p_task_type = 'UNPLANNED' check
1766
1767 IF (l_log_statement >= l_log_current_level) THEN
1768 fnd_log.string(l_log_statement,
1769 L_DEBUG_KEY,
1770 'Value of L_TSK_FLAG after task type check' || l_tsk_flag);
1771 END IF;
1772
1773 IF l_tsk_flag = 'Y' THEN
1774 SELECT ahl_task_links_s.nextval INTO l_task_link_id FROM DUAL;
1775
1776 IF l_task_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
1777 l_task_rec.attribute_category := NULL;
1778 END IF;
1779 --
1780 IF l_task_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
1781 l_task_rec.attribute1 := NULL;
1782 END IF;
1783 --
1784 IF l_task_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
1785 l_task_rec.attribute2 := NULL;
1786 END IF;
1787 --
1788 IF l_task_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
1789 l_task_rec.attribute3 := NULL;
1790 END IF;
1791 --
1792 IF l_task_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
1793 l_task_rec.attribute4 := NULL;
1794 END IF;
1795 --
1796 IF l_task_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
1797 l_task_rec.attribute5 := NULL;
1798 END IF;
1799 --
1800 IF l_task_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
1801 l_task_rec.attribute6 := NULL;
1802 END IF;
1803 --
1804 IF l_task_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
1805 l_task_rec.attribute7 := NULL;
1806 END IF;
1807 --
1808 IF l_task_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
1809 l_task_rec.attribute8 := NULL;
1810 END IF;
1811 --
1812 IF l_task_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
1813 l_task_rec.attribute9 := NULL;
1814 END IF;
1815 --
1816 IF l_task_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
1817 l_task_rec.attribute10 := NULL;
1818 END IF;
1819 --
1820 IF l_task_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
1821 l_task_rec.attribute11 := NULL;
1822 END IF;
1823 --
1824 IF l_task_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
1825 l_task_rec.attribute12 := NULL;
1826 END IF;
1827 --
1828 IF l_task_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
1829 l_task_rec.attribute13 := NULL;
1830 END IF;
1831 --
1832 IF l_task_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
1833 l_task_rec.attribute14 := NULL;
1834 END IF;
1835 --
1836 IF l_task_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
1837 l_task_rec.attribute15 := NULL;
1838 END IF;
1839
1840 IF (l_log_statement >= l_log_current_level) THEN
1841 fnd_log.string(l_log_statement,
1842 L_DEBUG_KEY,
1843 'Before inserting record into AHL_TASK_LINKS');
1844 END IF;
1845
1846 INSERT INTO AHL_TASK_LINKS
1847 (
1848 TASK_LINK_ID,OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1849 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, VISIT_TASK_ID, PARENT_TASK_ID,
1850 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
1851 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1852 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
1853 )
1854 VALUES
1855 (
1856 l_TASK_LINK_ID, 1,sysdate, fnd_global.user_id, sysdate,
1857 fnd_global.user_id, fnd_global.user_id, l_child_task_id, l_parent_task_id,
1858 l_task_rec.ATTRIBUTE_CATEGORY, l_task_rec.ATTRIBUTE1, l_task_rec.ATTRIBUTE2,
1859 l_task_rec.ATTRIBUTE3, l_task_rec.ATTRIBUTE4, l_task_rec.ATTRIBUTE5,
1860 l_task_rec.ATTRIBUTE6, l_task_rec.ATTRIBUTE7, l_task_rec.ATTRIBUTE8,
1861 l_task_rec.ATTRIBUTE9, l_task_rec.ATTRIBUTE10,l_task_rec.ATTRIBUTE11,
1862 l_task_rec.ATTRIBUTE12, l_task_rec.ATTRIBUTE13, l_task_rec.ATTRIBUTE14,
1863 l_task_rec.ATTRIBUTE15
1864 );
1865
1866 IF (l_log_statement >= l_log_current_level) THEN
1867 fnd_log.string(l_log_statement,
1868 L_DEBUG_KEY,
1869 'After inserting record into AHL_TASK_LINKS');
1870 END IF;
1871 END IF; -- -- End of check l_tsk_flag = 'Y'
1872 END LOOP;
1873 CLOSE c_route_seq;
1874 END IF; -- End of check l_count > 0
1875
1876 END LOOP;
1877 CLOSE c_MR_route;
1878
1879 EXIT WHEN y = p_MR_Serial_tbl.LAST ;
1880 y := p_MR_Serial_tbl.NEXT(y);
1881 END LOOP;
1882 END IF; -- End of check p_MR_Id_tbl.count > 0
1883 x_return_status := Fnd_Api.g_ret_sts_success;
1884 IF (l_log_procedure >= l_log_current_level) THEN
1885 fnd_log.string(l_log_procedure,
1886 L_DEBUG_KEY ||'.end',
1887 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
1888 END IF;
1889 END Tech_Dependency;
1890
1891 --------------------------------------------------------------------
1892 -- PROCEDURE
1893 -- Create_Tasks_for_MR
1894 --
1895 -- PURPOSE
1896 -- To create tasks for MR
1897 --------------------------------------------------------------------
1898 PROCEDURE Create_Tasks_for_MR
1899 ( p_visit_id IN NUMBER,
1900 p_unit_id IN NUMBER,
1901 p_item_id IN NUMBER,
1902 p_org_id IN NUMBER,
1903 p_serial_id IN NUMBER,
1904 p_mr_id IN NUMBER,
1905 p_department_id IN NUMBER,
1906 p_service_req_id IN NUMBER,
1907 -- Added by rnahata for Issue 105
1908 p_quantity IN NUMBER,
1909 p_x_parent_MR_Id IN OUT NOCOPY NUMBER,
1910 x_return_status OUT NOCOPY VARCHAR2)
1911 IS
1912 -- Define local variables
1913 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Tasks_for_MR';
1914 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1915
1916 -- Define Cursors
1917 -- To find MR_ROUTE_ID for the particular MR_HEADER_ID
1918 CURSOR c_MR_route(x_mr_id IN NUMBER) IS
1919 -- AnRaj: Changes made for fixing bug#4919353, issue# 10
1920 SELECT T1.MR_ROUTE_ID,
1921 -- Added for 11.5.10 Changes done by Senthil.
1922 T1.STAGE
1923 FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
1924 WHERE T1.MR_HEADER_ID = x_mr_id
1925 AND T1.ROUTE_ID = T2.ROUTE_ID
1926 AND T2.REVISION_STATUS_CODE = 'COMPLETE'
1927 -- Added as of Bug# 3562914
1928 -- By shbhanda 04/22/2004
1929 AND T1.ROUTE_REVISION_NUMBER
1930 IN (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
1931 FROM AHL_MR_ROUTES_V T3
1932 WHERE T3.MR_HEADER_ID = x_mr_id
1933 AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
1934 GROUP BY T3.ROUTE_NUMBER
1935 );
1936
1937 -- To find any visit task exists for the retrieve Serial Number, Unit Effectivity ID and MR Route ID and other info
1938 CURSOR c_task (x_mroute_id IN NUMBER, x_serial_id IN NUMBER, x_unit_id IN NUMBER) IS
1939 SELECT Visit_Id, Visit_Task_id
1940 FROM AHL_VISIT_TASKS_B
1941 WHERE MR_Route_Id = x_mroute_id
1942 AND Instance_Id = x_serial_id
1943 AND Unit_Effectivity_Id = x_unit_id
1944 AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
1945 c_task_rec c_task%ROWTYPE;
1946
1947 -- To find on the basis of input unit effectivity the related information
1948 CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER, x_serial_id IN NUMBER) IS
1949 SELECT CSI.INV_MASTER_ORGANIZATION_ID, CSI.INVENTORY_ITEM_ID
1950 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
1951 WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
1952 AND AUEB.CSI_ITEM_INSTANCE_ID = x_serial_id
1953 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
1954 AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id
1955 AND AUEB.MR_HEADER_ID = x_mr_header_id;
1956
1957 -- To find any visit task exists for the retrieve Serial Number, Unit Effectivity ID and MR Route ID and other info
1958 CURSOR c_task_chk (x_id IN NUMBER, x_mr_id IN NUMBER, x_serial_id in NUMBER) IS
1959 SELECT AMHV.Title
1960 FROM AHL_VISIT_TASKS_B AVTB, AHL_MR_HEADERS_APP_V AMHV
1961 WHERE AVTB.MR_ID = AMHV.MR_HEADER_ID
1962 AND AVTB.MR_Id = x_mr_id
1963 AND AVTB.Instance_Id = x_serial_id
1964 AND AVTB.VISIT_ID = x_id
1965 AND (AVTB.STATUS_CODE IS NULL OR AVTB.STATUS_CODE <> 'DELETED');
1966 c_task_chk_rec c_task_chk%ROWTYPE;
1967
1968 CURSOR c_stage(p_stage_number IN NUMBER, p_visit_id IN NUMBER) IS
1969 SELECT stage_id,
1970 stage_name
1971 FROM ahl_vwp_stages_vl
1972 WHERE stage_num = p_stage_number
1973 AND visit_id = p_visit_id;
1974
1975 CURSOR c_mr_title(p_mr_id IN NUMBER) IS
1976 SELECT TITLE
1977 FROM ahl_mr_headers_b
1978 WHERE mr_header_id = p_mr_id;
1979
1980 CURSOR c_task_det(p_visit_task_id NUMBER) IS
1981 SELECT visit_task_id,
1982 start_date_time,
1983 end_date_time
1984 FROM ahl_visit_tasks_b
1985 WHERE visit_task_id = p_visit_task_id;
1986
1987 l_task_det c_task_det%rowtype;
1988
1989 -- Table type for storing MR Route Id
1990 TYPE MR_Route_Tbl_Type IS TABLE OF INTEGER
1991 INDEX BY BINARY_INTEGER;
1992
1993 -- Table type for storing MR Route Id
1994 -- 11.5.10 Changes done by Senthil.
1995 TYPE Stage_num_Tbl_Type IS TABLE OF INTEGER
1996 INDEX BY BINARY_INTEGER;
1997
1998 MR_Route_Tbl MR_Route_Tbl_Type;
1999 l_Stage_num_Tbl Stage_num_Tbl_Type;
2000 l_return_status VARCHAR2(1);
2001 l_msg_data VARCHAR2(2000);
2002 l_planned_order_flag VARCHAR2(1);
2003 l_msg_count NUMBER;
2004 l_visit_id NUMBER;
2005 l_Unit_Id NUMBER;
2006 l_MR_Id NUMBER;
2007 l_serial_id NUMBER;
2008 l_service_req_id NUMBER;
2009 l_department_id NUMBER;
2010 l_parent_MR_Id NUMBER;
2011 l_org_id NUMBER;
2012 l_item_id NUMBER;
2013 l_mr_route_id NUMBER;
2014 l_task_id NUMBER;
2015 l_parent_task_id NUMBER;
2016 i NUMBER;
2017 l_stage_number NUMBER;
2018 l_stage_name VARCHAR2(80);
2019 l_stage_id NUMBER;
2020 l_mr_title VARCHAR2(80);
2021
2022 BEGIN
2023 IF (l_log_procedure >= l_log_current_level) THEN
2024 fnd_log.string(l_log_procedure,
2025 L_DEBUG_KEY ||'.begin',
2026 'At the start of PL SQL procedure. p_visit_id' || p_visit_id ||
2027 ', p_unit_id' || p_unit_id || ', p_item_id' || p_item_id ||
2028 ', p_mr_id' || p_mr_id || ', p_service_req_id' || p_service_req_id ||
2029 ', p_quantity' || p_quantity);
2030 END IF;
2031 l_visit_id := p_visit_id;
2032 l_Unit_Id := p_Unit_Id ;
2033 l_MR_Id := p_MR_Id ;
2034 l_serial_id := p_serial_id ;
2035 l_service_req_id:= p_service_req_id;
2036 l_department_id := p_department_id;
2037 l_parent_MR_Id := p_x_parent_MR_Id;
2038
2039 IF l_Unit_Id IS NOT NULL then
2040 -- Cursor to find MR Id, Uniteffectivty and Serial with in task entity.
2041 -- Check if the results falls in the same visit as the input visit
2042 OPEN c_task(l_MR_id, l_serial_id, l_unit_Id);
2043 FETCH c_task INTO c_task_rec;
2044
2045 IF c_task%FOUND THEN
2046 IF (l_log_statement >= l_log_current_level)THEN
2047 fnd_log.string(l_log_statement,
2048 L_DEBUG_KEY,
2049 'Inner c_task found. Unit Effectivity is found');
2050 END IF;
2051 CLOSE c_task;
2052 OPEN c_mr_title(l_MR_id);
2053 FETCH c_mr_title into l_mr_title;
2054 CLOSE c_mr_title;
2055 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_TASK_FOUND');
2056 FND_MESSAGE.SET_TOKEN('MR',l_mr_title);
2057 Fnd_Msg_Pub.ADD;
2058
2059 ELSE -- else of c_task cursor found or not
2060 --Dup-MR ER#6338208 - sowsubra - start
2061 --commented to allow duplicate MR's in a visit
2062 /*
2063 -- Cursor to find MR and Serial with in all tasks of a visit.
2064 OPEN c_task_chk(l_visit_id, l_MR_Id, l_serial_id);
2065 FETCH c_task_chk INTO c_task_chk_rec;
2066 IF c_task_chk%FOUND THEN
2067 IF (l_log_statement >= l_log_current_level)THEN
2068 fnd_log.string(l_log_statement,
2069 L_DEBUG_KEY,
2070 ' Inner c_task2 found');
2071 END IF;
2072 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_FOUND');
2073 Fnd_Message.SET_TOKEN('MR_TITLE',c_task_chk_rec.title);
2074 Fnd_Msg_Pub.ADD;
2075 ELSE
2076 */
2077 --Dup-MR ER#6338208 - sowsubra - end
2078
2079 -- To retrieve Item Id and Organization Id with the input Unit Effectivity and MR Id
2080 OPEN c_info (l_MR_Id, l_Unit_Id, l_serial_id);
2081 FETCH c_info INTO l_org_id, l_item_id;
2082 CLOSE c_info;
2083
2084 IF (l_log_statement >= l_log_current_level)THEN
2085 fnd_log.string(l_log_statement,
2086 L_DEBUG_KEY,
2087 'c_info cursor - Organization = '||l_org_id ||
2088 'and Item = ' || l_item_id);
2089 END IF;
2090 --Dup-MR ER#6338208 - sowsubra - commented out
2091 -- END IF;
2092 -- CLOSE c_task_chk;
2093 END IF;
2094 CLOSE c_task;
2095 ELSE
2096 -- Cursor to find MR and Serial with in all tasks of a visit.
2097 --Dup-MR ER#6338208 - sowsubra - start
2098 --commented to allow duplicate MR's in a visit
2099 /*
2100 OPEN c_task_chk(l_visit_id, l_MR_Id, l_serial_id);
2101 FETCH c_task_chk INTO c_task_chk_rec;
2102 IF c_task_chk%FOUND THEN
2103 IF (l_log_statement >= l_log_current_level)THEN
2104 fnd_log.string(l_log_statement,
2105 L_DEBUG_KEY,
2106 ' Inner c_task2 found');
2107 END IF;
2108 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_FOUND');
2109 Fnd_Message.SET_TOKEN('MR_TITLE',c_task_chk_rec.title);
2110 Fnd_Msg_Pub.ADD;
2111 END IF;
2112 CLOSE c_task_chk;
2113 */
2114 --Dup-MR ER#6338208 - sowsubra - end
2115
2116 l_item_id := p_item_id;
2117 l_org_id := p_org_id;
2118 END IF;
2119
2120 l_msg_count := FND_MSG_PUB.count_msg;
2121 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2122 x_return_status := FND_API.G_RET_STS_ERROR;
2123 RAISE FND_API.G_EXC_ERROR;
2124 END IF;
2125
2126 IF (l_log_statement >= l_log_current_level) THEN
2127 fnd_log.string(l_log_statement,
2128 L_DEBUG_KEY,
2129 'Before calling INSERT_TASKS for Summary Task. Task Id = ' || l_task_id);
2130 END IF;
2131
2132 INSERT_TASKS
2133 (p_visit_id => l_visit_id,
2134 p_unit_id => l_unit_id,
2135 p_serial_id => l_serial_id,
2136 p_service_id => l_service_req_id,
2137 p_dept_id => l_department_id,
2138 p_item_id => l_item_id,
2139 p_item_org_id => l_org_id,
2140 p_mr_id => l_MR_id,
2141 p_mr_route_id => NULL,
2142 p_parent_id => l_parent_MR_Id,
2143 p_flag => 'Y',
2144 P_STAGE_ID => NULL,
2145 -- Added by rnahata for Issue 105 - pass the quantity for summary task
2146 p_quantity => p_quantity,
2147 x_task_id => l_task_id,
2148 x_return_status => l_return_status,
2149 x_msg_count => l_msg_count,
2150 x_msg_data => l_msg_data);
2151
2152 IF (l_log_statement >= l_log_current_level) THEN
2153 fnd_log.string(l_log_statement,
2154 L_DEBUG_KEY,
2155 'After calling INSERT_TASKS for Summary Task. Task Id = ' || l_task_id ||
2156 '. Return Status = ' || l_return_status);
2157 END IF;
2158
2159 l_msg_count := FND_MSG_PUB.count_msg;
2160 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2161 IF (l_log_statement >= l_log_current_level) THEN
2162 fnd_log.string(l_log_statement,
2163 L_DEBUG_KEY,
2164 'Errors from INSERT_TASKS. Message count: ' ||
2165 l_msg_count || ', message data: ' || l_msg_data);
2166 END IF;
2167 x_return_status := FND_API.G_RET_STS_ERROR;
2168 RAISE FND_API.G_EXC_ERROR;
2169 END IF;
2170
2171 p_x_parent_MR_Id := l_task_id; -- To get MR as parent for child summary tasks
2172 l_parent_Task_Id := l_task_id; -- To get MR as parent for child planned/unplanned tasks
2173
2174 /*
2175 y := 0;
2176 -- To retrieve all MR Route Id's for MR Header Id
2177
2178 OPEN c_MR_route (l_MR_Id);
2179 FETCH c_MR_route INTO l_mr_route_id,l_stage_number;
2180 WHILE c_MR_route%FOUND LOOP
2181 MR_Route_Tbl(y) := l_mr_route_id;
2182 y := y + 1;
2183 FETCH c_MR_route INTO l_mr_route_id;
2184 END LOOP;
2185 CLOSE c_MR_route;
2186 */
2187
2188 -- To retrieve all MR Route Id's for MR Header Id
2189 -- 11.5.10 Changes by Senthil.
2190 -- AnRaj: Changes made for fixing bug#4919353, issue# 11
2191 SELECT T1.MR_ROUTE_ID, T1.STAGE
2192 BULK COLLECT INTO MR_Route_Tbl,l_Stage_num_Tbl
2193 FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
2194 WHERE T1.MR_HEADER_ID = l_MR_Id
2195 AND T1.ROUTE_ID = T2.ROUTE_ID
2196 AND T2.revision_status_code = 'COMPLETE'
2197 AND T1.ROUTE_REVISION_NUMBER IN
2198 (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
2199 FROM AHL_MR_ROUTES_V T3
2200 WHERE T3.MR_HEADER_ID = l_MR_Id
2201 AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
2202 GROUP BY T3.ROUTE_NUMBER
2203 );
2204
2205 -- To Create Planned Tasks
2206 i := 0 ;
2207 IF MR_Route_Tbl.COUNT > 0 THEN
2208 i := MR_Route_Tbl.FIRST;
2209 LOOP
2210 IF l_Stage_num_Tbl(i) IS NOT NULL THEN
2211 OPEN c_stage(l_Stage_num_Tbl(i),l_visit_id);
2212 FETCH c_stage INTO l_stage_id, l_stage_name ;
2213 IF c_stage%NOTFOUND THEN
2214 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NO_EXIST');
2215 Fnd_Msg_Pub.ADD;
2216 CLOSE c_stage;
2217 RAISE FND_API.G_EXC_ERROR;
2218 END IF;
2219 CLOSE c_stage;
2220 END IF;
2221 --- End of 11.5.10 Changes by Senthil.
2222
2223 l_mr_route_id := MR_Route_Tbl(i);
2224
2225 IF (l_log_statement >= l_log_current_level) THEN
2226 fnd_log.string(l_log_statement,
2227 L_DEBUG_KEY,
2228 'l_mr_route_id = ' || l_mr_route_id);
2229 fnd_log.string(l_log_statement,
2230 L_DEBUG_KEY,
2231 'Before calling INSERT_TASKS for Simple Task');
2232 END IF;
2233
2234 INSERT_TASKS
2235 (p_visit_id => l_visit_id,
2236 p_unit_id => l_unit_id,
2237 p_serial_id => l_serial_id,
2238 p_service_id => l_service_req_id,
2239 p_dept_id => l_department_id,
2240 p_item_id => l_item_id,
2241 p_item_org_id => l_org_id,
2242 p_mr_id => l_MR_Id,
2243 p_MR_Route_id => l_MR_route_id,
2244 p_parent_id => l_parent_task_id,
2245 p_flag => 'N',
2246 P_STAGE_ID => l_stage_id,
2247 -- Added by rnahata for Issue 105 - pass the quantity for the simple tasks
2248 p_quantity => p_quantity,
2249 x_task_id => l_task_id,
2250 x_return_status => l_return_status,
2251 x_msg_count => l_msg_count,
2252 x_msg_data => l_msg_data);
2253
2254 IF (l_log_statement >= l_log_current_level) THEN
2255 fnd_log.string(l_log_statement,
2256 L_DEBUG_KEY,
2257 'After calling INSERT_TASKS for Simple Task. Task Id = ' || l_task_id ||
2258 '. Visit ID = ' || l_visit_id);
2259 END IF;
2260
2261 l_msg_count := FND_MSG_PUB.count_msg;
2262 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2263 IF (l_log_statement >= l_log_current_level) THEN
2264 fnd_log.string(l_log_statement,
2265 L_DEBUG_KEY,
2266 'Errors from INSERT_TASKS. Message count: ' ||
2267 l_msg_count || ', message data: ' || l_msg_data);
2268 END IF;
2269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270 RAISE FND_API.G_EXC_ERROR;
2271 END IF;
2272
2273 -- To call LTP Process Materials API for APS Integration by Shbhanda 04-Dec-03
2274 OPEN c_task_det(l_task_id);
2275 FETCH c_task_det INTO l_task_det;
2276 CLOSE c_task_det;
2277
2278 IF l_task_det.start_date_time IS NOT NULL THEN
2279
2280 IF (l_log_statement >= l_log_current_level) THEN
2281 fnd_log.string(l_log_statement,
2282 L_DEBUG_KEY,
2283 'PLANNED TASK - Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
2284 END IF;
2285 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
2286 p_api_version => 1.0,
2287 p_init_msg_list => FND_API.g_false,
2288 p_commit => FND_API.g_false,
2289 p_validation_level => FND_API.g_valid_level_full,
2290 p_visit_id => l_Visit_Id,
2291 p_visit_task_id => l_Task_Id,
2292 p_org_id => NULL,
2293 p_start_date => NULL,
2294 p_operation_flag => 'C',
2295 x_planned_order_flag => l_planned_order_flag ,
2296 x_return_status => x_return_status,
2297 x_msg_count => l_msg_count,
2298 x_msg_data => l_msg_data );
2299 IF (l_log_statement >= l_log_current_level) THEN
2300 fnd_log.string(l_log_statement,
2301 L_DEBUG_KEY,
2302 'PLANNED TASK - After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
2303 END IF;
2304 l_msg_count := FND_MSG_PUB.count_msg;
2305 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2306 X_return_status := FND_API.G_RET_STS_ERROR;
2307 RAISE FND_API.G_EXC_ERROR;
2308 END IF;
2309 END IF; --check for Visit Task Start Date
2310
2311 EXIT WHEN i = MR_Route_Tbl.LAST ;
2312 i := MR_Route_Tbl.NEXT(i);
2313 END LOOP;
2314 END IF;
2315
2316 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2317 IF (l_log_procedure >= l_log_current_level) THEN
2318 fnd_log.string(l_log_procedure,
2319 L_DEBUG_KEY ||'.end',
2320 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
2321 ', p_x_parent_MR_Id = ' || p_x_parent_MR_Id);
2322 END IF;
2323
2324 EXCEPTION
2325 WHEN NO_DATA_FOUND THEN
2326 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2327 WHEN TOO_MANY_ROWS THEN
2328 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2329 WHEN OTHERS THEN
2330 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2331 RAISE;
2332 END Create_Tasks_for_MR;
2333
2334
2335 --------------------------------------------------------------------
2336 -- PROCEDURE
2337 -- Check_Cost_Parent_Loop
2338 --
2339 -- PURPOSE
2340 -- To check if the cost parent task not forming loop among other tasks
2341 --------------------------------------------------------------------
2342 PROCEDURE Check_Cost_Parent_Loop
2343 (p_visit_id IN NUMBER,
2344 p_visit_task_id IN NUMBER,
2345 p_cost_parent_id IN NUMBER
2346 )
2347 IS
2348 -- Define local variables
2349 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Cost_Parent_Loop';
2350 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2351
2352 -- Define local cursors
2353 -- For all children of cost_parent_id
2354 CURSOR c_child (c_cost_id IN NUMBER, c_id IN NUMBER) IS
2355 SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
2356 WHERE VISIT_ID = c_id
2357 START WITH COST_PARENT_ID = c_cost_id
2358 CONNECT BY PRIOR VISIT_TASK_ID = COST_PARENT_ID;
2359 c_child_rec c_child%ROWTYPE;
2360
2361 BEGIN
2362 IF (l_log_procedure >= l_log_current_level) THEN
2363 fnd_log.string(l_log_procedure,
2364 L_DEBUG_KEY ||'.begin',
2365 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
2366 'Visit Task Id = ' || p_visit_task_id || 'Cost Parent Id = ' || p_cost_parent_id);
2367 END IF;
2368 -- Check for cost parent task id not forming loop
2369 IF (p_cost_parent_id IS NOT NULL AND
2370 p_cost_parent_id <> Fnd_Api.G_MISS_NUM ) THEN
2371
2372 IF p_cost_parent_id = p_visit_task_id THEN
2373 IF (l_log_statement >= l_log_current_level) THEN
2374 fnd_log.string(l_log_statement,
2375 L_DEBUG_KEY,
2376 'p_cost_parent_id = p_visit_task_id');
2377 END IF;
2378 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_COST_LOOP');
2379 Fnd_Msg_Pub.ADD;
2380 RAISE Fnd_Api.G_EXC_ERROR;
2381 END IF;
2382
2383 OPEN c_child (p_visit_task_id, p_visit_id);
2384 LOOP
2385 FETCH c_child INTO c_child_rec;
2386
2387 IF p_cost_parent_id = c_child_rec.VISIT_TASK_ID THEN
2388 IF (l_log_statement >= l_log_current_level) THEN
2389 fnd_log.string(l_log_statement,
2390 L_DEBUG_KEY,
2391 'TASK LOOP');
2392 END IF;
2393 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_COST_LOOP');
2394 Fnd_Msg_Pub.ADD;
2395 CLOSE c_child;
2396 RAISE Fnd_Api.G_EXC_ERROR;
2397 END IF;
2398 EXIT WHEN c_child%NOTFOUND;
2399 END LOOP;
2400 CLOSE c_child;
2401 END IF;
2402 IF (l_log_procedure >= l_log_current_level) THEN
2403 fnd_log.string(l_log_procedure,
2404 L_DEBUG_KEY ||'.end',
2405 'At the end of PL SQL procedure.');
2406 END IF;
2407
2408 END Check_Cost_Parent_Loop;
2409
2410 --------------------------------------------------------------------
2411 -- PROCEDURE
2412 -- Check_Origin_Task_Loop
2413 --
2414 -- PURPOSE
2415 -- To check if the originating task not forming loop among other tasks
2416 --------------------------------------------------------------------
2417 PROCEDURE Check_Origin_Task_Loop
2418 (p_visit_id IN NUMBER,
2419 p_visit_task_id IN NUMBER,
2420 p_originating_task_id IN NUMBER
2421 )
2422 IS
2423 -- Define local variables
2424 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Origin_Task_Loop';
2425 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2426
2427 -- Define local cursors
2428 -- For all children of originating_task_id
2429 CURSOR c_child (x_org_id IN NUMBER, x_id IN NUMBER) IS
2430 SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
2431 WHERE VISIT_ID = x_id
2432 AND NVL(STATUS_CODE,'X') <> 'DELETED'
2433 START WITH ORIGINATING_TASK_ID = x_org_id
2434 CONNECT BY PRIOR VISIT_TASK_ID = ORIGINATING_TASK_ID;
2435 c_child_rec c_child%ROWTYPE;
2436
2437 BEGIN
2438 IF (l_log_procedure >= l_log_current_level) THEN
2439 fnd_log.string(l_log_procedure,
2440 L_DEBUG_KEY ||'.begin',
2441 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id || '. Visit Task Id = ' ||
2442 p_visit_task_id || '. Originating Task Id' || p_originating_task_id);
2443 END IF;
2444 -- Check for originating task id not forming loop
2445 IF (p_originating_task_id IS NOT NULL AND
2446 p_originating_task_id <> Fnd_Api.G_MISS_NUM ) THEN
2447
2448 IF p_originating_task_id = p_visit_task_id THEN
2449 IF (l_log_statement >= l_log_current_level) THEN
2450 fnd_log.string(l_log_statement,
2451 L_DEBUG_KEY,
2452 'TASK LOOP1');
2453 END IF;
2454 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_ORIGIN_LOOP');
2455 Fnd_Msg_Pub.ADD;
2456 RAISE Fnd_Api.G_EXC_ERROR;
2457 END IF;
2458
2459 OPEN c_child (p_visit_task_id, p_visit_id);
2460 LOOP
2461 FETCH c_child INTO c_child_rec;
2462
2463 IF p_originating_task_id = c_child_rec.VISIT_TASK_ID THEN
2464 IF (l_log_statement >= l_log_current_level) THEN
2465 fnd_log.string(l_log_statement,
2466 L_DEBUG_KEY,
2467 'TASK LOOP2');
2468 END IF;
2469
2470 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_ORIGIN_LOOP');
2471 Fnd_Msg_Pub.ADD;
2472 CLOSE c_child;
2473 RAISE Fnd_Api.G_EXC_ERROR;
2474 END IF;
2475
2476 EXIT WHEN c_child%NOTFOUND;
2477 END LOOP;
2478 CLOSE c_child;
2479 END IF;
2480 IF (l_log_procedure >= l_log_current_level) THEN
2481 fnd_log.string(l_log_procedure,
2482 L_DEBUG_KEY ||'.end',
2483 'At the end of PL SQL procedure.');
2484 END IF;
2485 END Check_Origin_Task_Loop;
2486
2487 --------------------------------------------------------------------
2488 -- PROCEDURE
2489 -- Update_Visit_Task_Flag
2490 --
2491 -- PURPOSE
2492 -- To update visit entity any_task_chg_flag attribute whenever there
2493 -- are changes in visit task - either addition or deletion or change
2494 -- in cost parent of any task
2495 --------------------------------------------------------------------
2496 PROCEDURE Update_Visit_Task_Flag
2497 (p_visit_id IN NUMBER,
2498 p_flag IN VARCHAR2,
2499 x_return_status OUT NOCOPY VARCHAR2
2500 )
2501 IS
2502 -- Define local variables
2503 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Visit_Task_Flag';
2504 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2505
2506 BEGIN
2507 IF (l_log_procedure >= l_log_current_level) THEN
2508 fnd_log.string(l_log_procedure,
2509 L_DEBUG_KEY ||'.begin',
2510 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
2511 'p_flag' || p_flag);
2512 END IF;
2513 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2514 IF p_visit_id IS NOT NULL THEN
2515 UPDATE AHL_VISITS_B
2516 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2517 ANY_TASK_CHG_FLAG = p_flag
2518 WHERE VISIT_ID = p_visit_id;
2519 END IF;
2520 IF (l_log_procedure >= l_log_current_level) THEN
2521 fnd_log.string(l_log_procedure,
2522 L_DEBUG_KEY ||'.end',
2523 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
2524 END IF;
2525 END Update_Visit_Task_Flag;
2526
2527 --------------------------------------------------------------------
2528 -- PROCEDURE
2529 -- Check_Price_List_Name_Or_Id
2530 --
2531 -- PURPOSE
2532 -- To find out price list id for price list name for a visit or tasks
2533 --------------------------------------------------------------------
2534 PROCEDURE Check_Price_List_Name_Or_Id(
2535 p_visit_id IN NUMBER,
2536 p_price_list_name IN VARCHAR2,
2537 x_price_list_id OUT NOCOPY NUMBER,
2538 x_return_status OUT NOCOPY VARCHAR2
2539 ) IS
2540 -- Define local variables
2541 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Price_List_Name_Or_Id';
2542 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2543
2544 CURSOR visit_info_csr(p_visit_id IN NUMBER)IS
2545 SELECT service_request_id FROM ahl_visits_b
2546 WHERE visit_id = p_visit_id;
2547
2548 l_service_request_id NUMBER;
2549
2550 CURSOR customer_id_csr(p_service_request_id IN NUMBER)IS
2551 SELECT customer_id FROM CS_INCIDENTS_ALL_B
2552 WHERE incident_id = p_service_request_id;
2553
2554 l_customer_id NUMBER;
2555
2556 /*CURSOR price_list_id_csr(p_price_list_name IN VARCHAR2,p_customer_id IN NUMBER)IS
2557 SELECT qlhv.list_header_id
2558 FROM qp_list_headers_vl qlhv, FINANCIALS_SYSTEM_PARAMETERS FSP, qp_qualifiers qpq, GL_SETS_OF_BOOKS GSB
2559 WHERE FSP.set_of_books_id = GSB.set_of_books_id
2560 AND qlhv.list_type_code = 'PRL'
2561 AND qlhv.currency_code = gsb.currency_code
2562 AND UPPER(qlhv.name) like UPPER(p_price_list_name)
2563 AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
2564 AND qpq.list_header_id=qlhv.list_header_id
2565 AND qpq.qualifier_context = 'CUSTOMER'
2566 AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16'
2567 UNION
2568 SELECT qlhv.list_header_id
2569 FROM qp_list_headers_vl qlhv,oe_agreements oa, qp_qualifiers qpq, FINANCIALS_SYSTEM_PARAMETERS FSP, GL_SETS_OF_BOOKS GSB
2570 WHERE FSP.set_of_books_id = GSB.set_of_books_id
2571 AND ((oa.price_list_id = qlhv.list_header_id AND qlhv.list_type_code
2572 IN('PRL', 'AGR')) OR qlhv.list_type_code = 'PRL')
2573 AND qlhv.currency_code = gsb.currency_code
2574 AND UPPER(qlhv.name) like UPPER(p_price_list_name)
2575 AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
2576 AND qpq.list_header_id=qlhv.list_header_id
2577 AND qpq.qualifier_context = 'CUSTOMER'
2578 AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';*/
2579
2580 CURSOR price_list_id_csr(p_price_list_name IN VARCHAR2,p_customer_id IN NUMBER)IS
2581 SELECT qlhv.list_header_id
2582 from qp_list_headers_vl qlhv, qp_qualifiers qpq
2583 where qlhv.list_type_code = 'PRL'
2584 and upper(qlhv.name) like upper(p_price_list_name)
2585 and qpq.QUALIFIER_ATTR_VALUE = p_customer_id
2586 and qpq.list_header_id=qlhv.list_header_id
2587 and qpq.qualifier_context = 'CUSTOMER'
2588 and qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';
2589
2590 BEGIN
2591 IF (l_log_procedure >= l_log_current_level) THEN
2592 fnd_log.string(l_log_procedure,
2593 L_DEBUG_KEY ||'.begin',
2594 'At the start of PL SQL procedure. Visit Id = ' ||
2595 p_visit_id || 'Price List Name = ' || p_price_list_name);
2596 END IF;
2597 -- Initialize API return status to success
2598 x_return_status := FND_API.G_RET_STS_SUCCESS;
2599
2600 OPEN visit_info_csr(p_visit_id);
2601 FETCH visit_info_csr INTO l_service_request_id;
2602 IF (visit_info_csr%NOTFOUND) THEN
2603 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
2604 FND_MSG_PUB.ADD;
2605 IF (l_log_unexpected >= l_log_current_level)THEN
2606 fnd_log.string(l_log_unexpected,
2607 L_DEBUG_KEY,
2608 'Visit id not found in AHL_VISITS_B table');
2609 END IF;
2610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2611 ELSIF(l_service_request_id IS NULL)THEN
2612 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT_UPDT_NOS');
2613 FND_MSG_PUB.ADD;
2614 IF (fnd_log.level_error >= l_log_current_level)THEN
2615 fnd_log.string(fnd_log.level_error,
2616 L_DEBUG_KEY,
2617 'price list can not be associated because service request id is not associated to visit');
2618 END IF;
2619 x_return_status := FND_API.G_RET_STS_ERROR;
2620 END IF;
2621 CLOSE visit_info_csr;
2622
2623 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2624 RETURN;
2625 END IF;
2626
2627 OPEN customer_id_csr(l_service_request_id);
2628 FETCH customer_id_csr INTO l_customer_id;
2629 IF(customer_id_csr%NOTFOUND)THEN
2630 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_SR_ID');
2631 FND_MSG_PUB.ADD;
2632 IF (l_log_unexpected >= l_log_current_level)THEN
2633 fnd_log.string(l_log_unexpected,
2634 L_DEBUG_KEY,
2635 'Associated Service Request ' || l_service_request_id ||
2636 ' is invalid as record not found.');
2637 END IF;
2638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2639 ELSIF(l_customer_id IS NULL)THEN
2640 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_SRVREQ_NOCUST');
2641 FND_MSG_PUB.ADD;
2642 IF (fnd_log.level_error >= l_log_current_level)THEN
2643 fnd_log.string(fnd_log.level_error,
2644 L_DEBUG_KEY,
2645 'Customer id for corresponding service request ' || l_service_request_id || ' is null.');
2646 END IF;
2647 x_return_status := FND_API.G_RET_STS_ERROR;
2648 END IF;
2649 CLOSE customer_id_csr;
2650
2651 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2652 RETURN;
2653 END IF;
2654
2655 -- find out the price list id
2656 OPEN price_list_id_csr(p_price_list_name,l_customer_id);
2657 FETCH price_list_id_csr INTO x_price_list_id;
2658 IF(price_list_id_csr%NOTFOUND)THEN
2659 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PLIST_NFOUND');
2660 FND_MESSAGE.Set_Token('PRICE_LIST',p_price_list_name);
2661 FND_MSG_PUB.ADD;
2662 IF (fnd_log.level_error >= l_log_current_level)THEN
2663 fnd_log.string(fnd_log.level_error,
2664 L_DEBUG_KEY,
2665 'Valid price list not found with price list name ' || p_price_list_name);
2666 END IF;
2667 x_return_status := FND_API.G_RET_STS_ERROR;
2668 END IF;
2669 CLOSE price_list_id_csr;
2670
2671 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2672 RETURN;
2673 END IF;
2674 IF (l_log_procedure >= l_log_current_level) THEN
2675 fnd_log.string(l_log_procedure,
2676 L_DEBUG_KEY ||'.end',
2677 'At the end of PL SQL procedure. x_price_list_id = ' || x_price_list_id);
2678 END IF;
2679
2680 END Check_Price_List_Name_Or_Id;
2681 --------------------------------------------------------------------
2682 -- PROCEDURE
2683 -- Update_Cost_Origin_Task
2684 --
2685 -- PURPOSE
2686 -- To update all tasks which have the deleting task as cost or originating task
2687 --------------------------------------------------------------------
2688 PROCEDURE Update_Cost_Origin_Task
2689 (p_visit_task_id IN NUMBER,
2690 x_return_status OUT NOCOPY VARCHAR2
2691 )
2692 IS
2693 -- Define local variables
2694 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Cost_Origin_Task';
2695 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2696 l_task_id NUMBER;
2697
2698 -- To find any task which have originating id as deleted task id
2699 CURSOR c_origin (x_task_id IN NUMBER) IS
2700 SELECT Visit_Task_Id, Object_Version_Number
2701 FROM Ahl_Visit_Tasks_VL
2702 WHERE ORIGINATING_TASK_ID = x_task_id;
2703 c_origin_rec c_origin%ROWTYPE;
2704
2705 -- To find any task which have parent id as deleted task id
2706 CURSOR c_parent (x_task_id IN NUMBER) IS
2707 SELECT Visit_Task_Id, Object_Version_Number
2708 FROM Ahl_Visit_Tasks_VL
2709 WHERE COST_PARENT_ID = x_task_id;
2710 c_parent_rec c_parent%ROWTYPE;
2711
2712 -- Post 11.5.10
2713 --RR
2714 -- For updating the Cost Parent Task
2715 CURSOR c_parent_id(x_task_id IN NUMBER) IS
2716 SELECT cost_parent_id
2717 FROM AHL_VISIT_TASKS_VL
2718 WHERE visit_task_id = x_task_id;
2719 l_parent_id NUMBER;
2720 -- Post 11.5.10
2721 --RR
2722 BEGIN
2723
2724 IF (l_log_procedure >= l_log_current_level) THEN
2725 fnd_log.string(l_log_procedure,
2726 L_DEBUG_KEY ||'.begin',
2727 'At the start of PL SQL procedure. Visit Task Id = ' || p_visit_task_id);
2728 END IF;
2729 l_task_id := p_visit_task_id;
2730
2731 -- Post 11.5.10
2732 -- RR
2733 OPEN c_parent_id(l_task_id);
2734 FETCH c_parent_id INTO l_parent_id;
2735 CLOSE c_parent_id;
2736
2737 -- To find if a task deleted is the "originating task" for another task,
2738 -- then association/s must be removed before the task can be deleted.
2739 OPEN c_origin (l_task_id);
2740 LOOP
2741 FETCH c_origin INTO c_origin_rec;
2742 EXIT WHEN c_origin%NOTFOUND;
2743 IF c_origin_rec.visit_task_id IS NOT NULL THEN
2744 IF (l_log_statement >= l_log_current_level) THEN
2745 fnd_log.string(l_log_statement,
2746 L_DEBUG_KEY,
2747 'update origin');
2748 END IF;
2749 UPDATE AHL_VISIT_TASKS_B SET ORIGINATING_TASK_ID = NULL,
2750 OBJECT_VERSION_NUMBER = c_origin_rec.object_version_number + 1
2751 WHERE VISIT_TASK_ID = c_origin_rec.visit_task_id;
2752 END IF;
2753 END LOOP;
2754 CLOSE c_origin;
2755
2756 -- To find if a task deleted is the "cost parent task" for another task,
2757 -- then association/s must be removed before the task can be deleted.
2758 -- Post 11.5.10
2759 -- RR
2760 OPEN c_parent (l_task_id);
2761 LOOP
2762 FETCH c_parent INTO c_parent_rec;
2763 EXIT WHEN c_parent%NOTFOUND;
2764 IF c_parent_rec.visit_task_id IS NOT NULL THEN
2765 IF (l_log_statement >= l_log_current_level) THEN
2766 fnd_log.string(l_log_statement,
2767 L_DEBUG_KEY,
2768 'update parent');
2769 END IF;
2770 UPDATE AHL_VISIT_TASKS_B SET COST_PARENT_ID = l_parent_id,
2771 OBJECT_VERSION_NUMBER = c_parent_rec.object_version_number + 1
2772 WHERE VISIT_TASK_ID = c_parent_rec.visit_task_id;
2773 END IF;
2774 END LOOP;
2775 CLOSE c_parent;
2776 -- RR
2777 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2778 IF (l_log_procedure >= l_log_current_level) THEN
2779 fnd_log.string(l_log_procedure,
2780 L_DEBUG_KEY ||'.end',
2781 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
2782 END IF;
2783 END Update_Cost_Origin_Task;
2784
2785 --------------------------------------------------------------------
2786 -- PROCEDURE
2787 -- Merge_for_Unique_Items
2788 --
2789 -- PURPOSE
2790 -- To merge two item tables and remove the redundant items
2791 -- in table for which no price is defined
2792 --------------------------------------------------------------------
2793 PROCEDURE Merge_for_Unique_Items
2794 (p_item_tbl1 IN Item_Tbl_Type,
2795 p_item_tbl2 IN Item_Tbl_Type,
2796 x_item_tbl OUT NOCOPY Item_Tbl_Type
2797 )
2798 IS
2799 -- Define local variables
2800 L_API_NAME CONSTANT VARCHAR2(30) := 'Merge_for_Unique_Items';
2801 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2802 l_index NUMBER:=0;
2803 l_item_present boolean:=false;
2804 BEGIN
2805
2806 IF (l_log_procedure >= l_log_current_level) THEN
2807 fnd_log.string(l_log_procedure,
2808 L_DEBUG_KEY ||'.begin',
2809 'At the start of PL SQL procedure. p_item_tbl1.count = ' || p_item_tbl1.count ||
2810 '. p_item_tbl2.count = ' || p_item_tbl2.count);
2811 END IF;
2812
2813 IF p_item_tbl1.count > 0 THEN
2814 x_item_tbl := p_item_tbl1;
2815 l_index:=x_item_tbl.count;
2816 ELSE
2817 x_item_tbl := p_item_tbl2;
2818 RETURN;
2819 END IF;
2820
2821 IF p_item_tbl2.count > 0 THEN
2822 FOR i IN p_item_tbl2.first ..p_item_tbl2.last
2823 LOOP
2824 l_item_present:=false;
2825 IF x_item_tbl.count > 0 THEN
2826 FOR k IN x_item_tbl.first .. x_item_tbl.last
2827 LOOP
2828 IF x_item_tbl(k).item_id=p_item_tbl2(i).item_id
2829 AND x_item_tbl(k).uom_code=p_item_tbl2(i).uom_code
2830 AND x_item_tbl(k).effective_date=p_item_tbl2(i).effective_date
2831 THEN
2832 IF x_item_tbl(k).duration is not NULL AND x_item_tbl(k).duration <> FND_API.G_MISS_NUM
2833 AND p_item_tbl2(i).duration is not NULL AND p_item_tbl2(i).duration <> FND_API.G_MISS_NUM THEN
2834 x_item_tbl(k).duration := nvl(x_item_tbl(k).duration,0)+nvl(p_item_tbl2(i).duration,0);
2835 l_item_present:=true;
2836 ELSIF (x_item_tbl(k).duration is NULL OR x_item_tbl(k).duration = FND_API.G_MISS_NUM )
2837 AND (p_item_tbl2(i).duration is NULL OR p_item_tbl2(i).duration = FND_API.G_MISS_NUM) THEN
2838 x_item_tbl(k).quantity := nvl(x_item_tbl(k).quantity,0)+nvl(p_item_tbl2(i).quantity,0);
2839 l_item_present:=true;
2840 END IF;
2841 END IF;
2842 END LOOP;
2843 END IF;
2844
2845 IF l_item_present=FALSE THEN
2846 l_index:=l_index+1;
2847 x_item_tbl(l_index) := p_item_tbl2(i);
2848 END IF;
2849 END LOOP;
2850 END IF;
2851
2852 IF (l_log_procedure >= l_log_current_level) THEN
2853 fnd_log.string(l_log_procedure,
2854 L_DEBUG_KEY ||'.end',
2855 'At the end of PL SQL procedure. x_item_tbl.COUNT = ' || x_item_tbl.COUNT);
2856 END IF;
2857
2858 END Merge_for_Unique_Items;
2859
2860 -------------------------------------------------------------------
2861 -- PROCEDURE
2862 -- Check_Item_in_Price_List
2863 --
2864 -- PURPOSE
2865 -- To Check if item of MR is defined in price list.
2866 --------------------------------------------------------------------
2867 /* commented as this is not being used anywhere
2868 PROCEDURE Check_Item_in_Price_List
2869 (p_price_list IN NUMBER,
2870 p_item_id IN NUMBER,
2871 x_item_chk_flag OUT NOCOPY NUMBER
2872 )
2873 IS
2874 -- Define local variables
2875 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Item_in_Price_List';
2876 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2877 l_item_chk_flag VARCHAR2(1) ;
2878 I NUMBER;
2879
2880 CURSOR c_task(p_visit_id NUMBER, p_tsk_id NUMBER) IS
2881 SELECT visit_task_id, originating_task_id
2882 FROM AHL_VISIT_TASKS_B
2883 WHERE VISIT_ID = p_visit_id
2884 AND VISIT_TASK_ID = p_tsk_id
2885 AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
2886 c_task_rec c_task%ROWTYPE;
2887
2888 BEGIN
2889
2890 OPEN c_task(p_price_list, p_item_id);
2891 LOOP
2892 FETCH c_task INTO c_task_rec;
2893 EXIT WHEN c_task%NOTFOUND;
2894
2895 IF c_task_rec.originating_task_id IS NOT NULL THEN
2896 l_item_chk_flag := 'Y';
2897 Check_Item_in_Price_List
2898 ( p_price_list => p_price_list,
2899 p_item_id => c_task_rec.originating_task_id,
2900 x_item_chk_flag => l_item_chk_flag);
2901
2902 IF (l_log_procedure >= l_log_current_level)THEN
2903 fnd_log.string(l_log_procedure,
2904 L_DEBUG_KEY||'.end',
2905 'Check for RECURSIVE task id = ' || p_item_id);
2906 END IF;
2907 ELSE
2908 l_item_chk_flag := 'N';
2909 IF (l_log_procedure >= l_log_current_level)THEN
2910 fnd_log.string(l_log_procedure,
2911 L_DEBUG_KEY||'.end',
2912 'Check for NON RECURSIVE task id = ' || p_item_id);
2913 END IF;
2914 END IF;
2915 I:=I+1;
2916 END LOOP;
2917 CLOSE c_task;
2918
2919 END Check_Item_in_Price_List;
2920 */
2921
2922 --------------------------------------------------------------------
2923 -- PROCEDURE
2924 -- Check_Currency_for_Costing
2925 --
2926 -- PURPOSE
2927 -- To used to retrieve currency code and pass as input parameter to Pricing API
2928 --------------------------------------------------------------------
2929 PROCEDURE Check_Currency_for_Costing
2930 (p_visit_id IN NUMBER,
2931 x_currency_code OUT NOCOPY VARCHAR2
2932 )
2933 IS
2934 -- Define local variables
2935 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Currency_for_Costing';
2936 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2937
2938 CURSOR organization_csr(p_visit_id IN NUMBER)IS
2939 SELECT VST.organization_id FROM AHL_VISITS_B VST
2940 WHERE VST.visit_id = p_visit_id;
2941
2942 l_org_id NUMBER;
2943
2944 -- AnRaj:Changes made for fixing bug#4919353, issue# 7
2945 CURSOR currency_code_csr(p_org_id IN NUMBER) IS
2946 SELECT currency_code
2947 FROM cst_acct_info_v COD, GL_SETS_OF_BOOKS GSOB
2948 WHERE COD.Organization_Id = p_org_id
2949 AND LEDGER_ID = GSOB.SET_OF_BOOKS_ID
2950 AND NVL(operating_unit, mo_global.get_current_org_id())= mo_global.get_current_org_id();
2951
2952 /*SELECT currency_code
2953 -- into x_currency_code
2954 FROM CST_ORGANIZATION_DEFINITIONS COD --,AHL_VISITS_B VST
2955 WHERE --VST.visit_id = p_visit_id AND
2956 --COD.Organization_Id = VST.organization_id
2957 COD.Organization_Id = p_org_id
2958 AND NVL(operating_unit, mo_global.get_current_org_id())
2959 = mo_global.get_current_org_id();*/
2960
2961 BEGIN
2962
2963 IF (l_log_procedure >= l_log_current_level) THEN
2964 fnd_log.string(l_log_procedure,
2965 L_DEBUG_KEY ||'.begin',
2966 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
2967 END IF;
2968 -- FETCH organization id
2969 OPEN organization_csr(p_visit_id);
2970 FETCH organization_csr INTO l_org_id;
2971 CLOSE organization_csr;
2972
2973 IF (l_org_id IS NOT NULL)THEN
2974 OPEN currency_code_csr(l_org_id);
2975 FETCH currency_code_csr INTO x_currency_code;
2976 IF (currency_code_csr%NOTFOUND)THEN
2977 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_NO_CURRENCY');
2978 FND_MSG_PUB.ADD;
2979 IF (fnd_log.level_error >= l_log_current_level)THEN
2980 fnd_log.string(fnd_log.level_error,
2981 L_DEBUG_KEY,
2982 'No curency is defined for the organization of the visit. l_org_id = ' || l_org_id);
2983 END IF;
2984 END IF;
2985 CLOSE currency_code_csr;
2986 END IF;
2987
2988 IF (l_log_procedure >= l_log_current_level) THEN
2989 fnd_log.string(l_log_procedure,
2990 L_DEBUG_KEY ||'.end',
2991 'At the end of PL SQL procedure. Currency Code = ' || x_currency_code);
2992 END IF;
2993 END Check_Currency_for_Costing;
2994 -------------------------------------------------------------------------------
2995 -- PROCEDURE
2996 -- Check_Job_Status
2997 --
2998 -- PURPOSE
2999 -- To find out valid job status on shop floor for a Visit/MR/Task
3000 -------------------------------------------------------------------------------
3001 PROCEDURE Check_Job_Status
3002 (p_id IN NUMBER,
3003 p_is_task_flag IN VARCHAR2,
3004 x_status_code OUT NOCOPY NUMBER,
3005 x_status_meaning OUT NOCOPY VARCHAR2
3006 )
3007 IS
3008 -- Define local variables
3009 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Job_Status';
3010 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3011
3012 -- To find if job exists for the visit at shop floor not in Cancelled-7 Or Deleted-22
3013 CURSOR c_job(x_id IN NUMBER) IS
3014 SELECT AWO.STATUS_CODE, FLV.MEANING
3015 FROM AHL_WORKORDERS AWO, FND_LOOKUP_VALUES_VL FLV
3016 WHERE AWO.VISIT_ID = x_id
3017 AND AWO.STATUS_CODE <> 7 AND AWO.STATUS_CODE <> 22
3018 AND FLV.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
3019 AND AWO.STATUS_CODE = FLV.LOOKUP_CODE(+)
3020 AND AWO.MASTER_WORKORDER_FLAG = 'Y'
3021 AND AWO.VISIT_TASK_ID IS NULL;
3022
3023 -- To find if job exists for the Task/MR at shop floor not in Cancelled-7 Or Deleted-22
3024 CURSOR c_job_tsk(x_id IN NUMBER)IS
3025 SELECT AWO.STATUS_CODE, FLV.MEANING
3026 FROM AHL_WORKORDERS AWO, FND_LOOKUP_VALUES_VL FLV
3027 WHERE AWO.VISIT_TASK_ID = x_id
3028 AND AWO.STATUS_CODE <> 7 AND AWO.STATUS_CODE <> 22
3029 AND FLV.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
3030 AND AWO.STATUS_CODE = FLV.LOOKUP_CODE(+);
3031 BEGIN
3032 IF (l_log_procedure >= l_log_current_level) THEN
3033 fnd_log.string(l_log_procedure,
3034 L_DEBUG_KEY ||'.begin',
3035 'At the start of PL SQL procedure. Visit OR Task Id = ' || p_id ||
3036 'p_is_task_flag = '|| p_is_task_flag);
3037 END IF;
3038
3039 IF p_is_task_flag = 'N' THEN -- For Visit
3040 OPEN c_job(p_id);
3041 FETCH c_job INTO x_status_code, x_status_meaning;
3042 CLOSE c_job;
3043 ELSE -- For MR/Task
3044 OPEN c_job_tsk(p_id);
3045 FETCH c_job_tsk INTO x_status_code, x_status_meaning;
3046 CLOSE c_job_tsk;
3047 END IF;
3048
3049 IF (l_log_procedure >= l_log_current_level) THEN
3050 fnd_log.string(l_log_procedure,
3051 L_DEBUG_KEY ||'.end',
3052 'At the end of PL SQL procedure. Status Code = '|| x_status_code ||
3053 'Status Meaning = ' || x_status_meaning);
3054 END IF;
3055
3056 END Check_Job_Status;
3057
3058 -------------------------------------------------------------------------------
3059 -- PROCEDURE
3060 -- Check_Department_Shift
3061 --
3062 -- PURPOSE
3063 -- To find out valid job status on shop floor for a Visit/MR/Task
3064 -------------------------------------------------------------------------------
3065 PROCEDURE Check_Department_Shift(
3066 p_dept_id IN NUMBER,
3067 x_return_status OUT NOCOPY VARCHAR2
3068 )
3069 is
3070 L_DUMMY VARCHAR2(1);
3071 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Department_Shift';
3072 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3073 --
3074 CURSOR get_dept_csr (p_dept_id IN NUMBER) IS
3075 SELECT 'x'
3076 FROM AHL_DEPARTMENT_SHIFTS
3077 WHERE DEPARTMENT_ID = P_DEPT_ID;
3078 --
3079 BEGIN
3080 IF (l_log_procedure >= l_log_current_level) THEN
3081 fnd_log.string(l_log_procedure,
3082 L_DEBUG_KEY ||'.begin',
3083 'At the start of PL SQL procedure. Department Id = ' || p_dept_id);
3084 END IF;
3085 IF P_DEPT_ID IS NOT NULL AND P_DEPT_ID <> FND_API.G_MISS_NUM
3086 THEN
3087 OPEN get_dept_csr (p_dept_id);
3088 FETCH get_dept_csr INTO l_dummy;
3089 IF (get_dept_csr%NOTFOUND) THEN
3090 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
3091 ELSE
3092 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3093 END IF;
3094 CLOSE get_dept_csr;
3095 ELSE
3096 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3097 END IF;
3098 IF (l_log_procedure >= l_log_current_level) THEN
3099 fnd_log.string(l_log_procedure,
3100 L_DEBUG_KEY ||'.end',
3101 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
3102 END IF;
3103 END CHECK_DEPARTMENT_SHIFT;
3104
3105 END AHL_VWP_RULES_PVT;
[Home] [Help]