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