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 pre-configured 20 fields can be modified to show data other than as determined by its default settings.
The Collateral UDF 1 - 4 fields will display under the collateral section on the Collector tab for collateral codes that do not have their Show Vehicle Tab option selected. If the Show Vehicle Tab is selected, the VIN, Year, Make, and Model will be displayed.
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) |
| loanacct_coll_status |
| cif |
| cif_detail |
| cif_demographics |
| cif_financials |
| loanacct_collateral (only available for Collateral UDF) |
| collateral_location (only available for Collateral UDF) |
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 buttons determine the justification of the output data within the field.
