[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 MAX(accounting_event_id)
INTO l_acctg_event_id
FROM ap_invoice_payments_all
WHERE invoice_id = p_inv_rec.invoice_id
AND NVL(accrual_posted_flag, 'N') = 'N';
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'))
AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
AND AID.Match_Status_Flag IN ('T','A'))
OR
((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
AND AID.Match_Status_Flag = 'A')));
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='INVOICE ADJUSTED'
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 APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
'PAYMENT CLEARING ADJUSTED');
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 APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
'PAYMENT MATURITY ADJUSTED');
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 APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED',
'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED');
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;
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
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;
'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;
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 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 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.*
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_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_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 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_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_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_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 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(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
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';