[Home] [Help]
PACKAGE BODY: APPS.AP_RECONCILIATION_PKG
Source
1 PACKAGE BODY AP_RECONCILIATION_PKG as
2 /* $Header: apreconb.pls 120.22 2006/12/20 20:16:15 lxzhang noship $ */
3
4 --============================================================================
5 -- POSSIBLE SCENARIOS ON HOW THIS API MAY BE CALLED:
6 -- 1) PAYMENT MATURITY
7 -- In this case we insert a record into the AP_PAYMENT_HISTORY which
8 -- contains all information in currency of payment.
9 -- 2) PAYMENT CLEARING WITH RECONCILIATION ACCOUNTING OFF
10 -- In this case we simply update the AP_CHECKS and no entry is generated
11 -- for AP_PAYMENT_HISTORY. The amounts need to be properly converted
12 -- to the payment currency.
13 -- 3) PAYMENT CLEARING WITH RECONCILIATION ACCOUNTING ON
14 -- Same as 2
15 -- 4) PAYMENT RECONCILIATION WITH RECONCILIATION ACCOUNTING OFF
16 -- Same as 2
17 -- 5) PAYMENT RECONCILIATION WITH RECONCILIATION ACCOUNTING ON
18 -- Same as 2 + Creation of entry into the AP_PAYMENT_HISTORY table.
19 -- NOTE: Entry into AP_PAYMENT_HISTORY table will be in the recon currency.
20 --
21 -- TERMINOLOGY:
22 -- 1) PAYMENT MATURITY:
23 -- TRANSACTION_TYPE = PAYMENT MATURITY
24 -- MATCHED_FLAG is irrelevant
25 -- 2) PAYMENT CLEARING:
26 -- TRANSACTION_TYPE = PAYMENT CLEARING
27 -- MATCHED_FLAG = N (i.e. clearing not matched to bank statement)
28 -- 3) PAYMENT RECONCILIATION:
29 -- TRANSACTION_TYPE = PAYMENT CLEARING
30 -- MATCHED_FLAG = Y (i.e. clearing matched to bank statement)
31 --
32 -- WHAT THIS API RECEIVES FROM CASH MANAGEMENT
33 -- 1) Transaction, Error and Charge Amounts in Bank Currency
34 -- 2) Bank Currency Code
35 -- 3) Exchange rate, date and type between Pmt. Currency and Functional Currency
36 -- (this exchange info is sufficient 'coz :
37 -- DOMESTIC: All involved currencies are the same (so it does not matter)
38 -- FOREIGN: Bank and Pmt. currencies are the same. Hence the rate between
39 -- pmt. and functional is enough.
40 -- INTERNATIONAL: Bank and Functional currencies are the same. Hence, the
41 -- only needed x-rate is between pmt. and functional.
42 -- )
43 -- WHAT THIS API RECEIVES FROM AP for MATURITY
44 -- 1) Transaction Amount in Pmt. Currency
45 -- 2) Pmt. Currency Code
46 -- 3) Exchange rate, date and type between Pmt. Currency and Functional Currency
47 --
48 -- POSSIBLE TRANSACTION CONTEXTS:
49 -- 1) Payment batch.
50 -- In this case we prorate errors and charges across
51 -- all checks in the batch with any remaining amount going to the
52 -- largest. For the cleared amount in AP_CHECKS, we use the check amount +
53 -- prorated errors and charges converted to payment currency
54 -- 2) Check
55 -- In this case all trx_amount, errors and charges converted to payment
56 -- currency go to the check.
57 --
58 -- POSSIBLE CURRENCY SCENARIOS:
59 -- 1) DOMESTIC:
60 -- Recon currency = Payment currency = Functional currency
61 -- 2) INTERNATIONAL
62 -- Recon currency = Functional currency <> Payment currency
63 -- 3) FOREIGN
64 -- Recon currency = Payment currency <> Functional currency
65 -- 4) CROSS CURRENCY (not currently supported in AP but can happen in MRC)
66 -- Recon currency <> Payment currency <> Functional currency
67 -- Recon currency - EMU FIXED - Payment currency
68 --
69 --============================================================================
70
71 -- Global exception
72 G_abort_it EXCEPTION;
73
74 /* *************************************************************************
75 * RECON_PAYMENT_HISTORY : Reconciliation API to clear or reconcile a *
76 * check. Transaction amount parameter though is not used for actual *
77 * update but actual check amount is used. *
78 ************************************************************************* */
79
80 PROCEDURE Recon_Payment_History(
81 X_CHECKRUN_ID NUMBER,
82 X_CHECK_ID NUMBER,
83 X_ACCOUNTING_DATE DATE,
84 X_CLEARED_DATE DATE,
85 X_TRANSACTION_AMOUNT NUMBER, -- in bank curr.
86 X_TRANSACTION_TYPE VARCHAR2,
87 X_ERROR_AMOUNT NUMBER, -- in bank curr.
88 X_CHARGE_AMOUNT NUMBER, -- in bank curr.
89 X_CURRENCY_CODE VARCHAR2, -- bank curr. code
90 X_EXCHANGE_RATE_TYPE VARCHAR2, -- between payment and functional
91 X_EXCHANGE_RATE_DATE DATE, -- between payment and functional
92 X_EXCHANGE_RATE NUMBER, -- between payment and functional
93 X_MATCHED_FLAG VARCHAR2,
94 X_ACTUAL_VALUE_DATE DATE,
95 X_LAST_UPDATE_DATE DATE,
96 X_LAST_UPDATED_BY NUMBER,
97 X_LAST_UPDATE_LOGIN NUMBER,
98 X_CREATED_BY NUMBER,
99 X_CREATION_DATE DATE,
100 X_PROGRAM_UPDATE_DATE DATE,
101 X_PROGRAM_APPLICATION_ID NUMBER,
102 X_PROGRAM_ID NUMBER,
103 X_REQUEST_ID NUMBER,
104 X_CALLING_SEQUENCE VARCHAR2
105 ) AS
106 current_calling_sequence VARCHAR2(2000);
107 l_Trx_Bank_Amount AP_PAYMENT_HISTORY.Trx_Bank_Amount%TYPE;
108 l_Errors_Bank_Amount AP_PAYMENT_HISTORY.Errors_Bank_Amount%TYPE;
109 l_Charges_Bank_Amount AP_PAYMENT_HISTORY.Charges_Bank_Amount%TYPE;
110 l_Bank_Currency_Code AP_PAYMENT_HISTORY.Bank_Currency_Code%TYPE;
111 l_Pmt_to_Base_Xrate_Type AP_PAYMENT_HISTORY.Pmt_to_Base_Xrate_Type%TYPE;
112 l_Pmt_to_Base_Xrate_Date AP_PAYMENT_HISTORY.Pmt_to_Base_Xrate_Date%TYPE;
113 l_Pmt_to_Base_Xrate AP_PAYMENT_HISTORY.Pmt_to_Base_Xrate%TYPE;
114 l_debug_info VARCHAR2(240);
115 BEGIN
116
117 current_calling_sequence := X_CALLING_SEQUENCE ||
118 'AP_RECONCILIATION_PKG.RECON_PAYMENT_HISTORY' ;
119
120 -- Check if all required data is passed
121
122 l_debug_info := 'Check for required info';
123
124 IF ( ( X_TRANSACTION_AMOUNT IS NULL ) or
125 ( X_TRANSACTION_TYPE IS NULL ) or
126 ( X_CURRENCY_CODE IS NULL ) or
127 ( X_CREATION_DATE IS NULL ) or
128 ( X_CREATED_BY IS NULL ) or
129 ( X_LAST_UPDATE_DATE IS NULL ) or
130 ( X_LAST_UPDATED_BY IS NULL ) ) THEN
131 APP_EXCEPTION.RAISE_EXCEPTION ;
132 END IF ;
133
134 l_Trx_Bank_Amount := X_TRANSACTION_AMOUNT;
135 l_Errors_Bank_Amount := X_ERROR_AMOUNT;
136 l_Charges_Bank_Amount := X_CHARGE_AMOUNT;
137 l_Bank_Currency_Code := X_CURRENCY_CODE;
138 l_Pmt_to_Base_Xrate_Type := X_EXCHANGE_RATE_TYPE;
139 l_Pmt_to_Base_Xrate_Date := X_EXCHANGE_RATE_DATE;
140 l_Pmt_to_Base_Xrate := X_EXCHANGE_RATE;
141
142 IF X_TRANSACTION_TYPE IN ('PAYMENT MATURITY' ,
143 'PAYMENT MATURITY REVERSAL' ) THEN
144 l_debug_info := 'Payment Maturity';
145
146 AP_RECONCILIATION_PKG.Recon_Payment_Maturity
147 ( X_CHECK_ID,
148 X_ACCOUNTING_DATE,
149 X_TRANSACTION_TYPE,
150 X_TRANSACTION_AMOUNT,
151 X_CURRENCY_CODE,
152 X_EXCHANGE_RATE_TYPE,
153 X_EXCHANGE_RATE_DATE,
154 X_EXCHANGE_RATE,
155 X_LAST_UPDATE_DATE,
156 X_LAST_UPDATED_BY,
157 X_LAST_UPDATE_LOGIN,
158 X_CREATED_BY,
159 X_CREATION_DATE,
160 X_PROGRAM_UPDATE_DATE,
161 X_PROGRAM_APPLICATION_ID,
162 X_PROGRAM_ID,
163 X_REQUEST_ID,
164 X_CALLING_SEQUENCE ) ;
165
166 ELSIF X_TRANSACTION_TYPE IN ('PAYMENT CLEARING' ) THEN
167
168 l_debug_info := 'Payment Clearing';
169 AP_RECONCILIATION_PKG.Recon_Payment_Clearing
170 ( X_CHECKRUN_ID,
171 X_CHECK_ID,
172 X_ACCOUNTING_DATE,
173 X_CLEARED_DATE,
174 X_TRANSACTION_TYPE,
175 l_Trx_Bank_Amount,
176 l_Errors_Bank_Amount,
177 l_Charges_Bank_Amount,
178 l_Bank_Currency_Code,
179 l_Pmt_to_Base_Xrate_Type,
180 l_Pmt_to_Base_Xrate_Date,
181 l_Pmt_to_Base_Xrate,
182 X_MATCHED_FLAG,
183 X_ACTUAL_VALUE_DATE,
184 X_LAST_UPDATE_DATE,
185 X_LAST_UPDATED_BY,
186 X_LAST_UPDATE_LOGIN,
187 X_CREATED_BY,
188 X_CREATION_DATE,
189 X_PROGRAM_UPDATE_DATE,
190 X_PROGRAM_APPLICATION_ID,
191 X_PROGRAM_ID,
192 X_REQUEST_ID,
193 X_CALLING_SEQUENCE ) ;
194
195 ELSIF X_TRANSACTION_TYPE IN ('PAYMENT UNCLEARING' ) THEN
196 l_debug_info := 'Payment Unclearing';
197 AP_RECONCILIATION_PKG.Recon_Payment_Unclearing
198 ( X_CHECKRUN_ID,
199 X_CHECK_ID,
200 X_ACCOUNTING_DATE,
201 X_TRANSACTION_TYPE,
202 X_MATCHED_FLAG,
203 X_LAST_UPDATE_DATE,
204 X_LAST_UPDATED_BY,
205 X_LAST_UPDATE_LOGIN,
206 X_CREATED_BY,
207 X_CREATION_DATE,
208 X_PROGRAM_UPDATE_DATE,
209 X_PROGRAM_APPLICATION_ID,
210 X_PROGRAM_ID,
211 X_REQUEST_ID,
212 X_CALLING_SEQUENCE ) ;
213
214 ELSE
215 APP_EXCEPTION.RAISE_EXCEPTION ;
216 END IF ;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 IF (SQLCODE <> -20001) THEN
221 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
222 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
223 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
224 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
225 END IF;
226 APP_EXCEPTION.RAISE_EXCEPTION;
227
228
229 END Recon_Payment_History;
230
231
232 /* *************************************************************************
233 * RECON_PAYMENT_MATURITY: Creates payment history activity for a check *
234 ************************************************************************* */
235
236 PROCEDURE Recon_Payment_Maturity(
237 X_CHECK_ID NUMBER,
238 X_ACCOUNTING_DATE DATE,
239 X_TRANSACTION_TYPE VARCHAR2,
240 X_TRANSACTION_AMOUNT NUMBER,
241 X_CURRENCY_CODE VARCHAR2,
242 X_EXCHANGE_RATE_TYPE VARCHAR2,
243 X_EXCHANGE_RATE_DATE DATE,
244 X_EXCHANGE_RATE NUMBER,
245 X_LAST_UPDATE_DATE DATE,
246 X_LAST_UPDATED_BY NUMBER,
247 X_LAST_UPDATE_LOGIN NUMBER,
248 X_CREATED_BY NUMBER,
249 X_CREATION_DATE DATE,
250 X_PROGRAM_UPDATE_DATE DATE,
251 X_PROGRAM_APPLICATION_ID NUMBER,
252 X_PROGRAM_ID NUMBER,
253 X_REQUEST_ID NUMBER,
254 X_CALLING_SEQUENCE VARCHAR2
255 ) AS
256 current_calling_sequence VARCHAR2(2000);
257 l_trx_base_amount AP_PAYMENT_HISTORY.Trx_Base_Amount%TYPE;
258 l_functional_currency_code VARCHAR2(15);
259 l_rev_pmt_hist_id NUMBER; -- Bug3343314
260 l_org_id NUMBER;
261 l_debug_info VARCHAR2(240);
262
263 BEGIN
264 current_calling_sequence := X_CALLING_SEQUENCE ||
265 'AP_RECONCILIATION_PKG.RECON_PAYMENT_MATURITY' ;
266
267 IF (x_transaction_type IN ('PAYMENT MATURITY', -- Bug3343314
268 'PAYMENT MATURITY REVERSAL')) THEN -- Bug3343314
269
270 -- Bug3343314
271 IF (x_transaction_type = 'PAYMENT MATURITY REVERSAL') THEN
272 SELECT payment_history_id
273 INTO l_rev_pmt_hist_id
274 FROM ap_payment_history
275 WHERE transaction_type = 'PAYMENT MATURITY'
276 AND check_id = x_check_id;
277 ELSE
278 l_rev_pmt_hist_id := NULL;
279 END IF;
280
281 -- bug 4578865
282 l_debug_info := 'Inside Recon Payment Maturity, getting base curr';
283 SELECT asp.base_currency_code,
284 asp.org_id
285 INTO l_functional_currency_code,
286 l_org_id
287 FROM ap_system_parameters asp,
288 ap_checks ac
289 WHERE ac.org_id = asp.org_id
290 AND ac.check_id = x_check_id;
291
292 l_debug_info := 'Call rounding function';
293
294 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
295 -- X_Transaction_Amount*nvl(X_Exchange_Rate, 1),
296 -- Bug 3168106
297 X_Transaction_Amount*X_Exchange_Rate,
298 l_functional_currency_code );
299 l_debug_info := 'Insert Payment History';
300
301 AP_RECONCILIATION_PKG.Insert_Payment_History
302 ( X_CHECK_ID => X_CHECK_ID,
303 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
304 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
305 X_TRX_BANK_AMOUNT => NULL,
306 X_ERRORS_BANK_AMOUNT => NULL,
307 X_CHARGES_BANK_AMOUNT => NULL,
308 X_BANK_CURRENCY_CODE => NULL,
309 X_BANK_TO_BASE_XRATE_TYPE => NULL,
310 X_BANK_TO_BASE_XRATE_DATE => NULL,
311 X_BANK_TO_BASE_XRATE => NULL,
312 X_TRX_PMT_AMOUNT => X_TRANSACTION_AMOUNT,
313 X_ERRORS_PMT_AMOUNT => NULL,
314 X_CHARGES_PMT_AMOUNT => NULL,
315 X_PMT_CURRENCY_CODE => X_CURRENCY_CODE,
316 X_PMT_TO_BASE_XRATE_TYPE => X_EXCHANGE_RATE_TYPE,
317 X_PMT_TO_BASE_XRATE_DATE => X_EXCHANGE_RATE_DATE,
318 X_PMT_TO_BASE_XRATE => X_EXCHANGE_RATE,
319 X_TRX_BASE_AMOUNT => l_trx_base_amount,
320 X_ERRORS_BASE_AMOUNT => NULL,
321 X_CHARGES_BASE_AMOUNT => NULL,
322 X_MATCHED_FLAG => NULL,
323 X_REV_PMT_HIST_ID => l_rev_pmt_hist_id, -- Bug3343314
324 X_ORG_ID => l_org_id, -- Bug 4578865
325 X_CREATION_DATE => X_CREATION_DATE,
326 X_CREATED_BY => X_CREATED_BY,
327 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
328 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
329 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
330 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
331 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
332 X_PROGRAM_ID => X_PROGRAM_ID,
333 X_REQUEST_ID => X_REQUEST_ID,
334 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
335
336 ELSE
337 APP_EXCEPTION.RAISE_EXCEPTION ;
338 END IF ;
339
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_NO_DATA_FOUND');
343 APP_EXCEPTION.RAISE_EXCEPTION;
344 WHEN OTHERS THEN
345 IF (SQLCODE <> -20001) THEN
346 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
347 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
348 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
349 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
350 END IF;
351 APP_EXCEPTION.RAISE_EXCEPTION;
352
353 END Recon_Payment_Maturity ;
354
355
356 /* *************************************************************************
357 * DELETE_PAYMENT_MATURITY: Removes entry for payment maturity from *
358 * AP_PAYMENT_HISTORY for a particular check *
359 ************************************************************************* */
360
361 PROCEDURE Delete_Payment_Maturity (
362 X_CHECK_ID NUMBER,
363 X_CALLING_SEQUENCE VARCHAR2
364 ) AS
365 current_calling_sequence VARCHAR2(2000);
366 l_debug_info VARCHAR2(240);
367
368 BEGIN
369 current_calling_sequence := X_CALLING_SEQUENCE ||
370 'AP_RECONCILIATION_PKG.DELETE_PAYMENT_MATURITY' ;
371 l_debug_info := 'Deleting from Payment History table';
372
373 DELETE FROM AP_PAYMENT_HISTORY
374 WHERE check_id = x_check_id
375 AND transaction_type = 'PAYMENT MATURITY'
376 AND nvl(posted_flag,'N') = 'N';
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_NO_DATA_FOUND');
381 APP_EXCEPTION.RAISE_EXCEPTION;
382 WHEN OTHERS THEN
386 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
383 IF (SQLCODE <> -20001) THEN
384 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
385 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
387 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
388 END IF;
389 APP_EXCEPTION.RAISE_EXCEPTION;
390 END Delete_Payment_Maturity ;
391
392
393 /* *************************************************************************
394 * RECON_PAYMENT_CLEARING: Creates entries/updates checks due to a *
395 * clearing event which may or may not be matched*
396 * to a bank statement. *
397 ************************************************************************* */
398
399 PROCEDURE Recon_Payment_Clearing(
400 X_CHECKRUN_ID NUMBER,
401 X_CHECK_ID NUMBER,
402 X_ACCOUNTING_DATE DATE,
403 X_CLEARED_DATE DATE,
404 X_TRANSACTION_TYPE VARCHAR2,
405 X_TRX_BANK_AMOUNT NUMBER,
406 X_ERRORS_BANK_AMOUNT NUMBER,
407 X_CHARGES_BANK_AMOUNT NUMBER,
408 X_BANK_CURRENCY_CODE VARCHAR2,
409 X_PMT_TO_BASE_XRATE_TYPE VARCHAR2,
410 X_PMT_TO_BASE_XRATE_DATE DATE,
411 X_PMT_TO_BASE_XRATE NUMBER,
412 X_MATCHED_FLAG VARCHAR2,
413 X_ACTUAL_VALUE_DATE DATE,
414 X_LAST_UPDATE_DATE DATE,
415 X_LAST_UPDATED_BY NUMBER,
416 X_LAST_UPDATE_LOGIN NUMBER,
417 X_CREATED_BY NUMBER,
418 X_CREATION_DATE DATE,
419 X_PROGRAM_UPDATE_DATE DATE,
420 X_PROGRAM_APPLICATION_ID NUMBER,
421 X_PROGRAM_ID NUMBER,
422 X_REQUEST_ID NUMBER,
423 X_CALLING_SEQUENCE VARCHAR2
424 ) AS
425
426 --bugfix:5623562
427 l_recon_accounting_flag ap_system_parameters.recon_accounting_flag%TYPE;
428 l_currency_case VARCHAR2(30);
429 l_status_code VARCHAR2(30) ;
430
431 l_pmt_currency_code ap_checks.currency_code%TYPE;
432 l_functional_currency_code ap_system_parameters.base_currency_code%TYPE;
433 l_bank_to_base_xrate ap_checks.exchange_rate%TYPE;
434 l_bank_to_base_xrate_type ap_checks.exchange_rate_type%TYPE;
435 l_bank_to_base_xrate_date ap_checks.exchange_date%TYPE;
436 l_trx_pmt_amount NUMBER;
437 l_errors_pmt_amount NUMBER;
438 l_charges_pmt_amount NUMBER;
439 l_trx_base_amount NUMBER;
440 l_errors_base_amount NUMBER;
441 l_charges_base_amount NUMBER;
442 l_remainder_errors_pmt_amt ap_checks.amount%TYPE;
443 l_remainder_charges_pmt_amt ap_checks.amount%TYPE;
444 l_errors_bank_amount ap_checks.amount%TYPE;
445 l_charges_bank_amount ap_checks.amount%TYPE;
446
447 l_max_pmt_amt ap_checks.amount%TYPE;
448 l_pay_sum_amt ap_checks.amount%TYPE;
449 l_payment_count NUMBER;
450 l_pmt_not_matured NUMBER := 0 ;
451 l_running_total_payments NUMBER := 0;
452 l_runtotal_trx_bank_amount NUMBER := 0;
453 l_runtotal_errors_bank_amount NUMBER := 0;
454 l_runtotal_charges_bank_amount NUMBER := 0;
455 l_check_amount ap_checks.amount%TYPE;
456 l_check_id ap_checks.check_id%TYPE;
457 l_payment_xrate ap_checks.exchange_rate%TYPE;
458 l_status_lookup_code ap_checks.status_lookup_code%TYPE;
459
460 l_future_pay_flag VARCHAR2(1) ;
461 l_ind_errors_pmt_amount ap_checks.amount%TYPE;
462 l_ind_charges_pmt_amount ap_checks.amount%TYPE;
463 l_debug_info VARCHAR2(240);
464 l_org_id NUMBER;
465 l_distinct_org NUMBER; -- Bug 5674397
466
467 current_calling_sequence VARCHAR2(2000);
468 cant_do_recon_acct EXCEPTION;
469
470 -- Distinct Org Cursor
471 -- Bug 5674397
472 CURSOR distinct_org_cur IS
473 SELECT distinct org_id
474 FROM ap_checks
475 WHERE checkrun_id = X_CHECKRUN_ID;
476
477 -- Payment Batch Cursor
478 -- Bug 5674397. Added the parameter
479 CURSOR pay_batch_cur(P_org_id IN NUMBER) IS
480 SELECT c.check_id,
481 c.status_lookup_code,
482 nvl(c.amount,0),
483 nvl(c.exchange_rate,1),
484 decode(c.future_pay_due_date,NULL,'N','Y'),
485 c.currency_code,
486 asp.base_currency_code,
487 asp.org_id,
488 nvl(asp.recon_accounting_flag, 'N')
489 FROM ap_checks_all c,
490 ap_system_parameters_all asp
491 WHERE c.checkrun_id = X_CHECKRUN_id
492 AND c.org_id = p_org_id
493 AND c.org_id = asp.org_id
494 AND c.status_lookup_code NOT IN -- Bug 3408260
495 ('VOIDED','SPOILED','OVERFLOW','SETUP','STOP INITIATED',
496 'UNCONFIRMED SET UP',
497 /* 3575546 fbreslin: Add other statuses to skip. */
498 'RECONCILED', 'RECONCILED UNACCOUNTED',
499 'ISSUED')
500 GROUP BY c.check_id,
501 c.status_lookup_code,
502 nvl(c.amount,0),
503 nvl(c.exchange_rate,1),
507 asp.org_id,
504 decode(c.future_pay_due_date,NULL,'N','Y'),
505 c.currency_code,
506 asp.base_currency_code,
508 nvl(recon_accounting_flag, 'N')
509 ORDER BY nvl(c.amount,0);
510
511 BEGIN
512 current_calling_sequence := X_CALLING_SEQUENCE ||
513 'AP_RECONCILIATION_PKG.RECON_PAYMENT_CLEARING' ;
514
515
516 --bugfix:5623562
517 --bug 5674397. Comment the following section
518 /*
519 SELECT nvl(recon_accounting_flag, 'N'),
520 base_currency_code
521 INTO l_recon_accounting_flag,
522 l_functional_currency_code
523 FROM ap_system_parameters;
524 */
525 l_errors_bank_amount := nvl(X_ERRORS_BANK_AMOUNT,0);
526 l_charges_bank_amount := nvl(X_CHARGES_BANK_AMOUNT,0);
527
528 IF (X_CHECKRUN_ID IS NOT NULL ) THEN
529 l_debug_info := ' Inside checkrun is not null';
530
531 -- Bug 5674397 Moving at the begining
532 SELECT count(check_id), sum(amount) , max(amount)
533 INTO l_payment_count, l_pay_sum_amt, l_max_pmt_amt
534 FROM AP_CHECKS
535 WHERE checkrun_id = x_checkrun_id
536 AND status_lookup_code NOT IN
537 ('VOIDED','SETUP', 'OVERFLOW','SPOILED',
538 'STOP INITIATED','UNCONFIRMED SET UP',
539 'RECONCILED', 'RECONCILED UNACCOUNTED',
540 'ISSUED');
541
542
543 -- Bug 5674397
544 OPEN distinct_org_cur ;
545 LOOP
546
547 FETCH distinct_org_cur INTO l_distinct_org;
548
549 l_debug_info := 'Inside distinct_org_cur cursor';
550
551 IF distinct_org_cur%NOTFOUND THEN
552 IF distinct_org_cur%ROWCOUNT < 1 THEN
553 RAISE no_data_found;
554 ELSE -- No more rows
555 EXIT ;
556 END IF;
557 END IF;
558
559 -- Bug 5674397. Moving the cursor before cas type
560 l_running_total_payments := 0;
561
562 OPEN pay_batch_cur(l_org_id) ;
563 LOOP
564
565 -- bug 4578865
566 FETCH pay_batch_cur INTO
567 l_check_id,
568 l_status_lookup_code,
569 l_check_amount,
570 l_payment_xrate,
571 l_future_pay_flag,
572 l_pmt_currency_code,
573 l_functional_currency_code,
574 l_org_id,
575 l_recon_accounting_flag;
576
577 l_debug_info := 'Inside pay_batch_cur cursor';
578
579 IF pay_batch_cur%NOTFOUND THEN
580 IF pay_batch_cur%ROWCOUNT < 1 THEN
581 RAISE no_data_found;
582 ELSE -- No more rows
583 EXIT ;
584 END IF;
585 END IF;
586
587 l_running_total_payments := l_running_total_payments + l_check_amount;
588 -- bug 4578865
589
590 /* Bug 5674397, Combining into cursor
591 SELECT ac.currency_code,
592 asp.base_currency_code,
593 asp.org_id,
594 nvl(recon_accounting_flag, 'N') -- Bug 5674397
595 INTO l_pmt_currency_code,
596 l_functional_currency_code,
597 l_org_id,
598 l_recon_accounting_flag
599 FROM ap_checks ac,
600 ap_system_parameters asp
601 WHERE ac.checkrun_id = X_CHECKRUN_ID
602 AND asp.org_id = ac.org_id
603 AND ac.org_id = l_distinct_org
604 AND rownum = 1;
605 */
606 l_debug_info := 'Got payment currency and other system options, calling case_type';
607
608 l_currency_case := Case_Type(
609 X_BANK_CURRENCY_CODE,
610 l_pmt_currency_code,
611 l_functional_currency_code
612 );
613
614 -- If international or cross currency, convert to payment currency
615 -- the errors and charges before proration.
616
617 IF (l_currency_case = 'INTERNATIONAL') THEN
618 l_debug_info := 'Inside International';
619 l_remainder_errors_pmt_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
620 l_errors_bank_amount / nvl(X_PMT_TO_BASE_XRATE,1),
621 l_pmt_currency_code );
622 l_remainder_charges_pmt_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
623 l_charges_bank_amount / nvl(X_PMT_TO_BASE_XRATE,1),
624 l_pmt_currency_code );
625
626 -- Since the bank and base currencies are the same ...
627
628 l_bank_to_base_xrate_type := NULL;
629 l_bank_to_base_xrate_date := NULL;
630 l_bank_to_base_xrate := NULL;
631
632 ELSIF (l_currency_case = 'FOREIGN') THEN
633 l_debug_info := 'Inside Foreign';
634
635 l_remainder_errors_pmt_amt := l_errors_bank_amount;
636 l_remainder_charges_pmt_amt := l_charges_bank_amount;
637 l_bank_to_base_xrate_type := X_PMT_TO_BASE_XRATE_TYPE;
638 l_bank_to_base_xrate_date := X_PMT_TO_BASE_XRATE_DATE;
639 l_bank_to_base_xrate := X_PMT_TO_BASE_XRATE;
640
641 ELSIF (l_currency_case = 'DOMESTIC') THEN
642 l_debug_info := 'Inside Domestic';
643
647 l_bank_to_base_xrate_date := NULL;
644 l_remainder_errors_pmt_amt := l_errors_bank_amount;
645 l_remainder_charges_pmt_amt := l_charges_bank_amount;
646 l_bank_to_base_xrate_type := NULL;
648 l_bank_to_base_xrate := NULL;
649
650 END IF;
651
652 -- Prepare errors and charges for proration. Now the amounts
653 -- should be in payment currency
654 l_errors_pmt_amount := l_remainder_errors_pmt_amt;
655 l_charges_pmt_amount := l_remainder_charges_pmt_amt;
656
657 -- Check if batch contains a future pmt check which has not matured
658 l_debug_info := 'Check for future pmt checks, that are not matured';
659
660
661 -- We cannot clear an already cleared check
662 IF ((X_MATCHED_FLAG <> 'Y' ) AND
663 (l_status_lookup_code IN ('CLEARED','CLEARED BUT UNACCOUNTED'))) THEN
664 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_CLEARED_UNMATCHED');
665 RAISE G_abort_it;
666 END IF ;
667
668 IF (l_pay_sum_amt = 0) THEN
669 l_debug_info := 'Inside l_pay_sum_amt is 0';
670
671 l_ind_errors_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
672 l_errors_pmt_amount/l_payment_count,
673 l_pmt_currency_code );
674 l_ind_charges_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
675 l_charges_pmt_amount/l_payment_count,
676 l_pmt_currency_code );
677
678 ELSIF (l_running_total_payments = l_pay_sum_amt) THEN
679 l_debug_info := 'Inside l_pay_sum_amt is running total';
680
681 l_ind_errors_pmt_amount := l_remainder_errors_pmt_amt;
682 l_ind_charges_pmt_amount := l_remainder_charges_pmt_amt;
683
684 ELSE
685 l_debug_info := 'Inside l_pay_sum_amt is another value';
686
687 l_ind_errors_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
688 l_errors_pmt_amount*l_check_amount
689 /l_pay_sum_amt,
690 l_pmt_currency_code );
691 l_ind_charges_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
692 l_charges_pmt_amount*l_check_amount
693 /l_pay_sum_amt,
694 l_pmt_currency_code );
695
696 END IF ; /* Total payment batch amount is 0 */
697
698 IF ( X_MATCHED_FLAG = 'Y') THEN
699 IF (l_status_lookup_code = 'NEGOTIABLE') THEN
700 l_status_code := 'RECONCILED UNACCOUNTED' ;
701 ELSIF (l_status_lookup_code = 'CLEARED BUT UNACCOUNTED') THEN
702 l_status_code := 'RECONCILED UNACCOUNTED';
703 ELSIF (l_status_lookup_code = 'CLEARED') THEN
704 l_status_code := 'RECONCILED';
705 END IF;
706 ELSE
707 l_status_code := 'CLEARED BUT UNACCOUNTED' ;
708 END IF ;
709
710 -- Only insert into payment history if reconciliation accounting is ON.
711 l_debug_info := 'Set l_status_code';
712
713 --bugfix:5623562
714 IF ( l_recon_accounting_flag = 'Y' AND
715 l_status_lookup_code = 'NEGOTIABLE') THEN
716
717 IF (l_currency_case = 'INTERNATIONAL') THEN
718
719 IF (l_running_total_payments = l_pay_sum_amt) THEN
720 l_debug_info := 'Inside Negotiable, International amounts equal';
721
722 l_trx_base_amount := X_TRX_BANK_AMOUNT - l_runtotal_trx_bank_amount;
723 l_errors_base_amount := X_ERRORS_BANK_AMOUNT
724 - l_runtotal_errors_bank_amount;
725 l_charges_base_amount := X_CHARGES_BANK_AMOUNT
726 - l_runtotal_charges_bank_amount;
727 ELSE
728 l_debug_info := 'Inside Negotiable, International amounts not eq';
729
730 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
731 (l_check_amount + l_ind_errors_pmt_amount
732 + l_ind_charges_pmt_amount) * nvl(X_PMT_TO_BASE_XRATE,1),
733 l_functional_currency_code);
734 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
735 l_ind_errors_pmt_amount
736 * nvl(X_PMT_TO_BASE_XRATE,1),
737 l_functional_currency_code);
738 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
739 l_ind_charges_pmt_amount
740 * nvl(X_PMT_TO_BASE_XRATE,1),
741 l_functional_currency_code);
742 END IF;
743
744 l_runtotal_trx_bank_amount := l_runtotal_trx_bank_amount
745 + l_trx_base_amount;
746 l_runtotal_errors_bank_amount := l_runtotal_errors_bank_amount
747 + l_errors_base_amount;
748 l_runtotal_charges_bank_amount := l_runtotal_charges_bank_amount
749 + l_charges_base_amount;
750
751 -- Bug 2276503
752 --IF Check_Not_Accounted_To_Cash(l_check_id) THEN
756 AP_RECONCILIATION_PKG.Insert_Payment_History
753 -- Commenting condition for bug 2626686 as accounting is now created correctly
754 l_debug_info := 'Inside International, Negotiable, before insert';
755
757 ( X_CHECK_ID => l_check_id,
758 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
759 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
760 X_TRX_BANK_AMOUNT => l_trx_base_amount,
761 X_ERRORS_BANK_AMOUNT => l_errors_base_amount,
762 X_CHARGES_BANK_AMOUNT => l_charges_base_amount,
763 X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
764 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
765 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
766 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
767 X_TRX_PMT_AMOUNT => l_check_amount
768 + l_ind_errors_pmt_amount
769 + l_ind_charges_pmt_amount,
770 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
771 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
772 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
773 X_PMT_TO_BASE_XRATE_TYPE => X_PMT_TO_BASE_XRATE_TYPE,
774 X_PMT_TO_BASE_XRATE_DATE => X_PMT_TO_BASE_XRATE_DATE,
775 X_PMT_TO_BASE_XRATE => X_PMT_TO_BASE_XRATE,
776 X_TRX_BASE_AMOUNT => l_trx_base_amount,
777 X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
778 X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
779 X_MATCHED_FLAG => X_MATCHED_FLAG,
780 X_REV_PMT_HIST_ID => NULL,
781 X_ORG_ID => l_org_id, -- bug 4578865
782 X_CREATION_DATE => X_CREATION_DATE,
783 X_CREATED_BY => X_CREATED_BY,
784 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
785 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
786 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
787 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
788 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
789 X_PROGRAM_ID => X_PROGRAM_ID,
790 X_REQUEST_ID => X_REQUEST_ID,
791 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
792 --END IF;
793
794 ELSIF (l_currency_case = 'FOREIGN') THEN
795 l_debug_info := 'Inside Negotiable, Foreign';
796
797 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
798 (l_check_amount + l_ind_errors_pmt_amount
799 + l_ind_charges_pmt_amount) * nvl(X_PMT_TO_BASE_XRATE,1),
800 l_functional_currency_code);
801 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
802 l_ind_errors_pmt_amount
803 * nvl(X_PMT_TO_BASE_XRATE,1),
804 l_functional_currency_code);
805 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
806 l_ind_charges_pmt_amount
807 * nvl(X_PMT_TO_BASE_XRATE,1),
808 l_functional_currency_code);
809 -- Bug 2276503
810 --IF Check_Not_Accounted_To_Cash(l_check_id) THEN
811 -- Commenting condition for bug 2626686 as accounting is now created correctly
812 l_debug_info := 'Insert history for Negotiable, Foreign';
813
814 AP_RECONCILIATION_PKG.Insert_Payment_History
815 ( X_CHECK_ID => l_check_id,
816 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
817 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
818 X_TRX_BANK_AMOUNT => l_check_amount
819 + l_ind_errors_pmt_amount
820 + l_ind_charges_pmt_amount,
821 X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
822 X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
823 X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
824 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
825 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
826 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
827 X_TRX_PMT_AMOUNT => l_check_amount
828 + l_ind_errors_pmt_amount
829 + l_ind_charges_pmt_amount,
830 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
831 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
832 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
833 X_PMT_TO_BASE_XRATE_TYPE => X_PMT_TO_BASE_XRATE_TYPE,
834 X_PMT_TO_BASE_XRATE_DATE => X_PMT_TO_BASE_XRATE_DATE,
835 X_PMT_TO_BASE_XRATE => X_PMT_TO_BASE_XRATE,
836 X_TRX_BASE_AMOUNT => l_trx_base_amount,
837 X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
838 X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
842 X_CREATION_DATE => X_CREATION_DATE,
839 X_MATCHED_FLAG => X_MATCHED_FLAG,
840 X_REV_PMT_HIST_ID => NULL,
841 X_ORG_ID => l_org_id, -- Bug 4578865
843 X_CREATED_BY => X_CREATED_BY,
844 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
845 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
846 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
847 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
848 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
849 X_PROGRAM_ID => X_PROGRAM_ID,
850 X_REQUEST_ID => X_REQUEST_ID,
851 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
852
853 --END IF;
854
855 ELSIF (l_currency_case = 'DOMESTIC') THEN
856
857 l_trx_base_amount := l_check_amount + l_ind_errors_pmt_amount
858 + l_ind_charges_pmt_amount;
859 l_errors_base_amount := l_ind_errors_pmt_amount;
860 l_charges_base_amount := l_ind_charges_pmt_amount;
861
862 -- Bug 2276503
863 --IF Check_Not_Accounted_To_Cash(l_check_id) THEN
864 -- Commenting condition for bug 2626686 as accounting is now created correctly
865 l_debug_info := 'Inside Negotiable, Domestic, before Insert';
866
867 AP_RECONCILIATION_PKG.Insert_Payment_History
868 ( X_CHECK_ID => l_check_id,
869 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
870 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
871 X_TRX_BANK_AMOUNT => l_check_amount
872 + l_ind_errors_pmt_amount
873 + l_ind_charges_pmt_amount,
874 X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
875 X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
876 X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
877 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
878 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
879 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
880 X_TRX_PMT_AMOUNT => l_check_amount
881 + l_ind_errors_pmt_amount
882 + l_ind_charges_pmt_amount,
883 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
884 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
885 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
886 X_PMT_TO_BASE_XRATE_TYPE => X_PMT_TO_BASE_XRATE_TYPE,
887 X_PMT_TO_BASE_XRATE_DATE => X_PMT_TO_BASE_XRATE_DATE,
888 X_PMT_TO_BASE_XRATE => X_PMT_TO_BASE_XRATE,
889 X_TRX_BASE_AMOUNT => l_trx_base_amount,
890 X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
891 X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
892 X_MATCHED_FLAG => X_MATCHED_FLAG,
893 X_REV_PMT_HIST_ID => NULL,
894 X_ORG_ID => l_org_id, -- Bug 4578865
895 X_CREATION_DATE => X_CREATION_DATE,
896 X_CREATED_BY => X_CREATED_BY,
897 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
898 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
899 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
900 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
901 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
902 X_PROGRAM_ID => X_PROGRAM_ID,
903 X_REQUEST_ID => X_REQUEST_ID,
904 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
905
906 --END IF;
907
908 END IF;
909
910 l_remainder_errors_pmt_amt := l_remainder_errors_pmt_amt
911 - l_ind_errors_pmt_amount ;
912 l_remainder_charges_pmt_amt := l_remainder_charges_pmt_amt
913 - l_ind_charges_pmt_amount ;
914
915 --bugfix:5623562 added the code under this ELSE stmt
916 ELSIF (l_recon_accounting_flag <> 'Y') THEN
917
918 l_debug_info := 'Inside recon flag not Y';
919
920 -- Bug 4538437 : Following IF condition is added so that base amounts
921 -- are populated even if recon_accounting_flag = 'N'
922 IF (l_currency_case = 'INTERNATIONAL') THEN
923
924 IF (l_running_total_payments = l_pay_sum_amt) THEN
925
926
927 l_trx_base_amount := X_TRX_BANK_AMOUNT - l_runtotal_trx_bank_amount;
928 l_errors_base_amount := X_ERRORS_BANK_AMOUNT
929 - l_runtotal_errors_bank_amount;
930 l_charges_base_amount := X_CHARGES_BANK_AMOUNT
931 - l_runtotal_charges_bank_amount;
932 ELSE
933
934 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
935 (l_check_amount + l_ind_errors_pmt_amount
936 + l_ind_charges_pmt_amount)
940 l_ind_errors_pmt_amount
937 * nvl(X_PMT_TO_BASE_XRATE,1),
938 l_functional_currency_code);
939 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
941 * nvl(X_PMT_TO_BASE_XRATE,1),
942 l_functional_currency_code);
943 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
944 l_ind_charges_pmt_amount
945 * nvl(X_PMT_TO_BASE_XRATE,1),
946 l_functional_currency_code);
947 END IF;
948
949 ELSIF (l_currency_case = 'FOREIGN') THEN
950
951 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
952 (l_check_amount + l_ind_errors_pmt_amount
953 + l_ind_charges_pmt_amount)
954 * nvl(X_PMT_TO_BASE_XRATE,1),
955 l_functional_currency_code);
956 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
957 l_ind_errors_pmt_amount
958 * nvl(X_PMT_TO_BASE_XRATE,1),
959 l_functional_currency_code);
960 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
961 l_ind_charges_pmt_amount
962 * nvl(X_PMT_TO_BASE_XRATE,1),
963 l_functional_currency_code);
964
965 ELSIF (l_currency_case = 'DOMESTIC') THEN
966
967 l_trx_base_amount := l_check_amount + l_ind_errors_pmt_amount
968 + l_ind_charges_pmt_amount;
969 l_errors_base_amount := l_ind_errors_pmt_amount;
970 l_charges_base_amount := l_ind_charges_pmt_amount;
971
972
973 END IF; -- Bug 4538437 IF condition end here
974
975 IF (l_future_pay_flag <> 'Y') THEN -- not a future payment
976 IF ((nvl(X_ERRORS_BANK_AMOUNT,0) <> 0) OR
977 (nvl(X_CHARGES_BANK_AMOUNT,0) <> 0)) THEN
978 RAISE cant_do_recon_acct;
979 ELSE
980 IF ((X_PMT_TO_BASE_XRATE_TYPE IS NOT NULL) OR
981 (X_PMT_TO_BASE_XRATE_DATE IS NOT NULL) OR
982 (X_PMT_TO_BASE_XRATE IS NOT NULL)) THEN -- xrate info passed
983
984 IF (l_payment_xrate <> X_PMT_TO_BASE_XRATE) THEN -- pay <> recon
985 RAISE cant_do_recon_acct;
986 END IF; -- l_payment_xrate <> XRATE
987 END IF; -- xrate_type, xrate_date, xrate is not null
988 END IF; -- error_amount <> 0 and charge_amount <> 0
989 END IF; -- l_future_pay_flag <> 'Y'
990
991 END IF ;/* Reconciliation accounting and matched flag */
992
993 -- Status needs to be updated depending upon matching flag irrespective
994 -- of reconciliation accouting is on or OFF
995 l_debug_info := 'Before recon_update_check call';
996
997 IF NOT (Recon_Update_Check(l_check_id,
998 l_check_amount+l_ind_errors_pmt_amount
999 +l_ind_charges_pmt_amount,
1000 l_trx_base_amount,
1001 l_ind_errors_pmt_amount,
1002 l_errors_base_amount,
1003 l_ind_charges_pmt_amount,
1004 l_charges_base_amount,
1005 X_CLEARED_DATE,
1006 l_status_code,
1007 X_PMT_TO_BASE_XRATE,
1008 X_PMT_TO_BASE_XRATE_TYPE,
1009 X_PMT_TO_BASE_XRATE_DATE,
1010 X_ACTUAL_VALUE_DATE,
1011 X_LAST_UPDATED_BY,
1012 X_LAST_UPDATE_LOGIN,
1013 X_REQUEST_ID )) THEN
1014 RAISE G_abort_it;
1015 END IF; -- recon_update_check
1016
1017 END LOOP ; /* Loop through checks in a payment batch */
1018
1019 CLOSE pay_batch_cur ;
1020
1021 END LOOP;
1022
1023 CLOSE distinct_org_cur; /* end distinct org cursor */
1024
1025 ELSE /* checkrun_id is null....... */
1026 l_debug_info := 'Inside checkrun is null';
1027
1028 -- Single Payment
1029 -- Select to retrieve check information
1030 -- bug 4578865
1031 SELECT c.currency_code,
1032 c.status_lookup_code,
1033 nvl(c.amount, 0),
1034 nvl(c.exchange_rate,1),
1035 decode(c.future_pay_due_date,NULL,'N','Y'),
1036 asp.base_currency_code,
1037 asp.org_id,
1038 nvl(recon_accounting_flag, 'N') -- Bug 5674397
1039 INTO l_pmt_currency_code,
1040 l_status_lookup_code,
1041 l_check_amount,
1042 l_payment_xrate,
1043 l_future_pay_flag,
1044 l_functional_currency_code,
1045 l_org_id,
1046 l_recon_accounting_flag
1047 FROM ap_checks_all c,
1048 ap_system_parameters_all asp
1049 WHERE c.check_id = X_CHECK_ID
1050 AND c.org_id = asp.org_id;
1051
1055 END IF ;
1052 IF ((l_status_lookup_code IN ('RECONCILED','RECONCILED UNACCOUNTED'))) THEN
1053 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_ALREADY_RECONCILED');
1054 RAISE G_abort_it;
1056
1057 IF ((l_status_lookup_code = 'ISSUED') And (l_future_pay_flag = 'Y' )) THEN
1058 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_PAYMENT_NOT_MATURED');
1059 RAISE G_abort_it;
1060 END IF ;
1061
1062 IF ((X_MATCHED_FLAG <> 'Y' ) And
1063 (l_status_lookup_code In ('CLEARED','CLEARED BUT UNACCOUNTED'))) THEN
1064 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_CLEARED_UNMATCHED');
1065 RAISE G_abort_it;
1066 END IF ;
1067 l_debug_info := 'checkrun is null, calling case_type';
1068
1069 l_currency_case := Case_Type(
1070 X_BANK_CURRENCY_CODE,
1071 l_pmt_currency_code,
1072 l_functional_currency_code
1073 );
1074
1075 -- If international or cross currency, convert to payment currency
1076 -- the errors and charges before proration.
1077 IF (l_currency_case = 'INTERNATIONAL') THEN
1078 l_debug_info := 'checkrun is null, inside International';
1079
1080 l_ind_errors_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
1081 l_errors_bank_amount / nvl(X_PMT_TO_BASE_XRATE,1),
1082 l_pmt_currency_code );
1083 l_ind_charges_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
1084 l_charges_bank_amount / nvl(X_PMT_TO_BASE_XRATE,1),
1085 l_pmt_currency_code );
1086
1087 -- Since the bank and base currencies are the same ...
1088
1089 l_bank_to_base_xrate_type := NULL;
1090 l_bank_to_base_xrate_date := NULL;
1091 l_bank_to_base_xrate := NULL;
1092
1093 ELSIF (l_currency_case = 'FOREIGN') THEN
1094 l_debug_info := 'checkrun is null, inside Foreign';
1095
1096 l_ind_errors_pmt_amount := l_errors_bank_amount;
1097 l_ind_charges_pmt_amount := l_charges_bank_amount;
1098 l_bank_to_base_xrate_type := X_PMT_TO_BASE_XRATE_TYPE;
1099 l_bank_to_base_xrate_date := X_PMT_TO_BASE_XRATE_DATE;
1100 l_bank_to_base_xrate := X_PMT_TO_BASE_XRATE;
1101
1102 ELSIF (l_currency_case = 'DOMESTIC') THEN
1103 l_debug_info := 'checkrun is null, inside Domestic';
1104
1105 l_ind_errors_pmt_amount := l_errors_bank_amount;
1106 l_ind_charges_pmt_amount := l_charges_bank_amount;
1107 l_bank_to_base_xrate_type := NULL;
1108 l_bank_to_base_xrate_date := NULL;
1109 l_bank_to_base_xrate := NULL;
1110
1111 END IF;
1112
1113 IF ( X_MATCHED_FLAG = 'Y') THEN
1114 IF (l_status_lookup_code = 'NEGOTIABLE') THEN
1115 l_status_code := 'RECONCILED UNACCOUNTED' ;
1116 ELSIF (l_status_lookup_code = 'CLEARED BUT UNACCOUNTED') THEN
1117 l_status_code := 'RECONCILED UNACCOUNTED';
1118 ELSIF (l_status_lookup_code = 'CLEARED') THEN
1119 l_status_code := 'RECONCILED';
1120 END IF;
1121 ELSE
1122 l_status_code := 'CLEARED BUT UNACCOUNTED' ;
1123 END IF ;
1124
1125 -- Insert in only if allow_recon ON
1126 --bugfix:5623562
1127 IF (l_recon_accounting_flag = 'Y' AND
1128 l_status_lookup_code = 'NEGOTIABLE') THEN
1129
1130 IF (l_currency_case = 'INTERNATIONAL') THEN
1131
1132 l_trx_base_amount := X_TRX_BANK_AMOUNT;
1133 l_errors_base_amount := X_ERRORS_BANK_AMOUNT;
1134 l_charges_base_amount := X_CHARGES_BANK_AMOUNT;
1135
1136 -- Bug 2276503
1137 --IF Check_Not_Accounted_To_Cash(l_check_id) THEN
1138 -- Commenting condition for bug 2626686 as accounting is now created correctly
1139 l_debug_info := 'Negotiable, International, before Insert';
1140 AP_RECONCILIATION_PKG.Insert_Payment_History
1141 ( X_CHECK_ID => X_check_id,
1142 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
1143 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
1144 X_TRX_BANK_AMOUNT => l_trx_base_amount,
1145 X_ERRORS_BANK_AMOUNT => l_errors_base_amount,
1146 X_CHARGES_BANK_AMOUNT => l_charges_base_amount,
1147 X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
1148 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
1149 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
1150 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
1151 X_TRX_PMT_AMOUNT => l_check_amount
1152 + l_ind_errors_pmt_amount
1153 + l_ind_charges_pmt_amount,
1154 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
1155 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
1156 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
1157 X_PMT_TO_BASE_XRATE_TYPE => X_PMT_TO_BASE_XRATE_TYPE,
1158 X_PMT_TO_BASE_XRATE_DATE => X_PMT_TO_BASE_XRATE_DATE,
1159 X_PMT_TO_BASE_XRATE => X_PMT_TO_BASE_XRATE,
1160 X_TRX_BASE_AMOUNT => l_trx_base_amount,
1161 X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
1162 X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
1163 X_MATCHED_FLAG => X_MATCHED_FLAG,
1164 X_REV_PMT_HIST_ID => NULL,
1168 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1165 X_ORG_ID => l_org_id, -- bug 4578865
1166 X_CREATION_DATE => X_CREATION_DATE,
1167 X_CREATED_BY => X_CREATED_BY,
1169 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1170 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1171 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
1172 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
1173 X_PROGRAM_ID => X_PROGRAM_ID,
1174 X_REQUEST_ID => X_REQUEST_ID,
1175 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
1176
1177 --END IF;
1178
1179
1180 ELSIF (l_currency_case = 'FOREIGN') THEN
1181
1182 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1183 (l_check_amount + l_ind_errors_pmt_amount
1184 + l_ind_charges_pmt_amount) * nvl(X_PMT_TO_BASE_XRATE,1),
1185 l_functional_currency_code);
1186 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1187 l_ind_errors_pmt_amount * nvl(X_PMT_TO_BASE_XRATE,1),
1188 l_functional_currency_code);
1189 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1190 l_ind_charges_pmt_amount * nvl(X_PMT_TO_BASE_XRATE,1),
1191 l_functional_currency_code);
1192
1193 -- Bug 2276503
1194 --IF Check_Not_Accounted_To_Cash(l_check_id) THEN
1195 -- Commenting condition for bug 2626686 as accounting is now created correctly
1196 l_debug_info := 'Negotiable, Foreign, before Insert';
1197
1198 AP_RECONCILIATION_PKG.Insert_Payment_History
1199 ( X_CHECK_ID => X_check_id,
1200 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
1201 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
1202 X_TRX_BANK_AMOUNT => l_check_amount
1203 + l_ind_errors_pmt_amount
1204 + l_ind_charges_pmt_amount,
1205 X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
1206 X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
1207 X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
1208 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
1209 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
1210 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
1211 X_TRX_PMT_AMOUNT => l_check_amount
1212 + l_ind_errors_pmt_amount
1213 + l_ind_charges_pmt_amount,
1214 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
1215 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
1216 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
1217 X_PMT_TO_BASE_XRATE_TYPE => X_PMT_TO_BASE_XRATE_TYPE,
1218 X_PMT_TO_BASE_XRATE_DATE => X_PMT_TO_BASE_XRATE_DATE,
1219 X_PMT_TO_BASE_XRATE => X_PMT_TO_BASE_XRATE,
1220 X_TRX_BASE_AMOUNT => l_trx_base_amount,
1221 X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
1222 X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
1223 X_MATCHED_FLAG => X_MATCHED_FLAG,
1224 X_REV_PMT_HIST_ID => NULL,
1225 X_ORG_ID => l_org_id, -- bug 4578865
1226 X_CREATION_DATE => X_CREATION_DATE,
1227 X_CREATED_BY => X_CREATED_BY,
1228 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1229 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1230 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1231 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
1232 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
1233 X_PROGRAM_ID => X_PROGRAM_ID,
1234 X_REQUEST_ID => X_REQUEST_ID,
1235 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
1236
1237 --END IF;
1238
1239 ELSIF (l_currency_case = 'DOMESTIC') THEN
1240
1241 l_trx_base_amount := l_check_amount + l_ind_errors_pmt_amount
1242 + l_ind_charges_pmt_amount;
1243 l_errors_base_amount := l_ind_errors_pmt_amount;
1244 l_charges_base_amount := l_ind_charges_pmt_amount;
1245
1246 -- Bug 2276503
1247 --IF Check_Not_Accounted_To_Cash(l_check_id) THEN
1248 -- Commenting condition for bug 2626686 as accounting is now created correctly
1249 l_debug_info := 'Negotiable, Domestic, before Insert';
1250
1251 AP_RECONCILIATION_PKG.Insert_Payment_History
1252 ( X_CHECK_ID => X_check_id,
1253 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
1254 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
1255 X_TRX_BANK_AMOUNT => l_check_amount
1256 + l_ind_errors_pmt_amount
1257 + l_ind_charges_pmt_amount,
1261 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
1258 X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
1259 X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
1260 X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
1262 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
1263 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
1264 X_TRX_PMT_AMOUNT => l_check_amount
1265 + l_ind_errors_pmt_amount
1266 + l_ind_charges_pmt_amount,
1267 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
1268 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
1269 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
1270 X_PMT_TO_BASE_XRATE_TYPE => X_PMT_TO_BASE_XRATE_TYPE,
1271 X_PMT_TO_BASE_XRATE_DATE => X_PMT_TO_BASE_XRATE_DATE,
1272 X_PMT_TO_BASE_XRATE => X_PMT_TO_BASE_XRATE,
1273 X_TRX_BASE_AMOUNT => l_trx_base_amount,
1274 X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
1275 X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
1276 X_MATCHED_FLAG => X_MATCHED_FLAG,
1277 X_REV_PMT_HIST_ID => NULL,
1278 X_ORG_ID => l_org_id, -- bug 4578865
1279 X_CREATION_DATE => X_CREATION_DATE,
1280 X_CREATED_BY => X_CREATED_BY,
1281 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1282 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1283 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1284 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
1285 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
1286 X_PROGRAM_ID => X_PROGRAM_ID,
1287 X_REQUEST_ID => X_REQUEST_ID,
1288 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
1289
1290 --END IF;
1291
1292 END IF;
1293
1294
1295 --bugfix:5623562 added ELSE and the code after that.
1296 ELSIF (l_recon_accounting_flag <> 'Y') THEN
1297
1298 -- Bug 4538437 Following IF condition added so that base amounts are
1299 -- populated even if recon_accounting_flag = 'N'
1300 IF (l_currency_case = 'INTERNATIONAL') THEN
1301
1302 l_trx_base_amount := X_TRX_BANK_AMOUNT;
1303 l_errors_base_amount := X_ERRORS_BANK_AMOUNT;
1304 l_charges_base_amount := X_CHARGES_BANK_AMOUNT;
1305
1306 ELSIF (l_currency_case = 'FOREIGN') THEN
1307
1308 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1309 (l_check_amount + l_ind_errors_pmt_amount
1310 + l_ind_charges_pmt_amount )
1311 * nvl(X_PMT_TO_BASE_XRATE,1),
1312 l_functional_currency_code);
1313
1314 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1315 l_ind_errors_pmt_amount * nvl(X_PMT_TO_BASE_XRATE,1),
1316 l_functional_currency_code);
1317
1318 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
1319 l_ind_charges_pmt_amount * nvl(X_PMT_TO_BASE_XRATE,1),
1320 l_functional_currency_code);
1321
1322 ELSIF (l_currency_case = 'DOMESTIC') THEN
1323
1324 l_trx_base_amount := l_check_amount + l_ind_errors_pmt_amount
1325 + l_ind_charges_pmt_amount;
1326 l_errors_base_amount := l_ind_errors_pmt_amount;
1327
1328 l_charges_base_amount := l_ind_charges_pmt_amount;
1329
1330
1331 END IF; -- Bug 4538437 : IF condition added ends here
1332
1333 IF (l_future_pay_flag <> 'Y') THEN -- not a future payment
1334 IF ((nvl(X_ERRORS_BANK_AMOUNT, 0) <> 0) OR
1335 (nvl(X_CHARGES_BANK_AMOUNT, 0) <> 0)) THEN
1336 RAISE cant_do_recon_acct;
1337
1338 -- Bug 898805: We should not make exchange rate comparisons if the
1339 -- 'Allow Future Dated Payments' option is turned on. Commenting out NOCOPY the ELSE.
1340 END IF; -- error_amount <> 0 and charge_amount <> 0
1341 END IF; -- l_future_pay_flag <> 'Y'
1342
1343 END IF ; /* Reconciliation_flag */
1344
1345 -- Status needs to be updated depending upon matching flag irrespective
1346 -- of reconciliation accouting is on or OFF
1347 l_debug_info := 'Before recon_update_check call';
1348
1349 IF NOT (Recon_Update_Check(X_check_id,
1350 l_check_amount+l_ind_errors_pmt_amount
1351 +l_ind_charges_pmt_amount,
1352 l_trx_base_amount,
1353 l_ind_errors_pmt_amount,
1354 l_errors_base_amount,
1355 l_ind_charges_pmt_amount,
1356 l_charges_base_amount,
1357 X_CLEARED_DATE,
1358 l_status_code,
1359 X_PMT_TO_BASE_XRATE,
1360 X_PMT_TO_BASE_XRATE_TYPE,
1361 X_PMT_TO_BASE_XRATE_DATE,
1362 X_ACTUAL_VALUE_DATE,
1366 RAISE G_abort_it;
1363 X_LAST_UPDATED_BY,
1364 X_LAST_UPDATE_LOGIN,
1365 X_REQUEST_ID )) THEN
1367 END IF; -- recon_update_check
1368
1369 END IF ; /* checkrun_id not null....... */
1370
1371
1372 EXCEPTION
1373 WHEN NO_DATA_FOUND THEN
1374 IF pay_batch_cur%ISOPEN THEN
1375 CLOSE pay_batch_cur ;
1376 END IF ;
1377 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_NO_DATA_FOUND');
1378 APP_EXCEPTION.RAISE_EXCEPTION;
1379 WHEN G_abort_it THEN
1380 IF pay_batch_cur%ISOPEN THEN
1381 CLOSE pay_batch_cur ;
1382 END IF ;
1383 APP_EXCEPTION.RAISE_EXCEPTION;
1384 WHEN cant_do_recon_acct THEN
1385 IF pay_batch_cur%ISOPEN THEN
1386 CLOSE pay_batch_cur ;
1387 END IF ;
1388 FND_MESSAGE.SET_NAME('SQLAP', 'AP_RECON_CANT_RECONCILE');
1389 APP_EXCEPTION.RAISE_EXCEPTION;
1390 WHEN OTHERS THEN
1391 IF pay_batch_cur%ISOPEN THEN
1392 CLOSE pay_batch_cur ;
1393 END IF ;
1394 IF (SQLCODE <> -20001) THEN
1395 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1396 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1397 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1398 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1399 END IF;
1400 APP_EXCEPTION.RAISE_EXCEPTION;
1401
1402 END Recon_Payment_Clearing ;
1403
1404
1405 /* *************************************************************************
1406 * RECON_PAYMENT_UNCLEARING: Creates entries/updates checks due to an *
1407 * in clearing event which may or may not be *
1408 * matched to a bank statement. *
1409 ************************************************************************* */
1410
1411 PROCEDURE recon_payment_unclearing
1412 (
1413 X_CHECKRUN_ID NUMBER,
1414 X_CHECK_ID NUMBER,
1415 X_ACCOUNTING_DATE DATE,
1416 X_TRANSACTION_TYPE VARCHAR2,
1417 X_MATCHED_FLAG VARCHAR2,
1418 X_LAST_UPDATE_DATE DATE,
1419 X_LAST_UPDATED_BY NUMBER,
1420 X_LAST_UPDATE_LOGIN NUMBER,
1421 X_CREATED_BY NUMBER,
1422 X_CREATION_DATE DATE,
1423 X_PROGRAM_UPDATE_DATE DATE,
1424 X_PROGRAM_APPLICATION_ID NUMBER,
1425 X_PROGRAM_ID NUMBER,
1426 X_REQUEST_ID NUMBER,
1427 X_CALLING_SEQUENCE VARCHAR2
1428 )
1429 AS
1430
1431 --bgufix:5623562
1432 l_recon_accounting_flag ap_system_parameters.recon_accounting_flag%TYPE;
1433 l_check_amount ap_checks.amount%TYPE;
1434 l_rev_pmt_hist_id ap_payment_history.rev_pmt_hist_id%TYPE;
1435 l_check_id ap_checks.check_id%TYPE;
1436 l_ind_trx_bank_amount ap_payment_history.trx_bank_amount%TYPE;
1437 l_ind_errors_bank_amount ap_payment_history.errors_bank_amount%TYPE;
1438 l_ind_charges_bank_amount ap_payment_history.charges_bank_amount%TYPE;
1439 l_bank_currency_code ap_payment_history.bank_currency_code%TYPE;
1440 l_bank_to_base_xrate_type ap_payment_history.bank_to_base_xrate_type%TYPE;
1441 l_bank_to_base_xrate_date ap_payment_history.bank_to_base_xrate_date%TYPE;
1442 l_bank_to_base_xrate ap_payment_history.bank_to_base_xrate%TYPE;
1443 l_ind_trx_pmt_amount ap_payment_history.trx_pmt_amount%TYPE;
1444 l_ind_errors_pmt_amount ap_payment_history.errors_pmt_amount%TYPE;
1445 l_ind_charges_pmt_amount ap_payment_history.charges_pmt_amount%TYPE;
1446 l_pmt_currency_code ap_payment_history.pmt_currency_code%TYPE;
1447 l_pmt_to_base_xrate_type ap_payment_history.pmt_to_base_xrate_type%TYPE;
1448 l_pmt_to_base_xrate_date ap_payment_history.pmt_to_base_xrate_date%TYPE;
1449 l_pmt_to_base_xrate ap_payment_history.pmt_to_base_xrate%TYPE;
1450 l_ind_trx_base_amount ap_payment_history.trx_base_amount%TYPE;
1451 l_ind_errors_base_amount ap_payment_history.errors_base_amount%TYPE;
1452 l_ind_charges_base_amount ap_payment_history.charges_base_amount%TYPE;
1453 l_debug_info VARCHAR2(240);
1454
1455 l_org_id NUMBER;
1456
1457 cant_do_recon_acct EXCEPTION;
1458 current_calling_sequence VARCHAR2(2000);
1459
1460 l_distinct_org NUMBER; -- Bug 5674397
1461
1462
1463 -- Distinct Org Cursor
1464 -- Bug 5674397
1465 CURSOR distinct_org_cur IS
1466 SELECT distinct org_id
1467 FROM ap_checks
1468 WHERE checkrun_id = X_CHECKRUN_ID;
1469
1470 -- Payment Batch Cursor
1471
1472 CURSOR pay_batch_cur (p_org_id IN NUMBER)IS
1473 SELECT APHA.payment_history_id,
1474 APHA.trx_bank_amount,
1475 APHA.errors_bank_amount,
1476 APHA.charges_bank_amount,
1477 APHA.bank_currency_code,
1478 APHA.bank_to_base_xrate_type,
1479 APHA.bank_to_base_xrate_date,
1480 APHA.bank_to_base_xrate,
1481 APHA.trx_pmt_amount,
1482 APHA.errors_pmt_amount,
1483 APHA.charges_pmt_amount,
1484 APHA.pmt_currency_code,
1485 APHA.pmt_to_base_xrate_type,
1489 APHA.errors_base_amount,
1486 APHA.pmt_to_base_xrate_date,
1487 APHA.pmt_to_base_xrate,
1488 APHA.trx_base_amount,
1490 APHA.charges_base_amount,
1491 C.check_id,
1492 NVL(c.amount, 0),
1493 c.org_id
1494 FROM ap_payment_history_all APHA,
1495 ap_checks_all C
1496 WHERE APHA.check_id = C.check_id
1497 AND C.checkrun_id = X_CHECKRUN_ID
1498 AND C.org_id = p_org_id -- Bug 5674397
1499 AND APHA.transaction_type = 'PAYMENT CLEARING'
1500 AND C.status_lookup_code IN ('CLEARED',
1501 'CLEARED BUT UNACCOUNTED',
1502 'RECONCILED',
1503 'RECONCILED UNACCOUNTED')
1504 AND NOT EXISTS (SELECT APHB.payment_history_id
1505 FROM ap_payment_history APHB
1506 WHERE APHB.check_id = APHA.check_id
1507 AND APHB.rev_pmt_hist_id = APHA.payment_history_id)
1508 GROUP BY
1509 APHA.payment_history_id,
1510 APHA.trx_bank_amount,
1511 APHA.errors_bank_amount,
1512 APHA.charges_bank_amount,
1513 APHA.bank_currency_code,
1514 APHA.bank_to_base_xrate_type,
1515 APHA.bank_to_base_xrate_date,
1516 APHA.bank_to_base_xrate,
1517 APHA.trx_pmt_amount,
1518 APHA.errors_pmt_amount,
1519 APHA.charges_pmt_amount,
1520 APHA.pmt_currency_code,
1521 APHA.pmt_to_base_xrate_type,
1522 APHA.pmt_to_base_xrate_date,
1523 APHA.pmt_to_base_xrate,
1524 APHA.trx_base_amount,
1525 APHA.errors_base_amount,
1526 APHA.charges_base_amount,
1527 C.check_id,
1528 NVL(C.amount, 0),
1529 c.org_id;
1530
1531 l_key_value_list_recon GL_CA_UTILITY_PKG.r_key_value_arr;
1532 debug_info VARCHAR2(1000);
1533 l_dummy NUMBER;
1534
1535 BEGIN
1536
1537 current_calling_sequence :=
1538 X_CALLING_SEQUENCE || 'AP_RECONCILIATION_PKG.recon_payment_unclearing';
1539
1540
1541 IF (X_CHECKRUN_ID IS NOT NULL) THEN
1542
1543 -- Bug 5674397
1544 OPEN distinct_org_cur ;
1545 LOOP
1546
1547 FETCH distinct_org_cur INTO l_distinct_org;
1548
1549 l_debug_info := 'Inside distinct_org_cur cursor';
1550
1551 IF distinct_org_cur%NOTFOUND THEN
1552 IF distinct_org_cur%ROWCOUNT < 1 THEN
1553 RAISE no_data_found;
1554 ELSE -- No more rows
1555 EXIT ;
1556 END IF;
1557 END IF;
1558
1559 --bugfix:5623562
1560 SELECT nvl(recon_accounting_flag, 'N')
1561 INTO l_recon_accounting_flag
1562 FROM ap_system_parameters_all
1563 WHERE org_id = l_distinct_org ;
1564
1565 IF (l_recon_accounting_flag = 'Y') THEN
1566
1567 OPEN pay_batch_cur(l_distinct_org);
1568 LOOP
1569
1570 FETCH pay_batch_cur INTO
1571 l_rev_pmt_hist_id,
1572 l_ind_trx_bank_amount,
1573 l_ind_errors_bank_amount,
1574 l_ind_charges_bank_amount,
1575 l_bank_currency_code,
1576 l_bank_to_base_xrate_type,
1577 l_bank_to_base_xrate_date,
1578 l_bank_to_base_xrate,
1579 l_ind_trx_pmt_amount,
1580 l_ind_errors_pmt_amount,
1581 l_ind_charges_pmt_amount,
1582 l_pmt_currency_code,
1583 l_pmt_to_base_xrate_type,
1584 l_pmt_to_base_xrate_date,
1585 l_pmt_to_base_xrate,
1586 l_ind_trx_base_amount,
1587 l_ind_errors_base_amount,
1588 l_ind_charges_base_amount,
1589 l_check_id,
1590 l_check_amount,
1591 l_org_id; -- bug 4578865
1592 l_debug_info := 'Inside pay_batch_cur cursor';
1593 IF (pay_batch_cur%NOTFOUND) THEN
1594 IF (pay_batch_cur%ROWCOUNT < 1) THEN
1595 RAISE no_data_found;
1596 ELSE -- No more rows
1597 EXIT ;
1598 END IF;
1599 END IF;
1600 l_debug_info := 'Inside recon_payment_unclearing, before insert';
1601
1602 AP_RECONCILIATION_PKG.insert_payment_history
1603 (
1604 X_CHECK_ID => l_check_id,
1605 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
1606 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
1607 X_TRX_BANK_AMOUNT => l_ind_trx_bank_amount,
1608 X_ERRORS_BANK_AMOUNT => l_ind_errors_bank_amount,
1609 X_CHARGES_BANK_AMOUNT => l_ind_charges_bank_amount,
1610 X_BANK_CURRENCY_CODE => l_bank_currency_code,
1611 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
1612 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
1613 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
1614 X_TRX_PMT_AMOUNT => l_ind_trx_pmt_amount,
1615 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
1616 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
1617 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
1618 X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type,
1619 X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date,
1620 X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate,
1621 X_TRX_BASE_AMOUNT => l_ind_trx_base_amount,
1622 X_ERRORS_BASE_AMOUNT => l_ind_errors_base_amount,
1623 X_CHARGES_BASE_AMOUNT => l_ind_charges_base_amount,
1627 X_CREATION_DATE => X_CREATION_DATE,
1624 X_MATCHED_FLAG => X_MATCHED_FLAG,
1625 X_REV_PMT_HIST_ID => l_rev_pmt_hist_id,
1626 X_ORG_ID => l_org_id, -- bug 4578865
1628 X_CREATED_BY => X_CREATED_BY,
1629 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1630 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1631 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1632 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
1633 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
1634 X_PROGRAM_ID => X_PROGRAM_ID,
1635 X_REQUEST_ID => X_REQUEST_ID,
1636 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE
1637 );
1638
1639 END LOOP;
1640
1641 CLOSE pay_batch_cur;
1642
1643 END IF /* Reconciliation flag */;
1644
1645 END LOOP;
1646
1647 CLOSE distinct_org_cur;
1648
1649
1650 -- Update the status for all checks to NEGOTIABLE and clear the amounts
1651 -- Bug 1889740 added who parameters
1652 IF NOT (UnClear_Check(X_CHECKRUN_ID,
1653 NULL,
1654 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1655 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1656 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN)) THEN
1657 RAISE G_abort_it;
1658 END IF;
1659
1660
1661 ELSE /* checkrun_id not null....... */
1662
1663 -- Single Payment
1664
1665 -- Select to retrieve check information
1666 l_debug_info := 'checkrun is null, before select';
1667
1668 --bugfix:5674397
1669 SELECT nvl(recon_accounting_flag, 'N')
1670 INTO l_recon_accounting_flag
1671 FROM ap_system_parameters_all asp,
1672 ap_checks_all ac
1673 WHERE asp.org_id = ac.org_id
1674 AND ac.check_id = X_CHECK_ID;
1675
1676 --bugfix:5623562 -- 5674397
1677 IF (l_recon_accounting_flag = 'Y') THEN
1678
1679 --bugfix:2972765 added the following select and if statements.
1680 SELECT count(*)
1681 INTO l_dummy
1682 FROM ap_payment_history
1683 WHERE check_id=x_check_id
1684 AND transaction_type='PAYMENT CLEARING';
1685
1686 --bugfix:5623562
1687 IF (l_dummy>0 ) THEN
1688
1689 l_debug_info := 'checkrun is null, recon flag is Y, before select';
1690
1691
1692 SELECT APHA.payment_history_id,
1693 APHA.trx_bank_amount,
1694 APHA.errors_bank_amount,
1695 APHA.charges_bank_amount,
1696 APHA.bank_currency_code,
1697 APHA.bank_to_base_xrate_type,
1698 APHA.bank_to_base_xrate_date,
1699 APHA.bank_to_base_xrate,
1700 APHA.trx_pmt_amount,
1701 APHA.errors_pmt_amount,
1702 APHA.charges_pmt_amount,
1703 APHA.pmt_currency_code,
1704 APHA.pmt_to_base_xrate_type,
1705 APHA.pmt_to_base_xrate_date,
1706 APHA.pmt_to_base_xrate,
1707 APHA.trx_base_amount,
1708 APHA.errors_base_amount,
1709 APHA.charges_base_amount,
1710 NVL(C.amount, 0),
1711 c.org_id
1712 INTO l_rev_pmt_hist_id,
1713 l_ind_trx_bank_amount,
1714 l_ind_errors_bank_amount,
1715 l_ind_charges_bank_amount,
1716 l_bank_currency_code,
1717 l_bank_to_base_xrate_type,
1718 l_bank_to_base_xrate_date,
1719 l_bank_to_base_xrate,
1720 l_ind_trx_pmt_amount,
1721 l_ind_errors_pmt_amount,
1722 l_ind_charges_pmt_amount,
1723 l_pmt_currency_code,
1724 l_pmt_to_base_xrate_type,
1725 l_pmt_to_base_xrate_date,
1726 l_pmt_to_base_xrate,
1727 l_ind_trx_base_amount,
1728 l_ind_errors_base_amount,
1729 l_ind_charges_base_amount,
1730 l_check_amount,
1731 l_org_id
1732 FROM ap_payment_history APHA,
1733 ap_checks C
1734 WHERE APHA.check_id = X_CHECK_ID
1735 AND APHA.check_id = C.check_id
1736 AND APHA.transaction_type = 'PAYMENT CLEARING'
1737 AND C.status_lookup_code IN ('CLEARED',
1738 'CLEARED BUT UNACCOUNTED',
1739 'RECONCILED',
1740 'RECONCILED UNACCOUNTED')
1741 AND NOT EXISTS (SELECT APHB.payment_history_id
1742 FROM ap_payment_history APHB
1743 WHERE APHB.check_id = APHA.check_id
1744 AND APHB.rev_pmt_hist_id = APHA.payment_history_id);
1745
1746 AP_RECONCILIATION_PKG.Insert_Payment_History
1747 (
1748 X_CHECK_ID => X_check_id,
1749 X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
1750 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
1751 X_TRX_BANK_AMOUNT => l_ind_trx_bank_amount,
1752 X_ERRORS_BANK_AMOUNT => l_ind_errors_bank_amount,
1753 X_CHARGES_BANK_AMOUNT => l_ind_charges_bank_amount,
1754 X_BANK_CURRENCY_CODE => l_bank_currency_code,
1755 X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
1756 X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
1757 X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
1758 X_TRX_PMT_AMOUNT => l_ind_trx_pmt_amount,
1762 X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type,
1759 X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
1760 X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
1761 X_PMT_CURRENCY_CODE => l_pmt_currency_code,
1763 X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date,
1764 X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate,
1765 X_TRX_BASE_AMOUNT => l_ind_trx_base_amount,
1766 X_ERRORS_BASE_AMOUNT => l_ind_errors_base_amount,
1767 X_CHARGES_BASE_AMOUNT => l_ind_charges_base_amount,
1768 X_MATCHED_FLAG => X_MATCHED_FLAG,
1769 X_REV_PMT_HIST_ID => l_rev_pmt_hist_id,
1770 X_ORG_ID => l_org_id,
1771 X_CREATION_DATE => X_CREATION_DATE,
1772 X_CREATED_BY => X_CREATED_BY,
1773 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1774 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1775 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1776 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
1777 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
1778 X_PROGRAM_ID => X_PROGRAM_ID,
1779 X_REQUEST_ID => X_REQUEST_ID,
1780 X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE
1781 );
1782
1783 END IF; --bugg2972765
1784 END IF; /* Reconciliation_flag */
1785
1786 -- Update the status for all checks to NEGOTIABLE and clear the amounts
1787 --Bug 1889740 added who parameters
1788 IF NOT (UnClear_Check(NULL,
1789 X_CHECK_ID,
1790 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1791 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1792 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN)) THEN
1793 RAISE G_abort_it;
1794 END IF;
1795
1796 END IF ; /* checkrun_id not null....... */
1797
1798
1799 EXCEPTION
1800 WHEN NO_DATA_FOUND THEN
1801 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_NO_DATA_FOUND');
1802 APP_EXCEPTION.RAISE_EXCEPTION;
1803 WHEN OTHERS THEN
1804 IF (SQLCODE <> -20001) THEN
1805 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1806 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1807 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1808 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1809 END IF;
1810 APP_EXCEPTION.RAISE_EXCEPTION;
1811 END Recon_Payment_Unclearing ;
1812
1813 --============================================================================
1814 -- INSERT_PAYMENT_HISTORY : API to insert a row into the AP_Payment_History
1815 -- table.
1816 --
1817 -- Due to R12 SLA project impact, for every payment action, we will need
1818 -- to add the row in ap_payment_history
1819 -- in 11i - transaction types are
1820 -- PAYMENT MATURITY
1821 -- PAYMENT UNCLEARING
1822 -- PAYMENT CLEARING
1823 -- in R12, for upgrade and code impact, these transction type will be
1824 -- retained, the transaction types will be the same as event type.
1825 --============================================================================
1826 PROCEDURE Insert_Payment_History(
1827 X_CHECK_ID IN NUMBER,
1828 X_TRANSACTION_TYPE IN VARCHAR2,
1829 X_ACCOUNTING_DATE IN DATE,
1830 X_TRX_BANK_AMOUNT IN NUMBER,
1831 X_ERRORS_BANK_AMOUNT IN NUMBER,
1832 X_CHARGES_BANK_AMOUNT IN NUMBER,
1833 X_BANK_CURRENCY_CODE IN VARCHAR2,
1834 X_BANK_TO_BASE_XRATE_TYPE IN VARCHAR2,
1835 X_BANK_TO_BASE_XRATE_DATE IN DATE,
1836 X_BANK_TO_BASE_XRATE IN NUMBER,
1837 X_TRX_PMT_AMOUNT IN NUMBER,
1838 X_ERRORS_PMT_AMOUNT IN NUMBER,
1839 X_CHARGES_PMT_AMOUNT IN NUMBER,
1840 X_PMT_CURRENCY_CODE IN VARCHAR2,
1841 X_PMT_TO_BASE_XRATE_TYPE IN VARCHAR2,
1842 X_PMT_TO_BASE_XRATE_DATE IN DATE,
1843 X_PMT_TO_BASE_XRATE IN NUMBER,
1844 X_TRX_BASE_AMOUNT IN NUMBER,
1845 X_ERRORS_BASE_AMOUNT IN NUMBER,
1846 X_CHARGES_BASE_AMOUNT IN NUMBER,
1847 X_MATCHED_FLAG IN VARCHAR2,
1848 X_REV_PMT_HIST_ID IN NUMBER,
1849 X_ORG_ID IN NUMBER, -- bug 4578865
1850 X_CREATION_DATE IN DATE,
1851 X_CREATED_BY IN NUMBER,
1852 X_LAST_UPDATE_DATE IN DATE,
1853 X_LAST_UPDATED_BY IN NUMBER,
1854 X_LAST_UPDATE_LOGIN IN NUMBER,
1855 X_PROGRAM_UPDATE_DATE IN DATE,
1856 X_PROGRAM_APPLICATION_ID IN NUMBER,
1857 X_PROGRAM_ID IN NUMBER,
1858 X_REQUEST_ID IN NUMBER,
1859 X_CALLING_SEQUENCE IN VARCHAR2,
1860 -- Bug 3343314
1861 X_ACCOUNTING_EVENT_ID IN NUMBER DEFAULT NULL,
1862 -- Bug fix 5694577
1863 x_invoice_adjustment_event_id IN NUMBER DEFAULT NULL
1864 ) IS
1865
1866 l_accounting_event_id NUMBER(15); -- Events Project - 6
1867
1868 current_calling_sequence VARCHAR2(2000);
1869 l_event_calling_type VARCHAR2(30); -- 4578865
1870 l_doc_type VARCHAR2(15);
1871 l_debug_info VARCHAR2(100);
1872
1873 l_related_event_id NUMBER(15); -- Bug 5015973
1874
1875 BEGIN
1876 -- Update the calling sequence
1877 --
1878 current_calling_sequence :=
1879 'AP_RECONCILIATION_PKG.insert_payment_history<-'||X_Calling_Sequence;
1880
1881 -- Bug 3343314
1882 ---------------------------------------------------------------
1886 l_debug_info := 'Call AP_ACCOUNTING_EVENTS_PKG.CREATE_EVENTS';
1883 -- Create accounting event if event id is null
1884 ---------------------------------------------------------------
1885 IF x_accounting_event_id is null THEN
1887
1888 -- bug 4578865
1889 IF ( X_TRANSACTION_TYPE = 'PAYMENT CREATED' ) THEN
1890 l_event_calling_type := 'PAYMENT';
1891 ELSIF ( X_TRANSACTION_TYPE = 'REFUND RECORDED') THEN
1892 l_doc_type := 'R';
1893 l_event_calling_type := 'PAYMENT';
1894 ELSIF ( X_TRANSACTION_TYPE = 'PAYMENT CANCELLED' ) THEN
1895 l_event_calling_type := 'PAYMENT CANCELLATION';
1896 ELSIF ( X_TRANSACTION_TYPE = 'REFUND CANCELLED' ) THEN
1897 l_doc_type := 'R';
1898 l_event_calling_type := 'PAYMENT CANCELLATION';
1899 ELSIF ( X_TRANSACTION_TYPE
1900 IN ('PAYMENT MATURITY',
1901 'PAYMENT CLEARING',
1902 'PAYMENT UNCLEARING') ) THEN
1903 l_event_calling_type := X_TRANSACTION_TYPE;
1904
1905 ELSIF ( X_TRANSACTION_TYPE
1906 IN ( 'MANUAL PAYMENT ADJUSTED') ) THEN
1907 l_event_calling_type := 'PAYMENT ADJUSTMENT';
1908
1909 -- bug fix 5659451
1910 -- fixed the following condition check as the X_TRANSACTION_TYPE passed
1911 -- in will be 'PAYMENT MATURITY REVERSAL'
1912 -- ELSIF ( X_TRANSACTION_TYPE = 'PAYMENT MATURITY REVERSED') THEN
1913 ELSIF ( X_TRANSACTION_TYPE = 'PAYMENT MATURITY REVERSAL') THEN
1914 l_event_calling_type := 'PAYMENT MATURITY REVERSAL';
1915 ELSE
1916 l_event_calling_type := NULL;
1917
1918 END IF;
1919
1920 AP_ACCOUNTING_EVENTS_PKG.CREATE_EVENTS
1921 (
1922 p_event_type => l_event_calling_type, -- bug 4578865
1923 p_doc_type => l_doc_type,
1924 p_doc_id => x_check_id,
1925 p_accounting_date => x_accounting_date,
1926 p_accounting_event_id => l_accounting_event_id, -- OUT
1927 p_checkrun_name => NULL,
1928 p_calling_sequence => current_calling_sequence
1929 );
1930 ELSE
1931 l_accounting_event_id := x_accounting_event_id;
1932 END IF;
1933
1934 -- End Bug 3343314
1935
1936 -- Bug 5015973. We need to populate the related event id
1937 -- for creating the payment dists for reversals
1938 IF X_REV_PMT_HIST_ID IS NOT NULL THEN
1939
1940 SELECT Accounting_Event_ID
1941 INTO l_related_event_id
1942 FROM AP_Payment_History APH
1943 WHERE APH.Payment_History_ID = X_REV_PMT_HIST_ID;
1944
1945 END IF;
1946
1947 ----------------------------------------------------------------
1948 -- Insert payment information into payment history table
1949 ----------------------------------------------------------------
1950 l_debug_info := 'Inserting into AP_Payment_History';
1951
1952 INSERT INTO AP_PAYMENT_HISTORY_ALL
1953 ( PAYMENT_HISTORY_ID,
1954 CHECK_ID,
1955 ACCOUNTING_DATE,
1956 TRANSACTION_TYPE,
1957 POSTED_FLAG,
1958 TRX_BANK_AMOUNT,
1959 ERRORS_BANK_AMOUNT,
1960 CHARGES_BANK_AMOUNT,
1961 BANK_CURRENCY_CODE,
1962 BANK_TO_BASE_XRATE_TYPE,
1963 BANK_TO_BASE_XRATE_DATE,
1964 BANK_TO_BASE_XRATE,
1965 TRX_PMT_AMOUNT,
1966 ERRORS_PMT_AMOUNT,
1967 CHARGES_PMT_AMOUNT,
1968 PMT_CURRENCY_CODE,
1969 PMT_TO_BASE_XRATE_TYPE,
1970 PMT_TO_BASE_XRATE_DATE,
1971 PMT_TO_BASE_XRATE,
1972 TRX_BASE_AMOUNT,
1973 ERRORS_BASE_AMOUNT,
1974 CHARGES_BASE_AMOUNT,
1975 MATCHED_FLAG,
1976 REV_PMT_HIST_ID,
1977 CREATION_DATE,
1978 CREATED_BY,
1979 LAST_UPDATE_DATE,
1980 LAST_UPDATED_BY,
1981 LAST_UPDATE_LOGIN,
1982 PROGRAM_UPDATE_DATE,
1983 PROGRAM_APPLICATION_ID,
1984 PROGRAM_ID,
1985 ACCOUNTING_EVENT_ID, -- Events Project - 10
1986 REQUEST_ID ,
1987 ORG_ID, -- Bug 4526577
1988 RELATED_EVENT_ID, -- Bug 5015973
1989 invoice_adjustment_event_id -- bug 5694577
1990 )
1991 VALUES
1992 ( AP_PAYMENT_HISTORY_S.nextval,
1993 X_CHECK_ID,
1994 X_ACCOUNTING_DATE,
1995 X_TRANSACTION_TYPE,
1996 'N',
1997 X_TRX_BANK_AMOUNT,
1998 X_ERRORS_BANK_AMOUNT,
1999 X_CHARGES_BANK_AMOUNT,
2000 X_BANK_CURRENCY_CODE,
2001 X_BANK_TO_BASE_XRATE_TYPE,
2002 X_BANK_TO_BASE_XRATE_DATE,
2003 X_BANK_TO_BASE_XRATE,
2004 X_TRX_PMT_AMOUNT,
2005 X_ERRORS_PMT_AMOUNT,
2006 X_CHARGES_PMT_AMOUNT,
2007 X_PMT_CURRENCY_CODE,
2008 X_PMT_TO_BASE_XRATE_TYPE,
2009 X_PMT_TO_BASE_XRATE_DATE,
2010 X_PMT_TO_BASE_XRATE,
2011 X_TRX_BASE_AMOUNT,
2012 X_ERRORS_BASE_AMOUNT,
2013 X_CHARGES_BASE_AMOUNT,
2014 X_MATCHED_FLAG,
2015 X_REV_PMT_HIST_ID,
2016 X_CREATION_DATE,
2017 X_CREATED_BY,
2018 X_LAST_UPDATE_DATE,
2019 X_LAST_UPDATED_BY,
2020 X_LAST_UPDATE_LOGIN,
2021 X_PROGRAM_UPDATE_DATE,
2022 X_PROGRAM_APPLICATION_ID,
2023 X_PROGRAM_ID,
2024 L_ACCOUNTING_EVENT_ID, -- Events Project - 11
2025 X_REQUEST_ID,
2026 x_org_id, -- bug 4578865
2027 NVL(l_related_event_id, l_accounting_event_id), --Bug 5015973
2028 x_invoice_adjustment_event_id -- bug fix 5694577
2029 );
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 IF (SQLCODE <> -20001) then
2034 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
2038 'X_check_id = '||to_char(x_check_id)
2035 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
2036 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2037 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2039 ||' X_transaction_type = '||X_transaction_type
2040 ||' X_accounting_date = '||to_char(x_accounting_date)
2041 ||' X_trx_bank_amount = '||to_char(x_trx_bank_amount)
2042 ||' X_errors_bank_amount = '||to_char(x_errors_bank_amount)
2043 ||' X_charges_bank_amount = '||to_char(x_charges_bank_amount)
2044 ||' X_bank_currency_code = '||x_bank_currency_code
2045 ||' X_bank_to_base_xrate_type = '||x_bank_to_base_xrate_type
2046 ||' X_bank_to_base_xrate_date = '||to_char(x_bank_to_base_xrate_date)
2047 ||' X_bank_to_base_xrate = '||to_char(x_bank_to_base_xrate)
2048 ||' X_trx_pmt_amount = '||to_char(x_trx_pmt_amount)
2049 ||' X_errors_pmt_amount = '||to_char(x_errors_pmt_amount)
2050 ||' X_charges_pmt_amount = '||to_char(x_charges_pmt_amount)
2051 ||' X_pmt_currency_code = '||x_pmt_currency_code
2052 ||' X_pmt_to_base_xrate_type = '||x_pmt_to_base_xrate_type
2053 ||' X_pmt_to_base_xrate_date = '||to_char(x_pmt_to_base_xrate_date)
2054 ||' X_pmt_to_base_xrate = '||to_char(x_pmt_to_base_xrate)
2055 ||' X_trx_base_amount = '||to_char(x_trx_base_amount)
2056 ||' X_errors_base_amount = '||to_char(x_errors_base_amount)
2057 ||' X_charges_base_amount = '||to_char(x_charges_base_amount)
2058 ||' X_matched_flag = '||x_matched_flag
2059 ||' X_creation_date = '||to_char(x_creation_date)
2060 ||' X_created_by = '||to_char(x_created_by)
2061 ||' X_Last_update_date = '||to_char(x_last_update_date)
2062 ||' X_Last_updated_by = '||to_char(x_last_updated_by)
2063 ||' X_last_update_login = '||to_char(x_last_update_login)
2064 ||' X_program_update_date = '||to_char(x_program_update_date)
2065 ||' X_program_application_id = '||to_char(x_program_application_id)
2066 ||' X_program_id = '||to_char(x_program_id)
2067 ||' X_request_id = '||to_char(x_request_id));
2068 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2069 END IF;
2070 APP_EXCEPTION.RAISE_EXCEPTION;
2071
2072 END Insert_Payment_History;
2073
2074
2075 /* *************************************************************************
2076 * UNCLEAR_CHECK: Nulls all clearing related columns in the check so *
2077 * it no longer shows up as cleared. *
2078 ************************************************************************* */
2079 --Bug 1889740 added who parameters
2080 FUNCTION UnClear_Check(
2081 CC_CHECKRUN_ID NUMBER,
2082 CC_CHECK_ID NUMBER,
2083 X_LAST_UPDATE_DATE DATE,
2084 X_LAST_UPDATED_BY NUMBER,
2085 X_LAST_UPDATE_LOGIN NUMBER
2086 ) RETURN BOOLEAN IS
2087
2088 l_debug_info VARCHAR2(100);
2089
2090 BEGIN
2091
2092 IF (CC_CHECKRUN_ID IS NOT NULL AND CC_CHECK_ID IS NULL) THEN
2093
2094 l_debug_info := 'cc_checkrun_id not null and cc_check_id null, before Update';
2095
2096 UPDATE ap_checks
2097 SET CLEARED_DATE = NULL,
2098 CLEARED_AMOUNT = NULL,
2099 CLEARED_BASE_AMOUNT = NULL,
2100 CLEARED_ERROR_AMOUNT = NULL,
2101 CLEARED_ERROR_BASE_AMOUNT = NULL,
2102 CLEARED_CHARGES_AMOUNT = NULL,
2103 CLEARED_CHARGES_BASE_AMOUNT= NULL,
2104 CLEARED_EXCHANGE_RATE = NULL,
2105 CLEARED_EXCHANGE_DATE = NULL,
2106 CLEARED_EXCHANGE_RATE_TYPE = NULL,
2107 ACTUAL_VALUE_DATE = NULL,
2108 STATUS_LOOKUP_CODE = 'NEGOTIABLE',
2109 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2110 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2111 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
2112 WHERE checkrun_id = CC_CHECKRUN_ID
2113 AND status_lookup_code IN ('CLEARED',
2114 'RECONCILED',
2115 'RECONCILED UNACCOUNTED',
2116 'CLEARED BUT UNACCOUNTED');
2117
2118 ELSE
2119
2120 l_debug_info := 'cc_checkrun_id null or cc_check_id not null, before Update ap_checks';
2121
2122 UPDATE ap_checks
2123 SET CLEARED_DATE = NULL,
2124 CLEARED_AMOUNT = NULL,
2125 CLEARED_BASE_AMOUNT = NULL,
2126 CLEARED_ERROR_AMOUNT = NULL,
2127 CLEARED_ERROR_BASE_AMOUNT = NULL,
2128 CLEARED_CHARGES_AMOUNT = NULL,
2129 CLEARED_CHARGES_BASE_AMOUNT= NULL,
2130 CLEARED_EXCHANGE_RATE = NULL,
2131 CLEARED_EXCHANGE_DATE = NULL,
2132 CLEARED_EXCHANGE_RATE_TYPE = NULL,
2133 ACTUAL_VALUE_DATE = NULL,
2134 STATUS_LOOKUP_CODE = 'NEGOTIABLE',
2135 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2136 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2137 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
2138 WHERE check_id = CC_CHECK_ID
2139 AND status_lookup_code IN ('CLEARED',
2140 'RECONCILED',
2144 END IF;
2141 'RECONCILED UNACCOUNTED',
2142 'CLEARED BUT UNACCOUNTED');
2143
2145
2146 RETURN(TRUE);
2147
2148 EXCEPTION
2149 WHEN NO_DATA_FOUND THEN
2150 RETURN(TRUE);
2151 WHEN OTHERS THEN
2152 IF (SQLCODE <> -20001) THEN
2153 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2154 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2155 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','AP_RECONCILATION_PKG.Clear_Check');
2156 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2157 END IF;
2158 RETURN(FALSE);
2159
2160 END UnClear_Check;
2161
2162
2163 /* *************************************************************************
2164 * RECON_UPDATE_CHECK: Populates all clearing related columns given the *
2165 * appropriate amounts. Also sets the status of *
2166 * the check appropriately. *
2167 ************************************************************************* */
2168
2169 FUNCTION Recon_Update_Check(
2170 RU_CHECK_ID NUMBER,
2171 RU_CLEARED_AMOUNT NUMBER,
2172 RU_CLEARED_BASE_AMOUNT NUMBER,
2173 RU_CLEARED_ERROR_AMOUNT NUMBER,
2174 RU_CLEARED_ERROR_BASE_AMOUNT NUMBER,
2175 RU_CLEARED_CHARGES_AMOUNT NUMBER,
2176 RU_CLEARED_CHARGES_BASE_AMOUNT NUMBER,
2177 RU_CLEARED_DATE DATE,
2178 RU_CHECK_STATUS VARCHAR2,
2179 RU_CLEARED_XRATE NUMBER,
2180 RU_CLEARED_XRATE_TYPE VARCHAR2,
2181 RU_CLEARED_XRATE_DATE DATE,
2182 RU_ACTUAL_VALUE_DATE DATE,
2183 RU_LAST_UPDATED_BY NUMBER,
2184 RU_LAST_UPDATE_LOGIN NUMBER,
2185 RU_REQUEST_ID NUMBER
2186 ) RETURN BOOLEAN IS
2187 l_date DATE;
2188 l_debug_info VARCHAR2(100);
2189
2190 BEGIN
2191
2192 SELECT
2193 SYSDATE
2194 INTO
2195 l_date
2196 FROM
2197 DUAL;
2198
2199 l_debug_info := ' inside Recon_Update_Check, Before Update';
2200 UPDATE ap_checks
2201 SET CLEARED_AMOUNT = RU_CLEARED_AMOUNT,
2202 CLEARED_BASE_AMOUNT = RU_CLEARED_BASE_AMOUNT,
2203 CLEARED_ERROR_AMOUNT = RU_CLEARED_ERROR_AMOUNT,
2204 CLEARED_ERROR_BASE_AMOUNT = RU_CLEARED_ERROR_BASE_AMOUNT,
2205 CLEARED_CHARGES_AMOUNT = RU_CLEARED_CHARGES_AMOUNT,
2206 CLEARED_CHARGES_BASE_AMOUNT = RU_CLEARED_CHARGES_BASE_AMOUNT,
2207 CLEARED_DATE = RU_CLEARED_DATE,
2208 STATUS_LOOKUP_CODE = RU_CHECK_STATUS,
2209 CLEARED_EXCHANGE_RATE = RU_CLEARED_XRATE,
2210 CLEARED_EXCHANGE_DATE = RU_CLEARED_XRATE_DATE,
2211 CLEARED_EXCHANGE_RATE_TYPE = RU_CLEARED_XRATE_TYPE,
2212 ACTUAL_VALUE_DATE = RU_ACTUAL_VALUE_DATE,
2213 LAST_UPDATED_BY = RU_LAST_UPDATED_BY,
2214 LAST_UPDATE_DATE = L_DATE,
2215 LAST_UPDATE_LOGIN = RU_LAST_UPDATE_LOGIN,
2216 REQUEST_ID = RU_REQUEST_ID
2217 WHERE check_id = RU_CHECK_ID;
2218
2219 RETURN(TRUE);
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 IF (SQLCODE <> -20001) THEN
2223 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2224 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2225 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','AP_RECONCILATION_PKG.Recon_Update_Check');
2226 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2227 END IF;
2228 RETURN(FALSE);
2229
2230 END Recon_Update_Check;
2231
2232
2233 /* *************************************************************
2234 * FUNCTION: CASE_TYPE *
2235 * This function returns the currency scenario of *
2236 * the current payment activity given the scenarios*
2237 * defined in the top of this package *
2238 ************************************************************* */
2239
2240 FUNCTION CASE_TYPE(
2241 X_BANK_CURRENCY IN VARCHAR2,
2242 X_PAY_CURRENCY IN VARCHAR2,
2243 X_FUNC_CURRENCY IN VARCHAR2
2244 ) RETURN VARCHAR2 IS
2245 BEGIN
2246
2247 IF (x_bank_currency = x_func_currency AND
2248 x_bank_currency = x_pay_currency) THEN
2249
2250 RETURN('DOMESTIC');
2251
2252 ELSIF (x_bank_currency = x_func_currency AND
2253 x_bank_currency <> x_pay_currency) THEN
2254
2255 RETURN('INTERNATIONAL');
2256
2257 ELSIF (x_bank_currency <> x_func_currency AND
2258 x_bank_currency = x_pay_currency) THEN
2259
2260 RETURN('FOREIGN');
2261
2262 /******* This is not valid yet!
2263 ELSIF (x_bank_currency <> x_func_currency AND
2264 x_bank_currency <> x_pay_currency) THEN
2265
2266 RETURN('CROSS_CURRENCY');
2267 *******/
2268
2269 ELSE
2270
2271 raise G_abort_it;
2272
2273 END IF;
2274
2275 END CASE_TYPE;
2276
2277
2278 END AP_RECONCILIATION_PKG;