[Home] [Help]
PACKAGE BODY: APPS.AR_AME_CMWF_API
Source
1 PACKAGE BODY ar_ame_cmwf_api AS
2 /* $Header: ARAMECMB.pls 120.21.12010000.2 2008/09/01 09:49:30 naneja ship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8
9 -- This procedure is called restore the context. This is necessary
10 -- after notifications because the process gets deferred, and workflow
11 -- application specific context when it resumes.
12 --
13 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
14
15
16 PROCEDURE callback_routine (
17 p_item_type IN VARCHAR2,
18 p_item_key IN VARCHAR2,
19 p_activity_id IN NUMBER,
20 p_command IN VARCHAR2,
21 p_result IN OUT NOCOPY VARCHAR2) IS
22
23 CURSOR org IS
24 SELECT org_id
25 FROM ra_cm_requests_all
26 WHERE request_id = p_item_key;
27
28 l_debug_mesg VARCHAR2(240);
29 l_org_id ra_cm_requests_all.org_id%TYPE;
30
31 BEGIN
32
33 OPEN org;
34 FETCH org INTO l_org_id;
35 CLOSE org;
36
37 wf_engine.setitemattrnumber(
38 p_item_type,
39 p_item_key,
40 'ORG_ID',
41 l_org_id);
42
43 l_debug_mesg := 'Org ID: ' || l_org_id;
44
45 IF ( p_command = 'RUN' ) THEN
46
47 -- executable statements for RUN mode
48 -- resultout := 'CMREQ_APPROVAL';
49 RETURN;
50 END IF;
51
52 IF ( p_command = 'SET_CTX' ) THEN
53
54 -- executable statements for establishing context information
55 mo_global.set_policy_context(
56 p_access_mode => 'S',
57 p_org_id => l_org_id);
58
59
60 END IF;
61
62 IF ( p_command = 'TEST_CTX' ) THEN
63
64 -- your executable statements for testing the validity of the current
65 -- context information
66 IF (NVL(mo_global.get_access_mode, '-9999') <> 'S') OR
67 (NVL(mo_global.get_current_org_id, -9999) <> l_org_id) THEN
68 p_result := 'FALSE';
69 ELSE
70 p_result := 'TRUE';
71 END IF;
72 RETURN;
73
74 END IF;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78
79 wf_core.context(
80 pkg_name => 'ARP_CMREQ_WF',
81 proc_name => 'CALLBACK_ROUTINE',
82 arg1 => p_item_type,
83 arg2 => p_item_key,
84 arg3 => to_char(p_activity_id),
85 arg4 => p_command,
86 arg5 => l_debug_mesg);
87
88 RAISE;
89
90 END callback_routine;
91
92
93 PROCEDURE restore_context (p_item_key IN VARCHAR2) IS
94
95 l_org_id wf_item_attribute_values.number_value%TYPE;
96
97 BEGIN
98
99 ----------------------------------------------------------
100 g_debug_mesg := 'Entered RESTORE_CONTEXT';
101 IF PG_DEBUG in ('Y', 'C') THEN
102 arp_standard.debug('restore_context: ' || g_debug_mesg);
103 END IF;
104 ----------------------------------------------------------
105
106 l_org_id := wf_engine.GetItemAttrNumber(
107 itemtype => c_item_type,
108 itemkey => p_item_key,
109 aname => 'ORG_ID');
110
111 fnd_client_info.set_org_context (
112 context => l_org_id);
113
114 arp_global.init_global;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118
119 wf_core.context(
120 pkg_name => 'AR_AME_CMWF_API',
121 proc_name => 'RESTORE_CONTEXT',
122 arg1 => c_item_type,
123 arg2 => NULL,
124 arg3 => NULL,
125 arg4 => NULL,
126 arg5 => g_debug_mesg);
127
128 RAISE;
129
130 END restore_context;
131
132
133 -- This procedure is called save the context. This is necessary
134 -- after notifications because the process gets deferred, and workflow
135 -- application specific context when it resumes.
136 --
137 PROCEDURE save_context (p_item_key IN VARCHAR2) IS
138
139 l_org_id wf_item_attribute_values.number_value%TYPE;
140
141 CURSOR c IS
142 SELECT org_id
143 FROM ra_cm_requests_all
144 WHERE request_id = p_item_key;
145
146 BEGIN
147
148 ----------------------------------------------------------
149 g_debug_mesg := 'Entered SAVE_CONTEXT';
150 IF PG_DEBUG in ('Y', 'C') THEN
151 arp_standard.debug('save_context: ' || g_debug_mesg);
152 END IF;
153 ----------------------------------------------------------
154
155 OPEN c;
156 FETCH c INTO l_org_id;
157 CLOSE c;
158
159 wf_engine.SetItemAttrNumber(
160 itemtype => c_item_type,
161 itemkey => p_item_key,
162 aname => 'ORG_ID',
163 avalue => l_org_id);
164
165 EXCEPTION
166 WHEN OTHERS THEN
167
168 wf_core.context(
169 pkg_name => 'AR_AME_CMWF_API',
170 proc_name => 'SAVE_CONTEXT',
171 arg1 => c_item_type,
172 arg2 => NULL,
173 arg3 => NULL,
174 arg4 => NULL,
175 arg5 => g_debug_mesg);
176
177 RAISE;
178
179 END save_context;
180 /*4139346: Added employee_valid */
181
182 FUNCTION employee_valid (p_employee_id IN NUMBER)
183 RETURN BOOLEAN IS
184
185 CURSOR c IS
186 SELECT 1
187 FROM wf_roles
188 WHERE orig_system = 'PER'
189 AND orig_system_id = p_employee_id
190 AND status = 'ACTIVE'
191 AND (expiration_date IS NULL OR
192 sysdate < expiration_date)
193 AND rownum < 2;
194
195 l_dummy NUMBER;
196 l_result BOOLEAN;
197
198 BEGIN
199
200 OPEN c;
201 FETCH c INTO l_dummy;
202 l_result := c%FOUND;
203 CLOSE c;
204
205 RETURN l_result;
206
207 END employee_valid;
208
209
210 PROCEDURE validate_first_approver (p_item_key IN NUMBER) IS
211
212 l_employee_id NUMBER;
213 l_manager_employee_id NUMBER;
214
215 CURSOR mgr IS
216 SELECT supervisor_id
217 FROM per_all_assignments_f
218 WHERE person_id = l_employee_id
219 AND per_all_assignments_f.primary_flag = 'Y'
220 AND per_all_assignments_f.assignment_type in ('E','C')
221 AND per_all_assignments_f.assignment_status_type_id
222 NOT IN
223 (SELECT assignment_status_type_id
224 FROM per_assignment_status_types
225 WHERE per_system_status = 'TERM_ASSIGN');
226
227 BEGIN
228
229 ----------------------------------------------------------
230 g_debug_mesg := 'Entered VALIDATE_FIRST_APPROVER';
231 IF PG_DEBUG in ('Y', 'C') THEN
232 arp_standard.debug('VALIDATE_FIRST_APPROVER: ' || g_debug_mesg);
233 END IF;
234 ----------------------------------------------------------
235
236 l_employee_id := wf_engine.getitemattrnumber(
237 itemtype => c_item_type,
238 itemkey => p_item_key,
239 aname => 'NON_DEFAULT_START_PERSON_ID');
240
241 -- Now that we allow users to setup HR Hierarchy and Non HR Hierarchy
242 -- based approvers, we must look into this variable and determine
243 -- if HR Hierarchy is being used only then do this validation and
244 -- correction.
245
246 IF l_employee_id IS NULL THEN
247 RETURN;
248 END IF;
249
250 -- if the employee is valid then proceed with the current first approver.
251 IF employee_valid(l_employee_id) THEN
252 RETURN;
253 END IF;
254
255 -- employee is not valid anymore, so go ahead and lookup the manager
256 OPEN mgr;
257 FETCH mgr INTO l_manager_employee_id;
258 CLOSE mgr;
259
260 -- now set the manager as the first approver.
261 wf_engine.setitemattrnumber(
262 itemtype => c_item_type,
263 itemkey => p_item_key,
264 aname => 'NON_DEFAULT_START_PERSON_ID',
265 avalue => l_manager_employee_id);
266
267 EXCEPTION
268 WHEN OTHERS THEN
269
270 wf_core.context(
271 pkg_name => 'AR_AME_CMWF_API',
272 proc_name => 'VALIDATE_FIRST_APPROVER',
273 arg1 => c_item_type,
274 arg2 => NULL,
275 arg3 => NULL,
276 arg4 => NULL,
277 arg5 => g_debug_mesg);
278
279 RAISE;
280
281 END validate_first_approver;
282
283
284
285 -- This procedure is simply a central place to get an employee id
286 -- given a user id.
287 --
288 FUNCTION get_employee_id (p_user_id NUMBER) RETURN NUMBER IS
289
290 CURSOR c IS
291 SELECT employee_id
292 FROM fnd_user
293 WHERE user_id = p_user_id;
294
295 l_employee_id NUMBER;
296
297 BEGIN
298
299 ----------------------------------------------------------
300 g_debug_mesg := 'Entered GET_EMPLOYEE_ID';
301 IF PG_DEBUG in ('Y', 'C') THEN
302 arp_standard.debug('get_employee_id: ' || g_debug_mesg);
303 END IF;
304 ----------------------------------------------------------
305
306 OPEN c;
307 FETCH c INTO l_employee_id;
308 CLOSE c;
309
310 RETURN l_employee_id;
311
312
313 EXCEPTION
314 WHEN OTHERS THEN
315
316 wf_core.context(
317 pkg_name => 'AR_AME_CMWF_API',
318 proc_name => 'GET_EMPLOYEE_ID',
319 arg1 => c_item_type,
320 arg2 => NULL,
321 arg3 => NULL,
322 arg4 => NULL,
323 arg5 => g_debug_mesg);
324
325 RAISE;
326
327 END get_employee_id;
328
329
330 -- This procedure is simply a central place to get a user id
331 -- given an employee id.
332 --
333 FUNCTION get_user_id (p_employee_id IN NUMBER) RETURN NUMBER IS
334
335 CURSOR c IS
336 SELECT user_id
337 FROM fnd_user
338 WHERE employee_id = p_employee_id
339 AND NVL(end_date,sysdate) >= sysdate ;
340
341 l_user_id NUMBER;
342
343 BEGIN
344
345 OPEN c;
346 FETCH c INTO l_user_id;
347 CLOSE c;
348
349 RETURN l_user_id;
350
351 EXCEPTION
352 WHEN OTHERS THEN
353
354 wf_core.context(
355 pkg_name => 'AR_AME_CMWF_API',
356 proc_name => 'GET_USER_ID',
357 arg1 => c_item_type,
358 arg2 => NULL,
359 arg3 => NULL,
360 arg4 => NULL,
361 arg5 => g_debug_mesg);
362
363 RAISE;
364
365 END get_user_id;
366
367
368 PROCEDURE GetUserInfoFromTable(
369 p_user_id IN NUMBER,
370 p_user_name OUT NOCOPY VARCHAR2,
371 p_display_name OUT NOCOPY VARCHAR2) IS
372
373 l_employee_id NUMBER;
374
375 BEGIN
376
377 ----------------------------------------------------------
378 g_debug_mesg := 'Entered GETUSERINFOFROMTABLE';
379 IF PG_DEBUG in ('Y', 'C') THEN
380 arp_standard.debug('GetUserInfoFromTable: ' || g_debug_mesg);
381 END IF;
382 ----------------------------------------------------------
383
384 -- Modifed for Bug # 3778203. We need to eliminate the restriction
385 -- that the approver must be an employee. As a result, now we can
386 -- simply use the api provided by the workflow team to get the user
387 -- name and the display name.
388 --
389 -- ORASHID 04-NOV-2004
390
391 wf_directory.getusername (
392 p_orig_system => 'FND_USR',
393 p_orig_system_id => p_user_id,
394 p_name => p_user_name, -- out variable
395 p_display_name => p_display_name -- out variable
396 );
397
398 -- it is possible the user is question was originally created
399 -- as person, so look for it as PER.
400
401 IF (p_user_name IS NULL) THEN
402
403 l_employee_id := get_employee_id(p_user_id);
404
405 wf_directory.getusername (
406 p_orig_system => 'PER',
407 p_orig_system_id => l_employee_id,
408 p_name => p_user_name, -- out variable
409 p_display_name => p_display_name -- out variable
410 );
411
412 END IF;
413
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 p_user_name := NULL ;
417 p_display_name := NULL ;
418
419 wf_core.context(
420 pkg_name => 'AR_AME_CMWF_API',
421 proc_name => 'GETUSERINFOFROMTABLE',
422 arg1 => c_item_type,
423 arg2 => p_user_id,
424 arg3 => l_employee_id,
425 arg4 => NULL,
426 arg5 => g_debug_mesg);
427
428 RAISE;
429
430 WHEN OTHERS THEN
431
432 wf_core.context(
433 pkg_name => 'AR_AME_CMWF_API',
434 proc_name => 'GETUSERINFOFROMTABLE',
435 arg1 => c_item_type,
436 arg2 => p_user_id,
437 arg3 => NULL,
438 arg4 => NULL,
439 arg5 => g_debug_mesg);
440
441 RAISE;
442
443 END GetUserInfoFromTable;
444
445
446 PROCEDURE GetEmployeeInfo(
447 p_user_id IN NUMBER,
448 p_item_type IN VARCHAR2,
449 p_item_key IN VARCHAR2) IS
450
451 l_approver_user_name wf_users.name%TYPE;
452 l_approver_display_name wf_users.display_name%TYPE;
453 l_manager_name VARCHAR2(100);
454 l_manager_display_name wf_users.display_name%TYPE;
455
456 BEGIN
457
458
459 ----------------------------------------------------------
460 g_debug_mesg := 'Entered GETEMPLOYEEINFO';
461 IF PG_DEBUG in ('Y', 'C') THEN
462 arp_standard.debug('GetEmployeeInfo: ' || g_debug_mesg);
463 END IF;
464 ----------------------------------------------------------
465
466 -- set username and display name for a primary approver
467
468 GetUserInfoFromTable(
469 p_user_id,
470 l_approver_user_name,
471 l_approver_display_name);
472
473 IF l_approver_user_name IS NOT NULL THEN
474
475 wf_engine.SetItemAttrNumber(p_item_type,
476 p_item_key,
477 'APPROVER_ID',
478 p_user_id);
479
480 wf_engine.SetItemAttrText(p_item_type,
481 p_item_key,
482 'APPROVER_USER_NAME',
483 l_approver_user_name);
484
485 wf_engine.SetItemAttrText(p_item_type,
486 p_item_key,
487 'APPROVER_DISPLAY_NAME',
488 l_approver_display_name);
489
490 ELSE
491 g_debug_mesg := 'USER NAME not found, ' ||
492 'user may not be setup as an employee!';
493 RAISE no_data_found;
494 END IF;
495
496 EXCEPTION
497 WHEN OTHERS THEN
498
499 wf_core.context(
500 pkg_name => 'AR_AME_CMWF_API',
501 proc_name => 'GETEMPLOYEEINFO',
502 arg1 => p_item_type,
503 arg2 => p_item_key,
504 arg3 => p_user_id,
505 arg4 => NULL,
506 arg5 => g_debug_mesg);
507
508 RAISE;
509
510 END GetEmployeeInfo;
511
512 /*4139346: Added handle_ntf_forward */
513
514 PROCEDURE handle_ntf_forward (
515 p_item_type IN VARCHAR2,
516 p_item_key IN VARCHAR2,
517 p_actid IN NUMBER,
518 p_funcmode IN VARCHAR2,
519 p_result OUT NOCOPY VARCHAR2) IS
520
521 CURSOR c (p_user_name VARCHAR2) IS
522 SELECT user_id
523 FROM fnd_user
524 WHERE user_name = p_user_name;
525
526 l_document_id NUMBER;
527 l_customer_trx_id NUMBER;
528 l_note_id NUMBER;
529 l_note_text ar_notes.text%type;
530 l_responder_user_id wf_users.orig_system_id%TYPE;
531 l_responder_user_name wf_users.name%TYPE;
532 l_responder_display_name wf_users.display_name%TYPE;
533
534 BEGIN
535
536 l_responder_user_name := wf_engine.context_text;
537
538 OPEN c(l_responder_user_name);
539 FETCH c INTO l_responder_user_id;
540 CLOSE c;
541
542 GetUserInfoFromTable(
543 p_user_id => l_responder_user_id,
544 p_user_name => l_responder_user_name,
545 p_display_name => l_responder_display_name);
546
547 IF p_funcmode = 'TRANSFER' OR p_funcmode = 'FORWARD' THEN
548
549 -- insert the reassignment note here
550
551 l_document_id := wf_engine.GetItemAttrNumber(
552 itemtype => p_item_type,
553 itemkey => p_item_key,
554 aname => 'WORKFLOW_DOCUMENT_ID');
555
556 l_customer_trx_id := wf_engine.GetItemAttrNumber(
557 itemtype => p_item_type,
558 itemkey => p_item_key,
559 aname => 'CUSTOMER_TRX_ID');
560
561 fnd_message.set_name('AR', 'AR_WF_APPROVAL_REASSIGNED');
562 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
563 fnd_message.set_token('APPROVER', l_responder_display_name);
564
565 l_note_text := fnd_message.get;
566
567 InsertTrxNotes (
568 x_customer_call_id => NULL,
569 x_customer_call_topic_id => NULL,
570 x_action_id => NULL,
571 x_customer_trx_id => l_customer_trx_id,
572 x_note_type => 'MAINTAIN',
573 x_text => l_note_text,
574 x_note_id => l_note_id);
575
576 ELSIF (p_funcmode = 'RESPOND') THEN
577
578 wf_engine.setitemattrtext(
579 itemtype => p_item_type,
580 itemkey => p_item_key,
581 aname => 'DEBUG',
582 avalue => '2: ' || l_responder_user_name);
583
584 -- insert the reassignment note here
585
586 wf_engine.SetItemAttrNumber(
587 itemtype => p_item_type,
588 itemkey => p_item_key,
589 aname => 'APPROVER_ID',
590 avalue => l_responder_user_id);
591
592 wf_engine.SetItemAttrText(
593 itemtype => p_item_type,
594 itemkey => p_item_key,
595 aname => 'APPROVER_USER_NAME',
596 avalue => l_responder_user_name);
597
598 wf_engine.SetItemAttrText(
599 itemtype => p_item_type,
600 itemkey => p_item_key,
601 aname => 'APPROVER_DISPLAY_NAME',
602 avalue => l_responder_display_name);
603
604 END IF;
605
606 END handle_ntf_forward;
607
608
609 PROCEDURE CheckUserInTable(p_item_type IN VARCHAR2,
610 p_item_key IN VARCHAR2,
611 p_employee_id IN NUMBER,
612 p_primary_flag IN VARCHAR2,
613 p_count OUT NOCOPY NUMBER) IS
614
615 l_reason_code VARCHAR2(45);
616 l_currency_code VARCHAR2(30);
617
618 CURSOR c1 IS
619 SELECT count(*)
620 FROM ar_approval_user_limits aul
621 WHERE aul.reason_code = l_reason_code
622 AND aul.currency_code = l_currency_code
623 AND aul.primary_flag = p_primary_flag
624 AND user_id = p_employee_id
625 ORDER BY - aul.amount_from;
626
627 BEGIN
628
629 ----------------------------------------------------------
630 g_debug_mesg := 'Entered CHECKUSERINTABLE';
631 IF PG_DEBUG in ('Y', 'C') THEN
632 arp_standard.debug('CheckUserInTable: ' || g_debug_mesg);
633 END IF;
634 ----------------------------------------------------------
635
636 l_reason_code := wf_engine.GetItemAttrText(
637 p_item_type,
638 p_item_key,
639 'REASON');
640
641 l_currency_code := wf_engine.GetItemAttrText(
642 p_item_type,
643 p_item_key,
644 'CURRENCY_CODE');
645
646 OPEN c1;
647 FETCH c1 into p_count;
648 CLOSE c1;
649
650 RETURN;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654
655 wf_core.context(
656 pkg_name => 'AR_AME_CMWF_API',
657 proc_name => 'CHECKUSERINTABLE',
658 arg1 => c_item_type,
659 arg2 => NULL,
660 arg3 => NULL,
661 arg4 => NULL,
662 arg5 => g_debug_mesg);
663
664 RAISE;
665
666 END CheckUserInTable;
667
668
669 /*************************************************************************/
670 -- Written For AME Integration
671 --
672 -- This procedure is called to find the next approver for the transaction
673 -- type. It also stores the person retrieved in a workflow attribute
674 -- appropriately named PERSON_ID. It also cals getemployeeinfo to set some
675 -- attributes to make sure notifications are sent smoothly to this approver.
676 --
677 PROCEDURE FindNextApprover (
678 p_item_type IN VARCHAR2,
679 p_item_key IN VARCHAR2,
680 p_ame_trx_type IN VARCHAR2,
681 x_approver_user_id OUT NOCOPY NUMBER,
682 x_approver_employee_id OUT NOCOPY NUMBER) IS
683
684 l_next_approver ame_util.approverrecord;
685 l_admin_approver ame_util.approverRecord;
686 l_approver_user_id NUMBER DEFAULT NULL;
687 l_approver_employee_id NUMBER;
688 l_error_message fnd_new_messages.message_text%TYPE;
689
690 BEGIN
691
692 ----------------------------------------------------------
693 g_debug_mesg := 'Entered FINDNEXTAPPROVER';
694 IF PG_DEBUG in ('Y', 'C') THEN
695 arp_standard.debug('FindNextApprover: ' || g_debug_mesg);
696 END IF;
697 ----------------------------------------------------------
698
699 ame_api.getnextapprover(
700 applicationidin => c_application_id,
701 transactionidin => p_item_key,
702 transactiontypein => p_ame_trx_type,
703 nextapproverout => l_next_approver);
704
705 ------------------------------------------------------------------------
706 g_debug_mesg := 'AME call to getNextApprover returned: ';
707 IF PG_DEBUG in ('Y', 'C') THEN
708 arp_standard.debug('FindNextAprrover: ' || g_debug_mesg);
709 END IF;
710 ------------------------------------------------------------------------
711
712 IF (l_next_approver.person_id IS NULL) THEN
713
714 IF (l_next_approver.user_id IS NULL) THEN
715 -- no more approvers left
716 RETURN;
717
718 ELSE
719 l_approver_user_id := l_next_approver.user_id;
720 l_approver_employee_id := get_employee_id(l_approver_user_id);
721
722 END IF;
723
724 ELSE
725
726 -- check if the person id matches admin person id
727 -- which means there was an error retrieving
728 -- the approver and this should be reported.
729
730 ame_api.getadminapprover(adminapproverout => l_admin_approver);
731
732 IF l_next_approver.person_id = l_admin_approver.person_id THEN
733 Fnd_message.set_name(
734 application => 'AR',
735 name => 'AR_CMWF_AME_NO_APPROVER_MESG');
736 l_error_message := fnd_message.get;
737 app_exception.raise_exception;
738
739 ELSE
740
741 -- the person id returned is a valid approver.
742
743 l_approver_employee_id := l_next_approver.person_id;
744 l_approver_user_id := get_user_id(
745 p_employee_id => l_approver_employee_id);
746
747 END IF;
748
749 END IF;
750
751 wf_engine.SetItemAttrNumber(
752 itemtype => p_item_type,
753 itemkey => p_item_key,
754 aname => 'PERSON_ID',
755 avalue => l_approver_employee_id);
756
757 x_approver_user_id := l_approver_user_id;
758 x_approver_employee_id := l_approver_employee_id;
759
760 EXCEPTION
761 WHEN OTHERS THEN
762
763 wf_core.context(
764 pkg_name => 'AR_AME_CMWF_API',
765 proc_name => 'FINDNEXTAPPROVER',
766 arg1 => p_item_type,
767 arg2 => p_item_key,
768 arg3 => NULL,
769 arg4 => NULL,
770 arg5 => g_debug_mesg);
771
772 RAISE;
773
774 END FindNextApprover;
775
776
777 /***********************************************************************/
778 -- Written For AME Integration
779 --
780 -- We reach this function because the approver has approved or rejected
781 -- or not responded to the the request and therefore we must communicate
782 -- that to AME. Only complicaton we have here is that this same function is
783 -- called for collector as well as for the subsequent approvers. We must
784 -- know where we are in the process for updateApprovalStatus2 to work
785 -- correctly. That is why we look at the CURRENT_HUB attribute to find
786 -- what value of transaction type to pass to AME.
787
788 PROCEDURE RecordResponseWithAME (
789 p_item_type IN VARCHAR2,
790 p_item_key IN VARCHAR2,
791 p_response IN VARCHAR2) IS
792
793 l_transaction_type VARCHAR2(30);
794 --l_approver_id NUMBER;
795 l_approver_user_id NUMBER;
796 l_approver_employee_id NUMBER;
797 l_next_approver ame_util.approverrecord;
798
799 BEGIN
800
801 l_transaction_type := wf_engine.GetItemAttrText(
802 itemtype => p_item_type,
803 itemkey => p_item_key,
804 aname => 'CURRENT_HUB');
805
806 g_debug_mesg := 'Before call to getNextApprover';
807
808
809 ame_api.getnextapprover(
810 applicationidin => c_application_id,
811 transactionidin => p_item_key,
812 transactiontypein => l_transaction_type,
813 nextapproverout => l_next_approver);
814
815 ------------------------------------------------------------------------
816 g_debug_mesg := 'AME call to getNextApprover returned: ';
817 IF pg_debug IN ('Y', 'C') THEN
818 arp_standard.debug('FindNextAprrover: ' || g_debug_mesg);
819 END IF;
820 ------------------------------------------------------------------------
821
822 IF (l_next_approver.person_id IS NULL) THEN
823 l_approver_user_id := l_next_approver.user_id;
824 l_approver_employee_id := NULL;
825 ELSE
826 l_approver_user_id := NULL;
827 l_approver_employee_id := l_next_approver.person_id;
828 END IF;
829
830 g_debug_mesg := 'call AME updateApprovalStatus - ' ||
831 'l_approver_user_id: ' || l_approver_user_id ||
832 ' l_approver_employee_id: ' || l_approver_employee_id;
833
834
835 ame_api.updateApprovalStatus2(
836 applicationIdIn => c_application_id,
837 transactionIdIn => p_item_key,
838 approvalStatusIn => p_response,
839 approverPersonIdIn => l_approver_employee_id,
840 approverUserIdIn => l_approver_user_id,
841 transactionTypeIn => l_transaction_type);
842
843 g_debug_mesg := 'Returned successfully from updateApprovalStatus!';
844
845 EXCEPTION
846 WHEN OTHERS THEN
847
848 wf_core.context(
849 pkg_name => 'AR_AME_CMWF_API',
850 proc_name => 'RECORDRESPONSEWITHAME',
851 arg1 => p_item_type,
852 arg2 => p_item_key,
853 arg3 => null,
854 arg4 => null,
855 arg5 => g_debug_mesg);
856
857 RAISE;
858
859 END RecordResponseWithAME;
860
861
862 /*************************************************************************/
863 -- Written For AME Integration
864 -- This is a subroutine to sync timeout scenarios with AME. Currently,
865 -- due to AME limitations, we ourselves are handling the logic for timeouts.
866 -- As a result, there is a need to sync up with AME when the manager responds
867 -- to the escalation notice. In case the manager is one of the approvers then
868 -- we must skip him so that he does not have to approve the same transaction
869 -- twice.
870
871 PROCEDURE SkipIfDuplicate (
872 p_item_type IN VARCHAR2,
873 p_item_key IN VARCHAR2,
874 p_ame_trx_type IN VARCHAR2,
875 p_approver_user_id IN VARCHAR2,
876 p_approver_employee_id IN VARCHAR2,
877 x_approver_user_id OUT NOCOPY NUMBER,
878 x_approver_employee_id OUT NOCOPY NUMBER) IS
879
880 l_manager_employee_id fnd_user.employee_id%TYPE;
881
882 BEGIN
883
884 l_manager_employee_id := wf_engine.getitemattrnumber(
885 itemtype => p_item_type,
886 itemkey => p_item_key,
887 aname => 'MANAGER_ID');
888
889 g_debug_mesg := 'Manager ID: ' || l_manager_employee_id;
890
891 IF (p_approver_employee_id = l_manager_employee_id) THEN
892
893 -- The next approver in the approval chain is the manager
894 -- who has approved this transaction once before.
895
896 g_debug_mesg := 'Skipping approver';
897
898 -- inform AME that the approver has approved this.
899 RecordResponseWithAME (
900 p_item_type => p_item_type,
901 p_item_key => p_item_key,
902 p_response => ame_util.approvedStatus);
903
904 -- now get the next approver in the list.
905 FindNextApprover(
906 p_item_type => p_item_type,
907 p_item_key => p_item_key,
908 p_ame_trx_type => p_ame_trx_type,
909 x_approver_user_id => x_approver_user_id,
910 x_approver_employee_id => x_approver_employee_id);
911
912 g_debug_mesg := 'Retrieved the next approver';
913
914 -- reset the scalation related attributes.
915 wf_engine.setitemattrnumber(
916 itemtype => p_item_type,
917 itemkey => p_item_key,
918 aname => 'MANAGER_ID',
919 avalue => -9999);
920
921 wf_engine.setitemattrnumber(
922 itemtype => p_item_type,
923 itemkey => p_item_key,
924 aname => 'ESCALATION_COUNT',
925 avalue => 0);
926
927 ELSE
928 g_debug_mesg := 'Not skipping the approver';
929 x_approver_user_id := p_approver_user_id;
930 x_approver_employee_id := p_approver_employee_id;
931 END IF;
932
933 EXCEPTION
934 WHEN OTHERS THEN
935
936 wf_core.context(
937 pkg_name => 'AR_AME_CMWF_API',
938 proc_name => 'SKIPIFDUPLICATE',
939 arg1 => p_item_type,
940 arg2 => p_item_key,
941 arg3 => NULL,
942 arg4 => NULL,
943 arg5 => g_debug_mesg);
944
945 RAISE;
946
947 END SkipIfDuplicate;
948
949
950 PROCEDURE FindTrx(p_item_type IN VARCHAR2,
951 p_item_key IN VARCHAR2,
952 p_actid IN NUMBER,
953 p_funcmode IN VARCHAR2,
954 p_result OUT NOCOPY VARCHAR2) IS
955
956 l_workflow_document_id NUMBER;
957 l_customer_trx_id NUMBER;
958 l_amount NUMBER;
959 l_tax_amount NUMBER;
960 l_line_amount NUMBER;
961 l_freight_amount NUMBER;
962 l_original_line_amount NUMBER;
963 l_original_tax_amount NUMBER;
964 l_original_freight_amount NUMBER;
965 l_original_total NUMBER;
966 l_reason_code VARCHAR2(45);
967 l_reason_meaning VARCHAR2(80);
968 l_currency_code VARCHAR2(15);
969 l_requestor_id NUMBER;
970 l_requestor_user_name wf_users.name%TYPE;
971 l_requestor_display_name wf_users.display_name%TYPE;
972 /*Bug3206020 Changed comments size from 240 to 1760. */
973 l_comments VARCHAR2(1760);
974 l_orig_trx_number ra_cm_requests_all.orig_trx_number%TYPE;
975 l_tax_ex_cert_num ra_cm_requests_all.tax_ex_cert_num%TYPE;
976 l_internal_comment VARCHAR2(1760) DEFAULT NULL; /*7367350*/
977
978
979 CURSOR c1 is
980 SELECT name, display_name
981 FROM wf_users
982 WHERE orig_system = 'PER'
983 AND orig_system_id = l_requestor_id;
984
985 CURSOR c2 is
986 SELECT name, display_name
987 FROM wf_users
988 WHERE orig_system = 'FND_USR'
989 AND orig_system_id = l_requestor_id;
990
991
992 BEGIN
993
994 ----------------------------------------------------------
995 g_debug_mesg := 'Entered FINDTRX';
996 IF PG_DEBUG in ('Y', 'C') THEN
997 arp_standard.debug('FindTrx: ' || g_debug_mesg);
998 END IF;
999 ----------------------------------------------------------
1000
1001 -- save_context(p_item_key);
1002
1003 --
1004 -- RUN mode - normal process execution
1005 --
1006 IF (p_funcmode = 'RUN') then
1007
1008 ------------------------------------------------------------
1009 g_debug_mesg := 'Get the requested trx and request_id';
1010 ------------------------------------------------------------
1011 /*7367350 As per enhacement passed parmeter to get vlaue and set
1012 in newly added attribute for the workflow. */
1013 GetCustomerTrxInfo(
1014 p_item_type,
1015 p_item_key,
1016 l_workflow_document_id,
1017 l_customer_trx_id,
1018 l_amount,
1019 l_line_amount,
1020 l_tax_amount,
1021 l_freight_amount,
1022 l_reason_code,
1023 l_reason_meaning,
1024 l_requestor_id,
1025 l_comments,
1026 l_orig_trx_number,
1027 l_tax_ex_cert_num,
1028 l_internal_comment
1029 );
1030
1031 IF l_customer_trx_id <> -1 then
1032
1033 wf_engine.SetItemAttrNumber(
1034 p_item_type,
1035 p_item_key,
1036 'WORKFLOW_DOCUMENT_ID',
1037 l_workflow_document_id);
1038
1039 wf_engine.SetItemAttrNumber(
1040 p_item_type,
1041 p_item_key,
1042 'CUSTOMER_TRX_ID',
1043 l_customer_trx_id);
1044
1045 wf_engine.SetItemAttrNumber(
1046 p_item_type,
1047 p_item_key,
1048 'TOTAL_CREDIT_TO_INVOICE',
1049 l_amount);
1050
1051 wf_engine.SetItemAttrNumber(
1052 p_item_type,
1053 p_item_key,
1054 'TOTAL_CREDIT_TO_LINES',
1055 l_line_amount);
1056
1057 wf_engine.SetItemAttrNumber(
1058 p_item_type,
1059 p_item_key,
1060 'TOTAL_CREDIT_TO_TAX',
1061 l_tax_amount);
1062
1063 wf_engine.SetItemAttrNumber(
1064 p_item_type,
1065 p_item_key,
1066 'TOTAL_CREDIT_TO_FREIGHT',
1067 l_freight_amount);
1068
1069 wf_engine.SetItemAttrText(
1070 p_item_type,
1071 p_item_key,
1072 'REASON',
1073 l_reason_code);
1074
1075 wf_engine.SetItemAttrText(
1076 p_item_type,
1077 p_item_key,
1078 'REASON_MEANING',
1079 l_reason_meaning);
1080
1081 wf_engine.SetItemAttrText(
1082 p_item_type,
1083 p_item_key,
1084 'COMMENTS',
1085 l_comments);
1086
1087 wf_engine.SetItemAttrNumber(
1088 p_item_type,
1089 p_item_key,
1090 'REQUESTOR_ID',
1091 l_requestor_id);
1092
1093 wf_engine.SetItemAttrText(
1094 p_item_type,
1095 p_item_key,
1096 'ORIG_TRX_NUMBER',
1097 l_orig_trx_number);
1098
1099 wf_engine.SetItemAttrText(
1100 p_item_type,
1101 p_item_key,
1102 'TAX_EX_CERT_NUM',
1103 l_tax_ex_cert_num);
1104
1105 wf_engine.SetItemAttrText(
1106 p_item_type,
1107 p_item_key,
1108 'INTERNAL_COMMENTS',
1109 l_internal_comment);
1110
1111 -- set requestor name and display name
1112 IF ( l_requestor_id <> -1) then
1113 OPEN c1;
1114 FETCH c1 into l_requestor_user_name, l_requestor_display_name;
1115 IF c1%notfound then
1116 l_requestor_user_name := NULL;
1117 l_requestor_display_name := NULL;
1118 OPEN c2;
1119 FETCH c2 into l_requestor_user_name, l_requestor_display_name;
1120 IF c2%notfound then
1121 l_requestor_user_name := NULL;
1122 l_requestor_display_name := NULL;
1123 g_debug_mesg := 'could not find the requestor';
1124 END if;
1125 END if;
1126 END if;
1127
1128 wf_engine.SetItemAttrText(
1129 p_item_type,
1130 p_item_key,
1131 'REQUESTOR_USER_NAME',
1132 l_requestor_user_name);
1133
1134 wf_engine.SetItemAttrText(
1135 p_item_type,
1136 p_item_key,
1137 'REQUESTOR_DISPLAY_NAME',
1138 l_requestor_display_name);
1139
1140 -- set amount for trx.
1141
1142 GetTrxAmount(
1143 p_item_type,
1144 p_item_key,
1145 l_customer_trx_id,
1146 l_original_line_amount,
1147 l_original_tax_amount,
1148 l_original_freight_amount,
1149 l_original_total ,
1150 l_currency_code);
1151
1152 wf_engine.SetItemAttrNumber(
1153 p_item_type,
1154 p_item_key,
1155 'ORIGINAL_LINE_AMOUNT',
1156 l_original_line_amount);
1157
1158 wf_engine.SetItemAttrNumber(
1159 p_item_type,
1160 p_item_key,
1161 'ORIGINAL_TAX_AMOUNT',
1162 l_original_tax_amount);
1163
1164 wf_engine.SetItemAttrNumber(
1165 p_item_type,
1166 p_item_key,
1167 'ORIGINAL_FREIGHT_AMOUNT',
1168 l_original_freight_amount);
1169
1170 wf_engine.SetItemAttrNumber(
1171 p_item_type,
1172 p_item_key,
1173 'ORIGINAL_TOTAL',
1174 l_original_total);
1175
1176 wf_engine.SetItemAttrText(
1177 p_item_type,
1178 p_item_key,
1179 'CURRENCY_CODE',
1180 l_currency_code);
1181
1182 p_result := 'COMPLETE:T';
1183 RETURN;
1184 ELSE
1185 p_result := 'COMPLETE:F';
1186 RETURN;
1187 END if;
1188
1189 END if; -- END of run mode
1190
1191 --
1192 -- CANCEL mode
1193 --
1194 -- This is an event point is called with the effect of the activity must
1195 -- be undone, for example when a process is reset to an earlier point
1196 -- due to a loop back.
1197 --
1198 IF (p_funcmode = 'CANCEL') then
1199
1200 -- no result needed
1201 p_result := 'COMPLETE:';
1202 RETURN;
1203 END if;
1204
1205 --
1206 -- Other execution modes may be created in the future. Your
1207 -- activity will indicate that it does not implement a mode
1208 -- by returning NULL
1209 --
1210 p_result := '';
1211 RETURN;
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215
1216 wf_core.context(
1217 pkg_name => 'AR_AME_CMWF_API',
1218 proc_name => 'FINDTRX',
1219 arg1 => p_item_type,
1220 arg2 => p_item_key,
1221 arg3 => p_funcmode,
1222 arg4 => to_char(p_actid),
1223 arg5 => g_debug_mesg);
1224
1225 END FindTrx;
1226
1227
1228 /*7367350 Changed procedure to retrive value for internal comment as well */
1229 PROCEDURE GetCustomerTrxInfo(p_item_type IN VARCHAR2,
1230 p_item_key IN VARCHAR2,
1231 p_workflow_document_id OUT NOCOPY NUMBER,
1232 p_customer_trx_id OUT NOCOPY NUMBER,
1233 p_amount OUT NOCOPY NUMBER,
1234 p_line_amount OUT NOCOPY NUMBER,
1235 p_tax_amount OUT NOCOPY NUMBER,
1236 p_freight_amount OUT NOCOPY NUMBER,
1237 p_reason OUT NOCOPY VARCHAR2,
1238 p_reason_meaning OUT NOCOPY VARCHAR2,
1239 p_requestor_id OUT NOCOPY NUMBER,
1240 p_comments OUT NOCOPY VARCHAR2,
1241 p_orig_trx_number OUT NOCOPY VARCHAR2,
1242 p_tax_ex_cert_num OUT NOCOPY VARCHAR2,
1243 p_internal_comment OUT NOCOPY VARCHAR2) IS
1244
1245 l_workflow_document_id NUMBER;
1246 l_customer_trx_id NUMBER;
1247 l_amount NUMBER;
1248 l_line_amount NUMBER;
1249 l_tax_amount NUMBER;
1250 l_freight_amount NUMBER;
1251 l_created_by NUMBER;
1252 l_line_credit_flag VARCHAR2(1);
1253 l_tax_disclaimer VARCHAR2(250);
1254 l_orig_trx_number ra_cm_requests_all.orig_trx_number%TYPE;
1255 l_tax_ex_cert_num ra_cm_requests_all.tax_ex_cert_num%TYPE;
1256
1257 CURSOR c IS
1258 SELECT r.request_id,
1259 r.customer_trx_id,
1260 r.total_amount,
1261 r.cm_reason_code,
1262 l.meaning,
1263 r.created_by,
1264 r.comments,
1265 r.line_credits_flag,
1266 r.line_amount,
1267 r.tax_amount,
1268 r.freight_amount,
1269 r.ORIG_TRX_NUMBER,
1270 r.TAX_EX_CERT_NUM,
1271 r.internal_comment
1272 FROM ar_lookups l, ra_cm_requests r
1273 WHERE r.request_id = p_item_key
1274 AND r.cm_reason_code = l.lookup_code
1275 AND l.lookup_type = 'CREDIT_MEMO_REASON';
1276
1277 BEGIN
1278
1279 ----------------------------------------------------------
1280 g_debug_mesg := 'Entered GETCUSTOMERTRXINFO';
1281 IF PG_DEBUG in ('Y', 'C') THEN
1282 arp_standard.debug('GetCustomerTrxInfo: ' || g_debug_mesg);
1283 END IF;
1284 ----------------------------------------------------------
1285
1286 OPEN c;
1287 FETCH c INTO
1288 l_workflow_document_id,
1289 l_customer_trx_id,
1290 l_amount,
1291 p_reason,
1292 p_reason_meaning,
1293 l_created_by,
1294 p_comments,
1295 l_line_credit_flag,
1296 l_line_amount,
1297 l_tax_amount,
1298 l_freight_amount,
1299 l_orig_trx_number,
1300 l_tax_ex_cert_num,
1301 p_internal_comment;
1302 CLOSE c;
1303
1304 p_workflow_document_id := l_workflow_document_id;
1305 p_customer_trx_id := l_customer_trx_id;
1306 p_amount := l_amount;
1307 p_line_amount := l_line_amount;
1308 p_tax_amount := l_tax_amount;
1309 p_freight_amount := l_freight_amount;
1310 p_orig_trx_number := l_orig_trx_number;
1311 p_tax_ex_cert_num := l_tax_ex_cert_num;
1312
1313
1314 IF l_line_credit_flag = 'Y' THEN
1315 p_line_amount := l_amount;
1316 END IF;
1317
1318 p_requestor_id := get_employee_id(l_created_by);
1319
1320 IF (p_requestor_id IS NULL) THEN
1321 p_requestor_id := l_created_by;
1322 END IF;
1323
1324 l_tax_disclaimer := NULL;
1325
1326 IF l_line_credit_flag = 'Y' THEN
1327 fnd_message.set_name('AR', 'ARW_INV_MSG10');
1328 l_tax_disclaimer := fnd_message.get;
1329 END IF;
1330
1331 wf_engine.SetItemAttrText(p_item_type,
1332 p_item_key,
1333 'TAX_DISCLAIMER',
1334 l_tax_disclaimer);
1335
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338
1339 p_workflow_document_id := -1;
1340 p_customer_trx_id := -1;
1341 p_amount := 0;
1342 p_tax_amount := 0;
1343 p_line_amount := 0;
1344 p_freight_amount := 0;
1345 p_reason := NULL;
1346 p_reason_meaning := NULL;
1347 p_comments := NULL;
1348 p_requestor_id := -1;
1349 p_orig_trx_number := NULL;
1350 p_tax_ex_cert_num := NULL;
1351 p_internal_comment := NULL;
1352
1353 wf_core.context(
1354 pkg_name => 'AR_AME_CMWF_API',
1355 proc_name => 'GETCUSTOMERTRXINFO',
1356 arg1 => p_item_type,
1357 arg2 => p_item_key,
1358 arg3 => NULL,
1359 arg4 => NULL,
1360 arg5 => g_debug_mesg);
1361
1362 RAISE;
1363
1364 END GetCustomerTrxInfo;
1365
1366
1367 PROCEDURE GetTrxAmount(p_item_type IN VARCHAR2,
1368 p_item_key IN VARCHAR2,
1369 p_customer_trx_id IN NUMBER,
1370 p_original_line_amount OUT NOCOPY NUMBER,
1371 p_original_tax_amount OUT NOCOPY NUMBER,
1372 p_original_freight_amount OUT NOCOPY NUMBER,
1373 p_original_total OUT NOCOPY NUMBER,
1374 p_currency_code OUT NOCOPY VARCHAR2) IS
1375
1376 CURSOR c IS
1377 SELECT sum(ps.amount_line_items_original),
1378 sum(ps.tax_original),
1379 sum(ps.freight_original),
1380 sum(ps.amount_due_original),
1381 ps.invoice_currency_code
1382 FROM ar_payment_schedules ps
1383 WHERE ps.customer_trx_id = p_customer_trx_id
1384 GROUP BY ps.invoice_currency_code ;
1385
1386 BEGIN
1387
1388 ----------------------------------------------------------
1389 g_debug_mesg := 'Entered GETTRXAMOUNT';
1390 IF PG_DEBUG in ('Y', 'C') THEN
1391 arp_standard.debug('GetTrxAmount: ' || g_debug_mesg);
1392 END IF;
1393 ----------------------------------------------------------
1394
1395 OPEN c;
1396 FETCH c INTO
1397 p_original_line_amount,
1398 p_original_tax_amount,
1399 p_original_freight_amount,
1400 p_original_total,
1401 p_currency_code;
1402 CLOSE c;
1403
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406
1407 p_original_line_amount := NULL;
1408 p_original_tax_amount := NULL;
1409 p_original_freight_amount := NULL;
1410 p_original_total := NULL;
1411 p_currency_code := NULL;
1412
1413 wf_core.context(
1414 pkg_name => 'AR_AME_CMWF_API',
1415 proc_name => 'GETTRXAMOUNT',
1416 arg1 => p_item_type,
1417 arg2 => p_item_key,
1418 arg3 => NULL,
1419 arg4 => NULL,
1420 arg5 => g_debug_mesg);
1421
1422 RAISE;
1423
1424 END GetTrxAmount;
1425
1426
1427 PROCEDURE FindCustomer(p_item_type IN VARCHAR2,
1428 p_item_key IN VARCHAR2,
1429 p_actid IN NUMBER,
1430 p_funcmode IN VARCHAR2,
1431 p_result OUT NOCOPY VARCHAR2) IS
1432
1433 l_customer_trx_id NUMBER;
1434 l_customer_id number(15);
1435 l_bill_to_site_use_id NUMBER;
1436 l_bill_to_customer_name VARCHAR2(50);
1437 l_bill_to_customer_number VARCHAR2(30);
1438 l_ship_to_customer_number VARCHAR2(30);
1439 l_ship_to_customer_name VARCHAR2(50);
1440 l_trx_number VARCHAR2(20);
1441 l_request_url ra_cm_requests.url%TYPE;
1442 l_url ra_cm_requests.url%TYPE;
1443 l_request_id NUMBER;
1444 l_trans_url ra_cm_requests.transaction_url%TYPE;
1445 l_act_url ra_cm_requests.activities_url%TYPE;
1446 wf_flag VARCHAR2(1) := 'Y';
1447
1448 CURSOR c IS
1449 SELECT url, transaction_url, activities_url
1450 FROM ra_cm_requests
1451 WHERE request_id = p_item_key;
1452
1453 BEGIN
1454
1455 ----------------------------------------------------------
1456 g_debug_mesg := 'Entered FINDCUSTOMER';
1457 IF PG_DEBUG in ('Y', 'C') THEN
1458 arp_standard.debug('FindCustomer: ' || g_debug_mesg);
1459 END IF;
1460 ----------------------------------------------------------
1461 --
1462 -- RUN mode - normal process execution
1463 --
1464 IF (p_funcmode = 'RUN') then
1465
1466 ------------------------------------------------------------
1467 g_debug_mesg := 'Get requested trx id ';
1468 ------------------------------------------------------------
1469 l_customer_trx_id := wf_engine.GetItemAttrNumber(
1470 p_item_type,
1471 p_item_key,
1472 'CUSTOMER_TRX_ID');
1473
1474 ------------------------------------------------------------
1475 g_debug_mesg := 'Get Customer info based on requested trx ';
1476 ------------------------------------------------------------
1477
1478 FindCustomerInfo(l_customer_trx_id,
1479 l_bill_to_site_use_id,
1480 l_customer_id,
1481 l_bill_to_customer_name,
1482 l_bill_to_customer_number,
1483 l_ship_to_customer_number,
1484 l_ship_to_customer_name,
1485 l_trx_number );
1486
1487 IF l_bill_to_customer_name is NULL then
1488 -- no customer has been found.
1489 p_result := 'COMPLETE:F';
1490 RETURN;
1491 END if;
1492
1493
1494
1495 ----------------------------------------------------------------------
1496 g_debug_mesg := 'Set value for customer_id(name) in workflow process';
1497 -----------------------------------------------------------------------
1498
1499 wf_engine.SetItemAttrNumber(p_item_type,
1500 p_item_key,
1501 'CUSTOMER_ID',
1502 l_customer_id);
1503
1504
1505 wf_engine.SetItemAttrText(p_item_type,
1506 p_item_key,
1507 'CUSTOMER_NAME',
1508 l_bill_to_customer_name);
1509
1510 -- set the bill to and ship to customer info.
1511
1512 wf_engine.SetItemAttrText(p_item_type,
1513 p_item_key,
1514 'BILL_TO_CUSTOMER_NAME',
1515 l_bill_to_customer_name);
1516
1517 -- Bug Fix 1882580. Since l_bill_to_customer_number is changed
1518 -- from number to VARCHAR2, replaced the function in call to
1519 -- wf_engine fom SetItemAttrNumber to SetItemAttrText
1520
1521 wf_engine.SetItemAttrText(p_item_type,
1522 p_item_key,
1523 'BILL_TO_CUSTOMER_NUMBER',
1524 l_bill_to_customer_number);
1525
1526 wf_engine.SetItemAttrText(p_item_type,
1527 p_item_key,
1528 'SHIP_TO_CUSTOMER_NAME',
1529 l_ship_to_customer_name);
1530
1531 -- Bug Fix 1882580. Since l_bill_to_customer_number is changed
1532 -- from number to VARCHAR2, replaced the function in call to
1533 -- wf_engine fom SetItemAttrNumber to SetItemAttrText
1534
1535 wf_engine.SetItemAttrText(p_item_type,
1536 p_item_key,
1537 'SHIP_TO_CUSTOMER_NUMBER',
1538 l_ship_to_customer_number);
1539
1540 -- set the trx number
1541
1542 wf_engine.SetItemAttrText(p_item_type,
1543 p_item_key,
1544 'TRX_NUMBER',
1545 l_trx_number);
1546
1547
1548
1549 ----------------------------------------------------------------------
1550 g_debug_mesg := 'Set value for bill_to_site_use_id in workflow process';
1551 -----------------------------------------------------------------------
1552 wf_engine.SetItemAttrNumber(p_item_type,
1553 p_item_key,
1554 'BILL_TO_SITE_USE_ID',
1555 l_bill_to_site_use_id);
1556
1557
1558
1559 -- set the URL site
1560
1561 l_request_id := wf_engine.GetItemAttrNumber(
1562 p_item_type,
1563 p_item_key,
1564 'WORKFLOW_DOCUMENT_ID');
1565
1566 OPEN c;
1567 FETCH c INTO l_request_url, l_trans_url, l_act_url;
1568 CLOSE c;
1569
1570 wf_engine.SetItemAttrText(p_item_type,
1571 p_item_key,
1572 'REQUEST_URL',
1573 l_request_url);
1574
1575 wf_engine.SetItemAttrText(p_item_type,
1576 p_item_key,
1577 'TRANSACTION_NUMBER_URL',
1578 l_trans_url);
1579
1580 wf_engine.SetItemAttrText(p_item_type,
1581 p_item_key,
1582 'TRANSACTION_ACTIVITY_URL',
1583 l_act_url);
1584
1585 p_result := 'COMPLETE:T';
1586 RETURN;
1587
1588 END if; -- END of run mode
1589
1590 --
1591 -- CANCEL mode
1592 --
1593 --
1594 IF (p_funcmode = 'CANCEL') then
1595
1596 -- no result needed
1597 p_result := 'COMPLETE:';
1598 RETURN;
1599 END if;
1600
1601
1602 --
1603 -- Other execution modes.
1604 --
1605 p_result := '';
1606 RETURN;
1607
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610
1611 wf_core.context(
1612 pkg_name => 'AR_AME_CMWF_API',
1613 proc_name => 'FINDCUSTOMER',
1614 arg1 => p_item_type,
1615 arg2 => p_item_key,
1616 arg3 => p_funcmode,
1617 arg4 => to_char(p_actid),
1618 arg5 => g_debug_mesg);
1619
1620 RAISE;
1621
1622 END FindCustomer;
1623
1624
1625 PROCEDURE FindCustomerInfo(p_customer_trx_id IN NUMBER,
1626 p_bill_to_site_use_id OUT NOCOPY NUMBER,
1627 p_customer_id OUT NOCOPY NUMBER,
1628 p_bill_to_customer_name OUT NOCOPY VARCHAR2,
1629 p_bill_to_customer_number OUT NOCOPY VARCHAR2,
1630 p_ship_to_customer_number OUT NOCOPY VARCHAR2,
1631 p_ship_to_customer_name OUT NOCOPY VARCHAR2,
1632 p_trx_number OUT NOCOPY VARCHAR2 ) IS
1633
1634
1635 CURSOR c IS
1636 SELECT rct.bill_to_site_use_id,
1637 rct.bill_to_customer_id,
1638 substrb(party.party_name,1,50),
1639 bill_to_cust.account_number,
1640 rct.trx_number
1641 FROM hz_cust_accounts bill_to_cust,
1642 hz_parties party,
1643 ra_customer_trx rct
1644 WHERE rct.customer_trx_id = p_customer_trx_id
1645 AND rct.bill_to_customer_id = bill_to_cust.cust_account_id
1646 AND bill_to_cust.party_id = party.party_id ;
1647
1648 CURSOR c2 IS
1649 SELECT substrb(party.party_name,1,50),
1650 ship_to_cust.account_number
1651 FROM hz_cust_accounts ship_to_cust,
1652 hz_parties party,
1653 ra_customer_trx rct
1654 WHERE rct.customer_trx_id = p_customer_trx_id
1655 AND rct.ship_to_customer_id = ship_to_cust.cust_account_id
1656 AND ship_to_cust.party_id = party.party_id;
1657
1658
1659 BEGIN
1660
1661 ----------------------------------------------------------
1662 g_debug_mesg := 'Entered FINDCUSTOMERINFO';
1663 IF PG_DEBUG in ('Y', 'C') THEN
1664 arp_standard.debug('FindCustomer: ' || g_debug_mesg);
1665 END IF;
1666 ----------------------------------------------------------
1667
1668 OPEN c;
1669 FETCH c INTO
1670 p_bill_to_site_use_id,
1671 p_customer_id,
1672 p_bill_to_customer_name,
1673 p_bill_to_customer_number,
1674 p_trx_number;
1675 CLOSE c;
1676
1677 ----------------------------------------------------------------------------
1678 g_debug_mesg := 'find ship to cust id and name based on requested invoice';
1679 ----------------------------------------------------------------------------
1680
1681 OPEN c2;
1682 FETCH c2 INTO
1683 p_ship_to_customer_name,
1684 p_ship_to_customer_number;
1685 CLOSE c2;
1686
1687 EXCEPTION
1688 WHEN NO_DATA_FOUND THEN
1689
1690 p_customer_id := NULL ;
1691 p_bill_to_customer_name := NULL;
1692 p_bill_to_customer_number := NULL;
1693
1694 WHEN OTHERS THEN
1695
1696 wf_core.context(
1697 pkg_name => 'AR_AME_CMWF_API',
1698 proc_name => 'FINDCUSTOMERINFO',
1699 arg1 => c_item_type,
1700 arg2 => NULL,
1701 arg3 => NULL,
1702 arg4 => NULL,
1703 arg5 => g_debug_mesg);
1704
1705 RAISE;
1706
1707 END FindCustomerInfo;
1708
1709
1710 PROCEDURE FindCollector(p_item_type IN VARCHAR2,
1711 p_item_key IN VARCHAR2,
1712 p_actid IN NUMBER,
1713 p_funcmode IN VARCHAR2,
1714 p_result OUT NOCOPY VARCHAR2) IS
1715
1716 l_customer_trx_id NUMBER(15);
1717 l_customer_id NUMBER;
1718 l_bill_to_site_use_id NUMBER(15);
1719 l_collector_employee_id NUMBER(15);
1720 l_collector_user_id NUMBER(15);
1721 l_collector_id NUMBER(15);
1722 l_collector_name VARCHAR2(30); -- name displayed in collector form.
1723 l_collector_user_name wf_users.name%TYPE;
1724 l_collector_display_name wf_users.display_name%TYPE; -- name for collector as employee
1725
1726 CURSOR c (p_employee_id NUMBER) IS
1727 SELECT collector_id, name
1728 FROM ar_collectors
1729 WHERE employee_id = p_employee_id;
1730
1731 BEGIN
1732
1733 ----------------------------------------------------------
1734 g_debug_mesg := 'Entered FINDCOLLECTOR';
1735
1736
1737 IF PG_DEBUG in ('Y', 'C') THEN
1738 arp_standard.debug('FindCollector: ' || g_debug_mesg);
1739 END IF;
1740 ----------------------------------------------------------
1741 --
1742 -- RUN mode - normal process execution
1743 --
1744 IF (p_funcmode = 'RUN') then
1745
1746 -----------------------------------------------------------------
1747 g_debug_mesg := 'Get the value of customer_trx_id(customer id)';
1748 -----------------------------------------------------------------
1749
1750 l_customer_trx_id := wf_engine.GetItemAttrNumber(
1751 itemtype => p_item_type,
1752 itemkey => p_item_key,
1753 aname => 'CUSTOMER_TRX_ID');
1754
1755 l_customer_id := wf_engine.getitemattrnumber(
1756 itemtype => p_item_type,
1757 itemkey => p_item_key,
1758 aname => 'CUSTOMER_ID');
1759
1760 ----------------------------------------------------------------------
1761 g_debug_mesg := 'get value of bill_to_site_use_id from workflow process';
1762 -----------------------------------------------------------------------
1763
1764 l_bill_to_site_use_id := wf_engine.getitemattrnumber(
1765 itemtype => p_item_type,
1766 itemkey => p_item_key,
1767 aname => 'BILL_TO_SITE_USE_ID');
1768
1769 -----------------------------------------------------------------------
1770 g_debug_mesg := 'Find Collector Info';
1771 -----------------------------------------------------------------------
1772
1773 -- Notice that this calls AME using c_collector_transaction_type.
1774
1775 FindNextApprover(
1776 p_item_type => p_item_type,
1777 p_item_key => p_item_key,
1778 p_ame_trx_type => c_collector_transaction_type,
1779 x_approver_user_id => l_collector_user_id,
1780 x_approver_employee_id => l_collector_employee_id);
1781
1782 IF l_collector_user_id IS NULL THEN
1783 p_result := 'COMPLETE:F';
1784 RETURN;
1785 END IF;
1786
1787 wf_engine.SetItemAttrText(
1788 itemtype => p_item_type,
1789 itemkey => p_item_key,
1790 aname => 'CURRENT_HUB',
1791 avalue => c_collector_transaction_type);
1792
1793 OPEN c (l_collector_employee_id);
1794 FETCH c INTO l_collector_id, l_collector_name;
1795 CLOSE c;
1796
1797 ----------------------------------------------------------------
1798 g_debug_mesg := 'Set value for collector in workflow process';
1799 ----------------------------------------------------------------
1800
1801 wf_engine.SetItemAttrNumber(
1802 itemtype => p_item_type,
1803 itemkey => p_item_key,
1804 aname => 'COLLECTOR_EMPLOYEE_ID',
1805 avalue => l_collector_employee_id);
1806
1807 wf_engine.SetItemAttrNumber(
1808 itemtype => p_item_type,
1809 itemkey => p_item_key,
1810 aname => 'COLLECTOR_ID',
1811 avalue => l_collector_id);
1812
1813 wf_engine.SetItemAttrText(
1814 itemtype => p_item_type,
1815 itemkey => p_item_key,
1816 aname => 'COLLECTOR_NAME',
1817 avalue => l_collector_name);
1818
1819 -------------------------------------------------------------------
1820 g_debug_mesg := 'Set user name for the collector';
1821 ------------------------------------------------------------------
1822
1823 wf_directory.GetUserName(
1824 'PER',
1825 l_collector_employee_id,
1826 l_collector_user_name,
1827 l_collector_display_name);
1828
1829 IF l_collector_user_name is NULL then
1830
1831 ----------------------------------------------------------------
1832 g_debug_mesg := 'The collector has not been defined in directory';
1833 -----------------------------------------------------------------
1834 p_result := 'COMPLETE:F';
1835 RETURN;
1836 ELSE
1837 wf_engine.SetItemAttrText(
1838 itemtype => p_item_type,
1839 itemkey => p_item_key,
1840 aname => 'COLLECTOR_USER_NAME',
1841 avalue => l_collector_user_name);
1842
1843 wf_engine.SetItemAttrText(
1844 itemtype => p_item_type,
1845 itemkey => p_item_key,
1846 aname => 'COLLECTOR_DISPLAY_NAME',
1847 avalue => l_collector_display_name);
1848
1849 wf_engine.SetItemAttrText(
1850 itemtype => p_item_type,
1851 itemkey => p_item_key,
1852 aname => 'APPROVER_ID',
1853 avalue => l_collector_user_id);
1854
1855 wf_engine.SetItemAttrText(
1856 itemtype => p_item_type,
1857 itemkey => p_item_key,
1858 aname => 'APPROVER_USER_NAME',
1859 avalue => l_collector_user_name);
1860
1861 wf_engine.SetItemAttrText(
1862 itemtype => p_item_type,
1863 itemkey => p_item_key,
1864 aname => 'APPROVER_DISPLAY_NAME',
1865 avalue => l_collector_display_name);
1866
1867 END IF;
1868
1869
1870 p_result := 'COMPLETE:T';
1871 RETURN;
1872
1873 END if; -- END of run mode
1874
1875 --
1876 -- CANCEL mode
1877 --
1878 -- This is an event point is called with the effect of the activity must
1879 -- be undone, for example when a process is reset to an earlier point
1880 -- due to a loop back.
1881 --
1882 IF (p_funcmode = 'CANCEL') then
1883
1884 -- no result needed
1885 p_result := 'COMPLETE:';
1886 RETURN;
1887 END if;
1888
1889
1890 --
1891 -- Other execution modes may be created in the future. Your
1892 -- activity will indicate that it does not implement a mode
1893 -- by RETURNing NULL
1894 --
1895 p_result := '';
1896 RETURN;
1897
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900
1901 wf_core.context(
1902 pkg_name => 'AR_AME_CMWF_API',
1903 proc_name => 'FINDCOLLECTOR',
1904 arg1 => p_item_type,
1905 arg2 => p_item_key,
1906 arg3 => p_funcmode,
1907 arg4 => to_char(p_actid),
1908 arg5 => g_debug_mesg);
1909
1910 RAISE;
1911
1912 END FindCollector;
1913
1914
1915 /*************************************************************************/
1916 -- Written For AME Integration
1917 --
1918 -- This procedure is called to check the integrity of the invoicing rule.
1919 -- Previously this was part of default send to subroutine, which is now
1920 -- obsolete.
1921
1922 PROCEDURE AMECheckRule (
1923 p_item_type IN VARCHAR2,
1924 p_item_key IN VARCHAR2,
1925 p_actid IN NUMBER,
1926 p_funcmode IN VARCHAR2,
1927 p_result OUT NOCOPY VARCHAR2) IS
1928
1929 l_customer_trx_id wf_item_attribute_values.number_value%TYPE;
1930 l_invoicing_rule_id wf_item_attribute_values.number_value%TYPE;
1931 l_need_rule_mesg wf_item_attribute_values.text_value%TYPE;
1932 l_credit_accounting_rule wf_item_attribute_values.text_value%TYPE;
1933 l_reason_code wf_item_attribute_values.text_value%TYPE;
1934 l_currency_code wf_item_attribute_values.text_value%TYPE;
1935
1936 CURSOR c (p_cust_trx_id NUMBER) IS
1937 SELECT invoicing_rule_id
1938 FROM ra_customer_trx
1939 WHERE customer_trx_id = p_cust_trx_id;
1940
1941 BEGIN
1942
1943 ----------------------------------------------------------
1944 g_debug_mesg := 'Entered AMECHECKRULE';
1945 IF PG_DEBUG in ('Y', 'C') THEN
1946 arp_standard.debug('AMECheckRule: ' || g_debug_mesg);
1947 END IF;
1948 ----------------------------------------------------------
1949 --
1950 -- RUN mode - normal process execution
1951 --
1952 IF (p_funcmode = 'RUN') THEN
1953
1954 --------------------------------------------------------------------------
1955 g_debug_mesg := 'AME Check Rule';
1956 -------------------------------------------------------------------------
1957
1958 -- Bug 991922 : check if message body needs to say rule is required
1959 -- get additional info to determine if rule is required
1960
1961 l_customer_trx_id := wf_engine.getitemattrnumber(
1962 itemtype => p_item_type,
1963 itemkey => p_item_key,
1964 aname => 'CUSTOMER_TRX_ID');
1965
1966 l_credit_accounting_rule := wf_engine.getitemattrtext(
1967 itemtype => p_item_type,
1968 itemkey => p_item_key,
1969 aname => 'CREDIT_ACCOUNTING_RULE');
1970
1971 l_reason_code := wf_engine.getitemattrtext(
1972 itemtype => p_item_type,
1973 itemkey => p_item_key,
1974 aname => 'REASON');
1975
1976 l_currency_code := wf_engine.GetItemAttrText(
1977 itemtype => p_item_type,
1978 itemkey => p_item_key,
1979 aname => 'CURRENCY_CODE');
1980
1981 OPEN c(l_customer_trx_id);
1982 FETCH c INTO l_invoicing_rule_id;
1983 CLOSE c;
1984
1985 IF l_invoicing_rule_id is NOT NULL THEN
1986 IF nvl(l_credit_accounting_rule,'*')
1987 NOT IN ('LIFO','PRORATE','UNIT') THEN
1988
1989 fnd_message.set_name(
1990 application => 'AR',
1991 name => 'ARW_NEED_RULE');
1992
1993 l_need_rule_mesg := fnd_message.get;
1994
1995 wf_engine.SetItemAttrText(
1996 itemtype => p_item_type,
1997 itemkey => p_item_key,
1998 aname => 'INVALID_RULE_MESG',
1999 avalue => l_need_rule_mesg);
2000 END IF;
2001 END IF;
2002
2003 p_result := 'COMPLETE:';
2004 RETURN;
2005
2006 END IF; -- end of run mode
2007
2008 --
2009 -- CANCEL mode
2010 --
2011
2012 IF (p_funcmode = 'CANCEL') then
2013
2014 -- no result needed
2015 p_result := 'COMPLETE:';
2016 RETURN;
2017 End if;
2018
2019 --
2020 -- Other execution modes.
2021 --
2022 p_result := '';
2023 RETURN;
2024
2025 EXCEPTION
2026 WHEN OTHERS THEN
2027
2028 wf_core.context(
2029 pkg_name => 'AR_AME_CMWF_API',
2030 proc_name => 'AMECHECKRULE',
2031 arg1 => p_item_type,
2032 arg2 => p_item_key,
2033 arg3 => p_funcmode,
2034 arg4 => to_char(p_actid),
2035 arg5 => g_debug_mesg);
2036
2037 RAISE;
2038
2039 END AMECheckRule;
2040
2041
2042 /*************************************************************************/
2043 -- Written For AME Integration
2044 --
2045 -- This function is called to figure out what path has the collector
2046 -- chosen. It simply looks at an workflow attribute APPROVAL_PATH
2047 -- to determine that. If it has a value of LIMITS then the path taken
2048 -- is primary, otherwise it is non-primary.
2049
2050 PROCEDURE AMECheckPrimaryApprover(
2051 p_item_type IN VARCHAR2,
2052 p_item_key IN VARCHAR2,
2053 p_actid IN NUMBER,
2054 p_funcmode IN VARCHAR2,
2055 p_result OUT NOCOPY VARCHAR2) IS
2056
2057 l_approval_path wf_item_attribute_values.text_value%TYPE;
2058
2059 BEGIN
2060
2061 ----------------------------------------------------------
2062 g_debug_mesg := 'Entered AMECHECKPRIMARYAPPROVER';
2063 IF PG_DEBUG in ('Y', 'C') THEN
2064 arp_standard.debug('AMECheckPrimaryApprover: ' || g_debug_mesg);
2065 END IF;
2066 ----------------------------------------------------------
2067 --
2068 -- RUN mode - normal process execution
2069 --
2070 IF (p_funcmode = 'RUN') THEN
2071
2072 l_approval_path := wf_engine.getitemattrtext(
2073 itemtype => p_item_type,
2074 itemkey => p_item_key,
2075 aname => 'APPROVAL_PATH');
2076
2077 IF (l_approval_path = 'LIMITS') THEN
2078 p_result := 'COMPLETE:T';
2079 ELSE
2080 p_result := 'COMPLETE:F';
2081 END if;
2082
2083 RETURN;
2084
2085 END IF; -- END of run mode
2086
2087 --
2088 -- CANCEL mode
2089 --
2090 -- This is an event point is called with the effect of the activity must
2091 -- be undone, for example when a process is reset to an earlier point
2092 -- due to a loop back.
2093 --
2094 IF (p_funcmode = 'CANCEL') then
2095
2096 -- no result needed
2097 p_result := 'COMPLETE:';
2098 RETURN;
2099 END if;
2100
2101
2102 --
2103 -- Other execution modes may be created in the future. Your
2104 -- activity will indicate that it does not implement a mode
2105 -- by RETURNing NULL
2106 --
2107 p_result := '';
2108 RETURN;
2109
2110 EXCEPTION
2111 WHEN NO_DATA_FOUND THEN
2112 p_result := 'COMPLETE:N';
2113 RETURN;
2114 WHEN OTHERS THEN
2115
2116 wf_core.context(
2117 pkg_name => 'AR_AME_CMWF_API',
2118 proc_name => 'AMECHECKPRIMARYAPPROVER',
2119 arg1 => p_item_type,
2120 arg2 => p_item_key,
2121 arg3 => p_funcmode,
2122 arg4 => to_char(p_actid),
2123 arg5 => g_debug_mesg);
2124
2125 RAISE;
2126
2127 END AMECheckPrimaryApprover;
2128
2129 /*************************************************************************/
2130
2131
2132
2133 /*************************************************************************/
2134 -- Written For AME Integration
2135 --
2136 -- This procedure is called to find the the first primary approver,
2137 -- as well as any subsequent approver from AME. After retrieving the
2138 -- person id from AME, it stores in a workflow attribute appropriately
2139 -- named PERSON_ID. It also cals getemployeeinfo to set some attributes
2140 -- to make sure notifications are sent smoothly to this approver.
2141
2142 PROCEDURE AMEFindPrimaryApprover(
2143 p_item_type IN VARCHAR2,
2144 p_item_key IN VARCHAR2,
2145 p_actid IN NUMBER,
2146 p_funcmode IN VARCHAR2,
2147 p_result OUT NOCOPY VARCHAR2) IS
2148
2149
2150 l_next_approver ame_util.approverrecord;
2151 l_admin_approver ame_util.approverrecord;
2152 l_error_message fnd_new_messages.message_text%TYPE;
2153
2154 l_approver_employee_id wf_item_attribute_values.number_value%TYPE;
2155 l_approver_user_id wf_item_attribute_values.number_value%TYPE;
2156 l_escalation_count NUMBER;
2157 l_timeout_occured VARCHAR2(1) ; /*4139346 */
2158
2159 BEGIN
2160
2161 ----------------------------------------------------------
2162 g_debug_mesg := 'Entered AMEFINDPRIMARYAPPROVER';
2163 IF PG_DEBUG in ('Y', 'C') THEN
2164 arp_standard.debug('AMEFindPrimaryApprover: ' || g_debug_mesg);
2165 END IF;
2166 ----------------------------------------------------------
2167 --
2168 -- RUN mode - normal process execution
2169 --
2170 IF (p_funcmode = 'RUN') THEN
2171
2172 -- Bug # 4139346
2173 -- In the case of HR Hierarchy AME is dependent on the first person
2174 -- in the hierarchy. However, the approval has moved and the first
2175 -- is terminated then AME errors out. In that case, we would like
2176 -- to make the person's manager as the first person.
2177
2178 validate_first_approver(p_item_key => p_item_key);
2179
2180 -- now that we know the first approver is set correctly, go ahead
2181 -- and fetch the next approver and notice that this calls AME using
2182 -- c_approvals_transaction_type.
2183
2184 BEGIN
2185 FindNextApprover(
2186 p_item_type => p_item_type,
2187 p_item_key => p_item_key,
2188 p_ame_trx_type => c_approvals_transaction_type,
2189 x_approver_user_id => l_approver_user_id,
2190 x_approver_employee_id => l_approver_employee_id);
2191
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194
2195 -- Bug # 3865317.
2196 --
2197 -- We are anticapting that AME will raise the following exception if
2198 -- if the last approver times out:
2199 --
2200 -- "ORA-20001: AN APPROVER LACKED A SURROGATE, EITHER BECAUSE THEY WERE
2201 -- NOT IN ANY OF THE REQUIRED APPROVAL GROUPS OR BECAUSE THEY WERE THE
2202 -- FINAL APPROVER IN ONE OF THE REQUIRED APPROVAL GROUPS."
2203 --
2204 -- In this case we need to identify that this is the case and handle it
2205 -- correctly. We need to check if we are operating under timeout mode,
2206 -- if so then the last approver has timed out and we do not know who to
2207 -- send the approval to. So, we should error out and send notification
2208 -- to SYSADMIN.
2209
2210 l_timeout_occured := wf_engine.GetItemAttrText(
2211 itemtype => p_item_type,
2212 itemkey => p_item_key,
2213 aname => 'TIMEOUT_OCCURRED');
2214
2215 IF NVL(l_timeout_occured,'N') = 'Y' THEN
2216 p_result := 'COMPLETE:E';
2217 RETURN;
2218 ELSE
2219 -- if that is not the case, then this is an unexpected case
2220 -- raise the exception.
2221 RAISE;
2222 END IF;
2223
2224 END;
2225
2226 IF (l_approver_user_id IS NOT NULL) THEN
2227
2228 -- Bug # 3865317.
2229 -- We need to reset a flag to indicate that we are no
2230 -- longer in a timeout mode.
2231
2232 wf_engine.setitemattrtext(
2233 itemtype => p_item_type,
2234 itemkey => p_item_key,
2235 aname => 'TIMEOUT_OCCURRED',
2236 avalue => 'N');
2237
2238 l_escalation_count := wf_engine.getitemattrnumber(
2239 itemtype => p_item_type,
2240 itemkey => p_item_key,
2241 aname => 'ESCALATION_COUNT');
2242
2243 IF (l_escalation_count <> 0) THEN
2244 SkipIFDuplicate(
2245 p_item_type => p_item_type,
2246 p_item_key => p_item_key,
2247 p_ame_trx_type => c_approvals_transaction_type,
2248 p_approver_user_id => l_approver_user_id,
2249 p_approver_employee_id => l_approver_employee_id,
2250 x_approver_user_id => l_approver_user_id,
2251 x_approver_employee_id => l_approver_employee_id);
2252 END IF;
2253
2254 END IF;
2255
2256 IF l_approver_user_id IS NULL THEN
2257
2258 -- end of hub, reset the scalation related attributes.
2259 wf_engine.setitemattrnumber(
2260 itemtype => p_item_type,
2261 itemkey => p_item_key,
2262 aname => 'MANAGER_ID',
2263 avalue => -9999);
2264
2265 wf_engine.setitemattrnumber(
2266 itemtype => p_item_type,
2267 itemkey => p_item_key,
2268 aname => 'ESCALATION_COUNT',
2269 avalue => 0);
2270
2271 -- no issue, return F to indicate no more approvers left.
2272 p_result := 'COMPLETE:F';
2273 RETURN;
2274
2275 END IF;
2276
2277 -- The following call will set the following attributes:
2278 --
2279 -- 1. APPROVER_ID
2280 -- 2. APPROVER_USER_NAME
2281 -- 3. APPROVER_DISPLAY_NAME
2282
2283 getemployeeinfo(
2284 p_user_id => l_approver_user_id,
2285 p_item_type => p_item_type,
2286 p_item_key => p_item_key);
2287
2288 p_result := 'COMPLETE:T';
2289
2290 RETURN;
2291
2292 END IF; -- END of run mode
2293
2294 --
2295 -- CANCEL mode
2296 --
2297 -- This is an event point is called with the effect of the activity must
2298 -- be undone, for example when a process is reset to an earlier point
2299 -- due to a loop back.
2300 --
2301 IF (p_funcmode = 'CANCEL') then
2302
2303 -- no result needed
2304 p_result := 'COMPLETE:';
2305 RETURN;
2306 END if;
2307
2308
2309 --
2310 -- Other execution modes may be created in the future. Your
2311 -- activity will indicate that it does not implement a mode
2312 -- by returning NULL
2313 --
2314 p_result := '';
2315 RETURN;
2316
2317 EXCEPTION
2318 WHEN OTHERS THEN
2319
2320 wf_core.context(
2321 pkg_name => 'AR_AME_CMWF_API',
2322 proc_name => 'AMEFINDPRIMARYAPPROVER',
2323 arg1 => p_item_type,
2324 arg2 => p_item_key,
2325 arg3 => p_funcmode,
2326 arg4 => to_char(p_actid),
2327 arg5 => g_debug_mesg);
2328
2329 RAISE;
2330
2331 END AMEFindPrimaryApprover;
2332
2333 /*************************************************************************/
2334
2335 /*************************************************************************/
2336 -- Written For AME Integration
2337 --
2338 -- This procedure is called to retrieve the person selected by the collector
2339 -- to be first person in HR hierarchy. Using the retrieved user name, it then
2340 -- computes the employee and user id. It checks to see if this guy is truely
2341 -- in HR. This function does another important task of indicating the first
2342 -- person in the supervisory hierarchy. It assigns person id to a workflow
2343 -- attribute named NON_DEFAULT_START_PERSON_ID, which will be used to derive
2344 -- the value of SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID.
2345
2346 PROCEDURE AMESetNonPrimaryApprover(
2347 p_item_type IN VARCHAR2,
2348 p_item_key IN VARCHAR2,
2349 p_actid IN NUMBER,
2350 p_funcmode IN VARCHAR2,
2351 p_result OUT NOCOPY VARCHAR2) IS
2352
2353 l_approver_id fnd_user.user_id%TYPE;
2354 l_employee_id fnd_user.employee_id%TYPE;
2355 l_approver_user_name wf_item_attribute_values.text_value%TYPE;
2356
2357 CURSOR c (p_user_name VARCHAR2) IS
2358 SELECT employee_id, user_id
2359 FROM fnd_user
2360 WHERE user_name = p_user_name;
2361
2362 BEGIN
2363
2364 ----------------------------------------------------------
2365 g_debug_mesg := 'Entered AMESETNONPRIMARYAPPROVER';
2366 IF PG_DEBUG in ('Y', 'C') THEN
2367 arp_standard.debug('AMESetNonPrimaryApprover: ' || g_debug_mesg);
2368 END IF;
2369 ----------------------------------------------------------
2370 --
2371 -- RUN mode - normal process execution
2372 --
2373 IF (p_funcmode = 'RUN') THEN
2374
2375 l_approver_user_name := wf_engine.GetItemAttrText(
2376 itemtype => p_item_type,
2377 itemkey => p_item_key,
2378 aname => 'ROLE');
2379
2380 OPEN c (l_approver_user_name);
2381 FETCH c INTO l_employee_id, l_approver_id;
2382 CLOSE c;
2383
2384 -- set employee id as the non_default_starting_point_person_id.
2385 -- This is used as the value for the following attributes:
2386 --
2387 -- SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID
2388 -- JOB_LEVEL_NON_DEFAULT_STARTING_POINT_PERSON_ID
2389 --
2390 -- If this is not populated here supervisory and job level
2391 -- hierarchy would not work.
2392
2393 wf_engine.setitemattrnumber(
2394 itemtype => p_item_type,
2395 itemkey => p_item_key,
2396 aname => 'NON_DEFAULT_START_PERSON_ID',
2397 avalue => l_employee_id);
2398
2399 p_result := 'COMPLETE:';
2400
2401 RETURN;
2402
2403 END IF;
2404
2405 --
2406 -- CANCEL mode
2407 --
2408 -- This is an event point is called with the effect of the activity must
2409 -- be undone, for example when a process is reset to an earlier point
2410 -- due to a loop back.
2411 --
2412 IF (p_funcmode = 'CANCEL') then
2413
2414 -- no result needed
2415 p_result := 'COMPLETE:';
2416 RETURN;
2417 END if;
2418
2419 --
2420 -- Other execution modes may be created in the future. Your
2421 -- activity will indicate that it does not implement a mode
2422 -- by RETURNing NULL
2423 --
2424 p_result := '';
2425 RETURN;
2426
2427 EXCEPTION
2428 WHEN OTHERS THEN
2429
2430 wf_core.context(
2431 pkg_name => 'AR_AME_CMWF_API',
2432 proc_name => 'AMESETNONPRIMARYAPPROVER',
2433 arg1 => p_item_type,
2434 arg2 => p_item_key,
2435 arg3 => p_funcmode,
2436 arg4 => to_char(p_actid),
2437 arg5 => g_debug_mesg);
2438
2439 RAISE;
2440
2441 END AMESetNonPrimaryApprover;
2442
2443
2444 /*************************************************************************/
2445
2446
2447 /*************************************************************************/
2448 -- Written For AME Integration
2449 --
2450 -- This procedure is called to find the first non primary approver.
2451 -- It then stores person in a workflow attribute appropriately named
2452 -- PERSON_ID. It also cals getemployeeinfo to set some attributes
2453 -- to make sure notifications are sent smoothly to this approver.
2454
2455 PROCEDURE AMEFindNonPrimaryApprover(
2456 p_item_type IN VARCHAR2,
2457 p_item_key IN VARCHAR2,
2458 p_actid IN NUMBER,
2459 p_funcmode IN VARCHAR2,
2460 p_result OUT NOCOPY VARCHAR2) IS
2461
2462 l_next_approver ame_util.approverrecord;
2463 l_admin_approver ame_util.approverrecord;
2464 l_error_message fnd_new_messages.message_text%TYPE;
2465 l_count NUMBER;
2466
2467 l_approver_employee_id wf_item_attribute_values.number_value%TYPE;
2468 l_approver_user_id wf_item_attribute_values.number_value%TYPE;
2469
2470 BEGIN
2471
2472 ----------------------------------------------------------
2473 g_debug_mesg := 'Entered AMEFINDNONPRIMARYAPPROVER';
2474 IF PG_DEBUG in ('Y', 'C') THEN
2475 arp_standard.debug('AMEFindNonPrimaryApprover: ' || g_debug_mesg);
2476 END IF;
2477 ----------------------------------------------------------
2478 --
2479 -- RUN mode - normal process execution
2480 --
2481 IF (p_funcmode = 'RUN') THEN
2482
2483 -- Notice that this calls AME using c_approvals_transaction_type.
2484 FindNextApprover(
2485 p_item_type => p_item_type,
2486 p_item_key => p_item_key,
2487 p_ame_trx_type => c_approvals_transaction_type,
2488 x_approver_user_id => l_approver_user_id,
2489 x_approver_employee_id => l_approver_employee_id);
2490
2491 IF l_approver_user_id IS NULL THEN
2492 p_result := 'COMPLETE:F';
2493 RETURN;
2494 END IF;
2495
2496 getemployeeinfo(
2497 p_user_id => l_approver_user_id,
2498 p_item_type => p_item_type,
2499 p_item_key => p_item_key);
2500
2501 p_result := 'COMPLETE:T';
2502
2503 RETURN;
2504
2505 END IF; -- END of run mode
2506
2507 --
2508 -- CANCEL mode
2509 --
2510 -- This is an event point is called with the effect of the activity must
2511 -- be undone, for example when a process is reset to an earlier point
2512 -- due to a loop back.
2513 --
2514 IF (p_funcmode = 'CANCEL') then
2515
2516 -- no result needed
2517 p_result := 'COMPLETE:';
2518 RETURN;
2519 END if;
2520
2521
2522 --
2523 -- Other execution modes may be created in the future. Your
2524 -- activity will indicate that it does not implement a mode
2525 -- by RETURNing NULL
2526 --
2527 p_result := '';
2528 RETURN;
2529
2530 EXCEPTION
2531 WHEN OTHERS THEN
2532
2533 wf_core.context(
2534 pkg_name => 'AR_AME_CMWF_API',
2535 proc_name => 'AMEFINDNONPRIMARYAPPROVER',
2536 arg1 => p_item_type,
2537 arg2 => p_item_key,
2538 arg3 => p_funcmode,
2539 arg4 => to_char(p_actid),
2540 arg5 => g_debug_mesg);
2541
2542 RAISE;
2543
2544 END AMEFindNonPrimaryApprover;
2545
2546
2547 /*************************************************************************/
2548
2549 /*************************************************************************/
2550 -- Written For AME Integration
2551 --
2552 -- This procedure is called to find the subsequent non primary approvers.
2553 -- It also stores person in a workflow attribute appropriately named
2554 -- PERSON_ID. It also cals getemployeeinfo to set some attributes
2555 -- to make sure notifications are sent smoothly to this approver.
2556
2557 /*4139346 */
2558 PROCEDURE AMEFindNextNonPrimaryApprover (
2559 p_item_type IN VARCHAR2,
2560 p_item_key IN VARCHAR2,
2561 p_actid IN NUMBER,
2562 p_funcmode IN VARCHAR2,
2563 p_result OUT NOCOPY VARCHAR2) IS
2564
2565 l_debug_info VARCHAR2(200);
2566 l_approver_id NUMBER;
2567 l_approver_display_name wf_users.display_name%TYPE;
2568 l_approver_user_name wf_users.name%TYPE;
2569 l_count NUMBER;
2570
2571 l_approver_employee_id wf_item_attribute_values.number_value%TYPE;
2572 l_approver_user_id wf_item_attribute_values.number_value%TYPE;
2573 l_timeout_occured VARCHAR2(1) ; /*4139346 */
2574 l_escalation_count NUMBER;
2575
2576 BEGIN
2577
2578 ----------------------------------------------------------
2579 g_debug_mesg := 'Entered AMEFINDNEXTNONPRIMARYAPPROVER';
2580 IF PG_DEBUG in ('Y', 'C') THEN
2581 arp_standard.debug('AMEFindNextNonPrimaryApprover: ' || g_debug_mesg);
2582 END IF;
2583 ----------------------------------------------------------
2584 --
2585 -- RUN mode - normal process execution
2586 --
2587 IF (p_funcmode = 'RUN') THEN
2588
2589 -- Bug # 4139346
2590 -- In the case of HR Hierarchy AME is dependent on the first person
2591 -- in the hierarchy. However, the approval has moved and the first
2592 -- is terminated then AME errors out. In that case, we would like
2593 -- to make the person's manager as the first person.
2594
2595 validate_first_approver(p_item_key => p_item_key);
2596
2597 -- now that we know the first approver is set correctly, go ahead
2598 -- and fetch the next approver.
2599
2600 BEGIN
2601
2602 FindNextApprover(
2603 p_item_type => p_item_type,
2604 p_item_key => p_item_key,
2605 p_ame_trx_type => c_approvals_transaction_type,
2606 x_approver_user_id => l_approver_user_id,
2607 x_approver_employee_id => l_approver_employee_id);
2608
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611
2612 -- Bug # 3865317.
2613 --
2614 -- We are anticapting that AME will raise the following exception if
2615 -- if the last approver times out:
2616 --
2617 -- "ORA-20001: AN APPROVER LACKED A SURROGATE, EITHER BECAUSE THEY WERE
2618 -- NOT IN ANY OF THE REQUIRED APPROVAL GROUPS OR BECAUSE THEY WERE THE
2619 -- FINAL APPROVER IN ONE OF THE REQUIRED APPROVAL GROUPS."
2620 --
2621 -- In this case we need to identify that this is the case and handle it
2622 -- correctly. We need to check if we are operating under timeout mode,
2623 -- if so then the last approver has timed out and we do not know who to
2624 -- send the approval to. So, we should error out and send notification
2625 -- to SYSADMIN.
2626
2627 l_timeout_occured := wf_engine.GetItemAttrText(
2628 itemtype => p_item_type,
2629 itemkey => p_item_key,
2630 aname => 'TIMEOUT_OCCURRED');
2631
2632 IF NVL(l_timeout_occured,'N') = 'Y' THEN
2633 p_result := 'COMPLETE:E';
2634 RETURN;
2635 ELSE
2636 -- if that is not the case, then this is an unexpected case
2637 -- raise the exception.
2638 RAISE;
2639 END IF;
2640
2641 END;
2642
2643 IF (l_approver_user_id IS NOT NULL) THEN
2644
2645 -- Bug # 3865317.
2646 -- We need to reset a flag to indicate that we are no
2647 -- longer in a timeout mode.
2648
2649 wf_engine.setitemattrtext(
2650 itemtype => p_item_type,
2651 itemkey => p_item_key,
2652 aname => 'TIMEOUT_OCCURRED',
2653 avalue => 'N');
2654
2655 l_escalation_count := wf_engine.getitemattrnumber(
2656 itemtype => p_item_type,
2657 itemkey => p_item_key,
2658 aname => 'ESCALATION_COUNT');
2659
2660 IF (l_escalation_count <> 0) THEN
2661 SkipIFDuplicate(
2662 p_item_type => p_item_type,
2663 p_item_key => p_item_key,
2664 p_ame_trx_type => c_approvals_transaction_type,
2665 p_approver_user_id => l_approver_user_id,
2666 p_approver_employee_id => l_approver_employee_id,
2667 x_approver_user_id => l_approver_user_id,
2668 x_approver_employee_id => l_approver_employee_id);
2669 END IF;
2670 END IF;
2671
2672 IF l_approver_user_id IS NULL THEN
2673
2674 -- end of hub, reset the scalation related attributes.
2675 wf_engine.setitemattrnumber(
2676 itemtype => p_item_type,
2677 itemkey => p_item_key,
2678 aname => 'MANAGER_ID',
2679 avalue => -9999);
2680
2681 wf_engine.setitemattrnumber(
2682 itemtype => p_item_type,
2683 itemkey => p_item_key,
2684 aname => 'ESCALATION_COUNT',
2685 avalue => 0);
2686
2687 -- no issue, return F to indicate no more approvers left.
2688 p_result := 'COMPLETE:F';
2689 RETURN;
2690 END IF;
2691
2692 wf_engine.SetItemAttrNumber(
2693 itemtype => p_item_type,
2694 itemkey => p_item_key,
2695 aname => 'PERSON_ID',
2696 avalue => l_approver_employee_id);
2697
2698 -- get employee info works if you pass user id
2699
2700 l_approver_id := get_user_id(
2701 p_employee_id => l_approver_employee_id);
2702
2703 g_debug_mesg := 'Approver ID: ' || l_approver_id;
2704
2705 getemployeeinfo(
2706 p_user_id => l_approver_id,
2707 p_item_type => p_item_type,
2708 p_item_key => p_item_key);
2709
2710 p_result := 'COMPLETE:T';
2711
2712 RETURN;
2713
2714 END IF; -- END of run mode
2715
2716 --
2717 -- CANCEL mode
2718 --
2719 -- This is an event point is called with the effect of the activity must
2720 -- be undone, for example when a process is reset to an earlier point
2721 -- due to a loop back.
2722 --
2723 IF (p_funcmode = 'CANCEL') THEN
2724
2725 -- no result needed
2726 p_result := 'COMPLETE:';
2727 RETURN;
2728
2729 END IF;
2730
2731 --
2732 -- Other execution modes may be created in the future. Your
2733 -- activity will indicate that it does not implement a mode
2734 -- by RETURNing NULL
2735 --
2736 p_result := '';
2737 RETURN;
2738
2739 EXCEPTION
2740 WHEN OTHERS THEN
2741
2742 wf_core.context(
2743 pkg_name => 'AR_AME_CMWF_API',
2744 proc_name => 'AMEFINDNEXTNONPRIMARYAPPROVER',
2745 arg1 => p_item_type,
2746 arg2 => p_item_key,
2747 arg3 => p_funcmode,
2748 arg4 => to_char(p_actid),
2749 arg5 => g_debug_mesg);
2750
2751 RAISE;
2752
2753 END AMEFindNextNonPrimaryApprover;
2754
2755 /*************************************************************************/
2756
2757
2758
2759
2760 /*************************************************************************/
2761 -- Written For AME Integration
2762 --
2763 -- This is the procedure that gets called if we do not hear from the approver.
2764 -- We let AME know that the approver has not responded, and the we retrieve
2765 -- manager's information based on the person id of the nonresponsive approver
2766 -- and set appropriate attributes to notify him.
2767
2768 PROCEDURE AMEFindManager (p_item_type IN VARCHAR2,
2769 p_item_key IN VARCHAR2,
2770 p_actid IN NUMBER,
2771 p_funcmode IN VARCHAR2,
2772 p_result OUT NOCOPY VARCHAR2) IS
2773
2774
2775 CURSOR manager (p_employee_id NUMBER) IS
2776 SELECT supervisor_id
2777 FROM per_all_assignments_f
2778 WHERE person_id = p_employee_id
2779 AND primary_flag = 'Y' -- get primary assgt
2780 AND assignment_type = 'E' -- ensure emp assgt, not applicant assgt
2781 AND trunc(sysdate) BETWEEN effective_start_date
2782 AND effective_end_date;
2783
2784
2785 l_approval_path wf_item_attribute_values.text_value%TYPE;
2786 l_nonresponsive_employee_id fnd_user.employee_id%TYPE;
2787 l_nonresponsive_user_id fnd_user.user_id%TYPE;
2788 l_nonresponsive_user_name wf_users.name%TYPE;
2789 l_nonresponsive_display_name wf_users.display_name%TYPE;
2790 l_manager_employee_id fnd_user.employee_id%TYPE;
2791 l_manager_user_name wf_users.name%TYPE;
2792 l_manager_display_name wf_users.display_name%TYPE;
2793 l_escalation_count NUMBER;
2794 l_transaction_type VARCHAR2(30);
2795
2796 BEGIN
2797
2798 ----------------------------------------------------------
2799 g_debug_mesg := 'Entered AMEFINDMANAGER';
2800 IF PG_DEBUG in ('Y', 'C') THEN
2801 arp_standard.debug('AMEFindManager: ' || g_debug_mesg);
2802 END IF;
2803 ----------------------------------------------------------
2804 --
2805 -- RUN mode - normal process execution
2806 --
2807 IF (p_funcmode = 'RUN') THEN
2808
2809 -- First record the fact with AME that the current approver
2810 -- has not responded to the notification. This way when
2811 -- we call getnextapprover it will give us the next approver
2812 -- not the current approver back.
2813
2814 -- However, for Limits Only path, to work around a AME limitation
2815 -- we must pass approved status to AME, so that AME does not try
2816 -- to find a manager and error out.
2817
2818 l_approval_path := wf_engine.getitemattrtext(
2819 itemtype => p_item_type,
2820 itemkey => p_item_key,
2821 aname => 'APPROVAL_PATH');
2822
2823 l_transaction_type := wf_engine.GetItemAttrText(
2824 itemtype => p_item_type,
2825 itemkey => p_item_key,
2826 aname => 'CURRENT_HUB');
2827
2828 g_debug_mesg := 'Approval Path: ' || l_approval_path;
2829
2830
2831 -- If the transaction type is anything other approval transaction
2832 -- type dont even respond.
2833
2834 IF (l_transaction_type = c_approvals_transaction_type) THEN
2835
2836 IF (l_approval_path = 'HR') THEN
2837 RecordResponseWithAME (
2838 p_item_type => p_item_type,
2839 p_item_key => p_item_key,
2840 p_response => ame_util.noResponseStatus);
2841 END IF;
2842
2843 END IF;
2844
2845
2846 -- Determine the number of escalations. It is possible that the
2847 -- current approver does not respond. The notification is sent
2848 -- to his/her manager and he/she too may not respond. In that case
2849 -- notification has be to sent to the manager's manager in a recursive
2850 -- manner.
2851
2852 l_escalation_count := wf_engine.getitemattrnumber(
2853 itemtype => p_item_type,
2854 itemkey => p_item_key,
2855 aname => 'ESCALATION_COUNT');
2856
2857 IF l_escalation_count=0 THEN
2858
2859 l_nonresponsive_user_id := wf_engine.getitemattrnumber(
2860 itemtype => p_item_type,
2861 itemkey => p_item_key,
2862 aname => 'APPROVER_ID');
2863
2864 l_nonresponsive_employee_id := get_employee_id(l_nonresponsive_user_id);
2865
2866 ELSE
2867 l_nonresponsive_employee_id := wf_engine.getitemattrnumber(
2868 itemtype => p_item_type,
2869 itemkey => p_item_key,
2870 aname => 'MANAGER_ID');
2871 END IF;
2872
2873 l_escalation_count := l_escalation_count + 1;
2874
2875 wf_engine.setitemattrnumber(
2876 itemtype => p_item_type,
2877 itemkey => p_item_key,
2878 aname => 'ESCALATION_COUNT',
2879 avalue => l_escalation_count);
2880
2881 -- When the escalation notfication is sent to the manager, the manager
2882 -- must know who did not respond to the notification. So, we must
2883 -- update/populate some attributes so that notification will contain that
2884 -- information.
2885
2886 g_debug_mesg := 'Getting User Name: ';
2887
2888 wf_directory.getusername(
2889 'PER',
2890 to_char(l_nonresponsive_employee_id),
2891 l_nonresponsive_user_name,
2892 l_nonresponsive_display_name);
2893
2894 wf_engine.setitemattrnumber(
2895 itemtype => p_item_type,
2896 itemkey => p_item_key,
2897 aname => 'FORWARD_FROM_ID',
2898 avalue => l_nonresponsive_user_id);
2899
2900 wf_engine.setitemattrtext(
2901 itemtype => p_item_type,
2902 itemkey => p_item_key,
2903 aname => 'FORWARD_FROM_USER_NAME',
2904 avalue => l_nonresponsive_user_name);
2905
2906 wf_engine.setitemattrtext(
2907 itemtype => p_item_type,
2908 itemkey => p_item_key,
2909 aname => 'FORWARD_FROM_DISPLAY_NAME',
2910 avalue => l_nonresponsive_display_name);
2911
2912 -- Now get the manager for the nonresponsive approver.
2913
2914 g_debug_mesg := 'Nonresponsive Employee ID: ' ||
2915 l_nonresponsive_employee_id ;
2916
2917 OPEN manager(l_nonresponsive_employee_id);
2918 FETCH manager INTO l_manager_employee_id;
2919 CLOSE manager;
2920
2921 IF l_manager_employee_id IS NULL THEN
2922
2923 p_result := 'COMPLETE:F';
2924 RETURN;
2925
2926 ELSE
2927 wf_engine.setitemattrnumber(
2928 itemtype => p_item_type,
2929 itemkey => p_item_key,
2930 aname => 'MANAGER_ID',
2931 avalue => l_manager_employee_id);
2932
2933 wf_directory.getusername(
2934 'PER',
2935 to_char(l_manager_employee_id),
2936 l_manager_user_name,
2937 l_manager_display_name);
2938
2939 wf_engine.setitemattrtext(
2940 itemtype => p_item_type,
2941 itemkey => p_item_key,
2942 aname => 'MANAGER_USER_NAME',
2943 avalue => l_manager_user_name);
2944
2945 wf_engine.setitemattrtext(
2946 itemtype => p_item_type,
2947 itemkey => p_item_key,
2948 aname => 'MANAGER_DISPLAY_NAME',
2949 avalue => l_manager_display_name);
2950
2951 p_result := 'COMPLETE:T';
2952 RETURN;
2953
2954 END IF;
2955
2956 END IF;
2957
2958 IF (p_funcmode = 'CANCEL') THEN
2959 -- no result needed
2960 p_result := 'COMPLETE:';
2961 RETURN;
2962 END IF;
2963
2964 EXCEPTION
2965 WHEN OTHERS THEN
2966
2967 wf_core.context(
2968 pkg_name => 'AR_AME_CMWF_API',
2969 proc_name => 'AMEFINDMANAGER',
2970 arg1 => p_item_type,
2971 arg2 => p_item_key,
2972 arg3 => p_funcmode,
2973 arg4 => to_char(p_actid),
2974 arg5 => g_debug_mesg);
2975
2976 RAISE;
2977
2978 END AMEFindManager;
2979
2980
2981 PROCEDURE RecordCollectorAsForwardFrom(p_item_type IN VARCHAR2,
2982 p_item_key IN VARCHAR2,
2983 p_actid IN NUMBER,
2984 p_funcmode IN VARCHAR2,
2985 p_result OUT NOCOPY VARCHAR2) IS
2986
2987 l_collector_employee_id NUMBER;
2988 l_collector_display_name wf_users.display_name%TYPE;
2989 l_collector_user_name wf_users.name%TYPE;
2990 l_notes wf_item_attribute_values.text_value%TYPE;
2991 l_approver_notes wf_item_attribute_values.text_value%TYPE;
2992 CRLF VARCHAR2(1);
2993
2994 BEGIN
2995
2996 ----------------------------------------------------------
2997 g_debug_mesg := 'Entered RECORDCOLLECTORASFORWARDFROM';
2998 IF PG_DEBUG in ('Y', 'C') THEN
2999 arp_standard.debug('RecordCollectorAsForwardFrom: ' || g_debug_mesg);
3000 END IF;
3001 ----------------------------------------------------------
3002 --
3003 -- Bug 2105483 : rather then calling arp_global at the start
3004 -- of the package, WHERE it can error out NOCOPY since org_id is not yet set,
3005 -- do the call right before it is needed
3006
3007 -- arp_global.init_global;
3008 CRLF := arp_global.CRLF;
3009
3010 --
3011 -- RUN mode - normal process execution
3012 --
3013 IF (p_funcmode = 'RUN') then
3014
3015 ------------------------------------------------------------
3016 g_debug_mesg := 'Get the user name of collector';
3017 ------------------------------------------------------------
3018
3019
3020 l_collector_employee_id := wf_engine.GetItemAttrNumber(
3021 p_item_type,
3022 p_item_key,
3023 'COLLECTOR_EMPLOYEE_ID');
3024 wf_engine.SetItemAttrNumber(p_item_type,
3025 p_item_key,
3026 'FORWARD_FROM_ID',
3027 l_collector_employee_id);
3028
3029
3030
3031
3032
3033 l_collector_user_name := wf_engine.GetItemAttrText(p_item_type,
3034 p_item_key,
3035 'COLLECTOR_USER_NAME');
3036 wf_engine.SetItemAttrText(p_item_type,
3037 p_item_key,
3038 'FORWARD_FROM_USER_NAME',
3039 l_collector_user_name);
3040
3041
3042 l_collector_display_name := wf_engine.GetItemAttrText(p_item_type,
3043 p_item_key,
3044 'COLLECTOR_DISPLAY_NAME');
3045 wf_engine.SetItemAttrText(p_item_type,
3046 p_item_key,
3047 'FORWARD_FROM_DISPLAY_NAME',
3048 l_collector_display_name);
3049
3050 -- Add the collector user name in front of notes field.
3051
3052
3053
3054 l_approver_notes := wf_engine.GetItemAttrText(p_item_type,
3055 p_item_key,
3056 'APPROVER_NOTES');
3057
3058 l_notes := l_collector_user_name ||
3059 ': ' || l_approver_notes || CRLF;
3060
3061 wf_engine.SetItemAttrText(p_item_type,
3062 p_item_key,
3063 'NOTES',
3064 l_notes);
3065
3066 -- Initialize the approver_notes
3067
3068 l_approver_notes := NULL;
3069
3070 wf_engine.SetItemAttrText(p_item_type,
3071 p_item_key,
3072 'APPROVER_NOTES',
3073 l_approver_notes);
3074
3075
3076 -- end of hub, reset the scalation related attributes.
3077 wf_engine.setitemattrnumber(
3078 itemtype => p_item_type,
3079 itemkey => p_item_key,
3080 aname => 'MANAGER_ID',
3081 avalue => -9999);
3082
3083 wf_engine.setitemattrnumber(
3084 itemtype => p_item_type,
3085 itemkey => p_item_key,
3086 aname => 'ESCALATION_COUNT',
3087 avalue => 0);
3088
3089 p_result := 'COMPLETE:T';
3090 RETURN;
3091
3092 END if; -- END of run mode
3093
3094 --
3095 -- CANCEL mode
3096 --
3097
3098 IF (p_funcmode = 'CANCEL') then
3099
3100 -- no result needed
3101 p_result := 'COMPLETE:';
3102 RETURN;
3103 END if;
3104
3105
3106 --
3107 -- Other execution modes
3108 --
3109 p_result := '';
3110 RETURN;
3111
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114
3115 wf_core.context(
3116 pkg_name => 'AR_AME_CMWF_API',
3117 proc_name => 'RECORDCOLLECTORASFORWARDFROM',
3118 arg1 => p_item_type,
3119 arg2 => p_item_key,
3120 arg3 => p_funcmode,
3121 arg4 => to_char(p_actid),
3122 arg5 => g_debug_mesg);
3123
3124 RAISE;
3125
3126 END RecordCollectorAsForwardFrom;
3127
3128
3129 PROCEDURE RecordForwardToUserInfo(p_item_type IN VARCHAR2,
3130 p_item_key IN VARCHAR2,
3131 p_actid IN NUMBER,
3132 p_funcmode IN VARCHAR2,
3133 p_result OUT NOCOPY VARCHAR2) IS
3134
3135
3136 l_approver_id NUMBER;
3137 l_approver_display_name wf_users.display_name%TYPE;
3138 l_approver_user_name wf_users.name%TYPE;
3139
3140 BEGIN
3141
3142 ----------------------------------------------------------
3143 g_debug_mesg := 'Entered RECORDFORWARDTOUSERINFO';
3144 IF PG_DEBUG in ('Y', 'C') THEN
3145 arp_standard.debug('RecordForwardToUserInfo: ' || g_debug_mesg);
3146 END IF;
3147 ----------------------------------------------------------
3148 --
3149 -- RUN mode - normal process execution
3150 --
3151 IF (p_funcmode = 'RUN') then
3152
3153 ------------------------------------------------------------
3154 g_debug_mesg := 'Get the user name of approver';
3155 ------------------------------------------------------------
3156
3157 l_approver_id := wf_engine.GetItemAttrNumber(
3158 p_item_type,
3159 p_item_key,
3160 'APPROVER_ID');
3161
3162 wf_engine.SetItemAttrNumber(
3163 p_item_type,
3164 p_item_key,
3165 'FORWARD_TO_ID',
3166 l_approver_id);
3167
3168 l_approver_user_name := wf_engine.GetItemAttrText(
3169 p_item_type,
3170 p_item_key,
3171 'APPROVER_USER_NAME');
3172
3173 wf_engine.SetItemAttrText(
3174 p_item_type,
3175 p_item_key,
3176 'FORWARD_TO_USER_NAME',
3177 l_approver_user_name);
3178
3179
3180 l_approver_display_name := wf_engine.GetItemAttrText(
3181 p_item_type,
3182 p_item_key,
3183 'APPROVER_DISPLAY_NAME');
3184
3185 wf_engine.SetItemAttrText(
3186 p_item_type,
3187 p_item_key,
3188 'FORWARD_TO_DISPLAY_NAME',
3189 l_approver_display_name);
3190
3191 p_result := 'COMPLETE:T';
3192
3193 RETURN;
3194
3195 END if; -- END of run mode
3196
3197 --
3198 -- CANCEL mode
3199 --
3200 -- This is an event point is called with the effect of the activity must
3201 -- be undone, for example when a process is reset to an earlier point
3202 -- due to a loop back.
3203 --
3204 IF (p_funcmode = 'CANCEL') then
3205
3206 -- no result needed
3207 p_result := 'COMPLETE:';
3208 RETURN;
3209 END if;
3210
3211
3212 --
3213 -- Other execution modes may be created in the future. Your
3214 -- activity will indicate that it does not implement a mode
3215 -- by RETURNing NULL
3216 --
3217 p_result := '';
3218 RETURN;
3219
3220 EXCEPTION
3221 WHEN OTHERS THEN
3222
3223
3224 wf_core.context(
3225 pkg_name => 'AR_AME_CMWF_API',
3226 proc_name => 'RECORDFORWARDTOUSERINFO',
3227 arg1 => p_item_type,
3228 arg2 => p_item_key,
3229 arg3 => p_funcmode,
3230 arg4 => to_char(p_actid),
3231 arg5 => g_debug_mesg);
3232
3233 RAISE;
3234
3235 END RecordForwardToUserInfo;
3236
3237
3238 PROCEDURE CheckForwardFromUser(p_item_type IN VARCHAR2,
3239 p_item_key IN VARCHAR2,
3240 p_actid IN NUMBER,
3241 p_funcmode IN VARCHAR2,
3242 p_result OUT NOCOPY VARCHAR2) IS
3243
3244 l_forward_from_user_name VARCHAR2(100);
3245
3246 BEGIN
3247
3248 ----------------------------------------------------------
3249 g_debug_mesg := 'Entered CHECKFORWARDFROMUSER';
3250 IF PG_DEBUG in ('Y', 'C') THEN
3251 arp_standard.debug('CheckForwardFromUser: ' || g_debug_mesg);
3252 END IF;
3253 ----------------------------------------------------------
3254 --
3255 -- RUN mode - normal process execution
3256 --
3257 IF (p_funcmode = 'RUN') then
3258
3259 ------------------------------------------------------------
3260 g_debug_mesg := 'Get the user name of forward from user';
3261 ------------------------------------------------------------
3262
3263 l_forward_from_user_name := wf_engine.GetItemAttrText(
3264 p_item_type,
3265 p_item_key,
3266 'FORWARD_FROM_USER_NAME');
3267
3268
3269 IF l_forward_from_user_name is NOT NULL then
3270 p_result := 'COMPLETE:T';
3271 RETURN;
3272 ELSE
3273 p_result := 'COMPLETE:F';
3274 RETURN;
3275 END if;
3276
3277 END if; -- END of run mode
3278
3279 --
3280 -- CANCEL mode
3281 --
3282 --
3283 IF (p_funcmode = 'CANCEL') then
3284
3285 -- no result needed
3286 p_result := 'COMPLETE:';
3287 RETURN;
3288 END if;
3289
3290
3291 --
3292 -- Other execution mode
3293
3294 p_result := '';
3295 RETURN;
3296
3297 EXCEPTION
3298 WHEN OTHERS THEN
3299
3300 wf_core.context(
3301 pkg_name => 'AR_AME_CMWF_API',
3302 proc_name => 'CHECKFORWARDFROMUSER',
3303 arg1 => p_item_type,
3304 arg2 => p_item_key,
3305 arg3 => p_funcmode,
3306 arg4 => to_char(p_actid),
3307 arg5 => g_debug_mesg);
3308
3309 RAISE;
3310
3311 END CheckForwardFromUser;
3312
3313
3314 PROCEDURE RecordApproverAsForwardFrom(p_item_type IN VARCHAR2,
3315 p_item_key IN VARCHAR2,
3316 p_actid IN NUMBER,
3317 p_funcmode IN VARCHAR2,
3318 p_result OUT NOCOPY VARCHAR2) IS
3319
3320 l_approver_id NUMBER;
3321 l_approver_user_name wf_users.name%TYPE;
3322 l_approver_display_name wf_users.display_name%TYPE;
3323 l_notes wf_item_attribute_values.text_value%TYPE;
3324 l_approver_notes wf_item_attribute_values.text_value%TYPE;
3325 CRLF VARCHAR2(1);
3326
3327 BEGIN
3328
3329 ----------------------------------------------------------
3330 g_debug_mesg := 'Entered RECORDAPPROVERASFORWARDFROM';
3331 IF PG_DEBUG in ('Y', 'C') THEN
3332 arp_standard.debug('RecordApproverAsForwardFrom: ' || g_debug_mesg);
3333 END IF;
3334 ----------------------------------------------------------
3335
3336 -- Bug 2105483 : rather then calling arp_global at the start
3337 -- of the package, WHERE it can error out since org_id is not yet set,
3338 -- do the call right before it is needed
3339
3340 -- arp_global.init_global;
3341 CRLF := arp_global.CRLF;
3342
3343 --
3344 -- RUN mode - normal process execution
3345 --
3346 IF (p_funcmode = 'RUN') then
3347
3348 ------------------------------------------------------------
3349 g_debug_mesg := 'Get info for an approver';
3350 ------------------------------------------------------------
3351
3352
3353
3354 l_approver_id := wf_engine.GetItemAttrNumber(p_item_type,
3355 p_item_key,
3356 'APPROVER_ID');
3357 wf_engine.SetItemAttrNumber(p_item_type,
3358 p_item_key,
3359 'FORWARD_FROM_ID',
3360 l_approver_id);
3361
3362
3363
3364 l_approver_user_name := wf_engine.GetItemAttrText(p_item_type,
3365 p_item_key,
3366 'APPROVER_USER_NAME');
3367 wf_engine.SetItemAttrText(p_item_type,
3368 p_item_key,
3369 'FORWARD_FROM_USER_NAME',
3370 l_approver_user_name);
3371
3372
3373 l_approver_display_name := wf_engine.GetItemAttrText(p_item_type,
3374 p_item_key,
3375 'APPROVER_DISPLAY_NAME');
3376 wf_engine.SetItemAttrText(p_item_type,
3377 p_item_key,
3378 'FORWARD_FROM_DISPLAY_NAME',
3379 l_approver_display_name);
3380
3381 -- Add the approver user name in front of notes field.
3382
3383 l_notes := wf_engine.GetItemAttrText(p_item_type,
3384 p_item_key,
3385 'NOTES');
3386
3387 l_approver_notes := wf_engine.GetItemAttrText(p_item_type,
3388 p_item_key,
3389 'APPROVER_NOTES');
3390
3391
3392 l_notes := l_notes || l_approver_user_name ||
3393 ': ' || l_approver_notes || CRLF;
3394
3395 wf_engine.SetItemAttrText(p_item_type,
3396 p_item_key,
3397 'NOTES',
3398 l_notes);
3399
3400 -- Initialize the approver_notes
3401
3402 l_approver_notes := NULL;
3403
3404 wf_engine.SetItemAttrText(p_item_type,
3405 p_item_key,
3406 'APPROVER_NOTES',
3407 l_approver_notes);
3408
3409
3410
3411 p_result := 'COMPLETE:T';
3412 RETURN;
3413
3414 END if; -- END of run mode
3415
3416 --
3417 -- CANCEL mode
3418 --
3419
3420 IF (p_funcmode = 'CANCEL') then
3421
3422 -- no result needed
3423 p_result := 'COMPLETE:';
3424 RETURN;
3425 END if;
3426
3427
3428 --
3429 -- Other execution modes
3430 --
3431 p_result := '';
3432 RETURN;
3433
3434 EXCEPTION
3435 WHEN OTHERS THEN
3436
3437 wf_core.context(
3438 pkg_name => 'AR_AME_CMWF_API',
3439 proc_name => 'RECORDAPPROVERASFORWARDFROM',
3440 arg1 => p_item_type,
3441 arg2 => p_item_key,
3442 arg3 => p_funcmode,
3443 arg4 => to_char(p_actid),
3444 arg5 => g_debug_mesg);
3445
3446 RAISE;
3447
3448 END RecordApproverAsForwardFrom;
3449
3450
3451 PROCEDURE CheckFinalApprover(p_reason_code IN VARCHAR2,
3452 p_currency_code IN VARCHAR2,
3453 p_amount IN VARCHAR2,
3454 p_approver_id IN NUMBER,
3455 p_result_flag OUT NOCOPY VARCHAR2) IS
3456
3457 l_amount_to NUMBER;
3458 l_amount_from NUMBER;
3459
3460 BEGIN
3461
3462 ----------------------------------------------------------
3463 g_debug_mesg := 'Entered CHECKFINALAPPROVER';
3464 IF PG_DEBUG in ('Y', 'C') THEN
3465 arp_standard.debug('CheckFinalApprover: ' || g_debug_mesg);
3466 END IF;
3467 ----------------------------------------------------------
3468
3469 SELECT aul.amount_to, aul.amount_from INTO l_amount_to, l_amount_from
3470 FROM ar_approval_user_limits aul
3471 WHERE aul.user_id = p_approver_id
3472 AND aul.reason_code = p_reason_code
3473 AND aul.currency_code = p_currency_code ;
3474
3475 IF ( ( p_amount < l_amount_to) and
3476 ( p_amount >= l_amount_from)) then
3477
3478 p_result_flag := 'Y';
3479
3480 ELSE
3481
3482 p_result_flag := 'N';
3483 END IF ;
3484
3485 RETURN;
3486
3487 EXCEPTION
3488 WHEN NO_DATA_FOUND THEN
3489 p_result_flag := 'N';
3490 RETURN;
3491
3492 WHEN OTHERS THEN
3493
3494 wf_core.context(
3495 pkg_name => 'AR_AME_CMWF_API',
3496 proc_name => 'CHECKFINALAPPROVER',
3497 arg1 => c_item_type,
3498 arg2 => NULL,
3499 arg3 => NULL,
3500 arg4 => NULL,
3501 arg5 => g_debug_mesg);
3502
3503 RAISE;
3504
3505 END CheckFinalApprover;
3506
3507
3508 PROCEDURE RemoveFromDispute (p_item_type IN VARCHAR2,
3509 p_item_key IN VARCHAR2,
3510 p_actid IN NUMBER,
3511 p_funcmode IN VARCHAR2,
3512 p_result OUT NOCOPY VARCHAR2) IS
3513
3514 l_approver_id NUMBER;
3515 l_reason_code VARCHAR2(45);
3516 l_currency_code VARCHAR2(15);
3517 l_total_credit_to_invoice NUMBER;
3518 l_result_flag VARCHAR2(1);
3519 l_customer_trx_id NUMBER;
3520
3521 /* bug 4469453 */
3522 l_request_id number;
3523 new_dispute_date date;
3524 new_dispute_amt number;
3525 remove_from_dispute_amt number;
3526
3527 /*4432212 */
3528
3529 CURSOR ps_cur(p_customer_trx_id NUMBER) IS
3530 SELECT payment_schedule_id, due_date, amount_in_dispute, dispute_date
3531 FROM ar_payment_schedules ps
3532 WHERE ps.customer_trx_id = p_customer_trx_id;
3533
3534
3535 BEGIN
3536
3537
3538 ----------------------------------------------------------
3539 g_debug_mesg := 'Entered REMOVEFROMDISPUTE';
3540 IF PG_DEBUG in ('Y', 'C') THEN
3541 arp_standard.debug('RemoveFromDispute: ' || g_debug_mesg);
3542 END IF;
3543 ----------------------------------------------------------
3544
3545 --
3546 -- RUN mode - normal process execution
3547 --
3548 IF (p_funcmode = 'RUN') then
3549
3550 l_customer_trx_id := wf_engine.GetItemAttrNumber(
3551 p_item_type,
3552 p_item_key,
3553 'CUSTOMER_TRX_ID');
3554
3555 l_request_id := WF_ENGINE.GetItemAttrNumber(
3556 p_item_type,
3557 p_item_key,
3558 'WORKFLOW_DOCUMENT_ID');
3559
3560 -- the amount stored in ra_cm_requests is the Credit amount, it needs to
3561 -- be negated to get the correct dispute amount
3562 SELECT total_amount * -1
3563 into remove_from_dispute_amt
3564 from ra_cm_requests
3565 WHERE request_id = l_request_id;
3566
3567
3568 /*4432212 */
3569 BEGIN
3570
3571 FOR ps_rec IN ps_cur (l_customer_trx_id )
3572 LOOP
3573
3574 new_dispute_amt := ps_rec.amount_in_dispute - remove_from_dispute_amt;
3575
3576 if new_dispute_amt = 0 then
3577 new_dispute_date := null;
3578 else
3579 new_dispute_date := ps_rec.dispute_date;
3580 end if;
3581
3582
3583 arp_process_cutil.update_ps
3584 (p_ps_id=> ps_rec.payment_schedule_id,
3585 p_due_date=> ps_rec.due_date,
3586 p_amount_in_dispute=> new_dispute_amt,
3587 p_dispute_date=> new_dispute_date,
3588 p_update_dff => 'N',
3589 p_attribute_category=>NULL,
3590 p_attribute1=>NULL,
3591 p_attribute2=>NULL,
3592 p_attribute3=>NULL,
3593 p_attribute4=>NULL,
3594 p_attribute5=>NULL,
3595 p_attribute6=>NULL,
3596 p_attribute7=>NULL,
3597 p_attribute8=>NULL,
3598 p_attribute9=>NULL,
3599 p_attribute10=>NULL,
3600 p_attribute11=>NULL,
3601 p_attribute12=>NULL,
3602 p_attribute13=>NULL,
3603 p_attribute14=>NULL,
3604 p_attribute15=>NULL );
3605
3606 END LOOP;
3607 END;
3608
3609 l_reason_code := wf_engine.GetItemAttrText(
3610 p_item_type,
3611 p_item_key,
3612 'REASON');
3613
3614 l_currency_code := wf_engine.GetItemAttrText(
3615 p_item_type,
3616 p_item_key,
3617 'CURRENCY_CODE');
3618
3619 l_total_credit_to_invoice
3620 := wf_engine.GetItemAttrNumber(
3621 p_item_type,
3622 p_item_key,
3623 'TOTAL_CREDIT_TO_INVOICE');
3624
3625
3626
3627 l_approver_id := wf_engine.GetItemAttrNumber(p_item_type,
3628 p_item_key,
3629 'APPROVER_ID');
3630
3631 CheckFinalApprover(l_reason_code,
3632 l_currency_code,
3633 l_total_credit_to_invoice,
3634 l_approver_id,
3635 l_result_flag);
3636
3637
3638
3639 IF (l_result_flag = 'Y') then
3640
3641 -- it is a final aprrover
3642 p_result := 'COMPLETE:T';
3643 RETURN;
3644 ELSE
3645 p_result := 'COMPLETE:F';
3646 RETURN;
3647 END if;
3648
3649 END if; -- END of run mode
3650
3651 --
3652 -- CANCEL mode
3653 --
3654
3655 IF (p_funcmode = 'CANCEL') then
3656
3657 -- no result needed
3658 p_result := 'COMPLETE:';
3659 RETURN;
3660 END if;
3661
3662
3663 --
3664 -- Other execution modes
3665 --
3666 p_result := '';
3667 RETURN;
3668
3669 EXCEPTION
3670 WHEN OTHERS THEN
3671
3672 wf_core.context(
3673 pkg_name => 'AR_AME_CMWF_API',
3674 proc_name => 'REMOVEFROMDISPUTE',
3675 arg1 => p_item_type,
3676 arg2 => p_item_key,
3677 arg3 => p_funcmode,
3678 arg4 => to_char(p_actid),
3679 arg5 => g_debug_mesg);
3680
3681 RAISE;
3682
3683 END RemoveFromDispute;
3684
3685
3686 /****************************************************************************/
3687 --
3688 -- This procedure is called to find the Receivable user.
3689 -- Notice that this calls AME using c_receivable_transaction_type.
3690
3691 PROCEDURE AMEFindReceivableApprover(
3692 p_item_type IN VARCHAR2,
3693 p_item_key IN VARCHAR2,
3694 p_actid IN NUMBER,
3695 p_funcmode IN VARCHAR2,
3696 p_result OUT NOCOPY VARCHAR2) IS
3697
3698 l_next_approver ame_util.approverrecord;
3699 l_admin_approver ame_util.approverrecord;
3700 l_error_message VARCHAR2(2000);
3701
3702 l_approver_employee_id wf_item_attribute_values.number_value%TYPE;
3703 l_approver_user_id wf_item_attribute_values.number_value%TYPE;
3704
3705 BEGIN
3706
3707 ----------------------------------------------------------
3708 g_debug_mesg := 'Entered FINDRECEIVABLEROLE';
3709 IF PG_DEBUG in ('Y', 'C') THEN
3710 arp_standard.debug('AMEFindReceivableApprover: ' || g_debug_mesg);
3711 END IF;
3712 ----------------------------------------------------------
3713
3714 --
3715 -- RUN mode - normal process execution
3716 --
3717 IF (p_funcmode = 'RUN') then
3718
3719 -----------------------------------------------------------------
3720 g_debug_mesg := 'Check if Receivable Approver has been defined';
3721 -----------------------------------------------------------------
3722
3723 FindNextApprover(
3724 p_item_type => p_item_type,
3725 p_item_key => p_item_key,
3726 p_ame_trx_type => c_receivable_transaction_type,
3727 x_approver_user_id => l_approver_user_id,
3728 x_approver_employee_id => l_approver_employee_id);
3729
3730 IF l_approver_user_id IS NULL THEN
3731 p_result := 'COMPLETE:F';
3732 RETURN;
3733 END IF;
3734
3735 wf_engine.SetItemAttrText(
3736 itemtype => c_item_type,
3737 itemkey => p_item_key,
3738 aname => 'CURRENT_HUB',
3739 avalue => c_receivable_transaction_type);
3740
3741 -- The following call will set the following attributes:
3742 --
3743 -- 1. APPROVER_ID
3744 -- 2. APPROVER_USER_NAME
3745 -- 3. APPROVER_DISPLAY_NAME
3746
3747 getemployeeinfo(
3748 p_user_id => l_approver_user_id,
3749 p_item_type => p_item_type,
3750 p_item_key => p_item_key);
3751
3752 p_result := 'COMPLETE:T';
3753
3754 RETURN;
3755
3756 END if; -- END of run mode
3757
3758 --
3759 -- CANCEL mode
3760 --
3761 --
3762 IF (p_funcmode = 'CANCEL') then
3763
3764 -- no result needed
3765 p_result := 'COMPLETE:';
3766 RETURN;
3767 END if;
3768
3769
3770 --
3771 -- Other execution modes may be created in the future. Your
3772 -- activity will indicate that it does not implement a mode
3773 -- by returning NULL
3774 --
3775 p_result := '';
3776 RETURN;
3777
3778 EXCEPTION
3779 WHEN OTHERS THEN
3780
3781 wf_core.context(
3782 pkg_name => 'AR_AME_CMWF_API',
3783 proc_name => 'AMEFINDRECEIVABLEAPPROVER',
3784 arg1 => p_item_type,
3785 arg2 => p_item_key,
3786 arg3 => p_funcmode,
3787 arg4 => to_char(p_actid),
3788 arg5 => g_debug_mesg);
3789
3790 RAISE;
3791
3792 END AMEFindReceivableApprover;
3793
3794 /*****************************************************************************/
3795
3796
3797 PROCEDURE FindResponder (p_item_type IN VARCHAR2,
3798 p_item_key IN VARCHAR2,
3799 p_actid IN NUMBER,
3800 p_funcmode IN VARCHAR2,
3801 p_result OUT NOCOPY VARCHAR2) IS
3802
3803 l_approver_id NUMBER;
3804 l_approver_user_name wf_users.name%TYPE;
3805 l_approver_display_name wf_users.display_name%TYPE;
3806 l_notification_id NUMBER;
3807
3808 BEGIN
3809
3810 ----------------------------------------------------------
3811 g_debug_mesg := 'Entered FINDRSPONDER';
3812 IF PG_DEBUG in ('Y', 'C') THEN
3813 arp_standard.debug('FindResponder: ' || g_debug_mesg);
3814 END IF;
3815 ----------------------------------------------------------
3816
3817 --
3818 -- RUN mode - normal process execution
3819 --
3820 IF (p_funcmode = 'RESPOND') then
3821
3822 -----------------------------------------------------------------
3823 g_debug_mesg := 'Find user in Receivable role who responded to
3824 the notification';
3825 -----------------------------------------------------------------
3826
3827 l_notification_id := wf_engine.context_nid;
3828 l_approver_user_name := wf_engine.context_text;
3829
3830 SELECT orig_system_id, display_name
3831 INTO l_approver_id, l_approver_display_name
3832 FROM wf_users
3833 WHERE orig_system = 'PER'
3834 AND name = l_approver_user_name;
3835
3836 wf_engine.SetItemAttrText(p_item_type,
3837 p_item_key,
3838 'APPROVER_ID',
3839 l_approver_id);
3840
3841 wf_engine.SetItemAttrText(p_item_type,
3842 p_item_key,
3843 'APPROVER_USER_NAME',
3844 l_approver_user_name);
3845
3846 wf_engine.SetItemAttrText(p_item_type,
3847 p_item_key,
3848 'APPROVER_DISPLAY_NAME',
3849 l_approver_display_name);
3850
3851 p_result := 'COMPLETE:T';
3852 RETURN;
3853
3854 END if; -- END of run mode
3855
3856 --
3857 -- CANCEL mode
3858 --
3859 -- This is an event point is called with the effect of the activity must
3860 -- be undone, for example when a process is reset to an earlier point
3861 -- due to a loop back.
3862 --
3863 IF (p_funcmode = 'CANCEL') then
3864
3865 -- no result needed
3866 p_result := 'COMPLETE:';
3867 RETURN;
3868 END if;
3869
3870
3871 --
3872 -- Other execution modes may be created in the future. Your
3873 -- activity will indicate that it does not implement a mode
3874 -- by RETURNing NULL
3875 --
3876 p_result := '';
3877 RETURN;
3878
3879 EXCEPTION
3880 WHEN OTHERS THEN
3881
3882 wf_core.context(
3883 pkg_name => 'AR_AME_CMWF_API',
3884 proc_name => 'FINDRESPONDER',
3885 arg1 => p_item_type,
3886 arg2 => p_item_key,
3887 arg3 => p_funcmode,
3888 arg4 => to_char(p_actid),
3889 arg5 => g_debug_mesg);
3890
3891 RAISE;
3892
3893 END FindResponder;
3894
3895 /*7367350 Changed procedure to insert note in Invoice maintenance notes.
3896 A new note is inserted in for internal comment */
3897 PROCEDURE InsertSubmissionNotes(p_item_type IN VARCHAR2,
3898 p_item_key IN VARCHAR2,
3899 p_actid IN NUMBER,
3900 p_funcmode IN VARCHAR2,
3901 p_result OUT NOCOPY VARCHAR2) IS
3902
3903 l_document_id NUMBER;
3904 l_requestor_user_name wf_users.name%TYPE;
3905 l_customer_trx_id NUMBER;
3906 l_note_id NUMBER;
3907 l_reason_code VARCHAR2(45);
3908 l_total_credit_to_invoice NUMBER;
3909 l_note_text ar_notes.text%type;
3910 /*Bug3206020 Changed comments size from 240 to 1760. */
3911 l_comments VARCHAR2(1760);
3912 l_reason_meaning VARCHAR2(100);
3913
3914 /*7367350*/
3915 l_internal_comment VARCHAR2(1760) DEFAULT NULL;
3916 l_note_text1 ar_notes.text%type;
3917 l_comment_type VARCHAR2(20);
3918
3919 BEGIN
3920
3921 ----------------------------------------------------------
3922 g_debug_mesg := 'Entered INSERTSUBMISSIONNOTES';
3923 IF PG_DEBUG in ('Y', 'C') THEN
3924 arp_standard.debug('InsertSubmissionNotes: ' || g_debug_mesg);
3925 END IF;
3926 ----------------------------------------------------------
3927
3928 --
3929 -- RUN mode - normal process execution
3930 --
3931 IF (p_funcmode = 'RUN') then
3932
3933
3934 l_document_id := wf_engine.GetItemAttrNumber(
3935 p_item_type,
3936 p_item_key,
3937 'WORKFLOW_DOCUMENT_ID');
3938
3939 l_requestor_user_name
3940 := wf_engine.GetItemAttrText(
3941 p_item_type,
3942 p_item_key,
3943 'REQUESTOR_USER_NAME');
3944
3945
3946 l_reason_code := wf_engine.GetItemAttrText(
3947 p_item_type,
3948 p_item_key,
3949 'REASON');
3950
3951
3952 l_total_credit_to_invoice
3953 := wf_engine.GetItemAttrNumber(
3954 p_item_type,
3955 p_item_key,
3956 'TOTAL_CREDIT_TO_INVOICE');
3957
3958
3959 l_customer_trx_id := wf_engine.GetItemAttrNumber(
3960 p_item_type,
3961 p_item_key,
3962 'CUSTOMER_TRX_ID');
3963
3964 l_comments := wf_engine.GetItemAttrText(
3965 p_item_type,
3966 p_item_key,
3967 'COMMENTS');
3968
3969 /*7367350 Retrieve internal comment*/
3970 l_internal_comment := wf_engine.GetItemAttrText(
3971 p_item_type,
3972 p_item_key,
3973 'INTERNAL_COMMENTS');
3974
3975 -- bug fix 1202680 -- notes should reflect meaning and not the code.
3976
3977 BEGIN
3978 SELECT meaning into l_reason_meaning
3979 from ar_lookups
3980 WHERE lookup_type = 'CREDIT_MEMO_REASON'
3981 and lookup_code = l_reason_code;
3982 EXCEPTION
3983 when others then
3984 l_reason_meaning := l_reason_code;
3985 END;
3986
3987 fnd_message.set_name('AR', 'AR_WF_SUBMISSION');
3988 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3989 fnd_message.set_token('REQUESTOR', l_requestor_user_name);
3990 fnd_message.set_token('AMOUNT', to_char(l_total_credit_to_invoice));
3991 fnd_message.set_token('REASON', l_reason_meaning);
3992
3993 l_note_text := fnd_message.get;
3994 l_note_text1 := l_note_text;
3995
3996 IF l_comments is NOT NULL then
3997 select meaning into l_comment_type
3998 from ar_lookups
3999 where LOOKUP_TYPE='AR_COMMENT_CLASSIFICATION'
4000 AND LOOKUP_CODE='C';
4001 l_note_text := l_note_text || ' :' || l_comment_type || ': "' || l_comments || '"';
4002 END IF;
4003
4004 /*bug 7367350 Changes to insert internla commen notes in invoice maintenance */
4005 IF l_internal_comment is NOT NULL then
4006 select meaning into l_comment_type
4007 from ar_lookups
4008 where LOOKUP_TYPE='AR_COMMENT_CLASSIFICATION'
4009 AND LOOKUP_CODE='I';
4010 l_note_text1 := l_note_text1 || ' :' || l_comment_type || ': "' || l_internal_comment || '"';
4011
4012 InsertTrxNotes(NULL,
4013 NULL,
4014 NULL,
4015 l_customer_trx_id,
4016 'MAINTAIN',
4017 l_note_text1,
4018 l_note_id);
4019 END IF;
4020
4021
4022 InsertTrxNotes(NULL,
4023 NULL,
4024 NULL,
4025 l_customer_trx_id,
4026 'MAINTAIN',
4027 l_note_text,
4028 l_note_id);
4029
4030
4031
4032
4033 p_result := 'COMPLETE:T';
4034 RETURN;
4035
4036
4037 END if; -- END of run mode
4038
4039 --
4040 -- CANCEL mode
4041 --
4042
4043 IF (p_funcmode = 'CANCEL') then
4044
4045 -- no result needed
4046 p_result := 'COMPLETE:';
4047 RETURN;
4048 END if;
4049
4050
4051 --
4052 -- Other execution modes
4053 --
4054 p_result := '';
4055 RETURN;
4056
4057 EXCEPTION
4058 WHEN OTHERS THEN
4059
4060 wf_core.context(
4061 pkg_name => 'AR_AME_CMWF_API',
4062 proc_name => 'INSERTSUBMISSIONNOTES',
4063 arg1 => p_item_type,
4064 arg2 => p_item_key,
4065 arg3 => p_funcmode,
4066 arg4 => to_char(p_actid),
4067 arg5 => g_debug_mesg);
4068
4069 RAISE;
4070
4071 END InsertSubmissionNotes;
4072
4073
4074 PROCEDURE InsertApprovalReminderNotes(p_item_type IN VARCHAR2,
4075 p_item_key IN VARCHAR2,
4076 p_actid IN NUMBER,
4077 p_funcmode IN VARCHAR2,
4078 p_result OUT NOCOPY VARCHAR2) IS
4079
4080 l_document_id NUMBER;
4081 l_customer_trx_id NUMBER;
4082 l_approver_display_name wf_users.display_name%TYPE;
4083 l_note_id NUMBER;
4084 l_note_text ar_notes.text%type;
4085 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4086
4087 BEGIN
4088
4089 ----------------------------------------------------------
4090 g_debug_mesg := 'Entered INSERTAPPROVALREMINDERNOTES';
4091 IF PG_DEBUG in ('Y', 'C') THEN
4092 arp_standard.debug('InsertApprovalReminderNotes: ' || g_debug_mesg);
4093 END IF;
4094 ----------------------------------------------------------
4095
4096 --
4097 -- RUN mode - normal process execution
4098 --
4099 IF (p_funcmode = 'RUN') then
4100
4101 l_document_id := wf_engine.GetItemAttrNumber(
4102 p_item_type,
4103 p_item_key,
4104 'WORKFLOW_DOCUMENT_ID');
4105
4106 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4107 p_item_type,
4108 p_item_key,
4109 'CUSTOMER_TRX_ID');
4110
4111 l_approver_display_name
4112 := wf_engine.GetItemAttrText(
4113 p_item_type,
4114 p_item_key,
4115 'APPROVER_DISPLAY_NAME');
4116
4117 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4118
4119 fnd_message.set_name('AR', 'AR_WF_APPROVAL_REMINDER');
4120 fnd_message.set_token('APPROVER', l_approver_display_name);
4121 -- bug fix 1122477
4122
4123 l_note_text := fnd_message.get;
4124
4125 IF l_notes is NOT NULL then
4126 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4127 END IF;
4128
4129 InsertTrxNotes(NULL,
4130 NULL,
4131 NULL,
4132 l_customer_trx_id,
4133 'MAINTAIN',
4134 l_note_text,
4135 l_note_id);
4136
4137
4138 p_result := 'COMPLETE:T';
4139 RETURN;
4140
4141
4142 END if; -- END of run mode
4143
4144 --
4145 -- CANCEL mode
4146 --
4147
4148 IF (p_funcmode = 'CANCEL') then
4149
4150 -- no result needed
4151 p_result := 'COMPLETE:';
4152 RETURN;
4153 END if;
4154
4155
4156 --
4157 -- Other execution modes
4158 --
4159 p_result := '';
4160 RETURN;
4161
4162 EXCEPTION
4163 WHEN OTHERS THEN
4164
4165 wf_core.context(
4166 pkg_name => 'AR_AME_CMWF_API',
4167 proc_name => 'INSERTAPPROVALREMINDERNOTES',
4168 arg1 => p_item_type,
4169 arg2 => p_item_key,
4170 arg3 => p_funcmode,
4171 arg4 => to_char(p_actid),
4172 arg5 => g_debug_mesg);
4173
4174 RAISE;
4175
4176 END InsertApprovalReminderNotes;
4177
4178
4179 PROCEDURE InsertEscalationNotes (p_item_type IN VARCHAR2,
4180 p_item_key IN VARCHAR2,
4181 p_actid IN NUMBER,
4182 p_funcmode IN VARCHAR2,
4183 p_result OUT NOCOPY VARCHAR2) IS
4184
4185 l_document_id NUMBER;
4186 l_customer_trx_id NUMBER;
4187 l_manager_user_name VARCHAR2(100);
4188 l_note_id NUMBER;
4189 l_note_text ar_notes.text%type;
4190 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4191
4192 BEGIN
4193
4194 ----------------------------------------------------------
4195 g_debug_mesg := 'Entered INSERTESCAlATIONNOTES';
4196 IF PG_DEBUG in ('Y', 'C') THEN
4197 arp_standard.debug('InsertEscalationNotes: ' || g_debug_mesg);
4198 END IF;
4199 ----------------------------------------------------------
4200
4201 --
4202 -- RUN mode - normal process execution
4203 --
4204 IF (p_funcmode = 'RUN') then
4205
4206
4207 l_document_id := wf_engine.GetItemAttrNumber(
4208 p_item_type,
4209 p_item_key,
4210 'WORKFLOW_DOCUMENT_ID');
4211
4212 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4213 p_item_type,
4214 p_item_key,
4215 'CUSTOMER_TRX_ID');
4216
4217 l_manager_user_name
4218 := wf_engine.GetItemAttrText(
4219 p_item_type,
4220 p_item_key,
4221 'MANAGER_USER_NAME');
4222
4223 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4224
4225 fnd_message.set_name('AR', 'AR_WF_APPROVAL_ESCALATION');
4226 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4227 fnd_message.set_token('APPROVER', l_manager_user_name);
4228
4229 l_note_text := fnd_message.get;
4230
4231
4232 IF l_notes is NOT NULL then
4233 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4234 END IF;
4235 InsertTrxNotes(NULL,
4236 NULL,
4237 NULL,
4238 l_customer_trx_id,
4239 'MAINTAIN',
4240 l_note_text,
4241 l_note_id);
4242
4243
4244 p_result := 'COMPLETE:T';
4245 RETURN;
4246
4247
4248 END if; -- END of run mode
4249
4250 --
4251 -- CANCEL mode
4252 --
4253
4254 IF (p_funcmode = 'CANCEL') then
4255
4256 -- no result needed
4257 p_result := 'COMPLETE:';
4258 RETURN;
4259 END if;
4260
4261
4262 --
4263 -- Other execution modes
4264 --
4265 p_result := '';
4266 RETURN;
4267
4268 EXCEPTION
4269 WHEN OTHERS THEN
4270
4271 wf_core.context(
4272 pkg_name => 'AR_AME_CMWF_API',
4273 proc_name => 'INSERTESCALATIONNOTES',
4274 arg1 => p_item_type,
4275 arg2 => p_item_key,
4276 arg3 => p_funcmode,
4277 arg4 => to_char(p_actid),
4278 arg5 => g_debug_mesg);
4279
4280 RAISE;
4281
4282 END InsertEscalationNotes;
4283
4284
4285 PROCEDURE InsertRequestManualNotes (p_item_type IN VARCHAR2,
4286 p_item_key IN VARCHAR2,
4287 p_actid IN NUMBER,
4288 p_funcmode IN VARCHAR2,
4289 p_result OUT NOCOPY VARCHAR2) IS
4290
4291 l_document_id NUMBER;
4292 l_customer_trx_id NUMBER;
4293 l_receivable_role VARCHAR2(100);
4294 l_role_display_name wf_users.display_name%TYPE;
4295 l_note_id NUMBER;
4296 l_note_text ar_notes.text%type;
4297 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4298
4299 BEGIN
4300
4301 ----------------------------------------------------------
4302 g_debug_mesg := 'Entered INSERTREQUESTMANUALNOTES';
4303 IF PG_DEBUG in ('Y', 'C') THEN
4304 arp_standard.debug('InsertRequestManualNotes: ' || g_debug_mesg);
4305 END IF;
4306 ----------------------------------------------------------
4307
4308 --
4309 -- RUN mode - normal process execution
4310 --
4311 IF (p_funcmode = 'RUN') then
4312
4313 l_document_id := wf_engine.GetItemAttrNumber(
4314 p_item_type,
4315 p_item_key,
4316 'WORKFLOW_DOCUMENT_ID');
4317
4318 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4319 p_item_type,
4320 p_item_key,
4321 'CUSTOMER_TRX_ID');
4322
4323 l_receivable_role
4324 := wf_engine.GetItemAttrText(
4325 p_item_type,
4326 p_item_key,
4327 'RECEIVABLE_ROLE');
4328 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4329 SELECT display_name INTO l_role_display_name
4330 FROM wf_roles
4331 WHERE name = l_receivable_role;
4332
4333 fnd_message.set_name('AR', 'AR_WF_REQUEST_MANUAL');
4334 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4335 fnd_message.set_token('RECEIVABLE_ROLE',l_role_display_name);
4336
4337 l_note_text := fnd_message.get;
4338
4339 IF l_notes is NOT NULL then
4340 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4341 END IF;
4342
4343 InsertTrxNotes(NULL,
4344 NULL,
4345 NULL,
4346 l_customer_trx_id,
4347 'MAINTAIN',
4348 l_note_text,
4349 l_note_id);
4350
4351
4352 p_result := 'COMPLETE:T';
4353 RETURN;
4354
4355
4356 END if; -- END of run mode
4357
4358 --
4359 -- CANCEL mode
4360 --
4361
4362 IF (p_funcmode = 'CANCEL') then
4363
4364 -- no result needed
4365 p_result := 'COMPLETE:';
4366 RETURN;
4367 END if;
4368
4369
4370 --
4371 -- Other execution modes
4372 --
4373 p_result := '';
4374 RETURN;
4375
4376 EXCEPTION
4377 WHEN OTHERS THEN
4378
4379 wf_core.context(
4380 pkg_name => 'AR_AME_CMWF_API',
4381 proc_name => 'INSERTREQUESTMANUALNOTES',
4382 arg1 => p_item_type,
4383 arg2 => p_item_key,
4384 arg3 => p_funcmode,
4385 arg4 => to_char(p_actid),
4386 arg5 => g_debug_mesg);
4387
4388 RAISE;
4389
4390 END InsertRequestManualNotes;
4391
4392
4393 PROCEDURE InsertCompletedManualNotes(p_item_type IN VARCHAR2,
4394 p_item_key IN VARCHAR2,
4395 p_actid IN NUMBER,
4396 p_funcmode IN VARCHAR2,
4397 p_result OUT NOCOPY VARCHAR2) IS
4398
4399 l_document_id NUMBER;
4400 l_customer_trx_id NUMBER;
4401 l_receivable_role VARCHAR2(100);
4402 l_role_display_name wf_users.display_name%TYPE;
4403 l_note_id NUMBER;
4404 l_note_text ar_notes.text%type;
4405 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4406
4407 l_last_updated_by NUMBER;
4408 l_last_update_login NUMBER;
4409
4410 BEGIN
4411
4412 ----------------------------------------------------------
4413 g_debug_mesg := 'Entered INSERTCOMPLETEDMANUALNOTES';
4414 IF PG_DEBUG in ('Y', 'C') THEN
4415 arp_standard.debug('InsertCompletedManualNotes: ' || g_debug_mesg);
4416 END IF;
4417 ----------------------------------------------------------
4418
4419 -- Bug 2105483 : rather THEN calling arp_global at the start
4420 -- of the package, WHERE it can error out NOCOPY since org_id is not yet set,
4421 -- do the call right before it is needed
4422 -- arp_global.init_global;
4423 -- Bug 1908252
4424
4425 --
4426 -- RUN mode - normal process execution
4427 --
4428 IF (p_funcmode = 'RUN') then
4429
4430 -- restore_context(p_item_key);
4431 l_last_updated_by := ARP_GLOBAL.user_id;
4432 l_last_update_login := ARP_GLOBAL.last_update_login ;
4433
4434 l_document_id := wf_engine.GetItemAttrNumber(
4435 p_item_type,
4436 p_item_key,
4437 'WORKFLOW_DOCUMENT_ID');
4438
4439 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4440 p_item_type,
4441 p_item_key,
4442 'CUSTOMER_TRX_ID');
4443
4444 l_receivable_role
4445 := wf_engine.GetItemAttrText(
4446 p_item_type,
4447 p_item_key,
4448 'RECEIVABLE_ROLE');
4449 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4450
4451 SELECT display_name INTO l_role_display_name
4452 FROM wf_roles
4453 WHERE name = l_receivable_role;
4454
4455
4456
4457 fnd_message.set_name('AR', 'AR_WF_COMPLETED_MANUAL');
4458 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4459 fnd_message.set_token('APPROVER',l_role_display_name);
4460
4461 l_note_text := fnd_message.get;
4462
4463 IF l_notes is NOT NULL then
4464 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4465 END IF;
4466
4467 InsertTrxNotes(NULL,
4468 NULL,
4469 NULL,
4470 l_customer_trx_id,
4471 'MAINTAIN',
4472 l_note_text,
4473 l_note_id);
4474
4475 -- Bug 1908252 : update last_update* fields
4476 update ra_cm_requests
4477 set status = 'COMPLETE',
4478 approval_date = SYSDATE,
4479 last_updated_by = l_last_updated_by,
4480 last_update_date = SYSDATE,
4481 last_update_login = l_last_update_login
4482 WHERE request_id = p_item_key;
4483
4484 p_result := 'COMPLETE:T';
4485 RETURN;
4486
4487
4488 END if; -- END of run mode
4489
4490 --
4491 -- CANCEL mode
4492 --
4493
4494 IF (p_funcmode = 'CANCEL') then
4495
4496 -- no result needed
4497 p_result := 'COMPLETE:';
4498 RETURN;
4499 END if;
4500
4501
4502 --
4503 -- Other execution modes
4504 --
4505 p_result := '';
4506 RETURN;
4507
4508 EXCEPTION
4509 WHEN OTHERS THEN
4510
4511 wf_core.context(
4512 pkg_name => 'AR_AME_CMWF_API',
4513 proc_name => 'INSERTCOMPLETEDMANUALNOTES',
4514 arg1 => p_item_type,
4515 arg2 => p_item_key,
4516 arg3 => p_funcmode,
4517 arg4 => to_char(p_actid),
4518 arg5 => g_debug_mesg);
4519
4520 RAISE;
4521
4522 END InsertCompletedManualNotes;
4523
4524
4525 PROCEDURE InsertRequestApprovalNotes(p_item_type IN VARCHAR2,
4526 p_item_key IN VARCHAR2,
4527 p_actid IN NUMBER,
4528 p_funcmode IN VARCHAR2,
4529 p_result OUT NOCOPY VARCHAR2) IS
4530
4531 l_document_id NUMBER;
4532 l_customer_trx_id NUMBER;
4533 l_approver_display_name wf_users.display_name%TYPE;
4534 l_note_id NUMBER;
4535 l_note_text ar_notes.text%type;
4536 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4537 BEGIN
4538
4539 ----------------------------------------------------------
4540 g_debug_mesg := 'Entered INSERTREQUESTAPPROVALNOTES';
4541 IF PG_DEBUG in ('Y', 'C') THEN
4542 arp_standard.debug('InsertRequestApprovalNotes: ' || g_debug_mesg);
4543 END IF;
4544 ----------------------------------------------------------
4545 --
4546 -- RUN mode - normal process execution
4547 --
4548 IF (p_funcmode = 'RUN') then
4549
4550
4551 l_document_id := wf_engine.GetItemAttrNumber(
4552 p_item_type,
4553 p_item_key,
4554 'WORKFLOW_DOCUMENT_ID');
4555
4556 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4557 p_item_type,
4558 p_item_key,
4559 'CUSTOMER_TRX_ID');
4560
4561 l_approver_display_name
4562 := wf_engine.GetItemAttrText(
4563 p_item_type,
4564 p_item_key,
4565 'APPROVER_DISPLAY_NAME');
4566
4567 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4568
4569 fnd_message.set_name('AR', 'AR_WF_REQUEST_APPROVAL');
4570 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4571 fnd_message.set_token('APPROVER', l_approver_display_name);
4572 -- bug fix 1122477
4573
4574 l_note_text := fnd_message.get;
4575
4576 IF l_notes is NOT NULL then
4577 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4578 END IF;
4579
4580 InsertTrxNotes(NULL,
4581 NULL,
4582 NULL,
4583 l_customer_trx_id,
4584 'MAINTAIN',
4585 l_note_text,
4586 l_note_id);
4587
4588
4589 p_result := 'COMPLETE:T';
4590 RETURN;
4591
4592
4593 END if; -- END of run mode
4594
4595 --
4596 -- CANCEL mode
4597 --
4598
4599 IF (p_funcmode = 'CANCEL') then
4600
4601 -- no result needed
4602 p_result := 'COMPLETE:';
4603 RETURN;
4604 END if;
4605
4606
4607 --
4608 -- Other execution modes
4609 --
4610 p_result := '';
4611 RETURN;
4612
4613 EXCEPTION
4614 WHEN OTHERS THEN
4615
4616 wf_core.context(
4617 pkg_name => 'AR_AME_CMWF_API',
4618 proc_name => 'INSERTREQUESTAPPROVALNOTES',
4619 arg1 => p_item_type,
4620 arg2 => p_item_key,
4621 arg3 => p_funcmode,
4622 arg4 => to_char(p_actid),
4623 arg5 => g_debug_mesg);
4624
4625 RAISE;
4626
4627 END InsertRequestApprovalNotes;
4628
4629
4630 PROCEDURE InsertApprovedResponseNotes(p_item_type IN VARCHAR2,
4631 p_item_key IN VARCHAR2,
4632 p_actid IN NUMBER,
4633 p_funcmode IN VARCHAR2,
4634 p_result OUT NOCOPY VARCHAR2) IS
4635
4636 l_document_id NUMBER;
4637 l_customer_trx_id NUMBER;
4638 l_approver_display_name wf_users.display_name%TYPE;
4639 l_note_id NUMBER;
4640 l_note_text ar_notes.text%type;
4641 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4642
4643 l_approver_id NUMBER;
4644 l_transaction_type VARCHAR2(30);
4645
4646 BEGIN
4647
4648 ----------------------------------------------------------
4649 g_debug_mesg := 'Entered INSERTAPPROVEDRESPONSENOTES';
4650 IF PG_DEBUG in ('Y', 'C') THEN
4651 arp_standard.debug('InsertApprovedResponseNotes: ' || g_debug_mesg);
4652 END IF;
4653 ----------------------------------------------------------
4654
4655 -- arp_standard.enable_debug;
4656 -- arp_standard.enable_file_debug('/sqlcom/out/findv115',
4657 -- 'OB'|| p_item_key || '.log');
4658 -- Setorgcontext (p_item_key);
4659
4660 ---------------------------------------------------------------------
4661 g_debug_mesg := 'Insert Approved Response notes';
4662 ---------------------------------------------------------------------
4663
4664 --
4665 -- RUN mode - normal process execution
4666 --
4667 IF (p_funcmode = 'RUN') then
4668
4669
4670 l_document_id := wf_engine.GetItemAttrNumber(
4671 p_item_type,
4672 p_item_key,
4673 'WORKFLOW_DOCUMENT_ID');
4674
4675 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4676 p_item_type,
4677 p_item_key,
4678 'CUSTOMER_TRX_ID');
4679
4680 l_approver_display_name
4681 := wf_engine.GetItemAttrText(
4682 p_item_type,
4683 p_item_key,
4684 'APPROVER_DISPLAY_NAME');
4685 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4686
4687
4688 fnd_message.set_name('AR', 'AR_WF_APPROVED_RESPONSE');
4689 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4690 fnd_message.set_token('APPROVER', l_approver_display_name);
4691 -- bug fix 1122477
4692
4693 l_note_text := fnd_message.get;
4694
4695 IF l_notes is NOT NULL then
4696 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4697 END IF;
4698 InsertTrxNotes(NULL,
4699 NULL,
4700 NULL,
4701 l_customer_trx_id,
4702 'MAINTAIN',
4703 l_note_text,
4704 l_note_id);
4705
4706 /***********************************************************************/
4707 -- Written For AME Integration
4708 --
4709 -- This piece of code communicates to AME and lets it know about the
4710 -- reponse.
4711
4712 g_debug_mesg := 'About to call RecordResponseWithAME ';
4713
4714 RecordResponseWithAME (
4715 p_item_type => p_item_type,
4716 p_item_key => p_item_key,
4717 p_response => ame_util.approvedStatus);
4718
4719 --------------------------------------------------------------------------
4720 g_debug_mesg := 'InsertApprovedResponseNotes - return from RecordResponse';
4721
4722 IF PG_DEBUG in ('Y', 'C') THEN
4723 arp_standard.debug('InsertApprovedResponseNotes: ' || g_debug_mesg);
4724 END IF;
4725 ---------------------------------------------------------------------------
4726
4727 wf_engine.SetItemAttrText(
4728 itemtype => p_item_type,
4729 itemkey => p_item_key,
4730 aname => 'CURRENT_HUB',
4731 avalue => c_approvals_transaction_type);
4732
4733 /*************************************************************************/
4734
4735 p_result := 'COMPLETE:';
4736 RETURN;
4737
4738 END if; -- END of run mode
4739
4740 --
4741 -- CANCEL mode
4742 --
4743
4744 IF (p_funcmode = 'CANCEL') then
4745
4746 -- no result needed
4747 p_result := 'COMPLETE:';
4748 RETURN;
4749 END if;
4750
4751 --
4752 -- Other execution modes
4753 --
4754
4755 p_result := 'COMPLETE:';
4756 RETURN;
4757
4758 EXCEPTION
4759 WHEN OTHERS THEN
4760
4761 wf_core.context(
4762 pkg_name => 'AR_AME_CMWF_API',
4763 proc_name => 'INSERTAPPROVEDRESPONSENOTES',
4764 arg1 => p_item_type,
4765 arg2 => p_item_key,
4766 arg3 => p_funcmode,
4767 arg4 => to_char(p_actid),
4768 arg5 => g_debug_mesg);
4769
4770 RAISE;
4771
4772 END InsertApprovedResponseNotes;
4773
4774
4775 PROCEDURE InsertRejectedResponseNotes(p_item_type IN VARCHAR2,
4776 p_item_key IN VARCHAR2,
4777 p_actid IN NUMBER,
4778 p_funcmode IN VARCHAR2,
4779 p_result OUT NOCOPY VARCHAR2) IS
4780
4781 l_document_id NUMBER;
4782 l_customer_trx_id NUMBER;
4783 l_approver_display_name wf_users.display_name%TYPE;
4784 l_note_id NUMBER;
4785 l_note_text ar_notes.text%type;
4786 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4787
4788 --Bug 1908252
4789 l_last_updated_by NUMBER;
4790 l_last_update_login NUMBER;
4791 l_approver_id NUMBER;
4792 l_transaction_type VARCHAR2(30);
4793
4794 BEGIN
4795
4796 ----------------------------------------------------------
4797 g_debug_mesg := 'Entered INSERTREJECTEDRESPONSENOTES';
4798 IF PG_DEBUG in ('Y', 'C') THEN
4799 arp_standard.debug('InsertRejectedResponseNotes: ' || g_debug_mesg);
4800 END IF;
4801 ----------------------------------------------------------
4802
4803 -- Bug 2105483 : rather then calling arp_global at the start
4804 -- of the package, WHERE it can error out NOCOPY since org_id is not yet set,
4805 -- do the call right before it is needed
4806
4807 -- arp_global.init_global;
4808
4809 -- Bug 1908252
4810
4811 l_last_updated_by := ARP_GLOBAL.user_id;
4812 l_last_update_login := ARP_GLOBAL.last_update_login ;
4813
4814 ---------------------------------------------------------------------
4815 g_debug_mesg := 'Insert Rejected Response notes';
4816 ---------------------------------------------------------------------
4817
4818 --
4819 -- RUN mode - normal process execution
4820 --
4821 IF (p_funcmode = 'RUN') then
4822
4823 l_document_id := wf_engine.GetItemAttrNumber(
4824 p_item_type,
4825 p_item_key,
4826 'WORKFLOW_DOCUMENT_ID');
4827
4828 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4829 p_item_type,
4830 p_item_key,
4831 'CUSTOMER_TRX_ID');
4832
4833 l_approver_display_name
4834 := wf_engine.GetItemAttrText(
4835 p_item_type,
4836 p_item_key,
4837 'APPROVER_DISPLAY_NAME');
4838
4839 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4840
4841 fnd_message.set_name('AR', 'AR_WF_REJECTED_RESPONSE');
4842 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4843 fnd_message.set_token('APPROVER', l_approver_display_name);
4844 -- bug fix 1122477
4845
4846 l_note_text := fnd_message.get;
4847
4848 IF l_notes is NOT NULL then
4849 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4850 END IF;
4851
4852 InsertTrxNotes(NULL,
4853 NULL,
4854 NULL,
4855 l_customer_trx_id,
4856 'MAINTAIN',
4857 l_note_text,
4858 l_note_id);
4859
4860 -- Bug 1908252 : update last_update* fields
4861
4862 UPDATE ra_cm_requests
4863 SET status = 'NOT_APPROVED',
4864 last_updated_by = l_last_updated_by,
4865 last_update_date = SYSDATE,
4866 last_update_login = l_last_update_login
4867 WHERE request_id = p_item_key;
4868
4869 /*COMMIT;*/
4870
4871
4872 /***********************************************************************/
4873 -- Written For AME Integration
4874 --
4875 -- This piece of code communicates to AME and lets it know about the
4876 -- reponse.
4877
4878 RecordResponseWithAME (
4879 p_item_type => p_item_type,
4880 p_item_key => p_item_key,
4881 p_response => ame_util.rejectStatus);
4882
4883
4884 -------------------------------------------------------------------------
4885 g_debug_mesg := 'InsertRejectedResponseNotes -return from updt Approval';
4886 IF PG_DEBUG in ('Y', 'C') THEN
4887 arp_standard.debug('InsertRejectedResponseNotes: ' || g_debug_mesg);
4888 END IF;
4889 -------------------------------------------------------------------------
4890
4891 wf_engine.SetItemAttrText(
4892 itemtype => p_item_type,
4893 itemkey => p_item_key,
4894 aname => 'CURRENT_HUB',
4895 avalue => c_approvals_transaction_type);
4896
4897 /*************************************************************************/
4898
4899 p_result := 'COMPLETE:T';
4900 RETURN;
4901
4902
4903 END if; -- END of run mode
4904
4905 --
4906 -- CANCEL mode
4907 --
4908
4909 IF (p_funcmode = 'CANCEL') then
4910
4911 -- no result needed
4912 p_result := 'COMPLETE:';
4913 RETURN;
4914 END if;
4915
4916
4917 --
4918 -- Other execution modes
4919 --
4920 p_result := '';
4921 RETURN;
4922
4923 EXCEPTION
4924 WHEN OTHERS THEN
4925
4926 wf_core.context(
4927 pkg_name => 'AR_AME_CMWF_API',
4928 proc_name => 'INSERTREJECTEDRESPONSENOTES',
4929 arg1 => p_item_type,
4930 arg2 => p_item_key,
4931 arg3 => p_funcmode,
4932 arg4 => to_char(p_actid),
4933 arg5 => g_debug_mesg);
4934
4935 RAISE;
4936
4937
4938 END InsertRejectedResponseNotes;
4939
4940
4941 PROCEDURE InsertSuccessfulAPINotes(p_item_type IN VARCHAR2,
4942 p_item_key IN VARCHAR2,
4943 p_actid IN NUMBER,
4944 p_funcmode IN VARCHAR2,
4945 p_result OUT NOCOPY VARCHAR2) IS
4946
4947 l_document_id NUMBER;
4948 l_credit_memo_number VARCHAR2(20);
4949 l_customer_trx_id NUMBER;
4950 l_note_id NUMBER;
4951 l_note_text ar_notes.text%type;
4952 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4953
4954 BEGIN
4955
4956 ----------------------------------------------------------
4957 g_debug_mesg := 'Entered INSERTSUCCESFULAPINOTES';
4958 IF PG_DEBUG in ('Y', 'C') THEN
4959 arp_standard.debug('InsertSuccessfulAPINotes: ' || g_debug_mesg);
4960 END IF;
4961 ----------------------------------------------------------
4962 --
4963 -- RUN mode - normal process execution
4964 --
4965 IF (p_funcmode = 'RUN') then
4966
4967
4968 l_document_id := wf_engine.GetItemAttrNumber(
4969 p_item_type,
4970 p_item_key,
4971 'WORKFLOW_DOCUMENT_ID');
4972
4973 l_credit_memo_number := wf_engine.GetItemAttrText(
4974 p_item_type,
4975 p_item_key,
4976 'CREDIT_MEMO_NUMBER');
4977
4978 l_customer_trx_id := wf_engine.GetItemAttrNumber(
4979 p_item_type,
4980 p_item_key,
4981 'CUSTOMER_TRX_ID');
4982
4983 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4984 /* Get trx number for CM and the insert into note text */
4985
4986
4987 fnd_message.set_name('AR', 'AR_WF_COMPLETED_SUCCESSFUL');
4988 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4989 fnd_message.set_token('TRXNUMBER', l_credit_memo_number);
4990
4991 l_note_text := fnd_message.get;
4992
4993
4994 IF l_notes is NOT NULL then
4995 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4996 END IF;
4997 InsertTrxNotes(NULL,
4998 NULL,
4999 NULL,
5000 l_customer_trx_id,
5001 'MAINTAIN',
5002 l_note_text,
5003 l_note_id);
5004
5005
5006 p_result := 'COMPLETE:T';
5007 RETURN;
5008
5009
5010 END if; -- END of run mode
5011
5012 --
5013 -- CANCEL mode
5014 --
5015
5016 IF (p_funcmode = 'CANCEL') then
5017
5018 -- no result needed
5019 p_result := 'COMPLETE:';
5020 RETURN;
5021 END if;
5022
5023
5024 --
5025 -- Other execution modes
5026 --
5027 p_result := '';
5028 RETURN;
5029
5030 EXCEPTION
5031 WHEN OTHERS THEN
5032
5033 wf_core.context(
5034 pkg_name => 'AR_AME_CMWF_API',
5035 proc_name => 'INSERTSUCCESSFULAPINOTES',
5036 arg1 => p_item_type,
5037 arg2 => p_item_key,
5038 arg3 => p_funcmode,
5039 arg4 => to_char(p_actid),
5040 arg5 => g_debug_mesg);
5041
5042 RAISE;
5043
5044 END InsertSuccessfulAPINotes;
5045
5046
5047 PROCEDURE InsertNotes(p_item_type IN VARCHAR2,
5048 p_item_key IN VARCHAR2,
5049 p_actid IN NUMBER,
5050 p_funcmode IN VARCHAR2,
5051 p_result OUT NOCOPY VARCHAR2) IS
5052
5053
5054 l_customer_id NUMBER;
5055 l_collector_id NUMBER;
5056 l_customer_trx_id NUMBER;
5057 l_bill_to_site_use_id NUMBER;
5058 l_customer_call_id NUMBER;
5059 l_customer_call_topic_id NUMBER;
5060 l_action_id NUMBER;
5061 l_note_id NUMBER;
5062 l_reason_code VARCHAR2(45);
5063 l_currency_code VARCHAR2(15);
5064 l_entered_amount_display NUMBER;
5065 l_result_flag VARCHAR2(1);
5066
5067 BEGIN
5068
5069 ----------------------------------------------------------
5070 g_debug_mesg := 'Entered INSERTNOTES';
5071 IF PG_DEBUG in ('Y', 'C') THEN
5072 arp_standard.debug('InsertNotes: ' || g_debug_mesg);
5073 END IF;
5074 ----------------------------------------------------------
5075
5076 --
5077 -- RUN mode - normal process execution
5078 --
5079 IF (p_funcmode = 'RUN') then
5080
5081 l_reason_code := wf_engine.GetItemAttrText(
5082 p_item_type,
5083 p_item_key,
5084 'REASON');
5085
5086 l_currency_code := wf_engine.GetItemAttrText(
5087 p_item_type,
5088 p_item_key,
5089 'CURRENCY_CODE');
5090
5091 l_entered_amount_display
5092 := wf_engine.GetItemAttrNumber(
5093 p_item_type,
5094 p_item_key,
5095 'ENTERED_AMOUNT_DISPLAY');
5096
5097 l_customer_id := wf_engine.GetItemAttrNumber(
5098 p_item_type,
5099 p_item_key,
5100 'CUSTOMER_ID');
5101
5102 l_collector_id := wf_engine.GetItemAttrNumber(
5103 p_item_type,
5104 p_item_key,
5105 'COLLECTOR_ID');
5106
5107
5108 l_customer_trx_id := wf_engine.GetItemAttrNumber(
5109 p_item_type,
5110 p_item_key,
5111 'CUSTOMER_TRX_ID');
5112
5113 l_bill_to_site_use_id := wf_engine.GetItemAttrNumber(
5114 p_item_type,
5115 p_item_key,
5116 'BILL_TO_SITE_USE_ID');
5117
5118
5119
5120 InsertTrxNotes(NULL,
5121 NULL,
5122 NULL,
5123 l_customer_trx_id,
5124 'MAINTAIN',
5125 'Credit Memo request was approved by receivable role.',
5126 l_note_id);
5127
5128
5129 p_result := 'COMPLETE:T';
5130 RETURN;
5131
5132
5133 END if; -- END of run mode
5134
5135 --
5136 -- CANCEL mode
5137 --
5138
5139 IF (p_funcmode = 'CANCEL') then
5140
5141 -- no result needed
5142 p_result := 'COMPLETE:';
5143 RETURN;
5144 END if;
5145
5146
5147 --
5148 -- Other execution modes
5149 --
5150 p_result := '';
5151 RETURN;
5152
5153 EXCEPTION
5154 WHEN OTHERS THEN
5155
5156 wf_core.context(
5157 pkg_name => 'AR_AME_CMWF_API',
5158 proc_name => 'INSERTNOTES',
5159 arg1 => p_item_type,
5160 arg2 => p_item_key,
5161 arg3 => p_funcmode,
5162 arg4 => to_char(p_actid),
5163 arg5 => g_debug_mesg);
5164
5165 RAISE;
5166
5167 END InsertNotes;
5168
5169
5170 PROCEDURE InsertTrxNotes(x_customer_call_id IN NUMBER,
5171 x_customer_call_topic_id IN NUMBER,
5172 x_action_id IN NUMBER,
5173 x_customer_trx_id IN NUMBER,
5174 x_note_type IN VARCHAR2,
5175 x_text IN VARCHAR2,
5176 x_note_id OUT NOCOPY NUMBER) IS
5177
5178 l_last_updated_by NUMBER;
5179 l_last_update_date date;
5180 l_last_update_login NUMBER;
5181 l_creation_date date;
5182 l_created_by NUMBER;
5183
5184 BEGIN
5185
5186 ----------------------------------------------------------
5187 g_debug_mesg := 'Entered INSERTTRXNOTES';
5188 IF PG_DEBUG in ('Y', 'C') THEN
5189 arp_standard.debug('InsertTrxNotes: ' || g_debug_mesg);
5190 END IF;
5191 ----------------------------------------------------------
5192
5193 -- Bug 2105483 : rather then calling arp_global at the start
5194 -- of the package, WHERE it can error out NOCOPY since org_id is not yet set,
5195 -- do the call right before it is needed
5196
5197
5198 -- Bug 1690118 : replace FND_GLOBAL with ARP_GLOBAL
5199
5200 l_created_by := ARP_GLOBAL.USER_ID;
5201 l_creation_date := sysdate;
5202 l_last_update_login := ARP_GLOBAL.last_update_login;
5203 l_last_update_date := sysdate;
5204 l_last_updated_by := ARP_GLOBAL.USER_ID;
5205
5206 g_debug_mesg := 'WHO columns retrieved';
5207
5208 arp_notes_pkg.insert_cover(
5209 p_note_type => x_note_type,
5210 p_text => x_text,
5211 p_customer_call_id => NULL,
5212 p_customer_call_topic_id => NULL,
5213 p_call_action_id => NULL,
5214 p_customer_trx_id => x_customer_trx_id,
5215 p_note_id => x_note_id,
5216 p_last_updated_by => l_last_updated_by,
5217 p_last_update_date => l_last_update_date,
5218 p_last_update_login => l_last_update_login,
5219 p_created_by => l_created_by,
5220 p_creation_date => l_creation_date);
5221
5222 ----------------------------------------------------------
5223 g_debug_mesg := 'INSERTTRXNOTES - notes inserted';
5224 IF PG_DEBUG in ('Y', 'C') THEN
5225 arp_standard.debug('InsertTrxNotes: ' || g_debug_mesg);
5226 END IF;
5227 ----------------------------------------------------------
5228
5229 EXCEPTION
5230 WHEN OTHERS THEN
5231 x_note_id := -1;
5232 wf_core.context(
5233 pkg_name => 'AR_AME_CMWF_API',
5234 proc_name => 'INSERTTRXNOTES',
5235 arg1 => c_item_type,
5236 arg2 => NULL,
5237 arg3 => NULL,
5238 arg4 => NULL,
5239 arg5 => g_debug_mesg);
5240
5241 RAISE;
5242
5243 END InsertTrxNotes;
5244
5245
5246 PROCEDURE CallTrxApi(p_item_type IN VARCHAR2,
5247 p_item_key IN VARCHAR2,
5248 p_actid IN NUMBER,
5249 p_funcmode IN VARCHAR2,
5250 p_result OUT NOCOPY VARCHAR2) IS
5251
5252
5253 l_customer_trx_id NUMBER;
5254 l_amount NUMBER;
5255 l_request_id NUMBER;
5256 l_error_tab arp_trx_validate.Message_Tbl_Type;
5257 l_batch_source_name VARCHAR2(50);
5258 l_credit_method_rules VARCHAR2(65);
5259 l_credit_method_installments VARCHAR2(65);
5260 l_cm_creation_error VARCHAR2(250);
5261 l_credit_memo_number VARCHAR2(20);
5262 l_credit_memo_id NUMBER;
5263 CRLF VARCHAR2(1);
5264 l_status VARCHAR2(255);
5265
5266 -- bug 1908252
5267 l_last_updated_by NUMBER;
5268 l_last_update_login NUMBER;
5269
5270 BEGIN
5271
5272 ----------------------------------------------------------
5273 g_debug_mesg := 'Entered CALLTRXAPI';
5274 IF PG_DEBUG in ('Y', 'C') THEN
5275 arp_standard.debug('CallTrxApi: ' || g_debug_mesg);
5276 END IF;
5277 ----------------------------------------------------------
5278
5279 -- Bug 2105483 : rather then calling arp_global at the start
5280 -- of the package, WHERE it can error out NOCOPY since org_id is not yet set,
5281 -- do the call right before it is needed
5282
5283 --
5284 -- RUN mode - normal process execution
5285 --
5286 IF (p_funcmode = 'RUN') then
5287
5288 -- restore_context(p_item_key);
5289
5290 crlf := arp_global.CRLF;
5291
5292 -- Bug 1908252
5293 l_last_updated_by := ARP_GLOBAL.user_id;
5294 l_last_update_login := ARP_GLOBAL.last_update_login ;
5295
5296 -- call transaction API here
5297
5298 l_customer_trx_id := wf_engine.GetItemAttrNumber(
5299 p_item_type,
5300 p_item_key,
5301 'CUSTOMER_TRX_ID');
5302
5303 l_amount := wf_engine.GetItemAttrNumber(
5304 p_item_type,
5305 p_item_key,
5306 'ORIGINAL_TOTAL');
5307
5308 l_request_id := wf_engine.GetItemAttrNumber(
5309 p_item_type,
5310 p_item_key,
5311 'WORKFLOW_DOCUMENT_ID');
5312
5313 l_batch_source_name := wf_engine.GetItemAttrText(
5314 p_item_type,
5315 p_item_key,
5316 'BATCH_SOURCE_NAME');
5317
5318
5319 l_credit_method_installments := wf_engine.GetItemAttrText(
5320 p_item_type,
5321 p_item_key,
5322 'CREDIT_INSTALLMENT_RULE');
5323
5324 l_credit_method_rules := wf_engine.GetItemAttrText(
5325 p_item_type,
5326 p_item_key,
5327 'CREDIT_ACCOUNTING_RULE');
5328
5329 l_cm_creation_error := NULL;
5330
5331 /* bug 3155533 : do not raise an error if user does not set-up batch source name
5332 in workflow definition
5333
5334 IF l_batch_source_name IS NULL THEN
5335
5336 fnd_message.set_name('AR', 'AR_WF_NO_BATCH');
5337 l_cm_creation_error := fnd_message.get;
5338
5339 wf_engine.SetItemAttrText(p_item_type,
5340 p_item_key,
5341 'CM_CREATION_ERROR',
5342 l_cm_creation_error);
5343
5344 p_result := 'COMPLETE:F';
5345 RETURN;
5346
5347 END IF;
5348 */
5349 IF (l_credit_method_installments = 'N') THEN
5350 l_credit_method_installments := NULL;
5351 END if;
5352
5353 IF (l_credit_method_rules = 'N') THEN
5354 l_credit_method_rules := NULL;
5355 END if;
5356
5357 g_debug_mesg := 'Before calling arw_cmreq_cover.ar_autocreate_cm';
5358
5359
5360 -- BUG 2290738 : added a new OUT NOCOPY parameter p_status
5361 arw_cmreq_cover.ar_autocreate_cm(
5362 p_request_id => l_request_id,
5363 p_batch_source_name => l_batch_source_name,
5364 p_credit_method_rules => l_credit_method_rules,
5365 p_credit_method_installments => l_credit_method_installments,
5366 p_error_tab => l_error_tab,
5367 p_status => l_status);
5368
5369 g_debug_mesg := 'After calling arw_cmreq_cover.ar_autocreate_cm';
5370
5371 l_cm_creation_error := NULL;
5372
5373
5374 BEGIN
5375
5376 SELECT cm_customer_trx_id INTO l_credit_memo_id
5377 FROM ra_cm_requests
5378 WHERE request_id = l_request_id;
5379
5380 EXCEPTION
5381 WHEN OTHERS THEN
5382 p_result := 'COMPLETE:F';
5383 l_cm_creation_error := 'Could not find the request';
5384 wf_engine.SetItemAttrText(p_item_type,
5385 p_item_key,
5386 'CM_CREATION_ERROR',
5387 l_cm_creation_error);
5388 RETURN;
5389 END;
5390
5391 g_debug_mesg := 'Credit Memo ID: ' || l_credit_memo_id;
5392
5393 -- IF l_error_tab.count = 0 THEN
5394 IF (l_credit_memo_id is NOT NULL) THEN
5395 p_result := 'COMPLETE:T';
5396
5397 -- Bug 1908252 : update last_update* fields
5398 UPDATE ra_cm_requests
5399 SET status='COMPLETE',
5400 approval_date = SYSDATE,
5401 last_updated_by = l_last_updated_by,
5402 last_update_date = SYSDATE,
5403 last_update_login = l_last_update_login
5404 WHERE request_id = p_item_key;
5405
5406 /*commit;*/
5407
5408 BEGIN
5409
5410 SELECT trx_number INTO l_credit_memo_number
5411 FROM ra_customer_trx
5412 WHERE customer_trx_id = l_credit_memo_id;
5413
5414 wf_engine.SetItemAttrText(p_item_type,
5415 p_item_key,
5416 'CREDIT_MEMO_NUMBER',
5417 l_credit_memo_number);
5418
5419 EXCEPTION
5420 WHEN OTHERS THEN
5421 p_result := 'COMPLETE:F';
5422 l_cm_creation_error := 'Could not find the credit memo';
5423 wf_engine.SetItemAttrText(p_item_type,
5424 p_item_key,
5425 'CM_CREATION_ERROR',
5426 l_cm_creation_error);
5427 RETURN;
5428 END;
5429
5430 ELSE
5431
5432 g_debug_mesg := 'Credit Memo ID Is NULL';
5433
5434 FOR i IN 1..l_error_tab.COUNT LOOP
5435 l_cm_creation_error := l_cm_creation_error ||
5436 l_error_tab(i).translated_message || CRLF;
5437 END LOOP;
5438
5439 wf_engine.SetItemAttrText(p_item_type,
5440 p_item_key,
5441 'CM_CREATION_ERROR',
5442 l_cm_creation_error);
5443
5444 -- Bug 1908252 : update last_update* fields
5445
5446 g_debug_mesg := 'last Updated By: '
5447 || l_last_updated_by || ' '
5448 || l_last_update_login;
5449
5450
5451 UPDATE ra_cm_requests
5452 SET status='APPROVED_PEND_COMP',
5453 approval_date = SYSDATE,
5454 last_updated_by = l_last_updated_by,
5455 last_update_date = SYSDATE,
5456 last_update_login = l_last_update_login
5457 WHERE request_id = p_item_key;
5458
5459 g_debug_mesg := 'After Update';
5460
5461 p_result := 'COMPLETE:F';
5462
5463 END IF;
5464
5465 g_debug_mesg := 'Before Return';
5466
5467 RETURN;
5468
5469 END IF; -- END of run mode
5470
5471 --
5472 -- CANCEL mode
5473 --
5474 -- This is an event point is called with the effect of the activity must
5475 -- be undone, for example when a process is reset to an earlier point
5476 -- due to a loop back.
5477 --
5478 IF (p_funcmode = 'CANCEL') THEN
5479
5480 -- no result needed
5481 p_result := 'COMPLETE:';
5482 RETURN;
5483
5484 END IF;
5485
5486
5487 --
5488 -- Other execution modes may be created in the future. Your
5489 -- activity will indicate that it does not implement a mode
5490 -- by returning NULL
5491 --
5492 p_result := '';
5493 RETURN;
5494
5495 EXCEPTION
5496 WHEN OTHERS THEN
5497
5498 wf_core.context(
5499 pkg_name => 'AR_AME_CMWF_API',
5500 proc_name => 'CALLTRXAPI',
5501 arg1 => p_item_type,
5502 arg2 => p_item_key,
5503 arg3 => p_funcmode,
5504 arg4 => to_char(p_actid),
5505 arg5 => g_debug_mesg);
5506
5507 RAISE;
5508
5509 END CallTrxApi;
5510
5511
5512 PROCEDURE CheckCreditMethods(p_item_type IN VARCHAR2,
5513 p_item_key IN VARCHAR2,
5514 p_actid IN NUMBER,
5515 p_funcmode IN VARCHAR2,
5516 p_result OUT NOCOPY VARCHAR2) IS
5517
5518 l_customer_trx_id NUMBER;
5519 l_credit_installment_rule VARCHAR2(65);
5520 l_credit_accounting_rule VARCHAR2(65);
5521 l_invalid_rule_value VARCHAR2(80);
5522 l_invalid_rule_mesg VARCHAR2(2000);
5523 l_count NUMBER;
5524 l_invoicing_rule_id NUMBER;
5525
5526 CURSOR c (p_cust_trx_id NUMBER) IS
5527 SELECT COUNT(*)
5528 FROM ra_terms_lines
5529 WHERE term_id =
5530 (SELECT term_id
5531 FROM ra_customer_trx
5532 WHERE customer_trx_id = p_cust_trx_id);
5533
5534 CURSOR c2 (p_cust_trx_id NUMBER) IS
5535 SELECT invoicing_rule_id
5536 FROM ra_customer_trx
5537 WHERE customer_trx_id = p_cust_trx_id;
5538
5539 BEGIN
5540
5541 ----------------------------------------------------------
5542 g_debug_mesg := 'Entered CHECKCREDITMETHODS';
5543 IF PG_DEBUG in ('Y', 'C') THEN
5544 arp_standard.debug('CheckCreditMethods: ' || g_debug_mesg);
5545 END IF;
5546 ----------------------------------------------------------
5547
5548 -- arp_standard.enable_debug;
5549 -- arp_standard.enable_file_debug('/sqlcom/out/findv115',
5550 -- 'OB'||userenv('SESSIONID')|| '.log');
5551 -- fnd_global.apps_initialize(1318,50559,222);
5552 -- mo_global.init;
5553 -- Setorgcontext (p_item_key);
5554
5555 --
5556 -- RUN mode - normal process execution
5557 --
5558
5559 IF (p_funcmode = 'RUN') then
5560
5561 -- restore_context(p_item_key);
5562 -- Setorgcontext (p_item_key);
5563
5564 ------------------------------------------------------------
5565 g_debug_mesg := 'Get the user value of rules';
5566 ------------------------------------------------------------
5567
5568 l_customer_trx_id := wf_engine.GetItemAttrNumber(
5569 p_item_type,
5570 p_item_key,
5571 'CUSTOMER_TRX_ID');
5572
5573 l_credit_installment_rule := wf_engine.GetItemAttrText(
5574 p_item_type,
5575 p_item_key,
5576 'CREDIT_INSTALLMENT_RULE');
5577
5578 l_credit_accounting_rule := wf_engine.GetItemAttrText(
5579 p_item_type,
5580 p_item_key,
5581 'CREDIT_ACCOUNTING_RULE');
5582
5583 l_invalid_rule_value := wf_engine.GetItemAttrText(
5584 p_item_type,
5585 p_item_key,
5586 'INVALID_RULE_VALUE');
5587
5588 l_invalid_rule_mesg := wf_engine.GetItemAttrText(
5589 p_item_type,
5590 p_item_key,
5591 'INVALID_RULE_MESG');
5592
5593
5594 OPEN c (l_customer_trx_id);
5595 FETCH c INTO l_count;
5596 CLOSE c;
5597
5598 g_debug_mesg := 'After Cursor: ' || l_count;
5599
5600 -- the l_count will always be >= 1, and the credit installment_rule is
5601 -- required for count > 1.
5602
5603 IF l_count > 1 then
5604
5605 IF l_credit_installment_rule not in ('LIFO', 'FIFO', 'PRORATE') then
5606 -- invalid credit method
5607 wf_engine.SetItemAttrText(p_item_type,
5608 p_item_key,
5609 'INVALID_RULE_MESG',
5610 l_invalid_rule_value);
5611 p_result := 'COMPLETE:F';
5612 RETURN;
5613 END if;
5614 END if;
5615
5616
5617 OPEN c2 (l_customer_trx_id);
5618 FETCH c2 INTO l_invoicing_rule_id;
5619 CLOSE c2;
5620
5621 IF l_invoicing_rule_id is not NULL then
5622
5623 IF l_credit_accounting_rule not in ('LIFO', 'PRORATE','UNIT') THEN
5624 -- invalid credit method
5625 wf_engine.SetItemAttrText(p_item_type,
5626 p_item_key,
5627 'INVALID_RULE_MESG',
5628 l_invalid_rule_value);
5629 p_result := 'COMPLETE:F';
5630 RETURN;
5631 END if;
5632 END if;
5633
5634 -- the credit methods are valid
5635 IF l_invalid_rule_mesg is NOT NULL THEN
5636
5637 l_invalid_rule_mesg := NULL;
5638 wf_engine.SetItemAttrText(p_item_type,
5639 p_item_key,
5640 'INVALID_RULE_MESG',
5641 l_invalid_rule_mesg);
5642 END if;
5643
5644 g_debug_mesg := 'Before Return';
5645
5646 p_result := 'COMPLETE:T';
5647
5648 RETURN;
5649
5650 END IF; -- END of run mode
5651
5652 --
5653 -- CANCEL mode
5654 --
5655
5656 IF (p_funcmode = 'CANCEL') then
5657
5658 -- no result needed
5659 g_debug_mesg := 'Cancel Mode';
5660 p_result := 'COMPLETE:';
5661 RETURN;
5662 END if;
5663
5664 --
5665 -- Other execution modes
5666 --
5667
5668 g_debug_mesg := 'Should not come here';
5669 p_result := '';
5670 RETURN;
5671
5672 EXCEPTION
5673 WHEN OTHERS THEN
5674
5675 wf_core.context(
5676 pkg_name => 'AR_AME_CMWF_API',
5677 proc_name => 'CHECKCREDITMETHODS',
5678 arg1 => p_item_type,
5679 arg2 => p_item_key,
5680 arg3 => p_funcmode,
5681 arg4 => to_char(p_actid),
5682 arg5 => g_debug_mesg);
5683
5684 RAISE;
5685
5686 END CheckCreditMethods;
5687
5688
5689 -- This procedure is called to find the primary salesrep associated with
5690 -- the invoice. It loooks ra_customer_trx to find the information.
5691 -- Once we find the salesrep, we need to determine if salesperson is the one
5692 -- initiating the credit memo request. If so then he need not be notified
5693 -- again. Otherwise, we will notify him/her to make him/her aware of this
5694 -- credit memo.
5695 --
5696 PROCEDURE find_primary_salesrep (
5697 p_item_type IN VARCHAR2,
5698 p_item_key IN VARCHAR2,
5699 p_actid IN NUMBER,
5700 p_funcmode IN VARCHAR2,
5701 p_result IN OUT NOCOPY VARCHAR2) IS
5702
5703
5704 l_requestor_user_name wf_item_attribute_values.text_value%TYPE;
5705 l_customer_trx_id wf_item_attribute_values.number_value%TYPE;
5706 l_employee_id ra_salesreps_all.person_id%TYPE;
5707 l_user_name wf_users.name%TYPE;
5708 l_display_name wf_users.display_name%TYPE;
5709
5710 CURSOR c (p_id IN NUMBER) IS
5711 SELECT rsa.person_id
5712 FROM ra_customer_trx_all rcta, ra_salesreps_all rsa
5713 WHERE rcta.primary_salesrep_id = rsa.salesrep_id
5714 AND rcta.customer_trx_id = p_id;
5715
5716 BEGIN
5717
5718 ----------------------------------------------------------
5719 g_debug_mesg := 'Entered FIND_PRIMARY_SALESREP';
5720 IF PG_DEBUG in ('Y', 'C') THEN
5721 arp_standard.debug('find_primary_salesrep: ' || g_debug_mesg);
5722 END IF;
5723 ----------------------------------------------------------
5724
5725 IF (p_funcmode = 'RUN') THEN
5726
5727 -- restore_context(p_item_key);
5728
5729 l_customer_trx_id := wf_engine.GetItemAttrNumber(
5730 itemtype => p_item_type,
5731 itemkey => p_item_key,
5732 aname => 'CUSTOMER_TRX_ID');
5733
5734 OPEN c (l_customer_trx_id);
5735 FETCH c INTO l_employee_id;
5736 CLOSE c;
5737
5738 wf_directory.getusername(
5739 p_orig_system => 'PER',
5740 p_orig_system_id => l_employee_id,
5741 p_name => l_user_name,
5742 p_display_name => l_display_name);
5743
5744 IF (l_user_name IS NULL) THEN
5745 p_result := 'COMPLETE:N';
5746 RETURN;
5747 END IF;
5748
5749 wf_engine.SetItemAttrText(
5750 itemtype =>p_item_type,
5751 itemkey => p_item_key,
5752 aname => 'SALESREP_USER_NAME',
5753 avalue => l_user_name);
5754
5755 l_requestor_user_name := wf_engine.GetItemAttrText(
5756 itemtype => p_item_type,
5757 itemkey => p_item_key,
5758 aname => 'REQUESTOR_USER_NAME');
5759
5760 IF (l_user_name = l_requestor_user_name) THEN
5761 p_result := 'COMPLETE:N';
5762 ELSE
5763 p_result := 'COMPLETE:Y';
5764 END IF;
5765
5766 RETURN;
5767
5768 END IF;
5769
5770 EXCEPTION
5771 WHEN OTHERS THEN
5772
5773 wf_core.context(
5774 pkg_name => 'AR_AME_CMWF_API',
5775 proc_name => 'FIND_PRIMARY_SALESREP',
5776 arg1 => p_item_type,
5777 arg2 => p_item_key,
5778 arg3 => p_funcmode,
5779 arg4 => to_char(p_actid),
5780 arg5 => g_debug_mesg);
5781
5782 RAISE;
5783
5784 END find_primary_salesrep;
5785
5786
5787 -- The following subroutine checks to see if collector forgot to put in a
5788 -- first approver for the HR hierarchy flow or put in a first primary for
5789 -- Limts flow. If either of this happens it returns the approproate errors.
5790 -- Otherwise it returns no error.
5791
5792 PROCEDURE check_first_approver (
5793 p_item_type IN VARCHAR2,
5794 p_item_key IN VARCHAR2,
5795 p_actid IN NUMBER,
5796 p_funcmode IN VARCHAR2,
5797 p_result IN OUT NOCOPY VARCHAR2) IS
5798
5799 l_approval_path wf_item_attribute_values.text_value%TYPE;
5800 l_approver_user_name wf_item_attribute_values.text_value%TYPE;
5801
5802 BEGIN
5803
5804 ----------------------------------------------------------
5805 g_debug_mesg := 'Entered CHECK_FIRST_APPROVER';
5806 IF PG_DEBUG in ('Y', 'C') THEN
5807 arp_standard.debug('check_first_approver: ' || g_debug_mesg);
5808 END IF;
5809 ----------------------------------------------------------
5810
5811 l_approval_path := wf_engine.getitemattrtext(
5812 itemtype => p_item_type,
5813 itemkey => p_item_key,
5814 aname => 'APPROVAL_PATH');
5815
5816 l_approver_user_name := wf_engine.GetItemAttrText(
5817 itemtype => p_item_type,
5818 itemkey => p_item_key,
5819 aname => 'ROLE');
5820
5821 IF (l_approval_path = 'LIMITS') THEN
5822
5823 IF (l_approver_user_name IS NOT NULL) THEN
5824 p_result := 'COMPLETE:UNNECESSARY_FIRST_APPROVER';
5825 ELSE
5826 p_result := 'COMPLETE:NO_ERROR';
5827 END IF;
5828
5829 ELSE -- HR hierarchy needs a first approver.
5830
5831 IF (l_approver_user_name IS NULL) THEN
5832 p_result := 'COMPLETE:MISSING_FIRST_APPROVER';
5833 ELSE
5834 p_result := 'COMPLETE:NO_ERROR';
5835 END IF;
5836
5837 END if;
5838
5839 RETURN;
5840
5841 EXCEPTION
5842 WHEN OTHERS THEN
5843
5844 wf_core.context(
5845 pkg_name => 'AR_AME_CMWF_API',
5846 proc_name => 'CHECK_FIRST_APPROVER',
5847 arg1 => p_item_type,
5848 arg2 => p_item_key,
5849 arg3 => p_funcmode,
5850 arg4 => to_char(p_actid),
5851 arg5 => g_debug_mesg);
5852
5853 RAISE;
5854
5855 END check_first_approver;
5856
5857
5858 -- This subroutine determines if the credit memo created is an on account
5859 -- credit memo. It looks at the row in ra_customer_trx given the credit
5860 -- memo customer trx id. In that row if the previous customer trx id is
5861 -- empty implying that this memo was not applied against any invoice then
5862 -- we conclude that the credit memo created is an on account credit memo.
5863 --
5864 PROCEDURE on_account_credit_memo (
5865 p_item_type IN VARCHAR2,
5866 p_item_key IN VARCHAR2,
5867 p_actid IN NUMBER,
5868 p_funcmode IN VARCHAR2,
5869 p_result IN OUT NOCOPY VARCHAR2) IS
5870
5871 l_credit_memo_number wf_item_attribute_values.text_value%TYPE;
5872 l_approver_user_name wf_item_attribute_values.text_value%TYPE;
5873 l_dummy wf_item_attribute_values.text_value%TYPE;
5874 l_on_account BOOLEAN DEFAULT FALSE;
5875
5876 -- If the previous customer trx id is null then
5877 -- the credit memo is an on account credit memo.
5878 -- So, if this cursor returns a row that means
5879 -- it is a on account credit memo
5880
5881 CURSOR c (p_credit_memo_id VARCHAR2) IS
5882 SELECT customer_trx_id
5883 FROM ra_customer_trx
5884 WHERE customer_trx_id = p_credit_memo_id
5885 AND previous_customer_trx_id IS NULL;
5886
5887 BEGIN
5888
5889 ----------------------------------------------------------
5890 g_debug_mesg := 'Entered ON_ACCOUNT_CREDIT_MEMO';
5891 IF PG_DEBUG in ('Y', 'C') THEN
5892 arp_standard.debug('on_account_credit_memo: ' || g_debug_mesg);
5893 END IF;
5894 ----------------------------------------------------------
5895
5896 l_credit_memo_number := wf_engine.getitemattrtext(
5897 itemtype => p_item_type,
5898 itemkey => p_item_key,
5899 aname => 'CREDIT_MEMO_NUMBER');
5900
5901 OPEN c (l_credit_memo_number);
5902 FETCH c INTO l_dummy;
5903 l_on_account := c%FOUND;
5904 CLOSE c;
5905
5906 IF l_on_account THEN
5907 p_result := 'COMPLETE:Y';
5908 ELSE
5909 p_result := 'COMPLETE:N';
5910 END IF;
5911
5912 RETURN;
5913
5914 EXCEPTION
5915 WHEN OTHERS THEN
5916
5917 wf_core.context(
5918 pkg_name => 'AR_AME_CMWF_API',
5919 proc_name => 'ON_ACCOUNT_CREDIT_MEMO',
5920 arg1 => p_item_type,
5921 arg2 => p_item_key,
5922 arg3 => p_funcmode,
5923 arg4 => to_char(p_actid),
5924 arg5 => g_debug_mesg);
5925
5926 RAISE;
5927
5928 END on_account_credit_memo;
5929
5930
5931 -- This subroutine determines if the collector shoudl be notified of the
5932 -- credit memo approval and creation. If the collector him/herself is the
5933 -- requestor then then he must not get two notifications. But, if he is not
5934 -- requestor then he must be informed.
5935
5936 PROCEDURE inform_collector (
5937 p_item_type IN VARCHAR2,
5938 p_item_key IN VARCHAR2,
5939 p_actid IN NUMBER,
5940 p_funcmode IN VARCHAR2,
5941 p_result IN OUT NOCOPY VARCHAR2) IS
5942
5943 l_requestor_user_name wf_item_attribute_values.text_value%TYPE;
5944 l_collector_user_name wf_item_attribute_values.text_value%TYPE;
5945
5946 BEGIN
5947
5948 ----------------------------------------------------------
5949 g_debug_mesg := 'Entered INFORM_COLLECTOR';
5950 IF PG_DEBUG in ('Y', 'C') THEN
5951 arp_standard.debug('inform_collector: ' || g_debug_mesg);
5952 END IF;
5953 ----------------------------------------------------------
5954
5955 IF (p_funcmode = 'RUN') THEN
5956
5957 -- restore_context(p_item_key);
5958
5959 l_requestor_user_name := wf_engine.GetItemAttrText(
5960 itemtype => p_item_type,
5961 itemkey => p_item_key,
5962 aname => 'REQUESTOR_USER_NAME');
5963
5964 l_collector_user_name := wf_engine.GetItemAttrText(
5965 itemtype => p_item_type,
5966 itemkey => p_item_key,
5967 aname => 'COLLECTOR_USER_NAME');
5968
5969 IF (l_collector_user_name = l_requestor_user_name) THEN
5970 p_result := 'COMPLETE:N';
5971 ELSE
5972 p_result := 'COMPLETE:Y';
5973 END IF;
5974
5975 RETURN;
5976
5977 END IF;
5978
5979 EXCEPTION
5980 WHEN OTHERS THEN
5981
5982 wf_core.context(
5983 pkg_name => 'AR_AME_CMWF_API',
5984 proc_name => 'INFORM_COLLECTOR',
5985 arg1 => p_item_type,
5986 arg2 => p_item_key,
5987 arg3 => p_funcmode,
5988 arg4 => to_char(p_actid),
5989 arg5 => g_debug_mesg);
5990
5991 RAISE;
5992
5993 END inform_collector;
5994
5995 /*4139346 */
5996 /* This was inntroduced for bug # 3865317 */
5997
5998 PROCEDURE AMEHandleTimeout (p_item_type IN VARCHAR2,
5999 p_item_key IN VARCHAR2,
6000 p_actid IN NUMBER,
6001 p_funcmode IN VARCHAR2,
6002 p_result OUT NOCOPY VARCHAR2) IS
6003
6004 l_profile_value VARCHAR2(30);
6005 l_result VARCHAR2(100);
6006
6007 BEGIN
6008
6009 ----------------------------------------------------------
6010 g_debug_mesg := 'Entered AMEHANDLETIMEOUT';
6011 IF PG_DEBUG in ('Y', 'C') THEN
6012 arp_standard.debug('AMEHandleTimeout: ' || g_debug_mesg);
6013 END IF;
6014 ----------------------------------------------------------
6015 --
6016 -- RUN mode - normal process execution
6017 --
6018
6019 IF (p_funcmode = 'RUN') THEN
6020
6021 l_profile_value := NVL(fnd_profile.value('AR_CMWF_TIME_OUT'), 'MANAGER');
6022
6023 wf_engine.setitemattrtext(
6024 itemtype => p_item_type,
6025 itemkey => p_item_key,
6026 aname => 'DEBUG',
6027 avalue => 'Profile: ' || l_profile_value);
6028
6029 IF l_profile_value = 'APPROVER' THEN
6030
6031 -- Profile option is set to look for the next approver (who may
6032 -- not be the manager) and send it to that approver. So, we will
6033 -- proceed in that direction. To do that all we need to is flag
6034 -- that we are in the timeout mode and simply put a value of "N"
6035 -- (next) in the result attribute.
6036
6037 wf_engine.setitemattrtext(
6038 itemtype => p_item_type,
6039 itemkey => p_item_key,
6040 aname => 'TIMEOUT_OCCURRED',
6041 avalue => 'Y');
6042
6043 -- let AME know that the current approver timed out, so that next
6044 -- time next time we ask for an approver it gives the next approver
6045 -- in line.
6046
6047 RecordResponseWithAME (
6048 p_item_type => p_item_type,
6049 p_item_key => p_item_key,
6050 p_response => ame_util.noResponseStatus);
6051
6052 -- Complete with transition set to "N" for next.
6053 p_result := 'COMPLETE:N';
6054 RETURN;
6055
6056 ELSE
6057
6058 -- profile option is set to look for manager, so continue
6059 -- with our original logic.
6060
6061 ar_ame_cmwf_api.amefindmanager(
6062 p_item_type => p_item_type,
6063 p_item_key => p_item_key,
6064 p_actid => p_actid,
6065 p_funcmode => p_funcmode,
6066 p_result => l_result);
6067
6068 -- in this branch the values that can be returned is T/F
6069 p_result := l_result;
6070
6071 END IF;
6072
6073 END IF;
6074
6075 END AMEHandleTimeout;
6076
6077 END ar_ame_cmwf_api;