1 PACKAGE BODY hr_sg_bank_acct_validation AS
2 /* $Header: pesgavbk.pkb 115.2 2002/09/27 03:26:50 jkarouza noship $ */
3 /* ==============================================================================
4 * Name : hr_sg_bank_acct_validation (BODY)
5 *
6 * Description : This package to validates SG bank accounts for the following
7 * banks.
8 * Bank of Singapore (Bank Code: 7117)
9 * Hong Kong and Shanghai Banking Corporation (Bank Code: 7232)
10 * Overseas Chinese Banking Corporation (Bank Code: 7339)
11 * UCO Bank (Bank Code: 7357)
12 * Malayan Banking Berhad (Bank Code: 7302)
13 *
14 *
15 * Change List
16 * -----------
17 *
18 * Version Date Author Bug No. Description of Change
19 * -------+---------+------------------+---------+-------------------------------
20 * 115.0 30-May-02 John Karouzakis Created
21 * 115.1 11-Jun-02 John Karouzakis Made GSCC compliant.
22 * 115.2 24-Sep-02 John Karouzakis 2590076 Changed to allow 7-digit Account Numbers
23 * for BOS,HSBC and OCBC
24 *
25 * ============================================================================== */
26
27 /* Returns 'FALSE' if Account Number is invalid for given Bank, otherwise will
28 return TRUE. */
29
30 FUNCTION validate_account (
31 p_account_number IN VARCHAR2,
32 p_bank_name IN VARCHAR2
33 )
34 RETURN VARCHAR2 IS
35
36 validation_failed EXCEPTION;
37
38 l_bank_code VARCHAR2(4);
39 l_return VARCHAR2(5) := 'FALSE';
40
41 CURSOR get_bank_code(p_bank_name VARCHAR2) IS
42 Select meaning
43 From hr_lookups
44 Where lookup_type = 'SG_BANK_CODE'
45 And lookup_code = p_bank_name
46 And application_id = 800
47 And enabled_flag = 'Y';
48
49
50 BEGIN
51
52 -- Check for valid parameters
53
54 IF (p_bank_name is NULL) or (p_account_number is NULL)
55 THEN
56 RAISE validation_failed;
57 END IF;
58
59
60 -- Get bank code from HR_LOOKUPS
61
62 OPEN get_bank_code (p_bank_name);
63 FETCH get_bank_code
64 INTO l_bank_code;
65
66 IF (get_bank_code%NOTFOUND)
67 THEN
68 RAISE validation_failed;
69 END IF;
70
71
72 -- Check that Account Number is the correct length for given bank.
73
74 -- Validate Account Numbers for BOS, HSBC and OCBC. Must be at least 4 digits.
75 IF (l_bank_code = '7117') or
76 (l_bank_code = '7232') or
77 (l_bank_code = '7339')
78 THEN
79 IF (length(p_account_number) < 4)
80 THEN
81 RAISE validation_failed;
82 END IF;
83 END IF;
84
85
86 -- Validate Account Numbers for UCO. Must be 12-digits
87 IF (l_bank_code = '7357')
88 THEN
89 IF (length(p_account_number) <> 12)
90 THEN
91 RAISE validation_failed;
92 END IF;
93 END IF;
94
95
96 -- Validate Account Numbers for MBB. Must be 11-digits
97 IF (l_bank_code = '7302')
98 THEN
99 IF (length(p_account_number) <> 11)
100 THEN
101 RAISE validation_failed;
102 END IF;
103 END IF;
104
105 CLOSE get_bank_code;
106
107 -- Must have passed all rules or not one of the banks to check.
108
109 l_return := 'TRUE';
110 RETURN l_return;
111
112 EXCEPTION
113 WHEN validation_failed THEN
114 RETURN l_return;
115 WHEN others THEN
116 RETURN l_return;
117 END validate_account;
118
119 END hr_sg_bank_acct_validation;
[Home] [Help]