[Home] [Help]
PACKAGE BODY: APPS.ARP_EXT_BANK_PKG
Source
1 PACKAGE BODY ARP_EXT_BANK_PKG AS
2 /* $Header: AREXTBAB.pls 120.12.12020000.3 2012/07/27 11:34:06 kkikkise ship $ */
3 /*-------------------------------------+
4 | WHO column values from FND_GLOBAL |
5 +-------------------------------------*/
6 pg_user_id varchar2(15);
7 pg_login_id number;
8 pg_prog_appl_id number;
9 pg_sob_id number;
10 pg_program_id number;
11 pg_request_id number;
12 l_account_exists number := 0;
13 l_rowid varchar2(18);
14 l_inactive_date date;
15 CRLF VARCHAR2(10):= arp_global.CRLF;
16 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
17 g_default_country ar_system_parameters.default_country%type;
18
19 PROCEDURE strip_white_spaces(
20 p_credit_card_num IN iby_ext_bank_accounts_v.bank_account_number%TYPE,
21 p_stripped_cc_num OUT NOCOPY iby_ext_bank_accounts_v.bank_account_number%TYPE
22 ) IS
23
24 TYPE character_tab_typ IS TABLE of char(1) INDEX BY BINARY_INTEGER;
25 len_credit_card_num number := 0;
26 l_cc_num_char character_tab_typ;
27 BEGIN
28 IF PG_DEBUG in ('Y', 'C') THEN
29 arp_standard.debug('arp_ext_bank_pkg.strip_white_spaces()+');
30 END IF;
31
32 SELECT lengthb(p_credit_card_num)
33 INTO len_credit_card_num
34 FROM dual;
35
36 FOR i in 1..len_credit_card_num LOOP
37 SELECT substrb(p_credit_card_num,i,1)
38 INTO l_cc_num_char(i)
39 FROM dual;
40
41 IF ( (l_cc_num_char(i) >= '0') and
42 (l_cc_num_char(i) <= '9')
43 )
44 THEN
45 -- Numeric digit. Add to stripped_number and table.
46 p_stripped_cc_num := p_stripped_cc_num || l_cc_num_char(i);
47 END IF;
48 END LOOP;
49
50 IF PG_DEBUG in ('Y', 'C') THEN
51 arp_standard.debug('arp_ext_bank_pkg.strip_white_spaces()-');
52 END IF;
53 EXCEPTION
54 when OTHERS then
55 raise;
56 END strip_white_spaces;
57
58 -- begin bug5594142
59 PROCEDURE Branch_Num_Format(
60 p_Country_Code IN Varchar2,
61 p_Branch_Number IN VARCHAR2,
62 p_value_out OUT NOCOPY VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2)
64 IS
65 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
66 l_return_status VARCHAR2(30);
67 l_msg_count NUMBER;
68 l_msg_data VARCHAR2(2000);
69 l_branch_number ce_bank_branches_v.branch_number%TYPE ;
70 l_value_out varchar2(100);
71 BEGIN
72 arp_standard.debug('arp_ext_bank_pkg.Branch_Num_Format(+)');
73 arp_standard.debug('branch number '||p_branch_number);
74 CE_VALIDATE_BANKINFO.GET_BRANCH_NUM_FORMAT
75 (X_COUNTRY_NAME => p_country_code,
76 X_BRANCH_NUMBER => p_branch_number,
77 X_VALUE_OUT => p_value_out,
78 p_init_msg_list => l_init_msg_list,
79 x_msg_count => l_msg_count,
80 x_msg_data => l_msg_data,
81 x_return_status => l_return_status);
82
83 arp_standard.debug ('IBY BRANCH NUM FORMAT API Return Status = ' || l_return_status);
84 arp_standard.debug('IBY BRANCH NUMB FORMAT API Branch Number = '||p_value_out);
85 x_return_status := l_return_status ;
86
87 IF l_return_status = fnd_api.g_ret_sts_error OR
88 l_return_status = fnd_api.g_ret_sts_unexp_error THEN
89
90 arp_standard.debug('Errors Reported By IBY Branch Num Format API: ');
91 FOR i in 1 .. l_msg_count LOOP
92 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data,
93 l_msg_count);
94 arp_standard.debug(l_msg_data);
95 END LOOP;
96 ELSE
97 arp_standard.debug('Branch Number from BRANCH NUM FORMAT API : ' || p_value_out);
98 END IF;
99 arp_standard.debug('arp_ext_bank_pkg.Branch_Num_Format(-)');
100 EXCEPTION
101 WHEN OTHERS THEN
102 arp_standard.debug('exception in arp_ext_bank_pkg.Branch_Num_Format');
103 RAISE;
104 END Branch_Num_Format;
105 -- end bug5594142
106
107
108 /*===========================================================================+
109 | PROCEDURE insert_ext_bank_branch |
110 | |
111 | DESCRIPTION |
112 | Inserts a bank branch via IBY - CE - TCA api. |
113 | |
114 | SCOPE - PUBLIC |
115 | |
116 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
117 | arp_standard.debug |
118 | iby_ext_bankacct_pub.create_ext_bank |
119 | iby_ext_bankacct_pub.create_ext_bank_branch |
120 | |
121 | ARGUMENTS : IN: p_bank_name Bank Name |
122 | p_branch_name Bank Branch Name |
123 | p_bank_number Bank Number |
124 | p_bank_num Bank Branch Number |
125 | p_end_date Inactive on |
126 | p_description Description |
127 | |
128 | OUT: |
129 | x_bank_party_id Bank Party ID |
130 | x_branch_party_id Branch Party ID |
131 | |
132 | RETURNS : NONE |
133 | |
134 | MODIFICATION HISTORY |
135 | 31-AUG-2005 Surendra Rajan Created. |
136 +===========================================================================*/
137
138 PROCEDURE insert_ext_bank_branch(
139 p_bank_name IN ce_bank_branches_v.bank_name%TYPE,
140 p_branch_name IN ce_bank_branches_v.bank_branch_name%TYPE,
141 p_bank_number IN ce_bank_branches_v.bank_number%TYPE,
142 p_branch_number IN ce_bank_branches_v.branch_number%TYPE,
143 p_end_date IN ce_bank_branches_v.end_date%TYPE DEFAULT NULL,
144 p_description IN ce_bank_branches_v.description%TYPE DEFAULT NULL,
145 x_bank_party_id OUT NOCOPY ce_bank_branches_v.bank_party_id%TYPE,
146 x_branch_party_id OUT NOCOPY ce_bank_branches_v.branch_party_id%TYPE,
147 x_return_status OUT NOCOPY VARCHAR2) IS -- bug 5594142
148
149 l_profile_value VARCHAR2(30);
150
151 l_bank_party_id NUMBER;
152 l_bank_party_number VARCHAR2(30);
153 l_bank_profile_id NUMBER;
154 l_bank_code_assignment_id NUMBER;
155 l_ext_bank_rec IBY_EXT_BANKACCT_PUB.extbank_rec_type;
156 l_bank_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
157
158 l_branch_type VARCHAR2(30);
159 l_branch_party_id NUMBER;
160 l_branch_party_number VARCHAR2(30);
161 l_branch_profile_id NUMBER;
162
163 l_ext_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
164 l_branch_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
165
166 l_api_version NUMBER := 1.0;
167 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
168 l_commit VARCHAR2(30) DEFAULT FND_API.G_FALSE;
169 l_return_status VARCHAR2(30);
170 l_msg_count NUMBER;
171 l_msg_data VARCHAR2(2000);
172
173 BEGIN
174
175 arp_standard.debug('arp_ext_bank_pkg.insert_ext_bank_branch(+)');
176
177 fnd_profile.get(
178 name => 'HZ_GENERATE_PARTY_NUMBER',
179 val => l_profile_value);
180
181 -- if profile value is 'N' then put 'Y' for this piece of code.
182 -- once this processing is done put it back to 'N'. please note
183 -- l_profile_valu will continue to have the original value.
184
185 IF (l_profile_value = 'N') THEN
186 fnd_profile.put(
187 name => 'HZ_GENERATE_PARTY_NUMBER',
188 val => 'Y');
189 END IF;
190
191 l_ext_bank_rec.bank_id := NULL;
192 l_ext_bank_rec.bank_name := p_bank_name;
193 l_ext_bank_rec.bank_number := p_bank_number;
194 l_ext_bank_rec.institution_type := 'BANK';
195 l_ext_bank_rec.country_code := g_default_country;
196 l_ext_bank_rec.description := p_description;
197
198
199 arp_standard.debug('Calling iby_ext_bankacct_pub.create_ext_bank(+)');
200
201 iby_ext_bankacct_pub.create_ext_bank(
202 -- IN parameters
203 p_api_version => l_api_version,
204 p_init_msg_list => l_init_msg_list,
205 p_ext_bank_rec => l_ext_bank_rec,
206 -- OUT parameters
207 x_bank_id => l_bank_party_id,
208 x_return_status => l_return_status,
209 x_msg_count => l_msg_count,
210 x_msg_data => l_msg_data,
211 x_response => l_bank_response );
212
213 arp_standard.debug ('IBY Bank API Return Status = ' || l_return_status);
214 -- begin bug5594142
215 x_return_status := l_return_status ;
216 -- end bug5594142
217
218 IF l_return_status = fnd_api.g_ret_sts_error OR
219 l_return_status = fnd_api.g_ret_sts_unexp_error THEN
220
221 arp_standard.debug('Errors Reported By Bank API: ');
222 FOR i in 1 .. l_msg_count LOOP
223 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data,
224 l_msg_count);
225 arp_standard.debug(l_msg_data);
226 END LOOP;
227
228 ELSE
229 arp_standard.debug('Bank Party ID : ' || l_bank_party_id);
230 END IF;
231
232 x_bank_party_id := l_bank_party_id;
233
234 -- create a branch for the bank above
235
236 l_ext_branch_rec.branch_party_id := NULL;
237 l_ext_branch_rec.bank_party_id := l_bank_party_id;
238 l_ext_branch_rec.branch_name := p_branch_name;
239 l_ext_branch_rec.branch_number := p_branch_number;
240 l_ext_branch_rec.branch_type := 'ABA';
241 l_ext_branch_rec.description := p_description;
242
243 arp_standard.debug('Calling iby_ext_bankacct_pub.create_ext_bank_branch(+)');
244
245 iby_ext_bankacct_pub.create_ext_bank_branch(
246 -- IN parameters
247 p_api_version => l_api_version,
248 p_init_msg_list => l_init_msg_list,
249 p_ext_bank_branch_rec => l_ext_branch_rec,
250 -- OUT parameters
251 x_branch_id => l_branch_party_id,
252 x_return_status => l_return_status,
253 x_msg_count => l_msg_count,
254 x_msg_data => l_msg_data,
255 x_response => l_branch_response);
256
257 arp_standard.debug ('IBY Bank Branch API Return Status = ' || l_return_status);
258 -- begin bug5594142
259 x_return_status := l_return_status ;
260 -- end bug5594142
261
262 IF l_return_status = fnd_api.g_ret_sts_error OR
263 l_return_status = fnd_api.g_ret_sts_unexp_error THEN
264
265 arp_standard.debug('Errors Reported By Bank Branch API: ');
266 FOR i in 1 .. l_msg_count LOOP
267 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data,
268 l_msg_count);
269 arp_standard.debug(l_msg_data);
270 END LOOP;
271 ELSE
272 arp_standard.debug('Branch Party ID : ' || l_branch_party_id);
273 END IF;
274
275 -- put the profile value back to the original value if changed
276 IF (l_profile_value = 'N') THEN
277 fnd_profile.put(
278 name => 'HZ_GENERATE_PARTY_NUMBER',
279 val => 'N');
280 END IF;
281
282 x_branch_party_id := l_branch_party_id;
283
284 arp_standard.debug('arp_ext_bank_pkg.insert_ext_bank_branch(-)');
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 arp_standard.debug('exception in arp_ext_bank_pkg.insert_ext_bank_branch');
289 RAISE;
290
291 END insert_ext_bank_branch;
292
293 /*===========================================================================+
294 | PROCEDURE insert_bank_account |
295 | |
296 | DESCRIPTION |
297 | Inserts a bank account via iby api. |
298 | |
299 | SCOPE - PUBLIC |
300 | |
301 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
302 | arp_standard.debug |
303 | iby_ext_bankacct_pub.create_ext_bank_acc |
304 | |
305 | ARGUMENTS : IN: |
306 | p_bank_account_name Account Name |
307 | p_bank_account_num Account Number |
308 | p_bank_party_id Bank Party ID |
309 | p_branch_party_id Branch Party ID |
310 | p_customer_id Customer ID |
311 | p_description Description |
312 | |
313 | OUT: |
314 | x_bank_account_id Bank Account ID |
315 | |
316 | RETURNS : NONE |
317 | |
318 | MODIFICATION HISTORY |
319 | 01-Sep-2005 Surendra Rajan Created |
320 | |
321 +===========================================================================*/
322
323 procedure insert_bank_account(
324 p_bank_account_name in iby_ext_bank_accounts_v.bank_account_name%type,
325 p_bank_account_num in iby_ext_bank_accounts_v.bank_account_number%type,
326 p_bank_party_id in iby_ext_bank_accounts_v.bank_party_id%type,
327 p_branch_party_id in iby_ext_bank_accounts_v.branch_party_id%type,
328 p_customer_id in iby_ext_bank_accounts_v.primary_acct_owner_party_id%type,
329 p_description in iby_ext_bank_accounts_v.description%type,
330 p_currency_code in iby_ext_bank_accounts_v.currency_code%type DEFAULT NULL,
331 p_start_date in DATE,
332 x_bank_account_id OUT NOCOPY iby_ext_bank_accounts_v.bank_account_id%type,
333 x_return_status OUT NOCOPY VARCHAR2 -- bug 5594142
334 ) IS
335
336 l_bank_account_id iby_ext_bank_accounts_v.bank_account_id%TYPE;
337 l_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
338 l_bank_acct_response iby_fndcpt_common_pub.result_rec_type;
339 l_party_id iby_ext_bank_accounts_v.primary_acct_owner_party_id%type;
340
341 l_api_version NUMBER := 1.0;
342 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
343 l_commit VARCHAR2(30) DEFAULT FND_API.G_FALSE;
344 l_return_status VARCHAR2(30);
345 l_msg_count NUMBER;
346 l_msg_data VARCHAR2(2000);
347
348 BEGIN
349 arp_standard.debug('arp_ext_bank_pkg.insert_bank_account(+)');
350
351 /* Bug 6799655 : Get the party_id from cust_account_id supplied */
352 IF p_customer_id IS NOT NULL THEN
353 SELECT party_id INTO l_party_id
354 FROM hz_cust_accounts
355 WHERE cust_account_id = p_customer_id;
356 ELSE
357 l_party_id := NULL;
358 END IF;
359
360 -- l_ext_bank_acct_rec.bank_account_id := NULL;
361 l_ext_bank_acct_rec.bank_account_name := p_bank_account_name;
362 l_ext_bank_acct_rec.bank_account_num := p_bank_account_num;
363 l_ext_bank_acct_rec.bank_id := p_bank_party_id;
364 l_ext_bank_acct_rec.branch_id := p_branch_party_id;
365 l_ext_bank_acct_rec.acct_owner_party_id := l_party_id;
366 l_ext_bank_acct_rec.country_code := g_default_country;
367 l_ext_bank_acct_rec.description := p_description;
368 l_ext_bank_acct_rec.currency := p_currency_code;
369 l_ext_bank_acct_rec.start_date := p_start_date;
370
371 -- fnd_file.put_line(FND_FILE.LOG,'The start date is ' || p_start_date);
372
373 arp_standard.debug('Calling iby_ext_bankacct_pub.create_ext_bank_acct(+)');
374
375 iby_ext_bankacct_pub.create_ext_bank_acct(
376 -- IN parameters
377 p_api_version => l_api_version,
378 p_init_msg_list => l_init_msg_list,
379 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
380 -- OUT parameters
381 x_acct_id => l_bank_account_id,
382 x_return_status => l_return_status,
383 x_msg_count => l_msg_count,
384 x_msg_data => l_msg_data,
385 x_response => l_bank_acct_response );
386
387 arp_standard.debug ('IBY Bank API Return Status = ' || l_return_status);
388 -- begin bug5594142
389 x_return_status := l_return_status ;
390 -- end bug5594142
391
392 IF l_return_status = fnd_api.g_ret_sts_error OR
393 l_return_status = fnd_api.g_ret_sts_unexp_error THEN
394
395 arp_standard.debug('Errors Reported By Bank API: ');
396 FOR i in 1 .. l_msg_count LOOP
397 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data,
398 l_msg_count);
399 arp_standard.debug(l_msg_data);
400 END LOOP;
401
402 ELSE
403 arp_standard.debug('Bank Account ID : ' || l_bank_account_id);
404 END IF;
405
406 x_bank_account_id:= l_bank_account_id;
407 arp_standard.debug('arp_ext_bank_pkg.insert_bank_account(-)');
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 arp_standard.debug('Customer_id is invalid');
411 RAISE;
412 WHEN OTHERS THEN
413 arp_standard.debug('exception in arp_ext_bank_pkg.insert_bank_account');
414 RAISE;
415 END insert_bank_account;
416
417
418 /*===========================================================================+
419 | PROCEDURE check_bank_account |
420 | |
421 | DESCRIPTION |
422 | Checks whether an external bank account already exists based on |
423 | routing_number and account_number. |
424 | SCOPE - PUBLIC |
425 | |
426 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
427 | arp_standard.debug |
428 | iby_ext_bankacct_pub.check_ext_acct_exist |
429 | |
430 | ARGUMENTS : IN: |
431 | p_routing_number Routing Number |
432 | p_account_number Account Number |
433 | p_bank_party_id Bank Party ID |
434 | p_branch_party_id Branch Party ID |
435 | OUT: |
436 | x_bank_account_id Bank Account Id |
437 | x_start_date Start Date |
438 | x_end_date End Date |
439 | |
440 | RETURNS : NONE |
441 | |
442 | MODIFICATION HISTORY |
443 | 01-Sep-2005 Surendra Rajan Created |
444 | |
445 +===========================================================================*/
446
447 procedure check_bank_account(
448 p_routing_number IN ce_bank_branches_v.branch_number%TYPE,
449 p_account_number IN iby_ext_bank_accounts_v.bank_account_number%TYPE,
450 p_bank_party_id IN iby_ext_bank_accounts_v.bank_party_id%type,
451 p_branch_party_id IN iby_ext_bank_accounts_v.branch_party_id%type,
452 p_currency_code IN iby_ext_bank_accounts_v.currency_code%type DEFAULT NULL,
453 x_bank_account_id OUT NOCOPY iby_ext_bank_accounts_v.bank_account_id%TYPE,
454 x_start_date OUT NOCOPY iby_ext_bank_accounts_v.start_date%TYPE,
455 x_end_date OUT NOCOPY iby_ext_bank_accounts_v.end_date%TYPE,
456 x_return_status OUT NOCOPY VARCHAR2 -- 5594142
457 ) IS
458
459 l_bank_account_id iby_ext_bank_accounts_v.bank_account_id%TYPE;
460 l_start_date iby_ext_bank_accounts_v.start_date%TYPE;
461 l_end_date iby_ext_bank_accounts_v.end_date%TYPE;
462 l_bank_acct_response iby_fndcpt_common_pub.result_rec_type;
463
464 l_api_version NUMBER := 1.0;
465 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
466 l_commit VARCHAR2(30) DEFAULT FND_API.G_FALSE;
467 l_return_status VARCHAR2(30);
468 l_msg_count NUMBER;
469 l_msg_data VARCHAR2(2000);
470
471 BEGIN
472 arp_standard.debug('arp_ext_bank_pkg.check_bank_account(+)');
473
474
475 iby_ext_bankacct_pub.check_ext_acct_exist(
476 -- IN parameters
477 p_api_version => l_api_version,
478 p_init_msg_list => l_init_msg_list,
479 p_bank_id => p_bank_party_id,
480 p_branch_id => p_branch_party_id,
481 p_acct_number => p_account_number,
482 p_acct_name => p_routing_number||' '||p_account_number,
483 p_currency => p_currency_code,
484 p_country_code => g_default_country,
485 -- OUT parameters
486 x_acct_id => l_bank_account_id,
487 x_start_date => l_start_date,
488 x_end_date => l_end_date,
489 x_return_status => l_return_status,
490 x_msg_count => l_msg_count,
491 x_msg_data => l_msg_data,
492 x_response => l_bank_acct_response );
493
494 arp_standard.debug ('IBY Bank API Return Status = ' || l_return_status);
495 -- begin bug5594142
496 x_return_status := l_return_status ;
497 -- end bug5594142
498
499 IF l_return_status = fnd_api.g_ret_sts_error OR
500 l_return_status = fnd_api.g_ret_sts_unexp_error THEN
501
502 arp_standard.debug('Errors Reported By Bank API: ');
503 FOR i in 1 .. l_msg_count LOOP
504 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data,
505 l_msg_count);
506 arp_standard.debug(l_msg_data);
507 END LOOP;
508
509 ELSE
510 arp_standard.debug('Bank Account ID : ' || l_bank_account_id);
511 arp_standard.debug('Start Date : ' || l_start_date );
512 arp_standard.debug('End Date : ' || l_end_date );
513 END IF;
514
515 x_bank_account_id:= l_bank_account_id;
516 x_start_date := l_start_date;
517 x_end_date := l_end_date;
518 arp_standard.debug('arp_ext_bank_pkg.check_bank_account(-)');
519 EXCEPTION
520 WHEN OTHERS THEN
521 arp_standard.debug('exception in arp_ext_bank_pkg.check_bank_account');
522 RAISE;
523 END check_bank_account;
524
525 /*===========================================================================+
526 | PROCEDURE Create_bank_branch_acc |
527 | |
528 | DESCRIPTION |
529 | Tries to find a bank branch based on routing number, if branch not |
530 | found, then a new one is created. |
531 | SCOPE - PUBLIC |
532 | |
533 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
534 | arp_standard.debug |
535 | iby_ext_bankacct_pub.create_ext_bank_acc |
536 | |
537 | ARGUMENTS : IN: |
538 | p_routing_number Routing Number |
539 | p_account_number Account Number |
540 | |
541 | OUT: |
542 | x_bank_party_id Bank Id |
543 | x_branch_party_id Bank Branch Id |
544 | x_bank_account_id Bank Account Id |
545 | |
546 | RETURNS : NONE |
547 | |
548 | MODIFICATION HISTORY |
549 | 01-Sep-2005 Surendra Rajan Created |
550 | |
551 +===========================================================================*/
552 PROCEDURE create_bank_branch_acc(
553 p_routing_number IN OUT NOCOPY ce_bank_branches_v.branch_number%TYPE,
554 p_account_number IN iby_ext_bank_accounts_v.bank_account_number%TYPE,
555 p_description IN iby_ext_bank_accounts_v.description%type,
556 p_customer_id IN iby_ext_bank_accounts_v.primary_acct_owner_party_id%type,
557 p_currency_code IN iby_ext_bank_accounts_v.currency_code%type DEFAULT NULL,
558 p_start_date IN DATE,
559 x_bank_party_id OUT NOCOPY ce_bank_branches_v.bank_party_id%TYPE,
560 x_branch_party_id OUT NOCOPY ce_bank_branches_v.branch_party_id%TYPE,
561 x_bank_account_id OUT NOCOPY iby_ext_bank_accounts_v.bank_account_id%TYPE,
562 x_return_status OUT NOCOPY VARCHAR2) IS -- bug5594142
563
564 /*-----------------------------------------------------+
565 | Cursor to fetch bank branch based on routing number |
566 +-----------------------------------------------------*/
567 CURSOR bank_branch_cur(l_routing_number VARCHAR2) IS
568 SELECT bank_party_id,branch_party_id
569 FROM ce_bank_branches_V
570 WHERE branch_number = l_routing_number;
571
572 l_routing_number ce_bank_branches_v.bank_number%TYPE;
573 bank_branch_rec bank_branch_cur%ROWTYPE;
574 l_bank_party_id ce_bank_branches_v.bank_party_id%TYPE;
575 l_branch_party_id ce_bank_branches_v.branch_party_id%TYPE;
576 l_account_name iby_ext_bank_accounts_v.bank_account_name%TYPE;
577 l_bank_account_id iby_ext_bank_accounts_v.bank_account_id%TYPE;
578 l_start_date iby_ext_bank_accounts_v.start_date%TYPE;
579 l_end_date iby_ext_bank_accounts_v.end_date%TYPE;
580 l_return_status VARCHAR2(30) ;
581 l_assign_id NUMBER;
582 l_party_id NUMBER;
583 l_count NUMBER;
584
585 BEGIN
586 arp_standard.debug('arp_ext_bank_pkg.create_bank_branch_acc(+)');
587 /*-----------------------------------------------------+
588 | Remove non-digit characters from the routing number |
589 +-----------------------------------------------------*/
590 strip_white_spaces(p_routing_number,l_routing_number);
591
592 -- begin bug 5594142, Get Routing Number
593 Branch_Num_Format(g_default_country,
594 l_routing_Number,
595 p_routing_Number,
596 l_return_status);
597 x_return_status := l_return_status ;
598 IF x_return_status <> 'S' THEN
599 p_routing_number := l_routing_number ;
600 x_bank_party_id := NULL ;
601 x_branch_party_id := NULL ;
602 x_bank_account_id := NULL ;
603 return ;
604 END IF ;
605 -- end bug 5594142,
606 /*----------------------------------------------------+
607 | Try to find bank branch based on routing number |
608 +----------------------------------------------------*/
609 OPEN bank_branch_cur(p_routing_number);
610 FETCH bank_branch_cur INTO bank_branch_rec;
611 IF (bank_branch_cur%FOUND) then
612 CLOSE bank_branch_cur;
613 l_bank_party_id := bank_branch_rec.bank_party_id;
614 l_branch_party_id := bank_branch_rec.branch_party_id;
615 x_bank_party_id := l_bank_party_id;
616 x_branch_party_id := l_branch_party_id;
617
618 arp_standard.debug('Bank and Branch exist for this Routing Number'
619 ||p_routing_number);
620 arp_standard.debug('Bank Id '||x_bank_party_id);
621 arp_standard.debug('Branch Id '||x_branch_party_id);
622 /*-----------------------------------------------------------+
623 | Try to find Account Id based on routing account number |
624 +-----------------------------------------------------------*/
625 check_bank_account(
626 -- IN parameters
627 p_routing_number => p_routing_number,
628 p_account_number => p_account_number,
629 p_bank_party_id => x_bank_party_id,
630 p_branch_party_id => x_branch_party_id,
631 p_currency_code => p_currency_code,
632 -- OUT parameters
633 x_bank_account_id => l_bank_account_id,
634 x_start_date => l_start_date,
635 x_end_date => l_end_date,
636 x_return_status => l_return_status
637 );
638 x_return_status := l_return_Status ;
639 If l_bank_account_id is NOT NULL
640 THEN
641 x_bank_account_id := l_bank_account_id;
642 arp_standard.debug('Bank Account ID : ' || l_bank_account_id);
643 ELSE
644 /*-----------------------------------------------------+
645 | Account not exists for this bank |
646 +-----------------------------------------------------*/
647 l_account_name := p_routing_number||' '||p_account_number;
648
649 Insert_bank_account(
650 -- IN parameters
651 p_bank_account_name => l_account_name,
652 p_bank_account_num => p_account_number,
653 p_bank_party_id => l_bank_party_id,
654 p_branch_party_id => l_branch_party_id,
655 p_customer_id => p_customer_id,
656 p_description => p_description,
657 p_currency_code => p_currency_code,
658 p_start_date => p_start_date,
659 -- OUT parameters
660 x_bank_account_id => l_bank_account_id,
661 x_return_status => l_return_status);
662
663 x_return_status := l_return_status ;
664 x_bank_account_id := l_bank_account_id;
665 arp_standard.debug('Bank Account ID : ' || l_bank_account_id);
666 END IF;
667
668 ELSE
669 CLOSE bank_branch_cur;
670 /*------------------------------------------------------+
671 | If bank branch could not be found, create new branch |
672 +------------------------------------------------------*/
673 arp_standard.debug('Bank and Branch does not exist for this Routing
674 Number'||p_routing_number);
675 Insert_ext_bank_branch(
676 -- IN parameters
677 p_bank_name => p_routing_number,
678 p_branch_name => p_routing_number,
679 p_bank_number => p_routing_number,
680 p_branch_number => p_routing_number,
681 p_description => p_description,
682 -- OUT parameters
683 x_bank_party_id => l_bank_party_id,
684 x_branch_party_id => l_branch_party_id,
685 x_return_status => l_return_status);
686
687 x_return_status := l_return_status ;
688 x_bank_party_id := l_bank_party_id;
689 x_branch_party_id := l_branch_party_id;
690
691 arp_standard.debug('Bank ID : ' || l_bank_party_id);
692 arp_standard.debug('Branch Party ID : ' || l_branch_party_id);
693
694 /*-----------------------------------------------------+
695 | Now create bank account based on the bank branch |
696 +-----------------------------------------------------*/
697 If l_bank_party_id is NOT NULL and
698 l_branch_party_id is NOT NULL
699 THEN
700 l_account_name := p_routing_number||' '||p_account_number;
701
702 Insert_bank_account(
703 -- IN parameters
704 p_bank_account_name => l_account_name,
705 p_bank_account_num => p_account_number,
706 p_bank_party_id => l_bank_party_id,
707 p_branch_party_id => l_branch_party_id,
708 p_customer_id => p_customer_id,
709 p_description => p_description,
710 p_currency_code => p_currency_code,
711 p_start_date => p_start_date,
712 -- OUT parameters
713 x_bank_account_id => l_bank_account_id,
714 x_return_status => l_return_status);
715
716 x_return_status := l_return_status ;
717 x_bank_account_id := l_bank_account_id;
718 arp_standard.debug('Bank Account ID : ' || l_bank_account_id);
719 END IF;
720 END IF;
721
722 -- Bug 7346354 - Start
723 IF ( l_bank_account_id IS NOT NULL ) AND ( p_customer_id IS NOT NULL ) THEN
724 -- Check whether the assignment exists already
725 SELECT count(*) INTO l_count FROM iby_fndcpt_payer_assgn_instr_v
726 WHERE cust_account_id = p_customer_id
727 AND bank_acct_num_hash1 = iby_security_pkg.get_hash(p_account_number, 'F')
728 AND bank_acct_num_hash2 = iby_security_pkg.get_hash(p_account_number, 'T')
729 AND branch_number = p_routing_number;
730
731 IF(l_count = 0) THEN
732 SELECT party_id INTO l_party_id
733 FROM hz_cust_accounts
734 WHERE cust_account_id = p_customer_id;
735
736 insert_acct_instr_assignment(
737 p_party_id => l_party_id,
738 p_customer_id => p_customer_id,
739 p_instr_id => l_bank_account_id,
740 p_assignment_start_date => p_start_date,
741 x_instr_assign_id => l_assign_id,
742 x_return_status => l_return_status);
743 END IF;
744 END IF;
745 -- Bug 7346354 - End
746 arp_standard.debug('l_assign_id '||l_assign_id);
747 arp_standard.debug('arp_ext_bank_pkg.create_bank_branch_acc(-)');
748 EXCEPTION
749 WHEN OTHERS THEN
750 arp_standard.debug('exception in arp_ext_bank_pkg.create_bank_branch_acc');
751 RAISE;
752 END create_bank_branch_acc;
753
754 -- Bug 7346354 - Start
755 /*===========================================================================+
756 | PROCEDURE insert_acct_instr_assignment |
757 | |
758 | DESCRIPTION |
759 | Creates an instrument assignment to the given customer with the |
760 | bank account. |
761 | SCOPE - PUBLIC |
762 | |
763 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
764 | arp_standard.debug |
765 | IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment |
766 | |
767 | ARGUMENTS : IN: |
768 | p_party_id Party Id |
769 | p_customer_id Customer Id[Cust Account Id] |
770 | p_instr_id Instrument Id[Bank Account Id] |
771 | OUT: |
772 | x_instr_assign_id Instrument Assign Id |
773 | |
774 | RETURNS : NONE |
775 | |
776 | MODIFICATION HISTORY |
777 | 01-Aug-2008 Thirumalaisamy Created |
778 | |
779 +===========================================================================*/
780 PROCEDURE insert_acct_instr_assignment(
781 p_party_id IN NUMBER,
782 p_customer_id IN NUMBER,
783 p_instr_id IN NUMBER,
784 p_assignment_start_date IN DATE,
785 x_instr_assign_id OUT NOCOPY iby_fndcpt_payer_assgn_instr_v.INSTR_ASSIGNMENT_ID%TYPE,
786 x_return_status OUT NOCOPY VARCHAR2) IS
787
788 l_payer_context_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
789 l_pmtInstrAssignment_rec IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
790 l_pmtInstrument_rec_type IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
791 l_api_version NUMBER := 1.0;
792 l_init_msg_list VARCHAR2(30) DEFAULT FND_API.G_TRUE;
793 l_commit VARCHAR2(30) DEFAULT FND_API.G_FALSE;
794 l_return_status VARCHAR2(30);
795 l_msg_count NUMBER;
796 l_msg_data VARCHAR2(2000);
797 l_assign_id NUMBER;
798 l_branch_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
799
800 BEGIN
801 arp_standard.debug('arp_ext_bank_pkg.insert_acct_instr_assignment(+)');
802 l_payer_context_rec.Payment_Function := 'CUSTOMER_PAYMENT';
803 l_payer_context_rec.Party_Id := p_party_id;
804 l_payer_context_rec.Cust_Account_Id := p_customer_id;
805
806 l_pmtInstrument_rec_type.Instrument_Type := 'BANKACCOUNT';
807 l_pmtInstrument_rec_type.Instrument_Id := p_instr_id;
808
809 l_pmtInstrAssignment_rec.Instrument := l_pmtInstrument_rec_type;
810 l_pmtInstrAssignment_rec.Start_date := p_assignment_start_date;
811
812 arp_standard.debug('Calling IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment(+)');
813
814 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment(
815 -- IN parameters
816 p_api_version => l_api_version,
817 p_init_msg_list => l_init_msg_list,
818 p_commit => l_commit,
819 p_payer => l_payer_context_rec,
820 p_assignment_attribs => l_pmtInstrAssignment_rec,
821 -- OUT parameters
822 x_assign_id => l_assign_id,
823 x_return_status => l_return_status,
824 x_msg_count => l_msg_count,
825 x_msg_data => l_msg_data,
826 x_response => l_branch_response);
827 arp_standard.debug('IBY IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment API Return Status = ' || l_return_status);
828 x_return_status := l_return_status ;
829
830 IF l_return_status = fnd_api.g_ret_sts_error OR
831 l_return_status = fnd_api.g_ret_sts_unexp_error THEN
832
833 arp_standard.debug('Errors Reported By Instrument Assignment API: ');
834 FOR i in 1 .. l_msg_count LOOP
835 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false, l_msg_data,
836 l_msg_count);
837 arp_standard.debug(l_msg_data);
838 END LOOP;
839
840 ELSE
841 arp_standard.debug('Instrument Assign Id : ' || l_assign_id);
842 END IF;
843 x_instr_assign_id := l_assign_id;
844 arp_standard.debug('arp_ext_bank_pkg.insert_acct_instr_assignment(-)');
845 EXCEPTION
846 WHEN OTHERS THEN
847 arp_standard.debug('exception in arp_ext_bank_pkg.insert_acct_instr_assignment');
848 RAISE;
849 END insert_acct_instr_assignment;
850 -- Bug 7346354 - End
851
852 /* bug 6121157 Function is added to return customer payment method id associated with a customer */
853
854 /*===========================================================================+
855 | FUNCTION get_cust_pay_method |
856 | |
857 | DESCRIPTION |
858 | Returns customer payment method id |
859 | |
860 | SCOPE - PUBLIC |
861 | |
862 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
863 | arp_standard.debug |
864 | |
865 | |
866 | ARGUMENTS : IN: p_customer_id |
867 | p_site_use_id |
868 | p_pay_method_id |
869 | p_cc_only |
870 | p_primary |
871 | p_check |
872 | p_as_of_date |
873 | |
874 | RETURNS : Number |
875 | |
876 | MODIFICATION HISTORY |
877 | 15-Jun-2007 Deep Gaurab Created. |
878 +===========================================================================*/
879 FUNCTION get_cust_pay_method(p_customer_id IN NUMBER,
880 p_site_use_id IN NUMBER DEFAULT null,
881 p_pay_method_id IN NUMBER DEFAULT null,
882 p_cc_only IN BOOLEAN DEFAULT TRUE,
883 p_primary IN BOOLEAN DEFAULT TRUE,
884 p_check IN BOOLEAN DEFAULT FALSE,
885 p_as_of_date IN DATE DEFAULT TRUNC(SYSDATE)) RETURN NUMBER IS
886 TYPE pri_pay_method_typ IS REF CURSOR;
887
888 pri_pay_method pri_pay_method_typ;
889 l_receipt_method_id AR_RECEIPT_METHODS.receipt_method_id%TYPE := NULL;
890 l_site_use_id AP_BANK_ACCOUNT_USES.CUSTOMER_SITE_USE_ID%TYPE;
891 l_as_of_date DATE := NVL(p_as_of_date, TRUNC(SYSDATE));
892
893 sql_stmt VARCHAR2(10000);
894
895 site_sql_stmt VARCHAR2(4000) := '
896 SELECT cust_RECEIPT_METHOD_ID, NVL(site_use_id, -1)
897 FROM ra_cust_receipt_methods rm
898 WHERE rm.customer_id = :p_customer_id
899 AND rm.SITE_USE_ID = NVL(:p_site_use_id, -1)
900 AND :p_as_of_date BETWEEN rm.start_date AND NVL(rm.end_date, :p_as_of_date ) ';
901
902 cust_sql_stmt VARCHAR2(4000) := '
903 UNION
904 SELECT cust_RECEIPT_METHOD_ID, NVL(site_use_id, -1)
905 FROM ra_cust_receipt_methods rm
906 WHERE rm.customer_id = :p_customer_id
907 AND rm.SITE_USE_ID IS NULL
908 AND :p_as_of_date BETWEEN rm.start_date AND NVL(rm.end_date, :p_as_of_date ) ';
909
910 cc_only_stmt VARCHAR2(4000) := ' AND EXISTS ( SELECT 1 FROM ar_receipt_methods ba
911 WHERE ba.RECEIPT_METHOD_ID = rm.RECEIPT_METHOD_ID
912 AND ba.payment_channel_code = ''CREDIT_CARD'' ) '; /* Bug 14319639 */
913 primary_stmt VARCHAR2(4000) := ' AND rm.primary_flag = ''Y'' ';
914 pay_stmt VARCHAR2(4000) := ' AND rm.receipt_method_id = :p_pay_method_id ';
915
916 BEGIN
917 --
918 IF NOT p_check THEN
919 IF p_site_use_id IS NOT NULL THEN
920 cust_sql_stmt := cust_sql_stmt || CRLF || ' AND 1 = 2 ';
921 END IF;
922 END IF;
923 --
924 sql_stmt := site_sql_stmt;
925 --
926 IF p_primary THEN
927 --
928 arp_standard.debug('Primary Only..');
929 sql_stmt := sql_stmt || CRLF || primary_stmt;
930 --
931 END IF;
932 --
933 IF p_cc_only THEN
934 arp_standard.debug('In CC Only..');
935 sql_stmt := sql_stmt || CRLF || cc_only_stmt;
936 null;
937 END IF;
938 --
939 IF p_pay_method_id IS NOT NULL THEN
940 arp_standard.debug('Pay Method Only..');
941 sql_stmt := sql_stmt || CRLF || pay_stmt;
942 null;
943 END IF;
944 --
945 sql_stmt := sql_stmt || CRLF || cust_sql_stmt;
946
947 IF p_primary THEN
948 --
949 sql_stmt := sql_stmt || CRLF || primary_stmt;
950 --
951 END IF;
952 --
953 IF p_cc_only THEN
954 arp_standard.debug('In CC Only..');
955 sql_stmt := sql_stmt || CRLF || cc_only_stmt;
956 null;
957 END IF;
958 --
959 IF p_pay_method_id IS NOT NULL THEN
960 arp_standard.debug('Pay Method Only..');
961 sql_stmt := sql_stmt || CRLF || pay_stmt;
962 null;
963 END IF;
964 --
965 arp_standard.debug(sql_stmt);
966
967 IF p_pay_method_id IS NOT NULL THEN
968 OPEN pri_pay_method FOR sql_stmt USING p_customer_id, p_site_use_id, l_as_of_date,
969 l_as_of_date, p_pay_method_id,
970 p_customer_id, l_as_of_date,
971 l_as_of_date, p_pay_method_id;
972
973 ELSE
974 OPEN pri_pay_method FOR sql_stmt USING p_customer_id, p_site_use_id, l_as_of_date, l_as_of_date,
975 p_customer_id, l_as_of_date, l_as_of_date;
976 END IF;
977
978 -- Always pick the first
979
980 FETCH pri_pay_method INTO l_receipt_method_id, l_site_use_id;
981
982 CLOSE pri_pay_method;
983
984 RETURN (l_receipt_method_id);
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 RAISE;
989 END get_cust_pay_method;
990
991 /* bug 6121157 Function "process_cust_pay_method()" added to check if the customer already has a payment method associated else it will insert a record in ra_cust_receipt_methods. */
992
993 /*===========================================================================+
994 | FUNCTION process_cust_pay_method |
995 | |
996 | DESCRIPTION |
997 | Check if the customer already has a payment method associated else it |
998 | will insert a record in ra_cust_receipt_methods. |
999 | |
1000 | SCOPE - PUBLIC |
1001 | |
1002 | |
1003 | ARGUMENTS : IN: p_pay_method_id |
1004 | p_customer_id |
1005 | p_site_use_id |
1006 | p_as_of_date |
1007 | |
1008 | |
1009 | RETURNS : Number |
1010 | |
1011 | MODIFICATION HISTORY |
1012 | 15-Jun-2007 Deep Gaurab Created. |
1013 +===========================================================================*/
1014 FUNCTION process_cust_pay_method (
1015 p_pay_method_id IN NUMBER,
1016 p_customer_id IN NUMBER,
1017 p_site_use_id IN NUMBER DEFAULT null,
1018 p_as_of_date IN DATE DEFAULT TRUNC(SYSDATE) ) RETURN NUMBER IS
1019 l_cust_pay_method_id NUMBER;
1020 l_primary_flag ra_cust_receipt_methods.primary_flag%type;
1021 --
1022 FUNCTION check_primary_method_exists (p_customer_id IN NUMBER,
1023 p_site_use_id IN NUMBER DEFAULT null,
1024 p_as_of_date IN DATE DEFAULT TRUNC(SYSDATE)) RETURN BOOLEAN IS
1025 l_result BOOLEAN := FALSE;
1026 BEGIN
1027 IF get_cust_pay_method(p_customer_id=>p_customer_id,
1028 p_site_use_id=>p_site_use_id,
1029 p_cc_only=>FALSE,
1030 p_primary=>TRUE,
1031 p_as_of_date=>NVL(p_as_of_date, TRUNC(SYSDATE))) IS NOT NULL THEN
1032 l_result := TRUE;
1033 ELSE
1034 l_result := FALSE;
1035 END IF;
1036
1037 RETURN(l_result);
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 RAISE;
1041 END check_primary_method_exists;
1042 --
1043 BEGIN
1044 l_cust_pay_method_id := get_cust_pay_method(p_customer_id=>p_customer_id,
1045 p_site_use_id=>p_site_use_id,
1046 p_pay_method_id=>p_pay_method_id,
1047 p_cc_only=>FALSE,
1048 p_primary=>FALSE,
1049 p_as_of_date=>NVL(p_as_of_date, TRUNC(SYSDATE)));
1050 IF l_cust_pay_method_id IS NULL THEN
1051 --
1052 SELECT
1053 RA_CUST_RECEIPT_METHODS_S.NEXTVAL
1054 INTO
1055 l_cust_pay_method_id
1056 FROM
1057 dual;
1058 --
1059
1060 /* Bug 8359208, We need to create non-primary receipt method for customer
1061 irrespective of whether a primary receipt method exists for customer or not. */
1062
1063 l_primary_flag := 'N';
1064
1065 --
1066 INSERT INTO ra_cust_receipt_methods
1067 (customer_id,
1068 receipt_method_id,
1069 primary_flag,
1070 creation_date,
1071 created_by,
1072 last_update_date,
1073 last_updated_by,
1074 program_application_id,
1075 site_use_id,
1076 start_date,
1077 cust_receipt_method_id)
1078 VALUES
1079 (p_customer_id, -- Customer Id
1080 p_pay_method_id, -- Receipt Method Id
1081 l_primary_flag, -- Primary Flag
1082 SYSDATE, -- Creation Date
1083 pg_user_id, -- Created By
1084 SYSDATE, -- Last Update Date
1085 pg_user_id, -- Last Updated By
1086 pg_prog_appl_id, -- Program Application Id
1087 p_site_use_id, -- Site use Id
1088 TRUNC(p_as_of_date), -- Start Date
1089 l_cust_pay_method_id);
1090
1091 END IF;
1092
1093 RETURN(l_cust_pay_method_id);
1094
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 RAISE;
1098
1099 END process_cust_pay_method;
1100
1101 /*---------------------------------------------+
1102 | Package initialization section. |
1103 | Sets WHO column variables for later use. |
1104 +---------------------------------------------*/
1105 BEGIN
1106
1107 pg_user_id := fnd_global.user_id;
1108 pg_login_id := fnd_global.login_id;
1109 pg_prog_appl_id := fnd_global.prog_appl_id;
1110
1111 /* J Rautiainen ACH Implementation */
1112 pg_program_id := fnd_global.conc_program_id;
1113 pg_request_id := fnd_global.conc_request_id;
1114 pg_sob_id := arp_global.set_of_books_id;
1115
1116 BEGIN
1117 SELECT NVL(DEFAULT_COUNTRY, 'US') INTO g_default_country
1118 FROM AR_SYSTEM_PARAMETERS;
1119
1120 arp_standard.debug('Default Country to create bank accounts for New MICR Numbers: '||g_default_country);
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 arp_standard.debug('Exception: Default Country to create bank accounts for New MICR Numbers');
1124 g_default_country := 'US';
1125 END;
1126
1127 END ARP_EXT_BANK_PKG;