Reports: General

Contents - Index

Reports: General - Query

This is used to control special queries of ALMSys data. 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.

PLEASE call ALMSys Support for assistance in creating queries.

"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 borrower 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
There are buttons at the top of the window for adding, copying, saving, and running your query. Each query created will need fields to show and possibly other special fields. The tabbed notebook on the right is used to gather information for the query.

Options Tab
Give your query a title. All query results will flow to the print queue, and this title will identify 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.

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

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

Who Can See This Query?
You can make it so that your query is available to all ALMSys users with access to the General Query screen.

Fields Tab
This contains all of the ALMSys data fields that are available for your query. These fields are arranged by table and you will need to expand each table to see the fields. Check the boxes for the fields for which you want to see in your query. You may limit your query to specific field data by double clicking on a selected field. That action will open up the "Query Field Selectors" window. Use the 1. - 5. boxes to limit your query to specific data values, and then select the A - J option on what to do with these values.

The ALMSys tables available for your queries are as follows:

AMPTax Additional monthly payments & taxes table. This contains the master code definitions. This table should not be used in conjunction with other tables. 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.

CollectionFees Collection fee related data (leases). Sorted by account number and 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 for leases and loans. Sorted by account number and deal number.

LeaseChargesDetail Charges assigned to the deal at inception and throughout the deal. 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.

LeaseDepr Lease depreciation detail. Sorted by account number, deal number, and depreciation iteration.

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.

TermPayments Terminated deal payments. Sorted by account number, deal number, and internal numbering order.

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.

Running Your Query
Save your query and the click the Run button in the window heading. When the query has completed, you will be taken to the print queue screen.

(Note: You may find that your resulting query has more fields in it than you selected. This is because fields like "Customer Lessee Name" need additional fields in order to determine who the lessee is and to correctly assemble the name.)