Spaces:
Running
Running
File size: 9,048 Bytes
3f0ea4e |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 |
FUNCTION validation (p_acct IN VARCHAR2,
p_id IN OUT VARCHAR2,
p_error_code OUT VARCHAR2,
p_error_desc OUT VARCHAR2)
RETURN BOOLEAN
IS
v_tran_date DATE;
v_acct_internal_key NUMBER;
v_dbtr_name fm_client.client_name%TYPE;
v_client_no rb_acct.client_no%TYPE;
v_id_type fm_client.global_id_type%TYPE;
v_client_type fm_client.client_type%TYPE;
v_country fm_client.country_citizen%TYPE;
v_id_cor VARCHAR2 (15);
v_id_nid VARCHAR2 (15);
v_id VARCHAR2 (12);
v_check_national_id VARCHAR2 (250) := NULL;
v_check_corporation_id VARCHAR2 (250) := NULL;
v_step VARCHAR2 (4);
BEGIN
v_step := '1';
p_error_code := '000000';
v_tran_date := get_run_date;
v_id := p_id;
BEGIN
SELECT internal_key,
a.client_no,
b.client_type,
b.global_id_type,
b.client_name,
b.country_citizen
INTO v_acct_internal_key,
v_client_no,
v_client_type,
v_id_type,
v_dbtr_name,
v_country
FROM rb_acct a, fm_client b
WHERE a.client_no = b.client_no AND acct_no = p_acct;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
SELECT internal_key,
a.client_no,
b.client_type,
b.global_id_type,
b.client_name,
b.country_citizen
INTO v_acct_internal_key,
v_client_no,
v_client_type,
v_id_type,
v_dbtr_name,
v_country
FROM rb_acct a, fm_client b
WHERE a.client_no = b.client_no
AND b.client_no = p_acct
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
p_error_code := '300395'; -- invalid client number;
RETURN FALSE;
END;
END;
v_step := '2';
IF v_id IS NULL OR v_id = ''
THEN
BEGIN
IF v_client_type = '1'
THEN
SELECT NVL (national_id, '0')
INTO v_id_nid
FROM fm_client_indvl
WHERE client_no = v_client_no;
ELSE
SELECT NVL (corporation_id, '0')
INTO v_id_cor
FROM fm_client_corporate
WHERE client_no = v_client_no;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_step := '3';
BEGIN
SELECT NVL (global_id, '0')
INTO v_id
FROM fm_client
WHERE client_no = v_client_no;
IF v_client_type = '1'
THEN
v_id_nid := v_id;
ELSE
v_id_cor := v_id;
END IF;
EXCEPTION
WHEN OTHERS
THEN
p_error_code := '104045';
RETURN FALSE;
END;
END;
v_step := '4';
IF v_client_type = '1'
THEN
IF v_id_type = 'FIN'
OR (v_id_type = 'PPT' AND v_country <> 'IR')
THEN
v_step := '5';
SELECT NVL (global_id, '0')
INTO v_id
FROM fm_client
WHERE client_no = v_client_no;
v_id := SUBSTR (v_id, 1, 15);
ELSE
v_step := '6';
v_check_national_id := validate_national_id (v_id_nid);
IF v_check_national_id IS NULL
OR v_check_national_id <> '829204'
THEN
v_id := v_id_nid;
ELSE
p_error_code := '104045';
RETURN FALSE;
END IF;
END IF;
ELSE
v_step := '7';
v_check_corporation_id := validate_cor_id (v_id_cor);
IF v_check_corporation_id IS NULL
OR v_check_corporation_id <> '829205'
THEN
v_id := v_id_cor;
ELSE
p_error_code := '104045';
RETURN FALSE;
END IF;
END IF;
ELSE
v_step := '8';
--v_id := p_id;
IF v_client_type = '1'
THEN
IF v_id_type = 'FIN'
OR (v_id_type = 'PPT' AND v_country <> 'IR')
THEN
NULL;
ELSE
v_step := '9';
v_check_national_id := validate_national_id (v_id);
IF v_check_national_id = '829204'
THEN
p_error_code := '104045';
p_error_desc :=
error_desc (NVL (p_error_code, '000000'));
RETURN FALSE;
END IF;
END IF;
ELSE
v_step := '10';
v_check_corporation_id := validate_cor_id (v_id);
IF NVL (v_check_corporation_id, '~') = '829205'
THEN
p_error_code := '104045';
p_error_desc := error_desc (NVL (p_error_code, '000000'));
RETURN FALSE;
END IF;
END IF;
END IF;
BEGIN
IF v_client_type = '1'
THEN
SELECT NVL (national_id, '0')
INTO v_id_nid
FROM fm_client_indvl
WHERE client_no = v_client_no AND national_id = v_id;
ELSE
SELECT NVL (corporation_id, '0')
INTO v_id_cor
FROM fm_client_corporate
WHERE client_no = v_client_no AND corporation_id = v_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_step := '3';
/*BEGIN
SELECT NVL (global_id, '0')
INTO v_id
FROM fm_client
WHERE client_no = v_client_no;
IF v_client_type = '1'
THEN
v_id_nid := v_id;
ELSE
v_id_cor := v_id;
END IF;
EXCEPTION
WHEN OTHERS
THEN*/
p_error_code := '111221';
RETURN FALSE;
--END;
END;
P_id := v_id;
p_error_desc := error_desc (NVL (p_error_code, '000000'));
IF NVL (p_error_code, 0) = 0
THEN
p_error_code := '000000';
END IF;
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
/*IF p_error_code IN (0, '000000')
THEN
p_error_code := '000000';
p_error_desc := error_desc (p_error_code);
ELS*/
IF p_error_code NOT IN (0, '000000')
AND p_error_code IS NOT NULL
THEN
p_error_desc := error_desc (p_error_code);
ELSE
p_error_code := SQLCODE;
p_error_desc := SQLERRM;
END IF;
END;
ROLLBACK;
insert_log (
p_acct,
p_id, --v_seq_no_coi,
'CL_OPENBANKING',
'VALIDATION',
SQLCODE,
'CL',
'step : ['
|| v_step
|| ']'
|| ' SQLCODE : ['
|| p_error_code
|| ']'
|| ' SQLERRM : ['
|| p_error_desc
|| ']');
END validation; |