Cash Management agent

 New!    You use the Cash Management agent to import financial data from external systems—such as ERPs—into the Prophix One Cash Management application.

This agent supports cash flow forecasting and balance tracking by allowing you to import structured financial data through two types of import table:

  • Balance – Snapshots of account balances
  • Document – Individual cash flow documents such as invoices, forecasts, or payment records

 Note:  Each import table type has specific formatting and data requirements (see below).

Add a connection

A Cash Management agent's connection always serves as a destination for data that you want to import.

To add a connection for a Cash Management agent:

  1. In the navigation panel, select Admin Setup.
  2. Select a Cash Management agent or peer connection, and click > Add Connection.

  3. Connection Name must be unique. Up to 40 characters.

  4. Authentication URL: The endpoint used to authenticate.
  5. API URL: The API endpoint.
  6. Username: The user name for accessing the API.
  7. User Password: The password for accessing the API.
  8. Click Save.

Add a Query

  1. Select a Script connection or peer query, and click > Add Query.
  2. Complete Query name.

  3. In the Query tab's text area, type or paste your Javascript. (No character restrictions or length limit.)

     Note:  This query specifies the table you want to import, and must meet the requirements set out in Balance or Document, as appropriate.

  4. Optionally, to add filter parameters to the query, select Filters; to add a parameter click in the blank row and complete the following:

    • Name: The parameter name. Up to 50 characters.

       Tip:  In the filter table, don't prepend "@" to the parameter names.

    • Type: The parameter's data type (text or numeric). (For date values choose text.)
    • Default value: What the user sees in the Data Integration process and can edit. Up to 50 characters.
    • Sample value: What the user sees when hovering over the cell in the Data Integration process. Use it to show the expected input/format. Up to 50 characters.
    • Hide: Turn on if you want to keep users of the Data Integration process from seeing the parameter, while using its Default value in the query.
  5. Click Save.

Add a Task

Compared to most other agents and their tasks, Cash Management agent tasks have some unique requirements.

To add a task for a Cash Management agent:

  1. In the navigation panel, select Job Definitions.
  2. Select the appropriate job for the task.
  3. Click > Add Task.
  4. Task name: Type a name that is unique within the step. Up to 100 characters.
  5. From Connection and Query select the source for the data you want to import (this refers to the source query you created in the preceding procedure).
  6. For Filters select the resources that you defined in the setup phase (if applicable).
  7. Under Destination, from Connection select the appropriate Cash Management connection.
  8. Under Data Type, select the type of data you want to import:
    • Balance: For importing snapshots of account balances. (See the requirements.)
    • Document: For importing individual cash flow documents such as invoices, forecasts, or payment records. (See the requirements.)
  9. For Start Date, do one of the following:
    • Leave blank to use the default start date specified in the Cash Management app's Integration parameters.
    • Specify the date the imported data should start from.
  10. Click Save.

Data type guidelines

The following table specifies the accepted formats and rules for the Cash Management import tables.

Data type Accepted formats/rules
Text Maximum length as specified. May be blank or omitted (treated as null). No escaping needed.
Date Accepts true date cells or text strings in formats: yyyy-MM-dd, yyyyMMdd, MM/dd/yyyy, MM/dd/yy. Normalized to yyyy-MM-dd. Can be blank or null.
Number Accepts numeric values or numeric strings (for example: "1234.56"). Parsed using . as decimal separator. Blank or null values are treated as null.
Yes/No flag Accepts Y/N, S/N, true/false, or equivalent. Any truthy value is interpreted as S, falsy as N. Blank or null values are treated as null.

Balance Import table

Each row in the Balance Import table represents a snapshot of account balances for a specific unit and date.

Format

  • Column headers must be written in uppercase with underscores.
    • For example: UNIT, COMPANY_ERP_BALANCE
  • All fields listed below are mandatory and must be included in each row.
  • Fields must follow the data type and formatting rules in the data type guidelines.

Column definitions

Column name Data type Requirement type Description
UNIT Text (up to 50) Mandatory Code of the business unit.
ACCOUNT Text (up to 50) Mandatory Code of the account in the ERP.
DATE Date Mandatory Balance date.
COMPANY_ERP_BALANCE Number Mandatory Total balance from the ERP.
BANK_BALANCE Number Mandatory Total balance from the bank.
COMPANY_ERP_INPUT_BALANCE Number Mandatory Incoming balance from the ERP.
COMPANY_ERP_OUTPUT_BALANCE Number Mandatory Outgoing balance from the ERP.
BANK_INPUT_BALANCE Number Mandatory Incoming balance from the bank.
BANK_OUTPUT_BALANCE Number Mandatory Outgoing balance from the bank.

Document Import table

Each row in the Document Import table represents a single cash flow document.

Format

  • Column headers must be written in uppercase with underscores.
    • For example: DOCUMENT, ISSUE_DATE
  • Fields marked as mandatory must be included and correctly formatted.
  • Fields marked as non-mandatory can be left blank.
  • Fields marked as soft requirements are recommended by the Cash Management API.
    • If not met, the API will not throw an error, but you may see record warnings in Cash Management.
  • Fields must follow the data type and formatting rules in the data type guidelines.

Column definitions

Column name Data type Requirement type Description
DOCUMENT Text (up to 255) Mandatory Identification of the document.
NATURE Text (1) Mandatory Nature of the document.
VALUE Number Mandatory Document value.
UNIT Text (up to 50) Mandatory Unit code registered in Cash Management.
INCLUSION_DATE Date Optional Entry date of document in Cash Management.
ISSUE_DATE Date Mandatory Date the document was issued.
EXPIRY_DATE Date Mandatory Due date registered for the document.
COST_CENTER Text (up to 50) Optional External cost center code registered.
DIMENSION1 to DIMENSION6 Text (up to 50) Mandatory if dimension is active Dimension code registered in Cash Management.
PERSON Text (up to 50) Optional Code registered in Cash Management.
FINANCIAL_CLASSIFICATION Text (up to 50) Optional (leave blank if not used) External code registered for the category.
PROJECT Text (up to 50) Optional Project code in Cash Management.
TREASURY_ACCOUNT Text (up to 50) Mandatory External code registered in Account.
DOCUMENT_TYPE Text (up to 50) Optional Type code registered for the document in Cash Management.
SETTLEMENT_DATE Date Optional Settlement date registered for the document.
HISTORY Text (up to 10,000) Optional History description of the document.
EXPECTED_WITHOUT_DOCUMENT Yes/No flag Mandatory Indicates whether the document is a forecast.
SUSPENDED Yes/No flag Mandatory Suspension of the document. In the ERP, it will depend on an acceptance.
PENDING_APPROVAL Yes/No flag Mandatory Indicates if there are unprocessed changes in the ERP.
ERP_DOCUMENT_EDITABLE Yes/No flag Mandatory Indicates whether the document can be edited.
ERP_UUID Text (up to 100) Optional Document key in the ERP. Used for validation.
ERP_ORIGIN Text (up to 100) Optional Name of the originating ERP system.
ORIGIN Text (up to 100) Optional Source of the document.
INTEGRATED Yes/No flag Optional Indicates whether the document is integrated.