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