Follow

SFTP-CSV (AX) Integration Documentation

Index

  1. Purpose
  2. Terms and Definitions
  3. Integration Configuration
  4. File Exchange Location
  5. Exchange Rules
  6. Filename rules
  7. Local Client Storage Rules
  8. Proposed file-exchange-workflow
  9. File Exchange Queue
  10. Fiix Record Reconciliation Process
  11. Exchange Responsibilities
  12. Exchange Schema and Concepts
  13. Relational Model
  14. CSV File Formats

Purpose

The purpose of this document is to familiarize the reader with the high level details of the integration, comprised of the concepts and mechanisms required to transfer records of data between the two systems being integrated. Both the logical structure (data model) and file contents structure (CSV columns) will be discussed in detail so that the implementer of the integrating system(s) have a full understanding of the information being transferred. Subsequently, the implementer(s) will be able to take this information and ensure that the integrating system is able to meet the file format requirements, as well as the sequences of file exchange needed for the workflows to be successful.

Terms and Definitions

The CMMS - This refers to the instance (belonging to the Integration Partner’s company) of Fiix software that is involved with the integration.

Integration Partner / Partner System - This refers to the system that the Fiix software will be integrating with. This is not limited to Microsoft Dynamics, and may include any complementing middleware or intermediate system that assists in the integration. When describing the schema or record structure of the target system, it refers to the structure at the point of transfer, not necessarily the underlying schema of the AX system etc.

Internal IDs - These refer to the unique identifiers for the various record types in the Integration Partner’s system. The internal ID for a Purchase Order in the partner’s system could be 1234, PO1234, or any other sort of number or string identifier. These internal IDs will be used to relate one record type to another, for example, Purchase Order Line Items to Purchase Orders etc.

Integration Configuration

  • Upon creation of a new AX integration, users can configure the SFTP address and username, and path structure etc.
  • It is responsibility of the integrating partner to ensure the Fiix public key is installed onto the partner SFTP server’s authorized_keys.
  • Once the key pair exchange is complete and working, the usual external integration can be added to the CMMS as usual. 

File Exchange Location

 

  • The exchange location will be on the Integration Partner System, accessed with the SFTP key authentication method described above. Within the exchange location, there should be a folder for archived records; These are files that have been successfully downloaded and processed by the CMMS. The files are placed in this folder on when they have been successfully processed by the CMMS.

Exchange Rules

  • The CMMS checks to see if there is a file in the exchange location; it should be marked with the record type and direction (PurchaseOrder_timestamp_out) etc.
  • After processing the file, the file should be moved to an archive location. (Different from the exchange location)
  • If there are multiple files in the exchange location, they will be processed in chronological+business logical order (If there are PO files, and Receipt files, the PO files will be read first. This is typically pre-defined by the workflow actions in the CMMS)
  • Files or records that are skipped/ignored by the user in the reconcilition dialog(s) will be sent back to the integration partner’s system, in a “Not Processed” csv. The partner system will deal with this accordingly. 

Filename rules

  • The filename should reflect the record type, date, and direction of consumption. “out” means that it is leaving the partner’s system, destined for the Fiix software instance. “in” would suggest a file destined for the partner’s system.
  • The date stamp allows for processing of the files in a chronological order.

Local Client Storage Rules

  • The integration mechanism will not save any files to the filesystem of the CMMS application server
  • Files will be streamed across via SFTP during processing and will be archived to Amazon S3 secure storage for audit/historical purposes

Proposed file-exchange-workflow 

Fiix

sFTP Adapter Call

Data Exchange Location (sFTP server)

API call / CSV Processing Logic

AX

Order submitted for approval

PurchaseOrder_timestamp_in.csv

PO Created in AX

AX PO sync’d with Fiix PO

PurchaseOrder-timestamp_out.csv

AX PO Number(s) filled into the CSV file

Receipt Created/Updated and stock updated

Receipt_timestamp_out.csv

AX Receipt created

Account Codes updated/created

AccountCode_timestamp_out.csv

Account Codes

Charge Dept.

updated/created

ChargeDept_timestamp_out.csv

Charge Depts

Supplier(Vendor) updated/created

Supplier_timestamp_out.csv

Suppliers

Other dependencies

...

Assets/Facilities/Inventory/Billing Terms/ Stock Locations /

