1 PACKAGE BODY hr_be_validation AS
2 /* $Header: hrbevali.pkb 115.1 2003/08/08 02:52:05 atrivedi noship $ */
3 --
4 --
5 -- This function validates that the bank account no is valid (see comments
6 -- within code for details of correct format and check digit calculation).
7 --
8 -- Its primary usage is within the bank account key flexfield definition
9 -- where it is used to drive some cross validation rules.
10 --
11 -- This function returns either:
12 --
13 -- Bank account no is OK - hr_be_validation.success
14 -- Bank account no is invalid - hr_be_validation.failure
15 --
16 FUNCTION bank_account_no
17 (p_bank_acc_no VARCHAR2) RETURN VARCHAR2 AS
18 --
19 --
20 -- Local exceptions.
21 --
22 invalid_bank_acc_no EXCEPTION;
23 --
24 --
25 -- Local variables.
26 --
27 l_temp_val NUMBER;
28 l_check_digits NUMBER;
29 l_10_digits NUMBER;
30 BEGIN
31 --
32 --
33 -- Ensure the format is NN-NNNNNNN-NN
34 --
35 IF INSTR(p_bank_acc_no, 'N') > 0 OR NVL(translate(p_bank_acc_no, '1234567890-', 'NNNNNNNNNN-'), 'ERROR') <> 'NNN-NNNNNNN-NN' THEN
36 RAISE invalid_bank_acc_no;
37 END IF;
38 --
39 --
40 -- Calculate the check digit using the following algorthmn -
41 --
42 -- 1. Take the first 10 digits and divide these by 97 (rounding the result).
43 -- 2. Multiply the result by 97.
44 -- 3. Subtract this number from ther original first 10 digits.
45 -- 4. This is the check digits NB. if < 10 need to front pad with a zero.
46 --
47 l_10_digits := TO_NUMBER(SUBSTR(p_bank_acc_no, 1, 3) || SUBSTR(p_bank_acc_no, 5, 7));
48 l_temp_val := TRUNC(l_10_digits / 97) * 97;
49 l_check_digits := l_10_digits - l_temp_val;
50 --
51 --
52 -- Compare the check digit with the calculated one.
53 --
54 IF LPAD(TO_CHAR(l_check_digits), 2, '0') <> SUBSTR(p_bank_acc_no, 13, 2) and l_check_digits <> 0 THEN
55 RAISE invalid_bank_acc_no;
56 END IF;
57 --
58 IF l_check_digits = 0 and SUBSTR(p_bank_acc_no, 13, 2) <> 97 then
59 RAISE invalid_bank_acc_no;
60 END IF;
61 --
62 -- Bank account no is OK.
63 --
64 RETURN success;
65 EXCEPTION
66 WHEN invalid_bank_acc_no THEN
67 --
68 --
69 -- Bank account no is incorrect.
70 --
71 RETURN failure;
72 END bank_account_no;
73 END hr_be_validation;
[Home] [Help]