[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_APPROVAL_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_APPROVAL_PVT AS
2 /* $Header: AHLVMWKB.pls 120.1.12020000.2 2012/12/07 00:51:05 sareepar ship $ */
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_APPRTRM_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');
382 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
379 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID);
380 l_body := fnd_message.get;
381 ELSE
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('APPROVER',l_approver);
398 -- fnd_message.set_token('l_approver',l_mr_header_rec.TITLE, false);
399 l_body := l_body||fnd_message.get;
400 l_subject:= fnd_message.get||'-'||l_body;
401 document := document || l_body;
402
403 IF G_DEBUG='Y' THEN
404 AHL_DEBUG_PUB.disable_debug;
405 END IF;
406 RETURN;
407
408 EXCEPTION
409 WHEN FND_API.G_EXC_ERROR THEN
410 FND_MSG_PUB.Count_And_Get (
411 p_encoded => FND_API.G_FALSE,
412 p_count => l_msg_count,
413 p_data => l_msg_data
414 );
415 ahl_generic_aprv_pvt.Handle_Error
416 (p_itemtype => l_item_type,
417 p_itemkey => l_item_key,
418 p_msg_count => l_msg_count,
419 p_msg_data => l_msg_data ,
420 p_attr_name => 'ERROR_MSG',
421 x_error_msg => l_error_msg
422 ) ;
423 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','NTF_FORWARD_FYI',
424 l_item_type,l_item_key,l_error_msg);
425 IF G_DEBUG='Y' THEN
426 AHL_DEBUG_PUB.disable_debug;
427 END IF;
428 RAISE;
429 WHEN OTHERS THEN
430 wf_core.context( 'AHLGAPP'
431 , 'Ntf_Forward_FYI'
432 , l_item_type
433 , l_item_key
434 );
435 IF G_DEBUG='Y' THEN
436 AHL_DEBUG_PUB.disable_debug;
437 END IF;
438 RAISE;
439 END Ntf_Forward_FYI;
440
441 PROCEDURE Ntf_Approved_FYI(
442 document_id IN VARCHAR2
443 ,display_type IN VARCHAR2
444 ,document IN OUT NOCOPY VARCHAR2
445 ,document_type IN OUT NOCOPY VARCHAR2)
446 IS
447
448 l_hyphen_pos1 NUMBER;
449 l_object VARCHAR2(30);
450 l_item_type VARCHAR2(30);
451 l_item_key VARCHAR2(30);
452 l_approver VARCHAR2(30);
453 l_body VARCHAR2(3500);
454 l_subject VARCHAR2(500);
455 l_object_id NUMBER;
456 l_msg_count NUMBER;
457 l_msg_data VARCHAR2(4000);
458 l_error_msg VARCHAR2(2000);
459
460 cursor GetMrHeaderDet(c_mr_header_id number)
461 is
462 select mr_header_id,title,effective_from,VERSION_NUMBER
463 from ahl_mr_headers_b
464 where mr_header_id=c_mr_header_id;
465
466 l_mr_header_rec GetMrHeaderDet%rowtype;
467
468 BEGIN
469 IF G_DEBUG='Y' THEN
470 AHL_DEBUG_PUB.enable_debug;
471 AHL_DEBUG_PUB.debug( 'Start Notify Approved FYI');
472 END IF;
473 document_type := 'text/plain';
474
475 l_hyphen_pos1 := INSTR(document_id, ':');
476 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
477 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
478
479 l_object := wf_engine.getitemattrtext(
480 itemtype => l_item_type
481 ,itemkey => l_item_key
482 ,aname => 'OBJECT_TYPE'
483 );
484
485 l_object_id := wf_engine.getitemattrNumber(
486 itemtype => l_item_type
487 ,itemkey => l_item_key
488 ,aname => 'OBJECT_ID'
489 );
490
491 l_approver := wf_engine.getitemattrtext(
492 itemtype => l_item_type
493 ,itemkey => l_item_key
494 ,aname => 'APPROVER'
495 );
496
497 /*--------------------------------------------------------------------------
498 -- Query approval object table for any detail information of this object
499 -- that will be used to replace tokens defined in FND Messages.
500 -- Here to simplify, we are using hard-coded messages.
501 ----------------------------------------------------------------------------*/
502 OPEN GetMrHeaderDet(l_object_id);
503 FETCH GetMrHeaderDet into l_mr_header_rec;
504
505 IF GetMrHeaderDet%NOTFOUND
506 THEN
507 fnd_message.set_name('AHL', 'AHL_MR_HEADER_ID_INVALID');
508 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID);
509 l_body := fnd_message.get;
510 ELSE
511 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
512 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE, false);
513 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.version_number);
514 l_body := fnd_message.get;
515 l_subject:= fnd_message.get;
516 END IF;
517 CLOSE GetMrHeaderDet;
518
519 fnd_message.set_name('AHL', 'AHL_FMP_MRNTF_APPRVED_FYI_BODY');
523 document := document || l_body;
520 fnd_message.set_token('APPROVER',l_approver);
521 l_body :=l_body||'.'||fnd_message.get;
522 l_subject :=l_body||fnd_message.get;
524 IF G_DEBUG='Y' THEN
525 AHL_DEBUG_PUB.disable_debug;
526 END IF;
527
528 RETURN;
529
530 EXCEPTION
531 WHEN FND_API.G_EXC_ERROR THEN
532 FND_MSG_PUB.Count_And_Get (
533 p_encoded => FND_API.G_FALSE,
534 p_count => l_msg_count,
535 p_data => l_msg_data
536 );
537 ahl_generic_aprv_pvt.Handle_Error
538 (p_itemtype => l_item_type ,
539 p_itemkey => l_item_key ,
540 p_msg_count => l_msg_count,
541 p_msg_data => l_msg_data ,
542 p_attr_name => 'ERROR_MSG',
543 x_error_msg => l_error_msg
544 ) ;
545 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','Ntf_Approved_FYI',
546 l_item_type,l_item_key,l_error_msg);
547 IF G_DEBUG='Y' THEN
548 AHL_DEBUG_PUB.disable_debug;
549 END IF;
550 RAISE;
551 WHEN OTHERS THEN
552 wf_core.context( 'AHLGAPP'
553 , 'Ntf_Approved_FYI'
554 , l_item_type
555 , l_item_key
556 );
557 IF G_DEBUG='Y' THEN
558 AHL_DEBUG_PUB.disable_debug;
559 END IF;
560 RAISE;
561 END Ntf_Approved_FYI;
562
563 PROCEDURE Ntf_Final_Approval_FYI(
564 document_id IN VARCHAR2
565 ,display_type IN VARCHAR2
566 ,document IN OUT NOCOPY VARCHAR2
567 ,document_type IN OUT NOCOPY VARCHAR2)
568 IS
569 l_hyphen_pos1 NUMBER;
570 l_object VARCHAR2(30);
571 l_item_type VARCHAR2(30);
572 l_item_key VARCHAR2(30);
573 l_body VARCHAR2(3500);
574 l_subject VARCHAR2(500);
575 l_object_id NUMBER;
576 l_msg_count NUMBER;
577 l_msg_data VARCHAR2(4000);
578 l_error_msg VARCHAR2(2000);
579
580 cursor GetMrHeaderDet(c_mr_header_id number)
581 is
582 select mr_header_id,title,effective_from,version_number
583 from ahl_mr_headers_b
584 where mr_header_id=c_mr_header_id;
585
586 l_mr_header_rec GetMrHeaderDet%rowtype;
587
588 BEGIN
589 IF G_DEBUG='Y' THEN
590 AHL_DEBUG_PUB.enable_debug;
591 AHL_DEBUG_PUB.debug( 'Start NTF Final approval');
592 END IF;
593
594 document_type := 'text/plain';
595
596 -- parse document_id for the ':' dividing item type name from item key value
597 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
598 -- release 2.5 version of this demo
599
600 l_hyphen_pos1 := INSTR(document_id, ':');
601 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
602 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
603
604 l_object := wf_engine.getitemattrtext(
605 itemtype => l_item_type
606 ,itemkey => l_item_key
607 ,aname => 'OBJECT_TYPE'
608 );
609
610 l_object_id := wf_engine.getitemattrNumber(
611 itemtype => l_item_type
612 ,itemkey => l_item_key
613 ,aname => 'OBJECT_ID'
614 );
615
616
617 /*--------------------------------------------------------------------------
618 -- Query approval object table for any detail information of this object
619 -- that will be used to replace tokens defined in FND Messages.
620 -- Here to simplify, we are using hard-coded messages.
621 ----------------------------------------------------------------------------*/
622
623 OPEN GetMrHeaderDet(l_object_id);
624 FETCH GetMrHeaderDet into l_mr_header_rec;
625
626 IF GetMrHeaderDet%NOTFOUND
627 THEN
628 fnd_message.set_name('AHL', 'AHL_MR_HEADER_ID_INVALID');
629 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID,false);
630 l_body := fnd_message.get;
631 ELSE
632 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
633 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE, false);
634 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.version_number);
635 l_body := fnd_message.get;
636 l_subject:= fnd_message.get;
637 END IF;
638 CLOSE GetMrHeaderDet;
639
640 fnd_message.set_name('AHL', 'AHL_FMP_MRNTF_FINAL_APPROVAL');
641 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE, false);
642 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.version_number);
643
644
645
646 l_body :=l_body||'.'|| fnd_message.get;
647
648 document := document || l_body;
649 IF G_DEBUG='Y' THEN
650 AHL_DEBUG_PUB.disable_debug;
651 END IF;
652
653 RETURN;
654
655 EXCEPTION
656 WHEN FND_API.G_EXC_ERROR THEN
657 FND_MSG_PUB.Count_And_Get (
658 p_encoded => FND_API.G_FALSE,
659 p_count => l_msg_count,
660 p_data => l_msg_data
661 );
662 ahl_generic_aprv_pvt.Handle_Error
663 (p_itemtype => l_item_type ,
664 p_itemkey => l_item_key ,
665 p_msg_count => l_msg_count,
666 p_msg_data => l_msg_data ,
667 p_attr_name => 'ERROR_MSG',
671 l_item_type,l_item_key,l_error_msg);
668 x_error_msg => l_error_msg
669 ) ;
670 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','Ntf_Final_Approval_FYI',
672 IF G_DEBUG='Y' THEN
673 AHL_DEBUG_PUB.disable_debug;
674 END IF;
675 RAISE;
676 WHEN OTHERS THEN
677 wf_core.context( 'AHLGAPP'
678 , 'Ntf_Final_Approval_FYI'
679 , l_item_type
680 , l_item_key
681 );
682 IF G_DEBUG='Y' THEN
683 AHL_DEBUG_PUB.disable_debug;
684 END IF;
685 RAISE;
686 END Ntf_Final_Approval_FYI;
687
688
689 PROCEDURE Ntf_Rejected_FYI(
690 document_id IN VARCHAR2
691 ,display_type IN VARCHAR2
692 ,document IN OUT NOCOPY VARCHAR2
693 ,document_type IN OUT NOCOPY VARCHAR2)
694 IS
695 l_hyphen_pos1 NUMBER;
696 l_object VARCHAR2(30);
697 l_item_type VARCHAR2(30);
698 l_item_key VARCHAR2(30);
699 l_approver VARCHAR2(30);
700 l_body VARCHAR2(3500);
701 l_subject VARCHAR2(500);
702 l_object_id NUMBER;
703 l_msg_count NUMBER;
704 l_msg_data VARCHAR2(4000);
705 l_error_msg VARCHAR2(2000);
706 cursor GetMrHeaderDet(c_mr_header_id number)
707 is
708 select mr_header_id,title,effective_from,VERSION_NUMBER
709 from ahl_mr_headers_b
710 where mr_header_id=c_mr_header_id;
711 l_mr_header_rec GetMrHeaderDet%rowtype;
712 BEGIN
713 IF G_DEBUG='Y' THEN
714 AHL_DEBUG_PUB.enable_debug;
715 AHL_DEBUG_PUB.debug( 'Start Notify Rejected');
716 END IF;
717 document_type := 'text/plain';
718
719 -- parse document_id for the ':' dividing item type name from item key value
720 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
721 -- release 2.5 version of this demo
722
723 l_hyphen_pos1 := INSTR(document_id, ':');
724 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
725 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
726
727 l_object := wf_engine.getitemattrtext(
728 itemtype => l_item_type
729 ,itemkey => l_item_key
730 ,aname => 'OBJECT_TYPE'
731 );
732
733 l_object_id := wf_engine.getitemattrNumber(
734 itemtype => l_item_type
735 ,itemkey => l_item_key
736 ,aname => 'OBJECT_ID'
737 );
738
739 l_approver := wf_engine.getitemattrtext(
740 itemtype => l_item_type
741 ,itemkey => l_item_key
742 ,aname => 'APPROVER'
743 );
744 OPEN GetMrHeaderDet(l_object_id);
745 FETCH GetMrHeaderDet into l_mr_header_rec;
746
747 IF GetMrHeaderDet%NOTFOUND
748 THEN
749 fnd_message.set_name('AHL', 'AHL_MR_HEADER_ID_INVALID');
750 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID,false);
751 l_body := fnd_message.get;
752 ELSE
753 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
754 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE, false);
755 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.VERSION_NUMBER,false);
756 l_body := fnd_message.get;
757 l_subject:= fnd_message.get;
758 END IF;
759 CLOSE GetMrHeaderDet;
760
761 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_APPROVAL_REJECT');
762 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE);
763 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.VERSION_NUMBER);
764 fnd_message.set_token('APPROVER',l_approver);
765 l_body := fnd_message.get;
766
767
768 l_body := l_body||fnd_message.get;
769 l_subject:= l_body||fnd_message.get;
770
771 document := document || l_body;
772 IF G_DEBUG='Y' THEN
773 AHL_DEBUG_PUB.disable_debug;
774 END IF;
775 RETURN;
776
777 EXCEPTION
778 WHEN FND_API.G_EXC_ERROR THEN
779 FND_MSG_PUB.Count_And_Get (
780 p_encoded => FND_API.G_FALSE,
781 p_count => l_msg_count,
782 p_data => l_msg_data
783 );
784 ahl_generic_aprv_pvt.Handle_Error
785 (p_itemtype => l_item_type ,
786 p_itemkey => l_item_key ,
787 p_msg_count => l_msg_count, -- Number of error Messages
788 p_msg_data => l_msg_data ,
789 p_attr_name => 'ERROR_MSG',
790 x_error_msg => l_error_msg
791 ) ;
792 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','Ntf_Rejected_FYI',
793 l_item_type,l_item_key,l_error_msg);
794 IF G_DEBUG='Y' THEN
795 AHL_DEBUG_PUB.disable_debug;
796 END IF;
797 RAISE;
798
799 WHEN OTHERS THEN
800 wf_core.context( 'AHLGAPP'
801 , 'Ntf_Rejected_FYI'
802 , l_item_type
803 , l_item_key
804 );
805 IF G_DEBUG='Y' THEN
806 AHL_DEBUG_PUB.disable_debug;
807 END IF;
808 RAISE;
809 END Ntf_Rejected_FYI;
810
811
812 PROCEDURE Ntf_Approval(
813 document_id IN VARCHAR2
814 ,display_type IN VARCHAR2
818
815 ,document IN OUT NOCOPY VARCHAR2
816 ,document_type IN OUT NOCOPY VARCHAR2)
817 IS
819 l_hyphen_pos1 NUMBER;
820 l_object VARCHAR2(30);
821 l_item_type VARCHAR2(30);
822 l_item_key VARCHAR2(30);
823 l_requester VARCHAR2(30);
824 l_requester_note VARCHAR2(4000);
825 l_body VARCHAR2(3500);
826 l_subject VARCHAR2(500);
827 l_object_id NUMBER;
828 l_msg_count NUMBER;
829 l_msg_data VARCHAR2(4000);
830 l_error_msg VARCHAR2(2000);
831 cursor GetMrHeaderDet(c_mr_header_id number)
832 is
833 select mr_header_id,title,effective_from,version_number
834 from ahl_mr_headers_b
835 where mr_header_id=c_mr_header_id;
836 l_mr_header_rec GetMrHeaderDet%rowtype;
837 BEGIN
838
839 IF G_DEBUG='Y' THEN
840 AHL_DEBUG_PUB.enable_debug;
841 AHL_DEBUG_PUB.debug( 'Start Nty_approval');
842 END IF;
843
844 document_type := 'text/plain';
845
846 -- parse document_id for the ':' dividing item type name from item key value
847 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
848 -- release 2.5 version of this demo
849
850 l_hyphen_pos1 := INSTR(document_id, ':');
851 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
852 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
853
854 l_object := wf_engine.getitemattrtext(
855 itemtype => l_item_type
856 ,itemkey => l_item_key
857 ,aname => 'OBJECT_TYPE'
858 );
859
860 l_object_id := wf_engine.getitemattrNumber(
861 itemtype => l_item_type
862 ,itemkey => l_item_key
863 ,aname => 'OBJECT_ID'
864 );
865
866 l_requester := wf_engine.getitemattrtext(
867 itemtype => l_item_type
868 ,itemkey => l_item_key
869 ,aname => 'REQUESTER'
870 );
871
872 l_requester_note := wf_engine.getitemattrtext(
873 itemtype => l_item_type
874 ,itemkey => l_item_key
875 ,aname => 'REQUESTER_NOTE'
876 );
877
878 OPEN GetMrHeaderDet(l_object_id);
879 FETCH GetMrHeaderDet into l_mr_header_rec;
880
881 IF GetMrHeaderDet%NOTFOUND
882 THEN
883 fnd_message.set_name('AHL', 'AHL_MR_HEADER_ID_INVALID');
884 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID);
885 l_body := fnd_message.get;
886 ELSE
887 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
888 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE);
889 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.VERSION_NUMBER);
890 l_body := fnd_message.get;
891 l_subject:= fnd_message.get;
892 END IF;
893 CLOSE GetMrHeaderDet;
894 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_APPROVER');
895 fnd_message.set_token('REQUESTER',l_requester);
896 fnd_message.set_token('REQUESTER_NOTE',l_requester_note);
897 l_body :=l_body||fnd_message.get;
898 document := document || l_body;
899
900 IF G_DEBUG='Y' THEN
901 AHL_DEBUG_PUB.disable_debug;
902 END IF;
903 RETURN;
904 EXCEPTION
905 WHEN FND_API.G_EXC_ERROR THEN
906 FND_MSG_PUB.Count_And_Get (
907 p_encoded => FND_API.G_FALSE,
908 p_count => l_msg_count,
909 p_data => l_msg_data
910 );
911 ahl_generic_aprv_pvt.Handle_Error
912 (p_itemtype => l_item_type ,
913 p_itemkey => l_item_key ,
914 p_msg_count => l_msg_count, -- Number of error Messages
915 p_msg_data => l_msg_data ,
916 p_attr_name => 'ERROR_MSG',
917 x_error_msg => l_error_msg
918 ) ;
919 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','Ntf_Approval',
920 l_item_type,l_item_key,l_error_msg);
921 IF G_DEBUG='Y' THEN
922 AHL_DEBUG_PUB.disable_debug;
923 END IF;
924 RAISE;
925 WHEN OTHERS THEN
926 wf_core.context( 'AHLGAPP'
927 , 'Ntf_Approval'
928 , l_item_type
929 , l_item_key
930 );
931 IF G_DEBUG='Y' THEN
932 AHL_DEBUG_PUB.disable_debug;
933 END IF;
934 RAISE;
935 END Ntf_Approval;
936
937
938 PROCEDURE Ntf_Approval_Reminder(
939 document_id IN VARCHAR2
940 ,display_type IN VARCHAR2
941 ,document IN OUT NOCOPY VARCHAR2
942 ,document_type IN OUT NOCOPY VARCHAR2)
943 IS
944
945 l_hyphen_pos1 NUMBER;
946 l_object VARCHAR2(30);
947 l_item_type VARCHAR2(30);
948 l_item_key VARCHAR2(30);
949 l_requester VARCHAR2(30);
950 l_requester_note VARCHAR2(4000);
951 l_body VARCHAR2(5000);
952 l_object_id NUMBER;
953 l_msg_count NUMBER;
954 l_msg_data VARCHAR2(4000);
955 l_error_msg VARCHAR2(2000);
956 l_subject VARCHAR2(500);
957 cursor GetMrHeaderDet(c_mr_header_id number)
958 is
959 select mr_header_id,title,effective_from,version_number
960 from ahl_mr_headers_b
961 where mr_header_id=c_mr_header_id;
965 AHL_DEBUG_PUB.enable_debug;
962 l_mr_header_rec GetMrHeaderDet%rowtype;
963 BEGIN
964 IF G_DEBUG='Y' THEN
966 AHL_DEBUG_PUB.debug( 'Start ntfy Apprvl remainder');
967 END IF;
968 document_type := 'text/plain';
969
970 l_hyphen_pos1 := INSTR(document_id, ':');
971 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
972 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
973
974 l_object := wf_engine.getitemattrtext(
975 itemtype => l_item_type
976 ,itemkey => l_item_key
977 ,aname => 'OBJECT_TYPE'
978 );
979
980 l_object_id := wf_engine.getitemattrNumber(
981 itemtype => l_item_type
982 ,itemkey => l_item_key
983 ,aname => 'OBJECT_ID'
984 );
985
986 l_requester := wf_engine.getitemattrtext(
987 itemtype => l_item_type
988 ,itemkey => l_item_key
989 ,aname => 'REQUESTER'
990 );
991
992 l_requester_note := wf_engine.getitemattrtext(
993 itemtype => l_item_type
994 ,itemkey => l_item_key
995 ,aname => 'REQUESTER_NOTE'
996 );
997
998 OPEN GetMrHeaderDet(l_object_id);
999 FETCH GetMrHeaderDet into l_mr_header_rec;
1000
1001 IF GetMrHeaderDet%NOTFOUND
1002 THEN
1003 fnd_message.set_name('AHL', 'AHL_MR_HEADER_ID_INVALID');
1004 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID,false);
1005 l_body := fnd_message.get;
1006 ELSE
1007 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
1008 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE, false);
1009 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.version_number);
1010 l_body := fnd_message.get;
1011 l_subject:= fnd_message.get;
1012 END IF;
1013 CLOSE GetMrHeaderDet;
1014
1015
1016
1017 --l_body :=l_body||'.'|| 'Reminder: You just received a request from '||l_requester;
1018 --l_body := l_body ||'. The note from him/her is as following: '||l_requester_note;
1019 document := document || l_body;
1020 IF G_DEBUG='Y' THEN
1021 AHL_DEBUG_PUB.disable_debug;
1022 END IF;
1023
1024 RETURN;
1025
1026 EXCEPTION
1027 WHEN FND_API.G_EXC_ERROR THEN
1028 FND_MSG_PUB.Count_And_Get (
1029 p_encoded => FND_API.G_FALSE,
1030 p_count => l_msg_count,
1031 p_data => l_msg_data
1032 );
1033 ahl_generic_aprv_pvt.Handle_Error
1034 (p_itemtype => l_item_type ,
1035 p_itemkey => l_item_key ,
1036 p_msg_count => l_msg_count, -- Number of error Messages
1037 p_msg_data => l_msg_data ,
1038 p_attr_name => 'ERROR_MSG',
1039 x_error_msg => l_error_msg
1040 ) ;
1041 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','Ntf_Approval_Reminder',
1042 l_item_type,l_item_key,l_error_msg);
1043 IF G_DEBUG='Y' THEN
1044 AHL_DEBUG_PUB.disable_debug;
1045 END IF;
1046 RAISE;
1047 WHEN OTHERS THEN
1048 wf_core.context( 'AHLGAPP'
1049 , 'Ntf_Approval_Reminder'
1050 , l_item_type
1051 , l_item_key
1052 );
1053 IF G_DEBUG='Y' THEN
1054 AHL_DEBUG_PUB.disable_debug;
1055 END IF;
1056 RAISE;
1057 END Ntf_Approval_Reminder;
1058
1059
1060
1061
1062 PROCEDURE Ntf_Error_Act(
1063 document_id IN VARCHAR2
1064 ,display_type IN VARCHAR2
1065 ,document IN OUT NOCOPY VARCHAR2
1066 ,document_type IN OUT NOCOPY VARCHAR2)
1067 IS
1068
1069 l_hyphen_pos1 NUMBER;
1070 l_object VARCHAR2(30);
1071 l_item_type VARCHAR2(30);
1072 l_item_key VARCHAR2(30);
1073 l_body VARCHAR2(3500);
1074 l_object_id NUMBER;
1075 l_error_msg VARCHAR2(4000);
1076 l_msg_count NUMBER;
1077 l_msg_data VARCHAR2(4000);
1078 l_subject VARCHAR2(500);
1079 cursor GetMrHeaderDet(c_mr_header_id number)
1080 is
1081 select mr_header_id,title,effective_from,VERSION_NUMBER
1082 from ahl_mr_headers_b
1083 where mr_header_id=c_mr_header_id;
1084 l_mr_header_rec GetMrHeaderDet%rowtype;
1085 BEGIN
1086 IF G_DEBUG='Y' THEN
1087 AHL_DEBUG_PUB.enable_debug;
1088 AHL_DEBUG_PUB.debug( 'Start Ntfy error','+NOTIFY ERROR ACT+');
1089 END IF;
1090
1091
1092 document_type := 'text/plain';
1093
1094 l_hyphen_pos1 := INSTR(document_id, ':');
1095 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1096 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1097
1098 l_object := wf_engine.getitemattrtext(
1099 itemtype => l_item_type
1100 ,itemkey => l_item_key
1101 ,aname => 'OBJECT_TYPE'
1102 );
1103
1104 l_object_id := wf_engine.getitemattrNumber(
1105 itemtype => l_item_type
1106 ,itemkey => l_item_key
1107 ,aname => 'OBJECT_ID'
1108 );
1109
1110 l_error_msg := wf_engine.getitemattrText(
1111 itemtype => l_item_type,
1112 itemkey => l_item_key,
1116
1113 aname => 'ERROR_MSG'
1114 );
1115
1117 OPEN GetMrHeaderDet(l_object_id);
1118 FETCH GetMrHeaderDet into l_mr_header_rec;
1119
1120 IF GetMrHeaderDet%NOTFOUND
1121 THEN
1122 fnd_message.set_name('AHL', 'AHL_MR_HEADER_ID_INVALID');
1123 fnd_message.set_token('MR_HEADER_ID',l_mr_header_rec.MR_HEADER_ID,false);
1124 l_body := fnd_message.get;
1125 ELSE
1126 fnd_message.set_name('AHL', 'AHL_FMP_MR_NTF_FORWARD_SUBJECT');
1127 fnd_message.set_token('TITLE',l_mr_header_rec.TITLE, false);
1128 fnd_message.set_token('VERSION_NUMBER',l_mr_header_rec.MR_HEADER_ID);
1129 l_body := fnd_message.get;
1130 l_subject:= fnd_message.get;
1131 END IF;
1132 CLOSE GetMrHeaderDet;
1133
1134
1135
1136 l_body :=l_body||'.'|| 'An error occured in the approval process of your request.'||fnd_global.local_chr(10);
1137 l_body := l_body || 'Please choose to cancel or re-submit your request.'||fnd_global.local_chr(10);
1138 l_body := l_body || 'Error Message'||l_error_msg;
1139
1140 document := document || l_body;
1141 IF G_DEBUG='Y' THEN
1142 AHL_DEBUG_PUB.disable_debug;
1143 END IF;
1144
1145 RETURN;
1146
1147 EXCEPTION
1148 WHEN FND_API.G_EXC_ERROR THEN
1149 FND_MSG_PUB.Count_And_Get (
1150 p_encoded => FND_API.G_FALSE,
1151 p_count => l_msg_count,
1152 p_data => l_msg_data
1153 );
1154 ahl_generic_aprv_pvt.Handle_Error
1155 (p_itemtype => l_item_type ,
1156 p_itemkey => l_item_key ,
1157 p_msg_count => l_msg_count,
1158 p_msg_data => l_msg_data ,
1159 p_attr_name => 'ERROR_MSG',
1160 x_error_msg => l_error_msg
1161 ) ;
1162 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','Ntf_Error_Act',
1163 l_item_type,l_item_key,l_error_msg);
1164 IF G_DEBUG='Y' THEN
1165 AHL_DEBUG_PUB.disable_debug;
1166 END IF;
1167 RAISE;
1168 WHEN OTHERS THEN
1169 wf_core.context( 'AHL_FMP_MR_APPROVAL_PVT'
1170 , 'Ntf_Error_Act'
1171 , l_item_type
1172 , l_item_key
1173 );
1174 IF G_DEBUG='Y' THEN
1175 AHL_DEBUG_PUB.disable_debug;
1176 END IF;
1177 RAISE;
1178 END Ntf_Error_Act;
1179
1180 PROCEDURE Update_Status(
1181 itemtype IN VARCHAR2
1182 ,itemkey IN VARCHAR2
1183 ,actid IN NUMBER
1184 ,funcmode IN VARCHAR2
1185 ,resultout OUT NOCOPY VARCHAR2)
1186 IS
1187 l_error_msg VARCHAR2(4000);
1188 l_next_status VARCHAR2(30);
1189 l_approval_status VARCHAR2(30);
1190 l_object_version_number NUMBER;
1191 l_object_id NUMBER;
1192 l_status_date DATE;
1193 l_msg_count NUMBER;
1194 l_msg_data VARCHAR2(4000);
1195 -- Variables for executing Complete_mr_Revision
1196 l_api_name CONSTANT VARCHAR2(30) := 'Update_Status';
1197 l_subject VARCHAR2(500);
1198 l_commit VARCHAR2(1):=FND_API.G_TRUE;
1199 l_mr_header_id number:=0;
1200 l_api_version NUMBER:=1.0;
1201 l_init_msg_list VARCHAR2(1):= FND_API.G_TRUE;
1202 l_validate_only VARCHAR2(1):= FND_API.G_TRUE;
1203 l_validation_level NUMBER:= FND_API.G_VALID_LEVEL_FULL;
1204 l_module_type VARCHAR2(1);
1205 x_return_status VARCHAR2(1);
1206 l_return_status VARCHAR2(1);
1207 x_msg_count NUMBER;
1208 x_msg_data VARCHAR2(2000);
1209 l_mr_header_rec ahl_FMP_mr_header_pvt.mr_header_Rec;
1210 l_default VARCHAR2(1):= FND_API.G_FALSE;
1211
1212 BEGIN
1213 IF G_DEBUG='Y' THEN
1214 AHL_DEBUG_PUB.enable_debug;
1215 AHL_DEBUG_PUB.debug( 'Start Update Status API','+UPDATE_STATUS+');
1216 END IF;
1217
1218 IF funcmode = 'RUN' THEN
1219 l_approval_status := wf_engine.getitemattrtext(
1220 itemtype => itemtype
1221 ,itemkey => itemkey
1222 ,aname => 'UPDATE_GEN_STATUS'
1223 );
1224
1225 IF l_approval_status = 'APPROVED' THEN
1226 l_next_status := wf_engine.getitemattrText(
1227 itemtype => itemtype
1228 ,itemkey => itemkey
1229 ,aname => 'NEW_STATUS_ID'
1230 );
1231
1232 ELSE
1233 l_next_status := wf_engine.getitemattrText(
1234 itemtype => itemtype
1235 ,itemkey => itemkey
1236 ,aname => 'REJECT_STATUS_ID'
1237 );
1238 END IF;
1239
1240 l_object_version_number := wf_engine.getitemattrnumber(
1241 itemtype => itemtype
1242 ,itemkey => itemkey
1243 ,aname => 'OBJECT_VER'
1244 );
1245 l_object_id := wf_engine.getitemattrnumber(
1246 itemtype => itemtype
1247 ,itemkey => itemkey
1248 ,aname => 'OBJECT_ID'
1249 );
1250
1251 IF G_DEBUG='Y' THEN
1252 AHL_DEBUG_PUB.debug( 'l_object_id mr_header_id'||l_object_id);
1253 AHL_DEBUG_PUB.debug( 'Approval Status--->'||l_approval_status);
1257 (
1254 AHL_DEBUG_PUB.debug( 'Before complete complete_mr_revision api');
1255 END IF;
1256 AHL_FMP_MR_REVISION_PVT.COMPLETE_MR_REVISION
1258 p_api_version =>l_api_version,
1259 p_init_msg_list =>l_init_msg_list,
1260 p_commit =>FND_API.G_FALSE,
1261 p_validation_level =>l_validation_level ,
1262 p_default =>l_default ,
1263 p_module_type =>'null',
1264 x_return_status =>l_return_status,
1265 x_msg_count =>x_msg_count ,
1266 x_msg_data =>x_msg_data ,
1267 p_appr_status =>l_approval_status,
1268 p_mr_header_id =>l_object_id,
1269 p_object_version_number =>l_object_version_number
1270 );
1271 IF G_DEBUG='Y' THEN
1272 AHL_DEBUG_PUB.debug( 'After complete Update Status');
1273 END IF;
1274 COMMIT;
1275 resultout := 'COMPLETE:';
1276 IF G_DEBUG='Y' THEN
1277 AHL_DEBUG_PUB.disable_debug;
1278 END IF;
1279 RETURN;
1280 END IF;
1281
1282 -- CANCEL mode
1283 --
1284 IF (funcmode = 'CANCEL') THEN
1285 resultout := 'COMPLETE:';
1286 IF G_DEBUG='Y' THEN
1287 AHL_DEBUG_PUB.disable_debug;
1288 END IF;
1289 RETURN;
1290 END IF;
1291
1292 --
1293 -- TIMEOUT mode
1294 --
1295 IF (funcmode = 'TIMEOUT') THEN
1296 resultout := 'COMPLETE:';
1297 IF G_DEBUG='Y' THEN
1298 AHL_DEBUG_PUB.disable_debug;
1299 END IF;
1300 RETURN;
1301 END IF;
1302
1303
1304 EXCEPTION
1305 WHEN fnd_api.g_exc_error THEN
1306 IF G_DEBUG='Y' THEN
1307 AHL_DEBUG_PUB.debug( ' Error in workflow:'||sqlerrm||'Update_status');
1308 END IF;
1309
1310 FND_MSG_PUB.Count_And_Get (
1311 p_encoded => FND_API.G_FALSE,
1312 p_count => l_msg_count,
1313 p_data => l_msg_data
1314 );
1315 ahl_generic_aprv_pvt.Handle_Error
1316 (p_itemtype => itemtype ,
1317 p_itemkey => itemkey ,
1318 p_msg_count => l_msg_count,
1319 p_msg_data => l_msg_data ,
1320 p_attr_name => 'ERROR_MSG',
1321 x_error_msg => l_error_msg
1322 ) ;
1323 wf_core.context('AHL_FMP_APRV_PVT','UPDATE_STATUS',
1324 itemtype,itemkey,actid,funcmode,l_error_msg);
1325 IF G_DEBUG='Y' THEN
1326 AHL_DEBUG_PUB.disable_debug;
1327 END IF;
1328 RAISE;
1329
1330 WHEN OTHERS THEN
1331 IF G_DEBUG='Y' THEN
1332 AHL_DEBUG_PUB.debug( ' Error ...2..'||sqlerrm||'<--From-->UPDATE_STATUS');
1333 END IF;
1334
1335 wf_core.context(
1336 'AHL_FMP_MR_APPROVAL_PVT'
1337 ,'Update_Status'
1338 ,itemtype
1339 ,itemkey
1340 ,actid
1341 ,funcmode
1342 ,'Unexpected Error!'
1343 );
1344 IF G_DEBUG='Y' THEN
1345 AHL_DEBUG_PUB.disable_debug;
1346 END IF;
1347 RAISE;
1348
1349 END Update_Status;
1350
1351 PROCEDURE Revert_Status(
1352 itemtype IN VARCHAR2
1353 ,itemkey IN VARCHAR2
1354 ,actid IN NUMBER
1355 ,funcmode IN VARCHAR2
1356 ,resultout OUT NOCOPY VARCHAR2)
1357 IS
1358 l_error_msg VARCHAR2(4000);
1359 l_next_status VARCHAR2(30);
1360 l_approval_status VARCHAR2(30);
1361 l_object_version_number NUMBER;
1362 l_object_id NUMBER;
1363 l_status_date DATE;
1364 l_msg_count NUMBER;
1365 l_msg_data VARCHAR2(4000);
1366 l_subject VARCHAR2(500);
1367 cursor GetMrHeaderDet(c_mr_header_id number)
1368 is
1369 select mr_header_id,title,effective_from
1370 from ahl_mr_headers_b
1371 where mr_header_id=c_mr_header_id;
1372
1373 l_mr_header_rec GetMrHeaderDet%rowtype;
1374 l_return_status VARCHAR2(1);
1375
1376 BEGIN
1377 IF G_DEBUG='Y' THEN
1378 AHL_DEBUG_PUB.enable_debug;
1379 AHL_DEBUG_PUB.debug( '10010','+REVERT STATUS+');
1380 END IF;
1381
1382 l_return_Status:='S';
1383 IF funcmode = 'RUN' THEN
1384 l_next_status := wf_engine.getitemattrText(
1385 itemtype => itemtype
1386 ,itemkey => itemkey
1387 ,aname => 'ORG_STATUS_ID'
1388 );
1389
1390 l_object_version_number := wf_engine.getitemattrnumber(
1391 itemtype => itemtype
1392 ,itemkey => itemkey
1393 ,aname => 'OBJECT_VER'
1394 );
1395 l_object_id := wf_engine.getitemattrnumber(
1396 itemtype => itemtype
1397 ,itemkey => itemkey
1398 ,aname => 'OBJECT_ID'
1399 );
1400
1401 l_status_date := SYSDATE;
1402 -- Update approval object table as following
1403 -- FMPMR Code
1404
1405 UPDATE AHL_MR_HEADERS_B
1406 SET MR_STATUS_CODE = 'DRAFT',
1407 object_version_number =l_object_version_number+1
1408 WHERE mr_header_id = l_object_id
1409 and object_Version_number=l_object_version_number;
1410
1411 if (sql%notfound)
1412 then
1413 FND_MESSAGE.Set_Name('AHL','AHL_APRV_OBJ_CHANGED');
1417 return;
1414 FND_MSG_PUB.Add;
1415
1416 l_return_status := FND_API.G_RET_STS_ERROR;
1418
1419 end if;
1420 IF G_DEBUG='Y' THEN
1421 AHL_DEBUG_PUB.disable_debug;
1422 END IF;
1423
1424 COMMIT;
1425 resultout := 'COMPLETE:';
1426 RETURN;
1427 END IF;
1428
1429 -- CANCEL mode
1430 --
1431 IF (funcmode = 'CANCEL') THEN
1432 resultout := 'COMPLETE:';
1433 RETURN;
1434 END IF;
1435
1436 --
1437 -- TIMEOUT mode
1438 --
1439 IF (funcmode = 'TIMEOUT') THEN
1440 resultout := 'COMPLETE:';
1441 RETURN;
1442 END IF;
1443
1444
1445 EXCEPTION
1446 WHEN fnd_api.g_exc_error THEN
1447 FND_MSG_PUB.Count_And_Get (
1448 p_encoded => FND_API.G_FALSE,
1449 p_count => l_msg_count,
1450 p_data => l_msg_data
1451 );
1452 ahl_generic_aprv_pvt.Handle_Error
1453 (p_itemtype => itemtype ,
1454 p_itemkey => itemkey ,
1455 p_msg_count => l_msg_count, -- Number of error Messages
1456 p_msg_data => l_msg_data ,
1457 p_attr_name => 'ERROR_MSG',
1458 x_error_msg => l_error_msg
1459 ) ;
1460 wf_core.context('AHL_FMP_MR_APPROVAL_PVT','revert_status',
1461 itemtype,itemkey,actid,funcmode,l_error_msg);
1462 IF G_DEBUG='Y' THEN
1463 AHL_DEBUG_PUB.disable_debug;
1464 END IF;
1465 RAISE;
1466 WHEN OTHERS THEN
1467 wf_core.context(
1468 'AHL_FMP_MR_APPROVAL_PVT'
1469 ,'REVERT_STATUS'
1470 ,itemtype
1471 ,itemkey
1472 ,actid
1473 ,funcmode
1474 ,'Unexpected Error!'
1475 );
1476 IF G_DEBUG='Y' THEN
1477 AHL_DEBUG_PUB.disable_debug;
1478 END IF;
1479 RAISE;
1480
1481 END Revert_Status;
1482
1483 END AHL_FMP_MR_APPROVAL_PVT;