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