Contents - Index

Query Page

Introduction
This page is used to control the special queries that you have created. The queries that you create are only available to you.

Queries are used to retrieve specific data from the ALMSys database. The ALMSys database consists of many data files, each of which has many data fields. You need to have a basic understanding of what data is in which file in order to create a query that makes sense.

"Relational Databases 101"
There are far too many fields in ALMSys for them to be stored in one file, so the data fields are grouped logically by their purpose (or function). For example, all of the insurance related fields are kept in a file called, "Insurance," all of the calculation information is kept in a file called, "Lease," and names and addresses for the lessee are kept in a file called, "Customers." (etc, etc, etc.)

Each of these files have a common field that links them together. For most of ALMSys' files, this is the Account Number field. The Customers, Insurance, and Lease files each have an account number field. This field "links" the three tables together, and for every account "1234" in the Customers table, there will be a corresponding record in the Insurance table with a "1234" account number and a corresponding record in the Lease table with a "1234" account number. These tables are said to be "relational," and the account number defines the relation. 

So that you do not need to be a rocket scientist or a database administrator, ALMSys simplifies the query process by "knowing" how all of the tables relate to each other. You also do not need to know SQL (standard query language) as ALMSys will create the SQL coding for you. All you have to do is select the fields that you want and ALMSys will do the rest.

Layout
The button is used to add a new query. Queries are named for you and cannot be changed. ALMSys starts naming at "Query000001," which means that you can have up to 999,999 queries. 

The grid below the button is a list of the queries that belong to you.

The button is used to "clone" or copy an existing query to a new query.

The button is used to delete an existing query.

The large area on the right that looks like

is comprised of the ALMSys tables available for the query. It is used to mark the fields that you wish to see in your query. (The buttons to the right of this will be explained later.) This area will be referred to as the query fields.

Above the query fields are two buttons that you may use limit the query fields to only those fields which you have selected. When in "selected-only" mode, you must click the All button in order to add new fields to the query. When the query is run, the view will be returned to all fields.

The area below the list of queries contains special features that may be used with your query.

ALMSys Tables Available
AMPTax Additional monthly payments & taxes table. This contains the master code definitions. This table should not be used in conjunction with other tables. Special internal codes are preceded with a $ sign. Sorted by AMPTax code.

Audit Audit trail detail. Sorted by date and time of entry.

CBDetail Credit bureau detail. Sorted by account number, deal number.

ContactHistory Contact history detail. Sorted by account number, date, and sequential number within date.

Customers Customer names and addresses. Sorted by account number.

Deal Deal related data, such as driver information, depreciation, non-applicant spouse, etc. Sorted by account number and deal number.

Insurance Insurance detail. Sorted by account number and deal number.

Lease Deal calculation detail. Sorted by account number and deal number.

LeaseChargesDetail  Charges assigned to the deal at inception. Sorted by account number, deal number, code, and type ("C" for Capitalized cost, "N" for Non-capitalized cost).

LeaseChargesMaster  Deal charges code definitions. This only relates to the LeaseChargesDetail file. The Lease Charges Detail Master Code in the LeaseChagesDetail file is the relation to this file. Sorted by code.

LeaseCo Company detail. There is only one record.

Lever Leveraged financing detail. Sorted by account number and deal number.

LeverBank Leveraged financing bank master data. This only relates to the Lever file. The Leveraged Financing Bank No. field in the Lever file is the relation to this file. Sorted by bank number.

Location Location names, addresses, and detail. This only relates to the Deal file. The Deal Location No. field in the Deal file is the relation to this file. Sorted by location number.

Payments Payment detail. Sorted by account number, deal number, and reverse posting order (last one posted is first).

RefContact Referral sources contact detail. The Deal Referral Code and Deal Referral Subcode in the Deal file are the relations to this table. This table also relates to the RefSource table. Sorted by referral source and referral source subcode.

RefSource Referral sources master. The Deal Referral Code in the Deal file is the relation to this table. The RefContact table also relates to this table. Sorted by referral source code.

Rep Company representative file. This table only relates to the Deal file. The Deal Representative Code in the Deal file is the relation to this table. Sorted by representative I.D. code.

