Export File Formats Created By SMCP

Post date: Jul 01, 2011 8:49:38 PM

The SMCP communicates with different General Ledger and Accounts Payable 'backend' programs by creating CSV ('comma-delimited') files that can be imported into the customer's own choice of GL and AP modules.  When transactions are posted, these files are created.

When an Inventory or Accounts Receivable batch is posted, an export file is created which is intended to be imported into the customer's General Ledger program.  When a batch of Accounts Payable invoices is posted in the Inventory module, it creates an export file which is intended to be imported into the customer's own Accounts Payable module.

What follows is a description of the export file formats for export files which will be imported into the ACCPAC Acocunts Payable and General Ledger modules.

These files have different types of records (the first 2 or 3 lines in the file define the columns - after that each line is a 'record') in each export - there is a 'header' type, and one or two 'detail' types.

The naming convention for AP invoice export files works like this:

A typical name might be: 'AP-IC-INVOICE-000001-2010-10-07 02_01_24 PM.CSV' indicating that it is an export file intended to be imported into the customer's 'AP' module, it originated in the SMCP 'IC' module, it came from IC batch number 1, and was created (i.e., the batch was posted) at 2:01:24 PM on 10/7/2010.

In the AP export, the header fields are: 

'RECTYPE' - record type (header, detail, or payment - in this case header)

'CNTBTCH' - the GL batch number (zero because the GL itself will assign a batch number)

'CNTITEM'  - the entry sequence, i.e., the first invoice is 1, the next is 2, etc.

'IDVEND' - the vendor's account number

'IDINVC' - the invoice number

'TEXTTRX' - the 'type' of transaction (always an invoice for AP imports)

'IDTRX' - the 'class' of transaction (specific to ACCPAC only)

'PONBR' - Number of the purchase order which the invoice was applied against

'INVCDESC' - a short description/remark field for the invoice

'DATEINVC' - invoice date in YYMMDD format

'TERMCODE' - the 'terms code' for the invoice (from a 'vendor terms' list)

'DATEDUE' - date the invoice is due

'DATEDISC' - discount date for the invoice

'PCTDISC' - discount percentage allowed for this invoice

'AMTDISCAVL' - discount amount available

'AMTDUE' - amount due

'AMTGROSTOT' - total amount due

In the AP export, the FIRST detail field is an 'invoice' detail and its fields are:

'RECTYPE' - record type (header, detail, or payment - in this case, detail)

'CNTBTCH' - the GL batch number (zero because the GL itself will assign a batch number)

'CNTITEM'  - the entry sequence, i.e., the first invoice is 1, the next is 2, etc.

'CNTLINE' - the line sequence (the first line for this entry is 1, the second line is 2, etc.)

'TEXTDESC' - a description of the line or 'remark'

'IDGLACCT' - the GL account code for the distribution (the balancing entry is the AP Control account)

'AMTDIST' - the amount of the line

In the AP export, the SECOND detail field is a 'payment' type of detail and its fields are:

'RECTYPE' - record type (header, detail, or payment - in this case payment)

'CNTBTCH' - the GL batch number (zero because the GL itself will assign a batch number)

'CNTITEM'  - the entry sequence, i.e., the first invoice is 1, the next is 2, etc.

'CNTPAYM' - the payment line sequence (the first line for this entry is 1, the second line is 2, etc.)

'DATEDUE' - date the payment is due

'AMTDUE' - the amount due

'DATEDISC' - the discount date

'AMTDISC' - the amount of the discount

The naming convention for GL export files works like this:

A typical name might be: 'GL-IC-Shipment-000873-2011-07-01 12_21_42 PM.CSV', indicating it is a file to be imported into the 'GL', created by the 'IC' module, it's a batch of 'Shipment's, the batch number (in IC) was 873, and it was created (i.e., the batch was posted) at 12:21:42 PM on 7/1/2011.

In the GL export files (currently the SMCP exports IC transactions and AR transactions to the customer's General Ledger), the header fields are:

'RECTYPE' - the record type (header or detail, in this case the 'header' type)

'BATCHID'  - the batch number (zero because the General Ledger will autoincrement the batch number when it's imported)

'BTCHENTRY' - the entry sequence - '1' for the first entry, '2' for the second, etc. 'SRCELEDGER' - this corresponds to the source code set up in ACCPAC (e.g. 'AR' for Accounts Receivable, 'IC' for Inventory, etc.)

'SRCETYPE' - the source 'type' - also corresponds to a source 'type' set up in the customer's ACCPAC system, for example 'SH' might be a shipment, 'IN might be an invoice

'JRNLDESC' - a short description of the batch

'SRCDESC' - 'Source Description' - usually 'SMIC' to identify that this batch orginated in the Service Manager Inventory Control module, or 'SMAR' for AR, etc.

In the GL export files, the detail fields are:

'RECTYPE'' - the record type (header or detail, in this case the 'detail' type)

'BATCHNBR'  - the batch number (zero because the General Ledger will autoincrement the batch number when it's imported - same as BATCHID in the header record)

'JOURNALID' - the entry sequence - '1' for the first entry, '2' for the second, etc. - same as BATCHENTRY in the header

'TRANSNBR' - consecutive sequence numbers for each transaction - these are in multiples of 20, so the first transaction is '20', the next is '40', etc.  They start at 20 again with each new entry.

'ACCTID' - the GL account

'TRANSAMT' - the amount of the transaction

'TRANSDESC' - a short description of the transaction

'TRANSREF' - a second description field, usually carrying memo or remark information

'TRANSDATE' - date of the transaction

'SRCELEDGER' - this corresponds to the source code set up in ACCPAC (e.g. 'AR' for Accounts Receivable, 'IC' for Inventory, etc.) (same value as SRCELEDGER in the header record)

'SRCETYPE' - the source 'type' - also corresponds to a source 'type' set up in the customer's ACCPAC system, for example 'SH' might be a shipment, 'IN might be an invoice (same value as the 'SRCETYPE' in the header

'COMMENT' - Additional information field for comment/remark, etc.

Samples of export files for ACCPAC:

https://sites.google.com/site/airotechservicemanager/downloadables/AP-IC-INVOICE-000001-2010-10-0702_01_24PM.CSV?attredirects=0&d=1

https://sites.google.com/site/airotechservicemanager/downloadables/GL-IC-Shipment-000873-2011-07-0112_21_42PM.CSV?attredirects=0&d=1