[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_AND_ACCOUNT_UTIL
Source
1 PACKAGE BODY CE_BANK_AND_ACCOUNT_UTIL AS
2 /*$Header: cebautlb.pls 120.15 2011/12/06 08:30:06 bkkashya ship $ */
3
4 /*=======================================================================+
5 | PUBLIC FUNCTION get_masked_bank_acct_num |
6 | |
7 | DESCRIPTION |
8 | This function takes a bank_account_id and returns the bank account |
9 | number with the appropriate mask based on the value of the profile |
10 | option 'CE: Mask Internal Bank Account Numbers' |
11 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
12 | |
13 | ARGUMENTS |
14 | IN: |
15 | p_bank_acct_id |
16 +=======================================================================*/
17
18 FUNCTION get_masked_bank_acct_num(p_bank_acct_id IN NUMBER)
19 RETURN VARCHAR2
20 IS
21 l_bank_acct_num VARCHAR2(100);
22 l_profile_value VARCHAR2(30);
23 l_len NUMBER;
24 l_sub VARCHAR2(15);
25 BEGIN
26 SELECT bank_account_num
27 INTO l_bank_acct_num
28 FROM ce_bank_accounts
29 WHERE bank_account_id = p_bank_acct_id;
30
31 l_len := LENGTH(l_bank_acct_num);
32
33 l_profile_value := NVL(FND_PROFILE.value
34 ('CE_MASK_INTERNAL_BANK_ACCT_NUM'), 'NO MASK');
35
36 -- 6932525: For account numbers less than 4 digits, masking will not apply
37 IF l_len < 4 THEN
38 l_profile_value := 'NO MASK';
39 END IF;
40
41
42 IF l_profile_value = 'FIRST FOUR VISIBLE' THEN
43 l_sub := SUBSTRB(l_bank_acct_num, 1, 4);
44 RETURN RPAD(l_sub, l_len, '*');
45 ELSIF l_profile_value = 'LAST FOUR VISIBLE' THEN
46 l_sub := SUBSTRB(l_bank_acct_num, -4, 4);
47 RETURN LPAD(l_sub, l_len, '*');
48 ELSE
49 RETURN l_bank_acct_num;
50 END IF;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_masked_bank_acct_num', sqlcode, sqlerrm);
55 END get_masked_bank_acct_num;
56
57 /*=======================================================================+
58 | PUBLIC FUNCTION get_masked_IBAN |
59 | |
60 | DESCRIPTION |
61 | This function takes a bank_account_id and returns the IBAN |
62 | number with the appropriate mask based on the value of the profile |
63 | option 'CE: Mask Internal Bank Account Numbers' |
64 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
65 | |
66 | ARGUMENTS |
67 | IN: |
68 | p_bank_acct_id |
69 +=======================================================================*/
70
71 FUNCTION get_masked_IBAN(p_bank_acct_id IN NUMBER)
72 RETURN VARCHAR2
73 IS
74 l_IBAN ce_bank_accounts.iban_number%type; -- 9930714
75 l_profile_value VARCHAR2(30);
76 l_len NUMBER;
77 l_sub VARCHAR2(5);
78 BEGIN
79 SELECT IBAN_NUMBER
80 INTO l_IBAN
81 FROM ce_bank_accounts
82 WHERE bank_account_id = p_bank_acct_id;
83
84 l_len := LENGTH(l_IBAN);
85
86 l_profile_value := NVL(FND_PROFILE.value
87 ('CE_MASK_INTERNAL_BANK_ACCT_NUM'), 'NO MASK');
88
89 -- For account numbers less than 4 digits, masking will not apply
90 IF l_len < 4 THEN
91 l_profile_value := 'NO MASK';
92 END IF;
93
94
95 IF l_profile_value = 'FIRST FOUR VISIBLE' THEN
96 l_sub := SUBSTRB(l_IBAN, 1, 4);
97 RETURN RPAD(l_sub, l_len, '*');
98 ELSIF l_profile_value = 'LAST FOUR VISIBLE' THEN
99 l_sub := SUBSTRB(l_IBAN, -4, 4);
100 RETURN LPAD(l_sub, l_len, '*');
101 ELSE
102 RETURN l_IBAN;
103 END IF;
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_masked_IBAN', sqlcode, sqlerrm);
108 END get_masked_IBAN;
109
110 /*=======================================================================+
111 | PUBLIC FUNCTION get_org_bank_acct_list |
112 | |
113 | DESCRIPTION |
114 | This function takes a org_id and returns the list of bank accounts |
115 | that this org has access |
116 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
117 | |
118 | ARGUMENTS |
119 | IN: |
120 | p_org_id |
121 | RETURN |
122 | '@' deliminated bank_account_id's |
123 +=======================================================================*/
124 FUNCTION get_org_bank_acct_list(p_org_id IN NUMBER)
125 RETURN VARCHAR2
126 IS
127 TYPE BankAcctIdTable IS TABLE OF ce_bank_accounts.bank_account_id%TYPE;
128 --l_bank_acct_list VARCHAR2(4000) DEFAULT '@';
129 l_bank_acct_list VARCHAR2(4000);
130 bank_acct_idtbl BankAcctIdTable;
131 BEGIN
132 /* select bank_account_id's */
133 SELECT DISTINCT bank_account_id
134 BULK COLLECT
135 INTO bank_acct_idtbl
136 FROM ce_bank_acct_uses_all
137 WHERE org_id = p_org_id;
138
139 --bug 3855002
140 IF (l_bank_acct_list IS NULL) THEN
141 l_bank_acct_list := '@';
142 END IF;
143
144 /* Concatenate Ids */
145 IF bank_acct_idtbl.COUNT > 0 THEN
146 FOR i IN bank_acct_idtbl.FIRST .. bank_acct_idtbl.LAST LOOP
147 l_bank_acct_list := l_bank_acct_list || bank_acct_idtbl(i) || '@';
148 END LOOP;
149 ELSE
150 l_bank_acct_list := '';
151 END IF;
152 RETURN l_bank_acct_list;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_org_bank_acct_list', sqlcode, sqlerrm);
157 END get_org_bank_acct_list;
158
159
160
161 /*=======================================================================+
162 | PUBLIC PRECEDURE sql_error |
163 | |
164 | DESCRIPTION |
165 | This procedure sets the error message and raise an exception |
166 | for unhandled sql errors. |
167 | Called by other routines. |
168 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
169 | |
170 | ARGUMENTS |
171 | IN: |
172 | p_routine |
173 | p_errcode |
174 | p_errmsg |
175 +=======================================================================*/
176 PROCEDURE sql_error(p_routine IN VARCHAR2,
177 p_errcode IN NUMBER,
178 p_errmsg IN VARCHAR2) IS
179 BEGIN
180 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
181 fnd_message.set_token('ROUTINE', p_routine);
182 fnd_message.set_token('ERRNO', p_errcode);
183 fnd_message.set_token('REASON', p_errmsg);
184 app_exception.raise_exception;
185 EXCEPTION
186 WHEN OTHERS THEN RAISE;
187 END;
188
189
190 /*=======================================================================+
191 | PUBLIC PRECEDURE get_internal_bank_accts |
192 | |
193 | DESCRIPTION |
194 | This procedure returns the list of internal bank accounts given the |
195 | conditions of date, currency, and organization that uses this BA. |
196 | |
197 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
198 | |
199 | ARGUMENTS |
200 | IN: |
201 | p_currency |
202 | p_org_type: acceptable values are 'OPERATING_UNIT', |
203 | 'BUSINESS_GROUP', |
204 | & 'LEGAL_ENTITY' |
205 | p_org_id |
206 | p_date |
207 | IN/OUT: |
208 | OUT: |
209 | x_bank_acct_ids '@' deliminated bank_account_id's |
210 +=======================================================================*/
211 PROCEDURE get_internal_bank_accts (p_currency IN VARCHAR2,
212 p_org_type IN VARCHAR2,
213 p_org_id IN NUMBER,
214 p_date IN DATE,
215 x_bank_acct_ids OUT NOCOPY BankAcctIdTable) IS
216 BEGIN
217 /* select bank_account_id's */
218 IF p_org_type = 'LEGAL_ENTITY' THEN
219 SELECT BA.bank_account_id
220 BULK COLLECT
221 INTO x_bank_acct_ids
222 FROM ( SELECT ce_ba.bank_account_id
223 FROM ce_bank_accounts ce_ba,
224 ce_bank_acct_uses_all ce_bau
225 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
226 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
227 AND (ce_ba.currency_code = p_currency
228 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
229 AND ce_bau.legal_entity_id = p_org_id
230 UNION
231 SELECT bank_account_id
232 FROM ce_bank_accounts
233 WHERE NVL(end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
234 AND (currency_code = p_currency
235 or MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
236 AND account_owner_org_id = p_org_id) BA;
237 ELSIF p_org_type in ('OPERATING_UNIT', 'BUSINESS_GROUP') THEN
238 SELECT ce_ba.bank_account_id
239 BULK COLLECT
240 INTO x_bank_acct_ids
241 FROM ce_bank_accounts ce_ba,
242 ce_bank_acct_uses_all ce_bau
243 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
244 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
245 AND (ce_ba.currency_code = p_currency
246 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
247 AND ce_bau.org_id = p_org_id;
248 ELSE
249 FND_MESSAGE.Set_Name('CE', 'CE_INVALID_ORG_TYPE');
250 APP_EXCEPTION.Raise_Exception;
251 END IF;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_internal_bank_accts', sqlcode, sqlerrm);
256 END get_internal_bank_accts;
257
258 /*=======================================================================+
259 | PUBLIC PRECEDURE get_internal_bank_accts For bug 8277703 |
260 | |
261 | DESCRIPTION |
262 | This procedure returns the list of internal bank accounts given the |
263 | conditions of date, currency, and organization that uses this BA. |
264 | |
265 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
266 | |
267 | ARGUMENTS |
268 | IN: |
272 | IN/OUT: |
269 | p_currency |
270 | p_org_id |
271 | p_date |
273 | OUT: |
274 | p_internal_bank_account_id |
275 | p_valid_flag |
276 +=======================================================================*/
277 PROCEDURE get_internal_bank_accts (p_currency IN VARCHAR2,
278 p_org_type IN VARCHAR2,
279 p_org_id IN NUMBER,
280 p_date IN DATE,
281 p_internal_bank_account_id IN OUT NOCOPY NUMBER,
282 p_valid_flag OUT NOCOPY BOOLEAN)
283 IS
284 l_count NUMBER;
285 l_bank_acct_ids BankAcctIdTable;
286 BEGIN
287 l_count := 0;
288 /* select bank_account_id's */
289 IF (p_internal_bank_account_id IS NOT NULL) THEN
290 IF p_org_type = 'LEGAL_ENTITY' THEN
291 SELECT Count(BA.bank_account_id)
292 INTO l_count
293 FROM ( SELECT ce_ba.bank_account_id
294 FROM ce_bank_accounts ce_ba,
295 ce_bank_acct_uses_all ce_bau
296 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
297 AND ce_ba.bank_account_id = p_internal_bank_account_id
298 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
299 AND (ce_ba.currency_code = p_currency
300 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
301 AND ce_bau.legal_entity_id = p_org_id
302 UNION
303 SELECT bank_account_id
304 FROM ce_bank_accounts
305 WHERE bank_account_id = p_internal_bank_account_id
306 AND NVL(end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
307 AND (currency_code = p_currency
308 or MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
309 AND account_owner_org_id = p_org_id
310 )BA;
311
312 ELSIF p_org_type in ('OPERATING_UNIT', 'BUSINESS_GROUP') THEN
313 SELECT Count(ce_ba.bank_account_id)
314 INTO l_count
315 FROM ce_bank_accounts ce_ba,
316 ce_bank_acct_uses_all ce_bau
317 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
318 AND ce_ba.bank_account_id = p_internal_bank_account_id
319 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
320 AND (ce_ba.currency_code = p_currency
321 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
322 AND ce_bau.org_id = p_org_id;
323 ELSE
324 FND_MESSAGE.Set_Name('CE', 'CE_INVALID_ORG_TYPE');
325 APP_EXCEPTION.Raise_Exception;
326 END IF;
327 IF(l_count = 0) THEN
328 p_valid_flag := FALSE;
329 ELSE
330 p_valid_flag := TRUE;
331 END IF;
332 ELSE
333 IF p_org_type = 'LEGAL_ENTITY' THEN
334 SELECT BA.bank_account_id
335 BULK COLLECT
336 INTO l_bank_acct_ids
337 FROM ( SELECT ce_ba.bank_account_id
338 FROM ce_bank_accounts ce_ba,
339 ce_bank_acct_uses_all ce_bau
340 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
341 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
342 AND (ce_ba.currency_code = p_currency
343 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
344 AND ce_bau.legal_entity_id = p_org_id
345 UNION
346 SELECT bank_account_id
347 FROM ce_bank_accounts
348 WHERE NVL(end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
349 AND (currency_code = p_currency
350 or MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
351 AND account_owner_org_id = p_org_id) BA;
352
353 ELSIF p_org_type in ('OPERATING_UNIT', 'BUSINESS_GROUP') THEN
354 SELECT ce_ba.bank_account_id
355 BULK COLLECT
356 INTO l_bank_acct_ids
357 FROM ce_bank_accounts ce_ba,
358 ce_bank_acct_uses_all ce_bau
359 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
360 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
361 AND (ce_ba.currency_code = p_currency
362 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
363 AND ce_bau.org_id = p_org_id;
364 ELSE
365 FND_MESSAGE.Set_Name('CE', 'CE_INVALID_ORG_TYPE');
366 APP_EXCEPTION.Raise_Exception;
367 END IF;
368
369 IF(l_bank_acct_ids.Count = 1) THEN
370 p_valid_flag := TRUE;
371 p_internal_bank_account_id := l_bank_acct_ids(l_bank_acct_ids.FIRST);
372 ELSE
373 p_valid_flag := FALSE;
374 END IF;
375 END IF;
376 EXCEPTION
377 WHEN OTHERS THEN
378 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_internal_bank_accts', sqlcode, sqlerrm);
379 END get_internal_bank_accts;
380
381 END CE_BANK_AND_ACCOUNT_UTIL;