[Home] [Help]
PACKAGE BODY: APPS.XTR_NOTIONAL_BANK_ACCOUNTS
Source
1 PACKAGE BODY XTR_NOTIONAL_BANK_ACCOUNTS as
2 /* $Header: xtrnbnkb.pls 120.1 2005/07/29 09:32:45 badiredd noship $ */
3
4 --------------------------------------
5 -- declaration of public procedures and functions
6 --------------------------------------
7
8 /**
9 * PROCEDURE modify_xtr_bank_accounts
10 *
11 * DESCRIPTION
12 * This procedure creates a dummy bank account in xtr_bank_accounts
13 * if the cash pool is being created.
14 * If an existing cash pool is being updated, then the corresponding
15 * dummy bank account in xtr_bank_accounts will be updated.
16 *
17 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
18 *
19 * ARGUMENTS
20 * IN:
21 * p_cashpool_id Cashpool ID of the Notional Cash Pool
22 * that is being created or updated.
23 * p_bank_account_id Bank Account ID from CE_BANK_ACCOUNTS
24 * of the Concentration Account of the
25 * Cashpool.
26 * IN/OUT:
27 * x_return_status Return status after the call. The
28 * status can be Y for success or N for
29 * error.
30 * OUT:
31 *
32 * NOTES
33 *
34 * MODIFICATION HISTORY
35 *
36 * 01-26-2005 Rajesh Jose o Created.
37 *
38 */
39
40 -- R12 Modified the reference to AP_BANK_ACCOUNTS and AP_BANK_ACCOUNT_ID to CE_BANK_ACCOUNTS and CE_BANK_ACCOUNT_ID
41 PROCEDURE modify_xtr_bank_accounts(
42 p_cashpool_id IN CE_CASHPOOLS.CASHPOOL_ID%TYPE,
43 p_bank_account_id IN CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
44 x_return_status IN OUT NOCOPY VARCHAR2)
45 IS
46 cursor cashpool_exists IS
47 SELECT cashpool_id
48 FROM xtr_bank_accounts
49 WHERE cashpool_id = p_cashpool_id
50 AND setoff_account_yn = 'Y';
51 l_cashpool_id CE_CASHPOOLS.CASHPOOL_ID%TYPE;
52 BEGIN
53 OPEN CASHPOOL_EXISTS;
54 FETCH CASHPOOL_EXISTS INTO l_cashpool_id;
55 IF CASHPOOL_EXISTS%FOUND THEN
56 update_xtr_bank_account(p_cashpool_id, p_bank_account_id,
57 x_return_status);
58 IF (x_return_status <> 'N') THEN
59 COMMIT;
60 END IF;
61 ELSE
62 create_xtr_bank_account(p_cashpool_id, p_bank_account_id,
63 x_return_status);
64 IF (x_return_status <> 'N') THEN
65 COMMIT;
66 END IF;
67 END IF;
68 CLOSE CASHPOOL_EXISTS;
69 END;
70
71 /**
72 * PROCEDURE create_xtr_bank_account
73 *
74 * DESCRIPTION
75 * Creates dummy account in XTR_BANK_ACCOUNTS
76 *
77 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
78 *
79 * ARGUMENTS
80 * IN:
81 * p_cashpool_id Cashpool ID of the Notional Cash Pool
82 * that is being created or updated.
83 * p_bank_account_id Bank Account ID from CE_BANK_ACCOUNTS
84 * of the Concentration Account of the
85 * Cashpool.
86 * IN/OUT:
87 * x_return_status Return status after the call. The
88 * status can be Y for success or N for
89 * error.
90 * OUT:
91 * NOTES
92 *
93 * MODIFICATION HISTORY
94 *
95 * 01-26-2005 Rajesh Jose o Created.
96 */
97 -- R12 Modified the reference to AP_BANK_ACCOUNTS_ALL and AP_BANK_ACCOUNT_ID to CE_BANK_ACCOUNTS and CE_BANK_ACCOUNT_ID
98 PROCEDURE create_xtr_bank_account(
99 p_cashpool_id IN CE_CASHPOOLS.CASHPOOL_ID%TYPE,
100 p_bank_account_id IN CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
101 x_return_status IN OUT NOCOPY VARCHAR2)
102 IS
103 cursor account_details IS
104 SELECT bank_code, party_code, currency, year_calc_type,
105 portfolio_code, interest_calculation_basis,
106 rounding_type, day_count_type, code_combination_id
107 FROM XTR_BANK_ACCOUNTS
108 WHERE ce_bank_account_id
109 = p_bank_account_id;
110
111 cursor acct_number_exists(
112 p_acct_number XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
113 p_party_code XTR_BANK_ACCOUNTS.PARTY_CODE%TYPE) IS
114 SELECT account_number
115 FROM XTR_BANK_ACCOUNTS
116 WHERE account_number = p_acct_number
117 AND party_code = p_party_code;
118
119 cursor cashpool_details IS
120 SELECT substrb(name,1,80), substrb(UPPER(name),1,20)
121 FROM ce_cashpools
122 WHERE cashpool_id = p_cashpool_id;
123
124 cursor dealer_details IS
125 SELECT dealer_code
126 FROM xtr_dealer_codes
127 WHERE user_id = FND_GLOBAL.USER_ID;
128
129 l_bank_code XTR_BANK_ACCOUNTS.BANK_CODE%TYPE;
130 l_party_code XTR_BANK_ACCOUNTS.PARTY_CODE%TYPE;
131 l_currency XTR_BANK_ACCOUNTS.CURRENCY%TYPE;
132 l_year_calc_type XTR_BANK_ACCOUNTS.YEAR_CALC_TYPE%TYPE;
133 l_portfolio_code XTR_BANK_ACCOUNTS.PORTFOLIO_CODE%TYPE;
134 l_int_calc_basis XTR_BANK_ACCOUNTS.INTEREST_CALCULATION_BASIS%TYPE;
135 l_rounding_type XTR_BANK_ACCOUNTS.ROUNDING_TYPE%TYPE;
136 l_day_count_type XTR_BANK_ACCOUNTS.DAY_COUNT_TYPE%TYPE;
137 l_code_combination_id XTR_BANK_ACCOUNTS.CODE_COMBINATION_ID%TYPE;
138
139 l_account_number XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
140
141 l_bank_short_code XTR_BANK_ACCOUNTS.BANK_SHORT_CODE%TYPE;
142 l_new_acct_number XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
143
144 l_created_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
145 l_updated_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
146
147 l_counter NUMBER;
148 l_dummy_char VARCHAR2(1);
149
150 BEGIN
151 l_dummy_char := '0';
152
153 OPEN CASHPOOL_DETAILS;
154 FETCH CASHPOOL_DETAILS
155 INTO l_bank_short_code, l_new_acct_number;
156 CLOSE CASHPOOL_DETAILS;
157
158 OPEN ACCOUNT_DETAILS;
159 FETCH ACCOUNT_DETAILS
160 INTO l_bank_code, l_party_code, l_currency, l_year_calc_type,
161 l_portfolio_code, l_int_calc_basis, l_rounding_type,
162 l_day_count_type, l_code_combination_id;
163 IF ACCOUNT_DETAILS%NOTFOUND THEN
164 CLOSE ACCOUNT_DETAILS;
165 x_return_status := 'N';
166 RAISE FND_API.G_EXC_ERROR;
167 END IF;
168 CLOSE ACCOUNT_DETAILS;
169
170 OPEN DEALER_DETAILS;
171 FETCH DEALER_DETAILS
172 INTO l_created_by;
173 CLOSE DEALER_DETAILS;
174 l_updated_by := l_created_by;
175
176 l_counter := 1;
177 FOR rec in acct_number_exists(l_new_acct_number, l_party_code)
178 LOOP
179 IF ACCT_NUMBER_EXISTS%FOUND THEN
180 IF l_counter < 10 THEN
181 SELECT REPLACE(
182 rpad(
183 l_new_acct_number, 20, l_dummy_char),
184 rpad(
185 l_new_acct_number, 20, l_dummy_char),
186 substrb
187 (rpad
188 (l_new_acct_number,20, l_dummy_char),
189 1,19)||l_counter)
190 INTO l_new_acct_number
191 FROM DUAL;
192 ELSIF ((l_counter >= 10) AND (l_counter < 100)) THEN
193 SELECT REPLACE(
194 rpad(
195 l_new_acct_number, 20, l_dummy_char),
196 rpad(
197 l_new_acct_number, 20, l_dummy_char),
198 substrb
199 (rpad
200 (l_new_acct_number,20, l_dummy_char),
201 1,18)||l_counter)
202 INTO l_new_acct_number
203 FROM DUAL;
204 ELSIF ((l_counter >=100) AND (l_counter < 1000)) THEN
205 SELECT REPLACE(
206 rpad(
207 l_new_acct_number, 20, l_dummy_char),
208 rpad(
209 l_new_acct_number, 20, l_dummy_char),
210 substrb
211 (rpad
212 (l_new_acct_number,20, l_dummy_char),
213 1,17)||l_counter)
214 INTO l_new_acct_number
215 FROM DUAL;
216 ELSIF ((l_counter >=1000) AND (l_counter < 10000)) THEN
217 SELECT REPLACE(
218 rpad(
219 l_new_acct_number, 20, l_dummy_char),
220 rpad(
221 l_new_acct_number, 20, l_dummy_char),
222 substrb
223 (rpad
224 (l_new_acct_number,20, l_dummy_char),
225 1,16)||l_counter)
226 INTO l_new_acct_number
227 FROM DUAL;
228 ELSIF ((l_counter >=10000) AND (l_counter <100000)) THEN
229 SELECT REPLACE(
230 rpad(
231 l_new_acct_number, 20, l_dummy_char),
232 rpad(
233 l_new_acct_number, 20, l_dummy_char),
234 substrb
235 (rpad
236 (l_new_acct_number,20, l_dummy_char),
237 1,15)||l_counter)
238 INTO l_new_acct_number
239 FROM DUAL;
240 ELSIF ((l_counter >=100000) AND
241 (l_counter <1000000)) THEN
242 SELECT REPLACE(
243 rpad(
244 l_new_acct_number, 20, l_dummy_char),
245 rpad(
246 l_new_acct_number, 20, l_dummy_char),
247 substrb
248 (rpad
249 (l_new_acct_number,20, l_dummy_char),
250 1,14)||l_counter)
251 INTO l_new_acct_number
252 FROM DUAL;
253 END IF;
254 l_counter := l_counter + 1;
255 END IF;
256 EXIT WHEN ACCT_NUMBER_EXISTS%NOTFOUND;
257 END LOOP;
258
259 BEGIN
260 INSERT INTO XTR_BANK_ACCOUNTS(
261 account_number, setoff_account_yn,
262 bank_code, party_code, bank_short_code,
263 currency, location, street, year_calc_type,
264 portfolio_code, interest_calculation_basis,
265 rounding_type, day_count_type,
266 code_combination_id, pricing_model,
267 authorised, party_type, cashpool_id,
268 created_by, created_on,
269 updated_by, updated_on)
270 VALUES
271 (l_new_acct_number, 'Y',
272 l_bank_code, l_party_code, l_bank_short_code,
273 l_currency, 'SETOFF ACCOUNT', 'SETOFF ACCOUNT',
274 l_year_calc_type,
275 l_portfolio_code, l_int_calc_basis,
276 l_rounding_type, l_day_count_type,
277 l_code_combination_id, 'NO_REVAL',
278 'Y', 'C', p_cashpool_id,
279 nvl(l_created_by, FND_GLOBAL.user_id), sysdate,
280 nvl(l_updated_by, FND_GLOBAL.user_id), sysdate);
281 EXCEPTION
282 WHEN OTHERS THEN
283 x_return_status := 'N';
284 RAISE FND_API.G_EXC_ERROR;
285 END;
286 x_return_status := 'Y';
287
288 END create_xtr_bank_account;
289
290 /**
291 * FUNCTION update_xtr_bank_account
292 *
293 * DESCRIPTION
294 * Updates the dummy account in XTR_BANK_ACCOUNTS
295 *
296 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
297 *
298 * ARGUMENTS
299 * IN:
300 * p_cashpool_id Cashpool ID of the Notional Cash Pool
301 * that is being created or updated.
302 * p_bank_account_id Bank Account ID from CE_BANK_ACCOUNTS
303 * of the Concentration Account of the
304 * Cashpool.
305 * IN/OUT:
306 * x_return_status Return status after the call. The
307 * status can be Y for success or N for
308 * error.
309 * OUT:
310 *
311 * NOTES
312 *
313 * MODIFICATION HISTORY
314 *
315 * 01-26-2005 Rajesh Jose o Created.
316 */
317 -- R12 Modified the reference to AP_BANK_ACCOUNTS and AP_BANK_ACCOUNT_ID to CE_BANK_ACCOUNTS and CE_BANK_ACCOUNT_ID
318 PROCEDURE update_xtr_bank_account(
319 p_cashpool_id IN CE_CASHPOOLS.CASHPOOL_ID%TYPE,
320 p_bank_account_id IN CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
321 x_return_status IN OUT NOCOPY VARCHAR2)
322 IS
323
324 cursor account_changed IS
325 SELECT conc_account_id
326 FROM CE_CASHPOOLS
327 WHERE cashpool_id = p_cashpool_id;
328
329 cursor changed_account_details IS
330 SELECT xba.bank_code, xba.year_calc_type,
331 xba.interest_calculation_basis, xba.rounding_type,
332 xba.day_count_type, xba.code_combination_id,
333 xpin.set_of_books_id
334 FROM XTR_BANK_ACCOUNTS xba, XTR_PARTY_INFO xpin
335 WHERE ce_bank_account_id =
336 p_bank_account_id
337 AND xpin.party_code = xba.party_code
338 AND xpin.party_type = 'C';
339
340 cursor dealer_details IS
341 SELECT dealer_code
342 FROM xtr_dealer_codes
343 WHERE user_id = FND_GLOBAL.USER_ID;
344
345 l_conc_account_id CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE;
346
347 l_bank_code XTR_BANK_ACCOUNTS.BANK_CODE%TYPE;
348 l_year_calc_type XTR_BANK_ACCOUNTS.YEAR_CALC_TYPE%TYPE;
349 l_int_calc_basis XTR_BANK_ACCOUNTS.INTEREST_CALCULATION_BASIS%TYPE;
350 l_rounding_type XTR_BANK_ACCOUNTS.ROUNDING_TYPE%TYPE;
351 l_day_count_type XTR_BANK_ACCOUNTS.DAY_COUNT_TYPE%TYPE;
352 l_code_combination_id XTR_BANK_ACCOUNTS.CODE_COMBINATION_ID%TYPE;
353 l_party_code XTR_BANK_ACCOUNTS.PARTY_CODE%TYPE;
354
355 l_orig_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
356 l_new_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
357
358 l_updated_by XTR_DEALER_CODES.DEALER_CODE%TYPE;
359
360 BEGIN
361 OPEN account_changed;
362 FETCH account_changed
363 INTO l_conc_account_id;
364 CLOSE account_changed;
365
366 IF (l_conc_account_id <> p_bank_account_ID) THEN
367 OPEN changed_account_details;
368 FETCH changed_account_details
369 INTO l_bank_code, l_year_calc_type,
370 l_int_calc_basis, l_rounding_type,
371 l_day_count_type, l_code_combination_id,
372 l_new_set_of_books_id;
373 CLOSE changed_account_details;
374
375 BEGIN
376 SELECT set_of_books_id
377 INTO l_orig_set_of_books_id
378 FROM XTR_PARTY_INFO xpin, XTR_BANK_ACCOUNTS xba
379 WHERE xpin.party_code = xba.party_code
380 AND ce_bank_account_id
381 = l_conc_account_id
382 AND xpin.party_type = 'C';
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 x_return_status := 'N';
387 RAISE FND_API.G_EXC_ERROR;
388 END;
389 OPEN DEALER_DETAILS;
390 FETCH DEALER_DETAILS
391 INTO l_updated_by;
392 CLOSE DEALER_DETAILS;
393
394 IF l_new_set_of_books_id = l_orig_set_of_books_id THEN
395 BEGIN
396 UPDATE XTR_BANK_ACCOUNTS
397 SET bank_code = l_bank_code,
398 year_calc_type = l_year_calc_type,
399 interest_calculation_basis = l_int_calc_basis,
400 rounding_type = l_rounding_type,
401 day_count_type = l_day_count_type,
402 code_combination_id = l_code_combination_id,
403 updated_by = nvl(l_updated_by,
404 FND_GLOBAL.user_id),
405 updated_on = sysdate
406 WHERE cashpool_id = p_cashpool_id
407 AND setoff_account_yn = 'Y';
408 EXCEPTION
409 WHEN OTHERS THEN
410 x_return_status := 'N';
411 RAISE FND_API.G_EXC_ERROR;
412 END;
413 ELSE
414 BEGIN
415 UPDATE XTR_BANK_ACCOUNTS
416 SET bank_code = l_bank_code,
417 year_calc_type = l_year_calc_type,
418 interest_calculation_basis = l_int_calc_basis,
419 rounding_type = l_rounding_type,
420 day_count_type = l_day_count_type,
421 updated_by = nvl(l_updated_by,
422 FND_GLOBAL.user_id),
423 updated_on = sysdate
424 WHERE cashpool_id = p_cashpool_id
425 AND setoff_account_yn = 'Y';
426 EXCEPTION
427 WHEN OTHERS THEN
428 x_return_status := 'N';
429 RAISE FND_API.G_EXC_ERROR;
430 END;
431 END IF;
432 END IF;
433 x_return_status := 'Y';
434 END update_xtr_bank_account;
435
436 END XTR_NOTIONAL_BANK_ACCOUNTS;
437