[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Gain_Loss_Ind
(P_XLA_Event_Rec IN ap_accounting_pay_pkg.r_xla_event_info
,P_Pay_Hist_Rec IN ap_accounting_pay_pkg.r_pay_hist_info
,P_Calling_Sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Gain_Loss_Ind';
l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Update_Gain_Loss_Ind<- ' ||
p_calling_sequence;
UPDATE AP_Payment_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(SIGN(SUM(APHD.Invoice_Dist_Base_Amount - APHD.Paid_Base_Amount)),
1, 'G', -1, 'L', NULL)
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
--AND NVL(APHD.Reversal_Flag,'N') <> 'Y' --added for bug 7244022
--above condition commented for bug 7445576
)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
UPDATE AP_Payment_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(SIGN(SUM(APHD.Invoice_Dist_Base_Amount - APHD.Paid_Base_Amount)),
1, 'G', -1, 'L', NULL)
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id
AND NVL(APHD.Reversal_Flag,'N') <> 'Y'
)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
UPDATE AP_Payment_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(SIGN(SUM(APHD.Paid_Base_Amount - APHD.Matured_Base_Amount)),
1, 'G', -1, 'L', NULL)
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
SELECT count(*)
INTO l_pay_mat_count
FROM AP_Payment_History_All APH,
AP_Payment_History_All APH1
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id
AND APH.Check_ID = APH1.Check_ID
AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT CLEARING ADJUSTED')
AND APH1.Transaction_Type = 'PAYMENT MATURITY';
UPDATE AP_Payment_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(SIGN(SUM(APHD.Matured_Base_Amount - APHD.Cleared_Base_Amount)),
1, 'G', -1, 'L', NULL)
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
SELECT ASP.when_to_account_gain_loss
INTO l_when_to_account_gain_loss
FROM ap_system_parameters_all ASP,
AP_Payment_History_All APH
WHERE APH.org_id = ASP.org_id
AND APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
l_log_msg := 'Update Gain/Loss between invoice and clearing for gain/loss at clear only';
UPDATE AP_Payment_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(SIGN(SUM(APHD.Invoice_Dist_Base_Amount - APHD.Cleared_Base_Amount)),
1, 'G', -1, 'L', NULL)
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
l_log_msg := 'Update Gain/Loss between payment and clearing for gain/loss at always';
UPDATE AP_Payment_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(SIGN(SUM(APHD.Paid_Base_Amount - APHD.Cleared_Base_Amount)),
1, 'G', -1, 'L', NULL)
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Payment_History_ID = APH.Payment_History_ID
AND APHD.Accounting_Event_ID = p_xla_event_rec.event_id)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
UPDATE AP_Payment_History_All APH
SET APH.Gain_Loss_Indicator =
(SELECT Gain_Loss_Indicator
FROM AP_Payment_History_All APH1
WHERE APH1.Payment_History_ID = APH.Rev_Pmt_Hist_ID)
WHERE APH.Payment_History_Id = p_pay_hist_rec.payment_history_id;
END Update_Gain_Loss_Ind;
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') --7630203
AND aid.org_id = asp.org_id
AND automatic_offsets_flag = 'N'
AND aid.historical_flag = 'Y';
SELECT APH.Payment_History_ID,
APH.Pmt_To_Base_XRate_Type,
APH.Pmt_To_Base_XRate_Date,
APH.Pmt_To_Base_XRate
INTO l_pay_history_id,
ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
ap_accounting_pay_pkg.g_pmt_to_base_xrate
FROM AP_Payment_History_All APH
WHERE APH.Payment_History_ID =
(SELECT MAX(APH1.Payment_History_ID)
FROM AP_Payment_History_All APH1
WHERE APH1.Check_ID = p_xla_event_rec.source_id_int_1
AND APH1.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED'));
SELECT APH.Payment_History_ID,
APH.Pmt_To_Base_XRate_Type,
APH.Pmt_To_Base_XRate_Date,
APH.Pmt_To_Base_XRate
INTO l_pay_history_id,
ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
ap_accounting_pay_pkg.g_pmt_to_base_xrate
FROM AP_Payment_History_All APH
WHERE APH.Payment_History_ID =
(SELECT MAX(APH1.Payment_History_ID)
FROM AP_Payment_History_All APH1
WHERE APH1.Check_ID = p_xla_event_rec.source_id_int_1
AND APH1.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED'));
SELECT MAX(APH.Payment_History_ID)
INTO l_mat_history_id
FROM AP_Payment_History_All APH
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
AND APH.Transaction_Type IN ('PAYMENT MATURITY');
SELECT APH.Pmt_To_Base_XRate_Type,
APH.Pmt_To_Base_XRate_Date,
APH.Pmt_To_Base_XRate
INTO ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
ap_accounting_pay_pkg.g_mat_to_base_xrate
FROM AP_Payment_History_All APH
WHERE APH.Payment_History_ID = l_mat_history_id;
SELECT count(*)
INTO l_upg_pmt_hist
FROM AP_Payment_History_All
WHERE Check_ID = p_xla_event_rec.source_id_int_1
AND Historical_Flag = 'Y'
AND Posted_Flag = 'Y';
SELECT count(*)
INTO l_upg_inv_pmts
FROM AP_Invoice_Payments_All AIP
WHERE Invoice_ID = l_inv_pay_rec.invoice_id
AND EXISTS (SELECT 'Upg Payment'
FROM AP_Payment_History_All APH
WHERE APH.Check_ID = AIP.Check_ID
AND APH.Historical_Flag = 'Y'
AND APH.Posted_Flag = 'Y'
AND Rownum = 1);
SELECT SUM(decode(aid.prepay_tax_parent_id, NULL, nvl(aid.amount, 0), 0)),
SUM(decode(aid.line_type_lookup_code, 'AWT', 0, nvl(aid.amount, 0)))
INTO G_Total_Dist_Amount , G_Proration_Divisor
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_inv_pay_rec.invoice_id
AND aid.line_type_lookup_code <> 'PREPAY'
AND aid.prepay_distribution_id IS NULL
AND (aid.awt_invoice_payment_id IS NULL OR
aid.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) -- bug fix: 6725866
AND NOT EXISTS
(SELECT 1 FROM xla_events
WHERE event_id = aid.accounting_event_id
AND application_id = 200
AND event_type_code IN('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
'CREDIT MEMO CANCELLED', 'DEBIT MEMO CANCELLED'));
l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
Update_Gain_Loss_Ind
(p_xla_event_rec,
l_pay_hist_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
SELECT SUM(decode(aid.prepay_tax_parent_id, NULL, nvl(aid.amount, 0), 0)),
SUM(decode(aid.line_type_lookup_code, 'AWT', 0, nvl(aid.amount, 0)))
INTO G_Total_Dist_Amount , G_Proration_Divisor
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_inv_pay_rec.invoice_id
AND aid.line_type_lookup_code <> 'PREPAY'
AND aid.prepay_distribution_id IS NULL
AND (aid.awt_invoice_payment_id IS NULL OR
aid.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) -- bug fix: 6725866
AND NOT EXISTS
(SELECT 1 FROM xla_events
WHERE event_id = aid.accounting_event_id
AND application_id = 200
AND event_type_code IN('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
'CREDIT MEMO CANCELLED', 'DEBIT MEMO CANCELLED'));
l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
Update_Gain_Loss_Ind
(p_xla_event_rec,
l_pay_hist_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
Update_Gain_Loss_Ind
(p_xla_event_rec,
l_pay_hist_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
SELECT Distinct AID.Invoice_Distribution_ID,
AID.Line_Type_Lookup_Code,
AID.Amount,
AID.Base_Amount,
AID.PO_Distribution_ID,
AID.RCV_Transaction_ID,
NVL(AID.Reversal_Flag,'N'),
AID.Parent_Reversal_ID,
AID.AWT_Related_ID,
AID.AWT_Invoice_Payment_ID,
AID.Quantity_Variance,
AID.Base_Quantity_Variance,
AID.Amount_Variance,
AID.Base_Amount_Variance,
AID.historical_flag, -- bug fix 6674279
AID.accounting_event_id -- bug fix 6674279
FROM AP_Invoice_Distributions_All AID,
AP_Payment_History_All APH,
AP_Payment_Hist_Dists APHD
WHERE AID.Invoice_ID = P_Invoice_ID
AND NVL(AID.Reversal_Flag,'N') <> 'Y'
AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Related_Event_ID = P_Related_Event_ID
AND NVL(AID.Accounting_Event_ID,-99) <> P_Event_ID
AND APHD.Pay_Dist_Lookup_Code IN ('CASH', 'AWT');
SELECT AIP.Invoice_ID,
AIP.Invoice_Payment_ID,
AIP.Amount,
AIP.Discount_Taken,
AIP.Payment_Base_Amount,
AIP.Invoice_Base_Amount,
AIP.Exchange_Rate_Type,
AIP.Exchange_Date,
AIP.Exchange_Rate,
NVL(AIP.Reversal_Flag,'N'),
AIP.Reversal_Inv_Pmt_ID
FROM AP_Invoice_Payments_All AIP
WHERE AIP.Check_ID = P_Check_ID
AND AIP.Invoice_ID = P_Invoice_ID;
SELECT MAX(APH.Payment_History_ID)
INTO l_pay_history_id
FROM AP_Payment_History_All APH
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
AND APH.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED');
SELECT APH.Pmt_To_Base_XRate_Type,
APH.Pmt_To_Base_XRate_Date,
APH.Pmt_To_Base_XRate
INTO ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
ap_accounting_pay_pkg.g_pmt_to_base_xrate
FROM AP_Payment_History_All APH
WHERE APH.Payment_History_ID = l_pay_history_id;
SELECT MAX(APH.Payment_History_ID)
INTO l_pay_history_id
FROM AP_Payment_History_All APH
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
AND APH.Transaction_Type IN ('PAYMENT CREATED', 'REFUND RECORDED');
SELECT APH.Pmt_To_Base_XRate_Type,
APH.Pmt_To_Base_XRate_Date,
APH.Pmt_To_Base_XRate
INTO ap_accounting_pay_pkg.g_pmt_to_base_xrate_type,
ap_accounting_pay_pkg.g_pmt_to_base_xrate_date,
ap_accounting_pay_pkg.g_pmt_to_base_xrate
FROM AP_Payment_History_All APH
WHERE APH.Payment_History_ID = l_pay_history_id;
SELECT MAX(APH.Payment_History_ID)
INTO l_mat_history_id
FROM AP_Payment_History_All APH
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
AND APH.Transaction_Type IN ('PAYMENT MATURITY');
SELECT APH.Pmt_To_Base_XRate_Type,
APH.Pmt_To_Base_XRate_Date,
APH.Pmt_To_Base_XRate
INTO ap_accounting_pay_pkg.g_mat_to_base_xrate_type,
ap_accounting_pay_pkg.g_mat_to_base_xrate_date,
ap_accounting_pay_pkg.g_mat_to_base_xrate
FROM AP_Payment_History_All APH
WHERE APH.Payment_History_ID = l_mat_history_id;
SELECT AID.Invoice_ID
INTO l_invoice_id
FROM AP_Invoice_Distributions_All AID
WHERE AID.Accounting_Event_ID = l_pay_hist_rec.invoice_adjustment_event_id
AND Rownum = 1;
SELECT SUM(decode(aid.prepay_tax_parent_id, NULL, nvl(aid.amount, 0), 0)),
SUM(decode(aid.line_type_lookup_code, 'AWT', 0, nvl(aid.amount, 0)))
INTO G_Total_Dist_Amount , G_Proration_Divisor
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_inv_pay_rec.invoice_id
AND aid.line_type_lookup_code <> 'PREPAY'
AND aid.prepay_distribution_id IS NULL
AND (aid.awt_invoice_payment_id IS NULL OR
aid.awt_invoice_payment_id = l_inv_pay_rec.invoice_payment_id) -- bug fix: 6725866
AND NOT EXISTS
(SELECT 1 FROM xla_events
WHERE event_id = aid.accounting_event_id
AND application_id = 200
AND event_type_code IN('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
'CREDIT MEMO CANCELLED', 'DEBIT MEMO CANCELLED'));
SELECT count(*)
INTO l_pay_dist_cnt
FROM ap_payment_hist_dists
WHERE invoice_distribution_id = l_inv_dist_rec.parent_reversal_id;
SELECT SUM(AID.Amount)
INTO l_inv_adj_amount
FROM AP_Invoice_Distributions_All AID
WHERE AID.Accounting_Event_ID = l_pay_hist_rec.invoice_adjustment_event_id;
Due to commenting the below code, it is inserting the data
in payment hist dist only for the adjusted distributions and not for all
the distributions. But it should do for all the distributions */
--/* commented the code for bug 7147610
-- For the Payment Adjustments we are populating the
-- Payment Hists Dists in the cursor loop Invoice_Dists
-- This Inv_Adj_Dists is not required.
IF l_inv_adj_amount <> 0 THEN
OPEN Inv_Adj_Dists(l_pay_hist_rec.invoice_adjustment_event_id,
l_inv_rec.invoice_id,
l_pay_hist_rec.related_event_id);
SELECT max(aph2.payment_history_id) into l_do_round
FROM ap_payment_history_all aph1,
ap_payment_history_all aph2
WHERE aph1.payment_history_id = l_pay_hist_rec.payment_history_id
AND aph2.check_id = aph1.check_id
AND aph2.posted_flag <> 'Y'
AND aph1.posted_flag <> 'Y'
AND aph1.transaction_type = aph2.transaction_type
AND l_invoice_id = (SELECT invoice_id
FROM ap_invoice_distributions_all d
WHERE d.accounting_event_id = aph2.invoice_adjustment_event_id
AND rownum = 1);
SELECT SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'CASH', APHD.Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK ERROR', APHD.Amount, 0)),
SUM(DECODE(APHD.Pay_Dist_Lookup_Code, 'BANK CHARGE', APHD.Amount, 0))
INTO l_sum_paid_amount,
l_sum_disc_amount,
l_sum_error_amount,
l_sum_charge_amount
FROM AP_Payment_Hist_Dists APHD,
AP_Invoice_Distributions_All AID,
AP_Payment_History_All APH
WHERE APH.Related_Event_ID = l_pay_hist_rec.related_event_id
AND APHD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AID.Invoice_ID = l_invoice_id;
UPDATE AP_Payment_Hist_Dists APD
SET APD.Amount = APD.Amount - NVL(l_sum_paid_amount,0) + l_inv_pay_rec.amount
WHERE APD.Invoice_Distribution_ID =
(SELECT MAX(APD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APD1
WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD1.Pay_Dist_Lookup_Code = 'CASH'
AND ABS(APD1.Amount) =
(SELECT MAX(APD2.Amount)
FROM AP_Payment_Hist_Dists APD2
WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD2.Pay_Dist_Lookup_Code = 'CASH'))
AND APD.Pay_Dist_Lookup_Code = 'CASH'
AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
UPDATE AP_Payment_Hist_Dists APD
SET APD.Amount = APD.Amount - NVL(l_sum_disc_amount,0)
+ l_inv_pay_rec.discount_taken
WHERE APD.Invoice_Distribution_ID =
(SELECT MAX(APD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APD1
WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD1.Pay_Dist_Lookup_Code = 'DISCOUNT'
AND ABS(APD1.Amount) =
(SELECT MAX(APD2.Amount)
FROM AP_Payment_Hist_Dists APD2
WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD2.Pay_Dist_Lookup_Code = 'DISCOUNT'))
AND APD.Pay_Dist_Lookup_Code = 'DISCOUNT'
AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
UPDATE AP_Payment_Hist_Dists APD
SET APD.Amount = APD.Amount - NVL(l_sum_error_amount,0)
+ l_pay_hist_rec.errors_bank_amount
WHERE APD.Invoice_Distribution_ID =
(SELECT MAX(APD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APD1
WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD1.Pay_Dist_Lookup_Code = 'BANK ERROR'
AND ABS(APD1.Amount) =
(SELECT MAX(APD2.Amount)
FROM AP_Payment_Hist_Dists APD2
WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD2.Pay_Dist_Lookup_Code = 'BANK ERROR'))
AND APD.Pay_Dist_Lookup_Code = 'BANK ERROR'
AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
UPDATE AP_Payment_Hist_Dists APD
SET APD.Amount = APD.Amount - NVL(l_sum_charge_amount,0)
+ l_pay_hist_rec.charges_bank_amount
WHERE APD.Invoice_Distribution_ID =
(SELECT MAX(APD1.Invoice_Distribution_ID)
FROM AP_Payment_Hist_Dists APD1
WHERE APD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD1.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD1.Pay_Dist_Lookup_Code = 'BANK CHARGE'
AND ABS(APD1.Amount) =
(SELECT MAX(APD2.Amount)
FROM AP_Payment_Hist_Dists APD2
WHERE APD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APD2.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD2.Pay_Dist_Lookup_Code = 'BANK CHARGE'))
AND APD.Pay_Dist_Lookup_Code = 'BANK CHARGE'
AND APD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APD.Payment_History_ID = l_pay_hist_rec.payment_history_id
AND APD.Accounting_Event_ID = p_xla_event_rec.event_id;
l_log_msg := 'Calling procedure Update_Gain_Loss_Ind for payments';
Update_Gain_Loss_Ind
(p_xla_event_rec,
l_pay_hist_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Update_Gain_Loss_Ind executed';
SELECT SUM(NVL(AID.Amount,0))
INTO l_total_dist_amount
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_pay_rec.invoice_id
AND AID.Line_Type_Lookup_Code <> 'PREPAY'
AND AID.Prepay_Distribution_ID IS NULL
AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
AND (AID.AWT_Invoice_Payment_ID IS NULL
OR AID.AWT_Invoice_Payment_ID = p_inv_pay_rec.invoice_payment_id)
-- bug fix: 6725866
AND NOT EXISTS (SELECT 1
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND application_id = 200 -- bug7281412
AND event_type_code IN ('INVOICE CANCELLED',
'PREPAYMENT CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
SELECT SUM(AID.amount)
INTO l_proration_divisor
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_id = p_inv_rec.invoice_id
AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT')
AND AID.Prepay_Distribution_ID IS NULL
AND NOT EXISTS (SELECT 1
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND application_id = 200 --bug 7281412
AND event_type_code IN ('INVOICE CANCELLED',
'PREPAYMENT CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
l_log_msg := 'Now calling AP_Accounting_Pay_Pkg.Get_Base_Amount before insert';
l_log_msg := 'Calling procedure Pay_Dist_Insert';
Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Pay_Dist_Insert executed';
SELECT GL_Currency_API.Convert_Amount(
p_inv_rec.invoice_currency_code,
p_inv_rec.payment_currency_code,
p_inv_rec.payment_cross_rate_date,
'EMU FIXED',
SUM(NVL(AID.Amount,0)))
INTO l_invoice_amount
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_inv_pay_rec.Invoice_ID
AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'AWT')
AND AID.Prepay_Tax_Parent_ID IS NULL -- For tax dists created in R11.5
AND AID.Prepay_Distribution_ID IS NULL
AND ( l_exclude_tax_from_disc = 'Y' and
AID.Line_Type_Lookup_Code NOT IN ('REC_TAX', 'NONREC_TAX') or
nvl(l_exclude_tax_from_disc, 'N') = 'N' )
AND AID.Line_Type_Lookup_Code <>
DECODE(l_exclude_frt_from_disc, 'Y', 'FREIGHT', 'DUMMY')
GROUP BY AID.Invoice_ID;
l_log_msg := 'Calling procedure Pay_Dist_Insert';
Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Pay_Dist_Insert executed';
SELECT Currency_Conversion_Rate
INTO l_po_exchange_rate
FROM rcv_transactions
WHERE transaction_id = p_inv_dist_rec.rcv_transaction_id;
SELECT Rate
INTO l_po_exchange_rate
FROM PO_Distributions_All
WHERE PO_Distribution_ID = p_inv_dist_rec.PO_Distribution_ID;
l_log_msg := 'Calling procedure Pay_Dist_Insert';
l_log_msg := 'before callining erv/terv insert -' ||
'l_pd_rec.invoice_dist_base_amount = ' ||
l_pd_rec.invoice_dist_base_amount ||
'l_pd_rec.paid_base_amount or cleared_base_amount =' ||
l_erv_amount;
Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Pay_Dist_Insert executed';
SELECT Accounting_Event_ID,
Invoice_Distribution_ID,
Amount,
Payment_History_ID,
Invoice_Payment_ID
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Accounting_Event_ID = P_Event_ID
AND APHD.Pay_Dist_Lookup_Code = 'CASH'
ORDER BY Amount;
SELECT SUM(Amount)
INTO l_total_pay_amt
FROM AP_Payment_Hist_Dists
WHERE Payment_History_ID = p_pay_hist_rec.payment_history_id
AND Pay_Dist_Lookup_Code = 'CASH';
l_log_msg := 'Calling procedure Pay_Dist_Insert';
Pay_Dist_Insert
(l_pd_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Pay_Dist_Insert executed';
INSERT INTO ap_payment_hist_dists
(Payment_Hist_Dist_ID,
Accounting_Event_ID,
Amount,
Pay_Dist_Lookup_Code,
Payment_History_ID,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Bank_Curr_Amount,
Cleared_Base_Amount,
Invoice_Dist_Amount,
Invoice_Dist_Base_Amount,
Invoice_Adjustment_Event_ID,
Matured_Base_Amount,
Paid_Base_Amount,
Reversal_Flag,
Reversed_Pay_Hist_Dist_ID,
AWT_Related_ID,
PA_Addition_Flag,
Quantity_Variance,
Invoice_Base_Qty_Variance,
Amount_Variance,
Invoice_Base_Amt_Variance,
Created_By,
Creation_Date,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Program_Login_ID,
Program_Update_Date,
Request_ID
)
SELECT AP_Payment_Hist_Dists_S.nextval,
p_xla_event_rec.event_id,
-1 * APHD.Amount,
APHD.Pay_Dist_Lookup_Code,
p_pay_hist_rec.Payment_History_ID,
APHD.Invoice_Distribution_ID,
p_inv_pay_rec.invoice_payment_id,
-1 * APHD.Bank_Curr_Amount,
-1 * APHD.Cleared_Base_Amount,
-1 * APHD.Invoice_Dist_Amount,
-1 * APHD.Invoice_Dist_Base_Amount,
APHD.Invoice_Adjustment_Event_ID,
-1 * APHD.Matured_Base_Amount,
-1 * APHD.Paid_Base_Amount,
'Y',
APHD.Payment_Hist_Dist_ID,
APHD.AWT_Related_ID,
'N',
APHD.Quantity_Variance,
APHD.Invoice_Base_Qty_Variance,
APHD.Amount_Variance,
APHD.Invoice_Base_Amt_Variance,
FND_GLOBAL.User_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
FND_GLOBAL.Prog_Appl_ID,
FND_GLOBAL.Conc_Program_ID,
NULL,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
FROM AP_Payment_Hist_Dists APHD,
AP_Invoice_Payments_All AIP,
AP_Invoice_Distributions_All AID
WHERE AIP.Invoice_Payment_ID = p_reversal_inv_pmt_id
AND AIP.Accounting_Event_ID = APHD.Accounting_Event_ID
AND AIP.Invoice_ID = AID.Invoice_ID
AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID;
INSERT INTO ap_payment_hist_dists
(Payment_Hist_Dist_ID,
Accounting_Event_ID,
Amount,
Pay_Dist_Lookup_Code,
Payment_History_ID,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Bank_Curr_Amount,
Cleared_Base_Amount,
Invoice_Dist_Amount,
Invoice_Dist_Base_Amount,
Invoice_Adjustment_Event_ID,
Matured_Base_Amount,
Paid_Base_Amount,
Reversal_Flag,
Reversed_Pay_Hist_Dist_ID,
AWT_Related_ID,
PA_Addition_Flag,
Quantity_Variance,
Invoice_Base_Qty_Variance,
Amount_Variance,
Invoice_Base_Amt_Variance,
Created_By,
Creation_Date,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Program_Login_ID,
Program_Update_Date,
Request_ID
)
SELECT AP_Payment_Hist_Dists_S.nextval,
p_xla_event_rec.event_id,
-1 * APHD.Amount,
APHD.Pay_Dist_Lookup_Code,
p_pay_hist_rec.Payment_History_ID,
APHD.Invoice_Distribution_ID,
AIP.Invoice_Payment_ID,
-1 * APHD.Bank_Curr_Amount,
-1 * APHD.Cleared_Base_Amount,
-1 * APHD.Invoice_Dist_Amount,
-1 * APHD.Invoice_Dist_Base_Amount,
APHD.Invoice_Adjustment_Event_ID,
-1 * APHD.Matured_Base_Amount,
-1 * APHD.Paid_Base_Amount,
'Y',
APHD.Payment_Hist_Dist_ID,
APHD.AWT_Related_ID,
'N',
APHD.Quantity_Variance,
APHD.Invoice_Base_Qty_Variance,
APHD.Amount_Variance,
APHD.Invoice_Base_Amt_Variance,
FND_GLOBAL.User_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
FND_GLOBAL.Prog_Appl_ID,
FND_GLOBAL.Conc_Program_ID,
NULL,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH,
AP_Invoice_Payments_All AIP,
AP_Invoice_Distributions_All AID -- 6804379
WHERE nvl(APH.Related_Event_ID, APH.Accounting_Event_ID) = p_related_event_id
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND NVL(APHD.Reversal_Flag,'N') <> 'Y'
AND AID.Invoice_Distribution_ID = APHD.Invoice_Distribution_ID
AND AIP.Reversal_inv_pmt_id = APHD.invoice_payment_id --Bug 6881085
AND AIP.Accounting_Event_ID = p_xla_event_rec.event_id
AND AIP.Check_ID = APH.Check_ID -- Bug 6856694
AND AIP.Invoice_ID = AID.Invoice_ID
AND NOT EXISTS (SELECT 'Reversal Payment Dists'
FROM AP_Payment_Hist_Dists APHD1
WHERE APHD1.Reversed_Pay_Hist_Dist_ID
= APHD.Payment_Hist_Dist_ID
-- Bug 6856694
AND APHD1.Invoice_Distribution_ID
= APHD.Invoice_Distribution_ID);
INSERT INTO ap_payment_hist_dists
(Payment_Hist_Dist_ID,
Accounting_Event_ID,
Amount,
Pay_Dist_Lookup_Code,
Payment_History_ID,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Bank_Curr_Amount,
Cleared_Base_Amount,
Invoice_Dist_Amount,
Invoice_Dist_Base_Amount,
Invoice_Adjustment_Event_ID,
Matured_Base_Amount,
Paid_Base_Amount,
Reversal_Flag,
Reversed_Pay_Hist_Dist_ID,
AWT_Related_ID,
PA_Addition_Flag,
Quantity_Variance,
Invoice_Base_Qty_Variance,
Amount_Variance,
Invoice_Base_Amt_Variance,
Created_By,
Creation_Date,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Program_Login_ID,
Program_Update_Date,
Request_ID
)
SELECT AP_Payment_Hist_Dists_S.nextval,
p_xla_event_rec.event_id,
-1 * APHD.Amount,
APHD.Pay_Dist_Lookup_Code,
p_pay_hist_rec.Payment_History_ID,
APHD.Invoice_Distribution_ID,
APHD.Invoice_Payment_ID,
-1 * APHD.Bank_Curr_Amount,
-1 * APHD.Cleared_Base_Amount,
-1 * APHD.Invoice_Dist_Amount,
-1 * APHD.Invoice_Dist_Base_Amount,
APHD.Invoice_Adjustment_Event_ID,
-1 * APHD.Matured_Base_Amount,
-1 * APHD.Paid_Base_Amount,
'Y',
APHD.Payment_Hist_Dist_ID,
APHD.AWT_Related_ID,
'N',
APHD.Quantity_Variance,
APHD.Invoice_Base_Qty_Variance,
APHD.Amount_Variance,
APHD.Invoice_Base_Amt_Variance,
FND_GLOBAL.User_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
FND_GLOBAL.Prog_Appl_ID,
FND_GLOBAL.Conc_Program_ID,
NULL,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH -- 6804379
WHERE APH.Check_ID = p_xla_event_rec.Source_ID_Int_1 -- Bug 6856694
AND NVL(APH.Related_Event_ID, APH.Accounting_Event_ID) = p_related_event_id
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND NVL(APHD.Reversal_Flag,'N') <> 'Y'
AND NOT EXISTS (SELECT 'Reversal Payment Dists'
FROM AP_Payment_Hist_Dists APHD1
WHERE APHD1.Reversed_Pay_Hist_Dist_ID
= APHD.Payment_Hist_Dist_ID
-- Bug 6856694
AND APHD1.Invoice_Distribution_ID
= APHD.Invoice_Distribution_ID);
INSERT INTO ap_payment_hist_dists
(Payment_Hist_Dist_ID,
Accounting_Event_ID,
Amount,
Pay_Dist_Lookup_Code,
Payment_History_ID,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Bank_Curr_Amount,
Cleared_Base_Amount,
Invoice_Dist_Amount,
Invoice_Dist_Base_Amount,
Invoice_Adjustment_Event_ID,
Matured_Base_Amount,
Paid_Base_Amount,
Reversal_Flag,
Reversed_Pay_Hist_Dist_ID,
AWT_Related_ID,
PA_Addition_Flag,
Quantity_Variance,
Invoice_Base_Qty_Variance,
Amount_Variance,
Invoice_Base_Amt_Variance,
Created_By,
Creation_Date,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Program_Login_ID,
Program_Update_Date,
Request_ID
)
SELECT AP_Payment_Hist_Dists_S.nextval,
p_xla_event_rec.event_id,
-1 * APHD.Amount,
APHD.Pay_Dist_Lookup_Code,
p_pay_hist_rec.Payment_History_ID,
p_inv_dist_rec.Invoice_Distribution_ID, -- Bug 6887295
APHD.Invoice_Payment_ID,
-1 * APHD.Bank_Curr_Amount,
-1 * APHD.Cleared_Base_Amount,
-1 * APHD.Invoice_Dist_Amount,
-1 * APHD.Invoice_Dist_Base_Amount,
p_pay_hist_rec.Invoice_Adjustment_Event_ID,
-1 * APHD.Matured_Base_Amount,
-1 * APHD.Paid_Base_Amount,
'Y',
APHD.Payment_Hist_Dist_ID,
APHD.AWT_Related_ID,
'N',
APHD.Quantity_Variance,
APHD.Invoice_Base_Qty_Variance,
APHD.Amount_Variance,
APHD.Invoice_Base_Amt_Variance,
FND_GLOBAL.User_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
FND_GLOBAL.Prog_Appl_ID,
FND_GLOBAL.Conc_Program_ID,
NULL,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = p_invoice_dist_id
AND APHD.Payment_History_ID = APH.Payment_History_ID
AND APH.Check_ID = p_xla_event_rec.source_id_int_1 -- Bug 6887295
AND APH.Related_Event_ID = p_pay_hist_rec.related_event_id;
SELECT Payment_History_ID,
Accounting_Event_ID,
NVL(Historical_Flag, 'N')
INTO l_payment_history_id,
l_accounting_event_id,
l_historical_flag
FROM ap_payment_history_all APH
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
AND APH.Transaction_Type = l_transaction_type
AND APH.Posted_Flag = 'Y';
l_log_msg := 'Calling procedure Upg_Dist_Link_Insert';
Upg_Dist_Links_Insert
(p_xla_event_rec,
l_payment_history_id,
l_accounting_event_id,
l_curr_calling_sequence);
l_log_msg := 'Procedure Upg_Dist_Link_Insert complete';
PROCEDURE Pay_Dist_Insert
(P_PD_Rec IN AP_PAYMENT_HIST_DISTS%ROWTYPE
,P_Calling_Sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Pay_Dist_Insert';
l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Pay_Dist_Insert<- ' ||
P_Calling_Sequence;
INSERT INTO AP_Payment_Hist_Dists
(Payment_Hist_Dist_ID,
Accounting_Event_ID,
Amount,
Pay_Dist_Lookup_Code,
Payment_History_ID,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Bank_Curr_Amount,
Cleared_Base_Amount,
Invoice_Dist_Amount,
Invoice_Dist_Base_Amount,
Invoice_Adjustment_Event_ID,
Matured_Base_Amount,
Paid_Base_Amount,
Reversal_Flag,
Reversed_Pay_Hist_Dist_ID,
AWT_Related_ID,
PA_Addition_Flag,
Quantity_Variance,
Invoice_Base_Qty_Variance,
Amount_Variance,
Invoice_Base_Amt_Variance,
Created_By,
Creation_Date,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Program_Login_ID,
Program_Update_Date,
Request_ID
)
VALUES (AP_Payment_Hist_Dists_S.nextval,
P_PD_Rec.Accounting_Event_ID,
P_PD_Rec.Amount,
P_PD_Rec.Pay_Dist_Lookup_Code,
P_PD_Rec.Payment_History_ID,
P_PD_Rec.Invoice_Distribution_ID,
P_PD_Rec.Invoice_Payment_ID,
P_PD_Rec.Bank_Curr_Amount,
P_PD_Rec.Cleared_Base_Amount,
P_PD_Rec.Invoice_Dist_Amount,
P_PD_Rec.Invoice_Dist_Base_Amount,
P_PD_Rec.Invoice_Adjustment_Event_ID,
P_PD_Rec.Matured_Base_Amount,
P_PD_Rec.Paid_Base_Amount,
P_PD_Rec.Reversal_Flag,
P_PD_Rec.Reversed_Pay_Hist_Dist_ID,
P_PD_Rec.AWT_Related_ID,
'N',
P_PD_Rec.Quantity_Variance,
P_PD_Rec.Invoice_Base_Qty_Variance,
P_PD_Rec.Amount_Variance,
P_PD_Rec.Invoice_Base_Amt_Variance,
FND_GLOBAL.User_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
FND_GLOBAL.Prog_Appl_ID,
FND_GLOBAL.Conc_Program_ID,
NULL,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
);
END Pay_Dist_Insert;
PROCEDURE Upg_Dist_Links_Insert
(p_xla_event_rec IN ap_accounting_pay_pkg.r_xla_event_info
,p_payment_history_id IN NUMBER
,p_accounting_event_id IN NUMBER
,p_calling_sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Upg_Dist_Links_Insert';
l_curr_calling_sequence := 'AP_ACCTG_PAY_DIST_PKG.Upg_Dist_Links_Insert<- ' ||
P_Calling_Sequence;
DELETE FROM xla_distribution_links
WHERE application_id = 200
AND ae_header_id IN
(SELECT ae_header_id
FROM xla_ae_headers aeh,
ap_payment_history_all aph
WHERE aeh.event_id = aph.accounting_event_id
AND aph.accounting_event_id = p_accounting_event_id
AND aph.check_id = p_xla_event_rec.source_id_int_1
AND aph.historical_flag = 'Y'
AND aeh.upg_batch_id IS NOT NULL)
AND upg_batch_id IS NOT NULL;
l_log_msg := 'Inserting xla_distribution_links for event '||
p_accounting_event_id;
INSERT INTO XLA_Distribution_Links t1
(APPLICATION_ID,
EVENT_ID,
AE_HEADER_ID,
AE_LINE_NUM,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_NUM_1,
UNROUNDED_ENTERED_CR,
UNROUNDED_ENTERED_DR,
UNROUNDED_ACCOUNTED_CR,
UNROUNDED_ACCOUNTED_DR,
REF_AE_HEADER_ID,
ACCOUNTING_LINE_CODE,
ACCOUNTING_LINE_TYPE_CODE,
MERGE_DUPLICATE_CODE,
TEMP_LINE_NUM,
REF_EVENT_ID,
UPG_BATCH_ID,
LINE_DEFINITION_OWNER_CODE,
LINE_DEFINITION_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
--- changed for bug#7293021 start
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_ID,
APPLIED_TO_DIST_ID_NUM_1,
APPLIED_TO_DISTRIBUTION_TYPE
--- changed for bug#7293021 end
)
SELECT 200,
Accounting_Event_ID,
AE_Header_ID,
AE_Line_Num,
'AP_PMT_DIST',
Source_Distribution_ID_Num_1,
(CASE
WHEN Line_Entered_Cr IS NOT NULL THEN
Decode(Rank_Num, Dist_Count, (Line_Entered_Amt - Sum_Entered_Amt) +
Entered_Amt, Entered_Amt)
ELSE NULL
END),
(CASE
WHEN Line_Entered_Dr IS NOT NULL THEN
Decode(Rank_Num, Dist_Count, (Line_Entered_Amt - Sum_Entered_Amt) +
Entered_Amt, Entered_Amt)
ELSE NULL
END),
(CASE
WHEN Line_Accounted_Cr IS NOT NULL THEN
Decode(Rank_Num, Dist_Count, (Line_Accounted_Amt - Sum_Accounted_Amt) +
Accounted_Amt, Accounted_Amt)
ELSE NULL
END),
(CASE
WHEN Line_Accounted_Dr IS NOT NULL THEN
Decode(Rank_Num, Dist_Count, (Line_Accounted_Amt - Sum_Accounted_Amt) +
Accounted_Amt, Accounted_Amt)
ELSE NULL
END),
Ref_AE_Header_ID,
(CASE
WHEN Payment_Type_Flag = 'R' THEN
DECODE(Accounting_Class_Code,
'CASH_CLEARING', 'AP_CASH_CLEAR_REF', 'CASH', 'AP_CASH_REF',
'ACCRUAL', 'AP_ACCRUAL_REF', 'DISCOUNT', 'AP_DISCOUNT_ACCR_REF',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_REF',
'ROUNDING', 'AP_FINAL_PMT_ROUNDING_REF',
'GAIN', 'AP_GAIN_REF', 'FREIGHT', 'AP_FREIGHT_EXPENSE_REF',
'IPV', 'AP_INV_PRICE_VAR_REF', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_REF',
'LOSS', 'AP_LOSS_REF', 'LIABILITY', 'AP_LIAB_REF',
'NRTAX', 'AP_NON_RECOV_TAX_REF',
'PREPAID_EXPENSE', 'AP_PREPAID_EXP_REF', 'RTAX','AP_RECOV_TAX_REF',
'AWT', 'AP_WITHHOLD_TAX_ACCR_REF')
WHEN Transaction_Type = 'PAYMENT MATURITY' THEN
DECODE(Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT_MAT',
'CASH_CLEARING', 'AP_CASH_CLEAR_PMT_MAT',
'CASH', 'AP_CASH_PMT_MAT', 'GAIN', 'AP_GAIN_PMT_MAT',
'LOSS', 'AP_LOSS_PMT_MAT', 'ROUNDING', 'AP_FUTURE_PMT_ROUNDING_MAT')
WHEN Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED') THEN
DECODE(Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT',
'CASH_CLEARING', 'AP_CASH_CLEAR_PMT', 'CASH', 'AP_CASH_PMT',
'ACCRUAL', 'AP_ACCRUAL_PMT', 'DISCOUNT', 'AP_DISCOUNT_ACCR_PMT',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PMT',
'ROUNDING', 'AP_FINAL_PMT_ROUNDING_PMT',
'GAIN', 'AP_GAIN_PMT', 'FREIGHT', 'AP_FREIGHT_EXPENSE_PMT',
'IPV', 'AP_INV_PRICE_VAR_PMT', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_PMT',
'LOSS', 'AP_LOSS_PMT', 'LIABILITY', 'AP_LIAB_PMT',
'NRTAX', 'AP_NON_RECOV_TAX_PMT',
'PREPAID_EXPENSE', 'AP_PREPAID_EXP_PMT', 'RTAX','AP_RECOV_TAX_PMT',
'AWT', 'AP_WITHHOLD_TAX_ACCR_PMT')
WHEN Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') THEN
DECODE(Accounting_Class_Code, 'BANK_CHG', 'AP_BANK_CHARGES_CLEAR',
'CASH_CLEARING', 'AP_CASH_CLEAR_CLEAR', 'CASH', 'AP_CASH_CLEAR',
'ACCRUAL', 'AP_ACCRUAL_CLEAR', 'DISCOUNT', 'AP_DISCOUNT_ACCR_CLEAR',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_CLEAR',
'BANK_ERROR', 'AP_BANK_ERROR_CLEAR',
'ROUNDING', 'AP_FINAL_PMT_ROUNDING_CLEAR',
'GAIN', 'AP_GAIN_PMT_CLEAR', 'FREIGHT', 'AP_FREIGHT_EXPENSE_CLEAR',
'IPV', 'AP_INV_PRICE_VAR_CLEAR', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_CLEAR',
'LOSS', 'AP_LOSS_PMT_CLEAR', 'LIABILITY', 'AP_LIAB_CLEAR',
'NRTAX', 'AP_NON_RECOV_TAX_CLEAR', 'RTAX','AP_RECOV_TAX_CLEAR',
'AWT', 'AP_WITHHOLD_TAX_ACCR_CLEAR')
END),
'S',
'N',
Row_Number() OVER (PARTITION BY AE_Header_ID
ORDER BY AE_Line_Num,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Payment_History_ID) Temp_Line_Num,
Accounting_Event_ID,
Upg_Batch_ID,
'S',
'ACCRUAL_PAYMENTS_ALL',
'PAYMENTS',
'PAYMENTS_ALL',
-- changed for bug#7293021 start
DECODE(Accounting_Class_Code, 'LIABILITY' ,200, null),
DECODE(Accounting_Class_Code, 'LIABILITY' ,aid_Entity_id, null),
DECODE(Accounting_Class_Code, 'LIABILITY' ,Invoice_Distribution_ID, null),
'AP_INV_DIST'
-- changed for bug#7293021 end
FROM (
SELECT Accounting_Event_ID,
AE_Header_ID,
AE_Line_Num,
Line_Entered_Cr,
Line_Entered_Dr,
Line_Accounted_Cr,
Line_Accounted_Dr,
Invoice_Distribution_ID,
Invoice_Payment_ID,
Payment_History_ID,
Upg_Batch_ID,
Base_Currency_Code,
Source_Distribution_ID_Num_1,
Line_Entered_Amt,
Line_Accounted_Amt,
DECODE(FC.Minimum_Accountable_Unit, NULL,
ROUND((Line_Accounted_Amt * Dist_Base_Amount
/ DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt)),
FC.Precision),
ROUND((Line_Accounted_Amt * Dist_Base_Amount
/ DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt))
/FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit) Accounted_Amt,
DECODE(FC.Minimum_Accountable_Unit, NULL,
ROUND((Line_Entered_Amt * Dist_Amount
/ DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt)), FC.Precision),
ROUND((Line_Entered_Amt * Dist_Amount
/ DECODE(PDivisor_Acct_Amt, 0 ,1, PDivisor_Ent_Amt))
/FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit) Entered_Amt,
Dist_Count,
Rank_Num,
SUM(DECODE(FC.Minimum_Accountable_Unit, NULL,
ROUND((Line_Accounted_Amt * Dist_Base_Amount
/ DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt)),
FC.Precision),
ROUND((Line_Accounted_Amt * Dist_Base_Amount
/ DECODE(PDivisor_Acct_Amt, 0, 1, PDivisor_Acct_Amt))
/FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
OVER (PARTITION BY Check_ID, Part_Key1, Part_Key2, AE_Line_Num)
Sum_Accounted_Amt,
SUM(DECODE(FC.Minimum_Accountable_Unit, NULL,
ROUND((Line_Entered_Amt * Dist_Amount
/ DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt)), FC.Precision),
ROUND((Line_Entered_Amt * Dist_Amount
/ DECODE(PDivisor_Ent_Amt, 0 ,1, PDivisor_Ent_Amt))
/FC.Minimum_Accountable_Unit) * FC.Minimum_Accountable_Unit))
OVER (PARTITION BY Check_ID, Part_Key1, Part_Key2, AE_Line_Num) Sum_Entered_Amt,
Ref_AE_Header_ID,
Payment_Type_Flag,
Transaction_Type,
Accounting_Class_Code,
aid_Entity_id -- changed for bug#7293021
FROM (
SELECT AC.Check_ID Check_ID,
AEH.Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
AEL.AE_Line_Num AE_Line_Num,
AEL.Entered_Cr Line_Entered_Cr,
AEL.Entered_Dr Line_Entered_Dr,
AEL.Accounted_Cr Line_Accounted_Cr,
AEL.Accounted_Dr Line_Accounted_Dr,
APHD.Invoice_Distribution_ID Invoice_Distribution_ID,
APHD.Invoice_Payment_ID Invoice_Payment_ID,
APHD.Payment_History_ID Payment_History_ID,
AEL.Upg_Batch_ID Upg_Batch_ID,
ASP.Base_Currency_Code Base_Currency_Code,
APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
NVL(AEL.Entered_Cr, AEL.Entered_Dr) Line_Entered_Amt,
NVL(AEL.Accounted_Cr, AEL.Accounted_Dr) Line_Accounted_Amt,
AID.Amount Dist_Amount,
NVL(AID.Base_Amount, AID.Amount) Dist_Base_Amount,
COUNT(*) OVER (PARTITION BY AI.Invoice_ID, AID1.Invoice_Distribution_ID,
AEL.AE_Line_Num) Dist_Count,
RANK() OVER (PARTITION BY AI.Invoice_ID, AID1.Invoice_Distribution_ID,
AEL.AE_Line_Num
ORDER BY AID.Amount, AID.Distribution_Line_Number) Rank_Num,
AID1.Amount PDivisor_Ent_Amt,
NVL(AID1.Base_Amount, AID1.Amount) PDivisor_Acct_Amt,
AI.Invoice_ID Part_Key1,
AID1.Invoice_Distribution_ID Part_Key2,
AEH.AE_Header_ID Ref_AE_Header_ID,
AC.Payment_Type_Flag Payment_Type_Flag,
APH.Transaction_Type Transaction_Type,
AEL.Accounting_Class_Code Accounting_Class_Code,
aid_xe.entity_id aid_Entity_id
FROM AP_Checks_All AC,
AP_System_Parameters_All ASP,
XLA_Transaction_Entities_Upg XTE,
XLA_Events XLE,
AP_Payment_History_All APH,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Inv_Dists_Source AID1,
AP_Invoices_All AI,
AP_Invoice_Distributions_All AID,
AP_Payment_Hist_Dists APHD,
xla_events aid_xe -- changed for bug#7293021
WHERE AC.Check_ID = p_xla_event_rec.source_id_int_1
AND AC.Org_ID = ASP.Org_ID
AND XLE.Event_ID = p_accounting_event_id
AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
AND XTE.Entity_Code = 'AP_PAYMENTS'
AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
AND XTE.Application_ID = 200
AND XTE.Entity_ID = XLE.Entity_ID
AND XLE.Application_ID = 200
AND XLE.Event_ID = AEH.Event_ID
AND XLE.Upg_Batch_ID IS NOT NULL
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND XLE.Event_ID = APH.Accounting_Event_ID
AND APH.Check_ID = AC.Check_ID
AND APH.Payment_History_ID = p_payment_history_id
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
AEL.Source_ID, APHD.Invoice_Payment_ID)
AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AEL.Account_Overlay_Source_ID = AID1.Invoice_Distribution_ID
AND AID1.Invoice_ID = AI.Invoice_ID
AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
AND aid_xe.application_id = 200 --- changed for bug#7293021
AND aid_xe.event_id = aid.accounting_event_id
UNION ALL
SELECT AC.Check_ID Check_ID,
AEH.Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
AEL.AE_Line_Num AE_Line_Num,
AEL.Entered_Cr Line_Entered_Cr,
AEL.Entered_Dr Line_Entered_Dr,
AEL.Accounted_Cr Line_Accounted_Cr,
AEL.Accounted_Dr Line_Accounted_Dr,
APHD.Invoice_Distribution_ID Invoice_Distribution_ID,
APHD.Invoice_Payment_ID Invoice_Payment_ID,
APHD.Payment_History_ID Payment_History_ID,
AEL.Upg_Batch_ID Upg_Batch_ID,
ASP.Base_Currency_Code Base_Currency_Code,
APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
NVL(AEL.Entered_Cr, AEL.Entered_Dr) Line_Entered_Amt,
NVL(AEL.Accounted_Cr, AEL.Accounted_Dr) Line_Accounted_Amt,
AID.Amount Dist_Amount,
NVL(AID.Base_Amount, AID.Amount) Dist_Base_Amount,
COUNT(*) OVER (PARTITION BY AC.Check_ID,
DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
AEL.AE_Line_Num) Dist_Count,
RANK() OVER (PARTITION BY AC.Check_ID,
DECODE(AEL.Source_Table, 'AP_CHECKS', AC.Check_ID, AI.Invoice_ID),
AEL.AE_Line_Num
ORDER BY AID.Amount, AID.Distribution_Line_Number) Rank_Num,
SUM(AID.Amount)
OVER (PARTITION BY AC.Check_ID,
DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
AEL.AE_Line_Num) PDivisor_Ent_Amt,
SUM(NVL(AID.Base_Amount, AID.Amount))
OVER (PARTITION BY AC.Check_ID,
DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
AEL.AE_Line_Num) PDivisor_Acct_Amt,
DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID) Part_Key1,
1 Part_Key2,
AEH.AE_Header_ID Ref_AE_Header_ID,
AC.Payment_Type_Flag Payment_Type_Flag,
APH.Transaction_Type Transaction_Type,
AEL.Accounting_Class_Code Accounting_Class_Code,
aid_xe.entity_id aid_Entity_id --- changed for bug#7293021
FROM AP_Checks_All AC,
AP_System_Parameters_All ASP,
XLA_Transaction_Entities_Upg XTE,
XLA_Events XLE,
AP_Payment_History_All APH,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Payment_Hist_Dists APHD,
AP_Invoice_Distributions_All AID,
AP_Invoices_All AI,
xla_events aid_xe -- changed for bug#7293021
WHERE AC.Check_ID = p_xla_event_rec.source_id_int_1
AND AC.Org_ID = ASP.Org_ID
AND XLE.Event_ID = p_accounting_event_id
AND ASP.Set_Of_Books_ID = XTE.Ledger_ID
AND XTE.Entity_Code = 'AP_PAYMENTS'
AND AC.Check_ID = nvl(XTE.Source_ID_Int_1,-99)
AND XTE.Application_ID = 200
AND XTE.Entity_ID = XLE.Entity_ID
AND XLE.Application_ID = 200
AND XLE.Event_ID = AEH.Event_ID
AND XLE.Upg_Batch_ID IS NOT NULL
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND XLE.Event_ID = APH.Accounting_Event_ID
AND APH.Check_ID = AC.Check_ID
AND APH.Payment_History_ID = p_payment_history_id
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
AEL.Source_ID, APHD.Invoice_Payment_ID)
AND AEL.Account_Overlay_Source_ID IS NULL
AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AI.Invoice_ID = AID.Invoice_ID
AND aid_xe.application_id = 200 --- changed for bug#7293021
AND aid_xe.event_id = aid.accounting_event_id
) ADL,
FND_Currencies FC
WHERE FC.Currency_Code = ADL.Base_Currency_Code);
l_log_msg := 'Done inserting into xla_distribution_links';
END Upg_Dist_Links_Insert;