[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';
SELECT SUM( NVL( aphd.paid_base_amount, 0 ) ) inv_pay_amt
, SUM( NVL( aphd.invoice_dist_base_amount, 0 ) ) inv_amt
, SUM( NVL( aphd.cleared_base_amount, 0 ) ) inv_clr_amt
, SUM( NVL( aphd.matured_base_amount, 0 ) ) inv_mat_amt
, aid.invoice_id
FROM ap_invoice_distributions_all aid
, ap_payment_hist_dists aphd
WHERE aid.invoice_distribution_id = aphd.invoice_distribution_id
AND aphd.payment_history_id = p_payment_history_id
AND aphd.accounting_event_id = p_accounting_event_id
AND aphd.pay_dist_lookup_code NOT IN( 'FINAL CASH ROUNDING'
, 'FINAL PAYMENT ROUNDING'
, 'BANK CHARGE'
, 'BANK ERROR'
, 'EXCHANGE RATE VARIANCE'
, 'TAX EXCHANGE RATE VARIANCE' ) -- Bug 13783723
GROUP BY aid.invoice_id ;
l_curr_calling_sequence := 'AP_Acctg_Pay_Dist_Pkg.Update_Gain_Loss_Ind<- '||
p_calling_sequence;
UPDATE AP_PAYMENT_HIST_DISTS APHD
SET APHD.GAIN_LOSS_INDICATOR =
DECODE(SIGN( GAIN_LOSS_TBL(I).INV_AMT-GAIN_LOSS_TBL(I).INV_PAY_AMT)--BUG 8276839
, 1, 'G'
, -1, 'L'
, NULL)
WHERE APHD.ACCOUNTING_EVENT_ID =P_XLA_EVENT_REC.EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID=GAIN_LOSS_TBL(I).INVOICE_ID) ;
UPDATE AP_PAYMENT_HIST_DISTS APHD
SET APHD.GAIN_LOSS_INDICATOR =
DECODE(SIGN( GAIN_LOSS_TBL(I).INV_PAY_AMT-GAIN_LOSS_TBL(I).INV_MAT_AMT) --BUG 8276839
, 1, 'G'
,-1, 'L'
, NULL)
WHERE APHD.ACCOUNTING_EVENT_ID =P_XLA_EVENT_REC.EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID=GAIN_LOSS_TBL(I).INVOICE_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';
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;
UPDATE AP_PAYMENT_HIST_DISTS APHD
SET APHD.GAIN_LOSS_INDICATOR =
DECODE(SIGN( GAIN_LOSS_TBL(I).INV_MAT_AMT-GAIN_LOSS_TBL(I).INV_CLR_AMT)--BUG 8276839
,1, 'G'
,-1, 'L'
, NULL)
WHERE APHD.ACCOUNTING_EVENT_ID =P_XLA_EVENT_REC.EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID=GAIN_LOSS_TBL(I).INVOICE_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_HIST_DISTS APHD
SET APHD.GAIN_LOSS_INDICATOR =
DECODE(SIGN( GAIN_LOSS_TBL(I).INV_AMT-GAIN_LOSS_TBL(I).INV_CLR_AMT) --BUG 8276839
,1, 'G'
, -1,'L'
, NULL)
WHERE APHD.ACCOUNTING_EVENT_ID=P_XLA_EVENT_REC.EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID=GAIN_LOSS_TBL(I).INVOICE_ID) ;
l_log_msg := 'Update Gain/Loss between payment and clearing for gain/loss at always';
UPDATE AP_PAYMENT_HIST_DISTS APHD
SET APHD.GAIN_LOSS_INDICATOR =
DECODE(SIGN( GAIN_LOSS_TBL(I).INV_PAY_AMT-GAIN_LOSS_TBL(I).INV_CLR_AMT)--BUG 8276839
,1 ,'G'
,-1,'L'
, NULL)
WHERE APHD.ACCOUNTING_EVENT_ID =P_XLA_EVENT_REC.EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID=GAIN_LOSS_TBL(I).INVOICE_ID) ;
l_log_msg := 'Update Gain/Loss for Payment Cancellation';
UPDATE AP_PAYMENT_HIST_DISTS APHD
SET APHD.GAIN_LOSS_INDICATOR =
DECODE(SIGN( GAIN_LOSS_TBL(I).INV_PAY_AMT-GAIN_LOSS_TBL(I).INV_AMT)--BUG 8276839
, 1, 'G'
, -1, 'L'
, NULL)
WHERE APHD.ACCOUNTING_EVENT_ID =P_XLA_EVENT_REC.EVENT_ID
AND APHD.INVOICE_DISTRIBUTION_ID IN
(SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID=GAIN_LOSS_TBL(I).INVOICE_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','PREPAYMENT ADJUSTED') --7630203 12731687
AND aid.org_id = asp.org_id
AND automatic_offsets_flag = 'N'
AND aid.historical_flag = 'Y'
AND evnt.application_id=200; --7623562
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 'N'
INTO l_exec_final_pay
FROM dual
WHERE EXISTS(
SELECT 'Unreversed clearing txns with different xrates'
FROM ap_payment_history_all aph,
ap_invoice_payments_all aip
WHERE aip.invoice_id = l_inv_pay_rec.invoice_id
AND aip.check_id = aph.check_id
AND aph.transaction_type = 'PAYMENT CLEARING'
AND EXISTS(
SELECT 'Unreversed clearing txn with different xrate'
FROM ap_payment_history_all aph_sub,
ap_invoice_payments_all aip_sub
WHERE aip_sub.invoice_id = aip.invoice_id
AND aip_sub.check_id = aph_sub.check_id
AND aph_sub.check_id <> aph.check_id
AND aph_sub.transaction_type = 'PAYMENT CLEARING'
AND (NVL(aph_sub.bank_to_base_xrate, -1) <> NVL(aph.bank_to_base_xrate, -1)
OR NVL(aph_sub.pmt_to_base_xrate, -1) <> NVL(aph.pmt_to_base_xrate, -1))
AND aph_sub.rev_pmt_hist_id IS NULL
AND NOT EXISTS(
SELECT 'reversal txn'
FROM ap_payment_history_all aph_rev
WHERE aph_rev.REV_PMT_HIST_ID = aph_sub.payment_history_id)));
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))),
SUM(decode(aid.awt_invoice_payment_id, Null, nvl(aid.amount, 0),0)),
SUM(decode(aid.awt_invoice_payment_id, Null, 0,nvl(aid.amount, 0))),
SUM(decode(aid.line_type_lookup_code, 'AWT',decode(aid.awt_invoice_payment_id, Null, nvl(aid.amount, 0),0), 0))
INTO G_Total_Dist_Amount,
G_Proration_Divisor,
l_inv_time_dist_total,
l_curr_pay_awt_tot,
l_inv_time_awt_tot
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 Sub.Invoice_Distribution_ID
INTO G_Last_NonExcluded_Dist_ID
FROM (SELECT AID.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID,
Financials_System_Params_All FSP,
AP_Invoices_All AI,
AP_System_Parameters_All ASP
WHERE AID.Invoice_ID = l_inv_rec.Invoice_ID
AND AI.Invoice_ID = AID.Invoice_ID
AND ASP.Org_ID = AI.Org_ID
AND AID.Line_Type_Lookup_Code NOT IN ('PREPAY', 'ERV', 'TERV'
, 'AWT' --Pay_Dist_Discount is only called for non-AWT line types
, decode(AI.Exclude_Freight_From_Discount,'Y', 'FREIGHT', 'DUMMY')
, decode(NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
, 'Y', 'TRV', 'DUMMY')
, decode(NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
, 'Y', 'TIPV', 'DUMMY')
, decode(NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
, 'Y', 'NONREC_TAX', 'DUMMY')
, decode(NVL(AI.Disc_Is_Inv_Less_Tax_Flag, ASP.Disc_Is_Inv_Less_Tax_Flag)
, 'Y', 'REC_TAX', 'DUMMY')
)
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
AND NOT EXISTS (SELECT 1
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND application_id = 200 --bug 7308385
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')))
ORDER BY abs(AID.Amount) desc, AID.Invoice_Distribution_ID desc) Sub
WHERE rownum = 1;
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))),
SUM(decode(aid.awt_invoice_payment_id, Null, nvl(aid.amount, 0),0)),
SUM(decode(aid.awt_invoice_payment_id, Null, 0,nvl(aid.amount, 0))),
SUM(decode(aid.line_type_lookup_code, 'AWT',decode(aid.awt_invoice_payment_id, Null, nvl(aid.amount, 0),0), 0))
INTO G_Total_Dist_Amount,
G_Proration_Divisor,
l_inv_time_dist_total,
l_curr_pay_awt_tot,
l_inv_time_awt_tot
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')
ORDER BY DECODE(AID.Line_Type_Lookup_Code, 'AWT', 1, 2),
abs(AID.Amount), AID.Invoice_Distribution_ID;
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
AND AIP.REVERSAL_INV_PMT_ID IS NULL; --bug 9005225
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))),
SUM(decode(aid.awt_invoice_payment_id, Null, nvl(aid.amount, 0),0)),
SUM(decode(aid.awt_invoice_payment_id, Null, 0,nvl(aid.amount, 0))),
SUM(decode(aid.line_type_lookup_code, 'AWT',decode(aid.awt_invoice_payment_id, Null, nvl(aid.amount, 0),0), 0))
INTO G_Total_Dist_Amount,
G_Proration_Divisor,
l_inv_time_dist_total,
l_curr_pay_awt_tot,
l_inv_time_awt_tot
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 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;
SELECT count(*)
INTO l_pay_dist_cnt
FROM ap_payment_hist_dists
WHERE invoice_distribution_id = l_inv_dist_rec.parent_reversal_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 + l_tech_disc_rnd_amt,
APD.bank_curr_amount = APD.bank_curr_amount + l_tech_disc_rnd_amt,
APD.Cleared_Base_Amount = Decode(l_pay_hist_rec.bank_currency_code,
ap_accounting_pay_pkg.g_base_currency_code,
APD.Cleared_Base_Amount + l_tech_disc_rnd_amt,
APD.Cleared_Base_Amount),
APD.Paid_Base_Amount = Decode(l_pay_hist_rec.bank_currency_code,
ap_accounting_pay_pkg.g_base_currency_code,
APD.Paid_Base_Amount + l_tech_disc_rnd_amt,
APD.Paid_Base_Amount),
APD.Matured_Base_Amount = Decode(l_pay_hist_rec.bank_currency_code,
ap_accounting_pay_pkg.g_base_currency_code,
APD.Matured_Base_Amount + l_tech_disc_rnd_amt,
APD.Matured_Base_Amount),
APD.Invoice_Dist_Base_Amount = Decode(l_pay_hist_rec.bank_currency_code,
ap_accounting_pay_pkg.g_base_currency_code,
Decode(l_pay_hist_rec.bank_currency_code,
l_inv_rec.invoice_currency_code,
APD.Invoice_Dist_Base_Amount + l_tech_disc_rnd_amt,
APD.Invoice_Dist_Base_Amount),
APD.Invoice_Dist_Base_Amount),
APD.Invoice_Dist_Amount = Decode(l_pay_hist_rec.bank_currency_code,
l_inv_rec.invoice_currency_code,
APD.Invoice_Dist_Amount + l_tech_disc_rnd_amt,
APD.Invoice_Dist_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 = '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;
/*This selection and update statemen massage last big distribution of discount distribution */
SELECT SUM (DECODE (APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.Amount, 0))
, SUM (DECODE (APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.invoice_dist_amount, 0))
, SUM (DECODE (APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.bank_curr_amount, 0))
, SUM (DECODE (APHD.Pay_Dist_Lookup_Code, 'DISCOUNT', APHD.invoice_dist_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))
INTO l_sum_disc_amount
, l_sum_disc_dist_amount
, l_sum_disc_bank_curr_amount
, l_sum_disc_dist_base_amount
, l_sum_disc_paid_base_amount
, l_sum_disc_clr_base_amount
FROM AP_Payment_Hist_Dists APHD
WHERE APHD.Invoice_Payment_ID = l_inv_pay_rec.invoice_payment_id
AND APHD.Payment_History_ID = l_pay_hist_rec.Payment_History_ID;
SELECT MAX (APD1.Invoice_Distribution_ID)
INTO l_max_disc_dist_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 ABS (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') ;
UPDATE AP_Payment_Hist_Dists APD
SET APD.Amount = APD.Amount - l_sum_disc_amount
, APD.Invoice_Dist_Amount = APD.Invoice_Dist_Amount - l_sum_disc_dist_amount
, APD.bank_curr_amount = APD.bank_curr_amount - l_sum_disc_bank_curr_amount
, APD.Invoice_Dist_Base_Amount = APD.Invoice_Dist_Base_Amount - l_sum_disc_dist_base_amount
, APD.Paid_Base_Amount = APD.Paid_Base_Amount - l_sum_disc_paid_base_amount
, apd.cleared_base_amount = apd.cleared_base_amount - l_sum_disc_clr_base_amount
WHERE APD.Invoice_Distribution_ID = l_max_disc_dist_id
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;
/*This update statement massage last big distribution of CASH distribution
because when this code reaches here, pay_dist_proc call is already over as above update adjusted
discount amount after all technical rounding so the same amount for same distribution id we are
adjusting here. */
UPDATE AP_Payment_Hist_Dists APD
SET APD.Amount = APD.Amount + l_sum_disc_amount
, APD.Invoice_Dist_Amount = APD.Invoice_Dist_Amount + l_sum_disc_dist_amount
, APD.bank_curr_amount = APD.bank_curr_amount + l_sum_disc_bank_curr_amount
, APD.Invoice_Dist_Base_Amount = APD.Invoice_Dist_Base_Amount + l_sum_disc_dist_base_amount
, APD.Paid_Base_Amount = APD.Paid_Base_Amount + l_sum_disc_paid_base_amount
, apd.cleared_base_amount = apd.cleared_base_amount + l_sum_disc_clr_base_amount
WHERE APD.Invoice_Distribution_ID = l_max_disc_dist_id
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_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
(p_inv_dist_rec.amount
/ g_proration_divisor
* ( l_pay_amount_inv_curr
- g_pay_awt_total_amt
)
+ nvl
(
(SELECT sum(amount)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id =p_inv_pay_rec.invoice_id
AND aid.awt_invoice_payment_id =p_inv_pay_rec.invoice_payment_id
AND aid.line_type_lookup_code ='AWT'
AND aid.awt_related_id =p_inv_dist_rec.invoice_distribution_id
)
,0
)
+ nvl
(
(SELECT sum(amount) / g_proration_divisor * (l_pay_amount_inv_curr - g_pay_awt_total_amt)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id =p_inv_pay_rec.invoice_id
AND aid.line_type_lookup_code ='AWT'
AND awt_invoice_payment_id is null
AND awt_related_id =p_inv_dist_rec.invoice_distribution_id
)
,0
)
)
INTO l_inv_dist_amount
FROM sys.dual;
SELECT -NVL(SUM(aphd.amount), 0) ,
-NVL(SUM(aphd.invoice_dist_amount), 0),
-NVL(SUM(aphd.bank_curr_amount), 0)
INTO l_prorated_amount,
l_inv_dist_amount ,
l_bank_curr_amount
FROM ap_payment_hist_dists aphd
WHERE aphd.invoice_distribution_id IN
(SELECT invoice_distribution_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_inv_rec.invoice_id
)
AND aphd.payment_history_id IN
(SELECT aph.payment_history_id
FROM ap_payment_history_all aph,
ap_invoice_distributions_all aid
WHERE aph.check_id = p_xla_event_rec.source_id_int_1
AND aid.invoice_id = p_inv_rec.invoice_id
AND aph.invoice_adjustment_event_id = aid.accounting_event_id
AND aph.transaction_type =
(SELECT transaction_type
FROM ap_payment_history_all aph_evt
WHERE aph_evt.payment_history_id = p_pay_hist_rec.payment_history_id)
AND aph.posted_flag = 'S');
SELECT
(p_inv_dist_rec.amount
/ g_proration_divisor
* ( l_pay_amount_inv_curr
- g_pay_awt_total_amt
)
+ nvl
(
(SELECT sum(amount)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id =p_inv_pay_rec.invoice_id
AND aid.awt_invoice_payment_id =p_inv_pay_rec.invoice_payment_id
AND aid.line_type_lookup_code ='AWT'
AND aid.awt_related_id =p_inv_dist_rec.invoice_distribution_id
)
,0
)
+ nvl
(
(SELECT sum(amount) / g_proration_divisor * (l_pay_amount_inv_curr - g_pay_awt_total_amt)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id =p_inv_pay_rec.invoice_id
AND aid.line_type_lookup_code ='AWT'
AND awt_invoice_payment_id is null
AND awt_related_id =p_inv_dist_rec.invoice_distribution_id
)
,0
)
)
INTO l_inv_dist_amount
FROM sys.dual;
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'
AND NOT EXISTS
(SELECT 1 FROM AP_PAYMENT_HIST_DISTS APHD1
WHERE APHD1.ACCOUNTING_EVENT_ID=P_Event_ID
AND APHD1.INVOICE_DISTRIBUTION_ID = APHD.INVOICE_DISTRIBUTION_ID
AND APHD1.PAY_DIST_LOOKUP_CODE ='BANK CHARGE')
ORDER BY Amount; /* Bug13385106 */
SELECT SUM(Amount)
INTO l_total_pay_amt
FROM AP_Payment_Hist_Dists
WHERE accounting_event_id = nvl(p_pay_hist_rec.related_event_id,P_XLA_Event_Rec.event_id)/* Bug13385106 */
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,
Gain_Loss_Indicator --11681786
)
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,
APHD.Gain_Loss_Indicator --11681786
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,
AP_Invoice_Distributions_ALL AID1 --Bug11681786
WHERE APHD1.Reversed_Pay_Hist_Dist_ID = APHD.Payment_Hist_Dist_ID -- Bug 6856694
AND APHD1.Invoice_Distribution_ID IN (AID1.Invoice_Distribution_id, APHD.Invoice_Distribution_ID)
AND AID1.Parent_Reversal_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,
AP_Invoice_Distributions_ALL AID1 --Bug11681786
WHERE APHD1.Reversed_Pay_Hist_Dist_ID = APHD.Payment_Hist_Dist_ID -- Bug 6856694
AND APHD1.Invoice_Distribution_ID IN (AID1.Invoice_Distribution_id, APHD.Invoice_Distribution_ID)
AND AID1.Parent_Reversal_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
AND APHD.invoice_payment_id = p_inv_pay_rec.invoice_payment_id ; -- Bug 13107915
SELECT Payment_History_ID,
Accounting_Event_ID,
NVL(Historical_Flag, 'N') Historical_Flag,
XAH.upg_batch_id
FROM ap_payment_history_all APH,
xla_ae_headers XAH,
ap_system_parameters_all ASP
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
-- AND APH.rev_pmt_hist_id is null bug9448974
AND APH.Posted_Flag = 'Y'
AND XAH.application_id = 200
AND XAH.event_id = APH.accounting_event_id
AND ASP.org_id = APH.org_id
AND ASP.set_of_books_id = XAH.ledger_id)
LOOP
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
l_log_msg := 'Inside loop for upgrading dist links for '||
'PAYMENT CANCELLED,REFUND CANCELLED ';
l_log_msg := 'Calling Upg_Dist_Links_Insert for payment_history_id '||
aph_events.payment_history_id;
SELECT 'Y'
INTO l_reversed_in_R12
FROM dual
WHERE EXISTS
(SELECT 'reversed in R12'
FROM ap_payment_history_all aph,
xla_ae_headers xah,
ap_system_parameters_all asp
WHERE aph.rev_pmt_hist_id = aph_events.payment_history_id
AND xah.application_id = 200
AND aph.accounting_event_id = xah.event_id
AND aph.posted_flag = 'Y'
AND xah.accounting_entry_status_code = 'F'
AND xah.ledger_id = asp.set_of_books_id
AND aph.org_id = asp.org_id --bug13814470
AND (xah.upg_batch_id IS NULL OR
xah.upg_batch_id = -9999));
Upg_Dist_Links_Insert
(p_xla_event_rec,
aph_events.payment_history_id,
aph_events.accounting_event_id,
l_curr_calling_sequence);
l_log_msg := 'Procedure Upg_Dist_Link_Insert complete';
SELECT Payment_History_ID,
Accounting_Event_ID,
NVL(Historical_Flag, 'N'),
XAH.upg_batch_id
INTO l_payment_history_id,
l_accounting_event_id,
l_historical_flag,
l_upg_batch_id
FROM ap_payment_history_all APH,
xla_ae_headers XAH,
ap_system_parameters_all ASP
WHERE APH.Check_ID = p_xla_event_rec.source_id_int_1
AND APH.Transaction_Type = l_transaction_type
AND APH.payment_history_id =
DECODE(l_transaction_type,
'PAYMENT CLEARING', p_pay_hist_rec.rev_pmt_hist_id,
'PAYMENT MATURITY', p_pay_hist_rec.rev_pmt_hist_id,
APH.payment_history_id)
AND APH.Posted_Flag = 'Y'
AND XAH.application_id = 200
AND XAH.event_id = APH.accounting_event_id
AND ASP.org_id = APH.org_id
AND ASP.set_of_books_id = XAH.ledger_id;
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,
--Bug 12619564 start
(
CASE
WHEN (Line_Entered_cr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) <> -1)
or (Line_Entered_dr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) =-1)
THEN abs(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
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) <> -1)
or (Line_Entered_cr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) =-1)
THEN abs(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
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))<> -1)
or (Line_Accounted_dr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))=-1)
THEN abs(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
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))<> -1)
or (Line_Accounted_cr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))=-1)
THEN abs(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))
ELSE NULL
END ),
--Bug 12619564 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',
'A', --changed by abhsaxen for bug#9073033
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,
APHD.Invoice_Payment_Id, --bug9307438
AID.Invoice_distribution_id --bug8774970
/*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,
xte_inv.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_Transaction_Entities_Upg XTE_INV --Bug7169843 Bug11071399
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 AEH.ledger_id = ASP.Set_Of_Books_ID -- Bug#8708433
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)
-- begin 8774970
AND NVL(AID.Old_Distribution_Id, AID.Invoice_Distribution_Id) =
DECODE(AEL.Source_Table, 'AP_INVOICE_DISTRIBUTIONS',
AEL.Source_ID, NVL(AID.Old_Distribution_Id,APHD.Invoice_Distribution_Id))
AND AID.Invoice_Id = DECODE(AEL.Source_Table, 'AP_INVOICES',
AEL.Source_ID, AID.Invoice_Id)
AND APH.Check_Id = DECODE(AEL.Source_Table, 'AP_CHECKS',
AEL.Source_ID, APH.Check_Id)
-- end 8774970
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 XTE_INV.ledger_id = AID.Set_Of_Books_id -- Bug11071399 start
AND XTE_INV.Entity_Code = 'AP_INVOICES'
AND XTE_INV.Application_id = 200
AND NVL(XTE_INV.Source_id_int_1, -99) = AID.invoice_id -- Bug11071399 end
--Bug 12619564 start
/* AND DECODE(ael.accounting_class_code,'AWT','AWT','DISCOUNT','DISCOUNT',
aphd.pay_dist_lookup_code) = aphd.pay_dist_lookup_code
Commented by Bug 12619564
*/
AND decode(AEL.source_table, 'AP_CHECKS', AEL.Accounting_Class_Code, APHD.Pay_Dist_Lookup_Code)
= decode(AEL.source_table, 'AP_CHECKS'
,decode(APHD.Pay_Dist_Lookup_Code,'AWT','AWT','DISCOUNT','DISCOUNT',
AEL.Accounting_Class_Code) ,APHD.Pay_Dist_Lookup_Code)
AND ael.accounting_class_code =
decode(aphd.pay_dist_lookup_code
, 'AWT', DECODE(ael.accounting_class_code
,'LIABILITY',ael.accounting_class_code
,aphd.pay_dist_lookup_code)
, 'DISCOUNT', aphd.pay_dist_lookup_code
, 'BANK_CHG', aphd.pay_dist_lookup_code
, 'BANK_ERROR', aphd.pay_dist_lookup_code
, DECODE(ael.accounting_class_code
,'BANK_CHG',aphd.pay_dist_lookup_code
,'BANK_ERROR',aphd.pay_dist_lookup_code
,'AWT',aphd.pay_dist_lookup_code
,'DISCOUNT',aphd.pay_dist_lookup_code
,ael.accounting_class_code)
) --Bug 13533030
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, aeh.ae_header_id, -- bug 8638413
DECODE(AEL.Source_Table, 'AP_CHECKS', AC.Check_ID, AI.Invoice_ID),
AEL.AE_Line_Num
ORDER BY AID.Amount,
APHD.Invoice_Payment_Id, --bug9307438
AID.Invoice_distribution_id --bug8774970
/*AID.Distribution_Line_Number*/) Rank_Num,
SUM(AID.Amount)
OVER (PARTITION BY AC.Check_ID, aeh.ae_header_id, -- bug 8638413
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, aeh.ae_header_id, -- bug 8638413
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,
xte_inv.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_Transaction_Entities_Upg XTE_INV --Bug7169843 Bug11071399
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 AEH.ledger_id = ASP.Set_Of_Books_ID -- Bug#8708433
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)
-- begin 8774970
AND NVL(AID.Old_Distribution_Id, AID.Invoice_Distribution_Id) =
DECODE(AEL.Source_Table, 'AP_INVOICE_DISTRIBUTIONS',
AEL.Source_ID, NVL(AID.Old_Distribution_Id,APHD.Invoice_Distribution_Id))
AND AID.Invoice_Id = DECODE(AEL.Source_Table, 'AP_INVOICES',
AEL.Source_ID, AID.Invoice_Id)
AND APH.Check_Id = DECODE(AEL.Source_Table, 'AP_CHECKS',
AEL.Source_ID, APH.Check_Id)
-- end 8774970
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 XTE_INV.ledger_id = AID.Set_Of_Books_id -- Bug11071399 start
AND XTE_INV.Entity_Code = 'AP_INVOICES'
AND XTE_INV.Application_id = 200
AND NVL(XTE_INV.Source_id_int_1, -99) = AID.invoice_id
/* AND DECODE(ael.accounting_class_code,'AWT','AWT','DISCOUNT','DISCOUNT', aphd.pay_dist_lookup_code)
= aphd.pay_dist_lookup_code --8293590
Commented by Bug 12619564
*/
AND decode(AEL.source_table, 'AP_CHECKS', AEL.Accounting_Class_Code, APHD.Pay_Dist_Lookup_Code)
= decode(AEL.source_table, 'AP_CHECKS'
,decode(APHD.Pay_Dist_Lookup_Code,'AWT','AWT','DISCOUNT','DISCOUNT',
AEL.Accounting_Class_Code) ,APHD.Pay_Dist_Lookup_Code) --8293590
AND ael.accounting_class_code =
decode(aphd.pay_dist_lookup_code
, 'AWT', DECODE(ael.accounting_class_code
,'LIABILITY',ael.accounting_class_code
,aphd.pay_dist_lookup_code)
, 'DISCOUNT', aphd.pay_dist_lookup_code
, 'BANK_CHG', aphd.pay_dist_lookup_code
, 'BANK_ERROR', aphd.pay_dist_lookup_code
, DECODE(ael.accounting_class_code
,'BANK_CHG',aphd.pay_dist_lookup_code
,'BANK_ERROR',aphd.pay_dist_lookup_code
,'AWT',aphd.pay_dist_lookup_code
,'DISCOUNT',aphd.pay_dist_lookup_code
,ael.accounting_class_code)
) --Bug 13533030
) ADL,
FND_Currencies FC
WHERE FC.Currency_Code = ADL.Base_Currency_Code);
l_log_msg := 'Done inserting into xla_distribution_links for primary ledger';
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,
--Bug 12619564 start
(
CASE
WHEN (Line_Entered_cr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) <> -1)
or (Line_Entered_dr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) =-1)
THEN abs(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
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) <> -1)
or (Line_Entered_cr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Entered_Amt - Sum_Entered_Amt ) + Entered_Amt, Entered_Amt )) =-1)
THEN abs(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
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))<> -1)
or (Line_Accounted_dr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))=-1)
THEN abs(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
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))<> -1)
or (Line_Accounted_cr is not null
and sign(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))=-1)
THEN abs(DECODE( Rank_Num, Dist_Count,( Line_Accounted_Amt - Sum_Accounted_Amt ) + Accounted_Amt, Accounted_Amt ))
ELSE NULL
END ),
--Bug 12619564 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',
'A', --changed by abhsaxen for bug#9073033
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'
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
FROM
(
SELECT AC.Check_ID Check_ID,--13520870 added the select query
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,
COALESCE(AID_MC.Amount, AID.Amount) Dist_Amount,
COALESCE(AID_MC.Base_Amount, AID_MC.Amount, 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,
APHD.Invoice_Payment_Id, --bug9307438
AID.Invoice_distribution_id --bug8774970
/*AID.Distribution_Line_Number*/) Rank_Num,
COALESCE(AID_MC.Amount, AID.Amount) PDivisor_Ent_Amt,
COALESCE(AID_MC.Base_Amount, AID_MC.Amount, AID.Base_Amount, AID.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,
xte_inv.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_Transaction_Entities_Upg XTE_INV, --Bug7169843 Bug11071399
AP_MC_Invoice_Dists AID_MC
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 AEH.ledger_id <> ASP.Set_Of_Books_ID -- Bug#8708433
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)
-- begin 8774970
AND NVL(AID.Old_Distribution_Id, AID.Invoice_Distribution_Id) =
DECODE(AEL.Source_Table, 'AP_INVOICE_DISTRIBUTIONS',
AEL.Source_ID, NVL(AID.Old_Distribution_Id,APHD.Invoice_Distribution_Id))
AND AID.Invoice_Id = DECODE(AEL.Source_Table, 'AP_INVOICES',
AEL.Source_ID, AID.Invoice_Id)
AND APH.Check_Id = DECODE(AEL.Source_Table, 'AP_CHECKS',
AEL.Source_ID, APH.Check_Id)
-- end 8774970
AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AEL.Account_Overlay_Source_ID = AID1.Invoice_Distribution_ID
AND AID.Invoice_ID = AI.Invoice_ID
AND AID1.Invoice_ID = AID.Invoice_ID
AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
AND XTE_INV.ledger_id = AID.Set_Of_Books_id -- Bug11071399 start
AND XTE_INV.Entity_Code = 'AP_INVOICES'
AND XTE_INV.Application_id = 200
AND NVL(XTE_INV.Source_id_int_1, -99) = AID.invoice_id -- Bug11071399 end
AND AID1.Invoice_Distribution_ID = AID_MC.Invoice_Distribution_ID(+)
AND AID1.Invoice_id = AID_MC.Invoice_id(+)
AND AEH.ledger_id = NVL(AID_MC.Set_Of_Books_ID,AEH.ledger_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,
COALESCE(AID_MC.Amount, AID.Amount) Dist_Amount ,--13520870
COALESCE(AID_MC.Base_Amount, AID_MC.Amount, AID.Base_Amount, AID.Amount) Dist_Base_Amount,--13520870
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, aeh.ae_header_id, -- bug 8638413
DECODE(AEL.Source_Table, 'AP_CHECKS', AC.Check_ID, AI.Invoice_ID),
AEL.AE_Line_Num
ORDER BY AID.Amount, --13520870
APHD.Invoice_Payment_Id, --bug9307438
AID.Invoice_distribution_id) Rank_Num,--13520870
SUM(COALESCE(AID_MC.Amount, AID.Amount)) --13520870
OVER (PARTITION BY AC.Check_ID, aeh.ae_header_id, -- bug 8638413
DECODE(AEL.Source_Table, 'AP_CHECKS', 1, AI.Invoice_ID),
AEL.AE_Line_Num) PDivisor_Ent_Amt,
SUM(COALESCE(AID_MC.Base_Amount, AID_MC.Amount, AID.Base_Amount, AID.Amount)) --13520870
OVER (PARTITION BY AC.Check_ID, aeh.ae_header_id, -- bug 8638413
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,
xte_inv.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_mc_invoice_dists AID_MC,
AP_Invoices_All AI,
XLA_Transaction_Entities_Upg XTE_INV, --Bug7169843 Bug11071399
AP_Invoice_Distributions_All AID,
AP_Inv_Dists_Source AID1 --13520870
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 AEH.ledger_id <> ASP.Set_Of_Books_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)
-- begin 8774970
AND NVL(AID.Old_Distribution_Id, AID.Invoice_Distribution_Id) =
DECODE(AEL.Source_Table, 'AP_INVOICE_DISTRIBUTIONS',
AEL.Source_ID, NVL(AID.Old_Distribution_Id,APHD.Invoice_Distribution_Id))
AND AID.Invoice_Id = DECODE(AEL.Source_Table, 'AP_INVOICES',
AEL.Source_ID, AID.Invoice_Id)
AND APH.Check_Id = DECODE(AEL.Source_Table, 'AP_CHECKS',
AEL.Source_ID, APH.Check_Id)
-- end 8774970
AND AEL.Account_Overlay_Source_ID IS NULL
AND APHD.Invoice_Distribution_ID = AID.invoice_distribution_id --13520870
AND AID1.Invoice_ID = AID.Invoice_ID --13520870
AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID --13520870
AND AID1.Invoice_Distribution_ID = AID_MC.Invoice_Distribution_ID(+) --13520870
AND AID1.Invoice_ID = AID_MC.Invoice_ID(+) --13520870
AND AEH.ledger_id = NVL(AID_MC.set_of_books_id, AEH.ledger_id) --13520870
AND AI.Invoice_ID = AID.Invoice_ID --13520870
AND XTE_INV.ledger_id = AID.Set_Of_Books_id -- Bug11071399 start
AND XTE_INV.Entity_Code = 'AP_INVOICES'
AND XTE_INV.Application_id = 200
AND NVL(XTE_INV.Source_id_int_1, -99) = AID.invoice_id -- Bug11071399 end
-- AND AID.invoice_distribution_id = AID_MC.Invoice_Distribution_ID --13520870
/* AND DECODE(ael.accounting_class_code,'AWT','AWT','DISCOUNT','DISCOUNT',
aphd.pay_dist_lookup_code) = aphd.pay_dist_lookup_code --8293590
Commented by Bug 12619564
*/
AND decode(AEL.source_table, 'AP_CHECKS', AEL.Accounting_Class_Code, APHD.Pay_Dist_Lookup_Code)
= decode(AEL.source_table, 'AP_CHECKS'
,decode(APHD.Pay_Dist_Lookup_Code,'AWT','AWT','DISCOUNT','DISCOUNT',
AEL.Accounting_Class_Code) ,APHD.Pay_Dist_Lookup_Code)
AND DECODE(ael.accounting_class_code,'AWT','AWT','DISCOUNT','DISCOUNT','True')
=DECODE(aphd.pay_dist_lookup_code,'AWT','AWT','DISCOUNT','DISCOUNT','True') -- Bug 12619564
) ADL,
FND_Currencies FC
WHERE FC.Currency_Code = ADL.Base_Currency_Code );
l_log_msg := 'Done inserting into xla_distribution_links for non-primary ledgers';
/* Added MERGE inplace of update statement for bug13437260 */
MERGE
INTO ap_payment_hist_dists aphd
USING ( SELECT aphd.payment_hist_dist_id,
aphd.invoice_dist_base_amount,
NVL(xdl.unrounded_accounted_cr,0) - NVL(xdl.unrounded_accounted_dr,0) xdl_acctd_amt,
aphd.invoice_dist_amount ,
NVL(xdl.unrounded_entered_cr,0) - NVL(xdl.unrounded_entered_dr,0) xdl_ent_amt
FROM ap_payment_hist_dists aphd,
ap_payment_history_all aph,
xla_ae_headers xh,
ap_system_parameters_all asp,
xla_distribution_links xdl
WHERE aphd.payment_history_id = aph.payment_history_id
AND aph.related_event_id = xh.event_id
AND xh.application_id = 200
AND aphd.accounting_event_id = p_xla_event_rec.event_id --event_id of cancellation_event
AND aph.org_id = asp.org_id
AND xh.ledger_id = asp.set_of_books_id
AND xh.upg_batch_id > 0
AND xh.ae_header_id = xdl.ae_header_id
AND xdl.application_id = xh.application_id
AND xdl.event_id = xh.event_id
AND xdl.source_distribution_type = 'AP_PMT_DIST'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = aphd.REVERSED_PAY_HIST_DIST_ID
AND (xdl.accounting_line_code LIKE 'AP%LIAB%' or xdl.accounting_line_code LIKE 'AP%ITEM%EXP%')
AND (aphd.invoice_dist_base_amount IS NULL or aphd.invoice_dist_amount IS NULL)) src
ON (aphd.payment_hist_dist_id = src.payment_hist_dist_id)
WHEN MATCHED
THEN
UPDATE
SET aphd.invoice_dist_base_amount = src.xdl_acctd_amt,
aphd.invoice_dist_amount = src.xdl_ent_amt ;
END IF; --Update End 11721100
END Upg_Dist_Links_Insert;