- Print
- PDF
SQL Letter Loops (for local print letter templates)
SQL Letter Loops
See also: LibreOffice - ACE Supported Version(s)
InterProse ACE has some specific options, available to be set up by default, for presenting loops of data in letter template tables; For example, certain fields associated with each debt with an active status in a bundle can be presented in a table (each debt represented in a row of the table).
Beyond the letter loops available by default, custom letter loops can be created and used in local print letter templates. The following steps outline the setup required to create and use these custom letter loops.
Note
The requirements are very specific for naming and formatting the fields to be included and the contextual identifier fields used. The syntax and table joins for SQL reports are also very specific.
Mistakes can be expected to result in errors and may have system impacts.
Caution
Development and testing of these tools should be conducted outside of working hours or custom development should be requested through support at time and materials.
Several steps are required to create and test custom SQL Designer reports for local print template letter loops. These steps should be attempted by only someone familiar with SQL and letter setup in ACE.
Setup Overview
Write a SQL report
Download a csv copy of the report
Register the copy as a LibreOffice Database
Create a letter template with a table for the letter loop & place fields in the table
Save and Upload the letter template to a letter profile
Test and verify the letter is producing anticipated results when scheduled and printed
1. Write a SQL Report
The SQL Report Identifier needs to clearly identify the letter loop the report will be associated with and must always match the letter loop database created based on it. ACE will refer back to the SQL Report when generating the associated local print letters.
The SELECT clause needs to include the fields to be used in the letter loop. The fields need to be named using, "AS" and a single lower case word or camelCase as shown here:
debt_status_code.debt_status_code AS "statusCode",
The FROM clause needs to include all of the tables required to accurately return the set of data needed for the letter loop based on the context of the letter. For example, if the name of the client is required, then the client table may need to be included in the SQL report. The tables in the FROM clause also need to be accurately joined either using INNER or OUTER joins in the FROM clause or by using additional statements in the WHERE clause.
The WHERE clause needs to include contextual logic if it applies (see examples #2 & #3 below). For example, if the letter loop will return a row for each debt in a bundle, a contextual bundler identifier will need to be included. Review examples in this article for additional details along with the list of supported parameters below.
Debt ID or debtID or DEBTID
Bundle ID or bundleID or BUNDLEID
Client ID or clientID or CLIENTID
Legal Case ID or legalCaseID or LEGALCASEID
Payment Plan ID or paymentPlanID or PAYMENTPLANID
Payment Trans ID or paymentTransID or PAYMENTTRANSID
Payment Schedule ID or paymentScheduleID or PAYMENTSCHEDULEID
Letter ID or letterID or LETTERID
Letter Config ID or letterConfigID or LETTERCONFIGID
Letter Destination ID or letterDestinationID or LETTERDESTINATIONID
Letter Series Config ID or letterSeriesConfigID or LETTERSERIESCONFIGID
A context identifier is not required if the data in the letter loop is not contextually limited to the account, the bundle or some other set of data associated with the account (see example #1 below). For example, a report could include a list of status codes available in ACE and the list could be presented in a letter loop; in that scenario, the status codes would not have any contextual association to the account the letter is generated from.
Examples:
1. SQL Report Identifier = LETTER_LOOP_STATUS
SELECT
debt_status_code.debt_status_code AS "statusCode",
debt_status_code.debt_status_code_group AS "codeGroup",
debt_status_code.label AS "label"
FROM
debt_status_code;
2. SQL Report Identifier = LETTER_LOOP_DEBT_TRANS
SELECT
debt.client_account_number AS "clientAccountNumber",
debt_trans.debt_id AS "debtID",
TO_CHAR(debt_trans.trans_date, 'MM/DD/YYYY') AS "transDate",
debt_trans.type_name AS "type",
'$' || TO_CHAR(sum(debt_trans.amount*.01),'FM999,999,990.00') AS "amount"
FROM
debt_trans,
debt,
client
WHERE
debt_trans.debt_id = ${Sequence:Debt ID}
AND debt_trans.debt_id = debt.debt_id
AND debt.client_id = client.client_id
AND debt_trans.operator = 'RECEIVED'
GROUP BY
debt_trans.debt_id,
debt.client_account_number,
debt_trans.payment_trans_id,
TO_CHAR(debt_trans.trans_date, 'MM/DD/YYYY'),
debt_trans.type_name,
debt_trans.trans_date
ORDER BY
debt_trans.debt_id,
debt.client_account_number,
debt_trans.trans_date;
3. SQL Report Identifier = LETTER_LOOP_BUNDLE_DEBT_TRANS
SELECT
client.customer_client_code AS "customerClientCode",
debt.client_account_number AS "clientAccountNumber",
debt_trans.debt_id AS "debtID",
TO_CHAR(debt_trans.trans_date, 'MM/DD/YYYY') AS "transDate",
debt_trans.type_name AS "type",
'$' || TO_CHAR(sum(debt_trans.amount*.01),'FM999,999,990.00') AS "amount"
FROM
debt_trans,
debt,
client
WHERE
debt.bundle_id = ${Sequence:Bundle ID}
AND debt_trans.debt_id = debt.debt_id
AND debt.client_id = client.client_id
AND debt_trans.operator = 'RECEIVED'
GROUP BY
debt.bundle_id,
client.customer_client_code,
debt_trans.debt_id,
debt.client_account_number,
debt_trans.payment_trans_id,
debt_trans.type_name,
TO_CHAR(debt_trans.trans_date, 'MM/DD/YYYY'),
debt_trans.type_name,
debt_trans.trans_date
ORDER BY
debt.bundle_id,
client.customer_client_code,
debt_trans.debt_id,
debt.client_account_number,
debt_trans.type_name,
debt_trans.trans_date;
2. Download a CSV copy of the report
(SAVE/MOVE this csv to a folder for Libre Office Letter Files)
Here is an example of the csv report (opened in notepad++) representative of the #2 example above when run in the context of a single debt ID (110211199). Please save the csv file with the same name as the SQL Report identifier. For example, the SQL Report ID = LETTER_LOOP_DEBT_TRANS and the csv file name = LETTER_LOOP_DEBT_TRANS.csv
Changes
If changes are made to the report, even after the LibreOffice Database is registered, be sure to replace/overwrite this csv file. It must continue to have the same name and be stored in the same location in order for the registered database to continue to see the data fields and use them for the LibreOffice letter templates using the databases.
ClientAccountNumber,DebtID,TransDate,Type,Amount
885,110211199,10/15/2019,PAYMENT,$20.00
885,110211199,10/15/2019,PAYMENT,$300.00
885,110211199,10/15/2019,PAYMENT,$250.00
885,110211199,10/15/2019,NSF,$-250.00
885,110211199,10/15/2019,CREDIT,$-300.00
885,110211199,04/29/2020,PAYMENT,"$15,882.91"
,,,,0
3. Register the copy as a LibreOffice Database
Open LibreOffice (not a document or spreadsheet)
Left click Base Database
Select "Connect to an existing database"
Select "Spreadsheet" from the drop down field
Select "Next"
"Browse" and "Open" the csv file
Select "Next"
Select "Finish"
Save the file to the same location as the csv and with the same file name (but do not use the csv extension)
the SQL Report ID = LETTER_LOOP_DEBT_TRANS
the csv file name = LETTER_LOOP_DEBT_TRANS.csv
the LibreOffice database file name = LETTER_LOOP_DEBT_TRANS.odb
Close LibreOffice
4. Create a letter template with a table for the letter loop & place fields in the table
Create a letter template in LibreOffice.
Insert a table. Name the table “sql_loop_[REPORT_IDENTIFIER]” (Example: sql_loop_LETTER_LOOP_DEBT_TRANS) with two rows and the number of columns needed for your letter loop table. If the table name needs to be updated it can also be done by going to Table -> Properties.
Check the "Heading" option before creating the table. In the top row of the table type the column names to be shown in the letter.
From View -> Data Sources, place fields from the letter loop database into the second row of the table.
5. Save and Upload the letter template to a letter profile
Save the letter template (save all letter template on your computer where they can be found and with names that will be easy to understand; for example, store them in the same location as the letter databases and spreadsheets)
Create a letter profile to load the letter template to
Upload the template created
Important Note
The letter template will return letter loop data bases on the SQL Letter Loop context identifier NOT the letter profile type.
Example, if the SQL Letter Loop reports data in context with the bundle id- it will show up in the local print letter the same way regardless of whether or not the letter profile is identified as a debt type or bundle type.
6. Test and verify the letter is producing anticipated results when scheduled and printed
Open an account and test the letter
Make sure the account(s) the letter is being tested from include data associated with the letter loop context; For example, if the letter loop is returning debts associated with a bundle, make sure the account the letter is tested from has more than one debt in the bundle; if the letter loop is returning received transactions of different types associated with an account, make sure there are received transactions on the account for each type expected to be returned
SQL Designer Report Letter Loop data is not available to letter vendor files
Please reach out to InterProse support if you have additional questions related to using this feature or if you would like to request InterProse develop SQL Letter Loops for you at time and materials.