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