[Home] [Help]
PACKAGE BODY: APPS.CE_VALIDATE_BANKINFO_UPG
Source
1 package body CE_VALIDATE_BANKINFO_UPG as
2 /* $Header: cevlbkub.pls 120.4 2005/08/23 21:52:35 lkwan noship $ */
3
4
5 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
6 --l_DEBUG varchar2(1) := 'Y';
7
8
9 -- 8/27/03 use CE_UPG_BANK_REC table
10
11 cursor get_bank_num ( Xi_BANK_ID NUMBER) IS
12 select PartyBank.BANK_OR_BRANCH_NUMBER
13 From CE_UPG_BANK_REC PartyBank
14 Where
15 PartyBank.CE_UPGRADE_ID = Xi_BANK_ID
16 --PartyBank.source_pk_id = Xi_BANK_ID
17 And PartyBank.BANK_ENTITY_TYPE = 'BANK';
18
19 function ce_check_numeric(check_value VARCHAR2,
20 pos_from NUMBER,
21 pos_for NUMBER)
22 RETURN VARCHAR2
23 IS
24 num_check VARCHAR2(40);
25 BEGIN
26 num_check := '1';
27 num_check := nvl(
28 rtrim(
29 translate(substr(check_value,pos_from,pos_for),
30 '1234567890',
31 ' ')
32 ), '0'
33 );
34 RETURN(num_check);
35 END ce_check_numeric;
36
37 /* --------------------------------------------------------------------
38 | PRIVATE PROCEDURE |
39 | COMPARE_BANK_AND_BRANCH_NUM |
40 | |
41 | CALLED BY |
42 | CE_VALIDATE_BRANCH_* |
43 | - DE, IS, SE, GB |
44 | |
45 | DESCRIPTION |
46 | Verify that the value entered for Bank number and Branch number |
47 | fields are the same when both fields are entered for some |
48 | countries |
49 --------------------------------------------------------------------- */
50
51
52 PROCEDURE COMPARE_BANK_AND_BRANCH_NUM(Xi_branch_num IN VARCHAR2,
53 Xi_BANK_ID IN NUMBER) AS
54
55
56 /* 10/19/04 move to top of package
57 -- 8/27/03 use CE_UPG_BANK_REC table
58 cursor get_bank_num is
59 select PartyBank.BANK_OR_BRANCH_NUMBER
60 From CE_UPG_BANK_REC PartyBank
61 Where
62 PartyBank.CE_UPGRADE_ID = Xi_BANK_ID
63 --PartyBank.source_pk_id = Xi_BANK_ID
64 And PartyBank.BANK_ENTITY_TYPE = 'BANK';
65 */
66
67 BANK_NUM varchar2(60);
68 bank_count number;
69 BEGIN
70 IF l_DEBUG in ('Y', 'C') THEN
71 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.compare_bank_and_branch_num');
72 END IF;
73
74 select count(*) into bank_count
75 From CE_UPG_BANK_REC PartyBank
76 Where
77 PartyBank.CE_UPGRADE_ID = Xi_BANK_ID
78 --PartyBank.source_pk_id = Xi_BANK_ID
79 And PartyBank.BANK_ENTITY_TYPE = 'BANK';
80
81
82 IF (bank_count = 1) then
83
84 OPEN get_bank_num(Xi_BANK_ID);
85 FETCH get_bank_num INTO bank_num;
86
87 IF l_DEBUG in ('Y', 'C') THEN
88 cep_standard.debug('fetch BANK_NUM : ' ||BANK_NUM);
89 END IF;
90
91 IF (BANK_NUM is null) then
92 null;
93 ELSIF (BANK_NUM is not null) then
94 BANK_NUM := upper(replace(BANK_NUM,' ',''));
95
96 BANK_NUM := upper(replace(BANK_NUM,'-',''));
97
98 IF l_DEBUG in ('Y', 'C') THEN
99 cep_standard.debug('new BANK_NUM : ' ||BANK_NUM);
100 END IF;
101
102
103 IF ((nvl(BANK_NUM,Xi_branch_num) <> Xi_branch_num)) then
104 IF l_DEBUG in ('Y', 'C') THEN
105 cep_standard.debug('Bank number and branch number does not match' ||
106 ' CE_BANK_BRANCH_NUM_NOT_MATCHED');
107 END IF;
108 fnd_message.set_name('CE', 'CE_BANK_BRANCH_NUM_NOT_MATCHED');
109 fnd_msg_pub.add;
110
111 END IF;
112
113 END IF;
114
115 close get_bank_num;
116 ELSIF (bank_count > 1) then
117 IF l_DEBUG in ('Y', 'C') THEN
118 cep_standard.debug('EXCEPTION: More than one bank match ');
119 END IF;
120 FND_MESSAGE.set_name('CE', 'CE_MANY_BANKS');
121 fnd_msg_pub.add;
122
123 ELSIF (bank_count = 0) then
124 IF l_DEBUG in ('Y', 'C') THEN
125 cep_standard.debug(' CE_BANK_DOES_NOT_EXISTS');
126 END IF;
127 fnd_message.set_name ('CE', 'CE_BANK_DOES_NOT_EXISTS');
128 fnd_msg_pub.add;
129
130
131 END IF;
132
133 /*IF l_DEBUG in ('Y', 'C') THEN
134 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.compare_bank_and_branch_num');
135 END IF;
136 */
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF l_DEBUG in ('Y', 'C') THEN
140 cep_standard.debug('CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM ' );
141 END IF;
142
143 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
144 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO_UPG.compare_bank_and_branch_num');
145 fnd_msg_pub.add;
146 RAISE;
147
148 END COMPARE_BANK_AND_BRANCH_NUM;
149
150 /* --------------------------------------------------------------------
151 | PRIVATE PROCEDURE |
152 | COMPARE_ACCOUNT_NUM_AND_CD |
153 | |
154 | CALLED BY |
155 | CE_VALIDATE_CD_* |
156 | |
157 | DESCRIPTION |
158 | Verify that the check digit entered on the account number |
159 | and check digit fields are the same |
160 --------------------------------------------------------------------- */
161
162 FUNCTION COMPARE_ACCOUNT_NUM_AND_CD(Xi_account_num IN VARCHAR2,
163 Xi_CD IN NUMBER,
164 Xi_CD_length in number,
165 Xi_CD_pos_from_right IN Number default 0) RETURN BOOLEAN AS
166
167 cd_position number;
168 acct_cd number;
169
170 /**************************/
171
172 procedure pass_check is
173 begin
174 IF l_DEBUG in ('Y', 'C') THEN
175 null;
176 --cep_standard.debug('COMPARE_ACCOUNT_NUM_AND_CD: ' || 'CD match');
177 END IF;
178 end pass_check;
179
180 /**************************/
181
182
183 BEGIN
184 IF l_DEBUG in ('Y', 'C') THEN
185 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.COMPARE_ACCOUNT_NUM_AND_CD');
186 END IF;
187
188 cd_position := (length(Xi_account_num) - Xi_CD_pos_from_right);
189 acct_cd := substr(Xi_account_num, cd_position, Xi_CD_length);
190
191 IF l_DEBUG in ('Y', 'C') THEN
192 cep_standard.debug('cd_position : '||cd_position || ' acct_cd : '||acct_cd ||' Xi_CD : '||Xi_CD );
193 END IF;
194
195
196 IF ( acct_cd <> Xi_CD) then
197 IF l_DEBUG in ('Y', 'C') THEN
198 cep_standard.debug('CD does not match '|| 'CE_ACCT_NUM_AND_CD_NOT_MATCHED');
199 END IF;
200 fnd_message.set_name('CE', 'CE_ACCT_NUM_AND_CD_NOT_MATCHED');
201 fnd_msg_pub.add;
202 return false;
203 ELSE
204 pass_check;
205 return true;
206 END IF;
207
208 /*
209 IF l_DEBUG in ('Y', 'C') THEN
210 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.COMPARE_ACCOUNT_NUM_AND_CD');
211 END IF;
212 */
213 END COMPARE_ACCOUNT_NUM_AND_CD;
214
215 /* --------------------------------------------------------------------
216 | PRIVATE FUNCTION |
217 | CE_VAL_UNIQUE_TAX_PAYER_ID |
218 | |
219 | CALLED BY |
220 | CE_VALIDATE_BANK_CO |
221 | |
222 | DESCRIPTION |
223 | Unique Tax Payer ID VALIDATIONS |
224 | |
225 --------------------------------------------------------------------- */
226
227 FUNCTION CE_VAL_UNIQUE_TAX_PAYER_ID (p_country_code IN VARCHAR2,
228 p_taxpayer_id IN VARCHAR2
229 ) RETURN VARCHAR2 IS
230
231
232 CURSOR CHECK_UNIQUE_TAXID_BK IS -- Banks
233 SELECT JGZZ_FISCAL_CODE
234 FROM CE_UPG_BANK_REC --HZ_PARTIES
235 WHERE JGZZ_FISCAL_CODE = p_taxpayer_id
236 AND country = p_country_code;
237
238 l_taxid VARCHAR2(30);
239
240 BEGIN
241 IF l_DEBUG in ('Y', 'C') THEN
242 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.CE_VAL_UNIQUE_TAX_PAYER_ID');
243 END IF;
244
245 OPEN CHECK_UNIQUE_TAXID_BK;
246 FETCH CHECK_UNIQUE_TAXID_BK INTO l_taxid;
247
248 IF (CHECK_UNIQUE_TAXID_BK%NOTFOUND) THEN
249 RETURN('TRUE');
250 ELSE
251 RETURN('FALSE');
252 IF l_DEBUG in ('Y', 'C') THEN
253 cep_standard.debug('CE_VAL_UNIQUE_TAX_PAYER_ID false');
254 END IF;
255 END IF;
256 CLOSE CHECK_UNIQUE_TAXID_BK;
257 /*
258 IF l_DEBUG in ('Y', 'C') THEN
259 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.CE_VAL_UNIQUE_TAX_PAYER_ID');
260 END IF;
261 */
262 END CE_VAL_UNIQUE_TAX_PAYER_ID;
263
264 /* --------------------------------------------------------------------
265 | PRIVATE PROCEDURE |
266 | CE_CHECK_CROSS_MODULE_TAX_ID |
267 | |
268 | CALLED BY |
269 | CE_VALIDATE_BANK_CO |
270 | |
271 | DESCRIPTION |
272 | Unique Tax Payer ID VALIDATIONS |
273 | |
274 --------------------------------------------------------------------- */
275 -- Check the cross validation
276 -- This procedure checks in AR, AP and HR to see if the TAX ID entered
277 -- for the Bank is used by a Customer, Supplier or a Company.
278 -- If it is used then the Customer name, Supplier name or the Company
279 -- name should match with the Bank name.
280 -- Depending upon the different combinations different error codes are
281 -- returned
282 -- The messages codes returned are:
283 -- bk1 Tax ID is used by a different Company
284 -- bk2 Tax ID is used by a different Customer
285 -- bk3 Tax ID is used by a different Supplier
286 -- bk4 Bank exists as a Customer with different Tax ID or Tax ID Type
287 -- bk5 Bank exists as a Supplier with different Tax ID or Tax ID Type
288 -- bk6 Bank exists as a Company with different Tax ID or Tax ID Type
289
290 PROCEDURE ce_check_cross_module_tax_id(p_country_code IN VARCHAR2,
291 p_entity_name IN VARCHAR2, --l_bank_name
292 p_taxpayer_id IN VARCHAR2,
293 p_return_ar OUT NOCOPY VARCHAR2,
294 p_return_ap OUT NOCOPY VARCHAR2,
295 p_return_hr OUT NOCOPY VARCHAR2,
296 p_return_bk OUT NOCOPY VARCHAR2) IS
297
298 CURSOR CHECK_CROSS_AP IS --Suppliers
299 SELECT AP.VENDOR_NAME, AP.NUM_1099
300 FROM PO_VENDORS AP
301 WHERE (AP.VENDOR_NAME=p_entity_name
302 OR AP.NUM_1099= p_taxpayer_id)
303 AND substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
304
305 CURSOR CHECK_CROSS_AR IS --Customers
306 SELECT AR.PARTY_NAME, AR.JGZZ_FISCAL_CODE
307 FROM HZ_PARTIES AR
308 WHERE (AR.PARTY_NAME=p_entity_name
309 OR AR.JGZZ_FISCAL_CODE= p_taxpayer_id)
310 AND substrb(nvl(AR.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
311
312 CURSOR CHECK_CROSS_HR IS --Companies
313 SELECT HR.GLOBAL_ATTRIBUTE8, HR.GLOBAL_ATTRIBUTE11
314 FROM HR_LOCATIONS HR
315 WHERE (HR.GLOBAL_ATTRIBUTE8= p_entity_name
316 OR HR.GLOBAL_ATTRIBUTE11= p_taxpayer_id)
317 AND substrb(nvl(HR.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code
318 AND HR.LOCATION_USE = 'HR';
319
320 l_taxid VARCHAR2(30);
321 l_taxid_type VARCHAR2(150);
322 l_entity_name VARCHAR2(80);
323
324 BEGIN
325 IF l_DEBUG in ('Y', 'C') THEN
326 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_check_cross_module_tax_id'||
327 ' p_taxpayer_id: ' || p_taxpayer_id );
328 END IF;
329
330 -- Checking cross module Banks/Customers
331
332 OPEN CHECK_CROSS_AR;
333 FETCH CHECK_CROSS_AR INTO l_entity_name, l_taxid;
334
335 IF l_DEBUG in ('Y', 'C') THEN
336 cep_standard.debug('l_entity_name: ' ||l_entity_name || ' l_taxid: ' ||l_taxid );
337 END IF;
338
339 IF CHECK_CROSS_AR%NOTFOUND THEN
340 p_return_ar:='SUCCESS';
341
342 ELSIF (l_taxid IS NULL AND l_entity_name=p_entity_name) THEN
343 p_return_ar:='SUCCESS';
344
345 ELSIF (l_taxid IS NOT NULL) THEN
346
347 IF (l_entity_name=p_entity_name AND l_taxid=p_taxpayer_id ) THEN
348 p_return_ar:='SUCCESS';
349
350 -- Check if Tax ID is used by a different Customer
351
352 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
353 p_return_ar:='bk2';
354
355 -- Check if Bank exists as Customer with different TAX ID or Tax ID
356 -- Type
357
358 ELSIF (l_entity_name=p_entity_name AND (l_taxid<>p_taxpayer_id )) THEN
359 p_return_ar:='bk4';
360
361 END IF;
362
363 END IF;
364
365 IF l_DEBUG in ('Y', 'C') THEN
366 cep_standard.debug('p_return_ar: ' || p_return_ar );
367 END IF;
368
369 CLOSE CHECK_CROSS_AR;
370
371 -- Checking cross module Banks/Companies
372
373 IF p_country_code='CO' THEN
374 l_taxid_type:='LEGAL_ENTITY';
375 END IF;
376
377 OPEN CHECK_CROSS_HR;
378 FETCH CHECK_CROSS_HR INTO l_entity_name, l_taxid;
379
380 IF CHECK_CROSS_HR%NOTFOUND THEN
381 p_return_hr:='SUCCESS';
382
383 ELSIF (l_taxid IS NULL AND l_entity_name=p_entity_name) THEN
384 p_return_hr:='SUCCESS';
385
386 ELSIF (l_taxid IS NOT NULL) THEN
387
388 IF (l_entity_name=p_entity_name AND l_taxid=p_taxpayer_id ) THEN
389 p_return_hr:='SUCCESS';
390
391 -- Check if Tax ID is used by a different Company
392
393 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
394 p_return_hr:='bk1';
395
396 -- Check if Bank exists as Company with different Tax ID
397
398 ELSIF (l_entity_name=p_entity_name AND (l_taxid<>p_taxpayer_id)) THEN
399 p_return_hr:='bk6';
400
401 END IF;
402
403 END IF;
404
405 CLOSE CHECK_CROSS_HR;
406
407 -- Checking cross module Banks/Suppliers
408
409 OPEN CHECK_CROSS_AP;
410 FETCH CHECK_CROSS_AP INTO l_entity_name, l_taxid;
411
412 IF CHECK_CROSS_AP%NOTFOUND THEN
413 p_return_ap:='SUCCESS';
414
415 ELSIF (l_taxid IS NULL AND l_entity_name=p_entity_name) THEN
416 p_return_ap:='SUCCESS';
417
418 ELSIF (l_taxid IS NOT NULL) THEN
419
420 IF (l_entity_name=p_entity_name AND l_taxid=p_taxpayer_id) THEN
421 p_return_ap:='SUCCESS';
422
423 -- Check if Tax ID is used by a different Supplier
424
425 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
426 p_return_ap:='bk3';
427
428 -- Check if Bank exists as Supplier with different Tax ID
429
430 ELSIF (l_entity_name=p_entity_name AND (l_taxid<>p_taxpayer_id )) THEN
431 p_return_ap:='bk5';
432
433 END IF;
434
435 END IF;
436
437 CLOSE CHECK_CROSS_AP;
438 p_return_bk:='NA';
439
440 /*
441 IF l_DEBUG in ('Y', 'C') THEN
442 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_check_cross_module_tax_id');
443 END IF;
444 */
445 END ce_check_cross_module_tax_id;
446 /* --------------------------------------------------------------------
447 | PRIVATE FUNCTION |
448 | CE_TAX_ID_CHECK_ALGORITHM |
449 | |
450 | CALLED BY |
451 | CE_VALIDATE_BANK_CO |
452 | |
453 | DESCRIPTION |
454 | Unique Tax Payer ID VALIDATIONS |
455 | |
456 --------------------------------------------------------------------- */
457 -- Taxpayer ID Validation
458
459 FUNCTION ce_tax_id_check_algorithm(p_taxpayer_id IN VARCHAR2,
460 p_country IN VARCHAR2,
461 p_tax_id_cd IN VARCHAR2
462 ) RETURN VARCHAR2 IS
463 l_var1 VARCHAR2(20);
464 l_val_digit VARCHAR2(2);
465 l_mod_value NUMBER(2);
466 BEGIN
467
468 IF l_DEBUG in ('Y', 'C') THEN
469 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.CE_TAX_ID_CHECK_ALGORITHM '||p_COUNTRY );
470 END IF;
471
472 -- Check the Taxpayer ID Valdiation digit for Colombia
473
474 IF p_country='CO' THEN
475
476 l_var1:=LPAD(p_taxpayer_id,15,'0');
477 l_mod_value:=(MOD(((TO_NUMBER(SUBSTR(l_var1,15,1))) *3 +
478 (TO_NUMBER(SUBSTR(l_var1,14,1))) *7 +
479 (TO_NUMBER(SUBSTR(l_var1,13,1))) *13 +
480 (TO_NUMBER(SUBSTR(l_var1,12,1))) *17 +
481 (TO_NUMBER(SUBSTR(l_var1,11,1))) *19 +
482 (TO_NUMBER(SUBSTR(l_var1,10,1))) *23 +
483 (TO_NUMBER(SUBSTR(l_var1,9,1))) *29 +
484 (TO_NUMBER(SUBSTR(l_var1,8,1))) *37 +
485 (TO_NUMBER(SUBSTR(l_var1,7,1))) *41 +
486 (TO_NUMBER(SUBSTR(l_var1,6,1))) *43 +
487 (TO_NUMBER(SUBSTR(l_var1,5,1))) *47 +
488 (TO_NUMBER(SUBSTR(l_var1,4,1))) *53 +
489 (TO_NUMBER(SUBSTR(l_var1,3,1))) *59 +
490 (TO_NUMBER(SUBSTR(l_var1,2,1))) *67 +
491 (TO_NUMBER(SUBSTR(l_var1,1,1))) *71),11));
492
493 IF l_DEBUG in ('Y', 'C') THEN
494 cep_standard.debug('l_mod_value: '|| l_mod_value);
495 END IF;
496
497 IF (l_mod_value IN (1,0)) THEN
498 l_val_digit:=l_mod_value;
499 ELSE
500 l_val_digit:=11-l_mod_value;
501 END IF;
502
503 IF l_DEBUG in ('Y', 'C') THEN
504 cep_standard.debug('l_val_digit: '|| l_val_digit|| ' p_tax_id_cd: '|| p_tax_id_cd );
505 END IF;
506
507 IF l_val_digit<> p_tax_id_cd THEN
508 IF l_DEBUG in ('Y', 'C') THEN
509 cep_standard.debug('failed ce_tax_id_check_algorithm' );
510 END IF;
511 RETURN('FALSE');
512
513 ELSE
514 RETURN('TRUE');
515 END IF;
516
517 END IF;
518 /*
519 IF l_DEBUG in ('Y', 'C') THEN
520 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.CE_TAX_ID_CHECK_ALGORITHM');
521 END IF;
522 */
523 END ce_tax_id_check_algorithm;
524
525
526 /* --------------------------------------------------------------------
527 | PRIVATE PROCEDURE |
528 | CE_VALIDATE_CD |
529 | |
530 | CALLED BY |
531 | CE_VALIDATE_ACCOUNT |
532 | |
533 | CALLS |
534 | CE_VALIDATE_CD_* for each country |
535 --------------------------------------------------------------------- */
536
537
538 PROCEDURE CE_VALIDATE_CD(X_COUNTRY_NAME IN varchar2,
539 X_CD IN varchar2,
540 X_BANK_NUMBER IN varchar2,
541 X_BRANCH_NUMBER IN varchar2,
542 X_ACCOUNT_NUMBER IN varchar2) AS
543 --p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
544 --x_msg_count OUT NOCOPY NUMBER,
545 --x_msg_data OUT NOCOPY VARCHAR2) AS
546 --X_VALUE_OUT OUT NOCOPY varchar2) AS
547
548 COUNTRY_NAME VARCHAR2(2);
549
550 X_PASS_MAND_CHECK VARCHAR2(1);
551
552 BEGIN
553 IF l_DEBUG in ('Y', 'C') THEN
554 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd X_COUNTRY_NAME: ' ||X_COUNTRY_NAME);
555 END IF;
556
557 COUNTRY_NAME := X_COUNTRY_NAME;
558
559 --X_VALUE_OUT := X_CD;
560
561
562 /*
563 IF l_DEBUG in ('Y', 'C') THEN
564 cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME);
565 --'CE_VALIDATE_BANKINFO_UPG.ce_validate_cd - X_VALUE_OUT: '|| X_VALUE_OUT|| '----------' ||
566 --'CE_VALIDATE_BANKINFO_UPG.ce_validate_cd - P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
567 END IF;
568 */
569 /* removed 9/2/03
570 -- Initialize message list if p_init_msg_list is set to TRUE.
571 IF FND_API.to_Boolean(p_init_msg_list) THEN
572 FND_MSG_PUB.initialize;
573 END IF;
574 */
575 /* We must validate the Check Digit*/
576
577 IF X_CD is null
578 AND (X_BANK_NUMBER is not null
579 OR X_BRANCH_NUMBER is not null
580 OR X_ACCOUNT_NUMBER is not null)
581 then
582
583 X_PASS_MAND_CHECK := 'F';
584
585 ELSIF X_CD is not null then
586 X_PASS_MAND_CHECK := 'P';
587 ELSE
588 X_PASS_MAND_CHECK := ' ';
589
590 END IF;
591
592 IF (COUNTRY_NAME = 'FR') then
593
594 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_FR(X_CD,
595 X_PASS_MAND_CHECK,
596 X_BANK_NUMBER,
597 X_BRANCH_NUMBER,
598 translate(X_ACCOUNT_NUMBER,
599 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
600 '123456789123456789234567890') );
601
602 ELSIF (COUNTRY_NAME = 'ES') then
603
604 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_ES(X_CD,
605 X_PASS_MAND_CHECK,
606 X_BANK_NUMBER,
607 X_BRANCH_NUMBER,
608 translate(X_ACCOUNT_NUMBER,
609 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
610 '123456789123456789234567890') );
611
612 ELSIF (COUNTRY_NAME = 'PT') then
613
614 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_PT(X_CD,
615 X_PASS_MAND_CHECK,
616 X_BANK_NUMBER,
617 X_BRANCH_NUMBER,
618 translate(X_ACCOUNT_NUMBER,
619 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
620 '123456789123456789234567890') );
621
622 -- added 5/14/02
623
624 ELSIF (COUNTRY_NAME = 'DE') then
625
626 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_DE(X_CD,
627 X_ACCOUNT_NUMBER);
628
629 ELSIF (COUNTRY_NAME = 'GR') then
630
631 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_GR(X_CD,
632 X_PASS_MAND_CHECK,
633 X_BANK_NUMBER,
634 X_BRANCH_NUMBER,
635 translate(X_ACCOUNT_NUMBER,
636 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
637 '123456789123456789234567890') );
638
639 ELSIF (COUNTRY_NAME = 'IS') then
640
641 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_IS(X_CD,
642 translate(X_ACCOUNT_NUMBER,
643 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
644 '123456789123456789234567890') );
645
646 ELSIF (COUNTRY_NAME = 'IT') then
647
648 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_IT(X_CD,
649 X_PASS_MAND_CHECK,
650 X_BANK_NUMBER,
651 X_BRANCH_NUMBER,
652 translate(X_ACCOUNT_NUMBER,
653 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
654 '123456789123456789234567890') );
655
656 ELSIF (COUNTRY_NAME = 'LU') then
657
658 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_LU(X_CD,
659 X_BANK_NUMBER,
660 X_BRANCH_NUMBER,
661 X_ACCOUNT_NUMBER);
662 -- X_VALUE_OUT);
663
664 ELSIF (COUNTRY_NAME = 'SE') then
665
666 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_SE(X_CD,
667 X_ACCOUNT_NUMBER);
668
669
670 END IF;
671
672 /* 9/2/03
673 FND_MSG_PUB.Count_And_Get(
674 p_encoded => FND_API.G_FALSE,
675 p_count => x_msg_count,
676 p_data => x_msg_data);
677 */
678 IF l_DEBUG in ('Y', 'C') THEN
679 --cep_standard.debug('CE_VALIDATE_BANKINFO_UPG.ce_validate_cd - P_COUNT: '|| x_msg_count|| '----------' ||
680 --'CE_VALIDATE_BANKINFO_UPG.ce_validate_cd - P_DATA: '|| x_msg_data|| '----------' ||
681 --'CE_VALIDATE_BANKINFO_UPG.ce_validate_cd - X_VALUE_OUT: '|| X_VALUE_OUT|| '----------' ||
682 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
683 END IF;
684 EXCEPTION
685 WHEN OTHERS THEN
686 IF l_DEBUG in ('Y', 'C') THEN
687 cep_standard.debug('CE_VALIDATE_BANKINFO_UPG.ce_validate_cd ' ||X_COUNTRY_NAME );
688 END IF;
689
690 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
691 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO_UPG.cd_validate_cd');
692 fnd_msg_pub.add;
693 RAISE;
694
695
696 END CE_VALIDATE_CD;
697
698
699 /* --------------------------------------------------------------------
700 | PRIVATE PROCEDURE |
701 | CE_VALIDATE_BRANCH |
702 | |
703 | CALLED BY |
704 | ceupgcpy.sql - Bank pre-upgrade script |
705 | |
706 | CALLS |
707 | CE_VALIDATE_BRANCH_* for each country |
708 | removed CE_VALIDATE_UNIQUE_BRANCH_* for each country |
709 --------------------------------------------------------------------- */
710
711
712 PROCEDURE CE_VALIDATE_BRANCH(X_COUNTRY_NAME IN varchar2,
713 --X_BANK_NUMBER IN varchar2,
714 X_BRANCH_NUMBER IN varchar2,
715 --X_BANK_NAME IN varchar2,
716 --X_BRANCH_NAME IN varchar2,
717 X_BRANCH_NAME_ALT IN varchar2,
718 X_BANK_ID IN NUMBER,
719 --X_BRANCH_ID IN NUMBER,
720 X_VALIDATION_TYPE IN varchar2 := 'ALL',
721 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2,
724 X_VALUE_OUT OUT NOCOPY varchar2,
725 x_message_name_all OUT NOCOPY varchar2)
726 AS
727
728 COUNTRY_NAME VARCHAR2(2);
729
730 X_PASS_MAND_CHECK VARCHAR2(1);
731
732 l_msg_data varchar2(2000);
733 l_msg_index_out number;
734 l_error varchar2(2000);
735 l_error_description varchar2(4000);
736 l_message_name varchar2(2000);
737 --x_message_name_all varchar2(2000);
738 l_message_name_all_tmp varchar2(2000);
739 v_error_description varchar2(2000);
740 l_app_short_name varchar2(20);
741
742
743 BEGIN
744 IF l_DEBUG in ('Y', 'C') THEN
745 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch');
746 END IF;
747
748
749 COUNTRY_NAME := X_COUNTRY_NAME;
750
751 X_VALUE_OUT := X_BRANCH_NUMBER;
752
753 -- Initialize message list if p_init_msg_list is set to TRUE.
754 IF FND_API.to_Boolean(p_init_msg_list) THEN
755 FND_MSG_PUB.initialize;
756 END IF;
757
758 IF l_DEBUG in ('Y', 'C') THEN
759 cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME
760 || ' X_VALUE_OUT: '|| X_VALUE_OUT || ' P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST );
761 END IF;
762
763
764 /* X_COUNTRY_NAME cannot be null, not able to make this required on form, so must do validation here */
765
766 IF X_COUNTRY_NAME is null then
767 IF l_DEBUG in ('Y', 'C') THEN
768 cep_standard.debug('CE_VALIDATE_BRANCH: ' || 'CE_COUNTRY_NAME_REQUIRED');
769 END IF;
770 fnd_message.set_name ('CE', 'CE_COUNTRY_NAME_REQUIRED');
771 fnd_msg_pub.add;
772 END IF;
773
774
775 /* We must validate the Bank Branch Number */
776
777 IF X_BRANCH_NUMBER is null then
778 X_PASS_MAND_CHECK := 'F';
779 ELSE
780 X_PASS_MAND_CHECK := 'P';
781 END IF;
782
783 IF l_DEBUG in ('Y', 'C') THEN
784 IF (X_PASS_MAND_CHECK <> 'P') THEN
785 cep_standard.debug('X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK);
786 END IF;
787 END IF;
788
789 -- VALIDATION TYPE: KEY, ALL
790
791 IF (COUNTRY_NAME = 'AT')
792 THEN
793 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_AT(X_BRANCH_NUMBER,
794 X_PASS_MAND_CHECK,
795 X_VALUE_OUT);
796
797 ELSIF (COUNTRY_NAME = 'ES')
798 then
799 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_ES(X_BRANCH_NUMBER,
800 X_PASS_MAND_CHECK,
801 X_VALUE_OUT);
802
803 ELSIF (COUNTRY_NAME = 'FR')
804 then
805 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_FR(X_BRANCH_NUMBER,
806 X_PASS_MAND_CHECK,
807 X_VALUE_OUT);
808
809 ELSIF (COUNTRY_NAME = 'PT')
810 then
811 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_PT(X_BRANCH_NUMBER,
812 X_PASS_MAND_CHECK);
813
814 ELSIF (COUNTRY_NAME = 'BR')
815 then
816 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_BR(X_BRANCH_NUMBER,
817 X_PASS_MAND_CHECK,
818 X_VALUE_OUT);
819
820 -- added 5/14/02
821
822 ELSIF (COUNTRY_NAME = 'DE')
823 then
824 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_DE(X_BRANCH_NUMBER,
825 X_BANK_ID);
826
827 ELSIF (COUNTRY_NAME = 'GR')
828 then
829 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_GR(X_BRANCH_NUMBER);
830
831 ELSIF (COUNTRY_NAME = 'IS')
832 then
833 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_IS(X_BRANCH_NUMBER,
834 X_BANK_ID,
835 X_VALUE_OUT);
836
837 ELSIF (COUNTRY_NAME = 'IE')
838 then
839 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_IE(X_BRANCH_NUMBER,
840 X_BANK_ID);
841
842 ELSIF (COUNTRY_NAME = 'IT')
843 then
844 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_IT(X_BRANCH_NUMBER,
845 X_PASS_MAND_CHECK);
846
847 ELSIF (COUNTRY_NAME = 'LU')
848 then
849 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_LU(X_BRANCH_NUMBER,
850 X_BANK_ID);
851
852 ELSIF (COUNTRY_NAME = 'PL')
853 then
854 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_PL(X_BRANCH_NUMBER,
855 X_BANK_ID);
856
857 ELSIF (COUNTRY_NAME = 'SE')
858 then
859 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_SE(X_BRANCH_NUMBER,
860 X_BANK_ID);
861
862 ELSIF (COUNTRY_NAME = 'CH')
863 then
864 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_CH(X_BRANCH_NUMBER,
865 X_BANK_ID);
866
867 ELSIF (COUNTRY_NAME = 'GB')
868 then
869 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_GB(X_BRANCH_NUMBER,
870 X_BANK_ID);
871
872 ELSIF (COUNTRY_NAME = 'US')
873 then
874 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BRANCH_US(X_BRANCH_NUMBER,
875 X_PASS_MAND_CHECK,
876 X_VALUE_OUT);
877 -- added 10/19/04
878
879 ELSIF (COUNTRY_NAME = 'AU')
880 then
881 CE_VALIDATE_BRANCH_AU(X_BRANCH_NUMBER,
882 X_BANK_ID,
883 X_PASS_MAND_CHECK);
884 ELSIF (COUNTRY_NAME = 'IL')
885 then
886 CE_VALIDATE_BRANCH_IL(X_BRANCH_NUMBER,
887 X_PASS_MAND_CHECK);
888 ELSIF (COUNTRY_NAME = 'NZ')
889 then
890 CE_VALIDATE_BRANCH_NZ(X_BRANCH_NUMBER,
891 X_PASS_MAND_CHECK);
892
893 ELSIF (COUNTRY_NAME = 'JP')
894 then
895 CE_VALIDATE_BRANCH_JP(X_BRANCH_NUMBER,
896 X_BRANCH_NAME_ALT,
897 X_PASS_MAND_CHECK,
898 X_VALIDATION_TYPE );
899
900 END IF;
901
902
903 /** UNIQUE VALIDATION CHECK for branch **/
904 -- NO UNIQUE VALIDATION CHECK
905 /*
906 IF l_DEBUG in ('Y', 'C') THEN
907 cep_standard.debug('UNIQUE VALIDATION CHECK for branch' );
908 END IF;
909
910 IF (COUNTRY_NAME = 'DE') THEN
911 IF l_DEBUG in ('Y', 'C') THEN
912 cep_standard.debug('call CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH_DE' );
913 END IF;
914
915 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH_DE(X_BRANCH_NUMBER,
916 X_BRANCH_NAME,
917 X_BANK_ID,
918 X_BRANCH_ID);
919
920 ELSIF (COUNTRY_NAME = 'US') THEN
921 IF l_DEBUG in ('Y', 'C') THEN
922 cep_standard.debug('CE_VALIDATE_BRANCH: ' || 'call CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH_US' );
923 END IF;
924
925 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH_US(X_BRANCH_NUMBER,
926 X_BRANCH_NAME,
927 X_BANK_ID,
928 X_BRANCH_ID);
929
930 ELSIF (COUNTRY_NAME = 'JP') THEN
931 IF l_DEBUG in ('Y', 'C') THEN
932 cep_standard.debug('call CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH_JP' );
933 END IF;
934
935 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH_JP(X_COUNTRY_NAME,
936 X_BRANCH_NUMBER,
937 X_BRANCH_NAME,
938 X_BRANCH_NAME_ALT,
939 X_BANK_ID,
940 X_BRANCH_ID);
941 ELSE
942 IF l_DEBUG in ('Y', 'C') THEN
943 cep_standard.debug('call CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH' );
944 END IF;
945 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BRANCH(X_COUNTRY_NAME,
946 X_BRANCH_NUMBER,
947 X_BRANCH_NAME,
948 X_BANK_ID,
949 X_BRANCH_ID);
950
951
952 END IF;
953
954 */
955 /** end country unique check for branch **/
956
957 --cep_standard.debug('call FND_MSG_PUB.Count_And_Get');
958 --cep_standard.debug('x_msg_count= '|| x_msg_count);
959 --cep_standard.debug('x_msg_data= '|| x_msg_data);
960
961 FND_MSG_PUB.Count_And_Get(
962 p_encoded => FND_API.G_FALSE,
963 p_count => x_msg_count,
964 p_data => x_msg_data);
965
966 --cep_standard.debug('call FND_MSG_PUB.Count_Msg');
967
968 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
969 FND_MSG_PUB.Get(
970 p_msg_index=>j,
971 p_encoded=>'T',
972 p_data=>x_msg_data,
973 p_msg_index_out=>l_msg_index_out);
974 v_error_description := l_error_description || ':' || x_msg_data;
975
976 FND_MESSAGE.PARSE_ENCODED(x_msg_data,l_app_short_name,l_message_name);
977
978 --cep_standard.debug('l_message_name: '|| l_message_name );
979 l_message_name_all_tmp:=(l_message_name_all_tmp ||','|| l_message_name);
980
981 END LOOP;
982 x_message_name_all:=substr(l_message_name_all_tmp,2);
983 cep_standard.debug('x_message_name_all: '|| x_message_name_all );
984
985
986 IF l_DEBUG in ('Y', 'C') THEN
987 cep_standard.debug('<<BRANCH: X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK ||' P_COUNT: '
988 || x_msg_count ||' X_VALUE_OUT: '|| X_VALUE_OUT );
989 --cep_standard.debug('P_DATA: '|| x_msg_data );
990 --cep_standard.debug('CE_VALIDATE_BRANCH: ' || 'CE_VALIDATE_BANKINFO_UPG.ce_validate_branch - X_VALUE_OUT: '|| X_VALUE_OUT);
991 --cep_standard.debug('CE_VALIDATE_BRANCH: ' || '<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch');
992 END IF;
993 EXCEPTION
994 WHEN OTHERS THEN
995 IF l_DEBUG in ('Y', 'C') THEN
996 cep_standard.debug('CE_VALIDATE_BRANCH: ' || 'CE_VALIDATE_BANKINFO_UPG.ce_validate_branch ' ||X_COUNTRY_NAME );
997 END IF;
998
999 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1000 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO_UPG.cd_validate_branch');
1001 fnd_msg_pub.add;
1002 RAISE;
1003
1004 END CE_VALIDATE_BRANCH;
1005
1006
1007 /* --------------------------------------------------------------------
1008 | PRIVATE PROCEDURE |
1009 | CE_VALIDATE_ACCOUNT |
1010 | |
1011 | CALLED BY |
1012 | ceupgcpy.sql - Bank pre-upgrade script |
1013 | |
1014 | CALLS |
1015 | CE_VALIDATE_ACCOUNT_* for each country |
1016 | removed CE_VALIDATE_UNIQUE_ACCOUNT_* for each country |
1017 --------------------------------------------------------------------- */
1018
1019
1020 PROCEDURE CE_VALIDATE_ACCOUNT(X_COUNTRY_NAME IN varchar2,
1021 X_BANK_NUMBER IN varchar2,
1022 X_BRANCH_NUMBER IN varchar2,
1023 X_ACCOUNT_NUMBER IN varchar2,
1024 --X_BANK_ID IN number,
1025 --X_BRANCH_ID IN number,
1026 --X_ACCOUNT_ID IN number,
1027 --X_CURRENCY_CODE IN varchar2,
1028 X_ACCOUNT_TYPE IN varchar2,
1029 X_ACCOUNT_SUFFIX IN varchar2,
1030 X_SECONDARY_ACCOUNT_REFERENCE IN varchar2,
1031 X_ACCOUNT_NAME IN varchar2,
1032 X_CD IN varchar2,
1033 X_VALIDATION_TYPE IN varchar2 := 'ALL',
1034 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1035 x_msg_count OUT NOCOPY NUMBER,
1036 x_msg_data OUT NOCOPY VARCHAR2,
1037 X_VALUE_OUT OUT NOCOPY varchar2,
1038 x_message_name_all OUT NOCOPY varchar2)
1039 AS
1040
1041 COUNTRY_NAME VARCHAR2(2);
1042
1043 X_PASS_MAND_CHECK VARCHAR2(1);
1044 l_msg_data varchar2(2000);
1045 l_msg_index_out number;
1046 l_error varchar2(2000);
1047 l_error_description varchar2(4000);
1048 l_message_name varchar2(2000);
1049 --x_message_name_all varchar2(2000);
1050 l_message_name_all_tmp varchar2(2000);
1051 v_error_description varchar2(2000);
1052 l_app_short_name varchar2(20);
1053
1054
1055 BEGIN
1056 IF l_DEBUG in ('Y', 'C') THEN
1057 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account');
1058 END IF;
1059
1060 COUNTRY_NAME := X_COUNTRY_NAME;
1061 X_VALUE_OUT := X_ACCOUNT_NUMBER;
1062
1063 -- Initialize message list if p_init_msg_list is set to TRUE.
1064
1065 IF FND_API.to_Boolean(p_init_msg_list) THEN
1066 FND_MSG_PUB.initialize;
1067 END IF;
1068
1069 IF l_DEBUG in ('Y', 'C') THEN
1070 cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME
1071 ||' X_VALUE_OUT: '|| X_VALUE_OUT || ' P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
1072 END IF;
1073
1074
1075 /* X_COUNTRY_NAME cannot be null, not able to make this required on form, so must do validation here */
1076
1077 IF X_COUNTRY_NAME is null then
1078 IF l_DEBUG in ('Y', 'C') THEN
1079 cep_standard.debug('CE_VALIDATE_ACCOUNT: ' || 'CE_COUNTRY_NAME_REQUIRED');
1080 END IF;
1081 fnd_message.set_name ('CE', 'CE_COUNTRY_NAME_REQUIRED');
1082 fnd_msg_pub.add;
1083 END IF;
1084
1085
1086 /* We must validate the Bank Account Number */
1087
1088 IF X_ACCOUNT_NUMBER is null then
1089 X_PASS_MAND_CHECK := 'F';
1090 ELSE
1091 X_PASS_MAND_CHECK := 'P';
1092 END IF;
1093
1094 -- VALIDATION TYPE: KEY, ALL
1095
1096 IF (COUNTRY_NAME = 'AT')
1097 then
1098 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_AT(X_ACCOUNT_NUMBER,
1099 X_PASS_MAND_CHECK,
1100 X_VALUE_OUT);
1101
1102 ELSIF (COUNTRY_NAME = 'DK')
1103 then
1104 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_DK(X_ACCOUNT_NUMBER,
1105 X_PASS_MAND_CHECK,
1106 X_VALUE_OUT);
1107
1108 ELSIF (COUNTRY_NAME = 'NO')
1109 then
1110 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_NO(X_ACCOUNT_NUMBER,
1111 X_PASS_MAND_CHECK);
1112
1113 ELSIF (COUNTRY_NAME = 'ES')
1114 then
1115
1116 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_ES(X_ACCOUNT_NUMBER,
1117 X_PASS_MAND_CHECK,
1118 X_VALUE_OUT);
1119
1120
1121 ELSIF (COUNTRY_NAME = 'NL')
1122 then
1123
1124 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_NL(X_ACCOUNT_NUMBER,
1125 X_PASS_MAND_CHECK);
1126
1127 ELSIF (COUNTRY_NAME = 'FR')
1128 then
1129
1130 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_FR(X_ACCOUNT_NUMBER,
1131 X_PASS_MAND_CHECK,
1132 X_VALUE_OUT);
1133
1134
1135 ELSIF (COUNTRY_NAME = 'BE')
1136 then
1137
1138 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_BE(X_ACCOUNT_NUMBER,
1139 X_PASS_MAND_CHECK);
1140
1141
1142 ELSIF (COUNTRY_NAME = 'PT')
1143 then
1144
1145 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_PT(X_ACCOUNT_NUMBER,
1146 X_PASS_MAND_CHECK,
1147 X_VALUE_OUT);
1148
1149 ELSIF (COUNTRY_NAME = 'FI')
1150 AND (X_BRANCH_NUMBER='LMP')
1151 then
1152
1153 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_FI(X_ACCOUNT_NUMBER,
1154 X_PASS_MAND_CHECK);
1155
1156 -- added 5/14/02
1157
1158 ELSIF (COUNTRY_NAME = 'DE')
1159 then
1160
1161 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_DE(X_ACCOUNT_NUMBER,
1162 X_VALUE_OUT );
1163
1164 ELSIF (COUNTRY_NAME = 'GR')
1165 then
1166
1167 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_GR(X_ACCOUNT_NUMBER,
1168 X_VALUE_OUT);
1169
1170 ELSIF (COUNTRY_NAME = 'IS')
1171 then
1172
1173 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_IS(X_ACCOUNT_NUMBER,
1174 X_VALUE_OUT );
1175
1176
1177 ELSIF (COUNTRY_NAME = 'IE')
1178 then
1179
1180 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_IE(X_ACCOUNT_NUMBER);
1181
1182 ELSIF (COUNTRY_NAME = 'IT')
1183 then
1184 IF l_DEBUG in ('Y', 'C') THEN
1185 cep_standard.debug('X_ACCOUNT_NUMBER : '|| X_ACCOUNT_NUMBER||
1186 ' X_VALUE_OUT : '|| X_VALUE_OUT);
1187 END IF;
1188
1189 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_IT(X_ACCOUNT_NUMBER,
1190 X_VALUE_OUT);
1191
1192 ELSIF (COUNTRY_NAME = 'LU')
1193 then
1194
1195 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_LU(X_ACCOUNT_NUMBER);
1196
1197
1198 ELSIF (COUNTRY_NAME = 'PL')
1199 then
1200
1201 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_PL(X_ACCOUNT_NUMBER);
1202
1203
1204 ELSIF (COUNTRY_NAME = 'SE')
1205 then
1206
1207 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_SE(X_ACCOUNT_NUMBER);
1208
1209
1210 ELSIF (COUNTRY_NAME = 'CH')
1211 then
1212
1213 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_CH(X_ACCOUNT_NUMBER,
1214 X_ACCOUNT_TYPE,
1215 X_VALIDATION_TYPE );
1216
1217 ELSIF (COUNTRY_NAME = 'GB')
1218 then
1219
1220 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_GB(X_ACCOUNT_NUMBER,
1221 X_VALUE_OUT);
1222
1223 -- added 10/19/04
1224 ELSIF (COUNTRY_NAME = 'AU')
1225 then
1226
1227 CE_VALIDATE_ACCOUNT_AU(X_ACCOUNT_NUMBER);
1228
1229
1230 ELSIF (COUNTRY_NAME = 'IL')
1231 then
1232 CE_VALIDATE_ACCOUNT_IL(X_ACCOUNT_NUMBER);
1233
1234 ELSIF (COUNTRY_NAME = 'NZ')
1235 then
1236
1237 CE_VALIDATE_ACCOUNT_NZ(X_ACCOUNT_NUMBER,
1238 X_ACCOUNT_SUFFIX,
1239 X_VALIDATION_TYPE);
1240 ELSIF (COUNTRY_NAME = 'JP')
1241 then
1242 CE_VALIDATE_ACCOUNT_JP(X_ACCOUNT_NUMBER,
1243 X_ACCOUNT_TYPE,
1244 X_VALIDATION_TYPE );
1245
1246 END IF; /** country account check **/
1247
1248
1249 IF (X_VALIDATION_TYPE = 'ALL') THEN
1250
1251 IF (COUNTRY_NAME = 'BR')
1252 then
1253
1254 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_BR(X_ACCOUNT_NUMBER,
1255 X_SECONDARY_ACCOUNT_REFERENCE);
1256 END IF;
1257
1258 END IF;
1259
1260 IF (X_VALIDATION_TYPE in ('KEY', 'ALL')) THEN
1261
1262 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD(X_COUNTRY_NAME,
1263 X_CD ,
1264 X_BANK_NUMBER ,
1265 X_BRANCH_NUMBER ,
1266 X_ACCOUNT_NUMBER);
1267 END IF;
1268
1269
1270 /** UNIQUE VALIDATION CHECK for account **/
1271
1272 -- NO UNIQUE VALIDATION CHECK
1273
1274 /*
1275 IF l_DEBUG in ('Y', 'C') THEN
1276 cep_standard.debug('UNIQUE_VALIDATION CHECK for account');
1277 END IF;
1278
1279 IF (COUNTRY_NAME = 'JP') THEN
1280 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_ACCOUNT_JP(X_ACCOUNT_NUMBER,
1281 X_CURRENCY_CODE,
1282 X_ACCOUNT_TYPE,
1283 X_ACCOUNT_NAME,
1284 X_BRANCH_ID,
1285 X_ACCOUNT_ID);
1286
1287 ELSIF (COUNTRY_NAME = 'NZ') THEN
1288 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_ACCOUNT_NZ(X_ACCOUNT_NUMBER,
1289 X_CURRENCY_CODE,
1290 X_ACCOUNT_SUFFIX,
1291 X_ACCOUNT_NAME,
1292 X_BRANCH_ID,
1293 X_ACCOUNT_ID);
1294 ELSE
1295 IF l_DEBUG in ('Y', 'C') THEN
1296 cep_standard.debug('call CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_ACCOUNT' );
1297 END IF;
1298 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_ACCOUNT(
1299 X_ACCOUNT_NUMBER,
1300 X_CURRENCY_CODE,
1301 X_ACCOUNT_NAME,
1302 X_BRANCH_ID,
1303 X_ACCOUNT_ID);
1304
1305 END IF;
1306 */
1307
1308 FND_MSG_PUB.Count_And_Get(
1309 p_encoded => FND_API.G_FALSE,
1310 p_count => x_msg_count,
1311 p_data => x_msg_data);
1312
1313 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
1314 FND_MSG_PUB.Get(
1315 p_msg_index=>j,
1316 p_encoded=>'T',
1317 p_data=>x_msg_data,
1318 p_msg_index_out=>l_msg_index_out);
1319 v_error_description := l_error_description || ':' || x_msg_data;
1320
1321 FND_MESSAGE.PARSE_ENCODED(x_msg_data,l_app_short_name,l_message_name);
1322
1323 --cep_standard.debug('l_message_name: '|| l_message_name );
1324 l_message_name_all_tmp:=(l_message_name_all_tmp ||','|| l_message_name);
1325
1326 END LOOP;
1327 x_message_name_all:=substr(l_message_name_all_tmp,2);
1328 cep_standard.debug('x_message_name_all: '|| x_message_name_all );
1329
1330
1331 IF l_DEBUG in ('Y', 'C') THEN
1332 cep_standard.debug('<<ACCOUNT: X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK ||
1333 ' P_COUNT: '|| x_msg_count || ' X_VALUE_OUT: '|| X_VALUE_OUT);
1334 --cep_standard.debug('P_DATA: '|| x_msg_data );
1335 --cep_standard.debug('CE_VALIDATE_ACCOUNT: ' || 'CE_VALIDATE_BANKINFO_UPG.ce_validate_account - X_VALUE_OUT: '|| X_VALUE_OUT);
1336 --cep_standard.debug('CE_VALIDATE_ACCOUNT: ' || '<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account');
1337 END IF;
1338 EXCEPTION
1339 WHEN OTHERS THEN
1340
1341 IF l_DEBUG in ('Y', 'C') THEN
1342 cep_standard.debug('CE_VALIDATE_ACCOUNT: ' || 'CE_VALIDATE_BANKINFO_UPG.ce_validate_account ' ||X_COUNTRY_NAME );
1343 END IF;
1344 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1345 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO_UPG.cd_validate_account');
1346 fnd_msg_pub.add;
1347 RAISE;
1348
1349 END CE_VALIDATE_ACCOUNT;
1350
1351 /* --------------------------------------------------------------------
1352 | PRIVATE PROCEDURE |
1353 | CE_VALIDATE_BANK |
1354 | |
1355 | CALLED BY |
1356 | ceupgcpy.sql - Bank pre-upgrade script |
1357 | |
1358 | CALLS |
1359 | CE_VALIDATE_BANK_* for each country |
1360 | removed CE_VALIDATE_UNIQUE_BANK_* for each country |
1361 --------------------------------------------------------------------- */
1362
1363
1364 PROCEDURE CE_VALIDATE_BANK( X_COUNTRY_NAME IN varchar2,
1365 X_BANK_NUMBER IN varchar2,
1366 X_BANK_NAME IN varchar2,
1367 X_BANK_NAME_ALT IN varchar2,
1368 X_TAX_PAYER_ID IN varchar2,
1369 --X_BANK_ID IN NUMBER,
1370 X_VALIDATION_TYPE IN varchar2 := 'ALL',
1371 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1372 x_msg_count OUT NOCOPY NUMBER,
1373 x_msg_data OUT NOCOPY VARCHAR2,
1374 X_VALUE_OUT OUT NOCOPY varchar2,
1375 x_message_name_all OUT NOCOPY varchar2 ) AS
1376
1377 COUNTRY_NAME VARCHAR2(2);
1378
1379 X_PASS_MAND_CHECK VARCHAR2(1);
1380
1381 l_msg_data varchar2(2000);
1382 l_msg_index_out number;
1383 l_error varchar2(2000);
1384 l_error_description varchar2(4000);
1385 l_message_name varchar2(2000);
1386 --x_message_name_all varchar2(2000);
1387 l_message_name_all_tmp varchar2(2000);
1388 v_error_description varchar2(2000);
1389 l_app_short_name varchar2(20);
1390
1391 BEGIN
1392 IF l_DEBUG in ('Y', 'C') THEN
1393 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank');
1394 END IF;
1395
1396 COUNTRY_NAME := X_COUNTRY_NAME;
1397 X_VALUE_OUT := X_BANK_NUMBER;
1398
1399 -- Initialize message list if p_init_msg_list is set to TRUE.
1400 IF FND_API.to_Boolean(p_init_msg_list) THEN
1401 FND_MSG_PUB.initialize;
1402 END IF;
1403
1404 IF l_DEBUG in ('Y', 'C') THEN
1405 cep_standard.debug('COUNTRY_NAME: '|| COUNTRY_NAME||
1406 ' X_VALUE_OUT: '|| X_VALUE_OUT || ' P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
1407 END IF;
1408
1409 /* X_COUNTRY_NAME cannot be null, not able to make this required on form, so must do validation here */
1410
1411 IF X_COUNTRY_NAME is null then
1412 IF l_DEBUG in ('Y', 'C') THEN
1413 cep_standard.debug('CE_VALIDATE_BANK: ' || 'CE_COUNTRY_NAME_REQUIRED');
1414 END IF;
1415 fnd_message.set_name ('CE', 'CE_COUNTRY_NAME_REQUIRED');
1416 fnd_msg_pub.add;
1417 END IF;
1418
1419 /* We must validate the Bank Number */
1420
1421 IF X_BANK_NUMBER is null then
1422 X_PASS_MAND_CHECK := 'F';
1423 ELSE
1424 X_PASS_MAND_CHECK := 'P';
1425 END IF;
1426
1427 -- VALIDATION TYPE: KEY, ALL
1428
1429 IF (COUNTRY_NAME = 'ES')
1430 then
1431 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_ES(X_BANK_NUMBER,
1432 X_PASS_MAND_CHECK,
1433 X_VALUE_OUT);
1434
1435 ELSIF (COUNTRY_NAME = 'FR')
1436 then
1437 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_FR(X_BANK_NUMBER,
1438 X_PASS_MAND_CHECK,
1439 X_VALUE_OUT);
1440
1441 ELSIF (COUNTRY_NAME = 'PT')
1442 then
1443 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_PT(X_BANK_NUMBER,
1444 X_PASS_MAND_CHECK);
1445
1446 ELSIF (COUNTRY_NAME = 'BR')
1447 then
1448 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_BR(X_BANK_NUMBER,
1449 X_PASS_MAND_CHECK,
1450 X_VALUE_OUT);
1451
1452 -- Added 5/14/02
1453
1454
1455 ELSIF (COUNTRY_NAME = 'DE')
1456 then
1457 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_DE(X_BANK_NUMBER);
1458
1459 ELSIF (COUNTRY_NAME = 'GR')
1460 then
1461 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_GR(X_BANK_NUMBER);
1462
1463 ELSIF (COUNTRY_NAME = 'IS')
1464 then
1465 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_IS(X_BANK_NUMBER,
1466 X_VALUE_OUT);
1467
1468 ELSIF (COUNTRY_NAME = 'IE')
1469 then
1470 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_IE(X_BANK_NUMBER);
1471
1472 ELSIF (COUNTRY_NAME = 'IT')
1473 then
1474 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_IT(X_BANK_NUMBER,
1475 X_PASS_MAND_CHECK);
1476
1477 ELSIF (COUNTRY_NAME = 'LU')
1478 then
1479 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_LU(X_BANK_NUMBER);
1480
1481
1482 ELSIF (COUNTRY_NAME = 'PL')
1483 then
1484 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_PL(X_BANK_NUMBER);
1485
1486 ELSIF (COUNTRY_NAME = 'SE')
1487 then
1488 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_SE(X_BANK_NUMBER);
1489
1490 ELSIF (COUNTRY_NAME = 'CH')
1491 then
1492 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_CH(X_BANK_NUMBER);
1493
1494 ELSIF (COUNTRY_NAME = 'GB')
1495 then
1496 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_GB(X_BANK_NUMBER);
1497
1498 -- Added 10/19/04
1499
1500 ELSIF (COUNTRY_NAME = 'AU')
1501 then
1502 CE_VALIDATE_BANK_AU(X_BANK_NUMBER);
1503 --CE_VALIDATE_BANKINFO.CE_VALIDATE_BANK_AU(X_BANK_NUMBER);
1504 ELSIF (COUNTRY_NAME = 'IL')
1505 then
1506 CE_VALIDATE_BANK_IL(X_BANK_NUMBER,
1507 X_PASS_MAND_CHECK);
1508 ELSIF (COUNTRY_NAME = 'NZ')
1509 then
1510 CE_VALIDATE_BANK_NZ(X_BANK_NUMBER,
1511 X_PASS_MAND_CHECK);
1512
1513 ELSIF (COUNTRY_NAME = 'JP')
1514 then
1515 CE_VALIDATE_BANK_JP(X_BANK_NUMBER,
1516 X_BANK_NAME_ALT,
1517 X_PASS_MAND_CHECK,
1518 X_VALIDATION_TYPE);
1519
1520
1521 END IF; /** country check for bank **/
1522
1523
1524 IF (X_VALIDATION_TYPE = 'ALL') THEN
1525 IF (COUNTRY_NAME = 'CO')
1526 then
1527 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_CO(X_COUNTRY_NAME,
1528 X_BANK_NAME ,
1529 X_TAX_PAYER_ID);
1530 END IF;
1531 END IF;
1532
1533
1534
1535
1536
1537 /** UNIQUE VALIDATION CHECK for bank **/
1538 -- NO UNIQUE VALIDATION CHECK
1539 /*
1540 IF l_DEBUG in ('Y', 'C') THEN
1541 cep_standard.debug('UNIQUE VALIDATION CHECK for bank' );
1542 END IF;
1543
1544 IF (COUNTRY_NAME = 'JP') THEN
1545 IF l_DEBUG in ('Y', 'C') THEN
1546 cep_standard.debug('call CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BANK_JP' );
1547 END IF;
1548
1549 CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_UNIQUE_BANK_JP(X_COUNTRY_NAME,
1550 X_BANK_NUMBER ,
1551 X_BANK_NAME ,
1552 X_BANK_NAME_ALT,
1553 X_BANK_ID);
1554
1555 END IF;
1556 */
1557 /** country unique check for bank **/
1558
1559
1560 FND_MSG_PUB.Count_And_Get(
1561 p_encoded => FND_API.G_FALSE,
1562 p_count => x_msg_count,
1563 p_data => x_msg_data);
1564
1565 FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
1566 FND_MSG_PUB.Get(
1567 p_msg_index=>j,
1568 p_encoded=>'T',
1569 p_data=>x_msg_data,
1570 p_msg_index_out=>l_msg_index_out);
1571 v_error_description := l_error_description || ':' || x_msg_data;
1572
1573 FND_MESSAGE.PARSE_ENCODED(x_msg_data,l_app_short_name,l_message_name);
1574
1575 --cep_standard.debug('l_message_name: '|| l_message_name );
1576 l_message_name_all_tmp:=(l_message_name_all_tmp ||','|| l_message_name);
1577
1578 END LOOP;
1579 x_message_name_all:=substr(l_message_name_all_tmp,2);
1580 cep_standard.debug('x_message_name_all: '|| x_message_name_all );
1581
1582 IF l_DEBUG in ('Y', 'C') THEN
1583 cep_standard.debug('<<BANK: X_PASS_MAND_CHECK: '|| X_PASS_MAND_CHECK
1584 ||' P_COUNT: '|| x_msg_count || ' X_VALUE_OUT: '|| X_VALUE_OUT );
1585 --cep_standard.debug('P_DATA: '|| x_msg_data );
1586 --cep_standard.debug('CE_VALIDATE_BANK: ' || 'CE_VALIDATE_BANKINFO_UPG.ce_validate_bank - X_VALUE_OUT: '|| X_VALUE_OUT );
1587 --cep_standard.debug('CE_VALIDATE_BANK: ' || '<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank');
1588 END IF;
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 IF l_DEBUG in ('Y', 'C') THEN
1592 cep_standard.debug('CE_VALIDATE_BANK: ' || 'CE_VALIDATE_BANKINFO_UPG.ce_validate_bank ' ||X_COUNTRY_NAME );
1593 END IF;
1594
1595 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
1596 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO_UPG.cd_validate_bank');
1597 fnd_msg_pub.add;
1598 RAISE;
1599
1600
1601 END CE_VALIDATE_BANK;
1602
1603
1604 /* --------------------------------------------------------------------
1605 | PRIVATE PROCEDURE |
1606 | CE_VALIDATE_CD_* |
1607 | |
1608 | CALLED BY |
1609 | CE_VALIDATE_CD |
1610 | |
1611 | DESCRIPTION |
1612 | Check Digit PROCEDURES, Validations 1 or more of the following: |
1613 | 1. Check Digit length |
1614 | 2. Check Digit Algorithm |
1615 | |
1616 --------------------------------------------------------------------- */
1617
1618
1619 procedure CE_VALIDATE_CD_PT(Xi_CD in varchar2,
1620 Xi_PASS_MAND_CHECK in varchar2,
1621 Xi_X_BANK_NUMBER in varchar2,
1622 Xi_X_BRANCH_NUMBER in varchar2,
1623 Xi_X_ACCOUNT_NUMBER in varchar2)
1624 AS
1625 numeric_result_bk varchar2(40);
1626 numeric_result_br varchar2(40);
1627 numeric_result_cd varchar2(40);
1628 numeric_result_ac varchar2(40);
1629 cal_cd number(10);
1630 CONCED_NUMBER varchar2(30);
1631 cd_value varchar2(20);
1632 bk_value varchar2(30);
1633 ac_value varchar2(30);
1634 br_value varchar2(30);
1635
1636 /**************************/
1637 /* SUB-PROCEDURES SECTION */
1638 /**************************/
1639
1640 procedure fail_mandatory is
1641 begin
1642 fnd_message.set_name ('CE', 'CE_ENTER_CHECK_DIGIT');
1643 fnd_msg_pub.add;
1644 IF l_DEBUG in ('Y', 'C') THEN
1645 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_ENTER_CHECK_DIGIT');
1646 END IF;
1647 end fail_mandatory;
1648
1649 procedure fail_check is
1650 begin
1651 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
1652 fnd_msg_pub.add;
1653 IF l_DEBUG in ('Y', 'C') THEN
1654 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_INVALID_CHECK_DIGIT');
1655 END IF;
1656 end fail_check;
1657
1658 procedure pass_check is
1659 begin
1660 IF l_DEBUG in ('Y', 'C') THEN
1661 null;
1662 --cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
1663 END IF;
1664 end pass_check;
1665 /****************/
1666 /* MAIN SECTION */
1667 /****************/
1668 BEGIN
1669 IF l_DEBUG in ('Y', 'C') THEN
1670 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_pt');
1671 END IF;
1672
1673 CD_VALUE := upper(replace(Xi_CD,' ',''));
1674 bk_value := upper(replace(Xi_X_BANK_NUMBER,' ',''));
1675 br_value := upper(replace(Xi_X_BRANCH_NUMBER,' ',''));
1676 ac_value := upper(replace(Xi_X_ACCOUNT_NUMBER,' ',''));
1677
1678 IF Xi_PASS_MAND_CHECK = 'F'
1679 then
1680 fail_mandatory;
1681
1682 ELSIF Xi_PASS_MAND_CHECK = 'P'
1683 then
1684
1685 numeric_result_bk := ce_check_numeric(bk_value,1,length(bk_value));
1686 numeric_result_br := ce_check_numeric(br_value,1,length(br_value));
1687 numeric_result_ac := ce_check_numeric(ac_value,1,length(ac_value));
1688 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
1689
1690 IF length(bk_value) = 4 and length(br_value) = 4 and length(ac_value) = 11
1691 and length(CD_VALUE) = 2
1692 then
1693
1694 IF numeric_result_bk = '0' and numeric_result_br = '0'
1695 and numeric_result_ac = '0' and numeric_result_cd = '0'
1696 then
1697 /* its numeric so continue */
1698
1699 CONCED_NUMBER := bk_value||br_value||ac_value||CD_VALUE;
1700
1701 cal_cd := 98 - mod(( (to_number(substr(CONCED_NUMBER,19,1)) * 3)
1702 +(to_number(substr(CONCED_NUMBER,18,1)) * 30)
1703 +(to_number(substr(CONCED_NUMBER,17,1)) * 9)
1704 +(to_number(substr(CONCED_NUMBER,16,1)) * 90)
1705 +(to_number(substr(CONCED_NUMBER,15,1)) * 27)
1706 +(to_number(substr(CONCED_NUMBER,14,1)) * 76)
1707 +(to_number(substr(CONCED_NUMBER,13,1)) * 81)
1708 +(to_number(substr(CONCED_NUMBER,12,1)) * 34)
1709 +(to_number(substr(CONCED_NUMBER,11,1)) * 49)
1710 +(to_number(substr(CONCED_NUMBER,10,1)) * 5)
1711 +(to_number(substr(CONCED_NUMBER,9,1)) * 50)
1712 +(to_number(substr(CONCED_NUMBER,8,1)) * 15)
1713 +(to_number(substr(CONCED_NUMBER,7,1)) * 53)
1714 +(to_number(substr(CONCED_NUMBER,6,1)) * 45)
1715 +(to_number(substr(CONCED_NUMBER,5,1)) * 62)
1716 +(to_number(substr(CONCED_NUMBER,4,1)) * 38)
1717 +(to_number(substr(CONCED_NUMBER,3,1)) * 89)
1718 +(to_number(substr(CONCED_NUMBER,2,1)) * 17)
1719 +(to_number(substr(CONCED_NUMBER,1,1)) * 73)),97);
1720
1721
1722 IF l_DEBUG in ('Y', 'C') THEN
1723 cep_standard.debug('cal_cd: ' || cal_cd);
1724 END IF;
1725
1726 IF CD_VALUE = cal_cd
1727 then
1728 pass_check;
1729 ELSE
1730 fail_check;
1731 END IF;
1732
1733 ELSE
1734 fail_check;
1735
1736 END IF; /* end of numeric check */
1737
1738 ELSE
1739 fail_check;
1740
1741 END IF; /* end of length check */
1742 END IF; /* end of mandatory check */
1743
1744 /*
1745 IF l_DEBUG in ('Y', 'C') THEN
1746 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_pt');
1747 END IF;
1748 */
1749 END CE_VALIDATE_CD_PT;
1750
1751 /**************** End of Procedure CE_VALIDATE_CD_PT ***********/
1752
1753 procedure CE_VALIDATE_CD_ES(Xi_CD in varchar2,
1754 Xi_PASS_MAND_CHECK in varchar2,
1755 Xi_X_BANK_NUMBER in varchar2,
1756 Xi_X_BRANCH_NUMBER in varchar2,
1757 Xi_X_ACCOUNT_NUMBER in varchar2)
1758 AS
1759 numeric_result_bk varchar2(40);
1760 numeric_result_br varchar2(40);
1761 numeric_result_cd varchar2(40);
1762 numeric_result_ac varchar2(40);
1763 cd_1 number(10);
1764 cd_2 number(10);
1765 cd_value varchar2(20);
1766 bk_value varchar2(30);
1767 ac_value varchar2(30);
1768 br_value varchar2(30);
1769
1770 /**************************/
1771 /* SUB-PROCEDURES SECTION */
1772 /**************************/
1773 /*Bug Fix:880887*/
1774 /*Changed the Xo_RET_VAR='F' to 'W' ,in 'fail_mandatory' and 'fail_check'*/
1775 /*so that a warning message is displayed*/
1776 /*for spain instead of Error message*/
1777
1778 procedure fail_mandatory is
1779 begin
1780 fnd_message.set_name ('CE', 'CE_ENTER_CHECK_DIGIT');
1781 fnd_msg_pub.add;
1782 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
1783 IF l_DEBUG in ('Y', 'C') THEN
1784 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_ENTER_CHECK_DIGIT');
1785 END IF;
1786
1787 end fail_mandatory;
1788
1789 procedure fail_check is
1790 begin
1791 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
1792 fnd_msg_pub.add;
1793 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
1794 IF l_DEBUG in ('Y', 'C') THEN
1795 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_INVALID_CHECK_DIGIT');
1796 END IF;
1797
1798 end fail_check;
1799
1800 procedure pass_check is
1801 begin
1802 IF l_DEBUG in ('Y', 'C') THEN
1803 null;
1804 --cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
1805 END IF;
1806
1807 end pass_check;
1808 /****************/
1809 /* MAIN SECTION */
1810 /****************/
1811 BEGIN
1812 IF l_DEBUG in ('Y', 'C') THEN
1813 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_es');
1814 END IF;
1815
1816 CD_VALUE := upper(Xi_CD);
1817 bk_value := upper(Xi_X_BANK_NUMBER);
1818 br_value := upper(Xi_X_BRANCH_NUMBER);
1819 ac_value := upper(Xi_X_ACCOUNT_NUMBER);
1820
1821 IF Xi_PASS_MAND_CHECK = 'F'
1822 then
1823 fail_mandatory;
1824
1825 ELSIF Xi_PASS_MAND_CHECK = 'P'
1826 then
1827
1828 numeric_result_bk := ce_check_numeric(bk_value,1,length(bk_value));
1829 numeric_result_br := ce_check_numeric(br_value,1,length(br_value));
1830 numeric_result_ac := ce_check_numeric(ac_value,1,length(ac_value));
1831 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
1832
1833 IF numeric_result_bk = '0' and numeric_result_br = '0'
1834 and numeric_result_ac = '0' and numeric_result_cd = '0'
1835 then
1836 /* its numeric so continue */
1837 cd_1 := 11-mod(((to_number(substr(Xi_X_BANK_NUMBER,1,1)) * 4)
1838 + (to_number(substr(Xi_X_BANK_NUMBER,2,1)) * 8)
1839 + (to_number(substr(Xi_X_BANK_NUMBER,3,1)) * 5)
1840 + (to_number(substr(Xi_X_BANK_NUMBER,4,1)) * 10)
1841 + (to_number(substr(Xi_X_BRANCH_NUMBER,1,1)) * 9)
1842 + (to_number(substr(Xi_X_BRANCH_NUMBER,2,1)) * 7)
1843 + (to_number(substr(Xi_X_BRANCH_NUMBER,3,1)) * 3)
1844 + (to_number(substr(Xi_X_BRANCH_NUMBER,4,1)) * 6)),11);
1845
1846
1847 cd_2 := 11-mod(((to_number(substr(Xi_X_ACCOUNT_NUMBER,1,1)) * 1)
1848 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,2,1)) * 2)
1849 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,3,1)) * 4)
1850 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,4,1)) * 8)
1851 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,5,1)) * 5)
1852 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,6,1)) * 10)
1853 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,7,1)) * 9)
1854 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,8,1)) * 7)
1855 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,9,1)) * 3)
1856 + (to_number(substr(Xi_X_ACCOUNT_NUMBER,10,1)) * 6)),11);
1857
1858
1859 IF (cd_1 = 10)
1860 then
1861 cd_1 :=1;
1862 ELSIF (cd_1 = 11)
1863 then
1864 cd_1 := 0;
1865 END IF;
1866
1867 IF (cd_2 = 10)
1868 then
1869 cd_2 :=1;
1870 ELSIF (cd_2 = 11)
1871 then
1872 cd_2 := 0;
1873 END IF;
1874
1875 IF (cd_1 = substr(CD_VALUE,1,1) and cd_2 = substr(CD_VALUE,2,1))
1876 OR (CD_VALUE = '00')
1877 then
1878 /* check digit checks out */
1879 pass_check;
1880 ELSE
1881 fail_check;
1882 END IF;
1883
1884 ELSE
1885 fail_check;
1886
1887 END IF; /* end of numeric check */
1888
1889 END IF; /* end of mandatory check */
1890 /*
1891 IF l_DEBUG in ('Y', 'C') THEN
1892 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_es');
1893 END IF;
1894 */
1895 END CE_VALIDATE_CD_ES;
1896
1897
1898 /**************** End of Procedure CE_VALIDATE_CD_ES ***********/
1899
1900
1901 procedure CE_VALIDATE_CD_FR(Xi_CD in varchar2,
1902 Xi_PASS_MAND_CHECK in varchar2,
1903 Xi_X_BANK_NUMBER in varchar2,
1904 Xi_X_BRANCH_NUMBER in varchar2,
1905 Xi_X_ACCOUNT_NUMBER in varchar2)
1906 AS
1907 numeric_result_bk varchar2(40);
1908 numeric_result_br varchar2(40);
1909 numeric_result_cd varchar2(40);
1910 numeric_result_ac varchar2(40);
1911 calc_value number(30);
1912 cd_value varchar2(20);
1913 bk_value varchar2(30);
1914 ac_value varchar2(30);
1915 br_value varchar2(30);
1916
1917
1918 /**************************/
1919 /* SUB-PROCEDURES SECTION */
1920 /**************************/
1921
1922 procedure fail_mandatory is
1923 begin
1924 fnd_message.set_name ('CE', 'CE_ENTER_CHECK_DIGIT');
1925 fnd_msg_pub.add;
1926 end fail_mandatory;
1927
1928 procedure fail_check is
1929 begin
1930 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
1931 fnd_msg_pub.add;
1932 end fail_check;
1933
1934 procedure pass_check is
1935 begin
1936 IF l_DEBUG in ('Y', 'C') THEN
1937 null;
1938 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
1939 END IF;
1940
1941 end pass_check;
1942
1943
1944 /****************/
1945 /* MAIN SECTION */
1946 /****************/
1947 BEGIN
1948 IF l_DEBUG in ('Y', 'C') THEN
1949 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_fr');
1950 END IF;
1951
1952 CD_VALUE := upper(Xi_CD);
1953 bk_value := upper(Xi_X_BANK_NUMBER);
1954 br_value := upper(Xi_X_BRANCH_NUMBER);
1955 ac_value := upper(Xi_X_ACCOUNT_NUMBER);
1956
1957 IF Xi_PASS_MAND_CHECK = 'F'
1958 then
1959 fail_mandatory;
1960
1961 ELSIF Xi_PASS_MAND_CHECK = 'P'
1962 then
1963
1964 IF length(CD_VALUE) < 3
1965 then
1966 numeric_result_bk := ce_check_numeric(bk_value,1,length(bk_value));
1967 numeric_result_br := ce_check_numeric(br_value,1,length(br_value));
1968 numeric_result_ac := ce_check_numeric(ac_value,1,length(ac_value));
1969 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
1970
1971 IF numeric_result_bk = '0' and numeric_result_br = '0'
1972 and numeric_result_cd = '0'
1973 then
1974 /* its numeric so continue */
1975 calc_value := 97-mod(to_number(Xi_X_BANK_NUMBER||Xi_X_BRANCH_NUMBER||
1976 translate(Xi_X_ACCOUNT_NUMBER,
1977 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
1978 '123456789123456789234567890')||'00'),97);
1979 IF calc_value = CD_VALUE
1980 then
1981 /* check digit checks out */
1982 pass_check;
1983 ELSE
1984 fail_check;
1985 END IF;
1986
1987 ELSE
1988 fail_check;
1989
1990 END IF; /* end of numeric check */
1991
1992 ELSE
1993 fail_check;
1994
1995 END IF; /* end of length check */
1996 END IF; /* end of mandatory check */
1997
1998 /*
1999 IF l_DEBUG in ('Y', 'C') THEN
2000 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_fr');
2001 END IF;
2002 */
2003 END CE_VALIDATE_CD_FR;
2004
2005
2006 /**************** End of Procedure CE_VALIDATE_CD_FR ***********/
2007
2008 -- new validations for check digits 5/14/02
2009
2010 procedure CE_VALIDATE_CD_DE(Xi_CD in varchar2,
2011 Xi_X_ACCOUNT_NUMBER in varchar2)
2012 AS
2013 numeric_result_cd varchar2(40);
2014
2015 calc_value number;
2016 cd_value varchar2(50);
2017
2018
2019
2020 /**************************/
2021 /* SUB-PROCEDURES SECTION */
2022 /**************************/
2023
2024 procedure fail_check is
2025 begin
2026 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
2027 fnd_msg_pub.add;
2028 IF l_DEBUG in ('Y', 'C') THEN
2029 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_INVALID_CHECK_DIGIT');
2030 END IF;
2031 end fail_check;
2032
2033 procedure pass_check is
2034 begin
2035 IF l_DEBUG in ('Y', 'C') THEN
2036 null;
2037 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
2038 END IF;
2039 end pass_check;
2040
2041
2042 /****************/
2043 /* MAIN SECTION */
2044 /****************/
2045 BEGIN
2046 IF l_DEBUG in ('Y', 'C') THEN
2047 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_DE');
2048 END IF;
2049
2050 CD_VALUE := upper(Xi_CD);
2051
2052 IF l_DEBUG in ('Y', 'C') THEN
2053 cep_standard.debug('cd_value: '|| cd_value);
2054 END IF;
2055
2056 IF (CD_VALUE is not null) THEN
2057 IF length(CD_VALUE) = 1 then
2058 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
2059
2060 IF numeric_result_cd = '0' then
2061 if (CE_VALIDATE_BANKINFO_UPG.COMPARE_ACCOUNT_NUM_AND_CD(Xi_X_ACCOUNT_NUMBER, CD_VALUE, 1, 0)) then
2062 pass_check;
2063 end if;
2064 ELSE
2065 fail_check;
2066 END IF;
2067
2068 ELSE
2069 fail_check;
2070
2071 END IF; /* end of length check */
2072 END IF;
2073
2074 /*
2075 IF l_DEBUG in ('Y', 'C') THEN
2076 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_CD_DE');
2077 END IF;
2078 */
2079 END CE_VALIDATE_CD_DE;
2080
2081 /* -------------------------------------------------------------------- */
2082
2083
2084 procedure CE_VALIDATE_CD_GR(Xi_CD in varchar2,
2085 Xi_PASS_MAND_CHECK in varchar2,
2086 Xi_X_BANK_NUMBER in varchar2,
2087 Xi_X_BRANCH_NUMBER in varchar2,
2088 Xi_X_ACCOUNT_NUMBER in varchar2)
2089 AS
2090 numeric_result_cd varchar2(40);
2091
2092 calc_value number(30);
2093 cd_value varchar2(20);
2094
2095
2096
2097 /**************************/
2098 /* SUB-PROCEDURES SECTION */
2099 /**************************/
2100
2101 procedure fail_check is
2102 begin
2103 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
2104 fnd_msg_pub.add;
2105 IF l_DEBUG in ('Y', 'C') THEN
2106 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_INVALID_CHECK_DIGIT');
2107 END IF;
2108 end fail_check;
2109
2110 procedure pass_check is
2111 begin
2112 IF l_DEBUG in ('Y', 'C') THEN
2113 null;
2114 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
2115 END IF;
2116 end pass_check;
2117
2118
2119 /****************/
2120 /* MAIN SECTION */
2121 /****************/
2122 BEGIN
2123
2124 CD_VALUE := upper(Xi_CD);
2125
2126 IF length(CD_VALUE) < 2 then
2127 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
2128
2129 IF numeric_result_cd = '0' then
2130 pass_check;
2131 ELSE
2132 fail_check;
2133 END IF;
2134
2135 ELSE
2136 fail_check;
2137
2138 END IF; /* end of length check */
2139
2140 END CE_VALIDATE_CD_GR;
2141
2142 /* -------------------------------------------------------------------- */
2143
2144 procedure CE_VALIDATE_CD_IS(Xi_CD in varchar2,
2145 Xi_X_ACCOUNT_NUMBER in varchar2)
2146 AS
2147 numeric_result_cd varchar2(50);
2148 numeric_result_ac varchar2(50);
2149 cal_cd number;
2150 cal_cd1 number;
2151
2152 cd_value varchar2(50);
2153 ac_value varchar2(50);
2154 ac_cd_value varchar2(50);
2155
2156
2157 /**************************/
2158 /* SUB-PROCEDURES SECTION */
2159 /**************************/
2160
2161 procedure fail_check is
2162 begin
2163 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
2164 fnd_msg_pub.add;
2165 IF l_DEBUG in ('Y', 'C') THEN
2166 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_INVALID_CHECK_DIGIT');
2167 END IF;
2168 end fail_check;
2169
2170
2171 procedure pass_check is
2172 begin
2173 IF l_DEBUG in ('Y', 'C') THEN
2174 null;
2175 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
2176 END IF;
2177 end pass_check;
2178 /****************/
2179 /* MAIN SECTION */
2180 /****************/
2181 BEGIN
2182 IF l_DEBUG in ('Y', 'C') THEN
2183 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_is');
2184 END IF;
2185
2186 CD_VALUE := upper(replace(Xi_CD,' ',''));
2187 ac_value := upper(replace(Xi_X_ACCOUNT_NUMBER,' ',''));
2188 ac_value := upper(replace(ac_value,'-',''));
2189
2190
2191 ac_cd_value := substr(ac_value,17,1);
2192
2193 IF l_DEBUG in ('Y', 'C') THEN
2194 cep_standard.debug('ac_cd_value : ' || ac_cd_value|| ' cal_cd : ' || cal_cd);
2195 END IF;
2196
2197 IF (CD_VALUE is not null) THEN
2198 IF length(CD_VALUE) = 1 then
2199 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
2200
2201 IF numeric_result_cd = '0' then
2202 IF l_DEBUG in ('Y', 'C') THEN
2203 cep_standard.debug('CE_VALIDATE_CD: ' || 'cd is numeric');
2204 END IF;
2205 if (CE_VALIDATE_BANKINFO_UPG.COMPARE_ACCOUNT_NUM_AND_CD(ac_value, CD_VALUE, 1,1)) then
2206 pass_check;
2207 IF l_DEBUG in ('Y', 'C') THEN
2208 cep_standard.debug('cd_value = cal_cd');
2209 END IF;
2210 else
2211 fail_check;
2212
2213 end if;
2214 ELSE
2215 IF l_DEBUG in ('Y', 'C') THEN
2216 cep_standard.debug('failed numeric');
2217 END IF;
2218 fail_check;
2219 END IF; --numeric check
2220
2221 ELSE
2222 IF l_DEBUG in ('Y', 'C') THEN
2223 cep_standard.debug('failed length');
2224 END IF;
2225 fail_check;
2226 END IF; /* end of length check */
2227
2228 ELSE
2229 pass_check;
2230 END IF;
2231
2232 /*
2233 IF l_DEBUG in ('Y', 'C') THEN
2234 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_is');
2235 END IF;
2236 */
2237 END CE_VALIDATE_CD_IS;
2238
2239 /* -------------------------------------------------------------------- */
2240
2241 procedure CE_VALIDATE_CD_IT(Xi_CD in varchar2,
2242 Xi_PASS_MAND_CHECK in varchar2,
2243 Xi_X_BANK_NUMBER in varchar2,
2244 Xi_X_BRANCH_NUMBER in varchar2,
2245 Xi_X_ACCOUNT_NUMBER in varchar2)
2246 AS
2247 numeric_result_bk varchar2(40);
2248 numeric_result_br varchar2(40);
2249 numeric_result_cd varchar2(40);
2250 numeric_result_ac varchar2(40);
2251 CONCED_NUMBER varchar2(30);
2252 calc_value varchar2(30);
2253 calc_value1 number;
2254 calc_value2 number;
2255 calc_value3 number;
2256 cd_value varchar2(20);
2257 bk_value varchar2(30);
2258 ac_value varchar2(30);
2259 br_value varchar2(30);
2260
2261
2262
2263 /**************************/
2264 /* SUB-PROCEDURES SECTION */
2265 /**************************/
2266
2267 procedure fail_mandatory is
2268 begin
2269 IF l_DEBUG in ('Y', 'C') THEN
2270 cep_standard.debug('CD: procedure fail_mandatory CE_ENTER_CHECK_DIGIT ');
2271 END IF;
2272 fnd_message.set_name ('CE', 'CE_ENTER_CHECK_DIGIT');
2273 fnd_msg_pub.add;
2274
2275 end fail_mandatory;
2276
2277 procedure fail_check is
2278 begin
2279 IF l_DEBUG in ('Y', 'C') THEN
2280 cep_standard.debug('CD: procedure fail_check CE_INVALID_CHECK_DIGIT');
2281 END IF;
2282 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
2283 fnd_msg_pub.add;
2284 end fail_check;
2285
2286
2287 procedure pass_check is
2288 begin
2289 IF l_DEBUG in ('Y', 'C') THEN
2290 null;
2291 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'procedure pass_check ');
2292 END IF;
2293 end pass_check;
2294
2295 function get_odd_value(odd_value_in varchar2) RETURN NUMBER
2296 IS
2297 odd_value_out number;
2298 BEGIN
2299 IF (odd_value_in in ('A', '0')) then
2300 odd_value_out := 1;
2301 ELSIF (odd_value_in in ('B', '1')) then
2302 odd_value_out := 0;
2303 ELSIF (odd_value_in in ('C', '2')) then
2304 odd_value_out := 5;
2305 ELSIF (odd_value_in in ('D', '3')) then
2306 odd_value_out := 7;
2307 ELSIF (odd_value_in in ('E', '4')) then
2308 odd_value_out := 9;
2309 ELSIF (odd_value_in in ('F', '5')) then
2310 odd_value_out := 13;
2311 ELSIF (odd_value_in in ('G', '6')) then
2312 odd_value_out := 15;
2313 ELSIF (odd_value_in in ('H', '7')) then
2314 odd_value_out := 17;
2315 ELSIF (odd_value_in in ('I', '8')) then
2316 odd_value_out := 19;
2317 ELSIF (odd_value_in in ('J', '9')) then
2318 odd_value_out := 21;
2319 ELSIF (odd_value_in = 'K') then
2320 odd_value_out := 2;
2321 ELSIF (odd_value_in = 'L') then
2322 odd_value_out := 4;
2323 ELSIF (odd_value_in = 'M') then
2324 odd_value_out := 18;
2325 ELSIF (odd_value_in = 'N') then
2326 odd_value_out := 20;
2327 ELSIF (odd_value_in = 'O') then
2328 odd_value_out := 11;
2329 ELSIF (odd_value_in = 'P') then
2330 odd_value_out := 3;
2331 ELSIF (odd_value_in = 'Q') then
2332 odd_value_out := 6;
2333 ELSIF (odd_value_in = 'R') then
2334 odd_value_out := 8;
2335 ELSIF (odd_value_in = 'S') then
2336 odd_value_out := 12;
2337 ELSIF (odd_value_in = 'T') then
2338 odd_value_out := 14;
2339 ELSIF (odd_value_in = 'U') then
2340 odd_value_out := 16;
2341 ELSIF (odd_value_in = 'V') then
2342 odd_value_out := 10;
2343 ELSIF (odd_value_in = 'W') then
2344 odd_value_out := 22;
2345 ELSIF (odd_value_in = 'X') then
2346 odd_value_out := 25;
2347 ELSIF (odd_value_in = 'Y') then
2348 odd_value_out := 24;
2349 ELSIF (odd_value_in = 'Z') then
2350 odd_value_out := 23;
2351 END IF;
2352
2353 RETURN(odd_value_out);
2354 END get_odd_value;
2355
2356 function get_even_value(even_value_in varchar2) RETURN NUMBER
2357 IS
2358 even_value_out number;
2359 BEGIN
2360 IF (even_value_in in ('A', '0')) then
2361 even_value_out := 0;
2362 ELSIF (even_value_in in ('B', '1')) then
2363 even_value_out := 1;
2364 ELSIF (even_value_in in ('C', '2')) then
2365 even_value_out := 2;
2366 ELSIF (even_value_in in ('D', '3')) then
2367 even_value_out := 3;
2368 ELSIF (even_value_in in ('E', '4')) then
2369 even_value_out := 4;
2370 ELSIF (even_value_in in ('F', '5')) then
2371 even_value_out := 5;
2372 ELSIF (even_value_in in ('G', '6')) then
2373 even_value_out := 6;
2374 ELSIF (even_value_in in ('H', '7')) then
2375 even_value_out := 7;
2376 ELSIF (even_value_in in ('I', '8')) then
2377 even_value_out := 8;
2378 ELSIF (even_value_in in ('J', '9')) then
2379 even_value_out := 9;
2380 ELSIF (even_value_in = 'K') then
2381 even_value_out := 10;
2382 ELSIF (even_value_in = 'L') then
2383 even_value_out := 11;
2384 ELSIF (even_value_in = 'M') then
2385 even_value_out := 12;
2386 ELSIF (even_value_in = 'N') then
2387 even_value_out := 13;
2388 ELSIF (even_value_in = 'O') then
2389 even_value_out := 14;
2390 ELSIF (even_value_in = 'P') then
2391 even_value_out := 15;
2392 ELSIF (even_value_in = 'Q') then
2393 even_value_out := 16;
2394 ELSIF (even_value_in = 'R') then
2395 even_value_out := 17;
2396 ELSIF (even_value_in = 'S') then
2397 even_value_out := 18;
2398 ELSIF (even_value_in = 'T') then
2399 even_value_out := 19;
2400 ELSIF (even_value_in = 'U') then
2401 even_value_out := 20;
2402 ELSIF (even_value_in = 'V') then
2403 even_value_out := 21;
2404 ELSIF (even_value_in = 'W') then
2405 even_value_out := 22;
2406 ELSIF (even_value_in = 'X') then
2407 even_value_out := 23;
2408 ELSIF (even_value_in = 'Y') then
2409 even_value_out := 24;
2410 ELSIF (even_value_in = 'Z') then
2411 even_value_out := 25;
2412 END IF;
2413
2414 RETURN(even_value_out);
2415 END get_even_value;
2416
2417 function get_result_cd(remainder_value_in number) RETURN varchar2
2418 IS
2419 remainder_value_out VARCHAR2(1);
2420 BEGIN
2421 IF (remainder_value_in = '0') then
2422 remainder_value_out := 'A';
2423 ELSIF (remainder_value_in = '1') then
2424 remainder_value_out := 'B';
2425 ELSIF (remainder_value_in = '2') then
2426 remainder_value_out := 'C';
2427 ELSIF (remainder_value_in = '3') then
2428 remainder_value_out := 'D';
2429 ELSIF (remainder_value_in = '4') then
2430 remainder_value_out := 'E';
2431 ELSIF (remainder_value_in = '5') then
2432 remainder_value_out := 'F';
2433 ELSIF (remainder_value_in = '6') then
2434 remainder_value_out := 'G';
2435 ELSIF (remainder_value_in = '7') then
2436 remainder_value_out := 'H';
2437 ELSIF (remainder_value_in = '8') then
2438 remainder_value_out := 'I';
2439 ELSIF (remainder_value_in = '9') then
2440 remainder_value_out := 'J';
2441 ELSIF (remainder_value_in = '10') then
2442 remainder_value_out := 'K';
2443 ELSIF (remainder_value_in = '11') then
2444 remainder_value_out := 'L';
2445 ELSIF (remainder_value_in = '12') then
2446 remainder_value_out := 'M';
2447 ELSIF (remainder_value_in = '13') then
2448 remainder_value_out := 'N';
2449 ELSIF (remainder_value_in = '14') then
2450 remainder_value_out := 'O';
2451 ELSIF (remainder_value_in = '15') then
2452 remainder_value_out := 'P';
2453 ELSIF (remainder_value_in = '16') then
2454 remainder_value_out := 'Q';
2455 ELSIF (remainder_value_in = '17') then
2456 remainder_value_out := 'R';
2457 ELSIF (remainder_value_in = '18') then
2458 remainder_value_out := 'S';
2459 ELSIF (remainder_value_in = '19') then
2460 remainder_value_out := 'T';
2461 ELSIF (remainder_value_in = '20') then
2462 remainder_value_out := 'U';
2463 ELSIF (remainder_value_in = '21') then
2464 remainder_value_out := 'V';
2465 ELSIF (remainder_value_in = '22') then
2466 remainder_value_out := 'W';
2467 ELSIF (remainder_value_in = '23') then
2468 remainder_value_out := 'X';
2469 ELSIF (remainder_value_in = '24') then
2470 remainder_value_out := 'Y';
2471 ELSIF (remainder_value_in = '25') then
2472 remainder_value_out := 'Z';
2473
2474 END IF;
2475
2476 RETURN(remainder_value_out);
2477 END get_result_cd;
2478
2479
2480
2481
2482 /****************/
2483 /* MAIN SECTION */
2484 /****************/
2485 BEGIN
2486
2487 IF l_DEBUG in ('Y', 'C') THEN
2488 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_it'||
2489 ' Xi_X_ACCOUNT_NUMBER: '||Xi_X_ACCOUNT_NUMBER);
2490 END IF;
2491
2492 CD_VALUE := upper(replace(Xi_CD,' ',''));
2493 bk_value := upper(replace(Xi_X_BANK_NUMBER,' ',''));
2494 br_value := upper(replace(Xi_X_BRANCH_NUMBER,' ',''));
2495 ac_value := upper(replace(Xi_X_ACCOUNT_NUMBER,' ',''));
2496
2497 ac_value := upper(ac_value);
2498
2499 IF l_DEBUG in ('Y', 'C') THEN
2500 cep_standard.debug('cd_value: '||cd_value|| ' ac_value: '||ac_value);
2501 END IF;
2502
2503 IF Xi_PASS_MAND_CHECK = 'F'
2504 then
2505 fail_mandatory;
2506
2507 ELSIF Xi_PASS_MAND_CHECK = 'P'
2508 then
2509
2510 IF length(CD_VALUE) = 1
2511 then
2512 numeric_result_bk := ce_check_numeric(bk_value,1,length(bk_value));
2513 numeric_result_br := ce_check_numeric(br_value,1,length(br_value));
2514
2515
2516 IF numeric_result_bk = '0' and numeric_result_br = '0'
2517 then
2518 /* its numeric so continue */
2519
2520
2521 CONCED_NUMBER := bk_value||br_value||ac_value;
2522
2523 IF l_DEBUG in ('Y', 'C') THEN
2524 cep_standard.debug('CONCED_NUMBER: '||CONCED_NUMBER);
2525 END IF;
2526
2527 calc_value1 := (get_odd_value(substr(conced_number,1,1)) +
2528 get_even_value(substr(conced_number,2,1)) +
2529 get_odd_value(substr(conced_number,3,1)) +
2530 get_even_value(substr(conced_number,4,1)) +
2531 get_odd_value(substr(conced_number,5,1)) +
2532 get_even_value(substr(conced_number,6,1)) +
2533 get_odd_value(substr(conced_number,7,1)) +
2534 get_even_value(substr(conced_number,8,1)) +
2535 get_odd_value(substr(conced_number,9,1)) +
2536 get_even_value(substr(conced_number,10,1)) +
2537 get_odd_value(substr(conced_number,11,1)) +
2538 get_even_value(substr(conced_number,12,1)) +
2539 get_odd_value(substr(conced_number,13,1)) +
2540 get_even_value(substr(conced_number,14,1)) +
2541 get_odd_value(substr(conced_number,15,1)) +
2542 get_even_value(substr(conced_number,16,1)) +
2543 get_odd_value(substr(conced_number,17,1)) +
2544 get_even_value(substr(conced_number,18,1)) +
2545 get_odd_value(substr(conced_number,19,1)) +
2546 get_even_value(substr(conced_number,20,1)) +
2547 get_odd_value(substr(conced_number,21,1)) +
2548 get_even_value(substr(conced_number,22,1))) ;
2549
2550
2551 IF l_DEBUG in ('Y', 'C') THEN
2552 cep_standard.debug('Bank Digit 1: '||get_odd_value(substr(conced_number,1,1))||
2553 get_even_value(substr(conced_number,2,1))||
2554 get_odd_value(substr(conced_number,3,1))||
2555 get_even_value(substr(conced_number,4,1))||
2556 get_odd_value(substr(conced_number,5,1))||
2557 ' Branch Digit 6: ' ||get_even_value(substr(conced_number,6,1))||
2558 get_odd_value(substr(conced_number,7,1))||
2559 get_even_value(substr(conced_number,8,1)) ||
2560 get_odd_value(substr(conced_number,9,1)) ||
2561 get_even_value(substr(conced_number,10,1))||
2562 ' Account Digit 11: '||get_odd_value(substr(conced_number,11,1))||
2563 get_even_value(substr(conced_number,12,1))||
2564 get_odd_value(substr(conced_number,13,1))||
2565 get_even_value(substr(conced_number,14,1))||
2566 get_odd_value(substr(conced_number,15,1)) ||
2567 get_even_value(substr(conced_number,16,1))||
2568 get_odd_value(substr(conced_number,17,1))||
2569 get_even_value(substr(conced_number,18,1))||
2570 get_odd_value(substr(conced_number,19,1))||
2571 get_even_value(substr(conced_number,20,1))||
2572 get_odd_value(substr(conced_number,21,1))||
2573 get_even_value(substr(conced_number,22,1))||
2574 'calc_value1: '||calc_value1);
2575 END IF;
2576
2577 calc_value2 := nvl(mod(calc_value1,26),0);
2578
2579 IF l_DEBUG in ('Y', 'C') THEN
2580 cep_standard.debug('calc_value2: '||calc_value2);
2581 END IF;
2582
2583
2584 calc_value := get_result_cd(calc_value2);
2585
2586 IF l_DEBUG in ('Y', 'C') THEN
2587 cep_standard.debug('calc_value: '||calc_value);
2588 END IF;
2589
2590
2591 IF calc_value = CD_VALUE
2592 then
2593 /* check digit checks out */
2594 pass_check;
2595 ELSE
2596 fail_check;
2597 IF l_DEBUG in ('Y', 'C') THEN
2598 cep_standard.debug('fail_check cd_value');
2599 END IF;
2600 END IF;
2601
2602 ELSE
2603 fail_check;
2604 IF l_DEBUG in ('Y', 'C') THEN
2605 cep_standard.debug('fail_check numeric ');
2606 END IF;
2607
2608 END IF; /* end of numeric check */
2609
2610 ELSE
2611 fail_check;
2612 IF l_DEBUG in ('Y', 'C') THEN
2613 cep_standard.debug('fail_check cd length ');
2614 END IF;
2615
2616 END IF; /* end of length check */
2617 END IF;
2618
2619 /*
2620 IF l_DEBUG in ('Y', 'C') THEN
2621 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_it');
2622 END IF;
2623 */
2624 END CE_VALIDATE_CD_IT;
2625
2626 /* -------------------------------------------------------------------- */
2627
2628
2629 procedure CE_VALIDATE_CD_LU(Xi_CD in varchar2,
2630 Xi_X_BANK_NUMBER in varchar2,
2631 Xi_X_BRANCH_NUMBER in varchar2,
2632 Xi_X_ACCOUNT_NUMBER in varchar2)
2633 --Xo_VALUE_OUT OUT NOCOPY varchar2)
2634 AS
2635 numeric_result_cd varchar2(40);
2636
2637 ACCOUNT_VALUE varchar2(30);
2638
2639 CHECK_DIGIT varchar2(2);
2640
2641 /**************************/
2642 /* SUB-PROCEDURES SECTION */
2643 /**************************/
2644
2645 procedure fail_check is
2646 begin
2647 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
2648 fnd_msg_pub.add;
2649 IF l_DEBUG in ('Y', 'C') THEN
2650 cep_standard.debug('CE_VALIDATE_CD_LU: ' || 'CE_INVALID_CHECK_DIGIT');
2651 END IF;
2652 end fail_check;
2653
2654 procedure pass_check is
2655 begin
2656 IF l_DEBUG in ('Y', 'C') THEN
2657 null;
2658 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
2659 END IF;
2660 end pass_check;
2661
2662
2663 /****************/
2664 /* MAIN SECTION */
2665 /****************/
2666 BEGIN
2667 IF l_DEBUG in ('Y', 'C') THEN
2668 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_lu');
2669 END IF;
2670
2671 ACCOUNT_VALUE := lpad(Xi_X_ACCOUNT_NUMBER,12,0);
2672
2673 CHECK_DIGIT := Xi_CD;
2674
2675
2676 IF CHECK_DIGIT is not null then
2677 IF length(CHECK_DIGIT) = 2 then
2678 numeric_result_cd := ce_check_numeric(CHECK_DIGIT,1,length(CHECK_DIGIT));
2679
2680
2681 IF numeric_result_cd = '0' then
2682 pass_check;
2683
2684 /*IF MOD(account_value,97) = CHECK_DIGIT
2685 THEN pass_check;
2686
2687 ELSIF MOD(account_value,97) = 0
2688 THEN Xo_VALUE_OUT := 97;
2689 ELSE fail_check;
2690
2691 END IF;*/
2692
2693 ELSE
2694 fail_check;
2695
2696 END IF; /* end of numeric check */
2697
2698 ELSE fail_check;
2699
2700 END IF; /* end of length check */
2701
2702 ELSE
2703 pass_check;
2704
2705 END IF;
2706
2707 /*
2708 IF l_DEBUG in ('Y', 'C') THEN
2709 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_lu');
2710 END IF;
2711 */
2712 END CE_VALIDATE_CD_LU;
2713
2714 /* -------------------------------------------------------------------- */
2715
2716 procedure CE_VALIDATE_CD_SE(Xi_CD in varchar2,
2717 Xi_X_ACCOUNT_NUMBER in varchar2)
2718 AS
2719 numeric_result_cd varchar2(40);
2720
2721 calc_value number(30);
2722 cd_value varchar2(20);
2723 account_value varchar2(30);
2724
2725 /**************************/
2726 /* SUB-PROCEDURES SECTION */
2727 /**************************/
2728
2729 procedure fail_check is
2730 begin
2731 fnd_message.set_name ('CE', 'CE_INVALID_CHECK_DIGIT');
2732 fnd_msg_pub.add;
2733 IF l_DEBUG in ('Y', 'C') THEN
2734 cep_standard.debug('CE_VALIDATE_CD: ' || 'CE_INVALID_CHECK_DIGIT');
2735 END IF;
2736 end fail_check;
2737
2738 procedure pass_check is
2739 begin
2740 IF l_DEBUG in ('Y', 'C') THEN
2741 null;
2742 -- cep_standard.debug('CE_VALIDATE_CD: ' || 'pass_check');
2743 END IF;
2744 end pass_check;
2745
2746
2747 /****************/
2748 /* MAIN SECTION */
2749 /****************/
2750 BEGIN
2751 IF l_DEBUG in ('Y', 'C') THEN
2752 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_se');
2753 END IF;
2754
2755 CD_VALUE := upper(Xi_CD);
2756 account_value := upper(replace(Xi_X_ACCOUNT_NUMBER ,' ',''));
2757 account_value := replace(account_value,'-','');
2758
2759 IF (CD_VALUE is not null) THEN
2760 IF length(CD_VALUE) = 1 then
2761 numeric_result_cd := ce_check_numeric(CD_VALUE,1,length(CD_VALUE));
2762
2763 IF numeric_result_cd = '0' then
2764 pass_check;
2765 ELSE
2766 fail_check;
2767 END IF;
2768
2769 ELSE
2770 fail_check;
2771
2772 END IF; /* end of length check */
2773 ELSE
2774 pass_check;
2775 END IF;
2776 /*
2777 IF l_DEBUG in ('Y', 'C') THEN
2778 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_cd_se');
2779 END IF;
2780 */
2781 END CE_VALIDATE_CD_SE;
2782
2783 /* --------------------------------------------------------------------
2784 | PRIVATE PROCEDURE |
2785 | CE_VALIDATE_BRANCH_* |
2786 | |
2787 | CALLED BY |
2788 | CE_VALIDATE_BRANCH |
2789 | |
2790 | DESCRIPTION |
2791 | Branch PROCEDURES, Validate 1 or more of the following: |
2792 | 1. Branch number length |
2793 | 2. Branch number datatype (numeric, alphanumeric, or alphabet |
2794 | 3. Branch number Algorithm |
2795 | |
2796 | RETURN |
2797 | Xo_VALUE_OUT - Branch Number is return with leading 0 |
2798 | (Not for all countries) |
2799 --------------------------------------------------------------------- */
2800
2801
2802 procedure CE_VALIDATE_BRANCH_AT(Xi_BRANCH_NUMBER in varchar2,
2803 Xi_PASS_MAND_CHECK in varchar2,
2804 Xo_VALUE_OUT OUT NOCOPY varchar2)
2805 AS
2806 BRANCH_VALUE varchar2(30);
2807
2808 numeric_result varchar2(40);
2809
2810 /**************************/
2811 /* SUB-PROCEDURES SECTION */
2812 /**************************/
2813
2814 procedure fail_mandatory is
2815 begin
2816 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
2817 fnd_msg_pub.add;
2818 IF l_DEBUG in ('Y', 'C') THEN
2819 cep_standard.debug('CE_VALIDATE_BRANCH_AT: ' || 'CE_ENTER_BRANCH_NUM');
2820 END IF;
2821 end fail_mandatory;
2822
2823 procedure fail_check is
2824 begin
2825 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
2826 fnd_msg_pub.add;
2827 IF l_DEBUG in ('Y', 'C') THEN
2828 cep_standard.debug('CE_VALIDATE_BRANCH_AT: ' || 'CE_INVALID_BRANCH_NUM');
2829 END IF;
2830 end fail_check;
2831
2832 procedure pass_check is
2833 begin
2834 IF l_DEBUG in ('Y', 'C') THEN
2835 null;
2836 -- cep_standard.debug('CE_VALIDATE_BRANCH_AT: ' || 'pass_check');
2837 END IF;
2838 end pass_check;
2839
2840 /****************/
2841 /* MAIN SECTION */
2842 /****************/
2843 BEGIN
2844 IF l_DEBUG in ('Y', 'C') THEN
2845 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_at');
2846 END IF;
2847
2848 BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
2849 Xo_VALUE_OUT := BRANCH_VALUE;
2850
2851 IF Xi_PASS_MAND_CHECK = 'F'
2852 THEN
2853 fail_mandatory;
2854
2855 ELSIF Xi_PASS_MAND_CHECK = 'P'
2856 THEN
2857
2858 BRANCH_VALUE := replace(BRANCH_VALUE,' ','');
2859
2860 IF ( length(BRANCH_VALUE) < 6 ) THEN
2861
2862 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
2863
2864 IF (numeric_result = '0' and length(BRANCH_VALUE) = 5 ) THEN
2865 /* its numeric so continue */
2866 Xo_VALUE_OUT := BRANCH_VALUE;
2867 pass_check;
2868 ELSE
2869 fail_check;
2870
2871 END IF; /* end of numeric check */
2872
2873 ELSE
2874 fail_check;
2875
2876 END IF; /* end of length check */
2877
2878 END IF; /* end of mandatory check */
2879 /*
2880 IF l_DEBUG in ('Y', 'C') THEN
2881 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_at');
2882 END IF;
2883 */
2884 END CE_VALIDATE_BRANCH_AT;
2885
2886 /**************** End of Procedure CE_VALIDATE_BRANCH_AT ***********/
2887
2888 procedure CE_VALIDATE_BRANCH_PT(Xi_BRANCH_NUMBER in varchar2,
2889 Xi_PASS_MAND_CHECK in varchar2)
2890 AS
2891 BRANCH_VALUE varchar2(30);
2892 numeric_result varchar2(40);
2893
2894 /**************************/
2895 /* SUB-PROCEDURES SECTION */
2896 /**************************/
2897
2898 procedure fail_mandatory is
2899 begin
2900 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
2901 fnd_msg_pub.add;
2902 IF l_DEBUG in ('Y', 'C') THEN
2903 cep_standard.debug('CE_VALIDATE_BRANCH_PT: ' || 'CE_INVALID_BRANCH_NUM');
2904 END IF;
2905 end fail_mandatory;
2906
2907 procedure fail_check is
2908 begin
2909 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
2910 fnd_msg_pub.add;
2911 IF l_DEBUG in ('Y', 'C') THEN
2912 cep_standard.debug('CE_VALIDATE_BRANCH_PT: ' || 'CE_INVALID_BRANCH_NUM');
2913 END IF;
2914 end fail_check;
2915
2916 procedure pass_check is
2917 begin
2918 IF l_DEBUG in ('Y', 'C') THEN
2919 null;
2920 -- cep_standard.debug('CE_VALIDATE_BRANCH_PT: ' || 'pass_check');
2921 END IF;
2922 end pass_check;
2923
2924 /****************/
2925 /* MAIN SECTION */
2926 /****************/
2927 BEGIN
2928 IF l_DEBUG in ('Y', 'C') THEN
2929 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_pt');
2930 END IF;
2931
2932 BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
2933
2934 IF Xi_PASS_MAND_CHECK = 'F'
2935 then
2936 fail_mandatory;
2937
2938 ELSIF Xi_PASS_MAND_CHECK = 'P'
2939 then
2940
2941 BRANCH_VALUE := replace(BRANCH_VALUE,' ','');
2942
2943 IF length(BRANCH_VALUE) = 4
2944 then
2945 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
2946
2947 IF numeric_result = '0'
2948 then /* its numeric so continue */
2949 pass_check;
2950 ELSE
2951 fail_check;
2952
2953 END IF; /* end of numeric check */
2954
2955 ELSE
2956 fail_check;
2957
2958 END IF; /* end of length check */
2959 END IF; /* end of mandatory check */
2960 /*
2961 IF l_DEBUG in ('Y', 'C') THEN
2962 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_pt');
2963 END IF;
2964 */
2965 END CE_VALIDATE_BRANCH_PT;
2966
2967 /**************** End of Procedure CE_VALIDATE_BRANCH_PT ***********/
2968
2969
2970 procedure CE_VALIDATE_BRANCH_FR(Xi_BRANCH_NUMBER in varchar2,
2971 Xi_PASS_MAND_CHECK in varchar2,
2972 Xo_VALUE_OUT OUT NOCOPY varchar2)
2973 AS
2974 BRANCH_VALUE varchar2(30);
2975
2976 numeric_result varchar2(40);
2977
2978 /**************************/
2979 /* SUB-PROCEDURES SECTION */
2980 /**************************/
2981
2982 procedure fail_mandatory is
2983 begin
2984 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
2985 fnd_msg_pub.add;
2986 end fail_mandatory;
2987
2988 procedure fail_check is
2989 begin
2990 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
2991 fnd_msg_pub.add;
2992 end fail_check;
2993
2994 procedure pass_check is
2995 begin
2996 IF l_DEBUG in ('Y', 'C') THEN
2997 null;
2998 -- cep_standard.debug('CE_VALIDATE_BRANCH_FR: ' || 'pass_check');
2999 END IF;
3000
3001 end pass_check;
3002
3003 /****************/
3004 /* MAIN SECTION */
3005 /****************/
3006 BEGIN
3007 IF l_DEBUG in ('Y', 'C') THEN
3008 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_fr');
3009 END IF;
3010
3011 BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
3012 Xo_VALUE_OUT := BRANCH_VALUE;
3013
3014 IF Xi_PASS_MAND_CHECK = 'F'
3015 then
3016 fail_mandatory;
3017
3018 ELSIF Xi_PASS_MAND_CHECK = 'P'
3019 then
3020
3021 BRANCH_VALUE := replace(BRANCH_VALUE,' ','');
3022
3023 IF length(BRANCH_VALUE) < 6
3024 then
3025 BRANCH_VALUE := lpad(BRANCH_VALUE,5,0);
3026 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
3027
3028 IF numeric_result = '0'
3029 then
3030 /* its numeric so continue */
3031 Xo_VALUE_OUT := BRANCH_VALUE;
3032 pass_check;
3033 ELSE
3034 fail_check;
3035
3036 END IF; /* end of numeric check */
3037
3038 ELSE
3039 fail_check;
3040
3041 END IF; /* end of length check */
3042 END IF; /* end of mandatory check */
3043 /*
3044 IF l_DEBUG in ('Y', 'C') THEN
3045 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_fr');
3046 END IF;
3047 */
3048 END CE_VALIDATE_BRANCH_FR;
3049
3050
3051 /**************** End of Procedure CE_VALIDATE_BRANCH_FR ***********/
3052
3053 procedure CE_VALIDATE_BRANCH_ES (Xi_BRANCH_NUMBER in varchar2,
3054 Xi_PASS_MAND_CHECK in varchar2,
3055 Xo_VALUE_OUT OUT NOCOPY varchar2)
3056 AS
3057 BRANCH_VALUE varchar2(30);
3058
3059 numeric_result varchar2(40);
3060
3061 /**************************/
3062 /* SUB-PROCEDURES SECTION */
3063 /**************************/
3064
3065 /*Bug Fix:880887*/
3066 /*Changed the Xo_RET_VAR='F' to 'W' ,in 'fail_mandatory' and 'fail_check'*/
3067 /*so that a warning message is displayed*/
3068 /*for spain instead of Error message*/
3069 procedure fail_mandatory is
3070 begin
3071 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
3072 fnd_msg_pub.add;
3073 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
3074
3075 end fail_mandatory;
3076
3077 procedure fail_check is
3078 begin
3079 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3080 fnd_msg_pub.add;
3081 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
3082
3083 end fail_check;
3084
3085 procedure pass_check is
3086 begin
3087
3088 IF l_DEBUG in ('Y', 'C') THEN
3089 null;
3090 -- cep_standard.debug('CE_VALIDATE_BRANCH_ES: ' || 'pass_check');
3091 END IF;
3092 end pass_check;
3093
3094 /****************/
3095 /* MAIN SECTION */
3096 /****************/
3097 BEGIN
3098 IF l_DEBUG in ('Y', 'C') THEN
3099 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_es');
3100 END IF;
3101
3102 BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
3103 Xo_VALUE_OUT := BRANCH_VALUE;
3104
3105 IF Xi_PASS_MAND_CHECK = 'F'
3106 then
3107 fail_mandatory;
3108
3109 ELSIF Xi_PASS_MAND_CHECK = 'P'
3110 then
3111
3112 BRANCH_VALUE := replace(BRANCH_VALUE,' ','');
3113
3114 IF length(BRANCH_VALUE) < 5
3115 then
3116 BRANCH_VALUE := lpad(BRANCH_VALUE,4,0);
3117 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
3118
3119 IF numeric_result = '0'
3120 then
3121 /* its numeric so continue */
3122 Xo_VALUE_OUT := BRANCH_VALUE;
3123 pass_check;
3124 ELSE
3125 fail_check;
3126
3127 END IF; /* end of numeric check */
3128
3129 ELSE
3130 fail_check;
3131
3132 END IF; /* end of length check */
3133 END IF; /* end of mandatory check */
3134 /*
3135 IF l_DEBUG in ('Y', 'C') THEN
3136 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_es');
3137 END IF;
3138 */
3139 END CE_VALIDATE_BRANCH_ES;
3140
3141
3142 /**************** End of Procedure CE_VALIDATE_BRANCH_ES ***********/
3143
3144
3145
3146 procedure CE_VALIDATE_BRANCH_BR(Xi_BRANCH_NUMBER in varchar2,
3147 Xi_PASS_MAND_CHECK in varchar2,
3148 Xo_VALUE_OUT OUT NOCOPY varchar2)
3149 AS
3150 BRANCH_VALUE varchar2(30);
3151
3152 numeric_result varchar2(40);
3153
3154 /**************************/
3155 /* SUB-PROCEDURES SECTION */
3156 /**************************/
3157
3158 procedure fail_mandatory is
3159 begin
3160 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
3161 fnd_msg_pub.add;
3162 end fail_mandatory;
3163
3164 procedure fail_check is
3165 begin
3166 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3167 fnd_msg_pub.add;
3168 end fail_check;
3169
3170 procedure pass_check is
3171 begin
3172
3173 IF l_DEBUG in ('Y', 'C') THEN
3174 null;
3175 -- cep_standard.debug('CE_VALIDATE_BRANCH_BR: ' || 'pass_check');
3176 END IF;
3177 end pass_check;
3178
3179 /****************/
3180 /* MAIN SECTION */
3181 /****************/
3182 BEGIN
3183 IF l_DEBUG in ('Y', 'C') THEN
3184 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_br');
3185 END IF;
3186
3187 BRANCH_VALUE := upper(Xi_BRANCH_NUMBER );
3188 Xo_VALUE_OUT := BRANCH_VALUE;
3189
3190 IF Xi_PASS_MAND_CHECK = 'F'
3191 then
3192 fail_mandatory;
3193
3194 ELSIF Xi_PASS_MAND_CHECK = 'P'
3195 then
3196
3197 BRANCH_VALUE := replace(BRANCH_VALUE,' ','');
3198
3199 IF length(BRANCH_VALUE) < 6
3200 then
3201 BRANCH_VALUE := lpad(BRANCH_VALUE,5,0);
3202 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
3203
3204 IF numeric_result = '0'
3205 then
3206 /* its numeric so continue */
3207 Xo_VALUE_OUT := BRANCH_VALUE;
3208 pass_check;
3209 ELSE
3210 fail_check;
3211
3212 END IF; /* end of numeric check for branch */
3213
3214 ELSE
3215 fail_check;
3216
3217 END IF; /* end of length check for branch */
3218
3219 END IF; /* end of mandatory check */
3220 /*
3221 IF l_DEBUG in ('Y', 'C') THEN
3222 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_br');
3223 END IF;
3224 */
3225 END CE_VALIDATE_BRANCH_BR;
3226
3227 /**************** End of Procedure CE_VALIDATE_BRANCH_BR ***********/
3228
3229 -- new branch validations 5/14/02
3230
3231 procedure CE_VALIDATE_BRANCH_DE(Xi_BRANCH_NUMBER in varchar2,
3232 Xi_BANK_ID in NUMBER)
3233 AS
3234 BRANCH_NUM varchar2(30);
3235 numeric_result varchar2(40);
3236
3237 /**************************/
3238 /* SUB-PROCEDURES SECTION */
3239 /**************************/
3240
3241 procedure fail_check is
3242 begin
3243 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3244 fnd_msg_pub.add;
3245 IF l_DEBUG in ('Y', 'C') THEN
3246 cep_standard.debug('CE_VALIDATE_BRANCH_DE: ' || 'CE_INVALID_BRANCH_NUM');
3247 END IF;
3248
3249 end fail_check;
3250
3251 procedure pass_check is
3252 begin
3253
3254 IF l_DEBUG in ('Y', 'C') THEN
3255 null;
3256 -- cep_standard.debug('CE_VALIDATE_BRANCH_DE: ' || 'pass_check');
3257 END IF;
3258 end pass_check;
3259
3260 /****************/
3261 /* MAIN SECTION */
3262 /****************/
3263 BEGIN
3264 IF l_DEBUG in ('Y', 'C') THEN
3265 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_de');
3266 END IF;
3267
3268 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3269
3270 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3271
3272 IF (BRANCH_NUM) is not null then
3273 IF length(BRANCH_NUM) = 8 then
3274 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3275
3276 IF numeric_result = '0' then /* its numeric so continue */
3277 -- Bank number and branch number should be the same
3278
3279 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3280
3281 ELSE
3282 fail_check;
3283 END IF; /* end of numeric check */
3284 ELSE
3285 IF l_DEBUG in ('Y', 'C') THEN
3286 cep_standard.debug('ce_validate_branch_de - length <>8');
3287 END IF;
3288 fail_check;
3289
3290 END IF; /* end of length check */
3291
3292 ELSE
3293 pass_check;
3294
3295 END IF;
3296
3297 IF l_DEBUG in ('Y', 'C') THEN
3298 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_de');
3299 END IF;
3300
3301 END CE_VALIDATE_BRANCH_DE;
3302
3303 /* -------------------------------------------------------------------- */
3304
3305 procedure CE_VALIDATE_BRANCH_GR(Xi_BRANCH_NUMBER in varchar2)
3306 AS
3307 BRANCH_VALUE varchar2(30);
3308
3309 numeric_result varchar2(40);
3310
3311 /**************************/
3312 /* SUB-PROCEDURES SECTION */
3313 /**************************/
3314
3315 procedure fail_check is
3316 begin
3317 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3318 fnd_msg_pub.add;
3319 IF l_DEBUG in ('Y', 'C') THEN
3320 cep_standard.debug('CE_VALIDATE_BRANCH_GR: ' || 'CE_INVALID_BRANCH_NUM');
3321 END IF;
3322 end fail_check;
3323
3324 procedure pass_check is
3325 begin
3326
3327 IF l_DEBUG in ('Y', 'C') THEN
3328 null;
3329 -- cep_standard.debug('CE_VALIDATE_BRANCH_GR: ' || 'pass_check');
3330 END IF;
3331 end pass_check;
3332
3333 /****************/
3334 /* MAIN SECTION */
3335 /****************/
3336 BEGIN
3337 IF l_DEBUG in ('Y', 'C') THEN
3338 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_gr');
3339 END IF;
3340
3341 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3342
3343 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
3344
3345 IF (BRANCH_VALUE) is not null then
3346
3347 IF length(BRANCH_VALUE) < 5 then
3348 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
3349
3350 IF numeric_result = '0' then /* its numeric so continue */
3351 pass_check;
3352 ELSE
3353 IF l_DEBUG in ('Y', 'C') THEN
3354 cep_standard.debug('branch_gr failed numeric check');
3355 END IF;
3356 fail_check;
3357
3358 END IF; /* end of numeric check */
3359
3360 ELSE
3361 IF l_DEBUG in ('Y', 'C') THEN
3362 cep_standard.debug('branch_gr failed length of branch_value < 5');
3363 END IF;
3364 fail_check;
3365
3366 END IF; /* end of length check */
3367
3368 ELSE
3369
3370 pass_check;
3371
3372 END IF;
3373 /*
3374 IF l_DEBUG in ('Y', 'C') THEN
3375 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_gr');
3376 END IF;
3377 */
3378 END CE_VALIDATE_BRANCH_GR;
3379
3380 /* -------------------------------------------------------------------- */
3381
3382 procedure CE_VALIDATE_BRANCH_IS(Xi_BRANCH_NUMBER in varchar2,
3383 Xi_BANK_ID in NUMBER,
3384 Xo_VALUE_OUT OUT NOCOPY varchar2)
3385 AS
3386 BRANCH_NUM varchar2(60);
3387 numeric_result varchar2(40);
3388
3389 /**************************/
3390 /* SUB-PROCEDURES SECTION */
3391 /**************************/
3392
3393 procedure fail_check is
3394 begin
3395 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3396 fnd_msg_pub.add;
3397 IF l_DEBUG in ('Y', 'C') THEN
3398 cep_standard.debug('CE_VALIDATE_BRANCH_IS: ' || 'CE_INVALID_BRANCH_NUM');
3399 END IF;
3400
3401 end fail_check;
3402
3403 procedure pass_check is
3404 begin
3405
3406 IF l_DEBUG in ('Y', 'C') THEN
3407 null;
3408 -- cep_standard.debug('CE_VALIDATE_BRANCH_IS: ' || 'pass_check');
3409 END IF;
3410 end pass_check;
3411
3412 /****************/
3413 /* MAIN SECTION */
3414 /****************/
3415 BEGIN
3416 IF l_DEBUG in ('Y', 'C') THEN
3417 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_is');
3418 END IF;
3419
3420 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3421
3422 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3423
3424 IF (BRANCH_NUM) is not null then
3425 IF length(BRANCH_NUM) < 5 then
3426 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3427
3428 IF numeric_result = '0' then /* its numeric so continue */
3429 -- Bank number and branch number should be the same
3430
3431 Xo_value_out := lpad(BRANCH_NUM,4,0);
3432 BRANCH_NUM := lpad(BRANCH_NUM,4,0);
3433
3434 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3435
3436 ELSE
3437 IF l_DEBUG in ('Y', 'C') THEN
3438 cep_standard.debug('branch_is fail numeric check');
3439 END IF;
3440 fail_check;
3441 END IF; /* end of numeric check */
3442 ELSE
3443 IF l_DEBUG in ('Y', 'C') THEN
3444 cep_standard.debug('branch_is fail check for length < 5');
3445 END IF;
3446 fail_check;
3447
3448 END IF; /* end of numeric check */
3449
3450 ELSE
3451 pass_check;
3452
3453 END IF; /* end of length check */
3454
3455 /*
3456 IF l_DEBUG in ('Y', 'C') THEN
3457 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_is');
3458 END IF;
3459 */
3460 END CE_VALIDATE_BRANCH_IS;
3461
3462 /* -------------------------------------------------------------------- */
3463
3464 procedure CE_VALIDATE_BRANCH_IE(Xi_BRANCH_NUMBER in varchar2,
3465 Xi_BANK_ID in NUMBER)
3466 AS
3467 BRANCH_NUM varchar2(60);
3468 numeric_result varchar2(40);
3469
3470 /**************************/
3471 /* SUB-PROCEDURES SECTION */
3472 /**************************/
3473
3474 procedure fail_check is
3475 begin
3476 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3477 fnd_msg_pub.add;
3478 IF l_DEBUG in ('Y', 'C') THEN
3479 cep_standard.debug('CE_VALIDATE_BRANCH_IE: ' || 'CE_INVALID_BRANCH_NUM');
3480 END IF;
3481
3482 end fail_check;
3483
3484 procedure pass_check is
3485 begin
3486
3487 IF l_DEBUG in ('Y', 'C') THEN
3488 null;
3489 -- cep_standard.debug('CE_VALIDATE_BRANCH_IE: ' || 'pass_check');
3490 END IF;
3491 end pass_check;
3492
3493 /****************/
3494 /* MAIN SECTION */
3495 /****************/
3496 BEGIN
3497 IF l_DEBUG in ('Y', 'C') THEN
3498 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_ie');
3499 END IF;
3500
3501 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3502
3503 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3504
3505 IF (BRANCH_NUM) is not null then
3506 IF length(BRANCH_NUM) = 8 then
3507 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3508
3509 IF numeric_result = '0' then /* its numeric so continue */
3510 -- Bank number and branch number should be the same
3511
3512 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3513
3514 ELSE
3515 fail_check;
3516 END IF; /* end of numeric check */
3517 ELSE
3518 fail_check;
3519
3520 END IF; /* end of length check */
3521
3522 ELSE
3523 pass_check;
3524
3525 END IF;
3526
3527 /*
3528 IF l_DEBUG in ('Y', 'C') THEN
3529 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_ie');
3530 END IF;
3531 */
3532 END CE_VALIDATE_BRANCH_IE;
3533
3534 /* -------------------------------------------------------------------- */
3535
3536 procedure CE_VALIDATE_BRANCH_IT(Xi_BRANCH_NUMBER in varchar2,
3537 Xi_PASS_MAND_CHECK in varchar2)
3538 AS
3539 BRANCH_VALUE varchar2(30);
3540
3541 numeric_result varchar2(40);
3542
3543 /**************************/
3544 /* SUB-PROCEDURES SECTION */
3545 /**************************/
3546
3547 procedure fail_mandatory is
3548 begin
3549 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
3550 fnd_msg_pub.add;
3551 IF l_DEBUG in ('Y', 'C') THEN
3552 cep_standard.debug('CE_VALIDATE_BRANCH_IT: ' || 'CE_ENTER_BRANCH_NUM');
3553 END IF;
3554 end fail_mandatory;
3555
3556 procedure fail_check is
3557 begin
3558 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3559 fnd_msg_pub.add;
3560 IF l_DEBUG in ('Y', 'C') THEN
3561 cep_standard.debug('CE_VALIDATE_BRANCH_IT: ' || 'CE_INVALID_BRANCH_NUM');
3562 END IF;
3563 end fail_check;
3564
3565 procedure pass_check is
3566 begin
3567
3568 IF l_DEBUG in ('Y', 'C') THEN
3569 null;
3570 -- cep_standard.debug('CE_VALIDATE_BRANCH_IT: ' || 'pass_check');
3571 END IF;
3572 end pass_check;
3573
3574 /****************/
3575 /* MAIN SECTION */
3576 /****************/
3577 BEGIN
3578 IF l_DEBUG in ('Y', 'C') THEN
3579 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_it');
3580 END IF;
3581
3582 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3583
3584 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
3585
3586
3587 IF Xi_PASS_MAND_CHECK = 'F'
3588 then
3589 fail_mandatory;
3590
3591 ELSIF Xi_PASS_MAND_CHECK = 'P'
3592 then
3593
3594 IF length(BRANCH_VALUE) = 5
3595 then
3596
3597 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
3598
3599 IF numeric_result = '0'
3600 then
3601 /* its numeric so continue */
3602
3603 pass_check;
3604 ELSE
3605 fail_check;
3606
3607 END IF; /* end of numeric check */
3608
3609 ELSE
3610 fail_check;
3611
3612 END IF; /* end of length check */
3613 END IF; /* end of mandatory check */
3614 /*
3615 IF l_DEBUG in ('Y', 'C') THEN
3616 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_it');
3617 END IF;
3618 */
3619 END CE_VALIDATE_BRANCH_IT;
3620
3621 /* -------------------------------------------------------------------- */
3622
3623 procedure CE_VALIDATE_BRANCH_LU(Xi_BRANCH_NUMBER in varchar2,
3624 Xi_BANK_ID in NUMBER)
3625 AS
3626 BRANCH_NUM varchar2(60);
3627 numeric_result varchar2(40);
3628
3629 /**************************/
3630 /* SUB-PROCEDURES SECTION */
3631 /**************************/
3632
3633 procedure fail_check is
3634 begin
3635 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3636 fnd_msg_pub.add;
3637 IF l_DEBUG in ('Y', 'C') THEN
3638 cep_standard.debug('CE_VALIDATE_BRANCH_LU: ' || 'CE_INVALID_BRANCH_NUM');
3639 END IF;
3640
3641 end fail_check;
3642
3643 procedure pass_check is
3644 begin
3645
3646 IF l_DEBUG in ('Y', 'C') THEN
3647 null;
3648 -- cep_standard.debug('CE_VALIDATE_BRANCH_LU: ' || 'pass_check');
3649 END IF;
3650 end pass_check;
3651
3652 /****************/
3653 /* MAIN SECTION */
3654 /****************/
3655 BEGIN
3656 IF l_DEBUG in ('Y', 'C') THEN
3657 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_lu');
3658 END IF;
3659
3660 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3661
3662 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3663
3664 IF (BRANCH_NUM) is not null then
3665 IF length(BRANCH_NUM) = 2 then
3666 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3667
3668 IF numeric_result = '0' then /* its numeric so continue */
3669 -- Bank number and branch number should be the same
3670
3671 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3672
3673 ELSE
3674 fail_check;
3675 END IF; /* end of numeric check */
3676 ELSE
3677 fail_check;
3678
3679 END IF; /* end of length check */
3680
3681 ELSE
3682 pass_check;
3683
3684 END IF;
3685
3686 /*
3687 IF l_DEBUG in ('Y', 'C') THEN
3688 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_lu');
3689 END IF;
3690 */
3691
3692 END CE_VALIDATE_BRANCH_LU;
3693
3694 /* -------------------------------------------------------------------- */
3695
3696 procedure CE_VALIDATE_BRANCH_PL(Xi_BRANCH_NUMBER in varchar2,
3697 Xi_BANK_ID in NUMBER)
3698 AS
3699 BRANCH_NUM varchar2(60);
3700 numeric_result varchar2(40);
3701 cal_cd1 number;
3702
3703 /**************************/
3704 /* SUB-PROCEDURES SECTION */
3705 /**************************/
3706
3707 procedure fail_check is
3708 begin
3709 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3710 fnd_msg_pub.add;
3711 IF l_DEBUG in ('Y', 'C') THEN
3712 cep_standard.debug('CE_VALIDATE_BRANCH_PL: ' || 'CE_INVALID_BRANCH_NUM');
3713 END IF;
3714
3715 end fail_check;
3716
3717
3718 procedure pass_check is
3719 begin
3720
3721 IF l_DEBUG in ('Y', 'C') THEN
3722 null;
3723 -- cep_standard.debug('CE_VALIDATE_BRANCH_PL: ' || 'pass_check');
3724 END IF;
3725 end pass_check;
3726
3727 /****************/
3728 /* MAIN SECTION */
3729 /****************/
3730 BEGIN
3731 IF l_DEBUG in ('Y', 'C') THEN
3732 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_pl');
3733 END IF;
3734
3735 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3736
3737 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3738
3739
3740 IF (BRANCH_NUM) is not null then
3741 IF length(BRANCH_NUM) = 8 then
3742 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3743
3744 IF numeric_result = '0' then /* its numeric so continue */
3745 -- Bank number and branch number should be the same
3746
3747 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3748
3749 -- Modulus 10
3750
3751 cal_cd1 := 10 - mod(((to_number(substr(BRANCH_NUM,1,1)) * 1)
3752 +(to_number(substr(BRANCH_NUM,2,1)) * 7)
3753 +(to_number(substr(BRANCH_NUM,3,1)) * 9)
3754 +(to_number(substr(BRANCH_NUM,4,1)) * 3)
3755 +(to_number(substr(BRANCH_NUM,5,1)) * 1)
3756 +(to_number(substr(BRANCH_NUM,6,1)) * 7)
3757 +(to_number(substr(BRANCH_NUM,7,1)) * 9)),10);
3758
3759 IF l_DEBUG in ('Y', 'C') THEN
3760 cep_standard.debug('cal_cd1: ' || cal_cd1);
3761 END IF;
3762
3763 IF cal_cd1 = substr(BRANCH_NUM,8,1) then
3764 pass_check;
3765 else
3766 fail_check;
3767 end if;
3768 ELSE
3769 fail_check;
3770 END IF; /* end of numeric check */
3771 ELSE
3772 fail_check;
3773
3774 END IF; /* end of length check */
3775
3776 ELSE
3777 pass_check;
3778
3779 END IF;
3780
3781 /*
3782 IF l_DEBUG in ('Y', 'C') THEN
3783 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_pl');
3784 END IF;
3785 */
3786 END CE_VALIDATE_BRANCH_PL;
3787
3788 /* -------------------------------------------------------------------- */
3789
3790 procedure CE_VALIDATE_BRANCH_SE(Xi_BRANCH_NUMBER in varchar2,
3791 Xi_BANK_ID in NUMBER)
3792 AS
3793 BRANCH_NUM varchar2(60);
3794 numeric_result varchar2(40);
3795
3796 /**************************/
3797 /* SUB-PROCEDURES SECTION */
3798 /**************************/
3799
3800 procedure fail_check is
3801 begin
3802 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3803 fnd_msg_pub.add;
3804 IF l_DEBUG in ('Y', 'C') THEN
3805 cep_standard.debug('CE_VALIDATE_BRANCH_SE: ' || 'CE_INVALID_BRANCH_NUM');
3806 END IF;
3807
3808 end fail_check;
3809
3810 procedure pass_check is
3811 begin
3812
3813 IF l_DEBUG in ('Y', 'C') THEN
3814 null;
3815 -- cep_standard.debug('CE_VALIDATE_BRANCH_SE: ' || 'pass_check');
3816 END IF;
3817 end pass_check;
3818
3819 /****************/
3820 /* MAIN SECTION */
3821 /****************/
3822 BEGIN
3823 IF l_DEBUG in ('Y', 'C') THEN
3824 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_se');
3825 END IF;
3826
3827 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3828
3829 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3830
3831 IF (BRANCH_NUM) is not null then
3832 IF ((length(BRANCH_NUM) = 4) or (length(BRANCH_NUM) = 5)) then
3833 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3834
3835 IF numeric_result = '0' then /* its numeric so continue */
3836 -- Bank number and branch number should be the same
3837
3838 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3839
3840 ELSE
3841 fail_check;
3842 END IF; /* end of numeric check */
3843 ELSE
3844 fail_check;
3845
3846 END IF; /* end of length check */
3847
3848 ELSE
3849 pass_check;
3850
3851 END IF;
3852 /*
3853 IF l_DEBUG in ('Y', 'C') THEN
3854 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_se');
3855 END IF;
3856 */
3857
3858 END CE_VALIDATE_BRANCH_SE;
3859
3860
3861 /* -------------------------------------------------------------------- */
3862
3863 procedure CE_VALIDATE_BRANCH_CH(Xi_BRANCH_NUMBER in varchar2,
3864 Xi_BANK_ID in NUMBER)
3865 AS
3866 BRANCH_NUM varchar2(60);
3867 numeric_result varchar2(40);
3868
3869 /**************************/
3870 /* SUB-PROCEDURES SECTION */
3871 /**************************/
3872
3873 procedure fail_check is
3874 begin
3875 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3876 fnd_msg_pub.add;
3877 IF l_DEBUG in ('Y', 'C') THEN
3878 cep_standard.debug('CE_VALIDATE_BRANCH_CH: ' || 'CE_INVALID_BRANCH_NUM');
3879 END IF;
3880
3881 end fail_check;
3882
3883 procedure pass_check is
3884 begin
3885
3886 IF l_DEBUG in ('Y', 'C') THEN
3887 null;
3888 -- cep_standard.debug('CE_VALIDATE_BRANCH_CH: ' || 'pass_check');
3889 END IF;
3890 end pass_check;
3891
3892 /****************/
3893 /* MAIN SECTION */
3894 /****************/
3895 BEGIN
3896 IF l_DEBUG in ('Y', 'C') THEN
3897 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_ch');
3898 END IF;
3899
3900 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3901
3902 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3903
3904 IF (BRANCH_NUM) is not null then
3905 IF ((length(BRANCH_NUM) > 2) and (length(BRANCH_NUM) < 6)) then
3906 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3907
3908 IF numeric_result = '0' then /* its numeric so continue */
3909 -- Bank number and branch number should be the same
3910
3911 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3912
3913 ELSE
3914 IF l_DEBUG in ('Y', 'C') THEN
3915 cep_standard.debug('ce_validate_branch_ch fail numeric check');
3916 END IF;
3917 fail_check;
3918 END IF; /* end of numeric check */
3919 ELSE
3920 IF l_DEBUG in ('Y', 'C') THEN
3921 cep_standard.debug('ce_validate_branch_ch fail length');
3922 END IF;
3923 fail_check;
3924
3925 END IF; /* end of length check */
3926
3927 ELSE
3928 pass_check;
3929
3930 END IF;
3931 /*
3932 IF l_DEBUG in ('Y', 'C') THEN
3933 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_ch');
3934 END IF;
3935 */
3936
3937 END CE_VALIDATE_BRANCH_CH;
3938
3939 /* -------------------------------------------------------------------- */
3940
3941 procedure CE_VALIDATE_BRANCH_GB(Xi_BRANCH_NUMBER in varchar2,
3942 Xi_BANK_ID in NUMBER)
3943 AS
3944 BRANCH_NUM varchar2(60);
3945 numeric_result varchar2(40);
3946
3947 /**************************/
3948 /* SUB-PROCEDURES SECTION */
3949 /**************************/
3950
3951 procedure fail_check is
3952 begin
3953 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
3954 fnd_msg_pub.add;
3955 IF l_DEBUG in ('Y', 'C') THEN
3956 cep_standard.debug('CE_VALIDATE_BRANCH_GB: ' || 'CE_INVALID_BRANCH_NUM');
3957 END IF;
3958
3959 end fail_check;
3960
3961 procedure pass_check is
3962 begin
3963
3964 IF l_DEBUG in ('Y', 'C') THEN
3965 null;
3966 -- cep_standard.debug('CE_VALIDATE_BRANCH_GB: ' || 'pass_check');
3967 END IF;
3968 end pass_check;
3969
3970 /****************/
3971 /* MAIN SECTION */
3972 /****************/
3973 BEGIN
3974 IF l_DEBUG in ('Y', 'C') THEN
3975 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_gb');
3976 END IF;
3977 BRANCH_NUM := upper(replace(Xi_BRANCH_NUMBER,' ',''));
3978
3979 BRANCH_NUM := replace(BRANCH_NUM,'-','');
3980
3981 IF (BRANCH_NUM) is not null then
3982 IF length(BRANCH_NUM) = 6 then
3983 numeric_result := ce_check_numeric(BRANCH_NUM,1,length(BRANCH_NUM));
3984
3985 IF numeric_result = '0' then /* its numeric so continue */
3986 -- Bank number and branch number should be the same
3987
3988 CE_VALIDATE_BANKINFO_UPG.COMPARE_BANK_AND_BRANCH_NUM(branch_num, Xi_BANK_ID);
3989
3990 ELSE
3991 fail_check;
3992 END IF; /* end of numeric check */
3993 ELSE
3994 fail_check;
3995
3996 END IF; /* end of length check */
3997
3998 ELSE
3999 pass_check;
4000
4001 END IF;
4002 /*
4003 IF l_DEBUG in ('Y', 'C') THEN
4004 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_gb');
4005 END IF;
4006 */
4007
4008 END CE_VALIDATE_BRANCH_GB;
4009
4010 /* -------------------------------------------------------------------- */
4011
4012 procedure CE_VALIDATE_BRANCH_US(Xi_BRANCH_NUMBER in varchar2,
4013 Xi_PASS_MAND_CHECK in varchar2,
4014 Xo_VALUE_OUT OUT NOCOPY varchar2)
4015 AS
4016 BRANCH_VALUE varchar2(30);
4017 BRANCH_VALUE_OUT varchar2(30);
4018 cal_cd1 number;
4019 cd_value number;
4020
4021 numeric_result varchar2(40);
4022
4023 /**************************/
4024 /* SUB-PROCEDURES SECTION */
4025 /**************************/
4026
4027 procedure fail_mandatory is
4028 begin
4029 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
4030 fnd_msg_pub.add;
4031 IF l_DEBUG in ('Y', 'C') THEN
4032 cep_standard.debug('CE_VALIDATE_BRANCH_US: ' || 'CE_ENTER_BRANCH_NUM');
4033 END IF;
4034 end fail_mandatory;
4035
4036 procedure fail_check is
4037 begin
4038 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
4039 fnd_msg_pub.add;
4040 IF l_DEBUG in ('Y', 'C') THEN
4041 cep_standard.debug('CE_VALIDATE_BRANCH_US: ' || 'CE_INVALID_BRANCH_NUM');
4042 END IF;
4043 end fail_check;
4044
4045
4046 procedure pass_check is
4047 begin
4048
4049 IF l_DEBUG in ('Y', 'C') THEN
4050 null;
4051 -- cep_standard.debug('CE_VALIDATE_BRANCH_US: ' || 'pass_check');
4052 END IF;
4053 end pass_check;
4054
4055 /****************/
4056 /* MAIN SECTION */
4057 /****************/
4058 BEGIN
4059 IF l_DEBUG in ('Y', 'C') THEN
4060 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_us');
4061 END IF;
4062
4063 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
4064
4065 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
4066
4067
4068 IF Xi_PASS_MAND_CHECK = 'F'
4069 then
4070
4071 -- Branch number should be made optional for all US banks.
4072 null;
4073 -- fail_mandatory;
4074
4075 ELSIF Xi_PASS_MAND_CHECK = 'P'
4076 then
4077
4078 BRANCH_VALUE := replace(BRANCH_VALUE,' ','');
4079
4080 IF l_DEBUG in ('Y', 'C') THEN
4081 cep_standard.debug('BRANCH_VALUE: ' || BRANCH_VALUE);
4082 END IF;
4083
4084 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
4085
4086 IF numeric_result = '0' then
4087
4088 IF length(BRANCH_VALUE) < 10 then
4089 IF length(BRANCH_VALUE) <> 9 then
4090 BRANCH_VALUE_OUT := lpad(BRANCH_VALUE,9,0);
4091 END IF;
4092
4093 IF l_DEBUG in ('Y', 'C') THEN
4094 cep_standard.debug('BRANCH_VALUE_OUT: ' || BRANCH_VALUE_OUT);
4095 END IF;
4096
4097 IF (to_number(SUBSTR(BRANCH_VALUE_OUT,1,8)) = '00000000') then
4098 fail_check;
4099 IF l_DEBUG in ('Y', 'C') THEN
4100 cep_standard.debug('fail branch_value = 00000000');
4101 END IF;
4102 ELSE
4103 -- Modulus 10
4104
4105 cal_cd1 := 10 - mod(((to_number(substr(BRANCH_VALUE_OUT,1,1)) * 3)
4106 +(to_number(substr(BRANCH_VALUE_OUT,2,1)) * 7)
4107 +(to_number(substr(BRANCH_VALUE_OUT,3,1)) * 1)
4108 +(to_number(substr(BRANCH_VALUE_OUT,4,1)) * 3)
4109 +(to_number(substr(BRANCH_VALUE_OUT,5,1)) * 7)
4110 +(to_number(substr(BRANCH_VALUE_OUT,6,1)) * 1)
4111 +(to_number(substr(BRANCH_VALUE_OUT,7,1)) * 3)
4112 +(to_number(substr(BRANCH_VALUE_OUT,8,1)) * 7)),10);
4113
4114 cd_value := substr(BRANCH_VALUE_OUT,9,1);
4115
4116 IF l_DEBUG in ('Y', 'C') THEN
4117 cep_standard.debug('cd_value: ' || cd_value ||' cal_cd1: ' || cal_cd1);
4118 END IF;
4119
4120 IF cal_cd1 = cd_value then
4121 pass_check;
4122 Xo_VALUE_OUT := BRANCH_VALUE_OUT;
4123
4124 else
4125 IF l_DEBUG in ('Y', 'C') THEN
4126 cep_standard.debug('failed cd check');
4127 END IF;
4128 fail_check;
4129
4130 end if;
4131
4132 IF l_DEBUG in ('Y', 'C') THEN
4133 cep_standard.debug('Xo_VALUE_OUT: '|| Xo_VALUE_OUT);
4134 END IF;
4135
4136 END IF;
4137 ELSE
4138 IF l_DEBUG in ('Y', 'C') THEN
4139 cep_standard.debug('fail length < 10');
4140 END IF;
4141 fail_check;
4142 END IF; /* end of length check for branch */
4143 ELSE
4144 IF l_DEBUG in ('Y', 'C') THEN
4145 cep_standard.debug('fail numeric check');
4146 END IF;
4147 fail_check;
4148
4149 END IF; /* end of numeric check for branch */
4150
4151 END IF; /* end of mandatory check */
4152
4153 /*
4154 IF l_DEBUG in ('Y', 'C') THEN
4155 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_branch_us');
4156 END IF;
4157 */
4158 END CE_VALIDATE_BRANCH_US;
4159
4160 /* -------------------------------------------------------------------- */
4161 -- added 10/19/04
4162
4163 procedure CE_VALIDATE_BRANCH_AU(Xi_BRANCH_NUMBER in varchar2,
4164 Xi_BANK_ID in NUMBER,
4165 Xi_PASS_MAND_CHECK in varchar2)
4166 AS
4167 BRANCH_VALUE varchar2(30);
4168 BANK_VALUE varchar2(30);
4169 BANK_num varchar2(30);
4170
4171 length_bank_and_branch number;
4172 numeric_result varchar2(40);
4173
4174 /**************************/
4175 /* SUB-PROCEDURES SECTION */
4176 /**************************/
4177
4178 procedure fail_mandatory is
4179 begin
4180 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
4181 fnd_msg_pub.add;
4182 IF l_DEBUG in ('Y', 'C') THEN
4183 cep_standard.debug('CE_VALIDATE_BRANCH_AU: ' || 'CE_ENTER_BRANCH_NUM');
4184 END IF;
4185 end fail_mandatory;
4186
4187 procedure fail_check is
4188 begin
4189 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
4190 fnd_msg_pub.add;
4191 IF l_DEBUG in ('Y', 'C') THEN
4192 cep_standard.debug('CE_VALIDATE_BRANCH_AU: ' || 'CE_INVALID_BRANCH_NUM');
4193 END IF;
4194 end fail_check;
4195
4196 procedure pass_check is
4197 begin
4198
4199 IF l_DEBUG in ('Y', 'C') THEN
4200 cep_standard.debug('CE_VALIDATE_BRANCH_AU: ' || 'pass_check');
4201 END IF;
4202 end pass_check;
4203
4204 /****************/
4205 /* MAIN SECTION */
4206 /****************/
4207 BEGIN
4208 IF l_DEBUG in ('Y', 'C') THEN
4209 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_au');
4210 END IF;
4211
4212 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
4213
4214 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
4215
4216
4217 IF Xi_PASS_MAND_CHECK = 'F'
4218 then
4219 fail_mandatory;
4220
4221 ELSIF Xi_PASS_MAND_CHECK = 'P'
4222 then
4223
4224 IF (length(BRANCH_VALUE) = 3 or length(BRANCH_VALUE) = 4 or length(BRANCH_VALUE) = 6)
4225 then
4226
4227 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
4228
4229 IF numeric_result = '0'
4230 then
4231 /* its numeric so continue */
4232 -- Bank number and branch number should have total of 6 digits
4233
4234 OPEN get_bank_num(Xi_BANK_ID);
4235 FETCH get_bank_num INTO bank_num;
4236
4237 BANK_VALUE := upper(replace(bank_num,' ',''));
4238 BANK_VALUE := replace(BANK_VALUE,'-','');
4239 length_bank_and_branch := length(BRANCH_VALUE) + nvl(length(BANK_VALUE), 0);
4240
4241 close get_bank_num;
4242
4243 IF length_bank_and_branch = 6 THEN
4244 pass_check;
4245 ELSE
4246 fail_check;
4247 END IF;
4248 ELSE
4249 fail_check;
4250
4251 END IF; /* end of numeric check */
4252
4253 ELSE
4254 fail_check;
4255
4256 END IF; /* end of length check */
4257 END IF; /* end of mandatory check */
4258 IF l_DEBUG in ('Y', 'C') THEN
4259 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_branch_au');
4260 END IF;
4261
4262 END CE_VALIDATE_BRANCH_AU;
4263 /* -------------------------------------------------------------------- */
4264
4265 procedure CE_VALIDATE_BRANCH_IL(Xi_BRANCH_NUMBER in varchar2,
4266 Xi_PASS_MAND_CHECK in varchar2)
4267 AS
4268 BRANCH_VALUE varchar2(30);
4269
4270 numeric_result varchar2(40);
4271
4272 /**************************/
4273 /* SUB-PROCEDURES SECTION */
4274 /**************************/
4275
4276 procedure fail_mandatory is
4277 begin
4278 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
4279 fnd_msg_pub.add;
4280 IF l_DEBUG in ('Y', 'C') THEN
4281 cep_standard.debug('CE_VALIDATE_BRANCH_IL: ' || 'CE_ENTER_BRANCH_NUM');
4282 END IF;
4283 end fail_mandatory;
4284
4285 procedure fail_check is
4286 begin
4287 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
4288 fnd_msg_pub.add;
4289 IF l_DEBUG in ('Y', 'C') THEN
4290 cep_standard.debug('CE_VALIDATE_BRANCH_IL: ' || 'CE_INVALID_BRANCH_NUM');
4291 END IF;
4292 end fail_check;
4293
4294 procedure pass_check is
4295 begin
4296
4297 IF l_DEBUG in ('Y', 'C') THEN
4298 cep_standard.debug('CE_VALIDATE_BRANCH_IL: ' || 'pass_check');
4299 END IF;
4300 end pass_check;
4301
4302 /****************/
4303 /* MAIN SECTION */
4304 /****************/
4305 BEGIN
4306 IF l_DEBUG in ('Y', 'C') THEN
4307 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_il');
4308 END IF;
4309
4310 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
4311
4312 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
4313
4314
4315 IF Xi_PASS_MAND_CHECK = 'F'
4316 then
4317 fail_mandatory;
4318
4319 ELSIF Xi_PASS_MAND_CHECK = 'P'
4320 then
4321
4322 IF length(BRANCH_VALUE) = 3
4323 then
4324
4325 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
4326
4327 IF numeric_result = '0'
4328 then
4329 /* its numeric so continue */
4330
4331 pass_check;
4332 ELSE
4333 fail_check;
4334
4335 END IF; /* end of numeric check */
4336
4337 ELSE
4338 fail_check;
4339
4340 END IF; /* end of length check */
4341 END IF; /* end of mandatory check */
4342 IF l_DEBUG in ('Y', 'C') THEN
4343 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_branch_il');
4344 END IF;
4345
4346 END CE_VALIDATE_BRANCH_IL;
4347 /* -------------------------------------------------------------------- */
4348
4349 procedure CE_VALIDATE_BRANCH_NZ(Xi_BRANCH_NUMBER in varchar2,
4350 Xi_PASS_MAND_CHECK in varchar2)
4351 AS
4352 BRANCH_VALUE varchar2(30);
4353
4354 numeric_result varchar2(40);
4355
4356 /**************************/
4357 /* SUB-PROCEDURES SECTION */
4358 /**************************/
4359
4360 procedure fail_mandatory is
4361 begin
4362 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
4363 fnd_msg_pub.add;
4364 IF l_DEBUG in ('Y', 'C') THEN
4365 cep_standard.debug('CE_VALIDATE_BRANCH_NZ: ' || 'CE_ENTER_BRANCH_NUM');
4366 END IF;
4367 end fail_mandatory;
4368
4369 procedure fail_check is
4370 begin
4371 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
4372 fnd_msg_pub.add;
4373 IF l_DEBUG in ('Y', 'C') THEN
4374 cep_standard.debug('CE_VALIDATE_BRANCH_NZ: ' || 'CE_INVALID_BRANCH_NUM');
4375 END IF;
4376 end fail_check;
4377
4378 procedure pass_check is
4379 begin
4380
4381 IF l_DEBUG in ('Y', 'C') THEN
4382 cep_standard.debug('CE_VALIDATE_BRANCH_NZ: ' || 'pass_check');
4383 END IF;
4384 end pass_check;
4385
4386 /****************/
4387 /* MAIN SECTION */
4388 /****************/
4389 BEGIN
4390 IF l_DEBUG in ('Y', 'C') THEN
4391 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_nz');
4392 END IF;
4393
4394 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
4395
4396 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
4397
4398
4399 IF Xi_PASS_MAND_CHECK = 'F'
4400 then
4401 fail_mandatory;
4402
4403 ELSIF Xi_PASS_MAND_CHECK = 'P'
4404 then
4405
4406 IF length(BRANCH_VALUE) = 4
4407 then
4408
4409 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
4410
4411 IF numeric_result = '0'
4412 then
4413 /* its numeric so continue */
4414
4415 pass_check;
4416 ELSE
4417 fail_check;
4418
4419 END IF; /* end of numeric check */
4420
4421 ELSE
4422 fail_check;
4423
4424 END IF; /* end of length check */
4425 END IF; /* end of mandatory check */
4426 IF l_DEBUG in ('Y', 'C') THEN
4427 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_branch_nz');
4428 END IF;
4429
4430 END CE_VALIDATE_BRANCH_NZ;
4431
4432 /* -------------------------------------------------------------------- */
4433
4434 procedure CE_VALIDATE_BRANCH_JP(Xi_BRANCH_NUMBER in varchar2,
4435 Xi_BRANCH_NAME_ALT in varchar2,
4436 Xi_PASS_MAND_CHECK in varchar2,
4437 Xi_VALIDATION_TYPE in varchar2)
4438 AS
4439 BRANCH_VALUE varchar2(30);
4440
4441 numeric_result varchar2(40);
4442
4443 /**************************/
4444 /* SUB-PROCEDURES SECTION */
4445 /**************************/
4446
4447 procedure fail_mandatory is
4448 begin
4449 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NUM');
4450 fnd_msg_pub.add;
4451 IF l_DEBUG in ('Y', 'C') THEN
4452 cep_standard.debug('CE_VALIDATE_BRANCH_JP: ' || 'CE_ENTER_BRANCH_NUM');
4453 END IF;
4454 end fail_mandatory;
4455
4456 procedure fail_branch_name_alt is
4457 begin
4458 fnd_message.set_name ('CE', 'CE_ENTER_BRANCH_NAME_ALT');
4459 fnd_msg_pub.add;
4460 IF l_DEBUG in ('Y', 'C') THEN
4461 cep_standard.debug('CE_VALIDATE_BRANCH_JP: ' || 'CE_ENTER_BRANCH_NAME_ALT');
4462 END IF;
4463 end fail_branch_name_alt;
4464
4465 procedure fail_check is
4466 begin
4467 fnd_message.set_name ('CE', 'CE_INVALID_BRANCH_NUM');
4468 fnd_msg_pub.add;
4469 IF l_DEBUG in ('Y', 'C') THEN
4470 cep_standard.debug('CE_VALIDATE_BRANCH_JP: ' || 'CE_INVALID_BRANCH_NUM');
4471 END IF;
4472 end fail_check;
4473
4474 procedure pass_check is
4475 begin
4476
4477 IF l_DEBUG in ('Y', 'C') THEN
4478 cep_standard.debug('CE_VALIDATE_BRANCH_JP: ' || 'pass_check');
4479 END IF;
4480 end pass_check;
4481
4482 /****************/
4483 /* MAIN SECTION */
4484 /****************/
4485 BEGIN
4486 IF l_DEBUG in ('Y', 'C') THEN
4487 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_branch_jp');
4488 END IF;
4489
4490 BRANCH_VALUE := upper(replace(Xi_BRANCH_NUMBER,' ',''));
4491
4492 BRANCH_VALUE := replace(BRANCH_VALUE,'-','');
4493
4494
4495 IF Xi_PASS_MAND_CHECK = 'F'
4496 then
4497 fail_mandatory;
4498
4499 ELSIF Xi_PASS_MAND_CHECK = 'P'
4500 then
4501
4502 IF length(BRANCH_VALUE) = 4
4503 then
4504
4505 numeric_result := ce_check_numeric(BRANCH_VALUE,1,length(BRANCH_VALUE));
4506
4507 IF numeric_result = '0'
4508 then
4509 /* its numeric so continue */
4510
4511 pass_check;
4512 ELSE
4513 fail_check;
4514
4515 END IF; /* end of numeric check */
4516
4517 ELSE
4518 fail_check;
4519
4520 END IF; /* end of length check */
4521 END IF; /* end of mandatory check */
4522
4523 IF (Xi_VALIDATION_TYPE = 'ALL') THEN
4524 IF ( Xi_BRANCH_NAME_ALT is null) THEN
4525
4526 fail_branch_name_alt ;
4527 END IF;
4528 END IF;
4529
4530
4531 IF l_DEBUG in ('Y', 'C') THEN
4532 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_branch_jp');
4533 END IF;
4534
4535 END CE_VALIDATE_BRANCH_JP;
4536
4537 /* --------------------------------------------------------------------
4538 | PRIVATE PROCEDURE |
4539 | CE_VALIDATE_ACCOUNT_* |
4540 | |
4541 | CALLED BY |
4542 | CE_VALIDATE_ACCOUNT |
4543 | |
4544 | DESCRIPTION |
4545 | ACCOUNT PROCEDURES, Validate 1 or more of the following: |
4546 | 1. Account number length |
4547 | 2. Account number datatype (numeric, alphanumeric, or alphabet |
4548 | 3. Account number Algorithm |
4549 | |
4550 | RETURN |
4551 | Xo_VALUE_OUT - Account Number is return with leading 0 |
4552 | (Not for all countries) |
4553 --------------------------------------------------------------------- */
4554
4555
4556 procedure CE_VALIDATE_ACCOUNT_AT (Xi_ACCOUNT_NUMBER in varchar2,
4557 Xi_PASS_MAND_CHECK in varchar2,
4558 Xo_VALUE_OUT OUT NOCOPY varchar2)
4559 AS
4560 account_value varchar2(30);
4561 chk_chars varchar2(30);
4562
4563 /*******************************/
4564 /* SUB FUNCTIONS and PROCEDURES*/
4565 /*******************************/
4566
4567 procedure fail_check is
4568 begin
4569 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
4570 fnd_msg_pub.add;
4571 IF l_DEBUG in ('Y', 'C') THEN
4572 cep_standard.debug('CE_VALIDATE_ACCOUNT_AT: ' || 'CE_INVALID_ACCOUNT_NUM');
4573 END IF;
4574 end fail_check;
4575
4576 procedure pass_check is
4577 begin
4578
4579 IF l_DEBUG in ('Y', 'C') THEN
4580 null;
4581 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_AT: ' || 'pass_check');
4582 END IF;
4583 end pass_check;
4584
4585 /****************/
4586 /* MAIN SECTION */
4587 /****************/
4588 BEGIN
4589 IF l_DEBUG in ('Y', 'C') THEN
4590 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_at');
4591 END IF;
4592
4593 account_value := upper(Xi_ACCOUNT_NUMBER );
4594 Xo_VALUE_OUT := account_value;
4595
4596 account_value := replace(account_value,' ','');
4597
4598 chk_chars := translate(account_value,'123456789'
4599 ,'00000000000000000000000000000000000');
4600
4601 IF length(account_value) < 12
4602 then
4603 chk_chars := lpad(chk_chars,11,0);
4604 IF CHK_CHARS = '00000000000'
4605 then
4606 Xo_VALUE_OUT := lpad(account_value,11,0);
4607 pass_check;
4608 ELSE
4609 fail_check;
4610 END IF; /* end number check */
4611 ELSE
4612 fail_check;
4613
4614 END IF; /* end of length check */
4615 /*
4616 IF l_DEBUG in ('Y', 'C') THEN
4617 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_at');
4618 END IF;
4619 */
4620 END CE_VALIDATE_ACCOUNT_AT;
4621
4622 /**************** End of Procedure CE_VALIDATE_ACCOUNT_AT ***********/
4623
4624
4625 procedure CE_VALIDATE_ACCOUNT_PT (Xi_ACCOUNT_NUMBER in varchar2,
4626 Xi_PASS_MAND_CHECK in varchar2,
4627 Xo_VALUE_OUT OUT NOCOPY varchar2)
4628 AS
4629 ACCOUNT_VALUE varchar2(30);
4630 numeric_result varchar2(40);
4631
4632 /**************************/
4633 /* SUB-PROCEDURES SECTION */
4634 /**************************/
4635
4636
4637 procedure fail_check is
4638 begin
4639 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
4640 fnd_msg_pub.add;
4641 IF l_DEBUG in ('Y', 'C') THEN
4642 cep_standard.debug('CE_VALIDATE_ACCOUNT_PT: ' || 'CE_INVALID_ACCOUNT_NUM');
4643 END IF;
4644 end fail_check;
4645
4646 procedure pass_check is
4647 begin
4648
4649 IF l_DEBUG in ('Y', 'C') THEN
4650 null;
4651 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_PT: ' || 'pass_check');
4652 END IF;
4653 end pass_check;
4654
4655 /****************/
4656 /* MAIN SECTION */
4657 /****************/
4658 BEGIN
4659 IF l_DEBUG in ('Y', 'C') THEN
4660 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_pt');
4661 END IF;
4662
4663 ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
4664 ACCOUNT_VALUE := replace(ACCOUNT_VALUE,' ','');
4665
4666 IF length(ACCOUNT_VALUE) <= 11
4667 then
4668 ACCOUNT_VALUE := lpad(ACCOUNT_VALUE,11,0);
4669 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
4670
4671 IF numeric_result = '0'
4672 then /* its numeric so continue */
4673 Xo_VALUE_OUT := ACCOUNT_VALUE;
4674 pass_check;
4675 ELSE
4676 fail_check;
4677
4678 END IF; /* end of numeric check */
4679
4680 ELSE
4681 fail_check;
4682
4683 END IF; /* end of length check */
4684 /*
4685 IF l_DEBUG in ('Y', 'C') THEN
4686 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_pt');
4687 END IF;
4688 */
4689 END CE_VALIDATE_ACCOUNT_PT;
4690
4691 /**************** End of Procedure CE_VALIDATE_ACCOUNT_PT ***********/
4692
4693 procedure CE_VALIDATE_ACCOUNT_BE (Xi_ACCOUNT_NUMBER in varchar2,
4694 Xi_PASS_MAND_CHECK in varchar2)
4695 AS
4696 ACCOUNT_VALUE varchar2(30);
4697 numeric_result varchar2(40);
4698 BANK_CODE varchar2(3);
4699 MIDDLE varchar2(7);
4700 CHECK_DIGIT varchar2(2);
4701 CONCED varchar2(30);
4702
4703 /**************************/
4704 /* SUB-PROCEDURES SECTION */
4705 /**************************/
4706
4707
4708 procedure fail_check is
4709 begin
4710 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
4711 fnd_msg_pub.add;
4712 IF l_DEBUG in ('Y', 'C') THEN
4713 cep_standard.debug('CE_VALIDATE_ACCOUNT_BE: ' || 'CE_INVALID_ACCOUNT_NUM');
4714 END IF;
4715 end fail_check;
4716
4717 procedure pass_check is
4718 begin
4719
4720 IF l_DEBUG in ('Y', 'C') THEN
4721 null;
4722 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_BE: ' || 'pass_check');
4723 END IF;
4724 end pass_check;
4725
4726 /****************/
4727 /* MAIN SECTION */
4728 /****************/
4729 BEGIN
4730 IF l_DEBUG in ('Y', 'C') THEN
4731 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_be');
4732 END IF;
4733
4734 ACCOUNT_VALUE := upper(substr(Xi_ACCOUNT_NUMBER ,1,14));
4735 BANK_CODE := substr(ACCOUNT_VALUE,1,3);
4736 MIDDLE := substr(ACCOUNT_VALUE,5,7);
4737 CHECK_DIGIT := substr(ACCOUNT_VALUE,13,2);
4738 CONCED := BANK_CODE||MIDDLE||CHECK_DIGIT;
4739
4740 numeric_result := ce_check_numeric(CONCED,1,length(CONCED));
4741
4742 CONCED := replace(CONCED,' ','');
4743
4744 IF length(CONCED) = 12
4745 THEN IF numeric_result = '0'
4746 THEN /* its numeric so continue */
4747
4748 IF MOD(BANK_CODE||MIDDLE,97) = 0
4749 AND CHECK_DIGIT = 97
4750 THEN pass_check;
4751 /*
4752 2261587 fbreslin: 00 is never a valid check digit, even if
4753 the MOD of the account number is 0
4754 */
4755
4756 ELSIF MOD(bank_code||middle, 97) = 0
4757 AND check_digit = 00
4758 THEN fail_check;
4759
4760 ELSIF MOD(BANK_CODE||MIDDLE,97) = CHECK_DIGIT
4761 THEN pass_check;
4762 ELSE fail_check;
4763
4764 END IF;
4765
4766 ELSE fail_check;
4767
4768 END IF; /* end of numeric check */
4769
4770 ELSE fail_check;
4771
4772 END IF; /* end of length check */
4773 /*
4774 IF l_DEBUG in ('Y', 'C') THEN
4775 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_be');
4776 END IF;
4777 */
4778
4779 END CE_VALIDATE_ACCOUNT_BE;
4780
4781
4782 /**************** End of Procedure CE_VALIDATE_ACCOUNT_BE ***********/
4783
4784 procedure CE_VALIDATE_ACCOUNT_DK (Xi_ACCOUNT_NUMBER in varchar2,
4785 Xi_PASS_MAND_CHECK in varchar2,
4786 Xo_VALUE_OUT OUT NOCOPY varchar2)
4787 AS
4788 check_digit_ok_flag boolean;
4789 account_value varchar2(30);
4790 chk_chars varchar2(30);
4791
4792 /*******************************/
4793 /* SUB FUNCTIONS and PROCEDURES*/
4794 /*******************************/
4795
4796 procedure fail_check is
4797 begin
4798 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
4799 fnd_msg_pub.add;
4800 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
4801 IF l_DEBUG in ('Y', 'C') THEN
4802 cep_standard.debug('CE_VALIDATE_ACCOUNT_DK: ' || 'CE_INVALID_ACCOUNT_NUM');
4803 END IF;
4804
4805 end fail_check;
4806
4807 procedure pass_check is
4808 begin
4809 IF l_DEBUG in ('Y', 'C') THEN
4810 null;
4811 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_DK: ' || 'pass_check');
4812 END IF;
4813
4814 end pass_check;
4815
4816 /****************/
4817 /* MAIN SECTION */
4818 /****************/
4819 BEGIN
4820 IF l_DEBUG in ('Y', 'C') THEN
4821 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_dk');
4822 END IF;
4823
4824 account_value := upper(Xi_ACCOUNT_NUMBER );
4825 Xo_VALUE_OUT := account_value;
4826
4827 account_value := replace(account_value,' ','');
4828
4829 chk_chars := translate(account_value,'123456789'
4830 ,'00000000000000000000000000000000000');
4831
4832 IF length(account_value) < 11
4833 then
4834 IF CHK_CHARS = '0000000000'
4835 then
4836 Xo_VALUE_OUT := account_value;
4837 pass_check;
4838 ELSE
4839 fail_check;
4840 END IF; /* end number check */
4841
4842
4843 ELSE
4844 fail_check;
4845
4846 END IF; /* end of length check */
4847 /*
4848 IF l_DEBUG in ('Y', 'C') THEN
4849 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_dk');
4850 END IF;
4851 */
4852 END CE_VALIDATE_ACCOUNT_DK;
4853
4854 /**************** End of Procedure CE_VALIDATE_ACCOUNT_DK ***********/
4855
4856 procedure CE_VALIDATE_ACCOUNT_FR (Xi_ACCOUNT_NUMBER in varchar2,
4857 Xi_PASS_MAND_CHECK in varchar2,
4858 Xo_VALUE_OUT OUT NOCOPY varchar2)
4859 AS
4860 ACCOUNT_VALUE varchar2(30);
4861 CHK_CHARS varchar2(30);
4862
4863
4864 /**************************/
4865 /* SUB-PROCEDURES SECTION */
4866 /**************************/
4867
4868
4869 procedure fail_check is
4870 begin
4871 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
4872 fnd_msg_pub.add;
4873 IF l_DEBUG in ('Y', 'C') THEN
4874 cep_standard.debug('CE_VALIDATE_ACCOUNT_FR: ' || 'CE_INVALID_ACCOUNT_NUM');
4875 END IF;
4876 end fail_check;
4877
4878 procedure pass_check is
4879 begin
4880
4881 IF l_DEBUG in ('Y', 'C') THEN
4882 null;
4883 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_FR: ' || 'pass_check');
4884 END IF;
4885 end pass_check;
4886
4887 /****************/
4888 /* MAIN SECTION */
4889 /****************/
4890 BEGIN
4891 IF l_DEBUG in ('Y', 'C') THEN
4892 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_fr');
4893 END IF;
4894
4895 ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
4896 Xo_VALUE_OUT := ACCOUNT_VALUE;
4897
4898 ACCOUNT_VALUE := replace(ACCOUNT_VALUE,' ','');
4899
4900 chk_chars := translate(ACCOUNT_VALUE,'ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'
4901 ,'00000000000000000000000000000000000');
4902
4903
4904 IF length(ACCOUNT_VALUE) < 12
4905 then
4906 chk_chars := lpad(chk_chars,11,0);
4907 IF CHK_CHARS = '00000000000'
4908 then
4909 Xo_VALUE_OUT := lpad(ACCOUNT_VALUE,11,0);
4910 ELSE
4911 fail_check;
4912 END IF; /* end of char and number check */
4913
4914
4915 ELSE
4916 fail_check;
4917
4918 END IF; /* end of length check */
4919
4920 /*
4921 IF l_DEBUG in ('Y', 'C') THEN
4922 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_fr');
4923 END IF;
4924 */
4925 END CE_VALIDATE_ACCOUNT_FR;
4926
4927
4928 /******************* End of Procedure ce_validate_account_fr ***********/
4929
4930
4931 procedure CE_VALIDATE_ACCOUNT_NL(Xi_ACCOUNT_NUMBER in varchar2,
4932 Xi_PASS_MAND_CHECK in varchar2)
4933 AS
4934 ACCOUNT_VALUE varchar2(30);
4935 numeric_result varchar2(40);
4936 position_i number(2);
4937 integer_value number(1);
4938 multiplied_number number(2);
4939 multiplied_sum number(3);
4940 loop_sum number(3);
4941
4942 /**************************/
4943 /* SUB-PROCEDURES SECTION */
4944 /**************************/
4945
4946 procedure check_11(input_value IN VARCHAR2,
4947 mult_sum_result OUT NOCOPY NUMBER)
4948 IS
4949 BEGIN
4950 FOR position_i in 1..10 LOOP
4951 integer_value := substr(input_value,position_i,1);
4952
4953 multiplied_number := integer_value * (11-position_i);
4954 loop_sum := loop_sum + multiplied_number;
4955
4956 END LOOP;
4957 mult_sum_result := loop_sum;
4958 END check_11;
4959
4960
4961 procedure fail_check is
4962 begin
4963 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
4964 fnd_msg_pub.add;
4965 IF l_DEBUG in ('Y', 'C') THEN
4966 cep_standard.debug('CE_VALIDATE_ACCOUNT_NL: ' || 'CE_INVALID_ACCOUNT_NUM');
4967 END IF;
4968 end fail_check;
4969
4970 procedure pass_check is
4971 begin
4972
4973 IF l_DEBUG in ('Y', 'C') THEN
4974 null;
4975 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_NL: ' || 'pass_check');
4976 END IF;
4977 end pass_check;
4978
4979 /****************/
4980 /* MAIN SECTION */
4981 /****************/
4982 BEGIN
4983 IF l_DEBUG in ('Y', 'C') THEN
4984 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_nl');
4985 END IF;
4986
4987 multiplied_number := 0;
4988 multiplied_sum := 0;
4989 loop_sum := 0;
4990
4991
4992 ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
4993 IF substr(ACCOUNT_VALUE,1,1) = 'P' OR substr(ACCOUNT_VALUE,1,1) = 'G'
4994 then
4995
4996 IF length(ACCOUNT_VALUE) < 9 and instr(ACCOUNT_VALUE,' ') = 0
4997 then
4998 numeric_result := ce_check_numeric(ACCOUNT_VALUE,2,length(ACCOUNT_VALUE));
4999 IF numeric_result = '0'
5000 then
5001 /* its numeric so continue */
5002
5003 IF substr(ACCOUNT_VALUE,2,7) > 0 and substr(ACCOUNT_VALUE,2,7) <= 9999999
5004 then
5005
5006 pass_check;
5007 ELSE
5008
5009 fail_check;
5010 END IF;
5011 ELSE /* failed numeric check */
5012
5013 fail_check;
5014 END IF;
5015
5016 ELSE /* failed length check */
5017
5018 fail_check;
5019 END IF;
5020
5021 ELSE /* not a P or G account */
5022 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5023 IF numeric_result = '0'
5024 then
5025 /* its numeric so continue */
5026 IF length(ACCOUNT_VALUE) = 9 and instr(ACCOUNT_VALUE,' ') = 0
5027 then
5028 ACCOUNT_VALUE := '0'||ACCOUNT_VALUE;
5029 check_11(ACCOUNT_VALUE,multiplied_sum);
5030 IF mod(multiplied_sum,11) <> 0
5031 then
5032 fail_check;
5033 ELSE
5034 pass_check;
5035 END IF;
5036
5037 ELSIF length(ACCOUNT_VALUE) = 10 and instr(ACCOUNT_VALUE,' ') = 0
5038 then
5039 check_11(ACCOUNT_VALUE,multiplied_sum);
5040 IF mod(multiplied_sum,11) <> 0
5041 then
5042 fail_check;
5043 ELSE
5044 pass_check;
5045 END IF;
5046
5047 ELSE
5048 fail_check;
5049
5050 END IF;
5051
5052 ELSE /* failed numeric check */
5053 fail_check;
5054
5055 END IF;
5056
5057 END IF;
5058
5059 /*
5060 IF l_DEBUG in ('Y', 'C') THEN
5061 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_nl');
5062 END IF;
5063 */
5064 END CE_VALIDATE_ACCOUNT_NL;
5065
5066 /**************** End of Procedure CE_validate_account_nl ******/
5067
5068
5069 procedure CE_VALIDATE_ACCOUNT_ES(Xi_ACCOUNT_NUMBER in varchar2,
5070 Xi_PASS_MAND_CHECK in varchar2,
5071 Xo_VALUE_OUT OUT NOCOPY varchar2)
5072 AS
5073 ACCOUNT_VALUE varchar2(30);
5074
5075 numeric_result varchar2(40);
5076
5077 /**************************/
5078 /* SUB-PROCEDURES SECTION */
5079 /**************************/
5080
5081 /*Bug Fix:880887*/
5082 /*Changed the Xo_RET_VAR='F' to 'W' ,so that a warning message is displayed*/
5083 /*for spain instead of Error message*/
5084 procedure fail_check is
5085 begin
5086 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5087 fnd_msg_pub.add;
5088 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
5089 IF l_DEBUG in ('Y', 'C') THEN
5090 cep_standard.debug('CE_VALIDATE_ACCOUNT_ES: ' || 'CE_INVALID_ACCOUNT_NUM');
5091 END IF;
5092
5093 end fail_check;
5094
5095 procedure pass_check is
5096 begin
5097
5098 IF l_DEBUG in ('Y', 'C') THEN
5099 null;
5100 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_ES: ' || 'pass_check');
5101 END IF;
5102 end pass_check;
5103
5104 /****************/
5105 /* MAIN SECTION */
5106 /****************/
5107 BEGIN
5108 IF l_DEBUG in ('Y', 'C') THEN
5109 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_es');
5110 END IF;
5111
5112 ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
5113 Xo_VALUE_OUT := ACCOUNT_VALUE;
5114
5115
5116 ACCOUNT_VALUE := replace(ACCOUNT_VALUE,' ','');
5117
5118 IF length(ACCOUNT_VALUE) < 11
5119 then
5120 ACCOUNT_VALUE := lpad(ACCOUNT_VALUE,10,0);
5121 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5122
5123 IF numeric_result = '0'
5124 then
5125 /* its numeric so continue */
5126 Xo_VALUE_OUT := ACCOUNT_VALUE;
5127 pass_check;
5128 ELSE
5129 fail_check;
5130
5131 END IF; /* end of numeric check */
5132
5133 ELSE
5134 fail_check;
5135
5136 END IF; /* end of length check */
5137 /*
5138 IF l_DEBUG in ('Y', 'C') THEN
5139 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_es');
5140 END IF;
5141 */
5142 END CE_VALIDATE_ACCOUNT_ES;
5143
5144
5145 /************** End of Procedure CE_VALIDATE_ACCOUNT_ES **************/
5146
5147
5148 procedure CE_VALIDATE_ACCOUNT_NO (Xi_ACCOUNT_NUMBER in varchar2,
5149 Xi_PASS_MAND_CHECK in varchar2)
5150 AS
5151 ACCOUNT_VALUE varchar2(30);
5152 numeric_result varchar2(40);
5153 computed_sum number(30);
5154 check_digit varchar2(20);
5155
5156 /**************************/
5157 /* SUB-PROCEDURES SECTION */
5158 /**************************/
5159
5160 procedure fail_check is
5161 begin
5162 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5163 fnd_msg_pub.add;
5164 IF l_DEBUG in ('Y', 'C') THEN
5165 cep_standard.debug('CE_VALIDATE_ACCOUNT_NO: ' || 'CE_INVALID_ACCOUNT_NUM');
5166 END IF;
5167 end fail_check;
5168
5169 procedure pass_check is
5170 begin
5171 IF l_DEBUG in ('Y', 'C') THEN
5172 null;
5173 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_NO: ' || 'pass_check');
5174 END IF;
5175
5176 end pass_check;
5177
5178
5179 /****************/
5180 /* MAIN SECTION */
5181 /****************/
5182 BEGIN
5183 IF l_DEBUG in ('Y', 'C') THEN
5184 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_no');
5185 END IF;
5186
5187 ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
5188
5189 IF length(ACCOUNT_VALUE) = 11 and ACCOUNT_VALUE <> '08271000279'and
5190 (substr(ACCOUNT_VALUE,5,2) <> '00')
5191 then
5192 check_digit := substr(ACCOUNT_VALUE,11,1);
5193
5194 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5195
5196 IF numeric_result = '0'
5197 then
5198 computed_sum :=
5199 5 * substr(ACCOUNT_VALUE,1,1) +
5200 4 * substr(ACCOUNT_VALUE,2,1) +
5201 3 * substr(ACCOUNT_VALUE,3,1) +
5202 2 * substr(ACCOUNT_VALUE,4,1) +
5203 7 * substr(ACCOUNT_VALUE,5,1) +
5204 6 * substr(ACCOUNT_VALUE,6,1) +
5205 5 * substr(ACCOUNT_VALUE,7,1) +
5206 4 * substr(ACCOUNT_VALUE,8,1) +
5207 3 * substr(ACCOUNT_VALUE,9,1) +
5208 2 * substr(ACCOUNT_VALUE,10,1);
5209
5210 IF mod((computed_sum + check_digit),11)=0
5211 then
5212 pass_check;
5213 ELSE
5214 fail_check; /* failed numeric check */
5215 END IF;
5216 ELSE
5217 fail_check;
5218 END IF;
5219
5220 /* 10/19/04 7-digit account numbers are no longer valid in Norway
5221 ELSIF length(ACCOUNT_VALUE) = 7 and ACCOUNT_VALUE <> '1520006'
5222 and substr(ACCOUNT_VALUE,1,2) <> 12
5223 and substr(ACCOUNT_VALUE,1,2) <> 19
5224 then
5225 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5226
5227 IF numeric_result = '0'
5228 then
5229 account_value := lpad(ACCOUNT_VALUE,11,0);
5230 computed_sum :=
5231 (1 * substr(ACCOUNT_VALUE,1,1)) +
5232 substr(2 * substr(ACCOUNT_VALUE,2,1),1,1) +
5233 nvl(substr(2 * substr(ACCOUNT_VALUE,2,1),2,1),0) +
5234 (1 * substr(ACCOUNT_VALUE,3,1)) +
5235 substr(2 * substr(ACCOUNT_VALUE,4,1),1,1) +
5236 nvl(substr(2 * substr(ACCOUNT_VALUE,4,1),2,1),0) +
5237 (1 * substr(ACCOUNT_VALUE,5,1)) +
5238 substr(2 * substr(ACCOUNT_VALUE,6,1),1,1) +
5239 nvl(substr(2 * substr(ACCOUNT_VALUE,6,1),2,1),0) +
5240 (1 * substr(ACCOUNT_VALUE,7,1)) +
5241 substr(2 * substr(ACCOUNT_VALUE,8,1),1,1) +
5242 nvl(substr(2 * substr(ACCOUNT_VALUE,8,1),2,1),0) +
5243 (1 * substr(ACCOUNT_VALUE,9,1)) +
5244 substr(2 * substr(ACCOUNT_VALUE,10,1),1,1) +
5245 nvl(substr(2 * substr(ACCOUNT_VALUE,10,1),2,1),0) +
5246 (1 * substr(ACCOUNT_VALUE,11,1));
5247
5248 IF mod(computed_sum,10) = 0
5249 then
5250 pass_check;
5251 ELSE
5252 fail_check;
5253 END IF;
5254
5255 ELSE
5256 fail_check; -- failed numeric check
5257
5258 END IF;
5259 */
5260 ELSE
5261 fail_check;
5262
5263 END IF;
5264 /*
5265 IF l_DEBUG in ('Y', 'C') THEN
5266 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_no');
5267 END IF;
5268 */
5269 END CE_VALIDATE_ACCOUNT_NO;
5270
5271
5272 /********* END CE_VALIDATE_ACCOUNT_NO *************************/
5273
5274 /* 960103 VVAHAMAA Bank Account Validation for Finland */
5275
5276 procedure CE_VALIDATE_ACCOUNT_FI (Xi_ACCOUNT_NUMBER in varchar2,
5277 Xi_PASS_MAND_CHECK in varchar2)
5278 AS
5279 ACCOUNT_VALUE varchar2(30);
5280 COMPUTED_SUM varchar2(20);
5281 BRANCH_NUMBER varchar2(3);
5282 numeric_result varchar2(40);
5283
5284 /**************************/
5285 /* SUB-PROCEDURES SECTION */
5286 /**************************/
5287
5288 procedure fail_check is
5289 begin
5290 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5291 fnd_msg_pub.add;
5292 IF l_DEBUG in ('Y', 'C') THEN
5293 cep_standard.debug('CE_VALIDATE_ACCOUNT_FI: ' || 'CE_INVALID_ACCOUNT_NUM');
5294 END IF;
5295 end fail_check;
5296
5297 procedure pass_check is
5298 begin
5299 IF l_DEBUG in ('Y', 'C') THEN
5300 null;
5301 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_FI: ' || 'pass_check');
5302 END IF;
5303
5304 end pass_check;
5305
5306
5307 /****************/
5308 /* MAIN SECTION */
5309 /****************/
5310 BEGIN
5311 IF l_DEBUG in ('Y', 'C') THEN
5312 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_fi');
5313 END IF;
5314
5315 ACCOUNT_VALUE := upper(Xi_ACCOUNT_NUMBER );
5316
5317 select
5318 decode(substr(Xi_ACCOUNT_NUMBER ,1,1), '1','Y','2','Y','3','Y','4','N',
5319 '5','N','6','Y','7','N','8','Y','9','Y','ERR')
5320 into BRANCH_NUMBER
5321 from dual;
5322
5323 if BRANCH_NUMBER='Y' then
5324 ACCOUNT_VALUE:= substr(Xi_ACCOUNT_NUMBER ,1,6)||
5325 lpad(substr(Xi_ACCOUNT_NUMBER ,8),8,'0');
5326 elsif BRANCH_NUMBER='N' then
5327 ACCOUNT_VALUE:= substr(Xi_ACCOUNT_NUMBER ,1,6)||
5328 substr(Xi_ACCOUNT_NUMBER ,8,1)||
5329 lpad(substr(Xi_ACCOUNT_NUMBER ,9),7,'0');
5330 else
5331 fail_check;
5332 return;
5333 end if;
5334 ACCOUNT_VALUE:=replace(ACCOUNT_VALUE,'-','0');
5335 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5336 IF numeric_result = '0'
5337 THEN
5338 null;
5339 ELSE
5340 fail_check;
5341 return;
5342 END IF;
5343 IF substr(ACCOUNT_VALUE,1,2)='88' then
5344 COMPUTED_SUM:=to_char(
5345 1* to_number(substr(ACCOUNT_VALUE,8,1))+
5346 3* to_number(substr(ACCOUNT_VALUE,9,1))+
5347 7* to_number(substr(ACCOUNT_VALUE,10,1)) +
5348 1* to_number(substr(ACCOUNT_VALUE,11,1)) +
5349 3* to_number(substr(ACCOUNT_VALUE,12,1)) +
5350 7* to_number(substr(ACCOUNT_VALUE,13,1)));
5351
5352 if substr(ACCOUNT_VALUE,14,1)=
5353 to_char(10-to_number(substr(COMPUTED_SUM,length(COMPUTED_SUM),1))) or
5354 (substr(ACCOUNT_VALUE,14,1)= '0' and
5355 substr(COMPUTED_SUM,length(COMPUTED_SUM),1)='0')
5356 then
5357 null;
5358 else
5359 fail_check;
5360 return;
5361 end if;
5362 ELSE
5363 COMPUTED_SUM:= mod(2*substr(ACCOUNT_VALUE,1,1),10) +
5364 trunc(2*substr(ACCOUNT_VALUE,1,1)/10) +
5365 mod(1*substr(ACCOUNT_VALUE,2,1),10) +
5366 mod(2*substr(ACCOUNT_VALUE,3,1),10) +
5367 trunc(2*substr(ACCOUNT_VALUE,3,1)/10) +
5368 mod(1*substr(ACCOUNT_VALUE,4,1),10) +
5369 mod(2*substr(ACCOUNT_VALUE,5,1),10) +
5370 trunc(2*substr(ACCOUNT_VALUE,5,1)/10) +
5371 mod(1*substr(ACCOUNT_VALUE,6,1),10) +
5372 mod(2*substr(ACCOUNT_VALUE,7,1),10) +
5373 trunc(2*substr(ACCOUNT_VALUE,7,1)/10) +
5374 mod(1*substr(ACCOUNT_VALUE,8,1),10) +
5375 mod(2*substr(ACCOUNT_VALUE,9,1),10) +
5376 trunc(2*substr(ACCOUNT_VALUE,9,1)/10) +
5377 mod(1*substr(ACCOUNT_VALUE,10,1),10) +
5378 mod(2*substr(ACCOUNT_VALUE,11,1),10) +
5379 trunc(2*substr(ACCOUNT_VALUE,11,1)/10) +
5380 mod(1*substr(ACCOUNT_VALUE,12,1),10) +
5381 mod(2*substr(ACCOUNT_VALUE,13,1),10) +
5382 trunc(2*substr(ACCOUNT_VALUE,13,1)/10);
5383
5384 IF trunc((COMPUTED_SUM+9)/10)*10 -COMPUTED_SUM<>
5385 to_number(substr(ACCOUNT_VALUE,14,1)) THEN
5386 fail_check;
5387 return;
5388 END IF;
5389 END IF;
5390 pass_check;
5391 /*
5392 IF l_DEBUG in ('Y', 'C') THEN
5393 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_fi');
5394 END IF;
5395 */
5396 END CE_VALIDATE_ACCOUNT_FI;
5397
5398 /********* END CE_VALIDATE_ACCOUNT_FI *************************/
5399
5400 -- new account validations 5/14/02
5401
5402 procedure CE_VALIDATE_ACCOUNT_DE(Xi_ACCOUNT_NUMBER in varchar2,
5403 Xo_VALUE_OUT OUT NOCOPY varchar2)
5404 AS
5405 account_value varchar2(60);
5406 numeric_result varchar2(40);
5407
5408
5409 /*******************************/
5410 /* SUB FUNCTIONS and PROCEDURES*/
5411 /*******************************/
5412
5413 procedure fail_check is
5414 begin
5415 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5416 fnd_msg_pub.add;
5417 IF l_DEBUG in ('Y', 'C') THEN
5418 cep_standard.debug('CE_VALIDATE_ACCOUNT_DE: ' || 'CE_INVALID_ACCOUNT_NUM');
5419 END IF;
5420 end fail_check;
5421
5422 procedure pass_check is
5423 begin
5424
5425 IF l_DEBUG in ('Y', 'C') THEN
5426 null;
5427 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_DE: ' || 'pass_check');
5428 END IF;
5429 end pass_check;
5430
5431 /****************/
5432 /* MAIN SECTION */
5433 /****************/
5434 BEGIN
5435 IF l_DEBUG in ('Y', 'C') THEN
5436 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_DE');
5437 END IF;
5438
5439 account_value := upper(Xi_ACCOUNT_NUMBER );
5440 Xo_VALUE_OUT := account_value;
5441
5442 account_value := replace(account_value,' ','');
5443
5444 IF ((length(account_value) > 10) or (length(account_value) < 1)) THEN
5445 fail_check;
5446
5447 ELSE
5448 numeric_result := ce_check_numeric(account_value,1,length(account_value));
5449
5450 IF numeric_result <> '0' then
5451 IF l_DEBUG in ('Y', 'C') THEN
5452 cep_standard.debug( 'failed numeric');
5453 END IF;
5454 fail_check;
5455 ELSE
5456 Xo_VALUE_OUT := lpad(account_value,10,0);
5457 END IF; /* end of numeric check */
5458 IF l_DEBUG in ('Y', 'C') THEN
5459 cep_standard.debug('Xo_VALUE_OUT: ' || Xo_VALUE_OUT);
5460 END IF;
5461 END IF; /* end of length check */
5462
5463 /*
5464 IF l_DEBUG in ('Y', 'C') THEN
5465 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_DE');
5466 END IF;
5467 */
5468 END CE_VALIDATE_ACCOUNT_DE;
5469
5470 /* -------------------------------------------------------------------- */
5471
5472
5473 procedure CE_VALIDATE_ACCOUNT_GR(Xi_ACCOUNT_NUMBER in varchar2,
5474 Xo_VALUE_OUT OUT NOCOPY varchar2)
5475 AS
5476 account_value varchar2(30);
5477 numeric_result varchar2(40);
5478
5479 /*******************************/
5480 /* SUB FUNCTIONS and PROCEDURES*/
5481 /*******************************/
5482
5483 procedure fail_check is
5484 begin
5485 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5486 fnd_msg_pub.add;
5487 IF l_DEBUG in ('Y', 'C') THEN
5488 cep_standard.debug('CE_VALIDATE_ACCOUNT_GR: ' || 'CE_INVALID_ACCOUNT_NUM');
5489 END IF;
5490 end fail_check;
5491
5492 procedure pass_check is
5493 begin
5494
5495 IF l_DEBUG in ('Y', 'C') THEN
5496 null;
5497 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_GR: ' || 'pass_check');
5498 END IF;
5499 end pass_check;
5500
5501 /****************/
5502 /* MAIN SECTION */
5503 /****************/
5504 BEGIN
5505 IF l_DEBUG in ('Y', 'C') THEN
5506 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_GR');
5507 END IF;
5508
5509 account_value := upper(replace(Xi_ACCOUNT_NUMBER,' ',''));
5510 account_value := replace(account_value,'-','');
5511
5512 IF l_DEBUG in ('Y', 'C') THEN
5513 cep_standard.debug(' account_value: ' ||account_value);
5514 END IF;
5515
5516 IF length(account_value) < 8 THEN
5517 fail_check;
5518
5519 ELSIF length(account_value) < 13 THEN
5520
5521 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5522
5523 IF numeric_result = '0' then
5524 IF length(account_value) <> 12 then
5525 Xo_VALUE_OUT := lpad(account_value,12,0);
5526 END IF;
5527
5528 ELSE
5529 fail_check;
5530 END IF; /* end number check */
5531 ELSE
5532 fail_check;
5533
5534 END IF; /* end of length check */
5535 /*
5536 IF l_DEBUG in ('Y', 'C') THEN
5537 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_ACCOUNT_GR');
5538 END IF;
5539 */
5540 END CE_VALIDATE_ACCOUNT_GR;
5541
5542 /* -------------------------------------------------------------------- */
5543
5544 procedure CE_VALIDATE_ACCOUNT_IS(Xi_ACCOUNT_NUMBER in varchar2,
5545 Xo_VALUE_OUT OUT NOCOPY varchar2)
5546 AS
5547
5548 ac_value varchar2(50);
5549 cal_cd number;
5550 cal_cd1 number;
5551
5552 cd_value varchar2(50);
5553 ac_cd_value varchar2(50);
5554
5555
5556 numeric_result varchar2(40);
5557
5558 /*******************************/
5559 /* SUB FUNCTIONS and PROCEDURES*/
5560 /*******************************/
5561
5562 procedure fail_check is
5563 begin
5564 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5565 fnd_msg_pub.add;
5566 IF l_DEBUG in ('Y', 'C') THEN
5567 cep_standard.debug('CE_VALIDATE_ACCOUNT_IS: ' || 'CE_INVALID_ACCOUNT_NUM');
5568 END IF;
5569 end fail_check;
5570
5571 procedure pass_check is
5572 begin
5573
5574 IF l_DEBUG in ('Y', 'C') THEN
5575 null;
5576 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_IS: ' || 'pass_check');
5577 END IF;
5578 end pass_check;
5579
5580 /****************/
5581 /* MAIN SECTION */
5582 /****************/
5583 BEGIN
5584 IF l_DEBUG in ('Y', 'C') THEN
5585 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_is');
5586 END IF;
5587
5588 ac_value := upper(Xi_ACCOUNT_NUMBER );
5589
5590 ac_value := replace(ac_value,' ','');
5591 ac_value := replace(ac_value,'-','');
5592
5593
5594 IF length(ac_value) < 19 THEN
5595 numeric_result := ce_check_numeric(AC_VALUE,1,length(AC_VALUE));
5596 IF numeric_result = '0' then
5597
5598 IF length(ac_value) <> 18 THEN
5599 Xo_VALUE_OUT := lpad(ac_value,18,0);
5600 ac_value := lpad(ac_value,18,0);
5601 END IF;
5602
5603 IF l_DEBUG in ('Y', 'C') THEN
5604 cep_standard.debug( 'ac_value : ' || ac_value);
5605 END IF;
5606
5607 cal_cd1 := mod(( (to_number(substr(ac_value,9,1)) * 3)
5608 +(to_number(substr(ac_value,10,1)) * 2)
5609 +(to_number(substr(ac_value,11,1)) * 7)
5610 +(to_number(substr(ac_value,12,1)) * 6)
5611 +(to_number(substr(ac_value,13,1)) * 5)
5612 +(to_number(substr(ac_value,14,1)) * 4)
5613 +(to_number(substr(ac_value,15,1)) * 3)
5614 +(to_number(substr(ac_value,16,1)) * 2)),11);
5615
5616 IF l_DEBUG in ('Y', 'C') THEN
5617 cep_standard.debug('cal_cd1 : ' || cal_cd1);
5618 END IF;
5619
5620 IF cal_cd1 = 0 then
5621 cal_cd := 0;
5622 else
5623 cal_cd := (11 - cal_cd1);
5624 END if;
5625
5626 -- the check digit is the penultimate digit of (a3).
5627 ac_cd_value := substr(ac_value,17,1);
5628
5629 IF l_DEBUG in ('Y', 'C') THEN
5630 cep_standard.debug('ac_cd_value: ' || ac_cd_value|| ' cal_cd: ' || cal_cd);
5631 END IF;
5632
5633 IF ac_cd_value = cal_cd then
5634 pass_check;
5635 ELSE
5636 IF l_DEBUG in ('Y', 'C') THEN
5637 cep_standard.debug('failed check - cd ');
5638 END IF;
5639 fail_check;
5640 END IF;
5641
5642 ELSE
5643 IF l_DEBUG in ('Y', 'C') THEN
5644 cep_standard.debug('failed check numeric ');
5645 END IF;
5646 fail_check;
5647 END IF;
5648 ELSE
5649 IF l_DEBUG in ('Y', 'C') THEN
5650 cep_standard.debug('failed check length ');
5651 END IF;
5652 fail_check;
5653 END IF; /* end of length check */
5654 /*
5655 IF l_DEBUG in ('Y', 'C') THEN
5656 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_is');
5657 END IF;
5658 */
5659 END CE_VALIDATE_ACCOUNT_IS;
5660
5661 /* -------------------------------------------------------------------- */
5662
5663 procedure CE_VALIDATE_ACCOUNT_IE(Xi_ACCOUNT_NUMBER in varchar2)
5664 AS
5665 account_value varchar2(30);
5666 numeric_result varchar2(40);
5667
5668 /*******************************/
5669 /* SUB FUNCTIONS and PROCEDURES*/
5670 /*******************************/
5671
5672 procedure fail_check is
5673 begin
5674 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5675 fnd_msg_pub.add;
5676 IF l_DEBUG in ('Y', 'C') THEN
5677 cep_standard.debug('CE_VALIDATE_ACCOUNT_IE: ' || 'CE_INVALID_ACCOUNT_NUM');
5678 END IF;
5679 end fail_check;
5680
5681 procedure pass_check is
5682 begin
5683 IF l_DEBUG in ('Y', 'C') THEN
5684 null;
5685 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_IE: ' || 'pass_check');
5686 END IF;
5687
5688 end pass_check;
5689
5690 /****************/
5691 /* MAIN SECTION */
5692 /****************/
5693 BEGIN
5694 IF l_DEBUG in ('Y', 'C') THEN
5695 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_ie');
5696 END IF;
5697
5698 account_value := upper(Xi_ACCOUNT_NUMBER );
5699
5700 account_value := replace(account_value,' ','');
5701 account_value := replace(account_value,'-','');
5702
5703
5704 IF length(account_value) = 8 THEN
5705 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5706
5707 IF numeric_result = '0' then
5708 pass_check;
5709
5710 ELSE
5711 fail_check;
5712 END IF;
5713 ELSE
5714 fail_check;
5715 END IF; /* end of length check */
5716 /*
5717 IF l_DEBUG in ('Y', 'C') THEN
5718 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_ie');
5719 END IF;
5720 */
5721 END CE_VALIDATE_ACCOUNT_IE;
5722
5723 /* -------------------------------------------------------------------- */
5724
5725 procedure CE_VALIDATE_ACCOUNT_IT(Xi_ACCOUNT_NUMBER in varchar2,
5726 Xo_VALUE_OUT OUT NOCOPY varchar2)
5727 AS
5728 account_value varchar2(50);
5729
5730
5731 /*******************************/
5732 /* SUB FUNCTIONS and PROCEDURES*/
5733 /*******************************/
5734
5735 procedure fail_check is
5736 begin
5737 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5738 fnd_msg_pub.add;
5739 IF l_DEBUG in ('Y', 'C') THEN
5740 cep_standard.debug('CE_VALIDATE_ACCOUNT_IT: ' || 'CE_INVALID_ACCOUNT_NUM');
5741 END IF;
5742 end fail_check;
5743
5744 procedure pass_check is
5745 begin
5746
5747 IF l_DEBUG in ('Y', 'C') THEN
5748 null;
5749 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_IT: ' || 'pass_check');
5750 END IF;
5751 end pass_check;
5752
5753
5754
5755 /****************/
5756 /* MAIN SECTION */
5757 /****************/
5758 BEGIN
5759 IF l_DEBUG in ('Y', 'C') THEN
5760 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_it');
5761 END IF;
5762
5763 account_value := upper(replace(Xi_ACCOUNT_NUMBER,' ',''));
5764 account_value := replace(account_value,'-','');
5765 IF l_DEBUG in ('Y', 'C') THEN
5766 cep_standard.debug('length(account_value): '||length(account_value) );
5767 END IF;
5768
5769 IF length(account_value) < 13 THEN
5770 Xo_VALUE_OUT := lpad(account_value,12,0);
5771 pass_check;
5772 IF l_DEBUG in ('Y', 'C') THEN
5773 cep_standard.debug('Xo_VALUE_OUT: '||Xo_VALUE_OUT );
5774 END IF;
5775 ELSE
5776 fail_check;
5777
5778 END IF; /* end of length check */
5779 /*
5780 IF l_DEBUG in ('Y', 'C') THEN
5781 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_it');
5782 END IF;
5783 */
5784 END CE_VALIDATE_ACCOUNT_IT;
5785
5786 /* -------------------------------------------------------------------- */
5787
5788 procedure CE_VALIDATE_ACCOUNT_LU(Xi_ACCOUNT_NUMBER in varchar2)
5789 AS
5790 account_value varchar2(30);
5791 numeric_result varchar2(40);
5792
5793 /*******************************/
5794 /* SUB FUNCTIONS and PROCEDURES*/
5795 /*******************************/
5796
5797 procedure fail_check is
5798 begin
5799 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5800 fnd_msg_pub.add;
5801 IF l_DEBUG in ('Y', 'C') THEN
5802 cep_standard.debug('CE_VALIDATE_ACCOUNT_LU: ' || 'CE_INVALID_ACCOUNT_NUM');
5803 END IF;
5804 end fail_check;
5805
5806 procedure pass_check is
5807 begin
5808 IF l_DEBUG in ('Y', 'C') THEN
5809 null;
5810 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_LU: ' || 'pass_check');
5811 END IF;
5812
5813 end pass_check;
5814
5815 /****************/
5816 /* MAIN SECTION */
5817 /****************/
5818 BEGIN
5819 IF l_DEBUG in ('Y', 'C') THEN
5820 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_lu');
5821 END IF;
5822
5823 account_value := upper(Xi_ACCOUNT_NUMBER );
5824
5825 account_value := replace(account_value,' ','');
5826 account_value := replace(account_value,'-','');
5827
5828
5829 IF length(account_value) < 13 THEN
5830 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5831
5832 IF numeric_result = '0' then
5833 pass_check;
5834
5835 ELSE
5836 fail_check;
5837 END IF;
5838 ELSE
5839 fail_check;
5840 END IF; /* end of length check */
5841 /*
5842 IF l_DEBUG in ('Y', 'C') THEN
5843 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_lu');
5844 END IF;
5845 */
5846 END CE_VALIDATE_ACCOUNT_LU;
5847
5848 /* -------------------------------------------------------------------- */
5849
5850 procedure CE_VALIDATE_ACCOUNT_PL(Xi_ACCOUNT_NUMBER in varchar2)
5851 AS
5852 account_value varchar2(30);
5853 numeric_result varchar2(40);
5854
5855 /*******************************/
5856 /* SUB FUNCTIONS and PROCEDURES*/
5857 /*******************************/
5858
5859 procedure fail_check is
5860 begin
5861 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5862 fnd_msg_pub.add;
5863 IF l_DEBUG in ('Y', 'C') THEN
5864 cep_standard.debug('CE_VALIDATE_ACCOUNT_PL: ' || 'CE_INVALID_ACCOUNT_NUM');
5865 END IF;
5866 end fail_check;
5867
5868 procedure pass_check is
5869 begin
5870 IF l_DEBUG in ('Y', 'C') THEN
5871 null;
5872 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_PL: ' || 'pass_check');
5873 END IF;
5874
5875 end pass_check;
5876
5877 /****************/
5878 /* MAIN SECTION */
5879 /****************/
5880 BEGIN
5881 IF l_DEBUG in ('Y', 'C') THEN
5882 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_pl');
5883 END IF;
5884
5885 account_value := upper(Xi_ACCOUNT_NUMBER );
5886
5887 account_value := replace(account_value,' ','');
5888 account_value := replace(account_value,'-','');
5889
5890
5891 IF length(account_value) < 25 THEN
5892
5893 pass_check;
5894 ELSE
5895 fail_check;
5896 END IF; /* end of length check */
5897 /*
5898 IF l_DEBUG in ('Y', 'C') THEN
5899 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_pl');
5900 END IF;
5901 */
5902 END CE_VALIDATE_ACCOUNT_PL;
5903
5904 /* -------------------------------------------------------------------- */
5905
5906 procedure CE_VALIDATE_ACCOUNT_SE(Xi_ACCOUNT_NUMBER in varchar2)
5907 AS
5908 account_value varchar2(30);
5909 numeric_result varchar2(40);
5910
5911 /*******************************/
5912 /* SUB FUNCTIONS and PROCEDURES*/
5913 /*******************************/
5914
5915 procedure fail_check is
5916 begin
5917 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5918 fnd_msg_pub.add;
5919 IF l_DEBUG in ('Y', 'C') THEN
5920 cep_standard.debug('CE_VALIDATE_ACCOUNT_SE: ' || 'CE_INVALID_ACCOUNT_NUM');
5921 END IF;
5922 end fail_check;
5923
5924 procedure pass_check is
5925 begin
5926 IF l_DEBUG in ('Y', 'C') THEN
5927 null;
5928 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_SE: ' || 'pass_check');
5929 END IF;
5930
5931 end pass_check;
5932
5933 /****************/
5934 /* MAIN SECTION */
5935 /****************/
5936 BEGIN
5937 IF l_DEBUG in ('Y', 'C') THEN
5938 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_se');
5939 END IF;
5940
5941 account_value := upper(replace(Xi_ACCOUNT_NUMBER ,' ',''));
5942 account_value := replace(account_value,'-','');
5943
5944 IF length(account_value) < 12 THEN
5945 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
5946
5947 IF numeric_result = '0' then
5948 pass_check;
5949
5950 ELSE
5951 fail_check;
5952 END IF;
5953 ELSE
5954 fail_check;
5955 END IF; /* end of length check */
5956 /*
5957 IF l_DEBUG in ('Y', 'C') THEN
5958 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_se');
5959 END IF;
5960 */
5961 END CE_VALIDATE_ACCOUNT_SE;
5962
5963 /* -------------------------------------------------------------------- */
5964
5965 procedure CE_VALIDATE_ACCOUNT_CH(Xi_ACCOUNT_NUMBER in varchar2,
5966 Xi_ACCOUNT_TYPE in varchar2,
5967 Xi_VALIDATION_TYPE in varchar2 )
5968 AS
5969 account_value varchar2(30);
5970
5971
5972 /*******************************/
5973 /* SUB FUNCTIONS and PROCEDURES*/
5974 /*******************************/
5975
5976 procedure fail_account_type is
5977 begin
5978 fnd_message.set_name ('CE', 'CE_ENTER_ACCOUNT_TYPE');
5979 fnd_msg_pub.add;
5980 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
5981
5982 end fail_account_type;
5983
5984 procedure fail_check is
5985 begin
5986 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
5987 fnd_msg_pub.add;
5988 IF l_DEBUG in ('Y', 'C') THEN
5989 cep_standard.debug('CE_VALIDATE_ACCOUNT_CH: ' || 'CE_INVALID_ACCOUNT_NUM');
5990 END IF;
5991 end fail_check;
5992
5993 procedure pass_check is
5994 begin
5995 IF l_DEBUG in ('Y', 'C') THEN
5996 null;
5997 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_CH: ' || 'pass_check');
5998 END IF;
5999
6000 end pass_check;
6001
6002
6003
6004 /****************/
6005 /* MAIN SECTION */
6006 /****************/
6007 BEGIN
6008 IF l_DEBUG in ('Y', 'C') THEN
6009 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_ch');
6010 END IF;
6011
6012 account_value := upper(replace(Xi_ACCOUNT_NUMBER,' ',''));
6013
6014 account_value := replace(account_value,'-','');
6015
6016 IF length(account_value) < 17 THEN
6017 pass_check;
6018 ELSE
6019 fail_check;
6020
6021 END IF; /* end of length check */
6022
6023 IF (Xi_VALIDATION_TYPE = 'ALL') THEN
6024 IF (Xi_ACCOUNT_TYPE is null) THEN
6025 fail_account_type;
6026 END if;
6027 END if;
6028
6029 /*
6030 IF l_DEBUG in ('Y', 'C') THEN
6031 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_ch');
6032 END IF;
6033 */
6034 END CE_VALIDATE_ACCOUNT_CH;
6035
6036 /* -------------------------------------------------------------------- */
6037
6038 procedure CE_VALIDATE_ACCOUNT_GB(Xi_ACCOUNT_NUMBER in varchar2,
6039 Xo_VALUE_OUT OUT NOCOPY varchar2)
6040 AS
6041 account_value varchar2(30);
6042 numeric_result varchar2(40);
6043
6044 /*******************************/
6045 /* SUB FUNCTIONS and PROCEDURES*/
6046 /*******************************/
6047
6048 procedure fail_check is
6049 begin
6050 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
6051 fnd_msg_pub.add;
6052 IF l_DEBUG in ('Y', 'C') THEN
6053 cep_standard.debug('CE_VALIDATE_ACCOUNT_GB: ' || 'CE_INVALID_ACCOUNT_NUM');
6054 END IF;
6055 end fail_check;
6056
6057 procedure pass_check is
6058 begin
6059
6060 IF l_DEBUG in ('Y', 'C') THEN
6061 null;
6062 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_GB: ' || 'pass_check');
6063 END IF;
6064 end pass_check;
6065
6066 /****************/
6067 /* MAIN SECTION */
6068 /****************/
6069 BEGIN
6070 IF l_DEBUG in ('Y', 'C') THEN
6071 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_gb');
6072 END IF;
6073
6074 Xo_VALUE_OUT := account_value;
6075
6076 account_value := upper(replace(Xi_ACCOUNT_NUMBER,' ',''));
6077 account_value := replace(account_value,'-','');
6078
6079 IF length(account_value) < 9 THEN
6080 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
6081
6082 IF numeric_result = '0' then
6083 IF (length(account_value) <> 8) then
6084 Xo_VALUE_OUT := lpad(account_value,8,0);
6085 END IF;
6086 ELSE
6087 fail_check;
6088 END IF;
6089
6090 ELSE
6091 fail_check;
6092
6093 END IF; /* end of length check */
6094 /*
6095 IF l_DEBUG in ('Y', 'C') THEN
6096 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_account_gb');
6097 END IF;
6098 */
6099 END CE_VALIDATE_ACCOUNT_GB;
6100
6101 /* -------------------------------------------------------------------- */
6102
6103 procedure CE_VALIDATE_ACCOUNT_BR(Xi_ACCOUNT_NUMBER in varchar2,
6104 Xi_SECONDARY_ACCOUNT_REFERENCE in varchar2)
6105 AS
6106 account_ref varchar2(30);
6107 numeric_result varchar2(40);
6108
6109 /*******************************/
6110 /* SUB FUNCTIONS and PROCEDURES*/
6111 /*******************************/
6112
6113 procedure fail_check is
6114 begin
6115 fnd_message.set_name ('CE', 'CE_INVALID_COMPANY_CODE');
6116 fnd_msg_pub.add;
6117 IF l_DEBUG in ('Y', 'C') THEN
6118 cep_standard.debug('CE_VALIDATE_ACCOUNT_BR: ' || 'CE_INVALID_COMPANY_CODE');
6119 END IF;
6120 end fail_check;
6121
6122 procedure pass_check is
6123 begin
6124
6125 IF l_DEBUG in ('Y', 'C') THEN
6126 null;
6127 -- cep_standard.debug('CE_VALIDATE_ACCOUNT_BR: ' || 'pass_check');
6128 END IF;
6129 end pass_check;
6130
6131 /****************/
6132 /* MAIN SECTION */
6133 /****************/
6134 BEGIN
6135
6136 IF l_DEBUG in ('Y', 'C') THEN
6137 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_br');
6138 END IF;
6139
6140 account_ref := upper(replace(Xi_SECONDARY_ACCOUNT_REFERENCE,' ',''));
6141
6142 IF l_DEBUG in ('Y', 'C') THEN
6143 cep_standard.debug('account_ref: '|| account_ref);
6144 END IF;
6145
6146 IF (account_ref) is not null then
6147
6148 IF length(account_ref) < 16 THEN
6149 numeric_result := ce_check_numeric(ACCOUNT_REF,1,length(ACCOUNT_REF));
6150
6151 IF numeric_result = '0' then
6152 pass_check;
6153 ELSE
6154 fail_check;
6155 END IF;
6156
6157 ELSE
6158 fail_check;
6159
6160 END IF; /* end of length check */
6161 ELSE
6162 pass_check;
6163
6164 END IF; /* end of not null check */
6165
6166 IF l_DEBUG in ('Y', 'C') THEN
6167 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_account_br');
6168 END IF;
6169
6170 END CE_VALIDATE_ACCOUNT_BR;
6171
6172 --added 10/19/04
6173
6174 /* -------------------------------------------------------------------- */
6175
6176 procedure CE_VALIDATE_ACCOUNT_AU(Xi_ACCOUNT_NUMBER in varchar2)
6177 AS
6178 account_value varchar2(30);
6179 numeric_result varchar2(40);
6180
6181 /*******************************/
6182 /* SUB FUNCTIONS and PROCEDURES*/
6183 /*******************************/
6184
6185 procedure fail_check is
6186 begin
6187 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
6188 fnd_msg_pub.add;
6189 IF l_DEBUG in ('Y', 'C') THEN
6190 cep_standard.debug('CE_VALIDATE_ACCOUNT_AU: ' || 'CE_INVALID_ACCOUNT_NUM');
6191 END IF;
6192 end fail_check;
6193
6194 procedure pass_check is
6195 begin
6196 IF l_DEBUG in ('Y', 'C') THEN
6197 cep_standard.debug('CE_VALIDATE_ACCOUNT_AU: ' || 'pass_check');
6198 END IF;
6199
6200 end pass_check;
6201
6202 /****************/
6203 /* MAIN SECTION */
6204 /****************/
6205 BEGIN
6206 IF l_DEBUG in ('Y', 'C') THEN
6207 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_au');
6208 END IF;
6209
6210 account_value := upper(Xi_ACCOUNT_NUMBER );
6211
6212 account_value := replace(account_value,' ','');
6213 account_value := replace(account_value,'-','');
6214
6215
6216 IF length(account_value) <= 10 and length(account_value) >= 7 THEN
6217 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
6218
6219 IF numeric_result = '0' then
6220 pass_check;
6221
6222 ELSE
6223 fail_check;
6224 END IF;
6225
6226 ELSE
6227 fail_check;
6228 END IF; /* end of length check */
6229 IF l_DEBUG in ('Y', 'C') THEN
6230 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_account_au');
6231 END IF;
6232
6233 END CE_VALIDATE_ACCOUNT_AU;
6234
6235 /* -------------------------------------------------------------------- */
6236
6237 procedure CE_VALIDATE_ACCOUNT_IL(Xi_ACCOUNT_NUMBER in varchar2)
6238 AS
6239 account_value varchar2(30);
6240 numeric_result varchar2(40);
6241
6242 /*******************************/
6243 /* SUB FUNCTIONS and PROCEDURES*/
6244 /*******************************/
6245
6246 procedure fail_check is
6247 begin
6248 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
6249 fnd_msg_pub.add;
6250 IF l_DEBUG in ('Y', 'C') THEN
6251 cep_standard.debug('CE_VALIDATE_ACCOUNT_IL: ' || 'CE_INVALID_ACCOUNT_NUM');
6252 END IF;
6253 end fail_check;
6254
6255 procedure pass_check is
6256 begin
6257 IF l_DEBUG in ('Y', 'C') THEN
6258 cep_standard.debug('CE_VALIDATE_ACCOUNT_IL: ' || 'pass_check');
6259 END IF;
6260
6261 end pass_check;
6262
6263 /****************/
6264 /* MAIN SECTION */
6265 /****************/
6266 BEGIN
6267 IF l_DEBUG in ('Y', 'C') THEN
6268 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_il');
6269 END IF;
6270
6271 account_value := upper(Xi_ACCOUNT_NUMBER );
6272
6273 account_value := replace(account_value,' ','');
6274 account_value := replace(account_value,'-','');
6275
6276
6277 IF length(account_value) = 9 THEN
6278 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
6279
6280 IF numeric_result = '0' then
6281 pass_check;
6282
6283 ELSE
6284 fail_check;
6285 END IF;
6286
6287 ELSE
6288 fail_check;
6289 END IF; /* end of length check */
6290 IF l_DEBUG in ('Y', 'C') THEN
6291 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_account_il');
6292 END IF;
6293
6294 END CE_VALIDATE_ACCOUNT_IL;
6295 /* -------------------------------------------------------------------- */
6296
6297 procedure CE_VALIDATE_ACCOUNT_NZ(Xi_ACCOUNT_NUMBER in varchar2,
6298 Xi_ACCOUNT_SUFFIX in varchar2,
6299 Xi_VALIDATION_TYPE in varchar2)
6300 AS
6301 account_value varchar2(30);
6302 account_suffix varchar2(30);
6303 numeric_result varchar2(40);
6304
6305 /*******************************/
6306 /* SUB FUNCTIONS and PROCEDURES*/
6307 /*******************************/
6308
6309 procedure fail_check is
6310 begin
6311 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_NUM');
6312 fnd_msg_pub.add;
6313 IF l_DEBUG in ('Y', 'C') THEN
6314 cep_standard.debug('CE_VALIDATE_ACCOUNT_NZ: ' || 'CE_INVALID_ACCOUNT_NUM');
6315 END IF;
6316 end fail_check;
6317
6318 procedure pass_check is
6319 begin
6320 IF l_DEBUG in ('Y', 'C') THEN
6321 cep_standard.debug('CE_VALIDATE_ACCOUNT_NZ: ' || 'pass_check');
6322 END IF;
6323
6324 end pass_check;
6325
6326 procedure fail_account_suffix_check is
6327 begin
6328 fnd_message.set_name ('CE', 'CE_INVALID_ACCOUNT_SUFFIX');
6329 fnd_msg_pub.add;
6330 IF l_DEBUG in ('Y', 'C') THEN
6331 cep_standard.debug('CE_VALIDATE_ACCOUNT_NZ: ' || 'CE_INVALID_ACCOUNT_SUFFIX');
6332 END IF;
6333 end fail_account_suffix_check;
6334
6335 procedure pass_account_suffix_check is
6336 begin
6337 IF l_DEBUG in ('Y', 'C') THEN
6338 cep_standard.debug('CE_VALIDATE_ACCOUNT_NZ: ' || 'pass_check');
6339 END IF;
6340
6341 end pass_account_suffix_check;
6342 /****************/
6343 /* MAIN SECTION */
6344 /****************/
6345 BEGIN
6346 IF l_DEBUG in ('Y', 'C') THEN
6347 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_nz');
6348 END IF;
6349 -- check account number
6350 account_value := upper(Xi_ACCOUNT_NUMBER );
6351
6352 account_value := replace(account_value,' ','');
6353 account_value := replace(account_value,'-','');
6354
6355
6356 IF length(account_value) <= 8 and length(account_value) >= 6 THEN
6357 numeric_result := ce_check_numeric(ACCOUNT_VALUE,1,length(ACCOUNT_VALUE));
6358
6359 IF numeric_result = '0' then
6360 pass_check;
6361
6362 ELSE
6363 fail_check;
6364 END IF;
6365
6366 ELSE
6367 fail_check;
6368 END IF; /* end of length check */
6369
6370 -- check account_suffix
6371 IF (Xi_VALIDATION_TYPE = 'ALL') THEN
6372 account_suffix := upper(Xi_ACCOUNT_SUFFIX );
6373
6374 account_suffix := replace(account_suffix,' ','');
6375 account_suffix := replace(account_suffix,'-','');
6376
6377
6378 IF length(account_suffix) = 3 THEN
6379 numeric_result := ce_check_numeric(ACCOUNT_SUFFIX,1,length(ACCOUNT_SUFFIX));
6380
6381 IF numeric_result = '0' then
6382 pass_account_suffix_check;
6383
6384 ELSE
6385 fail_account_suffix_check;
6386 END IF;
6387
6388 ELSE
6389 fail_account_suffix_check;
6390 END IF; /* end of length check */
6391 END IF;
6392
6393
6394 IF l_DEBUG in ('Y', 'C') THEN
6395 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_account_nz');
6396 END IF;
6397
6398 END CE_VALIDATE_ACCOUNT_NZ;
6399 /* -------------------------------------------------------------------- */
6400
6401 procedure CE_VALIDATE_ACCOUNT_JP(Xi_ACCOUNT_NUMBER in varchar2,
6402 Xi_ACCOUNT_TYPE in varchar2 ,
6403 Xi_VALIDATION_TYPE in varchar2)
6404 AS
6405 account_value varchar2(30);
6406 numeric_result varchar2(40);
6407
6408 /*******************************/
6409 /* SUB FUNCTIONS and PROCEDURES*/
6410 /*******************************/
6411
6412 procedure fail_mandatory is
6413 begin
6414 fnd_message.set_name ('CE', 'CE_ENTER_ACCOUNT_NUM');
6415 fnd_msg_pub.add;
6416 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
6417
6418 end fail_mandatory;
6419
6420 procedure fail_account_type is
6421 begin
6422 fnd_message.set_name ('CE', 'CE_ENTER_ACCOUNT_TYPE');
6423 fnd_msg_pub.add;
6424 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
6425
6426 end fail_account_type;
6427
6428 /****************/
6429 /* MAIN SECTION */
6430 /****************/
6431 BEGIN
6432 IF l_DEBUG in ('Y', 'C') THEN
6433 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_account_jp');
6434 END IF;
6435
6436 IF (Xi_ACCOUNT_NUMBER is null) THEN
6437 fail_mandatory;
6438 END if;
6439
6440 IF (Xi_VALIDATION_TYPE = 'ALL') THEN
6441 IF (Xi_ACCOUNT_TYPE is null) THEN
6442 fail_account_type;
6443 END if;
6444 END IF;
6445
6446 IF l_DEBUG in ('Y', 'C') THEN
6447 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_account_il');
6448 END IF;
6449
6450 END CE_VALIDATE_ACCOUNT_JP;
6451
6452 /* --------------------------------------------------------------------
6453 | PRIVATE PROCEDURE |
6454 | CE_VALIDATE_BANK_* |
6455 | |
6456 | CALLED BY |
6457 | CE_VALIDATE_BANK |
6458 | |
6459 | DESCRIPTION |
6460 | BANK PROCEDURES, Validate 1 or more of the following: |
6461 | 1. Bank number length |
6462 | 2. Bank number datatype (numeric, alphanumeric, or alphabet |
6463 | 3. Bank number Algorithm |
6464 | |
6465 | RETURN |
6466 | Xo_VALUE_OUT - Bank Number is return with leading 0 |
6467 | (Not for all countries) |
6468 --------------------------------------------------------------------- */
6469
6470 procedure CE_VALIDATE_BANK_ES (Xi_BANK_NUMBER in varchar2,
6471 Xi_PASS_MAND_CHECK in varchar2,
6472 Xo_VALUE_OUT OUT NOCOPY varchar2)
6473 AS
6474 BANK_VALUE varchar2(30);
6475
6476 numeric_result varchar2(40);
6477
6478 /**************************/
6479 /* SUB-PROCEDURES SECTION */
6480 /**************************/
6481
6482 /*Bug Fix:880887*/
6483 /*Changed the Xo_RET_VAR='F' to 'W' ,in 'fail_mandatory' and 'fail_check'*/
6484 /*so that a warning message is displayed*/
6485 /*for spain instead of Error message*/
6486 procedure fail_mandatory is
6487 begin
6488 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
6489 fnd_msg_pub.add;
6490 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
6491
6492 end fail_mandatory;
6493
6494 procedure fail_check is
6495 begin
6496 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6497 fnd_msg_pub.add;
6498 fnd_msg_pub.add_detail(fnd_msg_pub.g_warning_msg);
6499 IF l_DEBUG in ('Y', 'C') THEN
6500 cep_standard.debug('CE_VALIDATE_BANK_ES: ' || 'CE_INVALID_BANK_NUM');
6501 END IF;
6502
6503 end fail_check;
6504
6505 procedure pass_check is
6506 begin
6507
6508 IF l_DEBUG in ('Y', 'C') THEN
6509 null;
6510 -- cep_standard.debug('CE_VALIDATE_BANK_ES: ' || 'pass_check');
6511 END IF;
6512 end pass_check;
6513
6514 /****************/
6515 /* MAIN SECTION */
6516 /****************/
6517 BEGIN
6518 IF l_DEBUG in ('Y', 'C') THEN
6519 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_es');
6520 END IF;
6521
6522 BANK_VALUE := upper(Xi_BANK_NUMBER );
6523 Xo_VALUE_OUT := BANK_VALUE;
6524
6525 IF Xi_PASS_MAND_CHECK = 'F'
6526 then
6527 fail_mandatory;
6528
6529 ELSIF Xi_PASS_MAND_CHECK = 'P'
6530 then
6531
6532 BANK_VALUE := replace(BANK_VALUE,' ','');
6533
6534 IF length(BANK_VALUE) < 5
6535 then
6536 BANK_VALUE := lpad(BANK_VALUE,4,0);
6537 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
6538
6539 IF numeric_result = '0'
6540 then
6541 /* its numeric so continue */
6542 Xo_VALUE_OUT := BANK_VALUE;
6543 pass_check;
6544 ELSE
6545 fail_check;
6546
6547 END IF; /* end of numeric check */
6548
6549 ELSE
6550 fail_check;
6551
6552 END IF; /* end of length check */
6553 END IF; /* end of mandatory check */
6554 /*
6555 IF l_DEBUG in ('Y', 'C') THEN
6556 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_es');
6557 END IF;
6558 */
6559 END CE_VALIDATE_BANK_ES;
6560
6561
6562 /**************** End of Procedure CE_VALIDATE_BANK_ES ***********/
6563
6564
6565 procedure CE_VALIDATE_BANK_FR(Xi_BANK_NUMBER in varchar2,
6566 Xi_PASS_MAND_CHECK in varchar2,
6567 Xo_VALUE_OUT OUT NOCOPY varchar2)
6568 AS
6569 BANK_VALUE varchar2(30);
6570
6571 numeric_result varchar2(40);
6572
6573 /**************************/
6574 /* SUB-PROCEDURES SECTION */
6575 /**************************/
6576
6577 procedure fail_mandatory is
6578 begin
6579 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
6580 fnd_msg_pub.add;
6581 IF l_DEBUG in ('Y', 'C') THEN
6582 cep_standard.debug('CE_VALIDATE_BANK_FR: ' || 'CE_ENTER_BANK_NUM');
6583 END IF;
6584 end fail_mandatory;
6585
6586 procedure fail_check is
6587 begin
6588 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6589 fnd_msg_pub.add;
6590 IF l_DEBUG in ('Y', 'C') THEN
6591 cep_standard.debug('CE_VALIDATE_BANK_FR: ' || 'CE_INVALID_BANK_NUM');
6592 END IF;
6593
6594 end fail_check;
6595
6596 procedure pass_check is
6597 begin
6598 IF l_DEBUG in ('Y', 'C') THEN
6599 null;
6600 -- cep_standard.debug('CE_VALIDATE_BANK_FR: ' || 'pass_check');
6601 END IF;
6602
6603 end pass_check;
6604
6605 /****************/
6606 /* MAIN SECTION */
6607 /****************/
6608 BEGIN
6609 IF l_DEBUG in ('Y', 'C') THEN
6610 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_fr');
6611 END IF;
6612
6613 BANK_VALUE := upper(Xi_BANK_NUMBER );
6614 Xo_VALUE_OUT := BANK_VALUE;
6615
6616 IF Xi_PASS_MAND_CHECK = 'F'
6617 then
6618 fail_mandatory;
6619
6620 ELSIF Xi_PASS_MAND_CHECK = 'P'
6621 then
6622
6623 BANK_VALUE := replace(BANK_VALUE,' ','');
6624
6625 IF length(BANK_VALUE) < 6
6626 then
6627 BANK_VALUE := lpad(BANK_VALUE,5,0);
6628 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
6629
6630 IF numeric_result = '0'
6631 then
6632 /* its numeric so continue */
6633 Xo_VALUE_OUT := BANK_VALUE;
6634 pass_check;
6635 ELSE
6636 fail_check;
6637
6638 END IF; /* end of numeric check */
6639
6640 ELSE
6641 fail_check;
6642
6643 END IF; /* end of length check */
6644 END IF; /* end of mandatory check */
6645 /*
6646 IF l_DEBUG in ('Y', 'C') THEN
6647 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_fr');
6648 END IF;
6649 */
6650 END CE_VALIDATE_BANK_FR;
6651
6652
6653 /**************** End of Procedure CE_VALIDATE_BANK_FR ***********/
6654
6655 procedure CE_VALIDATE_BANK_PT(Xi_BANK_NUMBER in varchar2,
6656 Xi_PASS_MAND_CHECK in varchar2)
6657 AS
6658 BANK_VALUE varchar2(30);
6659 numeric_result varchar2(40);
6660
6661 /**************************/
6662 /* SUB-PROCEDURES SECTION */
6663 /**************************/
6664
6665 procedure fail_mandatory is
6666 begin
6667 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
6668 fnd_msg_pub.add;
6669 IF l_DEBUG in ('Y', 'C') THEN
6670 cep_standard.debug('CE_VALIDATE_BANK_PT: ' || 'CE_ENTER_BANK_NUM');
6671 END IF;
6672 end fail_mandatory;
6673
6674 procedure fail_check is
6675 begin
6676 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6677 fnd_msg_pub.add;
6678 IF l_DEBUG in ('Y', 'C') THEN
6679 cep_standard.debug('CE_VALIDATE_BANK_PT: ' || 'CE_INVALID_BANK_NUM');
6680 END IF;
6681
6682 end fail_check;
6683
6684 procedure pass_check is
6685 begin
6686 IF l_DEBUG in ('Y', 'C') THEN
6687 null;
6688 -- cep_standard.debug('CE_VALIDATE_BANK_PT: ' || 'pass_check');
6689 END IF;
6690
6691 end pass_check;
6692
6693 /****************/
6694 /* MAIN SECTION */
6695 /****************/
6696 BEGIN
6697 IF l_DEBUG in ('Y', 'C') THEN
6698 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_pt');
6699 END IF;
6700
6701
6702 BANK_VALUE := upper(Xi_BANK_NUMBER );
6703
6704 IF Xi_PASS_MAND_CHECK = 'F'
6705 then
6706 fail_mandatory;
6707
6708 ELSIF Xi_PASS_MAND_CHECK = 'P'
6709 then
6710
6711 BANK_VALUE := replace(BANK_VALUE,' ','');
6712
6713 IF length(BANK_VALUE) = 4
6714 then
6715 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
6716
6717 IF numeric_result = '0'
6718 then /* its numeric so continue */
6719 pass_check;
6720 ELSE
6721 fail_check;
6722
6723 END IF; /* end of numeric check */
6724
6725 ELSE
6726 fail_check;
6727
6728 END IF; /* end of length check */
6729 END IF; /* end of mandatory check */
6730 IF l_DEBUG in ('Y', 'C') THEN
6731 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_pt');
6732 END IF;
6733
6734 END CE_VALIDATE_BANK_PT;
6735
6736 /**************** End of Procedure CE_VALIDATE_BANK_PT ***********/
6737
6738 procedure CE_VALIDATE_BANK_BR(Xi_BANK_NUMBER in varchar2,
6739 Xi_PASS_MAND_CHECK in varchar2,
6740 Xo_VALUE_OUT OUT NOCOPY varchar2)
6741 AS
6742 BANK_VALUE varchar2(30);
6743
6744 numeric_result varchar2(40);
6745
6746 /**************************/
6747 /* SUB-PROCEDURES SECTION */
6748 /**************************/
6749
6750 procedure fail_mandatory is
6751 begin
6752 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
6753 fnd_msg_pub.add;
6754 IF l_DEBUG in ('Y', 'C') THEN
6755 cep_standard.debug('CE_VALIDATE_BANK_BR: ' || 'CE_ENTER_BANK_NUM');
6756 END IF;
6757 end fail_mandatory;
6758
6759 procedure fail_check is
6760 begin
6761 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6762 fnd_msg_pub.add;
6763 IF l_DEBUG in ('Y', 'C') THEN
6764 cep_standard.debug('CE_VALIDATE_BANK_BR: ' || 'CE_INVALID_BANK_NUM');
6765 END IF;
6766 end fail_check;
6767
6768 procedure pass_check is
6769 begin
6770
6771 IF l_DEBUG in ('Y', 'C') THEN
6772 null;
6773 -- cep_standard.debug('CE_VALIDATE_BANK_BR: ' || 'pass_check');
6774 END IF;
6775 end pass_check;
6776
6777 /****************/
6778 /* MAIN SECTION */
6779 /****************/
6780 BEGIN
6781 IF l_DEBUG in ('Y', 'C') THEN
6782 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_br');
6783 END IF;
6784
6785 BANK_VALUE := upper(Xi_BANK_NUMBER );
6786 Xo_VALUE_OUT := BANK_VALUE;
6787
6788 IF Xi_PASS_MAND_CHECK = 'F'
6789 then
6790 fail_mandatory;
6791
6792 ELSIF Xi_PASS_MAND_CHECK = 'P'
6793 then
6794
6795 BANK_VALUE := replace(BANK_VALUE,' ','');
6796
6797 IF length(BANK_VALUE) < 4
6798 then
6799 BANK_VALUE := lpad(BANK_VALUE,3,0);
6800 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
6801
6802 IF numeric_result = '0'
6803 then
6804 /* its numeric so continue */
6805 Xo_VALUE_OUT := BANK_VALUE;
6806 pass_check;
6807 ELSE
6808 fail_check;
6809
6810 END IF; /* end of numeric check for bank */
6811
6812 ELSE
6813 fail_check;
6814
6815 END IF; /* end of length check for bank */
6816
6817 END IF; /* end of mandatory check */
6818 /*
6819 IF l_DEBUG in ('Y', 'C') THEN
6820 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_br');
6821 END IF;
6822 */
6823 END CE_VALIDATE_BANK_BR;
6824
6825 /* -------------------------------------------------------------------- */
6826
6827 -- new bank validations 5/14/02
6828
6829 procedure CE_VALIDATE_BANK_DE(Xi_BANK_NUMBER in varchar2)
6830 AS
6831 BANK_NUM varchar2(30);
6832 numeric_result varchar2(40);
6833
6834 /**************************/
6835 /* SUB-PROCEDURES SECTION */
6836 /**************************/
6837
6838 procedure fail_check is
6839 begin
6840 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6841 fnd_msg_pub.add;
6842 IF l_DEBUG in ('Y', 'C') THEN
6843 cep_standard.debug('CE_VALIDATE_BANK_DE: ' || 'CE_INVALID_BANK_NUM');
6844 END IF;
6845
6846 end fail_check;
6847
6848 procedure pass_check is
6849 begin
6850
6851 IF l_DEBUG in ('Y', 'C') THEN
6852 null;
6853 -- cep_standard.debug('CE_VALIDATE_BANK_DE: ' || 'pass_check');
6854 END IF;
6855 end pass_check;
6856
6857 /****************/
6858 /* MAIN SECTION */
6859 /****************/
6860 BEGIN
6861 IF l_DEBUG in ('Y', 'C') THEN
6862 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_de');
6863 END IF;
6864
6865 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
6866
6867 BANK_NUM := replace(BANK_NUM,'-','');
6868
6869 IF (BANK_NUM) is not null then
6870 IF length(BANK_NUM) = 8 then
6871 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
6872
6873 IF numeric_result = '0'
6874 then /* its numeric so continue */
6875 pass_check;
6876 ELSE
6877 IF l_DEBUG in ('Y', 'C') THEN
6878 cep_standard.debug('bank num is not numeric');
6879 END IF;
6880 fail_check;
6881 END IF; /* end of numeric check */
6882 ELSE
6883 IF l_DEBUG in ('Y', 'C') THEN
6884 cep_standard.debug('bank num length is not 8');
6885 END IF;
6886 fail_check;
6887
6888 END IF; /* end of numeric check */
6889
6890 ELSE
6891 pass_check;
6892
6893 END IF; /* end of length check */
6894 /*
6895 IF l_DEBUG in ('Y', 'C') THEN
6896 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_de');
6897 END IF;
6898 */
6899 END CE_VALIDATE_BANK_DE;
6900
6901 /* -------------------------------------------------------------------- */
6902
6903 procedure CE_VALIDATE_BANK_GR(Xi_BANK_NUMBER in varchar2)
6904 AS
6905 BANK_VALUE varchar2(30);
6906 numeric_result varchar2(40);
6907
6908 /**************************/
6909 /* SUB-PROCEDURES SECTION */
6910 /**************************/
6911
6912 procedure fail_check is
6913 begin
6914 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6915 fnd_msg_pub.add;
6916 IF l_DEBUG in ('Y', 'C') THEN
6917 cep_standard.debug('CE_VALIDATE_BANK_GR: ' || 'CE_INVALID_BANK_NUM');
6918 END IF;
6919
6920 end fail_check;
6921
6922 procedure pass_check is
6923 begin
6924
6925 IF l_DEBUG in ('Y', 'C') THEN
6926 null;
6927 -- cep_standard.debug('CE_VALIDATE_BANK_GR: ' || 'pass_check');
6928 END IF;
6929 end pass_check;
6930
6931 /****************/
6932 /* MAIN SECTION */
6933 /****************/
6934 BEGIN
6935 IF l_DEBUG in ('Y', 'C') THEN
6936 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_gr');
6937 END IF;
6938
6939 BANK_VALUE := upper(replace(Xi_BANK_NUMBER,' ',''));
6940
6941 BANK_VALUE := replace(BANK_VALUE,'-','');
6942
6943 IF (BANK_VALUE) is not null then
6944
6945 IF length(BANK_VALUE) < 4
6946 then
6947 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
6948
6949 IF numeric_result = '0'
6950 then /* its numeric so continue */
6951 pass_check;
6952 ELSE
6953 fail_check;
6954
6955 END IF; /* end of numeric check */
6956
6957 ELSE
6958 fail_check;
6959
6960 END IF; /* end of length check */
6961
6962 ELSE
6963 pass_check;
6964
6965 END IF;
6966 /*
6967 IF l_DEBUG in ('Y', 'C') THEN
6968 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_gr');
6969 END IF;
6970 */
6971 END CE_VALIDATE_BANK_GR;
6972
6973 /* -------------------------------------------------------------------- */
6974
6975 procedure CE_VALIDATE_BANK_IS(Xi_BANK_NUMBER in varchar2,
6976 Xo_VALUE_OUT OUT NOCOPY varchar2)
6977 AS
6978 BANK_NUM varchar2(60);
6979 numeric_result varchar2(40);
6980
6981 /**************************/
6982 /* SUB-PROCEDURES SECTION */
6983 /**************************/
6984
6985 procedure fail_check is
6986 begin
6987 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
6988 fnd_msg_pub.add;
6989 IF l_DEBUG in ('Y', 'C') THEN
6990 cep_standard.debug('CE_VALIDATE_BANK_IS: ' || 'CE_INVALID_BANK_NUM');
6991 END IF;
6992
6993 end fail_check;
6994
6995 procedure pass_check is
6996 begin
6997
6998 IF l_DEBUG in ('Y', 'C') THEN
6999 null;
7000 -- cep_standard.debug('CE_VALIDATE_BANK_IS: ' || 'pass_check');
7001 END IF;
7002 end pass_check;
7003
7004 /****************/
7005 /* MAIN SECTION */
7006 /****************/
7007 BEGIN
7008 IF l_DEBUG in ('Y', 'C') THEN
7009 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_is');
7010 END IF;
7011
7012 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7013
7014 BANK_NUM := replace(BANK_NUM,'-','');
7015
7016 IF (BANK_NUM) is not null then
7017 IF length(BANK_NUM) < 5 then
7018 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7019
7020 IF numeric_result = '0'
7021 then /* its numeric so continue */
7022
7023 Xo_VALUE_OUT := lpad(bank_num,4,0);
7024 pass_check;
7025 ELSE
7026 fail_check;
7027 END IF; /* end of numeric check */
7028 ELSE
7029 fail_check;
7030
7031 END IF; /* end of length check */
7032
7033 ELSE
7034 pass_check;
7035
7036 END IF;
7037 /*
7038 IF l_DEBUG in ('Y', 'C') THEN
7039 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_is');
7040 END IF;
7041 */
7042 END CE_VALIDATE_BANK_IS;
7043
7044 /* -------------------------------------------------------------------- */
7045
7046 procedure CE_VALIDATE_BANK_IE(Xi_BANK_NUMBER in varchar2)
7047 AS
7048 BANK_NUM varchar2(60);
7049 numeric_result varchar2(40);
7050
7051 /**************************/
7052 /* SUB-PROCEDURES SECTION */
7053 /**************************/
7054
7055 procedure fail_check is
7056 begin
7057 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7058 fnd_msg_pub.add;
7059 IF l_DEBUG in ('Y', 'C') THEN
7060 cep_standard.debug('CE_VALIDATE_BANK_IE: ' || 'CE_INVALID_BANK_NUM');
7061 END IF;
7062
7063 end fail_check;
7064
7065 procedure pass_check is
7066 begin
7067
7068 IF l_DEBUG in ('Y', 'C') THEN
7069 null;
7070 -- cep_standard.debug('CE_VALIDATE_BANK_IE: ' || 'pass_check');
7071 END IF;
7072 end pass_check;
7073
7074 /****************/
7075 /* MAIN SECTION */
7076 /****************/
7077 BEGIN
7078 IF l_DEBUG in ('Y', 'C') THEN
7079 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_ie');
7080 END IF;
7081
7082 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7083
7084 BANK_NUM := replace(BANK_NUM,'-','');
7085
7086 IF (BANK_NUM) is not null then
7087 IF length(BANK_NUM) = 8 then
7088 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7089
7090 IF numeric_result = '0'
7091 then /* its numeric so continue */
7092 pass_check;
7093 ELSE
7094 fail_check;
7095 END IF; /* end of numeric check */
7096 ELSE
7097 fail_check;
7098
7099 END IF; /* end of length check */
7100
7101 ELSE
7102 pass_check;
7103
7104 END IF;
7105 /*
7106 IF l_DEBUG in ('Y', 'C') THEN
7107 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_ie');
7108 END IF;
7109 */
7110 END CE_VALIDATE_BANK_IE;
7111
7112 /* -------------------------------------------------------------------- */
7113
7114 procedure CE_VALIDATE_BANK_IT(Xi_BANK_NUMBER in varchar2,
7115 Xi_PASS_MAND_CHECK in varchar2)
7116 AS
7117 BANK_VALUE varchar2(30);
7118
7119 numeric_result varchar2(40);
7120
7121 /**************************/
7122 /* SUB-PROCEDURES SECTION */
7123 /**************************/
7124
7125 procedure fail_mandatory is
7126 begin
7127 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
7128 fnd_msg_pub.add;
7129 IF l_DEBUG in ('Y', 'C') THEN
7130 cep_standard.debug('CE_VALIDATE_BANK_IT: ' || 'CE_ENTER_BANK_NUM');
7131 END IF;
7132 end fail_mandatory;
7133
7134 procedure fail_check is
7135 begin
7136 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7137 fnd_msg_pub.add;
7138 IF l_DEBUG in ('Y', 'C') THEN
7139 cep_standard.debug('CE_VALIDATE_BANK_IT: ' || 'CE_INVALID_BANK_NUM');
7140 END IF;
7141
7142 end fail_check;
7143
7144 procedure pass_check is
7145 begin
7146
7147 IF l_DEBUG in ('Y', 'C') THEN
7148 null;
7149 -- cep_standard.debug('CE_VALIDATE_BANK_IT: ' || 'pass_check');
7150 END IF;
7151 end pass_check;
7152
7153 /****************/
7154 /* MAIN SECTION */
7155 /****************/
7156 BEGIN
7157 IF l_DEBUG in ('Y', 'C') THEN
7158 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_it');
7159 END IF;
7160
7161 BANK_VALUE := upper(replace(Xi_BANK_NUMBER,' ',''));
7162
7163 BANK_VALUE := replace(BANK_VALUE,'-','');
7164
7165
7166 IF Xi_PASS_MAND_CHECK = 'F'
7167 then
7168 fail_mandatory;
7169
7170 ELSIF Xi_PASS_MAND_CHECK = 'P'
7171 then
7172
7173 BANK_VALUE := replace(BANK_VALUE,' ','');
7174
7175 IF length(BANK_VALUE) = 5
7176 then
7177
7178 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
7179
7180 IF numeric_result = '0'
7181 then
7182 /* its numeric so continue */
7183
7184 pass_check;
7185 ELSE
7186 fail_check;
7187
7188 END IF; /* end of numeric check */
7189
7190 ELSE
7191 fail_check;
7192
7193 END IF; /* end of length check */
7194 END IF; /* end of mandatory check */
7195
7196 /*
7197 IF l_DEBUG in ('Y', 'C') THEN
7198 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_it');
7199 END IF;
7200 */
7201 END CE_VALIDATE_BANK_IT;
7202
7203 /* -------------------------------------------------------------------- */
7204
7205 procedure CE_VALIDATE_BANK_LU(Xi_BANK_NUMBER in varchar2)
7206 AS
7207 BANK_NUM varchar2(60);
7208 numeric_result varchar2(40);
7209
7210 /**************************/
7211 /* SUB-PROCEDURES SECTION */
7212 /**************************/
7213
7214 procedure fail_check is
7215 begin
7216 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7217 fnd_msg_pub.add;
7218 IF l_DEBUG in ('Y', 'C') THEN
7219 cep_standard.debug('CE_VALIDATE_BANK_LU: ' || 'CE_INVALID_BANK_NUM');
7220 END IF;
7221
7222 end fail_check;
7223
7224 procedure pass_check is
7225 begin
7226
7227 IF l_DEBUG in ('Y', 'C') THEN
7228 null;
7229 -- cep_standard.debug('CE_VALIDATE_BANK_LU: ' || 'pass_check');
7230 END IF;
7231 end pass_check;
7232
7233 /****************/
7234 /* MAIN SECTION */
7235 /****************/
7236 BEGIN
7237 IF l_DEBUG in ('Y', 'C') THEN
7238 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_lu');
7239 END IF;
7240
7241 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7242
7243 BANK_NUM := replace(BANK_NUM,'-','');
7244
7245 IF (BANK_NUM) is not null then
7246 IF length(BANK_NUM) = 2 then
7247 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7248
7249 IF numeric_result = '0'
7250 then /* its numeric so continue */
7251 pass_check;
7252 ELSE
7253 fail_check;
7254 END IF; /* end of numeric check */
7255 ELSE
7256 fail_check;
7257
7258 END IF; /* end of length check */
7259
7260 ELSE
7261 pass_check;
7262
7263 END IF;
7264 /*
7265 IF l_DEBUG in ('Y', 'C') THEN
7266 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_lu');
7267 END IF;
7268 */
7269 END CE_VALIDATE_BANK_LU;
7270
7271 /* -------------------------------------------------------------------- */
7272
7273 procedure CE_VALIDATE_BANK_PL(Xi_BANK_NUMBER in varchar2)
7274 AS
7275 BANK_NUM varchar2(60);
7276 numeric_result varchar2(40);
7277 cal_cd1 number;
7278 /**************************/
7279 /* SUB-PROCEDURES SECTION */
7280 /**************************/
7281
7282 procedure fail_check is
7283 begin
7284 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7285 fnd_msg_pub.add;
7286 IF l_DEBUG in ('Y', 'C') THEN
7287 cep_standard.debug('CE_VALIDATE_BANK_PL: ' || 'CE_INVALID_BANK_NUM');
7288 END IF;
7289
7290 end fail_check;
7291
7292
7293 procedure pass_check is
7294 begin
7295
7296 IF l_DEBUG in ('Y', 'C') THEN
7297 null;
7298 -- cep_standard.debug('CE_VALIDATE_BANK_PL: ' || 'pass_check');
7299 END IF;
7300 end pass_check;
7301
7302 /****************/
7303 /* MAIN SECTION */
7304 /****************/
7305 BEGIN
7306 IF l_DEBUG in ('Y', 'C') THEN
7307 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_pl');
7308 END IF;
7309
7310 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7311
7312 BANK_NUM := replace(BANK_NUM,'-','');
7313
7314 IF (BANK_NUM) is not null then
7315 IF length(BANK_NUM) = 8 then
7316 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7317
7318 IF numeric_result = '0'
7319 then /* its numeric so continue */
7320
7321 -- Modulus 10
7322
7323 cal_cd1 := 10 - mod(((to_number(substr(BANK_NUM,1,1)) * 1)
7324 +(to_number(substr(BANK_NUM,2,1)) * 7)
7325 +(to_number(substr(BANK_NUM,3,1)) * 9)
7326 +(to_number(substr(BANK_NUM,4,1)) * 3)
7327 +(to_number(substr(BANK_NUM,5,1)) * 1)
7328 +(to_number(substr(BANK_NUM,6,1)) * 7)
7329 +(to_number(substr(BANK_NUM,7,1)) * 9)),10);
7330
7331 IF l_DEBUG in ('Y', 'C') THEN
7332 cep_standard.debug('cal_cd1: '|| cal_cd1);
7333 END IF;
7334
7335 IF cal_cd1 = substr(BANK_NUM,8,1) then
7336 pass_check;
7337 else
7338 fail_check;
7339 end if;
7340
7341 ELSE
7342 fail_check;
7343 END IF; /* end of numeric check */
7344 ELSE
7345 fail_check;
7346
7347 END IF; /* end of length check */
7348
7349 ELSE
7350 pass_check;
7351
7352 END IF;
7353 /*
7354 IF l_DEBUG in ('Y', 'C') THEN
7355 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_pl');
7356 END IF;
7357 */
7358 END CE_VALIDATE_BANK_PL;
7359
7360 /* -------------------------------------------------------------------- */
7361
7362 procedure CE_VALIDATE_BANK_SE(Xi_BANK_NUMBER in varchar2)
7363 AS
7364 BANK_NUM varchar2(60);
7365 numeric_result varchar2(40);
7366
7367 /**************************/
7368 /* SUB-PROCEDURES SECTION */
7369 /**************************/
7370
7371 procedure fail_check is
7372 begin
7373 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7374 fnd_msg_pub.add;
7375 IF l_DEBUG in ('Y', 'C') THEN
7376 cep_standard.debug('CE_VALIDATE_BANK_SE: ' || 'CE_INVALID_BANK_NUM');
7377 END IF;
7378
7379 end fail_check;
7380
7381 procedure pass_check is
7382 begin
7383
7384 IF l_DEBUG in ('Y', 'C') THEN
7385 null;
7386 -- cep_standard.debug('CE_VALIDATE_BANK_SE: ' || 'pass_check');
7387 END IF;
7388 end pass_check;
7389
7390 /****************/
7391 /* MAIN SECTION */
7392 /****************/
7393 BEGIN
7394 IF l_DEBUG in ('Y', 'C') THEN
7395 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_se');
7396 END IF;
7397
7398 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7399
7400 BANK_NUM := replace(BANK_NUM,'-','');
7401
7402 IF (BANK_NUM) is not null then
7403 IF ((length(BANK_NUM) = 4) or (length(BANK_NUM) = 5)) then
7404 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7405
7406 IF numeric_result = '0'
7407 then /* its numeric so continue */
7408 pass_check;
7409 ELSE
7410 fail_check;
7411 END IF; /* end of numeric check */
7412 ELSE
7413 fail_check;
7414
7415 END IF; /* end of length check */
7416
7417 ELSE
7418 pass_check;
7419
7420 END IF;
7421 /*
7422 IF l_DEBUG in ('Y', 'C') THEN
7423 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_se');
7424 END IF;
7425 */
7426 END CE_VALIDATE_BANK_SE;
7427
7428 /* -------------------------------------------------------------------- */
7429
7430 procedure CE_VALIDATE_BANK_CH(Xi_BANK_NUMBER in varchar2)
7431 AS
7432 BANK_NUM varchar2(60);
7433 numeric_result varchar2(40);
7434
7435 /**************************/
7436 /* SUB-PROCEDURES SECTION */
7437 /**************************/
7438
7439 procedure fail_check is
7440 begin
7441 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7442 fnd_msg_pub.add;
7443 IF l_DEBUG in ('Y', 'C') THEN
7444 cep_standard.debug('CE_VALIDATE_BANK_CH: ' || 'CE_INVALID_BANK_NUM');
7445 END IF;
7446
7447 end fail_check;
7448
7449 procedure pass_check is
7450 begin
7451
7452 IF l_DEBUG in ('Y', 'C') THEN
7453 null;
7454 -- cep_standard.debug('CE_VALIDATE_BANK_CH: ' || 'pass_check');
7455 END IF;
7456 end pass_check;
7457
7458 /****************/
7459 /* MAIN SECTION */
7460 /****************/
7461 BEGIN
7462 IF l_DEBUG in ('Y', 'C') THEN
7463 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_ch');
7464 END IF;
7465
7466 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7467
7468 BANK_NUM := replace(BANK_NUM,'-','');
7469
7470 IF (BANK_NUM) is not null then
7471 IF ((length(BANK_NUM) > 2) and (length(BANK_NUM) < 6)) then
7472 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7473
7474 IF numeric_result = '0'
7475 then /* its numeric so continue */
7476 pass_check;
7477 ELSE
7478 fail_check;
7479 END IF; /* end of numeric check */
7480 ELSE
7481 fail_check;
7482
7483 END IF; /* end of length check */
7484
7485 ELSE
7486 pass_check;
7487
7488 END IF;
7489 /*
7490 IF l_DEBUG in ('Y', 'C') THEN
7491 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_ch');
7492 END IF;
7493 */
7494 END CE_VALIDATE_BANK_CH;
7495
7496 /* -------------------------------------------------------------------- */
7497
7498 procedure CE_VALIDATE_BANK_GB(Xi_BANK_NUMBER in varchar2)
7499 AS
7500 BANK_NUM varchar2(60);
7501 numeric_result varchar2(40);
7502
7503 /**************************/
7504 /* SUB-PROCEDURES SECTION */
7505 /**************************/
7506
7507 procedure fail_check is
7508 begin
7509 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7510 fnd_msg_pub.add;
7511 IF l_DEBUG in ('Y', 'C') THEN
7512 cep_standard.debug('CE_VALIDATE_BANK_GB: ' || 'CE_INVALID_BANK_NUM');
7513 END IF;
7514
7515 end fail_check;
7516
7517 procedure pass_check is
7518 begin
7519
7520 IF l_DEBUG in ('Y', 'C') THEN
7521 null;
7522 -- cep_standard.debug('CE_VALIDATE_BANK_GB: ' || 'pass_check');
7523 END IF;
7524 end pass_check;
7525
7526 /****************/
7527 /* MAIN SECTION */
7528 /****************/
7529 BEGIN
7530 IF l_DEBUG in ('Y', 'C') THEN
7531 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_gb');
7532 END IF;
7533
7534 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7535
7536 BANK_NUM := replace(BANK_NUM,'-','');
7537
7538 IF (BANK_NUM) is not null then
7539 IF length(BANK_NUM) = 6 then
7540 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7541
7542 IF numeric_result = '0'
7543 then /* its numeric so continue */
7544 pass_check;
7545 ELSE
7546 fail_check;
7547 END IF; /* end of numeric check */
7548 ELSE
7549 fail_check;
7550
7551 END IF; /* end of length check */
7552
7553 ELSE
7554 pass_check;
7555
7556 END IF;
7557 /*
7558 IF l_DEBUG in ('Y', 'C') THEN
7559 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.ce_validate_bank_gb');
7560 END IF;
7561 */
7562 END CE_VALIDATE_BANK_GB;
7563
7564 /* -------------------------------------------------------------------- */
7565
7566 procedure CE_VALIDATE_BANK_CO(Xi_COUNTRY_NAME in varchar2,
7567 Xi_BANK_NAME in varchar2,
7568 Xi_TAX_PAYER_ID in varchar2)
7569 AS
7570 tax_id varchar2(60);
7571 tax_id1 varchar2(60);
7572 tax_id_end number;
7573
7574 tax_id_cd_start number;
7575 tax_id_cd varchar2(60);
7576
7577 numeric_result varchar2(40);
7578
7579 l_supp varchar(10);
7580 l_comp varchar(10);
7581 l_cust varchar(10);
7582 l_bank varchar(10);
7583
7584 /**************************/
7585 /* SUB-PROCEDURES SECTION */
7586 /**************************/
7587
7588 procedure fail_check is
7589 begin
7590 fnd_message.set_name ('CE', 'CE_INVALID_TAX_PAYER_ID');
7591 fnd_msg_pub.add;
7592 IF l_DEBUG in ('Y', 'C') THEN
7593 cep_standard.debug('CE_VALIDATE_BANK_CO: ' || 'CE_INVALID_TAX_PAYER_ID');
7594 END IF;
7595
7596 end fail_check;
7597
7598 procedure fail_check_unique is
7599 begin
7600 fnd_message.set_name ('CE', 'CE_TAX_PAYER_ID_NOT_UNIQUE');
7601 fnd_msg_pub.add;
7602 IF l_DEBUG in ('Y', 'C') THEN
7603 cep_standard.debug('CE_VALIDATE_BANK_CO: ' || 'CE_TAX_PAYER_ID_NOT_UNIQUE');
7604 END IF;
7605
7606 end fail_check_unique;
7607
7608 procedure fail_check_required is
7609 begin
7610 fnd_message.set_name ('CE', 'CE_ENTER_TAX_PAYER_ID');
7611 fnd_msg_pub.add;
7612 IF l_DEBUG in ('Y', 'C') THEN
7613 cep_standard.debug('CE_VALIDATE_BANK_CO: ' || 'CE_ENTER_TAX_PAYER_ID');
7614 END IF;
7615
7616 end fail_check_required;
7617
7618 procedure pass_check is
7619 begin
7620
7621 IF l_DEBUG in ('Y', 'C') THEN
7622 null;
7623 -- cep_standard.debug('CE_VALIDATE_BANK_CO: ' || 'pass_check');
7624 END IF;
7625 end pass_check;
7626
7627 /****************/
7628 /* MAIN SECTION */
7629 /****************/
7630 BEGIN
7631
7632 IF l_DEBUG in ('Y', 'C') THEN
7633 cep_standard.debug('>>CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_CO');
7634 END IF;
7635
7636 -- the last digit of tax payer id is the check digits
7637
7638 TAX_ID1 := upper(replace(Xi_TAX_PAYER_ID,' ',''));
7639 TAX_ID1 := replace(TAX_ID1,'-','');
7640 tax_id_end := (length(tax_id1) - 1);
7641 tax_id := substr(tax_id1,1, tax_id_end);
7642
7643 tax_id_cd_start := (length(tax_id1));
7644 tax_id_cd := substr(tax_id1, tax_id_cd_start, length(tax_id1));
7645
7646 IF l_DEBUG in ('Y', 'C') THEN
7647 cep_standard.debug('tax_id_cd: ' || tax_id_cd || ' tax_id: ' || tax_id );
7648 END IF;
7649
7650 IF (tax_id) is not null then
7651 IF length(tax_id) <= 14 then
7652 numeric_result := ce_check_numeric(tax_id,1,length(tax_id));
7653
7654 IF numeric_result = '0'
7655 then /* its numeric so continue */
7656 if CE_VALIDATE_BANKINFO_UPG.CE_VAL_UNIQUE_TAX_PAYER_ID(Xi_COUNTRY_NAME,TAX_ID) = 'FALSE' then
7657 IF l_DEBUG in ('Y', 'C') THEN
7658 cep_standard.debug('failed CE_VAL_UNIQUE_TAX_PAYER_ID' );
7659 END IF;
7660 fail_check_unique;
7661 else
7662 CE_VALIDATE_BANKINFO_UPG.CE_CHECK_CROSS_MODULE_TAX_ID(Xi_COUNTRY_NAME,
7663 Xi_BANK_NAME,
7664 TAX_ID,
7665 l_cust,
7666 l_supp,
7667 l_comp,
7668 l_bank);
7669
7670 IF l_DEBUG in ('Y', 'C') THEN
7671 cep_standard.debug('l_cust: '|| l_cust ||' l_supp: '|| l_supp ||
7672 ' l_comp: '|| l_comp ||' l_bank: '|| l_bank );
7673 END IF;
7674
7675
7676 IF (l_supp = 'bk3' OR l_cust='bk2' OR l_comp = 'bk1')THEN
7677 --FND_MESSAGE.SET_NAME('JG','JG_ZZ_PRIMARYID_NUM_EXIST');
7678 FND_MESSAGE.SET_NAME('CE','CE_TAXID_EXIST');
7679 fnd_msg_pub.add;
7680 END IF;
7681 IF (l_supp = 'bk5') THEN
7682 --FND_MESSAGE.SET_NAME('JG','JG_ZZ_TAXID_BANK_EXIST_AS_SUPP');
7683 FND_MESSAGE.SET_NAME('CE','CE_TAXID_BANK_EXIST_AS_SUPP');
7684 fnd_msg_pub.add;
7685 END IF;
7686 IF (l_cust = 'bk4') THEN
7687 --FND_MESSAGE.SET_NAME('JG','JG_ZZ_TAXID_BANK_EXIST_AS_CUST');
7688 FND_MESSAGE.SET_NAME('CE','CE_TAXID_BANK_EXIST_AS_CUST');
7689 fnd_msg_pub.add;
7690 END IF;
7691 IF (l_comp = 'bk6') THEN
7692 --FND_MESSAGE.SET_NAME('JG','JG_ZZ_TAXID_BANK_EXIST_AS_COMP');
7693 FND_MESSAGE.SET_NAME('CE','CE_TAXID_BANK_EXIST_AS_COMP');
7694 fnd_msg_pub.add;
7695 END IF;
7696 IF CE_VALIDATE_BANKINFO_UPG.CE_TAX_ID_CHECK_ALGORITHM(TAX_ID,Xi_COUNTRY_NAME,TAX_ID_CD) = 'FALSE' then
7697 IF l_DEBUG in ('Y', 'C') THEN
7698 cep_standard.debug('failed CE_TAX_ID_CHECK_ALGORITHM' );
7699 END IF;
7700 fail_check;
7701 END IF;
7702
7703 end if;
7704 ELSE
7705 fail_check;
7706 END IF; /* end of numeric check */
7707 ELSE
7708 fail_check;
7709
7710 END IF; /* end of length check */
7711
7712 ELSE --tax payer id is required
7713 fail_check_required;
7714
7715 END IF;
7716
7717 /*
7718 IF l_DEBUG in ('Y', 'C') THEN
7719 cep_standard.debug('<<CE_VALIDATE_BANKINFO_UPG.CE_VALIDATE_BANK_CO');
7720 END IF;
7721 */
7722 END CE_VALIDATE_BANK_CO;
7723
7724 --Added 10/19/04
7725
7726 /* -------------------------------------------------------------------- */
7727
7728 procedure CE_VALIDATE_BANK_AU(Xi_BANK_NUMBER in varchar2)
7729 AS
7730 BANK_NUM varchar2(60);
7731 numeric_result varchar2(40);
7732
7733 /**************************/
7734 /* SUB-PROCEDURES SECTION */
7735 /**************************/
7736
7737 procedure fail_check is
7738 begin
7739 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7740 fnd_msg_pub.add;
7741 IF l_DEBUG in ('Y', 'C') THEN
7742 cep_standard.debug('CE_VALIDATE_BANK_AU: ' || 'CE_INVALID_BANK_NUM');
7743 END IF;
7744
7745 end fail_check;
7746
7747 procedure pass_check is
7748 begin
7749
7750 IF l_DEBUG in ('Y', 'C') THEN
7751 cep_standard.debug('CE_VALIDATE_BANK_AU: ' || 'pass_check');
7752 END IF;
7753 end pass_check;
7754
7755 /****************/
7756 /* MAIN SECTION */
7757 /****************/
7758 BEGIN
7759 IF l_DEBUG in ('Y', 'C') THEN
7760 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_au');
7761 END IF;
7762
7763 BANK_NUM := upper(replace(Xi_BANK_NUMBER,' ',''));
7764
7765 BANK_NUM := replace(BANK_NUM,'-','');
7766
7767 IF (BANK_NUM) is not null then
7768 IF length(BANK_NUM) = 2 or length(BANK_NUM) = 3 then
7769 numeric_result := ce_check_numeric(BANK_NUM,1,length(BANK_NUM));
7770
7771 IF numeric_result = '0'
7772 then /* its numeric so continue */
7773 pass_check;
7774 ELSE
7775 fail_check;
7776 END IF; /* end of numeric check */
7777 ELSE
7778 fail_check;
7779
7780 END IF; /* end of length check */
7781
7782 ELSE
7783 pass_check;
7784
7785 END IF;
7786 IF l_DEBUG in ('Y', 'C') THEN
7787 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_bank_au');
7788 END IF;
7789
7790 END CE_VALIDATE_BANK_AU;
7791
7792 /* -------------------------------------------------------------------- */
7793
7794 procedure CE_VALIDATE_BANK_IL(Xi_BANK_NUMBER in varchar2,
7795 Xi_PASS_MAND_CHECK in varchar2)
7796 AS
7797 BANK_VALUE varchar2(30);
7798
7799 numeric_result varchar2(40);
7800
7801 /**************************/
7802 /* SUB-PROCEDURES SECTION */
7803 /**************************/
7804
7805 procedure fail_mandatory is
7806 begin
7807 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
7808 fnd_msg_pub.add;
7809 IF l_DEBUG in ('Y', 'C') THEN
7810 cep_standard.debug('CE_VALIDATE_BANK_IL: ' || 'CE_ENTER_BANK_NUM');
7811 END IF;
7812 end fail_mandatory;
7813
7814 procedure fail_check is
7815 begin
7816 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7817 fnd_msg_pub.add;
7818 IF l_DEBUG in ('Y', 'C') THEN
7819 cep_standard.debug('CE_VALIDATE_BANK_IL: ' || 'CE_INVALID_BANK_NUM');
7820 END IF;
7821
7822 end fail_check;
7823
7824 procedure pass_check is
7825 begin
7826
7827 IF l_DEBUG in ('Y', 'C') THEN
7828 cep_standard.debug('CE_VALIDATE_BANK_IL: ' || 'pass_check');
7829 END IF;
7830 end pass_check;
7831
7832 /****************/
7833 /* MAIN SECTION */
7834 /****************/
7835 BEGIN
7836 IF l_DEBUG in ('Y', 'C') THEN
7837 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_il');
7838 END IF;
7839
7840 BANK_VALUE := upper(replace(Xi_BANK_NUMBER,' ',''));
7841
7842 BANK_VALUE := replace(BANK_VALUE,'-','');
7843
7844
7845 IF Xi_PASS_MAND_CHECK = 'F'
7846 then
7847 fail_mandatory;
7848
7849 ELSIF Xi_PASS_MAND_CHECK = 'P'
7850 then
7851
7852 BANK_VALUE := replace(BANK_VALUE,' ','');
7853
7854 IF length(BANK_VALUE) <= 2
7855 then
7856
7857 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
7858
7859 IF numeric_result = '0'
7860 then
7861 /* its numeric so continue */
7862
7863 pass_check;
7864 ELSE
7865 fail_check;
7866
7867 END IF; /* end of numeric check */
7868
7869 ELSE
7870 fail_check;
7871
7872 END IF; /* end of length check */
7873 END IF; /* end of mandatory check */
7874
7875 IF l_DEBUG in ('Y', 'C') THEN
7876 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_bank_il');
7877 END IF;
7878
7879 END CE_VALIDATE_BANK_IL;
7880
7881
7882 /* -------------------------------------------------------------------- */
7883
7884 procedure CE_VALIDATE_BANK_NZ(Xi_BANK_NUMBER in varchar2,
7885 Xi_PASS_MAND_CHECK in varchar2)
7886 AS
7887 BANK_VALUE varchar2(30);
7888
7889 numeric_result varchar2(40);
7890
7891 /**************************/
7892 /* SUB-PROCEDURES SECTION */
7893 /**************************/
7894
7895 procedure fail_mandatory is
7896 begin
7897 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
7898 fnd_msg_pub.add;
7899 IF l_DEBUG in ('Y', 'C') THEN
7900 cep_standard.debug('CE_VALIDATE_BANK_NZ: ' || 'CE_ENTER_BANK_NUM');
7901 END IF;
7902 end fail_mandatory;
7903
7904 procedure fail_check is
7905 begin
7906 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
7907 fnd_msg_pub.add;
7908 IF l_DEBUG in ('Y', 'C') THEN
7909 cep_standard.debug('CE_VALIDATE_BANK_NZ: ' || 'CE_INVALID_BANK_NUM');
7910 END IF;
7911
7912 end fail_check;
7913
7914 procedure pass_check is
7915 begin
7916
7917 IF l_DEBUG in ('Y', 'C') THEN
7918 cep_standard.debug('CE_VALIDATE_BANK_NZ: ' || 'pass_check');
7919 END IF;
7920 end pass_check;
7921
7922 /****************/
7923 /* MAIN SECTION */
7924 /****************/
7925 BEGIN
7926 IF l_DEBUG in ('Y', 'C') THEN
7927 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_nz');
7928 END IF;
7929
7930 BANK_VALUE := upper(replace(Xi_BANK_NUMBER,' ',''));
7931
7932 BANK_VALUE := replace(BANK_VALUE,'-','');
7933
7934
7935 IF Xi_PASS_MAND_CHECK = 'F'
7936 then
7937 fail_mandatory;
7938
7939 ELSIF Xi_PASS_MAND_CHECK = 'P'
7940 then
7941
7942 BANK_VALUE := replace(BANK_VALUE,' ','');
7943
7944 IF length(BANK_VALUE) = 2
7945 then
7946
7947 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
7948
7949 IF numeric_result = '0'
7950 then
7951 /* its numeric so continue */
7952
7953 pass_check;
7954 ELSE
7955 fail_check;
7956
7957 END IF; /* end of numeric check */
7958
7959 ELSE
7960 fail_check;
7961
7962 END IF; /* end of length check */
7963 END IF; /* end of mandatory check */
7964
7965 IF l_DEBUG in ('Y', 'C') THEN
7966 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_bank_nz');
7967 END IF;
7968
7969
7970 END CE_VALIDATE_BANK_NZ;
7971
7972 /* -------------------------------------------------------------------- */
7973
7974 procedure CE_VALIDATE_BANK_JP(Xi_BANK_NUMBER in varchar2,
7975 Xi_BANK_NAME_ALT in varchar2,
7976 Xi_PASS_MAND_CHECK in varchar2,
7977 Xi_VALIDATION_TYPE in varchar2)
7978 AS
7979 BANK_VALUE varchar2(30);
7980
7981 numeric_result varchar2(40);
7982
7983 /**************************/
7984 /* SUB-PROCEDURES SECTION */
7985 /**************************/
7986
7987 procedure fail_mandatory is
7988 begin
7989 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NUM');
7990 fnd_msg_pub.add;
7991 IF l_DEBUG in ('Y', 'C') THEN
7992 cep_standard.debug('CE_VALIDATE_BANK_JP: ' || 'CE_ENTER_BANK_NUM');
7993 END IF;
7994 end fail_mandatory;
7995
7996 procedure fail_bank_name_alt is
7997 begin
7998 fnd_message.set_name ('CE', 'CE_ENTER_BANK_NAME_ALT');
7999 fnd_msg_pub.add;
8000 IF l_DEBUG in ('Y', 'C') THEN
8001 cep_standard.debug('CE_VALIDATE_BANK_JP: ' || 'CE_ENTER_BANK_NAME_ALT');
8002 END IF;
8003 end fail_bank_name_alt;
8004
8005
8006 procedure fail_check is
8007 begin
8008 fnd_message.set_name ('CE', 'CE_INVALID_BANK_NUM');
8009 fnd_msg_pub.add;
8010 IF l_DEBUG in ('Y', 'C') THEN
8011 cep_standard.debug('CE_VALIDATE_BANK_JP: ' || 'CE_INVALID_BANK_NUM');
8012 END IF;
8013
8014 end fail_check;
8015
8016 procedure pass_check is
8017 begin
8018
8019 IF l_DEBUG in ('Y', 'C') THEN
8020 cep_standard.debug('CE_VALIDATE_BANK_JP: ' || 'pass_check');
8021 END IF;
8022 end pass_check;
8023
8024 /****************/
8025 /* MAIN SECTION */
8026 /****************/
8027 BEGIN
8028 IF l_DEBUG in ('Y', 'C') THEN
8029 cep_standard.debug('>>CE_VALIDATE_BANKINFO.ce_validate_bank_jp');
8030 END IF;
8031
8032 BANK_VALUE := upper(replace(Xi_BANK_NUMBER,' ',''));
8033
8034 BANK_VALUE := replace(BANK_VALUE,'-','');
8035
8036
8037 IF Xi_PASS_MAND_CHECK = 'F'
8038 then
8039 fail_mandatory;
8040
8041 ELSIF Xi_PASS_MAND_CHECK = 'P'
8042 then
8043
8044 BANK_VALUE := replace(BANK_VALUE,' ','');
8045
8046 IF length(BANK_VALUE) = 3
8047 then
8048
8049 numeric_result := ce_check_numeric(BANK_VALUE,1,length(BANK_VALUE));
8050
8051 IF numeric_result = '0'
8052 then
8053 /* its numeric so continue */
8054
8055 pass_check;
8056 ELSE
8057 fail_check;
8058
8059 END IF; /* end of numeric check */
8060
8061 ELSE
8062 fail_check;
8063
8064 END IF; /* end of length check */
8065 END IF; /* end of mandatory check */
8066
8067 IF (Xi_VALIDATION_TYPE = 'ALL') THEN
8068 IF (Xi_BANK_NAME_ALT is null) THEN
8069 fail_bank_name_alt;
8070 END IF;
8071 END IF;
8072
8073 IF l_DEBUG in ('Y', 'C') THEN
8074 cep_standard.debug('<<CE_VALIDATE_BANKINFO.ce_validate_bank_jp');
8075 END IF;
8076
8077
8078 END CE_VALIDATE_BANK_JP;
8079
8080
8081 -- added 10/25/04
8082 /* --------------------------------------------------------------------
8083 | PRIVATE PROCEDURE |
8084 | CE_VALIDATE_MISC_* other misc validations
8085 --------------------------------------------------------------------- */
8086
8087 procedure CE_VALIDATE_MISC_EFT_NUM(X_COUNTRY_NAME in varchar2,
8088 X_EFT_NUMBER in varchar2,
8089 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8090 x_msg_count OUT NOCOPY NUMBER,
8091 x_msg_data OUT NOCOPY VARCHAR2,
8092 x_return_status IN OUT NOCOPY VARCHAR2)
8093 AS
8094 COUNTRY_NAME VARCHAR2(2);
8095 EFT_NUM_VALUE varchar2(60);
8096 numeric_result varchar2(40);
8097
8098 procedure fail_mandatory is
8099 begin
8100 fnd_message.set_name ('CE', 'CE_ENTER_EFT_NUMBER');
8101 fnd_msg_pub.add;
8102 IF l_DEBUG in ('Y', 'C') THEN
8103 cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'CE_ENTER_EFT_NUMBER');
8104 END IF;
8105 end fail_mandatory;
8106
8107 procedure fail_check is
8108 begin
8109 fnd_message.set_name ('CE', 'CE_INVALID_EFT_NUMBER');
8110 fnd_msg_pub.add;
8111 IF l_DEBUG in ('Y', 'C') THEN
8112 cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'CE_INVALID_EFT_NUMBER');
8113 END IF;
8114 end fail_check;
8115
8116 procedure pass_check is
8117 begin
8118
8119 IF l_DEBUG in ('Y', 'C') THEN
8120 cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: ' || 'pass_check');
8121 END IF;
8122 end pass_check;
8123
8124
8125 BEGIN
8126 IF l_DEBUG in ('Y', 'C') THEN
8127 cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM');
8128 END IF;
8129
8130 -- initialize API return status to success.
8131 x_return_status := fnd_api.g_ret_sts_success;
8132
8133 COUNTRY_NAME := X_COUNTRY_NAME;
8134
8135 IF l_DEBUG in ('Y', 'C') THEN
8136 cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM - COUNTRY_NAME: '|| COUNTRY_NAME);
8137 END IF;
8138
8139 -- Initialize message list if p_init_msg_list is set to TRUE.
8140 IF FND_API.to_Boolean(p_init_msg_list) THEN
8141 FND_MSG_PUB.initialize;
8142 END IF;
8143
8144 IF l_DEBUG in ('Y', 'C') THEN
8145 cep_standard.debug('CE_VALIDATE_MISC_EFT_NUM: P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
8146 END IF;
8147
8148
8149 EFT_NUM_VALUE := upper(replace(X_EFT_NUMBER,' ',''));
8150
8151 EFT_NUM_VALUE := replace(EFT_NUM_VALUE,'-','');
8152
8153 IF (COUNTRY_NAME = 'IL')
8154 THEN
8155
8156 IF (EFT_NUM_VALUE is null) then
8157 fail_mandatory;
8158 ELSE
8159 IF length(EFT_NUM_VALUE) = 8 then
8160
8161 numeric_result := ce_check_numeric(EFT_NUM_VALUE,1,length(EFT_NUM_VALUE));
8162
8163 IF numeric_result = '0' then
8164 -- its numeric so continue
8165
8166 pass_check;
8167 ELSE
8168 fail_check;
8169
8170 END IF; -- end of numeric check
8171
8172 ELSE
8173 fail_check;
8174
8175 END IF; -- end of length check
8176 END IF; -- end of mandatory check
8177 ELSE -- other countries pass_check
8178 pass_check;
8179 END IF; -- end of country_name
8180
8181
8182 FND_MSG_PUB.Count_And_Get(
8183 p_encoded => FND_API.G_FALSE,
8184 p_count => x_msg_count,
8185 p_data => x_msg_data);
8186
8187 IF x_msg_count > 0 THEN
8188 x_return_status := fnd_api.g_ret_sts_error;
8189 END IF;
8190
8191 IF l_DEBUG in ('Y', 'C') THEN
8192 cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_misc_eft_num - P_COUNT: '|| x_msg_count||
8193 --'P_DATA: '|| x_msg_data||
8194 --' X_VALUE_OUT: '|| X_VALUE_OUT||
8195 '<<CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM');
8196 END IF;
8197
8198 EXCEPTION
8199 WHEN OTHERS THEN
8200 IF l_DEBUG in ('Y', 'C') THEN
8201 cep_standard.debug('CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_EFT_NUM ' ||X_COUNTRY_NAME );
8202 END IF;
8203
8204 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
8205 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_misc_eft_num');
8206 fnd_msg_pub.add;
8207 RAISE;
8208 END CE_VALIDATE_MISC_EFT_NUM;
8209
8210 /* -------------------------------------------------------------------- */
8211
8212 procedure CE_VALIDATE_MISC_ACCT_HLDR_ALT(X_COUNTRY_NAME in varchar2,
8213 X_ACCOUNT_HOLDER_ALT in varchar2,
8214 X_ACCOUNT_CLASSIFICATION in varchar2,
8215 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8216 x_msg_count OUT NOCOPY NUMBER,
8217 x_msg_data OUT NOCOPY VARCHAR2,
8218 x_return_status IN OUT NOCOPY VARCHAR2)
8219 AS
8220 COUNTRY_NAME VARCHAR2(2);
8221 ACCOUNT_HOLDER_ALT varchar2(60);
8222 numeric_result varchar2(40);
8223
8224
8225 procedure fail_mandatory is
8226 begin
8227 fnd_message.set_name ('CE', 'CE_ENTER_ACCOUNT_HOLDER_ALT');
8228 fnd_msg_pub.add;
8229 IF l_DEBUG in ('Y', 'C') THEN
8230 cep_standard.debug('CE_VALIDATE_MISC_ACCT_HLDR_ALT: ' || 'CE_ENTER_ACCOUNT_HOLDER_ALT');
8231 END IF;
8232 end fail_mandatory;
8233
8234 procedure pass_check is
8235 begin
8236
8237 IF l_DEBUG in ('Y', 'C') THEN
8238 cep_standard.debug('pass_check');
8239 END IF;
8240 end pass_check;
8241
8242 BEGIN
8243 IF l_DEBUG in ('Y', 'C') THEN
8244 cep_standard.debug('>>CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT');
8245 END IF;
8246
8247 -- initialize API return status to success.
8248 x_return_status := fnd_api.g_ret_sts_success;
8249
8250 COUNTRY_NAME := X_COUNTRY_NAME;
8251
8252 IF l_DEBUG in ('Y', 'C') THEN
8253 cep_standard.debug('CE_VALIDATE_MISC_ACCT_HLDR_ALT - COUNTRY_NAME: '|| COUNTRY_NAME);
8254 END IF;
8255
8256 -- Initialize message list if p_init_msg_list is set to TRUE.
8257 IF FND_API.to_Boolean(p_init_msg_list) THEN
8258 FND_MSG_PUB.initialize;
8259 END IF;
8260
8261 IF l_DEBUG in ('Y', 'C') THEN
8262 cep_standard.debug('CE_VALIDATE_MISC_ACCT_HLDR_ALT: P_INIT_MSG_LIST: '|| P_INIT_MSG_LIST);
8263 END IF;
8264
8265
8266 ACCOUNT_HOLDER_ALT := X_ACCOUNT_HOLDER_ALT;
8267
8268
8269 IF (COUNTRY_NAME = 'JP')
8270 THEN
8271
8272 IF (ACCOUNT_HOLDER_ALT is null and X_ACCOUNT_CLASSIFICATION = 'INTERNAL') then
8273 fail_mandatory;
8274
8275 END IF; -- end of mandatory check
8276 ELSE -- other countries pass_check
8277 pass_check;
8278 END IF; -- end of country_name
8279
8280
8281 FND_MSG_PUB.Count_And_Get(
8282 p_encoded => FND_API.G_FALSE,
8283 p_count => x_msg_count,
8284 p_data => x_msg_data);
8285
8286 IF x_msg_count > 0 THEN
8287 x_return_status := fnd_api.g_ret_sts_error;
8288 END IF;
8289
8290 IF l_DEBUG in ('Y', 'C') THEN
8291 cep_standard.debug('CE_VALIDATE_BANKINFO.ce_validate_misc_acct_hldr_alt - P_COUNT: '|| x_msg_count||
8292 --'P_DATA: '|| x_msg_data||
8293 --' X_VALUE_OUT: '|| X_VALUE_OUT||
8294 '<<CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT');
8295 END IF;
8296
8297 EXCEPTION
8298 WHEN OTHERS THEN
8299 IF l_DEBUG in ('Y', 'C') THEN
8300 cep_standard.debug('CE_VALIDATE_BANKINFO.CE_VALIDATE_MISC_ACCT_HLDR_ALT ' ||X_COUNTRY_NAME );
8301 END IF;
8302
8303 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
8304 fnd_message.set_token('PROCEDURE', 'CE_VALIDATE_BANKINFO.cd_validate_misc_acct_hldr_alt');
8305 fnd_msg_pub.add;
8306 RAISE;
8307
8308 END CE_VALIDATE_MISC_ACCT_HLDR_ALT;
8309
8310 END CE_VALIDATE_BANKINFO_UPG;