[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_CDV
Source
1 package body py_za_cdv as
2 /* $Header: pyzacdv1.pkb 120.4 2011/12/08 05:17:31 nchinnam ship $ */
3
4 function common_validation
5 (
6 x_branch_code in varchar2,
7 x_account_number in varchar2,
8 x_account_type in number
9 ) return varchar2 as
10
11 cursor c1 is
12 select *
13 from pay_za_bank_address_v
14 where branch_code = x_branch_code
15 and account_type = x_account_type;
16
17 bank_record c1%rowtype;
18 acct_prod number;
19 acct_mod number;
20 comp_number varchar2(16);
21 wsum number;
22 x_acct_is_valid varchar2(5);
23 check_account_number varchar2(11);
24 f_acct_1 number;
25 f_acct_2 number;
26
27 begin
28
29 open c1;
30 fetch c1 into bank_record;
31
32 if c1%notfound then
33 x_acct_is_valid := 'FALSE';
34 return x_acct_is_valid;
35 end if;
36
37 if bank_record.stream_code >= 50 and bank_record.stream_code <= 99 then
38
39 -- Branch is not computerised
40 if bank_record.account_indicator in (0, 1, 2) then
41 x_acct_is_valid := 'TRUE';
42 return x_acct_is_valid;
43 -- CDV not possible
44 end if;
45
46 else
47
48 if bank_record.account_indicator in (0) then
49 x_acct_is_valid := 'TRUE';
50 return x_acct_is_valid;
51 -- CDV not possible
52 end if;
53
54 end if;
55
56 -- Getting this far means that the account number is check digit verifiable.
57 check_account_number := lpad(x_account_number, 11, 0);
58
59 acct_prod := nvl(to_number(substr(check_account_number, 1, 1)), 0) * nvl(bank_record.cdv_weighting1, 0) +
60 nvl(to_number(substr(check_account_number, 2, 1)), 0) * nvl(bank_record.cdv_weighting2, 0) +
61 nvl(to_number(substr(check_account_number, 3, 1)), 0) * nvl(bank_record.cdv_weighting3, 0) +
62 nvl(to_number(substr(check_account_number, 4, 1)), 0) * nvl(bank_record.cdv_weighting4, 0) +
63 nvl(to_number(substr(check_account_number, 5, 1)), 0) * nvl(bank_record.cdv_weighting5, 0) +
64 nvl(to_number(substr(check_account_number, 6, 1)), 0) * nvl(bank_record.cdv_weighting6, 0) +
65 nvl(to_number(substr(check_account_number, 7, 1)), 0) * nvl(bank_record.cdv_weighting7, 0) +
66 nvl(to_number(substr(check_account_number, 8, 1)), 0) * nvl(bank_record.cdv_weighting8, 0) +
70 bank_record.fudge_factor;
67 nvl(to_number(substr(check_account_number, 9, 1)), 0) * nvl(bank_record.cdv_weighting9, 0) +
68 nvl(to_number(substr(check_account_number, 10, 1)), 0) * nvl(bank_record.cdv_weighting10, 0) +
69 nvl(to_number(substr(check_account_number, 11, 1)), 0) * nvl(bank_record.cdv_weighting11, 0) +
71
72 acct_mod := nvl(mod(acct_prod, bank_record.modulus), 0);
73
74 if acct_mod = 0 then
75 -- Acct valid. Passed CDV. No exception processing required.
76 x_acct_is_valid := 'TRUE';
77 return x_acct_is_valid;
78 end if;
79
80 -- Exception Code (a) - First National Bank (Current Accounts)
81 if bank_record.exception_code = 'A' then
82
83 begin
84
85 if length(x_account_number) < 11 then
86
87 comp_number := x_branch_code || x_account_number;
88
89 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
90 nvl(to_number(substr(comp_number, 2, 1)), 0) * 2 +
91 nvl(to_number(substr(comp_number, 3, 1)), 0) * 1 +
92 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
93 nvl(to_number(substr(comp_number, 5, 1)), 0) * 1 +
94 nvl(to_number(substr(comp_number, 6, 1)), 0) * 2 +
95 nvl(to_number(substr(comp_number, 7, 1)), 0) * 1 +
96 nvl(to_number(substr(comp_number, 8, 1)), 0) * 2 +
97 nvl(to_number(substr(comp_number, 9, 1)), 0) * 1 +
98 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
99 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 +
100 nvl(to_number(substr(comp_number, 12, 1)), 0) * 2 +
101 nvl(to_number(substr(comp_number, 13, 1)), 0) * 1 +
102 nvl(to_number(substr(comp_number, 14, 1)), 0) * 2 +
103 nvl(to_number(substr(comp_number, 15, 1)), 0) * 1 +
104 nvl(to_number(substr(comp_number, 16, 1)), 0) * 2;
105
106 acct_mod := nvl(mod(wsum, 10), 0);
107
108 if substr(x_account_number, 1, 1) = '1' and acct_mod = 1 then
109 x_acct_is_valid := 'TRUE';
110 return x_acct_is_valid;
111 elsif acct_mod = 0 then
112 x_acct_is_valid := 'TRUE';
113 return x_acct_is_valid;
114 end if;
115
116 end if;
117
118 if bank_record.exception_code = 'A' and acct_mod <> 0 then
119
120 if length(x_account_number) = 11 then
121
122 comp_number := x_account_number;
123
124 elsif length(x_account_number) = 13 then
125
126 comp_number := substr(x_account_number, 0, 1) || substr(x_account_number, 4, 13);
127
128 end if;
129
130 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
131 nvl(to_number(substr(comp_number, 2, 1)), 0) * 2 +
132 nvl(to_number(substr(comp_number, 3, 1)), 0) * 1 +
133 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
134 nvl(to_number(substr(comp_number, 5, 1)), 0) * 1 +
135 nvl(to_number(substr(comp_number, 6, 1)), 0) * 2 +
136 nvl(to_number(substr(comp_number, 7, 1)), 0) * 1 +
137 nvl(to_number(substr(comp_number, 8, 1)), 0) * 2 +
138 nvl(to_number(substr(comp_number, 9, 1)), 0) * 1 +
139 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
140 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
141
142 acct_mod := nvl(mod(wsum, 10), 0);
143
144 if acct_mod = 0 then
145 x_acct_is_valid := 'TRUE';
146 return x_acct_is_valid;
147 else
148 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 13 +
149 nvl(to_number(substr(comp_number, 2, 1)), 0) * 10 +
150 nvl(to_number(substr(comp_number, 3, 1)), 0) * 9 +
151 nvl(to_number(substr(comp_number, 4, 1)), 0) * 8 +
152 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
153 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
154 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
155 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
156 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
157 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
158 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
159 end if;
160
161 acct_mod := nvl(mod(wsum, 11), 0);
162
163 if acct_mod = 0 then
164 x_acct_is_valid := 'TRUE';
165 return x_acct_is_valid;
166 else
167 x_acct_is_valid := 'FALSE';
168 return x_acct_is_valid;
169 end if;
170
171 end if;
172
173 end;
174
175 -- Exception Code (b) - Cape of Good Hope Bank, Banque Indosuez, Bank of Athens, Mercantile Bank
176 elsif bank_record.exception_code = 'B' then
177
178 begin
179
180 if (substr(x_account_number, length(x_account_number), 1) = '0' or
181 substr(x_account_number, length(x_account_number), 1) = '1') and
185 return x_acct_is_valid;
182 acct_mod = 1 then
183
184 x_acct_is_valid := 'TRUE';
186
187 else
188
189 x_acct_is_valid := 'FALSE';
190 return x_acct_is_valid;
191
192 end if;
193
194 end;
195
196 -- Exception Code (c) - First National Bank (Savings Accounts)
197 elsif bank_record.exception_code = 'C' then
198
199 begin
200
201 if length(x_account_number) = 11 and acct_mod <> 0 then
202
203 comp_number := x_account_number;
204 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
205 nvl(to_number(substr(comp_number, 2, 1)), 0) * 2 +
206 nvl(to_number(substr(comp_number, 3, 1)), 0) * 1 +
207 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
208 nvl(to_number(substr(comp_number, 5, 1)), 0) * 1 +
209 nvl(to_number(substr(comp_number, 6, 1)), 0) * 2 +
210 nvl(to_number(substr(comp_number, 7, 1)), 0) * 1 +
211 nvl(to_number(substr(comp_number, 8, 1)), 0) * 2 +
212 nvl(to_number(substr(comp_number, 9, 1)), 0) * 1 +
213 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
214 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
215
216 acct_mod := nvl(mod(wsum, 10), 0);
217
218 end if;
219
220 if acct_mod = 0 then
221 x_acct_is_valid := 'TRUE';
222 return x_acct_is_valid;
223 else
224 x_acct_is_valid := 'FALSE';
225 return x_acct_is_valid;
226 end if;
227
228 end;
229
230 -- Exception Code (d) - Bank of Taiwan
231 elsif bank_record.exception_code = 'D' then
232
233 begin
234
235 if (to_number(substr(x_account_number, length(x_account_number), 1)) = acct_mod) or
236 (acct_mod = 10 and substr(x_account_number, length(x_account_number), 1) = '0') then
237
238 x_acct_is_valid := 'TRUE';
239 return x_acct_is_valid;
240
241 else
242
243 x_acct_is_valid := 'FALSE';
244 return x_acct_is_valid;
245
246 end if;
247
248 end;
249
250 -- Exception Code (e) - Boland Bank
251 elsif bank_record.exception_code = 'E' then
252
253 begin
254
255 if (to_number(substr(x_account_number, length(x_account_number) - 1, 1) ||
256 substr(x_account_number, length(x_account_number), 1)) > 0)
257 and (to_number(substr(x_account_number, length(x_account_number) - 10, 1)) = 0 and
258 to_number(substr(x_account_number, length(x_account_number) - 9, 1)) > 0) then
259
260 x_acct_is_valid := 'TRUE';
261 return x_acct_is_valid;
262
263 else
264
265 x_acct_is_valid := 'FALSE';
266 return x_acct_is_valid;
267
268 end if;
269
270 end;
271
272 -- Exception Code (f) - United Bank
273 elsif bank_record.exception_code = 'F' then
274
275 begin
276
277 -- ABSA 1
278 comp_number := lpad(x_account_number, 11, 0);
279
280 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
281 nvl(to_number(substr(comp_number, 2, 1)), 0) * 7 +
282 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
283 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
284 nvl(to_number(substr(comp_number, 5, 1)), 0) * 9 +
285 nvl(to_number(substr(comp_number, 6, 1)), 0) * 8 +
286 nvl(to_number(substr(comp_number, 7, 1)), 0) * 7 +
287 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
288 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
289 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
290 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
291
292 acct_mod := nvl(mod(wsum, 10), 0);
293 if acct_mod = 0 then
294 x_acct_is_valid := 'TRUE';
295 return x_acct_is_valid;
296 end if;
297
298 -- ABSA 2
299 comp_number := lpad(x_account_number, 11, 0);
300
301 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
302 nvl(to_number(substr(comp_number, 2, 1)), 0) * 4 +
303 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
304 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
305 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
306 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
307 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
308 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
309 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
310 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
311 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
312
313 acct_mod := nvl(mod(wsum, 11), 0);
314 if acct_mod = 0 then
315 x_acct_is_valid := 'TRUE';
316 return x_acct_is_valid;
317 end if;
318
319 -- ABSA 3
323 nvl(to_number(substr(comp_number, 2, 1)), 0) * 4 +
320 comp_number := lpad(x_account_number, 11, 0);
321
322 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 5 +
324 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
325 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
326 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
327 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
328 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
329 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
330 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
331 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
332 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
333
334 acct_mod := nvl(mod(wsum, 11), 0);
335 if acct_mod = 0 then
336 x_acct_is_valid := 'TRUE';
337 return x_acct_is_valid;
338 end if;
339
340 if length(x_account_number) in (10, 11) then
341
342 if substr(comp_number, 11, 1) in (0, 1) and acct_mod = 1 then
343 x_acct_is_valid := 'TRUE';
344 return x_acct_is_valid;
345 end if;
346
347 end if;
348
349 -- ABSA 4
350 comp_number := lpad(x_account_number, 11, 0);
351
352 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
353 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
354 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
355 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
356 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
357 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
358 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
359 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
360 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
361 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
362 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
363
364 acct_mod := nvl(mod(wsum, 11), 0);
365 if acct_mod = 0 then
366 x_acct_is_valid := 'TRUE';
367 return x_acct_is_valid;
368 end if;
369
370 if length(x_account_number) < 10 then
371
372 f_acct_1 := substr(x_account_number, 1, (length(x_account_number) - 1));
373 f_acct_2 := (substr(x_account_number, length(x_account_number), 1) + 6);
374 if f_acct_2 > 9 then
375 f_acct_2 := substr((substr(x_account_number, length(x_account_number), 1) + 6), 2, 1);
376 else
377 f_acct_2 := substr((substr(x_account_number, length(x_account_number), 1) + 6), 1, 1);
378 end if;
379
380 comp_number := lpad(f_acct_1 || f_acct_2, 11, 0);
381
382 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
383 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
384 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
385 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
386 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
387 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
388 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
389 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
390 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
391 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
392 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
393
394 acct_mod := nvl(mod(wsum, 11), 0);
395 if acct_mod = 0 then
396 x_acct_is_valid := 'TRUE';
397 return x_acct_is_valid;
398 end if;
399
400 end if;
401
402 -- ABSA 5
403 comp_number := lpad(x_account_number, 11, 0);
404
405 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
406 nvl(to_number(substr(comp_number, 2, 1)), 0) * 4 +
407 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
408 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
409 nvl(to_number(substr(comp_number, 5, 1)), 0) * 9 +
410 nvl(to_number(substr(comp_number, 6, 1)), 0) * 8 +
411 nvl(to_number(substr(comp_number, 7, 1)), 0) * 7 +
412 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
413 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
414 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
415 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
416
417 acct_mod := nvl(mod(wsum, 10), 0);
418 if acct_mod = 0 then
419 x_acct_is_valid := 'TRUE';
420 return x_acct_is_valid;
421 end if;
422
423 /*
424 if length(x_account_number) < 10 then
425
426 f_acct_1 := substr(x_account_number, 1, (length(x_account_number) - 1));
427 f_acct_2 := substr((substr(x_account_number, length(x_account_number), 1) + 6), 2, 1);
428 comp_number := lpad(f_acct_1 || f_acct_2, 11, 0);
429
430 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
431 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
432 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
433 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
437 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
434 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
435 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
436 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
438 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
439 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
440 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
441
442 acct_mod := nvl(mod(wsum, 11), 0);
443
444 if acct_mod = 0 then
445
446 x_acct_is_valid := 'TRUE';
447 return x_acct_is_valid;
448
449 else
450
451 wsum := nvl(to_number(substr(check_account_number, 1, 1)), 0) * 1 +
452 nvl(to_number(substr(check_account_number, 2, 1)), 0) * 4 +
453 nvl(to_number(substr(check_account_number, 3, 1)), 0) * 3 +
454 nvl(to_number(substr(check_account_number, 4, 1)), 0) * 2 +
455 nvl(to_number(substr(check_account_number, 5, 1)), 0) * 9 +
456 nvl(to_number(substr(check_account_number, 6, 1)), 0) * 8 +
457 nvl(to_number(substr(check_account_number, 7, 1)), 0) * 7 +
458 nvl(to_number(substr(check_account_number, 8, 1)), 0) * 4 +
459 nvl(to_number(substr(check_account_number, 9, 1)), 0) * 3 +
460 nvl(to_number(substr(check_account_number, 10, 1)), 0) * 2 +
461 nvl(to_number(substr(check_account_number, 11, 1)), 0) * 1 + 0;
462
463 acct_mod := nvl(mod(wsum, 10), 0);
464
465 if acct_mod = 0 then
466 x_acct_is_valid := 'TRUE';
467 return x_acct_is_valid;
468 else
469 x_acct_is_valid := 'FALSE';
470 return x_acct_is_valid;
471 end if;
472
473 end if;
474
475 elsif length(x_account_number) = 10 then
476
477 comp_number := lpad(x_account_number, 11, 0);
478
479 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
480 nvl(to_number(substr(comp_number, 2, 1)), 0) * 7 +
481 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
482 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
483 nvl(to_number(substr(comp_number, 5, 1)), 0) * 9 +
484 nvl(to_number(substr(comp_number, 6, 1)), 0) * 8 +
485 nvl(to_number(substr(comp_number, 7, 1)), 0) * 7 +
486 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
487 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
488 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
489 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
490
491 acct_mod := nvl(mod(wsum, 10), 0);
492 if acct_mod = 0 then
493 x_acct_is_valid := 'TRUE';
494 return x_acct_is_valid;
495 elsif substr(comp_number, 11, 1) in (0, 1) and acct_mod = 1 then
496 x_acct_is_valid := 'TRUE';
497 end if;
498
499 end if;
500
501 comp_number := lpad(x_account_number,11,0);
502
503 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
504 nvl(to_number(substr(comp_number, 2, 1)), 0) * 4 +
505 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
506 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
507 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
508 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
509 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
510 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
511 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
512 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
513 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
514
515 acct_mod := nvl(mod(wsum, 11), 0);
516 if acct_mod = 0 then
517 x_acct_is_valid := 'TRUE';
518 return x_acct_is_valid;
519 elsif substr(comp_number, 11, 1) in (0, 1) and acct_mod = 1 then
520 x_acct_is_valid := 'TRUE';
521 return x_acct_is_valid;
522 end if;
523
524 comp_number := lpad(x_account_number, 11, 0);
525
526 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 5 +
527 nvl(to_number(substr(comp_number, 2, 1)), 0) * 4 +
528 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
529 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
530 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
531 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
532 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
533 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
534 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
535 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
536 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
537
538 acct_mod := nvl(mod(wsum, 11), 0);
539 if acct_mod = 0 then
540 x_acct_is_valid := 'TRUE';
541 return x_acct_is_valid;
542 elsif substr(comp_number, 11, 1) in (0, 1) and acct_mod = 1 then
543 x_acct_is_valid := 'TRUE';
544 return x_acct_is_valid;
545 end if;
546
547 comp_number := lpad(x_account_number, 11, 0);
548
549 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
550 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
551 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
552 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
553 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
554 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
555 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
556 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
557 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
558 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
559 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
560
561 acct_mod := nvl(mod(wsum, 11), 0);
562 if acct_mod = 0 then
563 x_acct_is_valid := 'TRUE';
564 return x_acct_is_valid;
565 elsif substr(comp_number, 11, 1) in (0, 1) and acct_mod = 1 then
566 x_acct_is_valid := 'TRUE';
567 return x_acct_is_valid;
568 end if;
569 */
570
571 end;
572
573 -- Exception Code (g) - Permanent Bank, Peoples Bank
574 elsif bank_record.exception_code = 'G' then
575
576 begin
577
578 comp_number := lpad(substr(x_account_number, 1, 8), 11, 0);
579
580 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
581 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
582 nvl(to_number(substr(comp_number, 3, 1)), 0) * 1 +
583 nvl(to_number(substr(comp_number, 4, 1)), 0) * 29 +
584 nvl(to_number(substr(comp_number, 5, 1)), 0) * 23 +
585 nvl(to_number(substr(comp_number, 6, 1)), 0) * 19 +
586 nvl(to_number(substr(comp_number, 7, 1)), 0) * 17 +
587 nvl(to_number(substr(comp_number, 8, 1)), 0) * 13 +
588 nvl(to_number(substr(comp_number, 9, 1)), 0) * 7 +
589 nvl(to_number(substr(comp_number, 10, 1)), 0) * 3 +
590 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
591
592 acct_mod := nvl(mod(wsum, 11), 0);
593
594 if acct_mod = 0 then
595
596 x_acct_is_valid := 'TRUE';
597 return x_acct_is_valid;
598
599 elsif acct_mod <> 0 and substr(x_account_number, 7, 1) = substr(x_account_number, 8, 1) then
600
601 comp_number := lpad(substr(x_account_number, 1, 8), 11, 0);
602
603 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
604 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
605 nvl(to_number(substr(comp_number, 3, 1)), 0) * 1 +
606 nvl(to_number(substr(comp_number, 4, 1)), 0) * 29 +
607 nvl(to_number(substr(comp_number, 5, 1)), 0) * 23 +
608 nvl(to_number(substr(comp_number, 6, 1)), 0) * 19 +
609 nvl(to_number(substr(comp_number, 7, 1)), 0) * 17 +
610 nvl(to_number(substr(comp_number, 8, 1)), 0) * 13 +
611 nvl(to_number(substr(comp_number, 9, 1)), 0) * 7 +
612 nvl(to_number(substr(comp_number, 10, 1)), 0) * 3 +
613 nvl(to_number(substr(comp_number, 11, 1)), 0) * 0 + 10;
614
615 acct_mod := nvl(mod(wsum, 11), 0);
616 if acct_mod = 0 then
617 x_acct_is_valid := 'TRUE';
618 return x_acct_is_valid;
619 else
620 x_acct_is_valid := 'FALSE';
621 return x_acct_is_valid;
622 end if;
623
624 else
625
626 x_acct_is_valid := 'FALSE';
627 return x_acct_is_valid;
628
629 end if;
630
631 end;
632
633 -- Exception Code (h) - Nedbank Bond Accounts
634 elsif bank_record.exception_code = 'H' then
635
636 begin
637
638 comp_number := lpad(substr(x_account_number,1,8),11,0);
639 wsum := nvl(to_number(substr(comp_number,1,1)),0)* 1 +
640 nvl(to_number(substr(comp_number,2,1)),0)* 1 +
641 nvl(to_number(substr(comp_number,3,1)),0)* 1 +
642 nvl(to_number(substr(comp_number,4,1)),0)* 29 +
643 nvl(to_number(substr(comp_number,5,1)),0)* 23 +
644 nvl(to_number(substr(comp_number,6,1)),0)* 19 +
645 nvl(to_number(substr(comp_number,7,1)),0)* 17 +
646 nvl(to_number(substr(comp_number,8,1)),0)* 13 +
647 nvl(to_number(substr(comp_number,9,1)),0)* 7 +
648 nvl(to_number(substr(comp_number,10,1)),0)* 3 +
649 nvl(to_number(substr(comp_number,11,1)),0)* 1 + 0;
650
651 acct_mod := nvl(mod(wsum, 11), 0);
652 if acct_mod = 0 then
653
654 x_acct_is_valid := 'TRUE';
655 return x_acct_is_valid;
656
657 elsif acct_mod <> 0 and substr(x_account_number, 7, 1) = substr(x_account_number, 8, 1) then
658
659 comp_number := lpad(substr(x_account_number, 1, 8), 11, 0);
660
661 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
662 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
663 nvl(to_number(substr(comp_number, 3, 1)), 0) * 1 +
664 nvl(to_number(substr(comp_number, 4, 1)), 0) * 29 +
665 nvl(to_number(substr(comp_number, 5, 1)), 0) * 23 +
666 nvl(to_number(substr(comp_number, 6, 1)), 0) * 19 +
667 nvl(to_number(substr(comp_number, 7, 1)), 0) * 17 +
668 nvl(to_number(substr(comp_number, 8, 1)), 0) * 13 +
669 nvl(to_number(substr(comp_number, 9, 1)), 0) * 7 +
670 nvl(to_number(substr(comp_number, 10, 1)), 0) * 3 +
671 nvl(to_number(substr(comp_number, 11, 1)), 0) * 0 + 10;
672
673 acct_mod := nvl(mod(wsum, 11), 0);
674
675 if acct_mod = 0 then
676 x_acct_is_valid := 'TRUE';
677 return x_acct_is_valid;
678 else
679 x_acct_is_valid := 'FALSE';
680 return x_acct_is_valid;
681 end if;
682
683 else
684
685 x_acct_is_valid := 'FALSE';
686 return x_acct_is_valid;
687
688 end if;
689
690 end;
691
692 elsif bank_record.exception_code = 'I' then
693
694 begin
695
696 if length (x_account_number) < 10 then
697
698 comp_number := x_account_number;
699 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
700 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
701 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
702 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
703 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
704 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
705 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
706 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
707 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
708 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
709 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
710
711 acct_mod := nvl(mod(wsum, 11), 0);
712
713 if acct_mod = 0 then
714
715 x_acct_is_valid := 'TRUE';
716 return x_acct_is_valid;
717
718 else
719
720 comp_number := substr(x_account_number, 1, length(x_account_number) - 1) ||
721 substr(to_char(to_number(substr(x_account_number, length(x_account_number), 1)) + 6, 'FM00'), 2, 1);
722
723 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
724 nvl(to_number(substr(comp_number, 2, 1)), 0) * 1 +
725 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
726 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
727 nvl(to_number(substr(comp_number, 5, 1)), 0) * 7 +
728 nvl(to_number(substr(comp_number, 6, 1)), 0) * 6 +
729 nvl(to_number(substr(comp_number, 7, 1)), 0) * 5 +
730 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
731 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
732 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
733 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
734
735 acct_mod := nvl(mod(wsum, 11), 0);
736
737 if acct_mod = 0 then
738 x_acct_is_valid := 'TRUE';
739 return x_acct_is_valid;
740 else
741 x_acct_is_valid := 'FALSE';
742 return x_acct_is_valid;
743 end if;
744
745 end if;
746
747 elsif length(x_account_number) = 10 then
748
749 comp_number := x_account_number;
750
751 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
752 nvl(to_number(substr(comp_number, 2, 1)), 0) * 7 +
753 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
754 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
755 nvl(to_number(substr(comp_number, 5, 1)), 0) * 9 +
756 nvl(to_number(substr(comp_number, 6, 1)), 0) * 8 +
757 nvl(to_number(substr(comp_number, 7, 1)), 0) * 7 +
758 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
759 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
760 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
761 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
762
763 acct_mod := nvl(mod(wsum, 10), 0);
764
765 if acct_mod = 0 then
766 x_acct_is_valid := 'TRUE';
767 return x_acct_is_valid;
768 else
769 x_acct_is_valid := 'FALSE';
770 return x_acct_is_valid;
771 end if;
772
773 else
774
775 x_acct_is_valid := 'FALSE';
776 return x_acct_is_valid;
777
778 end if;
779
780 end;
781
782 -- Exception Code (j) - Allied Bank
783 elsif bank_record.exception_code = 'J' then
784
785 begin
786
787 if (length(x_account_number) = 10 or length(x_account_number) = 11) and
788 acct_mod = 1 and (substr(x_account_number, length(x_account_number) - 1, 1) = '0' or
789 substr(x_account_number, length(x_account_number) - 1, 1) = '1') then
790
791 x_acct_is_valid := 'TRUE';
792 return x_acct_is_valid;
793
794 elsif length(x_account_number) = 10 then
795
796 comp_number := x_account_number;
797
798 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
799 nvl(to_number(substr(comp_number, 2, 1)), 0) * 7 +
800 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
801 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
802 nvl(to_number(substr(comp_number, 5, 1)), 0) * 9 +
803 nvl(to_number(substr(comp_number, 6, 1)), 0) * 8 +
804 nvl(to_number(substr(comp_number, 7, 1)), 0) * 7 +
805 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
806 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
807 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
808 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
809
810 acct_mod := nvl(mod(wsum, 10), 0);
811
812 if acct_mod = 0 then
813 x_acct_is_valid := 'TRUE';
814 return x_acct_is_valid;
815 else
816 x_acct_is_valid := 'FALSE';
817 return x_acct_is_valid;
818 end if;
819
820 end if;
821
822 end;
823
824 -- Exception Code (k) - Volkskas Bank
825 elsif bank_record.exception_code = 'K' then
826
827 begin
828
829 if length(x_account_number) = 10 then
830
831 comp_number := x_account_number;
832
833 wsum := nvl(to_number(substr(comp_number, 1, 1)), 0) * 1 +
834 nvl(to_number(substr(comp_number, 2, 1)), 0) * 7 +
835 nvl(to_number(substr(comp_number, 3, 1)), 0) * 3 +
836 nvl(to_number(substr(comp_number, 4, 1)), 0) * 2 +
837 nvl(to_number(substr(comp_number, 5, 1)), 0) * 9 +
838 nvl(to_number(substr(comp_number, 6, 1)), 0) * 8 +
839 nvl(to_number(substr(comp_number, 7, 1)), 0) * 7 +
840 nvl(to_number(substr(comp_number, 8, 1)), 0) * 4 +
841 nvl(to_number(substr(comp_number, 9, 1)), 0) * 3 +
842 nvl(to_number(substr(comp_number, 10, 1)), 0) * 2 +
843 nvl(to_number(substr(comp_number, 11, 1)), 0) * 1 + 0;
844
845 acct_mod := nvl(mod(wsum, 10), 0);
846
847 if acct_mod = 0 then
848 x_acct_is_valid := 'TRUE';
849 return x_acct_is_valid;
850 else
851 x_acct_is_valid := 'FALSE';
852 return x_acct_is_valid;
853 end if;
854
855 end if;
856
857 end;
858
859 -- Exception Code (m) - Standard Bank of S.A
860 elsif bank_record.exception_code = 'M' then
861 begin
862 IF length(x_account_number) = 11
863 then
864 IF x_branch_code <> '051001' then
865 x_acct_is_valid := 'FALSE';
866 RETURN x_acct_is_valid;
867 ELSE
868 -- Fix for bug#13401581
869 -- IF substr(x_account_number,-1)='1' then
870 IF substr(x_account_number,1,1)='1' then
871 comp_number := x_account_number;
872 wsum := nvl(to_number(substr(comp_number,1,1)),0)* 13 +
873 nvl(to_number(substr(comp_number,2,1)),0)* 12 +
874 nvl(to_number(substr(comp_number,3,1)),0)* 9 +
875 nvl(to_number(substr(comp_number,4,1)),0)* 8 +
876 nvl(to_number(substr(comp_number,5,1)),0)* 7 +
877 nvl(to_number(substr(comp_number,6,1)),0)* 6 +
878 nvl(to_number(substr(comp_number,7,1)),0)* 5 +
879 nvl(to_number(substr(comp_number,8,1)),0)* 4 +
880 nvl(to_number(substr(comp_number,9,1)),0)* 3 +
881 nvl(to_number(substr(comp_number,10,1)),0)* 2 +
882 nvl(to_number(substr(comp_number,11,1)),0)* 1 + 0;
883
884 acct_mod := nvl(mod(wsum, 11), 0);
885 if acct_mod = 0 then
886 x_acct_is_valid := 'TRUE';
887 return x_acct_is_valid;
888 else
889 x_acct_is_valid := 'FALSE';
890 return x_acct_is_valid;
891 END if;
892 END IF;
893 END IF;
894 END IF;
895 end;
896
897 else
898
899 -- Acct invalid. Failed CDV
900 x_acct_is_valid := 'FALSE';
901 return x_acct_is_valid;
902
903 end if;
904
905 if x_acct_is_valid is null then
906 x_acct_is_valid := 'FALSE';
907 return x_acct_is_valid;
908 end if;
909
910 end common_validation;
911
912 end py_za_cdv;