1 PACKAGE BODY CE_CUSTOM_BANK_VALIDATIONS AS
2 /* $Header: cecbnkvb.pls 120.1.12010000.3 2009/12/24 07:18:41 vnetan noship $ */
3
4 /*----------------------------------------------------------------------+
5 | PRIVATE PROCEDURE |
6 | debug_log |
7 | |
8 | DESCRIPTION |
9 | For debugging purposes. Displays message in the debug log. |
10 +----------------------------------------------------------------------*/
11 PROCEDURE debug_log(msg_text VARCHAR2) AS
12 BEGIN
13 cep_standard.debug(msg_text);
14 END debug_log;
15
16 /*----------------------------------------------------------------------+
17 | PRIVATE PROCEDURE |
18 | set_error_text |
19 | |
20 | DESCRIPTION |
21 | Populates the error message stack with message to be displayed in |
22 | the UI. |
23 | |
24 | PARAMETERS |
25 | IN msg_name Message name created under application Cash Management |
26 +----------------------------------------------------------------------*/
27 PROCEDURE set_error_text(msg_name VARCHAR2) AS
28 BEGIN
29 fnd_message.set_name('CE',msg_name);
30 fnd_msg_pub.add;
31 END set_error_text;
32
33 /*----------------------------------------------------------------------+
34 | PUBLIC PROCEDURE |
35 | ce_usr_validate_bank |
36 | |
37 | DESCRIPTION |
38 | This will be called when validating a bank from the Bank Setup UI |
39 | |
40 | PARAMETERS |
41 | IN |
42 | Xi_COUNTRY_NAME Country Name (Two-digit ISO Code) |
43 | Xi_BANK_NUMBER Bank Number |
44 | Xi_BANK_NAME Bank Name |
45 | Xi_BANK_NAME_ALT Alt. Bank Name |
46 | Xi_TAX_PAYER_ID Tax Payer ID |
47 | Xi_BANK_ID Bank ID -> CE_BANKS_V.bank_party_id (available |
48 | when updating a bank) |
49 | |
50 | OUT |
51 | Xo_RETURN_STATUS fnd_api.g_ret_sts_success - Validation Successful |
52 | fnd_api.g_ret_sts_error - Validation Failure |
53 | |
54 | Xo_BANK_NUM_OUT Formatted value of bank number to be stored. |
55 +----------------------------------------------------------------------*/
56 PROCEDURE CE_USR_VALIDATE_BANK(
57 Xi_COUNTRY_NAME IN VARCHAR2,
58 Xi_BANK_NUMBER IN VARCHAR2,
59 Xi_BANK_NAME IN VARCHAR2,
60 Xi_BANK_NAME_ALT IN VARCHAR2,
61 Xi_TAX_PAYER_ID IN VARCHAR2,
62 Xi_BANK_ID IN NUMBER,
63 Xo_BANK_NUM_OUT OUT NOCOPY VARCHAR2,
64 Xo_RETURN_STATUS OUT NOCOPY VARCHAR2
65 ) AS
66
67 l_bank_num HZ_ORGANIZATION_PROFILES.bank_or_branch_number%TYPE;
68
69 BEGIN
70 debug_log('>>CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_bank');
71 -- Initialize Return values
72 Xo_RETURN_STATUS := fnd_api.g_ret_sts_success;
73 Xo_BANK_NUM_OUT := Xi_BANK_NUMBER;
74
75 /*------------------------------------------------------------------------------
76 -- TODO: Add validation logic at bank level
77 --
78
79 debug_log('Xi_COUNTRY_NAME =' ||Xi_COUNTRY_NAME);
80 debug_log('Xi_BANK_NUMBER =' ||Xi_BANK_NUMBER);
81 debug_log('Xi_BANK_NAME =' ||Xi_BANK_NAME);
82 debug_log('Xi_BANK_NAME_ALT=' ||Xi_BANK_NAME_ALT);
83 debug_log('Xi_TAX_PAYER_ID =' ||Xi_TAX_PAYER_ID);
84 debug_log('Xi_BANK_ID =' ||Xi_BANK_ID);
85
86 l_bank_num := CE_VALIDATE_BANKINFO.ce_remove_formats(Xi_BANK_NUMBER);
87
88 -- SAMPLE VALIDATION
89 -- For country AU, where Bank Number is populated, the Bank Number must be 3
90 -- characters in length; bank number must contain only digits
91
92 IF (Xi_COUNTRY_NAME = 'AU') AND (l_bank_num IS NOT NULL)
93 AND ((length(l_bank_num) <> 3)
94 OR (CE_VALIDATE_BANKINFO.ce_check_numeric(l_bank_num,1,length(l_bank_num)) <> '0'))
95 THEN
96 -- set error message name from Message Dictionary
97 -- AND set return flag to indicate failure
98 set_error_text('CE_CUSTOM_BANK');
99 Xo_RETURN_STATUS := fnd_api.g_ret_sts_error;
100 END IF;
101 ------------------------------------------------------------------------------*/
102
103 debug_log('<<CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_bank');
104 EXCEPTION
105 WHEN OTHERS THEN
106 debug_log('EXCEPTION: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_bank');
107 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
108 FND_MESSAGE.set_token('PROCEDURE', 'CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_bank');
109 FND_MSG_PUB.add;
110 RAISE;
111 END ce_usr_validate_bank;
112 /*----------------------------------------------------------------------+
113 | PUBLIC PROCEDURE |
114 | ce_usr_validate_branch |
115 | |
116 | DESCRIPTION |
117 | This will be called when validating a bank branch from the Bank |
118 | Branch Setup UI. |
119 | |
120 | PARAMETERS |
121 | IN |
122 | Xi_COUNTRY_NAME Country Name (Two-digit ISO Code) |
123 | Xi_BANK_NUMBER Bank Number |
124 | Xi_BRANCH_NUMBER Bank Branch Number |
125 | Xi_BANK_NAME Bank Name |
126 | Xi_BRANCH_NAME Bank Branch Name |
127 | Xi_BRANCH_NAME_ALT Alt. Bank Branch Name |
128 | Xi_BRANCH_TYPE Bank Branch Type |
129 | Xi_BANK_ID Bank ID -> CE_BANKS_V.bank_party_id |
130 | Xi_BRANCH_ID Bank ID -> CE_BANK_BRANCHES_V.branch_party_id |
131 | (available when updating a bank branch)|
132 | |
133 | OUT |
134 | Xo_RETURN_STATUS fnd_api.g_ret_sts_success - Validation Successful |
135 | fnd_api.g_ret_sts_error - Validation Failure |
136 | |
137 | Xo_BRANCH_NUM_OUT Formatted value of branch number to be stored |
138 +----------------------------------------------------------------------*/
139 PROCEDURE CE_USR_VALIDATE_BRANCH(
140 Xi_COUNTRY_NAME IN VARCHAR2,
141 Xi_BANK_NUMBER IN VARCHAR2,
142 Xi_BRANCH_NUMBER IN VARCHAR2,
143 Xi_BANK_NAME IN VARCHAR2,
144 Xi_BRANCH_NAME IN VARCHAR2,
145 Xi_BRANCH_NAME_ALT IN VARCHAR2,
146 Xi_BRANCH_TYPE IN VARCHAR2,
147 Xi_BANK_ID IN NUMBER,
148 Xi_BRANCH_ID IN NUMBER,
149 Xo_BRANCH_NUM_OUT OUT NOCOPY VARCHAR2,
150 Xo_RETURN_STATUS OUT NOCOPY VARCHAR2
151 ) AS
152
153 l_branch_num HZ_ORGANIZATION_PROFILES.bank_or_branch_number%TYPE;
154
155 BEGIN
156 debug_log('>>CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch');
157 -- Initialize return values
158 Xo_RETURN_STATUS := fnd_api.g_ret_sts_success;
159 Xo_BRANCH_NUM_OUT := Xi_BRANCH_NUMBER;
160
161 /*------------------------------------------------------------------------------
162 -- TODO: Add validation logic at branch level
163 --
164
165 debug_log('Xi_COUNTRY_NAME ='||Xi_COUNTRY_NAME );
166 debug_log('Xi_BANK_NUMBER ='||Xi_BANK_NUMBER );
167 debug_log('Xi_BRANCH_NUMBER ='||Xi_BRANCH_NUMBER );
168 debug_log('Xi_BANK_NAME ='||Xi_BANK_NAME );
169 debug_log('Xi_BRANCH_NAME ='||Xi_BRANCH_NAME );
170 debug_log('Xi_BRANCH_NAME_ALT ='||Xi_BRANCH_NAME_ALT );
171 debug_log('Xi_BRANCH_TYPE ='||Xi_BRANCH_TYPE );
172 debug_log('Xi_BANK_ID ='||Xi_BANK_ID );
173 debug_log('Xi_BRANCH_ID ='||Xi_BRANCH_ID );
174
175 -- SAMPLE VALIDATION
176 -- For country ES, where Branch Number is populated, the Branch Number must be 4
177 -- (or less) characters in length; where the length of the Bank Branch Number is
178 -- less than 4, the Bank Branch Number is left padded with zeroes
179
180 l_branch_num := CE_VALIDATE_BANKINFO.ce_remove_formats(Xi_BRANCH_NUMBER);
181 IF(Xi_COUNTRY_NAME = 'ES') THEN
182 IF(length(l_branch_num) > 4) THEN
183 -- set error message name from Message Dictionary
184 -- AND set return flag to indicate failure
185 set_error_text('CE_CUSTOM_BRANCH');
186 Xo_RETURN_STATUS := fnd_api.g_ret_sts_error;
187 ELSE
188 -- pad and set OUT value
189 Xo_BRANCH_NUM_OUT := LPAD(l_branch_num,4,'0');
190 END IF;
191 END IF;
192 ------------------------------------------------------------------------------*/
193
194 debug_log('<<CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_branch');
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 debug_log('EXCEPTION: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_branch');
199 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
200 FND_MESSAGE.set_token('PROCEDURE', 'CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_branch');
201 FND_MSG_PUB.add;
202 RAISE;
203 END ce_usr_validate_branch;
204
205 /*----------------------------------------------------------------------+
206 | PUBLIC PROCEDURE |
207 | ce_usr_validate_account |
208 | |
209 | DESCRIPTION |
210 | This will be called when validating a bank account from the Bank |
211 | Account Setup UI. Any error messages to be displayed in the UI |
212 | should be set by calling the procedure set_error_text(). |
213 | |
214 | PARAMETERS |
215 | IN |
216 | Xi_COUNTRY_NAME Country Name (Two-digit ISO Code) |
217 | Xi_BANK_NUMBER Bank Number |
218 | Xi_BRANCH_NUMBER Bank Branch Number |
219 | Xi_ACCOUNT_NUMBER Bank Account Number |
220 | Xi_CD Check Digit |
221 | Xi_ACCOUNT_NAME Bank Account Name |
222 | Xi_CURRENCY_CODE Bank Account Currency |
223 | Xi_ACCOUNT_TYPE Bank Account Type |
224 | Xi_ACCOUNT_SUFFIX Account Suffix |
225 | Xi_SECONDARY_ACCT_REF Secondary Account Reference |
226 | Xi_ACCT_CLASSIFICATION Account Classification ('Internal') |
227 | Xi_BANK_ID CE_BANKS_V.bank_party_id |
228 | Xi_BRANCH_ID CE_BANKS_BRANCHES_V.branch_party_id |
229 | Xi_ACCOUNT_ID CE_BANK_ACCOUNTS.bank_account_id |
230 | |
231 | OUT |
232 | Xo_RETURN_STATUS fnd_api.g_ret_sts_success - Validation Successful |
233 | fnd_api.g_ret_sts_error - Validation Failure |
234 | |
235 | Xo_ACCOUNT_NUM_OUT Formatted value of account number to be stored |
236 +----------------------------------------------------------------------*/
237 PROCEDURE CE_USR_VALIDATE_ACCOUNT(
238 Xi_COUNTRY_NAME IN VARCHAR2,
239 Xi_BANK_NUMBER IN VARCHAR2,
240 Xi_BRANCH_NUMBER IN VARCHAR2,
241 Xi_ACCOUNT_NUMBER IN VARCHAR2,
242 Xi_CD IN VARCHAR2,
243 Xi_ACCOUNT_NAME IN VARCHAR2,
244 Xi_CURRENCY_CODE IN VARCHAR2,
245 Xi_ACCOUNT_TYPE IN VARCHAR2,
246 Xi_ACCOUNT_SUFFIX IN VARCHAR2,
247 Xi_SECONDARY_ACCT_REF IN VARCHAR2,
251 Xi_ACCOUNT_ID IN NUMBER,
248 Xi_ACCT_CLASSIFICATION IN VARCHAR2,
249 Xi_BANK_ID IN NUMBER,
250 Xi_BRANCH_ID IN NUMBER,
252 Xo_ACCOUNT_NUM_OUT OUT NOCOPY VARCHAR2,
253 Xo_RETURN_STATUS OUT NOCOPY VARCHAR2
254 ) AS
255
256 /* CURSOR c_acct_details IS
257 SELECT * FROM CE_BANK_ACCOUNTS WHERE bank_account_id = Xi_Account_id;*/
258
259 BEGIN
260 debug_log('>>CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account');
261 -- Initialize Return values
262 Xo_RETURN_STATUS := fnd_api.g_ret_sts_success;
263 Xo_ACCOUNT_NUM_OUT := Xi_ACCOUNT_NUMBER;
264
265 /*------------------------------------------------------------------------------
266 -- TODO: Add validation at account level
267 --
268 debug_log('Xi_COUNTRY_NAME ='||Xi_COUNTRY_NAME );
269 debug_log('Xi_BANK_NUMBER ='||Xi_BANK_NUMBER );
270 debug_log('Xi_BRANCH_NUMBER ='||Xi_BRANCH_NUMBER );
271 debug_log('Xi_ACCOUNT_NUMBER ='||Xi_ACCOUNT_NUMBER );
272 debug_log('Xi_CD ='||Xi_CD );
273 debug_log('Xi_ACCOUNT_NAME ='||Xi_ACCOUNT_NAME );
274 debug_log('Xi_CURRENCY_CODE ='||Xi_CURRENCY_CODE );
275 debug_log('Xi_ACCOUNT_TYPE ='||Xi_ACCOUNT_TYPE );
276 debug_log('Xi_ACCOUNT_SUFFIX ='||Xi_ACCOUNT_SUFFIX );
277 debug_log('Xi_SECONDARY_ACCT_REF ='||Xi_SECONDARY_ACCT_REF );
278 debug_log('Xi_ACCT_CLASSIFICATION ='||Xi_ACCT_CLASSIFICATION );
279 debug_log('Xi_BANK_ID ='||Xi_BANK_ID );
280 debug_log('Xi_BRANCH_ID ='||Xi_BRANCH_ID );
281 debug_log('Xi_ACCOUNT_ID ='||Xi_ACCOUNT_ID );
282
283 -- Accessing bank account details using bank account id
284 FOR i IN c_acct_details
285 LOOP
286 debug_log('acct_name='||i.bank_account_name);
287 debug_log('bank_acct_num='||i.bank_account_num);
288 debug_log('attr_categ='||i.attribute_category);
289 debug_log('account_type='||i.bank_account_type);
290 END LOOP;
291
292 -- SAMPLE VALIDATION
293 -- For country JP, where the Bank Account Number is populated, the value for
294 -- the bank account type cannot be NULL nor can the length of the value for
295 -- the bank account type cannot be equal to 1
296
297 IF (Xi_COUNTRY_NAME = 'JP')
298 AND (Xi_ACCOUNT_NUMBER IS NOT NULL)
299 AND ((Xi_ACCOUNT_TYPE IS NULL) OR (length(Xi_ACCOUNT_TYPE) = 1))
300 THEN
301 -- set error message name from Message Dictionary
302 -- AND set return flag to indicate failure
303 set_error_text('CE_CUSTOM_ACCOUNT');
304 Xo_RETURN_STATUS := fnd_api.g_ret_sts_error;
305 END IF;
306 ------------------------------------------------------------------------------*/
307 debug_log('<<CE_CUSTOM_BANK_VALIDATIONS.ce_usr_validate_account');
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 debug_log('EXCEPTION: CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_account');
312 FND_MESSAGE.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
313 FND_MESSAGE.set_token('PROCEDURE', 'CE_CUSTOM_BANK_VALIDATIONS.ce_usr_valdiate_account');
314 FND_MSG_PUB.add;
315 RAISE;
316 END ce_usr_validate_account;
317
318 END CE_CUSTOM_BANK_VALIDATIONS;
[Home] [Help]