[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;
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))
FROM AP_Prepay_App_Dists APAD
WHERE APAD.Prepay_History_ID = APH.Prepay_History_ID
AND APAD.Accounting_Event_ID = p_xla_event_rec.event_id)
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,
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
GROUP BY AIL.Invoice_ID, AIL.Line_Number, AIL.Amount, AIL.Prepay_Invoice_ID,
AIL.Prepay_Line_Number, AIL.Org_ID, AID.Accounting_Date,
-- 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,
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 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,
-- 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
,NULL
,'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')
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 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 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 /*+ 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
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(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 ('PREPAY APPL', 'PREPAY APPL REC TAX',
'PREPAY APPL NONREC TAX')))
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;
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'));
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,
NULL, --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
WHERE Prepay_App_Distribution_ID = P_Prepay_Reversal_ID;
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
WHERE Invoice_Distribution_ID = P_Inv_Reversal_ID;
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 apad
WHERE prepay_history_id in (
select aph.prepay_history_id
from ap_prepay_history_all aph,ap_prepay_app_dists apad1
where aph.prepay_history_id=apad1.prepay_history_id
and aph.accounting_event_id is null
and (aph.bc_event_id is null or
(aph.bc_event_id is not null
and aph.invoice_id=p_invoice_id
and not exists(select 1 from ap_invoice_distributions_all aid
-- Bug fix 5634515
-- replace invoice_distribution_id with prepay_app_distribution_id
-- since the original one can only derive other invoice lines except
-- prepayment application line.
where aid.invoice_distribution_id=
apad1.prepay_app_distribution_id
--apad1.invoice_distribution_id
and nvl(aid.encumbered_flag,'N')='Y'))));
DELETE FROM AP_Prepay_history_all aph
WHERE Accounting_Event_ID is null
AND (bc_event_id is null or
(bc_event_id is not null
AND Invoice_id = p_invoice_id
AND not exists(select 1 from AP_Prepay_App_Dists apad,
ap_invoice_distributions_all aid
-- Bug fix 5634515
-- replace invoice_distribution_id with prepay_app_distribution_id
-- since the original one can only derive other invoice lines except
-- prepayment application line.
where aid.invoice_distribution_id= apad.prepay_app_distribution_id
--apad.invoice_distribution_id
and aph.prepay_history_id=apad.prepay_history_id
and nvl(encumbered_flag,'N')='Y')));
END Delete_Hist_Dists;