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