[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_PLAN_TASKS_PVT
Source
1 PACKAGE BODY AHL_VWP_PLAN_TASKS_PVT AS
2 /* $Header: AHLVPLNB.pls 120.19.12020000.2 2012/12/07 07:54:11 sareepar ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_PLAN_TASKS_PVT
6 --
7 -- PURPOSE
8 -- This package is a Private API for Creating VWP Visit Planned Tasks in
9 -- CMRO. It contains specification for pl/sql records and tables
10 --
11 -- Create_Planned_Task (see below for specification)
12 -- Create_Summary_Child_Tasks (see below for specification)
13 -- Asso_Inst_Dept_to_Tasks (see below for specification)
14 -- Update_Planned_Task (see below for specification)
15 -- Delete_Planned_Task (see below for specification)
16 --
17 -- NOTES
18 --
19 --
20 -- HISTORY
21 -- 12-MAY_2002 Shbhanda Created.
22 -- 21-FEB-2003 YAZHOU Separated from Task package
23 -- 06-AUG-2003 SHBHANDA 11.5.10 Changes.
24
25 -----------------------------------------------------------
26 -- Define Global CONSTANTS -------
27 -----------------------------------------------------------
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_PLAN_TASKS_PVT';
29 -----------------------------------------------------------------
30
31 ------------------------------------------------------------------
32 -- START: Defining local functions and procedures SIGNATURES --
33 --------------------------------------------------------------------
34 -- To Check_Visit_Task_Req_Items
35 PROCEDURE Check_Visit_Task_Req_Items (
36 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
37 x_return_status OUT NOCOPY VARCHAR2
38 );
39
40 -- To Check_Visit_Task_UK_Items
41 PROCEDURE Check_Visit_Task_UK_Items (
42 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
43 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
44 x_return_status OUT NOCOPY VARCHAR2
45 );
46
47 -- To Check_Task_Items
48 PROCEDURE Check_Task_Items (
49 p_Task_rec IN AHL_VWP_RULES_PVT.task_rec_type,
50 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
51 x_return_status OUT NOCOPY VARCHAR2
52 );
53
54 PROCEDURE create_mr_tasks(
55 p_ue_id IN NUMBER,
56 p_parent_ue_id IN NUMBER,
57 p_visit_id IN NUMBER,
58 p_department_id IN NUMBER,
59 p_service_request_id IN NUMBER,
60 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Pass past dates too
61 p_past_task_start_date IN DATE := NULL,
62 p_past_task_end_date IN DATE := NULL,
63 -- Added by rnahata for Issue 105 - pass the qty
64 p_quantity IN NUMBER,
65 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
66 p_task_start_date IN DATE := NULL,
67 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
68 p_service_type_code IN VARCHAR2 := NULL,
69 p_type IN VARCHAR2
70 );
71
72 -- To Validate_Visit_Task
73 /*
74 PROCEDURE Validate_Visit_Task (
75 p_api_version IN NUMBER,
76 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
77 p_commit IN VARCHAR2 := Fnd_Api.g_false,
78 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
79 p_Task_rec IN AHL_VWP_RULES_PVT.task_rec_type,
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2
83 );
84 */
85 -- To assign Null to missing attributes of visit while creation/updation.
86 PROCEDURE Default_Missing_Attribs(
87 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
88 );
89
90 -- To associated Service Request Or Serial Number to Tasks
91 PROCEDURE Asso_Inst_Dept_to_Tasks (
92 p_module_type IN VARCHAR2,
93 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
94 );
95
96 --------------------------------------------------------------------
97 -- END: Defining local functions and procedures SIGNATURES --
98 --------------------------------------------------------------------
99
100 --------------------------------------------------------------------
101 -- START: Defining local functions and procedures BODY --
102 --------------------------------------------------------------------
103 --------------------------------------------------------------------
104 -- PROCEDURE
105 -- Default_Missing_Attribs
106 --
107 -- PURPOSE
108 -- For all optional fields check if its g_miss_num/g_miss_char/
109 -- g_miss_date then Null else the value
110
111 --------------------------------------------------------------------
112 PROCEDURE Default_Missing_Attribs
113 ( p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type)
114 AS
115 BEGIN
116 IF p_x_task_rec.DURATION = Fnd_Api.G_MISS_NUM THEN
117 p_x_task_rec.DURATION := NULL;
118 ELSE
119 p_x_task_rec.DURATION := p_x_task_rec.DURATION;
120 END IF;
121
122 IF p_x_task_rec.PROJECT_TASK_ID = Fnd_Api.G_MISS_NUM THEN
123 p_x_task_rec.PROJECT_TASK_ID := NULL;
124 ELSE
125 p_x_task_rec.PROJECT_TASK_ID := p_x_task_rec.PROJECT_TASK_ID;
126 END IF;
127
128 IF p_x_task_rec.COST_PARENT_ID = Fnd_Api.G_MISS_NUM THEN
129 p_x_task_rec.COST_PARENT_ID := NULL;
130 ELSE
131 p_x_task_rec.COST_PARENT_ID := p_x_task_rec.COST_PARENT_ID;
132 END IF;
133
134 IF p_x_task_rec.MR_ROUTE_ID = Fnd_Api.G_MISS_NUM THEN
135 p_x_task_rec.MR_ROUTE_ID := NULL;
136 ELSE
137 p_x_task_rec.MR_ROUTE_ID := p_x_task_rec.MR_ROUTE_ID;
138 END IF;
139
140 IF p_x_task_rec.MR_ID = Fnd_Api.G_MISS_NUM THEN
141 p_x_task_rec.MR_ID := NULL;
142 ELSE
143 p_x_task_rec.MR_ID := p_x_task_rec.MR_ID;
144 END IF;
145
146 IF p_x_task_rec.UNIT_EFFECTIVITY_ID = Fnd_Api.G_MISS_NUM THEN
147 p_x_task_rec.UNIT_EFFECTIVITY_ID := NULL;
148 ELSE
149 p_x_task_rec.UNIT_EFFECTIVITY_ID := p_x_task_rec.UNIT_EFFECTIVITY_ID;
150 END IF;
151
152 IF p_x_task_rec.START_FROM_HOUR = Fnd_Api.G_MISS_NUM THEN
153 p_x_task_rec.START_FROM_HOUR := NULL;
154 ELSE
155 p_x_task_rec.START_FROM_HOUR := p_x_task_rec.START_FROM_HOUR;
156 END IF;
157
158 IF p_x_task_rec.PRIMARY_VISIT_TASK_ID = Fnd_Api.G_MISS_NUM THEN
159 p_x_task_rec.PRIMARY_VISIT_TASK_ID := NULL;
160 ELSE
161 p_x_task_rec.PRIMARY_VISIT_TASK_ID := p_x_task_rec.PRIMARY_VISIT_TASK_ID;
162 END IF;
163
164 IF p_x_task_rec.ORIGINATING_TASK_ID = Fnd_Api.G_MISS_NUM THEN
165 p_x_task_rec.ORIGINATING_TASK_ID := NULL;
166 ELSE
167 p_x_task_rec.ORIGINATING_TASK_ID := p_x_task_rec.ORIGINATING_TASK_ID;
168 END IF;
169
170 IF p_x_task_rec.SERVICE_REQUEST_ID = Fnd_Api.G_MISS_NUM THEN
171 p_x_task_rec.SERVICE_REQUEST_ID := NULL;
172 ELSE
173 p_x_task_rec.SERVICE_REQUEST_ID := p_x_task_rec.SERVICE_REQUEST_ID;
174 END IF;
175
176 IF p_x_task_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
177 p_x_task_rec.attribute_category := NULL;
178 ELSE
179 p_x_task_rec.attribute_category := p_x_task_rec.attribute_category;
180 END IF;
181 --
182 IF p_x_task_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
183 p_x_task_rec.attribute1 := NULL;
184 ELSE
185 p_x_task_rec.attribute1 := p_x_task_rec.attribute1;
186 END IF;
187 --
188 IF p_x_task_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
189 p_x_task_rec.attribute2 := NULL;
190 ELSE
191 p_x_task_rec.attribute2 := p_x_task_rec.attribute2;
192 END IF;
193 --
194 IF p_x_task_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
195 p_x_task_rec.attribute3 := NULL;
196 ELSE
197 p_x_task_rec.attribute3 := p_x_task_rec.attribute3;
198 END IF;
199 --
200 IF p_x_task_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
201 p_x_task_rec.attribute4 := NULL;
202 ELSE
203 p_x_task_rec.attribute4 := p_x_task_rec.attribute4;
204 END IF;
205 --
206 IF p_x_task_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
207 p_x_task_rec.attribute5 := NULL;
208 ELSE
209 p_x_task_rec.attribute5 := p_x_task_rec.attribute5;
210 END IF;
211 --
212 IF p_x_task_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
213 p_x_task_rec.attribute6 := NULL;
214 ELSE
215 p_x_task_rec.attribute6 := p_x_task_rec.attribute6;
216 END IF;
217 --
218 IF p_x_task_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
219 p_x_task_rec.attribute7 := NULL;
220 ELSE
221 p_x_task_rec.attribute7 := p_x_task_rec.attribute7;
222 END IF;
223 --
224 IF p_x_task_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
225 p_x_task_rec.attribute8 := NULL;
226 ELSE
227 p_x_task_rec.attribute8 := p_x_task_rec.attribute8;
228 END IF;
229 --
230 IF p_x_task_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
231 p_x_task_rec.attribute9 := NULL;
232 ELSE
233 p_x_task_rec.attribute9 := p_x_task_rec.attribute9;
234 END IF;
235 --
236 IF p_x_task_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
237 p_x_task_rec.attribute10 := NULL;
238 ELSE
239 p_x_task_rec.attribute10 := p_x_task_rec.attribute10;
240 END IF;
241 --
242 IF p_x_task_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
243 p_x_task_rec.attribute11 := NULL;
244 ELSE
245 p_x_task_rec.attribute11 := p_x_task_rec.attribute11;
246 END IF;
247 --
248 IF p_x_task_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
249 p_x_task_rec.attribute12 := NULL;
250 ELSE
251 p_x_task_rec.attribute12 := p_x_task_rec.attribute12;
252 END IF;
253 --
254 IF p_x_task_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
255 p_x_task_rec.attribute13 := NULL;
256 ELSE
257 p_x_task_rec.attribute13 := p_x_task_rec.attribute13;
258 END IF;
259 --
260 IF p_x_task_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
261 p_x_task_rec.attribute14 := NULL;
262 ELSE
263 p_x_task_rec.attribute14 := p_x_task_rec.attribute14;
264 END IF;
265 --
266 IF p_x_task_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
267 p_x_task_rec.attribute15 := NULL;
268 ELSE
269 p_x_task_rec.attribute15 := p_x_task_rec.attribute15;
270 END IF;
271 --
272 IF p_x_task_rec.description = Fnd_Api.G_MISS_CHAR THEN
273 p_x_task_rec.description := NULL;
274 ELSE
275 p_x_task_rec.description := p_x_task_rec.description;
276 END IF;
277
278 IF p_x_task_rec.STAGE_NAME = Fnd_Api.G_MISS_CHAR THEN
279 p_x_task_rec.STAGE_NAME := NULL;
280 ELSE
281 p_x_task_rec.STAGE_NAME := p_x_task_rec.STAGE_NAME;
282 END IF;
283
284 END Default_Missing_Attribs;
285
286 --------------------------------------------------------------------
287 -- PROCEDURE
288 -- Asso_Inst_Dept_to_Tasks
289 --
290 --------------------------------------------------------------------
291 PROCEDURE Asso_Inst_Dept_to_Tasks
292 (
293 p_module_type IN VARCHAR2,
294 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.task_rec_type
295 )
296 IS
297 L_API_NAME CONSTANT VARCHAR2(30) := 'Asso_Inst_Dept_to_Tasks';
298 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
299 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
300
301 -- local variables defined for the procedure
302 l_return_status VARCHAR2(1);
303 l_chk_flag VARCHAR2(1);
304 l_msg_data VARCHAR2(2000);
305 l_msg_count NUMBER;
306
307 -- To find visit related information
308 CURSOR c_visit(x_id IN NUMBER) IS
309 SELECT * FROM AHL_VISITS_VL
310 WHERE VISIT_ID = x_id;
311 c_visit_rec c_visit%ROWTYPE;
312
313 BEGIN
314
315 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
316 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PL SQL procedure ');
317 END IF;
318
319 --------------------Value OR ID conversion---------------------------
320 --Start API Body
321 IF p_module_type = 'JSP' THEN
322 p_x_task_Rec.instance_id := NULL;
323 p_x_task_Rec.department_id := NULL;
324 END IF;
325
326 OPEN c_visit(p_x_task_Rec.visit_id);
327 FETCH c_visit INTO c_visit_rec;
328 CLOSE c_visit;
329
330 IF c_visit_rec.organization_id IS NOT NULL THEN
331 -- Get dept code using dept description
332 IF (p_x_task_Rec.dept_name IS NOT NULL AND p_x_task_Rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
333 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
334 (p_organization_id => c_visit_rec.organization_id,
335 p_dept_name => p_x_task_Rec.dept_name,
336 p_department_id => Null,
337 x_department_id => p_x_task_Rec.department_id,
338 x_return_status => l_return_status,
339 x_error_msg_code => l_msg_data);
340
341 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
342 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
343 Fnd_Msg_Pub.ADD;
344 RAISE Fnd_Api.G_EXC_ERROR;
345 END IF;
346
347 -- Changes for Post 11.5.10 by amagrawa
348 AHL_VWP_RULES_PVT.CHECK_DEPARTMENT_SHIFT
349 (P_DEPT_ID => p_x_task_Rec.department_id,
350 X_RETURN_STATUS => l_return_status);
351
352 IF (NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS) THEN
353 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_DEPT_SHIFT');
354 Fnd_Msg_Pub.ADD;
355 RAISE Fnd_Api.G_EXC_ERROR;
356 END IF;
357 ELSE
358 p_x_task_Rec.dept_name := NULL;
359 -- Post 11.5.10 Changes by Senthil.
360 -- Fixed as per bug # 4073163
361 --p_x_task_Rec.department_id := c_visit_rec.department_id;
362 p_x_task_Rec.department_id := NULL;
363 END IF;
364
365 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
366 fnd_log.string(fnd_log.level_statement,L_DEBUG,' Dept ID= ' || p_x_task_Rec.department_id);
367 END IF;
368 ELSE -- Else of if visit org not exists
369 IF (p_x_task_Rec.dept_name IS NOT NULL AND p_x_task_Rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
370 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
371 fnd_log.string(fnd_log.level_statement,L_DEBUG,' NO ORGANIZATION FOR VISIT');
372 END IF;
373 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_NO_ORG_EXISTS');
374 Fnd_Msg_Pub.ADD;
375 RAISE Fnd_Api.G_EXC_ERROR;
376 END IF;
377 END IF; -- End of if visit org exists
378
379 /*Convert service request number to service request id
380 IF (p_x_task_Rec.service_request_number IS NOT NULL AND p_x_task_Rec.service_request_number <> Fnd_Api.G_MISS_CHAR ) THEN
381 AHL_VWP_RULES_PVT.Check_SR_Request_Number_Or_Id
382 (p_service_id => Null,
383 p_service_number => p_x_task_Rec.service_request_number,
384 x_service_id => p_x_task_Rec.service_request_id,
385 x_return_status => l_return_status,
386 x_error_msg_code => l_msg_data);
387
388 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
389 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERVICE_REQ_NOT_EXISTS');
390 Fnd_Msg_Pub.ADD;
391 RAISE Fnd_Api.g_exc_error;
392 END IF;
393
394 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
395 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Service ID= ' || p_x_task_Rec.service_request_id);
396 END IF;
397 ELSE
398 p_x_task_Rec.service_request_id := NULL;
399 p_x_task_Rec.service_request_number := NULL;
400 END IF;
401 */
402
403 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
404 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PL SQL procedure ');
405 END IF;
406
407 END Asso_Inst_Dept_to_Tasks;
408
409 PROCEDURE Create_Planned_Task (
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
412 p_commit IN VARCHAR2 := Fnd_Api.g_false,
413 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
414 p_module_type IN VARCHAR2 := 'JSP',
415 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.task_rec_type,
416 x_return_status OUT NOCOPY VARCHAR2,
417 x_msg_count OUT NOCOPY NUMBER,
418 x_msg_data OUT NOCOPY VARCHAR2
419 )
420 IS
421 L_API_VERSION CONSTANT NUMBER := 1.0;
422 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Planned_Task';
423 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
424 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
425
426 -- local variables defined for the procedure
427 l_msg_data VARCHAR2(2000);
428 l_return_status VARCHAR2(1);
429 l_check_flag VARCHAR2(1);
430
431 l_visit_id NUMBER;
432 l_parent_task_id NUMBER;
433 l_temp_parent_task_id NUMBER;
434 l_service_req_id NUMBER;
435 l_department_id NUMBER;
436 l_unit_effectivity_id NUMBER;
437 l_msg_count NUMBER;
438 l_serial_id NUMBER;
439 l_org_id NUMBER;
440 l_item_id NUMBER;
441 l_MR_route_id NUMBER;
442 l_mr_id NUMBER;
443 l_task_id NUMBER;
444 l_parent_mr_id NUMBER;
445 l_header_id NUMBER;
446 l_unit_id NUMBER;
447 l_parent_unit_id NUMBER;
448 l_visit_number NUMBER;
449 l_object_type VARCHAR2(3);
450 l_count NUMBER;
451 l_workflow_process_id NUMBER;
452 l_incident_id NUMBER;
453
454 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Added two local variables
455 l_past_task_start_date DATE;
456 l_past_task_end_date DATE;
457
458 -- AnRaj: changed for Fixing Siberian Airlines Bug#5007335
459 l_incident_number CS_INCIDENTS_ALL_B.INCIDENT_NUMBER%TYPE;
460 l_object_version_number CS_INCIDENTS_ALL_B.OBJECT_VERSION_NUMBER%TYPE;
461 l_incident_status_id CS_INCIDENTS_ALL_B.INCIDENT_ID%TYPE;
462 -- End Of Fix Bug#5007335
463
464 l_status_name cs_incident_statuses_tl.name%type;
465 l_interaction_id NUMBER;
466 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
467 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
468 l_notes_table CS_ServiceRequest_PUB.notes_table;
469
470 i NUMBER:=0;
471 k NUMBER:=0;
472 x NUMBER:=0;
473 y NUMBER:=0;
474 l_dummy varchar2(1);
475
476 -- To find on the basis of input unit effectivity the related information
477 CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER) IS
478 SELECT AUEB.CSI_ITEM_INSTANCE_ID
479 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
480 WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
481 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
482 AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id AND AUEB.MR_HEADER_ID = x_mr_header_id;
483
484 -- To find all Unit Effectvities i.e main root UEId, if any parent UEIds or
485 -- any child UEIds under it which acts as SUMMARY TASK
486 /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03
487 Modified the query to fetch same result Dec 20 2003 sjayacha */
488 CURSOR c_relation (x_ue_id IN NUMBER) IS
489 SELECT AUR.RELATED_UE_ID "UNIT_ID"
490 FROM AHL_UE_RELATIONSHIPS AUR
491 START WITH AUR.UE_ID IN (SELECT AUEB.unit_effectivity_id
492 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
493 WHERE (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
494 AND AUEB.unit_effectivity_id = x_ue_id
495 )
496 CONNECT BY PRIOR AUR.RELATED_UE_ID = AUR.UE_ID;
497 c_relation_rec c_relation%ROWTYPE;
498
499 -- Added by sjayacha for Servie Request Integration
500 -- To check whether any child UE exists
501 CURSOR c_check_child_ue(p_ue_id IN NUMBER) IS
502 SELECT 'X'
503 FROM ahl_ue_relationships AUR, ahl_unit_effectivities_vl AUEB
504 WHERE AUR.ue_id = AUEB.unit_effectivity_id
505 AND (AUEB.status_code IS NULL OR AUEB.status_code = 'INIT-DUE')
506 AND AUR.ue_id = p_ue_id;
507
508 -- To find MR Header Id for any related Sub Unit Effectivity Id
509 -- or for main Unit Effectivity Id
510 CURSOR c_header (x_unit_id IN NUMBER) IS
511 /*SELECT MR_HEADER_ID
512 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB
513 WHERE (STATUS_CODE IS NULL OR STATUS_CODE IN ('INIT-DUE', 'DEFERRED'))
514 AND UNIT_EFFECTIVITY_ID = x_unit_id;*/
515 /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03*/
516 SELECT AUEB.MR_HEADER_ID
517 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, AHL_MR_HEADERS_B AMHB
518 WHERE AUEB.MR_HEADER_ID = AMHB.MR_HEADER_ID
519 AND AMHB.MR_STATUS_CODE = 'COMPLETE'
520 AND AMHB.VERSION_NUMBER IN
521 ( SELECT MAX(VERSION_NUMBER)
522 FROM AHL_MR_HEADERS_B
523 WHERE TITLE = AMHB.TITLE
524 AND TRUNC(SYSDATE)
525 BETWEEN TRUNC(EFFECTIVE_FROM)
526 AND TRUNC(NVL(EFFECTIVE_TO,SYSDATE+1))
527 AND MR_STATUS_CODE = 'COMPLETE'
528 )
529 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
530 AND AUEB.UNIT_EFFECTIVITY_ID = x_unit_id;
531 c_header_rec c_header%ROWTYPE;
532
533 -- Record type for storing all Maintainence Requirement n Unit Effectivity
534 TYPE MR_Header_Rec_Type IS RECORD
535 (
536 Unit_Effect_ID NUMBER,
537 MR_Header_ID NUMBER
538 );
539
540 -- Table type for storing all Maintainence Requirement n Unit Effectivity
541 TYPE MR_Header_Tbl_Type IS TABLE OF MR_Header_Rec_Type
542 INDEX BY BINARY_INTEGER;
543
544 -- Table type for storing 'MR_Serial_Rec_Type' record datatype
545 MR_Header_Tbl MR_Header_Tbl_Type;
546 MR_Serial_Tbl AHL_VWP_RULES_PVT.MR_Serial_Tbl_Type;
547
548 -- To find visit related information
549 CURSOR c_Visit (p_visit_id IN NUMBER) IS
550 SELECT Any_Task_Chg_Flag, Visit_Id
551 FROM Ahl_Visits_VL
552 WHERE VISIT_ID = p_visit_id;
553 l_visit_csr_rec c_Visit%ROWTYPE;
554
555 -- To find if this Unit has been planned in other visits already
556 CURSOR c_unit (x_unit_id IN NUMBER) IS
557 SELECT VISIT_NUMBER
558 FROM AHL_VISITS_B
559 WHERE VISIT_ID IN ( SELECT DISTINCT VISIT_ID
560 FROM AHL_VISIT_TASKS_B
561 WHERE Unit_Effectivity_Id = x_unit_id
562 )
563 and status_code not in ('CANCELLED','DELETED');
564
565 CURSOR c_unit_object_type(p_unit_id IN NUMBER)
566 IS
567 SELECT OBJECT_TYPE
568 FROM AHL_UNIT_EFFECTIVITIES_VL
569 WHERE UNIT_EFFECTIVITY_ID = p_unit_id;
570
571 -- To find the Item Id, Inv Org Id and Serial Number
572 CURSOR c_item_info(p_unit_id IN NUMBER) IS
573 SELECT AUEB.CSI_ITEM_INSTANCE_ID,
574 AUEB.CS_INCIDENT_ID,
575 CSI.INV_MASTER_ORGANIZATION_ID,
576 CSI.INVENTORY_ITEM_ID
577 FROM AHL_UNIT_EFFECTIVITIES_VL AUEB, CSI_ITEM_INSTANCES CSI
578 WHERE AUEB.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
579 AND (AUEB.STATUS_CODE IS NULL OR AUEB.STATUS_CODE = 'INIT-DUE')
580 AND AUEB.UNIT_EFFECTIVITY_ID = p_unit_id ;
581
582 CURSOR c_service_details(P_service_id IN NUMBER)
583 IS
584 SELECT INCIDENT_ID,
585 INCIDENT_NUMBER,
586 OBJECT_VERSION_NUMBER
587 FROM CS_INCIDENTS_ALL_B
588 WHERE INCIDENT_ID=P_service_id;
589
590 -- AnRaj:Changed cursor for issues mentioned in bug#5007335
591 CURSOR c_service_status
592 IS
593 select incident_status_id,
594 name
595 from cs_incident_statuses_tl
596 -- where name = 'Planned';
597 where incident_status_id = 52
598 and language = userenv('lang');
599
600 /*NR-MR Changes - sowsubra */
601 CURSOR c_task_for_ue(p_visit_id IN NUMBER, p_ue_id IN NUMBER)
602 IS
603 SELECT visit_task_id
604 FROM ahl_visit_tasks_b
605 WHERE visit_id = p_visit_id
606 AND unit_effectivity_id = p_ue_id
607 AND NVL(status_code,'Y') <> 'DELETED';
608
609 /* Cursor added by rnahata for Bug 6939329 */
610 CURSOR c_task_id_for_ue(c_visit_id IN NUMBER, c_ue_id IN NUMBER) IS
611 SELECT visit_task_id
612 FROM ahl_visit_tasks_b
613 WHERE visit_id = c_visit_id
614 AND unit_effectivity_id = c_ue_id
615 AND NVL(status_code, 'PLANNING') <> 'DELETED'
616 AND TASK_TYPE_CODE = 'SUMMARY';
617
618 BEGIN
619 --------------------- initialize -----------------------
620 SAVEPOINT Create_Planned_Task;
621
622 -- Debug info.
623 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
624 fnd_log.string(fnd_log.level_procedure, L_DEBUG||'.begin','At the start of PLSQL procedure');
625 END IF;
626
627 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
628 Fnd_Msg_Pub.initialize;
629 END IF;
630
631 IF NOT Fnd_Api.compatible_api_call (
632 L_API_VERSION,
633 p_api_version,
634 L_API_NAME,
635 G_PKG_NAME
636 ) THEN
637 RAISE Fnd_Api.g_exc_unexpected_error;
638 END IF;
639
640 x_return_status := Fnd_Api.g_ret_sts_success;
641
642 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Populate the values from input record to local variables
643 l_past_task_start_date := p_x_task_Rec.past_task_start_date;
644 l_past_task_end_date := p_x_task_Rec.past_task_end_date;
645
646 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: validate Service Type attributes
647 AHL_VWP_RULES_PVT.Validate_Service_Type(
648 p_service_type => p_x_task_Rec.service_type,
649 p_module_type => p_module_type,
650 p_unit_effectivity_id => p_x_task_Rec.unit_effectivity_id,
651 p_x_service_type_code => p_x_task_Rec.service_type_code
652 );
653
654
655 -- Calling Asso_Inst_Dept_to_Tasks API
656 Asso_Inst_Dept_to_Tasks (
657 p_module_type => p_module_type,
658 p_x_task_Rec => p_x_task_Rec
659 );
660
661 -- Assigning record attributes in local variables
662 l_visit_id := p_x_task_Rec.visit_id;
663 l_service_req_id := p_x_task_Rec.service_request_id;
664 l_department_id := p_x_task_Rec.department_id;
665 l_unit_effectivity_id := p_x_task_Rec.unit_effectivity_id;
666
667 IF l_department_id = FND_API.g_miss_num THEN
668 l_department_id := NULL;
669 END IF;
670
671 -- Cursor to retrieve visit info
672 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
673 fnd_log.string(fnd_log.level_statement, L_DEBUG,'Visit Id: Unit Effe Id ' || l_visit_id || '-' || l_unit_effectivity_id);
674 fnd_log.string(fnd_log.level_statement, L_DEBUG ,'Service Req Id: Department Id:' || l_service_req_id || '-' || l_department_id);
675 END IF;
676
677 IF l_unit_effectivity_id IS NOT NULL THEN
678 OPEN c_unit (l_unit_effectivity_id);
679 FETCH c_unit INTO l_visit_number;
680
681 -- If this UE has already been planned in some other Visit
682 /*NR-MR Changes - sowsubra */
683 /*It is possible to update the SR with more MR's added through backward flow.(Included the
684 condition p_module_type <> 'SR')*/
685 IF c_unit%FOUND AND p_module_type <> 'SR' THEN
686 CLOSE c_unit;
687 -- ERROR MESSAGE
688 x_return_status := Fnd_Api.g_ret_sts_error;
689 Fnd_Message.SET_NAME('AHL','AHL_VWP_UNIT_FOUND');
690 Fnd_Message.SET_TOKEN('VISIT_NUMBER', l_visit_number);
691 Fnd_Msg_Pub.ADD;
692 ELSE -- UE not planned in any other Visit
693 CLOSE c_unit;
694
695 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
696 fnd_log.string(fnd_log.level_statement, L_DEBUG,'Unit effectivity' || l_unit_effectivity_id);
697 END IF;
698
699 -- Get the Object_type code to check whether it is SR or MR.
700 OPEN c_unit_object_type (l_unit_effectivity_id);
701 FETCH c_unit_object_type INTO l_object_type;
702 CLOSE c_unit_object_type;
703
704 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
705 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before l_object_type check' );
706 END IF;
707
708 IF l_object_type = 'MR' THEN
709 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
710 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling create_mr_tasks');
711 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue id =>' || l_unit_effectivity_id);
712 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || 'null');
713 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || l_visit_id);
714 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
715 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service_request id =>' || l_service_req_id);
716 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || p_x_task_Rec.quantity);
717 END IF;
718
719 create_mr_tasks(p_ue_id => l_unit_effectivity_id,
720 p_parent_ue_id => null,
721 p_visit_id => l_visit_id,
722 p_department_id => l_department_id,
723 p_service_request_id => l_service_req_id,
724 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Pass past dates too
725 p_past_task_start_date => l_past_task_start_date,
726 p_past_task_end_date => l_past_task_end_date,
727 -- Added by rnahata for Issue 105 - pass the qty
728 p_quantity => p_x_task_Rec.quantity,
729 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
730 p_service_type_code => p_x_task_Rec.service_type_code,
731 p_type => 'MR'
732 );
733
734 -- if object type is SR
735 ELSIF l_object_type = 'SR' THEN
736 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
737 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_object_type = SR');
738 END IF;
739 -- Get the details of the UE
740 OPEN c_item_info (l_unit_effectivity_id);
741 FETCH c_item_info INTO l_serial_id,l_service_req_id,l_org_id,l_item_id;
742 CLOSE c_item_info;
743
744 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
745 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling AHL_VWP_RULES_PVT.Insert_Tasks');
746 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue_id =>' || l_unit_effectivity_id);
747 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || 'null');
748 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || l_visit_id);
749 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
750 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service request id =>' || l_service_req_id);
751 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || p_x_task_Rec.quantity);
752 END IF;
753
754 OPEN c_task_for_ue(l_visit_id, l_unit_effectivity_id);
755 FETCH c_task_for_ue INTO l_parent_task_id;
756 /*NR-MR Changes - sowsubra*/
757 --Call Insert_Tasks only if summary task for the SR has not already been created.
758 IF c_task_for_ue%NOTFOUND THEN
759 AHL_VWP_RULES_PVT.Insert_Tasks
760 (p_visit_id => l_visit_id,
761 p_unit_id => l_unit_effectivity_id,
762 p_serial_id => l_serial_id,
763 p_service_id => l_service_req_id,
764 p_dept_id => l_department_id,
765 p_item_id => l_item_id,
766 p_item_org_id => l_org_id,
767 p_mr_id => NULL,
768 p_mr_route_id => NULL,
769 /* NR-MR Changes - sowsubra - Make the originating workorder as the originating task of NR Summary task*/
770 p_parent_id => p_x_task_Rec.ORIGINATING_TASK_ID,
771 p_flag => 'Y',
772 -- Added by rnahata for Issue 105 - pass the qty for summary task created for the SR
773 p_quantity => p_x_task_Rec.quantity,
774 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
775 p_task_start_date => p_x_task_Rec.task_start_date,
776 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
777 p_service_type_code => p_x_task_Rec.service_type_code,
778 x_task_id => l_parent_task_id,
779 x_return_status => l_return_status,
780 x_msg_count => l_msg_count,
781 x_msg_data => l_msg_data
782 );
783
784 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
785 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task');
786 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_return_status' || l_return_status);
787 END IF;
788
789 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
790 CLOSE c_task_for_ue; -- NR-MR Changes - sowsubra
791 RAISE Fnd_Api.G_EXC_ERROR;
792 END IF;
793 /*NR-MR Changes - sowsubra*/
794 END IF; --c_task_for_ue%NOTFOUND
795 CLOSE c_task_for_ue;
796
797 -- Check if any valid child UE exist
798 OPEN c_check_child_ue(l_unit_effectivity_id);
799 FETCH c_check_child_ue INTO l_dummy;
800 IF c_check_child_ue%FOUND THEN
801 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
802 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling create_mr_tasks');
803 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'c_check_child_ue%FOUND is TRUE');
804 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue id =>' || l_unit_effectivity_id);
805 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || l_parent_task_id);
806 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || l_visit_id);
807 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
808 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service request id =>' || l_service_req_id);
809 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || p_x_task_Rec.quantity);
810 END IF;
811 -- The New API which would recursively create tasks for all the MRs which are the children of the SR
812 -- the Task id returned by Insert_Tasks is passed as the parent id here
813 create_mr_tasks(p_ue_id => l_unit_effectivity_id,
814 p_parent_ue_id => l_parent_task_id,
815 p_visit_id => l_visit_id,
816 p_department_id => l_department_id,
817 p_service_request_id => l_service_req_id,
818 -- TCHIMIRA:: BUG 9390878 :: 19-FEB-2010 :: pass past task dates for SR
819 p_past_task_start_date => p_x_task_Rec.past_task_start_date,
820 p_past_task_end_date => p_x_task_Rec.past_task_end_date,
821 -- Added by rnahata for Issue 105 - pass the qty for SR
822 p_quantity => p_x_task_Rec.quantity,
823 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
824 p_task_start_date => p_x_task_Rec.task_start_date,
825 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
826 p_service_type_code => p_x_task_Rec.service_type_code,
827 p_type => 'SR'
828 );
829
830 ELSE -- No Child UEs
831 -- Create one Summary Task and a Planned Task
832
833 -- NR-MR Changes - sowsubra
834 -- Done to allow creation of a task for an instance that has already been removed.
835 /***
836 IF AHL_VWP_RULES_PVT.instance_in_config_tree(l_visit_id, l_serial_id) = FND_API.G_RET_STS_ERROR THEN
837 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_SERIAL');
838 Fnd_Msg_Pub.ADD;
839 RAISE Fnd_Api.G_EXC_ERROR;
840 END IF; ***/
841
842 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
843 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Inside No Child UEs ELSE BLOCK');
844 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task');
845 END IF;
846
847 -- Create a Planned Task
848 AHL_VWP_RULES_PVT.Insert_Tasks
849 (p_visit_id => l_visit_id,
850 p_unit_id => l_unit_effectivity_id,
851 p_serial_id => l_serial_id,
852 p_service_id => l_service_req_id,
853 p_dept_id => l_department_id,
854 p_item_id => l_item_id,
855 p_item_org_id => l_org_id,
856 p_mr_id => null,
857 p_mr_route_id => NULL,
858 p_parent_id => l_parent_task_id,
859 p_flag => 'N',
860 -- TCHIMIRA:: BUG 9390878 :: 19-FEB-2010 :: pass past task dates for SR
861 p_past_task_start_date => p_x_task_Rec.past_task_start_date,
862 p_past_task_end_date => p_x_task_Rec.past_task_end_date,
863 /* Added by rnahata for Issue 105 - pass the qty as 0 for
864 the planned task created when there are no MR's associated to the SR*/
865 p_quantity => p_x_task_Rec.quantity,
866 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
867 p_task_start_date => p_x_task_Rec.task_start_date,
868 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
869 p_service_type_code => p_x_task_Rec.service_type_code,
870 x_task_id => l_task_id,
871 x_return_status => l_return_status,
872 x_msg_count => l_msg_count,
873 x_msg_data => l_msg_data
874 );
875
876 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
877 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Insert_Tasks for Planned Task - l_return_status : '|| l_return_status);
878 END IF;
879
880 -- TCHIMIRA::ER 9368251 ::17-JUN-2010::Begin
881 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
882 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
883 'Id of the newly created Task: ' || l_task_id ||
884 'Id of the parent summary Task: ' || l_parent_task_id ||
885 ', p_x_task_Rec.duration: ' || p_x_task_Rec.duration ||
886 ', return_status from AHL_VWP_RULES_PVT.Insert_Tasks: ' || l_return_status );
887 END IF;
888 IF (NVL(l_return_status,'x') = FND_API.G_RET_STS_SUCCESS AND l_task_id IS NOT NULL AND p_x_task_Rec.duration IS NOT NULL) THEN
889 -- Update the duration column for the Planned task as well as its parent summary task with the user entered Estimated Duration
890 UPDATE AHL_VISIT_TASKS_B
891 SET DURATION = p_x_task_Rec.duration
892 WHERE VISIT_TASK_ID IN(l_task_id,l_parent_task_id) ;
893 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
894 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
895 'Updated the duration for the task.');
896 END IF;
897 END IF;
898 -- TCHIMIRA::ER 9368251 ::17-JUN-2010::End
899
900 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
901 RAISE Fnd_Api.G_EXC_ERROR;
902 END IF;
903 END IF; -- Child UE Check
904
905 -- Call Service Request package to update the status.
906 --CS_SERVICEREQUEST_PUB.Update_ServiceRequest
907 OPEN c_service_details(l_service_req_id);
908 FETCH c_service_details into l_incident_id,l_incident_number,l_object_version_number;
909 CLOSE c_service_details;
910
911 OPEN c_service_status;
912 FETCH c_service_status into l_incident_status_id,l_status_name;
913 CLOSE c_service_status;
914
915 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
916
917 -- Assign the SR rec values
918 l_service_request_rec.status_id := l_incident_status_id;
919 --l_service_request_rec.status_name := l_status_name;
920 /*
921 CS_SERVICEREQUEST_PUB.Update_ServiceRequest(
922 p_api_version => 3.0,
923 p_init_msg_list => FND_API.G_TRUE,
924 p_commit => FND_API.G_FALSE,
925 x_return_status => x_return_status,
926 x_msg_count => l_msg_count,
927 x_msg_data => l_msg_data,
928 p_request_id => l_incident_id,
929 --p_request_number => l_incident_number,
930 p_request_number => NUll,
931 p_audit_comments => Null,
932 p_object_version_number => l_object_version_number,
933 p_resp_appl_id => NULL,
934 p_resp_id => NULL,
935 p_last_updated_by => NULL,
936 p_last_update_login => NULL,
937 p_last_update_date => NULL,
938 p_service_request_rec => l_service_request_rec,
939 p_notes => l_notes_table,
940 p_contacts => l_contacts_table,
941 p_called_by_workflow => NULL,
942 p_workflow_process_id => NULL,
943 x_workflow_process_id => l_workflow_process_id,
944 x_interaction_id => l_interaction_id
945 );
946 */
947
948 -- Check Error Message stack.
949 x_msg_count := FND_MSG_PUB.count_msg;
950 IF x_msg_count > 0 THEN
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953
954 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
955 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before CS_ServiceRequest_PUB.Update_Status ');
956 END IF;
957 -- anraj changed the api
958 CS_ServiceRequest_PUB.Update_Status
959 (
960 p_api_version => 2.0,
961 p_init_msg_list => FND_API.G_TRUE,
962 p_commit => FND_API.G_FALSE,
963 p_resp_appl_id => NULL,
964 p_resp_id => NULL,
965 p_user_id => NULL,
966 p_login_id => NULL,
967 p_status_id => 52,
968 p_closed_date => NULL,
969 p_audit_comments => NULL,
970 p_called_by_workflow => FND_API.G_FALSE,
971 p_workflow_process_id => NULL,
972 p_comments => NULL,
973 p_public_comment_flag => FND_API.G_FALSE,
974 p_validate_sr_closure => 'N',
975 p_auto_close_child_entities => 'N',
976 p_request_id => l_incident_id,
977 p_request_number => NULL,
978 x_return_status => x_return_status,
979 x_msg_count => l_msg_count,
980 x_msg_data => l_msg_data,
981 p_object_version_number => l_object_version_number,
982 p_status => NULL,
983 x_interaction_id => l_interaction_id
984 );
985
986 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
987 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After CS_ServiceRequest_PUB.Update_Status - Return Status - '||x_return_status );
988 END IF;
989
990 IF NVL(x_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
991 RAISE Fnd_Api.G_EXC_ERROR;
992 ELSE
993 Fnd_Msg_Pub.initialize;
994 END IF;
995 END IF; -- SR/MR
996 /* Added by rnahata for Bug 6939329 */
997 OPEN c_task_id_for_ue(l_visit_id, l_unit_effectivity_id);
998 FETCH c_task_id_for_ue INTO p_x_task_Rec.visit_task_id;
999 CLOSE c_task_id_for_ue;
1000
1001 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1002 fnd_log.string(fnd_log.level_statement, L_DEBUG, 'p_x_task_Rec.visit_task_id = ' || p_x_task_Rec.visit_task_id);
1003 END IF;
1004 /* End changes by rnahata for Bug 6939329 */
1005 END IF; -- c_unit%FOUND
1006 ELSE -- l_unit_effectivity_id
1007 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_UNIT_EFFECTIVITY');
1008 Fnd_Msg_Pub.ADD;
1009 RAISE FND_API.G_EXC_ERROR;
1010 END IF; -- End of unit effectivity check
1011
1012 OPEN C_VISIT(l_visit_id);
1013 fetch c_visit into l_visit_csr_rec;
1014 IF C_VISIT%FOUND THEN
1015 IF l_visit_csr_rec.Any_Task_Chg_Flag='N' THEN
1016 AHL_VWP_RULES_PVT.update_visit_task_flag(
1017 p_visit_id =>l_visit_csr_rec.visit_id,
1018 p_flag =>'Y',
1019 x_return_status =>x_return_status);
1020 END IF;
1021
1022 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1023 CLOSE C_VISIT;
1024 RAISE FND_API.G_EXC_ERROR;
1025 END IF;
1026 END IF;
1027 CLOSE C_VISIT;
1028
1029 ------------------------- finish -------------------------------
1030 -- Standard call to get message count and if count is 1, get message info
1031 Fnd_Msg_Pub.count_and_get
1032 (
1033 p_encoded => Fnd_Api.g_false,
1034 p_count => x_msg_count,
1035 p_data => x_msg_data
1036 );
1037
1038 -- Check Error Message stack.
1039 x_msg_count := FND_MSG_PUB.count_msg;
1040 IF x_msg_count > 0 THEN
1041 RAISE FND_API.G_EXC_ERROR;
1042 END IF;
1043
1044 -- Standard check of p_commit.
1045 IF Fnd_Api.To_Boolean ( p_commit ) THEN
1046 COMMIT WORK;
1047 END IF;
1048
1049 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1050 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
1051 END IF;
1052
1053 EXCEPTION
1054 WHEN Fnd_Api.g_exc_error THEN
1055 ROLLBACK TO Create_Planned_Task;
1056 x_return_status := Fnd_Api.g_ret_sts_error;
1057 Fnd_Msg_Pub.count_and_get(
1058 p_encoded => Fnd_Api.g_false,
1059 p_count => x_msg_count,
1060 p_data => x_msg_data
1061 );
1062 WHEN Fnd_Api.g_exc_unexpected_error THEN
1063 ROLLBACK TO Create_Planned_Task;
1064 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1065 Fnd_Msg_Pub.count_and_get (
1066 p_encoded => Fnd_Api.g_false,
1067 p_count => x_msg_count,
1068 p_data => x_msg_data
1069 );
1070 WHEN OTHERS THEN
1071 ROLLBACK TO Create_Planned_Task;
1072 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1073 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1074 THEN
1075 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1076 END IF;
1077 Fnd_Msg_Pub.count_and_get (
1078 p_encoded => Fnd_Api.g_false,
1079 p_count => x_msg_count,
1080 p_data => x_msg_data
1081 );
1082 END Create_Planned_Task;
1083
1084 -------------------------------------------------------------------------
1085 -- PROCEDURE
1086 -- create_mr_tasks
1087 -- AnRaj: Created
1088 -- PURPOSE
1089 -- Seperates the Task creating functionality from Create_Planned_Task
1090 -------------------------------------------------------------------------
1091 PROCEDURE create_mr_tasks(p_ue_id IN NUMBER,
1092 p_parent_ue_id IN NUMBER,
1093 p_visit_id IN NUMBER,
1094 p_department_id IN NUMBER,
1095 p_service_request_id IN NUMBER,
1096 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010
1097 p_past_task_start_date IN DATE := NULL,
1098 p_past_task_end_date IN DATE := NULL,
1099 -- Added by rnahata for Issue 105
1100 p_quantity IN NUMBER,
1101 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
1102 p_task_start_date IN DATE := NULL,
1103 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
1104 p_service_type_code IN VARCHAR2 := NULL,
1105 p_type IN VARCHAR2
1106 )
1107 IS
1108 CURSOR c_header (x_unit_id IN NUMBER) IS
1109 SELECT aueb.mr_header_id
1110 FROM ahl_unit_effectivities_vl aueb, ahl_mr_headers_b amhb
1111 WHERE aueb.mr_header_id = amhb.mr_header_id
1112 AND amhb.mr_status_code = 'COMPLETE'
1113 AND amhb.version_number IN
1114 (SELECT MAX(version_number)
1115 FROM ahl_mr_headers_b
1116 WHERE title = amhb.title
1117 AND TRUNC(SYSDATE)
1118 BETWEEN TRUNC(effective_from)
1119 AND TRUNC(NVL(effective_to,SYSDATE+1))
1120 AND mr_status_code = 'COMPLETE'
1121 )
1122 AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
1123 AND aueb.unit_effectivity_id = x_unit_id;
1124 c_header_rec c_header%ROWTYPE;
1125 --SKPATHAK :: Bug 8344789 :: 19-MAY-2009
1126 --Validate status and version of the MR associated to the SR
1127 CURSOR c_validate_mr (x_unit_id IN NUMBER) IS
1128 SELECT 'X'
1129 FROM ahl_unit_effectivities_vl aueb, ahl_mr_headers_b amhb
1130 WHERE aueb.mr_header_id = amhb.mr_header_id
1131 AND aueb.unit_effectivity_id = x_unit_id
1132 AND (amhb.version_number NOT IN
1133 ( SELECT MAX(version_number)
1134 FROM ahl_mr_headers_b
1135 WHERE title = amhb.title
1136 AND TRUNC(SYSDATE)
1137 BETWEEN TRUNC(effective_from)
1138 AND TRUNC(NVL(effective_to,SYSDATE+1))
1139 AND mr_status_code = 'COMPLETE'
1140 )
1141 OR (aueb.status_code = 'MR-TERMINATE'));
1142
1143
1144 -- To find on the basis of input unit effectivity the related information
1145 CURSOR c_info(x_mr_header_id IN NUMBER, x_unit_id IN NUMBER) IS
1146 SELECT aueb.csi_item_instance_id
1147 FROM ahl_unit_effectivities_vl aueb, csi_item_instances csi
1148 WHERE aueb.csi_item_instance_id = csi.instance_id
1149 AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE')
1150 AND aueb.unit_effectivity_id = x_unit_id
1151 AND aueb.mr_header_id = x_mr_header_id;
1152
1153 CURSOR c_relation (x_ue_id IN NUMBER) IS
1154 SELECT aur.related_ue_id
1155 FROM ahl_ue_relationships aur,
1156 ahl_unit_effectivities_vl aueb
1157 WHERE aur.ue_id = x_ue_id
1158 AND aur.ue_id = aueb.unit_effectivity_id
1159 AND (aueb.status_code IS NULL OR aueb.status_code = 'INIT-DUE');
1160 c_relation_rec c_relation%ROWTYPE;
1161
1162 /*NR-MR Changes - sowsubra*/
1163 CURSOR c_task_for_ue(p_visit_id IN NUMBER, p_ue_id IN NUMBER) IS
1164 SELECT visit_task_id
1165 FROM ahl_visit_tasks_b
1166 WHERE visit_id = p_visit_id
1167 AND unit_effectivity_id = p_ue_id
1168 AND NVL(status_code,'Y') <> 'DELETED';
1169 c_task_for_ue_rec c_task_for_ue%ROWTYPE;
1170
1171 -- Begin changes by rnahata for Issue 105
1172 --Cursor to fetch the instance id when effectivity is given
1173 CURSOR c_get_prev_instance_id (p_unit_effectivity IN NUMBER) IS
1174 SELECT csi_item_instance_id FROM AHL_UNIT_EFFECTIVITIES_B
1175 WHERE UNIT_EFFECTIVITY_ID = p_unit_effectivity;
1176
1177 --Cursor to fetch instance quantity
1178 CURSOR c_get_instance_qty(p_unit_effectivity IN NUMBER) IS
1179 SELECT csii.quantity, ue.csi_item_instance_id
1180 FROM csi_item_instances csii, ahl_unit_effectivities_b ue
1181 WHERE ue.unit_effectivity_id = p_unit_effectivity
1182 AND csii.instance_id = ue.csi_item_instance_id;
1183
1184 -- SKPATHAK :: Bug 9971127 :: 13-AUG-2010
1185 -- Added this cursor to validate if any of the child MR for a group MR are in TERMINATED status
1186 CURSOR c_validate_child_mrs (c_mr_header_id IN NUMBER) IS
1187 SELECT 'X'
1188 FROM AHL_MR_HEADERS_B AMHB, AHL_MR_RELATIONSHIPS_APP_V REL
1189 WHERE REL.MR_HEADER_ID = c_mr_header_id
1190 AND REL.RELATED_MR_HEADER_ID = AMHB.MR_HEADER_ID
1191 AND AMHB.MR_STATUS_CODE = 'TERMINATED'
1192 AND AMHB.VERSION_NUMBER IN
1193 ( SELECT MAX(VERSION_NUMBER)
1194 FROM AHL_MR_HEADERS_B
1195 WHERE TITLE = AMHB.TITLE
1196 AND (MR_STATUS_CODE = 'COMPLETE' OR MR_STATUS_CODE = 'TERMINATED')
1197 );
1198 l_dummy1 c_validate_child_mrs%ROWTYPE;
1199
1200
1201 l_instance_qty NUMBER := 0;
1202 l_instance_id NUMBER := 0;
1203 l_prev_instance_id NUMBER := 0;
1204 -- End changes by rnahata for Issue 105
1205
1206 l_mr_header_id NUMBER;
1207 l_unit_eff_id NUMBER;
1208 l_parent_unit_eff_id NUMBER;
1209 l_serial_id NUMBER;
1210 l_parent_MR_Id NUMBER;
1211 l_department_id NUMBER;
1212 l_return_status VARCHAR2(1);
1213 l_service_request_id NUMBER;
1214 l_dummy VARCHAR2(1);
1215
1216
1217 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || 'CREATE_MR_TASKS';
1218 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1219 MR_Serial_Tbl AHL_VWP_RULES_PVT.MR_Serial_Tbl_Type;
1220
1221 BEGIN
1222 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1223 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin', 'At the start of the procedure..');
1224 fnd_log.string(fnd_log.level_procedure,L_DEBUG, 'p_ue_id' || p_ue_id);
1225 END IF;
1226
1227 l_unit_eff_id := p_ue_id;
1228 l_service_request_id := p_service_request_id;
1229 l_department_id := p_department_id;
1230 l_parent_MR_Id := p_parent_ue_id;
1231
1232 IF p_type = 'MR' THEN
1233 --SKPATHAK :: Bug 8344789 :: 19-MAY-2009
1234 OPEN c_validate_mr (p_ue_id);
1235 FETCH c_validate_mr INTO l_dummy;
1236 IF c_validate_mr%FOUND THEN
1237 CLOSE c_validate_mr;
1238 Fnd_Message.SET_NAME('AHL','AHL_VWP_NR_MR_EXPIRED');
1239 Fnd_Msg_Pub.ADD;
1240 RAISE Fnd_Api.G_EXC_ERROR;
1241 END IF;
1242 CLOSE c_validate_mr;
1243
1244
1245 -- Get the MR Header for the UE
1246 OPEN c_header (p_ue_id);
1247 FETCH c_header INTO c_header_rec;
1248 IF c_header%FOUND THEN
1249 CLOSE c_header;
1250 l_mr_header_id := c_header_rec.MR_Header_Id;
1251
1252 -- SKPATHAK :: Bug 9971127 :: 13-AUG-2010
1253 -- Validate if any of the child MR for a group MR are in TERMINATED status, if yes, throw appropriate error
1254 OPEN c_validate_child_mrs (l_mr_header_id);
1255 FETCH c_validate_child_mrs INTO l_dummy1;
1256 IF c_validate_child_mrs%FOUND THEN
1257 CLOSE c_validate_child_mrs;
1258 Fnd_Message.SET_NAME('AHL','AHL_VWP_NR_MR_EXPIRED');
1259 Fnd_Msg_Pub.ADD;
1260 RAISE Fnd_Api.G_EXC_ERROR;
1261 END IF;
1262 CLOSE c_validate_child_mrs;
1263
1264 OPEN c_info (l_mr_header_id, l_unit_eff_id);
1265 FETCH c_info INTO l_serial_id;
1266 CLOSE c_info;
1267
1268 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1269 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_mr_header_id =>' || l_mr_header_id);
1270 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_unit_eff_id =>' ||l_unit_eff_id);
1271 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'l_serial_id =>' || l_serial_id);
1272 END IF;
1273
1274 -- NR-MR Changes - sowsubra
1275 -- Done to allow creation of a task for an instance that has already been removed.
1276 /***
1277 IF AHL_VWP_RULES_PVT.instance_in_config_tree(p_visit_id, l_serial_id) = FND_API.G_RET_STS_ERROR THEN
1278 Fnd_Message.SET_NAME('AHL','AHL_VWP_TASK_SERIAL');
1279 Fnd_Msg_Pub.ADD;
1280 RAISE Fnd_Api.G_EXC_ERROR;
1281 END IF;
1282 ***/
1283
1284 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1285 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Calling ahl_vwp_rules_pvt.create_tasks_for_mr');
1286 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_visit_id =>' || p_visit_id);
1287 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_serial_id =>' || l_serial_id);
1288 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_mr_id =>' || l_mr_header_id);
1289 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_department_id =>' || l_department_id);
1290 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_service_req_id =>' || l_service_request_id);
1291 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_x_parent_MR_Id =>' || l_parent_MR_Id);
1292 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_service_req_id =>' || l_service_request_id);
1293 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_quantity =>' || p_quantity);
1294 END IF;
1295
1296 ahl_vwp_rules_pvt.create_tasks_for_mr(p_visit_id => p_visit_id,
1297 p_unit_id => l_unit_eff_id,
1298 p_item_id => NULL,
1299 p_org_id => NULL,
1300 p_serial_id => l_serial_id,
1301 p_mr_id => l_mr_header_id,
1302 p_department_id => l_department_id,
1303 p_service_req_id => l_service_request_id,
1304 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Pass past dates too
1305 p_past_task_start_date => p_past_task_start_date,
1306 p_past_task_end_date => p_past_task_end_date,
1307 -- Added by rnahata for Issue 105
1308 p_quantity => p_quantity,
1309 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
1310 p_task_start_date => p_task_start_date,
1311 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
1312 p_service_type_code => p_service_type_code,
1313 p_x_parent_MR_Id => l_parent_MR_Id,
1314 x_return_status => l_return_status
1315 );
1316
1317 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1318 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After ahl_vwp_rules_pvt.create_tasks_for_mr');
1319 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'p_x_parent_MR_Id = ' || l_parent_MR_Id );
1320 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'x_return_status is ' || l_return_status );
1321 END IF;
1322
1323 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1324 RAISE Fnd_Api.G_EXC_ERROR;
1325 END IF;
1326
1327 MR_Serial_Tbl(0).MR_ID := l_mr_header_id ;
1328 MR_Serial_Tbl(0).Serial_ID := l_serial_id;
1329 --SKPATHAK : BUG 13997419 :: 26-APR-2012 :: pass the ue id as well
1330 MR_Serial_Tbl(0).UE_ID := l_unit_eff_id;
1331 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1332 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Calling AHL_VWP_RULES_PVT.Tech_Dependency');
1333 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'MR_Serial_Tbl(0).MR_ID -->'|| MR_Serial_Tbl(0).MR_ID);
1334 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'MR_Serial_Tbl(0).Serial_ID'|| MR_Serial_Tbl(0).Serial_ID);
1335 END IF;
1336
1337 AHL_VWP_RULES_PVT.Tech_Dependency
1338 (p_visit_id => p_visit_id,
1339 p_task_type => 'PLANNED',
1340 p_MR_Serial_Tbl => MR_Serial_Tbl,
1341 x_return_status => l_return_status
1342 );
1343
1344 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1345 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'After AHL_VWP_RULES_PVT.Tech_Dependency - l_return_status : '||l_return_status);
1346 END IF;
1347
1348 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1349 RAISE Fnd_Api.G_EXC_ERROR;
1350 END IF;
1351 ELSE
1352 CLOSE c_header;
1353 END IF;
1354 END IF;
1355
1356 -- Begin changes by rnahata for Issue 105
1357 -- This part of the code has to be done for both SR as well as MR
1358 --fetches the instance id for the previous MR
1359 OPEN c_get_prev_instance_id (l_unit_eff_id);
1360 FETCH c_get_prev_instance_id INTO l_prev_instance_id;
1361 CLOSE c_get_prev_instance_id;
1362 -- End changes by rnahata for Issue 105
1363
1364 OPEN c_relation (l_unit_eff_id);
1365 LOOP
1366 FETCH c_relation INTO c_relation_rec;
1367 EXIT WHEN c_relation%NOTFOUND;
1368 /*NR-MR Changes - sowsubra*/
1369 --Call create_mr_tasks only if tasks for the MR have not already been created.
1370 OPEN c_task_for_ue(p_visit_id,c_relation_rec.related_ue_id);
1371 FETCH c_task_for_ue INTO c_task_for_ue_rec;
1372 IF c_task_for_ue%NOTFOUND THEN
1373 -- Begin changes by rnahata for Issue 105
1374 -- get the instance qty for the child MR's
1375 OPEN c_get_instance_qty (c_relation_rec.related_ue_id);
1376 FETCH c_get_instance_qty INTO l_instance_qty,l_instance_id;
1377 CLOSE c_get_instance_qty;
1378
1379 IF (l_instance_id = l_prev_instance_id) THEN
1380 l_instance_qty := p_quantity;
1381 END IF;
1382 -- End changes by rnahata for Issue 105
1383
1384 -- Call create_mr_tasks recursively for the next level of UEs
1385 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1386 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Before calling create_mr_tasks');
1387 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'ue id =>' || c_relation_rec.related_ue_id);
1388 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'parent ue id =>' || l_parent_MR_Id);
1389 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'visit id =>' || p_visit_id);
1390 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'department id =>' || l_department_id);
1391 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'service request id =>' || l_service_request_id);
1392 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'quantity =>' || l_instance_qty);
1393 END IF;
1394
1395 create_mr_tasks(p_ue_id => c_relation_rec.related_ue_id,
1396 p_parent_ue_id => l_parent_MR_Id,
1397 p_visit_id => p_visit_id,
1398 p_department_id => l_department_id,
1399 p_service_request_id => l_service_request_id,
1400 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Pass past dates too
1401 p_past_task_start_date => p_past_task_start_date,
1402 p_past_task_end_date => p_past_task_end_date,
1403 -- Added by rnahata for Issue 105
1404 p_quantity => l_instance_qty,
1405 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
1406 p_task_start_date => p_task_start_date,
1407 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: Service Type code added
1408 p_service_type_code => p_service_type_code,
1409 p_type => 'MR'
1410 );
1411 /*NR-MR Changes - sowsubra*/
1412 END IF; --c_task_for_ue%NOTFOUND
1413 CLOSE c_task_for_ue;
1414 END LOOP;
1415 CLOSE c_relation;
1416
1417 END create_mr_tasks;
1418
1419 --------------------------------------------------------------------
1420 -- PROCEDURE
1421 -- Update_Planned_Task
1422 --
1423 -- PURPOSE
1424 -- To update Planned task for the Maintainance visit.
1425 --------------------------------------------------------------------
1426 PROCEDURE Update_Planned_Task (
1427 p_api_version IN NUMBER,
1428 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1429 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1430 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1431 p_module_type IN VARCHAR2 := 'JSP',
1432 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
1433 x_return_status OUT NOCOPY VARCHAR2,
1434 x_msg_count OUT NOCOPY NUMBER,
1435 x_msg_data OUT NOCOPY VARCHAR2
1436 )
1437 IS
1438 L_API_VERSION CONSTANT NUMBER := 1.0;
1439 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Planned_Task';
1440 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1441 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1442
1443 -- local variables defined for the procedure
1444 l_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type := p_x_task_rec;
1445 l_return_status VARCHAR2(1);
1446 l_msg_data VARCHAR2(2000);
1447 l_planned_order_flag VARCHAR2(1);
1448 l_msg_count NUMBER;
1449 l_cost_parent_id NUMBER;
1450 l_department_id NUMBER;
1451 l_entl_rec_tbl AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type; --salogan added for supplier warranty
1452
1453 -- To find visit related information
1454 CURSOR c_Visit (x_id IN NUMBER) IS
1455 SELECT * FROM Ahl_Visits_VL
1456 WHERE VISIT_ID = x_id;
1457 c_Visit_rec c_Visit%ROWTYPE;
1458
1459 -- To find task related information
1460 CURSOR c_Task (x_id IN NUMBER) IS
1461 SELECT * FROM Ahl_Visit_Tasks_VL
1462 WHERE VISIT_TASK_ID = x_id;
1463 c_Task_rec c_Task%ROWTYPE;
1464 c_upd_Task_rec c_Task%ROWTYPE;
1465
1466 BEGIN
1467 --------------------- initialize -----------------------
1468 SAVEPOINT Update_Planned_Task;
1469
1470 -- Debug info.
1471 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1472 fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
1473 END IF;
1474
1475 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1476 Fnd_Msg_Pub.initialize;
1477 END IF;
1478
1479 -- Initialize API return status to success
1480 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1481
1482 -- Standard call to check for call compatibility.
1483 IF NOT Fnd_Api.compatible_api_call(
1484 l_api_version,
1485 p_api_version,
1486 l_api_name,
1487 G_PKG_NAME
1488 ) THEN
1489 RAISE Fnd_Api.g_exc_unexpected_error;
1490 END IF;
1491
1492 ------------------------Start of API Body------------------------------------
1493
1494 OPEN c_Visit(l_Task_rec.visit_id);
1495 FETCH c_Visit INTO c_Visit_rec;
1496 CLOSE c_Visit;
1497
1498 OPEN c_Task(l_Task_rec.visit_task_id);
1499 FETCH c_Task INTO c_Task_rec;
1500 CLOSE c_Task;
1501
1502 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1503 fnd_log.string(fnd_log.level_statement,L_DEBUG, 'Visit Id/Task Id = ' || l_task_rec.visit_id || '-' || l_task_rec.visit_task_id);
1504 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Inventory Id /org/name =' || l_task_rec.inventory_item_id || '-' || l_task_rec.item_organization_id || '-' || l_task_rec.item_name);
1505 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost Id -- Number=' || l_task_rec.cost_parent_id || '**' || l_task_rec.cost_parent_number );
1506 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Originating Id/Number=' || l_task_rec.originating_task_id || '**' || l_task_rec.orginating_task_number);
1507 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Object version number = ' || l_task_rec.object_version_number);
1508 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Duration from record = ' || l_task_rec.duration);
1509 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit start from hour/duration=' || '-' || l_task_rec.start_from_hour || '-' || l_task_rec.duration);
1510 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Task Type code/value=' || l_task_rec.task_type_code || '-' || l_task_rec.task_type_value );
1511 fnd_log.string(fnd_log.level_statement,L_DEBUG,'department_id = ' || l_task_rec.department_id );
1512 END IF;
1513
1514 ----------- Start defining and validate all LOVs on Create Visit's Task UI Screen---
1515 --
1516 -- For DEPARTMENT
1517 -- Convert department name to department id
1518 IF (l_task_rec.dept_name IS NOT NULL AND l_task_rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
1519
1520 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
1521 (p_organization_id => c_visit_rec.organization_id,
1522 p_dept_name => l_task_rec.dept_name,
1523 p_department_id => NULL,
1524 x_department_id => l_department_id,
1525 x_return_status => l_return_status,
1526 x_error_msg_code => l_msg_data);
1527
1528 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
1529 THEN
1530 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
1531 Fnd_Msg_Pub.ADD;
1532 RAISE Fnd_Api.G_EXC_ERROR;
1533 END IF;
1534
1535 --Assign the returned value
1536 l_task_rec.department_id := l_department_id;
1537 END IF;
1538
1539 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1540 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Dept ID= ' || l_Task_rec.department_id );
1541 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost parent= ' || l_Task_rec.cost_parent_number);
1542 END IF;
1543
1544 --
1545 -- For COST PARENT TASK
1546 -- Convert cost parent number to id
1547 IF (l_Task_rec.cost_parent_number IS NOT NULL AND
1548 l_Task_rec.cost_parent_number <> Fnd_Api.G_MISS_NUM ) THEN
1549
1550 AHL_VWP_RULES_PVT.Check_Visit_Task_Number_OR_ID
1551 (p_visit_task_id => l_Task_rec.cost_parent_id,
1552 p_visit_task_number => l_Task_rec.cost_parent_number,
1553 p_visit_id => l_Task_rec.visit_id,
1554 x_visit_task_id => l_cost_parent_id,
1555 x_return_status => l_return_status,
1556 x_error_msg_code => l_msg_data);
1557
1558 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
1559 THEN
1560 Fnd_Message.SET_NAME('AHL','AHL_VWP_PARENT_NOT_EXISTS');
1561 Fnd_Msg_Pub.ADD;
1562 RAISE Fnd_Api.g_exc_error;
1563 END IF;
1564
1565 --Assign the returned value
1566 l_Task_rec.cost_parent_id := l_cost_parent_id;
1567 END IF;
1568
1569 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1570 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Cost parent ID = ' || l_Task_rec.cost_parent_id);
1571 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validation: Start -- For COST PARENT ');
1572 END IF;
1573
1574 -- To Check for cost parent task id not forming loop
1575 IF (l_Task_rec.cost_parent_id IS NOT NULL AND
1576 l_Task_rec.cost_parent_id <> Fnd_Api.G_MISS_NUM ) THEN
1577
1578 AHL_VWP_RULES_PVT.Check_Cost_Parent_Loop
1579 (p_visit_id => l_Task_rec.visit_id,
1580 p_visit_task_id => l_Task_rec.visit_task_id ,
1581 p_cost_parent_id => l_Task_rec.cost_parent_id
1582 );
1583
1584 END IF;
1585
1586 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1587 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validation: End -- For COST PARENT ');
1588 END IF;
1589
1590 ----------- End defining and validate all LOVs on Create Visit's Task UI Screen---
1591
1592 ----------------------- validate ----------------------
1593
1594 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1595 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Validate');
1596 END IF;
1597
1598 -- For all optional fields check if its g_miss_num/g_miss_char/g_miss_date
1599 -- then Null else the value call Default_Missing_Attribs procedure
1600 Default_Missing_Attribs
1601 (
1602 p_x_task_rec => l_Task_rec
1603 );
1604
1605 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1606 Check_Task_Items (
1607 p_task_rec => p_x_task_rec,
1608 p_validation_mode => Jtf_Plsql_Api.g_update,
1609 x_return_status => l_return_status
1610 );
1611
1612 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1613 RAISE Fnd_Api.g_exc_unexpected_error;
1614 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1615 RAISE Fnd_Api.g_exc_error;
1616 END IF;
1617 END IF;
1618
1619 -- Check Object version number.
1620 IF (c_task_rec.object_version_number <> l_task_rec.object_version_number) THEN
1621 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1622 Fnd_Msg_Pub.ADD;
1623 RAISE Fnd_Api.g_exc_error;
1624 END IF;
1625
1626 -- Post 11.5.10 Changes by Senthil.
1627 IF(L_task_rec.STAGE_ID IS NOT NULL OR L_task_rec.STAGE_NAME IS NOT NULL) THEN
1628
1629 AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
1630 P_API_VERSION => 1.0,
1631 P_VISIT_ID => l_Task_rec.visit_id,
1632 P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
1633 P_STAGE_NAME => L_task_rec.STAGE_NAME,
1634 X_STAGE_ID => L_task_rec.STAGE_ID,
1635 X_RETURN_STATUS => l_return_status,
1636 X_MSG_COUNT => l_msg_count,
1637 X_MSG_DATA => l_msg_data );
1638
1639 END IF;
1640
1641 -- Standard check to count messages
1642 l_msg_count := Fnd_Msg_Pub.count_msg;
1643
1644 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.g_ret_sts_success THEN
1645 X_msg_count := l_msg_count;
1646 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1647 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1648 END IF;
1649
1650 -------------------------- update --------------------
1651 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1652 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Update');
1653 END IF;
1654
1655 --Modified by mpothuku to fix LTP forum issue #208 on 04/19/05
1656 IF( nvl(p_module_type,'XXX') <> 'LTP') THEN
1657 l_task_rec.originating_task_id := c_task_rec.originating_task_id;
1658 END IF;
1659 --End mpothuku
1660
1661 Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
1662 X_VISIT_TASK_ID => l_task_rec.visit_task_id,
1663 X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
1664 X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
1665 X_VISIT_ID => l_task_rec.visit_id,
1666 X_PROJECT_TASK_ID => c_task_rec.project_task_id,
1667 X_COST_PARENT_ID => l_task_rec.cost_parent_id,
1668 X_MR_ROUTE_ID => c_task_rec.mr_route_id,
1669 X_MR_ID => c_task_rec.mr_id,
1670 X_DURATION => c_task_rec.duration,
1671 X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
1672 X_START_FROM_HOUR => l_task_rec.start_from_hour,
1673 X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
1674 X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
1675 X_INSTANCE_ID => c_Task_rec.instance_id,
1676 X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
1677 X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id, --c_task_rec.originating_task_id,
1678 X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
1679 X_TASK_TYPE_CODE => l_task_rec.TASK_TYPE_CODE,
1680 -- PRAKKUM :: FP:PIE :: 13-OCT-2010 :: update Service Type code too
1681 X_SERVICE_TYPE_CODE => l_task_rec.service_type_code,
1682 X_DEPARTMENT_ID => l_task_rec.DEPARTMENT_ID,
1683 X_SUMMARY_TASK_FLAG => 'N',
1684 X_PRICE_LIST_ID => c_task_rec.price_list_id,
1685 X_STATUS_CODE => c_task_rec.status_code,
1686 X_ESTIMATED_PRICE => c_task_rec.estimated_price,
1687 X_ACTUAL_PRICE => c_task_rec.actual_price,
1688 X_ACTUAL_COST => c_task_rec.actual_cost,
1689 -- Changes for 11.5.10 by Senthil.
1690 X_STAGE_ID => l_task_rec.STAGE_ID,
1691 -- Added cxcheng POST11510--------------
1692 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010
1693 -- Pass past dates too, and if it is null, pass null for all the 4 columns below
1694 X_START_DATE_TIME => NVL(l_task_rec.PAST_TASK_START_DATE, c_task_rec.START_DATE_TIME), --SKPATHAK :: Bug 13890788
1695 X_END_DATE_TIME => NVL(l_task_rec.PAST_TASK_END_DATE,c_task_rec.END_DATE_TIME), --SKPATHAK :: Bug 13890788
1696 X_PAST_TASK_START_DATE => l_task_rec.PAST_TASK_START_DATE,
1697 X_PAST_TASK_END_DATE => l_task_rec.PAST_TASK_END_DATE,
1698 --MANESING::Component Maintenance Planning Project, 07-Aug-2011, added target quantity
1699 X_TARGET_QTY => c_task_rec.target_qty,
1700
1701 -- PRAKKUM commented the attributes loading from c_task_rec and added from
1702 -- l_task_rec for DFF implementation on 04/04/2012 :: Bug 13923236 :: START
1703 /*
1704 X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
1705 X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
1706 X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
1707 X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
1708 X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
1709 X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
1710 X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
1711 X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
1712 X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
1713 X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
1714 X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
1715 X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
1716 X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
1717 X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
1718 X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
1719 X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
1720 */
1721 X_ATTRIBUTE_CATEGORY => l_task_rec.ATTRIBUTE_CATEGORY,
1722 X_ATTRIBUTE1 => l_task_rec.ATTRIBUTE1,
1723 X_ATTRIBUTE2 => l_task_rec.ATTRIBUTE2,
1724 X_ATTRIBUTE3 => l_task_rec.ATTRIBUTE3,
1725 X_ATTRIBUTE4 => l_task_rec.ATTRIBUTE4,
1726 X_ATTRIBUTE5 => l_task_rec.ATTRIBUTE5,
1727 X_ATTRIBUTE6 => l_task_rec.ATTRIBUTE6,
1728 X_ATTRIBUTE7 => l_task_rec.ATTRIBUTE7,
1729 X_ATTRIBUTE8 => l_task_rec.ATTRIBUTE8,
1730 X_ATTRIBUTE9 => l_task_rec.ATTRIBUTE9,
1731 X_ATTRIBUTE10 => l_task_rec.ATTRIBUTE10,
1732 X_ATTRIBUTE11 => l_task_rec.ATTRIBUTE11,
1733 X_ATTRIBUTE12 => l_task_rec.ATTRIBUTE12,
1734 X_ATTRIBUTE13 => l_task_rec.ATTRIBUTE13,
1735 X_ATTRIBUTE14 => l_task_rec.ATTRIBUTE14,
1736 X_ATTRIBUTE15 => l_task_rec.ATTRIBUTE15,
1737
1738 -- PRAKKUM commented the attributes loading from c_task_rec and added from
1739 -- l_task_rec for DFF implementation on 04/04/2012 :: Bug 13923236 :: END
1740
1741 X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
1742 X_DESCRIPTION => l_task_rec.description,
1743 X_QUANTITY => c_task_rec.QUANTITY, -- Added by rnahata for Issue 105
1744 X_LAST_UPDATE_DATE => SYSDATE,
1745 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1746 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
1747
1748 --salogan added for supplier warranty starts
1749 --added the SR id from c_task_rec cursor whuch gets data from DB to l_task_rec for SR-VWP-SW changes
1750 l_task_rec.SERVICE_REQUEST_ID := c_task_rec.service_request_id;
1751 -- calling AHL_VWP_RULES_PVT.Get_Warranty_Rec_for_Update
1752 AHL_VWP_RULES_PVT.Get_Warranty_Rec_for_Update(
1753 p_task_rec => l_task_rec,
1754 x_warranty_entl_tbl => l_entl_rec_tbl);
1755
1756 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1757 fnd_log.string(fnd_log.level_statement,
1758 L_DEBUG,
1759 'Before calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements.');
1760 END IF;
1761
1762 -- calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements
1763 AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements(
1764 p_user_role => AHL_WARRANTY_ENTL_PVT.G_USER_PLANNER,
1765 p_x_warranty_entl_tbl => l_entl_rec_tbl,
1766 x_return_status => l_return_status,
1767 x_msg_count => l_msg_count,
1768 x_msg_data => l_msg_data);
1769
1770 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1771 fnd_log.string(fnd_log.level_statement,
1772 L_DEBUG,
1773 'After calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements. Return Status = ' ||
1774 l_return_status);
1775 END IF;
1776
1777 l_msg_count := Fnd_Msg_Pub.count_msg;
1778 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1779 x_msg_count := l_msg_count;
1780 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1781 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1782 END IF;
1783 --salogan added for supplier warranty ends
1784
1785 -- Added cxcheng POST11510--------------
1786 --Now adjust the times derivation for task
1787 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1788 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TIMES_PVT.Adjust_Task_Times');
1789 END IF;
1790
1791 --SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Call Adjust_Task_Times only if past date is null
1792 IF l_task_rec.PAST_TASK_START_DATE IS NULL THEN
1793 AHL_VWP_TIMES_PVT.Adjust_Task_Times(p_api_version => 1.0,
1794 p_init_msg_list => Fnd_Api.G_FALSE,
1795 p_commit => Fnd_Api.G_FALSE,
1796 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1797 x_return_status => l_return_status,
1798 x_msg_count => l_msg_count,
1799 x_msg_data => l_msg_data,
1800 p_task_id => l_task_rec.visit_task_id);
1801 END IF;
1802
1803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1804 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_TIMES_PVT.Adjust_Task_Times - l_return_status : '||l_return_status);
1805 END IF;
1806
1807 --Standard check to count messages
1808 l_msg_count := Fnd_Msg_Pub.count_msg;
1809
1810 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.g_ret_sts_success THEN
1811 x_msg_count := l_msg_count;
1812 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1813 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1814 END IF;
1815
1816 -- Assign back to in/out parameter
1817 p_x_task_rec := l_task_rec;
1818
1819 -- To Update visit attribute any_task_chg_flag for costing purpose
1820 -- Looking for changes in 'Start from hour' attributes of task
1821
1822 IF NVL(l_task_rec.Start_from_hour,-30) <> NVL(c_task_rec.Start_from_hour,-30) OR
1823 NVL(l_task_rec.STAGE_ID,-30) <> NVL(c_task_rec.STAGE_ID,-30) OR
1824 NVL(l_task_rec.department_id,-30) <> NVL(c_task_rec.department_id,-30) THEN
1825 OPEN c_Task(l_Task_rec.visit_task_id);
1826 FETCH c_Task INTO c_upd_Task_rec;
1827 CLOSE c_Task;
1828
1829 IF c_upd_Task_rec.start_date_time IS NOT NULL THEN
1830 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1831 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
1832 END IF;
1833
1834 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
1835 p_api_version => 1.0,
1836 p_init_msg_list => FND_API.g_false,
1837 p_commit => FND_API.g_false,
1838 p_validation_level => FND_API.g_valid_level_full,
1839 p_visit_id => l_task_rec.visit_id,
1840 p_visit_task_id => NULL,
1841 p_org_id => NULL,
1842 p_start_date => NULL,
1843 p_operation_flag => 'U',
1844 x_planned_order_flag => l_planned_order_flag ,
1845 x_return_status => l_return_status,
1846 x_msg_count => l_msg_count,
1847 x_msg_data => l_msg_data );
1848
1849 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1850 fnd_log.string(fnd_log.level_statement,L_DEBUG,'After AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials - l_return_status : '||l_return_status);
1851 fnd_log.string(fnd_log.level_statement,L_DEBUG,'Planned Order Flag : ' || l_planned_order_flag);
1852 END IF;
1853
1854 IF l_return_status <> 'S' THEN
1855 RAISE Fnd_Api.G_EXC_ERROR;
1856 END IF;
1857 END IF;
1858
1859 IF c_visit_rec.any_task_chg_flag = 'N' THEN
1860 AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
1861 (p_visit_id => l_task_rec.visit_id,
1862 p_flag => 'Y',
1863 x_return_status => x_return_status);
1864 END IF;
1865 END IF;
1866
1867 --Standard check to count messages
1868 l_msg_count := Fnd_Msg_Pub.count_msg;
1869
1870 IF l_msg_count > 0 THEN
1871 X_msg_count := l_msg_count;
1872 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1873 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1874 END IF;
1875
1876 --Standard check for commit
1877 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1878 COMMIT;
1879 END IF;
1880
1881 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1882 fnd_log.string(fnd_log.level_procedure,L_DEBUG ||'.end','At the end of PLSQL procedure');
1883 END IF;
1884
1885 EXCEPTION
1886 WHEN Fnd_Api.g_exc_error THEN
1887 ROLLBACK TO Update_Planned_Task;
1888 x_return_status := Fnd_Api.g_ret_sts_error;
1889 Fnd_Msg_Pub.count_and_get (
1890 p_encoded => Fnd_Api.g_false,
1891 p_count => x_msg_count,
1892 p_data => x_msg_data
1893 );
1894 WHEN Fnd_Api.g_exc_unexpected_error THEN
1895 ROLLBACK TO Update_Planned_Task;
1896 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1897 Fnd_Msg_Pub.count_and_get (
1898 p_encoded => Fnd_Api.g_false,
1899 p_count => x_msg_count,
1900 p_data => x_msg_data
1901 );
1902 WHEN OTHERS THEN
1903 ROLLBACK TO Update_Planned_Task;
1904 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1905 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
1906 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1907 END IF;
1908 Fnd_Msg_Pub.count_and_get (
1909 p_encoded => Fnd_Api.g_false,
1910 p_count => x_msg_count,
1911 p_data => x_msg_data
1912 );
1913 END Update_Planned_Task;
1914
1915 --------------------------------------------------------------------
1916 -- PROCEDURE
1917 -- Delete_Planned_Task
1918 --
1919 -- PURPOSE
1920 -- To delete Planned tasks for the Maintenace visit.
1921 --------------------------------------------------------------------
1922 PROCEDURE Delete_Planned_Task (
1923 p_api_version IN NUMBER,
1924 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1925 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1926 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1927 p_module_type IN VARCHAR2 := 'JSP',
1928 p_visit_task_ID IN NUMBER,
1929
1930 x_return_status OUT NOCOPY VARCHAR2,
1931 x_msg_count OUT NOCOPY NUMBER,
1932 x_msg_data OUT NOCOPY VARCHAR2
1933 )
1934
1935 IS
1936
1937 l_api_version CONSTANT NUMBER := 1.0;
1938 l_api_name CONSTANT VARCHAR2(30) := 'Delete Planned Task';
1939 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1940 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
1941 -- local variables defined for the procedure
1942 l_origin_id NUMBER;
1943
1944 -- To find all tasks related information
1945 CURSOR c_Task (x_id IN NUMBER) IS
1946 SELECT * FROM Ahl_Visit_Tasks_VL
1947 WHERE Visit_Task_ID = x_id;
1948 c_task_rec c_Task%ROWTYPE;
1949 BEGIN
1950 --------------------- initialize -----------------------
1951 SAVEPOINT Delete_Planned_Task;
1952 -- Check if API is called in debug mode. If yes, enable debug.
1953 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1954 fnd_log.string(fnd_log.level_procedure,l_debug||'.begin','At the start of PLSQL procedure');
1955 END IF;
1956
1957 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1958 Fnd_Msg_Pub.initialize;
1959 END IF;
1960
1961 -- Initialize API return status to success
1962 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1963
1964 -- Standard call to check for call compatibility.
1965 IF NOT Fnd_Api.compatible_api_call(
1966 l_api_version,
1967 p_api_version,
1968 l_api_name,
1969 G_PKG_NAME
1970 ) THEN
1971 RAISE Fnd_Api.g_exc_unexpected_error;
1972 END IF;
1973
1974 ------------------------Start of API Body------------------------------------
1975 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1976 fnd_log.string(fnd_log.level_statement,l_debug,'Task Id' || p_visit_task_ID);
1977 END IF;
1978
1979 -- To check if the input taskid exists in task entity.
1980 OPEN c_Task(p_Visit_Task_ID);
1981 FETCH c_Task INTO c_task_rec;
1982
1983 IF c_Task%NOTFOUND THEN
1984 CLOSE c_Task;
1985 Fnd_Message.set_name('AHL', 'AHL_VWP_TASK_ID_INVALID');
1986 FND_MESSAGE.SET_TOKEN('TASK_ID',p_visit_task_id,false);
1987 Fnd_Msg_Pub.ADD;
1988 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1989 fnd_log.string(fnd_log.level_statement,l_debug,'Invalid Task Id' || p_visit_task_ID);
1990 END IF;
1991 RAISE Fnd_Api.g_exc_error;
1992 ELSE
1993 CLOSE c_Task;
1994 -- To find the visit related information
1995 l_origin_id:= c_task_rec.originating_task_id;
1996
1997 If l_origin_id is Not Null then
1998 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1999 fnd_log.string(fnd_log.level_statement,l_debug,'Before calling AHL_VWP_TASKS_PVT.Delete_Summary_Task');
2000 END IF;
2001
2002 AHL_VWP_TASKS_PVT.Delete_Summary_Task(
2003 p_api_version => p_api_version,
2004 p_init_msg_list => Fnd_Api.g_false,
2005 p_commit => Fnd_Api.g_false,
2006 p_validation_level => Fnd_Api.g_valid_level_full,
2007 p_module_type => NULL,
2008 p_Visit_Task_Id => l_origin_id,
2009 x_return_status => x_return_status,
2010 x_msg_count => x_msg_count,
2011 x_msg_data => x_msg_data);
2012
2013 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2014 fnd_log.string(fnd_log.level_statement,l_debug,'After calling AHL_VWP_TASKS_PVT.Delete_Summary_Task : x_return_status - '||x_return_status);
2015 END IF;
2016
2017 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
2018 RAISE Fnd_Api.G_EXC_ERROR;
2019 END IF;
2020 ELSE
2021 Fnd_Message.SET_NAME('AHL','AHL_VWP_PLANNEDTASKMR');
2022 Fnd_Msg_Pub.ADD;
2023 RAISE Fnd_Api.G_EXC_ERROR;
2024 End IF;
2025 END IF;
2026 ------------------------End of API Body------------------------------------
2027 IF Fnd_Api.to_boolean (p_commit) THEN
2028 COMMIT;
2029 END IF;
2030
2031 Fnd_Msg_Pub.count_and_get (
2032 p_encoded => Fnd_Api.g_false,
2033 p_count => x_msg_count,
2034 p_data => x_msg_data
2035 );
2036
2037 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2038 fnd_log.string(fnd_log.level_procedure,l_debug ||'.end','At the end of PLSQL procedure');
2039 END IF;
2040 EXCEPTION
2041 WHEN Fnd_Api.g_exc_error THEN
2042 ROLLBACK TO Delete_Planned_Task;
2043 x_return_status := Fnd_Api.g_ret_sts_error;
2044 Fnd_Msg_Pub.count_and_get (
2045 p_encoded => Fnd_Api.g_false,
2046 p_count => x_msg_count,
2047 p_data => x_msg_data
2048 );
2049
2050 WHEN Fnd_Api.g_exc_unexpected_error THEN
2051 ROLLBACK TO Delete_Planned_Task;
2052 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2053 Fnd_Msg_Pub.count_and_get (
2054 p_encoded => Fnd_Api.g_false,
2055 p_count => x_msg_count,
2056 p_data => x_msg_data
2057 );
2058
2059 WHEN OTHERS THEN
2060 ROLLBACK TO Delete_Planned_Task;
2061 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2062 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2063 THEN
2064 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2065 END IF;
2066 Fnd_Msg_Pub.count_and_get (
2067 p_encoded => Fnd_Api.g_false,
2068 p_count => x_msg_count,
2069 p_data => x_msg_data
2070 );
2071
2072 END Delete_Planned_Task;
2073
2074 ---------------------------------------------------------------------
2075 -- PROCEDURE
2076 -- Check_Task_Items
2077 --
2078 -- PURPOSE
2079 --
2080 ---------------------------------------------------------------------
2081 PROCEDURE Check_Task_Items (
2082 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
2083 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
2084
2085 x_return_status OUT NOCOPY VARCHAR2
2086 )
2087 IS
2088 l_api_name CONSTANT VARCHAR2(30) := 'Check_Task_Items';
2089 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
2090 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
2091
2092 BEGIN
2093 --
2094 -- Validate required items.
2095 x_return_status := FND_API.G_RET_STS_SUCCESS;
2096
2097 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2098 fnd_log.string(fnd_log.level_procedure,l_debug||'.begin', 'At the start of the procedure..');
2099 fnd_log.string(fnd_log.level_procedure,l_debug, 'Before Check_Visit_Task_Req_Items');
2100 END IF;
2101
2102 Check_Visit_Task_Req_Items (
2103 p_task_rec => p_task_rec,
2104 x_return_status => x_return_status
2105 );
2106 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
2107 RETURN;
2108 END IF;
2109
2110 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2111 fnd_log.string(fnd_log.level_statement,l_debug,'After Check_Visit_Task_Req_Items');
2112 END IF;
2113
2114 -- Validate uniqueness.
2115 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2116 fnd_log.string(fnd_log.level_statement,l_debug,'Before Check_Visit_Task_UK_Items');
2117 END IF;
2118
2119 Check_Visit_Task_UK_Items (
2120 p_task_rec => p_task_rec,
2121 p_validation_mode => p_validation_mode,
2122 x_return_status => x_return_status
2123 );
2124 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
2125 RETURN;
2126 END IF;
2127
2128 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2129 fnd_log.string(fnd_log.level_procedure,l_debug,'After Check_Visit_Task_UK_Items..');
2130 fnd_log.string(fnd_log.level_procedure,l_debug||'.end','At the end of the procedure');
2131 END IF;
2132 END Check_Task_Items;
2133
2134 ---------------------------------------------------------------------
2135 -- PROCEDURE
2136 -- Complete_Visit_Task_Rec
2137 --
2138 -- PURPOSE
2139 --
2140 ---------------------------------------------------------------------
2141 /* It doesn't seem to be used anywhere
2142 PROCEDURE Complete_Visit_Task_Rec (
2143 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
2144 x_complete_rec OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
2145 )
2146 IS
2147 CURSOR c_Visit_Task IS
2148 SELECT *
2149 FROM Ahl_Visit_Tasks_vl
2150 WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;
2151 --
2152 -- This is the only exception for using %ROWTYPE.
2153 -- We are selecting from the VL view, which may
2154 -- have some denormalized columns as compared to
2155 -- the base tables.
2156 l_task_rec c_Visit_Task%ROWTYPE;
2157 BEGIN
2158 x_complete_rec := p_task_rec;
2159 OPEN c_Visit_Task;
2160 FETCH c_Visit_Task INTO l_task_rec;
2161 IF c_Visit_Task%NOTFOUND THEN
2162 CLOSE c_Visit_Task;
2163 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
2164 Fnd_Msg_Pub.ADD;
2165 RAISE Fnd_Api.g_exc_error;
2166 END IF;
2167 CLOSE c_Visit_Task;
2168
2169 END Complete_Visit_Task_Rec;
2170 */
2171
2172 ---------------------------------------------------------------------
2173 -- PROCEDURE
2174 -- Check_Visit_Task_Req_Items
2175 --
2176 -- PURPOSE
2177 --
2178 ---------------------------------------------------------------------
2179 PROCEDURE Check_Visit_Task_Req_Items (
2180 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
2181 x_return_status OUT NOCOPY VARCHAR2
2182 )
2183 IS
2184 l_api_name CONSTANT VARCHAR2(30) := 'Check_Visit_Task_Req_Items';
2185 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
2186 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
2187
2188 BEGIN
2189
2190 x_return_status := FND_API.G_RET_STS_SUCCESS;
2191
2192 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2193 fnd_log.string(fnd_log.level_procedure,l_debug,'At the Start of Check_Visit_Task_Req_Items ');
2194 END IF;
2195
2196 -- TASK NAME ==== NAME
2197 IF (p_task_rec.VISIT_TASK_NAME IS NULL OR p_Task_rec.VISIT_TASK_NAME = Fnd_Api.G_MISS_CHAR) THEN
2198 Fnd_Message.set_name ('AHL', 'AHL_VWP_NAME_MISSING');
2199 Fnd_Msg_Pub.ADD;
2200 x_return_status := Fnd_Api.g_ret_sts_error;
2201 RETURN;
2202 END IF;
2203
2204 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2205 fnd_log.string(fnd_log.level_statement,l_debug,'Inside Validation Start from Hour = ' || p_task_rec.START_FROM_HOUR);
2206 END IF;
2207
2208 IF (p_task_rec.START_FROM_HOUR IS NOT NULL and p_Task_rec.START_FROM_HOUR <> Fnd_Api.G_MISS_NUM) THEN
2209 IF p_task_rec.START_FROM_HOUR < 0 OR FLOOR(p_task_rec.START_FROM_HOUR) <> p_task_rec.START_FROM_HOUR THEN
2210 Fnd_Message.set_name ('AHL', 'AHL_VWP_ONLY_POSITIVE_VALUE');
2211 Fnd_Msg_Pub.ADD;
2212 x_return_status := Fnd_Api.g_ret_sts_error;
2213 RETURN;
2214 END IF;
2215 END IF;
2216
2217 END Check_Visit_Task_Req_Items;
2218
2219 ---------------------------------------------------------------------
2220 -- PROCEDURE
2221 -- Check_Visit_Task_UK_Items
2222 --
2223 -- PURPOSE
2224 --
2225 ---------------------------------------------------------------------
2226 PROCEDURE Check_Visit_Task_UK_Items (
2227 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
2228 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
2229 x_return_status OUT NOCOPY VARCHAR2
2230 )
2231 IS
2232 l_valid_flag VARCHAR2(1);
2233 l_api_name CONSTANT VARCHAR2(30) := 'Check_Visit_Task_Req_Items';
2234 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
2235 l_debug CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
2236
2237 BEGIN
2238 x_return_status := Fnd_Api.g_ret_sts_success;
2239 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2240 fnd_log.string(fnd_log.level_procedure,l_debug,'At the Start of Check_Visit_Task_UK_Items ');
2241 END IF;
2242 --
2243 -- For Task, when ID is passed in, we need to
2244 -- check if this ID is unique.
2245 IF p_validation_mode = Jtf_Plsql_Api.g_create AND p_task_rec.Visit_Task_ID IS NOT NULL THEN
2246 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2247 fnd_log.string(fnd_log.level_statement,'ahl.plsql.',': = Check_Visit_Task_UK_Items Uniqueness Of ID');
2248 END IF;
2249 -- FOR CREATION
2250 IF Ahl_Utility_Pvt.check_uniqueness(
2251 'Ahl_Visit_Tasks_vl',
2252 'Visit_Task_ID = ' || p_task_rec.Visit_Task_ID
2253 ) = Fnd_Api.g_false
2254 THEN
2255 Fnd_Message.set_name ('AHL', 'AHL_VWP_DUPLICATE_TASK_ID');
2256 Fnd_Msg_Pub.ADD;
2257 x_return_status := Fnd_Api.g_ret_sts_error;
2258 RETURN;
2259 END IF;
2260 END IF;
2261
2262 END Check_Visit_Task_UK_Items;
2263
2264 END AHL_VWP_PLAN_TASKS_PVT;