Custom Invoice Import
Custom imports allow for the creation of Munis invoices through a fixed or delimited file import.
Customization of the import form can be edited to fit an existing export or report from a subsystem, or to
accommodate a template to be used in place of large volume data entry.
Form Definitions
Financials>Accounts Payable>Setup>Form Definitions
1. Click Search.
2. Search for process: apinvimp and type: Master
3. Click Accept.
The system will display the default layout for AP Import.
5. Provide a Name and Description.
6. Click Accept.
Note: The standard invoice import format expects a 3-line import record per invoice: Header, Detail, and
Vendor information. This can be modified to expect a single line import, but will limit you from allocating
to multiple accounts or adding vendors on-the-fly. This example assumes it is a single line per invoice, and
allocated to one expense account.
This example is using an Excel template to populate invoice data, and then saved as a delimited file to be
imported as an AP batch.
8. Click Update.
9. Indicate the number of lines (rows) the import should expect for each invoice record:
11. For each field name, there are columns identifying the maximum characters, line that informs the
system as to which row the data is found on the import file, and the column or position that system
will find the field in the import file.
Note: You have the option to create a fixed length or a delimited file.
Fixed Length: The “Col” column is used to indicate the starting position of the field in the import
file.
Delimited: The “Col” column is used to indicate which column the system will find the data for
this field in the import file.
This example will create a delimited file.
12. The Row Type columns indicate which row or line on the import file to find the data. Since this
example assumes the import file is a single line per invoice, set the row type to 1 for all data fields.
13. Set the “Col” column to zero for any fields not included on the import file.
14. See Appendix A and B for the full form definition and sample data for the example used.
I created a Microsoft® Excel template, which outlines the fields I will include in my import.
15. Save the Excel file as a delimited file. You may choose your preferred delimiter. I chose a pipe ( |
).
16. Click Test from the Menu group of the ribbon to test the import.
The system will open Accounts Payable Invoice Import.
Note: This program can also be found from the menu: Financials>Accounts Payable> Invoice
Processing> Invoice Import
17. Click Define
18. Browse for the text file to be imported.
19. From the Import Format list, select Custom.
22. Indicate the delimiter of the file. I selected the pipe ( | ) in this example.
Note: This would be left blank if the import file was a fixed length format type.
23. Select the Include Decimals check box if your amount fields contain decimals.
24. Select the On the Fly Vendors check box to direct the system to create a new vendor record, if it
does not currently exist.
25. From the Flag Imported Invoices As list, choose the default payment method.
26. Click Accept.
27. Click Pre-edit to test the import.
The system will return any errors or warnings with the import.
28. Display or Print to review the errors.
29. Click OK to review the errors and make any corrections to the data.
Note: You may import with warnings, but not with errors)
31. Click OK to confirm the creation of invoices.
32. Open Invoice Entry to review the invoices, attach documentation, and to release/post the batch for
processing.
Keep in mind that the import status is a default established in AP Settings. That will determine
whether or not these invoices are required to follow standard API workflow.
01/22/2014 08:35 |KISD TESTING DATABASE Dec 30 2013 |PG 5
kiparmeterv |Form Definitions |spformdf
Process: apinvimp Description: AP INVOICE IMPORT FORM Master form process: apinvimp
Name: INVIMPRT Number of lines: 1 Master form name: BASEFORM
Type: User Modified Test program ID: apinvimp
FLD TYPE FIELD NAME / TEXT SZ LN COL LN COL LN COL LN COL
___ _______ ______________________________________________________________________ ___ ___ _____ ___ _____ ___ _____ ___ _____
1 Alpha Row Type 1 1 1 0 0 0 0 0 0
2 Alpha Invoice 20 1 2 0 0 0 0 0 0
3 Numeric Vendor 6 1 3 0 0 0 0 0 0
4 Numeric Remit No 8 1 4 0 0 0 0 0 0
5 Date Invoice Date 10 1 5 0 0 0 0 0 0
6 Date Due Date 10 1 6 0 0 0 0 0 0
7 Numeric Invoice Total 13 1 7 0 0 0 0 0 0
8 Alpha Invoice Description 30 1 8 0 0 0 0 0 0
9 Alpha Voucher 8 1 0 0 0 0 0 0 0
10 Alpha Purchase Order 8 1 0 0 0 0 0 0 0
11 Alpha Department 5 1 0 0 0 0 0 0 0
12 Alpha Separate Check 1 1 0 0 0 0 0 0 0
13 Alpha Budget Distribution 1 1 0 0 0 0 0 0 0
14 Alpha Comments 250 1 0 0 0 0 0 0 0
15 Date Discount Date 10 1 0 0 0 0 0 0 0
16 Numeric Discountable Amount of Gross 13 1 0 0 0 0 0 0 0
17 Numeric Discount Applied to Gross 13 1 0 0 0 0 0 0 0
18 Numeric Sales Tax 5 1 0 0 0 0 0 0 0
19 Numeric Use Tax 5 1 0 0 0 0 0 0 0
20 Numeric Not Taxable 13 1 0 0 0 0 0 0 0
21 Numeric Invoice Net 13 1 0 0 0 0 0 0 0
22 Alpha Wire Transfer 8 1 0 0 0 0 0 0 0
23 Alpha Contract 10 1 0 0 0 0 0 0 0
24 Numeric Work Order 8 1 0 0 0 0 0 0 0
25 Numeric Check No 10 1 0 0 0 0 0 0 0
26 Date Check Date 10 1 0 0 0 0 0 0 0
27 Alpha Include Documentation 1 1 0 0 0 0 0 0 0
28 Alpha Row Type 1 1 0 0 0 0 0 0 0
29 Alpha Invoice 20 1 0 0 0 0 0 0 0 30 Numeric Vendor 6 1 0 0 0 0 0 0 0 31 Numeric Sequence 8 1 9 0 0 0 0 0 0 32 Numeric Amount 13 1 10 0 0 0 0 0 0 33 Alpha Org 8 1 0 0 0 0 0 0 0 34 Alpha Object 6 1 0 0 0 0 0 0 0 35 Alpha Project 5 1 0 0 0 0 0 0 0 36 Alpha Description 30 1 0 0 0 0 0 0 0 37 Alpha Asset 15 1 0 0 0 0 0 0 0 38 Alpha 1099 Code 15 1 0 0 0 0 0 0 0
01/22/2014 08:35 |KISD TESTING DATABASE Dec 30 2013 |PG 6
kiparmeterv |Form Definitions |spformdf
Process: apinvimp Description: AP INVOICE IMPORT FORM Master form process: apinvimp
Name: INVIMPRT Number of lines: 1 Master form name: BASEFORM
Type: User Modified Test program ID: apinvimp
FLD TYPE FIELD NAME / TEXT SZ LN COL LN COL LN COL LN COL
___ _______ ______________________________________________________________________ ___ ___ _____ ___ _____ ___ _____ ___ _____
52 Alpha City 24 1 0 0 0 0 0 0 0
53 Alpha State 2 1 0 0 0 0 0 0 0
54 Alpha Zip 10 1 0 0 0 0 0 0 0
55 Numeric Remit No 8 1 0 0 0 0 0 0 0
56 Alpha Alpha Sort 20 1 0 0 0 0 0 0 0
57 Alpha DBA 35 1 0 0 0 0 0 0 0
58 Alpha Remit SW 1 1 0 0 0 0 0 0 0
59 Alpha Entity 1 1 0 0 0 0 0 0 0
60 Alpha Status Code 1 1 0 0 0 0 0 0 0
61 Alpha Class 4 1 0 0 0 0 0 0 0
62 Alpha 1099 Default 1 1 0 0 0 0 0 0 0
63 Alpha SSN 11 1 0 0 0 0 0 0 0
64 Alpha FID 16 1 0 0 0 0 0 0 0
65 Date Enter Date 10 1 0 0 0 0 0 0 0
66 Alpha W9 Sent 1 1 0 0 0 0 0 0 0
67 Alpha W9 Received 1 1 0 0 0 0 0 0 0
68 Alpha Remit Name 40 1 0 0 0 0 0 0 0
69 Alpha Remit Address 1 30 1 0 0 0 0 0 0 0
70 Alpha Remit Address 2 30 1 0 0 0 0 0 0 0
71 Alpha Remit City 24 1 0 0 0 0 0 0 0
72 Alpha Remit State 2 1 0 0 0 0 0 0 0
73 Alpha Remit Zip 10 1 0 0 0 0 0 0 0