1* See the section below for what the Partner system responsibilities are once

  • The CMMS creates a CSV file containing the Purchase Order record, conforming to the predefined CSV format and uploads it to the exchange location
  • The CSV uploaded to the file exchange location is a “new” purchase order and does not contain the internal IDs of the partner system.
  • The target system reads in the file, processes the contents and registers the PO as a new PO in their system.
  • The target system writes a CSV file back to the exchange location for the CMMS to pick up. It has the same predefined CSV format, but this time with the external ID fields filled in.
  • The target system, over the course of time may push multiple files of the same record type to the exchange location. The files will be processed in chronological order, as a FIFO queue.
  • The CMMS downloads and processes the returned CSV file, updating the references to the target system’s IDs and records.
  • The CMMS goes through the normal reconciliation process and records get created/updated etc.
  • If a record is not processed/skipped by the user in the reconciliation process, it will be sent back to the exchange location into a “skipped” CSV
  • All files that were downloaded and processed by the CMMS will be archived in the partner system’s sFTP exchange location, under an “archived” folder. 

Partner System Processing Responsibilities

File Exchange Queue

  • If multiple files of the same record type exist, (PurchaseOrder_1_out.csv, PurchaseOrder_2_out.csv, PurchaseOrder_3_out.csv), they will be processed in chronological order (1, then 2, then 3).
  • The order in which files of different business record types will be processed is based upon how the workflows in both the CMMS and the Integration Partner systems are configured. If Receipts depend on Pre-existing Purchase Orders, it is the responsibility of the engaged systems to ensure that dependency is respected. 

Fiix Record Reconciliation Process

Once the CSV files have been read and its contents have been processed by the CMMS, the system has the option of allowing the user to choose or configure choices, for exactly which records they want to sync down to the CMMS. For example, if records are read from the CSV file placed by the Partner system into the the exchange location, and the user decides to ignore or skip one or more records, the CMMS will only sync the items that are selected. The remainder will be re-uploaded to the exchange location, marked as purchaseorders_ma_<timestamp>_skipped.csv.

In addition to skipping and accepting incoming records, the CMMS can “bind” records together, if the user chooses or configures the system to do so. This will allow in some cases, where say the CMMS already contains an account code for a Partner System account code, and instead of creating an extra one, it allows the system to simply mark in the CMMS that the two records are the same and consolidate them into a single record for any future synchronizing actions.

Exchange Responsibilities 

While the exchange location gives the two systems the ability to transmit files from one to another, each system has responsibilities for processing and uploading files in order to complete the desired workflows. For example, if an “IN” file is placed at the file exchange location, the partner integration system is responsible for reading and processing the file, and subsequently performing any needed action to ensure the records in question are created or updated.

  • Fiix
    • Successfully processed “OUT” files, will be archived
    • Responsible for creating “IN” files, representing records to be created
    • Responsible for creating “SKIPPED” files, representing records that were skipped by the user during the reconciliation process 
  • Integration Partner
    • Responsible for creating “OUT” files, for the Fiix software to read and process
    • Responsible for reading and managing “SKIPPED” files and dealing with these accordingly
    • Responsible for reading and processing “IN” files

Relational Model

Fixed Assets (Tools/Equipment etc.) 

Fixed assets are typically non-consumable in nature, and would be quantified in single units. Compare this to inventory items which are often consumable and are quantified in multiples; (Forklifts are considered Assets, with each Forklift being a unique vehicle, possible with different identifiers. Fastening Screws would be considered inventory, as they are typically consumed for a work order etc. and can be quantified by the 10s, 100s, 1000s etc.) Assets also have the property of being a parent of another asset etc.

Locations

Items and assets are located at various locations, such as warehouses, plants, and other facilities. Like Assets, locations can be nested one inside another. Inventory items are also located at locations, using a concept known as Inventory Item Locations (described below)

Inventory Items (Supplies)

Inventory, as mentioned above, refers to parts and supplies which are often quantified in multiples, and typically are consumable parts/supplies/items. When describing Inventory items as CSV records, an included location will be provided to allow the system to determine the exact item location (stock) of the Inventory Item record.

Inventory Item Locations

The system tracks the location where inventory is stocked as a unique entity. This granularity extends down to the aisle, row, and bin that stock may reside. Inventory item locations are a composition of the inventory item, the facility location (see Locations), the row, aisle and bin that the item is stored. That is, an item stored in bin A and bin B, at the same location, row, aisle of the same item are still considered a separate inventory item location record. 

Charge Departments, Accounts, Billing Terms

These are simply records typically consisting of a name and code representing the accounts, departments and billing terms used in various ways by the CMMS and/or the purchasing workflow process.

Purchase Order / Receipt Status

These are status(es) representing the states that Purchases and Receipts can be in over the course of their lifecycle/workflow. A Purchase Order may begin in Draft, before moving to Submitted for Approval, to Cancelled or Fulfilled etc. The same occurs for Receipts. These statuses will likely vary between the integration partner system and the CMMS, and there should be a process involved to sync the statuses so that both systems use a common set.

Purchase Orders and Purchase Order Line Items

Purchase Orders are orders made to purchase various assets or to replenish inventory for the business. Each Purchase Order includes the items that they are looking to make a purchase of in the form of one or more Purchase Order Line items. Purchase orders will have statuses, billing terms and suppliers associated with them.

Receipts and Receipt Line Items

Receipts are generated when items or assets are received by the business, often as a result of a fulfilled Purchase Order. Each Receipt includes the items that were received in the form of one or more Receipt Line items. Receipts will have also have statuses, billing terms and suppliers associated with them.

Suppliers/Vendors

Suppliers are other Businesses that our business will interact with to complete purchases, receipts or any other means.



CSV File Formats 

Purchase Order

cmmsPOId - The internal record ID for the Purchase Order in the CMMS. This is needed to connect the Purchase Order created in the CMMS with the one that will be created in the Integration Partner’s system. This should be preserved by the Integration Partner’s system and returned back when a PurchaseOrder “OUT” file is written back to the file exchange location. (long)

internalId - The internal record ID for this Purchase Order in the Integration Partner’s system. (string)

total - The total decimal amount for the goods being ordered in this Purchase Order. (decimal)

internalBillingTermId - The internal record ID for the Billing Term in the Integration Partner’s system that this Purchase Order uses. (string)

internalVendorId - The internal record ID for the Vendor in the Integration Partner’s system that is fulfilling/providing the purchased goods for the Purchase. (string)

status - A String constant used to describe the Status of this Purchase Order. Must match the internal value supplied to the CMMS on the initial Purchase Order Status sync. (string)

Purchase Order Line Items

poliCmmsID - The CMMS record ID of the exact Purchase Order Line Item that this Purchase Order Line Item corresponds to.

parentPoInternalId - The internal record ID of the Purchase Order that this Purchase Order Line Item belongs to.

poliId - The internal record ID for the Purchase Order Line Item. (string)

poliItemId - The internal record ID representing the Item being ordered on this line of the Receipt (string)

qty - The amount of the particular Inventory Item ordered (on this Line). (decimal)

unitPrice - The decimal unit price of the particular Inventory Item ordered (on this Line). (decimal)

total - The total amount of the particular Inventory Item ordered (on this Line) (decimal)

description - A String description of the Purchase Order line item. (string)

 

Purchase Order CSV example

When a purchase order is sent from the CMMS, the file will have a similar format to below. (Not all columns are shown here, just a few in particular)

                       fig. 1 The Purchase order CSV being sent from the CMMS to the exchange location

In this example, a Purchase Order was generated from the CMMS (identified by id=24). It contains two line items :, with line 1 with id=45,line 2 with id=46, and line 3 with id=47. The inventory item being ordered in purchase order line 1 is ax1425, ax1426 on line 2 and ax1428 on line 3. The inventory items exist already in the partner system, and imported into CMMS at an earlier step of the integration. As a result,  when the CMMS purchase order was generated, the selected inventory items already have the corresponding partner system Id’s (i.e. ax1425 and ax1428) and can be included directly in the outgoing CSV.

The quantities and prices are not shown here, but simply live in the appropriately marked columns in the actual CSV.

The columns marked and highlighted in red are not included, and are responsibility of the partner system to include along in the file to send back to the CMMS in the next step of the workflow. 

Once the file has been picked up from the exchange location and processed, the partner system will write back a file that looks like this:

 

fig. 4 The Purchase Order Line Item CSV being sent from the CMMS to the exchange location

As highlighted in blue, the columns that have been updated or changed appear with their new values. When the CMMS pulls down the new CSV file, it will update the values accordingly

Accounts, Vendors, Billing Terms, Charge Departments

internalId

code

name

axcd18

ENG1

ENGINEERING1

