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