[Home] [Help]
PACKAGE BODY: APPS.CE_BAT_API
Source
1 PACKAGE BODY CE_BAT_API AS
2 /* $Header: cebtapib.pls 120.32.12010000.4 2008/11/13 12:48:02 talapati ship $ */
3 --
4
5
6 -- Statement lines
7 G_sl_trx_date DATE := NULL;
8 G_sl_description CE_STATEMENT_LINES.trx_text%TYPE := NULL;
9 G_sl_value_date DATE := NULL;
10 G_sl_currency_code CE_STATEMENT_LINES.currency_code%TYPE := NULL;
11 G_sl_amount CE_STATEMENT_LINES.amount%TYPE := NULL;
12 G_sl_original_amount CE_STATEMENT_LINES.original_amount%TYPE := NULL;
13 G_sl_statement_line_id CE_STATEMENT_LINES.statement_line_id%TYPE := NULL;
14 G_sl_bank_trx_number CE_STATEMENT_LINES.bank_trx_number%TYPE := NULL;
15 G_sl_trx_type CE_STATEMENT_LINES.trx_type%TYPE := NULL;
16
17 -- Bank accounts
18 G_source_bank_account_id CE_BANK_ACCOUNTS.bank_account_id%TYPE := NULL;
19 G_destination_bank_account_id CE_BANK_ACCOUNTS.bank_account_id%TYPE := NULL;
20 G_source_bank_account_name CE_BANK_ACCOUNTS.bank_account_name%TYPE := null;
21 G_dest_bank_account_name CE_BANK_ACCOUNTS.bank_account_name%TYPE := null;
22 G_source_ba_currency_code CE_BANK_ACCOUNTS.currency_code%TYPE := NULL;
23 G_destination_ba_currency_code CE_BANK_ACCOUNTS.currency_code%TYPE := NULL;
24 G_source_ba_asset_ccid CE_BANK_ACCOUNTS.asset_code_combination_id%TYPE := NULL;
25 G_destination_ba_asset_ccid CE_BANK_ACCOUNTS.asset_code_combination_id%TYPE := NULL;
26 G_ba_bank_charge_bearer CE_BANK_ACCOUNTS.pool_bank_charge_bearer_code%TYPE := NULL;
27 G_ba_payment_method_code CE_BANK_ACCOUNTS.pool_payment_method_code%TYPE := NULL;
28 G_ba_payment_reason_code CE_BANK_ACCOUNTS.pool_payment_reason_code%TYPE := NULL;
29 G_ba_payment_reason_comments CE_BANK_ACCOUNTS.pool_payment_reason_comments%TYPE
30 := NULL;
31 G_ba_remittance_message1 CE_BANK_ACCOUNTS.pool_remittance_message1%TYPE := NULL;
32 G_ba_remittance_message2 CE_BANK_ACCOUNTS.pool_remittance_message2%TYPE := NULL;
33 G_ba_remittance_message3 CE_BANK_ACCOUNTS.pool_remittance_message3%TYPE := NULL;
34
35
36 -- Legal entity
37 G_source_le_id CE_BANK_ACCOUNTS.account_owner_org_id%TYPE := NULL;
38 G_destination_le_id CE_BANK_ACCOUNTS.account_owner_org_id%TYPE := NULL;
39 G_source_le_party_id XLE_ENTITY_PROFILES.party_id%TYPE := NULL;
40 G_destination_le_party_id XLE_ENTITY_PROFILES.party_id%TYPE := NULL;
41 G_destination_party_site_id HZ_PARTY_SITES.party_site_id%TYPE := NULL;
42
43
44 -- Cash pool
45 G_cp_cashpool_id CE_CASHPOOLS.cashpool_id%TYPE := NULL;
46 G_cp_authorize_flag VARCHAR2(30) := NULL;
47 G_cp_trxn_subtype_code_id NUMBER := NULL;
48 G_cp_currency_code CE_CASHPOOLS.currency_code%TYPE:= NULL;
49
50 -- CL Proposed transfer
51 G_proposed_transfer_id NUMBER := NULL;
52 G_proposed_as_of_date DATE := NULL;
53 G_proposed_transfer_amount CE_PROPOSED_TRANSFERS.transfer_amount%TYPE := NULL;
54
55 -- System parameters
56 G_sp_authorize_flag CE_SYSTEM_PARAMETERS.authorization_bat%TYPE := NULL;
57
58 -- Transfer
59 G_sl_cashflow_direction VARCHAR2(30) := NULL;
60 G_bat_settle_flag VARCHAR2(10) := NULL;
61 G_bat_status VARCHAR2(30) := NULL;
62 G_bat_authorize_flag VARCHAR2(10) := NULL;
63 G_bat_payment_offset_ccid CE_PAYMENT_TRANSACTIONS.payment_offset_ccid%TYPE
64 := NULL;
65 G_bat_receipt_offset_ccid CE_PAYMENT_TRANSACTIONS.receipt_offset_ccid%TYPE
66 := NULL;
67 G_bat_amount NUMBER := NULL;
68 G_bat_date DATE := NULL;
69 G_bat_currency_code CE_STATEMENT_LINES.currency_code%TYPE := NULL;
70 G_bat_created_from_dir CE_STATEMENT_LINES.trx_type%TYPE := 'PAYMENT';
71 G_bat_statement_line_id CE_STATEMENT_LINES.statement_line_id%TYPE := NULL;
72 G_bat_anticipated_date DATE := NULL;
73 G_cashflows_created_flag VARCHAR2(1) := 'N';
74 G_multi_currency_flag VARCHAR2(1) := 'N';
75
76 FUNCTION spec_revision RETURN VARCHAR2 is
77 BEGIN
78 RETURN G_spec_revision;
79 END;
80
81 FUNCTION body_revision RETURN VARCHAR2 is
82 BEGIN
83 RETURN '$Revision: 120.32.12010000.4 $';
84 END;
85
86
87 PROCEDURE log(p_msg varchar2) is
88 BEGIN
89 -- FND_FILE.PUT_LINE(FND_FILE.LOG,p_msg);
90 -- dbms_output.put_line(p_msg);
91 cep_standard.debug(p_msg);
92
93 END log;
94
95 /*bug5219357*/
96 PROCEDURE check_cashpool(
97 p_called_by VARCHAR2,
98 p_cashpool_id NUMBER,
99 p_result OUT NOCOPY VARCHAR2)
100 IS
101 CURSOR c_cashpool (c_cashpool_id number)IS
102 SELECT authorization_bat
103 FROM ce_cashpools
104 WHERE cashpool_id = c_cashpool_id;
105
106 l_authorization_Bat CE_CASHPOOLS.authorization_bat%TYPE;
107 BEGIN
108
109 OPEN c_Cashpool(p_cashpool_id);
110 FETCH c_cashpool INTO l_authorization_bat;
111 CLOSE c_cashpool;
112
113 IF (l_authorization_bat IS NULL) THEN
114 --setup for XTR
115 FND_MESSAGE.set_name('CE','CE_INVALID_CASHPOOL_FOR_CE');
116 FND_MSG_PUB.add;
117 p_result := 'FAIL';
118 RETURN;
119 END IF;
120
121 IF (p_called_by = 'CL' AND g_ba_payment_method_code IS NULL) THEN
122 FND_MESSAGE.set_name('CE','CE_INVALID_CASHPOOL_FOR_CE');
123 FND_MSG_PUB.add;
124 p_result := 'FAIL';
125 RETURN;
126 END IF;
127 p_result := 'SUCCESS';
128 EXCEPTION
129 WHEN OTHERS THEN
130 RAISE;
131 END check_cashpool;
132
133 /*bug5219357*/
134 /* --------------------------------------------------------------------
135 | PRIVATE PROCEDURE |
136 | initiate_transfer
137 | DESCRIPTION |
138 | This procedure is used when the transfer is created
139 by the ZBA and CL programs.It gathers data from different
140 | tables necassary for creating the transfer
141 | |
142 | HISTORY |
143 | 14-JUL-2005 Shaik Vali Created |
144 --------------------------------------------------------------------- */
145
146 PROCEDURE initiate_transfer(
147 p_called_by VARCHAR2,
148 p_source_ba_id NUMBER,
149 p_destination_ba_id NUMBER,
150 p_cashpool_id NUMBER,
151 p_statement_line_id NUMBER,
152 p_transfer_amount NUMBER,
153 p_as_of_date DATE,
154 p_payment_details_from VARCHAR2,
155 p_result OUT NOCOPY varchar2)
156 IS
157
158 CURSOR ba_cursor (p_cs_bank_account_id NUMBER) IS
159 SELECT
160 ba.bank_account_id,
161 ba.currency_code,
162 ba.account_owner_org_id,
163 ba.asset_code_combination_id,
164 ba.pool_bank_charge_bearer_code,
165 ba.pool_payment_method_code,
166 ba.pool_payment_reason_code,
167 ba.pool_payment_reason_comments,
168 ba.pool_remittance_message1,
169 ba.pool_remittance_message2,
170 ba.pool_remittance_message3,
171 xfp.party_id,
172 hps.party_site_id,
173 decode(nvl(sp.authorization_bat,'NR'),
174 'NR','N','Y'),
175 sp.legal_entity_id,
176 ba.bank_account_name
177 FROM
178 ce_bank_accounts ba,
179 xle_entity_profiles xfp,
180 hz_party_sites hps,
181 ce_system_parameters sp
182 WHERE
183 ba.bank_account_id = p_cs_bank_account_id
184 AND xfp.legal_entity_id = ba.account_owner_org_id
185 AND sp.legal_entity_id(+)= xfp.legal_entity_id
186 AND hps.identifying_address_flag(+) = 'Y'
187 AND hps.party_id(+) = xfp.party_id;
188
189 l_dummy VARCHAR2(10);
190
191 l_src_bank_charge_bearer CE_BANK_ACCOUNTS.pool_bank_charge_bearer_code%TYPE;
192 l_src_payment_method_code CE_BANK_ACCOUNTS.pool_payment_method_code%TYPE;
193 l_src_payment_reason_code CE_BANK_ACCOUNTS.pool_payment_reason_code%TYPE;
194 l_src_payment_reason_comments CE_BANK_ACCOUNTS.pool_payment_reason_comments%TYPE;
195 l_src_remittance_message1 CE_BANK_ACCOUNTS.pool_remittance_message1%TYPE;
196 l_src_remittance_message2 CE_BANK_ACCOUNTS.pool_remittance_message2%TYPE;
197 l_src_remittance_message3 CE_BANK_ACCOUNTS.pool_remittance_message3%TYPE;
198
199 l_dest_bank_charge_bearer CE_BANK_ACCOUNTS.pool_bank_charge_bearer_code%TYPE;
200 l_dest_payment_method_code CE_BANK_ACCOUNTS.pool_payment_method_code%TYPE;
201 l_dest_payment_reason_code CE_BANK_ACCOUNTS.pool_payment_reason_code%TYPE;
202 l_dest_payment_reason_comments CE_BANK_ACCOUNTS.pool_payment_reason_comments%TYPE;
203 l_dest_remittance_message1 CE_BANK_ACCOUNTS.pool_remittance_message1%TYPE;
204 l_dest_remittance_message2 CE_BANK_ACCOUNTS.pool_remittance_message2%TYPE;
205 l_dest_remittance_message3 CE_BANK_ACCOUNTS.pool_remittance_message3%TYPE;
206
207 l_cnt NUMBER;
208 l_anticipated_float NUMBER;
209 l_dummy2 NUMBER;
210 l_sp_src_le_id NUMBER;
211 l_sp_dest_le_id NUMBER;
212 l_result varchar2(10);
213 BEGIN
214
215 log ('>> initiate_transfer...');
216 -- Fetch source bank account data
217 log ('Fetching source bank account data ...'|| p_source_ba_id);
218 OPEN ba_cursor(p_source_ba_id);
219 FETCH ba_cursor
220 INTO
221 G_source_bank_account_id,
222 G_source_ba_currency_code,
223 G_source_le_id,
224 G_source_ba_asset_ccid,
225 l_src_bank_charge_bearer,
226 l_src_payment_method_code,
227 l_src_payment_reason_code,
228 l_src_payment_reason_comments,
229 l_src_remittance_message1,
230 l_src_remittance_message2,
231 l_src_remittance_message3,
232 G_source_le_party_id,
233 l_dummy2,
234 G_sp_authorize_flag,
235 l_sp_src_le_id,
236 G_source_bank_account_name;
237 CLOSE ba_cursor;
238
239 -- Fetch destination bank account data
240 log ('Fetching destination bank account data ...'||p_destination_ba_id);
241 OPEN ba_cursor(p_destination_ba_id);
242 FETCH ba_cursor
243 INTO
244 G_destination_bank_account_id,
245 G_destination_ba_currency_code,
246 G_destination_le_id,
247 G_destination_ba_asset_ccid,
248 l_dest_bank_charge_bearer,
249 l_dest_payment_method_code,
250 l_dest_payment_reason_code,
251 l_dest_payment_reason_comments,
252 l_dest_remittance_message1,
253 l_dest_remittance_message2,
254 l_dest_remittance_message3,
255 G_destination_le_party_id,
256 G_destination_party_site_id,
257 l_dummy,
258 l_sp_dest_le_id,
259 G_dest_bank_account_name;
260 CLOSE ba_cursor;
261
262 -- Identify the bank account to default the payment
263 -- attributes from.
264 log('Fetching payment attributes ...' || p_payment_details_from);
265 IF p_payment_details_from = 'SRC' THEN
266 G_ba_bank_charge_bearer := l_src_bank_charge_bearer;
267 G_ba_payment_method_code := l_src_payment_method_code;
268 G_ba_payment_reason_code := l_src_payment_reason_code;
269 G_ba_payment_reason_comments := l_src_payment_reason_comments;
270 G_ba_remittance_message1 := l_src_remittance_message1;
271 G_ba_remittance_message2 := l_src_remittance_message2;
272 G_ba_remittance_message3 := l_src_remittance_message3;
273 ELSIF p_payment_details_from = 'DEST' THEN
274 G_ba_bank_charge_bearer := l_dest_bank_charge_bearer;
275 G_ba_payment_method_code := l_dest_payment_method_code;
276 G_ba_payment_reason_code := l_dest_payment_reason_code;
277 G_ba_payment_reason_comments := l_dest_payment_reason_comments;
278 G_ba_remittance_message1 := l_dest_remittance_message1;
279 G_ba_remittance_message2 := l_dest_remittance_message2;
280 G_ba_remittance_message3 := l_dest_remittance_message3;
281 END IF;
282 /*bug5219357*/
283 -- For ZBA and CL the cash pool should be setup
284 -- as CE cashpool
285 check_cashpool(p_called_by,p_cashpool_id,l_result);
286 IF (l_result in ('FAIL')) THEN
287 p_result := 'FAIL';
288 RETURN;
289 END IF;
290 /*bug5219357*/
291 -- Fetch cashpool data
292 log('Fetching cashpool data...');
293 SELECT
294 cp.cashpool_id,
295 cp.currency_code,
296 decode(nvl(cp.authorization_bat,'NR'),
297 'NR','N','Y'),
298 cp.trxn_subtype_code_id
299 INTO
300 G_cp_cashpool_id,
301 G_cp_currency_code,
302 G_cp_authorize_flag,
303 G_cp_trxn_subtype_code_id
304 FROM ce_cashpools cp
305 WHERE cp.cashpool_id = p_cashpool_id;
306
307 IF (p_called_by = 'ZBA') THEN
308 -- Fetch Statement line data
309 SELECT
310 sl.statement_line_id,
311 sl.trx_date,
312 sl.trx_type,
313 sl.amount,
314 sl.original_amount,
315 sl.effective_date,
316 sl.trx_text,
317 sl.bank_trx_number,
318 sl.currency_code
319 INTO
320 G_sl_statement_line_id,
321 G_sl_trx_date,
322 G_sl_trx_type,
323 G_sl_amount,
324 G_sl_original_amount,
325 G_sl_value_date,
326 G_sl_description,
327 G_sl_bank_trx_number,
328 G_sl_currency_code
329 FROM
330 ce_statement_lines sl
331 WHERE
332 sl.statement_line_id = p_statement_line_id;
333
334 G_bat_settle_flag := 'N';
335 G_bat_amount := NVL(G_sl_original_amount,G_sl_amount);
336 G_bat_date := G_sl_trx_date;
337 G_bat_anticipated_date := NVL(G_sl_value_date,G_sl_trx_date);
338 G_bat_currency_code := NVL(G_sl_currency_code,
339 G_source_ba_currency_code);
340 G_bat_statement_line_id := p_statement_line_id;
341
342 IF G_sl_trx_type = 'SWEEP_OUT' THEN
343 G_bat_created_from_dir := 'PAYMENT';
344 ELSIF G_sl_trx_type = 'SWEEP_IN' THEN
345 G_bat_created_from_dir := 'RECEIPT';
346 END IF;
347
348 ELSIF (p_called_by = 'CL') THEN
349
350 -- Get the payment method float days
351 log('Fetching anticipated_float from payment method...'||
352 G_ba_payment_method_code);
353 SELECT anticipated_float
354 INTO l_anticipated_float
355 FROM iby_payment_methods_vl
356 WHERE payment_method_code = G_ba_payment_method_code;
357
358 G_bat_settle_flag := 'Y';
359 G_bat_amount := p_transfer_amount;
360 G_bat_currency_code := G_cp_currency_code;
361 G_proposed_as_of_date := p_as_of_date;
362
363 IF (p_as_of_date < sysdate) THEN
364 G_bat_date := sysdate;
365 G_bat_anticipated_date := sysdate + l_anticipated_float;
366 ELSE
367 G_bat_date := p_as_of_date;
368 G_bat_anticipated_date := p_as_of_date + l_anticipated_float;
369 END IF;
370 END IF;
371 log ('<< initiate_transfer...');
372 EXCEPTION
373 WHEN OTHERS THEN
374 p_result := 'FAIL';
375 log('Exception in initiate_transfer');
376 RAISE;
377 END initiate_transfer;
378
379 /* --------------------------------------------------------------------
380 | PRIVATE PROCEDURE |
381 | check_duplicate
382 | DESCRIPTION |
383 | This procedure is used to check for duplicate transfers when
384 | the transfer is created by ZBA and CL programs. |
385 | |
386 | HISTORY |
387 | 14-JUL-2005 Shaik Vali Created |
388 --------------------------------------------------------------------- */
389
390 PROCEDURE check_duplicate(
391 p_called_by VARCHAR2,
392 p_source_ba_id NUMBER,
393 p_destination_ba_id NUMBER,
394 p_statement_line_id NUMBER,
395 p_transfer_amount NUMBER,
396 p_transfer_date DATE,
397 p_pay_trxn_number OUT NOCOPY NUMBER,
398 p_result OUT NOCOPY varchar2)
399 IS
400 l_cnt NUMBER;
401 BEGIN
402 log ('>> Check duplicate...');
403 log ('p_transfer_date='||p_transfer_date||
404 'p_source_ba_id='||p_source_ba_id||
405 'p_dest_ba_id='||p_destination_ba_id||
406 'p_transfer_amnount='||p_transfer_amount);
407
408 IF (p_called_by = 'ZBA') THEN
409 SELECT count(*)
410 INTO l_cnt
411 FROM ce_payment_transactions pt
412 WHERE pt.create_from_stmtline_id = p_statement_line_id
413 AND pt.source_bank_account_id = p_source_ba_id
414 AND pt.destination_bank_account_id = p_destination_ba_id
415 AND trunc(pt.transaction_date) = trunc(p_transfer_date)
419 ELSIF (p_called_by = 'CL') THEN
416 AND pt.payment_amount = p_transfer_amount
417 AND pt.trxn_status_code not in ('FAILED','CANCELLED');
418
420 SELECT count(*)
421 INTO l_cnt
422 FROM ce_payment_transactions pt
423 WHERE
424 pt.source_bank_account_id = p_source_ba_id
425 AND pt.destination_bank_account_id = p_destination_ba_id
426 AND trunc(pt.transaction_date) = trunc(p_transfer_date)
427 AND pt.payment_amount = p_transfer_amount
428 AND pt.trxn_status_code not in ('FAILED','CANCELLED');
429 END IF;
430
431 IF l_cnt > 0 THEN
432 p_result := 'FOUND';
433 ELSE
434 p_result := 'NOT_FOUND';
435 END IF;
436
437 log ('<< check_duplicate...l_cnt='||l_cnt);
438 EXCEPTION
439 WHEN OTHERS THEN
440 p_result := 'FAIL';
441 log('Exception in check_duplicate');
442 RAISE;
443 END check_duplicate;
444
445
446 /* --------------------------------------------------------------------
447 | PRIVATE PROCEDURE |
448 | check_user_security
449 | DESCRIPTION |
450 | This procedure is used to check whether the current user has
451 | access to the UMX CEBAT security function. Used when
452 | the transfer is created by ZBA and CL programs. |
453 | |
454 | HISTORY |
455 | 15-JUL-2005 Shaik Vali Created |
456 --------------------------------------------------------------------- */
457
458 PROCEDURE check_user_security(
459 p_source_le_id NUMBER,
460 p_destination_le_id NUMBER,
461 p_result OUT NOCOPY varchar2)
462 IS
463 l_s_cnt NUMBER;
464 l_d_cnt NUMBER;
465 BEGIN
466
467 log('>> check_user_security.....');
468
469 l_s_cnt := cep_standard.check_ba_security(p_source_le_id,'CEBAT');
470 l_d_cnt := cep_standard.check_ba_security(p_destination_le_id,'CEBAT');
471
472 IF (l_s_cnt = 1 AND l_d_cnt = 1) THEN
473 p_result := 'HAS_ACCESS';
474 ELSE
475 p_result := 'NO_ACCESS';
476 END IF;
477
478 log('<< check_user_security.....' || p_result);
479 EXCEPTION
480 WHEN OTHERS THEN
481 log('Exception in check_user_security');
482 p_result := 'FAIL';
483 RAISE;
484 END check_user_security;
485
486
487 /* --------------------------------------------------------------------
488 | PUBLIC PROCEDURE |
489 | create_transfer
490 | DESCRIPTION |
491 | This procedure is called directly from the ZBA and CL
492 | programs. the parameter p_called_by indicates the CL or ZBA |
493 | mode. When called by CL, the transfer is just created with status
494 | New and quit the program. When called by ZBA, the transfer is |
495 | created and also validated. |
496 | |
497 | HISTORY |
498 | 14-JUL-2005 Shaik Vali Created |
499 --------------------------------------------------------------------- */
500
501 PROCEDURE create_transfer(
502 p_called_by_1 VARCHAR2,
503 p_source_ba_id NUMBER,
504 p_destination_ba_id NUMBER,
505 p_statement_line_id NUMBER,
506 p_cashpool_id NUMBER,
507 p_transfer_amount NUMBER,
508 p_payment_details_from VARCHAR2,
509 p_as_of_date DATE,
510 p_cashflows_created_flag OUT NOCOPY VARCHAR2,
511 p_result OUT NOCOPY varchar2,
512 p_msg_count OUT NOCOPY NUMBER,
513 p_trxn_reference_number OUT NOCOPY NUMBER)
514 IS
515 l_result VARCHAR2(30);
516 l_ccid NUMBER;
517 l_reciprocal_ccid NUMBER;
518 p_called_by VARCHAR2(30);
519 l_cashflow_id1 NUMBER;
520 l_cashflow_id2 NUMBER;
521 l_mode VARCHAR2(100);
522 l_settle_flag VARCHAR2(10);
523 BEGIN
524
525 IF (p_called_by_1 = 'L') THEN
526 p_called_by := 'CL';
527 ELSIF (p_called_by_1 = 'Z') THEN
528 p_called_by := 'ZBA';
529 END IF;
530
531 log('>> CE_BAT_API.create_transfer ... mode = ' || p_called_by);
532 IF (p_called_by in ('ZBA','CL')) THEN
533
534 FND_MSG_PUB.initialize;
535 --Gather the data required for creating the transfer
536 initiate_transfer(p_called_by,
537 p_source_ba_id,
538 p_destination_ba_id,
539 p_cashpool_id,
540 p_statement_line_id,
541 p_transfer_amount,
542 p_as_of_date,
543 p_payment_details_from,
544 p_result);
545
546 IF (p_result in ('FAIL')) THEN
547 p_msg_count := FND_MSG_PUB.count_msg;
548 RETURN;
549 END IF;
550
551 -- Check whether the user has access to the
552 -- UMX security function to create transfers
553 check_user_security(G_source_le_id,
554 G_destination_le_id,
555 l_result);
556
557 IF (l_result in ('NO_ACCESS')) THEN
558 FND_MESSAGE.set_name('CE','CE_BAT_NO_ACCESS');
559 FND_MSG_PUB.add;
560 p_result := 'FAILURE';
561 p_msg_count := FND_MSG_PUB.count_msg;
562 RETURN;
563 END IF;
564
568 p_destination_ba_id,
565 -- check for duplicate transfers
566 check_duplicate(p_called_by,
567 p_source_ba_id,
569 p_statement_line_id,
570 G_bat_amount,
571 G_bat_date,
572 p_trxn_reference_number,
573 l_result);
574
575 IF (l_result in ('FOUND')) THEN
576 FND_MESSAGE.set_name('CE','CE_BAT_DUPLICATE_FOUND');
577 FND_MSG_PUB.add;
578 p_result := 'FAIL';
579 -- stop if duplicate transfer is found
580 p_msg_count := FND_MSG_PUB.count_msg;
581 RETURN;
582 END IF;
583
584
585 -- populate payment transactions table
586 populate_transfer(p_trxn_reference_number);
587
588 IF (p_called_by = 'ZBA' OR p_called_by = 'CL') THEN
589 IF (p_called_by ='ZBA') THEN
590 l_settle_flag := 'N';
591 ELSE
592 l_settle_flag := 'Y';
593 END IF;
594
595 validate_transfer( p_called_by,
596 p_trxn_reference_number,
597 G_source_le_id,
598 G_destination_le_id,
599 G_source_ba_currency_code,
600 G_destination_ba_currency_code,
601 G_bat_currency_code,
602 G_bat_date,
603 G_source_ba_asset_ccid,
604 G_destination_ba_asset_ccid,
605 p_destination_ba_id,
606 G_cp_authorize_flag,
607 l_settle_flag,
608 l_ccid,
609 l_reciprocal_ccid,
610 l_result);
611
612 IF (l_result = 'FAILURE') THEN
613 CE_PAYMENT_TRXN_PKG.update_transfer_status
614 (p_trxn_reference_number,'INVALID');
615
616 ELSIF (l_result = 'SUCCESS' AND p_called_by='ZBA') THEN
617 CE_PAYMENT_TRXN_PKG.update_transfer_status
618 (p_trxn_reference_number,'VALIDATED');
619 IF (NVL(NVL(G_cp_authorize_flag,G_sp_authorize_flag),'N')= 'N')
620 THEN
621 create_update_cashflows(p_trxn_reference_number,l_mode,
622 l_cashflow_id1,
623 l_cashflow_id2);
624
625 settle_transfer(p_called_by,p_trxn_reference_number,
626 null,
627 l_cashflow_id1,
628 l_cashflow_id2);
629
630 END IF;
631 END IF;
632 END IF;
633 END IF;
634 p_cashflows_created_flag := G_cashflows_created_flag;
635 p_result := 'SUCCESS';
636 p_msg_count := FND_MSG_PUB.count_msg;
637
638 log('<< BAT_API.create_transfer');
639 EXCEPTION
640 WHEN OTHERS THEN
641 log('EXCEPTION in create_transfer');
642 p_result := 'FAIL';
643 RAISE;
644 END create_transfer;
645
646
647 /* ---------------------------------------------------------------------
648 | PUBLIC PROCEDURE |
649 | validate_transfer |
650 | DESCRIPTION |
651 | This procedure performs the necassary validations required while |
652 | creating a transfer. This can be called while creating the |
653 | transfer either from ZBA, CL or MANUAL (from the UI) modes. |
654 | 1) When called with MANUAL mode and if Settlement is required |
655 | then Payments validations are also performed. |
656 | 2) After all the validations are successfull and authorization |
657 | is not required then authorize the transfer directly. |
658 | |
659 | HISTORY |
660 | 16-JUL-2005 Shaik Vali Created |
661 | 17-JUN-2008 Varun Netan Bug 6911203: Corrected call to |
662 | intercompany API. |
663 | 02-SEP-2008 Varun Netan Bug 7357191: Reworked the way |
664 | in which CCIDs are fetched |
665 | using intercompany API. |
666 --------------------------------------------------------------------- */
667
668 PROCEDURE validate_transfer(
669 p_called_by VARCHAR2,
670 p_trxn_reference_number NUMBER,
671 p_source_le_id NUMBER,
672 p_destination_le_id NUMBER,
673 p_source_ba_currency_code VARCHAR2,
674 p_destination_ba_currency_code VARCHAR2,
675 p_transfer_currency_code VARCHAR2,
676 p_transfer_date DATE,
677 p_source_ba_asset_ccid NUMBER,
678 p_destination_ba_asset_ccid NUMBER,
679 p_destination_bank_account_id NUMBER,
680 p_authorize_flag VARCHAR2,
681 p_settle_flag VARCHAR2,
682 p_ccid OUT NOCOPY NUMBER,
683 p_reciprocal_ccid OUT NOCOPY NUMBER,
684 p_result OUT NOCOPY varchar2)
685 IS
686 l_status VARCHAR2(20);
687 l_msg_count NUMBER;
688 l_msg_data VARCHAR2(1000);
689 l_result VARCHAR2(20);
690 l_mode VARCHAR2(10);
691 l_cashflow_id1 NUMBER;
692 l_cashflow_id2 NUMBER;
693 l_source_ledger_id NUMBER := NULL;
694 l_destination_ledger_id NUMBER := NULL;
695 l_source_ledger_curr GL_LEDGERS.currency_code%TYPE := NULL;
696 l_destination_ledger_curr GL_LEDGERS.currency_code%TYPE := NULL;
697 l_from_ledger_id NUMBER;
698 l_to_ledger_id NUMBER;
699 l_from_bsv VARCHAR2(1000);
703 BEGIN
700 l_to_bsv VARCHAR2(1000);
701 l_intercompany BOOLEAN := false;
702
704 log('>> validate_transfer.....');
705
706 IF p_called_by = 'MANUAL' THEN
707 FND_MSG_PUB.initialize;
708 END IF;
709
710 -- The bank accounts should have a not null
711 -- asset_combination_id. Transfer cannot be
712 -- created for bank accounts that are not attched to
713 -- any GL cash account
714
715 IF p_source_ba_asset_ccid is NULL OR
716 p_destination_ba_asset_ccid is NULL THEN
717 IF p_called_by = 'MANUAL' THEN
718 SELECT sba.bank_account_name,
719 d_ba.bank_account_name
720 INTO g_source_bank_account_name,
721 g_dest_bank_account_name
722 FROM ce_bank_accounts sba,
723 ce_bank_accounts d_ba,
724 ce_payment_transactions pt
725 WHERE pt.trxn_reference_number=p_trxn_reference_number
726 AND sba.bank_account_id=pt.source_bank_account_id
727 AND d_ba.bank_account_id=pt.destination_bank_account_id;
728 END IF;
729 IF p_source_ba_asset_ccid IS NULL
730 THEN
731 FND_MESSAGE.set_name('CE','CE_BAT_NO_CASH_ACCOUNT');
732 FND_MESSAGE.set_token('BA_NAME',G_source_bank_account_name);
733 FND_MSG_PUB.add;
734 END IF;
735 IF p_destination_ba_asset_ccid IS NULL
736 THEN
737 FND_MESSAGE.set_name('CE','CE_BAT_NO_CASH_ACCOUNT');
738 FND_MESSAGE.set_token('BA_NAME',G_dest_bank_account_name);
739 FND_MSG_PUB.add;
740 END IF;
741 p_result := 'FAILURE';
742 UPDATE ce_payment_transactions
743 SET trxn_status_code = 'INVALID'
744 WHERE trxn_reference_number = p_trxn_reference_number;
745 RETURN;
746 END IF;
747
748 -- The transfer currency should either the source
749 -- bank account currency or destination bank account
750 IF p_transfer_currency_code NOT IN (p_source_ba_currency_code,
751 p_destination_ba_currency_code)
752 THEN
753 FND_MESSAGE.set_name('CE','CE_BAT_INVALID_CURRENCY');
754 FND_MSG_PUB.add;
755 p_result := 'FAILURE';
756 UPDATE ce_payment_transactions
757 SET trxn_status_code = 'INVALID'
758 WHERE trxn_reference_number = p_trxn_reference_number;
759 RETURN;
760 END IF;
761
762 -- Both the bank accounts cannot be non-functional currency.
763
764 l_source_ledger_id := CE_BAT_UTILS.get_ledger_id(p_source_le_id);
765 l_destination_ledger_id:=CE_BAT_UTILS.get_ledger_id(p_destination_le_id);
766 SELECT currency_code
767 INTO l_source_ledger_curr
768 FROM gl_ledgers
769 WHERE ledger_id=l_source_ledger_id;
770
771 SELECT currency_code
772 INTO l_destination_ledger_curr
773 FROM gl_ledgers
774 WHERE ledger_id=l_destination_ledger_id;
775
776
777 -- for bug 6455698
778 IF p_source_ba_currency_code <> p_destination_ba_currency_code THEN
779 IF ((p_transfer_currency_code = p_source_ba_currency_code) and
780 (l_destination_ledger_curr <> p_destination_ba_currency_code)) THEN
781 FND_MESSAGE.set_name('CE','CE_BAT_INVALID_DEST_BANK');
782 FND_MSG_PUB.add;
783 p_result := 'FAILURE';
784 UPDATE ce_payment_transactions
785 SET trxn_status_code = 'INVALID'
786 WHERE trxn_reference_number = p_trxn_reference_number;
787 RETURN;
788 ELSIF ((p_transfer_currency_code = p_destination_ba_currency_code) and
789 (l_source_ledger_curr <> p_source_ba_currency_code)) THEN
790 FND_MESSAGE.set_name('CE','CE_BAT_INVALID_SRC_BANK');
791 FND_MSG_PUB.add;
792 p_result := 'FAILURE';
793 UPDATE ce_payment_transactions
794 SET trxn_status_code = 'INVALID'
795 WHERE trxn_reference_number = p_trxn_reference_number;
796 RETURN;
797 END IF;
798 END IF;
799
800 -- Check if the transfer is within the same ledger or
801 -- different ledgers
802 l_from_ledger_id := CE_BAT_UTILS.get_ledger_id(p_source_le_id);
803 l_to_ledger_id := CE_BAT_UTILS.get_ledger_id(p_destination_le_id);
804 l_from_bsv := CE_BAT_UTILS.get_bsv(p_source_ba_asset_ccid,
805 l_from_ledger_id);
806 l_to_bsv := CE_BAT_UTILS.get_bsv(p_destination_ba_asset_ccid,
807 l_to_ledger_id);
808 /* commented these lines for bug 5528877
809 IF (l_from_ledger_id <> l_to_ledger_id) THEN
810 l_intercompany := true;
811 ELSIF (l_from_bsv <> l_to_bsv) THEN
812 l_intercompany := true;
813 ELSE
814 l_intercompany := false;
815 END IF;
816 */
817 l_intercompany := true;
818 -- Call Intercompany API to fetch CCIDs
819 -- Bug 7357191: Intercompany API calls reworked. The call has to be made
820 -- twice, once to fetch Debit CC and once to fetch Credit CC.
821 -- For Debit CC the from_cash_gl_ccid/le should be the Payment Account.
822 -- For Credit CC the from_cash_gl_ccid/le should be the Receipt Account.
823 -- Also assigning values to p_ccid and p_reciprocal_ccid parameters such
824 -- that these OUT parameters contain the payment_offset_ccid and
825 -- reciept_offset_ccid respectively.
826 IF (l_intercompany)
827 THEN
828 log('Fetch intercompany');
829 log('bat_created_from_dir = '||G_bat_created_from_dir);
830 IF (G_bat_created_from_dir = 'PAYMENT')
831 THEN
832 -- Payment Account ==> p_source_ba_asset_ccid
836
833 -- Receipt Account ==> p_destination_ba_asset_ccid
834 -- Payment LE ==> p_source_le_id
835 -- Receipt LE ==> p_destination_le_id
837 -- fetch debit CC
838 CE_BAT_UTILS.get_intercompany_ccid (
839 p_from_le_id => p_source_le_id,
840 p_to_le_id => p_destination_le_id,
841 p_from_cash_gl_ccid => p_source_ba_asset_ccid,
842 p_to_cash_gl_ccid => p_destination_ba_asset_ccid,
843 p_transfer_date => p_transfer_date,
844 p_acct_type => 'D',
845 p_status => l_status,
846 p_msg_count => l_msg_count,
847 p_msg_data => l_msg_data,
848 p_ccid => p_ccid,
849 p_reciprocal_ccid => p_reciprocal_ccid,
850 p_result => l_result);
851 -- Set debit CCID as PAYMENT Offset
852 G_bat_payment_offset_ccid := p_ccid;
853
854 -- fetch credit CC
855 CE_BAT_UTILS.get_intercompany_ccid (
856 p_from_le_id => p_destination_le_id,
857 p_to_le_id => p_source_le_id,
858 p_from_cash_gl_ccid => p_destination_ba_asset_ccid,
859 p_to_cash_gl_ccid => p_source_ba_asset_ccid,
860 p_transfer_date => p_transfer_date,
861 p_acct_type => 'C',
862 p_status => l_status,
863 p_msg_count => l_msg_count,
864 p_msg_data => l_msg_data,
865 p_ccid => p_ccid,
866 p_reciprocal_ccid => p_reciprocal_ccid,
867 p_result => l_result);
868 -- Set credit CCID as RECEIPT offset
869 G_bat_receipt_offset_ccid := p_ccid;
870
871 ELSIF (G_bat_created_from_dir = 'RECEIPT')
872 THEN
873 -- Payment Account ==> p_destination_ba_asset_ccid
874 -- Receipt Account ==> p_source_ba_asset_ccid
875 -- Payment LE ==> p_destination_le_id
876 -- Receipt LE ==> p_source_le_id
877
878 -- fetch debit CC
879 CE_BAT_UTILS.get_intercompany_ccid (
880 p_from_le_id => p_destination_le_id,
881 p_to_le_id => p_source_le_id,
882 p_from_cash_gl_ccid => p_destination_ba_asset_ccid,
883 p_to_cash_gl_ccid => p_source_ba_asset_ccid,
884 p_transfer_date => p_transfer_date,
885 p_acct_type => 'D',
886 p_status => l_status,
887 p_msg_count => l_msg_count,
888 p_msg_data => l_msg_data,
889 p_ccid => p_ccid,
890 p_reciprocal_ccid => p_reciprocal_ccid,
891 p_result => l_result);
892 -- Set debit CCID as PAYMENT offset
893 G_bat_payment_offset_ccid := p_ccid;
894
895 -- fetch credit CC
896 CE_BAT_UTILS.get_intercompany_ccid (
897 p_from_le_id => p_source_le_id,
898 p_to_le_id => p_destination_le_id,
899 p_from_cash_gl_ccid => p_source_ba_asset_ccid,
900 p_to_cash_gl_ccid => p_destination_ba_asset_ccid,
901 p_transfer_date => p_transfer_date,
902 p_acct_type => 'C',
903 p_status => l_status,
904 p_msg_count => l_msg_count,
905 p_msg_data => l_msg_data,
906 p_ccid => p_ccid,
907 p_reciprocal_ccid => p_reciprocal_ccid,
908 p_result => l_result);
909 -- Set credit CCID as RECEIPT offset
910 G_bat_receipt_offset_ccid := p_ccid;
911
912 END IF;
913 -- Error resolving inter/intra-company rules
914 IF l_result = 'NO_INTERCOMPANY_CCID'
915 THEN
916 p_result := 'FAILURE';
917 UPDATE ce_payment_transactions
918 SET trxn_status_code = 'INVALID'
919 WHERE trxn_reference_number = p_trxn_reference_number;
920 RETURN;
921 END IF;
922 ELSE -- no intercompany
923 log('No Intercompany');
924 --7357191: Changed assignments to directly apply values
925 -- to Global variables.
926 IF (G_bat_created_from_dir = 'PAYMENT') THEN
927 G_bat_payment_offset_ccid := p_destination_ba_asset_ccid;
928 G_bat_receipt_offset_ccid := p_source_ba_asset_ccid;
929 ELSIF (G_bat_created_from_dir = 'RECEIPT') THEN
930 G_bat_payment_offset_ccid := p_source_ba_asset_ccid;
931 G_bat_receipt_offset_ccid := p_destination_ba_asset_ccid;
932 END IF;
933 END IF;
934
935 -- 7357191: added for compatibility to ensure that the OUT parameters
936 -- have proper values.
937 p_ccid := G_bat_payment_offset_ccid;
938 p_reciprocal_ccid := G_bat_receipt_offset_ccid;
939 log('G_bat_payment_offset_ccid = '||to_char(p_ccid));
940 log('G_bat_receipt_offset_ccid = '||to_char(p_reciprocal_ccid));
941
942 -- 7357191: changed values to Global variables
943 UPDATE ce_payment_transactions
944 SET payment_offset_ccid = G_bat_payment_offset_ccid,
945 receipt_offset_ccid = G_bat_receipt_offset_ccid
946 WHERE trxn_reference_number = p_trxn_reference_number;
947 p_result := 'SUCCESS';
948
949 IF (p_called_by = 'MANUAL' OR p_called_by = 'CL' OR p_called_by = 'ZBA')
950 THEN
951 IF (p_settle_flag = 'Y')
952 THEN
953 --call iby validation APIs
954 iby_validations(p_destination_bank_account_id,
958 IF (p_result = 'SUCCESS')
955 p_trxn_reference_number,
956 p_result);
957
959 THEN
960 -- When validations are successful, create/update cashflows
961 create_update_cashflows(p_trxn_reference_number,l_mode,
962 l_cashflow_id1,
963 l_cashflow_id2);
964
965 -- After validation is successfull and authorization
966 -- is not required then authorize the transfer directly
967 IF (p_authorize_flag = 'Y')
968 THEN
969 UPDATE ce_payment_transactions
970 SET trxn_status_code = 'VALIDATED'
971 WHERE trxn_reference_number = p_trxn_reference_number;
972 ELSE
973 authorize_transfer('AUTO',
974 p_trxn_reference_number,
975 NULL,
976 NULL,
977 p_result);
978 END IF;
979 ELSE --p_result != 'SUCCESS'
980 UPDATE ce_payment_transactions
981 SET trxn_Status_code = 'INVALID'
982 WHERE trxn_reference_number = p_trxn_reference_number;
983 END IF;
984
985 ELSE -- settle_flag != 'Y'
986 IF (p_authorize_flag = 'Y')
987 THEN
988 UPDATE ce_payment_transactions
989 SET trxn_status_code = 'VALIDATED'
990 WHERE trxn_reference_number = p_trxn_reference_number;
991
992 create_update_cashflows(p_trxn_reference_number,
993 l_mode,
994 l_cashflow_id1,
995 l_cashflow_id2);
996 ELSE
997 create_update_cashflows(p_trxn_reference_number,
998 l_mode,
999 l_cashflow_id1,
1000 l_cashflow_id2);
1001
1002 settle_transfer(p_called_by,
1003 p_trxn_reference_number,
1004 null,
1005 l_cashflow_id1,
1006 l_cashflow_id2);
1007 END IF;
1008 END IF;
1009 END IF;
1010 log('<< validate_transfer');
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 p_result := 'FAIL';
1014 log('Exception in validate_transfer');
1015 RAISE;
1016 END validate_transfer;
1017
1018
1019 /* --------------------------------------------------------------------
1020 | PUBLIC PROCEDURE |
1021 | authorize_transfer
1022 | DESCRIPTION |
1023 | This procedure is called either from |
1024 | 1)The UI when the transfer is validated. The validate_transfer |
1025 | api calls this procedure when validations are successfull and |
1026 | authorization is not required.This is the AUTO case. Since |
1027 | this is called only when Settlement is required, IBYBUILD |
1028 | program is submitted |
1029 | 2)The UI when the transfer is authorized. This is the MANUAL case|
1030 | IBYBUILD program is submitted since settlement is required
1031 | |
1032 | HISTORY |
1033 | 16-JUL-2005 Shaik Vali Created |
1034 --------------------------------------------------------------------- */
1035
1036 PROCEDURE authorize_transfer(
1037 p_called_by VARCHAR2,
1038 p_trxn_reference_number NUMBER,
1039 p_settle_flag VARCHAR2,
1040 p_pay_proc_req_code NUMBER,
1041 p_result OUT NOCOPY varchar2)
1042 IS
1043 l_cnt NUMBER;
1044 l_request_id NUMBER;
1045 l_pay_proc_req_code NUMBER;
1046 BEGIN
1047 log('>> authorize_transfer');
1048
1049 IF p_called_by = 'MANUAL' THEN
1050 -- called by UI
1051 SELECT count(*)
1052 INTO l_cnt
1053 FROM ce_payment_transactions
1054 WHERE payment_request_number = p_pay_proc_req_code;
1055
1056 IF l_cnt > 0 THEN
1057 CE_BAT_UTILS.call_payment_process_request(
1058 p_pay_proc_req_code,l_request_id);
1059
1060 IF (l_request_id = 0) THEN
1061 FND_MESSAGE.set_name('CE','CE_BAT_IBY_BUILD_FAILED');
1062 FND_MSG_PUB.add;
1063 p_result := 'FAILURE';
1064 ELSE
1065 UPDATE ce_payment_transactions
1066 SET trxn_status_code = 'IN_PROCESS'
1067 WHERE payment_request_number = p_pay_proc_req_code;
1068 p_result := 'SUCCESS';
1069 END IF;
1070 END IF;
1071
1072 ELSIF p_called_by = 'AUTO' THEN
1073 -- called by validate_transfer
1074
1075 -- generate the unique payment process request code
1076 SELECT ce_payment_transactions_s.nextval
1077 INTO l_pay_proc_req_code
1078 FROM dual;
1079
1080 -- stamp the transfer with the payment process reqeuest code
1081 UPDATE ce_payment_transactions
1082 SET payment_request_number = l_pay_proc_req_code
1083 WHERE trxn_reference_number = p_trxn_reference_number;
1084
1085 -- call IBY Build program
1086 log('submitting the Payments build program');
1087 CE_BAT_UTILS.call_payment_process_request(
1088 l_pay_proc_req_code,l_request_id);
1089
1090 IF l_request_id = 0 THEN
1094 ELSE
1091 FND_MESSAGE.set_name('CE','CE_BAT_IBY_BUILD_FAILED');
1092 FND_MSG_PUB.add;
1093 p_result := 'FAILURE';
1095 log('submitted the Payments Build program:' || l_request_id);
1096 CE_PAYMENT_TRXN_PKG.update_transfer_status(
1097 p_trxn_reference_number,'IN_PROCESS');
1098 p_result := 'SUCCESS';
1099 END IF;
1100 END IF;
1101 log('<< authorize_transfer result=' || p_result);
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 log('Exception in authorize_transfer');
1105 p_result := 'FAILURE';
1106 RAISE;
1107 END authorize_transfer;
1108
1109
1110 /* --------------------------------------------------------------------
1111 | PUBLIC PROCEDURE |
1112 | reject_transfer
1113 | DESCRIPTION |
1114 | This procedure is called either from the UI when the transfer |
1115 | is rejected or by the payments call back api. |
1116 | |
1117 | HISTORY |
1118 | 22-JUL-2005 Shaik Vali Created |
1119 --------------------------------------------------------------------- */
1120
1121 PROCEDURE reject_transfer(
1122 p_pay_trxn_number NUMBER,
1123 p_result OUT NOCOPY varchar2)
1124 IS
1125 CURSOR c_cashflows(p_pay_trxn_number NUMBER) IS
1126 SELECT cashflow_id
1127 FROM ce_cashflows
1128 WHERE trxn_reference_number=p_pay_trxn_number;
1129
1130 l_cashflow_id1 NUMBER;
1131 l_cashflow_id2 NUMBER;
1132 l_result VARCHAR2(20);
1133 BEGIN
1134 CE_PAYMENT_TRXN_PKG.update_transfer_status(
1135 p_pay_trxn_number, 'REJECTED');
1136
1137
1138 UPDATE ce_cashflows
1139 SET cashflow_status_code='CANCELED'
1140 WHERE trxn_reference_number=p_pay_trxn_number;
1141
1142 p_result := 'SUCCESS';
1143
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 p_result := 'FAIL';
1147 RAISE;
1148 END reject_transfer;
1149
1150
1151 /* --------------------------------------------------------------------
1152 | PUBLIC PROCEDURE |
1153 | cancel_transfer
1154 | DESCRIPTION |
1155 | This procedure is called either from the UI when the transfer |
1156 | is canceled. This procedure cancels the cashflows created |
1157 | by the transfer and raise the XLA events
1158 | |
1159 | HISTORY |
1160 | 22-JUL-2005 Shaik Vali Created |
1161 --------------------------------------------------------------------- */
1162
1163 PROCEDURE cancel_transfer(
1164 p_pay_trxn_number NUMBER,
1165 p_result OUT NOCOPY VARCHAR2)
1166 IS
1167
1168 l_cnt NUMBER;
1169 l_event_id NUMBER;
1170 l_cashflow_id NUMBER;
1171
1172 CURSOR cashflows_c (p_trxn_ref_number NUMBER) IS
1173 SELECT cf.cashflow_id
1174 FROM ce_cashflows cf,
1175 ce_payment_transactions pt
1176 WHERE cf.trxn_reference_number = pt.trxn_reference_number
1177 AND pt.trxn_status_code = 'SETTLED'
1178 AND cf.trxn_reference_number = p_trxn_ref_number;
1179
1180 BEGIN
1181 log('>>cancel transfer');
1182 OPEN cashflows_c (p_pay_trxn_number);
1183 LOOP
1184 FETCH cashflows_c
1185 INTO l_cashflow_id;
1186 EXIT WHEN cashflows_c%NOTFOUND OR cashflows_c%NOTFOUND is null;
1187 log('calling XLA cancel event...'|| l_cashflow_id);
1188 -- call the XLA API to cancel the BAT
1189 CE_XLA_ACCT_EVENTS_PKG.create_event
1190 (l_cashflow_id,'CE_BAT_CANCELED');
1191
1192 END LOOP;
1193 CLOSE cashflows_c;
1194
1195 -- Update the cashflows status to CANCELED
1196 UPDATE ce_cashflows
1197 SET cashflow_status_code = 'CANCELED'
1198 WHERE trxn_reference_number = p_pay_trxn_number;
1199
1200 -- Update the payment transaction status to CANCELED
1201 CE_PAYMENT_TRXN_PKG.update_transfer_status
1202 (p_pay_trxn_number,'CANCELED');
1203
1204 p_result := 'SUCCESS';
1205 log('<<cancel transfer');
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 p_result := 'FAIL';
1209 log('Exception in cancel_transfer');
1210 RAISE;
1211 END cancel_transfer;
1212
1213
1214 /* --------------------------------------------------------------------
1215 | PUBLIC PROCEDURE |
1216 | populate_transfer
1217 | DESCRIPTION |
1218 | This procedure is dumps the data gathered by initiate_transfer |
1219 | into the ce_payment_transactions table
1220 | |
1221 | HISTORY |
1222 | 17-JUL-2005 Shaik Vali Created |
1223 --------------------------------------------------------------------- */
1224
1225 PROCEDURE populate_transfer (p_pay_trxn_number OUT NOCOPY NUMBER)
1226 IS
1227 l_settle_flag VARCHAR2(2);
1228 l_created_from_dir CE_STATEMENT_LINES.trx_type%TYPE;
1229 l_row_id ROWID;
1230 BEGIN
1231
1232 log('>> populate_transfer...');
1233 CE_PAYMENT_TRXN_PKG.Insert_Row(
1234 X_ROWID => l_row_id,
1238 X_TRXN_SUBTYPE_CODE_ID => G_cp_trxn_subtype_code_id,
1235 X_TRXN_REFERENCE_NUMBER => p_pay_trxn_number,
1236 X_SETTLE_BY_SYSTEM_FLAG => G_bat_settle_flag,
1237 X_TRANSACTION_TYPE => 'BAT',
1239 X_TRANSACTION_DATE => G_bat_date,
1240 X_ANTICIPATED_VALUE_DATE => G_bat_anticipated_date,
1241 X_TRANSACTION_DESCRIPTION => G_sl_description,
1242 X_PAYMENT_CURRENCY_CODE => G_bat_currency_code,
1243 X_PAYMENT_AMOUNT => G_bat_amount,
1244 X_SOURCE_PARTY_ID => G_source_le_party_id,
1245 X_SOURCE_LEGAL_ENTITY_ID => G_source_le_id,
1246 X_SOURCE_BANK_ACCOUNT_ID => G_source_bank_account_id,
1247 X_DEST_PARTY_ID => G_destination_le_party_id,
1248 X_DEST_LEGAL_ENTITY_ID => G_destination_le_id,
1249 X_DEST_BANK_ACCOUNT_ID => G_destination_bank_account_id,
1250 X_DEST_PARTY_SITE_ID => G_destination_party_site_id,
1251 X_REPETITIVE_PAYMENT_CODE => NULL,
1252 X_TRXN_STATUS_CODE => 'NEW',
1253 X_PAYMENT_METHOD_CODE => G_ba_payment_method_code,
1254 X_AUTHORIZE_FLAG => NVL(G_cp_authorize_flag,G_sp_authorize_flag),
1255 X_BANK_CHARGE_BEARER => G_ba_bank_charge_bearer,
1256 X_PAYMENT_REASON_CODE => G_ba_payment_reason_code,
1257 X_PAYMENT_REASON_COMMENTS => G_ba_payment_reason_comments,
1258 X_REMITTANCE_MESSAGE1 => G_ba_remittance_message1,
1259 X_REMITTANCE_MESSAGE2 => G_ba_remittance_message2,
1260 X_REMITTANCE_MESSAGE3 => G_ba_remittance_message3,
1261 X_CREATED_FROM_DIR => G_bat_created_from_dir,
1262 X_CREATE_FROM_STMTLINE_ID => G_bat_statement_line_id,
1263 X_BANK_TRXN_NUMBER => G_sl_bank_trx_number,
1264 X_PAYMENT_REQUEST_NUMBER => NULL,
1265 X_PAPER_DOCUMENT_NUMBER => NULL,
1266 X_DOC_SEQUENCE_ID => NULL,
1267 X_DOC_SEQUENCE_VALUE => NULL,
1268 X_DOC_CATEGORY_CODE => NULL,
1269 X_PAYMENT_OFFSET_CCID => G_bat_payment_offset_ccid,
1270 X_RECEIPT_OFFSET_CCID => G_bat_receipt_offset_ccid,
1271 X_CASHPOOL_ID => G_cp_cashpool_id,
1272 X_CREATED_BY => FND_GLOBAL.user_id,
1273 X_CREATION_DATE => sysdate,
1274 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
1275 X_LAST_UPDATE_DATE => sysdate,
1276 X_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
1277 X_EXT_BANK_ACCOUNT_ID => NULL,
1278 X_ATTRIBUTE_CATEGORY => NULL,
1279 X_ATTRIBUTE1 => NULL,
1280 X_ATTRIBUTE2 => NULL,
1281 X_ATTRIBUTE3 => NULL,
1282 X_ATTRIBUTE4 => NULL,
1283 X_ATTRIBUTE5 => NULL,
1284 X_ATTRIBUTE6 => NULL,
1285 X_ATTRIBUTE7 => NULL,
1286 X_ATTRIBUTE8 => NULL,
1287 X_ATTRIBUTE9 => NULL,
1288 X_ATTRIBUTE10 => NULL,
1289 X_ATTRIBUTE11 => NULL,
1290 X_ATTRIBUTE12 => NULL,
1291 X_ATTRIBUTE13 => NULL,
1292 X_ATTRIBUTE14 => NULL,
1293 X_ATTRIBUTE15 => NULL);
1294 log('<< populate_transfer...');
1295 END populate_transfer;
1296
1297
1298 /* --------------------------------------------------------------------
1299 | PUBLIC PROCEDURE |
1300 | settle_transfer
1301 | DESCRIPTION |
1302 | This procedure creates the cashflows for a transfer and raises |
1303 | XLA events. This is called when |
1304 | 1) the transfer is authorized and settlement is not required |
1305 | 2) By the IBYBUILD program's callback apis, after the payment is|
1306 | created.
1307 | |
1308 | HISTORY |
1309 | 24-JUL-2005 Shaik Vali Created |
1310 --------------------------------------------------------------------- */
1311
1312 PROCEDURE settle_transfer(
1313 p_called_by VARCHAR2,
1314 p_pay_trxn_number NUMBER,
1315 /*Bug7559093 - Changed NUMBER to VARCHAR2 */
1316 p_payment_reference_number VARCHAR2,
1317 p_cashflow_id1 NUMBER,
1318 p_cashflow_id2 NUMBER)
1319 IS
1320 l_event_id1 NUMBER;
1321 l_event_id2 NUMBER;
1322
1323 l_cashflow_id1 NUMBER;
1324 l_cashflow_id2 NUMBER;
1325
1326 CURSOR c_cashflows(p_trxn_ref_number NUMBER) IS
1327 SELECT cashflow_id
1328 FROM ce_cashflows
1329 WHERE trxn_Reference_number=p_trxn_ref_number;
1330 BEGIN
1331 log('>>settle_transfer...' || p_pay_trxn_number);
1332
1333 l_cashflow_id1 := p_cashflow_id1;
1334 l_cashflow_id2 := p_cashflow_id2;
1335
1336 IF (p_called_by = 'CALL_BACK' OR p_called_by='MANUAL') THEN
1337
1338 OPEN c_cashflows(p_pay_trxn_number);
1339 FETCH c_cashflows INTO l_cashflow_id1;
1340 FETCH c_cashflows INTO l_cashflow_id2;
1341 CLOSE c_cashflows;
1342 /* Bug7559093, As p_payment_reference_number changed to VARCHAR2
1343 the comparision <>0 changed to <>'0' */
1344 IF (p_payment_reference_number <>'0') THEN
1345 UPDATE ce_payment_transactions
1346 SET bank_trxn_number = p_payment_reference_number
1347 WHERE trxn_reference_number = p_pay_trxn_number;
1348
1349 UPDATE ce_cashflows
1350 SET bank_trxn_number = p_payment_reference_number
1351 WHERE trxn_reference_number = p_pay_trxn_number;
1352 END IF;
1353 END IF;
1354
1355
1356 log('updating status to SETTLED...');
1357 --update transfer status to SETTLED
1358 CE_PAYMENT_TRXN_PKG.update_transfer_status(
1359 p_pay_trxn_number,
1360 'SETTLED');
1361
1362
1366 --raise CREATE AE in XLA for the 2 cashflows
1363 log('raising XLA create events for cashflows...: ' ||
1364 l_cashflow_id1 || ' , ' || l_cashflow_id2);
1365
1367 CE_XLA_ACCT_EVENTS_PKG.create_event(
1368 l_cashflow_id1,
1369 'CE_BAT_CREATED');
1370
1371 CE_XLA_ACCT_EVENTS_PKG.create_event(
1372 l_cashflow_id2,
1373 'CE_BAT_CREATED');
1374
1375 G_cashflows_created_flag := 'Y';
1376
1377 log('<<settle_transfer');
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 log('Exception in settle_transfer');
1381 RAISE;
1382 END settle_transfer;
1383
1384
1385 /* --------------------------------------------------------------------
1386 | PUBLIC PROCEDURE |
1387 | call_iby_validate
1388 | DESCRIPTION |
1389 | This procedure calls Payments online validations api and is called|
1390 | from the UI on clicking the validate icon. However, before |
1391 | the api is called, we need to populate the GT iby_docs_payment_gt|
1392 | After the validations are done, and if there are any validations|
1393 | failures, the errors are store in table iby_transaction_errors_gt|
1394 | This errors table is queried and shown from the UI.
1395 | |
1396 | HISTORY |
1397 | 24-JUL-2005 Shaik Vali Created |
1398 --------------------------------------------------------------------- */
1399
1400 PROCEDURE call_iby_validate(p_trxn_reference_number NUMBER,
1401 p_doc_payable_id OUT NOCOPY NUMBER,
1402 p_return_status OUT NOCOPY VARCHAR2)
1403 IS
1404
1405 l_docs_payable_rec IBY_DOCS_PAYABLE_GT%ROWTYPE;
1406 l_return_status VARCHAR2(20);
1407 l_msg_count NUMBER;
1408 l_msg_data VARCHAR2(1000);
1409 l_transaction_id NUMBER;
1410 l_error_type IBY_TRANSACTION_ERRORS_GT.error_type%TYPE;
1411 l_error_code IBY_TRANSACTION_ERRORS_GT.error_code%TYPE;
1412 l_error_message IBY_TRANSACTION_ERRORS_GT.error_message%TYPE;
1413 l_error_date IBY_TRANSACTION_ERRORS_GT.error_date%TYPE;
1414 l_error_status IBY_TRANSACTION_ERRORS_GT.error_status%TYPE;
1415 l_validation_Set_code IBY_TRANSACTION_ERRORS_GT.validation_set_code%TYPE;
1416 l_cnt NUMBER;
1417
1418 BEGIN
1419 log('>> call_iby_validate');
1420 SELECT
1421 ANTICIPATED_VALUE_DATE,
1422 'Y',
1423 BANK_CHARGE_BEARER,
1424 TRXN_REFERENCE_NUMBER,
1425 TRXN_REFERENCE_NUMBER,
1426 260,
1427 PAYMENT_AMOUNT,
1428 PAYMENT_CURRENCY_CODE,
1429 TRANSACTION_DATE,
1430 TRANSACTION_DESCRIPTION,
1431 'BAT',
1432 'Y',
1433 EXT_BANK_ACCOUNT_ID,
1434 DESTINATION_BANK_ACCOUNT_ID,
1435 DESTINATION_LEGAL_ENTITY_ID,
1436 DESTINATION_LEGAL_ENTITY_ID,
1437 'LEGAL_ENTITY',
1438 'BAT',
1439 DESTINATION_PARTY_ID,
1440 DESTINATION_PARTY_SITE_ID,
1441 PAYMENT_AMOUNT,
1442 PAYMENT_CURRENCY_CODE,
1443 TRANSACTION_DATE,
1444 'CASH_PAYMENT',
1445 PAYMENT_METHOD_CODE,
1446 PAYMENT_REASON_CODE,
1447 PAYMENT_REASON_COMMENTS,
1448 REMITTANCE_MESSAGE1,
1449 REMITTANCE_MESSAGE2,
1450 REMITTANCE_MESSAGE3,
1451 IBY_DOCS_PAYABLE_GT_S.nextval
1452 INTO
1453 l_docs_payable_rec.anticipated_value_date,
1454 l_docs_payable_rec.allow_removing_document_flag,
1455 l_docs_payable_rec.bank_charge_bearer,
1456 l_docs_payable_rec.calling_app_doc_unique_ref1,
1457 l_docs_payable_rec.calling_app_doc_ref_number,
1458 l_docs_payable_rec.calling_app_id,
1459 l_docs_payable_rec.document_amount,
1460 l_docs_payable_rec.document_currency_code,
1461 l_docs_payable_rec.document_date,
1462 l_docs_payable_rec.document_description,
1463 l_docs_payable_rec.document_type,
1464 l_docs_payable_rec.exclusive_payment_flag,
1465 l_docs_payable_rec.external_bank_account_id,
1466 l_docs_payable_rec.internal_bank_account_id,
1467 l_docs_payable_rec.legal_entity_id,
1468 l_docs_payable_rec.org_id,
1469 l_docs_payable_rec.org_type,
1470 l_docs_payable_rec.pay_proc_trxn_type_code,
1471 l_docs_payable_rec.payee_party_id,
1472 l_docs_payable_rec.payee_party_site_id,
1473 l_docs_payable_rec.payment_amount,
1474 l_docs_payable_rec.payment_currency_code,
1475 l_docs_payable_rec.payment_date,
1476 l_docs_payable_rec.payment_function,
1477 l_docs_payable_rec.payment_method_code,
1478 l_docs_payable_rec.payment_reason_code,
1479 l_docs_payable_rec.payment_reason_comments,
1480 l_docs_payable_rec.remittance_message1,
1481 l_docs_payable_rec.remittance_message2,
1482 l_docs_payable_rec.remittance_message3,
1483 l_docs_payable_rec.document_payable_id
1484 FROM
1485 ce_payment_transactions
1486 WHERE
1487 trxn_reference_number = p_trxn_reference_number;
1488
1489 log('inserting data into iby_docs_payable_gt');
1490
1491 INSERT INTO IBY_DOCS_PAYABLE_GT(
1492 ANTICIPATED_VALUE_DATE,
1493 ALLOW_REMOVING_DOCUMENT_FLAG,
1494 BANK_CHARGE_BEARER,
1495 CALLING_APP_DOC_UNIQUE_REF1,
1496 CALLING_APP_DOC_REF_NUMBER,
1497 CALLING_APP_ID,
1498 DOCUMENT_AMOUNT,
1499 DOCUMENT_CURRENCY_CODE,
1500 DOCUMENT_DATE,
1501 DOCUMENT_DESCRIPTION,
1502 DOCUMENT_PAYABLE_ID,
1503 DOCUMENT_TYPE,
1504 EXCLUSIVE_PAYMENT_FLAG,
1508 ORG_ID,
1505 EXTERNAL_BANK_ACCOUNT_ID,
1506 INTERNAL_BANK_ACCOUNT_ID,
1507 LEGAL_ENTITY_ID,
1509 ORG_TYPE,
1510 PAY_PROC_TRXN_TYPE_CODE,
1511 PAYEE_PARTY_ID,
1512 PAYEE_PARTY_SITE_ID,
1513 PAYMENT_AMOUNT,
1514 PAYMENT_CURRENCY_CODE,
1515 PAYMENT_DATE,
1516 PAYMENT_FUNCTION,
1517 PAYMENT_METHOD_CODE,
1518 PAYMENT_REASON_CODE,
1519 PAYMENT_REASON_COMMENTS,
1520 REMITTANCE_MESSAGE1,
1521 REMITTANCE_MESSAGE2,
1522 REMITTANCE_MESSAGE3,
1523 CREATED_BY,
1524 CREATION_DATE,
1525 LAST_UPDATED_BY,
1526 LAST_UPDATE_DATE,
1527 LAST_UPDATE_LOGIN,
1528 OBJECT_VERSION_NUMBER)
1529 VALUES(
1530 l_docs_payable_rec.anticipated_value_date,
1531 l_docs_payable_rec.allow_removing_document_flag,
1532 l_docs_payable_rec.bank_charge_bearer,
1533 l_docs_payable_rec.calling_app_doc_unique_ref1,
1534 l_docs_payable_rec.calling_app_doc_ref_number,
1535 l_docs_payable_rec.calling_app_id,
1536 l_docs_payable_rec.document_amount,
1537 l_docs_payable_rec.document_currency_code,
1538 l_docs_payable_rec.document_date,
1539 l_docs_payable_rec.document_description,
1540 l_docs_payable_rec.document_payable_id,
1541 l_docs_payable_rec.document_type,
1542 l_docs_payable_rec.exclusive_payment_flag,
1543 l_docs_payable_rec.external_bank_account_id,
1544 l_docs_payable_rec.internal_bank_account_id,
1545 l_docs_payable_rec.legal_entity_id,
1546 l_docs_payable_rec.org_id,
1547 l_docs_payable_rec.org_type,
1548 l_docs_payable_rec.pay_proc_trxn_type_code,
1549 l_docs_payable_rec.payee_party_id,
1550 l_docs_payable_rec.payee_party_site_id,
1551 l_docs_payable_rec.payment_amount,
1552 l_docs_payable_rec.payment_currency_code,
1553 l_docs_payable_rec.payment_date,
1554 l_docs_payable_rec.payment_function,
1555 l_docs_payable_rec.payment_method_code,
1556 l_docs_payable_rec.payment_reason_code,
1557 l_docs_payable_rec.payment_reason_comments,
1558 l_docs_payable_rec.remittance_message1,
1559 l_docs_payable_rec.remittance_message2,
1560 l_docs_payable_rec.remittance_message3,
1561 NVL(FND_GLOBAL.user_id,-1),
1562 SYSDATE,
1563 NVL(FND_GLOBAL.user_id,-1),
1564 SYSDATE,
1565 NVL(FND_GLOBAL.user_id,-1),
1566 1);
1567
1568 log('calling validate_documents...');
1569 -- Call iby validate api
1570 IBY_DISBURSEMENT_COMP_PUB.validate_documents(
1571 P_API_VERSION => 1.0,
1572 P_INIT_MSG_LIST => FND_API.G_FALSE,
1573 P_DOCUMENT_ID => l_docs_payable_rec.document_payable_id,
1574 X_RETURN_STATUS => l_return_status,
1575 X_MSG_COUNT => l_msg_count,
1576 X_MSG_DATA => l_msg_data);
1577
1578 p_doc_payable_id := l_docs_payable_rec.document_payable_id;
1579 p_return_status := l_return_status;
1580
1581 log('<< call_iby_validate... result=' || l_return_status);
1582 EXCEPTION
1583 WHEN OTHERS THEN
1584 log('Exception in call_iby_validate');
1585 --p_return_status := FND_API.G_RET_STS_EXCEPTION;
1586 p_return_status := 'EXCEPTION';
1587 RAISE;
1588 END call_iby_validate;
1589
1590
1591 /* --------------------------------------------------------------------
1592 | PUBLIC PROCEDURE |
1593 | check_create_ext_bank_acct
1594 | DESCRIPTION |
1595 | This procedure checks if the destination bank account of a transfer|
1596 | exists as an external bank account in iby. If it does't exists then |
1597 | it creates one. This procedure is called from the UI validate action|
1598 | if settlement is required.
1602 --------------------------------------------------------------------- */
1599 | |
1600 | HISTORY |
1601 | 24-JUL-2005 Shaik Vali Created |
1603
1604 PROCEDURE check_create_ext_bank_acct (p_bank_account_id NUMBER,
1605 p_ext_bank_account_id OUT NOCOPY NUMBER,
1606 p_return_status OUT NOCOPY VARCHAR2)
1607 IS
1608 l_return_status VARCHAR2(20);
1609 l_ext_bankacct_rec IBY_EXT_BANKACCT_PUB.Extbankacct_rec_type;
1610 l_msg_count NUMBER;
1611 l_msg_data VARCHAR2(1000);
1612 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1613 l_start_date DATE;
1614 l_end_date DATE;
1615 BEGIN
1616 log('>> check_create_ext_bank_acct');
1617 SELECT
1618 bb.bank_home_country,
1619 bb.branch_party_id,
1620 bb.bank_party_id,
1621 ba.account_owner_party_id,
1622 ba.bank_account_name,
1623 ba.bank_account_num,
1624 ba.currency_code,
1625 ba.iban_number,
1626 ba.check_digits,
1627 ba.multi_currency_allowed_flag,
1628 ba.bank_account_name_alt,
1629 ba.short_account_name,
1630 ba.bank_account_type,
1631 ba.account_suffix,
1632 ba.description,
1633 ba.agency_location_code,
1634 'N'
1635 INTO
1636 l_ext_bankacct_rec.COUNTRY_CODE,
1637 l_ext_bankacct_rec.BRANCH_ID,
1638 l_ext_bankacct_rec.BANK_ID,
1639 l_ext_bankacct_rec.ACCT_OWNER_PARTY_ID,
1640 l_ext_bankacct_rec.BANK_ACCOUNT_NAME,
1641 l_ext_bankacct_rec.BANK_ACCOUNT_NUM,
1642 l_ext_bankacct_rec.CURRENCY,
1643 l_ext_bankacct_rec.IBAN,
1644 l_ext_bankacct_rec.CHECK_DIGITS,
1645 l_ext_bankacct_rec.MULTI_CURRENCY_ALLOWED_FLAG,
1646 l_ext_bankacct_rec.ALTERNATE_ACCT_NAME,
1647 l_ext_bankacct_rec.SHORT_ACCT_NAME,
1648 l_ext_bankacct_rec.ACCT_TYPE,
1649 l_ext_bankacct_rec.ACCT_SUFFIX,
1650 l_ext_bankacct_rec.DESCRIPTION,
1651 l_ext_bankacct_rec.AGENCY_LOCATION_CODE,
1652 l_ext_bankacct_rec.PAYMENT_FACTOR_FLAG
1653 FROM
1654 ce_bank_accounts ba,
1655 ce_bank_branches_v bb
1656 WHERE
1657 ba.bank_branch_id = bb.branch_party_id
1658 AND ba.bank_account_id = p_bank_account_id;
1659
1660 log('checking if the ext bank account already exists...');
1661 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
1662 p_api_version => 1.0,
1663 p_bank_id => l_ext_bankacct_rec.bank_id,
1664 p_branch_id => l_ext_bankacct_rec.branch_id,
1665 p_acct_number => l_ext_bankacct_rec.bank_account_num,
1666 p_acct_name => l_ext_bankacct_rec.bank_account_name,
1667 p_currency => l_ext_bankacct_rec.currency,
1668 p_country_code => l_ext_bankacct_rec.country_code,
1669 x_acct_id => p_ext_bank_account_id,
1670 x_start_date => l_start_date,
1671 x_end_date => l_end_date,
1672 x_return_status => l_return_status,
1673 x_msg_count => l_msg_count,
1674 x_msg_data => l_msg_data,
1675 x_response => l_response);
1676
1677 p_return_status := l_return_status;
1678 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1679 IF p_ext_bank_account_id IS NOT NULL THEN
1680 -- external bank account already exists
1681 IF nvl(l_start_date, SYSDATE) <= SYSDATE AND
1682 nvl(l_end_date, SYSDATE) >= SYSDATE THEN
1683 RETURN;
1684 ELSE
1685 FND_MESSAGE.set_name('CE','CE_BAT_EXT_BA_END');
1686 FND_MSG_PUB.add;
1687 RETURN;
1688 END IF;
1689 END IF;
1690 END IF;
1691
1692 p_return_status := l_return_status;
1693
1694 log('creating the ext bank account ...');
1695 IBY_EXT_BANKACCT_PUB.create_ext_bank_acct(
1696 P_API_VERSION => 1.0,
1697 p_INIT_MSG_LIST => FND_API.G_FALSE,
1698 p_EXT_BANK_ACCT_REC => l_ext_bankacct_rec,
1699 X_ACCT_ID => p_ext_bank_account_id,
1700 X_RETURN_STATUS => l_return_status,
1701 X_MSG_COUNT => l_msg_count,
1702 X_MSG_DATA => l_msg_data,
1703 X_RESPONSE => l_response);
1704
1705 p_return_status := l_return_status;
1706 log('<< check_create_ext_bank_acct'||p_return_status);
1707 EXCEPTION
1708 WHEN OTHERS THEN
1709 log('Exception in check_create_ext_bank_acct');
1710 RAISE;
1711 END check_create_ext_bank_acct;
1712
1713
1714 /* --------------------------------------------------------------------
1715 | PUBLIC PROCEDURE |
1716 | create_external_payee
1717 | DESCRIPTION |
1718 | This procedure creates is called from the UI on validate action. |
1719 | The destination LE of a transfer has to created as an external |
1720 | payee in IBY.
1721 | |
1722 | HISTORY |
1723 | 24-JUL-2005 Shaik Vali Created |
1724 --------------------------------------------------------------------- */
1725
1726 PROCEDURE create_external_payee(p_trxn_reference_number NUMBER,
1727 p_return_status OUT NOCOPY VARCHAR2)
1728 IS
1729 l_ext_payee_rec IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type;
1730 l_ext_payee_tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type;
1731 l_ext_payee_id_rec IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Rec_Type;
1732 l_ext_payee_id_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type;
1736 l_msg_data VARCHAR2(2000);
1733 l_ext_payee_create_rec IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Rec_Type;
1734 l_ext_payee_create_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type;
1735 l_msg_count NUMBER;
1737 l_return_status VARCHAR2(1);
1738 BEGIN
1739 log('>> create_external_payee');
1740 SELECT
1741 destination_party_id,
1742 destination_party_site_id,
1743 source_legal_entity_id,
1744 'LEGAL_ENTITY',
1745 'CASH_PAYMENT',
1746 'Y'
1747 INTO
1748 l_ext_payee_rec.payee_party_id,
1749 l_ext_payee_rec.payee_party_site_id,
1750 l_ext_payee_rec.payer_org_id,
1751 l_ext_payee_rec.payer_org_type,
1752 l_ext_payee_rec.payment_function,
1753 l_ext_payee_rec.exclusive_pay_flag
1754 FROM
1755 ce_payment_transactions
1756 WHERE trxn_reference_number = p_trxn_reference_number;
1757
1758 l_ext_payee_tab(1) := l_ext_payee_rec;
1759
1760
1761 IBY_DISBURSEMENT_SETUP_PUB.create_external_payee(
1762 p_api_version => 1,
1763 p_init_msg_list => FND_API.G_FALSE,
1764 p_ext_payee_tab => l_ext_payee_tab,
1765 x_return_status => l_return_status,
1766 x_msg_count => l_msg_count,
1767 x_msg_data => l_msg_data,
1768 x_ext_payee_id_tab => l_ext_payee_id_tab,
1769 x_ext_payee_status_tab => l_ext_payee_create_tab);
1770
1771 p_return_status := l_return_status;
1772
1773 log('<< create_external_payee');
1774 EXCEPTION
1775 WHEN OTHERS THEN
1776 log('Exception in create_external_payee');
1777 RAISE;
1778 END create_external_payee;
1779
1780
1781 /* --------------------------------------------------------------------
1782 | PUBLIC PROCEDURE |
1783 | iby_validations
1784 | DESCRIPTION |
1785 | This procedure summarizes all the validations required for a transfer|
1786 | when settlement is required. This procedures does the following:|
1787 | 1)Checks and creates an external bank account in IBY for the |
1788 | destination bank acount of the transfer.
1789 | 2)Creates an external payee in IBY for the destination LE of the|
1790 | transfer.
1791 | 3)Calls IBY's online validations api.
1792 | |
1793 | HISTORY |
1794 | 24-JUL-2005 Shaik Vali Created |
1795 --------------------------------------------------------------------- */
1796
1797 PROCEDURE iby_validations(p_bank_account_id NUMBER,
1798 p_trxn_reference_number NUMBER,
1799 p_result OUT NOCOPY VARCHAR2)
1800 IS
1801 l_return_status VARCHAR2(20);
1802 l_ext_bank_account_id NUMBER;
1803 l_doc_payable_id NUMBER;
1804 l_cnt NUMBER;
1805 BEGIN
1806 log('>> iby_validations');
1807 -- check and create the external bank account
1808 check_create_ext_bank_acct(p_bank_account_id,
1809 l_ext_bank_account_id,
1810 l_return_status);
1811
1812 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1813 UPDATE ce_payment_transactions
1814 SET ext_bank_account_id = l_ext_bank_account_id
1815 WHERE trxn_reference_number = p_trxn_reference_number;
1816
1817 p_result := 'SUCCESS';
1818 ELSE
1819 p_result := 'FAILURE';
1820 RETURN;
1821 END IF;
1822
1823 -- create the external payee
1824 log('creating external payee..');
1825 create_external_payee(p_trxn_reference_number,
1826 l_return_status);
1827
1828 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1829 p_result := 'SUCCESS';
1830 ELSE
1831 p_result := 'FAILURE';
1832 RETURN;
1833 END IF;
1834
1835 -- call the IBY's validation API
1836 log('calling iby validations..');
1837 call_iby_validate(p_trxn_reference_number,
1838 l_doc_payable_id,
1839 l_return_status);
1840
1841 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1842 p_result := 'SUCCESS';
1843 ELSE
1844 p_result := 'FAILURE';
1845 END IF;
1846 log('<< iby_validations result=' || p_result);
1847 EXCEPTION
1848 WHEN OTHERS THEN
1849 log('Exception in iby_validations');
1850 p_result := 'FAILURE';
1851 RAISE;
1852 END iby_validations;
1853
1854
1855 PROCEDURE cancel_cashflow(
1856 p_cashflow_id NUMBER,
1857 p_result OUT NOCOPY VARCHAR2
1858 )
1859 IS
1860
1861 l_cashflow_id NUMBER;
1862
1863 BEGIN
1864 log('>>cancel cashflow');
1865 l_cashflow_id := p_cashflow_id;
1866 log('cashflow id '|| l_cashflow_id);
1867
1868 log('calling XLA cancel event...'|| l_cashflow_id);
1869 -- call the XLA API to cancel the BAT
1870 CE_XLA_ACCT_EVENTS_PKG.create_event
1871 (l_cashflow_id,'CE_STMT_CANCELED');
1872
1873 -- Update the cashflows status to CANCELED
1874 UPDATE ce_cashflows
1875 SET cashflow_status_code = 'CANCELED'
1876 WHERE cashflow_id = l_cashflow_id;
1877
1878 /*bug4997215*/
1879 -- Update statement line to nullify the cashflow id
1880 UPDATE ce_statement_lines
1881 SET cashflow_id = null,
1882 je_status_flag = null
1883 WHERE cashflow_id = l_cashflow_id;
1884 p_result := 'SUCCESS';
1885 log('<<cancel cashflow');
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888 p_result := 'FAIL';
1889 log('Exception in cancel_cashflow');
1890 RAISE;
1891 END cancel_cashflow;
1892
1893
1894 PROCEDURE create_update_cashflows(p_trxn_reference_number NUMBER,
1895 p_mode OUT NOCOPY VARCHAR2,
1899 BEGIN
1896 p_cashflow_id1 OUT NOCOPY NUMBER,
1897 p_cashflow_id2 OUT NOCOPY NUMBER)
1898 IS
1900 log('>>create_update_cashflows..');
1901 log('>>creating cashflows..');
1902 --create cashflows
1903 CE_BAT_UTILS.transfer_payment_transaction(
1904 p_trxn_reference_number,
1905 G_multi_currency_flag,
1906 p_mode,
1907 p_cashflow_id1,
1908 p_cashflow_id2);
1909
1910 UPDATE ce_statement_lines
1911 set cashflow_id=p_cashflow_id1
1912 WHERE statement_line_id=G_sl_statement_line_id;
1913
1914 log('<<..create_update_cashflows');
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 log('Exception in create_update_cashflows..');
1918 RAISE;
1919 END create_update_cashflows;
1920
1921 /* ---------------------------------------------------------------------
1922 | PUBLIC PROCEDURE |
1923 | validate_foreign_currency |
1924 | DESCRIPTION |
1925 | This procedure is called by the UI while creating a transfer for |
1926 | checking that the currencies for both accounts involved in bank |
1927 | account transfers are not non-functional(bug 6046852) |
1928 | HISTORY |
1929 | 13-JUL-2007 Varun Netan Created |
1930 | 07-FEB-2008 kbabu Modified for bug 6455698 |
1931 --------------------------------------------------------------------- */
1932 PROCEDURE validate_foreign_currency(
1933 p_source_le_id NUMBER,
1934 p_destination_le_id NUMBER,
1935 p_source_ba_id VARCHAR2,
1936 p_destination_ba_id VARCHAR2,
1937 p_pmt_currency VARCHAR2, --for bug 6455698
1938 p_error_code OUT NOCOPY VARCHAR2 --for bug 6455698
1939 )
1940 IS
1941 l_source_ledger_id NUMBER := NULL;
1942 l_destination_ledger_id NUMBER := NULL;
1943 l_source_ba_currency_code CE_BANK_ACCOUNTS.currency_code%TYPE := NULL;
1944 l_destination_ba_currency_code CE_BANK_ACCOUNTS.currency_code%TYPE := NULL;
1945 l_source_ledger_curr GL_LEDGERS.currency_code%TYPE := NULL;
1946 l_destination_ledger_curr GL_LEDGERS.currency_code%TYPE := NULL;
1947
1948 BEGIN
1949 log('>> validate_foreign_currency.....');
1950 p_error_code := NULL;
1951
1952 -- Get the bank account currencies for source/destination
1953 SELECT currency_code
1954 INTO l_source_ba_currency_code
1955 FROM ce_bank_accounts
1956 WHERE bank_account_id = p_source_ba_id;
1957
1958 SELECT currency_code
1959 INTO l_destination_ba_currency_code
1960 FROM ce_bank_accounts
1961 WHERE bank_account_id = p_destination_ba_id;
1962
1963 --Transaction currency is the same as the bank account currency on both sides
1964 --then it is valid bat
1968 RETURN;
1965 If (p_pmt_currency = l_source_ba_currency_code) and
1966 (p_pmt_currency = l_destination_ba_currency_code) then
1967 p_error_code := NULL;
1969 END IF;
1970
1971 --Get the ledger currencies for source/destination
1972 l_source_ledger_id := CE_BAT_UTILS.get_ledger_id(p_source_le_id);
1973 l_destination_ledger_id:=CE_BAT_UTILS.get_ledger_id(p_destination_le_id);
1974 SELECT currency_code
1975 INTO l_source_ledger_curr
1976 FROM gl_ledgers
1977 WHERE ledger_id=l_source_ledger_id;
1978
1979 SELECT currency_code
1980 INTO l_destination_ledger_curr
1981 FROM gl_ledgers
1982 WHERE ledger_id=l_destination_ledger_id;
1983
1984 IF p_pmt_currency = l_source_ba_currency_code THEN
1985 IF l_destination_ledger_curr <> l_destination_ba_currency_code THEN
1986 p_error_code := 'CE_BAT_INVALID_DEST_BANK';
1987 RETURN;
1988 END IF;
1989 ELSIF p_pmt_currency = l_destination_ba_currency_code THEN
1990 IF l_source_ledger_curr <> l_source_ba_currency_code THEN
1991 p_error_code := 'CE_BAT_INVALID_SRC_BANK';
1992 RETURN;
1993 END IF;
1994 ELSE
1995 -- Transaction currency should be either Source bank account currency or Destination bank account currency.
1996 p_error_code := 'CE_BAT_INVALID_CURRENCY';
1997 RETURN;
1998 END IF;
1999
2000 log('<< validate_foreign_currency');
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 p_error_code := 'FAILURE';
2004 log('Exception in validate_foreign_currency');
2005 RAISE;
2006 END validate_foreign_currency;
2007
2008
2009 END CE_BAT_API;
2010