A/P Invoice Import Utility

Introduction

This utility is similar to the COP Sales Order Import Utility.  It will allow vendor invoices created through an external source to be imported and become New A/P Transactions in Elliott.

Traditionally, if a third-party software vendor wished to update the Elliott New A/P Transaction files, they would have the following difficulties:

• Developers need to know all the relevant files when the New A/P Transaction file is updated and make sure those files were updated correctly.
• Developers need to know how Elliott determines variance and create necessary variance entries by comparing with PO.
• Developers need to understand each field’s data type in Elliott to make the interface work correctly.
• Developers need to be aware when Elliott updates are made since new fields or files might be introduced.  This poses a potential support issue when the A/P module in Elliott is not functioning correctly.  Is it the developer’s problem or is it an Elliott problem?

The Elliott A/P Invoice Import Utility is designed to provide any third-party software developer an easy method for developing an interface with the Elliott New A/P Transaction files.  The potential application is that it could be used as an EDI Vendor Invoice Interface.  Other E-Commerce applications may be utilized as well.

Functionality

The developer is responsible for writing an ASCII text file for the New A/P Transaction Header and Distribution Detail information in the format specified by this document.  The Elliott import utility will then import the data and create New A/P Transaction Records.  Elliott's import utility will perform the following functions:

• Validate data integrity
• Give a warning or error if it detects a problem with the data
• Assign a default value if a field is left blank
• Calculate due date, discount date, discount amount, distribution amount, etc.
• Assign voucher numbers sequentially from the A/P Control File
• Prevent vouchers from being created with duplicate invoice numbers for a vendor
• Validate against PO
• Update the following files:
• New A/P Transaction File
• New A/P Transaction Distribution File
• A/P Receiving Accrual File
• P/O Receiving Accrual File

The import utility provides two functions: (1) Pre-Post Edit List, and (2) Post.  The Pre-Post function will check the data integrity and report any warnings or errors, as well as print out a report to show the New A/P Transactions that will be imported.  Users should run the Pre-Post and make sure the data is correct before proceeding to post.

Import File Layout

The import file will be a fixed length ASCII file with CR/LF at the end of each record.  Records can be terminated with CR/LF less than the full record length if the remaining fields are to be left blank.  Also, the developer should not use the long file name convention.  The file can be placed in any directory that the user has privilege to access.  The file path and name should not exceed 50 bytes in length.

The first byte of each record is the record type and it can be "H" or "D."  "H" stands for header record and "D" stands for detail record (or the distribution record).  If it is a header record, then from byte 2 onward, it has the same layout as the APTRXFIL file.  If it is a detail record, then from byte 2 onward, it has the same layout as the APTRXDST file.

An “H” record can be followed by one “D” record, multiple “D” records or no “D” record at all:

• If there is only one “D” record following the “H” record, it implies the whole invoice amount is to be distributed using that single “D” record.
• If there are multiple “D” records following the “H” record, it implies the invoice amount is to be distributed among the multiple “D” records.  Each “D” record must provide the distributed amount and the sum of the “D” records must fully distribute the invoice amount.
• If there are no “D” records at all, it implies the import utility determines the distribution account number.  This can be determined if the vendor has auto distribution turned on or it can validate against the PO module if a PO number is provided.

The “D” record will be terminated by another “H” record or when it reaches the end of the file.

Almost all the fields can be left blank and the system can figure out a default value for you.  However, the following fields are required:

• VEND_NO
• INV_AMT

Data Types

• The data type “char” means alpha numeric and should be left justified.
• The data type “numeric” means numeric and is a free form text format.  It can be placed anywhere in the space reserved.  The following are examples of valid numeric data:
•  1.25
• -1.25
• +1.25
• 1.25-
• 1.25+
• 1.2
• 1
• 01.25

Make sure to verify the size and decimal precision capability of each numeric field.

• The date field is in YYYYMMDD format and it is always 8 bytes.

Header Record Layout (APTRXFIL)

Detail Record Layout (APTRXDST)

An Example of an Interface File

The interface file is a fixed length ASCII file in line sequential file format.  The position of each field is very important.  Elliott will allow CR/LF to terminate a record prematurely.  This assumes that you would leave the remaining fields in the record as blank.  The following example shows there are two distributions for the first H record, one distribution for the second H record and no distributions for the last H record.  When there are no distributions, it assumes the vendor has auto distribution set up.