[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_APPROVAL_PVT
Source
1 PACKAGE BODY AHL_RM_APPROVAL_PVT AS
2 /* $Header: AHLVROAB.pls 120.1.12010000.2 2008/11/23 14:27:22 bachandr 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 l_oper_rec1 get_oper_Det2%rowtype;
299 l_msg VARCHAR2(30);
300 l_start_date DATE;
301 BEGIN
302 SAVEPOINT INITIATE_OPER_APPROVAL;
303
304 -- Check if API is called in debug mode. If yes, enable debug.
305
306 IF G_DEBUG='Y' THEN
307 AHL_DEBUG_PUB.enable_debug;
308 AHL_DEBUG_PUB.debug( 'Enter Initiate OPERATION Approval..','+HEADERS+');
309 END IF;
310
311 -- Standard call to check for call compatibility.
312
313 IF FND_API.to_boolean(l_init_msg_list) THEN
314 FND_MSG_PUB.initialize;
315 END IF;
316
317 -- Initialize API return status to success
318
319 x_return_status :=FND_API.G_RET_STS_SUCCESS;
320
321 -- Get approval process setup.
322 IF (G_PM_INSTALL = 'Y') THEN
323 l_OPER_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_OPERATION_APPRV_ENABLED'), 'N');
324 ELSE
325 l_OPER_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_OPERATION_APPRV_ENABLED'), 'Y');
326 END IF;
327
328 -- Start work Flow Process
329 IF l_OPER_APPR_ENABLED = 'Y'
330 THEN
331 ahl_utility_pvt.get_wf_process_name(
332 p_object =>l_object,
333 x_active =>l_active,
334 x_process_name =>l_process_name ,
335 x_item_type =>l_item_type,
336 x_return_status=>l_return_status,
337 x_msg_count =>l_msg_count,
338 x_msg_data =>l_msg_data);
339 END IF ;
340 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
341 THEN
342 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJ_VERSION_NULL');
343 FND_MSG_PUB.ADD;
344 END IF;
345
346 IF p_source_operation_id is null or p_source_operation_id=FND_API.G_MISS_NUM
347 THEN
348 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJECT_ID_NULL');
349 FND_MSG_PUB.ADD;
350 ELSE
351 OPEN get_oper_Det(p_source_operation_id);
352 FETCH get_oper_Det INTO l_oper_Rec;
353 CLOSE get_oper_Det;
354
355 IF p_apprvl_type = 'APPROVE'
356 THEN
357 IF l_oper_rec.revision_status_code='DRAFT' or
358 l_oper_rec.revision_status_code='APPROVAL_REJECTED'
359 THEN
360 l_upd_status := 'APPROVAL_PENDING';
361 ELSE
362 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_STAT_NOT_DRFT');
363 FND_MESSAGE.set_token('FIELD',l_oper_rec.concatenated_Segments,false);
364 FND_MSG_PUB.ADD;
365 END IF;
366
367 AHL_RM_ROUTE_UTIL.Validate_rt_oper_start_date
368 (
369 p_object_id =>p_source_operation_id,
370 p_association_type =>'OPERATION',
371 p_start_date =>l_oper_rec.start_date_active,
372 x_start_date =>l_start_date,
373 x_msg_data =>l_msg,
374 x_return_status =>l_return_Status
375 );
376
377 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
378 THEN
379 FND_MESSAGE.SET_NAME('AHL',l_msg);
380 FND_MESSAGE.set_token('FIELD',l_start_Date);
381 FND_MSG_PUB.ADD;
382 IF G_DEBUG='Y' THEN
383 AHL_DEBUG_PUB.debug( 'AHL_rm_util.Validate_rt_oper_start_date','+OPER+');
384 END IF;
385 ELSIF TRUNC(l_oper_rec.START_DATE_ACTIVE)<TRUNC(SYSDATE)
386 THEN
387 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INVALID_ST_DATE');
388 FND_MESSAGE.set_token('FIELD',TRUNC(sysdate));
389 FND_MSG_PUB.ADD;
390 END IF;
391 ELSIF p_apprvl_type = 'TERMINATE'
392 THEN
393 --bachandr Enigma Phase I changes -- start
394 Select ENIGMA_OP_ID into l_enig_op_id
395 From ahl_operations_b
396 Where operation_id = p_source_operation_id;
397
398 IF ( l_enig_op_id is not null)
399 THEN
400 --if the operation is from enigma do not allow termination.
401 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_ENIG_TERM');
402 FND_MSG_PUB.ADD;
403
404 END IF;
405 --bachandr Enigma Phase I changes -- end
406
407 IF(
408 l_oper_rec.revision_status_code = 'TERMINATED'
409 )
410 THEN
411 -- if the operation is terminated
412 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_INV_TERMT');
413 FND_MSG_PUB.ADD;
414
415 ELSIF
416 (
417 ( l_oper_rec.END_DATE_ACTIVE IS NOT NULL OR
418 l_oper_rec.END_DATE_ACTIVE<>FND_API.G_MISS_DATE )
419 AND
420 l_oper_rec.revision_status_code = 'COMPLETE'
421 )
422 THEN
423 -- if the operation is coplete and end dated.
424 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_INACTIVE');
425 FND_MSG_PUB.ADD;
426 ELSE
427 -- for all other cases throw all errors;
428 IF l_oper_rec.revision_status_code='COMPLETE' or
429 l_oper_rec.revision_status_code='APPROVAL_TERMINATED'
430 THEN
431 l_upd_status := 'TERMINATION_PENDING';
432
433 SELECT COUNT(*) into l_counter
434 FROM AHL_OPERATIONS_B_KFV
435 WHERE CONCATENATED_SEGMENTS=l_oper_Rec.CONCATENATED_SEGMENTS
436 AND REVISION_NUMBER=l_oper_Rec.revision_number+1;
437
438 IF l_counter>0
439 THEN
440 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_C_TERM');
441 FND_MSG_PUB.ADD;
442 END IF;
443 ELSE
444 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_STAT_NOT_COMP');
445 FND_MESSAGE.set_token('FIELD',l_oper_rec.concatenated_Segments,false);
446 FND_MSG_PUB.ADD;
447 END IF;
448 END IF;
449 END IF;
450
451 END IF;
452
453 l_msg_count := FND_MSG_PUB.count_msg;
454
455 IF l_msg_count > 0
456 THEN
457 X_msg_count := l_msg_count;
458 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459 RAISE FND_API.G_EXC_ERROR;
460 END IF;
461
462 IF l_ACTIVE='Y'
463 THEN
464 Update AHL_OPERATIONS_B
465 Set REVISION_STATUS_CODE=l_upd_status,
466 OBJECT_VERSION_number=OBJECT_VERSION_number+1
467 Where OPERATION_ID=p_source_operation_id
468 and OBJECT_VERSION_NUMBER=p_object_Version_number;
469
470 IF sql%rowcount=0
471 THEN
472 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
473 FND_MSG_PUB.ADD;
474 END IF;
475
476 Update AHL_OPERATIONS_TL
477 Set APPROVER_NOTE=null
478 Where OPERATION_ID=p_source_operation_id;
479
480 IF sql%rowcount=0
481 THEN
482 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
483 FND_MSG_PUB.ADD;
484 END IF;
485
486 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
487 P_OBJECT =>l_object,
488 P_ACTIVITY_ID =>l_activity_id,
489 P_APPROVAL_TYPE =>'CONCEPT',
490 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
491 P_ORIG_STATUS_CODE =>'ACTIVE',
492 P_NEW_STATUS_CODE =>'APPROVED',
493 P_REJECT_STATUS_CODE =>'REJECTED',
494 P_REQUESTER_USERID =>fnd_global.user_id,
495 P_NOTES_FROM_REQUESTER =>NULL,
496 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
497 P_ITEM_TYPE =>'AHLGAPP');
498
499 ELSE
500 UPDATE AHL_OPERATIONS_B
501 SET REVISION_STATUS_CODE=l_upd_status,
502 OBJECT_VERSION_number=OBJECT_VERSION_number+1
503 WHERE OPERATION_ID=p_source_OPERATION_id
504 and OBJECT_VERSION_NUMBER=p_object_Version_number;
505
506
507 IF sql%rowcount=0
508 THEN
509 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
510 FND_MSG_PUB.ADD;
511 END IF;
512 COMPLETE_OPER_REVISION
513 (
514 p_api_version =>p_api_version,
515 p_init_msg_list =>p_init_msg_list,
516 p_commit =>FND_API.G_FALSE,
517 p_validation_level =>p_validation_level ,
518 p_default =>p_default ,
519 p_module_type =>p_module_type,
520 x_return_status =>l_return_status,
521 x_msg_count =>x_msg_count ,
522 x_msg_data =>x_msg_data ,
523 p_appr_status =>'APPROVED',
524 p_operation_id =>p_source_operation_id,
525 p_object_version_number =>l_object_version_number+1,
526 p_approver_note =>null
527 );
528
529 IF G_DEBUG='Y'
530 THEN
531 AHL_DEBUG_PUB.debug( 'After CompleteOperRevision');
532 END IF;
533 END IF ;
534
535
536 l_msg_count := FND_MSG_PUB.count_msg;
537
538 IF l_msg_count > 0
539 THEN
540 X_msg_count := l_msg_count;
541 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 RAISE FND_API.G_EXC_ERROR;
543 END IF;
544
545 IF FND_API.TO_BOOLEAN(p_commit) THEN
546 COMMIT;
547 END IF;
548 EXCEPTION
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550 ROLLBACK TO INITIATE_OPER_APPROVAL;
551 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
553 p_count => x_msg_count,
554 p_data => x_msg_data);
555
556 WHEN FND_API.G_EXC_ERROR THEN
557 ROLLBACK TO INITIATE_OPER_APPROVAL;
558 X_return_status := FND_API.G_RET_STS_ERROR;
559 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
560 p_count => x_msg_count,
561 p_data => X_msg_data);
562 WHEN OTHERS THEN
563 ROLLBACK TO INITIATE_OPER_APPROVAL;
564 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
565 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
566 THEN
567 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
568 p_procedure_name => 'INITIATE_OPER_APPROVAL',
569 p_error_text => SUBSTR(SQLERRM,1,240));
570 END IF;
571 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
572 p_count => x_msg_count,
573 p_data => X_msg_data);
574 END;
575
576 PROCEDURE INITIATE_ROUTE_APPROVAL
577 (
578 p_api_version IN NUMBER:= 1.0,
579 p_init_msg_list IN VARCHAR2,
580 p_commit IN VARCHAR2,
581 p_validation_level IN NUMBER,
582 p_default IN VARCHAR2 := FND_API.G_FALSE,
583 p_module_type IN VARCHAR2,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2,
587 p_source_route_id IN NUMBER,
588 p_object_Version_number IN NUMBER,
589 p_apprvl_type IN VARCHAR2
590 )
591 AS
592 CURSOR get_route_det(c_route_id NUMBER)
593 IS
594 SELECT *
595 FROM AHL_ROUTES_APP_V
596 WHERE route_id = c_route_id;
597 l_route_rec get_route_det%rowtype;
598 l_msg VARCHAR2(30);
599 l_start_date DATE;
600 l_counter NUMBER:=0;
601 l_status VARCHAR2(30);
602 l_object VARCHAR2(30):='RM';
603 l_approval_type VARCHAR2(100):='CONCEPT';
604 l_active VARCHAR2(50) := 'N';
605 l_process_name VARCHAR2(50);
606 l_item_type VARCHAR2(50);
607 l_return_status VARCHAR2(50);
608 l_msg_count NUMBER;
609 l_msg_data VARCHAR2(2000);
610 l_activity_id NUMBER:=p_source_route_id;
611 l_Status VARCHAR2(1);
612 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
613 l_object_Version_number NUMBER:=nvl(p_object_Version_number,0);
614 l_upd_status VARCHAR2(50);
615 l_rev_status VARCHAR2(50);
616 l_new_status VARCHAR2(50);
617
618 l_ROUTE_APPR_ENABLED VARCHAR2(30);
619 --bachandr Enigma Phase I changes -- start
620 l_enigma_doc_id VARCHAR2(30);
621 --bachandr Enigma Phase I changes -- end
622
623 BEGIN
624 SAVEPOINT INITIATE_ROUTE_APPROVAL;
625
626 -- Check if API is called in debug mode. If yes, enable debug.
627
628 IF G_DEBUG='Y' THEN
629 AHL_DEBUG_PUB.enable_debug;
630 AHL_DEBUG_PUB.debug( 'Enter Initiate Route Approval','+ROUTES+');
631 AHL_DEBUG_PUB.debug( 'p_approval_type'||p_apprvl_type,'+ROUTES+');
632 END IF;
633
634 -- Standard call to check for call compatibility.
635
636 IF FND_API.to_boolean(l_init_msg_list) THEN
637 FND_MSG_PUB.initialize;
638 END IF;
639
640 -- Initialize API return status to success
641
642 x_return_status := FND_API.G_RET_STS_SUCCESS;
643
644 -- Set default for CMRO and PM modes.
645 IF (G_PM_INSTALL = 'Y') THEN
646 l_ROUTE_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_ROUTE_APPRV_ENABLED'),'N');
647 ELSE
648 l_ROUTE_APPR_ENABLED := nvl(FND_PROFILE.VALUE('AHL_RM_ROUTE_APPRV_ENABLED'),'Y');
649 END IF;
650
651
652 --Before calling ahl_utility_pvt.get_wf_process_name()
653 -- Validate Application Usage
654 AHL_RM_ROUTE_UTIL .validate_ApplnUsage
655 (
656 p_object_id => p_source_route_id,
657 p_association_type => 'ROUTE',
658 x_return_status => x_return_status,
659 x_msg_data => x_msg_data
660 );
661
662 -- If any severe error occurs, then, abort API.
663 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
664 RAISE FND_API.G_EXC_ERROR;
665 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 END IF;
668 -- added code : prithwi : 29.9.2003
669 OPEN get_route_det(p_source_route_id);
670 FETCH get_route_det INTO l_route_rec;
671 CLOSE get_route_det;
672
673 -- Start work Flow Process
674 IF l_ROUTE_APPR_ENABLED = 'Y'
675 THEN
676 ahl_utility_pvt.get_wf_process_name(
677 p_object =>l_object,
678 p_application_usg_code => l_route_rec.application_usg_code,
679 x_active =>l_active,
680 x_process_name =>l_process_name ,
681 x_item_type =>l_item_type,
682 x_return_status=>l_return_status,
683 x_msg_count =>l_msg_count,
684 x_msg_data =>l_msg_data);
685 END IF ;
686
687 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
688 THEN
689 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJ_VERSION_NULL');
690 FND_MSG_PUB.ADD;
691 END IF;
692
693 IF p_source_route_id is null or p_source_route_id=FND_API.G_MISS_NUM
694 THEN
695 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OBJECT_ID_NULL');
696 FND_MSG_PUB.ADD;
697 ELSE
698 OPEN get_route_det(p_source_route_id);
699 FETCH get_route_det INTO l_route_rec;
700 CLOSE get_route_det;
701 IF p_apprvl_type = 'APPROVE'
702 THEN
703 AHL_RM_ROUTE_UTIL.Validate_rt_oper_start_date
704 (
705 p_object_id =>p_source_route_id,
706 p_association_type =>'ROUTE',
707 p_start_date =>l_route_rec.start_date_active,
708 x_start_date =>l_start_date,
709 x_msg_data =>l_msg,
710 x_return_status =>l_return_Status
711 );
712
713 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
714 THEN
715 FND_MESSAGE.SET_NAME('AHL',l_msg);
716 FND_MESSAGE.set_token('FIELD',l_start_Date);
717 FND_MSG_PUB.ADD;
718 IF G_DEBUG='Y' THEN
719 AHL_DEBUG_PUB.debug('AHL_rm_util.Validate_rt_oper_start_date','+ROUTE+');
720 END IF;
721 ELSIF TRUNC(l_route_rec.START_DATE_ACTIVE)<TRUNC(SYSDATE)
722 THEN
723 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INVALID_ST_DATE');
724 FND_MESSAGE.set_token('FIELD',TRUNC(sysdate));
725 FND_MSG_PUB.ADD;
726 END IF;
727
728 IF l_route_rec.revision_Status_code = 'DRAFT' or
729 l_route_rec.revision_Status_code='APPROVAL_REJECTED'
730 THEN
731 l_upd_status := 'APPROVAL_PENDING';
732 ELSE
733 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RO_STAT_NOT_DRFT');
734 FND_MESSAGE.set_token('FIELD',l_route_rec.route_no);
735 FND_MSG_PUB.ADD;
736 END IF;
737
738 --bachandr Enigma Phase I changes -- start
739 -- When the workflow type is approve , check if time span is entered as it is mandatory for
740 -- approval flow. If not throw an error.
741 IF l_route_rec.time_span is null or l_route_rec.time_span =FND_API.G_MISS_NUM
742 THEN
743 FND_MESSAGE.SET_NAME('AHL','AHL_RM_TIME_SPAN_NULL');
744 FND_MSG_PUB.ADD;
745 END IF;
746 --bachandr Enigma Phase I changes -- end
747
748 ELSIF p_apprvl_type = 'TERMINATE'
749 THEN
750
751 --bachandr Enigma Phase I changes -- start
752 Select ENIGMA_DOC_ID into l_enigma_doc_id
753 From ahl_routes_b
754 Where route_id = p_source_route_id;
755
756 IF ( l_enigma_doc_id is not null)
757 THEN
758 --if the route is from enigma do not allow termination.
759 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_ENIG_TERM');
760 FND_MSG_PUB.ADD;
761 END IF;
762 --bachandr Enigma Phase I changes -- end
763
764 IF (
765 (l_route_rec.END_DATE_ACTIVE IS NOT NULL OR
766 l_route_rec.END_DATE_ACTIVE<>FND_API.G_MISS_DATE
767 )
768 AND
769 l_route_rec.revision_status_code = 'TERMINATED'
770 )
771 THEN
772 -- if the Route is TERMINATED
773 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_INV_TERMT');
774 FND_MSG_PUB.ADD;
775 ELSIF
776 (
777 (l_route_rec.END_DATE_ACTIVE IS NOT NULL OR
778 l_route_rec.END_DATE_ACTIVE<>FND_API.G_MISS_DATE
779 )
780 AND
781 l_route_rec.revision_status_code = 'COMPLETE'
782 )
783 THEN
784 -- if the Route is COMPLETE and End Dated.
785 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROUTE_INACTIVE');
786 FND_MSG_PUB.ADD;
787 ELSE
788 -- for all other cases throw all errors
789
790
791 /*SELECT COUNT(*) into l_counter
792 FROM AHL_ROUTE_MR_V
793 WHERE ROUTE_ID=l_route_Rec.ROUTE_ID
794 AND trunc(nvl(AHL_ROUTE_MR_V.EFFECTIVE_TO,SYSDATE))>=trunc(SYSDATE);*/
795
796 --AMSRINIV : Bug 4913294. Tuned above commented query.
797 SELECT COUNT(*) into l_counter
798 from ahl_mr_routes a, ahl_mr_headers_b b
799 where
800 a.route_id = l_route_rec.route_id and
801 a.mr_header_id = b.mr_header_id and
802 b.application_usg_code = rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE'))) and
803 trunc(nvl(b.effective_to,sysdate)) >= trunc(sysdate);
804 IF l_counter>0
805 THEN
806 FND_MESSAGE.SET_NAME('AHL','AHL_RM_MR_C_TERM');
807 FND_MSG_PUB.ADD;
808 END IF;
809
810 IF l_route_rec.revision_status_code ='COMPLETE' or
811 l_route_rec.revision_Status_code='APPROVAL_TERMINATED'
812 THEN
813 SELECT COUNT(*) into l_counter
814 FROM AHL_ROUTES_APP_V
815 WHERE UPPER(TRIM(ROUTE_NO))=UPPER(TRIM(l_route_Rec.route_no))
816 AND REVISION_NUMBER=l_route_Rec.revision_number+1;
817
818 IF l_counter>0
819 THEN
820 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RT_C_TERM');
821 FND_MSG_PUB.ADD;
822 END IF;
823
824 l_upd_status := 'TERMINATION_PENDING';
825 ELSE
826 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RO_STAT_NOT_COMP');
827 FND_MESSAGE.set_token('FIELD',l_route_rec.route_no);
828 FND_MSG_PUB.ADD;
829 END IF;
830 END IF;
831 END IF;
832 END IF;
833
834 l_msg_count := FND_MSG_PUB.count_msg;
835
836 IF l_msg_count > 0
837 THEN
838 X_msg_count := l_msg_count;
839 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842
843 IF l_ACTIVE='Y'
844 THEN
845 UPDATE AHL_ROUTES_B
846 SET REVISION_STATUS_CODE=l_upd_status,
847 OBJECT_VERSION_number=OBJECT_VERSION_number+1
848 WHERE ROUTE_ID=p_source_route_id
849 and OBJECT_VERSION_NUMBER=p_object_Version_number;
850
851 IF sql%rowcount=0
852 THEN
853 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
854 FND_MSG_PUB.ADD;
855 END IF ;
856
857 UPDATE AHL_ROUTES_TL
858 SET APPROVER_NOTE=null
859 WHERE ROUTE_ID=p_source_route_id;
860
861 IF sql%rowcount=0
862 THEN
863 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
864 FND_MSG_PUB.ADD;
865 END IF ;
866
867
868 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
869 P_OBJECT =>l_object,
870 P_ACTIVITY_ID =>l_activity_id,
871 P_APPROVAL_TYPE =>'CONCEPT',
872 P_APPLICATION_USG_CODE =>l_route_rec.application_usg_code,
873 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
874 P_ORIG_STATUS_CODE =>'ACTIVE',
875 P_NEW_STATUS_CODE =>'APPROVED',
876 P_REJECT_STATUS_CODE =>'REJECTED',
877 P_REQUESTER_USERID =>fnd_global.user_id,
878 P_NOTES_FROM_REQUESTER =>NULL,
879 -- P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
880 P_WORKFLOWPROCESS =>l_process_name,
881 P_ITEM_TYPE =>'AHLGAPP');
882
883 ELSE
884 UPDATE AHL_ROUTES_B
885 SET REVISION_STATUS_CODE=l_upd_status,
886 OBJECT_VERSION_number=OBJECT_VERSION_number+1
887 WHERE ROUTE_ID=p_source_route_id
888 and OBJECT_VERSION_NUMBER=p_object_Version_number;
889
890 IF sql%rowcount=0
891 THEN
892 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
893 FND_MSG_PUB.ADD;
894
895 END IF;
896
897 COMPLETE_ROUTE_REVISION
898 (
899 p_api_version =>p_api_version,
900 p_init_msg_list =>p_init_msg_list,
901 p_commit =>FND_API.G_FALSE,
902 p_validation_level =>p_validation_level ,
903 p_default =>p_default ,
904 p_module_type =>p_module_type,
905 x_return_status =>l_return_status,
906 x_msg_count =>x_msg_count ,
907 x_msg_data =>x_msg_data ,
908 p_appr_status =>'APPROVED',
909 p_route_id =>p_source_route_id,
910 p_object_version_number =>l_object_version_number+1,
911 p_approver_note =>null
912 );
913 IF G_DEBUG='Y'
914 THEN
915 AHL_DEBUG_PUB.debug( 'After CompleteRouteRevision');
916 END IF;
917 END IF ;
918
919 l_msg_count := FND_MSG_PUB.count_msg;
920
921 IF l_msg_count > 0
922 THEN
923 X_msg_count := l_msg_count;
924 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927
928
929 IF FND_API.TO_BOOLEAN(p_commit) THEN
930 COMMIT;
931 END IF;
932
933 EXCEPTION
934 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
935 ROLLBACK TO INITIATE_ROUTE_APPROVAL;
936 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
938 p_count => x_msg_count,
939 p_data => x_msg_data);
940
941 WHEN FND_API.G_EXC_ERROR THEN
942 ROLLBACK TO INITIATE_ROUTE_APPROVAL;
943 X_return_status := FND_API.G_RET_STS_ERROR;
944 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
945 p_count => x_msg_count,
946 p_data => X_msg_data);
947 WHEN OTHERS THEN
948 ROLLBACK TO INITIATE_ROUTE_APPROVAL;
949 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
951 THEN
952 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RM_ROUTE_PUB',
953 p_procedure_name => 'INITIATE_ROUTE_APPROVAL',
954 p_error_text => SUBSTR(SQLERRM,1,240));
955 END IF;
956 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
957 p_count => x_msg_count,
958 p_data => X_msg_data);
959 END INITIATE_ROUTE_APPROVAL;
960
961 PROCEDURE COMPLETE_ROUTE_REVISION
962 (
963 p_api_version IN NUMBER:= 1.0,
964 p_init_msg_list IN VARCHAR2,
965 p_commit IN VARCHAR2,
966 p_validation_level IN NUMBER,
967 p_default IN VARCHAR2 := FND_API.G_FALSE,
968 p_module_type IN VARCHAR2,
969 x_return_status OUT NOCOPY VARCHAR2,
970 x_msg_count OUT NOCOPY NUMBER,
971 x_msg_data OUT NOCOPY VARCHAR2,
972 p_appr_status IN VARCHAR2,
973 p_route_id IN NUMBER,
974 p_object_version_number IN NUMBER,
975 p_approver_note IN VARCHAR2 := null
976 )
977 AS
978
979 CURSOR GetRouteDet(C_ROUTE_ID NUMBER)
980 IS
981 SELECT ROUTE_ID,ROUTE_NO,REVISION_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE
982 FROM AHL_ROUTES_APP_V
983 WHERE ROUTE_ID=C_ROUTE_ID;
984
985 CURSOR GetPrevRouteDet(C_REVISION_NUMBER NUMBER,C_ROUTE_NO VARCHAR2)
986 IS
987 SELECT ROUTE_ID,ROUTE_NO,REVISION_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE
988 FROM AHL_ROUTES_APP_V
989 WHERE UPPER(TRIM(ROUTE_NO))=UPPER(TRIM(C_ROUTE_NO))
990 AND REVISION_NUMBER=C_REVISION_NUMBER-1;
991
992 l_route_rec GetRouteDet%rowtype;
993 l_prev_route_rec GetPrevRouteDet%rowtype;
994 l_status VARCHAR2(30);
995 l_mr_status VARCHAR2(30);
996 l_check_flag VARCHAR2(1):='N';
997 l_check_flag2 VARCHAR2(1):='N';
998 l_check_flag3 VARCHAR2(1):='Y';
999 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_ROUTE_REVISION';
1000 l_api_version CONSTANT NUMBER := 1.0;
1001 l_num_rec NUMBER;
1002 l_msg_count NUMBER;
1003 l_msg_data VARCHAR2(2000);
1004 l_return_status VARCHAR2(1);
1005 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1006 l_fr_date DATE:=SYSDATE;
1007 l_to_Date DATE:=SYSDATE;
1008 l_commit VARCHAR2(10):=FND_API.G_TRUE;
1009 l_curr_status VARCHAR2(30);
1010
1011
1012 l_object VARCHAR2(30):='RM';
1013 l_approval_type VARCHAR2(100):='CONCEPT';
1014 l_active VARCHAR2(50);
1015 l_process_name VARCHAR2(50);
1016 l_item_type VARCHAR2(50);
1017
1018 -- Routes
1019 CURSOR CurGetRoutedet(C_ROUTE_ID NUMBER)
1020 IS
1021 SELECT * from AHL_MR_ROUTES
1022 WHERE ROUTE_ID=C_ROUTE_ID;
1023 l_mr_route_rec CurGetRoutedet%rowtype;
1024
1025 l_new_mr_ROUTE_ID NUMBER:=0;
1026 l_new_mr_ROUTE_SEQ_ID NUMBER:=0;
1027 l_old_mr_route_id NUMBER:=0;
1028 l_seq_mr_route_id NUMBER:=0;
1029 l_seq_rel_mr_route_id NUMBER:=0;
1030 -- Route Sequences
1031 CURSOR CurGetRouteSeqDet(C_MR_ROUTE_ID NUMBER)
1032 iS
1033 SELECT * FROM AHL_MR_ROUTE_SEQUENCES
1034 WHERE (MR_ROUTE_ID=C_MR_ROUTE_ID OR RELATED_MR_ROUTE_ID=C_MR_ROUTE_ID);
1035
1036 l_mr_route_seq_rec CurGetRouteSeqDet%rowtype;
1037 l_rowid VARCHAR2(30);
1038 BEGIN
1039
1040 SAVEPOINT COMPLETE_ROUTE_REVISION;
1041
1042 IF G_DEBUG='Y' THEN
1043 AHL_DEBUG_PUB.enable_debug;
1044 END IF;
1045
1046 SELECT REVISION_STATUS_CODE INTO l_curr_status
1047 FROM AHL_ROUTES_APP_V WHERE ROUTE_ID=p_route_id;
1048
1049
1050 IF p_appr_status='APPROVED'
1051 THEN
1052 l_status:='COMPLETE';
1053 ELSE
1054 IF l_curr_status='APPROVAL_PENDING'
1055 THEN
1056 l_status:='APPROVAL_REJECTED';
1057 ELSE
1058 l_status:='COMPLETE';
1059 END IF;
1060 l_check_flag3:='N';
1061 END IF;
1062
1063 IF p_route_id is not null or p_route_id<>fnd_api.g_miss_num
1064 THEN
1065 IF G_DEBUG='Y'
1066 THEN
1067 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:p_route_id'||p_route_id);
1068 END IF;
1069 OPEN GetRouteDet(p_route_id);
1070
1071 FETCH GetRouteDet INTO l_route_rec;
1072
1073 IF GetRouteDet%NOTFOUND
1074 THEN
1075 l_check_flag:='N';
1076 ELSE
1077 l_check_flag:='Y';
1078 IF trunc(l_route_rec.Start_date_active) >trunc(sysdate)
1079 THEN
1080 l_fr_date:=l_route_rec.start_date_active;
1081 l_to_date:=l_route_rec.start_date_active;
1082 ELSE
1083 l_fr_date:=sysdate;
1084 l_to_date:=sysdate;
1085 END IF;
1086 END IF;
1087
1088 CLOSE GetRouteDet;
1089 IF l_check_flag='Y' and p_appr_status='REJECTED'
1090 Then
1091 UPDATE AHL_ROUTES_B
1092 SET REVISION_STATUS_CODE=l_status,
1093 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1094 WHERE ROUTE_ID=P_ROUTE_ID;
1095
1096 UPDATE AHL_ROUTES_TL
1097 SET APPROVER_NOTE=p_approver_note
1098 WHERE ROUTE_ID=P_ROUTE_ID;
1099 Elsif l_check_flag='Y' and l_curr_status='TERMINATION_PENDING' and
1100 p_appr_status='APPROVED'
1101 Then
1102 UPDATE AHL_ROUTES_B
1103 SET REVISION_STATUS_CODE='TERMINATED',
1104 -- START_DATE_ACTIVE=l_fr_date,
1105 END_DATE_ACTIVE=l_to_date,
1106 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1107 WHERE ROUTE_ID=P_ROUTE_ID;
1108
1109 -- Do not call for Preventive Maintenance application Mode.
1110 IF (G_PM_INSTALL <> 'Y') THEN
1111 ahl_utility_pvt.get_wf_process_name(
1112 p_object =>'RM_NTF',
1113 x_active =>l_active,
1114 x_process_name =>l_process_name ,
1115 x_item_type =>l_item_type,
1116 x_return_status=>l_return_status,
1117 x_msg_count =>l_msg_count,
1118 x_msg_data =>l_msg_data);
1119
1120 IF (l_active = 'Y') THEN
1121 NOTIFY_TERMINATION
1122 (
1123 x_return_status =>l_return_status,
1124 x_msg_count =>l_msg_count,
1125 x_msg_data =>l_msg_data,
1126 p_object_type =>G_OBJECT_TYPE,
1127 p_prim_object_type =>'RM',
1128 p_activity_id =>p_ROUTE_ID
1129 );
1130 END IF;
1131 END IF;
1132 Elsif l_check_flag='Y' and l_route_rec.revision_number=1 and
1133 l_curr_status='APPROVAL_PENDING' AND p_appr_status='APPROVED'
1134 THEN
1135 UPDATE AHL_ROUTES_B
1136 SET REVISION_STATUS_CODE=l_status,
1137 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1138 WHERE ROUTE_ID=P_ROUTE_ID;
1139
1140 l_check_flag:='Y';
1141
1142 l_check_flag2:='Y';
1143 IF G_DEBUG='Y'
1144 THEN
1145 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:p_route_id'||p_route_id);
1146 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_status'||l_status);
1147 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_route_rec.revision_number'||l_route_rec.revision_number);
1148 END IF;
1149 Elsif l_check_flag='Y' and l_route_rec.revision_number>1 and
1150 l_curr_status='APPROVAL_PENDING' AND p_appr_status='APPROVED'
1151 THEN
1152 IF G_DEBUG='Y'
1153 THEN
1154 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:p_route_id'||p_route_id);
1155 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_status'||l_status);
1156 AHL_DEBUG_PUB.debug( 'Inside CompleteRouteRevision:l_route_rec.revision_number'||l_route_rec.revision_number);
1157 END IF;
1158 OPEN GetPrevRouteDet(l_route_rec.revision_number,l_route_rec.route_no);
1159
1160 FETCH GetPrevRouteDet INTO l_prev_route_rec;
1161
1162 IF GetPrevRouteDet%NOTFOUND
1163 THEN
1164 l_check_flag2:='N';
1165 ELSE
1166 l_check_flag2:='Y';
1167 END IF;
1168
1169 CLOSE GetPrevRouteDet;
1170
1171 IF l_check_flag2='Y'
1172 THEN
1173
1174 UPDATE AHL_ROUTES_B
1175 SET REVISION_STATUS_CODE= l_status,
1176 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1177 START_DATE_ACTIVE=l_fr_date
1178 WHERE ROUTE_ID=P_ROUTE_ID;
1179
1180 IF l_check_flag3='Y'
1181 THEN
1182 UPDATE AHL_ROUTES_B
1183 SET REVISION_STATUS_CODE= l_status,
1184 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1185 END_DATE_ACTIVE=l_to_date
1186 WHERE ROUTE_ID=l_prev_route_rec.ROUTE_ID;
1187
1188 IF l_check_flag2='Y'
1189 THEN
1190
1191 open CurGetRoutedet(l_prev_route_rec.ROUTE_ID);
1192 --open CurGetRoutedet(P_ROUTE_ID);
1193 loop
1194 fetch CurGetRoutedet into l_mr_route_rec;
1195 IF CurGetRoutedet%FOUND
1196 THEN
1197
1198 SELECT AHL_MR_ROUTES_S.NEXTVAL
1199 INTO l_new_mr_ROUTE_ID
1200 FROM DUAL;
1201
1202 l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
1203
1204 AHL_MR_ROUTES_PKG.INSERT_ROW (
1205 -- X_ROWID =>L_ROWID,
1206 X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
1207 X_MR_HEADER_ID =>l_mr_route_Rec.mr_header_id,
1208 X_ROUTE_ID =>P_ROUTE_ID,
1209 X_STAGE =>l_mr_route_Rec.STAGE,
1210 X_ATTRIBUTE_CATEGORY =>l_mr_route_Rec.ATTRIBUTE_CATEGORY,
1211 X_ATTRIBUTE1 =>l_mr_route_Rec.ATTRIBUTE1,
1212 X_ATTRIBUTE2 =>l_mr_route_Rec.ATTRIBUTE2,
1213 X_ATTRIBUTE3 =>l_mr_route_Rec.ATTRIBUTE3,
1214 X_ATTRIBUTE4 =>l_mr_route_Rec.ATTRIBUTE4,
1215 X_ATTRIBUTE5 =>l_mr_route_Rec.ATTRIBUTE5,
1216 X_ATTRIBUTE6 =>l_mr_route_Rec.ATTRIBUTE6,
1217 X_ATTRIBUTE7 =>l_mr_route_Rec.ATTRIBUTE7,
1218 X_ATTRIBUTE8 =>l_mr_route_Rec.ATTRIBUTE8,
1219 X_ATTRIBUTE9 =>l_mr_route_Rec.ATTRIBUTE9,
1220 X_ATTRIBUTE10 =>l_mr_route_Rec.ATTRIBUTE10,
1221 X_ATTRIBUTE11 =>l_mr_route_Rec.ATTRIBUTE11,
1222 X_ATTRIBUTE12 =>l_mr_route_Rec.ATTRIBUTE12,
1223 X_ATTRIBUTE13 =>l_mr_route_Rec.ATTRIBUTE13,
1224 X_ATTRIBUTE14 =>l_mr_route_Rec.ATTRIBUTE14,
1225 X_ATTRIBUTE15 =>l_mr_route_Rec.ATTRIBUTE15,
1226 X_OBJECT_VERSION_NUMBER =>1,
1227 X_CREATION_DATE =>sysdate,
1228 X_CREATED_BY =>fnd_global.user_id,
1229 X_LAST_UPDATE_DATE =>sysdate,
1230 X_LAST_UPDATED_BY =>fnd_global.user_id,
1231 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
1232 OPEN CurGetRouteSeqDet(l_old_mr_ROUTE_ID);
1233 LOOP
1234 FETCH CurGetRouteSeqDet INTO l_mr_route_seq_rec;
1235 IF CurGetRouteSeqDet%FOUND
1236 THEN
1237 SELECT AHL_MR_ROUTE_SEQUENCES_S .NEXTVAL
1238 INTO l_new_mr_ROUTE_SEQ_ID FROM DUAL;
1239 IF l_mr_route_seq_rec.mr_route_id=l_old_mr_route_id
1240 THEN
1241 l_seq_rel_mr_route_id:=l_mr_route_seq_rec.related_mr_route_id;
1242 l_seq_mr_route_id:=l_new_mr_ROUTE_ID;
1243
1244 ELSIF l_mr_route_seq_rec.related_mr_route_id=l_old_mr_route_id
1245 THEN
1246 l_seq_mr_route_id:=l_mr_route_seq_rec.mr_route_id;
1247 l_seq_rel_mr_route_id:=l_new_mr_ROUTE_ID;
1248 END IF;
1249 AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW (
1250 X_MR_ROUTE_SEQUENCE_ID =>l_new_mr_route_seq_id,
1251 X_RELATED_MR_ROUTE_ID =>l_seq_rel_mr_route_id,
1252 X_SEQUENCE_CODE =>l_mr_route_seq_rec.SEQUENCE_CODE,
1253 X_MR_ROUTE_ID =>l_seq_mr_route_id,
1254 X_OBJECT_VERSION_NUMBER =>1,
1255 X_ATTRIBUTE_CATEGORY =>l_mr_route_seq_rec.ATTRIBUTE_CATEGORY,
1256 X_ATTRIBUTE1 =>l_mr_route_seq_rec.ATTRIBUTE1,
1257 X_ATTRIBUTE2 =>l_mr_route_seq_rec.ATTRIBUTE2,
1258 X_ATTRIBUTE3 =>l_mr_route_seq_rec.ATTRIBUTE3,
1259 X_ATTRIBUTE4 =>l_mr_route_seq_rec.ATTRIBUTE4,
1260 X_ATTRIBUTE5 =>l_mr_route_seq_rec.ATTRIBUTE5,
1261 X_ATTRIBUTE6 =>l_mr_route_seq_rec.ATTRIBUTE6,
1262 X_ATTRIBUTE7 =>l_mr_route_seq_rec.ATTRIBUTE7,
1263 X_ATTRIBUTE8 =>l_mr_route_seq_rec.ATTRIBUTE8,
1264 X_ATTRIBUTE9 =>l_mr_route_seq_rec.ATTRIBUTE9,
1265 X_ATTRIBUTE10 =>l_mr_route_seq_rec.ATTRIBUTE10,
1266 X_ATTRIBUTE11 =>l_mr_route_seq_rec.ATTRIBUTE11,
1267 X_ATTRIBUTE12 =>l_mr_route_seq_rec.ATTRIBUTE12,
1268 X_ATTRIBUTE13 =>l_mr_route_seq_rec.ATTRIBUTE13,
1269 X_ATTRIBUTE14 =>l_mr_route_seq_rec.ATTRIBUTE14,
1270 X_ATTRIBUTE15 =>l_mr_route_seq_rec.ATTRIBUTE15,
1271 X_CREATION_DATE =>sysdate,
1272 X_CREATED_BY =>fnd_global.user_id,
1273 X_LAST_UPDATE_DATE =>sysdate,
1274 X_LAST_UPDATED_BY =>fnd_global.user_id,
1275 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
1276 ELSE
1277 EXIT WHEN CurGetRouteSeqDet%NOTFOUND;
1278 END IF;
1279 END LOOP;
1280 CLOSE CurGetRouteSeqDet;
1281
1282 ELSE
1283 EXIT WHEN CurGetRoutedet%NOTFOUND;
1284 END IF;
1285
1286 END LOOP;
1287
1288 END IF;
1289 close CurGetRoutedet;
1290
1291 -- Do not call for Preventive Maintenance application Mode.
1292 IF (G_PM_INSTALL <> 'Y') THEN
1293 ahl_utility_pvt.get_wf_process_name(
1294 p_object =>'RM_NTF',
1295 x_active =>l_active,
1296 x_process_name =>l_process_name ,
1297 x_item_type =>l_item_type,
1298 x_return_status=>l_return_status,
1299 x_msg_count =>l_msg_count,
1300 x_msg_data =>l_msg_data);
1301
1302 IF (l_active = 'Y') THEN
1303
1304 NOTIFY_TERMINATION
1305 (
1306 x_return_status =>l_return_status,
1307 x_msg_count =>l_msg_count,
1308 x_msg_data =>l_msg_data,
1309 p_object_type =>G_OBJECT_TYPE,
1310 p_prim_object_type =>'RM',
1311 p_activity_id =>l_prev_route_rec.ROUTE_ID
1312 );
1313 END IF;
1314 END IF;
1315
1316 else
1317 UPDATE AHL_ROUTES_B
1318 SET REVISION_STATUS_CODE= l_status,
1319 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1320 END_DATE_ACTIVE=l_to_date
1321 WHERE ROUTE_ID=l_prev_route_rec.ROUTE_ID;
1322 END IF;
1323 END IF;
1324 END IF;
1325
1326 END IF;
1327
1328 IF l_msg_count > 0
1329 THEN
1330 X_msg_count := l_msg_count;
1331 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334
1335 IF G_DEBUG='Y' THEN
1336 AHL_DEBUG_PUB.debug( 'Before commit Complete_route_revision Status----->'||p_appr_status,'+DEBUG_RELATIONS+');
1337 END IF;
1338
1339 IF FND_API.TO_BOOLEAN(p_commit) THEN
1340 COMMIT;
1341 END IF;
1342 EXCEPTION
1343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1344 ROLLBACK TO COMPLETE_ROUTE_REVISION;
1345 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1346 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1347 p_count => x_msg_count,
1348 p_data => x_msg_data);
1349
1350 WHEN FND_API.G_EXC_ERROR THEN
1351 ROLLBACK TO COMPLETE_MR_REVISION;
1352 X_return_status := FND_API.G_RET_STS_ERROR;
1353 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1354 p_count => x_msg_count,
1355 p_data => X_msg_data);
1356 WHEN OTHERS THEN
1357 ROLLBACK TO COMPLETE_MR_REVISION;
1358 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1360 THEN
1361 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1362 p_procedure_name =>'COMPLETE_ROUTE_REVISION',
1363 p_error_text =>SUBSTR(SQLERRM,1,240));
1364 END IF;
1365 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1366 p_count => x_msg_count,
1367 p_data => X_msg_data);
1368
1369 END;
1370
1371 PROCEDURE COMPLETE_OPER_REVISION
1372 (
1373 p_api_version IN NUMBER:= 1.0,
1374 p_init_msg_list IN VARCHAR2,
1375 p_commit IN VARCHAR2,
1376 p_validation_level IN NUMBER,
1377 p_default IN VARCHAR2 := FND_API.G_FALSE,
1378 p_module_type IN VARCHAR2,
1379 x_return_status OUT NOCOPY VARCHAR2,
1380 x_msg_count OUT NOCOPY NUMBER,
1381 x_msg_data OUT NOCOPY VARCHAR2,
1382 p_appr_status IN VARCHAR2,
1383 p_operation_id IN NUMBER,
1384 p_object_version_number IN NUMBER,
1385 p_approver_note IN VARCHAR2 := null
1386 )
1387 AS
1388 CURSOR GetOperationDet(C_OPERATION_ID NUMBER)
1389 IS
1390 SELECT OPERATION_ID,
1391 CONCATENATED_SEGMENTS,
1392 REVISION_NUMBER,
1393 START_DATE_ACTIVE,
1394 END_DATE_ACTIVE
1395 FROM AHL_OPERATIONS_B_KFV
1396 WHERE OPERATION_ID=C_OPERATION_ID;
1397
1398 CURSOR GetPrevOperDet(C_REVISION_NUMBER NUMBER,C_CONCATENATED_SEGMENTS VARCHAR2)
1399 IS
1400 SELECT OPERATION_ID,
1401 CONCATENATED_SEGMENTS,
1402 REVISION_NUMBER,
1403 START_DATE_ACTIVE,
1404 END_DATE_ACTIVE
1405 FROM AHL_OPERATIONS_B_KFV
1406 WHERE CONCATENATED_SEGMENTS=C_CONCATENATED_SEGMENTS
1407 AND REVISION_NUMBER=C_REVISION_NUMBER-1;
1408
1409 l_oper_rec GetOperationDet%rowtype;
1410 l_prev_oper_rec GetPrevOperDet%rowtype;
1411 l_status VARCHAR2(30);
1412 l_curr_status VARCHAR2(30);
1413 l_curr_atus VARCHAR2(30);
1414 l_check_flag VARCHAR2(1):='N';
1415 l_check_flag2 VARCHAR2(1):='N';
1416 l_check_flag3 VARCHAR2(1):='Y';
1417 l_api_name CONSTANT VARCHAR2(30) := 'COMPLETE_MR_REVISION';
1418 l_api_version CONSTANT NUMBER := 1.0;
1419 l_num_rec NUMBER;
1420 l_msg_count NUMBER;
1421 l_msg_data VARCHAR2(2000);
1422 l_return_status VARCHAR2(1);
1423 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1424 l_fr_date DATE:=SYSDATE;
1425 l_to_Date DATE:=SYSDATE;
1426 l_commit VARCHAR2(10):=FND_API.G_TRUE;
1427 l_active VARCHAR2(50);
1428 l_process_name VARCHAR2(50);
1429 l_item_type VARCHAR2(50);
1430
1431 BEGIN
1432 SAVEPOINT COMPLETE_OPER_REVISION;
1433
1434 IF G_DEBUG='Y' THEN
1435 AHL_DEBUG_PUB.enable_debug;
1436 AHL_DEBUG_PUB.debug( 'Complete_mr_revision Status----->'||p_appr_status,'+DEBUG_RELATIONS+');
1437 END IF;
1438
1439 SELECT REVISION_STATUS_CODE INTO l_curr_status
1440 FROM AHL_OPERATIONS_B_KFV WHERE OPERATION_ID=p_operation_id;
1441
1442 IF p_appr_status='APPROVED'
1443 THEN
1444 l_status:='COMPLETE';
1445 ELSE
1446 IF l_curr_status='APPROVAL_PENDING'
1447 THEN
1448 l_status:='APPROVAL_REJECTED';
1449 ELSE
1450 l_status:='COMPLETE';
1451 END IF;
1452 l_check_flag3:='N';
1453 END IF;
1454
1455 IF G_DEBUG='Y' THEN
1456 AHL_DEBUG_PUB.debug( 'Complete_operation_revision Status2----->'||L_status,'+DEBUG_REVISION+');
1457 AHL_DEBUG_PUB.debug( 'Complete_mr_revision Status2----->'||L_status,'+DEBUG_REVISION+');
1458 END IF;
1459
1460 IF p_operation_id is not null or p_operation_id<>fnd_api.g_miss_num
1461 THEN
1462 OPEN GetOperationDet(p_operation_id);
1463 FETCH GetOperationDet INTO l_oper_rec;
1464
1465 IF GetOperationDet%NOTFOUND
1466 THEN
1467 l_check_flag:='N';
1468 ELSE
1469 l_check_flag:='Y';
1470 IF trunc(l_oper_rec.start_date_active) >trunc(sysdate)
1471 THEN
1472 l_fr_date:=l_oper_rec.start_date_active;
1473 l_to_date:=l_oper_rec.Start_date_active;
1474 ELSE
1475 l_fr_date:=sysdate;
1476 l_to_date:=sysdate;
1477 END IF;
1478 END IF;
1479
1480 CLOSE GetOperationDet;
1481 IF l_check_flag='Y' and p_appr_status='REJECTED'
1482 THEN
1483 UPDATE AHL_OPERATIONS_B
1484 SET REVISION_STATUS_CODE=l_status,
1485 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1486 WHERE OPERATION_ID=P_OPERATION_ID;
1487 UPDATE AHL_OPERATIONS_TL
1488 SET APPROVER_NOTE=p_approver_note
1489 WHERE OPERATION_ID=P_OPERATION_ID;
1490 ELSIF l_check_flag='Y' and l_curr_status='TERMINATION_PENDING'
1491 and p_appr_status='APPROVED'
1492 THEN
1493 UPDATE AHL_OPERATIONS_B
1494 SET REVISION_STATUS_CODE='TERMINATED',
1495 -- START_DATE_ACTIVE=l_fr_date,
1496 END_DATE_ACTIVE=l_to_date,
1497 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1498 WHERE OPERATION_ID=P_OPERATION_ID;
1499
1500 -- Bypass notifications in Preventive Application mode.
1501 IF (G_PM_INSTALL <> 'Y') THEN
1502 ahl_utility_pvt.get_wf_process_name(
1503 p_object =>'RM_NTF',
1504 x_active =>l_active,
1505 x_process_name =>l_process_name ,
1506 x_item_type =>l_item_type,
1507 x_return_status=>l_return_status,
1508 x_msg_count =>l_msg_count,
1509 x_msg_data =>l_msg_data);
1510
1511 IF (l_active = 'Y') THEN
1512 NOTIFY_TERMINATION
1513 (
1514 x_return_status =>l_return_status,
1515 x_msg_count =>l_msg_count,
1516 x_msg_data =>l_msg_data,
1517 p_object_type =>G_OBJECT_TYPE,
1518 p_prim_object_type =>'OPER',
1519 p_activity_id =>p_operation_id
1520 );
1521 END IF;
1522 END IF;
1523
1524 ELSIF l_check_flag='Y' and l_oper_rec.revision_number=1
1525 and l_curr_status='APPROVAL_PENDING' and p_appr_status='APPROVED'
1526 THEN
1527 UPDATE AHL_OPERATIONS_B
1528 SET REVISION_STATUS_CODE=l_status,
1529 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
1530 WHERE OPERATION_ID=P_OPERATION_ID;
1531
1532 l_check_flag:='Y';
1533
1534 l_check_flag2:='Y';
1535
1536 ELSIF l_check_flag='Y' and
1537 l_oper_rec.revision_number>1 and
1538 p_appr_status='APPROVED'
1539 THEN
1540 OPEN GetPrevOperDet(l_oper_rec.revision_number,l_oper_rec.CONCATENATED_SEGMENTS);
1541 FETCH GetPrevOperDet INTO l_prev_oper_rec;
1542 IF GetPrevOperDet%NOTFOUND
1543 THEN
1544 l_check_flag2:='N';
1545 ELSE
1546 l_check_flag2:='Y';
1547 END IF;
1548
1549 CLOSE GetPrevOperDet;
1550
1551 IF l_check_flag2='Y'
1552 THEN
1553 UPDATE AHL_OPERATIONS_B
1554 SET REVISION_STATUS_CODE=l_status,
1555 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
1556 START_DATE_ACTIVE=l_fr_date
1557 WHERE OPERATION_ID=P_OPERATION_ID;
1558 IF l_check_flag3='Y'
1559 THEN
1560 UPDATE AHL_OPERATIONS_B
1561 SET REVISION_STATUS_CODE=l_status,
1562 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
1563 END_DATE_ACTIVE=l_to_date
1564 WHERE OPERATION_ID=l_prev_oper_rec.OPERATION_ID;
1565
1566 -- Bypass notifications in preventive Maintenance mode.
1567 IF (G_PM_INSTALL <> 'Y') THEN
1568 ahl_utility_pvt.get_wf_process_name(
1569 p_object =>'RM_NTF',
1570 x_active =>l_active,
1571 x_process_name =>l_process_name ,
1572 x_item_type =>l_item_type,
1573 x_return_status=>l_return_status,
1574 x_msg_count =>l_msg_count,
1575 x_msg_data =>l_msg_data);
1576
1577 IF (l_active = 'Y') THEN
1578 NOTIFY_TERMINATION
1579 (
1580 x_return_status =>l_return_status,
1581 x_msg_count =>l_msg_count,
1582 x_msg_data =>l_msg_data,
1583 p_object_type =>G_OBJECT_TYPE,
1584 p_prim_object_type =>'OPER',
1585 p_activity_id =>p_operation_id
1586 );
1587 END IF;
1588 END IF;
1589 END IF;
1590 END IF;
1591 END IF;
1592 ELSE
1593 IF G_DEBUG='Y' THEN
1594 AHL_DEBUG_PUB.debug( 'INVALID P_MR_HEADER_ID','+COMPLETE_OPERATION_REVISION+');
1595 ROLLBACK;
1596 END IF;
1597 END IF;
1598
1599 IF l_msg_count > 0
1600 THEN
1601 X_msg_count := l_msg_count;
1602 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF;
1605
1606 IF G_DEBUG='Y' THEN
1607 AHL_DEBUG_PUB.debug( 'Before commit Complete_mr_revision Status----->'||p_appr_status,'+DEBUG_RELATIONS+');
1608 END IF;
1609
1610 IF FND_API.TO_BOOLEAN(p_commit) THEN
1611 COMMIT;
1612 END IF;
1613 EXCEPTION
1614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1615 ROLLBACK TO COMPLETE_OPER_REVISION;
1616 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1617 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1618 p_count => x_msg_count,
1619 p_data => x_msg_data);
1620
1621 WHEN FND_API.G_EXC_ERROR THEN
1622 ROLLBACK TO COMPLETE_OPER_REVISION;
1623 X_return_status := FND_API.G_RET_STS_ERROR;
1624 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1625 p_count => x_msg_count,
1626 p_data => X_msg_data);
1627 WHEN OTHERS THEN
1628 ROLLBACK TO COMPLETE_OPER_REVISION;
1629 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1631 THEN
1632 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1633 p_procedure_name =>'COMPLETE_OPER_REVISION',
1634 p_error_text =>SUBSTR(SQLERRM,1,240));
1635 END IF;
1636 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1637 p_count => x_msg_count,
1638 p_data => X_msg_data);
1639
1640 END;
1641
1642
1643
1644 END AHL_RM_APPROVAL_PVT;