[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_UNPLAN_TASKS_PVT
Source
1 PACKAGE BODY AHL_VWP_UNPLAN_TASKS_PVT AS
2 /* $Header: AHLVUPTB.pls 120.13.12020000.2 2012/12/11 03:20:59 prakkum ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_VWP_UNPLAN_TASKS_PVT
6 --
7 -- PURPOSE
8 -- This package body is a Private API for VWP Unplanned Tasks in Advanced Services Online.
9 -- It contains specification for pl/sql records and tables.
10 --
11 -- PROCEDURES
12 -- Create_Unplanned_Task
13 -- Update_Unplanned_Task
14 -- Delete_Unplanned_Task
15 -- Asso_Inst_Dept_SR_To_Tasks
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 -- 12-MAY_2002 Shbhanda Created.
21 -- 21-FEB-2003 YAZHOU Separated from Task package
22 -- 06-AUG-2003 SHBHANDA 11.5.10 Changes
23 -----------------------------------------------------------------
24 -- Define Global CONSTANTS --
25 -----------------------------------------------------------------
26 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_UNPLAN_TASKS_PVT';
27 -----------------------------------------------------------------
28
29 --------------------------------------------------------------------
30 -- START: Defining local functions and procedures SIGNATURES --
31 --------------------------------------------------------------------
32 -- To Check_Visit_Task_Req_Items
33 PROCEDURE Check_Visit_Task_Req_Items (
34 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
35 x_return_status OUT NOCOPY VARCHAR2
36 );
37
38 -- To Check_Visit_Task_UK_Items
39 PROCEDURE Check_Visit_Task_UK_Items (
40 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
41 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
42 x_return_status OUT NOCOPY VARCHAR2
43 );
44
45 -- To Check_Task_Items
46 PROCEDURE Check_Task_Items (
47 p_Task_rec IN AHL_VWP_RULES_PVT.task_rec_type,
48 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
49 x_return_status OUT NOCOPY VARCHAR2
50 );
51
52 -- To assign Null to missing attributes of visit while creation/updation.
53 PROCEDURE Default_Missing_Attribs(
54 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
55 );
56
57 -- To associated Service Request Or Serial Number to Tasks
58 PROCEDURE Asso_Inst_Dept_SR_to_Tasks (
59 p_module_type IN VARCHAR2 := 'JSP',
60 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
61
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2
65 );
66
67 --------------------------------------------------------------------
68 -- END: Defining local functions and procedures SIGNATURES --
69 --------------------------------------------------------------------
70
71 --------------------------------------------------------------------
72 -- START: Defining local functions and procedures BODY --
73 --------------------------------------------------------------------
74 --------------------------------------------------------------------
75 -- PROCEDURE
76 -- Default_Missing_Attribs
77 --
78 -- PURPOSE
79 -- For all optional fields check if its g_miss_num/g_miss_char/
80 -- g_miss_date then Null else the value
81
82 --------------------------------------------------------------------
83 PROCEDURE Default_Missing_Attribs
84 ( p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type)
85 AS
86 BEGIN
87 IF p_x_task_rec.DURATION = Fnd_Api.G_MISS_NUM THEN
88 p_x_task_rec.DURATION := NULL;
89 ELSE
90 p_x_task_rec.DURATION := p_x_task_rec.DURATION;
91 END IF;
92
93 IF p_x_task_rec.PROJECT_TASK_ID = Fnd_Api.G_MISS_NUM THEN
94 p_x_task_rec.PROJECT_TASK_ID := NULL;
95 ELSE
96 p_x_task_rec.PROJECT_TASK_ID := p_x_task_rec.PROJECT_TASK_ID;
97 END IF;
98
99 IF p_x_task_rec.COST_PARENT_ID = Fnd_Api.G_MISS_NUM THEN
100 p_x_task_rec.COST_PARENT_ID := NULL;
101 ELSE
102 p_x_task_rec.COST_PARENT_ID := p_x_task_rec.COST_PARENT_ID;
103 END IF;
104
105 IF p_x_task_rec.MR_ROUTE_ID = Fnd_Api.G_MISS_NUM THEN
106 p_x_task_rec.MR_ROUTE_ID := NULL;
107 ELSE
108 p_x_task_rec.MR_ROUTE_ID := p_x_task_rec.MR_ROUTE_ID;
109 END IF;
110
111 IF p_x_task_rec.MR_ID = Fnd_Api.G_MISS_NUM THEN
112 p_x_task_rec.MR_ID := NULL;
113 ELSE
114 p_x_task_rec.MR_ID := p_x_task_rec.MR_ID;
115 END IF;
116
117 IF p_x_task_rec.UNIT_EFFECTIVITY_ID = Fnd_Api.G_MISS_NUM THEN
118 p_x_task_rec.UNIT_EFFECTIVITY_ID := NULL;
119 ELSE
120 p_x_task_rec.UNIT_EFFECTIVITY_ID := p_x_task_rec.UNIT_EFFECTIVITY_ID;
121 END IF;
122
123 IF p_x_task_rec.START_FROM_HOUR = Fnd_Api.G_MISS_NUM THEN
124 p_x_task_rec.START_FROM_HOUR := NULL;
125 ELSE
126 p_x_task_rec.START_FROM_HOUR := p_x_task_rec.START_FROM_HOUR;
127 END IF;
128
129 IF p_x_task_rec.PRIMARY_VISIT_TASK_ID = Fnd_Api.G_MISS_NUM THEN
130 p_x_task_rec.PRIMARY_VISIT_TASK_ID := NULL;
131 ELSE
132 p_x_task_rec.PRIMARY_VISIT_TASK_ID := p_x_task_rec.PRIMARY_VISIT_TASK_ID;
133 END IF;
134
135 IF p_x_task_rec.ORIGINATING_TASK_ID = Fnd_Api.G_MISS_NUM THEN
136 p_x_task_rec.ORIGINATING_TASK_ID := NULL;
137 ELSE
138 p_x_task_rec.ORIGINATING_TASK_ID := p_x_task_rec.ORIGINATING_TASK_ID;
139 END IF;
140
141 IF p_x_task_rec.SERVICE_REQUEST_ID = Fnd_Api.G_MISS_NUM THEN
142 p_x_task_rec.SERVICE_REQUEST_ID := NULL;
143 ELSE
144 p_x_task_rec.SERVICE_REQUEST_ID := p_x_task_rec.SERVICE_REQUEST_ID;
145 END IF;
146
147 IF p_x_task_rec.attribute_category = Fnd_Api.G_MISS_CHAR THEN
148 p_x_task_rec.attribute_category := NULL;
149 ELSE
150 p_x_task_rec.attribute_category := p_x_task_rec.attribute_category;
151 END IF;
152
153 IF p_x_task_rec.attribute1 = Fnd_Api.G_MISS_CHAR THEN
154 p_x_task_rec.attribute1 := NULL;
155 ELSE
156 p_x_task_rec.attribute1 := p_x_task_rec.attribute1;
157 END IF;
158
159 IF p_x_task_rec.attribute2 = Fnd_Api.G_MISS_CHAR THEN
160 p_x_task_rec.attribute2 := NULL;
161 ELSE
162 p_x_task_rec.attribute2 := p_x_task_rec.attribute2;
163 END IF;
164 --
165 IF p_x_task_rec.attribute3 = Fnd_Api.G_MISS_CHAR THEN
166 p_x_task_rec.attribute3 := NULL;
167 ELSE
168 p_x_task_rec.attribute3 := p_x_task_rec.attribute3;
169 END IF;
170 --
171 IF p_x_task_rec.attribute4 = Fnd_Api.G_MISS_CHAR THEN
172 p_x_task_rec.attribute4 := NULL;
173 ELSE
174 p_x_task_rec.attribute4 := p_x_task_rec.attribute4;
175 END IF;
176 --
177 IF p_x_task_rec.attribute5 = Fnd_Api.G_MISS_CHAR THEN
178 p_x_task_rec.attribute5 := NULL;
179 ELSE
180 p_x_task_rec.attribute5 := p_x_task_rec.attribute5;
181 END IF;
182 --
183 IF p_x_task_rec.attribute6 = Fnd_Api.G_MISS_CHAR THEN
184 p_x_task_rec.attribute6 := NULL;
185 ELSE
186 p_x_task_rec.attribute6 := p_x_task_rec.attribute6;
187 END IF;
188 --
189 IF p_x_task_rec.attribute7 = Fnd_Api.G_MISS_CHAR THEN
190 p_x_task_rec.attribute7 := NULL;
191 ELSE
192 p_x_task_rec.attribute7 := p_x_task_rec.attribute7;
193 END IF;
194 --
195 IF p_x_task_rec.attribute8 = Fnd_Api.G_MISS_CHAR THEN
196 p_x_task_rec.attribute8 := NULL;
197 ELSE
198 p_x_task_rec.attribute8 := p_x_task_rec.attribute8;
199 END IF;
200 --
201 IF p_x_task_rec.attribute9 = Fnd_Api.G_MISS_CHAR THEN
202 p_x_task_rec.attribute9 := NULL;
203 ELSE
204 p_x_task_rec.attribute9 := p_x_task_rec.attribute9;
205 END IF;
206 --
207 IF p_x_task_rec.attribute10 = Fnd_Api.G_MISS_CHAR THEN
208 p_x_task_rec.attribute10 := NULL;
209 ELSE
210 p_x_task_rec.attribute10 := p_x_task_rec.attribute10;
211 END IF;
212 --
213 IF p_x_task_rec.attribute11 = Fnd_Api.G_MISS_CHAR THEN
214 p_x_task_rec.attribute11 := NULL;
215 ELSE
216 p_x_task_rec.attribute11 := p_x_task_rec.attribute11;
217 END IF;
218 --
219 IF p_x_task_rec.attribute12 = Fnd_Api.G_MISS_CHAR THEN
220 p_x_task_rec.attribute12 := NULL;
221 ELSE
222 p_x_task_rec.attribute12 := p_x_task_rec.attribute12;
223 END IF;
224 --
225 IF p_x_task_rec.attribute13 = Fnd_Api.G_MISS_CHAR THEN
226 p_x_task_rec.attribute13 := NULL;
227 ELSE
228 p_x_task_rec.attribute13 := p_x_task_rec.attribute13;
229 END IF;
230 --
231 IF p_x_task_rec.attribute14 = Fnd_Api.G_MISS_CHAR THEN
232 p_x_task_rec.attribute14 := NULL;
233 ELSE
234 p_x_task_rec.attribute14 := p_x_task_rec.attribute14;
235 END IF;
236 --
237 IF p_x_task_rec.attribute15 = Fnd_Api.G_MISS_CHAR THEN
238 p_x_task_rec.attribute15 := NULL;
239 ELSE
240 p_x_task_rec.attribute15 := p_x_task_rec.attribute15;
241 END IF;
242 --
243 IF p_x_task_rec.description = Fnd_Api.G_MISS_CHAR THEN
244 p_x_task_rec.description := NULL;
245 ELSE
246 p_x_task_rec.description := p_x_task_rec.description;
247 END IF;
248 END Default_Missing_Attribs;
249
250 --------------------------------------------------------------------
251 -- PROCEDURE
252 -- Asso_Inst_Dept_SR_To_Tasks
253 -- Some logic corrections and clean up (indentation, debug messages) done
254 -- by skpathak on 20-OCT-2008 while fixing bug 7016519
255 --------------------------------------------------------------------
256 PROCEDURE Asso_Inst_Dept_SR_To_Tasks (
257 p_module_type IN VARCHAR2,
258 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.task_rec_type,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2
262 )
263 IS
264 L_API_VERSION CONSTANT NUMBER := 1.0;
265 L_API_NAME CONSTANT VARCHAR2(30) := 'Asso_Inst_Dept_SR_To_Tasks';
266 L_FULL_NAME CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
267
268 -- local variables defined for the procedure
269 l_return_status VARCHAR2(1);
270 l_item_name VARCHAR2(40);
271 l_msg_data VARCHAR2(2000);
272
273 x NUMBER := 0;
274 l_msg_count NUMBER;
275 l_instance_id NUMBER;
276 l_item_id NUMBER;
277 l_count NUMBER;
278 l_org_id NUMBER;
279
280 -- To find out Item and MR Header Id combination exists
281 CURSOR c_check(c_item_id IN NUMBER, c_mr_header_id IN NUMBER) IS
282 SELECT COUNT(*) FROM Ahl_MR_Items_V
283 WHERE Inventory_Item_ID = c_item_id AND MR_HEADER_ID = c_mr_header_id;
284
285 -- To find visit related information
286 CURSOR c_visit(c_visit_id IN NUMBER) IS
287 SELECT * FROM AHL_VISITS_VL
288 WHERE VISIT_ID = c_visit_id;
289 c_visit_rec c_visit%ROWTYPE;
290
291 -- Added by rnahata for Issue 105
292 -- To fetch if the instance is serial controlled/non-serial controlled.
293 CURSOR c_check_inst_nonserial(c_instance_id IN NUMBER) IS
294 SELECT 'X'
295 FROM mtl_system_items_b mtl, csi_item_instances csi
296 WHERE csi.instance_id = c_instance_id
297 AND csi.inventory_item_id = mtl.inventory_item_id
298 AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
299 AND mtl.serial_number_control_code = 1;
300
301 l_serial_ctrl VARCHAR2(2) := NULL;
302 l_inst_id NUMBER := 0 ;
303
304 BEGIN
305
306 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
307 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API');
308 END IF;
309
310
311 ------------------------- Start of Body -------------------------------------
312 --------------------Value OR ID conversion---------------------------
313 --Start API Body
314 IF p_module_type = 'JSP'
315 THEN
316 -- Added by rnahata for Issue 105
317 -- Copied the instance id into intermediatory variable for non-serialised items
318 l_inst_id := p_x_task_Rec.instance_id;
319 p_x_task_Rec.instance_id := NULL;
320 p_x_task_Rec.department_id := NULL;
321 END IF;
322
323 OPEN c_visit(p_x_task_Rec.visit_id);
324 FETCH c_visit INTO c_visit_rec;
325 CLOSE c_visit;
326
327 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
328 fnd_log.string(fnd_log.level_statement, l_full_name, 'tasktype= ' || p_x_task_Rec.task_type_code);
329
330 END IF;
331 -- Check item name or item id
332 IF (p_x_task_Rec.inventory_item_id IS NOT NULL AND
333 p_x_task_Rec.inventory_item_id <> Fnd_Api.G_MISS_NUM) AND
334 (p_x_task_Rec.item_organization_id IS NOT NULL AND
335 p_x_task_Rec.item_organization_id <> Fnd_Api.G_MISS_NUM) THEN
336 AHL_VWP_RULES_PVT.Check_Item_name_Or_Id
337 (p_item_id => p_x_task_Rec.inventory_item_id,
338 p_org_id => p_x_task_Rec.item_organization_id,
339 p_item_name => p_x_task_Rec.item_name,
340 x_item_id => l_item_id,
341 x_org_id => l_org_id,
342 x_item_name => l_item_name,
343 x_return_status => l_return_status,
344 x_error_msg_code => l_msg_data);
345 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
346 fnd_log.string(fnd_log.level_statement, l_full_name, 'item id, item name, orgid: ' || l_item_id || '**' || l_item_name || '**' || l_org_id);
347 END IF;
348 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
349 THEN
350 Fnd_Message.SET_NAME('AHL','AHL_VWP_ITEM_NOT_EXISTS');
351 Fnd_Msg_Pub.ADD;
352 RAISE Fnd_Api.G_EXC_ERROR;
353 END IF;
354
355 -- Changes made by skpathak on 20-OCT-2008 while fixing bug 7016519
356 -- Removing incorrect checks
357
358 p_x_task_Rec.item_name := l_item_name;
359
360
361 ELSE -- Else of item id and item org id exists or not
362 IF p_x_task_Rec.item_name IS NULL OR p_x_task_Rec.item_name = FND_API.g_miss_char THEN
363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
364 fnd_log.string(fnd_log.level_statement, l_full_name, 'Item name missing');
365 END IF;
366 Fnd_Message.SET_NAME('AHL','AHL_VWP_TSK_ITEM_MISSING');
367 Fnd_Msg_Pub.ADD;
368 RAISE Fnd_Api.G_EXC_ERROR;
369 ELSE
370 -- Item name is not null, but at least one of item id and org id is null
371 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
372 fnd_log.string(fnd_log.level_statement, l_full_name, 'Check item else condition.');
373 END IF;
374 Fnd_Message.SET_NAME('AHL','AHL_VWP_ITEM_USE_LOV');
375 Fnd_Msg_Pub.ADD;
376 RAISE Fnd_Api.G_EXC_ERROR;
377 END IF;
378 END IF; -- End of itemid and itemorg id check
379
380 --Assign the returned value
381 p_x_task_Rec.inventory_item_id := l_item_id;
382 p_x_task_Rec.item_organization_id := l_org_id;
383
384 /* Begin fix for Bug 4081044 on Dec 22, 2004 by JR */
385 -- This check in Ahl_MR_Items_V for a valid item/MR does not cover all cases
386 -- viz. MR/PC and MR/Position. So disabling this check for now.
387 -- If a validation is required, then all application cases should also be handled
388 /******
389 IF l_item_id IS NOT NULL AND p_x_task_Rec.MR_Id IS NOT NULL THEN
390 OPEN c_check(l_item_id, p_x_task_Rec.MR_Id);
391 FETCH c_check INTO l_count;
392 CLOSE c_check;
393
394 IF l_count = 0 OR l_count IS NULL THEN
395 x_return_status := Fnd_Api.g_ret_sts_error;
396 Fnd_Message.SET_NAME('AHL','AHL_VWP_ITEM_MR_NOT_MATCH');
397 Fnd_Msg_Pub.ADD;
398 END IF;
399 END IF;
400 ******/
401 /* End fix for Bug 4081044 on Dec 22, 2004 by JR */
402
403 -- Begin changes by rnahata for Issue 105
404 -- Check if the item is serial/non-serial controlled
405 OPEN c_check_inst_nonserial (l_inst_id);
406 FETCH c_check_inst_nonserial INTO l_serial_ctrl;
407 IF c_check_inst_nonserial%NOTFOUND THEN
408 CLOSE c_check_inst_nonserial;
409 -- Convert serial number to instance/ serial id
410 IF (p_x_task_Rec.serial_number IS NOT NULL AND p_x_task_Rec.serial_number <> Fnd_Api.G_MISS_CHAR) OR
411 (p_x_task_Rec.instance_id IS NOT NULL AND p_x_task_Rec.instance_id <> Fnd_Api.G_MISS_CHAR) THEN
412
413 AHL_VWP_RULES_PVT.Check_serial_name_Or_Id
414 (p_item_id => p_x_task_Rec.inventory_item_id,
415 p_org_id => p_x_task_Rec.item_organization_id,
416 p_serial_id => Null,
417 p_serial_number => p_x_task_Rec.serial_number,
418 x_serial_id => l_instance_id,
419 x_return_status => l_return_status,
420 x_error_msg_code => l_msg_data);
421
422 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
423 Fnd_Message.SET_NAME('AHL','AHL_VWP_SERIAL_NOT_EXISTS');
424 Fnd_Msg_Pub.ADD;
425 RAISE Fnd_Api.G_EXC_ERROR;
426 ELSE
427 --Assign the returned value
428 p_x_task_Rec.instance_id := l_instance_id;
429 END IF;
430 ELSE
431 -- Neither Serial Number not Instance Id has been passed
432 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
433 fnd_log.string(fnd_log.level_statement, l_full_name, 'Check serial not found else');
434 END IF;
435 Fnd_Message.SET_NAME('AHL','AHL_VWP_TSK_SERIAL_MISSING');
436 Fnd_Msg_Pub.ADD;
437 RAISE Fnd_Api.G_EXC_ERROR;
438 END IF; --Serial number not null
439 ELSE --non serial controlled item
440 p_x_task_Rec.instance_id := l_inst_id;
441 CLOSE c_check_inst_nonserial;
442 END IF; --non-serial ctrl
443 -- End changes by rnahata for Issue 105
444
445 IF c_visit_rec.organization_id IS NOT NULL THEN
446 -- Get dept code using dept description
447 IF (p_x_task_Rec.dept_name IS NOT NULL AND p_x_task_Rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
448
449 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
450 (p_organization_id => c_visit_rec.organization_id,
451 p_dept_name => p_x_task_Rec.dept_name,
452 p_department_id => Null,
453 x_department_id => p_x_task_Rec.department_id,
454 x_return_status => l_return_status,
455 x_error_msg_code => l_msg_data);
456
457 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
458 THEN
459 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
460 Fnd_Msg_Pub.ADD;
461 RAISE Fnd_Api.G_EXC_ERROR;
462 END IF;
463
464 -- Changes for Post 11.5.10 by amagrawa
465 Ahl_vwp_rules_pvt.CHECK_DEPARTMENT_SHIFT
466 (P_DEPT_ID => p_x_task_Rec.department_id,
467 X_RETURN_STATUS => l_return_status);
468
469 IF (NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS) THEN
470 Fnd_Message.SET_NAME('AHL','AHL_VWP_NO_DEPT_SHIFT');
471 Fnd_Msg_Pub.ADD;
472 RAISE Fnd_Api.G_EXC_ERROR;
473 END IF;
474 ELSE
475 p_x_task_Rec.dept_name := NULL;
476 -- Post 11.5.10 changes by Senthil
477 p_x_task_Rec.department_id := c_visit_rec.department_id;
478 END IF;
479 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
480 fnd_log.string(fnd_log.level_statement, l_full_name, 'Dept ID= ' || p_x_task_Rec.department_id);
481 END IF;
482 ELSE -- Else of if visit org not exists
483 IF (p_x_task_Rec.dept_name IS NOT NULL AND p_x_task_Rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
484 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
485 fnd_log.string(fnd_log.level_statement, l_full_name, 'NO ORGANIZATION FOR VISIT');
486 END IF;
487 Fnd_Message.SET_NAME('AHL','AHL_VWP_VISIT_NO_ORG_EXISTS');
488 Fnd_Msg_Pub.ADD;
489 RAISE Fnd_Api.G_EXC_ERROR;
490 END IF;
491 END IF; -- End of if visit org exists
492
493
494 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
495 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'At the normal execution end of the procedure');
496 END IF;
497
498
499 ------------------------- Finish of Body -------------------------------------
500 END Asso_Inst_Dept_SR_To_Tasks;
501
502
503 --------------------------------------------------------------------
504 -- PROCEDURE
505 -- Create_Unplanned_Task
506 -- Some indentation and debug messages clean up done
507 -- by skpathak on 20-OCT-2008 while fixing bug 7016519
508 --
509 --------------------------------------------------------------------
510 PROCEDURE Create_Unplanned_Task (
511 p_api_version IN NUMBER,
512 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
513 p_commit IN VARCHAR2 := Fnd_Api.g_false,
514 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
515 p_module_type IN VARCHAR2 := 'JSP',
516 p_x_task_Rec IN OUT NOCOPY AHL_VWP_RULES_PVT.task_rec_type,
517 x_return_status OUT NOCOPY VARCHAR2,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2
520 )
521 IS
522 L_API_VERSION CONSTANT NUMBER := 1.0;
523 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Unplanned_Task';
524 L_FULL_NAME CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME;
525
526 -- local variables defined for the procedure
527 l_return_status VARCHAR2(1);
528 l_msg_data VARCHAR2(2000);
529 l_msg_count NUMBER;
530 l_item_id NUMBER;
531 l_instance_id NUMBER;
532 l_org_id NUMBER;
533 l_mr_header_id NUMBER;
534 l_visit_id NUMBER;
535 l_department_id NUMBER;
536 l_count NUMBER;
537 i NUMBER:=0;
538 j NUMBER:=0;
539
540 -- Table type for storing MR Id
541 TYPE MR_Tbl IS TABLE OF INTEGER
542 INDEX BY BINARY_INTEGER;
543
544 -- Defining variables to table types
545 MR_Id_tbl MR_tbl;
546 MR_Serial_Tbl AHL_VWP_RULES_PVT.MR_Serial_Tbl_Type;
547
548 -- yazhou 15Nov2005 starts
549 -- Code clean up
550
551 -- To find all child MRs which acts as SUMMARY TASK
552 /* For Bug# 3152532 fix by shbhanda dated 02-Dec-03*/
553 --Cleaned up cxcheng 4-Aug-04
554 --Returns 1 level of child MRs
555 /* CURSOR get_child_mrs_csr(x_mr_id IN NUMBER) IS
556 SELECT REL.RELATED_MR_HEADER_ID
557 FROM AHL_MR_HEADERS_B AMHB, AHL_MR_RELATIONSHIPS_APP_V REL
558 WHERE REL.MR_HEADER_ID = x_mr_id
559 AND REL.RELATED_MR_HEADER_ID = AMHB.MR_HEADER_ID
560 AND AMHB.MR_STATUS_CODE = 'COMPLETE'
561 AND AMHB.VERSION_NUMBER IN
562 ( SELECT VERSION_NUMBER
563 FROM AHL_MR_HEADERS_B
564 WHERE TITLE = AMHB.TITLE
565 AND TRUNC(SYSDATE) BETWEEN TRUNC(nvl(EFFECTIVE_FROM, sysdate-1)) AND
566 TRUNC(NVL(EFFECTIVE_TO,SYSDATE+1))
567 AND MR_STATUS_CODE = 'COMPLETE');
568 */
569 -- yazhou 15Nov2005 ends
570
571 -- To find visit related information
572 CURSOR c_Visit (p_visit_id IN NUMBER) IS
573 SELECT Any_Task_Chg_Flag,visit_id
574 FROM Ahl_Visits_VL
575 WHERE VISIT_ID = p_visit_id;
576 l_visit_csr_rec c_Visit%ROWTYPE;
577
578 -- yazhou 11Nov2005 starts
579 -- Bug fix#4559475
580
581 -- To find any visit task exists for the retrieve Serial Number and MR_ID and other info
582 CURSOR c_MR_Visit (x_id IN NUMBER, x_mr_id IN NUMBER, x_serial_id in NUMBER) IS
583 SELECT AMHV.TITLE
584 FROM AHL_VISIT_TASKS_B AVTB, AHL_MR_HEADERS_APP_V AMHV
585 WHERE AVTB.MR_ID = AMHV.MR_HEADER_ID
586 AND AVTB.MR_Id in (select mr_header_id
587 from ahl_mr_headers_b
588 where title in
589 (select title from ahl_mr_headers_b where mr_header_id = x_mr_id))
590 AND AVTB.INSTANCE_ID = x_serial_id
591 AND AVTB.VISIT_ID = x_id
592 AND (AVTB.STATUS_CODE IS NULL OR AVTB.STATUS_CODE not in ('CANCELLED','DELETED'));
593
594 -- yazhou 11Nov2005 ends
595
596 c_MR_Visit_rec c_MR_Visit%ROWTYPE;
597
598
599 l_ue_id NUMBER;
600 BEGIN
601 --------------------- initialize -----------------------
602 SAVEPOINT Create_Unplanned_Task;
603 -- Debug info.
604 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
605 fnd_log.string(fnd_log.level_procedure, l_full_name || '.begin', 'Entering Procedure');
606 END IF;
607
608 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
609 Fnd_Msg_Pub.initialize;
610 END IF;
611
612 IF NOT Fnd_Api.compatible_api_call (
613 L_API_VERSION,
614 p_api_version,
615 L_API_NAME,
616 G_PKG_NAME
617 ) THEN
618 RAISE Fnd_Api.g_exc_unexpected_error;
619 END IF;
620 x_return_status := Fnd_Api.g_ret_sts_success;
621
622 -- Calling Asso_Inst_Dept_SR_to_Tasks API
623 Asso_Inst_Dept_SR_to_Tasks (
624 p_module_type => p_module_type,
625 p_x_task_Rec => p_x_task_Rec,
626
627 x_return_status => l_return_status,
628 x_msg_count => l_msg_count,
629 x_msg_data => l_msg_data
630 );
631
632 -- Assigning record attributes in local variables
633 l_visit_id := p_x_task_Rec.visit_id;
634 l_department_id := p_x_task_Rec.department_id;
635 l_instance_id := p_x_task_Rec.instance_id;
636 l_item_id := p_x_task_Rec.inventory_item_id;
637 l_org_id := p_x_task_Rec.item_organization_id;
638 l_mr_header_id := p_x_task_Rec.MR_ID;
639
640 IF l_department_id = FND_API.g_miss_num THEN
641 l_department_id := NULL;
642 END IF;
643
644 IF l_instance_id = FND_API.g_miss_num THEN
645 l_instance_id := NULL;
646 END IF;
647
648 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
649 fnd_log.string(fnd_log.level_statement, l_full_name, 'Visitid , service, dept: ' || l_visit_id || '---' || '---' || l_department_id);
650 fnd_log.string(fnd_log.level_statement, l_full_name, 'Serial, Item, Item Org: ' || l_instance_id || '---' ||l_item_id || '---' || l_org_id);
651 fnd_log.string(fnd_log.level_statement, l_full_name, 'mr_header:' ||l_mr_header_id );
652 END IF;
653
654 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
655 fnd_log.string(fnd_log.level_statement, l_full_name, 'About to call AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY with p_mr_header_id = ' || l_mr_header_id || ' and p_instance_id = ' || l_instance_id);
656 END IF;
657
658 AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY(
659 p_api_version => 1.0,
660 x_return_status => l_return_status,
661 x_msg_count => l_msg_count,
662 x_msg_data => l_msg_data,
663 p_mr_header_id => l_mr_header_id,
664 p_instance_id => l_instance_id,
665 x_orig_ue_id => l_ue_id);
666
667 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
668 fnd_log.string(fnd_log.level_statement, l_full_name, 'Returned from call to AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY. x_return_status = ' || l_return_status || ' and x_orig_ue_id = ' || l_ue_id);
669 END IF;
670
671 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
672 X_msg_count := l_msg_count;
673 X_return_status := Fnd_Api.G_RET_STS_ERROR;
674 RAISE Fnd_Api.G_EXC_ERROR;
675 END IF;
676
677
678 p_x_task_Rec.task_type_code :='UNPLANNED';
679 p_x_task_Rec.unit_effectivity_id :=l_ue_id;
680
681 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
682 fnd_log.string(fnd_log.level_statement, l_full_name, 'About to call AHL_VWP_PLAN_TASKS_PVT.CREATE_PLANNED_TASK');
683 END IF;
684
685 AHL_VWP_PLAN_TASKS_PVT.CREATE_PLANNED_TASK(
686 p_api_version => 1.0,
687 p_x_task_rec => p_x_task_Rec,
688 x_return_status => l_return_status,
689 x_msg_count => l_msg_count,
690 x_msg_data => l_msg_data);
691
692 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
693 fnd_log.string(fnd_log.level_statement, l_full_name, 'Returned from call to AHL_VWP_PLAN_TASKS_PVT.CREATE_PLANNED_TASK. x_return_status = ' || l_return_status || ', p_x_task_rec.VISIT_TASK_ID = ' || p_x_task_rec.VISIT_TASK_ID);
694 END IF;
695
696 IF l_msg_count > 0 THEN
697 X_msg_count := l_msg_count;
698 X_return_status := Fnd_Api.G_RET_STS_ERROR;
699 RAISE Fnd_Api.G_EXC_ERROR;
700 END IF;
701
702 -- Update of ahl_schedule_materials added by surrkuma on 15-JUl-2010 for Bug 9901811
703 -- Materials were created with the type of PLANNED. Need to change to UNPLANNED
704 UPDATE ahl_schedule_materials SET material_request_type = 'UNPLANNED'
705 WHERE visit_id = l_visit_id
706 AND visit_task_id IN
707 (
708 SELECT visit_task_id
709 FROM ahl_visit_tasks_b
710 WHERE visit_id = l_visit_id
711 START WITH mr_id = l_mr_header_id AND originating_task_id IS NULL
712 AND UNIT_EFFECTIVITY_ID = p_x_task_Rec.unit_effectivity_id
713 CONNECT BY cost_parent_id = PRIOR visit_task_id
714 )
715 AND material_request_type = 'PLANNED';
716
717 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
718 fnd_log.string(fnd_log.level_statement, l_full_name, 'Number of ahl_schedule_materials records updated: ' || SQL%ROWCOUNT);
719 END IF;
720 UPDATE ahl_visit_tasks_b SET task_type_code = 'UNPLANNED'
721 WHERE visit_id = l_visit_id
722 AND visit_task_id IN
723 (
724 SELECT visit_task_id
725 FROM ahl_visit_tasks_b
726 WHERE visit_id = l_visit_id
727 START WITH mr_id = l_mr_header_id AND originating_task_id IS NULL
728 /*B6452310 - sowsubra - after the implementation of same mr added muliple times, assume a planned
729 requirement is added followed by an unplanned requirement. Then here all the tasks should not be
730 made Unplanned, the newly added tasks for the unplanned requirement should only be made unplanned
731 and which can be uniquely identified by the UE id generated.*/
732 AND UNIT_EFFECTIVITY_ID = p_x_task_Rec.unit_effectivity_id
733 CONNECT BY cost_parent_id = PRIOR visit_task_id
734 )
735 AND TASK_TYPE_CODE = 'PLANNED';
736
737 OPEN C_VISIT(l_visit_id);
738 FETCH c_visit into l_visit_csr_rec;
739 IF C_VISIT%FOUND AND l_visit_csr_rec.Any_Task_Chg_Flag='N' THEN
740 AHL_VWP_RULES_PVT.update_visit_task_flag(
741 p_visit_id =>l_visit_csr_rec.visit_id,
742 p_flag =>'Y',
743 x_return_status =>x_return_status);
744 END IF;
745 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
746 CLOSE C_VISIT;
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
749 CLOSE C_VISIT;
750 -- B6452310 - sowsubra
751 -- END IF;
752
753 ------------------------- finish -------------------------------
754 --
755 -- END of API body.
756 --
757 -- Standard check of p_commit.
758 IF Fnd_Api.To_Boolean ( p_commit ) THEN
759 COMMIT WORK;
760 END IF;
761
762 -- Standard call to get message count and if count is 1, get message info
763 Fnd_Msg_Pub.count_and_get(
764 p_encoded => Fnd_Api.g_false,
765 p_count => x_msg_count,
766 p_data => x_msg_data
767 );
768
769 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
770 fnd_log.string(fnd_log.level_procedure, l_full_name ||'.end', 'Exiting procedure');
771 END IF;
772
773 EXCEPTION
774 WHEN Fnd_Api.g_exc_error THEN
775 ROLLBACK TO Create_Unplanned_Task;
776 x_return_status := Fnd_Api.g_ret_sts_error;
777 Fnd_Msg_Pub.count_and_get(
778 p_encoded => Fnd_Api.g_false,
779 p_count => x_msg_count,
780 p_data => x_msg_data
781 );
782 WHEN Fnd_Api.g_exc_unexpected_error THEN
783 ROLLBACK TO Create_Unplanned_Task;
784 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
785 Fnd_Msg_Pub.count_and_get (
786 p_encoded => Fnd_Api.g_false,
787 p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790 WHEN OTHERS THEN
791 ROLLBACK TO Create_Unplanned_Task;
792 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
793 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
794 THEN
795 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
796 END IF;
797 Fnd_Msg_Pub.count_and_get (
798 p_encoded => Fnd_Api.g_false,
799 p_count => x_msg_count,
800 p_data => x_msg_data
801 );
802 END Create_Unplanned_Task;
803
804 --------------------------------------------------------------------
805 -- PROCEDURE
806 -- Update_Unplanned_Task
807 --
808 -- PURPOSE
809 -- To update Unplanned task for the Maintainance visit.
810 --------------------------------------------------------------------
811 PROCEDURE Update_Unplanned_Task (
812 p_api_version IN NUMBER,
813 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
814 p_commit IN VARCHAR2 := Fnd_Api.g_false,
815 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
816 p_module_type IN VARCHAR2 := 'JSP',
817
818 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
819 x_return_status OUT NOCOPY VARCHAR2,
820 x_msg_count OUT NOCOPY NUMBER,
821 x_msg_data OUT NOCOPY VARCHAR2
822 )
823 IS
824 L_API_VERSION CONSTANT NUMBER := 1.0;
825 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Unplanned_Task';
826 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
827
828 -- local variables defined for the procedure
829 l_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type := p_x_task_rec;
830 l_return_status VARCHAR2(1);
831 l_msg_data VARCHAR2(2000);
832
833 l_msg_count NUMBER;
834 l_count NUMBER;
835 l_cost_parent_id NUMBER;
836 l_department_id NUMBER;
837 l_planned_order_flag VARCHAR2(1);
838 l_entl_rec_tbl AHL_WARRANTY_ENTL_PVT.Warranty_Entl_Tbl_Type; --salogan added for supplier warranty
839
840 -- To find task related information
841 CURSOR c_Task (x_id IN NUMBER) IS
842 SELECT * FROM Ahl_Visit_Tasks_VL
843 WHERE VISIT_TASK_ID = x_id;
844 c_Task_rec c_Task%ROWTYPE;
845 c_upd_Task_rec c_Task%ROWTYPE;
846
847 -- To find visit related information
848 CURSOR c_Visit (x_id IN NUMBER) IS
849 SELECT * FROM Ahl_Visits_VL
850 WHERE VISIT_ID = x_id;
851 c_Visit_rec c_Visit%ROWTYPE;
852
853 BEGIN
854 --------------------- initialize -----------------------
855 SAVEPOINT Update_Unplanned_Task;
856
857 -- Debug info.
858 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
859 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************Start*************************');
860 END IF;
861
862 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
863 Fnd_Msg_Pub.initialize;
864 END IF;
865
866 -- Initialize API return status to success
867 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
868
869 -- Standard call to check for call compatibility.
870 IF NOT Fnd_Api.compatible_api_call(
871 l_api_version,
872 p_api_version,
873 l_api_name,
874 G_PKG_NAME
875 ) THEN
876 RAISE Fnd_Api.g_exc_unexpected_error;
877 END IF;
878
879 ------------------------Start of API Body------------------------------------
880 OPEN c_Visit(l_Task_rec.visit_id);
881 FETCH c_Visit INTO c_Visit_rec;
882 CLOSE c_Visit;
883
884 OPEN c_Task(l_Task_rec.visit_task_id);
885 FETCH c_Task INTO c_Task_rec;
886 CLOSE c_Task;
887
888 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
889 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Visit Id/Task Id = ' || l_task_rec.visit_id || '-' || l_task_rec.visit_task_id);
890 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Inventory Id /org/name =' || l_task_rec.inventory_item_id || '-' || l_task_rec.item_organization_id || '-' || l_task_rec.item_name);
891 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Cost Id -- Number=' || l_task_rec.cost_parent_id || '**' || l_task_rec.cost_parent_number );
892 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Originating Id/Number=' || l_task_rec.originating_task_id || '**' || l_task_rec.orginating_task_number);
893 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Object version number = ' || l_task_rec.object_version_number);
894 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Duration from record = ' || l_task_rec.duration);
895 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Visit start from hour/duration=' || '-' || l_task_rec.start_from_hour || '-' || l_task_rec.duration);
896 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Task Type code/value=' || l_task_rec.task_type_code || '-' || l_task_rec.task_type_value );
897 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': department_id = ' || l_task_rec.department_id );
898 END IF;
899
900 ----------- Start defining and validate all LOVs on Create Visit's Task UI Screen---
901 --
902 -- For DEPARTMENT
903 -- Convert department name to department id
904 IF (l_task_rec.dept_name IS NOT NULL AND l_task_rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
905
906 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
907 (p_organization_id => c_visit_rec.organization_id,
908 p_dept_name => l_task_rec.dept_name,
909 p_department_id => NULL,
910 x_department_id => l_department_id,
911 x_return_status => l_return_status,
912 x_error_msg_code => l_msg_data);
913
914 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
915 THEN
916 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
917 Fnd_Msg_Pub.ADD;
918 RAISE Fnd_Api.G_EXC_ERROR;
919 END IF;
920
921 --Assign the returned value
922 l_task_rec.department_id := l_department_id;
923 END IF;
924
925 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
926 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Dept ID= ' || l_Task_rec.department_id );
927 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Cost parent= ' || l_Task_rec.cost_parent_number);
928 END IF;
929
930 --
931 -- For COST PARENT TASK
932 -- Convert cost parent number to id
933 IF (l_Task_rec.cost_parent_number IS NOT NULL AND
934 l_Task_rec.cost_parent_number <> Fnd_Api.G_MISS_NUM ) THEN
935
936 AHL_VWP_RULES_PVT.Check_Visit_Task_Number_OR_ID
937 (p_visit_task_id => l_Task_rec.cost_parent_id,
938 p_visit_task_number => l_Task_rec.cost_parent_number,
939 p_visit_id => l_Task_rec.visit_id,
940 x_visit_task_id => l_cost_parent_id,
941 x_return_status => l_return_status,
942 x_error_msg_code => l_msg_data);
943
944 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
945 THEN
946 Fnd_Message.SET_NAME('AHL','AHL_VWP_PARENT_NOT_EXISTS');
947 Fnd_Msg_Pub.ADD;
948 RAISE Fnd_Api.g_exc_error;
949 END IF;
950
951 --Assign the returned value
952 l_Task_rec.cost_parent_id := l_cost_parent_id;
953 END IF;
954
955 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
956 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Cost parent ID = ' || l_Task_rec.cost_parent_id);
957 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Validation: Start -- For COST PARENT ');
958 END IF;
959
960 -- To Check for cost parent task id not forming loop
961 IF (l_Task_rec.cost_parent_id IS NOT NULL AND
962 l_Task_rec.cost_parent_id <> Fnd_Api.G_MISS_NUM ) THEN
963
964 AHL_VWP_RULES_PVT.Check_Cost_Parent_Loop
965 (p_visit_id => l_Task_rec.visit_id,
966 p_visit_task_id => l_Task_rec.visit_task_id ,
967 p_cost_parent_id => l_Task_rec.cost_parent_id
968 );
969
970 END IF;
971
972 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
973 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Validation: End -- For COST PARENT ');
974 END IF;
975
976 ----------- End defining and validate all LOVs on Create Visit's Task UI Screen---
977
978
979 ----------------------- validate ----------------------
980
981 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
982 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':Validate');
983 END IF;
984
985 -- For all optional fields check if its g_miss_num/g_miss_char/g_miss_date
986 -- then Null else the value call Default_Missing_Attribs procedure
987 Default_Missing_Attribs
988 (
989 p_x_task_rec => l_Task_rec
990 );
991
992 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
993 Check_Task_Items (
994 p_task_rec => p_x_task_rec,
995 p_validation_mode => Jtf_Plsql_Api.g_update,
996 x_return_status => l_return_status
997 );
998 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
999 RAISE Fnd_Api.g_exc_unexpected_error;
1000 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1001 RAISE Fnd_Api.g_exc_error;
1002 END IF;
1003 END IF;
1004
1005 -- Check Object version number.
1006 IF (c_task_rec.object_version_number <> l_task_rec.object_version_number) THEN
1007 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1008 Fnd_Msg_Pub.ADD;
1009 RAISE Fnd_Api.G_EXC_ERROR;
1010 END IF;
1011
1012 -- Post 11.5.10 Changes by Senthil.
1013 IF(L_task_rec.STAGE_ID IS NOT NULL OR L_task_rec.STAGE_NAME IS NOT NULL) THEN
1014 AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
1015 P_API_VERSION => 1.0,
1016 P_VISIT_ID => l_Task_rec.visit_id,
1017 P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
1018 P_STAGE_NAME => L_task_rec.STAGE_NAME,
1019 X_STAGE_ID => L_task_rec.STAGE_ID,
1020 X_RETURN_STATUS => l_return_status,
1021 X_MSG_COUNT => l_msg_count,
1022 X_MSG_DATA => l_msg_data );
1023
1024 END IF;
1025
1026 --Standard check to count messages
1027 l_msg_count := Fnd_Msg_Pub.count_msg;
1028
1029 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1030 X_msg_count := l_msg_count;
1031 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1032 RAISE Fnd_Api.G_EXC_ERROR;
1033 END IF;
1034
1035 -------------------------- update --------------------
1036 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1037 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':Update');
1038 END IF;
1039
1040 --Modified by mpothuku to fix LTP forum issue #208 on 04/19/05
1041 IF( nvl(p_module_type,'XXX') <> 'LTP') THEN
1042 l_task_rec.originating_task_id := c_task_rec.originating_task_id;
1043 END IF;
1044 --End mpothuku
1045
1046 Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
1047 X_VISIT_TASK_ID => l_task_rec.visit_task_id,
1048 X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
1049 X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
1050 X_VISIT_ID => l_task_rec.visit_id,
1051 X_PROJECT_TASK_ID => c_task_rec.project_task_id,
1052 X_COST_PARENT_ID => l_task_rec.cost_parent_id,
1053 X_MR_ROUTE_ID => c_task_rec.mr_route_id,
1054 X_MR_ID => c_task_rec.mr_id,
1055 X_DURATION => c_task_rec.duration,
1056 X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
1057 X_START_FROM_HOUR => l_task_rec.start_from_hour,
1058 X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
1059 X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
1060 X_INSTANCE_ID => c_Task_rec.instance_id,
1061 X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
1062 X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id, --c_task_rec.originating_task_id,
1063 X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
1064 X_TASK_TYPE_CODE => l_task_rec.task_type_code,
1065 -- AVIKUKUM :: FP:PIE ::14-OCT-2010 :: update Service Type code too
1066 X_SERVICE_TYPE_CODE => l_task_rec.service_type_code,
1067 X_DEPARTMENT_ID => l_task_rec.department_id,
1068 X_SUMMARY_TASK_FLAG => 'N',
1069 X_PRICE_LIST_ID => c_task_rec.price_list_id,
1070 X_STATUS_CODE => c_task_rec.status_code,
1071 X_ESTIMATED_PRICE => c_task_rec.estimated_price,
1072 X_ACTUAL_PRICE => c_task_rec.actual_price,
1073 X_ACTUAL_COST => c_task_rec.actual_cost,
1074 -- Changes for 11.5.10 by Senthil.
1075 X_STAGE_ID => l_task_rec.STAGE_ID,
1076 -- Added cxcheng POST11510--------------
1077 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010
1078 -- Pass past dates too for the below 4 columns, and if it is null, pass null for all the 4 columns
1079 X_START_DATE_TIME => NVL(l_task_rec.PAST_TASK_START_DATE, c_task_rec.START_DATE_TIME), --SKPATHAK :: Bug 13890788
1080 X_END_DATE_TIME => NVL(l_task_rec.PAST_TASK_END_DATE,c_task_rec.END_DATE_TIME), --SKPATHAK :: Bug 13890788
1081 X_PAST_TASK_START_DATE => l_task_rec.PAST_TASK_START_DATE,
1082 X_PAST_TASK_END_DATE => l_task_rec.PAST_TASK_END_DATE,
1083 --MANESING::Component Maintenance Planning Project, 07-Aug-2011, added target quantity
1084 X_TARGET_QTY => c_task_rec.target_qty,
1085
1086 -- manisaga commented the attributes loading from c_task_rec and added from
1087 -- l_task_rec for DFF implementation on 19-Feb-2010 --Start
1088 /*
1089 X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
1090 X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
1091 X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
1092 X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
1093 X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
1094 X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
1095 X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
1096 X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
1097 X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
1098 X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
1099 X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
1100 X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
1101 X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
1102 X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
1103 X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
1104 X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
1105 */
1106 X_ATTRIBUTE_CATEGORY => l_task_rec.ATTRIBUTE_CATEGORY,
1107 X_ATTRIBUTE1 => l_task_rec.ATTRIBUTE1,
1108 X_ATTRIBUTE2 => l_task_rec.ATTRIBUTE2,
1109 X_ATTRIBUTE3 => l_task_rec.ATTRIBUTE3,
1110 X_ATTRIBUTE4 => l_task_rec.ATTRIBUTE4,
1111 X_ATTRIBUTE5 => l_task_rec.ATTRIBUTE5,
1112 X_ATTRIBUTE6 => l_task_rec.ATTRIBUTE6,
1113 X_ATTRIBUTE7 => l_task_rec.ATTRIBUTE7,
1114 X_ATTRIBUTE8 => l_task_rec.ATTRIBUTE8,
1115 X_ATTRIBUTE9 => l_task_rec.ATTRIBUTE9,
1116 X_ATTRIBUTE10 => l_task_rec.ATTRIBUTE10,
1117 X_ATTRIBUTE11 => l_task_rec.ATTRIBUTE11,
1118 X_ATTRIBUTE12 => l_task_rec.ATTRIBUTE12,
1119 X_ATTRIBUTE13 => l_task_rec.ATTRIBUTE13,
1120 X_ATTRIBUTE14 => l_task_rec.ATTRIBUTE14,
1121 X_ATTRIBUTE15 => l_task_rec.ATTRIBUTE15,
1122 -- manisaga commented the attributes loading from c_task_rec and added from
1123 -- l_task_rec for DFF implementation on 19-Feb-2010 --End
1124
1125 X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
1126 X_DESCRIPTION => l_task_rec.description,
1127 X_QUANTITY => c_task_rec.QUANTITY, -- Added by rnahata for Issue 105
1128 X_LAST_UPDATE_DATE => SYSDATE,
1129 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1130 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
1131
1132 --salogan added for supplier warranty starts
1133 -- calling AHL_VWP_RULES_PVT.Get_Warranty_Rec_for_Update
1134 AHL_VWP_RULES_PVT.Get_Warranty_Rec_for_Update(
1135 p_task_rec => l_task_rec,
1136 x_warranty_entl_tbl => l_entl_rec_tbl);
1137
1138 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1139 fnd_log.string(fnd_log.level_statement,
1140 'ahl.plsql.'||l_full_name,
1141 'Before calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements.');
1142 END IF;
1143
1144 -- calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements
1145 AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements(
1146 p_user_role => AHL_WARRANTY_ENTL_PVT.G_USER_PLANNER,
1147 p_x_warranty_entl_tbl => l_entl_rec_tbl,
1148 x_return_status => l_return_status,
1149 x_msg_count => l_msg_count,
1150 x_msg_data => l_msg_data);
1151
1152 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1153 fnd_log.string(fnd_log.level_statement,
1154 'ahl.plsql.'||l_full_name ,
1155 'After calling AHL_WARRANTY_ENTL_PVT.Process_Warranty_Entitlements. Return Status = ' ||
1156 l_return_status);
1157 END IF;
1158
1159 l_msg_count := Fnd_Msg_Pub.count_msg;
1160 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1161 x_msg_count := l_msg_count;
1162 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1163 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1164 END IF;
1165 --salogan added for supplier warranty ends
1166
1167 -- Assign back to in/out parameter
1168 p_x_task_rec := l_task_rec;
1169
1170 ------------------------End of API Body------------------------------------
1171 -- Added cxcheng POST11510--------------
1172 --Now adjust the times derivation for task
1173
1174 -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 ::Call Adjust_Task_Times only if past date is null
1175 IF l_task_rec.PAST_TASK_START_DATE IS NULL THEN
1176 AHL_VWP_TIMES_PVT.Adjust_Task_Times(p_api_version => 1.0,
1177 p_init_msg_list => Fnd_Api.G_FALSE,
1178 p_commit => Fnd_Api.G_FALSE,
1179 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1180 x_return_status => l_return_status,
1181 x_msg_count => l_msg_count,
1182 x_msg_data => l_msg_data,
1183 p_task_id => l_task_rec.visit_task_id);
1184 END IF;
1185
1186 --Standard check to count messages
1187 l_msg_count := Fnd_Msg_Pub.count_msg;
1188
1189 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1190 x_msg_count := l_msg_count;
1191 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1192 RAISE Fnd_Api.G_EXC_ERROR;
1193 END IF;
1194
1195
1196 -- To Update visit attribute any_task_chg_flag for costing purpose
1197 -- Looking for changes in 'Start from hour' attributes of task
1198
1199 IF NVL(l_task_rec.Start_from_hour,-30) <> NVL(c_task_rec.Start_from_hour,-30) OR
1200 NVL(l_task_rec.STAGE_ID,-30) <> NVL(c_task_rec.STAGE_ID,-30) OR
1201 NVL(l_task_rec.department_id,-30) <> NVL(c_task_rec.department_id,-30) THEN
1202
1203 OPEN c_Task(l_Task_rec.visit_task_id);
1204 FETCH c_Task INTO c_upd_Task_rec;
1205 CLOSE c_Task;
1206
1207 IF c_upd_Task_rec.start_date_time IS NOT NULL THEN
1208
1209 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
1210 p_api_version => 1.0,
1211 p_init_msg_list => FND_API.g_false,
1212 p_commit => FND_API.g_false,
1213 p_validation_level => FND_API.g_valid_level_full,
1214 p_visit_id => l_task_rec.visit_id,
1215 p_visit_task_id => NULL,
1216 p_org_id => NULL,
1217 p_start_date => NULL,
1218 p_operation_flag => 'U',
1219 x_planned_order_flag => l_planned_order_flag ,
1220 x_return_status => l_return_status,
1221 x_msg_count => l_msg_count,
1222 x_msg_data => l_msg_data );
1223
1224 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1225 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||'After AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
1226 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.',l_full_name ||'Planned Order Flag : ' || l_planned_order_flag);
1227 END IF;
1228
1229 IF l_return_status <> 'S' THEN
1230 RAISE Fnd_Api.G_EXC_ERROR;
1231 END IF;
1232
1233 END IF; -- Start_date_time check.
1234
1235 IF c_visit_rec.any_task_chg_flag = 'N' THEN
1236 AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
1237 (p_visit_id => l_task_rec.visit_id,
1238 p_flag => 'Y',
1239 x_return_status => x_return_status);
1240 END IF;
1241
1242 END IF;
1243
1244 --Standard check to count messages
1245 l_msg_count := Fnd_Msg_Pub.count_msg;
1246
1247 IF l_msg_count > 0 THEN
1248 X_msg_count := l_msg_count;
1249 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1250 RAISE Fnd_Api.G_EXC_ERROR;
1251 END IF;
1252
1253 --Standard check for commit
1254 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1255 COMMIT;
1256 END IF;
1257
1258 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1259 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************END*************************');
1260 END IF;
1261
1262
1263 EXCEPTION
1264 WHEN Fnd_Api.g_exc_error THEN
1265 ROLLBACK TO Update_Unplanned_Task;
1266 x_return_status := Fnd_Api.g_ret_sts_error;
1267 Fnd_Msg_Pub.count_and_get (
1268 p_encoded => Fnd_Api.g_false,
1269 p_count => x_msg_count,
1270 p_data => x_msg_data
1271 );
1272 WHEN Fnd_Api.g_exc_unexpected_error THEN
1273 ROLLBACK TO Update_Unplanned_Task;
1274 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1275 Fnd_Msg_Pub.count_and_get (
1276 p_encoded => Fnd_Api.g_false,
1277 p_count => x_msg_count,
1278 p_data => x_msg_data
1279 );
1280 WHEN OTHERS THEN
1281 ROLLBACK TO Update_Unplanned_Task;
1282 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1283 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1284 THEN
1285 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1286 END IF;
1287 Fnd_Msg_Pub.count_and_get (
1288 p_encoded => Fnd_Api.g_false,
1289 p_count => x_msg_count,
1290 p_data => x_msg_data
1291 );
1292 END Update_Unplanned_Task;
1293
1294 --------------------------------------------------------------------
1295 -- PROCEDURE
1296 -- Delete_Unplanned_Task
1297 --
1298 -- PURPOSE
1299 -- To delete Unplanned tasks for the Maintenace visit.
1300 -- Modifying the Unplanned tasks for costing by rtadikon
1301 --------------------------------------------------------------------
1302 PROCEDURE Delete_Unplanned_Task (
1303 p_api_version IN NUMBER,
1304 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1305 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1306 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1307 p_module_type IN VARCHAR2 := 'JSP',
1308 p_visit_task_ID IN NUMBER,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1311 x_msg_data OUT NOCOPY VARCHAR2
1312 )
1313
1314 IS
1315
1316 -- local variables defined for the procedure
1317 l_api_version CONSTANT NUMBER := 1.0;
1318 l_api_name CONSTANT VARCHAR2(30) := 'Delete Unplanned Task';
1319 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1320 l_origin_id NUMBER;
1321 l_msg_count NUMBER;
1322
1323 -- To find all tasks related information
1324 CURSOR c_Task (x_id IN NUMBER) IS
1325 SELECT * FROM Ahl_Visit_Tasks_VL
1326 WHERE Visit_Task_ID = x_id;
1327 c_task_rec c_Task%ROWTYPE;
1328
1329 BEGIN
1330 --------------------- initialize -----------------------
1331 SAVEPOINT Delete_Unplanned_Task;
1332
1333 -- Check if API is called in debug mode. If yes, enable debug.
1334 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1335 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************START*************************');
1336 END IF;
1337
1338 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1339 Fnd_Msg_Pub.initialize;
1340 END IF;
1341
1342 -- Initialize API return status to success
1343 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1344
1345 -- Standard call to check for call compatibility.
1346 IF NOT Fnd_Api.compatible_api_call(
1347 l_api_version,
1348 p_api_version,
1349 l_api_name,
1350 G_PKG_NAME) THEN
1351 RAISE Fnd_Api.g_exc_unexpected_error;
1352 END IF;
1353
1354 ------------------------Start of API Body------------------------------------
1355 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1356 THEN
1357 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':Task Id' || p_visit_task_ID);
1358 END IF;
1359
1360 -- To check if the input taskid exists in task entity.
1361
1362 OPEN c_Task(p_Visit_Task_ID);
1363 FETCH c_Task INTO c_task_rec;
1364
1365 IF c_Task%NOTFOUND THEN
1366 CLOSE c_Task;
1367 Fnd_Message.set_name('AHL', 'AHL_VWP_TASK_ID_INVALID');
1368 FND_MESSAGE.SET_TOKEN('TASK_ID',p_visit_task_id,false);
1369 Fnd_Msg_Pub.ADD;
1370 RAISE Fnd_Api.g_exc_error;
1371 ELSE
1372 CLOSE c_Task;
1373
1374 l_origin_id:= c_task_rec.originating_task_id;
1375
1376 IF l_origin_id IS NOT NULL THEN
1377
1378 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1379 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', ' Before Call to Delete Summary task ');
1380 END IF;
1381
1382 AHL_VWP_TASKS_PVT.Delete_Summary_Task(
1383 p_api_version => p_api_version,
1384 p_init_msg_list => Fnd_Api.g_false,
1385 p_commit => Fnd_Api.g_false,
1386 p_validation_level => Fnd_Api.g_valid_level_full,
1387 p_module_type => NULL,
1388 p_Visit_Task_Id => l_origin_id,
1389 x_return_status => x_return_status,
1390 x_msg_count => x_msg_count,
1391 x_msg_data => x_msg_data
1392 );
1393
1394 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1395 RAISE Fnd_Api.G_EXC_ERROR;
1396 END IF;
1397
1398 Else
1399 Fnd_Message.SET_NAME('AHL','AHL_VWP_UNPLANNEDTASKMR');
1400 Fnd_Msg_Pub.ADD;
1401 RAISE Fnd_Api.G_EXC_ERROR;
1402 --Displays 'Unplanned Task association to Maintenance Requirement is missing.'
1403 End IF;
1404
1405 END IF;
1406
1407 /* Commented by mpothuku on 02/25/05 as this is moved to ahl_vwp_tasks_pvt.Delete_Summary_Task
1408
1409 AHL_UMP_UNPLANNED_PVT.DELETE_UNIT_EFFECTIVITY(
1410 P_API_VERSION => 1.0,
1411 X_RETURN_STATUS => x_return_status,
1412 X_MSG_COUNT => x_msg_count,
1413 X_MSG_DATA => x_msg_data,
1414 P_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id
1415 );
1416 */
1417
1418 l_msg_count := Fnd_Msg_Pub.count_msg;
1419
1420 IF l_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421 X_msg_count := l_msg_count;
1422 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1423
1424 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1425 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', ' Error Before Commit---> After Delete Summary task call');
1426 END IF;
1427
1428 RAISE Fnd_Api.G_EXC_ERROR;
1429 END IF;
1430
1431
1432 ------------------------End of API Body------------------------------------
1433 IF Fnd_Api.to_boolean (p_commit) THEN
1434 COMMIT;
1435 END IF;
1436
1437 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1438 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************END*************************');
1439 END IF;
1440 EXCEPTION
1441 WHEN Fnd_Api.g_exc_error THEN
1442 ROLLBACK TO Delete_Unplanned_Task;
1443 x_return_status := Fnd_Api.g_ret_sts_error;
1444 Fnd_Msg_Pub.count_and_get (
1445 p_encoded => Fnd_Api.g_false,
1446 p_count => x_msg_count,
1447 p_data => x_msg_data
1448 );
1449
1450 WHEN Fnd_Api.g_exc_unexpected_error THEN
1451 ROLLBACK TO Delete_Unplanned_Task;
1452 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1453 Fnd_Msg_Pub.count_and_get (
1454 p_encoded => Fnd_Api.g_false,
1455 p_count => x_msg_count,
1456 p_data => x_msg_data
1457 );
1458
1459 WHEN OTHERS THEN
1460 ROLLBACK TO Delete_Unplanned_Task;
1461 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1462 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1463 THEN
1464 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1465 END IF;
1466 Fnd_Msg_Pub.count_and_get (
1467 p_encoded => Fnd_Api.g_false,
1468 p_count => x_msg_count,
1469 p_data => x_msg_data
1470 );
1471
1472 END Delete_Unplanned_Task;
1473
1474 ---------------------------------------------------------------------
1475 -- PROCEDURE
1476 -- Check_Task_Items
1477 --
1478 -- PURPOSE
1479 --
1480 ---------------------------------------------------------------------
1481 PROCEDURE Check_Task_Items (
1482 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1483 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1484
1485 x_return_status OUT NOCOPY VARCHAR2
1486 )
1487 IS
1488 BEGIN
1489 --
1490 -- Validate required items.
1491
1492 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1493
1494 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1495 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Task_Items' || ':Before Check_Visit_Task_Req_Items');
1496 END IF;
1497 Check_Visit_Task_Req_Items (
1498 p_task_rec => p_task_rec,
1499 x_return_status => x_return_status
1500 );
1501 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1502 RETURN;
1503 END IF;
1504 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1505 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', 'Check_Task_Items' || ':After Check_Visit_Task_Req_Items');
1506 END IF;
1507
1508 --
1509 -- Validate uniqueness.
1510 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1511 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME, 'Check_Task_Items' ||':Before Check_Visit_Task_UK_Items');
1512 END IF;
1513 Check_Visit_Task_UK_Items (
1514 p_task_rec => p_task_rec,
1515 p_validation_mode => p_validation_mode,
1516 x_return_status => x_return_status
1517 );
1518 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1519 RETURN;
1520 END IF;
1521 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1522 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME, 'Check_Task_Items' ||':Before Check_Visit_Task_UK_Items');
1523 END IF;
1524 END Check_Task_Items;
1525
1526 ---------------------------------------------------------------------
1527 -- PROCEDURE
1528 -- Complete_Visit_Task_Rec
1529 --
1530 -- PURPOSE
1531 --
1532 ---------------------------------------------------------------------
1533 /* Commented
1534 PROCEDURE Complete_Visit_Task_Rec (
1535 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1536 x_complete_rec OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
1537 )
1538 IS
1539 CURSOR c_Visit_Task IS
1540 SELECT *
1541 FROM Ahl_Visit_Tasks_vl
1542 WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;
1543 --
1544 -- This is the only exception for using %ROWTYPE.
1545 -- We are selecting from the VL view, which may
1546 -- have some denormalized columns as compared to
1547 -- the base tables.
1548 l_task_rec c_Visit_Task%ROWTYPE;
1549 BEGIN
1550 x_complete_rec := p_task_rec;
1551 OPEN c_Visit_Task;
1552 FETCH c_Visit_Task INTO l_task_rec;
1553 IF c_Visit_Task%NOTFOUND THEN
1554 CLOSE c_Visit_Task;
1555 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
1556 Fnd_Msg_Pub.ADD;
1557 RAISE Fnd_Api.g_exc_error;
1558 END IF;
1559 CLOSE c_Visit_Task;
1560
1561 END Complete_Visit_Task_Rec;
1562 */
1563
1564 ---------------------------------------------------------------------
1565 -- PROCEDURE
1566 -- Check_Visit_Task_Req_Items
1567 --
1568 -- PURPOSE
1569 --
1570 ---------------------------------------------------------------------
1571 PROCEDURE Check_Visit_Task_Req_Items (
1572 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1573 x_return_status OUT NOCOPY VARCHAR2
1574 )
1575 IS
1576
1577 BEGIN
1578
1579 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1580 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1581 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Visit_Task_Req_Items: = Start Check_Visit_Task_Req_Items ');
1582 END IF;
1583 -- TASK NAME ==== NAME
1584 IF (p_task_rec.VISIT_TASK_NAME IS NULL OR p_Task_rec.VISIT_TASK_NAME = Fnd_Api.G_MISS_CHAR) THEN
1585 Fnd_Message.set_name ('AHL', 'AHL_VWP_NAME_MISSING');
1586 Fnd_Msg_Pub.ADD;
1587 x_return_status := Fnd_Api.g_ret_sts_error;
1588 RETURN;
1589 END IF;
1590 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1591 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Visit_Task_Req_Items:Inside Validation Start from Hour = ' || p_task_rec.START_FROM_HOUR);
1592 END IF;
1593 IF (p_task_rec.START_FROM_HOUR IS NOT NULL and p_Task_rec.START_FROM_HOUR <> Fnd_Api.G_MISS_NUM) THEN
1594 IF p_task_rec.START_FROM_HOUR < 0 OR FLOOR(p_task_rec.START_FROM_HOUR) <> p_task_rec.START_FROM_HOUR THEN
1595 Fnd_Message.set_name ('AHL', 'AHL_VWP_ONLY_POSITIVE_VALUE');
1596 Fnd_Msg_Pub.ADD;
1597 x_return_status := Fnd_Api.g_ret_sts_error;
1598 RETURN;
1599 END IF;
1600 END IF;
1601
1602 END Check_Visit_Task_Req_Items;
1603
1604 ---------------------------------------------------------------------
1605 -- PROCEDURE
1606 -- Check_Visit_Task_UK_Items
1607 --
1608 -- PURPOSE
1609 --
1610 ---------------------------------------------------------------------
1611 PROCEDURE Check_Visit_Task_UK_Items (
1612 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1613 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1614 x_return_status OUT NOCOPY VARCHAR2
1615 )
1616 IS
1617 l_valid_flag VARCHAR2(1);
1618
1619 BEGIN
1620 x_return_status := Fnd_Api.g_ret_sts_success;
1621 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1622 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Visit_Task_UK_Items: = Start Check_Visit_Task_UK_Items ');
1623 END IF;
1624 --
1625 -- For Task, when ID is passed in, we need to
1626 -- check if this ID is unique.
1627 IF p_validation_mode = Jtf_Plsql_Api.g_create AND p_task_rec.Visit_Task_ID IS NOT NULL THEN
1628 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1629 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Visit_Task_UK_Items : = Check_Visit_Task_UK_Items Uniqueness Of ID');
1630 END IF;
1631 -- FOR CREATION
1632 IF Ahl_Utility_Pvt.check_uniqueness(
1633 'Ahl_Visit_Tasks_vl',
1634 'Visit_Task_ID = ' || p_task_rec.Visit_Task_ID
1635 ) = Fnd_Api.g_false
1636 THEN
1637 Fnd_Message.set_name ('AHL', 'AHL_VWP_DUPLICATE_TASK_ID');
1638 Fnd_Msg_Pub.ADD;
1639 x_return_status := Fnd_Api.g_ret_sts_error;
1640 RETURN;
1641 END IF;
1642 END IF;
1643
1644 END Check_Visit_Task_UK_Items;
1645
1646 END Ahl_Vwp_Unplan_Tasks_Pvt;