[Home] [Help]
PACKAGE BODY: APPS.POS_SUPP_APPR
Source
1 package body POS_SUPP_APPR as
2 /* $Header: POSSPAPB.pls 120.6 2006/08/25 20:30:38 abtrived noship $ */
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
699 begin
700
701 l_progress := 'CHECK_IF_APPROVER: 001';
702
703 --procedure returns Y for all users when AME is not enabled
704 CHECK_IF_AME_ENABLED(result => isAmeEnabled);
705
706 l_progress := 'CHECK_IF_APPROVER: 002 CHECK_IF_AME_ENABLED returns '||isAmeEnabled;
707 if g_fnd_debug = 'Y' then
708 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
709 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
710 END IF;
711 end if;
712
713 if (isAmeEnabled = wf_no) then
714 result := wf_yes;
715 return;
716 end if;
717
718 -- fix for bug 5077461
719 -- getting the next approver whenever check_if_approver is called to handle changes in AME setup
720
721 GET_NEXT_APPROVER(
722 itemtype => wfItemType,
723 itemkey => suppid,
724 actid => 0,
725 funcmode => 'RUN',
726 resultout => result);
727
728 --end fix
729
730 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
731 itemkey => suppid,
732 aname => 'APPROVER_USER_NAME');
733
734 wf_approver_response := wf_engine.GetItemAttrText(itemtype => wfItemType,
735 itemkey => suppid,
736 aname => 'APPROVER_RESPONSE');
737
738 l_progress := 'CHECK_IF_APPROVER: 003 WF attribute APPROVER_USER_NAME - '||wf_approver ||
739 ' username - '||wf_approver||'WF attribute APPROVER_RESPONSE - '||wf_approver_response;
740 if g_fnd_debug = 'Y' then
741 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
742 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
743 END IF;
744 end if;
745
746 if wf_approver = approver AND wf_approver_response = 'NORESPONSE'
747 then
748 --procedure returns Y when username passes is the same as WF attribute APPROVER_USER_NAME
749 --and WF attribute APPROVER_RESPONSE is NORESPONSE
750 result := wf_yes;
751 else
752 result := wf_no;
753 end if;
754
755 exception
756 when others then
757 if g_fnd_debug = 'Y' then
758 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
759 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
760 l_progress ||' sqlerrm - '||sqlerrm);
761 END IF;
762 end if;
763 result := wf_no;
764 end CHECK_IF_APPROVER;
765
766 -------------------------------------------------------------------------------
767 -- PROCEDURE GET_AME_PROCESS_STATUS
768 --
769 -- Procedure to find status of AME WF
770 -- Returns value for attribute AME_PROCESS_STATUS
771 --
772 -- IN
773 -- suppid - itemkey for workflow (SuppRegId)
774 -- OUT
775 -- resultout - INPROCESS/APPROVED/REJECTED/NOAME
776 -------------------------------------------------------------------------------
777 procedure GET_AME_PROCESS_STATUS(
778 suppid in varchar2,
779 result in out nocopy varchar2)
780 is
781 process_status varchar2(20);
782 l_api_name varchar2(50) := 'GET_AME_PROCESS_STATUS';
783 l_progress VARCHAR2(500) := '000';
784 begin
785
786 l_progress := 'GET_AME_PROCESS_STATUS: 001';
787
788 process_status := wf_engine.GetItemAttrText(itemtype => wfItemType,
789 itemkey => suppid,
790 aname => 'AME_PROCESS_STATUS');
791
792 result := process_status;
793
794 l_progress := 'GET_AME_PROCESS_STATUS: 002 suppid - '||suppid||' WF attribute AME_PROCESS_STATUS'||result;
795
796 if g_fnd_debug = 'Y' then
797 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
798 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
799 END IF;
800 end if;
801
802 exception
803 when others then
804 if g_fnd_debug = 'Y' then
805 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
806 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
807 l_progress ||' sqlerrm - '||sqlerrm);
808 END IF;
809 end if;
810 raise;
811 end GET_AME_PROCESS_STATUS;
812
813
814
815 -------------------------------------------------------------------------------
816 -- PROCEDURE PROCESS_APPROVE
817 --
818 -- Procedure to to Approve a Supplier Registration Request
819 -- Called when an Approver approves a request
820 -- Reject in AME
821 -- IN
822 -- suppid - itemkey for workflow (SuppRegId)
823 -- approver - username for user rejecting supplier request
824 -- OUT
825 -- result - SUCCESS/FAILURE
826 -- processresult - APPROVED/REJECTED/INPROCESS/ERROR
827 -------------------------------------------------------------------------------
828 procedure PROCESS_APPROVE(
829 suppid in varchar2,
830 approver in varchar2,
831 result in out nocopy varchar2,
832 processresult in out nocopy varchar2)
833
834 is
835 l_api_name varchar2(50) := 'PROCESS_APPROVE';
836 l_progress VARCHAR2(500) := '000';
837 wf_approver varchar2(100);
838 begin
839
840 l_progress := 'PROCESS_APPROVE: 001';
841
842 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
843 itemkey => suppid,
844 aname => 'APPROVER_USER_NAME');
845
846 l_progress := 'PROCESS_APPROVE: 002 username - '||approver||' approver in WF - '||wf_approver;
847
848 if g_fnd_debug = 'Y' then
849 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
850 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
851 END IF;
852 end if;
853
854 if wf_approver = approver
855 then
856 wf_engine.SetItemAttrText ( itemtype => wfItemType,
857 itemkey => suppid,
858 aname => 'APPROVER_RESPONSE',
859 avalue => 'APPROVE');
860
861 l_progress := 'PROCESS_APPROVE: 003 WF attribute APPROVER_RESPONSE set to APPROVE for itemkey - '||suppid;
862
863 if g_fnd_debug = 'Y' then
864 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
865 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
866 END IF;
867 end if;
868
869 Process_Response_Internal( suppid,'APPROVE');
870
871 l_progress := 'PROCESS_APPROVE: 004 Invoked Process_Response_Internal with attributes - '||suppid||' and APPROVE';
872
873 if g_fnd_debug = 'Y' then
874 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
875 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
876 END IF;
877 end if;
878
879 wf_engine.CompleteActivity( itemtype => wfItemType,
880 itemkey => suppid,
881 activity => 'BLOCK',
882 result => null);
883
884 l_progress := 'PROCESS_APPROVE: 005 Block completed for WF with itemkey - '||suppid;
885 -- Bug 5467675. Commit should never be in the middle of a procedure.
886 -- commit;
887 result := 'SUCCESS';
888
889 if g_fnd_debug = 'Y' then
890 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
891 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
892 END IF;
893 end if;
894
895 else
896
897 if g_fnd_debug = 'Y' then
898 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
899 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
900 ' This should never happenwf_approver != approver l_progress - '||l_progress );
901 END IF;
902 end if;
903
904 result := 'FAILURE';
905 end if;
906
907 GET_AME_PROCESS_STATUS( suppid => suppid,
908 result =>processresult);
909
910 l_progress := 'PROCESS_APPROVE: 006 GET_AME_PROCESS_STATUS returns - '||processresult;
911 if g_fnd_debug = 'Y' then
912 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
913 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
914 END IF;
915 end if;
916
917
918 exception
919 when others then
920 if g_fnd_debug = 'Y' then
921 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
922 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
923 l_progress ||' sqlerrm - '||sqlerrm);
924 END IF;
925 end if;
926 raise;
927 end PROCESS_APPROVE;
928
929
930
931 -------------------------------------------------------------------------------
932 -- PROCEDURE PROCESS_REJECT
933 --
934 -- Procedure to to Reject a Supplier Registration Request
935 -- Called when an Approver rejects a request
936 -- Reject in AME
937 -- IN
938 -- suppid - itemkey for workflow (SuppRegId)
939 -- approver - username for user rejecting supplier request
940 -- OUT
941 -- result - SUCCESS/FAILURE
942 -- processresult
943 -- - APPROVED
944 -- - ERROR
945 -- - REJECTED
946 -- - INPROCESS
947 -------------------------------------------------------------------------------
948 procedure PROCESS_REJECT(
949 suppid in varchar2,
950 approver in varchar2,
951 result in out nocopy varchar2,
952 processresult in out nocopy varchar2)
953 is
954 l_api_name varchar2(50) := 'PROCESS_REJECT';
955 l_progress VARCHAR2(500) := '000';
956 wf_approver varchar2(100);
957 begin
958
959 l_progress := 'PROCESS_REJECT: 001';
960
961 wf_approver := wf_engine.GetItemAttrText(itemtype => wfItemType,
962 itemkey => suppid,
963 aname => 'APPROVER_USER_NAME');
964
965 l_progress := 'PROCESS_REJECT: 002 username - '||approver||' approver in WF - '||wf_approver;
966
967 if g_fnd_debug = 'Y' then
968 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
969 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
970 END IF;
971 end if;
972
973 if wf_approver = approver
974 then
975 wf_engine.SetItemAttrText ( itemtype => wfItemType,
976 itemkey => suppid,
977 aname => 'APPROVER_RESPONSE',
978 avalue => 'REJECT');
979
980
981 l_progress := 'PROCESS_REJECT: 003 WF attribute APPROVER_RESPONSE set to REJECT for itemkey - '||suppid;
982
983 if g_fnd_debug = 'Y' then
984 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
985 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
986 END IF;
987 end if;
988
989
990 Process_Response_Internal(suppid,'REJECT');
991
992 l_progress := 'PROCESS_REJECT: 004 Invoked Process_Response_Internal with attributes - '||suppid||' and REJECT';
993
994 if g_fnd_debug = 'Y' then
995 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
996 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
997 END IF;
998 end if;
999
1000 wf_engine.CompleteActivity( itemtype => wfItemType,
1001 itemkey => suppid,
1002 activity => 'BLOCK',
1003 result => null);
1004
1005 l_progress := 'PROCESS_REJECT: 005 Block completed for WF with itemkey - '||suppid;
1006 -- Bug 5467675. Commit should never be in the middle of a procedure.
1007 -- commit;
1008 result := 'SUCCESS';
1009
1010 if g_fnd_debug = 'Y' then
1011 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1012 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1013 END IF;
1014 end if;
1015
1016 else
1017
1018 if g_fnd_debug = 'Y' then
1019 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1020 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
1021 ' This should never happenwf_approver != approver l_progress - '||l_progress );
1022 END IF;
1023 end if;
1024
1025 result := 'FAILURE';
1026 end if;
1027
1028
1029 GET_AME_PROCESS_STATUS( suppid => suppid,
1030 result =>processresult);
1031
1032 l_progress := 'PROCESS_REJECT: 006 GET_AME_PROCESS_STATUS returns - '||processresult;
1033 if g_fnd_debug = 'Y' then
1034 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1035 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1036 END IF;
1037 end if;
1038
1039 exception
1040 when others then
1041 if g_fnd_debug = 'Y' then
1042 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1043 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name, ' Unexpected Error l_progress - '||
1044 l_progress ||' sqlerrm - '||sqlerrm);
1045 END IF;
1046 end if;
1047 raise;
1048 end PROCESS_REJECT;
1049
1050 -------------------------------------------------------------------------------
1051 -- PROCEDURE CHECK_IF_AME_ENABLED
1052 --
1053 -- Procedure to to find out if AME is enabled for Supplier Approval Management
1054 --
1055 -- IN
1056 --
1057 -- OUT
1058 -- result - Y/N
1059 -------------------------------------------------------------------------------
1060 procedure CHECK_IF_AME_ENABLED(
1061 result in out nocopy varchar2)
1062 is
1063 l_api_name varchar2(50) := 'CHECK_IF_AME_ENABLED';
1064 l_progress VARCHAR2(500) := '000';
1065 begin
1066
1067 l_progress := 'CHECK_IF_AME_ENABLED: 001';
1068
1069 result := NVL(FND_PROFILE.VALUE('POS_SAM_AME_ENABLED'),'N');
1070
1071 l_progress := 'CHECK_IF_AME_ENABLED: 002 -- result :' || result ;
1072
1073 if g_fnd_debug = 'Y' then
1074 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1075 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1076 END IF;
1077 end if;
1078
1079 exception
1080 when others then
1081 if g_fnd_debug = 'Y' then
1082 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1083 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
1084 ' Unexpected Error l_progress - '||l_progress );
1085 END IF;
1086 end if;
1087 raise;
1088 end CHECK_IF_AME_ENABLED;
1089
1090
1091 -------------------------------------------------------------------------------
1092 -- PROCEDURE Process_Response_Internal
1093 --
1094 -- Procedure to approve/reject a Supplier Request in AME
1095 -- Called when Approver responsd to a Supplier Registration Request
1096 --
1097 -- IN
1098 -- suppid - AME transactionId (same as SuppRegId)
1099 -- p_response - APPROVE/REJECT
1100 -- OUT
1101 --
1102 -------------------------------------------------------------------------------
1103 procedure Process_Response_Internal( suppid in varchar2,
1104 p_response in varchar2 ) IS
1105
1106 l_progress VARCHAR2(500) := '000';
1107 l_document_id number;
1108 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
1109 l_current_approver ame_util.approverRecord2;
1110 l_approver_posoition_id number;
1111 l_approver_type varchar2(10);
1112 l_api_name varchar2(50) := 'Process_Response_Internal';
1113
1114 begin
1115
1116 l_progress := 'Process_Response_Internal: 001';
1117 -- IF (g_po_wf_debug = 'Y') THEN
1118 -- PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
1119 --END IF;
1120
1121 l_transaction_type := wf_engine.GetItemAttrText( itemtype => wfItemType,
1122 itemkey => suppid,
1123 aname => 'AME_TRANSACTION_TYPE');
1124
1125 l_approver_type := wf_engine.GetItemAttrText( itemtype => wfItemType,
1126 itemkey => suppid,
1127 aname => 'AME_APPROVER_TYPE');
1128
1129 l_progress := 'Process_Response_Internal: 002 -- l_approver_type :' || l_approver_type ;
1130
1131 if g_fnd_debug = 'Y' then
1132 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1133 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1134 END IF;
1135 end if;
1136
1137 -- bug 5486926
1138 -- for position hierarchy, using POS:<AME_APPROVER_ID> as approver_name
1139 /*
1140 if (l_approver_type = 'POS') then
1141 l_current_approver.orig_system := 'POS';
1142 elsif (l_approver_type = 'FND') then
1143 l_current_approver.orig_system := 'FND';
1144 else
1145 l_current_approver.orig_system := 'PER';
1146 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1147 itemkey => suppid,
1148 aname => 'APPROVER_USER_NAME');
1149 end if;
1150
1151 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1152 itemkey => suppid,
1153 aname => 'APPROVER_USER_NAME');
1154
1155 l_current_approver.orig_system_id := wf_engine.GetItemAttrNumber( itemtype => wfItemType,
1156 itemkey => suppid,
1157 aname => 'AME_APPROVER_ID');
1158 */
1159 --start replace
1160 if (l_approver_type = 'POS') then
1161 l_current_approver.orig_system := 'POS';
1162 l_current_approver.name := 'POS:'||wf_engine.GetItemAttrText( itemtype => wfItemType,
1163 itemkey => suppid,
1164 aname => 'AME_APPROVER_ID');
1165 elsif (l_approver_type = 'FND') then
1166 l_current_approver.orig_system := 'FND';
1167 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1168 itemkey => suppid,
1169 aname => 'APPROVER_USER_NAME');
1170 else
1171 l_current_approver.orig_system := 'PER';
1172 l_current_approver.name := wf_engine.GetItemAttrText( itemtype => wfItemType,
1173 itemkey => suppid,
1174 aname => 'APPROVER_USER_NAME');
1175 end if;
1176
1177 l_current_approver.orig_system_id := wf_engine.GetItemAttrText( itemtype => wfItemType,
1178 itemkey => suppid,
1179 aname => 'AME_APPROVER_ID');
1180 --end replace
1181
1182 l_progress := 'Process_Response_Internal: 003 -- l_current_approver.orig_system_id :' ||
1183 l_current_approver.orig_system_id ;
1184
1185 if g_fnd_debug = 'Y' then
1186 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1187 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1188 END IF;
1189 end if;
1190
1191 if( p_response = 'APPROVE') then
1192 l_current_approver.approval_status := ame_util.approvedStatus;
1193 elsif( p_response = 'REJECT') then
1194 l_current_approver.approval_status := ame_util.rejectStatus;
1195 elsif( p_response = 'TIMEOUT') then
1196 l_current_approver.approval_status := ame_util.noResponseStatus;
1197 end if;
1198
1199 l_progress := 'Process_Response_Internal: 004 -- p_response :' || p_response ;
1200
1201 if g_fnd_debug = 'Y' then
1202 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1203 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1204 END IF;
1205 end if;
1206
1207
1208 --Update the Approval status with the response from the approver.
1209 ame_api2.updateApprovalStatus( applicationIdIn=>ameApplicationId,
1210 transactionIdIn=>suppid,
1211 transactionTypeIn=>ameTransactionType,
1212 approverIn => l_current_approver);
1213
1214 l_progress := 'Process_Response_Internal: 005 -- Updated AME for transactiontype -'||ameTransactionType||
1215 ' and transactionId - '||suppid;
1216
1217 if g_fnd_debug = 'Y' then
1218 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1219 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, l_progress );
1220 END IF;
1221 end if;
1222
1223 exception
1224 when others then
1225 if g_fnd_debug = 'Y' then
1226 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1227 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, g_module_prefix || l_api_name,
1228 ' Unexpected Error l_progress - '||l_progress );
1229 END IF;
1230 end if;
1231 raise;
1232 end Process_Response_Internal;
1233
1234
1235 -------------------------------------------------------------------------------
1236 -- PROCEDURE STARTWF_POSSPAPP
1237 --
1238 -- Procedure to start workflow for AME Approval
1239 -- Called when Prospective supplier registration is submitted
1240 --
1241 -- IN
1242 -- suppid - id for Prospective Supplier (SupplierRegId)
1243 -- requestor - user name of requestor
1244 -- OUT
1245 -- result
1246 -- - SUCCESS
1247 -- When Workflow was completed successfully
1248 -- - FAILURE
1249 -- When Workflow was started successfully
1250 -- processresult
1251 -- - APPROVED
1252 -- - ERROR
1253 -- - REJECTED
1254 -- - INPROCESS
1255 -------------------------------------------------------------------------------
1256
1257 PROCEDURE STARTWF_POSSPAPP (suppid IN VARCHAR2,
1258 suppname IN VARCHAR2,
1259 requestor IN VARCHAR2,
1260 result in out nocopy varchar2,
1261 processresult in out nocopy varchar2)
1262 IS
1263 l_progress NUMBER := 0;
1264 l_api_name varchar2(50) := 'STARTWF_POSSPAPP';
1265 BEGIN
1266
1267 l_progress := 1;
1268
1269 -- create workflow process
1270
1271 wf_engine.CreateProcess(itemtype => wfItemType,
1272 itemkey => suppid, /*using suppid as the itemkey*/
1273 process => wfProcess);
1274
1275
1276 l_progress := 2;
1277
1278 wf_engine.SetItemAttrText (itemtype => wfItemType,
1279 itemkey => suppid,
1280 aname => 'REQUESTOR',
1281 avalue => requestor);
1282
1283 wf_engine.SetItemAttrText (itemtype => wfItemType,
1284 itemkey => suppid,
1285 aname => 'SUPPLIERNAME',
1286 avalue => suppname);
1287
1288 l_progress := 3;
1289
1290 wf_engine.StartProcess(itemtype => wfItemType,
1291 itemkey => suppid );
1292
1293 l_progress := 4;
1294
1295 if g_fnd_debug = 'Y' then
1296 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1297 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name,
1298 'Started workflow itemtype - '|| wfItemType ||' itemkey - '||suppid);
1299 END IF;
1300 end if;
1301
1302 result := 'SUCCESS';
1303
1304 l_progress := 5;
1305
1306 GET_AME_PROCESS_STATUS( suppid => suppid,
1307 result =>processresult);
1308
1309 EXCEPTION WHEN OTHERS THEN
1310
1311 result := 'FAILURE';
1312
1313 IF (g_fnd_debug = 'Y') THEN
1314 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1315 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,g_module_prefix ||l_api_name,
1316 'Error in starting workflow l_progress - ' || l_progress ||' sqlerrm - '||sqlerrm);
1317 END IF;
1318 END IF;
1319
1320 raise;
1321
1322 END STARTWF_POSSPAPP;
1323
1324 --------------------------------------------------------------------------------
1325 --Start of Comments
1326 --Name: get_ame_approval_list_history
1327 --Function:
1328 -- Call AME API to build the approver list history.
1329 --Parameters:
1330 --IN:
1331 -- pProspSupplierId Prospective Supplier Id
1332 --OUT:
1333 -- pApprovalListStr Approval List concatenated in a string
1334 -- pApprovalListCount Number of Approvers.
1335 -- It has a value of 0, if the document does not require approval.
1336 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
1337 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
1338 --End of Comments
1339 --------------------------------------------------------------------------------
1340 procedure get_ame_approval_list_history( pProspSupplierId IN VARCHAR2,
1341 pApprovalListStr OUT NOCOPY VARCHAR2,
1342 pApprovalListCount OUT NOCOPY NUMBER,
1343 pQuoteChar OUT NOCOPY VARCHAR2,
1344 pFieldDelimiter OUT NOCOPY VARCHAR2
1345 ) IS
1346
1347 l_api_name varchar2(50):= 'get_ame_approval_list_history';
1348
1349 approverList ame_util.approversTable2;
1350 l_process_out VARCHAR2(10);
1351
1352 l_full_name per_people_f.full_name%TYPE;
1353 l_person_id per_people_f.person_id%TYPE;
1354 l_job_or_position VARCHAR2(2000);
1355 l_orig_system VARCHAR2(10);
1356 l_orig_system_id NUMBER;
1357
1358 l_job_id number;
1359 l_position_id number;
1360 l_valid_approver VARCHAR2(1);
1361
1362 BEGIN
1363
1364 if g_fnd_debug = 'Y' then
1365 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1366 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_ame_approval_list...');
1367 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pProspSupplierId :' ||
1368 pProspSupplierId );
1369 END IF;
1370 end if;
1371
1372 pQuoteChar :=quoteChar;
1373 pFieldDelimiter :=fieldDelimiter;
1374
1375 approvalListStr := NULL;
1376 pApprovalListCount := 0;
1377
1378
1379 ame_api2.getAllApprovers7( applicationIdIn => ameApplicationId,
1380 transactionIdIn => pProspSupplierId,
1381 transactionTypeIn => ameTransactionType,
1382 approvalProcessCompleteYNOut => l_process_out,
1383 approversOut => approverList
1384 );
1385 -- Iterate through the list of approvers.
1386 for i in 1 .. approverList.count loop
1387
1388 l_valid_approver := 'Y';
1389 if g_fnd_debug = 'Y' then
1390 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1391 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Processing the approver :' || i );
1392 END IF;
1393 end if;
1394
1395 -- do not consider the deleted approver.
1396 --if the approval_status value is SUPPRESSED, then the user is deleted from the list.
1397 if( ( ( l_process_out = 'Y' OR l_process_out = 'N' ) AND
1398 --changing the logic from AP implementation
1399 --( approverList(i).approval_status is not null AND approverList(i).approval_status <> 'SUPPRESSED' )
1400 ( approverList(i).approval_status is null OR approverList(i).approval_status <> 'SUPPRESSED' )
1401 ) OR ( ( l_process_out = 'W' OR l_process_out = 'P' )AND
1402 (approverList(i).approval_status is null OR approverList(i).approval_status <> 'SUPPRESSED'))) then
1403
1404 l_orig_system := approverList(i).orig_system;
1405 l_orig_system_id := approverList(i).orig_system_id;
1406 l_job_or_position := NULL;
1407
1408 if ( l_orig_system = 'PER') then
1409
1410 -- Employee Supervisor Record.
1411 if g_fnd_debug = 'Y' then
1412 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1413 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Emp - Sup Record ...');
1414 END IF;
1415 end if;
1416
1417 l_full_name := approverList(i).display_name;
1418
1419 l_person_id := l_orig_system_id;
1420
1421 elsif ( l_orig_system = 'POS') then
1422
1423 -- Position Hierarchy Record. The logic is mentioned in the comments section.
1424 if g_fnd_debug = 'Y' then
1425 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1426 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Position Hierarchy Record ...');
1427 END IF;
1428 end if;
1429
1430 begin
1431 SELECT person_id, full_name into l_person_id,l_full_name FROM (
1432 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
1433 WHERE asg.position_id = l_orig_system_id and trunc(sysdate) between person.effective_start_date
1434 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1435 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1436 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1437 and asg.assignment_status_type_id not in (
1438 SELECT assignment_status_type_id FROM per_assignment_status_types
1439 WHERE per_system_status = 'TERM_ASSIGN'
1440 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
1441 order by person.last_name
1442 ) where rownum = 1;
1443 exception
1444 WHEN NO_DATA_FOUND THEN
1445 --RAISE;
1446 l_valid_approver := 'N';
1447 END;
1448
1449 elsif (l_orig_system = 'FND' OR l_orig_system = 'FND_USR' ) then
1450
1451 -- FND User Record.
1452 if g_fnd_debug = 'Y' then
1453 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1454 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'FND User Record ...');
1455 END IF;
1456 end if;
1457
1458 SELECT employee_id into l_person_id
1459 FROM fnd_user
1460 WHERE user_id = l_orig_system_id
1461 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
1462
1463 l_full_name := approverList(i).display_name;
1464
1465 end if;
1466
1467 if g_fnd_debug = 'Y' then
1468 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1469 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_full_name :' || l_full_name );
1470 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_person_id :' || l_person_id );
1471 END IF;
1472 end if;
1473
1474 -- Find position | job name
1475 if ( l_orig_system = 'POS' ) then
1476 l_job_or_position := approverList(i).display_name;
1477 else
1478 l_job_or_position := null;
1479 end if;
1480 -- Make sure position/job name is populated.
1481 if( l_job_or_position is null ) then
1482
1483 -- retrieve the position name. if the position name is null check for the job name.
1484
1485 SELECT position_id, job_id INTO l_position_id, l_job_id
1486 FROM per_all_assignments_f
1487 WHERE person_id = l_person_id
1488 and primary_flag = 'Y' and assignment_type in ('E','C')
1489 and assignment_status_type_id not in
1490 (select assignment_status_type_id
1491 from per_assignment_status_types
1492 where per_system_status = 'TERM_ASSIGN')
1493 and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE)
1494 AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
1495 and rownum = 1;
1496
1497 if l_position_id is not null then
1498 SELECT name INTO l_job_or_position FROM per_all_positions WHERE position_id = l_position_id;
1499 end if;
1500
1501 if l_job_or_position is null and l_job_id is not null then
1502 SELECT name INTO l_job_or_position FROM per_jobs WHERE job_id = l_job_id;
1503 end if;
1504
1505 if g_fnd_debug = 'Y' then
1506 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1507 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_job_or_position :' || l_job_or_position );
1508 END IF;
1509 end if;
1510
1511 end if;
1512
1513 if( l_valid_approver = 'Y' ) then
1514 marshalField(l_full_name, quoteChar, fieldDelimiter);
1515 marshalField( to_char(l_person_id), quoteChar, fieldDelimiter);
1516 marshalField(l_job_or_position, quoteChar, fieldDelimiter);
1517 marshalField(approverList(i).name, quoteChar, fieldDelimiter);
1518
1519 --marshalField(approversTableIn(i).orig_system, quoteChar, fieldDelimiter);
1520 --marshalField(to_char(approversTableIn(i).orig_system_id), quoteChar, fieldDelimiter);
1521
1522 marshalField(l_orig_system, quoteChar, fieldDelimiter);
1523 marshalField(to_char(l_orig_system_id), quoteChar, fieldDelimiter);
1524
1525 marshalField(approverList(i).api_insertion, quoteChar, fieldDelimiter);
1526 marshalField(approverList(i).authority, quoteChar, fieldDelimiter);
1527 marshalField(approverList(i).approval_status, quoteChar, fieldDelimiter);
1528 marshalField(approverList(i).approver_category, quoteChar, fieldDelimiter);
1529 marshalField(approverList(i).approver_order_number, quoteChar, fieldDelimiter);
1530 marshalField(approverList(i).action_type_id, quoteChar, fieldDelimiter);
1531
1532 --changing the logic from AP implementation
1533 --marshalField(approverList(i).group_or_chain_id, quoteChar, fieldDelimiter);
1534
1535 marshalField('', quoteChar, fieldDelimiter);
1536 marshalField(approverList(i).member_order_number, quoteChar, fieldDelimiter);
1537 --marshalField(to_char(i), quoteChar, fieldDelimiter);
1538 pApprovalListCount := pApprovalListCount +1;
1539 marshalField(to_char(pApprovalListCount), quoteChar, fieldDelimiter);
1540 end if;
1541
1542 end if;
1543
1544 end loop;
1545
1546 pApprovalListStr := approvalListStr;
1547
1548 if g_fnd_debug = 'Y' then
1549 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1550 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_ame_approval_list...');
1551 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
1552 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
1553 END IF;
1554 end if;
1555
1556 exception
1557 when NO_DATA_FOUND then
1558 pApprovalListCount := 0;
1559 pApprovalListStr := 'NO_DATA_FOUND';
1560 if g_fnd_debug = 'Y' then
1561 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1562 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1563 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
1564 END IF;
1565 end if;
1566
1567 when others then
1568 pApprovalListCount := 0;
1569 pApprovalListStr := 'EXCEPTION';
1570 if g_fnd_debug = 'Y' then
1571 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1572 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1573 l_api_name || '.others_exception', sqlerrm);
1574 END IF;
1575
1576 end if;
1577
1578 END get_ame_approval_list_history;
1579
1580 end POS_SUPP_APPR;