[Home] [Help]
PACKAGE BODY: APPS.CE_LEVELING_UTILS
Source
1 PACKAGE BODY CE_LEVELING_UTILS as
2 /* $Header: celutilb.pls 120.16.12020000.5 2013/04/12 09:38:52 vnetan ship $ */
3
4 -----------------------------------------------------------------------------
5 -- 16462154: Debug Logging Standards uptake
6 G_debug_package VARCHAR2(32) := 'CE_LEVELING_UTILS';
7
8 /* Procedure to log messages to FND_LOG */
9 PROCEDURE log_msg(
10 p_debug_proc IN VARCHAR2,
11 p_text IN VARCHAR2,
12 p_log_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT
13 )IS
14 BEGIN
15 IF(p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
16 THEN
17 FND_LOG.STRING(
18 p_log_level,
19 'ce.plsql.'||G_debug_package||'.'||p_debug_proc,
20 p_text);
21 END IF;
22 END log_msg;
23 -----------------------------------------------------------------------------
24
25 /* ---------------------------------------------------------------------
26 | PUBLIC PROCEDURE |
27 | Generate_Button |
28 | |
29 | DESCRIPTION |
30 | This procedure is called when the 'Generate' button |
31 | is pressed from the Cash Leveling Proposal confirmation page. |
32 | In turn it submits a concurrent request that runs the |
33 | 'Generate Cash Leveling Fund Transfers' concurrent program. |
34 | |
35 | CALLED BY |
36 | OA Controller Classes |
37 | |
38 | REQUIRES |
39 | |
40 | HISTORY |
41 | 15-DEC-2004 Created Sunil Poonen |
42 --------------------------------------------------------------------- */
43 PROCEDURE Generate_Button (
44 p_as_of_date DATE,
45 p_accept_limit_error VARCHAR2,
46 p_run_id VARCHAR2
47 ) IS
48 l_request_id NUMBER;
49 BEGIN
50 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
51 'CE',
52 'CECLEVEL',
53 '',
54 '',
55 NULL,
56 to_char(to_date(p_as_of_date,'DD-MM-RRRR'),'YYYY/MM/DD HH24:MI:SS'),
57 p_accept_limit_error,
58 p_run_id,
59 fnd_global.local_chr(0));
60 END Generate_Button;
61
62 /* ---------------------------------------------------------------------
63 | PUBLIC PROCEDURE |
64 | Cash_Leveling |
65 | |
66 | DESCRIPTION |
67 | This procedure serves as the executable of the |
68 | 'Generate Cash Leveling Fund Transfers' concurrent program. |
69 | This procedure will generate a fund transfer for each |
70 | proposed transfer in CE_PROPOSED_TRANSFERS. |
71 | |
72 | CALLED BY |
73 | 'Generate Cash Leveling Fund Transfers' concurrent program |
74 | |
75 | REQUIRES |
76 | |
77 | HISTORY |
78 | 15-JUN-2004 Created Sunil Poonen |
79 --------------------------------------------------------------------- */
80 PROCEDURE Cash_Leveling (
81 errbuf OUT NOCOPY VARCHAR2,
82 retcode OUT NOCOPY NUMBER,
83 p_as_of_date IN VARCHAR2,
84 p_accept_limit_error IN VARCHAR2,
85 p_run_id IN VARCHAR2
86 ) IS
87 CURSOR C_proposed_transfers(c_run_id VARCHAR2)
88 IS
89 SELECT proposed_transfer_id,
90 sub_account_id,
91 conc_account_id,
92 transfer_amount,
93 cashpool_id
94 FROM ce_proposed_transfers
95 WHERE status = c_run_id;
96
97 l_from_bank_account_id NUMBER;
98 l_to_bank_account_id NUMBER;
99 l_deal_type VARCHAR2(3);
100 l_deal_no NUMBER;
101 l_trx_number NUMBER;
102 l_offset_deal_no NUMBER;
103 l_offset_trx_number NUMBER;
104 l_effective_date_from DATE;
105 l_effective_date_to DATE;
106 l_success_flag VARCHAR2(1);
107 l_msg_count NUMBER;
108 l_cashflows_created_flag VARCHAR2(1);
109 req_id NUMBER;
110 request_id NUMBER;
111 reqid VARCHAR2(30);
112 number_of_copies NUMBER;
113 printer VARCHAR2(30);
114 print_style VARCHAR2(30);
115 save_output_flag VARCHAR2(30);
116 save_output_bool BOOLEAN;
117
118 -- 16462154: Added
119 l_debug_proc VARCHAR2(30) := 'Cash_Leveling';
120 BEGIN
121 log_msg(l_debug_proc,'begin',FND_LOG.LEVEL_PROCEDURE);
122 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
123 CEP_STANDARD.init_security;
124
125 -- Get original request id
126 fnd_profile.get('CONC_REQUEST_ID', reqid);
127 request_id := to_number(reqid);
128
129 -- Generate fund transfer for each proposed transfer
130 FOR p_transfer in C_proposed_transfers(p_run_id)
131 LOOP
132 IF p_transfer.transfer_amount <> 0
133 THEN
134 SELECT nvl(effective_date_from,to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS')),
135 nvl(effective_date_to,to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS'))
136 INTO l_effective_date_from, l_effective_date_to
137 FROM ce_cashpools
138 WHERE cashpool_id = p_transfer.cashpool_id;
139
140 IF ( to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS') >= l_effective_date_from
141 AND to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS') <= l_effective_date_to)
142 THEN
143 IF p_transfer.transfer_amount > 0
144 THEN
145 l_from_bank_account_id := p_transfer.conc_account_id;
146 l_to_bank_account_id := p_transfer.sub_account_id;
147 ELSE
148 l_from_bank_account_id := p_transfer.sub_account_id;
149 l_to_bank_account_id := p_transfer.conc_account_id;
150 END IF;
151
152 CE_LEVELING_UTILS.Generate_Fund_Transfer(
153 X_from_bank_account_id => l_from_bank_account_id,
154 X_to_bank_account_id => l_to_bank_account_id,
155 X_cashpool_id => p_transfer.cashpool_id,
156 X_amount => abs(p_transfer.transfer_amount),
157 X_transfer_date => to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS'),
158 X_settlement_authorized => null,
159 X_accept_limit_error => p_accept_limit_error,
160 X_request_id => request_id,
161 X_deal_type => l_deal_type,
162 X_deal_no => l_deal_no,
163 X_trx_number => l_trx_number,
164 X_offset_deal_no => l_offset_deal_no,
165 X_offset_trx_number => l_offset_trx_number,
166 X_success_flag => l_success_flag,
167 X_statement_line_id => to_number(null),
168 X_msg_count => l_msg_count,
169 X_cashflows_created_flag => l_cashflows_created_flag,
170 X_called_by_flag => 'L');
171 ELSE -- cash pool (p_as_of_date) is not effective
172 DELETE ce_proposed_transfers
173 WHERE proposed_transfer_id = p_transfer.proposed_transfer_id;
174 COMMIT;
175 END IF;
176 ELSE -- transfer amount = 0
177 DELETE ce_proposed_transfers
178 WHERE proposed_transfer_id = p_transfer.proposed_transfer_id;
179 COMMIT;
180 END IF;
181 END LOOP;
182
183 -- Purge all submitted transfers
184 DELETE FROM ce_proposed_transfers
185 WHERE status = p_run_id;
186 COMMIT;
187
188 -- Launch the Cash Leveling Execution Report
189
190 -- Get print options
191 log_msg(l_debug_proc,'Request Id is ' || request_id);
192 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(
193 request_id, number_of_copies, print_style, printer, save_output_flag))
194 THEN
195 log_msg(l_debug_proc,'Message: get print options failed');
196 ELSE
197 IF (save_output_flag = 'Y')
198 THEN
199 save_output_bool := TRUE;
200 ELSE
201 save_output_bool := FALSE;
202 END IF;
203
204 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES (
205 'CE','CECLEXER', printer, print_style, save_output_flag))
206 THEN
207 log_msg(l_debug_proc,'Message: get print options failed');
208 END IF;
209
210 -- Set print options
211 IF (NOT FND_REQUEST.set_print_options(
212 printer, print_style, number_of_copies, save_output_bool))
213 THEN
214 log_msg(l_debug_proc,'Set print options failed');
215 END IF;
216 END IF;
217
218 -- Submit the concurrent request for the Cash Leveling Execution Report
219 -- and pass in the original request_id
220 req_id := FND_REQUEST.SUBMIT_REQUEST('CE','CECLEXER',NULL,trunc(sysdate),FALSE,request_id);
221
222 log_msg(l_debug_proc,'end',FND_LOG.LEVEL_PROCEDURE);
223 EXCEPTION
224 WHEN OTHERS THEN
225 log_msg(l_debug_proc,'EXCEPTION:'||sqlerrm, FND_LOG.LEVEL_EXCEPTION);
226 RAISE;
227 END Cash_Leveling;
228
229
230 /* --------------------------------------------------------------------
231 | PUBLIC PROCEDURE |
232 | Generate_Fund_Transfer |
233 | |
234 | DESCRIPTION |
235 | This shared procedure will be used by both ZBA and |
236 | Cash Leveling to generate a fund transfer by calling |
237 | Treasury's deal creation APIs |
238 | |
239 | CALLED BY |
240 | CE_LEVELING_UTILS.Cash_Leveling |
241 | |
242 | REQUIRES |
243 | |
244 | HISTORY |
245 | 15-JUN-2004 Sunil Poonen Created |
246 | 27-AUG-2012 vnetan Bug 14118443: ZBA Changes |
247 ---------------------------------------------------------------------*/
248 PROCEDURE Generate_Fund_Transfer (
249 X_from_bank_account_id NUMBER,
250 X_to_bank_account_id NUMBER,
251 X_cashpool_id NUMBER,
252 X_amount NUMBER,
253 X_transfer_date DATE,
254 X_settlement_authorized VARCHAR2,
255 X_accept_limit_error VARCHAR2,
256 X_request_id NUMBER,
257 X_deal_type OUT NOCOPY VARCHAR2,
258 X_deal_no OUT NOCOPY NUMBER,
259 X_trx_number OUT NOCOPY NUMBER,
260 X_offset_deal_no OUT NOCOPY NUMBER,
261 X_offset_trx_number OUT NOCOPY NUMBER,
262 X_success_flag OUT NOCOPY VARCHAR2,
263 X_statement_line_id NUMBER,
264 X_msg_count OUT NOCOPY NUMBER,
265 X_cashflows_created_flag OUT NOCOPY VARCHAR2,
266 X_called_by_flag VARCHAR2
267 ) IS
268 l_from_le_id NUMBER(15);
269 l_to_le_id NUMBER(15);
270 l_conc_account_id NUMBER(15);
271 l_company_account_id NUMBER(15);
272 l_party_account_id NUMBER(15);
273 l_currency_code VARCHAR2(15);
274 l_action_code VARCHAR2(3);
275 l_deal_type VARCHAR2(3);
276 l_deal_no NUMBER;
277 l_trx_number NUMBER;
278 l_offset_deal_no NUMBER;
279 l_offset_trx_number NUMBER;
280 l_success_flag VARCHAR2(1);
281 l_bat_profile VARCHAR2(30);
282 l_payment_details_from VARCHAR2(4);
283 l_count NUMBER;
284 l_trx_subtype VARCHAR2(30);
285 l_result VARCHAR2(7);
286 l_msg_count NUMBER;
287 l_authorization_bat CE_CASHPOOLS.authorization_bat%TYPE;
288 l_trx_type CE_STATEMENT_LINES.trx_type%TYPE;
289 l_from_bank_account_id CE_BANK_ACCOUNTS.bank_Account_id%TYPE;
290 l_to_bank_Account_id CE_BANK_ACCOUNTS.bank_account_id%TYPE;
291
292 --
293 -- 16462154: Added
294 --
295 l_message_name FND_NEW_MESSAGES.message_name%TYPE;
296 l_message_text FND_NEW_MESSAGES.message_text%TYPE;
297 l_app_short_name FND_APPLICATION.application_short_name%TYPE;
298 l_debug_proc VARCHAR2(30) := 'Generate_Fund_Transfer';
299 BEGIN
300 log_msg(l_debug_proc,'begin',FND_LOG.LEVEL_PROCEDURE);
301
302 l_bat_profile := FND_PROFILE.VALUE('CE_BANK_ACCOUNT_TRANSFERS');
303 log_msg(l_debug_proc,'l_bat_profile='||l_bat_profile);
304
305 IF (l_bat_profile = 'XTR') THEN
306 SELECT authorization_bat
307 INTO l_authorization_bat
308 FROM ce_cashpools
309 WHERE cashpool_id = X_cashpool_id;
310
311 log_msg(l_debug_proc,'l_authorization_bat='||nvl(l_authorization_bat,'<null>'));
312 IF (l_authorization_bat IS NOT NULL)
313 THEN
314 -- cashpool was setup for CE
315 CE_ZBA_DEAL_INF_PKG.insert_row (
316 CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
317 CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
318 'CE_INVALID_CASHPOOL_FOR_XTR');
319 x_success_flag := 'FAIL';
320 RETURN;
321 END IF;
322 END IF;
323
324 -- Get company account, party account, and action code
325 -- Company account = Concentration account
326 SELECT decode(nvl(single_conc_account_flag,'Y'),'Y',conc_account_id, fund_conc_account_id),
327 currency_code
328 INTO l_conc_account_id,
329 l_currency_code
330 FROM ce_cashpools
331 WHERE cashpool_id = X_cashpool_id;
332
333 --bug5346601
334 l_from_bank_account_id := X_from_Bank_Account_id;
335 l_to_bank_account_id := X_to_Bank_Account_id;
336 --bug5346601
337
338 --bug5335122
339 IF X_from_bank_account_id = l_conc_account_id
340 THEN
341 l_company_account_id := X_from_bank_account_id;
342 l_party_account_id := X_to_bank_account_id;
343 l_action_code := 'PAY';
344 ELSE
345 l_company_account_id := X_to_bank_account_id;
346 l_party_account_id := X_from_bank_account_id;
347 l_action_code := 'REC';
348 END IF;
349 --bug5335122
350
351 log_msg(l_debug_proc,'X_called_by_flag='||X_called_by_flag);
352 IF X_called_by_flag = 'Z'
353 THEN
354 SELECT trx_type INTO l_trx_type
355 FROM ce_statement_lines
356 WHERE statement_line_id=X_statement_line_id;
357
358 -- Bug5122576. X_from_bank_account_id is the statement line's bank
359 -- account id and X_to_bank_account_id is the offset
360 -- bank account. The real from and to bank accounts
361 -- are determined here and stored in the l_ variables.
362 -- This is only for ZBA. For CL the accounts are determined above.
363 log_msg(l_debug_proc,'l_trx_type='||l_trx_type);
364 log_msg(l_debug_proc,'l_conc_account_id='||l_conc_account_id);
365 log_msg(l_debug_proc,'X_from_bank_account_id='||X_from_bank_account_id);
366 log_msg(l_debug_proc,'X_to_bank_account_id='||X_to_bank_account_id);
367
368 IF X_from_bank_account_id = l_conc_account_id
369 THEN
370 IF l_trx_type = 'SWEEP_OUT'
371 THEN
372 -- commented for bug 12349973
373 -- l_company_account_id := X_from_bank_account_id;
374 -- l_party_account_id := X_to_bank_account_id;
375 l_action_code := 'PAY';
376 l_from_bank_account_id := X_from_Bank_Account_id;
377 l_to_bank_account_id := X_to_Bank_Account_id;
378 ELSE -- l_trx_type <> 'SWEEP_OUT'
379 -- commented for bug 12349973
380 -- l_company_account_id := X_to_bank_account_id;
381 -- l_party_account_id := X_from_bank_account_id;
382 l_action_code := 'REC';
383 l_from_bank_account_id := X_to_Bank_Account_id;
384 l_to_bank_account_id := X_from_Bank_Account_id;
385 END IF;
386 ELSE -- X_from_bank_account_id <> l_conc_account_id
387 IF l_trx_type = 'SWEEP_OUT'
388 THEN
389 l_from_bank_account_id := X_from_Bank_Account_id;
390 l_to_bank_account_id := X_to_Bank_Account_id;
391 ELSE -- l_trx_type <> 'SWEEP_OUT'
392 l_from_bank_account_id := X_to_Bank_Account_id;
393 l_to_bank_account_id := X_from_Bank_Account_id;
394 END IF;
395 END IF;
396 log_msg(l_debug_proc,'l_from_bank_account_id='||l_from_bank_account_id);
397 log_msg(l_debug_proc,'l_to_bank_account_id='||l_to_bank_account_id);
398 END IF;
399
400 IF l_bat_profile = 'CE'
401 THEN
402 log_msg(l_debug_proc,'Creating BAT ....');
403 -- Bug 14118443: BAT should always created from direction 'PAYMENT'
404 -- The source account will always be the payment account
405 l_payment_details_from := 'SRC';
406 -- SELECT count(1)
407 -- INTO l_count
408 -- FROM ce_cashpool_sub_accts
409 -- WHERE cashpool_id = X_cashpool_id
410 -- AND account_id = X_from_bank_account_id
411 -- AND type in ('CONC','INV','FUND');
412 --
413 -- IF l_count = 0 THEN
414 -- l_payment_details_from := 'SRC';
415 -- ELSE
416 -- l_payment_details_from := 'DEST';
417 -- END IF;
418
419 log_msg(l_debug_proc,'l_payment_details_from='||l_payment_details_from);
420
421 -- Call BAT API to create the transfer
422 -- Bug 14118443: X_from_bank_account_id is the statement line's bank
423 -- account id and X_to_bank_account_id is the offset bank account.
424 -- The real from (source=outflow) and to (destination=inflow) bank
425 -- accounts have been determined above and stored in the l_ variables.
426 CE_BAT_API.create_transfer(
427 p_called_by_1 => X_called_by_flag,
428 --p_source_ba_id => X_from_bank_account_id, -- 14118443
429 --p_destination_ba_id => X_to_bank_account_id, -- 14118443
430 p_source_ba_id => l_from_bank_account_id, -- 14118443
431 p_destination_ba_id => l_to_bank_account_id, -- 14118443
432 p_statement_line_id => X_statement_line_id,
433 p_cashpool_id => X_cashpool_id,
434 p_transfer_amount => X_amount,
435 p_payment_details_from => l_payment_details_from,
436 p_as_of_date => X_transfer_date,
437 p_cashflows_created_flag => X_cashflows_created_flag,
438 p_result => l_result,
439 p_msg_count => l_msg_count,
440 p_trxn_reference_number => l_trx_number);
441
442 SELECT tst.transaction_sub_type_name
443 INTO l_trx_subtype
444 FROM ce_trxns_subtype_codes tst, ce_cashpools cp
445 WHERE cp.trxn_subtype_code_id = tst.trxn_subtype_code_id(+)
446 AND cp.cashpool_id = X_cashpool_id;
447
448 IF l_result = 'SUCCESS' THEN
449 l_success_flag := 'Y';
450 ELSE
451 l_success_flag := 'N';
452 END IF;
453
454 IF l_msg_count = 0 THEN
455 l_count := 1;
456 ELSE
457 l_count := l_msg_count;
458 END IF;
459
460 ELSE -- bat_profile = 'XTR'
461 log_msg(l_debug_proc,'Creating XTR Deal ....');
462 SELECT account_owner_org_id
463 INTO l_from_le_id
464 FROM ce_bank_accounts
465 WHERE bank_account_id = X_from_bank_account_id;
466
467 SELECT account_owner_org_id
468 INTO l_to_le_id
469 FROM ce_bank_accounts
470 WHERE bank_account_id = X_to_bank_account_id;
471
472 -- Call the relevant XTR wrapper API based on the deal type
473 IF l_from_le_id = l_to_le_id
474 THEN -- Inter-Account Transfer (IAC)
475 l_deal_type := 'IAC';
476 XTR_WRAPPER_API_P.IAC_GENERATION(
477 X_cashpool_id,
478 l_from_bank_account_id,
479 l_to_bank_account_id,
480 X_transfer_date,
481 X_amount,
482 l_trx_number,
483 l_success_flag,
484 X_called_by_flag);
485 ELSE -- Intercompany Funding (IG)
486 l_deal_type := 'IG';
487 XTR_WRAPPER_API_P.IG_GENERATION(
488 X_cashpool_id,
489 l_company_account_id,
490 l_party_account_id,
491 l_currency_code,
492 X_transfer_date,
493 X_amount,
494 l_action_code,
495 X_accept_limit_error,
496 l_deal_no,
497 l_trx_number,
498 l_offset_deal_no,
499 l_offset_trx_number,
500 l_success_flag,
501 X_called_by_flag);
502
503 END IF;
504
505 l_count := 1;
506 END IF; -- end bat_profile check
507
508 -- Populate CE_LEVELING_MESSAGES
509 log_msg(l_debug_proc,'Populating CE_LEVELING_MESSAGES..');
510 IF X_called_by_flag = 'L'
511 THEN
512 FOR i IN 1..l_count
513 LOOP
514 --
515 -- 16462154: Modified code to store message name and message text
516 -- in CE_LEVELING_MESSAGES table. Earlier message name was being
517 -- passed as NULL.
518 --
519 log_msg(l_debug_proc,'Message #'||l_count);
520 FND_MESSAGE.parse_encoded(
521 encoded_message => FND_MSG_PUB.get(1, FND_API.G_TRUE),
522 app_short_name => l_app_short_name,
523 message_name => l_message_name);
524 l_message_text := FND_MSG_PUB.get(1, FND_API.G_FALSE);
525
526 log_msg(l_debug_proc,'Message Name='||l_message_name);
527 log_msg(l_debug_proc,'Message Text='||l_message_text);
528
529 INSERT INTO ce_leveling_messages(
530 /*01*/ leveling_message_id,
531 /*02*/ request_id,
532 /*03*/ sub_account_id,
533 /*04*/ conc_account_id,
534 /*05*/ transfer_amount,
535 /*06*/ message_name,
536 /*07*/ message_text,
537 /*08*/ deal_type,
538 /*09*/ deal_no,
539 /*10*/ trx_number,
540 /*11*/ offset_deal_no,
541 /*12*/ offset_trx_number,
542 /*13*/ success_flag,
543 /*14*/ cashpool_id,
544 /*15*/ created_by,
545 /*16*/ creation_date,
546 /*17*/ last_updated_by,
547 /*18*/ last_update_date,
548 /*19*/ last_update_login
549 ) VALUES (
550 /*01*/ CE_LEVELING_MESSAGES_S.nextval,
551 /*02*/ nvl(X_request_id,-1),
552 /*03*/ l_party_account_id,
553 /*04*/ l_company_account_id,
554 /*05*/ X_amount,
555 /*06*/ l_message_name,
556 /*07*/ l_message_text,
557 /*08*/ l_deal_type,
558 /*09*/ l_deal_no,
559 /*10*/ l_trx_number,
560 /*11*/ l_offset_deal_no,
561 /*12*/ l_offset_trx_number,
562 /*13*/ l_success_flag,
563 /*14*/ X_cashpool_id,
564 /*15*/ nvl(fnd_global.user_id,-1),
565 /*16*/ sysdate,
566 /*17*/ nvl(fnd_global.user_id,-1),
567 /*18*/ sysdate,
568 /*19*/ nvl(fnd_global.user_id,-1)
569 );
570 FND_MSG_PUB.delete_msg(1);
571 COMMIT;
572 END LOOP;
573 ELSE -- X_called_by_flag <> 'L'
574 X_deal_type := l_deal_type;
575 X_deal_no := l_deal_no;
576 X_trx_number := l_trx_number;
577 X_offset_deal_no := l_offset_deal_no;
578 X_offset_trx_number := l_offset_trx_number;
579 X_success_flag := l_success_flag;
580 X_msg_count := l_msg_count;
581 END IF;
582 log_msg(l_debug_proc,'end',FND_LOG.LEVEL_PROCEDURE);
583 EXCEPTION
584 WHEN OTHERS THEN
585 log_msg(l_debug_proc,'EXCEPTION:'||sqlerrm, FND_LOG.LEVEL_EXCEPTION);
586 RAISE;
587 END Generate_Fund_Transfer;
588
589 /* --------------------------------------------------------------------
590 | PUBLIC PROCEDRE |
591 | Populate_Nested_Accounts |
592 | |
593 | DESCRIPTION |
594 | This procedure populates the sub-accounts of nested cash pools |
595 | as sub-accounts of the parent cash pool as well |
596 | |
597 | CALLED BY |
598 | OA Controller Classes |
599 | REQUIRES |
600 | |
601 | HISTORY |
602 | 15-JUN-2004 Created Sunil Poonen |
603 ---------------------------------------------------------------------*/
604 PROCEDURE Populate_Nested_Accounts(
605 p_parent_cashpool_id NUMBER,
606 p_cashpool_id NUMBER) IS
607 BEGIN
608
609 UPDATE ce_cashpools
610 SET parent_cashpool_id = p_parent_cashpool_id
611 WHERE cashpool_id = p_cashpool_id;
612
613 INSERT INTO ce_cashpool_sub_accts(
614 /*01*/cashpool_sub_acct_id,
615 /*02*/cashpool_id,
616 /*03*/type,
617 /*04*/account_id,
618 /*05*/party_code,
619 /*06*/legal_entity_id,
620 /*07*/nested_parent_pool_id,
621 /*08*/created_by,
622 /*09*/creation_date,
623 /*10*/last_updated_by,
624 /*11*/last_update_date,
625 /*12*/last_update_login)
626 SELECT
627 /*01*/CE_CASHPOOL_SUB_ACCTS_S.nextval,
628 /*02*/p_parent_cashpool_id,
629 /*03*/'NEST',
630 /*04*/sub.account_id,
631 /*05*/sub.party_code,
632 /*06*/sub.legal_entity_id,
633 /*07*/sub.cashpool_id,
634 /*08*/nvl(fnd_global.user_id,-1),
635 /*09*/sysdate,
636 /*10*/nvl(fnd_global.user_id,-1),
637 /*11*/sysdate,
638 /*12*/nvl(fnd_global.user_id,-1)
639 FROM ce_cashpool_sub_accts sub
640 WHERE sub.cashpool_id = p_cashpool_id
641 AND sub.type <> 'POOL';
642
643 END Populate_Nested_Accounts;
644
645 /* ---------------------------------------------------------------------
646 | PUBLIC PROCEDRE |
647 | Delete_Sub_Accounts |
648 | |
649 | DESCRIPTION |
650 | This procedure deletes the sub-accounts of a cash pool |
651 | prior to re-populating |
652 | |
653 | CALLED BY |
654 | OA Controller Classes |
655 | REQUIRES |
656 | |
657 | HISTORY |
658 | 14-SEP-2004 Created Sunil Poonen |
659 --------------------------------------------------------------------- */
660 PROCEDURE Delete_Sub_Accounts(p_cashpool_id NUMBER) IS
661 BEGIN
662
663 UPDATE ce_cashpools
664 SET parent_cashpool_id = null
665 WHERE parent_cashpool_id = p_cashpool_id;
666
667 DELETE ce_cashpool_sub_accts
668 WHERE cashpool_id = p_cashpool_id;
669
670 END Delete_Sub_Accounts;
671
672 /* --------------------------------------------------------------------
673 | PUBLIC PROCEDRE |
674 | Update_Parent_Nested_Accounts |
675 | |
676 | DESCRIPTION |
677 | This procedure updates the parent cash pool's nested |
678 | sub accounts of the current child cash pool to reflect |
679 | any changes to the current child cash pool's sub accounts |
680 | |
681 | CALLED BY |
682 | OA Controller Classes |
683 | REQUIRES |
684 | |
685 | HISTORY |
686 | 11-JAN-2005 Created Sunil Poonen |
687 ---------------------------------------------------------------------*/
688 PROCEDURE Update_Parent_Nested_Accounts(
689 p_cashpool_id NUMBER,
690 p_parent_cashpool_id NUMBER
691 ) IS
692 BEGIN
693
694 DELETE FROM ce_cashpool_sub_accts
695 WHERE cashpool_id = p_parent_cashpool_id
696 AND nested_parent_pool_id = p_cashpool_id;
697
698 INSERT INTO ce_cashpool_sub_accts(
699 /*01*/cashpool_sub_acct_id,
700 /*02*/cashpool_id,
701 /*03*/type,
702 /*04*/account_id,
703 /*05*/party_code,
704 /*06*/legal_entity_id,
705 /*07*/nested_parent_pool_id,
706 /*08*/created_by,
707 /*09*/creation_date,
708 /*10*/last_updated_by,
709 /*11*/last_update_date,
710 /*12*/last_update_login)
711 SELECT
712 /*01*/CE_CASHPOOL_SUB_ACCTS_S.nextval,
713 /*02*/p_parent_cashpool_id,
714 /*03*/'NEST',
715 /*04*/sub.account_id,
716 /*05*/sub.party_code,
717 /*06*/sub.legal_entity_id,
718 /*07*/sub.cashpool_id,
719 /*08*/nvl(fnd_global.user_id,-1),
720 /*09*/sysdate,
721 /*10*/nvl(fnd_global.user_id,-1),
722 /*11*/sysdate,
723 /*12*/nvl(fnd_global.user_id,-1)
724 FROM ce_cashpool_sub_accts sub
725 WHERE sub.cashpool_id = p_cashpool_id
726 AND sub.type <> 'POOL';
727
728 END Update_Parent_Nested_Accounts;
729
730 /* ---------------------------------------------------------------------
731 | PUBLIC PROCEDRE |
732 | Populate_Target_Balances |
733 | |
734 | DESCRIPTION |
735 | This procedure populates target balances for a particular |
736 | sub-account of a cash pool |
737 | |
738 | CALLED BY |
739 | OA Controller Classes |
740 | REQUIRES |
741 | |
742 | HISTORY |
743 | 15-JUN-2004 Created Sunil Poonen |
744 --------------------------------------------------------------------- */
745 PROCEDURE Populate_Target_Balances(
746 p_bank_account_id NUMBER,
747 p_min_target_balance NUMBER,
748 p_max_target_balance NUMBER,
749 p_min_payment_amt NUMBER,
750 p_min_receipt_amt NUMBER,
751 p_round_factor VARCHAR2,
752 p_round_rule VARCHAR2
753 ) IS
754 BEGIN
755 UPDATE ce_bank_accounts
756 SET min_target_balance = nvl(p_min_target_balance,to_number(null)),
757 max_target_balance = nvl(p_max_target_balance,to_number(null)),
758 cashpool_min_payment_amt = nvl(p_min_payment_amt,0),
759 cashpool_min_receipt_amt = nvl(p_min_receipt_amt,0),
760 cashpool_round_factor = to_number(nvl(p_round_factor,'0')),
761 cashpool_round_rule = nvl(p_round_rule,'R')
762 WHERE bank_account_id = p_bank_account_id;
763 END Populate_Target_Balances;
764
765 /* ---------------------------------------------------------------------
766 | PUBLIC PROCEDRE |
767 | Populate_BAT_Payment_Details |
768 | |
769 | DESCRIPTION |
770 | This procedure populates BAT payment details for a particular |
771 | sub-account of a cash pool |
772 | |
773 | CALLED BY |
774 | OA Controller Classes |
775 | REQUIRES |
776 | |
777 | HISTORY |
778 | 15-JUL-2005 Created Sunil Poonen |
779 --------------------------------------------------------------------- */
780 PROCEDURE Populate_BAT_Payment_Details(
781 p_bank_account_id NUMBER,
782 p_payment_method_code VARCHAR2,
783 p_bank_charge_bearer_code VARCHAR2,
784 p_payment_reason_code VARCHAR2,
785 p_payment_reason_comments VARCHAR2,
786 p_remittance_message1 VARCHAR2,
787 p_remittance_message2 VARCHAR2,
788 p_remittance_message3 VARCHAR2
789 ) IS
790 BEGIN
791 UPDATE ce_bank_accounts
792 SET pool_payment_method_code = p_payment_method_code,
793 pool_bank_charge_bearer_code = p_bank_charge_bearer_code,
794 pool_payment_reason_code = p_payment_reason_code,
795 pool_payment_reason_comments = p_payment_reason_comments,
796 pool_remittance_message1 = p_remittance_message1,
797 pool_remittance_message2 = p_remittance_message2,
798 pool_remittance_message3 = p_remittance_message3
799 WHERE bank_account_id = p_bank_account_id;
800 END Populate_BAT_Payment_Details;
801
802
803 /* --------------------------------------------------------------------
804 | PUBLIC PROCEDRE |
805 | Update_Bank_Account_Id |
806 | |
807 | DESCRIPTION |
808 | This procedure updates the bank account id in ce_cashpools |
809 | and ce_cashpool_sub_accts when a bank account that is also a |
810 | cash pool sub-account is "linked" or "unlinked" with AP |
811 | |
812 | CALLED BY |
813 | OA Controller Classes |
814 | REQUIRES |
815 | |
816 | HISTORY |
817 | 15-JUN-2004 Created Sunil Poonen |
818 ---------------------------------------------------------------------*/
819 PROCEDURE Update_Bank_Account_Id(
820 p_old_bank_account_id NUMBER,
821 p_new_bank_account_id NUMBER
822 ) IS
823 BEGIN
824 UPDATE ce_cashpool_sub_accts
825 SET account_id = p_new_bank_account_id
826 WHERE account_id = p_old_bank_account_id
827 AND type <> 'POOL';
828
829 UPDATE ce_cashpool_sub_accts
830 SET conc_account_id = p_new_bank_account_id
831 WHERE conc_account_id = p_old_bank_account_id;
832
833 UPDATE ce_cashpool_sub_accts
834 SET inv_conc_account_id = p_new_bank_account_id
835 WHERE inv_conc_account_id = p_old_bank_account_id;
836
837 UPDATE ce_cashpool_sub_accts
838 SET fund_conc_account_id = p_new_bank_account_id
839 WHERE fund_conc_account_id = p_old_bank_account_id;
840
841 UPDATE ce_cashpools
842 SET conc_account_id = p_new_bank_account_id
843 WHERE conc_account_id = p_old_bank_account_id;
844
845 UPDATE ce_cashpools
846 SET inv_conc_account_id = p_new_bank_account_id
847 WHERE inv_conc_account_id = p_old_bank_account_id;
848
849 UPDATE ce_cashpools
850 SET fund_conc_account_id = p_new_bank_account_id
851 WHERE fund_conc_account_id = p_old_bank_account_id;
852
853 UPDATE ce_cp_worksheet_lines
854 SET bank_account_id = p_new_bank_account_id
855 WHERE bank_account_id = p_old_bank_account_id;
856
857 UPDATE ce_forecast_rows
858 SET bank_account_id = p_new_bank_account_id
859 WHERE bank_account_id = p_old_bank_account_id;
860 END Update_Bank_Account_Id;
861
862 /* ---------------------------------------------------------------------
863 | PUBLIC PROCEDRE |
864 | Match_Cashpool |
865 | |
866 | DESCRIPTION |
867 | This procedure finds the matching cash pool containing |
868 | a given pair of bank accounts |
869 | |
870 | CALLED BY |
871 | CE_ZBA_DEAL_GENERATION.zba_generation |
872 | REQUIRES |
873 | |
874 | HISTORY |
875 | 10-DEC-2004 Created Sunil Poonen |
876 | 24-AUG-2012 Bug 14512843 Chetak Kansara |
877 --------------------------------------------------------------------- */
878 FUNCTION Match_Cashpool(
879 p_header_bank_account_id IN NUMBER,
880 p_offset_bank_account_num IN VARCHAR2,
881 p_trx_type IN VARCHAR2,
882 p_trx_date IN DATE,
883 p_offset_bank_account_id OUT NOCOPY NUMBER,
884 p_cashpool_id OUT NOCOPY NUMBER
885 ) RETURN BOOLEAN IS
886
887 l_cashpool_id NUMBER(15);
888 l_type VARCHAR2(4);
889 l_offset_type VARCHAR2(4);
890 l_offset_bank_account_id NUMBER(15);
891 l_error_flag VARCHAR2(1);
892
893 -- 16462154: Added
894 l_debug_proc VARCHAR2(30) := 'match_cashpool';
895 BEGIN
896 log_msg(l_debug_proc,'begin',FND_LOG.LEVEL_PROCEDURE);
897 l_error_flag := 'N';
898
899 -- Bug 8489586 Start
900 SELECT DISTINCT cps. account_id -- Bug 14512843: Added DISTINCT
901 INTO l_offset_bank_account_id
902 FROM ce_bank_accounts cba, ce_cashpools cp, ce_cashpool_sub_accts cps
903 WHERE cba.bank_account_id in(cp.conc_account_id, -- Bug 14512483 Modified clause
904 cp.inv_conc_account_id,
905 cp.fund_conc_account_id)
906 AND cp.cashpool_id = cps.cashpool_id
907 AND cps.type in ('ACCT','CONC','INV','FUND') /* 14140123 */
908 AND cps.account_id IN
909 (SELECT bank_account_id
910 FROM ce_bank_accounts
911 WHERE bank_account_num = p_offset_bank_account_num);
912 -- Bug 8489586 End
913 log_msg(l_debug_proc,'l_offset_bank_account_id='||l_offset_bank_account_id);
914 log_msg(l_debug_proc,'p_trx_type='||p_trx_type);
915
916 IF p_trx_type = 'SWEEP_IN'
917 THEN -- Sweep in
918 SELECT cashpool_id, type
919 INTO l_cashpool_id, l_type
920 FROM ce_cashpool_sub_accts
921 WHERE type in ('ACCT','CONC','INV','FUND') -- Bug 14512483: Added FUND
922 AND account_id = p_header_bank_account_id
923 AND cashpool_id in
924 (SELECT cashpool_id
925 FROM ce_cashpools
926 WHERE sweeps_flag = 'Y'
927 AND NVL(effective_date_to,p_trx_date) >= p_trx_date);
928 log_msg(l_debug_proc,'l_cashpool_id='||l_cashpool_id||',l_type='||l_type);
929
930 SELECT type
931 INTO l_offset_type
932 FROM ce_cashpool_sub_accts
933 WHERE cashpool_id = l_cashpool_id
934 AND account_id = l_offset_bank_account_id
935 AND type in ('ACCT','CONC','FUND','INV'); -- Bug 14512843: Added INV
936 log_msg(l_debug_proc,'l_offset_type='||l_offset_type);
937
938 -- If neither account is a valid concentration account
939 -- or if both accounts are concentration accounts, raise error
940 IF ( (l_type = 'ACCT' AND l_offset_type = 'ACCT')
941 OR (l_type = 'INV' AND l_offset_type = 'FUND'))
942 THEN
943 l_error_flag := 'Y';
944 END IF;
945
946 ELSE -- Sweep out
947 SELECT cashpool_id, type
948 INTO l_cashpool_id, l_type
949 FROM ce_cashpool_sub_accts
950 WHERE type in ('ACCT','CONC','FUND','INV') -- Bug 14512843: Added INV
951 AND account_id = p_header_bank_account_id
952 AND cashpool_id in
953 (SELECT cashpool_id
954 FROM ce_cashpools
955 WHERE sweeps_flag = 'Y'
956 AND nvl(effective_date_to,p_trx_date) >= p_trx_date);
957 log_msg(l_debug_proc,'l_cashpool_id='||l_cashpool_id||',l_type='||l_type);
958
959 SELECT type
960 INTO l_offset_type
961 FROM ce_cashpool_sub_accts
962 WHERE cashpool_id = l_cashpool_id
963 AND account_id = l_offset_bank_account_id
964 AND type in ('ACCT','CONC','INV','FUND'); -- Bug 14512843: Added FUND
965 log_msg(l_debug_proc,'l_offset_type='||l_offset_type);
966
967 -- If neither account is a valid concentration account
968 -- or if both accounts are concentration accounts, raise error
969 IF ( (l_type = 'ACCT' AND l_offset_type = 'ACCT')
970 OR (l_type = 'FUND' AND l_offset_type = 'INV'))
971 THEN
972 l_error_flag := 'Y';
973 END IF;
974 END IF;
975 log_msg(l_debug_proc,'l_error_flag='||l_error_flag);
976
977 p_offset_bank_account_id := l_offset_bank_account_id;
978 p_cashpool_id := l_cashpool_id;
979 log_msg(l_debug_proc,'p_offset_bank_account_id='||p_offset_bank_account_id);
980 log_msg(l_debug_proc,'p_cashpool_id='||p_cashpool_id);
981
982 IF l_error_flag = 'Y' THEN
983 RETURN FALSE;
984 ELSE
985 RETURN TRUE;
986 END IF;
987 log_msg(l_debug_proc,'end',FND_LOG.LEVEL_PROCEDURE);
988 EXCEPTION
989 WHEN NO_DATA_FOUND THEN
990 log_msg(l_debug_proc,'EXCEPTION:'||sqlerrm, FND_LOG.LEVEL_EXCEPTION);
991 RETURN FALSE;
992 END Match_Cashpool;
993
994 END CE_LEVELING_UTILS;