The SQL Integration feature enables other software systems to push data directly into a Treasury Software SQL Server (not SQL Express) based platform.  This eliminates the need for 'pass-thru' files.

 

 

This feature requires an advanced knowledge and skill set with SQL Server.  It is best used for high volume and highly automated environments.
 
We highly recommend printing out these instructions for reference.

 

 

Overview

1. You will populate data into the Treasury Software holding table.  The holding table exists in each SQL Server database.

2. Then, when Treasury Software is triggered, it will move the data from the holding table into a permanent table and create an ACH file.

 

 

Settings

1. Start the Software Integration Kit.
Select the Automation tab > Software Integration Kit.

Note: If you are using a version prior to 2013.  Select from the main menu Automation > Advanced - Software Integration Kit.
Select an unused process and click Add/edit.
SQL_Insertion04

 

 

 

2. Basic tab:
--Check the 'Visible' box (described later) and name this process (example Daily Processing)
--Select 'SQL Insertion'.****
--Click the 'SQL Insertion - Select Fields' link *****
Note: As the mapping templates are not used, you can leave the default.

 

SQL_Insertion05

 

 

 

3. SQL Insertion tab
After you click the 'SQL Insertion - Select Fields' link, you will be brought to the SQL Insertion tab

Select the top selection 'SQL - Insertion'

Leave the Transaction Code option to the default Populating CHK/SAV field only, unless you are a Financial Institution.

When complete, select the Automation tab.

 

SQL_Insertion06

 

 

 

 

 

4. Automation tab

Select a non-zero command line group number (typically 1).

Select an action from the drop down list (ACH File - Create and Transmit).

Copy the command line noted below.  You will use this line to trigger moving this process.

 

Click 'Save' when complete.

Note: Do not make any other changes in the other tabs.

Specifically, leave the default file format to 'tabular' and do not enable archiving.

 

SQL_Insertion07

 

 

 

 

 

Populating data into the Treasury Software holding table.

The following fields exist in each Treasury Software created SQL Server database.
Notes:
--All fields are text fields and located in the table tblHoldingGLIntegration.
--All are mandatory unless otherwise noted.
--You can add records as often as you want
--The table is automatically cleared (deleted) each time you trigger the Software Integration Kit to run.
 

 

Field name

Description

Mandatory fields

Amount

Can import as signed or absolute amounts, depending on whether or not you are supplying the Transaction Type codes.  Regardless of sign, always include a decimal point as needed.  Do not include commas or dollar signs.

Tip: Typically you will sign the amount as positive when you are collecting funds, and negative when you are sending out funds.

 
Either:

1. If Transaction codes are supplied in the ACH_CheckingorSavings field, enter all amounts in absolutes (positive).
2. If Transaction codes are NOT supplied, indicate whether the amount is a Debit (collecting funds, ie from clients) by signing it positive, or a Credit (paying funds, ie. employees and vendors) by signing it with a negative sign.

Date

The effective date, in MM/DD/YY format.  Note: The month and day can be one or two digit, the year can be two or four digit.

International format? - Use your local regional settings.

Description

Name of receiving party

Text1

Identification (ID#) of receiving party.  Optional field.

ACH_Account

Bank Account number of receiving party

ACH_Routing_ABA

Routing number of receiving party

Class_3_Letters

Standard Entry Class code (PPD, CCD, WEB, TEL, etc....). Optional field.

ACH_CheckingorSavings

Numeric entry only.  Either enter:

1. Simple checking (0) or savings (1) indicator

or

2. Transaction codes (22, 27, 32, 37, etc...)


 
 

 
 

Testing
After you have performed your one-time setup, and then populated your data, you can test your work.
Start the Software Integration Kit, select the process, and then click 'Run'.  Complete the import wizard.

 

Upon a successful import, the ACH file will be automatically created.  The file can typically be found (by default) at:

C:\Users\Public\Treasury Software\To Bank

 

Note: Your data that you populated will be cleared out of the tblHoldingGLIntegration.

 

No valid records? - Remember that the software will filter out invalid ABA numbers.  Make sure that you use valid ABA numbers in testing.

 

Need a test SQL script to populate the table?
INSERT into tblHoldingGLIntegration (tblHoldingGLIntegration.Amount, tblHoldingGLIntegration.Description, tblHoldingGLIntegration.Text1, tblHoldingGLIntegration.ACH_Account, tblHoldingGLIntegration.ACH_Routing_ABA, tblHoldingGLIntegration.ACH_CheckingorSavings, tblHoldingGLIntegration.Class_3_Letters, tblHoldingGLIntegration.Date) VALUES

('123.45', 'ABC Company', 'Vendor01', '88888888','063000045', '0', 'CCD', '12/31/2013')

 

 

SQL_Insertion08

 
 
 
 
Production

Trigger the Software Integration Kit using the command line noted in the Automation tab.

 
 

 

General notes
--This feature requires SQL Server (not SQL Express), and access to the Software Integration Kit.

 

--This integration is designed to be run in an automated fashion.  If you trigger the import manually via the menu structure as noted in testing - once you are at the review records screen, the records have been removed from the temporary holding table.