[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_CLEAR
Source
1 PACKAGE BODY CE_AUTO_BANK_CLEAR AS
2 /* $Header: ceabrcrb.pls 120.14.12010000.3 2008/11/20 09:04:33 vnetan ship $ */
3 CURSOR C_STATEMENT_LINE_SEQ IS SELECT ce_statement_lines_s.nextval from sys.dual;
4
5 FUNCTION body_revision RETURN VARCHAR2 IS
6 BEGIN
7
8 RETURN '$Revision: 120.14.12010000.3 $';
9
10 END body_revision;
11
12 FUNCTION spec_revision RETURN VARCHAR2 IS
13 BEGIN
14
15 RETURN G_spec_revision;
16
17 END spec_revision;
18 /* --------------------------------------------------------------------
19 | PRIVATE PROCEDURE |
20 | set_manual_clearing |
21 --------------------------------------------------------------------- */
22 PROCEDURE set_manual_clearing IS
23 BEGIN
24 CE_AUTO_BANK_CLEAR.yes_manual_clearing := 1;
25 END set_manual_clearing;
26
27 PROCEDURE set_reverse_mode IS
28 BEGIN
29 CE_AUTO_BANK_CLEAR.yes_reverse_mode := 1;
30 END set_reverse_mode;
31
32 PROCEDURE unset_manual_clearing IS
33 BEGIN
34 CE_AUTO_BANK_CLEAR.yes_manual_clearing := 0;
35 END unset_manual_clearing;
36
37 PROCEDURE unset_reverse_mode IS
38 BEGIN
39 CE_AUTO_BANK_CLEAR.yes_reverse_mode := 0;
40 END unset_reverse_mode;
41
42 FUNCTION get_manual_clearing RETURN NUMBER IS
43 BEGIN
44 RETURN CE_AUTO_BANK_CLEAR.yes_manual_clearing;
45 END get_manual_clearing;
46
47 FUNCTION get_reverse_mode RETURN NUMBER IS
48 BEGIN
49 RETURN CE_AUTO_BANK_CLEAR.yes_reverse_mode;
50 END get_reverse_mode;
51
52 /* ---------------------------------------------------------------------
53 | PRIVATE PROCEDURE |
54 | update_line_status |
55 | |
56 | DESCRIPTION |
57 | Update the record status to indicate its current state. |
58 --------------------------------------------------------------------- */
59 PROCEDURE update_line_status (
60 X_statement_line_id NUMBER,
61 X_status VARCHAR2) IS
62 BEGIN
63 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.update_line_status');
64 if (X_status = 'RECONCILED') then
65 if (CE_AUTO_BANK_MATCH.trx_currency_type = 'BANK'
66 and CE_AUTO_BANK_MATCH.foreign_exchange_defaulted = 'Y') then
67 UPDATE ce_statement_lines l
68 SET status = X_status,
69 reconcile_to_statement_flag =
70 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag
71 WHERE statement_line_id = X_statement_line_id
72 AND EXISTS
73 (select NULL
74 from ce_statement_recon_gt_v --ce_statement_reconciliations
75 where statement_line_id = l.statement_line_id
76 and current_record_flag = 'Y'
77 and status_flag = 'M');
78 else
79 UPDATE ce_statement_lines l
80 SET status = X_status,
81 exchange_rate_type = CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
82 exchange_rate_date = CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
83 exchange_rate = CE_AUTO_BANK_MATCH.csl_exchange_rate,
84 currency_code = CE_AUTO_BANK_MATCH.csl_currency_code,
85 reconcile_to_statement_flag =
86 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag
87 WHERE statement_line_id = X_statement_line_id
88 AND EXISTS
89 (select NULL
90 from ce_statement_recon_gt_v --ce_statement_reconciliations
91 where statement_line_id = l.statement_line_id
92 and current_record_flag = 'Y'
93 and status_flag = 'M');
94 end if;
95 else /* UNRECONCILED */
96 UPDATE ce_statement_lines
97 SET status = X_status,
98 reconcile_to_statement_flag =
99 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag
100 WHERE statement_line_id = X_statement_line_id;
101 end if;
102 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.update_line_status');
103 EXCEPTION
104 WHEN OTHERS THEN
105 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR.update_line_status');
106 RAISE;
107 END update_line_status;
108
109 /* ---------------------------------------------------------------------
110 | PRIVATE PROCEDURE |
111 | DM_reversals |
112 | CALLED BY |
113 | reconcile_process |
114 --------------------------------------------------------------------- */
115 PROCEDURE DM_reversals
116 ( cash_receipt_id NUMBER,
117 cc_id NUMBER,
118 cust_trx_type_id NUMBER,
119 cust_trx_type VARCHAR2,
120 gl_date DATE,
121 reversal_date DATE,
122 reason VARCHAR2,
123 category VARCHAR2,
124 module_name VARCHAR2,
125 comment VARCHAR2,
126 document_number NUMBER,
127 doc_sequence_id NUMBER) IS
128 out_trx_number ar_payment_schedules.trx_number%TYPE;
129 out_status varchar2(10);
130 BEGIN
131 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.DM_reversals');
132
133 ARP_CASHBOOK.debit_memo_reversal
134 ( p_cash_receipt_id => cash_receipt_id,
135 p_cc_id => cc_id,
136 p_dm_cust_trx_type_id => cust_trx_type_id,
137 p_dm_cust_trx_type => cust_trx_type,
138 p_reversal_gl_date => gl_date,
139 p_reversal_date => reversal_date,
140 p_reversal_category => category,
141 p_reversal_reason_code => reason,
142 p_reversal_comments => comment,
143 p_dm_number => out_trx_number,
144 p_dm_doc_sequence_value => document_number,
145 p_dm_doc_sequence_id => doc_sequence_id,
146 p_tw_status => out_status,
147 p_module_name => 'CEXCABMR',
148 p_module_version => '11.5');
149
150 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.DM_reversals');
151 END DM_reversals;
152
153 /* ---------------------------------------------------------------------
154 | PRIVATE PROCEDURE |
155 | reversals |
156 | CALLED BY |
157 | reconcile_process |
158 --------------------------------------------------------------------- */
159 PROCEDURE reversals( cash_receipt_id NUMBER,
160 gl_date DATE,
161 reason VARCHAR2,
162 category VARCHAR2,
163 module_name VARCHAR2,
164 comment VARCHAR2 ) IS
165 history_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_history_id%TYPE;
166 BEGIN
167 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reversals');
168 ARP_CASHBOOK.reverse( p_cr_id => cash_receipt_id,
169 p_reversal_gl_date => gl_date,
170 p_reversal_date => sysdate,
171 p_reversal_comments => comment,
172 p_reversal_reason_code => reason,
173 p_reversal_category => category,
174 p_module_name => module_name,
175 p_module_version => '1.0',
176 p_crh_id => history_id);
177 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reversals');
178 END reversals;
179
180 /* ---------------------------------------------------------------------
181 | PRIVATE PROCEDURE |
182 | reconcile_rbatch |
183 | DESCRIPTION |
184 | Each receipt within the remittance batch must be cleared and |
185 | reconciled. |
186 | CALLED BY |
187 | reconcile_process |
188 --------------------------------------------------------------------- */
189 PROCEDURE reconcile_rbatch(
190 passin_mode VARCHAR2,
191 rbatch_id NUMBER,
192 X_statement_line_id IN OUT NOCOPY NUMBER,
193 gl_date DATE,
194 value_date DATE,
195 bank_currency VARCHAR2,
196 exchange_rate_type VARCHAR2,
197 exchange_rate NUMBER,
198 exchange_rate_date DATE,
199 trx_currency_type VARCHAR2,
200 module VARCHAR2,
201 X_trx_number IN OUT NOCOPY VARCHAR2,
202 X_trx_date DATE,
203 X_deposit_date DATE,
204 X_amount NUMBER,
205 X_foreign_diff_amt NUMBER,
206 X_set_of_books_id NUMBER,
207 X_misc_currency_code VARCHAR2,
208 X_receipt_method_id NUMBER,
209 X_bank_account_id NUMBER,
210 X_activity_type_id NUMBER,
211 X_comments VARCHAR2,
212 X_reference_type VARCHAR2,
213 X_clear_currency_code VARCHAR2,
214 X_tax_id NUMBER,
215 X_tax_rate NUMBER,
216 X_cr_vat_tax_id VARCHAR2,
217 X_dr_vat_tax_id VARCHAR2,
218 X_trx_type VARCHAR2 DEFAULT NULL,
219 X_statement_header_id IN OUT NOCOPY NUMBER,
220 X_statement_date DATE DEFAULT NULL,
221 X_bank_trx_number VARCHAR2 DEFAULT NULL,
222 X_statement_amount NUMBER DEFAULT NULL,
223 X_original_amount NUMBER DEFAULT NULL,
224 X_effective_date DATE,
225 X_float_handling_flag VARCHAR2) IS
226 BEGIN
227 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reconcile_rbatch');
228 CE_AUTO_BANK_CLEAR1.reconcile_rbatch(
229 passin_mode,
230 rbatch_id,
231 X_statement_line_id,
232 gl_date,
233 value_date,
234 bank_currency,
235 exchange_rate_type,
236 exchange_rate,
237 exchange_rate_date,
238 trx_currency_type,
239 module,
240 X_trx_number,
241 X_trx_date,
242 X_deposit_date,
243 X_amount,
244 X_foreign_diff_amt,
245 X_set_of_books_id,
246 X_misc_currency_code,
247 X_receipt_method_id,
248 X_bank_account_id,
249 X_activity_type_id,
250 X_comments,
251 X_reference_type,
252 X_clear_currency_code,
253 X_tax_id,
254 X_tax_rate,
255 X_cr_vat_tax_id,
256 X_dr_vat_tax_id,
257 X_trx_type,
258 X_statement_header_id,
259 X_statement_date,
260 X_bank_trx_number,
261 X_statement_amount,
262 X_original_amount,
263 X_effective_date,
264 X_float_handling_flag);
265 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reconcile_rbatch');
266 END reconcile_rbatch;
267
268 /* ---------------------------------------------------------------------
269 | PRIVATE PROCEDURE |
270 | misc_receipt |
271 --------------------------------------------------------------------- */
272 PROCEDURE misc_receipt( X_passin_mode VARCHAR2,
273 X_trx_number VARCHAR2,
274 X_doc_sequence_value VARCHAR2,
275 X_doc_sequence_id NUMBER,
276 X_gl_date DATE,
277 X_value_date DATE,
278 X_trx_date DATE,
279 X_deposit_date DATE,
280 X_amount NUMBER,
281 X_bank_account_amount NUMBER,
282 X_set_of_books_id NUMBER,
283 X_misc_currency_code VARCHAR2,
284 X_exchange_rate_date DATE,
285 X_exchange_rate_type VARCHAR2,
286 X_exchange_rate NUMBER,
287 X_receipt_method_id NUMBER,
288 X_bank_account_id NUMBER,
289 X_activity_type_id NUMBER,
290 X_comments VARCHAR2,
291 X_reference_type VARCHAR2,
292 X_reference_id NUMBER,
293 X_clear_currency_code VARCHAR2,
294 X_statement_line_id IN OUT NOCOPY NUMBER,
295 X_tax_id NUMBER,
296 X_tax_rate NUMBER,
297 X_paid_from VARCHAR2,
298 X_module_name VARCHAR2,
299 X_cr_vat_tax_id VARCHAR2,
300 X_dr_vat_tax_id VARCHAR2,
301 trx_currency_type VARCHAR2,
302 X_cr_id IN OUT NOCOPY NUMBER,
303 X_effective_date DATE,
304 X_org_id NUMBER ) IS
305 BEGIN
306 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.misc_receipt');
307 CE_AUTO_BANK_CLEAR1.misc_receipt(
308 X_passin_mode,
309 X_trx_number,
310 X_doc_sequence_value,
311 X_doc_sequence_id,
312 X_gl_date,
313 X_value_date,
314 X_trx_date,
315 X_deposit_date,
316 X_amount,
317 X_bank_account_amount,
318 X_set_of_books_id,
319 X_misc_currency_code,
320 X_exchange_rate_date,
321 X_exchange_rate_type,
322 X_exchange_rate,
323 X_receipt_method_id,
324 X_bank_account_id,
325 X_activity_type_id,
326 X_comments,
327 X_reference_type,
328 X_reference_id,
329 X_clear_currency_code,
330 X_statement_line_id,
331 X_tax_id,
332 X_tax_rate,
333 X_paid_from,
334 X_module_name,
335 X_cr_vat_tax_id,
336 X_dr_vat_tax_id,
337 trx_currency_type,
338 X_cr_id,
339 X_effective_date,
340 X_org_id);
341 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.misc_receipt');
342 END misc_receipt;
343
344 /* ---------------------------------------------------------------------
345 | PRIVATE PROCEDURE |
346 | reconcile_pbatch |
347 | CALLED BY |
348 | reconcile_process |
349 --------------------------------------------------------------------- */
350 PROCEDURE reconcile_pbatch ( passin_mode VARCHAR2,
351 pbatch_id NUMBER,
352 statement_line_id IN OUT NOCOPY NUMBER,
353 gl_date DATE,
354 value_date DATE,
355 cleared_date DATE,
356 amount_to_clear NUMBER,
357 errors_amount NUMBER,
358 charges_amount NUMBER,
359 prorate_amount NUMBER,
360 exchange_rate_type VARCHAR2,
361 exchange_rate_date DATE,
362 exchange_rate NUMBER,
363 trx_currency_type VARCHAR2,
364 X_statement_header_id IN OUT NOCOPY NUMBER,
365 statement_header_date DATE,
366 X_trx_type VARCHAR2,
367 X_bank_trx_number VARCHAR2,
368 X_currency_code VARCHAR2,
369 X_original_amount NUMBER,
370 X_effective_date DATE,
371 X_float_handling_flag VARCHAR2,
372 X_bank_currency_code VARCHAR2,
373 pgroup_id VARCHAR2 default null -- FOR SEPA ER 6700007
374 ) IS
375 BEGIN
376 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reconcile_pbatch');
377 CE_AUTO_BANK_CLEAR1.reconcile_pbatch(
378 passin_mode,
379 pbatch_id,
380 statement_line_id,
381 gl_date,
382 value_date,
383 cleared_date,
384 amount_to_clear,
385 errors_amount,
386 charges_amount,
387 prorate_amount,
388 exchange_rate_type,
389 exchange_rate_date,
390 exchange_rate,
391 trx_currency_type,
392 X_statement_header_id,
393 statement_header_date,
394 X_trx_type,
395 X_bank_trx_number,
396 X_currency_code,
397 X_original_amount,
398 X_effective_date,
399 X_float_handling_flag,
400 X_bank_currency_code,
401 pgroup_id);
402 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reconcile_pbatch');
403 END reconcile_pbatch;
404
405 /* ---------------------------------------------------------------------
406 | PRIVATE PROCEDURE |
407 | calc_foreign_clearing_amounts |
408 | CALLED BY |
409 | calculate_clearing_amounts |
410 --------------------------------------------------------------------- */
411 PROCEDURE calc_foreign_clearing_amounts (success IN OUT NOCOPY BOOLEAN) IS
412 difference_amount NUMBER;
413 clearing_sign NUMBER;
414 real_rate NUMBER;
415 precision NUMBER;
416 ext_precision NUMBER;
417 min_acct_unit NUMBER;
418 BEGIN
419 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.calc_foreign_clearing_amounts');
420 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_type = '||
421 CE_AUTO_BANK_MATCH.csl_match_type);
422 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '||
423 CE_AUTO_BANK_MATCH.calc_csl_amount);
424 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_amount = '||
425 CE_AUTO_BANK_MATCH.trx_amount);
426 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '||
427 CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
428
429 -- cep_standard.debug('DEBUG: CE_AUTO_BANK_REC.G_foreign_difference_handling = '||
430 -- CE_AUTO_BANK_REC.G_foreign_difference_handling);
431
432 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_recon_ce_fx_diff_handling = '||
433 CE_AUTO_BANK_MATCH.ba_recon_ce_fx_diff_handling);
434 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_recon_ap_fx_diff_handling = '||
435 CE_AUTO_BANK_MATCH.ba_recon_ap_fx_diff_handling);
436 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_recon_ar_fx_diff_handling = '||
437 CE_AUTO_BANK_MATCH.ba_recon_ar_fx_diff_handling);
438 --
439 -- Payment
440 --
441 -- 7571492: Added PGROUP
442 IF (CE_AUTO_BANK_MATCH.csl_match_type IN ('PAYMENT','PBATCH','PGROUP') OR
443 --CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW' OR
444 CE_AUTO_BANK_MATCH.trx_match_type = 'PAYMENT') THEN
445 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
446 clearing_sign := -1;
447 else
448 clearing_sign := 1;
449 end if;
450
451 -- bug 4528375
452 -- trx_amount was not rounded in view ce_200_transactions_v, so round it
453 fnd_currency.get_info(CE_AUTO_BANK_MATCH.aba_bank_currency,
454 precision, ext_precision, min_acct_unit);
455 CE_AUTO_BANK_MATCH.trx_amount := round(CE_AUTO_BANK_MATCH.trx_amount, precision);
456
457 difference_amount := CE_AUTO_BANK_MATCH.calc_csl_amount * clearing_sign
458 - CE_AUTO_BANK_MATCH.trx_amount;
459 /* 2886201
460 If transaction currency amount is 0, set real_rate to 1.0 to avoid
461 Division by Zero error. */
462 IF (CE_AUTO_BANK_MATCH.trx_curr_amount = 0) THEN
463 real_rate := 1.0;
464 ELSE
465 real_rate := (CE_AUTO_BANK_MATCH.csl_amount -
466 NVL(CE_AUTO_BANK_MATCH.csl_charges_amount,0))/
467 CE_AUTO_BANK_MATCH.trx_curr_amount;
468 END IF;
469
470 /* 2886201 End of Code Changes */
471 IF (ABS(difference_amount) <> ABS(CE_AUTO_BANK_MATCH.csl_charges_amount)) THEN
472 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
473 --IF (CE_AUTO_BANK_MATCH.G_le_fx_difference_handling = 'C') THEN
474 IF (CE_AUTO_BANK_MATCH.ba_recon_ce_fx_diff_handling = 'CH') THEN
475 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_MATCH.trx_exchange_rate_type;
476 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_exchange_rate_date;
477 CE_AUTO_BANK_MATCH.csl_exchange_rate := CE_AUTO_BANK_MATCH.trx_exchange_rate;
478
479 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
480
481 ELSIF (CE_AUTO_BANK_MATCH.ba_recon_ce_fx_diff_handling in ('E', 'FX')) THEN
482 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
483 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
484 END IF;
485 ELSE
486 --IF (CE_AUTO_BANK_MATCH.G_foreign_difference_handling = 'G') THEN
487 IF (CE_AUTO_BANK_MATCH.ba_recon_ap_fx_diff_handling = 'G') THEN
488 --
489 -- bug# 1209738
490 -- When foreign option is set to Gain/Loss, for EMU Rate Type
491 -- do not override the exchange rate
492 --
493 IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type = 'EMU FIXED') THEN
494 CE_AUTO_BANK_MATCH.calc_csl_amount := CE_AUTO_BANK_MATCH.calc_csl_amount + difference_amount;
495 ELSE
496 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
497 IF (real_rate <> NVL(CE_AUTO_BANK_MATCH.csl_exchange_rate,real_rate+1)) THEN
498 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'User';
499 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := sysdate;
500 CE_AUTO_BANK_MATCH.csl_exchange_rate := real_rate;
501 END IF;
502 END IF;
503 ELSIF (CE_AUTO_BANK_MATCH.ba_recon_ap_fx_diff_handling = 'C') THEN
504 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_MATCH.trx_exchange_rate_type;
505 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_exchange_rate_date;
506 CE_AUTO_BANK_MATCH.csl_exchange_rate := CE_AUTO_BANK_MATCH.trx_exchange_rate;
507
508 /*
509 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
510 IF (CE_AUTO_BANK_REC.G_ce_differences_account = 'CHARGES') THEN
511 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
512 ELSE
513 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
514 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
515 END IF;
516 ELSE
517 */
518 IF (CE_AUTO_BANK_REC.G_differences_account = 'CHARGES') THEN
519 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
520 ELSE
521 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
522 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
523 END IF;
524 --END IF;
525
526 ELSIF (CE_AUTO_BANK_MATCH.ba_recon_ap_fx_diff_handling = 'N') THEN
527 CE_RECONCILIATION_ERRORS_PKG.insert_row(
528 CE_AUTO_BANK_MATCH.csh_statement_header_id,
529 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_FOREIGN_DIFFERENCE');
530 success := FALSE;
531 END IF;
532 END IF;
533 ELSE --diff amt <> csl_charges_amount
534 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
535
536 END IF;
537 --
538 -- Receipt
539 --
540 ELSE
541 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'REVERSAL') then
542 difference_amount := 0;
543 else
544 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT' AND
545 CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT') then
546 clearing_sign := -1;
547 else
548 clearing_sign := 1;
549 end if;
550 difference_amount := CE_AUTO_BANK_MATCH.trx_amount -
551 CE_AUTO_BANK_MATCH.calc_csl_amount * clearing_sign;
552 end if;
553
554 /* 2886201
555 If transaction currency amount is 0, set real_rate to 1.0 to avoid
556 Division by Zero error. */
557 IF (CE_AUTO_BANK_MATCH.trx_curr_amount = 0) THEN
558 real_rate := 1.0;
559 ELSE
560 real_rate := (CE_AUTO_BANK_MATCH.csl_amount+
561 NVL(CE_AUTO_BANK_MATCH.csl_charges_amount,0))/
562 CE_AUTO_BANK_MATCH.trx_curr_amount;
563 END IF; /* 2886201 End Code Added */
564
565 IF (difference_amount <> CE_AUTO_BANK_MATCH.csl_charges_amount) THEN
566 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
567 IF (CE_AUTO_BANK_MATCH.ba_recon_ce_fx_diff_handling = 'CH') THEN
568 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_MATCH.trx_exchange_rate_type;
569 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_exchange_rate_date;
570 CE_AUTO_BANK_MATCH.csl_exchange_rate := CE_AUTO_BANK_MATCH.trx_exchange_rate;
571
572 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
573
574 ELSIF (CE_AUTO_BANK_MATCH.ba_recon_ce_fx_diff_handling in ('E', 'FX')) THEN
575 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
576 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
577 END IF;
578 ELSE
579
580 IF (CE_AUTO_BANK_MATCH.ba_recon_ar_fx_diff_handling = 'G') THEN
581 --
582 -- bug# 1209738
583 -- When foreign option is set to Gain/Loss, for EMU Rate Type
584 -- do not override the exchange rate
585 --
586 IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type = 'EMU FIXED') THEN
587 CE_AUTO_BANK_MATCH.calc_csl_amount := CE_AUTO_BANK_MATCH.calc_csl_amount + difference_amount;
588 cep_standard.debug('****** CE_AUTO_BANK_MATCH.calc_csl_amount = '||to_char(CE_AUTO_BANK_MATCH.calc_csl_amount));
589 ELSE
590 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
591 IF (real_rate <> NVL(CE_AUTO_BANK_MATCH.csl_exchange_rate,real_rate+1)) THEN
592 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'User';
593 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := sysdate;
594 CE_AUTO_BANK_MATCH.csl_exchange_rate := real_rate;
595 END IF;
596 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
597 END IF;
598 ELSIF (CE_AUTO_BANK_MATCH.ba_recon_ar_fx_diff_handling = 'C') THEN
599 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_MATCH.trx_exchange_rate_type;
600 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_exchange_rate_date;
601 CE_AUTO_BANK_MATCH.csl_exchange_rate := CE_AUTO_BANK_MATCH.trx_exchange_rate;
602
603 /*IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
604 IF (CE_AUTO_BANK_REC.G_ce_differences_account = 'CHARGES') THEN
605 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
606 ELSE
607 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
608 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
609 END IF;
610 ELSE */
611
612 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
613 --END IF;
614 ELSIF (CE_AUTO_BANK_MATCH.ba_recon_ar_fx_diff_handling = 'N') THEN
615 CE_RECONCILIATION_ERRORS_PKG.insert_row(
616 CE_AUTO_BANK_MATCH.csh_statement_header_id,
617 CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_FOREIGN_DIFFERENCE');
618 success := FALSE;
619 END IF;
620 END IF;
621 ELSE
622 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
623 END IF;
624 END IF;
625 IF (CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL) THEN
626 CE_AUTO_BANK_MATCH.csl_exchange_rate := real_rate;
627 CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'User';
628 CE_AUTO_BANK_MATCH.csl_exchange_rate_date := sysdate;
629 END IF;
630 cep_standard.debug('CE_AUTO_BANK_CLEAR.calc_foreign_clearing_amounts');
631 END calc_foreign_clearing_amounts;
632
633 /* ---------------------------------------------------------------------
634 | PRIVATE PROCEDURE |
635 | calculate_clearing_amounts |
636 | DESCRIPTION |
637 | Calculates the error/changes/clearing |
638 | CALLS |
639 | calc_foreign_clearing_amounts |
640 | CALLED BY |
641 | reconcile_process
642 --------------------------------------------------------------------- */
643 FUNCTION calculate_clearing_amounts RETURN BOOLEAN IS
644 difference_amount NUMBER;
645 clearing_sign NUMBER;
646 success BOOLEAN;
647 BEGIN
648 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.calculate_clearing_amounts');
649 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_type = '||
650 CE_AUTO_BANK_MATCH.csl_match_type);
651 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_match_type = '||
652 CE_AUTO_BANK_MATCH.trx_match_type);
653 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_amount = '||
654 CE_AUTO_BANK_MATCH.trx_amount);
655 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_correction_type = '||
656 CE_AUTO_BANK_MATCH.csl_match_correction_type);
657 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_type = '||
658 CE_AUTO_BANK_MATCH.csl_trx_type);
659 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
660 CE_AUTO_BANK_MATCH.trx_currency_type);
661 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '||
662 CE_AUTO_BANK_MATCH.calc_csl_amount);
663
664 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '||
665 CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
666
667 success := TRUE;
668 IF (CE_AUTO_BANK_MATCH.csl_match_type = 'JE_LINE') THEN
669 NULL;
670 ELSIF ((CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI') AND
671 (CE_AUTO_BANK_REC.G_open_interface_matching_code = 'D')) THEN
672 CE_AUTO_BANK_MATCH.trx_charges_amount :=
673 CE_AUTO_BANK_MATCH.csl_charges_amount;
674 ELSIF (CE_AUTO_BANK_MATCH.tolerance_amount = 0) THEN
675 NULL;
676 ELSE
677 IF (CE_AUTO_BANK_MATCH.trx_currency_type IN ('FUNCTIONAL', 'BANK')) THEN
678 -- added 'PGROUP'
679 IF (CE_AUTO_BANK_MATCH.csl_match_type IN ('PAYMENT','PBATCH','PGROUP') OR
680 CE_AUTO_BANK_MATCH.trx_match_type = 'PAYMENT') THEN
681 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
682 clearing_sign := -1;
683 else
684 clearing_sign := 1;
685 end if;
686 difference_amount := CE_AUTO_BANK_MATCH.calc_csl_amount * clearing_sign
687 - CE_AUTO_BANK_MATCH.trx_amount;
688
689 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
690 IF (CE_AUTO_BANK_REC.G_ce_differences_account = 'CHARGES') THEN
691 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
692 ELSE
693 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
694 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
695 END IF;
696
697 ELSE
698 IF (CE_AUTO_BANK_REC.G_differences_account = 'CHARGES') THEN
699 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
700 ELSE
701 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
702 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
703 END IF;
704 END IF;
705
706 ELSE -- cash/receipt
707 if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_DEBIT' AND
708 CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT') then
709 clearing_sign := -1;
710 else
711 clearing_sign := 1;
712 end if;
713 difference_amount := CE_AUTO_BANK_MATCH.trx_amount
714 - CE_AUTO_BANK_MATCH.calc_csl_amount * clearing_sign;
715
716 IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
717 IF (CE_AUTO_BANK_REC.G_ce_differences_account = 'CHARGES') THEN
718 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
719 ELSE
720 CE_AUTO_BANK_MATCH.trx_charges_amount := CE_AUTO_BANK_MATCH.csl_charges_amount;
721 CE_AUTO_BANK_MATCH.trx_errors_amount := difference_amount - CE_AUTO_BANK_MATCH.csl_charges_amount;
722 END IF;
723 ELSE
724 CE_AUTO_BANK_MATCH.trx_charges_amount := difference_amount;
725 END IF;
726 END IF;
727 ELSIF (CE_AUTO_BANK_MATCH.trx_currency_type = 'FOREIGN') THEN
728 calc_foreign_clearing_amounts(success);
729 END IF;
730 END IF;
731 --
732 -- Zero equals to NULL
733 --
734 IF (CE_AUTO_BANK_MATCH.trx_errors_amount = 0) THEN
735 CE_AUTO_BANK_MATCH.trx_errors_amount := NULL;
736 END IF;
737 IF (CE_AUTO_BANK_MATCH.trx_charges_amount = 0) THEN
738 CE_AUTO_BANK_MATCH.trx_charges_amount := NULL;
739 END IF;
740 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_charges_amount = '||
741 CE_AUTO_BANK_MATCH.trx_charges_amount);
742
743 cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_errors_amount = '||
744 CE_AUTO_BANK_MATCH.trx_errors_amount);
745
746 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.calculate_clearing_amounts');
747 RETURN (success);
748 EXCEPTION
749 WHEN OTHERS THEN
750 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR.calculate_clearing_amounts' );
751 RAISE;
752 END calculate_clearing_amounts;
753
754 /* ---------------------------------------------------------------------
755 | PRIVATE PROCEDURE |
756 | trx_remain |
757 | DESCRIPTION |
758 |
759 | CALLS |
760 | Manual unReconciliation only |
761 | call from form - RECONCILED_PAY_EFT.UNRECON_ALL_EFT |
762 --------------------------------------------------------------------- */
763 FUNCTION trx_remain( stmt_ln_list VARCHAR2,
764 trx_id_list VARCHAR2) RETURN NUMBER IS
765 tmp_query varchar2(1000) := null;
766 count_pay_eft1 NUMBER;
767 cursor_id INTEGER;
768 exec_id INTEGER;
769
770 BEGIN
771 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.trx_remain' );
772 tmp_query := 'select count(*)
773 from ce_801_eft_reconciled_v
774 where clearing_trx_type = ''PAY_EFT''
775 and to_char(statement_line_id) in (' || stmt_ln_list ||')
776 and to_char(trx_id) not in (' || trx_id_list || ')';
777
778 cep_standard.debug('created tmp_query = '|| tmp_query);
779 cep_standard.debug('open_cursor');
780
781 cursor_id := DBMS_SQL.open_cursor;
782 cep_standard.debug('Cursor opened sucessfully with cursor_id: '||
783 to_char(cursor_id));
784
785 cep_standard.debug('parse sql');
786 --DBMS_SQL.Parse(cursor_id, tmp_query, DBMS_SQL.native);
787 DBMS_SQL.Parse(cursor_id, tmp_query, DBMS_SQL.v7);
788
789 cep_standard.debug('define column');
790 DBMS_SQL.Define_Column(cursor_id, 1, count_pay_eft1);
791
792 cep_standard.debug('execute cursor');
793 exec_id := dbms_sql.execute(cursor_id);
794
795 cep_standard.debug('column_value');
796
797 IF (DBMS_SQL.FETCH_ROWS(cursor_id) >0 ) THEN
798 DBMS_SQL.COLUMN_VALUE(cursor_id, 1, count_pay_eft1);
799 END IF;
800
801 cep_standard.debug('count_pay_eft1 = '|| count_pay_eft1);
802 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.trx_remain' );
803
804 return count_pay_eft1;
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 cep_standard.debug('EXCEPTION - OTHERS: CE_AUTO_BANK_CLEAR.trx_remain' );
809 IF DBMS_SQL.IS_OPEN(cursor_id) THEN
810 DBMS_SQL.CLOSE_CURSOR(cursor_id);
811 cep_standard.debug('Cursor Closed');
812 END IF;
813 RAISE;
814 END trx_remain;
815
816
817 /* ---------------------------------------------------------------------
818 | PRIVATE PROCEDURE |
819 | reconcile_stmt |
820 | CALLED BY |
821 | reconcile_process |
822 --------------------------------------------------------------------- */
823 PROCEDURE reconcile_stmt(passin_mode VARCHAR2,
824 tx_type VARCHAR2,
825 trx_id NUMBER,
826 trx_status VARCHAR2,
827 receipt_type VARCHAR2,
828 exchange_rate_type VARCHAR2,
829 exchange_date DATE,
830 exchange_rate NUMBER,
831 amount_cleared NUMBER,
832 charges_amount NUMBER,
833 errors_amount NUMBER,
834 gl_date DATE,
835 value_date DATE,
836 cleared_date DATE,
837 ar_cash_receipt_id NUMBER,
838 X_bank_currency VARCHAR2,
839 X_statement_line_id IN OUT NOCOPY NUMBER,
840 X_statement_line_type VARCHAR2,
841 reference_status VARCHAR2,
842 trx_currency_type VARCHAR2,
843 auto_reconciled_flag VARCHAR2,
844 X_statement_header_id IN OUT NOCOPY NUMBER,
845 X_effective_date DATE DEFAULT NULL,
846 X_float_handling_flag VARCHAR2 DEFAULT NULL,
847 X_currency_code VARCHAR2 default NULL,
848 X_bank_trx_number VARCHAR2 default NULL,
849 X_reversed_receipt_flag VARCHAR2) IS
850 BEGIN
851 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reconcile_stmt');
852 CE_AUTO_BANK_CLEAR1.reconcile_stmt(
853 passin_mode,
854 tx_type,
855 trx_id,
856 trx_status,
857 receipt_type,
858 exchange_rate_type,
859 exchange_date,
860 exchange_rate,
861 amount_cleared,
862 charges_amount,
863 errors_amount,
864 gl_date,
865 value_date,
866 cleared_date,
867 ar_cash_receipt_id,
868 X_bank_currency,
869 X_statement_line_id,
870 X_statement_line_type,
871 reference_status,
872 trx_currency_type,
873 auto_reconciled_flag,
874 X_statement_header_id,
875 X_effective_date,
876 X_float_handling_flag,
877 X_currency_code,
878 X_bank_trx_number,
879 X_reversed_receipt_flag);
880 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reconcile_stmt');
881 END reconcile_stmt;
882
883 /* ---------------------------------------------------------------------
884 | PRIVATE PROCEDURE |
885 | reconcile_trx |
886 | CALLED BY |
887 | reconcile_process |
888 --------------------------------------------------------------------- */
889 PROCEDURE reconcile_trx( passin_mode VARCHAR2,
890 tx_type VARCHAR2,
891 trx_id NUMBER,
892 trx_status VARCHAR2,
893 receipt_type VARCHAR2,
894 exchange_rate_type VARCHAR2,
895 exchange_date DATE,
896 exchange_rate NUMBER,
897 amount_cleared NUMBER,
898 charges_amount NUMBER,
899 errors_amount NUMBER,
900 gl_date DATE,
901 value_date DATE,
902 cleared_date DATE,
903 ar_cash_receipt_id NUMBER,
904 X_bank_currency VARCHAR2,
905 X_statement_line_id IN OUT NOCOPY NUMBER,
906 X_statement_line_type VARCHAR2,
907 reference_status VARCHAR2,
908 trx_currency_type VARCHAR2,
909 auto_reconciled_flag VARCHAR2,
910 X_statement_header_id IN OUT NOCOPY NUMBER,
911 X_statement_header_date DATE,
912 X_bank_trx_number VARCHAR2,
913 X_currency_code VARCHAR2,
914 X_original_amount NUMBER,
915 X_effective_date DATE,
916 X_float_handling_flag VARCHAR2,
917 X_reversed_receipt_flag VARCHAR2,
918 X_org_id NUMBER DEFAULT NULL,
919 X_legal_entity_id NUMBER DEFAULT NULL) IS
920 BEGIN
921 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reconcile_trx');
922 CE_AUTO_BANK_CLEAR1.reconcile_trx(
923 passin_mode,
924 tx_type,
925 trx_id,
926 trx_status,
927 receipt_type,
928 exchange_rate_type,
929 exchange_date,
930 exchange_rate,
931 amount_cleared,
932 charges_amount,
933 errors_amount,
934 gl_date,
935 value_date,
936 cleared_date,
937 ar_cash_receipt_id,
938 X_bank_currency,
939 X_statement_line_id,
940 X_statement_line_type,
941 reference_status,
942 trx_currency_type,
943 auto_reconciled_flag,
944 X_statement_header_id,
945 X_statement_header_date,
946 X_bank_trx_number,
947 X_currency_code,
948 X_original_amount,
949 X_effective_date,
950 X_float_handling_flag,
951 X_reversed_receipt_flag,
952 X_org_id,
953 X_legal_entity_id);
954 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reconcile_trx');
955 END reconcile_trx;
956
957 /* ---------------------------------------------------------------------
958 | PRIVATE PROCEDURE |
959 | reconcile_pay_eft |
960 | CALLED BY |
961 | reconcile_process |
962 --------------------------------------------------------------------- */
963 PROCEDURE reconcile_pay_eft( passin_mode VARCHAR2,
964 tx_type VARCHAR2,
965 trx_count NUMBER,
966 trx_group VARCHAR2,
967 cleared_trx_type VARCHAR2,
968 cleared_date DATE,
969 X_bank_currency VARCHAR2,
970 X_statement_line_id NUMBER,
971 X_statement_line_type VARCHAR2,
972 trx_currency_type VARCHAR2,
973 auto_reconciled_flag VARCHAR2,
974 X_statement_header_id NUMBER,
975 X_bank_trx_number VARCHAR2,
976 X_bank_account_id NUMBER,
977 X_payroll_payment_format VARCHAR2,
978 X_effective_date DATE,
979 X_float_handling_flag VARCHAR2) IS
980 BEGIN
981 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reconcile_pay_eft');
982 CE_AUTO_BANK_CLEAR1.reconcile_pay_eft(
983 passin_mode,
984 tx_type,
985 trx_count,
986 trx_group,
987 cleared_trx_type,
988 cleared_date,
989 X_bank_currency,
990 X_statement_line_id,
991 X_statement_line_type,
992 trx_currency_type,
993 auto_reconciled_flag,
994 X_statement_header_id,
995 X_bank_trx_number,
996 X_bank_account_id,
997 X_payroll_payment_format,
998 X_effective_date,
999 X_float_handling_flag);
1000 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reconcile_pay_eft');
1001 END reconcile_pay_eft;
1002
1003 /* ---------------------------------------------------------------------
1004 | PRIVATE PROCEDURE |
1005 | unclear_process |
1006 | DESCRIPTION |
1007 | Unclear and unreconcile the reconcilied statement line. |
1008 | CALLED BY |
1009 | This piece of code is called only from Manual Reconciliation |
1010 --------------------------------------------------------------------- */
1011 PROCEDURE unclear_process ( passin_mode VARCHAR2,
1012 X_header_or_line VARCHAR2,
1013 tx_type VARCHAR2,
1014 clearing_trx_type VARCHAR2,
1015 batch_id NUMBER,
1016 trx_id NUMBER,
1017 cash_receipt_id NUMBER,
1018 trx_date DATE,
1019 gl_date DATE,
1020 cash_receipt_history_id IN OUT NOCOPY NUMBER,
1021 stmt_line_id NUMBER,
1022 status VARCHAR2,
1023 cleared_date DATE,
1024 transaction_amount NUMBER,
1025 error_amount NUMBER,
1026 charge_amount NUMBER,
1027 currency_code VARCHAR2,
1028 xtype VARCHAR2,
1029 xdate DATE,
1030 xrate NUMBER,
1031 org_id NUMBER,
1032 legal_entity_id NUMBER ) IS
1033 BEGIN
1034 CE_AUTO_BANK_CLEAR1.unclear_process(
1035 passin_mode ,
1036 X_header_or_line ,
1037 tx_type ,
1038 clearing_trx_type ,
1039 batch_id ,
1040 trx_id ,
1041 cash_receipt_id ,
1042 trx_date ,
1043 gl_date ,
1044 cash_receipt_history_id ,
1045 stmt_line_id ,
1046 status ,
1047 cleared_date ,
1048 transaction_amount ,
1049 error_amount ,
1050 charge_amount ,
1051 currency_code ,
1052 xtype ,
1053 xdate ,
1054 xrate ,
1055 org_id ,
1056 legal_entity_id);
1057
1058 END unclear_process;
1059
1060 /* ---------------------------------------------------------------------
1061 | PRIVATE PROCEDURE |
1062 | reconcile_process |
1063 | DESCRIPTION |
1064 | Clear and reconcile the matched statement line. |
1065 | CALLS |
1066 | calculate_clearing_amounts
1067 | CALLED BY |
1068 | CE_AUTO_BANK_MATCH.match_process |
1069 --------------------------------------------------------------------- */
1070 PROCEDURE reconcile_process IS
1071 encoded_message VARCHAR2(255);
1072 message_name VARCHAR2(50);
1073 app_short_name VARCHAR2(30);
1074 d_statement_header_id CE_STATEMENT_HEADERS.statement_header_id%TYPE;
1075 misc_number AR_CASH_RECEIPTS_ALL.receipt_number%TYPE;
1076 BEGIN
1077 cep_standard.debug('>>CE_AUTO_BANK_CLEAR.reconcile_process');
1078 --
1079 -- Statement lines
1080 --
1081 IF (CE_AUTO_BANK_MATCH.csl_match_type = 'MISC' AND
1082 CE_AUTO_BANK_MATCH.csl_match_correction_type IN
1083 ('REVERSAL', 'ADJUSTMENT')) THEN
1084 IF (calculate_clearing_amounts) THEN
1085 reconcile_stmt(
1086 passin_mode => 'AUTO',
1087 tx_type => CE_AUTO_BANK_MATCH.csl_match_type,
1088 trx_id => CE_AUTO_BANK_MATCH.trx_id,
1089 trx_status => CE_AUTO_BANK_MATCH.trx_status,
1090 receipt_type => CE_AUTO_BANK_MATCH.csl_reconcile_flag,
1091 exchange_rate_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
1092 exchange_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1093 exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate,
1094 amount_cleared => CE_AUTO_BANK_MATCH.calc_csl_amount,
1095 charges_amount => CE_AUTO_BANK_MATCH.trx_charges_amount,
1096 errors_amount => CE_AUTO_BANK_MATCH.trx_errors_amount,
1097 gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1098 value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1099 cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1100 ar_cash_receipt_id => CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
1101 X_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
1102 X_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
1103 X_statement_line_type => CE_AUTO_BANK_MATCH.csl_line_trx_type,
1104 reference_status => NULL,
1105 trx_currency_type => CE_AUTO_BANK_MATCH.trx_currency_type,
1106 auto_reconciled_flag => 'Y',
1107 X_statement_header_id => d_statement_header_id,
1108 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1109 X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag,
1110 X_currency_code => CE_AUTO_BANK_MATCH.trx_currency_code,
1111 X_reversed_receipt_flag => CE_AUTO_BANK_MATCH.reversed_receipt_flag);
1112 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := 'Y';
1113 if (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'REVERSAL') then
1114 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.trx_id,
1115 'RECONCILED');
1116 else /* ADJUSTMENT */
1117 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.trx_id2,
1118 'RECONCILED');
1119 end if;
1120 CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := NULL;
1121 CE_AUTO_BANK_CLEAR.update_line_status(
1122 CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
1123 END IF;
1124 --
1125 -- Transaction
1126 --
1127 ELSIF (CE_AUTO_BANK_MATCH.csl_match_type IN
1128 ('PAY_LINE', 'JE_LINE','PAYMENT','CASH','MISC','RECEIPT')) THEN
1129 IF (calculate_clearing_amounts) THEN
1130 reconcile_trx(
1131 passin_mode => 'AUTO',
1132 tx_type => CE_AUTO_BANK_MATCH.csl_match_type,
1133 trx_id => CE_AUTO_BANK_MATCH.trx_id,
1134 trx_status => CE_AUTO_BANK_MATCH.trx_status,
1135 receipt_type => CE_AUTO_BANK_MATCH.csl_reconcile_flag,
1136 exchange_rate_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
1137 exchange_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1138 exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate,
1139 amount_cleared => CE_AUTO_BANK_MATCH.calc_csl_amount,
1140 charges_amount => CE_AUTO_BANK_MATCH.trx_charges_amount,
1141 errors_amount => CE_AUTO_BANK_MATCH.trx_errors_amount,
1142 gl_date => to_date(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1143 value_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1144 cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1145 ar_cash_receipt_id => CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
1146 X_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
1147 X_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
1148 X_statement_line_type => CE_AUTO_BANK_MATCH.csl_line_trx_type,
1149 reference_status => NULL,
1150 trx_currency_type => CE_AUTO_BANK_MATCH.trx_currency_type,
1151 X_currency_code => CE_AUTO_BANK_MATCH.trx_currency_code,
1152 auto_reconciled_flag => 'Y',
1153 X_statement_header_id => d_statement_header_id,
1154 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1155 X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag,
1156 X_reversed_receipt_flag => CE_AUTO_BANK_MATCH.reversed_receipt_flag);
1157 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
1158 END IF;
1159 --
1160 -- Payroll EFT Transaction
1161 --
1162 ELSIF (CE_AUTO_BANK_MATCH.csl_match_type = 'PAY_EFT') THEN
1163 IF (calculate_clearing_amounts) THEN
1164 reconcile_pay_eft(
1165 passin_mode => 'AUTO',
1166 tx_type => CE_AUTO_BANK_MATCH.csl_match_type, --PAY_EFT
1167 trx_count => CE_AUTO_BANK_MATCH.trx_count,
1168 trx_group => CE_AUTO_BANK_MATCH.trx_group,
1169 cleared_trx_type => CE_AUTO_BANK_MATCH.csl_reconcile_flag,
1170 cleared_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1171 X_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
1172 X_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
1173 X_statement_line_type => CE_AUTO_BANK_MATCH.csl_line_trx_type,
1174 trx_currency_type => CE_AUTO_BANK_MATCH.trx_currency_type,
1175 auto_reconciled_flag => 'Y',
1176 X_statement_header_id => d_statement_header_id,
1177 X_bank_trx_number => CE_AUTO_BANK_MATCH.csl_bank_trx_number,
1178 X_bank_account_id => CE_AUTO_BANK_MATCH.csh_bank_account_id,
1179 X_payroll_payment_format => CE_AUTO_BANK_MATCH.csl_payroll_payment_format,
1180 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1181 X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag);
1182 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
1183 END IF;
1184 --
1185 -- Payment Batch
1186 --
1187 ELSIF (CE_AUTO_BANK_MATCH.csl_match_type = 'PBATCH') THEN
1188 IF (calculate_clearing_amounts) THEN
1189 reconcile_pbatch(
1190 passin_mode => 'AUTO',
1191 pbatch_id => CE_AUTO_BANK_MATCH.trx_id,
1192 statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
1193 gl_date => TO_DATE(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD' ),'YYYY/MM/DD'),
1194 value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1195 cleared_date => TO_DATE(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1196 amount_to_clear => CE_AUTO_BANK_MATCH.calc_csl_amount,
1197 errors_amount => CE_AUTO_BANK_MATCH.trx_errors_amount,
1198 charges_amount => CE_AUTO_BANK_MATCH.trx_charges_amount,
1199 prorate_amount => CE_AUTO_BANK_MATCH.trx_prorate_amount,
1200 exchange_rate_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
1201 exchange_rate_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1202 exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate,
1203 trx_currency_type => CE_AUTO_BANK_MATCH.trx_currency_type,
1204 X_statement_header_id => d_statement_header_id,
1205 X_currency_code => CE_AUTO_BANK_MATCH.trx_currency_code,
1206 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1207 X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag,
1208 X_bank_currency_code => CE_AUTO_BANK_MATCH.aba_bank_currency);
1209
1210 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
1211 END IF;
1212 --
1213 -- Payment Group
1214 --
1215 ELSIF (CE_AUTO_BANK_MATCH.csl_match_type = 'PGROUP') THEN
1216 IF (calculate_clearing_amounts) THEN
1217 reconcile_pbatch(
1218 passin_mode => 'AUTO',
1219 pbatch_id => CE_AUTO_BANK_MATCH.trx_id,
1220 statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
1221 gl_date => TO_DATE(to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD' ),'YYYY/MM/DD'),
1222 value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1223 cleared_date => TO_DATE(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1224 amount_to_clear => CE_AUTO_BANK_MATCH.calc_csl_amount,
1225 errors_amount => CE_AUTO_BANK_MATCH.trx_errors_amount,
1226 charges_amount => CE_AUTO_BANK_MATCH.trx_charges_amount,
1227 prorate_amount => CE_AUTO_BANK_MATCH.trx_prorate_amount,
1228 exchange_rate_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
1229 exchange_rate_date => to_date(to_char(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
1230 'YYYY/MM/DD'),'YYYY/MM/DD'),
1231 exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate,
1232 trx_currency_type => CE_AUTO_BANK_MATCH.trx_currency_type,
1233 X_statement_header_id => d_statement_header_id,
1234 X_currency_code => CE_AUTO_BANK_MATCH.trx_currency_code,
1235 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1236 X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag,
1237 X_bank_currency_code => CE_AUTO_BANK_MATCH.aba_bank_currency,
1238 pgroup_id => CE_AUTO_BANK_MATCH.LOGICAL_GROUP_REFERENCE);
1239 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
1240 END IF;
1241 --
1242 -- Remittance Batch
1243 --
1244 ELSIF (CE_AUTO_BANK_MATCH.csl_match_type = 'RBATCH') THEN
1245 IF (calculate_clearing_amounts) THEN
1246 misc_number := nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,
1247 CE_AUTO_BANK_MATCH.csh_statement_number||'/'
1248 || CE_AUTO_BANK_MATCH.csl_line_number);
1249 reconcile_rbatch(
1250 passin_mode => 'AUTO',
1251 rbatch_id => CE_AUTO_BANK_MATCH.trx_id,
1252 X_statement_line_id => CE_AUTO_BANK_MATCH.csl_statement_line_id,
1253 gl_date => CE_AUTO_BANK_REC.G_gl_date,
1254 value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1255 bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
1256 exchange_rate_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
1257 exchange_rate => CE_AUTO_BANK_MATCH.csl_exchange_rate,
1258 exchange_rate_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
1259 trx_currency_type => CE_AUTO_BANK_MATCH.trx_currency_type,
1260 module => 'CE_AUTO_BANK_CLEAR',
1261 X_TRX_NUMBER => misc_number,
1262 X_TRX_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1263 X_DEPOSIT_DATE => to_date(to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'),'YYYY/MM/DD'),
1264 X_AMOUNT => -CE_AUTO_BANK_MATCH.trx_charges_amount,
1265 X_FOREIGN_DIFF_AMT => CE_AUTO_BANK_MATCH.trx_prorate_amount,
1266 X_SET_OF_BOOKS_ID => CE_AUTO_BANK_REC.G_set_of_books_id,
1267 X_MISC_CURRENCY_CODE => CE_AUTO_BANK_MATCH.aba_bank_currency,
1268 X_RECEIPT_METHOD_ID => CE_AUTO_BANK_REC.G_payment_method_id,
1269 X_BANK_ACCOUNT_ID => CE_AUTO_BANK_MATCH.csh_bank_account_id,
1270 X_ACTIVITY_TYPE_ID => CE_AUTO_BANK_REC.G_receivables_trx_id,
1271 X_COMMENTS => 'Created by Auto Bank Rec',
1272 X_REFERENCE_TYPE => 'REMITTANCE BATCH',
1273 X_CLEAR_CURRENCY_CODE => CE_AUTO_BANK_MATCH.aba_bank_currency,
1274 X_TAX_ID => NULL,
1275 X_TAX_RATE => NULL,
1276 X_CR_VAT_TAX_ID => CE_AUTO_BANK_REC.G_cr_vat_tax_code,
1277 X_DR_VAT_TAX_ID => CE_AUTO_BANK_REC.G_dr_vat_tax_code,
1278 X_statement_header_id => d_statement_header_id,
1279 X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
1280 X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag);
1281 CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
1282 END IF;
1283 END IF;
1284 cep_standard.debug('<<CE_AUTO_BANK_CLEAR.reconcile_process');
1285 EXCEPTION
1286 WHEN APP_EXCEPTION.application_exception THEN
1287 encoded_message := FND_MESSAGE.GET_ENCODED;
1288 IF (encoded_message IS NOT NULL) THEN
1289 FND_MESSAGE.parse_encoded(encoded_message,app_short_name,message_name);
1290 END IF;
1291 IF (message_name IS NULL) THEN
1292 app_short_name := 'CE';
1293 message_name := 'OTHER_APP_ERROR';
1294 END IF;
1295 CE_RECONCILIATION_ERRORS_PKG.insert_row(
1296 CE_AUTO_BANK_MATCH.csh_statement_header_id,
1297 CE_AUTO_BANK_MATCH.csl_statement_line_id, message_name, app_short_name);
1298 --
1299 -- get rid of any lines that might have been inserted into
1300 -- the reconciliation tables. This happens when lines per commit
1301 -- is not zero
1302 --
1303 --DELETE FROM ce_statement_reconciliations
1304 DELETE FROM ce_statement_reconcils_all
1305 WHERE statement_line_id = CE_AUTO_BANK_MATCH.csl_statement_line_id;
1306 WHEN OTHERS THEN
1307 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR.reconcile_process OTHERS');
1308 /*
1309 IF (rbatch_cursor%ISOPEN) THEN
1310 CLOSE rbatch_cursor;
1311 END IF;
1312 IF (pbatch_cursor%ISOPEN) THEN
1313 CLOSE pbatch_cursor;
1314 END IF;
1315 */
1316 RAISE;
1317 END reconcile_process;
1318
1319 END CE_AUTO_BANK_CLEAR;