File Automation sFTP

Unimarket File Automation allows customers to automate the upload and export of community data using sFTP. Uploads are managed using the File Processing menu.

  • Unimarket File Automation (sFTP)

    If you are looking for ways to automate your Unimarket master data uploads or generate regular report exports we now have a simple flat file option if you don't have the budget or bandwidth for full integration. 

    Unimarket File Automation allows customers to securely import and export files to and from Unimarket using simple CSV flat files. This removes the big jump to full integration, but it also removes the need to deal with data loads and reports manually. File Automation requires very little setup other than Secure File Transfer Protocol (sFTP) and the use of our CSV import templates. Automated reporting is even simpler whereby reports can be automated on a regular export cycle to an sFTP location of your choice. 

    The following Unimarket master data imports are supported by File Automation:

    Unimarket can enable this service for specific uploads as needed (much like an integration point), so you can easily pick and choose what level of automation makes the most sense for your organization. Further details relating to setting up File Automation can be found here.

    If you are interested in enabling File Automation, please speak to your Account Manager so we can understand your requirements, enable the relevant setup and configure secure authentication. Note: there is a minimal annual cost to have this functionality.

     

  • sFTP Setup

    sFTP Setup for File Automation

    Unimarket can send and receive files securely using Secure File Transfer Protocol (sFTP). This can be used to automate master data uploads like Users, Account Codes, Approvals and more. It can also be used to automate reports/ exports from your community to an sFTP folder.

    The following steps are required to setup the sFTP functionality before you can start sending and receiving files:

    1. Unimarket will enable the sFTP functionality which provides a secure location to send the CSV files.
    2. Unimarket will provide the customer with the command line for connecting to the sFTP server.
    3. Authentication needs to be established. This can be via user/pw of the integration user in Unimarket; OR via public key provided by the customer (preferred option) - Unimarket supports both Open SSH private keys and RSA private keys
    4. Unimarket updates the sFTP setup with the public key
    5. The customer needs to provide Unimarket with their IP address so this can be whitelisted (note you will get a timeout if this is not done). 
    6. The customer can now connect to the sFTP server using the sFTP client.

     

    sFTP Configuration

    When a customer opts for Inbound sFTP, Unimarket will provide the above mentioned credentials via a formal document to ensure there is an appropriate record.
    Our sFTP servers have fixed hostnames and applicable IP addresses per region:

     

    The sFTP server for DEMO is:

    • sftp.unimarket-demo.com  IP is 34.172.138.111

    The sFTP servers for PROD are:

    • NZ: sftp.unimarket.co.nz   IP 35.189.31.11
    • AU: sftp.unimarket.com.au   IP: 34.151.122.70
    • US: sftp.unimarket.com   IP: 35.224.213.147

     

  • Automatic approval data uploads using sFTP

    You can automate approval data uploads by sending the Approval CSV file templates to Unimarket via sFTP (Secure file transfer). There are 3 templates that can be automated which are Groups, Chains and Rulesets. The upload feature must be enabled by Unimarket so please contact your account manager if you would like to discuss this option and have it enabled.

     

    sFTP Setup

    Before you can start sending / receiving files, sFTP setup is required. Details can be found here. 

     

    Loading Files

    Files sent to Unimarket must be sent in order so they process correctly. This means that each file must be sent and fully uploaded (not fully processed) before sending the next file. They should not be sent all at once. Note that once uploaded the files will be processed.

    Because the approval uploads have related data the following order is required:

    1. Groups

    2. Chains

    3. Ruleset

    Files should be sent to the correct inbound folder location on the sFTP server. Details are below.

    mceclip0.png

    Once processed the files will be moved the processed folder and the relevant sub-directory beneath. 

     

    File naming convention

    Groups and Chains

    Groups and Chains do not have a naming convention and should just be sent to the corresponding folder on the sFTP server.

     

    Rulesets

    There can be many rulesets where a separate CSV upload file is needed for each. Files should be sent to the corresponding sFTP ruleset folder, however the filename needs to contain the ruleset name as outlined below. Note there are 3 underscores required between the Date and the Ruleset Name. This will ensure the rules are loaded to the correct ruleset.

    • Name Format: date___rulesetname.csv
      • Example: 20220825___1 Project Accounting

     

  • User data uploads using sFTP

    You can automate user data uploads by sending the Approval CSV file templates to Unimarket via sFTP (Secure file transfer).  The upload feature must be enabled by Unimarket so please contact your account manager if you would like to discuss this option and have it enabled.

     

    sFTP Setup

    1. Unimarket will enable the sFTP functionality which provides a secure location to send the CSV files.
    2. Unimarket will provide the customer with the command line for connecting to the sFTP server.
    3. Authentication needs to be established. This can be via user/pw of the integration user in Unimarket; OR via public key provided by the customer (preferred option). 
    4. Unimarket updates the sFTP setup with the public key.
    5. The customer needs to provide Unimarket with their IP address so this can be whitelisted (note you will get a timeout if this is not done). 
    6. The customer can now connect to the sFTP server using the sFTP client.

     

    Loading Files

    Files sent to Unimarket must be sent in order so they process correctly. This means that each file must be sent and fully uploaded (not fully processed) before sending the next file. They should not be sent all at once. Note that once uploaded the files will be processed.

     

    Files should be sent to the correct inbound folder location on the sFTP server. Details are below.

    Invoice-payments.png

    Once processed the files will be moved to the processed folder and the relevant sub-directory beneath. 

     

    File naming convention

    User files do not have a naming convention and should just be sent to the corresponding folder on the sFTP server.

     

  • Creating and updating Supplier records

    Suppliers and their related details can be easily created and updated using the bulk community Supplier Upload function. This allows suppliers to be uploaded using a CSV template for the purposes of: 

    • Synchronizing your suppliers with the suppliers in your Finance ERP system so that changes are reflected back up into Unimarket e.g. a daily feed of new suppliers, removed suppliers, and changes to supplier details.
    • Bulk updates to supplier records such as Tags, Vendor ID or Buyer Groups
    • Connecting or Disconnecting suppliers in bulk

    File Format

    The template is provided below. Note that most columns are optional, and the order of the columns does not matter, eg: you could just submit a file with an ID in column A & the Supplier Tags as column B.

    The table below summarises all of the available fields for update via this mechanism.

    Column Name Description Format Optional / Mandatory
    Community Supplier ID The Unimarket assigned ID for the supplier in this specific community. Alphanumeric Mandatory - At least one ID must be provided
    Supplier ID

    The Customer’s ID for this supplier (from your ERP). 

    Logic: There must either be a Community Supplier ID, OR a Supplier ID. If neither ID is found, create a new Lite Supplier.

    Note where there is more than 1 Supplier ID in the same community, the Community Supplier ID must be provided.

    		 Alphanumeric Mandatory - At least one ID must be provided
    Profile Type Accepts one of:

    - Individual
    - Company/Organization (US)
    - Company/Organisation (AU/NZ)

    New lite suppliers will default to Company/Organization if this column is not provided.    		 Alphanumeric Optional column

    Mandatory to be populated if the column exists in the upload file
    Company or Trading Name The display name of the Supplier.

    Note: This is currently the only required field for creating a new lite supplier.    		 Alphanumeric Mandatory if a new supplier
    Optional if an existing supplier
    Legal Business Name The official name of the Supplier.

    Only applies if the Supplier has a Profile Type of Company/Organization.

    When creating a Lite Supplier, it will be defaulted as the Company or Trading Name if the column is not included.    		 Alphanumeric Mandatory if column included in file.
    Business Number NZ Suppliers only for the New Zealand Business Number https://www.nzbn.govt.nz/whats-an-nzbn/about/ Alphanumeric Optional
    First Name The Supplier’s first name.

    Only applies if the Supplier has a Profile Type of Individual.    		 Alphanumeric Mandatory if column included in file.
    Last Name The Supplier’s last name.

    Only applies if the Supplier has a Profile Type of Individual.    		 Alphanumeric Mandatory if column included in file.
    Default Email Address The default email address for contacting the supplier. This will be used for most email notifications. Valid Email Address Optional
    Remittance Email Address If the Supplier wants users to contact them on a different email address for any remittance-related queries.

    If this is blank & Default Email Address is supplied, set it to the Default Email Address (same behaviour as UI).    		 Valid Email Address Optional
    Order Email Address Used if the supplier has a specific email address for order information (eg: the Purchase Order email).

    If this is blank & Default Email Address is supplied, set it to the Default Email Address (same behaviour as UI).    		 Valid Email Address Optional
    Default Category Must be a valid 8-digit UNSPSC Category. This will be used as the default for Non-Catalog items for this supplier. 8-digit number Optional
    Supplier Tags Must match existing Supplier Tags in the community. Multiple tags can be provided if comma separated. Alphanumeric Optional
    Buyer Groups Must match existing Buyer Groups in the community. Multiple buyer groups can be provided if comma separated Alphanumeric Optional
    Payment Term Must match an existing Payment Term Code. This can only be one individual Code, not many.

    If blank, the supplier will be set to the “Use default Payment Term” option    		 Alphanumeric Optional
    Contact Name Mandatory if any other contact fields are provided. Alphanumeric Optional
    Contact Phone Currently mandatory to create the contact. Number Optional
    Contact Email The contact person’s email address Valid Email Address Optional
    Default Contact Used to set this as the default contact person for this supplier. Either Y or Blank. Fixed value Optional
    Connection State

    Either set the Supplier to Connected or Disconnected. There are a couple of specific rules for this field:

    • If the column is provided & blank, we will keep the existing Connection State. Most other fields would be set to Blank.
    • If this is blank for a newly created Lite Supplier, we will set them to Connected.

    Note: This will not generate any supplier-side disconnected email notifications.

    		 Fixed value Optional
    Tax Registered AU or NZ only
    Accepts either Y or N    		 Fixed Value Optional
    Tax Identification Type US Only
    Only accepts one of the following values:
    -EIN
    -Foreign TIN
    -GIIN
    -ITIN
    -SSN    		 Fixed Value Optional
    Tax Number New Zealand & Australia
    In NZ this must be either an 8 or 9 digit number

    In AU this must be 11 digit number
    Is mandatory for AU & NZ if Tax Registered = Yes.

    US
    The format depends on the corresponding Tax Identification Type

    EIN = must be in 99-9999999 format
    Foreign TIN = appears to accept any character (alpha, numeric, special character)
    GIIN = Must be in XXXXXX.XXXXX.AA.000 format
    ITIN = must be in 999-99-9999 format
    SSN = must be in 199-99-9999 format    		 Number Mandatory for AU/NZ if Tax Registered = Y
  • Supplier Address data uploads using sFTP

     

    You can automate Supplier Address data uploads by sending the Supplier Address CSV file templates to Unimarket via sFTP (Secure file transfer). The upload feature must be enabled by Unimarket so please contact your account manager if you would like to discuss this option and have it enabled.

     

    sFTP Setup

    1. Unimarket will enable the sFTP functionality which provides a secure location to send the CSV files.
    2. Unimarket will provide the customer with the command line for connecting to the sFTP server.
    3. Authentication needs to be established. This can be via user/pw of the integration user in. Unimarket; OR via public key provided by the customer (preferred option). 
    4. Unimarket updates the sFTP setup with the public key.
    5. The customer needs to provide Unimarket with their IP address so this can be whitelisted (note you will get a timeout if this is not done). 
    6. The customer can now connect to the sFTP server using the sFTP client.

     

    Loading Files

    Files sent to Unimarket must be sent in order so they process correctly. This means that each file must be sent and fully uploaded (not fully processed) before sending the next file. They should not be sent all at once. Note that once uploaded, the files will be processed.

     

    Files should be sent to the correct inbound folder location on the sFTP server. Details are below.

    supplier addresses.png

    Once processed the files will be moved to the processed folder and the relevant sub-directory beneath. 

     

    File naming convention

    Supplier Addresses do not have a naming convention and should just be sent to the corresponding folder on the sFTP server.

     

    Expected Behavior

      • Remittance Address

        • If Address Name matches an existing address then update that record

        • If Address Name does not match an existing, then create a new Remittance Address

      • There can be only 1 Physical and Mailing address for a specific Community Supplier ID.

        • ie: if two are included in the same file, an error will be returned

      • For Registered & Premium Suppliers, the Physical and Mailing Address will not be updated

      • Only 1 Default Remittance address for a specific Community Supplier ID will be allowed.

        • ie: 2 Remittance addresses can’t both be the default.

      • Address Code is optional 

      • There is currently a maximum of 100 remittance addresses per supplier 

     

  • Payment data uploads using sFTP

    You can automate payment data uploads by sending the payment CSV file template to Unimarket via sFTP (Secure file transfer). There are 3 templates that can be automated which are Groups, Chains and Rulesets. The upload feature must be enabled by Unimarket so please contact your account manager if you would like to discuss this option and have it enabled.

     

    sFTP Setup

    1. Unimarket will enable the sFTP functionality which provides a secure location to send the CSV files.
    2. Unimarket will provide the customer with the command line for connecting to the sFTP server.
    3. Authentication needs to be established. This can be via user/pw of the integration user in Unimarket; OR via public key provided by the customer (preferred option). 
    4. Unimarket updates the sFTP setup with the public key.
    5. The customer needs to provide Unimarket with their IP address so this can be whitelisted (note you will get a timeout if this is not done). 
    6. The customer can now connect to the sFTP server using the sFTP client.

     

    Loading Files

    Files sent to Unimarket must be sent in order so they process correctly. This means that each file must be sent and fully uploaded (not fully processed) before sending the next file. They should not be sent all at once. Note that once uploaded, the files will be processed.

     

    Files should be sent to the correct inbound folder location on the sFTP server. Details are below.

    Invoice-payments.png

    Once processed the files will be moved to the processed folder and the relevant sub-directory beneath. 

     

    File naming convention

    Payment files do not have a naming convention and should just be sent to the corresponding folder on the sFTP server.