[Home] [Help]
PACKAGE BODY: APPS.POS_SUPP_APPR
Source
1 package body POS_SUPP_APPR as
2 /* $Header: POSSPAPB.pls 120.10.12020000.3 2013/02/09 13:20:19 hvutukur ship $ */
3
4 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 g_module_prefix CONSTANT VARCHAR2(50) := 'pos.plsql.' || 'POS_SUPP_APPR' || '.';
6
7 approvalListStr VARCHAR2(32767) := NULL;
8
9 ------------------
10 --Private Routines
11 ------------------
12
13 --------------------------------------------------------------------------------
14 --Start of Comments
15 --Name: marshalField
16 --Function:
17 -- Append the input string into approval list string
18 -- Replace the input string if it contains either a quote or delimiter char.
19 -- Another quote char is added in front of a quote or delimiter char.
20
21 --Parameters:
22 --IN:
23 --p_string
24 -- Input string
25
26 --OUT:
27 --None
28 --End of Comments
29 --------------------------------------------------------------------------------
30 PROCEDURE marshalField(p_string IN VARCHAR2,
31 p_quote_char IN VARCHAR2,
32 p_delimiter IN VARCHAR2);
33 --------------------------------------------------------------------------------
34
35
36 PROCEDURE marshalField(p_string IN VARCHAR2,
37 p_quote_char IN VARCHAR2,
38 p_delimiter IN VARCHAR2) IS
39 l_string VARCHAR2(32767) := NULL;
40 BEGIN
41 l_string := p_string;
42 l_string := REPLACE(l_string, p_quote_char, p_quote_char || p_quote_char);
43 l_string := REPLACE(l_string, p_delimiter, p_quote_char || p_delimiter);
44 approvalListStr := approvalListStr ||l_string || p_delimiter;
45 END marshalField;
46
47 ------------------
48 --End Private Routines
49 ------------------
50
51
52 -------------------------------------------------------------------------------
53 -- PROCEDURE INITIALIZE_WF
54 --
55 -- Initializes WF attributes
56 --
57 -- IN
58 -- itemtype - type of the current item
59 -- itemkey - key of the current item
60 -- actid - process activity instance id
61 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
62 -- OUT
63 -- resultout
64 -- - COMPLETE[:<result>]
65 -- activity has completed with the indicated result
66 -- - WAITING
67 -- activity is waiting for additional transitions
68 -- - DEFERED
69 -- execution should be defered to background
70 -- - NOTIFIED[:<notification_id>:<assigned_user>]
71 -- activity has notified an external entity that this
72 -- step must be performed. A call to wf_engine.CompleteActivty
73 -- will signal when this step is complete. Optional
74 -- return of notification ID and assigned user.
75 -- - ERROR[:<error_code>]
76 -- function encountered an error.
77 -------------------------------------------------------------------------------
78 procedure INITIALIZE_WF(
79 itemtype in varchar2,
80 itemkey in varchar2,
81 actid in number,
82 funcmode in varchar2,
83 resultout in out nocopy varchar2)
84 is
85 l_api_name varchar2(50) := 'INITIALIZE_WF';
86 l_progress VARCHAR2(500) := '000';
87 begin
88
89 l_progress := 'SET_STATUS_REJECTED 001';
90
91 --
92 -- RUN mode - normal process execution
93 --
94 if (funcmode = 'RUN') then
95
96 wf_engine.SetItemAttrText( itemtype => itemtype,
97 itemkey => itemkey,
98 aname => 'AME_PROCESS_STATUS',
99 avalue => 'INPROCESS');
100
101 wf_engine.SetItemAttrText( itemtype => itemtype,
102 itemkey => itemkey,
103 aname => 'APPROVER_RESPONSE',
104 avalue => 'NORESPONSE');
105
106 l_progress := 'SET_STATUS_REJECTED 002 Setting WF attributes AME_PROCESS_STATUS and APPROVER_RESPONSE';
107 if g_fnd_debug = 'Y' then
108 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
109 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
110 END IF;
111 end if;
112
113 -- no result needed
114 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
115 return;
116 end if;
117
118
119 --
120 -- CANCEL mode - activity 'compensation'
121 --
122 if (funcmode = 'CANCEL') then
123
124 -- no result needed
125 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
126 return;
127 end if;
128
129 --
130 -- Other execution modes may be created in the future. Your
131 -- activity will indicate that it does not implement a mode
132 -- by returning null
133 --
134 resultout := wf_engine.eng_null;
135 return;
136
137 exception
138 when others then
139 -- The line below records this function call in the error system
140 -- in the case of an exception.
141 wf_core.context('POS_SUPP_APPR', 'INITIALIZE_WF',
142 itemtype, itemkey, to_char(actid), funcmode);
143 if g_fnd_debug = 'Y' then
144 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
145 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
146 l_progress ||' sqlerrm - '||sqlerrm);
147 END IF;
148 end if;
149 raise;
150 end INITIALIZE_WF;
151
152
153 -------------------------------------------------------------------------------
154 -- PROCEDURE SET_STATUS_REJECTED
155 --
156 -- Sets the value of attribute AME_PROCESS_STATUS to REJECTED
157 -- Called when approver rejects a request, supplier is rejected
158 --
159 -- IN
160 -- itemtype - type of the current item
161 -- itemkey - key of the current item
162 -- actid - process activity instance id
163 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
164 -- OUT
165 -- resultout
166 -- - COMPLETE[:<result>]
167 -- activity has completed with the indicated result
168 -------------------------------------------------------------------------------
169 procedure SET_STATUS_REJECTED(
170 itemtype in varchar2,
171 itemkey in varchar2,
172 actid in number,
173 funcmode in varchar2,
174 resultout in out nocopy varchar2)
175
176 is
177 l_profile_value varchar2(1);
178 l_api_name varchar2(50) := 'SET_STATUS_REJECTED';
179 l_progress VARCHAR2(500) := '000';
180 begin
181
182 l_progress := 'SET_STATUS_REJECTED 001';
183
184 --
185 -- RUN mode - normal process execution
186 --
187 if (funcmode = 'RUN') then
188
189
190 wf_engine.SetItemAttrText ( itemtype => itemtype,
191 itemkey => itemkey,
192 aname => 'AME_PROCESS_STATUS',
193 avalue => 'REJECTED');
194
195 l_progress := 'SET_STATUS_REJECTED 002 WF attribute AME_PROCESS_STATUS set to REJECETED';
196 if g_fnd_debug = 'Y' then
197 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
198 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
199 END IF;
200 end if;
201
202 end if;
203
204 --
205 -- Other execution modes may be created in the future. Your
206 -- activity will indicate that it does not implement a mode
207 -- by returning null
208 --
209 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
210 return;
211
212 exception
213 when others then
214 -- The line below records this function call in the error system
215 -- in the case of an exception.
216 wf_core.context('POS_SUPP_APPR', 'SET_STATUS_REJECTED',
217 itemtype, itemkey, to_char(actid), funcmode);
218 if g_fnd_debug = 'Y' then
219 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
220 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
221 l_progress ||' sqlerrm - '||sqlerrm);
222 END IF;
223 end if;
224 raise;
225 end SET_STATUS_REJECTED;
226
227
228 -------------------------------------------------------------------------------
229 -- PROCEDURE IS_AME_ENABLED
230 --
231 -- Procedure to check if AME is enabled for Supplier Approval Management
232 --
233 -- IN
234 -- itemtype - type of the current item
235 -- itemkey - key of the current item
236 -- actid - process activity instance id
237 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
238 -- OUT
239 -- resultout
240 -- - COMPLETE:Y
241 -- - COMPLETE:N
242 -------------------------------------------------------------------------------
243 procedure IS_AME_ENABLED(
244 itemtype in varchar2,
245 itemkey in varchar2,
246 actid in number,
247 funcmode in varchar2,
248 resultout in out nocopy varchar2)
249
250 is
251 l_profile_value varchar2(1);
252 l_api_name varchar2(50) := 'IS_AME_ENABLED';
253 l_progress VARCHAR2(500) := '000';
254
255 begin
256
257 l_progress := 'IS_AME_ENABLED 001';
258 --
259 -- RUN mode - normal process execution
260 --
261 if (funcmode = 'RUN') then
262
263
264 CHECK_IF_AME_ENABLED(
265 result => l_profile_value );
266
267 l_progress := 'IS_AME_ENABLED 002 CHECK_IF_AME_ENABLED returned - '||l_profile_value;
268 if g_fnd_debug = 'Y' then
269 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
270 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
271 END IF;
272 end if;
273
274 resultout := wf_engine.eng_completed||':'||l_profile_value;
275
276
277 wf_engine.SetItemAttrText ( itemtype => itemtype,
278 itemkey => itemkey,
279 aname => 'AME_ENABLED',
280 avalue => l_profile_value);
281 l_progress := 2;
282
283 if (l_profile_value = 'N') then
284 wf_engine.SetItemAttrText ( itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'AME_PROCESS_STATUS',
287 avalue => 'NOAME');
288 end if;
289
290 l_progress := 'IS_AME_ENABLED 003 - WF attributes set';
291 if g_fnd_debug = 'Y' then
292 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
293 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
294 END IF;
295 end if;
296
297 return;
298
299 end if;
300
301 resultout := wf_engine.eng_null;
302 return;
303
304 exception
305 when others then
306 wf_core.context('POSSPAPP', 'POSSPAPP_PROCESS',
307 itemtype, itemkey, to_char(actid), funcmode);
308 if g_fnd_debug = 'Y' then
309 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
310 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
311 l_progress ||' sqlerrm - '||sqlerrm);
312 END IF;
313 end if;
314 raise;
315 end IS_AME_ENABLED;
316
317
318
319 -------------------------------------------------------------------------------
320 -- PROCEDURE GET_NEXT_APPROVER
321 --
322 -- Procedure to check if current user is the next APPROVER in AME
323 --
324 -- IN
325 -- itemtype - type of the current item
326 -- itemkey - key of the current item
327 -- actid - process activity instance id
328 -- funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
329 -- OUT
330 -- resultout
331 -- - COMPLETE:VALID_NEXT_APPROVER
332 -- activity has completed, there is a valid next approver
333 -- - COMPLETE:NO_NEXT_APPROVER
334 -- activity has completed, no more approvers
335 -------------------------------------------------------------------------------
336 procedure GET_NEXT_APPROVER(
337 itemtype in varchar2,
338 itemkey in varchar2,
339 actid in number,
340 funcmode in varchar2,
341 resultout in out nocopy varchar2)
342 is
343 wf_yes varchar2(1) := 'Y';
344 wf_no varchar2(1) := 'N';
345
346 l_api_name varchar2(50) := 'GET_NEXT_APPROVER';
347 l_progress VARCHAR2(500) := '000';
348
349 l_next_approver_id NUMBER;
350 l_next_approver_user_name fnd_user.user_name%TYPE;
351 l_next_approver_disp_name wf_users.display_name%TYPE;
352 l_orig_system wf_users.orig_system%TYPE := 'PER';
353 l_sequence_num NUMBER;
354 l_approver_type VARCHAR2(30);
355
356 l_doc_string varchar2(200);
357 l_preparer_user_name fnd_user.user_name%TYPE;
358 l_org_id number;
359
360 l_next_approver ame_util.approverRecord;
361 l_insertion_type VARCHAR2(30);
362 l_authority_type VARCHAR2(30);
363 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
364
365 l_completeYNO varchar2(100);
366 l_position_has_valid_approvers varchar2(10);
367 l_need_to_get_next_approver boolean;
368 g_next_approvers ame_util.approversTable2;
369
370 l_approver_index NUMBER;
371 l_next_approver_name per_employees_current_x.full_name%TYPE;
372
373 begin
374
375
376 l_progress := 'GET_NEXT_APPROVER: 001';
377 --
378 -- RUN mode - normal process execution
379 --
380 if (funcmode = 'RUN') then
381
382
383 wf_engine.SetItemAttrText ( itemtype => itemtype,
384 itemkey => itemkey,
385 aname => 'APPROVER_RESPONSE',
386 avalue => 'NORESPONSE');
387
388
389 resultout := wf_engine.eng_completed||':'||'VALID_NEXT_APPROVER';
390
391 -- ame_api2.clearAllApprovals( applicationIdIn=>177,
392 -- transactionTypeIn=>'POS_SUPP_APPR',
393 -- transactionIdIn=>itemkey);
394
395 ame_api2.getNextApprovers4( applicationIdIn=>177,
396 transactionIdIn=>itemkey,
397 transactionTypeIn=>'POS_SUPP_APPR',
398 approvalProcessCompleteYNOut=>l_completeYNO,
399 nextApproversOut=>g_next_approvers
400 );
401
402 l_progress := 'GET_NEXT_APPROVER: 002 for AME transactionId - '||itemkey||
403 ' getNextApprovers4 returns completeYNO - '|| l_completeYNO;
404 if g_fnd_debug = 'Y' then
405 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
406 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
407 END IF;
408 end if;
409
410 if (l_completeYNO = 'Y') then
411
412 resultout := wf_engine.eng_completed||':'||'NO_NEXT_APPROVER';
413
414 wf_engine.SetItemAttrText ( itemtype => itemtype,
415 itemkey => itemkey,
416 aname => 'AME_PROCESS_STATUS',
417 avalue => 'APPROVED');
418
419 l_progress := 'GET_NEXT_APPROVER: 003 for AME transactionId - '||itemkey||
420 ' setting AME_PROCESS_STATUS to APPROVED';
421 if g_fnd_debug = 'Y' then
422 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
423 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
424 END IF;
425 end if;
426
427 return;
428 end if;
429
430 --Assuming that we have only serial approvers
431 --Code does not handle parallel approvers
432
433 l_approver_index := g_next_approvers.first();
434
435 if ( l_approver_index is not null ) then
436
437 l_progress := 'GET_NEXT_APPROVER: 004 for next approver orig_System is '||
438 g_next_approvers(l_approver_index).orig_system;
439 if g_fnd_debug = 'Y' then
440 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
441 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
442 END IF;
443 end if;
444
445 -- Check whether Position Hierarchy or Employee-Sup Hierarchy setup or FND users.
446 if (g_next_approvers(l_approver_index).orig_system = 'PER') then
447 l_next_approver_id := g_next_approvers(l_approver_index).orig_system_id;
448 elsif (g_next_approvers(l_approver_index).orig_system = 'POS') then
449
450 begin
451 -- find the persond id from the position_id
452 SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
453 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
454 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate)
455 between person.effective_start_date
456 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
457 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
458 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
459 and asg.assignment_status_type_id not in (
460 SELECT assignment_status_type_id FROM per_assignment_status_types
461 WHERE per_system_status = 'TERM_ASSIGN'
462 ) and trunc(sysdate) between asg.effective_start_date
463 and asg.effective_end_date order by person.last_name
464 ) where rownum = 1;
465
466 exception
467 WHEN NO_DATA_FOUND THEN
468 RAISE;
469 END;
470
471 elsif (g_next_approvers(l_approver_index).orig_system = 'FND' OR
472 g_next_approvers(l_approver_index).orig_system = 'FND_USR') then
473
474 SELECT employee_id
475 into l_next_approver_id
476 FROM fnd_user
477 WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
478 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
479 end if;
480
481 l_progress := 'GET_NEXT_APPROVER: 005 for next approver approver_id is '||l_next_approver_id;
482 if g_fnd_debug = 'Y' then
483 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
484 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
485 END IF;
486 end if;
487
488
489 WF_DIRECTORY.GetUserName('PER', l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
490
491 l_progress := 'GET_NEXT_APPROVER: 006 for next approver l_next_approver_user_name is '||l_next_approver_user_name;
492 if g_fnd_debug = 'Y' then
493 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
494 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
495 END IF;
496 end if;
497
498
499 wf_engine.SetItemAttrText ( itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'APPROVER_USER_NAME',
502 avalue => l_next_approver_user_name);
503
504
505 wf_engine.SetItemAttrText ( itemtype => itemtype,
506 itemkey => itemkey,
507 aname => 'APPROVER_DISPLAY_NAME',
508 avalue => l_next_approver_disp_name);
509
510 wf_engine.SetItemAttrText ( itemtype => itemtype,
511 itemkey => itemkey,
512 aname => 'AME_APPROVER_ID',
513 avalue => g_next_approvers(l_approver_index).orig_system_id);
514
515 wf_engine.SetItemAttrText ( itemtype => itemtype,
516 itemkey => itemkey,
517 aname => 'APPROVER_EMPID',
518 avalue => l_next_approver_id);
519
520 wf_engine.SetItemAttrText ( itemtype => itemtype,
521 itemkey => itemkey,
522 aname => 'APPROVAL_GROUP_ID',
523 avalue => g_next_approvers(l_approver_index).group_or_chain_id);
524
525 wf_engine.SetItemAttrText ( itemtype => itemtype,
526 itemkey => itemkey,
527 aname => 'AME_APPROVER_TYPE',
528 avalue => g_next_approvers(l_approver_index).orig_system);
529
530
531 l_progress := 'GET_NEXT_APPROVER: 007 set WF attributes for AME next approver';
532 if g_fnd_debug = 'Y' then
533 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
534 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
535 END IF;
536 end if;
537
538 end if;
539
540 return;
541
542 end if;
543
544 --
545 -- CANCEL mode
546 -- This is in the event that the activity must be undone,
547 -- for example when a process is reset to an earlier point
548 -- due to a loop back.
549 --
550 if (funcmode = 'CANCEL') then
551
552 -- no result needed
553 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
554 return;
555 end if;
556
557 --
558 -- Other execution modes may be created in the future. Your
559 -- activity will indicate that it does not implement a mode
560 -- by returning null
561 --
562 resultout := wf_engine.eng_null;
563 return;
564
565 exception
566 when others then
567 -- The line below records this function call in the error system
568 -- in the case of an exception.
569 wf_core.context('POS_SUPP_APPR', 'GET_NEXT_APPROVER',
570 itemtype, itemkey, to_char(actid), funcmode);
571 if g_fnd_debug = 'Y' then
572 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
573 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
574 l_progress ||' sqlerrm - '||sqlerrm);
575 END IF;
576 end if;
577 raise;
578 end GET_NEXT_APPROVER;
579
580
581 -------------------------------------------------------------------------------
582 -- PROCEDURE GET_APPROVER_IN_WF
583 --
584 -- Procedure to get information about next approver stored in workflow
585 --
586 -- IN
587 -- suppid - itemkey for workflow (SuppRegId)
588 -- OUT
589 -- user_id - user_id in fnd_user
590 -- user_name - user_name in fnd_user
591 -- user_firstname - the first name of this user
592 -- user_lastname - the last name of this user
593 -- status - S/E
594 -------------------------------------------------------------------------------
595 procedure GET_APPROVER_IN_WF(
596 suppid in varchar2,
597 user_id out nocopy varchar2,
598 user_name out nocopy varchar2,
599 user_firstname out nocopy varchar2,
600 user_lastname out nocopy varchar2,
601 status in out nocopy varchar2)
602 is
603 wf_yes varchar2(1) := 'Y';
604 wf_no varchar2(1) := 'N';
605 wf_approver varchar2(100);
606 wf_approver_response varchar2(100);
607 isAmeEnabled varchar2(1);
608 l_api_name varchar2(50) := 'GET_APPROVER_IN_WF';
609 l_progress VARCHAR2(500) := '000';
610
611 begin
612
613 status := 'S';
614 l_progress := 'GET_APPROVER_IN_WF: 001';
615
616 --procedure returns Y for all users when AME is not enabled
617 CHECK_IF_AME_ENABLED(result => isAmeEnabled);
618
619 if (isAmeEnabled = wf_no) then
620 status := 'E';
621 return;
622 end if;
623
624 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
625 itemkey => suppid,
626 aname => 'APPROVER_USER_NAME');
627
628 wf_approver_response := wf_engine.GetItemAttrText(itemtype => wfItemType,
629 itemkey => suppid,
630 aname => 'APPROVER_RESPONSE');
631
632 l_progress := 'GET_APPROVER_IN_WF: 002 WF attribute APPROVER_USER_NAME - '||wf_approver ||
633 ' username - '||wf_approver||'WF attribute APPROVER_RESPONSE - '||wf_approver_response;
634 if g_fnd_debug = 'Y' then
635 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
636 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
637 END IF;
638 end if;
639
640 if wf_approver_response = 'NORESPONSE'
641 then
642 begin
643 SELECT fu.user_id, fu.user_name, hp.person_first_name, hp.person_last_name
644 into user_id, user_name, user_firstname, user_lastname
645 FROM fnd_user fu, hz_parties hp
646 WHERE fu.user_name = wf_approver
647 and fu.person_party_id = hp.party_id(+)
648 and rownum = 1;
649 exception
650 WHEN NO_DATA_FOUND THEN
651 RAISE;
652 END;
653 else
654 status := 'E';
655 end if;
656
657 l_progress := 'GET_APPROVER_IN_WF: 003 values retreived for user_id, description, user_name are - '||user_id||', '||user_name||', '||user_firstname||','||user_lastname;
658 if g_fnd_debug = 'Y' then
659 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
660 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
661 END IF;
662 end if;
663
664 exception
665 when others then
666 if g_fnd_debug = 'Y' then
667 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
668 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
669 l_progress ||' sqlerrm - '||sqlerrm);
670 END IF;
671 end if;
672 status := 'E';
673 end GET_APPROVER_IN_WF;
674
675 -------------------------------------------------------------------------------
676 -- PROCEDURE CHECK_IF_APPROVER
677 --
678 -- Procedure to check if current user is the next APPROVER in AME
679 --
680 -- IN
681 -- suppid - itemkey for workflow (SuppRegId)
682 -- approver - username of user
683 -- OUT
684 -- result - Y/N
685 -------------------------------------------------------------------------------
686 procedure CHECK_IF_APPROVER(
687 suppid in varchar2,
688 approver in varchar2,
689 result in out nocopy varchar2)
690 is
691 wf_yes varchar2(1) := 'Y';
692 wf_no varchar2(1) := 'N';
693 wf_approver varchar2(100);
694 wf_approver_response varchar2(100);
695 isAmeEnabled varchar2(1);
696 l_api_name varchar2(50) := 'CHECK_IF_APPROVER';
697 l_progress VARCHAR2(500) := '000';
698 l_resultout VARCHAR2(100);
699
700 begin
701
702 l_progress := 'CHECK_IF_APPROVER: 001';
703
704 --procedure returns Y for all users when AME is not enabled
705 CHECK_IF_AME_ENABLED(result => isAmeEnabled);
706
707 l_progress := 'CHECK_IF_APPROVER: 002 CHECK_IF_AME_ENABLED returns '||isAmeEnabled;
708 if g_fnd_debug = 'Y' then
709 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
710 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
711 END IF;
712 end if;
713
714 if (isAmeEnabled = wf_no) then
715 result := wf_yes;
716 return;
717 end if;
718
719 -- fix for bug 5077461
720 -- getting the next approver whenever check_if_approver is called to handle changes in AME setup
721
722 GET_NEXT_APPROVER(
723 itemtype => wfItemType,
724 itemkey => suppid,
725 actid => 0,
726 funcmode => 'RUN',
727 resultout => l_resultout);
728
729 --end fix
730
731 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
732 itemkey => suppid,
733 aname => 'APPROVER_USER_NAME');
734
735 wf_approver_response := wf_engine.GetItemAttrText(itemtype => wfItemType,
736 itemkey => suppid,
737 aname => 'APPROVER_RESPONSE');
738
739 l_progress := 'CHECK_IF_APPROVER: 003 WF attribute APPROVER_USER_NAME - '||wf_approver ||
740 ' username - '||wf_approver||'WF attribute APPROVER_RESPONSE - '||wf_approver_response;
741 if g_fnd_debug = 'Y' then
742 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
743 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
744 END IF;
745 end if;
746
747 if wf_approver = approver AND wf_approver_response = 'NORESPONSE'
748 then
749 --procedure returns Y when username passes is the same as WF attribute APPROVER_USER_NAME
750 --and WF attribute APPROVER_RESPONSE is NORESPONSE
751 result := wf_yes;
752 else
753 result := wf_no;
754 end if;
755
756 exception
757 when others then
758 if g_fnd_debug = 'Y' then
759 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
760 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
761 l_progress ||' sqlerrm - '||sqlerrm);
762 END IF;
763 end if;
764 result := wf_no;
765 end CHECK_IF_APPROVER;
766
767 -------------------------------------------------------------------------------
768 -- PROCEDURE GET_AME_PROCESS_STATUS
769 --
770 -- Procedure to find status of AME WF
771 -- Returns value for attribute AME_PROCESS_STATUS
772 --
773 -- IN
774 -- suppid - itemkey for workflow (SuppRegId)
775 -- OUT
776 -- resultout - INPROCESS/APPROVED/REJECTED/NOAME
777 -------------------------------------------------------------------------------
778 procedure GET_AME_PROCESS_STATUS(
779 suppid in varchar2,
780 result in out nocopy varchar2)
781 is
782 process_status varchar2(20);
783 l_api_name varchar2(50) := 'GET_AME_PROCESS_STATUS';
784 l_progress VARCHAR2(500) := '000';
785 begin
786
787 l_progress := 'GET_AME_PROCESS_STATUS: 001';
788
789 -- Bug 9314932
790 -- Added "ignore_notfound => TRUE", so that it returns null if there is no process yet
791 process_status := wf_engine.GetItemAttrText(itemtype => wfItemType,
792 itemkey => suppid,
793 aname => 'AME_PROCESS_STATUS',
794 ignore_notfound => TRUE);
795
796 result := process_status;
797
798 l_progress := 'GET_AME_PROCESS_STATUS: 002 suppid - '||suppid||' WF attribute AME_PROCESS_STATUS'||result;
799
800 if g_fnd_debug = 'Y' then
801 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
802 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
803 END IF;
804 end if;
805
806 exception
807 when others then
808 if g_fnd_debug = 'Y' then
809 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
810 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
811 l_progress ||' sqlerrm - '||sqlerrm);
812 END IF;
813 end if;
814 raise;
815 end GET_AME_PROCESS_STATUS;
816
817
818
819 -------------------------------------------------------------------------------
820 -- PROCEDURE PROCESS_APPROVE
821 --
822 -- Procedure to to Approve a Supplier Registration Request
823 -- Called when an Approver approves a request
824 -- Reject in AME
825 -- IN
826 -- suppid - itemkey for workflow (SuppRegId)
827 -- approver - username for user rejecting supplier request
828 -- OUT
829 -- result - SUCCESS/FAILURE
830 -- processresult - APPROVED/REJECTED/INPROCESS/ERROR
831 -------------------------------------------------------------------------------
832 procedure PROCESS_APPROVE(
833 suppid in varchar2,
834 approver in varchar2,
835 result in out nocopy varchar2,
836 processresult in out nocopy varchar2)
837
838 is
839 l_api_name varchar2(50) := 'PROCESS_APPROVE';
840 l_progress VARCHAR2(500) := '000';
841 wf_approver varchar2(100);
842 lerrname varchar2(30);
843 lerrmsg varchar2(2000);
844 lerrstack varchar2(32000);
845 begin
846
847 l_progress := 'PROCESS_APPROVE: 001';
848
849 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
850 itemkey => suppid,
851 aname => 'APPROVER_USER_NAME');
852
853 l_progress := 'PROCESS_APPROVE: 002 username - '||approver||' approver in WF - '||wf_approver;
854
855 if g_fnd_debug = 'Y' then
856 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
857 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
858 END IF;
859 end if;
860
861 if wf_approver = approver
862 then
863 wf_engine.SetItemAttrText ( itemtype => wfItemType,
864 itemkey => suppid,
865 aname => 'APPROVER_RESPONSE',
866 avalue => 'APPROVE');
867
868 l_progress := 'PROCESS_APPROVE: 003 WF attribute APPROVER_RESPONSE set to APPROVE for itemkey - '||suppid;
869
870 if g_fnd_debug = 'Y' then
871 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
872 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
873 END IF;
874 end if;
875
876 Process_Response_Internal( suppid,'APPROVE');
877
878 l_progress := 'PROCESS_APPROVE: 004 Invoked Process_Response_Internal with attributes - '||suppid||' and APPROVE';
879
880 if g_fnd_debug = 'Y' then
881 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
882 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
883 END IF;
884 end if;
885
886
887 begin
888 wf_engine.CompleteActivity( itemtype => wfItemType,
889 itemkey => suppid,
890 activity => 'BLOCK',
891 result => null);
892 exception
893 when others then
894 wf_core.get_error(lerrname,lerrmsg,lerrstack);
895 if lerrname = 'WFENG_NOT_NOTIFIED' then
896 null;
897 end if;
898 end;
899
900 l_progress := 'PROCESS_APPROVE: 005 Block completed for WF with itemkey - '||suppid;
901 -- Bug 5467675. Commit should never be in the middle of a procedure.
902 -- commit;
903 result := 'SUCCESS';
904
905 if g_fnd_debug = 'Y' then
906 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
907 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
908 END IF;
909 end if;
910
911 else
912
913 if g_fnd_debug = 'Y' then
914 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
915 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
916 ' This should never happenwf_approver != approver l_progress - '||l_progress );
917 END IF;
918 end if;
919
920 result := 'FAILURE';
921 end if;
922
923 GET_AME_PROCESS_STATUS( suppid => suppid,
924 result =>processresult);
925
926 l_progress := 'PROCESS_APPROVE: 006 GET_AME_PROCESS_STATUS returns - '||processresult;
927 if g_fnd_debug = 'Y' then
928 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
929 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
930 END IF;
931 end if;
932
933
934 exception
935 when others then
936 if g_fnd_debug = 'Y' then
937 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
938 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
939 l_progress ||' sqlerrm - '||sqlerrm);
940 END IF;
941 end if;
942 raise;
943 end PROCESS_APPROVE;
944
945
946
947 -------------------------------------------------------------------------------
948 -- PROCEDURE PROCESS_REJECT
949 --
950 -- Procedure to to Reject a Supplier Registration Request
951 -- Called when an Approver rejects a request
952 -- Reject in AME
953 -- IN
954 -- suppid - itemkey for workflow (SuppRegId)
955 -- approver - username for user rejecting supplier request
956 -- OUT
957 -- result - SUCCESS/FAILURE
958 -- processresult
959 -- - APPROVED
960 -- - ERROR
961 -- - REJECTED
962 -- - INPROCESS
963 -------------------------------------------------------------------------------
964 procedure PROCESS_REJECT(
965 suppid in varchar2,
966 approver in varchar2,
967 result in out nocopy varchar2,
968 processresult in out nocopy varchar2)
969 is
970 l_api_name varchar2(50) := 'PROCESS_REJECT';
971 l_progress VARCHAR2(500) := '000';
972 wf_approver varchar2(100);
973 lerrname varchar2(30);
974 lerrmsg varchar2(2000);
975 lerrstack varchar2(32000);
976 begin
977
978 l_progress := 'PROCESS_REJECT: 001';
979
980 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
981 itemkey => suppid,
982 aname => 'APPROVER_USER_NAME');
983
984 l_progress := 'PROCESS_REJECT: 002 username - '||approver||' approver in WF - '||wf_approver;
985
986 if g_fnd_debug = 'Y' then
987 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
988 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
989 END IF;
990 end if;
991
992 if wf_approver = approver
993 then
994 wf_engine.SetItemAttrText ( itemtype => wfItemType,
995 itemkey => suppid,
996 aname => 'APPROVER_RESPONSE',
997 avalue => 'REJECT');
998
999
1000 l_progress := 'PROCESS_REJECT: 003 WF attribute APPROVER_RESPONSE set to REJECT for itemkey - '||suppid;
1001
1002 if g_fnd_debug = 'Y' then
1003 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1004 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1005 END IF;
1006 end if;
1007
1008
1009 Process_Response_Internal(suppid,'REJECT');
1010
1011 l_progress := 'PROCESS_REJECT: 004 Invoked Process_Response_Internal with attributes - '||suppid||' and REJECT';
1012
1013 if g_fnd_debug = 'Y' then
1014 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1015 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1016 END IF;
1017 end if;
1018
1019
1020 begin
1021 wf_engine.CompleteActivity( itemtype => wfItemType,
1022 itemkey => suppid,
1023 activity => 'BLOCK',
1024 result => null);
1025 exception
1026 when others then
1027 wf_core.get_error(lerrname,lerrmsg,lerrstack);
1028 if lerrname = 'WFENG_NOT_NOTIFIED' then
1029 null;
1030 end if;
1031 end;
1032
1033
1034 l_progress := 'PROCESS_REJECT: 005 Block completed for WF with itemkey - '||suppid;
1035 -- Bug 5467675. Commit should never be in the middle of a procedure.
1036 -- commit;
1037 result := 'SUCCESS';
1038
1039 if g_fnd_debug = 'Y' then
1040 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1041 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1042 END IF;
1043 end if;
1044
1045 else
1046
1047 if g_fnd_debug = 'Y' then
1048 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1049 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
1050 ' This should never happenwf_approver != approver l_progress - '||l_progress );
1051 END IF;
1052 end if;
1053
1054 result := 'FAILURE';
1055 end if;
1056
1057
1058 GET_AME_PROCESS_STATUS( suppid => suppid,
1059 result =>processresult);
1060
1061 l_progress := 'PROCESS_REJECT: 006 GET_AME_PROCESS_STATUS returns - '||processresult;
1062 if g_fnd_debug = 'Y' then
1063 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1064 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1065 END IF;
1066 end if;
1067
1068 exception
1069 when others then
1070 if g_fnd_debug = 'Y' then
1071 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1072 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
1073 l_progress ||' sqlerrm - '||sqlerrm);
1074 END IF;
1075 end if;
1076 raise;
1077 end PROCESS_REJECT;
1078
1079 -------------------------------------------------------------------------------
1080 -- PROCEDURE CHECK_IF_AME_ENABLED
1081 --
1082 -- Procedure to to find out if AME is enabled for Supplier Approval Management
1083 --
1084 -- IN
1085 --
1086 -- OUT
1087 -- result - Y/N
1088 -------------------------------------------------------------------------------
1089 procedure CHECK_IF_AME_ENABLED(
1090 result in out nocopy varchar2)
1091 is
1092 l_api_name varchar2(50) := 'CHECK_IF_AME_ENABLED';
1093 l_progress VARCHAR2(500) := '000';
1094 begin
1095
1096 l_progress := 'CHECK_IF_AME_ENABLED: 001';
1097
1098 result := NVL(FND_PROFILE.VALUE('POS_SAM_AME_ENABLED'),'N');
1099
1100 l_progress := 'CHECK_IF_AME_ENABLED: 002 -- result :' || result ;
1101
1102 if g_fnd_debug = 'Y' then
1103 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1104 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1105 END IF;
1106 end if;
1107
1108 exception
1109 when others then
1110 if g_fnd_debug = 'Y' then
1111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1112 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
1113 ' Unexpected Error l_progress - '||l_progress );
1114 END IF;
1115 end if;
1116 raise;
1117 end CHECK_IF_AME_ENABLED;
1118
1119
1120 -------------------------------------------------------------------------------
1121 -- PROCEDURE Process_Response_Internal
1122 --
1123 -- Procedure to approve/reject a Supplier Request in AME
1124 -- Called when Approver responsd to a Supplier Registration Request
1125 --
1126 -- IN
1127 -- suppid - AME transactionId (same as SuppRegId)
1128 -- p_response - APPROVE/REJECT
1129 -- OUT
1130 --
1131 -------------------------------------------------------------------------------
1132 procedure Process_Response_Internal( suppid in varchar2,
1133 p_response in varchar2 ) IS
1134
1135 l_progress VARCHAR2(500) := '000';
1136 l_document_id number;
1137 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
1138 l_current_approver ame_util.approverRecord2;
1139 l_approver_posoition_id number;
1140 l_approver_type varchar2(10);
1141 l_api_name varchar2(50) := 'Process_Response_Internal';
1142
1143 begin
1144
1145 l_progress := 'Process_Response_Internal: 001';
1146 -- IF (g_po_wf_debug = 'Y') THEN
1147 -- PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1148 --END IF;
1149
1150 l_transaction_type := wf_engine.GetItemAttrText( itemtype => wfItemType,
1151 itemkey => suppid,
1152 aname => 'AME_TRANSACTION_TYPE');
1153
1154 l_approver_type := wf_engine.GetItemAttrText( itemtype => wfItemType,
1155 itemkey => suppid,
1156 aname => 'AME_APPROVER_TYPE');
1157
1158 l_progress := 'Process_Response_Internal: 002 -- l_approver_type :' || l_approver_type ;
1159
1160 if g_fnd_debug = 'Y' then
1161 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1162 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1163 END IF;
1164 end if;
1165
1166 -- bug 5486926
1167 -- for position hierarchy, using POS:<AME_APPROVER_ID> as approver_name
1168 /*
1169 if (l_approver_type = 'POS') then
1170 l_current_approver.orig_system := 'POS';
1171 elsif (l_approver_type = 'FND') then
1172 l_current_approver.orig_system := 'FND';
1173 else
1174 l_current_approver.orig_system := 'PER';
1175 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1176 itemkey => suppid,
1177 aname => 'APPROVER_USER_NAME');
1178 end if;
1179
1180 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1181 itemkey => suppid,
1182 aname => 'APPROVER_USER_NAME');
1183
1184 l_current_approver.orig_system_id := wf_engine.GetItemAttrNumber( itemtype => wfItemType,
1185 itemkey => suppid,
1186 aname => 'AME_APPROVER_ID');
1187 */
1188 --start replace
1189 if (l_approver_type = 'POS') then
1190 l_current_approver.orig_system := 'POS';
1191 l_current_approver.name := 'POS:'||wf_engine.GetItemAttrText( itemtype => wfItemType,
1192 itemkey => suppid,
1193 aname => 'AME_APPROVER_ID');
1194 elsif (l_approver_type = 'FND') then
1195 l_current_approver.orig_system := 'FND';
1196 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1197 itemkey => suppid,
1198 aname => 'APPROVER_USER_NAME');
1199 else
1200 l_current_approver.orig_system := 'PER';
1201 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1202 itemkey => suppid,
1203 aname => 'APPROVER_USER_NAME');
1204 end if;
1205
1206 l_current_approver.orig_system_id := wf_engine.GetItemAttrText( itemtype => wfItemType,
1207 itemkey => suppid,
1208 aname => 'AME_APPROVER_ID');
1209 --end replace
1210
1211 l_progress := 'Process_Response_Internal: 003 -- l_current_approver.orig_system_id :' ||
1212 l_current_approver.orig_system_id ;
1213
1214 if g_fnd_debug = 'Y' then
1215 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1216 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1217 END IF;
1218 end if;
1219
1220 if( p_response = 'APPROVE') then
1221 l_current_approver.approval_status := ame_util.approvedStatus;
1222 elsif( p_response = 'REJECT') then
1223 l_current_approver.approval_status := ame_util.rejectStatus;
1224 elsif( p_response = 'TIMEOUT') then
1225 l_current_approver.approval_status := ame_util.noResponseStatus;
1226 end if;
1227
1228 l_progress := 'Process_Response_Internal: 004 -- p_response :' || p_response ;
1229
1230 if g_fnd_debug = 'Y' then
1231 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1232 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1233 END IF;
1234 end if;
1235
1236
1237 --Update the Approval status with the response from the approver.
1238 ame_api2.updateApprovalStatus( applicationIdIn=>ameApplicationId,
1239 transactionIdIn=>suppid,
1240 transactionTypeIn=>ameTransactionType,
1241 approverIn => l_current_approver);
1242
1243 l_progress := 'Process_Response_Internal: 005 -- Updated AME for transactiontype -'||ameTransactionType||
1244 ' and transactionId - '||suppid;
1245
1246 if g_fnd_debug = 'Y' then
1247 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1248 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1249 END IF;
1250 end if;
1251
1252 exception
1253 when others then
1254 if g_fnd_debug = 'Y' then
1255 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1256 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
1257 ' Unexpected Error l_progress - '||l_progress );
1258 END IF;
1259 end if;
1260 raise;
1261 end Process_Response_Internal;
1262
1263
1264 -------------------------------------------------------------------------------
1265 -- PROCEDURE STARTWF_POSSPAPP
1266 --
1267 -- Procedure to start workflow for AME Approval
1268 -- Called when Prospective supplier registration is submitted
1269 --
1270 -- IN
1271 -- suppid - id for Prospective Supplier (SupplierRegId)
1272 -- requestor - user name of requestor
1273 -- OUT
1274 -- result
1275 -- - SUCCESS
1276 -- When Workflow was completed successfully
1277 -- - FAILURE
1278 -- When Workflow was started successfully
1279 -- processresult
1280 -- - APPROVED
1281 -- - ERROR
1282 -- - REJECTED
1283 -- - INPROCESS
1284 -------------------------------------------------------------------------------
1285
1286 PROCEDURE STARTWF_POSSPAPP (suppid IN VARCHAR2,
1287 suppname IN VARCHAR2,
1288 requestor IN VARCHAR2,
1289 result in out nocopy varchar2,
1290 processresult in out nocopy varchar2)
1291 IS
1292 l_progress NUMBER := 0;
1293 l_api_name varchar2(50) := 'STARTWF_POSSPAPP';
1294 BEGIN
1295
1296 l_progress := 1;
1297
1298 -- create workflow process
1299
1300 wf_engine.CreateProcess(itemtype => wfItemType,
1301 itemkey => suppid, /*using suppid as the itemkey*/
1302 process => wfProcess);
1303
1304
1305 l_progress := 2;
1306
1307 wf_engine.SetItemAttrText (itemtype => wfItemType,
1308 itemkey => suppid,
1309 aname => 'REQUESTOR',
1310 avalue => requestor);
1311
1312 wf_engine.SetItemAttrText (itemtype => wfItemType,
1313 itemkey => suppid,
1314 aname => 'SUPPLIERNAME',
1315 avalue => suppname);
1316
1317 l_progress := 3;
1318
1319 wf_engine.StartProcess(itemtype => wfItemType,
1320 itemkey => suppid );
1321
1322 l_progress := 4;
1323
1324 if g_fnd_debug = 'Y' then
1325 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1326 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name,
1327 'Started workflow itemtype - '|| wfItemType ||' itemkey - '||suppid);
1328 END IF;
1329 end if;
1330
1331 result := 'SUCCESS';
1332
1333 l_progress := 5;
1334
1335 GET_AME_PROCESS_STATUS( suppid => suppid,
1336 result =>processresult);
1337
1338 EXCEPTION WHEN OTHERS THEN
1339
1340 result := 'FAILURE';
1341
1342 IF (g_fnd_debug = 'Y') THEN
1343 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1344 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,g_module_prefix ||l_api_name,
1345 'Error in starting workflow l_progress - ' || l_progress ||' sqlerrm - '||sqlerrm);
1346 END IF;
1347 END IF;
1348
1349 raise;
1350
1351 END STARTWF_POSSPAPP;
1352
1353 --------------------------------------------------------------------------------
1354 --Start of Comments
1355 --Name: get_ame_approval_list_history
1356 --Function:
1357 -- Call AME API to build the approver list history.
1358 --Parameters:
1359 --IN:
1360 -- pProspSupplierId Prospective Supplier Id
1361 --OUT:
1362 -- pApprovalListStr Approval List concatenated in a string
1363 -- pApprovalListCount Number of Approvers.
1364 -- It has a value of 0, if the document does not require approval.
1365 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
1366 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
1367 --End of Comments
1368 --------------------------------------------------------------------------------
1369 procedure get_ame_approval_list_history( pProspSupplierId IN VARCHAR2,
1370 pApprovalListStr OUT NOCOPY VARCHAR2,
1371 pApprovalListCount OUT NOCOPY NUMBER,
1372 pQuoteChar OUT NOCOPY VARCHAR2,
1373 pFieldDelimiter OUT NOCOPY VARCHAR2
1374 ) IS
1375
1376 l_api_name varchar2(50):= 'get_ame_approval_list_history';
1377
1378 approverList ame_util.approversTable2;
1379 l_process_out VARCHAR2(10);
1380
1381 l_full_name per_people_f.full_name%TYPE;
1382 l_person_id per_people_f.person_id%TYPE;
1383 l_job_or_position VARCHAR2(2000);
1384 l_orig_system VARCHAR2(10);
1385 l_orig_system_id NUMBER;
1386
1387 l_job_id number;
1388 l_position_id number;
1389 l_valid_approver VARCHAR2(1);
1390
1391 BEGIN
1392
1393 if g_fnd_debug = 'Y' then
1394 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1395 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_ame_approval_list...');
1396 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pProspSupplierId :' ||
1397 pProspSupplierId );
1398 END IF;
1399 end if;
1400
1401 pQuoteChar :=quoteChar;
1402 pFieldDelimiter :=fieldDelimiter;
1403
1404 approvalListStr := NULL;
1405 pApprovalListCount := 0;
1406
1407
1408 ame_api2.getAllApprovers7( applicationIdIn => ameApplicationId,
1409 transactionIdIn => pProspSupplierId,
1410 transactionTypeIn => ameTransactionType,
1411 approvalProcessCompleteYNOut => l_process_out,
1412 approversOut => approverList
1413 );
1414 -- Iterate through the list of approvers.
1415 for i in 1 .. approverList.count loop
1416
1417 l_valid_approver := 'Y';
1418 if g_fnd_debug = 'Y' then
1419 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1420 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Processing the approver :' || i );
1421 END IF;
1422 end if;
1423
1424 -- do not consider the deleted approver.
1425 --if the approval_status value is SUPPRESSED, then the user is deleted from the list.
1426 if( ( ( l_process_out = 'Y' OR l_process_out = 'N' ) AND
1427 --changing the logic from AP implementation
1428 --( approverList(i).approval_status is not null AND approverList(i).approval_status <> 'SUPPRESSED' )
1429 ( approverList(i).approval_status is null OR approverList(i).approval_status <> 'SUPPRESSED' )
1430 ) OR ( ( l_process_out = 'W' OR l_process_out = 'P' )AND
1431 (approverList(i).approval_status is null OR approverList(i).approval_status <> 'SUPPRESSED'))) then
1432
1433 l_orig_system := approverList(i).orig_system;
1434 l_orig_system_id := approverList(i).orig_system_id;
1435 l_job_or_position := NULL;
1436
1437 if ( l_orig_system = 'PER') then
1438
1439 -- Employee Supervisor Record.
1440 if g_fnd_debug = 'Y' then
1441 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1442 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Emp - Sup Record ...');
1443 END IF;
1444 end if;
1445
1446 l_full_name := approverList(i).display_name;
1447
1448 l_person_id := l_orig_system_id;
1449
1450 elsif ( l_orig_system = 'POS') then
1451
1452 -- Position Hierarchy Record. The logic is mentioned in the comments section.
1453 if g_fnd_debug = 'Y' then
1454 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1455 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Position Hierarchy Record ...');
1456 END IF;
1457 end if;
1458
1459 begin
1460 SELECT person_id, full_name into l_person_id,l_full_name FROM (
1461 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
1462 WHERE asg.position_id = l_orig_system_id and trunc(sysdate) between person.effective_start_date
1463 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1464 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1465 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1466 and asg.assignment_status_type_id not in (
1467 SELECT assignment_status_type_id FROM per_assignment_status_types
1468 WHERE per_system_status = 'TERM_ASSIGN'
1469 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
1470 order by person.last_name
1471 ) where rownum = 1;
1472 exception
1473 WHEN NO_DATA_FOUND THEN
1474 --RAISE;
1475 l_valid_approver := 'N';
1476 END;
1477
1478 elsif (l_orig_system = 'FND' OR l_orig_system = 'FND_USR' ) then
1479
1480 -- FND User Record.
1481 if g_fnd_debug = 'Y' then
1482 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1483 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'FND User Record ...');
1484 END IF;
1485 end if;
1486
1487 SELECT employee_id into l_person_id
1488 FROM fnd_user
1489 WHERE user_id = l_orig_system_id
1490 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
1491
1492 l_full_name := approverList(i).display_name;
1493
1494 end if;
1495
1496 if g_fnd_debug = 'Y' then
1497 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1498 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_full_name :' || l_full_name );
1499 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_person_id :' || l_person_id );
1500 END IF;
1501 end if;
1502
1503 -- Find position | job name
1504 if ( l_orig_system = 'POS' ) then
1505 l_job_or_position := approverList(i).display_name;
1506 else
1507 l_job_or_position := null;
1508 end if;
1509 -- Make sure position/job name is populated.
1510 if( l_job_or_position is null ) then
1511
1512 -- retrieve the position name. if the position name is null check for the job name.
1513
1514 SELECT position_id, job_id INTO l_position_id, l_job_id
1515 FROM per_all_assignments_f
1516 WHERE person_id = l_person_id
1517 and primary_flag = 'Y' and assignment_type in ('E','C')
1518 and assignment_status_type_id not in
1519 (select assignment_status_type_id
1520 from per_assignment_status_types
1521 where per_system_status = 'TERM_ASSIGN')
1522 and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE)
1523 AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
1524 and rownum = 1;
1525
1526 if l_position_id is not null then
1527 SELECT name INTO l_job_or_position FROM per_all_positions WHERE position_id = l_position_id;
1528 end if;
1529
1530 if l_job_or_position is null and l_job_id is not null then
1531 SELECT name INTO l_job_or_position FROM per_jobs WHERE job_id = l_job_id;
1532 end if;
1533
1534 if g_fnd_debug = 'Y' then
1535 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1536 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_job_or_position :' || l_job_or_position );
1537 END IF;
1538 end if;
1539
1540 end if;
1541
1542 if( l_valid_approver = 'Y' ) then
1543 marshalField(l_full_name, quoteChar, fieldDelimiter);
1544 marshalField( to_char(l_person_id), quoteChar, fieldDelimiter);
1545 marshalField(l_job_or_position, quoteChar, fieldDelimiter);
1546 marshalField(approverList(i).name, quoteChar, fieldDelimiter);
1547
1548 --marshalField(approversTableIn(i).orig_system, quoteChar, fieldDelimiter);
1549 --marshalField(to_char(approversTableIn(i).orig_system_id), quoteChar, fieldDelimiter);
1550
1551 marshalField(l_orig_system, quoteChar, fieldDelimiter);
1552 marshalField(to_char(l_orig_system_id), quoteChar, fieldDelimiter);
1553
1554 marshalField(approverList(i).api_insertion, quoteChar, fieldDelimiter);
1555 marshalField(approverList(i).authority, quoteChar, fieldDelimiter);
1556 marshalField(approverList(i).approval_status, quoteChar, fieldDelimiter);
1557 marshalField(approverList(i).approver_category, quoteChar, fieldDelimiter);
1558 marshalField(approverList(i).approver_order_number, quoteChar, fieldDelimiter);
1559 marshalField(approverList(i).action_type_id, quoteChar, fieldDelimiter);
1560
1561 --changing the logic from AP implementation
1562 --marshalField(approverList(i).group_or_chain_id, quoteChar, fieldDelimiter);
1563
1564 marshalField('', quoteChar, fieldDelimiter);
1565 marshalField(approverList(i).member_order_number, quoteChar, fieldDelimiter);
1566 --marshalField(to_char(i), quoteChar, fieldDelimiter);
1567 pApprovalListCount := pApprovalListCount +1;
1568 marshalField(to_char(pApprovalListCount), quoteChar, fieldDelimiter);
1569 end if;
1570
1571 end if;
1572
1573 end loop;
1574
1575 pApprovalListStr := approvalListStr;
1576
1577 if g_fnd_debug = 'Y' then
1578 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1579 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_ame_approval_list...');
1580 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
1581 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
1582 END IF;
1583 end if;
1584
1585 exception
1586 when NO_DATA_FOUND then
1587 pApprovalListCount := 0;
1588 pApprovalListStr := 'NO_DATA_FOUND';
1589 if g_fnd_debug = 'Y' then
1590 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1591 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1592 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
1593 END IF;
1594 end if;
1595
1596 when others then
1597 pApprovalListCount := 0;
1598 pApprovalListStr := 'EXCEPTION';
1599 if g_fnd_debug = 'Y' then
1600 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1601 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1602 l_api_name || '.others_exception', sqlerrm);
1603 END IF;
1604
1605 end if;
1606
1607 END get_ame_approval_list_history;
1608
1609 function CHECK_CURRENT_APPROVER(
1610 suppid in varchar2)
1611 return VARCHAR2 is
1612
1613 wf_yes varchar2(1) := 'Y';
1614 wf_no varchar2(1) := 'N';
1615 wf_approver varchar2(100);
1616 wf_approver_response varchar2(100);
1617 isAmeEnabled varchar2(1);
1618 l_api_name varchar2(50) := 'CHECK_IF_APPROVER';
1619 l_progress VARCHAR2(500) := '000';
1620 l_resultout VARCHAR2(100);
1621 l_current_user VARCHAR2(100);
1622
1623 begin
1624
1625 l_progress := 'CHECK_CURRENT_APPROVER: 001';
1626
1627 l_current_user:=FND_GLOBAL.user_name;
1628
1629 --procedure returns Y for all users when AME is not enabled
1630 CHECK_IF_AME_ENABLED(result => isAmeEnabled);
1631
1632 l_progress := 'CHECK_CURRENT_APPROVER: 002 CHECK_IF_AME_ENABLED returns '||isAmeEnabled;
1633 if g_fnd_debug = 'Y' then
1634 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1635 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1636 END IF;
1637 end if;
1638
1639 if (isAmeEnabled = wf_no) then
1640 return 'Y';
1641 end if;
1642
1643 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
1644 itemkey => suppid,
1645 aname => 'APPROVER_USER_NAME');
1646
1647 wf_approver_response := wf_engine.GetItemAttrText(itemtype => wfItemType,
1648 itemkey => suppid,
1649 aname => 'APPROVER_RESPONSE');
1650
1651
1652 if wf_approver = l_current_user AND wf_approver_response = 'NORESPONSE'
1653 then
1654 --procedure returns Y when username passes is the same as WF attribute APPROVER_USER_NAME
1655 --and WF attribute APPROVER_RESPONSE is NORESPONSE
1656 return 'Y';
1657 else
1658 return 'N';
1659 end if;
1660
1661 exception
1662 when others then
1663 if g_fnd_debug = 'Y' then
1664 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1665 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
1666 l_progress ||' sqlerrm - '||sqlerrm);
1667 END IF;
1668 end if;
1669 return 'N';
1670 end CHECK_CURRENT_APPROVER;
1671
1672 function GET_APPROVER_NAME_IN_WF(
1673 suppid in varchar2) return varchar2
1674 is
1675 wf_yes varchar2(1) := 'Y';
1676 wf_no varchar2(1) := 'N';
1677 wf_approver varchar2(100);
1678 wf_approver_response varchar2(100);
1679 isAmeEnabled varchar2(1);
1680 l_api_name varchar2(50) := 'GET_APPROVER_NAME_IN_WF';
1681 l_progress VARCHAR2(500) := '000';
1682 user_firstname varchar2(100);
1683 user_lastname varchar2(100);
1684
1685 begin
1686
1687 l_progress := 'GET_APPROVER_NAME_IN_WF: 001';
1688
1689 --procedure returns Y for all users when AME is not enabled
1690 CHECK_IF_AME_ENABLED(result => isAmeEnabled);
1691
1692 if (isAmeEnabled = wf_no) then
1693 return '';
1694 end if;
1695
1696 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
1697 itemkey => suppid,
1698 aname => 'APPROVER_USER_NAME');
1699
1700 wf_approver_response := wf_engine.GetItemAttrText(itemtype => wfItemType,
1701 itemkey => suppid,
1702 aname => 'APPROVER_RESPONSE');
1703
1704 l_progress := 'GET_APPROVER_NAME_IN_WF: 002 WF attribute APPROVER_USER_NAME - '||wf_approver ||
1705 ' username - '||wf_approver||'WF attribute APPROVER_RESPONSE - '||wf_approver_response;
1706 if g_fnd_debug = 'Y' then
1707 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1708 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1709 END IF;
1710 end if;
1711
1712 if wf_approver_response = 'NORESPONSE'
1713 then
1714 begin
1715 SELECT hp.person_first_name, hp.person_last_name
1716 into user_firstname, user_lastname
1717 FROM fnd_user fu, hz_parties hp
1718 WHERE fu.user_name = wf_approver
1719 and fu.person_party_id = hp.party_id(+)
1720 and rownum = 1;
1721
1722 return user_firstname||' '||user_lastname;
1723
1724 exception
1725 WHEN NO_DATA_FOUND THEN
1726 RAISE;
1727 END;
1728 else
1729 return '';
1730 end if;
1731
1732 exception
1733 when others then
1734 if g_fnd_debug = 'Y' then
1735 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1736 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
1737 l_progress ||' sqlerrm - '||sqlerrm);
1738 END IF;
1739 end if;
1740 return '';
1741 end GET_APPROVER_NAME_IN_WF;
1742
1743 end POS_SUPP_APPR;