[Home] [Help]
PACKAGE BODY: APPS.ARP_CMREQ_WF
Source
1 package body ARP_CMREQ_WF as
2 /* $Header: ARWCMWFB.pls 120.19.12010000.2 2008/09/01 10:00:23 naneja ship $ */
3 -- <describe the activity here>
4 --
5 -- IN
6 -- p_item_type - type of the current item
7 -- p_item_key - key of the current item
8 -- p_actid - process activity instance id
9 -- p_funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...)
10 -- OUT NOCOPY
11 -- p_result
12 -- - COMPLETE[:<result>]
13 -- activity has completed with the indicated result
14 -- - WAITING
15 -- activity is waiting for additional transitions
16 -- - DEFERED
17 -- execution should be defered to background
18 -- - NOTIFIED[:<notification_id>:<assigned_user>]
19 -- activity has notified an external entity that this
20 -- step must be performed. A call to wf_engine.CompleteActivty
21 -- will signal when this step is complete. Optional
22 -- return of notification ID and assigned user.
23 -- - ERROR[:<error_code>]
24 -- function encountered an error.
25
26
27
28
29 -- Constants definition
30 ----------------------------------------------------------------------------
31 -- Max number of approver
32 C_MAX_NUMBER_APPROVER CONSTANT NUMBER := 200;
33
34 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
35
36
37 PROCEDURE CheckUserInHR( p_employee_id IN NUMBER,
38 p_count OUT NOCOPY NUMBER);
39
40 PROCEDURE CheckUserInTable(p_item_type IN VARCHAR2,
41 p_item_key IN VARCHAR2,
42 p_employee_id IN NUMBER,
43 p_primary_flag IN VARCHAR2,
44 p_count OUT NOCOPY NUMBER);
45 -----------------------------------------------------------------------------
46
47
48 PROCEDURE callback_routine (
49 p_item_type IN VARCHAR2,
50 p_item_key IN VARCHAR2,
51 p_activity_id IN NUMBER,
52 p_command IN VARCHAR2,
53 p_result IN OUT NOCOPY VARCHAR2) IS
54
55 CURSOR org IS
56 SELECT org_id
57 FROM ra_cm_requests_all
58 WHERE request_id = p_item_key;
59
60 l_debug_mesg VARCHAR2(240);
61 l_org_id ra_cm_requests_all.org_id%TYPE;
62
63 BEGIN
64
65 OPEN org;
66 FETCH org INTO l_org_id;
67 CLOSE org;
68
69 wf_engine.setitemattrnumber(
70 p_item_type,
71 p_item_key,
72 'ORG_ID',
73 l_org_id);
74
75 l_debug_mesg := 'Org ID: ' || l_org_id;
76
77 IF ( p_command = 'RUN' ) THEN
78
79 -- executable statements for RUN mode
80 -- resultout := 'CMREQ_APPROVAL';
81 RETURN;
82 END IF;
83
84 IF ( p_command = 'SET_CTX' ) THEN
85
86 -- executable statements for establishing context information
87 mo_global.set_policy_context(
88 p_access_mode => 'S',
89 p_org_id => l_org_id);
90
91
92 END IF;
93
94 IF ( p_command = 'TEST_CTX' ) THEN
95
96 -- your executable statements for testing the validity of the current
97 -- context information
98 IF (NVL(mo_global.get_access_mode, '-9999') <> 'S') OR
99 (NVL(mo_global.get_current_org_id, -9999) <> l_org_id) THEN
100 p_result := 'FALSE';
101 ELSE
102 p_result := 'TRUE';
103 END IF;
104 RETURN;
105
106 END IF;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110
111 wf_core.context(
112 pkg_name => 'ARP_CMREQ_WF',
113 proc_name => 'CALLBACK_ROUTINE',
114 arg1 => p_item_type,
115 arg2 => p_item_key,
116 arg3 => to_char(p_activity_id),
117 arg4 => p_command,
118 arg5 => l_debug_mesg);
119
120 RAISE;
121
122 END callback_routine;
123
124
125 PROCEDURE FindTrx(p_item_type IN VARCHAR2,
126 p_item_key IN VARCHAR2,
127 p_actid IN NUMBER,
128 p_funcmode IN VARCHAR2,
129 p_result OUT NOCOPY VARCHAR2) IS
130
131 l_debug_mesg varchar2(240);
132 l_workflow_document_id number;
133 l_customer_trx_id number;
134 l_amount number;
135 l_tax_amount number;
136 l_line_amount number;
137 l_freight_amount number;
138 l_original_line_amount number;
139 l_original_tax_amount number;
140 l_original_freight_amount number;
141 l_original_total number;
142 l_reason_code varchar2(45);
143 l_reason_meaning varchar2(80);
144 l_currency_code varchar2(15);
145 l_requestor_id number;
146 l_requestor_user_name varchar2(100);
147 l_requestor_display_name varchar2(100);
148 /* Bug 3206020 Changed comments width from 240 to 1760. */
149 l_comments varchar2(1760);
150 l_orig_trx_number ra_cm_requests_all.orig_trx_number%TYPE;
151 l_tax_ex_cert_num ra_cm_requests_all.tax_ex_cert_num%TYPE;
152
153 /*7367350 storing internal comment and inserting notes*/
154 l_internal_comment VARCHAR2(1760) DEFAULT NULL;
155
156 cursor c1 is
157 SELECT name, display_name
158 FROM wf_users
159 WHERE orig_system = 'PER'
160 AND orig_system_id = l_requestor_id;
161
162
163 cursor c2 is
164 SELECT name, display_name
165 FROM wf_users
166 WHERE orig_system = 'FND_USR'
167 AND orig_system_id = l_requestor_id;
168
169
170 begin
171 -- SetOrgContext (p_item_key);
172
173 --
174 -- RUN mode - normal process execution
175 --
176 if (p_funcmode = 'RUN') then
177
178 ------------------------------------------------------------
179 l_debug_mesg := 'Get the requested trx and request_id';
180 ------------------------------------------------------------
181 /*7367350 retrieve info */
182 GetCustomerTrxInfo(p_item_type,
183 p_item_key,
184 l_workflow_document_id,
185 l_customer_trx_id,
186 l_amount,
187 l_line_amount,
188 l_tax_amount,
189 l_freight_amount,
190 l_reason_code,
191 l_reason_meaning,
192 l_requestor_id,
193 l_comments,
194 l_orig_trx_number,
195 l_tax_ex_cert_num,
196 l_internal_comment);
197
198
199 if l_customer_trx_id <> -1 then
200
201 WF_ENGINE.SetItemAttrNumber(p_item_type,
202 p_item_key,
203 'WORKFLOW_DOCUMENT_ID',
204 l_workflow_document_id);
205
206
207 WF_ENGINE.SetItemAttrNumber(p_item_type,
208 p_item_key,
209 'CUSTOMER_TRX_ID',
210 l_customer_trx_id);
211
212 WF_ENGINE.SetItemAttrNumber(p_item_type,
213 p_item_key,
214 'TOTAL_CREDIT_TO_INVOICE',
215 l_amount);
216
217 WF_ENGINE.SetItemAttrNumber(p_item_type,
218 p_item_key,
219 'TOTAL_CREDIT_TO_LINES',
220 l_line_amount);
221
222 WF_ENGINE.SetItemAttrNumber(p_item_type,
223 p_item_key,
224 'TOTAL_CREDIT_TO_TAX',
225 l_tax_amount);
226
227 WF_ENGINE.SetItemAttrNumber(p_item_type,
228 p_item_key,
229 'TOTAL_CREDIT_TO_FREIGHT',
230 l_freight_amount);
231
232 WF_ENGINE.SetItemAttrText(p_item_type,
233 p_item_key,
234 'REASON',
235 l_reason_code);
236
237 WF_ENGINE.SetItemAttrText(p_item_type,
238 p_item_key,
239 'REASON_MEANING',
240 l_reason_meaning);
241
242 WF_ENGINE.SetItemAttrText(p_item_type,
243 p_item_key,
244 'COMMENTS',
245 l_comments);
246
247
248 WF_ENGINE.SetItemAttrNumber(p_item_type,
249 p_item_key,
250 'REQUESTOR_ID',
251 l_requestor_id);
252
253 WF_ENGINE.SetItemAttrText(p_item_type,
254 p_item_key,
255 'ORIG_TRX_NUMBER',
256 l_orig_trx_number);
257
258 WF_ENGINE.SetItemAttrText(p_item_type,
259 p_item_key,
260 'TAX_EX_CERT_NUM',
261 l_tax_ex_cert_num);
262 /*7367350 set attribute*/
263 wf_engine.SetItemAttrText(p_item_type,
264 p_item_key,
265 'INTERNAL_COMMENTS',
266 l_internal_comment);
267
268 -- set requestor name and display name
269 if ( l_requestor_id <> -1) then
270 open c1;
271 fetch c1 into l_requestor_user_name, l_requestor_display_name;
272 if c1%notfound then
273 l_requestor_user_name := null;
274 l_requestor_display_name := null;
275 open c2;
276 fetch c2 into l_requestor_user_name, l_requestor_display_name;
277 if c2%notfound then
278 l_requestor_user_name := null;
279 l_requestor_display_name := null;
280 l_debug_mesg := 'could not find the requestor';
281 end if;
282 end if;
283 end if;
284
285
286 WF_ENGINE.SetItemAttrText(p_item_type,
287 p_item_key,
288 'REQUESTOR_USER_NAME',
289 l_requestor_user_name);
290
291 WF_ENGINE.SetItemAttrText(p_item_type,
292 p_item_key,
293 'REQUESTOR_DISPLAY_NAME',
294 l_requestor_display_name);
295
296 -- set amount for trx.
297
298 GetTrxAmount(p_item_type,
299 p_item_key,
300 l_customer_trx_id,
301 l_original_line_amount,
302 l_original_tax_amount,
303 l_original_freight_amount,
304 l_original_total ,
305 l_currency_code);
306
307 WF_ENGINE.SetItemAttrNumber(p_item_type,
308 p_item_key,
309 'ORIGINAL_LINE_AMOUNT',
310 l_original_line_amount);
311
312 WF_ENGINE.SetItemAttrNumber(p_item_type,
313 p_item_key,
314 'ORIGINAL_TAX_AMOUNT',
315 l_original_tax_amount);
316
317
318 WF_ENGINE.SetItemAttrNumber(p_item_type,
319 p_item_key,
320 'ORIGINAL_FREIGHT_AMOUNT',
321 l_original_freight_amount);
322
323 WF_ENGINE.SetItemAttrNumber(p_item_type,
324 p_item_key,
325 'ORIGINAL_TOTAL',
326 l_original_total);
327
328 WF_ENGINE.SetItemAttrText(p_item_type,
329 p_item_key,
330 'CURRENCY_CODE',
331 l_currency_code);
332 p_result := 'COMPLETE:T';
333 return;
334 else
335 p_result := 'COMPLETE:F';
336 return;
337 end if;
338
339 end if; -- end of run mode
340
341 --
342 -- CANCEL mode
343 --
344 -- This is an event point is called with the effect of the activity must
345 -- be undone, for example when a process is reset to an earlier point
346 -- due to a loop back.
347 --
348 if (p_funcmode = 'CANCEL') then
349
350 -- no result needed
351 p_result := 'COMPLETE:';
352 return;
353 end if;
354
355
356 --
357 -- Other execution modes may be created in the future. Your
358 -- activity will indicate that it does not implement a mode
359 -- by returning null
360 --
361 p_result := '';
362 return;
363
364 exception
365 when others then
366 -- The line below records this function call in the error system
367 -- in the case of an exception.
368 wf_core.context('ARP_CMREQ_WF', 'FindTrx',
369 p_item_type, p_item_key, to_char(p_actid), p_funcmode, l_debug_mesg);
370 raise;
371
372 end FindTrx;
373
374 /*7367350 added parameter to retrive internal comment*/
375 PROCEDURE GetCustomerTrxInfo(p_item_type IN VARCHAR2,
376 p_item_key IN VARCHAR2,
377 p_workflow_document_id OUT NOCOPY NUMBER,
378 p_customer_trx_id OUT NOCOPY NUMBER,
379 p_amount OUT NOCOPY NUMBER,
380 p_line_amount OUT NOCOPY NUMBER,
381 p_tax_amount OUT NOCOPY NUMBER,
382 p_freight_amount OUT NOCOPY NUMBER,
383 p_reason OUT NOCOPY VARCHAR2,
384 p_reason_meaning OUT NOCOPY VARCHAR2,
385 p_requestor_id OUT NOCOPY NUMBER,
386 p_comments OUT NOCOPY VARCHAR2,
387 p_orig_trx_number OUT NOCOPY VARCHAR2,
388 p_tax_ex_cert_num OUT NOCOPY VARCHAR2,
389 p_internal_comment OUT NOCOPY VARCHAR2) IS
390
391 l_debug_mesg varchar2(240);
392 l_workflow_document_id number;
393 l_customer_trx_id number;
394 l_amount number;
395 l_line_amount number;
396 l_tax_amount number;
397 l_freight_amount number;
398 l_created_by number;
399 l_line_credit_flag varchar2(1);
400 l_tax_disclaimer varchar2(250);
401 l_orig_trx_number ra_cm_requests_all.orig_trx_number%TYPE;
402 l_tax_ex_cert_num ra_cm_requests_all.tax_ex_cert_num%TYPE;
403
404
405
406 BEGIN
407 -- SetOrgContext (p_item_key);
408
409 ----------------------------------------------------------
410 l_debug_mesg := 'Get the customer trx id from table';
411 ----------------------------------------------------------
412 select r.request_id,
413 r.customer_trx_id,
414 r.total_amount,
415 r.cm_reason_code,
416 l.meaning,
417 r.created_by,
418 r.comments,
419 r.line_credits_flag,
420 r.line_amount,
421 r.tax_amount,
422 r.freight_amount,
423 r.ORIG_TRX_NUMBER,
424 r.TAX_EX_CERT_NUM,
425 r.internal_comment
426 into l_workflow_document_id,
427 l_customer_trx_id,
431 l_created_by,
428 l_amount,
429 p_reason,
430 p_reason_meaning,
432 p_comments,
433 l_line_credit_flag,
434 l_line_amount,
435 l_tax_amount,
436 l_freight_amount,
437 l_orig_trx_number,
438 l_tax_ex_cert_num,
439 p_internal_comment
440 from ar_lookups l,
441 ra_cm_requests r
442 where r.request_id = p_item_key
443 and r.cm_reason_code = l.lookup_code
444 and l.lookup_type = 'CREDIT_MEMO_REASON';
445
446 p_workflow_document_id := l_workflow_document_id;
447 p_customer_trx_id := l_customer_trx_id;
448 p_amount := l_amount;
449 p_line_amount := l_line_amount;
450 p_tax_amount := l_tax_amount;
451 p_freight_amount := l_freight_amount;
452 p_orig_trx_number := l_orig_trx_number;
453 p_tax_ex_cert_num := l_tax_ex_cert_num;
454
455
456 IF l_line_credit_flag = 'Y' THEN
457 p_line_amount := l_amount;
458 END IF;
459
460 select employee_id
461 into p_requestor_id
462 from fnd_user
463 where user_id = l_created_by;
464
465
466 IF (p_requestor_id IS NULL) THEN
467 p_requestor_id := l_created_by;
468 END IF;
469
470 l_tax_disclaimer := NULL;
471
472 if l_line_credit_flag = 'Y'
473 then
474 fnd_message.set_name('AR', 'ARW_INV_MSG10');
475 l_tax_disclaimer := fnd_message.get;
476 end if;
477
478 WF_ENGINE.SetItemAttrText(p_item_type,
479 p_item_key,
480 'TAX_DISCLAIMER',
481 l_tax_disclaimer);
482
483 exception
484 when others then
485 p_workflow_document_id := -1;
486 p_customer_trx_id := -1;
487 p_amount := 0;
488 p_tax_amount := 0;
489 p_line_amount := 0;
490 p_freight_amount := 0;
491 p_reason := NULL;
492 p_reason_meaning := NULL;
493 p_comments := NULL;
494 p_requestor_id := -1;
495 p_orig_trx_number := NULL;
496 p_tax_ex_cert_num := NULL;
497
498 wf_core.Context('ARP_CMREQ_WF', 'GetCustomerTrxInfo',
499 null, null, null, l_debug_mesg);
500 raise;
501
502 END GetCustomerTrxInfo;
503
504 PROCEDURE GetTrxAmount(p_item_type IN VARCHAR2,
505 p_item_key IN VARCHAR2,
506 p_customer_trx_id IN NUMBER,
507 p_original_line_amount OUT NOCOPY NUMBER,
508 p_original_tax_amount OUT NOCOPY NUMBER,
509 p_original_freight_amount OUT NOCOPY NUMBER,
510 p_original_total OUT NOCOPY NUMBER,
511 p_currency_code OUT NOCOPY VARCHAR2) IS
512
513 l_debug_mesg varchar2(240);
514 BEGIN
515 -- SetOrgContext (p_item_key);
516 ----------------------------------------------------------
517 l_debug_mesg := 'Get the customer trx amount from table';
518 ----------------------------------------------------------
519
520 select sum(ps.amount_line_items_original), sum(ps.tax_original),
521 sum(ps.freight_original), sum(ps.amount_due_original),
522 ps.invoice_currency_code
523 into p_original_line_amount , p_original_tax_amount,
524 p_original_freight_amount, p_original_total, p_currency_code
525 from ar_payment_schedules ps
526 where ps.customer_trx_id = p_customer_trx_id
527 group by ps.invoice_currency_code ;
528
529 exception
530 when others then
531 p_original_line_amount := NULL;
532 p_original_tax_amount := NULL;
533 p_original_freight_amount := NULL;
534 p_original_total := NULL;
535 p_currency_code := NULL;
536
537 wf_core.Context('ARP_CMREQ_WF', 'GetTrxAmount',
538 null, null, null, l_debug_mesg);
539 raise;
540
541 END GetTrxAmount;
542
543
544
545 PROCEDURE FindCustomer(p_item_type IN VARCHAR2,
546 p_item_key IN VARCHAR2,
547 p_actid IN NUMBER,
548 p_funcmode IN VARCHAR2,
549 p_result OUT NOCOPY VARCHAR2) IS
550
551 l_debug_mesg varchar2(240);
552 l_customer_trx_id number;
553 l_customer_id number(15);
554 l_bill_to_site_use_id number;
555 l_bill_to_customer_name varchar2(50);
556 l_bill_to_customer_number varchar2(30); /* Bug Fix 1882580 */
557 l_ship_to_customer_number varchar2(30);
558 l_ship_to_customer_name varchar2(50);
559 l_trx_number varchar2(20);
560 l_request_url ra_cm_requests.url%TYPE;
561 l_url ra_cm_requests.url%TYPE;
562 l_request_id number;
563 l_trans_url ra_cm_requests.transaction_url%TYPE;
564 l_act_url ra_cm_requests.activities_url%TYPE;
568 begin
565 wf_flag varchar2(1) := 'Y';
566
567
569 -- SetOrgContext (p_item_key);
570
571 --
572 -- RUN mode - normal process execution
573 --
574 if (p_funcmode = 'RUN') then
575
576 ------------------------------------------------------------
577 l_debug_mesg := 'Get requested trx id ';
578 ------------------------------------------------------------
579 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
580 p_item_type,
581 p_item_key,
582 'CUSTOMER_TRX_ID');
583
584 ------------------------------------------------------------
585 l_debug_mesg := 'Get Customer info based on requested trx ';
586 ------------------------------------------------------------
587 IF PG_DEBUG in ('Y', 'C') THEN
588 arp_util.debug('CheckUserInHR: ' || 'before getting cust info');
589 END IF;
590
591 FindCustomerInfo(l_customer_trx_id,
592 l_bill_to_site_use_id,
593 l_customer_id,
594 l_bill_to_customer_name,
595 l_bill_to_customer_number,
596 l_ship_to_customer_number,
597 l_ship_to_customer_name,
598 l_trx_number );
599
600 IF PG_DEBUG in ('Y', 'C') THEN
601 arp_util.debug('CheckUserInHR: ' || 'l_bill_to_customer_name ' || l_bill_to_customer_name);
602 END IF;
603
604 if l_bill_to_customer_name is NULL then
605 -- no customer has been found.
606 p_result := 'COMPLETE:F';
607 return;
608 end if;
609
610
611
612 ----------------------------------------------------------------------
613 l_debug_mesg := 'Set value for customer_id(name) in workflow process';
614 -----------------------------------------------------------------------
615
616 WF_ENGINE.SetItemAttrNumber(p_item_type,
617 p_item_key,
618 'CUSTOMER_ID',
619 l_customer_id);
620
621
622 WF_ENGINE.SetItemAttrText(p_item_type,
623 p_item_key,
624 'CUSTOMER_NAME',
625 l_bill_to_customer_name);
626
627 -- set the bill to and ship to customer info.
628
629 WF_ENGINE.SetItemAttrText(p_item_type,
630 p_item_key,
631 'BILL_TO_CUSTOMER_NAME',
632 l_bill_to_customer_name);
633
634 /* Bug Fix 1882580. Since l_bill_to_customer_number is changed
635 from number to varchar2, replaced the function in call to
636 WF_ENGINE fom SetItemAttrNumber to SetItemAttrText
637 */
638
639 WF_ENGINE.SetItemAttrText(p_item_type,
640 p_item_key,
641 'BILL_TO_CUSTOMER_NUMBER',
642 l_bill_to_customer_number);
643
644 WF_ENGINE.SetItemAttrText(p_item_type,
645 p_item_key,
646 'SHIP_TO_CUSTOMER_NAME',
647 l_ship_to_customer_name);
648
649 /* Bug Fix 1882580. Since l_bill_to_customer_number is changed
650 from number to varchar2, replaced the function in call to
651 WF_ENGINE fom SetItemAttrNumber to SetItemAttrText
652 */
653 WF_ENGINE.SetItemAttrText(p_item_type,
654 p_item_key,
655 'SHIP_TO_CUSTOMER_NUMBER',
656 l_ship_to_customer_number);
657
658 -- set the trx number
659
660 WF_ENGINE.SetItemAttrText(p_item_type,
661 p_item_key,
662 'TRX_NUMBER',
663 l_trx_number);
664
665
666
667 ----------------------------------------------------------------------
668 l_debug_mesg := 'Set value for bill_to_site_use_id in workflow process';
669 -----------------------------------------------------------------------
670 WF_ENGINE.SetItemAttrNumber(p_item_type,
671 p_item_key,
672 'BILL_TO_SITE_USE_ID',
673 l_bill_to_site_use_id);
674
675
676
677 -- set the URL site
678
679 l_request_id := WF_ENGINE.GetItemAttrNumber(
680 p_item_type,
681 p_item_key,
682 'WORKFLOW_DOCUMENT_ID');
683
684 select url
685 into l_url
686 from ra_cm_requests
687 where request_id = p_item_key;
688
689 l_request_url := l_url;
690
691
692 WF_ENGINE.SetItemAttrText(p_item_type,
693 p_item_key,
694 'REQUEST_URL',
695 l_request_url);
696
697
698 -- set the transaction number URL site.
699
700 select transaction_url
704
701 into l_trans_url
702 from ra_cm_requests
703 where request_id = p_item_key;
705
706 WF_ENGINE.SetItemAttrText(p_item_type,
707 p_item_key,
708 'TRANSACTION_NUMBER_URL',
709 l_trans_url);
710
711
712 select activities_url
713 into l_act_url
714 from ra_cm_requests
715 where request_id =p_item_key;
716
717
718 WF_ENGINE.SetItemAttrText(p_item_type,
719 p_item_key,
720 'TRANSACTION_ACTIVITY_URL',
721 l_act_url);
722
723 p_result := 'COMPLETE:T';
724 return;
725
726 end if; -- end of run mode
727
728 --
729 -- CANCEL mode
730 --
731 --
732 if (p_funcmode = 'CANCEL') then
733
734 -- no result needed
735 p_result := 'COMPLETE:';
736 return;
737 end if;
738
739
740 --
741 -- Other execution modes.
742 --
743 p_result := '';
744 return;
745
746 exception
747 when others then
748 -- The line below records this function call in the error system
749 -- in the case of an exception.
750 wf_core.context('ARP_CMREQ_WF', 'FindCustomer',
751 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
752 raise;
753
754 end FindCustomer;
755
756 PROCEDURE FindCustomerInfo(p_customer_trx_id IN NUMBER,
757 p_bill_to_site_use_id OUT NOCOPY NUMBER,
758 p_customer_id OUT NOCOPY NUMBER,
759 p_bill_to_customer_name OUT NOCOPY VARCHAR2,
760 p_bill_to_customer_number OUT NOCOPY VARCHAR2,
761 p_ship_to_customer_number OUT NOCOPY VARCHAR2,
762 p_ship_to_customer_name OUT NOCOPY VARCHAR2,
763 p_trx_number OUT NOCOPY VARCHAR2 ) IS
764
765 l_debug_mesg varchar2(240);
766
767 BEGIN
768
769 ---------------------------------------------------------------------------
770 l_debug_mesg := 'find customer id and name based on requested invoice';
771 ---------------------------------------------------------------------------
772
773 BEGIN
774 select rct.bill_to_site_use_id,
775 rct.bill_to_customer_id,
776 substrb(party.party_name,1,50),
777 bill_to_cust.account_number,
778 rct.trx_number
779 into p_bill_to_site_use_id, p_customer_id,
780 p_bill_to_customer_name, p_bill_to_customer_number,
781 p_trx_number
782 from hz_cust_accounts bill_to_cust,
783 hz_parties party,
784 ra_customer_trx rct
785 where rct.customer_trx_id = p_customer_trx_id
786 and rct.bill_to_customer_id = bill_to_cust.cust_account_id
787 and bill_to_cust.party_id = party.party_id ;
788
789 EXCEPTION
790 WHEN NO_DATA_FOUND THEN
791 p_customer_id := NULL ;
792 p_bill_to_customer_name := NULL;
793 p_bill_to_customer_number := NULL;
794
795 WHEN OTHERS THEN
796 wf_core.Context('ARP_CMREQ_WF', 'FindCustomerInfo',
797 null, null, null, l_debug_mesg);
798 raise;
799 END;
800
801 --------------------------------------------------------------------------------
802 l_debug_mesg := 'find ship to customer id and name based on requested invoice';
803 --------------------------------------------------------------------------------
804 BEGIN
805 select substrb(party.party_name,1,50),
806 ship_to_cust.account_number
807 into p_ship_to_customer_name,
808 p_ship_to_customer_number
809 from hz_cust_accounts ship_to_cust,
810 hz_parties party,
811 ra_customer_trx rct
812 where rct.customer_trx_id = p_customer_trx_id
813 and rct.ship_to_customer_id = ship_to_cust.cust_account_id
814 and ship_to_cust.party_id = party.party_id;
815
816 EXCEPTION
817 WHEN NO_DATA_FOUND THEN
818 p_ship_to_customer_name := NULL;
819 p_ship_to_customer_number := NULL;
820
821 WHEN OTHERS THEN
822 wf_core.Context('ARP_CMREQ_WF', 'FindCustomerInfo',
823 null, null, null, l_debug_mesg);
824 raise;
825 END;
826
827
828 EXCEPTION
829
830 WHEN OTHERS THEN
831 wf_core.Context('ARP_CMREQ_WF', 'FindCustomerInfo',
832 null, null, null, l_debug_mesg);
833 raise;
834
835
836 END FindCustomerInfo;
837
838
839 PROCEDURE FindCollector(p_item_type IN VARCHAR2,
840 p_item_key IN VARCHAR2,
841 p_actid IN NUMBER,
842 p_funcmode IN VARCHAR2,
843 p_result OUT NOCOPY VARCHAR2) IS
844
845 l_debug_mesg varchar2(240);
849 l_bill_to_site_use_id number(15);
846
847 l_customer_trx_id number(15);
848 l_customer_id number;
850 l_collector_employee_id number(15);
851 l_collector_id number(15);
852 l_collector_name varchar2(30); -- name displayed in collector form.
853 l_collector_user_name varchar2(100);
854 l_collector_display_name varchar2(240); -- name for collector as employee
855
856 begin
857 -- SetOrgContext (p_item_key);
858
859 --
860 -- RUN mode - normal process execution
861 --
862 if (p_funcmode = 'RUN') then
863
864 -----------------------------------------------------------------
865 l_debug_mesg := 'Get the value of customer_trx_id(customer id)';
866 -----------------------------------------------------------------
867 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
868 p_item_type,
869 p_item_key,
870 'CUSTOMER_TRX_ID');
871
872
873 l_customer_id := WF_ENGINE.GetItemAttrNumber(
874 p_item_type,
875 p_item_key,
876 'CUSTOMER_ID');
877
878
879 ----------------------------------------------------------------------
880 l_debug_mesg := 'get value of bill_to_site_use_id from workflow process';
881 -----------------------------------------------------------------------
882 l_bill_to_site_use_id := WF_ENGINE.GetItemAttrNumber(
883 p_item_type,
884 p_item_key,
885 'BILL_TO_SITE_USE_ID');
886
887 -----------------------------------------------------------------------
888 l_debug_mesg := 'Find Collector Info';
889 -----------------------------------------------------------------------
890
891 FindCollectorInfo(l_customer_id,
892 l_bill_to_site_use_id,
893 l_collector_employee_id,
894 l_collector_id,
895 l_collector_name);
896
897 if l_collector_name is NULL then
898 -- no collector has been found.
899 p_result := 'COMPLETE:F';
900 return;
901 end if;
902
903 ----------------------------------------------------------------
904 l_debug_mesg := 'Set value for collector in workflow process';
905 ----------------------------------------------------------------
906
907 WF_ENGINE.SetItemAttrNumber(p_item_type,
908 p_item_key,
909 'COLLECTOR_EMPLOYEE_ID',
910 l_collector_employee_id);
911
912 WF_ENGINE.SetItemAttrNumber(p_item_type,
913 p_item_key,
914 'COLLECTOR_ID',
915 l_collector_id);
916
917 WF_ENGINE.SetItemAttrText(p_item_type,
918 p_item_key,
919 'COLLECTOR_NAME',
920 l_collector_name);
921
922 -------------------------------------------------------------------
923 l_debug_mesg := 'Set user name for the collector';
924 ------------------------------------------------------------------
925 WF_DIRECTORY.GetUserName('PER',
926 l_collector_employee_id,
927 l_collector_user_name,
928 l_collector_display_name);
929
930 if l_collector_user_name is NULL then
931
932 ----------------------------------------------------------------
933 l_debug_mesg := 'The collector has not been defined in directory';
934 -----------------------------------------------------------------
935 p_result := 'COMPLETE:F';
936 return;
937 else
938 WF_ENGINE.SetItemAttrText(p_item_type,
939 p_item_key,
940 'COLLECTOR_USER_NAME',
941 l_collector_user_name);
942
943 WF_ENGINE.SetItemAttrText(p_item_type,
944 p_item_key,
945 'COLLECTOR_DISPLAY_NAME',
946 l_collector_display_name);
947 end if;
948
949
950 p_result := 'COMPLETE:T';
951 return;
952
953 end if; -- end of run mode
954
955 --
956 -- CANCEL mode
957 --
958 -- This is an event point is called with the effect of the activity must
959 -- be undone, for example when a process is reset to an earlier point
960 -- due to a loop back.
961 --
962 if (p_funcmode = 'CANCEL') then
963
964 -- no result needed
965 p_result := 'COMPLETE:';
966 return;
967 end if;
968
969
970 --
971 -- Other execution modes may be created in the future. Your
972 -- activity will indicate that it does not implement a mode
973 -- by returning null
974 --
975 p_result := '';
976 return;
977
978 exception
979 when others then
983 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
980 -- The line below records this function call in the error system
981 -- in the case of an exception.
982 wf_core.context('ARP_CMREQ_WF', 'FindCollector',
984 raise;
985
986 end FindCollector;
987
988
989 PROCEDURE FindCollectorInfo(p_customer_id IN NUMBER,
990 p_bill_to_site_use_id IN NUMBER,
991 p_collector_employee_id OUT NOCOPY NUMBER,
992 p_collector_id OUT NOCOPY NUMBER,
993 p_collector_name OUT NOCOPY VARCHAR2) IS
994
995 l_debug_mesg varchar2(240);
996
997 BEGIN
998 ---------------------------------------------------------------------------
999 l_debug_mesg := 'find collector id and name based on customer site id';
1000 ---------------------------------------------------------------------------
1001 select col.employee_id, cp.collector_id,
1002 col.name
1003 into p_collector_employee_id, p_collector_id,
1004 p_collector_name
1005 from ar_collectors col, hz_customer_profiles cp
1006 where cp.cust_account_id = p_customer_id
1007 and cp.site_use_id = p_bill_to_site_use_id
1008 and cp.collector_id = col.collector_id ;
1009
1010 EXCEPTION
1011 WHEN NO_DATA_FOUND THEN
1012
1013 -- Bug 2609335 : when no collector defined at site level, go up to customer level
1014 BEGIN
1015
1016 ---------------------------------------------------------------------------
1017 l_debug_mesg := 'find collector id and name based on customer id';
1018 ---------------------------------------------------------------------------
1019 select col.employee_id,
1020 cp_cust.collector_id,
1021 col.name
1022 into p_collector_employee_id,
1023 p_collector_id,
1024 p_collector_name
1025 from ar_collectors col,
1026 hz_customer_profiles cp_cust
1027 where cp_cust.cust_account_id = p_customer_id
1028 and cp_cust.site_use_id IS NULL
1029 and cp_cust.collector_id = col.collector_id ;
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033
1034 p_collector_employee_id := -9999 ;
1035 p_collector_id := -9999 ;
1036 p_collector_name := NULL ;
1037 END;
1038
1039 WHEN OTHERS THEN
1040 wf_core.Context('ARP_CMREQ_WF', 'FindCollectorInfo',
1041 null, null, null, l_debug_mesg);
1042 raise;
1043
1044 END FindCollectorInfo;
1045
1046 PROCEDURE DefaultSendTo (p_item_type IN VARCHAR2,
1047 p_item_key IN VARCHAR2,
1048 p_actid IN NUMBER,
1049 p_funcmode IN VARCHAR2,
1050 p_result OUT NOCOPY VARCHAR2) IS
1051
1052 /* Bug 991922 : temp variables */
1053 l_customer_trx_id number;
1054 l_invoicing_rule_id number;
1055 l_need_rule_mesg varchar2(2000) DEFAULT
1056 'Please enter a revenue rule before approving this request because the disputed transaction has accounting rules.';
1057 l_credit_accounting_rule varchar2(65);
1058 /* Bug3195343 */
1059 l_collector_employee_id number;
1060 l_collector_user_id number;
1061 l_debug_mesg varchar2(240);
1062 l_reason_code varchar2(45);
1063 l_currency_code varchar2(30);
1064 l_approver_id number;
1065 l_approver_user_name varchar2(30);
1066 l_employee_id number;
1067 l_collector_user_name varchar2(30);
1068 l_collector_display_name varchar2(240);
1069 /* Bug 3195343 */
1070 Cursor c1 is
1071 Select user_id
1072 From fnd_user
1073 Where employee_id = l_collector_employee_id;
1074 begin
1075 -- SetOrgContext (p_item_key);
1076
1077 --
1078 -- RUN mode - normal process execution
1079 --
1080 if (p_funcmode = 'RUN') then
1081
1082 ------------------------------------------------------------
1083 l_debug_mesg := 'Defaulting Send To to Primary Approver with lowest $ amount';
1084 ------------------------------------------------------------
1085
1086 /* Bug 991922 : get additional information to determine if rule is required */
1087
1088 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
1089 p_item_type,
1090 p_item_key,
1091 'CUSTOMER_TRX_ID');
1092
1093 l_credit_accounting_rule := WF_ENGINE.GetItemAttrText(
1094 p_item_type,
1095 p_item_key,
1096 'CREDIT_ACCOUNTING_RULE');
1097
1098 l_reason_code := WF_ENGINE.GetItemAttrText(
1099 p_item_type,
1100 p_item_key,
1101 'REASON');
1102
1103 l_currency_code := WF_ENGINE.GetItemAttrText(
1104 p_item_type,
1108 SelectFirstPrimaryApproverId(l_reason_code,
1105 p_item_key,
1106 'CURRENCY_CODE');
1107
1109 l_currency_code,
1110 l_approver_id);
1111
1112
1113 if l_approver_id = -1 then
1114
1115 -----------------------------------------
1116 l_debug_mesg := 'No first approver found';
1117 ------------------------------------------
1118
1119 WF_ENGINE.SetItemAttrText(p_item_type,
1120 p_item_key,
1121 'ROLE',
1122 '');
1123
1124
1125 p_result := 'COMPLETE:F';
1126 return;
1127
1128 else
1129
1130 GetEmployeeInfo(l_approver_id,
1131 p_item_type,
1132 p_item_key,
1133 'Y');
1134
1135 l_approver_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
1136 p_item_key,
1137 'APPROVER_USER_NAME');
1138 IF l_approver_user_name IS NULL THEN
1139 p_result := 'COMPLETE:F';
1140 return;
1141 ELSE
1142
1143 WF_ENGINE.SetItemAttrText(p_item_type,
1144 p_item_key,
1145 'ROLE',
1146 l_approver_user_name);
1147
1148
1149 l_collector_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
1150 p_item_key,
1151 'COLLECTOR_USER_NAME');
1152
1153 WF_ENGINE.SetItemAttrText(p_item_type,
1154 p_item_key,
1155 'APPROVER_USER_NAME',
1156 l_collector_user_name);
1157
1158 -- Bug 1331562 : set approver_display_name to collector's name, so that details
1159 -- inserted into Notes are accurate
1160
1161 l_collector_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
1162 p_item_key,
1163 'COLLECTOR_DISPLAY_NAME');
1164 WF_ENGINE.SetItemAttrText(p_item_type,
1165 p_item_key,
1166 'APPROVER_DISPLAY_NAME',
1167 l_collector_display_name);
1168
1169 /* Bug 3195343 Getting the collector_employee_id , from which the
1170 user_id is obtained .Set this user_id as the APPROVER_ID. */
1171
1172 l_collector_employee_id := WF_ENGINE.GetItemAttrText(p_item_type,
1173 p_item_key,
1174 'COLLECTOR_EMPLOYEE_ID');
1175 Open c1;
1176 Fetch c1 into l_collector_user_id ;
1177 Close c1;
1178
1179 WF_ENGINE.SetItemAttrText(p_item_type,
1180 p_item_key,
1181 'APPROVER_ID',
1182 l_collector_user_id);
1183
1184 /* Bug 991922 : check if message body needs to say rule is required */
1185
1186 SELECT invoicing_rule_id
1187 INTO l_invoicing_rule_id
1188 FROM ra_customer_trx
1189 WHERE customer_trx_id = l_customer_trx_id;
1190
1191 if l_invoicing_rule_id is not NULL then
1192
1193 if nvl(l_credit_accounting_rule,'*') not in ('LIFO','PRORATE','UNIT') then
1194
1195 fnd_message.set_name('AR', 'ARW_NEED_RULE');
1196 l_need_rule_mesg := fnd_message.get;
1197
1198 WF_ENGINE.SetItemAttrText(p_item_type,
1199 p_item_key,
1200 'INVALID_RULE_MESG',
1201 l_need_rule_mesg);
1202 end if;
1203 end if;
1204
1205 p_result := 'COMPLETE:T';
1206 return;
1207 END IF;
1208 end if;
1209
1210 end if; -- end of run mode
1211
1212 --
1213 -- CANCEL mode
1214 --
1215
1216 if (p_funcmode = 'CANCEL') then
1217
1218 -- no result needed
1219 p_result := 'COMPLETE:';
1220 return;
1221 end if;
1222
1223
1224 --
1225 -- Other execution modes.
1226 --
1227 p_result := '';
1228 return;
1229
1230 exception
1231 when others then
1232 -- The line below records this function call in the error system
1233 -- in the case of an exception.
1234 wf_core.context('ARP_CMREQ_WF', 'DefaultSendTo',
1235 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
1236 raise;
1237
1238 end DefaultSendTo;
1239
1240
1241
1242 PROCEDURE CheckPrimaryApprover(p_item_type IN VARCHAR2,
1243 p_item_key IN VARCHAR2,
1247
1244 p_actid IN NUMBER,
1245 p_funcmode IN VARCHAR2,
1246 p_result OUT NOCOPY VARCHAR2) IS
1248
1249 l_debug_mesg varchar2(240);
1250
1251 l_approver_name varchar2(50);
1252 l_approver_user_name varchar2(30);
1253 l_employee_id number;
1254 l_reason_code varchar2(45);
1255 l_currency_code varchar2(30);
1256 l_primary_flag varchar2(1);
1257
1258 begin
1259 -- SetOrgContext (p_item_key);
1260
1261 --
1262 -- RUN mode - normal process execution
1263 --
1264 if (p_funcmode = 'RUN') then
1265
1266 ------------------------------------------------------------
1267 l_debug_mesg := 'Get the user name of selected role';
1268 ------------------------------------------------------------
1269
1270 l_approver_name := WF_ENGINE.GetItemAttrText(p_item_type,
1271 p_item_key,
1272 'ROLE');
1273
1274 IF l_approver_name IS NULL THEN
1275 p_result := 'COMPLETE:N';
1276 RETURN;
1277 END IF;
1278
1279 l_reason_code := WF_ENGINE.GetItemAttrText(
1280 p_item_type,
1281 p_item_key,
1282 'REASON');
1283
1284 l_currency_code := WF_ENGINE.GetItemAttrText(
1285 p_item_type,
1286 p_item_key,
1287 'CURRENCY_CODE');
1288
1289 SELECT user_id INTO l_employee_id
1290 FROM fnd_user
1291 WHERE user_name = l_approver_name;
1292
1293 SELECT primary_flag INTO l_primary_flag
1294 FROM ar_approval_user_limits aul
1295 WHERE reason_code = l_reason_code
1296 AND currency_code = l_currency_code
1297 AND user_id = l_employee_id;
1298
1299 if l_primary_flag = 'Y' THEN
1300 p_result := 'COMPLETE:T';
1301 else
1302 p_result := 'COMPLETE:F';
1303 end if;
1304
1305 return;
1306
1307 end if; -- end of run mode
1308
1309 --
1310 -- CANCEL mode
1311 --
1312 -- This is an event point is called with the effect of the activity must
1313 -- be undone, for example when a process is reset to an earlier point
1314 -- due to a loop back.
1315 --
1316 if (p_funcmode = 'CANCEL') then
1317
1318 -- no result needed
1319 p_result := 'COMPLETE:';
1320 return;
1321 end if;
1322
1323
1324 --
1325 -- Other execution modes may be created in the future. Your
1326 -- activity will indicate that it does not implement a mode
1327 -- by returning null
1328 --
1329 p_result := '';
1330 return;
1331
1332 exception
1333 when no_data_found then
1334 p_result := 'COMPLETE:N';
1335 return;
1336 when others then
1337 -- The line below records this function call in the error system
1338 -- in the case of an exception.
1339 wf_core.context('ARP_CMREQ_WF', 'CheckPrimaryApprover',
1340 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
1341 raise;
1342
1343 end CheckPrimaryApprover;
1344
1345 PROCEDURE FindPrimaryApprover(p_item_type IN VARCHAR2,
1346 p_item_key IN VARCHAR2,
1347 p_actid IN NUMBER,
1348 p_funcmode IN VARCHAR2,
1349 p_result OUT NOCOPY VARCHAR2) IS
1350
1351 l_debug_mesg varchar2(240);
1352 l_approver_count number;
1353 l_reason_code varchar2(45);
1354 l_currency_code varchar2(30);
1355 l_first_approver_id number;
1356 l_approver_id number;
1357 l_count number;
1358
1359 begin
1360 -- SetOrgContext (p_item_key);
1361 --
1362 -- RUN mode - normal process execution
1363 --
1364 if (p_funcmode = 'RUN') then
1365
1366 l_reason_code := WF_ENGINE.GetItemAttrText(
1367 p_item_type,
1368 p_item_key,
1369 'REASON');
1370
1371 l_currency_code := WF_ENGINE.GetItemAttrText(
1372 p_item_type,
1373 p_item_key,
1374 'CURRENCY_CODE');
1375
1376
1377 l_approver_count := WF_ENGINE.GetItemAttrNumber(
1378 p_item_type,
1379 p_item_key,
1380 'FIND_APPROVER_COUNT');
1381
1382
1383 if l_approver_count = 0 then
1384
1385 SelectFirstPrimaryApproverId(l_reason_code,
1386 l_currency_code,
1387 l_first_approver_id);
1388
1389 if l_first_approver_id = -1 then
1390
1391 -----------------------------------------
1395 return;
1392 l_debug_mesg := 'No first approver found';
1393 ------------------------------------------
1394 p_result := 'COMPLETE:F';
1396
1397 else
1398 -- found the first approver
1399 l_count := 1;
1400
1401 WF_ENGINE.SetItemAttrNumber(p_item_type,
1402 p_item_key,
1403 'FIND_APPROVER_COUNT',
1404 l_count);
1405
1406
1407 -- set info for the first approver
1408
1409 GetEmployeeInfo(l_first_approver_id,
1410 p_item_type,
1411 p_item_key,
1412 'Y');
1413
1414 end if; -- end of if l_first_approver_id = -1
1415
1416
1417 else
1418
1419 -- Increase the Approver Counter
1420 l_approver_count := l_approver_count + 1 ;
1421
1422
1423 SelectPrimaryApproverId(l_reason_code,
1424 l_currency_code,
1425 l_approver_count,
1426 l_approver_id);
1427
1428
1429
1430 if l_approver_id = -1 then
1431 -----------------------------------------
1432 l_debug_mesg := 'No approver found';
1433 ------------------------------------------
1434 p_result := 'COMPLETE:F';
1435 return;
1436 else
1437
1438 WF_ENGINE.SetItemAttrNumber(p_item_type,
1439 p_item_key,
1440 'FIND_APPROVER_COUNT',
1441 l_approver_count);
1442
1443 -- set info for the approver
1444
1445 GetEmployeeInfo(l_approver_id,
1446 p_item_type,
1447 p_item_key,
1448 'Y');
1449
1450
1451 end if; -- if l_approver_id = -1
1452
1453
1454
1455 end if; -- if l_approver_count = 0
1456
1457
1458
1459 p_result := 'COMPLETE:T';
1460 return;
1461
1462 end if; -- end of run mode
1463
1464 --
1465 -- CANCEL mode
1466 --
1467
1468 if (p_funcmode = 'CANCEL') then
1469
1470 -- no result needed
1471 p_result := 'COMPLETE:';
1472 return;
1473 end if;
1474
1475
1476 --
1477 -- Other execution modes.
1478 --
1479 p_result := '';
1480 return;
1481
1482 exception
1483 when others then
1484 -- The line below records this function call in the error system
1485 -- in the case of an exception.
1486 wf_core.context('ARP_CMREQ_WF', 'FindPrimaryApprover',
1487 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
1488 raise;
1489
1490 end FindPrimaryApprover;
1491
1492 PROCEDURE FindNonPrimaryApprover(p_item_type IN VARCHAR2,
1493 p_item_key IN VARCHAR2,
1494 p_actid IN NUMBER,
1495 p_funcmode IN VARCHAR2,
1496 p_result OUT NOCOPY VARCHAR2) IS
1497
1498 l_debug_mesg varchar2(240);
1499 l_approver_id number;
1500 l_employee_id number;
1501 l_approver_display_name varchar2(50);
1502 l_approver_user_name varchar2(30);
1503 l_count number;
1504
1505
1506 begin
1507 -- SetOrgContext (p_item_key);
1508
1509 --
1510 -- RUN mode - normal process execution
1511 --
1512 if (p_funcmode = 'RUN') then
1513
1514 ------------------------------------------------------------
1515 l_debug_mesg := 'Retreiving info for Non-Primary Approver';
1516 ------------------------------------------------------------
1517
1518 l_approver_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
1519 p_item_key,
1520 'ROLE');
1521
1522 select employee_id, user_id into l_employee_id, l_approver_id
1523 from fnd_user
1524 where user_name = l_approver_user_name;
1525
1526 l_count := 0;
1527
1528 CheckUserInHR(l_employee_id,
1529 l_count);
1530
1531 if l_count =0 THEN
1532 p_result := 'COMPLETE:F';
1533 else
1534 p_result := 'COMPLETE:T';
1535 end if;
1536
1537 if p_result = 'COMPLETE:T'THEN
1538 GetEmployeeInfo(l_approver_id,
1539 p_item_type,
1540 p_item_key,
1541 'N');
1542 end if;
1543
1544 return;
1545
1546 end if; -- end of run mode
1547
1548 --
1549 -- CANCEL mode
1550 --
1551 -- This is an event point is called with the effect of the activity must
1552 -- be undone, for example when a process is reset to an earlier point
1553 -- due to a loop back.
1554 --
1555 if (p_funcmode = 'CANCEL') then
1556
1557 -- no result needed
1558 p_result := 'COMPLETE:';
1559 return;
1560 end if;
1561
1562
1563 --
1567 --
1564 -- Other execution modes may be created in the future. Your
1565 -- activity will indicate that it does not implement a mode
1566 -- by returning null
1568 p_result := '';
1569 return;
1570
1571 exception
1572 when others then
1573 -- The line below records this function call in the error system
1574 -- in the case of an exception.
1575 wf_core.context('ARP_CMREQ_WF', 'FindNonPrimaryApprover',
1576 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
1577 raise;
1578
1579 end FindNonPrimaryApprover;
1580
1581 PROCEDURE FindNextNonPrimaryApprover ( p_item_type IN VARCHAR2,
1582 p_item_key IN VARCHAR2,
1583 p_actid IN NUMBER,
1584 p_funcmode IN VARCHAR2,
1585 p_result OUT NOCOPY VARCHAR2) IS
1586
1587 l_debug_info VARCHAR2(200);
1588 l_approver_id number;
1589 l_approver_display_name varchar2(50);
1590 l_approver_user_name varchar2(30);
1591 l_count number;
1592
1593 l_employee_id number;
1594 l_supervisor_emp_id number;
1595
1596 begin
1597 -- SetOrgContext (p_item_key);
1598 --
1599 -- RUN mode - normal process execution
1600 --
1601 if (p_funcmode = 'RUN') then
1602
1603 ------------------------------------------------------------
1604 l_debug_info := 'Retreiving info for Next Non-Primary Approver';
1605 ------------------------------------------------------------
1606
1607 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1608 p_item_key,
1609 'APPROVER_ID');
1610 BEGIN
1611 select employee_id into l_employee_id
1612 from fnd_user
1613 where user_id = l_approver_id;
1614 EXCEPTION WHEN NO_DATA_FOUND THEN
1615 p_result := 'COMPLETE:NE';
1616 return;
1617 END;
1618
1619 BEGIN
1620 SELECT hremp.supervisor_id
1621 INTO l_supervisor_emp_id
1622 FROM per_all_assignments_f hremp
1623 WHERE hremp.person_id = l_employee_id
1624 AND primary_flag = 'Y' -- get primary assgt
1625 AND assignment_type = 'E' -- ensure emp assgt, not applicant assgt
1626 AND trunc(sysdate) BETWEEN hremp.effective_start_date AND
1627 hremp.effective_end_date ;
1628
1629 EXCEPTION WHEN NO_DATA_FOUND THEN
1630 p_result := 'COMPLETE:NH';
1631 RETURN;
1632 END;
1633
1634
1635 BEGIN
1636 select user_id into l_approver_id
1637 from fnd_user
1638 where employee_id = l_supervisor_emp_id;
1639 EXCEPTION WHEN NO_DATA_FOUND THEN
1640 p_result := 'COMPLETE:NU';
1641 return;
1642 END;
1643
1644
1645 CheckUserInTable(p_item_type,
1646 p_item_key,
1647 l_approver_id,
1648 'N',
1649 l_count);
1650
1651 if l_count =0 THEN
1652 p_result := 'COMPLETE:NA';
1653 else
1654 p_result := 'COMPLETE:Y';
1655 end if;
1656
1657 if p_result = 'COMPLETE:Y'THEN
1658 GetEmployeeInfo(l_approver_id,
1659 p_item_type,
1660 p_item_key,
1661 'N');
1662 end if;
1663
1664 return;
1665
1666 end if; -- end of run mode
1667
1668 --
1669 -- CANCEL mode
1670 --
1671 -- This is an event point is called with the effect of the activity must
1672 -- be undone, for example when a process is reset to an earlier point
1673 -- due to a loop back.
1674 --
1675 if (p_funcmode = 'CANCEL') then
1676
1677 -- no result needed
1678 p_result := 'COMPLETE:';
1679 return;
1680 end if;
1681
1682
1683 --
1684 -- Other execution modes may be created in the future. Your
1685 -- activity will indicate that it does not implement a mode
1686 -- by returning null
1687 --
1688 p_result := '';
1689 return;
1690
1691 exception
1692 when others then
1693 -- The line below records this function call in the error system
1694 -- in the case of an exception.
1695 wf_core.context('ARP_CMREQ_WF', 'FindNextNonPrimaryApprover',
1696 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
1697 raise;
1698
1699 end FindNextNonPrimaryApprover;
1700
1701 PROCEDURE CheckUserInHR( p_employee_id IN NUMBER,
1702 p_count OUT NOCOPY NUMBER) IS
1703
1704 l_debug_mesg varchar2(240);
1705 l_reason_code varchar2(45);
1706 l_currency_code varchar2(30);
1707
1708 cursor c1 is
1709 select count(*)
1710 from per_all_people_f
1711 where person_id=p_employee_id;
1712
1713 begin
1714
1715 ------------------------------------------------------------
1716 l_debug_mesg := 'Checking if User exists in HR Table';
1717 ------------------------------------------------------------
1718
1719 open c1;
1720 fetch c1 into p_count;
1724
1721 close c1;
1722
1723 return;
1725 exception
1726 when others then
1727 -- The line below records this function call in the error system
1728 -- in the case of an exception.
1729 wf_core.context('ARP_CMREQ_WF', 'CheckUserInHR',
1730 null, null, null, l_debug_mesg);
1731 raise;
1732
1733 end CheckUserInHR;
1734
1735
1736
1737 PROCEDURE CheckUserInTable(p_item_type IN VARCHAR2,
1738 p_item_key IN VARCHAR2,
1739 p_employee_id IN NUMBER,
1740 p_primary_flag IN VARCHAR2,
1741 p_count OUT NOCOPY NUMBER) IS
1742
1743 l_debug_mesg varchar2(240);
1744 l_reason_code varchar2(45);
1745 l_currency_code varchar2(30);
1746
1747 cursor c1 is
1748 select count(*)
1749 from ar_approval_user_limits aul
1750 where aul.reason_code = l_reason_code
1751 and aul.currency_code = l_currency_code
1752 and aul.primary_flag = p_primary_flag
1753 and user_id = p_employee_id
1754 order by - aul.amount_from;
1755
1756 begin
1757 -- SetOrgContext (p_item_key);
1758
1759 ------------------------------------------------------------
1760 l_debug_mesg := 'Checking if User exists in Approval Limits Table';
1761 ------------------------------------------------------------
1762
1763 l_reason_code := WF_ENGINE.GetItemAttrText(
1764 p_item_type,
1765 p_item_key,
1766 'REASON');
1767
1768 l_currency_code := WF_ENGINE.GetItemAttrText(
1769 p_item_type,
1770 p_item_key,
1771 'CURRENCY_CODE');
1772
1773 open c1;
1774 fetch c1 into p_count;
1775 close c1;
1776
1777 return;
1778
1779 exception
1780 when others then
1781 -- The line below records this function call in the error system
1782 -- in the case of an exception.
1783 wf_core.context('ARP_CMREQ_WF', 'CheckUserInTable',
1784 p_item_type, p_item_key, null, l_debug_mesg);
1785 raise;
1786
1787 end CheckUserInTable;
1788
1789 PROCEDURE SelectFirstPrimaryApproverId(p_reason_code IN VARCHAR2,
1790 p_currency_code IN VARCHAR2,
1791 p_approver_employee_id OUT NOCOPY NUMBER) IS
1792
1793
1794 cursor c1 is
1795 select aul.user_id
1796 from ar_approval_user_limits aul
1797 where aul.reason_code = p_reason_code
1798 and aul.currency_code = p_currency_code
1799 and aul.primary_flag = 'Y'
1800 order by - aul.amount_from;
1801
1802 l_debug_mesg varchar2(240);
1803
1804 j Number := 1;
1805 approver_id number;
1806
1807 BEGIN
1808 ----------------------------------------------------------------------------
1809 l_debug_mesg := 'Select first employee_id with lowest dollar value';
1810 ---------------------------------------------------------------------------
1811
1812 -- Populate value from cursor
1813 open c1;
1814 loop
1815 fetch c1 into approver_id;
1816
1817 IF c1%notfound THEN
1818 p_approver_employee_id := -1 ;
1819 EXIT;
1820 END IF;
1821
1822 if j = 1 then
1823 p_approver_employee_id := approver_id ;
1824 exit;
1825 end if;
1826
1827 j := j + 1;
1828
1829 end loop;
1830 close c1;
1831
1832 null;
1833
1834 EXCEPTION
1835 WHEN NO_DATA_FOUND then
1836 p_approver_employee_id := -1 ;
1837 WHEN OTHERS THEN
1838 Wf_Core.Context('AR_CMREQ_WF', 'SelectFirstPrimaryApproverId',
1839 null, null, null, l_debug_mesg);
1840 raise;
1841 END SelectFirstPrimaryApproverId;
1842
1843
1844 PROCEDURE SelectPrimaryApproverId(p_reason_code IN VARCHAR2,
1845 p_currency_code IN VARCHAR2,
1846 p_approver_count IN NUMBER,
1847 p_approver_employee_id OUT NOCOPY NUMBER) IS
1848
1849 cursor c1 is
1850 select aul.user_id
1851 from ar_approval_user_limits aul
1852 where aul.reason_code = p_reason_code
1853 and aul.currency_code = p_currency_code
1854 and aul.primary_flag = 'Y'
1855 order by - aul.amount_from;
1856
1857
1858 l_debug_mesg varchar2(240);
1859 approver_id number;
1860 i number;
1861
1862 BEGIN
1863
1864 -----------------------------------------------------------------------------
1865 l_debug_mesg := 'Select employee_id with dollar value larger than previous one';
1866 ------------------------------------------------------------------------------
1867 -- initialize the number
1868 i := 1 ;
1869
1870 -- find the approver id
1871 open c1;
1872 loop
1873 fetch c1 into approver_id;
1874
1875 IF c1%notfound THEN
1879
1876 p_approver_employee_id := -1 ;
1877 EXIT;
1878 END IF;
1880 if i = p_approver_count then
1881 p_approver_employee_id := approver_id ;
1882 i := i + 1 ;
1883 exit;
1884 end if;
1885
1886 i := i + 1;
1887
1888 end loop;
1889 close c1;
1890
1891
1892 EXCEPTION
1893 WHEN NO_DATA_FOUND then
1894 p_approver_employee_id := -1 ;
1895 WHEN OTHERS THEN
1896 Wf_Core.Context('AR_CMREQ_WF', 'SelectPrimaryApproverId',
1897 null, null, null, l_debug_mesg);
1898 raise;
1899
1900 END SelectPrimaryApproverId;
1901
1902 PROCEDURE GetEmployeeInfo(
1903 p_user_id in number,
1904 p_item_type in varchar2,
1905 p_item_key in varchar2,
1906 p_primary_approver_flag in varchar2) IS
1907
1908 l_debug_mesg varchar2(240);
1909 l_approver_user_name varchar2(100);
1910 l_approver_display_name varchar2(240);
1911 l_manager_name varchar2(100);
1912 l_manager_display_name varchar(240);
1913
1914
1915 BEGIN
1916
1917 -------------------------------------------------------------------
1918 l_debug_mesg := 'Trying to get employee information';
1919 -------------------------------------------------------------------
1920
1921 -- set username and display name for a primary approver
1922
1923 if ( p_primary_approver_flag = 'Y') then
1924
1925 GetUserInfoFromTable(
1926 p_user_id,
1927 p_primary_approver_flag,
1928 l_approver_user_name,
1929 l_approver_display_name);
1930
1931 IF l_approver_user_name IS NOT NULL THEN
1932
1933 WF_ENGINE.SetItemAttrNumber(p_item_type,
1934 p_item_key,
1935 'APPROVER_ID',
1936 p_user_id);
1937
1938 WF_ENGINE.SetItemAttrText(p_item_type,
1939 p_item_key,
1940 'APPROVER_USER_NAME',
1941 l_approver_user_name);
1942
1943 WF_ENGINE.SetItemAttrText(p_item_type,
1944 p_item_key,
1945 'APPROVER_DISPLAY_NAME',
1946 l_approver_display_name);
1947 END IF;
1948
1949 else
1950
1951 -- set username and display name for a manager
1952 GetUserInfoFromTable(
1953 p_user_id,
1954 p_primary_approver_flag,
1955 l_manager_name,
1956 l_manager_display_name);
1957
1958 IF l_manager_name IS NOT NULL THEN
1959
1960 WF_ENGINE.SetItemAttrNumber(p_item_type,
1961 p_item_key,
1962 'MANAGER_ID',
1963 p_user_id);
1964
1965 WF_ENGINE.SetItemAttrText(p_item_type,
1966 p_item_key,
1967 'MANAGER_USER_NAME',
1968 l_manager_name);
1969
1970 WF_ENGINE.SetItemAttrText(p_item_type,
1971 p_item_key,
1972 'MANAGER_DISPLAY_NAME',
1973 l_manager_display_name);
1974
1975 WF_ENGINE.SetItemAttrNumber(p_item_type,
1976 p_item_key,
1977 'APPROVER_ID',
1978 p_user_id);
1979
1980 WF_ENGINE.SetItemAttrText(p_item_type,
1981 p_item_key,
1982 'APPROVER_USER_NAME',
1983 l_manager_name);
1984
1985 WF_ENGINE.SetItemAttrText(p_item_type,
1986 p_item_key,
1987 'APPROVER_DISPLAY_NAME',
1988 l_manager_display_name);
1989
1990 END IF;
1991
1992 end if;
1993
1994 EXCEPTION
1995 WHEN OTHERS THEN
1996 Wf_Core.Context('ARP_CMREQ_WF', 'GetEmployeeInfo',
1997 p_item_type, p_item_key, null, l_debug_mesg);
1998 raise;
1999 END GetEmployeeInfo;
2000
2001
2002 PROCEDURE GetUserInfoFromTable(p_user_id IN NUMBER,
2003 p_primary_approver_flag IN VARCHAR2,
2004 p_user_name OUT NOCOPY VARCHAR2,
2005 p_display_name OUT NOCOPY VARCHAR2) IS
2006
2007 l_debug_mesg varchar2(240);
2008 l_employee_id number;
2009 l_user_name varchar2(100);
2010 l_display_name varchar2(240);
2011
2012 BEGIN
2013
2014 ----------------------------------------------------------------
2015 l_debug_mesg := 'Get user info for an employee';
2016 ----------------------------------------------------------------
2017
2018 -- can not use default WF_DIRECTORY.GetUserName to get user info
2022 -- need to write a sql function to get user info.
2019 -- because it can get user name which is not defined in limits
2020 -- table, the reason is multiply user names have been defined for
2021 -- such employee and the first one is returned by GetUserName.
2023
2024 -- select user_id for a primary approver
2025
2026 SELECT employee_id
2027 INTO l_employee_id
2028 FROM fnd_user
2029 WHERE user_id = p_user_id;
2030
2031
2032 select wu.name, wu.display_name
2033 into p_user_name, p_display_name
2034 from wf_users wu, fnd_user fu
2035 where wu.orig_system = 'PER'
2036 and wu.orig_system_id = l_employee_id
2037 and wu.orig_system_id = fu.employee_id
2038 and fu.user_id = p_user_id
2039 and fu.user_name = wu.name;
2040
2041 EXCEPTION
2042 WHEN NO_DATA_FOUND THEN
2043 p_user_name := NULL ;
2044 p_display_name := NULL ;
2045
2046 WHEN OTHERS THEN
2047 wf_core.Context('ARP_CMREQ_WF', 'GetUserInfoFromTable',
2048 null, null, null, l_debug_mesg);
2049 raise;
2050
2051 END GetUserInfoFromTable;
2052
2053 PROCEDURE FindManager (p_item_type IN VARCHAR2,
2054 p_item_key IN VARCHAR2,
2055 p_actid IN NUMBER,
2056 p_funcmode IN VARCHAR2,
2057 p_result OUT NOCOPY VARCHAR2) IS
2058
2059 l_debug_info VARCHAR2(200);
2060 l_employee_id number;
2061 l_manager_id number;
2062 l_manager_user_name varchar2(100);
2063 l_manager_display_name varchar2(240);
2064 l_escalation_count number;
2065 /* Bug 3195343 */
2066 l_approver_id number;
2067 l_manager_user_id number;
2068 Cursor c1 is
2069 SELECT employee_id
2070 FROM fnd_user
2071 WHERE user_id = l_approver_id;
2072 Cursor c2 is
2073 Select user_id
2074 From fnd_user
2075 Where employee_id = l_manager_id ;
2076 BEGIN
2077 -- SetOrgContext (p_item_key);
2078 --
2079 -- RUN mode - normal process execution
2080 --
2081 if (p_funcmode = 'RUN') then
2082
2083 -------------------------------------------------------
2084 l_debug_info := 'Trying to retrieve employee manager';
2085 -------------------------------------------------------
2086
2087 l_escalation_count := WF_ENGINE.GetItemAttrNumber(
2088 p_item_type,
2089 p_item_key,
2090 'ESCALATION_COUNT');
2091
2092 /* Bug 3195343 Changes l_employee_id to l_approver_id */
2093 IF l_escalation_count=0 THEN
2094
2095 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2096 p_item_key,
2097 'APPROVER_ID');
2098 ELSE
2099
2100 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2101 p_item_key,
2102 'MANAGER_ID');
2103
2104 END IF;
2105
2106 /* Bug 3195343 Added the following query to get the employee_id
2107 for a particular user_id.*/
2108 Open c1;
2109 Fetch c1 into l_employee_id;
2110 Close c1;
2111
2112 SELECT hremp.supervisor_id
2113 INTO l_manager_id
2114 FROM per_all_assignments_f hremp
2115 WHERE hremp.person_id = l_employee_id
2116 AND primary_flag = 'Y' -- get primary assgt
2117 AND assignment_type = 'E' -- ensure emp assgt, not applicant assgt
2118 AND trunc(sysdate) BETWEEN hremp.effective_start_date AND
2119 hremp.effective_end_date;
2120
2121 p_result := 'COMPLETE:T';
2122
2123 l_escalation_count := l_escalation_count + 1;
2124
2125 WF_ENGINE.SetItemAttrNumber( p_item_type,
2126 p_item_key,
2127 'ESCALATION_COUNT',
2128 l_escalation_count);
2129
2130 /*Bug 3195343 Retrieving user_id to be stored in the attribute
2131 MANAGER_ID */
2132 Open c2;
2133 Fetch c2 into l_manager_user_id ;
2134 Close c2;
2135
2136 WF_ENGINE.SetItemAttrNumber(p_item_type,
2137 p_item_key,
2138 'MANAGER_ID',
2139 l_manager_user_id);
2140
2141 WF_DIRECTORY.GetUserName('PER',
2142 to_char(l_manager_id),
2143 l_manager_user_name,
2144 l_manager_display_name);
2145
2146 WF_ENGINE.SetItemAttrText(p_item_type,
2147 p_item_key,
2148 'MANAGER_USER_NAME',
2149 l_manager_user_name);
2150
2151 WF_ENGINE.SetItemAttrText(p_item_type,
2152 p_item_key,
2153 'MANAGER_DISPLAY_NAME',
2154 l_manager_display_name);
2155
2156 return;
2157
2158 end if; -- end of run mode
2159
2160 --
2161 -- CANCEL mode
2162 --
2166 --
2163 -- This is an event point is called with the effect of the activity must
2164 -- be undone, for example when a process is reset to an earlier point
2165 -- due to a loop back.
2167 if (p_funcmode = 'CANCEL') then
2168
2169 -- no result needed
2170 p_result := 'COMPLETE:';
2171 return;
2172 end if;
2173
2174
2175 --
2176 -- Other execution modes may be created in the future. Your
2177 -- activity will indicate that it does not implement a mode
2178 -- by returning null
2179 --
2180 p_result := '';
2181 return;
2182
2183
2184 EXCEPTION
2185 WHEN NO_DATA_FOUND THEN
2186 l_manager_id := NULL;
2187 p_result := 'COMPLETE:F';
2188 return;
2189 WHEN OTHERS THEN
2190 Wf_Core.Context('ARP_CMREQ_WF', 'FindManager',
2191 null, null, null, l_debug_info);
2192 raise;
2193 END FindManager;
2194
2195 PROCEDURE RecordCollectorAsApprover(p_item_type IN VARCHAR2,
2196 p_item_key IN VARCHAR2,
2197 p_actid IN NUMBER,
2198 p_funcmode IN VARCHAR2,
2199 p_result OUT NOCOPY VARCHAR2) IS
2200
2201 l_debug_mesg varchar2(240);
2202 l_collector_employee_id number;
2203 l_collector_display_name varchar2(240);
2204 l_collector_user_name varchar2(100);
2205 /* Bug 3195343 */
2206 l_collector_user_id number;
2207 Cursor c1 is
2208 Select user_id
2209 From fnd_user
2210 Where employee_id = l_collector_employee_id;
2211
2212 begin
2213
2214 -- SetOrgContext (p_item_key);
2215
2216 --
2217 -- RUN mode - normal process execution
2218 --
2219 if (p_funcmode = 'RUN') then
2220
2221 ------------------------------------------------------------
2222 l_debug_mesg := 'Record collector as approver';
2223 ------------------------------------------------------------
2224
2225
2226 l_collector_employee_id := WF_ENGINE.GetItemAttrNumber(
2227 p_item_type,
2228 p_item_key,
2229 'COLLECTOR_EMPLOYEE_ID');
2230
2231 /* Bug 3195343 Retrieving user_id from employee_id. */
2232 Open c1;
2233 Fetch c1 into l_collector_user_id;
2234 Close c1;
2235
2236 WF_ENGINE.SetItemAttrNumber(p_item_type,
2237 p_item_key,
2238 'APPROVER_ID',
2239 l_collector_user_id);
2240
2241 l_collector_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
2242 p_item_key,
2243 'COLLECTOR_USER_NAME');
2244 WF_ENGINE.SetItemAttrText(p_item_type,
2245 p_item_key,
2246 'APPROVER_USER_NAME',
2247 l_collector_user_name);
2248
2249
2250 l_collector_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
2251 p_item_key,
2252 'COLLECTOR_DISPLAY_NAME');
2253 WF_ENGINE.SetItemAttrText(p_item_type,
2254 p_item_key,
2255 'APPROVER_DISPLAY_NAME',
2256 l_collector_display_name);
2257
2258
2259
2260 p_result := 'COMPLETE:T';
2261 return;
2262
2263 end if; -- end of run mode
2264
2265 --
2266 -- CANCEL mode
2267 --
2268
2269 if (p_funcmode = 'CANCEL') then
2270
2271 -- no result needed
2272 p_result := 'COMPLETE:';
2273 return;
2274 end if;
2275
2276
2277 --
2278 -- Other execution modes
2279 --
2280 p_result := '';
2281 return;
2282
2283 exception
2284 when others then
2285 -- The line below records this function call in the error system
2286 -- in the case of an exception.
2287 wf_core.context('ARP_CMREQ_WF', 'RecordCollectorAsApprover',
2288 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
2289 raise;
2290
2291 end RecordCollectorAsApprover;
2292
2293
2294
2295 PROCEDURE RecordCollectorAsForwardFrom(p_item_type IN VARCHAR2,
2296 p_item_key IN VARCHAR2,
2297 p_actid IN NUMBER,
2298 p_funcmode IN VARCHAR2,
2299 p_result OUT NOCOPY VARCHAR2) IS
2300
2301
2302 l_debug_mesg varchar2(240);
2303 l_collector_employee_id number;
2304 l_collector_display_name varchar2(240);
2305 l_collector_user_name varchar2(100);
2306 l_notes varchar2(240);
2307 l_approver_notes varchar2(100);
2308 CRLF varchar2(1);
2309
2313
2310 begin
2311
2312 -- SetOrgContext (p_item_key);
2314 -- Bug 2105483 : rather then calling arp_global at the start
2315 -- of the package, where it can error out NOCOPY since org_id is not yet set,
2316 -- do the call right before it is needed
2317 arp_global.init_global;
2318 CRLF := arp_global.CRLF;
2319
2320 --
2321 -- RUN mode - normal process execution
2322 --
2323 if (p_funcmode = 'RUN') then
2324
2325 ------------------------------------------------------------
2326 l_debug_mesg := 'Get the user name of collector';
2327 ------------------------------------------------------------
2328
2329
2330 l_collector_employee_id := WF_ENGINE.GetItemAttrNumber(
2331 p_item_type,
2332 p_item_key,
2333 'COLLECTOR_EMPLOYEE_ID');
2334 WF_ENGINE.SetItemAttrNumber(p_item_type,
2335 p_item_key,
2336 'FORWARD_FROM_ID',
2337 l_collector_employee_id);
2338
2339
2340
2341
2342
2343 l_collector_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
2344 p_item_key,
2345 'COLLECTOR_USER_NAME');
2346 WF_ENGINE.SetItemAttrText(p_item_type,
2347 p_item_key,
2348 'FORWARD_FROM_USER_NAME',
2349 l_collector_user_name);
2350
2351
2352 l_collector_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
2353 p_item_key,
2354 'COLLECTOR_DISPLAY_NAME');
2355 WF_ENGINE.SetItemAttrText(p_item_type,
2356 p_item_key,
2357 'FORWARD_FROM_DISPLAY_NAME',
2358 l_collector_display_name);
2359
2360 -- Add the collector user name in front of notes field.
2361
2362
2363
2364 l_approver_notes := WF_ENGINE.GetItemAttrText(p_item_type,
2365 p_item_key,
2366 'APPROVER_NOTES');
2367
2368 l_notes := l_collector_user_name ||
2369 ': ' || l_approver_notes || CRLF;
2370
2371
2372 WF_ENGINE.SetItemAttrText(p_item_type,
2373 p_item_key,
2374 'NOTES',
2375 l_notes);
2376
2377 -- Initialize the approver_notes
2378
2379 l_approver_notes := NULL;
2380
2381 WF_ENGINE.SetItemAttrText(p_item_type,
2382 p_item_key,
2383 'APPROVER_NOTES',
2384 l_approver_notes);
2385
2386
2387 p_result := 'COMPLETE:T';
2388 return;
2389
2390 end if; -- end of run mode
2391
2392 --
2393 -- CANCEL mode
2394 --
2395
2396 if (p_funcmode = 'CANCEL') then
2397
2398 -- no result needed
2399 p_result := 'COMPLETE:';
2400 return;
2401 end if;
2402
2403
2404 --
2405 -- Other execution modes
2406 --
2407 p_result := '';
2408 return;
2409
2410 exception
2411 when others then
2412 -- The line below records this function call in the error system
2413 -- in the case of an exception.
2414 wf_core.context('ARP_CMREQ_WF', 'RecordCollectorAsForwardFrom',
2415 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
2416 raise;
2417
2418 end RecordCollectorAsForwardFrom;
2419
2420
2421 PROCEDURE RecordForwardToUserInfo(p_item_type IN VARCHAR2,
2422 p_item_key IN VARCHAR2,
2423 p_actid IN NUMBER,
2424 p_funcmode IN VARCHAR2,
2425 p_result OUT NOCOPY VARCHAR2) IS
2426
2427
2428 l_debug_mesg varchar2(240);
2429 l_approver_id number;
2430 l_approver_display_name varchar2(240);
2431 l_approver_user_name varchar2(100);
2432
2433
2434
2435 begin
2436 -- SetOrgContext (p_item_key);
2437
2438 --
2439 -- RUN mode - normal process execution
2440 --
2441 if (p_funcmode = 'RUN') then
2442
2443 ------------------------------------------------------------
2444 l_debug_mesg := 'Get the user name of approver';
2445 ------------------------------------------------------------
2446
2447 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2448 p_item_key,
2449 'APPROVER_ID');
2450 WF_ENGINE.SetItemAttrNumber(p_item_type,
2451 p_item_key,
2452 'FORWARD_TO_ID',
2453 l_approver_id);
2454
2458 p_item_key,
2455
2456
2457 l_approver_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
2459 'APPROVER_USER_NAME');
2460 WF_ENGINE.SetItemAttrText(p_item_type,
2461 p_item_key,
2462 'FORWARD_TO_USER_NAME',
2463 l_approver_user_name);
2464
2465
2466 l_approver_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
2467 p_item_key,
2468 'APPROVER_DISPLAY_NAME');
2469 WF_ENGINE.SetItemAttrText(p_item_type,
2470 p_item_key,
2471 'FORWARD_TO_DISPLAY_NAME',
2472 l_approver_display_name);
2473
2474
2475
2476 p_result := 'COMPLETE:T';
2477 return;
2478
2479 end if; -- end of run mode
2480
2481 --
2482 -- CANCEL mode
2483 --
2484 -- This is an event point is called with the effect of the activity must
2485 -- be undone, for example when a process is reset to an earlier point
2486 -- due to a loop back.
2487 --
2488 if (p_funcmode = 'CANCEL') then
2489
2490 -- no result needed
2491 p_result := 'COMPLETE:';
2492 return;
2493 end if;
2494
2495
2496 --
2497 -- Other execution modes may be created in the future. Your
2498 -- activity will indicate that it does not implement a mode
2499 -- by returning null
2500 --
2501 p_result := '';
2502 return;
2503
2504 exception
2505 when others then
2506 -- The line below records this function call in the error system
2507 -- in the case of an exception.
2508 wf_core.context('ARP_CMREQ_WF', 'RecordForwardToUserInfo',
2509 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
2510 raise;
2511
2512 end RecordForwardToUserInfo;
2513
2514 PROCEDURE CheckForwardFromUser(p_item_type IN VARCHAR2,
2515 p_item_key IN VARCHAR2,
2516 p_actid IN NUMBER,
2517 p_funcmode IN VARCHAR2,
2518 p_result OUT NOCOPY VARCHAR2) IS
2519
2520 l_debug_mesg varchar2(240);
2521 l_forward_from_user_name varchar2(100);
2522
2523
2524
2525 begin
2526 -- SetOrgContext (p_item_key);
2527 --
2528 -- RUN mode - normal process execution
2529 --
2530 if (p_funcmode = 'RUN') then
2531
2532 ------------------------------------------------------------
2533 l_debug_mesg := 'Get the user name of forward from user';
2534 ------------------------------------------------------------
2535
2536 l_forward_from_user_name := WF_ENGINE.GetItemAttrText(
2537 p_item_type,
2538 p_item_key,
2539 'FORWARD_FROM_USER_NAME');
2540
2541
2542 if l_forward_from_user_name is not NULL then
2543 p_result := 'COMPLETE:T';
2544 return;
2545 else
2546 p_result := 'COMPLETE:F';
2547 return;
2548 end if;
2549
2550 end if; -- end of run mode
2551
2552 --
2553 -- CANCEL mode
2554 --
2555 --
2556 if (p_funcmode = 'CANCEL') then
2557
2558 -- no result needed
2559 p_result := 'COMPLETE:';
2560 return;
2561 end if;
2562
2563
2564 --
2565 -- Other execution mode
2566
2567 p_result := '';
2568 return;
2569
2570 exception
2571 when others then
2572 -- The line below records this function call in the error system
2573 -- in the case of an exception.
2574 wf_core.context('ARP_CMREQ_WF', 'CheckForwardFromUser',
2575 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
2576 raise;
2577
2578 end CheckForwardFromUser;
2579
2580 PROCEDURE RecordApproverAsForwardFrom(p_item_type IN VARCHAR2,
2581 p_item_key IN VARCHAR2,
2582 p_actid IN NUMBER,
2583 p_funcmode IN VARCHAR2,
2584 p_result OUT NOCOPY VARCHAR2) IS
2585
2586 l_debug_mesg varchar2(240);
2587 l_approver_id number;
2588 l_approver_user_name varchar2(100);
2589 l_approver_display_name varchar2(240);
2590 l_notes varchar2(240);
2591 l_approver_notes varchar2(100);
2592 CRLF varchar2(1);
2593
2594 begin
2595 SetOrgContext (p_item_key);
2596
2597 -- Bug 2105483 : rather then calling arp_global at the start
2598 -- of the package, where it can error out NOCOPY since org_id is not yet set,
2599 -- do the call right before it is needed
2600 arp_global.init_global;
2601 CRLF := arp_global.CRLF;
2602
2603 --
2604 -- RUN mode - normal process execution
2605 --
2609 l_debug_mesg := 'Get info for an approver';
2606 if (p_funcmode = 'RUN') then
2607
2608 ------------------------------------------------------------
2610 ------------------------------------------------------------
2611
2612
2613
2614 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2615 p_item_key,
2616 'APPROVER_ID');
2617 WF_ENGINE.SetItemAttrNumber(p_item_type,
2618 p_item_key,
2619 'FORWARD_FROM_ID',
2620 l_approver_id);
2621
2622
2623
2624 l_approver_user_name := WF_ENGINE.GetItemAttrText(p_item_type,
2625 p_item_key,
2626 'APPROVER_USER_NAME');
2627 WF_ENGINE.SetItemAttrText(p_item_type,
2628 p_item_key,
2629 'FORWARD_FROM_USER_NAME',
2630 l_approver_user_name);
2631
2632
2633 l_approver_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
2634 p_item_key,
2635 'APPROVER_DISPLAY_NAME');
2636 WF_ENGINE.SetItemAttrText(p_item_type,
2637 p_item_key,
2638 'FORWARD_FROM_DISPLAY_NAME',
2639 l_approver_display_name);
2640
2641 -- Add the approver user name in front of notes field.
2642
2643 l_notes := WF_ENGINE.GetItemAttrText(p_item_type,
2644 p_item_key,
2645 'NOTES');
2646
2647 l_approver_notes := WF_ENGINE.GetItemAttrText(p_item_type,
2648 p_item_key,
2649 'APPROVER_NOTES');
2650
2651
2652 l_notes := l_notes || l_approver_user_name ||
2653 ': ' || l_approver_notes || CRLF;
2654
2655
2656 WF_ENGINE.SetItemAttrText(p_item_type,
2657 p_item_key,
2658 'NOTES',
2659 l_notes);
2660
2661 -- Initialize the approver_notes
2662
2663 l_approver_notes := NULL;
2664
2665 WF_ENGINE.SetItemAttrText(p_item_type,
2666 p_item_key,
2667 'APPROVER_NOTES',
2668 l_approver_notes);
2669
2670
2671
2672 p_result := 'COMPLETE:T';
2673 return;
2674
2675 end if; -- end of run mode
2676
2677 --
2678 -- CANCEL mode
2679 --
2680
2681 if (p_funcmode = 'CANCEL') then
2682
2683 -- no result needed
2684 p_result := 'COMPLETE:';
2685 return;
2686 end if;
2687
2688
2689 --
2690 -- Other execution modes
2691 --
2692 p_result := '';
2693 return;
2694
2695 exception
2696 when others then
2697 -- The line below records this function call in the error system
2698 -- in the case of an exception.
2699 wf_core.context('ARP_CMREQ_WF', 'RecordApproverAsForwardFrom',
2700 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
2701 raise;
2702
2703 end RecordApproverAsForwardFrom;
2704
2705 PROCEDURE FinalApprover(p_item_type IN VARCHAR2,
2706 p_item_key IN VARCHAR2,
2707 p_actid IN NUMBER,
2708 p_funcmode IN VARCHAR2,
2709 p_result OUT NOCOPY VARCHAR2) IS
2710
2711
2712 l_debug_mesg varchar2(240);
2713 l_approver_id number;
2714 l_reason_code varchar2(45);
2715 l_currency_code varchar2(15);
2716 l_total_credit_to_invoice number;
2717 l_result_flag varchar2(1);
2718
2719 begin
2720 --uncommented for bug 5410467
2721 SetOrgContext (p_item_key);
2722 ---------------------------------------------------------
2723 l_debug_mesg := 'if approver is a final approver';
2724 ---------------------------------------------------------
2725
2726
2727 --
2728 -- RUN mode - normal process execution
2729 --
2730 if (p_funcmode = 'RUN') then
2731
2732 l_reason_code := WF_ENGINE.GetItemAttrText(
2733 p_item_type,
2734 p_item_key,
2735 'REASON');
2736
2737 l_currency_code := WF_ENGINE.GetItemAttrText(
2738 p_item_type,
2739 p_item_key,
2740 'CURRENCY_CODE');
2741
2742 l_total_credit_to_invoice:= WF_ENGINE.GetItemAttrNumber(
2743 p_item_type,
2744 p_item_key,
2748
2745 'TOTAL_CREDIT_TO_INVOICE');
2746
2747
2749 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2750 p_item_key,
2751 'APPROVER_ID');
2752 /*
2753 CheckFinalApprover(l_reason_code,
2754 l_currency_code,
2755 l_total_credit_to_invoice,
2756 l_approver_id,
2757 l_result_flag);
2758
2759
2760
2761 if (l_result_flag = 'Y') then
2762
2763 -- it is a final aprrover
2764 p_result := 'COMPLETE:T';
2765 return;
2766 else
2767 p_result := 'COMPLETE:F';
2768 return;
2769 end if;
2770 */
2771 --fix for 5410467
2772 p_result := 'COMPLETE:T';
2773 return;
2774 --fix for 5410467 ends here.
2775 end if; -- end of run mode
2776
2777 --
2778 -- CANCEL mode
2779 --
2780
2781 if (p_funcmode = 'CANCEL') then
2782
2783 -- no result needed
2784 p_result := 'COMPLETE:';
2785 return;
2786 end if;
2787
2788
2789 --
2790 -- Other execution modes
2791 --
2792 p_result := '';
2793 return;
2794
2795 exception
2796 when others then
2797 -- The line below records this function call in the error system
2798 -- in the case of an exception.
2799 wf_core.context('ARP_CMREQ_WF', 'FinalApprover',
2800 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
2801 raise;
2802
2803 end FinalApprover;
2804
2805 PROCEDURE CheckFinalApprover(p_reason_code IN VARCHAR2,
2806 p_currency_code IN VARCHAR2,
2807 p_amount IN VARCHAR2,
2808 p_approver_id IN NUMBER,
2809 p_result_flag OUT NOCOPY VARCHAR2) IS
2810
2811 l_debug_mesg varchar2(240);
2812 l_amount_to number;
2813 l_amount_from number;
2814
2815 begin
2816
2817 ---------------------------------------------------------------------------
2818 l_debug_mesg := 'Check if the selected approver is a final one';
2819 ---------------------------------------------------------------------------
2820
2821 select aul.amount_to, aul.amount_from
2822 into l_amount_to, l_amount_from
2823 from ar_approval_user_limits aul
2824 where aul.user_id = p_approver_id
2825 and aul.reason_code = p_reason_code
2826 and aul.currency_code = p_currency_code ;
2827
2828
2829 if ( ( p_amount < l_amount_to) and
2830 ( p_amount >= l_amount_from)) then
2831
2832 p_result_flag := 'Y';
2833 else
2834
2835 p_result_flag := 'N';
2836 end if ;
2837
2838 return;
2839
2840 EXCEPTION
2841 WHEN NO_DATA_FOUND THEN
2842 p_result_flag := 'N';
2843 return;
2844 WHEN OTHERS THEN
2845 wf_core.Context('ARP_CMREQ_WF', 'CheckFinalApprover',
2846 null, null, null, l_debug_mesg);
2847 raise;
2848
2849 END CheckFinalApprover;
2850
2851 PROCEDURE RemoveFromDispute (p_item_type IN VARCHAR2,
2852 p_item_key IN VARCHAR2,
2853 p_actid IN NUMBER,
2854 p_funcmode IN VARCHAR2,
2855 p_result OUT NOCOPY VARCHAR2) IS
2856
2857 l_debug_mesg varchar2(240);
2858 l_approver_id number;
2859 l_reason_code varchar2(45);
2860 l_currency_code varchar2(15);
2861 l_total_credit_to_invoice number;
2862 l_result_flag varchar2(1);
2863 l_customer_trx_id number;
2864
2865 /* bug 4478232 */
2866 l_request_id number;
2867 new_dispute_date date;
2868 new_dispute_amt number;
2869 remove_from_dispute_amt number;
2870
2871 /*4220382 */
2872
2873 CURSOR ps_cur(p_customer_trx_id NUMBER) IS
2874 SELECT payment_schedule_id, due_date, amount_in_dispute, dispute_date
2875 FROM ar_payment_schedules ps
2876 WHERE ps.customer_trx_id = p_customer_trx_id;
2877
2878 begin
2879 SetOrgContext (p_item_key);
2880 ---------------------------------------------------------
2881 l_debug_mesg := 'Remove Transaction from Dispute';
2882 ---------------------------------------------------------
2883
2884 --
2885 -- RUN mode - normal process execution
2886 --
2887 if (p_funcmode = 'RUN') then
2888
2889 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
2890 p_item_type,
2891 p_item_key,
2892 'CUSTOMER_TRX_ID');
2893
2894 l_request_id := WF_ENGINE.GetItemAttrNumber(
2895 p_item_type,
2896 p_item_key,
2897 'WORKFLOW_DOCUMENT_ID');
2898
2902 into remove_from_dispute_amt
2899 -- the amount stored in ra_cm_requests is a negative credit amount, it needs to
2900 -- be negated to get the correct dispute amount
2901 SELECT total_amount * -1
2903 from ra_cm_requests
2904 WHERE request_id = l_request_id;
2905
2906 /*4220382 */
2907 BEGIN
2908
2909 FOR ps_rec IN ps_cur (l_customer_trx_id )
2910 LOOP
2911
2912 new_dispute_amt := ps_rec.amount_in_dispute - remove_from_dispute_amt;
2913
2914 if new_dispute_amt = 0 then
2915 new_dispute_date := null;
2916 else
2917 new_dispute_date := ps_rec.dispute_date;
2918 end if;
2919
2920 arp_process_cutil.update_ps
2921 (p_ps_id=> ps_rec.payment_schedule_id,
2922 p_due_date=> ps_rec.due_date,
2923 p_amount_in_dispute=> new_dispute_amt,
2924 p_dispute_date=> new_dispute_date,
2925 p_update_dff => 'N',
2926 p_attribute_category=>NULL,
2927 p_attribute1=>NULL,
2928 p_attribute2=>NULL,
2929 p_attribute3=>NULL,
2930 p_attribute4=>NULL,
2931 p_attribute5=>NULL,
2932 p_attribute6=>NULL,
2933 p_attribute7=>NULL,
2934 p_attribute8=>NULL,
2935 p_attribute9=>NULL,
2936 p_attribute10=>NULL,
2937 p_attribute11=>NULL,
2938 p_attribute12=>NULL,
2939 p_attribute13=>NULL,
2940 p_attribute14=>NULL,
2941 p_attribute15=>NULL );
2942
2943 END LOOP;
2944 END;
2945
2946 l_reason_code := WF_ENGINE.GetItemAttrText(
2947 p_item_type,
2948 p_item_key,
2949 'REASON');
2950
2951 l_currency_code := WF_ENGINE.GetItemAttrText(
2952 p_item_type,
2953 p_item_key,
2954 'CURRENCY_CODE');
2955
2956 l_total_credit_to_invoice
2957 := WF_ENGINE.GetItemAttrNumber(
2958 p_item_type,
2959 p_item_key,
2960 'TOTAL_CREDIT_TO_INVOICE');
2961
2962
2963
2964 l_approver_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
2965 p_item_key,
2966 'APPROVER_ID');
2967
2968 CheckFinalApprover(l_reason_code,
2969 l_currency_code,
2970 l_total_credit_to_invoice,
2971 l_approver_id,
2972 l_result_flag);
2973
2974
2975
2976 if (l_result_flag = 'Y') then
2977
2978 -- it is a final aprrover
2979 p_result := 'COMPLETE:T';
2980 return;
2981 else
2982 p_result := 'COMPLETE:F';
2983 return;
2984 end if;
2985
2986 end if; -- end of run mode
2987
2988 --
2989 -- CANCEL mode
2990 --
2991
2992 if (p_funcmode = 'CANCEL') then
2993
2994 -- no result needed
2995 p_result := 'COMPLETE:';
2996 return;
2997 end if;
2998
2999
3000 --
3001 -- Other execution modes
3002 --
3003 p_result := '';
3004 return;
3005
3006 exception
3007 when others then
3008 -- The line below records this function call in the error system
3009 -- in the case of an exception.
3010 wf_core.context('ARP_CMREQ_WF', 'RemoveFromDispute',
3011 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3012 raise;
3013
3014 end RemoveFromDispute;
3015
3016
3017
3018 PROCEDURE FindReceivableApprover(p_item_type IN VARCHAR2,
3019 p_item_key IN VARCHAR2,
3020 p_actid IN NUMBER,
3021 p_funcmode IN VARCHAR2,
3022 p_result OUT NOCOPY VARCHAR2) IS
3023
3024 l_debug_mesg varchar2(240);
3025 l_receivable_role varchar2(240);
3026 l_role_display_name varchar2(240);
3027 l_role_id number;
3028
3029 begin
3030 -- SetOrgContext (p_item_key);
3031 --
3032 -- RUN mode - normal process execution
3033 --
3034 if (p_funcmode = 'RUN') then
3035
3036 -----------------------------------------------------------------
3037 l_debug_mesg := 'Check if Receivable Approver has been defined';
3038 -----------------------------------------------------------------
3039
3040 l_receivable_role := WF_ENGINE.GetItemAttrText(p_item_type,
3041 p_item_key,
3042 'RECEIVABLE_ROLE');
3043
3044 IF l_receivable_role IS NOT NULL THEN
3045
3049
3046 SELECT display_name,orig_system_id INTO l_role_display_name, l_role_id
3047 FROM wf_roles
3048 WHERE name = l_receivable_role;
3050 WF_ENGINE.SetItemAttrText(p_item_type,
3051 p_item_key,
3052 'APPROVER_USER_NAME',
3053 l_receivable_role);
3054
3055 WF_ENGINE.SetItemAttrText(p_item_type,
3056 p_item_key,
3057 'APPROVER_DISPLAY_NAME',
3058 l_role_display_name);
3059
3060 WF_ENGINE.SetItemAttrNumber(p_item_type,
3061 p_item_key,
3062 'APPROVER_ID',
3063 l_role_id);
3064
3065 p_result := 'COMPLETE:T';
3066 ELSE
3067 p_result := 'COMPLETE:F';
3068
3069 END IF;
3070
3071 return;
3072
3073 end if; -- end of run mode
3074
3075 --
3076 -- CANCEL mode
3077 --
3078 -- This is an event point is called with the effect of the activity must
3079 -- be undone, for example when a process is reset to an earlier point
3080 -- due to a loop back.
3081 --
3082 if (p_funcmode = 'CANCEL') then
3083
3084 -- no result needed
3085 p_result := 'COMPLETE:';
3086 return;
3087 end if;
3088
3089
3090 --
3091 -- Other execution modes may be created in the future. Your
3092 -- activity will indicate that it does not implement a mode
3093 -- by returning null
3094 --
3095 p_result := '';
3096 return;
3097
3098 exception
3099 when others then
3100 -- The line below records this function call in the error system
3101 -- in the case of an exception.
3102 wf_core.context('ARP_CMREQ_WF', 'FindReceivableApprover',
3103 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3104 raise;
3105
3106 end FindReceivableApprover;
3107
3108
3109 PROCEDURE FindResponder (p_item_type IN VARCHAR2,
3110 p_item_key IN VARCHAR2,
3111 p_actid IN NUMBER,
3112 p_funcmode IN VARCHAR2,
3113 p_result OUT NOCOPY VARCHAR2) IS
3114
3115 l_debug_mesg varchar2(240);
3116 l_approver_id number;
3117 l_approver_user_name varchar2(100);
3118 l_approver_display_name varchar2(240);
3119 l_notification_id number;
3120
3121 begin
3122 --uncommented for 5410467
3123 SetOrgContext (p_item_key);
3124 --
3125 -- RUN mode - normal process execution
3126 --
3127 if (p_funcmode = 'RESPOND') then
3128
3129 -----------------------------------------------------------------
3130 l_debug_mesg := 'Find user in Receivable role who responded to
3131 the notification';
3132 -----------------------------------------------------------------
3133
3134 l_notification_id := wf_engine.context_nid;
3135 l_approver_user_name := wf_engine.context_text;
3136
3137 SELECT orig_system_id, display_name
3138 INTO l_approver_id, l_approver_display_name
3139 FROM wf_users
3140 WHERE orig_system = 'PER'
3141 AND name = l_approver_user_name;
3142
3143 WF_ENGINE.SetItemAttrText(p_item_type,
3144 p_item_key,
3145 'APPROVER_ID',
3146 l_approver_id);
3147
3148 WF_ENGINE.SetItemAttrText(p_item_type,
3149 p_item_key,
3150 'APPROVER_USER_NAME',
3151 l_approver_user_name);
3152
3153 WF_ENGINE.SetItemAttrText(p_item_type,
3154 p_item_key,
3155 'APPROVER_DISPLAY_NAME',
3156 l_approver_display_name);
3157
3158 p_result := 'COMPLETE:T';
3159 return;
3160
3161 end if; -- end of run mode
3162
3163 --
3164 -- CANCEL mode
3165 --
3166 -- This is an event point is called with the effect of the activity must
3167 -- be undone, for example when a process is reset to an earlier point
3168 -- due to a loop back.
3169 --
3170 if (p_funcmode = 'CANCEL') then
3171
3172 -- no result needed
3173 p_result := 'COMPLETE:';
3174 return;
3175 end if;
3176
3177
3178 --
3179 -- Other execution modes may be created in the future. Your
3180 -- activity will indicate that it does not implement a mode
3181 -- by returning null
3182 --
3183 p_result := '';
3184 return;
3185
3186 exception
3187 when others then
3188 -- The line below records this function call in the error system
3189 -- in the case of an exception.
3190 wf_core.context('ARP_CMREQ_WF', 'FindResponder',
3191 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3192 raise;
3193
3194 end FindResponder;
3195
3196
3200 p_funcmode IN VARCHAR2,
3197 PROCEDURE InsertSubmissionNotes(p_item_type IN VARCHAR2,
3198 p_item_key IN VARCHAR2,
3199 p_actid IN NUMBER,
3201 p_result OUT NOCOPY VARCHAR2) IS
3202
3203 l_debug_mesg varchar2(240);
3204
3205 l_document_id number;
3206 l_requestor_user_name varchar2(100);
3207 l_customer_trx_id number;
3208 l_note_id number;
3209 l_reason_code varchar2(45);
3210 l_total_credit_to_invoice number;
3211 l_note_text ar_notes.text%type;
3212 /* Bug 3206020 Changed comments width from 240 to 1760 */
3213 l_comments varchar2(1760);
3214 l_reason_meaning varchar2(100);
3215
3216 /* Bug 7367350 inserting internal notes */
3217 l_internal_comment VARCHAR2(1760) DEFAULT NULL;
3218 l_note_text1 ar_notes.text%type;
3219 l_comment_type VARCHAR2(20);
3220
3221 begin
3222 -- SetOrgContext (p_item_key);
3223 -------------------------------------------------------------
3224 l_debug_mesg := 'Insert WF submission notes';
3225 -----------------------------------------------------------
3226
3227 --
3228 -- RUN mode - normal process execution
3229 --
3230 if (p_funcmode = 'RUN') then
3231
3232
3233 l_document_id := WF_ENGINE.GetItemAttrNumber(
3234 p_item_type,
3235 p_item_key,
3236 'WORKFLOW_DOCUMENT_ID');
3237
3238 l_requestor_user_name
3239 := WF_ENGINE.GetItemAttrText(
3240 p_item_type,
3241 p_item_key,
3242 'REQUESTOR_USER_NAME');
3243
3244
3245 l_reason_code := WF_ENGINE.GetItemAttrText(
3246 p_item_type,
3247 p_item_key,
3248 'REASON');
3249
3250
3251 l_total_credit_to_invoice
3252 := WF_ENGINE.GetItemAttrNumber(
3253 p_item_type,
3254 p_item_key,
3255 'TOTAL_CREDIT_TO_INVOICE');
3256
3257
3258 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3259 p_item_type,
3260 p_item_key,
3261 'CUSTOMER_TRX_ID');
3262
3263 l_comments := WF_ENGINE.GetItemAttrText(
3264 p_item_type,
3265 p_item_key,
3266 'COMMENTS');
3267 l_internal_comment := wf_engine.GetItemAttrText(
3268 p_item_type,
3269 p_item_key,
3270 'INTERNAL_COMMENTS');
3271
3272 -- bug fix 1202680 -- notes should reflect the reason meaning and not the code.
3273 begin
3274 select meaning into l_reason_meaning
3275 from ar_lookups
3276 where lookup_type = 'CREDIT_MEMO_REASON'
3277 and lookup_code = l_reason_code;
3278 exception
3279 when others then
3280 l_reason_meaning := l_reason_code;
3281 end;
3282
3283 fnd_message.set_name('AR', 'AR_WF_SUBMISSION');
3284 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3285 fnd_message.set_token('REQUESTOR', l_requestor_user_name);
3286 fnd_message.set_token('AMOUNT', to_char(l_total_credit_to_invoice));
3287 fnd_message.set_token('REASON', l_reason_meaning);
3288
3289 l_note_text := fnd_message.get;
3290 l_note_text1 := l_note_text;
3291 if l_comments is not NULL then
3292 select meaning into l_comment_type
3293 from ar_lookups
3294 where LOOKUP_TYPE='AR_COMMENT_CLASSIFICATION'
3295 AND LOOKUP_CODE='C';
3296 l_note_text := l_note_text || ' :' || l_comment_type || ': "' || l_comments || '"';
3297 end if;
3298 IF l_internal_comment is NOT NULL then
3299 select meaning into l_comment_type
3300 from ar_lookups
3301 where LOOKUP_TYPE='AR_COMMENT_CLASSIFICATION'
3302 AND LOOKUP_CODE='I';
3303 l_note_text1 := l_note_text1 || ' :' || l_comment_type || ': "' || l_internal_comment || '"';
3304
3305 InsertTrxNotes(NULL,
3306 NULL,
3307 NULL,
3308 l_customer_trx_id,
3309 'MAINTAIN',
3310 l_note_text1,
3311 l_note_id);
3312 END IF;
3313
3314
3315 InsertTrxNotes(NULL,
3316 NULL,
3317 NULL,
3318 l_customer_trx_id,
3322
3319 'MAINTAIN',
3320 l_note_text,
3321 l_note_id);
3323
3324 p_result := 'COMPLETE:T';
3325 return;
3326
3327
3328 end if; -- end of run mode
3329
3330 --
3331 -- CANCEL mode
3332 --
3333
3334 if (p_funcmode = 'CANCEL') then
3335
3336 -- no result needed
3337 p_result := 'COMPLETE:';
3338 return;
3339 end if;
3340
3341
3342 --
3343 -- Other execution modes
3344 --
3345 p_result := '';
3346 return;
3347
3348 exception
3349 when others then
3350 -- The line below records this function call in the error system
3351 -- in the case of an exception.
3352 wf_core.context('ARP_CMREQ_WF', 'InsertSubmissionNotes',
3353 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3354 raise;
3355
3356 end InsertSubmissionNotes;
3357
3358
3359 PROCEDURE InsertApprovalReminderNotes(p_item_type IN VARCHAR2,
3360 p_item_key IN VARCHAR2,
3361 p_actid IN NUMBER,
3362 p_funcmode IN VARCHAR2,
3363 p_result OUT NOCOPY VARCHAR2) IS
3364
3365 l_debug_mesg varchar2(240);
3366
3367 l_document_id number;
3368 l_customer_trx_id number;
3369 l_approver_display_name varchar2(100);
3370 l_note_id number;
3371 l_note_text ar_notes.text%type;
3372 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
3373
3374 begin
3375 -- SetOrgContext (p_item_key);
3376 ---------------------------------------------------------------------
3377 l_debug_mesg := 'Insert Request Approval Reminder notes';
3378 ---------------------------------------------------------------------
3379
3380 --
3381 -- RUN mode - normal process execution
3382 --
3383 if (p_funcmode = 'RUN') then
3384
3385
3386 l_document_id := WF_ENGINE.GetItemAttrNumber(
3387 p_item_type,
3388 p_item_key,
3389 'WORKFLOW_DOCUMENT_ID');
3390
3391 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3392 p_item_type,
3393 p_item_key,
3394 'CUSTOMER_TRX_ID');
3395
3396 l_approver_display_name
3397 := WF_ENGINE.GetItemAttrText(
3398 p_item_type,
3399 p_item_key,
3400 'APPROVER_DISPLAY_NAME');
3401
3402 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
3403
3404 fnd_message.set_name('AR', 'AR_WF_APPROVAL_REMINDER');
3405 fnd_message.set_token('APPROVER', l_approver_display_name);
3406 -- bug fix 1122477
3407
3408 l_note_text := fnd_message.get;
3409
3410 IF l_notes is NOT NULL then
3411 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
3412 END IF;
3413
3414 InsertTrxNotes(NULL,
3415 NULL,
3416 NULL,
3417 l_customer_trx_id,
3418 'MAINTAIN',
3419 l_note_text,
3420 l_note_id);
3421
3422 /* Bug 3195343 Initialise the escalation_count attribute for the
3423 Primary approver. */
3424
3425 WF_ENGINE.SetItemAttrNumber( p_item_type,
3426 p_item_key,
3427 'ESCALATION_COUNT',
3428 0 ) ;
3429 p_result := 'COMPLETE:T';
3430 return;
3431
3432
3433 end if; -- end of run mode
3434
3435 --
3436 -- CANCEL mode
3437 --
3438
3439 if (p_funcmode = 'CANCEL') then
3440
3441 -- no result needed
3442 p_result := 'COMPLETE:';
3443 return;
3444 end if;
3445
3446
3447 --
3448 -- Other execution modes
3449 --
3450 p_result := '';
3451 return;
3452
3453 exception
3454 when others then
3455 -- The line below records this function call in the error system
3456 -- in the case of an exception.
3457 wf_core.context('ARP_CMREQ_WF', 'InsertApprovalReminderNotes',
3458 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3459 raise;
3460
3461 end InsertApprovalReminderNotes;
3462
3463
3464 PROCEDURE InsertEscalationNotes (p_item_type IN VARCHAR2,
3465 p_item_key IN VARCHAR2,
3466 p_actid IN NUMBER,
3467 p_funcmode IN VARCHAR2,
3468 p_result OUT NOCOPY VARCHAR2) IS
3469
3473 l_customer_trx_id number;
3470 l_debug_mesg varchar2(240);
3471
3472 l_document_id number;
3474 l_manager_user_name varchar2(100);
3475 l_note_id number;
3476 l_note_text ar_notes.text%type;
3477 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
3478
3479 begin
3480 -- SetOrgContext (p_item_key);
3481 ---------------------------------------------------------------------
3482 l_debug_mesg := 'Insert Escalation notes';
3483 ---------------------------------------------------------------------
3484
3485 --
3486 -- RUN mode - normal process execution
3487 --
3488 if (p_funcmode = 'RUN') then
3489
3490
3491 l_document_id := WF_ENGINE.GetItemAttrNumber(
3492 p_item_type,
3493 p_item_key,
3494 'WORKFLOW_DOCUMENT_ID');
3495
3496 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3497 p_item_type,
3498 p_item_key,
3499 'CUSTOMER_TRX_ID');
3500
3501 l_manager_user_name
3502 := WF_ENGINE.GetItemAttrText(
3503 p_item_type,
3504 p_item_key,
3505 'MANAGER_USER_NAME');
3506
3507 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
3508
3509 fnd_message.set_name('AR', 'AR_WF_APPROVAL_ESCALATION');
3510 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3511 fnd_message.set_token('APPROVER', l_manager_user_name);
3512
3513 l_note_text := fnd_message.get;
3514
3515 IF l_notes is NOT NULL then
3516 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
3517 END IF;
3518
3519 InsertTrxNotes(NULL,
3520 NULL,
3521 NULL,
3522 l_customer_trx_id,
3523 'MAINTAIN',
3524 l_note_text,
3525 l_note_id);
3526
3527
3528 p_result := 'COMPLETE:T';
3529 return;
3530
3531
3532 end if; -- end of run mode
3533
3534 --
3535 -- CANCEL mode
3536 --
3537
3538 if (p_funcmode = 'CANCEL') then
3539
3540 -- no result needed
3541 p_result := 'COMPLETE:';
3542 return;
3543 end if;
3544
3545
3546 --
3547 -- Other execution modes
3548 --
3549 p_result := '';
3550 return;
3551
3552 exception
3553 when others then
3554 -- The line below records this function call in the error system
3555 -- in the case of an exception.
3556 wf_core.context('ARP_CMREQ_WF', 'InsertEscalationNotes',
3557 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3558 raise;
3559
3560 end InsertEscalationNotes;
3561
3562
3563 PROCEDURE InsertRequestManualNotes (p_item_type IN VARCHAR2,
3564 p_item_key IN VARCHAR2,
3565 p_actid IN NUMBER,
3566 p_funcmode IN VARCHAR2,
3567 p_result OUT NOCOPY VARCHAR2) IS
3568
3569 l_debug_mesg varchar2(240);
3570
3571 l_document_id number;
3572 l_customer_trx_id number;
3573 l_receivable_role varchar2(100);
3574 l_role_display_name varchar2(240);
3575 l_note_id number;
3576 l_note_text ar_notes.text%type;
3577 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
3578
3579 begin
3580 -- SetOrgContext (p_item_key);
3581 ---------------------------------------------------------------------
3582 l_debug_mesg := 'Insert Request Manual Entry notes';
3583 ---------------------------------------------------------------------
3584
3585 --
3586 -- RUN mode - normal process execution
3587 --
3588 if (p_funcmode = 'RUN') then
3589
3590
3591 l_document_id := WF_ENGINE.GetItemAttrNumber(
3592 p_item_type,
3593 p_item_key,
3594 'WORKFLOW_DOCUMENT_ID');
3595
3596 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3597 p_item_type,
3598 p_item_key,
3599 'CUSTOMER_TRX_ID');
3600
3601 l_receivable_role
3602 := WF_ENGINE.GetItemAttrText(
3603 p_item_type,
3604 p_item_key,
3605 'RECEIVABLE_ROLE');
3606 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
3607 SELECT display_name INTO l_role_display_name
3608 FROM wf_roles
3612 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3609 WHERE name = l_receivable_role;
3610
3611 fnd_message.set_name('AR', 'AR_WF_REQUEST_MANUAL');
3613 fnd_message.set_token('RECEIVABLE_ROLE',l_role_display_name);
3614
3615 l_note_text := fnd_message.get;
3616
3617 IF l_notes is NOT NULL then
3618 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
3619 END IF;
3620
3621 InsertTrxNotes(NULL,
3622 NULL,
3623 NULL,
3624 l_customer_trx_id,
3625 'MAINTAIN',
3626 l_note_text,
3627 l_note_id);
3628
3629
3630 p_result := 'COMPLETE:T';
3631 return;
3632
3633
3634 end if; -- end of run mode
3635
3636 --
3637 -- CANCEL mode
3638 --
3639
3640 if (p_funcmode = 'CANCEL') then
3641
3642 -- no result needed
3643 p_result := 'COMPLETE:';
3644 return;
3645 end if;
3646
3647
3648 --
3649 -- Other execution modes
3650 --
3651 p_result := '';
3652 return;
3653
3654 exception
3655 when others then
3656 -- The line below records this function call in the error system
3657 -- in the case of an exception.
3658 wf_core.context('ARP_CMREQ_WF', 'InsertRequestManualNotes',
3659 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3660 raise;
3661
3662 end InsertRequestManualNotes;
3663
3664
3665 PROCEDURE InsertCompletedManualNotes(p_item_type IN VARCHAR2,
3666 p_item_key IN VARCHAR2,
3667 p_actid IN NUMBER,
3668 p_funcmode IN VARCHAR2,
3669 p_result OUT NOCOPY VARCHAR2) IS
3670
3671 l_debug_mesg varchar2(240);
3672 l_document_id number;
3673 l_customer_trx_id number;
3674 l_receivable_role varchar2(100);
3675 l_role_display_name varchar2(240);
3676 l_note_id number;
3677 l_note_text ar_notes.text%type;
3678 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
3679
3680 /* bug 1908252 */
3681 l_last_updated_by number;
3682 l_last_update_login number;
3683
3684 BEGIN
3685 -- SetOrgContext (p_item_key);
3686
3687 -- Bug 2105483 : rather then calling arp_global at the start
3688 -- of the package, where it can error out NOCOPY since org_id is not yet set,
3689 -- do the call right before it is needed
3690 arp_global.init_global;
3691
3692 /* Bug 1908252 */
3693 l_last_updated_by := ARP_GLOBAL.user_id;
3694 l_last_update_login := ARP_GLOBAL.last_update_login ;
3695
3696 ---------------------------------------------------------------------
3697 l_debug_mesg := 'Insert Completed Manual Entry notes';
3698 ---------------------------------------------------------------------
3699
3700 --
3701 -- RUN mode - normal process execution
3702 --
3703 if (p_funcmode = 'RUN') then
3704
3705
3706 l_document_id := WF_ENGINE.GetItemAttrNumber(
3707 p_item_type,
3708 p_item_key,
3709 'WORKFLOW_DOCUMENT_ID');
3710
3711 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3712 p_item_type,
3713 p_item_key,
3714 'CUSTOMER_TRX_ID');
3715
3716 l_receivable_role
3717 := WF_ENGINE.GetItemAttrText(
3718 p_item_type,
3719 p_item_key,
3720 'RECEIVABLE_ROLE');
3721 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
3722
3723 SELECT display_name INTO l_role_display_name
3724 FROM wf_roles
3725 WHERE name = l_receivable_role;
3726
3727
3728
3729 fnd_message.set_name('AR', 'AR_WF_COMPLETED_MANUAL');
3730 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3731 fnd_message.set_token('APPROVER',l_role_display_name);
3732
3733 l_note_text := fnd_message.get;
3734
3735 IF l_notes is NOT NULL then
3736 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
3737 END IF;
3738
3739 InsertTrxNotes(NULL,
3740 NULL,
3741 NULL,
3742 l_customer_trx_id,
3743 'MAINTAIN',
3744 l_note_text,
3745 l_note_id);
3746
3747 /* Bug 1908252 : update last_update* fields */
3748 update ra_cm_requests
3749 set status = 'COMPLETE',
3750 approval_date = SYSDATE,
3751 last_updated_by = l_last_updated_by,
3752 last_update_date = SYSDATE,
3753 last_update_login = l_last_update_login
3754 where request_id = p_item_key;
3755
3756 p_result := 'COMPLETE:T';
3760 end if; -- end of run mode
3757 return;
3758
3759
3761
3762 --
3763 -- CANCEL mode
3764 --
3765
3766 if (p_funcmode = 'CANCEL') then
3767
3768 -- no result needed
3769 p_result := 'COMPLETE:';
3770 return;
3771 end if;
3772
3773
3774 --
3775 -- Other execution modes
3776 --
3777 p_result := '';
3778 return;
3779
3780 exception
3781 when others then
3782 -- The line below records this function call in the error system
3783 -- in the case of an exception.
3784 wf_core.context('ARP_CMREQ_WF', 'InsertCompletedManualNotes',
3785 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3786 raise;
3787
3788 end InsertCompletedManualNotes;
3789
3790
3791 PROCEDURE InsertRequestApprovalNotes(p_item_type IN VARCHAR2,
3792 p_item_key IN VARCHAR2,
3793 p_actid IN NUMBER,
3794 p_funcmode IN VARCHAR2,
3795 p_result OUT NOCOPY VARCHAR2) IS
3796
3797 l_debug_mesg varchar2(240);
3798
3799 l_document_id number;
3800 l_customer_trx_id number;
3801 l_approver_display_name varchar2(100);
3802 l_note_id number;
3803 l_note_text ar_notes.text%type;
3804 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
3805
3806 begin
3807 -- SetOrgContext (p_item_key);
3808 ---------------------------------------------------------------------
3809 l_debug_mesg := 'Insert Request Approval notes';
3810 ---------------------------------------------------------------------
3811
3812 --
3813 -- RUN mode - normal process execution
3814 --
3815 if (p_funcmode = 'RUN') then
3816
3817
3818 l_document_id := WF_ENGINE.GetItemAttrNumber(
3819 p_item_type,
3820 p_item_key,
3821 'WORKFLOW_DOCUMENT_ID');
3822
3823 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3824 p_item_type,
3825 p_item_key,
3826 'CUSTOMER_TRX_ID');
3827
3828 l_approver_display_name
3829 := WF_ENGINE.GetItemAttrText(
3830 p_item_type,
3831 p_item_key,
3832 'APPROVER_DISPLAY_NAME');
3833
3834 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
3835
3836 fnd_message.set_name('AR', 'AR_WF_REQUEST_APPROVAL');
3837 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3838 fnd_message.set_token('APPROVER', l_approver_display_name);
3839 -- bug fix 1122477
3840
3841 l_note_text := fnd_message.get;
3842
3843 IF l_notes is NOT NULL then
3844 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
3845 END IF;
3846
3847 InsertTrxNotes(NULL,
3848 NULL,
3849 NULL,
3850 l_customer_trx_id,
3851 'MAINTAIN',
3852 l_note_text,
3853 l_note_id);
3854
3855 p_result := 'COMPLETE:T';
3856 return;
3857
3858
3859 end if; -- end of run mode
3860
3861 --
3862 -- CANCEL mode
3863 --
3864
3865 if (p_funcmode = 'CANCEL') then
3866
3867 -- no result needed
3868 p_result := 'COMPLETE:';
3869 return;
3870 end if;
3871
3872
3873 --
3874 -- Other execution modes
3875 --
3876 p_result := '';
3877 return;
3878
3879 exception
3880 when others then
3881 -- The line below records this function call in the error system
3882 -- in the case of an exception.
3883 wf_core.context('ARP_CMREQ_WF', 'InsertRequestApprovalNotes',
3884 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3885 raise;
3886
3887 end InsertRequestApprovalNotes;
3888
3889 PROCEDURE InsertApprovedResponseNotes(p_item_type IN VARCHAR2,
3890 p_item_key IN VARCHAR2,
3891 p_actid IN NUMBER,
3892 p_funcmode IN VARCHAR2,
3893 p_result OUT NOCOPY VARCHAR2) IS
3894
3895 l_debug_mesg varchar2(240);
3896
3897 l_document_id number;
3898 l_customer_trx_id number;
3899 l_approver_display_name varchar2(100);
3900 l_note_id number;
3901 l_note_text ar_notes.text%type;
3902 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
3903
3904 begin
3905 -- SetOrgContext (p_item_key);
3906 ---------------------------------------------------------------------
3907 l_debug_mesg := 'Insert Approved Response notes';
3911 -- RUN mode - normal process execution
3908 ---------------------------------------------------------------------
3909
3910 --
3912 --
3913 if (p_funcmode = 'RUN') then
3914
3915
3916 l_document_id := WF_ENGINE.GetItemAttrNumber(
3917 p_item_type,
3918 p_item_key,
3919 'WORKFLOW_DOCUMENT_ID');
3920
3921 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
3922 p_item_type,
3923 p_item_key,
3924 'CUSTOMER_TRX_ID');
3925
3926 l_approver_display_name
3927 := WF_ENGINE.GetItemAttrText(
3928 p_item_type,
3929 p_item_key,
3930 'APPROVER_DISPLAY_NAME');
3931
3932 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
3933
3934 fnd_message.set_name('AR', 'AR_WF_APPROVED_RESPONSE');
3935 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
3936 fnd_message.set_token('APPROVER', l_approver_display_name);
3937 -- bug fix 1122477
3938
3939 l_note_text := fnd_message.get;
3940
3941 IF l_notes is NOT NULL then
3942 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
3943 END IF;
3944
3945 InsertTrxNotes(NULL,
3946 NULL,
3947 NULL,
3948 l_customer_trx_id,
3949 'MAINTAIN',
3950 l_note_text,
3951 l_note_id);
3952
3953
3954 p_result := 'COMPLETE:T';
3955 return;
3956
3957
3958 end if; -- end of run mode
3959
3960 --
3961 -- CANCEL mode
3962 --
3963
3964 if (p_funcmode = 'CANCEL') then
3965
3966 -- no result needed
3967 p_result := 'COMPLETE:';
3968 return;
3969 end if;
3970
3971
3972 --
3973 -- Other execution modes
3974 --
3975 p_result := '';
3976 return;
3977
3978 exception
3979 when others then
3980 -- The line below records this function call in the error system
3981 -- in the case of an exception.
3982 wf_core.context('ARP_CMREQ_WF', 'InsertApprovedResponseNotes',
3983 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
3984 raise;
3985
3986 end InsertApprovedResponseNotes;
3987
3988
3989 PROCEDURE InsertRejectedResponseNotes(p_item_type IN VARCHAR2,
3990 p_item_key IN VARCHAR2,
3991 p_actid IN NUMBER,
3992 p_funcmode IN VARCHAR2,
3993 p_result OUT NOCOPY VARCHAR2) IS
3994
3995 l_debug_mesg varchar2(240);
3996 l_document_id number;
3997 l_customer_trx_id number;
3998 l_approver_display_name varchar2(100);
3999 l_note_id number;
4000 l_note_text ar_notes.text%type;
4001 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4002
4003 /* bug 1908252 */
4004 l_last_updated_by number;
4005 l_last_update_login number;
4006
4007 BEGIN
4008 -- SetOrgContext (p_item_key);
4009
4010 -- Bug 2105483 : rather then calling arp_global at the start
4011 -- of the package, where it can error out NOCOPY since org_id is not yet set,
4012 -- do the call right before it is needed
4013 arp_global.init_global;
4014
4015 /* Bug 1908252 */
4016 l_last_updated_by := ARP_GLOBAL.user_id;
4017 l_last_update_login := ARP_GLOBAL.last_update_login ;
4018
4019 ---------------------------------------------------------------------
4020 l_debug_mesg := 'Insert Rejected Response notes';
4021 ---------------------------------------------------------------------
4022
4023 --
4024 -- RUN mode - normal process execution
4025 --
4026 if (p_funcmode = 'RUN') then
4027
4028
4029 l_document_id := WF_ENGINE.GetItemAttrNumber(
4030 p_item_type,
4031 p_item_key,
4032 'WORKFLOW_DOCUMENT_ID');
4033
4034 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
4035 p_item_type,
4036 p_item_key,
4037 'CUSTOMER_TRX_ID');
4038
4039 l_approver_display_name
4040 := WF_ENGINE.GetItemAttrText(
4041 p_item_type,
4042 p_item_key,
4043 'APPROVER_DISPLAY_NAME');
4044 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4045
4046
4047 fnd_message.set_name('AR', 'AR_WF_REJECTED_RESPONSE');
4051
4048 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4049 fnd_message.set_token('APPROVER', l_approver_display_name);
4050 -- bug fix 1122477
4052 l_note_text := fnd_message.get;
4053
4054 IF l_notes is NOT NULL then
4055 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4056 END IF;
4057
4058 InsertTrxNotes(NULL,
4059 NULL,
4060 NULL,
4061 l_customer_trx_id,
4062 'MAINTAIN',
4063 l_note_text,
4064 l_note_id);
4065
4066 /* Bug 1908252 : update last_update* fields */
4067
4068 UPDATE ra_cm_requests
4069 SET status = 'NOT_APPROVED',
4070 last_updated_by = l_last_updated_by,
4071 last_update_date = SYSDATE,
4072 last_update_login = l_last_update_login
4073 WHERE request_id = p_item_key;
4074
4075 /*COMMIT;*/
4076
4077 p_result := 'COMPLETE:T';
4078 return;
4079
4080
4081 end if; -- end of run mode
4082
4083 --
4084 -- CANCEL mode
4085 --
4086
4087 if (p_funcmode = 'CANCEL') then
4088
4089 -- no result needed
4090 p_result := 'COMPLETE:';
4091 return;
4092 end if;
4093
4094
4095 --
4096 -- Other execution modes
4097 --
4098 p_result := '';
4099 return;
4100
4101 exception
4102 when others then
4103 -- The line below records this function call in the error system
4104 -- in the case of an exception.
4105 wf_core.context('ARP_CMREQ_WF', 'InsertRejectedResponseNotes',
4106 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
4107 raise;
4108
4109
4110 end InsertRejectedResponseNotes;
4111
4112
4113 PROCEDURE InsertSuccessfulAPINotes(p_item_type IN VARCHAR2,
4114 p_item_key IN VARCHAR2,
4115 p_actid IN NUMBER,
4116 p_funcmode IN VARCHAR2,
4117 p_result OUT NOCOPY VARCHAR2) IS
4118
4119 l_debug_mesg varchar2(240);
4120
4121 l_document_id number;
4122 l_credit_memo_number varchar2(20);
4123 l_customer_trx_id number;
4124 l_note_id number;
4125 l_note_text ar_notes.text%type;
4126 l_notes wf_item_attribute_values.text_value%TYPE; /*5119049 */
4127
4128 begin
4129 SetOrgContext (p_item_key);
4130 ---------------------------------------------------------------------
4131 l_debug_mesg := 'Insert Completed Successful API notes';
4132 ---------------------------------------------------------------------
4133
4134 --
4135 -- RUN mode - normal process execution
4136 --
4137 if (p_funcmode = 'RUN') then
4138
4139
4140 l_document_id := WF_ENGINE.GetItemAttrNumber(
4141 p_item_type,
4142 p_item_key,
4143 'WORKFLOW_DOCUMENT_ID');
4144
4145 l_credit_memo_number := WF_ENGINE.GetItemAttrText(
4146 p_item_type,
4147 p_item_key,
4148 'CREDIT_MEMO_NUMBER');
4149
4150 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
4151 p_item_type,
4152 p_item_key,
4153 'CUSTOMER_TRX_ID');
4154 l_notes := wf_engine.GetItemAttrText( p_item_type, p_item_key,'NOTES'); /*5119049*/
4155
4156 /* Get trx number for CM and the insert into note text */
4157
4158
4159 fnd_message.set_name('AR', 'AR_WF_COMPLETED_SUCCESSFUL');
4160 fnd_message.set_token('REQUEST_ID', to_char(l_document_id));
4161 fnd_message.set_token('TRXNUMBER', l_credit_memo_number);
4162
4163 l_note_text := fnd_message.get;
4164
4165 IF l_notes is NOT NULL then
4166 l_note_text := SUBSTRB(l_note_text || ' "' || l_notes || '"',1,2000) ; /*5119049*/
4167 END IF;
4168
4169 InsertTrxNotes(NULL,
4170 NULL,
4171 NULL,
4172 l_customer_trx_id,
4173 'MAINTAIN',
4174 l_note_text,
4175 l_note_id);
4176
4177
4178 p_result := 'COMPLETE:T';
4179 return;
4180
4181
4182 end if; -- end of run mode
4183
4184 --
4185 -- CANCEL mode
4186 --
4187
4188 if (p_funcmode = 'CANCEL') then
4189
4190 -- no result needed
4191 p_result := 'COMPLETE:';
4192 return;
4193 end if;
4194
4195
4196 --
4197 -- Other execution modes
4198 --
4199 p_result := '';
4200 return;
4201
4202 exception
4203 when others then
4204 -- The line below records this function call in the error system
4205 -- in the case of an exception.
4209
4206 wf_core.context('ARP_CMREQ_WF', 'InsertSuccessfulAPINotes',
4207 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
4208 raise;
4210 end InsertSuccessfulAPINotes;
4211
4212
4213 PROCEDURE InsertNotes(p_item_type IN VARCHAR2,
4214 p_item_key IN VARCHAR2,
4215 p_actid IN NUMBER,
4216 p_funcmode IN VARCHAR2,
4217 p_result OUT NOCOPY VARCHAR2) IS
4218
4219
4220 l_debug_mesg varchar2(240);
4221
4222 l_customer_id number;
4223 l_collector_id number;
4224 l_customer_trx_id number;
4225 l_bill_to_site_use_id number;
4226 l_customer_call_id number;
4227 l_customer_call_topic_id number;
4228 l_action_id number;
4229 l_note_id number;
4230
4231 l_reason_code varchar2(45);
4232 l_currency_code varchar2(15);
4233 l_entered_amount_display number;
4234 l_result_flag varchar2(1);
4235
4236 begin
4237 -- SetOrgContext (p_item_key);
4238
4239 ---------------------------------------------------------
4240 l_debug_mesg := 'Create a call record and insert a note';
4241 ---------------------------------------------------------
4242
4243
4244 --
4245 -- RUN mode - normal process execution
4246 --
4247 if (p_funcmode = 'RUN') then
4248
4249 l_reason_code := WF_ENGINE.GetItemAttrText(
4250 p_item_type,
4251 p_item_key,
4252 'REASON');
4253
4254 l_currency_code := WF_ENGINE.GetItemAttrText(
4255 p_item_type,
4256 p_item_key,
4257 'CURRENCY_CODE');
4258
4259 l_entered_amount_display
4260 := WF_ENGINE.GetItemAttrNumber(
4261 p_item_type,
4262 p_item_key,
4263 'ENTERED_AMOUNT_DISPLAY');
4264
4265 l_customer_id := WF_ENGINE.GetItemAttrNumber(
4266 p_item_type,
4267 p_item_key,
4268 'CUSTOMER_ID');
4269
4270 l_collector_id := WF_ENGINE.GetItemAttrNumber(
4271 p_item_type,
4272 p_item_key,
4273 'COLLECTOR_ID');
4274
4275
4276 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
4277 p_item_type,
4278 p_item_key,
4279 'CUSTOMER_TRX_ID');
4280
4281 l_bill_to_site_use_id := WF_ENGINE.GetItemAttrNumber(
4282 p_item_type,
4283 p_item_key,
4284 'BILL_TO_SITE_USE_ID');
4285
4286
4287
4288 InsertTrxNotes(NULL,
4289 NULL,
4290 NULL,
4291 l_customer_trx_id,
4292 'MAINTAIN',
4293 'Credit Memo request was approved by receivable role.',
4294 l_note_id);
4295
4296
4297 p_result := 'COMPLETE:T';
4298 return;
4299
4300
4301 end if; -- end of run mode
4302
4303 --
4304 -- CANCEL mode
4305 --
4306
4307 if (p_funcmode = 'CANCEL') then
4308
4309 -- no result needed
4310 p_result := 'COMPLETE:';
4311 return;
4312 end if;
4313
4314
4315 --
4316 -- Other execution modes
4317 --
4318 p_result := '';
4319 return;
4320
4321 exception
4322 when others then
4323 -- The line below records this function call in the error system
4324 -- in the case of an exception.
4325 wf_core.context('ARP_CMREQ_WF', 'InsertNotes',
4326 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
4327 raise;
4328
4329 end InsertNotes;
4330
4331
4332 PROCEDURE InsertTrxNotes(x_customer_call_id IN NUMBER,
4333 x_customer_call_topic_id IN NUMBER,
4334 x_action_id IN NUMBER,
4335 x_customer_trx_id IN NUMBER,
4336 x_note_type IN VARCHAR2,
4337 x_text IN VARCHAR2,
4338 x_note_id OUT NOCOPY NUMBER) IS
4339
4340 l_debug_mesg varchar2(240);
4341 l_last_updated_by number;
4342 l_last_update_date date;
4343 l_last_update_login number;
4344 l_creation_date date;
4345 l_created_by number;
4346
4347 BEGIN
4348 ---------------------------------------------------------------------------
4352 -- Bug 2105483 : rather then calling arp_global at the start
4349 l_debug_mesg := 'Insert call topic notes';
4350 ---------------------------------------------------------------------------
4351
4353 -- of the package, where it can error out NOCOPY since org_id is not yet set,
4354 -- do the call right before it is needed
4355 arp_global.init_global;
4356
4357 -- call a server side package
4358
4359 /* Bug 1690118 : replace FND_GLOBAL with ARP_GLOBAL */
4360
4361 l_created_by := ARP_GLOBAL.USER_ID;
4362 l_creation_date := sysdate;
4363 l_last_update_login := ARP_GLOBAL.last_update_login ;
4364 l_last_update_date := sysdate;
4365 l_last_updated_by := ARP_GLOBAL.USER_ID;
4366
4367 arp_notes_pkg.insert_cover(
4368 p_note_type => x_note_type,
4369 p_text => x_text,
4370 p_customer_call_id => null,
4371 p_customer_call_topic_id => null,
4372 p_call_action_id => NULL,
4373 p_customer_trx_id => x_customer_trx_id,
4374 p_note_id => x_note_id,
4375 p_last_updated_by => l_last_updated_by,
4376 p_last_update_date => l_last_update_date,
4377 p_last_update_login => l_last_update_login,
4378 p_created_by => l_created_by,
4379 p_creation_date => l_creation_date);
4380
4381
4382 EXCEPTION
4383 WHEN OTHERS THEN
4384 x_note_id := -1;
4385 wf_core.Context('ARP_CMREQ_WF', 'InsertTrxNotes',
4386 null, null, null, l_debug_mesg);
4387 RAISE;
4388
4389 END InsertTrxNotes;
4390
4391
4392 PROCEDURE CallTrxApi(p_item_type IN VARCHAR2,
4393 p_item_key IN VARCHAR2,
4394 p_actid IN NUMBER,
4395 p_funcmode IN VARCHAR2,
4396 p_result OUT NOCOPY VARCHAR2) IS
4397
4398
4399 l_customer_trx_id number;
4400 l_amount number;
4401 l_request_id number;
4402 l_error_tab arp_trx_validate.Message_Tbl_Type;
4403 l_batch_source_name varchar2(50);
4404 l_credit_method_rules varchar2(65);
4405 l_credit_method_installments varchar2(65);
4406 l_cm_creation_error varchar2(250);
4407 l_credit_memo_number varchar2(20);
4408 l_credit_memo_id number;
4409 CRLF varchar2(1);
4410 l_status varchar2(255);
4411
4412 /* bug 1908252 */
4413 l_last_updated_by number;
4414 l_last_update_login number;
4415
4416 BEGIN
4417 SetOrgContext (p_item_key);
4418
4419 -- Bug 2105483 : rather then calling arp_global at the start
4420 -- of the package, where it can error out NOCOPY since org_id is not yet set,
4421 -- do the call right before it is needed
4422 arp_global.init_global;
4423
4424 crlf := arp_global.CRLF;
4425
4426 /* Bug 1908252 */
4427 l_last_updated_by := ARP_GLOBAL.user_id;
4428 l_last_update_login := ARP_GLOBAL.last_update_login ;
4429
4430 --
4431 -- RUN mode - normal process execution
4432 --
4433 if (p_funcmode = 'RUN') then
4434
4435 -- call transaction API here
4436
4437 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
4438 p_item_type,
4439 p_item_key,
4440 'CUSTOMER_TRX_ID');
4441
4442 l_amount := WF_ENGINE.GetItemAttrNumber(
4443 p_item_type,
4444 p_item_key,
4445 'ORIGINAL_TOTAL');
4446
4447 l_request_id := WF_ENGINE.GetItemAttrNumber(
4448 p_item_type,
4449 p_item_key,
4450 'WORKFLOW_DOCUMENT_ID');
4451
4452 l_batch_source_name := WF_ENGINE.GetItemAttrText(
4453 p_item_type,
4454 p_item_key,
4455 'BATCH_SOURCE_NAME');
4456
4457
4458 l_credit_method_installments := WF_ENGINE.GetItemAttrText(
4459 p_item_type,
4460 p_item_key,
4461 'CREDIT_INSTALLMENT_RULE');
4462
4463 l_credit_method_rules := WF_ENGINE.GetItemAttrText(
4464 p_item_type,
4465 p_item_key,
4466 'CREDIT_ACCOUNTING_RULE');
4467
4468 l_cm_creation_error := NULL;
4469
4470 IF l_batch_source_name IS NULL THEN
4471
4472 fnd_message.set_name('AR', 'AR_WF_NO_BATCH');
4473 l_cm_creation_error := fnd_message.get;
4474
4475 WF_ENGINE.SetItemAttrText(p_item_type,
4476 p_item_key,
4477 'CM_CREATION_ERROR',
4478 l_cm_creation_error);
4479
4480
4481 p_result := 'COMPLETE:F';
4482 return;
4486 then
4483 END IF;
4484
4485 if (l_credit_method_installments = 'N')
4487 l_credit_method_installments := NULL;
4488 end if;
4489
4490
4491 if (l_credit_method_rules = 'N')
4492 then
4493 l_credit_method_rules := NULL;
4494 end if;
4495
4496 -- bug 2290738 : add p_status
4497 arw_cmreq_cover.ar_autocreate_cm(
4498 p_request_id => l_request_id,
4499 p_batch_source_name => l_batch_source_name,
4500 p_credit_method_rules => l_credit_method_rules,
4501 p_credit_method_installments => l_credit_method_installments,
4502 p_error_tab => l_error_tab,
4503 p_status => l_status);
4504 l_cm_creation_error := NULL;
4505
4506
4507 begin
4508 select cm_customer_trx_id
4509 into l_credit_memo_id
4510 from ra_cm_requests
4511 where request_id = l_request_id;
4512 exception
4513 when others then
4514 p_result := 'COMPLETE:F';
4515 l_cm_creation_error := 'Could not find the request';
4516 WF_ENGINE.SetItemAttrText(p_item_type,
4517 p_item_key,
4518 'CM_CREATION_ERROR',
4519 l_cm_creation_error);
4520 return;
4521 end;
4522
4523
4524
4525 -- IF l_error_tab.count = 0 THEN
4526 IF (l_credit_memo_id is not null) THEN
4527 p_result := 'COMPLETE:T';
4528
4529 /* Bug 1908252 : update last_update* fields */
4530 update ra_cm_requests
4531 set status='COMPLETE',
4532 approval_date = SYSDATE,
4533 last_updated_by = l_last_updated_by,
4534 last_update_date = SYSDATE,
4535 last_update_login = l_last_update_login
4536 where request_id = p_item_key;
4537
4538 /*commit;*/
4539
4540 begin
4541 select trx_number
4542 into l_credit_memo_number
4543 from ra_customer_trx
4544 where customer_trx_id = l_credit_memo_id;
4545
4546 WF_ENGINE.SetItemAttrText(p_item_type,
4547 p_item_key,
4548 'CREDIT_MEMO_NUMBER',
4549 l_credit_memo_number);
4550
4551
4552 exception
4553 when others then
4554 p_result := 'COMPLETE:F';
4555 l_cm_creation_error := 'Could not find the credit memo';
4556 WF_ENGINE.SetItemAttrText(p_item_type,
4557 p_item_key,
4558 'CM_CREATION_ERROR',
4559 l_cm_creation_error);
4560 return;
4561 end;
4562
4563 ELSE
4564 FOR i IN 1..l_error_tab.COUNT LOOP
4565 l_cm_creation_error := l_cm_creation_error || l_error_tab(i).translated_message || CRLF;
4566 END LOOP;
4567
4568 WF_ENGINE.SetItemAttrText(p_item_type,
4569 p_item_key,
4570 'CM_CREATION_ERROR',
4571 l_cm_creation_error);
4572
4573 /* Bug 1908252 : update last_update* fields */
4574 update ra_cm_requests
4575 set status='APPROVED_PEND_COMP',
4576 approval_date = SYSDATE,
4577 last_updated_by = l_last_updated_by,
4578 last_update_date = SYSDATE,
4579 last_update_login = l_last_update_login
4580 where request_id = p_item_key;
4581
4582 p_result := 'COMPLETE:F';
4583 END IF;
4584
4585 return;
4586
4587 end if; -- end of run mode
4588
4589 --
4590 -- CANCEL mode
4591 --
4592 -- This is an event point is called with the effect of the activity must
4593 -- be undone, for example when a process is reset to an earlier point
4594 -- due to a loop back.
4595 --
4596 if (p_funcmode = 'CANCEL') then
4597
4598 -- no result needed
4599 p_result := 'COMPLETE:';
4600 return;
4601 end if;
4602
4603
4604 --
4605 -- Other execution modes may be created in the future. Your
4606 -- activity will indicate that it does not implement a mode
4607 -- by returning null
4608 --
4609 p_result := '';
4610 return;
4611
4612 exception
4613 when others then
4614 -- The line below records this function call in the error system
4615 -- in the case of an exception.
4616 wf_core.context('ARP_CMREQ_WF', 'CallTrxApi',
4617 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
4618 raise;
4619
4620 end CallTrxApi;
4621
4622 PROCEDURE CheckCreditMethods(p_item_type IN VARCHAR2,
4623 p_item_key IN VARCHAR2,
4624 p_actid IN NUMBER,
4625 p_funcmode IN VARCHAR2,
4626 p_result OUT NOCOPY VARCHAR2) IS
4627
4628 l_debug_mesg varchar2(240);
4629
4630 l_customer_trx_id number;
4631 l_credit_installment_rule varchar2(65);
4632 l_credit_accounting_rule varchar2(65);
4633 l_invalid_rule_value varchar2(80);
4637
4634 l_invalid_rule_mesg varchar2(2000);
4635 l_count number;
4636 l_invoicing_rule_id number;
4638 begin
4639 --uncommented for 5410467
4640 SetOrgContext (p_item_key);
4641 --
4642 -- RUN mode - normal process execution
4643 --
4644 if (p_funcmode = 'RUN') then
4645
4646 ------------------------------------------------------------
4647 l_debug_mesg := 'Get the user value of rules';
4648 ------------------------------------------------------------
4649
4650 l_customer_trx_id := WF_ENGINE.GetItemAttrNumber(
4651 p_item_type,
4652 p_item_key,
4653 'CUSTOMER_TRX_ID');
4654
4655
4656 l_credit_installment_rule := WF_ENGINE.GetItemAttrText(
4657 p_item_type,
4658 p_item_key,
4659 'CREDIT_INSTALLMENT_RULE');
4660
4661 l_credit_accounting_rule := WF_ENGINE.GetItemAttrText(
4662 p_item_type,
4663 p_item_key,
4664 'CREDIT_ACCOUNTING_RULE');
4665
4666 l_invalid_rule_value := WF_ENGINE.GetItemAttrText(
4667 p_item_type,
4668 p_item_key,
4669 'INVALID_RULE_VALUE');
4670
4671 l_invalid_rule_mesg := WF_ENGINE.GetItemAttrText(
4672 p_item_type,
4673 p_item_key,
4674 'INVALID_RULE_MESG');
4675
4676 SELECT COUNT(*) INTO l_count
4677 FROM ra_terms_lines
4678 WHERE term_id = (SELECT term_id FROM ra_customer_trx
4679 WHERE customer_trx_id = l_customer_trx_id);
4680
4681
4682 -- the l_count will always be >= 1, and the credit installment_rule is
4683 -- required for count > 1.
4684
4685 if l_count > 1 then
4686
4687 if l_credit_installment_rule not in ('LIFO', 'FIFO', 'PRORATE') then
4688 -- invalid credit method
4689 WF_ENGINE.SetItemAttrText(p_item_type,
4690 p_item_key,
4691 'INVALID_RULE_MESG',
4692 l_invalid_rule_value);
4693 p_result := 'COMPLETE:F';
4694 return;
4695 end if;
4696 end if;
4697
4698 SELECT invoicing_rule_id INTO l_invoicing_rule_id
4699 FROM ra_customer_trx
4700 WHERE customer_trx_id = l_customer_trx_id;
4701
4702 if l_invoicing_rule_id is not NULL then
4703
4704 if l_credit_accounting_rule not in ('LIFO', 'PRORATE','UNIT') then
4705 -- invalid credit method
4706 WF_ENGINE.SetItemAttrText(p_item_type,
4707 p_item_key,
4708 'INVALID_RULE_MESG',
4709 l_invalid_rule_value);
4710 p_result := 'COMPLETE:F';
4711 return;
4712 end if;
4713 end if;
4714
4715 -- the credit methods are valid
4716 if l_invalid_rule_mesg is not NULL then
4717 l_invalid_rule_mesg := NULL;
4718 WF_ENGINE.SetItemAttrText(p_item_type,
4719 p_item_key,
4720 'INVALID_RULE_MESG',
4721 l_invalid_rule_mesg);
4722 end if;
4723
4724
4725
4726
4727 p_result := 'COMPLETE:T';
4728 return;
4729
4730 end if; -- end of run mode
4731
4732 --
4733 -- CANCEL mode
4734 --
4735
4736 if (p_funcmode = 'CANCEL') then
4737
4738 -- no result needed
4739 p_result := 'COMPLETE:';
4740 return;
4741 end if;
4742
4743
4744 --
4745 -- Other execution modes
4746 --
4747 p_result := '';
4748 return;
4749
4750 exception
4751 when others then
4752 -- The line below records this function call in the error system
4753 -- in the case of an exception.
4754 wf_core.context('ARP_CMREQ_WF', 'CheckCreditMethods',
4755 p_item_type, p_item_key, to_char(p_actid), p_funcmode);
4756 raise;
4757
4758 end CheckCreditMethods;
4759
4760 -- Bug 1365263 Workflow does not set the org_id based on the user's profile.
4761 -- When workflow connects to the database and executes a package, the default
4762 -- org is set to what is at the site level. Our selects from multi org views
4763 -- fail due to this when the org is different from what is at the site level.
4764 -- The org context has to be set to the org under the which the
4765 -- the transaction was created.
4766
4767 PROCEDURE SetOrgContext (p_item_key IN VARCHAR2) IS
4768
4769 l_org_id number;
4770 l_debug_mesg varchar2(240);
4771
4772 BEGIN
4773
4774 select org_id into l_org_id
4775 from ra_cm_requests_all
4776 where request_id = p_item_key;
4777 ----------------------------------------------------------
4778 l_debug_mesg := 'Get the org_id for the credit memo request';
4779 ----------------------------------------------------------
4780
4781 --commented code below for 5410467 instead introduced mo_global.set_policy_context
4782 -- fnd_client_info.set_org_context (l_org_id);
4783 mo_global.set_policy_context('S', l_org_id);
4784
4785 exception
4786 when others then
4787 wf_core.Context('ARP_CMREQ_WF', 'SetContext',
4788 null, null, null, l_debug_mesg);
4789 raise;
4790
4791 END SetOrgContext;
4792
4793 end ARP_CMREQ_WF;