 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. You can import balances, documents, trading partners, and bank statement transactions.

 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.
A Prophix Cash Management tenant with its Authentication URL, API URL, and a service account (username and password).
A service user that matches the direction of your job. Use a web service user to extract data and an integrator user to import data. See Choose the right service user.
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.
For bank statement imports: the bank account must already exist in Cash Management and be marked Validated before its imported transactions appear. See Bank Statement import.

Add an agent

You add an agent the same way for all agent types. For instructions, see Add an agent.

 Note: You set the Agent Type when you create the agent. After you save the agent, you cannot change its type.

Add a connection

A Cash Management connection can be used to extract data (as a source) or to import data (as a destination).

To add a connection for a Cash Management agent:

In the navigation panel, select  Admin Setup.
Select a Cash Management agent or peer connection, and click  >  Add Connection.
Connection Name must be unique. Up to 40 characters.
Authentication URL: The endpoint used to authenticate.
API URL: The API endpoint for your tenant. It must end with /api, for example https://<host>/api. If it does not, the job fails with a message that points to this requirement.
Username: The service account used for DI authentication. Use the service user type that matches how you will use the connection.
Password: The service account password. Stored encrypted; never displayed in plain text after saving.
Click Save. All required fields must be filled in before you can save.

Choose the right service user

Cash Management issues separate service users for reading and writing data. A connection only works in the direction its user is authorized for.

Direction	DI use	Service User type
Pull (extract from Cash Management)	Source queries	Web service user
Push (import into Cash Management)	Destination imports, including Bank Statement	Integrator user

Create one connection per user type. For example, create one connection to extract data and another to import data.

 Tip: If a job fails with an authorization error, ask your Cash Management administrator to confirm that you are using the correct service user type.

 Note: Treat service account credentials as sensitive. Do not paste production secrets into shared documents or tickets.

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. The connection must use a web service user.

In the navigation panel, right-click your Cash Management connection and select Create Query. The cash flow query editor opens.
Complete Query name.
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.
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.
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.
Under Group By, select one or more dimensions to aggregate the extracted data. Available options depend on the selected Type.
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.
Map the query to your destination, for example, a Prophix Staging table, a SQL table, or an Excel file.
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

Select a Cash management connection or peer query, and click  >  Add Query.
Complete Query name.
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, Trading Partner Import table or Bank Statement Import table, as appropriate.
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, do not 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.
Click Save.

How column names are matched

These rules apply to every import table in this article.

Column names are not case-sensitive. Use capital letters with underscores. This article uses capital letters with underscores, and you should use the same format.
Otherwise, you must spell each column name exactly as shown. A space does not match an underscore, so DUE DATE does not match DUE_DATE. The import does not ignore extra spaces. The only names with spaces that the import supports are the alternative names in the Bank Statement table.
Each field has a current name. Use the current name for new jobs. If the table lists an older name, existing jobs that use it keep working.
If your source has both the current name and the older name, the job uses the current name and shows a warning in the job log. The job log also lists the fields that matched an older name, so you can switch to the current name.

Extra columns

The import sends any column that the tables below do not list to Cash Management under the column’s own name, so you can use these columns to fill custom fields. Extra columns keep their exact spelling. The import sends true/false columns as Y/N, dates as yyyy-MM-dd, numbers as numbers, and text without changes. It does not send empty values.

 Caution: The import does not flag a misspelled column name. It sends the column as an extra field, and the field you intended stays empty. Check your spelling carefully.

Add a task and run the job

In the navigation panel, select  Job Definitions.
Select the appropriate job for the task.
Click  >  Add Task.
Task name: Type a name that is unique within the step. Up to 100 characters.
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).
For Filters select the resources that you defined in the setup phase (if applicable).
Under Destination, from Connection select the appropriate Cash Management connection. The connection must use an integrator user.
Under Import 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. (See the Trading Partner Import table requirements). When selected, choose Recreate to replace the staging table on each run, or Append to add rows to the existing table.
- Bank Statement: For importing bank statement transactions for a period. (See Bank Statement import and the Bank Statement Import table requirements.)
Start Date:
- For Balance and Document, 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.
- For Bank Statement, set the date and import mode:
  - Start Date and End Date (both optional): The period to import. Click the clear (x) button to remove a saved date. A blank date is saved as empty, and Cash Management determines the boundary.
  - Recreate/ Append: Choose how new transactions are combined with existing ones. Append is the default. See Append vs. Recreate.
    
     Caution: For Document and Balance imports, Cash Management replaces existing data. It deletes all existing data from the start date onward and replaces it with the data your job sends. Include all the data you want to keep from the start date onward in every run. If you leave Start Date blank, the default start date from the Cash Management Integration parameters is used.
