Cash Management agent

You use the Cash Management agent to work with cash flow data in two directions:

  • Extract — Pull cash flow details and balances from Cash Management into any table destination, such as Prophix Staging, SQL, or Excel.

  • Import — Bring financial data from external systems, such as ERPs, into the Cash Management application.

 Note:  A Cash Management source always sends output to a table destination. You cannot route cash flow output back into another Cash Management connection.

Prerequisites

  • You must have access to Admin Setup in DI Studio.

  • A Prophix Cash Management tenant with its Authentication URL, API URL, and a service account (username and password).

  • If you plan to group results by custom dimensions (Dimension 1 through 6), those dimensions must already be configured in your Cash Management tenant before use. See Group By options by report type.

Add an agent

Adding an agent is done the same way for all agent types. For instructions, see Add an agent.

 Note:  Agent Type is fixed at creation. Once saved, it cannot be changed.

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 for your tenant.
  6. Username: The service account used for DI authentication.
  7. User Password: The service account password. Stored encrypted; never displayed in plain text after saving.
  8. Click Save.

Add a source query (extract cash flow data)

Use a source query to read cash flow report data from Cash Management and load it into a destination of your choice. The extract connection reuses your existing Cash Management connection, so no new credentials or configuration are required.

  1. In the navigation panel, right-click your Cash Management connection and select Create Query. The cash flow query editor opens.

  2. Complete Query name.

  3. In the Type field, select the dataset to extract. This determines the which Group By options are available.

    • Cash Flow Data — the value of each financial flow for the selected period.

    • Cash Flow Balances — the ending balance for the selected period.

       Note:  Switching Type to Cash Flow Balances removes any Group By selections that Balances does not support.

  4. Set the View to control the period granularity: Daily, Weekly, or Monthly.

     Note:  Changing View clears the Start Period and End Period fields. Reselect them after switching between Daily, Weekly, and Monthly.

  5. Set the Start Period and End Period for the date range you want to pull. The End Period must be on or after the Start Period.

  6. Under Group By, select one or more dimensions to aggregate the extracted data. Available options depend on the selected Type.

  7. For Use Revised Due Date, turn on to use the revised due date instead of the original. Applies to both report types. Off by default.

     Note:  The Use Revised Due Date setting has no effect on settled (realized) documents. Settled documents always bucket by their settlement date, regardless of whether this setting is on or off.

  8. Map the query to your destination, for example, a Prophix Staging table, a SQL table, or an Excel file.

  9. Click Save.

Extracted rows are written to your destination, aggregated according to the View and Group By settings you selected. Cash Management data is read-only through this source; no data can be written back to Cash Management through an extract query.

Group By options by report type

The dimensions available in Group By depend on the selected Type:

Type Available Group By dimensions
Cash Flow Data Entity, Cost Center, Project, Trading Partner, Financial Flow, Treasury, Document Type, Dimension 1 through 6
Cash Flow Balances Entity, Treasury, Dimension 1 through 6

Dimension 1 through 6: These options are always listed in the drop-down, but they only work if your Cash Management tenant has those dimensions configured. Selecting a dimension that has not been set up in your tenant causes the job to fail at run time. Confirm your tenant's dimension configuration before grouping by any Dimension 1 through 6 option.

Add an import query (import data)

Use an import query to define the data you want to bring into Cash Management from an external source

  1. Select a Cash management 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 Import table or Document Import table, 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 and run the job

  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 Balance Import table requirements.)
    • Document: For importing individual cash flow documents such as invoices, forecasts, or payment records. (See the Document Import table requirements.)
    • Trading Partner: For importing trading partner records. When selected, choose Recreate to replace the staging table on each run, or Append to add rows to the existing table.
  9. For Start Date, do one of the following:
    • Leave blank to use the default start date specified in the Cash Management Integration parameters.
    • Specify the date the imported data should start from.
  10. Click Save.
  11. Run the job from the toolbar, or schedule it using Process Manager in your Prophix application.

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 entity 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 or entity.
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 Entity 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.
TRADING_PARTNER 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.