Support for mixed payment bank file

In some scenarios, users are provided with a single payment bank file for Account Receivables that contains details about payments made via different transaction type. The mixed payment bank file support feature allows you to configure Cash Application Manager in a way that it reads and identifies the following transaction types and payment methods present in the mixed payment bank file based on the batch number range or an identifier:

• Payment method: Cash, Check, and EFT
• Transaction type: ACH, Check, and Wire

Usually, a mixed payment bank file constitutes of payments aligned in separate batch ranges. This feature has been designed to detect the payment method and transaction type from the batch numbers and process the information in the NetSuite, under the transaction record. The following flows have been added in the connector to support this feature:

Once these flows are triggered, the connector reads the information present in the bank file and for every transaction, it updates the NetSuite’s Celigo Cash App Transaction record with the relevant information:

Setup needed to identify the transaction type and payment method from the mixed payment bank file

You can configure Cash Application Manager to use one the following parameters in the mixed payment bank file for identifying the transaction type and payment method which would then be used for further processing:

1. Batch Number

Generally, the transaction type and payment method information for a transaction is recognized by the connector based on the batch number information present in the bank file. The connector is dependent on the following Field Mappings defined in the Bank Transactions to NetSuite [Mixed] flow to identify the transaction type and payment method information

These mappings are available by default with your connector. These mappings consist of an expression that helps the connector in identifying the payment method or transaction type. You can edit this expression to define the range for recognizing payment method or transaction type based on the mixed payment bank file being shared with you from your bank.

For example:

The default range set to identify Payment Method in the expression specified in the connector’s field mappings are:

{{#compare $.[Batch number] '<=' '100'}}{{#compare $.[Batch number] '>=' '0'}}Check{{/compare}}{{else}}{{#compare $.[Batch number] '<=' '200'}}{{#compare $.[Batch number] '>=' '101'}}EFT{{/compare}}{{else}}{{#compare $.[Batch number] '<=' '300'}}{{#compare $.[Batch number] '>=' '201'}}Cash{{/compare}}{{/compare}}{{/compare}}{{/compare}}

Default Range:

• Batch number between O to 100 will be considered as Check
• Batch number between 101 to 200 will be considered as EFT
• Batch number between 201 to 300 will be considered as Cash

Based on your bank file, if the range is as follows:

New Range defined in the bank file:

• Batch number between O to 300 to be considered as Check
• Batch number between 301 to 400 to be considered as EFT
• Batch number between 401 to 600 to be considered as Cash

You can edit the same expression by just replacing the numeric values of range in the following way to define the new range:

{{#compare $.[Batch number] '<=' '300'}}{{#compare $.[Batch number] '>=' '0'}}Check{{/compare}}{{else}}{{#compare $.[Batch number] '<=' '400'}}{{#compare $.[Batch number] '>=' '301'}}EFT{{/compare}}{{else}}{{#compare $.[Batch number] '<=' '600'}}{{#compare $.[Batch number] '>=' '401'}}Cash{{/compare}}{{/compare}}{{/compare}}{{/compare}}

Note:

• In the two aforementioned expressions, the only difference is in the numeric values describing the range. Rest of the two expressions are exactly the same.
• By default, any batch number that does not fall under the range specified in the expression is considered as Check.

2. Keyword to distinguish the payment method and transaction type

The other way to configure identification criteria in the connector to identify the payment method and transaction type is to use keyword present in the bank file. To use this criterion in the connector, use the steps as follows:

1. Identify the keyword for all payment methods and transaction types in the bank file and the position of the keyword. This information about the keywords in the bank file is provided by the bank.
   For example, in the following image, the highlighted section shows the keyword identified in the mixed bank file and the position for keywords lies between columns 94 to 96 for type 4 record. For example, LBX represents Lockbox, ACH is for clearing house transactions.

2. Click the Settings icon against the Bank File to NetSuite [Mixed] flow.The Settings dialog box is displayed.
   
3. Click the File Parsing tab and select Type 6 – Detail Record.
   
4. Click + Add New Row. A new row is added.

5. Enter the details in the new row as per your bank file and click Save. In the above image, start position and end position for the new row has been mentioned as 94 and 96 (position of the keyword in bank file) based on the example used in step 1.
6. Click Field Mappings icon against the Bank Transactions to NetSuite [Mixed] flow. The mapping page is displayed.
7. Remove the expression in the Export Field (Webhook) column mentioned against Celigo Cash App Transaction: Payment Method (Name) and Celigo Cash App Transaction: Transaction Type in the Import Field (NetSuite) column

8. In place of the expressions in the Export Field (Webhook) column, enter the following text:
   Transactions[*].['Field Description’]
   Where Field Description should be replaced with the name of the new row added in step 4. 
   Based on demo row added in step 4, the value that needs to be entered in the Export Field (Webhook) column is Transactions[*].['Key’]
   
9. Click the Settings icon as displayed in the above screen for the Transaction Type field.  The Settings dialog box is displayed.
10. Select Lookup as Field Mapping Type and for Options select Static: Value to Value.
11. In the Export and Import field define the key you have selected in the bank file and add the matching Transaction Type against it.
    For example:

12. Repeat step 9, step 10, and step 11 for the Payment Method field.
    For example: