[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.6.12010000.3 2008/10/29 07:39:31 skpathak 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 --B6452310 - sowsubra - commented to allow duplicate MR's in a visit
655 --Begin Comment out
656 /***
657 OPEN c_MR_Visit (l_visit_id,l_mr_header_id,l_serial_id);
658 FETCH c_MR_Visit INTO c_MR_Visit_rec;
659
660 IF c_MR_Visit%FOUND THEN
661 -- ERROR MESSAGE
662 x_return_status := Fnd_Api.g_ret_sts_error;
663 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_FOUND');
664 Fnd_Message.SET_TOKEN('MR_TITLE', c_MR_Visit_rec.Title);
665 Fnd_Msg_Pub.ADD;
666 CLOSE c_MR_Visit;
667 ***/
668 --End Comment out
669
670 /* OPEN c_MR_Visit (l_visit_id,l_mr_header_id,l_serial_id);
671 FETCH c_MR_Visit INTO l_count;
672 CLOSE c_MR_Visit;
673
674 IF l_count > 0 THEN
675 -- ERROR MESSAGE
676 x_return_status := Fnd_Api.g_ret_sts_error;
677 Fnd_Message.SET_NAME('AHL','AHL_VWP_MR_FOUND');
678 Fnd_Msg_Pub.ADD;
679 */
680 --B6452310 - sowsubra
681 -- ELSE
682 -- CLOSE c_MR_Visit;
683
684 -- yazhou 15Nov2005 starts
685 -- Code clean up
686 /*
687 ----------------------------------------------------------------------------------------------------------
688 -- FOR MAIN MR HEADER ID'S
689 -- To store all MR Headers in table datatype
690 i := 0;
691 MR_Id_Tbl(i):= l_mr_header_id;
692 i := i + 1;
693 j :=0;
694 ----------------------------------------------------------------------------------------------------------
695 --Do breadth 1st iterative fetch of child MRs. This is because we can not
696 -- do a join of the MR relationships tree with the connect by clause.
697 WHILE (j < i) LOOP
698 OPEN get_child_mrs_csr(MR_Id_Tbl(j));
699 <<l_inner_loop>>
700 LOOP
701 --Add new childs to the end of the mr id table
702 FETCH get_child_mrs_csr INTO MR_Id_Tbl(i);
703 EXIT l_inner_loop WHEN get_child_mrs_csr%NOTFOUND;
704 i:=i+1;
705 END LOOP l_inner_loop;
706 CLOSE get_child_mrs_csr;
707 j:=j+1;
708 END LOOP;
709 ----------------------------------------------------------------------------------------------------------
710 */
711 -- yazhou 15Nov2005 ends
712
713 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
714 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);
715 END IF;
716
717 AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY(
718 p_api_version => 1.0,
719 x_return_status => l_return_status,
720 x_msg_count => l_msg_count,
721 x_msg_data => l_msg_data,
722 p_mr_header_id => l_mr_header_id,
723 p_instance_id => l_instance_id,
724 x_orig_ue_id => l_ue_id);
725
726 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
727 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);
728 END IF;
729
730 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
731 X_msg_count := l_msg_count;
732 X_return_status := Fnd_Api.G_RET_STS_ERROR;
733 RAISE Fnd_Api.G_EXC_ERROR;
734 END IF;
735
736
737 p_x_task_Rec.task_type_code :='UNPLANNED';
738 p_x_task_Rec.unit_effectivity_id :=l_ue_id;
739
740 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
741 fnd_log.string(fnd_log.level_statement, l_full_name, 'About to call AHL_VWP_PLAN_TASKS_PVT.CREATE_PLANNED_TASK');
742 END IF;
743
744 AHL_VWP_PLAN_TASKS_PVT.CREATE_PLANNED_TASK(
745 p_api_version => 1.0,
746 p_x_task_rec => p_x_task_Rec,
747 x_return_status => l_return_status,
748 x_msg_count => l_msg_count,
749 x_msg_data => l_msg_data);
750
751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
752 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);
753 END IF;
754
755 IF l_msg_count > 0 THEN
756 X_msg_count := l_msg_count;
757 X_return_status := Fnd_Api.G_RET_STS_ERROR;
758 RAISE Fnd_Api.G_EXC_ERROR;
759 END IF;
760
761 UPDATE ahl_visit_tasks_b SET task_type_code = 'UNPLANNED'
762 WHERE visit_id = l_visit_id
763 AND visit_task_id IN
764 (
765 SELECT visit_task_id
766 FROM ahl_visit_tasks_b
767 WHERE visit_id = l_visit_id
768 START WITH mr_id = l_mr_header_id AND originating_task_id IS NULL
769 /*B6452310 - sowsubra - after the implementation of same mr added muliple times, assume a planned
770 requirement is added followed by an unplanned requirement. Then here all the tasks should not be
771 made Unplanned, the newly added tasks for the unplanned requirement should only be made unplanned
772 and which can be uniquely identified by the UE id generated.*/
773 AND UNIT_EFFECTIVITY_ID = p_x_task_Rec.unit_effectivity_id
774 CONNECT BY cost_parent_id = PRIOR visit_task_id
775 )
776 AND TASK_TYPE_CODE = 'PLANNED';
777
778 OPEN C_VISIT(l_visit_id);
779 FETCH c_visit into l_visit_csr_rec;
780 IF C_VISIT%FOUND AND l_visit_csr_rec.Any_Task_Chg_Flag='N' THEN
781 AHL_VWP_RULES_PVT.update_visit_task_flag(
782 p_visit_id =>l_visit_csr_rec.visit_id,
783 p_flag =>'Y',
784 x_return_status =>x_return_status);
785 END IF;
786 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
787 CLOSE C_VISIT;
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790 CLOSE C_VISIT;
791 -- B6452310 - sowsubra
792 -- END IF;
793
794 ------------------------- finish -------------------------------
795 --
796 -- END of API body.
797 --
798 -- Standard check of p_commit.
799 IF Fnd_Api.To_Boolean ( p_commit ) THEN
800 COMMIT WORK;
801 END IF;
802
803 -- Standard call to get message count and if count is 1, get message info
804 Fnd_Msg_Pub.count_and_get(
805 p_encoded => Fnd_Api.g_false,
806 p_count => x_msg_count,
807 p_data => x_msg_data
808 );
809
810 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
811 fnd_log.string(fnd_log.level_procedure, l_full_name ||'.end', 'Exiting procedure');
812 END IF;
813
814 EXCEPTION
815 WHEN Fnd_Api.g_exc_error THEN
816 ROLLBACK TO Create_Unplanned_Task;
817 x_return_status := Fnd_Api.g_ret_sts_error;
818 Fnd_Msg_Pub.count_and_get(
819 p_encoded => Fnd_Api.g_false,
820 p_count => x_msg_count,
821 p_data => x_msg_data
822 );
823 WHEN Fnd_Api.g_exc_unexpected_error THEN
824 ROLLBACK TO Create_Unplanned_Task;
825 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
826 Fnd_Msg_Pub.count_and_get (
827 p_encoded => Fnd_Api.g_false,
828 p_count => x_msg_count,
829 p_data => x_msg_data
830 );
831 WHEN OTHERS THEN
832 ROLLBACK TO Create_Unplanned_Task;
833 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
834 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
835 THEN
836 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
837 END IF;
838 Fnd_Msg_Pub.count_and_get (
839 p_encoded => Fnd_Api.g_false,
840 p_count => x_msg_count,
841 p_data => x_msg_data
842 );
843 END Create_Unplanned_Task;
844
845 --------------------------------------------------------------------
846 -- PROCEDURE
847 -- Update_Unplanned_Task
848 --
849 -- PURPOSE
850 -- To update Unplanned task for the Maintainance visit.
851 --------------------------------------------------------------------
852 PROCEDURE Update_Unplanned_Task (
853 p_api_version IN NUMBER,
854 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
855 p_commit IN VARCHAR2 := Fnd_Api.g_false,
856 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
857 p_module_type IN VARCHAR2 := 'JSP',
858
859 p_x_task_rec IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type,
860 x_return_status OUT NOCOPY VARCHAR2,
861 x_msg_count OUT NOCOPY NUMBER,
862 x_msg_data OUT NOCOPY VARCHAR2
863 )
864 IS
865 L_API_VERSION CONSTANT NUMBER := 1.0;
866 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Unplanned_Task';
867 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
868
869 -- local variables defined for the procedure
870 l_task_rec AHL_VWP_RULES_PVT.Task_Rec_Type := p_x_task_rec;
871 l_return_status VARCHAR2(1);
872 l_msg_data VARCHAR2(2000);
873
874 l_msg_count NUMBER;
875 l_count NUMBER;
876 l_cost_parent_id NUMBER;
877 l_department_id NUMBER;
878 l_planned_order_flag VARCHAR2(1);
879
880 -- To find task related information
881 CURSOR c_Task (x_id IN NUMBER) IS
882 SELECT * FROM Ahl_Visit_Tasks_VL
883 WHERE VISIT_TASK_ID = x_id;
884 c_Task_rec c_Task%ROWTYPE;
885 c_upd_Task_rec c_Task%ROWTYPE;
886
887 -- To find visit related information
888 CURSOR c_Visit (x_id IN NUMBER) IS
889 SELECT * FROM Ahl_Visits_VL
890 WHERE VISIT_ID = x_id;
891 c_Visit_rec c_Visit%ROWTYPE;
892
893 BEGIN
894 --------------------- initialize -----------------------
895 SAVEPOINT Update_Unplanned_Task;
896
897 -- Debug info.
898 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
899 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************Start*************************');
900 END IF;
901
902 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
903 Fnd_Msg_Pub.initialize;
904 END IF;
905
906 -- Initialize API return status to success
907 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
908
909 -- Standard call to check for call compatibility.
910 IF NOT Fnd_Api.compatible_api_call(
911 l_api_version,
912 p_api_version,
913 l_api_name,
914 G_PKG_NAME
915 ) THEN
916 RAISE Fnd_Api.g_exc_unexpected_error;
917 END IF;
918
919 ------------------------Start of API Body------------------------------------
920 OPEN c_Visit(l_Task_rec.visit_id);
921 FETCH c_Visit INTO c_Visit_rec;
922 CLOSE c_Visit;
923
924 OPEN c_Task(l_Task_rec.visit_task_id);
925 FETCH c_Task INTO c_Task_rec;
926 CLOSE c_Task;
927
928 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
929 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);
930 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);
931 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 );
932 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);
933 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Object version number = ' || l_task_rec.object_version_number);
934 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Duration from record = ' || l_task_rec.duration);
935 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);
936 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 );
937 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': department_id = ' || l_task_rec.department_id );
938 END IF;
939
940 ----------- Start defining and validate all LOVs on Create Visit's Task UI Screen---
941 --
942 -- For DEPARTMENT
943 -- Convert department name to department id
944 IF (l_task_rec.dept_name IS NOT NULL AND l_task_rec.dept_name <> Fnd_Api.G_MISS_CHAR ) THEN
945
946 AHL_VWP_RULES_PVT.Check_Dept_Desc_Or_Id
947 (p_organization_id => c_visit_rec.organization_id,
948 p_dept_name => l_task_rec.dept_name,
949 p_department_id => NULL,
950 x_department_id => l_department_id,
951 x_return_status => l_return_status,
952 x_error_msg_code => l_msg_data);
953
954 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
955 THEN
956 Fnd_Message.SET_NAME('AHL','AHL_VWP_DEPT_NOT_EXISTS');
957 Fnd_Msg_Pub.ADD;
958 RAISE Fnd_Api.G_EXC_ERROR;
959 END IF;
960
961 --Assign the returned value
962 l_task_rec.department_id := l_department_id;
963 END IF;
964
965 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
966 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Dept ID= ' || l_Task_rec.department_id );
967 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Cost parent= ' || l_Task_rec.cost_parent_number);
968 END IF;
969
970 --
971 -- For COST PARENT TASK
972 -- Convert cost parent number to id
973 IF (l_Task_rec.cost_parent_number IS NOT NULL AND
974 l_Task_rec.cost_parent_number <> Fnd_Api.G_MISS_NUM ) THEN
975
976 AHL_VWP_RULES_PVT.Check_Visit_Task_Number_OR_ID
977 (p_visit_task_id => l_Task_rec.cost_parent_id,
978 p_visit_task_number => l_Task_rec.cost_parent_number,
979 p_visit_id => l_Task_rec.visit_id,
980 x_visit_task_id => l_cost_parent_id,
981 x_return_status => l_return_status,
982 x_error_msg_code => l_msg_data);
983
984 IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS
985 THEN
986 Fnd_Message.SET_NAME('AHL','AHL_VWP_PARENT_NOT_EXISTS');
987 Fnd_Msg_Pub.ADD;
988 RAISE Fnd_Api.g_exc_error;
989 END IF;
990
991 --Assign the returned value
992 l_Task_rec.cost_parent_id := l_cost_parent_id;
993 END IF;
994
995 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
996 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Cost parent ID = ' || l_Task_rec.cost_parent_id);
997 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Validation: Start -- For COST PARENT ');
998 END IF;
999
1000 -- To Check for cost parent task id not forming loop
1001 IF (l_Task_rec.cost_parent_id IS NOT NULL AND
1002 l_Task_rec.cost_parent_id <> Fnd_Api.G_MISS_NUM ) THEN
1003
1004 AHL_VWP_RULES_PVT.Check_Cost_Parent_Loop
1005 (p_visit_id => l_Task_rec.visit_id,
1006 p_visit_task_id => l_Task_rec.visit_task_id ,
1007 p_cost_parent_id => l_Task_rec.cost_parent_id
1008 );
1009
1010 END IF;
1011
1012 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1013 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||': Validation: End -- For COST PARENT ');
1014 END IF;
1015
1016 ----------- End defining and validate all LOVs on Create Visit's Task UI Screen---
1017
1018
1019 ----------------------- validate ----------------------
1020
1021 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1022 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':Validate');
1023 END IF;
1024
1025 -- For all optional fields check if its g_miss_num/g_miss_char/g_miss_date
1026 -- then Null else the value call Default_Missing_Attribs procedure
1027 Default_Missing_Attribs
1028 (
1029 p_x_task_rec => l_Task_rec
1030 );
1031
1032 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1033 Check_Task_Items (
1034 p_task_rec => p_x_task_rec,
1035 p_validation_mode => Jtf_Plsql_Api.g_update,
1036 x_return_status => l_return_status
1037 );
1038 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1039 RAISE Fnd_Api.g_exc_unexpected_error;
1040 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1041 RAISE Fnd_Api.g_exc_error;
1042 END IF;
1043 END IF;
1044
1045 -- Check Object version number.
1046 IF (c_task_rec.object_version_number <> l_task_rec.object_version_number) THEN
1047 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1048 Fnd_Msg_Pub.ADD;
1049 RAISE Fnd_Api.G_EXC_ERROR;
1050 END IF;
1051
1052 -- Post 11.5.10 Changes by Senthil.
1053 IF(L_task_rec.STAGE_ID IS NOT NULL OR L_task_rec.STAGE_NAME IS NOT NULL) THEN
1054 AHL_VWP_VISITS_STAGES_PVT.VALIDATE_STAGE_UPDATES(
1055 P_API_VERSION => 1.0,
1056 P_VISIT_ID => l_Task_rec.visit_id,
1057 P_VISIT_TASK_ID => l_Task_rec.visit_task_id,
1058 P_STAGE_NAME => L_task_rec.STAGE_NAME,
1059 X_STAGE_ID => L_task_rec.STAGE_ID,
1060 X_RETURN_STATUS => l_return_status,
1061 X_MSG_COUNT => l_msg_count,
1062 X_MSG_DATA => l_msg_data );
1063
1064 END IF;
1065
1066 --Standard check to count messages
1067 l_msg_count := Fnd_Msg_Pub.count_msg;
1068
1069 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1070 X_msg_count := l_msg_count;
1071 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1072 RAISE Fnd_Api.G_EXC_ERROR;
1073 END IF;
1074
1075 -------------------------- update --------------------
1076 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1077 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':Update');
1078 END IF;
1079
1080 --Modified by mpothuku to fix LTP forum issue #208 on 04/19/05
1081 IF( nvl(p_module_type,'XXX') <> 'LTP') THEN
1082 l_task_rec.originating_task_id := c_task_rec.originating_task_id;
1083 END IF;
1084 --End mpothuku
1085
1086 Ahl_Visit_Tasks_Pkg.UPDATE_ROW (
1087 X_VISIT_TASK_ID => l_task_rec.visit_task_id,
1088 X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
1089 X_OBJECT_VERSION_NUMBER => l_task_rec.OBJECT_VERSION_NUMBER + 1,
1090 X_VISIT_ID => l_task_rec.visit_id,
1091 X_PROJECT_TASK_ID => c_task_rec.project_task_id,
1092 X_COST_PARENT_ID => l_task_rec.cost_parent_id,
1093 X_MR_ROUTE_ID => c_task_rec.mr_route_id,
1094 X_MR_ID => c_task_rec.mr_id,
1095 X_DURATION => c_task_rec.duration,
1096 X_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id,
1097 X_START_FROM_HOUR => l_task_rec.start_from_hour,
1098 X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
1099 X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
1100 X_INSTANCE_ID => c_Task_rec.instance_id,
1101 X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
1102 X_ORIGINATING_TASK_ID => l_task_rec.originating_task_id, --c_task_rec.originating_task_id,
1103 X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
1104 X_TASK_TYPE_CODE => l_task_rec.task_type_code,
1105 X_DEPARTMENT_ID => l_task_rec.department_id,
1106 X_SUMMARY_TASK_FLAG => 'N',
1107 X_PRICE_LIST_ID => c_task_rec.price_list_id,
1108 X_STATUS_CODE => c_task_rec.status_code,
1109 X_ESTIMATED_PRICE => c_task_rec.estimated_price,
1110 X_ACTUAL_PRICE => c_task_rec.actual_price,
1111 X_ACTUAL_COST => c_task_rec.actual_cost,
1112 -- Changes for 11.5.10 by Senthil.
1113 X_STAGE_ID => l_task_rec.STAGE_ID,
1114 -- Added cxcheng POST11510--------------
1115 X_START_DATE_TIME => NULL,
1116 X_END_DATE_TIME => NULL,
1117 X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
1118 X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
1119 X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
1120 X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
1121 X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
1122 X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
1123 X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
1124 X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
1125 X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
1126 X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
1127 X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
1128 X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
1129 X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
1130 X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
1131 X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
1132 X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
1133 X_VISIT_TASK_NAME => l_task_rec.visit_task_name,
1134 X_DESCRIPTION => l_task_rec.description,
1135 X_QUANTITY => c_task_rec.QUANTITY, -- Added by rnahata for Issue 105
1136 X_LAST_UPDATE_DATE => SYSDATE,
1137 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1138 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
1139
1140
1141 -- Assign back to in/out parameter
1142 p_x_task_rec := l_task_rec;
1143
1144 ------------------------End of API Body------------------------------------
1145 -- Added cxcheng POST11510--------------
1146 --Now adjust the times derivation for task
1147 AHL_VWP_TIMES_PVT.Adjust_Task_Times(p_api_version => 1.0,
1148 p_init_msg_list => Fnd_Api.G_FALSE,
1149 p_commit => Fnd_Api.G_FALSE,
1150 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1151 x_return_status => l_return_status,
1152 x_msg_count => l_msg_count,
1153 x_msg_data => l_msg_data,
1154 p_task_id => l_task_rec.visit_task_id);
1155
1156 --Standard check to count messages
1157 l_msg_count := Fnd_Msg_Pub.count_msg;
1158
1159 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1160 x_msg_count := l_msg_count;
1161 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1162 RAISE Fnd_Api.G_EXC_ERROR;
1163 END IF;
1164
1165
1166 -- To Update visit attribute any_task_chg_flag for costing purpose
1167 -- Looking for changes in 'Start from hour' attributes of task
1168
1169 IF NVL(l_task_rec.Start_from_hour,-30) <> NVL(c_task_rec.Start_from_hour,-30) OR
1170 NVL(l_task_rec.STAGE_ID,-30) <> NVL(c_task_rec.STAGE_ID,-30) OR
1171 NVL(l_task_rec.department_id,-30) <> NVL(c_task_rec.department_id,-30) THEN
1172
1173 OPEN c_Task(l_Task_rec.visit_task_id);
1174 FETCH c_Task INTO c_upd_Task_rec;
1175 CLOSE c_Task;
1176
1177 IF c_upd_Task_rec.start_date_time IS NOT NULL THEN
1178
1179 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
1180 p_api_version => 1.0,
1181 p_init_msg_list => FND_API.g_false,
1182 p_commit => FND_API.g_false,
1183 p_validation_level => FND_API.g_valid_level_full,
1184 p_visit_id => l_task_rec.visit_id,
1185 p_visit_task_id => NULL,
1186 p_org_id => NULL,
1187 p_start_date => NULL,
1188 p_operation_flag => 'U',
1189 x_planned_order_flag => l_planned_order_flag ,
1190 x_return_status => l_return_status,
1191 x_msg_count => l_msg_count,
1192 x_msg_data => l_msg_data );
1193
1194 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1195 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||'After AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
1196 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.',l_full_name ||'Planned Order Flag : ' || l_planned_order_flag);
1197 END IF;
1198
1199 IF l_return_status <> 'S' THEN
1200 RAISE Fnd_Api.G_EXC_ERROR;
1201 END IF;
1202
1203 END IF; -- Start_date_time check.
1204
1205 IF c_visit_rec.any_task_chg_flag = 'N' THEN
1206 AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
1207 (p_visit_id => l_task_rec.visit_id,
1208 p_flag => 'Y',
1209 x_return_status => x_return_status);
1210 END IF;
1211
1212 END IF;
1213
1214 --Standard check to count messages
1215 l_msg_count := Fnd_Msg_Pub.count_msg;
1216
1217 IF l_msg_count > 0 THEN
1218 X_msg_count := l_msg_count;
1219 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1220 RAISE Fnd_Api.G_EXC_ERROR;
1221 END IF;
1222
1223 --Standard check for commit
1224 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1225 COMMIT;
1226 END IF;
1227
1228 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1229 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************END*************************');
1230 END IF;
1231
1232
1233 EXCEPTION
1234 WHEN Fnd_Api.g_exc_error THEN
1235 ROLLBACK TO Update_Unplanned_Task;
1236 x_return_status := Fnd_Api.g_ret_sts_error;
1237 Fnd_Msg_Pub.count_and_get (
1238 p_encoded => Fnd_Api.g_false,
1239 p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242 WHEN Fnd_Api.g_exc_unexpected_error THEN
1243 ROLLBACK TO Update_Unplanned_Task;
1244 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1245 Fnd_Msg_Pub.count_and_get (
1246 p_encoded => Fnd_Api.g_false,
1247 p_count => x_msg_count,
1248 p_data => x_msg_data
1249 );
1250 WHEN OTHERS THEN
1251 ROLLBACK TO Update_Unplanned_Task;
1252 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1253 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1254 THEN
1255 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1256 END IF;
1257 Fnd_Msg_Pub.count_and_get (
1258 p_encoded => Fnd_Api.g_false,
1259 p_count => x_msg_count,
1260 p_data => x_msg_data
1261 );
1262 END Update_Unplanned_Task;
1263
1264 --------------------------------------------------------------------
1265 -- PROCEDURE
1266 -- Delete_Unplanned_Task
1267 --
1268 -- PURPOSE
1269 -- To delete Unplanned tasks for the Maintenace visit.
1270 -- Modifying the Unplanned tasks for costing by rtadikon
1271 --------------------------------------------------------------------
1272 PROCEDURE Delete_Unplanned_Task (
1273 p_api_version IN NUMBER,
1274 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1275 p_commit IN VARCHAR2 := Fnd_Api.g_false,
1276 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1277 p_module_type IN VARCHAR2 := 'JSP',
1278 p_visit_task_ID IN NUMBER,
1279 x_return_status OUT NOCOPY VARCHAR2,
1280 x_msg_count OUT NOCOPY NUMBER,
1281 x_msg_data OUT NOCOPY VARCHAR2
1282 )
1283
1284 IS
1285
1286 -- local variables defined for the procedure
1287 l_api_version CONSTANT NUMBER := 1.0;
1288 l_api_name CONSTANT VARCHAR2(30) := 'Delete Unplanned Task';
1289 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1290 l_origin_id NUMBER;
1291 l_msg_count NUMBER;
1292
1293 -- To find all tasks related information
1294 CURSOR c_Task (x_id IN NUMBER) IS
1295 SELECT * FROM Ahl_Visit_Tasks_VL
1296 WHERE Visit_Task_ID = x_id;
1297 c_task_rec c_Task%ROWTYPE;
1298
1299 BEGIN
1300 --------------------- initialize -----------------------
1301 SAVEPOINT Delete_Unplanned_Task;
1302
1303 -- Check if API is called in debug mode. If yes, enable debug.
1304 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1305 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************START*************************');
1306 END IF;
1307
1308 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1309 Fnd_Msg_Pub.initialize;
1310 END IF;
1311
1312 -- Initialize API return status to success
1313 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1314
1315 -- Standard call to check for call compatibility.
1316 IF NOT Fnd_Api.compatible_api_call(
1317 l_api_version,
1318 p_api_version,
1319 l_api_name,
1320 G_PKG_NAME) THEN
1321 RAISE Fnd_Api.g_exc_unexpected_error;
1322 END IF;
1323
1324 ------------------------Start of API Body------------------------------------
1325 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1326 THEN
1327 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':Task Id' || p_visit_task_ID);
1328 END IF;
1329
1330 -- To check if the input taskid exists in task entity.
1331
1332 OPEN c_Task(p_Visit_Task_ID);
1333 FETCH c_Task INTO c_task_rec;
1334
1335 IF c_Task%NOTFOUND THEN
1336 CLOSE c_Task;
1337 Fnd_Message.set_name('AHL', 'AHL_VWP_TASK_ID_INVALID');
1338 FND_MESSAGE.SET_TOKEN('TASK_ID',p_visit_task_id,false);
1339 Fnd_Msg_Pub.ADD;
1340 RAISE Fnd_Api.g_exc_error;
1341 ELSE
1342 CLOSE c_Task;
1343
1344 l_origin_id:= c_task_rec.originating_task_id;
1345
1346 IF l_origin_id IS NOT NULL THEN
1347
1348 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1349 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', ' Before Call to Delete Summary task ');
1350 END IF;
1351
1352 AHL_VWP_TASKS_PVT.Delete_Summary_Task(
1353 p_api_version => p_api_version,
1354 p_init_msg_list => Fnd_Api.g_false,
1355 p_commit => Fnd_Api.g_false,
1356 p_validation_level => Fnd_Api.g_valid_level_full,
1357 p_module_type => NULL,
1358 p_Visit_Task_Id => l_origin_id,
1359 x_return_status => x_return_status,
1360 x_msg_count => x_msg_count,
1361 x_msg_data => x_msg_data
1362 );
1363
1364 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1365 RAISE Fnd_Api.G_EXC_ERROR;
1366 END IF;
1367
1368 Else
1369 Fnd_Message.SET_NAME('AHL','AHL_VWP_UNPLANNEDTASKMR');
1370 Fnd_Msg_Pub.ADD;
1371 RAISE Fnd_Api.G_EXC_ERROR;
1372 --Displays 'Unplanned Task association to Maintenance Requirement is missing.'
1373 End IF;
1374
1375 END IF;
1376
1377 /* Commented by mpothuku on 02/25/05 as this is moved to ahl_vwp_tasks_pvt.Delete_Summary_Task
1378
1379 AHL_UMP_UNPLANNED_PVT.DELETE_UNIT_EFFECTIVITY(
1380 P_API_VERSION => 1.0,
1381 X_RETURN_STATUS => x_return_status,
1382 X_MSG_COUNT => x_msg_count,
1383 X_MSG_DATA => x_msg_data,
1384 P_UNIT_EFFECTIVITY_ID => c_task_rec.unit_effectivity_id
1385 );
1386 */
1387
1388 l_msg_count := Fnd_Msg_Pub.count_msg;
1389
1390 IF l_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1391 X_msg_count := l_msg_count;
1392 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1393
1394 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1395 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', ' Error Before Commit---> After Delete Summary task call');
1396 END IF;
1397
1398 RAISE Fnd_Api.G_EXC_ERROR;
1399 END IF;
1400
1401
1402 ------------------------End of API Body------------------------------------
1403 IF Fnd_Api.to_boolean (p_commit) THEN
1404 COMMIT;
1405 END IF;
1406
1407 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1408 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', l_full_name ||':*************************END*************************');
1409 END IF;
1410 EXCEPTION
1411 WHEN Fnd_Api.g_exc_error THEN
1412 ROLLBACK TO Delete_Unplanned_Task;
1413 x_return_status := Fnd_Api.g_ret_sts_error;
1414 Fnd_Msg_Pub.count_and_get (
1415 p_encoded => Fnd_Api.g_false,
1416 p_count => x_msg_count,
1417 p_data => x_msg_data
1418 );
1419
1420 WHEN Fnd_Api.g_exc_unexpected_error THEN
1421 ROLLBACK TO Delete_Unplanned_Task;
1422 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1423 Fnd_Msg_Pub.count_and_get (
1424 p_encoded => Fnd_Api.g_false,
1425 p_count => x_msg_count,
1426 p_data => x_msg_data
1427 );
1428
1429 WHEN OTHERS THEN
1430 ROLLBACK TO Delete_Unplanned_Task;
1431 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1432 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1433 THEN
1434 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1435 END IF;
1436 Fnd_Msg_Pub.count_and_get (
1437 p_encoded => Fnd_Api.g_false,
1438 p_count => x_msg_count,
1439 p_data => x_msg_data
1440 );
1441
1442 END Delete_Unplanned_Task;
1443
1444 ---------------------------------------------------------------------
1445 -- PROCEDURE
1446 -- Check_Task_Items
1447 --
1448 -- PURPOSE
1449 --
1450 ---------------------------------------------------------------------
1451 PROCEDURE Check_Task_Items (
1452 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1453 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1454
1455 x_return_status OUT NOCOPY VARCHAR2
1456 )
1457 IS
1458 BEGIN
1459 --
1460 -- Validate required items.
1461
1462 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1463
1464 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1465 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Task_Items' || ':Before Check_Visit_Task_Req_Items');
1466 END IF;
1467 Check_Visit_Task_Req_Items (
1468 p_task_rec => p_task_rec,
1469 x_return_status => x_return_status
1470 );
1471 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1472 RETURN;
1473 END IF;
1474 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1475 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.', 'Check_Task_Items' || ':After Check_Visit_Task_Req_Items');
1476 END IF;
1477
1478 --
1479 -- Validate uniqueness.
1480 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1481 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME, 'Check_Task_Items' ||':Before Check_Visit_Task_UK_Items');
1482 END IF;
1483 Check_Visit_Task_UK_Items (
1484 p_task_rec => p_task_rec,
1485 p_validation_mode => p_validation_mode,
1486 x_return_status => x_return_status
1487 );
1488 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1489 RETURN;
1490 END IF;
1491 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1492 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME, 'Check_Task_Items' ||':Before Check_Visit_Task_UK_Items');
1493 END IF;
1494 END Check_Task_Items;
1495
1496 ---------------------------------------------------------------------
1497 -- PROCEDURE
1498 -- Complete_Visit_Task_Rec
1499 --
1500 -- PURPOSE
1501 --
1502 ---------------------------------------------------------------------
1503 /* Commented
1504 PROCEDURE Complete_Visit_Task_Rec (
1505 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1506 x_complete_rec OUT NOCOPY AHL_VWP_RULES_PVT.Task_Rec_Type
1507 )
1508 IS
1509 CURSOR c_Visit_Task IS
1510 SELECT *
1511 FROM Ahl_Visit_Tasks_vl
1512 WHERE Visit_Task_ID = p_task_rec.Visit_Task_ID;
1513 --
1514 -- This is the only exception for using %ROWTYPE.
1515 -- We are selecting from the VL view, which may
1516 -- have some denormalized columns as compared to
1517 -- the base tables.
1518 l_task_rec c_Visit_Task%ROWTYPE;
1519 BEGIN
1520 x_complete_rec := p_task_rec;
1521 OPEN c_Visit_Task;
1522 FETCH c_Visit_Task INTO l_task_rec;
1523 IF c_Visit_Task%NOTFOUND THEN
1524 CLOSE c_Visit_Task;
1525 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
1526 Fnd_Msg_Pub.ADD;
1527 RAISE Fnd_Api.g_exc_error;
1528 END IF;
1529 CLOSE c_Visit_Task;
1530
1531 END Complete_Visit_Task_Rec;
1532 */
1533
1534 ---------------------------------------------------------------------
1535 -- PROCEDURE
1536 -- Check_Visit_Task_Req_Items
1537 --
1538 -- PURPOSE
1539 --
1540 ---------------------------------------------------------------------
1541 PROCEDURE Check_Visit_Task_Req_Items (
1542 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1543 x_return_status OUT NOCOPY VARCHAR2
1544 )
1545 IS
1546
1547 BEGIN
1548
1549 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1550 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1551 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Visit_Task_Req_Items: = Start Check_Visit_Task_Req_Items ');
1552 END IF;
1553 -- TASK NAME ==== NAME
1554 IF (p_task_rec.VISIT_TASK_NAME IS NULL OR p_Task_rec.VISIT_TASK_NAME = Fnd_Api.G_MISS_CHAR) THEN
1555 Fnd_Message.set_name ('AHL', 'AHL_VWP_NAME_MISSING');
1556 Fnd_Msg_Pub.ADD;
1557 x_return_status := Fnd_Api.g_ret_sts_error;
1558 RETURN;
1559 END IF;
1560 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1561 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);
1562 END IF;
1563 IF (p_task_rec.START_FROM_HOUR IS NOT NULL and p_Task_rec.START_FROM_HOUR <> Fnd_Api.G_MISS_NUM) THEN
1564 IF p_task_rec.START_FROM_HOUR < 0 OR FLOOR(p_task_rec.START_FROM_HOUR) <> p_task_rec.START_FROM_HOUR THEN
1565 Fnd_Message.set_name ('AHL', 'AHL_VWP_ONLY_POSITIVE_VALUE');
1566 Fnd_Msg_Pub.ADD;
1567 x_return_status := Fnd_Api.g_ret_sts_error;
1568 RETURN;
1569 END IF;
1570 END IF;
1571
1572 END Check_Visit_Task_Req_Items;
1573
1574 ---------------------------------------------------------------------
1575 -- PROCEDURE
1576 -- Check_Visit_Task_UK_Items
1577 --
1578 -- PURPOSE
1579 --
1580 ---------------------------------------------------------------------
1581 PROCEDURE Check_Visit_Task_UK_Items (
1582 p_task_rec IN AHL_VWP_RULES_PVT.Task_Rec_Type,
1583 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1584 x_return_status OUT NOCOPY VARCHAR2
1585 )
1586 IS
1587 l_valid_flag VARCHAR2(1);
1588
1589 BEGIN
1590 x_return_status := Fnd_Api.g_ret_sts_success;
1591 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1592 fnd_log.string(fnd_log.level_procedure,'ahl.plsql.'||G_PKG_NAME,'Check_Visit_Task_UK_Items: = Start Check_Visit_Task_UK_Items ');
1593 END IF;
1594 --
1595 -- For Task, when ID is passed in, we need to
1596 -- check if this ID is unique.
1597 IF p_validation_mode = Jtf_Plsql_Api.g_create AND p_task_rec.Visit_Task_ID IS NOT NULL THEN
1598 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1599 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');
1600 END IF;
1601 -- FOR CREATION
1602 IF Ahl_Utility_Pvt.check_uniqueness(
1603 'Ahl_Visit_Tasks_vl',
1604 'Visit_Task_ID = ' || p_task_rec.Visit_Task_ID
1605 ) = Fnd_Api.g_false
1606 THEN
1607 Fnd_Message.set_name ('AHL', 'AHL_VWP_DUPLICATE_TASK_ID');
1608 Fnd_Msg_Pub.ADD;
1609 x_return_status := Fnd_Api.g_ret_sts_error;
1610 RETURN;
1611 END IF;
1612 END IF;
1613
1614 END Check_Visit_Task_UK_Items;
1615
1616 END Ahl_Vwp_Unplan_Tasks_Pvt;