Click Save
Run the job from the toolbar, or schedule it using Process Manager in your Prophix application. Check the result in the Job Log. Use the copy button in the log to share the details of a failed run. See Job Log.

Run multiple imports together

To run several imports together, add each import to its own Step. Place only one Cash Management import task in each Step. Each import runs on its own, and imports in the same step can interfere with each other.

 Note: A Trading Partner import does not save any data on its own. Trading partners do not take effect until a Document or Balance import runs. Add the Document or Balance import in a separate step after the Trading Partner step. The job log also shows this warning.

Bank Statement import

Use the Bank Statement import type to bring bank transactions into Cash Management. Each run is a single import session: DI starts the session, sends the transactions, and then finishes it.

Append vs Recreate

Append and Recreate also apply to Trading Partner imports. They do not apply to Document and Balance imports, which replace existing data from the start date onward.

The mode you choose is applied once, when the import session finishes.

Mode	What it does	When to use it
Append (default)	Adds the new transactions next to the existing ones.	Loading new transactions. Re-running the same data creates duplicates.
Recreate	For each bank account in the run, first deletes the transactions this integration previously imported for the period, then inserts the new ones.	Re-running or correcting a period.

 Note: Recreate does not change transactions from file import or Plaid imports . It also does not change bank accounts that are not in the run.

 Caution: Recreate also removes the published transactions for the rows it replaces. After a Recreate run, re-publish the transactions that were classified and published

Date rules

Neither the Start Date nor the End Date can be in the future.
When both are set, End Date must be after Start Date. When you change one date, the other date is validated again
A row with a missing or invalid DATE, or a DATE outside the Start Date–End Date period, is skipped, and a warning appears. The other rows still import.

Before you run a Bank Statement import

Cash Management allows only one open bank statement import per tenant. Starting a new import, from any job, user, or tool, cancels the one still in progress and discards its transactions, so the earlier run fails. Do not schedule or run bank statement jobs for the same tenant at the same time. Avoid putting multiple imports in the same Step.

Validate the bank account first. Imported transactions appear in Bank Demonstration only after the bank account is marked Validated on the Validated final balance tab. Until then, the import succeeds but nothing shows.
The account key must already exist. EXTERNAL_TREASURY_ACCOUNT_KEY must match an existing account linked to a single last-level bank account. Rows with an unknown key are skipped. Valid rows still import.
At least one row must be accepted. If no transaction is accepted, nothing is imported and the job fails.
Large files are sent in pages. DI sends at most 2,000 rows per API call (page) within the same import. If a run fails, re-run the whole job. Do not retry part of it, because resending a page duplicates its rows.
The import must be enabled. The Bank Statement import must be enabled for your Cash Management environment. If it is not, the job reports that the import is not available. Ask Prophix Support to confirm that your tenant is enabled.

Data type guidelines

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

Data type	Accepted format/rules	Examples
Text	Leave a cell empty to leave the field blank. Text longer than the limit stops the job for Documents and Balances, or skips the row for Bank Statement.	INV-1001, CC100, Project A
Date	Accepts real date columns or text in yyyy-MM-dd, yyyyMMdd, MM/dd/yyyy, or MM/dd/yy (month first). Sent as yyyy-MM-dd. Empty values are allowed where the field is optional.	2024-11-05, 20241105, 11/05/2024
Number	Use . as the decimal separator. Empty = no value.	12345.67, 987.50
Yes/No	Y or N is sent to Cash Management. Accepted values are Y, S, N, true, false, or a true/false column. Any other value, such as yes, no, 0, or 1, stops the job.	Y, N, true, false
Allowed-value fields	NATURE accepts I or O. The older E and S values still work. Trading Partner TYPE accepts C, S, or B.	I, O, C, S, B

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 should be written in uppercase with underscores. See How column names are matched.
- For example: ENTITY_KEY, COMPANYS_ENDING_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.

 Note: DI does not check whether the required Balance columns are present. If a required column is missing, DI does not send and the problem is reported in the Cash Management process logs.

Column definitions

Current name	Older name (still works)	Data type	Required by Cash Management	Description
ENTITY_KEY	UNIT	Text	Yes	Business unit (entity) code.
EXTERNAL_TREASURY_ACCOUNT_KEY	ACCOUNT	Text	Yes	Account code.
DATE	-	Date	Yes	Balance date.
COMPANYS_ENDING_BALANCE	COMPANY_ERP_BALANCE	Number	Yes	Total balance from the ERP.
BANK_ENDING_BALANCE	BANK_BALANCE	Number	Yes	Total balance from the bank.
COMPANYS_CASH_INFLOWS	COMPANY_ERP_INPUT_BALANCE	Number	Yes	Incoming, from the ERP.
COMPANYS_CASH_OUTFLOWS	COMPANY_ERP_OUTPUT_BALANCE	Number	Yes	Outgoing, from the ERP.
BANK_CASH_INFLOWS	BANK_INPUT_BALANCE	Number	Yes	Incoming, from the bank.
BANK_CASH_OUTFLOWS	BANK_OUTPUT_BALANCE	Number	Yes	Outgoing, from the bank.