SellingDealer Selling dealer detail. This table only relates to the Deal file. The Deal Selling Dealer Name in the Deal file is the relation to this table. Sorted by selling dealer name.

Vehicle Vehicle detail. This table only relates to the Deal file. The Deal VIN in the Deal file is the relation to this table. Sorted by VIN.

Although there are many files available, you will find that most of your queries will involve the Deal, Customer, and Lease files.

A Simple Query
Let's do a very simple query. You want to see all of the active deals in your portfolio. All you wish to see are the people's names and account numbers.
  • Start a new query by pressing .
  • Give the query a title by typing Active Deals in the Title field under the list of queries grid.
  • In the query fields, click the + in front of Customers. This will expand the Customers file to show all of its data fields.
  • Click the box for Customer Lessee Name (F, MI, L).
  • Click the - in front of Customers. This will hide the Customers file data fields.
  • Click the + in front of Deal.
  • Click the boxes for Deal Account Deal No., Deal Account No., Deal Active Code, and Deal Active Code Status Date.
  • Highlight Deal Active Code by clicking on its name (not the box).
  • Press the button to the right of the query fields box. This will bring up the ALMSys Query Field Selectors window. This is used to provide selection criteria for the field. In our example, we want all active deals only.
  • Type an A in the first selector field.
  • Press to save and return to the query screen. Notice that the field title turns red when selection criteria is present for a field.
  • Press to save the query.
  • Press to run the query.

    (The button is used to uncheck all field boxes.)

    (Queries are sensitive to the locations that have been marked. You may need to press Set Locations to ensure that the locations are marked correctly for your query.)

    Query View Screen
    The query will process and send you to the ALMSys Query View screen when it has completed.

    You will notice right away that you were given more data fields than you checked. You will always get an RCtr field. This is simply a record counter. 

    The next 7 fields are the result of asking for Lessee Name. ALMSys needs these fields in order to construct the appropriate name for the borrower. The actual borrower name is under the heading of CustLesseeName later in the grid. 

    The next 4 fields are ones that we asked for. The LeaseLessee field is again used in the construction of the borrower name. 

    After the CustLesseeName is the AccountNoDealNo field. Whenever both the account number and deal number are present, this field will be constructed for you.

    The remaining fields will appear on every query that you do. The report starting and ending dates are shown, but in our simple example, we did not apply the date range to any field. Therefore, these dates are meaningless to us in this example. 

    The query results may be sorted by any of the fields simply by clicking on the field's column heading.

    Query data may be exported via the button. There are a number of export formats available. Use the down-arrow button in the "Save as type:" box to review your options. If you choose to export as Excel Data, ALMSys will automatically provide spreadsheet headings for you.

    Query data may be placed into the print queue via the Q button. While this will not allow you to view the data, it will allow you to perform queue entry tasks on the data, such as Name & Address Requery.

    Special Features
    ALMSys allows additional calculations and fields to enhance your query. 

    Balance Calculations
    These calculations will provide balances, delinquency, and paid data through the end date specified. Additional information can be provided for balance calculations, as follow:

    Date to use in Balance Calculations
    You can choose to use the payment made date or the payment posted date. Usually, you would use the payment posted date.

    Need Billed Detail?
    This will provide billing detail records to be created.

    AMPTax Extract?
    This will provide additional monthly payment detail records to be created. This data will be created in a separate file. (This file is available in the query view screen.)

    Amortization Schedule Balances
    These calculations will provide balances and paid data through the end date specified, based on each payment being made on the payment due day.

    Depreciation Calcs.
    These calculations will provide depreciation information through the end date specified.

    Leveraged Financing Amortization Schedule Balances
    These calculations will provide amortization balances for leveraged financing through the end date specified.

    Active in Date Range only?
    You can have ALMSys weed out deals that were not active as of the end date of the date range. If you choose this feature, please indicate which date is to be used to determine the "active" status. (Normally, this would be the deal date.)

    Field to apply date range against
    ALMSys can apply the report date range against any date field in the database. Do not use this in conjunction with the "Active in Date Range only?" feature.