internalId - The internal record ID of the integration partner’s system. This would be a primary resource ID or primary key for that particular record in the system. (string)

code - A typical business-level code, often in a human-readable format. For example, people within the organization have an understanding of ENG1 as a department code, as opposed to a less descriptive GUID or other key format. (string)

name - A string describing the name of this Account/Vendor/Charge Department.(string)

Receipts

internalId - The internal record ID for this Receipt in the Integration Partner’s system. (string)

internalCreatedFromId - The internal record ID for the Purchase Order in the Integration Partner’s system that this Receipt was created for. (string)

internalVendorId - The internal record ID for the Vendor in the Integration Partner’s system that is fulfilling/providing the purchased goods for the Purchase. (string)

cmmsReceiptId - The internal record ID for the receipt in the CMMS. (long)

receiptDate - The date in which the Receipt was received

Receipt Line Items

internalId - The internal record ID for the Receipt Line Item (string)

internalParentReceiptId - The internal record ID representing the Item being ordered on this line of the Receipt (string)

internalInventoryItemId - The internal record ID representing the Item being ordered on this line of the Receipt (OPTIONAL, leave blank if receiving Assets) (string)

internalInventoryItemLocationId - The internal record ID of the Location of the Inventory item being received to. (OPTIONAL, leave blank if receiving Assets) (string)

internalAssetId - The internal record ID representing the Asset being ordered on this line of the Receipt. (OPTIONAL, leave blank if receiving inventory) (string)

internalAssetLocationId - The internal record ID of the Location of the Asset is being received to. (OPTIONAL, leave blank if receiving inventory) (string)

internalPurchaseOrderLineItemId - The internal record ID of the exact Purchase Order Line Item that this Receipt Line Item corresponds to. (string)

quantityReceived - The amount of the particular Inventory Item received as part of this Receipt (decimal)

Fixed Assets

internalId - The internal record ID of the integration partner’s system. This would be a primary resource ID or primary key for that particular asset in the system. (string)

internalLocationId - The internal record ID of asset’s location inside integration partner’s system. This should be the internal ID of a location type (see the location section) (string)

internalParentAssetId - The internal record ID of asset’s parent asset inside integration partner’s system. (string)

displayName - The name of this asset. (string)

createdDate - The date which this asset was created. (date= dd/MM/yyyy)

lastModifiedDate - The date which this asset was last modified. (date= dd/MM/yyyy)

description - The description of this asset. (string)

Locations

internalId - The internal record ID of the integration partner’s system. This would be a primary resource ID or primary key for that particular location in the system. (string)

internalParentLocationId - The internal record ID of asset’s parent location inside integration partner’s system. (string)

name - The name of this asset. (string)

address - The address of this location (string)

province - The province of this location. (string)

postalCode - The postal code of this location. (string)

city - The city of this location. (string)

country - The country of this location. (string)

Purchase Order / Receipt Statuses

At present, the Purchase Order statuses can be provided as a set of string constants, as opposed to a CSV of records. This should be included along when completing the initial integration setup.

Inventory Items

internalId - The internal record ID of the integration partner’s system. This would be a primary resource ID or primary key for that particular inventory item in the system. (string)

internalLocationId - The integration system’s internal record ID of the location where this inventory item is located. (string)

internalChargeDeptId - The internal record ID of the charge department associated with this inventory item inside the Integration Partner’s system. (string)

internalAccountId - The internal record ID of the account code associated with this inventory item inside the Integration Partner’s system (string)

name - The for this inventory item. (string)

createdDate - The date this inventory item was created. (date= dd/MM/yyyy)

lastModifiedDate - The date this inventory item was last modified. (date= dd/MM/yyyy)

lastPrice - The last price for this inventory item.. (decimal)

Inventory Item Location

internalId - The internal record ID of the integration partner’s system. This would be a primary resource ID or primary key for that particular inventory item location in the system. (string)

internalInventoryItemId - The internal record ID of the charge department associated with this inventory item inside the Integration Partner’s system. (string)

internalLocationId - The integration system’s internal record ID of the location where this inventory item is located. (string)

aisle - The aisle that this inventory item is located. (string)

row - The row that this inventory item is located. (string)

bin - The bin that this inventory item is located. (string)

qtyOnHand - The quantity on hand at this location for this inventory item. (decimal)

minimumQty - The minimum quantity for this inventory item at this location. (decimal)

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk