1 PACKAGE BODY nl_bank_details_pkg AS
2 -- $Header: penlbank.pkb 115.8 2002/04/17 03:52:03 pkm ship $
3 --
4 -- Validates the bank account number
5 --
6 FUNCTION validate_account_number
7 ( accno IN VARCHAR2) RETURN NUMBER IS
8
9 a number := 0;
10 b number := 0;
11 c number := 0;
12 d number := 0;
13 e number := 0;
14 f number := 0;
15 g number := 0;
16 h number := 0;
17 i number := 0;
18 j number := 0;
19 ii varchar2(1);
20 a_total number := 0;
21 a_rem number := 0;
22 a_diff number := 0;
23 acno number := 0;
24 begin
25 -- Check for length of the account number
26 -- If Account number starts with 'P' followed by up to 8
27 -- numbers, then account is valid, no validations are
28 -- performed.
29
30 if length(accno) >1 then
31 ii := substr(accno,1,1);
32 else
33 ii := '';
34 end if;
35
36 if length(accno) >1 and length(accno) <= 9 and upper(ii) = 'P' then
37 acno := to_number(substr(accno,2));
38 a_rem := length(substr(accno,2));
39 if (a_rem > 8) or (acno = 0) or
40 (substr(accno,2,1) = ' ') then
41 return 1;
42 else
43 return 0;
44 end if;
45
46 -- If Account Number is nine characters long, perform the weighted
47 -- average calculations.
48
49 elsif length(accno) = 9 then
50 acno := to_number(accno);
51 a := to_number(substr(acno,1,1));
52 b := to_number(substr(acno,2,1));
53 c := to_number(substr(acno,3,1));
54 d := to_number(substr(acno,4,1));
55 e := to_number(substr(acno,5,1));
56 f := to_number(substr(acno,6,1));
57 g := to_number(substr(acno,7,1));
58 h := to_number(substr(acno,8,1));
59 i := to_number(substr(acno,9,1));
60 a_total := (a*9) + (b*8) + (c*7) + (d*6) + (e*5) + (f*4) + (g*3) + (h*2);
61 a_rem := (a_total - 11*floor(a_total/11));
62 if a_rem = 0 then
63 a_diff := 0;
64 else
65 a_diff := 11 - a_rem;
66 end if;
67 if a_diff = i then
68 return 0;
69 else
70 return 1;
71 end if;
72
73 -- If Account Number is ten characters long
74
75 elsif length(accno) = 10 then
76 --acno := to_number(accno);
77 a := to_number(substr(accno,1,1));
78 b := to_number(substr(accno,2,1));
79 c := to_number(substr(accno,3,1));
80 d := to_number(substr(accno,4,1));
81 e := to_number(substr(accno,5,1));
82 f := to_number(substr(accno,6,1));
83 g := to_number(substr(accno,7,1));
84 h := to_number(substr(accno,8,1));
85 i := to_number(substr(accno,9,1));
86 j := to_number(substr(accno,10,1));
87 a_total := (a*10) + (b*9) + (c*8) + (d*7) + (e*6) + (f*5) + (g*4) + (h*3) + (i*2);
88 a_rem := (a_total - 11*floor(a_total/11));
89 if a_rem = 0 then
90 a_diff := 0;
91 else
92 a_diff := 11 - a_rem;
93 end if;
94 if a_diff = j then
95 return 0;
96 else
97 return 1;
98 end if;
99
100 else
101 return 1;
102 end if;
103
104 EXCEPTION
105 when INVALID_NUMBER then
106 return 1;
107 when VALUE_ERROR then
108 return 1;
109
110 end validate_account_number;
111
112 end nl_bank_details_pkg;