FISCAL has been built and optimised for processing invoice data, allowing customers to utilise the duplicate and exception matching technology to identify potential risks.
Find out more about Transactions module of FISCAL here: Transactions Factsheet
In this article:
- Transaction data overview
- Transaction data files and format requirements
- Transaction data fields
- Sample transaction file
Transaction data overview
Transaction types you should include in your extracts:
- Invoices
- Credit Notes
- Reversals
- Cancellations
Transaction types you should not include in your extracts:
- Expenses payments, including payments to employees as well as patients or service users
- Payment records relating to an invoice
Including non-invoice information in your extracts can lead to an increase in false positive risk detection, which will thereby increase the workload in reviewing the resulting risks.
Transaction data files and format requirements
Files
If you are extracting data to implement FISCAL you will need to produce both a historical extract and a daily extract; if you are extracting data for a Proof of Value Review you only need the historical extract.
- Historical Extract - one off upload containing your historical transaction data as stated in your FISCAL contract/agreement, usually a period of 2-3 years.
- Daily Extract - this should contain any new or updated transactions since the previous extract was taken. Updates may include revised transactions details or payment status or similar.
Format
Extracts must be in one of the following formats .csv, .tsv, .xlsx or .txt format. For .txt files the values must be separated by commas. For .csv files, the file must be encoded as UTF-8.
Specific requirements for transaction data
- Only include gross header invoice values, do not include line items.
- Only include Invoices, Credit Notes, Cancellations and Reversals, do not include payment records or expenses.
- All text fields should be qualified with double quotes (e.g. "Test Supplier, Ltd"). Any double quotes occurring inside a text field must be escaped with another double quote (e.g. 50” Television becomes “50”” Television”).
- All date fields must be in the same date format. FISCAL accepts many different formats for dates, including dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd etc. Timestamps can also be included in the format hh:mm:ss.
- You may provide additional fields for data that is relevant to your organisation. These fields will be for display purposes only.
- Do not use placeholders in any fields, such as ‘0’,’N/A’,’-’, etc.. If data isn’t available, leave it blank.
Transaction data fields
| Field | Description | Purpose | Format |
|---|---|---|---|
| Unique ID |
A unique ID generated within your accounting system that identifies a transaction; if no such ID exists (e.g. if you recycle document numbers) then it is acceptable to derive one as long as it is unique to that transaction (e.g. a common approach is to concatenate document number+company code+fiscal year). Only the latest instance of a transaction should be included in the file - multiple instances of a unique ID will not be imported. |
Used to identify a transaction and update the transaction in FISCAL when it is re-uploaded with changes. Mandatory |
Alphanumeric Character limit: 255 |
| Invoice Number | The invoice document reference from the supplier. |
Used for risk analysis. Mandatory |
Alphanumeric Character limit: 255 |
| Invoice Amount | Total gross value of the invoice without the currency symbol. Invoices should be positive amounts, credit should be negative amounts or vice versa (negative sign to left of amount). |
Used for risk analysis. Mandatory |
Numeric Character limit: 14 digits + 5 decimals |
| Invoice Amount Currency | The ISO 4217 code denoting the currency in which the invoice is issued. |
Currently only for information, but likely used in future enhancements to FISCAL. Recommended |
Alphanumeric Character limit: 3 |
| Base Amount |
Gross invoice value converted into local/base/reporting currency. Invoices should be positive amounts, credit should be negative amounts or vice versa (negative sign to left of amount). If you transact in single currency, the Invoice and Base Amounts will match; if you transact in multi currency, then the Base Amount should represent the invoice in your accounting system's single base currency. |
Provides accurate reporting in a single currency where invoices are in multiple currency values. Mandatory if transact in multi currency. |
Numeric Character limit: 14 digits + 5 decimals |
| Base Amount Currency | The ISO 4217 code denoting the currency in which the invoice amount is reported. |
Currently only for information, but likely used in future enhancements to FISCAL. Recommended if transact in multi currency. |
Alphanumeric Character limit: 3 |
| Description | A summary of the goods or services that the invoice covers. |
Can improve risk matching and is helpful for users reviewing presented risks. Recommended |
Alphanumeric Character limit: 2000 |
| PO Number |
Purchase Order number from your accounting system relating to the invoice. If you have multiple PO Numbers for one invoice, please populate this field with the first PO and include the other PO's, concatenated into a single comma-separated list, in an additional field. |
Can improve risk matching and is used for some reports. Recommended |
Alphanumeric Character limit: 255 |
| Invoice / Payment Status | Status of the invoice, e.g. whether paid or not, could also include other statuses such as approved, cancelled, etc. |
Helpful to AP staff reviewing the presented risks, supports Statement Reconciliation functionality. Recommended |
Alphanumeric Character limit: 255 |
| Invoice Date | Date when the invoice was raised by the supplier. |
Used for risk analysis and some reports. Mandatory |
Date |
| Received Date | Date when invoice was received by your business. |
Required for Payment Practices Report to generate and is used for other reports. Recommended |
Date |
| Entered Date | Date the invoice was entered into your accounting system. |
Used for risk analysis and some reports. Mandatory |
Date |
| Due Date | Date the invoice is due for payment. |
Required for Payment Practices Report to generate and is used for other reports. Recommended |
Date |
| Paid Date | Date the invoice was paid. |
Used for risk analysis, required for Payment Practices Report and other reports. Recommended |
Date |
| Payment Cleared Date | Date when invoice payment has cleared in the supplier's bank account. |
Used for Payment Practices Report, but if you don’t record this date, there is functionality to set a default number of days post Paid Date per payment type. Optional |
Date |
| Payment Type | Method of payment for this invoice, e.g. Bacs, Chaps, Cheque | Required for Payment Practices Report |
Alphanumeric Character limit: 255 |
| VAT Amount Paid | Amount of VAT paid on the invoice |
Useful for reporting. Optional |
Numeric Character limit: 14 digits + 5 decimals |
| Supplier Name | Supplier name from your accounting system. |
Links the transaction to the Supplier. Mandatory |
Alphanumeric Character limit: 255 |
| Supplier Ref |
A unique ID generated within your accounting system that identifies the supplier. This value must match the UID field in the supplier extract file for the same supplier. |
Links the transaction to the Supplier. Mandatory |
Alphanumeric Character limit: 255 |
| Site Ref | Where you have multiple sites relating to one supplier (e.g. different locations / branches), the Site Ref is a Shortname/code for each site for the supplier. |
Links the transaction to the Supplier site. Optional |
Alphanumeric Character limit: 255 |
| Supplier Type | Category that classifies the supplier based on what they provide or their relationship with your organisation for example. |
For information. Can be used for categorising transactions. Optional |
Alphanumeric Character limit: 255 |
| User ID | ID or username of the user or system that created the invoice in your accounting system. |
Can improve risk matching and is used for some reports. Recommended |
Alphanumeric Character limit: 255 |
| Scanned Image | Hypertext link or directory path to a scanned image of the invoice stored in your accounting system or elsewhere. |
Helpful to AP staff reviewing the presented risks, saves them having to search the ERP separately. Recommended |
Hyperlink Character limit: 2000 |
| Org Group Ref |
Reference from your accounting system identifying the Organisation Group (Org Group) to which the transaction is related Org Groups are separate entities within your organisation, often identified by different Company Codes or Legal Entities, that may have different access or contractual agreements with Suppliers. |
Links the transaction to a specific OrgGroup which can then be used to filter risks. Optional |
Alphanumeric Character limit: 255 |
| Org Group Name |
The name of the Org Group from your accounting system. This value must match the Org Group Name field in the supplier extract for the same Organisation Group. |
Links the transaction to a specific OrgGroup which can then be used to filter risks. Optional |
Alphanumeric Character limit: 255 |
| ERP ID |
This field will indicate from which accounting system the transaction originates. If there is only one accounting system then this field is not required. If there is more than one accounting system then each system must be uniquely identified with its own ID Use the same ERPID as used in the Master Supplier data file for each accounting system. |
To identify which finance system the transaction relates to when there is more than 1 live system or more than 1 entity within the system. Recommended if multi systems |
Alphanumeric Character limit: Numeric: up to 32,767 Alphanumeric: up to 50 characters |
| Modified Date | Date the transaction was last modified in any way in your accounting system (including being paid); might also be referred to as the Last Edit Date. |
Identifies the latest version of the transaction when updated versions of a transaction is uploaded. Recommended |
Date |
| Extract Date | Date the file was created (or the transaction was extracted if directly uploading from your ERP). |
Identifies the latest version of the transaction when updated versions of a transaction is uploaded. Recommended if Modified Date is not provided |
Date |
Sample transaction file
ERPID,UniqueID,PONumber,InvoiceNumber,InvoiceAmount,BaseAmount,Description,UserID,ExtractDate,InvoiceDate,ReceivedDate,EnteredDate,ModifiedDate,DueDate,PaidDate,PaymentClearedDate,PaymentType,ScannedImage,SupplierRef,SupplierName,SiteRef,OrgGroupRef,OrgGroupName
1,"0010043533","010081173","31496500",372.47,372.47,"Accountancy Services","HD011",2020-01-19 00:01:00,2018-12-09 00:00:00,2018-12-18 00:00:00,2018-12-19 00:00:00,2018-08-23 10:03:00,2015-01-01 00:00:00,2015-01-09 00:00:00,2015-01-16 00:00:00,"BACS","http://Image265451","02188","Wright Stuff","S001","OG001","CC001"
1,"0010043506","010081809","9220590399",49.77,49.77,"Legal Services","SS023",2020-01-19 00:01:00,2018-12-11 00:00:00,2018-12-18 00:00:00,2018-12-19 00:00:00,2018-08-23 10:03:00,2015-01-09 00:00:00,2015-01-09 00:00:00,2015-01-29 00:00:00,"DD","http://Image265443","02133","Carter Law LLP","S002","OG002","CC002"
1,"0010043509","010081543","9220586288",-112.37,-112.37,"Toner","HD011",2020-01-19 00:01:00,2018-12-08 00:00:00,2018-12-18 00:00:00,2018-12-19 00:00:00,2018-08-23 10:03:00,2015-01-09 00:00:00,2015-01-09 00:00:00,2015-01-28 00:00:00,"DD","http://Image265447","0568","Print Supplies Ltd","S003","OG003","CC003"
1,"0010043485","010081180","132370",700.16,700.16,"Consultancy","SS023",2020-01-19 00:01:00,2018-12-02 00:00:00,2018-12-18 00:00:00,2018-12-19 00:00:00,2018-08-23 10:03:00,2015-01-15 00:00:00,2015-01-09 00:00:00,2015-01-31 00:00:00,"CHEQUE","http://Image265452","02598","Bull Information Systems Ltd","S004","OG004","CC004"
1,"0010043496","010081122","132534",77,77,"Motor Spares","SS023",2020-01-19 00:01:00,2018-12-08 00:00:00,2018-12-18 00:00:00,2018-12-19 00:00:00,2018-08-23 10:03:00,2015-01-05 00:00:00,2015-01-09 00:00:00,2015-01-17 00:00:00,"BACS","http://Image265445","03679","Ridgeway Ford","S005","OG004","CC005"