Document Import table

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

Format

Column headers should be written in uppercase with underscores. See How columns are matched.
- For example: DOCUMENT, ISSUE_DATE
Fields marked as Yes in the Required by Cash Management column must be included and correctly formatted.
Fields marked No can be left blank.
Fields must follow the data type and formatting rules in the Data type guidelines.

 Note: DI does not check whether the required Document columns are present. If a required column is missing, DI does not send and the problem is reported in the Cash Management process logs.

Column definitions

Current name	Older name (still works)	Data type	Required by Cash Management	Description
DOCUMENT	-	Text, up to 255	Yes	Identification of the document.
NATURE	-	Text (1)	Yes	I = inflow, O = outflow. Older E (inflow) and S (outflow) also work.
VALUE	-	Number	Yes	Document value.
ENTITY_KEY	UNIT	Text, up to 50	Yes	Business unit (entity) code registered in Cash Management.
INCLUSION_DATE	-	Date	No	Entry date.
ISSUE_DATE	-	Date	Yes	Date issued.
DUE_DATE	EXPIRY_DATE	Date	Yes	Due date.
REVISED_DUE_DATE	-	Date	No	Revised due date, if the due date was renegotiated. Leave empty if none.
EXTERNAL_COST_CENTER_KEY	COST_CENTER	Text, up to 50	No	External cost center code.
DIMENSION_1_KEY to DIMENSION_6_KEY	DIMENSION1 to DIMENSION6	Text, up to 50	Yes, if the dimension is active	Six separate columns, one per dimension.
TRADING_PARTNER_KEY	PERSON	Text, up to 50	No	Client /supplier code. The trading partner must exist in Cash Management. See Trading Partner Import table.
EXTERNAL_FINANCIAL_CLASSIFICATION_KEY	FINANCIAL_CLASSIFICATION	Text, up to 50	Yes	Category code.
PROJECT_KEY	PROJECT	Text, up to 50	No	Project code.
EXTERNAL_TREASURY_ACCOUNT_KEY	TREASURY_ACCOUNT	Text, up to 50	Yes	Treasury account code.
DOCUMENT_TYPE_KEY	DOCUMENT_TYPE	Text, up to 50	No	Document type code.
SETTLEMENT_DATE	-	Date	No	Settlement date. Empty = not yet settled.
HISTORY	-	Text, up to 10,000	No	Document history /description.
PROJECTION_WITHOUT_DOCUMENT	EXPECTED_WITHOUT_DOCUMENT	Yes/No	Yes	Forecast (no document) indicator.
SUSPENDED	-	Yes/No	Yes	Document suspended.
PENDING_APPROVAL	-	Yes/No	Yes	Unprocessed changes in the ERP.
EDITABLE_DOCUMENT	ERP_DOCUMENT_EDITABLE	Yes/No	Yes	Document can be edited.
ERP_UUID	-	Text, up to 100	No	Document key in the ERP.
ERP_ORIGIN	-	Text, up to 100	No	Originating ERP name.

Trading Partner Import table

Each row in the Trading Partner Import table represents one client or supplier. When you run the import, it creates new trading partners or updates existing ones. To use this table, set Import Type to Trading Partner.

 Note: Trading partners do not take effect until you run a Document or Balance import. Run that import as a separate Step after the Trading Partner import finishes. For details, see Run multiple imports together.

Column definitions

Column	Also accepted as	Data type	Required	Description
CODE	ID	Text, up to 50. Letters, digits, -, _ and . only.	Yes	Trading partner code. Spaces around it are removed.
NAME	-	Text, up to 200	Yes	Client /supplier name.
TYPE	-	C, S or B (not case-sensitive)	Yes	C = Client, S = Supplier, B = Both.
CATEGORY_KEY	CATEGORY	Text	No	Code of an existing category in Cash Management.
ALIASES	-	Text, separated by ;, up to 10	No	Alternative names, used to match bank transactions to the partner.

 Note: The import skips any row that has an empty or invalid code, an empty name, an invalid type, or a key that repeats in the file, and it shows a warning for each skipped row. The import still processes all other rows. If a required column is missing, the job stops and shows a message that names the missing column. The job log includes a summary of counts.

Bank Statement Import table

