[Home] [Help]
30: IS
31: l_count_int number(8) :=0;
32: l_count_ext number(8) :=0;
33: BEGIN
34: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_end_date_check');
35: cep_standard.debug('branch end_date = ' || Xi_BRANCH_END_DATE);
36: SELECT COUNT(*)
37: INTO l_count_int
38: FROM ce_bank_accounts
31: l_count_int number(8) :=0;
32: l_count_ext number(8) :=0;
33: BEGIN
34: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_end_date_check');
35: cep_standard.debug('branch end_date = ' || Xi_BRANCH_END_DATE);
36: SELECT COUNT(*)
37: INTO l_count_int
38: FROM ce_bank_accounts
39: WHERE NVL(end_date,Xi_BRANCH_END_DATE+1) > Xi_BRANCH_END_DATE
37: INTO l_count_int
38: FROM ce_bank_accounts
39: WHERE NVL(end_date,Xi_BRANCH_END_DATE+1) > Xi_BRANCH_END_DATE
40: AND bank_branch_id = Xi_BRANCH_ID;
41: cep_standard.debug( 'Number of active internal bankaccounts = '||l_count_int);
42: SELECT COUNT(*)
43: INTO l_count_ext
44: FROM iby_ext_bank_accounts
45: WHERE NVL(end_date,Xi_BRANCH_END_DATE+1) > Xi_BRANCH_END_DATE
43: INTO l_count_ext
44: FROM iby_ext_bank_accounts
45: WHERE NVL(end_date,Xi_BRANCH_END_DATE+1) > Xi_BRANCH_END_DATE
46: AND branch_id = Xi_BRANCH_ID;
47: cep_standard.debug( 'Number of active external bankaccounts = '||l_count_ext);
48: IF (l_count_int > 0 OR l_count_ext > 0) THEN
49: fnd_message.set_name('CE', 'CE_BRANCH_END_DATE');
50: FND_MESSAGE.set_token('FIELD1', l_count_int, true);
51: FND_MESSAGE.set_token('FIELD2', l_count_ext, true);
50: FND_MESSAGE.set_token('FIELD1', l_count_int, true);
51: FND_MESSAGE.set_token('FIELD2', l_count_ext, true);
52: fnd_msg_pub.add;
53: END IF;
54: cep_standard.debug('<
56:
57: -- bug 7582842 : Added private function
58: FUNCTION CE_DISABLE_VALIDATION RETURN BOOLEAN
58: FUNCTION CE_DISABLE_VALIDATION RETURN BOOLEAN
59: AS
60: l_disable VARCHAR2(1) := 'N';
61: BEGIN
62: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_disable_validation');
63: l_disable := nvl(FND_PROFILE.value('CE_DISABLE_BANK_VAL'),'N');
64: cep_standard.debug('disable = '||l_disable);
65:
66: IF (l_disable = 'Y') THEN
60: l_disable VARCHAR2(1) := 'N';
61: BEGIN
62: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_disable_validation');
63: l_disable := nvl(FND_PROFILE.value('CE_DISABLE_BANK_VAL'),'N');
64: cep_standard.debug('disable = '||l_disable);
65:
66: IF (l_disable = 'Y') THEN
67: RETURN TRUE;
68: ELSE
99: l_bic varchar2(30) ;
100: l_string varchar2(30) ;
101:
102: BEGIN
103: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bic');
104: -- Initialize message list if p_init_msg_list is set to TRUE.
105: IF FND_API.to_Boolean(p_init_msg_list) THEN
106: FND_MSG_PUB.initialize;
107: END IF;
164: BANK_NUM varchar2(60);
165: bank_count number;
166: BEGIN
167:
168: cep_standard.debug('>>CE_VALIDATE_BANKINFO.compare_bank_and_branch_num');
169:
170:
171: -- Bug 6856840: Added IF clause to handle cases where Bank ID is the Bank Number --
172: IF BANK_ID_IS_NUM = TRUE
173: THEN
174: -- Bank ID is the bank number. No need to fetch from the table --
175: IF ((nvl(Xi_BANK_ID,Xi_BRANCH_NUM) = Xi_BRANCH_NUM))
176: THEN
177: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || ' passed_check');
178: ELSE
179: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' ||
180: 'Bank number and branch number does not match'||
181: 'CE_BANK_BRANCH_NUM_NOT_MATCHED');
175: IF ((nvl(Xi_BANK_ID,Xi_BRANCH_NUM) = Xi_BRANCH_NUM))
176: THEN
177: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || ' passed_check');
178: ELSE
179: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' ||
180: 'Bank number and branch number does not match'||
181: 'CE_BANK_BRANCH_NUM_NOT_MATCHED');
182: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NUM_NOT_MATCHED');
183: fnd_msg_pub.add;
216: THEN
217: BANK_NUM := upper(replace(BANK_NUM,' ',''));
218: BANK_NUM := upper(replace(BANK_NUM,'-',''));
219:
220: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || 'BANK_NUM : ' ||BANK_NUM);
221:
222: IF ((nvl(BANK_NUM,Xi_branch_num) <> Xi_branch_num))
223: THEN
224: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' ||
220: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || 'BANK_NUM : ' ||BANK_NUM);
221:
222: IF ((nvl(BANK_NUM,Xi_branch_num) <> Xi_branch_num))
223: THEN
224: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' ||
225: 'Bank number and branch number does not match'||
226: 'CE_BANK_BRANCH_NUM_NOT_MATCHED');
227: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NUM_NOT_MATCHED');
228: fnd_msg_pub.add;
229: END IF;
230: END IF;
231: ELSIF (bank_count > 1)
232: THEN
233: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' ||'EXCEPTION: More than one bank match ');
234: fnd_message.set_name('CE', 'CE_MANY_BANKS');
235: fnd_msg_pub.add;
236:
237: ELSIF (bank_count = 0)
235: fnd_msg_pub.add;
236:
237: ELSIF (bank_count = 0)
238: THEN
239: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || ' CE_BANK_DOES_NOT_EXISTS');
240: fnd_message.set_name ('CE', 'CE_BANK_DOES_NOT_EXISTS');
241: fnd_msg_pub.add;
242:
243: ELSE
240: fnd_message.set_name ('CE', 'CE_BANK_DOES_NOT_EXISTS');
241: fnd_msg_pub.add;
242:
243: ELSE
244: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || ' passed_check');
245: END IF;
246:
247: cep_standard.debug('<
243: ELSE
244: cep_standard.debug('COMPARE_BANK_AND_BRANCH_NUM: ' || ' passed_check');
245: END IF;
246:
247: cep_standard.debug('<
249:
250: EXCEPTION
251: WHEN OTHERS THEN
248: END IF; /* end of check for BANK_ID_IS_NUM*/
249:
250: EXCEPTION
251: WHEN OTHERS THEN
252: cep_standard.debug('CE_VALIDATE_BANKINFO.COMPARE_BANK_AND_BRANCH_NUM: Exception ' );
253: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
254: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.compare_bank_and_branch_num');
255: fnd_msg_pub.add;
256: RAISE;
276: cd_position number;
277: acct_cd number;
278:
279: BEGIN
280: cep_standard.debug('>>CE_VALIDATE_BANKINFO.COMPARE_ACCOUNT_NUM_AND_CD');
281:
282: cd_position := (length(Xi_account_num) - Xi_CD_pos_from_right);
283: acct_cd := substr(Xi_account_num, cd_position, Xi_CD_length);
284:
281:
282: cd_position := (length(Xi_account_num) - Xi_CD_pos_from_right);
283: acct_cd := substr(Xi_account_num, cd_position, Xi_CD_length);
284:
285: cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'cd_position : '||cd_position ||
286: 'acct_cd : '||acct_cd || 'Xi_CD : '||Xi_CD );
287:
288: IF (acct_cd <> Xi_CD) THEN
289: cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'CD does not match'||
285: cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'cd_position : '||cd_position ||
286: 'acct_cd : '||acct_cd || 'Xi_CD : '||Xi_CD );
287:
288: IF (acct_cd <> Xi_CD) THEN
289: cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'CD does not match'||
290: 'CE_ACCT_NUM_AND_CD_NOT_MATCHED');
291:
292: fnd_message.set_name('CE', 'CE_ACCT_NUM_AND_CD_NOT_MATCHED');
293: fnd_msg_pub.add;
292: fnd_message.set_name('CE', 'CE_ACCT_NUM_AND_CD_NOT_MATCHED');
293: fnd_msg_pub.add;
294: RETURN FALSE;
295: ELSE
296: cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'CD match');
297: RETURN TRUE;
298: END IF;
299:
300: cep_standard.debug('<
296: cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'CD match');
297: RETURN TRUE;
298: END IF;
299:
300: cep_standard.debug('<
302: END COMPARE_ACCOUNT_NUM_AND_CD;
303:
304: /* --------------------------------------------------------------------
328:
329: l_taxid VARCHAR2(30);
330:
331: BEGIN
332: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VAL_UNIQUE_TAX_PAYER_ID');
333:
334: OPEN CHECK_UNIQUE_TAXID_BK;
335: FETCH CHECK_UNIQUE_TAXID_BK INTO l_taxid;
336:
337: IF (CHECK_UNIQUE_TAXID_BK%NOTFOUND)
338: THEN
339: CLOSE CHECK_UNIQUE_TAXID_BK;
340: RETURN('TRUE');
341: cep_standard.debug('CE_VAL_UNIQUE_TAX_PAYER_ID true');
342: ELSE
343: CLOSE CHECK_UNIQUE_TAXID_BK;
344: RETURN('FALSE');
345: cep_standard.debug('CE_VAL_UNIQUE_TAX_PAYER_ID false');
341: cep_standard.debug('CE_VAL_UNIQUE_TAX_PAYER_ID true');
342: ELSE
343: CLOSE CHECK_UNIQUE_TAXID_BK;
344: RETURN('FALSE');
345: cep_standard.debug('CE_VAL_UNIQUE_TAX_PAYER_ID false');
346: END IF;
347:
348: cep_standard.debug('<
344: RETURN('FALSE');
345: cep_standard.debug('CE_VAL_UNIQUE_TAX_PAYER_ID false');
346: END IF;
347:
348: cep_standard.debug('<
350: END CE_VAL_UNIQUE_TAX_PAYER_ID;
351:
352: /* --------------------------------------------------------------------
412: l_taxid_type VARCHAR2(150);
413: l_entity_name VARCHAR2(80);
414:
415: BEGIN
416: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_check_cross_module_tax_id'||
417: 'ce_check_cross_module_tax_id - p_taxpayer_id ' || p_taxpayer_id);
418:
419: -- Checking cross module Banks/Customers
420: OPEN CHECK_CROSS_AR;
419: -- Checking cross module Banks/Customers
420: OPEN CHECK_CROSS_AR;
421: FETCH CHECK_CROSS_AR INTO l_entity_name, l_taxid;
422:
423: cep_standard.debug('ce_check_cross_module_tax_id - l_entity_name ' ||l_entity_name ||
424: 'l_taxid ' ||l_taxid );
425:
426: IF CHECK_CROSS_AR%NOTFOUND THEN
427: p_return_ar:='SUCCESS';
439: p_return_ar:='bk4';
440: END IF;
441: END IF;
442:
443: cep_standard.debug('ce_check_cross_module_tax_id - p_return_ar ' || p_return_ar );
444:
445: CLOSE CHECK_CROSS_AR;
446:
447: -- Checking cross module Banks/Companies
466: ELSIF (l_entity_name=p_entity_name AND (l_taxid<>p_taxpayer_id)) THEN
467: p_return_hr:='bk6';
468: END IF;
469: END IF;
470: cep_standard.debug('ce_check_cross_module_tax_id - p_return_hr ' || p_return_hr );
471: CLOSE CHECK_CROSS_HR;
472:
473: -- Checking cross module Banks/Suppliers
474: OPEN CHECK_CROSS_AP;
488: ELSIF (l_entity_name=p_entity_name AND (l_taxid<>p_taxpayer_id )) THEN
489: p_return_ap:='bk5';
490: END IF;
491: END IF;
492: cep_standard.debug('ce_check_cross_module_tax_id - p_return_ap ' || p_return_ap );
493: CLOSE CHECK_CROSS_AP;
494:
495: p_return_bk:='NA';
496:
493: CLOSE CHECK_CROSS_AP;
494:
495: p_return_bk:='NA';
496:
497: cep_standard.debug('<
499: END ce_check_cross_module_tax_id;
500:
501: /* --------------------------------------------------------------------
519: l_var1 VARCHAR2(20);
520: l_val_digit VARCHAR2(2);
521: l_mod_value NUMBER(2);
522: BEGIN
523: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_TAX_ID_CHECK_ALGORITHM'|| '----------' ||
524: 'CE_VALIDATE_BANKINFO.CE_TAX_ID_CHECK_ALGORITHM ' ||p_COUNTRY );
525:
526: -- Check the Taxpayer ID Valdiation digit for Colombia
527: IF p_country='CO' THEN
541: (TO_NUMBER(SUBSTR(l_var1,3,1))) *59 +
542: (TO_NUMBER(SUBSTR(l_var1,2,1))) *67 +
543: (TO_NUMBER(SUBSTR(l_var1,1,1))) *71),11));
544:
545: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_tax_id_check_algorithm - l_mod_value: '|| l_mod_value);
546:
547: IF (l_mod_value IN (1,0)) THEN
548: l_val_digit:=l_mod_value;
549: ELSE
549: ELSE
550: l_val_digit:=11-l_mod_value;
551: END IF;
552:
553: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_tax_id_check_algorithm - l_val_digit: '|| l_val_digit|| '----------' ||
554: 'CE_VALIDATE_BANKINFO.ce_tax_id_check_algorithm - p_tax_id_cd : '|| p_tax_id_cd );
555:
556: IF l_val_digit<> p_tax_id_cd THEN
557: cep_standard.debug('failed ce_tax_id_check_algorithm' );
553: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_tax_id_check_algorithm - l_val_digit: '|| l_val_digit|| '----------' ||
554: 'CE_VALIDATE_BANKINFO.ce_tax_id_check_algorithm - p_tax_id_cd : '|| p_tax_id_cd );
555:
556: IF l_val_digit<> p_tax_id_cd THEN
557: cep_standard.debug('failed ce_tax_id_check_algorithm' );
558: RETURN('FALSE');
559: ELSE
560: cep_standard.debug('passed ce_tax_id_check_algorithm' );
561: RETURN('TRUE');
556: IF l_val_digit<> p_tax_id_cd THEN
557: cep_standard.debug('failed ce_tax_id_check_algorithm' );
558: RETURN('FALSE');
559: ELSE
560: cep_standard.debug('passed ce_tax_id_check_algorithm' );
561: RETURN('TRUE');
562: END IF;
563: END IF;
564:
561: RETURN('TRUE');
562: END IF;
563: END IF;
564:
565: cep_standard.debug('<
567:
568:
569: /* --------------------------------------------------------------------
584:
585: temp_name number;
586:
587: BEGIN
588: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_UNIQUE_BRANCH_NAME');
589:
590: -- unique combination -> bank_id, branch_name, country --confirmed sql 6/25/02
591: SELECT COUNT(*) INTO temp_name
592: FROM HZ_PARTIES BankParty,
631: AND BranchParty.country = Xi_COUNTRY_NAME */
632: AND BranchOrgProfile.home_country = Xi_COUNTRY_NAME
633: AND BranchParty.PARTY_ID <> nvl(Xi_BRANCH_ID, -1);
634:
635: cep_standard.debug('CE_UNIQUE_BRANCH_NAME - temp_name: ' ||temp_name);
636:
637: IF (nvl(temp_name,0) > 0) THEN
638: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_BANK_BRANCH_NAME_EXISTS');
639: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NAME_EXISTS');
634:
635: cep_standard.debug('CE_UNIQUE_BRANCH_NAME - temp_name: ' ||temp_name);
636:
637: IF (nvl(temp_name,0) > 0) THEN
638: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_BANK_BRANCH_NAME_EXISTS');
639: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NAME_EXISTS');
640: fnd_msg_pub.add;
641: END IF;
642:
639: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NAME_EXISTS');
640: fnd_msg_pub.add;
641: END IF;
642:
643: cep_standard.debug('<
645: END CE_UNIQUE_BRANCH_NAME;
646:
647: /* --------------------------------------------------------------------
661: Xi_BRANCH_ID IN varchar2) AS
662:
663: temp_number number;
664: BEGIN
665: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || '>>CE_VALIDATE_BANKINFO.CE_UNIQUE_BRANCH_NUMBER');
666:
667: -- unique combination -> bank_id, branch_number, country --confirmed sql 6/25/02
668:
669: SELECT COUNT(*) INTO temp_number
709: AND BranchParty.country = Xi_COUNTRY_NAME */
710: AND BranchOrgProfile.home_country = Xi_COUNTRY_NAME
711: AND BranchParty.PARTY_ID <> nvl(Xi_BRANCH_ID, -1);
712:
713: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_UNIQUE_BRANCH_NUMBER - temp_number: ' ||temp_number);
714:
715: IF (nvl(temp_number,0) > 0) THEN
716: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_BANK_BRANCH_NUMBER_EXISTS');
717: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NUMBER_EXISTS');
712:
713: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_UNIQUE_BRANCH_NUMBER - temp_number: ' ||temp_number);
714:
715: IF (nvl(temp_number,0) > 0) THEN
716: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_BANK_BRANCH_NUMBER_EXISTS');
717: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NUMBER_EXISTS');
718: fnd_msg_pub.add;
719: END IF;
720:
717: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NUMBER_EXISTS');
718: fnd_msg_pub.add;
719: END IF;
720:
721: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || '<
723:
724: /* --------------------------------------------------------------------
725: | PRIVATE PROCEDURE |
737: Xi_BANK_ID IN varchar2,
738: Xi_BRANCH_ID IN varchar2) AS
739: temp_name_alt number;
740: BEGIN
741: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_UNIQUE_BRANCH_NAME_ALT');
742: -- unique combination -> bank_id, branch_name_alt, country bug 2363959
743: -- confirmed sql 6/25/02
744:
745: SELECT COUNT(*) INTO temp_name_alt
783: AND BranchParty.ORGANIZATION_NAME_PHONETIC = Xi_BRANCH_NAME_ALT
784: AND BranchOrgProfile.home_country = Xi_COUNTRY_NAME -- bug 8552410 Changed BranchParty to BranchOrgProfile
785: AND BranchParty.PARTY_ID <> nvl(Xi_BRANCH_ID, -1);
786:
787: cep_standard.debug('CE_UNIQUE_BRANCH_NAME_ALT - temp_name_alt: ' ||temp_name_alt);
788:
789: IF (nvl(temp_name_alt,0) > 0) THEN
790: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_BANK_BRANCH_NAME_ALT_EXISTS');
791: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NAME_ALT_EXISTS');
786:
787: cep_standard.debug('CE_UNIQUE_BRANCH_NAME_ALT - temp_name_alt: ' ||temp_name_alt);
788:
789: IF (nvl(temp_name_alt,0) > 0) THEN
790: cep_standard.debug('CE_UNIQUE_BRANCH_NAME: ' || 'CE_BANK_BRANCH_NAME_ALT_EXISTS');
791: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NAME_ALT_EXISTS');
792: fnd_msg_pub.add;
793: END IF;
794:
791: fnd_message.set_name('CE', 'CE_BANK_BRANCH_NAME_ALT_EXISTS');
792: fnd_msg_pub.add;
793: END IF;
794:
795: cep_standard.debug('<
797:
798:
799: /* --------------------------------------------------------------------
815: Xi_ACCOUNT_ID IN varchar2) AS
816:
817: temp_name number;
818: BEGIN
819: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_UNIQUE_ACCOUNT_NAME'||
820: 'Xi_ACCOUNT_NAME ' ||Xi_ACCOUNT_NAME ||
821: ', Xi_BRANCH_ID ' ||Xi_BRANCH_ID );
822:
823: -- unique combination -> bank_branch_id, bank account name --confirmed sql
826: WHERE ba.bank_account_name = Xi_ACCOUNT_NAME
827: AND ba.bank_branch_id = Xi_BRANCH_ID
828: AND ba.bank_account_id <> nvl(Xi_ACCOUNT_ID,-1);
829:
830: cep_standard.debug('CE_UNIQUE_ACCOUNT_NAME: ' || 'temp_name: '||temp_name);
831:
832: IF (nvl(temp_name,0) > 0) THEN
833: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NAME_EXISTS');
834: fnd_msg_pub.add;
831:
832: IF (nvl(temp_name,0) > 0) THEN
833: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NAME_EXISTS');
834: fnd_msg_pub.add;
835: cep_standard.debug('CE_UNIQUE_ACCOUNT_NAME: ' || 'CE_BANK_ACCOUNT_NAME_EXISTS');
836: END IF;
837:
838: cep_standard.debug('<
834: fnd_msg_pub.add;
835: cep_standard.debug('CE_UNIQUE_ACCOUNT_NAME: ' || 'CE_BANK_ACCOUNT_NAME_EXISTS');
836: END IF;
837:
838: cep_standard.debug('<
840: END CE_UNIQUE_ACCOUNT_NAME;
841:
842:
865: COUNTRY_NAME VARCHAR2(2);
866: X_PASS_MAND_CHECK VARCHAR2(1);
867: x_init_count NUMBER; --bug 7460921: added
868: BEGIN
869: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd');
870:
871: -- initialize API return status to success.
872: x_return_status := fnd_api.g_ret_sts_success;
873: COUNTRY_NAME := X_COUNTRY_NAME;
871: -- initialize API return status to success.
872: x_return_status := fnd_api.g_ret_sts_success;
873: COUNTRY_NAME := X_COUNTRY_NAME;
874:
875: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_cd - COUNTRY_NAME: '|| COUNTRY_NAME||
876: 'CE_VALIDATE_BANKINFO.ce_validate_cd - P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
877:
878: -- Initialize message list if p_init_msg_list is set to TRUE.
879: IF FND_API.to_Boolean(p_init_msg_list) THEN
898: ELSE
899: X_PASS_MAND_CHECK := ' ';
900: END IF;
901:
902: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_cd - X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
903:
904: IF X_CD IS NOT NULL THEN -- Bug 6632733 Perform all the validations only if check digit is entered
905:
906: IF (COUNTRY_NAME = 'FR') THEN
975: IF x_msg_count > x_init_count THEN -- bug 7460921
976: x_return_status := fnd_api.g_ret_sts_error;
977: END IF;
978:
979: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_cd - P_COUNT: '|| x_msg_count);
980:
981: cep_standard.debug('<
983: EXCEPTION
977: END IF;
978:
979: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_cd - P_COUNT: '|| x_msg_count);
980:
981: cep_standard.debug('<
983: EXCEPTION
984: WHEN OTHERS THEN
985: cep_standard.debug('Exception: CE_VALIDATE_BANKINFO.ce_validate_cd ' ||X_COUNTRY_NAME );
981: cep_standard.debug('<
983: EXCEPTION
984: WHEN OTHERS THEN
985: cep_standard.debug('Exception: CE_VALIDATE_BANKINFO.ce_validate_cd ' ||X_COUNTRY_NAME );
986:
987: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
988: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_cd');
989: fnd_msg_pub.add;
1029: l_count_before_custom NUMBER; -- 9250566: Added
1030: l_count_after_custom NUMBER; -- 9250566: Added
1031:
1032: BEGIN
1033: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch');
1034:
1035: -- initialize API return status to success.
1036: x_return_status := fnd_api.g_ret_sts_success;
1037:
1037:
1038: COUNTRY_NAME := X_COUNTRY_NAME;
1039: l_value_out := X_BRANCH_NUMBER;
1040:
1041: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
1042: cep_standard.debug('l_value_out: '|| l_value_out);
1043:
1044: -- Initialize message list if p_init_msg_list is set to TRUE.
1045: IF FND_API.to_Boolean(p_init_msg_list) THEN
1038: COUNTRY_NAME := X_COUNTRY_NAME;
1039: l_value_out := X_BRANCH_NUMBER;
1040:
1041: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
1042: cep_standard.debug('l_value_out: '|| l_value_out);
1043:
1044: -- Initialize message list if p_init_msg_list is set to TRUE.
1045: IF FND_API.to_Boolean(p_init_msg_list) THEN
1046: FND_MSG_PUB.initialize;
1052: ELSE
1053: X_PASS_MAND_CHECK := 'P';
1054: END IF;
1055:
1056: cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
1057: cep_standard.debug('Calling CE_VALIDATE_BRANCH_'||COUNTRY_NAME);
1058:
1059: IF (COUNTRY_NAME = 'AT') THEN
1060: CE_VALIDATE_BANKINFO.CE_VALIDATE_BRANCH_AT(
1053: X_PASS_MAND_CHECK := 'P';
1054: END IF;
1055:
1056: cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
1057: cep_standard.debug('Calling CE_VALIDATE_BRANCH_'||COUNTRY_NAME);
1058:
1059: IF (COUNTRY_NAME = 'AT') THEN
1060: CE_VALIDATE_BANKINFO.CE_VALIDATE_BRANCH_AT(
1061: X_BRANCH_NUMBER,
1181: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
1182: l_usr_valid := fnd_api.g_ret_sts_success;
1183:
1184: -- Call to custom validation routines
1185: cep_standard.debug('Calling custom validation hooks');
1186: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1187: cep_standard.debug('l_value_out=' ||l_value_out);
1188: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch(
1189: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1182: l_usr_valid := fnd_api.g_ret_sts_success;
1183:
1184: -- Call to custom validation routines
1185: cep_standard.debug('Calling custom validation hooks');
1186: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1187: cep_standard.debug('l_value_out=' ||l_value_out);
1188: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch(
1189: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1190: Xi_BANK_NUMBER => X_BANK_NUMBER,
1183:
1184: -- Call to custom validation routines
1185: cep_standard.debug('Calling custom validation hooks');
1186: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1187: cep_standard.debug('l_value_out=' ||l_value_out);
1188: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch(
1189: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1190: Xi_BANK_NUMBER => X_BANK_NUMBER,
1191: Xi_BRANCH_NUMBER => l_value_out,
1199: Xo_RETURN_STATUS => l_usr_valid
1200: );
1201:
1202: l_count_after_custom := FND_MSG_PUB.count_msg;
1203: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1204:
1205: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
1206: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
1207:
1201:
1202: l_count_after_custom := FND_MSG_PUB.count_msg;
1203: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1204:
1205: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
1206: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
1207:
1208: -- Bug 14242293: Code change(1/2) starts
1209: -- The uniqueness validation must be a combination of bank, branch name
1207:
1208: -- Bug 14242293: Code change(1/2) starts
1209: -- The uniqueness validation must be a combination of bank, branch name
1210: -- and branch number for all countries.
1211: cep_standard.debug('unqiue VALIDATION CHECK for branch: generic' );
1212: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH(
1213: X_COUNTRY_NAME,
1214: X_VALUE_OUT, /* bug 14610187 - added X_VALUE_OUT instead of X_BRANCH_NUMBER */
1215: X_BRANCH_NAME,
1216: X_BANK_ID,
1217: X_BRANCH_ID);
1218:
1219: -- country specific uniqueness checks --
1220: cep_standard.debug('unqiue VALIDATION CHECK for branch: country specific' );
1221: IF (COUNTRY_NAME = 'JP')
1222: THEN
1223: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH_JP(
1224: X_COUNTRY_NAME,
1227: X_BRANCH_NAME_ALT,
1228: X_BANK_ID,
1229: X_BRANCH_ID);
1230: ELSE
1231: cep_standard.debug('No country uniqueness specific validations');
1232: END IF;
1233: -- end country unique check for branch --
1234: -- Bug 14242293: code change (1/2) ends
1235:
1235:
1236: -- Check return status
1237: IF l_usr_valid = fnd_api.g_ret_sts_error
1238: THEN
1239: cep_standard.debug('Custom validations done - failure');
1240: IF l_count_after_custom = 0 THEN
1241: cep_standard.debug('No custom error message set');
1242: END IF;
1243: ELSE
1237: IF l_usr_valid = fnd_api.g_ret_sts_error
1238: THEN
1239: cep_standard.debug('Custom validations done - failure');
1240: IF l_count_after_custom = 0 THEN
1241: cep_standard.debug('No custom error message set');
1242: END IF;
1243: ELSE
1244: cep_standard.debug('Custom validations done - success');
1245: -- remove any unnecessary messages
1240: IF l_count_after_custom = 0 THEN
1241: cep_standard.debug('No custom error message set');
1242: END IF;
1243: ELSE
1244: cep_standard.debug('Custom validations done - success');
1245: -- remove any unnecessary messages
1246: WHILE l_count_after_custom > l_count_before_custom
1247: LOOP
1248: FND_MSG_PUB.delete_msg(l_count_after_custom);
1246: WHILE l_count_after_custom > l_count_before_custom
1247: LOOP
1248: FND_MSG_PUB.delete_msg(l_count_after_custom);
1249: l_count_after_custom := l_count_after_custom - 1;
1250: cep_standard.debug(l_count_after_custom);
1251: END LOOP;
1252: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
1253:
1254: END IF;
1248: FND_MSG_PUB.delete_msg(l_count_after_custom);
1249: l_count_after_custom := l_count_after_custom - 1;
1250: cep_standard.debug(l_count_after_custom);
1251: END LOOP;
1252: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
1253:
1254: END IF;
1255: -- 9250566 ADDED 1/6 END --------------------------
1256:
1258: p_encoded => FND_API.G_FALSE,
1259: p_count => x_msg_count,
1260: p_data => x_msg_data);
1261:
1262: cep_standard.debug('P_COUNT: '|| x_msg_count);
1263:
1264: IF x_msg_count > 0 THEN
1265: x_return_status := fnd_api.g_ret_sts_error;
1266: END IF;
1264: IF x_msg_count > 0 THEN
1265: x_return_status := fnd_api.g_ret_sts_error;
1266: END IF;
1267:
1268: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
1269: cep_standard.debug('<
1271: EXCEPTION
1272: WHEN OTHERS THEN
1265: x_return_status := fnd_api.g_ret_sts_error;
1266: END IF;
1267:
1268: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
1269: cep_standard.debug('<
1271: EXCEPTION
1272: WHEN OTHERS THEN
1273: cep_standard.debug('EXCEPTION: ce_validate_branch ' ||X_COUNTRY_NAME );
1269: cep_standard.debug('<
1271: EXCEPTION
1272: WHEN OTHERS THEN
1273: cep_standard.debug('EXCEPTION: ce_validate_branch ' ||X_COUNTRY_NAME );
1274: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1275: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_branch');
1276: fnd_msg_pub.add;
1277: RAISE;
1285: ) AS
1286: account_value VARCHAR2(30);
1287: l_bank_account_num VARCHAR2(30);
1288: BEGIN
1289: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM_BE');
1290:
1291: account_value:= ce_remove_formats(X_ACCOUNT_NUMBER);
1292:
1293: IF X_ACCOUNT_CLASSIFICATION = 'EXTERNAL' THEN
1301: ||substr(l_bank_account_num,13,2)),'0'));
1302:
1303: END IF;
1304:
1305: cep_standard.debug('account_value ' ||account_value);
1306: X_ELECTRONIC_ACCT_NUM := account_value;
1307:
1308: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1309: cep_standard.debug('<
1304:
1305: cep_standard.debug('account_value ' ||account_value);
1306: X_ELECTRONIC_ACCT_NUM := account_value;
1307:
1308: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1309: cep_standard.debug('<
1311:
1312:
1305: cep_standard.debug('account_value ' ||account_value);
1306: X_ELECTRONIC_ACCT_NUM := account_value;
1307:
1308: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1309: cep_standard.debug('<
1311:
1312:
1313: -- Bug 11783906
1320: account_value VARCHAR2(30);
1321: account_pad_value VARCHAR2(30);
1322:
1323: BEGIN
1324: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM_FI');
1325:
1326: -- remove formatting characters
1327: account_value := ce_remove_formats(X_Account_Number);
1328: cep_standard.debug('account_value='||account_value);
1324: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM_FI');
1325:
1326: -- remove formatting characters
1327: account_value := ce_remove_formats(X_Account_Number);
1328: cep_standard.debug('account_value='||account_value);
1329:
1330: -- pad as per first digit
1331: IF (SubStr(account_value,1,1) IN ('4','5'))
1332: THEN
1335: ELSE
1336: account_pad_value := SubStr(account_value,1,6) ||
1337: LPad(SubStr(account_value,7,Length(account_value)),8,'0');
1338: END IF;
1339: cep_standard.debug('account_pad_value='||account_pad_value);
1340:
1341: X_ELECTRONIC_ACCT_NUM := account_pad_value;
1342:
1343: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1339: cep_standard.debug('account_pad_value='||account_pad_value);
1340:
1341: X_ELECTRONIC_ACCT_NUM := account_pad_value;
1342:
1343: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1344: cep_standard.debug('<
1346:
1347:
1340:
1341: X_ELECTRONIC_ACCT_NUM := account_pad_value;
1342:
1343: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1344: cep_standard.debug('<
1346:
1347:
1348: /* ----------------------------------------------------------------------- */
1352: X_ELECTRONIC_ACCT_NUM OUT NOCOPY VARCHAR2
1353: ) AS
1354: account_value VARCHAR2(100);
1355: BEGIN
1356: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM_NL');
1357: SELECT decode(
1358: upper(substr(X_ACCOUNT_NUMBER, 1, 1)),
1359: 'P', upper(X_ACCOUNT_NUMBER),
1360: 'G', 'P' || substr(X_ACCOUNT_NUMBER, 2),
1365: FROM dual; -- Bug 13420335
1366:
1367: X_ELECTRONIC_ACCT_NUM := account_value;
1368:
1369: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1370: cep_standard.debug('<
1372:
1373: /* ----------------------------------------------------------------------- */
1366:
1367: X_ELECTRONIC_ACCT_NUM := account_value;
1368:
1369: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1370: cep_standard.debug('<
1372:
1373: /* ----------------------------------------------------------------------- */
1374: PROCEDURE CE_FORMAT_ELECTRONIC_NUM_NO(
1377: X_ELECTRONIC_ACCT_NUM OUT NOCOPY varchar2
1378: ) AS
1379: account_value varchar2(30);
1380: BEGIN
1381: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM_NO');
1382:
1383: account_value:= lpad(replace(replace(NVL(X_ACCOUNT_NUMBER,''),'.',''),' ',''),11,0);
1384: X_ELECTRONIC_ACCT_NUM := account_value;
1385:
1382:
1383: account_value:= lpad(replace(replace(NVL(X_ACCOUNT_NUMBER,''),'.',''),' ',''),11,0);
1384: X_ELECTRONIC_ACCT_NUM := account_value;
1385:
1386: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1387: cep_standard.debug('<
1389:
1390: /* ----------------------------------------------------------------------- */
1383: account_value:= lpad(replace(replace(NVL(X_ACCOUNT_NUMBER,''),'.',''),' ',''),11,0);
1384: X_ELECTRONIC_ACCT_NUM := account_value;
1385:
1386: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1387: cep_standard.debug('<
1389:
1390: /* ----------------------------------------------------------------------- */
1391: PROCEDURE CE_FORMAT_ELECTRONIC_NUM_SE(
1394: X_ELECTRONIC_ACCT_NUM OUT NOCOPY VARCHAR2
1395: ) AS
1396: account_value VARCHAR2(30);
1397: BEGIN
1398: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM_SE');
1399:
1400: account_value:= ce_remove_formats(X_ACCOUNT_NUMBER);
1401: X_ELECTRONIC_ACCT_NUM := account_value;
1402:
1399:
1400: account_value:= ce_remove_formats(X_ACCOUNT_NUMBER);
1401: X_ELECTRONIC_ACCT_NUM := account_value;
1402:
1403: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1404: cep_standard.debug('<
1406:
1407: /* --------------------------------------------------------------------
1400: account_value:= ce_remove_formats(X_ACCOUNT_NUMBER);
1401: X_ELECTRONIC_ACCT_NUM := account_value;
1402:
1403: cep_standard.debug('X_ELECTRONIC_ACCT_NUM ' ||X_ELECTRONIC_ACCT_NUM);
1404: cep_standard.debug('<
1406:
1407: /* --------------------------------------------------------------------
1408: | PUBLIC PROCEDURE |
1430: x_return_status IN OUT NOCOPY VARCHAR2
1431: ) AS
1432: country_name VARCHAR2(2);
1433: BEGIN
1434: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM');
1435: -- initialize API return status to success.
1436: x_return_status := fnd_api.g_ret_sts_success;
1437:
1438: COUNTRY_NAME := X_COUNTRY_NAME;
1437:
1438: COUNTRY_NAME := X_COUNTRY_NAME;
1439: X_ELECTRONIC_ACCT_NUM := X_ACCOUNT_NUMBER;
1440:
1441: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
1442: cep_standard.debug('X_ELECTRONIC_ACCT_NUM: '|| X_ELECTRONIC_ACCT_NUM);
1443: --Bug number 13096191: When country Specific validations are disabled
1444: --formatting is not done
1445: IF CE_DISABLE_VALIDATION THEN
1438: COUNTRY_NAME := X_COUNTRY_NAME;
1439: X_ELECTRONIC_ACCT_NUM := X_ACCOUNT_NUMBER;
1440:
1441: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
1442: cep_standard.debug('X_ELECTRONIC_ACCT_NUM: '|| X_ELECTRONIC_ACCT_NUM);
1443: --Bug number 13096191: When country Specific validations are disabled
1444: --formatting is not done
1445: IF CE_DISABLE_VALIDATION THEN
1446: cep_standard.debug('Validations are Disabled.No formatting done.');
1442: cep_standard.debug('X_ELECTRONIC_ACCT_NUM: '|| X_ELECTRONIC_ACCT_NUM);
1443: --Bug number 13096191: When country Specific validations are disabled
1444: --formatting is not done
1445: IF CE_DISABLE_VALIDATION THEN
1446: cep_standard.debug('Validations are Disabled.No formatting done.');
1447: ELSE
1448: -- Initialize message list if p_init_msg_list is set to TRUE.
1449: IF FND_API.to_Boolean(p_init_msg_list) THEN
1450: FND_MSG_PUB.initialize;
1489: IF x_msg_count > 0 THEN
1490: x_return_status := fnd_api.g_ret_sts_error;
1491: END IF;
1492:
1493: cep_standard.debug('P_COUNT: '|| x_msg_count);
1494: cep_standard.debug('<
1496: WHEN OTHERS THEN
1497: cep_standard.debug('EXCEPTION: CE_FORMAT_ELECTRONIC_NUM ' ||X_COUNTRY_NAME );
1490: x_return_status := fnd_api.g_ret_sts_error;
1491: END IF;
1492:
1493: cep_standard.debug('P_COUNT: '|| x_msg_count);
1494: cep_standard.debug('<
1496: WHEN OTHERS THEN
1497: cep_standard.debug('EXCEPTION: CE_FORMAT_ELECTRONIC_NUM ' ||X_COUNTRY_NAME );
1498: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1493: cep_standard.debug('P_COUNT: '|| x_msg_count);
1494: cep_standard.debug('<
1496: WHEN OTHERS THEN
1497: cep_standard.debug('EXCEPTION: CE_FORMAT_ELECTRONIC_NUM ' ||X_COUNTRY_NAME );
1498: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1499: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.CE_FORMAT_ELECTRONIC_NUM');
1500: fnd_msg_pub.add;
1501: RAISE;
1544: l_count_before_custom NUMBER; -- 9250566: Added
1545: l_count_after_custom NUMBER; -- 9250566: Added
1546:
1547: BEGIN
1548: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account');
1549: -- initialize API return status to success.
1550: x_return_status := fnd_api.g_ret_sts_success;
1551:
1552: COUNTRY_NAME := X_COUNTRY_NAME;
1552: COUNTRY_NAME := X_COUNTRY_NAME;
1553: l_value_out := X_ACCOUNT_NUMBER;
1554: X_ELECTRONIC_ACCT_NUM := X_ACCOUNT_NUMBER;
1555:
1556: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME||', l_value_out: '|| l_value_out);
1557:
1558: -- Initialize message list if p_init_msg_list is set to TRUE.
1559: IF FND_API.to_Boolean(p_init_msg_list) THEN
1560: FND_MSG_PUB.initialize;
1566: ELSE
1567: X_PASS_MAND_CHECK := 'P';
1568: END IF;
1569:
1570: cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
1571:
1572: IF (COUNTRY_NAME = 'AT') THEN
1573: CE_VALIDATE_BANKINFO.CE_VALIDATE_ACCOUNT_AT(
1574: X_ACCOUNT_NUMBER,
1684: X_ACCOUNT_TYPE );
1685: END IF; /* country account check */
1686:
1687: /* UNIQUE VALIDATION CHECK for account */
1688: cep_standard.debug('UNIQUE_VALIDATION CHECK for account');
1689:
1690: IF (COUNTRY_NAME = 'JP') THEN
1691: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT_JP(
1692: X_ACCOUNT_NUMBER,
1705: X_BRANCH_ID,
1706: X_ACCOUNT_ID);
1707:
1708: ELSE
1709: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT' );
1710: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT(
1711: X_ACCOUNT_NUMBER,
1712: X_CURRENCY_CODE,
1713: X_ACCOUNT_NAME,
1715: X_ACCOUNT_ID);
1716:
1717: END IF;
1718:
1719: cep_standard.debug(' UNIQUE_VALIDATION CHECK for account end ');
1720:
1721: -- ER 3973203
1722: -- Format Electronic Bank Account Num
1723: -- (CE_BANK_ACCOUNTS.BANK_ACCOUNT_NUM_ELECTRONIC)
1725: NEW_ACCOUNT_NUM := l_value_out;
1726: ELSE
1727: NEW_ACCOUNT_NUM := X_ACCOUNT_NUMBER;
1728: END IF;
1729: cep_standard.debug('CE_VALIDATE_ACCOUNT: NEW_ACCOUNT_NUM: '|| NEW_ACCOUNT_NUM);
1730:
1731: CE_FORMAT_ELECTRONIC_NUM(
1732: X_COUNTRY_NAME ,
1733: X_BANK_NUMBER ,
1742: x_msg_count ,
1743: x_msg_data ,
1744: x_return_status );
1745:
1746: cep_standard.debug('CE_VALIDATE_ACCOUNT: X_ELECTRONIC_ACCT_NUM: '|| X_ELECTRONIC_ACCT_NUM);
1747:
1748: -- 9250566 ADDED 2/6 START -------------------------
1749: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
1750: l_usr_valid := fnd_api.g_ret_sts_success;
1749: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
1750: l_usr_valid := fnd_api.g_ret_sts_success;
1751:
1752: -- Call to custom validation routines
1753: cep_standard.debug('Calling custom validation hooks');
1754: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1755: cep_standard.debug('l_value_out=' ||l_value_out);
1756: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account(
1757: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1750: l_usr_valid := fnd_api.g_ret_sts_success;
1751:
1752: -- Call to custom validation routines
1753: cep_standard.debug('Calling custom validation hooks');
1754: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1755: cep_standard.debug('l_value_out=' ||l_value_out);
1756: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account(
1757: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1758: Xi_BANK_NUMBER => X_BANK_NUMBER,
1751:
1752: -- Call to custom validation routines
1753: cep_standard.debug('Calling custom validation hooks');
1754: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1755: cep_standard.debug('l_value_out=' ||l_value_out);
1756: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account(
1757: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1758: Xi_BANK_NUMBER => X_BANK_NUMBER,
1759: Xi_BRANCH_NUMBER => X_BRANCH_NUMBER,
1772: Xo_RETURN_STATUS => l_usr_valid
1773: );
1774:
1775: l_count_after_custom := FND_MSG_PUB.count_msg;
1776: cep_standard.debug('l_count_before='||l_count_before_custom);
1777:
1778: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
1779: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
1780:
1774:
1775: l_count_after_custom := FND_MSG_PUB.count_msg;
1776: cep_standard.debug('l_count_before='||l_count_before_custom);
1777:
1778: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
1779: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
1780:
1781: -- Check return status
1782: IF l_usr_valid = fnd_api.g_ret_sts_error
1780:
1781: -- Check return status
1782: IF l_usr_valid = fnd_api.g_ret_sts_error
1783: THEN
1784: cep_standard.debug('Custom validations done - failure');
1785: IF l_count_after_custom = 0 THEN
1786: cep_standard.debug('No custom error message set');
1787: END IF;
1788: ELSE
1782: IF l_usr_valid = fnd_api.g_ret_sts_error
1783: THEN
1784: cep_standard.debug('Custom validations done - failure');
1785: IF l_count_after_custom = 0 THEN
1786: cep_standard.debug('No custom error message set');
1787: END IF;
1788: ELSE
1789: cep_standard.debug('Custom validations done - success');
1790: -- remove any unnecessary messages
1785: IF l_count_after_custom = 0 THEN
1786: cep_standard.debug('No custom error message set');
1787: END IF;
1788: ELSE
1789: cep_standard.debug('Custom validations done - success');
1790: -- remove any unnecessary messages
1791: WHILE l_count_after_custom > l_count_before_custom
1792: LOOP
1793: FND_MSG_PUB.delete_msg(l_count_after_custom);
1791: WHILE l_count_after_custom > l_count_before_custom
1792: LOOP
1793: FND_MSG_PUB.delete_msg(l_count_after_custom);
1794: l_count_after_custom := l_count_after_custom - 1;
1795: cep_standard.debug(l_count_after_custom);
1796: END LOOP;
1797: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
1798:
1799: END IF;
1793: FND_MSG_PUB.delete_msg(l_count_after_custom);
1794: l_count_after_custom := l_count_after_custom - 1;
1795: cep_standard.debug(l_count_after_custom);
1796: END LOOP;
1797: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
1798:
1799: END IF;
1800: -- 9250566 2/6 ADDED END --------------------------
1801:
1806:
1807: IF x_msg_count > 0 THEN
1808: x_return_status := fnd_api.g_ret_sts_error;
1809: END IF;
1810: cep_standard.debug('CE_VALIDATE_ACCOUNT: P_COUNT: '|| x_msg_count);
1811: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
1812:
1813: cep_standard.debug('<
1807: IF x_msg_count > 0 THEN
1808: x_return_status := fnd_api.g_ret_sts_error;
1809: END IF;
1810: cep_standard.debug('CE_VALIDATE_ACCOUNT: P_COUNT: '|| x_msg_count);
1811: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
1812:
1813: cep_standard.debug('<
1815: WHEN OTHERS THEN
1809: END IF;
1810: cep_standard.debug('CE_VALIDATE_ACCOUNT: P_COUNT: '|| x_msg_count);
1811: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
1812:
1813: cep_standard.debug('<
1815: WHEN OTHERS THEN
1816: cep_standard.debug('EXCEPTION: ce_validate_account ' ||X_COUNTRY_NAME );
1817: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1812:
1813: cep_standard.debug('<
1815: WHEN OTHERS THEN
1816: cep_standard.debug('EXCEPTION: ce_validate_account ' ||X_COUNTRY_NAME );
1817: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1818: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.ce_validate_account');
1819: fnd_msg_pub.add;
1820: RAISE;
1854: l_count_before_custom NUMBER; -- 9250566: Added
1855: l_count_after_custom NUMBER; -- 9250566: Added
1856:
1857: BEGIN
1858: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank');
1859: -- initialize API return status to success.
1860: x_return_status := fnd_api.g_ret_sts_success;
1861:
1862: COUNTRY_NAME := X_COUNTRY_NAME;
1861:
1862: COUNTRY_NAME := X_COUNTRY_NAME;
1863: l_value_out := X_BANK_NUMBER;
1864:
1865: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME||
1866: ', l_value_out: '|| l_value_out);
1867:
1868: -- Initialize message list if p_init_msg_list is set to TRUE.
1869: IF FND_API.to_Boolean(p_init_msg_list) THEN
1876: ELSE
1877: X_PASS_MAND_CHECK := 'P';
1878: END IF;
1879:
1880: cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
1881:
1882: IF (COUNTRY_NAME = 'ES') THEN
1883: CE_VALIDATE_BANKINFO.CE_VALIDATE_BANK_ES(
1884: X_BANK_NUMBER,
1972: l_value_out);
1973: END IF; /* country check for bank */
1974:
1975: /* UNIQUE VALIDATION CHECK for bank */
1976: cep_standard.debug('UNIQUE VALIDATION CHECK for bank' );
1977:
1978: IF (COUNTRY_NAME = 'JP') THEN
1979: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP');
1980: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP(
1975: /* UNIQUE VALIDATION CHECK for bank */
1976: cep_standard.debug('UNIQUE VALIDATION CHECK for bank' );
1977:
1978: IF (COUNTRY_NAME = 'JP') THEN
1979: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP');
1980: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP(
1981: X_COUNTRY_NAME,
1982: X_BANK_NUMBER ,
1983: X_BANK_NAME ,
1990: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
1991: l_usr_valid := fnd_api.g_ret_sts_success;
1992:
1993: -- Call to custom validation routines
1994: cep_standard.debug('Calling custom validation hooks');
1995: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1996: cep_standard.debug('l_value_out=' ||l_value_out);
1997: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_bank (
1998: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1991: l_usr_valid := fnd_api.g_ret_sts_success;
1992:
1993: -- Call to custom validation routines
1994: cep_standard.debug('Calling custom validation hooks');
1995: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1996: cep_standard.debug('l_value_out=' ||l_value_out);
1997: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_bank (
1998: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1999: Xi_BANK_NUMBER => l_value_out,
1992:
1993: -- Call to custom validation routines
1994: cep_standard.debug('Calling custom validation hooks');
1995: cep_standard.debug('l_count_before=' ||l_count_before_custom);
1996: cep_standard.debug('l_value_out=' ||l_value_out);
1997: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_bank (
1998: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
1999: Xi_BANK_NUMBER => l_value_out,
2000: Xi_BANK_NAME => X_BANK_NAME,
2004: Xo_BANK_NUM_OUT => l_value_out_custom,
2005: Xo_RETURN_STATUS => l_usr_valid);
2006:
2007: l_count_after_custom := FND_MSG_PUB.count_msg;
2008: cep_standard.debug('l_count_before='||l_count_before_custom);
2009:
2010: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
2011: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
2012: -- Check return status
2006:
2007: l_count_after_custom := FND_MSG_PUB.count_msg;
2008: cep_standard.debug('l_count_before='||l_count_before_custom);
2009:
2010: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
2011: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
2012: -- Check return status
2013: IF l_usr_valid = fnd_api.g_ret_sts_error
2014: THEN
2011: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
2012: -- Check return status
2013: IF l_usr_valid = fnd_api.g_ret_sts_error
2014: THEN
2015: cep_standard.debug('Custom validations done - failure');
2016: IF l_count_after_custom = 0 THEN
2017: cep_standard.debug('No custom error message set');
2018: END IF;
2019: ELSE
2013: IF l_usr_valid = fnd_api.g_ret_sts_error
2014: THEN
2015: cep_standard.debug('Custom validations done - failure');
2016: IF l_count_after_custom = 0 THEN
2017: cep_standard.debug('No custom error message set');
2018: END IF;
2019: ELSE
2020: cep_standard.debug('Custom validations done - success');
2021: -- remove any unnecessary messages
2016: IF l_count_after_custom = 0 THEN
2017: cep_standard.debug('No custom error message set');
2018: END IF;
2019: ELSE
2020: cep_standard.debug('Custom validations done - success');
2021: -- remove any unnecessary messages
2022: WHILE l_count_after_custom > l_count_before_custom
2023: LOOP
2024: FND_MSG_PUB.delete_msg(l_count_after_custom);
2022: WHILE l_count_after_custom > l_count_before_custom
2023: LOOP
2024: FND_MSG_PUB.delete_msg(l_count_after_custom);
2025: l_count_after_custom := l_count_after_custom - 1;
2026: cep_standard.debug(l_count_after_custom);
2027: END LOOP;
2028: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
2029:
2030: END IF;
2024: FND_MSG_PUB.delete_msg(l_count_after_custom);
2025: l_count_after_custom := l_count_after_custom - 1;
2026: cep_standard.debug(l_count_after_custom);
2027: END LOOP;
2028: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
2029:
2030: END IF;
2031: -- 9250566 ADDED 3/6 END --------------------------
2032:
2038: IF X_MSG_COUNT > 0 THEN
2039: x_return_status := fnd_api.g_ret_sts_error;
2040: END IF;
2041:
2042: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_bank - P_COUNT: '||X_MSG_COUNT);
2043: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
2044: cep_standard.debug('<
2046: EXCEPTION
2039: x_return_status := fnd_api.g_ret_sts_error;
2040: END IF;
2041:
2042: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_bank - P_COUNT: '||X_MSG_COUNT);
2043: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
2044: cep_standard.debug('<
2046: EXCEPTION
2047: WHEN OTHERS THEN
2040: END IF;
2041:
2042: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_bank - P_COUNT: '||X_MSG_COUNT);
2043: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
2044: cep_standard.debug('<
2046: EXCEPTION
2047: WHEN OTHERS THEN
2048: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_bank ' ||X_COUNTRY_NAME );
2044: cep_standard.debug('<
2046: EXCEPTION
2047: WHEN OTHERS THEN
2048: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_bank ' ||X_COUNTRY_NAME );
2049: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
2050: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_bank');
2051: fnd_msg_pub.add;
2052: RAISE;
2070: Xi_Field IN VARCHAR2,
2071: Xi_Country IN VARCHAR2
2072: ) AS
2073: BEGIN
2074: cep_standard.debug('CE_VALIDATE_'||xi_field
2075: ||'_'||xi_country||' : passed_check');
2076: END CE_PASSED_CHECK;
2077:
2078: /* --------------------------------------------------------------------
2099: field_name VARCHAR2(100) DEFAULT NULL;
2100: mesg_name VARCHAR2(100) DEFAULT NULL;
2101: length_val VARCHAR2(100) DEFAULT NULL;
2102: BEGIN
2103: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FAILED_CHECK');
2104:
2105: -- Setting the field name as per call --
2106: IF p_Field = 'ACCOUNT_NUM' THEN field_token := 'CE_TOKEN_ACCOUNT_NUM';
2107: ELSIF p_Field = 'ACCOUNT_SUFFIX' THEN field_token := 'CE_TOKEN_ACCOUNT_SUFFIX';
2112: ELSIF p_Field = 'TAX_PAYER_ID' THEN field_token := 'CE_TOKEN_TAX_PAYER_ID';
2113: ELSIF p_Field = 'ROUTE_NUM' THEN field_token := 'CE_TOKEN_BRANCH_NUM_US';
2114: END IF;
2115:
2116: cep_standard.debug('field_token = '||field_token);
2117: -- Get field name from values stored in FND_NEW_MESSAGES --
2118: field_name := fnd_message.get_string('CE',field_token);
2119: cep_standard.debug('field_name = '||field_name);
2120:
2115:
2116: cep_standard.debug('field_token = '||field_token);
2117: -- Get field name from values stored in FND_NEW_MESSAGES --
2118: field_name := fnd_message.get_string('CE',field_token);
2119: cep_standard.debug('field_name = '||field_name);
2120:
2121: -- Setting the error message name as per call --
2122: IF p_Error = 'LENGTH' THEN mesg_name := 'CE_FIELD_INVALID_LEN';
2123: ELSIF p_Error = 'LENGTH_MAX' THEN mesg_name := 'CE_FIELD_INVALID_MAX_LEN';
2129: ELSIF p_Error = 'INVALID_FORMAT' THEN mesg_name := 'CE_FIELD_INVALID_FORMAT';
2130: ELSIF p_Error = 'INVALID_RTN' THEN mesg_name := 'CE_FIELD_INVALID_RTN';
2131: END IF;
2132:
2133: cep_standard.debug('mesg_name = '||mesg_name);
2134: -- set the value for the tokens and add message to FND_MSG_PUB --
2135: FND_MESSAGE.set_name('CE', mesg_name);
2136: FND_MESSAGE.set_token('FIELD', field_name, true);
2137:
2155:
2156: -- populate the message queue
2157: FND_MSG_PUB.add;
2158:
2159: cep_standard.debug('<
2161:
2162: /* --------------------------------------------------------------------
2163: | PRIVATE PROCEDURE |
2180:
2181: mesg_name VARCHAR2(30);
2182:
2183: BEGIN
2184: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_FAILED_MANDATORY');
2185:
2186: IF p_Field = 'BANK_NUM' THEN mesg_name := 'CE_ENTER_BANK_NUM';
2187: ELSIF p_Field = 'BRANCH_NUM' THEN mesg_name := 'CE_ENTER_BRANCH_NUM';
2188: ELSIF p_Field = 'TAX_PAYER_ID' THEN mesg_name := 'CE_ENTER_TAX_PAYER_ID';
2191: ELSIF p_Field = 'ACCOUNT_TYPE' THEN mesg_name := 'CE_ENTER_ACCOUNT_TYPE';
2192: ELSIF p_Field = 'ACCOUNT_SUFFIX' THEN mesg_name := 'CE_ENTER_ACCOUNT_SUFFIX';
2193: END IF;
2194:
2195: cep_standard.debug('mesg_name = '||mesg_name);
2196:
2197: FND_MESSAGE.set_name('CE', mesg_name);
2198: FND_MSG_PUB.add;
2199:
2196:
2197: FND_MESSAGE.set_name('CE', mesg_name);
2198: FND_MSG_PUB.add;
2199:
2200: cep_standard.debug('<
2202:
2203: /* --------------------------------------------------------------------
2204: | PRIVATE PROCEDURE |
2234: ac_value VARCHAR2(30);
2235: br_value VARCHAR2(30);
2236:
2237: BEGIN
2238: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_pt');
2239:
2240: -- 7582842: Disable validations using profile option
2241: IF CE_DISABLE_VALIDATION THEN
2242: cep_standard.debug('validations disabled. no check done');
2238: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_pt');
2239:
2240: -- 7582842: Disable validations using profile option
2241: IF CE_DISABLE_VALIDATION THEN
2242: cep_standard.debug('validations disabled. no check done');
2243: RETURN;
2244: END IF;
2245:
2246: CD_VALUE := upper(replace(Xi_CD,' ',''));
2287: ELSE
2288: ce_failed_check('CHECK_DIGIT','LENGTH','2');
2289: END IF; /* end of length check */
2290:
2291: cep_standard.debug('<
2293:
2294:
2295: /* ---------------------------------------------------------------------
2311: ac_value VARCHAR2(30);
2312: br_value VARCHAR2(30);
2313:
2314: BEGIN
2315: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_es');
2316:
2317: -- 7582842: Disable validations using profile option
2318: IF CE_DISABLE_VALIDATION THEN
2319: cep_standard.debug('validations disabled. no check done');
2315: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_es');
2316:
2317: -- 7582842: Disable validations using profile option
2318: IF CE_DISABLE_VALIDATION THEN
2319: cep_standard.debug('validations disabled. no check done');
2320: RETURN;
2321: END IF;
2322:
2323: CD_VALUE := upper(Xi_CD);
2372: ELSE
2373: ce_failed_check('CHECK_DIGIT','NUMERIC');
2374: END IF; /* end of numeric check */
2375:
2376: cep_standard.debug('<
2378:
2379: /* ---------------------------------------------------------------------
2380: | CD Validation: FRANCE |
2397: br_value varchar2(30);
2398:
2399:
2400: BEGIN
2401: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_fr');
2402:
2403: -- 7582842: Disable validations using profile option
2404: IF CE_DISABLE_VALIDATION THEN
2405: cep_standard.debug('validations disabled. no check done');
2401: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_fr');
2402:
2403: -- 7582842: Disable validations using profile option
2404: IF CE_DISABLE_VALIDATION THEN
2405: cep_standard.debug('validations disabled. no check done');
2406: RETURN;
2407: END IF;
2408:
2409: CD_VALUE := upper(Xi_CD);
2433: ELSE
2434: ce_failed_check('CHECK_DIGIT','LENGTH','2');
2435: END IF; /* end of length check */
2436:
2437: cep_standard.debug('<
2439: END CE_VALIDATE_CD_FR;
2440:
2441: /* ---------------------------------------------------------------------
2450: cd_value VARCHAR2(50);
2451: ac_value VARCHAR2(50);
2452:
2453: BEGIN
2454: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_CD_DE');
2455:
2456: -- 7582842: Disable validations using profile option
2457: IF CE_DISABLE_VALIDATION THEN
2458: cep_standard.debug('validations disabled. no check done');
2454: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_CD_DE');
2455:
2456: -- 7582842: Disable validations using profile option
2457: IF CE_DISABLE_VALIDATION THEN
2458: cep_standard.debug('validations disabled. no check done');
2459: RETURN;
2460: END IF;
2461:
2462: cd_value := upper(Xi_CD);
2480: ce_failed_check('CHECK_DIGIT','LENGTH','1');
2481: END IF; /* end of length check */
2482: END IF;
2483:
2484: cep_standard.debug('<
2486:
2487:
2488: /* ---------------------------------------------------------------------
2500: calc_value NUMBER(30);
2501: cd_value VARCHAR2(20);
2502:
2503: BEGIN
2504: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_gr');
2505:
2506: -- 7582842: Disable validations using profile option
2507: IF CE_DISABLE_VALIDATION THEN
2508: cep_standard.debug('validations disabled. no check done');
2504: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_gr');
2505:
2506: -- 7582842: Disable validations using profile option
2507: IF CE_DISABLE_VALIDATION THEN
2508: cep_standard.debug('validations disabled. no check done');
2509: RETURN;
2510: END IF;
2511:
2512: CD_VALUE := upper(Xi_CD);
2522: ELSE
2523: ce_failed_check('CHECK_DIGIT','LENGTH','1');
2524: END IF; /* end of length check */
2525:
2526: cep_standard.debug('<
2528:
2529: /* ---------------------------------------------------------------------
2530: | CD Validation: ICELAND |
2539: cd_value VARCHAR2(50);
2540: ac_value VARCHAR2(50);
2541:
2542: BEGIN
2543: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_is');
2544:
2545: -- 7582842: Disable validations using profile option
2546: IF CE_DISABLE_VALIDATION THEN
2547: cep_standard.debug('validations disabled. no check done');
2543: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_is');
2544:
2545: -- 7582842: Disable validations using profile option
2546: IF CE_DISABLE_VALIDATION THEN
2547: cep_standard.debug('validations disabled. no check done');
2548: RETURN;
2549: END IF;
2550:
2551: CD_VALUE := upper(replace(Xi_CD,' ',''));
2564: ELSE
2565: ce_failed_check('CHECK_DIGIT','LENGTH','1');
2566: END IF; /* end of length check */
2567:
2568: cep_standard.debug('<
2570:
2571: /* ---------------------------------------------------------------------
2572: | CD Validation: ITALY |
2700: /* MAIN SECTION */
2701: /****************/
2702: BEGIN
2703:
2704: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_it');
2705: cep_standard.debug('Xi_X_ACCOUNT_NUMBER: '||Xi_X_ACCOUNT_NUMBER);
2706:
2707: -- 7582842: Disable validations using profile option
2708: IF CE_DISABLE_VALIDATION THEN
2701: /****************/
2702: BEGIN
2703:
2704: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_it');
2705: cep_standard.debug('Xi_X_ACCOUNT_NUMBER: '||Xi_X_ACCOUNT_NUMBER);
2706:
2707: -- 7582842: Disable validations using profile option
2708: IF CE_DISABLE_VALIDATION THEN
2709: cep_standard.debug('validations disabled. no check done');
2705: cep_standard.debug('Xi_X_ACCOUNT_NUMBER: '||Xi_X_ACCOUNT_NUMBER);
2706:
2707: -- 7582842: Disable validations using profile option
2708: IF CE_DISABLE_VALIDATION THEN
2709: cep_standard.debug('validations disabled. no check done');
2710: RETURN;
2711: END IF;
2712:
2713: CD_VALUE := upper(replace(Xi_CD,' ',''));
2753: ELSE
2754: ce_failed_check('CHECK_DIGIT','LENGTH','1');
2755: END IF; /* end of length check */
2756:
2757: cep_standard.debug('<
2759:
2760: /* ---------------------------------------------------------------------
2761: | CD Validation: LUXEMBOURG |
2770: account_value VARCHAR2(30);
2771: check_digit VARCHAR2(2);
2772:
2773: BEGIN
2774: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_lu');
2775:
2776: -- 7582842: Disable validations using profile option
2777: IF CE_DISABLE_VALIDATION THEN
2778: cep_standard.debug('validations disabled. no check done');
2774: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_lu');
2775:
2776: -- 7582842: Disable validations using profile option
2777: IF CE_DISABLE_VALIDATION THEN
2778: cep_standard.debug('validations disabled. no check done');
2779: RETURN;
2780: END IF;
2781:
2782: ACCOUNT_VALUE := lpad(Xi_X_ACCOUNT_NUMBER,12,0);
2794: ELSE
2795: ce_failed_check('CHECK_DIGIT','LENGTH','2');
2796: END IF; /* end of length check */
2797:
2798: cep_standard.debug('<
2800:
2801: /* ---------------------------------------------------------------------
2802: | CD Validation: SWEDEN |
2809: calc_value number(30);
2810: cd_value varchar2(20);
2811:
2812: BEGIN
2813: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_se');
2814:
2815: -- 7582842: Disable validations using profile option
2816: IF CE_DISABLE_VALIDATION THEN
2817: cep_standard.debug('validations disabled. no check done');
2813: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_se');
2814:
2815: -- 7582842: Disable validations using profile option
2816: IF CE_DISABLE_VALIDATION THEN
2817: cep_standard.debug('validations disabled. no check done');
2818: RETURN;
2819: END IF;
2820:
2821: CD_VALUE := upper(Xi_CD);
2833: ELSE
2834: ce_failed_check('CHECK_DIGIT','LENGTH','1');
2835: END IF; /* end of length check */
2836:
2837: cep_standard.debug('<
2839:
2840: -- 9249372: Added ce_validate_cd_fi
2841: /* ---------------------------------------------------------------------
2857: int_val NUMBER;
2858: weighted_sum NUMBER;
2859: digit NUMBER;
2860: BEGIN
2861: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_fi');
2862:
2863: -- 7582842: Disable validations using profile option
2864: IF CE_DISABLE_VALIDATION THEN
2865: cep_standard.debug('validations disabled. no check done');
2861: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_cd_fi');
2862:
2863: -- 7582842: Disable validations using profile option
2864: IF CE_DISABLE_VALIDATION THEN
2865: cep_standard.debug('validations disabled. no check done');
2866: RETURN;
2867: END IF;
2868:
2869: branch_value := ce_remove_formats(Xi_X_Branch_Number);
2883: check_pad_value := SubStr(check_value,1,6) ||
2884: LPad(SubStr(check_value,7,Length(check_value)),8,'0');
2885: END IF;
2886:
2887: cep_standard.debug('check_pad_value='||check_pad_value);
2888:
2889: -- Luhn Mod 10 Alogrithm
2890: int_val := To_Number(
2891: To_Char(SubStr(check_pad_value,1,1) *2)||
2902: To_Char(SubStr(check_pad_value,12,1)*1)||
2903: To_Char(SubStr(check_pad_value,13,1)*2)||
2904: To_Char(SubStr(check_pad_value,14,1)*1)
2905: );
2906: cep_standard.debug('int_val='||int_val);
2907:
2908: weighted_sum := 0;
2909: WHILE int_val <> 0 LOOP
2910: digit := Mod(int_val,10);
2911: weighted_sum := weighted_sum + digit;
2912: int_val := floor(int_val/10);
2913: END LOOP;
2914:
2915: cep_standard.debug ('weighted_sum='||weighted_sum);
2916:
2917: IF (Mod(weighted_sum,10)=0)
2918: THEN
2919: ce_passed_check('CHECK_DIGIT','FI');
2929: ELSE
2930: ce_failed_check('CHECK_DIGIT','LENGTH','1');
2931: END IF; /* length check */
2932:
2933: cep_standard.debug('<
2935:
2936:
2937:
2965: branch_value VARCHAR2(30);
2966: numeric_result VARCHAR2(40);
2967:
2968: BEGIN
2969: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_at');
2970:
2971: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
2972:
2973: -- 7582842: Disable validations using profile option
2971: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
2972:
2973: -- 7582842: Disable validations using profile option
2974: IF CE_DISABLE_VALIDATION THEN
2975: cep_standard.debug('validations disabled. no check done');
2976: RETURN;
2977: END IF;
2978:
2979: BRANCH_VALUE := upper(Xi_BRANCH_NUMBER);
2997: ce_failed_check('BRANCH_NUM','LENGTH','5');
2998: END IF; /* end of length check */
2999: END IF;
3000:
3001: cep_standard.debug('<
3003:
3004: /* ---------------------------------------------------------------------
3005: | Branch Number Validation: PORTUGAL |
3011: branch_value VARCHAR2(30);
3012: numeric_result VARCHAR2(40);
3013:
3014: BEGIN
3015: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_pt');
3016:
3017: -- 7582842: Disable validations using profile option
3018: IF CE_DISABLE_VALIDATION THEN
3019: cep_standard.debug('validations disabled. no check done');
3015: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_pt');
3016:
3017: -- 7582842: Disable validations using profile option
3018: IF CE_DISABLE_VALIDATION THEN
3019: cep_standard.debug('validations disabled. no check done');
3020: RETURN;
3021: END IF;
3022:
3023: BRANCH_VALUE := upper(Xi_BRANCH_NUMBER);
3042: ce_failed_check('BRANCH_NUM','LENGTH','4');
3043: END IF; /* end of length check */
3044: END IF; /* end of mandatory check */
3045:
3046: cep_standard.debug('<
3048:
3049: /* ---------------------------------------------------------------------
3050: | Branch Number Validation: FRANCE |
3058: branch_value VARCHAR2(30);
3059: numeric_result VARCHAR2(40);
3060:
3061: BEGIN
3062: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_fr');
3063:
3064:
3065: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3066: -- 7582842: Disable validations using profile option
3064:
3065: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3066: -- 7582842: Disable validations using profile option
3067: IF CE_DISABLE_VALIDATION THEN
3068: cep_standard.debug('validations disabled. no check done');
3069: RETURN;
3070: END IF;
3071:
3072: BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
3092: END IF; /* end of numeric check */
3093: END IF; /* end of length check */
3094: END IF; /* end of mandatory check */
3095:
3096: cep_standard.debug('<
3098:
3099: /* ---------------------------------------------------------------------
3100: | Branch Number Validation: SPAIN |
3108: BRANCH_VALUE varchar2(30);
3109: NUMERIC_RESULT varchar2(40);
3110:
3111: BEGIN
3112: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_es');
3113:
3114: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3115: -- 7582842: Disable validations using profile option
3116: IF CE_DISABLE_VALIDATION THEN
3113:
3114: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3115: -- 7582842: Disable validations using profile option
3116: IF CE_DISABLE_VALIDATION THEN
3117: cep_standard.debug('validations disabled. no check done');
3118: RETURN;
3119: END IF;
3120:
3121: BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
3142: END IF; /* end of numeric check */
3143: END IF; /* end of length check */
3144: END IF; /* end of mandatory check */
3145:
3146: cep_standard.debug('<
3148:
3149: /* ---------------------------------------------------------------------
3150: | Branch Number Validation: BRAZIL |
3158: branch_value VARCHAR2(30);
3159: numeric_result VARCHAR2(40);
3160:
3161: BEGIN
3162: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_br');
3163:
3164: -- 6083246: Brazil Branch number is not standardized and there should be
3165: -- no validation for this field.
3166: Xo_VALUE_OUT := Xi_BRANCH_NUMBER ;
3160:
3161: BEGIN
3162: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_br');
3163:
3164: -- 6083246: Brazil Branch number is not standardized and there should be
3165: -- no validation for this field.
3166: Xo_VALUE_OUT := Xi_BRANCH_NUMBER ;
3167: ce_passed_check('BRANCH','BR');
3168:
3171: Xo_VALUE_OUT := BRANCH_VALUE;
3172:
3173: -- 7582842: Disable validations using profile option
3174: IF CE_DISABLE_VALIDATION THEN
3175: cep_standard.debug('validations disabled. no check done');
3176: RETURN;
3177: END IF;
3178:
3179: IF Xi_PASS_MAND_CHECK = 'F'
3200: END IF; -- end of length check
3201: END IF; -- end of mandatory check
3202: ----*/
3203:
3204: cep_standard.debug('<
3206:
3207: /* ---------------------------------------------------------------------
3208: | Branch Number Validation: GERMANY |
3215: branch_num VARCHAR2(30);
3216: numeric_result VARCHAR2(40);
3217:
3218: BEGIN
3219: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_de');
3220:
3221: -- 7582842: Disable validations using profile option
3222: IF CE_DISABLE_VALIDATION THEN
3223: cep_standard.debug('validations disabled. no check done');
3219: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_de');
3220:
3221: -- 7582842: Disable validations using profile option
3222: IF CE_DISABLE_VALIDATION THEN
3223: cep_standard.debug('validations disabled. no check done');
3224: RETURN;
3225: END IF;
3226:
3227: -- remove spaces and hyphens --
3244: ce_failed_check('BRANCH_NUM','LENGTH','8');
3245: END IF; /* end of length check */
3246: END IF;
3247:
3248: cep_standard.debug('<
3250:
3251: /* ---------------------------------------------------------------------
3252: | Branch Number Validation: GREECE |
3258: branch_value VARCHAR2(30);
3259: numeric_result VARCHAR2(40);
3260:
3261: BEGIN
3262: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_gr');
3263:
3264: -- 7582842: Disable validations using profile option
3265: IF CE_DISABLE_VALIDATION THEN
3266: cep_standard.debug('validations disabled. no check done');
3262: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_gr');
3263:
3264: -- 7582842: Disable validations using profile option
3265: IF CE_DISABLE_VALIDATION THEN
3266: cep_standard.debug('validations disabled. no check done');
3267: RETURN;
3268: END IF;
3269:
3270: BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3285: END IF; /* end of numeric check */
3286: END IF; /* end of length check */
3287: END IF; /* end of null check */
3288:
3289: cep_standard.debug('<
3291:
3292: /* ---------------------------------------------------------------------
3293: | Branch Number Validation: ICELAND |
3301: branch_num VARCHAR2(60);
3302: numeric_result VARCHAR2(40);
3303:
3304: BEGIN
3305: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_is');
3306:
3307: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3308: -- 7582842: Disable validations using profile option
3309: IF CE_DISABLE_VALIDATION THEN
3306:
3307: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3308: -- 7582842: Disable validations using profile option
3309: IF CE_DISABLE_VALIDATION THEN
3310: cep_standard.debug('validations disabled. no check done');
3311: RETURN;
3312: END IF;
3313:
3314: -- remove spaces and hyphens --
3335: ELSE
3336: ce_passed_check('BRANCH','IS');
3337: END IF; /* end of null check */
3338:
3339: cep_standard.debug('<
3341:
3342:
3343: /* ---------------------------------------------------------------------
3351: branch_num VARCHAR2(60);
3352: numeric_result VARCHAR2(40);
3353:
3354: BEGIN
3355: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_ie');
3356:
3357: -- 7582842: Disable validations using profile option
3358: IF CE_DISABLE_VALIDATION THEN
3359: cep_standard.debug('validations disabled. no check done');
3355: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_ie');
3356:
3357: -- 7582842: Disable validations using profile option
3358: IF CE_DISABLE_VALIDATION THEN
3359: cep_standard.debug('validations disabled. no check done');
3360: RETURN;
3361: END IF;
3362:
3363: -- remove spaces and hyphens --
3383: ELSE
3384: ce_passed_check('BRANCH','IE');
3385: END IF; /* end of null check */
3386:
3387: cep_standard.debug('<
3389:
3390: /* ---------------------------------------------------------------------
3391: | Branch Number Validation: ITALY |
3398: branch_value VARCHAR2(30);
3399: numeric_result VARCHAR2(40);
3400:
3401: BEGIN
3402: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_it');
3403:
3404: -- 7582842: Disable validations using profile option
3405: IF CE_DISABLE_VALIDATION THEN
3406: cep_standard.debug('validations disabled. no check done');
3402: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_it');
3403:
3404: -- 7582842: Disable validations using profile option
3405: IF CE_DISABLE_VALIDATION THEN
3406: cep_standard.debug('validations disabled. no check done');
3407: RETURN;
3408: END IF;
3409:
3410: -- remove spaces and hyphens --
3429: ce_failed_check('BRANCH_NUM','LENGTH','5');
3430: END IF; /* end of length check */
3431: END IF; /* end of mandatory check */
3432:
3433: cep_standard.debug('<
3435:
3436: /* ---------------------------------------------------------------------
3437: | Branch Number Validation: LUXEMBOURG |
3443: branch_num VARCHAR2(60);
3444: numeric_result VARCHAR2(40);
3445:
3446: BEGIN
3447: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_lu');
3448:
3449: -- 7582842: Disable validations using profile option
3450: IF CE_DISABLE_VALIDATION THEN
3451: cep_standard.debug('validations disabled. no check done');
3447: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_lu');
3448:
3449: -- 7582842: Disable validations using profile option
3450: IF CE_DISABLE_VALIDATION THEN
3451: cep_standard.debug('validations disabled. no check done');
3452: RETURN;
3453: END IF;
3454:
3455: -- remove hyphens and spaces --
3475: ELSE
3476: ce_passed_check('BRANCH','LU');
3477: END IF; /* end of null check */
3478:
3479: cep_standard.debug('<
3481:
3482: /* ---------------------------------------------------------------------
3483: | Branch Number Validation: POLAND |
3491: numeric_result VARCHAR2(40);
3492: cal_cd1 NUMBER;
3493:
3494: BEGIN
3495: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_pl');
3496:
3497: -- 7582842: Disable validations using profile option
3498: IF CE_DISABLE_VALIDATION THEN
3499: cep_standard.debug('validations disabled. no check done');
3495: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_pl');
3496:
3497: -- 7582842: Disable validations using profile option
3498: IF CE_DISABLE_VALIDATION THEN
3499: cep_standard.debug('validations disabled. no check done');
3500: RETURN;
3501: END IF;
3502:
3503: -- remove spaces and hyphens --
3524: ELSE
3525: ce_passed_check('BRANCH','PL');
3526: END IF; /* end of null check */
3527:
3528: cep_standard.debug('<
3530:
3531: /* ---------------------------------------------------------------------
3532: | Branch Number Validation: SWEDEN |
3539: branch_num VARCHAR2(60);
3540: numeric_result VARCHAR2(40);
3541:
3542: BEGIN
3543: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_se');
3544:
3545: -- 7582842: Disable validations using profile option
3546: IF CE_DISABLE_VALIDATION THEN
3547: cep_standard.debug('validations disabled. no check done');
3543: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_se');
3544:
3545: -- 7582842: Disable validations using profile option
3546: IF CE_DISABLE_VALIDATION THEN
3547: cep_standard.debug('validations disabled. no check done');
3548: RETURN;
3549: END IF;
3550:
3551: -- remove hyphens and spaces --
3575: ELSE
3576: ce_passed_check('BRANCH','SE');
3577: END IF; /* end of null check */
3578:
3579: cep_standard.debug('<
3581:
3582:
3583: /* ---------------------------------------------------------------------
3591: branch_num VARCHAR2(60);
3592: numeric_result VARCHAR2(40);
3593:
3594: BEGIN
3595: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_ch');
3596:
3597: -- 7582842: Disable validations using profile option
3598: IF CE_DISABLE_VALIDATION THEN
3599: cep_standard.debug('validations disabled. no check done');
3595: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_ch');
3596:
3597: -- 7582842: Disable validations using profile option
3598: IF CE_DISABLE_VALIDATION THEN
3599: cep_standard.debug('validations disabled. no check done');
3600: RETURN;
3601: END IF;
3602:
3603: -- remove spaces and hyphens --
3631: ELSE
3632: ce_passed_check('BRANCH','CH');
3633: END IF; /* end of null check */
3634:
3635: cep_standard.debug('<
3637:
3638: /* ---------------------------------------------------------------------
3639: | Branch Number Validation: UNITED KINGDOM |
3646: branch_num VARCHAR2(60);
3647: numeric_result VARCHAR2(40);
3648:
3649: BEGIN
3650: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_gb');
3651:
3652: -- 7582842: Disable validations using profile option
3653: IF CE_DISABLE_VALIDATION THEN
3654: cep_standard.debug('validations disabled. no check done');
3650: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_gb');
3651:
3652: -- 7582842: Disable validations using profile option
3653: IF CE_DISABLE_VALIDATION THEN
3654: cep_standard.debug('validations disabled. no check done');
3655: RETURN;
3656: END IF;
3657:
3658: -- remove spaces and hyphens --
3677: ELSE
3678: ce_passed_check('BRANCH','GB');
3679: END IF; /* end of null check */
3680:
3681: cep_standard.debug('<
3683:
3684: /* ---------------------------------------------------------------------
3685: | Branch Number Validation: UNITED STATES |
3698: l_mod_value NUMBER;
3699: numeric_result VARCHAR2(40);
3700:
3701: BEGIN
3702: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_us');
3703:
3704: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3705: -- 7582842: Disable validations using profile option
3706: IF CE_DISABLE_VALIDATION THEN
3703:
3704: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
3705: -- 7582842: Disable validations using profile option
3706: IF CE_DISABLE_VALIDATION THEN
3707: cep_standard.debug('validations disabled. no check done');
3708: RETURN;
3709: END IF;
3710:
3711: -- remove spaces and hyphens --
3762: ELSE
3763: ce_passed_check('BRANCH','US');
3764: END IF; /* end of null check */
3765:
3766: cep_standard.debug('<
3768:
3769: /* ---------------------------------------------------------------------
3770: | Branch Number Validation: AUSTRALIA |
3781: valid_branch_length number;
3782: numeric_result varchar2(40);
3783:
3784: BEGIN
3785: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_au');
3786:
3787: -- 7582842: Disable validations using profile option
3788: IF CE_DISABLE_VALIDATION THEN
3789: cep_standard.debug('validations disabled. no check done');
3785: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_au');
3786:
3787: -- 7582842: Disable validations using profile option
3788: IF CE_DISABLE_VALIDATION THEN
3789: cep_standard.debug('validations disabled. no check done');
3790: RETURN;
3791: END IF;
3792:
3793: -- remove spaces and hyphens --
3832: END IF; /* end of length check */
3833:
3834: END IF; /* end of mandatory check */
3835:
3836: cep_standard.debug('<
3838:
3839: /* ---------------------------------------------------------------------
3840: | Branch Number Validation: ISRAEL |
3847: branch_value VARCHAR2(30);
3848: numeric_result VARCHAR2(40);
3849:
3850: BEGIN
3851: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_il');
3852:
3853: -- 7582842: Disable validations using profile option
3854: IF CE_DISABLE_VALIDATION THEN
3855: cep_standard.debug('validations disabled. no check done');
3851: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_il');
3852:
3853: -- 7582842: Disable validations using profile option
3854: IF CE_DISABLE_VALIDATION THEN
3855: cep_standard.debug('validations disabled. no check done');
3856: RETURN;
3857: END IF;
3858:
3859: BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3878: ce_failed_check('BRANCH_NUM','LENGTH','3');
3879: END IF; /* end of length check */
3880: END IF; /* end of mandatory check */
3881:
3882: cep_standard.debug('<
3884:
3885: /* ---------------------------------------------------------------------
3886: | Branch Number Validation: NEW ZEALAND |
3893: branch_value VARCHAR2(30);
3894: numeric_result VARCHAR2(40);
3895:
3896: BEGIN
3897: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_nz');
3898:
3899: -- 7582842: Disable validations using profile option
3900: IF CE_DISABLE_VALIDATION THEN
3901: cep_standard.debug('validations disabled. no check done');
3897: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_nz');
3898:
3899: -- 7582842: Disable validations using profile option
3900: IF CE_DISABLE_VALIDATION THEN
3901: cep_standard.debug('validations disabled. no check done');
3902: RETURN;
3903: END IF;
3904:
3905: -- remove hyphens and spaces --
3925: ce_failed_check('BRANCH_NUM','LENGTH','4');
3926: END IF; /* end of length check */
3927: END IF; /* end of mandatory check */
3928:
3929: cep_standard.debug('<
3931:
3932: /* ---------------------------------------------------------------------
3933: | Branch Number Validation: JAPAN |
3941: branch_value VARCHAR2(30);
3942: numeric_result VARCHAR2(40);
3943:
3944: BEGIN
3945: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_jp');
3946:
3947: -- 7582842: This validation fires irrespective of disable profile
3948: -- Check that Branch Name Alt has been entered --
3949: IF (Xi_BRANCH_NAME_ALT IS NULL)
3952: END IF;
3953:
3954: -- 7582842: Disable validations using profile option
3955: IF CE_DISABLE_VALIDATION THEN
3956: cep_standard.debug('branch number validations disabled.');
3957: RETURN;
3958: END IF;
3959:
3960: -- remove spaces and hyphens --
3980: ce_failed_check('BRANCH_NUM','LENGTH','3');
3981: END IF; /* end of length check */
3982: END IF; /* end of mandatory check */
3983:
3984: cep_standard.debug('<
3986:
3987: -- Bug 9249372: Added
3988: /* ---------------------------------------------------------------------
3994:
3995: branch_value VARCHAR2(30);
3996: numeric_result VARCHAR2(40);
3997: BEGIN
3998: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_fi');
3999:
4000: -- 7582842: Disable validations using profile option
4001: IF CE_DISABLE_VALIDATION THEN
4002: cep_standard.debug('branch number validations disabled.');
3998: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_fi');
3999:
4000: -- 7582842: Disable validations using profile option
4001: IF CE_DISABLE_VALIDATION THEN
4002: cep_standard.debug('branch number validations disabled.');
4003: RETURN;
4004: END IF;
4005:
4006: -- remove spaces and hyphens --
4022: ce_failed_check('BRANCH_NUM','LENGTH','6');
4023: END IF; /* end of length check */
4024: END IF; /* end of mandatory check */
4025:
4026: cep_standard.debug('<
4028:
4029: /* --------------------------------------------------------------------
4030: | PRIVATE PROCEDURE |
4056: account_value VARCHAR2(30);
4057: numeric_result VARCHAR2(30);
4058:
4059: BEGIN
4060: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_at');
4061:
4062: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER ;
4063: -- 7582842: Disable validations using profile option
4064: IF CE_DISABLE_VALIDATION THEN
4061:
4062: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER ;
4063: -- 7582842: Disable validations using profile option
4064: IF CE_DISABLE_VALIDATION THEN
4065: cep_standard.debug('validations disabled. no check done');
4066: RETURN;
4067: END IF;
4068:
4069: account_value := UPPER(Xi_ACCOUNT_NUMBER);
4082: THEN
4083: ce_failed_check('ACCOUNT_NUM','NUMERIC');
4084: END IF;
4085:
4086: cep_standard.debug('<
4088:
4089: /* ---------------------------------------------------------------------
4090: | Account Number Validation: PORTUGAL |
4096: Xo_VALUE_OUT out nocopy varchar2
4097: ) AS
4098: account_value VARCHAR2(30);
4099: BEGIN
4100: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_pt');
4101:
4102: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4103: -- 7582842: Disable validations using profile option
4104: IF CE_DISABLE_VALIDATION THEN
4101:
4102: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4103: -- 7582842: Disable validations using profile option
4104: IF CE_DISABLE_VALIDATION THEN
4105: cep_standard.debug('validations disabled. no check done');
4106: RETURN;
4107: END IF;
4108:
4109: account_value := UPPER(Xi_ACCOUNT_NUMBER);
4119: THEN
4120: ce_failed_check('ACCOUNT_NUM','NUMERIC');
4121: END IF;
4122:
4123: cep_standard.debug('<
4125:
4126: /* ---------------------------------------------------------------------
4127: | Account Number Validation: BELGIUM |
4139: check_digit VARCHAR2(2);
4140: conced VARCHAR2(30);
4141:
4142: BEGIN
4143: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_be');
4144:
4145: -- 7582842: Disable validations using profile option
4146: IF CE_DISABLE_VALIDATION THEN
4147: cep_standard.debug('validations disabled. no check done');
4143: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_be');
4144:
4145: -- 7582842: Disable validations using profile option
4146: IF CE_DISABLE_VALIDATION THEN
4147: cep_standard.debug('validations disabled. no check done');
4148: RETURN;
4149: END IF;
4150:
4151: -- remove spaces --
4194: ELSE
4195: ce_failed_check('ACCOUNT_NUM','LENGTH','12');
4196: END IF; /* end of length check */
4197:
4198: cep_standard.debug('<
4200:
4201:
4202: /* ---------------------------------------------------------------------
4210: account_value varchar2(30);
4211: numeric_result varchar2(30);
4212:
4213: BEGIN
4214: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_dk');
4215:
4216: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4217: -- 7582842: Disable validations using profile option
4218: IF CE_DISABLE_VALIDATION THEN
4215:
4216: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4217: -- 7582842: Disable validations using profile option
4218: IF CE_DISABLE_VALIDATION THEN
4219: cep_standard.debug('validations disabled. no check done');
4220: RETURN;
4221: END IF;
4222:
4223: account_value := upper(Xi_ACCOUNT_NUMBER );
4235: ELSE
4236: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','10');
4237: END IF; /* end of length check */
4238:
4239: cep_standard.debug('<
4241:
4242: /* ---------------------------------------------------------------------
4243: | Account Number Validation: FRANCE |
4251:
4252: ACCOUNT_VALUE varchar2(30);
4253:
4254: BEGIN
4255: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_fr');
4256:
4257: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4258: -- 7582842: Disable validations using profile option
4259: IF CE_DISABLE_VALIDATION THEN
4256:
4257: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4258: -- 7582842: Disable validations using profile option
4259: IF CE_DISABLE_VALIDATION THEN
4260: cep_standard.debug('validations disabled. no check done');
4261: RETURN;
4262: END IF;
4263:
4264: account_value := UPPER(Xi_ACCOUNT_NUMBER);
4274: THEN
4275: ce_failed_check('ACCOUNT_NUM','ALPHANUM');
4276: END IF;
4277:
4278: cep_standard.debug('<
4280:
4281:
4282: /* ---------------------------------------------------------------------
4313: /**************************/
4314: /* MAIN SECTION */
4315: /**************************/
4316: BEGIN
4317: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_nl');
4318:
4319: -- 7582842: Disable validations using profile option
4320: IF CE_DISABLE_VALIDATION THEN
4321: cep_standard.debug('validations disabled. no check done');
4317: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_nl');
4318:
4319: -- 7582842: Disable validations using profile option
4320: IF CE_DISABLE_VALIDATION THEN
4321: cep_standard.debug('validations disabled. no check done');
4322: RETURN;
4323: END IF;
4324:
4325: multiplied_number := 0;
4344: OR length(ACCOUNT_VALUE) <= 7
4345:
4346: THEN -- 'Giro' and 'Postbank' accounts
4347:
4348: cep_standard.debug('Validating account as Post/Giro...');
4349: -- Remove preceeding format strings
4350: IF (substr(ACCOUNT_VALUE,1,1) = 'P' OR substr(ACCOUNT_VALUE,1,1) = 'G')
4351: THEN
4352: ACCOUNT_VALUE := substr(ACCOUNT_VALUE,2,length(ACCOUNT_VALUE));
4369: ce_failed_check('ACCOUNT_NUM','POST_GIRO');
4370: END IF;
4371:
4372: ELSE /* not a PostGiro account */
4373: cep_standard.debug('Validating account as regular...');
4374: -- check length
4375: IF length(ACCOUNT_VALUE) < 9
4376: THEN
4377: ce_failed_check('ACCOUNT_NUM','LENGTH_MIN','9');
4400: END IF; /* end of length check */
4401:
4402: END IF; /* end of 'Postgiro' check */
4403:
4404: cep_standard.debug('<
4406:
4407: /* ---------------------------------------------------------------------
4408: | Account Number Validation: SPAIN |
4416:
4417: account_value VARCHAR2(30);
4418:
4419: BEGIN
4420: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_es');
4421:
4422: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER; -- 12998039: Prevent auto-uppercase conversion
4423: ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER);
4424:
4423: ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER);
4424:
4425: -- 7582842: Disable validations using profile option
4426: IF CE_DISABLE_VALIDATION THEN
4427: cep_standard.debug('validations disabled. no check done');
4428: RETURN;
4429: END IF;
4430:
4431: -- check for length
4439: THEN
4440: ce_failed_check('ACCOUNT_NUM','NUMERIC');
4441: END IF;
4442:
4443: cep_standard.debug('<
4445:
4446: /* ---------------------------------------------------------------------+
4447: | Account Number Validation: NORWAY |
4457: calc_cd NUMBER;
4458: check_digit VARCHAR2(20);
4459:
4460: BEGIN
4461: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_no');
4462:
4463: -- 7582842: Disable validations using profile option
4464: IF CE_DISABLE_VALIDATION THEN
4465: cep_standard.debug('validations disabled. no check done');
4461: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_no');
4462:
4463: -- 7582842: Disable validations using profile option
4464: IF CE_DISABLE_VALIDATION THEN
4465: cep_standard.debug('validations disabled. no check done');
4466: RETURN;
4467: END IF;
4468:
4469: ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
4515: ELSE
4516: ce_failed_check('ACCOUNT_NUM','LENGTH','11');
4517: END IF; /* end of length check */
4518:
4519: cep_standard.debug('<
4521:
4522: /* ---------------------------------------------------------------------
4523: | Account Number Validation: FINLAND |
4534: weighted_sum NUMBER;
4535: digit NUMBER;
4536:
4537: BEGIN
4538: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_fi');
4539:
4540: -- 7582842: Disable validations using profile option
4541: IF CE_DISABLE_VALIDATION THEN
4542: cep_standard.debug('validations disabled. no check done');
4538: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_fi');
4539:
4540: -- 7582842: Disable validations using profile option
4541: IF CE_DISABLE_VALIDATION THEN
4542: cep_standard.debug('validations disabled. no check done');
4543: RETURN;
4544: END IF;
4545:
4546: -- remove formatting characters
4544: END IF;
4545:
4546: -- remove formatting characters
4547: account_value := ce_remove_formats(Xi_Account_Number);
4548: cep_standard.debug('account_value='||account_value);
4549:
4550: --check the length
4551: IF (length(account_value) < 15)
4552: THEN
4563: ELSE
4564: account_pad_value := SubStr(account_value,1,6) ||
4565: LPad(SubStr(account_value,7,Length(account_value)),8,'0');
4566: END IF;
4567: cep_standard.debug('account_pad_value='||account_pad_value);
4568:
4569: -- Luhn Mod 10 Alogrithm
4570: int_val := To_Number(
4571: To_Char(SubStr(account_pad_value,1,1) *2)||
4582: To_Char(SubStr(account_pad_value,12,1)*1)||
4583: To_Char(SubStr(account_pad_value,13,1)*2)||
4584: To_Char(SubStr(account_pad_value,14,1)*1)
4585: );
4586: cep_standard.debug('int_val='||int_val);
4587: weighted_sum := 0;
4588: WHILE int_val <> 0
4589: LOOP
4590: digit := Mod(int_val,10);
4591: weighted_sum := weighted_sum + digit;
4592: int_val := floor(int_val/10);
4593: END LOOP;
4594:
4595: cep_standard.debug ('weighted_sum='||weighted_sum);
4596:
4597: IF (Mod(weighted_sum,10)=0)
4598: THEN
4599: ce_passed_check('ACCOUNT','FI');
4612: --Bug 14635098: Token parameter changed to 14
4613: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','14');
4614: END IF; -- End Max Length check
4615:
4616: cep_standard.debug('<
4618:
4619: /* ---------------------------------------------------------------------
4620: | Account Number Validation: GERMANY |
4628: account_value VARCHAR2(60);
4629: numeric_result VARCHAR2(40);
4630:
4631: BEGIN
4632: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_ACCOUNT_DE');
4633:
4634: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER; -- 12998039: Prevent auto-uppercase conversion
4635: account_value := upper(Xi_ACCOUNT_NUMBER );
4636:
4635: account_value := upper(Xi_ACCOUNT_NUMBER );
4636:
4637: -- 7582842: Disable validations using profile option
4638: IF CE_DISABLE_VALIDATION THEN
4639: cep_standard.debug('validations disabled. no check done');
4640: RETURN;
4641: END IF;
4642:
4643: -- length check
4651: THEN
4652: ce_failed_check('ACCOUNT_NUM','NUMERIC');
4653: END IF;
4654:
4655: cep_standard.debug('<
4657:
4658: /* ---------------------------------------------------------------------
4659: | Account Number Validation: GREECE |
4666: account_value VARCHAR2(30);
4667: numeric_result VARCHAR2(40);
4668:
4669: BEGIN
4670: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_ACCOUNT_GR');
4671:
4672: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4673: -- 7582842: Disable validations using profile option
4674: IF CE_DISABLE_VALIDATION THEN
4671:
4672: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4673: -- 7582842: Disable validations using profile option
4674: IF CE_DISABLE_VALIDATION THEN
4675: cep_standard.debug('validations disabled. no check done');
4676: RETURN;
4677: END IF;
4678:
4679: -- remove spaces and hyphens --
4691: Xo_VALUE_OUT := lpad(account_value,16,0);
4692: ce_passed_check('ACCOUNT','GR');
4693: END IF; /* end of length check */
4694:
4695: cep_standard.debug('<
4697:
4698: /* ---------------------------------------------------------------------
4699: | Account Number Validation: ICELAND |
4710: ac_cd_value VARCHAR2(50);
4711: numeric_result VARCHAR2(40);
4712:
4713: BEGIN
4714: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_is');
4715:
4716: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4717: -- 7582842: Disable validations using profile option
4718: IF CE_DISABLE_VALIDATION THEN
4715:
4716: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4717: -- 7582842: Disable validations using profile option
4718: IF CE_DISABLE_VALIDATION THEN
4719: cep_standard.debug('validations disabled. no check done');
4720: RETURN;
4721: END IF;
4722:
4723: -- remove spaces and hyphens --
4765: ELSE
4766: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','18');
4767: END IF; /* end of length check */
4768:
4769: cep_standard.debug('<
4771:
4772: /* ---------------------------------------------------------------------
4773: | Account Number Validation: IRELAND |
4779: account_value VARCHAR2(30);
4780: numeric_result VARCHAR2(40);
4781:
4782: BEGIN
4783: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_ie');
4784:
4785: -- 7582842: Disable validations using profile option
4786: IF CE_DISABLE_VALIDATION THEN
4787: cep_standard.debug('validations disabled. no check done');
4783: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_ie');
4784:
4785: -- 7582842: Disable validations using profile option
4786: IF CE_DISABLE_VALIDATION THEN
4787: cep_standard.debug('validations disabled. no check done');
4788: RETURN;
4789: END IF;
4790:
4791: -- remove spaces and hyphens --
4805: ELSE
4806: ce_failed_check('ACCOUNT_NUM','LENGTH','8');
4807: END IF; /* end of length check */
4808:
4809: cep_standard.debug('<
4811:
4812: /* ---------------------------------------------------------------------
4813: | Account Number Validation: ITALY |
4819:
4820: account_value VARCHAR2(50);
4821:
4822: BEGIN
4823: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_it');
4824:
4825: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4826: -- 7582842: Disable validations using profile option
4827: IF CE_DISABLE_VALIDATION THEN
4824:
4825: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
4826: -- 7582842: Disable validations using profile option
4827: IF CE_DISABLE_VALIDATION THEN
4828: cep_standard.debug('validations disabled. no check done');
4829: RETURN;
4830: END IF;
4831:
4832: -- remove hyphens and spaces --
4840: ELSE
4841: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','12');
4842: END IF; /* end of length check */
4843:
4844: cep_standard.debug('<
4846:
4847: /* ---------------------------------------------------------------------
4848: | Account Number Validation: LUXEMBOURG |
4853: account_value varchar2(30);
4854: numeric_result varchar2(40);
4855:
4856: BEGIN
4857: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_lu');
4858:
4859: -- 7582842: Disable validations using profile option
4860: IF CE_DISABLE_VALIDATION THEN
4861: cep_standard.debug('validations disabled. no check done');
4857: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_lu');
4858:
4859: -- 7582842: Disable validations using profile option
4860: IF CE_DISABLE_VALIDATION THEN
4861: cep_standard.debug('validations disabled. no check done');
4862: RETURN;
4863: END IF;
4864:
4865: -- remove spaces and hyphens --
4866: account_value := upper(Xi_ACCOUNT_NUMBER );
4867: account_value := replace(account_value,' ','');
4868: account_value := replace(account_value,'-','');
4869:
4870: -- 6005620: Standardized IBAN structure. Account number: 13an
4871: IF length(account_value) = 13
4872: THEN /* length is ok */
4873: ce_passed_check('ACCOUNT','LU');
4874: ELSE
4875: -- Bug 7570051 : Correct length token
4876: ce_failed_check('ACCOUNT_NUM','LENGTH','13');
4877: END IF; /* end of length check */
4878:
4879: cep_standard.debug('<
4881:
4882: /* ---------------------------------------------------------------------
4883: | Account Number Validation: POLAND |
4888: ) AS
4889: account_value VARCHAR2(30);
4890:
4891: BEGIN
4892: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_pl');
4893:
4894: -- 7582842: Disable validations using profile option
4895: IF CE_DISABLE_VALIDATION THEN
4896: cep_standard.debug('validations disabled. no check done');
4892: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_pl');
4893:
4894: -- 7582842: Disable validations using profile option
4895: IF CE_DISABLE_VALIDATION THEN
4896: cep_standard.debug('validations disabled. no check done');
4897: RETURN;
4898: END IF;
4899:
4900: account_value := UPPER(Xi_ACCOUNT_NUMBER);
4910: THEN
4911: ce_failed_check('ACCOUNT_NUM','NUMERIC');
4912: END IF;
4913:
4914: cep_standard.debug('<
4916:
4917: /* ---------------------------------------------------------------------
4918: | Account Number Validation: SWEDEN |
4923: account_value VARCHAR2(30);
4924: numeric_result VARCHAR2(40);
4925:
4926: BEGIN
4927: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_se');
4928:
4929: -- 7582842: Disable validations using profile option
4930: IF CE_DISABLE_VALIDATION THEN
4931: cep_standard.debug('validations disabled. no check done');
4927: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_se');
4928:
4929: -- 7582842: Disable validations using profile option
4930: IF CE_DISABLE_VALIDATION THEN
4931: cep_standard.debug('validations disabled. no check done');
4932: RETURN;
4933: END IF;
4934:
4935: -- remove spaces and hyphens --
4948: ELSE
4949: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','16'); -- 8246542
4950: END IF; /* end of length check */
4951:
4952: cep_standard.debug('<
4954:
4955: /* ---------------------------------------------------------------------
4956: | Account Number Validation: SWITZERLAND |
4963:
4964: account_value VARCHAR2(30);
4965:
4966: BEGIN
4967: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_ch');
4968:
4969: --8707415 Removed mandatory check for the account type, removed the code
4970: -- 7582842: Disable validations using profile option
4971: IF CE_DISABLE_VALIDATION THEN
4968:
4969: --8707415 Removed mandatory check for the account type, removed the code
4970: -- 7582842: Disable validations using profile option
4971: IF CE_DISABLE_VALIDATION THEN
4972: cep_standard.debug('validations disabled');
4973: RETURN;
4974: END IF;
4975:
4976: account_value := UPPER(Xi_ACCOUNT_NUMBER);
4984: IF NOT regexp_like(account_value,'^[a-zA-Z0-9]*$') THEN
4985: ce_failed_check('ACCOUNT_NUM','ALPHANUM');
4986: END IF;
4987:
4988: cep_standard.debug('<
4990:
4991: /* ---------------------------------------------------------------------
4992: | Account Number Validation: UNITED KINGDOM |
5001: numeric_result VARCHAR2(40);
5002: l_return_status VARCHAR2(1); ---Bug16234978
5003: branch_num VARCHAR2(8); ---Bug16234978
5004: BEGIN
5005: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_gb');
5006:
5007: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
5008: -- 7582842: Disable validations using profile option
5009: IF CE_DISABLE_VALIDATION THEN
5006:
5007: Xo_VALUE_OUT := Xi_ACCOUNT_NUMBER;
5008: -- 7582842: Disable validations using profile option
5009: IF CE_DISABLE_VALIDATION THEN
5010: cep_standard.debug('validations disabled. no check done');
5011: RETURN;
5012: END IF;
5013:
5014: -- initialize API return status to success.
5030: CE_VALIDATE_BANKINFO.ce_validate_mod_gb(Xi_ACCOUNT_NUMBER => account_value,
5031: Xi_SORT_CODE => branch_num,
5032: Xo_RETURN_STATUS => l_return_status);
5033: END IF;
5034: cep_standard.debug('l_return_status = '||l_return_status);
5035: IF l_return_status = fnd_api.g_ret_sts_error THEN
5036: ce_failed_check('ACCOUNT_NUM', 'CD_FAILED');
5037: ELSE
5038: ce_passed_check('ACCOUNT','GB');
5044: ELSE
5045: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','8');
5046: END IF; /* end of length check */
5047:
5048: cep_standard.debug('<
5050:
5051: /* ---------------------------------------------------------------------
5052: | Account Number Validation: BRAZIL |
5058: account_ref VARCHAR2(30);
5059: numeric_result VARCHAR2(40);
5060: l_account_num VARCHAR2(40); -- Bug 16035273
5061: BEGIN
5062: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_br');
5063: -- 7582842: Disable validations using profile option
5064: IF CE_DISABLE_VALIDATION THEN -- Bug 11065718
5065: cep_standard.debug('validations disabled. no check done');
5066: RETURN;
5061: BEGIN
5062: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_br');
5063: -- 7582842: Disable validations using profile option
5064: IF CE_DISABLE_VALIDATION THEN -- Bug 11065718
5065: cep_standard.debug('validations disabled. no check done');
5066: RETURN;
5067: END IF;
5068: --Bug 16035273
5069: --remove spaces and hyphens from account number
5094: END IF;
5095: /* end of length check */
5096: END IF;
5097: /* end of not null check */
5098: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_br');
5099: END CE_VALIDATE_ACCOUNT_BR;
5100:
5101: /* ---------------------------------------------------------------------
5102: | Account Number Validation: AUSTRALIA |
5109: account_value VARCHAR2(30);
5110: numeric_result VARCHAR2(40);
5111:
5112: BEGIN
5113: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_au');
5114:
5115: -- 7582842: Disable validations using profile option
5116: IF CE_DISABLE_VALIDATION THEN
5117: cep_standard.debug('validations disabled. no check done');
5113: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_au');
5114:
5115: -- 7582842: Disable validations using profile option
5116: IF CE_DISABLE_VALIDATION THEN
5117: cep_standard.debug('validations disabled. no check done');
5118: RETURN;
5119: END IF;
5120:
5121: -- remove spaces and hyphens
5146: ce_passed_check('ACCOUNT','AU');
5147: END IF; /* end of currency check */
5148: END IF; /* end of length check */
5149:
5150: cep_standard.debug('<
5152:
5153: /* ---------------------------------------------------------------------
5154: | Account Number Validation: ISRAEL |
5160: account_value VARCHAR2(30);
5161: numeric_result VARCHAR2(40);
5162:
5163: BEGIN
5164: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_il');
5165:
5166: -- 7582842: Disable validations using profile option
5167: IF CE_DISABLE_VALIDATION THEN
5168: cep_standard.debug('validations disabled. no check done');
5164: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_il');
5165:
5166: -- 7582842: Disable validations using profile option
5167: IF CE_DISABLE_VALIDATION THEN
5168: cep_standard.debug('validations disabled. no check done');
5169: RETURN;
5170: END IF;
5171:
5172: account_value := upper(Xi_ACCOUNT_NUMBER );
5185: ELSE
5186: ce_failed_check('ACCOUNT_NUM','LENGTH_MAX','9');
5187: END IF; /* end of length check */
5188:
5189: cep_standard.debug('<
5191:
5192: /* ---------------------------------------------------------------------
5193: | Account Number Validation: NEW ZEALAND |
5201: account_suffix VARCHAR2(30);
5202: numeric_result VARCHAR2(40);
5203:
5204: BEGIN
5205: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_nz');
5206:
5207: -- bug 8416679, relaxed account validations for New Zealand
5208: -- -- check account number
5209: -- account_value := upper(Xi_ACCOUNT_NUMBER );
5249: -- ELSE
5250: -- ce_failed_mandatory('ACCOUNT_SUFFIX');
5251: -- END IF; /* end of mandatory check */
5252: --
5253: cep_standard.debug('<
5255:
5256: /* ---------------------------------------------------------------------
5257: | Account Number Validation: JAPAN |
5261: Xi_ACCOUNT_TYPE in varchar2
5262: ) AS
5263:
5264: BEGIN
5265: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_jp');
5266:
5267: -- 7582842: No Checks on Account number. Disable profile option does not
5268: -- apply here.
5269:
5276: ce_failed_mandatory('ACCOUNT_TYPE');
5277: END if;
5278:
5279:
5280: cep_standard.debug('<
5282:
5283: /* --------------------------------------------------------------------
5284: | PRIVATE PROCEDURE |
5310: bank_value VARCHAR2(30);
5311: numeric_result VARCHAR2(40);
5312:
5313: BEGIN
5314: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_es');
5315:
5316: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5317: -- 7582842: Disable validations using profile option
5318: IF CE_DISABLE_VALIDATION THEN
5315:
5316: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5317: -- 7582842: Disable validations using profile option
5318: IF CE_DISABLE_VALIDATION THEN
5319: cep_standard.debug('validations disabled. no check done');
5320: RETURN;
5321: END IF;
5322:
5323: -- remove spaces and hyphens --
5347: END IF; /* end of numeric check */
5348: END IF; /* end of length check */
5349: END IF; /* end of mandatory check */
5350:
5351: cep_standard.debug('<
5353:
5354: /* ---------------------------------------------------------------------
5355: | Bank Number Validation: FRANCE |
5362: BANK_VALUE varchar2(30);
5363: numeric_result varchar2(40);
5364:
5365: BEGIN
5366: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_fr');
5367:
5368: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5369: -- 7582842: Disable validations using profile option
5370: IF CE_DISABLE_VALIDATION THEN
5367:
5368: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5369: -- 7582842: Disable validations using profile option
5370: IF CE_DISABLE_VALIDATION THEN
5371: cep_standard.debug('validations disabled. no check done');
5372: RETURN;
5373: END IF;
5374:
5375: -- remove spaces and hyphens --
5402: END IF; /* end of numeric check */
5403: END IF; /* end of length check */
5404: END IF; /* end of mandatory check */
5405:
5406: cep_standard.debug('<
5408: END CE_VALIDATE_BANK_FR;
5409:
5410: /* ---------------------------------------------------------------------
5418: bank_value VARCHAR2(30);
5419: numeric_result VARCHAR2(40);
5420:
5421: BEGIN
5422: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_pt');
5423:
5424: -- 7582842: Disable validations using profile option
5425: IF CE_DISABLE_VALIDATION THEN
5426: cep_standard.debug('validations disabled. no check done');
5422: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_pt');
5423:
5424: -- 7582842: Disable validations using profile option
5425: IF CE_DISABLE_VALIDATION THEN
5426: cep_standard.debug('validations disabled. no check done');
5427: RETURN;
5428: END IF;
5429:
5430: -- remove spaces --
5452: END IF; /* end of length check */
5453:
5454: END IF; /* end of mandatory check */
5455:
5456: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_pt');
5457: END CE_VALIDATE_BANK_PT;
5458:
5459: /* ---------------------------------------------------------------------
5460: | Bank Number Validation: BRAZIL |
5468: bank_value VARCHAR2(30);
5469: numeric_result VARCHAR2(40);
5470:
5471: BEGIN
5472: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_br');
5473:
5474: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5475: -- 7582842: Disable validations using profile option
5476: IF CE_DISABLE_VALIDATION THEN
5473:
5474: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5475: -- 7582842: Disable validations using profile option
5476: IF CE_DISABLE_VALIDATION THEN
5477: cep_standard.debug('validations disabled. no check done');
5478: RETURN;
5479: END IF;
5480:
5481: BANK_VALUE := upper(Xi_BANK_NUMBER );
5505: END IF; /* end of numeric check */
5506: END IF; /* end of length check */
5507: END IF; /* end of mandatory check */
5508:
5509: cep_standard.debug('<
5511:
5512: /* ---------------------------------------------------------------------
5513: | Bank Number Validation: GERMANY |
5518: BANK_NUM varchar2(30);
5519: numeric_result varchar2(40);
5520:
5521: BEGIN
5522: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_de');
5523:
5524: -- 7582842: Disable validations using profile option
5525: IF CE_DISABLE_VALIDATION THEN
5526: cep_standard.debug('validations disabled. no check done');
5522: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_de');
5523:
5524: -- 7582842: Disable validations using profile option
5525: IF CE_DISABLE_VALIDATION THEN
5526: cep_standard.debug('validations disabled. no check done');
5527: RETURN;
5528: END IF;
5529:
5530: BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
5546: ce_failed_check('BANK_NUM','LENGTH','8');
5547: END IF; /* end of length check */
5548: END IF;
5549:
5550: cep_standard.debug('<
5552:
5553: /* ---------------------------------------------------------------------
5554: | Bank Number Validation: GREECE |
5560: BANK_VALUE varchar2(30);
5561: numeric_result varchar2(40);
5562:
5563: BEGIN
5564: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_gr');
5565:
5566: -- 7582842: Disable validations using profile option
5567: IF CE_DISABLE_VALIDATION THEN
5568: cep_standard.debug('validations disabled. no check done');
5564: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_gr');
5565:
5566: -- 7582842: Disable validations using profile option
5567: IF CE_DISABLE_VALIDATION THEN
5568: cep_standard.debug('validations disabled. no check done');
5569: RETURN;
5570: END IF;
5571:
5572: -- remove spaces and hyphens before validating --
5589: ce_failed_check('BANK_NUM','LENGTH_MAX', '3');
5590: END IF; /* end of length check */
5591: END IF;
5592:
5593: cep_standard.debug('<
5595:
5596: /* ---------------------------------------------------------------------
5597: | Bank Number Validation: ICELAND |
5604: BANK_NUM varchar2(60);
5605: numeric_result varchar2(40);
5606:
5607: BEGIN
5608: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_is');
5609:
5610: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5611: -- 7582842: Disable validations using profile option
5612: IF CE_DISABLE_VALIDATION THEN
5609:
5610: Xo_VALUE_OUT := Xi_BANK_NUMBER;
5611: -- 7582842: Disable validations using profile option
5612: IF CE_DISABLE_VALIDATION THEN
5613: cep_standard.debug('validations disabled. no check done');
5614: RETURN;
5615: END IF;
5616:
5617: -- remove spaces and hyphens --
5634: ce_failed_check('BANK_NUM','LENGTH_MAX','4');
5635: END IF; /* end of length check */
5636: END IF;
5637:
5638: cep_standard.debug('<
5640:
5641: /* ---------------------------------------------------------------------
5642: | Bank Number Validation: IRELAND |
5648: bank_num VARCHAR2(60);
5649: numeric_result VARCHAR2(40);
5650:
5651: BEGIN
5652: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_ie');
5653:
5654: -- 7582842: Disable validations using profile option
5655: IF CE_DISABLE_VALIDATION THEN
5656: cep_standard.debug('validations disabled. no check done');
5652: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_ie');
5653:
5654: -- 7582842: Disable validations using profile option
5655: IF CE_DISABLE_VALIDATION THEN
5656: cep_standard.debug('validations disabled. no check done');
5657: RETURN;
5658: END IF;
5659:
5660: -- remove spaces and hyphens
5680: END IF; /* end of length check */
5681:
5682: END IF;
5683:
5684: cep_standard.debug('<
5686:
5687: /* ---------------------------------------------------------------------
5688: | Bank Number Validation: ITALY |
5695: bank_value VARCHAR2(30);
5696: numeric_result VARCHAR2(40);
5697:
5698: BEGIN
5699: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_it');
5700:
5701: -- 7582842: Disable validations using profile option
5702: IF CE_DISABLE_VALIDATION THEN
5703: cep_standard.debug('validations disabled. no check done');
5699: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_it');
5700:
5701: -- 7582842: Disable validations using profile option
5702: IF CE_DISABLE_VALIDATION THEN
5703: cep_standard.debug('validations disabled. no check done');
5704: RETURN;
5705: END IF;
5706:
5707: -- remove spaces and hyphens --
5731: END IF; /* end of length check */
5732:
5733: END IF; /* end of mandatory check */
5734:
5735: cep_standard.debug('<
5737:
5738: /* ---------------------------------------------------------------------
5739: | Bank Number Validation: LUXEMBOURG |
5745: bank_num VARCHAR2(60);
5746: numeric_result VARCHAR2(40);
5747:
5748: BEGIN
5749: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_lu');
5750:
5751: -- 7582842: Disable validations using profile option
5752: IF CE_DISABLE_VALIDATION THEN
5753: cep_standard.debug('validations disabled. no check done');
5749: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_lu');
5750:
5751: -- 7582842: Disable validations using profile option
5752: IF CE_DISABLE_VALIDATION THEN
5753: cep_standard.debug('validations disabled. no check done');
5754: RETURN;
5755: END IF;
5756:
5757: -- remove spaces and hyphens --
5760:
5761: IF (BANK_NUM) IS NOT NULL
5762: THEN
5763: /* only validate if a value has been entered */
5764: -- 6005620: IBAN standardized structure. Bank Num: 3n
5765: IF length(BANK_NUM) = 3
5766: THEN
5767: /* length is ok */
5768: numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
5778: END IF; /* end of length check */
5779:
5780: END IF;
5781:
5782: cep_standard.debug('<
5784:
5785: /* ---------------------------------------------------------------------
5786: | Bank Number Validation: POLAND |
5793: numeric_result VARCHAR2(40);
5794: cal_cd1 NUMBER;
5795:
5796: BEGIN
5797: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_pl');
5798:
5799: -- 7582842: Disable validations using profile option
5800: IF CE_DISABLE_VALIDATION THEN
5801: cep_standard.debug('validations disabled. no check done');
5797: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_pl');
5798:
5799: -- 7582842: Disable validations using profile option
5800: IF CE_DISABLE_VALIDATION THEN
5801: cep_standard.debug('validations disabled. no check done');
5802: RETURN;
5803: END IF;
5804:
5805: -- remove spaces and hyphens --
5826: END IF; /* end of length check */
5827:
5828: END IF;
5829:
5830: cep_standard.debug('<
5832:
5833: /* ---------------------------------------------------------------------
5834: | Bank Number Validation: SWEDEN |
5840: bank_num VARCHAR2(60);
5841: numeric_result VARCHAR2(40);
5842:
5843: BEGIN
5844: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_se');
5845:
5846: -- 7582842: Disable validations using profile option
5847: IF CE_DISABLE_VALIDATION THEN
5848: cep_standard.debug('validations disabled. no check done');
5844: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_se');
5845:
5846: -- 7582842: Disable validations using profile option
5847: IF CE_DISABLE_VALIDATION THEN
5848: cep_standard.debug('validations disabled. no check done');
5849: RETURN;
5850: END IF;
5851:
5852: -- remove spaces and hyphens --
5873: END IF; /* end of numeric check */
5874: END IF; /* end of length check */
5875: END IF;
5876:
5877: cep_standard.debug('<
5879:
5880: /* ---------------------------------------------------------------------
5881: | Bank Number Validation: SWITZERLAND |
5887: bank_num VARCHAR2(60);
5888: numeric_result VARCHAR2(40);
5889:
5890: BEGIN
5891: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_ch');
5892:
5893: -- 7582842: Disable validations using profile option
5894: IF CE_DISABLE_VALIDATION THEN
5895: cep_standard.debug('validations disabled. no check done');
5891: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_ch');
5892:
5893: -- 7582842: Disable validations using profile option
5894: IF CE_DISABLE_VALIDATION THEN
5895: cep_standard.debug('validations disabled. no check done');
5896: RETURN;
5897: END IF;
5898:
5899: -- remove spaces and hyphens --
5920: END IF; /* end of numeric check */
5921: END IF; /* end of length check */
5922: END IF;
5923:
5924: cep_standard.debug('<
5926:
5927: /* ---------------------------------------------------------------------
5928: | Bank Number Validation: UNITED KINGDOM |
5934: bank_num VARCHAR2(60);
5935: numeric_result VARCHAR2(40);
5936:
5937: BEGIN
5938: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_gb');
5939:
5940: -- 7582842: Disable validations using profile option
5941: IF CE_DISABLE_VALIDATION THEN
5942: cep_standard.debug('validations disabled. no check done');
5938: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_gb');
5939:
5940: -- 7582842: Disable validations using profile option
5941: IF CE_DISABLE_VALIDATION THEN
5942: cep_standard.debug('validations disabled. no check done');
5943: RETURN;
5944: END IF;
5945:
5946: -- remove spaces and hyphens --
5964: ce_failed_check('BANK_NUM','LENGTH','6');
5965: END IF; /* end of length check */
5966: END IF;
5967:
5968: cep_standard.debug('<
5970:
5971: /* ---------------------------------------------------------------------
5972: | Bank Number Validation: COLUMBIA |
5990: l_cust VARCHAR(10);
5991: l_bank VARCHAR(10);
5992:
5993: BEGIN
5994: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_BANK_CO');
5995:
5996: -- 7582842: No validations on bank number. Disable profile will not apply
5997: -- here.
5998:
6026: l_comp,
6027: l_bank
6028: );
6029:
6030: cep_standard.debug('l_cust : '|| l_cust );
6031: cep_standard.debug('l_supp : '|| l_supp );
6032: cep_standard.debug('l_comp : '|| l_comp );
6033: cep_standard.debug('l_bank : '|| l_bank );
6034:
6027: l_bank
6028: );
6029:
6030: cep_standard.debug('l_cust : '|| l_cust );
6031: cep_standard.debug('l_supp : '|| l_supp );
6032: cep_standard.debug('l_comp : '|| l_comp );
6033: cep_standard.debug('l_bank : '|| l_bank );
6034:
6035: IF (l_supp = 'bk3' OR l_cust= 'bk2' OR l_comp = 'bk1')
6028: );
6029:
6030: cep_standard.debug('l_cust : '|| l_cust );
6031: cep_standard.debug('l_supp : '|| l_supp );
6032: cep_standard.debug('l_comp : '|| l_comp );
6033: cep_standard.debug('l_bank : '|| l_bank );
6034:
6035: IF (l_supp = 'bk3' OR l_cust= 'bk2' OR l_comp = 'bk1')
6036: THEN
6029:
6030: cep_standard.debug('l_cust : '|| l_cust );
6031: cep_standard.debug('l_supp : '|| l_supp );
6032: cep_standard.debug('l_comp : '|| l_comp );
6033: cep_standard.debug('l_bank : '|| l_bank );
6034:
6035: IF (l_supp = 'bk3' OR l_cust= 'bk2' OR l_comp = 'bk1')
6036: THEN
6037: FND_MESSAGE.SET_NAME('CE','CE_TAXID_EXIST');
6068: ELSE
6069: ce_failed_mandatory('TAX_PAYER_ID');
6070: END IF; /* end of mandatory check */
6071:
6072: cep_standard.debug('<
6074:
6075: /* ---------------------------------------------------------------------
6076: | Bank Number Validation: AUSTRALIA |
6081: BANK_NUM varchar2(60);
6082: numeric_result varchar2(40);
6083:
6084: BEGIN
6085: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_au');
6086:
6087: -- 7582842: Disable validations using profile option
6088: IF CE_DISABLE_VALIDATION THEN
6089: cep_standard.debug('validations disabled. no check done');
6085: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_au');
6086:
6087: -- 7582842: Disable validations using profile option
6088: IF CE_DISABLE_VALIDATION THEN
6089: cep_standard.debug('validations disabled. no check done');
6090: RETURN;
6091: END IF;
6092:
6093: -- Remove blanks and hyphens before validation --
6116: ELSE
6117: ce_passed_check('BANK','AU');
6118: END IF;
6119:
6120: cep_standard.debug('<
6122:
6123: /* ---------------------------------------------------------------------
6124: | Bank Number Validation: ISRAEL |
6131: bank_value VARCHAR2(30);
6132: numeric_result VARCHAR2(40);
6133:
6134: BEGIN
6135: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_il');
6136:
6137: -- 7582842: Disable validations using profile option
6138: IF CE_DISABLE_VALIDATION THEN
6139: cep_standard.debug('validations disabled. no check done');
6135: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_il');
6136:
6137: -- 7582842: Disable validations using profile option
6138: IF CE_DISABLE_VALIDATION THEN
6139: cep_standard.debug('validations disabled. no check done');
6140: RETURN;
6141: END IF;
6142:
6143: -- remove spaces and hyphens --
6168: END IF; /* end of length check */
6169:
6170: END IF; /* end of mandatory check */
6171:
6172: cep_standard.debug('<
6174:
6175: /* ---------------------------------------------------------------------
6176: | Bank Number Validation: NEW ZEALAND |
6183: bank_value VARCHAR2(30);
6184: numeric_result VARCHAR2(40);
6185:
6186: BEGIN
6187: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_nz');
6188:
6189: -- 7582842: Disable validations using profile option
6190: IF CE_DISABLE_VALIDATION THEN
6191: cep_standard.debug('validations disabled. no check done');
6187: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_nz');
6188:
6189: -- 7582842: Disable validations using profile option
6190: IF CE_DISABLE_VALIDATION THEN
6191: cep_standard.debug('validations disabled. no check done');
6192: RETURN;
6193: END IF;
6194:
6195: -- remove spaces and hyphens --
6219: END IF; /* end of length check */
6220:
6221: END IF; /* end of mandatory check */
6222:
6223: cep_standard.debug('<
6225:
6226: /* ---------------------------------------------------------------------
6227: | Bank Number Validation: JAPAN |
6235: bank_value VARCHAR2(30);
6236: numeric_result VARCHAR2(40);
6237:
6238: BEGIN
6239: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_jp');
6240:
6241: -- check that BANK_NAME_ALT is also entered
6242: -- 7582842: This check is done irrespective of the disable profile option
6243: IF (Xi_BANK_NAME_ALT is null) THEN
6245: END IF;
6246:
6247: -- 7582842: Disable validations using profile option
6248: IF CE_DISABLE_VALIDATION THEN
6249: cep_standard.debug('validations disabled. no check done');
6250: RETURN;
6251: END IF;
6252:
6253: -- remove spaces and hyphens --
6278: END IF; /* end of length check */
6279:
6280: END IF; /* end of mandatory check for bank num */
6281:
6282: cep_standard.debug('<
6284:
6285: -- 8266356: Added procedure
6286: /* ---------------------------------------------------------------------
6295: bank_value VARCHAR2(30);
6296: numeric_result VARCHAR2(40);
6297:
6298: BEGIN
6299: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_at');
6300:
6301: Xo_VALUE_OUT := Xi_BANK_NUMBER;
6302: -- 7582842: Disable validations using profile option
6303: IF CE_DISABLE_VALIDATION THEN
6300:
6301: Xo_VALUE_OUT := Xi_BANK_NUMBER;
6302: -- 7582842: Disable validations using profile option
6303: IF CE_DISABLE_VALIDATION THEN
6304: cep_standard.debug('validations disabled. no check done');
6305: RETURN;
6306: END IF;
6307:
6308: -- 8266356: Bank Number is not mandatory.
6326: ce_failed_check('BANK_NUM','LENGTH','5');
6327: END IF; /* end of length check */
6328: END IF;
6329:
6330: cep_standard.debug('<
6332:
6333: /* --------------------------------------------------------------------
6334: | PRIVATE PROCEDURE |
6350: Xi_ACCOUNT_ID in NUMBER
6351: ) AS
6352: temp_number number;
6353: BEGIN
6354: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT');
6355:
6356: -- unique combination -> bank_branch_id, bank account number, currency code
6357: -- due to upgrade changes, the unique combination was changed to:
6358: -- bank_branch_id, bank account number, currency code, and account name
6363: AND ba.bank_branch_id = Xi_BRANCH_ID
6364: AND ba.currency_code = Xi_CURRENCY_CODE
6365: AND ba.bank_account_id <> nvl(Xi_ACCOUNT_ID,-1);
6366:
6367: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'temp_number: '||temp_number);
6368:
6369: IF (nvl(temp_number,0) > 0) THEN
6370: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'CE_BANK_ACCOUNT_NUM_EXISTS');
6371: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NUM_EXISTS');
6366:
6367: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'temp_number: '||temp_number);
6368:
6369: IF (nvl(temp_number,0) > 0) THEN
6370: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'CE_BANK_ACCOUNT_NUM_EXISTS');
6371: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NUM_EXISTS');
6372: fnd_msg_pub.add;
6373: END IF;
6374:
6373: END IF;
6374:
6375: -- unique combination -> bank_branch_id, bank account name
6376: -- Bug 7836516 removed this check
6377: cep_standard.debug('<
6379: END CE_VALIDATE_UNIQUE_ACCOUNT;
6380:
6381: /* -------------------------------------------------------------------- */
6389: Xi_ACCOUNT_ID in NUMBER
6390: )AS
6391: temp_number NUMBER;
6392: BEGIN
6393: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT_JP');
6394:
6395: -- unique combination -> bank_branch_id, bank account number, currency code, account type
6396: -- due to upgrade changes account name was added to the unique combination
6397: SELECT count(*) INTO temp_number
6402: AND ba.currency_code = Xi_CURRENCY_CODE
6403: AND ba.bank_branch_id = Xi_BRANCH_ID
6404: AND ba.bank_account_id <> nvl(Xi_ACCOUNT_ID,-1);
6405:
6406: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'temp_number: '||temp_number);
6407:
6408: IF (nvl(temp_number,0) > 0) THEN
6409: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NUM_EXISTS');
6410: fnd_msg_pub.add;
6407:
6408: IF (nvl(temp_number,0) > 0) THEN
6409: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NUM_EXISTS');
6410: fnd_msg_pub.add;
6411: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'CE_BANK_ACCOUNT_NUM_EXISTS');
6412: END IF;
6413:
6414: -- unique combination -> bank_branch_id, bank account name
6415: -- Bug 7836516 removed this check
6413:
6414: -- unique combination -> bank_branch_id, bank account name
6415: -- Bug 7836516 removed this check
6416:
6417: cep_standard.debug('<
6419: END CE_VALIDATE_UNIQUE_ACCOUNT_JP;
6420:
6421: /* -------------------------------------------------------------------- */
6430:
6431: temp_number NUMBER;
6432:
6433: BEGIN
6434: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT_NZ');
6435:
6436: -- unique combination -> bank_branch_id, bank account number, currency code, account suffix
6437: -- due to upgrade changes account name was added to the unique combination
6438: SELECT COUNT(*) INTO temp_number
6443: AND ba.currency_code = Xi_CURRENCY_CODE
6444: AND ba.bank_branch_id = Xi_BRANCH_ID
6445: AND ba.bank_account_id <> nvl(Xi_ACCOUNT_ID,-1);
6446:
6447: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'temp_number: ' || temp_number);
6448:
6449: IF (nvl(temp_number,0) > 0) THEN
6450: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'CE_BANK_ACCOUNT_NUM_EXISTS');
6451: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NUM_EXISTS');
6446:
6447: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'temp_number: ' || temp_number);
6448:
6449: IF (nvl(temp_number,0) > 0) THEN
6450: cep_standard.debug('CE_VALIDATE_UNIQUE_ACCOUNT: ' || 'CE_BANK_ACCOUNT_NUM_EXISTS');
6451: fnd_message.set_name('CE', 'CE_BANK_ACCOUNT_NUM_EXISTS');
6452: fnd_msg_pub.add;
6453: END IF;
6454:
6454:
6455: -- unique combination -> bank_branch_id, bank account name
6456: -- Bug 7836516 removed this check
6457:
6458: cep_standard.debug('<
6460:
6461:
6462: /* --------------------------------------------------------------------
6479: Xi_BRANCH_ID in number
6480: ) AS
6481: l_temp_number NUMBER;
6482: BEGIN
6483: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH');
6484: SELECT COUNT(*)
6485: INTO l_temp_number
6486: FROM HZ_PARTIES BankParty,
6487: HZ_PARTIES BranchParty,
6524: AND nvl(BranchOrgProfile.BANK_OR_BRANCH_NUMBER,'X') = nvl(Xi_BRANCH_NUMBER,'X')
6525: AND BranchOrgProfile.HOME_COUNTRY = Xi_COUNTRY_NAME
6526: AND BranchParty.PARTY_ID <> nvl(Xi_BRANCH_ID, -1);
6527:
6528: cep_standard.debug('already existing branches='||l_temp_number);
6529:
6530: IF (nvl(l_temp_number,0) > 0) THEN
6531: cep_standard.debug('Branch already exists');
6532: fnd_message.set_name('CE', 'CE_BANK_BRANCH_EXISTS');
6527:
6528: cep_standard.debug('already existing branches='||l_temp_number);
6529:
6530: IF (nvl(l_temp_number,0) > 0) THEN
6531: cep_standard.debug('Branch already exists');
6532: fnd_message.set_name('CE', 'CE_BANK_BRANCH_EXISTS');
6533: fnd_msg_pub.add;
6534: END IF;
6535:
6533: fnd_msg_pub.add;
6534: END IF;
6535:
6536:
6537: cep_standard.debug('<
6539:
6540: PROCEDURE CE_VALIDATE_UNIQUE_BRANCH_JP(
6541: Xi_COUNTRY_NAME IN VARCHAR2,
6545: Xi_BANK_ID IN NUMBER,
6546: Xi_BRANCH_ID IN NUMBER
6547: ) AS
6548: BEGIN
6549: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH_JP');
6550: -- unique combination -> bank_id, branch_name_alt, country bug 2363959
6551: CE_VALIDATE_BANKINFO.CE_UNIQUE_BRANCH_NAME_ALT(
6552: Xi_COUNTRY_NAME,
6553: Xi_BRANCH_NAME_ALT,
6553: Xi_BRANCH_NAME_ALT,
6554: Xi_BANK_ID,
6555: Xi_BRANCH_ID);
6556:
6557: cep_standard.debug('<
6559:
6560: PROCEDURE CE_VALIDATE_UNIQUE_BRANCH_DE(
6561: Xi_BRANCH_NUMBER in varchar2,
6563: Xi_BANK_ID in number,
6564: Xi_BRANCH_ID in number
6565: ) AS
6566: BEGIN
6567: cep_standard.debug('CE_VALIDATE_UNIQUE_BRANCH_DE no specific unique requirement');
6568: END CE_VALIDATE_UNIQUE_BRANCH_DE;
6569:
6570: PROCEDURE CE_VALIDATE_UNIQUE_BRANCH_US(
6571: Xi_BRANCH_NUMBER in varchar2,
6573: Xi_BANK_ID in number,
6574: Xi_BRANCH_ID in number
6575: ) AS
6576: BEGIN
6577: cep_standard.debug('CE_VALIDATE_UNIQUE_BRANCH_US no specific unique requirement');
6578: END CE_VALIDATE_UNIQUE_BRANCH_US;
6579: /* ------------------------------------------------------------------------ */
6580: -- Bug 14242293: Code change(2/2) ends
6581:
6600: temp_number number;
6601: temp_name number;
6602: temp_name_alt number;
6603: BEGIN
6604: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP');
6605: -- unique combination -> bank_name_alt, country bug 2363959 --confirmed sql 6/28/02
6606: SELECT COUNT(*)
6607: INTO temp_name_alt
6608: FROM HZ_PARTIES HzPartyBankEO,
6621: AND HzCodeAssignmentBankEO.OWNER_TABLE_ID = HzPartyBankEO.PARTY_ID
6622: AND NVL(HzCodeAssignmentBankEO.status, 'A') = 'A'
6623: AND HzPartyBankEO.PARTY_ID <> NVL(Xi_BANK_ID, -1); -- Bug 8552410: Changed = to <>
6624:
6625: cep_standard.debug('CE_VALIDATE_UNIQUE_BANK_JP - temp_name_alt: ' ||temp_name_alt);
6626:
6627: IF (nvl(temp_name_alt,0) > 0) THEN
6628: cep_standard.debug('CE_VALIDATE_UNIQUE_BANK_JP: ' || 'CE_BANK_NAME_ALT_EXISTS');
6629: fnd_message.set_name('CE', 'CE_BANK_NAME_ALT_EXISTS');
6624:
6625: cep_standard.debug('CE_VALIDATE_UNIQUE_BANK_JP - temp_name_alt: ' ||temp_name_alt);
6626:
6627: IF (nvl(temp_name_alt,0) > 0) THEN
6628: cep_standard.debug('CE_VALIDATE_UNIQUE_BANK_JP: ' || 'CE_BANK_NAME_ALT_EXISTS');
6629: fnd_message.set_name('CE', 'CE_BANK_NAME_ALT_EXISTS');
6630: fnd_msg_pub.add;
6631: END IF;
6632:
6629: fnd_message.set_name('CE', 'CE_BANK_NAME_ALT_EXISTS');
6630: fnd_msg_pub.add;
6631: END IF;
6632:
6633: cep_standard.debug('<
6635:
6636: -- added 10/25/04
6637: /* --------------------------------------------------------------------
6653: procedure fail_mandatory is
6654: begin
6655: fnd_message.set_name ('CE', 'CE_ENTER_EFT_NUMBER');
6656: fnd_msg_pub.add;
6657: cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'CE_ENTER_EFT_NUMBER');
6658: end fail_mandatory;
6659:
6660: procedure fail_check is
6661: begin
6660: procedure fail_check is
6661: begin
6662: fnd_message.set_name ('CE', 'CE_INVALID_EFT_NUMBER');
6663: fnd_msg_pub.add;
6664: cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'CE_INVALID_EFT_NUMBER');
6665: end fail_check;
6666:
6667: procedure pass_check is
6668: begin
6665: end fail_check;
6666:
6667: procedure pass_check is
6668: begin
6669: cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'pass_check');
6670: end pass_check;
6671: BEGIN
6672: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM');
6673:
6668: begin
6669: cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'pass_check');
6670: end pass_check;
6671: BEGIN
6672: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM');
6673:
6674: -- initialize API return status to success.
6675: x_return_status := fnd_api.g_ret_sts_success;
6676:
6674: -- initialize API return status to success.
6675: x_return_status := fnd_api.g_ret_sts_success;
6676:
6677: COUNTRY_NAME := X_COUNTRY_NAME;
6678: cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM - COUNTRY_NAME: '|| COUNTRY_NAME);
6679:
6680: -- Initialize message list if p_init_msg_list is set to TRUE.
6681: IF FND_API.to_Boolean(p_init_msg_list) THEN
6682: FND_MSG_PUB.initialize;
6713: IF x_msg_count > 0 THEN
6714: x_return_status := fnd_api.g_ret_sts_error;
6715: END IF;
6716:
6717: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_misc_eft_num - P_COUNT: '|| x_msg_count);
6718: cep_standard.debug('<
6720: EXCEPTION
6721: WHEN OTHERS THEN
6714: x_return_status := fnd_api.g_ret_sts_error;
6715: END IF;
6716:
6717: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_misc_eft_num - P_COUNT: '|| x_msg_count);
6718: cep_standard.debug('<
6720: EXCEPTION
6721: WHEN OTHERS THEN
6722: cep_standard.debug('CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM ' ||X_COUNTRY_NAME );
6718: cep_standard.debug('<
6720: EXCEPTION
6721: WHEN OTHERS THEN
6722: cep_standard.debug('CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM ' ||X_COUNTRY_NAME );
6723: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
6724: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_misc_eft_num');
6725: fnd_msg_pub.add;
6726: RAISE;
6744: procedure fail_mandatory is
6745: begin
6746: fnd_message.set_name ('CE', 'CE_ENTER_ACCOUNT_HOLDER_ALT');
6747: fnd_msg_pub.add;
6748: cep_standard.debug('CE_VALIDATE_MISC_ACCT_HLDR_ALT: ' || 'CE_ENTER_ACCOUNT_HOLDER_ALT');
6749: end fail_mandatory;
6750:
6751: procedure pass_check is
6752: begin
6749: end fail_mandatory;
6750:
6751: procedure pass_check is
6752: begin
6753: cep_standard.debug('pass_check');
6754: end pass_check;
6755:
6756: BEGIN
6757: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT');
6753: cep_standard.debug('pass_check');
6754: end pass_check;
6755:
6756: BEGIN
6757: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT');
6758:
6759: -- initialize API return status to success.
6760: x_return_status := fnd_api.g_ret_sts_success;
6761:
6759: -- initialize API return status to success.
6760: x_return_status := fnd_api.g_ret_sts_success;
6761:
6762: COUNTRY_NAME := X_COUNTRY_NAME;
6763: cep_standard.debug('CE_VALIDATE_MISC_ACCT_HLDR_ALT - COUNTRY_NAME: '|| COUNTRY_NAME);
6764:
6765: -- Initialize message list if p_init_msg_list is set to TRUE.
6766: IF FND_API.to_Boolean(p_init_msg_list) THEN
6767: FND_MSG_PUB.initialize;
6785:
6786: IF x_msg_count > 0 THEN
6787: x_return_status := fnd_api.g_ret_sts_error;
6788: END IF;
6789: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_misc_acct_hldr_alt - P_COUNT: '|| x_msg_count);
6790:
6791: cep_standard.debug('<
6793: WHEN OTHERS THEN
6787: x_return_status := fnd_api.g_ret_sts_error;
6788: END IF;
6789: cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_misc_acct_hldr_alt - P_COUNT: '|| x_msg_count);
6790:
6791: cep_standard.debug('<
6793: WHEN OTHERS THEN
6794: cep_standard.debug('CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT ' ||X_COUNTRY_NAME );
6795: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
6790:
6791: cep_standard.debug('<
6793: WHEN OTHERS THEN
6794: cep_standard.debug('CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT ' ||X_COUNTRY_NAME );
6795: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
6796: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_misc_acct_hldr_alt');
6797: fnd_msg_pub.add;
6798: RAISE;
6807: ) AS
6808: branch_num VARCHAR2(100);
6809: numeric_result VARCHAR2(100);
6810: BEGIN
6811: cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_BRANCH_IS_FORMAT');
6812:
6813: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
6814: -- 7582842: Disable validations using profile option
6815: IF CE_DISABLE_VALIDATION THEN
6812:
6813: Xo_VALUE_OUT := Xi_BRANCH_NUMBER;
6814: -- 7582842: Disable validations using profile option
6815: IF CE_DISABLE_VALIDATION THEN
6816: cep_standard.debug('validations disabled. no check done');
6817: RETURN;
6818: END IF;
6819:
6820: BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
6836: ce_failed_check('BRANCH_NUM','LENGTH_MAX','4');
6837: END IF; /* end of length check */
6838: END IF; /* end of null check */
6839:
6840: cep_standard.debug('<
6842:
6843: /* --------------------------------------------------------------------
6844: | PRIVATE PROCEDURE |
6867: procedure fail_mandatory is
6868: begin
6869: fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
6870: fnd_msg_pub.add;
6871: cep_standard.debug('GET_BRANCH_NUM_FORMAT: ' || 'CE_ENTER_BRANCH_NUM');
6872: end fail_mandatory;
6873:
6874: BEGIN
6875: cep_standard.debug('>>CE_VALIDATE_BANKINFO.GET_BRANCH_NUM_FORMAT');
6871: cep_standard.debug('GET_BRANCH_NUM_FORMAT: ' || 'CE_ENTER_BRANCH_NUM');
6872: end fail_mandatory;
6873:
6874: BEGIN
6875: cep_standard.debug('>>CE_VALIDATE_BANKINFO.GET_BRANCH_NUM_FORMAT');
6876:
6877: -- initialize API return status to success.
6878: x_return_status := fnd_api.g_ret_sts_success;
6879:
6878: x_return_status := fnd_api.g_ret_sts_success;
6879:
6880: COUNTRY_NAME := X_COUNTRY_NAME;
6881: X_VALUE_OUT := X_BRANCH_NUMBER;
6882: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
6883: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
6884:
6885: -- Initialize message list if p_init_msg_list is set to TRUE.
6886: IF FND_API.to_Boolean(p_init_msg_list) THEN
6879:
6880: COUNTRY_NAME := X_COUNTRY_NAME;
6881: X_VALUE_OUT := X_BRANCH_NUMBER;
6882: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
6883: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
6884:
6885: -- Initialize message list if p_init_msg_list is set to TRUE.
6886: IF FND_API.to_Boolean(p_init_msg_list) THEN
6887: FND_MSG_PUB.initialize;
6893: ELSE
6894: X_PASS_MAND_CHECK := 'P';
6895: END IF;
6896:
6897: cep_standard.debug('GET_BRANCH_NUM_FORMAT - X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
6898: IF (X_PASS_MAND_CHECK = 'P') THEN
6899: IF (COUNTRY_NAME = 'AT') THEN
6900: CE_VALIDATE_BANKINFO.CE_VALIDATE_BRANCH_AT(
6901: X_BRANCH_NUMBER,
6944: IF x_msg_count > 0 THEN
6945: x_return_status := fnd_api.g_ret_sts_error;
6946: END IF;
6947:
6948: cep_standard.debug('P_COUNT: '|| x_msg_count );
6949: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
6950: cep_standard.debug('<
6952: EXCEPTION
6945: x_return_status := fnd_api.g_ret_sts_error;
6946: END IF;
6947:
6948: cep_standard.debug('P_COUNT: '|| x_msg_count );
6949: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
6950: cep_standard.debug('<
6952: EXCEPTION
6953: WHEN OTHERS THEN
6946: END IF;
6947:
6948: cep_standard.debug('P_COUNT: '|| x_msg_count );
6949: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
6950: cep_standard.debug('<
6952: EXCEPTION
6953: WHEN OTHERS THEN
6954: cep_standard.debug('CE_VALIDATE_BANKINFO.GET_BRANCH_NUM_FORMAT ' ||X_COUNTRY_NAME );
6950: cep_standard.debug('<
6952: EXCEPTION
6953: WHEN OTHERS THEN
6954: cep_standard.debug('CE_VALIDATE_BANKINFO.GET_BRANCH_NUM_FORMAT ' ||X_COUNTRY_NAME );
6955: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
6956: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.GET_BRANCH_NUM_FORMAT');
6957: fnd_msg_pub.add;
6958: RAISE;
6979: Xi_BANK_NUM IN varchar2,
6980: Xo_VALUE_OUT OUT NOCOPY varchar2
6981: ) AS
6982: BEGIN
6983: cep_standard.debug('<
6985: -- setting global variable to map Bank ID as Bank Number instead
6986: -- of party ID
6987: BANK_ID_IS_NUM := TRUE;
7047:
7048: -- resetting the variable
7049: BANK_ID_IS_NUM := FALSE;
7050:
7051: cep_standard.debug('<
7053:
7054: -- 7582842: The following procedures are no longer used by CE:
7055: -- 1) UPD_BANK_UNIQUE
7083: ) AS
7084: COUNTRY_NAME VARCHAR2(2);
7085: X_PASS_MAND_CHECK VARCHAR2(1);
7086: BEGIN
7087: cep_standard.debug('>>CE_VALIDATE_BANKINFO.upd_bank_unique');
7088: -- initialize API return status to success.
7089: x_return_status := fnd_api.g_ret_sts_success;
7090:
7091: COUNTRY_NAME := X_COUNTRY_NAME;
7090:
7091: COUNTRY_NAME := X_COUNTRY_NAME;
7092: X_VALUE_OUT := X_BANK_NUMBER;
7093:
7094: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME ||
7095: ', X_VALUE_OUT: '|| X_VALUE_OUT);
7096:
7097: -- Initialize message list if p_init_msg_list is set to TRUE.
7098: IF FND_API.to_Boolean(p_init_msg_list) THEN
7098: IF FND_API.to_Boolean(p_init_msg_list) THEN
7099: FND_MSG_PUB.initialize;
7100: END IF;
7101:
7102: cep_standard.debug('P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
7103:
7104: /* UNIQUE VALIDATION CHECK for bank */
7105: cep_standard.debug('UNIQUE VALIDATION CHECK for bank' );
7106:
7101:
7102: cep_standard.debug('P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
7103:
7104: /* UNIQUE VALIDATION CHECK for bank */
7105: cep_standard.debug('UNIQUE VALIDATION CHECK for bank' );
7106:
7107: IF (COUNTRY_NAME = 'JP') THEN
7108: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP' );
7109:
7104: /* UNIQUE VALIDATION CHECK for bank */
7105: cep_standard.debug('UNIQUE VALIDATION CHECK for bank' );
7106:
7107: IF (COUNTRY_NAME = 'JP') THEN
7108: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP' );
7109:
7110: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BANK_JP(
7111: X_COUNTRY_NAME,
7112: X_BANK_NUMBER ,
7114: X_BANK_NAME_ALT,
7115: X_BANK_ID);
7116:
7117: END IF; /* country unique check for bank */
7118: cep_standard.debug('CE_VALIDATE_CD: ' || 'UNIQUE VALIDATION CHECK for bank end' );
7119:
7120: FND_MSG_PUB.Count_And_Get(
7121: p_encoded => FND_API.G_FALSE,
7122: p_count => x_msg_count,
7125: IF x_msg_count > 0 THEN
7126: x_return_status := fnd_api.g_ret_sts_error;
7127: END IF;
7128:
7129: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_unique - P_COUNT: '|| x_msg_count);
7130: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7131: cep_standard.debug('<
7133: WHEN OTHERS THEN
7126: x_return_status := fnd_api.g_ret_sts_error;
7127: END IF;
7128:
7129: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_unique - P_COUNT: '|| x_msg_count);
7130: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7131: cep_standard.debug('<
7133: WHEN OTHERS THEN
7134: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_unique ' ||X_COUNTRY_NAME );
7127: END IF;
7128:
7129: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_unique - P_COUNT: '|| x_msg_count);
7130: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7131: cep_standard.debug('<
7133: WHEN OTHERS THEN
7134: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_unique ' ||X_COUNTRY_NAME );
7135: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7130: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7131: cep_standard.debug('<
7133: WHEN OTHERS THEN
7134: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_unique ' ||X_COUNTRY_NAME );
7135: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7136: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.upd_bank_unique');
7137: fnd_msg_pub.add;
7138: RAISE;
7169: l_count_before_custom NUMBER; -- 9250566: Added
7170: l_count_after_custom NUMBER; -- 9250566: Added
7171:
7172: BEGIN
7173: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank');
7174:
7175: -- initialize API return status to success.
7176: x_return_status := fnd_api.g_ret_sts_success;
7177:
7177:
7178: COUNTRY_NAME := X_COUNTRY_NAME;
7179: l_value_out := X_BANK_NUMBER;
7180:
7181: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME||
7182: ', l_value_out: '|| l_value_out);
7183:
7184: -- Initialize message list if p_init_msg_list is set to TRUE.
7185: IF FND_API.to_Boolean(p_init_msg_list) THEN
7185: IF FND_API.to_Boolean(p_init_msg_list) THEN
7186: FND_MSG_PUB.initialize;
7187: END IF;
7188:
7189: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_validate - P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
7190:
7191: /* We must validate the Bank Number */
7192: IF X_BANK_NUMBER is null THEN
7193: X_PASS_MAND_CHECK := 'F';
7295: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
7296: l_usr_valid := fnd_api.g_ret_sts_success;
7297:
7298: -- Call to custom validation routines
7299: cep_standard.debug('Calling custom validation hooks');
7300: cep_standard.debug('l_count_before=' ||l_count_before_custom);
7301: cep_standard.debug('l_value_out=' ||l_value_out);
7302: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_bank (
7303: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
7296: l_usr_valid := fnd_api.g_ret_sts_success;
7297:
7298: -- Call to custom validation routines
7299: cep_standard.debug('Calling custom validation hooks');
7300: cep_standard.debug('l_count_before=' ||l_count_before_custom);
7301: cep_standard.debug('l_value_out=' ||l_value_out);
7302: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_bank (
7303: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
7304: Xi_BANK_NUMBER => l_value_out,
7297:
7298: -- Call to custom validation routines
7299: cep_standard.debug('Calling custom validation hooks');
7300: cep_standard.debug('l_count_before=' ||l_count_before_custom);
7301: cep_standard.debug('l_value_out=' ||l_value_out);
7302: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_bank (
7303: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
7304: Xi_BANK_NUMBER => l_value_out,
7305: Xi_BANK_NAME => X_BANK_NAME,
7309: Xo_BANK_NUM_OUT => l_value_out_custom,
7310: Xo_RETURN_STATUS => l_usr_valid
7311: );
7312: l_count_after_custom := FND_MSG_PUB.count_msg;
7313: cep_standard.debug('l_count_before='||l_count_before_custom);
7314:
7315: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
7316: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
7317:
7311: );
7312: l_count_after_custom := FND_MSG_PUB.count_msg;
7313: cep_standard.debug('l_count_before='||l_count_before_custom);
7314:
7315: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
7316: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
7317:
7318: -- Check return status
7319: IF l_usr_valid = fnd_api.g_ret_sts_error
7317:
7318: -- Check return status
7319: IF l_usr_valid = fnd_api.g_ret_sts_error
7320: THEN
7321: cep_standard.debug('Custom validations done - failure');
7322: IF l_count_after_custom = 0 THEN
7323: cep_standard.debug('No custom error message set');
7324: END IF;
7325: ELSE
7319: IF l_usr_valid = fnd_api.g_ret_sts_error
7320: THEN
7321: cep_standard.debug('Custom validations done - failure');
7322: IF l_count_after_custom = 0 THEN
7323: cep_standard.debug('No custom error message set');
7324: END IF;
7325: ELSE
7326: cep_standard.debug('Custom validations done - success');
7327: -- remove any unnecessary messages
7322: IF l_count_after_custom = 0 THEN
7323: cep_standard.debug('No custom error message set');
7324: END IF;
7325: ELSE
7326: cep_standard.debug('Custom validations done - success');
7327: -- remove any unnecessary messages
7328: WHILE l_count_after_custom > l_count_before_custom
7329: LOOP
7330: FND_MSG_PUB.delete_msg(l_count_after_custom);
7328: WHILE l_count_after_custom > l_count_before_custom
7329: LOOP
7330: FND_MSG_PUB.delete_msg(l_count_after_custom);
7331: l_count_after_custom := l_count_after_custom - 1;
7332: cep_standard.debug(l_count_after_custom);
7333: END LOOP;
7334: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
7335:
7336: END IF;
7330: FND_MSG_PUB.delete_msg(l_count_after_custom);
7331: l_count_after_custom := l_count_after_custom - 1;
7332: cep_standard.debug(l_count_after_custom);
7333: END LOOP;
7334: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
7335:
7336: END IF;
7337: -- 9250566 ADDED 4/6 END --------------------------
7338:
7345: IF x_msg_count > 0 THEN
7346: x_return_status := fnd_api.g_ret_sts_error;
7347: END IF;
7348:
7349: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_validate - P_COUNT: '|| x_msg_count);
7350: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7351: cep_standard.debug('<
7353: WHEN OTHERS THEN
7346: x_return_status := fnd_api.g_ret_sts_error;
7347: END IF;
7348:
7349: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_validate - P_COUNT: '|| x_msg_count);
7350: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7351: cep_standard.debug('<
7353: WHEN OTHERS THEN
7354: cep_standard.debug('EXCEPTION: upd_bank_validate' ||X_COUNTRY_NAME );
7347: END IF;
7348:
7349: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_bank_validate - P_COUNT: '|| x_msg_count);
7350: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7351: cep_standard.debug('<
7353: WHEN OTHERS THEN
7354: cep_standard.debug('EXCEPTION: upd_bank_validate' ||X_COUNTRY_NAME );
7355: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7350: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7351: cep_standard.debug('<
7353: WHEN OTHERS THEN
7354: cep_standard.debug('EXCEPTION: upd_bank_validate' ||X_COUNTRY_NAME );
7355: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7356: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.upd_bank_validate');
7357: fnd_msg_pub.add;
7358: RAISE;
7386: X_PASS_MAND_CHECK VARCHAR2(1);
7387:
7388: BEGIN
7389:
7390: cep_standard.debug('>>CE_VALIDATE_BANKINFO.upd_branch_unique');
7391:
7392: -- initialize API return status to success.
7393: x_return_status := fnd_api.g_ret_sts_success;
7394:
7394:
7395: COUNTRY_NAME := X_COUNTRY_NAME;
7396: X_VALUE_OUT := X_BRANCH_NUMBER;
7397:
7398: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_branch_unique - COUNTRY_NAME: '|| COUNTRY_NAME||
7399: ', X_VALUE_OUT: '|| X_VALUE_OUT);
7400:
7401: -- Initialize message list if p_init_msg_list is set to TRUE.
7402: IF FND_API.to_Boolean(p_init_msg_list) THEN
7403: FND_MSG_PUB.initialize;
7404: END IF;
7405:
7406: /* UNIQUE VALIDATION CHECK for branch */
7407: cep_standard.debug('UNIQUE VALIDATION CHECK for branch' );
7408:
7409: -- bug 4730717,
7410: -- 11/30/05 unique validation for US and Germany bank branches should not
7411: -- be removed
7410: -- 11/30/05 unique validation for US and Germany bank branches should not
7411: -- be removed
7412: -- 02/04/12 bug 13641238: changes to unique validation for US
7413: IF (COUNTRY_NAME = 'JP') THEN
7414: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH_JP' );
7415:
7416: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH_JP(
7417: X_COUNTRY_NAME,
7418: X_BRANCH_NUMBER,
7422: X_BRANCH_ID);
7423:
7424: -- bug 13641238: Added call for US
7425: ELSIF (COUNTRY_NAME = 'US') THEN
7426: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH_US');
7427:
7428: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH_US(
7429: Xi_BRANCH_NUMBER => X_BRANCH_NUMBER,
7430: Xi_BRANCH_NAME => X_BRANCH_NAME,
7430: Xi_BRANCH_NAME => X_BRANCH_NAME,
7431: Xi_BANK_ID => X_BANK_ID,
7432: Xi_BRANCH_ID => X_BRANCH_ID);
7433: ELSE
7434: cep_standard.debug('call CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH' );
7435:
7436: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_BRANCH(
7437: X_COUNTRY_NAME,
7438: X_BRANCH_NUMBER,
7450: IF x_msg_count > 0 THEN
7451: x_return_status := fnd_api.g_ret_sts_error;
7452: END IF;
7453:
7454: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_branch_unique - P_COUNT: '|| x_msg_count||
7455: ' X_VALUE_OUT: '|| X_VALUE_OUT||
7456: '<
7458: EXCEPTION
7456: '<
7458: EXCEPTION
7459: WHEN OTHERS THEN
7460: cep_standard.debug('EXCEPTION: upd_branch_unique ' ||X_COUNTRY_NAME );
7461: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7462: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.upd_branch_unique');
7463: fnd_msg_pub.add;
7464: RAISE;
7501: l_count_after_custom NUMBER; -- 9250566: Added
7502:
7503: BEGIN
7504:
7505: cep_standard.debug('>>CE_VALIDATE_BANKINFO.upd_branch_validate');
7506:
7507: -- initialize API return status to success.
7508: x_return_status := fnd_api.g_ret_sts_success;
7509:
7509:
7510: COUNTRY_NAME := X_COUNTRY_NAME;
7511: l_value_out := X_BRANCH_NUMBER;
7512:
7513: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
7514: cep_standard.debug('l_value_out: '|| l_value_out);
7515:
7516: -- Initialize message list if p_init_msg_list is set to TRUE.
7517: IF FND_API.to_Boolean(p_init_msg_list) THEN
7510: COUNTRY_NAME := X_COUNTRY_NAME;
7511: l_value_out := X_BRANCH_NUMBER;
7512:
7513: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
7514: cep_standard.debug('l_value_out: '|| l_value_out);
7515:
7516: -- Initialize message list if p_init_msg_list is set to TRUE.
7517: IF FND_API.to_Boolean(p_init_msg_list) THEN
7518: FND_MSG_PUB.initialize;
7523: X_PASS_MAND_CHECK := 'F';
7524: ELSE
7525: X_PASS_MAND_CHECK := 'P';
7526: END IF;
7527: cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
7528:
7529: cep_standard.debug('Calling CE_VALIDATE_BRANCH_'||COUNTRY_NAME);
7530: IF (COUNTRY_NAME = 'AT') THEN
7531: CE_VALIDATE_BANKINFO.CE_VALIDATE_BRANCH_AT(
7525: X_PASS_MAND_CHECK := 'P';
7526: END IF;
7527: cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
7528:
7529: cep_standard.debug('Calling CE_VALIDATE_BRANCH_'||COUNTRY_NAME);
7530: IF (COUNTRY_NAME = 'AT') THEN
7531: CE_VALIDATE_BANKINFO.CE_VALIDATE_BRANCH_AT(
7532: X_BRANCH_NUMBER,
7533: X_PASS_MAND_CHECK,
7641: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
7642: l_usr_valid := fnd_api.g_ret_sts_success;
7643:
7644: -- Call to custom validation routines
7645: cep_standard.debug('Calling custom validation hooks');
7646: cep_standard.debug('l_count_before=' ||l_count_before_custom);
7647: cep_standard.debug('l_value_out=' ||l_value_out);
7648: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch(
7649: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
7642: l_usr_valid := fnd_api.g_ret_sts_success;
7643:
7644: -- Call to custom validation routines
7645: cep_standard.debug('Calling custom validation hooks');
7646: cep_standard.debug('l_count_before=' ||l_count_before_custom);
7647: cep_standard.debug('l_value_out=' ||l_value_out);
7648: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch(
7649: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
7650: Xi_BANK_NUMBER => X_BANK_NUMBER,
7643:
7644: -- Call to custom validation routines
7645: cep_standard.debug('Calling custom validation hooks');
7646: cep_standard.debug('l_count_before=' ||l_count_before_custom);
7647: cep_standard.debug('l_value_out=' ||l_value_out);
7648: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch(
7649: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
7650: Xi_BANK_NUMBER => X_BANK_NUMBER,
7651: Xi_BRANCH_NUMBER => l_value_out,
7659: Xo_RETURN_STATUS => l_usr_valid
7660: );
7661:
7662: l_count_after_custom := FND_MSG_PUB.count_msg;
7663: cep_standard.debug('l_count_before='||l_count_before_custom);
7664:
7665: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
7666: X_VALUE_OUT := l_value_out_custom;
7667:
7661:
7662: l_count_after_custom := FND_MSG_PUB.count_msg;
7663: cep_standard.debug('l_count_before='||l_count_before_custom);
7664:
7665: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
7666: X_VALUE_OUT := l_value_out_custom;
7667:
7668: -- Check return status
7669: IF l_usr_valid = fnd_api.g_ret_sts_error
7667:
7668: -- Check return status
7669: IF l_usr_valid = fnd_api.g_ret_sts_error
7670: THEN
7671: cep_standard.debug('Custom validations done - failure');
7672: IF l_count_after_custom = 0 THEN
7673: cep_standard.debug('No custom error message set');
7674: END IF;
7675: ELSE
7669: IF l_usr_valid = fnd_api.g_ret_sts_error
7670: THEN
7671: cep_standard.debug('Custom validations done - failure');
7672: IF l_count_after_custom = 0 THEN
7673: cep_standard.debug('No custom error message set');
7674: END IF;
7675: ELSE
7676: cep_standard.debug('Custom validations done - success');
7677: -- remove any unnecessary messages
7672: IF l_count_after_custom = 0 THEN
7673: cep_standard.debug('No custom error message set');
7674: END IF;
7675: ELSE
7676: cep_standard.debug('Custom validations done - success');
7677: -- remove any unnecessary messages
7678: WHILE l_count_after_custom > l_count_before_custom
7679: LOOP
7680: FND_MSG_PUB.delete_msg(l_count_after_custom);
7678: WHILE l_count_after_custom > l_count_before_custom
7679: LOOP
7680: FND_MSG_PUB.delete_msg(l_count_after_custom);
7681: l_count_after_custom := l_count_after_custom - 1;
7682: cep_standard.debug(l_count_after_custom);
7683: END LOOP;
7684: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
7685:
7686: END IF;
7680: FND_MSG_PUB.delete_msg(l_count_after_custom);
7681: l_count_after_custom := l_count_after_custom - 1;
7682: cep_standard.debug(l_count_after_custom);
7683: END LOOP;
7684: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
7685:
7686: END IF;
7687: -- 9250566 5/6 ADDED END --------------------------
7688:
7694: IF x_msg_count > 0 THEN
7695: x_return_status := fnd_api.g_ret_sts_error;
7696: END IF;
7697:
7698: cep_standard.debug('P_COUNT: '|| x_msg_count);
7699: cep_standard.debug(' X_VALUE_OUT: '|| X_VALUE_OUT);
7700: cep_standard.debug('<
7702: EXCEPTION
7695: x_return_status := fnd_api.g_ret_sts_error;
7696: END IF;
7697:
7698: cep_standard.debug('P_COUNT: '|| x_msg_count);
7699: cep_standard.debug(' X_VALUE_OUT: '|| X_VALUE_OUT);
7700: cep_standard.debug('<
7702: EXCEPTION
7703: WHEN OTHERS THEN
7696: END IF;
7697:
7698: cep_standard.debug('P_COUNT: '|| x_msg_count);
7699: cep_standard.debug(' X_VALUE_OUT: '|| X_VALUE_OUT);
7700: cep_standard.debug('<
7702: EXCEPTION
7703: WHEN OTHERS THEN
7704: cep_standard.debug('EXCEPTION: upd_branch_validate ' ||X_COUNTRY_NAME );
7700: cep_standard.debug('<
7702: EXCEPTION
7703: WHEN OTHERS THEN
7704: cep_standard.debug('EXCEPTION: upd_branch_validate ' ||X_COUNTRY_NAME );
7705: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7706: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.upd_branch_validate');
7707: fnd_msg_pub.add;
7708: RAISE;
7737: ) AS
7738: COUNTRY_NAME VARCHAR2(2);
7739: X_PASS_MAND_CHECK VARCHAR2(1);
7740: BEGIN
7741: cep_standard.debug('>>CE_VALIDATE_BANKINFO.upd_account_unique');
7742:
7743: -- initialize API return status to success.
7744: x_return_status := fnd_api.g_ret_sts_success;
7745:
7745:
7746: COUNTRY_NAME := X_COUNTRY_NAME;
7747: X_VALUE_OUT := X_ACCOUNT_NUMBER;
7748:
7749: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME||
7750: ', X_VALUE_OUT: '|| X_VALUE_OUT);
7751:
7752: -- Initialize message list if p_init_msg_list is set to TRUE.
7753: IF FND_API.to_Boolean(p_init_msg_list) THEN
7754: FND_MSG_PUB.initialize;
7755: END IF;
7756:
7757: /* UNIQUE VALIDATION CHECK for account */
7758: cep_standard.debug('UNIQUE_VALIDATION CHECK for account');
7759:
7760: IF (COUNTRY_NAME = 'JP') THEN
7761: CE_VALIDATE_BANKINFO.CE_VALIDATE_UNIQUE_ACCOUNT_JP(
7762: X_ACCOUNT_NUMBER,
7781: X_ACCOUNT_NAME,
7782: X_BRANCH_ID,
7783: X_ACCOUNT_ID);
7784: END IF;
7785: cep_standard.debug(' UNIQUE_VALIDATION CHECK for account end ');
7786:
7787: FND_MSG_PUB.Count_And_Get(
7788: p_encoded => FND_API.G_FALSE,
7789: p_count => x_msg_count,
7792: IF x_msg_count > 0 THEN
7793: x_return_status := fnd_api.g_ret_sts_error;
7794: END IF;
7795:
7796: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_unique - P_COUNT: '|| x_msg_count);
7797: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7798: cep_standard.debug('<
7800: WHEN OTHERS THEN
7793: x_return_status := fnd_api.g_ret_sts_error;
7794: END IF;
7795:
7796: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_unique - P_COUNT: '|| x_msg_count);
7797: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7798: cep_standard.debug('<
7800: WHEN OTHERS THEN
7801: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_unique ' ||X_COUNTRY_NAME );
7794: END IF;
7795:
7796: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_unique - P_COUNT: '|| x_msg_count);
7797: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7798: cep_standard.debug('<
7800: WHEN OTHERS THEN
7801: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_unique ' ||X_COUNTRY_NAME );
7802: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7797: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
7798: cep_standard.debug('<
7800: WHEN OTHERS THEN
7801: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_unique ' ||X_COUNTRY_NAME );
7802: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
7803: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.upd_account_unique');
7804: fnd_msg_pub.add;
7805: RAISE;
7845: l_count_before_custom NUMBER; -- 9250566: Added
7846: l_count_after_custom NUMBER; -- 9250566: Added
7847:
7848: BEGIN
7849: cep_standard.debug('>>CE_VALIDATE_BANKINFO.upd_account_validate');
7850: -- initialize API return status to success.
7851: x_return_status := fnd_api.g_ret_sts_success;
7852:
7853: COUNTRY_NAME := X_COUNTRY_NAME;
7853: COUNTRY_NAME := X_COUNTRY_NAME;
7854: l_value_out := X_ACCOUNT_NUMBER;
7855: X_ELECTRONIC_ACCT_NUM := X_ACCOUNT_NUMBER;
7856:
7857: cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME||
7858: ', l_value_out: '|| l_value_out);
7859:
7860: -- Initialize message list if p_init_msg_list is set to TRUE.
7861: --IF FND_API.to_Boolean(p_init_msg_list) THEN
8017: l_count_before_custom := Nvl(FND_MSG_PUB.count_msg,0);
8018: l_usr_valid := fnd_api.g_ret_sts_success;
8019:
8020: -- Call to custom validation routines
8021: cep_standard.debug('Calling custom validation hooks');
8022: cep_standard.debug('l_count_before=' ||l_count_before_custom);
8023: cep_standard.debug('l_value_out=' ||l_value_out);
8024: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account(
8025: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
8018: l_usr_valid := fnd_api.g_ret_sts_success;
8019:
8020: -- Call to custom validation routines
8021: cep_standard.debug('Calling custom validation hooks');
8022: cep_standard.debug('l_count_before=' ||l_count_before_custom);
8023: cep_standard.debug('l_value_out=' ||l_value_out);
8024: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account(
8025: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
8026: Xi_BANK_NUMBER => X_BANK_NUMBER,
8019:
8020: -- Call to custom validation routines
8021: cep_standard.debug('Calling custom validation hooks');
8022: cep_standard.debug('l_count_before=' ||l_count_before_custom);
8023: cep_standard.debug('l_value_out=' ||l_value_out);
8024: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account(
8025: Xi_COUNTRY_NAME => X_COUNTRY_NAME,
8026: Xi_BANK_NUMBER => X_BANK_NUMBER,
8027: Xi_BRANCH_NUMBER => X_BRANCH_NUMBER,
8040: Xo_RETURN_STATUS => l_usr_valid
8041: );
8042:
8043: l_count_after_custom := FND_MSG_PUB.count_msg;
8044: cep_standard.debug('l_count_after=' ||l_count_after_custom);
8045:
8046: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
8047: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
8048:
8042:
8043: l_count_after_custom := FND_MSG_PUB.count_msg;
8044: cep_standard.debug('l_count_after=' ||l_count_after_custom);
8045:
8046: cep_standard.debug('l_value_out_custom='||l_value_out_custom);
8047: X_VALUE_OUT := NVL(l_value_out_custom,l_value_out);
8048:
8049: -- Check return status
8050: IF l_usr_valid = fnd_api.g_ret_sts_error
8048:
8049: -- Check return status
8050: IF l_usr_valid = fnd_api.g_ret_sts_error
8051: THEN
8052: cep_standard.debug('Custom validations done - failure');
8053: IF l_count_after_custom = 0 THEN
8054: cep_standard.debug('No custom error message set');
8055: END IF;
8056: ELSE
8050: IF l_usr_valid = fnd_api.g_ret_sts_error
8051: THEN
8052: cep_standard.debug('Custom validations done - failure');
8053: IF l_count_after_custom = 0 THEN
8054: cep_standard.debug('No custom error message set');
8055: END IF;
8056: ELSE
8057: cep_standard.debug('Custom validations done - success');
8058: -- remove any unnecessary messages
8053: IF l_count_after_custom = 0 THEN
8054: cep_standard.debug('No custom error message set');
8055: END IF;
8056: ELSE
8057: cep_standard.debug('Custom validations done - success');
8058: -- remove any unnecessary messages
8059: WHILE l_count_after_custom > l_count_before_custom
8060: LOOP
8061: FND_MSG_PUB.delete_msg(l_count_after_custom);
8059: WHILE l_count_after_custom > l_count_before_custom
8060: LOOP
8061: FND_MSG_PUB.delete_msg(l_count_after_custom);
8062: l_count_after_custom := l_count_after_custom - 1;
8063: cep_standard.debug(l_count_after_custom);
8064: END LOOP;
8065: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
8066:
8067: END IF;
8061: FND_MSG_PUB.delete_msg(l_count_after_custom);
8062: l_count_after_custom := l_count_after_custom - 1;
8063: cep_standard.debug(l_count_after_custom);
8064: END LOOP;
8065: cep_standard.debug('After cleanup, count='||FND_MSG_PUB.count_msg);
8066:
8067: END IF;
8068: -- 9250566 6/6 ADDED END --------------------------
8069:
8075: IF x_msg_count > 0 THEN
8076: x_return_status := fnd_api.g_ret_sts_error;
8077: END IF;
8078:
8079: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_validate - P_COUNT: '|| x_msg_count);
8080: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
8081: cep_standard.debug('<
8083: WHEN OTHERS THEN
8076: x_return_status := fnd_api.g_ret_sts_error;
8077: END IF;
8078:
8079: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_validate - P_COUNT: '|| x_msg_count);
8080: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
8081: cep_standard.debug('<
8083: WHEN OTHERS THEN
8084: cep_standard.debug('EXCEPTION: upd_account_validate ' ||X_COUNTRY_NAME );
8077: END IF;
8078:
8079: cep_standard.debug('CE_VALIDATE_BANKINFO.upd_account_validate - P_COUNT: '|| x_msg_count);
8080: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
8081: cep_standard.debug('<
8083: WHEN OTHERS THEN
8084: cep_standard.debug('EXCEPTION: upd_account_validate ' ||X_COUNTRY_NAME );
8085: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
8080: cep_standard.debug('X_VALUE_OUT: '|| X_VALUE_OUT);
8081: cep_standard.debug('<
8083: WHEN OTHERS THEN
8084: cep_standard.debug('EXCEPTION: upd_account_validate ' ||X_COUNTRY_NAME );
8085: FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
8086: fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.upd_account_validate');
8087: fnd_msg_pub.add;
8088: RAISE;
8222: end loop;
8223: return l_sum;
8224: end sumdigits;
8225: begin
8226: cep_standard.debug('>>calccheckdigit');
8227: -- multiply digits with their weights
8228: l_prod_u := p_modulus_checks_rec.weight_u * SUBSTR(p_combined_number,1,1);
8229: l_prod_v := p_modulus_checks_rec.weight_v * SUBSTR(p_combined_number,2,1);
8230: l_prod_w := p_modulus_checks_rec.weight_w * SUBSTR(p_combined_number,3,1);
8239: l_prod_f := p_modulus_checks_rec.weight_f * SUBSTR(p_combined_number,12,1);
8240: l_prod_g := p_modulus_checks_rec.weight_g * SUBSTR(p_combined_number,13,1);
8241: l_prod_h := p_modulus_checks_rec.weight_h * SUBSTR(p_combined_number,14,1);
8242:
8243: cep_standard.debug('check_type = '||p_modulus_checks_rec.check_type);
8244: -- for DBLAL sum is of the digits of the products
8245: if(p_modulus_checks_rec.check_type = 'DBLAL')
8246: then
8247: l_sum_prod := sumDigits(l_prod_u) +
8286: l_mod_res := mod(l_sum_prod,11);
8287: else -- for mod10 and dblal, get mod of 10
8288: l_mod_res := mod(l_sum_prod,10);
8289: end if;
8290: cep_standard.debug('<
8292: end calccheckdigit;
8293:
8294: begin
8291: return l_mod_res;
8292: end calccheckdigit;
8293:
8294: begin
8295: cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_mod_gb');
8296: -- initialize API return status to success.
8297: Xo_return_status := fnd_api.g_ret_sts_success;
8298:
8299: open c_modulus_checks(xi_sort_code);
8306: for l_mod_check_count in 1 .. t_modulus_checks.count
8307: loop
8308: l_check_reqd := true;
8309:
8310: cep_standard.debug('------------------------ performing check #'||l_mod_check_count);
8311: cep_standard.debug('exception_code = '||t_modulus_checks(l_mod_check_count).exception_code);
8312: cep_standard.debug('sort_code = '||xi_sort_code);
8313: cep_standard.debug('account_number = '||xi_account_number);
8314:
8307: loop
8308: l_check_reqd := true;
8309:
8310: cep_standard.debug('------------------------ performing check #'||l_mod_check_count);
8311: cep_standard.debug('exception_code = '||t_modulus_checks(l_mod_check_count).exception_code);
8312: cep_standard.debug('sort_code = '||xi_sort_code);
8313: cep_standard.debug('account_number = '||xi_account_number);
8314:
8315: for l_pos in 1..8
8308: l_check_reqd := true;
8309:
8310: cep_standard.debug('------------------------ performing check #'||l_mod_check_count);
8311: cep_standard.debug('exception_code = '||t_modulus_checks(l_mod_check_count).exception_code);
8312: cep_standard.debug('sort_code = '||xi_sort_code);
8313: cep_standard.debug('account_number = '||xi_account_number);
8314:
8315: for l_pos in 1..8
8316: loop
8309:
8310: cep_standard.debug('------------------------ performing check #'||l_mod_check_count);
8311: cep_standard.debug('exception_code = '||t_modulus_checks(l_mod_check_count).exception_code);
8312: cep_standard.debug('sort_code = '||xi_sort_code);
8313: cep_standard.debug('account_number = '||xi_account_number);
8314:
8315: for l_pos in 1..8
8316: loop
8317: l_account_num_arr(l_pos) := substr(xi_account_number, l_pos, 1);
8367: then
8368: if l_account_num_arr(3) in (6, 9)
8369: then
8370: l_check_reqd := false;
8371: cep_standard.debug('Exception 3: No check required');
8372: end if;
8373:
8374: ----
8375: -- exception 5
8382: into l_sort_code
8383: from ce_uk_val_subs_codes
8384: where orig_sort_code = xi_sort_code;
8385:
8386: cep_standard.debug('substituting sort code with value from mod_subs_sort_code table');
8387: cep_standard.debug('original sort code = '||xi_sort_code);
8388: cep_standard.debug('substitution sort code = '||l_sort_code);
8389: EXCEPTION
8390: WHEN no_data_found THEN
8383: from ce_uk_val_subs_codes
8384: where orig_sort_code = xi_sort_code;
8385:
8386: cep_standard.debug('substituting sort code with value from mod_subs_sort_code table');
8387: cep_standard.debug('original sort code = '||xi_sort_code);
8388: cep_standard.debug('substitution sort code = '||l_sort_code);
8389: EXCEPTION
8390: WHEN no_data_found THEN
8391: cep_standard.debug('Sort code does not appear in mod_subs_sort_code table, hence original sort code is used');
8384: where orig_sort_code = xi_sort_code;
8385:
8386: cep_standard.debug('substituting sort code with value from mod_subs_sort_code table');
8387: cep_standard.debug('original sort code = '||xi_sort_code);
8388: cep_standard.debug('substitution sort code = '||l_sort_code);
8389: EXCEPTION
8390: WHEN no_data_found THEN
8391: cep_standard.debug('Sort code does not appear in mod_subs_sort_code table, hence original sort code is used');
8392: END;
8387: cep_standard.debug('original sort code = '||xi_sort_code);
8388: cep_standard.debug('substitution sort code = '||l_sort_code);
8389: EXCEPTION
8390: WHEN no_data_found THEN
8391: cep_standard.debug('Sort code does not appear in mod_subs_sort_code table, hence original sort code is used');
8392: END;
8393: end if;
8394: ----
8395: -- exception 6
8399: if (l_account_num_arr(1) in (4, 5, 6, 7, 8))
8400: and (l_account_num_arr(7) = l_account_num_arr(8))
8401: then
8402: l_check_reqd := false;
8403: cep_standard.debug('Foreign accounts- checks cannot be performed');
8404: end if;
8405:
8406: ----
8407: -- exception 7
8425: ----
8426: when t_modulus_checks(l_mod_check_count).exception_code = 8
8427: then
8428: l_sort_code := '090126';
8429: cep_standard.debug('substituting sort code..');
8430: cep_standard.debug('original sort code = '||xi_sort_code);
8431: cep_standard.debug('substitution sort code = '||l_sort_code);
8432:
8433: ----
8426: when t_modulus_checks(l_mod_check_count).exception_code = 8
8427: then
8428: l_sort_code := '090126';
8429: cep_standard.debug('substituting sort code..');
8430: cep_standard.debug('original sort code = '||xi_sort_code);
8431: cep_standard.debug('substitution sort code = '||l_sort_code);
8432:
8433: ----
8434: -- exception 9
8427: then
8428: l_sort_code := '090126';
8429: cep_standard.debug('substituting sort code..');
8430: cep_standard.debug('original sort code = '||xi_sort_code);
8431: cep_standard.debug('substitution sort code = '||l_sort_code);
8432:
8433: ----
8434: -- exception 9
8435: ----
8435: ----
8436: when t_modulus_checks(l_mod_check_count).exception_code = 9
8437: then
8438: l_sort_code := '309634';
8439: cep_standard.debug('substituting sort code..');
8440: cep_standard.debug('original sort code = '||xi_sort_code);
8441: cep_standard.debug('substitution sort code = '||l_sort_code);
8442:
8443: ----
8436: when t_modulus_checks(l_mod_check_count).exception_code = 9
8437: then
8438: l_sort_code := '309634';
8439: cep_standard.debug('substituting sort code..');
8440: cep_standard.debug('original sort code = '||xi_sort_code);
8441: cep_standard.debug('substitution sort code = '||l_sort_code);
8442:
8443: ----
8444: -- exception 10
8437: then
8438: l_sort_code := '309634';
8439: cep_standard.debug('substituting sort code..');
8440: cep_standard.debug('original sort code = '||xi_sort_code);
8441: cep_standard.debug('substitution sort code = '||l_sort_code);
8442:
8443: ----
8444: -- exception 10
8445: ----
8498: when t_modulus_checks(l_mod_check_count).exception_code = 14
8499: then
8500: if l_calc_cd <> 0
8501: then
8502: cep_standard.debug('l_calc_cd = '||l_calc_cd);
8503: cep_standard.debug('l_actual_cd = '||l_actual_cd);
8504: cep_standard.debug('Exception 14: First check fails, hence performing second check');
8505: if l_account_num_arr(8) not in (0, 1, 9)
8506: then
8499: then
8500: if l_calc_cd <> 0
8501: then
8502: cep_standard.debug('l_calc_cd = '||l_calc_cd);
8503: cep_standard.debug('l_actual_cd = '||l_actual_cd);
8504: cep_standard.debug('Exception 14: First check fails, hence performing second check');
8505: if l_account_num_arr(8) not in (0, 1, 9)
8506: then
8507: cep_standard.debug('It is not a valid Coutts number - second check cannot be performed');
8500: if l_calc_cd <> 0
8501: then
8502: cep_standard.debug('l_calc_cd = '||l_calc_cd);
8503: cep_standard.debug('l_actual_cd = '||l_actual_cd);
8504: cep_standard.debug('Exception 14: First check fails, hence performing second check');
8505: if l_account_num_arr(8) not in (0, 1, 9)
8506: then
8507: cep_standard.debug('It is not a valid Coutts number - second check cannot be performed');
8508: else
8503: cep_standard.debug('l_actual_cd = '||l_actual_cd);
8504: cep_standard.debug('Exception 14: First check fails, hence performing second check');
8505: if l_account_num_arr(8) not in (0, 1, 9)
8506: then
8507: cep_standard.debug('It is not a valid Coutts number - second check cannot be performed');
8508: else
8509: l_combined_number := l_sort_code||'0'||substr(xi_account_number,1,7);
8510: l_calc_cd := calcCheckDigit(l_combined_number, t_modulus_checks(l_mod_check_count));
8511: end if;
8513: ELSE
8514: NULL;
8515: end case ;
8516:
8517: cep_standard.debug('l_actual_cd = '||l_actual_cd);
8518: cep_standard.debug('l_calc_cd = '||l_calc_cd);
8519:
8520: if (l_calc_cd = l_actual_cd )
8521: then
8514: NULL;
8515: end case ;
8516:
8517: cep_standard.debug('l_actual_cd = '||l_actual_cd);
8518: cep_standard.debug('l_calc_cd = '||l_calc_cd);
8519:
8520: if (l_calc_cd = l_actual_cd )
8521: then
8522: l_acct_valid := true;
8519:
8520: if (l_calc_cd = l_actual_cd )
8521: then
8522: l_acct_valid := true;
8523: cep_standard.debug('check succesful');
8524: else
8525: l_acct_valid := false;
8526: cep_standard.debug('check fails');
8527: end if;
8522: l_acct_valid := true;
8523: cep_standard.debug('check succesful');
8524: else
8525: l_acct_valid := false;
8526: cep_standard.debug('check fails');
8527: end if;
8528: if (not l_acct_valid and t_modulus_checks(l_mod_check_count).exception_code not in (2,10,12))
8529: or (l_acct_valid and t_modulus_checks(l_mod_check_count).exception_code IN (2,10,12))
8530: then
8534: end loop;
8535: if(l_acct_valid)
8536: then
8537: Xo_return_status := fnd_api.g_ret_sts_success;
8538: cep_standard.debug('Account #'||xi_account_number||' is valid.');
8539: else
8540: Xo_return_status := fnd_api.g_ret_sts_error;
8541: cep_standard.debug('Account #'||xi_account_number||' is invalid.');
8542: end if;
8537: Xo_return_status := fnd_api.g_ret_sts_success;
8538: cep_standard.debug('Account #'||xi_account_number||' is valid.');
8539: else
8540: Xo_return_status := fnd_api.g_ret_sts_error;
8541: cep_standard.debug('Account #'||xi_account_number||' is invalid.');
8542: end if;
8543: cep_standard.debug('<
8545: /* Bug 16234978 Code change (1/2) ends */
8539: else
8540: Xo_return_status := fnd_api.g_ret_sts_error;
8541: cep_standard.debug('Account #'||xi_account_number||' is invalid.');
8542: end if;
8543: cep_standard.debug('<
8545: /* Bug 16234978 Code change (1/2) ends */
8546:
8547: END CE_VALIDATE_BANKINFO;