[Home] [Help]
PACKAGE BODY: APPS.AP_WFAPPROVAL_PKG
Source
1 PACKAGE BODY AP_WFAPPROVAL_PKG AS
2 /* $Header: apiawgeb.pls 120.3.12000000.3 2007/07/20 07:09:22 schamaku ship $ */
3 -- Public Procedure Specifications
4
5 -- Procedure Definitions
6
7 FUNCTION ap_accounting_flex(p_ccid IN NUMBER,
8 p_seg_name IN VARCHAR2,
9 p_set_of_books_id IN NUMBER ) RETURN VARCHAR2 IS
10
11 l_segments FND_FLEX_EXT.SEGMENTARRAY;
12 l_result BOOLEAN;
13 l_chart_of_accounts_id NUMBER;
14 l_num_segments NUMBER;
15 l_segment_num NUMBER;
16 l_reason_flex VARCHAR2(2000):='';
17 l_segment_delimiter VARCHAR2(1);
18 current_calling_sequence VARCHAR2(2000);
19 l_seg_val VARCHAR2(50);
20 BEGIN
21 SELECT chart_of_accounts_id
22 INTO l_chart_of_accounts_id
23 FROM gl_sets_of_books
24 WHERE set_of_books_id = p_set_of_books_id;
25
26 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
27 'SQLGL',
28 'GL#',
29 l_chart_of_accounts_id);
30
31 IF (NOT l_result) THEN
32 l_reason_flex := FND_MESSAGE.GET;
33 END IF;
34
35 l_result := FND_FLEX_EXT.GET_SEGMENTS(
36 'SQLGL',
37 'GL#',
38 l_chart_of_accounts_id,
39 p_ccid,
40 l_num_segments,
41 l_segments);
42
43 IF (NOT l_result) THEN
44 l_reason_flex := FND_MESSAGE.GET;
45 END IF;
46
47 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
48 101,
49 'GL#',
50 l_chart_of_accounts_id,
51 p_seg_name,
52 l_segment_num);
53
54
55 IF (NOT l_result) THEN
56 l_reason_flex := FND_MESSAGE.GET;
57 END IF;
58
59 l_seg_val := l_segments(l_segment_num);
60
61 return l_seg_val;
62 END;
63
64 FUNCTION ap_dist_accounting_flex(p_seg_name IN VARCHAR2,
65 p_dist_id IN NUMBER) RETURN VARCHAR2 IS
66
67 l_segments FND_FLEX_EXT.SEGMENTARRAY;
68 l_result BOOLEAN;
69 l_chart_of_accounts_id NUMBER;
70 l_num_segments NUMBER;
71 l_segment_num NUMBER;
72 l_reason_flex VARCHAR2(2000):='';
73 l_segment_delimiter VARCHAR2(1);
74 current_calling_sequence VARCHAR2(2000);
75 l_seg_val VARCHAR2(50);
76 l_ccid NUMBER;
77 l_sob NUMBER;
78
79 BEGIN
80
81 SELECT dist_code_combination_id,set_of_books_id
82 INTO l_ccid,l_sob
83 FROM ap_invoice_distributions_all
84 WHERE invoice_distribution_id=p_dist_id;
85
86 SELECT chart_of_accounts_id
87 INTO l_chart_of_accounts_id
88 FROM gl_sets_of_books
89 WHERE set_of_books_id = l_sob;
90
91 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
92 'SQLGL',
93 'GL#',
94 l_chart_of_accounts_id);
95 IF (NOT l_result) THEN
96 l_reason_flex := FND_MESSAGE.GET;
97 END IF;
98
99 l_result := FND_FLEX_EXT.GET_SEGMENTS(
100 'SQLGL',
101 'GL#',
102 l_chart_of_accounts_id,
103 l_ccid,
104 l_num_segments,
105 l_segments);
106
107 IF (NOT l_result) THEN
108 l_reason_flex := FND_MESSAGE.GET;
109 END IF;
110
111 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
112 101,
113 'GL#',
114 l_chart_of_accounts_id,
115 p_seg_name,
116 l_segment_num);
117 IF (NOT l_result) THEN
118 l_reason_flex := FND_MESSAGE.GET;
119 END IF;
120
121 l_seg_val := l_segments(l_segment_num);
122
123 return l_seg_val;
124 END;
125
126 PROCEDURE iaw_po_check(itemtype IN VARCHAR2,
127 itemkey IN VARCHAR2,
128 actid IN NUMBER,
129 funcmode IN VARCHAR2,
130 resultout OUT NOCOPY VARCHAR2 ) IS
131
132 l_po_count NUMBER;
133 l_check_PO_match VARCHAR2(3);
134 l_org_id NUMBER;
135 l_debug VARCHAR2(240);
136
137 BEGIN
138
139 --check 'Approve PO Matched' flag here
140 l_check_PO_match := WF_ENGINE.GetItemAttrText(itemtype,
141 itemkey,
142 'APINV_AAPO');
143
144 --we need to get the org_id until I can change the raise event
145 --in the invoice workbench
146
147 SELECT org_id
148 INTO l_org_id
149 FROM ap_invoices_all
150 WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);
151
152 -- lets go ahead and set the wf attribute
153 WF_ENGINE.SETITEMATTRNumber(itemtype,
154 itemkey,
155 'APINV_AOI',
156 l_org_id);
157
158 --Now set the environment
159 fnd_client_info.set_org_context(l_org_id);
160
161
162 IF l_check_PO_match = 'Y' THEN
163
164 SELECT count(invoice_distribution_id)
165 INTO l_po_count
166 FROM ap_invoice_distributions
167 WHERE po_distribution_id is null
168 AND invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);
169
170
171 IF nvl(l_po_count,0) = 0 THEN
172 resultout := wf_engine.eng_completed||':'||'Y';
173 --update invoice status
174 UPDATE AP_INVOICES
175 SET wfapproval_status = 'NOT REQUIRED'
176 WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
177 AND wfapproval_status <> 'MANUALLY APPROVED';
178 ELSE
179 resultout := wf_engine.eng_completed||':'||'N';
180 END IF;
181 ELSE
182 resultout := wf_engine.eng_completed||':'||'N';
183 END IF;
184
185 WF_ENGINE.SETITEMATTRText(itemtype,
186 itemkey,
187 'APINV_ADB',
188 l_debug);
189 EXCEPTION
190
191 WHEN FND_API.G_EXC_ERROR
192 THEN
193 WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);
194 RAISE;
195
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
197 THEN
198 WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);
199 RAISE;
200
201 WHEN OTHERS
202 THEN
203 WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);
204 RAISE;
205
206 END;
207
208 PROCEDURE get_approver(itemtype IN VARCHAR2,
209 itemkey IN VARCHAR2,
210 actid IN NUMBER,
211 funcmode IN VARCHAR2,
212 resultout OUT NOCOPY VARCHAR2 ) IS
213
214 l_next_approver AME_UTIL.approverRecord;
215 l_admin_approver AME_UTIL.approverRecord;
216 l_ret_approver VARCHAR2(50);
217 l_name VARCHAR2(30);
218 l_display_name VARCHAR2(150);
219 l_debug_info VARCHAR2(50);
220 l_role VARCHAR2(50);
221 l_role_display VARCHAR2(150);
222 l_org_id NUMBER(15);
223 l_error_message VARCHAR2(2000);
224 l_invoice_id NUMBER(15);
225 l_iteration NUMBER(9);
226 l_count NUMBER(9);
227 l_orig_system WF_ROLES.ORIG_SYSTEM%TYPE; -- bug 4961253
228 l_orig_sys_id WF_ROLES.ORIG_SYSTEM_ID%TYPE; -- bug 4961253
229
230 BEGIN
231
232 AME_API.getNextApprover(200,
233 substr(itemkey, 1, instr(itemkey,'_')-1),
234 'APINV',
235 l_next_approver);
236
237 --Bug 2743734 instead of checking against admin approver, checking
238 -- next approver status
239 IF l_next_approver.approval_status = ame_util.exceptionStatus THEN
240 -- raise EXCEPTION
241 l_debug_info := 'Error in AME_API.getNextApprover call';
242 APP_EXCEPTION.RAISE_EXCEPTION;
243
244 END IF;
245
246 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
247 itemkey,
248 'APINV_AOI');
249
250 l_invoice_id := substr(itemkey, 1, instr(itemkey,'_')-1);
251 l_iteration := substr(itemkey, instr(itemkey,'_')+1, length(itemkey));
252
253 --Now set the environment
254 fnd_client_info.set_org_context(l_org_id);
255 -- bug 4961253 added user_id condition.
256 IF l_next_approver.person_id is null
257 AND l_next_approver.user_id is null THEN /*no approver on the list*/
258
259 resultout := wf_engine.eng_completed||':'||'N';
260
261 --check for prior approvers
262 SELECT count(*)
263 INTO l_count
264 FROM ap_inv_aprvl_hist
265 WHERE invoice_id = l_invoice_id
266 AND iteration = l_iteration
267 AND RESPONSE <> 'MANUALLY APPROVED';
268
269 IF l_count >0 THEN
270 --update invoice header status
271 UPDATE AP_INVOICES
272 SET wfapproval_status = 'WFAPPROVED'
273 WHERE invoice_id = l_invoice_id
274 AND wfapproval_status <> 'MANUALLY APPROVED';
275 ELSE
276 UPDATE AP_INVOICES
277 SET wfapproval_status = 'NOT REQUIRED'
278 WHERE invoice_id = l_invoice_id
279 AND wfapproval_status <> 'MANUALLY APPROVED';
280 END IF;
281
282 ELSE /*have approver*/
283 -- bug 4961253 initialise the variables.
284 IF l_next_approver.person_id is not null THEN
285 -- Approver is a HR employee
286 l_orig_system := 'PER';
287 l_orig_sys_id := l_next_approver.person_id;
288 ELSE
289 -- Approver is a FND user
290 l_orig_system := 'FND_USR';
291 l_orig_sys_id := l_next_approver.user_id;
292 END IF;
293 -- end bug 4961253
294
295 WF_DIRECTORY.GetRoleName(l_orig_system, -- bug 4961253
296 l_orig_sys_id, -- bug 4961253
297 l_role,
298 l_role_display);
299
300 WF_DIRECTORY.GetUserName(l_orig_system, -- bug 4961253
301 l_orig_sys_id, -- bug 4961253
302 l_name,
303 l_display_name);
304
305
306 WF_ENGINE.SetItemAttrText(itemtype,
307 itemkey,
308 'APINV_ANA',
309 l_display_name);
310
311 WF_ENGINE.SetItemAttrNumber(itemtype,
312 itemkey,
313 'APINV_ANAI',
314 l_orig_sys_id); --bug4961253
315
316 WF_ENGINE.SetItemAttrText(itemtype,
317 itemkey,
318 'APINV_ARN',
319 l_role);
320
321 WF_ENGINE.SetItemAttrText(itemtype,
322 itemkey,
323 'APINV_AAN',
324 l_display_name);
325
326
327 --call set attributes so that notification tokens will be correct
328 set_attribute_values(itemtype,itemkey);
329
330 resultout := wf_engine.eng_completed||':'||'Y';
331
332 insert_history(itemtype,itemkey);
333 END IF;
334 WF_ENGINE.SETITEMATTRText(itemtype,
335 itemkey,
336 'APINV_ADB',
337 l_debug_info);
338
339 EXCEPTION
340
341 WHEN OTHERS THEN
342 Wf_Core.Context('APINV', 'Get_Approver',
343 itemtype, itemkey, to_char(actid), l_debug_info);
344 raise;
345 END Get_Approver;
346
347 PROCEDURE update_history(itemtype IN VARCHAR2,
348 itemkey IN VARCHAR2,
349 actid IN NUMBER,
350 funcmode IN VARCHAR2,
351 resultout OUT NOCOPY VARCHAR2 ) IS
352
353 l_next_approver AME_UTIL.approverRecord;
354 l_admin_approver AME_UTIL.approverRecord;
355 l_ret_approver VARCHAR2(50);
356 l_name VARCHAR2(30);
357 l_display_name VARCHAR2(150);
358 l_debug_info VARCHAR2(50);
359 l_approver VARCHAR2(150);
360 l_approver_id NUMBER(15);
361 l_invoice_id NUMBER(15);
362 l_result VARCHAR2(50);
363 l_hist_id NUMBER(15);
364 l_comments VARCHAR2(240);
365 l_amount ap_invoices_all.invoice_amount%TYPE;
366 l_status VARCHAR2(50);
367 l_org_id NUMBER(15);
368 l_user_id NUMBER(15);
369 l_login_id NUMBER(15);
370
371
372 BEGIN
373 --Get attribute values to update the history table
374
375 l_approver := WF_ENGINE.GetItemAttrText(itemtype,
376 itemkey,
377 'APINV_ANA');
378
379 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
380 itemkey,
381 'APINV_ANAI');
382
383 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
384 itemkey,
385 'APINV_AII');
386
387 l_comments := WF_ENGINE.GetItemAttrText(itemtype,
388 itemkey,
389 'APINV_AC');
390
391 l_hist_id := WF_ENGINE.GetItemAttrNumber(itemtype,
392 itemkey,
393 'APINV_AHI');
394
395 --Bug 2685695
396 l_result := WF_ENGINE.GetActivityAttrText(itemtype,
397 itemkey,
398 actid,
399 'APINV_RSLT');
400
401 l_amount := WF_ENGINE.GetItemAttrNumber(itemtype,
402 itemkey,
403 'APINV_AIA');
404
405 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
406 itemkey,
407 'APINV_AOI');
408
409 --Now set the environment
410 fnd_client_info.set_org_context(l_org_id);
411
412
413 WF_ENGINE.SetItemAttrText(itemtype,
414 itemkey,
415 'APINV_APC',
416 l_comments);
417
418
419 --update AME with response
420 IF l_result = 'APPROVED' THEN
421 AME_API.updateApprovalStatus2(applicationIdIn => 200,
422 transactionIdIn => to_char(l_invoice_id),
423 approvalStatusIn => AME_UTIL.approvedStatus,
424 approverPersonIdIn => l_approver_id,
425 approverUserIdIn => NULL,
426 transactionTypeIn => 'APINV');
427
428 ELSE
429 AME_API.updateApprovalStatus2(applicationIdIn => 200,
430 transactionIdIn => to_char(l_invoice_id),
431 approvalStatusIn => AME_UTIL.rejectStatus,
432 approverPersonIdIn => l_approver_id,
433 approverUserIdIn => NULL,
434 transactionTypeIn => 'APINV');
435 END IF;
436
437 --Bug 2674037 set to -1 if responding to email notification
438 l_user_id := nvl(to_number(fnd_profile.value('USER_ID')),-1);
439 l_login_id := nvl(to_number(fnd_profile.value('LOGIN_ID')),-1);
440
441 IF l_result = 'APPROVED' THEN
442 l_result := 'WFAPPROVED';
443 END IF;
444
445 --update the history table
446 UPDATE AP_INV_APRVL_HIST
447 SET RESPONSE = l_result,
448 APPROVER_COMMENTS = l_comments,
449 AMOUNT_APPROVED = l_amount,
450 LAST_UPDATE_DATE = sysdate,
451 LAST_UPDATED_BY = l_user_id,
452 LAST_UPDATE_LOGIN = l_login_id
453 WHERE APPROVAL_HISTORY_ID = l_hist_id;
454
455 IF l_result = 'REJECTED' THEN
456 --update invoice status
457 UPDATE AP_INVOICES
458 SET wfapproval_status = l_result
459 WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
460 AND wfapproval_status <> 'MANUALLY APPROVED';
461 END IF;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 Wf_Core.Context('APINV', 'update_history',
466 itemtype, itemkey, to_char(actid), l_debug_info);
467 raise;
468
469 END update_history;
470
471
472 PROCEDURE insert_history(itemtype IN VARCHAR2,
473 itemkey IN VARCHAR2 ) IS
474
475 l_next_approver AME_UTIL.approverRecord;
476 l_admin_approver AME_UTIL.approverRecord;
477 l_ret_approver VARCHAR2(50);
478 l_name VARCHAR2(30);
479 l_display_name VARCHAR2(150);
480 l_debug_info VARCHAR2(50);
481 l_name VARCHAR2(30);
482 l_approver VARCHAR2(150);
483 l_approver_id NUMBER(15);
484 l_invoice_id NUMBER(15);
485 l_result VARCHAR2(50);
486 l_org_id NUMBER(15);
487 l_comments VARCHAR2(240);
488 l_iteration NUMBER(9);
489 l_hist_id NUMBER(15);
490 l_amount ap_invoices_all.invoice_amount%TYPE;
491
492 BEGIN
493 --Get attribute values to create record in the history table
494
495 l_approver := WF_ENGINE.GetItemAttrText(itemtype,
496 itemkey,
497 'APINV_ANA');
498
499 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
500 itemkey,
501 'APINV_ANAI');
502
503 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
504 itemkey,
505 'APINV_AII');
506
507 l_iteration := WF_ENGINE.GetItemAttrNumber(itemtype,
508 itemkey,
509 'APINV_AI');
510
511 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
512 itemkey,
513 'APINV_AOI');
514
515 l_amount := WF_ENGINE.GetItemAttrNumber(itemtype,
516 itemkey,
517 'APINV_AIA');
518
519
520 --Now set the environment
521 fnd_client_info.set_org_context(l_org_id);
522
523 SELECT AP_INV_APRVL_HIST_S.nextval
524 INTO l_hist_id
525 FROM dual;
526
527 --insert into the history table
528 INSERT INTO AP_INV_APRVL_HIST
529 (APPROVAL_HISTORY_ID
530 ,INVOICE_ID
531 ,ITERATION
532 ,RESPONSE
533 ,APPROVER_ID
534 ,APPROVER_NAME
535 ,CREATED_BY
536 ,CREATION_DATE
537 ,LAST_UPDATE_DATE
538 ,LAST_UPDATED_BY
539 ,LAST_UPDATE_LOGIN
540 ,ORG_ID
541 ,AMOUNT_APPROVED)
542 VALUES (
543 l_hist_id,
544 l_invoice_id,
545 l_iteration,
546 'PENDING',
547 l_approver_id,
548 l_approver,
549 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
550 sysdate,
551 sysdate,
552 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
553 nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1),
554 l_org_id,
555 l_amount);
556
557 WF_ENGINE.SetItemAttrNumber(itemtype,
558 itemkey,
559 'APINV_AHI',
560 l_hist_id);
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 Wf_Core.Context('APINV', 'insert_history',
565 itemtype, itemkey, l_debug_info);
566 raise;
567
568 END insert_history;
569
570 PROCEDURE insert_history(p_invoice_id IN NUMBER,
571 p_iteration IN NUMBER,
572 p_org_id IN NUMBER,
573 p_status IN VARCHAR2) IS
574 l_hist_id NUMBER;
575 l_amount ap_invoices_all.invoice_amount%TYPE;
576 BEGIN
577 --insert into the history table
578 SELECT AP_INV_APRVL_HIST_S.nextval
579 INTO l_hist_id
580 FROM dual;
581
582 SELECT invoice_amount
583 INTO l_amount
584 FROM AP_INVOICES_ALL
585 WHERE invoice_id = p_invoice_id;
586
587 INSERT INTO AP_INV_APRVL_HIST
588 (APPROVAL_HISTORY_ID
589 ,INVOICE_ID
590 ,ITERATION
591 ,RESPONSE
592 ,APPROVER_ID
593 ,APPROVER_NAME
594 ,AMOUNT_APPROVED
595 ,CREATED_BY
596 ,CREATION_DATE
597 ,LAST_UPDATE_DATE
598 ,LAST_UPDATED_BY
599 ,LAST_UPDATE_LOGIN
600 ,ORG_ID)
601 VALUES (
602 l_hist_id,
603 p_invoice_id,
604 p_iteration,
605 p_status,
606 NULL,
607 FND_PROFILE.VALUE('USERNAME'),
608 l_amount,
609 TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
610 sysdate,
611 sysdate,
612 TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
613 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
614 p_org_id);
615
616 commit;
617
618 END insert_history;
619
620 PROCEDURE escalate_request(itemtype IN VARCHAR2,
621 itemkey IN VARCHAR2,
622 actid IN NUMBER,
623 funcmode IN VARCHAR2,
624 resultout OUT NOCOPY VARCHAR2 ) IS
625
626 l_esc_approver AME_UTIL.approverRecord;
627 l_admin_approver AME_UTIL.approverRecord;
628 l_ret_approver VARCHAR2(50);
629 l_name VARCHAR2(30);
630 l_display_name VARCHAR2(150);
631 l_debug_info VARCHAR2(50);
632 l_manager_id NUMBER(15);
633 l_employee_id NUMBER(15);
634 l_invoice_id NUMBER(15);
635 l_hist_id NUMBER(15);
636 l_role VARCHAR2(50);
637 l_role_display VARCHAR2(150);
638 l_org_id NUMBER(15);
639
640 BEGIN
641
642 /*Get the current approver's manager*/
643 l_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
644 itemkey,
645 'APINV_ANAI');
646
647 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
648 itemkey,
649 'APINV_AOI');
650
651 --Now set the environment
652 fnd_client_info.set_org_context(l_org_id);
653
654 --see if we have an HR api for this select
655 SELECT supervisor_id, first_name, last_name
656 INTO l_manager_id, l_esc_approver.first_name, l_esc_approver.last_name
657 FROM per_employees_current_x
658 WHERE employee_id = l_employee_id;
659
660 WF_DIRECTORY.GetUserName('PER',
661 l_manager_id,
662 l_name,
663 l_display_name);
664
665 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
666 itemkey,
667 'APINV_AII');
668
669 l_hist_id := WF_ENGINE.GetItemAttrNumber(itemtype,
670 itemkey,
671 'APINV_AHI');
672
673 l_esc_approver.user_id := NULL;
674 l_esc_approver.person_id := l_manager_id;
675 l_esc_approver.api_insertion := ame_util.apiInsertion;
676 l_esc_approver.authority := ame_util.authorityApprover;
677 l_esc_approver.approval_status := ame_util.forwardStatus;
678
679 --update AME
680 /*AME_API.updateApprovalStatus2(200,
681 l_invoice_id,
682 ame_util.noResponseStatus,
683 l_employee_id,
684 null,
685 'APINV',
686 l_esc_approver);*/
687
688 AME_API.updateApprovalStatus2(applicationIdIn => 200,
689 transactionIdIn => to_char(l_invoice_id),
690 approvalStatusIn => AME_UTIL.noResponseStatus,
691 approverPersonIdIn => l_employee_id,
692 approverUserIdIn => NULL,
693 transactionTypeIn => 'APINV',
694 forwardeeIn => l_esc_approver);
695
696 --update the history table
697 UPDATE AP_INV_APRVL_HIST
698 SET RESPONSE = 'ESCALATED'
699 WHERE APPROVAL_HISTORY_ID = l_hist_id;
700
701 WF_DIRECTORY.GetRoleName('PER',l_manager_id,l_role,l_role_display);
702
703 WF_ENGINE.SetItemAttrText(itemtype,
704 itemkey,
705 'APINV_ANA',
706 l_display_name);
707
708 WF_ENGINE.SetItemAttrNumber(itemtype,
709 itemkey,
710 'APINV_ANAI',
711 l_manager_id);
712
713 WF_ENGINE.SetItemAttrText(itemtype,
714 itemkey,
715 'APINV_ARN',
716 l_role);
717
718 insert_history(itemtype,itemkey);
719
720 END escalate_request;
721
722 PROCEDURE set_attribute_values(itemtype IN VARCHAR2,
723 itemkey IN VARCHAR2 ) IS
724
725 l_ret_approver VARCHAR2(50);
726 l_name VARCHAR2(30);
727 l_display_name VARCHAR2(150);
728 l_debug_info VARCHAR2(50);
729 l_name VARCHAR2(30);
730 l_approver VARCHAR2(150);
731 l_approver_id NUMBER(15);
732 l_invoice_id NUMBER(15);
733 l_result VARCHAR2(50);
734 l_org_id NUMBER(15);
735 l_comments VARCHAR2(240);
736 l_iteration NUMBER(9);
737 l_vendor_site_code VARCHAR2(15);
738 l_vendor_name po_vendors.vendor_name%TYPE;
739 l_description VARCHAR2(240);
740 l_currency VARCHAR2(15);
741 l_vendor_id NUMBER(15);
742 l_vendor_site_id NUMBER(15);
743 l_amount ap_invoices_all.invoice_amount%TYPE;
744 l_invoice_num VARCHAR(50);
745 l_invoice_date DATE;
746 l_prev_com VARCHAR2(240);
747 l_dsp_format VARCHAR2(50);
748 l_dsp_amount VARCHAR2(100);
749 l_po_num VARCHAR2(20);
750 l_po_count NUMBER(9);
751 --bug 2785396
752 l_requester_id NUMBER(15);
753 l_requester_name VARCHAR(250);
754
755
756 BEGIN
757
758 --we need to get the org_id until I can change the raise event
759 --in the invoice workbench
760
761 SELECT org_id
762 INTO l_org_id
763 FROM ap_invoices_all
764 WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);
765
766 -- lets go ahead and set the wf attribute again
767 WF_ENGINE.SETITEMATTRNumber(itemtype,
768 itemkey,
769 'APINV_AOI',
770 l_org_id);
771
772 --Now set the environment
773 fnd_client_info.set_org_context(l_org_id);
774
775 --set env so will not need to access all table
776 SELECT approval_iteration,
777 vendor_id,
778 vendor_site_id,
779 invoice_amount,
780 description,
781 invoice_currency_code,
782 org_id,
783 invoice_id,
784 invoice_num,
785 invoice_date,
786 requester_id
787 INTO
788 l_iteration,
789 l_vendor_id,
790 l_vendor_site_id,
791 l_amount,
792 l_description,
793 l_currency,
794 l_org_id,
795 l_invoice_id,
796 l_invoice_num,
797 l_invoice_date,
798 l_requester_id
799 FROM AP_INVOICES
800 WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);
801
802 SELECT vendor_name
803 INTO l_vendor_name
804 FROM PO_VENDORS
805 WHERE vendor_id = l_vendor_id;
806
807 SELECT vendor_site_code
808 INTO l_vendor_site_code
809 FROM PO_VENDOR_SITES
810 WHERE vendor_site_id = l_vendor_site_id;
811
812 SELECT count(invoice_distribution_id)
813 INTO l_po_count
814 FROM ap_invoice_distributions
815 WHERE invoice_id = l_invoice_id
816 and po_distribution_id is not null;
817
818 IF l_po_count >1 THEN
819 SELECT displayed_field
820 INTO l_po_num
821 FROM ap_lookup_codes
822 WHERE lookup_code = 'MULTIPLE'
823 AND lookup_type = 'NLS TRANSLATION';
824 ELSIF l_po_count = 1 THEN
825 SELECT poh.segment1
826 INTO l_po_num
827 FROM ap_invoice_distributions aid,
828 po_distributions pod,
829 po_headers poh
830 WHERE aid.invoice_id = l_invoice_id
831 AND aid.po_distribution_id = pod.po_distribution_id
832 AND pod.po_header_id = poh.po_header_id;
833 ELSE
834 l_po_num := '';
835 END IF;
836
837 --Bug 2785396 get requester name
838 IF l_requester_id IS NOT NULL THEN
839 SELECT full_name
840 INTO l_requester_name
841 FROM per_all_people_f pap
842 WHERE person_id = l_requester_id
843 and trunc(sysdate) between effective_start_date --bug3815124
844 and nvl(effective_end_date,trunc(sysdate));
845
846 --Bug 2785396 Set requester name
847 WF_ENGINE.SetItemAttrText(itemtype,
848 itemkey,
849 'APINV_ARQN',
850 l_requester_name);
851 ELSE
852 l_requester_name := '';
853 END IF;
854
855
856 --Set attribute values in WF
857
858 WF_ENGINE.SetItemAttrText(itemtype,
859 itemkey,
860 'APINV_APON',
861 l_po_num);
862
863 WF_ENGINE.SetItemAttrText(itemtype,
864 itemkey,
865 'APINV_AIDE',
866 l_description);
867
868 WF_ENGINE.SetItemAttrText(itemtype,
869 itemkey,
870 'APINV_AIC',
871 l_currency);
872
873 WF_ENGINE.SetItemAttrText(itemtype,
874 itemkey,
875 'APINV_AS',
876 l_vendor_name);
877
878 WF_ENGINE.SetItemAttrText(itemtype,
879 itemkey,
880 'APINV_ASSI',
881 l_vendor_site_code);
882
883 WF_ENGINE.SetItemAttrNumber(itemtype,
884 itemkey,
885 'APINV_AI',
886 l_iteration);
887
888 WF_ENGINE.SetItemAttrNumber(itemtype,
889 itemkey,
890 'APINV_AIA',
891 l_amount);
892
893 WF_ENGINE.SetItemAttrNumber(itemtype,
894 itemkey,
895 'APINV_AOI',
896 l_org_id);
897
898 WF_ENGINE.SetItemAttrNumber(itemtype,
899 itemkey,
900 'APINV_AII',
901 l_invoice_id);
902
903 WF_ENGINE.SetItemAttrText(itemtype,
904 itemkey,
905 'APINV_AIN',
906 l_invoice_num);
907
908 WF_ENGINE.SetItemAttrDate(itemtype,
909 itemkey,
910 'APINV_AID',
911 l_invoice_date);
912
913 --set previous comments
914 l_prev_com := WF_ENGINE.GetItemAttrText(itemtype,
915 itemkey,
916 'APINV_AC');
917
918 WF_ENGINE.SetItemAttrText(itemtype,
919 itemkey,
920 'APINV_APC',
921 l_prev_com);
922
923 WF_ENGINE.SetItemAttrText(itemtype,
924 itemkey,
925 'APINV_AC',
926 '');
927
928 --Bug 2645332 Changed format parameter to 30
929 --set display amount
930 l_dsp_format := fnd_currency.get_format_mask(l_currency,30);
931 l_dsp_amount := to_char(l_amount,l_dsp_format);
932
933 WF_ENGINE.SetItemAttrText(itemtype,
934 itemkey,
935 'APINV_AIAD',
936 l_dsp_amount);
937
938
939 END set_attribute_values;
940
941 PROCEDURE notification_handler(itemtype IN VARCHAR2,
942 itemkey IN VARCHAR2,
943 actid IN NUMBER,
944 funcmode IN VARCHAR2,
945 resultout OUT NOCOPY VARCHAR2 ) IS
946
947
948 BEGIN
949
950 if ( funcmode = 'FORWARD' ) then
951
952
953 resultout := 'COMPLETE';
954
955 return;
956
957 end if;
958
959 if ( funcmode = 'TRANSFER' ) then
960
961
962 resultout := 'ERROR:WFSRV_NO_DELEGATE';
963
964 return;
965
966 end if;
967
968 return;
969 EXCEPTION
970
971 WHEN OTHERS
972 THEN
973 WF_CORE.CONTEXT('AP_WF',itemtype, itemkey, to_char(actid), funcmode);
974 RAISE;
975
976 END;
977
978 PROCEDURE iaw_raise_event(eventname IN VARCHAR2,
979 itemkey IN VARCHAR2,
980 p_org_id IN NUMBER ) IS
981
982 l_parameter_list wf_parameter_list_t;
983 l_debug varchar2(200);
984 l_invoice_id NUMBER;
985 l_iteration NUMBER;
986
987 BEGIN
988
989 l_invoice_id := substr(itemkey, 1, instr(itemkey,'_')-1);
990 l_iteration := substr(itemkey, instr(itemkey,'_')+1, length(itemkey));
991
992 --Bug 2626619 Clear AME for this invoice
993 AME_API.clearAllApprovals(200,
994 l_invoice_id,
995 'APINV');
996
997
998 wf_event.raise(eventname,
999 itemkey);
1000
1001 --Bug 2739340
1002 commit;
1003
1004 EXCEPTION
1005
1006 WHEN OTHERS
1007 THEN
1008 WF_CORE.CONTEXT('AP_WF',eventname, itemkey);
1009 RAISE;
1010
1011 END;
1012
1013 /*get_attribute_value is called by AME when determining the value for more
1014 complicated attributes. It can be called at the header or line level, and
1015 the p_attribute_name is used to determine what the return value should be.
1016 p_context is currently a miscellaneous parameter to be used as necessary in
1017 the future. The goal with this function is to avoid adding a new function
1018 for each new AME attribute.*/
1019
1020 FUNCTION get_attribute_value(p_invoice_id IN NUMBER,
1021 p_dist_id IN NUMBER DEFAULT NULL,
1022 p_attribute_name IN VARCHAR2,
1023 p_context IN VARCHAR2 DEFAULT NULL)
1024 RETURN VARCHAR2 IS
1025
1026 l_debug_info VARCHAR2(2000);
1027 l_return_val VARCHAR2(2000);
1028 l_count_pa_rel NUMBER;
1029
1030 BEGIN
1031
1032 IF p_dist_id is null THEN
1033 /*dealing with a header level attribute*/
1034 IF p_attribute_name =
1035 'SUPPLIER_INVOICE_EXPENDITURE_ORGANIZATION_NAME' THEN
1036
1037 SELECT organization
1038 INTO l_return_val
1039 FROM PA_EXP_ORGS_IT
1040 WHERE organization_id=(SELECT expenditure_organization_id
1041 FROM ap_invoices_all
1042 WHERE invoice_id = p_invoice_id);
1043
1044 ELSIF p_attribute_name = 'SUPPLIER_INVOICE_PROJECT_RELATED' THEN
1045
1046 SELECT count(invoice_distribution_id)
1047 INTO l_count_pa_rel
1048 FROM ap_invoice_distributions_all
1049 WHERE invoice_id = p_invoice_id
1050 AND project_id is not null;
1051
1052 IF l_count_pa_rel >0 THEN
1053 l_return_val := 'Y';
1054 ELSE
1055 l_return_val := 'N';
1056 END IF;
1057
1058 END IF;
1059 ELSE /*p_dist_id is not null*/
1060 IF p_attribute_name =
1061 'SUPPLIER_INVOICE_DISTRIBUTION_PO_BUYER_EMP_NUM' THEN
1062
1063 SELECT employee_number
1064 INTO l_return_val
1065 FROM per_all_people_f pap
1066 WHERE person_id = (SELECT ph.agent_id
1067 FROM ap_invoice_distributions_all aid,
1068 po_distributions_all pd,
1069 po_headers_all ph
1070 WHERE pd.po_distribution_id =
1071 aid.po_distribution_id
1072 AND pd.po_header_id = ph.po_header_id
1073 AND aid.invoice_distribution_id =
1074 p_dist_id
1075 AND pd.creation_date >= pap.effective_start_date
1076 AND pd.creation_date <= nvl(pap.effective_end_date,sysdate));
1077
1078 ELSIF p_attribute_name =
1079 'SUPPLIER_INVOICE_DISTRIBUTION_PO_REQUESTER_EMP_NUM' THEN
1080
1081 SELECT employee_number
1082 INTO l_return_val
1083 FROM per_all_people_f pap
1084 WHERE person_id = (SELECT pd.deliver_to_person_id
1085 FROM ap_invoice_distributions_all aid,
1086 po_distributions_all pd
1087 WHERE pd.po_distribution_id =
1088 aid.po_distribution_id
1089 AND aid.invoice_distribution_id =
1090 p_dist_id
1091 AND pd.creation_date >= pap.effective_start_date
1092 AND pd.creation_date <= nvl(pap.effective_end_date,sysdate));
1093 END IF;
1094 END IF;
1095
1096 return l_return_val;
1097
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 Wf_Core.Context('APINV', 'get_attribute_value',
1101 p_invoice_id , p_dist_id, p_attribute_name, l_debug_info);
1102 raise;
1103
1104 END get_attribute_value;
1105 --Bug 5968183
1106 -- Added procedure to update in
1107 PROCEDURE Update_Invoice_Status(
1108 p_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
1109
1110 PRAGMA autonomous_transaction;
1111 BEGIN
1112 UPDATE ap_inv_aprvl_hist_all
1113 SET RESPONSE ='CANCELLED'
1114 WHERE invoice_id = p_invoice_id
1115 AND response ='PENDING';
1116 commit;
1117 END Update_Invoice_Status;
1118
1119 END AP_WFAPPROVAL_PKG;