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