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