[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_CLEAR1
Source
1 PACKAGE BODY CE_AUTO_BANK_CLEAR1 AS
2 /* $Header: ceabrc1b.pls 120.49.12020000.7 2013/03/21 05:23:01 ckansara ship $ */
3 -- l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
4 l_DEBUG varchar2(1) := 'Y';
5
6 CURSOR rbatch_cursor (rbatch_id NUMBER ) IS
7 SELECT a.trx_id cash_receipt_history_id,
8 a.cash_receipt_id cash_receipt_id,
9 a.trx_type trx_type,
10 a.trx_date trx_date,
11 a.status status,
12 a.bank_account_amount ba_amount,
13 a.amount amount,
14 --a.receipt_gl_date receipt_gl_date,
15 a.gl_date receipt_gl_date,
16 a.exchange_rate_date exchange_rate_date,
17 a.exchange_rate_type exchange_rate_type,
18 a.seq_id seq_id
19 --FROM ce_222_txn_for_batch_v a
20 FROM ce_available_transactions_tmp a
21 WHERE a.batch_id = rbatch_id
22 AND nvl(a.status, 'REMITTED') <> 'REVERSED'
23 AND a.application_id = 222
24 AND NVL(a.reconciled_status_flag, 'N') = 'N';
25
26 -- Fix bug 5637589 for manual remittance batch reconciliation
27 CURSOR manual_rbatch_cursor (rbatch_id NUMBER ) IS
28 SELECT a.trx_id cash_receipt_history_id,
29 a.cash_receipt_id cash_receipt_id,
30 a.trx_type trx_type,
31 a.trx_date trx_date,
32 a.status status,
33 a.bank_account_amount ba_amount,
34 a.amount amount,
35 a.receipt_gl_date receipt_gl_date,
36 a.exchange_rate_date exchange_rate_date,
37 a.exchange_rate_type exchange_rate_type,
38 a.org_id
39 FROM ce_222_txn_for_batch_v a
40 --FROM ce_available_transactions_tmp a
41 WHERE a.batch_id = rbatch_id
42 AND nvl(a.status, 'REMITTED') <> 'REVERSED';
43
44 -- BUG 4435028 added CE trx to payment batches
45 -- Bug 7506336 added NVL to the logical_group_reference clause
46 CURSOR pbatch_cursor (pbatch_id NUMBER,
47 pgroup_id varchar2) IS -- FOR SEPA ER 6700007
48 SELECT a.trx_id check_id,
49 a.status status_lookup_code,
50 'PAYMENT' batch_trx_type,
51 a.cash_receipt_id batch_app_id,
52 a.seq_id seq_id
53 --FROM ce_200_transactions_v a
54 FROM ce_available_transactions_tmp a
55 WHERE a.batch_id = pbatch_id
56 AND nvl(a.status, 'NEGOTIABLE') <> 'VOIDED'
57 AND a.application_id = 200
58 AND NVL(a.reconciled_status_flag, 'N') = 'N'
59 AND EXISTS ( SELECT 1
60 FROM iby_payments_all IPA ,AP_CHECKS_ALL ACA
61 WHERE ACA.CHECK_ID =a.trx_id
62 AND ACA.PAYMENT_INSTRUCTION_ID = pbatch_id
63 AND IPA.PAYMENT_INSTRUCTION_ID (+) = pbatch_id -- Bug # 8353600 Added Outer Join (+)
64 AND IPA.PAYMENT_ID (+) = ACA.PAYMENT_ID -- Bug # 8353600 Added Outer Join (+)
65 AND NVL(IPA.LOGICAL_GROUP_REFERENCE,'N') = NVL(pgroup_id,NVL(IPA.LOGICAL_GROUP_REFERENCE,'N')))
66 UNION ALL
67 SELECT a.trx_id,
68 a.status,
69 'CASHFLOW',
70 673,
71 a.seq_id
72 --FROM ce_260_cf_transactions_v a
73 FROM ce_available_transactions_tmp a
74 WHERE a.batch_id = pbatch_id
75 AND nvl(a.status, 'CANCELED') <> 'CANCELED'
76 AND a.application_id = 261
77 AND NVL(a.reconciled_status_flag, 'N') = 'N'
78 AND pgroup_id is null ; -- FOR SEPA ER 6700007
79
80 -- BUG 5350073 use for manual reconcilation of IBY batches
81 -- Bug 7506336 added NVL to the logical_group_reference clause
82 CURSOR manual_pbatch_cursor (pbatch_id NUMBER,pgroup_id VARCHAR2) IS -- FOR SEPA ER 6700007
83 SELECT a.trx_id check_id,
84 a.status status_lookup_code,
85 'PAYMENT' batch_trx_type,
86 a.cash_receipt_id batch_app_id,
87 a.org_id org_id,
88 a.legal_entity_id legal_entity_id
89 FROM ce_200_transactions_v a
90 --FROM ce_available_transactions_tmp a
91 WHERE a.batch_id = pbatch_id
92 AND nvl(a.status, 'NEGOTIABLE') <> 'VOIDED'
93 -- FOR SEPA ER 6700007
94 AND EXISTS ( SELECT 1
95 FROM iby_payments_all IPA ,AP_CHECKS_ALL ACA
96 WHERE ACA.CHECK_ID =a.trx_id
97 AND ACA.PAYMENT_INSTRUCTION_ID = pbatch_id
98 AND IPA.PAYMENT_INSTRUCTION_ID (+) = pbatch_id -- Bug # 8353600 Added Outer Join (+)
99 AND IPA.PAYMENT_ID (+) = ACA.PAYMENT_ID -- Bug # 8353600 Added Outer Join (+)
100 AND NVL(IPA.LOGICAL_GROUP_REFERENCE,'N') = NVL(pgroup_id,NVL(IPA.LOGICAL_GROUP_REFERENCE,'N')))
101 --AND a.application_id = 200
102 --AND NVL(a.reconciled_status_flag, 'N') = 'N'
103 UNION ALL
104 SELECT a.trx_id,
105 a.status,
106 'CASHFLOW',
107 673,
108 a.org_id,
109 a.legal_entity_id
110 FROM ce_260_cf_transactions_v a
111 --FROM ce_available_transactions_tmp a
112 WHERE a.batch_id = pbatch_id
113 AND nvl(a.status, 'CANCELED') <> 'CANCELED'
114 AND pgroup_id is null ; -- FOR SEPA ER 6700007
115 --AND a.application_id = 261;
116 --AND NVL(a.reconciled_status_flag, 'N') = 'N';
117
118 CURSOR C_STATEMENT_LINE_SEQ IS SELECT ce_statement_lines_s.nextval from sys.dual;
119
120 FUNCTION body_revision RETURN VARCHAR2 IS
121 BEGIN
122
123 RETURN '$Revision: 120.49.12020000.7 $';
124
125 END body_revision;
126
127 FUNCTION spec_revision RETURN VARCHAR2 IS
128 BEGIN
129
130 RETURN G_spec_revision;
131
132 END spec_revision;
133
134 /* ---------------------------------------------------------------------
135 | PRIVATE PROCEDURE |
136 | validate_effective_date |
137 | CALLED BY |
138 | reconcile_trx, reconcile_stmt, reconcile_pbatch, reoncile_rbatch|
139 --------------------------------------------------------------------- */
140
141 PROCEDURE validate_effective_date( passin_mode VARCHAR2,
142 X_effective_date DATE,
143 X_float_handling_flag VARCHAR2 ) IS
144 BEGIN
145 IF l_DEBUG in ('Y', 'C') THEN
146 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.validate_effective_date');
147 END IF;
148 IF (X_effective_date > sysdate AND
149 X_float_handling_flag = 'E' AND
150 passin_mode = 'AUTO') THEN
151 FND_MESSAGE.set_name( 'CE','CE_FLOAT_VIOLATION');
152 RAISE APP_EXCEPTION.application_exception;
153 END IF;
154 IF l_DEBUG in ('Y', 'C') THEN
155 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.validate_effective_date');
156 END IF;
157 END validate_effective_date;
158
159 /* ---------------------------------------------------------------------
160 | PRIVATE PROCEDURE |
161 | check_matching_status |
162 | |
163 | DESCRIPTION |
164 | Find out NOCOPY if a receipt has already been matched to Statement line|
165 | Original design was to have this SQL to be included into |
166 | CE_AVAILABLE_TRANSACTIONS_V. For performance etc. reasons |
167 | it was moved into reconciliation. |
168 | Since AR allows RISK_ELIMINATION of already reconciled/matched |
169 | receipts, we need to check the status of the receipt, and |
170 | and compare it against the receipt to be matched |
171 | To be reconciled Already reconciled Outcome |
172 | -- CLEARED,RISK_ELIMINATED RISK_ELIMINATED NOT AVAILABLE |
173 | -- CLEARED,RISK_ELIMINATED CLEARED NOT AVAILABLE |
174 | -- CLEARED,RISK_ELIMINATED REMITTED AVAILABLE |
175 | -- CLEARED,RISK_ELIMINATED REVERSED AVAILABLE |
176 | |
177 | RETURNS |
178 | Status Status of the cash_receipt_history_record matched |
179 --------------------------------------------------------------------- */
180 FUNCTION check_matching_status(cr_id IN NUMBER,
181 orig_status IN VARCHAR2) RETURN BOOLEAN IS
182 x_status AR_CASH_RECEIPT_HISTORY_ALL.status%TYPE;
183 BEGIN
184 IF l_DEBUG in ('Y', 'C') THEN
185 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.check_matching_status');
186 END IF;
187 SELECT crh.status
188 INTO x_status
189 FROM ce_statement_recon_gt_v rec, --ce_statement_reconcils_all rec,
190 ar_cash_receipt_history_all crh,
191 ar_cash_receipts_all cr
192 WHERE rec.current_record_flag = 'Y' AND
193 rec.status_flag = 'M' AND
194 rec.reference_type = 'RECEIPT' AND
195 rec.reference_id = crh.cash_receipt_history_id AND
196 crh.cash_receipt_id = cr.cash_receipt_id AND
197 cr.cash_receipt_id = cr_id;
198 IF (orig_status IN ('CLEARED','RISK_ELIMINATED') AND
199 x_status IN ('CLEARED','RISK_ELIMINATED')) THEN
200 IF l_DEBUG in ('Y', 'C') THEN
201 cep_standard.debug('check_matching_status: ' || 'NOT AVAILABLE FOR RECONCILIATION');
202 END IF;
203 return(FALSE);
204 ELSE
205 return(TRUE);
206 END IF;
207 IF l_DEBUG in ('Y', 'C') THEN
208 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.check_matching_status');
209 END IF;
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 IF l_DEBUG in ('Y', 'C') THEN
213 cep_standard.debug('check_matching_status: ' || 'No data found: not an error');
214 END IF;
215 RETURN TRUE;
216 WHEN OTHERS THEN
217 IF l_DEBUG in ('Y', 'C') THEN
218 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR1.check_matching_status');
219 END IF;
220 RAISE;
221 END check_matching_status;
222
223 /* ---------------------------------------------------------------------
224 | PRIVATE PROCEDURE |
225 | update_line_unreconciled |
226 | DESCRIPTION |
227 | Checks if the statement line is fully unreconciled |
228 | and updates the status accordingly |
229 --------------------------------------------------------------------- */
230 PROCEDURE update_line_unreconciled (X_statement_line_id NUMBER) IS
231 c_count_reconciled NUMBER;
232 BEGIN
233 IF l_DEBUG in ('Y', 'C') THEN
234 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
235 END IF;
236
237 SELECT count(*)
238 INTO c_count_reconciled
239 FROM CE_STATEMENT_RECONCILS_ALL
240 WHERE statement_line_id = X_statement_line_id
241 AND current_record_flag = 'Y'
242 AND status_flag = 'M';
243
244 IF (c_count_reconciled = 0) THEN
245 CE_AUTO_BANK_CLEAR.update_line_status(X_statement_line_id,'UNRECONCILED');
246 END IF;
247 IF l_DEBUG in ('Y', 'C') THEN
248 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
249 END IF;
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF l_DEBUG in ('Y', 'C') THEN
253 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
254 END IF;
255 RAISE;
256 END update_line_unreconciled;
257
258 /* ---------------------------------------------------------------------
259 | PRIVATE PROCEDURE |
260 | create_statement_line |
261 | DESCRIPTION |
262 | Inserts records into CE_STATEMENT_LINES . |
263 --------------------------------------------------------------------- */
264 PROCEDURE create_statement_line IS
265 BEGIN
266 IF l_DEBUG in ('Y', 'C') THEN
267 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.create_statement_line');
268 END IF;
269 CE_STAT_LINES_DML_PKG.Insert_Row(
270 X_Row_Id => CE_AUTO_BANK_MATCH.csl_rowid,
271 X_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
272 X_statement_header_id => CE_AUTO_BANK_MATCH.csh_statement_header_id,
273 X_line_number => CE_AUTO_BANK_MATCH.csl_line_number,
274 X_trx_date => CE_AUTO_BANK_MATCH.csl_trx_date,
275 X_trx_type => CE_AUTO_BANK_MATCH.csl_trx_type,
276 X_trx_status => CE_AUTO_BANK_MATCH.trx_status,
277 X_trx_code => NULL,
278 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
279 X_bank_trx_number => CE_AUTO_BANK_MATCH.csl_bank_trx_number,
280 X_trx_text => NULL,
281 X_customer_text => NULL,
282 X_invoice_text => NULL,
283 X_bank_account_text => NULL,
284 X_amount => CE_AUTO_BANK_MATCH.csl_amount,
285 X_charges_amount => CE_AUTO_BANK_MATCH.csl_charges_amount,
286 X_status => 'RECONCILED',
287 X_created_by => NVL(FND_GLOBAL.user_id,-1),
288 X_creation_date => sysdate,
289 X_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
290 X_last_update_date => sysdate,
291 X_currency_code => CE_AUTO_BANK_MATCH.csl_currency_code,
292 X_original_amount => CE_AUTO_BANK_MATCH.csl_original_amount,
293 X_exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate,
294 X_exchange_rate_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
295 X_exchange_rate_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
296 X_attribute_category => NULL,
297 X_attribute1 => NULL,
298 X_attribute2 => NULL,
299 X_attribute3 => NULL,
300 X_attribute4 => NULL,
301 X_attribute5 => NULL,
302 X_attribute6 => NULL,
303 X_attribute7 => NULL,
304 X_attribute8 => NULL,
305 X_attribute9 => NULL,
306 X_attribute10 => NULL,
307 X_attribute11 => NULL,
308 X_attribute12 => NULL,
309 X_attribute13 => NULL,
310 X_attribute14 => NULL,
311 X_attribute15 => NULL,
312 -- 5916290: GDF Changes
313 X_global_att_category => NULL,
314 X_global_attribute1 => NULL,
315 X_global_attribute2 => NULL,
316 X_global_attribute3 => NULL,
317 X_global_attribute4 => NULL,
318 X_global_attribute5 => NULL,
319 X_global_attribute6 => NULL,
320 X_global_attribute7 => NULL,
321 X_global_attribute8 => NULL,
322 X_global_attribute9 => NULL,
323 X_global_attribute10 => NULL,
324 X_global_attribute11 => NULL,
325 X_global_attribute12 => NULL,
326 X_global_attribute13 => NULL,
327 X_global_attribute14 => NULL,
328 X_global_attribute15 => NULL,
329 X_global_attribute16 => NULL,
330 X_global_attribute17 => NULL,
331 X_global_attribute18 => NULL,
332 X_global_attribute19 => NULL,
333 X_global_attribute20 => NULL
334 );
335 IF l_DEBUG in ('Y', 'C') THEN
336 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.create_statement_line');
337 END IF;
338 END create_statement_line;
339
340 /* ---------------------------------------------------------------------
341 | PRIVATE PROCEDURE |
342 | insert_reconciliation |
343 | DESCRIPTION |
344 | Inserts records into CE_STATEMENT_RECONCILIATIONS. |
345 --------------------------------------------------------------------- */
346 PROCEDURE insert_reconciliation (
347 Y_statement_line_id NUMBER ,
348 Y_cleared_trx_type VARCHAR2,
349 Y_cleared_trx_id NUMBER,
350 Y_ar_cash_receipt_id NUMBER,
351 Y_reference_status VARCHAR2,
352 Y_auto_reconciled_flag VARCHAR2,
353 Y_status_flag VARCHAR2,
354 Y_amount NUMBER ) IS
355 Y_rowid VARCHAR2(100);
356 Y_org_id number(15);
357 Y_legal_entity_id number(15);
358 BEGIN
359 IF l_DEBUG in ('Y', 'C') THEN
360 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.insert_reconciliation');
361 END IF;
362
363 --Y_org_id := nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_REC.G_legal_entity_id);
364 --Y_org_id := nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.bau_org_id);
365 --Y_legal_entity_id := nvl(CE_AUTO_BANK_REC.G_legal_entity_id,CE_AUTO_BANK_MATCH.bau_legal_entity_id);
366
367 Y_org_id := nvl(nvl(CE_AUTO_BANK_CLEAR.G_org_id,CE_AUTO_BANK_MATCH.trx_org_id),CE_AUTO_BANK_REC.G_org_id) ;
368 Y_legal_entity_id := nvl(nvl(CE_AUTO_BANK_CLEAR.G_legal_entity_id,CE_AUTO_BANK_MATCH.trx_legal_entity_id),
369 CE_AUTO_BANK_REC.G_legal_entity_id);
370
371 IF l_DEBUG in ('Y', 'C') THEN
372 cep_standard.debug('Y_org_id = ' ||Y_org_id || ', Y_legal_entity_id = ' ||Y_legal_entity_id||
373 ', CE_AUTO_BANK_MATCH.csl_reconcile_flag = ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag ||
374 ', Y_cleared_trx_type = ' ||Y_cleared_trx_type);
375 cep_standard.debug('call CE_STATEMENT_RECONS_PKG.insert_row cestmreb');
376 END IF;
377
378 IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE') THEN
379 CE_STATEMENT_RECONS_PKG.insert_row(
380 X_row_id => Y_rowid,
381 X_statement_line_id => Y_statement_line_id,
382 X_reference_type => Y_cleared_trx_type,
383 X_reference_id => Y_cleared_trx_id,
384 X_je_header_id => Y_ar_cash_receipt_id,
385 X_org_id => NULL,
386 X_legal_entity_id => NULL,
387 X_reference_status => Y_reference_status,
388 X_amount => Y_amount,
389 X_status_flag => Y_status_flag,
390 X_action_flag => 'C',
391 X_current_record_flag => 'Y',
392 X_auto_reconciled_flag => Y_auto_reconciled_flag,
393 X_created_by => nvl(FND_GLOBAL.user_id,-1),
394 X_creation_date => sysdate,
395 X_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
396 X_last_update_date => sysdate,
397 X_request_id => nvl(FND_GLOBAL.conc_request_id,-1),
398 X_program_application_id =>nvl(FND_GLOBAL.prog_appl_id,-1),
399 X_program_id => nvl(FND_GLOBAL.conc_program_id,-1),
400 X_program_update_date => sysdate);
401 ELSE
402 IF (Y_cleared_trx_type IN ('ROI_LINE','STATEMENT')) THEN
403 CE_STATEMENT_RECONS_PKG.insert_row(
404 X_row_id => Y_rowid,
405 X_statement_line_id => Y_statement_line_id,
406 X_reference_type => Y_cleared_trx_type,
407 X_reference_id => Y_cleared_trx_id,
408 X_org_id => null,
409 X_legal_entity_id => null,
410 X_reference_status => Y_reference_status,
411 X_amount => Y_amount,
412 X_status_flag => Y_status_flag,
413 X_action_flag => 'C',
414 X_current_record_flag => 'Y',
415 X_auto_reconciled_flag => Y_auto_reconciled_flag,
416 X_created_by => nvl(FND_GLOBAL.user_id,-1),
417 X_creation_date => sysdate,
418 X_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
419 X_last_update_date => sysdate,
420 X_request_id => nvl(FND_GLOBAL.conc_request_id,-1),
421 X_program_application_id =>nvl(FND_GLOBAL.prog_appl_id,-1),
422 X_program_id => nvl(FND_GLOBAL.conc_program_id,-1),
423 X_program_update_date => sysdate);
424 ELSE
425 CE_STATEMENT_RECONS_PKG.insert_row(
426 X_row_id => Y_rowid,
427 X_statement_line_id => Y_statement_line_id,
428 X_reference_type => Y_cleared_trx_type,
429 X_reference_id => Y_cleared_trx_id,
430 X_org_id => Y_org_id,
431 X_legal_entity_id => Y_legal_entity_id,
432 X_reference_status => Y_reference_status,
433 X_amount => Y_amount,
434 X_status_flag => Y_status_flag,
435 X_action_flag => 'C',
436 X_current_record_flag => 'Y',
437 X_auto_reconciled_flag => Y_auto_reconciled_flag,
438 X_created_by => nvl(FND_GLOBAL.user_id,-1),
439 X_creation_date => sysdate,
440 X_last_updated_by => nvl(FND_GLOBAL.user_id,-1),
441 X_last_update_date => sysdate,
442 X_request_id => nvl(FND_GLOBAL.conc_request_id,-1),
443 X_program_application_id =>nvl(FND_GLOBAL.prog_appl_id,-1),
444 X_program_id => nvl(FND_GLOBAL.conc_program_id,-1),
445 X_program_update_date => sysdate);
446
447 END IF;
448 END IF;
449 IF l_DEBUG in ('Y', 'C') THEN
450 cep_standard.debug('end call CE_STATEMENT_RECONS_PKG.insert_row');
451 END IF;
452
453 if (CE_AUTO_BANK_MATCH.nsf_info_flag = 'Y') then
454 IF l_DEBUG in ('Y', 'C') THEN
455 cep_standard.debug('insert_reconciliation: ' || 'Insert CE_ABR_NSF_INFO warning.');
456 END IF;
457 CE_RECONCILIATION_ERRORS_PKG.insert_row(
458 CE_AUTO_BANK_MATCH.csh_statement_header_id,
459 CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NSF_INFO');
460 CE_AUTO_BANK_MATCH.nsf_info_flag := 'N';
461 end if;
462 if (CE_AUTO_BANK_MATCH.trx_clr_flag = 'Y') then
463 IF l_DEBUG in ('Y', 'C') THEN
464 cep_standard.debug('insert_reconciliation: ' || 'Insert CE_TRX_DATE_CLEARED_DATE warning.');
465 END IF;
466 CE_RECONCILIATION_ERRORS_PKG.insert_row(
467 CE_AUTO_BANK_MATCH.csh_statement_header_id,
468 CE_AUTO_BANK_MATCH.csl_statement_line_id,
469 'CE_TRX_DATE_CLEARED_DATE');
470 CE_AUTO_BANK_MATCH.trx_clr_flag := 'N';
471 end if;
472 IF l_DEBUG in ('Y', 'C') THEN
473 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.insert_reconciliation');
474 END IF;
475 EXCEPTION
476 WHEN OTHERS THEN
477 IF l_DEBUG in ('Y', 'C') THEN
478 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR1.insert_reconciliation');
479 END IF;
480 RAISE;
481 END insert_reconciliation;
482
483 /* ---------------------------------------------------------------------
484 | PRIVATE PROCEDURE |
485 | reconcile_rbatch |
486 | DESCRIPTION |
487 | Each receipt within the remittance batch must be cleared and |
488 | reconciled. |
489 | CALLED BY |
490 | reconcile_process |
491 --------------------------------------------------------------------- */
492 PROCEDURE reconcile_rbatch(
493 passin_mode VARCHAR2,
494 rbatch_id NUMBER,
495 X_statement_line_id IN OUT NOCOPY NUMBER,
496 gl_date DATE,
497 value_date DATE,
498 bank_currency VARCHAR2,
499 exchange_rate_type VARCHAR2,
500 exchange_rate NUMBER,
501 exchange_rate_date DATE,
502 trx_currency_type VARCHAR2,
503 module VARCHAR2,
504 X_trx_number IN OUT NOCOPY VARCHAR2,
505 X_trx_date DATE,
506 X_deposit_date DATE,
507 X_amount NUMBER,
508 X_foreign_diff_amt NUMBER,
509 X_set_of_books_id NUMBER,
510 X_misc_currency_code VARCHAR2,
511 X_receipt_method_id NUMBER,
512 X_bank_account_id NUMBER,
513 X_activity_type_id NUMBER,
514 X_comments VARCHAR2,
515 X_reference_type VARCHAR2,
516 X_clear_currency_code VARCHAR2,
517 X_tax_id NUMBER,
518 X_tax_rate NUMBER,
519 X_cr_vat_tax_id VARCHAR2,
520 X_dr_vat_tax_id VARCHAR2,
521 X_trx_type VARCHAR2,
522 X_statement_header_id IN OUT NOCOPY NUMBER,
523 X_statement_date DATE,
524 X_bank_trx_number VARCHAR2,
525 X_statement_amount NUMBER,
526 X_original_amount NUMBER,
527 X_effective_date DATE,
528 X_float_handling_flag VARCHAR2) IS
529 receipt_id AR_CASH_RECEIPTS_ALL.cash_receipt_id%TYPE;
530 receipt_history_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_history_id%TYPE;
531 receipt_type AR_CASH_RECEIPTS_ALL.type%TYPE;
532 receipt_status CE_LOOKUPS.lookup_code%TYPE;
533 receipt_date DATE;
534 receipt_gl_date DATE;
535 amount_to_clear NUMBER;
536 trx_amount NUMBER;
537 adjusted_xrate_amount NUMBER;
538 trx_exchange_rate_date DATE;
539 trx_exchange_rate_type AR_CASH_RECEIPTS_ALL.exchange_rate_type%TYPE;
540 misc_receipt_id AR_CASH_RECEIPTS_ALL.cash_receipt_id%TYPE;
541 auto_reconcile_flag VARCHAR2(1);
542 l_amount NUMBER;
543 l_vat_tax_id NUMBER := to_number(null);
544 l_tax_rate NUMBER := to_number(null);
545 X_org_id NUMBER ;
546 precision NUMBER default NULL;
547 ext_precision NUMBER default NULL;
548 min_acct_unit NUMBER default NULL;
549 l_gt_seq_id NUMBER := to_number(null);
550
551 BEGIN
552 IF l_DEBUG in ('Y', 'C') THEN
553 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_rbatch');
554 END IF;
555 auto_reconcile_flag := 'Y';
556 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
557 X_effective_date,
558 X_float_handling_flag);
559 IF (NVL(X_amount,0) <> 0 AND (X_receipt_method_id IS NULL OR X_activity_type_id IS NULL)) THEN
560 FND_MESSAGE.set_name('CE','CE_BATCHES_MISC_MISSING');
561 RAISE APP_EXCEPTION.application_exception;
562 END IF;
563 IF( passin_mode IN ( 'MANUAL','MANUAL_H', 'MANUAL_C')) THEN
564 --IF( X_statement_line_id IS NULL) THEN
565 IF (X_statement_line_id IS NULL AND passin_mode <> 'MANUAL_C') THEN --bug 3436722
566 OPEN C_STATEMENT_LINE_SEQ;
567 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
568 CLOSE C_STATEMENT_LINE_SEQ;
569 END IF;
570 auto_reconcile_flag := 'N';
571 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
572 CE_AUTO_BANK_MATCH.csh_statement_date := X_statement_date;
573 CE_AUTO_BANK_REC.G_gl_date := gl_date;
574 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
575 CE_AUTO_BANK_REC.G_dr_vat_tax_code := X_dr_vat_tax_id;
576 CE_AUTO_BANK_REC.G_cr_vat_tax_code := X_cr_vat_tax_id;
577 CE_AUTO_BANK_MATCH.aba_bank_currency := bank_currency;
578 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
579 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
580 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_rate_date;
581 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
582 CE_AUTO_BANK_MATCH.csl_trx_date := X_statement_date;
583 CE_AUTO_BANK_MATCH.csl_trx_type := X_trx_type;
584 CE_AUTO_BANK_MATCH.csl_amount := X_statement_amount;
585 CE_AUTO_BANK_MATCH.csl_currency_code := X_clear_currency_code;
586 CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
587 CE_AUTO_BANK_MATCH.csl_charges_amount := NULL;
588 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
589 CE_AUTO_BANK_MATCH.trx_status := NULL;
590 IF (passin_mode = 'MANUAL_H') THEN
591 CE_AUTO_BANK_CLEAR1.create_statement_line;
592 IF(X_statement_header_id IS NULL)THEN
593 X_statement_header_id := CE_AUTO_BANK_MATCH.csh_statement_header_id;
594 END IF;
595 END IF;
596 END IF;
597 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
598 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
599 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
600 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
601 ELSE
602 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
603 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
604 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
605 END IF;
606 --
607 -- The batch cannot be cleared and reconciled as one transaction so each
608 -- receipt within the batch must be processed separately.
609 --
610
611 if (passin_mode = 'AUTO') then
612 OPEN rbatch_cursor (rbatch_id);
613 LOOP
614 FETCH rbatch_cursor INTO receipt_history_id,
615 receipt_id,
616 receipt_type,
617 receipt_date,
618 receipt_status,
619 amount_to_clear,
620 trx_amount,
621 receipt_gl_date,
622 trx_exchange_rate_date,
623 trx_exchange_rate_type,
624 l_gt_seq_id;
625 EXIT WHEN rbatch_cursor%NOTFOUND OR rbatch_cursor%NOTFOUND IS NULL;
626
627 -- mark the transaction in ce_available_transactions_tmp as reconciled
628 /*
629 IF l_gt_seq_id is not null THEN
630 update ce_available_transactions_tmp
631 set reconciled_status_flag = 'Y'
632 where seq_id = l_gt_seq_id;
633 END IF;
634 */
635 --IF (to_date(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD') < to_date(receipt_gl_date,'YYYY/MM/DD')) THEN
636 IF (to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD') < to_date(to_char(receipt_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD')) THEN
637 CE_AUTO_BANK_REC.G_gl_date := receipt_gl_date;
638 END IF;
639
640 --IF (to_date(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD') <
641 --to_date(receipt_gl_date,'YYYY/MM/DD')) THEN
642 IF (to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') <
643 to_date(to_char(receipt_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD')) THEN
644 CE_AUTO_BANK_MATCH.csl_trx_date := receipt_gl_date;
645 END IF;
646
647 IF (receipt_status not in ('CLEARED', 'RISK_ELIMINATED')) THEN
648 IF (amount_to_clear = trx_amount) THEN
649 ARP_CASHBOOK.clear(
650 p_cr_id => receipt_id,
651 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
652 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
653 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
654 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
655 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
656 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate),
657 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
658 p_amount_cleared => amount_to_clear,
659 p_amount_factored => 0,
660 p_module_name => module,
661 p_module_version => '1.0',
662 p_crh_id => receipt_history_id);
663
664 -- Bug 16298972 Start
665 IF receipt_history_id IS NOT NULL THEN
666 G_RECEIPT_PROCESSED_FLAG := TRUE;
667 END IF;
668 -- Bug 16298972 End
669 ELSE -- foreign currency remittance batch
670 -- bug 3911424 used the new xrate, xrate date, xrate type and xrate amount cleared
671 IF l_DEBUG in ('Y', 'C') THEN
672 cep_standard.debug('receipt_id = '|| receipt_id);
673 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_trx_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') );
674 cep_standard.debug('(CE_AUTO_BANK_REC.G_gl_date) = '||to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
675 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_effective_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
676 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
677 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
678
679 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
680 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
681 cep_standard.debug('module = '|| module );
682 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
683
684 cep_standard.debug('trx_amount = '|| trx_amount);
685 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
686
687 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
688 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
689 END IF;
690
691 IF ((trx_currency_type = 'FOREIGN') and
692 (CE_AUTO_BANK_CLEAR.G_exchange_rate is not null)) THEN
693
694 adjusted_xrate_amount := (trx_amount * to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
695
696 FND_CURRENCY.get_info(CE_AUTO_BANK_MATCH.csl_currency_code,
697 precision,
698 ext_precision,
699 min_acct_unit);
700
701 IF l_DEBUG in ('Y', 'C') THEN
702 cep_standard.debug('precision = '|| precision);
703 END IF;
704
705 amount_to_clear := round(adjusted_xrate_amount, precision) ;
706
707 END IF;
708 IF l_DEBUG in ('Y', 'C') THEN
709
710 cep_standard.debug('set adjusted xrate_amount to amount_to_clear, trx_amount * CE_AUTO_BANK_CLEAR.G_exchange_rate ');
711 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
712 END IF;
713
714 ARP_CASHBOOK.clear(
715 p_cr_id => receipt_id,
716 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
717 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
718 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
719 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'), --trx_exchange_rate_date,
720 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type, --trx_exchange_rate_type,
721 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate), --amount_to_clear/trx_amount,
722 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
723 p_amount_cleared => amount_to_clear,
724 p_amount_factored => 0,
725 p_module_name => module,
726 p_module_version => '1.0',
727 p_crh_id => receipt_history_id);
728 -- Bug 16298972 Start
729 IF receipt_history_id IS NOT NULL THEN
730 G_RECEIPT_PROCESSED_FLAG := TRUE;
731 END IF;
732 -- Bug 16298972 End
733 END IF;
734 END IF;
735 IF l_DEBUG in ('Y', 'C') THEN
736 cep_standard.debug('after call');
737 cep_standard.debug('receipt_id = '|| receipt_id);
738 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_trx_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
739 cep_standard.debug('(CE_AUTO_BANK_REC.G_gl_date) = '||to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
740 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_effective_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
741 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
742 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
743
744 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
745 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
746 cep_standard.debug('module = '|| module );
747 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
748
749 cep_standard.debug('trx_amount = '|| trx_amount);
750 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
751 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
752 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
753 END IF;
754 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'CASH';
755 IF (passin_mode <> 'MANUAL_C' ) THEN -- bug 3436722
756 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
757 Y_cleared_trx_type =>receipt_type,
758 Y_cleared_trx_id =>receipt_history_id,
759 Y_ar_cash_receipt_id =>receipt_id,
760 Y_reference_status =>receipt_status,
761 Y_auto_reconciled_flag =>auto_reconcile_flag,
762 Y_status_flag =>'M',
763 Y_amount => amount_to_clear);
764 END IF;
765
766 -- mark the transaction in ce_available_transactions_tmp as reconciled
767 IF l_gt_seq_id is not null THEN
768 CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');
769 END IF;
770
771
772 END LOOP; -- rbatch_cursor
773 CLOSE rbatch_cursor;
774 else
775 -- Fix bug 5637589 for manual remittance batch reconciliation
776 OPEN manual_rbatch_cursor (rbatch_id);
777 LOOP
778 FETCH manual_rbatch_cursor INTO receipt_history_id,
779 receipt_id,
780 receipt_type,
781 receipt_date,
782 receipt_status,
783 amount_to_clear,
784 trx_amount,
785 receipt_gl_date,
786 trx_exchange_rate_date,
787 trx_exchange_rate_type,
788 X_org_id;
789 EXIT WHEN manual_rbatch_cursor%NOTFOUND OR manual_rbatch_cursor%NOTFOUND IS NULL;
790
791 --IF (to_date(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD') < to_date(receipt_gl_date,'YYYY/MM/DD')) THEN
792 IF (to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD') < to_date(to_char(receipt_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD')) THEN
793 CE_AUTO_BANK_REC.G_gl_date := receipt_gl_date;
794 END IF;
795
796 --IF (to_date(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD') <
797 --to_date(receipt_gl_date,'YYYY/MM/DD')) THEN
798 IF (to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') <
799 to_date(to_char(receipt_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD')) THEN
800 CE_AUTO_BANK_MATCH.csl_trx_date := receipt_gl_date;
801 END IF;
802
803 IF (receipt_status not in ('CLEARED', 'RISK_ELIMINATED')) THEN
804 IF (amount_to_clear = trx_amount) THEN
805 ARP_CASHBOOK.clear(
806 p_cr_id => receipt_id,
807 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
808 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
809 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
810 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
811 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
812 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate),
813 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
814 p_amount_cleared => amount_to_clear,
815 p_amount_factored => 0,
816 p_module_name => module,
817 p_module_version => '1.0',
818 p_crh_id => receipt_history_id);
819 -- Bug 16298972 Start
820 IF receipt_history_id IS NOT NULL THEN
821 G_RECEIPT_PROCESSED_FLAG := TRUE;
822 END IF;
823 -- Bug 16298972 End
824 ELSE -- foreign currency remittance batch
825 -- bug 3911424 used the new xrate, xrate date, xrate type and xrate amount cleared
826 IF l_DEBUG in ('Y', 'C') THEN
827 cep_standard.debug('receipt_id = '|| receipt_id);
828 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_trx_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') );
829 cep_standard.debug('(CE_AUTO_BANK_REC.G_gl_date) = '||to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
830 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_effective_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
831 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
832 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
833
834 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
835 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
836 cep_standard.debug('module = '|| module );
837 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
838
839 cep_standard.debug('trx_amount = '|| trx_amount);
840 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
841
842 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
843 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
844 END IF;
845
846 IF ((trx_currency_type = 'FOREIGN') and
847 (CE_AUTO_BANK_CLEAR.G_exchange_rate is not null)) THEN
848
849 adjusted_xrate_amount := (trx_amount * to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
850
851 FND_CURRENCY.get_info(CE_AUTO_BANK_MATCH.csl_currency_code,
852 precision,
853 ext_precision,
854 min_acct_unit);
855
856 IF l_DEBUG in ('Y', 'C') THEN
857 cep_standard.debug('precision = '|| precision);
858 END IF;
859
860 amount_to_clear := round(adjusted_xrate_amount, precision) ;
861
862 END IF;
863 IF l_DEBUG in ('Y', 'C') THEN
864
865 cep_standard.debug('set adjusted xrate_amount to amount_to_clear, trx_amount * CE_AUTO_BANK_CLEAR.G_exchange_rate ');
866 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
867 END IF;
868
869 ARP_CASHBOOK.clear(
870 p_cr_id => receipt_id,
871 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
872 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
873 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
874 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'), --trx_exchange_rate_date,
875 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type, --trx_exchange_rate_type,
876 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate), --amount_to_clear/trx_amount,
877 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
878 p_amount_cleared => amount_to_clear,
879 p_amount_factored => 0,
880 p_module_name => module,
881 p_module_version => '1.0',
882 p_crh_id => receipt_history_id);
883 -- Bug 16298972 Start
884 IF receipt_history_id IS NOT NULL THEN
885 G_RECEIPT_PROCESSED_FLAG := TRUE;
886 END IF;
887 -- Bug 16298972 End
888 END IF;
889 END IF;
890 IF l_DEBUG in ('Y', 'C') THEN
891 cep_standard.debug('after call');
892 cep_standard.debug('receipt_id = '|| receipt_id);
893 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_trx_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
894 cep_standard.debug('(CE_AUTO_BANK_REC.G_gl_date) = '||to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
895 cep_standard.debug('(CE_AUTO_BANK_MATCH.csl_effective_date) = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
896 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
897 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
898
899 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
900 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
901 cep_standard.debug('module = '|| module );
902 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
903
904 cep_standard.debug('trx_amount = '|| trx_amount);
905 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
906 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
907 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
908 END IF;
909 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'CASH';
910 IF (passin_mode <> 'MANUAL_C' ) THEN -- bug 3436722
911 -- 5637589
912 CE_AUTO_BANK_MATCH.trx_org_id := X_org_id;
913 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
914 Y_cleared_trx_type =>receipt_type,
915 Y_cleared_trx_id =>receipt_history_id,
916 Y_ar_cash_receipt_id =>receipt_id,
917 Y_reference_status =>receipt_status,
918 Y_auto_reconciled_flag =>auto_reconcile_flag,
919 Y_status_flag =>'M',
920 Y_amount => amount_to_clear);
921 END IF;
922
923 END LOOP; -- manual_rbatch_cursor
924 CLOSE manual_rbatch_cursor;
925 end if; -- End manual remittance batch reconciliation
926
927 IF (passin_mode <> 'MANUAL_C' ) THEN -- bug 3436722
928 IF (NVL(X_amount,0) <> 0) THEN
929 SELECT h.statement_number || '/' || to_char(l.line_number)
930 INTO X_trx_number
931 FROM CE_STATEMENT_HEADERS h,
932 CE_STATEMENT_LINES l
933 WHERE h.statement_header_id = l.statement_header_id AND
934 l.statement_line_id = X_statement_line_id;
935
936 IF trx_currency_type = 'FOREIGN' THEN
937 l_amount := X_amount/exchange_rate;
938 ELSE
939 l_amount := X_amount;
940 END IF;
941
942 if (passin_mode = 'AUTO' and
943 CE_AUTO_BANK_MATCH.ar_accounting_method = 'ACCRUAL') then
944 CE_AUTO_BANK_MATCH.get_vat_tax_id('AUTO', l_vat_tax_id, l_tax_rate);
945 else
946 l_vat_tax_id := X_tax_id;
947 l_tax_rate := X_tax_rate;
948 end if;
949
950 CE_AUTO_BANK_CLEAR.misc_receipt(
951 X_PASSIN_MODE => passin_mode,
952 X_TRX_NUMBER => X_trx_number,
953 X_DOC_SEQUENCE_VALUE => NULL,
954 X_DOC_SEQUENCE_ID => NULL,
955 X_GL_DATE => gl_date,
956 X_VALUE_DATE => value_date,
957 X_TRX_DATE => X_trx_date,
958 X_DEPOSIT_DATE => X_deposit_date,
959 X_AMOUNT => l_amount,
960 X_BANK_ACCOUNT_AMOUNT => X_amount,
961 X_SET_OF_BOOKS_ID => X_set_of_books_id,
962 X_MISC_CURRENCY_CODE => X_misc_currency_code,
963 X_EXCHANGE_RATE_DATE => exchange_rate_date,
964 X_EXCHANGE_RATE_TYPE => exchange_rate_type,
965 X_EXCHANGE_RATE => exchange_rate,
966 X_RECEIPT_METHOD_ID => X_receipt_method_id,
967 X_BANK_ACCOUNT_ID => X_bank_account_id,
968 X_ACTIVITY_TYPE_ID => X_activity_type_id,
969 X_COMMENTS => X_comments,
970 X_REFERENCE_TYPE => X_reference_type,
971 X_REFERENCE_ID => rbatch_id,
972 X_CLEAR_CURRENCY_CODE => bank_currency,
973 X_STATEMENT_LINE_ID => X_statement_line_id,
974 X_TAX_ID => l_vat_tax_id,
975 X_TAX_RATE => l_tax_rate,
976 X_PAID_FROM => NULL,
977 X_MODULE_NAME => module,
978 X_cr_vat_tax_id => X_cr_vat_tax_id,
979 X_dr_vat_tax_id => X_dr_vat_tax_id,
980 trx_currency_type => trx_currency_type,
981 X_CR_ID => misc_receipt_id,
982 X_effective_date => X_effective_date,
983 --X_org_id => CE_AUTO_BANK_MATCH.bau_org_id);
984 X_org_id => CE_AUTO_BANK_MATCH.trx_org_id );
985 END IF;
986 END IF;
987 IF l_DEBUG in ('Y', 'C') THEN
988 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_rbatch');
989 END IF;
990 EXCEPTION
991 WHEN OTHERS THEN
992 -- bug 2875549
993 cep_standard.debug('Exception: CE_AUTO_BANK_CLEAR1.reconcile_rbatch');
994 if (passin_mode = 'AUTO') then
995 IF rbatch_cursor%ISOPEN THEN
996 CLOSE rbatch_cursor;
997 END IF;
998 else
999 IF manual_rbatch_cursor%ISOPEN THEN
1000 CLOSE manual_rbatch_cursor;
1001 END IF;
1002 end if;
1003 RAISE;
1004 END reconcile_rbatch;
1005
1006 /* ---------------------------------------------------------------------
1007 | PRIVATE PROCEDURE |
1008 | reconcile_pay_eft |
1009 | DESCRIPTION |
1010 | Each EFT payment should be cleared and reconciled |
1011 | This procedure is used in AutoReconciliation only |
1012 | Use reconcile_trx for manual reconciliation of EFT payments |
1013 | CALLED BY |
1014 | reconcile_process |
1015 --------------------------------------------------------------------- */
1016 PROCEDURE reconcile_pay_eft( passin_mode VARCHAR2,
1017 tx_type VARCHAR2,
1018 trx_count NUMBER,
1019 trx_group VARCHAR2,
1020 cleared_trx_type VARCHAR2,
1021 cleared_date DATE,
1022 X_bank_currency VARCHAR2,
1023 X_statement_line_id NUMBER,
1024 X_statement_line_type VARCHAR2,
1025 trx_currency_type VARCHAR2,
1026 auto_reconciled_flag VARCHAR2,
1027 X_statement_header_id NUMBER,
1028 X_bank_trx_number VARCHAR2,
1029 X_bank_account_id VARCHAR2,
1030 X_payroll_payment_format VARCHAR2,
1031 X_effective_date DATE,
1032 X_float_handling_flag VARCHAR2) IS
1033
1034 amount_to_clear NUMBER;
1035 cleared_trx_id NUMBER;
1036 auto_reconcile_flag VARCHAR2(1);
1037 l_amount NUMBER;
1038 l_gt_seq_id NUMBER := to_number(null);
1039
1040 cursor pay_eft_cursor is
1041
1042 SELECT catv.trx_id,
1043 catv.bank_account_amount,
1044 catv.seq_id
1045 --FROM ce_801_EFT_transactions_v catv
1046 FROM ce_available_transactions_tmp catv
1047 WHERE upper(LTrim(catv.batch_name)) = /*12733547 added ltrim on batch number*/
1048 upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
1049 AND catv.trx_date = CE_AUTO_BANK_MATCH.csl_trx_date
1050 AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
1051 AND nvl(catv.status, 'C') <> 'V'
1052 and nvl(catv.batch_id, 0) = nvl(CE_AUTO_BANK_MATCH.trx_group,0)
1053 AND catv.application_id = 802 -- for payroll eft 802 is application id bug 7242853
1054 AND NVL(catv.reconciled_status_flag, 'N') = 'N';
1055
1056
1057
1058 BEGIN
1059 IF l_DEBUG in ('Y', 'C') THEN
1060 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_pay_eft');
1061 END IF;
1062 auto_reconcile_flag := 'Y';
1063 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
1064 --CE_AUTO_BANK_MATCH.csl_amount := amount_cleared;
1065 CE_AUTO_BANK_MATCH.csh_bank_account_id := X_bank_account_id;
1066 CE_AUTO_BANK_MATCH.trx_count := trx_count;
1067 CE_AUTO_BANK_MATCH.trx_group := trx_group;
1068 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
1069 CE_AUTO_BANK_MATCH.csl_payroll_payment_format := X_payroll_payment_format;
1070 CE_AUTO_BANK_MATCH.csl_reconcile_flag := cleared_trx_type;
1071
1072 IF l_DEBUG in ('Y', 'C') THEN
1073 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1074 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id);
1075 cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_group = '|| CE_AUTO_BANK_MATCH.trx_group);
1076 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1077 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
1078 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date);
1079
1080 END IF;
1081
1082 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
1083 X_effective_date,
1084 X_float_handling_flag);
1085 --
1086 -- The batch cannot be cleared and reconciled as one transaction so each
1087 -- EFT payment must be processed separately.
1088 --
1089 IF( passin_mode IN ( 'AUTO') AND (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'PAY_EFT')) THEN
1090 --IF (substr(CE_AUTO_BANK_MATCH.csl_payroll_payment_format,1,4) = 'BACS') THEN
1091
1092 IF l_DEBUG in ('Y', 'C') THEN
1093 cep_standard.debug('reconcile_pay_eft ' );
1094 cep_standard.debug('>>open pay_eft_cursor ');
1095 END IF;
1096
1097 OPEN pay_eft_cursor;
1098 LOOP
1099 IF l_DEBUG in ('Y', 'C') THEN
1100 cep_standard.debug('>>fetch pay_eft_cursor ');
1101 END IF;
1102
1103 FETCH pay_eft_cursor INTO cleared_trx_id,
1104 amount_to_clear,
1105 l_gt_seq_id;
1106
1107 IF l_DEBUG in ('Y', 'C') THEN
1108 cep_standard.debug('>>pay_eft_cursor cleared_trx_id = '|| cleared_trx_id);
1109 cep_standard.debug('>>pay_eft_cursor amount_to_clear = '|| amount_to_clear);
1110 END IF;
1111
1112 EXIT WHEN pay_eft_cursor%NOTFOUND OR pay_eft_cursor%NOTFOUND IS NULL;
1113
1114 -- mark the transaction in ce_available_transactions_tmp as reconciled
1115 /*
1116 IF l_gt_seq_id is not null THEN
1117 update ce_available_transactions_tmp
1118 set reconciled_status_flag = 'Y'
1119 where seq_id = l_gt_seq_id;
1120 END IF;
1121 */
1122
1123
1124 IF l_DEBUG in ('Y', 'C') THEN
1125 cep_standard.debug('reconcile_pay_eft: ' || '>>> Calling PAY_CE_RECONCILIATION_PKG.reconcile_payment'|| '-----' ||
1126 ' reconcile_pay_eft: ' || '>>> p_payment_id = '|| cleared_trx_id||
1127 ' p_cleared_date = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') ||
1128 ' p_trx_amount = '|| NVL(amount_to_clear,0)||
1129 ' p_trx_type = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
1130 END IF;
1131
1132 PAY_CE_RECONCILIATION_PKG.reconcile_payment (
1133 p_payment_id => cleared_trx_id, --CE_AUTO_BANK_MATCH.trx_id,
1134 p_cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1135 p_trx_amount => NVL(amount_to_clear,0),
1136 p_trx_type => cleared_trx_type, --CE_AUTO_BANK_MATCH.csl_reconcile_flag,
1137 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
1138 p_last_update_login => NVL(FND_GLOBAL.user_id,-1),
1139 p_created_by => NVL(FND_GLOBAL.user_id,-1) );
1140
1141 IF l_DEBUG in ('Y', 'C') THEN
1142 cep_standard.debug('reconcile_pay_eft: ' || '<<< End PAY_CE_RECONCILIATION_PKG.reconcile_payment');
1143 END IF;
1144
1145
1146 IF l_DEBUG in ('Y', 'C') THEN
1147 cep_standard.debug('reconcile_pay_eft: ' || 'cleared_trx_type='||cleared_trx_type||
1148 ',cleared_trx_id='||to_char(cleared_trx_id));
1149 cep_standard.debug(',auto_reconciled_flag='||auto_reconciled_flag||',amount_cleared='||to_char(amount_to_clear));
1150 cep_standard.debug('reconcile_pay_eft: call CE_AUTO_BANK_CLEAR1.insert_reconciliation ');
1151 END IF;
1152
1153 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1154 Y_cleared_trx_type => cleared_trx_type,
1155 Y_cleared_trx_id => cleared_trx_id,
1156 Y_ar_cash_receipt_id => null,
1157 Y_reference_status => null,
1158 Y_auto_reconciled_flag => auto_reconciled_flag,
1159 Y_status_flag => 'M',
1160 Y_amount => amount_to_clear);
1161
1162 -- mark the transaction in ce_available_transactions_tmp as reconciled
1163 IF l_gt_seq_id is not null THEN
1164 CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');
1165 END IF;
1166
1167
1168 IF l_DEBUG in ('Y', 'C') THEN
1169 cep_standard.debug('reconcile_pay_eft: ' || '<<< End CE_AUTO_BANK_CLEAR1.insert_reconciliation');
1170 END IF;
1171
1172 END LOOP; -- pay_eft_cursor
1173 CLOSE pay_eft_cursor;
1174
1175 END IF;
1176
1177 IF l_DEBUG in ('Y', 'C') THEN
1178 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_pay_eft');
1179 END IF;
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 cep_standard.debug('Exception - OTHERS: CE_AUTO_BANK_CLEAR1.reconcile_pay_eft');
1183 IF pay_eft_cursor%ISOPEN THEN
1184 CLOSE pay_eft_cursor;
1185 END IF;
1186 RAISE;
1187 END reconcile_pay_eft;
1188
1189 /* ---------------------------------------------------------------------
1190 | PRIVATE PROCEDURE |
1191 | misc_receipt |
1192 --------------------------------------------------------------------- */
1193 PROCEDURE misc_receipt( X_passin_mode VARCHAR2,
1194 X_trx_number VARCHAR2,
1195 X_doc_sequence_value VARCHAR2,
1196 X_doc_sequence_id NUMBER,
1197 X_gl_date DATE,
1198 X_value_date DATE,
1199 X_trx_date DATE,
1200 X_deposit_date DATE,
1201 X_amount NUMBER,
1202 X_bank_account_amount NUMBER,
1203 X_set_of_books_id NUMBER,
1204 X_misc_currency_code VARCHAR2,
1205 X_exchange_rate_date DATE,
1206 X_exchange_rate_type VARCHAR2,
1207 X_exchange_rate NUMBER,
1208 X_receipt_method_id NUMBER,
1209 X_bank_account_id NUMBER,
1210 X_activity_type_id NUMBER,
1211 X_comments VARCHAR2,
1212 X_reference_type VARCHAR2,
1213 X_reference_id NUMBER,
1214 X_clear_currency_code VARCHAR2,
1215 X_statement_line_id IN OUT NOCOPY NUMBER,
1216 X_tax_id NUMBER,
1217 X_tax_rate NUMBER,
1218 X_paid_from VARCHAR2,
1219 X_module_name VARCHAR2,
1220 X_cr_vat_tax_id VARCHAR2,
1221 X_dr_vat_tax_id VARCHAR2,
1222 trx_currency_type VARCHAR2,
1223 X_cr_id IN OUT NOCOPY NUMBER,
1224 X_effective_date DATE,
1225 X_org_id NUMBER ) IS
1226 cash_receipt_history_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_history_id%
1227 TYPE;
1228 l_dbseqname VARCHAR2(30);
1229 l_doc_seq_id NUMBER;
1230 l_doc_seq_value NUMBER;
1231 l_valid_seq BOOLEAN := TRUE;
1232 l_status VARCHAR2(30);
1233 l_amount NUMBER;
1234 temp BOOLEAN;
1235 current_org_id number;
1236 X_REMIT_BANK_ACCT_USE_ID number;
1237 BEGIN
1238 IF l_DEBUG in ('Y', 'C') THEN
1239 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.misc_receipt');
1240 END IF;
1241 IF (X_statement_line_id IS NULL) THEN
1242 OPEN C_STATEMENT_LINE_SEQ;
1243 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
1244 CLOSE C_STATEMENT_LINE_SEQ;
1245 END IF;
1246 IF (X_passin_mode IN ('MANUAL_REC','MANUAL_NO_REC','MANUAL', 'MANUAL_H')) THEN
1247 CE_AUTO_BANK_REC.G_gl_date := X_gl_date;
1248 CE_AUTO_BANK_MATCH.csl_trx_date := X_trx_date;
1249 CE_AUTO_BANK_MATCH.csl_effective_date := X_value_date;
1250 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := X_exchange_rate_date;
1251 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := X_exchange_rate_type;
1252 CE_AUTO_BANK_MATCH.csl_exchange_rate := X_exchange_rate;
1253 CE_AUTO_BANK_REC.G_payment_method_id := X_receipt_method_id;
1254 CE_AUTO_BANK_MATCH.csh_bank_account_id := X_bank_account_id;
1255 CE_AUTO_BANK_REC.G_receivables_trx_id := X_activity_type_id;
1256 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
1257 CE_AUTO_BANK_MATCH.aba_bank_currency := X_clear_currency_code;
1258 CE_AUTO_BANK_REC.G_set_of_books_id := X_set_of_books_id;
1259 CE_AUTO_BANK_REC.G_cr_vat_tax_code := X_cr_vat_tax_id;
1260 CE_AUTO_BANK_REC.G_dr_vat_tax_code := X_dr_vat_tax_id;
1261 temp := CE_AUTO_BANK_MATCH.validate_payment_method;
1262 --CE_AUTO_BANK_REC.G_org_id := X_org_id;
1263 CE_AUTO_BANK_CLEAR.G_org_id := X_org_id;
1264 END IF;
1265 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
1266 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
1267 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
1268 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
1269 ELSE
1270 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
1271 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
1272 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
1273 END IF;
1274 --
1275 -- Call the AOL sequence numbering routine to get Seq. number
1276 --
1277 IF (X_passin_mode IN ('MANUAL_REC', 'MANUAL_NO_REC')) THEN
1278 l_doc_seq_id := X_doc_sequence_id;
1279 l_doc_seq_value := X_doc_sequence_value;
1280 ELSE
1281 -- l_vat_tax_id := CE_AUTO_BANK_MATCH.get_vat_tax_id;
1282 -- CE_AUTO_BANK_MATCH.get_vat_tax_id(l_vat_tax_id, l_tax_rate);
1283 l_valid_seq := CE_AUTO_BANK_IMPORT.get_sequence_info(
1284 222,
1285 nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_name,
1286 CE_AUTO_BANK_REC.G_payment_method_name),
1287 CE_AUTO_BANK_REC.G_set_of_books_id,
1288 'A',
1289 CE_AUTO_BANK_MATCH.csl_trx_date,
1290 l_dbseqname,
1291 l_doc_seq_id,
1292 l_doc_seq_value );
1293 IF (NOT l_valid_seq) THEN
1294 IF (X_passin_mode IN ('AUTO','AUTO_TRX')) THEN
1295 CE_RECONCILIATION_ERRORS_PKG.insert_row(
1296 CE_AUTO_BANK_MATCH.csh_statement_header_id,
1297 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_DOC_SEQUENCE_ERR');
1298 ELSE
1299 FND_MESSAGE.set_name('CE','CE_DOC_SEQUENCE_ERR');
1300 END IF;
1301 RAISE APP_EXCEPTION.application_exception;
1302 END IF;
1303 END IF;
1304
1305 /* bug# 1097681 take care of the logic in the MISC_RECEIPTS forms
1306
1307 --
1308 -- Bug750582
1309 --
1310 IF X_misc_currency_code <> CE_AUTO_BANK_MATCH.aba_bank_currency THEN
1311 l_amount := X_amount / X_exchange_rate;
1312 ELSE
1313 l_amount := X_amount;
1314 END IF;
1315 */
1316 --set this in the form mo_global.set_policy_context('S',x_org_id);
1317
1318 select mo_global.GET_CURRENT_ORG_ID
1319 into current_org_id
1320 from dual;
1321
1322 IF l_DEBUG in ('Y', 'C') THEN
1323 cep_standard.debug('current_org_id =' ||current_org_id );
1324
1325 cep_standard.debug('X_trx_number =' ||X_trx_number );
1326 cep_standard.debug('l_doc_seq_value =' ||l_doc_seq_value );
1327 cep_standard.debug('l_doc_seq_id =' ||l_doc_seq_id );
1328 cep_standard.debug('X_gl_date =' ||X_gl_date );
1329 cep_standard.debug('X_trx_date =' ||X_trx_date );
1330 cep_standard.debug('X_deposit_date =' ||X_deposit_date );
1331 cep_standard.debug('X_amount =' ||X_amount );
1332 cep_standard.debug('X_misc_currency_code =' ||X_misc_currency_code );
1333 cep_standard.debug('X_exchange_rate_date =' ||X_exchange_rate_date );
1334 cep_standard.debug('X_exchange_rate_type =' ||X_exchange_rate_type );
1335 cep_standard.debug('X_exchange_rate =' ||X_exchange_rate );
1336 cep_standard.debug('X_receipt_method_id =' ||X_receipt_method_id );
1337 cep_standard.debug('X_bank_account_id =' ||X_bank_account_id );
1338 cep_standard.debug('X_activity_type_id =' ||X_activity_type_id );
1339 cep_standard.debug('X_comments =' ||X_comments );
1340 cep_standard.debug('X_tax_id =' ||X_tax_id );
1341 cep_standard.debug('X_reference_type =' ||X_reference_type );
1342 cep_standard.debug('X_reference_id =' ||X_reference_id );
1343 cep_standard.debug('X_paid_from =' ||X_paid_from );
1344 cep_standard.debug('X_effective_date =' ||X_effective_date );
1345 cep_standard.debug('X_module_name =' ||X_module_name );
1346 --cep_standard.debug('X_cr_id =' ||X_cr_id );
1347 cep_standard.debug('X_tax_rate =' ||X_tax_rate );
1348
1349 END IF;
1350
1351 -- bug 5185358/5221366 p_remit_bank_account_id is the
1352 -- ar_receipt_method_accounts.remit_bank_acct_uses_id
1353 -- bug 5722367 changed to check for the bank account use id
1354
1355 BEGIN
1356 SELECT REMIT_BANK_ACCT_USE_ID
1357 INTO X_REMIT_BANK_ACCT_USE_ID
1358 FROM ar_receipt_methods rm,
1359 ar_receipt_method_accounts rma,
1360 ce_bank_acct_uses cba
1361 WHERE
1362 rm.receipt_method_id = X_receipt_method_id
1363 AND rma.receipt_method_id = rm.receipt_method_id
1364 AND cba.bank_acct_use_id = rma.remit_bank_acct_use_id
1365 AND cba.ar_use_enable_flag = 'Y'
1366 AND cba.bank_account_id = X_bank_account_id;
1367
1368 EXCEPTION
1369 WHEN NO_DATA_FOUND THEN
1370 IF l_DEBUG in ('Y', 'C') THEN
1371 cep_standard.debug('X_receipt_method_id does not exists ' || 'No data found');
1372 END IF;
1373 CE_RECONCILIATION_ERRORS_PKG.insert_row(
1374 CE_AUTO_BANK_MATCH.csh_statement_header_id,
1375 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_TEMP_AR_METHOD_ORG_INVALID');
1376 END;
1377
1378 IF l_DEBUG in ('Y', 'C') THEN
1379 cep_standard.debug('X_REMIT_BANK_ACCT_USE_ID = '||X_REMIT_BANK_ACCT_USE_ID);
1380 cep_standard.debug('call ARP_CASHBOOK.ins_misc_txn ');
1381 END IF;
1382
1383 ARP_CASHBOOK.ins_misc_txn(
1384 p_receipt_number => X_trx_number,
1385 p_document_number => l_doc_seq_value,
1386 p_doc_sequence_id => l_doc_seq_id,
1387 p_gl_date => X_gl_date,
1388 p_receipt_date => X_trx_date,
1389 p_deposit_date => X_deposit_date,
1390 p_receipt_amount => X_amount,
1391 p_currency_code => X_misc_currency_code,
1392 p_exchange_date => X_exchange_rate_date,
1393 p_exchange_rate_type => X_exchange_rate_type,
1394 p_exchange_rate => X_exchange_rate,
1395 p_receipt_method_id => X_receipt_method_id,
1396 p_remit_bank_account_id => X_REMIT_BANK_ACCT_USE_ID, --X_bank_account_id,
1397 p_receivables_trx_id => X_activity_type_id,
1398 p_comments => X_comments,
1399 p_vat_tax_id => X_tax_id,
1400 p_reference_type => X_reference_type,
1401 p_reference_id => X_reference_id,
1402 p_misc_payment_source => X_paid_from,
1403 p_anticipated_clearing_date => X_effective_date,
1404 p_module_name => X_module_name,
1405 p_module_version => '1.0',
1406 p_cr_id => X_cr_id,
1407 p_tax_rate => abs(X_tax_rate));
1408
1409 IF l_DEBUG in ('Y', 'C') THEN
1410 cep_standard.debug('end call ARP_CASHBOOK.ins_misc_txn ');
1411 cep_standard.debug('X_cr_id = '|| X_cr_id);
1412
1413 END IF;
1414
1415 -- set this in the form mo_global.set_policy_context('M',null);
1416 --
1417 -- Check the status of the newly created receipt, and not to cleared
1418 -- it if it was created with Cleared status
1419 --
1420 BEGIN
1421 SELECT arh.status, arh.cash_receipt_history_id
1422 INTO l_status, cash_receipt_history_id
1423 FROM ar_cash_receipt_history_all arh --ar_cash_receipt_history arh
1424 WHERE arh.cash_receipt_id = X_cr_id AND
1425 arh.current_record_flag = 'Y';
1426 EXCEPTION
1427 WHEN NO_DATA_FOUND THEN
1428 l_status := 'REMITTED';
1429 END;
1430 if (CE_AUTO_BANK_MATCH.trx_status <> 'REVERSED') then
1431 CE_AUTO_BANK_MATCH.trx_status := l_status;
1432 CE_AUTO_BANK_MATCH.trx_id := cash_receipt_history_id;
1433 end if;
1434 --
1435 -- Clear and Match the newly created receipt
1436 --
1437 IF (X_passin_mode NOT IN ('MANUAL_NO_REC','AUTO_TRX')) THEN
1438 IF (l_status not in ('CLEARED', 'RISK_ELIMINATED'))THEN
1439 ARP_CASHBOOK.clear(X_cr_id,
1440 to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1441 to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1442 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1443 to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1444 CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1445 CE_AUTO_BANK_CLEAR.G_exchange_rate,
1446 CE_AUTO_BANK_MATCH.aba_bank_currency,
1447 X_bank_account_amount,
1448 0,
1449 X_module_name,
1450 '1.0',
1451 cash_receipt_history_id);
1452 -- Bug 16298972 Start
1453 IF cash_receipt_history_id IS NOT NULL THEN
1454 G_RECEIPT_PROCESSED_FLAG := TRUE;
1455 END IF;
1456 -- Bug 16298972 End
1457 END IF;
1458 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'MISC';
1459 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1460 Y_cleared_trx_type => 'MISC',
1461 Y_cleared_trx_id => cash_receipt_history_id,
1462 Y_ar_cash_receipt_id => X_cr_id,
1463 Y_reference_status => NULL,
1464 Y_auto_reconciled_flag => 'N',
1465 Y_status_flag => 'M',
1466 Y_amount => X_bank_account_amount);
1467 END IF;
1468 IF l_DEBUG in ('Y', 'C') THEN
1469 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.misc_receipt');
1470 END IF;
1471 END misc_receipt;
1472
1473 /* ---------------------------------------------------------------------
1474 | PRIVATE PROCEDURE |
1475 | reconcile_pbatch |
1476 | CALLED BY |
1477 | reconcile_process |
1478 --------------------------------------------------------------------- */
1479 PROCEDURE reconcile_pbatch ( passin_mode VARCHAR2,
1480 pbatch_id NUMBER,
1481 statement_line_id IN OUT NOCOPY NUMBER,
1482 gl_date DATE,
1483 value_date DATE,
1484 cleared_date DATE,
1485 amount_to_clear NUMBER,
1486 errors_amount NUMBER,
1487 charges_amount NUMBER,
1488 prorate_amount NUMBER,
1489 exchange_rate_type VARCHAR2,
1490 exchange_rate_date DATE,
1491 exchange_rate NUMBER,
1492 trx_currency_type VARCHAR2,
1493 X_statement_header_id IN OUT NOCOPY NUMBER,
1494 statement_header_date DATE,
1495 X_trx_type VARCHAR2,
1496 X_bank_trx_number VARCHAR2,
1497 X_currency_code VARCHAR2,
1498 X_original_amount NUMBER,
1499 X_effective_date DATE,
1500 X_float_handling_flag VARCHAR2,
1501 X_bank_currency_code VARCHAR2,
1502 pgroup_id VARCHAR2 DEFAULT NULL -- FOR SEPA ER 6700007
1503 ) IS
1504 check_id AP_CHECKS_ALL.check_id%TYPE;
1505 check_status CE_LOOKUPS.lookup_code%TYPE;
1506 amount_cleared AP_CHECKS_ALL.cleared_amount%TYPE;
1507 auto_reconciled_flag VARCHAR2(1);
1508 batch_trx_type varchar2(30);
1509 batch_app_id number;
1510 l_gt_seq_id number := to_number(null);
1511 x_org_id number;
1512 x_legal_entity_id number;
1513 clearing_flag VARCHAR2(1) := 'N'; -- Bug 13803033
1514 BEGIN
1515 IF l_DEBUG in ('Y', 'C') THEN
1516 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_pbatch');
1517 cep_standard.debug('passin_mode='||passin_mode );
1518 END IF;
1519 auto_reconciled_flag := 'Y';
1520 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
1521 X_effective_date,
1522 X_float_handling_flag);
1523 -- Bug 13803033 Start
1524 IF (passin_mode <> 'MANUAL_C') THEN
1525 clearing_flag := 'Y';
1526 END IF;
1527 -- Bug 13803033 End
1528 IF (passin_mode IN ( 'MANUAL', 'MANUAL_H', 'MANUAL_C')) THEN
1529 --IF (statement_line_id IS NULL) THEN
1530 IF (statement_line_id IS NULL AND passin_mode <> 'MANUAL_C') THEN --bug 3436722
1531 OPEN C_STATEMENT_LINE_SEQ;
1532 FETCH C_STATEMENT_LINE_SEQ INTO statement_line_id;
1533 CLOSE C_STATEMENT_LINE_SEQ;
1534 END IF;
1535 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
1536 CE_AUTO_BANK_MATCH.csh_statement_date := statement_header_date;
1537 CE_AUTO_BANK_REC.G_gl_date := gl_date;
1538 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
1539 CE_AUTO_BANK_MATCH.csl_statement_line_id := statement_line_id;
1540 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
1541 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_rate_date;
1542 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
1543 CE_AUTO_BANK_MATCH.csl_amount := ABS(amount_to_clear);
1544 CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
1545 CE_AUTO_BANK_MATCH.csl_charges_amount := charges_amount;
1546 CE_AUTO_BANK_MATCH.csl_currency_code := X_currency_code;
1547 CE_AUTO_BANK_MATCH.csl_trx_type := X_trx_type;
1548 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
1549 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
1550 CE_AUTO_BANK_MATCH.trx_status := NULL;
1551 auto_reconciled_flag := 'N';
1552 IF( passin_mode = 'MANUAL_H') THEN
1553 CE_AUTO_BANK_CLEAR1.create_statement_line;
1554 IF(X_statement_header_id IS NULL)THEN
1555 X_statement_header_id := CE_AUTO_BANK_MATCH.csh_statement_header_id;
1556 END IF;
1557 END IF;
1558 END IF;
1559 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
1560 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
1561 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
1562 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
1563 ELSE
1564 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
1565 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
1566 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
1567 END IF;
1568 ------------------------------------------------------------------------
1569 IF (passin_mode <> 'MANUAL_C' ) THEN --bug 3436722
1570 -- bug 5350073 ce_available_transactions_tmp is not populated when
1571 -- manually reconcile IBY batches
1572 IF (passin_mode = 'AUTO' ) THEN
1573 cep_standard.debug('open pbatch_cursor');
1574
1575 OPEN pbatch_cursor(pbatch_id, pgroup_id );-- FOR SEPA ER 6700007
1576 LOOP
1577 FETCH pbatch_cursor INTO check_id, check_status, batch_trx_type, batch_app_id, l_gt_seq_id;
1578 EXIT WHEN pbatch_cursor%NOTFOUND OR pbatch_cursor%NOTFOUND IS NULL;
1579
1580 -- mark the transaction in ce_available_transactions_tmp as reconciled
1581 /*
1582 IF l_gt_seq_id is not null THEN
1583 update ce_available_transactions_tmp
1584 set reconciled_status_flag = 'Y'
1585 where seq_id = l_gt_seq_id;
1586 END IF;
1587 */
1588 IF l_DEBUG in ('Y', 'C') THEN
1589 cep_standard.debug('batch_trx_type='||batch_trx_type ||
1590 ', check_id='||check_id||',check_status='||check_status );
1591 cep_standard.debug('batch_app_id='||batch_app_id||', l_gt_seq_id='||l_gt_seq_id);
1592 cep_standard.debug('call CE_AUTO_BANK_CLEAR1.insert_reconciliation');
1593 END IF;
1594 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1595 Y_cleared_trx_type => batch_trx_type, -- 'PAYMENT',
1596 Y_cleared_trx_id => check_id,
1597 Y_ar_cash_receipt_id => NULL,
1598 Y_reference_status => check_status,
1599 Y_auto_reconciled_flag => auto_reconciled_flag,
1600 Y_status_flag => 'M');
1601
1602 -- mark the transaction in ce_available_transactions_tmp as reconciled
1603 IF l_gt_seq_id is not null THEN
1604 CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');
1605 END IF;
1606
1607 END LOOP; -- pbatch_cursor
1608 CLOSE pbatch_cursor;
1609 ELSE -- manual reconciliation
1610 cep_standard.debug('open manual_pbatch_cursor ');
1611
1612 OPEN manual_pbatch_cursor(pbatch_id, pgroup_id );-- FOR SEPA ER 6700007
1613 LOOP
1614 FETCH manual_pbatch_cursor
1615 INTO check_id, check_status, batch_trx_type, batch_app_id,
1616 x_org_id, x_legal_entity_id;
1617 EXIT WHEN manual_pbatch_cursor%NOTFOUND OR manual_pbatch_cursor%NOTFOUND IS NULL;
1618
1619 IF l_DEBUG in ('Y', 'C') THEN
1620 cep_standard.debug('batch_trx_type='||batch_trx_type ||
1621 ', check_id='||check_id||',check_status='||check_status );
1622 cep_standard.debug('batch_app_id='||batch_app_id);
1623 cep_standard.debug('call CE_AUTO_BANK_CLEAR1.insert_reconciliation');
1624 END IF;
1625 CE_AUTO_BANK_MATCH.trx_org_id := x_org_id;
1626 CE_AUTO_BANK_MATCH.trx_legal_entity_id := x_legal_entity_id;
1627 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1628 Y_cleared_trx_type => batch_trx_type, -- 'PAYMENT',
1629 Y_cleared_trx_id => check_id,
1630 Y_ar_cash_receipt_id => NULL,
1631 Y_reference_status => check_status,
1632 Y_auto_reconciled_flag => auto_reconciled_flag,
1633 Y_status_flag => 'M');
1634
1635 END LOOP; -- manual_pbatch_cursor
1636 CLOSE manual_pbatch_cursor;
1637 END IF;
1638 END IF; --(passin_mode <> 'MANUAL_C' )
1639
1640 ------------------------------------------------------------------------
1641
1642 /*
1643 IF l_DEBUG in ('Y', 'C') THEN
1644 cep_standard.debug('reconcile_pbatch: ' || '>>>AP_RECONCILIATION_PKG.recon_payment_history'|| chr(10) ||
1645 'X_CHECKRUN_ID: '||to_char(pbatch_id)|| chr(10) ||
1646 'pgroup_id: '||to_char(pgroup_id)|| chr(10) ||
1647 'X_ACCOUNTING_DATE: '||CE_AUTO_BANK_REC.G_gl_date|| chr(10) ||
1648 'X_CLEARED_DATE: '||CE_AUTO_BANK_MATCH.csl_trx_date|| chr(10) ||
1649 'X_TRANSACTION_AMOUNT: '||to_char(amount_to_clear)|| chr(10) ||
1650 'X_ERROR_AMOUNT: '||to_char(errors_amount)|| chr(10) ||
1651 'X_CHARGE_AMOUNT: '||to_char(charges_amount)|| chr(10) ||
1652 'X_CURRENCY_CODE: '||X_currency_code|| chr(10) ||
1653 'X_BANK_CURRENCY_CODE: '||X_bank_currency_code|| chr(10) ||
1654 'X_EXCHANGE_RATE_TYPE: '||CE_AUTO_BANK_CLEAR.G_exchange_rate_type|| chr(10) ||
1655 'X_EXCHANGE_RATE_DATE: '||CE_AUTO_BANK_CLEAR.G_exchange_date|| chr(10) ||
1656 'X_EXCHANGE_RATE: '||to_char(CE_AUTO_BANK_CLEAR.G_exchange_rate)|| chr(10) ||
1657 'X_ACTUAL_VALUE_DATE: '||CE_AUTO_BANK_MATCH.csl_effective_date);
1658 END IF;
1659 */
1660 ------------------------------------------------------------------------------
1661 IF l_DEBUG in ('Y', 'C') THEN
1662 cep_standard.debug('pbatch_id='||pbatch_id||', CE_AUTO_BANK_REC.G_gl_date='||to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD')||
1663 ', CE_AUTO_BANK_MATCH.csl_trx_date='||to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
1664
1665 cep_standard.debug('amount_to_clear='||NVL(amount_to_clear,0)||', errors_amount='||NVL(errors_amount,0)||
1666 ', charges_amount='||NVL(charges_amount,0)||', X_bank_currency_code='||X_bank_currency_code);
1667
1668 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type='||CE_AUTO_BANK_CLEAR.G_exchange_rate_type||
1669 ', CE_AUTO_BANK_CLEAR.G_exchange_date='||to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD') ||
1670 ', CE_AUTO_BANK_CLEAR.G_exchange_rate='||CE_AUTO_BANK_CLEAR.G_exchange_rate);
1671
1672 cep_standard.debug('CE_AUTO_BANK_MATCH.csl_effective_date='||to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD')||
1673 ', passin_mode='||passin_mode ||', statement_line_id='||statement_line_id);
1674
1675 END IF;
1676
1677 --check if pbatch_id is a AP batch or IBY batch
1678 if (batch_app_id = 200) THEN
1679 cep_standard.debug('call AP_RECONCILIATION_PKG.recon_payment_history');
1680
1681 AP_RECONCILIATION_PKG.recon_payment_history(
1682 X_CHECKRUN_ID => pbatch_id,
1683 X_CHECK_ID => to_number(NULL),
1684 X_ACCOUNTING_DATE => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1685 X_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1686 X_TRANSACTION_AMOUNT => NVL(amount_to_clear,0),
1687 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
1688 X_ERROR_AMOUNT => NVL(errors_amount,0),
1689 X_CHARGE_AMOUNT => NVL(charges_amount,0),
1690 X_CURRENCY_CODE => X_bank_currency_code,
1691 X_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1692 X_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1693 X_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
1694 X_MATCHED_FLAG => 'Y',
1695 X_ACTUAL_VALUE_DATE =>
1696 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1697 X_LAST_UPDATE_DATE => sysdate,
1698 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
1699 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
1700 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
1701 X_CREATION_DATE => sysdate,
1702 X_PROGRAM_UPDATE_DATE => sysdate,
1703 X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
1704 X_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
1705 X_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
1706 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_pbatch '
1707 );
1708 IF l_DEBUG in ('Y', 'C') THEN
1709 cep_standard.debug('reconcile_pbatch: ' || '<<<AP_RECONCILIATION_PKG.recon_payment_history');
1710 END IF;
1711
1712 else --IBY batches
1713
1714 cep_standard.debug('call IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing');
1715 IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing(
1716 P_PAYMENT_INSTRUCTION_ID => pbatch_id,
1717 P_ACCOUNTING_DATE => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1718 P_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1719 P_TRANSACTION_AMOUNT => NVL(amount_to_clear,0), -- in bank curr.
1720 P_ERROR_AMOUNT => NVL(errors_amount,0), -- in bank curr.
1721 P_CHARGE_AMOUNT => NVL(charges_amount,0), -- in bank curr.
1722 P_CURRENCY_CODE => X_bank_currency_code, -- bank curr. code
1723 P_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type, -- between payment and functional
1724 P_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'), -- between payment and functional
1725 P_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate, -- between payment and functional
1726 P_MATCHED_FLAG => clearing_flag, -- Bug 13803033 Changed hardcoded 'Y' to clearing_flag
1727 P_ACTUAL_VALUE_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1728 P_PASSIN_MODE => passin_mode ,
1729 P_STATEMENT_LINE_ID => statement_line_id ,
1730 P_STATEMENT_LINE_TYPE => null,
1731 P_LAST_UPDATE_DATE => sysdate,
1732 P_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
1733 P_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
1734 P_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
1735 P_CREATION_DATE => sysdate,
1736 P_PROGRAM_UPDATE_DATE => sysdate,
1737 P_PROGRAM_APPLICATION_ID => NVL(FND_GLOBAL.prog_appl_id,-1),
1738 P_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
1739 P_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
1740 P_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_pbatch',
1741 P_LOGICAL_GROUP_REFERENCE => pgroup_id);-- FOR SEPA ER 6700007
1742
1743 cep_standard.debug('end call IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing');
1744
1745 end if;
1746 ------------------------------------
1747 IF (passin_mode <> 'MANUAL_C' ) THEN --bug 3436677
1748 IF l_DEBUG in ('Y', 'C') THEN
1749 cep_standard.debug('update ce_statement_reconcils_all');
1750 END IF;
1751 --update ce_statement_reconciliations r
1752 update ce_statement_reconcils_all r
1753 set amount =
1754 (select DECODE(c.currency_code,
1755 X_bank_currency_code, c.cleared_amount,
1756 nvl(c.cleared_base_amount, c.cleared_amount*CE_AUTO_BANK_CLEAR.G_exchange_rate))
1757 from ap_checks_all c
1758 where c.check_id = r.reference_id)
1759 where statement_line_id = CE_AUTO_BANK_MATCH.csl_statement_line_id
1760 and reference_type = 'PAYMENT'
1761 and status_flag = 'M'
1762 and current_record_flag = 'Y'
1763 and amount is null;
1764 END IF;
1765 IF l_DEBUG in ('Y', 'C') THEN
1766 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_pbatch');
1767 END IF;
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 cep_standard.debug('Exception: CE_AUTO_BANK_CLEAR1.reconcile_pbatch');
1771 if (passin_mode = 'AUTO') then
1772 IF pbatch_cursor%ISOPEN THEN
1773 CLOSE pbatch_cursor;
1774 END IF;
1775 else
1776 IF manual_pbatch_cursor%ISOPEN THEN
1777 CLOSE manual_pbatch_cursor;
1778 END IF;
1779 end if;
1780 RAISE;
1781 END reconcile_pbatch;
1782
1783 /* ---------------------------------------------------------------------
1784 | PRIVATE PROCEDURE |
1785 | reconcile_stmt |
1786 | CALLED BY |
1787 | reconcile_process |
1788 --------------------------------------------------------------------- */
1789 PROCEDURE reconcile_stmt(passin_mode VARCHAR2,
1790 tx_type VARCHAR2,
1791 trx_id NUMBER,
1792 trx_status VARCHAR2,
1793 receipt_type VARCHAR2,
1794 exchange_rate_type VARCHAR2,
1795 exchange_date DATE,
1796 exchange_rate NUMBER,
1797 amount_cleared NUMBER,
1798 charges_amount NUMBER,
1799 errors_amount NUMBER,
1800 gl_date DATE,
1801 value_date DATE,
1802 cleared_date DATE,
1803 ar_cash_receipt_id NUMBER,
1804 X_bank_currency VARCHAR2,
1805 X_statement_line_id IN OUT NOCOPY NUMBER,
1806 X_statement_line_type VARCHAR2,
1807 reference_status VARCHAR2,
1808 trx_currency_type VARCHAR2,
1809 auto_reconciled_flag VARCHAR2,
1810 X_statement_header_id IN OUT NOCOPY NUMBER,
1811 X_effective_date DATE,
1812 X_float_handling_flag VARCHAR2,
1813 X_currency_code VARCHAR2,
1814 X_bank_trx_number VARCHAR2,
1815 X_reversed_receipt_flag VARCHAR2) IS
1816 cleared_trx_type CE_LOOKUPS.lookup_code%TYPE;
1817 cleared_trx_id CE_STATEMENT_RECONCILS_ALL.reference_id%TYPE;
1818 clearing_flag VARCHAR2(1) := 'N';
1819 clearing_sign NUMBER;
1820 x_trx_id NUMBER;
1821 x_trx_amount NUMBER;
1822 x_trx_amount2 NUMBER;
1823 x_trx_amount3 NUMBER;
1824 BEGIN
1825 IF l_DEBUG in ('Y', 'C') THEN
1826 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_stmt, passin_mode = '||passin_mode );
1827 END IF;
1828 IF (passin_mode <> 'MANUAL_C') THEN
1829 clearing_flag := 'Y';
1830 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
1831 X_effective_date,
1832 X_float_handling_flag);
1833 END IF;
1834 IF (passin_mode IN ('MANUAL_L','MANUAL_C')) THEN
1835 IF (X_statement_line_id IS NULL AND passin_mode <> 'MANUAL_C') THEN
1836 OPEN C_STATEMENT_LINE_SEQ;
1837 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
1838 CLOSE C_STATEMENT_LINE_SEQ;
1839 END IF;
1840 CE_AUTO_BANK_MATCH.csl_trx_type := tx_type;
1841 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
1842 -- CE_AUTO_BANK_MATCH.csh_statement_date := X_statement_header_date;
1843 CE_AUTO_BANK_MATCH.aba_bank_currency := X_bank_currency;
1844 CE_AUTO_BANK_REC.G_gl_date := gl_date;
1845 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
1846 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
1847 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_date;
1848 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
1849 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
1850 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
1851 CE_AUTO_BANK_MATCH.csl_reconcile_flag := receipt_type;
1852 CE_AUTO_BANK_MATCH.csl_match_type := tx_type;
1853 CE_AUTO_BANK_MATCH.csl_amount := amount_cleared;
1854 CE_AUTO_BANK_MATCH.csl_trx_type := tx_type;
1855 CE_AUTO_BANK_MATCH.trx_id := trx_id;
1856 CE_AUTO_BANK_MATCH.trx_status := trx_status;
1857 CE_AUTO_BANK_MATCH.csl_currency_code := X_currency_code;
1858 -- CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
1859 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
1860 CE_AUTO_BANK_MATCH.reversed_receipt_flag := X_reversed_receipt_flag;
1861 IF (passin_mode = 'MANUAL_C' ) THEN
1862 CE_AUTO_BANK_MATCH.csl_trx_date := sysdate;
1863 END IF;
1864 END IF;
1865 IF l_DEBUG in ('Y', 'C') THEN
1866 cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ='|| CE_AUTO_BANK_MATCH.csl_trx_type ||
1867 ', CE_AUTO_BANK_MATCH.reversed_receipt_flag = ' ||CE_AUTO_BANK_MATCH.reversed_receipt_flag);
1868 cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_correction_type =' || CE_AUTO_BANK_MATCH.csl_match_correction_type ||
1869 ', CE_AUTO_BANK_MATCH.reconciled_this_run = '|| CE_AUTO_BANK_MATCH.reconciled_this_run);
1870 END IF;
1871
1872
1873 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
1874 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
1875 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
1876 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
1877 ELSE
1878 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
1879 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
1880 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
1881 END IF;
1882 if (passin_mode = 'AUTO') then
1883 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT' AND
1884 CE_AUTO_BANK_MATCH.reconciled_this_run is NULL) then
1885 if (CE_AUTO_BANK_MATCH.trx_match_type IN ('CASH', 'MISC')) then
1886 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
1887 --
1888 -- when reconcile the original receipt which has been reversed
1889 -- only perform the reconciliation process and skip the call to
1890 -- any AR packages
1891 --
1892 IF (CE_AUTO_BANK_MATCH.reversed_receipt_flag = 'Y'
1893 OR (trx_status IN ('RISK_ELIMINATED', 'CLEARED'))) THEN
1894 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
1895 --
1896 -- bug 922650
1897 -- update actual_value_date in AR_CASH_RECEIPS table.
1898 --
1899 IF l_DEBUG in ('Y', 'C') THEN
1900 cep_standard.debug('call ARP_CASHBOOK.update_actual_value_date');
1901 END IF;
1902
1903 ARP_CASHBOOK.update_actual_value_date(to_number(ar_cash_receipt_id),
1904 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
1905 ELSE
1906 IF l_DEBUG in ('Y', 'C') THEN
1907 cep_standard.debug('reconcile_stmt: ' || 'RECEIPT: amount_cleared = '|| to_char(amount_cleared)||
1908 'RECEIPT: charges_amount = '|| to_char(charges_amount));
1909 END IF;
1910 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT') then
1911 clearing_sign := -1;
1912 else
1913 clearing_sign := 1;
1914 end if;
1915 cleared_trx_type := 'RECEIPT';
1916 IF l_DEBUG in ('Y', 'C') THEN
1917 cep_standard.debug('call ARP_CASHBOOK.clear');
1918 END IF;
1919 ARP_CASHBOOK.clear(
1920 p_cr_id => to_number(ar_cash_receipt_id),
1921 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1922 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1923 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1924 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1925 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1926 p_exchange_rate => CE_AUTO_BANK_CLEAR.G_exchange_rate,
1927 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
1928 p_amount_cleared => amount_cleared * clearing_sign,
1929 p_amount_factored => charges_amount,
1930 p_module_name => 'CE_AUTO_BANK_REC',
1931 p_module_version => '1.0',
1932 p_crh_id => cleared_trx_id );
1933 -- Bug 16298972 Start
1934 IF cleared_trx_id IS NOT NULL THEN
1935 G_RECEIPT_PROCESSED_FLAG := TRUE;
1936 END IF;
1937 -- Bug 16298972 End
1938
1939 END IF;
1940 elsif (CE_AUTO_BANK_MATCH.trx_match_type = 'PAYMENT') then
1941 IF( trx_status NOT IN ( 'STOP INITIATED', 'VOIDED' )) THEN
1942 IF l_DEBUG in ('Y', 'C') THEN
1943 cep_standard.debug('reconcile_stmt: ' || 'PAYMENT: amount_cleared = '|| to_char(amount_cleared)||
1944 'PAYMENT: charges_amount = '|| to_char(charges_amount));
1945 END IF;
1946 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
1947 clearing_sign := -1;
1948 else
1949 clearing_sign := 1;
1950 end if;
1951 if (passin_mode = 'AUTO') then
1952 if (CE_AUTO_BANK_MATCH.trx_gl_date is not null) then
1953 CE_AUTO_BANK_REC.G_gl_date := CE_AUTO_BANK_MATCH.trx_gl_date;
1954 end if;
1955 if (CE_AUTO_BANK_MATCH.trx_cleared_date is not null) then
1956 CE_AUTO_BANK_MATCH.csl_trx_date :=
1957 CE_AUTO_BANK_MATCH.trx_cleared_date;
1958 end if;
1959 end if;
1960
1961 IF l_DEBUG in ('Y', 'C') THEN
1962 cep_standard.debug('reconcile_stmt: ' || '>>>AP_RECONCILIATION_PKG.recon_payment_history'||
1963 'X_CHECK_ID: '||to_char(CE_AUTO_BANK_MATCH.trx_id)||
1964 'X_ACCOUNTING_DATE: '||CE_AUTO_BANK_REC.G_gl_date);
1965 cep_standard.debug('X_CLEARED_DATE: '||CE_AUTO_BANK_MATCH.csl_trx_date||
1966 'X_TRANSACTION_AMOUNT: '||to_char(amount_cleared*clearing_sign)||
1967 'X_ERROR_AMOUNT: '||to_char(errors_amount)||
1968 'X_CHARGE_AMOUNT: '||to_char(charges_amount));
1969 cep_standard.debug('X_CURRENCY_CODE: '||X_currency_code||
1970 'X_EXCHANGE_RATE_TYPE: '||CE_AUTO_BANK_CLEAR.G_exchange_rate_type||
1971 'X_EXCHANGE_RATE_DATE: '||CE_AUTO_BANK_CLEAR.G_exchange_date);
1972 cep_standard.debug('X_EXCHANGE_RATE: '||to_char(CE_AUTO_BANK_CLEAR.G_exchange_rate)||
1973 'X_ACTUAL_VALUE_DATE: '||CE_AUTO_BANK_MATCH.csl_effective_date);
1974 END IF;
1975
1976 IF l_DEBUG in ('Y', 'C') THEN
1977 cep_standard.debug('call AP_RECONCILIATION_PKG.recon_payment_history');
1978 END IF;
1979
1980 AP_RECONCILIATION_PKG.recon_payment_history(
1981 X_CHECKRUN_ID => to_number(NULL),
1982 X_CHECK_ID => CE_AUTO_BANK_MATCH.trx_id,
1983 X_ACCOUNTING_DATE => CE_AUTO_BANK_REC.G_gl_date,
1984 X_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1985 X_TRANSACTION_AMOUNT => NVL(amount_cleared,0) * clearing_sign,
1986 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
1987 X_ERROR_AMOUNT => NVL(errors_amount,0),
1988 X_CHARGE_AMOUNT => NVL(charges_amount,0),
1989 X_CURRENCY_CODE => X_bank_currency,
1990 X_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1991 X_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1992 X_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
1993 X_MATCHED_FLAG => clearing_flag,
1994 X_ACTUAL_VALUE_DATE =>
1995 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1996 X_LAST_UPDATE_DATE => sysdate,
1997 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
1998 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
1999 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
2000 X_CREATION_DATE => sysdate,
2001 X_PROGRAM_UPDATE_DATE => sysdate,
2002 X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
2003 X_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
2004 X_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
2005 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_stmt '
2006 );
2007 IF l_DEBUG in ('Y', 'C') THEN
2008 cep_standard.debug('reconcile_stmt: ' || '<<<AP_RECONCILIATION_PKG.recon_payment_history');
2009 END IF;
2010 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2011 cleared_trx_type := 'PAYMENT';
2012 END IF;
2013 elsif (CE_AUTO_BANK_MATCH.trx_match_type = 'PAY_LINE') then
2014 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
2015 clearing_sign := -1;
2016 else
2017 clearing_sign := 1;
2018 end if;
2019 IF l_DEBUG in ('Y', 'C') THEN
2020 cep_standard.debug('call PAY_CE_RECONCILIATION_PKG.reconcile_payment');
2021 END IF;
2022
2023
2024 PAY_CE_RECONCILIATION_PKG.reconcile_payment (
2025 p_payment_id => CE_AUTO_BANK_MATCH.trx_id,
2026 p_cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2027 p_trx_amount => NVL(amount_cleared,0) * clearing_sign,
2028 p_trx_type => 'PAY',
2029 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
2030 p_last_update_login => NVL(FND_GLOBAL.user_id,-1),
2031 p_created_by => NVL(FND_GLOBAL.user_id,-1) );
2032 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2033 cleared_trx_type := 'PAY';
2034 end if;
2035 end if;
2036 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'REVERSAL') then
2037 x_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2038 x_trx_amount := CE_AUTO_BANK_MATCH.trx_amount;
2039 x_trx_amount2 := CE_AUTO_BANK_MATCH.csl_amount;
2040 else
2041 x_trx_id := CE_AUTO_BANK_MATCH.trx_id2;
2042 x_trx_amount := CE_AUTO_BANK_MATCH.trx_amount2;
2043 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
2044 if (CE_AUTO_BANK_MATCH.trx_type2 in
2045 ('CREDIT', 'MISC_CREDIT')) then
2046 x_trx_amount2 := - x_trx_amount;
2047 else
2048 x_trx_amount2 := x_trx_amount;
2049 end if;
2050 else /* CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT' */
2051 if (CE_AUTO_BANK_MATCH.trx_type2 in
2052 ('DEBIT', 'MISC_DEBIT')) then
2053 x_trx_amount2 := - x_trx_amount;
2054 else
2055 x_trx_amount2 := x_trx_amount;
2056 end if;
2057 end if;
2058 IF l_DEBUG in ('Y', 'C') THEN
2059 cep_standard.debug('reconcile_stmt: ' || 'DEBUG: trx_type2 = '|| CE_AUTO_BANK_MATCH.trx_type2||
2060 ' x_trx_amount = '|| x_trx_amount||' x_trx_amount2 = '|| x_trx_amount2);
2061 END IF;
2062 end if;
2063 -- Need to calculate cleared amount, x_amount_cleared, here.
2064 IF l_DEBUG in ('Y', 'C') THEN
2065 cep_standard.debug('update ce_statement_reconcils_all');
2066 END IF;
2067 --update ce_statement_reconciliations
2068 update ce_statement_reconcils_all
2069 set current_record_flag = 'N'
2070 where statement_line_id in
2071 (x_trx_id, CE_AUTO_BANK_MATCH.csl_statement_line_id)
2072 and reference_type = tx_type
2073 and nvl(current_record_flag, 'N') = 'Y'
2074 and nvl(request_id, -999) <> nvl(FND_GLOBAL.conc_request_id,-1);
2075
2076 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2077 Y_statement_line_id => x_trx_id,
2078 Y_cleared_trx_type => 'STATEMENT',
2079 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2080 Y_ar_cash_receipt_id => to_number(NULL),
2081 Y_reference_status => reference_status,
2082 Y_auto_reconciled_flag => auto_reconciled_flag,
2083 Y_status_flag => 'M',
2084 Y_amount => x_trx_amount );
2085
2086 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2087 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2088 Y_cleared_trx_type => 'STATEMENT',
2089 Y_cleared_trx_id => x_trx_id,
2090 Y_ar_cash_receipt_id => to_number(NULL),
2091 Y_reference_status => reference_status,
2092 Y_auto_reconciled_flag => auto_reconciled_flag,
2093 Y_status_flag => 'M',
2094 Y_amount => x_trx_amount2);
2095
2096 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT') then
2097
2098 -- bug 3252844 reconciled amount is duplicated because the
2099 -- the reconciled PAYMENT amount is negative on
2100 -- CE_STATEMENT_RECONCILS_ALL
2101
2102 x_trx_amount3 := CE_AUTO_BANK_MATCH.corr_csl_amount;
2103
2104 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
2105 if (CE_AUTO_BANK_MATCH.trx_match_type = 'PAYMENT') then
2106 x_trx_amount3 :=
2107 -1 * CE_AUTO_BANK_MATCH.corr_csl_amount;
2108 end if;
2109 /*elsif (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT') then
2110 if (CE_AUTO_BANK_MATCH.trx_match_type = 'CASH') then
2111 x_trx_amount3 :=
2112 -1 * CE_AUTO_BANK_MATCH.corr_csl_amount;
2113 end if;*/
2114 end if;
2115
2116 IF l_DEBUG in ('Y', 'C') THEN
2117 cep_standard.debug('reconcile_stmt: DEBUG: x_trx_amount3 = '|| x_trx_amount3
2118 ||', CE_AUTO_BANK_MATCH.csl_trx_type: ' ||CE_AUTO_BANK_MATCH.csl_trx_type
2119 ||', CE_AUTO_BANK_MATCH.trx_match_type: ' ||CE_AUTO_BANK_MATCH.trx_match_type);
2120 END IF;
2121
2122 if (CE_AUTO_BANK_MATCH.reconciled_this_run is NULL) then
2123 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2124 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2125 Y_cleared_trx_type => cleared_trx_type,
2126 Y_cleared_trx_id => cleared_trx_id,
2127 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2128 Y_reference_status => reference_status,
2129 Y_auto_reconciled_flag => auto_reconciled_flag,
2130 Y_status_flag => 'M',
2131 Y_amount => x_trx_amount3);
2132 --Y_amount => CE_AUTO_BANK_MATCH.corr_csl_amount);
2133 else /* CE_AUTO_BANK_MATCH.reconciled_this_run = 'Y' */
2134 IF l_DEBUG in ('Y', 'C') THEN
2135 cep_standard.debug('update ce_statement_reconcils_all');
2136 END IF;
2137 --update ce_statement_reconciliations
2138 update ce_statement_reconcils_all
2139 set statement_line_Id = CE_AUTO_BANK_MATCH.csl_statement_line_id,
2140 amount = x_trx_amount3
2141 --amount = CE_AUTO_BANK_MATCH.corr_csl_amount
2142 where statement_line_id = x_trx_id
2143 and reference_type <> 'STATEMENT'
2144 and reference_id = CE_AUTO_BANK_MATCH.trx_id
2145 and nvl(request_id,-999) = nvl(FND_GLOBAL.conc_request_id,-1);
2146 end if;
2147 end if;
2148 elsif (passin_mode <> 'MANUAL_C') then
2149 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := 'Y';
2150
2151 -- bug# 1189554
2152 x_trx_amount2 := CE_AUTO_BANK_MATCH.csl_amount;
2153
2154 if (X_statement_line_type = 'MISC_CREDIT') then
2155 if (tx_type in ('CREDIT', 'MISC_CREDIT')) then
2156 x_trx_amount2 :=
2157 -1 * CE_AUTO_BANK_MATCH.csl_amount;
2158 end if;
2159 else /* X_statement_line_type = 'MISC_DEBIT' */
2160 if (tx_type in ('DEBIT', 'MISC_DEBIT')) then
2161 x_trx_amount2 :=
2162 -1 * CE_AUTO_BANK_MATCH.csl_amount;
2163 end if;
2164 end if;
2165
2166 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2167 Y_statement_line_id => CE_AUTO_BANK_MATCH.trx_id,
2168 Y_cleared_trx_type => 'STATEMENT',
2169 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2170 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2171 Y_reference_status => reference_status,
2172 Y_auto_reconciled_flag => auto_reconciled_flag,
2173 Y_status_flag => 'M',
2174 Y_amount => CE_AUTO_BANK_MATCH.csl_amount);
2175
2176 CE_AUTO_BANK_CLEAR.update_line_status(
2177 CE_AUTO_BANK_MATCH.trx_id,'RECONCILED');
2178
2179 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2180 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2181 Y_cleared_trx_type => 'STATEMENT',
2182 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.trx_id,
2183 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2184 Y_reference_status => reference_status,
2185 Y_auto_reconciled_flag => auto_reconciled_flag,
2186 Y_status_flag => 'M',
2187 Y_amount => x_trx_amount2);
2188
2189 CE_AUTO_BANK_CLEAR.update_line_status(
2190 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'RECONCILED');
2191 end if;
2192 IF l_DEBUG in ('Y', 'C') THEN
2193 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_stmt');
2194 END IF;
2195 END reconcile_stmt;
2196
2197 /* ---------------------------------------------------------------------
2198 | PRIVATE PROCEDURE |
2199 | reconcile_trx |
2200 | CALLED BY |
2201 | reconcile_process |
2202 --------------------------------------------------------------------- */
2203 PROCEDURE reconcile_trx(
2204 passin_mode VARCHAR2,
2205 tx_type VARCHAR2,
2206 trx_id NUMBER,
2207 trx_status VARCHAR2,
2208 receipt_type VARCHAR2,
2209 exchange_rate_type VARCHAR2,
2210 exchange_date DATE,
2211 exchange_rate NUMBER,
2212 amount_cleared NUMBER,
2213 charges_amount NUMBER,
2214 errors_amount NUMBER,
2215 gl_date DATE,
2216 value_date DATE,
2217 cleared_date DATE,
2218 ar_cash_receipt_id NUMBER,
2219 X_bank_currency VARCHAR2,
2220 X_statement_line_id IN OUT NOCOPY NUMBER,
2221 X_statement_line_type VARCHAR2,
2222 reference_status VARCHAR2,
2223 trx_currency_type VARCHAR2,
2224 auto_reconciled_flag VARCHAR2,
2225 X_statement_header_id IN OUT NOCOPY NUMBER,
2226 X_statement_header_date DATE,
2227 X_bank_trx_number VARCHAR2,
2228 X_currency_code VARCHAR2,
2229 X_original_amount NUMBER,
2230 X_effective_date DATE,
2231 X_float_handling_flag VARCHAR2,
2232 X_reversed_receipt_flag VARCHAR2,
2233 X_org_id NUMBER DEFAULT NULL,
2234 X_legal_entity_id NUMBER DEFAULT NULL
2235 ) IS
2236 cleared_trx_type CE_LOOKUPS.lookup_code%TYPE;
2237 cleared_trx_id CE_STATEMENT_RECONCILS_ALL.reference_id%TYPE;
2238 clearing_flag VARCHAR2(1) := 'N';
2239 X_RESULT VARCHAR2(100);
2240 X_RECONCILED_METHOD VARCHAR2(1);
2241 current_org_id NUMBER;
2242 BEGIN
2243 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_trx ');
2244 cep_standard.debug('CE_AUTO_BANK_CLEAR1.csl_reconcile_flag = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
2245
2246 -- Bug 16298972 Start
2247 -- Reset the G_RECEIPT_PROCESSED_FLAG flag for manual recon.
2248 IF G_RECEIPT_PROCESSED_FLAG AND passin_mode <> 'AUTO' THEN
2249 G_RECEIPT_PROCESSED_FLAG := FALSE;
2250 END IF;
2251 -- Bug 16298972 End
2252
2253 IF (X_org_id is not null or X_legal_entity_id is not null)
2254 THEN
2255 CE_AUTO_BANK_CLEAR.G_org_id := X_org_id;
2256 CE_AUTO_BANK_CLEAR.G_legal_entity_id := X_legal_entity_id;
2257 END IF;
2258
2259 IF (passin_mode <> 'MANUAL_C')
2260 THEN
2261 clearing_flag := 'Y';
2262 CE_AUTO_BANK_CLEAR1.validate_effective_date(
2263 passin_mode,
2264 X_effective_date,
2265 X_float_handling_flag);
2266 END IF;
2267
2268 IF (passin_mode IN ('MANUAL_H','MANUAL_L','MANUAL_C'))
2269 THEN
2270 IF (X_statement_line_id IS NULL AND passin_mode <> 'MANUAL_C')
2271 THEN
2272 OPEN C_STATEMENT_LINE_SEQ;
2273 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
2274 CLOSE C_STATEMENT_LINE_SEQ;
2275 END IF;
2276
2277 CE_AUTO_BANK_MATCH.csl_trx_type := tx_type;
2278 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
2279 CE_AUTO_BANK_MATCH.csh_statement_date := X_statement_header_date;
2280 CE_AUTO_BANK_MATCH.aba_bank_currency := X_bank_currency;
2281 CE_AUTO_BANK_REC.G_gl_date := gl_date;
2282 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
2283 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_date;
2284 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
2285 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
2286 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
2287 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
2288 CE_AUTO_BANK_MATCH.csl_reconcile_flag := receipt_type;
2289 CE_AUTO_BANK_MATCH.csl_match_type := tx_type;
2290 CE_AUTO_BANK_MATCH.csl_amount := amount_cleared;
2291 CE_AUTO_BANK_MATCH.trx_id := trx_id;
2292 CE_AUTO_BANK_MATCH.trx_status := trx_status;
2293 CE_AUTO_BANK_MATCH.csl_currency_code := X_currency_code;
2294 CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
2295 CE_AUTO_BANK_MATCH.csl_charges_amount := charges_amount;
2296 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
2297 CE_AUTO_BANK_MATCH.reversed_receipt_flag := X_reversed_receipt_flag;
2298
2299 IF (passin_mode = 'MANUAL_H' )
2300 THEN
2301 CE_AUTO_BANK_CLEAR1.create_statement_line;
2302 IF(X_statement_header_id IS NULL)
2303 THEN
2304 X_statement_header_id := CE_AUTO_BANK_MATCH.csh_statement_header_id;
2305 END IF;
2306 END IF;
2307
2308 IF (passin_mode = 'MANUAL_C' )
2309 THEN
2310 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
2311 END IF;
2312 END IF;
2313
2314 IF (trx_currency_type IN ('FOREIGN','BANK'))
2315 THEN
2316 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
2317 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
2318 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
2319 ELSE
2320 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
2321 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
2322 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
2323 END IF;
2324
2325 IF(CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI' and CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'XTR_LINE')
2326 THEN
2327 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'XTR_LINE';
2328 END IF;
2329 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW')
2330 THEN
2331 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'CASHFLOW';
2332 END IF;
2333
2334 IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI')
2335 THEN
2336 CE_999_PKG.clear(
2337 X_trx_id => CE_AUTO_BANK_MATCH.trx_id,
2338 X_trx_type => CE_AUTO_BANK_MATCH.csl_match_type,
2339 X_status => nvl(CE_AUTO_BANK_REC.G_open_interface_clear_status,CE_AUTO_BANK_MATCH.trx_status),
2340 X_trx_number => CE_AUTO_BANK_MATCH.csl_bank_trx_number,
2341 X_trx_date => CE_AUTO_BANK_MATCH.csl_trx_date,
2342 X_trx_currency => CE_AUTO_BANK_MATCH.csl_currency_code,
2343 X_gl_date => CE_AUTO_BANK_REC.G_gl_date,
2344 X_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
2345 X_cleared_amount => NVL(amount_cleared,0),
2346 X_cleared_date => CE_AUTO_BANK_MATCH.csl_trx_date,
2347 X_charges_amount => charges_amount,
2348 X_errors_amount => errors_amount,
2349 X_exchange_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
2350 X_exchange_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
2351 X_exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate);
2352 cleared_trx_type := 'ROI_LINE';
2353 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2354
2355 ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'XTR_LINE')
2356 THEN
2357 X_RECONCILED_METHOD := substr(passin_mode,1,1);
2358 XTR_WRAPPER_API_P.reconciliation(
2359 P_SETTLEMENT_SUMMARY_ID => CE_AUTO_BANK_MATCH.trx_id,
2360 P_TASK => 'REC',
2361 P_RECONCILED_METHOD => X_RECONCILED_METHOD,
2362 P_RESULT => X_RESULT,
2363 P_RECON_AMT => amount_cleared, -- 8978548 (1/4): Added
2364 P_VAL_DATE => value_date); -- 8978548 (2/4): Added
2365
2366 if (X_RESULT <> 'XTR2_SUCCESS')
2367 then
2368 FND_MESSAGE.set_name( 'CE','CE_XTR_UPDATE_FAILED');
2369 RAISE APP_EXCEPTION.application_exception;
2370 end if;
2371 cleared_trx_type := 'XTR_LINE';
2372 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2373
2374 ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CASHFLOW')
2375 THEN
2376 cep_standard.debug('reconcile_trx: calling CE_CASHFLOW_PKG.CLEAR_CASHFLOW');
2377 CE_CASHFLOW_PKG.CLEAR_CASHFLOW(
2378 X_CASHFLOW_ID => CE_AUTO_BANK_MATCH.trx_id,
2379 X_TRX_STATUS => CE_AUTO_BANK_MATCH.trx_status,
2380 x_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2381 /* 14840476: Use GL_DATE passed to reconcile_trx */
2382 X_ACCOUNTING_DATE => nvl(gl_date,CE_AUTO_BANK_REC.G_gl_date),
2383 X_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2384 X_CLEARED_AMOUNT => NVL(amount_cleared,0),
2385 X_CLEARED_ERROR_AMOUNT => NVL(errors_amount,0),
2386 X_CLEARED_CHARGE_AMOUNT => NVL(charges_amount,0),
2387 X_CLEARED_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2388 X_CLEARED_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2389 X_CLEARED_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2390 X_PASSIN_MODE => passin_mode,
2391 x_statement_line_id => x_statement_line_id,
2392 x_statement_line_type => x_statement_line_type) ;
2393
2394 cleared_trx_type := 'CASHFLOW';
2395 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2396
2397 ELSIF( CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE' )
2398 THEN
2399 cleared_trx_type := 'JE_LINE';
2400 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2401
2402 ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag IN ('PAY', 'PAY_EFT'))
2403 THEN
2404
2405 IF ((NVL(X_statement_line_type,'XXX') = 'STOP' AND trx_status <> 'V') OR
2406 (NVL(X_statement_line_type,'STOP') <> 'STOP' AND trx_status = 'V'))
2407 THEN
2408 FND_MESSAGE.set_name( 'CE', 'CE_STOP_VOID');
2409 RAISE APP_EXCEPTION.application_exception;
2410 ELSE
2411 cep_standard.debug('...Calling PAY_CE_RECONCILIATION_PKG.reconcile_payment'||
2412 'p_payment_id = '|| to_char(CE_AUTO_BANK_MATCH.trx_id)||
2413 ' p_cleared_date = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
2414 cep_standard.debug(' p_trx_amount = '|| to_char(amount_cleared)||
2415 ' p_trx_type = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
2416
2417 PAY_CE_RECONCILIATION_PKG.reconcile_payment (
2418 p_payment_id => CE_AUTO_BANK_MATCH.trx_id,
2419 p_cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2420 p_trx_amount => NVL(amount_cleared,0),
2421 p_trx_type => CE_AUTO_BANK_MATCH.csl_reconcile_flag,
2422 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
2423 p_last_update_login => NVL(FND_GLOBAL.user_id,-1),
2424 p_created_by => NVL(FND_GLOBAL.user_id,-1) );
2425
2426 cep_standard.debug('reconcile_trx: ' || '<<< End PAY_CE_RECONCILIATION_PKG.reconcile_payment');
2427 END IF;
2428 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2429 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_reconcile_flag;
2430 ELSE
2431 IF (CE_AUTO_BANK_MATCH.csl_match_type = 'PAYMENT')
2432 THEN
2433 --
2434 -- NVL(X_statement_line_type) is for cases where we create the statement line
2435 -- here (MANUAL_H) and we know that the created statement line
2436 -- is always fine
2437 --
2438 IF ((NVL(X_statement_line_type,'XXX') = 'STOP' AND
2439 trx_status NOT IN ('STOP INITIATED', 'VOIDED'))
2440 OR
2441 (NVL(X_statement_line_type,'STOP') <> 'STOP' AND
2442 trx_status IN ( 'STOP INITIATED', 'VOIDED')))
2443 THEN
2444 if (trx_status = 'STOP INITIATED')
2445 then
2446 FND_MESSAGE.set_name( 'CE', 'CE_ABR_PYMT_STOPPED' );
2447 RAISE APP_EXCEPTION.application_exception;
2448 elsif (trx_status = 'VOIDED')
2449 then
2450 FND_MESSAGE.set_name( 'CE', 'CE_ABR_PYMT_VOIDED' );
2451 RAISE APP_EXCEPTION.application_exception;
2452 else
2453 FND_MESSAGE.set_name( 'CE', 'CE_PAYMENT_STOPPED' );
2454 RAISE APP_EXCEPTION.application_exception;
2455 end if;
2456 ELSIF( trx_status NOT IN ( 'STOP INITIATED', 'VOIDED' ))
2457 THEN
2458 if (passin_mode = 'AUTO')
2459 then
2460 if (CE_AUTO_BANK_MATCH.trx_cleared_date is not null)
2461 then
2462 CE_AUTO_BANK_MATCH.csl_trx_date := CE_AUTO_BANK_MATCH.trx_cleared_date;
2463 end if;
2464 end if;
2465
2466 cep_standard.debug('X_CHECK_ID: '||to_char(CE_AUTO_BANK_MATCH.trx_id)||
2467 ' X_ACCOUNTING_DATE: '||CE_AUTO_BANK_REC.G_gl_date);
2468 cep_standard.debug('X_CLEARED_DATE: '||CE_AUTO_BANK_MATCH.csl_trx_date||
2469 ' X_TRANSACTION_AMOUNT: '||to_char(amount_cleared));
2470 cep_standard.debug('X_ERROR_AMOUNT: '||to_char(errors_amount)||
2471 ' X_CHARGE_AMOUNT: '||to_char(charges_amount));
2472 cep_standard.debug('X_CURRENCY_CODE: '||X_currency_code||
2473 ' X_EXCHANGE_RATE_TYPE: '||CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
2474 cep_standard.debug('X_EXCHANGE_RATE_DATE: '||CE_AUTO_BANK_CLEAR.G_exchange_date||
2475 ' X_EXCHANGE_RATE: '||to_char(CE_AUTO_BANK_CLEAR.G_exchange_rate));
2476 cep_standard.debug('X_ACTUAL_VALUE_DATE: '||CE_AUTO_BANK_MATCH.csl_effective_date);
2477 cep_standard.debug('reconcile_trx >>>AP_RECONCILIATION_PKG.recon_payment_history');
2478
2479 AP_RECONCILIATION_PKG.recon_payment_history(
2480 X_CHECKRUN_ID => to_number(NULL),
2481 X_CHECK_ID => to_number(CE_AUTO_BANK_MATCH.trx_id),
2482 X_ACCOUNTING_DATE => CE_AUTO_BANK_REC.G_gl_date,
2483 X_CLEARED_DATE => CE_AUTO_BANK_MATCH.csl_trx_date,
2484 X_TRANSACTION_AMOUNT => NVL(amount_cleared,0),
2485 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
2486 X_ERROR_AMOUNT => NVL(errors_amount,0),
2487 X_CHARGE_AMOUNT => NVL(charges_amount,0),
2488 X_CURRENCY_CODE => X_bank_currency,
2489 X_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2490 X_EXCHANGE_RATE_DATE => CE_AUTO_BANK_CLEAR.G_exchange_date,
2491 X_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2492 X_MATCHED_FLAG => clearing_flag,
2493 X_ACTUAL_VALUE_DATE => CE_AUTO_BANK_MATCH.csl_effective_date,
2494 X_LAST_UPDATE_DATE => sysdate,
2495 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
2496 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
2497 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
2498 X_CREATION_DATE => sysdate,
2499 X_PROGRAM_UPDATE_DATE => sysdate,
2500 X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
2501 X_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
2502 X_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
2503 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_trx ');
2504
2505 cep_standard.debug('reconcile_trx: ' || '<<<AP_RECONCILIATION_PKG.recon_payment_history');
2506 END IF;
2507 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2508 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2509 ELSE -- Receipt
2510 IF ((NVL(X_statement_line_type,'NSF') NOT IN ('NSF','REJECTED') AND
2511 trx_status in ('REVERSED','DM REVERSED')))
2512 THEN
2513 FND_MESSAGE.set_name('CE', 'CE_STATEMENT_REVERSAL_NSF');
2514 RAISE APP_EXCEPTION.application_exception;
2515 ELSE
2516 IF (passin_mode = 'AUTO' AND
2517 CE_AUTO_BANK_MATCH.csl_trx_type IN ('NSF','REJECTED') AND
2518 CE_AUTO_BANK_REC.G_nsf_handling = 'REVERSE' AND
2519 CE_AUTO_BANK_MATCH.trx_status <> 'REVERSED')
2520 THEN
2521 ARP_CASHBOOK.reverse(
2522 p_cr_id => ar_cash_receipt_id,
2523 p_reversal_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2524 p_reversal_date => sysdate,
2525 p_reversal_comments => NULL,
2526 p_reversal_reason_code => NULL,
2527 p_reversal_category => NULL,
2528 p_module_name => 'CE_AUTO_BANK_REC',
2529 p_module_version => '1.0',
2530 p_crh_id => cleared_trx_id);
2531 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2532
2533 ELSIF (passin_mode = 'AUTO' AND
2534 CE_AUTO_BANK_MATCH.csl_trx_type IN ('NSF','REJECTED') AND
2535 CE_AUTO_BANK_REC.G_nsf_handling = 'DM REVERSE' AND
2536 CE_AUTO_BANK_MATCH.trx_status <> 'REVERSED')
2537 THEN
2538 IF (CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' AND
2539 CE_AUTO_BANK_MATCH.trx_customer_id is not null)
2540 THEN
2541 declare
2542 cc_id ra_cust_trx_types.gl_id_rec%type;
2543 cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%type;
2544 cust_trx_type ra_cust_trx_types.name%type;
2545 l_dbseqname varchar2(30);
2546 l_doc_seq_id NUMBER;
2547 l_doc_seq_value NUMBER;
2548 l_valid_seq BOOLEAN := TRUE;
2549 out_trx_number ar_payment_schedules_all.trx_number%TYPE;
2550 out_status varchar2(10);
2551
2552 begin
2553 select name, gl_id_rec, cust_trx_type_id
2554 into cust_trx_type, cc_id, cust_trx_type_id
2555 from ra_cust_trx_types
2556 where type = 'DM'
2557 and post_to_gl = 'Y'
2558 and accounting_affect_flag = 'Y'
2559 and tax_calculation_flag = 'N'
2560 and rownum = 1
2561 order by name, cust_trx_type_id;
2562
2563 l_valid_seq := CE_AUTO_BANK_IMPORT.get_sequence_info(
2564 222,
2565 cust_trx_type,
2566 CE_AUTO_BANK_REC.G_set_of_books_id,
2567 'A',
2568 CE_AUTO_BANK_MATCH.csl_trx_date,
2569 l_dbseqname,
2570 l_doc_seq_id,
2571 l_doc_seq_value );
2572
2573 if (l_valid_seq) then
2574 ARP_CASHBOOK.debit_memo_reversal(
2575 p_cash_receipt_id => ar_cash_receipt_id,
2576 p_cc_id => cc_id,
2577 p_dm_cust_trx_type_id => cust_trx_type_id,
2578 p_dm_cust_trx_type => cust_trx_type,
2579 p_reversal_gl_date =>
2580 to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2581 p_reversal_date =>
2582 to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2583 p_reversal_category => 'NSF',
2584 p_reversal_reason_code => 'NSF',
2585 p_reversal_comments => 'test',
2586 p_dm_number => out_trx_number,
2587 p_dm_doc_sequence_value => l_doc_seq_value,
2588 p_dm_doc_sequence_id => l_doc_seq_id,
2589 p_tw_status => out_status,
2590 p_module_name => 'CE_AUTO_BANK_REC',
2591 p_module_version => '1.0');
2592
2593 if (out_status = 'OK') then
2594 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2595 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2596 end if;
2597 end if;
2598 exception
2599 when no_data_found then
2600 cep_standard.debug('reconcile_trx: calls debit_memo_reversal NO DATA FOUND');
2601 raise;
2602 when others then
2603 cep_standard.debug('reconcile_trx: calls debit_memo_reversal EXCEPTION');
2604 raise;
2605 end;
2606 END IF;
2607
2608 ELSE -- not reversed nor dm_reverse
2609 IF (reference_status <> 'REVERSED' AND
2610 NOT CE_AUTO_BANK_CLEAR1.check_matching_status(ar_cash_receipt_id,reference_status))
2611 THEN
2612 FND_MESSAGE.set_name('CE','CE_MATCHED_AR_ENTRY');
2613 RAISE APP_EXCEPTION.application_exception;
2614 ELSE
2615 cep_standard.debug('reconcile_trx: ' || ' amount_cleared = '|| amount_cleared||
2616 ' charges_amount = '|| charges_amount|| ' trx_status = '|| trx_status);
2617
2618 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2619 --
2620 -- when reconcile the original receipt which has been reversed
2621 -- only perform the reconciliation process and skip the call to
2622 -- any AR packages
2623 --
2624 IF (CE_AUTO_BANK_MATCH.reversed_receipt_flag = 'Y'
2625 OR (trx_status IN ('RISK_ELIMINATED', 'CLEARED'))
2626 OR (CE_AUTO_BANK_MATCH.csl_trx_type in ('NSF', 'REJECTED')) )
2627 THEN
2628 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2629 --
2630 -- bug 922650
2631 -- update actual_value_date in AR_CASH_RECEIPS table.
2632 --
2633 cep_standard.debug('reconcile_trx: call ARP_CASHBOOK.update_actual_value_date');
2634 ARP_CASHBOOK.update_actual_value_date(to_number(ar_cash_receipt_id),
2635 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
2636 ELSE
2637 cep_standard.debug('reconcile_trx: ' || ' ar_cash_receipt_id= '|| ar_cash_receipt_id ||
2638 ' CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date ||
2639 ' CE_AUTO_BANK_REC.G_gl_date= '|| CE_AUTO_BANK_REC.G_gl_date);
2640 cep_standard.debug(' CE_AUTO_BANK_MATCH.csl_effective_date= '|| CE_AUTO_BANK_MATCH.csl_effective_date ||
2641 ' CE_AUTO_BANK_CLEAR.G_exchange_date= '|| CE_AUTO_BANK_CLEAR.G_exchange_date ||
2642 ' CE_AUTO_BANK_CLEAR.G_exchange_rate_type= '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
2643 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate= '|| CE_AUTO_BANK_CLEAR.G_exchange_rate ||
2644 'CE_AUTO_BANK_MATCH.aba_bank_currency= '|| CE_AUTO_BANK_MATCH.aba_bank_currency ||
2645 ' amount_cleared = '|| amount_cleared);
2646 cep_standard.debug('charges_amount = '|| charges_amount ||
2647 ' cleared_trx_id = '|| cleared_trx_id);
2648
2649 cep_standard.debug('reconcile_trx: call ARP_CASHBOOK.clear ');
2650
2651 ARP_CASHBOOK.clear(
2652 p_cr_id => to_number(ar_cash_receipt_id),
2653 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2654 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2655 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2656 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2657 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2658 p_exchange_rate => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2659 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
2660 p_amount_cleared => amount_cleared,
2661 p_amount_factored => charges_amount,
2662 p_module_name => 'CE_AUTO_BANK_REC',
2663 p_module_version => '1.0',
2664 p_crh_id => cleared_trx_id );
2665
2666 -- Bug 16298972 Start
2667 IF cleared_trx_id IS NOT NULL THEN
2668 G_RECEIPT_PROCESSED_FLAG := TRUE;
2669 END IF;
2670 -- Bug 16298972 E
2671 cep_standard.debug('end call ARP_CASHBOOK.clear ');
2672 END IF;
2673 END IF;
2674 END IF;
2675 END IF;
2676 END IF;
2677 END IF;
2678
2679 IF (passin_mode <> 'MANUAL_C' )
2680 THEN
2681 cep_standard.debug('reconcile_trx: ' ||
2682 'cleared_trx_type='||cleared_trx_type||
2683 ',cleared_trx_id='||to_char(cleared_trx_id)||
2684 ',ar_cash_receipt_id='||to_char(ar_cash_receipt_id));
2685 cep_standard.debug('reconcile_trx: ' ||
2686 ',reference_status='||reference_status||
2687 ',auto_reconciled_flag='||auto_reconciled_flag||
2688 ',amount_cleared='|| to_char(amount_cleared));
2689
2690 cep_standard.debug('reconcile_trx: calling CE_AUTO_BANK_CLEAR1.insert_reconciliation');
2691
2692 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2693 Y_cleared_trx_type => cleared_trx_type,
2694 Y_cleared_trx_id => cleared_trx_id,
2695 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2696 Y_reference_status => reference_status,
2697 Y_auto_reconciled_flag => auto_reconciled_flag,
2698 Y_status_flag => 'M',
2699 Y_amount => amount_cleared);
2700 END IF;
2701
2702 /* Bug 16491164 Commented the following code
2703 -- Bug 16298972 Start
2704 cep_standard.debug('FND_GLOBAL.conc_request_id = ' || FND_GLOBAL.conc_request_id);
2705 cep_standard.debug('passin_mode = ' || passin_mode);
2706 IF G_RECEIPT_PROCESSED_FLAG AND passin_mode <> 'AUTO' THEN
2707 cep_standard.debug('Calling AR API ARP_CASHBOOK.refresh_risk_value for manual clearing.');
2708 ARP_CASHBOOK.refresh_risk_value;
2709 cep_standard.debug('Call to AR API ARP_CASHBOOK.refresh_risk_value ended successfully.');
2710 END IF;
2711 -- Bug 16298972 End
2712 */
2713 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_trx');
2714
2715 -- 14840476 : Added exception block
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 cep_standard.debug('reconcile_trx: EXCEPTION ::'||sqlerrm);
2719 RAISE;
2720 END reconcile_trx;
2721
2722 /* ---------------------------------------------------------------------
2723 | PRIVATE PROCEDURE |
2724 | unclear_process |
2725 | DESCRIPTION |
2726 | Unclear and unreconcile the reconcilied statement line. |
2727 | CALLED BY |
2728 | This piece of code is called only from Manual Reconciliation |
2729 --------------------------------------------------------------------- */
2730 PROCEDURE unclear_process ( passin_mode VARCHAR2,
2731 X_header_or_line VARCHAR2,
2732 tx_type VARCHAR2,
2733 clearing_trx_type VARCHAR2,
2734 batch_id NUMBER,
2735 trx_id NUMBER,
2736 cash_receipt_id NUMBER,
2737 trx_date DATE,
2738 gl_date DATE,
2739 cash_receipt_history_id IN OUT NOCOPY NUMBER,
2740 stmt_line_id NUMBER,
2741 status VARCHAR2,
2742 cleared_date DATE,
2743 transaction_amount NUMBER,
2744 error_amount NUMBER,
2745 charge_amount NUMBER,
2746 currency_code VARCHAR2,
2747 xtype VARCHAR2,
2748 xdate DATE,
2749 xrate NUMBER,
2750 org_id NUMBER,
2751 legal_entity_id NUMBER ) IS
2752 value_date DATE := null;
2753 p_current_record_flag VARCHAR2(1) := null;
2754 current_org_id NUMBER;
2755
2756 X_RESULT VARCHAR2(100);
2757 clearing_flag VARCHAR2(1) := 'N'; -- Bug 13803033
2758
2759 BEGIN
2760
2761 IF l_DEBUG in ('Y', 'C') THEN
2762 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.unclear_process');
2763
2764 cep_standard.debug('passin_mode = ' ||passin_mode||' tx_type = '|| tx_type ||
2765 ', clearing_trx_type = ' ||clearing_trx_type || ', org_id = ' ||org_id ||
2766 ', legal_entity_id = ' ||legal_entity_id);
2767 cep_standard.debug('stmt_line_id = ' ||stmt_line_id || ', status = ' || status||
2768 ', currency_code = '||currency_code );
2769 cep_standard.debug('X_CHECKRUN_ID: '||to_char(batch_id)||', X_CHECK_ID: '||to_char(trx_id) ||
2770 ', cash_receipt_id ' ||cash_receipt_id ||
2771 ', trx_date ' ||trx_date ||
2772 ', cash_receipt_history_id '|| cash_receipt_history_id );
2773
2774 cep_standard.debug('X_ACCOUNTING_DATE: '||fnd_date.date_to_charDT(gl_date)||
2775 ', X_CLEARED_DATE: '||fnd_date.date_to_charDT(cleared_date)||
2776 ', X_TRANSACTION_AMOUNT: '||to_char(transaction_amount));
2777 cep_standard.debug('X_ERROR_AMOUNT: '||to_char(error_amount)||
2778 ', X_CHARGE_AMOUNT: '||to_char(charge_amount)||
2779 ', X_CURRENCY_CODE: '||currency_code);
2780 cep_standard.debug('X_EXCHANGE_RATE_TYPE: '||xtype||
2781 ', X_EXCHANGE_RATE_DATE: '||fnd_date.date_to_charDT(xdate));
2782 cep_standard.debug('X_EXCHANGE_RATE: '||to_char(xrate)||
2783 ', X_ACTUAL_VALUE_DATE: '||fnd_date.date_to_charDT(value_date));
2784 END IF;
2785 -- Bug 13803033 Start
2786 IF (passin_mode <> 'MANUAL_UC') THEN
2787 clearing_flag := 'Y';
2788 END IF;
2789 -- Bug 13803033 End
2790 --CE_AUTO_BANK_REC.G_org_id := org_id;
2791 --CE_AUTO_BANK_REC.G_legal_entity_id := legal_entity_id;
2792 CE_AUTO_BANK_CLEAR.G_org_id := org_id;
2793 CE_AUTO_BANK_CLEAR.G_legal_entity_id := legal_entity_id;
2794
2795 IF (org_id is not null) THEN
2796 select mo_global.GET_CURRENT_ORG_ID
2797 into current_org_id
2798 from dual;
2799
2800 IF l_DEBUG in ('Y', 'C') THEN
2801 cep_standard.debug('current_org_id =' ||current_org_id );
2802 END IF;
2803
2804 -- bug 3782741 set single org, since AR will not allow org_id to be passed
2805 IF (((current_org_id is null) or (org_id <> current_org_id )) AND
2806 (clearing_trx_type in ('CASH','MISC', 'PAYMENT'))) THEN
2807 mo_global.set_policy_context('S',org_id);
2808 cep_standard.debug('set current_org_id to ' ||org_id );
2809 END IF;
2810 END IF;
2811
2812 select mo_global.GET_CURRENT_ORG_ID
2813 into current_org_id
2814 from dual;
2815
2816 IF l_DEBUG in ('Y', 'C') THEN
2817 cep_standard.debug('current_org_id =' ||current_org_id );
2818 END IF;
2819
2820 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := NULL;
2821 IF (clearing_trx_type = 'PAYMENT') THEN
2822
2823 IF l_DEBUG in ('Y', 'C') THEN
2824 cep_standard.debug('>>>AP_RECONCILIATION_PKG.recon_payment_history');
2825 END IF;
2826
2827 /*Bug 3427050 added the following IF */
2828 IF (status NOT IN ('STOP INITIATED','VOIDED')) THEN
2829 AP_RECONCILIATION_PKG.recon_payment_history(
2830 X_CHECKRUN_ID => to_number(NULL),
2831 X_CHECK_ID => trx_id,
2832 X_ACCOUNTING_DATE => gl_date,
2833 X_CLEARED_DATE => cleared_date,
2834 X_TRANSACTION_AMOUNT => transaction_amount,
2835 X_TRANSACTION_TYPE => 'PAYMENT UNCLEARING',
2836 X_ERROR_AMOUNT => error_amount,
2837 X_CHARGE_AMOUNT => charge_amount,
2838 X_CURRENCY_CODE => currency_code,
2839 X_EXCHANGE_RATE_TYPE => xtype,
2840 X_EXCHANGE_RATE_DATE => xdate,
2841 X_EXCHANGE_RATE => xrate,
2842 X_MATCHED_FLAG => clearing_flag, -- Bug 13803033 Changed hardcoded 'Y' to clearing_flag
2843 X_ACTUAL_VALUE_DATE => value_date,
2844 X_LAST_UPDATE_DATE => sysdate,
2845 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
2846 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
2847 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
2848 X_CREATION_DATE => sysdate,
2849 X_PROGRAM_UPDATE_DATE => to_date(NULL),
2850 X_PROGRAM_APPLICATION_ID=> to_number(NULL),
2851 X_PROGRAM_ID => to_number(NULL),
2852 X_REQUEST_ID => to_number(NULL),
2853 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.unclear_process '
2854 );
2855 IF l_DEBUG in ('Y', 'C') THEN
2856 cep_standard.debug('unclear_process: ' || '>>>AP_RECONCILIATION_PKG.recon_payment_history');
2857 END IF;
2858
2859 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2860 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(200, trx_id, 'N');
2861 END IF; -- Bug 3427050
2862 ELSIF (clearing_trx_type IN ('CASH','MISC')) THEN
2863
2864 SELECT NVL(arh.current_record_flag, 'N')
2865 INTO p_current_record_flag
2866 FROM ar_cash_receipt_history_all arh
2867 WHERE arh.cash_receipt_history_id = trx_id;
2868
2869 IF (status not in ('REVERSED', 'RISK_ELIMINATED')
2870 AND p_current_record_flag <> 'N'
2871 AND arp_cashbook.receipt_debit_memo_reversed(cash_receipt_id) = 'N') THEN
2872 IF l_DEBUG in ('Y', 'C') THEN
2873 cep_standard.debug('call ARP_CASHBOOK.unclear');
2874 END IF;
2875
2876 ARP_CASHBOOK.unclear( p_cr_id => cash_receipt_id,
2877 p_trx_date => trx_date,
2878 p_gl_date => gl_date,
2879 p_actual_value_date => value_date,
2880 p_module_name => 'CEXCABMR',
2881 p_module_version => '1.0',
2882 p_crh_id => cash_receipt_history_id);
2883 IF l_DEBUG in ('Y', 'C') THEN
2884 cep_standard.debug('end call ARP_CASHBOOK.unclear');
2885 END IF;
2886
2887 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2888 update ce_available_transactions_tmp
2889 set reconciled_status_flag = 'N'
2890 where application_id = 222
2891 and trx_id = trx_id
2892 and status <> 'REVERSED';
2893 END IF;
2894 ELSIF (clearing_trx_type = 'ROI_LINE' ) THEN
2895 CE_999_PKG.unclear( X_trx_id => trx_id,
2896 X_trx_type => tx_type,
2897 X_status => nvl(CE_AUTO_BANK_REC.G_open_interface_float_status, status),
2898 X_trx_date => trx_date,
2899 X_gl_date => gl_date);
2900 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2901 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(999, trx_id, 'N');
2902
2903 ELSIF (clearing_trx_type = 'XTR_LINE' ) THEN
2904 IF l_DEBUG in ('Y', 'C') THEN
2905 cep_standard.debug('unclear_process: ' || 'clearing_trx_type = xtr_line start');
2906 END IF;
2907 XTR_WRAPPER_API_P.reconciliation(
2908 P_SETTLEMENT_SUMMARY_ID => trx_id,
2909 P_TASK => 'UNR',
2910 P_RECONCILED_METHOD => null,
2911 P_RESULT => X_RESULT,
2912 P_RECON_AMT => NULL, -- 8978548 (3/4): Added
2913 P_VAL_DATE => NULL); -- 8978548 (4/4): Added
2914 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2915 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(185, trx_id, 'N');
2916
2917
2918 IF l_DEBUG in ('Y', 'C') THEN
2919 cep_standard.debug('unclear_process: ' || 'X_RESULT = ' ||X_RESULT);
2920 END IF;
2921 if (X_RESULT <> 'XTR2_SUCCESS') then
2922 FND_MESSAGE.set_name( 'CE','CE_XTR_UPDATE_FAILED');
2923 RAISE APP_EXCEPTION.application_exception;
2924 end if;
2925 IF l_DEBUG in ('Y', 'C') THEN
2926 cep_standard.debug('unclear_process: ' || 'clearing_trx_type = xtr_line END');
2927 END IF;
2928 ELSIF (clearing_trx_type = 'CASHFLOW' ) THEN
2929 IF l_DEBUG in ('Y', 'C') THEN
2930 cep_standard.debug('clearing_trx_type = CASHFLOW start');
2931 END IF;
2932 -- passin_mode MANUAL, MANUAL_UC
2933
2934
2935 CE_CASHFLOW_PKG.CLEAR_CASHFLOW(
2936 X_CASHFLOW_ID => trx_id,
2937 X_TRX_STATUS => status,
2938 x_actual_value_date => trx_date,
2939 X_ACCOUNTING_DATE => gl_date,
2940 X_CLEARED_DATE => trx_date,
2941 X_CLEARED_AMOUNT => transaction_amount,
2942 X_CLEARED_ERROR_AMOUNT => NVL(error_amount,0),
2943 X_CLEARED_CHARGE_AMOUNT => NVL(charge_amount,0),
2944 X_CLEARED_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2945 X_CLEARED_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2946 X_CLEARED_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2947 X_PASSIN_MODE => passin_mode,
2948 x_statement_line_id => stmt_line_id,
2949 x_statement_line_type => null
2950 ) ;
2951
2952 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2953 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(261, trx_id, 'N');
2954
2955
2956 /*
2957 CE_CASHFLOW_PKG.CLEAR_CASHFLOW(
2958 X_CASHFLOW_ID => trx_id,
2959 X_TRX_STATUS => status,
2960 x_actual_value_date => trx_date,
2961 X_ACCOUNTING_DATE => gl_date,
2962 X_CLEARED_DATE => trx_date,
2963 X_CLEARED_AMOUNT => NVL(amount_cleared,0),
2964 X_CLEARED_ERROR_AMOUNT => NVL(errors_amount,0),
2965 X_CLEARED_CHARGE_AMOUNT => NVL(charges_amount,0),
2966 X_CLEARED_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2967 X_CLEARED_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2968 X_CLEARED_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2969 X_PASSIN_MODE => passin_mode
2970 ) ;
2971 */
2972 IF l_DEBUG in ('Y', 'C') THEN
2973 cep_standard.debug('clearing_trx_type = CASHFLOW END');
2974 END IF;
2975
2976 ELSIF (clearing_trx_type = 'STATEMENT') THEN
2977 IF (passin_mode = 'MANUAL') THEN
2978 CE_AUTO_BANK_MATCH.csl_statement_line_id := stmt_line_id;
2979 END IF;
2980 -- bug 4247469 the CE_AUTO_BANK_MATCH.csl_reconcile_flag need to be cleared
2981 -- in case a journal is processed before the stmt transaction
2982 CE_AUTO_BANK_MATCH.csl_reconcile_flag := NULL;
2983
2984 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2985 Y_statement_line_id => trx_id,
2986 Y_cleared_trx_type => clearing_trx_type,
2987 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2988 Y_ar_cash_receipt_id => cash_receipt_id,
2989 Y_reference_status => status,
2990 Y_auto_reconciled_flag => 'N',
2991 Y_status_flag => 'U');
2992
2993 CE_AUTO_BANK_CLEAR1.update_line_unreconciled(trx_id);
2994
2995 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2996 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(260, trx_id, 'N');
2997
2998 ELSIF (clearing_trx_type IN ('PAY','PAY_EFT')) THEN
2999 IF l_DEBUG in ('Y', 'C') THEN
3000 cep_standard.debug('call PAY_CE_RECONCILIATION_PKG.reverse_reconcile');
3001 END IF;
3002
3003 PAY_CE_RECONCILIATION_PKG.reverse_reconcile(
3004 p_payment_id => trx_id);
3005
3006 -- after unreconciliation, update the reconciled_status of this trx in the GT table to 'N'
3007 IF (clearing_trx_type = 'PAY') THEN
3008 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(801, trx_id, 'N');
3009 ELSE
3010 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(802, trx_id, 'N');
3011 END IF;
3012
3013
3014 IF l_DEBUG in ('Y', 'C') THEN
3015 cep_standard.debug('end call PAY_CE_RECONCILIATION_PKG.reverse_reconcile');
3016 END IF;
3017 END IF;
3018 IF (passin_mode <> 'MANUAL_UC') THEN
3019 CE_AUTO_BANK_MATCH.csl_statement_line_id := stmt_line_id;
3020 IF (clearing_trx_type IN ('PAYMENT','CASH','MISC','ROI_LINE','XTR_LINE','STATEMENT','PAY','PAY_EFT','CASHFLOW')) THEN
3021 CE_AUTO_BANK_MATCH.csl_reconcile_flag := NULL;
3022 ELSE
3023 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'JE';
3024 END IF;
3025
3026 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
3027 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
3028 Y_cleared_trx_type => clearing_trx_type,
3029 Y_cleared_trx_id => trx_id,
3030 Y_ar_cash_receipt_id => cash_receipt_id,
3031 Y_reference_status => status,
3032 Y_auto_reconciled_flag => 'N',
3033 Y_status_flag => 'U');
3034
3035 IF l_DEBUG in ('Y', 'C') THEN
3036 cep_standard.debug('call CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
3037 END IF;
3038
3039 CE_AUTO_BANK_CLEAR1.update_line_unreconciled(stmt_line_id);
3040
3041 IF l_DEBUG in ('Y', 'C') THEN
3042 cep_standard.debug('end call CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
3043 END IF;
3044 END IF;
3045 IF (X_header_or_line = 'HEADERS') THEN
3046 CE_AUTO_BANK_CLEAR1.update_line_unreconciled(stmt_line_id);
3047 END IF;
3048 IF l_DEBUG in ('Y', 'C') THEN
3049 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.unclear_process');
3050 END IF;
3051
3052 EXCEPTION
3053 when others then
3054 IF l_DEBUG in ('Y', 'C') THEN
3055 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR1.unclear_process');
3056 END IF;
3057 RAISE;
3058 END unclear_process;
3059
3060
3061 PROCEDURE populate_avail_trx(
3062 X_table VARCHAR2,
3063 X_where_clause VARCHAR,
3064 X_asset_code_combination_id NUMBER,
3065 X_bank_account_id NUMBER,
3066 X_from_number VARCHAR2,
3067 X_to_number VARCHAR2,
3068 X_status VARCHAR2,
3069 X_currency_code VARCHAR2,
3070 X_reference_id NUMBER,
3071 X_from_batch_name VARCHAR2,
3072 X_to_batch_name VARCHAR2,
3073 X_from_remit_num VARCHAR2,
3074 X_to_remit_num VARCHAR2,
3075 X_from_date DATE,
3076 X_to_date DATE,
3077 X_from_maturity_date DATE,
3078 X_to_maturity_date DATE,
3079 X_customer_id NUMBER,
3080 X_receipt_class_id NUMBER,
3081 X_receipt_method_id NUMBER,
3082 X_deposit_date_from DATE,
3083 X_deposit_date_to DATE,
3084 X_supplier_id NUMBER,
3085 X_reference_type VARCHAR2,
3086 X_period_name VARCHAR2,
3087 X_from_journal_entry_name VARCHAR2,
3088 X_to_journal_entry_name VARCHAR2,
3089 X_from_document_number NUMBER,
3090 X_to_document_number NUMBER,
3091 X_from_je_line_number NUMBER,
3092 X_to_je_line_number NUMBER,
3093 X_cleared_date DATE,
3094 X_value_date DATE,
3095 X_gl_date DATE,
3096 X_from_amount NUMBER,
3097 X_to_amount NUMBER,
3098 X_org_id NUMBER,
3099 X_legal_entity_id NUMBER) IS
3100 insert_stmt VARCHAR2(3000);
3101 cursor_id INTEGER;
3102 exec_id INTEGER;
3103 BEGIN
3104 cursor_id := DBMS_SQL.open_cursor;
3105 insert_stmt := 'INSERT INTO ce_available_transactions_tmp ' ||
3106 '(ROW_ID, MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, ' ||
3107 'BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, '||
3108 'TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, ' ||
3109 'CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, ' ||
3110 'GL_DATE, STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE, ' ||
3111 'MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE, ' ||
3112 'USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS, '||
3113 'BATCH_NAME, BATCH_ID, AGENT_NAME, CUSTOMER_NAME, PAYMENT_METHOD, '||
3114 'VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, REFERENCE_TYPE_DSP, '||
3115 'REFERENCE_TYPE, REFERENCE_ID, ACTUAL_AMOUNT_CLEARED, CREATION_DATE, '||
3116 'CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, '||
3117 'CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, '||
3118 'TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME, '||
3119 'JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER, '||
3120 'CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID, '||
3121 'RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE, ' ||
3122 'REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID) ' ||
3123 'SELECT ROW_ID, MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, '||
3124 'BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, ' ||
3125 'TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, CURRENCY_CODE, AMOUNT, '||
3126 'BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, GL_DATE, STATUS_DSP, STATUS, ' ||
3127 'null, TRX_DATE, CLEARED_DATE, MATURITY_DATE, EXCHANGE_RATE_DATE, ' ||
3128 'EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, ' ||
3129 'BANK_CHARGES, BANK_ERRORS, BATCH_NAME, BATCH_ID, AGENT_NAME, ' ||
3130 'CUSTOMER_NAME, PAYMENT_METHOD, VENDOR_NAME, CUSTOMER_ID, ' ||
3131 'SUPPLIER_ID, REFERENCE_TYPE_DSP, REFERENCE_TYPE, REFERENCE_ID, ' ||
3132 'ACTUAL_AMOUNT_CLEARED, CREATION_DATE, CREATED_BY, ' ||
3133 'LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, ' ||
3134 'CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, '||
3135 'TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME, '||
3136 'JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER, '||
3137 'CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID, '||
3138 'RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE, ' ||
3139 'REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID FROM ' || X_table ||
3140 ' WHERE ' || X_where_clause;
3141
3142 DBMS_SQL.Parse(cursor_id,
3143 insert_stmt,
3144 DBMS_SQL.v7);
3145 if (X_asset_code_combination_id is not null) then
3146 DBMS_SQL.bind_variable(cursor_id, ':asset_code_combination_id',
3147 X_asset_code_combination_id);
3148 end if;
3149 if (X_bank_account_id is not null) then
3150 DBMS_SQL.bind_variable(cursor_id, ':bank_account_id',
3151 X_bank_account_id);
3152 end if;
3153 if (X_org_id is not null) then
3154 DBMS_SQL.bind_variable(cursor_id, ':org_id',
3155 X_org_id);
3156 elsif (X_legal_entity_id is not null) then
3157 DBMS_SQL.bind_variable(cursor_id, ':legal_entity_id',
3158 X_legal_entity_id);
3159 end if;
3160 if (X_from_number is not null AND X_from_number <> '%') then
3161 DBMS_SQL.bind_variable(cursor_id, ':from_number', X_from_number);
3162 end if;
3163 if (X_to_number is not null AND X_to_number <> '%') then
3164 DBMS_SQL.bind_variable(cursor_id, ':to_number', X_to_number);
3165 end if;
3166 if (X_status is not null) then
3167 DBMS_SQL.bind_variable(cursor_id, ':status', X_status);
3168 end if;
3169 if (X_currency_code is not null) then
3170 DBMS_SQL.bind_variable(cursor_id, ':currency_code', X_currency_code);
3171 end if;
3172 if (X_reference_id is not null) then
3173 DBMS_SQL.bind_variable(cursor_id, ':reference_id', X_reference_id);
3174 end if;
3175 if (X_from_batch_name is not null AND X_from_batch_name <> '%') then
3176 DBMS_SQL.bind_variable(cursor_id, ':from_batch_name', X_from_batch_name);
3177 end if;
3178 if (X_to_batch_name is not null AND X_to_batch_name <> '%') then
3179 DBMS_SQL.bind_variable(cursor_id, ':to_batch_name', X_to_batch_name);
3180 end if;
3181 if (X_from_remit_num is not null AND X_from_remit_num <> '%') then
3182 DBMS_SQL.bind_variable(cursor_id, ':from_remit_num', X_from_remit_num);
3183 end if;
3184 if (X_to_remit_num is not null AND X_to_remit_num <> '%') then
3185 DBMS_SQL.bind_variable(cursor_id, ':to_remit_num', X_to_remit_num);
3186 end if;
3187 if (X_from_date is not null) then
3188 DBMS_SQL.bind_variable(cursor_id, ':from_date', X_from_date);
3189 end if;
3190 if (X_to_date is not null) then
3191 DBMS_SQL.bind_variable(cursor_id, ':to_date', X_to_date);
3192 end if;
3193 if (X_from_maturity_date is not null) then
3194 DBMS_SQL.bind_variable(cursor_id, ':from_maturity_date',
3195 X_from_maturity_date);
3196 end if;
3197 if (X_to_maturity_date is not null) then
3198 DBMS_SQL.bind_variable(cursor_id, ':to_maturity_date',
3199 X_to_maturity_date);
3200 end if;
3201 if (X_customer_id is not null) then
3202 DBMS_SQL.bind_variable(cursor_id, ':customer_id', X_customer_id);
3203 end if;
3204 if (X_receipt_class_id is not null) then
3205 DBMS_SQL.bind_variable(cursor_id, ':receipt_class_id',
3206 X_receipt_class_id);
3207 end if;
3208 if (X_receipt_method_id is not null) then
3209 DBMS_SQL.bind_variable(cursor_id, ':receipt_method_id',
3210 X_receipt_method_id);
3211 end if;
3212 if (X_deposit_date_from is not null) then
3213 DBMS_SQL.bind_variable(cursor_id, ':deposit_date_from',
3214 X_deposit_date_from);
3215 end if;
3216 if (X_deposit_date_to is not null) then
3217 DBMS_SQL.bind_variable(cursor_id, ':deposit_date_to', X_deposit_date_to);
3218 end if;
3219 if (X_receipt_class_id is not null) then
3220 DBMS_SQL.bind_variable(cursor_id, ':receipt_class_id',
3221 X_receipt_class_id);
3222 end if;
3223 if (X_supplier_id is not null) then
3224 DBMS_SQL.bind_variable(cursor_id, ':supplier_id', X_supplier_id);
3225 end if;
3226 if (X_reference_type is not null) then
3227 DBMS_SQL.bind_variable(cursor_id, ':reference_type', X_reference_type);
3228 end if;
3229 if (X_period_name is not null) then
3230 DBMS_SQL.bind_variable(cursor_id, ':period_name', X_period_name);
3231 end if;
3232 if (X_from_journal_entry_name is not null
3233 AND X_from_journal_entry_name <> '%') then
3234 DBMS_SQL.bind_variable(cursor_id, ':from_journal_entry_name',
3235 X_from_journal_entry_name);
3236 end if;
3237 if (X_to_journal_entry_name is not null
3238 AND X_to_journal_entry_name <> '%') then
3239 DBMS_SQL.bind_variable(cursor_id, ':to_journal_entry_name',
3240 X_to_journal_entry_name);
3241 end if;
3242 if (X_from_document_number is not null) then
3243 DBMS_SQL.bind_variable(cursor_id, ':from_document_number',
3244 X_from_document_number);
3245 end if;
3246 if (X_to_document_number is not null) then
3247 DBMS_SQL.bind_variable(cursor_id, ':to_document_number',
3248 X_to_document_number);
3249 end if;
3250 if (X_from_je_line_number is not null) then
3251 DBMS_SQL.bind_variable(cursor_id, ':from_je_line_number',
3252 X_from_je_line_number);
3253 end if;
3254 if (X_to_je_line_number is not null) then
3255 DBMS_SQL.bind_variable(cursor_id, ':to_je_line_number',
3256 X_to_je_line_number);
3257 end if;
3258 if (X_cleared_date is not null) then
3259 DBMS_SQL.bind_variable(cursor_id, ':cleared_date', X_cleared_date);
3260 end if;
3261 if (X_value_date is not null) then
3262 DBMS_SQL.bind_variable(cursor_id, ':value_date', X_value_date);
3263 end if;
3264 if (X_gl_date is not null) then
3265 DBMS_SQL.bind_variable(cursor_id, ':gl_date', X_gl_date);
3266 end if;
3267 if (X_from_amount is not null) then
3268 DBMS_SQL.bind_variable(cursor_id, ':from_amount', X_from_amount);
3269 end if;
3270 if (X_to_amount is not null) then
3271 DBMS_SQL.bind_variable(cursor_id, ':to_amount', X_to_amount);
3272 end if;
3273 exec_id := DBMS_SQL.execute(cursor_id);
3274 DBMS_SQL.close_cursor(cursor_id);
3275 EXCEPTION
3276 when others then
3277 null;
3278 END populate_avail_trx;
3279
3280 END CE_AUTO_BANK_CLEAR1;