1 PACKAGE BODY it_bank_details_pkg AS
2 -- $Header: peitbank.pkb 120.2.12010000.2 2008/09/17 07:16:46 rbabla ship $
3 --
4 --
5 -- Validates the bank account number.
6 --
7 -- The format is as follows CIN-ABI-CAB-Acc where
8 --
9 -- CIN = check digit
10 -- ABI = 5 digits representing the bank
11 -- CAB = 5 digits representing the branch
12 -- Acc = up to 12 characters representing the account no
13 --
14 FUNCTION validate_iban_acc
15 ( p_account_number IN varchar2
16 ) RETURN NUMBER IS
17
18 l_max_acc_length NUMBER := 34;
19 l_account_length number;
20 l_val_digit NUMBER := 9;
21 l_acc_in_new_format varchar2(34);
22 l_trans_acc varchar2(70);
23 l_trans_acc_num number;
24 l_trnc_acc_mod_97 number;
25 l_regen_acc number;
26 BEGIN
27 l_account_length := LENGTH(p_account_number);
28 -- Validate the length of the account
29 IF l_account_length > l_max_acc_length THEN
30 RETURN 1;
31 END IF;
32 --Validate the lenght of IT IBAN account
33 IF substr(p_account_number,1,2)='IT' AND l_account_length <> 27 THEN
34 RETURN 1;
35 END IF;
36 --- validating the format OF the account
37 IF TRANSLATE(p_account_number, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
38 '999999999999999999999999999999999999')
39 <> RPAD(l_val_digit, l_account_length, '9') THEN
40 RETURN 1;
41 END IF;
42
43 --- validating first 2 digits of account
44
45 IF TRANSLATE(substr(p_account_number, 1, 2), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
46 '99999999999999999999999999')
47 <> '99' THEN
48 RETURN 1;
49 END IF;
50
51 --- Validating the 3 and 4 digit for Iban
52
53 IF substr(p_account_number, 3, 1) NOT IN(0,1,2,3,4,5,6,7,8,9) THEN
54 RETURN 1;
55
56 ELSIF substr(p_account_number, 4, 1) NOT IN(0,1,2,3,4,5,6,7,8,9) THEN
57 RETURN 1;
58 END IF;
59 --- new format the forst 4 digit placed at the end
60 l_acc_in_new_format := SUBSTR(p_account_number, 5, l_account_length) || SUBSTR(p_account_number, 1, 4);
61
62 l_trans_acc := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
63 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
64 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
65 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(l_acc_in_new_format,
66 'A', '10'), 'B', '11'), 'C', '12'), 'D', '13'), 'E', '14'),
67 'F', '15'), 'G', '16'), 'H', '17'),'I', '18'), 'J', '19'),
68 'K', '20'), 'L', '21'), 'M', '22'), 'N', '23'), 'O', '24'),
69 'P', '25'), 'Q', '26'), 'R', '27'), 'S', '28'), 'T', '29'),
70 'U', '30'), 'V', '31'), 'W', '32'), 'X', '33'), 'Y', '34'),
71 'Z', '35');
72
73 begin
74 l_trans_acc_num := TO_NUMBER(l_trans_acc);
75
76 exception
77 WHEN OTHERS then
78 RETURN 1;
79 END;
80
81 l_trnc_acc_mod_97 := trunc((l_trans_acc_num-1)/97);
82 l_regen_acc := l_trnc_acc_mod_97 * 97;
83 --
84 -- IF l_mod <> 1 THEN
85 IF l_regen_acc <> l_trans_acc_num-1 then
86 RETURN 1;
87 END IF;
88
89 RETURN 0;
90 END validate_iban_acc;
91
92
93 FUNCTION validate_non_iban_acc
94 (p_account_number IN VARCHAR2) RETURN NUMBER IS
95 --
96 TYPE OddTransform IS TABLE OF NUMBER;
97 TYPE EvenTransform IS TABLE OF NUMBER;
98 TYPE TransAlgorithm IS TABLE OF VARCHAR2(1);
99 --
100 oddtable OddTransform := OddTransform(1,0,5,7,9,13,15,17,19,21,2,4,18,20,11,3,6,8,
101 12,14,16,10,22,25,24,23,27,28,26);
102 eventable EvenTransform := EvenTransform(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,
103 17,18,19,20,21,22,23,24,25,26,27,28);
104 transtable TransAlgorithm := TransAlgorithm('A','B','C','D','E','F','G','H','I','J',
105 'K','L','M','N','O','P','Q','R','S','T',
106 'U','V','W','X','Y','Z','-','.',' ');
107 --
108 check_digit VARCHAR2(1);
109 new_account_number VARCHAR2(23);
110 new_account_length NUMBER;
111 acc_pos NUMBER;
112 acc_char VARCHAR2(1);
113 convert_value NUMBER;
114 convert_total NUMBER;
115 trans_pos NUMBER;
116 calc_rem NUMBER;
117 calc_check_digit VARCHAR2(1);
118 BEGIN
119 --
120 --
121 -- Minimum account no length is 15 characters.
122 --
123 IF LENGTH(p_account_number) < 15 THEN
124 RETURN 1;
125 END IF;
126 --
127 --
128 -- Check separators exist at the correct places within the account no.
129 --
130 IF SUBSTR(p_account_number,2,1) <> '-' THEN
131 RETURN 1;
132 END IF;
133 IF SUBSTR(p_account_number,8,1) <> '-' THEN
134 RETURN 1;
135 END IF;
136 IF SUBSTR(p_account_number,14,1) <> '-' THEN
137 RETURN 1;
138 END IF;
139 --
140 --
141 -- Ensure the ABI consists only of digits.
142 --
143 IF SUBSTR(p_account_number,3,1) < '0' OR SUBSTR(p_account_number,3,1) > '9' or
144 SUBSTR(p_account_number,4,1) < '0' OR SUBSTR(p_account_number,4,1) > '9' or
145 SUBSTR(p_account_number,5,1) < '0' OR SUBSTR(p_account_number,5,1) > '9' or
146 SUBSTR(p_account_number,6,1) < '0' OR SUBSTR(p_account_number,6,1) > '9' or
147 SUBSTR(p_account_number,7,1) < '0' OR SUBSTR(p_account_number,7,1) > '9' THEN
148 RETURN 1;
149 END IF;
150 --
151 --
152 -- Ensure the CAB consists only of digits.
153 --
154 IF SUBSTR(p_account_number,9,1) < '0' OR SUBSTR(p_account_number,9,1) > '9' or
155 SUBSTR(p_account_number,10,1) < '0' OR SUBSTR(p_account_number,10,1) > '9' or
156 SUBSTR(p_account_number,11,1) < '0' OR SUBSTR(p_account_number,11,1) > '9' or
157 SUBSTR(p_account_number,12,1) < '0' OR SUBSTR(p_account_number,12,1) > '9' or
158 SUBSTR(p_account_number,13,1) < '0' OR SUBSTR(p_account_number,13,1) > '9' THEN
159 RETURN 1;
160 END IF;
161 --
162 --
163 -- CIN must be a letter from A to Z.
164 --
165 check_digit := SUBSTR(p_account_number,1,1);
166 IF check_digit < 'A' OR check_digit > 'Z' THEN
167 RETURN 1;
168 END IF;
169 --
170 --
171 -- Remove the separators and re-combine the account no.
172 --
173 new_account_number := SUBSTR(p_account_number,3,5)||
174 SUBSTR(p_account_number,9,5)||
175 SUBSTR(p_account_number,15,12);
176 --
177 --
178 -- Transform each character in the account to a value based on its position i.e. odd or even using
179 -- separate odd and even transforms. Total them all up into a single figure. Divide this by 26 and
180 -- then use the remainder in a further transform to derive the check digit.
181 --
182 new_account_length := length(new_account_number);
183 convert_total := 0;
184 for acc_pos in 1..22
185 LOOP
186 IF acc_pos > new_account_length THEN
187 acc_char := ' ';
188 ELSE
189 acc_char := SUBSTR(new_account_number,acc_pos,1);
190 END IF;
191 IF (acc_pos MOD 2) > 0 THEN
192 IF acc_char < '0' OR acc_char > '9' THEN
193 trans_pos := 0;
194 LOOP
195 trans_pos := trans_pos + 1;
196 IF trans_pos > 29 THEN
197 RETURN 1;
198 EXIT;
199 END IF;
200 IF transtable(trans_pos) = acc_char THEN
201 convert_value := oddtable(trans_pos);
202 EXIT;
203 END IF;
204 END LOOP;
205 ELSE
206 convert_value := oddtable(TO_NUMBER(acc_char)+1);
207 END IF;
208 ELSE
209 IF acc_char < '0' OR acc_char > '9' THEN
210 trans_pos := 0;
211 LOOP
212 trans_pos := trans_pos + 1;
213 IF trans_pos > 29 THEN
214 RETURN 1;
215 EXIT;
216 END IF;
217 IF transtable(trans_pos) = acc_char THEN
218 convert_value := eventable(trans_pos);
219 EXIT;
220 END IF;
221 END LOOP;
222 ELSE
223 convert_value := eventable(TO_NUMBER(acc_char)+1);
224 END IF;
225 END IF;
226 convert_total := convert_total + convert_value;
227 END LOOP;
228 calc_rem := convert_total MOD 26;
229 calc_check_digit := transtable(calc_rem + 1);
230 IF calc_check_digit <> check_digit THEN
231 RETURN 1;
232 END IF;
233 RETURN 0;
234 END validate_non_iban_acc;
235
236
237 FUNCTION validate_account_number
238 ( p_account_number IN VARCHAR2
239 , p_is_iban_acc IN varchar2) RETURN NUMBER IS
240 --
241 l_ret NUMBER ;
242 begin
243 -- hr_utility.trace_on(null,'ITACCVAL');
244 l_ret :=0;
245 hr_utility.set_location('p_is_iban_acc ' || p_is_iban_acc,1);
246 hr_utility.set_location('p_account_number ' || p_account_number,1);
247
248 IF (p_account_number IS NOT NULL AND p_is_iban_acc = 'N') then
249 l_ret := validate_non_iban_acc(p_account_number);
250 hr_utility.set_location('l_ret ' || l_ret,1);
251 RETURN l_ret;
252 ELSIF (p_account_number IS NOT NULL AND p_is_iban_acc = 'Y') then
253 l_ret := validate_iban_acc(p_account_number);
254 hr_utility.set_location('l_ret ' || l_ret,3);
255 RETURN l_ret;
256 ELSIF (p_account_number IS NULL AND p_is_iban_acc IS NULL) then
257 hr_utility.set_location('Both Account Nos Null',4);
258 RETURN 1;
259 ELSE
260 hr_utility.set_location('l_ret: 3 ' ,5);
261 RETURN 3;
262 /*Changed for Bug 7028494 as changes done in default value of segments so that cross
263 validation rules are fired properly in self service pages */
264 END if;
265
266
267 END validate_account_number;
268
269
270 END it_bank_details_pkg;
[Home] [Help]