[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_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_Prepay_Dist_Pkg.Update_Gain_Loss_Ind<- ' ||
p_calling_sequence;
SELECT aph1.Gain_Loss_Indicator
INTO l_gain_loss_indicator_parent
FROM AP_Prepay_History_All aph1,
AP_Prepay_History_All APH
WHERE aph1.invoice_id=aph.invoice_id
AND aph1.accounting_event_id = aph.related_prepay_app_event_id
AND aph.accounting_event_id = p_xla_event_rec.event_id
AND rownum=1;
SELECT 'Y'
INTO l_reversal_adj
FROM dual
WHERE EXISTS
(SELECT 1
FROM ap_prepay_history_all apph,
ap_prepay_app_dists apad,
ap_prepay_app_dists apad_rel,
ap_prepay_history_all apph_rel
WHERE apph.accounting_event_id = P_XLA_Event_Rec.Event_ID
AND apph.prepay_history_id = apad.prepay_history_id
AND apad.reversed_prepay_app_dist_id = apad_rel.prepay_app_dist_id
AND apad_rel.prepay_history_id = apph_rel.prepay_history_id
AND apph_rel.accounting_event_id = apph.related_prepay_app_event_id);
UPDATE AP_Prepay_History_All APH
SET Gain_Loss_Indicator =
(SELECT DECODE(APH.Transaction_Type, 'PREPAYMENT APPLIED',
DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
-1, 'G', 1, 'L', NULL),
'PREPAYMENT UNAPPLIED',
DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
1, 'G', -1, 'L', NULL),
'PREPAYMENT APPLICATION ADJ',
DECODE(SIGN(SUM(APAD.Base_Amount - APAD.Base_Amt_At_Prepay_XRate)),
-1, 'G', 1, 'L',
0, l_gain_loss_indicator_parent)) -- bug9175969
FROM AP_Prepay_App_Dists APAD,
AP_System_Parameters_ALL ASP
WHERE ASP.Org_ID = APH.Org_ID
AND APAD.Prepay_History_ID = APH.Prepay_History_ID
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD.PREPAY_DIST_LOOKUP_CODE NOT IN ('FINAL PAYMENT ROUNDING',
'FINAL APPL ROUNDING') -- bug9716573
AND NOT (NVL(ASP.INVRATE_FOR_PREPAY_TAX, 'N') = 'Y' AND -- bug11651946
APAD.Prepay_Dist_Lookup_Code LIKE '%TAX%')
)
WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
SELECT aph.gain_loss_indicator
INTO l_gain_loss_indicator
FROM ap_prepay_history_all aph
WHERE APH.Accounting_Event_ID = p_xla_event_rec.event_id;
END Update_Gain_Loss_Ind;
PROCEDURE Prepay_Hist_Insert
(P_Invoice_ID IN NUMBER
,P_Calling_Sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Hist_Insert';
SELECT AIL.Line_Number,
AIL.Amount Amount,
AIL.Prepay_Invoice_ID,
AIL.Prepay_Line_Number,
AID.Accounting_Event_Id,
AIL.Org_ID,
AID.Accounting_Date,
-- 6718967
DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2) Group_Number
FROM AP_Invoice_Lines_ALL AIL,
AP_Invoice_Distributions_All AID
WHERE AIL.Invoice_ID = p_invoice_id
AND AIL.Line_Type_Lookup_Code = 'PREPAY'
AND AIL.Invoice_ID = AID.Invoice_ID
AND AIL.Line_Number = AID.Invoice_Line_Number
--AND AID.Accounting_Event_ID IS NULL
AND nvl(AID.posted_flag, 'N') <> 'Y'
AND nvl(AID.encumbered_flag, 'N') <> 'Y'
GROUP BY AIL.Invoice_ID, AIL.Line_Number, AIL.Amount, AIL.Prepay_Invoice_ID,
AIL.Prepay_Line_Number, AIL.Org_ID, AID.Accounting_Date,
AID.Accounting_Event_Id,
-- 6718967
DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2)
UNION
SELECT AID.Invoice_Line_Number,
SUM(AID.Amount) Amount,
AIL1.Invoice_ID,
AIL1.Line_Number,
AID.Accounting_Event_Id,
AIL1.Org_ID,
AID.Accounting_Date,
-- 6718967
DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2) Group_Number
FROM AP_Invoice_Lines AIL,
AP_Invoice_Distributions AID,
AP_Invoice_Lines AIL1,
AP_Invoice_Distributions AID1
WHERE AID.Invoice_ID = p_invoice_id
AND AID.Line_Type_Lookup_Code = 'PREPAY'
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
AND AIL.Line_Type_Lookup_Code <> 'PREPAY'
--AND AID.Accounting_Event_ID IS NULL
AND NVL(AID.posted_flag, 'N') <> 'Y'
AND NVL(AID.encumbered_flag, 'N') <> 'Y'
AND AID.Prepay_Distribution_ID = AID1.Invoice_Distribution_ID
AND AIL1.Invoice_ID = AID1.Invoice_ID
AND AIL1.Line_Number = AID1.Invoice_Line_Number
GROUP BY AIL1.Invoice_ID, AIL1.Line_Number, AIL1.Org_ID,
AID.Invoice_Line_Number, AID.Accounting_Date,
AID.Accounting_Event_Id,
-- 6718967
DECODE(NVL(AID.Parent_Reversal_ID,-99), -99, 1, 2);
' -> AP_ACCTG_PREPAY_DISTS_PKG.PREPAY_HIST_INSERT';
SELECT min(accounting_Event_id)
INTO l_related_prepay_app_event_id
FROM AP_INVOICE_DISTRIBUTIONS AID
WHERE AID.line_type_lookup_code = 'PREPAY'
AND nvl(posted_flag,'N') = 'Y'
AND nvl(AID.amount,0) < 0
AND AID.invoice_id = P_invoice_id
AND AID.invoice_line_number = l_invoice_line_number;
INSERT INTO AP_PREPAY_HISTORY_ALL
(PREPAY_HISTORY_ID
,PREPAY_INVOICE_ID
,PREPAY_LINE_NUM
,ACCOUNTING_EVENT_ID
,HISTORICAL_FLAG
,INVOICE_ID
,INVOICE_LINE_NUMBER
,ACCOUNTING_DATE
,INVOICE_ADJUSTMENT_EVENT_ID
,ORG_ID
,POSTED_FLAG
,RELATED_PREPAY_APP_EVENT_ID
,TRANSACTION_TYPE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID)
VALUES (AP_PREPAY_HISTORY_S.nextval
,l_prepay_invoice_id
,l_prepay_line_number
,l_accounting_event_id --bug9038462
,'N'
,p_invoice_id
,l_invoice_line_number
,l_accounting_date
,NULL
,l_org_id
,'N'
,l_related_prepay_app_event_id
,l_transaction_type
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.login_id
,FND_GLOBAL.user_id
,sysdate
,null
,null
,null
,null);
END Prepay_Hist_Insert;
SELECT SUM(amount), count(1)
FROM ap_invoice_distributions_all aid,
xla_events evnt,
xla_ae_headers xah,
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') -- 12731687
AND evnt.event_id = xah.event_id
AND xah.upg_batch_id IS NOT NULL
AND aid.org_id = asp.org_id
AND asp.automatic_offsets_flag = 'N'
AND aid.historical_flag = 'Y'
AND evnt.application_id=200;
delete_hist_dists(P_Invoice_ID,
l_curr_calling_sequence);
l_log_msg := 'calling procedure Prepay_Hist_Insert ';
/* Bug 4996808. Inserting into the prepayment history table */
Prepay_Hist_Insert (P_Invoice_ID,
l_curr_calling_sequence);
SELECT APPH.Prepay_History_ID,
APPH.Accounting_Event_ID,
APPH.Posted_Flag,
NVL(APPH.Historical_Flag, 'N') Historical_Flag,
XAH.upg_batch_id
INTO l_prepay_hist_id,
l_accounting_event_id,
l_posted_flag,
l_historical_flag,
l_upg_batch_id
FROM ap_prepay_history_all APPH,
xla_ae_headers XAH,
ap_system_parameters_all ASP
WHERE APPH.Invoice_ID = P_Invoice_ID
AND APPH.accounting_event_id = l_prepay_hist_rec.related_prepay_app_event_id
AND XAH.application_id = 200
AND XAH.event_id = APPH.accounting_event_id
AND ASP.org_id = APPH.org_id
AND ASP.set_of_books_id = XAH.ledger_id;
l_log_msg := 'Proceeding to call the Upg_Dist_Links_Insert procedure';
Upg_Dist_Links_Insert
(P_Invoice_ID,
l_prepay_hist_id,
l_accounting_event_id,
l_curr_calling_sequence);
l_log_msg := 'Upg_Dist_Links_Insert encountered exception '||SQLERRM;
SELECT DISTINCT ac.payment_type_flag
INTO l_payment_type_flag
FROM ap_checks_all ac,
ap_invoice_payments_all aip
WHERE ac.check_id = aip.check_id
AND aip.invoice_id = l_prepay_hist_rec.prepay_invoice_id
AND rownum < 2;
SELECT SUM(NVL(AID.Amount,0)),
SUM(DECODE(aid.line_type_lookup_code, 'AWT', 0, NVL(AID.Amount,0) ) ),
SUM(DECODE(aid.line_type_lookup_code, 'AWT', NVL(AID.Amount,0),0 ) )
INTO G_Total_Dist_amount,
G_Total_Inv_amount,
G_Total_awt_amount --Bug9106549
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = p_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
AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
AND NOT EXISTS (SELECT 1 --bug fix 6909150
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND application_id = 200
AND event_type_code IN ('INVOICE CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
SELECT asp.base_currency_code
INTO ap_accounting_pay_pkg.g_base_currency_code
FROM ap_system_parameters_all asp,
ap_invoices_all ai
WHERE asp.org_id = ai.org_id
AND ai.invoice_id = l_inv_rec.invoice_id;
SELECT APH.Prepay_History_ID,
APH.Prepay_Invoice_ID,
APH.Invoice_ID,
APH.Invoice_Line_Number,
APH.Transaction_Type,
APH.Accounting_Date,
APH.Invoice_Adjustment_Event_ID,
APH.Related_Prepay_App_Event_ID
FROM AP_Prepay_History_All APH
WHERE APH.Invoice_ID = P_Invoice_ID
AND APH.Accounting_Event_ID = P_Event_ID;
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_Prepay_App_Dists APAD,
Financials_System_Params_All FSP
WHERE AID.Invoice_ID = P_Invoice_ID
AND NVL(AID.Reversal_Flag,'N') <> 'Y'
AND NVL(AID.Accounting_Event_ID,-99) <> P_Event_ID
AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND FSP.Org_ID = AID.Org_ID
AND ((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'N'
AND AID.Match_Status_Flag IN ('T','A'))
OR
((NVL(FSP.Purch_Encumbrance_Flag,'N') = 'Y'
AND AID.Match_Status_Flag = 'A')));
(SELECT AID.Invoice_ID,
AID.Invoice_Distribution_ID,
AID.Line_Type_Lookup_Code,
AID.Amount,
AID.Base_Amount,
AID.Accounting_Event_ID,
AID.Prepay_Distribution_ID,
AID.Prepay_Tax_Diff_Amount,
AID.Parent_Reversal_ID
FROM AP_Invoice_Distributions_All AID
WHERE Accounting_Event_ID = P_Event_ID
AND EXISTS (SELECT 'Prepay History'
FROM AP_Prepay_History_All APH,
AP_Invoice_Distributions_All AID1
WHERE APH.Prepay_History_ID = P_Prepay_History_ID
AND AID1.Invoice_Distribution_ID = AID.Prepay_Distribution_ID
AND AID1.Invoice_ID = APH.Prepay_Invoice_ID
AND AID1.Invoice_Line_Number = APH.Prepay_Line_Num)
UNION ALL
SELECT AID.Invoice_ID,
AID.Invoice_Distribution_ID,
AID.Line_Type_Lookup_Code,
AID.Amount,
AID.Base_Amount,
AID.Accounting_Event_ID,
AID.Prepay_Distribution_ID,
AID.Prepay_Tax_Diff_Amount,
AID.Parent_Reversal_ID
FROM AP_Invoice_Distributions_All AID
WHERE Line_Type_Lookup_Code IN ( 'NONREC_TAX','REC_TAX')
AND Accounting_Event_ID = P_Event_ID
AND Charge_Applicable_To_Dist_ID IN
(SELECT AID1.Invoice_Distribution_ID
FROM AP_Invoice_Distributions_All AID1
WHERE Line_Type_Lookup_Code = 'PREPAY'
AND Accounting_Event_ID = P_Event_ID
AND EXISTS (SELECT 'Prepay History'
FROM AP_Prepay_History_All APH,
AP_Invoice_Distributions_All AID2
WHERE APH.Prepay_History_ID = P_Prepay_History_ID
AND AID2.Invoice_Distribution_ID = AID1.Prepay_Distribution_ID
AND AID2.Invoice_ID = APH.Prepay_Invoice_ID
AND AID2.Invoice_Line_Number = APH.Prepay_Line_Num)));
SELECT SUM(NVL(AID.Amount,0)),
SUM(DECODE(aid.line_type_lookup_code, 'AWT', 0, NVL(AID.Amount,0) ) )
INTO G_Total_Dist_amount,
G_Total_Inv_amount
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = l_prepay_hist_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
AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
AND NOT EXISTS (SELECT 1 --bug fix 6909150
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND application_id = 200
AND event_type_code IN ('INVOICE CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
SELECT count(*)
INTO l_prepay_dist_cnt
FROM ap_prepay_app_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_prepay_hist_rec.invoice_adjustment_event_id;
SELECT SUM(NVL(AID.Amount,0)),
SUM(DECODE(aid.line_type_lookup_code, 'AWT', 0, NVL(AID.Amount,0) ) )
INTO G_Total_Dist_amount,
G_Total_Inv_amount
FROM AP_Invoice_Distributions_All AID
WHERE AID.Invoice_ID = l_inv_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
AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
AND NOT EXISTS (SELECT 1 --bug fix 6909150
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND application_id = 200
AND event_type_code IN ('INVOICE CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
SELECT MAX(accounting_event_id) into l_rounding_adjust_id --8201141
FROM ap_prepay_history_all apph
WHERE transaction_type = 'PREPAYMENT APPLICATION ADJ'
AND posted_flag <> 'Y'
AND prepay_invoice_id = l_prepay_hist_rec.prepay_invoice_id
AND invoice_id = l_prepay_hist_rec.invoice_id
/* bug12858105 - start */
AND EXISTS (SELECT 1
FROM AP_Prepay_App_Dists APAD2
WHERE 1=1
AND APAD2.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL',
'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad2_rev,
ap_prepay_history_all apph_rev
WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
AND apad2_rev.prepay_history_id = apph_rev.prepay_history_id
AND apph_rev.invoice_id = apph.invoice_id)
)
/* bug12858105 - end */
;
SELECT /*+ leading(aid) */ SUM(DECODE(APAD.Prepay_Dist_Lookup_Code, 'PREPAY APPL', APAD.Amount,
'PREPAY APPL REC TAX', APAD.Amount,
'PREPAY APPL NONREC TAX', APAD.Amount, 0)),
SUM(DECODE(APAD.Prepay_Dist_Lookup_Code, 'TAX DIFF', APAD.Amount, 0))
INTO l_sum_prepaid_amount,
l_sum_tax_diff_amount
FROM AP_Prepay_App_Dists APAD,
ap_invoice_distributions_all aid
WHERE APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND apad.invoice_distribution_id = aid.invoice_distribution_id
AND aid.invoice_id = l_prepay_dist_rec.invoice_id;
UPDATE AP_Prepay_App_Dists APAD
SET APAD.Amount = APAD.Amount - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
APAD.BASE_AMT_AT_PREPAY_XRATE = APAD.BASE_AMT_AT_PREPAY_XRATE - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
APAD.BASE_AMT_AT_PREPAY_PAY_XRATE=APAD.BASE_AMT_AT_PREPAY_PAY_XRATE - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
APAD.BASE_AMOUNT=APAD.BASE_AMOUNT - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount,
APAD.BASE_AMT_AT_PREPAY_CLR_XRATE=APAD.BASE_AMT_AT_PREPAY_CLR_XRATE - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount, rounding_amt = nvl(l_sum_prepaid_amount, 0) + l_prepay_dist_rec.amount
WHERE APAD.Invoice_Distribution_ID =
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
AND ABS(APAD1.Amount) =
(SELECT MAX(ABS(APAD2.Amount)) -- added ABS for bug12858105
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD2.Prepay_App_Distribution_ID
= l_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
/* bug12858105 - start */
AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad2_rev,
ap_prepay_history_all apph
WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
AND apad2_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
/* bug12858105 - end */
)
/* bug12858105 - start */
AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad1_rev,
ap_prepay_history_all apph
WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
AND apad1_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
/* bug12858105 - end */
)
AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
UPDATE AP_Prepay_App_Dists APAD
SET APAD.Amount = APAD.Amount - NVL(l_sum_prepaid_amount,0) + l_prepay_dist_rec.amount
WHERE APAD.Invoice_Distribution_ID =
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
AND ABS(APAD1.Amount) =
(SELECT MAX(ABS(APAD2.Amount)) -- adding ABS for bug12858105
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD2.Prepay_App_Distribution_ID
= l_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
/* bug12858105 - start */
AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad2_rev,
ap_prepay_history_all apph
WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
AND apad2_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
/* bug12858105 - end */
)
/* bug12858105 - start */
AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad1_rev,
ap_prepay_history_all apph
WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
AND apad1_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
/* bug12858105 - end */
)
AND APAD.Prepay_Dist_Lookup_Code IN ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
UPDATE AP_Prepay_App_Dists APAD
SET APAD.Amount = APAD.Amount - NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount
WHERE APAD.Invoice_Distribution_ID =
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_Dist_Lookup_Code = 'TAX DIFF'
AND ABS(APAD1.Amount) =
(SELECT MAX(APAD2.Amount)
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD2.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_Dist_Lookup_Code = 'TAX DIFF'))
AND APAD.Prepay_Dist_Lookup_Code = 'TAX DIFF'
AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id; */
UPDATE AP_Prepay_App_Dists APAD
SET APAD.Amount = APAD.Amount- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount ,
APAD.BASE_AMT_AT_PREPAY_XRATE = APAD.BASE_AMT_AT_PREPAY_XRATE
- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount,
APAD.BASE_AMT_AT_PREPAY_PAY_XRATE=APAD.BASE_AMT_AT_PREPAY_PAY_XRATE
- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount,
APAD.BASE_AMOUNT=APAD.BASE_AMOUNT
- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount,
APAD.BASE_AMT_AT_PREPAY_CLR_XRATE=APAD.BASE_AMT_AT_PREPAY_CLR_XRATE
- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount
WHERE APAD.Invoice_Distribution_ID =
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
AND ABS(APAD1.Amount) =
(SELECT MAX(ABS(APAD2.Amount))
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD2.Prepay_App_Distribution_ID
= l_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad2_rev,
ap_prepay_history_all apph
WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
AND apad2_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
)
AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad1_rev,
ap_prepay_history_all apph
WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
AND apad1_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
)
AND APAD.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
UPDATE AP_Prepay_App_Dists APAD
SET APAD.Amount = APAD.Amount- NVL(l_sum_tax_diff_amount,0) + l_prepay_dist_rec.prepay_tax_diff_amount
WHERE APAD.Invoice_Distribution_ID =
(SELECT MAX(APAD1.Invoice_Distribution_ID)
FROM AP_Prepay_App_Dists APAD1
WHERE APAD1.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD1.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id
AND APAD1.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
AND ABS(APAD1.Amount) =
(SELECT MAX(ABS(APAD2.Amount))
FROM AP_Prepay_App_Dists APAD2
WHERE APAD2.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD2.Prepay_App_Distribution_ID
= l_prepay_dist_rec.invoice_distribution_id
AND APAD2.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
AND APAD2.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad2_rev,
ap_prepay_history_all apph
WHERE apad2_rev.reversed_prepay_app_dist_id = APAD2.prepay_app_dist_id
AND apad2_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
)
AND APAD1.Reversed_Prepay_App_Dist_Id IS NULL
AND NOT EXISTS (SELECT 1
FROM ap_prepay_app_dists apad1_rev,
ap_prepay_history_all apph
WHERE apad1_rev.reversed_prepay_app_dist_id = APAD1.prepay_app_dist_id
AND apad1_rev.prepay_history_id = apph.prepay_history_id
AND apph.invoice_id = p_xla_event_rec.source_id_int_1)
)
AND APAD.Prepay_Dist_Lookup_Code IN ('TAX DIFF')
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id
AND APAD.Prepay_App_Distribution_ID = l_prepay_dist_rec.invoice_distribution_id;
l_log_msg := 'Calling procedure AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind';
AP_Acctg_Prepay_Dist_Pkg.Update_Gain_Loss_Ind
(p_xla_event_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure AP_Acctg_Prepay_Dist_Pkg.Updated_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_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
AND NVL(AID.Cancellation_Flag,'N') <> 'Y' -- BUG 6513956
--bug fix 6909150
AND NOT EXISTS (SELECT 1
FROM xla_events
WHERE event_id = AID.accounting_event_id
AND event_type_code IN ('INVOICE CANCELLED',
'CREDIT MEMO CANCELLED',
'DEBIT MEMO CANCELLED'));
/*SELECT SUM(apad.amount) INTO l_awt_prorated_amt
FROM ap_prepay_app_dists apad
WHERE apad.prepay_dist_lookup_code = 'AWT'
AND apad.awt_related_id = p_inv_dist_rec.invoice_distribution_id
AND apad.invoice_distribution_id in
(SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_inv_rec.invoice_id
AND line_type_lookup_code = 'AWT');
SELECT p_inv_dist_rec.amount
/ l_total_inv_amount
* (p_prepay_dist_rec.amount
- (
l_total_awt_amount / l_total_dist_amount * p_prepay_dist_rec.amount
)
)
+
nvl(
(select sum(amount) / l_total_dist_amount * p_prepay_dist_rec.amount
from ap_invoice_distributions_all aid
where aid.invoice_id=p_inv_rec.invoice_id
and aid.awt_invoice_payment_id is null
and aid.awt_related_id=p_inv_dist_rec.invoice_distribution_id
), 0)
INTO l_prorated_amount
from sys.dual ;
l_log_msg := 'Calling procedure Prepay_Dist_Insert';
Prepay_Dist_Insert
(l_pad_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Prepay_Dist_Insert executed';
l_log_msg := 'Procedure Prepay_Dist_Insert executed';
l_log_msg := 'Calling procedure Prepay_Dist_Insert';
Prepay_Dist_Insert
(l_pad_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Prepay_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 Prepay_Dist_Insert';
Prepay_Dist_Insert
(l_pad_rec,
l_curr_calling_sequence);
l_log_msg := 'Procedure Prepay_Dist_Insert executed';
INSERT INTO AP_Prepay_App_Dists
(Prepay_App_Dist_ID,
Prepay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Prepay_App_Distribution_ID,
Accounting_Event_ID,
Prepay_History_ID,
Prepay_Exchange_Date,
Prepay_Pay_Exchange_Date,
Prepay_Clr_Exchange_Date,
Prepay_Exchange_Rate,
Prepay_Pay_Exchange_Rate,
Prepay_Clr_Exchange_Rate,
Prepay_Exchange_Rate_Type,
Prepay_Pay_Exchange_Rate_Type,
Prepay_Clr_Exchange_Rate_Type,
Reversed_Prepay_App_Dist_ID,
Amount,
Base_Amt_At_Prepay_XRate,
Base_Amt_At_Prepay_Pay_XRate,
Base_Amt_At_Prepay_Clr_XRate,
Base_Amount,
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_Update_Date,
Request_ID
)
SELECT AP_Prepay_App_Dists_S.nextval,
APAD.Prepay_Dist_Lookup_Code,
APAD.Invoice_Distribution_ID,
p_prepay_inv_dist_id,
xer.event_id, --p_xla_event_rec.event_id,
p_prepay_hist_rec.prepay_history_id,
APAD.Prepay_Exchange_Date,
APAD.Prepay_Pay_Exchange_Date,
APAD.Prepay_Clr_Exchange_Date,
APAD.Prepay_Exchange_Rate,
APAD.Prepay_Pay_Exchange_Rate,
APAD.Prepay_Clr_Exchange_Rate,
APAD.Prepay_Exchange_Rate_Type,
APAD.Prepay_Pay_Exchange_Rate_Type,
APAD.Prepay_Clr_Exchange_Rate_Type,
APAD.Prepay_App_Dist_ID,
-1 * APAD.Amount,
-1 * APAD.Base_Amt_At_Prepay_XRate,
-1 * APAD.Base_Amt_At_Prepay_Pay_XRate,
-1 * APAD.Base_Amt_At_Prepay_Clr_XRate,
-1 * APAD.Base_Amount,
APAD.AWT_Related_ID,
'N',
APAD.Quantity_Variance,
APAD.Invoice_Base_Qty_Variance,
APAD.Amount_Variance,
APAD.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,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
FROM AP_Prepay_App_Dists APAD,
ap_prepay_history_all aph, --Bug 9112240
ap_prepay_history_all aphr,
xla_events xer
WHERE apad.Prepay_App_Distribution_ID = P_Prepay_Reversal_ID
AND apad.prepay_history_id = aph.prepay_history_id --Bug 9112240
AND aphr.prepay_history_id = p_prepay_hist_rec.prepay_history_id
AND aphr.accounting_event_id = xer.event_id(+)
AND xer.application_id(+) = 200;
INSERT INTO AP_Prepay_App_Dists
(Prepay_App_Dist_ID,
Prepay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Prepay_App_Distribution_ID,
Accounting_Event_ID,
Prepay_History_ID,
Prepay_Exchange_Date,
Prepay_Pay_Exchange_Date,
Prepay_Clr_Exchange_Date,
Prepay_Exchange_Rate,
Prepay_Pay_Exchange_Rate,
Prepay_Clr_Exchange_Rate,
Prepay_Exchange_Rate_Type,
Prepay_Pay_Exchange_Rate_Type,
Prepay_Clr_Exchange_Rate_Type,
Reversed_Prepay_App_Dist_ID,
Amount,
Base_Amt_At_Prepay_XRate,
Base_Amt_At_Prepay_Pay_XRate,
Base_Amt_At_Prepay_Clr_XRate,
Base_Amount,
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_Update_Date,
Request_ID
)
SELECT AP_Prepay_App_Dists_S.nextval,
APAD.Prepay_Dist_Lookup_Code,
p_inv_dist_id, -- Bug 7134020
APAD.Prepay_App_Distribution_ID,
p_xla_event_rec.event_id,
p_prepay_hist_rec.prepay_history_id,
APAD.Prepay_Exchange_Date,
APAD.Prepay_Pay_Exchange_Date,
APAD.Prepay_Clr_Exchange_Date,
APAD.Prepay_Exchange_Rate,
APAD.Prepay_Pay_Exchange_Rate,
APAD.Prepay_Clr_Exchange_Rate,
APAD.Prepay_Exchange_Rate_Type,
APAD.Prepay_Pay_Exchange_Rate_Type,
APAD.Prepay_Clr_Exchange_Rate_Type,
APAD.Prepay_App_Dist_ID,
-1 * APAD.Amount,
-1 * APAD.Base_Amt_At_Prepay_XRate,
-1 * APAD.Base_Amt_At_Prepay_Pay_XRate,
-1 * APAD.Base_Amt_At_Prepay_Clr_XRate,
-1 * APAD.Base_Amount,
APAD.AWT_Related_ID,
'N',
APAD.Quantity_Variance,
APAD.Invoice_Base_Qty_Variance,
APAD.Amount_Variance,
APAD.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,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
FROM AP_Prepay_App_Dists APAD,
ap_prepay_history_all aph --Bug 9112240
WHERE apad.prepay_history_id = aph.prepay_history_id --Bug 9112240
AND APAD.Prepay_App_Distribution_ID = nvl(p_prepay_inv_dist_id,APAD.Prepay_App_Distribution_ID) --7686421
AND APAD.Invoice_Distribution_Id = p_inv_reversal_id --bug9440073
/*AND APAD.Accounting_Event_Id = p_prepay_hist_rec.related_prepay_app_event_id; --bug9440073 */
PROCEDURE Prepay_Dist_Insert
(P_PAD_Rec IN AP_PREPAY_APP_DISTS%ROWTYPE
,P_Calling_Sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Prepay_Dist_Insert';
l_curr_calling_sequence := 'AP_ACCTG_PREPAY_DIST_PKG.Prepay_Dist_Insert<- ' ||
P_Calling_Sequence;
INSERT INTO AP_Prepay_App_Dists
(Prepay_App_Dist_ID,
Prepay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Prepay_App_Distribution_ID,
Accounting_Event_ID,
Prepay_History_ID,
Prepay_Exchange_Date,
Prepay_Pay_Exchange_Date,
Prepay_Clr_Exchange_Date,
Prepay_Exchange_Rate,
Prepay_Pay_Exchange_Rate,
Prepay_Clr_Exchange_Rate,
Prepay_Exchange_Rate_Type,
Prepay_Pay_Exchange_Rate_Type,
Prepay_Clr_Exchange_Rate_Type,
Reversed_Prepay_App_Dist_ID,
Amount,
Base_Amt_At_Prepay_XRate,
Base_Amt_At_Prepay_Pay_XRate,
Base_Amt_At_Prepay_Clr_XRate,
Base_Amount,
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_Update_Date,
Request_ID
)
VALUES (AP_Prepay_App_Dists_S.nextval,
P_PAD_Rec.Prepay_Dist_Lookup_Code,
P_PAD_Rec.Invoice_Distribution_ID,
P_PAD_Rec.Prepay_App_Distribution_ID,
P_PAD_Rec.Accounting_Event_ID,
P_PAD_Rec.Prepay_History_ID,
P_PAD_Rec.Prepay_Exchange_Date,
P_PAD_Rec.Prepay_Pay_Exchange_Date,
P_PAD_Rec.Prepay_Clr_Exchange_Date,
P_PAD_Rec.Prepay_Exchange_Rate,
P_PAD_Rec.Prepay_Pay_Exchange_Rate,
P_PAD_Rec.Prepay_Clr_Exchange_Rate,
P_PAD_Rec.Prepay_Exchange_Rate_Type,
P_PAD_Rec.Prepay_Pay_Exchange_Rate_Type,
P_PAD_Rec.Prepay_Clr_Exchange_Rate_Type,
P_PAD_Rec.Reversed_Prepay_App_Dist_ID,
P_PAD_Rec.Amount,
P_PAD_Rec.Base_Amt_At_Prepay_XRate,
P_PAD_Rec.Base_Amt_At_Prepay_Pay_XRate,
P_PAD_Rec.Base_Amt_At_Prepay_Clr_XRate,
P_PAD_Rec.Base_Amount,
P_PAD_Rec.AWT_Related_ID,
'N',
P_PAD_Rec.Quantity_Variance,
P_PAD_Rec.Invoice_Base_Qty_Variance,
P_PAD_Rec.Amount_Variance,
P_PAD_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,
SYSDATE,
FND_GLOBAL.Conc_Request_ID
);
END Prepay_Dist_Insert;
PROCEDURE Delete_Hist_Dists
(P_invoice_id IN NUMBER,
P_Calling_Sequence IN VARCHAR2
) IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Delete_Hist_Dists';
l_curr_calling_sequence := 'AP_Acctg_Prepay_Dist_Pkg.Delete_hist_dists<- ' ||
p_calling_sequence;
DELETE FROM ap_prepay_app_dists apad1
WHERE apad1.prepay_history_id IN
(SELECT apph.prepay_history_id
FROM ap_prepay_history_all apph
WHERE nvl(apph.posted_flag, 'N') <> 'Y'
AND apph.invoice_id = p_invoice_id
AND apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ' --bug9973070
AND NOT EXISTS
(SELECT /*+ no_unnest */ 1 --bug12337556
FROM ap_prepay_app_dists apad,
ap_invoice_distributions_all aid
WHERE apad.prepay_history_id = apph.prepay_history_id
AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
AND (aid.posted_flag = 'Y' OR aid.encumbered_flag = 'Y')));
DELETE FROM ap_prepay_history_all apph1
WHERE apph1.prepay_history_id IN
(SELECT apph.prepay_history_id
FROM ap_prepay_history_all apph
WHERE nvl(apph.posted_flag, 'N') <> 'Y'
AND apph.invoice_id = p_invoice_id
AND apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ' --bug9973070
AND NOT EXISTS
(SELECT /*+ no_unnest */ 1 --bug12337556
FROM ap_prepay_app_dists apad,
ap_invoice_distributions_all aid
WHERE apad.prepay_history_id = apph.prepay_history_id
AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
AND (aid.posted_flag = 'Y' OR aid.encumbered_flag = 'Y')));
END Delete_Hist_Dists;
PROCEDURE Upg_Dist_Links_Insert
(P_Invoice_ID IN NUMBER
,p_prepay_history_id IN NUMBER
,p_accounting_event_id IN NUMBER
,p_calling_sequence IN VARCHAR2
) IS
l_rowcount NUMBER;
l_procedure_name CONSTANT VARCHAR2(30) := 'Upg_Dist_Links_Insert';
l_curr_calling_sequence := 'AP_ACCTG_PREPAY_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_prepay_history_all aph
WHERE aeh.event_id = aph.accounting_event_id
AND aph.accounting_event_id = p_accounting_event_id
AND aph.invoice_id = p_invoice_id
AND aph.historical_flag = 'Y'
AND aeh.upg_batch_id IS NOT NULL
AND aeh.upg_batch_id <> -9999)
AND upg_batch_id IS NOT NULL
AND upg_batch_id <> -9999;
l_log_msg := ' Number of XLA distribution LInks Deleted :'||l_rowcount||
' Now 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,
STATISTICAL_AMOUNT,
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,
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_ID,
APPLIED_TO_DIST_ID_NUM_1,
GAIN_OR_LOSS_REF )
SELECT Application_ID,
Accounting_Event_ID,
AE_Header_ID,
AE_Line_Num,
Source_Distribution_Type,
Source_Distribution_ID_Num_1,
NULL Statistical_Amount,
(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,
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,
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_ID,
APPLIED_TO_DIST_ID_NUM_1,
GAIN_OR_LOSS_REF
FROM
(SELECT Application_ID,
Accounting_Event_ID,
AE_Header_ID,
AE_Line_Num,
Source_Distribution_Type,
Source_Distribution_ID_Num_1,
Statistical_Amount,
Accounting_Line_Code,
Accounting_Line_Type_Code,
Merge_Duplicate_Code,
Line_Entered_Cr,
Line_Entered_Dr,
Line_Accounted_Cr,
Line_Accounted_Dr,
Line_Entered_Amt,
Line_Accounted_Amt,
Dist_Count,
Ref_AE_Header_ID,
Temp_Line_Num,
Ref_Event_ID,
Upg_Batch_ID,
Line_Definition_Owner_Code,
Line_Definition_Code,
Event_Class_Code,
Event_Type_Code,
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_ID,
APPLIED_TO_DIST_ID_NUM_1,
GAIN_OR_LOSS_REF,
Rank_Num,
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,
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 Invoice_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 Invoice_Id, Part_Key1, Part_Key2, AE_Line_Num) Sum_Entered_Amt
FROM( /*Bug 10016633 Added another wrapper query*/
SELECT Application_ID,
Invoice_Id,
Base_Currency_Code,
Accounting_Event_ID,
AE_Header_ID,
AE_Line_Num,
Source_Distribution_Type,
Source_Distribution_ID_Num_1,
Statistical_Amount,
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,
Line_Entered_Cr,
Line_Entered_Dr,
Line_Accounted_Cr,
Line_Accounted_Dr,
Line_Entered_Amt,
Line_Accounted_Amt,
Dist_Amount,
Dist_Base_Amount,
Dist_Count,
PDivisor_Ent_Amt,
PDivisor_Acct_Amt,
Part_Key1,
Part_Key2,
/*Bug10016633 Moved temp_line_num logic here
and using ROW_NUMBER() instead of RANK()*/
ROW_NUMBER() OVER (PARTITION BY Invoice_ID,
AE_Header_Id
ORDER BY AE_Line_Num,
Invoice_Distribution_ID,
Source_Distribution_ID_Num_1,
Prepay_Dist_Lookup_Code) Temp_Line_Num,
Rank_Num,
Ref_Event_ID,
Upg_Batch_ID,
Line_Definition_Owner_Code,
Line_Definition_Code,
Event_Class_Code,
Event_Type_Code,
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_ID,
APPLIED_TO_DIST_ID_NUM_1,
GAIN_OR_LOSS_REF
FROM
(
SELECT 200 Application_ID,
AI.Invoice_Id Invoice_Id,
ASP.Base_Currency_Code Base_Currency_Code,
AEH.Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
AEL.AE_Line_Num AE_Line_Num,
'AP_PREPAY' Source_Distribution_Type,
APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
0 Statistical_Amount,
DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
AEH.AE_Header_ID Ref_AE_Header_ID,
DECODE(AEL.Accounting_Class_Code,
'GAIN', 'AP_GAIN_PREPAY_APP',
'LOSS', 'AP_LOSS_PREPAY_APP',
'LIABILITY', 'AP_LIAB_PREPAY_APP',
'PREPAID_EXPENSE','AP_PREPAID_EXP_ACCR_PREPAY_APP',
'ROUNDING', 'AP_FINAL_PMT_ROUND_PREPAY_APP',
'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP',
'ITEM EXPENSE', 'AP_ITEM_PREPAY_PAY_RATE_APP',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP',
'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
'FREIGHT', 'AP_FREIGHT_PREPAY_PAY_RATE_APP',
'AP_ITEM_PREPAY_PAY_RATE_APP')
Accounting_Line_Code,
'S' Accounting_Line_Type_Code,
'A' Merge_Duplicate_Code,
AEL.Entered_Cr Line_Entered_Cr,
AEL.Entered_Dr Line_Entered_Dr,
AEL.Accounted_Cr Line_Accounted_Cr,
AEL.Accounted_Dr Line_Accounted_Dr,
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,
AEH.AE_Header_Id,
AEL.AE_Line_Num) Dist_Count,
/* bug 12845564 - start */
SUM(AID.Amount)
OVER (PARTITION BY AI.Invoice_ID,
AEH.ae_header_id,
AEL.AE_Line_Num,
AEL.Account_Overlay_Source_ID) PDivisor_Ent_Amt,
SUM(NVL(AID.Base_Amount, AID.Amount))
OVER (PARTITION BY AI.Invoice_ID,
AEH.ae_header_id,
AEL.AE_Line_Num,
AEL.Account_Overlay_Source_ID) PDivisor_Acct_Amt,
/* bug 12845564 - end */
AI.Invoice_ID Part_Key1,
NVL(AID.old_distribution_id, AID.Invoice_Distribution_ID) Part_Key2, -- bug 12845564
RANK() OVER (PARTITION BY AI.Invoice_ID,
AEH.AE_Header_Id,
AEL.AE_Line_Num
ORDER BY AEL.AE_Line_Num,
APAD.Invoice_Distribution_ID,
APAD.Prepay_App_Distribution_ID,
APAD.Prepay_Dist_Lookup_Code) Rank_Num,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
'S' Line_Definition_Owner_Code,
'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
'INVOICES' Event_Class_Code,
'INVOICES_ALL' Event_Type_Code,
DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE.Entity_ID, null) APPLIED_TO_ENTITY_ID,
DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null) APPLIED_TO_DIST_ID_NUM_1,
'-2222' GAIN_OR_LOSS_REF,
APAD.Invoice_Distribution_ID,
APAD.Prepay_Dist_Lookup_Code
FROM AP_Invoices_All AI,
AP_System_Parameters_All ASP,
XLA_Transaction_Entities_upg XTE,
XLA_Events XLE,
AP_Prepay_App_Dists APAD,
AP_Invoice_Distributions_All AID,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL
WHERE XLE.event_id = p_accounting_event_id
AND AI.Org_Id = ASP.Org_Id
AND AI.Invoice_ID = AID.Invoice_ID
AND XTE.Application_ID = 200
AND AI.Set_Of_Books_ID = XTE.Ledger_ID
AND XTE.Entity_Code = 'AP_INVOICES'
AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
AND XTE.Entity_ID = XLE.Entity_ID
AND XLE.Application_ID = 200
AND XLE.Event_Type_Code IN ('PREPAYMENT APPLIED',
'PREPAYMENT UNAPPLIED')
AND XLE.Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND XLE.Event_ID = APAD.Accounting_Event_ID
AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AEL.Account_Overlay_Source_ID IS NOT NULL
AND AID.Old_Distribution_ID = AEL.Account_Overlay_Source_ID
UNION
SELECT 200 Application_ID,
AI.Invoice_id Invoice_Id,
ASP.Base_Currency_Code Base_Currency_Code,
AEH.Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
AEL.AE_Line_Num AE_Line_Num,
'AP_PREPAY' Source_Distribution_Type,
APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
0 Statistical_Amount,
DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
AEH.AE_Header_ID Ref_AE_Header_ID,
DECODE(AEL.Accounting_Class_Code,
'GAIN', 'AP_GAIN_PREPAY_APP',
'LOSS', 'AP_LOSS_PREPAY_APP',
'LIABILITY', 'AP_LIAB_PREPAY_APP',
'PREPAID_EXPENSE','AP_PREPAID_EXP_ACCR_PREPAY_APP',
'ROUNDING', 'AP_FINAL_PMT_ROUND_PREPAY_APP',
'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP',
'ITEM EXPENSE', 'AP_ITEM_PREPAY_PAY_RATE_APP',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP',
'NRTAX', 'AP_NRTAX_PREPAY_PAY_RATE_APP',
'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
'FREIGHT', 'AP_FREIGHT_PREPAY_PAY_RATE_APP',
'AP_ITEM_PREPAY_PAY_RATE_APP')
Accounting_Line_Code,
'S' Accounting_Line_Type_Code,
'A' Merge_Duplicate_Code,
AEL.Entered_Cr Line_Entered_Cr,
AEL.Entered_Dr Line_Entered_Dr,
AEL.Accounted_Cr Line_Accounted_Cr,
AEL.Accounted_Dr Line_Accounted_Dr,
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,
AEH.AE_Header_Id,
AEL.AE_Line_Num) Dist_Count,
SUM(AID.Amount)
OVER (PARTITION BY AI.Invoice_ID,
AEH.ae_header_id,
AEL.AE_Line_Num) PDivisor_Ent_Amt,
SUM(NVL(AID.Base_Amount, AID.Amount))
OVER (PARTITION BY AI.Invoice_ID,
AEH.ae_header_id,
AEL.AE_Line_Num) PDivisor_Acct_Amt,
AI.Invoice_ID Part_Key1,
1 Part_Key2,
RANK() OVER (PARTITION BY AI.Invoice_ID,
AEH.AE_Header_Id,
AEL.AE_Line_Num
ORDER BY AEL.AE_Line_Num,
APAD.Invoice_Distribution_ID,
APAD.Prepay_App_Distribution_ID,
APAD.Prepay_Dist_Lookup_Code) Rank_Num,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
'S' Line_Definition_Owner_Code,
'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
'INVOICES' Event_Class_Code,
'INVOICES_ALL' Event_Type_Code,
DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,200, null) APPLIED_TO_APPLICATION_ID,
DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,XTE.Entity_ID, null) APPLIED_TO_ENTITY_ID,
DECODE(AEL.Accounting_Class_Code, 'LIABILITY' ,AID.Invoice_Distribution_ID, null)
APPLIED_TO_DIST_ID_NUM_1,
'-2222' GAIN_OR_LOSS_REF,
APAD.Invoice_Distribution_ID,
APAD.Prepay_Dist_Lookup_Code
FROM AP_Invoices_All AI,
AP_System_Parameters_All ASP,
XLA_Transaction_Entities_upg XTE,
XLA_Events XLE,
AP_Prepay_App_Dists APAD,
AP_Invoice_Distributions_All AID,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL
WHERE XLE.event_id = p_accounting_event_id
AND AI.Org_Id = ASP.Org_id
AND AI.Invoice_ID = AID.Invoice_ID
AND XTE.Application_ID = 200
AND AI.Set_Of_Books_ID = XTE.Ledger_ID
AND XTE.Entity_Code = 'AP_INVOICES'
AND AI.Invoice_ID = NVL(XTE.Source_ID_Int_1,-99)
AND XTE.Entity_ID = XLE.Entity_ID
AND XLE.Application_ID = 200
AND XLE.Event_Type_Code IN ('PREPAYMENT APPLIED',
'PREPAYMENT UNAPPLIED')
AND XLE.Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND XLE.Event_ID = APAD.Accounting_Event_ID
AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AEL.Account_Overlay_Source_ID IS NULL)) v1,
Fnd_Currencies FC
WHERE FC.Currency_Code = v1.Base_Currency_Code) v2;
l_log_msg := 'Number of records Inserted in XDL '||l_rowcount;
END Upg_Dist_Links_Insert;