[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_CLEAR1
Source
1 PACKAGE BODY CE_AUTO_BANK_CLEAR1 AS
2 /* $Header: ceabrc1b.pls 120.44.12010000.3 2008/11/02 18:27:02 csutaria 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
64 AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
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
99 AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
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.44.12010000.3 $';
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 ELSE -- foreign currency remittance batch
664 -- bug 3911424 used the new xrate, xrate date, xrate type and xrate amount cleared
665 IF l_DEBUG in ('Y', 'C') THEN
666 cep_standard.debug('receipt_id = '|| receipt_id);
667 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') );
668 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'));
669 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'));
670 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
671 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
672
673 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
674 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
675 cep_standard.debug('module = '|| module );
676 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
677
678 cep_standard.debug('trx_amount = '|| trx_amount);
679 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
680
681 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
682 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
683 END IF;
684
685 IF ((trx_currency_type = 'FOREIGN') and
686 (CE_AUTO_BANK_CLEAR.G_exchange_rate is not null)) THEN
687
688 adjusted_xrate_amount := (trx_amount * to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
689
690 FND_CURRENCY.get_info(CE_AUTO_BANK_MATCH.csl_currency_code,
691 precision,
692 ext_precision,
693 min_acct_unit);
694
695 IF l_DEBUG in ('Y', 'C') THEN
696 cep_standard.debug('precision = '|| precision);
697 END IF;
698
699 amount_to_clear := round(adjusted_xrate_amount, precision) ;
700
701 END IF;
702 IF l_DEBUG in ('Y', 'C') THEN
703
704 cep_standard.debug('set adjusted xrate_amount to amount_to_clear, trx_amount * CE_AUTO_BANK_CLEAR.G_exchange_rate ');
705 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
706 END IF;
707
708 ARP_CASHBOOK.clear(
709 p_cr_id => receipt_id,
710 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
711 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
712 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
713 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'), --trx_exchange_rate_date,
714 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type, --trx_exchange_rate_type,
715 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate), --amount_to_clear/trx_amount,
716 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
717 p_amount_cleared => amount_to_clear,
718 p_amount_factored => 0,
719 p_module_name => module,
720 p_module_version => '1.0',
721 p_crh_id => receipt_history_id);
722 END IF;
723 END IF;
724 IF l_DEBUG in ('Y', 'C') THEN
725 cep_standard.debug('after call');
726 cep_standard.debug('receipt_id = '|| receipt_id);
727 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'));
728 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'));
729 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'));
730 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
731 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
732
733 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
734 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
735 cep_standard.debug('module = '|| module );
736 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
737
738 cep_standard.debug('trx_amount = '|| trx_amount);
739 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
740 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
741 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
742 END IF;
743 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'CASH';
744 IF (passin_mode <> 'MANUAL_C' ) THEN -- bug 3436722
745 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
746 Y_cleared_trx_type =>receipt_type,
747 Y_cleared_trx_id =>receipt_history_id,
748 Y_ar_cash_receipt_id =>receipt_id,
749 Y_reference_status =>receipt_status,
750 Y_auto_reconciled_flag =>auto_reconcile_flag,
751 Y_status_flag =>'M',
752 Y_amount => amount_to_clear);
753 END IF;
754
755 -- mark the transaction in ce_available_transactions_tmp as reconciled
756 IF l_gt_seq_id is not null THEN
757 CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');
758 END IF;
759
760
761 END LOOP; -- rbatch_cursor
762 CLOSE rbatch_cursor;
763 else
764 -- Fix bug 5637589 for manual remittance batch reconciliation
765 OPEN manual_rbatch_cursor (rbatch_id);
766 LOOP
767 FETCH manual_rbatch_cursor INTO receipt_history_id,
768 receipt_id,
769 receipt_type,
770 receipt_date,
771 receipt_status,
772 amount_to_clear,
773 trx_amount,
774 receipt_gl_date,
775 trx_exchange_rate_date,
776 trx_exchange_rate_type,
777 X_org_id;
778 EXIT WHEN manual_rbatch_cursor%NOTFOUND OR manual_rbatch_cursor%NOTFOUND IS NULL;
779
780 --IF (to_date(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD') < to_date(receipt_gl_date,'YYYY/MM/DD')) THEN
781 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
782 CE_AUTO_BANK_REC.G_gl_date := receipt_gl_date;
783 END IF;
784
785 --IF (to_date(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD') <
786 --to_date(receipt_gl_date,'YYYY/MM/DD')) THEN
787 IF (to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') <
788 to_date(to_char(receipt_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD')) THEN
789 CE_AUTO_BANK_MATCH.csl_trx_date := receipt_gl_date;
790 END IF;
791
792 IF (receipt_status not in ('CLEARED', 'RISK_ELIMINATED')) THEN
793 IF (amount_to_clear = trx_amount) THEN
794 ARP_CASHBOOK.clear(
795 p_cr_id => receipt_id,
796 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
797 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
798 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
799 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
800 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
801 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate),
802 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
803 p_amount_cleared => amount_to_clear,
804 p_amount_factored => 0,
805 p_module_name => module,
806 p_module_version => '1.0',
807 p_crh_id => receipt_history_id);
808 ELSE -- foreign currency remittance batch
809 -- bug 3911424 used the new xrate, xrate date, xrate type and xrate amount cleared
810 IF l_DEBUG in ('Y', 'C') THEN
811 cep_standard.debug('receipt_id = '|| receipt_id);
812 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') );
813 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'));
814 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'));
815 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
816 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
817
818 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
819 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
820 cep_standard.debug('module = '|| module );
821 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
822
823 cep_standard.debug('trx_amount = '|| trx_amount);
824 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
825
826 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
827 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
828 END IF;
829
830 IF ((trx_currency_type = 'FOREIGN') and
831 (CE_AUTO_BANK_CLEAR.G_exchange_rate is not null)) THEN
832
833 adjusted_xrate_amount := (trx_amount * to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
834
835 FND_CURRENCY.get_info(CE_AUTO_BANK_MATCH.csl_currency_code,
836 precision,
837 ext_precision,
838 min_acct_unit);
839
840 IF l_DEBUG in ('Y', 'C') THEN
841 cep_standard.debug('precision = '|| precision);
842 END IF;
843
844 amount_to_clear := round(adjusted_xrate_amount, precision) ;
845
846 END IF;
847 IF l_DEBUG in ('Y', 'C') THEN
848
849 cep_standard.debug('set adjusted xrate_amount to amount_to_clear, trx_amount * CE_AUTO_BANK_CLEAR.G_exchange_rate ');
850 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
851 END IF;
852
853 ARP_CASHBOOK.clear(
854 p_cr_id => receipt_id,
855 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
856 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
857 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
858 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'), --trx_exchange_rate_date,
859 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type, --trx_exchange_rate_type,
860 p_exchange_rate => to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate), --amount_to_clear/trx_amount,
861 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
862 p_amount_cleared => amount_to_clear,
863 p_amount_factored => 0,
864 p_module_name => module,
865 p_module_version => '1.0',
866 p_crh_id => receipt_history_id);
867 END IF;
868 END IF;
869 IF l_DEBUG in ('Y', 'C') THEN
870 cep_standard.debug('after call');
871 cep_standard.debug('receipt_id = '|| receipt_id);
872 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'));
873 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'));
874 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'));
875 cep_standard.debug('exchange_rate = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate));
876 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type = '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
877
878 cep_standard.debug('to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) = '|| to_number(CE_AUTO_BANK_CLEAR.G_exchange_rate) );
879 cep_standard.debug('CE_AUTO_BANK_MATCH.aba_bank_currency = '|| CE_AUTO_BANK_MATCH.aba_bank_currency );
880 cep_standard.debug('module = '|| module );
881 cep_standard.debug('receipt_history_id = '|| receipt_history_id );
882
883 cep_standard.debug('trx_amount = '|| trx_amount);
884 cep_standard.debug('trx_exchange_rate_date = '|| trx_exchange_rate_date );
885 cep_standard.debug(' trx_exchange_rate_type = '|| trx_exchange_rate_type );
886 cep_standard.debug('amount_to_clear = '|| amount_to_clear);
887 END IF;
888 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'CASH';
889 IF (passin_mode <> 'MANUAL_C' ) THEN -- bug 3436722
890 -- 5637589
891 CE_AUTO_BANK_MATCH.trx_org_id := X_org_id;
892 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
893 Y_cleared_trx_type =>receipt_type,
894 Y_cleared_trx_id =>receipt_history_id,
895 Y_ar_cash_receipt_id =>receipt_id,
896 Y_reference_status =>receipt_status,
897 Y_auto_reconciled_flag =>auto_reconcile_flag,
898 Y_status_flag =>'M',
899 Y_amount => amount_to_clear);
900 END IF;
901
902 END LOOP; -- manual_rbatch_cursor
903 CLOSE manual_rbatch_cursor;
904 end if; -- End manual remittance batch reconciliation
905
906 IF (passin_mode <> 'MANUAL_C' ) THEN -- bug 3436722
907 IF (NVL(X_amount,0) <> 0) THEN
908 SELECT h.statement_number || '/' || to_char(l.line_number)
909 INTO X_trx_number
910 FROM CE_STATEMENT_HEADERS h,
911 CE_STATEMENT_LINES l
912 WHERE h.statement_header_id = l.statement_header_id AND
913 l.statement_line_id = X_statement_line_id;
914
915 IF trx_currency_type = 'FOREIGN' THEN
916 l_amount := X_amount/exchange_rate;
917 ELSE
918 l_amount := X_amount;
919 END IF;
920
921 if (passin_mode = 'AUTO' and
922 CE_AUTO_BANK_MATCH.ar_accounting_method = 'ACCRUAL') then
923 CE_AUTO_BANK_MATCH.get_vat_tax_id('AUTO', l_vat_tax_id, l_tax_rate);
924 else
925 l_vat_tax_id := X_tax_id;
926 l_tax_rate := X_tax_rate;
927 end if;
928
929 CE_AUTO_BANK_CLEAR.misc_receipt(
930 X_PASSIN_MODE => passin_mode,
931 X_TRX_NUMBER => X_trx_number,
932 X_DOC_SEQUENCE_VALUE => NULL,
933 X_DOC_SEQUENCE_ID => NULL,
934 X_GL_DATE => gl_date,
935 X_VALUE_DATE => value_date,
936 X_TRX_DATE => X_trx_date,
937 X_DEPOSIT_DATE => X_deposit_date,
938 X_AMOUNT => l_amount,
939 X_BANK_ACCOUNT_AMOUNT => X_amount,
940 X_SET_OF_BOOKS_ID => X_set_of_books_id,
941 X_MISC_CURRENCY_CODE => X_misc_currency_code,
942 X_EXCHANGE_RATE_DATE => exchange_rate_date,
943 X_EXCHANGE_RATE_TYPE => exchange_rate_type,
944 X_EXCHANGE_RATE => exchange_rate,
945 X_RECEIPT_METHOD_ID => X_receipt_method_id,
946 X_BANK_ACCOUNT_ID => X_bank_account_id,
947 X_ACTIVITY_TYPE_ID => X_activity_type_id,
948 X_COMMENTS => X_comments,
949 X_REFERENCE_TYPE => X_reference_type,
950 X_REFERENCE_ID => rbatch_id,
951 X_CLEAR_CURRENCY_CODE => bank_currency,
952 X_STATEMENT_LINE_ID => X_statement_line_id,
953 X_TAX_ID => l_vat_tax_id,
954 X_TAX_RATE => l_tax_rate,
955 X_PAID_FROM => NULL,
956 X_MODULE_NAME => module,
957 X_cr_vat_tax_id => X_cr_vat_tax_id,
958 X_dr_vat_tax_id => X_dr_vat_tax_id,
959 trx_currency_type => trx_currency_type,
960 X_CR_ID => misc_receipt_id,
961 X_effective_date => X_effective_date,
962 --X_org_id => CE_AUTO_BANK_MATCH.bau_org_id);
963 X_org_id => CE_AUTO_BANK_MATCH.trx_org_id );
964 END IF;
965 END IF;
966 IF l_DEBUG in ('Y', 'C') THEN
967 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_rbatch');
968 END IF;
969 EXCEPTION
970 WHEN OTHERS THEN
971 -- bug 2875549
972 cep_standard.debug('Exception: CE_AUTO_BANK_CLEAR1.reconcile_rbatch');
973 if (passin_mode = 'AUTO') then
974 IF rbatch_cursor%ISOPEN THEN
975 CLOSE rbatch_cursor;
976 END IF;
977 else
978 IF manual_rbatch_cursor%ISOPEN THEN
979 CLOSE manual_rbatch_cursor;
980 END IF;
981 end if;
982 RAISE;
983 END reconcile_rbatch;
984
985 /* ---------------------------------------------------------------------
986 | PRIVATE PROCEDURE |
987 | reconcile_pay_eft |
988 | DESCRIPTION |
989 | Each EFT payment should be cleared and reconciled |
990 | This procedure is used in AutoReconciliation only |
991 | Use reconcile_trx for manual reconciliation of EFT payments |
992 | CALLED BY |
993 | reconcile_process |
994 --------------------------------------------------------------------- */
995 PROCEDURE reconcile_pay_eft( passin_mode VARCHAR2,
996 tx_type VARCHAR2,
997 trx_count NUMBER,
998 trx_group VARCHAR2,
999 cleared_trx_type VARCHAR2,
1000 cleared_date DATE,
1001 X_bank_currency VARCHAR2,
1002 X_statement_line_id NUMBER,
1003 X_statement_line_type VARCHAR2,
1004 trx_currency_type VARCHAR2,
1005 auto_reconciled_flag VARCHAR2,
1006 X_statement_header_id NUMBER,
1007 X_bank_trx_number VARCHAR2,
1008 X_bank_account_id VARCHAR2,
1009 X_payroll_payment_format VARCHAR2,
1010 X_effective_date DATE,
1011 X_float_handling_flag VARCHAR2) IS
1012
1013 amount_to_clear NUMBER;
1014 cleared_trx_id NUMBER;
1015 auto_reconcile_flag VARCHAR2(1);
1016 l_amount NUMBER;
1017 l_gt_seq_id NUMBER := to_number(null);
1018
1019 cursor pay_eft_cursor is
1020
1021 SELECT catv.trx_id,
1022 catv.bank_account_amount,
1023 catv.seq_id
1024 --FROM ce_801_EFT_transactions_v catv
1025 FROM ce_available_transactions_tmp catv
1026 WHERE upper(catv.batch_name) =
1027 upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
1028 AND catv.trx_date = CE_AUTO_BANK_MATCH.csl_trx_date
1029 AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
1030 AND nvl(catv.status, 'C') <> 'V'
1031 and nvl(catv.batch_id, 0) = nvl(CE_AUTO_BANK_MATCH.trx_group,0)
1032 AND catv.application_id = 802 -- for payroll eft 802 is application id bug 7242853
1033 AND NVL(catv.reconciled_status_flag, 'N') = 'N';
1034
1035
1036
1037 BEGIN
1038 IF l_DEBUG in ('Y', 'C') THEN
1039 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_pay_eft');
1040 END IF;
1041 auto_reconcile_flag := 'Y';
1042 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
1043 --CE_AUTO_BANK_MATCH.csl_amount := amount_cleared;
1044 CE_AUTO_BANK_MATCH.csh_bank_account_id := X_bank_account_id;
1045 CE_AUTO_BANK_MATCH.trx_count := trx_count;
1046 CE_AUTO_BANK_MATCH.trx_group := trx_group;
1047 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
1048 CE_AUTO_BANK_MATCH.csl_payroll_payment_format := X_payroll_payment_format;
1049 CE_AUTO_BANK_MATCH.csl_reconcile_flag := cleared_trx_type;
1050
1051 IF l_DEBUG in ('Y', 'C') THEN
1052 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1053 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id);
1054 cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_group = '|| CE_AUTO_BANK_MATCH.trx_group);
1055 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1056 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
1057 cep_standard.debug('>>CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date);
1058
1059 END IF;
1060
1061 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
1062 X_effective_date,
1063 X_float_handling_flag);
1064 --
1065 -- The batch cannot be cleared and reconciled as one transaction so each
1066 -- EFT payment must be processed separately.
1067 --
1068 IF( passin_mode IN ( 'AUTO') AND (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'PAY_EFT')) THEN
1069 --IF (substr(CE_AUTO_BANK_MATCH.csl_payroll_payment_format,1,4) = 'BACS') THEN
1070
1071 IF l_DEBUG in ('Y', 'C') THEN
1072 cep_standard.debug('reconcile_pay_eft ' );
1073 cep_standard.debug('>>open pay_eft_cursor ');
1074 END IF;
1075
1076 OPEN pay_eft_cursor;
1077 LOOP
1078 IF l_DEBUG in ('Y', 'C') THEN
1079 cep_standard.debug('>>fetch pay_eft_cursor ');
1080 END IF;
1081
1082 FETCH pay_eft_cursor INTO cleared_trx_id,
1083 amount_to_clear,
1084 l_gt_seq_id;
1085
1086 IF l_DEBUG in ('Y', 'C') THEN
1087 cep_standard.debug('>>pay_eft_cursor cleared_trx_id = '|| cleared_trx_id);
1088 cep_standard.debug('>>pay_eft_cursor amount_to_clear = '|| amount_to_clear);
1089 END IF;
1090
1091 EXIT WHEN pay_eft_cursor%NOTFOUND OR pay_eft_cursor%NOTFOUND IS NULL;
1092
1093 -- mark the transaction in ce_available_transactions_tmp as reconciled
1094 /*
1095 IF l_gt_seq_id is not null THEN
1096 update ce_available_transactions_tmp
1097 set reconciled_status_flag = 'Y'
1098 where seq_id = l_gt_seq_id;
1099 END IF;
1100 */
1101
1102
1103 IF l_DEBUG in ('Y', 'C') THEN
1104 cep_standard.debug('reconcile_pay_eft: ' || '>>> Calling PAY_CE_RECONCILIATION_PKG.reconcile_payment'|| '-----' ||
1105 ' reconcile_pay_eft: ' || '>>> p_payment_id = '|| cleared_trx_id||
1106 ' p_cleared_date = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD') ||
1107 ' p_trx_amount = '|| NVL(amount_to_clear,0)||
1108 ' p_trx_type = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
1109 END IF;
1110
1111 PAY_CE_RECONCILIATION_PKG.reconcile_payment (
1112 p_payment_id => cleared_trx_id, --CE_AUTO_BANK_MATCH.trx_id,
1113 p_cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1114 p_trx_amount => NVL(amount_to_clear,0),
1115 p_trx_type => cleared_trx_type, --CE_AUTO_BANK_MATCH.csl_reconcile_flag,
1116 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
1117 p_last_update_login => NVL(FND_GLOBAL.user_id,-1),
1118 p_created_by => NVL(FND_GLOBAL.user_id,-1) );
1119
1120 IF l_DEBUG in ('Y', 'C') THEN
1121 cep_standard.debug('reconcile_pay_eft: ' || '<<< End PAY_CE_RECONCILIATION_PKG.reconcile_payment');
1122 END IF;
1123
1124
1125 IF l_DEBUG in ('Y', 'C') THEN
1126 cep_standard.debug('reconcile_pay_eft: ' || 'cleared_trx_type='||cleared_trx_type||
1127 ',cleared_trx_id='||to_char(cleared_trx_id));
1128 cep_standard.debug(',auto_reconciled_flag='||auto_reconciled_flag||',amount_cleared='||to_char(amount_to_clear));
1129 cep_standard.debug('reconcile_pay_eft: call CE_AUTO_BANK_CLEAR1.insert_reconciliation ');
1130 END IF;
1131
1132 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1133 Y_cleared_trx_type => cleared_trx_type,
1134 Y_cleared_trx_id => cleared_trx_id,
1135 Y_ar_cash_receipt_id => null,
1136 Y_reference_status => null,
1137 Y_auto_reconciled_flag => auto_reconciled_flag,
1138 Y_status_flag => 'M',
1139 Y_amount => amount_to_clear);
1140
1141 -- mark the transaction in ce_available_transactions_tmp as reconciled
1142 IF l_gt_seq_id is not null THEN
1143 CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');
1144 END IF;
1145
1146
1147 IF l_DEBUG in ('Y', 'C') THEN
1148 cep_standard.debug('reconcile_pay_eft: ' || '<<< End CE_AUTO_BANK_CLEAR1.insert_reconciliation');
1149 END IF;
1150
1151 END LOOP; -- pay_eft_cursor
1152 CLOSE pay_eft_cursor;
1153
1154 END IF;
1155
1156 IF l_DEBUG in ('Y', 'C') THEN
1157 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_pay_eft');
1158 END IF;
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 cep_standard.debug('Exception - OTHERS: CE_AUTO_BANK_CLEAR1.reconcile_pay_eft');
1162 IF pay_eft_cursor%ISOPEN THEN
1163 CLOSE pay_eft_cursor;
1164 END IF;
1165 RAISE;
1166 END reconcile_pay_eft;
1167
1168 /* ---------------------------------------------------------------------
1169 | PRIVATE PROCEDURE |
1170 | misc_receipt |
1171 --------------------------------------------------------------------- */
1172 PROCEDURE misc_receipt( X_passin_mode VARCHAR2,
1173 X_trx_number VARCHAR2,
1174 X_doc_sequence_value VARCHAR2,
1175 X_doc_sequence_id NUMBER,
1176 X_gl_date DATE,
1177 X_value_date DATE,
1178 X_trx_date DATE,
1179 X_deposit_date DATE,
1180 X_amount NUMBER,
1181 X_bank_account_amount NUMBER,
1182 X_set_of_books_id NUMBER,
1183 X_misc_currency_code VARCHAR2,
1184 X_exchange_rate_date DATE,
1185 X_exchange_rate_type VARCHAR2,
1186 X_exchange_rate NUMBER,
1187 X_receipt_method_id NUMBER,
1188 X_bank_account_id NUMBER,
1189 X_activity_type_id NUMBER,
1190 X_comments VARCHAR2,
1191 X_reference_type VARCHAR2,
1192 X_reference_id NUMBER,
1193 X_clear_currency_code VARCHAR2,
1194 X_statement_line_id IN OUT NOCOPY NUMBER,
1195 X_tax_id NUMBER,
1196 X_tax_rate NUMBER,
1197 X_paid_from VARCHAR2,
1198 X_module_name VARCHAR2,
1199 X_cr_vat_tax_id VARCHAR2,
1200 X_dr_vat_tax_id VARCHAR2,
1201 trx_currency_type VARCHAR2,
1202 X_cr_id IN OUT NOCOPY NUMBER,
1203 X_effective_date DATE,
1204 X_org_id NUMBER ) IS
1205 cash_receipt_history_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_history_id%
1206 TYPE;
1207 l_dbseqname VARCHAR2(30);
1208 l_doc_seq_id NUMBER;
1209 l_doc_seq_value NUMBER;
1210 l_valid_seq BOOLEAN := TRUE;
1211 l_status VARCHAR2(30);
1212 l_amount NUMBER;
1213 temp BOOLEAN;
1214 current_org_id number;
1215 X_REMIT_BANK_ACCT_USE_ID number;
1216 BEGIN
1217 IF l_DEBUG in ('Y', 'C') THEN
1218 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.misc_receipt');
1219 END IF;
1220 IF (X_statement_line_id IS NULL) THEN
1221 OPEN C_STATEMENT_LINE_SEQ;
1222 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
1223 CLOSE C_STATEMENT_LINE_SEQ;
1224 END IF;
1225 IF (X_passin_mode IN ('MANUAL_REC','MANUAL_NO_REC','MANUAL', 'MANUAL_H')) THEN
1226 CE_AUTO_BANK_REC.G_gl_date := X_gl_date;
1227 CE_AUTO_BANK_MATCH.csl_trx_date := X_trx_date;
1228 CE_AUTO_BANK_MATCH.csl_effective_date := X_value_date;
1229 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := X_exchange_rate_date;
1230 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := X_exchange_rate_type;
1231 CE_AUTO_BANK_MATCH.csl_exchange_rate := X_exchange_rate;
1232 CE_AUTO_BANK_REC.G_payment_method_id := X_receipt_method_id;
1233 CE_AUTO_BANK_MATCH.csh_bank_account_id := X_bank_account_id;
1234 CE_AUTO_BANK_REC.G_receivables_trx_id := X_activity_type_id;
1235 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
1236 CE_AUTO_BANK_MATCH.aba_bank_currency := X_clear_currency_code;
1237 CE_AUTO_BANK_REC.G_set_of_books_id := X_set_of_books_id;
1238 CE_AUTO_BANK_REC.G_cr_vat_tax_code := X_cr_vat_tax_id;
1239 CE_AUTO_BANK_REC.G_dr_vat_tax_code := X_dr_vat_tax_id;
1240 temp := CE_AUTO_BANK_MATCH.validate_payment_method;
1241 --CE_AUTO_BANK_REC.G_org_id := X_org_id;
1242 CE_AUTO_BANK_CLEAR.G_org_id := X_org_id;
1243 END IF;
1244 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
1245 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
1246 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
1247 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
1248 ELSE
1249 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
1250 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
1251 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
1252 END IF;
1253 --
1254 -- Call the AOL sequence numbering routine to get Seq. number
1255 --
1256 IF (X_passin_mode IN ('MANUAL_REC', 'MANUAL_NO_REC')) THEN
1257 l_doc_seq_id := X_doc_sequence_id;
1258 l_doc_seq_value := X_doc_sequence_value;
1259 ELSE
1260 -- l_vat_tax_id := CE_AUTO_BANK_MATCH.get_vat_tax_id;
1261 -- CE_AUTO_BANK_MATCH.get_vat_tax_id(l_vat_tax_id, l_tax_rate);
1262 l_valid_seq := CE_AUTO_BANK_IMPORT.get_sequence_info(
1263 222,
1264 nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_name,
1265 CE_AUTO_BANK_REC.G_payment_method_name),
1266 CE_AUTO_BANK_REC.G_set_of_books_id,
1267 'A',
1268 CE_AUTO_BANK_MATCH.csl_trx_date,
1269 l_dbseqname,
1270 l_doc_seq_id,
1271 l_doc_seq_value );
1272 IF (NOT l_valid_seq) THEN
1273 IF (X_passin_mode IN ('AUTO','AUTO_TRX')) THEN
1274 CE_RECONCILIATION_ERRORS_PKG.insert_row(
1275 CE_AUTO_BANK_MATCH.csh_statement_header_id,
1276 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_DOC_SEQUENCE_ERR');
1277 ELSE
1278 FND_MESSAGE.set_name('CE','CE_DOC_SEQUENCE_ERR');
1279 END IF;
1280 RAISE APP_EXCEPTION.application_exception;
1281 END IF;
1282 END IF;
1283
1284 /* bug# 1097681 take care of the logic in the MISC_RECEIPTS forms
1285
1286 --
1287 -- Bug750582
1288 --
1289 IF X_misc_currency_code <> CE_AUTO_BANK_MATCH.aba_bank_currency THEN
1290 l_amount := X_amount / X_exchange_rate;
1291 ELSE
1292 l_amount := X_amount;
1293 END IF;
1294 */
1295 --set this in the form mo_global.set_policy_context('S',x_org_id);
1296
1297 select mo_global.GET_CURRENT_ORG_ID
1298 into current_org_id
1299 from dual;
1300
1301 IF l_DEBUG in ('Y', 'C') THEN
1302 cep_standard.debug('current_org_id =' ||current_org_id );
1303
1304 cep_standard.debug('X_trx_number =' ||X_trx_number );
1305 cep_standard.debug('l_doc_seq_value =' ||l_doc_seq_value );
1306 cep_standard.debug('l_doc_seq_id =' ||l_doc_seq_id );
1307 cep_standard.debug('X_gl_date =' ||X_gl_date );
1308 cep_standard.debug('X_trx_date =' ||X_trx_date );
1309 cep_standard.debug('X_deposit_date =' ||X_deposit_date );
1310 cep_standard.debug('X_amount =' ||X_amount );
1311 cep_standard.debug('X_misc_currency_code =' ||X_misc_currency_code );
1312 cep_standard.debug('X_exchange_rate_date =' ||X_exchange_rate_date );
1313 cep_standard.debug('X_exchange_rate_type =' ||X_exchange_rate_type );
1314 cep_standard.debug('X_exchange_rate =' ||X_exchange_rate );
1315 cep_standard.debug('X_receipt_method_id =' ||X_receipt_method_id );
1316 cep_standard.debug('X_bank_account_id =' ||X_bank_account_id );
1317 cep_standard.debug('X_activity_type_id =' ||X_activity_type_id );
1318 cep_standard.debug('X_comments =' ||X_comments );
1319 cep_standard.debug('X_tax_id =' ||X_tax_id );
1320 cep_standard.debug('X_reference_type =' ||X_reference_type );
1321 cep_standard.debug('X_reference_id =' ||X_reference_id );
1322 cep_standard.debug('X_paid_from =' ||X_paid_from );
1323 cep_standard.debug('X_effective_date =' ||X_effective_date );
1324 cep_standard.debug('X_module_name =' ||X_module_name );
1325 --cep_standard.debug('X_cr_id =' ||X_cr_id );
1326 cep_standard.debug('X_tax_rate =' ||X_tax_rate );
1327
1328 END IF;
1329
1330 -- bug 5185358/5221366 p_remit_bank_account_id is the
1331 -- ar_receipt_method_accounts.remit_bank_acct_uses_id
1332 -- bug 5722367 changed to check for the bank account use id
1333
1334 BEGIN
1335 SELECT REMIT_BANK_ACCT_USE_ID
1336 INTO X_REMIT_BANK_ACCT_USE_ID
1337 FROM ar_receipt_methods rm,
1338 ar_receipt_method_accounts rma,
1339 ce_bank_acct_uses cba
1340 WHERE
1341 rm.receipt_method_id = X_receipt_method_id
1342 AND rma.receipt_method_id = rm.receipt_method_id
1343 AND cba.bank_acct_use_id = rma.remit_bank_acct_use_id
1344 AND cba.ar_use_enable_flag = 'Y'
1345 AND cba.bank_account_id = X_bank_account_id;
1346
1347 EXCEPTION
1348 WHEN NO_DATA_FOUND THEN
1349 IF l_DEBUG in ('Y', 'C') THEN
1350 cep_standard.debug('X_receipt_method_id does not exists ' || 'No data found');
1351 END IF;
1352 CE_RECONCILIATION_ERRORS_PKG.insert_row(
1353 CE_AUTO_BANK_MATCH.csh_statement_header_id,
1354 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_TEMP_AR_METHOD_ORG_INVALID');
1355 END;
1356
1357 IF l_DEBUG in ('Y', 'C') THEN
1358 cep_standard.debug('X_REMIT_BANK_ACCT_USE_ID = '||X_REMIT_BANK_ACCT_USE_ID);
1359 cep_standard.debug('call ARP_CASHBOOK.ins_misc_txn ');
1360 END IF;
1361
1362 ARP_CASHBOOK.ins_misc_txn(
1363 p_receipt_number => X_trx_number,
1364 p_document_number => l_doc_seq_value,
1365 p_doc_sequence_id => l_doc_seq_id,
1366 p_gl_date => X_gl_date,
1367 p_receipt_date => X_trx_date,
1368 p_deposit_date => X_deposit_date,
1369 p_receipt_amount => X_amount,
1370 p_currency_code => X_misc_currency_code,
1371 p_exchange_date => X_exchange_rate_date,
1372 p_exchange_rate_type => X_exchange_rate_type,
1373 p_exchange_rate => X_exchange_rate,
1374 p_receipt_method_id => X_receipt_method_id,
1375 p_remit_bank_account_id => X_REMIT_BANK_ACCT_USE_ID, --X_bank_account_id,
1376 p_receivables_trx_id => X_activity_type_id,
1377 p_comments => X_comments,
1378 p_vat_tax_id => X_tax_id,
1379 p_reference_type => X_reference_type,
1380 p_reference_id => X_reference_id,
1381 p_misc_payment_source => X_paid_from,
1382 p_anticipated_clearing_date => X_effective_date,
1383 p_module_name => X_module_name,
1384 p_module_version => '1.0',
1385 p_cr_id => X_cr_id,
1386 p_tax_rate => abs(X_tax_rate));
1387
1388 IF l_DEBUG in ('Y', 'C') THEN
1389 cep_standard.debug('end call ARP_CASHBOOK.ins_misc_txn ');
1390 cep_standard.debug('X_cr_id = '|| X_cr_id);
1391
1392 END IF;
1393
1394 -- set this in the form mo_global.set_policy_context('M',null);
1395 --
1396 -- Check the status of the newly created receipt, and not to cleared
1397 -- it if it was created with Cleared status
1398 --
1399 BEGIN
1400 SELECT arh.status, arh.cash_receipt_history_id
1401 INTO l_status, cash_receipt_history_id
1402 FROM ar_cash_receipt_history_all arh --ar_cash_receipt_history arh
1403 WHERE arh.cash_receipt_id = X_cr_id AND
1404 arh.current_record_flag = 'Y';
1405 EXCEPTION
1406 WHEN NO_DATA_FOUND THEN
1407 l_status := 'REMITTED';
1408 END;
1409 if (CE_AUTO_BANK_MATCH.trx_status <> 'REVERSED') then
1410 CE_AUTO_BANK_MATCH.trx_status := l_status;
1411 CE_AUTO_BANK_MATCH.trx_id := cash_receipt_history_id;
1412 end if;
1413 --
1414 -- Clear and Match the newly created receipt
1415 --
1416 IF (X_passin_mode NOT IN ('MANUAL_NO_REC','AUTO_TRX')) THEN
1417 IF (l_status not in ('CLEARED', 'RISK_ELIMINATED'))THEN
1418 ARP_CASHBOOK.clear(X_cr_id,
1419 to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1420 to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1421 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1422 to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1423 CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1424 CE_AUTO_BANK_CLEAR.G_exchange_rate,
1425 CE_AUTO_BANK_MATCH.aba_bank_currency,
1426 X_bank_account_amount,
1427 0,
1428 X_module_name,
1429 '1.0',
1430 cash_receipt_history_id);
1431 END IF;
1432 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'MISC';
1433 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1434 Y_cleared_trx_type => 'MISC',
1435 Y_cleared_trx_id => cash_receipt_history_id,
1436 Y_ar_cash_receipt_id => X_cr_id,
1437 Y_reference_status => NULL,
1438 Y_auto_reconciled_flag => 'N',
1439 Y_status_flag => 'M',
1440 Y_amount => X_bank_account_amount);
1441 END IF;
1442 IF l_DEBUG in ('Y', 'C') THEN
1443 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.misc_receipt');
1444 END IF;
1445 END misc_receipt;
1446
1447 /* ---------------------------------------------------------------------
1448 | PRIVATE PROCEDURE |
1449 | reconcile_pbatch |
1450 | CALLED BY |
1451 | reconcile_process |
1452 --------------------------------------------------------------------- */
1453 PROCEDURE reconcile_pbatch ( passin_mode VARCHAR2,
1454 pbatch_id NUMBER,
1455 statement_line_id IN OUT NOCOPY NUMBER,
1456 gl_date DATE,
1457 value_date DATE,
1458 cleared_date DATE,
1459 amount_to_clear NUMBER,
1460 errors_amount NUMBER,
1461 charges_amount NUMBER,
1462 prorate_amount NUMBER,
1463 exchange_rate_type VARCHAR2,
1464 exchange_rate_date DATE,
1465 exchange_rate NUMBER,
1466 trx_currency_type VARCHAR2,
1467 X_statement_header_id IN OUT NOCOPY NUMBER,
1468 statement_header_date DATE,
1469 X_trx_type VARCHAR2,
1470 X_bank_trx_number VARCHAR2,
1471 X_currency_code VARCHAR2,
1472 X_original_amount NUMBER,
1473 X_effective_date DATE,
1474 X_float_handling_flag VARCHAR2,
1475 X_bank_currency_code VARCHAR2,
1476 pgroup_id VARCHAR2 DEFAULT NULL -- FOR SEPA ER 6700007
1477 ) IS
1478 check_id AP_CHECKS_ALL.check_id%TYPE;
1479 check_status CE_LOOKUPS.lookup_code%TYPE;
1480 amount_cleared AP_CHECKS_ALL.cleared_amount%TYPE;
1481 auto_reconciled_flag VARCHAR2(1);
1482 batch_trx_type varchar2(30);
1483 batch_app_id number;
1484 l_gt_seq_id number := to_number(null);
1485 x_org_id number;
1486 x_legal_entity_id number;
1487
1488 BEGIN
1489 IF l_DEBUG in ('Y', 'C') THEN
1490 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_pbatch');
1491 cep_standard.debug('passin_mode='||passin_mode );
1492 END IF;
1493 auto_reconciled_flag := 'Y';
1494 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
1495 X_effective_date,
1496 X_float_handling_flag);
1497 IF (passin_mode IN ( 'MANUAL', 'MANUAL_H', 'MANUAL_C')) THEN
1498 --IF (statement_line_id IS NULL) THEN
1499 IF (statement_line_id IS NULL AND passin_mode <> 'MANUAL_C') THEN --bug 3436722
1500 OPEN C_STATEMENT_LINE_SEQ;
1501 FETCH C_STATEMENT_LINE_SEQ INTO statement_line_id;
1502 CLOSE C_STATEMENT_LINE_SEQ;
1503 END IF;
1504 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
1505 CE_AUTO_BANK_MATCH.csh_statement_date := statement_header_date;
1506 CE_AUTO_BANK_REC.G_gl_date := gl_date;
1507 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
1508 CE_AUTO_BANK_MATCH.csl_statement_line_id := statement_line_id;
1509 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
1510 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_rate_date;
1511 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
1512 CE_AUTO_BANK_MATCH.csl_amount := ABS(amount_to_clear);
1513 CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
1514 CE_AUTO_BANK_MATCH.csl_charges_amount := charges_amount;
1515 CE_AUTO_BANK_MATCH.csl_currency_code := X_currency_code;
1516 CE_AUTO_BANK_MATCH.csl_trx_type := X_trx_type;
1517 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
1518 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
1519 CE_AUTO_BANK_MATCH.trx_status := NULL;
1520 auto_reconciled_flag := 'N';
1521 IF( passin_mode = 'MANUAL_H') THEN
1522 CE_AUTO_BANK_CLEAR1.create_statement_line;
1523 IF(X_statement_header_id IS NULL)THEN
1524 X_statement_header_id := CE_AUTO_BANK_MATCH.csh_statement_header_id;
1525 END IF;
1526 END IF;
1527 END IF;
1528 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
1529 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
1530 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
1531 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
1532 ELSE
1533 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
1534 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
1535 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
1536 END IF;
1537 ------------------------------------------------------------------------
1538 IF (passin_mode <> 'MANUAL_C' ) THEN --bug 3436722
1539 -- bug 5350073 ce_available_transactions_tmp is not populated when
1540 -- manually reconcile IBY batches
1541 IF (passin_mode = 'AUTO' ) THEN
1542 cep_standard.debug('open pbatch_cursor');
1543
1544 OPEN pbatch_cursor(pbatch_id, pgroup_id );-- FOR SEPA ER 6700007
1545 LOOP
1546 FETCH pbatch_cursor INTO check_id, check_status, batch_trx_type, batch_app_id, l_gt_seq_id;
1547 EXIT WHEN pbatch_cursor%NOTFOUND OR pbatch_cursor%NOTFOUND IS NULL;
1548
1549 -- mark the transaction in ce_available_transactions_tmp as reconciled
1550 /*
1551 IF l_gt_seq_id is not null THEN
1552 update ce_available_transactions_tmp
1553 set reconciled_status_flag = 'Y'
1554 where seq_id = l_gt_seq_id;
1555 END IF;
1556 */
1557 IF l_DEBUG in ('Y', 'C') THEN
1558 cep_standard.debug('batch_trx_type='||batch_trx_type ||
1559 ', check_id='||check_id||',check_status='||check_status );
1560 cep_standard.debug('batch_app_id='||batch_app_id||', l_gt_seq_id='||l_gt_seq_id);
1561 cep_standard.debug('call CE_AUTO_BANK_CLEAR1.insert_reconciliation');
1562 END IF;
1563 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1564 Y_cleared_trx_type => batch_trx_type, -- 'PAYMENT',
1565 Y_cleared_trx_id => check_id,
1566 Y_ar_cash_receipt_id => NULL,
1567 Y_reference_status => check_status,
1568 Y_auto_reconciled_flag => auto_reconciled_flag,
1569 Y_status_flag => 'M');
1570
1571 -- mark the transaction in ce_available_transactions_tmp as reconciled
1572 IF l_gt_seq_id is not null THEN
1573 CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');
1574 END IF;
1575
1576 END LOOP; -- pbatch_cursor
1577 CLOSE pbatch_cursor;
1578 ELSE -- manual reconciliation
1579 cep_standard.debug('open manual_pbatch_cursor ');
1580
1581 OPEN manual_pbatch_cursor(pbatch_id, pgroup_id );-- FOR SEPA ER 6700007
1582 LOOP
1583 FETCH manual_pbatch_cursor
1584 INTO check_id, check_status, batch_trx_type, batch_app_id,
1585 x_org_id, x_legal_entity_id;
1586 EXIT WHEN manual_pbatch_cursor%NOTFOUND OR manual_pbatch_cursor%NOTFOUND IS NULL;
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);
1592 cep_standard.debug('call CE_AUTO_BANK_CLEAR1.insert_reconciliation');
1593 END IF;
1594 CE_AUTO_BANK_MATCH.trx_org_id := x_org_id;
1595 CE_AUTO_BANK_MATCH.trx_legal_entity_id := x_legal_entity_id;
1596 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
1597 Y_cleared_trx_type => batch_trx_type, -- 'PAYMENT',
1598 Y_cleared_trx_id => check_id,
1599 Y_ar_cash_receipt_id => NULL,
1600 Y_reference_status => check_status,
1601 Y_auto_reconciled_flag => auto_reconciled_flag,
1602 Y_status_flag => 'M');
1603
1604 END LOOP; -- manual_pbatch_cursor
1605 CLOSE manual_pbatch_cursor;
1606 END IF;
1607 END IF; --(passin_mode <> 'MANUAL_C' )
1608
1609 ------------------------------------------------------------------------
1610
1611 /*
1612 IF l_DEBUG in ('Y', 'C') THEN
1613 cep_standard.debug('reconcile_pbatch: ' || '>>>AP_RECONCILIATION_PKG.recon_payment_history'|| chr(10) ||
1614 'X_CHECKRUN_ID: '||to_char(pbatch_id)|| chr(10) ||
1615 'pgroup_id: '||to_char(pgroup_id)|| chr(10) ||
1616 'X_ACCOUNTING_DATE: '||CE_AUTO_BANK_REC.G_gl_date|| chr(10) ||
1617 'X_CLEARED_DATE: '||CE_AUTO_BANK_MATCH.csl_trx_date|| chr(10) ||
1618 'X_TRANSACTION_AMOUNT: '||to_char(amount_to_clear)|| chr(10) ||
1619 'X_ERROR_AMOUNT: '||to_char(errors_amount)|| chr(10) ||
1620 'X_CHARGE_AMOUNT: '||to_char(charges_amount)|| chr(10) ||
1621 'X_CURRENCY_CODE: '||X_currency_code|| chr(10) ||
1622 'X_BANK_CURRENCY_CODE: '||X_bank_currency_code|| chr(10) ||
1623 'X_EXCHANGE_RATE_TYPE: '||CE_AUTO_BANK_CLEAR.G_exchange_rate_type|| chr(10) ||
1624 'X_EXCHANGE_RATE_DATE: '||CE_AUTO_BANK_CLEAR.G_exchange_date|| chr(10) ||
1625 'X_EXCHANGE_RATE: '||to_char(CE_AUTO_BANK_CLEAR.G_exchange_rate)|| chr(10) ||
1626 'X_ACTUAL_VALUE_DATE: '||CE_AUTO_BANK_MATCH.csl_effective_date);
1627 END IF;
1628 */
1629 ------------------------------------------------------------------------------
1630 IF l_DEBUG in ('Y', 'C') THEN
1631 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')||
1632 ', CE_AUTO_BANK_MATCH.csl_trx_date='||to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
1633
1634 cep_standard.debug('amount_to_clear='||NVL(amount_to_clear,0)||', errors_amount='||NVL(errors_amount,0)||
1635 ', charges_amount='||NVL(charges_amount,0)||', X_bank_currency_code='||X_bank_currency_code);
1636
1637 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate_type='||CE_AUTO_BANK_CLEAR.G_exchange_rate_type||
1638 ', CE_AUTO_BANK_CLEAR.G_exchange_date='||to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD') ||
1639 ', CE_AUTO_BANK_CLEAR.G_exchange_rate='||CE_AUTO_BANK_CLEAR.G_exchange_rate);
1640
1641 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')||
1642 ', passin_mode='||passin_mode ||', statement_line_id='||statement_line_id);
1643
1644 END IF;
1645
1646 --check if pbatch_id is a AP batch or IBY batch
1647 if (batch_app_id = 200) THEN
1648 cep_standard.debug('call AP_RECONCILIATION_PKG.recon_payment_history');
1649
1650 AP_RECONCILIATION_PKG.recon_payment_history(
1651 X_CHECKRUN_ID => pbatch_id,
1652 X_CHECK_ID => to_number(NULL),
1653 X_ACCOUNTING_DATE => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1654 X_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1655 X_TRANSACTION_AMOUNT => NVL(amount_to_clear,0),
1656 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
1657 X_ERROR_AMOUNT => NVL(errors_amount,0),
1658 X_CHARGE_AMOUNT => NVL(charges_amount,0),
1659 X_CURRENCY_CODE => X_bank_currency_code,
1660 X_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1661 X_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1662 X_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
1663 X_MATCHED_FLAG => 'Y',
1664 X_ACTUAL_VALUE_DATE =>
1665 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1666 X_LAST_UPDATE_DATE => sysdate,
1667 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
1668 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
1669 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
1670 X_CREATION_DATE => sysdate,
1671 X_PROGRAM_UPDATE_DATE => sysdate,
1672 X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
1673 X_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
1674 X_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
1675 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_pbatch '
1676 );
1677 IF l_DEBUG in ('Y', 'C') THEN
1678 cep_standard.debug('reconcile_pbatch: ' || '<<<AP_RECONCILIATION_PKG.recon_payment_history');
1679 END IF;
1680
1681 else --IBY batches
1682
1683 cep_standard.debug('call IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing');
1684 IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing(
1685 P_PAYMENT_INSTRUCTION_ID => pbatch_id,
1686 P_ACCOUNTING_DATE => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1687 P_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1688 P_TRANSACTION_AMOUNT => NVL(amount_to_clear,0), -- in bank curr.
1689 P_ERROR_AMOUNT => NVL(errors_amount,0), -- in bank curr.
1690 P_CHARGE_AMOUNT => NVL(charges_amount,0), -- in bank curr.
1691 P_CURRENCY_CODE => X_bank_currency_code, -- bank curr. code
1692 P_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type, -- between payment and functional
1693 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
1694 P_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate, -- between payment and functional
1695 P_MATCHED_FLAG => 'Y',
1696 P_ACTUAL_VALUE_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1697 P_PASSIN_MODE => passin_mode ,
1698 P_STATEMENT_LINE_ID => statement_line_id ,
1699 P_STATEMENT_LINE_TYPE => null,
1700 P_LAST_UPDATE_DATE => sysdate,
1701 P_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
1702 P_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
1703 P_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
1704 P_CREATION_DATE => sysdate,
1705 P_PROGRAM_UPDATE_DATE => sysdate,
1706 P_PROGRAM_APPLICATION_ID => NVL(FND_GLOBAL.prog_appl_id,-1),
1707 P_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
1708 P_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
1709 P_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_pbatch',
1710 P_LOGICAL_GROUP_REFERENCE => pgroup_id);-- FOR SEPA ER 6700007
1711
1712 cep_standard.debug('end call IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing');
1713
1714 end if;
1715 ------------------------------------
1716 IF (passin_mode <> 'MANUAL_C' ) THEN --bug 3436677
1717 IF l_DEBUG in ('Y', 'C') THEN
1718 cep_standard.debug('update ce_statement_reconcils_all');
1719 END IF;
1720 --update ce_statement_reconciliations r
1721 update ce_statement_reconcils_all r
1722 set amount =
1723 (select DECODE(c.currency_code,
1724 X_bank_currency_code, c.cleared_amount,
1725 nvl(c.cleared_base_amount, c.cleared_amount*CE_AUTO_BANK_CLEAR.G_exchange_rate))
1726 from ap_checks_all c
1727 where c.check_id = r.reference_id)
1728 where statement_line_id = CE_AUTO_BANK_MATCH.csl_statement_line_id
1729 and reference_type = 'PAYMENT'
1730 and status_flag = 'M'
1731 and current_record_flag = 'Y'
1732 and amount is null;
1733 END IF;
1734 IF l_DEBUG in ('Y', 'C') THEN
1735 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_pbatch');
1736 END IF;
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 cep_standard.debug('Exception: CE_AUTO_BANK_CLEAR1.reconcile_pbatch');
1740 if (passin_mode = 'AUTO') then
1741 IF pbatch_cursor%ISOPEN THEN
1742 CLOSE pbatch_cursor;
1743 END IF;
1744 else
1745 IF manual_pbatch_cursor%ISOPEN THEN
1746 CLOSE manual_pbatch_cursor;
1747 END IF;
1748 end if;
1749 RAISE;
1750 END reconcile_pbatch;
1751
1752 /* ---------------------------------------------------------------------
1753 | PRIVATE PROCEDURE |
1754 | reconcile_stmt |
1755 | CALLED BY |
1756 | reconcile_process |
1757 --------------------------------------------------------------------- */
1758 PROCEDURE reconcile_stmt(passin_mode VARCHAR2,
1759 tx_type VARCHAR2,
1760 trx_id NUMBER,
1761 trx_status VARCHAR2,
1762 receipt_type VARCHAR2,
1763 exchange_rate_type VARCHAR2,
1764 exchange_date DATE,
1765 exchange_rate NUMBER,
1766 amount_cleared NUMBER,
1767 charges_amount NUMBER,
1768 errors_amount NUMBER,
1769 gl_date DATE,
1770 value_date DATE,
1771 cleared_date DATE,
1772 ar_cash_receipt_id NUMBER,
1773 X_bank_currency VARCHAR2,
1774 X_statement_line_id IN OUT NOCOPY NUMBER,
1775 X_statement_line_type VARCHAR2,
1776 reference_status VARCHAR2,
1777 trx_currency_type VARCHAR2,
1778 auto_reconciled_flag VARCHAR2,
1779 X_statement_header_id IN OUT NOCOPY NUMBER,
1780 X_effective_date DATE,
1781 X_float_handling_flag VARCHAR2,
1782 X_currency_code VARCHAR2,
1783 X_bank_trx_number VARCHAR2,
1784 X_reversed_receipt_flag VARCHAR2) IS
1785 cleared_trx_type CE_LOOKUPS.lookup_code%TYPE;
1786 cleared_trx_id CE_STATEMENT_RECONCILS_ALL.reference_id%TYPE;
1787 clearing_flag VARCHAR2(1) := 'N';
1788 clearing_sign NUMBER;
1789 x_trx_id NUMBER;
1790 x_trx_amount NUMBER;
1791 x_trx_amount2 NUMBER;
1792 x_trx_amount3 NUMBER;
1793 BEGIN
1794 IF l_DEBUG in ('Y', 'C') THEN
1795 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_stmt, passin_mode = '||passin_mode );
1796 END IF;
1797 IF (passin_mode <> 'MANUAL_C') THEN
1798 clearing_flag := 'Y';
1799 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
1800 X_effective_date,
1801 X_float_handling_flag);
1802 END IF;
1803 IF (passin_mode IN ('MANUAL_L','MANUAL_C')) THEN
1804 IF (X_statement_line_id IS NULL AND passin_mode <> 'MANUAL_C') THEN
1805 OPEN C_STATEMENT_LINE_SEQ;
1806 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
1807 CLOSE C_STATEMENT_LINE_SEQ;
1808 END IF;
1809 CE_AUTO_BANK_MATCH.csl_trx_type := tx_type;
1810 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
1811 -- CE_AUTO_BANK_MATCH.csh_statement_date := X_statement_header_date;
1812 CE_AUTO_BANK_MATCH.aba_bank_currency := X_bank_currency;
1813 CE_AUTO_BANK_REC.G_gl_date := gl_date;
1814 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
1815 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
1816 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_date;
1817 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
1818 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
1819 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
1820 CE_AUTO_BANK_MATCH.csl_reconcile_flag := receipt_type;
1821 CE_AUTO_BANK_MATCH.csl_match_type := tx_type;
1822 CE_AUTO_BANK_MATCH.csl_amount := amount_cleared;
1823 CE_AUTO_BANK_MATCH.csl_trx_type := tx_type;
1824 CE_AUTO_BANK_MATCH.trx_id := trx_id;
1825 CE_AUTO_BANK_MATCH.trx_status := trx_status;
1826 CE_AUTO_BANK_MATCH.csl_currency_code := X_currency_code;
1827 -- CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
1828 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
1829 CE_AUTO_BANK_MATCH.reversed_receipt_flag := X_reversed_receipt_flag;
1830 IF (passin_mode = 'MANUAL_C' ) THEN
1831 CE_AUTO_BANK_MATCH.csl_trx_date := sysdate;
1832 END IF;
1833 END IF;
1834 IF l_DEBUG in ('Y', 'C') THEN
1835 cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ='|| CE_AUTO_BANK_MATCH.csl_trx_type ||
1836 ', CE_AUTO_BANK_MATCH.reversed_receipt_flag = ' ||CE_AUTO_BANK_MATCH.reversed_receipt_flag);
1837 cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_correction_type =' || CE_AUTO_BANK_MATCH.csl_match_correction_type ||
1838 ', CE_AUTO_BANK_MATCH.reconciled_this_run = '|| CE_AUTO_BANK_MATCH.reconciled_this_run);
1839 END IF;
1840
1841
1842 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
1843 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
1844 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
1845 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
1846 ELSE
1847 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
1848 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
1849 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
1850 END IF;
1851 if (passin_mode = 'AUTO') then
1852 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT' AND
1853 CE_AUTO_BANK_MATCH.reconciled_this_run is NULL) then
1854 if (CE_AUTO_BANK_MATCH.trx_match_type IN ('CASH', 'MISC')) then
1855 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
1856 --
1857 -- when reconcile the original receipt which has been reversed
1858 -- only perform the reconciliation process and skip the call to
1859 -- any AR packages
1860 --
1861 IF (CE_AUTO_BANK_MATCH.reversed_receipt_flag = 'Y'
1862 OR (trx_status IN ('RISK_ELIMINATED', 'CLEARED'))) THEN
1863 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
1864 --
1865 -- bug 922650
1866 -- update actual_value_date in AR_CASH_RECEIPS table.
1867 --
1868 IF l_DEBUG in ('Y', 'C') THEN
1869 cep_standard.debug('call ARP_CASHBOOK.update_actual_value_date');
1870 END IF;
1871
1872 ARP_CASHBOOK.update_actual_value_date(to_number(ar_cash_receipt_id),
1873 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
1874 ELSE
1875 IF l_DEBUG in ('Y', 'C') THEN
1876 cep_standard.debug('reconcile_stmt: ' || 'RECEIPT: amount_cleared = '|| to_char(amount_cleared)||
1877 'RECEIPT: charges_amount = '|| to_char(charges_amount));
1878 END IF;
1879 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT') then
1880 clearing_sign := -1;
1881 else
1882 clearing_sign := 1;
1883 end if;
1884 cleared_trx_type := 'RECEIPT';
1885 IF l_DEBUG in ('Y', 'C') THEN
1886 cep_standard.debug('call ARP_CASHBOOK.clear');
1887 END IF;
1888 ARP_CASHBOOK.clear(
1889 p_cr_id => to_number(ar_cash_receipt_id),
1890 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1891 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1892 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1893 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1894 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1895 p_exchange_rate => CE_AUTO_BANK_CLEAR.G_exchange_rate,
1896 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
1897 p_amount_cleared => amount_cleared * clearing_sign,
1898 p_amount_factored => charges_amount,
1899 p_module_name => 'CE_AUTO_BANK_REC',
1900 p_module_version => '1.0',
1901 p_crh_id => cleared_trx_id );
1902 END IF;
1903 elsif (CE_AUTO_BANK_MATCH.trx_match_type = 'PAYMENT') then
1904 IF( trx_status NOT IN ( 'STOP INITIATED', 'VOIDED' )) THEN
1905 IF l_DEBUG in ('Y', 'C') THEN
1906 cep_standard.debug('reconcile_stmt: ' || 'PAYMENT: amount_cleared = '|| to_char(amount_cleared)||
1907 'PAYMENT: charges_amount = '|| to_char(charges_amount));
1908 END IF;
1909 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
1910 clearing_sign := -1;
1911 else
1912 clearing_sign := 1;
1913 end if;
1914 if (passin_mode = 'AUTO') then
1915 if (CE_AUTO_BANK_MATCH.trx_gl_date is not null) then
1916 CE_AUTO_BANK_REC.G_gl_date := CE_AUTO_BANK_MATCH.trx_gl_date;
1917 end if;
1918 if (CE_AUTO_BANK_MATCH.trx_cleared_date is not null) then
1919 CE_AUTO_BANK_MATCH.csl_trx_date :=
1920 CE_AUTO_BANK_MATCH.trx_cleared_date;
1921 end if;
1922 end if;
1923
1924 IF l_DEBUG in ('Y', 'C') THEN
1925 cep_standard.debug('reconcile_stmt: ' || '>>>AP_RECONCILIATION_PKG.recon_payment_history'||
1926 'X_CHECK_ID: '||to_char(CE_AUTO_BANK_MATCH.trx_id)||
1927 'X_ACCOUNTING_DATE: '||CE_AUTO_BANK_REC.G_gl_date);
1928 cep_standard.debug('X_CLEARED_DATE: '||CE_AUTO_BANK_MATCH.csl_trx_date||
1929 'X_TRANSACTION_AMOUNT: '||to_char(amount_cleared*clearing_sign)||
1930 'X_ERROR_AMOUNT: '||to_char(errors_amount)||
1931 'X_CHARGE_AMOUNT: '||to_char(charges_amount));
1932 cep_standard.debug('X_CURRENCY_CODE: '||X_currency_code||
1933 'X_EXCHANGE_RATE_TYPE: '||CE_AUTO_BANK_CLEAR.G_exchange_rate_type||
1934 'X_EXCHANGE_RATE_DATE: '||CE_AUTO_BANK_CLEAR.G_exchange_date);
1935 cep_standard.debug('X_EXCHANGE_RATE: '||to_char(CE_AUTO_BANK_CLEAR.G_exchange_rate)||
1936 'X_ACTUAL_VALUE_DATE: '||CE_AUTO_BANK_MATCH.csl_effective_date);
1937 END IF;
1938
1939 IF l_DEBUG in ('Y', 'C') THEN
1940 cep_standard.debug('call AP_RECONCILIATION_PKG.recon_payment_history');
1941 END IF;
1942
1943 AP_RECONCILIATION_PKG.recon_payment_history(
1944 X_CHECKRUN_ID => to_number(NULL),
1945 X_CHECK_ID => CE_AUTO_BANK_MATCH.trx_id,
1946 X_ACCOUNTING_DATE => CE_AUTO_BANK_REC.G_gl_date,
1947 X_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1948 X_TRANSACTION_AMOUNT => NVL(amount_cleared,0) * clearing_sign,
1949 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
1950 X_ERROR_AMOUNT => NVL(errors_amount,0),
1951 X_CHARGE_AMOUNT => NVL(charges_amount,0),
1952 X_CURRENCY_CODE => X_bank_currency,
1953 X_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
1954 X_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1955 X_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
1956 X_MATCHED_FLAG => clearing_flag,
1957 X_ACTUAL_VALUE_DATE =>
1958 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1959 X_LAST_UPDATE_DATE => sysdate,
1960 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
1961 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
1962 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
1963 X_CREATION_DATE => sysdate,
1964 X_PROGRAM_UPDATE_DATE => sysdate,
1965 X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
1966 X_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
1967 X_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
1968 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_stmt '
1969 );
1970 IF l_DEBUG in ('Y', 'C') THEN
1971 cep_standard.debug('reconcile_stmt: ' || '<<<AP_RECONCILIATION_PKG.recon_payment_history');
1972 END IF;
1973 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
1974 cleared_trx_type := 'PAYMENT';
1975 END IF;
1976 elsif (CE_AUTO_BANK_MATCH.trx_match_type = 'PAY_LINE') then
1977 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
1978 clearing_sign := -1;
1979 else
1980 clearing_sign := 1;
1981 end if;
1982 IF l_DEBUG in ('Y', 'C') THEN
1983 cep_standard.debug('call PAY_CE_RECONCILIATION_PKG.reconcile_payment');
1984 END IF;
1985
1986
1987 PAY_CE_RECONCILIATION_PKG.reconcile_payment (
1988 p_payment_id => CE_AUTO_BANK_MATCH.trx_id,
1989 p_cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1990 p_trx_amount => NVL(amount_cleared,0) * clearing_sign,
1991 p_trx_type => 'PAY',
1992 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
1993 p_last_update_login => NVL(FND_GLOBAL.user_id,-1),
1994 p_created_by => NVL(FND_GLOBAL.user_id,-1) );
1995 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
1996 cleared_trx_type := 'PAY';
1997 end if;
1998 end if;
1999 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'REVERSAL') then
2000 x_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2001 x_trx_amount := CE_AUTO_BANK_MATCH.trx_amount;
2002 x_trx_amount2 := CE_AUTO_BANK_MATCH.csl_amount;
2003 else
2004 x_trx_id := CE_AUTO_BANK_MATCH.trx_id2;
2005 x_trx_amount := CE_AUTO_BANK_MATCH.trx_amount2;
2006 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
2007 if (CE_AUTO_BANK_MATCH.trx_type2 in
2008 ('CREDIT', 'MISC_CREDIT')) then
2009 x_trx_amount2 := - x_trx_amount;
2010 else
2011 x_trx_amount2 := x_trx_amount;
2012 end if;
2013 else /* CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT' */
2014 if (CE_AUTO_BANK_MATCH.trx_type2 in
2015 ('DEBIT', 'MISC_DEBIT')) then
2016 x_trx_amount2 := - x_trx_amount;
2017 else
2018 x_trx_amount2 := x_trx_amount;
2019 end if;
2020 end if;
2021 IF l_DEBUG in ('Y', 'C') THEN
2022 cep_standard.debug('reconcile_stmt: ' || 'DEBUG: trx_type2 = '|| CE_AUTO_BANK_MATCH.trx_type2||
2023 ' x_trx_amount = '|| x_trx_amount||' x_trx_amount2 = '|| x_trx_amount2);
2024 END IF;
2025 end if;
2026 -- Need to calculate cleared amount, x_amount_cleared, here.
2027 IF l_DEBUG in ('Y', 'C') THEN
2028 cep_standard.debug('update ce_statement_reconcils_all');
2029 END IF;
2030 --update ce_statement_reconciliations
2031 update ce_statement_reconcils_all
2032 set current_record_flag = 'N'
2033 where statement_line_id in
2034 (x_trx_id, CE_AUTO_BANK_MATCH.csl_statement_line_id)
2035 and reference_type = tx_type
2036 and nvl(current_record_flag, 'N') = 'Y'
2037 and nvl(request_id, -999) <> nvl(FND_GLOBAL.conc_request_id,-1);
2038
2039 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2040 Y_statement_line_id => x_trx_id,
2041 Y_cleared_trx_type => 'STATEMENT',
2042 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2043 Y_ar_cash_receipt_id => to_number(NULL),
2044 Y_reference_status => reference_status,
2045 Y_auto_reconciled_flag => auto_reconciled_flag,
2046 Y_status_flag => 'M',
2047 Y_amount => x_trx_amount );
2048
2049 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2050 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2051 Y_cleared_trx_type => 'STATEMENT',
2052 Y_cleared_trx_id => x_trx_id,
2053 Y_ar_cash_receipt_id => to_number(NULL),
2054 Y_reference_status => reference_status,
2055 Y_auto_reconciled_flag => auto_reconciled_flag,
2056 Y_status_flag => 'M',
2057 Y_amount => x_trx_amount2);
2058
2059 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT') then
2060
2061 -- bug 3252844 reconciled amount is duplicated because the
2062 -- the reconciled PAYMENT amount is negative on
2063 -- CE_STATEMENT_RECONCILS_ALL
2064
2065 x_trx_amount3 := CE_AUTO_BANK_MATCH.corr_csl_amount;
2066
2067 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
2068 if (CE_AUTO_BANK_MATCH.trx_match_type = 'PAYMENT') then
2069 x_trx_amount3 :=
2070 -1 * CE_AUTO_BANK_MATCH.corr_csl_amount;
2071 end if;
2072 /*elsif (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT') then
2073 if (CE_AUTO_BANK_MATCH.trx_match_type = 'CASH') then
2074 x_trx_amount3 :=
2075 -1 * CE_AUTO_BANK_MATCH.corr_csl_amount;
2076 end if;*/
2077 end if;
2078
2079 IF l_DEBUG in ('Y', 'C') THEN
2080 cep_standard.debug('reconcile_stmt: DEBUG: x_trx_amount3 = '|| x_trx_amount3
2081 ||', CE_AUTO_BANK_MATCH.csl_trx_type: ' ||CE_AUTO_BANK_MATCH.csl_trx_type
2082 ||', CE_AUTO_BANK_MATCH.trx_match_type: ' ||CE_AUTO_BANK_MATCH.trx_match_type);
2083 END IF;
2084
2085 if (CE_AUTO_BANK_MATCH.reconciled_this_run is NULL) then
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 => cleared_trx_type,
2089 Y_cleared_trx_id => cleared_trx_id,
2090 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2091 Y_reference_status => reference_status,
2092 Y_auto_reconciled_flag => auto_reconciled_flag,
2093 Y_status_flag => 'M',
2094 Y_amount => x_trx_amount3);
2095 --Y_amount => CE_AUTO_BANK_MATCH.corr_csl_amount);
2096 else /* CE_AUTO_BANK_MATCH.reconciled_this_run = 'Y' */
2097 IF l_DEBUG in ('Y', 'C') THEN
2098 cep_standard.debug('update ce_statement_reconcils_all');
2099 END IF;
2100 --update ce_statement_reconciliations
2101 update ce_statement_reconcils_all
2102 set statement_line_Id = CE_AUTO_BANK_MATCH.csl_statement_line_id,
2103 amount = x_trx_amount3
2104 --amount = CE_AUTO_BANK_MATCH.corr_csl_amount
2105 where statement_line_id = x_trx_id
2106 and reference_type <> 'STATEMENT'
2107 and reference_id = CE_AUTO_BANK_MATCH.trx_id
2108 and nvl(request_id,-999) = nvl(FND_GLOBAL.conc_request_id,-1);
2109 end if;
2110 end if;
2111 elsif (passin_mode <> 'MANUAL_C') then
2112 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := 'Y';
2113
2114 -- bug# 1189554
2115 x_trx_amount2 := CE_AUTO_BANK_MATCH.csl_amount;
2116
2117 if (X_statement_line_type = 'MISC_CREDIT') then
2118 if (tx_type in ('CREDIT', 'MISC_CREDIT')) then
2119 x_trx_amount2 :=
2120 -1 * CE_AUTO_BANK_MATCH.csl_amount;
2121 end if;
2122 else /* X_statement_line_type = 'MISC_DEBIT' */
2123 if (tx_type in ('DEBIT', 'MISC_DEBIT')) then
2124 x_trx_amount2 :=
2125 -1 * CE_AUTO_BANK_MATCH.csl_amount;
2126 end if;
2127 end if;
2128
2129 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2130 Y_statement_line_id => CE_AUTO_BANK_MATCH.trx_id,
2131 Y_cleared_trx_type => 'STATEMENT',
2132 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2133 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2134 Y_reference_status => reference_status,
2135 Y_auto_reconciled_flag => auto_reconciled_flag,
2136 Y_status_flag => 'M',
2137 Y_amount => CE_AUTO_BANK_MATCH.csl_amount);
2138
2139 CE_AUTO_BANK_CLEAR.update_line_status(
2140 CE_AUTO_BANK_MATCH.trx_id,'RECONCILED');
2141
2142 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2143 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2144 Y_cleared_trx_type => 'STATEMENT',
2145 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.trx_id,
2146 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2147 Y_reference_status => reference_status,
2148 Y_auto_reconciled_flag => auto_reconciled_flag,
2149 Y_status_flag => 'M',
2150 Y_amount => x_trx_amount2);
2151
2152 CE_AUTO_BANK_CLEAR.update_line_status(
2153 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'RECONCILED');
2154 end if;
2155 IF l_DEBUG in ('Y', 'C') THEN
2156 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_stmt');
2157 END IF;
2158 END reconcile_stmt;
2159
2160 /* ---------------------------------------------------------------------
2161 | PRIVATE PROCEDURE |
2162 | reconcile_trx |
2163 | CALLED BY |
2164 | reconcile_process |
2165 --------------------------------------------------------------------- */
2166 PROCEDURE reconcile_trx( passin_mode VARCHAR2,
2167 tx_type VARCHAR2,
2168 trx_id NUMBER,
2169 trx_status VARCHAR2,
2170 receipt_type VARCHAR2,
2171 exchange_rate_type VARCHAR2,
2172 exchange_date DATE,
2173 exchange_rate NUMBER,
2174 amount_cleared NUMBER,
2175 charges_amount NUMBER,
2176 errors_amount NUMBER,
2177 gl_date DATE,
2178 value_date DATE,
2179 cleared_date DATE,
2180 ar_cash_receipt_id NUMBER,
2181 X_bank_currency VARCHAR2,
2182 X_statement_line_id IN OUT NOCOPY NUMBER,
2183 X_statement_line_type VARCHAR2,
2184 reference_status VARCHAR2,
2185 trx_currency_type VARCHAR2,
2186 auto_reconciled_flag VARCHAR2,
2187 X_statement_header_id IN OUT NOCOPY NUMBER,
2188 X_statement_header_date DATE,
2189 X_bank_trx_number VARCHAR2,
2190 X_currency_code VARCHAR2,
2191 X_original_amount NUMBER,
2192 X_effective_date DATE,
2193 X_float_handling_flag VARCHAR2,
2194 X_reversed_receipt_flag VARCHAR2,
2195 X_org_id NUMBER DEFAULT NULL,
2196 X_legal_entity_id NUMBER DEFAULT NULL) IS
2197 cleared_trx_type CE_LOOKUPS.lookup_code%TYPE;
2198 cleared_trx_id CE_STATEMENT_RECONCILS_ALL.reference_id%TYPE;
2199 clearing_flag VARCHAR2(1) := 'N';
2200 X_RESULT VARCHAR2(100);
2201 X_RECONCILED_METHOD VARCHAR2(1);
2202 current_org_id NUMBER;
2203 BEGIN
2204 IF l_DEBUG in ('Y', 'C') THEN
2205 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.reconcile_trx '||
2206 ', CE_AUTO_BANK_CLEAR1.csl_reconcile_flag = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
2207 END IF;
2208
2209 IF (X_org_id is not null or X_legal_entity_id is not null) THEN
2210 --CE_AUTO_BANK_REC.G_org_id := X_org_id;
2211 --CE_AUTO_BANK_REC.G_legal_entity_id := X_legal_entity_id;
2212 CE_AUTO_BANK_CLEAR.G_org_id := X_org_id;
2213 CE_AUTO_BANK_CLEAR.G_legal_entity_id := X_legal_entity_id;
2214 END IF;
2215
2216 --fnd_message.debug('CE_AUTO_BANK_REC.G_org_id = '||CE_AUTO_BANK_REC.G_org_id);
2217
2218 IF (passin_mode <> 'MANUAL_C') THEN
2219 clearing_flag := 'Y';
2220 CE_AUTO_BANK_CLEAR1.validate_effective_date( passin_mode,
2221 X_effective_date,
2222 X_float_handling_flag);
2223 END IF;
2224 IF (passin_mode IN ('MANUAL_H','MANUAL_L','MANUAL_C')) THEN
2225 IF (X_statement_line_id IS NULL AND passin_mode <> 'MANUAL_C') THEN
2226 OPEN C_STATEMENT_LINE_SEQ;
2227 FETCH C_STATEMENT_LINE_SEQ INTO X_statement_line_id;
2228 CLOSE C_STATEMENT_LINE_SEQ;
2229 END IF;
2230 CE_AUTO_BANK_MATCH.csl_trx_type := tx_type;
2231 CE_AUTO_BANK_MATCH.csh_statement_header_id := X_statement_header_id;
2232 CE_AUTO_BANK_MATCH.csh_statement_date := X_statement_header_date;
2233 CE_AUTO_BANK_MATCH.aba_bank_currency := X_bank_currency;
2234 CE_AUTO_BANK_REC.G_gl_date := gl_date;
2235 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := exchange_rate_type;
2236 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := exchange_date;
2237 CE_AUTO_BANK_MATCH.csl_exchange_rate := exchange_rate;
2238 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
2239 CE_AUTO_BANK_MATCH.csl_effective_date := value_date;
2240 CE_AUTO_BANK_MATCH.csl_statement_line_id := X_statement_line_id;
2241 CE_AUTO_BANK_MATCH.csl_reconcile_flag := receipt_type;
2242 CE_AUTO_BANK_MATCH.csl_match_type := tx_type;
2243 CE_AUTO_BANK_MATCH.csl_amount := amount_cleared;
2244 CE_AUTO_BANK_MATCH.trx_id := trx_id;
2245 CE_AUTO_BANK_MATCH.trx_status := trx_status;
2246 CE_AUTO_BANK_MATCH.csl_currency_code := X_currency_code;
2247 CE_AUTO_BANK_MATCH.csl_original_amount := X_original_amount;
2248 CE_AUTO_BANK_MATCH.csl_charges_amount := charges_amount;
2249 CE_AUTO_BANK_MATCH.csl_bank_trx_number := X_bank_trx_number;
2250 CE_AUTO_BANK_MATCH.reversed_receipt_flag := X_reversed_receipt_flag;
2251 IF (passin_mode = 'MANUAL_H' ) THEN
2252 CE_AUTO_BANK_CLEAR1.create_statement_line;
2253 IF(X_statement_header_id IS NULL)THEN
2254 X_statement_header_id := CE_AUTO_BANK_MATCH.csh_statement_header_id;
2255 END IF;
2256 END IF;
2257 IF (passin_mode = 'MANUAL_C' ) THEN
2258 CE_AUTO_BANK_MATCH.csl_trx_date := cleared_date;
2259 END IF;
2260 END IF;
2261 IF (trx_currency_type IN ('FOREIGN','BANK')) THEN
2262 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := CE_AUTO_BANK_MATCH.csl_exchange_rate_type;
2263 CE_AUTO_BANK_CLEAR.G_exchange_date := CE_AUTO_BANK_MATCH.csl_exchange_rate_date;
2264 CE_AUTO_BANK_CLEAR.G_exchange_rate := CE_AUTO_BANK_MATCH.csl_exchange_rate;
2265 ELSE
2266 CE_AUTO_BANK_CLEAR.G_exchange_rate_type := NULL;
2267 CE_AUTO_BANK_CLEAR.G_exchange_date := NULL;
2268 CE_AUTO_BANK_CLEAR.G_exchange_rate := NULL;
2269 END IF;
2270 IF(CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI' and
2271 CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'XTR_LINE') THEN
2272 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'XTR_LINE';
2273 END IF;
2274 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
2275 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'CASHFLOW';
2276 END IF;
2277 IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI') THEN
2278 CE_999_PKG.clear(
2279 X_trx_id => CE_AUTO_BANK_MATCH.trx_id,
2280 X_trx_type => CE_AUTO_BANK_MATCH.csl_match_type,
2281 X_status => nvl(CE_AUTO_BANK_REC.G_open_interface_clear_status,
2282 CE_AUTO_BANK_MATCH.trx_status),
2283 X_trx_number => CE_AUTO_BANK_MATCH.csl_bank_trx_number,
2284 X_trx_date => CE_AUTO_BANK_MATCH.csl_trx_date,
2285 X_trx_currency => CE_AUTO_BANK_MATCH.csl_currency_code,
2286 X_gl_date => CE_AUTO_BANK_REC.G_gl_date,
2287 X_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
2288 X_cleared_amount => NVL(amount_cleared,0),
2289 X_cleared_date => CE_AUTO_BANK_MATCH.csl_trx_date,
2290 X_charges_amount => charges_amount,
2291 X_errors_amount => errors_amount,
2292 X_exchange_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
2293 X_exchange_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
2294 X_exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate);
2295 cleared_trx_type := 'ROI_LINE';
2296 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2297 ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'XTR_LINE') THEN
2298 X_RECONCILED_METHOD := substr(passin_mode,1,1);
2299 XTR_WRAPPER_API_P.reconciliation(
2300 P_SETTLEMENT_SUMMARY_ID => CE_AUTO_BANK_MATCH.trx_id,
2301 P_TASK => 'REC',
2302 P_RECONCILED_METHOD => X_RECONCILED_METHOD,
2303 P_RESULT => X_RESULT);
2304 if (X_RESULT <> 'XTR2_SUCCESS') then
2305 FND_MESSAGE.set_name( 'CE','CE_XTR_UPDATE_FAILED');
2306 RAISE APP_EXCEPTION.application_exception;
2307 end if;
2308 cleared_trx_type := 'XTR_LINE';
2309 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2310 ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CASHFLOW') THEN
2311
2312 CE_CASHFLOW_PKG.CLEAR_CASHFLOW(
2313 X_CASHFLOW_ID => CE_AUTO_BANK_MATCH.trx_id,
2314 X_TRX_STATUS => CE_AUTO_BANK_MATCH.trx_status,
2315 x_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2316 X_ACCOUNTING_DATE => CE_AUTO_BANK_REC.G_gl_date,
2317 X_CLEARED_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2318 X_CLEARED_AMOUNT => NVL(amount_cleared,0),
2319 X_CLEARED_ERROR_AMOUNT => NVL(errors_amount,0),
2320 X_CLEARED_CHARGE_AMOUNT => NVL(charges_amount,0),
2321 X_CLEARED_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2322 X_CLEARED_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2323 X_CLEARED_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2324 X_PASSIN_MODE => passin_mode,
2325 x_statement_line_id => x_statement_line_id,
2326 x_statement_line_type => x_statement_line_type
2327 ) ;
2328 cleared_trx_type := 'CASHFLOW';
2329 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2330 ELSIF( CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE' ) THEN
2331 cleared_trx_type := 'JE_LINE';
2332 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2333 ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag IN ('PAY', 'PAY_EFT')) THEN
2334 --
2335 -- NVL(X_statement_line_type) is for cases where we create the statement line
2336 -- here (MANUAL_H) and we know that the created statement line
2337 -- is always fine
2338 --
2339 IF ((NVL(X_statement_line_type,'XXX') = 'STOP' AND trx_status <> 'V') OR
2340 (NVL(X_statement_line_type,'STOP') <> 'STOP' AND trx_status = 'V')) THEN
2341 FND_MESSAGE.set_name( 'CE', 'CE_STOP_VOID');
2342 RAISE APP_EXCEPTION.application_exception;
2343 ELSE
2344 IF l_DEBUG in ('Y', 'C') THEN
2345 cep_standard.debug('>>> Calling PAY_CE_RECONCILIATION_PKG.reconcile_payment'||
2346 ' p_payment_id = '|| to_char(CE_AUTO_BANK_MATCH.trx_id)||
2347 ' p_cleared_date = '|| to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
2348 cep_standard.debug(' p_trx_amount = '|| to_char(amount_cleared)||
2349 ' p_trx_type = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
2350 END IF;
2351
2352 PAY_CE_RECONCILIATION_PKG.reconcile_payment (
2353 p_payment_id => CE_AUTO_BANK_MATCH.trx_id,
2354 p_cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2355 p_trx_amount => NVL(amount_cleared,0),
2356 p_trx_type => CE_AUTO_BANK_MATCH.csl_reconcile_flag,
2357 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
2358 p_last_update_login => NVL(FND_GLOBAL.user_id,-1),
2359 p_created_by => NVL(FND_GLOBAL.user_id,-1) );
2360
2361 IF l_DEBUG in ('Y', 'C') THEN
2362 cep_standard.debug('reconcile_trx: ' || '<<< End PAY_CE_RECONCILIATION_PKG.reconcile_payment');
2363 END IF;
2364 END IF;
2365 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2366 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_reconcile_flag;
2367 ELSE
2368 IF (CE_AUTO_BANK_MATCH.csl_match_type = 'PAYMENT') THEN
2369 --
2370 -- NVL(X_statement_line_type) is for cases where we create the statement line
2371 -- here (MANUAL_H) and we know that the created statement line
2372 -- is always fine
2373 --
2374 IF ((NVL(X_statement_line_type,'XXX') = 'STOP' AND trx_status NOT IN ('STOP INITIATED', 'VOIDED')) OR
2375 (NVL(X_statement_line_type,'STOP') <> 'STOP' AND trx_status IN ( 'STOP INITIATED', 'VOIDED'))) THEN
2376 if (trx_status = 'STOP INITIATED') then
2377 FND_MESSAGE.set_name( 'CE', 'CE_ABR_PYMT_STOPPED' );
2378 RAISE APP_EXCEPTION.application_exception;
2379 elsif (trx_status = 'VOIDED') then
2380 FND_MESSAGE.set_name( 'CE', 'CE_ABR_PYMT_VOIDED' );
2381 RAISE APP_EXCEPTION.application_exception;
2382 else
2383 FND_MESSAGE.set_name( 'CE', 'CE_PAYMENT_STOPPED' );
2384 RAISE APP_EXCEPTION.application_exception;
2385 end if;
2386 ELSIF( trx_status NOT IN ( 'STOP INITIATED', 'VOIDED' )) THEN
2387 if (passin_mode = 'AUTO') then
2388 /* bug 2260411
2389 if (CE_AUTO_BANK_MATCH.trx_gl_date is not null) then
2390 CE_AUTO_BANK_REC.G_gl_date := CE_AUTO_BANK_MATCH.trx_gl_date;
2391 end if;
2392 */
2393 if (CE_AUTO_BANK_MATCH.trx_cleared_date is not null) then
2394 CE_AUTO_BANK_MATCH.csl_trx_date :=
2395 CE_AUTO_BANK_MATCH.trx_cleared_date;
2396 end if;
2397 end if;
2398
2399 IF l_DEBUG in ('Y', 'C') THEN
2400 cep_standard.debug('X_CHECK_ID: '||to_char(CE_AUTO_BANK_MATCH.trx_id)||
2401 ' X_ACCOUNTING_DATE: '||CE_AUTO_BANK_REC.G_gl_date);
2402 cep_standard.debug('X_CLEARED_DATE: '||CE_AUTO_BANK_MATCH.csl_trx_date||
2403 ' X_TRANSACTION_AMOUNT: '||to_char(amount_cleared));
2404 cep_standard.debug('X_ERROR_AMOUNT: '||to_char(errors_amount)||
2405 ' X_CHARGE_AMOUNT: '||to_char(charges_amount));
2406 cep_standard.debug('X_CURRENCY_CODE: '||X_currency_code||
2407 ' X_EXCHANGE_RATE_TYPE: '||CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
2408 cep_standard.debug('X_EXCHANGE_RATE_DATE: '||CE_AUTO_BANK_CLEAR.G_exchange_date||
2409 ' X_EXCHANGE_RATE: '||to_char(CE_AUTO_BANK_CLEAR.G_exchange_rate));
2410 cep_standard.debug('X_ACTUAL_VALUE_DATE: '||CE_AUTO_BANK_MATCH.csl_effective_date);
2411 cep_standard.debug('reconcile_trx >>>AP_RECONCILIATION_PKG.recon_payment_history');
2412 END IF;
2413
2414 AP_RECONCILIATION_PKG.recon_payment_history(
2415 X_CHECKRUN_ID => to_number(NULL),
2416 X_CHECK_ID => to_number(CE_AUTO_BANK_MATCH.trx_id),
2417 X_ACCOUNTING_DATE => CE_AUTO_BANK_REC.G_gl_date,
2418 X_CLEARED_DATE => CE_AUTO_BANK_MATCH.csl_trx_date,
2419 X_TRANSACTION_AMOUNT => NVL(amount_cleared,0),
2420 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
2421 X_ERROR_AMOUNT => NVL(errors_amount,0),
2422 X_CHARGE_AMOUNT => NVL(charges_amount,0),
2423 X_CURRENCY_CODE => X_bank_currency,
2424 X_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2425 X_EXCHANGE_RATE_DATE => CE_AUTO_BANK_CLEAR.G_exchange_date,
2426 X_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2427 X_MATCHED_FLAG => clearing_flag,
2428 X_ACTUAL_VALUE_DATE =>
2429 CE_AUTO_BANK_MATCH.csl_effective_date,
2430 X_LAST_UPDATE_DATE => sysdate,
2431 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
2432 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
2433 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
2434 X_CREATION_DATE => sysdate,
2435 X_PROGRAM_UPDATE_DATE => sysdate,
2436 X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
2437 X_PROGRAM_ID => NVL(FND_GLOBAL.conc_program_id,-1),
2438 X_REQUEST_ID => NVL(FND_GLOBAL.conc_request_id,-1),
2439 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.reconcile_trx '
2440 );
2441
2442 IF l_DEBUG in ('Y', 'C') THEN
2443 cep_standard.debug('reconcile_trx: ' || '<<<AP_RECONCILIATION_PKG.recon_payment_history');
2444 END IF;
2445 END IF;
2446 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2447 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2448 ELSE -- Receipt
2449 IF ((NVL(X_statement_line_type,'NSF') NOT IN ('NSF','REJECTED') AND
2450 trx_status in ('REVERSED','DM REVERSED'))) THEN
2451 FND_MESSAGE.set_name('CE', 'CE_STATEMENT_REVERSAL_NSF');
2452 RAISE APP_EXCEPTION.application_exception;
2453 ELSE
2454 IF (passin_mode = 'AUTO' AND
2455 CE_AUTO_BANK_MATCH.csl_trx_type IN ('NSF','REJECTED') AND
2456 CE_AUTO_BANK_REC.G_nsf_handling = 'REVERSE' AND
2457 CE_AUTO_BANK_MATCH.trx_status <> 'REVERSED') THEN
2458 ARP_CASHBOOK.reverse(
2459 p_cr_id => ar_cash_receipt_id,
2460 p_reversal_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2461 p_reversal_date => sysdate,
2462 p_reversal_comments => NULL,
2463 p_reversal_reason_code => NULL,
2464 p_reversal_category => NULL,
2465 p_module_name => 'CE_AUTO_BANK_REC',
2466 p_module_version => '1.0',
2467 p_crh_id => cleared_trx_id);
2468 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2469 ELSIF (passin_mode = 'AUTO' AND
2470 CE_AUTO_BANK_MATCH.csl_trx_type IN ('NSF','REJECTED') AND
2471 CE_AUTO_BANK_REC.G_nsf_handling = 'DM REVERSE' AND
2472 CE_AUTO_BANK_MATCH.trx_status <> 'REVERSED') THEN
2473
2474 IF (CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' AND
2475 CE_AUTO_BANK_MATCH.trx_customer_id is not null) THEN
2476 declare
2477 cc_id ra_cust_trx_types.gl_id_rec%type;
2478 cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%type;
2479 cust_trx_type ra_cust_trx_types.name%type;
2480 l_dbseqname varchar2(30);
2481 l_doc_seq_id NUMBER;
2482 l_doc_seq_value NUMBER;
2483 l_valid_seq BOOLEAN := TRUE;
2484 out_trx_number ar_payment_schedules_all.trx_number%TYPE;
2485 out_status varchar2(10);
2486
2487 begin
2488 select name, gl_id_rec, cust_trx_type_id
2489 into cust_trx_type, cc_id, cust_trx_type_id
2490 from ra_cust_trx_types
2491 where type = 'DM'
2492 and post_to_gl = 'Y'
2493 and accounting_affect_flag = 'Y'
2494 and tax_calculation_flag = 'N'
2495 and rownum = 1
2496 order by name, cust_trx_type_id;
2497
2498 l_valid_seq := CE_AUTO_BANK_IMPORT.get_sequence_info(
2499 222,
2500 cust_trx_type,
2501 CE_AUTO_BANK_REC.G_set_of_books_id,
2502 'A',
2503 CE_AUTO_BANK_MATCH.csl_trx_date,
2504 l_dbseqname,
2505 l_doc_seq_id,
2506 l_doc_seq_value );
2507
2508 if (l_valid_seq) then
2509 ARP_CASHBOOK.debit_memo_reversal
2510 ( p_cash_receipt_id => ar_cash_receipt_id,
2511 p_cc_id => cc_id,
2512 p_dm_cust_trx_type_id => cust_trx_type_id,
2513 p_dm_cust_trx_type => cust_trx_type,
2514 p_reversal_gl_date =>
2515 to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2516 p_reversal_date =>
2517 to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2518 p_reversal_category => 'NSF',
2519 p_reversal_reason_code => 'NSF',
2520 p_reversal_comments => 'test',
2521 p_dm_number => out_trx_number,
2522 p_dm_doc_sequence_value => l_doc_seq_value,
2523 p_dm_doc_sequence_id => l_doc_seq_id,
2524 p_tw_status => out_status,
2525 p_module_name => 'CE_AUTO_BANK_REC',
2526 p_module_version => '1.0');
2527
2528 if (out_status = 'OK') then
2529 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2530 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2531 end if;
2532 end if;
2533 exception
2534 when no_data_found then
2535 IF l_DEBUG in ('Y', 'C') THEN
2536 cep_standard.debug('reconcile_trx: ' || '<<CE_AUTO_BANK_CLEAR1.reconcile_trx calls debit_memo_reversal'||
2537 'NO DATA FOUND');
2538 END IF;
2539 raise;
2540 when others then
2541 IF l_DEBUG in ('Y', 'C') THEN
2542 cep_standard.debug('reconcile_trx: ' ||
2543 '<<CE_AUTO_BANK_CLEAR1.reconcile_trx calls debit_memo_reversal');
2544 END IF;
2545 raise;
2546 end;
2547 END IF;
2548
2549 ELSE
2550 IF (reference_status <> 'REVERSED' AND NOT
2551 CE_AUTO_BANK_CLEAR1.check_matching_status(ar_cash_receipt_id,
2552 reference_status)) THEN
2553 FND_MESSAGE.set_name('CE','CE_MATCHED_AR_ENTRY');
2554 RAISE APP_EXCEPTION.application_exception;
2555 ELSE
2556 IF l_DEBUG in ('Y', 'C') THEN
2557 cep_standard.debug('reconcile_trx: ' || ' amount_cleared = '|| amount_cleared||
2558 ' charges_amount = '|| charges_amount|| ' trx_status = '|| trx_status);
2559 END IF;
2560 cleared_trx_type := CE_AUTO_BANK_MATCH.csl_match_type;
2561 --
2562 -- when reconcile the original receipt which has been reversed
2563 -- only perform the reconciliation process and skip the call to
2564 -- any AR packages
2565 --
2566 IF (CE_AUTO_BANK_MATCH.reversed_receipt_flag = 'Y'
2567 OR (trx_status IN ('RISK_ELIMINATED', 'CLEARED'))
2568 OR (CE_AUTO_BANK_MATCH.csl_trx_type in ('NSF', 'REJECTED')) ) THEN
2569 cleared_trx_id := CE_AUTO_BANK_MATCH.trx_id;
2570 --
2571 -- bug 922650
2572 -- update actual_value_date in AR_CASH_RECEIPS table.
2573 --
2574 cep_standard.debug('reconcile_trx: call ARP_CASHBOOK.update_actual_value_date');
2575 ARP_CASHBOOK.update_actual_value_date(to_number(ar_cash_receipt_id),
2576 to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));
2577 ELSE
2578 IF l_DEBUG in ('Y', 'C') THEN
2579 cep_standard.debug('reconcile_trx: ' || ' ar_cash_receipt_id= '|| ar_cash_receipt_id ||
2580 ' CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date ||
2581 ' CE_AUTO_BANK_REC.G_gl_date= '|| CE_AUTO_BANK_REC.G_gl_date);
2582 cep_standard.debug(' CE_AUTO_BANK_MATCH.csl_effective_date= '|| CE_AUTO_BANK_MATCH.csl_effective_date ||
2583 ' CE_AUTO_BANK_CLEAR.G_exchange_date= '|| CE_AUTO_BANK_CLEAR.G_exchange_date ||
2584 ' CE_AUTO_BANK_CLEAR.G_exchange_rate_type= '|| CE_AUTO_BANK_CLEAR.G_exchange_rate_type);
2585 cep_standard.debug('CE_AUTO_BANK_CLEAR.G_exchange_rate= '|| CE_AUTO_BANK_CLEAR.G_exchange_rate ||
2586 'CE_AUTO_BANK_MATCH.aba_bank_currency= '|| CE_AUTO_BANK_MATCH.aba_bank_currency ||
2587 ' amount_cleared = '|| amount_cleared);
2588 cep_standard.debug('charges_amount = '|| charges_amount ||
2589 ' cleared_trx_id = '|| cleared_trx_id);
2590 END IF;
2591 cep_standard.debug('reconcile_trx: call ARP_CASHBOOK.clear ');
2592
2593 ARP_CASHBOOK.clear(
2594 p_cr_id => to_number(ar_cash_receipt_id),
2595 p_trx_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2596 p_gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2597 p_actual_value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2598 p_exchange_date => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2599 p_exchange_rate_type => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2600 p_exchange_rate => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2601 p_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
2602 p_amount_cleared => amount_cleared,
2603 p_amount_factored => charges_amount,
2604 p_module_name => 'CE_AUTO_BANK_REC',
2605 p_module_version => '1.0',
2606 p_crh_id => cleared_trx_id );
2607 cep_standard.debug('end call ARP_CASHBOOK.clear ');
2608 END IF;
2609 END IF;
2610 END IF;
2611 END IF;
2612 END IF;
2613 END IF;
2614
2615 IF (passin_mode <> 'MANUAL_C' ) THEN
2616
2617 IF l_DEBUG in ('Y', 'C') THEN
2618 cep_standard.debug('reconcile_trx: ' || 'cleared_trx_type='||cleared_trx_type||',cleared_trx_id='||
2619 to_char(cleared_trx_id)||',ar_cash_receipt_id='||to_char(ar_cash_receipt_id));
2620 cep_standard.debug('reconcile_trx: ' ||',reference_status='||reference_status||
2621 ',auto_reconciled_flag='||auto_reconciled_flag||',amount_cleared='|| to_char(amount_cleared));
2622 END IF;
2623
2624 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2625 Y_cleared_trx_type => cleared_trx_type,
2626 Y_cleared_trx_id => cleared_trx_id,
2627 Y_ar_cash_receipt_id => ar_cash_receipt_id,
2628 Y_reference_status => reference_status,
2629 Y_auto_reconciled_flag => auto_reconciled_flag,
2630 Y_status_flag => 'M',
2631 Y_amount => amount_cleared);
2632 END IF;
2633
2634 IF l_DEBUG in ('Y', 'C') THEN
2635 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.reconcile_trx');
2636 END IF;
2637
2638 END reconcile_trx;
2639
2640 /* ---------------------------------------------------------------------
2641 | PRIVATE PROCEDURE |
2642 | unclear_process |
2643 | DESCRIPTION |
2644 | Unclear and unreconcile the reconcilied statement line. |
2645 | CALLED BY |
2646 | This piece of code is called only from Manual Reconciliation |
2647 --------------------------------------------------------------------- */
2648 PROCEDURE unclear_process ( passin_mode VARCHAR2,
2649 X_header_or_line VARCHAR2,
2650 tx_type VARCHAR2,
2651 clearing_trx_type VARCHAR2,
2652 batch_id NUMBER,
2653 trx_id NUMBER,
2654 cash_receipt_id NUMBER,
2655 trx_date DATE,
2656 gl_date DATE,
2657 cash_receipt_history_id IN OUT NOCOPY NUMBER,
2658 stmt_line_id NUMBER,
2659 status VARCHAR2,
2660 cleared_date DATE,
2661 transaction_amount NUMBER,
2662 error_amount NUMBER,
2663 charge_amount NUMBER,
2664 currency_code VARCHAR2,
2665 xtype VARCHAR2,
2666 xdate DATE,
2667 xrate NUMBER,
2668 org_id NUMBER,
2669 legal_entity_id NUMBER ) IS
2670 value_date DATE := null;
2671 p_current_record_flag VARCHAR2(1) := null;
2672 current_org_id NUMBER;
2673
2674 X_RESULT VARCHAR2(100);
2675
2676 BEGIN
2677
2678 IF l_DEBUG in ('Y', 'C') THEN
2679 cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.unclear_process');
2680
2681 cep_standard.debug('passin_mode = ' ||passin_mode||' tx_type = '|| tx_type ||
2682 ', clearing_trx_type = ' ||clearing_trx_type || ', org_id = ' ||org_id ||
2683 ', legal_entity_id = ' ||legal_entity_id);
2684 cep_standard.debug('stmt_line_id = ' ||stmt_line_id || ', status = ' || status||
2685 ', currency_code = '||currency_code );
2686 cep_standard.debug('X_CHECKRUN_ID: '||to_char(batch_id)||', X_CHECK_ID: '||to_char(trx_id) ||
2687 ', cash_receipt_id ' ||cash_receipt_id ||
2688 ', trx_date ' ||trx_date ||
2689 ', cash_receipt_history_id '|| cash_receipt_history_id );
2690
2691 cep_standard.debug('X_ACCOUNTING_DATE: '||fnd_date.date_to_charDT(gl_date)||
2692 ', X_CLEARED_DATE: '||fnd_date.date_to_charDT(cleared_date)||
2693 ', X_TRANSACTION_AMOUNT: '||to_char(transaction_amount));
2694 cep_standard.debug('X_ERROR_AMOUNT: '||to_char(error_amount)||
2695 ', X_CHARGE_AMOUNT: '||to_char(charge_amount)||
2696 ', X_CURRENCY_CODE: '||currency_code);
2697 cep_standard.debug('X_EXCHANGE_RATE_TYPE: '||xtype||
2698 ', X_EXCHANGE_RATE_DATE: '||fnd_date.date_to_charDT(xdate));
2699 cep_standard.debug('X_EXCHANGE_RATE: '||to_char(xrate)||
2700 ', X_ACTUAL_VALUE_DATE: '||fnd_date.date_to_charDT(value_date));
2701 END IF;
2702
2703 --CE_AUTO_BANK_REC.G_org_id := org_id;
2704 --CE_AUTO_BANK_REC.G_legal_entity_id := legal_entity_id;
2705 CE_AUTO_BANK_CLEAR.G_org_id := org_id;
2706 CE_AUTO_BANK_CLEAR.G_legal_entity_id := legal_entity_id;
2707
2708 IF (org_id is not null) THEN
2709 select mo_global.GET_CURRENT_ORG_ID
2710 into current_org_id
2711 from dual;
2712
2713 IF l_DEBUG in ('Y', 'C') THEN
2714 cep_standard.debug('current_org_id =' ||current_org_id );
2715 END IF;
2716
2717 -- bug 3782741 set single org, since AR will not allow org_id to be passed
2718 IF (((current_org_id is null) or (org_id <> current_org_id )) AND
2719 (clearing_trx_type in ('CASH','MISC', 'PAYMENT'))) THEN
2720 mo_global.set_policy_context('S',org_id);
2721 cep_standard.debug('set current_org_id to ' ||org_id );
2722 END IF;
2723 END IF;
2724
2725 select mo_global.GET_CURRENT_ORG_ID
2726 into current_org_id
2727 from dual;
2728
2729 IF l_DEBUG in ('Y', 'C') THEN
2730 cep_standard.debug('current_org_id =' ||current_org_id );
2731 END IF;
2732
2733 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := NULL;
2734 IF (clearing_trx_type = 'PAYMENT') THEN
2735
2736 IF l_DEBUG in ('Y', 'C') THEN
2737 cep_standard.debug('>>>AP_RECONCILIATION_PKG.recon_payment_history');
2738 END IF;
2739
2740 /*Bug 3427050 added the following IF */
2741 IF (status NOT IN ('STOP INITIATED','VOIDED')) THEN
2742 AP_RECONCILIATION_PKG.recon_payment_history(
2743 X_CHECKRUN_ID => to_number(NULL),
2744 X_CHECK_ID => trx_id,
2745 X_ACCOUNTING_DATE => gl_date,
2746 X_CLEARED_DATE => cleared_date,
2747 X_TRANSACTION_AMOUNT => transaction_amount,
2748 X_TRANSACTION_TYPE => 'PAYMENT UNCLEARING',
2749 X_ERROR_AMOUNT => error_amount,
2750 X_CHARGE_AMOUNT => charge_amount,
2751 X_CURRENCY_CODE => currency_code,
2752 X_EXCHANGE_RATE_TYPE => xtype,
2753 X_EXCHANGE_RATE_DATE => xdate,
2754 X_EXCHANGE_RATE => xrate,
2755 X_MATCHED_FLAG => 'Y',
2756 X_ACTUAL_VALUE_DATE => value_date,
2757 X_LAST_UPDATE_DATE => sysdate,
2758 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
2759 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
2760 X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
2761 X_CREATION_DATE => sysdate,
2762 X_PROGRAM_UPDATE_DATE => to_date(NULL),
2763 X_PROGRAM_APPLICATION_ID=> to_number(NULL),
2764 X_PROGRAM_ID => to_number(NULL),
2765 X_REQUEST_ID => to_number(NULL),
2766 X_CALLING_SEQUENCE => 'CE_AUTO_BANK_CLEAR1.unclear_process '
2767 );
2768 IF l_DEBUG in ('Y', 'C') THEN
2769 cep_standard.debug('unclear_process: ' || '>>>AP_RECONCILIATION_PKG.recon_payment_history');
2770 END IF;
2771
2772 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2773 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(200, trx_id, 'N');
2774 END IF; -- Bug 3427050
2775 ELSIF (clearing_trx_type IN ('CASH','MISC')) THEN
2776
2777 SELECT NVL(arh.current_record_flag, 'N')
2778 INTO p_current_record_flag
2779 FROM ar_cash_receipt_history_all arh
2780 WHERE arh.cash_receipt_history_id = trx_id;
2781
2782 IF (status not in ('REVERSED', 'RISK_ELIMINATED')
2783 AND p_current_record_flag <> 'N'
2784 AND arp_cashbook.receipt_debit_memo_reversed(cash_receipt_id) = 'N') THEN
2785 IF l_DEBUG in ('Y', 'C') THEN
2786 cep_standard.debug('call ARP_CASHBOOK.unclear');
2787 END IF;
2788
2789 ARP_CASHBOOK.unclear( p_cr_id => cash_receipt_id,
2790 p_trx_date => trx_date,
2791 p_gl_date => gl_date,
2792 p_actual_value_date => value_date,
2793 p_module_name => 'CEXCABMR',
2794 p_module_version => '1.0',
2795 p_crh_id => cash_receipt_history_id);
2796 IF l_DEBUG in ('Y', 'C') THEN
2797 cep_standard.debug('end call ARP_CASHBOOK.unclear');
2798 END IF;
2799
2800 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2801 update ce_available_transactions_tmp
2802 set reconciled_status_flag = 'N'
2803 where application_id = 222
2804 and trx_id = trx_id
2805 and status <> 'REVERSED';
2806 END IF;
2807 ELSIF (clearing_trx_type = 'ROI_LINE' ) THEN
2808 CE_999_PKG.unclear( X_trx_id => trx_id,
2809 X_trx_type => tx_type,
2810 X_status => nvl(CE_AUTO_BANK_REC.G_open_interface_float_status, status),
2811 X_trx_date => trx_date,
2812 X_gl_date => gl_date);
2813 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2814 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(999, trx_id, 'N');
2815
2816 ELSIF (clearing_trx_type = 'XTR_LINE' ) THEN
2817 IF l_DEBUG in ('Y', 'C') THEN
2818 cep_standard.debug('unclear_process: ' || 'clearing_trx_type = xtr_line start');
2819 END IF;
2820 XTR_WRAPPER_API_P.reconciliation(
2821 P_SETTLEMENT_SUMMARY_ID => trx_id,
2822 P_TASK => 'UNR',
2823 P_RECONCILED_METHOD => null,
2824 P_RESULT => X_RESULT);
2825 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2826 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(185, trx_id, 'N');
2827
2828
2829 IF l_DEBUG in ('Y', 'C') THEN
2830 cep_standard.debug('unclear_process: ' || 'X_RESULT = ' ||X_RESULT);
2831 END IF;
2832 if (X_RESULT <> 'XTR2_SUCCESS') then
2833 FND_MESSAGE.set_name( 'CE','CE_XTR_UPDATE_FAILED');
2834 RAISE APP_EXCEPTION.application_exception;
2835 end if;
2836 IF l_DEBUG in ('Y', 'C') THEN
2837 cep_standard.debug('unclear_process: ' || 'clearing_trx_type = xtr_line END');
2838 END IF;
2839 ELSIF (clearing_trx_type = 'CASHFLOW' ) THEN
2840 IF l_DEBUG in ('Y', 'C') THEN
2841 cep_standard.debug('clearing_trx_type = CASHFLOW start');
2842 END IF;
2843 -- passin_mode MANUAL, MANUAL_UC
2844
2845
2846 CE_CASHFLOW_PKG.CLEAR_CASHFLOW(
2847 X_CASHFLOW_ID => trx_id,
2848 X_TRX_STATUS => status,
2849 x_actual_value_date => trx_date,
2850 X_ACCOUNTING_DATE => gl_date,
2851 X_CLEARED_DATE => trx_date,
2852 X_CLEARED_AMOUNT => transaction_amount,
2853 X_CLEARED_ERROR_AMOUNT => NVL(error_amount,0),
2854 X_CLEARED_CHARGE_AMOUNT => NVL(charge_amount,0),
2855 X_CLEARED_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2856 X_CLEARED_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2857 X_CLEARED_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2858 X_PASSIN_MODE => passin_mode,
2859 x_statement_line_id => stmt_line_id,
2860 x_statement_line_type => null
2861 ) ;
2862
2863 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2864 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(261, trx_id, 'N');
2865
2866
2867 /*
2868 CE_CASHFLOW_PKG.CLEAR_CASHFLOW(
2869 X_CASHFLOW_ID => trx_id,
2870 X_TRX_STATUS => status,
2871 x_actual_value_date => trx_date,
2872 X_ACCOUNTING_DATE => gl_date,
2873 X_CLEARED_DATE => trx_date,
2874 X_CLEARED_AMOUNT => NVL(amount_cleared,0),
2875 X_CLEARED_ERROR_AMOUNT => NVL(errors_amount,0),
2876 X_CLEARED_CHARGE_AMOUNT => NVL(charges_amount,0),
2877 X_CLEARED_EXCHANGE_RATE_TYPE => CE_AUTO_BANK_CLEAR.G_exchange_rate_type,
2878 X_CLEARED_EXCHANGE_RATE_DATE => to_date(to_char(CE_AUTO_BANK_CLEAR.G_exchange_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
2879 X_CLEARED_EXCHANGE_RATE => CE_AUTO_BANK_CLEAR.G_exchange_rate,
2880 X_PASSIN_MODE => passin_mode
2881 ) ;
2882 */
2883 IF l_DEBUG in ('Y', 'C') THEN
2884 cep_standard.debug('clearing_trx_type = CASHFLOW END');
2885 END IF;
2886
2887 ELSIF (clearing_trx_type = 'STATEMENT') THEN
2888 IF (passin_mode = 'MANUAL') THEN
2889 CE_AUTO_BANK_MATCH.csl_statement_line_id := stmt_line_id;
2890 END IF;
2891 -- bug 4247469 the CE_AUTO_BANK_MATCH.csl_reconcile_flag need to be cleared
2892 -- in case a journal is processed before the stmt transaction
2893 CE_AUTO_BANK_MATCH.csl_reconcile_flag := NULL;
2894
2895 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2896 Y_statement_line_id => trx_id,
2897 Y_cleared_trx_type => clearing_trx_type,
2898 Y_cleared_trx_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2899 Y_ar_cash_receipt_id => cash_receipt_id,
2900 Y_reference_status => status,
2901 Y_auto_reconciled_flag => 'N',
2902 Y_status_flag => 'U');
2903
2904 CE_AUTO_BANK_CLEAR1.update_line_unreconciled(trx_id);
2905
2906 -- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
2907 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(260, trx_id, 'N');
2908
2909 ELSIF (clearing_trx_type IN ('PAY','PAY_EFT')) THEN
2910 IF l_DEBUG in ('Y', 'C') THEN
2911 cep_standard.debug('call PAY_CE_RECONCILIATION_PKG.reverse_reconcile');
2912 END IF;
2913
2914 PAY_CE_RECONCILIATION_PKG.reverse_reconcile(
2915 p_payment_id => trx_id);
2916
2917 -- after unreconciliation, update the reconciled_status of this trx in the GT table to 'N'
2918 IF (clearing_trx_type = 'PAY') THEN
2919 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(801, trx_id, 'N');
2920 ELSE
2921 CE_AUTO_BANK_MATCH.update_gt_reconciled_status(802, trx_id, 'N');
2922 END IF;
2923
2924
2925 IF l_DEBUG in ('Y', 'C') THEN
2926 cep_standard.debug('end call PAY_CE_RECONCILIATION_PKG.reverse_reconcile');
2927 END IF;
2928 END IF;
2929 IF (passin_mode <> 'MANUAL_UC') THEN
2930 CE_AUTO_BANK_MATCH.csl_statement_line_id := stmt_line_id;
2931 IF (clearing_trx_type IN ('PAYMENT','CASH','MISC','ROI_LINE','XTR_LINE','STATEMENT','PAY','PAY_EFT','CASHFLOW')) THEN
2932 CE_AUTO_BANK_MATCH.csl_reconcile_flag := NULL;
2933 ELSE
2934 CE_AUTO_BANK_MATCH.csl_reconcile_flag := 'JE';
2935 END IF;
2936
2937 CE_AUTO_BANK_CLEAR1.insert_reconciliation (
2938 Y_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
2939 Y_cleared_trx_type => clearing_trx_type,
2940 Y_cleared_trx_id => trx_id,
2941 Y_ar_cash_receipt_id => cash_receipt_id,
2942 Y_reference_status => status,
2943 Y_auto_reconciled_flag => 'N',
2944 Y_status_flag => 'U');
2945
2946 IF l_DEBUG in ('Y', 'C') THEN
2947 cep_standard.debug('call CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
2948 END IF;
2949
2950 CE_AUTO_BANK_CLEAR1.update_line_unreconciled(stmt_line_id);
2951
2952 IF l_DEBUG in ('Y', 'C') THEN
2953 cep_standard.debug('end call CE_AUTO_BANK_CLEAR1.update_line_unreconciled');
2954 END IF;
2955 END IF;
2956 IF (X_header_or_line = 'HEADERS') THEN
2957 CE_AUTO_BANK_CLEAR1.update_line_unreconciled(stmt_line_id);
2958 END IF;
2959 IF l_DEBUG in ('Y', 'C') THEN
2960 cep_standard.debug('<<CE_AUTO_BANK_CLEAR1.unclear_process');
2961 END IF;
2962
2963 EXCEPTION
2964 when others then
2965 IF l_DEBUG in ('Y', 'C') THEN
2966 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR1.unclear_process');
2967 END IF;
2968 RAISE;
2969 END unclear_process;
2970
2971
2972 PROCEDURE populate_avail_trx(
2973 X_table VARCHAR2,
2974 X_where_clause VARCHAR,
2975 X_asset_code_combination_id NUMBER,
2976 X_bank_account_id NUMBER,
2977 X_from_number VARCHAR2,
2978 X_to_number VARCHAR2,
2979 X_status VARCHAR2,
2980 X_currency_code VARCHAR2,
2981 X_reference_id NUMBER,
2982 X_from_batch_name VARCHAR2,
2983 X_to_batch_name VARCHAR2,
2984 X_from_remit_num VARCHAR2,
2985 X_to_remit_num VARCHAR2,
2986 X_from_date DATE,
2987 X_to_date DATE,
2988 X_from_maturity_date DATE,
2989 X_to_maturity_date DATE,
2990 X_customer_id NUMBER,
2991 X_receipt_class_id NUMBER,
2992 X_receipt_method_id NUMBER,
2993 X_deposit_date_from DATE,
2994 X_deposit_date_to DATE,
2995 X_supplier_id NUMBER,
2996 X_reference_type VARCHAR2,
2997 X_period_name VARCHAR2,
2998 X_from_journal_entry_name VARCHAR2,
2999 X_to_journal_entry_name VARCHAR2,
3000 X_from_document_number NUMBER,
3001 X_to_document_number NUMBER,
3002 X_from_je_line_number NUMBER,
3003 X_to_je_line_number NUMBER,
3004 X_cleared_date DATE,
3005 X_value_date DATE,
3006 X_gl_date DATE,
3007 X_from_amount NUMBER,
3008 X_to_amount NUMBER,
3009 X_org_id NUMBER,
3010 X_legal_entity_id NUMBER) IS
3011 insert_stmt VARCHAR2(3000);
3012 cursor_id INTEGER;
3013 exec_id INTEGER;
3014 BEGIN
3015 cursor_id := DBMS_SQL.open_cursor;
3016 insert_stmt := 'INSERT INTO ce_available_transactions_tmp ' ||
3017 '(ROW_ID, MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, ' ||
3018 'BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, '||
3019 'TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, ' ||
3020 'CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, ' ||
3021 'GL_DATE, STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE, ' ||
3022 'MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE, ' ||
3023 'USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS, '||
3024 'BATCH_NAME, BATCH_ID, AGENT_NAME, CUSTOMER_NAME, PAYMENT_METHOD, '||
3025 'VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, REFERENCE_TYPE_DSP, '||
3026 'REFERENCE_TYPE, REFERENCE_ID, ACTUAL_AMOUNT_CLEARED, CREATION_DATE, '||
3027 'CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, '||
3028 'CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, '||
3029 'TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME, '||
3030 'JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER, '||
3031 'CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID, '||
3032 'RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE, ' ||
3033 'REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID) ' ||
3034 'SELECT ROW_ID, MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, '||
3035 'BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, ' ||
3036 'TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, CURRENCY_CODE, AMOUNT, '||
3037 'BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, GL_DATE, STATUS_DSP, STATUS, ' ||
3038 'null, TRX_DATE, CLEARED_DATE, MATURITY_DATE, EXCHANGE_RATE_DATE, ' ||
3039 'EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, ' ||
3040 'BANK_CHARGES, BANK_ERRORS, BATCH_NAME, BATCH_ID, AGENT_NAME, ' ||
3041 'CUSTOMER_NAME, PAYMENT_METHOD, VENDOR_NAME, CUSTOMER_ID, ' ||
3042 'SUPPLIER_ID, REFERENCE_TYPE_DSP, REFERENCE_TYPE, REFERENCE_ID, ' ||
3043 'ACTUAL_AMOUNT_CLEARED, CREATION_DATE, CREATED_BY, ' ||
3044 'LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, ' ||
3045 'CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, '||
3046 'TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME, '||
3047 'JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER, '||
3048 'CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID, '||
3049 'RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE, ' ||
3050 'REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID FROM ' || X_table ||
3051 ' WHERE ' || X_where_clause;
3052
3053 DBMS_SQL.Parse(cursor_id,
3054 insert_stmt,
3055 DBMS_SQL.v7);
3056 if (X_asset_code_combination_id is not null) then
3057 DBMS_SQL.bind_variable(cursor_id, ':asset_code_combination_id',
3058 X_asset_code_combination_id);
3059 end if;
3060 if (X_bank_account_id is not null) then
3061 DBMS_SQL.bind_variable(cursor_id, ':bank_account_id',
3062 X_bank_account_id);
3063 end if;
3064 if (X_org_id is not null) then
3065 DBMS_SQL.bind_variable(cursor_id, ':org_id',
3066 X_org_id);
3067 elsif (X_legal_entity_id is not null) then
3068 DBMS_SQL.bind_variable(cursor_id, ':legal_entity_id',
3069 X_legal_entity_id);
3070 end if;
3071 if (X_from_number is not null AND X_from_number <> '%') then
3072 DBMS_SQL.bind_variable(cursor_id, ':from_number', X_from_number);
3073 end if;
3074 if (X_to_number is not null AND X_to_number <> '%') then
3075 DBMS_SQL.bind_variable(cursor_id, ':to_number', X_to_number);
3076 end if;
3077 if (X_status is not null) then
3078 DBMS_SQL.bind_variable(cursor_id, ':status', X_status);
3079 end if;
3080 if (X_currency_code is not null) then
3081 DBMS_SQL.bind_variable(cursor_id, ':currency_code', X_currency_code);
3082 end if;
3083 if (X_reference_id is not null) then
3084 DBMS_SQL.bind_variable(cursor_id, ':reference_id', X_reference_id);
3085 end if;
3086 if (X_from_batch_name is not null AND X_from_batch_name <> '%') then
3087 DBMS_SQL.bind_variable(cursor_id, ':from_batch_name', X_from_batch_name);
3088 end if;
3089 if (X_to_batch_name is not null AND X_to_batch_name <> '%') then
3090 DBMS_SQL.bind_variable(cursor_id, ':to_batch_name', X_to_batch_name);
3091 end if;
3092 if (X_from_remit_num is not null AND X_from_remit_num <> '%') then
3093 DBMS_SQL.bind_variable(cursor_id, ':from_remit_num', X_from_remit_num);
3094 end if;
3095 if (X_to_remit_num is not null AND X_to_remit_num <> '%') then
3096 DBMS_SQL.bind_variable(cursor_id, ':to_remit_num', X_to_remit_num);
3097 end if;
3098 if (X_from_date is not null) then
3099 DBMS_SQL.bind_variable(cursor_id, ':from_date', X_from_date);
3100 end if;
3101 if (X_to_date is not null) then
3102 DBMS_SQL.bind_variable(cursor_id, ':to_date', X_to_date);
3103 end if;
3104 if (X_from_maturity_date is not null) then
3105 DBMS_SQL.bind_variable(cursor_id, ':from_maturity_date',
3106 X_from_maturity_date);
3107 end if;
3108 if (X_to_maturity_date is not null) then
3109 DBMS_SQL.bind_variable(cursor_id, ':to_maturity_date',
3110 X_to_maturity_date);
3111 end if;
3112 if (X_customer_id is not null) then
3113 DBMS_SQL.bind_variable(cursor_id, ':customer_id', X_customer_id);
3114 end if;
3115 if (X_receipt_class_id is not null) then
3116 DBMS_SQL.bind_variable(cursor_id, ':receipt_class_id',
3117 X_receipt_class_id);
3118 end if;
3119 if (X_receipt_method_id is not null) then
3120 DBMS_SQL.bind_variable(cursor_id, ':receipt_method_id',
3121 X_receipt_method_id);
3122 end if;
3123 if (X_deposit_date_from is not null) then
3124 DBMS_SQL.bind_variable(cursor_id, ':deposit_date_from',
3125 X_deposit_date_from);
3126 end if;
3127 if (X_deposit_date_to is not null) then
3128 DBMS_SQL.bind_variable(cursor_id, ':deposit_date_to', X_deposit_date_to);
3129 end if;
3130 if (X_receipt_class_id is not null) then
3131 DBMS_SQL.bind_variable(cursor_id, ':receipt_class_id',
3132 X_receipt_class_id);
3133 end if;
3134 if (X_supplier_id is not null) then
3135 DBMS_SQL.bind_variable(cursor_id, ':supplier_id', X_supplier_id);
3136 end if;
3137 if (X_reference_type is not null) then
3138 DBMS_SQL.bind_variable(cursor_id, ':reference_type', X_reference_type);
3139 end if;
3140 if (X_period_name is not null) then
3141 DBMS_SQL.bind_variable(cursor_id, ':period_name', X_period_name);
3142 end if;
3143 if (X_from_journal_entry_name is not null
3144 AND X_from_journal_entry_name <> '%') then
3145 DBMS_SQL.bind_variable(cursor_id, ':from_journal_entry_name',
3146 X_from_journal_entry_name);
3147 end if;
3148 if (X_to_journal_entry_name is not null
3149 AND X_to_journal_entry_name <> '%') then
3150 DBMS_SQL.bind_variable(cursor_id, ':to_journal_entry_name',
3151 X_to_journal_entry_name);
3152 end if;
3153 if (X_from_document_number is not null) then
3154 DBMS_SQL.bind_variable(cursor_id, ':from_document_number',
3155 X_from_document_number);
3156 end if;
3157 if (X_to_document_number is not null) then
3158 DBMS_SQL.bind_variable(cursor_id, ':to_document_number',
3159 X_to_document_number);
3160 end if;
3161 if (X_from_je_line_number is not null) then
3162 DBMS_SQL.bind_variable(cursor_id, ':from_je_line_number',
3163 X_from_je_line_number);
3164 end if;
3165 if (X_to_je_line_number is not null) then
3166 DBMS_SQL.bind_variable(cursor_id, ':to_je_line_number',
3167 X_to_je_line_number);
3168 end if;
3169 if (X_cleared_date is not null) then
3170 DBMS_SQL.bind_variable(cursor_id, ':cleared_date', X_cleared_date);
3171 end if;
3172 if (X_value_date is not null) then
3173 DBMS_SQL.bind_variable(cursor_id, ':value_date', X_value_date);
3174 end if;
3175 if (X_gl_date is not null) then
3176 DBMS_SQL.bind_variable(cursor_id, ':gl_date', X_gl_date);
3177 end if;
3178 if (X_from_amount is not null) then
3179 DBMS_SQL.bind_variable(cursor_id, ':from_amount', X_from_amount);
3180 end if;
3181 if (X_to_amount is not null) then
3182 DBMS_SQL.bind_variable(cursor_id, ':to_amount', X_to_amount);
3183 end if;
3184 exec_id := DBMS_SQL.execute(cursor_id);
3185 DBMS_SQL.close_cursor(cursor_id);
3186 EXCEPTION
3187 when others then
3188 null;
3189 END populate_avail_trx;
3190
3191 END CE_AUTO_BANK_CLEAR1;