[Home] [Help]
PACKAGE BODY: APPS.ARI_UTILITIES
Source
1 PACKAGE BODY ARI_UTILITIES AS
2 /* $Header: ARIUTILB.pls 120.22.12010000.6 2009/01/15 11:56:24 nkanchan ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARI_UTILITIES';
8 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9
10 G_PRV_ADDRESS_ID HZ_CUST_ACCT_SITES.CUST_ACCT_SITE_ID%TYPE := 0;
11 G_BILL_TO_SITE_USE_ID HZ_CUST_SITE_USES.SITE_USE_ID%TYPE := 0;
12 G_PRV_SITE_USES VARCHAR2(2000);
13
14
15 FUNCTION check_external_user_access (p_person_party_id IN VARCHAR2,
16 p_customer_id IN VARCHAR2,
17 p_customer_site_use_id IN VARCHAR2) RETURN VARCHAR2 IS
18 user_access VARCHAR2(1) ;
19 CURSOR customer_assigned_cur(p_customer_id IN VARCHAR2, p_person_party_id IN VARCHAR2) IS
20 SELECT 'Y'
21 INTO user_access
22 FROM dual
23 WHERE p_customer_id IN (SELECT cust_account_id
24 FROM ar_customers_assigned_v
25 WHERE party_id = p_person_party_id);
26 CURSOR customer_site_assigned_cur(p_person_party_id IN VARCHAR2, p_customer_site_use_id IN VARCHAR2) IS
27 SELECT 'Y'
28 FROM ar_sites_assigned_v a,HZ_CUST_SITE_USES b
29 where a.cust_acct_site_id = b.cust_acct_site_id
30 and b.SITE_USE_CODE = 'BILL_TO'
31 AND party_id = p_person_party_id and site_use_id = p_customer_site_use_id;
32
33 CURSOR customer_acc_site_cur(p_person_party_id IN VARCHAR2, p_customer_site_use_id IN VARCHAR2) IS
34 SELECT 'Y'
35 FROM ar_customers_assigned_v Custs_assigned,
36 hz_cust_acct_sites Site,HZ_CUST_SITE_USES site_uses
37 WHERE Custs_assigned.party_id = p_person_party_id
38 AND Site.cust_account_id =
39 Custs_assigned.cust_account_id
40 and Site.cust_acct_site_id =
41 site_uses.cust_acct_site_id
42 and site_uses.SITE_USE_CODE = 'BILL_TO' and site_uses.SITE_USE_ID = p_customer_site_use_id;
43
44
45 customer_assigned_rec customer_assigned_cur%ROWTYPE;
46 customer_site_assigned_rec customer_site_assigned_cur%ROWTYPE;
47 customer_acc_site_rec customer_acc_site_cur%ROWTYPE;
48
49 BEGIN
50
51 OPEN customer_assigned_cur(p_customer_id, p_person_party_id);
52 FETCH customer_assigned_cur INTO customer_assigned_rec;
53
54 IF customer_assigned_cur%FOUND THEN
55 user_access := 'Y';
56 ELSE
57 OPEN customer_site_assigned_cur(p_person_party_id, p_customer_site_use_id);
58 FETCH customer_site_assigned_cur INTO customer_site_assigned_rec;
59 IF customer_site_assigned_cur%FOUND THEN
60 user_access := 'Y';
61 ELSE
62 OPEN customer_acc_site_cur(p_person_party_id, p_customer_site_use_id);
63 FETCH customer_acc_site_cur INTO customer_acc_site_rec;
64 IF customer_acc_site_cur%FOUND THEN
65 user_access := 'Y';
66 END IF;
67 END IF;
68 END IF;
69
70 IF user_access is not null
71 then
72 return 'Y' ;
73 end if ;
74
75 return 'N';
76
77 EXCEPTION WHEN OTHERS THEN
78 return 'N' ;
79
80 END check_external_user_access;
81 /*============================================================
82 | PUBLIC procedure send_notification
83 |
84 | DESCRIPTION
85 | Send single Workflow notification for multiple print requests
86 | submitted through iReceivables
87 |
88 | PSEUDO CODE/LOGIC
89 |
90 | PARAMETERS
91 | p_user_name IN VARCHAR2
92 | p_customer_name IN VARCHAR2
93 | p_request_id IN NUMBER
94 | p_requests IN NUMBER
95 | p_parameter IN VARCHAR2
96 | p_subject_msg_name IN VARCHAR2
97 | p_subject_msg_appl IN VARCHAR2 DEFAULT 'AR'
98 | p_body_msg_name IN VARCHAR2 DEFAULT NULL
99 | p_body_msg_appl In VARCHAR2 DEFAULT 'AR'
100 |
101 | KNOWN ISSUES
102 |
103 |
104 |
105 | NOTES
106 |
107 |
108 |
109 | MODIFICATION HISTORY
110 | Date Author Description of Changes
111 | 19-OCT-2004 vnb Created
112 +============================================================*/
113
114 PROCEDURE send_notification(p_user_name IN VARCHAR2,
115 p_customer_name IN VARCHAR2,
116 p_request_id IN NUMBER,
117 p_requests IN NUMBER,
118 p_parameter IN VARCHAR2,
119 p_subject_msg_name IN VARCHAR2,
120 p_subject_msg_appl IN VARCHAR2 DEFAULT 'AR',
121 p_body_msg_name IN VARCHAR2 DEFAULT NULL,
122 p_body_msg_appl In VARCHAR2 DEFAULT 'AR') IS
123
124 l_subject varchar2(2000);
125 l_body varchar2(2000);
126
127 l_procedure_name VARCHAR2(50);
128 l_debug_info VARCHAR2(200);
129
130 BEGIN
131
132 l_procedure_name := '.send_notification';
133
134 ----------------------------------------------------------------------------------------
135 l_debug_info := 'Fetch the message used as the confirmation message subject';
136 -----------------------------------------------------------------------------------------
137 IF (PG_DEBUG = 'Y') THEN
138 arp_standard.debug(l_debug_info);
139 END IF;
140 FND_MESSAGE.SET_NAME (p_subject_msg_appl, p_subject_msg_name);
141 FND_MESSAGE.set_token('CUSTOMER_NAME',p_customer_name);
142 l_subject := FND_MESSAGE.get;
143
144 /*----------------------------------------------------------------------------------------
145 l_debug_info := 'Fetch the message used as the confirmation message body';
146 -----------------------------------------------------------------------------------------
147 IF (PG_DEBUG = 'Y') THEN
148 arp_standard.debug(l_debug_info);
149 END IF;
150 FND_MESSAGE.SET_NAME (p_body_msg_appl, p_body_msg_name);
151 l_body := FND_MESSAGE.get;*/
152
153 ----------------------------------------------------------------------------------------
154 l_debug_info := 'Create a Workflow process for sending iReceivables Print Notification(ARIPRNTF)';
155 -----------------------------------------------------------------------------------------
156 IF (PG_DEBUG = 'Y') THEN
157 arp_standard.debug(l_debug_info);
158 END IF;
159 WF_ENGINE.CREATEPROCESS('ARIPRNTF',
160 p_request_id,
161 'ARI_PRINT_NOTIFICATION_PROCESS');
162
163 /*------------------------------------------------------------------+
164 | Set the notification subject to the message fetched previously |
165 +------------------------------------------------------------------*/
166 WF_ENGINE.SetItemAttrText('ARIPRNTF',
167 p_request_id,
168 'ARI_MSG_SUBJ',
169 l_subject);
170
171 /*---------------------------------------------------------------+
172 | Set the notification body to the message fetched previously |
173 +---------------------------------------------------------------*/
174 /*WF_ENGINE.SetItemAttrText('ARIPRNTF',
175 p_request_id,
176 'AR_MESSAGE_BODY',
177 l_body);*/
178
179 /*-----------------------------------------------------------+
180 | Set the recipient to the user name passed in as parameter |
181 +-----------------------------------------------------------*/
182 WF_ENGINE.SetItemAttrText('ARIPRNTF',
183 p_request_id,
184 'ARI_MSG_RECIPIENT',
185 p_user_name);
186
187 /*-----------------------------------------------------------+
188 | Set the sender to System Administrator's role |
189 | Check Workflow ER 3720065 |
190 +-----------------------------------------------------------*/
191 WF_ENGINE.SetItemAttrText('ARIPRNTF',
192 p_request_id,
193 '#FROM_ROLE',
194 'SYSADMIN');
195
196 /*-----------------------------------------------------------+
197 | Set the customer name attribute |
198 +-----------------------------------------------------------*/
199 WF_ENGINE.SetItemAttrText('ARIPRNTF',
200 p_request_id,
201 'ARI_NOTIFICATION_CUSTOMER_NAME',
202 p_customer_name);
203
204 /*-----------------------------------------------------------+
205 | Set the current concurrent request id |
206 +-----------------------------------------------------------*/
207 WF_ENGINE.SetItemAttrText('ARIPRNTF',
208 p_request_id,
209 'ARI_NOTIFICATION_CONC_REQ_ID',
210 p_request_id);
211
212 /*-----------------------------------------------------------+
213 | Set the number of requests |
214 +-----------------------------------------------------------*/
215 WF_ENGINE.SetItemAttrText('ARIPRNTF',
216 p_request_id,
217 'ARI_NOTIFICATION_NUM_REQUESTS',
218 p_requests);
219
220 /*------------------------------------------------------------------+
221 | Set the URL param for the embedded framework region |
222 +------------------------------------------------------------------*/
223 WF_ENGINE.SetItemAttrText('ARIPRNTF',
224 p_request_id,
225 'ARI_NOTIFICATION_REQUEST_IDS',
226 p_parameter);
227
228 ----------------------------------------------------------------------------------------
229 l_debug_info := 'Start the notification process';
230 -----------------------------------------------------------------------------------------
231 IF (PG_DEBUG = 'Y') THEN
232 arp_standard.debug(l_debug_info);
233 END IF;
234 WF_ENGINE.STARTPROCESS('ARIPRNTF',
235 p_request_id);
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 IF (PG_DEBUG = 'Y') THEN
240 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
241 arp_standard.debug(' - No of Requests: '||p_requests);
242 arp_standard.debug(' - User Name : '||p_user_name);
243 arp_standard.debug(' - Customer Name : '||p_customer_name);
244 arp_standard.debug(' - Requests List : '||p_parameter);
245 arp_standard.debug(' - Concurrent Request Id : '||p_request_id);
246 arp_standard.debug('ERROR =>'|| SQLERRM);
247 END IF;
248
249 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
250 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
251 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
252 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
253 FND_MSG_PUB.ADD;
254
255 END send_notification;
256
257 /*========================================================================
258 | PUBLIC function curr_round_amt
259 |
260 | DESCRIPTION
261 | Rounds a given amount based on the precision defined for the currency code.
262 | ----------------------------------------
263 |
264 | PSEUDO CODE/LOGIC
265 | This function rounds the amount based on the precision defined for the
266 | currency code.
267 |
268 | PARAMETERS
269 | p_amount IN NUMBER Input amount for rounding
270 | p_currency_code IN VARCHAR2 Currency Code
271 |
272 | RETURNS
273 | l_return_amt NUMBER Rounded Amount
274 |
275 | KNOWN ISSUES
276 |
277 | NOTES
278 |
279 | MODIFICATION HISTORY
280 | Date Author Description of Changes
281 | 17-DEC-2004 vnb Created
282 |
283 *=======================================================================*/
284 FUNCTION curr_round_amt( p_amount IN NUMBER,
285 p_currency_code IN VARCHAR2)
286 RETURN NUMBER IS
287 l_return_amt NUMBER;
288 l_precision NUMBER;
289 l_ext_precision NUMBER;
290 l_min_acct_unit NUMBER;
291
292 l_procedure_name VARCHAR2(50);
293 l_debug_info VARCHAR2(200);
294
295 BEGIN
296 l_return_amt := p_amount;
297 l_precision := 2;
298 l_procedure_name := '.round_amount_currency';
299
300 ---------------------------------------------------------------------------
301 l_debug_info := 'Get precision information for the active currency';
302 ---------------------------------------------------------------------------
303 FND_CURRENCY_CACHE. GET_INFO(
304 currency_code => p_currency_code, /* currency code */
305 precision => l_precision, /* number of digits to right of decimal */
306 ext_precision => l_ext_precision, /* precision where more precision is needed */
307 min_acct_unit => l_min_acct_unit /* minimum value by which amt can vary */
308 );
309
310 IF (PG_DEBUG = 'Y') THEN
311 arp_standard.debug('- Currency Code: '||p_currency_code);
312 arp_standard.debug('- Precision: '||l_precision);
313 arp_standard.debug('- Extended Precision: '||l_ext_precision);
317 ---------------------------------------------------------------------------
314 arp_standard.debug('- Minimum Accounting Unit: '||l_min_acct_unit);
315 END IF;
316
318 l_debug_info := 'Round the input amount based on the precision information';
319 ---------------------------------------------------------------------------
320 l_return_amt := round(p_amount,l_precision);
321
322 IF (PG_DEBUG = 'Y') THEN
323 arp_standard.debug('- Unrounded Amount: '||p_amount);
324 arp_standard.debug('- Rounded Amount: '||l_return_amt);
325 END IF;
326
327 RETURN l_return_amt;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 IF (PG_DEBUG = 'Y') THEN
332 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
333 arp_standard.debug('Input Amount: '||p_amount);
334 arp_standard.debug('Rounded Amount: '||l_return_amt);
335 arp_standard.debug('Currency: '||p_currency_code);
336 arp_standard.debug('Precision: '||l_precision);
337 arp_standard.debug('ERROR =>'|| SQLERRM);
338 END IF;
339
340 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
341 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
342 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
343 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
344 FND_MSG_PUB.ADD;
345
346 RETURN l_return_amt;
347
348 END;
349
350 /*========================================================================
351 | get_lookup_meaning function returns the lookup meaning of lookup code |
352 | in user specific language. |
353 *=======================================================================*/
354 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
355 p_lookup_code IN VARCHAR2)
356 RETURN VARCHAR2 IS
357 l_meaning ar_lookups.meaning%TYPE;
358 l_hash_value NUMBER;
359 l_procedure_name VARCHAR2(50);
360 l_debug_info VARCHAR2(200);
361
362 BEGIN
363 l_procedure_name := '.get_lookup_meaning';
364 ----------------------------------------------------------------------------------------
365 l_debug_info := 'Fetch hash value by sending lookup code, type and user env language';
366 -----------------------------------------------------------------------------------------
367 IF (PG_DEBUG = 'Y') THEN
368 arp_standard.debug(l_debug_info);
369 END IF;
370
371 IF p_lookup_code IS NOT NULL AND
372 p_lookup_type IS NOT NULL THEN
373
374 l_hash_value := DBMS_UTILITY.get_hash_value(
375 p_lookup_type||'@*?'||p_lookup_code||USERENV('LANG'),
376 1000,
377 25000);
378
379 IF pg_ar_lookups_rec.EXISTS(l_hash_value) THEN
380 l_meaning := pg_ar_lookups_rec(l_hash_value);
381 ELSE
382
383 SELECT meaning
384 INTO l_meaning
385 FROM ar_lookups
386 WHERE lookup_type = p_lookup_type
387 AND lookup_code = p_lookup_code;
388
389 ----------------------------------------------------------------------------------------
390 l_debug_info := 'Setting lookup meaning into page lookups rec';
391 -----------------------------------------------------------------------------------------
392 IF (PG_DEBUG = 'Y') THEN
393 arp_standard.debug(l_debug_info);
394 END IF;
395
396 pg_ar_lookups_rec(l_hash_value) := l_meaning;
397
398 END IF;
399
400 END IF;
401
402 return(l_meaning);
403
404 EXCEPTION
405 WHEN no_data_found THEN
406 return(null);
407 WHEN OTHERS THEN
408 IF (PG_DEBUG = 'Y') THEN
409 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
410 arp_standard.debug('ERROR =>'|| SQLERRM);
411 arp_standard.debug('Debug Info : '||l_debug_info);
412 END IF;
413
414 END;
415
416
417 FUNCTION get_bill_to_site_use_id (p_address_id IN NUMBER) RETURN NUMBER AS
418 l_procedure_name VARCHAR2(50);
419 l_debug_info VARCHAR2(200);
420 --
421 BEGIN
422 l_procedure_name := '.get_bill_to_site_use_id';
423 ----------------------------------------------------------------------------------------
424 l_debug_info := 'Fetch site use id';
425 -----------------------------------------------------------------------------------------
426 IF (PG_DEBUG = 'Y') THEN
427 arp_standard.debug(l_debug_info);
428 END IF;
429
430 IF G_PRV_ADDRESS_ID <> p_address_id THEN
431 G_PRV_ADDRESS_ID := p_address_id;
432 G_PRV_SITE_USES := get_site_uses(p_address_id);
433 END IF;
434
435 RETURN(G_BILL_TO_SITE_USE_ID);
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 IF (PG_DEBUG = 'Y') THEN
440 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
441 arp_standard.debug('ERROR =>'|| SQLERRM);
442 arp_standard.debug('Debug Info : '||l_debug_info);
443 END IF;
444
445
446
447 END;
448
449
450 FUNCTION get_site_uses (p_address_id IN NUMBER) RETURN VARCHAR2 AS
451 --
455 --
452 l_site_uses VARCHAR2(4000) := '';
453 --
454 l_separator VARCHAR2(2) := '';
456 CURSOR c01 (addr_id VARCHAR2) IS
457 SELECT
458 SITE_USE_CODE, SITE_USE_ID
459 FROM
460 hz_cust_site_uses
461 WHERE
462 cust_acct_site_id = addr_id;
463 --AND status = 'A' ;
464 /*Bug 6503280: Commented out above condition on checking status='A'
465 * to allow Drill Down from Inactive Sites from Customer Search Page*/
466 l_procedure_name VARCHAR2(50);
467 l_debug_info VARCHAR2(200);
468 --
469 BEGIN
470 --
471 G_BILL_TO_SITE_USE_ID := 0;
472 --
473 l_procedure_name := '.get_site_uses';
474 ----------------------------------------------------------------------------------------
475 l_debug_info := 'Fetch Bill to Site use id';
476 -----------------------------------------------------------------------------------------
477 IF (PG_DEBUG = 'Y') THEN
478 arp_standard.debug(l_debug_info);
479 END IF;
480
481 FOR c01_rec IN c01 (p_address_id) LOOP
482 l_site_uses := l_site_uses || l_separator || site_use_meaning(c01_rec.site_use_code);
483
484 IF c01_rec.site_use_code = 'BILL_TO' THEN
485 G_BILL_TO_SITE_USE_ID := c01_rec.site_use_id;
486 END IF;
487
488 IF l_separator IS NULL THEN
489 l_separator := ', ';
490 END IF;
491
492 END LOOP;
493 --
494 RETURN l_site_uses;
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 IF (PG_DEBUG = 'Y') THEN
499 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
500 arp_standard.debug('ERROR =>'|| SQLERRM);
501 arp_standard.debug('Debug Info : '||l_debug_info);
502 END IF;
503
504
505
506 END;
507
508
509 FUNCTION site_use_meaning (p_site_use IN VARCHAR2) RETURN VARCHAR2 AS
510 --
511 l_meaning VARCHAR2(80);
512 l_procedure_name VARCHAR2(50);
513 l_debug_info VARCHAR2(200);
514 --
515 BEGIN
516
517 l_procedure_name := '.site_use_meaning';
518 ----------------------------------------------------------------------------------------
519 l_debug_info := 'Fetch lookup meaning for site use';
520 -----------------------------------------------------------------------------------------
521 IF (PG_DEBUG = 'Y') THEN
522 arp_standard.debug(l_debug_info);
523 END IF;
524
525 l_meaning := get_lookup_meaning('SITE_USE_CODE', p_site_use);
526
527 RETURN l_meaning;
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 IF (PG_DEBUG = 'Y') THEN
532 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
533 arp_standard.debug('ERROR =>'|| SQLERRM);
534 arp_standard.debug('Debug Info : '||l_debug_info);
535 END IF;
536
537 END;
538
539 /*========================================================================
540 | PUBLIC function cust_srch_sec_predicate
541 |
542 | DESCRIPTION
543 | Security predicate for internal customer search in iReceivables.
544 | This is to ensure the 'All Locations'(which has org_id = -1) record gets picked up.
545 |
546 | PARAMETERS
547 | obj_schema IN VARCHAR2 Object Schema
548 | obj_name IN VARCHAR2 Object Name
549 |
550 | RETURNS
551 | Where clause to be appended to the object.
552 |
553 | MODIFICATION HISTORY
554 | Date Author Description of Changes
555 | 24-MAY-2005 vnb Created
556 |
557 *=======================================================================*/
558 FUNCTION cust_srch_sec_predicate(obj_schema VARCHAR2,
559 obj_name VARCHAR2) RETURN VARCHAR2
560 IS
561 BEGIN
562 RETURN 'EXISTS (SELECT 1
563 FROM mo_glob_org_access_tmp oa
564 WHERE oa.organization_id = org_id
565 OR org_id = -1)';
566 END cust_srch_sec_predicate;
567
568 /*========================================================================
569 | PUBLIC function get_default_currency
570 |
571 | DESCRIPTION
572 | Function returns the first currency set up in the customer/site profiles.
573 | If no currency is set up for the customer, it pickes up from the Set of Books.
574 |
575 | PARAMETERS
576 | p_customer_id IN VARCHAR2
577 | p_customer_site_use_id IN VARCHAR2
578 |
579 | RETURNS
580 | Default Currency Code
581 |
582 | MODIFICATION HISTORY
583 | Date Author Description of Changes
584 | 19-MAY-2005 vnb Created
585 | 08-JUN-2005 vnb Bug 4417906 - Cust Label has extra line spacing
586 | 20-JUL-2005 rsinthre Bug 4488421 - Remove reference to obsolete TCA views
587 *=======================================================================*/
588 FUNCTION get_default_currency ( p_customer_id IN VARCHAR2,
589 p_session_id IN VARCHAR2)
590
591 RETURN VARCHAR2
592 IS
593 l_default_currency VARCHAR2(15);
594 l_default_org_id NUMBER(15,0);
595 l_profile_default_currrency VARCHAR2(15);
599
596 l_currency_exist NUMBER(4);
597 BEGIN
598 l_profile_default_currrency := FND_PROFILE.value('OIR_DEFAULT_CURRENCY_CODE');
600 IF(p_customer_id IS NULL) THEN
601
602 select count(*) into l_currency_exist from dual where l_profile_default_currrency in
603 ( SELECT unique ( CUR.CURRENCY_CODE ) FROM HZ_CUST_PROFILE_AMTS CPA,
604 FND_CURRENCIES_VL CUR, HZ_CUSTOMER_PROFILES CPF, ar_irec_user_acct_sites_all AUAS
605 WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
606 AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
607 AND CPF.CUST_ACCOUNT_ID =AUAS.CUSTOMER_ID
608 AND (
609 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
610 OR
611 CPF.SITE_USE_ID IS NULL
612 )
613 AND AUAS.user_id=FND_GLOBAL.USER_ID()
614 AND AUAS.session_id=p_session_id);
615 if( l_currency_exist > 0 ) then
616 return l_profile_default_currrency;
617 end if;
618
619 SELECT unique ( CUR.CURRENCY_CODE )
620 INTO l_default_currency
621 FROM HZ_CUST_PROFILE_AMTS CPA,
622 FND_CURRENCIES_VL CUR,
623 HZ_CUSTOMER_PROFILES CPF,
624 ar_irec_user_acct_sites_all AUAS
625 WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
626 AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
627 AND CPF.CUST_ACCOUNT_ID =AUAS.CUSTOMER_ID
628 AND (
629 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
630 OR
631
632 CPF.SITE_USE_ID IS NULL
633 )
634 AND AUAS.user_id=FND_GLOBAL.USER_ID()
635 AND AUAS.session_id=p_session_id
636 AND ROWNUM = 1;
637 ELSE
638
639 select count(*) into l_currency_exist from dual where l_profile_default_currrency in
640 ( SELECT unique ( CUR.CURRENCY_CODE ) FROM HZ_CUST_PROFILE_AMTS CPA,
641 FND_CURRENCIES_VL CUR, HZ_CUSTOMER_PROFILES CPF, ar_irec_user_acct_sites_all AUAS
642 WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
643 AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
644 AND CPF.CUST_ACCOUNT_ID = p_customer_id
645 AND (
646 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
647 OR
648 CPF.SITE_USE_ID IS NULL
649 )
650 AND AUAS.user_id=FND_GLOBAL.USER_ID()
651 AND AUAS.session_id=p_session_id);
652
653 if( l_currency_exist > 0 ) then
654 return l_profile_default_currrency;
655 end if;
656
657 SELECT unique ( CUR.CURRENCY_CODE )
658 INTO l_default_currency
659 FROM HZ_CUST_PROFILE_AMTS CPA,
660 FND_CURRENCIES_VL CUR,
661 HZ_CUSTOMER_PROFILES CPF,
662 ar_irec_user_acct_sites_all AUAS
663 WHERE
664 CPA.CURRENCY_CODE = CUR.CURRENCY_CODE AND
665 CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID AND
666 CPF.CUST_ACCOUNT_ID = p_customer_id AND
667 (
668 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
669 OR
670 CPF.SITE_USE_ID IS NULL
671 )
672 AND AUAS.user_id=FND_GLOBAL.USER_ID()
673 AND AUAS.session_id=p_session_id
674 AND ROWNUM = 1;
675 END IF;
676
677 RETURN l_default_currency;
678
679 EXCEPTION
680 WHEN NO_DATA_FOUND THEN
681
682 SELECT sb.currency_code
683 INTO l_default_currency
684 FROM ar_system_parameters sys,
685 gl_sets_of_books sb
686 WHERE sb.set_of_books_id = sys.set_of_books_id;
687
688 RETURN l_default_currency;
689
690 WHEN OTHERS THEN
691 RETURN NULL;
692
693 END get_default_currency;
694
695
696 /*========================================================================
697 | PUBLIC FUNCTION check_site_access
698 |
699 | DESCRIPTION
700 | This function checks if the person party has access to the specified
701 | customer site.
702 |
703 | PARAMETERS
704 | p_person_party_id IN VARCHAR2
705 | p_customer_id IN VARCHAR2
706 | p_customer_site_use_id IN VARCHAR2
707 |
708 | NOTES
709 | This does not check access at the account level - only at this particular site.
710 |
711 | MODIFICATION HISTORY
712 | Date Author Description of Changes
713 | 09-May-2005 vnb Created
714 |
715 *=======================================================================*/
716 FUNCTION check_site_access (p_person_party_id IN VARCHAR2,
717 p_customer_id IN VARCHAR2,
718 p_customer_site_use_id IN VARCHAR2)
719 RETURN VARCHAR2
720 IS
721 user_access VARCHAR2(1) ;
722 BEGIN
723
724 SELECT 'Y'
725 INTO user_access
726 FROM dual
727 WHERE EXISTS (SELECT 'Y'
728 FROM ar_sites_assigned_v a,HZ_CUST_SITE_USES b
729 WHERE a.cust_acct_site_id = b.cust_acct_site_id
730 AND b.SITE_USE_CODE = 'BILL_TO'
731 AND party_id = p_person_party_id
732 AND site_use_id = p_customer_site_use_id );
733
737
734 IF user_access is not null THEN
735 RETURN 'Y' ;
736 END IF ;
738 RETURN 'N';
739
740 EXCEPTION WHEN OTHERS THEN
741 RETURN 'N' ;
742
743 END;
744
745 /*========================================================================
746 | PUBLIC FUNCTION check_admin_access
747 |
748 | DESCRIPTION
749 | Check if the admin identified by p_person_party_id has access to this customer.
750 |
751 | PARAMETERS
752 | p_person_party_id IN VARCHAR2
753 | p_customer_id IN VARCHAR2
754 |
755 | NOTES
756 | This does not check access at the account level - only at this particular site.
757 |
758 | MODIFICATION HISTORY
759 | Date Author Description of Changes
760 | 09-May-2005 vnb Created
761 |
762 *=======================================================================*/
763 FUNCTION check_admin_access (p_person_party_id IN VARCHAR2,
764 p_customer_id IN VARCHAR2)
765 RETURN VARCHAR2
766 IS
767 user_access VARCHAR2(1) ;
768 BEGIN
769
770 SELECT 'Y'
771 INTO user_access
772 FROM dual
773 WHERE p_customer_id IN (
774 select hca.cust_account_id
775 from hz_relationships hr,
776 hz_parties hp1,
777 hz_parties hp2,
778 hz_cust_accounts hca
779 where hr.subject_id = hp1.party_id
780 and hr.object_id = hp2.party_id
781 and subject_table_name = 'HZ_PARTIES'
782 and object_table_name = 'HZ_PARTIES'
783 and hr.relationship_type IN ( 'EMPLOYMENT', 'CONTACT')
784 and hr.subject_id = p_person_party_id
785 and hca.party_id = hp2.party_id);
786
787 IF user_access is not null THEN
788 RETURN 'Y' ;
789 END IF;
790
791 RETURN 'N';
792
793 EXCEPTION WHEN OTHERS THEN
794 RETURN 'N' ;
795
796 END;
797
798 /*========================================================================
799 | PUBLIC procedure get_contact_id
800 |
801 | DESCRIPTION
802 | Returns contact id of the given site at the customer/site level
803 | ----------------------------------------
804 |
805 | PSEUDO CODE/LOGIC
806 |
807 | PARAMETERS
808 | p_customer_id IN Customer Id
809 | p_customer_site_use_id IN Customer Site Id
810 | p_contact_role_type IN Contact Role Type
811 |
812 | RETURNS
813 | l_contact_id Contact id of the given site at the customer/site level
814 | KNOWN ISSUES
815 |
816 | MODIFICATION HISTORY
817 | Date Author Description of Changes
818 | 12-AUG-2005 rsinthre Created
819 *=======================================================================*/
820 FUNCTION get_contact_id(p_customer_id IN NUMBER,
821 p_customer_site_use_id IN NUMBER DEFAULT NULL,
822 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') RETURN NUMBER AS
823
824 l_contact_id NUMBER := null;
825
826 CURSOR contact_id_cur(p_customer_id IN NUMBER,
827 p_customer_site_use_id IN NUMBER DEFAULT NULL,
828 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') IS
829 select contact_id from (
830 select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
831 row_number() OVER ( partition by SROLES.responsibility_type , SUB.CUST_ACCT_SITE_ID order by SROLES.PRIMARY_FLAG DESC NULLS LAST, SUB.last_update_date desc) last_update_record,
832 decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
833 from hz_cust_account_roles SUB,
834 hz_role_responsibility SROLES
835 where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
836 SUB.status = 'A' AND
837 SUB.CUST_ACCOUNT_ID = p_customer_id
838 AND ( SUB.CUST_ACCT_SITE_ID = p_customer_site_use_id)
839 )
840 where last_update_record <=1
841 ORDER BY resp_code ASC, CUST_ACCT_SITE_ID ASC NULLS LAST ;
842
843 CURSOR contact_id_acct_cur(p_customer_id IN NUMBER,
844 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') IS
845 select contact_id from (
846 select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
847 row_number() OVER ( partition by SROLES.responsibility_type , SUB.CUST_ACCT_SITE_ID order by SROLES.PRIMARY_FLAG DESC NULLS LAST, SUB.last_update_date desc) last_update_record,
848 decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
849 from hz_cust_account_roles SUB,
850 hz_role_responsibility SROLES
851 where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
852 SUB.status = 'A' AND
853 SUB.CUST_ACCOUNT_ID = p_customer_id
854 AND (SUB.CUST_ACCT_SITE_ID IS NULL)
855 )
856 where last_update_record <=1
857 ORDER BY resp_code ASC, CUST_ACCT_SITE_ID ASC NULLS LAST ;
858
859 contact_id_rec contact_id_cur%ROWTYPE;
860
861 BEGIN
862
866 l_contact_id := contact_id_rec.contact_id;
863 IF(p_customer_site_use_id IS NOT NULL AND p_customer_site_use_id <> -1) THEN
864 OPEN contact_id_cur(p_customer_id, p_customer_site_use_id, p_contact_role_type);
865 FETCH contact_id_cur INTO contact_id_rec;
867 CLOSE contact_id_cur;
868 ELSE
869 OPEN contact_id_acct_cur(p_customer_id, p_contact_role_type);
870 FETCH contact_id_acct_cur INTO contact_id_rec;
871 l_contact_id := contact_id_rec.contact_id;
872 CLOSE contact_id_acct_cur;
873 END IF;
874
875 IF l_contact_id IS NOT NULL THEN
876 RETURN l_contact_id;
877 END IF;
878
879 RETURN l_contact_id;
880
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 RETURN NULL ;
884 WHEN OTHERS THEN
885 RAISE;
886 END;
887
888 /*========================================================================
889 | PUBLIC procedure get_contact
890 |
891 | DESCRIPTION
892 | Returns contact name of the given site at the customer/site level
893 | ----------------------------------------
894 |
895 | PSEUDO CODE/LOGIC
896 |
897 | PARAMETERS
898 | p_customer_id IN Customer Id
899 | p_customer_site_use_id IN Customer Site Id
900 | p_contact_role_type IN Contact Role Type
901 |
902 | RETURNS
903 | l_contact_name Contact name of the given site at the customer/site level
904 | KNOWN ISSUES
905 |
906 | MODIFICATION HISTORY
907 | Date Author Description of Changes
908 | 12-AUG-2005 rsinthre Created
909 *=======================================================================*/
910 FUNCTION get_contact(p_customer_id IN NUMBER,
911 p_customer_site_use_id IN NUMBER,
912 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 AS
913
914 l_contact_id NUMBER := NULL;
915 l_contact_name VARCHAR2(2000):= null;
916 BEGIN
917 --
918 l_contact_id := get_contact_id (p_customer_id, p_customer_site_use_id, p_contact_role_type);
919
920 IF l_contact_id IS NOT NULL THEN
921 --
922 SELECT LTRIM(substrb(PARTY.PERSON_FIRST_NAME,1,40) || ' ') ||
923 substrb(PARTY.PERSON_LAST_NAME,1,50)
924 INTO l_contact_name
925 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
926 HZ_PARTIES PARTY,
927 HZ_RELATIONSHIPS REL
928 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_contact_id
929 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
930 AND REL.SUBJECT_ID = PARTY.PARTY_ID
931 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
932 AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
933 AND DIRECTIONAL_FLAG = 'F';
934 --
935 END IF;
936
937 RETURN l_contact_name;
938
939 EXCEPTION
940 WHEN NO_DATA_FOUND THEN
941 RETURN NULL;
942 WHEN OTHERS THEN
943 RAISE;
944 END;
945
946
947 /*========================================================================
948 | PUBLIC procedure get_contact
949 |
950 | DESCRIPTION
951 | Returns contact name of the given contact id
952 | ----------------------------------------
953 |
954 | PSEUDO CODE/LOGIC
955 |
956 | PARAMETERS
957 | p_contact_id IN Customer Id
958 |
959 | RETURNS
960 | l_contact_name Contact name of the given site at the customer/site level
961 | KNOWN ISSUES
962 |
963 | MODIFICATION HISTORY
964 | Date Author Description of Changes
965 | 24-AUG-2005 rsinthre Created
966 | 11-SEP- 2008 avepati bug 7368288 For the New Customer search after running the
967 | program customer text data indexing is not showing any output
968 *=======================================================================*/
969 FUNCTION get_contact(p_contact_id IN NUMBER) RETURN VARCHAR2 AS
970 l_contact_name VARCHAR2(2000):= null;
971 BEGIN
972
973 IF p_contact_id IS NOT NULL THEN
974 SELECT LTRIM(substrb(PARTY.PERSON_FIRST_NAME,1,40) || ' ') ||
975 substrb(PARTY.PERSON_LAST_NAME,1,50)
976 INTO l_contact_name
977 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
978 HZ_PARTIES PARTY,
979 HZ_RELATIONSHIPS REL
980 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
981 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
982 AND REL.SUBJECT_ID = PARTY.PARTY_ID
983 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
984 AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
985 AND DIRECTIONAL_FLAG = 'F';
986 END IF;
987
988 RETURN l_contact_name;
989
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 RETURN NULL ;
993 WHEN OTHERS THEN
994 RAISE;
995 END;
996
997
998 /*========================================================================
999 | PUBLIC procedure get_phone
1000 |
1001 | DESCRIPTION
1002 | Returns contact point of the given contact type, site at the customer/site level
1003 | ----------------------------------------
1004 |
1005 | PSEUDO CODE/LOGIC
1006 |
1007 | PARAMETERS
1008 | p_customer_id IN Customer Id
1012 |
1009 | p_customer_site_use_id IN Customer Site Id
1010 | p_contact_role_type IN Contact Role Type
1011 | p_phone_type IN contact type like 'PHONE', 'FAX', 'GEN' etc
1013 | RETURNS
1014 | l_contact_phone Contact type number of the given site at the customer/site level
1015 | KNOWN ISSUES
1016 |
1017 | MODIFICATION HISTORY
1018 | Date Author Description of Changes
1019 | 12-AUG-2005 rsinthre Created
1020 *=======================================================================*/
1021 FUNCTION get_phone(p_customer_id IN NUMBER,
1022 p_customer_site_use_id IN NUMBER DEFAULT NULL,
1023 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL',
1024 p_phone_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 AS
1025 l_phone_id NUMBER := NULL;
1026 l_contact_id NUMBER := NULL;
1027 l_contact_phone VARCHAR2(2000):= null;
1028 CURSOR phone_id_cur(p_contact_id IN NUMBER DEFAULT NULL,
1029 p_phone_type IN VARCHAR2 DEFAULT 'ALL',
1030 p_primary_flag IN VARCHAR2 DEFAULT 'Y') IS
1031 SELECT phone_id FROM
1032 ( SELECT CONT_POINT.CONTACT_POINT_ID phone_id,
1033 row_number() OVER ( order by CONT_POINT.last_update_date desc) last_update_record
1034 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1035 HZ_CONTACT_POINTS CONT_POINT
1036 WHERE
1037 ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1038 AND ACCT_ROLE.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
1039 AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
1040 AND CONT_POINT.STATUS = 'A'
1041 AND INSTRB(NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE) || 'ALL', p_phone_type) > 0
1042 AND CONT_POINT.PRIMARY_FLAG = p_primary_flag
1043 )
1044 WHERE last_update_record<=1;
1045
1046 phone_id_rec phone_id_cur%ROWTYPE;
1047
1048 BEGIN
1049 --
1050 l_contact_id := get_contact_id (p_customer_id, p_customer_site_use_id, p_contact_role_type);
1051
1052
1053
1054 IF l_contact_id IS NOT NULL THEN
1055 --
1056 OPEN phone_id_cur(l_contact_id, p_phone_type ,'Y');
1057 FETCH phone_id_cur INTO phone_id_rec;
1058 l_phone_id := phone_id_rec.phone_id;
1059 CLOSE phone_id_cur;
1060
1061 IF l_phone_id IS NULL THEN
1062 OPEN phone_id_cur(l_contact_id, p_phone_type ,'N');
1063 FETCH phone_id_cur INTO phone_id_rec;
1064 l_phone_id := phone_id_rec.phone_id;
1065 CLOSE phone_id_cur;
1066 END IF;
1067 --
1068 END IF;
1069 --
1070 IF l_phone_id IS NOT NULL THEN
1071 --
1072 SELECT RTRIM(LTRIM(cont_point.PHONE_AREA_CODE || '-' ||
1073 DECODE(CONT_POINT.CONTACT_POINT_TYPE,'TLX',
1074 CONT_POINT.TELEX_NUMBER,
1075 CONT_POINT.PHONE_NUMBER)||'-'||
1076 CONT_POINT.PHONE_EXTENSION, '-'), '-')
1077 INTO l_contact_phone
1078 FROM HZ_CONTACT_POINTS CONT_POINT
1079 WHERE CONT_POINT.CONTACT_POINT_ID = l_phone_id;
1080 --
1081 END IF;
1082
1083 RETURN l_contact_phone;
1084
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 RAISE;
1088 END;
1089
1090
1091 /*========================================================================
1092 | PUBLIC procedure get_phone
1093 |
1094 | DESCRIPTION
1095 | Returns contact point of the given contact id
1096 | ----------------------------------------
1097 | PSEUDO CODE/LOGIC
1098 |
1099 | PARAMETERS
1100 | p_contact_id IN Customer Id
1101 | p_phone_type IN contact type like 'PHONE', 'FAX', 'GEN' etc
1102 |
1103 | RETURNS
1104 | l_contact_phone Contact type number of the given site at the customer/site level
1105 | KNOWN ISSUES
1106 |
1107 | MODIFICATION HISTORY
1108 | Date Author Description of Changes
1109 | 5-JUL-2005 hikumar Created
1110 *=======================================================================*/
1111 FUNCTION get_phone(p_contact_id IN NUMBER,
1112 p_phone_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 AS
1113 l_phone_id NUMBER := NULL;
1114 l_contact_phone VARCHAR2(2000):= null;
1115 CURSOR phone_id_cur(p_contact_id IN NUMBER DEFAULT NULL,
1116 p_phone_type IN VARCHAR2 DEFAULT 'ALL',
1117 p_primary_flag IN VARCHAR2 DEFAULT 'Y') IS
1118 SELECT phone_id FROM
1119 ( SELECT CONT_POINT.CONTACT_POINT_ID phone_id,
1120 row_number() OVER ( order by CONT_POINT.last_update_date desc) last_update_record
1121 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1122 HZ_CONTACT_POINTS CONT_POINT
1123 WHERE
1124 ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1125 AND ACCT_ROLE.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
1126 AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
1127 AND CONT_POINT.STATUS = 'A'
1128 AND INSTRB(NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE) || 'ALL', p_phone_type) > 0
1129 AND CONT_POINT.PRIMARY_FLAG = p_primary_flag
1130 )
1131 WHERE last_update_record<=1;
1132
1133 phone_id_rec phone_id_cur%ROWTYPE;
1134
1135 BEGIN
1136 --
1137 IF p_contact_id IS NOT NULL THEN
1138 --
1142 CLOSE phone_id_cur;
1139 OPEN phone_id_cur(p_contact_id, p_phone_type ,'Y');
1140 FETCH phone_id_cur INTO phone_id_rec;
1141 l_phone_id := phone_id_rec.phone_id;
1143
1144 IF l_phone_id IS NULL THEN
1145 OPEN phone_id_cur(p_contact_id, p_phone_type ,'N');
1146 FETCH phone_id_cur INTO phone_id_rec;
1147 l_phone_id := phone_id_rec.phone_id;
1148 CLOSE phone_id_cur;
1149 END IF;
1150 --
1151 END IF;
1152 --
1153 IF l_phone_id IS NOT NULL THEN
1154 --
1155 SELECT RTRIM(LTRIM(cont_point.PHONE_AREA_CODE || '-' ||
1156 DECODE(CONT_POINT.CONTACT_POINT_TYPE,'TLX',
1157 CONT_POINT.TELEX_NUMBER,
1158 CONT_POINT.PHONE_NUMBER)||'-'||
1159 CONT_POINT.PHONE_EXTENSION, '-'), '-')
1160 INTO l_contact_phone
1161 FROM HZ_CONTACT_POINTS CONT_POINT
1162 WHERE CONT_POINT.CONTACT_POINT_ID = l_phone_id;
1163 --
1164 END IF;
1165
1166 RETURN l_contact_phone;
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 RAISE;
1171 END;
1172
1173
1174
1175
1176 FUNCTION save_payment_instrument_info ( p_customer_id IN VARCHAR2,
1177 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN
1178 IS
1179 l_attr varchar2(15):=null;
1180 current_org_id NUMBER ;
1181 BEGIN
1182 -- If you do not want to save credit card info set this
1183 -- flag to false.
1184 -- Note:
1185 -- If this is set to false, you cannot use Bank Account
1186 -- to pay. Please disable bank account ACH payment method
1187
1188 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1189 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_SAVE_PAYMENT_INSTRUMENT', NULL,current_org_id) ) THEN
1190
1191 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_SAVE_PAYMENT_INSTRUMENT', 'AR', NULL, current_org_id );
1192 FUN_RULE_PUB.init_parameter_list;
1193 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1194 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1195 FUN_RULE_PUB.apply_rule('AR','ARI_SAVE_PAYMENT_INSTRUMENT');
1196 l_attr := FUN_RULE_PUB.get_string;
1197 if(l_attr is not null) then
1198 if l_attr='Y'then
1199 return true;
1200 else
1201 return false;
1202 end if;
1203 end if;
1204 ELSIF (nvl(FND_PROFILE.VALUE('OIR_SAVE_PAYMENT_INSTR_INFO'),'N') = 'N') THEN
1205 RETURN FALSE;
1206 ELSE
1207 RETURN TRUE;
1208 END IF;
1209
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 RETURN TRUE;
1213
1214 END save_payment_instrument_info;
1215
1216
1217 FUNCTION is_save_payment_instr_enabled ( p_customer_id IN VARCHAR2,
1218 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1219 IS
1220 BEGIN
1221 IF save_payment_instrument_info(p_customer_id, p_customer_site_use_id) THEN
1222 RETURN 'Y';
1223 ELSE
1224 RETURN 'N';
1225 END IF;
1226
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 RETURN 'Y';
1230
1231 END is_save_payment_instr_enabled;
1232
1233
1234
1235
1236 FUNCTION is_aging_enabled ( p_customer_id IN VARCHAR2,
1237 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1238 IS
1239 l_attr varchar2(15):=NULL;
1240 current_org_id NUMBER ;
1241 BEGIN
1242 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1243
1244 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_AGING_BUCKETS', NULL,current_org_id) ) THEN
1245
1246 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_AGING_BUCKETS', 'AR', NULL, current_org_id );
1247 FUN_RULE_PUB.init_parameter_list;
1248 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1249 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1250 FUN_RULE_PUB.apply_rule('AR','ARI_AGING_BUCKETS');
1251 l_attr := FUN_RULE_PUB.get_string;
1252
1253 IF(l_attr IS NOT NULL) THEN
1254 RETURN l_attr;
1255 ELSE
1256 RETURN (NVL(FND_PROFILE.VALUE('OIR_AGING_BUCKETS'),'0'));
1257 END IF;
1258 ELSE
1259 RETURN (NVL(FND_PROFILE.VALUE('OIR_AGING_BUCKETS'),'0'));
1260 END IF;
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 RETURN '0';
1264
1265 END is_aging_enabled;
1266
1267
1268
1269 FUNCTION multi_print_limit ( p_customer_id IN VARCHAR2,
1270 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1271 IS
1272 l_attr varchar2(15):=null;
1273 current_org_id NUMBER ;
1274 BEGIN
1275 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1276 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_MULTI_PRINT_LIMIT', NULL,current_org_id) ) THEN
1277
1278 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_MULTI_PRINT_LIMIT', 'AR', NULL, current_org_id );
1279 FUN_RULE_PUB.init_parameter_list;
1280 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1281 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1282 FUN_RULE_PUB.apply_rule('AR','ARI_MULTI_PRINT_LIMIT');
1286 ELSE
1283 l_attr := FUN_RULE_PUB.get_string;
1284 IF(l_attr IS NOT NULL) THEN
1285 RETURN l_attr;
1287 RETURN (NVL(FND_PROFILE.VALUE('OIR_BPA_MULTI_PRINT_LIMIT'),'0'));
1288 END IF;
1289 ELSE
1290 RETURN (NVL(FND_PROFILE.VALUE('OIR_BPA_MULTI_PRINT_LIMIT'),'0'));
1291 END IF;
1292
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 RETURN '0';
1296
1297 END multi_print_limit;
1298
1299
1300
1301 FUNCTION is_discount_grace_days_enabled ( p_customer_id IN VARCHAR2,
1302 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1303 IS
1304 l_attr varchar2(15):=null;
1305 current_org_id NUMBER ;
1306 BEGIN
1307 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1308 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_DISCOUNT_GRACE_DAYS', NULL,current_org_id) ) THEN
1309
1310 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_DISCOUNT_GRACE_DAYS', 'AR', NULL, current_org_id );
1311 FUN_RULE_PUB.init_parameter_list;
1312 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1313 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1314 FUN_RULE_PUB.apply_rule('AR','ARI_DISCOUNT_GRACE_DAYS');
1315 l_attr := FUN_RULE_PUB.get_string;
1316 IF(l_attr IS NOT NULL) THEN
1317 RETURN l_attr;
1318 ELSE
1319 RETURN (NVL(FND_PROFILE.VALUE('OIR_ENABLE_DISCOUNT_GRACE_DAYS'),'N'));
1320 END IF;
1321 ELSE
1322 RETURN (NVL(FND_PROFILE.VALUE('OIR_ENABLE_DISCOUNT_GRACE_DAYS'),'N'));
1323 END IF;
1324
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 RETURN 'N';
1328
1329 END is_discount_grace_days_enabled;
1330
1331
1332 FUNCTION is_service_charge_enabled ( p_customer_id IN VARCHAR2,
1333 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN boolean
1334 IS
1335 l_attr varchar2(15):=null;
1336 current_org_id NUMBER ;
1337 BEGIN
1338 -- This can be configured to return the appropriate value based on
1339 -- the service charge needs to be applied
1340
1341 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1342 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_SERVICE_CHARGE_ENABLED', NULL,current_org_id) ) THEN
1343
1344 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_SERVICE_CHARGE_ENABLED', 'AR', NULL, current_org_id );
1345 FUN_RULE_PUB.init_parameter_list;
1346 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1347 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1348 FUN_RULE_PUB.apply_rule('AR','ARI_SERVICE_CHARGE_ENABLED');
1349 l_attr := FUN_RULE_PUB.get_string;
1350 if(l_attr is not null) then
1351 if l_attr='Y' then
1352 return true;
1353 else
1354 return false;
1355 end if;
1356 end if;
1357
1358 ELSIF (nvl(FND_PROFILE.VALUE('OIR_ENABLE_SERVICE_CHARGE'),'N') = 'Y') THEN
1359 RETURN TRUE;
1360 ELSE
1361 RETURN FALSE;
1362 END IF;
1363
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 RETURN FALSE;
1367
1368 END is_service_charge_enabled;
1369
1370
1371
1372
1373 FUNCTION is_discount_grace_days_enabled RETURN BOOLEAN
1374 IS
1375 BEGIN
1376 -- This can be configured to return the appropriate value based on
1377 -- whether grace days have to be picked up for discounts.
1378 IF (nvl(FND_PROFILE.VALUE('OIR_ENABLE_DISCOUNT_GRACE_DAYS'),'N') = 'Y') THEN
1379 RETURN TRUE;
1380 ELSE
1381 RETURN FALSE;
1382 END IF;
1383
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 RETURN FALSE;
1387 END is_discount_grace_days_enabled;
1388
1389
1390
1391
1392
1393 FUNCTION get_service_charge_activity_id ( p_customer_id IN VARCHAR2,
1394 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL ) RETURN NUMBER
1395 IS
1396 CURSOR SYSPARAMCUR IS
1397 SELECT IREC_SERVICE_CHARGE_REC_TRX_ID FROM AR_SYSTEM_PARAMETERS;
1398 l_attr varchar2(15):=null;
1399 current_org_id NUMBER ;
1400 BEGIN
1401 -- This is the activity id for service charge
1402 -- Please configure this to the activity id at installation site
1403 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1404 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_SERVICE_CHARGE_ACTIVITY_ID', NULL,current_org_id) ) THEN
1405
1406 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_SERVICE_CHARGE_ACTIVITY_ID', 'AR', NULL, current_org_id );
1407 FUN_RULE_PUB.init_parameter_list;
1408 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1409 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1410 FUN_RULE_PUB.apply_rule('AR','ARI_SERVICE_CHARGE_ACTIVITY_ID');
1411 l_attr := FUN_RULE_PUB.get_string;
1412 if(l_attr is not null) then
1413 return to_number(l_attr);
1414 end if;
1415
1416 END IF ;
1417
1418 FOR rec IN SYSPARAMCUR
1419 LOOP
1420 RETURN NVL(rec.IREC_SERVICE_CHARGE_REC_TRX_ID,0);
1421 END LOOP;
1422
1423 RETURN 0 ;
1424
1425 EXCEPTION
1426 WHEN OTHERS THEN
1427 RETURN 0;
1428 END get_service_charge_activity_id;
1429
1430
1431 PROCEDURE get_contact_info (
1432 p_customer_id IN VARCHAR2,
1433 p_customer_site_use_id IN VARCHAR2,
1434 p_language_string IN VARCHAR2,
1435 p_page IN VARCHAR2,
1436 p_trx_id IN VARCHAR2,
1437 p_output_string OUT NOCOPY VARCHAR2
1438 ) IS
1439 l_attr varchar2(200):=null;
1440 current_org_id NUMBER ;
1441 BEGIN
1442
1443 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1444 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_CONTACT_INFO', NULL,current_org_id) ) THEN
1445
1446 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_CONTACT_INFO', 'AR', NULL, current_org_id );
1447 FUN_RULE_PUB.init_parameter_list;
1448 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1449 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1450 FUN_RULE_PUB.add_parameter('ARI_LANGUAGE_STRING',p_language_string);
1451 FUN_RULE_PUB.add_parameter('ARI_PAGE',p_page);
1452 /* Fix for the Bug# 5054123. The below parameter 'ARI_TRX_ID' is not used anywhere.
1453 * Moreover, it can sometimes take the value of a URL and so it is not always a number.
1454 * So the below conversion can result in an error (Eg: for DISPUTE) and so it is commented out.
1455 */
1456 --FUN_RULE_PUB.add_parameter('ARI_TRX_ID',to_number(p_trx_id));
1457 FUN_RULE_PUB.apply_rule('AR','ARI_CONTACT_INFO');
1458
1459 l_attr := FUN_RULE_PUB.get_string;
1460 if (l_attr is null) then
1461 l_attr:= 'mailto:webmaster@your_company.com?subject=iReceivables';
1462 end if;
1463
1464 p_output_string := l_attr;
1465
1466 ELSE
1467 p_output_string := 'mailto:webmaster@your_company.com?subject=iReceivables';
1468 END IF ;
1469
1470 END get_contact_info;
1471
1472
1473
1474
1475 FUNCTION get_max_future_payment_date( p_customer_id IN VARCHAR2,
1476 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN DATE
1477 IS
1478 l_attr varchar2(15):=null;
1479 current_org_id NUMBER ;
1480 BEGIN
1481 -- This date will be used to validate that any future dated payments
1482 -- are not beyond this date.
1483
1484 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1485 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_MAX_FUTURE_PAYMENT_DATE', NULL,current_org_id) ) THEN
1486
1487 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_MAX_FUTURE_PAYMENT_DATE', 'AR', NULL, current_org_id );
1488 FUN_RULE_PUB.init_parameter_list;
1489 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1490 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1491 FUN_RULE_PUB.apply_rule('AR','ARI_MAX_FUTURE_PAYMENT_DATE');
1492 l_attr := FUN_RULE_PUB.get_string;
1493 if(l_attr is not null) then
1494 RETURN TRUNC(SYSDATE+to_number(l_attr));
1495 end if;
1496
1497 END IF;
1498
1499 RETURN TRUNC(SYSDATE + NVL(FND_PROFILE.VALUE('OIR_MAX_FUTURE_PAYMENT_DAYS_ALLOWED'),365));
1500
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 RETURN TRUNC(SYSDATE + 365);
1504
1505 END get_max_future_payment_date;
1506
1507
1508
1509
1510 FUNCTION get_site_use_location (p_address_id IN NUMBER) RETURN VARCHAR2 AS
1511 --
1512 l_site_uses VARCHAR2(4000) := '';
1513 --
1514 l_separator VARCHAR2(2) := '';
1515 --
1516 CURSOR c01 (addr_id VARCHAR2) IS
1517 SELECT
1518 unique( LOCATION)
1519 FROM
1520 hz_cust_site_uses
1521 WHERE
1522 cust_acct_site_id = addr_id
1523 AND status = 'A' ;
1524 l_procedure_name VARCHAR2(50);
1525 l_debug_info VARCHAR2(200);
1526 --
1527 BEGIN
1528 --
1529
1530 --
1531 l_procedure_name := '.get_site_use_location';
1532 ----------------------------------------------------------------------------------------
1533 l_debug_info := 'Fetch Bill to Location';
1534 -----------------------------------------------------------------------------------------
1535 IF (PG_DEBUG = 'Y') THEN
1536 arp_standard.debug(l_debug_info);
1537 END IF;
1538
1539 FOR c01_rec IN c01 (p_address_id) LOOP
1540 l_site_uses := l_site_uses || l_separator ||c01_rec.location;
1541
1542 IF l_separator IS NULL THEN
1543 l_separator := ', ';
1544 END IF;
1545
1546 END LOOP;
1547 --
1548 RETURN l_site_uses;
1549
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 IF (PG_DEBUG = 'Y') THEN
1553 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1554 arp_standard.debug('ERROR =>'|| SQLERRM);
1555 arp_standard.debug('Debug Info : '||l_debug_info);
1556 END IF;
1557
1558 END;
1559
1560 /*========================================================================
1561 | PUBLIC function get_site_use_code
1562 |
1563 | DESCRIPTION
1564 | Function returns the site use codes for the given adddress id
1565 |
1566 | PARAMETERS
1567 | p_address_id IN NUMBER
1568 |
1569 | RETURNS
1570 | Site Use Codes for the given address id.
1571 |
1572 | MODIFICATION HISTORY
1573 | Date Author Description of Changes
1574 | 17-JAN-2006 rsinthre Created
1575 | 21-JAN-2007 abathini Modified for Bug 6503280
1576 *=======================================================================*/
1577 FUNCTION get_site_use_code (p_address_id IN NUMBER) RETURN VARCHAR2 AS
1578 l_site_use_codes VARCHAR2(4000) := '';
1579 l_separator VARCHAR2(2) := '';
1580 CURSOR c01 (addr_id VARCHAR2) IS
1581 SELECT
1582 SITE_USE_CODE, SITE_USE_ID
1583 FROM
1584 hz_cust_site_uses
1585 WHERE
1586 cust_acct_site_id = addr_id;
1587 --AND status = 'A' ;
1588 /*Bug 6503280: Commented out above condition on checking status='A'
1589 * to allow Drill Down from Inactive Sites from Customer Search Page*/
1590 l_procedure_name VARCHAR2(50);
1591 l_debug_info VARCHAR2(200);
1592 --
1593 BEGIN
1594 --
1595 G_BILL_TO_SITE_USE_ID := 0;
1596 --
1597 l_procedure_name := '.get_site_use_code';
1598 ----------------------------------------------------------------------------------------
1599 l_debug_info := 'Fetch Bill to Site use id';
1600 -----------------------------------------------------------------------------------------
1601 IF (PG_DEBUG = 'Y') THEN
1602 arp_standard.debug(l_debug_info);
1603 END IF;
1604
1605 FOR c01_rec IN c01 (p_address_id) LOOP
1606 l_site_use_codes := l_site_use_codes || l_separator || c01_rec.site_use_code;
1607
1608 IF c01_rec.site_use_code = 'BILL_TO' THEN
1609 G_BILL_TO_SITE_USE_ID := c01_rec.site_use_id;
1610 END IF;
1611
1612 IF l_separator IS NULL THEN
1613 l_separator := ', ';
1614 END IF;
1615
1616 END LOOP;
1617 --
1618 RETURN l_site_use_codes;
1619
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 IF (PG_DEBUG = 'Y') THEN
1623 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1624 arp_standard.debug('ERROR =>'|| SQLERRM);
1625 arp_standard.debug('Debug Info : '||l_debug_info);
1626 END IF;
1627
1628
1629
1630 END get_site_use_code;
1631
1632
1633 FUNCTION get_group_header(p_customer_id IN NUMBER,
1634 p_party_id IN NUMBER , p_trx_number IN VARCHAR) RETURN NUMBER AS
1635
1636 l_account_access_count NUMBER := NULL;
1637 l_site_access_count NUMBER :=NULL;
1638 l_flag NUMBER := NULL;
1639
1640 BEGIN
1641
1642 select count(*) into l_account_access_count from ar_customers_assigned_v hzca where hzca.cust_account_id = p_customer_id
1643 and hzca.party_id=p_party_id;
1644
1645
1646 IF l_account_access_count > 0 THEN
1647 RETURN 0;
1648 END IF;
1649
1653 and INSTR(ARI_UTILITIES.GET_SITE_USE_CODE(acct_sites_count.CUST_ACCT_SITE_ID), 'BILL_TO')>0;
1650 select count(*) into l_site_access_count from ar_sites_assigned_v acct_sites_count
1651 where acct_sites_count.party_id=p_party_id
1652 and acct_sites_count.cust_account_id=p_customer_id
1654
1655 select count(*) into l_flag from(
1656 select trx_number,CUSTOMER_SITE_USE_ID from ar_payment_schedules where trx_number=p_trx_number
1657 and CUSTOMER_SITE_USE_ID in
1658 (
1659 select ARI_UTILITIES.get_bill_to_site_use_id(CUST_ACCT_SITE_ID) from ar_sites_assigned_v where
1660 party_id=p_party_id
1661 and cust_account_id=p_customer_id
1662 )
1663 );
1664
1665 IF l_site_access_count > 1 AND l_flag > 0 THEN
1666 RETURN 1;
1667 ELSE
1668 RETURN 2;
1669 END IF;
1670
1671 END get_group_header;
1672
1673 FUNCTION invoke_invoice_email_notwf( p_subscription_guid In RAW , p_event IN OUT NOCOPY WF_EVENT_T ) return varchar2 AS
1674
1675 l_trx_number VARCHAR2(30);
1676 l_customer_trx_id NUMBER;
1677 l_org_id NUMBER;
1678 l_user_id NUMBER;
1679 l_resp_id NUMBER;
1680 l_application_id NUMBER;
1681
1682 l_customer_id NUMBER := NULL;
1683 l_customer_acct_site_id NUMBER := NULL;
1684
1685
1686 l_procedure_name VARCHAR2(30) := '.invoke_invoice_email_notwf';
1687 l_debug_info VARCHAR2(500);
1688
1689 l_itemtype VARCHAR2(20) := 'ARINVNTF';
1690
1691 BEGIN
1692
1693 --------------------------------------------------------------------
1694 l_debug_info := 'In debug mode, log we have entered this procedure';
1695 --------------------------------------------------------------------
1696 IF (PG_DEBUG = 'Y') THEN
1697 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
1698 END IF;
1699
1700 l_customer_trx_id := p_event.GetValueForParameter('CUSTOMER_TRX_ID');
1701 l_org_id := p_event.GetValueForParameter('ORG_ID');
1702 l_user_id := p_event.GetValueForParameter('USER_ID');
1703 l_resp_id := p_event.GetValueForParameter('RESP_ID');
1704 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
1705
1706 IF (PG_DEBUG = 'Y') THEN
1707
1708 arp_standard.debug ('l_customer_trx_id ='||l_customer_trx_id);
1709 arp_standard.debug ('l_org_id ='||l_org_id);
1710 arp_standard.debug ('l_user_id ='||l_user_id);
1711 arp_standard.debug ('l_resp_id ='||l_resp_id);
1712 arp_standard.debug ('l_application_id ='||l_application_id);
1713 END IF;
1714
1715 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
1716
1717 mo_global.init('AR');
1718 mo_global.set_policy_context('S',l_org_id);
1719
1720 --------------------------------------------------------------------
1721 l_debug_info := 'fetching customer_id,customer_site_use_id and trx number';
1722 --------------------------------------------------------------------
1723
1724 select aps.customer_id,sites.CUST_ACCT_SITE_ID,trx_number
1725 into l_customer_id,l_customer_acct_site_id,l_trx_number
1726 from ar_payment_schedules_all aps,HZ_CUST_SITE_USES sites
1727 where aps.customer_trx_id = l_customer_trx_id
1728 and aps.org_id = l_org_id
1729 and sites.site_use_id = aps.customer_site_use_id;
1730
1731 IF (PG_DEBUG = 'Y') THEN
1732 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_customer_id || '+');
1733 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_customer_acct_site_id || '+');
1734 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_trx_number || '+');
1735 END IF;
1736
1737 --------------------------------------------------------------------
1738 l_debug_info := 'creating workflow process';
1739 --------------------------------------------------------------------
1740
1741 WF_ENGINE.CREATEPROCESS(l_itemtype,
1742 l_customer_trx_id,
1743 'ARINVNTF');
1744
1745 ----------------------------------------------------------------------------
1746 l_debug_info := 'Set parameters expected by ARINVNTF Workflow';
1747 ----------------------------------------------------------------------------
1748
1749 ----------------------------------------------------------------------------
1750 l_debug_info := 'Set ARI_CUSTOMER_TRX_ID parameter';
1751 ----------------------------------------------------------------------------
1752
1753
1754 WF_ENGINE.SetItemAttrText(l_itemtype,
1755 l_customer_trx_id,
1756 'ARI_CUSTOMER_TRX_ID',
1757 l_trx_number);
1758
1759 ----------------------------------------------------------------------------
1760 l_debug_info := 'Set ARI_CUST_ACCT_NUM parameter';
1761 ----------------------------------------------------------------------------
1762
1763 WF_ENGINE.SetItemAttrText(l_itemtype,
1764 l_customer_trx_id,
1765 'ARI_CUST_ACCT_NUM',
1766 l_customer_id);
1767
1768 ----------------------------------------------------------------------------
1769 l_debug_info := 'Set ARI_CUST_ACCT_SITE_NUM parameter';
1770 ----------------------------------------------------------------------------
1771
1772 WF_ENGINE.SetItemAttrText(l_itemtype,
1773 l_customer_trx_id,
1774 'ARI_CUST_ACCT_SITE_NUM',
1775 l_customer_acct_site_id);
1776
1777 ----------------------------------------------------------------------------
1778 l_debug_info := 'Starting Workflow..';
1779 ----------------------------------------------------------------------------
1780
1781 IF (PG_DEBUG = 'Y') THEN
1785 WF_ENGINE.StartProcess(l_itemtype,l_customer_trx_id);
1782 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS') || '+');
1783 END IF;
1784
1786
1787
1788 RETURN 'SUCCESS';
1789
1790 EXCEPTION
1791 WHEN OTHERS THEN
1792 IF (PG_DEBUG = 'Y') THEN
1793 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1794 arp_standard.debug('ERROR =>'|| SQLERRM);
1795 arp_standard.debug('Debug Info : '||l_debug_info);
1796 END IF;
1797 RETURN 'ERROR';
1798
1799 END invoke_invoice_email_notwf;
1800
1801 FUNCTION get_contact_emails( p_customer_id IN VARCHAR2,
1802 p_customer_acct_site_id IN VARCHAR2 ) RETURN VARCHAR2 AS
1803
1804 l_contact_email_addrs varchar2 (32760) := null;
1805 l_contact_id NUMBER :=NULL;
1806
1807 l_procedure_name VARCHAR2(30) := '.get_contact_emails';
1808 l_debug_info VARCHAR2(500);
1809
1810 CURSOR contact_cur(p_customer_id IN NUMBER DEFAULT NULL,
1811 p_customer_acct_site_id IN NUMBER DEFAULT NULL ) IS
1812 SELECT hcar.CUST_ACCOUNT_ROLE_ID as contact_id
1813 FROM HZ_CUST_ACCOUNT_ROLES hcar, HZ_PARTIES hpsub, HZ_PARTIES hprel,
1814 HZ_ORG_CONTACTS hoc, HZ_RELATIONSHIPS hr, HZ_PARTY_SITES hps, FND_TERRITORIES_VL ftv,
1815 fnd_lookup_values_vl lookups,hz_role_responsibility hrr
1816 WHERE hrr.responsibility_type = 'SELF_SERVICE_USER'
1817 and hrr.cust_account_role_id = hcar.cust_account_role_id
1818 and hcar.CUST_ACCOUNT_ID = p_customer_id
1819 AND hcar.ROLE_TYPE = 'CONTACT'
1820 AND hcar.PARTY_ID = hr.PARTY_ID
1821 AND hr.PARTY_ID = hprel.PARTY_ID
1822 AND hr.SUBJECT_ID = hpsub.PARTY_ID
1823 AND hoc.PARTY_RELATIONSHIP_ID = hr.RELATIONSHIP_ID
1824 AND hr.DIRECTIONAL_FLAG = 'F'
1825 AND hps.PARTY_ID(+) = hprel.PARTY_ID
1826 AND nvl(hps.IDENTIFYING_ADDRESS_FLAG, 'Y') = 'Y'
1827 AND nvl(hps.STATUS, 'A') = 'A'
1828 AND hprel.COUNTRY = ftv.TERRITORY_CODE(+)
1829 AND nvl(hcar.CUST_ACCT_SITE_ID, 1) = nvl(p_customer_acct_site_id, 1)
1830 AND lookups.LOOKUP_TYPE (+)='RESPONSIBILITY'
1831 AND lookups.LOOKUP_CODE(+)=hoc.JOB_TITLE_CODE
1832 and hcar.status='A';
1833
1834 CURSOR email_addr_cur (l_contact_id IN NUMBER DEFAULT NULL) IS
1835 SELECT cont_point.email_Address
1836 FROM hz_cust_account_roles acct_role,
1837 hz_contact_points cont_point
1838 WHERE acct_role.cust_account_role_id =l_contact_id
1839 AND acct_role.party_id = cont_point.owner_table_id
1840 AND cont_point.owner_table_name = 'HZ_PARTIES'
1841 AND cont_point.status = 'A'
1842 AND cont_point.email_Address is not null;
1843
1844 contact_rec contact_cur%ROWTYPE;
1845 email_addr_rec email_addr_cur%ROWTYPE;
1846
1847 BEGIN
1848
1849 ----------------------------------------------------------------------------------------
1850 l_debug_info := 'fetches all email addres at account level for all self sevice users';
1851 -----------------------------------------------------------------------------------------
1852 IF (PG_DEBUG = 'Y') THEN
1853 arp_standard.debug(l_debug_info);
1854 END IF;
1855
1856 FOR contact_rec in contact_cur(p_customer_id) LOOP
1857
1858 l_contact_id := contact_rec.contact_id;
1859
1860 FOR email_addr_rec in email_addr_cur( l_contact_id ) LOOP
1861
1862 if (l_contact_email_addrs is null) then
1863 l_contact_email_addrs := email_addr_rec.email_Address;
1864 else
1865 l_contact_email_addrs := l_contact_email_addrs || ',' || email_addr_rec.email_Address;
1866 end if;
1867
1868 END LOOP;
1869
1870 END LOOP;
1871
1872 ----------------------------------------------------------------------------------------
1873 l_debug_info := 'emails at account level';
1874 -----------------------------------------------------------------------------------------
1875 IF (PG_DEBUG = 'Y') THEN
1876 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_contact_email_addrs || '+');
1877 END IF;
1878
1879 ----------------------------------------------------------------------------------------
1880 l_debug_info := 'fetches all email addres at site level for all self sevice users';
1881 -----------------------------------------------------------------------------------------
1882
1883 FOR contact_rec in contact_cur(p_customer_id,p_customer_acct_site_id) LOOP
1884
1885 l_contact_id := contact_rec.contact_id;
1886
1887 FOR email_addr_rec in email_addr_cur( l_contact_id ) LOOP
1888
1889 if (l_contact_email_addrs is null) then
1890 l_contact_email_addrs := email_addr_rec.email_Address;
1891 else
1892 l_contact_email_addrs := l_contact_email_addrs || ',' || email_addr_rec.email_Address;
1893 end if;
1894
1895 END LOOP;
1896
1897 END LOOP;
1898
1899 ----------------------------------------------------------------------------------------
1900 l_debug_info := 'emails at account and site level';
1901 -----------------------------------------------------------------------------------------
1902 IF (PG_DEBUG = 'Y') THEN
1903 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_contact_email_addrs || '+');
1904 END IF;
1905
1906 RETURN l_contact_email_addrs;
1907
1908 EXCEPTION
1909 WHEN OTHERS THEN
1910 IF (PG_DEBUG = 'Y') THEN
1911 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1912 arp_standard.debug('ERROR =>'|| SQLERRM);
1913 arp_standard.debug('Debug Info : '||l_debug_info);
1914 END IF;
1918
1915 RETURN 'ERROR';
1916
1917 END ;
1919
1920
1921 PROCEDURE det_if_send_email( l_itemtype in varchar2,
1922 l_itemkey in varchar2,
1923 actid in number,
1924 funcmode in varchar2,
1925 rslt out NOCOPY varchar2 ) IS
1926
1927 l_adhoc_user_name VARCHAR2(200);
1928 l_adhoc_user_display_name VARCHAR2(200);
1929 l_role_prefix VARCHAR2(14) := 'ARINVNTF_';
1930 l_role_exists NUMBER;
1931
1932
1933 l_customer_trx_id NUMBER ;
1934
1935 l_email varchar2(3000) := null;
1936
1937 p_customer_id NUMBER := NULL;
1938 p_customer_acct_site_id NUMBER := NULL;
1939
1940 l_trx_number VARCHAR2(30) :=null;
1941 l_inv_curr_code VARCHAR2(15) :=null;
1942 l_term_nanme VARCHAR2(15) := null;
1943 l_term_desc VARCHAR2(240) :=null;
1944 l_inv_due_date DATE;
1945 l_inv_amt_due NUMBER;
1946
1947
1948 l_procedure_name VARCHAR2(30) := '.det_if_send_email';
1949 l_debug_info VARCHAR2(500);
1950
1951 BEGIN
1952
1953 -----------------------------------------------------------
1954 l_debug_info := 'Retrieve ARI_INV_NUM Item Attribute';
1955 -----------------------------------------------------------
1956
1957 l_customer_trx_id:= wf_engine.GetItemAttrText(itemtype => l_itemtype,
1958 itemkey => l_itemkey,
1959 aname => 'ARI_INV_NUM');
1960 IF (PG_DEBUG = 'Y') THEN
1961 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_customer_trx_id || '+');
1962 END IF;
1963
1964 -----------------------------------------------------------
1965 l_debug_info := 'Retrieve ARI_CUST_ACCT_NUM Item Attribute';
1966 -----------------------------------------------------------
1967
1968 p_customer_id:= wf_engine.GetItemAttrText(itemtype => l_itemtype,
1969 itemkey => l_itemkey,
1970 aname => 'ARI_CUST_ACCT_NUM');
1971 IF (PG_DEBUG = 'Y') THEN
1972 arp_standard.debug(G_PKG_NAME || l_procedure_name || p_customer_id || '+');
1973 END IF;
1974
1975 ---------------------------------------------------------------
1976 l_debug_info := 'Retrieve ARI_CUST_ACCT_SITE_NUM Item Attribute';
1977 ----------------------------------------------------------------
1978
1979 p_customer_acct_site_id := wf_engine.GetItemAttrText(itemtype => l_itemtype,
1980 itemkey => l_itemkey,
1981 aname => 'ARI_CUST_ACCT_SITE_NUM');
1982
1983 IF (PG_DEBUG = 'Y') THEN
1984 arp_standard.debug(G_PKG_NAME || l_procedure_name || p_customer_acct_site_id || '+');
1985 END IF;
1986
1987 --------------------------------------------------------------------------
1988 l_debug_info := 'selecting all the attributes required to send in notification';
1989 ---------------------------------------------------------------------------
1990
1991 select aps.trx_number,aps.amount_due_original,aps.invoice_currency_code,aps.due_date,t.name,t.description
1992 into l_trx_number,l_inv_amt_due,l_inv_curr_code,l_inv_due_date,l_term_nanme,l_term_desc
1993 from ar_payment_schedules_all aps,ra_terms t
1994 where aps.customer_trx_id = l_customer_trx_id
1995 and aps.customer_id = p_customer_id
1996 and aps.term_id = t.term_id(+);
1997
1998 IF (PG_DEBUG = 'Y') THEN
1999 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_trx_number || '+');
2000 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_inv_amt_due || '+');
2001 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_inv_curr_code || '+');
2002 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_inv_due_date || '+');
2003 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_term_nanme || '+');
2004 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_term_desc || '+');
2005 END IF;
2006
2007 --------------------------------------------------------------------------
2008 l_debug_info := 'fetching all contacts emails to send notification ';
2009 ---------------------------------------------------------------------------
2010
2011 l_email := get_contact_emails(p_customer_id,p_customer_acct_site_id);
2012
2013 IF (PG_DEBUG = 'Y') THEN
2014 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_email || '+');
2015 END IF;
2016
2017 ------------------------------------------------------------------------------------------
2018 l_debug_info := 'check if emails exits , if yes creating adhoc user , if no invoice complete ';
2019 -------------------------------------------------------------------------------------------
2020
2021 if ( l_email is null ) then
2022 rslt := 'COMPLETE:' || 'N';
2023 else
2024
2025 l_adhoc_user_name := l_role_prefix || l_customer_trx_id;
2026 l_adhoc_user_display_name := l_adhoc_user_name;
2027
2028 IF (PG_DEBUG = 'Y') THEN
2029 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'adhoc username :: ' || l_adhoc_user_name || '+');
2030 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'adhoc display name :: ' || l_adhoc_user_display_name || '+');
2031 END IF;
2032
2033 -----------------------------------------------------
2034 l_debug_info := 'checking for is role already exists';
2035 --------------------------------------------------------
2036
2037 begin
2038 select count(1),name
2039 into l_role_exists,l_adhoc_user_name
2040 from WF_LOCAL_ROLES
2041 where EMAIL_ADDRESS = l_email
2042 group by name;
2043 exception
2047
2044 WHEN NO_DATA_FOUND THEN
2045 l_role_exists:= 0;
2046 end;
2048 IF (PG_DEBUG = 'Y') THEN
2049 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_role_exists :: ' || l_role_exists || '+');
2050 END IF;
2051
2052 if l_role_exists = 0 then
2053
2054 -------------------------------------------------------
2055 l_debug_info := 'if no role exits ,creating a new role';
2056 --------------------------------------------------------
2057 WF_DIRECTORY.CreateAdHocRole(role_name => l_adhoc_user_name,
2058 role_display_name => l_adhoc_user_display_name,
2059 notification_preference => 'MAILTEXT',
2060 email_address => l_email );
2061 end if;
2062
2063 --------------------------------------------------------------
2064 l_debug_info := 'Set AR_NOTIFY_ROLES Item Attribute';
2065 --------------------------------------------------------------
2066
2067 WF_ENGINE.SetItemAttrText(l_itemtype,
2068 l_customer_trx_id,
2069 'AR_NOTIFY_ROLES',
2070 l_adhoc_user_name);
2071
2072 --------------------------------------------------------------
2073 l_debug_info := 'Set ARI_INV_NUM Item Attribute';
2074 --------------------------------------------------------------
2075
2076 WF_ENGINE.SetItemAttrText(l_itemtype,
2077 l_customer_trx_id,
2078 'ARI_INV_NUM',
2079 l_trx_number);
2080
2081 --------------------------------------------------------------
2082 l_debug_info := 'Set ARI_INV_AMT_DUE Item Attribute';
2083 --------------------------------------------------------------
2084
2085 WF_ENGINE.SetItemAttrText(l_itemtype,
2086 l_customer_trx_id,
2087 'ARI_INV_AMT_DUE',
2088 l_inv_amt_due);
2089
2090 --------------------------------------------------------------
2091 l_debug_info := 'Set ARI_INV_CUR_CODE Item Attribute';
2092 --------------------------------------------------------------
2093
2094 WF_ENGINE.SetItemAttrText(l_itemtype,
2095 l_customer_trx_id,
2096 'ARI_INV_CUR_CODE',
2097 l_inv_curr_code);
2098
2099 --------------------------------------------------------------
2100 l_debug_info := 'Set ARI_DUE_DATE Item Attribute';
2101 --------------------------------------------------------------
2102
2103 WF_ENGINE.SetItemAttrText(l_itemtype,
2104 l_customer_trx_id,
2105 'ARI_DUE_DATE',
2106 l_inv_due_date);
2107
2108 --------------------------------------------------------------
2109 l_debug_info := 'Set ARI_PAY_TERM Item Attribute';
2110 --------------------------------------------------------------
2111
2112 WF_ENGINE.SetItemAttrText(l_itemtype,
2113 l_customer_trx_id,
2114 'ARI_PAY_TERM',
2115 l_term_nanme);
2116
2117 --------------------------------------------------------------
2118 l_debug_info := 'Set ARI_PAY_TERM_DESC Item Attribute';
2119 --------------------------------------------------------------
2120
2121 WF_ENGINE.SetItemAttrText(l_itemtype,
2122 l_customer_trx_id,
2123 'ARI_PAY_TERM_DESC',
2124 l_term_desc);
2125
2126 rslt := 'COMPLETE:' || 'Y';
2127 end if;
2128
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 rslt := 'COMPLETE:' || 'N';
2132 wf_core.context('ARI_UTILITIES','DET_IF_SEND_EMAIL',l_itemtype,
2133 l_itemkey,to_char(actid),funcmode);
2134 IF (PG_DEBUG = 'Y') THEN
2135 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2136 arp_standard.debug('ERROR =>'|| SQLERRM);
2137 arp_standard.debug('Debug Info : '||l_debug_info);
2138 END IF;
2139 raise;
2140
2141 end det_if_send_email;
2142
2143 PROCEDURE cancel_dispute(p_dispute_id IN NUMBER,
2144 p_cancel_comments IN VARCHAR2,
2145 p_return_status OUT NOCOPY VARCHAR2
2146 ) IS
2147
2148 CURSOR c_item_type(l_item_key NUMBER) IS
2149 SELECT item_type
2150 FROM wf_items
2151 WHERE item_key = l_item_key
2152 AND item_type IN('ARCMREQ','ARAMECM');
2153
2154
2155 CURSOR ps_cur(p_customer_trx_id NUMBER) IS
2156 SELECT payment_schedule_id,
2157 due_date,
2158 amount_in_dispute,
2159 dispute_date
2160 FROM ar_payment_schedules ps
2161 WHERE ps.customer_trx_id = p_customer_trx_id;
2162
2163 cursor get_partyid(p_cust_acct_id number) is
2164 select party_id
2165 from hz_cust_accounts
2166 where cust_account_id = p_cust_acct_id;
2167
2168 Cursor Get_billto(p_cust_trx_id number) Is
2169 select bill_to_site_use_id
2170 from ra_customer_trx
2171 where customer_trx_id = p_cust_trx_id;
2172
2173 Cursor Get_paymentid(p_cust_trx_id number) Is
2174 select customer_id,payment_schedule_id
2175 from ar_payment_schedules
2176 where customer_trx_id = p_cust_trx_id;
2177
2178 l_item_type VARCHAR2(100);
2179 l_customer_trx_id NUMBER;
2180 l_status VARCHAR2(8);
2181 l_result VARCHAR2(100);
2182 l_last_updated_by NUMBER;
2183 l_last_update_login NUMBER;
2184 l_last_update_date DATE;
2185 l_creation_date DATE;
2189 l_note_text ar_notes.text%TYPE;
2186 l_created_by NUMBER;
2187 l_document_id NUMBER;
2188 l_note_id NUMBER;
2190 l_notes wf_item_attribute_values.text_value%TYPE;
2191 l_cust_account_id NUMBER;
2192 l_payment_schedule_id NUMBER;
2193 l_party_id NUMBER;
2194 l_customer_site_use_id number;
2195 new_dispute_date DATE;
2196 new_dispute_amt NUMBER;
2197 remove_from_dispute_amt NUMBER;
2198 i NUMBER;
2199 l_default_note_type varchar2(240) := FND_PROFILE.VALUE('AST_NOTES_DEFAULT_TYPE');
2200 l_jtf_note_contexts_table jtf_notes_pub.jtf_note_contexts_tbl_type;
2201 l_context_tab CONTEXTS_TBL_TYPE;
2202 l_return_status VARCHAR2(1);
2203 l_msg_count NUMBER;
2204 l_msg_data VARCHAR2(32767);
2205 l_procedure_name VARCHAR2(50);
2206 l_debug_info VARCHAR2(200);
2207
2208 BEGIN
2209
2210 l_procedure_name := '.cancel_dispute';
2211 l_debug_info := 'Cancel Credit Memo Request';
2212
2213 SAVEPOINT CANCEL_DISPUTE;
2214
2215 p_return_status := FND_API.G_RET_STS_SUCCESS;
2216 OPEN c_item_type(p_dispute_id);
2217 FETCH c_item_type INTO l_item_type;
2218 CLOSE c_item_type;
2219
2220 IF l_item_type IS NOT NULL THEN
2221 l_customer_trx_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'CUSTOMER_TRX_ID');
2222 END IF;
2223
2224 SELECT total_amount * -1
2225 INTO remove_from_dispute_amt
2226 FROM ra_cm_requests
2227 WHERE request_id = p_dispute_id;
2228
2229 FOR ps_rec IN ps_cur(l_customer_trx_id)
2230 LOOP
2231 new_dispute_amt := ps_rec.amount_in_dispute -remove_from_dispute_amt;
2232
2233 IF new_dispute_amt = 0 THEN
2234 new_dispute_date := NULL;
2235 ELSE
2236 new_dispute_date := ps_rec.dispute_date;
2237 END IF;
2238
2239 arp_process_cutil.update_ps(p_ps_id => ps_rec.payment_schedule_id,
2240 p_due_date => ps_rec.due_date,
2241 p_amount_in_dispute => new_dispute_amt,
2242 p_dispute_date => new_dispute_date,
2243 p_update_dff => 'N',
2244 p_attribute_category => NULL,
2245 p_attribute1 => NULL,
2246 p_attribute2 => NULL,
2247 p_attribute3 => NULL,
2248 p_attribute4 => NULL,
2249 p_attribute5 => NULL,
2250 p_attribute6 => NULL,
2251 p_attribute7 => NULL,
2252 p_attribute8 => NULL,
2253 p_attribute9 => NULL,
2254 p_attribute10 => NULL,
2255 p_attribute11 => NULL,
2256 p_attribute12 => NULL,
2257 p_attribute13 => NULL,
2258 p_attribute14 => NULL,
2259 p_attribute15 => NULL);
2260
2261 END LOOP;
2262
2263 wf_engine.SetItemAttrText(l_item_type, p_dispute_id, 'NOTES', p_cancel_comments);
2264
2265 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_dispute_id, status => l_status, result => l_result);
2266
2267 IF l_status <> wf_engine.eng_completed THEN
2268 wf_engine.abortprocess(itemtype => l_item_type, itemkey => p_dispute_id);
2269 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_dispute_id, status => l_status, result => l_result);
2270 END IF;
2271
2272 l_last_updated_by := arp_global.user_id;
2273 l_last_update_login := arp_global.last_update_login;
2274 l_document_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'WORKFLOW_DOCUMENT_ID');
2275 l_customer_trx_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'CUSTOMER_TRX_ID');
2276
2277
2278 if l_customer_trx_id is null then
2279 SELECT customer_trx_id
2280 INTO l_customer_trx_id
2281 FROM ra_cm_requests
2282 WHERE request_id = l_document_id;
2283 end if;
2284
2285 l_notes := wf_engine.getitemattrtext(l_item_type, p_dispute_id, 'NOTES');
2286 fnd_message.set_name('AR', 'AR_WF_REJECTED_RESPONSE');
2287 fnd_message.set_token('REQUEST_ID', to_char(p_dispute_id));
2288 fnd_message.set_token('APPROVER', fnd_global.user_id);
2289 l_note_text := fnd_message.GET;
2290
2291 IF l_notes IS NOT NULL THEN
2292 l_note_text := substrb(l_note_text || ' "' || l_notes || '"', 1, 2000);
2293 END IF;
2294
2295 arp_notes_pkg.insert_cover(
2296 p_note_type => 'MAINTAIN',
2297 p_text => l_note_text,
2298 p_customer_call_id => null,
2299 p_customer_call_topic_id => null,
2300 p_call_action_id => NULL,
2301 p_customer_trx_id => l_customer_trx_id,
2302 p_note_id => l_note_id,
2303 p_last_updated_by => l_last_updated_by,
2304 p_last_update_date => l_last_update_date,
2305 p_last_update_login => l_last_update_login,
2306 p_created_by => l_created_by,
2307 p_creation_date => l_creation_date);
2308
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 IF (PG_DEBUG = 'Y') THEN
2312 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2313 arp_standard.debug('ERROR =>'|| SQLERRM);
2314 arp_standard.debug('Debug Info : '||l_debug_info);
2315 END IF;
2316 ROLLBACK TO CANCEL_DISPUTE;
2317 p_return_status := FND_API.G_RET_STS_ERROR;
2318 END cancel_dispute;
2319
2320 END ari_utilities;
2321
2322