Overview
The AGR data interface documentation is your comprehensive guide to understanding how AGR processes data. It outlines the specific format and structure your data needs to adhere to for seamless integration with AGR.
Your data can originate from various sources, but it must be mapped according to the guidelines provided in this documentation before it is pulled into AGR. The staging and mapping of your data to the AGR data interface can be owned and maintained by
your internal IT team
a third party IT or integration partner
AGR integrations team
AGR daily job
AGR is synced with data from the AGR data interface nightly. After the data sync, AGR updates any calculations performed by the system. The AGR data interface must be updated before the data sync begins to ensure the data presented in AGR is up to date.
Data format
AGR stores data in SQL. However, the data can be presented to AGR in either SQL tables or CSV files. See Grant AGR access to your data for data format requirements.
Additionally, when presenting your mapped data to AGR, the following requirements apply:
SQL
Table and column names must be identical to the ones listed in the supplied table descriptions.
Table definitions and column data types must match the AGR Data Interface documentation.
CSV
File names must be consistent with the table names provided in the section List of Tables. Naming is case-sensitive.
Headers in the files must be consistent with the column names within each table described in the section List of Tables. Naming is case-sensitive.
Data types
The table below shows the types of data that are stored in the SQL tables listed in the next section and a short description of each data type.
Type | Description |
nvarchar(N) | Is a variable-length Unicode string data type. It can store up to N characters. The "n" stands for national, indicating that it supports the Unicode character set. |
smallint | Is a 2-byte integer data type that can store integer values in the range of -32,768 to 32,767. |
decimal(N,M) | Is a fixed-point numeric data type. In this case, it can store numbers with up to N digits, with M of them to the right of the decimal point. |
bit | Is a data type that can store either 0, 1, or NULL where 0 typically signifies false and 1 signifies true. |
date | Is a data type used to store date values in the format 'YYYY-MM-DD'. It does not store time information, only the date. |
tinyint | Is a 1-byte integer data type that can store values in the range of 0 to 255. |
List of tables
The list below provides the table names for the SQL tables where AGR stores the data. Click on the table name to get more information about its properties and columns.
Some basic master data is required for AGR to function, but other data may be unavailable or unapplicable to your setup, in which case the tables or columns can be omitted.
Note
Some data columns, e.g. lead time, are found in multiple tables. This allows you to easily map data to the appropriate data dimension.
For example, if your lead times are registered per vendor, you can simply map the vendor lead time into the VENDOR table, and omit the lead time column in other tables. If you also store lead times per Item, e.g. if an item has a lead time different from the vendor lead time, you can provide the lead time for that item in the ITEM table, and this will take precedence over the vendor lead time.
Remember, validation ensures accurate mapping. For details, check out the Validate your master data article.
Table name | Importance | Description |
Required | All locations that hold stock (warehouses, stores, etc.). This may be limited by the project scope. | |
Required | All items that need to be visible in AGR. Excluding inactive items with no stock or other items that don't need to be seen in AGR. | |
Preferred | All active item groups that the items used in AGR can fall within. | |
If applicable | Location-specific data for items in AGR. Can be left empty if no data is kept at that level. However, if any data is kept at this level, this table should be populated for all items. | |
Required | All active vendors | |
If applicable | Item purchase order route data for items in AGR. Can be left empty if no data is kept at that level. | |
If applicable | All active or recently active customers | |
Required | Sales history for all items | |
Required | Stock history for all items | |
Required | Current available stoc. | |
Required | Undelivered quantity in purchase orders | |
If applicable | Undelivered quantity in transfer orders | |
If applicable | All open sales orders | |
If applicable | All open sales orders per item | |
If applicable | All BOM components that are meant to be planned and ordered in AGR | |
If applicable | Historical consumption of BOM components and products |
Supporting documentation
Table structure and table relationships can provide valuable insights into the overview of tables when staging the data yourself. We recommend utilizing this interactive visualization of the tables to make staging the data easier.
If presenting data to AGR via SQL, you can use the below scripts to create the staging tables or views, before you start mapping your source data into the AGR data interface.