1 PACKAGE BODY AHL_VWP_TASKS_LINKS_PVT AS
2 /* $Header: AHLVTLNB.pls 120.0.12020000.2 2012/12/07 14:57:16 sareepar ship $ */
3 G_PKG_NAME VARCHAR2(30) := 'AHL_VWP_TASKS_LINKS_PVT';
4
5 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
6 PROCEDURE DEFAULT_MISSING_ATTRIBS
7 (p_x_task_link_tbl IN OUT NOCOPY AHL_VWP_TASKS_LINKS_PVT.TASK_LINK_TBL)
8 AS
9 BEGIN
10 IF p_x_task_link_TBL.count >0
11 THEN
12
13 FOR i IN p_x_task_link_TBL.FIRST.. p_x_task_link_TBL.LAST
14 LOOP
15
16 IF p_x_task_link_tbl(i).TASK_LINK_ID= FND_API.G_MISS_NUM
17 THEN
18 p_x_task_link_tbl(i).TASK_LINK_ID:=NULL;
19 END IF;
20 IF p_x_task_link_tbl(i).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
21 THEN
22 p_x_task_link_tbl(i).OBJECT_VERSION_NUMBER:=NULL;
23 END IF;
24 IF p_x_task_link_tbl(i).LAST_UPDATE_DATE=FND_API.G_MISS_DATE
25 THEN
26 p_x_task_link_tbl(i).LAST_UPDATE_DATE:=NULL;
27 END IF;
28 IF p_x_task_link_tbl(i).LAST_UPDATED_BY= FND_API.G_MISS_NUM
29 THEN
30 p_x_task_link_tbl(i).LAST_UPDATED_BY:=NULL;
31 END IF;
32 IF p_x_task_link_tbl(i).CREATION_DATE=FND_API.G_MISS_DATE
33 THEN
34 p_x_task_link_tbl(i).CREATION_DATE:=NULL;
35 END IF;
36 IF p_x_task_link_tbl(i).CREATED_BY= FND_API.G_MISS_NUM
37 THEN
38 p_x_task_link_tbl(i).CREATED_BY:=NULL;
39 END IF;
40 IF p_x_task_link_tbl(i).LAST_UPDATE_LOGIN= FND_API.G_MISS_NUM
41 THEN
42 p_x_task_link_tbl(i).LAST_UPDATE_LOGIN:=NULL;
43 END IF;
44 IF p_x_task_link_tbl(i).VISIT_TASK_ID= FND_API.G_MISS_NUM
45 THEN
46 p_x_task_link_tbl(i).VISIT_TASK_ID:=NULL;
47 END IF;
48 IF p_x_task_link_tbl(i).PARENT_TASK_ID= FND_API.G_MISS_NUM
49 THEN
50 p_x_task_link_tbl(i).PARENT_TASK_ID:=NULL;
51 END IF;
52
53 IF p_x_task_link_TBL(i).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
54 THEN
55 p_x_task_link_TBL(i).ATTRIBUTE_CATEGORY:=NULL;
56 END IF;
57
58 IF p_x_task_link_TBL(i).ATTRIBUTE1=FND_API.G_MISS_CHAR
59 THEN
60 p_x_task_link_TBL(i).ATTRIBUTE1:=NULL;
61 END IF;
62
63 IF p_x_task_link_TBL(i).ATTRIBUTE2=FND_API.G_MISS_CHAR
64 THEN
65 p_x_task_link_TBL(i).ATTRIBUTE2:=NULL;
66 END IF;
67
68 IF p_x_task_link_TBL(i).ATTRIBUTE3=FND_API.G_MISS_CHAR
69 THEN
70 p_x_task_link_TBL(i).ATTRIBUTE3:=NULL;
71 END IF;
72
73 IF p_x_task_link_TBL(i).ATTRIBUTE4 IS NULL OR p_x_task_link_TBL(i).ATTRIBUTE4=FND_API.G_MISS_CHAR
74 THEN
75 p_x_task_link_TBL(i).ATTRIBUTE4:=NULL;
76 END IF;
77
78 IF p_x_task_link_TBL(i).ATTRIBUTE5=FND_API.G_MISS_CHAR
79 THEN
80 p_x_task_link_TBL(i).ATTRIBUTE5:=NULL;
81 END IF;
82
83 IF p_x_task_link_TBL(i).ATTRIBUTE6=FND_API.G_MISS_CHAR
84 THEN
85 p_x_task_link_TBL(i).ATTRIBUTE6:=NULL;
86 END IF;
87
88 IF p_x_task_link_TBL(i).ATTRIBUTE7=FND_API.G_MISS_CHAR
89 THEN
90 p_x_task_link_TBL(i).ATTRIBUTE7:=NULL;
91 END IF;
92
93 IF p_x_task_link_TBL(i).ATTRIBUTE8=FND_API.G_MISS_CHAR
94 THEN
95 p_x_task_link_TBL(i).ATTRIBUTE8:=NULL;
96 END IF;
97
98 IF p_x_task_link_TBL(i).ATTRIBUTE9=FND_API.G_MISS_CHAR
99 THEN
100 p_x_task_link_TBL(i).ATTRIBUTE9:=NULL;
101 END IF;
102
103 IF p_x_task_link_TBL(i).ATTRIBUTE10=FND_API.G_MISS_CHAR
104 THEN
105 p_x_task_link_TBL(i).ATTRIBUTE10:=NULL;
106 END IF;
107
108 IF p_x_task_link_TBL(i).ATTRIBUTE11=FND_API.G_MISS_CHAR
109 THEN
110 p_x_task_link_TBL(i).ATTRIBUTE11:=NULL;
111 END IF;
112
113 IF p_x_task_link_TBL(i).ATTRIBUTE12=FND_API.G_MISS_CHAR
114 THEN
115 p_x_task_link_TBL(i).ATTRIBUTE12:=NULL;
116 END IF;
117
118 IF p_x_task_link_TBL(i).ATTRIBUTE13=FND_API.G_MISS_CHAR
119 THEN
120 p_x_task_link_TBL(i).ATTRIBUTE13:=NULL;
121 END IF;
122
123 IF p_x_task_link_TBL(i).ATTRIBUTE14=FND_API.G_MISS_CHAR
124 THEN
125 p_x_task_link_TBL(i).ATTRIBUTE14:=NULL;
126 END IF;
127
128 IF p_x_task_link_TBL(i).ATTRIBUTE15=FND_API.G_MISS_CHAR
129 THEN
130 p_x_task_link_TBL(i).ATTRIBUTE15:=NULL;
131 END IF;
132 END LOOP;
133 END IF;
134 END;
135
136 PROCEDURE VALIDATE_VWP_LINKS
137 (
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2,
141 p_task_link_rec IN TASK_LINK_rEC
142 )
143 as
144
145 -- Check For Unique Combination
146
147 CURSOR CHECK_UNIQ(C_PARENT_TASK_ID NUMBER,C_visit_Task_id NUMBER)
148 IS
149 select count(*)
150 from AHL_TASK_LINKS
151 where VISIT_TASK_ID=C_VISIT_TASK_ID
152 and PARENT_TASK_ID=C_PARENT_TASK_ID;
153
154 -- Changes done for Post 11.5.10 by senthil.
155
156 -- Commented as per review comments
157 /*CURSOR c_child_task(p_child_task_id IN NUMBER)
158 IS
159 select stage_num from ahl_vwp_stages_b where stage_id = (SELECT stage_id
160 FROM ahl_visit_tasks_vl
161 WHERE visit_task_id = p_child_task_id);
162
163 l_child_stage_num number;
164 */
165
166 -- VWP ER-12424063:: tchimira :: 15-APR-2011:: start
167 -- Get the passed p_parent_task_id and all its successive parent tasks
168 CURSOR c_get_parent_tasks(p_parent_task_id IN NUMBER, p_child_task_id IN NUMBER)
169 IS
170 select link.parent_task_id from ahl_task_links link
171 start with link.visit_task_id = p_child_task_id
172 and link.parent_task_id = p_parent_task_id
173 connect by prior link.parent_task_id = link.visit_task_id;
174
175 -- Get the passed p_child_task_id and all its successive child tasks
176 CURSOR c_get_child_tasks(p_parent_task_id IN NUMBER, p_child_task_id IN NUMBER)
177 IS
178 select link.visit_task_id from ahl_task_links link
179 start with link.parent_task_id = p_parent_task_id
180 and link.visit_task_id = p_child_task_id
181 connect by prior link.visit_task_id = link.parent_task_id;
182
183 --Get stage id for a given task
184 CURSOR c_get_stage_id(p_task_id IN NUMBER)
185 IS
186 SELECT stage_id FROM ahl_visit_tasks_b
187 WHERE visit_task_id = p_task_id;
188
189 --Check if the task rule being created voilates the already defined stage rules
190 CURSOR c_validate_rules (p_parent_stage_id IN NUMBER, p_child_stage_id IN NUMBER)
191 IS
192 SELECT 'X' FROM ahl_stage_links
193 WHERE subject_id = p_child_stage_id
194 AND relation_type <> 'PARALLEL'
195 START WITH object_id = p_parent_stage_id
196 CONNECT BY PRIOR subject_id = object_id;
197
198 l_parent_stage_id NUMBER;
199 l_child_stage_id NUMBER;
200 l_val_dummy VARCHAR2(1);
201
202 --VWP ER-12424063:: tchimira ::15-APR-2011:: end
203
204 --Commented as is never used
205 /*CURSOR c_job_status(p_visit_task_id IN NUMBER)
206 IS
207 SELECT status_code,
208 confirm_failure_flag
209 FROM ahl_workorders
210 WHERE visit_task_id = p_visit_task_id;
211 */
212
213 CURSOR c_task_status(p_task_id IN NUMBER)
214 IS
215 select 'X'
216 FROM ahl_visit_tasks_b
217 WHERE visit_task_id = p_task_id
218 AND status_code = 'RELEASED';
219
220 CURSOR c_task_type_code(p_task_id IN NUMBER)
221 IS
222 select 'X'
223 FROM ahl_visit_tasks_b
224 WHERE visit_task_id = p_task_id
225 AND task_type_code = 'SUMMARY';
226
227 -- As per latest Post 11.5.10 DLD if the task are in release status
228 -- user cannot create dependencies.
229 -- Commented by amagrawa as user can associate only task in planning status to task hierarchy
230 /*CURSOR c_children_check_status(p_parent_visit_task_id IN NUMBER)
231 IS
232 SELECT 'X'
233 FROM ahl_task_links lin,
234 ahl_visit_tasks_b vt
235 WHERE lin.visit_task_id = vt.visit_task_id
236 AND vt.status_code = 'RELEASED'
237 START WITH lin.parent_task_id = p_parent_visit_task_id
238 CONNECT BY PRIOR lin.visit_task_id = lin.parent_task_id;
239 */
240 CURSOR c_task_number(p_task_id IN NUMBER)
241 IS
242 SELECT VISIT_TASK_NUMBER
243 FROM ahl_visit_tasks_b
244 where visit_task_id = p_task_id;
245
246 l_dummy VARCHAR2(1);
247 l_parent_task_number number;
248 l_child_task_number number;
249
250 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_VWP_LINKS';
251 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
252 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
253 l_counter2 NUMBER:=0;
254 l_child_task_id number;
255 l_parent_task_id number;
256
257 BEGIN
258
259 x_return_status:=FND_API.G_RET_STS_SUCCESS;
260
261
262 -- Changes done for Post 11.5.10 by senthil.
263 /*IF p_task_link_rec.HIERARCHY_INDICATOR='AFTER' --Commented by tchimira as per vwp ER-12424063 on 14-FEB-2011
264 THEN
265 l_parent_task_id := p_task_link_rec.visit_task_id;
266 l_child_task_id := p_task_link_rec.parent_task_id;
267 ELSE */
268 l_parent_task_id := p_task_link_rec.parent_task_id;
269 l_child_task_id := p_task_link_rec.visit_task_id;
270 --END IF;
271
272 -- Added by amagrawa to check for a task in summary status
273 OPEN c_task_type_code(p_task_link_rec.parent_task_id);
274 FETCH c_task_type_code INTO l_dummy;
275 IF c_task_type_code%FOUND THEN
276 --Added by amagrawa
277 open c_task_number(p_task_link_rec.parent_task_id);
278 FETCH c_task_number into l_parent_task_number;
279 CLOSE c_task_number;
280 --End of change by amagrawa
281 CLOSE c_task_type_code;
282 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_TASK_SUMMARY');
283 FND_MESSAGE.SET_TOKEN('TASK_NUM',l_parent_task_number);
284 FND_MSG_PUB.ADD;
285 RAISE FND_API.G_EXC_ERROR;
286 END IF;
287 CLOSE c_task_type_code;
288
289 OPEN c_task_status(l_parent_task_id);
290 FETCH c_task_status INTO l_dummy;
291 IF c_task_status%FOUND THEN
292 --Added by amagrawa
293 open c_task_number(l_parent_task_id);
294 FETCH c_task_number into l_parent_task_number;
295 CLOSE c_task_number;
296 --End of change by amagrawa
297 CLOSE c_task_status;
298 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_TASK_PARENT_REL');
299 FND_MESSAGE.SET_TOKEN('TASK_NUM',l_parent_task_number);
300 FND_MSG_PUB.ADD;
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303 CLOSE c_task_status;
304
305
306 OPEN c_task_status(l_child_task_id);
307 FETCH c_task_status INTO l_dummy;
308 IF c_task_status%FOUND THEN
309 --Added by amagrawa
310 open c_task_number(l_child_task_id);
311 FETCH c_task_number into l_child_task_number;
312 CLOSE c_task_number;
313 --End of change by amagrawa
314 CLOSE c_task_status;
315 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_TASK_CHILD_REL');
316 FND_MESSAGE.SET_TOKEN('TASK_NUM',l_child_task_number);
317 FND_MSG_PUB.ADD;
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320 CLOSE c_task_status;
321
322 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
323 fnd_log.string(fnd_log.level_statement, L_DEBUG, 'parent task id= '||l_parent_task_id||', child task id is= '||l_child_task_id);
324 END IF;
325
326 --VWP ER-12424063:: tchimira ::15-APR-2011:: start
327 --Instead of comparing stage number for validation errors, compare stage rules.
328
329 -- Loop through all the successive parents of the passed task rule
330 FOR parent_task_rec IN c_get_parent_tasks(l_parent_task_id, l_child_task_id) LOOP
331
332 -- Loop through all the successive chidlren of the passed task rule
333 FOR child_task_rec IN c_get_child_tasks(l_parent_task_id, l_child_task_id) LOOP
334
335 OPEN c_get_stage_id (parent_task_rec.parent_task_id);
336 FETCH c_get_stage_id INTO l_parent_stage_id;
337 CLOSE c_get_stage_id;
338
339 OPEN c_get_stage_id (child_task_rec.visit_task_id);
340 FETCH c_get_stage_id INTO l_child_stage_id;
341 CLOSE c_get_stage_id;
342
343 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
344 fnd_log.string(fnd_log.level_statement, L_DEBUG, 'parent stage id= '||l_parent_stage_id||', child stage id= '||l_child_stage_id);
345 END IF;
346
347 --If child task's stage starts before parent task's stage, throw an error
348 IF l_parent_stage_id IS NOT NULL AND l_child_stage_id IS NOT NULL THEN
349 OPEN c_validate_rules (l_parent_stage_id, l_child_stage_id);
350 FETCH c_validate_rules INTO l_val_dummy;
351 IF c_validate_rules%FOUND THEN
352 CLOSE c_validate_rules;
353
354 OPEN c_task_number(l_parent_task_id);
355 FETCH c_task_number into l_parent_task_number;
356 CLOSE c_task_number;
357
358 OPEN c_task_number(l_child_task_id);
359 FETCH c_task_number into l_child_task_number;
360 CLOSE c_task_number;
361 --added by tchimira for VWPE 12730539
362 IF p_task_link_rec.visit_Task_number IS NULL THEN
363 l_child_task_number := NULL;
364 l_parent_task_number := NULL;
365 END IF;
366 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_CHILD_STG_GT');
367 FND_MESSAGE.SET_TOKEN('CHILD_TASK_NUM',l_child_task_number);
368 FND_MESSAGE.SET_TOKEN('PARENT_TASK_NUM',l_parent_task_number);
369 FND_MSG_PUB.ADD;
370 RAISE FND_API.G_EXC_ERROR;
371 END IF; -- if c_validate_rules not found
372 CLOSE c_validate_rules;
373 END IF; -- if either parent or child stage id is null
374 END LOOP; -- loop through child tasks
375 END LOOP; -- loop through parent tasks
376 --VWP ER-12424063:: tchimira ::15-APR-2011:: end
377
378 IF p_task_link_rec.dml_operation<>'D'
379 THEN
380
381 IF (p_task_link_rec.task_link_id IS NULL OR p_task_link_rec.task_link_id=FND_API.G_MISS_NUM) AND p_task_link_rec.dml_operation<>'C'
382 THEN
383 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_VISIT_LINK_ID_NULL');
384 FND_MSG_PUB.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388 IF (p_task_link_rec.OBJECT_VERSION_NUMBER IS NULL OR p_task_link_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM) and p_task_link_rec.dml_operation<>'C'
389 THEN
390 FND_MESSAGE.SET_NAME('AHL','AHL_COM_OBJECT_VERS_NUM_NULL');
391 FND_MSG_PUB.ADD;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394
395
396 -- Check for uniq ness of the Route Sequences
397
398 IF (p_task_link_rec.visit_Task_id IS NOT NULL AND p_task_link_rec.visit_Task_id<>FND_API.G_MISS_NUM
399 AND p_task_link_rec.PARENT_TASK_ID IS NOT NULL AND p_task_link_rec.PARENT_TASK_ID<>FND_API.G_MISS_NUM)
400 THEN
401 IF G_DEBUG='Y' THEN
402 AHL_DEBUG_PUB.debug(L_FULL_NAME|| 'Enter Validate 02','+VWP_HIERARCHY+');
403 END IF;
404 -- Commented by amagrawa based on review comments
405 /* IF p_task_link_rec.HIERARCHY_INDICATOR='AFTER'
406 THEN
407 OPEN check_uniq(p_task_link_rec.visit_Task_id,p_task_link_rec.PARENT_TASK_ID);
408 ELSE
409 OPEN check_uniq(p_task_link_rec.PARENT_TASK_ID,p_task_link_rec.visit_Task_id);
410 END IF;
411 */
412 -- Added by amagrawa based on review comments
413 OPEN check_uniq(l_parent_task_id ,l_child_task_id);
414 IF G_DEBUG='Y' THEN
415 AHL_DEBUG_PUB.debug(L_FULL_NAME||'l_parent_task_id '||l_parent_task_id||'l_child_task_id '||l_child_task_id);
416 END IF;
417 FETCH check_uniq INTO l_counter2;
418
419 IF G_DEBUG='Y' THEN
420 AHL_DEBUG_PUB.debug(L_FULL_NAME||'Count Records'||to_char(l_counter2),'+VWP_HIERARCHY+');
421 END IF;
422 --VWP ER-12424063:: tchimira:: changed the condition l_counter2>0 to l_counter2>1
423 -- and added one more condition
424 IF ((l_counter2>1) AND (p_task_link_rec.visit_Task_number IS NOT NULL))
425 THEN
426 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_TASK_LINK_DUP');
427 FND_MESSAGE.SET_TOKEN('RECORD',p_task_link_rec.visit_Task_number,false);
428 FND_MSG_PUB.ADD;
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431 CLOSE check_UNIQ;
432
433 END IF;
434 END IF;
435 EXCEPTION
436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
437 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
439 p_count => x_msg_count,
440 p_data => x_msg_data);
441
442 WHEN FND_API.G_EXC_ERROR THEN
443 X_return_status := FND_API.G_RET_STS_ERROR;
444 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
445 p_count => x_msg_count,
446 p_data => X_msg_data);
447 WHEN OTHERS THEN
448 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
450 THEN
451 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
452 p_procedure_name => L_API_NAME,
453 p_error_text => SUBSTR(SQLERRM,1,240));
454 END IF;
455 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
456 p_count => x_msg_count,
457 p_data => X_msg_data);
458 END;
459
460
461 --Tranlate Value to id.
462
463 PROCEDURE TRANS_VALUE_ID
464 (
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2,
468 p_x_task_link_rec IN OUT NOCOPY TASK_LINK_REC
469 )
470 as
471 CURSOR get_task_info(c_visit_task_id Number,C_PARENT_TASK_NUMBER VARCHAR2)
472 IS
473 SELECT *
474 FROM AHL_VISIT_TASKS_VL
475 WHERE VISIT_TASK_NUMBER=C_PARENT_TASK_NUMBER
476 AND SUMMARY_TASK_FLAG='N'
477 AND VISIT_ID = (select visit_id
478 from AHL_VISIT_TASKS_B
479 where VISIT_TASK_ID = c_visit_task_id);
480
481 l_task_det get_task_info%rowtype;
482 BEGIN
483
484 x_return_status:=FND_API.G_RET_STS_SUCCESS;
485
486 IF p_x_task_link_rec.visit_task_number is null OR p_x_task_link_rec.visit_task_number=FND_API.G_MISS_CHAR
487 THEN
488 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_TASK_NUMBER_NULL');
489 FND_MSG_PUB.ADD;
490 RAISE FND_API.G_EXC_ERROR;
491
492 ELSE
493 OPEN get_task_info(p_x_task_link_rec.VISIT_TASK_ID,p_x_task_link_rec.visit_task_number);
494 FETCH get_task_info INTO l_task_det;
495
496 IF get_task_info%NOTFOUND
497 THEN
498 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_CONTEXT_TSK_INV');
499 FND_MESSAGE.SET_TOKEN('RECORD',p_x_task_link_rec.visit_Task_number,false);
500 FND_MSG_PUB.ADD;
501 RAISE FND_API.G_EXC_ERROR;
502 ELSE
503 p_x_task_link_rec.parent_task_id:=l_task_det.visit_task_id;
504 -- Commented by amagrawa based on review comments.
505 -- p_x_task_link_rec.visit_task_number:=l_task_det.visit_task_number;
506 p_x_task_link_rec.visit_task_name:=l_task_det.visit_task_name;
507 END IF;
508 CLOSE get_task_info;
509 END IF;
510
511 EXCEPTION
512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
513 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
515 p_count => x_msg_count,
516 p_data => x_msg_data);
517
518 WHEN FND_API.G_EXC_ERROR THEN
519 X_return_status := FND_API.G_RET_STS_ERROR;
520 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
521 p_count => x_msg_count,
522 p_data => X_msg_data);
523 WHEN OTHERS THEN
524 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
526 THEN
527 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
528 p_procedure_name => 'TRANS_VALUE_ID',
529 p_error_text => SUBSTR(SQLERRM,1,240));
530 END IF;
531 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
532 p_count => x_msg_count,
533 p_data => X_msg_data);
534 END;
535
536
537 PROCEDURE NON_CYCLIC_ENF
538 (
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2,
542 P_VISIT_TASK_ID IN NUMBER,
543 P_VISIT_TASK_NUMBER IN VARCHAR2
544 )
545 AS
546 l_cyclic_loop EXCEPTION;
547 PRAGMA EXCEPTION_INIT(l_cyclic_loop,-1436);
548 l_counter NUMBER;
549 BEGIN
550 x_return_status:=FND_API.G_RET_STS_SUCCESS;
551
552 SELECT COUNT(*) INTO l_counter
553 FROM AHL_TASK_LINKS A
554 START WITH VISIT_TASK_ID=P_VISIT_TASK_ID
555 CONNECT BY PRIOR VISIT_TASK_ID =PARENT_TASK_ID;
556 EXCEPTION
557 WHEN l_cyclic_loop THEN
558 IF P_VISIT_TASK_NUMBER IS NOT NULL THEN
559 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_VISIT_TASK_ID_CYC');
560 FND_MESSAGE.SET_TOKEN('RECORD',P_VISIT_TASK_NUMBER ,false);
561 FND_MSG_PUB.ADD;
562 -- Added by tchimira for VWPE 12730539
563 ELSE
564 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_WBS_INVALID'); --WBS rule and Route Dependency are in violation
565 FND_MSG_PUB.ADD;
566 END IF;
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
569 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
570 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
571 p_count => x_msg_count,
572 p_data => x_msg_data);
573
574 WHEN FND_API.G_EXC_ERROR THEN
575 X_return_status := FND_API.G_RET_STS_ERROR;
576 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
577 p_count => x_msg_count,
578 p_data => X_msg_data);
579 WHEN OTHERS THEN
580 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
582 THEN
583 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
584 p_procedure_name => 'NON_CYCLIC_ENF',
585 p_error_text => SUBSTR(SQLERRM,1,240));
586 END IF;
587 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
588 p_count => x_msg_count,
589 p_data => X_msg_data);
590 END;
591
592 -- Post 11.5.10 Changes by Senthil
593 PROCEDURE Create_Task_Links
594 (
595 P_task_link_rec IN OUT NOCOPY TASK_LINK_REC
596 )
597
598 IS
599
600 -- Modified by amagrawa
601 CURSOR CHECK_TASK_ID(C_TASK_NUMBER IN NUMBER, C_TASK_ID IN NUMBER)
602 IS
603 select VISIT_TASK_ID from AHL_VISIT_TASKS_B where visit_task_number = C_TASK_NUMBER and
604 visit_id = (select visit_id from ahl_visit_tasks_b where visit_task_id = C_TASK_ID );
605
606 -- Added by tchimira for VWPE 12730539
607 CURSOR VALIDATE_TASK_ID(C_TASK_ID IN NUMBER)
608 IS
609 select VISIT_TASK_ID from AHL_VISIT_TASKS_B
610 where visit_task_id = C_TASK_ID;
611
612 --Added below two lines by tchimira for VWP ER-12424063 on 14-FEB-2011
613 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Task_Links';
614 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
615
616 l_flip_vtid NUMBER;
617 l_return_Status VARCHAR2(1);
618 l_msg_count NUMBER;
619 l_msg_data VARCHAR2(2000);
620 l_visit_task_id NUMBER;
621
622 BEGIN
623
624 -- Select ahl_task_links_s.nextval into l_task_link_id from dual;
625 -- Validate the values entered by the user
626
627 IF(P_task_link_rec.visit_task_number is NULL or length(trim(P_task_link_rec.visit_task_number)) = 0 )
628 THEN --Added by tchimira for VWPE 12730539
629 OPEN VALIDATE_TASK_ID(P_task_link_rec.visit_task_id);
630 FETCH VALIDATE_TASK_ID INTO l_visit_task_id;
631 IF (VALIDATE_TASK_ID%NOTFOUND) THEN
632 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_CONTEXT_TSK_INV');
633 FND_MESSAGE.SET_TOKEN('RECORD',P_task_link_rec.visit_task_number,false);
634 FND_MSG_PUB.ADD;
635 END IF;
636 CLOSE VALIDATE_TASK_ID;
637 ELSE
638 OPEN CHECK_TASK_ID(P_task_link_rec.visit_task_number,P_task_link_rec.visit_task_id);
639 FETCH CHECK_TASK_ID INTO l_visit_task_id;
640 IF (CHECK_TASK_ID%FOUND) THEN
641 P_task_link_rec.parent_task_id := l_visit_task_id;
642 ELSE
643 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_CONTEXT_TSK_INV');
644 FND_MESSAGE.SET_TOKEN('RECORD',P_task_link_rec.visit_task_number,false);
645 FND_MSG_PUB.ADD;
646 END IF;
647 CLOSE Check_Task_ID;
648 END IF;
649
650 l_msg_count := FND_MSG_PUB.count_msg;
651 IF l_msg_count > 0
652 THEN
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656
657
658 --commented the below 6 lines by tchimira for vwp ER-12424063 on 14-FEB-2011
659 /*IF P_task_link_rec.HIERARCHY_INDICATOR='AFTER'
660 THEN
661 l_flip_vtid:=P_task_link_rec.VISIT_TASK_ID;
662 P_task_link_rec.VISIT_TASK_ID:=P_task_link_rec.PARENT_TASK_ID;
663 P_task_link_rec.PARENT_TASK_ID:=l_flip_vtid;
664 END IF;*/
665
666 --Added new 'PRECEDENCE_CONSTRAINT'column by tchimira - VWP ER-12424063 ::14-FEB-2011
667 INSERT INTO AHL_TASK_LINKS(
668 TASK_LINK_ID,
669 OBJECT_VERSION_NUMBER,
670 LAST_UPDATE_DATE,
671 LAST_UPDATED_BY,
672 CREATION_DATE,
673 CREATED_BY,
674 LAST_UPDATE_LOGIN,
675 VISIT_TASK_ID,
676 PARENT_TASK_ID,
677 ATTRIBUTE_CATEGORY,
678 ATTRIBUTE1,
679 ATTRIBUTE2,
680 ATTRIBUTE3,
681 ATTRIBUTE4,
682 ATTRIBUTE5,
683 ATTRIBUTE6,
684 ATTRIBUTE7,
685 ATTRIBUTE8,
686 ATTRIBUTE9,
687 ATTRIBUTE10,
688 ATTRIBUTE11,
689 ATTRIBUTE12,
690 ATTRIBUTE13,
691 ATTRIBUTE14,
692 ATTRIBUTE15,
693 PRECEDENCE_CONSTRAINT)
694 values
695 (
696 ahl_task_links_s.nextval,
697 1,
698 sysdate,
699 fnd_global.user_id,
700 sysdate,
701 fnd_global.user_id,
702 fnd_global.user_id,
703 P_task_link_rec.VISIT_TASK_ID,
704 P_task_link_rec.PARENT_TASK_ID,
705 P_task_link_rec.ATTRIBUTE_CATEGORY,
706 P_task_link_rec.ATTRIBUTE1,
707 P_task_link_rec.ATTRIBUTE2,
708 P_task_link_rec.ATTRIBUTE3,
709 P_task_link_rec.ATTRIBUTE4,
710 P_task_link_rec.ATTRIBUTE5,
711 P_task_link_rec.ATTRIBUTE6,
712 P_task_link_rec.ATTRIBUTE7,
713 P_task_link_rec.ATTRIBUTE8,
714 P_task_link_rec.ATTRIBUTE9,
715 P_task_link_rec.ATTRIBUTE10,
716 P_task_link_rec.ATTRIBUTE11,
717 P_task_link_rec.ATTRIBUTE12,
718 P_task_link_rec.ATTRIBUTE13,
719 P_task_link_rec.ATTRIBUTE14,
720 P_task_link_rec.ATTRIBUTE15,
721 P_task_link_rec.HIERARCHY_INDICATOR);
722
723 NON_CYCLIC_ENF
724 (
725 x_return_status =>l_return_Status,
726 x_msg_count =>l_msg_count,
727 x_msg_data =>l_msg_data,
728 p_visit_Task_id =>P_task_link_rec.visit_Task_id,
729 p_visit_Task_number =>P_task_link_rec.visit_Task_number
730 );
731
732 -- Added by amagrawa based on review comments.
733 IF l_return_Status <>'S' THEN
734 IF l_return_Status = FND_API.G_RET_STS_ERROR THEN
735 RAISE FND_API.G_EXC_ERROR;
736 ELSIF l_return_Status = FND_API.G_RET_STS_UNEXP_ERROR THEN
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 END IF;
739
740 END IF;
741
742 -- VWP ER-12424063:: tchimira:: call validate vwp links after insering the task rule::start
743 -- Validate the Links
744
745 VALIDATE_VWP_LINKS
746 (
747 x_return_status =>l_return_Status,
748 x_msg_count =>l_msg_count,
749 x_msg_data =>l_msg_data,
750 p_task_link_rec =>P_task_link_rec
751 );
752
753 IF l_return_Status <>'S' THEN
754 IF l_return_Status = FND_API.G_RET_STS_ERROR THEN
755 RAISE FND_API.G_EXC_ERROR;
756 ELSIF l_return_Status = FND_API.G_RET_STS_UNEXP_ERROR THEN
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 END IF;
759 END IF;
760 -- VWP ER-12424063:: tchimira:: call validate vwp links after insering the task rule::end
761
762 -- Added cxcheng POST11510--------------
763 --Now adjust the times derivation for the child task
764 AHL_VWP_TIMES_PVT.Adjust_Task_Times(p_api_version => 1.0,
765 p_init_msg_list => Fnd_Api.G_FALSE,
766 p_commit => Fnd_Api.G_FALSE,
767 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
768 x_return_status => l_return_status,
769 x_msg_count => l_msg_count,
770 x_msg_data => l_msg_data,
771 p_task_id => P_task_link_rec.visit_task_id);
772
773
774 -- Added by amagrawa based on review comments.
775 IF l_return_Status <>'S'
776 THEN
777 IF l_return_Status = FND_API.G_RET_STS_ERROR
778 THEN
779 RAISE FND_API.G_EXC_ERROR;
780 ELSIF l_return_Status = FND_API.G_RET_STS_UNEXP_ERROR
781 THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784
785 END IF;
786
787 l_msg_count := FND_MSG_PUB.count_msg;
788 IF l_msg_count > 0
789 THEN
790 RAISE FND_API.G_EXC_ERROR;
791 END IF;
792
793 END Create_Task_Links;
794
795 -- End of Post 11.5.10 Changes by Senthil
796
797 PROCEDURE DELETE_TASK_LINKS
798 ( p_task_link_id IN NUMBER,
799 p_object_version_number IN NUMBER)
800 as
801 --
802 cursor child_task_csr (p_task_link_id IN NUMBER) IS
803 SELECT visit_task_id
804 FROM ahl_task_links
805 WHERE task_link_id = p_task_link_id;
806 --
807 l_return_Status VARCHAR2(1);
808 l_msg_count NUMBER;
809 l_msg_data VARCHAR2(2000);
810 l_child_task_id NUMBER;
811 --
812 BEGIN
813 --Find the child task link
814 OPEN child_task_csr(p_task_link_id);
815 FETCH child_task_csr INTO l_child_task_id;
816 CLOSE child_task_csr;
817
818 DELETE AHL_TASK_LINKS
819 WHERE TASK_LINK_ID=p_task_link_id
820 AND OBJECT_VERSION_NUMBER=p_object_version_number;
821 IF SQL%ROWCOUNT=0
822 THEN
823 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
824 FND_MSG_PUB.ADD;
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827
828
829 -- Added cxcheng POST11510--------------
830 --Now adjust the times derivation for the child task. Parent doesn't matter
831 AHL_VWP_TIMES_PVT.Adjust_Task_Times(p_api_version => 1.0,
832 p_init_msg_list => Fnd_Api.G_FALSE,
833 p_commit => Fnd_Api.G_FALSE,
834 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
835 x_return_status => l_return_status,
836 x_msg_count => l_msg_count,
837 x_msg_data => l_msg_data,
838 p_task_id => l_child_task_id);
839
840 -- Added by amagrawa based on review comments.
841 IF l_return_Status <>'S'
842 THEN
843 IF l_return_Status = FND_API.G_RET_STS_ERROR
844 THEN
845 RAISE FND_API.G_EXC_ERROR;
846 ELSIF l_return_Status = FND_API.G_RET_STS_UNEXP_ERROR
847 THEN
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849 END IF;
850
851 END IF;
852
853 l_msg_count := FND_MSG_PUB.count_msg;
854 IF l_msg_count > 0
855 THEN
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858
859 END DELETE_TASK_LINKS;
860
861 PROCEDURE PROCESS_TASK_LINKS
862 (
863 p_api_version IN NUMBER := 1.0,
864 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
865 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
866 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
867 p_default IN VARCHAR2 := FND_API.G_FALSE,
868 p_module_type IN VARCHAR2 := NULL,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_count OUT NOCOPY NUMBER,
871 x_msg_data OUT NOCOPY VARCHAR2,
872 p_x_task_link_tbl IN OUT NOCOPY TASK_LINK_TBL
873 )
874 as
875 -- for finding the status of a task. Added in 11.5.10
876 -- amagrawa Modified the cursor declaration based on review comments.
877 CURSOR task_status_csr(p_task_id IN NUMBER) IS
878 SELECT STATUS_CODE
879 FROM AHL_VISIT_TASKS_B
880 WHERE VISIT_TASK_ID=p_task_id;
881 -- AND VISIT_TASK_NUMBER= p_task_no ;
882
883 -- Cursor find all details about visit
884 CURSOR c_Visit (p_task_id IN NUMBER) IS
885 SELECT Any_Task_Chg_Flag,visit_id FROM Ahl_Visits_VL
886 WHERE VISIT_ID = (
887 SELECT visit_id FROM AHL_VISIT_TASKS_B
888 WHERE visit_task_id = p_task_id
889 );
890
891 l_visit_csr_rec c_Visit%ROWTYPE;
892
893 -- Cursor to find visit_task_id from task_link_id
894 CURSOR c_task_link (p_task_link_id IN NUMBER) IS
895 SELECT visit_task_id FROM AHL_TASK_LINKS
896 WHERE task_link_id = p_task_link_id;
897
898
899 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_TASK_LINKS';
900 -- Added by amagrawa
901 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
902 l_api_version CONSTANT NUMBER := 1.0;
903 l_msg_count NUMBER;
904 l_msg_data VARCHAR2(2000);
905 l_return_status VARCHAR2(1);
906 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
907 l_commit VARCHAR2(1):= FND_API.G_FALSE;
908 l_TASK_LINK_tbl TASK_LINK_TBL:=p_x_task_link_tbl;
909 L_TASK_LINK_REC TASK_LINK_REC;
910 l_status_code VARCHAR2(30);
911 l_task_id NUMBER;
912 l_counter NUMBER:=0;
913
914 l_planned_order_flag VARCHAR2(1);
915
916 l_visit_end_date DATE;
917
918 BEGIN
919
920 SAVEPOINT PROCESS_TASK_LINKS;
921
922 -- Standard call to check for call compatibility.
923
924 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
925 p_api_version,
926 l_api_name,G_PKG_NAME) THEN
927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
928 END IF;
929
930 -- Initialize message list if p_init_msg_list is set to TRUE.
931 IF FND_API.to_boolean(l_init_msg_list) THEN
932 FND_MSG_PUB.initialize;
933 END IF;
934
935
936 IF G_DEBUG='Y' THEN
937 AHL_DEBUG_PUB.enable_debug;
938 END IF;
939
940 -- Debug info.
941 -- Commented by amagrawa based on review comments
942 -- IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
943 IF G_DEBUG='Y' THEN
944 AHL_DEBUG_PUB.debug( L_FULL_NAME||':Enter VWP_HIERARCHY');
945 END IF;
946 -- END IF;
947
948 -- AHL_DEBUG_PUB.enable_debug;
949
950
951 -- Initialize API return status to success
952
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954
955
956 --Start of API Body
957
958 if p_x_task_link_tbl.count >0
959 then
960
961 FOR i IN P_X_TASK_LINK_TBL.FIRST.. P_X_TASK_LINK_TBL.LAST
962 LOOP
963
964 IF P_X_TASK_LINK_TBL(I).DML_operation<>'D'
965 THEN
966
967 l_TASK_LINK_rec.object_version_number :=l_TASK_LINK_tbl(i).object_version_number;
968 l_TASK_LINK_rec.visit_task_id :=l_TASK_LINK_tbl(i).visit_task_id;
969 -- Commented by amagrawa based on review comments.
970 -- l_TASK_LINK_rec.parent_task_id :=l_TASK_LINK_tbl(i).parent_task_id;
971 l_TASK_LINK_rec.hierarchy_indicator :=l_TASK_LINK_tbl(i).hierarchy_indicator;
972 l_TASK_LINK_rec.visit_task_nUMBER :=l_TASK_LINK_tbl(i).visit_task_number;
973 l_TASK_LINK_rec.visit_task_id :=l_TASK_LINK_tbl(i).visit_task_id;
974 l_TASK_LINK_rec.dml_operation :=l_TASK_LINK_tbl(i).DML_OPERATION;
975
976 TRANS_VALUE_ID
977 (
978 x_return_status =>x_return_Status,
979 x_msg_count =>l_msg_count,
980 x_msg_data =>l_msg_data,
981 P_X_TASK_LINK_rec => l_TASK_LINK_rec);
982
983 -- Added by amagrawa based on review comments.
984 IF x_return_Status <>'S'
985 THEN
986 IF x_return_Status = FND_API.G_RET_STS_ERROR
987 THEN
988 RAISE FND_API.G_EXC_ERROR;
989 ELSIF x_return_Status = FND_API.G_RET_STS_UNEXP_ERROR
990 THEN
991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
992 END IF;
993 END IF;
994
995 l_TASK_LINK_tbl(i).parent_task_id:=l_TASK_LINK_rec.parent_task_id;
996 l_TASK_LINK_tbl(i).visit_task_name:=l_TASK_LINK_rec.visit_task_name;
997 -- Commented by amagrawa based on review comments.
998 -- l_TASK_LINK_tbl(i).visit_task_number:=l_TASK_LINK_rec.visit_task_number;
999 END IF;
1000
1001 END LOOP;
1002 end if;
1003
1004 -- Commented by amagrawa based on review comments.
1005 /* l_msg_count := FND_MSG_PUB.count_msg;
1006 IF l_msg_count > 0 THEN
1007 X_msg_count := l_msg_count;
1008 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009 RAISE FND_API.G_EXC_ERROR;
1010 END IF;
1011 */
1012 IF G_DEBUG='Y' THEN
1013 AHL_DEBUG_PUB.debug( L_FULL_NAME||' After Translate','+VWP_HIERARCHY+');
1014 END IF;
1015
1016 IF P_X_TASK_LINK_TBL.COUNT >0
1017 THEN
1018
1019
1020 DEFAULT_MISSING_ATTRIBS
1021 (
1022 p_x_TASK_LINK_TBL =>L_TASK_LINK_tbl
1023 );
1024
1025 END IF;
1026
1027 -- Calling for Validation
1028
1029 if p_x_task_link_tbl.count >0
1030 then
1031 l_counter:= p_x_task_link_tbl.first;
1032 l_task_id := p_x_task_link_tbl(l_counter).visit_task_id;
1033
1034 IF l_task_id IS NULL and (p_x_task_link_tbl(l_counter).DML_OPERATION) = 'D' THEN
1035 OPEN c_task_link(p_x_task_link_tbl(l_counter).task_link_id);
1036 FETCH c_task_link INTO l_task_id;
1037 CLOSE c_task_link;
1038 END IF;
1039
1040 IF G_DEBUG='Y' THEN
1041 AHL_DEBUG_PUB.debug( L_FULL_NAME||' Task Hierarchy l_task_id -> '||l_task_id);
1042 END IF;
1043
1044 FOR i IN l_TASK_LINK_TBL.FIRST.. l_TASK_LINK_TBL.LAST
1045 LOOP
1046 OPEN task_status_csr(l_TASK_LINK_tbl(i).parent_task_id);
1047 FETCH task_status_csr INTO l_status_code;
1048 CLOSE task_status_csr;
1049
1050 IF l_status_code <>'PLANNING' THEN
1051
1052 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_INV_TASK_STATUS');
1053 FND_MESSAGE.SET_TOKEN('RECORD',l_TASK_LINK_tbl(i).visit_task_number ,false);
1054 FND_MSG_PUB.ADD;
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056
1057 ELSE
1058 l_TASK_LINK_rec.object_version_number :=l_TASK_LINK_tbl(i).object_version_number;
1059 l_TASK_LINK_rec.visit_task_id :=l_TASK_LINK_tbl(i).visit_task_id;
1060 l_TASK_LINK_rec.parent_task_id :=l_TASK_LINK_tbl(i).parent_task_id;
1061 l_TASK_LINK_rec.hierarchy_indicator :=l_TASK_LINK_tbl(i).hierarchy_indicator;
1062 l_TASK_LINK_rec.visit_task_nUMBER :=l_TASK_LINK_tbl(i).visit_task_number;
1063 l_TASK_LINK_rec.visit_task_id :=l_TASK_LINK_tbl(i).visit_task_id;
1064 l_TASK_LINK_rec.dml_operation :=l_TASK_LINK_tbl(i).DML_OPERATION;
1065
1066
1067 -- IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
1068 -- THEN
1069 /* IF l_TASK_LINK_TBL(i).DML_OPERATION<>'D'
1070 THEN
1071
1072 VALIDATE_VWP_LINKS
1073 (
1074 x_return_status =>x_return_Status,
1075 x_msg_count =>l_msg_count,
1076 x_msg_data =>l_msg_data,
1077 p_task_link_rec =>l_TASK_LINK_rec);
1078
1079 END IF;
1080 */
1081
1082
1083
1084 IF l_TASK_LINK_TBL(i).DML_OPERATION='D'
1085 THEN
1086
1087 -- Post 11.5.10 Changes by Senthil
1088 DELETE_TASK_LINKS
1089 (
1090 p_task_link_id => l_task_link_tbl(i).task_link_id,
1091 p_object_version_number => l_task_link_tbl(i).object_version_number
1092 );
1093
1094 /* DELETE AHL_TASK_LINKS WHERE TASK_LINK_ID=l_task_link_tbl(i).task_link_id
1095 AND OBJECT_VERSION_NUMBER=l_task_link_tbl(i).OBJECT_VERSION_NUMBER;
1096 IF SQL%ROWCOUNT=0
1097 THEN
1098 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1099 FND_MSG_PUB.ADD;
1100 END IF;
1101 */
1102
1103 -- End of Post 11.5.10 Changes by Senthil
1104 ELSIF P_X_TASK_LINK_tbl(i).DML_operation='C'
1105 THEN
1106 -- Post 11.5.10 Changes by Senthil
1107 Create_Task_Links ( p_task_link_rec =>P_X_TASK_LINK_tbl(i));
1108 -- End of Post 11.5.10 Changes by Senthil
1109 END IF;
1110 END IF;
1111 END LOOP;
1112
1113
1114 OPEN C_VISIT(l_task_id);
1115 fetch c_visit into l_visit_csr_rec;
1116 IF C_VISIT%FOUND THEN
1117 IF p_module_type = 'JSP' and P_X_TASK_LINK_tbl.count > 0 THEN
1118
1119 l_visit_end_date:= AHL_VWP_TIMES_PVT.get_visit_end_time(l_visit_csr_rec.visit_id);
1120
1121 IF l_visit_end_date IS NOT NULL THEN
1122
1123 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials
1124 (p_api_version => p_api_version,
1125 p_init_msg_list => Fnd_Api.G_FALSE,
1126 p_commit => Fnd_Api.G_FALSE,
1127 p_visit_id => l_visit_csr_rec.visit_id,
1128 p_visit_task_id => NULL,
1129 p_org_id => NULL,
1130 p_start_date => NULL,
1131 p_operation_flag => 'U',
1132 x_planned_order_flag => l_planned_order_flag ,
1133 x_return_status => l_return_status,
1134 x_msg_count => l_msg_count,
1135 x_msg_data => l_msg_data);
1136
1137
1138
1139 IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1140 X_msg_count := l_msg_count;
1141 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1142 RAISE Fnd_Api.G_EXC_ERROR;
1143 END IF;
1144
1145 END IF;
1146
1147 END IF;
1148
1149 IF l_visit_csr_rec.Any_Task_Chg_Flag='N'
1150 THEN
1151 AHL_VWP_RULES_PVT.update_visit_task_flag(
1152 p_visit_id =>l_visit_csr_rec.visit_id,
1153 p_flag =>'Y',
1154 x_return_status =>x_return_status);
1155 END IF;
1156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1157 CLOSE C_VISIT;
1158 RAISE FND_API.G_EXC_ERROR;
1159 END IF;
1160 END IF;
1161 CLOSE C_VISIT;
1162
1163 END IF;
1164 l_msg_count := FND_MSG_PUB.count_msg;
1165 IF l_msg_count > 0 THEN
1166 X_msg_count := l_msg_count;
1167 X_return_status := FND_API.G_RET_STS_ERROR;
1168 RAISE FND_API.G_EXC_ERROR;
1169 END IF;
1170
1171
1172 IF FND_API.TO_BOOLEAN(p_commit) THEN
1173 COMMIT;
1174 END IF;
1175 -- Debug info
1176
1177 IF G_DEBUG='Y' THEN
1178 AHL_DEBUG_PUB.debug( 'End of Private api PROCESS_TASK_LINKS','+visit_Task_id+');
1179 END IF;
1180
1181 -- Check if API is called in debug mode. If yes, disable debug.
1182
1183 IF G_DEBUG='Y' THEN
1184 AHL_DEBUG_PUB.disable_debug;
1185 END IF;
1186
1187 EXCEPTION
1188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1189 ROLLBACK TO PROCESS_TASK_LINKS;
1190 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1191 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1192 p_count => x_msg_count,
1193 p_data => x_msg_data);
1194
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 ROLLBACK TO PROCESS_TASK_LINKS;
1197 X_return_status := FND_API.G_RET_STS_ERROR;
1198 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1199 p_count => x_msg_count,
1200 p_data => X_msg_data);
1201 WHEN OTHERS THEN
1202 ROLLBACK TO PROCESS_TASK_LINKS;
1203 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1204 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1205 THEN
1206 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_VWP_TASKS_LINKS_PVT',
1207 p_procedure_name => 'PROCESS_TASK_LINKS',
1208 p_error_text => SUBSTR(SQLERRM,1,240));
1209 END IF;
1210 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1211 p_count => x_msg_count,
1212 p_data => X_msg_data);
1213 END;
1214
1215 END AHL_VWP_TASKS_LINKS_PVT;
[Home] [Help]