23.08. COMS integration

The COMS is a control tower system, introduced to Nigeria by Chemonics. It controls the movements of stock around many facilities across a few 3PLs (third part logistics providers).

The tower gathers report files (in csv format) from all the 3PLs every night. It uses the information provided to decide who gets what stock and when. Stock movements are sent by shipment file (again in csv format) as required.

So, there are 2 parts to the interface as far as mSupply is concerned:

• Send inventory and buffer files to COMS automatically every night.
• Process shipment files into supplier invoices and stock transfer customer invoices as appropriate, and send back resulting pick lists.

Detailed design information (for Sustainable Solutions only) is here: https://github.com/sussol/org-issues/issues/21

The setup of the interface is all done through the COMS interface tab of the Preferences (File > Preferences…):

Use the SFTP interface: If checked, the interface is turned on and the various schedulers which run it are activated. If unchecked, the interface is turned off and the various schedulers are de-activated. No report files will be created and no shipment files will be processed. The various fields below are only editable if this is checked.

SFTP server settings These are the settings for the remote server to be accessed by SFTP

• URL: the URL of the server e.g. https://somewhere.here.com
• User name: the username to use to login to this server
• Password: the password to use to login to this server
• Test connection button: when a URL, User name and Password have been entered, clicking this will use those values to test the connection to the server. Text will appear next to the button to tell you whether the connection is successful or not. If the connection is not successful one or more of the settings above must be changed before mSupply can connect to the COMS server and operate the interface.

Email settings These tell mSupply where to send errors and information emails. Multiple email addresses can be entered in each field and should be separated with a comma (,).

• Email address for errors: the email address to send error information to e.g. someone@somwhere.com
• Email address for reports: the email address to send information about the inventory file reports to e.g. someone@somwhere.com

Temporary file download & report generation folder This is the folder on the local server where the locally generated files will be stored before being uploaded to the server described in the SFTP server settings section above. Click on the Choose button to select the folder on the server. Note that mSupply will automatically create OneNetwork/downloads, OneNetwork/inventory/errors, OneNetwork/inventory/transactionFiles and OneNetwork/inventory/inventoryFiles folders inside the folder you choose if they do not already exist.

Schedule settings New shipment files are checked for and the inventory and buffer reports files are created and uploaded on schedules, controlled by these settings. The inventory and buffer files are created at the same time but the buffer files have to be uploaded a set period after the inventory files. This is to allow time for the day's inventory files to be processed by the One tower before the buffer files arrive for processing.

• Shipment file check interval: the time in minutes between each check on the remote server for shipment files to process
• Time to create buffer files: the time of day to create the buffer report files
• Inventory files check interval: the number of minutes between checks to see if inventory and lot files are old enough to upload
• Time (from creation) to wait before uploading inventory files: the number of minutes old an inventory file must be before uploading it to the remote server.

Failure retry settings These settings give mSupply details about what to do if there is a failure uploading files to the remote server via SFTP

• Number of times to retry sending: the maximum number of times to retry sending a file if it fails
• Interval between retries: the number of minutes to wait after a failed upload to try again

COMS site Code/Description and Store code table This table contains the mapping between the Site code used by COMS and the Store code used by mSupply. If a store has a secondary store (e.g. a main store and a secondary malaria store) that should be treated as a single store when sending inventory files and receiving shipments, the secondary store can also be selected. For each mapping you can also choose which programs belong to the main and which belong to the secondary store so that shipments for different programs can be directed to their correct store. NOTE: A program in the context of the COMS interface is a customer type transaction category which has a code (customer transaction categories without a code are not considered as COMS programs).

To edit a mapping double click on it in the table, to add or remove mappings click on the + and - buttons respectively.

These files are retrieved from the remote SFTP server according to the schedule. A single file can contain multiple shipments but the rows for a shipment will always be grouped together. Depending on the identity of the source and destination store, a single shipment will either be turned into a supplier invoice (to receive stock) or a stock transfer customer invoice (to send stock to another store).

Shipments are created as customer invoices with placeholder lines. They are created in the correct store by matching the supplier code and program of a shipment in the shipment file to the entries made in the COMS site Code/Description and Store code table table in the settings (see the Setup section above).

The shipment ID is stored in the Their ref field of each customer invoice created.

Before being processed, the shipment number of each shipment file is checked against the shipment number stored in the Their ref field of all other customer invoices in all the stores defined in the One site Code/Description and Store code table (described in the preferences above). If the shipment number isn't found in any of these customer invoices then processing can continue as normal. If the shipment number is found the shipment is not processed:

• If any shipment in the file is not a duplicate then the shipment file is moved to the successes folder if the shipment processes successfully i.e. it is not rejected because of the duplicates. An email with this body is sent: “Shipments in file XXX are duplicates and have NOT been processed. The duplicate shipment numbers are: YYY. One or more shipments in the file are not duplicates and have been processed normally (but might still fail - a separate email will be sent about the failure if that is the case).” where XXX is the file name and YYY is a comma separated list of the duplicate shipment numbers. A suitable log entry is also made.
• If all the shipments in a shipment file are duplicates then the file is rejected and an email with this body is sent: “All shipments in file XXX are duplicates so the file has been rejected.”. where XXX is the file name. A log entry is also made. The rejected file is moved to the error folder.

In a similar way, the status of the first line in every shipment (not shipment file 😉) is checked. If it is confirmed then the shipment is processed. If the status is not confirmed then the shipment is not processed:

• If one or more of the shipments in a file (but not all) are not confirmed then an error email with this body is sent: “Shipments in file XXX are not confirmed and have not been processed. The non-confirmed shipments are YYY. One or more shipments in the file are confirmed and have been processed normally (but might still fail - a separate email will be sent about the failure if that is the case).” where XXX is the file name and YYY is a comma separated list of the non-confirmed shipment numbers. A suitable log entry is also made. If any shipment in the file is confirmed then the shipment file is moved to the successes folder if the shipment processes successfully i.e. it is not rejected because of the non-confirmed status of one or more of the shipments.
• If all the shipments in a shipment file are non-confirmed then the file is rejected and an email with this body is sent: “None of the shipments in file XXX are confirmed so the file has been rejected.” where XXX is the file name. A log entry is also made. The rejected file is moved to the error folder.

After these checks, shipment files are validated before processing for valid item codes, unrecognised transaction category codes etc. If a shipment file fails validation then the whole file is rejected, an email is sent and the file is moved to the results > axioserror folder.

On finalisation of a customer invoice), a pick list file needs to be created on the server. To do this, on customer invoice finalisation a sync message (type 'axiosRequestFilesCreate') is sent to the server. That message is processed by the sync queue and turned into a pick list file which is stored locally to await upload to the remote SFTP server according to the schedule.

• Program column: Populated with the transaction category code of the supplier invoice (or inventory adjustment) the stock in the pick list line arrived on (empty if the supplier invoice or inventory adjustment does not have a transaction category assigned).

There are 4 types of files that are sent automatically once a day at the time set in the preferences. They are:

1. Lot file (filename Axios.Lotddmmyyyy.csv.ready) Contains a line for each unique batch in stock in the site (main store plus secondary store).

2. Inventory file (filename )

3. Buffer file (filename )

4. BOH file (filename )

The pick list files generated by mSupply have the filename format:

Axios.PickList//ddmmyyyyhhmmss//ONE-S-//shipment Id//-//random number//.csv.ready

where:

• ddmmyyyyhhmmss is the date and time the file was created
• shipment Id is the ID of the shipment sent from the One Network that this pick list belongs to
• random number is a 5 digit random number

An example is:

Axios.PickList26072019155745ONE-S-15049-30604.csv.ready