[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.6 2005/12/29 21:55:52 lkwan 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(30);
22 l_profile_value VARCHAR2(30);
23 l_len NUMBER;
24 l_sub VARCHAR2(5);
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 IF l_profile_value = 'FIRST FOUR VISIBLE' THEN
37 l_sub := SUBSTR(l_bank_acct_num, 1, 4);
38 RETURN RPAD(l_sub, l_len, '*');
39 ELSIF l_profile_value = 'LAST FOUR VISIBLE' THEN
40 l_sub := SUBSTR(l_bank_acct_num, -4, 4);
41 RETURN LPAD(l_sub, l_len, '*');
42 ELSE
43 RETURN l_bank_acct_num;
44 END IF;
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_masked_bank_acct_num', sqlcode, sqlerrm);
49 END get_masked_bank_acct_num;
50
51
52 /*=======================================================================+
53 | PUBLIC FUNCTION get_org_bank_acct_list |
54 | |
55 | DESCRIPTION |
56 | This function takes a org_id and returns the list of bank accounts |
57 | that this org has access |
58 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
59 | |
60 | ARGUMENTS |
61 | IN: |
62 | p_org_id |
63 | RETURN |
64 | '@' deliminated bank_account_id's |
65 +=======================================================================*/
66 FUNCTION get_org_bank_acct_list(p_org_id IN NUMBER)
67 RETURN VARCHAR2
68 IS
69 TYPE BankAcctIdTable IS TABLE OF ce_bank_accounts.bank_account_id%TYPE;
70 --l_bank_acct_list VARCHAR2(4000) DEFAULT '@';
71 l_bank_acct_list VARCHAR2(4000);
72 bank_acct_idtbl BankAcctIdTable;
73 BEGIN
74 /* select bank_account_id's */
75 SELECT DISTINCT bank_account_id
76 BULK COLLECT
77 INTO bank_acct_idtbl
78 FROM ce_bank_acct_uses_all
79 WHERE org_id = p_org_id;
80
81 --bug 3855002
82 IF (l_bank_acct_list IS NULL) THEN
83 l_bank_acct_list := '@';
84 END IF;
85
86 /* Concatenate Ids */
87 IF bank_acct_idtbl.COUNT > 0 THEN
88 FOR i IN bank_acct_idtbl.FIRST .. bank_acct_idtbl.LAST LOOP
89 l_bank_acct_list := l_bank_acct_list || bank_acct_idtbl(i) || '@';
90 END LOOP;
91 ELSE
92 l_bank_acct_list := '';
93 END IF;
94 RETURN l_bank_acct_list;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_org_bank_acct_list', sqlcode, sqlerrm);
99 END get_org_bank_acct_list;
100
101
102
103 /*=======================================================================+
104 | PUBLIC PRECEDURE sql_error |
105 | |
106 | DESCRIPTION |
107 | This procedure sets the error message and raise an exception |
108 | for unhandled sql errors. |
109 | Called by other routines. |
110 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
111 | |
112 | ARGUMENTS |
113 | IN: |
114 | p_routine |
115 | p_errcode |
116 | p_errmsg |
117 +=======================================================================*/
118 PROCEDURE sql_error(p_routine IN VARCHAR2,
119 p_errcode IN NUMBER,
120 p_errmsg IN VARCHAR2) IS
121 BEGIN
122 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
123 fnd_message.set_token('ROUTINE', p_routine);
124 fnd_message.set_token('ERRNO', p_errcode);
125 fnd_message.set_token('REASON', p_errmsg);
126 app_exception.raise_exception;
127 EXCEPTION
128 WHEN OTHERS THEN RAISE;
129 END;
130
131
132 /*=======================================================================+
133 | PUBLIC PRECEDURE get_internal_bank_accts |
134 | |
135 | DESCRIPTION |
136 | This procedure returns the list of internal bank accounts given the |
137 | conditions of date, currency, and organization that uses this BA. |
138 | |
139 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
140 | |
141 | ARGUMENTS |
142 | IN: |
143 | p_currency |
144 | p_org_type: acceptable values are 'OPERATING_UNIT', |
145 | 'BUSINESS_GROUP', |
146 | & 'LEGAL_ENTITY' |
147 | p_org_id |
148 | p_date |
149 | IN/OUT: |
150 | OUT: |
151 | x_bank_acct_ids '@' deliminated bank_account_id's |
152 +=======================================================================*/
153 PROCEDURE get_internal_bank_accts (p_currency IN VARCHAR2,
154 p_org_type IN VARCHAR2,
155 p_org_id IN NUMBER,
156 p_date IN DATE,
157 x_bank_acct_ids OUT NOCOPY BankAcctIdTable) IS
158 BEGIN
159 /* select bank_account_id's */
160 IF p_org_type = 'LEGAL_ENTITY' THEN
161 SELECT BA.bank_account_id
162 BULK COLLECT
163 INTO x_bank_acct_ids
164 FROM ( SELECT ce_ba.bank_account_id
165 FROM ce_bank_accounts ce_ba,
166 ce_bank_acct_uses_all ce_bau
167 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
168 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
169 AND (ce_ba.currency_code = p_currency
170 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
171 AND ce_bau.legal_entity_id = p_org_id
172 UNION
173 SELECT bank_account_id
174 FROM ce_bank_accounts
175 WHERE NVL(end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
176 AND (currency_code = p_currency
177 or MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
178 AND account_owner_org_id = p_org_id) BA;
179 ELSIF p_org_type in ('OPERATING_UNIT', 'BUSINESS_GROUP') THEN
180 SELECT ce_ba.bank_account_id
181 BULK COLLECT
182 INTO x_bank_acct_ids
183 FROM ce_bank_accounts ce_ba,
184 ce_bank_acct_uses_all ce_bau
185 WHERE ce_ba.bank_account_id = ce_bau.bank_account_id
186 AND NVL(ce_ba.end_date, NVL(p_date, sysdate)) >= NVL(p_date, sysdate)
187 AND (ce_ba.currency_code = p_currency
188 or ce_ba.MULTI_CURRENCY_ALLOWED_FLAG = 'Y') --bug 4915527
189 AND ce_bau.org_id = p_org_id;
190 ELSE
191 FND_MESSAGE.Set_Name('CE', 'CE_INVALID_ORG_TYPE');
192 APP_EXCEPTION.Raise_Exception;
193 END IF;
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 sql_error('CE_BANK_AND_ACCOUNT_UTIL.get_internal_bank_accts', sqlcode, sqlerrm);
198 END get_internal_bank_accts;
199
200 END CE_BANK_AND_ACCOUNT_UTIL;