Skip to content

APINVIMS Accounts Payable Invoice Import Utility

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)

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.




Feedback and Knowledge Base