[Home] [Help]
PACKAGE BODY: APPS.IBAN_VALIDATION_PKG
Source
1 PACKAGE BODY IBAN_VALIDATION_PKG AS
2 -- /* $Header: peribanval.pkb 120.0.12010000.1 2009/11/20 11:52:53 dchindar noship $ */
3 --
4 --
5 -- Exceptions
6 hr_application_error exception;
7 pragma exception_init (hr_application_error, -20001);
8
9
10 --******************************************************************************
11 --* Returns the Discription for a lookup code of a IBAN_ACC_LOOKUP type.
12 --******************************************************************************
13 Function get_acc_length ( p_lookup_code VARCHAR2) RETURN VARCHAR2 IS
14 --
15 CURSOR csr_lookup IS
16 SELECT DESCRIPTION
17 FROM hr_lookups
18 WHERE lookup_type = 'IBAN_ACC_LOOKUP'
19 AND lookup_code = p_lookup_code;
20 --
21 v_description varchar2(80) := NULL;
22 --
23 BEGIN
24 --
25 -- Only open the cursor if the parameters are going to retrieve anything
26 --
27 IF p_lookup_code IS NOT NULL THEN
28 --
29 OPEN csr_lookup;
30 FETCH csr_lookup INTO v_description;
31 CLOSE csr_lookup;
32 --
33 END IF ;
34 --
35 RETURN v_description;
36 --
37 END get_acc_length;
38
39
40 --******************************************************************************
41 --* This function will validate the IBAN account number.
42 --******************************************************************************
43
44 FUNCTION validate_iban_acc
45 (
46 p_account_number IN varchar2
47 ) RETURN NUMBER IS
48
49 l_max_acc_length NUMBER := 34;
50 l_account_length NUMBER ;
51 l_val_digit NUMBER := 9;
52 l_acc_in_new_format VARCHAR2 (34);
53 l_trans_acc VARCHAR2 (70);
54 l_trans_acc_num NUMBER ;
55 l_trnc_acc_mod_97 NUMBER ;
56 l_regen_acc NUMBER ;
57 BEGIN
58 l_account_length := LENGTH(p_account_number);
59 -- Validate the length of the account
60 IF l_account_length > l_max_acc_length THEN
61 RETURN 1;
62 END IF;
63
64 --Validate the lenght and 1st 2 characters of IBAN account
65
66 IF NVL(get_acc_length(substr(p_account_number,1,2)), 1) <> l_account_length then
67 RETURN 1;
68 END IF;
69
70
71 --- validating the format OF the account
72 IF TRANSLATE(p_account_number, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
73 '999999999999999999999999999999999999')
74 <> RPAD(l_val_digit, l_account_length, '9') THEN
75 RETURN 1;
76 END IF;
77
78 --- validating first 2 digits of account
79
80 IF TRANSLATE(substr(p_account_number, 1, 2), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
81 '99999999999999999999999999')
82 <> '99' THEN
83 RETURN 1;
84 END IF;
85
86 --- Validating the 3 and 4 digit for Iban
87
88 IF substr(p_account_number, 3, 1) NOT IN(0,1,2,3,4,5,6,7,8,9) THEN
89 RETURN 1;
90
91 ELSIF substr(p_account_number, 4, 1) NOT IN(0,1,2,3,4,5,6,7,8,9) THEN
92 RETURN 1;
93 END IF;
94 --- new format the forst 4 digit placed at the end
95 l_acc_in_new_format := SUBSTR(p_account_number, 5, l_account_length) || SUBSTR(p_account_number, 1, 4);
96
97 l_trans_acc := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
98 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
99 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
100 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(l_acc_in_new_format,
101 'A', '10'), 'B', '11'), 'C', '12'), 'D', '13'), 'E', '14'),
102 'F', '15'), 'G', '16'), 'H', '17'),'I', '18'), 'J', '19'),
103 'K', '20'), 'L', '21'), 'M', '22'), 'N', '23'), 'O', '24'),
104 'P', '25'), 'Q', '26'), 'R', '27'), 'S', '28'), 'T', '29'),
105 'U', '30'), 'V', '31'), 'W', '32'), 'X', '33'), 'Y', '34'),
106 'Z', '35');
107
108 begin
109 l_trans_acc_num := TO_NUMBER(l_trans_acc);
110
111 exception
112 WHEN OTHERS then
113 RETURN 1;
114 END;
115
116 l_trnc_acc_mod_97 := trunc((l_trans_acc_num-1)/97);
117 l_regen_acc := l_trnc_acc_mod_97 * 97;
118 --
119 -- IF l_mod <> 1 THEN
120 IF l_regen_acc <> l_trans_acc_num-1 then
121 RETURN 1;
122 END IF;
123
124 RETURN 0;
125 END validate_iban_acc;
126
127 END IBAN_VALIDATION_PKG;
128