Home > Reports > Screen Shots/Explanations > Access Interface Report Format

Access Interface Report Format

By running the Access Interface report REQUESTREPORTEOD.EXE AccessInterface information is extracted in a comma-delimited text file and generates five csv files.

Department Sales

The DepartSales.CSV file contains the sales and VAT for each department. The report shows the department sales is summarised by Location and Department, Date and VAT code.

Field Name

Comment

Data Source

AnalysisCode

The sales code used by the accounts interface for the location/department. (Sales A/c on the Categories tab in Location Accounts).

ACCOUNTDEPT.STRSALESACCOUNT

Location

The POS BE location code.

DAILYSALES.STRTRADECODE

Department

The REBE department code.

ITEM.STRDEPTCODE

Date

The date the adjustments took place. Date in default format (d/m/y).

DAILYSALES.DTMTRADEDATE

VATCode

The VAT Code used.

DAILYSALES.STRTAXCODE

Sales

The ex-tax sales for the location/department.

DAILYSALES.CURSALES

VAT

The tax on the sales for the location/department.

DAILYSALES.CURTAXAMT2

TotalSales  

sales+vat

 

Example of DeptSales.CSV

AnalysisCode,Location,Department,Date,VATCode,Sales,VAT,TotalSales

1234,1,1,1/10/2010,1,1000,175.5,1175.5

1234,1,2,1/10/2010,0,1000,175.5,1175.5

1234,1,3,1/10/2010,1,1000,175.5,1175.5

Stock

The Stock.CSV file contains the stock movements made within the system, reported at a Dept level. Stock figures are summarised figures and grouped by location instead of department. All values are positive amounts as Access will automatically debit or credit stock control depending on the type of transaction.

Field Name

Comment

Data Source

AnalysisCode

The sales code used by the accounts interface for the location/department. (Sales A/c on the Categories tab in Location Accounts).

ACCOUNTDEPT.STRSALESACCOUNT

Location

The POS BE location code.

DAILYSALES.STRTRADECODE

Date

The date the adjustments took place. Date in default format (d/m/y).

DAILYSALES.DTMTRADEDATE

Type The type of transaction. You should be able to just use the STOCKAUDIT.STRSOURCE field, for example GR for Goods Receipt, TFRO for Transfer Sends, and so on. STOCKAUDIT.STRSOURCE
CostValue Value of the stock adjustment. STOCKAUDIT.CURLANDEDCOST * STOCKAUDIT.DBLCOSTQTYADJUST

Example of Stock.CSV

AnalysisCode,Location,Date,Type,CostValue

1233,1,01/08/2010,POS,1234.44

1234,1,01/08/2010,PO,2234.44

1235,1,01/08/2010,GR,2134.55

1236,1,01/08/2010,returns to supplier,123.33

1237,1,01/08/2010,TFRO,400.55

1238,1,2,01/08/2010,TFRI,678.44

1239,1,1,01/08/2010,STKA,1234.33

Goods Receipt

The GoodsReceipts.CSV file shows the good receipt data.

Field Name

Comment

Data Source

Location

The POS BE location code.

GRHEADER.STRTRADECODE

GRNumber

The goods receipt number.

GRHEADER.LINTGRNUMBER

PONumber

The purchase order number. A GR can be linked to multiple PO. The PO number should come the detail line, not the header.

GRDETAIL.LINTPONUMBER

SupplierDeliveryRef

Supplier delivery reference.

GRHEADER.STRSUPPREFERENCE

SupplierCode

POS BE supplier code.

GRHEADER.STRSUPPLIERCODE

OrderDate

The date the PO was ordered.

 

RequiredByDate

The date the PO was required by.

 

IPN

The number of the item.

GRDETAIL.LINTITEMNUMBER

OrderCode

The order code of the item.

GRDETAIL.STRORDERCODE

Description

Description of the item. Commas should be stripped from the description if they exist.

GRDETAIL.STRDESCRIPTION

Quantity

Number of units received.

GRDETAIL.DBLQTYRECEIVED

UnitCost

Unit cost of the item.

GRDETAIL.CURLANDEDCOST divided by GRDETAIL.INTCARTONSIZE

DeliveryDate

The date the GR was received.

GRHEADER.DTMRECEIVEDDATE

VATCode

The VAT code used from the purchase order.

link back from GRDETAIL.LINTPONUMBER back to the PODETAIL

Example of GoodsReceipts.CSV

Location,GRNumber,PONumber,Supplierdeliveryref,Suppliercode,Orderdate,Requiredbydate,IPN,Ordercode,Description,Quantity,Unitcost,Deliverydate,VATCode

1,14,12,18/05/1903,ABC,01/08/2010,12/08/2010,1234567,07/12/2812,gucci bag,5,25,12/08/2010,1

1,14,12,18/05/1903,ABC,01/08/2010,12/08/2010,1234568,07/12/2812,Hermes bag,10,30,12/08/2010,1

1,14,12,18/05/1903,ABC,01/08/2010,12/08/2010,1234569,07/12/2812,Mulberry bag,8,40,12/08/2010,0

Tenders

The Tenders.CSV file contains the tenders made on sales, reported at a tender level. Tenders are summarised figures, by date and tender type, not individual transactions.

Field Name

Comment

Data Source

AnalysisCode

The code used by the accounts interface for the location/tender. This can be obtained from the ACCOUNTTENDER.strGLAccount field (Tender A/c on the Tenders tab in Location Accounts).

ACCOUNTTENDER.STRGLACCOUNT

Location

The POS BE location code.

DAILYSALES.STRTRADECODE

Date The date the payment took place. Date in default format (d/m/y). DAILYSALES.DTMTRADEDATE
Description The tender description TENDER.STRTENDERDESCRIPTION
Value The value of the tender. DAILYSALES.CURTENDERAMT

Example of Tenders.CSV

AnalysisCode,Location,Date,Description,Value

1233,1,01/08/2010,cash,123.44

1234,1,01/08/2010,cheque,234.44

1235,1,01/08/2010,creditcard,2134.55

VAT

Field Name

Comment

Data Source

AnalysisCode

The sales code used by the accounts interface for the location/department.

ACCOUNTDEPT.STRSALESACCOUNT

Location

The POS BE location code.

DAILYSALES.STRTRADECODE

VATCode

The VAT Code used.

DAILYSALES.STRTAXCODE

Date

The date the adjustments took place. Date in default format (d/m/y).

DAILYSALES.DTMTRADEDATE

Sales

The ex-tax sales for the location/department.

DAILYSALES.CURSALES

VAT

The tax on the sales for the location/department.

DAILYSALES.CURTAXAMT2

TotalSales  

sales+vat

 

Example of VAT.CSV

AnalysisCode,Location,VATCode,Date,Sales,VAT,TotalSales

1233,1,1,01/08/2010,1000,175.5,1175.5

Converted from CHM to HTML with chm2web Pro 2.85 (unicode)