1 PACKAGE BODY AHL_VWP_RULES_PVT AS
2 /* $Header: AHLVRULB.pls 120.21.12020000.2 2012/12/07 14:07:21 sareepar 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 -- Add_Return_To_Supply -- Remove_Return_To_Supply
34 -- Validate_Return_To_Supply
35
36 -- NOTES
37 --
38 -- HISTORY
39 -- 03-MAR-2003 SHBHANDA Created.
40 -- 06-AUG-2003 SHBHANDA 11.5.10 Changes.
41 -- 09-12-2003 RTADIKON Merge_for_Unique_Items Coded for costing 11.5.10
42 -- Along with the logging mechanism.
43 -- 05-NOV-2007 RNAHATA Replaced all Ahl_Debug_Pub.debug with STATEMENT
44 -- level logs and added more STATEMENT level logs at
45 -- key decision points. Added PROCEDURE level logs
46 -- when entering and exiting a procedure.
47 -----------------------------------------------------------------
48
49 -----------------------------------------------------------------
50 -- Define Global CONSTANTS --
51 -----------------------------------------------------------------
52 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
53 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_RULES_PVT';
54 G_OPER_ASSOC_TYPE CONSTANT VARCHAR2(30) := 'OPERATION';
55 G_RT_ASSOC_TYPE CONSTANT VARCHAR2(30) := 'ROUTE';
56 ------------------------------------
57 -- Common constants and variables --
58 ------------------------------------
59 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
60 l_log_statement NUMBER := fnd_log.level_statement;
61 l_log_procedure NUMBER := fnd_log.level_procedure;
62 l_log_error NUMBER := fnd_log.level_error;
63 l_log_unexpected NUMBER := fnd_log.level_unexpected;
64 ---------------------------------------------------------------------
65 -- Define Record Types for record structures needed by the APIs --
66 ---------------------------------------------------------------------
67 -- Serial record type for validating serial exists in unit-config tree of the visit
68 TYPE Serial_Rec_Type IS RECORD
69 (INSTANCE_ID NUMBER,
70 SERIAL_NUMBER VARCHAR2(30));
71
72 ---------------------------------------------------------------------
73 -- Define Table Types for table structures of records needed by the APIs --
74 ---------------------------------------------------------------------
75 -- Table type for storing 'Serial_Rec_Type' record datatype
76 TYPE Serial_Tbl_Type IS TABLE OF Serial_Rec_Type
77 INDEX BY BINARY_INTEGER;
78
79 --AJPRASAN:: VWPE:: 29-JUL-11:: New Table Type for Numbers
80 TYPE Num_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
81
82 --------------------------------------------------------------------
83 -- Define local procedures signature --
84 --------------------------------------------------------------------
85
86 --------------------------------------------------------------------
87 -- Define local functions signature --
88 --------------------------------------------------------------------
89
90 --------------------------------------------------------------------
91 -- Define local procedures body --
92 --------------------------------------------------------------------
93 -----------------------------------------------------------------------
94 -- FUNCTION
95 -- instance_in_config_tree
96 --
97 -- PURPOSE
98 -- Check whether p_instance_id belongs to the instance of p_visit_id
99 -- Return 'Y' for the following cases:
100 -- 1. p_visit_id doesn't have instance_id associated at all
101 -- 2. The instance_id of p_visit_id = p_instance_id regardless whether
102 -- the instance of p_visit_id has components or not
103 -- 3. p_instance_id is a component of the instance of p_visit_id regardless
104 -- whether it is a UC tree or IB tree
105 -- Return 'N' otherwise
106 -----------------------------------------------------------------------
107 FUNCTION instance_in_config_tree(p_visit_id NUMBER, p_instance_id NUMBER) RETURN VARCHAR2
108 IS
109 l_instance_id NUMBER;
110 l_visit_instance_id NUMBER;
111 L_API_NAME CONSTANT VARCHAR2(30) := 'instance_in_config_tree';
112 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
113
114 CURSOR get_visit_instance IS
115 SELECT item_instance_id
116 FROM ahl_visits_b
117 WHERE visit_id = p_visit_id;
118 CURSOR check_instance_in_tree(c_top_instance_id NUMBER, c_instance_id NUMBER) IS
119 SELECT subject_id
120 FROM csi_ii_relationships
121 WHERE subject_id = c_instance_id
122 START WITH object_id = c_top_instance_id
123 AND relationship_type_code = 'COMPONENT-OF'
124 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
125 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
126 CONNECT BY object_id = PRIOR subject_id
127 AND relationship_type_code = 'COMPONENT-OF'
128 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
129 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
130
131 BEGIN
132 IF (l_log_procedure >= l_log_current_level) THEN
133 fnd_log.string(l_log_procedure,
134 L_DEBUG_KEY ||'.begin',
135 'At the start of PL SQL function. Visit Id = ' || p_visit_id ||
136 ', p_instance_id = ' || p_instance_id);
137 END IF;
138 OPEN get_visit_instance;
139 FETCH get_visit_instance INTO l_visit_instance_id;
140 -- Added additional or condition by senthil as the Visit Instance Id can be null.
141 IF get_visit_instance%NOTFOUND OR l_visit_instance_id is NULL THEN
142 CLOSE get_visit_instance;
143 RETURN FND_API.G_RET_STS_SUCCESS;
144 ELSE
145 CLOSE get_visit_instance;
146 IF l_visit_instance_id = p_instance_id THEN
147 RETURN FND_API.G_RET_STS_SUCCESS;
148 ELSE
149 OPEN check_instance_in_tree(l_visit_instance_id, p_instance_id);
150 FETCH check_instance_in_tree INTO l_instance_id;
151 IF check_instance_in_tree%FOUND THEN
152 CLOSE check_instance_in_tree;
153 RETURN FND_API.G_RET_STS_SUCCESS;
154 ELSE
155 CLOSE check_instance_in_tree;
156 RETURN FND_API.G_RET_STS_ERROR;
157 END IF;
158 END IF;
159 END IF;
160 END;
161
162 --------------------------------------------------------------------
163 -- PROCEDURE
164 -- Check_Item_Name_OR_Id
165 --
166 -- PURPOSE
167 -- Converts Item Name and Inventory Org to Inventory Item Id
168 --------------------------------------------------------------------
169 PROCEDURE Check_Item_Name_Or_Id
170 (p_item_id IN NUMBER,
171 p_org_id IN NUMBER,
172 p_item_name IN VARCHAR2,
173
174 x_item_id OUT NOCOPY NUMBER,
175 x_org_id OUT NOCOPY NUMBER,
176 x_item_name OUT NOCOPY VARCHAR2,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_error_msg_code OUT NOCOPY VARCHAR2
179 )
180 IS
181 -- Define local variables
182 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Item_Name_Or_Id';
183 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
184 BEGIN
185 IF (l_log_procedure >= l_log_current_level) THEN
186 fnd_log.string(l_log_procedure,
187 L_DEBUG_KEY ||'.begin',
188 'At the start of PL SQL procedure. Item Id = ' ||
189 p_item_id || ', Item Name = ' || p_item_name || ', Org Id = ' || p_org_id);
190 END IF;
191
192 IF p_org_id IS NOT NULL THEN
193 IF p_item_name IS NOT NULL THEN
194 -- SELECT concatenated_segments, inventory_item_id, inventory_org_id
195 -- INTO x_item_name, x_item_id, x_org_id
196 -- FROM AHL_MTL_ITEMS_OU_V
197 -- WHERE concatenated_segments = p_item_name AND inventory_org_id = p_org_id;
198
199 -- Fix for ADS bug# 4357001.
200 -- AnRaj:Changes made for fixing bug#4919353, issue# 1
201 /* SELECT concatenated_segments, inventory_item_id, organization_id
202 INTO x_item_name, x_item_id, x_org_id
203 FROM MTL_SYSTEM_ITEMS_KFV
204 WHERE concatenated_segments = p_item_name AND organization_id = p_org_id
205 AND organization_id IN (Select DISTINCT m.master_organization_id
206 FROM org_organization_definitions org, mtl_parameters m
207 WHERE org.organization_id = m.organization_id
208 AND NVL(org.operating_unit, mo_global.get_current_org_id())
209 = mo_global.get_current_org_id()
210 );*/
211
212 SELECT concatenated_segments, inventory_item_id, organization_id
213 INTO x_item_name, x_item_id, x_org_id
214 FROM MTL_SYSTEM_ITEMS_KFV
215 WHERE concatenated_segments = p_item_name
216 AND organization_id = p_org_id
217 AND organization_id IN
218 (SELECT DISTINCT m.master_organization_id
219 FROM INV_ORGANIZATION_INFO_V org,
220 mtl_parameters m
221 WHERE org.organization_id = m.organization_id
222 AND NVL(org.operating_unit,mo_global.get_current_org_id()) = mo_global.get_current_org_id()
223 ) ;
224 ELSIF p_item_id IS NOT NULL THEN
225 -- SELECT concatenated_segments, inventory_item_id, inventory_org_id
226 -- INTO x_item_name, x_item_id, x_org_id
227 -- FROM AHL_MTL_ITEMS_OU_V
228 -- WHERE inventory_item_id = p_item_id AND inventory_org_id = p_org_id;
229
230 -- Fix for ADS bug# 4357001.
231 /* SELECT concatenated_segments, inventory_item_id, organization_id
232 INTO x_item_name, x_item_id, x_org_id
233 FROM MTL_SYSTEM_ITEMS_KFV
234 WHERE inventory_item_id = p_item_id AND organization_id = p_org_id
235 AND organization_id IN (Select DISTINCT m.master_organization_id
236 FROM org_organization_definitions org, mtl_parameters m
237 WHERE org.organization_id = m.organization_id
238 AND NVL(org.operating_unit, mo_global.get_current_org_id())
239 = mo_global.get_current_org_id()
240 ); */
241 -- AnRaj: Changes made for fixing bug#4919353
242 SELECT concatenated_segments, inventory_item_id, organization_id
243 INTO x_item_name, x_item_id, x_org_id
244 FROM MTL_SYSTEM_ITEMS_KFV
245 WHERE inventory_item_id = p_item_id AND organization_id = p_org_id
246 AND organization_id IN (Select DISTINCT m.master_organization_id
247 FROM inv_organization_info_v org,
248 mtl_parameters m
249 WHERE org.organization_id = m.organization_id
250 AND NVL(org.operating_unit,mo_global.get_current_org_id())
251 = mo_global.get_current_org_id()
252 ) ;
253 END IF;
254 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
255 ELSE
256 x_return_status := Fnd_Api.G_RET_STS_ERROR;
257 END IF;
258
259 IF (l_log_procedure >= l_log_current_level) THEN
260 fnd_log.string(l_log_procedure,
261 L_DEBUG_KEY ||'.end',
262 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
263 '. Item Id = ' || x_item_id || ', Item Name = ' || x_item_name ||
264 ', Org Id = ' || x_org_id);
265 END IF;
266
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
270 x_error_msg_code:= 'AHL_VWP_ITEM_NOT_EXISTS';
271 WHEN TOO_MANY_ROWS THEN
272 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
273 x_error_msg_code:= 'AHL_VWP_ITEM_NOT_EXISTS';
274 WHEN OTHERS THEN
275 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
276 RAISE;
277 END Check_Item_Name_Or_Id;
278
279 -- Post 11.5.10 Enhancements
280 -- Added Procedure for Checking Project Template Name of Id.
281 --------------------------------------------------------------------
282 -- PROCEDURE
283 -- Check_Project_Template_Or_Id
284 --
285 -- PURPOSE
286 -- Procedure to check project template name and retrieve project id
287 --------------------------------------------------------------------
288 PROCEDURE Check_Project_Template_Or_Id
289 (
290 p_proj_temp_name IN VARCHAR2,
291
292 x_project_id OUT NOCOPY NUMBER,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_error_msg_code OUT NOCOPY VARCHAR2
295 )
296 IS
297 -- Define local variables
298 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Project_Template_Or_Id';
299 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
300
301 CURSOR c_proj_template(p_proj_temp_name IN VARCHAR2)
302 IS
303 -- AnRaj:Changes made for fixing bug#4919353, issue# 8
304 SELECT project_id
305 FROM PA_PROJECTS
306 WHERE name = p_proj_temp_name
307 AND TEMPLATE_FLAG = 'Y';
308
309 /*
310 -- Commented by rnahata on June 25, 2007 for Bug 6147752
311 -- Check removed to avoid having to setup Project's
312 -- Carrying-out Org as an Inventory Org
313 AND carrying_out_organization_id IN
314 ( SELECT organization_id
315 FROM INV_ORGANIZATION_INFO_V
316 WHERE NVL(operating_unit,mo_global.get_current_org_id()) =
317 mo_global.get_current_org_id()
318 );
319 */
320 /*
321 SELECT project_id
322 FROM PA_PROJECTS
323 WHERE name = p_proj_temp_name
324 AND TEMPLATE_FLAG = 'Y'
325 AND carrying_out_organization_id IN (SELECT organization_id
326 FROM org_organization_definitions
327 WHERE NVL(operating_unit, mo_global.get_current_org_id()) =
328 mo_global.get_current_org_id());
329 */
330
331 BEGIN
332
333 IF (l_log_procedure >= l_log_current_level) THEN
334 fnd_log.string(l_log_procedure,
335 L_DEBUG_KEY ||'.begin',
336 'At the start of PL SQL procedure. Project Template Name = ' || p_proj_temp_name);
337 END IF;
338
339 IF p_proj_temp_name IS NOT NULL THEN
340 OPEN c_proj_template(p_proj_temp_name);
341 FETCH c_proj_template INTO x_project_id;
342 IF c_proj_template%NOTFOUND
343 THEN
344 x_return_status := Fnd_Api.G_RET_STS_ERROR;
345 ELSE
346 IF (l_log_statement >= l_log_current_level) THEN
347 fnd_log.string(l_log_statement,
348 L_DEBUG_KEY,
349 'Project Id = ' || x_project_id);
350 END IF;
351 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
352 END IF;
353 CLOSE c_proj_template;
354 END IF;
355
356 IF (l_log_procedure >= l_log_current_level) THEN
357 fnd_log.string(l_log_procedure,
358 L_DEBUG_KEY ||'.end',
359 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
360 END IF;
361
362 EXCEPTION
363 WHEN OTHERS THEN
364 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
365 RAISE;
366 END Check_Project_Template_Or_Id;
367
368 --------------------------------------------------------------------
369 -- PROCEDURE
370 -- Check_Serial_Name_Or_Id
371 --
372 -- PURPOSE
373 -- Converts Serial Name to Instance Id
374 --------------------------------------------------------------------
375 PROCEDURE Check_Serial_Name_Or_Id
376 (p_item_id IN NUMBER,
377 p_org_id IN NUMBER,
378 p_serial_id IN NUMBER,
379 p_serial_number IN VARCHAR2,
380
381 x_serial_id OUT NOCOPY NUMBER,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_error_msg_code OUT NOCOPY VARCHAR2
384 )
385 IS
386 -- Define local variables
387 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Serial_Name_Or_Id';
388 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
389 BEGIN
390 IF (l_log_procedure >= l_log_current_level) THEN
391 fnd_log.string(l_log_procedure,
392 L_DEBUG_KEY ||'.begin',
393 'At the start of PL SQL procedure. Org Id = ' || p_org_id ||
394 ', Item Id = ' || p_item_id ||
395 ', Serial Id = ' || p_serial_id ||
396 ', Serial Number' || p_serial_number);
397 END IF;
398
399 IF (p_serial_id IS NOT NULL AND p_item_id IS NOT NULL AND p_org_id IS NOT NULL) THEN
400 SELECT Instance_Id INTO x_serial_id
401 FROM CSI_ITEM_INSTANCES
402 WHERE Instance_Id = p_serial_id AND Inventory_Item_Id = p_item_id AND Inv_Master_Organization_Id = p_org_id
403 AND ACTIVE_START_DATE <= sysdate AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
404 END IF;
405
406 IF (p_serial_number IS NOT NULL AND p_item_id IS NOT NULL AND p_org_id IS NOT NULL) THEN
407 SELECT Instance_Id INTO x_serial_id
408 FROM CSI_ITEM_INSTANCES
409 WHERE Serial_Number = p_serial_number AND Inventory_Item_Id = p_item_id AND Inv_Master_Organization_Id = p_org_id
410 AND ACTIVE_START_DATE <= sysdate AND (ACTIVE_END_DATE >= sysdate OR ACTIVE_END_DATE IS NULL);
411 END IF;
412
413 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
414
415 IF (l_log_procedure >= l_log_current_level) THEN
416 fnd_log.string(l_log_procedure,
417 L_DEBUG_KEY ||'.end',
418 'At the end of PL SQL procedure. Return Status = ' ||
419 x_return_status || ', Serial Id = ' || x_serial_id);
420 END IF;
421
422 EXCEPTION
423 WHEN NO_DATA_FOUND THEN
424 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
425 x_error_msg_code:= 'AHL_VWP_SERIAL_NOT_EXISTS';
426 WHEN TOO_MANY_ROWS THEN
427 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
428 x_error_msg_code:= 'AHL_VWP_SERIAL_NOT_EXISTS';
429 WHEN OTHERS THEN
430 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
431 RAISE;
432 END Check_Serial_Name_Or_Id;
433
434 --------------------------------------------------------------------
435 -- PROCEDURE
436 -- Check_Dept_Desc_Or_Id
437 --
438 -- PURPOSE
439 -- Converts Deparment Name to Department Id.
440 --------------------------------------------------------------------
441 PROCEDURE Check_Dept_Desc_Or_Id
442 (p_organization_id IN NUMBER,
443 p_department_id IN NUMBER,
444 p_dept_name IN VARCHAR2,
445
446 x_department_id OUT NOCOPY NUMBER,
447 x_return_status OUT NOCOPY VARCHAR2,
448 x_error_msg_code OUT NOCOPY VARCHAR2
449 )
450 IS
451 -- Define local variables
452 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Dept_Desc_Or_Id';
453 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
454 BEGIN
455 IF (l_log_procedure >= l_log_current_level) THEN
456 fnd_log.string(l_log_procedure,
457 L_DEBUG_KEY ||'.begin',
458 'At the start of PL SQL procedure. Org Id = ' || p_organization_id ||
459 ', Dept Id = ' || p_department_id || ', Dept Name = ' || p_dept_name);
460 END IF;
461 IF (p_department_id IS NOT NULL) THEN
462 SELECT department_id INTO x_department_id
463 FROM BOM_DEPARTMENTS
464 WHERE organization_id = p_organization_id
465 AND department_id = p_department_id;
466 END IF;
467
468 IF (p_dept_name IS NOT NULL) THEN
469 SELECT department_id INTO x_department_id
470 FROM BOM_DEPARTMENTS
471 WHERE organization_id = p_organization_id
472 AND description = p_dept_name;
473 END IF;
474
475 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
476
477 IF (l_log_procedure >= l_log_current_level) THEN
478 fnd_log.string(l_log_procedure,
479 L_DEBUG_KEY ||'.end',
480 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
481 '. Department Id = ' || x_department_id);
482 END IF;
483
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
487 x_error_msg_code:= 'AHL_VWP_DEPT_NOT_EXISTS';
488 WHEN TOO_MANY_ROWS THEN
489 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
490 x_error_msg_code:= 'AHL_VWP_DEPT_NOT_EXISTS';
491 WHEN OTHERS THEN
492 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
493 RAISE;
494 END Check_Dept_Desc_Or_Id;
495
496 --------------------------------------------------------------------
497 -- PROCEDURE
498 -- Check_Org_Name_Or_Id
499 --
500 -- PURPOSE
501 -- Converts Organization Name to Organization Id
502 --------------------------------------------------------------------
503 PROCEDURE Check_Org_Name_Or_Id
504 (p_organization_id IN NUMBER,
505 p_org_name IN VARCHAR2,
506
507 x_organization_id OUT NOCOPY NUMBER,
508 x_return_status OUT NOCOPY VARCHAR2,
509 x_error_msg_code OUT NOCOPY VARCHAR2
510 )
511 IS
512 -- Define local variables
513 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Org_Name_Or_Id';
514 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
515
516 BEGIN
517 IF (l_log_procedure >= l_log_current_level) THEN
518 fnd_log.string(l_log_procedure,
519 L_DEBUG_KEY ||'.begin',
520 'At the start of PL SQL procedure. Organization Id = ' || p_organization_id ||
521 'Organization Name = ' || p_org_name);
522 END IF;
523 IF (p_organization_id IS NOT NULL) THEN
524 -- AnRaj:Changes made for fixing bug#4919353, issue# 6
525 SELECT hou.organization_id
526 INTO x_organization_id
527 FROM hr_organization_units hou, mtl_parameters MP
528 WHERE hou.organization_id = mp.organization_id
529 AND hou.organization_id = p_organization_id
530 AND hou.organization_id IN
531 (SELECT organization_id
532 FROM INV_ORGANIZATION_INFO_V
533 WHERE hou.organization_id = mp.organization_id
534 AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
535 AND MP.EAM_enabled_flag='Y';
536 ELSE
537 -- AnRaj:Changes made for fixing bug#4919353, issue# 5
538 SELECT hou.organization_id
539 INTO x_organization_id
540 FROM hr_organization_units hou, mtl_parameters MP
541 WHERE hou.organization_id = mp.organization_id
542 AND hou.Name = p_org_name
543 AND hou.organization_id IN
544 (SELECT organization_id
545 FROM INV_ORGANIZATION_INFO_V
546 WHERE hou.organization_id = mp.organization_id
547 AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
548 AND MP.EAM_enabled_flag='Y';
549 END IF;
550 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
551 IF (l_log_procedure >= l_log_current_level) THEN
552 fnd_log.string(l_log_procedure,
553 L_DEBUG_KEY ||'.end',
554 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
555 'Organization Id = ' || x_organization_id);
556 END IF;
557
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
561 x_error_msg_code:= 'AHL_VWP_ORG_NOT_EXISTS';
562 WHEN TOO_MANY_ROWS THEN
563 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
564 x_error_msg_code:= 'AHL_VWP_ORG_NOT_EXISTS';
565 WHEN OTHERS THEN
566 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
567 RAISE;
568 END Check_Org_Name_Or_Id;
569
570 --------------------------------------------------------------------
571 -- PROCEDURE
572 -- Check_SR_Request_Number_Or_Id
573 --
574 -- PURPOSE
575 -- Converts Service Request Number to Service Request Id
576 --------------------------------------------------------------------
577 PROCEDURE Check_SR_Request_Number_Or_Id
578 (p_service_id IN NUMBER,
579 p_service_number IN VARCHAR2,
580
581 x_service_id OUT NOCOPY NUMBER,
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_error_msg_code OUT NOCOPY VARCHAR2
584 )
585 IS
586 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_SR_Request_Number_Or_Id';
587 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
588
589 BEGIN
590
591 IF (l_log_procedure >= l_log_current_level) THEN
592 fnd_log.string(l_log_procedure,
593 L_DEBUG_KEY ||'.begin',
594 'At the start of PL SQL procedure. SR Number= ' ||
595 p_service_number || ', SR Id = ' || p_service_id);
596 END IF;
597
598 -- yazhou 19Oct2005 Starts
599 -- Bug fix #4415024
600 IF p_service_id IS NOT NULL THEN
601 SELECT a.Incident_Id INTO x_service_id
602 FROM CS_INCIDENTS_ALL_B a, CS_INCIDENT_TYPES_VL CIT
603 WHERE a.incident_type_id = cit.incident_type_id
604 AND cit.INCIDENT_SUBTYPE = 'INC'
605 AND cit.CMRO_FLAG = 'Y'
606 AND Incident_Id = p_service_id;
607 ELSIF p_service_number IS NOT NULL THEN
608 SELECT a.Incident_Id INTO x_service_id
609 FROM CS_INCIDENTS_ALL_B a, CS_INCIDENT_TYPES_VL CIT
610 WHERE a.incident_type_id = cit.incident_type_id
611 AND cit.INCIDENT_SUBTYPE = 'INC'
612 AND cit.CMRO_FLAG = 'Y'
613 AND Incident_Number = p_service_number;
614 -- yazhou 19Oct2005 ends
615 ELSE
616 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
617 x_error_msg_code:= 'AHL_VWP_SERVICE_REQ_NOT_EXISTS';
618 END IF;
619
620 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
621
622 IF (l_log_procedure >= l_log_current_level) THEN
623 fnd_log.string(l_log_procedure,
624 L_DEBUG_KEY ||'.end',
625 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
626 'Service Id = ' || x_service_id);
627 END IF;
628
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
632 x_error_msg_code:= 'AHL_VWP_SERVICE_REQ_NOT_EXISTS';
633 WHEN TOO_MANY_ROWS THEN
634 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
635 x_error_msg_code:= 'AHL_VWP_SERVICE_REQ_NOT_EXISTS';
636 WHEN OTHERS THEN
637 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
638 RAISE;
639 END Check_SR_Request_Number_Or_Id;
640
641 --------------------------------------------------------------------
642 -- PROCEDURE
643 -- Check_Visit_is_Simulated
644 --
645 -- PURPOSE
646 -- Check if the Visit is Simulated or not
647 --------------------------------------------------------------------
648 PROCEDURE Check_Visit_is_Simulated
649 (p_Visit_id IN NUMBER,
650
651 x_bln_flag OUT NOCOPY VARCHAR2,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_error_msg_code OUT NOCOPY VARCHAR2
654 )
655 IS
656 -- Define local variables
657 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Visit_is_Simulated';
658 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
659
660 l_simulation_plan NUMBER;
661 l_simulation_plan_id NUMBER;
662 l_dummy VARCHAR2(1);
663
664 -- Define local cursors
665 CURSOR c_visit(x_id IN NUMBER)IS
666 SELECT SIMULATION_PLAN_ID FROM
667 AHL_VISITS_VL WHERE VISIT_ID = x_id;
668 /*
669 CURSOR c_sim_visit(x_id IN NUMBER) IS
670 SELECT 'x'
671 FROM ahl_simulation_plans_vl ASP
672 WHERE primary_plan_flag = 'Y'
673 AND EXISTS ( SELECT 1
674 FROM ahl_visits_b
675 WHERE visit_id = x_id
676 AND NVL(simulation_plan_id,-99) = ASP.simulation_plan_id);
677 */
678 BEGIN
679 IF (l_log_procedure >= l_log_current_level) THEN
680 fnd_log.string(l_log_procedure,
681 L_DEBUG_KEY ||'.begin',
682 'At the start of PL SQL procedure. Visit Id = ' || p_Visit_id);
683 END IF;
684 IF (p_visit_id IS NOT NULL) THEN
685 OPEN c_visit(p_visit_id);
686 FETCH c_visit INTO l_simulation_plan;
687 CLOSE c_visit;
688
689 IF (l_simulation_plan IS NOT NULL) THEN
690
691 IF (l_log_statement >= l_log_current_level)THEN
692 fnd_log.string(l_log_statement,
693 L_DEBUG_KEY,
694 'Simulation Id Check' || l_simulation_plan);
695 END IF;
696 SELECT SIMULATION_PLAN_ID INTO l_simulation_plan_id
697 FROM AHL_SIMULATION_PLANS_VL WHERE primary_plan_flag = 'Y';
698
699 IF l_simulation_plan_id = l_simulation_plan THEN
700 x_bln_flag := 'Y';
701 ELSE
702 x_bln_flag := 'N';
703 END IF;
704 END IF;
705 END IF;
706
707 /*
708 OPEN c_sim_visit(p_visit_id);
709 FETCH c_sim_visit INTO l_dummy;
710 IF c_sim_visit%FOUND THEN
711 x_bln_flag := 'Y';
712 ELSE
713 x_bln_flag := 'N';
714 END IF;
715 CLOSE c_sim_visit;
716 */
717 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
718
719 IF (l_log_procedure >= l_log_current_level) THEN
720 fnd_log.string(l_log_procedure,
721 L_DEBUG_KEY ||'.end',
722 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
723 'x_bln_flag = ' || x_bln_flag);
724 END IF;
725
726 EXCEPTION
727 WHEN NO_DATA_FOUND THEN
728 x_return_status := Fnd_Api.G_RET_STS_ERROR;
729 x_error_msg_code:= 'AHL_LTP_SIMULATION_PLAN_NOT_EXISTS';
730 WHEN TOO_MANY_ROWS THEN
731 x_return_status := Fnd_Api.G_RET_STS_ERROR;
732 x_error_msg_code:= 'AHL_LTP_SIMULATION_PLAN_NOT_EXISTS';
733 WHEN OTHERS THEN
734 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
735 RAISE;
736 END Check_Visit_is_Simulated;
737
738 --------------------------------------------------------------------
739 -- PROCEDURE
740 -- Check_Visit_Task_Number_Or_Id
741 --
742 -- PURPOSE
743 -- Converts Visit Task Number to Visit Task Id
744 --------------------------------------------------------------------
745 PROCEDURE Check_Visit_Task_Number_Or_Id
746 (p_visit_task_id IN NUMBER,
747 p_visit_task_number IN NUMBER,
748 p_visit_id IN NUMBER,
749
750 x_visit_task_id OUT NOCOPY NUMBER,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_error_msg_code OUT NOCOPY VARCHAR2
753 )
754 IS
755 -- Define local variables
756 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Visit_Task_Number_Or_Id';
757 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
758
759 --mpothuku added status_code <> 'DELETED' clause to fix #206 on 03/30/05
760 BEGIN
761 IF (l_log_procedure >= l_log_current_level) THEN
762 fnd_log.string(l_log_procedure,
763 L_DEBUG_KEY ||'.begin',
764 'At the start of PL SQL procedure. Visit Task Id = ' || p_visit_task_id ||
765 ', Visit Task Number = ' || p_visit_task_number || ', Visit Id = ' || p_visit_id);
766 END IF;
767 IF (p_visit_task_id IS NOT NULL) THEN
768 SELECT Visit_Task_Id INTO x_visit_task_id
769 FROM AHL_VISIT_TASKS_B
770 WHERE Visit_Task_Id = p_visit_task_id AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
771 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
772 ELSIF (p_visit_task_number IS NOT NULL) THEN
773 SELECT Visit_Task_Id INTO x_visit_task_id
774 FROM AHL_VISIT_TASKS_B
775 WHERE Visit_Task_Number = p_visit_task_number AND Visit_Id = p_visit_id AND status_code <> 'DELETED';
776 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
777 ELSE
778 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
779 Fnd_Message.set_name ('AHL', 'AHL_VWP_VISIT_TASKS_NOT_EXISTS');
780 Fnd_Msg_Pub.ADD;
781 END IF;
782
783 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
784 IF (l_log_procedure >= l_log_current_level) THEN
785 fnd_log.string(l_log_procedure,
786 L_DEBUG_KEY ||'.end',
787 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
788 'Visit Task Id = ' || x_visit_task_id);
789 END IF;
790 EXCEPTION
791 WHEN NO_DATA_FOUND THEN
792 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
793 x_error_msg_code:= 'AHL_VWP_VISIT_TASKS_NOT_EXISTS';
794 WHEN TOO_MANY_ROWS THEN
795 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
796 x_error_msg_code:= 'AHL_VWP_VISIT_TASKS_NOT_EXISTS';
797 WHEN OTHERS THEN
798 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
799 RAISE;
800 END Check_Visit_Task_Number_OR_ID;
801
802 --------------------------------------------------------------------
803 -- PROCEDURE
804 -- Check_Lookup_Name_Or_Id
805 --
806 -- PURPOSE
807 -- To derive the any of the lookup codes to its lookup values
808 --------------------------------------------------------------------
809 PROCEDURE Check_Lookup_Name_Or_Id
810 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
811 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
812 p_meaning IN FND_LOOKUPS.meaning%TYPE,
813 p_check_id_flag IN VARCHAR2,
814
815 x_lookup_code OUT NOCOPY VARCHAR2,
816 x_return_status OUT NOCOPY VARCHAR2)
817 IS
818 -- Define local variables
819 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Lookup_Name_Or_Id';
820 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
821 BEGIN
822 IF (l_log_procedure >= l_log_current_level) THEN
823 fnd_log.string(l_log_procedure,
824 L_DEBUG_KEY ||'.begin',
825 'At the start of PL SQL procedure. p_lookup_type = ' || p_lookup_type ||
826 ', p_lookup_code = ' || p_lookup_code ||
827 ', p_meaning = ' || p_meaning ||
828 ', p_check_id_flag = ' || p_check_id_flag);
829 END IF;
830 IF (p_lookup_code IS NOT NULL) THEN
831 IF (p_check_id_flag = 'Y') THEN
832 SELECT Lookup_Code INTO x_lookup_code
833 FROM FND_LOOKUP_VALUES_VL
834 WHERE Lookup_Type = p_lookup_type
835 AND Lookup_Code = p_lookup_code
836 AND enabled_flag = 'Y' --sowsubra FP:Bug#5758829
837 AND SYSDATE BETWEEN nvl(start_date_active,sysdate) --Sowmya Bug#5715342
838 AND NVL(end_date_active,SYSDATE);
839 ELSE
840 x_lookup_code := p_lookup_code;
841 END IF;
842 ELSE
843 SELECT Lookup_Code INTO x_lookup_code
844 FROM FND_LOOKUP_VALUES_VL
845 WHERE Lookup_Type = p_lookup_type
846 AND Meaning = p_meaning
847 AND enabled_flag = 'Y' --sowsubra FP:Bug#5758829
848 AND SYSDATE BETWEEN nvl(start_date_active,sysdate) --sowsubra FP:Bug#5758829
849 AND NVL(end_date_active,SYSDATE);
850 END IF;
851
852 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
853
854 IF (l_log_procedure >= l_log_current_level) THEN
855 fnd_log.string(l_log_procedure,
856 L_DEBUG_KEY ||'.end',
857 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
858 '. x_lookup_code = ' || x_lookup_code);
859 END IF;
860
861 EXCEPTION
862 WHEN NO_DATA_FOUND THEN
863 x_return_status := Fnd_Api.G_RET_STS_ERROR;
864 WHEN TOO_MANY_ROWS THEN
865 x_return_status := Fnd_Api.G_RET_STS_ERROR;
866 WHEN OTHERS THEN
867 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
868 RAISE;
869 END Check_Lookup_Name_Or_Id;
870
871
872 --------------------------------------------------------------------
873 -- PROCEDURE
874 -- Check_Proj_Responsibility
875 --
876 -- PURPOSE
877 -- While integrating with projects,
878 -- VWP need to check for valid project resposibilities
879 --------------------------------------------------------------------
880 PROCEDURE Check_Proj_Responsibility
881 ( x_check_project OUT NOCOPY VARCHAR2,
882 x_return_status OUT NOCOPY VARCHAR2)
883 IS
884 -- Define local variables
885 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Proj_Responsibility';
886 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
887 l_msg_count NUMBER;
888 l_responsibility_id NUMBER;
889 l_return_status VARCHAR2(1);
890 l_msg_data VARCHAR2(2000);
891 G_EXC_ERROR EXCEPTION;
892
893 -- Define local cursors
894 -- To find the responsibiltiy_id for Project Billing Superuser
895 -- Not using project superuser resposibility as the menu associated is changed in 11.5.9
896 CURSOR c_fnd_response IS
897 SELECT RESPONSIBILITY_ID
898 FROM FND_RESPONSIBILITY_VL
899 WHERE RESPONSIBILITY_KEY LIKE 'PROJECT_BILLING_SUPER_USER';
900
901 -- To find the responsibiltiy_id for Project Superuser
902 /*
903 CURSOR c_fnd_user_resp(x_resp_id IN NUMBER) IS
904 SELECT RESPONSIBILITY_ID
905 FROM FND_USER_RESP_GROUPS
906 WHERE USER_ID = Fnd_Global.USER_ID AND RESPONSIBILITY_ID = x_resp_id;
907 */
908 BEGIN
909
910 IF (l_log_procedure >= l_log_current_level) THEN
911 fnd_log.string(l_log_procedure,
912 L_DEBUG_KEY ||'.begin',
913 'At the start of PL SQL procedure.');
914 END IF;
915 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
916 -- Always return x_check_project = 'Y' as we no longer need to check for Project Billing
917 -- Superuser Resp. Part of ADS bug fix 4357001.
918 x_check_project := 'Y';
919
920 /* -- Commented out hardcoding of Responsibility. AMG's function security functions will be
921 -- included into ahlmenu instead. Part of ADS bug fix 4357001.
922 OPEN c_fnd_response;
923 FETCH c_fnd_response INTO l_responsibility_id;
924
925 IF (l_log_procedure >= l_log_current_level) THEN
926 fnd_log.string(l_log_procedure,L_DEBUG_KEY, 'Responsiblity_Id from c_fnd_response = ' || l_responsibility_id);
927 END IF;
928
929 IF c_fnd_response%NOTFOUND THEN
930 x_return_status := Fnd_Api.g_ret_sts_error;
931 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
932 Fnd_Message.set_name ('AHL', 'AHL_VWP_PROJ_NO_SUPERUSER');
933 Fnd_Msg_Pub.ADD;
934 CLOSE c_fnd_response;
935 RAISE G_EXC_ERROR;
936 END IF;
937 END IF;
938 CLOSE c_fnd_response;
939
940 OPEN c_fnd_user_resp(l_responsibility_id);
941 FETCH c_fnd_user_resp INTO l_responsibility_id;
942
943 IF (l_log_procedure >= l_log_current_level) THEN
944 fnd_log.string(l_log_procedure,L_DEBUG_KEY, 'Responsiblity_Id from c_fnd_user_resp = ' || l_responsibility_id);
945 END IF;
946
947 IF c_fnd_user_resp%NOTFOUND THEN
948 x_return_status := Fnd_Api.g_ret_sts_error;
949 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
950 Fnd_Message.set_name ('AHL', 'AHL_VWP_PROJ_NO_USER_GROUPS');
951 Fnd_Msg_Pub.ADD;
952 END IF;
953 END IF;
954 CLOSE c_fnd_user_resp;
955
956 IF x_return_status =Fnd_Api.G_RET_STS_SUCCESS THEN
957
958 IF l_responsibility_id = l_responsibility_id THEN
959 x_check_project := 'Y';
960
961 -- Project Billing Super user
962 PA_INTERFACE_UTILS_PUB.Set_Global_Info
963 ( p_api_version_number => 1.0,
964 p_responsibility_id => l_responsibility_id,
965 p_user_id => Fnd_Global.USER_ID,
966 p_msg_count => l_msg_count,
967 p_msg_data => l_msg_data,
968 */
969
970 /* Fix for Bug 4086726 on Dec 23, 2004 by JR.
971 * Commenting out the calling mode param.
972 * Need to add if required, along with build dependency and a one-off
973 * fix from PA.
974 p_return_status => l_return_status ,
975 p_calling_mode => 'PUBLISH');
976 p_return_status => l_return_status);
977 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
978 RAISE G_EXC_ERROR;
979 END IF;
980
981 ELSE
982 x_check_project := 'N';
983 x_return_status := Fnd_Api.g_ret_sts_error;
984
985 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
986 Fnd_Message.set_name ('AHL', 'AHL_VWP_PROJ_NO_RESPONSIBILITY');
987 Fnd_Msg_Pub.ADD;
988 END IF;
989 END IF;
990 END IF;
991 */
992
993 -- jaramana July 25, 2005
994 /* From Majid Ansari's email dated June 8, 2005:
995 * Before calling any AMG api, its mandatory to call PA_INTERFACE_UTILS_PUB.Set_Global_Info.
996 * This has been mentioned in the AMG doc. You cannot get rid of this call.
997 * None of the APIs will be usable.
998 */
999 -- So, reintroduced the call to PA_INTERFACE_UTILS_PUB.Set_Global_Info.
1000 -- However, passing the current responsibilty id instead of the hardcoded
1001 -- Project Billing Superuser Responsibility as was done before the ADS Bug 4357001 Fix.
1002 PA_INTERFACE_UTILS_PUB.Set_Global_Info( p_api_version_number => 1.0,
1003 p_responsibility_id => Fnd_Global.RESP_ID,
1004 p_resp_appl_id => Fnd_Global.RESP_APPL_ID,
1005 p_user_id => Fnd_Global.USER_ID,
1006 p_operating_unit_id => mo_global.get_current_org_id, -- Yazhou added for MOAC changes on 05Oct2005
1007 p_msg_count => l_msg_count,
1008 p_msg_data => l_msg_data,
1009 p_return_status => x_return_status);
1010 IF (fnd_log.level_event >= l_log_current_level) THEN
1011 fnd_log.string(fnd_log.level_event,
1012 L_DEBUG_KEY,
1013 'After calling PA_INTERFACE_UTILS_PUB.Set_Global_Info. Return Status = ' || x_return_status);
1014 END IF;
1015 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1016 IF (l_log_statement >= l_log_current_level) THEN
1017 fnd_log.string(l_log_statement,
1018 L_DEBUG_KEY,
1019 'Errors from PA_INTERFACE_UTILS_PUB.Set_Global_Info. Message count: ' ||
1020 l_msg_count || ', message data: ' || l_msg_data);
1021 END IF;
1022 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1023 RAISE FND_API.G_EXC_ERROR;
1024 ELSE
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1026 END IF;
1027 END IF;
1028 -- End Changes made by jaramana on July 25, 2005
1029
1030 IF (l_log_procedure >= l_log_current_level) THEN
1031 fnd_log.string(l_log_procedure,
1032 L_DEBUG_KEY ||'.end',
1033 'At the end of PL SQL procedure. Return Status = ' ||
1034 x_return_status || '. x_check_project = ' || x_check_project);
1035 END IF;
1036
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN
1039 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1040 WHEN TOO_MANY_ROWS THEN
1041 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1042 WHEN OTHERS THEN
1043 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1044 RAISE;
1045 END Check_Proj_Responsibility;
1046
1047 --------------------------------------------------------------------
1048 -- PROCEDURE
1049 -- Get_Serial_Item_by_Unit
1050 --
1051 -- PURPOSE
1052 -- To retrieve Inventory Item Id, Item Org Id and Instance Id from Unit Name
1053 --------------------------------------------------------------------
1054 PROCEDURE Get_Serial_Item_by_Unit
1055 ( p_unit_name IN VARCHAR2,
1056 x_instance_id OUT NOCOPY NUMBER,
1057 x_item_id OUT NOCOPY NUMBER,
1058 x_item_org_id OUT NOCOPY NUMBER,
1059 x_return_status OUT NOCOPY VARCHAR2,
1060 x_error_msg_code OUT NOCOPY VARCHAR2)
1061 IS
1062 -- Define local variables
1063 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Serial_Item_by_Unit';
1064 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1065
1066 -- Define local cursors
1067 CURSOR c_serial (p_unit_name IN VARCHAR2) IS
1068 SELECT csi_item_instance_id
1069 FROM ahl_unit_config_headers
1070 WHERE name = p_unit_name AND unit_config_status_code = 'COMPLETE'
1071 AND (active_end_date is null or active_end_date > sysdate);
1072
1073 BEGIN
1074 IF (l_log_procedure >= l_log_current_level) THEN
1075 fnd_log.string(l_log_procedure,
1076 L_DEBUG_KEY ||'.begin',
1077 'At the start of PL SQL procedure. Unit Name = ' || p_unit_name);
1078 END IF;
1079 IF (p_unit_name IS NOT NULL) THEN
1080 OPEN c_serial(p_unit_name);
1081 FETCH c_serial INTO x_instance_id;
1082 CLOSE c_serial;
1083
1084 IF (l_log_statement >= l_log_current_level) THEN
1085 fnd_log.string(l_log_statement,
1086 L_DEBUG_KEY,
1087 'Instance Id = ' || x_instance_id);
1088 END IF;
1089
1090 IF x_instance_id IS NOT NULL THEN
1091 SELECT Inventory_Item_Id, Inv_Master_Organization_Id
1092 INTO x_Item_Id, x_Item_Org_Id
1093 FROM CSI_ITEM_INSTANCES
1094 WHERE Instance_Id = x_instance_id;
1095 ELSE
1096 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERIAL_NOT_EXISTS');
1097 Fnd_Msg_Pub.ADD;
1098 RAISE Fnd_Api.G_EXC_ERROR;
1099 END IF;
1100 END IF;
1101
1102 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1103 IF (l_log_procedure >= l_log_current_level) THEN
1104 fnd_log.string(l_log_procedure,
1105 L_DEBUG_KEY ||'.end',
1106 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
1107 '. Item Id = ' || x_item_id || ' Item Org Id' || x_item_org_id);
1108 END IF;
1109 EXCEPTION
1110 WHEN NO_DATA_FOUND THEN
1111 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1112 WHEN TOO_MANY_ROWS THEN
1113 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1114 WHEN OTHERS THEN
1115 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1116 RAISE;
1117 END Get_Serial_Item_by_Unit;
1118
1119 --------------------------------------------------------------------
1120 -- PROCEDURE
1121 -- Insert_Tasks
1122 --
1123 -- PURPOSE
1124 -- To call when inserting planned/unplanned tasks
1125 --------------------------------------------------------------------
1126 PROCEDURE Insert_Tasks (
1127 p_visit_id IN NUMBER,
1128 p_unit_id IN NUMBER,
1129 p_serial_id IN NUMBER,
1130 p_service_id IN NUMBER,
1131 p_dept_id IN NUMBER,
1132 p_item_id IN NUMBER,
1133 p_item_org_id IN NUMBER,
1134 p_mr_id IN NUMBER,
1135 p_mr_route_id IN NUMBER,
1136 p_parent_id IN NUMBER,
1137 p_flag IN VARCHAR2,
1138 p_stage_id IN NUMBER := NULL,
1139 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010
1140 -- Added past start and end dates
1141 p_past_task_start_date IN DATE := NULL,
1142 p_past_task_end_date IN DATE := NULL,
1143 p_quantity IN NUMBER := NULL, -- Added by rnahata for Issue 105
1144 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
1145 p_task_start_date IN DATE := NULL,
1146 -- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
1147 p_service_type_code IN VARCHAR2 := NULL,
1148 x_task_id OUT NOCOPY NUMBER,
1149 x_return_status OUT NOCOPY VARCHAR2,
1150 x_msg_count OUT NOCOPY NUMBER,
1151 x_msg_data OUT NOCOPY VARCHAR2
1152 )
1153 IS
1154 -- Define local variables
1155 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Tasks';
1156 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1157
1158 -- Added by sjayacha for Servie Request Integration
1159
1160 CURSOR c_mr_title(c_mr_id IN NUMBER) IS
1161 SELECT title, description /*Bug 5758813- Fetch the description of the MR*/
1162 FROM ahl_mr_headers_vl
1163 WHERE mr_header_id = c_mr_id;
1164
1165 CURSOR c_unit_eff_title(c_unit_eff_id IN NUMBER) IS
1166 SELECT substrb(title,1,80), description /*Bug 5758813 - Fetch the description.*/
1167 FROM ahl_unit_effectivities_v
1168 WHERE unit_effectivity_id = c_unit_eff_id;
1169
1170 /*Bug 5758813 - rnahata - Route title and route remarks should be passed
1171 to visit as visit task name and visit task description*/
1172 CURSOR c_route_details(c_mr_route_id IN NUMBER) IS
1173 SELECT substrb(ar.title,1,80), ar.remarks,
1174 -- SKPATHAK :: 02-MAY-2011 :: VWPE :: Added below two more columns
1175 mrr.stage_type_code, ar.return_to_supply_flag
1176 FROM ahl_routes_vl ar, ahl_mr_routes mrr
1177 WHERE mrr.mr_route_id = c_mr_route_id
1178 AND mrr.route_id = ar.route_id;
1179
1180 --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: start
1181 -- cursor to fetch the stage_id of the stage, which has stage type associated that matches with the stage type of the passed task's route-MR association.
1182 CURSOR c_stage_id (c_visit_id IN NUMBER, c_mr_route_id IN NUMBER) IS
1183 SELECT stage.stage_id
1184 FROM ahl_mr_routes mr, ahl_visit_stage_typ_asoc assoc, ahl_vwp_stages_b stage
1185 WHERE assoc.stage_id = stage.stage_id
1186 AND stage.visit_id = c_visit_id
1187 AND assoc.stage_type_code = mr.stage_type_code
1188 AND mr.mr_route_id = c_mr_route_id
1189 AND stage.stage_status_code <> 'RELEASED';
1190
1191 -- MR_HEADER_ID of the Originating MR for all NR tasks.
1192 -- If the NR is created from another NR, then it should contain the Originating MR of the Originating NR
1193 -- If the NR is created from a group MR's child MR, the the originating mr is the child MR of the group MR
1194 CURSOR get_originating_mr_id (c_parent_id IN NUMBER, c_visit_id IN NUMBER) IS
1195 SELECT mr_id
1196 FROM ahl_visit_tasks_b
1197 WHERE visit_id = c_visit_id
1198 AND service_request_id IS NULL
1199 START WITH visit_task_id = c_parent_id
1200 CONNECT BY PRIOR originating_task_id = visit_task_id;
1201 --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: end
1202
1203 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539
1204 -- To find whether visit is old or new
1205 CURSOR c_get_old_visit_dets (vst_id IN NUMBER) IS
1206 SELECT AHL_VWP_VISITS_PVT.Is_Old_Visit(vst_id) IS_OLD_VISIT
1207 FROM DUAL;
1208
1209 l_is_old_visit VARCHAR2(1) := 'N';
1210
1211 -- SKPATHAK :: Bug 13574991 :: 16-FEB-2012 :: FPed all fixes on R121 codeline
1212 -- TCHIMIRA :: Bug 10088921 :: 15-NOV-2010
1213 CURSOR c_row_lock(x_id IN NUMBER) IS
1214 SELECT unit_effectivity_id FROM Ahl_Unit_effectivities_b
1215 WHERE unit_effectivity_id = x_id
1216 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1217
1218 -- SKPATHAK :: 13914410 :: 03-APR-2012
1219 Cursor get_visit_start_date (c_visit_id IN NUMBER ) IS
1220 SELECT start_date_time
1221 FROM ahl_visits_b
1222 WHERE visit_id = c_visit_id;
1223 l_visit_start_date DATE;
1224
1225 -- Local variables defined for the procedure
1226 l_msg_data VARCHAR2(2000);
1227 l_name VARCHAR2(80);
1228 l_rowid VARCHAR2(30);
1229 l_type VARCHAR2(30);
1230 l_return_status VARCHAR2(1);
1231 l_template_flag VARCHAR2(1);
1232
1233 l_msg_count NUMBER;
1234 l_task_number NUMBER;
1235 l_task_id NUMBER;
1236 l_item_id NUMBER;
1237 l_item_org_id NUMBER;
1238 l_serial_id NUMBER;
1239 l_mr_id NUMBER;
1240 l_mr_route_id NUMBER;
1241 l_description ahl_routes_vl.remarks%TYPE; --Bug 5758813
1242 l_entl_rec_tbl AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type; --salogan added for supplier warranty
1243 l_stage_id NUMBER; --VWP ER-12424063 tchimira :: 17-FEB -2011
1244 -- SKPATHAK :: 02-MAY-2011 :: VWPE
1245 l_stage_type_code VARCHAR2(30);
1246 l_return_to_supply_flag VARCHAR2(1);
1247 --VWP ER-12424063:: tchimira :: 10-MAY -2011
1248 l_originating_mr_header_id NUMBER;
1249 -- SKPATHAK :: Bug 13574991 :: 16-FEB-2012 :: FPed all fixes on R121 codeline
1250 l_lock_var NUMBER; -- bug 10088921
1251
1252 BEGIN
1253 IF (l_log_procedure >= l_log_current_level) THEN
1254 fnd_log.string(l_log_procedure,
1255 L_DEBUG_KEY ||'.begin',
1256 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
1257 ', parent id = ' || p_parent_id || ', p_serial_id = ' || p_serial_id ||
1258 ', p_item_id = ' || p_item_id || ', p_mr_id = ' || p_mr_id ||
1259 ', p_mr_route_id = ' || p_mr_route_id);
1260 END IF;
1261 IF p_visit_id IS NOT NULL THEN
1262 IF p_unit_id IS NOT NULL THEN
1263 l_type := 'PLANNED';
1264 ELSE
1265 l_type := 'UNPLANNED';
1266 IF p_serial_id IS NULL THEN
1267 x_return_status := Fnd_Api.g_ret_sts_error;
1268 IF (l_log_statement >= l_log_current_level) THEN
1269 fnd_log.string(l_log_statement,
1270 L_DEBUG_KEY,
1271 'Error in Insert_Tasks. Serial Id missing.');
1272 END IF;
1273 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1274 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERIAL_MISSING');
1275 Fnd_Msg_Pub.ADD;
1276 END IF;
1277 END IF;
1278 END IF; -- End of p_unit_id check
1279
1280 IF p_flag = 'Y' THEN
1281 l_type := 'SUMMARY';
1282 IF p_mr_id IS NOT NULL THEN
1283 -- MR Summary Task
1284 /*Bug 5758813 - rnahata*/
1285 OPEN c_mr_title(p_mr_id);
1286 FETCH c_mr_title INTO l_name, l_description;
1287 CLOSE c_mr_title;
1288 IF (l_log_statement >= l_log_current_level) THEN
1289 fnd_log.string(l_log_statement,
1290 L_DEBUG_KEY,
1291 'Summary Task. MR Title = ' || l_name || ', MR Description = ' || l_description);
1292 END IF;
1293 ELSIF p_unit_id IS NOT NULL THEN
1294 -- Added by sjayacha for Servie Request Integration
1295 /*Bug 5758813 - rnahata*/
1296 OPEN c_unit_eff_title(p_unit_id);
1297 FETCH c_unit_eff_title INTO l_name, l_description;
1298 CLOSE c_unit_eff_title;
1299 IF (l_log_statement >= l_log_current_level) THEN
1300 fnd_log.string(l_log_statement,
1301 L_DEBUG_KEY,
1302 'Summary Task. Unit Eff Title = ' || l_name || ', Unit Eff Description = ' || l_description);
1303 END IF;
1304 END IF;
1305 ELSE
1306 -- Not a Summary Task
1307 IF p_mr_route_id IS NOT NULL THEN
1308 /*Bug 5758813 - rnahata*/
1309 OPEN c_route_details(p_mr_route_id);
1310 -- SKPATHAK :: 02-MAY-2011 :: VWPE :: Fetched the two newly added columns also
1311 FETCH c_route_details INTO l_name, l_description, l_stage_type_code, l_return_to_supply_flag;
1312 CLOSE c_route_details;
1313 IF (l_log_statement >= l_log_current_level) THEN
1314 fnd_log.string(l_log_statement,
1315 L_DEBUG_KEY,
1316 'Task. Route Title = ' || l_name || ', Route Description = ' || l_description);
1317 END IF;
1318 ELSIF p_unit_id IS NOT NULL THEN
1319 -- Added by sjayacha for Service Request Integration
1320 /*Bug 5758813 - rnahata*/
1321 OPEN c_unit_eff_title(p_unit_id);
1322 FETCH c_unit_eff_title INTO l_name, l_description;
1323 CLOSE c_unit_eff_title;
1324 IF (l_log_statement >= l_log_current_level) THEN
1325 fnd_log.string(l_log_statement,
1326 L_DEBUG_KEY,
1327 'Task. Unit Eff Title = ' || l_name || ', Unit Eff Description = ' || l_description);
1328 END IF;
1329 END IF;
1330 END IF; -- Summary Task or Not
1331
1332 -- Check for the Visit Task ID and Number.
1333 l_task_ID := Get_Visit_Task_Id();
1334 l_task_number := Get_Visit_Task_Number(p_visit_id);
1335 IF (l_log_statement >= l_log_current_level) THEN
1336 fnd_log.string(l_log_statement,
1337 L_DEBUG_KEY,
1338 'Task ID = ' || l_task_id || ', Task Number = ' || l_task_number);
1339 END IF;
1340
1341 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: START
1342 OPEN c_get_old_visit_dets(p_visit_id);
1343 FETCH c_get_old_visit_dets into l_is_old_visit;
1344 CLOSE c_get_old_visit_dets;
1345
1346 IF (l_log_procedure >= l_log_current_level) THEN
1347 fnd_log.string(l_log_procedure, L_DEBUG_KEY, ' l_is_old_visit :'||l_is_old_visit);
1348 END IF;
1349 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: END
1350
1351 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: Stages based on stage types will only be effective for new visits
1352 IF l_is_old_visit <> 'Y' THEN
1353 --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: start
1354 -- Open the curor, fetch the stage id into which the task falls and pass it to p_stage_id before inserting in DB.
1355 OPEN c_stage_id(p_visit_id, p_mr_route_id);
1356 FETCH c_stage_id INTO l_stage_id;
1357 IF c_stage_id%NOTFOUND THEN
1358 l_stage_id := p_stage_id;
1359 END IF;
1360 CLOSE c_stage_id;
1361 ELSE
1362 l_stage_id := p_stage_id;
1363 END IF;
1364 IF (l_log_statement >= l_log_current_level) THEN
1365 fnd_log.string(l_log_statement,L_DEBUG_KEY,
1366 'Stage Id for the task is : ' ||l_stage_id );
1367 END IF;
1368
1369 IF p_service_id IS NOT NULL THEN
1370 OPEN get_originating_mr_id (p_parent_id, p_visit_id);
1371 FETCH get_originating_mr_id INTO l_originating_mr_header_id;
1372 CLOSE get_originating_mr_id;
1373 END IF;
1374 --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: end
1375 l_msg_count := Fnd_Msg_Pub.count_msg;
1376 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1377 x_msg_count := l_msg_count;
1378 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1379 RAISE Fnd_Api.G_EXC_ERROR;
1380 END IF;
1381
1382 IF (l_log_statement >= l_log_current_level) THEN
1383 fnd_log.string(l_log_statement,
1384 L_DEBUG_KEY,
1385 'Before calling AHL_VISIT_TASKS_PKG.Insert_Row.');
1386 END IF;
1387
1388 -- SKPATHAK :: Bug 13574991 :: 16-FEB-2012 :: FPed all fixes on R121 codeline
1389 -- TCHIMIRA :: Bug 10088921 :: 15-NOV-2010
1390 -- After fetching the UE id, we need to lock the row to prevent any other process(BUE) from deleting the row corresponding to that UE id
1391 OPEN c_row_lock(p_unit_id);
1392 FETCH c_row_lock INTO l_lock_var;
1393
1394 -- Insert a record in AHL_VISIT_TASKS base and translational tables
1395 Ahl_Visit_Tasks_Pkg.Insert_Row (
1396 X_ROWID => l_rowid,
1397 X_VISIT_TASK_ID => l_task_ID ,
1398 X_VISIT_TASK_NUMBER => l_task_number,
1399 X_OBJECT_VERSION_NUMBER => 1,
1400 X_VISIT_ID => p_visit_id,
1401 X_PROJECT_TASK_ID => NULL,
1402 X_COST_PARENT_ID => p_parent_id,
1403 X_MR_ROUTE_ID => p_mr_route_id,
1404 X_MR_ID => p_mr_id,
1405 X_DURATION => NULL,
1406 X_UNIT_EFFECTIVITY_ID => p_unit_id,
1407 X_START_FROM_HOUR => NULL,
1408 X_INVENTORY_ITEM_ID => p_item_id,
1409 X_ITEM_ORGANIZATION_ID => p_item_org_id,
1410 X_INSTANCE_ID => p_serial_id,
1411 X_PRIMARY_VISIT_TASK_ID => NULL,
1412 X_ORIGINATING_TASK_ID => p_parent_id,
1413 X_SERVICE_REQUEST_ID => p_service_id,
1414 X_TASK_TYPE_CODE => l_type,
1415 -- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
1416 X_SERVICE_TYPE_CODE => p_service_type_code,
1417 -- SKPATHAK :: 02-MAY-2011 :: VWPE :: Added below two more parameters
1418 X_STAGE_TYPE_CODE => l_stage_type_code,
1419 X_RETURN_TO_SUPPLY_FLAG => l_return_to_supply_flag,
1420 --VWP ER-12424063:: tchimira :: 10-MAY -2011
1421 X_ORIGINATING_MR_HEADER_ID => l_originating_mr_header_id,
1422 X_DEPARTMENT_ID => p_dept_id,
1423 X_SUMMARY_TASK_FLAG => 'N',
1424 X_PRICE_LIST_ID => NULL,
1425 X_STATUS_CODE => 'PLANNING',
1426 X_ESTIMATED_PRICE => NULL,
1427 X_ACTUAL_PRICE => NULL,
1428 X_ACTUAL_COST => NULL,
1429 X_STAGE_ID => l_stage_id,
1430 -- Added cxcheng POST11510--------------
1431 X_START_DATE_TIME => p_past_task_start_date,
1432 X_END_DATE_TIME => p_past_task_end_date,
1433 X_PAST_TASK_START_DATE => p_past_task_start_date,
1434 X_PAST_TASK_END_DATE => p_past_task_end_date,
1435 X_ATTRIBUTE_CATEGORY => NULL,
1436 X_ATTRIBUTE1 => NULL,
1437 X_ATTRIBUTE2 => NULL,
1438 X_ATTRIBUTE3 => NULL,
1439 X_ATTRIBUTE4 => NULL,
1440 X_ATTRIBUTE5 => NULL,
1441 X_ATTRIBUTE6 => NULL,
1442 X_ATTRIBUTE7 => NULL,
1443 X_ATTRIBUTE8 => NULL,
1444 X_ATTRIBUTE9 => NULL,
1445 X_ATTRIBUTE10 => NULL,
1446 X_ATTRIBUTE11 => NULL,
1447 X_ATTRIBUTE12 => NULL,
1448 X_ATTRIBUTE13 => NULL,
1449 X_ATTRIBUTE14 => NULL,
1450 X_ATTRIBUTE15 => NULL,
1451 X_VISIT_TASK_NAME => l_name, --Bug 5758813
1452 X_DESCRIPTION => l_description, --Bug 5758813
1453 -- Added by rnahata for Issue 105
1454 X_QUANTITY => p_quantity,
1455 X_CREATION_DATE => SYSDATE,
1456 X_CREATED_BY => Fnd_Global.USER_ID,
1457 X_LAST_UPDATE_DATE => SYSDATE,
1458 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1459 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1460
1461 -- SKPATHAK :: Bug 13574991 :: 16-FEB-2012 :: FPed all fixes on R121 codeline
1462 CLOSE c_row_lock;
1463
1464 IF (l_log_statement >= l_log_current_level) THEN
1465 fnd_log.string(l_log_statement,
1466 L_DEBUG_KEY,
1467 'After calling AHL_VISIT_TASKS_PKG.Insert_Row.');
1468 END IF;
1469
1470 x_task_id := l_task_id;
1471
1472 /* MANESING::Component Maintenance Planning Project, 05-Jul-2011
1473 * There is no need to consider Stage materials for Component Visits
1474 * as there won't be any Stage associated with them.
1475 */
1476 -- SURRKUMA :: 13914410 :: 03-APR-2012 :: Associate stage materials only if visit start date is not null
1477 OPEN get_visit_start_date(p_visit_id);
1478 FETCH get_visit_start_date INTO l_visit_start_date;
1479 CLOSE get_visit_start_date;
1480 IF (AHL_CMP_UTIL_PKG.Is_Comp_Visit (p_visit_id) = 'N' AND l_visit_start_date IS NOT NULL) THEN
1481
1482 -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 :: START
1483 -- For every MR, associate the materials based on the MR Profile to the stage tasks
1484 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1485 fnd_log.string (fnd_log.level_statement,
1486 l_debug_key,
1487 'Before calling AHL_LTP_REQST_MATRL_PVT.Associate_Stage_Materials, l_type= '||l_type
1488 );
1489 END IF;
1490 IF l_type = 'SUMMARY' THEN
1491 AHL_LTP_REQST_MATRL_PVT.Associate_Stage_Materials (
1492 p_api_version => 1.0,
1493 p_init_msg_list => Fnd_Api.G_FALSE,
1494 p_commit => Fnd_Api.G_FALSE,
1495 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1496 p_module_type => 'VST',
1497 p_visit_id => p_visit_id,
1498 p_mr_header_id => p_mr_id,
1499 p_instance_id => p_serial_id,
1500 p_ue_id => p_unit_id,
1501 x_return_status => l_return_status,
1502 x_msg_count => l_msg_count,
1503 x_msg_data => l_msg_data);
1504 END IF;
1505
1506 IF (l_log_statement >= fnd_log.g_current_runtime_level) THEN
1507 fnd_log.string(fnd_log.level_statement,
1508 L_DEBUG_KEY,
1509 'After callingAHL_LTP_REQST_MATRL_PVT.Associate_Stage_Materials. l_return_status = ' || l_return_status);
1510 END IF;
1511
1512 -- Check Error Message stack.
1513 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1514 x_msg_count := l_msg_count;
1515 x_return_status := l_return_status;
1516 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1517 RAISE Fnd_Api.g_exc_error;
1518 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1519 RAISE Fnd_Api.g_exc_unexpected_error;
1520 END IF;
1521 END IF;
1522 -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 :: END
1523
1524 END IF; -- if AHL_CMP_UTIL_PKG.Is_Comp_Visit (p_visit_id) = 'N'
1525
1526 -- salogan added for supplier warranty starts
1527 -- calling Get_Warranty_Rec_for_Create
1528 Get_Warranty_Rec_for_Create(
1529 p_task_id => x_task_id,
1530 p_instance_id => p_serial_id,
1531 p_service_req_id => p_service_id,
1532 x_warranty_entl_tbl => l_entl_rec_tbl);
1533
1534 IF (l_log_statement >= l_log_current_level) THEN
1535 fnd_log.string(l_log_statement,
1536 L_DEBUG_KEY,
1537 'Before calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements.');
1538 END IF;
1539
1540 -- calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements
1541 AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements(
1542 p_user_role => AHL_WARRANTY_ENTL_PVT.G_USER_PLANNER,
1543 p_x_warranty_entl_tbl => l_entl_rec_tbl,
1544 x_return_status => l_return_status,
1545 x_msg_count => l_msg_count,
1546 x_msg_data => l_msg_data);
1547
1548 IF (l_log_statement >= l_log_current_level) THEN
1549 fnd_log.string(l_log_statement,
1550 L_DEBUG_KEY,
1551 'After calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements. Return Status = ' ||
1552 l_return_status);
1553 END IF;
1554
1555 l_msg_count := Fnd_Msg_Pub.count_msg;
1556 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1557 x_msg_count := l_msg_count;
1558 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1559 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1560 END IF;
1561 -- salogan added for supplier warranty ends
1562
1563 -- Added cxcheng POST11510--------------
1564 --Now adjust the times derivation for task
1565 IF (l_log_statement >= l_log_current_level) THEN
1566 fnd_log.string(l_log_statement,
1567 L_DEBUG_KEY,
1568 'Before calling AHL_VWP_TIMES_PVT.Adjust_Task_Times.');
1569 END IF;
1570
1571 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
1572 -- Included the new in param p_task_start_date
1573 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Call Adjust_Task_Times only if past date is null
1574 IF p_past_task_start_date IS NULL THEN
1575 AHL_VWP_TIMES_PVT.Adjust_Task_Times
1576 (p_api_version => 1.0,
1577 p_init_msg_list => Fnd_Api.G_FALSE,
1578 p_commit => Fnd_Api.G_FALSE,
1579 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1580 x_return_status => l_return_status,
1581 x_msg_count => l_msg_count,
1582 x_msg_data => l_msg_data,
1583 p_task_id => l_task_id,
1584 p_task_start_date => p_task_start_date);
1585 END IF;
1586
1587 IF (l_log_statement >= l_log_current_level) THEN
1588 fnd_log.string(l_log_statement,
1589 L_DEBUG_KEY,
1590 'After calling AHL_VWP_TIMES_PVT.Adjust_Task_Times. Return Status = ' ||
1591 l_return_status);
1592 END IF;
1593
1594 l_msg_count := Fnd_Msg_Pub.count_msg;
1595 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1596 IF (l_log_statement >= l_log_current_level) THEN
1597 fnd_log.string(l_log_statement,
1598 L_DEBUG_KEY,
1599 'Errors from AHL_VWP_TIMES_PVT.Adjust_Task_Times. Message count: ' ||
1600 l_msg_count || ', message data: ' || l_msg_data);
1601 END IF;
1602 x_msg_count := l_msg_count;
1603 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1604 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1605 END IF;
1606
1607 x_return_status := Fnd_Api.g_ret_sts_success;
1608 IF (l_log_procedure >= l_log_current_level) THEN
1609 fnd_log.string(l_log_procedure,
1610 L_DEBUG_KEY || '.end',
1611 'Return Status = ' || x_return_status || '. Task Id = ' || x_task_id);
1612 END IF;
1613 END IF; -- p_visit_id IS NOT NULL
1614 END Insert_Tasks;
1615
1616 --------------------------------------------------------------------
1617 -- Define local functions body --
1618 --------------------------------------------------------------------
1619
1620
1621 --------------------------------------------------------------------
1622 -- FUNCTION
1623 -- Get_Cost_Originating_Id
1624 --
1625 -- PURPOSE
1626 -- To seek Cost Parent Id and Originating Task Id for the
1627 -- planned/unplanned task which has to be created.
1628 --------------------------------------------------------------------
1629 FUNCTION Get_Cost_Originating_Id (p_mr_main_id IN NUMBER, p_mr_header_id IN NUMBER)
1630 RETURN NUMBER
1631 IS
1632 -- Define local variables
1633 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Cost_Originating_Id';
1634 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1635 l_flag1 VARCHAR2(1);
1636 x_parent_id NUMBER;
1637 y NUMBER := 0;
1638 i NUMBER := 0;
1639
1640 -- Define local cursors
1641 -- To find all relationships between various MR_HEADER_IDs
1642 -- under main MR_HEADER_IDs which was retreived on basis of the Unit Effectivity
1643 CURSOR c_relationship(x_MR_id IN NUMBER) IS
1644 SELECT MR_HEADER_ID, RELATED_MR_HEADER_ID
1645 FROM AHL_MR_RELATIONSHIPS
1646 START WITH MR_HEADER_ID = (
1647 SELECT MR_HEADER_ID
1648 FROM AHL_MR_HEADERS_APP_V
1649 WHERE MR_HEADER_ID = x_MR_id)
1650 CONNECT BY PRIOR RELATED_MR_HEADER_ID = MR_HEADER_ID;
1651 relation_rec c_relationship%ROWTYPE;
1652
1653 -- Define local record type for storing MR_Id and Related MR_Header_Id
1654 TYPE Task_Rel_Type IS RECORD
1655 (MR_HEADER_ID NUMBER,
1656 RELATED_MR_HEADER_ID NUMBER);
1657
1658 -- Define local table type for storing MR_Id and Related MR_Header_Id
1659 TYPE relation_tbl IS TABLE OF Task_Rel_Type
1660 INDEX BY BINARY_INTEGER;
1661
1662 relation_rec_tbl relation_tbl;
1663
1664 BEGIN
1665 IF (l_log_procedure >= l_log_current_level) THEN
1666 fnd_log.string(l_log_procedure,
1667 L_DEBUG_KEY ||'.begin',
1668 'At the start of PL SQL function. MAIN HEADER = ' || p_MR_main_id ||
1669 'CURRENT MR HEADER = ' || p_MR_HEADER_id);
1670 END IF;
1671 -- To store all related rows of relationship with main mr_header id in a form of a table datatype
1672 OPEN c_relationship (p_mr_main_id);
1673 LOOP
1674 FETCH c_relationship INTO relation_rec;
1675 EXIT WHEN c_relationship%NOTFOUND;
1676 relation_rec_tbl(i).MR_HEADER_ID:=relation_rec.MR_HEADER_ID;
1677 relation_rec_tbl(i).RELATED_MR_HEADER_ID:=relation_rec.RELATED_MR_HEADER_ID;
1678 i:=i+1;
1679 END LOOP;
1680 CLOSE c_relationship;
1681
1682 -- Find out parent of MR_HEADER_ID as cost and parent ID
1683 IF relation_rec_tbl.COUNT > 0 THEN
1684 y := relation_rec_tbl.FIRST;
1685 LOOP
1686 IF relation_rec_tbl(y).RELATED_MR_HEADER_ID = p_MR_HEADER_id THEN
1687 IF relation_rec_tbl(y).RELATED_MR_HEADER_ID = p_mr_main_id THEN
1688 x_parent_id := NULL;
1689 l_flag1 := 'Y';
1690 EXIT WHEN l_flag1 = 'Y';
1691 ELSE
1692 x_parent_id := relation_rec_tbl(y).MR_HEADER_ID;
1693 l_flag1 := 'Y';
1694 EXIT WHEN l_flag1 = 'Y';
1695 END IF;
1696 END IF;
1697 EXIT WHEN y = relation_rec_tbl.LAST ;
1698 y :=relation_rec_tbl.NEXT(y);
1699 END LOOP;
1700 END IF;
1701
1702 IF (l_log_procedure >= l_log_current_level) THEN
1703 fnd_log.string(l_log_procedure,
1704 L_DEBUG_KEY ||'.end',
1705 'At the end of PL SQL function. Parent_Id' || x_parent_id);
1706 END IF;
1707 RETURN x_parent_id;
1708 END Get_Cost_Originating_Id;
1709
1710 --------------------------------------------------------------------
1711 -- FUNCTION
1712 -- Get_Visit_Task_Id
1713 --
1714 -- PURPOSE
1715 -- To retrieve visit task id from the sequence
1716 --------------------------------------------------------------------
1717 FUNCTION Get_Visit_Task_Id
1718 RETURN NUMBER
1719 IS
1720 -- Define local cursors
1721 CURSOR c_seq_t IS
1722 SELECT Ahl_Visit_Tasks_B_S.NEXTVAL
1723 FROM dual;
1724
1725 CURSOR c_id_exists_t (c_id IN NUMBER) IS
1726 SELECT 1
1727 FROM Ahl_Visit_Tasks_VL
1728 WHERE Visit_Task_id = c_id;
1729
1730 -- Define local variables
1731 x_visit_task_id NUMBER;
1732 l_dummy NUMBER;
1733 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Id';
1734 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1735
1736 BEGIN
1737 IF (l_log_procedure >= l_log_current_level) THEN
1738 fnd_log.string(l_log_procedure,
1739 L_DEBUG_KEY ||'.begin',
1740 'At the start of PL SQL function.');
1741 END IF;
1742
1743 -- If the ID is not passed into the API, then grab a value from the sequence.
1744 OPEN c_seq_t;
1745 FETCH c_seq_t INTO x_visit_task_id;
1746 CLOSE c_seq_t;
1747
1748 -- Check to be sure that the sequence does not exist.
1749 OPEN c_id_exists_t (x_visit_task_id);
1750 FETCH c_id_exists_t INTO l_dummy;
1751 IF c_id_exists_t%FOUND THEN
1752 x_visit_task_id := Get_Visit_Task_Id();
1753 END IF;
1754 CLOSE c_id_exists_t;
1755
1756 IF (l_log_procedure >= l_log_current_level) THEN
1757 fnd_log.string(l_log_procedure,
1758 L_DEBUG_KEY ||'.end',
1759 'At the end of PL SQL function. Visit Task Id = ' || x_visit_task_id);
1760 END IF;
1761 RETURN x_visit_task_id;
1762 END Get_Visit_Task_Id;
1763
1764 --------------------------------------------------------------------
1765 -- FUNCTION
1766 -- Get_Visit_Task_Number
1767 --
1768 -- PURPOSE
1769 -- To retrieve visit task's task number with maximum plus one criteria
1770 --------------------------------------------------------------------
1771
1772 FUNCTION Get_Visit_Task_Number(p_visit_id IN NUMBER)
1773 RETURN NUMBER
1774 IS
1775 -- To find out the maximum task number value in the visit
1776 CURSOR c_task_number IS
1777 SELECT MAX(visit_task_number)
1778 FROM Ahl_Visit_Tasks_B
1779 WHERE Visit_Id = p_visit_id;
1780
1781 x_Visit_Task_Number NUMBER;
1782 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Task_Number';
1783 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1784
1785 BEGIN
1786 IF (l_log_procedure >= l_log_current_level) THEN
1787 fnd_log.string(l_log_procedure,
1788 L_DEBUG_KEY ||'.begin',
1789 'At the start of PL SQL function. Visit Id = ' || p_visit_id);
1790 END IF;
1791
1792 -- Check for Visit Number
1793 OPEN c_Task_Number;
1794 FETCH c_Task_Number INTO x_Visit_Task_Number;
1795 CLOSE c_Task_Number;
1796 IF x_Visit_Task_Number IS NOT NULL THEN
1797 x_Visit_Task_Number := x_Visit_Task_Number + 1;
1798 ELSE
1799 x_Visit_Task_Number := 1;
1800 END IF;
1801
1802 IF (l_log_procedure >= l_log_current_level) THEN
1803 fnd_log.string(l_log_procedure,
1804 L_DEBUG_KEY ||'.end',
1805 'At the end of PL SQL function. Visit Task Number = ' || x_Visit_Task_Number);
1806 END IF;
1807 RETURN x_Visit_Task_Number;
1808 END Get_Visit_Task_Number;
1809
1810 --------------------------------------------------------------------
1811 -- PROCEDURE
1812 -- Tech_Dependency
1813 --
1814 --
1815 --------------------------------------------------------------------
1816 PROCEDURE Tech_Dependency (
1817 p_visit_id IN NUMBER,
1818 p_task_type IN VARCHAR2,
1819 p_MR_Serial_Tbl IN MR_Serial_Tbl_Type,
1820 x_return_status OUT NOCOPY VARCHAR2)
1821 IS
1822 -- Define local variables
1823 L_API_NAME CONSTANT VARCHAR2(30) := 'Tech_Dependency';
1824 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1825 l_task_rec Task_Rec_Type;
1826 l_return_status VARCHAR2(1);
1827 l_flag VARCHAR2(1);
1828 l_tsk_flag VARCHAR2(1);
1829 l_serial_id NUMBER;
1830 l_count NUMBER;
1831 l_task_link_id NUMBER;
1832 l_child_task_id NUMBER;
1833 l_parent_task_id NUMBER;
1834 l_route_id NUMBER;
1835 y NUMBER;
1836 --Added following variables by tchimira for VWP ER-12424063 on 17-FEB -2011
1837 l_msg_data VARCHAR2(2000);
1838 l_msg_count NUMBER;
1839 l_task_link_rec AHL_VWP_TASKS_LINKS_PVT.TASK_LINK_REC;
1840 --Since at engineering level we have only before and after convention which can be translated to only StartAfterEnd.
1841 --we need to pass the value for precedence constraint as Starts After End only.
1842 -- SKPATHAK :: 10-NOV-2011 :: changed STARTS AFTER END to StartsAfterEnd
1843 -- SKPATHAK :: 11-NOV-2011 :: Bug 13374723 :: changed StartsAfterEnd to STARTSAFTEREND
1844 l_prec_constraint VARCHAR2(30) :='STARTSAFTEREND';
1845
1846 -- Table type for storing Task Ids record type
1847 TYPE Task_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1848 l_parent_Task_Tbl Task_Tbl_Type;
1849 l_child_Task_Tbl Task_Tbl_Type;
1850
1851 -- Define local variables
1852 -- To find out the count for the relationship with MR Route Id
1853 CURSOR c_route_seq_ct (x_route_id IN NUMBER) IS
1854 SELECT COUNT(*) FROM AHL_MR_ROUTE_SEQUENCES_APP_V
1855 WHERE MR_ROUTE_ID = x_route_id;
1856
1857 -- To find out the the relationship between MR Route Id and Related MR Route Id
1858 CURSOR c_route_seq (x_route_id IN NUMBER) IS
1859 SELECT MR_ROUTE_ID, RELATED_MR_ROUTE_ID FROM AHL_MR_ROUTE_SEQUENCES_APP_V
1860 WHERE MR_ROUTE_ID = x_route_id;
1861 c_route_seq_rec c_route_seq%ROWTYPE;
1862
1863 -- To find out the tasks with tasktype code as planned for visits
1864 --SKPATHAK : BUG 13997419 :: 26-APR-2012 :: Compare ue id of the tasks also to get the visit tasks between which rule needs to be created
1865 CURSOR c_route_task (x_route_id IN NUMBER, x_serial_id IN NUMBER,
1866 x_id IN NUMBER, x_type IN VARCHAR2, c_ue_id IN NUMBER) IS
1867 SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_VL
1868 WHERE MR_ROUTE_ID = x_route_id AND INSTANCE_ID = x_serial_id
1869 AND VISIT_ID = x_id AND TASK_TYPE_CODE = x_type
1870 AND nvl(STATUS_CODE,'x') <> 'DELETED'
1871 AND UNIT_EFFECTIVITY_ID = c_ue_id;
1872
1873 -- To find MR_ROUTE_ID for the particular MR_HEADER_ID
1874 CURSOR c_MR_route (x_mr_id IN NUMBER) IS
1875 SELECT T1.MR_ROUTE_ID
1876 FROM AHL_MR_ROUTES_V T1,
1877 AHL_ROUTES_B T2
1878 WHERE T1.MR_HEADER_ID = x_mr_id
1879 AND T1.ROUTE_ID = T2.ROUTE_ID
1880 AND T2.REVISION_STATUS_CODE = 'COMPLETE'
1881 -- Added as of Bug# 3562914
1882 -- By shbhanda 04/22/2004
1883 AND T1.ROUTE_REVISION_NUMBER
1884 IN ( SELECT MAX(T3.ROUTE_REVISION_NUMBER)
1885 FROM AHL_MR_ROUTES_V T3
1886 WHERE T3.MR_HEADER_ID = x_mr_id
1887 AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
1888 GROUP BY T3.ROUTE_NUMBER
1889 );
1890 BEGIN
1891 ---------------------------Start of Body-------------------------------------
1892 IF (l_log_procedure >= l_log_current_level) THEN
1893 fnd_log.string(l_log_procedure,
1894 L_DEBUG_KEY ||'.begin',
1895 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
1896 ', Task Type = ' || p_task_type || ', MR count = ' || p_MR_Serial_tbl.count);
1897 END IF;
1898
1899 IF p_MR_Serial_tbl.count > 0 THEN
1900 y := p_MR_Serial_tbl.FIRST;
1901 LOOP
1902 IF (l_log_statement >= l_log_current_level)THEN
1903 fnd_log.string(l_log_statement,
1904 L_DEBUG_KEY,
1905 'MR ID = ' || p_MR_Serial_tbl(y).MR_ID);
1906 END IF;
1907
1908 -- Cursor to find all MR routes under the current MR
1909 OPEN c_MR_route (p_MR_Serial_tbl(y).MR_ID);
1910 LOOP
1911 FETCH c_MR_route INTO l_route_id;
1912 EXIT WHEN c_MR_route%NOTFOUND;
1913
1914 -- Cursor to find the count of number of MR routes
1915 -- which have parent-child relationship with the current MR Route
1916 OPEN c_route_seq_ct (l_route_id);
1917 FETCH c_route_seq_ct INTO l_count;
1918 CLOSE c_route_seq_ct;
1919
1920 IF (l_log_statement >= l_log_current_level) THEN
1921 fnd_log.string(l_log_statement,
1922 L_DEBUG_KEY,
1923 'Route sequence count = ' || l_count);
1924 END IF;
1925
1926 IF l_count > 0 THEN
1927
1928 -- To know parent-child MR Routes in AHL_MR_ROUTE_SEQUENCES
1929 OPEN c_route_seq (l_route_id);
1930 LOOP
1931 FETCH c_route_seq INTO c_route_seq_rec;
1932 EXIT WHEN c_route_seq%NOTFOUND;
1933 l_parent_task_id := 0;
1934 l_child_task_id := 0;
1935
1936 IF p_task_type = 'UNPLANNED' THEN -- For Unplanned task: Serial Id remains same for all Routes within a MR
1937 l_serial_id := p_MR_Serial_tbl(y).SERIAL_ID ;
1938
1939 IF (l_log_statement >= l_log_current_level) THEN
1940 fnd_log.string(l_log_statement,
1941 L_DEBUG_KEY,
1942 'Task type code = ' || p_task_type || ', MR ROUTE = ' || c_route_seq_rec.MR_ROUTE_ID);
1943 END IF;
1944 --SKPATHAK:: BUG 13997419 ::pass ue id as well
1945 OPEN c_route_task (c_route_seq_rec.MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type, p_MR_Serial_tbl(y).UE_ID );
1946 FETCH c_route_task INTO l_parent_task_id;
1947 CLOSE c_route_task;
1948
1949 IF (l_log_statement >= l_log_current_level) THEN
1950 fnd_log.string(l_log_statement,
1951 L_DEBUG_KEY,
1952 'Parent Id = ' || l_parent_task_id || ', RELATED MR ROUTE = ' || c_route_seq_rec.RELATED_MR_ROUTE_ID);
1953 END IF;
1954 --SKPATHAK:: BUG 13997419 ::pass ue id as well
1955 OPEN c_route_task (c_route_seq_rec.RELATED_MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type, p_MR_Serial_tbl(y).UE_ID);
1956 FETCH c_route_task INTO l_child_task_id;
1957 CLOSE c_route_task;
1958
1959 IF (l_log_statement >= l_log_current_level) THEN
1960 fnd_log.string(l_log_statement,
1961 L_DEBUG_KEY,
1962 'Child Id = ' || l_child_task_id);
1963 END IF;
1964
1965 IF l_parent_task_id <> 0 AND l_child_task_id <> 0 THEN
1966 IF (l_log_statement >= l_log_current_level) THEN
1967 fnd_log.string(l_log_statement,
1968 L_DEBUG_KEY,
1969 'Parent Id = ' || l_parent_task_id || ', Child Id = ' || l_child_task_id);
1970 END IF;
1971 l_tsk_flag := 'Y';
1972 ELSE
1973 l_tsk_flag := 'N';
1974 END IF;
1975
1976 ELSE -- Else of p_task_type = 'UNPLANNED' check
1977 -- For Planned task Serial Id are different for each MR Route within a MR,
1978 -- because of Unit effectivites relations
1979
1980 l_serial_id := p_MR_Serial_tbl(y).SERIAL_ID ;
1981 --SKPATHAK:: BUG 13997419 ::pass ue id as well
1982 OPEN c_route_task (c_route_seq_rec.MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type, p_MR_Serial_tbl(y).UE_ID);
1983 FETCH c_route_task INTO l_parent_task_id;
1984 CLOSE c_route_task;
1985 --SKPATHAK:: BUG 13997419 ::pass ue id as well
1986 OPEN c_route_task (c_route_seq_rec.RELATED_MR_ROUTE_ID, l_serial_id, p_visit_id, p_task_type, p_MR_Serial_tbl(y).UE_ID);
1987 FETCH c_route_task INTO l_child_task_id;
1988 CLOSE c_route_task;
1989
1990 IF l_parent_task_id <> 0 AND l_child_task_id <> 0 THEN
1991 l_tsk_flag := 'Y';
1992 ELSE
1993 l_tsk_flag := 'N';
1994 END IF;
1995
1996 END IF; -- End of check p_task_type = 'UNPLANNED' check
1997
1998 IF (l_log_statement >= l_log_current_level) THEN
1999 fnd_log.string(l_log_statement,
2000 L_DEBUG_KEY,
2001 'Value of L_TSK_FLAG after task type check' || l_tsk_flag);
2002 END IF;
2003
2004 IF (l_log_statement >= l_log_current_level) THEN
2005 fnd_log.string(l_log_statement,
2006 L_DEBUG_KEY,
2007 'Value of precedence constraint is ' || l_prec_constraint);
2008 END IF;
2009
2010 IF l_tsk_flag = 'Y' THEN
2011 SELECT ahl_task_links_s.nextval INTO l_task_link_id FROM DUAL;
2012
2013 IF l_task_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
2014 l_task_rec.attribute_category := NULL;
2015 END IF;
2016 --
2017 IF l_task_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
2018 l_task_rec.attribute1 := NULL;
2019 END IF;
2020 --
2021 IF l_task_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
2022 l_task_rec.attribute2 := NULL;
2023 END IF;
2024 --
2025 IF l_task_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
2026 l_task_rec.attribute3 := NULL;
2027 END IF;
2028 --
2029 IF l_task_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
2030 l_task_rec.attribute4 := NULL;
2031 END IF;
2032 --
2033 IF l_task_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
2034 l_task_rec.attribute5 := NULL;
2035 END IF;
2036 --
2037 IF l_task_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
2038 l_task_rec.attribute6 := NULL;
2039 END IF;
2040 --
2041 IF l_task_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
2042 l_task_rec.attribute7 := NULL;
2043 END IF;
2044 --
2045 IF l_task_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
2046 l_task_rec.attribute8 := NULL;
2047 END IF;
2048 --
2049 IF l_task_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
2050 l_task_rec.attribute9 := NULL;
2051 END IF;
2052 --
2053 IF l_task_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
2054 l_task_rec.attribute10 := NULL;
2055 END IF;
2056 --
2057 IF l_task_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
2058 l_task_rec.attribute11 := NULL;
2059 END IF;
2060 --
2061 IF l_task_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
2062 l_task_rec.attribute12 := NULL;
2063 END IF;
2064 --
2065 IF l_task_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
2066 l_task_rec.attribute13 := NULL;
2067 END IF;
2068 --
2069 IF l_task_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
2070 l_task_rec.attribute14 := NULL;
2071 END IF;
2072 --
2073 IF l_task_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
2074 l_task_rec.attribute15 := NULL;
2075 END IF;
2076
2077 IF (l_log_statement >= l_log_current_level) THEN
2078 fnd_log.string(l_log_statement,
2079 L_DEBUG_KEY,
2080 'Before inserting record into AHL_TASK_LINKS');
2081 END IF;
2082 --Added new 'PRECEDENCE_CONSTRAINT'column by tchimira -VWP ER-12424063
2083 INSERT INTO AHL_TASK_LINKS
2084 (
2085 TASK_LINK_ID,OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY,
2086 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, VISIT_TASK_ID, PARENT_TASK_ID,
2087 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
2088 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
2089 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,PRECEDENCE_CONSTRAINT
2090 )
2091 VALUES
2092 (
2093 l_TASK_LINK_ID, 1,sysdate, fnd_global.user_id, sysdate,
2094 fnd_global.user_id, fnd_global.user_id, l_child_task_id, l_parent_task_id,
2095 l_task_rec.ATTRIBUTE_CATEGORY, l_task_rec.ATTRIBUTE1, l_task_rec.ATTRIBUTE2,
2096 l_task_rec.ATTRIBUTE3, l_task_rec.ATTRIBUTE4, l_task_rec.ATTRIBUTE5,
2097 l_task_rec.ATTRIBUTE6, l_task_rec.ATTRIBUTE7, l_task_rec.ATTRIBUTE8,
2098 l_task_rec.ATTRIBUTE9, l_task_rec.ATTRIBUTE10,l_task_rec.ATTRIBUTE11,
2099 l_task_rec.ATTRIBUTE12, l_task_rec.ATTRIBUTE13, l_task_rec.ATTRIBUTE14,
2100 l_task_rec.ATTRIBUTE15, l_prec_constraint
2101 );
2102
2103 IF (l_log_statement >= l_log_current_level) THEN
2104 fnd_log.string(l_log_statement,
2105 L_DEBUG_KEY,
2106 'After inserting record into AHL_TASK_LINKS and before calling VALIDATE_VWP_LINKS');
2107 END IF;
2108 -- VWPE ER-12424063:: tchimira :: 31-MAY-2011:: start
2109 -- We need to validate already inserted task rule such that child task's stage does not
2110 -- start before parent task's stage.
2111 l_task_link_rec.PARENT_TASK_ID := l_parent_task_id;
2112 l_task_link_rec.visit_Task_id := l_child_task_id;
2113 l_task_link_rec.dml_operation := 'C';
2114
2115 AHL_VWP_TASKS_LINKS_PVT.VALIDATE_VWP_LINKS
2116 (
2117 x_return_status =>l_return_Status,
2118 x_msg_count =>l_msg_count,
2119 x_msg_data =>l_msg_data,
2120 p_task_link_rec =>l_task_link_rec
2121 );
2122 IF (l_log_statement >= l_log_current_level) THEN
2123 fnd_log.string(l_log_statement,
2124 L_DEBUG_KEY,
2125 'After calling VALIDATE_VWP_LINKS and return status is:'||l_return_status);
2126 END IF;
2127 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2128 l_msg_count := FND_MSG_PUB.count_msg;
2129 IF (l_log_statement >= l_log_current_level) THEN
2130 fnd_log.string(l_log_statement,
2131 L_DEBUG_KEY,
2132 'Errors from VALIDATE_VWP_LINKS. l_msg_data: ' || l_msg_data);
2133 END IF;
2134 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2135 RAISE FND_API.G_EXC_ERROR;
2136 ELSE
2137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2138 END IF;
2139 END IF; -- Return Status is not Success
2140 -- VWPE ER-12424063:: tchimira :: 31-MAY-2011:: end
2141
2142 END IF; -- -- End of check l_tsk_flag = 'Y'
2143 END LOOP;
2144 CLOSE c_route_seq;
2145 END IF; -- End of check l_count > 0
2146
2147 END LOOP;
2148 CLOSE c_MR_route;
2149
2150 EXIT WHEN y = p_MR_Serial_tbl.LAST ;
2151 y := p_MR_Serial_tbl.NEXT(y);
2152 END LOOP;
2153 END IF; -- End of check p_MR_Id_tbl.count > 0
2154 x_return_status := Fnd_Api.g_ret_sts_success;
2155 IF (l_log_procedure >= l_log_current_level) THEN
2156 fnd_log.string(l_log_procedure,
2157 L_DEBUG_KEY ||'.end',
2158 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
2159 END IF;
2160 END Tech_Dependency;
2161
2162 --------------------------------------------------------------------
2163 -- PROCEDURE
2164 -- Create_Tasks_for_MR
2165 --
2166 -- PURPOSE
2167 -- To create tasks for MR
2168 --------------------------------------------------------------------
2169 PROCEDURE Create_Tasks_for_MR
2170 ( p_visit_id IN NUMBER,
2171 p_unit_id IN NUMBER,
2172 p_item_id IN NUMBER,
2173 p_org_id IN NUMBER,
2174 p_serial_id IN NUMBER,
2175 p_mr_id IN NUMBER,
2176 p_department_id IN NUMBER,
2177 p_service_req_id IN NUMBER,
2178 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Added past task start and end dates
2179 p_past_task_start_date IN DATE := NULL,
2180 p_past_task_end_date IN DATE := NULL,
2181 -- Added by rnahata for Issue 105
2182 p_quantity IN NUMBER,
2183 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
2184 p_task_start_date IN DATE := NULL,
2185 -- AVIKUKUM :: FP:PIE ::13_OCT-2010 :: Service Type code added
2186 p_service_type_code IN VARCHAR2 := NULL,
2187 p_x_parent_MR_Id IN OUT NOCOPY NUMBER,
2188 x_return_status OUT NOCOPY VARCHAR2)
2189 IS
2190 -- Define local variables
2191 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Tasks_for_MR';
2192 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2193
2194 -- Define Cursors
2195 -- To find MR_ROUTE_ID for the particular MR_HEADER_ID
2196 CURSOR c_MR_route(x_mr_id IN NUMBER) IS
2197 -- AnRaj: Changes made for fixing bug#4919353, issue# 10
2198 SELECT T1.MR_ROUTE_ID,
2199 -- Added for 11.5.10 Changes done by Senthil.
2200 T1.STAGE
2201 FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
2202 WHERE T1.MR_HEADER_ID = x_mr_id
2203 AND T1.ROUTE_ID = T2.ROUTE_ID
2204 AND T2.REVISION_STATUS_CODE = 'COMPLETE'
2205 -- Added as of Bug# 3562914
2206 -- By shbhanda 04/22/2004
2207 AND T1.ROUTE_REVISION_NUMBER
2208 IN (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
2209 FROM AHL_MR_ROUTES_V T3
2210 WHERE T3.MR_HEADER_ID = x_mr_id
2211 AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
2212 GROUP BY T3.ROUTE_NUMBER
2213 );
2214
2215 -- To find any visit task exists for the retrieve Serial Number, Unit Effectivity ID and MR Route ID and other info
2216 CURSOR c_task (x_mroute_id IN NUMBER, x_serial_id IN NUMBER, x_unit_id IN NUMBER) IS
2217 SELECT Visit_Id, Visit_Task_id
2218 FROM AHL_VISIT_TASKS_B
2219 WHERE MR_Route_Id = x_mroute_id
2220 AND Instance_Id = x_serial_id
2221 AND Unit_Effectivity_Id = x_unit_id
2222 AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
2223 c_task_rec c_task%ROWTYPE;
2224
2225 -- To find on the basis of input unit effectivity the related information
2226 CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER, x_serial_id IN NUMBER) IS
2227 SELECT CSI.INV_MASTER_ORGANIZATION_ID, CSI.INVENTORY_ITEM_ID
2228 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
2229 WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
2230 AND AUEB.CSI_ITEM_INSTANCE_ID = x_serial_id
2231 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
2232 AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id
2233 AND AUEB.MR_HEADER_ID = x_mr_header_id;
2234
2235 -- To find any visit task exists for the retrieve Serial Number, Unit Effectivity ID and MR Route ID and other info
2236 CURSOR c_task_chk (x_id IN NUMBER, x_mr_id IN NUMBER, x_serial_id in NUMBER) IS
2237 SELECT AMHV.Title
2238 FROM AHL_VISIT_TASKS_B AVTB, AHL_MR_HEADERS_APP_V AMHV
2239 WHERE AVTB.MR_ID = AMHV.MR_HEADER_ID
2240 AND AVTB.MR_Id = x_mr_id
2241 AND AVTB.Instance_Id = x_serial_id
2242 AND AVTB.VISIT_ID = x_id
2243 AND (AVTB.STATUS_CODE IS NULL OR AVTB.STATUS_CODE <> 'DELETED');
2244 c_task_chk_rec c_task_chk%ROWTYPE;
2245
2246 CURSOR c_stage(p_stage_number IN NUMBER, p_visit_id IN NUMBER) IS
2247 SELECT stage_id,
2248 stage_name
2249 FROM ahl_vwp_stages_vl
2250 WHERE stage_num = p_stage_number
2251 AND visit_id = p_visit_id;
2252
2253 CURSOR c_mr_title(p_mr_id IN NUMBER) IS
2254 SELECT TITLE
2255 FROM ahl_mr_headers_b
2256 WHERE mr_header_id = p_mr_id;
2257
2258 CURSOR c_task_det(p_visit_task_id NUMBER) IS
2259 SELECT visit_task_id,
2260 start_date_time,
2261 end_date_time
2262 FROM ahl_visit_tasks_b
2263 WHERE visit_task_id = p_visit_task_id;
2264
2265 l_task_det c_task_det%rowtype;
2266
2267 -- Table type for storing MR Route Id
2268 TYPE MR_Route_Tbl_Type IS TABLE OF INTEGER
2269 INDEX BY BINARY_INTEGER;
2270
2271 -- Table type for storing MR Route Id
2272 -- 11.5.10 Changes done by Senthil.
2273 TYPE Stage_num_Tbl_Type IS TABLE OF INTEGER
2274 INDEX BY BINARY_INTEGER;
2275
2276 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539
2277 -- To find whether visit is old or new
2278 CURSOR c_get_old_visit_dets (vst_id IN NUMBER) IS
2279 SELECT AHL_VWP_VISITS_PVT.Is_Old_Visit(vst_id) IS_OLD_VISIT
2280 FROM DUAL;
2281
2282 l_is_old_visit VARCHAR2(1) := 'N';
2283
2284 MR_Route_Tbl MR_Route_Tbl_Type;
2285 l_Stage_num_Tbl Stage_num_Tbl_Type;
2286 l_return_status VARCHAR2(1);
2287 l_msg_data VARCHAR2(2000);
2288 l_planned_order_flag VARCHAR2(1);
2289 l_msg_count NUMBER;
2290 l_visit_id NUMBER;
2291 l_Unit_Id NUMBER;
2292 l_MR_Id NUMBER;
2293 l_serial_id NUMBER;
2294 l_service_req_id NUMBER;
2295 l_department_id NUMBER;
2296 l_parent_MR_Id NUMBER;
2297 l_org_id NUMBER;
2298 l_item_id NUMBER;
2299 l_mr_route_id NUMBER;
2300 l_task_id NUMBER;
2301 l_parent_task_id NUMBER;
2302 i NUMBER;
2303 l_stage_number NUMBER;
2304 l_stage_name VARCHAR2(80);
2305 l_stage_id NUMBER;
2306 l_mr_title VARCHAR2(80);
2307
2308 BEGIN
2309 IF (l_log_procedure >= l_log_current_level) THEN
2310 fnd_log.string(l_log_procedure,
2311 L_DEBUG_KEY ||'.begin',
2312 'At the start of PL SQL procedure. p_visit_id' || p_visit_id ||
2313 ', p_unit_id' || p_unit_id || ', p_item_id' || p_item_id ||
2314 ', p_mr_id' || p_mr_id || ', p_service_req_id' || p_service_req_id ||
2315 ', p_quantity' || p_quantity);
2316 END IF;
2317 l_visit_id := p_visit_id;
2318 l_Unit_Id := p_Unit_Id ;
2319 l_MR_Id := p_MR_Id ;
2320 l_serial_id := p_serial_id ;
2321 l_service_req_id:= p_service_req_id;
2322 l_department_id := p_department_id;
2323 l_parent_MR_Id := p_x_parent_MR_Id;
2324
2325 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: START
2326 OPEN c_get_old_visit_dets(l_visit_id);
2327 FETCH c_get_old_visit_dets into l_is_old_visit;
2328 CLOSE c_get_old_visit_dets;
2329
2330 IF (l_log_procedure >= l_log_current_level) THEN
2331 fnd_log.string(l_log_procedure, L_DEBUG_KEY, ' l_is_old_visit :'||l_is_old_visit);
2332 END IF;
2333 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: END
2334
2335 IF l_Unit_Id IS NOT NULL then
2336 -- Cursor to find MR Id, Uniteffectivty and Serial with in task entity.
2337 -- Check if the results falls in the same visit as the input visit
2338 OPEN c_task(l_MR_id, l_serial_id, l_unit_Id);
2339 FETCH c_task INTO c_task_rec;
2340
2341 IF c_task%FOUND THEN
2342 IF (l_log_statement >= l_log_current_level)THEN
2343 fnd_log.string(l_log_statement,
2344 L_DEBUG_KEY,
2345 'Inner c_task found. Unit Effectivity is found');
2346 END IF;
2347 CLOSE c_task;
2348 OPEN c_mr_title(l_MR_id);
2349 FETCH c_mr_title into l_mr_title;
2350 CLOSE c_mr_title;
2351 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_TASK_FOUND');
2352 FND_MESSAGE.SET_TOKEN('MR',l_mr_title);
2353 Fnd_Msg_Pub.ADD;
2354
2355 ELSE -- else of c_task cursor found or not
2356 --Dup-MR ER#6338208 - sowsubra - start
2357 --commented to allow duplicate MR's in a visit
2358 /*
2359 -- Cursor to find MR and Serial with in all tasks of a visit.
2360 OPEN c_task_chk(l_visit_id, l_MR_Id, l_serial_id);
2361 FETCH c_task_chk INTO c_task_chk_rec;
2362 IF c_task_chk%FOUND THEN
2363 IF (l_log_statement >= l_log_current_level)THEN
2364 fnd_log.string(l_log_statement,
2365 L_DEBUG_KEY,
2366 ' Inner c_task2 found');
2367 END IF;
2368 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_FOUND');
2369 Fnd_Message.SET_TOKEN('MR_TITLE',c_task_chk_rec.title);
2370 Fnd_Msg_Pub.ADD;
2371 ELSE
2372 */
2373 --Dup-MR ER#6338208 - sowsubra - end
2374
2375 -- To retrieve Item Id and Organization Id with the input Unit Effectivity and MR Id
2376 OPEN c_info (l_MR_Id, l_Unit_Id, l_serial_id);
2377 FETCH c_info INTO l_org_id, l_item_id;
2378 CLOSE c_info;
2379
2380 IF (l_log_statement >= l_log_current_level)THEN
2381 fnd_log.string(l_log_statement,
2382 L_DEBUG_KEY,
2383 'c_info cursor - Organization = '||l_org_id ||
2384 'and Item = ' || l_item_id);
2385 END IF;
2386 --Dup-MR ER#6338208 - sowsubra - commented out
2387 -- END IF;
2388 -- CLOSE c_task_chk;
2389 END IF;
2390 CLOSE c_task;
2391 ELSE
2392 -- Cursor to find MR and Serial with in all tasks of a visit.
2393 --Dup-MR ER#6338208 - sowsubra - start
2394 --commented to allow duplicate MR's in a visit
2395 /*
2396 OPEN c_task_chk(l_visit_id, l_MR_Id, l_serial_id);
2397 FETCH c_task_chk INTO c_task_chk_rec;
2398 IF c_task_chk%FOUND THEN
2399 IF (l_log_statement >= l_log_current_level)THEN
2400 fnd_log.string(l_log_statement,
2401 L_DEBUG_KEY,
2402 ' Inner c_task2 found');
2403 END IF;
2404 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_FOUND');
2405 Fnd_Message.SET_TOKEN('MR_TITLE',c_task_chk_rec.title);
2406 Fnd_Msg_Pub.ADD;
2407 END IF;
2408 CLOSE c_task_chk;
2409 */
2410 --Dup-MR ER#6338208 - sowsubra - end
2411
2412 l_item_id := p_item_id;
2413 l_org_id := p_org_id;
2414 END IF;
2415
2416 l_msg_count := FND_MSG_PUB.count_msg;
2417 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2418 x_return_status := FND_API.G_RET_STS_ERROR;
2419 RAISE FND_API.G_EXC_ERROR;
2420 END IF;
2421
2422 IF (l_log_statement >= l_log_current_level) THEN
2423 fnd_log.string(l_log_statement,
2424 L_DEBUG_KEY,
2425 'Before calling INSERT_TASKS for Summary Task. Task Id = ' || l_task_id);
2426 END IF;
2427
2428 INSERT_TASKS
2429 (p_visit_id => l_visit_id,
2430 p_unit_id => l_unit_id,
2431 p_serial_id => l_serial_id,
2432 p_service_id => l_service_req_id,
2433 p_dept_id => l_department_id,
2434 p_item_id => l_item_id,
2435 p_item_org_id => l_org_id,
2436 p_mr_id => l_MR_id,
2437 p_mr_route_id => NULL,
2438 p_parent_id => l_parent_MR_Id,
2439 p_flag => 'Y',
2440 P_STAGE_ID => NULL,
2441 -- Added by rnahata for Issue 105 - pass the quantity for summary task
2442 p_quantity => p_quantity,
2443 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
2444 p_task_start_date => p_task_start_date,
2445 -- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
2446 p_service_type_code => p_service_type_code,
2447 x_task_id => l_task_id,
2448 x_return_status => l_return_status,
2449 x_msg_count => l_msg_count,
2450 x_msg_data => l_msg_data);
2451
2452 IF (l_log_statement >= l_log_current_level) THEN
2453 fnd_log.string(l_log_statement,
2454 L_DEBUG_KEY,
2455 'After calling INSERT_TASKS for Summary Task. Task Id = ' || l_task_id ||
2456 '. Return Status = ' || l_return_status);
2457 END IF;
2458
2459 l_msg_count := FND_MSG_PUB.count_msg;
2460 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2461 IF (l_log_statement >= l_log_current_level) THEN
2462 fnd_log.string(l_log_statement,
2463 L_DEBUG_KEY,
2464 'Errors from INSERT_TASKS. Message count: ' ||
2465 l_msg_count || ', message data: ' || l_msg_data);
2466 END IF;
2467 x_return_status := FND_API.G_RET_STS_ERROR;
2468 RAISE FND_API.G_EXC_ERROR;
2469 END IF;
2470
2471 p_x_parent_MR_Id := l_task_id; -- To get MR as parent for child summary tasks
2472 l_parent_Task_Id := l_task_id; -- To get MR as parent for child planned/unplanned tasks
2473
2474 /*
2475 y := 0;
2476 -- To retrieve all MR Route Id's for MR Header Id
2477
2478 OPEN c_MR_route (l_MR_Id);
2479 FETCH c_MR_route INTO l_mr_route_id,l_stage_number;
2480 WHILE c_MR_route%FOUND LOOP
2481 MR_Route_Tbl(y) := l_mr_route_id;
2482 y := y + 1;
2483 FETCH c_MR_route INTO l_mr_route_id;
2484 END LOOP;
2485 CLOSE c_MR_route;
2486 */
2487
2488 -- To retrieve all MR Route Id's for MR Header Id
2489 -- 11.5.10 Changes by Senthil.
2490 -- AnRaj: Changes made for fixing bug#4919353, issue# 11
2491 SELECT T1.MR_ROUTE_ID, T1.STAGE
2492 BULK COLLECT INTO MR_Route_Tbl,l_Stage_num_Tbl
2493 FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
2494 WHERE T1.MR_HEADER_ID = l_MR_Id
2495 AND T1.ROUTE_ID = T2.ROUTE_ID
2496 AND T2.revision_status_code = 'COMPLETE'
2497 AND T1.ROUTE_REVISION_NUMBER IN
2498 (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
2499 FROM AHL_MR_ROUTES_V T3
2500 WHERE T3.MR_HEADER_ID = l_MR_Id
2501 AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
2502 GROUP BY T3.ROUTE_NUMBER
2503 );
2504
2505 -- To Create Planned Tasks
2506 i := 0 ;
2507 IF MR_Route_Tbl.COUNT > 0 THEN
2508 i := MR_Route_Tbl.FIRST;
2509 LOOP
2510 IF l_Stage_num_Tbl(i) IS NOT NULL THEN
2511 OPEN c_stage(l_Stage_num_Tbl(i),l_visit_id);
2512 FETCH c_stage INTO l_stage_id, l_stage_name ;
2513 IF c_stage%NOTFOUND THEN
2514 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: START
2515 -- On new visits, assignment of task to stage based on stage no. defined in routes is not supported
2516 IF l_is_old_visit <> 'Y' THEN
2517 l_stage_id := NULL;
2518 l_stage_name := NULL;
2519 ELSE
2520 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_NO_EXIST');
2521 Fnd_Msg_Pub.ADD;
2522 CLOSE c_stage;
2523 RAISE FND_API.G_EXC_ERROR;
2524 END IF;
2525 -- PRAKKUM :: 14-JUL-2011 :: VWPE 12730539 :: NED
2526 END IF;
2527 CLOSE c_stage;
2528 END IF;
2529 --- End of 11.5.10 Changes by Senthil.
2530
2531 l_mr_route_id := MR_Route_Tbl(i);
2532
2533 IF (l_log_statement >= l_log_current_level) THEN
2534 fnd_log.string(l_log_statement,
2535 L_DEBUG_KEY,
2536 'l_mr_route_id = ' || l_mr_route_id);
2537 fnd_log.string(l_log_statement,
2538 L_DEBUG_KEY,
2539 'Before calling INSERT_TASKS for Simple Task');
2540 END IF;
2541
2542 INSERT_TASKS
2543 (p_visit_id => l_visit_id,
2544 p_unit_id => l_unit_id,
2545 p_serial_id => l_serial_id,
2546 p_service_id => l_service_req_id,
2547 p_dept_id => l_department_id,
2548 p_item_id => l_item_id,
2549 p_item_org_id => l_org_id,
2550 p_mr_id => l_MR_Id,
2551 p_MR_Route_id => l_MR_route_id,
2552 p_parent_id => l_parent_task_id,
2553 p_flag => 'N',
2554 P_STAGE_ID => l_stage_id,
2555 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Pass past dates too
2556 p_past_task_start_date => p_past_task_start_date,
2557 p_past_task_end_date => p_past_task_end_date,
2558 -- Added by rnahata for Issue 105 - pass the quantity for the simple tasks
2559 p_quantity => p_quantity,
2560 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
2561 p_task_start_date => p_task_start_date,
2562 -- AVIKUKUM :: FP:PIE ::13-OCT-2010 :: Service Type code added
2563 p_service_type_code => p_service_type_code,
2564 x_task_id => l_task_id,
2565 x_return_status => l_return_status,
2566 x_msg_count => l_msg_count,
2567 x_msg_data => l_msg_data);
2568
2569 IF (l_log_statement >= l_log_current_level) THEN
2570 fnd_log.string(l_log_statement,
2571 L_DEBUG_KEY,
2572 'After calling INSERT_TASKS for Simple Task. Task Id = ' || l_task_id ||
2573 '. Visit ID = ' || l_visit_id);
2574 END IF;
2575
2576 l_msg_count := FND_MSG_PUB.count_msg;
2577 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2578 IF (l_log_statement >= l_log_current_level) THEN
2579 fnd_log.string(l_log_statement,
2580 L_DEBUG_KEY,
2581 'Errors from INSERT_TASKS. Message count: ' ||
2582 l_msg_count || ', message data: ' || l_msg_data);
2583 END IF;
2584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2585 RAISE FND_API.G_EXC_ERROR;
2586 END IF;
2587
2588 -- To call LTP Process Materials API for APS Integration by Shbhanda 04-Dec-03
2589 OPEN c_task_det(l_task_id);
2590 FETCH c_task_det INTO l_task_det;
2591 CLOSE c_task_det;
2592
2593 IF l_task_det.start_date_time IS NOT NULL THEN
2594
2595 IF (l_log_statement >= l_log_current_level) THEN
2596 fnd_log.string(l_log_statement,
2597 L_DEBUG_KEY,
2598 'PLANNED TASK - Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
2599 END IF;
2600 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
2601 p_api_version => 1.0,
2602 p_init_msg_list => FND_API.g_false,
2603 p_commit => FND_API.g_false,
2604 p_validation_level => FND_API.g_valid_level_full,
2605 p_visit_id => l_Visit_Id,
2606 p_visit_task_id => l_Task_Id,
2607 p_org_id => NULL,
2608 p_start_date => NULL,
2609 p_operation_flag => 'C',
2610 x_planned_order_flag => l_planned_order_flag ,
2611 x_return_status => x_return_status,
2612 x_msg_count => l_msg_count,
2613 x_msg_data => l_msg_data );
2614 IF (l_log_statement >= l_log_current_level) THEN
2615 fnd_log.string(l_log_statement,
2616 L_DEBUG_KEY,
2617 'PLANNED TASK - After calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
2618 END IF;
2619 l_msg_count := FND_MSG_PUB.count_msg;
2620 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
2621 X_return_status := FND_API.G_RET_STS_ERROR;
2622 RAISE FND_API.G_EXC_ERROR;
2623 END IF;
2624 END IF; --check for Visit Task Start Date
2625
2626 EXIT WHEN i = MR_Route_Tbl.LAST ;
2627 i := MR_Route_Tbl.NEXT(i);
2628 END LOOP;
2629 END IF;
2630
2631 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2632 IF (l_log_procedure >= l_log_current_level) THEN
2633 fnd_log.string(l_log_procedure,
2634 L_DEBUG_KEY ||'.end',
2635 'At the end of PL SQL procedure. Return Status = ' || x_return_status ||
2636 ', p_x_parent_MR_Id = ' || p_x_parent_MR_Id);
2637 END IF;
2638
2639 EXCEPTION
2640 WHEN NO_DATA_FOUND THEN
2641 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2642 WHEN TOO_MANY_ROWS THEN
2643 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2644 WHEN OTHERS THEN
2645 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2646 RAISE;
2647 END Create_Tasks_for_MR;
2648
2649
2650 --------------------------------------------------------------------
2651 -- PROCEDURE
2652 -- Check_Cost_Parent_Loop
2653 --
2654 -- PURPOSE
2655 -- To check if the cost parent task not forming loop among other tasks
2656 --------------------------------------------------------------------
2657 PROCEDURE Check_Cost_Parent_Loop
2658 (p_visit_id IN NUMBER,
2659 p_visit_task_id IN NUMBER,
2660 p_cost_parent_id IN NUMBER
2661 )
2662 IS
2663 -- Define local variables
2664 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Cost_Parent_Loop';
2665 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2666
2667 -- Define local cursors
2668 -- For all children of cost_parent_id
2669 CURSOR c_child (c_cost_id IN NUMBER, c_id IN NUMBER) IS
2670 SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
2671 WHERE VISIT_ID = c_id
2672 START WITH COST_PARENT_ID = c_cost_id
2673 CONNECT BY PRIOR VISIT_TASK_ID = COST_PARENT_ID;
2674 c_child_rec c_child%ROWTYPE;
2675
2676 BEGIN
2677 IF (l_log_procedure >= l_log_current_level) THEN
2678 fnd_log.string(l_log_procedure,
2679 L_DEBUG_KEY ||'.begin',
2680 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
2681 'Visit Task Id = ' || p_visit_task_id || 'Cost Parent Id = ' || p_cost_parent_id);
2682 END IF;
2683 -- Check for cost parent task id not forming loop
2684 IF (p_cost_parent_id IS NOT NULL AND
2685 p_cost_parent_id <> Fnd_Api.G_MISS_NUM ) THEN
2686
2687 IF p_cost_parent_id = p_visit_task_id THEN
2688 IF (l_log_statement >= l_log_current_level) THEN
2689 fnd_log.string(l_log_statement,
2690 L_DEBUG_KEY,
2691 'p_cost_parent_id = p_visit_task_id');
2692 END IF;
2693 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_COST_LOOP');
2694 Fnd_Msg_Pub.ADD;
2695 RAISE Fnd_Api.G_EXC_ERROR;
2696 END IF;
2697
2698 OPEN c_child (p_visit_task_id, p_visit_id);
2699 LOOP
2700 FETCH c_child INTO c_child_rec;
2701
2702 IF p_cost_parent_id = c_child_rec.VISIT_TASK_ID THEN
2703 IF (l_log_statement >= l_log_current_level) THEN
2704 fnd_log.string(l_log_statement,
2705 L_DEBUG_KEY,
2706 'TASK LOOP');
2707 END IF;
2708 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_COST_LOOP');
2709 Fnd_Msg_Pub.ADD;
2710 CLOSE c_child;
2711 RAISE Fnd_Api.G_EXC_ERROR;
2712 END IF;
2713 EXIT WHEN c_child%NOTFOUND;
2714 END LOOP;
2715 CLOSE c_child;
2716 END IF;
2717 IF (l_log_procedure >= l_log_current_level) THEN
2718 fnd_log.string(l_log_procedure,
2719 L_DEBUG_KEY ||'.end',
2720 'At the end of PL SQL procedure.');
2721 END IF;
2722
2723 END Check_Cost_Parent_Loop;
2724
2725 --------------------------------------------------------------------
2726 -- PROCEDURE
2727 -- Check_Origin_Task_Loop
2728 --
2729 -- PURPOSE
2730 -- To check if the originating task not forming loop among other tasks
2731 --------------------------------------------------------------------
2732 PROCEDURE Check_Origin_Task_Loop
2733 (p_visit_id IN NUMBER,
2734 p_visit_task_id IN NUMBER,
2735 p_originating_task_id IN NUMBER
2736 )
2737 IS
2738 -- Define local variables
2739 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Origin_Task_Loop';
2740 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2741
2742 -- Define local cursors
2743 -- For all children of originating_task_id
2744 CURSOR c_child (x_org_id IN NUMBER, x_id IN NUMBER) IS
2745 SELECT VISIT_TASK_ID FROM AHL_VISIT_TASKS_B
2746 WHERE VISIT_ID = x_id
2747 AND NVL(STATUS_CODE,'X') <> 'DELETED'
2748 START WITH ORIGINATING_TASK_ID = x_org_id
2749 CONNECT BY PRIOR VISIT_TASK_ID = ORIGINATING_TASK_ID;
2750 c_child_rec c_child%ROWTYPE;
2751
2752 BEGIN
2753 IF (l_log_procedure >= l_log_current_level) THEN
2754 fnd_log.string(l_log_procedure,
2755 L_DEBUG_KEY ||'.begin',
2756 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id || '. Visit Task Id = ' ||
2757 p_visit_task_id || '. Originating Task Id' || p_originating_task_id);
2758 END IF;
2759 -- Check for originating task id not forming loop
2760 IF (p_originating_task_id IS NOT NULL AND
2761 p_originating_task_id <> Fnd_Api.G_MISS_NUM ) THEN
2762
2763 IF p_originating_task_id = p_visit_task_id THEN
2764 IF (l_log_statement >= l_log_current_level) THEN
2765 fnd_log.string(l_log_statement,
2766 L_DEBUG_KEY,
2767 'TASK LOOP1');
2768 END IF;
2769 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_ORIGIN_LOOP');
2770 Fnd_Msg_Pub.ADD;
2771 RAISE Fnd_Api.G_EXC_ERROR;
2772 END IF;
2773
2774 OPEN c_child (p_visit_task_id, p_visit_id);
2775 LOOP
2776 FETCH c_child INTO c_child_rec;
2777
2778 IF p_originating_task_id = c_child_rec.VISIT_TASK_ID THEN
2779 IF (l_log_statement >= l_log_current_level) THEN
2780 fnd_log.string(l_log_statement,
2781 L_DEBUG_KEY,
2782 'TASK LOOP2');
2783 END IF;
2784
2785 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_ORIGIN_LOOP');
2786 Fnd_Msg_Pub.ADD;
2787 CLOSE c_child;
2788 RAISE Fnd_Api.G_EXC_ERROR;
2789 END IF;
2790
2791 EXIT WHEN c_child%NOTFOUND;
2792 END LOOP;
2793 CLOSE c_child;
2794 END IF;
2795 IF (l_log_procedure >= l_log_current_level) THEN
2796 fnd_log.string(l_log_procedure,
2797 L_DEBUG_KEY ||'.end',
2798 'At the end of PL SQL procedure.');
2799 END IF;
2800 END Check_Origin_Task_Loop;
2801
2802 --------------------------------------------------------------------
2803 -- PROCEDURE
2804 -- Update_Visit_Task_Flag
2805 --
2806 -- PURPOSE
2807 -- To update visit entity any_task_chg_flag attribute whenever there
2808 -- are changes in visit task - either addition or deletion or change
2809 -- in cost parent of any task
2810 --------------------------------------------------------------------
2811 PROCEDURE Update_Visit_Task_Flag
2812 (p_visit_id IN NUMBER,
2813 p_flag IN VARCHAR2,
2814 x_return_status OUT NOCOPY VARCHAR2
2815 )
2816 IS
2817 -- Define local variables
2818 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Visit_Task_Flag';
2819 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2820
2821 BEGIN
2822 IF (l_log_procedure >= l_log_current_level) THEN
2823 fnd_log.string(l_log_procedure,
2824 L_DEBUG_KEY ||'.begin',
2825 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id ||
2826 'p_flag' || p_flag);
2827 END IF;
2828 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2829 IF p_visit_id IS NOT NULL THEN
2830 UPDATE AHL_VISITS_B
2831 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
2832 --TCHIMIRA::BUG 9222622 ::15-DEC-2009::UPDATE WHO COLUMNS
2833 LAST_UPDATE_DATE = SYSDATE,
2834 LAST_UPDATED_BY = Fnd_Global.USER_ID,
2835 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID,
2836 ANY_TASK_CHG_FLAG = p_flag
2837 WHERE VISIT_ID = p_visit_id;
2838 END IF;
2839 IF (l_log_procedure >= l_log_current_level) THEN
2840 fnd_log.string(l_log_procedure,
2841 L_DEBUG_KEY ||'.end',
2842 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
2843 END IF;
2844 END Update_Visit_Task_Flag;
2845
2846 --------------------------------------------------------------------
2847 -- PROCEDURE
2848 -- Check_Price_List_Name_Or_Id
2849 --
2850 -- PURPOSE
2851 -- To find out price list id for price list name for a visit or tasks
2852 --------------------------------------------------------------------
2853 PROCEDURE Check_Price_List_Name_Or_Id(
2854 p_visit_id IN NUMBER,
2855 p_price_list_name IN VARCHAR2,
2856 x_price_list_id OUT NOCOPY NUMBER,
2857 x_return_status OUT NOCOPY VARCHAR2
2858 ) IS
2859 -- Define local variables
2860 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Price_List_Name_Or_Id';
2861 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
2862
2863 CURSOR visit_info_csr(p_visit_id IN NUMBER)IS
2864 SELECT service_request_id FROM ahl_visits_b
2865 WHERE visit_id = p_visit_id;
2866
2867 l_service_request_id NUMBER;
2868
2869 CURSOR customer_id_csr(p_service_request_id IN NUMBER)IS
2870 SELECT customer_id FROM CS_INCIDENTS_ALL_B
2871 WHERE incident_id = p_service_request_id;
2872
2873 l_customer_id NUMBER;
2874
2875 /*CURSOR price_list_id_csr(p_price_list_name IN VARCHAR2,p_customer_id IN NUMBER)IS
2876 SELECT qlhv.list_header_id
2877 FROM qp_list_headers_vl qlhv, FINANCIALS_SYSTEM_PARAMETERS FSP, qp_qualifiers qpq, GL_SETS_OF_BOOKS GSB
2878 WHERE FSP.set_of_books_id = GSB.set_of_books_id
2879 AND qlhv.list_type_code = 'PRL'
2880 AND qlhv.currency_code = gsb.currency_code
2881 AND UPPER(qlhv.name) like UPPER(p_price_list_name)
2882 AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
2883 AND qpq.list_header_id=qlhv.list_header_id
2884 AND qpq.qualifier_context = 'CUSTOMER'
2885 AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16'
2886 UNION
2887 SELECT qlhv.list_header_id
2888 FROM qp_list_headers_vl qlhv,oe_agreements oa, qp_qualifiers qpq, FINANCIALS_SYSTEM_PARAMETERS FSP, GL_SETS_OF_BOOKS GSB
2889 WHERE FSP.set_of_books_id = GSB.set_of_books_id
2890 AND ((oa.price_list_id = qlhv.list_header_id AND qlhv.list_type_code
2891 IN('PRL', 'AGR')) OR qlhv.list_type_code = 'PRL')
2892 AND qlhv.currency_code = gsb.currency_code
2893 AND UPPER(qlhv.name) like UPPER(p_price_list_name)
2894 AND qpq.QUALIFIER_ATTR_VALUE = p_customer_id
2895 AND qpq.list_header_id=qlhv.list_header_id
2896 AND qpq.qualifier_context = 'CUSTOMER'
2897 AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';*/
2898
2899 CURSOR price_list_id_csr(p_price_list_name IN VARCHAR2,p_customer_id IN NUMBER)IS
2900 SELECT qlhv.list_header_id
2901 from qp_list_headers_vl qlhv, qp_qualifiers qpq
2902 where qlhv.list_type_code = 'PRL'
2903 and upper(qlhv.name) like upper(p_price_list_name)
2904 and qpq.QUALIFIER_ATTR_VALUE = p_customer_id
2905 and qpq.list_header_id=qlhv.list_header_id
2906 and qpq.qualifier_context = 'CUSTOMER'
2907 and qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE16';
2908
2909 BEGIN
2910 IF (l_log_procedure >= l_log_current_level) THEN
2911 fnd_log.string(l_log_procedure,
2912 L_DEBUG_KEY ||'.begin',
2913 'At the start of PL SQL procedure. Visit Id = ' ||
2914 p_visit_id || 'Price List Name = ' || p_price_list_name);
2915 END IF;
2916 -- Initialize API return status to success
2917 x_return_status := FND_API.G_RET_STS_SUCCESS;
2918
2919 OPEN visit_info_csr(p_visit_id);
2920 FETCH visit_info_csr INTO l_service_request_id;
2921 IF (visit_info_csr%NOTFOUND) THEN
2922 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT');
2923 FND_MSG_PUB.ADD;
2924 IF (l_log_unexpected >= l_log_current_level)THEN
2925 fnd_log.string(l_log_unexpected,
2926 L_DEBUG_KEY,
2927 'Visit id not found in AHL_VISITS_B table');
2928 END IF;
2929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2930 ELSIF(l_service_request_id IS NULL)THEN
2931 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_VISIT_UPDT_NOS');
2932 FND_MSG_PUB.ADD;
2933 IF (fnd_log.level_error >= l_log_current_level)THEN
2934 fnd_log.string(fnd_log.level_error,
2935 L_DEBUG_KEY,
2936 'price list can not be associated because service request id is not associated to visit');
2937 END IF;
2938 x_return_status := FND_API.G_RET_STS_ERROR;
2939 END IF;
2940 CLOSE visit_info_csr;
2941
2942 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2943 RETURN;
2944 END IF;
2945
2946 OPEN customer_id_csr(l_service_request_id);
2947 FETCH customer_id_csr INTO l_customer_id;
2948 IF(customer_id_csr%NOTFOUND)THEN
2949 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_SR_ID');
2950 FND_MSG_PUB.ADD;
2951 IF (l_log_unexpected >= l_log_current_level)THEN
2952 fnd_log.string(l_log_unexpected,
2953 L_DEBUG_KEY,
2954 'Associated Service Request ' || l_service_request_id ||
2955 ' is invalid as record not found.');
2956 END IF;
2957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2958 ELSIF(l_customer_id IS NULL)THEN
2959 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_INV_SRVREQ_NOCUST');
2960 FND_MSG_PUB.ADD;
2961 IF (fnd_log.level_error >= l_log_current_level)THEN
2962 fnd_log.string(fnd_log.level_error,
2963 L_DEBUG_KEY,
2964 'Customer id for corresponding service request ' || l_service_request_id || ' is null.');
2965 END IF;
2966 x_return_status := FND_API.G_RET_STS_ERROR;
2967 END IF;
2968 CLOSE customer_id_csr;
2969
2970 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2971 RETURN;
2972 END IF;
2973
2974 -- find out the price list id
2975 OPEN price_list_id_csr(p_price_list_name,l_customer_id);
2976 FETCH price_list_id_csr INTO x_price_list_id;
2977 IF(price_list_id_csr%NOTFOUND)THEN
2978 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_PLIST_NFOUND');
2979 FND_MESSAGE.Set_Token('PRICE_LIST',p_price_list_name);
2980 FND_MSG_PUB.ADD;
2981 IF (fnd_log.level_error >= l_log_current_level)THEN
2982 fnd_log.string(fnd_log.level_error,
2983 L_DEBUG_KEY,
2984 'Valid price list not found with price list name ' || p_price_list_name);
2985 END IF;
2986 x_return_status := FND_API.G_RET_STS_ERROR;
2987 END IF;
2988 CLOSE price_list_id_csr;
2989
2990 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2991 RETURN;
2992 END IF;
2993 IF (l_log_procedure >= l_log_current_level) THEN
2994 fnd_log.string(l_log_procedure,
2995 L_DEBUG_KEY ||'.end',
2996 'At the end of PL SQL procedure. x_price_list_id = ' || x_price_list_id);
2997 END IF;
2998
2999 END Check_Price_List_Name_Or_Id;
3000 --------------------------------------------------------------------
3001 -- PROCEDURE
3002 -- Update_Cost_Origin_Task
3003 --
3004 -- PURPOSE
3005 -- To update all tasks which have the deleting task as cost or originating task
3006 --------------------------------------------------------------------
3007 PROCEDURE Update_Cost_Origin_Task
3008 (p_visit_task_id IN NUMBER,
3009 x_return_status OUT NOCOPY VARCHAR2
3010 )
3011 IS
3012 -- Define local variables
3013 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Cost_Origin_Task';
3014 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3015 l_task_id NUMBER;
3016
3017 -- To find any task which have originating id as deleted task id
3018 CURSOR c_origin (x_task_id IN NUMBER) IS
3019 SELECT Visit_Task_Id, Object_Version_Number
3020 FROM Ahl_Visit_Tasks_VL
3021 WHERE ORIGINATING_TASK_ID = x_task_id;
3022 c_origin_rec c_origin%ROWTYPE;
3023
3024 -- To find any task which have parent id as deleted task id
3025 CURSOR c_parent (x_task_id IN NUMBER) IS
3026 SELECT Visit_Task_Id, Object_Version_Number
3027 FROM Ahl_Visit_Tasks_VL
3028 WHERE COST_PARENT_ID = x_task_id;
3029 c_parent_rec c_parent%ROWTYPE;
3030
3031 -- Post 11.5.10
3032 --RR
3033 -- For updating the Cost Parent Task
3034 CURSOR c_parent_id(x_task_id IN NUMBER) IS
3035 SELECT cost_parent_id
3036 FROM AHL_VISIT_TASKS_VL
3037 WHERE visit_task_id = x_task_id;
3038 l_parent_id NUMBER;
3039 -- Post 11.5.10
3040 --RR
3041 BEGIN
3042
3043 IF (l_log_procedure >= l_log_current_level) THEN
3044 fnd_log.string(l_log_procedure,
3045 L_DEBUG_KEY ||'.begin',
3046 'At the start of PL SQL procedure. Visit Task Id = ' || p_visit_task_id);
3047 END IF;
3048 l_task_id := p_visit_task_id;
3049
3050 -- Post 11.5.10
3051 -- RR
3052 OPEN c_parent_id(l_task_id);
3053 FETCH c_parent_id INTO l_parent_id;
3054 CLOSE c_parent_id;
3055
3056 -- To find if a task deleted is the "originating task" for another task,
3057 -- then association/s must be removed before the task can be deleted.
3058 OPEN c_origin (l_task_id);
3059 LOOP
3060 FETCH c_origin INTO c_origin_rec;
3061 EXIT WHEN c_origin%NOTFOUND;
3062 IF c_origin_rec.visit_task_id IS NOT NULL THEN
3063 IF (l_log_statement >= l_log_current_level) THEN
3064 fnd_log.string(l_log_statement,
3065 L_DEBUG_KEY,
3066 'update origin');
3067 END IF;
3068 UPDATE AHL_VISIT_TASKS_B SET ORIGINATING_TASK_ID = NULL,
3069 OBJECT_VERSION_NUMBER = c_origin_rec.object_version_number + 1
3070 WHERE VISIT_TASK_ID = c_origin_rec.visit_task_id;
3071 END IF;
3072 END LOOP;
3073 CLOSE c_origin;
3074
3075 -- To find if a task deleted is the "cost parent task" for another task,
3076 -- then association/s must be removed before the task can be deleted.
3077 -- Post 11.5.10
3078 -- RR
3079 OPEN c_parent (l_task_id);
3080 LOOP
3081 FETCH c_parent INTO c_parent_rec;
3082 EXIT WHEN c_parent%NOTFOUND;
3083 IF c_parent_rec.visit_task_id IS NOT NULL THEN
3084 IF (l_log_statement >= l_log_current_level) THEN
3085 fnd_log.string(l_log_statement,
3086 L_DEBUG_KEY,
3087 'update parent');
3088 END IF;
3089 UPDATE AHL_VISIT_TASKS_B SET COST_PARENT_ID = l_parent_id,
3090 OBJECT_VERSION_NUMBER = c_parent_rec.object_version_number + 1
3091 WHERE VISIT_TASK_ID = c_parent_rec.visit_task_id;
3092 END IF;
3093 END LOOP;
3094 CLOSE c_parent;
3095 -- RR
3096 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3097 IF (l_log_procedure >= l_log_current_level) THEN
3098 fnd_log.string(l_log_procedure,
3099 L_DEBUG_KEY ||'.end',
3100 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
3101 END IF;
3102 END Update_Cost_Origin_Task;
3103
3104 --------------------------------------------------------------------
3105 -- PROCEDURE
3106 -- Merge_for_Unique_Items
3107 --
3108 -- PURPOSE
3109 -- To merge two item tables and remove the redundant items
3110 -- in table for which no price is defined
3111 --------------------------------------------------------------------
3112 PROCEDURE Merge_for_Unique_Items
3113 (p_item_tbl1 IN Item_Tbl_Type,
3114 p_item_tbl2 IN Item_Tbl_Type,
3115 x_item_tbl OUT NOCOPY Item_Tbl_Type
3116 )
3117 IS
3118 -- Define local variables
3119 L_API_NAME CONSTANT VARCHAR2(30) := 'Merge_for_Unique_Items';
3120 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3121 l_index NUMBER:=0;
3122 l_item_present boolean:=false;
3123 BEGIN
3124
3125 IF (l_log_procedure >= l_log_current_level) THEN
3126 fnd_log.string(l_log_procedure,
3127 L_DEBUG_KEY ||'.begin',
3128 'At the start of PL SQL procedure. p_item_tbl1.count = ' || p_item_tbl1.count ||
3129 '. p_item_tbl2.count = ' || p_item_tbl2.count);
3130 END IF;
3131
3132 IF p_item_tbl1.count > 0 THEN
3133 x_item_tbl := p_item_tbl1;
3134 l_index:=x_item_tbl.count;
3135 ELSE
3136 x_item_tbl := p_item_tbl2;
3137 RETURN;
3138 END IF;
3139
3140 IF p_item_tbl2.count > 0 THEN
3141 FOR i IN p_item_tbl2.first ..p_item_tbl2.last
3142 LOOP
3143 l_item_present:=false;
3144 IF x_item_tbl.count > 0 THEN
3145 FOR k IN x_item_tbl.first .. x_item_tbl.last
3146 LOOP
3147 IF x_item_tbl(k).item_id=p_item_tbl2(i).item_id
3148 AND x_item_tbl(k).uom_code=p_item_tbl2(i).uom_code
3149 AND x_item_tbl(k).effective_date=p_item_tbl2(i).effective_date
3150 THEN
3151 IF x_item_tbl(k).duration is not NULL AND x_item_tbl(k).duration <> FND_API.G_MISS_NUM
3152 AND p_item_tbl2(i).duration is not NULL AND p_item_tbl2(i).duration <> FND_API.G_MISS_NUM THEN
3153 x_item_tbl(k).duration := nvl(x_item_tbl(k).duration,0)+nvl(p_item_tbl2(i).duration,0);
3154 l_item_present:=true;
3155 ELSIF (x_item_tbl(k).duration is NULL OR x_item_tbl(k).duration = FND_API.G_MISS_NUM )
3156 AND (p_item_tbl2(i).duration is NULL OR p_item_tbl2(i).duration = FND_API.G_MISS_NUM) THEN
3157 x_item_tbl(k).quantity := nvl(x_item_tbl(k).quantity,0)+nvl(p_item_tbl2(i).quantity,0);
3158 l_item_present:=true;
3159 END IF;
3160 END IF;
3161 END LOOP;
3162 END IF;
3163
3164 IF l_item_present=FALSE THEN
3165 l_index:=l_index+1;
3166 x_item_tbl(l_index) := p_item_tbl2(i);
3167 END IF;
3168 END LOOP;
3169 END IF;
3170
3171 IF (l_log_procedure >= l_log_current_level) THEN
3172 fnd_log.string(l_log_procedure,
3173 L_DEBUG_KEY ||'.end',
3174 'At the end of PL SQL procedure. x_item_tbl.COUNT = ' || x_item_tbl.COUNT);
3175 END IF;
3176
3177 END Merge_for_Unique_Items;
3178
3179 -------------------------------------------------------------------
3180 -- PROCEDURE
3181 -- Check_Item_in_Price_List
3182 --
3183 -- PURPOSE
3184 -- To Check if item of MR is defined in price list.
3185 --------------------------------------------------------------------
3186 /* commented as this is not being used anywhere
3187 PROCEDURE Check_Item_in_Price_List
3188 (p_price_list IN NUMBER,
3189 p_item_id IN NUMBER,
3190 x_item_chk_flag OUT NOCOPY NUMBER
3191 )
3192 IS
3193 -- Define local variables
3194 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Item_in_Price_List';
3195 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3196 l_item_chk_flag VARCHAR2(1) ;
3197 I NUMBER;
3198
3199 CURSOR c_task(p_visit_id NUMBER, p_tsk_id NUMBER) IS
3200 SELECT visit_task_id, originating_task_id
3201 FROM AHL_VISIT_TASKS_B
3202 WHERE VISIT_ID = p_visit_id
3203 AND VISIT_TASK_ID = p_tsk_id
3204 AND (STATUS_CODE IS NULL OR STATUS_CODE <> 'DELETED');
3205 c_task_rec c_task%ROWTYPE;
3206
3207 BEGIN
3208
3209 OPEN c_task(p_price_list, p_item_id);
3210 LOOP
3211 FETCH c_task INTO c_task_rec;
3212 EXIT WHEN c_task%NOTFOUND;
3213
3214 IF c_task_rec.originating_task_id IS NOT NULL THEN
3215 l_item_chk_flag := 'Y';
3216 Check_Item_in_Price_List
3217 ( p_price_list => p_price_list,
3218 p_item_id => c_task_rec.originating_task_id,
3219 x_item_chk_flag => l_item_chk_flag);
3220
3221 IF (l_log_procedure >= l_log_current_level)THEN
3222 fnd_log.string(l_log_procedure,
3223 L_DEBUG_KEY||'.end',
3224 'Check for RECURSIVE task id = ' || p_item_id);
3225 END IF;
3226 ELSE
3227 l_item_chk_flag := 'N';
3228 IF (l_log_procedure >= l_log_current_level)THEN
3229 fnd_log.string(l_log_procedure,
3230 L_DEBUG_KEY||'.end',
3231 'Check for NON RECURSIVE task id = ' || p_item_id);
3232 END IF;
3233 END IF;
3234 I:=I+1;
3235 END LOOP;
3236 CLOSE c_task;
3237
3238 END Check_Item_in_Price_List;
3239 */
3240
3241 --------------------------------------------------------------------
3242 -- PROCEDURE
3243 -- Check_Currency_for_Costing
3244 --
3245 -- PURPOSE
3246 -- To used to retrieve currency code and pass as input parameter to Pricing API
3247 --------------------------------------------------------------------
3248 PROCEDURE Check_Currency_for_Costing
3249 (p_visit_id IN NUMBER,
3250 x_currency_code OUT NOCOPY VARCHAR2
3251 )
3252 IS
3253 -- Define local variables
3254 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Currency_for_Costing';
3255 L_DEBUG_KEY CONSTANT VARCHAR2(100):= 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3256
3257 CURSOR organization_csr(p_visit_id IN NUMBER)IS
3258 SELECT VST.organization_id FROM AHL_VISITS_B VST
3259 WHERE VST.visit_id = p_visit_id;
3260
3261 l_org_id NUMBER;
3262
3263 -- AnRaj:Changes made for fixing bug#4919353, issue# 7
3264 CURSOR currency_code_csr(p_org_id IN NUMBER) IS
3265 SELECT currency_code
3266 FROM cst_acct_info_v COD, GL_SETS_OF_BOOKS GSOB
3267 WHERE COD.Organization_Id = p_org_id
3268 AND LEDGER_ID = GSOB.SET_OF_BOOKS_ID
3269 AND NVL(operating_unit, mo_global.get_current_org_id())= mo_global.get_current_org_id();
3270
3271 /*SELECT currency_code
3272 -- into x_currency_code
3273 FROM CST_ORGANIZATION_DEFINITIONS COD --,AHL_VISITS_B VST
3274 WHERE --VST.visit_id = p_visit_id AND
3275 --COD.Organization_Id = VST.organization_id
3276 COD.Organization_Id = p_org_id
3277 AND NVL(operating_unit, mo_global.get_current_org_id())
3278 = mo_global.get_current_org_id();*/
3279
3280 BEGIN
3281
3282 IF (l_log_procedure >= l_log_current_level) THEN
3283 fnd_log.string(l_log_procedure,
3284 L_DEBUG_KEY ||'.begin',
3285 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
3286 END IF;
3287 -- FETCH organization id
3288 OPEN organization_csr(p_visit_id);
3289 FETCH organization_csr INTO l_org_id;
3290 CLOSE organization_csr;
3291
3292 IF (l_org_id IS NOT NULL)THEN
3293 OPEN currency_code_csr(l_org_id);
3294 FETCH currency_code_csr INTO x_currency_code;
3295 IF (currency_code_csr%NOTFOUND)THEN
3296 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_VWP_CST_NO_CURRENCY');
3297 FND_MSG_PUB.ADD;
3298 IF (fnd_log.level_error >= l_log_current_level)THEN
3299 fnd_log.string(fnd_log.level_error,
3300 L_DEBUG_KEY,
3301 'No curency is defined for the organization of the visit. l_org_id = ' || l_org_id);
3302 END IF;
3303 END IF;
3304 CLOSE currency_code_csr;
3305 END IF;
3306
3307 IF (l_log_procedure >= l_log_current_level) THEN
3308 fnd_log.string(l_log_procedure,
3309 L_DEBUG_KEY ||'.end',
3310 'At the end of PL SQL procedure. Currency Code = ' || x_currency_code);
3311 END IF;
3312 END Check_Currency_for_Costing;
3313 -------------------------------------------------------------------------------
3314 -- PROCEDURE
3315 -- Check_Job_Status
3316 --
3317 -- PURPOSE
3318 -- To find out valid job status on shop floor for a Visit/MR/Task
3319 -------------------------------------------------------------------------------
3320 PROCEDURE Check_Job_Status
3321 (p_id IN NUMBER,
3322 p_is_task_flag IN VARCHAR2,
3323 x_status_code OUT NOCOPY NUMBER,
3324 x_status_meaning OUT NOCOPY VARCHAR2
3325 )
3326 IS
3327 -- Define local variables
3328 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Job_Status';
3329 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3330
3331 -- To find if job exists for the visit at shop floor not in Cancelled-7 Or Deleted-22
3332 CURSOR c_job(x_id IN NUMBER) IS
3333 SELECT AWO.STATUS_CODE, FLV.MEANING
3334 FROM AHL_WORKORDERS AWO, FND_LOOKUP_VALUES_VL FLV
3335 WHERE AWO.VISIT_ID = x_id
3336 AND AWO.STATUS_CODE <> 7 AND AWO.STATUS_CODE <> 22
3337 AND FLV.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
3338 AND AWO.STATUS_CODE = FLV.LOOKUP_CODE(+)
3339 AND AWO.MASTER_WORKORDER_FLAG = 'Y'
3340 AND AWO.VISIT_TASK_ID IS NULL;
3341
3342 -- To find if job exists for the Task/MR at shop floor not in Cancelled-7 Or Deleted-22
3343 CURSOR c_job_tsk(x_id IN NUMBER)IS
3344 SELECT AWO.STATUS_CODE, FLV.MEANING
3345 FROM AHL_WORKORDERS AWO, FND_LOOKUP_VALUES_VL FLV
3346 WHERE AWO.VISIT_TASK_ID = x_id
3347 AND AWO.STATUS_CODE <> 7 AND AWO.STATUS_CODE <> 22
3348 AND FLV.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS'
3349 AND AWO.STATUS_CODE = FLV.LOOKUP_CODE(+);
3350 BEGIN
3351 IF (l_log_procedure >= l_log_current_level) THEN
3352 fnd_log.string(l_log_procedure,
3353 L_DEBUG_KEY ||'.begin',
3354 'At the start of PL SQL procedure. Visit OR Task Id = ' || p_id ||
3355 'p_is_task_flag = '|| p_is_task_flag);
3356 END IF;
3357
3358 IF p_is_task_flag = 'N' THEN -- For Visit
3359 OPEN c_job(p_id);
3360 FETCH c_job INTO x_status_code, x_status_meaning;
3361 CLOSE c_job;
3362 ELSE -- For MR/Task
3363 OPEN c_job_tsk(p_id);
3364 FETCH c_job_tsk INTO x_status_code, x_status_meaning;
3365 CLOSE c_job_tsk;
3366 END IF;
3367
3368 IF (l_log_procedure >= l_log_current_level) THEN
3369 fnd_log.string(l_log_procedure,
3370 L_DEBUG_KEY ||'.end',
3371 'At the end of PL SQL procedure. Status Code = '|| x_status_code ||
3372 'Status Meaning = ' || x_status_meaning);
3373 END IF;
3374
3375 END Check_Job_Status;
3376
3377 -------------------------------------------------------------------------------
3378 -- PROCEDURE
3379 -- Check_Department_Shift
3380 --
3381 -- PURPOSE
3382 -- To find out valid job status on shop floor for a Visit/MR/Task
3383 -------------------------------------------------------------------------------
3384 PROCEDURE Check_Department_Shift(
3385 p_dept_id IN NUMBER,
3386 x_return_status OUT NOCOPY VARCHAR2
3387 )
3388 is
3389 L_DUMMY VARCHAR2(1);
3390 L_API_NAME CONSTANT VARCHAR2(30) := 'Check_Department_Shift';
3391 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3392 --
3393 CURSOR get_dept_csr (p_dept_id IN NUMBER) IS
3394 SELECT 'x'
3395 FROM AHL_DEPARTMENT_SHIFTS
3396 WHERE DEPARTMENT_ID = P_DEPT_ID;
3397 --
3398 BEGIN
3399 IF (l_log_procedure >= l_log_current_level) THEN
3400 fnd_log.string(l_log_procedure,
3401 L_DEBUG_KEY ||'.begin',
3402 'At the start of PL SQL procedure. Department Id = ' || p_dept_id);
3403 END IF;
3404 IF P_DEPT_ID IS NOT NULL AND P_DEPT_ID <> FND_API.G_MISS_NUM
3405 THEN
3406 OPEN get_dept_csr (p_dept_id);
3407 FETCH get_dept_csr INTO l_dummy;
3408 IF (get_dept_csr%NOTFOUND) THEN
3409 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
3410 ELSE
3411 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3412 END IF;
3413 CLOSE get_dept_csr;
3414 ELSE
3415 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3416 END IF;
3417 IF (l_log_procedure >= l_log_current_level) THEN
3418 fnd_log.string(l_log_procedure,
3419 L_DEBUG_KEY ||'.end',
3420 'At the end of PL SQL procedure. Return Status = ' || x_return_status);
3421 END IF;
3422 END CHECK_DEPARTMENT_SHIFT;
3423
3424
3425 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: START
3426 -- Added this new procedure
3427 --------------------------------------------------------------------
3428 -- PROCEDURE : Validate_Past_Task_Dates
3429 -- Type : Private
3430 -- Purpose : To validate the past task start and end dates against cost parent/task stages
3431 -- Parameters :
3432 --
3433 -- Validate_Past_Task_Dates IN Parameters :
3434 -- p_task_rec IN OUT NOCOPY Task_Rec_Type Required
3435
3436 -- Validate_Past_Task_Dates OUT Parameters :
3437 -- x_return_status OUT varchar2 Required
3438 --
3439 -- Version :
3440 -- Initial Version 1.0
3441 --
3442 --------------------------------------------------------------------
3443 PROCEDURE Validate_Past_Task_Dates (
3444 p_task_rec IN OUT NOCOPY Task_Rec_Type,
3445 x_return_status OUT NOCOPY VARCHAR2
3446 )
3447 IS
3448 L_DUMMY VARCHAR2(1);
3449 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Past_Task_Dates';
3450 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3451 l_cost_parent_id NUMBER;
3452 l_stage_planned_start_time DATE;
3453 l_cum_duration NUMBER;
3454 l_visit_start_date DATE;
3455 l_dept_id NUMBER;
3456
3457 --
3458
3459 -- To fetch the cost parent id corresponding to cost parent number
3460 Cursor get_cost_parent_id (c_cost_parent_number NUMBER,
3461 c_visit_id NUMBER)
3462 IS
3463 SELECT visit_task_id
3464 FROM ahl_visit_tasks_b
3465 WHERE visit_task_number = c_cost_parent_number
3466 AND visit_id = c_visit_id;
3467
3468
3469 -- To fetch the start and end of the first non-summary parent (in the cost hierarchy) of the task
3470 Cursor get_cost_parent_dates (c_id NUMBER)
3471 IS
3472 SELECT start_date_time, end_date_time
3473 FROM ahl_visit_tasks_b
3474 WHERE task_type_code<>'SUMMARY'
3475 START WITH visit_task_id = c_id
3476 CONNECT BY PRIOR cost_parent_id = visit_task_id;
3477 cost_parent_dates_rec get_cost_parent_dates%ROWTYPE;
3478
3479 -- To fetch the start and end of the first non-summary child (in the cost hierarchy) of the task
3480 Cursor get_cost_child_dates (c_id NUMBER)
3481 IS
3482 SELECT start_date_time, end_date_time
3483 FROM ahl_visit_tasks_b
3484 WHERE task_type_code<>'SUMMARY'
3485 AND visit_task_id <> c_id
3486 START WITH visit_task_id = c_id
3487 CONNECT BY PRIOR visit_task_id = cost_parent_id;
3488 cost_child_dates_rec get_cost_child_dates%ROWTYPE;
3489
3490 -- To find visit related information
3491 CURSOR c_visit (c_visit_id IN NUMBER)
3492 IS
3493 SELECT START_DATE_TIME , department_id FROM AHL_VISITS_B
3494 WHERE VISIT_ID = c_visit_id;
3495
3496 -- Cursor to find out the cumulative duration of all the stages before the stage of this task
3497 CURSOR c_sum_stage_duration (c_stage_name VARCHAR2,
3498 c_visit_id NUMBER)
3499 IS
3500 SELECT sum(duration)
3501 FROM AHL_VWP_STAGES_VL
3502 WHERE visit_id = c_visit_id
3503 AND stage_num < (select stage_num
3504 from AHL_VWP_STAGES_VL
3505 WHERE stage_name = c_stage_name
3506 AND visit_id = c_visit_id);
3507 --
3508
3509 BEGIN
3510 IF (l_log_procedure >= l_log_current_level) THEN
3511 fnd_log.string(l_log_procedure,
3512 L_DEBUG_KEY ||'.begin',
3513 'At the start of PL SQL procedure. Visit task id = ' || p_task_rec.visit_task_id);
3514 END IF;
3515
3516 -- Initialize API return status to success
3517 x_return_status := FND_API.G_RET_STS_SUCCESS;
3518
3519 -- Get the cost parent id corresponding to the cost parent number
3520 -- Please note that this cost parent is the new updated cost parent
3521 -- and hence it is mandatory to pass this to the cursor get_cost_parent_dates
3522 OPEN get_cost_parent_id (p_task_rec.cost_parent_number, p_task_rec.visit_id);
3523 FETCH get_cost_parent_id INTO l_cost_parent_id;
3524 CLOSE get_cost_parent_id;
3525
3526 -- Get the start and end of this task's first non-summary cost parent in the cost hierarchy
3527 OPEN get_cost_parent_dates (l_cost_parent_id);
3528 FETCH get_cost_parent_dates INTO cost_parent_dates_rec;
3529 CLOSE get_cost_parent_dates;
3530
3531 -- Validate that this past task's dates are within the first cost parent's dates
3532 IF cost_parent_dates_rec.start_date_time IS NOT NULL THEN
3533 IF (cost_parent_dates_rec.start_date_time > p_task_rec.past_task_start_date
3534 OR cost_parent_dates_rec.end_date_time < p_task_rec.past_task_end_date) THEN
3535 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_PAST_DATE_INVLD');
3536 Fnd_Msg_Pub.ADD;
3537 x_return_status := FND_API.G_RET_STS_ERROR;
3538 END IF;
3539 END IF;
3540
3541 -- Get the start and end of this task's first non-summary cost child in the cost hierarchy
3542 OPEN get_cost_child_dates (p_task_rec.visit_task_id);
3543 FETCH get_cost_child_dates INTO cost_child_dates_rec;
3544 CLOSE get_cost_child_dates;
3545
3546 -- Validate that the first cost child's dates are within this task's dates
3547 IF cost_child_dates_rec.start_date_time IS NOT NULL THEN
3548 IF (cost_child_dates_rec.start_date_time < p_task_rec.past_task_start_date
3549 OR cost_child_dates_rec.end_date_time > p_task_rec.past_task_end_date) THEN
3550 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_PAST_DATE_INVLD');
3551 Fnd_Msg_Pub.ADD;
3552 x_return_status := FND_API.G_RET_STS_ERROR;
3553 END IF;
3554 END IF;
3555
3556 -- Cursor to find out the cumulative duration of all the stages before the stage of this task
3557 OPEN c_sum_stage_duration (p_task_rec.stage_name, p_task_rec.visit_id);
3558 FETCH c_sum_stage_duration INTO l_cum_duration;
3559 CLOSE c_sum_stage_duration;
3560
3561 -- Cursor to find visit start time
3562 OPEN c_visit (p_task_rec.visit_id);
3563 FETCH c_visit INTO l_visit_start_date,l_dept_id;
3564 CLOSE c_visit;
3565 -- Find the planned start time of the stage in which this task falls
3566 l_stage_planned_start_time :=
3567 AHL_VWP_TIMES_PVT.compute_date(l_visit_start_date, l_dept_id, l_cum_duration);
3568
3569 -- Validate that the any of the tasks does not start before the stage starts
3570 IF p_task_rec.past_task_start_date < l_stage_planned_start_time THEN
3571 Fnd_Message.SET_NAME('AHL','AHL_VWP_STAGE_TASK_DATE_INVLD');
3572 Fnd_Msg_Pub.ADD;
3573 x_return_status := FND_API.G_RET_STS_ERROR;
3574 END IF;
3575
3576 IF (l_log_procedure >= l_log_current_level)THEN
3577 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure, x_return_status=' || x_return_status);
3578 END IF;
3579
3580 END Validate_Past_Task_Dates;
3581 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: END
3582
3583 -- AVIKUKUM :: FP:PIE :: 13-OCT-2010
3584 -------------------------------------------------------------------------------------------------
3585 -- PROCEDURE
3586 -- Validate_Service_Type
3587 --
3588 -- PURPOSE
3589 -- To validate the passed Service Type attributes. For public API call for creation:
3590 -- - if passed Service Type attributes are G_MISS_CHAR, NULLify them
3591 -- - else if NULL, default to the appropriate profile
3592 -- - else validate in the usual manner
3593 -------------------------------------------------------------------------------------------------
3594 PROCEDURE Validate_Service_Type(
3595 p_service_type IN VARCHAR2,
3596 p_module_type IN VARCHAR2 := 'JSP',
3597 p_unit_effectivity_id IN NUMBER,
3598 p_x_service_type_code IN OUT NOCOPY VARCHAR2
3599 ) IS
3600 --
3601 CURSOR get_service_type_code (c_service_type VARCHAR2) IS
3602 SELECT lookup_code
3603 FROM pa_lookups
3604 WHERE lookup_type = 'SERVICE TYPE'
3605 AND enabled_flag = 'Y'
3606 AND meaning = c_service_type
3607 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE-1))
3608 AND TRUNC(NVL(end_date_active,SYSDATE));
3609
3610 CURSOR check_service_type_code (c_service_type_code VARCHAR2) IS
3611 SELECT 'X'
3612 FROM pa_lookups
3613 WHERE lookup_type = 'SERVICE TYPE'
3614 AND enabled_flag = 'Y'
3615 AND lookup_code = c_service_type_code
3616 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE-1))
3617 AND TRUNC(NVL(end_date_active,SYSDATE));
3618
3619 CURSOR get_object_type (c_unit_effectivity_id NUMBER) IS
3620 SELECT object_type
3621 FROM ahl_unit_effectivities_b
3622 WHERE unit_effectivity_id = c_unit_effectivity_id;
3623
3624 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Service_Type';
3625 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
3626
3627 l_profile_value VARCHAR2(30);
3628 l_object_type VARCHAR2(3);
3629 l_dummy VARCHAR2(1) := NULL;
3630 --
3631 BEGIN
3632 IF (l_log_procedure >= l_log_current_level) THEN
3633 FND_LOG.string(l_log_procedure, l_debug_key||'.begin',
3634 'At the start of the API.'||
3635 ' p_service_type => '||p_service_type||
3636 ', p_module_type => '||p_module_type||
3637 ', p_unit_effectivity_id => '||p_unit_effectivity_id||
3638 ', p_x_service_type_code => '||p_x_service_type_code);
3639 END IF;
3640
3641 -- if p_module_type is JSP, ignore p_x_service_type_code and fetch it from p_service_type, after validation
3642 IF (p_module_type = 'JSP') THEN
3643 -- if p_service_type is NULL, return p_x_service_type_code as NULL too
3644 IF (p_service_type IS NULL) THEN
3645 p_x_service_type_code := NULL;
3646 ELSE
3647 -- validate p_service_type and fetch p_x_service_type_code
3648 OPEN get_service_type_code (p_service_type);
3649 FETCH get_service_type_code INTO p_x_service_type_code;
3650 IF (get_service_type_code%NOTFOUND) THEN
3651 -- raise exception - "The Service Type (VALUE) is invalid."
3652 IF (l_log_statement >= l_log_current_level) THEN
3653 FND_LOG.string(l_log_statement, l_debug_key,
3654 'exception raised - The Service Type ('||p_service_type||') is invalid.');
3655 END IF;
3656
3657 CLOSE get_service_type_code;
3658
3659 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_ST_VAL_INVLD');
3660 FND_MESSAGE.Set_Token('VALUE', p_service_type);
3661 FND_MSG_PUB.ADD;
3662 RAISE FND_API.G_EXC_ERROR;
3663 END IF;
3664 CLOSE get_service_type_code;
3665 END IF;
3666 ELSIF (p_module_type = 'SR') THEN
3667 -- if p_module_type is SR, i.e. call from Execution, then give p_x_service_type_code precedence over p_service_type
3668 IF (p_x_service_type_code IS NULL) THEN
3669 -- check for p_service_type
3670 IF (p_service_type IS NULL) THEN
3671 NULL; -- do nothing
3672 ELSE
3673 -- validate p_service_type and fetch p_x_service_type_code
3674 OPEN get_service_type_code (p_service_type);
3675 FETCH get_service_type_code INTO p_x_service_type_code;
3676 IF (get_service_type_code%NOTFOUND) THEN
3677 -- raise exception - "The Service Type (VALUE) is invalid."
3678 IF (l_log_statement >= l_log_current_level) THEN
3679 FND_LOG.string(l_log_statement, l_debug_key,
3680 'exception raised - The Service Type ('||p_service_type||') is invalid.');
3681 END IF;
3682
3683 CLOSE get_service_type_code;
3684
3685 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_ST_VAL_INVLD');
3686 FND_MESSAGE.Set_Token('VALUE', p_service_type);
3687 FND_MSG_PUB.ADD;
3688 RAISE FND_API.G_EXC_ERROR;
3689 END IF;
3690 CLOSE get_service_type_code;
3691 END IF;
3692 ELSE
3693 -- validate p_x_service_type_code
3694 OPEN check_service_type_code (p_x_service_type_code);
3695 FETCH check_service_type_code INTO l_dummy;
3696 IF (check_service_type_code%NOTFOUND) THEN
3697 -- raise exception - "The Service Type code (CODE) is invalid."
3698 IF (l_log_statement >= l_log_current_level) THEN
3699 FND_LOG.string(l_log_statement, l_debug_key,
3700 'exception raised - The Service Type code ('||p_x_service_type_code||') is invalid.');
3701 END IF;
3702
3703 CLOSE check_service_type_code;
3704
3705 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_ST_CD_INVLD');
3706 FND_MESSAGE.Set_Token('CODE', p_x_service_type_code);
3707 FND_MSG_PUB.ADD;
3708 RAISE FND_API.G_EXC_ERROR;
3709 END IF;
3710 CLOSE check_service_type_code;
3711 END IF; -- (p_x_service_type_code IS NULL) check, for SR module type call
3712 ELSE
3713 -- if p_module_type is not JSP and SR, i.e. public API call for creation:
3714 -- - if passed Service Type attributes are G_MISS_CHAR, NULLify them
3715 -- - else if NULL, default to the appropriate profile
3716 -- - else validate in the usual manner
3717 IF (p_x_service_type_code = FND_API.G_MISS_CHAR OR
3718 p_service_type = FND_API.G_MISS_CHAR) THEN
3719 -- NULLify the Service Type attributes
3720 p_x_service_type_code := NULL;
3721 ELSIF (p_x_service_type_code IS NULL) THEN
3722 -- check for p_service_type
3723 IF (p_service_type IS NULL) THEN
3724 -- default to the appropriate profile value
3725 IF (p_unit_effectivity_id IS NULL) THEN
3726 -- use 'AHL: Default Service Type for Routine tasks' profile value
3727 l_profile_value := FND_PROFILE.VALUE('AHL_DEFAULT_ROUTINE_TASK_TYPE');
3728 ELSE
3729 -- fetch from the cursor get_object_type
3730 OPEN get_object_type (p_unit_effectivity_id);
3731 FETCH get_object_type INTO l_object_type;
3732 CLOSE get_object_type;
3733
3734 IF (l_object_type IS NOT NULL AND l_object_type = 'SR') THEN
3735 -- use 'AHL: Default Service Type for Non-Routine tasks' profile value
3736 l_profile_value := FND_PROFILE.VALUE('AHL_DEFAULT_NON_ROUTINE_TASK_TYPE');
3737 ELSE
3738 -- use 'AHL: Default Service Type for Routine tasks' profile value
3739 l_profile_value := FND_PROFILE.VALUE('AHL_DEFAULT_ROUTINE_TASK_TYPE');
3740 END IF;
3741 END IF; -- p_unit_effectivity_id NULL check
3742
3743 p_x_service_type_code := l_profile_value;
3744 ELSE
3745 -- validate p_service_type and fetch p_x_service_type_code
3746 OPEN get_service_type_code (p_service_type);
3747 FETCH get_service_type_code INTO p_x_service_type_code;
3748 IF (get_service_type_code%NOTFOUND) THEN
3749 -- raise exception - "The Service Type (VALUE) is invalid."
3750 IF (l_log_statement >= l_log_current_level) THEN
3751 FND_LOG.string(l_log_statement, l_debug_key,
3752 'exception raised - The Service Type ('||p_service_type||') is invalid.');
3753 END IF;
3754
3755 CLOSE get_service_type_code;
3756
3757 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_ST_VAL_INVLD');
3758 FND_MESSAGE.Set_Token('VALUE', p_service_type);
3759 FND_MSG_PUB.ADD;
3760 RAISE FND_API.G_EXC_ERROR;
3761 END IF;
3762 CLOSE get_service_type_code;
3763 END IF;
3764 ELSE
3765 -- validate p_x_service_type_code
3766 OPEN check_service_type_code (p_x_service_type_code);
3767 FETCH check_service_type_code INTO l_dummy;
3768 IF (check_service_type_code%NOTFOUND) THEN
3769 -- raise exception - "The Service Type code (CODE) is invalid."
3770 IF (l_log_statement >= l_log_current_level) THEN
3771 FND_LOG.string(l_log_statement, l_debug_key,
3772 'exception raised - The Service Type code ('||p_x_service_type_code||') is invalid.');
3773 END IF;
3774
3775 CLOSE check_service_type_code;
3776
3777 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_ST_CD_INVLD');
3778 FND_MESSAGE.Set_Token('CODE', p_x_service_type_code);
3779 FND_MSG_PUB.ADD;
3780 RAISE FND_API.G_EXC_ERROR;
3781 END IF;
3782 CLOSE check_service_type_code;
3783 END IF; -- FND_API.G_MISS_CHAR check, for non-JSP and non-SR module type call
3784 END IF; -- (p_module_type = 'JSP') check
3785 IF (l_log_procedure >= l_log_current_level) THEN
3786 FND_LOG.string(l_log_procedure, l_debug_key||'.end',
3787 'At the end of the API.'||
3788 ' p_service_type => '||p_service_type||
3789 ', p_x_service_type_code => '||p_x_service_type_code);
3790 END IF;
3791 END Validate_Service_Type;
3792
3793 --salogan added the following for supplier warranty starts
3794 ------------------------------------------------------------------------------------
3795 -- Start of Comments
3796 -- Procedure name : Get_Warranty_Rec_for_Create
3797 -- Type : Public
3798 -- Function : To get the warranty entitlement record for task creation
3799 -- Pre-reqs :
3800 -- Parameters : p_task_id IN NUMBER Required : Visit Task id
3801 -- : p_instance_id IN NUMBER Required : Instance id
3802 -- : p_service_req_id IN NUMBER
3803 -- : x_warranty_entl_tbl OUT AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
3804 --
3805 -- End of Comments
3806
3807 PROCEDURE Get_Warranty_Rec_for_Create(
3808 p_task_id IN NUMBER,
3809 p_instance_id IN NUMBER,
3810 p_service_req_id IN NUMBER := NULL,
3811 x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
3812 ) IS
3813 -- Standard variables
3814 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Warranty_Rec_for_Create';
3815 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3816 -- Local variables defined for the procedure
3817 l_mr_template NUMBER;
3818 l_route_template NUMBER;
3819 l_contract_id NUMBER := FND_API.G_MISS_NUM;
3820 l_warranty_entl_id NUMBER := null;
3821 l_task_type VARCHAR2(20):= null;
3822 l_ovn NUMBER;
3823 l_dummy VARCHAR2(1);
3824 l_entitlement_rec AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Rec_Type;
3825
3826 -- Cursors defined for the procedure
3827 --cursor to fetch the visit task type code
3828 CURSOR c_task_type (c_task_id IN NUMBER) IS
3829 SELECT TASK_TYPE_CODE
3830 FROM ahl_visit_tasks_b
3831 WHERE VISIT_TASK_ID = c_task_id;
3832
3833 --cursor to fetch warranty template associated at MR
3834 CURSOR c_mr_template (c_task_id IN NUMBER) IS
3835 SELECT AMRH.WARRANTY_TEMPLATE_ID
3836 FROM AHL_VISIT_TASKS_VL ATSK, AHL_MR_HEADERS_B AMRH
3837 WHERE ATSK.MR_ID = AMRH.MR_HEADER_ID(+)
3838 AND ATSK.VISIT_TASK_ID = c_task_id;
3839
3840 --cursor to fetch warranty template associated at route
3841 CURSOR c_route_template (c_task_id IN NUMBER) IS
3842 SELECT ARV.WARRANTY_TEMPLATE_ID
3843 FROM AHL_VISIT_TASKS_VL ATSK,
3844 AHL_MR_ROUTES_V AMRR,
3845 AHL_ROUTES_B ARV
3846 WHERE ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID(+)
3847 AND AMRR.ROUTE_ID = ARV.ROUTE_ID (+)
3848 AND ATSK.VISIT_TASK_ID = c_task_id;
3849
3850 --cursor to fetch active warranty contract of an instance for a given template
3851 CURSOR c_match_contract (c_instance_id IN NUMBER, c_warranty_templ_id IN NUMBER) IS
3852 SELECT AWCV.WARRANTY_CONTRACT_ID
3853 FROM AHL_WARRANTY_CONTRACTS_VL AWCV
3854 WHERE AWCV.CONTRACT_STATUS_CODE = 'ACTIVE'
3855 AND AWCV.ITEM_INSTANCE_ID = c_instance_id
3856 AND AWCV.WARRANTY_TEMPLATE_ID = c_warranty_templ_id;
3857
3858 --cursor to fetch warranty entitlement id for a given SR Id
3859 CURSOR c_get_entl_for_NR_task (c_sr_incident_id IN NUMBER) IS
3860 SELECT AWE.WARRANTY_ENTITLEMENT_ID,AWE.OBJECT_VERSION_NUMBER
3861 FROM AHL_WARRANTY_ENTITLEMENTS AWE ,AHL_VISIT_TASKS_B ATSK
3862 WHERE ATSK.SERVICE_REQUEST_ID = AWE.SR_INCIDENT_ID
3863 AND ATSK.TASK_TYPE_CODE = 'PLANNED'
3864 AND ATSK.MR_ROUTE_ID IS NULL
3865 AND AWE.VISIT_TASK_ID IS NULL
3866 AND AWE.SR_INCIDENT_ID = c_sr_incident_id;
3867
3868 --cursor to check whether the task is a planned SR based task(SR must not have any NR)
3869 CURSOR c_is_planned_SR_task (c_task_id IN NUMBER) IS
3870 SELECT 'X'
3871 FROM AHL_VISIT_TASKS_B
3872 WHERE TASK_TYPE_CODE = 'PLANNED'
3873 AND MR_ROUTE_ID IS NULL
3874 AND VISIT_TASK_ID = c_task_id;
3875
3876 BEGIN
3877 --------------------Start of API Body-----------------------------------
3878 IF (l_log_procedure >= l_log_current_level) THEN
3879 fnd_log.string(l_log_procedure,
3880 L_DEBUG_KEY ||'.begin',
3881 'At the start of PL SQL procedure. '||
3882 'Passed Task_id : '|| p_task_id ||' - Instance id : '|| p_instance_id ||' - SR Id : '||p_service_req_id);
3883 END IF;
3884
3885 --------------------Get Entitlements Record For Create-------------------
3886 IF(p_task_id = null OR p_instance_id = null) THEN
3887 IF (l_log_statement >= l_log_current_level) THEN
3888 fnd_log.string(l_log_statement,
3889 L_DEBUG_KEY,
3890 'Passed Visit_task_id : '|| p_task_id ||' or Instance id : '|| p_instance_id ||' is null');
3891 END IF;
3892 FND_MSG_PUB.add;
3893 END IF;
3894
3895 -- Instance warranty enabled
3896 IF(ahl_warranty_contracts_pvt.is_instance_warranty_available(p_instance_id) = 'Y') THEN
3897 OPEN c_get_entl_for_NR_task(p_service_req_id);
3898 FETCH c_get_entl_for_NR_task into l_warranty_entl_id,l_ovn;
3899 IF(c_get_entl_for_NR_task%FOUND) THEN
3900 l_entitlement_rec.warranty_entitlement_id := l_warranty_entl_id;
3901 l_entitlement_rec.visit_task_id := p_task_id;
3902 l_entitlement_rec.object_version_number := l_ovn;
3903 l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
3904 ELSE
3905 --Store the attributes in Entitlements Record
3906 OPEN c_is_planned_SR_task(p_task_id);
3907 FETCH c_is_planned_SR_task into l_dummy;
3908 IF(c_is_planned_SR_task%FOUND) THEN
3909 l_entitlement_rec.sr_incident_id := p_service_req_id;
3910 END IF;
3911 CLOSE c_is_planned_SR_task;
3912 l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_CREATE;
3913
3914 --Donot Store the task id in Entitlements Record if service req id is not null
3915 --and task type is summary as these are Non Routine MR's (Summary tasks) and
3916 --these entitlemet records will be deleted on push to production
3917 OPEN c_task_type(p_task_id);
3918 FETCH c_task_type into l_task_type;
3919 CLOSE c_task_type;
3920 IF(p_service_req_id IS NOT NULL AND l_task_type = 'SUMMARY') THEN
3921 l_entitlement_rec.visit_task_id := FND_API.G_MISS_NUM;
3922 --do nothing, in this case we donot have to create or update the existing Entl record
3923 --so passing the create flag as null
3924 l_entitlement_rec.operation_flag := null;
3925 ELSE
3926 l_entitlement_rec.visit_task_id := p_task_id;
3927 END IF;
3928
3929 --Auto match contract
3930 IF(AHL_WARRANTY_ENTL_PVT.is_task_warranty_available(p_task_id) = 'Y') THEN -- Task warranty Enabled
3931 OPEN c_mr_template(p_task_id);
3932 FETCH c_mr_template into l_mr_template; -- Fetch the warranty template id at the MR level
3933 IF(c_mr_template%FOUND) THEN -- If the warranty template id at the MR level is found
3934 OPEN c_match_contract(p_instance_id, l_mr_template);
3935 -- Loop to find if there more than one active contract is found for a template-instance pair
3936 LOOP
3937 FETCH c_match_contract into l_contract_id; -- Store the warranty contract id for the template/instance match
3938 EXIT WHEN c_match_contract%NOTFOUND OR c_match_contract%ROWCOUNT > 1;
3939 END LOOP;
3940 IF(c_match_contract%ROWCOUNT <> 1)THEN
3941 l_contract_id := FND_API.G_MISS_NUM;
3942 END IF;
3943 CLOSE c_match_contract;
3944 END IF;
3945 CLOSE c_mr_template;
3946 IF(l_contract_id = FND_API.G_MISS_NUM) THEN -- If the warranty template id at the MR level is not found and/or No warranty contract match
3947 OPEN c_route_template(p_task_id); -- Fetch the warranty template id at the Route level
3948 FETCH c_route_template into l_mr_template;
3949 IF(c_route_template%FOUND) THEN -- If the warranty template id at the Route level is found
3950 OPEN c_match_contract(p_instance_id, l_mr_template);
3951 -- Loop to find if there more than one active contract is found for a template-instance pair
3952 LOOP
3953 FETCH c_match_contract into l_contract_id; -- Store the warranty contract id for the template/instance match
3954 EXIT WHEN c_match_contract%NOTFOUND OR c_match_contract%ROWCOUNT > 1;
3955 END LOOP;
3956 IF(c_match_contract%ROWCOUNT <> 1)THEN
3957 l_contract_id := FND_API.G_MISS_NUM;
3958 END IF;
3959 CLOSE c_match_contract;
3960 END IF;
3961 END IF;
3962 --Store the Warranty contract id and entitlement status code in Entitlements Record
3963 IF(l_contract_id = FND_API.G_MISS_NUM) THEN
3964 l_entitlement_rec.warranty_contract_id := null;
3965 l_entitlement_rec.entitlement_status_code := 'REVIEW';
3966 ELSE
3967 l_entitlement_rec.warranty_contract_id := l_contract_id;
3968 l_entitlement_rec.entitlement_status_code := 'APPROVED';
3969 END IF;
3970 ELSE -- Task warranty not Enabled
3971 l_entitlement_rec.warranty_contract_id := null;
3972 l_entitlement_rec.entitlement_status_code := 'REVIEW';
3973 END IF;
3974 END IF;
3975 CLOSE c_get_entl_for_NR_task;
3976 -- Set OUT parameter with created entitlement record
3977 x_warranty_entl_tbl(0) := l_entitlement_rec;
3978 END IF;
3979
3980 --------------------End of API Body-----------------------------------
3981 IF (l_log_statement >= l_log_current_level) THEN
3982 fnd_log.string(l_log_statement,
3983 L_DEBUG_KEY||'.end',
3984 'At the end of PL SQL procedure.' ||
3985 ' Task Id : '|| l_entitlement_rec.visit_task_id ||
3986 ' Entitlement Id : '|| l_entitlement_rec.warranty_entitlement_id ||
3987 ' Warranty Contract Id : '|| l_entitlement_rec.warranty_contract_id ||
3988 ' Entitlement Status Code : '|| l_entitlement_rec.entitlement_status_code ||
3989 ' SR Incident Id : '|| l_entitlement_rec.sr_incident_id ||
3990 ' Operation Flag : '|| l_entitlement_rec.operation_flag);
3991 END IF;
3992
3993 END Get_Warranty_Rec_for_Create;
3994
3995
3996
3997 ------------------------------------------------------------------------------------
3998 -- Start of Comments
3999 -- Procedure name : Get_Warranty_Rec_for_Update
4000 -- Type : Public
4001 -- Function : To get the set warranty entitlement record (table type) for task updation
4002 -- Pre-reqs :
4003 -- Parameters : p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type Required : Visit Task id
4004 -- : x_warranty_entl_tbl OUT AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
4005 --
4006 -- End of Comments
4007
4008 PROCEDURE Get_Warranty_Rec_for_Update(
4009 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
4010 x_warranty_entl_tbl OUT NOCOPY AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type
4011 ) IS
4012 -- Standard variables
4013 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Warranty_Rec_for_Update';
4014 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
4015 -- Local variables defined for the procedure
4016 l_contract_id NUMBER := null;
4017 l_entl_status VARCHAR2(20) := null;
4018 l_task_type VARCHAR2(20) := null;
4019 l_child_task_id NUMBER := null;
4020 l_warranty_entl_id NUMBER := null;
4021 l_old_contract_id NUMBER := null;
4022 l_old_contract_num NUMBER := null;
4023 l_old_entl_status VARCHAR2(20) := 'NOT_APPLICABLE';
4024 l_ovn NUMBER;
4025 l_dummy VARCHAR2(1);
4026 l_entitlement_rec AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Rec_Type;
4027 i NUMBER := 1;
4028
4029 -- Cursors defined for the procedure
4030 --cursor to fetch the visit task type code
4031 CURSOR c_task_type (c_task_id IN NUMBER) IS
4032 SELECT TASK_TYPE_CODE
4033 FROM ahl_visit_tasks_b
4034 WHERE VISIT_TASK_ID = c_task_id;
4035
4036 --cursor to fetch the old entitlemet record from DB for the task
4037 CURSOR c_old_entitlement_rec (c_task_id IN NUMBER) IS
4038 SELECT AWCB.WARRANTY_CONTRACT_ID, AWCB.CONTRACT_NUMBER
4039 , AWSE.ENTITLEMENT_STATUS_CODE, AWSE.OBJECT_VERSION_NUMBER
4040 FROM AHL_WARRANTY_ENTITLEMENTS AWSE, AHL_WARRANTY_CONTRACTS_B AWCB
4041 WHERE AWSE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID(+)
4042 AND AWSE.VISIT_TASK_ID = c_task_id;
4043
4044 --cursor to fetch active contract id using contract number
4045 CURSOR c_get_contract_id (c_contract_num IN NUMBER) IS
4046 SELECT WARRANTY_CONTRACT_ID
4047 FROM AHL_WARRANTY_CONTRACTS_B
4048 WHERE CONTRACT_STATUS_CODE = 'ACTIVE'
4049 AND CONTRACT_NUMBER = c_contract_num;
4050
4051 --cursor to check if a record exists in the entitlements for the given work order
4052 CURSOR c_entitlements_exists(c_task_id NUMBER) IS
4053 SELECT WARRANTY_ENTITLEMENT_ID, OBJECT_VERSION_NUMBER
4054 FROM AHL_WARRANTY_ENTITLEMENTS
4055 WHERE VISIT_TASK_ID = c_task_id;
4056
4057 --cursor to fetch non summary child tasks
4058 CURSOR c_child_tasks(c_task_id NUMBER) IS
4059 SELECT VISIT_TASK_ID
4060 FROM AHL_VISIT_TASKS_B
4061 WHERE TASK_TYPE_CODE <> 'SUMMARY'
4062 AND ORIGINATING_TASK_ID = c_task_id;
4063
4064 --cursor to check whether the task is a planned SR based task(SR must not have any NR)
4065 CURSOR c_is_planned_SR_task (c_task_id IN NUMBER) IS
4066 SELECT 'X'
4067 FROM AHL_VISIT_TASKS_B
4068 WHERE TASK_TYPE_CODE = 'PLANNED'
4069 AND MR_ROUTE_ID IS NULL
4070 AND VISIT_TASK_ID = c_task_id;
4071
4072 BEGIN
4073 --------------------Start of API Body-----------------------------------
4074 IF (l_log_procedure >= l_log_current_level) THEN
4075 fnd_log.string(l_log_procedure,
4076 L_DEBUG_KEY ||'.begin',
4077 'At the start of PL SQL procedure.');
4078 END IF;
4079
4080 --------------------Get Entitlements Record For Update-------------------
4081 IF(p_task_rec.visit_task_id is null ) THEN
4082 IF (l_log_statement >= l_log_current_level) THEN
4083 fnd_log.string(l_log_statement,
4084 L_DEBUG_KEY,
4085 'Passed Visit task id is null');
4086 END IF;
4087 FND_MSG_PUB.add;
4088 END IF;
4089
4090 IF (l_log_statement >= l_log_current_level) THEN
4091 fnd_log.string(l_log_statement,
4092 L_DEBUG_KEY,
4093 ' Visit task id : '|| p_task_rec.visit_task_id ||
4094 ' Instance is : '|| p_task_rec.instance_id||
4095 ' Entitlement Id is : '|| p_task_rec.warranty_entitlement_id||
4096 ' Entitlement Status is : '|| p_task_rec.entitlement_status||
4097 ' Entitlement Status Code is : '|| p_task_rec.entitlement_status_code||
4098 ' Warranty Contract Num is : '|| p_task_rec.contract_number||
4099 ' Claim Name is : '|| p_task_rec.claim_name||
4100 ' Claim Status is : '|| p_task_rec.claim_status||
4101 ' Claim Status code is : '|| p_task_rec.claim_status_code||
4102 ' Service Request Id is : '|| p_task_rec.service_request_id||
4103 ' Warranty Not Applicable Flag is : '|| p_task_rec.warranty_not_appl_flag);
4104 END IF;
4105
4106 /* MANESING::Component Maintenance Planning Project, 07-Aug-2011
4107 * Call APIs to fetch value of Task and Instance Warranty available flags which would be useful
4108 * when they are passed as null in the record (API Get_Warranty_Rec_for_Create does the same).
4109 */
4110 --IF(p_task_rec.instance_warranty = 'Y') THEN -- If Instance warranty enabled
4111 IF (AHL_WARRANTY_CONTRACTS_PVT.Is_Instance_Warranty_Available (p_task_rec.instance_id) = 'Y') THEN
4112 OPEN c_old_entitlement_rec(p_task_rec.visit_task_id);
4113 FETCH c_old_entitlement_rec into l_old_contract_id, l_old_contract_num, l_old_entl_status, l_ovn;
4114 CLOSE c_old_entitlement_rec;
4115
4116 --Defaulting the entitlement record with old values from DB
4117 l_contract_id := l_old_contract_id;
4118 l_entl_status := l_old_entl_status;
4119
4120 IF(p_task_rec.warranty_not_appl_flag = 'Y') THEN -- If Warranty Not Applicable check box is checked
4121 l_contract_id := FND_API.G_MISS_NUM;
4122 l_entl_status := 'NOT_APPLICABLE';
4123 ELSE -- If Warranty Not Applicable check box is unchecked
4124 IF(p_task_rec.contract_number is not null) THEN -- If contact number is associated
4125 OPEN c_get_contract_id(p_task_rec.contract_number);
4126 FETCH c_get_contract_id into l_contract_id;
4127 CLOSE c_get_contract_id;
4128 IF(l_contract_id is null) THEN
4129 FND_MESSAGE.set_name('AHL','AHL_COM_INVALID_WARRANTY_CONTRACT');
4130 FND_MESSAGE.set_token('CONT_NUM', p_task_rec.contract_number);
4131 FND_MSG_PUB.add;
4132 END IF;
4133 l_entl_status := 'APPROVED';
4134 ELSE -- If contact number is not associated
4135 l_contract_id := FND_API.G_MISS_NUM;
4136 IF(l_old_entl_status <> 'APPROVED') THEN
4137 l_entl_status := l_old_entl_status;
4138 ELSE
4139 l_entl_status := 'NOT_APPLICABLE';
4140 END IF;
4141 END IF;
4142 --END IF;
4143 END IF;
4144
4145 -- Store the local variables in the Entitlements Record
4146 l_entitlement_rec.visit_task_id := p_task_rec.visit_task_id;
4147 l_entitlement_rec.warranty_contract_id := l_contract_id;
4148 l_entitlement_rec.entitlement_status_code := l_entl_status;
4149
4150 --Adding the SR id to entitlement only when the task is planned
4151 --and the sr is not based on a MR
4152 OPEN c_is_planned_SR_task(p_task_rec.visit_task_id);
4153 FETCH c_is_planned_SR_task into l_dummy;
4154 IF(c_is_planned_SR_task%FOUND) THEN
4155 l_entitlement_rec.sr_incident_id := p_task_rec.service_request_id;
4156 END IF;
4157 CLOSE c_is_planned_SR_task;
4158
4159 IF(p_task_rec.warranty_entitlement_id is not null) THEN -- Entitlements Record Already Exists UPDATE
4160 l_entitlement_rec.warranty_entitlement_id := p_task_rec.warranty_entitlement_id;
4161 l_entitlement_rec.object_version_number := l_ovn;
4162 l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
4163 ELSE -- CREATE Entitlement record
4164 l_entitlement_rec.operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_CREATE;
4165 END IF;
4166 --Adding the first record to the OUT param table type
4167 x_warranty_entl_tbl(0) := l_entitlement_rec;
4168
4169 IF (l_log_statement >= l_log_current_level) THEN
4170 fnd_log.string(l_log_statement,
4171 L_DEBUG_KEY,
4172 ' After updating the Entitlements record.' ||
4173 ' Task Id : '|| l_entitlement_rec.visit_task_id ||
4174 ' Warranty Contract Id : '|| l_entitlement_rec.warranty_contract_id ||
4175 ' Entitlement Status Code : '|| l_entitlement_rec.entitlement_status_code ||
4176 ' SR Incident Id '|| l_entitlement_rec.sr_incident_id ||
4177 ' Operation Flag : '|| l_entitlement_rec.operation_flag);
4178 END IF;
4179
4180 -- Handling the child task cascading for summary task
4181 -- MANESING::Component Maintenance Planning Project, 07-Aug-2011, call API instead of using flag
4182 --IF(p_task_rec.task_warranty = 'Y') THEN -- If Task warranty enabled
4183 IF (AHL_WARRANTY_ENTL_PVT.Is_Task_Warranty_Available (p_task_rec.visit_task_id) = 'Y') THEN
4184 OPEN c_task_type(p_task_rec.visit_task_id);
4185 FETCH c_task_type into l_task_type;
4186 CLOSE c_task_type;
4187 IF(l_task_type = 'SUMMARY') THEN -- If the Task type is Summary
4188 OPEN c_child_tasks(p_task_rec.visit_task_id); -- Loop all the child route tasks of the summary task
4189 LOOP
4190 FETCH c_child_tasks into l_child_task_id;
4191 EXIT WHEN c_child_tasks%NOTFOUND;
4192 -- Store the contract and entitlement status values calculated earlier for the child tasks in OUT param
4193 x_warranty_entl_tbl(i).visit_task_id := l_child_task_id;
4194 x_warranty_entl_tbl(i).warranty_contract_id := l_contract_id;
4195 x_warranty_entl_tbl(i).entitlement_status_code := l_entl_status;
4196
4197 OPEN c_entitlements_exists(l_child_task_id); -- Check whether the warranty entitlement exists for the child task
4198 FETCH c_entitlements_exists into l_warranty_entl_id, l_ovn;
4199 IF(c_entitlements_exists%FOUND) THEN -- if exists then set update flag else create flag / store it in OUT param
4200 x_warranty_entl_tbl(i).warranty_entitlement_id := l_warranty_entl_id;
4201 x_warranty_entl_tbl(i).object_version_number := l_ovn;
4202 x_warranty_entl_tbl(i).operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_UPDATE;
4203 ELSE
4204 x_warranty_entl_tbl(i).operation_flag := AHL_WARRANTY_ENTL_PVT.G_OP_CREATE;
4205 END IF;
4206 CLOSE c_entitlements_exists;
4207 i := i+1;
4208 END LOOP;
4209 CLOSE c_child_tasks;
4210 END IF;
4211 END IF;
4212 END IF; -- end of instance warranty check
4213
4214 ---------------------------End of API Body---------------------------------------
4215 IF (l_log_procedure >= l_log_current_level) THEN
4216 fnd_log.string(l_log_procedure,
4217 L_DEBUG_KEY ||'.end',
4218 'At the end of PL SQL procedure. ');
4219 END IF;
4220 END Get_Warranty_Rec_for_Update;
4221 --salogan added the following for supplier warranty ends
4222
4223 --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: start
4224
4225 --------------------------------------------------------------------------------------
4226 -- PROCEDURE
4227 -- Handle_Task_Dependency
4228 --
4229 -- PURPOSE
4230 -- To handle all the dependencies - task dependency,stage dependency and WBS rules.
4231 -- And to convert WBS rule into task rule, route dependencies to task rules
4232 --------------------------------------------------------------------------------------
4233 PROCEDURE Handle_Task_Dependency (
4234 p_api_version IN NUMBER,
4235 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4236 p_commit IN VARCHAR2 := Fnd_Api.g_false,
4237 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4238
4239 p_visit_id IN NUMBER,
4240 p_task_tbl IN AHL_VWP_RULES_PVT.Task_Tbl_Type,
4241
4242 x_return_status OUT NOCOPY VARCHAR2,
4243 x_msg_count OUT NOCOPY NUMBER,
4244 x_msg_data OUT NOCOPY VARCHAR2
4245 )
4246 is
4247
4248 -- Define local Variables
4249 L_API_VERSION CONSTANT NUMBER := 1.0;
4250 L_API_NAME CONSTANT VARCHAR2(30) := 'Handle_Task_Dependency';
4251 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
4252 l_msg_count NUMBER;
4253 l_task_tbl AHL_VWP_RULES_PVT.Task_Tbl_Type := p_task_tbl;
4254 l_post_rout_no VARCHAR2(30);
4255 l_post_route_type VARCHAR2(30);
4256 l_post_system_code VARCHAR2(100);
4257 l_post_process_code VARCHAR2(30);
4258 l_task_link_rec AHL_VWP_TASKS_LINKS_PVT.TASK_LINK_REC;
4259 l_return_status VARCHAR2(1);
4260 l_msg_data VARCHAR2(2000);
4261
4262 --Cursor to get the stage details for a given task
4263 CURSOR c_stage_details(p_task_id IN NUMBER)
4264 IS
4265 SELECT task1.visit_task_id, stage.STAGE_ID, stage.STAGE_NAME
4266 FROM AHL_VWP_STAGES_VL stage, AHL_VISIT_TASKS_B task1
4267 WHERE task1.stage_id = stage.stage_id
4268 AND task1.visit_task_id IN ( SELECT task.visit_task_id
4269 FROM AHL_VISIT_TASKS_B task
4270 WHERE task.task_type_code IN ('PLANNED','UNPLANNED')
4271 AND task.status_code = 'PLANNING'
4272 START WITH task.visit_task_id = p_task_id
4273 CONNECT BY PRIOR task.visit_task_id = task.originating_task_id)
4274 ORDER BY visit_task_id;
4275
4276 --Cusor to fetch all the stages in a visit
4277 Cursor get_visit_stages (p_visit_id IN NUMBER) IS
4278 SELECT stage_id
4279 FROM ahl_vwp_stages_b
4280 WHERE visit_id = p_visit_id
4281 AND stage_status_code <> 'RELEASED';
4282
4283 --To fetch all the route tasks of a stage in a visit.
4284 Cursor get_stage_tasks (p_stage_id IN NUMBER) IS
4285 SELECT visit_task_id, visit_task_number
4286 FROM ahl_visit_tasks_b
4287 WHERE stage_id = p_stage_id
4288 AND task_type_code IN ('PLANNED', 'UNPLANNED')
4289 AND status_code NOT IN ('DELETED','RELEASED');
4290
4291 --To fetch all the route attributes of a task.
4292 Cursor get_task_route_attr (p_task_id IN NUMBER) IS
4293 SELECT route.route_no,
4294 route.route_type_code,
4295 kfv.concatenated_segments,
4296 route.process_code
4297 FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes mrt
4298 WHERE tsk.mr_route_id = mrt.mr_route_id
4299 AND mrt.route_id = route.route_id
4300 AND tsk.visit_task_id = p_task_id
4301 AND kfv.route_id = route.route_id;
4302
4303 l_route_attr_rec get_task_route_attr%ROWTYPE;
4304
4305 --To fetch the WBS rule IDs if any of the passed route attributes match with pre-attributes of a wbs rule.
4306 Cursor get_rule_id (p_route_no IN VARCHAR2, p_route_type IN VARCHAR2, p_system_code IN VARCHAR2, p_process_code IN VARCHAR2) IS
4307 SELECT rule_id
4308 FROM ahl_wbs_rules
4309 WHERE NVL(PRE_ROUTE_NUMBER,'X') = NVL2(PRE_ROUTE_NUMBER,p_route_no, 'X')
4310 AND NVL(PRE_ROUTE_TYPE_CODE,'X') = NVL2(PRE_ROUTE_TYPE_CODE,p_route_type, 'X')
4311 AND NVL(PRE_SYSTEM_CODE,'X') = NVL2(PRE_SYSTEM_CODE,p_system_code, 'X')
4312 AND NVL(PRE_PROCESS_CODE,'X') = NVL2(PRE_PROCESS_CODE,p_process_code, 'X');
4313
4314 --To fetch the WBS post route attributes of the passed rule id.
4315 Cursor get_post_route_attr (p_rule_id IN NUMBER) IS
4316 SELECT POST_ROUTE_NUMBER, POST_ROUTE_TYPE_CODE, POST_SYSTEM_CODE, POST_PROCESS_CODE
4317 FROM ahl_wbs_rules
4318 WHERE RULE_ID = p_rule_id;
4319
4320 --To match the post route attributes with the route attributes of all the tasks that fall under the same stage.
4321 Cursor get_task_id (p_stage_id IN NUMBER, p_route_no IN VARCHAR2, p_route_type IN VARCHAR2, p_system_code IN VARCHAR2, p_process_code IN VARCHAR2) IS
4322 SELECT tsk.visit_task_id, tsk.visit_task_number
4323 FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes assoc
4324 WHERE kfv.route_id = route.route_id
4325 AND NVL2(p_route_no, route.ROUTE_NO,'X') = NVL(p_route_no, 'X')
4326 AND NVL2(p_route_type,route.ROUTE_TYPE_CODE,'X') = NVL(p_route_type, 'X')
4327 AND NVL2(p_system_code,kfv.CONCATENATED_SEGMENTS,'X') = NVL(p_system_code, 'X')
4328 AND NVL2(p_process_code,route.PROCESS_CODE,'X') = NVL(p_process_code, 'X')
4329 AND tsk.mr_route_id = assoc.mr_route_id
4330 AND route.route_id = assoc.route_id
4331 AND tsk.stage_id = p_stage_id
4332 AND tsk.status_code <> 'DELETED';
4333
4334 Cursor check_rule_exists (p_visit_task_id IN NUMBER, p_parent_task_id IN NUMBER) IS
4335 SELECT 'X' FROM ahl_task_links
4336 WHERE visit_task_id = p_visit_task_id
4337 AND parent_task_id = p_parent_task_id;
4338 l_dummy VARCHAR2(1);
4339 begin
4340 --------------------- Initialize -----------------------
4341 SAVEPOINT Handle_Task_Dependency;
4342
4343 IF (l_log_procedure >= l_log_current_level) THEN
4344 fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure');
4345 END IF;
4346
4347 -- Initialize message list if p_init_msg_list is set to TRUE.
4348 IF Fnd_Api.to_boolean(p_init_msg_list)
4349 THEN
4350 Fnd_Msg_Pub.initialize;
4351 END IF;
4352
4353 -- Initialize API return status to success
4354 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4355
4356 -- Standard call to check for call compatibility.
4357 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4358 p_api_version,
4359 l_api_name,G_PKG_NAME)
4360 THEN
4361 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4362 END IF;
4363
4364 -- Loop through all the task records that are passed to this API
4365 FOR i IN l_task_tbl.FIRST..l_task_tbl.LAST
4366 LOOP
4367 -- Loop through all the tasks in the hierarchy of a given task
4368 FOR l_stage_rec IN c_stage_details(l_task_tbl(i).visit_task_id)
4369 LOOP
4370 IF (l_log_statement >= l_log_current_level) THEN
4371 fnd_log.string(l_log_statement,
4372 L_DEBUG_KEY,
4373 'Before calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES l_stage_rec.visit_task_id= '|| l_stage_rec.visit_task_id);
4374 END IF;
4375
4376 AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
4377 P_API_VERSION => 1.0,
4378 P_VISIT_ID => p_visit_id,
4379 P_VISIT_TASK_ID => l_stage_rec.visit_task_id,
4380 P_STAGE_NAME => l_stage_rec.STAGE_NAME,
4381 X_STAGE_ID => l_stage_rec.STAGE_ID,
4382 X_RETURN_STATUS => l_return_status,
4383 X_MSG_COUNT => l_msg_count,
4384 X_MSG_DATA => l_msg_data );
4385
4386 IF (l_log_statement >= l_log_current_level) THEN
4387 fnd_log.string(l_log_statement,
4388 L_DEBUG_KEY,
4389 'After calling AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES: Return Status = ' || l_return_status );
4390 END IF;
4391 IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4392 x_msg_count := l_msg_count;
4393 x_return_status := l_return_status;
4394 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4395 RAISE Fnd_Api.g_exc_error;
4396 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4397 RAISE Fnd_Api.g_exc_unexpected_error;
4398 END IF;
4399 END IF;
4400
4401 END LOOP; -- Loop through all the tasks hierarchy
4402 END LOOP; -- Loop through all the tasks
4403
4404 --Convertion from WBS rules to task rules :: start.
4405 -- First, get all the stages of a visit and loop through all those fetched stages
4406 FOR l_stages_rec IN get_visit_stages(p_visit_id)
4407 LOOP
4408 IF (l_log_statement >= l_log_current_level) THEN
4409 fnd_log.string(l_log_statement,
4410 L_DEBUG_KEY,
4411 'stage id : '||l_stages_rec.stage_id||' visit id : '||p_visit_id);
4412 END IF;
4413
4414 -- Secondly, for a given stage of a visit, get the all the route tasks that fall under that stage
4415 -- Loop through all those fetched tasks
4416 FOR l_tasks_rec IN get_stage_tasks(l_stages_rec.stage_id)
4417 LOOP
4418 IF (l_log_statement >= l_log_current_level) THEN
4419 fnd_log.string(l_log_statement,
4420 L_DEBUG_KEY,
4421 'visit task id : '||l_tasks_rec.visit_task_id);
4422 END IF;
4423
4424 -- For a route task, fetched the route attributes and store them in l_route_attr_rec.
4425 OPEN get_task_route_attr(l_tasks_rec.visit_task_id);
4426 FETCH get_task_route_attr INTO l_route_attr_rec;
4427 CLOSE get_task_route_attr;
4428
4429 IF (l_log_statement >= l_log_current_level) THEN
4430 fnd_log.string(l_log_statement,
4431 L_DEBUG_KEY,
4432 'Pre route number : '||l_route_attr_rec.route_no);
4433 END IF;
4434
4435 -- For the above stored route task attributes, see if any of the attributes match with the pre-attributes
4436 -- of a wbs rule. If yes, then get the all those rule ids and loop though the rule IDs
4437 FOR l_rule_id_rec IN get_rule_id(l_route_attr_rec.route_no, l_route_attr_rec.route_type_code, l_route_attr_rec.concatenated_segments, l_route_attr_rec.process_code)
4438 LOOP
4439 IF (l_log_statement >= l_log_current_level) THEN
4440 fnd_log.string(l_log_statement,
4441 L_DEBUG_KEY,
4442 'Rule id is : '||l_rule_id_rec.rule_id);
4443 END IF;
4444
4445 -- Fetch the post attributes of the passed WBS rule id
4446 OPEN get_post_route_attr(l_rule_id_rec.rule_id);
4447 FETCH get_post_route_attr INTO l_post_rout_no, l_post_route_type, l_post_system_code, l_post_process_code;
4448 CLOSE get_post_route_attr;
4449
4450 IF (l_log_statement >= l_log_current_level) THEN
4451 fnd_log.string(l_log_statement,
4452 L_DEBUG_KEY,
4453 'Post route no: '||l_post_rout_no);
4454 END IF;
4455
4456 -- See if the passed route attributes match with any of the task that falls within the same stage
4457 -- Loop though all those matched tasks ids
4458 FOR l_task_id_rec IN get_task_id(l_stages_rec.stage_id, l_post_rout_no,l_post_route_type,l_post_system_code,l_post_process_code)
4459 LOOP
4460 l_task_link_rec.VISIT_TASK_ID := l_task_id_rec.visit_task_id;
4461 --l_task_link_rec.visit_task_number := l_tasks_rec.visit_task_number;
4462 l_task_link_rec.PARENT_TASK_ID := l_tasks_rec.visit_task_id;
4463 -- SKPATHAK :: 10-NOV-2011 :: changed STARTS AFTER END to StartsAfterEnd
4464 -- SKPATHAK :: 11-NOV-2011 :: Bug 13374723 :: changed StartsAfterEnd to STARTSAFTEREND
4465 l_task_link_rec.HIERARCHY_INDICATOR := 'STARTSAFTEREND';
4466 IF (l_log_statement >= l_log_current_level) THEN
4467 fnd_log.string(l_log_statement,L_DEBUG_KEY,
4468 'Before calling create task links. Visit task id :' ||l_task_link_rec.VISIT_TASK_ID||'Parent task id :' ||l_task_link_rec.PARENT_TASK_ID||'Relation type is :' ||l_task_link_rec.HIERARCHY_INDICATOR);
4469 END IF;
4470
4471 OPEN check_rule_exists (l_task_link_rec.VISIT_TASK_ID, l_task_link_rec.PARENT_TASK_ID);
4472 FETCH check_rule_exists INTO l_dummy;
4473 IF check_rule_exists%NOTFOUND THEN
4474 AHL_VWP_TASKS_LINKS_PVT.Create_Task_Links (p_task_link_rec =>l_task_link_rec);
4475 END IF;
4476 CLOSE check_rule_exists;
4477
4478 END LOOP;-- Loop through all the tasks that fall under same stage
4479
4480 END LOOP; -- Loop through all the WBS rule ids
4481
4482 END LOOP;-- Loop through all those fetched tasks
4483
4484 END LOOP;-- Loop through all the stages of a visit
4485 --Convertion from WBS rules to task rules :: end.
4486
4487 --PRAKKUM :: 05/04/2012 :: Bug 13711800 :: Commented out as this call no need to be here
4488 /*
4489 IF (l_log_statement >= l_log_current_level) THEN
4490 fnd_log.string(l_log_statement,
4491 L_DEBUG_KEY,
4492 'in HTD api; Before calling UPDATE_STAGES_HIERARICHY');
4493 END IF;
4494
4495 --Calculate and update all the stages planned start and end dates
4496 AHL_VWP_VISITS_STAGES_PVT.UPDATE_STAGES_HIERARICHY (
4497 p_api_version => 1.0,
4498 p_init_msg_list => Fnd_Api.g_false,
4499 p_commit => Fnd_Api.g_false,
4500 p_validation_level => Fnd_Api.g_valid_level_full,
4501 p_module_type => 'JSP',
4502 p_visit_id => p_visit_id,
4503 x_return_status => l_return_status,
4504 x_msg_count => l_msg_count,
4505 x_msg_data => l_msg_data
4506 );
4507
4508 IF (l_log_statement >= l_log_current_level) THEN
4509 fnd_log.string(l_log_statement,
4510 L_DEBUG_KEY,
4511 'After calling UPDATE_STAGES_HIERARICHY and return status is:'||l_return_status);
4512 END IF;
4513
4514 IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4515 x_msg_count := l_msg_count;
4516 x_return_status := l_return_status;
4517 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4518 RAISE Fnd_Api.g_exc_error;
4519 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4520 RAISE Fnd_Api.g_exc_unexpected_error;
4521 END IF;
4522 END IF;
4523 */
4524
4525 EXCEPTION
4526 WHEN Fnd_Api.g_exc_error THEN
4527 x_return_status := Fnd_Api.g_ret_sts_error;
4528 ROLLBACK TO Handle_Task_Dependency;
4529 Fnd_Msg_Pub.count_and_get(
4530 p_encoded => Fnd_Api.g_false,
4531 p_count => x_msg_count,
4532 p_data => x_msg_data
4533 );
4534 WHEN Fnd_Api.g_exc_unexpected_error THEN
4535 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4536 ROLLBACK TO Handle_Task_Dependency;
4537 Fnd_Msg_Pub.count_and_get (
4538 p_encoded => Fnd_Api.g_false,
4539 p_count => x_msg_count,
4540 p_data => x_msg_data
4541 );
4542 WHEN OTHERS THEN
4543 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4544 ROLLBACK TO Handle_Task_Dependency;
4545 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
4546 THEN
4547 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
4548 END IF;
4549 Fnd_Msg_Pub.count_and_get (
4550 p_encoded => Fnd_Api.g_false,
4551 p_count => x_msg_count,
4552 p_data => x_msg_data
4553 );
4554
4555 END Handle_Task_Dependency;
4556
4557 --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: end
4558
4559
4560 --SKPATHAK :: 02-MAY-2011 :: VWPE :: start
4561 --------------------------------------------------------------------
4562 -- PROCEDURE
4563 -- Warranty_Not_Applicable
4564 --
4565 -- PURPOSE
4566 -- To make warranty not applicable for a task
4567 --------------------------------------------------------------------
4568 PROCEDURE Warranty_Not_Applicable (
4569 p_api_version IN NUMBER,
4570 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
4571 p_commit IN VARCHAR2 := Fnd_Api.g_false,
4572 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
4573 p_task_tbl IN Task_Tbl_Type,
4574 x_return_status OUT NOCOPY VARCHAR2,
4575 x_msg_count OUT NOCOPY NUMBER,
4576 x_msg_data OUT NOCOPY VARCHAR2
4577 )
4578 is
4579
4580 -- Define local Variables
4581 L_API_VERSION CONSTANT NUMBER := 1.0;
4582 L_API_NAME CONSTANT VARCHAR2(30) := 'Warranty_Not_Applicable';
4583 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
4584 l_return_status VARCHAR2(1);
4585 l_msg_data VARCHAR2(2000);
4586 l_task_tbl AHL_VWP_RULES_PVT.Task_Tbl_Type := p_task_tbl;
4587 l_entl_rec_tbl AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type;
4588 l_msg_count NUMBER;
4589
4590 CURSOR c_warranty_record (p_task_id IN NUMBER)
4591 IS
4592 SELECT ATSK.INSTANCE_ID,
4593 ATSK.OBJECT_VERSION_NUMBER,
4594 ATSK.SERVICE_REQUEST_ID,
4595 ASWE.WARRANTY_ENTITLEMENT_ID,
4596 AWCB.CONTRACT_NUMBER,
4597 LKUP1.MEANING ENTITLEMENT_STATUS,
4598 ASWE.ENTITLEMENT_STATUS_CODE,
4599 AWCL.CLAIM_NAME,
4600 LKUP2.MEANING CLAIM_STATUS,
4601 AWCL.CLAIM_STATUS_CODE,
4602 AHL_WARRANTY_ENTL_PVT.IS_TASK_WARRANTY_AVAILABLE(ATSK.VISIT_TASK_ID) TASK_WARRANTY,
4603 AHL_WARRANTY_CONTRACTS_PVT.IS_INSTANCE_WARRANTY_AVAILABLE(ATSK.INSTANCE_ID) INSTANCE_WARRANTY,
4604 'Y' WARRANTY_NOT_APPL_FLAG
4605 FROM AHL_VISIT_TASKS_B ATSK,
4606 AHL_WARRANTY_ENTITLEMENTS ASWE,
4607 AHL_WARRANTY_CLAIMS_B AWCL,
4608 AHL_WARRANTY_CONTRACTS_B AWCB,
4609 FND_LOOKUP_VALUES_VL LKUP1,
4610 FND_LOOKUP_VALUES_VL LKUP2
4611 WHERE ATSK.VISIT_TASK_ID = ASWE.VISIT_TASK_ID(+)
4612 AND ASWE.WARRANTY_CONTRACT_ID = AWCB.WARRANTY_CONTRACT_ID(+)
4613 AND ASWE.WARRANTY_CLAIM_ID = AWCL.WARRANTY_CLAIM_ID(+)
4614 AND LKUP1.LOOKUP_TYPE(+) = 'AHL_WARRANTY_ENTLMNT_STATUS'
4615 AND lkup1.lookup_code(+) = ASWE.entitlement_status_code
4616 AND LKUP2.LOOKUP_TYPE(+) = 'AHL_WARRANTY_CLAIM_STATUS'
4617 AND LKUP2.LOOKUP_CODE(+) = AWCL.CLAIM_STATUS_CODE
4618 AND NVL(ATSK.STATUS_CODE,'X') <> 'DELETED'
4619 AND ATSK.VISIT_TASK_ID = p_task_id;
4620 c_warranty_rec c_warranty_record%ROWTYPE;
4621
4622 begin
4623 --------------------- Initialize -----------------------
4624 SAVEPOINT Warranty_Not_Applicable;
4625
4626 IF (l_log_procedure >= l_log_current_level) THEN
4627 fnd_log.string(l_log_procedure,L_DEBUG_KEY||'.begin','At the start of the PLSQL procedure');
4628 END IF;
4629
4630 -- Initialize message list if p_init_msg_list is set to TRUE.
4631 IF Fnd_Api.to_boolean(p_init_msg_list)
4632 THEN
4633 Fnd_Msg_Pub.initialize;
4634 END IF;
4635
4636 -- Initialize API return status to success
4637 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4638
4639 -- Standard call to check for call compatibility.
4640 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4641 p_api_version,
4642 l_api_name,G_PKG_NAME)
4643 THEN
4644 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4645 END IF;
4646
4647 FOR i IN l_task_tbl.FIRST..l_task_tbl.LAST
4648 LOOP
4649 OPEN c_warranty_record (l_task_tbl(i).visit_task_id);
4650 FETCH c_warranty_record INTO c_warranty_rec;
4651 CLOSE c_warranty_record;
4652 IF (c_warranty_rec.OBJECT_VERSION_NUMBER <> l_task_tbl(i).OBJECT_VERSION_NUMBER) THEN
4653 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
4654 Fnd_Msg_Pub.ADD;
4655 RAISE Fnd_Api.G_EXC_ERROR;
4656 END IF;
4657
4658 l_task_tbl(i).instance_id := c_warranty_rec.instance_id;
4659 l_task_tbl(i).warranty_entitlement_id := c_warranty_rec.warranty_entitlement_id;
4660 l_task_tbl(i).entitlement_status := c_warranty_rec.entitlement_status;
4661 l_task_tbl(i).entitlement_status_code := c_warranty_rec.entitlement_status_code;
4662 l_task_tbl(i).contract_number := c_warranty_rec.contract_number;
4663 l_task_tbl(i).claim_name := c_warranty_rec.claim_name;
4664 l_task_tbl(i).claim_status := c_warranty_rec.claim_status;
4665 l_task_tbl(i).claim_status_code := c_warranty_rec.claim_status_code;
4666 l_task_tbl(i).service_request_id := c_warranty_rec.service_request_id;
4667 l_task_tbl(i).task_warranty := c_warranty_rec.task_warranty;
4668 l_task_tbl(i).instance_warranty := c_warranty_rec.instance_warranty;
4669 l_task_tbl(i).warranty_not_appl_flag := 'Y';
4670
4671 AHL_VWP_RULES_PVT.Get_Warranty_Rec_for_Update(
4672 p_task_rec => l_task_tbl(i),
4673 x_warranty_entl_tbl => l_entl_rec_tbl);
4674
4675 IF (l_log_statement >= l_log_current_level) THEN
4676 fnd_log.string(l_log_statement,
4677 L_DEBUG_KEY,
4678 'Before calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements.');
4679 END IF;
4680
4681 AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements(
4682 p_user_role => AHL_WARRANTY_ENTL_PVT.G_USER_PLANNER,
4683 p_x_warranty_entl_tbl => l_entl_rec_tbl,
4684 x_return_status => l_return_status,
4685 x_msg_count => l_msg_count,
4686 x_msg_data => l_msg_data);
4687
4688 IF (l_log_statement >= l_log_current_level) THEN
4689 fnd_log.string(l_log_statement,
4690 L_DEBUG_KEY,
4691 'After calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements. Return Status = ' ||
4692 l_return_status);
4693 END IF;
4694
4695 l_msg_count := Fnd_Msg_Pub.count_msg;
4696 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
4697 x_msg_count := l_msg_count;
4698 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4699 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4700 END IF;
4701 END LOOP;
4702
4703 -- MANESING::Component Maintenance Planning Project, 07-Aug-2011, added standard check for commit
4704 -- Standard check of p_commit
4705 IF FND_API.to_boolean(p_commit) THEN
4706 COMMIT WORK;
4707 END IF;
4708
4709 IF (l_log_procedure >= l_log_current_level) THEN
4710 FND_LOG.string(l_log_procedure, l_debug_key || '.end', 'At the end of PL/SQL procedure');
4711 END IF;
4712
4713 EXCEPTION
4714 WHEN Fnd_Api.g_exc_error THEN
4715 ROLLBACK TO Warranty_Not_Applicable;
4716 x_return_status := Fnd_Api.g_ret_sts_error;
4717 Fnd_Msg_Pub.count_and_get(
4718 p_encoded => Fnd_Api.g_false,
4719 p_count => x_msg_count,
4720 p_data => x_msg_data
4721 );
4722 WHEN Fnd_Api.g_exc_unexpected_error THEN
4723 ROLLBACK TO Warranty_Not_Applicable;
4724 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4725 Fnd_Msg_Pub.count_and_get (
4726 p_encoded => Fnd_Api.g_false,
4727 p_count => x_msg_count,
4728 p_data => x_msg_data
4729 );
4730 WHEN OTHERS THEN
4731 ROLLBACK TO Warranty_Not_Applicable;
4732 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
4733 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
4734 THEN
4735 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
4736 END IF;
4737 Fnd_Msg_Pub.count_and_get (
4738 p_encoded => Fnd_Api.g_false,
4739 p_count => x_msg_count,
4740 p_data => x_msg_data
4741 );
4742
4743 END Warranty_Not_Applicable;
4744
4745 --SKPATHAK :: 02-MAY-2011 :: VWPE :: end
4746
4747 --VWPE: ER:12424063 :: AJPRASAN :: 14-MAR-2011 :: START
4748 -------------------------------------------------------------------
4749 -- Start of Comments
4750 -- Procedure name : Add_Return_To_Supply
4751 -- Type : Public
4752 -- Function : To set RETURN_TO_SUPPLY flag to 'Y' for a task after validating.
4753 -- Pre-reqs :
4754 -- Parameters : p_task_tbl_type IN AHL_VWP_RULES_PVT.Task_Tbl_Type
4755 --
4756 -- End of Comments
4757 -------------------------------------------------------------------
4758
4759 PROCEDURE Add_Return_To_Supply
4760 (
4761 p_api_version IN NUMBER,
4762 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4763 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4764 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4765 p_task_tbl_type IN AHL_VWP_RULES_PVT.Task_Tbl_Type,
4766 x_return_status OUT NOCOPY VARCHAR2,
4767 x_msg_count OUT NOCOPY NUMBER,
4768 x_msg_data OUT NOCOPY VARCHAR2
4769 )
4770 IS
4771 l_api_version CONSTANT NUMBER := 1.0;
4772 l_api_name CONSTANT VARCHAR2(30) := 'Add_Return_To_Supply';
4773 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
4774 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
4775
4776 -- Define local cursors
4777 -- Get the preliminary details of a task from task ID
4778 CURSOR get_task_prelim_details(c_visit_task_id NUMBER) IS
4779 SELECT visit_id,
4780 visit_task_number,
4781 status_code,
4782 task_type_code,
4783 instance_id,
4784 return_to_supply_flag current_rts
4785 FROM ahl_visit_tasks_b
4786 WHERE visit_task_id = c_visit_task_id;
4787
4788 -- Get the number of tasks flagged RTS for an instance, for all tasks other than this task
4789 CURSOR get_rts_count(c_visit_id NUMBER, c_visit_task_id NUMBER, c_instance_id NUMBER) IS
4790 SELECT count(visit_task_id) rts_count
4791 FROM ahl_visit_tasks_b
4792 WHERE (status_code = 'PLANNING' OR status_code = 'RELEASED')
4793 AND return_to_supply_flag = 'Y'
4794 AND visit_id = c_visit_id
4795 AND visit_task_id <> c_visit_task_id
4796 AND instance_id = c_instance_id;
4797 -- Kasridha: Changes for Component Maintenance Planning Project Begins
4798 -- When the first RTS workorder is added to a visit
4799 -- move the items in the repair batch to the inrepair locator
4800 -- Cursor to get the RTS workorder for a repair batch
4801 CURSOR c_get_rts_wo_id(p_rpr_batch_task_id NUMBER, p_instance_id NUMBER) IS
4802 SELECT wip_entity_id FROM ahl_workorders
4803 WHERE status_code IN ('1', '3', '6', '19', '20', '17')
4804 --unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
4805 AND visit_task_id IN
4806 (SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt
4807 WHERE vt.cost_parent_id IS NOT NULL
4808 AND NVL(vt.return_to_supply_flag,'N') = 'Y'
4809 AND vt.instance_id = p_instance_id
4810 START WITH vt.visit_task_id = p_rpr_batch_task_id
4811 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
4812 );
4813 --Cursor to get the repair batch details for a given task
4814 CURSOR get_repair_batch_dtls_csr(p_task_id_csr IN NUMBER) IS
4815 SELECT repair_batch_name, visit_task_id FROM ahl_visit_tasks_b WHERE
4816 repair_batch_name IS NOT NULL
4817 START WITH visit_task_id = p_task_id_csr
4818 CONNECT BY PRIOR cost_parent_id = visit_task_id;
4819
4820 -- Kasridha: Changes for Component Maintenance Planning Project Ends
4821
4822 -- Define local variables
4823 l_visit_id NUMBER;
4824 l_visit_task_number NUMBER;
4825 l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
4826 l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
4827 l_instance_id NUMBER;
4828 l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
4829 l_rts_count NUMBER;
4830 l_task_rec get_task_prelim_details%ROWTYPE;
4831 l_count_rec get_rts_count%ROWTYPE;
4832 l_is_comp_visit VARCHAR2(1);
4833 l_rpr_batch_task_id NUMBER := NULL;
4834 l_rpr_batch_name VARCHAR2(100);
4835 l_rts_wip_entity_id NUMBER := NULL;
4836 l_first_task_flag VARCHAR2(1) := 'Y';
4837
4838 BEGIN
4839
4840 -- Standard Start of API savepoint
4841 SAVEPOINT add_rts_sp;
4842
4843 IF (l_log_procedure >= l_log_current_level) THEN
4844 FND_LOG.string(FND_LOG.level_procedure, L_DEBUG || '.begin','At the start of PLSQL procedure');
4845 END IF;
4846
4847 -- Initialize message list if p_init_msg_list is set to TRUE.
4848 IF FND_API.to_boolean( p_init_msg_list ) THEN
4849 FND_MSG_PUB.initialize;
4850 END IF;
4851
4852 -- Initialize API return status to success
4853 x_return_status := FND_API.G_RET_STS_SUCCESS;
4854
4855 -- Standard call to check for call compatibility.
4856 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4858 END IF;
4859
4860 FOR i in p_task_tbl_type.FIRST..p_task_tbl_type.LAST
4861 LOOP
4862 FOR l_task_rec in get_task_prelim_details(p_task_tbl_type(i).visit_task_id)
4863 LOOP
4864 l_visit_id := l_task_rec.visit_id;
4865 l_visit_task_number := l_task_rec.visit_task_number;
4866 l_status_code := l_task_rec.status_code;
4867 l_task_type_code := l_task_rec.task_type_code;
4868 l_instance_id := l_task_rec.instance_id;
4869 l_current_rts := l_task_rec.current_rts;
4870 END LOOP;
4871
4872 IF (l_log_statement >= l_log_current_level) THEN
4873 FND_LOG.string(l_log_statement, L_DEBUG,
4874 'Retrived values: Visit id:' || l_visit_id || ', task number:' || l_visit_task_number
4875 ||', task status:' || l_status_code || ', task type:' || l_task_type_code || ', instance id:'
4876 || l_instance_id || ', Current Value of RTS flag before updating:' || l_current_rts);
4877 END IF;
4878
4879 -- Check Task Type
4880 IF (l_task_type_code = 'SUMMARY' OR l_task_type_code = 'DUPLICATE') THEN
4881 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_INVALID_TASK');
4882 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
4883 FND_MSG_PUB.add;
4884 x_return_status := FND_API.G_RET_STS_ERROR;
4885
4886 ELSE
4887 -- Check Task Status
4888 IF (l_status_code <> 'PLANNING') THEN
4889 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_INVALID_STATUS');
4890 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
4891 FND_MSG_PUB.add;
4892 x_return_status := FND_API.G_RET_STS_ERROR;
4893 END IF;
4894
4895 -- Check if this flag already marked RTS
4896 IF(l_current_rts = 'Y') THEN
4897 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_PRESENT');
4898 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
4899 FND_MSG_PUB.add;
4900 x_return_status := FND_API.G_RET_STS_ERROR;
4901 END IF;
4902
4903 -- Cursor to get the RTS count for this instance
4904 FOR l_count_rec in get_rts_count(l_visit_id, p_task_tbl_type(i).visit_task_id, l_instance_id)
4905 LOOP
4906 l_rts_count := l_count_rec.rts_count;
4907 END LOOP;
4908
4909 --Check if this instance is already marked RTS
4910 IF (l_rts_count > 0) THEN
4911 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_FOUND');
4912 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
4913 FND_MSG_PUB.add;
4914 x_return_status := FND_API.G_RET_STS_ERROR;
4915 END IF;
4916 END IF;
4917
4918 -- Kasridha: Changes for Component Maintenance Planning Project Begins
4919 --Check whether an RTS workorder already exist for the repair batch
4920 l_is_comp_visit := AHL_CMP_UTIL_PKG.Is_Comp_Visit (l_visit_id);
4921 IF l_is_comp_visit = 'Y' THEN
4922
4923 l_rpr_batch_name := NULL;
4924 l_rpr_batch_task_id := NULL;
4925 l_rts_wip_entity_id := NULL;
4926
4927 OPEN get_repair_batch_dtls_csr(p_task_tbl_type(i).visit_task_id);
4928 FETCH get_repair_batch_dtls_csr INTO l_rpr_batch_name, l_rpr_batch_task_id;
4929 CLOSE get_repair_batch_dtls_csr;
4930
4931 OPEN c_get_rts_wo_id(l_rpr_batch_task_id, l_instance_id);
4932 FETCH c_get_rts_wo_id INTO l_rts_wip_entity_id;
4933 CLOSE c_get_rts_wo_id;
4934
4935 IF (l_log_statement >= l_log_current_level) THEN
4936 fnd_log.string(l_log_statement,
4937 l_debug,
4938 'RTS Workorder existing in RB::' || l_rts_wip_entity_id
4939 || ' For Instance : ' || l_instance_id);
4940 END IF;
4941 END IF;
4942 -- Kasridha: Changes for Component Maintenance Planning Project Ends
4943
4944
4945 -- Perform update if the status is still success
4946 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4947 UPDATE AHL_VISIT_TASKS_B
4948 SET object_version_number = object_version_number + 1,
4949 last_update_date = sysdate,
4950 last_updated_by = FND_GLOBAL.user_id,
4951 last_update_login = FND_GLOBAL.login_id,
4952 return_to_supply_flag = 'Y'
4953 WHERE visit_task_id = p_task_tbl_type(i).visit_task_id
4954 AND object_version_number = p_task_tbl_type(i).object_version_number;
4955
4956 IF ( SQL%ROWCOUNT = 0 ) THEN
4957 FND_MESSAGE.set_name('AHL','AHL_TASK_RECORD_CHANGED');
4958 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
4959 FND_MSG_PUB.add;
4960 x_return_status := FND_API.G_RET_STS_ERROR;
4961 END IF;
4962
4963 END IF;
4964
4965 -- Kasridha: Changes for Component Maintenance Planning Project Begins
4966 --Check whether an RTS workorder already exist for the repair batch
4967 -- If not call move_to_inrepair_locator procedure to
4968 -- move the instance to the inrepair locator
4969 IF l_is_comp_visit = 'Y' AND l_rpr_batch_task_id IS NOT NULL
4970 AND l_rts_wip_entity_id IS NULL THEN
4971
4972 OPEN c_get_rts_wo_id(l_rpr_batch_task_id, l_instance_id);
4973 FETCH c_get_rts_wo_id INTO l_rts_wip_entity_id;
4974 CLOSE c_get_rts_wo_id;
4975
4976 IF (l_log_statement >= l_log_current_level) THEN
4977 fnd_log.string(l_log_statement,
4978 l_debug,
4979 'New RTS Workorder in RB::' || l_rts_wip_entity_id );
4980 END IF;
4981
4982 IF l_rts_wip_entity_id IS NOT NULL THEN
4983 IF (l_log_statement >= l_log_current_level) THEN
4984 fnd_log.string(l_log_statement,
4985 l_debug,
4986 'Calling AHL_CMP_PVT.Move_To_InRepair_Locator');
4987 END IF;
4988
4989 AHL_CMP_PVT.Move_To_InRepair_Locator(
4990 p_api_version => l_api_version,
4991 p_init_msg_list => Fnd_Api.g_false,
4992 p_commit => Fnd_Api.g_false,
4993 p_validation_level => p_validation_level,
4994 p_wip_entity_id => l_rts_wip_entity_id,
4995 p_instance_id => l_instance_id,
4996 p_repair_batch_name => l_rpr_batch_name,
4997 x_return_status => x_return_status,
4998 x_msg_count => x_msg_count,
4999 x_msg_data => x_msg_data);
5000
5001
5002 IF (l_log_statement >= l_log_current_level) THEN
5003 fnd_log.string(l_log_statement,
5004 l_debug,
5005 'After Calling AHL_VWP_PROJ_PROD_PVT.Move_To_InRepair_Locator.' ||
5006 'Return Status = ' || x_return_status );
5007 END IF;
5008 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5009 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
5010 RAISE Fnd_Api.g_exc_error;
5011 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
5012 RAISE Fnd_Api.g_exc_unexpected_error;
5013 END IF;
5014
5015 END IF;
5016 END IF;
5017 END IF;
5018 -- Kasridha: Changes for Component Maintenance Planning Project Ends
5019
5020 END LOOP;
5021
5022 --Standard check for commit
5023 IF (FND_API.TO_BOOLEAN(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5024 COMMIT;
5025 END IF;
5026
5027 -- Count and Get messages
5028 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5029 p_count => x_msg_count,
5030 p_data => x_msg_data);
5031
5032 IF (l_log_procedure >= l_log_current_level) THEN
5033 fnd_log.string(l_log_procedure, L_DEBUG || '.end', 'At the end of PL SQL procedure');
5034 END IF;
5035
5036 EXCEPTION
5037 WHEN FND_API.G_EXC_ERROR THEN
5038 ROLLBACK TO add_rts_sp;
5039 x_return_status := FND_API.G_RET_STS_ERROR;
5040 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5041 p_count => x_msg_count,
5042 p_data => x_msg_data);
5043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5044 ROLLBACK TO add_rts_sp;
5045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5046 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5047 p_count => x_msg_count,
5048 p_data => x_msg_data);
5049 WHEN OTHERS THEN
5050 ROLLBACK TO add_rts_sp;
5051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5052 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5053 FND_MSG_PUB.add_exc_msg (G_PKG_NAME, L_API_NAME);
5054 END IF;
5055 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5056 p_count => x_msg_count,
5057 p_data => x_msg_data);
5058
5059 END Add_Return_To_Supply;
5060
5061
5062 -------------------------------------------------------------------
5063 -- Start of Comments
5064 -- Procedure name : Remove_Return_To_Supply
5065 -- Type : Public
5066 -- Function : To revoke RETURN_TO_SUPPLY flag for a task after validating.
5067 -- Pre-reqs :
5068 -- Parameters : p_task_tbl_type IN AHL_VWP_RULES_PVT.Task_Tbl_Type
5069 --
5070 -- End of Comments
5071 -------------------------------------------------------------------
5072
5073 PROCEDURE Remove_Return_To_Supply
5074 (
5075 p_api_version IN NUMBER,
5076 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5077 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5078 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5079 p_task_tbl_type IN AHL_VWP_RULES_PVT.Task_Tbl_Type,
5080 x_return_status OUT NOCOPY VARCHAR2,
5081 x_msg_count OUT NOCOPY NUMBER,
5082 x_msg_data OUT NOCOPY VARCHAR2
5083 )
5084 IS
5085 l_api_version CONSTANT NUMBER := 1.0;
5086 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Return_To_Supply';
5087 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
5088 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
5089
5090 -- Define local cursors
5091 -- Get the preliminary details of a task from task ID
5092 CURSOR get_task_prelim_details(c_visit_task_id NUMBER) IS
5093 SELECT visit_task_number,
5094 status_code,
5095 task_type_code,
5096 return_to_supply_flag current_rts
5097 FROM ahl_visit_tasks_b
5098 WHERE visit_task_id = c_visit_task_id;
5099
5100 -- Define local variables
5101 l_visit_task_number NUMBER;
5102 l_status_code AHL_VISIT_TASKS_B.STATUS_CODE%TYPE;
5103 l_task_type_code AHL_VISIT_TASKS_B.TASK_TYPE_CODE%TYPE;
5104 l_current_rts AHL_VISIT_TASKS_B.RETURN_TO_SUPPLY_FLAG%TYPE;
5105 l_task_rec get_task_prelim_details%ROWTYPE;
5106
5107 BEGIN
5108
5109 -- Standard Start of API savepoint
5110 SAVEPOINT remove_rts_sp;
5111
5112 IF (l_log_procedure >= l_log_current_level) THEN
5113 FND_LOG.string(FND_LOG.level_procedure, L_DEBUG || '.begin','At the start of PLSQL procedure');
5114 END IF;
5115
5116 -- Initialize message list if p_init_msg_list is set to TRUE.
5117 IF FND_API.to_boolean( p_init_msg_list ) THEN
5118 FND_MSG_PUB.initialize;
5119 END IF;
5120
5121 -- Initialize API return status to success
5122 x_return_status := FND_API.G_RET_STS_SUCCESS;
5123
5124 -- Standard call to check for call compatibility.
5125 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
5126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5127 END IF;
5128
5129 FOR i in p_task_tbl_type.FIRST..p_task_tbl_type.LAST
5130 LOOP
5131 FOR l_task_rec in get_task_prelim_details(p_task_tbl_type(i).visit_task_id)
5132 LOOP
5133 l_visit_task_number := l_task_rec.visit_task_number;
5134 l_status_code := l_task_rec.status_code;
5135 l_task_type_code := l_task_rec.task_type_code;
5136 l_current_rts := l_task_rec.current_rts;
5137 END LOOP;
5138
5139 IF (l_log_statement >= l_log_current_level) THEN
5140 FND_LOG.string(l_log_statement, L_DEBUG,
5141 'Retrived values: task number:' || l_visit_task_number ||', task status:' || l_status_code || ', task type:' || l_task_type_code || ', Current Value of RTS flag before updating:' || l_current_rts);
5142 END IF;
5143
5144 -- Check Task Type
5145 IF (l_task_type_code = 'SUMMARY' OR l_task_type_code = 'DUPLICATE') THEN
5146 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_INVALID_TASK');
5147 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
5148 FND_MSG_PUB.add;
5149 x_return_status := FND_API.G_RET_STS_ERROR;
5150
5151 ELSE
5152 -- Check Task Status
5153 IF (l_status_code <> 'PLANNING') THEN
5154 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_INVALID_STATUS');
5155 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
5156 FND_MSG_PUB.add;
5157 x_return_status := FND_API.G_RET_STS_ERROR;
5158 END IF;
5159
5160 -- Check if this flag already unmarked RTS
5161 IF(l_current_rts = 'N') THEN
5162 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_NOT_PRESENT');
5163 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
5164 FND_MSG_PUB.add;
5165 x_return_status := FND_API.G_RET_STS_ERROR;
5166 END IF;
5167 END IF;
5168
5169 -- Perform update if the status is still success
5170 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5171 UPDATE AHL_VISIT_TASKS_B
5172 SET object_version_number = object_version_number + 1,
5173 last_update_date = sysdate,
5174 last_updated_by = FND_GLOBAL.user_id,
5175 last_update_login = FND_GLOBAL.login_id,
5176 return_to_supply_flag = 'N'
5177 WHERE visit_task_id = p_task_tbl_type(i).visit_task_id
5178 AND object_version_number = p_task_tbl_type(i).object_version_number;
5179
5180 IF ( SQL%ROWCOUNT = 0 ) THEN
5181 FND_MESSAGE.set_name('AHL','AHL_TASK_RECORD_CHANGED');
5182 FND_MESSAGE.set_token( 'TASK_NO', l_visit_task_number );
5183 FND_MSG_PUB.add;
5184 x_return_status := FND_API.G_RET_STS_ERROR;
5185 END IF;
5186 END IF;
5187
5188 END LOOP;
5189 --Standard check for commit
5190 IF (FND_API.TO_BOOLEAN(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5191 COMMIT;
5192 END IF;
5193
5194 -- Count and Get messages
5195 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5196 p_count => x_msg_count,
5197 p_data => x_msg_data);
5198
5199 IF (l_log_procedure >= l_log_current_level) THEN
5200 fnd_log.string(l_log_procedure, L_DEBUG || '.end', 'At the end of PL SQL procedure');
5201 END IF;
5202
5203 EXCEPTION
5204 WHEN FND_API.G_EXC_ERROR THEN
5205 ROLLBACK TO remove_rts_sp;
5206 x_return_status := FND_API.G_RET_STS_ERROR;
5207 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5208 p_count => x_msg_count,
5209 p_data => x_msg_data);
5210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5211 ROLLBACK TO remove_rts_sp;
5212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5213 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5214 p_count => x_msg_count,
5215 p_data => x_msg_data);
5216 WHEN OTHERS THEN
5217 ROLLBACK TO remove_rts_sp;
5218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5219 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5220 FND_MSG_PUB.add_exc_msg (G_PKG_NAME, L_API_NAME);
5221 END IF;
5222 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5223 p_count => x_msg_count,
5224 p_data => x_msg_data);
5225
5226 END Remove_Return_To_Supply;
5227
5228
5229 -------------------------------------------------------------------
5230 -- Start of Comments
5231 -- Procedure name : Validate_Return_To_Supply
5232 -- Type : Public
5233 -- Function : To Validate RETURN_TO_SUPPLY functionality for tasks in a visit.
5234 -- If module_type is 'VST' i.e., if validation for whole visit,
5235 -- this checks if an instance is marked RTS in more than one PLANNING tasks of a visit.
5236 -- Otherwise if this called for stage P2P or task P2P, this checks for all tasks passed in p_tasks_tbl.
5237 -- Because, this is called after the stage is P2Ped
5238 -- we do not perform the check on tasks still in PLANNING status.
5239 -- Pre-reqs :
5240 -- Parameters : p_visit_id IN NUMBER
5241 -- p_tasks_tbl IN AHL_VWP_PROJ_PROD_PVT.Task_Tbl_Type
5242 --
5243 -- End of Comments
5244 -------------------------------------------------------------------
5245
5246
5247 PROCEDURE Validate_Return_To_Supply
5248 (
5249 p_api_version IN NUMBER,
5250 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5251 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5252 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5253 p_module_type IN VARCHAR2,
5254 p_visit_id IN NUMBER,
5255 p_tasks_tbl IN Task_Tbl_Type,
5256 x_return_status OUT NOCOPY VARCHAR2,
5257 x_msg_count OUT NOCOPY NUMBER,
5258 x_msg_data OUT NOCOPY VARCHAR2
5259 )
5260 IS
5261 l_api_version CONSTANT NUMBER := 1.0;
5262 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Return_To_Supply';
5263 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
5264 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
5265
5266 -- Define local cursors
5267 -- Get the instance numbers which are marked RTS in more than one instance in visit
5268 CURSOR get_vst_violating_inst(c_visit_id NUMBER) IS
5269 SELECT CSIS.instance_number
5270 FROM ahl_visit_tasks_b ATSK,
5271 csi_item_instances CSIS
5272 WHERE ATSK.instance_id = CSIS.instance_id
5273 AND ATSK.status_code = 'PLANNING'
5274 AND ATSK.return_to_supply_flag = 'Y'
5275 AND ATSK.visit_id = c_visit_id
5276 GROUP BY CSIS.instance_number
5277 HAVING COUNT(CSIS.instance_number) > 1;
5278
5279 -- Define local variables
5280 l_instance_num NUMBER;
5281 l_task_rec get_vst_violating_inst%ROWTYPE;
5282 l_task_id NUMBER;
5283 taskStr VARCHAR2(500) := '';
5284 l_query_str VARCHAR2(2000);
5285 l_violating_instances Num_Tbl_Type;
5286
5287 BEGIN
5288
5289 IF (l_log_procedure >= l_log_current_level) THEN
5290 FND_LOG.string(FND_LOG.level_procedure, L_DEBUG || '.begin','At the start of PLSQL procedure');
5291 END IF;
5292
5293 -- Initialize message list if p_init_msg_list is set to TRUE.
5294 IF FND_API.to_boolean( p_init_msg_list ) THEN
5295 FND_MSG_PUB.initialize;
5296 END IF;
5297
5298 -- Initialize API return status to success
5299 x_return_status := FND_API.G_RET_STS_SUCCESS;
5300
5301 -- Standard call to check for call compatibility.
5302 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
5303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5304 END IF;
5305
5306 IF (l_log_statement >= l_log_current_level) THEN
5307 FND_LOG.string(l_log_statement, L_DEBUG, 'p_module_type: ' || p_module_type);
5308 END IF;
5309
5310 -- Visit P2P validation, use Visit ID
5311 IF(p_module_type = 'VST') THEN
5312 -- Cursor which returns all instances which are marked for RTS in more than one task
5313 FOR l_task_rec in get_vst_violating_inst(p_visit_id)
5314 LOOP
5315 -- Get the violating instance numbers and write them into the error message list
5316 l_instance_num := l_task_rec.instance_number;
5317 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_VALIDATE_ERR');
5318 FND_MESSAGE.set_token( 'INST_NO', l_instance_num );
5319 FND_MSG_PUB.add;
5320
5321 IF (x_return_status <> FND_API.G_RET_STS_ERROR) THEN
5322 x_return_status := FND_API.G_RET_STS_ERROR;
5323 END IF;
5324
5325 IF (l_log_statement >= l_log_current_level) THEN
5326 FND_LOG.string(l_log_statement, L_DEBUG, 'For Visit id: ' || p_visit_id || ' RTS violating instance number:' || l_instance_num);
5327 END IF;
5328 END LOOP;
5329
5330 -- Stage or Task P2P, use task table
5331 ELSE
5332
5333 --l_task_id := p_tasks_tbl.FIRST.VISIT_TASK_ID;
5334 FOR i in p_tasks_tbl.FIRST..p_tasks_tbl.LAST
5335 LOOP
5336 IF (taskStr IS NULL OR taskStr = '') THEN
5337 taskStr := p_tasks_tbl(i).visit_task_id;
5338 ELSE
5339 taskStr := taskStr || ',' || p_tasks_tbl(i).visit_task_id;
5340 END IF;
5341 END LOOP;
5342
5343 --Validate if at least one task is passed
5344 IF taskStr IS NOT NULL THEN
5345
5346 -- Formulate the query with the tasks obtained
5347 l_query_str := ' SELECT CSIS.instance_number ' ||
5348 ' FROM ahl_visit_tasks_b ATSK, ' ||
5349 ' csi_item_instances CSIS ' ||
5350 ' WHERE ATSK.instance_id = CSIS.instance_id ' ||
5351 ' AND ATSK.return_to_supply_flag = ''Y'' ' ||
5352 ' AND ATSK.status_code = ''PLANNING'' ' ||
5353 ' AND ATSK.visit_task_id IN (' || taskStr || ') ' ||
5354 ' GROUP BY CSIS.instance_number ' ||
5355 ' HAVING COUNT(CSIS.instance_number) > 1 ';
5356
5357
5358 IF (l_log_statement >= l_log_current_level) THEN
5359 fnd_log.string(l_log_statement,L_DEBUG,'l_query_str is :' ||l_query_str);
5360 END IF;
5361
5362 EXECUTE IMMEDIATE l_query_str BULK COLLECT INTO l_violating_instances;
5363
5364 IF l_violating_instances.COUNT > 0 THEN
5365
5366 --Add a error message for every violating instance
5367 FOR i in l_violating_instances.FIRST..l_violating_instances.LAST
5368 LOOP
5369
5370 -- Get the violating instance numbers and write them into the error message list
5371 l_instance_num := l_violating_instances(i);
5372 FND_MESSAGE.set_name('AHL','AHL_VWP_RTS_VALIDATE_ERR');
5373 FND_MESSAGE.set_token( 'INST_NO', l_instance_num );
5374 FND_MSG_PUB.add;
5375
5376 IF (x_return_status <> FND_API.G_RET_STS_ERROR) THEN
5377 x_return_status := FND_API.G_RET_STS_ERROR;
5378 END IF;
5379
5380 IF (l_log_statement >= l_log_current_level) THEN
5381 FND_LOG.string(l_log_statement, L_DEBUG, 'RTS violating instance number:' || l_instance_num);
5382 END IF;
5383
5384 END LOOP;
5385
5386 END IF; -- END IF l_violating_instances.COUNT > 0
5387
5388 END IF; -- END IF taskStr IS NOT NULL
5389
5390 END IF;-- END IF(p_module_type = 'VST')
5391
5392 -- Count and Get messages
5393 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
5394 p_count => x_msg_count,
5395 p_data => x_msg_data);
5396
5397 IF (l_log_procedure >= l_log_current_level) THEN
5398 fnd_log.string(l_log_procedure, L_DEBUG || '.end', 'At the end of PL SQL procedure');
5399 END IF;
5400
5401 END Validate_Return_To_Supply;
5402
5403 -------------------------------------------------------------------
5404 --VWPE: ER:12424063 :: AJPRASAN :: 14-MAR-2011 :: END
5405
5406 --VWPE :: AVIKUKUM :: 05-APR-2011 :: START
5407 -------------------------------------------------------------------
5408 -- Start of Comments
5409 -- Procedure name : Flag_Duplicate_Task
5410 -- Type : Public
5411 -- Function : To set TASK_TYPE_CODE to 'DUPLICATE' for a task after validating..
5412 -- Pre-reqs :
5413 -- Parameters : p_task_rec_tbl IN AHL_VWP_RULES_PVT.Task_Tbl_Type
5414 --
5415 -- End of Comments
5416 -------------------------------------------------------------------
5417
5418 PROCEDURE Flag_Duplicate_Task
5419 (
5420 p_api_version IN NUMBER,
5421 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
5422 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
5423 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
5424 p_module_type IN VARCHAR2 := NULL,
5425 p_task_rec_tbl IN AHL_VWP_RULES_PVT.Task_Tbl_Type,
5426 x_return_status OUT NOCOPY VARCHAR2,
5427 x_msg_count OUT NOCOPY NUMBER,
5428 x_msg_data OUT NOCOPY VARCHAR2 )
5429 IS
5430 L_API_VERSION CONSTANT NUMBER := 1;
5431 L_API_NAME CONSTANT VARCHAR2(30) := 'Flag_Duplicate_Task';
5432 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
5433 l_dummy VARCHAR2(1);
5434 l_count NUMBER;
5435 l_object_version_number NUMBER;
5436 -- cursor to validate the given task is in 'PLANNING' and Task_Type is either 'PLANNED' OR 'UNPLANNED' (not unassociated or summary)
5437 CURSOR validate_task_csr (c_task_id NUMBER)
5438 IS
5439 SELECT 'X'
5440 FROM AHL_VISIT_TASKS_B TSK
5441 WHERE TSK.VISIT_TASK_ID = c_task_id
5442 AND TSK.STATUS_CODE = 'PLANNING'
5443 AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED');
5444 -- SATRAJEN :: Bug 13054370 :: Task which is a dependent task should not be duplicated.
5445 -- cursor to validate the given task is already a dependent task or not.
5446 CURSOR validate_dep_csr (c_task_id NUMBER)
5447 IS
5448 SELECT 'X' FROM AHL_TASK_LINKS
5449 WHERE parent_task_id = c_task_id
5450 OR visit_task_id = c_task_id;
5451 -- End of Bug 13054370
5452 -- cursor to validate if route for the task has its Duplicate Flag set and Return to Supply flag not set.
5453 CURSOR validate_route_csr (c_task_id NUMBER)
5454 IS
5455 SELECT 'X'
5456 FROM AHL_VISIT_TASKS_B TSK,
5457 AHL_ROUTES_B ROUTE,
5458 AHL_MR_ROUTES AMRR
5459 WHERE TSK.VISIT_TASK_ID = c_task_id
5460 AND NVL(TSK.RETURN_TO_SUPPLY_FLAG, 'X') <> 'Y'
5461 AND AMRR.MR_ROUTE_ID = TSK.MR_ROUTE_ID
5462 AND AMRR.ROUTE_ID = ROUTE.ROUTE_ID
5463 AND NVL(ROUTE.DUPLICATE_FLAG, 'X') = 'Y';
5464 -- cursor to validate if there are more than one task (having task type planned and unplanned)
5465 -- for the same route in the visit having task status "PLANNING" or "RELEASED" and operating on same instance.
5466 CURSOR validate_duplicate_task_csr (c_task_id NUMBER)
5467 IS
5468 SELECT 'X'
5469 FROM AHL_VISIT_TASKS_B TSK1,
5470 AHL_ROUTES_B ROUTE,
5471 AHL_MR_ROUTES AMRR1
5472 WHERE TSK1.VISIT_TASK_ID = c_task_id
5473 AND TSK1.STATUS_CODE = 'PLANNING'
5474 AND TSK1.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
5475 AND TSK1.MR_ROUTE_ID = AMRR1.MR_ROUTE_ID
5476 AND AMRR1.ROUTE_ID = ROUTE.ROUTE_ID
5477 AND ROUTE.DUPLICATE_FLAG = 'Y'
5478 AND EXISTS
5479 (SELECT 'X'
5480 FROM AHL_VISIT_TASKS_B TSK2,
5481 AHL_MR_ROUTES AMRR2
5482 WHERE TSK2.VISIT_ID = TSK1.VISIT_ID
5483 AND TSK2.STATUS_CODE IN ('RELEASED', 'PLANNING')
5484 AND TSK2.MR_ROUTE_ID = AMRR2.MR_ROUTE_ID
5485 AND AMRR2.ROUTE_ID = ROUTE.ROUTE_ID
5486 AND TSK2.VISIT_TASK_ID <> TSK1.VISIT_TASK_ID
5487 AND TSK2.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
5488 );
5489
5490 -- cursor to validate if there are other tasks in MR, of which one of the task is being flagged duplicate,
5491 -- in planning or released status belonging to task type planned / unplanned.
5492 -- Here all the tasks under a root MR are considered
5493
5494 CURSOR validate_mr_csr (c_task_id NUMBER)
5495 IS
5496 SELECT 'X'
5497 FROM AHL_VISIT_TASKS_B TSK
5498 WHERE TSK.VISIT_ID IN
5499 (SELECT VISIT_ID FROM AHL_VISIT_TASKS_B
5500 WHERE VISIT_TASK_ID = c_task_id)
5501 AND TSK.VISIT_TASK_ID <> c_task_id
5502 AND TSK.STATUS_CODE IN ('PLANNING', 'RELEASED')
5503 AND TSK.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
5504 START WITH TSK.VISIT_TASK_ID IN
5505 (SELECT TSK1.VISIT_TASK_ID
5506 FROM AHL_VISIT_TASKS_B TSK1
5507 WHERE TSK1.ORIGINATING_TASK_ID IS NULL
5508 START WITH TSK1.VISIT_TASK_ID = c_task_id
5509 CONNECT BY PRIOR TSK1.ORIGINATING_TASK_ID = TSK1.VISIT_TASK_ID
5510 )
5511 CONNECT BY PRIOR TSK.VISIT_TASK_ID = TSK.ORIGINATING_TASK_ID;
5512
5513 -- cursor to fetch OVN for a given task.
5514 CURSOR visit_task_csr (c_task_id NUMBER)
5515 IS
5516 SELECT TSK.OBJECT_VERSION_NUMBER
5517 FROM AHL_VISIT_TASKS_B TSK
5518 WHERE TSK.VISIT_TASK_ID = c_task_id
5519 AND TSK.STATUS_CODE <> 'DELETED';
5520
5521 BEGIN
5522 -- Standard start of API savepoint
5523 SAVEPOINT Flag_Duplicate_Task;
5524 IF (l_log_procedure >= l_log_current_level) THEN
5525 FND_LOG.string(l_log_procedure, L_DEBUG_KEY || '.begin', 'At the start of the PLSQL procedure Flag_Duplicate_Task. p_task_rec.COUNT ->' || p_task_rec_tbl.COUNT);
5526 END IF;
5527 -- Initialize message list if p_init_msg_list is set to TRUE
5528 IF FND_API.To_Boolean(p_init_msg_list) THEN
5529 FND_MSG_PUB.Initialize;
5530 END IF;
5531 -- Initialize API return status to success
5532 x_return_status := FND_API.G_RET_STS_SUCCESS;
5533 -- Standard call to check for call compatibility.
5534 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
5535 IF (l_log_statement >= l_log_current_level) THEN
5536 fnd_log.string(l_log_statement,L_DEBUG_KEY||'UNEXPECTED ERROR', 'l_api_version :' || l_api_version || 'p_api_version :' || p_api_version );
5537 END IF;
5538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;--G_EXC_ERROR
5539 END IF;
5540 --------------------Start of API Body-----------------------------------
5541 FOR i IN p_task_rec_tbl.FIRST .. p_task_rec_tbl.LAST
5542 LOOP
5543 IF (l_log_statement >= l_log_current_level) THEN
5544 fnd_log.string(l_log_statement,L_DEBUG_KEY,'In Loop, Task Id :' || p_task_rec_tbl(i).visit_task_id );
5545 END IF;
5546 OPEN validate_task_csr(p_task_rec_tbl(i).Visit_Task_Id);
5547 FETCH validate_task_csr INTO l_dummy;
5548 IF validate_task_csr%NOTFOUND THEN
5549 CLOSE validate_task_csr;
5550 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVD_TASK_FOR_DUP_FLAG' ); -- //Task can not be flagged duplicate as it is either not in planning or
5551 -- //its task type is not planned or unplanned.
5552 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5553 Fnd_Msg_Pub.ADD;
5554 RAISE FND_API.G_EXC_ERROR;
5555 END IF;
5556 CLOSE validate_task_csr; --Added by tchimira since the cursor must be closed in any case
5557 OPEN validate_route_csr(p_task_rec_tbl(i).Visit_Task_Id);
5558 FETCH validate_route_csr INTO l_dummy;
5559 IF validate_route_csr%NOTFOUND THEN
5560 CLOSE validate_route_csr;
5561 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVD_ROUTE_DUP_FLAG' ); -- // Task can not be flagged duplicate because either duplicate flag of Route for the Task is not set
5562 -- //or its return to supply flag is set.
5563 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5564 Fnd_Msg_Pub.ADD;
5565 RAISE FND_API.G_EXC_ERROR;
5566 END IF;
5567 CLOSE validate_route_csr; --Added by tchimira since the cursor must be closed in any case
5568 OPEN validate_duplicate_task_csr(p_task_rec_tbl(i).Visit_Task_Id);
5569 FETCH validate_duplicate_task_csr INTO l_dummy;
5570 IF validate_duplicate_task_csr%NOTFOUND THEN
5571 CLOSE validate_duplicate_task_csr;
5572 Fnd_Message.SET_NAME('AHL','AHL_VWP_UNQ_ROUTE_FOR_DUP_FLAG'); -- //Task number can not be flagged duplicate because all the tasks created
5573 -- //for a given route and item instance pair cannot be flagged duplicate.
5574 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5575 Fnd_Msg_Pub.ADD;
5576 RAISE FND_API.G_EXC_ERROR;
5577 END IF;
5578 CLOSE validate_duplicate_task_csr; --Added by tchimira since the cursor must be closed in any case
5579 OPEN validate_mr_csr (p_task_rec_tbl(i).Visit_Task_Id);
5580 FETCH validate_mr_csr INTO l_dummy;
5581 IF validate_mr_csr%NOTFOUND THEN
5582 CLOSE validate_mr_csr;
5583 Fnd_Message.SET_NAME('AHL','AHL_VWP_INVD_RT_FOR_DUP_FLAG'); -- //Task cannot be flagged duplicate because all the tasks of a given MR
5584 -- //in a visit cannot be flagged duplicate.
5585 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5586 Fnd_Msg_Pub.ADD;
5587 RAISE FND_API.G_EXC_ERROR;
5588 END IF;
5589 CLOSE validate_mr_csr; --Added by tchimira since the cursor must be closed in any case
5590 -- SATRAJEN :: Bug 13054370 :: Tasks which are dependent should not be marked as duplicate.
5591 OPEN validate_dep_csr (p_task_rec_tbl(i).Visit_Task_Id);
5592 FETCH validate_dep_csr INTO l_dummy;
5593 IF validate_dep_csr%NOTFOUND THEN
5594 CLOSE validate_dep_csr;
5595 ELSE
5596 CLOSE validate_dep_csr;
5597 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEP_TASK_FOR_DUP_FLAG'); -- //Task cannot be flagged duplicate because tasks which are dependent cannot be flagged duplicate.
5598 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5599 Fnd_Msg_Pub.ADD;
5600 RAISE FND_API.G_EXC_ERROR;
5601 END IF;
5602 -- SATRAJEN :: END of Bug 13054370
5603 OPEN visit_task_csr(p_task_rec_tbl(i).Visit_Task_Id);
5604 FETCH visit_task_csr INTO l_object_version_number;
5605 IF visit_task_csr%NOTFOUND THEN
5606 CLOSE visit_task_csr;
5607 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_DELETED'); -- // The task has been deleted.
5608 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5609 Fnd_Msg_Pub.ADD;
5610 RAISE FND_API.G_EXC_ERROR;
5611 END IF;
5612 CLOSE visit_task_csr; --Added by tchimira since the cursor must be closed in any case
5613 IF (l_object_version_number > p_task_rec_tbl(i).object_version_number) THEN
5614 Fnd_Message.SET_NAME('AHL','AHL_TASK_RECORD_CHANGED'); -- // Record has been changed
5615 Fnd_Message.Set_Token('TASK_NO', p_task_rec_tbl(i).Visit_Task_Number);
5616 Fnd_Msg_Pub.ADD;
5617 RAISE FND_API.G_EXC_ERROR;
5618 ELSE
5619 UPDATE AHL_VISIT_TASKS_B
5620 SET TASK_TYPE_CODE = 'DUPLICATE' ,
5621 LAST_UPDATE_DATE = SYSDATE ,
5622 LAST_UPDATED_BY = Fnd_Global.USER_ID ,
5623 OBJECT_VERSION_NUMBER = p_task_rec_tbl(i).object_version_number + 1,
5624 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
5625 WHERE VISIT_TASK_ID = p_task_rec_tbl(i).Visit_Task_Id;
5626 END IF;
5627 END LOOP;
5628 ---------------------------End of Body-------------------------------------
5629 -- Standard check of p_commit.
5630 IF Fnd_Api.To_Boolean ( p_commit ) THEN
5631 COMMIT WORK;
5632 END IF;
5633 IF (l_log_procedure >= l_log_current_level)THEN
5634 fnd_log.string(l_log_procedure,L_DEBUG_KEY ||'.end', 'At the end of PLSQL procedure Flag_Duplicate_Task, x_return_status=' || x_return_status);
5635 END IF;
5636
5637 EXCEPTION
5638 WHEN FND_API.G_EXC_ERROR THEN
5639 x_return_status := FND_API.G_RET_STS_ERROR;
5640 ROLLBACK TO Flag_Duplicate_Task;
5641 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
5642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5644 ROLLBACK TO Flag_Duplicate_Task;
5645 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
5646 WHEN OTHERS THEN
5647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5648 ROLLBACK TO Flag_Duplicate_Task;
5649 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5650 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => L_API_NAME, p_error_text => SUBSTR(SQLERRM,1,500));
5651 END IF;
5652 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
5653 END Flag_Duplicate_Task;
5654
5655
5656 -------------------------------------------------------------------
5657 -- Start of Comments
5658 -- Procedure name : Remove_Duplicate_Task_Flag
5659 -- Type : Public
5660 -- Function : To Change TASK_TYPE_CODE for a task from 'DUPLICATE' to other appropriate value after validating.
5661 -- Pre-reqs :
5662 -- Parameters : p_task_rec_tbl IN AHL_VWP_RULES_PVT.Task_Tbl_Type
5663 --
5664 -- End of Comments
5665 -------------------------------------------------------------------
5666 PROCEDURE Remove_Duplicate_Task_Flag
5667 (
5668 p_api_version IN NUMBER,
5669 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
5670 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
5671 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
5672 p_module_type IN VARCHAR2 := NULL,
5673 p_task_rec_tbl IN AHL_VWP_RULES_PVT.Task_Tbl_Type,
5674 x_return_status OUT NOCOPY VARCHAR2,
5675 x_msg_count OUT NOCOPY NUMBER,
5676 x_msg_data OUT NOCOPY VARCHAR2 )
5677 IS
5678 L_API_VERSION CONSTANT NUMBER := 1;
5679 L_API_NAME CONSTANT VARCHAR2(30) := 'Remove_Duplicate_Task_Flag';
5680 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
5681 l_dummy VARCHAR2(1);
5682 l_object_version_number NUMBER;
5683 l_task_type_code VARCHAR2(30);
5684
5685 -- cursor to validate the given task is in 'PLANNING' and Task_Type is 'DUPLICATE'
5686 CURSOR validate_task_csr (c_task_id NUMBER) IS
5687 SELECT 'X'
5688 FROM AHL_VISIT_TASKS_B TSK
5689 WHERE TSK.VISIT_TASK_ID = c_task_id --cursor param
5690 AND TSK.STATUS_CODE = 'PLANNING'
5691 AND TSK.TASK_TYPE_CODE = 'DUPLICATE';
5692
5693 -- cursor to validate if none of the tasks with in a given task's root MR are released.
5694 CURSOR validate_task_status_csr (c_task_id NUMBER) IS
5695 SELECT 'X'
5696 FROM AHL_VISIT_TASKS_B TSK
5697 WHERE TSK.STATUS_CODE = 'RELEASED'
5698 START WITH TSK.VISIT_TASK_ID = (SELECT ORIGINATING_TASK_ID
5699 FROM AHL_VISIT_TASKS_B
5700 WHERE VISIT_TASK_ID = c_task_id)
5701 CONNECT BY PRIOR TSK.VISIT_TASK_ID = TSK.ORIGINATING_TASK_ID;
5702
5703
5704 CURSOR update_visit_task_csr (c_task_id NUMBER)
5705 IS
5706 SELECT TSK.OBJECT_VERSION_NUMBER,
5707 TSK.SERVICE_REQUEST_ID,
5708 AUEF.MANUALLY_PLANNED_FLAG
5709 FROM AHL_VISIT_TASKS_B TSK,
5710 AHL_UNIT_EFFECTIVITIES_B AUEF
5711 WHERE TSK.VISIT_TASK_ID = c_task_id
5712 AND TSK.UNIT_EFFECTIVITY_ID = AUEF.UNIT_EFFECTIVITY_ID;
5713
5714 update_task_rec update_visit_task_csr%ROWTYPE;
5715
5716
5717 BEGIN
5718 -- Standard start of API savepoint
5719 SAVEPOINT Remove_Duplicate_Task_Flag;
5720 IF (l_log_procedure >= l_log_current_level) THEN
5721 FND_LOG.string(l_log_procedure, L_DEBUG_KEY || '.begin', 'At the start of the PLSQL procedure Remove_Duplicate_Task_Flag. p_task_rec.COUNT ->' || p_task_rec_tbl.COUNT);
5722 END IF;
5723 -- Initialize message list if p_init_msg_list is set to TRUE
5724 IF FND_API.To_Boolean(p_init_msg_list) THEN
5725 FND_MSG_PUB.Initialize;
5726 END IF;
5727 -- Initialize API return status to success
5728 x_return_status := FND_API.G_RET_STS_SUCCESS;
5729 -- Standard call to check for call compatibility.
5730 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
5731 IF (l_log_statement >= l_log_current_level) THEN
5732 fnd_log.string(l_log_statement,L_DEBUG_KEY||'UNEXPECTED ERROR', 'l_api_version :' || l_api_version || 'p_api_version :' || p_api_version );
5733 END IF;
5734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;--G_EXC_ERROR
5735 END IF;
5736
5737 --------------------Start of API Body-----------------------------------
5738 FOR i IN p_task_rec_tbl.FIRST .. p_task_rec_tbl.LAST
5739 LOOP
5740 IF (l_log_statement >= l_log_current_level) THEN
5741 fnd_log.string(l_log_statement,L_DEBUG_KEY,'In Loop, Task Id :' || p_task_rec_tbl(i).visit_task_id );
5742 END IF;
5743 OPEN validate_task_csr(p_task_rec_tbl(i).Visit_Task_Id);
5744 FETCH validate_task_csr INTO l_dummy;
5745 IF validate_task_csr%NOTFOUND THEN
5746 CLOSE validate_task_csr;
5747 Fnd_Message.SET_NAME('AHL','AHL_VWP_TSK_INVD_FOR_REMOV_DUP' ); --// Task is either not in planning or task type is not Duplicate.
5748 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5749 Fnd_Msg_Pub.ADD;
5750 RAISE FND_API.G_EXC_ERROR;
5751 END IF;
5752
5753 OPEN validate_task_status_csr(p_task_rec_tbl(i).Visit_Task_Id);
5754 FETCH validate_task_status_csr INTO l_dummy;
5755 IF validate_task_status_csr%FOUND THEN
5756 CLOSE validate_task_status_csr;
5757 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_INVD_FOR_REMOVE_DUP' ); --// MR for the Task has some tasks in released status.
5758 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5759 Fnd_Msg_Pub.ADD;
5760 RAISE FND_API.G_EXC_ERROR;
5761 END IF;
5762
5763 OPEN update_visit_task_csr(p_task_rec_tbl(i).Visit_Task_Id);
5764 FETCH update_visit_task_csr INTO update_task_rec;
5765 IF update_visit_task_csr%NOTFOUND THEN
5766 CLOSE update_visit_task_csr;
5767 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_HAS_NO_UE_RECORD '); -- // Duplicate flag for Task number can not be removed as it has no
5768 -- // unit effectivity record.
5769 Fnd_Message.Set_Token('TASK_NUM', p_task_rec_tbl(i).Visit_Task_Number);
5770 Fnd_Msg_Pub.ADD;
5771 RAISE FND_API.G_EXC_ERROR;
5772 END IF;
5773 IF ((update_task_rec.MANUALLY_PLANNED_FLAG is NULL) OR (update_task_rec.MANUALLY_PLANNED_FLAG = 'N')) THEN
5774 l_task_type_code := 'PLANNED';
5775 ELSIF ((update_task_rec.MANUALLY_PLANNED_FLAG = 'Y') AND (update_task_rec.SERVICE_REQUEST_ID is not NULL)) THEN
5776 l_task_type_code := 'PLANNED'; --NR Task
5777 ELSIF ((update_task_rec.MANUALLY_PLANNED_FLAG = 'Y') AND (update_task_rec.SERVICE_REQUEST_ID is NULL)) THEN
5778 l_task_type_code := 'UNPLANNED';
5779 END IF;
5780
5781 IF (update_task_rec.object_version_number > p_task_rec_tbl(i).object_version_number) THEN
5782 Fnd_Message.SET_NAME('AHL','AHL_TASK_RECORD_CHANGED'); -- // Record has been changed
5783 Fnd_Message.Set_Token('TASK_NO', p_task_rec_tbl(i).Visit_Task_Number);
5784 Fnd_Msg_Pub.ADD;
5785 RAISE FND_API.G_EXC_ERROR;
5786 ELSE
5787 UPDATE AHL_VISIT_TASKS_B
5788 SET TASK_TYPE_CODE = l_task_type_code ,
5789 LAST_UPDATE_DATE = SYSDATE ,
5790 LAST_UPDATED_BY = Fnd_Global.USER_ID ,
5791 OBJECT_VERSION_NUMBER = p_task_rec_tbl(i).object_version_number + 1,
5792 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
5793 WHERE VISIT_TASK_ID = p_task_rec_tbl(i).Visit_Task_Id;
5794 -- END;
5795 END IF;
5796 END LOOP;
5797 ---------------------------End of Body-------------------------------------
5798
5799 -- Standard check of p_commit.
5800 IF Fnd_Api.To_Boolean ( p_commit ) THEN
5801 COMMIT WORK;
5802 END IF;
5803 IF (l_log_procedure >= l_log_current_level)THEN
5804 fnd_log.string(l_log_procedure,L_DEBUG_KEY ||'.end', 'At the end of PLSQL procedure Remove_Duplicate_Task_Flag, x_return_status=' || x_return_status);
5805 END IF;
5806
5807 EXCEPTION
5808 WHEN FND_API.G_EXC_ERROR THEN
5809 x_return_status := FND_API.G_RET_STS_ERROR;
5810 ROLLBACK TO Remove_Duplicate_Task_Flag;
5811 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
5812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5814 ROLLBACK TO Remove_Duplicate_Task_Flag;
5815 FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
5816 WHEN OTHERS THEN
5817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5818 ROLLBACK TO Remove_Duplicate_Task_Flag;
5819 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5820 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => L_API_NAME, p_error_text => SUBSTR(SQLERRM,1,500));
5821 END IF;
5822 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
5823 END Remove_Duplicate_Task_Flag;
5824
5825 --VWPE :: AVIKUKUM :: 05-APR-2011 :: END
5826
5827 -- Added the below code by tchimira on 14-Mar-2012 for the bug 13694807 - start
5828 -------------------------------------------------------------------
5829 -- Procedure name : Cancel_Planning_tasks
5830 -- Type : Private
5831 -- Function : Procedure to cancel planning tasks corresponding to a given UE
5832 -- Parameters :
5833 --
5834 -- Standard IN Parameters :
5835 -- p_api_version IN NUMBER Required
5836 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5837 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5838 --
5839 -- Standard OUT Parameters :
5840 -- x_return_status OUT VARCHAR2 Required
5841 -- x_msg_count OUT NUMBER Required
5842 -- x_msg_data OUT VARCHAR2 Required
5843 --
5844 -- Cancel_Planning_tasks Parameters:
5845 -- p_ue_id IN NUMBER Required
5846 --------------------------------------------------------------------------------------------------------------------
5847 PROCEDURE Cancel_Planning_tasks (
5848 p_api_version IN NUMBER,
5849 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5850 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5851 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5852 p_ue_id IN NUMBER,
5853 x_return_status OUT NOCOPY VARCHAR2,
5854 x_msg_count OUT NOCOPY NUMBER,
5855 x_msg_data OUT NOCOPY VARCHAR2)IS
5856
5857 -- Local Variables
5858
5859 -- Standard in/out parameters
5860 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_Planning_tasks';
5861 l_api_version CONSTANT NUMBER := 1.0;
5862 l_debug CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
5863 l_msg_count NUMBER;
5864 l_msg_data VARCHAR2(2000);
5865 l_return_status VARCHAR2(1);
5866
5867
5868 --Cursors
5869
5870 Cursor get_summary_task (c_UE_id IN NUMBER)
5871 IS
5872 SELECT visit_task_id, visit_id
5873 FROM ahl_visit_tasks_b
5874 WHERE unit_effectivity_id = c_UE_id
5875 AND status_code IN ( 'PLANNING', 'PARTIALLY RELEASED')
5876 AND task_type_code = 'SUMMARY';
5877 l_summary_task_id NUMBER;
5878 l_visit_id NUMBER;
5879
5880 Cursor is_visit_implemented (c_visit_id IN NUMBER)
5881 IS
5882 SELECT 'N'
5883 FROM ahl_visits_b visit, ahl_visit_tasks_b task
5884 WHERE visit.visit_id = c_visit_id
5885 AND visit.visit_id = task.visit_id
5886 AND task.status_code = 'PLANNING';
5887 l_is_visit_implemented VARCHAR2(1);
5888
5889
5890 BEGIN
5891
5892 IF (l_log_procedure >= l_log_current_level)THEN
5893 fnd_log.string
5894 (
5895 l_log_procedure,
5896 'ahl.plsql.AHL_AVF_PRIM_VSTS_PVT.Cancel_Planning_tasks.begin',
5897 'At the start of PLSQL procedure, ue ID : ' || p_ue_id
5898 );
5899 END IF;
5900
5901 -- Standard start of API savepoint
5902 SAVEPOINT Cancel_Planning_tasks_pvt;
5903
5904 -- Initialize message list if p_init_msg_list is set to TRUE
5905
5906 IF FND_API.To_Boolean( p_init_msg_list) THEN
5907 FND_MSG_PUB.Initialize;
5908 END IF;
5909
5910 -- Initialize API return status to success
5911 x_return_status := FND_API.G_RET_STS_SUCCESS;
5912
5913 -- Standard call to check for call compatibility.
5914 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
5915 p_api_version,
5916 l_api_name,G_PKG_NAME)
5917 THEN
5918 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5919 END IF;
5920
5921 -- make sure that UE id is present in the input and is valid
5922 IF(p_ue_id IS NULL OR p_ue_id = FND_API.G_MISS_NUM) THEN
5923 FND_MESSAGE.Set_Name('AHL','AHL_AVF_SNPSHT_INPUT_MISS'); --tch
5924 FND_MSG_PUB.ADD;
5925
5926 IF (fnd_log.level_exception >= l_log_current_level)THEN
5927 fnd_log.string
5928 (
5929 fnd_log.level_exception,
5930 L_DEBUG,
5931 'UE id is mandatory but found null in input '
5932 );
5933 END IF;
5934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5935
5936 ELSE
5937
5938 OPEN get_summary_task ( p_ue_id );
5939 FETCH get_summary_task INTO l_summary_task_id, l_visit_id;
5940 CLOSE get_summary_task;
5941
5942 IF (l_log_statement >= l_log_current_level) THEN
5943 fnd_log.string(l_log_statement,L_DEBUG,'l_summary_task_id :'||l_summary_task_id);
5944 END IF;
5945 IF l_summary_task_id IS NOT NULL THEN
5946
5947 IF (l_log_statement >= l_log_current_level) THEN
5948 fnd_log.string(l_log_statement,L_DEBUG,'Before call to Delete_Task');
5949 END IF;
5950
5951 AHL_VWP_TASKS_PVT.Delete_Task (
5952 p_api_version => p_api_version,
5953 p_init_msg_list => Fnd_Api.g_false,
5954 p_commit => Fnd_Api.g_false,
5955 p_Visit_Task_Id => l_summary_task_id,
5956 x_return_status => l_return_status,
5957 x_msg_count => l_msg_count,
5958 x_msg_data => l_msg_data );
5959
5960 IF (l_log_statement >= l_log_current_level) THEN
5961 fnd_log.string(l_log_statement,L_DEBUG,'After call to Delete_Task: l_return_status:'||l_return_status||' l_msg_count:'||l_msg_count);
5962 END IF;
5963
5964 IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
5965 x_msg_count := l_msg_count;
5966 x_return_status := l_return_status;
5967 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
5968 RAISE Fnd_Api.g_exc_error;
5969 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
5970 RAISE Fnd_Api.g_exc_unexpected_error;
5971 END IF;
5972 END IF;
5973 END IF; --IF l_summary_task_id IS NOT NULL THEN
5974
5975 OPEN is_visit_implemented (l_visit_id);
5976 FETCH is_visit_implemented INTO l_is_visit_implemented;
5977 IF is_visit_implemented%NOTFOUND THEN
5978 l_is_visit_implemented := 'Y';
5979 END IF;
5980 CLOSE is_visit_implemented;
5981
5982 IF l_is_visit_implemented = 'Y' THEN
5983 UPDATE ahl_visits_b
5984 SET status_code = 'RELEASED',
5985 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
5986 LAST_UPDATE_DATE = SYSDATE,
5987 LAST_UPDATED_BY = Fnd_Global.USER_ID,
5988 LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
5989 WHERE visit_id = l_visit_id;
5990 END IF;
5991 END IF;
5992
5993
5994 ---------------------------End of Body-------------------------------------
5995 -- END of API body.
5996 -- Standard check of p_commit.
5997
5998 IF Fnd_Api.To_Boolean (p_commit) THEN
5999 COMMIT WORK;
6000 END IF;
6001
6002 Fnd_Msg_Pub.count_and_get(
6003 p_encoded => Fnd_Api.g_false,
6004 p_count => x_msg_count,
6005 p_data => x_msg_data
6006 );
6007
6008 IF (l_log_procedure >= l_log_current_level)THEN
6009 fnd_log.string
6010 (
6011 l_log_procedure,
6012 'ahl.plsql.AHL_VWP_PRIM_VSTS_PVT.Cancel_Planning_tasks.end',
6013 'At the end of PLSQL procedure'
6014 );
6015 END IF;
6016
6017 EXCEPTION
6018
6019 WHEN FND_API.G_EXC_ERROR THEN
6020
6021 x_return_status := FND_API.G_RET_STS_ERROR;
6022 ROLLBACK TO Cancel_Planning_tasks_pvt;
6023 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
6024 p_data => x_msg_data,
6025 p_encoded => fnd_api.g_false);
6026
6027 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6029 ROLLBACK TO Cancel_Planning_tasks_pvt;
6030 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
6031 p_data => x_msg_data,
6032 p_encoded => fnd_api.g_false);
6033
6034 WHEN OTHERS THEN
6035 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6036 ROLLBACK TO Cancel_Planning_tasks_pvt;
6037
6038 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6039 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
6040 p_procedure_name => 'Cancel_Planning_tasks',
6041 p_error_text => SUBSTR(SQLERRM,1,500));
6042 END IF;
6043
6044 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
6045 p_data => x_msg_data,
6046 p_encoded => fnd_api.g_false);
6047 END Cancel_Planning_tasks;
6048 -- tchimira on 14-Mar-2012 for the bug 13694807 - end
6049
6050 END AHL_VWP_RULES_PVT;
[Home] [Help]