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