[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_APPROVAL_PVT
Source
1 PACKAGE BODY AHL_RM_APPROVAL_PVT AS
2 /* $Header: AHLVROAB.pls 120.5.12020000.2 2012/12/07 13:39:32 sareepar ship $ */
3 G_PKG_NAME VARCHAR2(30):='AHL_RM_APPROVAL_PVT';
4 G_OBJECT_TYPE VARCHAR2(30):='RM_NTF';
5 G_PM_INSTALL VARCHAR2(30):=ahl_util_pkg.is_pm_installed;
6
7 -- Local Api
8 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
9 PROCEDURE NOTIFY_TERMINATION
10 (
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_object_type IN VARCHAR2,
15 p_prim_object_type IN VARCHAR2,
16 p_activity_id IN NUMBER
17 )
18 AS
19 CURSOR CursorNotify
20 IS
21 SELECT A.APPROVAL_RULE_ID,
22 A.APPROVAL_OBJECT_CODE,
23 A.STATUS_CODE,
24 B.APPROVER_NAME,
25 B.APPROVER_SEQUENCE
26 FROM AHL_APPROVAL_RULES_Vl A,AHL_APPROVERS_V B
27 WHERE A.APPROVAL_RULE_ID=B.APPROVAL_RULE_ID
28 AND A.STATUS_CODE='ACTIVE'
29 AND A.APPROVAL_OBJECT_CODE=p_object_type
30 ORDER BY B.APPROVER_SEQUENCE;
31
32 l_rec CursorNotify%rowtype;
33
34 CURSOR CurRoute
35 is
36 SELECT C.*,D.VISIT_NUMBER
37 FROM AHL_ROUTES_APP_V A,AHL_MR_ROUTES B,AHL_VISIT_TASKS_B C,AHL_VISITS_VL D
38 WHERE A.ROUTE_ID=p_activity_id
39 AND A.ROUTE_ID=B.ROUTE_ID
40 AND B.MR_ROUTE_ID=C.MR_ROUTE_ID
41 AND C.VISIT_ID=D.VISIT_ID
42 AND D.STATUS_CODE<>'CLOSED';
43
44
45 CURSOR CurRouteDet
46 is
47 SELECT *
48 FROM AHL_ROUTES_VL
49 WHERE ROUTE_ID=p_activity_id;
50
51 l_route_Rec CurRouteDet%rowtype;
52
53
54 CURSOR CurOper
55 is
56 SELECT C.*,D.VISIT_NUMBER
57 FROM AHL_ROUTES_APP_V A,AHL_MR_ROUTES B,AHL_VISIT_TASKS_B C,AHL_VISITS_VL D,AHL_ROUTE_OPERATIONS E
58 WHERE A.ROUTE_ID=B.ROUTE_ID
59 AND A.ROUTE_ID=E.ROUTE_ID
60 AND B.MR_ROUTE_ID=C.MR_ROUTE_ID
61 AND C.VISIT_ID=D.VISIT_ID
62 AND E.OPERATION_ID=p_activity_id
63 AND D.STATUS_CODE<>'CLOSED';
64
65 CURSOR CurOperDet
66 is
67 SELECT *
68 FROM AHL_OPERATIONS_B_KFV
69 WHERE OPERATION_ID=p_activity_id;
70
71 l_oper_Rec CurOperDet%rowtype;
72
73 CURSOR CurFullname(C_USERNAME VARCHAR2)
74 IS
75 SELECT * FROM AHL_JTF_RS_EMP_V
76 WHERE USER_NAME=C_USERNAME;
77
78 l_item_type VARCHAR2(30) := 'AHLGAPP';
79 l_message_name VARCHAR2(200) := 'GEN_STDLN_MESG';
80 l_subject VARCHAR2(3000);
81 l_body VARCHAR2(3000) := NULL;
82 l_send_to_role_name VARCHAR2(30):= NULL;
83 l_send_to_res_id NUMBER:= NULL;
84 l_notif_id NUMBER;
85 l_notif_id1 NUMBER;
86 l_return_status VARCHAR2(1);
87 l_role_name VARCHAR2(100);
88 l_display_role_name VARCHAR2(240);
89 l_object_notes VARCHAR2(400);
90 l_counter NUMBER:=0;
91 BEGIN
92 SAVEPOINT NOTIFY_TERMINATION;
93
94 IF G_DEBUG='Y' THEN
95 AHL_DEBUG_PUB.enable_debug;
96 AHL_DEBUG_PUB.debug( 'Enter Notifications','+RM_NOTIFY+');
97 END IF;
98
99 IF p_prim_object_type='RM'
100 THEN
101 OPEN CurRouteDet;
102 FETCH CurRouteDet INTO l_route_rec;
103 CLOSE CurRouteDet;
104
105 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_TERMINATE_NTF_SUB');
106 FND_MESSAGE.set_token('ROUTE',l_route_Rec.ROUTE_NO,false);
107 FND_MESSAGE.set_token('REVISION',l_route_Rec.REVISION_NUMBER, false);
108 l_subject:=fnd_message.get;
109
110 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_TERM_NTF_CONTENT');
111 l_body:=l_subject||'-'||fnd_message.get;
112
113
114 FOR l_visit_rec in CurRoute
115 LOOP
116 l_body:=l_subject;
117 l_body:=l_body||'-'||l_visit_rec.visit_number||','||l_visit_rec.visit_task_number;
118 l_counter:=l_counter+1;
119 end loop;
120
121 if l_counter>0
122 then
123
124 FOR XREC IN CursorNotify
125 LOOP
126
127 l_role_name:=xrec.approver_name;
128
129 l_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 l_notif_id := WF_NOTIFICATION.Send
132 ( role => l_role_name
133 , msg_type => l_item_type
134 , msg_name => l_message_name
135 );
136
137 WF_NOTIFICATION.SetAttrText(l_notif_id,
138 'GEN_MSG_SUBJECT',
139 l_subject);
140
141 WF_NOTIFICATION.SetAttrText(l_notif_id,
142 'GEN_MSG_BODY',
143 l_body);
144
145 WF_NOTIFICATION.SetAttrText(l_notif_id,
146 'GEN_MSG_SEND_TO',
147 l_role_name);
148
149 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
150 end loop;
151 end if;
152 else
153 OPEN CurOperDet;
154 FETCH CurOperDet INTO l_Oper_rec;
155 CLOSE CurOperDet;
156
157
158 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_TERMINATE_NTF_SUB');
159 FND_MESSAGE.set_token('SEGMENT_COMB',l_oper_rec.concatenated_segments,false);
160 FND_MESSAGE.set_token('REVISION',l_oper_Rec.REVISION_NUMBER, false);
161 l_subject:=fnd_message.get;
162
163 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_TERM_NTF_CONTENT');
164 l_body:=l_subject||'-'||fnd_message.get;
165
166
167
168 FOR l_visit_rec in CurOper
169 LOOP
170 l_body:=l_subject;
171 l_body:=l_body||'-'||l_visit_rec.visit_number||','||l_visit_rec.visit_task_number;
172 l_counter:=l_counter+1;
173 END LOOP;
174 IF l_counter>0
175 THEN
176 FOR XREC IN CursorNotify
177 LOOP
178
179 l_role_name:=xrec.approver_name;
180
181 l_return_status := FND_API.G_RET_STS_SUCCESS;
182
183 l_notif_id := WF_NOTIFICATION.Send
184 ( role => l_role_name
185 , msg_type => l_item_type
186 , msg_name => l_message_name
187 );
188
189 WF_NOTIFICATION.SetAttrText( l_notif_id
190 , 'GEN_MSG_SUBJECT'
191 , l_subject
192 );
193 WF_NOTIFICATION.SetAttrText( l_notif_id
194 , 'GEN_MSG_BODY'
195 , l_body
196 );
197 WF_NOTIFICATION.SetAttrText( l_notif_id
198 , 'GEN_MSG_SEND_TO'
199 , l_role_name
200 );
201 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
202 end loop;
203 END IF;
204 end if;
205
206
207 EXCEPTION
208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
209 ROLLBACK TO NOTIFY_TERMINATION;
210 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
212 p_count => x_msg_count,
213 p_data => x_msg_data);
214
215 WHEN FND_API.G_EXC_ERROR THEN
216 ROLLBACK TO NOTIFY_TERMINATION;
217 X_return_status := FND_API.G_RET_STS_ERROR;
218 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
219 p_count => x_msg_count,
220 p_data => X_msg_data);
221 WHEN OTHERS THEN
222 ROLLBACK TO NOTIFY_TERMINATION;
223 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
225 THEN
226 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
227 p_procedure_name => 'NOTIFY_TERMINATION',
228 p_error_text => SUBSTR(SQLERRM,1,240));
229 END IF;
230 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
231 p_count => x_msg_count,
232 p_data => X_msg_data);
233 END;
234
235 PROCEDURE INITIATE_OPER_APPROVAL
236 (
237 p_api_version IN NUMBER:= 1.0,
238 p_init_msg_list IN VARCHAR2,
239 p_commit IN VARCHAR2,
240 p_validation_level IN NUMBER,
241 p_default IN VARCHAR2 := FND_API.G_FALSE,
242 p_module_type IN VARCHAR2,
243 x_return_status OUT NOCOPY VARCHAR2,
244 x_msg_count OUT NOCOPY NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2,
246 p_source_operation_id IN NUMBER,
247 p_object_Version_number IN NUMBER,
248 p_apprvl_type IN VARCHAR2)
249 AS
250 l_counter NUMBER:=0;
251 l_status VARCHAR2(30);
252 l_object VARCHAR2(30):='OPER';
253 l_approval_type VARCHAR2(100):='CONCEPT';
254 l_active VARCHAR2(50) := 'N';
255 l_process_name VARCHAR2(50);
256 l_item_type VARCHAR2(50);
257 l_return_status VARCHAR2(50);
258 l_msg_count NUMBER;
259 l_msg_data VARCHAR2(2000);
260 l_activity_id NUMBER:=p_source_OPERATION_id;
261 l_Status VARCHAR2(1);
262 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
263 l_object_Version_number NUMBER:=nvl(p_object_Version_number,0);
264
265 l_upd_status VARCHAR2(50);
266 l_rev_status VARCHAR2(50);
267 --bachandr Enigma Phase I changes -- start
268
269 l_enig_op_id VARCHAR2(80);
270 --bachandr Enigma Phase I changes -- end
271
272 -- Contains value for approval workflow setup.
273 l_OPER_APPR_ENABLED VARCHAR2(10);
274
275
276 CURSOR get_oper_Det(c_operation_id NUMBER)
277 Is
278 Select revision_status_code,
279 revision_number,
280 start_date_active,
281 end_date_active,
282 concatenated_segments
283 From ahl_operations_b_KFV
284 Where operation_id = c_operation_id;
285
286 l_oper_rec get_oper_Det%rowtype;
287
288 CURSOR get_oper_Det2(c_operation_id NUMBER)
289 Is
290 Select revision_status_code,
291 revision_number,
292 start_date_active,
293 end_date_active,
294 concatenated_segments
295 From ahl_operations_b_kfv
296 Where operation_id = c_operation_id;
297
298 -- Bug # 8639648 -- start
299 -- Fetches all active routes which has time span greater than
300 -- operation duration.
301 CURSOR get_asso_active_routes(c_oper_id IN NUMBER, c_op_duration IN NUMBER)
302 IS
303 SELECT rou.route_id
304 FROM AHL_ROUTES_APP_V rou ,
305 ahl_route_operations arop,
306 ahl_operations_b oper
307 WHERE (
308 TRUNC(rou.start_date_active) >= TRUNC(oper.start_date_active)
309 AND
310 TRUNC(NVL(oper.end_date_active, rou.start_date_active + 1)) > TRUNC(rou.start_date_active)
311 )
312 AND TRUNC( NVL( rou.end_date_active , SYSDATE + 1 )) > TRUNC( SYSDATE )
313 AND rou.time_span < c_op_duration
314 AND rou.route_id = arop.route_id
315 AND oper.operation_id = arop.operation_id
316 AND arop.operation_id = c_oper_id
317 AND rownum < 2;
318
319 l_route_id NUMBER;
320
321 -- cursor for deriving operation duration.
322 CURSOR get_op_max_duration( c_operation_id NUMBER )
323 IS
324 SELECT NVL(SUM(RES_DURATION),0)
325 FROM ( SELECT MAX( duration ) RES_DURATION
326 FROM AHL_RT_OPER_RESOURCES
327 WHERE association_type_code = 'OPERATION'
328 AND scheduled_type_id = 1
329 AND object_id = c_operation_id
330 GROUP BY schedule_seq
331 );
332
333 l_op_duration NUMBER;
334
335 -- Bug # 8639648 -- end
336
337 l_oper_rec1 get_oper_Det2%rowtype;
338 l_msg VARCHAR2(30);
339 l_start_date DATE;
340 BEGIN
341 SAVEPOINT INITIATE_OPER_APPROVAL;
342
343 -- Check if API is called in debug mode. If yes, enable debug.
344
345 IF G_DEBUG='Y' THEN
346 AHL_DEBUG_PUB.enable_debug;
347 AHL_DEBUG_PUB.debug( 'Enter Initiate OPERATION Approval..','+HEADERS+');
348 END IF;
349
350 -- Standard call to check for call compatibility.
351
352 IF FND_API.to_boolean(l_init_msg_list) THEN
353 FND_MSG_PUB.initialize;
354 END IF;
355
356 -- Initialize API return status to success
357
358 x_return_status :=FND_API.G_RET_STS_SUCCESS;
359
360 -- Get approval process setup.
361 IF (G_PM_INSTALL = 'Y') THEN
362 l_OPER_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_OPERATION_APPRV_ENABLED'), 'N');
363 ELSE
364 l_OPER_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_OPERATION_APPRV_ENABLED'), 'Y');
365 END IF;
366
367 -- Bug # 8639648 -- start
368 OPEN get_op_max_duration(p_source_operation_id);
369 FETCH get_op_max_duration INTO l_op_duration;
370 CLOSE get_op_max_duration;
371
372 OPEN get_asso_active_routes(p_source_operation_id, l_op_duration);
373 FETCH get_asso_active_routes INTO l_route_id;
374 CLOSE get_asso_active_routes;
375
376 IF l_route_id IS NOT NULL
377 THEN
378 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_DUR_GT_ROU');
379 FND_MESSAGE.set_token('DURATION',l_op_duration);
380 FND_MSG_PUB.ADD;
381 END IF;
382 -- Bug # 8639648 -- end
383
384 -- Start work Flow Process
385 IF l_OPER_APPR_ENABLED = 'Y'
386 THEN
387 ahl_utility_pvt.get_wf_process_name(
388 p_object =>l_object,
389 x_active =>l_active,
390 x_process_name =>l_process_name ,
391 x_item_type =>l_item_type,
392 x_return_status=>l_return_status,
393 x_msg_count =>l_msg_count,
394 x_msg_data =>l_msg_data);
395 END IF ;
396 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
397 THEN
398 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJ_VERSION_NULL');
399 FND_MSG_PUB.ADD;
400 END IF;
401
402 IF p_source_operation_id is null or p_source_operation_id=FND_API.G_MISS_NUM
403 THEN
404 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJECT_ID_NULL');
405 FND_MSG_PUB.ADD;
406 ELSE
407 OPEN get_oper_Det(p_source_operation_id);
408 FETCH get_oper_Det INTO l_oper_Rec;
409 CLOSE get_oper_Det;
410
411 IF p_apprvl_type = 'APPROVE'
412 THEN
413 IF l_oper_rec.revision_status_code='DRAFT' or
414 l_oper_rec.revision_status_code='APPROVAL_REJECTED'
415 THEN
416 l_upd_status := 'APPROVAL_PENDING';
417 ELSE
418 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_STAT_NOT_DRFT');
419 FND_MESSAGE.set_token('FIELD',l_oper_rec.concatenated_Segments,false);
420 FND_MSG_PUB.ADD;
421 END IF;
422
423 AHL_RM_ROUTE_UTIL.Validate_rt_oper_start_date
424 (
425 p_object_id =>p_source_operation_id,
426 p_association_type =>'OPERATION',
427 p_start_date =>l_oper_rec.start_date_active,
428 x_start_date =>l_start_date,
429 x_msg_data =>l_msg,
430 x_return_status =>l_return_Status
431 );
432
433 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
434 THEN
435 FND_MESSAGE.SET_NAME('AHL',l_msg);
436 FND_MESSAGE.set_token('FIELD',l_start_Date);
437 FND_MSG_PUB.ADD;
438 IF G_DEBUG='Y' THEN
439 AHL_DEBUG_PUB.debug( 'AHL_rm_util.Validate_rt_oper_start_date','+OPER+');
440 END IF;
441 ELSIF TRUNC(l_oper_rec.START_DATE_ACTIVE)<TRUNC(SYSDATE)
442 THEN
443 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INVALID_ST_DATE');
444 FND_MESSAGE.set_token('FIELD',TRUNC(sysdate));
445 FND_MSG_PUB.ADD;
446 END IF;
447 ELSIF p_apprvl_type = 'TERMINATE'
448 THEN
449 --bachandr Enigma Phase I changes -- start
450 Select ENIGMA_OP_ID into l_enig_op_id
451 From ahl_operations_b
452 Where operation_id = p_source_operation_id;
453
454 IF ( l_enig_op_id is not null)
455 THEN
456 --if the operation is from enigma do not allow termination.
457 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_ENIG_TERM');
458 FND_MSG_PUB.ADD;
459
460 END IF;
461 --bachandr Enigma Phase I changes -- end
462
463 IF(
464 l_oper_rec.revision_status_code = 'TERMINATED'
465 )
466 THEN
467 -- if the operation is terminated
468 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_INV_TERMT');
469 FND_MSG_PUB.ADD;
470
471 ELSIF
472 (
473 ( l_oper_rec.END_DATE_ACTIVE IS NOT NULL OR
474 l_oper_rec.END_DATE_ACTIVE<>FND_API.G_MISS_DATE )
475 AND
476 l_oper_rec.revision_status_code = 'COMPLETE'
477 )
478 THEN
479 -- if the operation is coplete and end dated.
480 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_INACTIVE');
481 FND_MSG_PUB.ADD;
482 ELSE
483 -- for all other cases throw all errors;
484 IF l_oper_rec.revision_status_code='COMPLETE' or
485 l_oper_rec.revision_status_code='APPROVAL_TERMINATED'
486 THEN
487 l_upd_status := 'TERMINATION_PENDING';
488
489 SELECT COUNT(*) into l_counter
490 FROM AHL_OPERATIONS_B_KFV
491 WHERE CONCATENATED_SEGMENTS=l_oper_Rec.CONCATENATED_SEGMENTS
492 AND REVISION_NUMBER=l_oper_Rec.revision_number+1;
493
494 IF l_counter>0
495 THEN
496 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_C_TERM');
497 FND_MSG_PUB.ADD;
498 END IF;
499 ELSE
500 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_STAT_NOT_COMP');
501 FND_MESSAGE.set_token('FIELD',l_oper_rec.concatenated_Segments,false);
502 FND_MSG_PUB.ADD;
503 END IF;
504 END IF;
505 END IF;
506
507 END IF;
508
509 l_msg_count := FND_MSG_PUB.count_msg;
510
511 IF l_msg_count > 0
512 THEN
513 X_msg_count := l_msg_count;
514 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518 IF l_ACTIVE='Y'
519 THEN
520 Update AHL_OPERATIONS_B
521 Set REVISION_STATUS_CODE=l_upd_status,
522 OBJECT_VERSION_number=OBJECT_VERSION_number+1
523 Where OPERATION_ID=p_source_operation_id
524 and OBJECT_VERSION_NUMBER=p_object_Version_number;
525
526 IF sql%rowcount=0
527 THEN
528 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
529 FND_MSG_PUB.ADD;
530 END IF;
531
532 Update AHL_OPERATIONS_TL
533 Set APPROVER_NOTE=null
534 Where OPERATION_ID=p_source_operation_id;
535
536 IF sql%rowcount=0
537 THEN
538 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
539 FND_MSG_PUB.ADD;
540 END IF;
541
542 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
543 P_OBJECT =>l_object,
544 P_ACTIVITY_ID =>l_activity_id,
545 P_APPROVAL_TYPE =>'CONCEPT',
546 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
547 P_ORIG_STATUS_CODE =>'ACTIVE',
548 P_NEW_STATUS_CODE =>'APPROVED',
549 P_REJECT_STATUS_CODE =>'REJECTED',
550 P_REQUESTER_USERID =>fnd_global.user_id,
551 P_NOTES_FROM_REQUESTER =>NULL,
552 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
553 P_ITEM_TYPE =>'AHLGAPP');
554
555 ELSE
556 UPDATE AHL_OPERATIONS_B
557 SET REVISION_STATUS_CODE=l_upd_status,
558 OBJECT_VERSION_number=OBJECT_VERSION_number+1
559 WHERE OPERATION_ID=p_source_OPERATION_id
560 and OBJECT_VERSION_NUMBER=p_object_Version_number;
561
562
563 IF sql%rowcount=0
564 THEN
565 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
566 FND_MSG_PUB.ADD;
567 END IF;
568 COMPLETE_OPER_REVISION
569 (
570 p_api_version =>p_api_version,
571 p_init_msg_list =>p_init_msg_list,
572 p_commit =>FND_API.G_FALSE,
573 p_validation_level =>p_validation_level ,
574 p_default =>p_default ,
575 p_module_type =>p_module_type,
576 x_return_status =>l_return_status,
577 x_msg_count =>x_msg_count ,
578 x_msg_data =>x_msg_data ,
579 p_appr_status =>'APPROVED',
580 p_operation_id =>p_source_operation_id,
581 p_object_version_number =>l_object_version_number+1,
582 p_approver_note =>null
583 );
584
585 IF G_DEBUG='Y'
586 THEN
587 AHL_DEBUG_PUB.debug( 'After CompleteOperRevision');
588 END IF;
589 END IF ;
590
591
592 l_msg_count := FND_MSG_PUB.count_msg;
593
594 IF l_msg_count > 0
595 THEN
596 X_msg_count := l_msg_count;
597 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600
601 IF FND_API.TO_BOOLEAN(p_commit) THEN
602 COMMIT;
603 END IF;
604 EXCEPTION
605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
606 ROLLBACK TO INITIATE_OPER_APPROVAL;
607 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
609 p_count => x_msg_count,
610 p_data => x_msg_data);
611
612 WHEN FND_API.G_EXC_ERROR THEN
613 ROLLBACK TO INITIATE_OPER_APPROVAL;
614 X_return_status := FND_API.G_RET_STS_ERROR;
615 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
616 p_count => x_msg_count,
617 p_data => X_msg_data);
618 WHEN OTHERS THEN
619 ROLLBACK TO INITIATE_OPER_APPROVAL;
620 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
621 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
622 THEN
623 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
624 p_procedure_name => 'INITIATE_OPER_APPROVAL',
625 p_error_text => SUBSTR(SQLERRM,1,240));
626 END IF;
627 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
628 p_count => x_msg_count,
629 p_data => X_msg_data);
630 END;
631
632 PROCEDURE INITIATE_ROUTE_APPROVAL
633 (
634 p_api_version IN NUMBER:= 1.0,
635 p_init_msg_list IN VARCHAR2,
636 p_commit IN VARCHAR2,
637 p_validation_level IN NUMBER,
638 p_default IN VARCHAR2 := FND_API.G_FALSE,
639 p_module_type IN VARCHAR2,
640 x_return_status OUT NOCOPY VARCHAR2,
641 x_msg_count OUT NOCOPY NUMBER,
642 x_msg_data OUT NOCOPY VARCHAR2,
643 p_source_route_id IN NUMBER,
644 p_object_Version_number IN NUMBER,
645 p_apprvl_type IN VARCHAR2
646 )
647 AS
648 CURSOR get_route_det(c_route_id NUMBER)
649 IS
650 SELECT *
651 FROM AHL_ROUTES_APP_V
652 WHERE route_id = c_route_id;
653 l_route_rec get_route_det%rowtype;
654 l_msg VARCHAR2(30);
655 l_start_date DATE;
656 l_counter NUMBER:=0;
657 l_status VARCHAR2(30);
658 l_object VARCHAR2(30):='RM';
659 l_approval_type VARCHAR2(100):='CONCEPT';
660 l_active VARCHAR2(50) := 'N';
661 l_process_name VARCHAR2(50);
662 l_item_type VARCHAR2(50);
663 l_return_status VARCHAR2(50);
664 l_msg_count NUMBER;
665 l_msg_data VARCHAR2(2000);
666 l_activity_id NUMBER:=p_source_route_id;
667 l_Status VARCHAR2(1);
668 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
669 l_object_Version_number NUMBER:=nvl(p_object_Version_number,0);
670 l_upd_status VARCHAR2(50);
671 l_rev_status VARCHAR2(50);
672 l_new_status VARCHAR2(50);
673
674 l_ROUTE_APPR_ENABLED VARCHAR2(30);
675 --bachandr Enigma Phase I changes -- start
676 --snarkhed modified for Enigma Phase II
677 l_enigma_route_id VARCHAR2(30);
678 --bachandr Enigma Phase I changes -- end
679
680 BEGIN
681 SAVEPOINT INITIATE_ROUTE_APPROVAL;
682
683 -- Check if API is called in debug mode. If yes, enable debug.
684
685 IF G_DEBUG='Y' THEN
686 AHL_DEBUG_PUB.enable_debug;
687 AHL_DEBUG_PUB.debug( 'Enter Initiate Route Approval','+ROUTES+');
688 AHL_DEBUG_PUB.debug( 'p_approval_type'||p_apprvl_type,'+ROUTES+');
689 END IF;
690
691 -- Standard call to check for call compatibility.
692
693 IF FND_API.to_boolean(l_init_msg_list) THEN
694 FND_MSG_PUB.initialize;
695 END IF;
696
697 -- Initialize API return status to success
698
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 -- Set default for CMRO and PM modes.
702 IF (G_PM_INSTALL = 'Y') THEN
703 l_ROUTE_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_ROUTE_APPRV_ENABLED'),'N');
704 ELSE
705 l_ROUTE_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_ROUTE_APPRV_ENABLED'),'Y');
706 END IF;
707
708
709 --Before calling ahl_utility_pvt.get_wf_process_name()
710 -- Validate Application Usage
711 AHL_RM_ROUTE_UTIL .validate_ApplnUsage
712 (
713 p_object_id => p_source_route_id,
714 p_association_type => 'ROUTE',
715 x_return_status => x_return_status,
716 x_msg_data => x_msg_data
717 );
718
719 -- If any severe error occurs, then, abort API.
720 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
721 RAISE FND_API.G_EXC_ERROR;
722 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724 END IF;
725 -- added code : prithwi : 29.9.2003
726 OPEN get_route_det(p_source_route_id);
727 FETCH get_route_det INTO l_route_rec;
728 CLOSE get_route_det;
729
730 -- Start work Flow Process
731 IF l_ROUTE_APPR_ENABLED = 'Y'
732 THEN
733 ahl_utility_pvt.get_wf_process_name(
734 p_object =>l_object,
735 p_application_usg_code => l_route_rec.application_usg_code,
736 x_active =>l_active,
737 x_process_name =>l_process_name ,
738 x_item_type =>l_item_type,
739 x_return_status=>l_return_status,
740 x_msg_count =>l_msg_count,
741 x_msg_data =>l_msg_data);
742 END IF ;
743
744 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
745 THEN
746 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJ_VERSION_NULL');
747 FND_MSG_PUB.ADD;
748 END IF;
749
750 IF p_source_route_id is null or p_source_route_id=FND_API.G_MISS_NUM
751 THEN
752 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJECT_ID_NULL');
753 FND_MSG_PUB.ADD;
754 ELSE
755 OPEN get_route_det(p_source_route_id);
756 FETCH get_route_det INTO l_route_rec;
757 CLOSE get_route_det;
758 IF p_apprvl_type = 'APPROVE'
759 THEN
760 AHL_RM_ROUTE_UTIL.Validate_rt_oper_start_date
761 (
762 p_object_id =>p_source_route_id,
763 p_association_type =>'ROUTE',
764 p_start_date =>l_route_rec.start_date_active,
765 x_start_date =>l_start_date,
766 x_msg_data =>l_msg,
767 x_return_status =>l_return_Status
768 );
769
770 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
771 THEN
772 FND_MESSAGE.SET_NAME('AHL',l_msg);
773 FND_MESSAGE.set_token('FIELD',l_start_Date);
774 FND_MSG_PUB.ADD;
775 IF G_DEBUG='Y' THEN
776 AHL_DEBUG_PUB.debug('AHL_rm_util.Validate_rt_oper_start_date','+ROUTE+');
777 END IF;
778 ELSIF TRUNC(l_route_rec.START_DATE_ACTIVE)<TRUNC(SYSDATE)
779 THEN
780 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INVALID_ST_DATE');
781 FND_MESSAGE.set_token('FIELD',TRUNC(sysdate));
782 FND_MSG_PUB.ADD;
783 END IF;
784
785 IF l_route_rec.revision_Status_code = 'DRAFT' or
786 l_route_rec.revision_Status_code='APPROVAL_REJECTED'
787 THEN
788 l_upd_status := 'APPROVAL_PENDING';
789 ELSE
790 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RO_STAT_NOT_DRFT');
791 FND_MESSAGE.set_token('FIELD',l_route_rec.route_no);
792 FND_MSG_PUB.ADD;
793 END IF;
794
795 --bachandr Enigma Phase I changes -- start
796 -- When the workflow type is approve , check if time span is entered as it is mandatory for
797 -- approval flow. If not throw an error.
798 IF l_route_rec.time_span is null or l_route_rec.time_span =FND_API.G_MISS_NUM
799 THEN
800 FND_MESSAGE.SET_NAME('AHL','AHL_RM_TIME_SPAN_NULL');
801 FND_MSG_PUB.ADD;
802 END IF;
803 --bachandr Enigma Phase I changes -- end
804
805 ELSIF p_apprvl_type = 'TERMINATE'
806 THEN
807
808 --bachandr Enigma Phase I changes -- start
809 --snarkhed Enigma Phase II changes to identify automatic enigma routes.
810 Select ENIGMA_ROUTE_ID into l_enigma_route_id
811 From ahl_routes_b
812 Where route_id = p_source_route_id;
813
814 IF ( l_enigma_route_id is not null)
815 THEN
816 --if the route is from enigma do not allow termination.
817 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_ENIG_TERM');
818 FND_MSG_PUB.ADD;
819 END IF;
820 --bachandr Enigma Phase I changes -- end
821
822 IF (
823 (l_route_rec.END_DATE_ACTIVE IS NOT NULL OR
824 l_route_rec.END_DATE_ACTIVE<>FND_API.G_MISS_DATE
825 )
826 AND
827 l_route_rec.revision_status_code = 'TERMINATED'
828 )
829 THEN
830 -- if the Route is TERMINATED
831 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_INV_TERMT');
832 FND_MSG_PUB.ADD;
833 ELSIF
834 (
835 (l_route_rec.END_DATE_ACTIVE IS NOT NULL OR
836 l_route_rec.END_DATE_ACTIVE<>FND_API.G_MISS_DATE
837 )
838 AND
839 l_route_rec.revision_status_code = 'COMPLETE'
840 )
841 THEN
842 -- if the Route is COMPLETE and End Dated.
843 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_INACTIVE');
844 FND_MSG_PUB.ADD;
845 ELSE
846 -- for all other cases throw all errors
847
848
849 /*SELECT COUNT(*) into l_counter
850 FROM AHL_ROUTE_MR_V
851 WHERE ROUTE_ID=l_route_Rec.ROUTE_ID
852 AND trunc(nvl(AHL_ROUTE_MR_V.EFFECTIVE_TO,SYSDATE))>=trunc(SYSDATE);*/
853
854 --AMSRINIV : Bug 4913294. Tuned above commented query.
855 SELECT COUNT(*) into l_counter
856 from ahl_mr_routes a, ahl_mr_headers_b b
857 where
858 a.route_id = l_route_rec.route_id and
859 a.mr_header_id = b.mr_header_id and
860 b.application_usg_code = rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE'))) and
861 trunc(nvl(b.effective_to,sysdate)) >= trunc(sysdate);
862 IF l_counter>0
863 THEN
864 FND_MESSAGE.SET_NAME('AHL','AHL_RM_MR_C_TERM');
865 FND_MSG_PUB.ADD;
866 END IF;
867
868 IF l_route_rec.revision_status_code ='COMPLETE' or
869 l_route_rec.revision_Status_code='APPROVAL_TERMINATED'
870 THEN
871 SELECT COUNT(*) into l_counter
872 FROM AHL_ROUTES_APP_V
873 WHERE UPPER(TRIM(ROUTE_NO))=UPPER(TRIM(l_route_Rec.route_no))
874 AND REVISION_NUMBER=l_route_Rec.revision_number+1;
875
876 IF l_counter>0
877 THEN
878 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RT_C_TERM');
879 FND_MSG_PUB.ADD;
880 END IF;
881
882 l_upd_status := 'TERMINATION_PENDING';
883 ELSE
884 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RO_STAT_NOT_COMP');
885 FND_MESSAGE.set_token('FIELD',l_route_rec.route_no);
886 FND_MSG_PUB.ADD;
887 END IF;
888 END IF;
889 END IF;
890 END IF;
891
892 l_msg_count := FND_MSG_PUB.count_msg;
893
894 IF l_msg_count > 0
895 THEN
896 X_msg_count := l_msg_count;
897 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
898 RAISE FND_API.G_EXC_ERROR;
899 END IF;
900
901 IF l_ACTIVE='Y'
902 THEN
903 UPDATE AHL_ROUTES_B
904 SET REVISION_STATUS_CODE=l_upd_status,
905 OBJECT_VERSION_number=OBJECT_VERSION_number+1
906 WHERE ROUTE_ID=p_source_route_id
907 and OBJECT_VERSION_NUMBER=p_object_Version_number;
908
909 IF sql%rowcount=0
910 THEN
911 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
912 FND_MSG_PUB.ADD;
913 END IF ;
914
915 UPDATE AHL_ROUTES_TL
916 SET APPROVER_NOTE=null
917 WHERE ROUTE_ID=p_source_route_id;
918
919 IF sql%rowcount=0
920 THEN
921 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
922 FND_MSG_PUB.ADD;
923 END IF ;
924
925
926 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
927 P_OBJECT =>l_object,
928 P_ACTIVITY_ID =>l_activity_id,
929 P_APPROVAL_TYPE =>'CONCEPT',
930 P_APPLICATION_USG_CODE =>l_route_rec.application_usg_code,
931 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
932 P_ORIG_STATUS_CODE =>'ACTIVE',
933 P_NEW_STATUS_CODE =>'APPROVED',
934 P_REJECT_STATUS_CODE =>'REJECTED',
935 P_REQUESTER_USERID =>fnd_global.user_id,
936 P_NOTES_FROM_REQUESTER =>NULL,
937 -- P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
938 P_WORKFLOWPROCESS =>l_process_name,
939 P_ITEM_TYPE =>'AHLGAPP');
940
941 ELSE
942 UPDATE AHL_ROUTES_B
943 SET REVISION_STATUS_CODE=l_upd_status,
944 OBJECT_VERSION_number=OBJECT_VERSION_number+1
945 WHERE ROUTE_ID=p_source_route_id
946 and OBJECT_VERSION_NUMBER=p_object_Version_number;
947
948 IF sql%rowcount=0
949 THEN
950 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
951 FND_MSG_PUB.ADD;
952
953 END IF;
954
955 COMPLETE_ROUTE_REVISION
956 (
957 p_api_version =>p_api_version,
958 p_init_msg_list =>p_init_msg_list,
959 p_commit =>FND_API.G_FALSE,
960 p_validation_level =>p_validation_level ,
961 p_default =>p_default ,
962 p_module_type =>p_module_type,
963 x_return_status =>l_return_status,
964 x_msg_count =>x_msg_count ,
965 x_msg_data =>x_msg_data ,
966 p_appr_status =>'APPROVED',
967 p_route_id =>p_source_route_id,
968 p_object_version_number =>l_object_version_number+1,
969 p_approver_note =>null
970 );
971 IF G_DEBUG='Y'
972 THEN
973 AHL_DEBUG_PUB.debug( 'After CompleteRouteRevision');
974 END IF;
975 END IF ;
976
977 l_msg_count := FND_MSG_PUB.count_msg;
978
979 IF l_msg_count > 0
980 THEN
981 X_msg_count := l_msg_count;
982 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
983 RAISE FND_API.G_EXC_ERROR;
984 END IF;
985
986
987 IF FND_API.TO_BOOLEAN(p_commit) THEN
988 COMMIT;
989 END IF;
990
991 EXCEPTION
992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
993 ROLLBACK TO INITIATE_ROUTE_APPROVAL;
994 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
996 p_count => x_msg_count,
997 p_data => x_msg_data);
998
999 WHEN FND_API.G_EXC_ERROR THEN
1000 ROLLBACK TO INITIATE_ROUTE_APPROVAL;
1001 X_return_status := FND_API.G_RET_STS_ERROR;
1002 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1003 p_count => x_msg_count,
1004 p_data => X_msg_data);
1005 WHEN OTHERS THEN
1006 ROLLBACK TO INITIATE_ROUTE_APPROVAL;
1007 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1009 THEN
1010 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RM_ROUTE_PUB',
1011 p_procedure_name => 'INITIATE_ROUTE_APPROVAL',
1012 p_error_text => SUBSTR(SQLERRM,1,240));
1013 END IF;
1014 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1015 p_count => x_msg_count,
1016 p_data => X_msg_data);
1017 END INITIATE_ROUTE_APPROVAL;
1018
1019 PROCEDURE COMPLETE_ROUTE_REVISION
1020 (
1021 p_api_version IN NUMBER:= 1.0,
1022 p_init_msg_list IN VARCHAR2,
1023 p_commit IN VARCHAR2,
1024 p_validation_level IN NUMBER,
1025 p_default IN VARCHAR2 := FND_API.G_FALSE,
1026 p_module_type IN VARCHAR2,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2,
1030 p_appr_status IN VARCHAR2,
1031 p_route_id IN NUMBER,
1032 p_object_version_number IN NUMBER,
1033 p_approver_note IN VARCHAR2 := null
1034 )
1035 AS
1036
1037 CURSOR GetRouteDet(C_ROUTE_ID NUMBER)
1038 IS
1039 SELECT ROUTE_ID,ROUTE_NO,REVISION_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE
1040 FROM AHL_ROUTES_APP_V
1041 WHERE ROUTE_ID=C_ROUTE_ID;
1042
1043 CURSOR GetPrevRouteDet(C_REVISION_NUMBER NUMBER,C_ROUTE_NO VARCHAR2)
1044 IS
1045 SELECT ROUTE_ID,ROUTE_NO,REVISION_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE
1046 FROM AHL_ROUTES_APP_V
1047 WHERE UPPER(TRIM(ROUTE_NO))=UPPER(TRIM(C_ROUTE_NO))
1048 AND REVISION_NUMBER=C_REVISION_NUMBER-1;
1049
1050 l_route_rec GetRouteDet%rowtype;
1051 l_prev_route_rec GetPrevRouteDet%rowtype;
1052 l_status VARCHAR2(30);
1053 l_mr_status VARCHAR2(30);
1054 l_check_flag VARCHAR2(1):='N';
1055 l_check_flag2 VARCHAR2(1):='N';
1056 l_check_flag3 VARCHAR2(1):='Y';
1057 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_ROUTE_REVISION';
1058 l_api_version CONSTANT NUMBER := 1.0;
1059 l_num_rec NUMBER;
1060 l_msg_count NUMBER;
1061 l_msg_data VARCHAR2(2000);
1062 l_return_status VARCHAR2(1);
1063 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1064 l_fr_date DATE:=SYSDATE;
1065 l_to_Date DATE:=SYSDATE;
1066 l_commit VARCHAR2(10):=FND_API.G_TRUE;
1067 l_curr_status VARCHAR2(30);
1068
1069
1070 l_object VARCHAR2(30):='RM';
1071 l_approval_type VARCHAR2(100):='CONCEPT';
1072 l_active VARCHAR2(50);
1073 l_process_name VARCHAR2(50);
1074 l_item_type VARCHAR2(50);
1075
1076 -- Routes
1077 CURSOR CurGetRoutedet(C_ROUTE_ID NUMBER)
1078 IS
1079 SELECT * from AHL_MR_ROUTES
1080 WHERE ROUTE_ID=C_ROUTE_ID;
1081 l_mr_route_rec CurGetRoutedet%rowtype;
1082
1083 l_new_mr_ROUTE_ID NUMBER:=0;
1084 l_new_mr_ROUTE_SEQ_ID NUMBER:=0;
1085 l_old_mr_route_id NUMBER:=0;
1086 l_seq_mr_route_id NUMBER:=0;
1087 l_seq_rel_mr_route_id NUMBER:=0;
1088 -- Route Sequences
1089 CURSOR CurGetRouteSeqDet(C_MR_ROUTE_ID NUMBER)
1090 iS
1091 SELECT * FROM AHL_MR_ROUTE_SEQUENCES
1092 WHERE (MR_ROUTE_ID=C_MR_ROUTE_ID OR RELATED_MR_ROUTE_ID=C_MR_ROUTE_ID);
1093
1094 l_mr_route_seq_rec CurGetRouteSeqDet%rowtype;
1095 l_rowid VARCHAR2(30);
1096 BEGIN
1097
1098 SAVEPOINT COMPLETE_ROUTE_REVISION;
1099
1100 IF G_DEBUG='Y' THEN
1101 AHL_DEBUG_PUB.enable_debug;
1102 END IF;
1103
1104 SELECT REVISION_STATUS_CODE INTO l_curr_status
1105 FROM AHL_ROUTES_APP_V WHERE ROUTE_ID=p_route_id;
1106
1107
1108 IF p_appr_status='APPROVED'
1109 THEN
1110 l_status:='COMPLETE';
1111 ELSE
1112 IF l_curr_status='APPROVAL_PENDING'
1113 THEN
1114 l_status:='APPROVAL_REJECTED';
1115 ELSE
1116 l_status:='COMPLETE';
1117 END IF;
1118 l_check_flag3:='N';
1119 END IF;
1120
1121 IF p_route_id is not null or p_route_id<>fnd_api.g_miss_num
1122 THEN
1123 IF G_DEBUG='Y'
1124 THEN
1125 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:p_route_id'||p_route_id);
1126 END IF;
1127 OPEN GetRouteDet(p_route_id);
1128
1129 FETCH GetRouteDet INTO l_route_rec;
1130
1131 IF GetRouteDet%NOTFOUND
1132 THEN
1133 l_check_flag:='N';
1134 ELSE
1135 l_check_flag:='Y';
1136 IF trunc(l_route_rec.Start_date_active) >trunc(sysdate)
1137 THEN
1138 l_fr_date:=l_route_rec.start_date_active;
1139 l_to_date:=l_route_rec.start_date_active;
1140 ELSE
1141 l_fr_date:=sysdate;
1142 l_to_date:=sysdate;
1143 END IF;
1144 END IF;
1145
1146 CLOSE GetRouteDet;
1147 IF l_check_flag='Y' and p_appr_status='REJECTED'
1148 Then
1149 UPDATE AHL_ROUTES_B
1150 SET REVISION_STATUS_CODE=l_status,
1151 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1152 WHERE ROUTE_ID=P_ROUTE_ID;
1153
1154 UPDATE AHL_ROUTES_TL
1155 SET APPROVER_NOTE=p_approver_note
1156 WHERE ROUTE_ID=P_ROUTE_ID;
1157 Elsif l_check_flag='Y' and l_curr_status='TERMINATION_PENDING' and
1158 p_appr_status='APPROVED'
1159 Then
1160 UPDATE AHL_ROUTES_B
1161 SET REVISION_STATUS_CODE='TERMINATED',
1162 -- START_DATE_ACTIVE=l_fr_date,
1163 END_DATE_ACTIVE=l_to_date,
1164 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1165 WHERE ROUTE_ID=P_ROUTE_ID;
1166
1167 -- Do not call for Preventive Maintenance application Mode.
1168 IF (G_PM_INSTALL <> 'Y') THEN
1169 ahl_utility_pvt.get_wf_process_name(
1170 p_object =>'RM_NTF',
1171 x_active =>l_active,
1172 x_process_name =>l_process_name ,
1173 x_item_type =>l_item_type,
1174 x_return_status=>l_return_status,
1175 x_msg_count =>l_msg_count,
1176 x_msg_data =>l_msg_data);
1177
1178 IF (l_active = 'Y') THEN
1179 NOTIFY_TERMINATION
1180 (
1181 x_return_status =>l_return_status,
1182 x_msg_count =>l_msg_count,
1183 x_msg_data =>l_msg_data,
1184 p_object_type =>G_OBJECT_TYPE,
1185 p_prim_object_type =>'RM',
1186 p_activity_id =>p_ROUTE_ID
1187 );
1188 END IF;
1189 END IF;
1190 Elsif l_check_flag='Y' and l_route_rec.revision_number=1 and
1191 l_curr_status='APPROVAL_PENDING' AND p_appr_status='APPROVED'
1192 THEN
1193 UPDATE AHL_ROUTES_B
1194 SET REVISION_STATUS_CODE=l_status,
1195 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1196 WHERE ROUTE_ID=P_ROUTE_ID;
1197
1198 l_check_flag:='Y';
1199
1200 l_check_flag2:='Y';
1201 IF G_DEBUG='Y'
1202 THEN
1203 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:p_route_id'||p_route_id);
1204 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_status'||l_status);
1205 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_route_rec.revision_number'||l_route_rec.revision_number);
1206 END IF;
1207 Elsif l_check_flag='Y' and l_route_rec.revision_number>1 and
1208 l_curr_status='APPROVAL_PENDING' AND p_appr_status='APPROVED'
1209 THEN
1210 IF G_DEBUG='Y'
1211 THEN
1212 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:p_route_id'||p_route_id);
1213 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_status'||l_status);
1214 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_route_rec.revision_number'||l_route_rec.revision_number);
1215 END IF;
1216 OPEN GetPrevRouteDet(l_route_rec.revision_number,l_route_rec.route_no);
1217
1218 FETCH GetPrevRouteDet INTO l_prev_route_rec;
1219
1220 IF GetPrevRouteDet%NOTFOUND
1221 THEN
1222 l_check_flag2:='N';
1223 ELSE
1224 l_check_flag2:='Y';
1225 END IF;
1226
1227 CLOSE GetPrevRouteDet;
1228
1229 IF l_check_flag2='Y'
1230 THEN
1231
1232 UPDATE AHL_ROUTES_B
1233 SET REVISION_STATUS_CODE= l_status,
1234 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1235 START_DATE_ACTIVE=l_fr_date
1236 WHERE ROUTE_ID=P_ROUTE_ID;
1237
1238 IF l_check_flag3='Y'
1239 THEN
1240 UPDATE AHL_ROUTES_B
1241 SET REVISION_STATUS_CODE= l_status,
1242 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1243 END_DATE_ACTIVE=l_to_date
1244 WHERE ROUTE_ID=l_prev_route_rec.ROUTE_ID;
1245
1246 IF l_check_flag2='Y'
1247 THEN
1248
1249 open CurGetRoutedet(l_prev_route_rec.ROUTE_ID);
1250 --open CurGetRoutedet(P_ROUTE_ID);
1251 loop
1252 fetch CurGetRoutedet into l_mr_route_rec;
1253 IF CurGetRoutedet%FOUND
1254 THEN
1255
1256 SELECT AHL_MR_ROUTES_S.NEXTVAL
1257 INTO l_new_mr_ROUTE_ID
1258 FROM DUAL;
1259
1260 l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
1261
1262 AHL_MR_ROUTES_PKG.INSERT_ROW (
1263 -- X_ROWID =>L_ROWID,
1264 X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
1265 X_MR_HEADER_ID =>l_mr_route_Rec.mr_header_id,
1266 X_ROUTE_ID =>P_ROUTE_ID,
1267 X_STAGE =>l_mr_route_Rec.STAGE,
1268 --MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
1269 X_STAGE_TYPE_CODE =>l_mr_route_Rec.STAGE_TYPE_CODE,
1270 X_ATTRIBUTE_CATEGORY =>l_mr_route_Rec.ATTRIBUTE_CATEGORY,
1271 X_ATTRIBUTE1 =>l_mr_route_Rec.ATTRIBUTE1,
1272 X_ATTRIBUTE2 =>l_mr_route_Rec.ATTRIBUTE2,
1273 X_ATTRIBUTE3 =>l_mr_route_Rec.ATTRIBUTE3,
1274 X_ATTRIBUTE4 =>l_mr_route_Rec.ATTRIBUTE4,
1275 X_ATTRIBUTE5 =>l_mr_route_Rec.ATTRIBUTE5,
1276 X_ATTRIBUTE6 =>l_mr_route_Rec.ATTRIBUTE6,
1277 X_ATTRIBUTE7 =>l_mr_route_Rec.ATTRIBUTE7,
1278 X_ATTRIBUTE8 =>l_mr_route_Rec.ATTRIBUTE8,
1279 X_ATTRIBUTE9 =>l_mr_route_Rec.ATTRIBUTE9,
1280 X_ATTRIBUTE10 =>l_mr_route_Rec.ATTRIBUTE10,
1281 X_ATTRIBUTE11 =>l_mr_route_Rec.ATTRIBUTE11,
1282 X_ATTRIBUTE12 =>l_mr_route_Rec.ATTRIBUTE12,
1283 X_ATTRIBUTE13 =>l_mr_route_Rec.ATTRIBUTE13,
1284 X_ATTRIBUTE14 =>l_mr_route_Rec.ATTRIBUTE14,
1285 X_ATTRIBUTE15 =>l_mr_route_Rec.ATTRIBUTE15,
1286 X_OBJECT_VERSION_NUMBER =>1,
1287 X_CREATION_DATE =>sysdate,
1288 X_CREATED_BY =>fnd_global.user_id,
1289 X_LAST_UPDATE_DATE =>sysdate,
1290 X_LAST_UPDATED_BY =>fnd_global.user_id,
1291 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
1292 OPEN CurGetRouteSeqDet(l_old_mr_ROUTE_ID);
1293 LOOP
1294 FETCH CurGetRouteSeqDet INTO l_mr_route_seq_rec;
1295 IF CurGetRouteSeqDet%FOUND
1296 THEN
1297 SELECT AHL_MR_ROUTE_SEQUENCES_S .NEXTVAL
1298 INTO l_new_mr_ROUTE_SEQ_ID FROM DUAL;
1299 IF l_mr_route_seq_rec.mr_route_id=l_old_mr_route_id
1300 THEN
1301 l_seq_rel_mr_route_id:=l_mr_route_seq_rec.related_mr_route_id;
1302 l_seq_mr_route_id:=l_new_mr_ROUTE_ID;
1303
1304 ELSIF l_mr_route_seq_rec.related_mr_route_id=l_old_mr_route_id
1305 THEN
1306 l_seq_mr_route_id:=l_mr_route_seq_rec.mr_route_id;
1307 l_seq_rel_mr_route_id:=l_new_mr_ROUTE_ID;
1308 END IF;
1309 AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW (
1310 X_MR_ROUTE_SEQUENCE_ID =>l_new_mr_route_seq_id,
1311 X_RELATED_MR_ROUTE_ID =>l_seq_rel_mr_route_id,
1312 X_SEQUENCE_CODE =>l_mr_route_seq_rec.SEQUENCE_CODE,
1313 X_MR_ROUTE_ID =>l_seq_mr_route_id,
1314 X_OBJECT_VERSION_NUMBER =>1,
1315 X_ATTRIBUTE_CATEGORY =>l_mr_route_seq_rec.ATTRIBUTE_CATEGORY,
1316 X_ATTRIBUTE1 =>l_mr_route_seq_rec.ATTRIBUTE1,
1317 X_ATTRIBUTE2 =>l_mr_route_seq_rec.ATTRIBUTE2,
1318 X_ATTRIBUTE3 =>l_mr_route_seq_rec.ATTRIBUTE3,
1319 X_ATTRIBUTE4 =>l_mr_route_seq_rec.ATTRIBUTE4,
1320 X_ATTRIBUTE5 =>l_mr_route_seq_rec.ATTRIBUTE5,
1321 X_ATTRIBUTE6 =>l_mr_route_seq_rec.ATTRIBUTE6,
1322 X_ATTRIBUTE7 =>l_mr_route_seq_rec.ATTRIBUTE7,
1323 X_ATTRIBUTE8 =>l_mr_route_seq_rec.ATTRIBUTE8,
1324 X_ATTRIBUTE9 =>l_mr_route_seq_rec.ATTRIBUTE9,
1325 X_ATTRIBUTE10 =>l_mr_route_seq_rec.ATTRIBUTE10,
1326 X_ATTRIBUTE11 =>l_mr_route_seq_rec.ATTRIBUTE11,
1327 X_ATTRIBUTE12 =>l_mr_route_seq_rec.ATTRIBUTE12,
1328 X_ATTRIBUTE13 =>l_mr_route_seq_rec.ATTRIBUTE13,
1329 X_ATTRIBUTE14 =>l_mr_route_seq_rec.ATTRIBUTE14,
1330 X_ATTRIBUTE15 =>l_mr_route_seq_rec.ATTRIBUTE15,
1331 X_CREATION_DATE =>sysdate,
1332 X_CREATED_BY =>fnd_global.user_id,
1333 X_LAST_UPDATE_DATE =>sysdate,
1334 X_LAST_UPDATED_BY =>fnd_global.user_id,
1335 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
1336 ELSE
1337 EXIT WHEN CurGetRouteSeqDet%NOTFOUND;
1338 END IF;
1339 END LOOP;
1340 CLOSE CurGetRouteSeqDet;
1341
1342 ELSE
1343 EXIT WHEN CurGetRoutedet%NOTFOUND;
1344 END IF;
1345
1346 END LOOP;
1347
1348 END IF;
1349 close CurGetRoutedet;
1350
1351 -- Do not call for Preventive Maintenance application Mode.
1352 IF (G_PM_INSTALL <> 'Y') THEN
1353 ahl_utility_pvt.get_wf_process_name(
1354 p_object =>'RM_NTF',
1355 x_active =>l_active,
1356 x_process_name =>l_process_name ,
1357 x_item_type =>l_item_type,
1358 x_return_status=>l_return_status,
1359 x_msg_count =>l_msg_count,
1360 x_msg_data =>l_msg_data);
1361
1362 IF (l_active = 'Y') THEN
1363
1364 NOTIFY_TERMINATION
1365 (
1366 x_return_status =>l_return_status,
1367 x_msg_count =>l_msg_count,
1368 x_msg_data =>l_msg_data,
1369 p_object_type =>G_OBJECT_TYPE,
1370 p_prim_object_type =>'RM',
1371 p_activity_id =>l_prev_route_rec.ROUTE_ID
1372 );
1373 END IF;
1374 END IF;
1375
1376 else
1377 UPDATE AHL_ROUTES_B
1378 SET REVISION_STATUS_CODE= l_status,
1379 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1380 END_DATE_ACTIVE=l_to_date
1381 WHERE ROUTE_ID=l_prev_route_rec.ROUTE_ID;
1382 END IF;
1383 END IF;
1384 END IF;
1385
1386 END IF;
1387
1388 IF l_msg_count > 0
1389 THEN
1390 X_msg_count := l_msg_count;
1391 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1392 RAISE FND_API.G_EXC_ERROR;
1393 END IF;
1394
1395 IF G_DEBUG='Y' THEN
1396 AHL_DEBUG_PUB.debug( 'Before commit Complete_route_revision Status----->'||p_appr_status,'+DEBUG_RELATIONS+');
1397 END IF;
1398
1399 IF FND_API.TO_BOOLEAN(p_commit) THEN
1400 COMMIT;
1401 END IF;
1402 EXCEPTION
1403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1404 ROLLBACK TO COMPLETE_ROUTE_REVISION;
1405 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1406 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1407 p_count => x_msg_count,
1408 p_data => x_msg_data);
1409
1410 WHEN FND_API.G_EXC_ERROR THEN
1411 ROLLBACK TO COMPLETE_MR_REVISION;
1412 X_return_status := FND_API.G_RET_STS_ERROR;
1413 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1414 p_count => x_msg_count,
1415 p_data => X_msg_data);
1416 WHEN OTHERS THEN
1417 ROLLBACK TO COMPLETE_MR_REVISION;
1418 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1420 THEN
1421 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1422 p_procedure_name =>'COMPLETE_ROUTE_REVISION',
1423 p_error_text =>SUBSTR(SQLERRM,1,240));
1424 END IF;
1425 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1426 p_count => x_msg_count,
1427 p_data => X_msg_data);
1428
1429 END;
1430
1431 PROCEDURE COMPLETE_OPER_REVISION
1432 (
1433 p_api_version IN NUMBER:= 1.0,
1434 p_init_msg_list IN VARCHAR2,
1435 p_commit IN VARCHAR2,
1436 p_validation_level IN NUMBER,
1437 p_default IN VARCHAR2 := FND_API.G_FALSE,
1438 p_module_type IN VARCHAR2,
1439 x_return_status OUT NOCOPY VARCHAR2,
1440 x_msg_count OUT NOCOPY NUMBER,
1441 x_msg_data OUT NOCOPY VARCHAR2,
1442 p_appr_status IN VARCHAR2,
1443 p_operation_id IN NUMBER,
1444 p_object_version_number IN NUMBER,
1445 p_approver_note IN VARCHAR2 := null
1446 )
1447 AS
1448 CURSOR GetOperationDet(C_OPERATION_ID NUMBER)
1449 IS
1450 SELECT OPERATION_ID,
1451 CONCATENATED_SEGMENTS,
1452 REVISION_NUMBER,
1453 START_DATE_ACTIVE,
1454 END_DATE_ACTIVE
1455 FROM AHL_OPERATIONS_B_KFV
1456 WHERE OPERATION_ID=C_OPERATION_ID;
1457
1458 CURSOR GetPrevOperDet(C_REVISION_NUMBER NUMBER,C_CONCATENATED_SEGMENTS VARCHAR2)
1459 IS
1460 SELECT OPERATION_ID,
1461 CONCATENATED_SEGMENTS,
1462 REVISION_NUMBER,
1463 START_DATE_ACTIVE,
1464 END_DATE_ACTIVE
1465 FROM AHL_OPERATIONS_B_KFV
1466 WHERE CONCATENATED_SEGMENTS=C_CONCATENATED_SEGMENTS
1467 AND REVISION_NUMBER=C_REVISION_NUMBER-1;
1468
1469 l_oper_rec GetOperationDet%rowtype;
1470 l_prev_oper_rec GetPrevOperDet%rowtype;
1471 l_status VARCHAR2(30);
1472 l_curr_status VARCHAR2(30);
1473 l_curr_atus VARCHAR2(30);
1474 l_check_flag VARCHAR2(1):='N';
1475 l_check_flag2 VARCHAR2(1):='N';
1476 l_check_flag3 VARCHAR2(1):='Y';
1477 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_MR_REVISION';
1478 l_api_version CONSTANT NUMBER := 1.0;
1479 l_num_rec NUMBER;
1480 l_msg_count NUMBER;
1481 l_msg_data VARCHAR2(2000);
1482 l_return_status VARCHAR2(1);
1483 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1484 l_fr_date DATE:=SYSDATE;
1485 l_to_Date DATE:=SYSDATE;
1486 l_commit VARCHAR2(10):=FND_API.G_TRUE;
1487 l_active VARCHAR2(50);
1488 l_process_name VARCHAR2(50);
1489 l_item_type VARCHAR2(50);
1490
1491 BEGIN
1492 SAVEPOINT COMPLETE_OPER_REVISION;
1493
1494 IF G_DEBUG='Y' THEN
1495 AHL_DEBUG_PUB.enable_debug;
1496 AHL_DEBUG_PUB.debug( 'Complete_mr_revision Status----->'||p_appr_status,'+DEBUG_RELATIONS+');
1497 END IF;
1498
1499 SELECT REVISION_STATUS_CODE INTO l_curr_status
1500 FROM AHL_OPERATIONS_B_KFV WHERE OPERATION_ID=p_operation_id;
1501
1502 IF p_appr_status='APPROVED'
1503 THEN
1504 l_status:='COMPLETE';
1505 ELSE
1506 IF l_curr_status='APPROVAL_PENDING'
1507 THEN
1508 l_status:='APPROVAL_REJECTED';
1509 ELSE
1510 l_status:='COMPLETE';
1511 END IF;
1512 l_check_flag3:='N';
1513 END IF;
1514
1515 IF G_DEBUG='Y' THEN
1516 AHL_DEBUG_PUB.debug( 'Complete_operation_revision Status2----->'||L_status,'+DEBUG_REVISION+');
1517 AHL_DEBUG_PUB.debug( 'Complete_mr_revision Status2----->'||L_status,'+DEBUG_REVISION+');
1518 END IF;
1519
1520 IF p_operation_id is not null or p_operation_id<>fnd_api.g_miss_num
1521 THEN
1522 OPEN GetOperationDet(p_operation_id);
1523 FETCH GetOperationDet INTO l_oper_rec;
1524
1525 IF GetOperationDet%NOTFOUND
1526 THEN
1527 l_check_flag:='N';
1528 ELSE
1529 l_check_flag:='Y';
1530 IF trunc(l_oper_rec.start_date_active) >trunc(sysdate)
1531 THEN
1532 l_fr_date:=l_oper_rec.start_date_active;
1533 l_to_date:=l_oper_rec.Start_date_active;
1534 ELSE
1535 l_fr_date:=sysdate;
1536 l_to_date:=sysdate;
1537 END IF;
1538 END IF;
1539
1540 CLOSE GetOperationDet;
1541 IF l_check_flag='Y' and p_appr_status='REJECTED'
1542 THEN
1543 UPDATE AHL_OPERATIONS_B
1544 SET REVISION_STATUS_CODE=l_status,
1545 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1546 WHERE OPERATION_ID=P_OPERATION_ID;
1547 UPDATE AHL_OPERATIONS_TL
1548 SET APPROVER_NOTE=p_approver_note
1549 WHERE OPERATION_ID=P_OPERATION_ID;
1550 ELSIF l_check_flag='Y' and l_curr_status='TERMINATION_PENDING'
1551 and p_appr_status='APPROVED'
1552 THEN
1553 UPDATE AHL_OPERATIONS_B
1554 SET REVISION_STATUS_CODE='TERMINATED',
1555 -- START_DATE_ACTIVE=l_fr_date,
1556 END_DATE_ACTIVE=l_to_date,
1557 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1558 WHERE OPERATION_ID=P_OPERATION_ID;
1559
1560 -- Bypass notifications in Preventive Application mode.
1561 IF (G_PM_INSTALL <> 'Y') THEN
1562 ahl_utility_pvt.get_wf_process_name(
1563 p_object =>'RM_NTF',
1564 x_active =>l_active,
1565 x_process_name =>l_process_name ,
1566 x_item_type =>l_item_type,
1567 x_return_status=>l_return_status,
1568 x_msg_count =>l_msg_count,
1569 x_msg_data =>l_msg_data);
1570
1571 IF (l_active = 'Y') THEN
1572 NOTIFY_TERMINATION
1573 (
1574 x_return_status =>l_return_status,
1575 x_msg_count =>l_msg_count,
1576 x_msg_data =>l_msg_data,
1577 p_object_type =>G_OBJECT_TYPE,
1578 p_prim_object_type =>'OPER',
1579 p_activity_id =>p_operation_id
1580 );
1581 END IF;
1582 END IF;
1583
1584 ELSIF l_check_flag='Y' and l_oper_rec.revision_number=1
1585 and l_curr_status='APPROVAL_PENDING' and p_appr_status='APPROVED'
1586 THEN
1587 UPDATE AHL_OPERATIONS_B
1588 SET REVISION_STATUS_CODE=l_status,
1589 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1590 WHERE OPERATION_ID=P_OPERATION_ID;
1591
1592 l_check_flag:='Y';
1593
1594 l_check_flag2:='Y';
1595
1596 ELSIF l_check_flag='Y' and
1597 l_oper_rec.revision_number>1 and
1598 p_appr_status='APPROVED'
1599 THEN
1600 OPEN GetPrevOperDet(l_oper_rec.revision_number,l_oper_rec.CONCATENATED_SEGMENTS);
1601 FETCH GetPrevOperDet INTO l_prev_oper_rec;
1602 IF GetPrevOperDet%NOTFOUND
1603 THEN
1604 l_check_flag2:='N';
1605 ELSE
1606 l_check_flag2:='Y';
1607 END IF;
1608
1609 CLOSE GetPrevOperDet;
1610
1611 IF l_check_flag2='Y'
1612 THEN
1613 UPDATE AHL_OPERATIONS_B
1614 SET REVISION_STATUS_CODE=l_status,
1615 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1616 START_DATE_ACTIVE=l_fr_date
1617 WHERE OPERATION_ID=P_OPERATION_ID;
1618 IF l_check_flag3='Y'
1619 THEN
1620 UPDATE AHL_OPERATIONS_B
1621 SET REVISION_STATUS_CODE=l_status,
1622 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
1623 END_DATE_ACTIVE=l_to_date
1624 WHERE OPERATION_ID=l_prev_oper_rec.OPERATION_ID;
1625
1626 -- Bypass notifications in preventive Maintenance mode.
1627 IF (G_PM_INSTALL <> 'Y') THEN
1628 ahl_utility_pvt.get_wf_process_name(
1629 p_object =>'RM_NTF',
1630 x_active =>l_active,
1631 x_process_name =>l_process_name ,
1632 x_item_type =>l_item_type,
1633 x_return_status=>l_return_status,
1634 x_msg_count =>l_msg_count,
1635 x_msg_data =>l_msg_data);
1636
1637 IF (l_active = 'Y') THEN
1638 NOTIFY_TERMINATION
1639 (
1640 x_return_status =>l_return_status,
1641 x_msg_count =>l_msg_count,
1642 x_msg_data =>l_msg_data,
1643 p_object_type =>G_OBJECT_TYPE,
1644 p_prim_object_type =>'OPER',
1645 p_activity_id =>p_operation_id
1646 );
1647 END IF;
1648 END IF;
1649 END IF;
1650 END IF;
1651 END IF;
1652 ELSE
1653 IF G_DEBUG='Y' THEN
1654 AHL_DEBUG_PUB.debug( 'INVALID P_MR_HEADER_ID','+COMPLETE_OPERATION_REVISION+');
1655 ROLLBACK;
1656 END IF;
1657 END IF;
1658
1659 IF l_msg_count > 0
1660 THEN
1661 X_msg_count := l_msg_count;
1662 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1663 RAISE FND_API.G_EXC_ERROR;
1664 END IF;
1665
1666 IF G_DEBUG='Y' THEN
1667 AHL_DEBUG_PUB.debug( 'Before commit Complete_mr_revision Status----->'||p_appr_status,'+DEBUG_RELATIONS+');
1668 END IF;
1669
1670 IF FND_API.TO_BOOLEAN(p_commit) THEN
1671 COMMIT;
1672 END IF;
1673 EXCEPTION
1674 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1675 ROLLBACK TO COMPLETE_OPER_REVISION;
1676 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1677 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1678 p_count => x_msg_count,
1679 p_data => x_msg_data);
1680
1681 WHEN FND_API.G_EXC_ERROR THEN
1682 ROLLBACK TO COMPLETE_OPER_REVISION;
1683 X_return_status := FND_API.G_RET_STS_ERROR;
1684 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1685 p_count => x_msg_count,
1686 p_data => X_msg_data);
1687 WHEN OTHERS THEN
1688 ROLLBACK TO COMPLETE_OPER_REVISION;
1689 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1690 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1691 THEN
1692 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1693 p_procedure_name =>'COMPLETE_OPER_REVISION',
1694 p_error_text =>SUBSTR(SQLERRM,1,240));
1695 END IF;
1696 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1697 p_count => x_msg_count,
1698 p_data => X_msg_data);
1699
1700 END;
1701
1702
1703
1704 END AHL_RM_APPROVAL_PVT;