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