[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* update but actual check amount is used. *
************************************************************************* */
PROCEDURE Recon_Payment_History(
X_CHECKRUN_ID NUMBER,
X_CHECK_ID NUMBER,
X_ACCOUNTING_DATE DATE,
X_CLEARED_DATE DATE,
X_TRANSACTION_AMOUNT NUMBER, -- in bank curr.
X_TRANSACTION_TYPE VARCHAR2,
X_ERROR_AMOUNT NUMBER, -- in bank curr.
X_CHARGE_AMOUNT NUMBER, -- in bank curr.
X_CURRENCY_CODE VARCHAR2, -- bank curr. code
X_EXCHANGE_RATE_TYPE VARCHAR2, -- between payment and functional
X_EXCHANGE_RATE_DATE DATE, -- between payment and functional
X_EXCHANGE_RATE NUMBER, -- between payment and functional
X_MATCHED_FLAG VARCHAR2,
X_ACTUAL_VALUE_DATE DATE,
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_CREATED_BY NUMBER,
X_CREATION_DATE DATE,
X_PROGRAM_UPDATE_DATE DATE,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_REQUEST_ID NUMBER,
X_CALLING_SEQUENCE VARCHAR2
) AS
current_calling_sequence VARCHAR2(2000);
( X_LAST_UPDATE_DATE IS NULL ) or
( X_LAST_UPDATED_BY IS NULL ) ) THEN
APP_EXCEPTION.RAISE_EXCEPTION ;
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_CREATED_BY,
X_CREATION_DATE,
X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_REQUEST_ID,
X_CALLING_SEQUENCE ) ;
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_CREATED_BY,
X_CREATION_DATE,
X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_REQUEST_ID,
X_CALLING_SEQUENCE ) ;
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_CREATED_BY,
X_CREATION_DATE,
X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
X_REQUEST_ID,
X_CALLING_SEQUENCE ) ;
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_CREATED_BY NUMBER,
X_CREATION_DATE DATE,
X_PROGRAM_UPDATE_DATE DATE,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_REQUEST_ID NUMBER,
X_CALLING_SEQUENCE VARCHAR2
) AS
current_calling_sequence VARCHAR2(2000);
SELECT payment_history_id
INTO l_rev_pmt_hist_id
FROM ap_payment_history aph
WHERE transaction_type = 'PAYMENT MATURITY'
AND check_id = x_check_id
AND not exists (select 1
from ap_payment_history aph1
where aph.check_id=aph1.check_id
and aph.payment_history_id=aph1.rev_pmt_hist_id);
SELECT asp.base_currency_code,
asp.org_id
INTO l_functional_currency_code,
l_org_id
FROM ap_system_parameters asp,
ap_checks ac
WHERE ac.org_id = asp.org_id
AND ac.check_id = x_check_id;
SELECT exchange_rate,
exchange_date,
exchange_rate_type
INTO l_exchange_rate,
l_exchange_date,
l_exchange_rate_type
FROM ap_checks_all
WHERE check_id = X_CHECK_ID;
l_debug_info := 'Insert Payment History';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => X_CHECK_ID,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => NULL,
X_ERRORS_BANK_AMOUNT => NULL,
X_CHARGES_BANK_AMOUNT => NULL,
X_BANK_CURRENCY_CODE => NULL,
X_BANK_TO_BASE_XRATE_TYPE => NULL,
X_BANK_TO_BASE_XRATE_DATE => NULL,
X_BANK_TO_BASE_XRATE => NULL,
X_TRX_PMT_AMOUNT => X_TRANSACTION_AMOUNT,
X_ERRORS_PMT_AMOUNT => NULL,
X_CHARGES_PMT_AMOUNT => NULL,
X_PMT_CURRENCY_CODE => X_CURRENCY_CODE,
X_PMT_TO_BASE_XRATE_TYPE => NVL(X_EXCHANGE_RATE_TYPE, l_exchange_rate_type), -- bug 9069767
X_PMT_TO_BASE_XRATE_DATE => NVL(X_EXCHANGE_RATE_DATE, l_exchange_date), --bug 9069767
X_PMT_TO_BASE_XRATE => NVL(X_EXCHANGE_RATE, l_exchange_rate), --bug 9069767
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => NULL,
X_CHARGES_BASE_AMOUNT => NULL,
X_MATCHED_FLAG => NULL,
X_REV_PMT_HIST_ID => l_rev_pmt_hist_id, -- Bug3343314
X_ORG_ID => l_org_id, -- Bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
* DELETE_PAYMENT_MATURITY: Removes entry for payment maturity from *
* AP_PAYMENT_HISTORY for a particular check *
************************************************************************* */
PROCEDURE Delete_Payment_Maturity (
X_CHECK_ID NUMBER,
X_CALLING_SEQUENCE VARCHAR2
) AS
current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(1000) := 'Delete_Payment_Maturity' ;
'AP_RECONCILIATION_PKG.DELETE_PAYMENT_MATURITY' ;
DELETE FROM AP_PAYMENT_HISTORY
WHERE check_id = x_check_id
AND transaction_type = 'PAYMENT MATURITY'
AND nvl(posted_flag,'N') = 'N';
END Delete_Payment_Maturity ;
* RECON_PAYMENT_CLEARING: Creates entries/updates checks due to a *
* clearing event which may or may not be matched*
* to a bank statement. *
************************************************************************* */
PROCEDURE Recon_Payment_Clearing(
X_CHECKRUN_ID NUMBER,
X_CHECK_ID NUMBER,
X_ACCOUNTING_DATE DATE,
X_CLEARED_DATE DATE,
X_TRANSACTION_TYPE VARCHAR2,
X_TRX_BANK_AMOUNT NUMBER,
X_ERRORS_BANK_AMOUNT NUMBER,
X_CHARGES_BANK_AMOUNT NUMBER,
X_BANK_CURRENCY_CODE VARCHAR2,
X_PMT_TO_BASE_XRATE_TYPE VARCHAR2,
X_PMT_TO_BASE_XRATE_DATE DATE,
X_PMT_TO_BASE_XRATE NUMBER,
X_MATCHED_FLAG VARCHAR2,
X_ACTUAL_VALUE_DATE DATE,
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_CREATED_BY NUMBER,
X_CREATION_DATE DATE,
X_PROGRAM_UPDATE_DATE DATE,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_REQUEST_ID NUMBER,
X_CALLING_SEQUENCE VARCHAR2
) AS
--bugfix:5623562
l_recon_accounting_flag ap_system_parameters.recon_accounting_flag%TYPE;
SELECT distinct org_id
FROM ap_checks
WHERE checkrun_id = X_CHECKRUN_ID;
SELECT c.check_id,
c.status_lookup_code,
nvl(c.amount,0),
nvl(c.exchange_rate,1),
decode(c.future_pay_due_date,NULL,'N','Y'),
c.currency_code,
asp.base_currency_code,
asp.org_id,
nvl(asp.recon_accounting_flag, 'N')
FROM ap_checks_all c,
ap_system_parameters_all asp
WHERE c.checkrun_id = X_CHECKRUN_id
AND c.org_id = p_org_id
AND c.org_id = asp.org_id
AND c.status_lookup_code NOT IN -- Bug 3408260
('VOIDED','SPOILED','OVERFLOW','SETUP','STOP INITIATED',
'UNCONFIRMED SET UP',
/* 3575546 fbreslin: Add other statuses to skip. */
'RECONCILED', 'RECONCILED UNACCOUNTED',
'ISSUED')
GROUP BY c.check_id,
c.status_lookup_code,
nvl(c.amount,0),
nvl(c.exchange_rate,1),
decode(c.future_pay_due_date,NULL,'N','Y'),
c.currency_code,
asp.base_currency_code,
asp.org_id,
nvl(recon_accounting_flag, 'N')
ORDER BY nvl(c.amount,0);
SELECT nvl(recon_accounting_flag, 'N'),
base_currency_code
INTO l_recon_accounting_flag,
l_functional_currency_code
FROM ap_system_parameters;
SELECT count(check_id), sum(amount) , max(amount)
INTO l_payment_count, l_pay_sum_amt, l_max_pmt_amt
FROM AP_CHECKS
WHERE checkrun_id = x_checkrun_id
AND status_lookup_code NOT IN
('VOIDED','SETUP', 'OVERFLOW','SPOILED',
'STOP INITIATED','UNCONFIRMED SET UP',
'RECONCILED', 'RECONCILED UNACCOUNTED',
'ISSUED');
SELECT ac.currency_code,
asp.base_currency_code,
asp.org_id,
nvl(recon_accounting_flag, 'N') -- Bug 5674397
INTO l_pmt_currency_code,
l_functional_currency_code,
l_org_id,
l_recon_accounting_flag
FROM ap_checks ac,
ap_system_parameters asp
WHERE ac.checkrun_id = X_CHECKRUN_ID
AND asp.org_id = ac.org_id
AND ac.org_id = l_distinct_org
AND rownum = 1;
SELECT exchange_date,
exchange_rate_type,
exchange_rate
INTO l_pmt_to_base_xrate_date,
l_pmt_to_base_xrate_type,
l_pmt_to_base_xrate
FROM ap_checks_all ac
WHERE check_id = l_check_id;
l_debug_info := 'Inside International, Negotiable, before insert';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => l_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_trx_base_amount,
X_ERRORS_BANK_AMOUNT => l_errors_base_amount,
X_CHARGES_BANK_AMOUNT => l_charges_base_amount,
X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type, --bug8628166
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date, --bug8628166
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate, --bug8628166
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => NULL,
X_ORG_ID => l_org_id, -- bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
l_debug_info := 'Insert history for Negotiable, Foreign';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => l_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type, --bug8628166
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date, --bug8628166
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate, --bug8628166
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => NULL,
X_ORG_ID => l_org_id, -- Bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
l_debug_info := 'Inside Negotiable, Domestic, before Insert';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => l_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type,--bug8628166
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date,--bug8628166
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate,--bug8628166
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => NULL,
X_ORG_ID => l_org_id, -- Bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
l_debug_info := 'Before recon_update_check call';
IF NOT (Recon_Update_Check(l_check_id,
l_check_amount+l_ind_errors_pmt_amount
+l_ind_charges_pmt_amount,
l_trx_base_amount,
l_ind_errors_pmt_amount,
l_errors_base_amount,
l_ind_charges_pmt_amount,
l_charges_base_amount,
X_CLEARED_DATE,
l_status_code,
l_pmt_to_base_xrate, --bug8628166
l_pmt_to_base_xrate_type, --bug8628166
l_pmt_to_base_xrate_date, --bug8628166
X_ACTUAL_VALUE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID )) THEN
RAISE G_abort_it;
END IF; -- recon_update_check
SELECT c.currency_code,
c.status_lookup_code,
nvl(c.amount, 0),
nvl(c.exchange_rate,1),
decode(c.future_pay_due_date,NULL,'N','Y'),
asp.base_currency_code,
asp.org_id,
nvl(recon_accounting_flag, 'N') -- Bug 5674397
INTO l_pmt_currency_code,
l_status_lookup_code,
l_check_amount,
l_payment_xrate,
l_future_pay_flag,
l_functional_currency_code,
l_org_id,
l_recon_accounting_flag
FROM ap_checks_all c,
ap_system_parameters_all asp
WHERE c.check_id = X_CHECK_ID
AND c.org_id = asp.org_id;
SELECT exchange_date,
exchange_rate_type,
exchange_rate
INTO l_pmt_to_base_xrate_date,
l_pmt_to_base_xrate_type,
l_pmt_to_base_xrate
FROM ap_checks_all ac
WHERE check_id = X_CHECK_ID;
l_debug_info := 'Negotiable, International, before Insert';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => X_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_trx_base_amount,
X_ERRORS_BANK_AMOUNT => l_errors_base_amount,
X_CHARGES_BANK_AMOUNT => l_charges_base_amount,
X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type, --bug8628166
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date, --bug8628166
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate, --bug8628166
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => NULL,
X_ORG_ID => l_org_id, -- bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
l_debug_info := 'Negotiable, Foreign, before Insert';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => X_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type, --bug8628166
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date, --bug8628166
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate, --bug8628166
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => NULL,
X_ORG_ID => l_org_id, -- bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
l_debug_info := 'Negotiable, Domestic, before Insert';
AP_RECONCILIATION_PKG.Insert_Payment_History
( X_CHECK_ID => X_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_BANK_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_BANK_AMOUNT => l_ind_charges_pmt_amount,
X_BANK_CURRENCY_CODE => X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_check_amount
+ l_ind_errors_pmt_amount
+ l_ind_charges_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type, --bug8628166
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date, --bug8628166
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate, --bug8628166
X_TRX_BASE_AMOUNT => l_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => NULL,
X_ORG_ID => l_org_id, -- bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE ) ;
l_debug_info := 'Before recon_update_check call';
IF NOT (Recon_Update_Check(X_check_id,
l_check_amount+l_ind_errors_pmt_amount
+l_ind_charges_pmt_amount,
l_trx_base_amount,
l_ind_errors_pmt_amount,
l_errors_base_amount,
l_ind_charges_pmt_amount,
l_charges_base_amount,
X_CLEARED_DATE,
l_status_code,
l_pmt_to_base_xrate, --bug8628166
l_pmt_to_base_xrate_type, --bug8628166
l_pmt_to_base_xrate_date, --bug8628166
X_ACTUAL_VALUE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID )) THEN
RAISE G_abort_it;
END IF; -- recon_update_check
* RECON_PAYMENT_UNCLEARING: Creates entries/updates checks due to an *
* in clearing event which may or may not be *
* matched to a bank statement. *
************************************************************************* */
PROCEDURE recon_payment_unclearing
(
X_CHECKRUN_ID NUMBER,
X_CHECK_ID NUMBER,
X_ACCOUNTING_DATE DATE,
X_TRANSACTION_TYPE VARCHAR2,
X_MATCHED_FLAG VARCHAR2,
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_LOGIN NUMBER,
X_CREATED_BY NUMBER,
X_CREATION_DATE DATE,
X_PROGRAM_UPDATE_DATE DATE,
X_PROGRAM_APPLICATION_ID NUMBER,
X_PROGRAM_ID NUMBER,
X_REQUEST_ID NUMBER,
X_CALLING_SEQUENCE VARCHAR2
)
AS
--bgufix:5623562
l_recon_accounting_flag ap_system_parameters.recon_accounting_flag%TYPE;
SELECT distinct org_id
FROM ap_checks
WHERE checkrun_id = X_CHECKRUN_ID;
SELECT APHA.payment_history_id,
APHA.trx_bank_amount,
APHA.errors_bank_amount,
APHA.charges_bank_amount,
APHA.bank_currency_code,
APHA.bank_to_base_xrate_type,
APHA.bank_to_base_xrate_date,
APHA.bank_to_base_xrate,
APHA.trx_pmt_amount,
APHA.errors_pmt_amount,
APHA.charges_pmt_amount,
APHA.pmt_currency_code,
APHA.pmt_to_base_xrate_type,
APHA.pmt_to_base_xrate_date,
APHA.pmt_to_base_xrate,
APHA.trx_base_amount,
APHA.errors_base_amount,
APHA.charges_base_amount,
C.check_id,
NVL(c.amount, 0),
c.org_id
FROM ap_payment_history_all APHA,
ap_checks_all C
WHERE APHA.check_id = C.check_id
AND C.checkrun_id = X_CHECKRUN_ID
AND C.org_id = p_org_id -- Bug 5674397
AND APHA.transaction_type = 'PAYMENT CLEARING'
AND C.status_lookup_code IN ('CLEARED',
'CLEARED BUT UNACCOUNTED',
'RECONCILED',
'RECONCILED UNACCOUNTED')
AND NOT EXISTS (SELECT APHB.payment_history_id
FROM ap_payment_history APHB
WHERE APHB.check_id = APHA.check_id
AND APHB.rev_pmt_hist_id = APHA.payment_history_id)
GROUP BY
APHA.payment_history_id,
APHA.trx_bank_amount,
APHA.errors_bank_amount,
APHA.charges_bank_amount,
APHA.bank_currency_code,
APHA.bank_to_base_xrate_type,
APHA.bank_to_base_xrate_date,
APHA.bank_to_base_xrate,
APHA.trx_pmt_amount,
APHA.errors_pmt_amount,
APHA.charges_pmt_amount,
APHA.pmt_currency_code,
APHA.pmt_to_base_xrate_type,
APHA.pmt_to_base_xrate_date,
APHA.pmt_to_base_xrate,
APHA.trx_base_amount,
APHA.errors_base_amount,
APHA.charges_base_amount,
C.check_id,
NVL(C.amount, 0),
c.org_id;
SELECT nvl(recon_accounting_flag, 'N')
INTO l_recon_accounting_flag
FROM ap_system_parameters_all
WHERE org_id = l_distinct_org ;
l_debug_info := 'Inside recon_payment_unclearing, before insert';
AP_RECONCILIATION_PKG.insert_payment_history
(
X_CHECK_ID => l_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_ind_trx_bank_amount,
X_ERRORS_BANK_AMOUNT => l_ind_errors_bank_amount,
X_CHARGES_BANK_AMOUNT => l_ind_charges_bank_amount,
X_BANK_CURRENCY_CODE => l_bank_currency_code,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_ind_trx_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type,
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date,
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate,
X_TRX_BASE_AMOUNT => l_ind_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_ind_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_ind_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => l_rev_pmt_hist_id,
X_ORG_ID => l_org_id, -- bug 4578865
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE
);
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN)) THEN
RAISE G_abort_it;
l_debug_info := 'checkrun is null, before select';
SELECT nvl(recon_accounting_flag, 'N')
INTO l_recon_accounting_flag
FROM ap_system_parameters_all asp,
ap_checks_all ac
WHERE asp.org_id = ac.org_id
AND ac.check_id = X_CHECK_ID;
SELECT count(*)
INTO l_dummy
FROM ap_payment_history
WHERE check_id=x_check_id
AND transaction_type='PAYMENT CLEARING';
l_debug_info := 'checkrun is null, recon flag is Y, before select';
SELECT APHA.payment_history_id,
APHA.trx_bank_amount,
APHA.errors_bank_amount,
APHA.charges_bank_amount,
APHA.bank_currency_code,
APHA.bank_to_base_xrate_type,
APHA.bank_to_base_xrate_date,
APHA.bank_to_base_xrate,
APHA.trx_pmt_amount,
APHA.errors_pmt_amount,
APHA.charges_pmt_amount,
APHA.pmt_currency_code,
APHA.pmt_to_base_xrate_type,
APHA.pmt_to_base_xrate_date,
APHA.pmt_to_base_xrate,
APHA.trx_base_amount,
APHA.errors_base_amount,
APHA.charges_base_amount,
NVL(C.amount, 0),
c.org_id
INTO l_rev_pmt_hist_id,
l_ind_trx_bank_amount,
l_ind_errors_bank_amount,
l_ind_charges_bank_amount,
l_bank_currency_code,
l_bank_to_base_xrate_type,
l_bank_to_base_xrate_date,
l_bank_to_base_xrate,
l_ind_trx_pmt_amount,
l_ind_errors_pmt_amount,
l_ind_charges_pmt_amount,
l_pmt_currency_code,
l_pmt_to_base_xrate_type,
l_pmt_to_base_xrate_date,
l_pmt_to_base_xrate,
l_ind_trx_base_amount,
l_ind_errors_base_amount,
l_ind_charges_base_amount,
l_check_amount,
l_org_id
FROM ap_payment_history APHA,
ap_checks C
WHERE APHA.check_id = X_CHECK_ID
AND APHA.check_id = C.check_id
AND APHA.transaction_type = 'PAYMENT CLEARING'
AND C.status_lookup_code IN ('CLEARED',
'CLEARED BUT UNACCOUNTED',
'RECONCILED',
'RECONCILED UNACCOUNTED')
AND NOT EXISTS (SELECT APHB.payment_history_id
FROM ap_payment_history APHB
WHERE APHB.check_id = APHA.check_id
AND APHB.rev_pmt_hist_id = APHA.payment_history_id);
AP_RECONCILIATION_PKG.Insert_Payment_History
(
X_CHECK_ID => X_check_id,
X_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
X_TRX_BANK_AMOUNT => l_ind_trx_bank_amount,
X_ERRORS_BANK_AMOUNT => l_ind_errors_bank_amount,
X_CHARGES_BANK_AMOUNT => l_ind_charges_bank_amount,
X_BANK_CURRENCY_CODE => l_bank_currency_code,
X_BANK_TO_BASE_XRATE_TYPE=> l_bank_to_base_xrate_type,
X_BANK_TO_BASE_XRATE_DATE=> l_bank_to_base_xrate_date,
X_BANK_TO_BASE_XRATE => l_bank_to_base_xrate,
X_TRX_PMT_AMOUNT => l_ind_trx_pmt_amount,
X_ERRORS_PMT_AMOUNT => l_ind_errors_pmt_amount,
X_CHARGES_PMT_AMOUNT => l_ind_charges_pmt_amount,
X_PMT_CURRENCY_CODE => l_pmt_currency_code,
X_PMT_TO_BASE_XRATE_TYPE => l_pmt_to_base_xrate_type,
X_PMT_TO_BASE_XRATE_DATE => l_pmt_to_base_xrate_date,
X_PMT_TO_BASE_XRATE => l_pmt_to_base_xrate,
X_TRX_BASE_AMOUNT => l_ind_trx_base_amount,
X_ERRORS_BASE_AMOUNT => l_ind_errors_base_amount,
X_CHARGES_BASE_AMOUNT => l_ind_charges_base_amount,
X_MATCHED_FLAG => X_MATCHED_FLAG,
X_REV_PMT_HIST_ID => l_rev_pmt_hist_id,
X_ORG_ID => l_org_id,
X_CREATION_DATE => X_CREATION_DATE,
X_CREATED_BY => X_CREATED_BY,
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID => X_PROGRAM_ID,
X_REQUEST_ID => X_REQUEST_ID,
X_CALLING_SEQUENCE => CURRENT_CALLING_SEQUENCE
);
X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN)) THEN
RAISE G_abort_it;
PROCEDURE Insert_Payment_History(
X_CHECK_ID IN NUMBER,
X_TRANSACTION_TYPE IN VARCHAR2,
X_ACCOUNTING_DATE IN DATE,
X_TRX_BANK_AMOUNT IN NUMBER,
X_ERRORS_BANK_AMOUNT IN NUMBER,
X_CHARGES_BANK_AMOUNT IN NUMBER,
X_BANK_CURRENCY_CODE IN VARCHAR2,
X_BANK_TO_BASE_XRATE_TYPE IN VARCHAR2,
X_BANK_TO_BASE_XRATE_DATE IN DATE,
X_BANK_TO_BASE_XRATE IN NUMBER,
X_TRX_PMT_AMOUNT IN NUMBER,
X_ERRORS_PMT_AMOUNT IN NUMBER,
X_CHARGES_PMT_AMOUNT IN NUMBER,
X_PMT_CURRENCY_CODE IN VARCHAR2,
X_PMT_TO_BASE_XRATE_TYPE IN VARCHAR2,
X_PMT_TO_BASE_XRATE_DATE IN DATE,
X_PMT_TO_BASE_XRATE IN NUMBER,
X_TRX_BASE_AMOUNT IN NUMBER,
X_ERRORS_BASE_AMOUNT IN NUMBER,
X_CHARGES_BASE_AMOUNT IN NUMBER,
X_MATCHED_FLAG IN VARCHAR2,
X_REV_PMT_HIST_ID IN NUMBER,
X_ORG_ID IN NUMBER, -- bug 4578865
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_PROGRAM_APPLICATION_ID IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_REQUEST_ID IN NUMBER,
X_CALLING_SEQUENCE IN VARCHAR2,
-- Bug 3343314
X_ACCOUNTING_EVENT_ID IN NUMBER DEFAULT NULL,
-- Bug fix 5694577
x_invoice_adjustment_event_id IN NUMBER DEFAULT NULL
) IS
l_accounting_event_id NUMBER(15); -- Events Project - 6
l_api_name CONSTANT VARCHAR2(1000) := 'Insert_Payment_History' ;
'AP_RECONCILIATION_PKG.insert_payment_history<-'||X_Calling_Sequence;
SELECT Accounting_Event_ID
INTO l_related_event_id
FROM AP_Payment_History APH
WHERE APH.Payment_History_ID = X_REV_PMT_HIST_ID;
SELECT Accounting_Event_ID
INTO l_related_event_id
FROM AP_Payment_History APH
WHERE APH.Check_ID = x_check_id
AND APH.Transaction_Type =
DECODE(X_Transaction_Type, 'PAYMENT ADJUSTED' ,'PAYMENT CREATED',
'MANUAL PAYMENT ADJUSTED', 'PAYMENT CREATED',
'PAYMENT MATURITY ADJUSTED', 'PAYMENT MATURITY',
'PAYMENT CLEARING ADJUSTED', 'PAYMENT CLEARING',
'REFUND ADJUSTED', 'REFUND RECORDED', 'MANUAL REFUND ADJUSTED' ) --8449674
AND NOT EXISTS (SELECT 'Reversal Pay Hist'
FROM AP_Payment_History APH1
WHERE APH1.Check_ID = x_check_id
AND APH1.Rev_Pmt_Hist_ID = APH.Payment_History_ID)
AND ROWNUM = 1;
l_debug_info := 'Inserting into AP_Payment_History';
INSERT INTO AP_PAYMENT_HISTORY_ALL
( PAYMENT_HISTORY_ID,
CHECK_ID,
ACCOUNTING_DATE,
TRANSACTION_TYPE,
POSTED_FLAG,
TRX_BANK_AMOUNT,
ERRORS_BANK_AMOUNT,
CHARGES_BANK_AMOUNT,
BANK_CURRENCY_CODE,
BANK_TO_BASE_XRATE_TYPE,
BANK_TO_BASE_XRATE_DATE,
BANK_TO_BASE_XRATE,
TRX_PMT_AMOUNT,
ERRORS_PMT_AMOUNT,
CHARGES_PMT_AMOUNT,
PMT_CURRENCY_CODE,
PMT_TO_BASE_XRATE_TYPE,
PMT_TO_BASE_XRATE_DATE,
PMT_TO_BASE_XRATE,
TRX_BASE_AMOUNT,
ERRORS_BASE_AMOUNT,
CHARGES_BASE_AMOUNT,
MATCHED_FLAG,
REV_PMT_HIST_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
ACCOUNTING_EVENT_ID, -- Events Project - 10
REQUEST_ID ,
ORG_ID, -- Bug 4526577
RELATED_EVENT_ID, -- Bug 5015973
invoice_adjustment_event_id -- bug 5694577
)
VALUES
( AP_PAYMENT_HISTORY_S.nextval,
X_CHECK_ID,
X_ACCOUNTING_DATE,
X_TRANSACTION_TYPE,
'N',
X_TRX_BANK_AMOUNT,
X_ERRORS_BANK_AMOUNT,
X_CHARGES_BANK_AMOUNT,
X_BANK_CURRENCY_CODE,
X_BANK_TO_BASE_XRATE_TYPE,
X_BANK_TO_BASE_XRATE_DATE,
X_BANK_TO_BASE_XRATE,
X_TRX_PMT_AMOUNT,
X_ERRORS_PMT_AMOUNT,
X_CHARGES_PMT_AMOUNT,
X_PMT_CURRENCY_CODE,
X_PMT_TO_BASE_XRATE_TYPE,
X_PMT_TO_BASE_XRATE_DATE,
X_PMT_TO_BASE_XRATE,
X_TRX_BASE_AMOUNT,
X_ERRORS_BASE_AMOUNT,
X_CHARGES_BASE_AMOUNT,
X_MATCHED_FLAG,
X_REV_PMT_HIST_ID,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_PROGRAM_UPDATE_DATE,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_ID,
L_ACCOUNTING_EVENT_ID, -- Events Project - 11
X_REQUEST_ID,
x_org_id, -- bug 4578865
NVL(l_related_event_id, l_accounting_event_id), --Bug 5015973
x_invoice_adjustment_event_id -- bug fix 5694577
);
||' X_Last_update_date = '||to_char(x_last_update_date)
||' X_Last_updated_by = '||to_char(x_last_updated_by)
||' X_last_update_login = '||to_char(x_last_update_login)
||' X_program_update_date = '||to_char(x_program_update_date)
||' X_program_application_id = '||to_char(x_program_application_id)
||' X_program_id = '||to_char(x_program_id)
||' X_request_id = '||to_char(x_request_id));
END Insert_Payment_History;
X_LAST_UPDATE_DATE DATE,
X_LAST_UPDATED_BY NUMBER,
X_LAST_UPDATE_LOGIN NUMBER
) RETURN BOOLEAN IS
l_debug_info VARCHAR2(240);
l_debug_info := 'cc_checkrun_id not null and cc_check_id null, before Update';
UPDATE ap_checks
SET CLEARED_DATE = NULL,
CLEARED_AMOUNT = NULL,
CLEARED_BASE_AMOUNT = NULL,
CLEARED_ERROR_AMOUNT = NULL,
CLEARED_ERROR_BASE_AMOUNT = NULL,
CLEARED_CHARGES_AMOUNT = NULL,
CLEARED_CHARGES_BASE_AMOUNT= NULL,
CLEARED_EXCHANGE_RATE = NULL,
CLEARED_EXCHANGE_DATE = NULL,
CLEARED_EXCHANGE_RATE_TYPE = NULL,
ACTUAL_VALUE_DATE = NULL,
STATUS_LOOKUP_CODE = 'NEGOTIABLE',
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
WHERE checkrun_id = CC_CHECKRUN_ID
AND status_lookup_code IN ('CLEARED',
'RECONCILED',
'RECONCILED UNACCOUNTED',
'CLEARED BUT UNACCOUNTED');
l_debug_info := 'cc_checkrun_id null or cc_check_id not null, before Update ap_checks';
UPDATE ap_checks
SET CLEARED_DATE = NULL,
CLEARED_AMOUNT = NULL,
CLEARED_BASE_AMOUNT = NULL,
CLEARED_ERROR_AMOUNT = NULL,
CLEARED_ERROR_BASE_AMOUNT = NULL,
CLEARED_CHARGES_AMOUNT = NULL,
CLEARED_CHARGES_BASE_AMOUNT= NULL,
CLEARED_EXCHANGE_RATE = NULL,
CLEARED_EXCHANGE_DATE = NULL,
CLEARED_EXCHANGE_RATE_TYPE = NULL,
ACTUAL_VALUE_DATE = NULL,
STATUS_LOOKUP_CODE = 'NEGOTIABLE',
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
WHERE check_id = CC_CHECK_ID
AND status_lookup_code IN ('CLEARED',
'RECONCILED',
'RECONCILED UNACCOUNTED',
'CLEARED BUT UNACCOUNTED');
* RECON_UPDATE_CHECK: Populates all clearing related columns given the *
* appropriate amounts. Also sets the status of *
* the check appropriately. *
************************************************************************* */
FUNCTION Recon_Update_Check(
RU_CHECK_ID NUMBER,
RU_CLEARED_AMOUNT NUMBER,
RU_CLEARED_BASE_AMOUNT NUMBER,
RU_CLEARED_ERROR_AMOUNT NUMBER,
RU_CLEARED_ERROR_BASE_AMOUNT NUMBER,
RU_CLEARED_CHARGES_AMOUNT NUMBER,
RU_CLEARED_CHARGES_BASE_AMOUNT NUMBER,
RU_CLEARED_DATE DATE,
RU_CHECK_STATUS VARCHAR2,
RU_CLEARED_XRATE NUMBER,
RU_CLEARED_XRATE_TYPE VARCHAR2,
RU_CLEARED_XRATE_DATE DATE,
RU_ACTUAL_VALUE_DATE DATE,
RU_LAST_UPDATED_BY NUMBER,
RU_LAST_UPDATE_LOGIN NUMBER,
RU_REQUEST_ID NUMBER
) RETURN BOOLEAN IS
l_date DATE;
l_api_name CONSTANT VARCHAR2(1000) := 'Recon_Update_Check' ;
SELECT
SYSDATE
INTO
l_date
FROM
DUAL;
l_debug_info := ' inside Recon_Update_Check, Before Update';
UPDATE ap_checks_all
SET CLEARED_AMOUNT = RU_CLEARED_AMOUNT,
CLEARED_BASE_AMOUNT = RU_CLEARED_BASE_AMOUNT,
CLEARED_ERROR_AMOUNT = RU_CLEARED_ERROR_AMOUNT,
CLEARED_ERROR_BASE_AMOUNT = RU_CLEARED_ERROR_BASE_AMOUNT,
CLEARED_CHARGES_AMOUNT = RU_CLEARED_CHARGES_AMOUNT,
CLEARED_CHARGES_BASE_AMOUNT = RU_CLEARED_CHARGES_BASE_AMOUNT,
CLEARED_DATE = RU_CLEARED_DATE,
STATUS_LOOKUP_CODE = RU_CHECK_STATUS,
CLEARED_EXCHANGE_RATE = RU_CLEARED_XRATE,
CLEARED_EXCHANGE_DATE = RU_CLEARED_XRATE_DATE,
CLEARED_EXCHANGE_RATE_TYPE = RU_CLEARED_XRATE_TYPE,
ACTUAL_VALUE_DATE = RU_ACTUAL_VALUE_DATE,
LAST_UPDATED_BY = RU_LAST_UPDATED_BY,
LAST_UPDATE_DATE = L_DATE,
LAST_UPDATE_LOGIN = RU_LAST_UPDATE_LOGIN,
REQUEST_ID = RU_REQUEST_ID
WHERE check_id = RU_CHECK_ID;
FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','AP_RECONCILATION_PKG.Recon_Update_Check');
END Recon_Update_Check;