Campaigns are used to automatically pop loans to the screen one after the other for expedited collection actions. A user can connect to their own personal campaign, or to any campaign that was created with the campaign builder. Once connected to the campaign, the user will have the loans of the campaign pop one by one until they leave collection mode, or the campaign is complete. Each time they complete a collection action on a loan, the next loan in the campaign will pop automatically.
Each user has a personal campaign. To add loans to your personal campaign, go into collector mode and run a query using any desired search criteria.
The Campaign Builder is used to create a campaign using SQL query criteria. It is accessed
To build a new campaign, click Add
Enter your queries in the columns of the Query section. To be included in the campaign, the loan must meet each query criteria selected.
Example showing how to combine the “Criteria:” and “Or:” rows to generate a particular SQL WHERE clause.
To enter a query, select a table on the NLS database from the Table drop down field, or type the name of a table. Then select a field from that table from the Field drop down field, or type the name of the field.
Next, you may choose if you want the resulting loans sorted by the results of this field in ascending or descending order.
Finally, enter the criteria for the field. If a criteria is entered directly in the field, it will be assumed that the field in question must equal the search criteria. Do not use quotes for strings. If the field in question is a string, NLS will add quotes to the query where needed. You may use greater than or less than symbols in the criteria fields. You may type additional query parameters in the Or field.
Note that entries in the Or row, and all rows below it, are combined into a single parenthesized OR clause, which is then added to the final SQL query with an AND clause, along with the above Criteria row and the clauses generated from other columns.
When the query is entered in the grid as shown in the figure above, the resulting where clause is:
WHERE (acctrefno < 50 OR acctrefno > 100) AND statement1_code != 25 AND (statement1_code > 20 OR statement1_code = 10 OR statement1_code = 11) AND total_payments > 5
Alternatively, you may choose to define your query as an SQL query directly against the database by clicking the Use SQL Statement button. This gives you all of the flexibility of the SQL language in creating your query.
Select acctrefno from loanacct where days_past_due > 30
When you are finished click Save
On the Options tab decide if you want to include or exclude loans that are already on other campaigns.
To execute the query, click . Any loans that meet the query will be added to the campaign.
Before the campaign will be visible to the collectors, its status must be changed to Open. Your collectors may then attach to the campaign from the Collections Screen.
NLS 5.25 and later
To configure which user or group can access a campaign, select the campaign and go to File > Trustees.
Move the user or group on the right to the Trustees list on the left to give access.
Deleting a Campaign
To delete a campaign setup or a campaign list within, highlight the item in the tree bar and click .
NoteThe Personal Campaign setup cannot be deleted.
Following is the color coding used in the Loans list.
Blue – Assigned
Gray – Completed
Green – Outside FDCPA allowed calling time.
Black – Within FDCPA allowed calling time.
Useful Query Examples
Select by Days Past Due; Exclude Certain Status Codes
Query: all loans between 5 and 45 days past due that do not have certain status codes in effect (in this example, the status codes in question are represented by status code numbers 10 and 14).
To determine what number represents a certain status code, go to the status code setup, and expand the invisible field to the left of the Status Code column to reveal the code numbers.
select acctrefno from loanacct where days_past_due > 4 and days_past_due < 46 and acctrefno not in (select acctrefno from loanacct_statuses where status_code_no = 10 or status_code_no = 14)
Select Loans where Promise to Pay has been Broken within a certain Date Range
Query: all loans with promises to pay which were broken between October 1st and October 14th of 2010.
select acctrefno from loanacct where acctrefno in (select acctrefno from loanacct_collections where row_id in (select loanacct_collections_row_id from loanacct_collections_ptp where promise_broken_date > '2010-10-01' and promise_broken_date < '2010-10-14'))
Select Loans where Promise to Pay has been Broken within the past week
Query: all loans with promises to pay which were broken between today and a week in the past.
select acctrefno from loanacct where acctrefno in (select acctrefno from loanacct_collections where row_id in (select loanacct_collections_row_id from loanacct_collections_ptp where promise_broken_date > dateadd(week, -1, getdate()) and promise_broken_date < getdate()))
dateadd(week, -1, getdate()) function is used to specify the date one week before today’s date.
The following parameters may be used with the dateadd() function to specify a date in the future using a positive number and a date in the past using a negative number.
|year, yyyy, yy||Year|
|quarter, qq, q||Quarter|
|month, mm, m||Month|
|day, dy, d, y||Day|
|week, ww, wk||Week|
|minute, mi, n||Minute|
|second, ss, s||Second|
Select by Days Past Due; Exclude Loans with unresolved Promises to Pay
Query: all loans 30 or more days past due that do not have unexpired promises to pay.
select acctrefno from loanacct where days_past_due > 29 and acctrefno not in (select acctrefno from loanacct_collections where row_id in (select loanacct_collections_row_id from loanacct_collections_ptp where promise_kept_date = NULL and promise_broken_date = NULL))