[Home] [Help]
PACKAGE BODY: APPS.CE_BAT_UTILS
Source
1 PACKAGE BODY CE_BAT_UTILS as
2 /* $Header: cebtutlb.pls 120.20 2006/08/24 09:45:55 svali noship $ */
3
4 --Get the payment transaction
5
6 CURSOR f_row_cursor (p_reference_number NUMBER) IS
7
8 SELECT TRXN_REFERENCE_NUMBER,
9 TRXN_SUBTYPE_CODE_ID,
10 TRANSACTION_DATE,
11 ANTICIPATED_VALUE_DATE,
12 TRANSACTION_DESCRIPTION,
13 PAYMENT_CURRENCy_CODE,
14 PAYMENT_AMOUNT,
15 SOURCE_PARTY_ID,
16 SOURCE_LEGAL_ENTITY_ID,
17 SOURCE_BANK_ACCOUNT_ID,
18 DESTINATION_PARTY_ID,
19 DESTINATION_LEGAL_ENTITY_ID,
20 DESTINATION_BANK_ACCOUNT_ID,
21 CREATED_FROM_DIR,
22 CREATE_FROM_STMTLINE_ID,
23 BANK_TRXN_NUMBER,
24 PAYMENT_OFFSET_CCID,
25 RECEIPT_OFFSET_CCID
26 FROM
27 CE_PAYMENT_TRANSACTIONS
28 WHERE
29 trxn_reference_number = p_reference_number;
30
31
32 FUNCTION body_revision RETURN VARCHAR2 IS
33 BEGIN
34
35 RETURN '$Revision: 120.20 $';
36
37 END body_revision;
38
39 FUNCTION spec_revision RETURN VARCHAR2 IS
40 BEGIN
41
42 RETURN G_spec_revision;
43
44 END spec_revision;
45
46 PROCEDURE log(p_char varchar2) is
47 begin
48 --dbms_output.put_line(p_char);
49 cep_standard.debug(p_char);
50
51 end;
52
53 /*----------------------------------------------------------------------
54 | PUBLIC FUNCTION |
55 | get_exchange_rate_type |
56 | |
57 | DESCRIPTION |
58 | Returns the cashflow exchange rate type |
59 | |
60 -----------------------------------------------------------------------*/
61
62 FUNCTION get_exchange_rate_type(p_le_id number) RETURN VARCHAR2 IS
63
64 l_exchange_rate_type VARCHAR2(30);
65 BEGIN
66 log('>> get_exchange_rate_type');
67 select cashflow_exchange_rate_type
68 into l_exchange_rate_type
69 from ce_system_parameters
70 where legal_entity_id = p_le_id;
71 log(' type '||l_exchange_rate_type);
72 log('<< get_exchange_rate_type');
73 return (l_exchange_rate_type);
74 EXCEPTION
75 WHEN OTHERS THEN
76 Return null;
77
78 END get_exchange_rate_type;
79
80
81 /*----------------------------------------------------------------------
82 | PUBLIC PROCEDURE |
83 | get_exchange_rate_date |
84 | |
85 | DESCRIPTION |
86 | Returns the cashflow exchange date and cashflow exchange rate |
87 | In case of zba if its foreign scenario then if date type in |
88 | AVD or CLD then rate and date is null otherwise corresponding |
89 | rate will be populated. If international sceanrio then |
90 | rate/date/type from ce_statement_lines will be populated. |
91 | If bat/cl then in foreign scenario if date type in CFD or TRX |
92 | then corresponding date and rate is populated else rate and date |
93 | will be null |
94 ------------------------------------------------------------------------*/
95 PROCEDURE get_exchange_rate_date(p_ledger_id number,
96 p_bank_account_id number,
97 p_legal_entity_id number,
98 p_exch_type IN OUT NOCOPY varchar2,
99 p_exchange_date OUT NOCOPY date,
100 p_exchange_rate OUT NOCOPY number) IS
101
102 ledger_currency_code varchar2(15);
103 bank_currency_code varchar2(15);
104 p_exchange_date_type varchar2(10);
105 p_exch_rate number:=null;
106 p_exch_date date;
107
108 BEGIN
109 log('>>get_exchange_rate_date');
110
111 --get the ledger currency code
112 gl_mc_info.get_ledger_currency(p_ledger_id,ledger_currency_code);
113
114 --get the bank account currency
115 select currency_code
116 into bank_currency_code
117 from ce_bank_accounts
118 where bank_account_id = p_bank_account_id;
119
120 --get the exchange date type
121 p_exchange_date_type := get_exchange_date_type(p_legal_entity_id);
122
123
124
125 --check if zba or bat/cl
126 if(G_created_from_stmtline_id is not null) then --zba case
127
128 --check if foreign scenario or international scenario
129 if ((G_payment_curr_code = bank_currency_code) and
130 (bank_currency_code <> ledger_currency_code)) then --foreign scenario
131
132 if(p_exchange_date_type in ('AVD','CLD')) then
133 p_exch_date :=null;
134 p_exch_rate :=null;
135
136 elsif (p_exchange_date_type = 'CFD') then
137 p_exch_date:=nvl(G_anticipated_value_date,G_transaction_date);
138
139 elsif (p_exchange_date_type = 'BSD') then
140 begin
141 select statement_date
142 into p_exch_date
143 from ce_statement_headers sh,
144 ce_statement_lines sl
145 where sh.statement_header_id = sl.statement_header_id
146 and sl.statement_line_id = G_created_from_stmtline_id;
147 exception
148 when others then
149 null;
150 end;
151 elsif (p_exchange_date_type = 'BSG') then
152 begin
153 select gl_date
154 into p_exch_date
155 from ce_statement_headers sh,
156 ce_statement_lines sl
157 where sh.statement_header_id = sl.statement_header_id
158 and sl.statement_line_id = G_created_from_stmtline_id;
159 exception
160 when others then
161 null;
162 end;
163 elsif (p_exchange_date_type = 'SLD') then
164 begin
165 select trx_date
166 into p_exch_date
167 from ce_statement_lines sl
168 where sl.statement_line_id = G_created_from_stmtline_id;
169 exception
170 when others then
171 null;
172 end;
173
174 elsif (p_exchange_date_type = 'TRX') then
175 p_exch_date:=G_transaction_date;
176
177 elsif (p_exchange_date_type is null) then
178 p_exch_date := null;
179 p_exch_rate := null;
180 end if;
181 if (p_exch_date is not null) then
182 p_exch_rate:=gl_currency_api.get_rate(G_payment_curr_code,
183 ledger_currency_code,
184 p_exch_date,
185 p_exch_type);
186 end if;
187
188 --if its an international scenario
189 elsif ((G_payment_curr_code <> bank_currency_code) and (bank_currency_code = ledger_currency_code)) then
190 begin
191 select exchange_rate,
192 exchange_rate_type,
193 exchange_rate_date
194 into p_exch_rate,
195 p_exch_type,
196 p_exch_date
197 from ce_statement_lines
198 where statement_line_id = G_created_from_stmtline_id;
199
200 exception
201 when others then
202 null;
203 end;
204
205 else --domestic scenario
206 p_exch_date:= null;
207 p_exch_rate:= null;
208 p_exch_type:=null;
209 end if; --end of zba scenario
210
211 --if bat/cl scenario
212 elsif (G_created_from_stmtline_id is null) then
213
214 --check if foreign or international scenario
215
216 if (G_payment_curr_code <> ledger_currency_code) then
217
218 if (p_exchange_date_type in ('CFD','TRX')) then
219 if (p_exchange_date_type = 'CFD') then
220 p_exch_date:=nvl(G_anticipated_value_date,G_transaction_date);
221 else
222 p_exch_date:=G_transaction_date;
223 end if;
224 if (p_exch_date is not null) then
225 p_exch_rate:=gl_currency_api.get_rate(G_payment_curr_code,
226 ledger_currency_code,
227 p_exch_date,
228 p_exch_type);
229 end if;
230 else
231 p_exch_date:= null;
232 p_exch_rate:= null;
233 end if;
234
235 else --domestic scenario
236 p_exch_date:= null;
237 p_exch_rate:= null;
238 p_exch_type:=null;
239
240 end if; --foreign scenario ended
241
242 end if;--zba or bat/cl ended
243
244
245 log('exchange rate '||p_exch_rate);
246 log('exchange date '||p_exch_date);
247 log('<< get_exchange_rate');
248 p_exchange_rate:= p_exch_rate;
249 p_exchange_date:= p_exch_date;
250 EXCEPTION
251 when others then
252 FND_MESSAGE.SET_NAME('CE','CE_INCOMPLETE_USER_RATE');
253 FND_MSG_PUB.ADD;
254 log('exception in get_exchange_rate_date');
255 p_exchange_rate:=null;
256 p_exchange_date:=null;
257
258 END get_exchange_rate_date;
259
260
261 /*----------------------------------------------------------------------
262 | PUBLIC FUNCTION |
263 | get_exchange_date_type |
264 | |
265 | DESCRIPTION |
266 | Returns the cashflow exchange date type |
267 | |
268 -----------------------------------------------------------------------*/
269 FUNCTION get_exchange_date_type(p_le_id NUMBER) RETURN VARCHAR2 IS
270
271 l_exchange_date_type VARCHAR2(30);
272 BEGIN
273 log('>> get_exchange_date_type');
274 select bat_exchange_date_type
275 into l_exchange_date_type
276 from ce_system_parameters
277 where legal_entity_id = p_le_id;
278
279 log(' type '||l_exchange_date_type);
280 log('<< get_exchange_date_type');
281 return (l_exchange_date_type);
282 EXCEPTION
283 WHEN OTHERS THEN
284 log('exception in get_exchange_rate_type');
285 Return NULL;
286
287 END get_exchange_date_type;
288
289
290 /*----------------------------------------------------------------------
291 | PUBLIC FUNCTION |
292 | get_ledger_id |
293 | |
294 | DESCRIPTION |
295 | Returns the ledger id for a given legal entity id.
296 |
297 | A given LE can be linked to many types of ledgers:
298 |
299 | a. Primary Ledger [Only one possible for a given LE]
300 | b. Secondary Ledger [Many possible for a given LE]
301 | c. ALC Ledger [Many possible for a given LE]
302 | Therefore, it is possible to uniquely derive the id
303 | of the primary ledger from the given LE id.
304 |
305 |
306 | Get the primary ledger id for the given LE.
307 | The ledger id was formerly known as the
308 | set of books id.
309 | |
310 -----------------------------------------------------------------------*/
311
312
313 FUNCTION get_ledger_id (l_le_id NUMBER) RETURN NUMBER IS
314
315 l_ledger_list GL_MC_INFO.ledger_tbl_type:= GL_MC_INFO.ledger_tbl_type();
316 l_ledger_id NUMBER(15);
317 l_ret_val BOOLEAN;
318
319 BEGIN
320 log('>> get_ledger_id');
321 l_ledger_list := GL_MC_INFO.ledger_tbl_type();
322
323
324 l_ret_val := GL_MC_INFO.get_le_ledgers
325 ( p_legal_entity_id => l_le_id
326 , p_get_primary_flag => 'Y'
330 );
327 , p_get_secondary_flag => 'N'
328 , p_get_alc_flag => 'N'
329 , x_ledger_list => l_ledger_list
331
332 -- Check if return status is a success.
333 -- Otherwise raise error.
334 --
335
336 IF (l_ret_val <> TRUE) THEN
337
338 l_ledger_id := -1;
339
340 ELSIF (l_ledger_list.COUNT = 0) THEN
341
342 l_ledger_id := -1;
343
344 ELSE
345
346 l_ledger_id:=l_ledger_list(1).ledger_id;
347
348 END IF;
349 log('ledger_id '||l_ledger_id);
350 log('<< get_ledger_id');
351 return (l_ledger_id);
352 EXCEPTION
353 WHEN OTHERS THEN
354 log('exception in get_ledger_id');
355 RAISE;
356
357 END get_ledger_id;
358
359
360 /* ---------------------------------------------------------------------
361 | PUBLIC FUNCTION |
362 | get_accounting_status |
363 | |
364 | DESCRIPTION |
365 | returns the accounting status of a cashflow |
366 | |
367 -----------------------------------------------------------------------*/
368
369 FUNCTION get_accounting_status(p_cashflow_number NUMBER) RETURN VARCHAR2 is
370 p_temp number:=0;
371 p_status varchar2(30);
372 begin
373 log('>> get_accounting_status');
374 select count(1)
375 into p_temp
376 from ce_cashflow_acct_h
377 where cashflow_id = p_cashflow_number
378 and status_code = 'ACCOUNTING_ERROR';
379
380 if (p_temp <> 0 ) then
381 log('status accounting_error');
382 return ('ACCOUNTING_ERROR');
383 else
384 select count(1),status_code
385 into p_temp,p_status
386 from ce_cashflow_acct_h
387 where cashflow_id = p_cashflow_number
388 group by status_code;
389
390 log('status '||p_status);
391 log('<< get_accounting_status');
392 return p_status;
393 end if;
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 log('no data found exception so returning status not_applicable');
397 return('UNACCOUNTED');
398 WHEN TOO_MANY_ROWS THEN
399 log('exception in get_accounting_status returning partial_accounted');
400 return ('PARTIAL_ACCOUNTED');
401 END get_accounting_status;
402
403 /* ---------------------------------------------------------------------
404 | PUBLIC PROCEDURE |
405 | call_payment_process_request |
406 | |
407 | DESCRIPTION |
408 | Calls the Oracle Payments submit payment process request API |
409 | |
410 -----------------------------------------------------------------------*/
411
412 PROCEDURE call_payment_process_request ( p_payment_request_id NUMBER,
413 p_request_id OUT NOCOPY NUMBER
414 ) is
415
416 l_request_id NUMBER;
417
418 BEGIN
419 log('>> call_payment_process_request');
420 G_payment_request_id := p_payment_request_id;
421
422 --call the Payments API
423
424 l_request_id := FND_REQUEST.submit_request (
425 'IBY',
426 'IBYBUILD',
427 '',
428 '',
429 FALSE,
430 '260',
431 to_char(p_payment_request_id)
432 );
433
434 p_request_id:=l_request_id;
435 log('request id is '|| p_request_id);
436 log('<< call_payment_process_request');
437 EXCEPTION
438 WHEN others THEN
439 log('exception in call_payment_process_request');
440 p_request_id:=0;
441 END call_payment_process_request;
442
443
444 PROCEDURE get_intercompany_ccid (p_from_le_id NUMBER,
445 p_to_le_id NUMBER,
446 p_from_cash_gl_ccid NUMBER,
447 p_to_cash_gl_ccid NUMBER,
448 p_transfer_date DATE,
449 p_acct_type VARCHAR2,
450 p_status OUT NOCOPY VARCHAR2,
451 p_msg_count OUT NOCOPY NUMBER,
452 p_msg_data OUT NOCOPY VARCHAR2,
453 p_ccid OUT NOCOPY NUMBER,
454 p_reciprocal_ccid OUT NOCOPY NUMBER,
455 p_result OUT NOCOPY VARCHAR2)
456 IS
457 l_from_ledger_id NUMBER;
458 l_to_ledger_id NUMBER;
459 l_from_bsv VARCHAR2(1000);
460 l_to_bsv VARCHAR2(1000);
461 BEGIN
462
463 l_from_ledger_id := get_ledger_id(p_from_le_id);
464 l_to_ledger_id := get_ledger_id(p_to_le_id);
465 l_from_bsv := get_bsv(p_from_cash_gl_ccid,l_from_ledger_id);
466 l_to_bsv := get_bsv(p_to_cash_gl_ccid,l_to_ledger_id);
467
468 FUN_BAL_UTILS_GRP.get_inter_intra_account (
469 p_api_version =>1.0,
470 p_init_msg_list=>null,
471 p_ledger_id=>l_from_ledger_id,
472 p_to_ledger_id=>l_to_ledger_id,
473 p_from_bsv => l_from_bsv,
474 p_to_bsv => l_to_bsv,
475 p_source => 'Cash Management',
476 p_category => 'Bank Transfers',
477 p_gl_date => p_transfer_date,
478 p_acct_type => p_acct_type,
479 x_status => p_status,
480 x_msg_count => p_msg_count,
481 x_msg_data => p_msg_data,
482 x_ccid => p_ccid,
483 x_reciprocal_ccid => p_reciprocal_ccid);
484
488 END IF;
485 IF (p_status = 'E') THEN
486 p_result := 'NO_INTERCOMPANY_CCID';
487 return;
489
490 EXCEPTION
491 WHEN OTHERS THEN
492 p_result := 'FAIL';
493 END get_intercompany_ccid;
494
495
496 FUNCTION get_bsv (p_cash_ccid NUMBER,
497 p_ledger_id NUMBER)
498 RETURN VARCHAR2
499 IS
500 l_dist_segments FND_FLEX_EXT.SEGMENTARRAY ;
501 l_segments FND_FLEX_EXT.SEGMENTARRAY ;
502 l_num_of_segments NUMBER;
503 l_result BOOLEAN;
504 l_coa_id NUMBER;
505 l_flex_segment_num NUMBER;
506
507 BEGIN
508 SELECT chart_of_accounts_id
509 INTO l_coa_id
510 FROM gl_sets_of_books
511 WHERE set_of_books_id = p_ledger_id;
512
513 -- Get the segments of the two given accounts
514 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL', 'GL#',
515 l_coa_id,
516 p_cash_ccid,
517 l_num_of_segments,
518 l_dist_segments)
519 ) THEN
520 -- Return -1 if flex failed
521 RETURN (-1);
522 END IF;
523 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
524 101, 'GL#',
525 l_coa_id,
526 'GL_BALANCING',
527 l_flex_segment_num)
528 ) THEN
529 RETURN (-1);
530 END IF;
531 FOR i IN 1.. l_num_of_segments LOOP
532 IF (i = l_flex_segment_num) THEN
533 RETURN(l_dist_segments(i));
534 END IF;
535 END LOOP;
536
537 END Get_bsv;
538
539
540 /* ---------------------------------------------------------------------
541 | PUBLIC PROCEDURE |
542 | transfer_payment_transaction |
543 | |
544 | DESCRIPTION |
545 | Creates cashflows on the base of the values in payment |
546 | transactions |
547 | |
548 -----------------------------------------------------------------------*/
549
550 PROCEDURE transfer_payment_transaction ( p_trxn_reference_number NUMBER,
551 p_multi_currency VARCHAR2,
552 p_mode OUT NOCOPY VARCHAR2,
553 p_cashflow_id1 OUT NOCOPY NUMBER,
554 p_cashflow_id2 OUT NOCOPY NUMBER) is
555
556
557 type t_cashflow is table of ce_cashflows.cashflow_id%type;
558 type t_objectversion is table of ce_cashflows.object_version_number%type;
559 type t_rowid is table of varchar2(30);
560
561 l_cashflow t_cashflow;
562 l_objectversion t_objectversion;
563 l_rowid t_rowid;
564
565 p_pay_exch_rate_type VARCHAR2(30);
566 p_recp_exch_rate_type VARCHAR2(30);
567 p_source_dir VARCHAR2(30);
568 p_dest_dir VARCHAR2(30);
569 l_row_id VARCHAR2(30) := null;
570 p_cashflow_id NUMBER;
571 p_pay_exchange_rate NUMBER;
572 p_recp_exchange_rate NUMBER;
573 p_pay_exch_date DATE;
574 p_recp_exch_date DATE;
575 p_pay_base_amount NUMBER;
576 p_recp_base_amount NUMBER;
577 p_source_ledger_id NUMBER;
578 p_dest_ledger_id NUMBER;
579 p_source_offset NUMBER;
580 p_dest_offset NUMBER;
581 p_objectversion1 NUMBER;
582 p_objectversion2 NUMBER;
583 l_mode VARCHAR2(10);
584 p_count NUMBER;
585
586 BEGIN
587 log('>> transfer_payment_transaction');
588 OPEN f_row_cursor (p_trxn_reference_number);
589 FETCH
590 f_row_cursor
591 INTO
592 G_trxn_reference_number,
593 G_trxn_subtype_code_id,
594 G_transaction_date,
595 G_anticipated_value_date,
596 G_transaction_desc,
597 G_payment_curr_code,
598 G_payment_amount,
599 G_source_party_id,
600 G_source_le_id,
601 G_source_bank_acct_id,
602 G_dest_party_id,
603 G_dest_le_id,
604 G_dest_bank_acct_id,
605 G_created_from_dir,
606 G_created_from_stmtline_id,
607 G_bank_trxn_number,
608 G_payment_offset_ccid,
609 G_receipt_offset_ccid;
610
611 --calculate all the necessary parameters.
612
613
614 p_source_ledger_id := get_ledger_id(G_source_le_id);
615 p_dest_ledger_id := get_ledger_id(G_dest_le_id);
616
617 --if multicurrency then calculate exchange information
618 if (p_multi_currency = 'N') then
619 p_pay_exch_rate_type := get_exchange_rate_type(G_source_le_id);
620 p_recp_exch_rate_type := get_exchange_rate_type(G_dest_le_id);
621
622 get_exchange_rate_date( p_source_ledger_id,
623 G_source_bank_acct_id,
624 G_source_le_id,
625 p_pay_exch_rate_type,
626 p_pay_exch_date,
627 p_pay_exchange_rate);
628
629 get_exchange_rate_date( p_dest_ledger_id,
630 G_dest_bank_acct_id,
631 G_dest_le_id,
632 p_recp_exch_rate_type,
633 p_recp_exch_date,
634 p_recp_exchange_rate);
635
636 p_pay_base_amount := G_payment_amount*nvl(p_pay_exchange_rate,1);
637 p_recp_base_amount := G_payment_amount*nvl(p_recp_exchange_rate,1);
638
639 --if same currency then exchange information should be null
640 else
641 p_pay_exch_rate_type := null;
642 p_recp_exch_rate_type := null;
643 p_pay_exch_date := null;
644 p_pay_exchange_rate := null;
645 p_recp_exch_date := null;
646 p_recp_exchange_rate := null;
647 p_pay_base_amount := G_payment_amount;
648 p_recp_base_amount := G_payment_amount;
649 end if;
653 log('dest ledger id '|| p_dest_ledger_id);
650
651 log('values to be inserted in cashfows are');
652 log('source ledger id '|| p_source_ledger_id);
654 log('pay exchange rate type '|| p_pay_exch_rate_type);
655 log('recp exchange rate type '|| p_recp_exch_rate_type);
656 log('pay exchange rate '|| p_pay_exchange_rate);
657 log('recp exchange rate '|| p_recp_exchange_rate);
658 log('pay base amount '|| p_pay_base_amount);
659 log('recp base amount '|| p_recp_base_amount);
660
661
662 IF (G_created_from_dir = 'PAYMENT') THEN
663 p_source_dir := 'PAYMENT';
664 p_source_offset := G_payment_offset_ccid;
665 p_dest_dir := 'RECEIPT';
666 p_dest_offset := G_receipt_offset_ccid;
667 ELSE
668 p_source_dir :='RECEIPT';
669 p_source_offset := G_payment_offset_ccid;
670 p_dest_dir := 'PAYMENT';
671 p_dest_offset := G_receipt_offset_ccid;
672 END IF;
673
674 --check if its in insert or update mode
675 BEGIN
676 SELECT COUNT(*)
677 INTO p_count
678 FROM CE_CASHFLOWS
679 WHERE TRXN_REFERENCE_NUMBER = p_trxn_reference_number;
680
681 IF ( p_count <> 0 ) THEN
682 l_mode := 'UPDATE';
683 ELSE
684 l_mode := 'INSERT';
685 END IF;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 NULL;
690 END;
691
692 IF (l_mode = 'INSERT') THEN
693
694 --insert a source cashflow
695
696 CE_CASHFLOWS_PKG.insert_row (
697 X_ROWID => l_row_id,
698 X_CASHFLOW_ID => p_cashflow_id1,
699 X_CASHFLOW_LEDGER_ID => p_source_ledger_id,
700 X_CASHFLOW_LEGAL_ENTITY_ID => G_source_le_id,
701 X_CASHFLOW_BANK_ACCOUNT_ID => G_source_bank_acct_id,
702 X_CASHFLOW_DIRECTION => p_source_dir,
703 X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
704 X_CASHFLOW_DATE => G_transaction_date,
705 X_CASHFLOW_AMOUNT => G_payment_amount,
706 X_BASE_AMOUNT => p_pay_base_amount,
707 X_DESCRIPTION => G_transaction_desc,
708 X_CASHFLOW_EXCHANGE_RATE => p_pay_exchange_rate,
709 X_CASHFLOW_EXCHANGE_DATE => p_pay_exch_date,
710 X_CASHFLOW_EXCHANGE_RATE_TYPE => p_pay_exch_rate_type,
711 X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
712 X_BANK_TRXN_NUMBER => G_bank_trxn_number,
713 X_SOURCE_TRXN_TYPE => 'BAT',
714 X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
715 X_STATEMENT_LINE_ID => G_created_from_stmtline_id,
716 X_ACTUAL_VALUE_DATE => null,
717 X_COUNTERPARTY_PARTY_ID => G_dest_party_id,
718 X_COUNTERPARTY_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
719 X_OFFSET_CCID => p_source_offset,
720 X_CASHFLOW_STATUS_CODE => 'CREATED',
721 X_CLEARED_DATE => null,
722 X_CLEARED_AMOUNT => null,
723 X_CLEARED_EXCHANGE_RATE => null,
724 X_CLEARED_EXCHANGE_DATE => null,
725 X_CLEARED_EXCHANGE_RATE_TYPE => null,
726 X_CLEARING_CHARGES_AMOUNT => null,
727 X_CLEARING_ERROR_AMOUNT => null,
728 X_CLEARED_BY_FLAG => null,
729 X_REFERENCE_TEXT => null,
730 X_BANK_ACCOUNT_TEXT => null,
731 X_CUSTOMER_TEXT => null,
732 X_CREATED_BY => nvl(fnd_global.user_id, -1),
733 X_CREATION_DATE => sysdate,
734 X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
735 X_LAST_UPDATE_DATE => sysdate,
736 X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1)
737 ) ;
738
739 -- insert a destination cashflow
740
741
742 CE_CASHFLOWS_PKG.insert_row (
743 X_ROWID => l_row_id,
744 X_CASHFLOW_ID => p_cashflow_id2,
745 X_CASHFLOW_LEDGER_ID => p_dest_ledger_id,
746 X_CASHFLOW_LEGAL_ENTITY_ID => G_dest_le_id,
747 X_CASHFLOW_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
748 X_CASHFLOW_DIRECTION => p_dest_dir,
749 X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
750 X_CASHFLOW_DATE => G_transaction_date,
751 X_CASHFLOW_AMOUNT => G_payment_amount,
752 X_BASE_AMOUNT => p_recp_base_amount,
753 X_DESCRIPTION => G_transaction_desc,
754 X_CASHFLOW_EXCHANGE_RATE => p_recp_exchange_rate,
755 X_CASHFLOW_EXCHANGE_DATE => p_recp_exch_date,
756 X_CASHFLOW_EXCHANGE_RATE_TYPE => p_recp_exch_rate_type,
757 X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
758 X_BANK_TRXN_NUMBER => G_bank_trxn_number,
759 X_SOURCE_TRXN_TYPE => 'BAT',
760 X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
761 X_STATEMENT_LINE_ID => null,
762 X_ACTUAL_VALUE_DATE => null,
763 X_COUNTERPARTY_PARTY_ID => G_source_party_id,
764 X_COUNTERPARTY_BANK_ACCOUNT_ID => G_source_bank_acct_id,
765 X_OFFSET_CCID => p_dest_offset,
766 X_CASHFLOW_STATUS_CODE => 'CREATED',
767 X_CLEARED_DATE => null,
768 X_CLEARED_AMOUNT => null,
769 X_CLEARED_EXCHANGE_RATE => null,
770 X_CLEARED_EXCHANGE_DATE => null,
771 X_CLEARED_EXCHANGE_RATE_TYPE => null,
772 X_CLEARING_CHARGES_AMOUNT => null,
773 X_CLEARING_ERROR_AMOUNT => null,
774 X_CLEARED_BY_FLAG => null,
775 X_REFERENCE_TEXT => null,
776 X_BANK_ACCOUNT_TEXT => null,
777 X_CUSTOMER_TEXT => null,
778 X_CREATED_BY => nvl(fnd_global.user_id, -1),
779 X_CREATION_DATE => sysdate,
780 X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
784
781 X_LAST_UPDATE_DATE => sysdate,
782 X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1)
783 ) ;
785 ELSIF (l_mode = 'UPDATE') THEN
786 BEGIN
787 select rowid, cashflow_id, object_version_number
788 bulk collect into l_rowid, l_cashflow, l_objectversion
789 from ce_cashflows
790 where trxn_reference_number = p_trxn_reference_number
791 order by cashflow_id;
792 p_cashflow_id1 := l_cashflow(1);
793 p_cashflow_id2 := l_cashflow(2);
794
795 --update the source cashflow
796 CE_CASHFLOWS_PKG.update_row (
797 X_ROWID => l_rowid(1),
798 X_CASHFLOW_ID => p_cashflow_id1,
799 X_CASHFLOW_LEDGER_ID => p_source_ledger_id,
800 X_CASHFLOW_LEGAL_ENTITY_ID => G_source_le_id,
801 X_CASHFLOW_BANK_ACCOUNT_ID => G_source_bank_acct_id,
802 X_CASHFLOW_DIRECTION => p_source_dir,
803 X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
804 X_CASHFLOW_DATE => G_transaction_date,
805 X_CASHFLOW_AMOUNT => G_payment_amount,
806 X_BASE_AMOUNT => p_pay_base_amount,
807 X_DESCRIPTION => G_transaction_desc,
808 X_CASHFLOW_EXCHANGE_RATE => p_pay_exchange_rate,
809 X_CASHFLOW_EXCHANGE_DATE => p_pay_exch_date,
810 X_CASHFLOW_EXCHANGE_RATE_TYPE => p_pay_exch_rate_type,
811 X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
812 X_BANK_TRXN_NUMBER => G_bank_trxn_number,
813 X_SOURCE_TRXN_TYPE => 'BAT',
814 X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
815 X_STATEMENT_LINE_ID => G_created_from_stmtline_id,
816 X_ACTUAL_VALUE_DATE => null,
817 X_COUNTERPARTY_PARTY_ID => G_dest_party_id,
818 X_COUNTERPARTY_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
819 X_OFFSET_CCID => p_source_offset,
820 X_CASHFLOW_STATUS_CODE => 'CREATED',
821 X_CLEARED_DATE => null,
822 X_CLEARED_AMOUNT => null,
823 X_CLEARED_EXCHANGE_RATE => null,
824 X_CLEARED_EXCHANGE_DATE => null,
825 X_CLEARED_EXCHANGE_RATE_TYPE => null,
826 X_CLEARING_CHARGES_AMOUNT => null,
827 X_CLEARING_ERROR_AMOUNT => null,
828 X_CLEARED_BY_FLAG => null,
829 X_REFERENCE_TEXT => null,
830 X_BANK_ACCOUNT_TEXT => null,
831 X_CUSTOMER_TEXT => null,
832 X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
833 X_LAST_UPDATE_DATE => sysdate,
834 X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1),
835 X_OBJECT_VERSION_NUMBER => l_objectversion(1)
836 );
837
838 --update a destination cashflow
839 CE_CASHFLOWS_PKG.update_row(
840 X_ROWID => l_rowid(2),
841 X_CASHFLOW_ID => p_cashflow_id2,
842 X_CASHFLOW_LEDGER_ID => p_dest_ledger_id,
843 X_CASHFLOW_LEGAL_ENTITY_ID => G_dest_le_id,
844 X_CASHFLOW_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
845 X_CASHFLOW_DIRECTION => p_dest_dir,
846 X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
847 X_CASHFLOW_DATE => G_transaction_date,
848 X_CASHFLOW_AMOUNT => G_payment_amount,
849 X_BASE_AMOUNT => p_recp_base_amount,
850 X_DESCRIPTION => G_transaction_desc,
851 X_CASHFLOW_EXCHANGE_RATE => p_recp_exchange_rate,
852 X_CASHFLOW_EXCHANGE_DATE => p_recp_exch_date,
853 X_CASHFLOW_EXCHANGE_RATE_TYPE => p_recp_exch_rate_type,
854 X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
855 X_BANK_TRXN_NUMBER => G_bank_trxn_number,
856 X_SOURCE_TRXN_TYPE => 'BAT',
857 X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
858 X_STATEMENT_LINE_ID => null,
859 X_ACTUAL_VALUE_DATE => null,
860 X_COUNTERPARTY_PARTY_ID => G_source_party_id,
861 X_COUNTERPARTY_BANK_ACCOUNT_ID => G_source_bank_acct_id,
862 X_OFFSET_CCID => p_dest_offset,
863 X_CASHFLOW_STATUS_CODE => 'CREATED',
864 X_CLEARED_DATE => null,
865 X_CLEARED_AMOUNT => null,
866 X_CLEARED_EXCHANGE_RATE => null,
867 X_CLEARED_EXCHANGE_DATE => null,
868 X_CLEARED_EXCHANGE_RATE_TYPE => null,
869 X_CLEARING_CHARGES_AMOUNT => null,
870 X_CLEARING_ERROR_AMOUNT => null,
871 X_CLEARED_BY_FLAG => null,
872 X_REFERENCE_TEXT => null,
873 X_BANK_ACCOUNT_TEXT => null,
874 X_CUSTOMER_TEXT => null,
875 X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
876 X_LAST_UPDATE_DATE => sysdate,
877 X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1),
878 X_OBJECT_VERSION_NUMBER => l_objectversion(2)
879 );
880 EXCEPTION
881 WHEN OTHERS THEN
882 NULL;
883 END;
884 END IF;
885 p_mode:= l_mode;
886 CLOSE f_row_cursor;
887 log('first cashflow id'|| p_cashflow_id1);
888 log('second cashflow id'|| p_cashflow_id2);
889 log('<< transfer payment transaction');
890 EXCEPTION
891 WHEN OTHERS THEN
892 log('exception in transfer payment transaction');
893 CLOSE f_row_cursor;
894 RAISE;
895 END transfer_payment_transaction;
896
897
898 PROCEDURE get_bat_default_pmt_method
899 (p_payer_le_id NUMBER,
900 p_org_id NUMBER,
901 p_payee_party_id NUMBER,
902 p_payee_party_site_id NUMBER,
903 p_supplier_site_id NUMBER,
904 p_payment_currency VARCHAR2,
905 p_payment_amount NUMBER,
906 x_return_status OUT NOCOPY VARCHAR2 ,
907 x_msg_data OUT NOCOPY VARCHAR2 ,
908 x_msg_count OUT NOCOPY NUMBER ,
909 x_def_pm_code OUT NOCOPY VARCHAR2 ,
910 x_def_pm_name OUT NOCOPY VARCHAR2 )
911 IS
912 l_Trxn_Attribs IBY_DISBURSEMENT_COMP_PUB.Trxn_Attributes_Rec_Type;
913 x_default_pmt_attrs_rec IBY_DISBURSEMENT_COMP_PUB.Default_Pmt_Attrs_Rec_Type;
914 BEGIN
915 l_Trxn_Attribs.Application_Id:=260;
916 l_Trxn_Attribs.Payer_Legal_Entity_Id:=p_payer_le_id;
917 l_Trxn_Attribs.Payer_Org_Id:=p_org_id;
918 l_Trxn_Attribs.Payer_Org_Type:='LEGAL_ENTITY';
919 l_Trxn_Attribs.Payee_Party_Id:=p_payee_party_id;
920 l_Trxn_Attribs.Payee_Party_Site_Id:=p_payee_party_site_id;
921 l_Trxn_Attribs.Supplier_Site_Id:=p_supplier_site_id;
922 l_Trxn_Attribs.Pay_Proc_Trxn_Type_Code:='BAT';
923 l_Trxn_Attribs.Payment_Currency :=p_payment_currency;
924 l_Trxn_Attribs.Payment_Amount :=p_payment_amount;
925 l_Trxn_Attribs.Payment_Function:='CASH_PAYMENT';
926 iby_disbursement_comp_pub.Get_Default_Payment_Attributes(
927 1.0,
928 FND_API.G_TRUE,
929 'Y',
930 l_Trxn_Attribs,
931 x_return_status,
932 x_msg_count,
933 x_msg_data,
934 x_default_pmt_attrs_rec
935 );
936
937 x_def_pm_code := x_default_pmt_attrs_rec.payment_method.payment_method_code;
938 x_def_pm_name := x_default_pmt_attrs_rec.payment_method.payment_method_name;
939 END;
940
941
942 END CE_BAT_UTILS;
943