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 1st 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)

Column name	Comment	Data Type	Size	Position	Dec
RECORD_TYPE	Always “H”	Char	1	1
VCHR_NO	Typically Blank	Numeric	6	2	0
VEND_NO_ALT	Typically Blank	Char	6	8
INV_NO_ALT	Typically Blank	Char	8	14
VCHR_DATE		Date	8	22
TRX_TYPE	Typically “V”	Char	1	30
VEND_NO	Required	Char	6	31
PO_NO		Char	10	37
INV_DATE		Date	8	47
INV_NO		Char	8	55
INV_APPLY_TO	Typically Blank	Char	8	63
INV_AMT	Required	Numeric	10	71	2
NON_DSC_AMT		Numeric	10	81	2
MISC_AMT		Numeric	10	91	2
TAX_AMT		Numeric	10	101	2
FREGIHT_AMT		Numeric	10	111	2
DSC_MISC_FLG		Char	1	121
DSC_TAX_FLG		Char	1	122
DSC_FRT_FLG		Char	1	123
DUE_DAYS		Numeric	3	124	0
DUE_DATE		Date	8	127
DSC_DAYS		Numeric	3	135	0
DSC_DATE		Date	8	138
DSC_PCT		Numeric	8	146	3
DSC_AMT		Numeric	10	154	2
VCHR_REF		Char	30	164
MAIN_AP_ACCT_NO		Char	8	194
SUB_AP_ACCT_NO		Char	8	202
DP_AP_ACCT_NO		Char	8	210
CHK_NO	For “P”repaid trx type only	Numeric	6	218	0
CHK_DATE	For “P”repaid trx type only	Date	8	224
MAIN_CASH_ACCT_NO		Char	8	232
SUB_CASH_ACCT_NO		Char	8	240
DP_CASH_ACCT_NO		Char	8	248
GST_AMT	Canada Only	Numeric	10	256	2
DSC_GST_FLG	Canada Only	Char	1	266
REMIT_TO		Char	6	267
FILLER_001	Reserved	Char	33	273

 

Detail Record Layout (APTRXDST)

Column Name	Comment	Data Type	Size	Position	Dec
RECORD_TYPE	Always “D”	Char	1	1
VCHR_NO	Typically Blank	Numeric	6	2	0
SEQ_NO	Typically Blank	Numeric	3	8	0
MAIN_ACCT_NO		Char	8	11
SUB_ACCT_NO		Char	8	19
DP_ACCT_NO		Char	8	27
DIST_AMT		Numeric	10	35	2
PO_NO	Typically Blank	Char	10	45
PO_LINE_NO		Numeric	3	55	0
ITEM_NO		Char	15	58
ITEM_INV_QTY		Numeric	13	73	3
ITEM_UOM		Char	2	86
JOB_NO		Char	6	88
VAR_FLAG	Typically Blank	Char	1	94
OLD_ACT_COST	Typically Blank	Numeric	11	95	4
MAIN_ASST_ACCT_NO	Typically Blank	Char	8	106
SUB_ASST_ACCT_NO	Typically Blank	Char	8	114
DP_ASST_ACCT_NO	Typically Blank	Char	8	122
RECV_DATE	Typically Blank	Date	8	130
RECV_DOC_NO	Typically Blank	Numeric	6	138	0

 

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.

 

H                             00010012345600  2003100612345678        0002500.00+

D         000012340000010000000000  1300.00+                       CPU            000000010.00+

D         000012340000010000000000  1200.00+                       CLOCK                     10

H                             000200          20031010I123456             100.00+

D         000050500000000000000000   100.00+

H                             000300          20031007111222                50.25