Collection UDF

The NLS Collection Mode contains 28 User-Defined Fields, but these fields are more powerful in their functionality than standard User-Defined Fields in NLS. These are loan display fields, and you do not simply specify the label and type of information to be displayed. You may, in fact grab specific values for the loan in question from the tables of the NLS database. In addition, you may display the results of advanced SQL queries.

The first 20 of these User-Defined Fields are pre-configured for you. The remaining eight are blank by default, but can be used to display any loan information you wish to query. If you need more than eight fields, any of the reconfigured 20 fields can be modified to show data other than as determined by its default settings.

If you need help determining the table and field for a particular piece of loan data, please contact Nortridge Software customer support.

Derive Values From Default

If the value you need is a single value that is stored in the NLS database, you can pull it by specifying the Table and Field for the value.

Because it must be a value with a one to one relationship to the loan in question (e.g., it could not be a value taken from transaction history because a field in the transaction history table would return many rows for any one loan) the value must exist in one of the following tables:

loanacct
loanacct_setup
loanacct_payment
loanacct_detail
loanacct_detail2
loanacct_statistics (Year to Date)
loanacct_statistics (Life to Date)
cif
cif_detail
cif_demographics
cif_financials


Any field from the available tables can be selected and its contents will be shown in the appropriate field of the Collector Screen. The data will be automatically formatted according to the data type of the database field.

NLS Custom

For the purpose of allowing access to various pieces of data that you may need to show, but which are not stored in a specific table in the database, there is a selection in the drop down list called NLS Custom. The table below describes the options available from NLS Custom and details what each of those options returns.

First Payment Date Returns the earlier of the First Principal Payment Date or the First Interest Payment Date. Loans with a payment period of N/A can return a blank.
Paid Thru Date If there is a valid Principal Paid Thru Date and a valid Interest Paid Thru Date, returns the earlier one. If only one is valid, returns that one. If neither has a valid date, returns NULL.
Due Date If there is a billing outstanding, returns the due date of the earliest Principal or Interest Billing. If there is no billing outstanding, returns the earlier value of the Next Principal Due Date or Next Interest Due Date.
BP / Last Returns the broken promise counter from statistics and the latest broken promise date. Returns blank date if there has never been a broken promise.
KP / Last Returns the kept promise counter from statistics and the latest kept promise date. Returns blank date if there has never been a kept promise.
NSF / Last Returns the NSF counter from statistics and the latest NSF date. Returns blank date if there has never been an NSF.
Term / Period Returns the term and specifies if that term is defined in the loan setup as Days, Months, or Payments.

Advanced (SQL Statement)

You may select data other than what is available from single database fields, either for the purpose of displaying some piece of global data (such as a GL account associated with the loan’s group), for displaying a calculation based on several data points, or for displaying multiple data points in a single field. This is done using SQL (structured query language).

If you need help in creating a SQL query to get a particular piece of data, please contact Nortridge Software’s customer support department.

The following are examples of the SQL queries used on some of the default Collector Mode fields, with explanations and examples of data shown in the fields.

Example #1 SQL Query

Field Name: Loan Term

SQL Query

select term,' ',term_char from loanacct_setup where acctrefno = NLS_ACTIVE_ACCTREFNO

Output Example

12 M

Explanation

The term of a loan in NLS is defined by two different database fields, the term type and the term. The term type can be months, days, or payments. The term is the number of months, days, or payments. The field “term” in table “loanacct_setup” contains that number. The term type is stored in the field “term_char” in the same table. The text: (,' ',) between them indicates that a space should be inserted between the term and term type. The where clause: “WHERE acctrefno = NLS_ACTIVE_ACCTREFNO” allows NLS to return the data for just the loan currently queried. “NLS_ACTIVE_ACCTREFNO” is the account reference number of the currently selected loan.

Example #2 SQL Query

Field Name: Curr/10/30

SQL Query

select '* / ', days_late_10,' / ', days_late_30 from loanacct_statistics where master_record=0 and acctrefno=NLS_ACTIVE_ACCTREFNO

Output Example

* / 1 / 2

Explanation

This is an example of a complex query used to show multiple data items on one line. It shows the number of times this loan was 10 or 30 days past due. These figures are stored in the life to date statistics. In the loanacct_statistics table, a statistics record is specified as being the life to date statistics record for the loan by the designation of its “Master Record.” When master_record = 0, the statistic entry is life to date.

Example #3 SQL Query

Field Name: NSF / Last

SQL Query

select nsf_payments, ' /', last_nsf_date from loanacct_statistics, loanacct_payment where loanacct_statistics.acctrefno=loanacct_payment.acctrefno and loanacct_statistics.master_record=0 and loanacct_payment.acctrefno=NLS_ACTIVE_ACCTREFNO

Output Example

1 /05/13/04 00:00:00.000

Explanation

The number of NSF payments and the date of the last NSF payment are two separate fields recorded in two separate tables. This query merges the two tables. The first WHERE clause: “loanacct_statistics.acctrefno=loanacct_payment.acctrefno” ensures that only those entries in the resulting merged table where the loan is identical are returned. Otherwise, the number of NSFs from one loan with the date from another loan will be returned. The second WHERE clause makes sure that the life to date statistics is being looked up. The third selects the loan that is currently up in the query to be the only loan for which values are returned.

Alignment

The alignment checkbox determines if the output data is left justified, right justified, or centered within the output field.