[Home] [Help]
PACKAGE BODY: APPS.ARI_UTILITIES
Source
4 /*=======================================================================+
1 PACKAGE BODY ARI_UTILITIES AS
2 /* $Header: ARIUTILB.pls 120.37.12020000.3 2012/12/25 18:31:05 shvimal ship $ */
3
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
100 |
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'
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
223 WF_ENGINE.SetItemAttrText('ARIPRNTF',
220 /*------------------------------------------------------------------+
221 | Set the URL param for the embedded framework region |
222 +------------------------------------------------------------------*/
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);
314 arp_standard.debug('- Minimum Accounting Unit: '||l_min_acct_unit);
315 END IF;
316
317 ---------------------------------------------------------------------------
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
353 *=======================================================================*/
350 /*========================================================================
351 | get_lookup_meaning function returns the lookup meaning of lookup code |
352 | in user specific language. |
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 --
452 l_site_uses VARCHAR2(4000) := '';
453 --
454 l_separator VARCHAR2(2) := '';
455 --
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;
510 --
507
508
509 FUNCTION site_use_meaning (p_site_use IN VARCHAR2) RETURN VARCHAR2 AS
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 | 05-NOV-2009 avepati Bug 9074606 - GSI: 12.1.1 Poor performance in external customer account
588 | 23-MAR-2011 rsinthre Bug 11804918 - Acct Summary no longer allows pulldown for multiple currency
589 *=======================================================================*/
590 FUNCTION get_default_currency ( p_customer_id IN VARCHAR2,
591 p_session_id IN VARCHAR2)
592
593 RETURN VARCHAR2
594 IS
595 l_default_currency VARCHAR2(15);
596 l_default_org_id NUMBER(15,0);
597 l_profile_default_currrency VARCHAR2(15);
598 l_currency_exist NUMBER(4);
599 BEGIN
600 l_profile_default_currrency := FND_PROFILE.value('OIR_DEFAULT_CURRENCY_CODE');
601
602 IF(p_customer_id IS NULL) THEN
603
604 select count(*) into l_currency_exist from dual where l_profile_default_currrency in
605 (( SELECT /*+ leading(auas) use_nl(auas cpf) */ unique ( CUR.CURRENCY_CODE ) FROM HZ_CUST_PROFILE_AMTS CPA,
606 FND_CURRENCIES_VL CUR, HZ_CUSTOMER_PROFILES CPF, ar_irec_user_acct_sites_all AUAS
607 WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
608 AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
609 AND CPF.CUST_ACCOUNT_ID =AUAS.CUSTOMER_ID
610 AND (
611 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
612 OR
613 CPF.SITE_USE_ID IS NULL
614 )
615 AND AUAS.user_id=FND_GLOBAL.USER_ID()
616 AND AUAS.session_id=p_session_id)
617 UNION
618 (SELECT unique ( CUR.CURRENCY_CODE ) FROM FND_CURRENCIES_VL CUR, AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
619 WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
620 ATB.CUST_ACCOUNT_ID = AUAS.CUSTOMER_ID AND
621 (ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
622 AND AUAS.user_id=FND_GLOBAL.USER_ID()
623 AND AUAS.session_id=p_session_id));
624 if( l_currency_exist > 0 ) then
625 return l_profile_default_currrency;
626 end if;
627
628 SELECT /*+ leading(auas) use_nl(auas cpf) */ unique ( CUR.CURRENCY_CODE )
629 INTO l_default_currency
630 FROM HZ_CUST_PROFILE_AMTS CPA,
631 FND_CURRENCIES_VL CUR,
632 HZ_CUSTOMER_PROFILES CPF,
633 ar_irec_user_acct_sites_all AUAS
634 WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
635 AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
636 AND CPF.CUST_ACCOUNT_ID =AUAS.CUSTOMER_ID
637 AND (
638 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
639 OR
640
644 AND AUAS.session_id=p_session_id
641 CPF.SITE_USE_ID IS NULL
642 )
643 AND AUAS.user_id=FND_GLOBAL.USER_ID()
645 AND ROWNUM = 1;
646
647 --If currency does not exist in customer profile, then check if currency exist in transactions of the customer
648 if(l_default_currency = '' OR l_default_currency is null) then
649
650 SELECT unique ( CUR.CURRENCY_CODE ) INTO l_default_currency FROM FND_CURRENCIES_VL CUR,
651 AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
655 AND AUAS.user_id=FND_GLOBAL.USER_ID()
652 WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
653 ATB.CUST_ACCOUNT_ID = AUAS.CUSTOMER_ID AND
654 (ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
656 AND AUAS.session_id=p_session_id;
657 end if;
658
659 ELSE
660
661 select count(*) into l_currency_exist from dual where l_profile_default_currrency in
662 (( SELECT /*+ leading(auas) use_nl(auas cpf) */ unique ( CUR.CURRENCY_CODE ) FROM HZ_CUST_PROFILE_AMTS CPA,
663 FND_CURRENCIES_VL CUR, HZ_CUSTOMER_PROFILES CPF, ar_irec_user_acct_sites_all AUAS
664 WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
665 AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
666 AND CPF.CUST_ACCOUNT_ID = p_customer_id
667 AND (
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 UNION
675 (SELECT unique ( CUR.CURRENCY_CODE ) FROM FND_CURRENCIES_VL CUR, AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
676 WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
677 ATB.CUST_ACCOUNT_ID = p_customer_id AND
678 (ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
679 AND AUAS.user_id=FND_GLOBAL.USER_ID()
680 AND AUAS.session_id=p_session_id));
681
682 if( l_currency_exist > 0 ) then
683 return l_profile_default_currrency;
684 end if;
685
686 SELECT /*+ leading(auas) use_nl(auas cpf) */ unique ( CUR.CURRENCY_CODE )
687 INTO l_default_currency
688 FROM HZ_CUST_PROFILE_AMTS CPA,
689 FND_CURRENCIES_VL CUR,
690 HZ_CUSTOMER_PROFILES CPF,
691 ar_irec_user_acct_sites_all AUAS
692 WHERE
693 CPA.CURRENCY_CODE = CUR.CURRENCY_CODE AND
694 CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID AND
695 CPF.CUST_ACCOUNT_ID = p_customer_id AND
696 (
697 CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
698 OR
699 CPF.SITE_USE_ID IS NULL
700 )
701 AND AUAS.user_id=FND_GLOBAL.USER_ID()
702 AND AUAS.session_id=p_session_id
703 AND ROWNUM = 1;
704
705 --If currency does not exist in customer profile, then check if currency exist in transactions of the customer
706 if(l_default_currency = '' OR l_default_currency is null) then
707
708 SELECT unique ( CUR.CURRENCY_CODE ) INTO l_default_currency FROM FND_CURRENCIES_VL CUR,
709 AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
710 WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
711 ATB.CUST_ACCOUNT_ID = p_customer_id AND
712 (ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
713 AND AUAS.user_id=FND_GLOBAL.USER_ID()
714 AND AUAS.session_id=p_session_id;
715 end if;
716
717 END IF;
718
719 RETURN l_default_currency;
720
721 EXCEPTION
722 WHEN NO_DATA_FOUND THEN
723
724 SELECT sb.currency_code
725 INTO l_default_currency
726 FROM ar_system_parameters sys,
727 gl_sets_of_books sb
728 WHERE sb.set_of_books_id = sys.set_of_books_id;
729
730 RETURN l_default_currency;
731
732 WHEN OTHERS THEN
733 RETURN NULL;
734
735 END get_default_currency;
736
737
738 /*========================================================================
739 | PUBLIC FUNCTION check_site_access
740 |
741 | DESCRIPTION
742 | This function checks if the person party has access to the specified
743 | customer site.
744 |
745 | PARAMETERS
746 | p_person_party_id IN VARCHAR2
747 | p_customer_id IN VARCHAR2
748 | p_customer_site_use_id IN VARCHAR2
749 |
750 | NOTES
751 | This does not check access at the account level - only at this particular site.
752 |
753 | MODIFICATION HISTORY
754 | Date Author Description of Changes
758 FUNCTION check_site_access (p_person_party_id IN VARCHAR2,
755 | 09-May-2005 vnb Created
756 |
757 *=======================================================================*/
759 p_customer_id IN VARCHAR2,
760 p_customer_site_use_id IN VARCHAR2)
761 RETURN VARCHAR2
762 IS
763 user_access VARCHAR2(1) ;
764 BEGIN
765
766 SELECT 'Y'
767 INTO user_access
768 FROM dual
769 WHERE EXISTS (SELECT 'Y'
770 FROM ar_sites_assigned_v a,HZ_CUST_SITE_USES b
771 WHERE a.cust_acct_site_id = b.cust_acct_site_id
772 AND b.SITE_USE_CODE = 'BILL_TO'
773 AND party_id = p_person_party_id
774 AND site_use_id = p_customer_site_use_id );
775
776 IF user_access is not null THEN
777 RETURN 'Y' ;
778 END IF ;
779
780 RETURN 'N';
781
782 EXCEPTION WHEN OTHERS THEN
783 RETURN 'N' ;
784
785 END;
786
787 /*========================================================================
788 | PUBLIC FUNCTION check_admin_access
789 |
790 | DESCRIPTION
791 | Check if the admin identified by p_person_party_id has access to this customer.
792 |
793 | PARAMETERS
794 | p_person_party_id IN VARCHAR2
795 | p_customer_id IN VARCHAR2
796 |
797 | NOTES
798 | This does not check access at the account level - only at this particular site.
799 |
800 | MODIFICATION HISTORY
801 | Date Author Description of Changes
802 | 09-May-2005 vnb Created
803 |
804 *=======================================================================*/
805 FUNCTION check_admin_access (p_person_party_id IN VARCHAR2,
806 p_customer_id IN VARCHAR2)
807 RETURN VARCHAR2
808 IS
809 user_access VARCHAR2(1) ;
810 BEGIN
811
812 SELECT 'Y'
813 INTO user_access
814 FROM dual
815 WHERE p_customer_id IN (
816 select hca.cust_account_id
817 from hz_relationships hr,
818 hz_parties hp1,
819 hz_parties hp2,
820 hz_cust_accounts hca
821 where hr.subject_id = hp1.party_id
822 and hr.object_id = hp2.party_id
826 and hr.subject_id = p_person_party_id
823 and subject_table_name = 'HZ_PARTIES'
824 and object_table_name = 'HZ_PARTIES'
825 and hr.relationship_type IN ( 'EMPLOYMENT', 'CONTACT')
827 and hca.party_id = hp2.party_id);
828
829 IF user_access is not null THEN
830 RETURN 'Y' ;
831 END IF;
832
833 RETURN 'N';
834
835 EXCEPTION WHEN OTHERS THEN
836 RETURN 'N' ;
837
838 END;
839
840 /*========================================================================
841 | PUBLIC procedure get_contact_id
842 |
843 | DESCRIPTION
844 | Returns contact id of the given site at the customer/site level
845 | ----------------------------------------
846 |
847 | PSEUDO CODE/LOGIC
848 |
849 | PARAMETERS
850 | p_customer_id IN Customer Id
851 | p_customer_site_use_id IN Customer Site Id
852 | p_contact_role_type IN Contact Role Type
853 |
854 | RETURNS
855 | l_contact_id Contact id of the given site at the customer/site level
856 | KNOWN ISSUES
857 |
858 | MODIFICATION HISTORY
859 | Date Author Description of Changes
860 | 12-AUG-2005 rsinthre Created
861 *=======================================================================*/
862 FUNCTION get_contact_id(p_customer_id IN NUMBER,
863 p_customer_site_use_id IN NUMBER DEFAULT NULL,
864 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') RETURN NUMBER AS
865
866 l_contact_id NUMBER := null;
867
868 CURSOR contact_id_cur(p_customer_id IN NUMBER,
869 p_customer_site_use_id IN NUMBER DEFAULT NULL,
870 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') IS
871 select contact_id from (
872 select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
873 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,
877 where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
874 decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
875 from hz_cust_account_roles SUB,
876 hz_role_responsibility SROLES
878 SUB.status = 'A' AND
879 SUB.CUST_ACCOUNT_ID = p_customer_id
880 AND ( SUB.CUST_ACCT_SITE_ID = p_customer_site_use_id)
881 )
882 where last_update_record <=1
883 ORDER BY resp_code ASC, CUST_ACCT_SITE_ID ASC NULLS LAST ;
884
885 CURSOR contact_id_acct_cur(p_customer_id IN NUMBER,
886 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') IS
887 select contact_id from (
888 select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
889 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,
890 decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
891 from hz_cust_account_roles SUB,
892 hz_role_responsibility SROLES
893 where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
894 SUB.status = 'A' AND
895 SUB.CUST_ACCOUNT_ID = p_customer_id
896 AND (SUB.CUST_ACCT_SITE_ID IS NULL)
897 )
898 where last_update_record <=1
899 ORDER BY resp_code ASC, CUST_ACCT_SITE_ID ASC NULLS LAST ;
900
901 contact_id_rec contact_id_cur%ROWTYPE;
902
903 BEGIN
904
905 IF(p_customer_site_use_id IS NOT NULL AND p_customer_site_use_id <> -1) THEN
906 OPEN contact_id_cur(p_customer_id, p_customer_site_use_id, p_contact_role_type);
907 FETCH contact_id_cur INTO contact_id_rec;
908 l_contact_id := contact_id_rec.contact_id;
909 CLOSE contact_id_cur;
910 ELSE
911 OPEN contact_id_acct_cur(p_customer_id, p_contact_role_type);
912 FETCH contact_id_acct_cur INTO contact_id_rec;
913 l_contact_id := contact_id_rec.contact_id;
914 CLOSE contact_id_acct_cur;
915 END IF;
916
917 IF l_contact_id IS NOT NULL THEN
918 RETURN l_contact_id;
919 END IF;
920
921 RETURN l_contact_id;
922
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 RETURN NULL ;
926 WHEN OTHERS THEN
927 RAISE;
928 END;
929
930 /*========================================================================
931 | PUBLIC procedure get_contact
932 |
933 | DESCRIPTION
934 | Returns contact name of the given site at the customer/site level
935 | ----------------------------------------
936 |
937 | PSEUDO CODE/LOGIC
938 |
939 | PARAMETERS
940 | p_customer_id IN Customer Id
941 | p_customer_site_use_id IN Customer Site Id
942 | p_contact_role_type IN Contact Role Type
943 |
944 | RETURNS
945 | l_contact_name Contact name of the given site at the customer/site level
946 | KNOWN ISSUES
947 |
948 | MODIFICATION HISTORY
949 | Date Author Description of Changes
950 | 12-AUG-2005 rsinthre Created
951 *=======================================================================*/
952 FUNCTION get_contact(p_customer_id IN NUMBER,
953 p_customer_site_use_id IN NUMBER,
954 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 AS
955
956 l_contact_id NUMBER := NULL;
957 l_contact_name VARCHAR2(2000):= null;
958 BEGIN
959 --
960 l_contact_id := get_contact_id (p_customer_id, p_customer_site_use_id, p_contact_role_type);
961
962 IF l_contact_id IS NOT NULL THEN
963 --
964 SELECT LTRIM(substrb(PARTY.PERSON_FIRST_NAME,1,40) || ' ') ||
965 substrb(PARTY.PERSON_LAST_NAME,1,50)
966 INTO l_contact_name
967 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
968 HZ_PARTIES PARTY,
969 HZ_RELATIONSHIPS REL
970 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_contact_id
971 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
972 AND REL.SUBJECT_ID = PARTY.PARTY_ID
973 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
974 AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
975 AND DIRECTIONAL_FLAG = 'F';
976 --
977 END IF;
978
979 RETURN l_contact_name;
980
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 RETURN NULL;
984 WHEN OTHERS THEN
985 RAISE;
986 END;
987
988
989 /*========================================================================
990 | PUBLIC procedure get_contact
991 |
995 |
992 | DESCRIPTION
993 | Returns contact name of the given contact id
994 | ----------------------------------------
996 | PSEUDO CODE/LOGIC
997 |
998 | PARAMETERS
999 | p_contact_id IN Customer Id
1000 |
1001 | RETURNS
1002 | l_contact_name Contact name of the given site at the customer/site level
1003 | KNOWN ISSUES
1004 |
1005 | MODIFICATION HISTORY
1006 | Date Author Description of Changes
1007 | 24-AUG-2005 rsinthre Created
1008 | 11-SEP- 2008 avepati bug 7368288 For the New Customer search after running the
1009 | program customer text data indexing is not showing any output
1010 *=======================================================================*/
1011 FUNCTION get_contact(p_contact_id IN NUMBER) RETURN VARCHAR2 AS
1012 l_contact_name VARCHAR2(2000):= null;
1013 BEGIN
1014
1015 IF p_contact_id IS NOT NULL THEN
1016 SELECT LTRIM(substrb(PARTY.PERSON_FIRST_NAME,1,40) || ' ') ||
1017 substrb(PARTY.PERSON_LAST_NAME,1,50)
1018 INTO l_contact_name
1019 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1020 HZ_PARTIES PARTY,
1021 HZ_RELATIONSHIPS REL
1022 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1023 AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
1024 AND REL.SUBJECT_ID = PARTY.PARTY_ID
1025 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1026 AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
1027 AND DIRECTIONAL_FLAG = 'F';
1028 END IF;
1029
1030 RETURN l_contact_name;
1031
1032 EXCEPTION
1033 WHEN NO_DATA_FOUND THEN
1034 RETURN NULL ;
1038
1035 WHEN OTHERS THEN
1036 RAISE;
1037 END;
1039
1040 /*========================================================================
1041 | PUBLIC procedure get_phone
1042 |
1043 | DESCRIPTION
1044 | Returns contact point of the given contact type, site at the customer/site level
1045 | ----------------------------------------
1046 |
1047 | PSEUDO CODE/LOGIC
1048 |
1049 | PARAMETERS
1050 | p_customer_id IN Customer Id
1051 | p_customer_site_use_id IN Customer Site Id
1052 | p_contact_role_type IN Contact Role Type
1053 | p_phone_type IN contact type like 'PHONE', 'FAX', 'GEN' etc
1054 |
1055 | RETURNS
1056 | l_contact_phone Contact type number of the given site at the customer/site level
1057 | KNOWN ISSUES
1058 |
1059 | MODIFICATION HISTORY
1060 | Date Author Description of Changes
1061 | 12-AUG-2005 rsinthre Created
1062 | 24-Dec-2012 shvimal Removed DEFAULT from p_contact_role_type parameter
1063 *=======================================================================*/
1064 FUNCTION get_phone(p_customer_id IN NUMBER,
1065 p_customer_site_use_id IN NUMBER DEFAULT NULL,
1066 p_contact_role_type IN VARCHAR2,
1067 p_phone_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 AS
1068 l_phone_id NUMBER := NULL;
1069 l_contact_id NUMBER := NULL;
1070 l_contact_phone VARCHAR2(2000):= null;
1071 CURSOR phone_id_cur(p_contact_id IN NUMBER DEFAULT NULL,
1072 p_phone_type IN VARCHAR2 DEFAULT 'ALL',
1073 p_primary_flag IN VARCHAR2 DEFAULT 'Y') IS
1074 SELECT phone_id FROM
1075 ( SELECT CONT_POINT.CONTACT_POINT_ID phone_id,
1076 row_number() OVER ( order by CONT_POINT.last_update_date desc) last_update_record
1077 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1078 HZ_CONTACT_POINTS CONT_POINT
1079 WHERE
1080 ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1081 AND ACCT_ROLE.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
1082 AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
1083 AND CONT_POINT.STATUS = 'A'
1084 AND INSTRB(NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE) || 'ALL', p_phone_type) > 0
1085 AND CONT_POINT.PRIMARY_FLAG = p_primary_flag
1086 )
1087 WHERE last_update_record<=1;
1088
1089 phone_id_rec phone_id_cur%ROWTYPE;
1090
1091 BEGIN
1092 --
1093 l_contact_id := get_contact_id (p_customer_id, p_customer_site_use_id, p_contact_role_type);
1094
1095
1096
1097 IF l_contact_id IS NOT NULL THEN
1098 --
1099 OPEN phone_id_cur(l_contact_id, p_phone_type ,'Y');
1100 FETCH phone_id_cur INTO phone_id_rec;
1101 l_phone_id := phone_id_rec.phone_id;
1102 CLOSE phone_id_cur;
1103
1104 IF l_phone_id IS NULL THEN
1105 OPEN phone_id_cur(l_contact_id, p_phone_type ,'N');
1106 FETCH phone_id_cur INTO phone_id_rec;
1107 l_phone_id := phone_id_rec.phone_id;
1108 CLOSE phone_id_cur;
1109 END IF;
1110 --
1111 END IF;
1112 --
1113 IF l_phone_id IS NOT NULL THEN
1114 --
1115 SELECT RTRIM(LTRIM(cont_point.PHONE_AREA_CODE || '-' ||
1116 DECODE(CONT_POINT.CONTACT_POINT_TYPE,'TLX',
1117 CONT_POINT.TELEX_NUMBER,
1118 CONT_POINT.PHONE_NUMBER)||'-'||
1119 CONT_POINT.PHONE_EXTENSION, '-'), '-')
1120 INTO l_contact_phone
1121 FROM HZ_CONTACT_POINTS CONT_POINT
1122 WHERE CONT_POINT.CONTACT_POINT_ID = l_phone_id;
1123 --
1124 END IF;
1125
1126 RETURN l_contact_phone;
1127
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 RAISE;
1131 END;
1132
1133
1134 /*========================================================================
1135 | PUBLIC procedure get_phone
1136 |
1137 | DESCRIPTION
1138 | Returns contact point of the given contact id
1139 | ----------------------------------------
1140 | PSEUDO CODE/LOGIC
1141 |
1142 | PARAMETERS
1143 | p_contact_id IN Customer Id
1144 | p_phone_type IN contact type like 'PHONE', 'FAX', 'GEN' etc
1145 |
1146 | RETURNS
1147 | l_contact_phone Contact type number of the given site at the customer/site level
1148 | KNOWN ISSUES
1149 |
1150 | MODIFICATION HISTORY
1151 | Date Author Description of Changes
1152 | 5-JUL-2005 hikumar Created
1153 *=======================================================================*/
1154 FUNCTION get_phone(p_contact_id IN NUMBER,
1155 p_phone_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2 AS
1156 l_phone_id NUMBER := NULL;
1157 l_contact_phone VARCHAR2(2000):= null;
1158 CURSOR phone_id_cur(p_contact_id IN NUMBER DEFAULT NULL,
1159 p_phone_type IN VARCHAR2 DEFAULT 'ALL',
1160 p_primary_flag IN VARCHAR2 DEFAULT 'Y') IS
1161 SELECT phone_id FROM
1162 ( SELECT CONT_POINT.CONTACT_POINT_ID phone_id,
1163 row_number() OVER ( order by CONT_POINT.last_update_date desc) last_update_record
1164 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1165 HZ_CONTACT_POINTS CONT_POINT
1166 WHERE
1167 ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1168 AND ACCT_ROLE.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
1169 AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
1170 AND CONT_POINT.STATUS = 'A'
1171 AND INSTRB(NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE) || 'ALL', p_phone_type) > 0
1172 AND CONT_POINT.PRIMARY_FLAG = p_primary_flag
1176 phone_id_rec phone_id_cur%ROWTYPE;
1173 )
1174 WHERE last_update_record<=1;
1175
1177
1178 BEGIN
1179 --
1180 IF p_contact_id IS NOT NULL THEN
1181 --
1182 OPEN phone_id_cur(p_contact_id, p_phone_type ,'Y');
1183 FETCH phone_id_cur INTO phone_id_rec;
1184 l_phone_id := phone_id_rec.phone_id;
1185 CLOSE phone_id_cur;
1186
1187 IF l_phone_id IS NULL THEN
1188 OPEN phone_id_cur(p_contact_id, p_phone_type ,'N');
1189 FETCH phone_id_cur INTO phone_id_rec;
1190 l_phone_id := phone_id_rec.phone_id;
1191 CLOSE phone_id_cur;
1192 END IF;
1193 --
1194 END IF;
1195 --
1196 IF l_phone_id IS NOT NULL THEN
1197 --
1198 SELECT RTRIM(LTRIM(cont_point.PHONE_AREA_CODE || '-' ||
1199 DECODE(CONT_POINT.CONTACT_POINT_TYPE,'TLX',
1200 CONT_POINT.TELEX_NUMBER,
1201 CONT_POINT.PHONE_NUMBER)||'-'||
1202 CONT_POINT.PHONE_EXTENSION, '-'), '-')
1203 INTO l_contact_phone
1204 FROM HZ_CONTACT_POINTS CONT_POINT
1205 WHERE CONT_POINT.CONTACT_POINT_ID = l_phone_id;
1206 --
1207 END IF;
1208
1209 RETURN l_contact_phone;
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 RAISE;
1214 END;
1215
1216
1217
1218
1219 FUNCTION save_payment_instrument_info ( p_customer_id IN VARCHAR2,
1220 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN
1221 IS
1222 l_attr varchar2(15):=null;
1223 current_org_id NUMBER ;
1224 BEGIN
1225 -- If you do not want to save credit card info set this
1226 -- flag to false.
1227 -- Note:
1228 -- If this is set to false, you cannot use Bank Account
1229 -- to pay. Please disable bank account ACH payment method
1230
1231 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1232 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_SAVE_PAYMENT_INSTRUMENT', NULL,current_org_id) ) THEN
1233
1234 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_SAVE_PAYMENT_INSTRUMENT', 'AR', NULL, current_org_id );
1235 FUN_RULE_PUB.init_parameter_list;
1236 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1237 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1238 FUN_RULE_PUB.apply_rule('AR','ARI_SAVE_PAYMENT_INSTRUMENT');
1239 l_attr := FUN_RULE_PUB.get_string;
1240 if(l_attr is not null) then
1241 if l_attr='Y'then
1242 return true;
1243 else
1244 return false;
1245 end if;
1246 end if;
1247 ELSIF (nvl(FND_PROFILE.VALUE('OIR_SAVE_PAYMENT_INSTR_INFO'),'N') = 'N') THEN
1248 RETURN FALSE;
1249 ELSE
1250 RETURN TRUE;
1251 END IF;
1252
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 RETURN TRUE;
1256
1257 END save_payment_instrument_info;
1258
1259
1260 FUNCTION is_save_payment_instr_enabled ( p_customer_id IN VARCHAR2,
1261 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1262 IS
1263 BEGIN
1264 IF save_payment_instrument_info(p_customer_id, p_customer_site_use_id) THEN
1265 RETURN 'Y';
1266 ELSE
1267 RETURN 'N';
1268 END IF;
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 RETURN 'Y';
1273
1274 END is_save_payment_instr_enabled;
1275
1276
1277
1278
1279 FUNCTION is_aging_enabled ( p_customer_id IN VARCHAR2,
1280 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1281 IS
1282 l_attr varchar2(15):=NULL;
1283 current_org_id NUMBER ;
1284 BEGIN
1285 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1286
1287 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_AGING_BUCKETS', NULL,current_org_id) ) THEN
1288
1289 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_AGING_BUCKETS', 'AR', NULL, current_org_id );
1290 FUN_RULE_PUB.init_parameter_list;
1291 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1292 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1293 FUN_RULE_PUB.apply_rule('AR','ARI_AGING_BUCKETS');
1294 l_attr := FUN_RULE_PUB.get_string;
1295
1296 IF(l_attr IS NOT NULL) THEN
1297 RETURN l_attr;
1298 ELSE
1299 RETURN (NVL(FND_PROFILE.VALUE('OIR_AGING_BUCKETS'),'0'));
1300 END IF;
1301 ELSE
1302 RETURN (NVL(FND_PROFILE.VALUE('OIR_AGING_BUCKETS'),'0'));
1303 END IF;
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 RETURN '0';
1307
1308 END is_aging_enabled;
1309
1310
1311
1312 FUNCTION multi_print_limit ( p_customer_id IN VARCHAR2,
1313 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1314 IS
1315 l_attr varchar2(15):=null;
1316 current_org_id NUMBER ;
1317 BEGIN
1318 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1319 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_MULTI_PRINT_LIMIT', NULL,current_org_id) ) THEN
1320
1321 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_MULTI_PRINT_LIMIT', 'AR', NULL, current_org_id );
1322 FUN_RULE_PUB.init_parameter_list;
1323 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1324 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1328 RETURN l_attr;
1325 FUN_RULE_PUB.apply_rule('AR','ARI_MULTI_PRINT_LIMIT');
1326 l_attr := FUN_RULE_PUB.get_string;
1327 IF(l_attr IS NOT NULL) THEN
1329 ELSE
1330 RETURN (NVL(FND_PROFILE.VALUE('OIR_BPA_MULTI_PRINT_LIMIT'),'0'));
1331 END IF;
1332 ELSE
1333 RETURN (NVL(FND_PROFILE.VALUE('OIR_BPA_MULTI_PRINT_LIMIT'),'0'));
1334 END IF;
1335
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 RETURN '0';
1339
1340 END multi_print_limit;
1341
1342
1343
1347 l_attr varchar2(15):=null;
1344 FUNCTION is_discount_grace_days_enabled ( p_customer_id IN VARCHAR2,
1345 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
1346 IS
1348 current_org_id NUMBER ;
1349 BEGIN
1350 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1351 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_DISCOUNT_GRACE_DAYS', NULL,current_org_id) ) THEN
1352
1353 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_DISCOUNT_GRACE_DAYS', 'AR', NULL, current_org_id );
1354 FUN_RULE_PUB.init_parameter_list;
1355 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1356 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1357 FUN_RULE_PUB.apply_rule('AR','ARI_DISCOUNT_GRACE_DAYS');
1358 l_attr := FUN_RULE_PUB.get_string;
1359 IF(l_attr IS NOT NULL) THEN
1360 RETURN l_attr;
1361 ELSE
1362 RETURN (NVL(FND_PROFILE.VALUE('OIR_ENABLE_DISCOUNT_GRACE_DAYS'),'N'));
1363 END IF;
1364 ELSE
1365 RETURN (NVL(FND_PROFILE.VALUE('OIR_ENABLE_DISCOUNT_GRACE_DAYS'),'N'));
1366 END IF;
1367
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 RETURN 'N';
1371
1372 END is_discount_grace_days_enabled;
1373
1374
1375 FUNCTION is_service_charge_enabled ( p_customer_id IN VARCHAR2,
1376 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN boolean
1377 IS
1378 l_attr varchar2(15):=null;
1379 current_org_id NUMBER ;
1380 BEGIN
1381 -- This can be configured to return the appropriate value based on
1382 -- the service charge needs to be applied
1383
1384 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1385 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_SERVICE_CHARGE_ENABLED', NULL,current_org_id) ) THEN
1386
1387 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_SERVICE_CHARGE_ENABLED', 'AR', NULL, current_org_id );
1388 FUN_RULE_PUB.init_parameter_list;
1389 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1390 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1391 FUN_RULE_PUB.apply_rule('AR','ARI_SERVICE_CHARGE_ENABLED');
1392 l_attr := FUN_RULE_PUB.get_string;
1393 if(l_attr is not null) then
1394 if l_attr='Y' then
1395 return true;
1396 else
1397 return false;
1398 end if;
1399 end if;
1400
1401 ELSIF (nvl(FND_PROFILE.VALUE('OIR_ENABLE_SERVICE_CHARGE'),'N') = 'Y') THEN
1402 RETURN TRUE;
1403 ELSE
1404 RETURN FALSE;
1405 END IF;
1406
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 RETURN FALSE;
1410
1411 END is_service_charge_enabled;
1412
1413
1414
1415
1416 FUNCTION is_discount_grace_days_enabled RETURN BOOLEAN
1417 IS
1418 BEGIN
1419 -- This can be configured to return the appropriate value based on
1420 -- whether grace days have to be picked up for discounts.
1421 IF (nvl(FND_PROFILE.VALUE('OIR_ENABLE_DISCOUNT_GRACE_DAYS'),'N') = 'Y') THEN
1422 RETURN TRUE;
1423 ELSE
1424 RETURN FALSE;
1425 END IF;
1426
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 RETURN FALSE;
1430 END is_discount_grace_days_enabled;
1431
1432
1433
1434
1435
1436 FUNCTION get_service_charge_activity_id ( p_customer_id IN VARCHAR2,
1437 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL ) RETURN NUMBER
1438 IS
1439 CURSOR SYSPARAMCUR IS
1440 SELECT IREC_SERVICE_CHARGE_REC_TRX_ID FROM AR_SYSTEM_PARAMETERS;
1441 l_attr varchar2(15):=null;
1442 current_org_id NUMBER ;
1443 BEGIN
1444 -- This is the activity id for service charge
1445 -- Please configure this to the activity id at installation site
1446 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1447 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_SERVICE_CHARGE_ACTIVITY_ID', NULL,current_org_id) ) THEN
1448
1449 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_SERVICE_CHARGE_ACTIVITY_ID', 'AR', NULL, current_org_id );
1450 FUN_RULE_PUB.init_parameter_list;
1451 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1455 if(l_attr is not null) then
1452 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1453 FUN_RULE_PUB.apply_rule('AR','ARI_SERVICE_CHARGE_ACTIVITY_ID');
1454 l_attr := FUN_RULE_PUB.get_string;
1456 return to_number(l_attr);
1457 end if;
1458
1459 END IF ;
1460
1461 FOR rec IN SYSPARAMCUR
1462 LOOP
1463 RETURN NVL(rec.IREC_SERVICE_CHARGE_REC_TRX_ID,0);
1464 END LOOP;
1465
1466 RETURN 0 ;
1467
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 RETURN 0;
1471 END get_service_charge_activity_id;
1472
1473
1474 PROCEDURE get_contact_info (
1475 p_customer_id IN VARCHAR2,
1476 p_customer_site_use_id IN VARCHAR2,
1477 p_language_string IN VARCHAR2,
1478 p_page IN VARCHAR2,
1479 p_trx_id IN VARCHAR2,
1480 p_output_string OUT NOCOPY VARCHAR2
1481 ) IS
1482 l_attr varchar2(200):=null;
1483 current_org_id NUMBER ;
1484 l_org_id NUMBER;
1485 BEGIN
1486
1487 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1488 --Bug 12719843 - In Customer Search Page, since the Org Id set is -1 always, get the MO:Default Operating Unit
1489 --value in the page and apply the rule against the Default OU.
1490 if('ARI_CUSTOMER_SEARCH' = p_page) then
1491 l_org_id := fnd_profile.value('DEFAULT_ORG_ID');
1492 if(l_org_id is not null AND l_org_id <> '-1') then
1493 current_org_id := l_org_id;
1494 end if;
1495 end if;
1496
1497 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1498 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||'GET_CONTACT_INFO','current_org_id => ' || current_org_id);
1499 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||'GET_CONTACT_INFO','p_page => ' || p_page);
1500 end if;
1501
1502 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_CONTACT_INFO', NULL,current_org_id) ) THEN
1503
1504 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_CONTACT_INFO', 'AR', NULL, current_org_id );
1505 FUN_RULE_PUB.init_parameter_list;
1506 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1507 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1508 FUN_RULE_PUB.add_parameter('ARI_LANGUAGE_STRING',p_language_string);
1509 FUN_RULE_PUB.add_parameter('ARI_PAGE',p_page);
1510 /* Fix for the Bug# 5054123. The below parameter 'ARI_TRX_ID' is not used anywhere.
1511 * Moreover, it can sometimes take the value of a URL and so it is not always a number.
1515 FUN_RULE_PUB.apply_rule('AR','ARI_CONTACT_INFO');
1512 * So the below conversion can result in an error (Eg: for DISPUTE) and so it is commented out.
1513 */
1514 --FUN_RULE_PUB.add_parameter('ARI_TRX_ID',to_number(p_trx_id));
1516
1517 l_attr := FUN_RULE_PUB.get_string;
1518 if (l_attr is null) then
1519 SELECT WEB_HTML_CALL INTO l_attr FROM FND_FORM_FUNCTIONS WHERE FUNCTION_NAME LIKE 'ARICONTACTUS';
1520 end if;
1521
1522 p_output_string := l_attr;
1523
1524 ELSE
1525 SELECT WEB_HTML_CALL INTO p_output_string FROM FND_FORM_FUNCTIONS WHERE FUNCTION_NAME LIKE 'ARICONTACTUS';
1526 END IF ;
1527
1528 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1529 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||'GET_CONTACT_INFO','p_output_string => ' || p_output_string);
1530 end if;
1531
1532 END get_contact_info;
1533
1534
1535
1536
1537 FUNCTION get_max_future_payment_date( p_customer_id IN VARCHAR2,
1538 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN DATE
1539 IS
1540 l_attr varchar2(15):=null;
1541 current_org_id NUMBER ;
1542 BEGIN
1543 -- This date will be used to validate that any future dated payments
1544 -- are not beyond this date.
1545
1546 current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
1547 IF (FUN_RULE_OBJECTS_PUB.rule_object_instance_exists(222,'ARI_MAX_FUTURE_PAYMENT_DATE', NULL,current_org_id) ) THEN
1548
1549 FUN_RULE_PUB.SET_INSTANCE_CONTEXT('ARI_MAX_FUTURE_PAYMENT_DATE', 'AR', NULL, current_org_id );
1550 FUN_RULE_PUB.init_parameter_list;
1551 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_ID',to_number(p_customer_id));
1552 FUN_RULE_PUB.add_parameter('ARI_CUSTOMER_SITE_USE_ID',to_number(p_customer_site_use_id));
1553 FUN_RULE_PUB.apply_rule('AR','ARI_MAX_FUTURE_PAYMENT_DATE');
1554 l_attr := FUN_RULE_PUB.get_string;
1555 if(l_attr is not null) then
1556 RETURN TRUNC(SYSDATE+to_number(l_attr));
1557 end if;
1558
1559 END IF;
1560
1561 RETURN TRUNC(SYSDATE + NVL(FND_PROFILE.VALUE('OIR_MAX_FUTURE_PAYMENT_DAYS_ALLOWED'),365));
1562
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 RETURN TRUNC(SYSDATE + 365);
1566
1567 END get_max_future_payment_date;
1568
1569
1570
1571
1572 FUNCTION get_site_use_location (p_address_id IN NUMBER) RETURN VARCHAR2 AS
1573 --
1577 --
1574 l_site_uses VARCHAR2(4000) := '';
1575 --
1576 l_separator VARCHAR2(2) := '';
1578 CURSOR c01 (addr_id VARCHAR2) IS
1579 SELECT
1580 unique( LOCATION)
1581 FROM
1582 hz_cust_site_uses
1583 WHERE
1584 cust_acct_site_id = addr_id
1585 AND status = 'A' ;
1586 l_procedure_name VARCHAR2(50);
1587 l_debug_info VARCHAR2(200);
1588 --
1589 BEGIN
1590 --
1591
1592 --
1593 l_procedure_name := '.get_site_use_location';
1594 ----------------------------------------------------------------------------------------
1595 l_debug_info := 'Fetch Bill to Location';
1596 -----------------------------------------------------------------------------------------
1597 IF (PG_DEBUG = 'Y') THEN
1598 arp_standard.debug(l_debug_info);
1599 END IF;
1600
1601 FOR c01_rec IN c01 (p_address_id) LOOP
1602 l_site_uses := l_site_uses || l_separator ||c01_rec.location;
1603
1604 IF l_separator IS NULL THEN
1605 l_separator := ', ';
1606 END IF;
1607
1608 END LOOP;
1609 --
1610 RETURN l_site_uses;
1611
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 IF (PG_DEBUG = 'Y') THEN
1615 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1616 arp_standard.debug('ERROR =>'|| SQLERRM);
1617 arp_standard.debug('Debug Info : '||l_debug_info);
1618 END IF;
1619
1620 END;
1621
1622 /*========================================================================
1623 | PUBLIC function get_site_use_code
1624 |
1625 | DESCRIPTION
1626 | Function returns the site use codes for the given adddress id
1627 |
1628 | PARAMETERS
1629 | p_address_id IN NUMBER
1630 |
1631 | RETURNS
1632 | Site Use Codes for the given address id.
1633 |
1634 | MODIFICATION HISTORY
1635 | Date Author Description of Changes
1636 | 17-JAN-2006 rsinthre Created
1637 | 21-JAN-2007 abathini Modified for Bug 6503280
1638 *=======================================================================*/
1639 FUNCTION get_site_use_code (p_address_id IN NUMBER) RETURN VARCHAR2 AS
1640 l_site_use_codes VARCHAR2(4000) := '';
1641 l_separator VARCHAR2(2) := '';
1642 CURSOR c01 (addr_id VARCHAR2) IS
1643 SELECT
1644 SITE_USE_CODE, SITE_USE_ID
1645 FROM
1646 hz_cust_site_uses
1647 WHERE
1648 cust_acct_site_id = addr_id;
1649 --AND status = 'A' ;
1650 /*Bug 6503280: Commented out above condition on checking status='A'
1651 * to allow Drill Down from Inactive Sites from Customer Search Page*/
1652 l_procedure_name VARCHAR2(50);
1653 l_debug_info VARCHAR2(200);
1654 --
1655 BEGIN
1656 --
1657 G_BILL_TO_SITE_USE_ID := 0;
1658 --
1659 l_procedure_name := '.get_site_use_code';
1660 ----------------------------------------------------------------------------------------
1661 l_debug_info := 'Fetch Bill to Site use id';
1662 -----------------------------------------------------------------------------------------
1663 IF (PG_DEBUG = 'Y') THEN
1664 arp_standard.debug(l_debug_info);
1665 END IF;
1666
1667 FOR c01_rec IN c01 (p_address_id) LOOP
1668 l_site_use_codes := l_site_use_codes || l_separator || c01_rec.site_use_code;
1669
1670 IF c01_rec.site_use_code = 'BILL_TO' THEN
1671 G_BILL_TO_SITE_USE_ID := c01_rec.site_use_id;
1672 END IF;
1673
1674 IF l_separator IS NULL THEN
1675 l_separator := ', ';
1676 END IF;
1677
1678 END LOOP;
1679 --
1680 RETURN l_site_use_codes;
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 IF (PG_DEBUG = 'Y') THEN
1685 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1686 arp_standard.debug('ERROR =>'|| SQLERRM);
1687 arp_standard.debug('Debug Info : '||l_debug_info);
1688 END IF;
1689
1690
1691
1692 END get_site_use_code;
1693
1694 /*===========================================================================+
1695 | FUNCTION validate_ACH_routing_number |
1696 | |
1697 | DESCRIPTION |
1698 | This function validates that given routing number is an existing ACH |
1699 | bank. |
1700 | |
1701 | SCOPE - PUBLIC |
1702 | |
1703 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1704 | None |
1705 | |
1706 | ARGUMENTS : IN: p_routing_number Routing Number |
1707 | |
1708 | RETURNS : 1 routing number is valid |
1712 | 27-Aug-2009 avepati Created |
1709 | 0 routing number is invalid |
1710 | |
1711 | MODIFICATION HISTORY |
1713 | |
1714 +===========================================================================*/
1715 FUNCTION validate_ACH_routing_number(p_routing_number IN VARCHAR2) RETURN NUMBER IS
1716 /*-----------------------------------------------------+
1717 | Cursor to fetch bank branch based on routing number |
1718 +-----------------------------------------------------*/
1719 CURSOR bank_branch_cur IS
1720 SELECT branch_party_id bank_branch_id
1721 FROM ce_bank_branches_v
1722 WHERE branch_number = p_routing_number
1723 and nvl(trunc(end_date), trunc(sysdate)) >= trunc(sysdate);
1724
1725 CURSOR bank_directory_cur IS
1726 SELECT bank_name
1727 FROM AR_BANK_DIRECTORY
1728 WHERE routing_number = p_routing_number;
1729
1730 bank_branch_rec bank_branch_cur%ROWTYPE;
1731 bank_directory_rec bank_directory_cur%ROWTYPE;
1732 l_routing_number_validation fnd_profile_option_values.profile_option_value%TYPE;
1733 l_result NUMBER;
1734 l_procedure_name VARCHAR2(30);
1735
1736 BEGIN
1737
1738 l_procedure_name := '.validate_ACH_routing_number';
1739
1740 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1741 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Begin validate_ACH_routing_number');
1742 end if;
1743
1744
1745 /*--------------------------------------------------------------------+
1746 | Validate that the routing number cheksum is correct |
1747 | bug # 9283134 - canadian transit numbers are not accepted in irec |
1748 +--------------------------------------------------------------------*/
1749 l_routing_number_validation := FND_PROFILE.value('OIR_SKIP_ROUT_NUM_VALID');
1750 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1751 fnd_log.string(fnd_log.LEVEL_STATEMENT,'.validate_ACH_routing_number',
1752 ' OIR: Skip Routing Number Validation :: '||l_routing_number_validation);
1753 end if;
1754
1755 if l_routing_number_validation = 'Y' then
1756 l_result := 1;
1757 else
1758 l_result := validate_ACH_checksum(p_routing_number);
1759 end if;
1760
1761 IF l_result = 0 THEN
1762 RETURN 0;
1763 END IF;
1764
1765 /*-------------------------------------------------------------+
1766 | Validate if the routing number already exists in the system |
1767 +-------------------------------------------------------------*/
1768 OPEN bank_branch_cur;
1769 FETCH bank_branch_cur INTO bank_branch_rec;
1770 IF (bank_branch_cur%FOUND) then
1771 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1772 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Found routing number in ce_bank_branches_v');
1773 end if;
1774 CLOSE bank_branch_cur;
1775 RETURN 1;
1776 ELSE
1777 CLOSE bank_branch_cur;
1778 END IF;
1779
1780 l_routing_number_validation := NVL(FND_PROFILE.value('AR_BANK_DIRECTORY_SOURCE'),'NONE');
1781
1782 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1783 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'profile AR_BANK_DIRECTORY_SOURCE value :: '||l_routing_number_validation);
1784 end if;
1785
1786 /*-----------------------------------------------------+
1787 | If source is 'NONE' then no validate routing number against AR_BANKD_DIRECTORY |
1788 +-----------------------------------------------------*/
1789 IF NVL(l_routing_number_validation,'NONE') <> 'NONE' THEN
1790
1791 OPEN bank_directory_cur;
1792 FETCH bank_directory_cur INTO bank_directory_rec;
1793 IF (bank_directory_cur%FOUND) then
1794 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1795 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Found routing number in AR_BANK_DIRECTORY');
1796 end if;
1797 CLOSE bank_directory_cur;
1798 RETURN 1;
1799 ELSE
1800 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1801 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Routing Number not found in AR_BANK_DIRECTORY');
1802 end if;
1803 CLOSE bank_directory_cur;
1804 RETURN 0;
1805 END IF;
1806
1807 ELSE
1808 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1809 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Routing Number not found in ce_bank_branches_v');
1810 end if;
1811 RETURN 0;
1812 END IF;
1813
1814 END validate_ACH_routing_number;
1815
1816 /*========================================================================
1817 | PUBLIC function is_routing_number_valid
1818 |
1819 | DESCRIPTION
1820 | Determines if a given routing number is valid.
1821 | ----------------------------------------
1822 |
1823 | PSEUDO CODE/LOGIC
1824 | This function validates routing number, note currently it only
1825 | validates US specific ABA (ACH) routing number. When other
1826 | types are added also new logic needs to be introduced.
1827 |
1828 | PARAMETERS
1829 | p_routing_number IN Routing number
1830 | p_routing_number_type IN Routing number type, defaults to ABA
1831 |
1832 | RETURNS
1833 | 1 if Routing number is valid
1837 |
1834 | 0 if Routing number is invalid
1835 |
1836 | KNOWN ISSUES
1838 | NOTES
1839 |
1840 | MODIFICATION HISTORY
1841 | Date Author Description of Changes
1842 | 27-Aug-2009 avepati Created
1843 |
1844 *=======================================================================*/
1845 FUNCTION is_routing_number_valid(p_routing_number IN VARCHAR2,
1846 p_routing_number_type IN VARCHAR2 DEFAULT 'ABA') RETURN NUMBER IS
1847
1848 BEGIN
1849
1850 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1851 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME,' Begin is_routing_number_valid ');
1852 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME,' p_routing_number_type :: '||p_routing_number_type);
1853 end if;
1854
1855 if p_routing_number_type = 'ABA' then
1856
1857 return validate_ACH_routing_number(p_routing_number);
1858
1859 else
1860 return 0;
1861 end if;
1862
1863 END is_routing_number_valid;
1864
1865 /*========================================================================
1866 | PUBLIC function validate_ACH_checksum
1867 |
1868 | DESCRIPTION
1869 | Determines if a given ACH routing number checksum is valid.
1870 | ----------------------------------------
1871 |
1872 | PSEUDO CODE/LOGIC
1873 | This function validates US specific ACH routing number.
1874 | Note that even if a number passes this test, it does not
1875 | necessarily mean that it is valid. The number may not, in fact,
1876 | be assigned to any financial institution. ACH routing numbers are
1877 | always nine digits long. The first four specify the routing
1878 | symbol, the next four identify the institution and the last is
1879 | the checksum digit.
1880 | Here's how the algorithm works. First the code strips out any non-numeric characters
1881 | (like dashes or spaces) and makes sure the resulting string's length is nine digits,
1882 | 7 8 9 4 5 6 1 2 4
1883 | Then we multiply the first digit by 3, the second by 7, the third by 1, the fourth by 3,
1884 | the fifth by 7, the sixth by 1, etc., and add them all up.
1885 | (7 x 3) + (8 x 7) + (9 x 1) +
1886 | (4 x 3) + (5 x 7) + (6 x 1) +
1887 | (1 x 3) + (2 x 7) + (4 x 1) = 160
1888 | If this sum is an integer multiple of 10 (e.g., 10, 20, 30, 40, 50,...) then the number
1889 | is valid, as far as the checksum is concerned.
1890 |
1891 | PARAMETERS
1892 | p_routing_number IN ACH Routing number
1893 |
1894 | RETURNS
1895 | TRUE if ACH Routing number is valid
1896 | FALSE if ACH Routing number is invalid
1897 |
1898 | KNOWN ISSUES
1899 |
1900 |
1901 |
1902 | NOTES
1903 |
1904 |
1905 |
1906 | MODIFICATION HISTORY
1907 | Date Author Description of Changes
1908 | 27-Aug-2009 avepati Created
1909 |
1910 *=======================================================================*/
1911 FUNCTION validate_ACH_checksum (p_routing_number IN VARCHAR2) RETURN number IS
1912
1913 l_routing_num_stripped ap_bank_accounts.bank_account_num%TYPE;
1914 cheksum NUMBER := 0;
1915 counter NUMBER := 1;
1916
1917 BEGIN
1918
1919 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1920 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME,' Begin validate_ACH_checksum ');
1921 end if;
1922
1923 /*---------------------------------+
1924 | Remove all non-digit characters |
1925 +---------------------------------*/
1926 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1927 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME,' calling ari_utilities.strip_white_spaces p_routing_number ::'||p_routing_number);
1928 end if;
1929
1930 ari_utilities.strip_white_spaces (p_routing_number,
1931 l_routing_num_stripped);
1932
1933 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1934 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME,' Stripped Routing number l_routing_num_stripped ::'||l_routing_num_stripped);
1935 end if;
1936
1937 /*--------------------------------------------+
1938 | ACH routing number has to be 9 digits long |
1939 +--------------------------------------------*/
1940 if length(l_routing_num_stripped) <> 9 then
1941 return 0;
1942 else
1943 /*---------------------------------------------------+
1944 | Loop through the routing number incrementing by 3 |
1945 +---------------------------------------------------*/
1946 while counter < length(l_routing_num_stripped) loop
1947 /*------------------------------------------+
1948 | Multiply digits by the algorithm numbers |
1949 +------------------------------------------*/
1950 cheksum := cheksum +
1951 to_number(substr(l_routing_num_stripped,counter,1)) * 3 +
1952 to_number(substr(l_routing_num_stripped,counter+1,1)) * 7 +
1953 to_number(substr(l_routing_num_stripped,counter+2,1));
1954
1955 counter := counter + 3;
1956
1957 end loop;
1958
1959 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1960 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME,' Routing number checksum ::'||cheksum);
1961 end if; /*-------------------------------------------------+
1962 | If the resulting sum is an even multiple of ten |
1963 | (but not zero), the ach routing number is good. |
1964 +-------------------------------------------------*/
1965 if (cheksum <> 0 and mod(cheksum,10) = 0) then
1966 return 1;
1967 else
1968 return 0;
1969 end if;
1970 end if;
1971
1975 | PROCEDURE strip_white_spaces |
1972 END validate_ACH_checksum;
1973
1974 /*===========================================================================+
1976 | |
1977 | DESCRIPTION |
1978 | This proc stips out any non numberic characters like sapces,dashes etc |
1979 | |
1980 | SCOPE - PUBLIC |
1981 | |
1982 | |
1983 | ARGUMENTS : IN: p_num_to_strip Number to be stripped |
1984 | |
1985 | RETURNS : OUT: p_stripped_num Stripped number |
1986 | |
1987 | MODIFICATION HISTORY |
1988 | 27-Aug-2009 avepati Created |
1989 | |
1990 +===========================================================================*/
1991
1992 PROCEDURE strip_white_spaces(
1993 p_num_to_strip IN AP_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE,
1994 p_stripped_num OUT NOCOPY AP_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE
1995 ) IS
1996
1997 TYPE character_tab_typ IS TABLE of char(1) INDEX BY BINARY_INTEGER;
1998 len_strip_num number := 0;
1999 l_strip_num_char character_tab_typ;
2000 BEGIN
2001 IF PG_DEBUG in ('Y', 'C') THEN
2002 arp_standard.debug('ari_utilities.strip_white_spaces()+');
2003 END IF;
2004
2005 SELECT lengthb(p_num_to_strip)
2006 INTO len_strip_num
2007 FROM dual;
2008
2009 FOR i in 1..len_strip_num LOOP
2010 SELECT substrb(p_num_to_strip,i,1)
2011 INTO l_strip_num_char(i)
2012 FROM dual;
2013
2014 IF ( (l_strip_num_char(i) >= '0') and
2015 (l_strip_num_char(i) <= '9')
2016 )
2017 THEN
2018 -- Numeric digit. Add to stripped_number and table.
2019 p_stripped_num := p_stripped_num || l_strip_num_char(i);
2020 END IF;
2021 END LOOP;
2022
2023 IF PG_DEBUG in ('Y', 'C') THEN
2024 arp_standard.debug('ari_utilities.strip_white_spaces()-');
2025 END IF;
2026 EXCEPTION
2027 when OTHERS then
2028 raise;
2029 END strip_white_spaces;
2030
2031 FUNCTION get_group_header(p_customer_id IN NUMBER,
2032 p_party_id IN NUMBER , p_trx_number IN VARCHAR) RETURN NUMBER AS
2033
2034 l_account_access_count NUMBER := NULL;
2035 l_site_access_count NUMBER :=NULL;
2036 l_flag NUMBER := NULL;
2037
2038 BEGIN
2039
2040 select count(*) into l_account_access_count from ar_customers_assigned_v hzca where hzca.cust_account_id = p_customer_id
2041 and hzca.party_id=p_party_id;
2042
2043
2044 IF l_account_access_count > 0 THEN
2045 RETURN 0;
2046 END IF;
2047
2048 select count(*) into l_site_access_count from ar_sites_assigned_v acct_sites_count
2049 where acct_sites_count.party_id=p_party_id
2050 and acct_sites_count.cust_account_id=p_customer_id
2051 and INSTR(ARI_UTILITIES.GET_SITE_USE_CODE(acct_sites_count.CUST_ACCT_SITE_ID), 'BILL_TO')>0;
2052
2053 select count(*) into l_flag from(
2054 select trx_number,CUSTOMER_SITE_USE_ID from ar_payment_schedules where trx_number=p_trx_number
2055 and CUSTOMER_SITE_USE_ID in
2056 (
2057 select ARI_UTILITIES.get_bill_to_site_use_id(CUST_ACCT_SITE_ID) from ar_sites_assigned_v where
2058 party_id=p_party_id
2059 and cust_account_id=p_customer_id
2060 )
2061 );
2062
2063 IF l_site_access_count > 1 AND l_flag > 0 THEN
2064 RETURN 1;
2065 ELSE
2066 RETURN 2;
2067 END IF;
2068
2069 END get_group_header;
2070
2071 FUNCTION invoke_invoice_email_notwf( p_subscription_guid In RAW , p_event IN OUT NOCOPY WF_EVENT_T ) return varchar2 AS
2072
2073 l_trx_number VARCHAR2(30);
2074 l_customer_trx_id NUMBER(15);
2075 l_org_id NUMBER;
2076 l_user_id NUMBER;
2077 l_resp_id NUMBER;
2078 l_application_id NUMBER;
2079
2080 l_customer_id NUMBER(15) := NULL;
2081 l_customer_acct_site_id NUMBER(15) := NULL;
2082 l_customer_acct_number NUMBER(15) := NULL;
2083 l_customer_acct_name hz_parties.party_name%TYPE;
2084
2085
2086 l_procedure_name VARCHAR2(30) := '.invoke_invoice_email_notwf';
2087 l_debug_info VARCHAR2(500);
2088
2089 l_itemtype VARCHAR2(20) := 'ARINVNTF';
2090
2091 BEGIN
2092
2093 --------------------------------------------------------------------
2094 l_debug_info := 'In debug mode, log we have entered this procedure';
2095 --------------------------------------------------------------------
2096 IF (PG_DEBUG = 'Y') THEN
2097 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
2098 END IF;
2099
2100 l_customer_trx_id := p_event.GetValueForParameter('CUSTOMER_TRX_ID');
2101 l_org_id := p_event.GetValueForParameter('ORG_ID');
2102 l_user_id := p_event.GetValueForParameter('USER_ID');
2103 l_resp_id := p_event.GetValueForParameter('RESP_ID');
2104 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
2105
2106 IF (PG_DEBUG = 'Y') THEN
2107
2108 arp_standard.debug ('l_customer_trx_id ='||l_customer_trx_id);
2109 arp_standard.debug ('l_org_id ='||l_org_id);
2110 arp_standard.debug ('l_user_id ='||l_user_id);
2111 arp_standard.debug ('l_resp_id ='||l_resp_id);
2115 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
2112 arp_standard.debug ('l_application_id ='||l_application_id);
2113 END IF;
2114
2116
2117 mo_global.init('AR');
2118 mo_global.set_policy_context('M',l_org_id);
2119
2120 --------------------------------------------------------------------
2121 l_debug_info := 'fetching customer_id,customer_site_use_id and trx number';
2122 --------------------------------------------------------------------
2123
2124 select aps.customer_id,sites.CUST_ACCT_SITE_ID,aps.trx_number
2125 into l_customer_id,l_customer_acct_site_id,l_trx_number
2126 from ar_payment_schedules_all aps,HZ_CUST_SITE_USES sites
2127 where aps.customer_trx_id = l_customer_trx_id
2128 and aps.org_id = l_org_id
2129 and sites.site_use_id = aps.customer_site_use_id;
2130
2131 select hp.party_name,hca.account_number
2132 into l_customer_acct_name,l_customer_acct_number
2133 from hz_parties hp,hz_cust_accounts hca
2134 where hp.party_id = hca.party_id
2135 and hca.cust_account_id = l_customer_id;
2136
2137 IF (PG_DEBUG = 'Y') THEN
2138 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_customer_id || '+');
2139 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_customer_acct_site_id || '+');
2140 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_trx_number || '+');
2141 END IF;
2142
2143 --------------------------------------------------------------------
2144 l_debug_info := 'creating workflow process';
2145 --------------------------------------------------------------------
2146
2147 WF_ENGINE.CREATEPROCESS(l_itemtype,
2148 l_customer_trx_id,
2149 'ARI_INVOICE_NTF_PROCESS');
2150
2151 ----------------------------------------------------------------------------
2152 l_debug_info := 'Set parameters expected by ARINVNTF Workflow';
2153 ----------------------------------------------------------------------------
2154
2155 ----------------------------------------------------------------------------
2156 l_debug_info := 'Set ARI_CUSTOMER_TRX_ID parameter';
2157 ----------------------------------------------------------------------------
2158 WF_ENGINE.SetItemAttrText(l_itemtype,
2159 l_customer_trx_id,
2160 'ARI_CUSTOMER_TRX_ID',
2161 l_customer_trx_id);
2162
2163 ----------------------------------------------------------------------------
2164 l_debug_info := 'Set ARI_TRX_NUM parameter';
2165 ----------------------------------------------------------------------------
2166
2167 WF_ENGINE.SetItemAttrText(l_itemtype,
2168 l_customer_trx_id,
2169 'ARI_TRX_NUMBER',
2170 l_trx_number);
2171
2172 ----------------------------------------------------------------------------
2173 l_debug_info := 'Set ARI_CUST_ACCT_NUM parameter';
2174 ----------------------------------------------------------------------------
2175
2176 WF_ENGINE.SetItemAttrText(l_itemtype,
2177 l_customer_trx_id,
2178 'ARI_CUST_ACCT_NUM',
2179 l_customer_acct_number);
2180
2181 ----------------------------------------------------------------------------
2182 l_debug_info := 'Set ARI_CUST_ACCT_ID parameter';
2183 ----------------------------------------------------------------------------
2184
2185 WF_ENGINE.SetItemAttrText(l_itemtype,
2186 l_customer_trx_id,
2187 'ARI_CUST_ACCT_ID',
2188 l_customer_id);
2189
2190 ----------------------------------------------------------------------------
2191 l_debug_info := 'Set ARI_CUST_ACCT_NAME parameter';
2192 ----------------------------------------------------------------------------
2193
2194 WF_ENGINE.SetItemAttrText(l_itemtype,
2195 l_customer_trx_id,
2196 'ARI_CUST_ACCT_NAME',
2197 l_customer_acct_name);
2198
2199 ----------------------------------------------------------------------------
2200 l_debug_info := 'Set ARI_CUST_ACCT_SITE_NUM parameter';
2201 ----------------------------------------------------------------------------
2202
2203 WF_ENGINE.SetItemAttrText(l_itemtype,
2204 l_customer_trx_id,
2205 'ARI_CUST_ACCT_SITE_ID',
2206 l_customer_acct_site_id);
2207
2208 ----------------------------------------------------------------------------
2209 l_debug_info := 'Starting Workflow..';
2210 ----------------------------------------------------------------------------
2211
2212 IF (PG_DEBUG = 'Y') THEN
2213 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS') || '+');
2214 END IF;
2215
2216 WF_ENGINE.StartProcess(l_itemtype,l_customer_trx_id);
2217
2218
2219 RETURN 'SUCCESS';
2220
2221 EXCEPTION
2222 WHEN OTHERS THEN
2223 IF (PG_DEBUG = 'Y') THEN
2224 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2225 arp_standard.debug('ERROR =>'|| SQLERRM);
2226 arp_standard.debug('Debug Info : '||l_debug_info);
2227 END IF;
2228 RETURN 'ERROR';
2229
2230 END invoke_invoice_email_notwf;
2231
2232 FUNCTION get_contact_emails_adhoc_list( p_customer_id IN VARCHAR2,
2233 p_customer_acct_site_id IN VARCHAR2 ) RETURN VARCHAR2 AS
2234
2235 l_adhoc_user_name VARCHAR2(200);
2236 l_adhoc_user_display_name VARCHAR2(200);
2237
2238 l_email_address hz_contact_points.email_Address%TYPE;
2239 l_user_email_addr_list email_addr_type;
2243
2240 l_adhoc_users_list varchar2 (32760) := null;
2241 l_contact_id NUMBER := NULL;
2242 i PLS_INTEGER := 1 ;
2244 l_procedure_name VARCHAR2(30) := '.get_contact_emails';
2245 l_debug_info VARCHAR2(500);
2246
2247 CURSOR contact_cur(p_customer_id IN NUMBER ,
2248 p_customer_acct_site_id IN NUMBER ) IS
2249 SELECT hcar.CUST_ACCOUNT_ROLE_ID as contact_id
2250 FROM HZ_CUST_ACCOUNT_ROLES hcar, HZ_PARTIES hpsub, HZ_PARTIES hprel,
2251 HZ_ORG_CONTACTS hoc, HZ_RELATIONSHIPS hr, HZ_PARTY_SITES hps, FND_TERRITORIES_VL ftv,
2252 fnd_lookup_values_vl lookups,hz_role_responsibility hrr
2253 WHERE hrr.responsibility_type = 'SELF_SERVICE_USER'
2254 and hrr.cust_account_role_id = hcar.cust_account_role_id
2255 and hcar.CUST_ACCOUNT_ID = p_customer_id
2256 AND hcar.ROLE_TYPE = 'CONTACT'
2257 AND hcar.PARTY_ID = hr.PARTY_ID
2258 AND hr.PARTY_ID = hprel.PARTY_ID
2259 AND hr.SUBJECT_ID = hpsub.PARTY_ID
2260 AND hoc.PARTY_RELATIONSHIP_ID = hr.RELATIONSHIP_ID
2261 AND hr.DIRECTIONAL_FLAG = 'F'
2262 AND hps.PARTY_ID(+) = hprel.PARTY_ID
2263 AND nvl(hps.IDENTIFYING_ADDRESS_FLAG, 'Y') = 'Y'
2264 AND nvl(hps.STATUS, 'A') = 'A'
2265 AND hprel.COUNTRY = ftv.TERRITORY_CODE(+)
2266 AND nvl(hcar.CUST_ACCT_SITE_ID, 1) = nvl(p_customer_acct_site_id, 1)
2267 AND lookups.LOOKUP_TYPE (+)='RESPONSIBILITY'
2268 AND lookups.LOOKUP_CODE(+)=hoc.JOB_TITLE_CODE
2269 and hcar.status='A';
2270
2271 CURSOR email_addr_cur (l_contact_id IN NUMBER DEFAULT NULL) IS
2272 SELECT cont_point.email_Address
2273 FROM hz_cust_account_roles acct_role,
2274 hz_contact_points cont_point
2275 WHERE acct_role.cust_account_role_id =l_contact_id
2276 AND acct_role.party_id = cont_point.owner_table_id
2277 AND cont_point.owner_table_name = 'HZ_PARTIES'
2278 AND cont_point.status = 'A'
2279 AND cont_point.email_Address is not null;
2280
2281 contact_rec contact_cur%ROWTYPE;
2282 email_addr_rec email_addr_cur%ROWTYPE;
2283
2284 BEGIN
2285
2286 ----------------------------------------------------------------------------------------
2287 l_debug_info := 'fetches all email addres at account level for all self sevice users';
2288 -----------------------------------------------------------------------------------------
2289 IF (PG_DEBUG = 'Y') THEN
2290 arp_standard.debug(l_debug_info);
2291 END IF;
2292
2293 FOR contact_rec in contact_cur(p_customer_id,NULL) LOOP
2294
2295 l_contact_id := contact_rec.contact_id;
2296
2297 FOR email_addr_rec in email_addr_cur( l_contact_id ) LOOP
2298 l_email_address := email_addr_rec.email_Address;
2299 if (l_email_address is not null) then
2300 l_adhoc_user_name := remove_existing_user_role(p_email_address => l_email_address);
2301 if (l_adhoc_user_name is null ) then
2302 l_adhoc_user_name := SUBSTRB(l_email_address,1,INSTRB(l_email_address,'@')-1) || to_char(sysdate, 'YYYYMMDD_HH24MISSSS');
2303 l_adhoc_user_display_name := l_adhoc_user_name;
2304
2305 ------------------------------------------------------------
2306 l_debug_info := 'Create AdHoc Workflow User';
2307 ------------------------------------------------------------
2308
2309 WF_DIRECTORY.CreateAdHocUser(name => l_adhoc_user_name,
2310 display_name => l_adhoc_user_display_name,
2311 email_address => l_email_address);
2312 end if;
2313 end if;
2314
2315 l_user_email_addr_list(i) := l_adhoc_user_name;
2316
2317 ----------------------------------------------------------------------------------------
2318 l_debug_info := 'emails at account level';
2319 -----------------------------------------------------------------------------------------
2320 IF (PG_DEBUG = 'Y') THEN
2321 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'emails adhoc user list at Account Level ' || l_user_email_addr_list(i));
2322 END IF;
2323
2324 i := i+1;
2325
2326 END LOOP;
2327
2328 END LOOP;
2329
2330 ----------------------------------------------------------------------------------------
2331 l_debug_info := 'fetches all email addres at site level for all self sevice users';
2332 -----------------------------------------------------------------------------------------
2333
2334 FOR contact_rec in contact_cur(p_customer_id,p_customer_acct_site_id) LOOP
2335
2336 l_contact_id := contact_rec.contact_id;
2337
2338 FOR email_addr_rec in email_addr_cur( l_contact_id ) LOOP
2339 l_email_address := email_addr_rec.email_Address;
2340 if (l_email_address is not null) then
2341 l_adhoc_user_name := remove_existing_user_role(p_email_address => l_email_address);
2342 if (l_adhoc_user_name is null ) then
2343 l_adhoc_user_name := SUBSTRB(l_email_address,1,INSTRB(l_email_address,'@')-1) || to_char(sysdate, 'YYYYMMDD_HH24MISS');
2344 l_adhoc_user_display_name := l_adhoc_user_name;
2345
2346 ------------------------------------------------------------
2347 l_debug_info := 'Create AdHoc Workflow User';
2348 ------------------------------------------------------------
2349
2350 WF_DIRECTORY.CreateAdHocUser(name => l_adhoc_user_name,
2351 display_name => l_adhoc_user_display_name,
2355
2352 email_address => l_email_address);
2353 end if;
2354 end if;
2356 l_user_email_addr_list(i) := l_adhoc_user_name;
2357
2358 ----------------------------------------------------------------------------------------
2359 l_debug_info := 'emails at account level';
2360 -----------------------------------------------------------------------------------------
2361 IF (PG_DEBUG = 'Y') THEN
2362 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'emails adhoc user list at Site Level ' || l_user_email_addr_list(i));
2363 END IF;
2364
2365 i := i+1;
2366
2367 END LOOP;
2368 END LOOP;
2369
2370
2371 l_adhoc_users_list := remove_duplicate_user_names( l_user_email_list => l_user_email_addr_list);
2372
2373 IF (PG_DEBUG = 'Y') THEN
2374 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'emails adhoc user list after Removing Duplicates ' || l_adhoc_users_list);
2375 END IF;
2376
2377 RETURN l_adhoc_users_list;
2378
2379 EXCEPTION
2380 WHEN OTHERS THEN
2381 IF (PG_DEBUG = 'Y') THEN
2382 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2383 arp_standard.debug('ERROR =>'|| SQLERRM);
2384 arp_standard.debug('Debug Info : '||l_debug_info);
2385 END IF;
2386 RETURN 'ERROR';
2387
2388 END get_contact_emails_adhoc_list;
2389
2390 FUNCTION remove_duplicate_user_names(l_user_email_list IN email_addr_type ) RETURN VARCHAR2 AS
2391
2392 l_adhoc_users_list varchar2 (32760) := null;
2393 v_email_list email_addr_type;
2394
2395 l_procedure_name VARCHAR2(30) := '.remove_duplicate_user_names';
2396 l_debug_info VARCHAR2(500);
2397
2398 Begin
2399
2400 v_email_list := l_user_email_list;
2401 ----------------------------------------------------------------------------------------
2402 l_debug_info := 'Removes All the Dulicate Uses in the List';
2403 -----------------------------------------------------------------------------------------
2404 IF (PG_DEBUG = 'Y') THEN
2405 arp_standard.debug(l_debug_info);
2406 END IF;
2407
2408
2409 for i in 1..v_email_list.count loop
2410 for j in i+1..v_email_list.count loop
2411 if ( v_email_list(j) = v_email_list(i) ) then
2412 v_email_list(j) := 'REMOVED';
2413
2414 end if;
2415 end loop;
2416 end loop;
2417
2418 for k in 1..v_email_list.count loop
2419 if (v_email_list(k) <> 'REMOVED' and l_adhoc_users_list is null) then
2420 l_adhoc_users_list := v_email_list(k);
2421
2422 elsif (v_email_list(k) <> 'REMOVED' and l_adhoc_users_list is not null) then
2423 l_adhoc_users_list := l_adhoc_users_list || ',' || v_email_list(k);
2424
2425 end if;
2426 end loop;
2427
2428 return l_adhoc_users_list;
2429
2430 end remove_duplicate_user_names;
2431
2432 FUNCTION remove_existing_user_role( p_email_address IN VARCHAR2 ) RETURN VARCHAR2 AS
2433
2434 l_adhoc_user_name varchar2(1000) default null;
2435 l_adhoc_role_name varchar2(1000) default null;
2436
2437 l_procedure_name VARCHAR2(30) := '.remove_existing_user_role';
2438 l_debug_info VARCHAR2(500);
2439
2440 CURSOR user_name_by_email_addr (p_email_address IN varchar2 DEFAULT NULL) IS
2441 select name from wf_local_roles
2442 where upper(EMAIL_ADDRESS) = UPPER(p_email_address)
2443 and ORIG_SYSTEM = 'WF_LOCAL_USERS'
2444 and STATUS ='ACTIVE'
2445 and USER_FLAG = 'Y'
2446 order by last_update_date desc;
2447
2448 CURSOR user_role_by_email_addr (l_adhoc_user_name IN varchar2 DEFAULT NULL) IS
2449 select role_name from wf_local_user_roles
2450 where user_name = l_adhoc_user_name
2451 and user_orig_system ='WF_LOCAL_USERS'
2452 and role_orig_system ='WF_LOCAL_ROLES';
2453
2454 user_name_by_email_addr_rec user_name_by_email_addr%ROWTYPE;
2455 user_role_by_email_addr_rec user_role_by_email_addr%ROWTYPE;
2456
2457 BEGIN
2458
2459 ----------------------------------------------------------------------------------------
2460 l_debug_info := 'Checks whether the user is already created are not if created the value will be re-used';
2461 -----------------------------------------------------------------------------------------
2462 IF (PG_DEBUG = 'Y') THEN
2463 arp_standard.debug(l_debug_info);
2464 END IF;
2465
2466 -- Checks whether the user is already created are not.. if created the loop will exit and re-use the existing value
2467 FOR user_name_by_email_addr_rec in user_name_by_email_addr(p_email_address) LOOP
2468
2469 l_adhoc_user_name := user_name_by_email_addr_rec.name;
2470 IF (PG_DEBUG = 'Y') THEN
2471 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'Existing Adhoc user ' || l_adhoc_user_name);
2472 END IF;
2473 exit when user_name_by_email_addr%rowcount>0;
2474 end loop;
2475
2476 -- removes the user from the role to which user has already assinged.
2477
2478 FOR user_role_by_email_addr_rec in user_role_by_email_addr(l_adhoc_user_name) LOOP
2479
2480 l_adhoc_role_name := user_role_by_email_addr_rec.role_name;
2481
2482 WF_DIRECTORY.RemoveUsersFromAdHocRole (role_name =>l_adhoc_role_name,
2483 role_users => l_adhoc_user_name);
2484 IF (PG_DEBUG = 'Y') THEN
2485 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'removed the user '|| l_adhoc_user_name ||' from the role ' || l_adhoc_role_name);
2486 END IF;
2487 end loop;
2488
2489
2490 return l_adhoc_user_name;
2491
2492 END remove_existing_user_role;
2493
2497 funcmode in varchar2,
2494 PROCEDURE det_if_send_email( l_itemtype in varchar2,
2495 l_itemkey in varchar2,
2496 actid in number,
2498 rslt out NOCOPY varchar2 ) IS
2499
2500 l_adhoc_user_name VARCHAR2(200);
2501 l_adhoc_user_display_name VARCHAR2(200);
2502 l_role_prefix VARCHAR2(14) := 'ARINVNTF_';
2503 l_role_exists NUMBER;
2504
2505
2506 l_customer_trx_id NUMBER ;
2507 l_users_list varchar2(3000) := null;
2508 p_customer_id NUMBER := NULL;
2509 p_customer_acct_site_id NUMBER := NULL;
2510
2511 l_trx_number VARCHAR2(30) :=null;
2512 l_trx_type VARCHAR2(20) :=null;
2513 l_trx_curr_code VARCHAR2(15) :=null;
2514 l_trx_term_name VARCHAR2(15) := null;
2515 l_trx_term_desc VARCHAR2(240) :=null;
2516 l_trx_due_date DATE;
2517 l_trx_amt_due NUMBER;
2518
2519 l_procedure_name VARCHAR2(30) := '.det_if_send_email';
2520 l_result_code VARCHAR2(25);
2521 l_debug_info VARCHAR2(500);
2522
2523 BEGIN
2524
2525 -----------------------------------------------------------
2526 l_debug_info := 'Retrieve ARI_CUSTOMER_TRX_ID Item Attribute';
2527 -----------------------------------------------------------
2528
2529 l_customer_trx_id:= wf_engine.GetItemAttrText(itemtype => l_itemtype,
2530 itemkey => l_itemkey,
2531 aname => 'ARI_CUSTOMER_TRX_ID');
2532 IF (PG_DEBUG = 'Y') THEN
2533 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'ARI_CUSTOMER_TRX_ID ::' || l_customer_trx_id );
2534 END IF;
2535
2536 -----------------------------------------------------------
2537 l_debug_info := 'Retrieve ARI_TRX_NUM Item Attribute';
2538 -----------------------------------------------------------
2539
2540 l_trx_number:= wf_engine.GetItemAttrText(itemtype => l_itemtype,
2541 itemkey => l_itemkey,
2542 aname => 'ARI_TRX_NUMBER');
2543 IF (PG_DEBUG = 'Y') THEN
2544 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'ARI_TRX_NUMBER ::' || l_trx_number);
2545 END IF;
2546
2547 -----------------------------------------------------------
2548 l_debug_info := 'Retrieve ARI_CUST_ACCT_NUM Item Attribute';
2549 -----------------------------------------------------------
2550
2551 p_customer_id:= wf_engine.GetItemAttrText(itemtype => l_itemtype,
2552 itemkey => l_itemkey,
2553 aname => 'ARI_CUST_ACCT_ID');
2554 IF (PG_DEBUG = 'Y') THEN
2555 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'ARI_CUST_ACCT_ID ::' || p_customer_id );
2556 END IF;
2557
2558 ---------------------------------------------------------------
2559 l_debug_info := 'Retrieve ARI_CUST_ACCT_SITE_ID Item Attribute';
2560 ----------------------------------------------------------------
2561
2562 p_customer_acct_site_id := wf_engine.GetItemAttrText(itemtype => l_itemtype,
2563 itemkey => l_itemkey,
2564 aname => 'ARI_CUST_ACCT_SITE_ID');
2565
2566 IF (PG_DEBUG = 'Y') THEN
2567 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'ARI_CUST_ACCT_SITE_ID ::' || p_customer_acct_site_id );
2568 END IF;
2569
2570 --------------------------------------------------------------------------
2571 l_debug_info := 'selecting all the attributes required to send in notification';
2572 ---------------------------------------------------------------------------
2573
2574 select aps.trx_number,aps.amount_due_original,aps.invoice_currency_code,aps.due_date,t.name,t.description,aps.class
2575 into l_trx_number,l_trx_amt_due,l_trx_curr_code,l_trx_due_date,l_trx_term_name,l_trx_term_desc,l_trx_type
2576 from ar_payment_schedules_all aps,ra_terms t
2577 where aps.trx_number = l_trx_number
2578 and aps.customer_id = p_customer_id
2579 and aps.term_id = t.term_id(+);
2580
2581 IF (PG_DEBUG = 'Y') THEN
2582 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_number ::'|| l_trx_number );
2583 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_amt_due ::'|| l_trx_amt_due );
2584 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_curr_code ::'||l_trx_curr_code );
2585 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_due_date ::'||l_trx_due_date );
2586 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_term_name ::'||l_trx_term_name );
2587 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_term_desc ::'||l_trx_term_desc );
2588 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_trx_type ::'||l_trx_type );
2589 END IF;
2590
2594
2591 ------------------------------------------------------------------------------------------
2592 l_debug_info := 'check if wether to send invoice notification or credit memo notification';
2593 -------------------------------------------------------------------------------------------
2595 if ( l_trx_type = 'INV') then
2596 l_result_code := 'INVOICE';
2597 elsif ( l_trx_type = 'CM') then
2598 l_result_code := 'CREDIT_MEMO';
2599 else
2600 l_result_code := 'OTHER';
2601 end if;
2602
2603 IF (PG_DEBUG = 'Y') THEN
2604 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_result_code ::'||l_result_code );
2605 END IF;
2606
2607 --------------------------------------------------------------------------
2608 l_debug_info := 'fetching all contacts emails adhoc users list to send notification ';
2609 ---------------------------------------------------------------------------
2610
2611 l_users_list := get_contact_emails_adhoc_list(p_customer_id,p_customer_acct_site_id);
2612
2613 IF (PG_DEBUG = 'Y') THEN
2614 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'l_users_list ::' || l_users_list );
2615 END IF;
2616
2617 ------------------------------------------------------------------------------------------
2618 l_debug_info := 'check if emails exits , if yes creating adhoc user , if no invoice complete ';
2619 -------------------------------------------------------------------------------------------
2620
2621 if ( l_users_list is null ) then
2622 rslt := 'COMPLETE:' || 'OTHER';
2623 else
2624
2625 l_adhoc_user_name := l_role_prefix || l_customer_trx_id;
2626 l_adhoc_user_display_name := l_adhoc_user_name;
2627
2628 IF (PG_DEBUG = 'Y') THEN
2629 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'adhoc username :: ' || l_adhoc_user_name );
2630 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'adhoc display name :: ' || l_adhoc_user_display_name);
2631 END IF;
2632
2633 -------------------------------------------------------
2634 l_debug_info := 'if no role exits ,creating a new role';
2638 notification_preference => 'MAILHTM2');
2635 --------------------------------------------------------
2636 WF_DIRECTORY.CreateAdHocRole(role_name => l_adhoc_user_name,
2637 role_display_name => l_adhoc_user_display_name,
2639
2640 IF (PG_DEBUG = 'Y') THEN
2641 arp_standard.debug(G_PKG_NAME || l_procedure_name || l_adhoc_user_name || 'Created Role ' );
2642 END IF;
2643
2644 wf_directory.AddUsersToAdhocRole(role_name => l_adhoc_user_name,
2645 role_users => l_users_list);
2646
2647 end if;
2648
2649 IF (PG_DEBUG = 'Y') THEN
2650 arp_standard.debug(G_PKG_NAME || l_procedure_name || 'Assigned Users To The Role' || l_adhoc_user_name );
2651 END IF;
2652
2653 --------------------------------------------------------------
2654 l_debug_info := 'Set AR_NOTIFY_ROLES Item Attribute';
2655 --------------------------------------------------------------
2656
2657 WF_ENGINE.SetItemAttrText(l_itemtype,
2658 l_customer_trx_id,
2659 'ARI_NOTIFY_ROLES',
2660 l_adhoc_user_name);
2661
2662 IF (PG_DEBUG = 'Y') THEN
2663 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_NOTIFY_ROLES set to :: ' ||l_adhoc_user_name);
2664 END IF;
2665 --------------------------------------------------------------
2666 l_debug_info := 'Set ARI_TRX_NUM Item Attribute';
2667 --------------------------------------------------------------
2668
2669 WF_ENGINE.SetItemAttrText(l_itemtype,
2670 l_customer_trx_id,
2671 'ARI_TRX_NUMBER',
2672 l_trx_number);
2673
2674 IF (PG_DEBUG = 'Y') THEN
2675 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_TRX_NUMBER set to :: ' ||l_trx_number);
2676 END IF;
2677
2678 --------------------------------------------------------------
2679 l_debug_info := 'Set ARI_TRX_AMT_DUE Item Attribute';
2680 --------------------------------------------------------------
2681
2682 WF_ENGINE.SetItemAttrText(l_itemtype,
2683 l_customer_trx_id,
2684 'ARI_TRX_AMT_DUE',
2685 l_trx_amt_due);
2686
2687 IF (PG_DEBUG = 'Y') THEN
2688 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_TRX_AMT_DUE set to :: ' ||l_trx_amt_due);
2689 END IF;
2690
2691 --------------------------------------------------------------
2692 l_debug_info := 'Set ARI_INV_CUR_CODE Item Attribute';
2693 --------------------------------------------------------------
2694
2695 WF_ENGINE.SetItemAttrText(l_itemtype,
2696 l_customer_trx_id,
2697 'ARI_TRX_CUR_CODE',
2698 l_trx_curr_code);
2699
2700 IF (PG_DEBUG = 'Y') THEN
2701 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_TRX_CUR_CODE set to :: ' ||l_trx_curr_code);
2702 END IF;
2703
2704 --------------------------------------------------------------
2705 l_debug_info := 'Set ARI_TRX_DUE_DATE Item Attribute';
2706 --------------------------------------------------------------
2707
2708 WF_ENGINE.SetItemAttrText(l_itemtype,
2709 l_customer_trx_id,
2710 'ARI_TRX_DUE_DATE',
2711 l_trx_due_date);
2712
2713 IF (PG_DEBUG = 'Y') THEN
2714 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_TRX_DUE_DATE set to :: ' ||l_trx_due_date);
2715 END IF;
2716
2717 --------------------------------------------------------------
2718 l_debug_info := 'Set ARI_TRX_PAY_TERM Item Attribute';
2719 --------------------------------------------------------------
2720
2721 WF_ENGINE.SetItemAttrText(l_itemtype,
2722 l_customer_trx_id,
2723 'ARI_TRX_PAY_TERM',
2724 l_trx_term_name);
2725
2726 IF (PG_DEBUG = 'Y') THEN
2727 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_TRX_PAY_TERM set to :: ' ||l_trx_term_name);
2728 END IF;
2729
2730 --------------------------------------------------------------
2731 l_debug_info := 'Set ARI_TRX_PAY_TERM_DESC Item Attribute';
2732 --------------------------------------------------------------
2733
2734 WF_ENGINE.SetItemAttrText(l_itemtype,
2735 l_customer_trx_id,
2736 'ARI_TRX_PAY_TERM_DESC',
2737 l_trx_term_desc);
2738
2739 IF (PG_DEBUG = 'Y') THEN
2740 arp_standard.debug(G_PKG_NAME || l_procedure_name || ' Attribute ARI_TRX_PAY_TERM_DESC set to :: ' || l_trx_term_desc);
2741 END IF;
2742
2743 rslt := 'COMPLETE:' || l_result_code;
2744
2745 EXCEPTION
2746 WHEN OTHERS THEN
2747 rslt := 'COMPLETE:' || 'N';
2748 wf_core.context('ARI_UTILITIES','DET_IF_SEND_EMAIL',l_itemtype,
2749 l_itemkey,to_char(actid),funcmode);
2750 IF (PG_DEBUG = 'Y') THEN
2751 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2752 arp_standard.debug('ERROR =>'|| SQLERRM);
2753 arp_standard.debug('Debug Info : '||l_debug_info);
2754 END IF;
2755 raise;
2756
2757 end det_if_send_email;
2758
2759 PROCEDURE cancel_dispute(p_dispute_id IN NUMBER,
2760 p_cancel_comments IN VARCHAR2,
2761 p_return_status OUT NOCOPY VARCHAR2
2762 ) IS
2766 FROM wf_items
2763
2764 CURSOR c_item_type(l_item_key NUMBER) IS
2765 SELECT item_type
2767 WHERE item_key = l_item_key
2768 AND item_type IN('ARCMREQ','ARAMECM');
2769
2770
2771 CURSOR ps_cur(p_customer_trx_id NUMBER) IS
2772 SELECT payment_schedule_id,
2773 due_date,
2774 amount_in_dispute,
2775 dispute_date
2776 FROM ar_payment_schedules ps
2777 WHERE ps.customer_trx_id = p_customer_trx_id;
2778
2779 cursor get_partyid(p_cust_acct_id number) is
2780 select party_id
2781 from hz_cust_accounts
2782 where cust_account_id = p_cust_acct_id;
2783
2784 Cursor Get_billto(p_cust_trx_id number) Is
2785 select bill_to_site_use_id
2786 from ra_customer_trx
2787 where customer_trx_id = p_cust_trx_id;
2788
2789 Cursor Get_paymentid(p_cust_trx_id number) Is
2790 select customer_id,payment_schedule_id
2791 from ar_payment_schedules
2792 where customer_trx_id = p_cust_trx_id;
2793
2794 l_item_type VARCHAR2(100);
2795 l_customer_trx_id NUMBER;
2796 l_status VARCHAR2(8);
2797 l_result VARCHAR2(100);
2798 l_last_updated_by NUMBER;
2799 l_last_update_login NUMBER;
2800 l_last_update_date DATE;
2801 l_creation_date DATE;
2802 l_created_by NUMBER;
2803 l_document_id NUMBER;
2804 l_note_id NUMBER;
2805 l_note_text ar_notes.text%TYPE;
2806 l_notes wf_item_attribute_values.text_value%TYPE;
2807 l_cust_account_id NUMBER;
2808 l_payment_schedule_id NUMBER;
2809 l_party_id NUMBER;
2810 l_customer_site_use_id number;
2811 new_dispute_date DATE;
2812 new_dispute_amt NUMBER;
2813 remove_from_dispute_amt NUMBER;
2814 i NUMBER;
2815 l_default_note_type varchar2(240) := FND_PROFILE.VALUE('AST_NOTES_DEFAULT_TYPE');
2816 l_jtf_note_contexts_table jtf_notes_pub.jtf_note_contexts_tbl_type;
2817 l_context_tab CONTEXTS_TBL_TYPE;
2818 l_return_status VARCHAR2(1);
2819 l_msg_count NUMBER;
2820 l_msg_data VARCHAR2(32767);
2821 l_procedure_name VARCHAR2(50);
2822 l_debug_info VARCHAR2(200);
2823
2824 BEGIN
2825
2826 l_procedure_name := '.cancel_dispute';
2827 l_debug_info := 'Cancel Credit Memo Request';
2828
2829 SAVEPOINT CANCEL_DISPUTE;
2830
2831 p_return_status := FND_API.G_RET_STS_SUCCESS;
2832 OPEN c_item_type(p_dispute_id);
2833 FETCH c_item_type INTO l_item_type;
2834 CLOSE c_item_type;
2835
2836 IF l_item_type IS NOT NULL THEN
2837 l_customer_trx_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'CUSTOMER_TRX_ID');
2838 END IF;
2839
2840 SELECT total_amount * -1
2841 INTO remove_from_dispute_amt
2842 FROM ra_cm_requests
2843 WHERE request_id = p_dispute_id;
2844
2845 FOR ps_rec IN ps_cur(l_customer_trx_id)
2846 LOOP
2847 new_dispute_amt := ps_rec.amount_in_dispute -remove_from_dispute_amt;
2848
2849 IF new_dispute_amt = 0 THEN
2850 new_dispute_date := NULL;
2851 ELSE
2852 new_dispute_date := ps_rec.dispute_date;
2853 END IF;
2854
2855 arp_process_cutil.update_ps(p_ps_id => ps_rec.payment_schedule_id,
2856 p_due_date => ps_rec.due_date,
2857 p_amount_in_dispute => new_dispute_amt,
2858 p_dispute_date => new_dispute_date,
2859 p_update_dff => 'N',
2860 p_attribute_category => NULL,
2861 p_attribute1 => NULL,
2862 p_attribute2 => NULL,
2863 p_attribute3 => NULL,
2864 p_attribute4 => NULL,
2865 p_attribute5 => NULL,
2866 p_attribute6 => NULL,
2867 p_attribute7 => NULL,
2868 p_attribute8 => NULL,
2869 p_attribute9 => NULL,
2870 p_attribute10 => NULL,
2871 p_attribute11 => NULL,
2872 p_attribute12 => NULL,
2873 p_attribute13 => NULL,
2874 p_attribute14 => NULL,
2875 p_attribute15 => NULL);
2876
2877 END LOOP;
2878
2879 wf_engine.SetItemAttrText(l_item_type, p_dispute_id, 'NOTES', p_cancel_comments);
2880
2881 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_dispute_id, status => l_status, result => l_result);
2882
2883 IF l_status <> wf_engine.eng_completed THEN
2884 wf_engine.abortprocess(itemtype => l_item_type, itemkey => p_dispute_id);
2885 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_dispute_id, status => l_status, result => l_result);
2886 END IF;
2887
2888 l_last_updated_by := arp_global.user_id;
2889 l_last_update_login := arp_global.last_update_login;
2890 l_document_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'WORKFLOW_DOCUMENT_ID');
2891 l_customer_trx_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'CUSTOMER_TRX_ID');
2892
2893
2894 if l_customer_trx_id is null then
2895 SELECT customer_trx_id
2896 INTO l_customer_trx_id
2897 FROM ra_cm_requests
2898 WHERE request_id = l_document_id;
2899 end if;
2900
2901 l_notes := wf_engine.getitemattrtext(l_item_type, p_dispute_id, 'NOTES');
2902 fnd_message.set_name('AR', 'AR_WF_REJECTED_RESPONSE');
2903 fnd_message.set_token('REQUEST_ID', to_char(p_dispute_id));
2904 fnd_message.set_token('APPROVER', fnd_global.user_id);
2905 l_note_text := fnd_message.GET;
2906
2907 IF l_notes IS NOT NULL THEN
2908 l_note_text := substrb(l_note_text || ' "' || l_notes || '"', 1, 2000);
2909 END IF;
2910
2911 arp_notes_pkg.insert_cover(
2912 p_note_type => 'MAINTAIN',
2913 p_text => l_note_text,
2914 p_customer_call_id => null,
2918 p_note_id => l_note_id,
2915 p_customer_call_topic_id => null,
2916 p_call_action_id => NULL,
2917 p_customer_trx_id => l_customer_trx_id,
2919 p_last_updated_by => l_last_updated_by,
2920 p_last_update_date => l_last_update_date,
2921 p_last_update_login => l_last_update_login,
2922 p_created_by => l_created_by,
2923 p_creation_date => l_creation_date);
2924
2925 EXCEPTION
2926 WHEN OTHERS THEN
2927 IF (PG_DEBUG = 'Y') THEN
2928 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2929 arp_standard.debug('ERROR =>'|| SQLERRM);
2930 arp_standard.debug('Debug Info : '||l_debug_info);
2931 END IF;
2932 ROLLBACK TO CANCEL_DISPUTE;
2933 p_return_status := FND_API.G_RET_STS_ERROR;
2934 END cancel_dispute;
2935
2936
2937
2938 /*===========================================================================+
2939 | PROCEDURE get_link_filter |
2940 | |
2941 | DESCRIPTION |
2942 | This proc returns if the link for a particular transaction in activities needs |
2943 | to be displayed or not. |
2944 | SCOPE - PUBLIC |
2945 | |
2946 | |
2947 | ARGUMENTS : IN: |
2948 | |
2949 | RETURNS : OUT: |
2950 | |
2951 | MODIFICATION HISTORY |
2952 | 17-Apr-2012 parln Created |
2953 | |
2954 +===========================================================================*/
2955
2956 FUNCTION get_link_filter(p_txn_no IN VARCHAR2,
2957 p_txn_type IN VARCHAR2,
2958 p_cust_trx_id IN NUMBER,
2959 p_payment_schedule_id IN NUMBER)
2960 RETURN VARCHAR2 IS
2961 l_txn_difference NUMBER;
2962 l_profile_limit_days NUMBER;
2963 l_print_flag VARCHAR2(100);
2964 l_print_profile VARCHAR2(100);
2965 l_cust_trx_id NUMBER;
2966 p_result VARCHAR(2);
2967 BEGIN
2968 p_result := 'N';
2969 if(p_cust_trx_id is null) then
2970 select customer_trx_id into l_cust_trx_id
2971 from ar_payment_schedules_all
2972 where payment_schedule_id = p_payment_schedule_id
2973 and trx_number = p_txn_no;
2974 else
2975 l_cust_trx_id := p_cust_trx_id;
2976 end if;
2977 if(p_txn_type<>'PMT') THEN
2978 Select (sysdate - ps.TRX_DATE), ra.Printing_option into l_txn_difference, l_print_flag
2979 from ar_payment_schedules_all ps, ra_customer_trx_all ra
2980 where ps.CUSTOMER_TRX_ID= ra.CUSTOMER_TRX_ID
2981 and ps.trx_number=p_txn_no
2982 and ps.CUSTOMER_TRX_ID = l_cust_trx_id;
2983 else
2984 Select (sysdate - TRX_DATE) into l_txn_difference from ar_payment_schedules_all
2985 where trx_number=p_txn_no
2986 and PAYMENT_SCHEDULE_ID = p_payment_schedule_id;
2987 end if;
2988 IF (PG_DEBUG = 'Y') THEN
2989 arp_standard.debug('In get_link_filter p_txn_no:'||p_txn_no||'p_txn_type:'||
2990 p_txn_type||'p_cust_trx_id:'||to_char(p_cust_trx_id)||
2991 'p_payment_schedule_id:'||to_char(p_payment_schedule_id));
2992 END IF;
2993 l_profile_limit_days := FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER');
2994 l_print_profile := FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX');
2995
2996
2997 IF (PG_DEBUG = 'Y') THEN
2998 arp_standard.debug('In get_link_filter l_print_flag:'||
2999 l_print_flag||'l_print_profile: '||l_print_profile);
3000 END IF;
3001 if(l_profile_limit_days is not null and l_txn_difference > l_profile_limit_days ) THEN
3002 p_result:= 'Y';
3003 END IF;
3004 if(l_print_profile='Y' AND l_print_flag='NOT') THEN
3005 p_result:= 'Y';
3006 END IF;
3007 return p_result;
3008 EXCEPTION
3009 WHEN OTHERS THEN
3010 p_result:= 'N';
3011 IF (PG_DEBUG = 'Y') THEN
3012 arp_standard.debug('Unexpected Exception in get_link_filter'||SQLERRM);
3013 END IF;
3014 return p_result;
3015 END get_link_filter;
3016
3017
3018 /*========================================================================
3019 | PUBLIC function get_pending_cmreq_amount
3020 |
3021 | DESCRIPTION
3022 | Function returns the pending credit memo request amount for the customer id passed.
3023 | If customer is not passed, then it will return the pending credit memo request amount
3024 | for all the customer in current session.
3025 |
3026 | PARAMETERS
3027 | p_customer_id IN NUMBER,
3028 | p_customer_site_use_id IN NUMBER,
3029 | p_session_id IN NUMBER,
3030 | p_currency_code IN VARCHAR2
3031 |
3032 | RETURNS
3033 | Pending Credit Memo Request amount
3034 |
3035 | MODIFICATION HISTORY
3036 | Date Author Description of Changes
3037 | 16-Nov-2011 melapaku Created
3038 | 10-May-2012 parln Bug 13848644 - FP:13455876:13117430: ACCOUNT SUMMARY PERF ISSUES
3039 *=======================================================================*/
3040
3041 FUNCTION get_pending_cmreq_amount(p_customer_id IN NUMBER,
3042 p_customer_site_use_id IN NUMBER,
3043 p_session_id IN NUMBER,
3044 p_currency_code IN VARCHAR2) RETURN NUMBER AS
3045
3046 pending_cmreq_amt NUMBER := 0;
3047 l_procedure_name VARCHAR2(30);
3048 BEGIN
3049
3050 l_procedure_name := '.get_pending_cmreq_amount';
3051
3052 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3053 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Begin+');
3054 end if;
3055
3056 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3057 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'p_customer_id '||p_customer_id);
3058 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'p_customer_site_use_id '||p_customer_site_use_id);
3059 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'p_session_id '||p_session_id);
3060 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'p_currency_code '||p_currency_code);
3061 end if;
3062
3063 if(p_customer_id IS NULL OR p_customer_id = -1) then
3064 SELECT /*+ LEADING(RaCmRequests)*/ nvl(sum ( total_amount ), 0) into pending_cmreq_amt
3065 FROM RA_CM_REQUESTS RaCmRequests, AR_PAYMENT_SCHEDULES ArPaymentSchedules, ar_irec_user_acct_sites_all AcctSites, ra_customer_trx ct
3066 WHERE (RaCmRequests.CUSTOMER_TRX_ID = ArPaymentSchedules.CUSTOMER_TRX_ID) AND
3067 nvl(ArPaymentSchedules.terms_sequence_number,1) = 1
3068 AND AcctSites.user_id=FND_GLOBAL.USER_ID()
3069 AND ArPaymentSchedules.customer_id = AcctSites.customer_id
3070 AND AcctSites.customer_site_use_id=ArPaymentSchedules.customer_site_use_id
3071 AND AcctSites.session_id=p_session_id
3072 AND ( ArPaymentSchedules.class = 'INV' OR ArPaymentSchedules.class = 'GUAR' OR ArPaymentSchedules.class = 'CB' OR
3073 ArPaymentSchedules.class = 'DM' OR ArPaymentSchedules.class = 'DEP' )
3074 AND( ArPaymentSchedules.invoice_currency_code = p_currency_code ) AND
3075 (RaCmRequests.status IN ('PENDING_APPROVAL','APPROVED_PEND_COMP'))
3076 AND ArPaymentSchedules.customer_trx_id = ct.customer_trx_id
3077 AND(TRUNC(ArPaymentSchedules.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ArPaymentSchedules.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
3078 AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option) ;
3079 else
3080 SELECT /*+ LEADING(RaCmRequests)*/ nvl(sum ( total_amount ), 0) into pending_cmreq_amt
3081 FROM RA_CM_REQUESTS RaCmRequests, AR_PAYMENT_SCHEDULES ArPaymentSchedules, ar_irec_user_acct_sites_all AcctSites, ra_customer_trx ct
3082 WHERE (RaCmRequests.CUSTOMER_TRX_ID = ArPaymentSchedules.CUSTOMER_TRX_ID) AND
3083 nvl(ArPaymentSchedules.terms_sequence_number,1) = 1
3084 AND AcctSites.user_id=FND_GLOBAL.USER_ID()
3085 AND ArPaymentSchedules.customer_id=p_customer_id
3086 AND ArPaymentSchedules.customer_id= AcctSites.customer_id
3087 AND AcctSites.customer_site_use_id=ArPaymentSchedules.customer_site_use_id
3088 AND AcctSites.session_id=p_session_id
3089 AND ( ArPaymentSchedules.class = 'INV' OR ArPaymentSchedules.class = 'GUAR' OR ArPaymentSchedules.class = 'CB' OR
3090 ArPaymentSchedules.class = 'DM' OR ArPaymentSchedules.class = 'DEP' )
3091 AND( ArPaymentSchedules.invoice_currency_code = p_currency_code ) AND
3092 (RaCmRequests.status IN ('PENDING_APPROVAL','APPROVED_PEND_COMP'))
3093 AND ArPaymentSchedules.customer_trx_id = ct.customer_trx_id
3094 AND(TRUNC(ArPaymentSchedules.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ArPaymentSchedules.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
3095 AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option) ;
3096 end if;
3097
3098 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3099 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'pending_cmreq_amt '||pending_cmreq_amt);
3100 end if;
3101
3102 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3103 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'End-');
3104 end if;
3105 RETURN pending_cmreq_amt;
3106 EXCEPTION
3107 WHEN OTHERS THEN
3108 pending_cmreq_amt := 0;
3109 return pending_cmreq_amt;
3110 END get_pending_cmreq_amount;
3111
3112 END ari_utilities;
3113
3114