[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(-1 * APAD.Amount)
INTO l_prepay_acctg_amt
FROM AP_Prepay_App_Dists APAD,
AP_Invoice_Distributions_All AID
WHERE APAD.Prepay_App_Distribution_ID = AID.Invoice_Distribution_ID
AND AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
SELECT AID.Amount
INTO l_prepay_amt
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
SELECT AID.Invoice_Distribution_ID,
AID.Line_Type_Lookup_Code,
AID.related_id,
AID.Amount,
AID.Base_Amount,
AID.Invoice_Id,
AID.accounting_event_id,
AID.historical_flag
FROM AP_Invoice_Distributions_All AID,
Financials_System_Params_All FSP
WHERE AID.Invoice_ID = p_invoice_id
AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT', 'ERV', 'TERV')
AND AID.Prepay_Distribution_ID IS NULL
AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
AND AID.Org_ID = FSP.Org_ID
--Bug6511672
/*AND 'INVOICE CANCELLED' <> (SELECT event_type_code
FROM xla_events
WHERE event_id = AID.accounting_event_id)*/
--bug6614371
-- Bug 6712649. Added Credit and Debit memo cancelled
AND NOT EXISTS (SELECT 1
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND event_type_code IN ('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
SELECT SUM(amount), count(1)
FROM ap_invoice_distributions_all aid,
xla_events evnt,
ap_system_parameters_all asp
WHERE aid.accounting_event_id = p_acct_event_id
AND aid.accounting_event_id = evnt.event_id
AND evnt.event_type_code IN ('INVOICE ADJUSTED',
'CREDIT MEMO ADJUSTED',
'DEBIT MEMO ADJUSTED',
'PREPAYMENT ADJUSTED') -- added for bug#9545528 and 12731687
AND aid.org_id = asp.org_id
AND automatic_offsets_flag = 'N'
AND aid.historical_flag = 'Y';
SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
-- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
-- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
APHD.Invoice_Dist_Base_Amount)
INTO l_sum_pay_dist_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Invoice_Payments_All AIP
WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
AND APHD.Pay_Dist_Lookup_Code IN
('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING'))
OR (APHD.Pay_Dist_Lookup_Code='AWT'
AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
AND AIP.Invoice_ID = p_inv_rec.invoice_id
AND AIP.Check_ID = APH.Check_ID
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND AIP.Invoice_payment_id = APHD.Invoice_payment_id -- Bug 8722710
AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
'PAYMENT CLEARING ADJUSTED')
-- bug 9257606, ignore the event/payment if reversed
AND NVL(AIP.reversal_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Event Reversed'
FROM Ap_Payment_History_All APH_REL
WHERE APH_REL.check_id = APH.check_id
AND NVL(APH_REL.related_event_id, APH_REL.accounting_event_id) =
NVL(APH.related_event_id, APH.accounting_event_id)
AND APH_REL.rev_pmt_hist_id IS NOT NULL);
SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
-- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
-- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
APHD.Invoice_Dist_Base_Amount)
INTO l_sum_pay_dist_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Invoice_Payments_All AIP
WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
AND APHD.Pay_Dist_Lookup_Code IN
('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING' ))
OR (APHD.Pay_Dist_Lookup_Code='AWT'
AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
AND AIP.Invoice_ID = p_inv_rec.invoice_id
AND AIP.Check_ID = APH.Check_ID
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND AIP.Invoice_payment_id = APHD.Invoice_payment_id -- Bug 8722710
AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
'PAYMENT MATURITY ADJUSTED')
-- bug 9257606, ignore the event/payment if reversed
AND NVL(AIP.reversal_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Event Reversed'
FROM Ap_Payment_History_All APH_REL
WHERE APH_REL.check_id = APH.check_id
AND NVL(APH_REL.related_event_id, APH_REL.accounting_event_id) =
NVL(APH.related_event_id, APH.accounting_event_id)
AND APH_REL.rev_pmt_hist_id IS NOT NULL);
SELECT SUM(--DECODE(APHD.Pay_Dist_Lookup_Code,
-- 'EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
-- 'TAX EXCHANGE RATE VARIANCE', -1*APHD.Invoice_Dist_Base_Amount,
APHD.Invoice_Dist_Base_Amount)
INTO l_sum_pay_dist_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Invoice_Payments_All AIP
WHERE ((APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
AND APHD.Pay_Dist_Lookup_Code IN
('CASH', 'DISCOUNT', 'FINAL PAYMENT ROUNDING' ))
OR (APHD.Pay_Dist_Lookup_Code='AWT'
AND APHD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id))
AND AIP.Invoice_ID = p_inv_rec.invoice_id
AND AIP.Check_ID = APH.Check_ID
AND AIP.Invoice_payment_id = APHD.Invoice_payment_id -- Bug 8722710
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED',
'MANUAL REFUND ADJUSTED') --bug 10336668
-- bug 9257606, ignore the event/payment if reversed
AND NVL(AIP.reversal_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Event Reversed'
FROM Ap_Payment_History_All APH_REL
WHERE APH_REL.check_id = APH.check_id
AND NVL(APH_REL.related_event_id, APH_REL.accounting_event_id) =
NVL(APH.related_event_id, APH.accounting_event_id)
AND APH_REL.rev_pmt_hist_id IS NOT NULL);
SELECT SUM(APAD.Base_Amount)
INTO l_sum_prepay_base_amt
FROM AP_Prepay_App_Dists APAD,
AP_Prepay_History_All APH
WHERE ((APAD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX'))
OR (APAD.AWT_Related_ID = l_inv_dist_rec.invoice_distribution_id
AND APAD.Prepay_Dist_Lookup_Code = 'AWT'))
AND APH.Invoice_ID = p_inv_rec.invoice_id
AND APH.Prepay_History_ID = APAD.Prepay_History_ID
AND NOT EXISTS (SELECT 'reversed'
FROM Ap_Invoice_Distributions_All AID
WHERE APAD.Prepay_App_Distribution_ID =
AID.Invoice_Distribution_ID
AND AID.reversal_flag = 'Y');
SELECT NVL(base_amount, 0)
INTO l_erv_base_amount
FROM ap_invoice_distributions_all AID
WHERE AID.line_type_lookup_code in ('ERV', 'TERV')
AND AID.invoice_id = l_inv_dist_rec.invoice_id
AND AID.related_id = l_inv_dist_rec.invoice_distribution_id;
/* If this is a prepayment type of event then insert the rounding distribution into
prepayment dists table. Otherwise insert into payment dists table */
IF (p_prepay_dist_rec.invoice_distribution_id IS NOT NULL) THEN
-- Get the prepay appl pay dists info for this distribution
--bug 7614480
/* SELECT APAD.*
INTO l_max_prepay_rec
FROM AP_Prepay_App_Dists APAD
WHERE APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND APAD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
AND Rownum = 1;*/
select accounting_event_id
into l_pad_rec.accounting_event_id
from ap_prepay_history_all
where prepay_history_id = p_prepay_hist_rec.prepay_history_id;
'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert';
AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert
(l_pad_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert '
|| 'executed';
/*SELECT APHD.*
INTO l_max_pay_rec
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = p_pay_hist_rec.payment_history_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_rec.invoice_distribution_id
AND Rownum = 1;*/ --bug 7614480
l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
SELECT APHD.Payment_History_ID,
APHD.Invoice_Distribution_ID,
APHD.Invoice_Adjustment_Event_ID
INTO l_payment_hist_id,
l_invoice_dist_id,
l_inv_adj_event_id
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Invoice_Distribution_ID =
(SELECT MAX(APHD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APHD1
WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD1.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD1.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
AND APHD1.Pay_Dist_Lookup_Code not IN('AWT') --8727277
AND ABS(APHD1.Amount) =
(SELECT MAX(ABS(APHD2.Amount))
FROM AP_Payment_Hist_Dists APHD2
WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD2.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD2.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
AND APHD2.Pay_Dist_Lookup_Code not IN('AWT') --8727277
))
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND Rownum = 1;
SELECT SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Invoice_Dist_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Paid_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Cleared_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Matured_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Paid_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Cleared_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK ERROR', APHD.Cleared_Base_Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK CHARGE', APHD.Cleared_Base_Amount, 0))
INTO l_inv_rate_sum_amt,
l_pay_rate_sum_amt,
l_clr_rate_sum_amt,
l_mat_rate_sum_amt,
l_disc_pay_rate_sum_amt,
l_disc_clr_rate_sum_amt,
l_err_clr_rate_sum_amt,
l_chrg_clr_rate_sum_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.Invoice_Payment_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
SELECT sum(nvl(APHD.amount,0)),
max(APHD.Invoice_Distribution_Id)
,SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Invoice_Dist_Base_Amount,
'AWT', APHD.Invoice_Dist_Base_Amount,
'DISCOUNT', APHD.Invoice_Dist_Base_Amount, 0))
INTO l_pay_sum_amt,l_max_dist_id,l_inv_rate_sum_full_amt
FROM AP_PAYMENT_HIST_DISTS APHD,
AP_PAYMENT_HISTORY_ALL APH
WHERE
APHD.PAYMENT_HISTORY_ID =APH.PAYMENT_HISTORY_ID
ANd APHD.INVOICE_PAYMENT_ID =p_inv_pay_rec.Invoice_Payment_ID --6614295
AND APHD.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
SELECT sum(AI.invoice_amount) ,sum(AI.base_amount)
INTO l_inv_amt,l_inv_base_amt
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_inv_rec.invoice_id;
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.invoice_dist_Base_Amount = APHD.invoice_dist_Base_Amount + NVL(l_inv_dist_diff_amt,0)
WHERE APHD.Invoice_Distribution_ID = l_invoice_dist_id -- l_max_dist_id
AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
AND APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.PAYMENT_HIST_DIST_ID= (select max(APHD1.PAYMENT_HIST_DIST_ID)
from AP_Payment_Hist_Dists APHD1
where APHD1.invoice_distribution_id = l_invoice_dist_id); --l_max_dist_id);
l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
If there is a difference between the total and sum amounts then we will insert
the difference as the rounding amounts */
l_clr_rate_diff_amt := l_clr_rate_total_amt - l_clr_rate_sum_amt;
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Invoice_Dist_Base_Amount =
APHD.Invoice_Dist_Base_Amount + NVL(l_inv_rate_diff_amt,0),
APHD.Rounding_Amt = l_inv_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_inv_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
+ NVL(l_clr_rate_diff_amt,0),
APHD.Rounding_Amt = l_clr_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_clr_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Matured_Base_Amount = APHD.Matured_Base_Amount
+ NVL(l_mat_rate_diff_amt,0),
APHD.Rounding_Amt = l_mat_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_mat_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount + NVL(l_pay_rate_diff_amt,0),
APHD.Rounding_Amt = l_pay_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount
+ NVL(l_disc_pay_rate_diff_amt,0),
APHD.Rounding_Amt = l_disc_pay_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
l_log_msg := 'Updated discount rounding amount for payment';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
+ NVL(l_disc_clr_rate_diff_amt,0),
APHD.Rounding_Amt = l_disc_clr_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
l_log_msg := 'Updated discount rounding amount for clearing';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
+ NVL(l_err_clr_rate_diff_amt,0),
APHD.Rounding_Amt = l_err_clr_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
l_log_msg := 'Updated error rounding amount';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount
+ NVL(l_chrg_clr_rate_diff_amt,0),
APHD.Rounding_Amt = l_chrg_clr_rate_diff_amt
WHERE APHD.Payment_History_ID = l_payment_hist_id
AND APHD.Invoice_Distribution_ID = l_invoice_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
l_log_msg := 'Updated charge rounding amount';
SELECT APHD.Invoice_Dist_Amount,
APHD.Invoice_Dist_Base_Amount,
APHD.matured_base_amount,
APHD.paid_base_Amount,
APHD.cleared_base_amount
INTO l_inv_dist_amt,
l_inv_dist_base_amt,
l_inv_mat_base_amt,
l_inv_paid_base_amt,
l_inv_clr_base_amt
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Invoice_Dist_Base_Amount = 0,
APHD.Rounding_Amt = -sign(l_inv_dist_amt)*l_inv_dist_base_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Invoice_Dist_Base_Amount = APHD.Invoice_Dist_Base_Amount +
NVL(l_inv_dist_rounding_amt,
0),
APHD.Rounding_Amt=l_inv_dist_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_inv_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = 0,
APHD.Rounding_Amt=-sign(l_inv_clr_base_amt)*l_inv_clr_base_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
NVL(l_clr_rate_rounding_amt, 0),
APHD.Rounding_Amt=l_clr_rate_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_clr_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Matured_Base_Amount = 0,
APHD.Rounding_Amt=-sign(l_inv_mat_base_amt )*l_inv_mat_base_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Matured_Base_Amount = APHD.Matured_Base_Amount +
NVL(l_mat_rate_rounding_amt, 0),
APHD.Rounding_Amt=l_mat_rate_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_mat_rate_diff_amt';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Paid_Base_Amount = 0,
APHD.rounding_amt=-sign(l_inv_paid_base_amt)*l_inv_paid_base_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount +
NVL(l_pay_rate_rounding_amt, 0),
APHD.Rounding_amt=l_pay_rate_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Updated rounding amount for l_pay_rate_diff_amt';
SELECT APHD.Invoice_Dist_Amount,
APHD.Invoice_Dist_Base_Amount,
APHD.matured_base_amount,
APHD.paid_base_Amount,
APHD.cleared_base_amount
INTO l_inv_dist_amt_disc,
l_inv_dist_base_amt_disc,
l_inv_mat_base_amt_disc,
l_inv_paid_base_amt_disc,
l_inv_clr_base_amt_disc
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
l_log_msg := 'exception when select discount row amount values for payment'||SQLERRM;
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Paid_Base_Amount = 0,
APHD.Rounding_amt=-sign(l_inv_paid_base_amt_disc)*l_inv_paid_base_amt_disc
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Paid_Base_Amount = APHD.Paid_Base_Amount +
NVL(l_disc_pay_rounding_amt, 0),
APHD.Rounding_amt=l_disc_pay_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
l_log_msg := 'Updated discount rounding amount for payment';
SELECT APHD.Invoice_Dist_Amount,
APHD.Invoice_Dist_Base_Amount,
APHD.matured_base_amount,
APHD.paid_base_Amount,
APHD.cleared_base_amount
INTO l_inv_dist_amt_disc,
l_inv_dist_base_amt_disc,
l_inv_mat_base_amt_disc,
l_inv_paid_base_amt_disc,
l_inv_clr_base_amt_disc
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
l_log_msg := 'exception when select discount row amount values for payment'||SQLERRM;
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = 0,
APHD.Rounding_amt=-sign(l_inv_clr_base_amt_disc)*l_inv_clr_base_amt_disc
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
NVL(l_disc_clr_rounding_amt, 0),
APHD.Rounding_Amt=l_disc_clr_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'DISCOUNT';
l_log_msg := 'Updated discount rounding amount for clearing';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = 0,
APHD.Rounding_amt=-sign(l_inv_clr_base_amt)*l_inv_clr_base_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
NVL(l_err_clr_rounding_amt, 0),
APHD.Rounding_amt=l_err_clr_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'BANK ERROR';
l_log_msg := 'Updated error rounding amount';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = 0,
APHD.Rounding_amt=-sign(l_inv_clr_base_amt)*l_inv_clr_base_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
UPDATE AP_Payment_Hist_Dists APHD
SET APHD.Cleared_Base_Amount = APHD.Cleared_Base_Amount +
NVL(l_chrg_clr_rounding_amt, 0),
APHD.Rounding_amt=l_chrg_clr_rounding_amt
WHERE APHD.Payment_History_ID = l_pay_hist_id
AND APHD.Invoice_Distribution_ID = l_inv_dist_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Pay_Dist_Lookup_Code = 'BANK CHARGE';
l_log_msg := 'Updated charge rounding amount';
SELECT APHD.Payment_History_ID,
APHD.Invoice_Distribution_ID
INTO l_pay_hist_id, l_inv_dist_id
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Invoice_Distribution_ID =
(SELECT MAX(APHD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APHD1
WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD1.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND APHD1.Rounding_Amt is NULL
AND APHD1.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
AND ABS(APHD1.Amount) =
(SELECT MAX(ABS(APHD2.Amount))
FROM AP_Payment_Hist_Dists APHD2
WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD2.Invoice_Payment_ID =
p_inv_pay_rec.invoice_payment_id
AND APHD2.Rounding_Amt is NULL
AND APHD2.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id)))
AND APHD.Rounding_Amt is NULL
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id
AND Rownum = 1;
SELECT APHD.*
INTO l_max_pd_rec
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Invoice_Distribution_ID =
(SELECT MAX(APHD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APHD1
WHERE APHD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD1.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
AND APHD1.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id)
AND ABS(APHD1.Amount) =
(SELECT MAX(ABS(APHD2.Amount))
FROM AP_Payment_Hist_Dists APHD2
WHERE APHD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD2.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
AND APHD2.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id)))
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
AND Rownum = 1;
SELECT SUM(APHD.Paid_Base_Amount)
INTO l_sum_pay_paid_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Payment_History_All APH1
WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_pay_pmt_history_id
AND APH.Related_Event_ID = APH1.Accounting_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
AND APHD.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
SELECT SUM(APHD.Paid_Base_Amount),
SUM(APHD.Matured_Base_Amount)
INTO l_sum_mat_paid_base_amt,
l_sum_mat_mat_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Payment_History_All APH1
WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_mat_pmt_history_id
AND APH.Related_Event_ID = APH1.Accounting_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
AND APHD.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
SELECT SUM(APHD.Paid_Base_Amount),
SUM(APHD.Matured_Base_Amount)
INTO l_sum_clr_paid_base_amt,
l_sum_clr_mat_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Payment_History_All APH1
WHERE APH1.Payment_History_ID = ap_accounting_pay_pkg.g_clr_pmt_history_id
AND APH.Related_Event_ID = APH1.Accounting_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH')
AND APHD.Invoice_payment_id = P_Inv_Pay_Rec.Invoice_payment_id -- Bug 8722710
AND APHD.Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_rec.invoice_id);
this event and the prior event then we will insert the appropriate rounding
distribution */
l_diff_mat_paid_base_amt := l_sum_pay_paid_base_amt -
NVL(l_sum_mat_paid_base_amt, l_sum_pay_paid_base_amt);
l_log_msg := 'Inserting future payment rounding';
l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
l_log_msg := 'Inserting payment to clearing rounding';
l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
l_log_msg := 'Inserting maturity to clearing rounding';
l_log_msg := 'Calling procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';
SELECT APAD.*
INTO l_max_prepay_rec
FROM AP_Prepay_App_Dists APAD
WHERE Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND Invoice_Distribution_ID =
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Prepay_App_Distribution_ID =
p_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND ABS(APAD1.Amount) =
(SELECT MAX(ABS(APAD2.Amount))
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Prepay_App_Distribution_ID =
p_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id))
AND Rownum = 1;
SELECT SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amount,
'PREPAY APPL REC TAX', Base_Amount, 'PREPAY APPL NONREC TAX', Base_Amount, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_XRate,
'PREPAY APPL REC TAX', Base_Amt_At_Prepay_XRate,
'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_XRate, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_Pay_XRate,
'PREPAY APPL REC TAX', Base_Amt_At_Prepay_Pay_XRate,
'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_Pay_XRate, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'PREPAY APPL', Base_Amt_At_Prepay_Clr_XRate,
'PREPAY APPL REC TAX', Base_Amt_At_Prepay_Clr_XRate,
'PREPAY APPL NONREC TAX', Base_Amt_At_Prepay_Clr_XRate, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amount, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_XRate, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_Pay_XRate, 0)),
SUM(DECODE(Prepay_Dist_Lookup_Code, 'TAX DIFF', Base_Amt_At_Prepay_Clr_XRate, 0))
INTO l_sum_inv_rate_amt,
l_sum_prepay_rate_amt,
l_sum_prepay_pay_rate_amt,
l_sum_prepay_clr_rate_amt,
l_td_sum_inv_rate_amt,
l_td_sum_prepay_rate_amt,
l_td_sum_prepay_pay_rate_amt,
l_td_sum_prepay_clr_rate_amt
FROM AP_Prepay_App_Dists APAD
WHERE APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id;
/* If there is difference between the total and sum amounts then we will insert the
difference as the rounding amounts */
l_diff_inv_rate_amt := NVL(l_tot_inv_rate_amt,l_sum_inv_rate_amt) - l_sum_inv_rate_amt;
UPDATE AP_Prepay_App_Dists APPD
SET Base_Amount = Base_Amount + NVL(l_diff_inv_rate_amt,0),
Rounding_Amt = l_diff_inv_rate_amt,
Base_Amt_At_Prepay_XRate = Base_Amt_At_Prepay_XRate
+ NVL(l_diff_prepay_rate_amt,0),
Round_Amt_At_Prepay_XRate = l_diff_prepay_rate_amt,
Base_Amt_At_Prepay_Pay_XRate = Base_Amt_At_Prepay_Pay_XRate
+ NVL(l_diff_prepay_pay_rate_amt,0),
Round_Amt_At_Prepay_Pay_XRate = l_diff_prepay_pay_rate_amt,
Base_Amt_At_Prepay_Clr_XRate = Base_Amt_At_Prepay_Clr_XRate
+ NVL(l_diff_prepay_clr_rate_amt,0),
Round_Amt_At_Prepay_Clr_XRate = l_diff_prepay_clr_rate_amt
WHERE Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND Invoice_Distribution_ID = l_max_prepay_rec.invoice_distribution_id
AND Prepay_App_Distribution_ID = l_max_prepay_rec.prepay_app_distribution_id
-- AND Accounting_Event_ID = p_xla_event_rec.event_id
AND Prepay_Dist_Lookup_Code IN ('PREPAY APPL','PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX');
l_log_msg := 'Updated prepay appl rounding amount';
UPDATE AP_Prepay_App_Dists APPD
SET Base_Amount = Base_Amount + NVL(l_td_diff_inv_rate_amt,0),
Rounding_Amt = l_td_diff_inv_rate_amt,
Base_Amt_At_Prepay_XRate = Base_Amt_At_Prepay_XRate
+ NVL(l_td_diff_prepay_rate_amt,0),
Round_Amt_At_Prepay_XRate = l_td_diff_prepay_rate_amt,
Base_Amt_At_Prepay_Pay_XRate = Base_Amt_At_Prepay_Pay_XRate
+ NVL(l_td_diff_prepay_pay_rate_amt,0),
Round_Amt_At_Prepay_Pay_XRate = l_td_diff_prepay_pay_rate_amt,
Base_Amt_At_Prepay_Clr_XRate = Base_Amt_At_Prepay_Clr_XRate
+ NVL(l_td_diff_prepay_clr_rate_amt,0),
Round_Amt_At_Prepay_Clr_XRate = l_td_diff_prepay_clr_rate_amt
WHERE Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND Invoice_Distribution_ID = l_max_prepay_rec.invoice_distribution_id
AND Prepay_App_Distribution_ID = l_max_prepay_rec.prepay_app_distribution_id
-- AND Accounting_Event_ID = p_xla_event_rec.event_id
AND Prepay_Dist_Lookup_Code IN ('TAX DIFF');
SELECT NVL(ASP.Invrate_for_prepay_tax, 'N')
INTO l_invrate_for_prepay
FROM ap_system_parameters_all ASP,
ap_prepay_history_all APH
WHERE ASP.Org_id = APH.Org_id
AND APH.Prepay_history_id = P_Prepay_Hist_Rec.Prepay_History_ID;
SELECT APAD.*
INTO l_max_prepay_rec
FROM AP_Prepay_App_Dists APAD
WHERE Invoice_Distribution_ID IN
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND ABS(APAD1.Amount) =
(SELECT MAX(ABS(APAD2.Amount))
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id))
AND APAD.Prepay_App_Distribution_ID = p_prepay_dist_rec.invoice_distribution_id
AND APAD.Prepay_History_ID = p_prepay_hist_rec.prepay_history_id
AND Rownum = 1;
SELECT SUM(APHD.Paid_Base_Amount)
INTO l_sum_pay_paid_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id)
AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL PAYMENT ROUNDING',
'FINAL PAYMENT ROUNDING');
SELECT SUM(APHD.Cleared_Base_Amount)
INTO l_sum_pay_clrd_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APH.Related_Event_ID = p_clr_hist_rec.related_event_id
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND Invoice_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id)
AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL CLEARING ROUNDING',
'FINAL PAYMENT ROUNDING');
SELECT SUM(APHD.Paid_Base_Amount)
INTO l_sum_pay_paid_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APH.Related_Event_ID = p_pay_hist_rec.Related_Event_ID
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id
AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL PAYMENT ROUNDING',
'FINAL PAYMENT ROUNDING');
SELECT SUM(APHD.Cleared_Base_Amount)
INTO l_sum_pay_clrd_base_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APH.Related_Event_ID = p_clr_hist_rec.related_event_id
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id
AND Pay_Dist_Lookup_Code IN ('CASH', 'DISCOUNT', 'AWT', 'TOTAL CLEARING ROUNDING',
'FINAL PAYMENT ROUNDING');
SELECT SUM(Base_Amt_At_Prepay_XRate),
SUM(Base_Amt_At_Prepay_Pay_XRate),
SUM(Base_Amt_At_Prepay_Clr_XRate)
INTO l_sum_prepay_rate_amt,
l_sum_prepay_pay_rate_amt,
l_sum_prepay_clr_rate_amt
FROM AP_Prepay_App_Dists
WHERE Prepay_App_Distribution_ID IN
(SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID
WHERE AID.Prepay_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id);
SELECT AID.Base_Amount +
NVL((SELECT SUM(NVL(AID_erv.Base_Amount, 0))
FROM AP_Invoice_Distributions_All AID_erv
WHERE AID_erv.Invoice_id = AID.Invoice_id
AND AID_erv.line_type_lookup_code IN ('ERV', 'TERV')
AND AID_erv.related_id = AID.invoice_distribution_id), 0)
INTO l_sum_pay_base_amt
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_Distribution_ID = p_prepay_dist_rec.prepay_distribution_id;
l_log_msg := 'Inserting final appl rounding dist';
l_log_msg := 'Calling procedure Prepay_Dist_Insert';
AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert
(l_pad_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Prepay_Dist_Insert executed';
SELECT count(*) into l_do_cash_rounding
FROM ap_invoice_payments_all aip
WHERE check_id = P_XLA_Event_Rec.source_id_int_1
AND NOT EXISTS (SELECT 1
FROM ap_payment_hist_dists aphd
WHERE aphd.invoice_payment_id = aip.invoice_payment_id);
SELECT decode(aps.recon_accounting_flag, 'Y',coalesce(ac.cleared_base_amount,ac.base_amount, 0),nvl(base_amount,0)),
decode(aps.recon_accounting_flag, 'Y', 'CLEARING',
decode(ac.future_pay_due_date, NULL, 'CREATED', 'MATURITY')) type,
ac.amount
INTO l_ac_base_amount, l_transaction_type, l_ac_amount
FROM ap_checks_all ac,
ap_system_parameters_all aps,
ap_invoice_payments_all aip
WHERE ac.org_id = aps.org_id
AND ac.check_id = P_XLA_Event_Rec.source_id_int_1
AND aip.check_id = ac.check_id
AND ac.currency_code <> aps.base_currency_code
AND aip.reversal_inv_pmt_id IS NULL
GROUP BY ac.check_id, ac.amount, ac.base_amount , ac.future_pay_due_date, aps.recon_accounting_flag,ac.cleared_base_amount
HAVING ABS(nvl(ac.base_amount, 0) -SUM(nvl(aip.payment_base_amount, 0))) > 0
AND nvl(ac.base_amount, 0) <> SUM(nvl(aip.payment_base_amount, 0));
SELECT SUM(DECODE(l_transaction_type, 'CLEARING' , nvl(cleared_base_amount,0),
'MATURITY' , nvl(matured_base_amount,0),nvl(paid_base_amount,0)))
INTO l_sum_cash_amt
FROM ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aph.payment_history_id = aphd.payment_history_id
AND aph.check_id = P_XLA_Event_Rec.source_id_int_1
AND aph.accounting_event_id = P_XLA_Event_Rec.event_id
AND aph.posted_flag <> 'Y'
AND aphd.pay_dist_lookup_code in ( 'CASH'
, 'FINAL CASH ROUNDING'
, 'BANK CHARGE'
, 'BANK ERROR') -- Bug 13783723
AND aph.transaction_type = DECODE(l_transaction_type, 'CLEARING' , 'PAYMENT CLEARING',
'MATURITY' , 'PAYMENT MATURITY','PAYMENT CREATED');
SELECT APH.Payment_History_ID,
APH.Pmt_Currency_Code,
APH.Bank_Currency_Code,
APH.Bank_To_Base_XRate_Type,
APH.Bank_To_Base_XRate_Date,
NVL(APH.Bank_To_Base_XRate,1)
INTO l_pay_hist_rec.Payment_History_ID,
l_pay_hist_rec.Pmt_Currency_Code,
l_pay_hist_rec.Bank_Currency_Code,
l_pay_hist_rec.Bank_To_Base_XRate_Type,
l_pay_hist_rec.Bank_To_Base_XRate_Date,
l_pay_hist_rec.Bank_To_Base_XRate
FROM AP_Payment_History_All APH
WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
SELECT APHD.*
INTO l_max_pd_rec
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APHD.pay_dist_lookup_code = 'CASH'
AND Rownum = 1;
l_log_msg := 'Calling proc AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert';
AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Proc AP_Acctg_Pay_Dist_Pkg.Pay_Dist_Insert executed';