[Home] [Help]
PACKAGE BODY: APPS.AHL_OPERATIONS_APPROVAL_PVT
Source
1 PACKAGE BODY AHL_OPERATIONS_APPROVAL_PVT AS
2 /* $Header: AHLVOWKB.pls 120.1 2005/09/29 04:26:44 tamdas noship $ */
3
4 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
5
6 PROCEDURE GET_FULLNAME
7 (
8 p_username IN VARCHAR2,
9 x_fullname OUT NOCOPY VARCHAR2
10 )
11 AS
12 CURSOR CurGetName IS
13 SELECT full_name FROM AHL_JTF_RS_EMP_V WHERE USER_NAME=p_username;
14 begin
15 OPEN CurGetName;
16 FETCH CurGetName INTO x_fullname;
17
18 IF CurGetName%notfound
19 THEN
20 x_fullname:=p_username;
21 END IF;
22 CLOSE CurGetName;
23 END;
24
25 PROCEDURE Set_Activity_Details(
26 itemtype IN VARCHAR2
27 ,itemkey IN VARCHAR2
28 ,actid IN NUMBER
29 ,funcmode IN VARCHAR2
30 ,resultout OUT NOCOPY VARCHAR2)
31 IS
32
33 l_object_id NUMBER;
34 l_object VARCHAR2(30) := 'OPER';
35 l_approval_type VARCHAR2(30) := 'CONCEPT';
36 l_object_details ahl_generic_aprv_pvt.ObjRecTyp;
37 l_approval_rule_id NUMBER;
38 l_approver_seq NUMBER;
39 l_return_status VARCHAR2(1);
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(4000);
42 l_subject VARCHAR2(500);
43 l_subject_rej VARCHAR2(500);
44 l_subject_apr VARCHAR2(500);
45 l_subject_aprvd VARCHAR2(500);
46 l_subject_req VARCHAR2(500);
47 l_subject_err VARCHAR2(500);
48 l_error_msg VARCHAR2(2000);
49 l_OPERATION_ID NUMBER:=0;
50 l_check_flag VARCHAR2(1):='N';
51
52 cursor GetOperationDet(c_OPERATION_ID number)
53 is
54 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
55 from ahl_OPERATIONS_b_KFV
56 where OPERATION_ID=c_OPERATION_ID;
57
58 l_operation_rec GetOperationDet%rowtype;
59
60
61 BEGIN
62 IF G_DEBUG='Y' THEN
63 AHL_DEBUG_PUB.enable_debug;
64 AHL_DEBUG_PUB.debug( 'Set_Activity_Details Route','+DebugWfOper+');
65 END IF;
66 fnd_msg_pub.initialize;
67
68 l_return_status :=fnd_api.g_ret_sts_success;
69
70 l_object_id := wf_engine.getitemattrnumber(
71 itemtype => itemtype
72 ,itemkey => itemkey
73 ,aname => 'OBJECT_ID'
74 );
75
76 l_object_details.operating_unit_id:=NULL;
77
78 l_object_details.priority:=NULL;
79
80 IF (funcmode = 'RUN') THEN
81 OPEN GetOperationDet(l_object_id);
82 FETCH GetOperationDet into l_operation_rec;
83
84 IF GetOperationDet%FOUND
85 THEN
86 l_check_flag:='Y';
87
88 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
89 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
90 l_subject := fnd_message.get;
91
92 fnd_message.set_name('AHL','AHL_RM_OPERNTF_FORWARD_SUBJECT');
93 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
94 l_subject_apr:= fnd_message.get;
95
96 fnd_message.set_name('AHL', 'AHL_RM_OPER_AFT_APPR_REJTS_SUB');
97 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
98 l_subject_rej:= fnd_message.get;
99
100 fnd_message.set_name('AHL', 'AHL_RM_OPER_AFT_APPR_APRVS_SUB');
101 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
102 l_subject_aprvd:= fnd_message.get;
103
104 fnd_message.set_name('AHL', 'AHL_RM_OPER_ERROR_SUB');
105 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
106 l_subject_err:= fnd_message.get;
107 ELSE
108 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
109 l_subject_err:= fnd_message.get;
110 l_subject_err:= l_subject_err||to_char(l_object_id);
111 END IF;
112 CLOSE GetOperationDet;
113
114 wf_engine.setitemattrtext(
115 itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'FORWARD_SUBJECT',
118 avalue => l_subject_apr);
119
120 wf_engine.setitemattrtext(
121 itemtype => itemtype,
122 itemkey => itemkey,
123 aname => 'APPROVAL_SUBJECT',
124 avalue => l_subject);
125
126 wf_engine.setitemattrtext(
127 itemtype => itemtype,
128 itemkey => itemkey,
129 aname => 'REJECT_SUBJECT',
130 avalue => l_subject_rej);
131
132 wf_engine.setitemattrtext(
133 itemtype => itemtype,
134 itemkey => itemkey,
135 aname => 'APPROVED_SUBJECT',
136 avalue => l_subject);
137
138 wf_engine.setitemattrtext(
139 itemtype => itemtype,
140 itemkey => itemkey,
141 aname => 'FINAL_SUBJECT',
142 avalue => l_subject);
143
144 wf_engine.setitemattrtext(
145 itemtype => itemtype,
146 itemkey => itemkey,
147 aname => 'REMIND_SUBJECT',
148 avalue => l_subject);
149
150 wf_engine.setitemattrtext(
151 itemtype => itemtype,
152 itemkey => itemkey,
153 aname => 'ERROR_SUBJECT',
154 avalue => l_subject_err);
155
156 -----------------------------------------------------------------------------------
157 -- Get Approval Rule and First Approver Sequence
158 -----------------------------------------------------------------------------------
159
160 ahl_generic_aprv_pvt.get_approval_details(
161 p_object => l_object,
162 p_approval_type => l_approval_type,
163 p_object_details => l_object_details,
164 x_approval_rule_id => l_approval_rule_id,
165 x_approver_seq => l_approver_seq,
166 x_return_status => l_return_status
167 );
168
169 IF l_return_status = fnd_api.g_ret_sts_success THEN
170
171 wf_engine.setitemattrnumber(
172 itemtype => itemtype,
173 itemkey => itemkey,
174 aname => 'RULE_ID',
175 avalue => l_approval_rule_id
176 );
177
178 wf_engine.setitemattrnumber(
179 itemtype => itemtype,
180 itemkey => itemkey,
181 aname => 'APPROVER_SEQ',
182 avalue => l_approver_seq
183 );
184
185 resultout := 'COMPLETE:SUCCESS';
186
187 RETURN;
188
189 ELSE
190 RAISE fnd_api.G_EXC_ERROR;
191 END IF;
192 END IF;
193
194 IF (funcmode = 'CANCEL') THEN
195 resultout := 'COMPLETE:';
196 RETURN;
197 END IF;
198
199
200 IF (funcmode = 'TIMEOUT') THEN
201 resultout := 'COMPLETE:';
202 RETURN;
203 END IF;
204
205 EXCEPTION
206 WHEN fnd_api.G_EXC_ERROR THEN
207
208 FND_MSG_PUB.Count_And_Get (
209 p_encoded => FND_API.G_FALSE,
210 p_count => l_msg_count,
211 p_data => l_msg_data
212 );
213
214 ahl_generic_aprv_pvt.Handle_Error
215 (p_itemtype => itemtype ,
216 p_itemkey => itemkey ,
217 p_msg_count => l_msg_count,
218 p_msg_data => l_msg_data ,
219 p_attr_name => 'ERROR_MSG',
220 x_error_msg => l_error_msg
221 ) ;
222 wf_core.context('AHL_ROUTE_APROVAL_PVT','Set_Activity_Details',
223 itemtype,itemkey,actid,funcmode,l_error_msg);
224
225 resultout := 'COMPLETE:ERROR';
226
227 WHEN OTHERS THEN
228 wf_core.context(
229 'AHL_OPERATIONS_APPROVAL_PVT'
230 ,'Set_Activity_Details'
231 ,itemtype
232 ,itemkey
233 ,actid
234 ,'Unexpected Error!'
235 );
236 RAISE;
237
238 END Set_Activity_Details;
239
240
241
242 PROCEDURE Ntf_Forward_FYI(
243 document_id IN VARCHAR2
244 ,display_type IN VARCHAR2
245 ,document IN OUT NOCOPY VARCHAR2
246 ,document_type IN OUT NOCOPY VARCHAR2)
247 IS
248
249 l_hyphen_pos1 NUMBER;
250 l_object VARCHAR2(30);
251 l_item_type VARCHAR2(30);
252 l_item_key VARCHAR2(30);
253 l_approver VARCHAR2(30);
254 l_body VARCHAR2(3500);
255 l_object_id NUMBER;
256 l_msg_count NUMBER;
257 l_msg_data VARCHAR2(4000);
258 l_error_msg VARCHAR2(2000);
259 l_apr_fulname VARCHAR2(100);
260 l_req_fulname VARCHAR2(100);
261
262 cursor GetOperationDet(c_OPERATION_ID number)
263 is
264 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
265 from ahl_OPERATIONS_b_KFV
266 where OPERATION_ID=c_OPERATION_ID;
267
268 l_operation_rec GetOperationDet%rowtype;
269 l_fullname VARCHAR2(100);
270
271 BEGIN
272 IF G_DEBUG='Y' THEN
273 AHL_DEBUG_PUB.enable_debug;
274 AHL_DEBUG_PUB.debug( 'Ntf_Forward_FYI','+DebugWfOper+');
275 END IF;
276
277 document_type := 'text/plain';
278
279 l_hyphen_pos1 := INSTR(document_id, ':');
280 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
281 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
282
283 l_object := wf_engine.getitemattrtext(
284 itemtype => l_item_type,
285 itemkey => l_item_key,
286 aname => 'OBJECT_TYPE');
287
288 l_object_id := wf_engine.getitemattrNumber(
289 itemtype => l_item_type,
290 itemkey => l_item_key,
291 aname => 'OBJECT_ID');
292
293 l_approver := wf_engine.getitemattrtext(
294 itemtype => l_item_type,
295 itemkey => l_item_key,
296 aname => 'APPROVER');
297
298
299 GET_FULLNAME
300 (
301 p_username =>l_approver,
302 x_fullname =>l_fullname
303 );
304
305
306 OPEN GetOperationDet(l_object_id);
307 FETCH GetOperationDet into l_operation_rec;
308
309 IF GetOperationDet%NOTFOUND
310 THEN
311 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
312 l_body := fnd_message.get;
313 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
314 ELSE
315 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_CONTENT');
316 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
317 fnd_message.set_token('NAME',l_fullname, false);
318 l_body := fnd_message.get;
319 END IF;
320 CLOSE GetOperationDet;
321
322 /*--------------------------------------------------------------------------
323 -- Query approval object table for any detail information of this object
324 -- that will be used to replace tokens defined in FND Messages.
325 -- Here to simplify, we are using hard-coded messages.
326 ----------------------------------------------------------------------------*/
327
328 -- if nvl(l_fname,l_approver);
329
330 l_body :=l_body||'.'||'Your request has been forwarded to ' ||l_fullname||' for approval' ;
331
332 document := document || l_body;
333 RETURN;
334
335 EXCEPTION
336 WHEN FND_API.G_EXC_ERROR THEN
337 FND_MSG_PUB.Count_And_Get (
338 p_encoded => FND_API.G_FALSE,
339 p_count => l_msg_count,
340 p_data => l_msg_data
341 );
342 ahl_generic_aprv_pvt.Handle_Error
343 (p_itemtype => l_item_type ,
344 p_itemkey => l_item_key ,
345 p_msg_count => l_msg_count, -- Number of error Messages
346 p_msg_data => l_msg_data ,
347 p_attr_name => 'ERROR_MSG',
348 x_error_msg => l_error_msg
349 ) ;
350 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','ntf_forward_fyi',
351 l_item_type,l_item_key,l_error_msg);
352 RAISE;
353 WHEN OTHERS THEN
354 wf_core.context( 'AHLGAPP'
355 , 'Ntf_Forward_FYI'
356 , l_item_type
357 , l_item_key
358 );
359 RAISE;
360 END Ntf_Forward_FYI;
361
362 PROCEDURE Ntf_Approved_FYI(
363 document_id IN VARCHAR2
364 ,display_type IN VARCHAR2
365 ,document IN OUT NOCOPY VARCHAR2
366 ,document_type IN OUT NOCOPY VARCHAR2)
367 IS
368
369 l_hyphen_pos1 NUMBER;
370 l_object VARCHAR2(30);
371 l_item_type VARCHAR2(30);
372 l_item_key VARCHAR2(30);
373 l_approver VARCHAR2(30);
374 l_body VARCHAR2(3500);
375 l_object_id NUMBER;
376
377 l_msg_count NUMBER;
378 l_msg_data VARCHAR2(4000);
379 l_error_msg VARCHAR2(2000);
380
381 cursor GetOperationDet(c_OPERATION_ID number)
382 is
383 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
384 from ahl_OPERATIONS_b_KFV
385 where OPERATION_ID=c_OPERATION_ID;
386
387 l_operation_rec GetOperationDet%rowtype;
388 l_fullname VARCHAR2(100);
389
390 BEGIN
391
392 IF G_DEBUG='Y' THEN
393 AHL_DEBUG_PUB.enable_debug;
394 AHL_DEBUG_PUB.debug( 'Ntf_Approved_FYI ROUTE ','+DebugWfOper+');
395 END IF;
396
397
398 document_type := 'text/plain';
399
400 l_hyphen_pos1 := INSTR(document_id, ':');
401 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
402 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
403
404 l_object := wf_engine.getitemattrtext(
405 itemtype => l_item_type
406 ,itemkey => l_item_key
407 ,aname => 'OBJECT_TYPE'
408 );
409
413 ,aname => 'OBJECT_ID'
410 l_object_id := wf_engine.getitemattrNumber(
411 itemtype => l_item_type
412 ,itemkey => l_item_key
414 );
415
416 l_approver := wf_engine.getitemattrtext(
417 itemtype => l_item_type
418 ,itemkey => l_item_key
419 ,aname => 'APPROVER'
420 );
421
422 /*--------------------------------------------------------------------------
423 -- Query approval object table for any detail information of this object
424 -- that will be used to replace tokens defined in FND Messages.
425 -- Here to simplify, we are using hard-coded messages.
426 ----------------------------------------------------------------------------*/
427 OPEN GetOperationDet(l_object_id);
428 FETCH GetOperationDet into l_operation_rec;
429
430 IF GetOperationDet%NOTFOUND
431 THEN
432 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
433 l_body := fnd_message.get;
434 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
435 ELSE
436 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
437 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
438 l_body := fnd_message.get;
439 END IF;
440 CLOSE GetOperationDet;
441
442 GET_FULLNAME
443 (
444 p_username =>l_approver,
445 x_fullname =>l_fullname
446 );
447
448
449 l_body :='Your request has been approved by ' ||l_fullname ;
450
451 document := document || l_body;
452
453 RETURN;
454
455 EXCEPTION
456 WHEN FND_API.G_EXC_ERROR THEN
457 FND_MSG_PUB.Count_And_Get (
458 p_encoded => FND_API.G_FALSE,
459 p_count => l_msg_count,
460 p_data => l_msg_data
461 );
462 ahl_generic_aprv_pvt.Handle_Error
463 (p_itemtype => l_item_type ,
464 p_itemkey => l_item_key ,
465 p_msg_count => l_msg_count, -- Number of error Messages
466 p_msg_data => l_msg_data ,
467 p_attr_name => 'ERROR_MSG',
468 x_error_msg => l_error_msg
469 ) ;
470 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','Ntf_Approved_FYI',
471 l_item_type,l_item_key,l_error_msg);
472 RAISE;
473 WHEN OTHERS THEN
474 wf_core.context( 'AHLGAPP'
475 , 'Ntf_Approved_FYI'
476 , l_item_type
477 , l_item_key
478 );
479 RAISE;
480 END Ntf_Approved_FYI;
481
482 PROCEDURE Ntf_Final_Approval_FYI(
483 document_id IN VARCHAR2
484 ,display_type IN VARCHAR2
485 ,document IN OUT NOCOPY VARCHAR2
486 ,document_type IN OUT NOCOPY VARCHAR2)
487 IS
488
489 l_hyphen_pos1 NUMBER;
490 l_object VARCHAR2(30);
491 l_item_type VARCHAR2(30);
492 l_item_key VARCHAR2(30);
493 l_body VARCHAR2(3500);
494 l_object_id NUMBER;
495 l_msg_count NUMBER;
496 l_msg_data VARCHAR2(4000);
497 l_error_msg VARCHAR2(2000);
498
499 cursor GetOperationDet(c_OPERATION_ID number)
500 is
501 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
502 from ahl_OPERATIONS_b_KFV
503 where OPERATION_ID=c_OPERATION_ID;
504
505 l_operation_rec GetOperationDet%rowtype;
506 BEGIN
507
508 IF G_DEBUG='Y' THEN
509 AHL_DEBUG_PUB.enable_debug;
510 AHL_DEBUG_PUB.debug( 'Start NtfyFinalApproval','+DebugWfOper+');
511 END IF;
512
513 document_type := 'text/plain';
514
515 l_hyphen_pos1 := INSTR(document_id, ':');
516 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
517 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
518
519 l_object := wf_engine.getitemattrtext(
520 itemtype => l_item_type
521 ,itemkey => l_item_key
522 ,aname => 'OBJECT_TYPE'
523 );
524
525 l_object_id := wf_engine.getitemattrNumber(
526 itemtype => l_item_type
527 ,itemkey => l_item_key
528 ,aname => 'OBJECT_ID'
529 );
530
531
532 /*--------------------------------------------------------------------------
533 -- Query approval object table for any detail information of this object
534 -- that will be used to replace tokens defined in FND Messages.
535 -- Here to simplify, we are using hard-coded messages.
536 ----------------------------------------------------------------------------*/
537
538 OPEN GetOperationDet(l_object_id);
539 FETCH GetOperationDet into l_operation_rec;
540
541 IF GetOperationDet%NOTFOUND
542 THEN
543 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
544 l_body := fnd_message.get;
545 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
549 l_body := fnd_message.get;
546 ELSE
547 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
548 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
550 END IF;
551 CLOSE GetOperationDet;
552
553
554 l_body :=l_body||'.'|| 'Your request has been approved by all approvers.';
555
556 document := document || l_body;
557
558 RETURN;
559
560 EXCEPTION
561 WHEN FND_API.G_EXC_ERROR THEN
562 FND_MSG_PUB.Count_And_Get (
563 p_encoded => FND_API.G_FALSE,
564 p_count => l_msg_count,
565 p_data => l_msg_data
566 );
567 ahl_generic_aprv_pvt.Handle_Error
568 (p_itemtype => l_item_type ,
569 p_itemkey => l_item_key ,
570 p_msg_count => l_msg_count,
571 p_msg_data => l_msg_data ,
572 p_attr_name => 'ERROR_MSG',
573 x_error_msg => l_error_msg
574 ) ;
575 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','Ntf_Final_Approval_FYI',
576 l_item_type,l_item_key,l_error_msg);
577 RAISE;
578 WHEN OTHERS THEN
579 wf_core.context( 'AHLGAPP'
580 , 'Ntf_Final_Approval_FYI'
581 , l_item_type
582 , l_item_key
583 );
584 RAISE;
585 END Ntf_Final_Approval_FYI;
586
587
588 PROCEDURE Ntf_Rejected_FYI(
589 document_id IN VARCHAR2
590 ,display_type IN VARCHAR2
591 ,document IN OUT NOCOPY VARCHAR2
592 ,document_type IN OUT NOCOPY VARCHAR2)
593 IS
594
595 l_hyphen_pos1 NUMBER;
596 l_object VARCHAR2(30);
597 l_item_type VARCHAR2(30);
598 l_item_key VARCHAR2(30);
599 l_approver VARCHAR2(30);
600 l_body VARCHAR2(3500);
601 l_object_id NUMBER;
602 l_msg_count NUMBER;
603 l_msg_data VARCHAR2(4000);
604 l_error_msg VARCHAR2(2000);
605 l_fullname VARCHAR2(100);
606 cursor GetOperationDet(c_OPERATION_ID number)
607 is
608 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
609 from ahl_OPERATIONS_b_KFV
610 where OPERATION_ID=c_OPERATION_ID;
611
612 l_operation_rec GetOperationDet%rowtype;
613 BEGIN
614
615 IF G_DEBUG='Y' THEN
616 AHL_DEBUG_PUB.enable_debug;
617 AHL_DEBUG_PUB.debug( 'NtfRejectedFyi','+DebugWfOper+');
618 END IF;
619
620 document_type := 'text/plain';
621
622 l_hyphen_pos1 := INSTR(document_id, ':');
623 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
624 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
625
626 l_object := wf_engine.getitemattrtext(
627 itemtype => l_item_type
628 ,itemkey => l_item_key
629 ,aname => 'OBJECT_TYPE'
630 );
631
632 l_object_id := wf_engine.getitemattrNumber(
633 itemtype => l_item_type
634 ,itemkey => l_item_key
635 ,aname => 'OBJECT_ID'
636 );
637
638 l_approver := wf_engine.getitemattrtext(
639 itemtype => l_item_type
640 ,itemkey => l_item_key
641 ,aname => 'APPROVER'
642 );
643
644 /*--------------------------------------------------------------------------
645 -- Query approval object table for any detail information of this object
646 -- that will be used to replace tokens defined in FND Messages.
647 -- Here to simplify, we are using hard-coded messages.
648 ----------------------------------------------------------------------------*/
649 OPEN GetOperationDet(l_object_id);
650 FETCH GetOperationDet into l_operation_rec;
651
652 IF GetOperationDet%NOTFOUND
653 THEN
654 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
655 l_body := fnd_message.get;
656 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
657 ELSE
658 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
659 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
660 l_body := fnd_message.get;
661 END IF;
662 CLOSE GetOperationDet;
663
664 GET_FULLNAME
665 (
666 p_username =>l_approver,
667 x_fullname =>l_fullname
668 );
669
670
671 l_body := 'Your request has been rejected by ' ||l_fullname ;
672
673 document := document || l_body;
674
675 RETURN;
676
677 EXCEPTION
678 WHEN FND_API.G_EXC_ERROR THEN
679 FND_MSG_PUB.Count_And_Get (
680 p_encoded => FND_API.G_FALSE,
681 p_count => l_msg_count,
682 p_data => l_msg_data
683 );
684 ahl_generic_aprv_pvt.Handle_Error
685 (p_itemtype => l_item_type ,
686 p_itemkey => l_item_key ,
690 x_error_msg => l_error_msg
687 p_msg_count => l_msg_count, -- Number of error Messages
688 p_msg_data => l_msg_data ,
689 p_attr_name => 'ERROR_MSG',
691 ) ;
692 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','Ntf_Rejected_FYI',
693 l_item_type,l_item_key,l_error_msg);
694 RAISE;
695
696 WHEN OTHERS THEN
697 wf_core.context( 'AHLGAPP'
698 , 'Ntf_Rejected_FYI'
699 , l_item_type
700 , l_item_key
701 );
702 RAISE;
703 END Ntf_Rejected_FYI;
704
705
706 PROCEDURE Ntf_Approval(
707 document_id IN VARCHAR2
708 ,display_type IN VARCHAR2
709 ,document IN OUT NOCOPY VARCHAR2
710 ,document_type IN OUT NOCOPY VARCHAR2)
711 IS
712
713 l_hyphen_pos1 NUMBER;
714 l_object VARCHAR2(30);
715 l_item_type VARCHAR2(30);
716 l_item_key VARCHAR2(30);
717 l_requester VARCHAR2(30);
718 l_requester_note VARCHAR2(4000);
719 l_body VARCHAR2(5000);
720 l_object_id NUMBER;
721 l_msg_count NUMBER;
722 l_msg_data VARCHAR2(4000);
723 l_error_msg VARCHAR2(2000);
724 cursor GetOperationDet(c_OPERATION_ID number)
725 is
726 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
727 from ahl_OPERATIONS_b_KFV
728 where OPERATION_ID=c_OPERATION_ID;
729
730 l_operation_rec GetOperationDet%rowtype;
731 l_fullname VARCHAR2(100);
732 BEGIN
733
734 IF G_DEBUG='Y' THEN
735 AHL_DEBUG_PUB.enable_debug;
736 AHL_DEBUG_PUB.debug( 'Start Notify Approval','+DebugWfOper+');
737 END IF;
738
739 document_type := 'text/plain';
740
741 l_hyphen_pos1 := INSTR(document_id, ':');
742 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
743 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
744
745 l_object := wf_engine.getitemattrtext(
746 itemtype => l_item_type
747 ,itemkey => l_item_key
748 ,aname => 'OBJECT_TYPE'
749 );
750
751 l_object_id := wf_engine.getitemattrNumber(
752 itemtype => l_item_type
753 ,itemkey => l_item_key
754 ,aname => 'OBJECT_ID'
755 );
756
757 l_requester := wf_engine.getitemattrtext(
758 itemtype => l_item_type
759 ,itemkey => l_item_key
760 ,aname => 'REQUESTER'
761 );
762
763 l_requester_note := wf_engine.getitemattrtext(
764 itemtype => l_item_type
765 ,itemkey => l_item_key
766 ,aname => 'REQUESTER_NOTE'
767 );
768
769 commit;
770 /*--------------------------------------------------------------------------
771 -- Query approval object table for any detail information of this object
772 -- that will be used to replace tokens defined in FND Messages.
773 -- Here to simplify, we are using hard-coded messages.
774 ----------------------------------------------------------------------------*/
775
776 OPEN GetOperationDet(l_object_id);
777 FETCH GetOperationDet into l_operation_rec;
778
779 IF GetOperationDet%FOUND
780 THEN
781 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
782 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
783 l_body := fnd_message.get;
784 ELSE
785 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
786 l_body := fnd_message.get;
787 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
788 END IF;
789 CLOSE GetOperationDet;
790
791 GET_FULLNAME
792 (
793 p_username =>l_requester,
794 x_fullname =>l_fullname
795 );
796
797
798 l_body := 'You just received a request from '||l_requester;
799 l_body := l_requester_note;
800
801 document := document || l_body;
802
803 RETURN;
804
805 EXCEPTION
806 WHEN FND_API.G_EXC_ERROR THEN
807 FND_MSG_PUB.Count_And_Get (
808 p_encoded => FND_API.G_FALSE,
809 p_count => l_msg_count,
810 p_data => l_msg_data
811 );
812 ahl_generic_aprv_pvt.Handle_Error
813 (p_itemtype => l_item_type ,
814 p_itemkey => l_item_key ,
815 p_msg_count => l_msg_count, -- Number of error Messages
816 p_msg_data => l_msg_data ,
817 p_attr_name => 'ERROR_MSG',
818 x_error_msg => l_error_msg
819 ) ;
820 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','Ntf_Approval',
821 l_item_type,l_item_key,l_error_msg);
822 RAISE;
823 WHEN OTHERS THEN
824 wf_core.context( 'AHLGAPP'
828 );
825 , 'Ntf_Approval'
826 , l_item_type
827 , l_item_key
829 RAISE;
830 END Ntf_Approval;
831
832
833 PROCEDURE Ntf_Approval_Reminder(
834 document_id IN VARCHAR2
835 ,display_type IN VARCHAR2
836 ,document IN OUT NOCOPY VARCHAR2
837 ,document_type IN OUT NOCOPY VARCHAR2)
838 IS
839 l_hyphen_pos1 NUMBER;
840 l_object VARCHAR2(30);
841 l_item_type VARCHAR2(30);
842 l_item_key VARCHAR2(30);
843 l_requester VARCHAR2(30);
844 l_requester_note VARCHAR2(4000);
845 l_body VARCHAR2(5000);
846 l_object_id NUMBER;
847 l_msg_count NUMBER;
848 l_msg_data VARCHAR2(4000);
849 l_error_msg VARCHAR2(2000);
850 l_fullname VARCHAR2(100);
851
852 cursor GetOperationDet(c_OPERATION_ID number)
853 is
854 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
855 from ahl_OPERATIONS_b_KFV
856 where OPERATION_ID=c_OPERATION_ID;
857 l_operation_rec GetOperationDet%rowtype;
858 BEGIN
859
860 IF G_DEBUG='Y' THEN
861 AHL_DEBUG_PUB.enable_debug;
862 AHL_DEBUG_PUB.debug( 'Start NtfAprRemainder','+DebugWfOper+');
863 END IF;
864
865 document_type := 'text/plain';
866
867 l_hyphen_pos1 := INSTR(document_id, ':');
868 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
869 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
870
871 l_object := wf_engine.getitemattrtext(
872 itemtype => l_item_type
873 ,itemkey => l_item_key
874 ,aname => 'OBJECT_TYPE'
875 );
876
877 l_object_id := wf_engine.getitemattrNumber(
878 itemtype => l_item_type
879 ,itemkey => l_item_key
880 ,aname => 'OBJECT_ID'
881 );
882
883 l_requester := wf_engine.getitemattrtext(
884 itemtype => l_item_type
885 ,itemkey => l_item_key
886 ,aname => 'REQUESTER'
887 );
888
889 l_requester_note := wf_engine.getitemattrtext(
890 itemtype => l_item_type
891 ,itemkey => l_item_key
892 ,aname => 'REQUESTER_NOTE'
893 );
894
895
896 /*--------------------------------------------------------------------------
897 -- Query approval object table for any detail information of this object
898 -- that will be used to replace tokens defined in FND Messages.
899 -- Here to simplify, we are using hard-coded messages.
900 ----------------------------------------------------------------------------*/
901 OPEN GetOperationDet(l_object_id);
902 FETCH GetOperationDet into l_operation_rec;
903
904 IF GetOperationDet%NOTFOUND
905 THEN
906 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
907 l_body := fnd_message.get;
908 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
909 ELSE
910 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
911 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
912 l_body := fnd_message.get;
913 END IF;
914 CLOSE GetOperationDet;
915
916 GET_FULLNAME
917 (
918 p_username =>l_requester,
919 x_fullname =>l_fullname
920 );
921
922
923 l_body :=l_body||'.'|| 'Reminder: You just received a request from '||l_fullname;
924 l_body := l_body ||'. The note from him/her is as following: '||l_requester_note;
925
926 document := document || l_body;
927
928 RETURN;
929
930 EXCEPTION
931 WHEN FND_API.G_EXC_ERROR THEN
932 FND_MSG_PUB.Count_And_Get (
933 p_encoded => FND_API.G_FALSE,
934 p_count => l_msg_count,
935 p_data => l_msg_data
936 );
937 ahl_generic_aprv_pvt.Handle_Error
938 (p_itemtype => l_item_type ,
939 p_itemkey => l_item_key ,
940 p_msg_count => l_msg_count, -- Number of error Messages
941 p_msg_data => l_msg_data ,
942 p_attr_name => 'ERROR_MSG',
943 x_error_msg => l_error_msg
944 ) ;
945 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','Ntf_Approval_Reminder',
946 l_item_type,l_item_key,l_error_msg);
947 RAISE;
948 WHEN OTHERS THEN
949 wf_core.context( 'AHLGAPP'
950 , 'Ntf_Approval_Reminder'
951 , l_item_type
952 , l_item_key
953 );
954 RAISE;
955 END Ntf_Approval_Reminder;
956
957
958
959
960 PROCEDURE Ntf_Error_Act(
961 document_id IN VARCHAR2
962 ,display_type IN VARCHAR2
963 ,document IN OUT NOCOPY VARCHAR2
964 ,document_type IN OUT NOCOPY VARCHAR2)
965 IS
966
967 l_hyphen_pos1 NUMBER;
971 l_body VARCHAR2(3500);
968 l_object VARCHAR2(30);
969 l_item_type VARCHAR2(30);
970 l_item_key VARCHAR2(30);
972 l_object_id NUMBER;
973 l_error_msg VARCHAR2(4000);
974 l_msg_count NUMBER;
975 l_msg_data VARCHAR2(4000);
976
977 cursor GetOperationDet(c_OPERATION_ID number)
978 is
979 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
980 from ahl_OPERATIONS_b_KFV
981 where OPERATION_ID=c_OPERATION_ID;
982
983 l_operation_rec GetOperationDet%rowtype;
984 BEGIN
985 IF G_DEBUG='Y' THEN
986 AHL_DEBUG_PUB.enable_debug;
987 AHL_DEBUG_PUB.debug( 'Start NtfErrorAct','+DebugWfOper+');
988 END IF;
989
990
991 document_type := 'text/plain';
992
993 -- parse document_id for the ':' dividing item type name from item key value
994 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
995 -- release 2.5 version of this demo
996
997 l_hyphen_pos1 := INSTR(document_id, ':');
998 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
999 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1000
1001 l_object := wf_engine.getitemattrtext(
1002 itemtype => l_item_type
1003 ,itemkey => l_item_key
1004 ,aname => 'OBJECT_TYPE'
1005 );
1006
1007 l_object_id := wf_engine.getitemattrNumber(
1008 itemtype => l_item_type
1009 ,itemkey => l_item_key
1010 ,aname => 'OBJECT_ID'
1011 );
1012
1013 l_error_msg := wf_engine.getitemattrText(
1014 itemtype => l_item_type
1015 ,itemkey => l_item_key
1016 ,aname => 'ERROR_MSG'
1017 );
1018
1019 /*--------------------------------------------------------------------------
1020 -- Query approval object table for any detail information of this object
1021 -- that will be used to replace tokens defined in FND Messages.
1022 -- Here to simplify, we are using hard-coded messages.
1023 ----------------------------------------------------------------------------*/
1024
1025 OPEN GetOperationDet(l_object_id);
1026 FETCH GetOperationDet into l_operation_rec;
1027
1028 IF GetOperationDet%NOTFOUND
1029 THEN
1030 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
1031 l_body := fnd_message.get;
1032 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
1033 ELSE
1034 fnd_message.set_name('AHL', 'AHL_RM_OPERNTF_FORWARD_SUBJECT');
1035 fnd_message.set_token('SEGMENT',l_operation_rec.SEGMENT, false);
1036 l_body := fnd_message.get;
1037 END IF;
1038 CLOSE GetOperationDet;
1039
1040
1041
1042 l_body :=l_body||'.'|| 'An error occured in the approval process of your request.'||fnd_global.local_chr(10);
1043 l_body := l_body || 'Please choose to cancel or re-submit your request.'||fnd_global.local_chr(10);
1044 l_body := l_body || 'Error Message'||l_error_msg;
1045
1046 document := document || l_body;
1047
1048 RETURN;
1049
1050 EXCEPTION
1051 WHEN FND_API.G_EXC_ERROR THEN
1052 FND_MSG_PUB.Count_And_Get (
1053 p_encoded => FND_API.G_FALSE,
1054 p_count => l_msg_count,
1055 p_data => l_msg_data
1056 );
1057 ahl_generic_aprv_pvt.Handle_Error
1058 (p_itemtype => l_item_type ,
1059 p_itemkey => l_item_key ,
1060 p_msg_count => l_msg_count, -- Number of error Messages
1061 p_msg_data => l_msg_data ,
1062 p_attr_name => 'ERROR_MSG',
1063 x_error_msg => l_error_msg
1064 ) ;
1065 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','Ntf_Error_Act',
1066 l_item_type,l_item_key,l_error_msg);
1067 RAISE;
1068 WHEN OTHERS THEN
1069 wf_core.context( 'AHL_OPERATIONS_APPROVAL_PVT'
1070 , 'Ntf_Error_Act'
1071 , l_item_type
1072 , l_item_key
1073 );
1074 RAISE;
1075 END Ntf_Error_Act;
1076
1077 PROCEDURE Update_Status(
1078 itemtype IN VARCHAR2
1079 ,itemkey IN VARCHAR2
1080 ,actid IN NUMBER
1081 ,funcmode IN VARCHAR2
1082 ,resultout OUT NOCOPY VARCHAR2)
1083 IS
1084 l_error_msg VARCHAR2(4000);
1085 l_next_status VARCHAR2(30);
1086 l_approval_status VARCHAR2(30);
1087 l_object_version_number NUMBER;
1088 l_object_id NUMBER;
1089 l_status_date DATE;
1090 l_msg_count NUMBER;
1091 l_msg_data VARCHAR2(4000);
1092 -- Variables for executing Complete_mr_Revision
1093 l_api_name CONSTANT VARCHAR2(30) := 'Update_Status';
1094
1095 l_commit VARCHAR2(1):=FND_API.G_TRUE;
1096 l_OPERATION_ID number:=0;
1097 l_comp_OPERATION_ID number:=0;
1098 l_api_version NUMBER:=1.0;
1099 l_init_msg_list VARCHAR2(1):= FND_API.G_TRUE;
1103 x_return_status VARCHAR2(2000);
1100 l_validate_only VARCHAR2(1):= FND_API.G_TRUE;
1101 l_validation_level NUMBER:= FND_API.G_VALID_LEVEL_FULL;
1102 l_module_type VARCHAR2(1);
1104 l_return_status VARCHAR2(1);
1105 x_msg_count NUMBER;
1106 x_msg_data VARCHAR2(2000);
1107 l_default VARCHAR2(1):= FND_API.G_FALSE;
1108 l_status VARCHAR2(30);
1109 l_body VARCHAR2(2000);
1110 l_approver_note VARCHAR2(2000);
1111
1112 cursor GetOperationDet(c_OPERATION_ID number)
1113 is
1114 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
1115 from ahl_OPERATIONS_b_KFV
1116 where OPERATION_ID=c_OPERATION_ID;
1117
1118
1119 l_operation_rec GetOperationDet%rowtype;
1120 BEGIN
1121 IF G_DEBUG='Y' THEN
1122 AHL_DEBUG_PUB.enable_debug;
1123 AHL_DEBUG_PUB.debug( 'Start UpdateStatus','+DebugWfOper+');
1124 END IF;
1125
1126 IF funcmode = 'RUN' THEN
1127 l_approval_status := wf_engine.getitemattrtext(
1128 itemtype => itemtype
1129 ,itemkey => itemkey
1130 ,aname => 'UPDATE_GEN_STATUS'
1131 );
1132 IF G_DEBUG='Y' THEN
1133 AHL_DEBUG_PUB.debug( 'Update Status After Getitemattrtext','+DebugWfOper+');
1134 END IF;
1135
1136 IF l_approval_status = 'APPROVED' THEN
1137 l_next_status := wf_engine.getitemattrText(
1138 itemtype => itemtype
1139 ,itemkey => itemkey
1140 ,aname => 'NEW_STATUS_ID'
1141 );
1142
1143 ELSE
1144 l_next_status := wf_engine.getitemattrText(
1145 itemtype => itemtype
1146 ,itemkey => itemkey
1147 ,aname => 'REJECT_STATUS_ID'
1148 );
1149 END IF;
1150
1151 l_object_version_number := wf_engine.getitemattrnumber(
1152 itemtype => itemtype
1153 ,itemkey => itemkey
1154 ,aname => 'OBJECT_VER'
1155 );
1156 l_object_id := wf_engine.getitemattrnumber(
1157 itemtype => itemtype
1158 ,itemkey => itemkey
1159 ,aname => 'OBJECT_ID'
1160 );
1161
1162 l_status_date := SYSDATE;
1163
1164 l_approver_note := wf_engine.GetItemAttrText(
1165 itemtype => itemtype,
1166 itemkey => itemkey,
1167 aname => 'APPROVER NOTE' );
1168
1169 OPEN GetOperationDet(l_object_id);
1170 FETCH GetOperationDet INTO l_operation_rec;
1171 IF GetOperationDet%notfound
1172 then
1173 fnd_message.set_name('AHL', 'AHL_RM_INVALID_OPERATION');
1174 l_body := fnd_message.get;
1175 l_body:=l_body||'-'||to_char(l_operation_rec.OPERATION_ID);
1176 end if;
1177
1178 CLOSE GetOperationDet;
1179 IF G_DEBUG='Y' THEN
1180 AHL_DEBUG_PUB.debug( 'Before CompleteOperRev'||l_approval_status,'+DebugWfOper+');
1181 END IF;
1182
1183 AHL_RM_APPROVAL_PVT.COMPLETE_OPER_REVISION
1184 (
1185 p_api_version =>l_api_version,
1186 p_init_msg_list =>l_init_msg_list,
1187 p_commit =>l_commit,
1188 p_validation_level =>l_validation_level ,
1189 p_default =>l_default ,
1190 p_module_type =>'JSP',
1191 x_return_status =>l_return_status,
1192 x_msg_count =>x_msg_count ,
1193 x_msg_data =>x_msg_data ,
1194 p_appr_status =>l_approval_status,
1195 p_operation_id =>l_object_id,
1196 p_object_version_number =>l_object_version_number,
1197 p_approver_note =>l_approver_note
1198 );
1199 IF G_DEBUG='Y' THEN
1200 AHL_DEBUG_PUB.debug( 'After CompleteOperRev'||l_approval_status,'+DebugWfOper+');
1201 END IF;
1202
1203 COMMIT;
1204 resultout := 'COMPLETE:';
1205 RETURN;
1206 END IF;
1207
1208 -- CANCEL mode
1209 --
1210 IF (funcmode = 'CANCEL') THEN
1211 resultout := 'COMPLETE:';
1212 RETURN;
1213 END IF;
1214
1215 --
1216 -- TIMEOUT mode
1217 --
1218 IF (funcmode = 'TIMEOUT') THEN
1219 resultout := 'COMPLETE:';
1220 RETURN;
1221 END IF;
1222
1223
1224 EXCEPTION
1225 WHEN fnd_api.g_exc_error THEN
1226 IF G_DEBUG='Y' THEN
1227 AHL_DEBUG_PUB.debug( 'GExec Error:'||sqlerrm,'+DebugWfOper+');
1228 END IF;
1229 FND_MSG_PUB.Count_And_Get (
1230 p_encoded => FND_API.G_FALSE,
1231 p_count => l_msg_count,
1232 p_data => l_msg_data
1233 );
1234 ahl_generic_aprv_pvt.Handle_Error
1238 p_msg_data => l_msg_data ,
1235 (p_itemtype => itemtype ,
1236 p_itemkey => itemkey ,
1237 p_msg_count => l_msg_count, -- Number of error Messages
1239 p_attr_name => 'ERROR_MSG',
1240 x_error_msg => l_error_msg
1241 ) ;
1242 wf_core.context('AHL_RM_APPROVAL_PVT','UPDATE_STATUS',
1243 itemtype,itemkey,actid,funcmode,l_error_msg);
1244 RAISE;
1245
1246 WHEN OTHERS THEN
1247 IF G_DEBUG='Y' THEN
1248 AHL_DEBUG_PUB.debug( 'When others Error:'||sqlerrm,'+DebugwfOper+');
1249 END IF;
1250
1251 wf_core.context(
1252 'AHL_OPERATIONS_APPROVAL_PVT'
1253 ,'Update_Status'
1254 ,itemtype
1255 ,itemkey
1256 ,actid
1257 ,funcmode
1258 ,'Unexpected Error!'
1259 );
1260 RAISE;
1261
1262 END Update_Status;
1263
1264 PROCEDURE Revert_Status(
1265 itemtype IN VARCHAR2
1266 ,itemkey IN VARCHAR2
1267 ,actid IN NUMBER
1268 ,funcmode IN VARCHAR2
1269 ,resultout OUT NOCOPY VARCHAR2)
1270 IS
1271 l_error_msg VARCHAR2(4000);
1272 l_next_status VARCHAR2(30);
1273 l_approval_status VARCHAR2(30);
1274 l_object_version_number NUMBER;
1275 l_object_id NUMBER;
1276 l_status_date DATE;
1277 l_msg_count NUMBER;
1278 l_msg_data VARCHAR2(4000);
1279
1280 cursor GetOperationDet(c_OPERATION_ID number)
1281 is
1282 select OPERATION_ID,CONCATENATED_SEGMENTS SEGMENT
1283 from ahl_OPERATIONS_b_KFV
1284 where OPERATION_ID=c_OPERATION_ID;
1285
1286 l_operation_rec GetOperationDet%rowtype;
1287 l_return_status VARCHAR2(1);
1288
1289 BEGIN
1290 IF G_DEBUG='Y' THEN
1291 AHL_DEBUG_PUB.enable_debug;
1292 AHL_DEBUG_PUB.debug( 'Start Revert Status','+WfforOper+');
1293 END IF;
1294
1295 l_return_Status:='S';
1296 IF funcmode = 'RUN' THEN
1297 l_next_status := wf_engine.getitemattrText(
1298 itemtype => itemtype
1299 ,itemkey => itemkey
1300 ,aname => 'ORG_STATUS_ID'
1301 );
1302
1303 l_object_version_number := wf_engine.getitemattrnumber(
1304 itemtype => itemtype
1305 ,itemkey => itemkey
1306 ,aname => 'OBJECT_VER'
1307 );
1308 l_object_id := wf_engine.getitemattrnumber(
1309 itemtype => itemtype
1310 ,itemkey => itemkey
1311 ,aname => 'OBJECT_ID'
1312 );
1313
1314 l_status_date := SYSDATE;
1315
1316 UPDATE AHL_OPERATIONS_B
1317 SET REVISION_STATUS_CODE = 'DRAFT',
1318 object_version_number =l_object_version_number+1
1319 WHERE OPERATION_ID = l_object_id
1320 and object_Version_number=l_object_version_number;
1321
1322 COMMIT;
1323 resultout := 'COMPLETE:';
1324 RETURN;
1325 END IF;
1326
1327 -- CANCEL mode
1328 --
1329 IF (funcmode = 'CANCEL') THEN
1330 resultout := 'COMPLETE:';
1331 RETURN;
1332 END IF;
1333
1334 --
1335 -- TIMEOUT mode
1336 --
1337 IF (funcmode = 'TIMEOUT') THEN
1338 resultout := 'COMPLETE:';
1339 RETURN;
1340 END IF;
1341
1342
1343 EXCEPTION
1344 WHEN fnd_api.g_exc_error THEN
1345 FND_MSG_PUB.Count_And_Get (
1346 p_encoded => FND_API.G_FALSE,
1347 p_count => l_msg_count,
1348 p_data => l_msg_data
1349 );
1350 ahl_generic_aprv_pvt.Handle_Error
1351 (p_itemtype => itemtype ,
1352 p_itemkey => itemkey ,
1353 p_msg_count => l_msg_count, -- Number of error Messages
1354 p_msg_data => l_msg_data ,
1355 p_attr_name => 'ERROR_MSG',
1356 x_error_msg => l_error_msg
1357 ) ;
1358 wf_core.context('AHL_OPERATIONS_APPROVAL_PVT','revert_status',
1359 itemtype,itemkey,actid,funcmode,l_error_msg);
1360 RAISE;
1361 WHEN OTHERS THEN
1362 wf_core.context(
1363 'AHL_OPERATIONS_APPROVAL_PVT'
1364 ,'REVERT_STATUS'
1365 ,itemtype
1366 ,itemkey
1367 ,actid
1368 ,funcmode
1369 ,'Unexpected Error!'
1370 );
1371 RAISE;
1372
1373 END Revert_Status;
1374 END AHL_OPERATIONS_APPROVAL_PVT;
1375