[Home] [Help]
PACKAGE BODY: APPS.AHL_ITEMGROUPS_APPROVAL_PVT
Source
1 PACKAGE BODY AHL_ITEMGROUPS_APPROVAL_PVT AS
2 /* $Header: AHLVIWFB.pls 120.0 2005/05/26 01:58:44 appldev noship $ */
3 --------------------------------------------------------------------------
4 -- PROCEDURE
5 -- Set_Activity_Details
6 --
7 -- PURPOSE
8 -- This procedure will set the workflow attributes for the details of the activity.
9 --
10 -- IN
11 -- itemtype - The internale name of the Item Type
12 -- itemkey - Unique key formulated in Start_WF_Process for WF internal reference
13 -- actid - The ID number of the activity from which this procedure is called.
14 -- funcmode - The execution mode of the activity
15 -- OUT
16 -- resultout - The expected result thats returned when the procedure comletes.
17 -- USED BY
18 -- Oracle CMRO Apporval
19 --
20 -- HISTORY
21 -- 04/23/2003 Senthil Kumar created
22 --------------------------------------------------------------------------
23
24
25 PROCEDURE Set_Activity_Details(
26 itemtype IN VARCHAR2
27 ,itemkey IN VARCHAR2
28 ,actid IN NUMBER
29 ,funcmode IN VARCHAR2
30 ,resultout OUT NOCOPY VARCHAR2)
31
32 AS
33
34 Cursor GetItemGroupDet(c_item_group_id Number) IS
35
36 Select
37 item_group_id ,
38 object_version_number,
39 name ,
40 status_code ,
41 type_code ,
42 source_item_group_id
43 FROM
44 ahl_item_groups_vl
45 WHERE
46 item_group_id =c_item_group_id;
47
48 l_item_group_rec GetItemGroupDet%ROWTYPE;
49
50 l_object_id NUMBER;
51 l_subject VARCHAR2(500);
52 l_return_status VARCHAR2(1);
53 l_msg_count NUMBER;
54 l_msg_data VARCHAR2(4000);
55 l_object_details ahl_generic_aprv_pvt.ObjRecTyp;
56 l_object VARCHAR2(30) := 'IGWF';
57 l_approval_type VARCHAR2(30) := 'CONCEPT';
58 l_approval_rule_id NUMBER;
59 l_approver_seq NUMBER;
60 l_error_msg VARCHAR2(2000);
61
62
63
64 BEGIN
65
66 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
67 THEN
68 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
69 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'Start SetActvityDetails');
70 END IF;
71 -- Debug info.
72
73 fnd_msg_pub.initialize;
74
75 l_return_status := fnd_api.g_ret_sts_success;
76
77 l_object_id := wf_engine.getitemattrnumber(
78 itemtype => itemtype
79 ,itemkey => itemkey
80 ,aname => 'OBJECT_ID'
81 );
82
83 l_object_details.operating_unit_id :=NULL;
84
85 l_object_details.priority :=NULL;
86
87 IF (funcmode = 'RUN') THEN
88
89 OPEN GetItemGroupDet(l_object_id);
90 FETCH GetItemGroupDet into l_item_group_rec;
91
92 IF GetItemGroupDet%NOTFOUND
93 THEN
94 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
95 fnd_message.set_token('ItemGroup_id',l_object_id,false);
96 l_subject := fnd_message.get;
97 ELSE
98 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
99 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
100 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
101 l_subject := fnd_message.get;
102 END IF;
103 CLOSE GetItemGroupDet;
104
105 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
106 THEN
107 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
108 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','ItemGroups Name'||l_item_group_rec.name);
109 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
110 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Subject:'||l_subject);
111
112 END IF;
113
114
115 wf_engine.setitemattrtext(
116 itemtype => itemtype
117 ,itemkey => itemkey
118 ,aname => 'FORWARD_SUBJECT'
119 ,avalue => l_subject);
120 wf_engine.setitemattrtext(
121 itemtype => itemtype
122 ,itemkey => itemkey
123 ,aname => 'APPROVAL_SUBJECT'
124 ,avalue => l_subject);
125
126
127 wf_engine.setitemattrtext(
128 itemtype => itemtype
129 ,itemkey => itemkey
130 ,aname => 'REJECT_SUBJECT'
131 ,avalue => l_subject);
132
133
134 wf_engine.setitemattrtext(
135 itemtype => itemtype
136 ,itemkey => itemkey
137 ,aname => 'APPROVED_SUBJECT'
138 ,avalue => l_subject);
139
140 wf_engine.setitemattrtext(
141 itemtype => itemtype
142 ,itemkey => itemkey
143 ,aname => 'APPROVED_SUBJECT'
144 ,avalue => l_subject);
145
146 wf_engine.setitemattrtext(
147 itemtype => itemtype
148 ,itemkey => itemkey
149 ,aname => 'FINAL_SUBJECT'
150 ,avalue => l_subject);
151
152 wf_engine.setitemattrtext(
153 itemtype => itemtype
154 ,itemkey => itemkey
155 ,aname => 'REMIND_SUBJECT'
156 ,avalue => l_subject);
157
158 wf_engine.setitemattrtext(
159 itemtype => itemtype
160 ,itemkey => itemkey
161 ,aname => 'ERROR_SUBJECT'
162 ,avalue => l_subject
163 );
164 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
165 THEN
166 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
167 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'l_subject'||l_subject);
168 END IF;
169
170
171 -----------------------------------------------------------------------------------
172 -- Get Approval Rule and First Approver Sequence
173 -----------------------------------------------------------------------------------
174
175 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
176 THEN
177 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
178 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'Before getting approval details'||l_subject);
179 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
180 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', '---l_object-->'||l_object);
181 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
182 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', '---l_approval_type-->'||l_approval_type);
183 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
184 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', '---l_approval_RULE_ID-->'||TO_CHAR(L_APPROVAL_RULE_ID));
185 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
186 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', '---L_APPROVER_SEQ-->'||TO_CHAR(L_APPROVER_SEQ));
187 END IF;
188 ahl_generic_aprv_pvt.get_approval_details(
189 p_object => l_object,
190 p_approval_type => l_approval_type,
191 p_object_details => l_object_details,
192 x_approval_rule_id => l_approval_rule_id,
193 x_approver_seq => l_approver_seq,
194 x_return_status => l_return_status
195 );
196
197
198 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
199 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
200 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','AfterGetApprovalDetails:'||l_return_status||'-'||l_subject);
201 END IF;
202
203 IF l_return_status = fnd_api.g_ret_sts_success THEN
204
205 wf_engine.setitemattrnumber(
206 itemtype => itemtype,
207 itemkey => itemkey,
208 aname => 'RULE_ID',
209 avalue => l_approval_rule_id
210 );
211
212 wf_engine.setitemattrnumber(
213 itemtype => itemtype,
214 itemkey => itemkey,
215 aname => 'APPROVER_SEQ',
216 avalue => l_approver_seq
217 );
218
219
220 resultout := 'COMPLETE:SUCCESS';
221
222 RETURN;
223
224 ELSE
225
226 RAISE fnd_api.G_EXC_ERROR;
227
228 END IF;
229 END IF;
230
231 --
232 -- CANCEL mode
233 --
234
235 IF (funcmode = 'CANCEL') THEN
236 resultout := 'COMPLETE:';
237 RETURN;
238 END IF;
239
240
241 --
242 -- TIMEOUT mode
243 --
244 IF (funcmode = 'TIMEOUT') THEN
245 resultout := 'COMPLETE:';
246 RETURN;
247 END IF;
248
249
250 EXCEPTION
251 WHEN fnd_api.G_EXC_ERROR THEN
252
253 FND_MSG_PUB.Count_And_Get (
254 p_encoded => FND_API.G_FALSE,
255 p_count => l_msg_count,
256 p_data => l_msg_data
257 );
258 ahl_generic_aprv_pvt.Handle_Error
259 (p_itemtype => itemtype ,
260 p_itemkey => itemkey ,
261 p_msg_count => l_msg_count, -- Number of error Messages
262 p_msg_data => l_msg_data ,
263 p_attr_name => 'ERROR_MSG',
264 x_error_msg => l_error_msg
265 ) ;
266 wf_core.context('AHL_ITEMGROUPS_APROVAL_PVT','Set_Activity_Details',
267 itemtype,itemkey,actid,funcmode,l_error_msg);
268
269 resultout := 'COMPLETE:ERROR';
270
271 WHEN OTHERS THEN
272 wf_core.context(
273 'AHL_ITEMGROUPS_APPROVAL_PVT'
274 ,'Set_Activity_Details'
275 ,itemtype
276 ,itemkey
277 ,actid
278 ,'Unexpected Error!'
279 );
280 RAISE;
281
282
283 END Set_Activity_Details;
284
285
286
287 PROCEDURE Ntf_Forward_FYI(
288 document_id IN VARCHAR2
289 ,display_type IN VARCHAR2
290 ,document IN OUT NOCOPY VARCHAR2
291 ,document_type IN OUT NOCOPY VARCHAR2
292 )
293
294 AS
295 Cursor GetItemGroupDet(c_item_group_id Number) IS
296
297 Select
298 item_group_id ,
299 object_version_number,
300 name ,
301 status_code ,
302 type_code ,
303 source_item_group_id
304 FROM
305 ahl_item_groups_vl
306 WHERE
307 item_group_id =c_item_group_id;
308
309 l_item_group_rec GetItemGroupDet%ROWTYPE;
310
311 l_object_id NUMBER;
312 l_subject VARCHAR2(500);
313 l_hyphen_pos1 NUMBER;
314 l_object VARCHAR2(30);
315 l_item_type VARCHAR2(30);
316 l_item_key VARCHAR2(30);
317 l_approver VARCHAR2(30);
318 l_body VARCHAR2(3500);
319 l_msg_count NUMBER;
320 l_msg_data VARCHAR2(4000);
321 l_error_msg VARCHAR2(2000);
322
323
324
325 BEGIN
326 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
327 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
328 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Start NtfForwardFyi');
329 END IF;
330
331 -- Debug info.
332
333
334 document_type := 'text/plain';
335
336 -- parse document_id for the ':' dividing item type name from item key value
337 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
338 -- release 2.5 version of this demo
339
340 l_hyphen_pos1 := INSTR(document_id, ':');
341 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
342 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
343
344 l_object := wf_engine.getitemattrtext(
345 itemtype => l_item_type
346 ,itemkey => l_item_key
347 ,aname => 'OBJECT_TYPE'
348 );
349
350 l_object_id := wf_engine.getitemattrNumber(
351 itemtype => l_item_type
352 ,itemkey => l_item_key
353 ,aname => 'OBJECT_ID'
354 );
355
356 l_approver := wf_engine.getitemattrtext(
357 itemtype => l_item_type
358 ,itemkey => l_item_key
359 ,aname => 'APPROVER'
360 );
361
362 OPEN GetItemGroupDet(l_object_id);
363 FETCH GetItemGroupDet into l_item_group_rec;
364
365 IF GetItemGroupDet%NOTFOUND
366 THEN
367 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
368 fnd_message.set_token('GROUP_ID',l_object_id,false);
369 l_body := fnd_message.get;
370 ELSE
371 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
372 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
373 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
374 l_body := fnd_message.get;
375 END IF;
376 CLOSE GetItemGroupDet;
377
378
379 /*--------------------------------------------------------------------------
380 -- Query approval object table for any detail information of this object
381 -- that will be used to replace tokens defined in FND Messages.
382 -- Here to simplify, we are using hard-coded messages.
383 ----------------------------------------------------------------------------*/
384
385 l_body := l_body||'.'||'Your request has been forwarded to ' ||l_approver||' for approval' ;
386 document := document || l_body;
387 RETURN;
388
389 EXCEPTION
390
391 WHEN FND_API.G_EXC_ERROR THEN
392 FND_MSG_PUB.Count_And_Get (
393 p_encoded => FND_API.G_FALSE,
394 p_count => l_msg_count,
395 p_data => l_msg_data
396 );
397 ahl_generic_aprv_pvt.Handle_Error
398 (p_itemtype => l_item_type ,
399 p_itemkey => l_item_key ,
400 p_msg_count => l_msg_count, -- Number of error Messages
401 p_msg_data => l_msg_data ,
402 p_attr_name => 'ERROR_MSG',
403 x_error_msg => l_error_msg
404 ) ;
405 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','ntf_forward_fyi',
406 l_item_type,l_item_key,l_error_msg);
407 RAISE;
408 WHEN OTHERS THEN
409 wf_core.context( 'AHLGAPP'
410 , 'Ntf_Forward_FYI'
411 , l_item_type
412 , l_item_key
413 );
414 RAISE;
415
416
417 END ntf_forward_fyi;
418
419 PROCEDURE Ntf_Approved_FYI(
420 document_id IN VARCHAR2
421 ,display_type IN VARCHAR2
422 ,document IN OUT NOCOPY VARCHAR2
423 ,document_type IN OUT NOCOPY VARCHAR2
424 )
425
426 AS
427
428 Cursor GetItemGroupDet(c_item_group_id Number) IS
429
430 Select
431 item_group_id ,
432 object_version_number,
433 name ,
434 status_code ,
435 type_code ,
436 source_item_group_id
437 FROM
438 ahl_item_groups_vl
439 WHERE
440 item_group_id =c_item_group_id;
441
442 l_item_group_rec GetItemGroupDet%ROWTYPE;
443
444 l_object_id NUMBER;
445 l_subject VARCHAR2(500);
446 l_hyphen_pos1 NUMBER;
447 l_object VARCHAR2(30);
448 l_item_type VARCHAR2(30);
449 l_item_key VARCHAR2(30);
450 l_approver VARCHAR2(30);
451 l_body VARCHAR2(3500);
452 l_msg_count NUMBER;
453 l_msg_data VARCHAR2(4000);
454 l_error_msg VARCHAR2(2000);
455
456 BEGIN
457 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
458 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
459 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Start NtfApproved Fyi');
460 END IF;
461
462 document_type := 'text/plain';
463
464 l_hyphen_pos1 := INSTR(document_id, ':');
465 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
466 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
467
468 l_object := wf_engine.getitemattrtext(
469 itemtype => l_item_type
470 ,itemkey => l_item_key
471 ,aname => 'OBJECT_TYPE'
472 );
473
474 l_object_id := wf_engine.getitemattrNumber(
475 itemtype => l_item_type
476 ,itemkey => l_item_key
477 ,aname => 'OBJECT_ID'
478 );
479
480 l_approver := wf_engine.getitemattrtext(
481 itemtype => l_item_type
482 ,itemkey => l_item_key
483 ,aname => 'APPROVER'
484 );
485
486 /*--------------------------------------------------------------------------
487 -- Query approval object table for any detail information of this object
488 -- that will be used to replace tokens defined in FND Messages.
489 -- Here to simplify, we are using hard-coded messages.
490 ----------------------------------------------------------------------------*/
491 OPEN GetItemGroupDet(l_object_id);
492 FETCH GetItemGroupDet into l_item_group_rec;
493
494 IF GetItemGroupDet%NOTFOUND
495 THEN
496 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
497 fnd_message.set_token('GROUP_ID',l_object_id,false);
498 l_body := fnd_message.get;
499 ELSE
500 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
501 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
502 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
503 l_body := fnd_message.get;
504 END IF;
505 CLOSE GetItemGroupDet;
506
507
508
509 l_body :=l_body||'.'|| 'Your request has been approved by ' ||l_approver ;
510
511 document := document || l_body;
512
513 RETURN;
514
515 EXCEPTION
516
517 WHEN FND_API.G_EXC_ERROR THEN
518 FND_MSG_PUB.Count_And_Get (
519 p_encoded => FND_API.G_FALSE,
520 p_count => l_msg_count,
521 p_data => l_msg_data
522 );
523 ahl_generic_aprv_pvt.Handle_Error
524 (p_itemtype => l_item_type ,
525 p_itemkey => l_item_key ,
526 p_msg_count => l_msg_count, -- Number of error Messages
527 p_msg_data => l_msg_data ,
528 p_attr_name => 'ERROR_MSG',
529 x_error_msg => l_error_msg
530 ) ;
531 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','Ntf_Approved_FYI',
532 l_item_type,l_item_key,l_error_msg);
533 RAISE;
534 WHEN OTHERS THEN
535 wf_core.context( 'AHLGAPP'
536 , 'Ntf_Approved_FYI'
537 , l_item_type
538 , l_item_key
539 );
540 RAISE;
541
542 END;
543
544 --------------------------------------------------------------------------
545 -- PROCEDURE
546 -- Ntf_Final_Approval_FYI
547 --
548 -- PURPOSE
549 -- Generate the FYI Document for display in messages, either text or html
550 --
551 -- IN
552 -- document_id - Item Key
553 -- display_type - either 'text/plain' or 'text/html'
554 -- document - document buffer
555 -- document_type - type of document buffer created, either 'text/plain'
556 -- or 'text/html'
557 -- OUT
558 --
559 -- USED BY
560 -- Oracle CMRO Apporval
561 --
562 -- HISTORY
563 -- 04/23/2003 Senthil Kumar created
564 --------------------------------------------------------------------------
565 PROCEDURE Ntf_Final_Approval_FYI(
566 document_id IN VARCHAR2
567 ,display_type IN VARCHAR2
568 ,document IN OUT NOCOPY VARCHAR2
569 ,document_type IN OUT NOCOPY VARCHAR2
570 )
571
572 AS
573
574 Cursor GetItemGroupDet(c_item_group_id Number) IS
575
576 Select
577 item_group_id ,
578 object_version_number,
579 name ,
580 status_code ,
581 type_code ,
582 source_item_group_id
583 FROM
584 ahl_item_groups_vl
585 WHERE
586 item_group_id =c_item_group_id;
587
588 l_hyphen_pos1 NUMBER;
589 l_object VARCHAR2(30);
590 l_item_type VARCHAR2(30);
591 l_item_key VARCHAR2(30);
592 l_body VARCHAR2(3500);
593 l_object_id NUMBER;
594 l_msg_count NUMBER;
595 l_msg_data VARCHAR2(4000);
596 l_error_msg VARCHAR2(2000);
597
598
599 l_item_group_rec GetItemGroupDet%ROWTYPE;
600
601
602 BEGIN
603
604 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
605 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
606 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','NtfyFinalApprovalFyi');
607 END IF;
608
609
610 document_type := 'text/plain';
611
612 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
613 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
614 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Notify Final approval;');
615 END IF;
616 -- parse document_id for the ':' dividing item type name from item key value
617 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
618 -- release 2.5 version of this demo
619
620 l_hyphen_pos1 := INSTR(document_id, ':');
621 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
622 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
623
624 l_object := wf_engine.getitemattrtext(
625 itemtype => l_item_type
626 ,itemkey => l_item_key
627 ,aname => 'OBJECT_TYPE'
628 );
629
630 l_object_id := wf_engine.getitemattrNumber(
631 itemtype => l_item_type
632 ,itemkey => l_item_key
633 ,aname => 'OBJECT_ID'
634 );
635
636
637 /*--------------------------------------------------------------------------
638 -- Query approval object table for any detail information of this object
639 -- that will be used to replace tokens defined in FND Messages.
640 -- Here to simplify, we are using hard-coded messages.
641 ----------------------------------------------------------------------------*/
642
643 OPEN GetItemGroupDet(l_object_id);
644 FETCH GetItemGroupDet into l_item_group_rec;
645
646 IF GetItemGroupDet%NOTFOUND
647 THEN
648 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
649 fnd_message.set_token('GROUP_ID',l_object_id,false);
650 l_body := fnd_message.get;
651 ELSE
652 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
653 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
654 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
655 l_body := fnd_message.get;
656 END IF;
657 CLOSE GetItemGroupDet;
658
659
660 l_body :=l_body||'.'|| 'Your request has been approved by all approvers.';
661
662 document := document || l_body;
663
664 RETURN;
665
666 EXCEPTION
667 WHEN FND_API.G_EXC_ERROR THEN
668 FND_MSG_PUB.Count_And_Get (
669 p_encoded => FND_API.G_FALSE,
670 p_count => l_msg_count,
671 p_data => l_msg_data
672 );
673 ahl_generic_aprv_pvt.Handle_Error
674 (p_itemtype => l_item_type ,
675 p_itemkey => l_item_key ,
676 p_msg_count => l_msg_count,
677 p_msg_data => l_msg_data ,
678 p_attr_name => 'ERROR_MSG',
679 x_error_msg => l_error_msg
680 ) ;
681 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','Ntf_Final_Approval_FYI',
682 l_item_type,l_item_key,l_error_msg);
683 RAISE;
684 WHEN OTHERS THEN
685 wf_core.context( 'AHLGAPP'
686 , 'Ntf_Final_Approval_FYI'
687 , l_item_type
688 , l_item_key
689 );
690 RAISE;
691
692 END;
693 --------------------------------------------------------------------------
694 -- PROCEDURE
695 -- Ntf_Rejected_FYI
696 --
697 -- PURPOSE
698 -- Generate the FYI Document for display in messages, either text or html
699 --
700 -- IN
701 -- document_id - Item Key
702 -- display_type - either 'text/plain' or 'text/html'
703 -- document - document buffer
704 -- document_type - type of document buffer created, either 'text/plain'
705 -- or 'text/html'
706 -- OUT
707 --
708 -- USED BY
709 -- Oracle CMRO Apporval
710 --
711 -- HISTORY
712 -- 04/23/2003 Senthil Kumar created
713 --------------------------------------------------------------------------
714 PROCEDURE Ntf_Rejected_FYI(
715 document_id IN VARCHAR2
716 ,display_type IN VARCHAR2
717 ,document IN OUT NOCOPY VARCHAR2
718 ,document_type IN OUT NOCOPY VARCHAR2
719 )
720 AS
721 Cursor GetItemGroupDet(c_item_group_id Number) IS
722
723 Select
724 item_group_id ,
725 object_version_number,
726 name ,
727 status_code ,
728 type_code ,
729 source_item_group_id
730 FROM
731 ahl_item_groups_vl
732 WHERE
733 item_group_id =c_item_group_id;
734
735 l_item_group_rec GetItemGroupDet%ROWTYPE;
736
737
738 l_hyphen_pos1 NUMBER;
739 l_object VARCHAR2(30);
740 l_item_type VARCHAR2(30);
741 l_item_key VARCHAR2(30);
742 l_approver VARCHAR2(30);
743 l_body VARCHAR2(3500);
744 l_object_id NUMBER;
745 l_msg_count NUMBER;
746 l_msg_data VARCHAR2(4000);
747 l_error_msg VARCHAR2(2000);
748
749
750 BEGIN
751 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
752 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
753 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'Start NtfyRejectedFYi');
754 END IF;
755
756
757 document_type := 'text/plain';
758
759 -- parse document_id for the ':' dividing item type name from item key value
760 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
761 -- release 2.5 version of this demo
762
763 l_hyphen_pos1 := INSTR(document_id, ':');
764 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
765 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
766
767 l_object := wf_engine.getitemattrtext(
768 itemtype => l_item_type
769 ,itemkey => l_item_key
770 ,aname => 'OBJECT_TYPE'
771 );
772
773 l_object_id := wf_engine.getitemattrNumber(
774 itemtype => l_item_type
775 ,itemkey => l_item_key
776 ,aname => 'OBJECT_ID'
777 );
778
779 l_approver := wf_engine.getitemattrtext(
780 itemtype => l_item_type
781 ,itemkey => l_item_key
782 ,aname => 'APPROVER'
783 );
784
785 /*--------------------------------------------------------------------------
786 -- Query approval object table for any detail information of this object
787 -- that will be used to replace tokens defined in FND Messages.
788 -- Here to simplify, we are using hard-coded messages.
789 ----------------------------------------------------------------------------*/
790 OPEN GetItemGroupDet(l_object_id);
791 FETCH GetItemGroupDet into l_item_group_rec;
792
793 IF GetItemGroupDet%NOTFOUND
794 THEN
795 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
796 fnd_message.set_token('GROUP_ID',l_object_id,false);
797 l_body := fnd_message.get;
798 ELSE
799 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
800 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
801 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
802 l_body := fnd_message.get;
803 END IF;
804 CLOSE GetItemGroupDet;
805
806
807 l_body := l_body||'.'||'Your request has been rejected by ' ||l_approver ;
808
809 document := document || l_body;
810
811 RETURN;
812
813 EXCEPTION
814 WHEN FND_API.G_EXC_ERROR THEN
815 FND_MSG_PUB.Count_And_Get (
816 p_encoded => FND_API.G_FALSE,
817 p_count => l_msg_count,
818 p_data => l_msg_data
819 );
820 ahl_generic_aprv_pvt.Handle_Error
821 (p_itemtype => l_item_type ,
822 p_itemkey => l_item_key ,
823 p_msg_count => l_msg_count, -- Number of error Messages
824 p_msg_data => l_msg_data ,
825 p_attr_name => 'ERROR_MSG',
826 x_error_msg => l_error_msg
827 ) ;
828 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','Ntf_Rejected_FYI',
829 l_item_type,l_item_key,l_error_msg);
830 RAISE;
831
832 WHEN OTHERS THEN
833 wf_core.context( 'AHLGAPP'
834 , 'Ntf_Rejected_FYI'
835 , l_item_type
836 , l_item_key
837 );
838 RAISE;
839
840 END Ntf_Rejected_FYI;
841
842
843 --------------------------------------------------------------------------
844 -- PROCEDURE
845 -- Ntf_Approval
846 --
847 -- PURPOSE
848 -- Generate the Document to ask for approval, either text or html
849 --
850 -- IN
851 -- document_id - Item Key
852 -- display_type - either 'text/plain' or 'text/html'
853 -- document - document buffer
854 -- document_type - type of document buffer created, either 'text/plain'
855 -- or 'text/html'
856 -- OUT
857 --
858 -- USED BY
859 -- Oracle CMRO Apporval
860 --
861 -- HISTORY
862 -- 04/23/2003 Senthil Kumar created
863 --------------------------------------------------------------------------
864 PROCEDURE Ntf_Approval(
865 document_id IN VARCHAR2
866 ,display_type IN VARCHAR2
867 ,document IN OUT NOCOPY VARCHAR2
868 ,document_type IN OUT NOCOPY VARCHAR2
869 )
870
871 AS
872
873 Cursor GetItemGroupDet(c_item_group_id Number) IS
874
875 Select
876 item_group_id ,
877 object_version_number,
878 name ,
879 status_code ,
880 type_code ,
881 source_item_group_id
882 FROM
883 ahl_item_groups_vl
884 WHERE
885 item_group_id =c_item_group_id;
886
887 l_item_group_rec GetItemGroupDet%ROWTYPE;
888
889 l_hyphen_pos1 NUMBER;
890 l_object VARCHAR2(30);
891 l_item_type VARCHAR2(30);
892 l_item_key VARCHAR2(30);
893 l_requester VARCHAR2(30);
894 l_requester_note VARCHAR2(4000);
895 l_body VARCHAR2(5000);
896 l_object_id NUMBER;
897 l_msg_count NUMBER;
898 l_msg_data VARCHAR2(4000);
899 l_error_msg VARCHAR2(2000);
900
901 BEGIN
902 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
903 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
904 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Start NtfyApproval');
905 END IF;
906
907 document_type := 'text/plain';
908
909 l_hyphen_pos1 := INSTR(document_id, ':');
910 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
911 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
912
913 l_object := wf_engine.getitemattrtext(
914 itemtype => l_item_type
915 ,itemkey => l_item_key
916 ,aname => 'OBJECT_TYPE'
917 );
918
919 l_object_id := wf_engine.getitemattrNumber(
920 itemtype => l_item_type
921 ,itemkey => l_item_key
922 ,aname => 'OBJECT_ID'
923 );
924
925 l_requester := wf_engine.getitemattrtext(
926 itemtype => l_item_type
927 ,itemkey => l_item_key
928 ,aname => 'REQUESTER'
929 );
930
931 l_requester_note := wf_engine.getitemattrtext(
932 itemtype => l_item_type
933 ,itemkey => l_item_key
934 ,aname => 'REQUESTER_NOTE'
935 );
936
937
938 commit;
939 /*--------------------------------------------------------------------------
940 -- Query approval object table for any detail information of this object
941 -- that will be used to replace tokens defined in FND Messages.
942 -- Here to simplify, we are using hard-coded messages.
943 ----------------------------------------------------------------------------*/
944
945 OPEN GetItemGroupDet(l_object_id);
946 FETCH GetItemGroupDet into l_item_group_rec;
947
948 IF GetItemGroupDet%NOTFOUND
949 THEN
950 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
951 fnd_message.set_token('GROUP_ID',l_object_id,false);
952 l_body := fnd_message.get;
953 ELSE
954 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
955 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
956 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
957 l_body := fnd_message.get;
958 END IF;
959 CLOSE GetItemGroupDet;
960
961
962 l_body :=l_body||'.'|| 'You just received a request from '||l_requester;
963 l_body := l_body ||', the note from him/her is as following: '||l_requester_note;
964
965 document := document || l_body;
966
967 RETURN;
968
969 EXCEPTION
970 WHEN FND_API.G_EXC_ERROR THEN
971 FND_MSG_PUB.Count_And_Get (
972 p_encoded => FND_API.G_FALSE,
973 p_count => l_msg_count,
974 p_data => l_msg_data
975 );
976 ahl_generic_aprv_pvt.Handle_Error
977 (p_itemtype => l_item_type ,
978 p_itemkey => l_item_key ,
979 p_msg_count => l_msg_count, -- Number of error Messages
980 p_msg_data => l_msg_data ,
981 p_attr_name => 'ERROR_MSG',
982 x_error_msg => l_error_msg
983 ) ;
984 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','Ntf_Approval',
985 l_item_type,l_item_key,l_error_msg);
986 RAISE;
987 WHEN OTHERS THEN
988 wf_core.context( 'AHLGAPP'
989 , 'Ntf_Approval'
990 , l_item_type
991 , l_item_key
992 );
993 RAISE;
994
995 END Ntf_Approval;
996 --------------------------------------------------------------------------
997 -- PROCEDURE
998 -- Ntf_Approval_Reminder
999 --
1000 -- PURPOSE
1001 -- Generate the Reminder Document for display in messages, either text or html
1002 --
1003 -- IN
1004 -- document_id - Item Key
1005 -- display_type - either 'text/plain' or 'text/html'
1006 -- document - document buffer
1007 -- document_type - type of document buffer created, either 'text/plain'
1008 -- or 'text/html'
1009 -- OUT
1010 --
1011 -- USED BY
1012 -- Oracle CMRO Apporval
1013 --
1014 -- HISTORY
1015 -- 04/23/2003 Senthil Kumar created
1016 --------------------------------------------------------------------------
1017 PROCEDURE Ntf_Approval_Reminder(
1018 document_id IN VARCHAR2
1019 ,display_type IN VARCHAR2
1020 ,document IN OUT NOCOPY VARCHAR2
1021 ,document_type IN OUT NOCOPY VARCHAR2
1022 )
1023
1024 AS
1025
1026 Cursor GetItemGroupDet(c_item_group_id Number) IS
1027
1028 Select
1029 item_group_id ,
1030 object_version_number,
1031 name ,
1032 status_code ,
1033 type_code ,
1034 source_item_group_id
1035 FROM
1036 ahl_item_groups_vl
1037 WHERE
1038 item_group_id =c_item_group_id;
1039
1040 l_item_group_rec GetItemGroupDet%ROWTYPE;
1041
1042 l_hyphen_pos1 NUMBER;
1043 l_object VARCHAR2(30);
1044 l_item_type VARCHAR2(30);
1045 l_item_key VARCHAR2(30);
1046 l_requester VARCHAR2(30);
1047 l_requester_note VARCHAR2(4000);
1048 l_body VARCHAR2(5000);
1049 l_object_id NUMBER;
1050 l_msg_count NUMBER;
1051 l_msg_data VARCHAR2(4000);
1052 l_error_msg VARCHAR2(2000);
1053
1054 BEGIN
1055 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1056 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1057 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Start NtfyApprovalRemainder');
1058 END IF;
1059
1060
1061 document_type := 'text/plain';
1062
1063 -- parse document_id for the ':' dividing item type name from item key value
1064 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1065 -- release 2.5 version of this demo
1066
1067 l_hyphen_pos1 := INSTR(document_id, ':');
1068 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1069 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1070
1071 l_object := wf_engine.getitemattrtext(
1072 itemtype => l_item_type
1073 ,itemkey => l_item_key
1074 ,aname => 'OBJECT_TYPE'
1075 );
1076
1077 l_object_id := wf_engine.getitemattrNumber(
1078 itemtype => l_item_type
1079 ,itemkey => l_item_key
1080 ,aname => 'OBJECT_ID'
1081 );
1082
1083 l_requester := wf_engine.getitemattrtext(
1084 itemtype => l_item_type
1085 ,itemkey => l_item_key
1086 ,aname => 'REQUESTER'
1087 );
1088
1089 l_requester_note := wf_engine.getitemattrtext(
1090 itemtype => l_item_type
1091 ,itemkey => l_item_key
1092 ,aname => 'REQUESTER_NOTE'
1093 );
1094
1095
1096 /*--------------------------------------------------------------------------
1097 -- Query approval object table for any detail information of this object
1098 -- that will be used to replace tokens defined in FND Messages.
1099 -- Here to simplify, we are using hard-coded messages.
1100 ----------------------------------------------------------------------------*/
1101 OPEN GetItemGroupDet(l_object_id);
1102 FETCH GetItemGroupDet into l_item_group_rec;
1103
1104 IF GetItemGroupDet%NOTFOUND
1105 THEN
1106 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
1107 fnd_message.set_token('GROUP_ID',l_object_id,false);
1108 l_body := fnd_message.get;
1109 ELSE
1110 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
1111 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
1112 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
1113 l_body := fnd_message.get;
1114 END IF;
1115 CLOSE GetItemGroupDet;
1116
1117
1118 l_body :=l_body||'.'|| 'Reminder: You just received a request from '||l_requester;
1119 l_body := l_body ||'. The note from him/her is as following: '||l_requester_note;
1120
1121 document := document || l_body;
1122
1123 RETURN;
1124
1125 EXCEPTION
1126 WHEN FND_API.G_EXC_ERROR THEN
1127 FND_MSG_PUB.Count_And_Get (
1128 p_encoded => FND_API.G_FALSE,
1129 p_count => l_msg_count,
1130 p_data => l_msg_data
1131 );
1132 ahl_generic_aprv_pvt.Handle_Error
1133 (p_itemtype => l_item_type ,
1134 p_itemkey => l_item_key ,
1135 p_msg_count => l_msg_count, -- Number of error Messages
1136 p_msg_data => l_msg_data ,
1137 p_attr_name => 'ERROR_MSG',
1138 x_error_msg => l_error_msg
1139 ) ;
1140 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','Ntf_Approval_Reminder',
1141 l_item_type,l_item_key,l_error_msg);
1142 RAISE;
1143 WHEN OTHERS THEN
1144 wf_core.context( 'AHLGAPP'
1145 , 'Ntf_Approval_Reminder'
1146 , l_item_type
1147 , l_item_key
1148 );
1149 RAISE;
1150 END Ntf_Approval_Reminder;
1151
1152
1153
1154 --------------------------------------------------------------------------
1155 -- PROCEDURE
1156 -- Ntf_Error_Act
1157 --
1158 -- PURPOSE
1159 -- Generate the Document to request action to handle error, either text or html
1160 --
1161 -- IN
1162 -- document_id - Item Key
1163 -- display_type - either 'text/plain' or 'text/html'
1164 -- document - document buffer
1165 -- document_type - type of document buffer created, either 'text/plain'
1166 -- or 'text/html'
1167 -- OUT
1168 --
1169 -- USED BY
1170 -- Oracle CMRO Apporval
1171 --
1172 -- HISTORY
1173 -- 04/23/2003 Senthil Kumar created
1174 --------------------------------------------------------------------------
1175 PROCEDURE Ntf_Error_Act(
1176 document_id IN VARCHAR2
1177 ,display_type IN VARCHAR2
1178 ,document IN OUT NOCOPY VARCHAR2
1179 ,document_type IN OUT NOCOPY VARCHAR2
1180 )
1181
1182 AS
1183
1184 Cursor GetItemGroupDet(c_item_group_id Number) IS
1185
1186 Select
1187 item_group_id ,
1188 object_version_number,
1189 name ,
1190 status_code ,
1191 type_code ,
1192 source_item_group_id
1193 FROM
1194 ahl_item_groups_vl
1195 WHERE
1196 item_group_id =c_item_group_id;
1197
1198 l_item_group_rec GetItemGroupDet%ROWTYPE;
1199
1200 l_hyphen_pos1 NUMBER;
1201 l_object VARCHAR2(30);
1202 l_item_type VARCHAR2(30);
1203 l_item_key VARCHAR2(30);
1204 l_body VARCHAR2(3500);
1205 l_object_id NUMBER;
1206 l_error_msg VARCHAR2(4000);
1207 l_msg_count NUMBER;
1208 l_msg_data VARCHAR2(4000);
1209
1210
1211 BEGIN
1212 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1213 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1214 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'NtfyErrorAct');
1215 END IF;
1216
1217
1218 document_type := 'text/plain';
1219
1220 -- parse document_id for the ':' dividing item type name from item key value
1221 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1222 -- release 2.5 version of this demo
1223
1224 l_hyphen_pos1 := INSTR(document_id, ':');
1225 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1226 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1227
1228 l_object := wf_engine.getitemattrtext(
1229 itemtype => l_item_type
1230 ,itemkey => l_item_key
1231 ,aname => 'OBJECT_TYPE'
1232 );
1233
1234 l_object_id := wf_engine.getitemattrNumber(
1235 itemtype => l_item_type
1236 ,itemkey => l_item_key
1237 ,aname => 'OBJECT_ID'
1238 );
1239
1240 l_error_msg := wf_engine.getitemattrText(
1241 itemtype => l_item_type
1242 ,itemkey => l_item_key
1243 ,aname => 'ERROR_MSG'
1244 );
1245
1246 /*--------------------------------------------------------------------------
1247 -- Query approval object table for any detail information of this object
1248 -- that will be used to replace tokens defined in FND Messages.
1249 -- Here to simplify, we are using hard-coded messages.
1250 ----------------------------------------------------------------------------*/
1251
1252 OPEN GetItemGroupDet(l_object_id);
1253 FETCH GetItemGroupDet into l_item_group_rec;
1254
1255 IF GetItemGroupDet%NOTFOUND
1256 THEN
1257 fnd_message.set_name('AHL', 'AHL_ITEMGROUPS_ID_INVALID');
1258 fnd_message.set_token('GROUP_ID',l_object_id,false);
1259 l_body := fnd_message.get;
1260 ELSE
1261 fnd_message.set_name('AHL', 'AHL_MC_NTF_ALT_PRT_FWD_SUB');
1262 fnd_message.set_token('GROUPNAME',l_item_group_rec.name ,false);
1263 fnd_message.set_token('GROUP_ID',l_item_group_rec.item_group_id, false);
1264 l_body := fnd_message.get;
1265 END IF;
1266 CLOSE GetItemGroupDet;
1267
1268
1269
1270 l_body :=l_body||'.'|| 'An error occured in the approval process of your request.'||fnd_global.local_chr(10);
1271 l_body := l_body || 'Please choose to cancel or re-submit your request.'||fnd_global.local_chr(10);
1272 l_body := l_body || 'Error Message'||l_error_msg;
1273
1274 document := document || l_body;
1275
1276 RETURN;
1277
1278 EXCEPTION
1279 WHEN FND_API.G_EXC_ERROR THEN
1280 FND_MSG_PUB.Count_And_Get (
1281 p_encoded => FND_API.G_FALSE,
1282 p_count => l_msg_count,
1283 p_data => l_msg_data
1284 );
1285 ahl_generic_aprv_pvt.Handle_Error
1286 (p_itemtype => l_item_type ,
1287 p_itemkey => l_item_key ,
1288 p_msg_count => l_msg_count, -- Number of error Messages
1289 p_msg_data => l_msg_data ,
1290 p_attr_name => 'ERROR_MSG',
1291 x_error_msg => l_error_msg
1292 ) ;
1293 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','Ntf_Error_Act',
1294 l_item_type,l_item_key,l_error_msg);
1295 RAISE;
1296 WHEN OTHERS THEN
1297 wf_core.context( 'AHL_ITEMGROUPS_APPROVAL_PVT'
1298 , 'Ntf_Error_Act'
1299 , l_item_type
1300 , l_item_key
1301 );
1302 RAISE;
1303 END Ntf_Error_Act;
1304
1305 ---------------------------------------------------------------------
1306 -- PROCEDURE
1307 -- Update_Status
1308 --
1309 -- PURPOSE
1310 -- This Procedure will update the status
1311 --
1312 -- IN
1313 --
1314 -- OUT
1315 --
1316 -- USED BY
1317 -- Oracle CMRO Apporval
1318 --
1319 -- HISTORY
1320 -- 04/23/2003 Senthil Kumar created
1321 --------------------------------------------------------------------------
1322 PROCEDURE Update_Status(
1323 itemtype IN VARCHAR2
1324 ,itemkey IN VARCHAR2
1325 ,actid IN NUMBER
1326 ,funcmode IN VARCHAR2
1327 ,resultout OUT NOCOPY VARCHAR2
1328 )
1329
1330 AS
1331 Cursor GetItemGroupDet(c_item_group_id Number) IS
1332
1333 Select
1334 item_group_id ,
1335 object_version_number,
1336 name ,
1337 status_code ,
1338 type_code ,
1339 source_item_group_id
1340 FROM
1341 ahl_item_groups_vl
1342 WHERE
1343 item_group_id =c_item_group_id;
1344
1345 l_item_group_rec GetItemGroupDet%ROWTYPE;
1346
1347 l_object_id NUMBER;
1348 l_approval_status VARCHAR2(30);
1349 l_error_msg VARCHAR2(4000);
1350 l_msg_count NUMBER;
1351 l_msg_data VARCHAR2(4000);
1352 l_next_status VARCHAR2(30);
1353 l_object_version_number NUMBER;
1354 l_status_date DATE;
1355 l_return_status VARCHAR2(1);
1356 l_api_version NUMBER := 1.0;
1357 l_init_msg_list BOOLEAN := false;
1358 l_commit BOOLEAN :=false;
1359
1360
1361 BEGIN
1362 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1363 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1364 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'Start UpdateStatus');
1365 END IF;
1366
1367 IF funcmode = 'RUN' THEN
1368 l_approval_status := wf_engine.getitemattrtext(
1369 itemtype => itemtype
1370 ,itemkey => itemkey
1371 ,aname => 'UPDATE_GEN_STATUS'
1372 );
1373 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1374 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1375 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'After GetItemAttrText UpdateStatus');
1376 END IF;
1377
1378 IF l_approval_status = 'APPROVED' THEN
1379 l_next_status := wf_engine.getitemattrText(
1380 itemtype => itemtype
1381 ,itemkey => itemkey
1382 ,aname => 'NEW_STATUS_ID'
1383 );
1384
1385 ELSE
1386 l_next_status := wf_engine.getitemattrText(
1387 itemtype => itemtype
1388 ,itemkey => itemkey
1389 ,aname => 'REJECT_STATUS_ID'
1390 );
1391 END IF;
1392
1393 l_object_version_number := wf_engine.getitemattrnumber(
1394 itemtype => itemtype
1395 ,itemkey => itemkey
1396 ,aname => 'OBJECT_VER'
1397 );
1398 l_object_id := wf_engine.getitemattrnumber(
1399 itemtype => itemtype
1400 ,itemkey => itemkey
1401 ,aname => 'OBJECT_ID'
1402 );
1403
1404 l_status_date := SYSDATE;
1405
1406 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1407 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1408 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','l_object_id:'||to_char(l_object_id));
1409 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1410 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','l_approvalStatus:'||l_approval_status);
1411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1412 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'Object version id check :'||to_char(l_object_id));
1413 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1414 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'l_approval_status:'||l_approval_status);
1415 END IF;
1416
1417 OPEN GetItemGroupDet(l_object_id);
1418 FETCH GetItemGroupDet into l_item_group_rec;
1419 CLOSE GetItemGroupDet;
1420
1421
1422 /*
1423 p_api_version IN NUMBER,
1424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1427 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
1428 P_MODULE_TYPE IN VARCHAR2,
1429 x_return_status OUT NOCOPY VARCHAR2,
1430 x_msg_count OUT NOCOPY NUMBER,
1431 x_msg_data OUT NOCOPY VARCHAR2,
1432 p_appr_status IN VARCHAR2,
1433 p_ItemGroups_id IN NUMBER,
1434 p_object_version_number IN NUMBER);
1435 */
1436
1437 AHL_MC_ITEMGROUP_PVT.Approve_ItemGroups
1438 (
1439 p_api_version =>l_api_version,
1440 -- p_init_msg_list =>l_init_msg_list,
1441 -- p_commit =>l_commit,
1442 -- p_validation_level =>NULL ,
1443 -- p_default =>NULL ,
1444 p_module_type =>'JSP',
1445 x_return_status =>l_return_status,
1446 x_msg_count =>l_msg_count ,
1447 x_msg_data =>l_msg_data ,
1448 p_appr_status =>l_approval_status,
1449 p_ItemGroups_id =>l_object_id,
1450 p_object_version_number =>l_object_version_number
1451 );
1452 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1453 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1454 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','After Complete Item Group Revision:L_ApprovalStatus'||l_approval_status);
1455 END IF;
1456
1457 if (sql%notfound)
1458 then
1459 FND_MESSAGE.Set_Name('AHL','AHL_APRV_OBJ_CHANGED');
1460 FND_MSG_PUB.Add;
1461 l_return_status := FND_API.G_RET_STS_ERROR;
1462 End if;
1463
1464 IF l_return_Status=fnd_api.g_ret_sts_success
1465 THEN
1466 COMMIT;
1467 ELSE
1468 ROLLBACK;
1469 END IF;
1470
1471 resultout := 'COMPLETE:';
1472 RETURN;
1473 END IF;
1474
1475 -- CANCEL mode
1476 --
1477 IF (funcmode = 'CANCEL') THEN
1478 resultout := 'COMPLETE:';
1479 RETURN;
1480 END IF;
1481
1482 --
1483 -- TIMEOUT mode
1484 --
1485 IF (funcmode = 'TIMEOUT') THEN
1486 resultout := 'COMPLETE:';
1487 RETURN;
1488 END IF;
1489
1490
1491 EXCEPTION
1492 WHEN fnd_api.g_exc_error THEN
1493 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1494 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1495 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details','Error G_exec UpdateSatus:'||sqlerrm);
1496 END IF;
1497 FND_MSG_PUB.Count_And_Get (
1498 p_encoded => FND_API.G_FALSE,
1499 p_count => l_msg_count,
1500 p_data => l_msg_data
1501 );
1502 ahl_generic_aprv_pvt.Handle_Error
1503 (p_itemtype => itemtype ,
1504 p_itemkey => itemkey ,
1505 p_msg_count => l_msg_count, -- Number of error Messages
1506 p_msg_data => l_msg_data ,
1507 p_attr_name => 'ERROR_MSG',
1508 x_error_msg => l_error_msg
1509 ) ;
1510 wf_core.context('AHL_FMP_APRV_PVT','UPDATE_STATUS',
1511 itemtype,itemkey,actid,funcmode,l_error_msg);
1512 RAISE;
1513
1514 WHEN OTHERS THEN
1515 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1516 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1517 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'UpdateStatus Whenothers Err:'||sqlerrm);
1518
1519 END IF;
1520
1521 wf_core.context(
1522 'AHL_ITEMGROUPS_APPROVAL_PVT'
1523 ,'Update_Status'
1524 ,itemtype
1525 ,itemkey
1526 ,actid
1527 ,funcmode
1528 ,'Unexpected Error!'
1529 );
1530 RAISE;
1531
1532 END Update_Status;
1533 ---------------------------------------------------------------------
1534 -- PROCEDURE
1535 -- Revert_Status
1536 --
1537 -- PURPOSE
1538 -- This Procedure will revert the status in the case of an error
1539 --
1540 -- IN
1541 --
1542 -- OUT
1543 --
1544 -- USED BY
1545 -- Oracle CMRO Apporval
1546 --
1547 -- HISTORY
1548 -- 04/23/2003 Senthil Kumar created
1549 --------------------------------------------------------------------------
1550 PROCEDURE Revert_Status(
1551 itemtype IN VARCHAR2
1552 ,itemkey IN VARCHAR2
1553 ,actid IN NUMBER
1554 ,funcmode IN VARCHAR2
1555 ,resultout OUT NOCOPY VARCHAR2
1556 )
1557
1558 AS
1559
1560 l_error_msg VARCHAR2(4000);
1561 l_next_status VARCHAR2(30);
1562 l_approval_status VARCHAR2(30);
1563 l_object_version_number NUMBER;
1564 l_object_id NUMBER;
1565 l_status_date DATE;
1566 l_msg_count NUMBER;
1567 l_msg_data VARCHAR2(4000);
1568 l_return_status VARCHAR2(1);
1569
1570 Cursor GetItemGroupDet(c_item_group_id Number) IS
1571
1572 Select
1573 item_group_id ,
1574 object_version_number,
1575 name ,
1576 status_code ,
1577 type_code ,
1578 source_item_group_id
1579 FROM
1580 ahl_item_groups_vl
1581 WHERE
1582 item_group_id =c_item_group_id;
1583
1584 l_item_group_rec GetItemGroupDet%ROWTYPE;
1585
1586 BEGIN
1587 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1588 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1589 'AHL_ITEMGROUPS_APPROVAL_PVT.Set_Activity_Details', 'Start RevertStatus');
1590 END IF;
1591
1592 l_return_Status:='S';
1593 IF funcmode = 'RUN' THEN
1594 l_next_status := wf_engine.getitemattrText(
1595 itemtype => itemtype
1596 ,itemkey => itemkey
1597 ,aname => 'ORG_STATUS_ID'
1598 );
1599
1600 l_object_version_number := wf_engine.getitemattrnumber(
1601 itemtype => itemtype
1602 ,itemkey => itemkey
1603 ,aname => 'OBJECT_VER'
1604 );
1605 l_object_id := wf_engine.getitemattrnumber(
1606 itemtype => itemtype
1607 ,itemkey => itemkey
1608 ,aname => 'OBJECT_ID'
1609 );
1610
1611 l_status_date := SYSDATE;
1612 -- Update approval object table as following
1613
1614 OPEN GetItemGroupDet(l_object_id);
1615 FETCH GetItemGroupDet into l_item_group_rec;
1616 CLOSE GetItemGroupDet;
1617
1618
1619 if l_item_group_rec.STATUS_CODE ='APPROVAL_PENDING'
1620 THEN
1621 UPDATE AHL_ITEM_GROUPS_B
1622 SET STATUS_CODE = 'DRAFT',
1623 object_version_number =l_object_version_number+1
1624 WHERE ITEM_GROUP_ID = l_object_id
1625 and object_Version_number=l_object_version_number;
1626 /* ELSE
1627 UPDATE AHL_ITEM_GROUPS_B
1628 SET STATUS = 'COMPLETE',
1629 object_version_number =l_object_version_number+1
1630 WHERE ITEM_GROUP_ID = l_object_id
1631 and object_Version_number=l_object_version_number;
1632 */
1633 END IF;
1634
1635
1636 if (sql%notfound)
1637 then
1638 FND_MESSAGE.Set_Name('AHL','AHL_APRV_OBJ_CHANGED');
1639 FND_MSG_PUB.Add;
1640
1641 l_return_status := FND_API.G_RET_STS_ERROR;
1642 return;
1643
1644 end if;
1645
1646 COMMIT;
1647 resultout := 'COMPLETE:';
1648 RETURN;
1649 END IF;
1650
1651 -- CANCEL mode
1652 --
1653 IF (funcmode = 'CANCEL') THEN
1654 resultout := 'COMPLETE:';
1655 RETURN;
1656 END IF;
1657
1658 --
1659 -- TIMEOUT mode
1660 --
1661 IF (funcmode = 'TIMEOUT') THEN
1662 resultout := 'COMPLETE:';
1663 RETURN;
1664 END IF;
1665
1666
1667 EXCEPTION
1668 WHEN fnd_api.g_exc_error THEN
1669 FND_MSG_PUB.Count_And_Get (
1670 p_encoded => FND_API.G_FALSE,
1671 p_count => l_msg_count,
1672 p_data => l_msg_data
1673 );
1674 ahl_generic_aprv_pvt.Handle_Error
1675 (p_itemtype => itemtype ,
1676 p_itemkey => itemkey ,
1677 p_msg_count => l_msg_count, -- Number of error Messages
1678 p_msg_data => l_msg_data ,
1679 p_attr_name => 'ERROR_MSG',
1680 x_error_msg => l_error_msg
1681 ) ;
1682 wf_core.context('AHL_ITEMGROUPS_APPROVAL_PVT','revert_status',
1683 itemtype,itemkey,actid,funcmode,l_error_msg);
1684 RAISE;
1685 WHEN OTHERS THEN
1686 wf_core.context(
1687 'AHL_ItemGroups_APPROVAL_PVT'
1688 ,'REVERT_STATUS'
1689 ,itemtype
1690 ,itemkey
1691 ,actid
1692 ,funcmode
1693 ,'Unexpected Error!'
1694 );
1695 RAISE;
1696
1697 END Revert_Status;
1698
1699 END AHL_ITEMGROUPS_APPROVAL_PVT;