[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*This is a wrapper procedure for inserting rows into table
AP_INVOICE_LINES_INTERFACE.*/
PROCEDURE Invoice_Insert_Row(
X_Invoice_ID IN NUMBER,
X_Invoice_Num IN VARCHAR2,
X_Vendor_ID IN NUMBER,
X_Vendor_Site_ID IN NUMBER,
X_Invoice_Amount IN NUMBER,
X_Invoice_Currency_Code IN VARCHAR2,
X_Source IN VARCHAR2,
X_Terms_ID IN NUMBER,
X_Last_Updated_by IN NUMBER,
X_Last_Update_Date IN DATE,
X_Last_Update_Login IN NUMBER,
X_Created_by IN NUMBER,
X_Creation_Date IN DATE,
X_GL_Date IN DATE,
X_Invoice_Date IN DATE,
X_Org_ID IN NUMBER,
X_LE_ID IN NUMBER,
X_LE_Name IN VARCHAR2,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
BEGIN
INSERT INTO AP_Invoices_Interface(
Invoice_ID,
Invoice_Num,
Vendor_ID,
Vendor_Site_ID,
Invoice_Amount,
Invoice_Currency_Code,
Source,
Terms_ID,
Last_Updated_by,
Last_Update_Date,
Last_Update_Login,
Created_by,
Creation_Date,
GL_Date,
Invoice_Date,
Invoice_Received_Date,
Org_ID,
Legal_Entity_ID,
Legal_Entity_Name,
Goods_Received_Date
)
VALUES (
X_Invoice_ID,
X_Invoice_Num,
X_Vendor_ID,
X_Vendor_Site_ID,
X_Invoice_Amount,
X_Invoice_Currency_Code,
X_Source,
X_Terms_ID,
X_Last_Updated_by,
X_Last_Update_Date,
X_Last_Update_Login,
X_Created_by,
X_Creation_Date,
X_GL_Date,
X_Invoice_Date,
X_GL_Date, -- BUG# 2267117
X_Org_ID,
X_LE_ID,
X_LE_Name,
X_GL_Date -- BUG# 3900673
);
CALLING_FN => 'FA_LPITEMS_EXPT_PKG.Invoice_Insert_Row'
,p_log_level_rec => p_log_level_rec);
END Invoice_Insert_Row;
/*This is a wrapper procedure for inserting rows into table
AP_INVOICE_LINES_INTERFACE.*/
PROCEDURE Invoice_Line_Insert_Row(
X_Invoice_ID IN NUMBER,
X_Invoice_Line_ID IN NUMBER,
X_Line_Number IN NUMBER,
X_Line_Type_Lookup_Code IN VARCHAR2 := 'ITEM',
X_Amount IN NUMBER,
X_Dist_Code_Combination_ID IN NUMBER,
X_Last_Updated_by IN NUMBER,
X_Last_Update_Date IN DATE,
X_Last_Update_Login IN NUMBER,
X_Created_by IN NUMBER,
X_Creation_Date IN DATE,
X_Accounting_Date IN DATE,
X_Org_ID IN NUMBER,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
BEGIN
INSERT INTO AP_Invoice_Lines_Interface(
Invoice_ID,
Invoice_Line_ID,
Line_Number,
Line_Type_Lookup_Code,
Amount,
Dist_Code_Combination_ID,
Last_Updated_by,
Last_Update_Date,
Last_Update_Login,
Created_by,
Creation_Date,
Accounting_Date,
Org_ID)
VALUES (
X_Invoice_ID,
X_Invoice_Line_ID,
X_Line_Number,
X_Line_Type_Lookup_Code,
X_Amount,
X_Dist_Code_Combination_ID,
X_Last_Updated_by,
X_Last_Update_Date,
X_Last_Update_Login,
X_Created_by,
X_Creation_Date,
X_Accounting_Date,
X_Org_ID);
CALLING_FN => 'FA_LPITEMS_EXPT_PKG.Invoice_Line_Insert_Row'
,p_log_level_rec => p_log_level_rec);
END Invoice_Line_Insert_Row;
SELECT flpi.Lease_ID Lease_ID,
flpi.Payment_Schedule_ID Payment_Schedule_ID,
flpi.Schedule_Amort_Line_Num Amort_Line_Num,
flpi.Dist_Code_Combination_ID,
flpi.Invoice_Number Invoice_Number,
flpi.Terms_ID Terms_ID,
fas.Payment_Date Payment_Date,
fas.Payment_Amount Payment_Amount,
fl.Lease_Number Lease_Number,
fl.Currency_Code Currency_Code,
fl.Lessor_ID Lessor_ID,
flpi.Lessor_Site_ID Lessor_Site_ID,
pv.Vendor_Name Lessor_Name,
pvsa.Vendor_Site_Code Lessor_Site_Name,
pvsa.Org_ID Org_ID,
hao.Name Org_Name
FROM FA_LEASE_PAYMENT_ITEMS flpi,
FA_LEASES fl, FA_AMORT_SCHEDULES fas,
PO_VENDORS pv, PO_VENDOR_SITES_ALL pvsa,
HR_ALL_ORGANIZATION_UNITS hao
WHERE flpi.Export_Status = 'POST'
AND flpi.Lease_ID = fl.Lease_ID
AND flpi.Payment_Schedule_ID = fas.Payment_Schedule_ID
AND flpi.Schedule_Amort_Line_Num = fas.Amortization_Line_Num
AND pv.Vendor_ID = fl.Lessor_ID
AND pvsa.Vendor_Site_ID = flpi.Lessor_Site_ID
AND hao.Organization_ID = pvsa.Org_ID
ORDER BY hao.Organization_ID, pv.Vendor_ID
/* code fix for bug no.3649844. adding the update clause to avoid duplicate lines in AP interface table*/
FOR UPDATE;
SELECT ap_invoices_interface_s.nextval, ap_invoice_lines_interface_s.nextval
INTO V_Invoice_ID, V_Invoice_Line_ID
FROM dual;
Changed the signature of FA_LPITEMS_EXPT_PKG.Invoice_Insert_Row
to pass on the above two paramters.
*/
begin
XLE_BUSINESSINFO_GRP.GET_PURCHASETOPAY_INFO(
X_return_status => V_Return_Status,
X_msg_data => V_Msg_Data,
P_registration_code => null,
P_registration_number => null,
P_location_id => null,
P_code_combination_id => V_Dist_Code_Combination_ID,
P_operating_unit_id => current_item.Org_ID,
X_ptop_le_info => V_Ptop_LE_Info);
-- insert into table AP_INVOICES_INTERFACE
FA_LPITEMS_EXPT_PKG.Invoice_Insert_Row(
X_Invoice_ID => V_Invoice_ID,
X_Invoice_Num => V_Invoice_Num,
X_Vendor_ID => V_Vendor_ID,
X_Vendor_Site_ID => V_Vendor_Site_ID,
X_Invoice_Amount => V_Invoice_Amount,
X_Invoice_Currency_Code => V_Invoice_Currency_Code,
X_Source => V_Source,
X_Terms_ID => V_Terms_ID,
X_Last_Updated_by => V_User_ID,
X_Last_Update_Date => Sysdate,
X_Last_Update_Login => V_User_ID,
X_Created_by => V_User_ID,
X_Creation_Date => Sysdate,
X_GL_Date => V_Payment_Date,
X_Invoice_Date => V_Payment_Date,
X_Org_ID => V_Org_ID,
X_LE_ID => V_LE_ID,
X_LE_Name => V_LE_Name
,p_log_level_rec => p_log_level_rec);
-- insert into table AP_INVOICE_LINE_INTERFACE
FA_LPITEMS_EXPT_PKG.Invoice_Line_Insert_Row(
X_Invoice_ID => V_Invoice_ID,
X_Invoice_Line_ID => V_Invoice_Line_ID,
X_Line_Number => V_Line_Number,
X_Line_Type_Lookup_Code => V_Line_Type_Lookup_Code,
X_Amount => V_Invoice_Amount,
X_Dist_Code_Combination_ID => V_Dist_Code_Combination_ID,
X_Last_Updated_by => V_User_ID,
X_Last_Update_Date => Sysdate,
X_Last_Update_Login => V_User_ID,
X_Created_by => V_User_ID,
X_Creation_Date => Sysdate,
X_Accounting_Date => V_Payment_Date,
X_Org_ID => V_Org_ID
,p_log_level_rec => p_log_level_rec);
/*update the FA_Lease_Payment_Items table with new information.*/
UPDATE FA_Lease_Payment_Items
SET Invoice_ID = V_Invoice_ID,
Invoice_Line_ID = V_Invoice_Line_ID,
Request_ID = V_Request_ID,
Export_Status = 'POSTED'
WHERE Lease_ID = current_item.Lease_ID
AND Payment_Schedule_ID = current_item.Payment_Schedule_ID
AND Schedule_Amort_Line_Num = current_item.Amort_Line_Num;