Each row in the Bank Statement Import table represents a single bank transaction. To use it, set Import Type to Bank Statement.

Format

Column names are not case-sensitive. You can also use the alternative names with spaces shown below.
Mandatory columns must be included and correctly formatted.
Optional columns can be left blank.
Fields must follow the data type and formatting rules in the Data type guidelines.

Column definitions

Current name	Also accepted as	Data type	Required	Description
DATE	Date	Date	Yes	Transaction date at the bank.
VALUE	Value	Number, up to 8 decimals	Yes	Negative = outflow, positive = inflow.
EXTERNAL_TREASURY_ACCOUNT_KEY	External Treasury Account Key	Text, up to 50	Yes	Account code. Must match an existing account linked to a single last-level bank account.
ISSUE_DATE	Issue Date	Date	No	Date the transaction was issued. Defaults to DATE when empty.
CHECK_NUMBER	Check Number	Text, up to 50	No	Check number, if the transaction is a check.
DESCRIPTION	Description	Text, up to 255	No	Transaction description.
RAW_DESCRIPTION	Raw Description	Text, up to 255	No	Original bank description.
MERCHANT_NAME	Merchant Name	Text, up to 255	No	Merchant or counterparty name.
CURRENCY_KEY	Currency Key	Text, 3-letter code	No	ISO 4217 currency code (for example, BRL) registered in Cash Management. When empty, the account currency is used, then the entity currency, then the default currency.
PAYMENT_METHOD	Payment Method	Text, up to 100	No	Payment method, for example PIX.
CATEGORY	Category	Text, up to 255	No	Transaction category.
DETAILED_CATEGORY	Detailed Category	Text, up to 100	No	Transaction subcategory.

 Note: If a required column is missing, the job stops and shows a message that names the missing column. The import skips any row that has a missing or invalid date, a date outside the statement period, a missing account, an invalid amount, or text that is too long, and it shows a warning for each skipped row.

Track import status

To check the status of an import. open the job execution log in DI. For Document and Balance imports, the log includes lines like the following:

1	...
2	[Cash Mgmnt Dest] API Request ID = 33
3	...
4	[Cash Mgmnt Dest] Process ID = 196
5	...

Use the API Request ID to track the status of the API request in Cash Management.
Use the Process ID to track the Cash Management background projection process.

 Note: The API Request ID and Process ID lines appear for Document and Balance imports only. Trading Partner imports log a summary of counts. Bank Statement imports log how many transactions the import created and replaced.

Troubleshooting

Symptom	What to do
The job is refused with an authorization error.	The connection is using the wrong service user type. Use a web service user for extracts and an integrator user for imports. If you are unsure which to use, ask your Cash Management administrator.
The job fails with a message about the API URL.	The API URL must end with /api, for example https://<host>/api. Update the connection with the correct URL.
A field you filled in is empty in Cash Management.	Check the spelling of the column name. Cash Management treats a misspelled column as an extra field, so the intended field stays empty.
The job log shows a warning about column names.	Your source has both the current and an older column name. The job uses the current name. Remove the older column, or rename it to the current name.
The job stops with a message about a value.	For Document and Balance imports, the job stops if text is too long or if a Yes/No value is anything other than Y, S, N, true, or false. Correct the value and run the job again.
A required Document or Balance column is missing, but the job did not stop.	DI does not check these columns. Cash Management reports the problem in its own process logs.
Trading partners do not appear.	A Trading Partner import takes effect only when a later Document or Balance import runs. Add the Document or Balance import in a separate Step after the Trading Partner Step.
Imports interfere with each other.	Use one Cash Management import task in each Step.
A Bank Statement import succeeds, but no transactions appear.	Mark the bank account as Validated on the Validated final balance tab. Transactions appear only after you validate the account.
Some Bank Statement rows were skipped.	Check the warnings in the job log. Common causes include an unknown EXTERNAL_TREASURY_ACCOUNT_KEY, a missing or invalid date, a date outside the statement period, an invalid amount, or text that is too long. The import still processes all other rows.
A Bank Statement job fails because the import accepted no transactions.	The import added nothing. Use the skipped-row warnings in the job log to fix the source data, then run the job again.
A Bank Statement job failed and its transactions were discarded.	Another import for the same tenant started and cancelled the job. Run Bank Statement jobs one at a time for each tenant, then run the failed job again.
Transactions are duplicated.	You ran the same data again in Append mode, or you resent part of a large file. Use Recreate to correct the period. Always run the whole job again instead of retrying part of it.
Transactions you published disappear after a run.	Recreate removes published transactions for the replaced rows. Publish the classified and previously published transactions again.
The import reports that it is not available.	Your tenant may not have the Bank Statement import enabled. Ask Prophix Support to confirm.