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.

Personal Campaigns

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. Click Personal Campaign in the Collector Mode tab to add the selected set of loans to your personal campaign. You may then attach to your personal campaign to begin popping loans.

Campaign Builder

The Campaign Builder is used to create a campaign using SQL query criteria. It is accessed by clicking Campaign Builder or in the shortcut bar.

To build a new campaign, click Add . Enter a name and description for your new campaign setup.

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.

When you are finished click Save to save your query.

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.

Options Tab

Ensure that loans queried are not part of another Campaign List (OPEN/PENDING) - Select to exclude loans that are already in another campaign list.

Deleting a Campaign

To delete a campaign setup or a campaign list within, highlight the item in the tree bar and click .

Loans List

Following is the color coding used in the Loans list.

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 1^st and October 14^th 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()))

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

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))