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.

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

1. Write a SQL report

2. Download a csv copy of the report

3. Register the copy as a LibreOffice Database

4. Create a letter template with a table for the letter loop & place fields in the table

5. Save and Upload the letter template to a letter profile

6. Test and verify the letter is producing anticipated results when scheduled and printed

1. Write a SQL Report 

1. 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.

2. 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",

3. 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.

4. 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 

5. 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

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

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