AP_INVOICES_INTERFACE
-----------------------
1)INVOICE_ID not null Required, Primary key. This value is assigned in
the Invoice Gateway by the AP_INVOICES_INTERFACE_S sequence.
2) INVOICE_NUM Required if there is more than one invoice for the
supplier during import
3)VENDOR_ID or VENDOR_NAME or VENDOR_NUM is reqd if not matched to PO
4)VENDOR_SITE_ID or VENDOR_SITE_CODE is reqd if not matched to PO
5)INVOICE_AMOUNT
6)PO_NUMBER
6)INVOICE_CURRENCY_CODE:if not eneterd defaulted to functional currency
7)EXCHANGE_RATE_TYPE :User,Spot, Corporate, EMU Fixed, and user–defined.for Spot, Corporate, or any user–defined rate type, the value you enter here is validated against
the GL Daily Rates table.If you use EMU Fixed,Payables will provide the exchange rate during import. If you use User as the exchange rate type,you must enter a value for EXCHANGE_RATE orthe record will be rejected during import.
8)TERMS_NAME or TERMS_ID.:Payables searches1)invoice record header 2)purchase order terms if invoice is matched to PO 3)supplier site.
9)SOURCE
10)DOC_CATEGORY_CODE :
If you are using automatic sequential numbering, then Payables Open
Interface Import uses this column to assign a document category to the
invoice it creates.If the Sequential Numbering profile value is ”Always” and you do not
enter a value in this column, then during import Payables will use STANDARD as the category if the invoice amount is zero or positive,and CREDIT if the invoice amount is negative.
If you enable the Allow Document Category Override Payables option,you can enter the document category you want Open Interface Import to assign to the invoice created from this record
10)VOUCHER_NUM:If you use manual sequential numbering, then enter a unique value
11)PAYMENT_CURRENCY_CODE:Currency code for the payment. If you do not provide a value, then
during import PAYMENT_CURRENCY_CODE will be set to the same value as the INVOICE_CURRENCY_CODE,
12)PAYMENT_METHOD_LOOKUP_CODE:Method that will be used to pay the invoice.
The value must be a valid value for the PAYMENT_METHOD lookup code: Validation:
13)ACCTS_PAY_CODE_COMBINATION_ID:Liability account.
14)ORG_ID
AP_INVOICE_LINES_INTERFACE
Create Or Replace Procedure m_Ap_Invoices(Errbuf Out Varchar2,
Retcode Out Varchar2) Is
--starts the declaring variables for headers
v_Invoice_Id Ap_Invoices_Interface.Invoice_Id%Type;
v_Type Ap_Invoices_Interface.Invoice_Type_Lookup_Code %Type;
v_Supplier Ap_Invoices_Interface.Vendor_Name %Type;
v_Invoice_Date Ap_Invoices_Interface.Invoice_Date %Type;
v_Invoice_Num Ap_Invoices_Interface.Invoice_Num %Type;
v_Invoice_Curr Ap_Invoices_Interface.Invoice_Currency_Code%Type;
v_Invoice_Amount Ap_Invoices_Interface.Invoice_Amount %Type;
v_Payment_Cross_Rate Ap_Invoices_Interface.Payment_Cross_Rate%Type;
v_Cross_Payment_Curr Ap_Invoices_Interface.Payment_Currency_Code%Type;
v_Pay_Rate_Date Ap_Invoices_Interface.Payment_Cross_Rate_Date%Type;
v_Payment_Rate_Type Ap_Invoices_Interface.Payment_Cross_Rate_Type%Type;
v_Description Ap_Invoices_Interface.Description%Type;
v_Rate_Type Ap_Invoices_Interface.Exchange_Rate_Type%Type;
v_Exchange_Date Ap_Invoices_Interface.Exchange_Date%Type;
v_Exchange_Rate Ap_Invoices_Interface.Exchange_Rate%Type;
v_Terms_Date Ap_Invoices_Interface.Terms_Date%Type;
v_Terms Ap_Invoices_Interface.Terms_Id%Type;
v_Payment_Method Ap_Invoices_Interface.Payment_Method_Lookup_Code%Type;
v_Discountable_Amt Ap_Invoices_Interface.Amount_Applicable_To_Discount%Type;
v_Invoice_Recieved_Date Ap_Invoices_Interface.Invoice_Received_Date%Type;
v_Recieved_Goods_Date Ap_Invoices_Interface.Goods_Received_Date%Type;
v_Pay_Code_Combination_Id Ap_Invoices_Interface.Accts_Pay_Code_Combination_Id%Type;
v_Status Ap_Invoices_Interface.Status%Type;
v_Po_Number Ap_Invoices_Interface.Po_Number%Type := Null;
v_Attribute1 Ap_Invoices_Interface.Attribute1%Type;
v_Attribute2 Ap_Invoices_Interface.Attribute2%Type;
v_Attribute3 Ap_Invoices_Interface.Attribute3%Type;
v_Attribute4 Ap_Invoices_Interface.Attribute4%Type;
v_Attribute5 Ap_Invoices_Interface.Attribute5%Type;
v_Attribute6 Ap_Invoices_Interface.Attribute6%Type;
v_Attribute7 Ap_Invoices_Interface.Attribute7%Type;
v_Attribute8 Ap_Invoices_Interface.Attribute8%Type;
v_Attribute9 Ap_Invoices_Interface.Attribute9%Type;
v_Attribute10 Ap_Invoices_Interface.Attribute10%Type;
v_Vendor_Id Po_Vendors.Vendor_Id%Type;
v_Vendor_Site_Cd Po_Vendor_Sites.Vendor_Site_Code%Type;
v_Vendor_Site_Id Po_Vendor_Sites.Vendor_Site_Id%Type;
v_Currency_Code Fnd_Currencies.Currency_Code%Type;
v_Lookup_Code Ap_Lookup_Codes.Lookup_Code%Type;
v_Code_Comb Gl_Code_Combinations.Code_Combination_Id%Type;
v_Segment1 Po_Headers_All.Segment1%Type;
v_Term_Name Ap_Terms.Name%Type;
v_Term_Id Ap_Terms.Term_Id%Type;
v_Closed_Date Po_Headers_All.Closed_Date%Type;
v_Process_Flag Char(1) := Null;
v_Last_Date Date;
v_Count Number;
--ends the declaring variables for headers
--statrts the declaring variables for lines
--v_invoice_id ap_invoice_lines_interface.invoice_id%type;
v_Line_No Ap_Invoice_Lines_Interface.Line_Number%Type;
v_Amount Ap_Invoice_Lines_Interface.Amount%Type;
v_Expence_Account Ap_Invoice_Lines_Interface.Dist_Code_Combination_Id%Type;
v_Expence_Account1 Ap_Invoice_Lines_Interface.Dist_Code_Concatenated%Type;
v_Line_Type Ap_Invoice_Lines_Interface.Line_Type_Lookup_Code%Type;
v_Accounting_Date Ap_Invoice_Lines_Interface.Accounting_Date%Type;
v_Invoice_Line_Id Ap_Invoice_Lines_Interface.Invoice_Line_Id%Type;
v_Line_Attribute1 Ap_Invoice_Lines_Interface.Attribute1%Type;
v_Line_Attribute2 Ap_Invoice_Lines_Interface.Attribute2%Type;
v_Line_Attribute3 Ap_Invoice_Lines_Interface.Attribute3%Type;
v_Line_Attribute4 Ap_Invoice_Lines_Interface.Attribute4%Type;
v_Line_Attribute5 Ap_Invoice_Lines_Interface.Attribute5%Type;
v_Line_Attribute6 Ap_Invoice_Lines_Interface.Attribute6%Type;
v_Line_Attribute7 Ap_Invoice_Lines_Interface.Attribute7%Type;
v_Line_Attribute8 Ap_Invoice_Lines_Interface.Attribute8%Type;
v_Line_Attribute9 Ap_Invoice_Lines_Interface.Attribute9%Type;
v_Line_Attribute10 Ap_Invoice_Lines_Interface.Attribute10%Type;
v_Receipt_Number Ap_Invoice_Lines_Interface.Receipt_Number%Type;
/*following variables for PO related invoices*/
v_Found Char(1) := Null;
v_Po_Header_Id Ap_Invoice_Lines_Interface.Po_Header_Id%Type;
v_Po_Line_Id Ap_Invoice_Lines_Interface.Po_Line_Id%Type;
v_Po_Line_Num Ap_Invoice_Lines_Interface.Po_Line_Number%Type;
v_Po_Segment Ap_Invoice_Lines_Interface.Po_Number%Type;
v_Line_Location_Id Ap_Invoice_Lines_Interface.Po_Line_Location_Id%Type;
v_Shipment_Num Ap_Invoice_Lines_Interface.Po_Shipment_Num%Type;
v_Po_Distribution_Id Ap_Invoice_Lines_Interface.Po_Distribution_Id%Type;
v_Po_Distribution_Num Ap_Invoice_Lines_Interface.Po_Distribution_Num%Type;
--ends the declaring variables for lines
--declaring cursor for fetching header,Lines values from staging table.
Cursor Invoice_Cur Is
Select Invh.Rec_Id,
Invh.Type,
Invh.Supplier,
Invh.Site,
Invh.Invoice_Date,
Invh.Invoice_Num Invoice_Num,
Invh.Invoice_Curr,
Invh.Invoice_Amount,
Invh.Amount_Paid,
Invh.Payment_Cross_Rate,
Invh.Payment_Cross_Curr,
Invh.Pay_Rate_Date,
Invh.Payment_Rate_Type,
Invh.Payment_Rate,
Invh.Payment_Amt,
Invh.Description,
Invh.Tranction_Code,
Invh.Rate_Type,
Invh.Exchange_Date,
Invh.Exchange_Rate,
Invh.Functional_Amount,
Invh.Terms_Date,
Invh.Terms,
Invh.Payment_Method,
Invh.Discountable_Amt,
Invh.Invoice_Recieved_Date,
Invh.Recieved_Goods_Date,
Invh.Accts_Pay_Code_Combination_Id,
Invh.Status,
Invh.Po_Number,
Invh.Attribute1 Headattr1,
Invh.Attribute2 Headattr2,
Invh.Attribute3 Headattr3,
Invh.Attribute4 Headattr4,
Invh.Attribute5 Headattr5,
Invh.Attribute6 Headattr6,
Invh.Attribute7 Headattr7,
Invh.Attribute8 Headattr8,
Invh.Attribute9 Headattr9,
Invh.Attribute10 Headattr10,
Invh.Process_Flag,
Invl.Invoice_Num Line_Invoice_Num,
Invl.Line_No,
Invl.Amount,
Invl.Expence_Account,
Invl.Expence_Account1,
Invl.Accounting_Date,
Invl.Attribute1 Lineattr1,
Invl.Attribute2 Lineattr2,
Invl.Attribute3 Lineattr3,
Invl.Attribute4 Lineattr4,
Invl.Attribute5 Lineattr5,
Invl.Attribute6 Lineattr6,
Invl.Attribute7 Lineattr7,
Invl.Attribute8 Lineattr8,
Invl.Attribute9 Lineattr9,
Invl.Attribute10 Lineattr10
From m_Invoice_Headers_Int Invh, m_Invoice_Lines_Int Invl
Where Invh.Rec_Id = Invl.Rec_Id;
Begin
For Cur_Invoice In Invoice_Cur Loop
Begin
--Loop starts here to process invoice headers data
--fetching sequence value for invoice_id
Select Ap_Invoices_Interface_s.Nextval Into v_Invoice_Id From Dual;
v_Type := Cur_Invoice.Type;
v_Supplier := Cur_Invoice.Supplier;
v_Invoice_Date := Cur_Invoice.Invoice_Date;
v_Invoice_Num := Cur_Invoice.Invoice_Num;
v_Invoice_Curr := Cur_Invoice.Invoice_Curr;
v_Invoice_Amount := Cur_Invoice.Invoice_Amount;
v_Payment_Cross_Rate := Cur_Invoice.Payment_Cross_Rate;
v_Cross_Payment_Curr := Cur_Invoice.Payment_Cross_Curr;
v_Pay_Rate_Date := Cur_Invoice.Pay_Rate_Date;
v_Payment_Rate_Type := Cur_Invoice.Payment_Rate_Type;
v_Description := Cur_Invoice.Description;
v_Rate_Type := Cur_Invoice.Rate_Type;
v_Exchange_Date := Cur_Invoice.Exchange_Date;
v_Exchange_Rate := Cur_Invoice.Exchange_Rate;
v_Terms_Date := Cur_Invoice.Terms_Date;
v_Terms := Cur_Invoice.Terms;
v_Payment_Method := Cur_Invoice.Payment_Method;
v_Discountable_Amt := Cur_Invoice.Discountable_Amt;
v_Invoice_Recieved_Date := Cur_Invoice.Invoice_Recieved_Date;
v_Recieved_Goods_Date := Cur_Invoice.Recieved_Goods_Date;
v_Pay_Code_Combination_Id := Cur_Invoice.Accts_Pay_Code_Combination_Id;
v_Status := Cur_Invoice.Status;
v_Po_Number := Cur_Invoice.Po_Number;
v_Attribute1 := Cur_Invoice.Headattr1;
v_Attribute2 := Cur_Invoice.Headattr2;
v_Attribute3 := Cur_Invoice.Headattr3;
v_Attribute4 := Cur_Invoice.Headattr4;
v_Attribute5 := Cur_Invoice.Headattr5;
v_Attribute6 := Cur_Invoice.Headattr6;
v_Attribute7 := Cur_Invoice.Headattr7;
v_Attribute8 := Cur_Invoice.Headattr8;
v_Attribute9 := Cur_Invoice.Headattr9;
v_Attribute10 := Cur_Invoice.Headattr10;
v_Process_Flag := Cur_Invoice.Process_Flag;
/* Validate invoice number if null then assign invoice number equal to week_end_date.*/
If v_Invoice_Num Is Null Then
Select Next_Day(Sysdate, 'FRIDAY') Into v_Last_Date From Dual;
v_Invoice_Num := v_Last_Date;
End If;
/* validate invoice type*/
If v_Type <> 'STANDARD' Or v_Type <> 'CREDIT' Or v_Type <> 'DEBIT' Then
v_Type := Null;
End If;
/* validating vendor id */
Begin
Select Vendor_Id
Into v_Vendor_Id
From Po_Vendors
Where Vendor_Name = Ltrim(Rtrim(Upper(v_Supplier)))
And Sysdate >= Start_Date_Active
And Sysdate < End_Date_Active;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
'No vendor id found OR vendor status is not active = ');
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/* Validation for VENDOR_SITE_CODE */
Begin
Select Vendor_Site_Code, Vendor_Site_Id
Into v_Vendor_Site_Cd, v_Vendor_Site_Id
From Po_Vendor_Sites
Where Vendor_Id = v_Vendor_Id;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
'No vendor site code found for this vendor - ' ||
v_Supplier);
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/* Invoice currecy code */
Begin
Select Currency_Code
Into v_Currency_Code
From Fnd_Currencies
Where Currency_Code = Ltrim(Rtrim(Upper(v_Invoice_Curr)))
And Sysdate Between Nvl(Start_Date_Active, Sysdate) And
Nvl(End_Date_Active, Sysdate);
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
'Currency Code Does not Exist for ' ||
v_Invoice_Curr);
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/* validating term id */
Begin
Select Term_Id, Name
Into v_Term_Id, v_Term_Name
From Ap_Terms
Where Term_Id = v_Terms
And v_Terms_Date Between Nvl(Start_Date_Active, Sysdate) And
Nvl(End_Date_Active, Sysdate); -- v_term_date not in ver 11.5.0
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
'Term id OR Term name Does not Exist for ' ||
v_Terms);
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/*validating payment method */
Begin
Select Lookup_Code
Into v_Lookup_Code
From Ap_Lookup_Codes
Where Lookup_Code = Upper(v_Payment_Method);
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
' Payment Method are not exist
in lookup table');
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/* validating GL Code Combination validation */
Begin
Select Code_Combination_Id
Into v_Code_Comb
From Gl_Code_Combinations
Where Code_Combination_Id = v_Pay_Code_Combination_Id;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
' Code Combination id not exist in table');
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/* validating po number */
Begin
If v_Po_Number Is Not Null Then
Select Segment1, Closed_Date
Into v_Segment1, v_Closed_Date
From Po_Headers_All
Where Segment1 = v_Po_Number;
If v_Closed_Date Is Not Null Then
Fnd_File.Put_Line(Fnd_File.Log, ' P O Staus is inactive');
End If;
End If;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log, ' P O number not found');
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/*Validating Payment cross rate */
Begin
If v_Invoice_Curr = v_Cross_Payment_Curr Then
v_Payment_Cross_Rate := 1;
Else
Select Conversion_Rate
Into v_Payment_Cross_Rate --v_conversion_rate
From Gl_Daily_Rates
Where From_Currency = v_Invoice_Curr
And To_Currency = v_Cross_Payment_Curr
And Conversion_Date = v_Pay_Rate_Date
And Conversion_Type = v_Payment_Rate_Type;
End If;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log, ' Conversion rates not found');
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
End;
/* validating exchange rate */
If v_Rate_Type = 'USER' And v_Exchange_Rate Is Not Null Then
Null;
Elsif v_Rate_Type = 'USER' And v_Exchange_Rate Is Null Then
Fnd_File.Put_Line(Fnd_File.Log,
' Exchange rate is null for the invoice number -' ||
v_Invoice_Num);
Elsif v_Rate_Type <> 'USER' And v_Exchange_Rate Is Not Null Then
v_Exchange_Rate := 0;
End If;
/* validating duplication of invoice */
Select Count(*)
Into v_Count
From Ap_Invoices_All
Where Invoice_Num = v_Invoice_Num
And Vendor_Id = v_Vendor_Id;
If v_Count > 0 Then
Fnd_File.Put_Line(Fnd_File.Log,
' Invocie already Exists - ' || v_Invoice_Num);
Update m_Invoice_Headers_Int
Set Process_Flag = 'N'
Where Invoice_Num = v_Invoice_Num
And Supplier = v_Supplier;
Else
--inserting validated values to interface table(headers)
Insert Into Ap_Invoices_Interface
(Invoice_Id,
Invoice_Num,
Invoice_Type_Lookup_Code,
Invoice_Date,
Po_Number,
Vendor_Id,
Vendor_Name,
Vendor_Site_Id,
Vendor_Site_Code,
Invoice_Amount,
Invoice_Currency_Code,
Exchange_Rate,
Exchange_Rate_Type,
Exchange_Date,
Terms_Id,
Terms_Name,
Terms_Date,
Description,
Status,
Source,
Payment_Cross_Rate_Type,
Payment_Cross_Rate_Date,
Payment_Cross_Rate,
Payment_Currency_Code,
Payment_Method_Lookup_Code,
Goods_Received_Date,
Invoice_Received_Date,
Gl_Date,
Accts_Pay_Code_Combination_Id,
Amount_Applicable_To_Discount,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By)
Values
(v_Invoice_Id,
v_Invoice_Num,
v_Type,
v_Invoice_Date,
v_Po_Number,
v_Vendor_Id,
v_Supplier,
v_Vendor_Site_Id,
v_Vendor_Site_Cd,
Nvl(v_Invoice_Amount, 0),
v_Invoice_Curr,
v_Exchange_Rate,
v_Rate_Type,
v_Exchange_Date,
v_Terms,
v_Term_Name,
v_Terms_Date,
v_Description,
v_Status,
'InvoiceGateway',
v_Payment_Rate_Type,
v_Pay_Rate_Date,
v_Payment_Cross_Rate,
v_Cross_Payment_Curr,
v_Payment_Method,
v_Recieved_Goods_Date,
v_Invoice_Recieved_Date,
'30-NOV-2003',
v_Pay_Code_Combination_Id,
v_Discountable_Amt,
v_Attribute1,
v_Attribute2,
v_Attribute3,
v_Attribute4,
v_Attribute5,
v_Attribute6,
v_Attribute7,
v_Attribute8,
v_Attribute9,
v_Attribute10,
Sysdate,
Fnd_Global.User_Id,
Sysdate,
Fnd_Global.User_Id);
--starts here to process invoice lines data
--fetching sequence value for invoice_line_id
Select Ap_Invoice_Lines_Interface_s.Nextval
Into v_Invoice_Line_Id
From Dual;
--assigning values from staging table(lines)
--v_invoice_num :=cur_invoice.invoice_num;
v_Line_No := Cur_Invoice.Line_No;
v_Amount := Cur_Invoice.Amount;
v_Expence_Account := Cur_Invoice.Expence_Account;
v_Expence_Account1 := Cur_Invoice.Expence_Account1;
v_Accounting_Date := Cur_Invoice.Accounting_Date;
v_Line_Type := 'Item'; --(Item, Fright,Tax,Miscellaneous)
v_Line_Attribute1 := Cur_Invoice.Lineattr1;
v_Line_Attribute2 := Cur_Invoice.Lineattr2;
v_Line_Attribute3 := Cur_Invoice.Lineattr3;
v_Line_Attribute4 := Cur_Invoice.Lineattr4;
v_Line_Attribute5 := Cur_Invoice.Lineattr5;
v_Line_Attribute6 := Cur_Invoice.Lineattr6;
v_Line_Attribute7 := Cur_Invoice.Lineattr7;
v_Line_Attribute8 := Cur_Invoice.Lineattr8;
v_Line_Attribute9 := Cur_Invoice.Lineattr9;
v_Line_Attribute10 := Cur_Invoice.Lineattr10;
-- Validate Line Level Accounting Date
Begin
Select 'Y'
Into v_Found
From Gl_Period_Statuses
Where Application_Id = 200 --ASSUMTION
And Set_Of_Books_Id = 3 --ASSUMTION
And Sysdate Between Start_Date And End_Date
And Closing_Status In ('O', 'F')
And Nvl(Adjustment_Period_Flag, 'N') = 'N'
And Rownum = 1;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
'Accounting Date Is Not In Open');
End;
/*------start to Validate Line Level for po_number matching/validating----------*/
-- get the info of header (if invoice is related to po_number)
Begin
Select Po_Header_Id, Segment1
Into v_Po_Header_Id, v_Po_Segment
From Po_Headers_All
Where Vendor_Id = Vendor_Id;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log, 'PO not found in Headers');
End;
-- get the info line(if invoice is related to po_number)
Begin
Select Po_Line_Id, Line_Num
Into v_Po_Line_Id, v_Po_Line_Num
From Po_Lines_All
Where Po_Header_Id = v_Po_Header_Id;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log, 'PO not found in Lines');
End;
-- get the info location (if invoice is related to po_number)
Begin
Select Line_Location_Id, Shipment_Num
Into v_Line_Location_Id, v_Shipment_Num
From Po_Line_Locations_All
Where Po_Header_Id = v_Po_Header_Id
And Po_Line_Id = v_Po_Line_Id;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log, 'PO not found in Locations');
End;
-- get the info of distributions(if invoice is related to po_number)
Begin
Select Po_Distribution_Id, Distribution_Num
Into v_Po_Distribution_Id, v_Po_Distribution_Num
From Po_Distributions_All
Where Po_Header_Id = v_Po_Header_Id
And Po_Line_Id = v_Po_Line_Id
And Line_Location_Id = v_Line_Location_Id;
Exception
When No_Data_Found Then
Fnd_File.Put_Line(Fnd_File.Log,
'PO not found in distributionss');
End;
/*------end to Validate Line Level for po_number matching/validating----------*/
/*start to inserting invoice line*/
Insert Into Ap_Invoice_Lines_Interface
(Invoice_Id,
Invoice_Line_Id,
Line_Number,
Line_Type_Lookup_Code,
Amount,
Accounting_Date,
Dist_Code_Concatenated,
Dist_Code_Combination_Id,
Po_Header_Id,
Po_Number,
Po_Line_Id,
Po_Line_Number,
Po_Line_Location_Id,
Po_Shipment_Num,
Po_Distribution_Id,
Po_Distribution_Num,
Receipt_Number,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By)
Values
(v_Invoice_Id,
v_Invoice_Line_Id,
v_Line_No,
v_Line_Type,
v_Amount,
v_Accounting_Date,
v_Expence_Account1,
v_Expence_Account,
v_Po_Header_Id,
v_Po_Segment,
v_Po_Line_Id,
v_Po_Line_Num,
v_Line_Location_Id,
v_Shipment_Num,
v_Po_Distribution_Id,
v_Po_Distribution_Num,
v_Receipt_Number,
v_Line_Attribute1,
v_Line_Attribute2,
v_Line_Attribute3,
v_Line_Attribute4,
v_Line_Attribute5,
v_Line_Attribute6,
v_Line_Attribute7,
v_Line_Attribute8,
v_Line_Attribute9,
v_Line_Attribute10,
Sysdate,
Fnd_Global.User_Id,
Sysdate,
Fnd_Global.User_Id);
/*end to inserting invoice line*/
-- ends here to process invoice lines data
End If;
Exception
When No_Data_Found Then
Commit;
Exit;
When Others Then
Fnd_File.Put_Line(Fnd_File.Log, 'Exception' || Sqlerrm);
Rollback;
Exit;
End;
End Loop; --Loop ends here to process invoice headers data
--FND_FILE.PUT_LINE(FND_FILE.log,'Data Transfer is Successfull');
End;
/
Showing posts with label Script. Show all posts
Showing posts with label Script. Show all posts
Tuesday, 24 March 2015
AP Complete Interface
Pre-requisites:
a) Vendors and their Sites Should Be Setup
b) Currency and exchange Rates Should Be setup
c) Source and invoice type lookup code must be defined
d) Account Payables Account and Distribution Account must be defined.
e) Payment Terms should be defines and GL Period must be in opened status.
Interface Tables:
AP_INVOICES_INTERFACE
AP_INVOCE_LINES_INTERFACE
BASE TABLES :
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_PAYMENT_SCHEDULES
INTERFACE PROGRAM:
Payables Open Interface Impor
Parameters to be passed during the program:
Source = External
group_id = As Specified in the data file
Batch Name = N/A
Hold Name = null
Hold Reason = null
gl date = null
Purge = No
Summarize = No
Error Tables:
AP_INTERFACE_REJECTIONS
AP_INTERFACE_CONTROLS
Main Columns In AP_INVOICES_INTERFACE:
INVOICE_ID, INVOICE_NUM, INVOICE_DATE ,INVOICE_AMOUNT
VENDOR_ID , VENDOR_NUM, VENDOR_NAME ,VENDOR_SITE_CODE, VENDOR_SITE_ID
TERMS_NAME, TERM_ID, TERM_DATE, SOURCE, PAYMENT_METHOD_LOOKUP_CODE,
GL_DATE, PO_NUMBER, INVOICE_CURRENCE_CODE, EXCHANGE_RATE_TYPE
VOUCHER_NUMBER, PAYMENT_CURRENCY_CODE, DOC_CATEGPRY_CODE
Main Columns In AP_INVOICE_LINES_INTERFACE:
INVOICE_ID , INVOICE_LINE_ID , LINE_NUM, LINE_TYPE_LOOKUP_CODE
AMOUNR , DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
PO_HEADER_ID, PO_LINE_ID, PO_LINE_NUMBER
Tables To Be Used For Validation:
PO_VENDORS AP_TERMS ORG_ORGANIZATION_DEFINITIONS
FND_CURRNECIES AP_INVOICES_ALL GL_DAILY_CONVERSION_TYPES
GL_CODE_COMBINATIONS AP_LOOKUP_CODES GL_PERIOD_STATUSES
Control file for Headers:-
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.csv'
truncate into table ap_int_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INVOICE_NUM ,
INVOICE_DATE ,
INVOICE_TYPE_LOOKUP_CODE ,
VENDOR_NAME ,
VENDOR_SITE_CODE ,
INVOICE_AMOUNT ,
INVOICE_CURRENCY_CODE ,
DESCRIPTION ,
SOURCE ,
PAYMENT_METHOD_LOOKUP_CODE ,
PAY_GROUP_LOOKUP_CODE ,
OU_NAME "REPLACE(:OU_NAME,CHR(13),'')"
)
For Lines:-
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.csv'
truncate into table ap_int_line_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INVOICE_NUM ,
OU_NAME ,
LINE_NUMBER ,
LINE_TYPE_LOOKUP_CODE ,
AMOUNT ,
DESCRIPTION ,
DIST_CODE_CONCATENATED "REPLACE(:DIST_CODE_CONCATENATED,CHR(13),'')"
)
Shell Script:-
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.log'
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.log'
exit 0
Common error table and procedure:-
create table ap_common_err_tab
(
interface_name varchar2(50),
table_name varchar2(50),
column_name varchar2(50),
error_message varchar2(100),
created_by varchar2(10),
creation_date date,
last_updated_by varchar2(10),
last_update_date date,
request_id varchar2(10)
);
create or replace procedure ap_common_err_proc
(
p_interface_name varchar2,
p_table_name varchar2,
p_column_name varchar2,
p_error_message varchar2
)
is
begin
insert into ap_common_err_tab
values
(
p_interface_name,
p_table_name,
p_column_name,
p_error_message,
fnd_profile.value('USER_ID'),
SYSDATE,
fnd_profile.value('USER_ID'),
SYSDATE,
fnd_profile.value('CONC_REQUEST_ID')
);
Total Package With Validation:-
CREATE OR REPLACE package body APPS.ee_ap_inv_pkg
is
--for declaring global variables
g_user_id number(10):=fnd_profile.value('USER_ID');
g_date date:=sysdate;
procedure ap_record_details
is
cursor c1 is select * from ap_inv_head
where process_flag='S';
cursor c2 is select * from ap_inv_head
where process_flag='E';
v_tot_rec number;
v_sus_rec number;
v_err_rec number;
begin
--for total records
select count(*) into v_tot_rec
from ap_inv_head;
-- for success records
select count(*) into v_sus_rec
from ap_inv_head
where process_flag='S';
--for error records
select count(*) into v_err_rec
from ap_inv_head
where process_flag='E';
for info in c1
loop
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'FOR SUSCESS INFORMATION');
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info.invoice_num);
fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info.vendor_name);
fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);
fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);
fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);
end loop;
for info1 in c2
loop
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'FOR ERROR INFORMATION');
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info1.invoice_num);
fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info1.vendor_name);
fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);
fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);
fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);
fnd_file.put_line(fnd_file.output,'ERROR MESSAGE =>'||info1.error_message);
end loop;
end ap_record_details;
---arch table information
procedure arch_proc
is
begin
insert into ap_inv_head_arch select * from ap_inv_head;
insert into ap_inv_line_arch select * from ap_inv_line;
delete from ap_inv_head;
delete from ap_inv_line;
commit;
end;
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end dis_log;
procedure main(errbuf out varchar2,
retcode out varchar2
)
is
cursor head is select * from ap_inv_head;
cursor line(p_inv_num in varchar2) is select * from ap_inv_line
where invoice_num=p_inv_num;
v_head ap_invoices_interface%rowtype;
v_line ap_invoice_lines_interface%rowtype;
v_head_tab varchar2(100):='AP_INV_HEAD';
v_line_tab varchar2(100):='AP_INV_LINE';
v_conc_program varchar2(100);
v_process_flag varchar2(1);
v_error_message varchar2(100);
v_tot_err_msg varchar2(1000);
v_count number;
v_inv_num_exists exception;
v_amount number;
begin
---concurrent program------
select fcp.user_concurrent_program_name
into v_conc_program
from fnd_concurrent_programs_vl fcp,
fnd_concurrent_requests fcr
where fcp.concurrent_program_id=fcr.concurrent_program_id
and fcr.request_id=g_request_id;
---end of concurrent program------
for rec_head in head
loop
begin
v_process_flag:='S';
v_error_message:=null;
v_tot_err_msg:=null;
--*****invoice type lookyp code****--
begin
select lookup_code into v_head.invoice_type_lookup_code
from ap_lookup_codes
where lookup_type='INVOICE TYPE'
and displayed_field=rec_head.invoice_type_lookup_code;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID INVOICE TYPE LOOKUP CODE =>'||rec_head.invoice_type_lookup_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE TYPE LOOKYP CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT INVOICE TYPE LOOKUP CODE VALIDATION =>'||rec_head.invoice_type_lookup_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE TYPE LOOKYP CODE',
p_error_message => v_error_message
);
end;
--*****vendor name validation *****---
begin
select vendor_id into v_head.vendor_id
from po_vendors
where vendor_name=rec_head.vendor_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID VENDOR NAME =>'||rec_head.vendor_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT VENDOR_NAME VALIDATION =>'||rec_head.vendor_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_NAME',
p_error_message => v_error_message
);
end;
---*****organiozation name validation ********---
begin
select organization_id into v_head.org_id
from org_organization_definitions
where organization_name=rec_head.ou_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID ORG NAME =>'||rec_head.ou_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'ORGANIZATION NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.ou_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'ORGANIZATION NAME',
p_error_message => v_error_message
);
end;
---*****vendor site code validation *******------
begin
select vendor_site_id into v_head.vendor_site_id
from po_vendor_sites_all
where vendor_site_code=rec_head.vendor_site_code
and vendor_id=v_head.vendor_id
and org_id=v_head.org_id;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID VENDOR SITE CODE =>'||rec_head.vendor_site_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_SITE_CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.vendor_site_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_SITE_CODE',
p_error_message => v_error_message
);
end;
--*******invoice num validation****---
select count(1) into v_count
from ap_invoices_all
where invoice_num=rec_head.invoice_num
and vendor_id=v_head.vendor_id
and org_id=v_head.org_id;
if v_count > 0
then
raise v_inv_num_exists;
end if;
-----****source validation****-----
begin
select lookup_code into v_head.source
from ap_lookup_codes
where lookup_type='SOURCE'
and displayed_field=rec_head.source;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID SOURCE =>'||rec_head.source;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'SOURCE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT SOURCE VALIDATION =>'||rec_head.source||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'SOURCE',
p_error_message => v_error_message
);
end;
---***terms validation ***----
begin
select term_id into v_head.terms_id
from ap_terms
where name=rec_head.terms_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID TERMS NAME =>'||rec_head.terms_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'TERMS_NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT TERMS NAME VALIDATION =>'||rec_head.terms_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'TERMS_NAME',
p_error_message => v_error_message
);
end;
---**** invoice amount validation ****----
begin
select sum(amount) into v_amount
from ap_inv_line
where invoice_num=rec_head.invoice_num;
if rec_head.invoice_amount <> v_amount then
v_process_flag:='E';
v_error_message:='INVOICE AMOUNT DOES NOT MATCH YOUR LINE AMOUNT =>'||rec_head.invoice_amount;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE AMOUNT',
p_error_message => v_error_message
);
end if;
end;
select ap_invoices_interface_s.nextval into v_head.invoice_id from dual;
v_head.invoice_num := rec_head.invoice_num;
-- v_head.invoice_type_lookup_code := rec_head.invoice_type_lookup_code;
v_head.vendor_name := rec_head.vendor_name;
v_head.vendor_site_code := rec_head.vendor_site_code;
v_head.invoice_amount := rec_head.invoice_amount;
v_head.terms_name := rec_head.terms_name;
v_head.created_by := g_user_id;
v_head.creation_date := g_date;
v_head.last_updated_by := g_user_id;
v_head.last_update_date := g_date;
for rec_line in line(rec_head.invoice_num)
loop
---*** line type validation ***----
begin
select lookup_code into v_line.line_type_lookup_code
from ap_lookup_codes
where lookup_type ='INVOICE DISTRIBUTION TYPE'
and displayed_field=rec_line.line_type_lookup_code;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID LINE TYPE LOOKUP CODE =>'||rec_line.line_type_lookup_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'LINE TYPE LOOKUP CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT LINE TYPE LOOKUP CODE VALIDATION =>'||rec_line.line_type_lookup_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'LINE TYPE LOOKUP CODE',
p_error_message => v_error_message
);
end;
---*** charge account valiation ***------
begin
select gcc.code_combination_id into v_line.dist_code_combination_id
from gl_code_combinations_kfv gcc,
gl_sets_of_books gsb
where gcc.chart_of_accounts_id=gsb.chart_of_accounts_id
and gsb.set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')
and gcc.concatenated_segments=rec_line.dist_code_concatenated;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID CHARGE ACCOUNT =>'||rec_line.dist_code_concatenated;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'CHARGE ACCOUNT',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT CHARGE ACCOUNT VALIDATION =>'||rec_line.dist_code_concatenated||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'CHARGE ACCOUNT',
p_error_message => v_error_message
);
end;
select ap_invoice_lines_interface_s.nextval into v_line.invoice_line_id from dual;
v_line.invoice_id := v_head.invoice_id;
v_line.line_number := rec_line.line_number;
--v_line.line_type_lookup_code := rec_line.line_type_lookup_code;
v_line.amount := rec_line.amount;
v_line.dist_code_concatenated := rec_line.dist_code_concatenated;
v_line.created_by := g_user_id;
v_line.creation_date := g_date;
v_line.last_updated_by := g_user_id;
v_line.last_update_date := g_date;
v_line.org_id := v_head.org_id;
dis_log('PROCESS_FLAG =>'||v_process_flag);
if v_process_flag = 'S' then
insert into ap_invoice_lines_interface values v_line;
end if;
end loop;
dis_log('PROCESS_FLAG =>'||v_process_flag);
if v_process_flag = 'S' then
insert into ap_invoices_interface values v_head;
end if;
update ap_inv_head set process_flag=v_process_flag,
error_message=v_tot_err_msg
where invoice_num=rec_head.invoice_num;
exception
when v_inv_num_exists then
v_process_flag:='E';
dis_log('PROCESS_FLAG =>'||v_process_flag);
v_error_message:='INVOICE NUM ALREADY EXISTS =>'||rec_head.invoice_num;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE NUM',
p_error_message => v_error_message
);
update ap_inv_head set process_flag=v_process_flag,
error_message=v_tot_err_msg
where invoice_num=rec_head.invoice_num;
end;
end loop;
ap_record_details; --for records information
arch_proc ; --for arch tables information
exception
when others then
dis_log('EXCEPTION AT PACKAGE =>'||sqlerrm);
end main;
end ee_ap_inv_pkg;
/
a) Vendors and their Sites Should Be Setup
b) Currency and exchange Rates Should Be setup
c) Source and invoice type lookup code must be defined
d) Account Payables Account and Distribution Account must be defined.
e) Payment Terms should be defines and GL Period must be in opened status.
Interface Tables:
AP_INVOICES_INTERFACE
AP_INVOCE_LINES_INTERFACE
BASE TABLES :
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_PAYMENT_SCHEDULES
INTERFACE PROGRAM:
Payables Open Interface Impor
Parameters to be passed during the program:
Source = External
group_id = As Specified in the data file
Batch Name = N/A
Hold Name = null
Hold Reason = null
gl date = null
Purge = No
Summarize = No
Error Tables:
AP_INTERFACE_REJECTIONS
AP_INTERFACE_CONTROLS
Main Columns In AP_INVOICES_INTERFACE:
INVOICE_ID, INVOICE_NUM, INVOICE_DATE ,INVOICE_AMOUNT
VENDOR_ID , VENDOR_NUM, VENDOR_NAME ,VENDOR_SITE_CODE, VENDOR_SITE_ID
TERMS_NAME, TERM_ID, TERM_DATE, SOURCE, PAYMENT_METHOD_LOOKUP_CODE,
GL_DATE, PO_NUMBER, INVOICE_CURRENCE_CODE, EXCHANGE_RATE_TYPE
VOUCHER_NUMBER, PAYMENT_CURRENCY_CODE, DOC_CATEGPRY_CODE
Main Columns In AP_INVOICE_LINES_INTERFACE:
INVOICE_ID , INVOICE_LINE_ID , LINE_NUM, LINE_TYPE_LOOKUP_CODE
AMOUNR , DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
PO_HEADER_ID, PO_LINE_ID, PO_LINE_NUMBER
Tables To Be Used For Validation:
PO_VENDORS AP_TERMS ORG_ORGANIZATION_DEFINITIONS
FND_CURRNECIES AP_INVOICES_ALL GL_DAILY_CONVERSION_TYPES
GL_CODE_COMBINATIONS AP_LOOKUP_CODES GL_PERIOD_STATUSES
Control file for Headers:-
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.csv'
truncate into table ap_int_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INVOICE_NUM ,
INVOICE_DATE ,
INVOICE_TYPE_LOOKUP_CODE ,
VENDOR_NAME ,
VENDOR_SITE_CODE ,
INVOICE_AMOUNT ,
INVOICE_CURRENCY_CODE ,
DESCRIPTION ,
SOURCE ,
PAYMENT_METHOD_LOOKUP_CODE ,
PAY_GROUP_LOOKUP_CODE ,
OU_NAME "REPLACE(:OU_NAME,CHR(13),'')"
)
For Lines:-
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.csv'
truncate into table ap_int_line_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INVOICE_NUM ,
OU_NAME ,
LINE_NUMBER ,
LINE_TYPE_LOOKUP_CODE ,
AMOUNT ,
DESCRIPTION ,
DIST_CODE_CONCATENATED "REPLACE(:DIST_CODE_CONCATENATED,CHR(13),'')"
)
Shell Script:-
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.log'
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.log'
exit 0
Common error table and procedure:-
create table ap_common_err_tab
(
interface_name varchar2(50),
table_name varchar2(50),
column_name varchar2(50),
error_message varchar2(100),
created_by varchar2(10),
creation_date date,
last_updated_by varchar2(10),
last_update_date date,
request_id varchar2(10)
);
create or replace procedure ap_common_err_proc
(
p_interface_name varchar2,
p_table_name varchar2,
p_column_name varchar2,
p_error_message varchar2
)
is
begin
insert into ap_common_err_tab
values
(
p_interface_name,
p_table_name,
p_column_name,
p_error_message,
fnd_profile.value('USER_ID'),
SYSDATE,
fnd_profile.value('USER_ID'),
SYSDATE,
fnd_profile.value('CONC_REQUEST_ID')
);
Total Package With Validation:-
CREATE OR REPLACE package body APPS.ee_ap_inv_pkg
is
--for declaring global variables
g_user_id number(10):=fnd_profile.value('USER_ID');
g_date date:=sysdate;
procedure ap_record_details
is
cursor c1 is select * from ap_inv_head
where process_flag='S';
cursor c2 is select * from ap_inv_head
where process_flag='E';
v_tot_rec number;
v_sus_rec number;
v_err_rec number;
begin
--for total records
select count(*) into v_tot_rec
from ap_inv_head;
-- for success records
select count(*) into v_sus_rec
from ap_inv_head
where process_flag='S';
--for error records
select count(*) into v_err_rec
from ap_inv_head
where process_flag='E';
for info in c1
loop
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'FOR SUSCESS INFORMATION');
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info.invoice_num);
fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info.vendor_name);
fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);
fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);
fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);
end loop;
for info1 in c2
loop
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'FOR ERROR INFORMATION');
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info1.invoice_num);
fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info1.vendor_name);
fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);
fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);
fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);
fnd_file.put_line(fnd_file.output,'ERROR MESSAGE =>'||info1.error_message);
end loop;
end ap_record_details;
---arch table information
procedure arch_proc
is
begin
insert into ap_inv_head_arch select * from ap_inv_head;
insert into ap_inv_line_arch select * from ap_inv_line;
delete from ap_inv_head;
delete from ap_inv_line;
commit;
end;
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end dis_log;
procedure main(errbuf out varchar2,
retcode out varchar2
)
is
cursor head is select * from ap_inv_head;
cursor line(p_inv_num in varchar2) is select * from ap_inv_line
where invoice_num=p_inv_num;
v_head ap_invoices_interface%rowtype;
v_line ap_invoice_lines_interface%rowtype;
v_head_tab varchar2(100):='AP_INV_HEAD';
v_line_tab varchar2(100):='AP_INV_LINE';
v_conc_program varchar2(100);
v_process_flag varchar2(1);
v_error_message varchar2(100);
v_tot_err_msg varchar2(1000);
v_count number;
v_inv_num_exists exception;
v_amount number;
begin
---concurrent program------
select fcp.user_concurrent_program_name
into v_conc_program
from fnd_concurrent_programs_vl fcp,
fnd_concurrent_requests fcr
where fcp.concurrent_program_id=fcr.concurrent_program_id
and fcr.request_id=g_request_id;
---end of concurrent program------
for rec_head in head
loop
begin
v_process_flag:='S';
v_error_message:=null;
v_tot_err_msg:=null;
--*****invoice type lookyp code****--
begin
select lookup_code into v_head.invoice_type_lookup_code
from ap_lookup_codes
where lookup_type='INVOICE TYPE'
and displayed_field=rec_head.invoice_type_lookup_code;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID INVOICE TYPE LOOKUP CODE =>'||rec_head.invoice_type_lookup_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE TYPE LOOKYP CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT INVOICE TYPE LOOKUP CODE VALIDATION =>'||rec_head.invoice_type_lookup_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE TYPE LOOKYP CODE',
p_error_message => v_error_message
);
end;
--*****vendor name validation *****---
begin
select vendor_id into v_head.vendor_id
from po_vendors
where vendor_name=rec_head.vendor_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID VENDOR NAME =>'||rec_head.vendor_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT VENDOR_NAME VALIDATION =>'||rec_head.vendor_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_NAME',
p_error_message => v_error_message
);
end;
---*****organiozation name validation ********---
begin
select organization_id into v_head.org_id
from org_organization_definitions
where organization_name=rec_head.ou_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID ORG NAME =>'||rec_head.ou_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'ORGANIZATION NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.ou_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'ORGANIZATION NAME',
p_error_message => v_error_message
);
end;
---*****vendor site code validation *******------
begin
select vendor_site_id into v_head.vendor_site_id
from po_vendor_sites_all
where vendor_site_code=rec_head.vendor_site_code
and vendor_id=v_head.vendor_id
and org_id=v_head.org_id;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID VENDOR SITE CODE =>'||rec_head.vendor_site_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_SITE_CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.vendor_site_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_SITE_CODE',
p_error_message => v_error_message
);
end;
--*******invoice num validation****---
select count(1) into v_count
from ap_invoices_all
where invoice_num=rec_head.invoice_num
and vendor_id=v_head.vendor_id
and org_id=v_head.org_id;
if v_count > 0
then
raise v_inv_num_exists;
end if;
-----****source validation****-----
begin
select lookup_code into v_head.source
from ap_lookup_codes
where lookup_type='SOURCE'
and displayed_field=rec_head.source;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID SOURCE =>'||rec_head.source;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'SOURCE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT SOURCE VALIDATION =>'||rec_head.source||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'SOURCE',
p_error_message => v_error_message
);
end;
---***terms validation ***----
begin
select term_id into v_head.terms_id
from ap_terms
where name=rec_head.terms_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID TERMS NAME =>'||rec_head.terms_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'TERMS_NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT TERMS NAME VALIDATION =>'||rec_head.terms_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'TERMS_NAME',
p_error_message => v_error_message
);
end;
---**** invoice amount validation ****----
begin
select sum(amount) into v_amount
from ap_inv_line
where invoice_num=rec_head.invoice_num;
if rec_head.invoice_amount <> v_amount then
v_process_flag:='E';
v_error_message:='INVOICE AMOUNT DOES NOT MATCH YOUR LINE AMOUNT =>'||rec_head.invoice_amount;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE AMOUNT',
p_error_message => v_error_message
);
end if;
end;
select ap_invoices_interface_s.nextval into v_head.invoice_id from dual;
v_head.invoice_num := rec_head.invoice_num;
-- v_head.invoice_type_lookup_code := rec_head.invoice_type_lookup_code;
v_head.vendor_name := rec_head.vendor_name;
v_head.vendor_site_code := rec_head.vendor_site_code;
v_head.invoice_amount := rec_head.invoice_amount;
v_head.terms_name := rec_head.terms_name;
v_head.created_by := g_user_id;
v_head.creation_date := g_date;
v_head.last_updated_by := g_user_id;
v_head.last_update_date := g_date;
for rec_line in line(rec_head.invoice_num)
loop
---*** line type validation ***----
begin
select lookup_code into v_line.line_type_lookup_code
from ap_lookup_codes
where lookup_type ='INVOICE DISTRIBUTION TYPE'
and displayed_field=rec_line.line_type_lookup_code;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID LINE TYPE LOOKUP CODE =>'||rec_line.line_type_lookup_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'LINE TYPE LOOKUP CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT LINE TYPE LOOKUP CODE VALIDATION =>'||rec_line.line_type_lookup_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'LINE TYPE LOOKUP CODE',
p_error_message => v_error_message
);
end;
---*** charge account valiation ***------
begin
select gcc.code_combination_id into v_line.dist_code_combination_id
from gl_code_combinations_kfv gcc,
gl_sets_of_books gsb
where gcc.chart_of_accounts_id=gsb.chart_of_accounts_id
and gsb.set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')
and gcc.concatenated_segments=rec_line.dist_code_concatenated;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID CHARGE ACCOUNT =>'||rec_line.dist_code_concatenated;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'CHARGE ACCOUNT',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT CHARGE ACCOUNT VALIDATION =>'||rec_line.dist_code_concatenated||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'CHARGE ACCOUNT',
p_error_message => v_error_message
);
end;
select ap_invoice_lines_interface_s.nextval into v_line.invoice_line_id from dual;
v_line.invoice_id := v_head.invoice_id;
v_line.line_number := rec_line.line_number;
--v_line.line_type_lookup_code := rec_line.line_type_lookup_code;
v_line.amount := rec_line.amount;
v_line.dist_code_concatenated := rec_line.dist_code_concatenated;
v_line.created_by := g_user_id;
v_line.creation_date := g_date;
v_line.last_updated_by := g_user_id;
v_line.last_update_date := g_date;
v_line.org_id := v_head.org_id;
dis_log('PROCESS_FLAG =>'||v_process_flag);
if v_process_flag = 'S' then
insert into ap_invoice_lines_interface values v_line;
end if;
end loop;
dis_log('PROCESS_FLAG =>'||v_process_flag);
if v_process_flag = 'S' then
insert into ap_invoices_interface values v_head;
end if;
update ap_inv_head set process_flag=v_process_flag,
error_message=v_tot_err_msg
where invoice_num=rec_head.invoice_num;
exception
when v_inv_num_exists then
v_process_flag:='E';
dis_log('PROCESS_FLAG =>'||v_process_flag);
v_error_message:='INVOICE NUM ALREADY EXISTS =>'||rec_head.invoice_num;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE NUM',
p_error_message => v_error_message
);
update ap_inv_head set process_flag=v_process_flag,
error_message=v_tot_err_msg
where invoice_num=rec_head.invoice_num;
end;
end loop;
ap_record_details; --for records information
arch_proc ; --for arch tables information
exception
when others then
dis_log('EXCEPTION AT PACKAGE =>'||sqlerrm);
end main;
end ee_ap_inv_pkg;
/
Subscribe to:
Posts (Atom)