Docstoc

Proposal to

Document Sample
Proposal to Powered By Docstoc
					                Design Specification
                        For
               Terlingua Ranch, Inc.
          Property Owner Database System



                   Prepared for
Property Owners’ Association of Terlingua Ranch, Inc.
                  HC 65 Box 220
             Alpine, Texas 79830-9700




                    Prepared by
                 Majec Systems, Inc.
             5758 Balcones Dr., Suite 102
                 Austin, Texas 78731




                    June 8, 2005
Overview
Data Overview

The new database management system for tracking property and owner account information will use
Microsoft Access as the database repository and user interface. The data that exists in the current dBase
application will be used to seed the new data tables. The new data structure will expand on the current
one to allow storage of more extensive information. The following is a summary listing of the data tables
that will be used by the new system. (See appendix A for a detailed description of all the tables, fields,
data types and relationships.)

Owners
      -      Demographic information about individuals who own property on Terlingua Ranch;
      -      Includes such items as name, phone numbers, email addresses, etc.
Addresses
      -      Address information for each owner;
      -      Having a separate table for addresses allows for tracking changes of address for each person
             and maintaining a history.
         -   Address information will allow for international addresses as well as domestic and the use of
             “in care of” (c/o) addressing.
Tracts
         -   Information about each tract of land on Terlingua Ranch;
         -   Includes tract number, ranch section, acreage, legal description, exemption status, and map
             coordinates.
Accounts
       -     An owner Account is the entity that POATRI deals with concerning property;
       -     May consist of one or more Owners and may own one or more Tracts of land;
       -     All financial information is related to an Account rather than the individual Owners.
Ledger
       -   All financial information (annual assessments, payments, credits, debits, write-offs) for an
           Account.
PaymentPlans
       - POATRI may want to set up a payment plan to assist owners in meeting their financial
           obligations.
       - Information regarding the number of payments, amount, frequency, and duration of each plan
           is stored here.
Foreclosures
       - In the event of payment default, POATRI (or Brewster County) may elect to foreclose on the
           property held by an Account.
       - Information about demand letters, liens, and final foreclosures.




Confidential                                Page 2 of 63                                             6/8/2005
Process and Control Flow

The application will consist of multiple MS Access files. One will be the data repository and contain all
of the data tables defined in Appendix A of this document. The others will contain the user access
programs (forms, reports, queries) including one for each security level.
The user interface for the application will be a screen that has a logo in the top left corner, a title across
the top center, and an exit button in the top right corner. There is a hierarchical function menu down the
left side and a work area on the right. The user interface will be the same for all security levels, but the
functions available on the left menu will vary.


Login Process

We will use the built in security features provided by MS Access. This will enable us to set up
permission levels based on individual users and/or groups. When the application is started, the user will
be required to login. If their security level is not appropriate for that version of the application, a message
will appear and the application will not start.

There are 4 levels of security access. The functions described below will be color-coded to indicate
which security level may perform each function (or some limited version of it).

   1 - Administrator/Manager – full access to all of the following features:
        o Modification of all property owner data;
        o Setup of users, groups, and permissions;
        o Ability to open the Access database containing the data tables.

   2 - POATRI Clerk – this level shall have access to all forms, reports, and the following functions:
        o Query the database;
        o Add records to the database through accessible forms;
        o Modify records in the database through the accessible forms;
        o Deletion of records from the database shall not be allowed at this level.

   3 - Front Desk – this level shall have permission to:
        o View certain property owner data
        o Perform limited modification of property owner, such as address changes.

   4 - Property Owner – this level shall have read-only access to a limited amount of property owner data
    via a report menu.

Each level of user will have access to functions at their level as well as all lesser levels.

We recommend that whenever an employee is terminated, all users be assigned new username and
password combinations.




Confidential                                 Page 3 of 63                                             6/8/2005
Menu

Along the left side of the main application screen there is a hierarchical menu of functions. When the user
moves the mouse over an active function, it changes color. Clicking on an active function will load the
screen for that function into the work area on the right, highlight the menu item and display a star next to
it. The Administrator menu is divided into 5 categories: Account Inquires, Account Activities, Yearly
Activities, Reports, and Database Administration. Each category and its functions are described in detail
below along with color flags indicating the security levels that will be allowed some type of accessibility
to it.

   Account Inquiries 1 2 3 4
   This section of the menu enables the user to view owner account information. The user will select one
   of two methods for searching for the information they want – by owner or tract. When a particular
   owner or tract is found, all information regarding the account of that owner or tract will be gathered
   and displayed for the user. They will initially be presented with a summary screen from which they
   may then elect to view more detailed information in various areas – owner, tract, or financial. The
   selected account will be stored in memory and remain active so if the user selects a different function
   from the left menu, that account will still be selected for that function. (The user may choose a
   different account for the selected function when they get there.)

       1. Inquire by Owner 1 2 3 4




Confidential                               Page 4 of 63                                           6/8/2005
          The screen for looking up accounts by owner presents the user with several input fields they
          can use to help search for a particular owner account. They may enter data into any or all of
          the fields. If they would like to receive a list of all owner accounts in the system, they can
          leave all fields blank. After entering their search criteria, they will click the Find button and a
          summary list of all accounts that meet their search criteria will be displayed in a scrollable
          listbox at the bottom of the screen. The available input fields are:

              Account # - Enter known account # or leave blank. Only accounts exactly matching the
               number entered will be returned. Only numeric data is accepted.

              First Name – The user may enter a string of characters into this field or leave it blank.
               Letters, numbers, and special characters such as &‟*%$?@# are allowed in this field. If
               this field is not blank when the user clicks the find button, the firstName field of the
               Owners table as well as the inCareOf field of the Addresses table is searched for the string
               entered. Partial strings are allowed – i.e. if the user enters “mar” then any owner with the
               first name of Mary, Mark, Annmarie, or Marcy will be returned. Likewise, any accounts
               with an address record that has an inCareOf field that contains a name with the “mar”
               string in it will also be returned.


Confidential                              Page 5 of 63                                              6/8/2005
               *NOTE: The inCareOf field does not separate the first and last name so even if the “mar”
               string is in the last name portion, it will be returned.

              Last Name – The user may enter a string of characters into this field or leave it blank.
               Letters, numbers, and special characters such as &‟*%$?@# are allowed in this field. If
               this field is not blank when the user clicks the find button, the lastName field of the
               Owners table, as well as the inCareOf field of the Addresses table, are searched for the
               string entered. Partial strings are allowed – i.e. if the user enters “joh” then any owner with
               the last name of Johnson, Johns, Majohca, or Fajoh will be returned. Likewise, any
               accounts with an address record that has an inCareOf field that contains a name with the
               “joh” string in it will also be returned.

               *NOTE: The inCareOf field does not separate the first and last name so even if the “joh”
               string is in the first name portion, it will be returned.

              Organization – The user may enter a string of characters into this field or leave it blank.
               Letters, numbers, and special characters such as &‟*%$?@# are allowed in this field. If
               this field is not blank when the user clicks the find button, the organization field of the
               Owners table is searched for the string entered. Partial strings are allowed.

              Include Historical Data – If this box is checked, then any account that ever existed which
               matches the search criteria will be returned. If this box is not checked then only currently
               active accounts matching the search criteria will be displayed.

          The search fields are cumulative, that is, if you enter “mick” in the First Name field and
          “mouse” in the Last Name field, only those owners whose first AND last names meet the
          criteria will be returned. (Mickey Mouse would return but Mighty Mouse would not.) All
          searches are case insensitive. After clicking the Find button, the search results list is
          displayed.

          The columns displayed in the summary listing are Account #, Active, Contact, Name,
          Organization, In Care Of, and City. The Active column is a Yes/No column indicating
          whether the specified account is currently active or not. The Contact column is a Yes/No
          column indicating if the named owner is the main contact for that account.

          There is a radio button at the top of each column indicating how the search results are sorted.
          By default, the results are sorted by owner name. Clicking a different radio button will cause
          the result list to be resorted by that column.

          If the user finds the account they were looking for in the search result list, they can highlight
          that row and click the View Details button to go to an account summary screen The
          accountID is passed to the View Details screen.

          The search criteria will be saved so whenever the user returns to the same search screen the
          criteria will be reentered into the search fields. There is a Clear button on the right below the


Confidential                               Page 6 of 63                                             6/8/2005
          Find button, which enables the user to quickly clear all the search criteria fields so they may
          enter new data.

          Account Summary
          This screen is divided. The left side contains summary information about the individual
          account owners, tracts owned by the account, and the financial state of the account. There are
          “View Details” buttons for each which will display the selected information on the right side
          of the screen.




               Owners
               There is a listbox containing the name and organization for each individual that is a part of
               the selected account. There is also a column showing which owner is the contact for the
               account. Beneath the listbox, there is an email link that will pop up an empty email form
               addressed to all owners of the account. If the user highlights a name in the listbox and
               clicks the View Details button then the following information for that user is displayed in
               the right pane:
                    Name
                    Organization
                    Current Address

Confidential                               Page 7 of 63                                            6/8/2005
                     Phone numbers (home, business, cell, fax)
                     Email Addresses (these are links which can be clicked to send an email)
                     Notes
               (The details for the contact of the account are displayed in the right pane when the user
               first gets to the page.)




               Tracts
               Below the owner listbox is a listbox containing the tract number, ranch section, acreage,
               and exempt status of all tracts owned by this account. If the user highlights a tract and
               clicks the View Details button then the following information for that tract is displayed in
               the right pane:
                    Tract Number
                    Ranch Section
                    Legal Description
                    Acreage
                    Map Coordinates
                    Exempt Status
                    Notes
                    Subdivision information (tract number of parent tract if this is the result of a
                       subdivision)

Confidential                               Page 8 of 63                                            6/8/2005
                     History of ownership – listbox with the account number, contact name, and date of
                      acquisition for all previous owners of the tract (or its parent tract).




               Finances
               Below the tract listbox is a financial summary of the selected account. It will display the
               following information:
                     Past Due
                     Current Assessment
                     Current Payments
                     Late Fees
                     Debits/Credits
                     Account Balance
               Additionally, if the account has a payment plan set up for it, there will be a Payment Plan
               button visible, and if they account is anywhere in the foreclosure process, there will be a
               Foreclosure button visible. If the user clicks the View Details button then the complete
               listing of all ledger entries for the account is displayed in the right pane. They are listed in
               reverse chronological order and have the following columns:
                     Date
                     Description (payment, credit, annual assessment, etc.)
Confidential                                Page 9 of 63                                              6/8/2005
                     Amount
                     Running Balance




               If the user clicks the Payment Plan button the following information is displayed in the
               right pane:
                     Total Due
                     Payment Amount
                     Frequency of payments
                     Number of Payments
                     Date of first payment
                     Date Agreement was sent
                     Date Form Letter was sent
                     Notes
                     Alternate Payer info:
                           o Name
                           o Address
                           o Phone
                           o Email

Confidential                              Page 10 of 63                                           6/8/2005
               If the user clicks the Foreclosure button the following information is displayed in the right
               pane:
                     Tract numbers involved in the foreclosure
                     Deed Holder
                     Date Demand letter was sent
                     Date Lien was filed
                     Date of foreclosure
                     Date made good (if owner paid his way out of the foreclosure)
                     Notes


       2. Inquire by Tract 1 2 3 4




Confidential                              Page 11 of 63                                             6/8/2005
          The screen for looking up accounts by tract presents the user with several input fields they can
          use to help search for a particular owner account. They may enter data into any or all of the
          fields (or none if they would like to receive a list of all owner accounts in the system). When
          they have entered their search criteria, they will click the Find button and a summary list of all
          accounts that meet their search criteria will be displayed in a scrollable listbox at the bottom of
          the screen. The available input fields are:

                  Account # -- If the user already knows the account number they want to view, they can
                   enter it here. The user must enter numeric data into this field. Only accounts exactly
                   matching the number entered will be returned.
                  Tract Number – The user may enter a string of characters into this field. Letters,
                   numbers, and special characters such as &‟*%$#@? are allowed in this field. If this
                   field is not blank when the user clicks the Find button, the tractNumber field of the
                   Tracts table is searched for the string entered. Partial strings are allowed.
                  Ranch Section – The user may select one of the ranch sections from the drop-down
                   list. If a section is selected when the user clicks the Find button, the sectionID field of
                   the Tracts table is searched for the matching section.
                  Legal Description – The user may enter a string of characters into this field. Letters,
                   numbers, and special characters such as &‟*%$#@? are allowed in this field. If this
Confidential                               Page 12 of 63                                             6/8/2005
                   field is not blank when the user clicks the Find button, the legalDescription field of the
                   Tracts table is searched for the string entered. Partial strings are allowed.
                  Include Historical Data – If this box is not checked, then only currently active
                   accounts matching the search criteria will be displayed. If this box is checked, then
                   any account that ever existed which matches the search criteria will be returned.

          The search fields are cumulative, that is, if you enter “NC-” in the Tract Number field and
          select the Solotario ranch section, only those tracts in the Solotario section with tract numbers
          containing the string “NC-“ will be returned. After clicking the Find button, the search results
          list is displayed.

          The columns displayed in the summary listing are Account #, Active, Tract Number, Ranch
          Section, Acreage, and Legal Description. The Active column is a Yes/No column indicating
          whether the specified account is currently active or not.

          There is a radio button at the top of each column that indicates which column the data is sorted
          on. By default, the results are sorted by Tract Number. Clicking a different radio button will
          cause the result list to be resorted by that column.

          If the user finds the account they were looking for in the search result list, they can highlight
          that row and click the View Details button to go to a screen containing the account summary
          information. The accountID is passed to the View Details screen.

          The search criteria will be saved so whenever the user returns to the same search screen the
          criteria will be reentered into the search fields. There is a Clear button on the right, below the
          Find button, enabling the user to quickly clear all the search criteria fields so they may enter
          new data.

   Account Activities 1 2 3
   This section of the menu allows the user to perform activities related to the maintenance of account
   owners, property, and finances. The 9 menu options are: Modify Owner Information, Modify Tract
   Map Information, Subdivide a Tract, Transfer Ownership, Payments and Adjustments, Setup Payment
   Plans, Process Foreclosures, Enter Volunteer Info, and Update Deed Transfer Date.


       1. Modify Owner Information 1 2 3
          This option enables the user to change basic information for an owner such as their address,
          phone number, email, etc. Selecting this option will present the user with the same search
          form as described in the Inquire by Owner section above. After clicking the Find button and
          selecting the person whose info you want to modify, the user will click the Modify button to
          go to the Modify Owner screen described next. The ownerID is passed to the Modify Owner
          screen.




Confidential                              Page 13 of 63                                             6/8/2005
          This screen will display all of the demographic information for the owner selected on the
          preceding screen. All information on the form is editable. The following fields are included
          (an * indicates a required field):
               First name*
               Middle name
               Last name*
               Suffix (this is for Jr., Sr., III, Esq., etc.)
               Home phone*
               Business phone
               Cell phone
               Fax
               Primary email address
               Alternate email address
               Organization
               Notes (anything you want to add that doesn‟t fit anywhere else)
               Address
                  There will usually only be one address per owner, but the system will maintain all
                  addresses that an owner has ever used. The current address will be displayed in
                  separate address fields on the form and can be edited. All previous addresses used for

Confidential                            Page 14 of 63                                           6/8/2005
                  this person will be displayed in the listbox below the current address. There is an “Add
                  New Address” button that will move the current address to the old address listbox and
                  clear the current address fields for the user to enter new data.

          When the user is satisfied that the information they entered for this owner is accurate, they can
          click the Save button to make the changes permanent and be returned to the previous screen.
          There is a Cancel button which will return the user to the previous screen without saving any
          changes.

          Saving will modify the selected record in the Owners table and the Addresses table.

          Possible error conditions:
          No changes are saved until all errors are corrected.

          Required field not filled in – If one of the required fields is left blank, an error message will be
          displayed and the user must enter the missing data and hit save again.

       2. Modify Tract Map Info 1 2
          This option enables the user to add or change the map coordinates for a tract. Selecting this
          option will present the user with the same search form as described in the Inquire by Tract
          section above. After clicking the Find button and selecting the tract whose coordinates are to
          be modified, the user will click the Modify Map Info button to go to the screen described next.
          The tractID is passed to the Modify Tract Map Info screen.




Confidential                              Page 15 of 63                                              6/8/2005
          This screen will display all of the information for the tract selected on the preceding screen.
          Only the two map coordinate fields are editable and neither is required.
              X Coordinate – The north/south designation (could be Latitude or UTM coordinate.)
              Y Coordinate – The east/west designation (could be Longitude or UTM coordinate.)

          Additionally, for information purposes only, the current owners are listed. When the user is
          satisfied that the information they entered for the map coordinates is accurate, they can click
          the Save button to make the changes permanent and be returned to the previous screen. There
          is a Cancel button which will return the user to the previous screen without saving any
          changes.

          Saving will update the map coordinate fields in the selected tract record in the Tracts table.

          Possible error conditions:
          None.

       3. Subdivide a Tract 1 2
          This option enables the user to subdivide an existing tract of land into multiple smaller tracts.
          After selecting the tract to be subdivided, the user will specify how many child tracts to create

Confidential                              Page 16 of 63                                            6/8/2005
          and will then enter the new tract information for each of them. The ranch section and exempt
          status of the original tract will be used for all child tracts. All other information will have to
          be entered new. Each new child tract will point back to the original tract as its parent for
          historical purposes. All new tracts will be assigned to the same owner account as the original
          tract.




          Selecting this option will present the user with the same search form as described in the
          Inquire by Tract section above. After clicking the Find button and selecting the tract that is to
          be subdivided, the user will enter the number of child tracts and click the Subdivide button to
          go to the Subdivide a Tract screen described next. The tractID is passed to the Subdivide a
          Tract screen.




Confidential                              Page 17 of 63                                             6/8/2005
          The screen will display a summary of the original tract information at the top of the screen.
          Then there will be a 3-field form for each child tract specified. Each form will request the
          following information (an * indicates a required field):
               Tract Number*
               Legal Description
               Acreage*

          When the user is satisfied that the information for all new tracts is accurate, they can click the
          Save button to make the changes permanent and be returned to the previous screen. There is a
          Cancel button which will return the user to the previous screen without saving any changes.

          Saving will update the original tract record by setting the isSubdivided flag to Yes and creating
          new records in the Tracts table for each child tract. It will also create new records in the
          AccountTracts table so the new tracts are associated to the same owner account as the original.

          Possible error conditions:
          No changes are saved until all errors are corrected.



Confidential                             Page 18 of 63                                             6/8/2005
          Required field not filled in – If one of the required fields is left blank, an error message will be
          displayed and the user must enter the missing data and hit save again.

          Duplicate tract number – If the tract number the user entered is already being used in the
          database by another tract, an error message is returned and the user must choose a new tract
          number. This error is also generated if the user enters the same tract number for more than one
          child tract.

          Incorrect acreage total – The total acreage for the new child tracts must equal the acreage of
          the original tract. If it does not, an error message will be returned noting the discrepancy.


       4. Transfer Ownership 1 2
          This option enables the user to transfer ownership of selected tracts from one owner account to
          another owner account. If the new owner account does not already exist in the database, the
          user can create a new one and assign or add owners to it.

          Selecting this option will present the user with the same search form as described in the
          Inquire by Tract section above. After clicking the Find button and selecting one of the tracts to
          be transferred, the user will click the Transfer Ownership button. The accountID are passed to
          the Transfer Ownership screen.




Confidential                              Page 19 of 63                                              6/8/2005
          This screen is similar to the Account Summary screen described in the Inquire by Owner
          section above. The left side of the screen shows the seller‟s account summary information but
          instead of the detail information displaying on the right side of the screen when the user clicks
          a View Details button, the information is shown in a pop-up window. Additionally, the tracts
          listbox will allow the user to select multiple tracts to indicate which ones are to be transferred
          to a new owner.

          The user must select the buyer‟s account. They may either enter search information for an
          existing account and click the Find button to view the owner list and financial information for
          that account or click the Create New Owner Account button which will pop-up a screen for
          creating a new account.

               Create New Owner Account




Confidential                             Page 20 of 63                                             6/8/2005
               This screen will display the new owner account number at the top of the screen. On the
               right, it will display a listbox for the names of all the owners to be associated with this new
               account – initially empty. Another listbox on the left will allow the user to search for
               existing owners that they can associate with the new account.

               The user will select a name in the left listbox and click the >> button to move it to the
               owner listbox for this new account. There will also be a button to Add New Owner in the
               event that the owner to be added to the account is not already in the system. These actions
               can be repeated until all of the owners to be associated with this account have been moved
               to the new owner listbox on the right.

               When the user is satisfied that the list of owners for the selected account is accurate, they
               should highlight the individual who will be the contact for the account and click the Save
               New Account button to make the changes permanent and be returned to the previous
               screen. There is a Cancel button which will return the user to the previous screen without
               adding the new account and removing any new owners that were added in the process.

               Saving will create a new record in the Accounts table.

               Possible error conditions:
               No changes are saved until all errors are corrected.

               Empty owner list – There must be at least one owner assigned to an owner account.

               No contact selected – There must be one owner designated as the contact for the account.


Confidential                               Page 21 of 63                                             6/8/2005
               More than one contact selected – There must be only one owner designated as the contact
               for the account.


               Add New Owner




               This form will allow the user to enter information for a new owner. It will be the same as
               the form described for modifying an owner above except that it will not contain the old
               address listbox or Add New Address button.

               When the user is satisfied that the information they entered for this owner is accurate, they
               can click the Save button to make the changes permanent and be returned to the previous
               screen. There is a Cancel button which will return the user to the previous screen without
               adding the new owner.

               Saving will create a new record in the Owners table and Addresses table for this account.
               It will also update the owner listbox on the Create New Owner Account screen to include
               this new owner.

               Possible error conditions:
               No changes are saved until all errors are corrected.




Confidential                              Page 22 of 63                                            6/8/2005
               Required field not filled in – If one of the required fields is left blank, an error message
               will be displayed and the user must enter the missing data and hit save again.

               When the user has completed the Create New Owner process and returned to the Transfer
               Ownership screen, the new owner account will be visible in the search box and the owners
               will be displayed in the New Owner listbox. There will be a Proceed with Transfer button
               at the bottom of the page that the user will click when they are certain the correct buyer
               information has been selected. There is a Cancel button available also which will return
               the user to the initial search screen and remove any newly added owners and accounts.

               Transfer Summary Screen




               If the user clicks the Proceed with Transfer button, a Transfer Summary screen pops up.
               At the top of the screen is a listbox containing all the tracts that are to be transferred. The
               left side of the screen contains seller information and the right side contains buyer
               information. First there is a listbox with the individual owner names. Below that is box
               containing the details of any automatic adjustments that the system determines are
               necessary for the buyer or seller. Each has a checkbox next to it so the user can choose
               whether or not to use the automatic adjustments when finalizing the transfer. Then there is
               a place where the user can enter manual adjustments. And below that is the account‟s
               financial summary as seen on all the other pages. At the very bottom of the screen is a
               place for the user to enter the Date Ownership Transferred, Date Deed Transferred, and
               notes specific to the transfer.


Confidential                               Page 23 of 63                                              6/8/2005
               If the user checks or unchecks the Use Auto box, the financial summary for that account
               will be updated to reflect the change. The user may not pick and choose which automatic
               adjustments to use if multiple are listed. They must use all or none. If they wish to use
               just one, for instance, then they should uncheck the Use Auto box and enter the adjustment
               they want manually. To make a manual adjustment, the user fills in the amount, type, and
               notes(optional) fields then clicks Apply. The financial summary will be updated and the
               input fields cleared so another adjustment can be entered.

               The user fills out the remaining transfer information on the screen (an * indicates a
               required field):
                   Date ownership transferred (defaults to today‟s date)*
                   Date deed transferred (defaults to today‟s date)
                   Notes (anything you want to add about this transfer)

               When the user is satisfied that the information for the transfer is accurate, they can click
               the Complete Transfer button to make the changes permanent and be returned to the
               original search screen for the transfer process. There is a Cancel button which will return
               the user to the previous screen without transferring any property. Any adjustments that
               were made on this screen are rolled back.

               Completing the transfer will cause a new record for each tract to be created in the
               AccountTracts table with the transfer information entered above along with the previous
               owner accountID. These records will have the isCurrent flag set and the records in the
               AccountTracts table for the previous owner account will have the isCurrent flag turned off.
               Adjustments will be added to the Ledger table for each owner account as appropriate.

               Possible error conditions:
               No changes are saved until all errors are corrected.

               Required field not filled in – If one of the required fields is left blank, an error message
               will be displayed and the user must enter the missing data and hit save again.

       5. Payments and Adjustments 1 2
          This option enables the user to enter payments or adjustments against an owner account ledger.
          Selecting this option will present the user with the same search form as described in the
          Inquire by Owner section above. After clicking the Find button and selecting a specific owner
          account the user should click the Payments & Adjustments button to proceed to a screen where
          they can enter the payment and adjustment information. The accountID is passed to this
          screen.




Confidential                               Page 24 of 63                                              6/8/2005
          This screen is similar to the Account Summary screen described in the Inquire by Owner
          section above. The left side of the screen shows the same account summary information but
          instead of the detail information displaying on the right side of the screen when the user clicks
          a View Details button, the information is shown in a pop-up window. The right side of the
          screen contains the input fields for entering payments and/or adjustments.

          In order to expedite entry of payments for multiple accounts, there will be an Account Number
          entry field at the top of the left side of the page and a Find button so the user does not have to
          return to the search page between each account. If they have entered payments and/or
          adjustments for an account and try to find another before committing those transactions, a
          message box will appear reminding them to commit first.

          Above the payments section are two buttons for tracking batches of payments. The user may
          click the Start Batch button to group all subsequent payment entries until the Stop Batch
          button is clicked. When the Stop Batch button is clicked a report will pop up listing all the
          payments in that batch so the user may print it out to send to the bank. The input fields for
          payments are listed below (an * indicates a required field):
               Amount paid*
               Date paid* (defaults to today‟s date)

Confidential                             Page 25 of 63                                             6/8/2005
                  Payment method* (check, credit card, cash, money order)
                  Reference number (check #, money order #, etc.)
                  Notes (any comment you want to add about this payment)

          When the user is satisfied that the information for the payment is accurate, they can click the
          Apply button which will update the financial summary on the left and clear the fields so
          another payment can be entered for the same account.

          At the bottom are the input fields for adjustments and they are listed below (an * indicates a
          required field):
               Date of Adjustment* (defaults to today‟s date)
               Amount *
               Transaction type* (debit, credit, foreclosure write-off, amnesty write-off)
               Notes (any comment you want to add about this adjustment)

          When the user is satisfied that the information for the adjustment is accurate, they can click the
          Apply button which will update the financial summary on the left and clear the fields so
          another adjustment can be entered for the same account.

          When the user has finished entering payments and adjustments for the selected account, they
          can click the Commit Transactions button to make the ledger entries permanent. There is a
          Cancel button which will remove any payments or adjustments made for the selected account.

          Possible error conditions:
          No changes are saved until all errors are corrected.

          Required field not filled in – If one of the required fields is left blank, an error message will be
          displayed and the user must enter the missing data and hit save again.

       6. Setup Payment Plan 1 2
          This option enables the user to set up a payment plan for a past due owner account. Selecting
          this option will present the user with the same search form as described in the Inquire by
          Owner section above. After clicking the Find button and selecting a specific owner account
          the user should click the Setup Payment Plan button to proceed to the next screen. The
          accountID is passed to this screen.




Confidential                              Page 26 of 63                                              6/8/2005
          This screen is similar to the Account Summary screen described in the Inquire by Owner
          section above. The left side of the screen shows the same account summary information but
          instead of the detail information displaying on the right side of the screen when the user clicks
          a View Details button, the information is shown in a pop-up window. The input fields are
          listed below (an * indicates a required field):
                Total amount due on this plan* (defaults to the account balance)
                Individual payment amount*
                Frequency of the payments* (weekly, bi-weekly, monthly, quarterly, other)
                Other frequency (*if other is selected above, this is required)
                Number of payments*
                Date first payment due*
                Date the agreement was sent*
                Date the form letter was sent*
                Notes (any comment you want to add about this payment plan)
               If not being paid by the owner, then the user may also enter information about the alternate
               payer:
                Payer name (if this field in filled in, the other * fields below are required)
                Address line 1*
                Address line 2

Confidential                             Page 27 of 63                                            6/8/2005
                  City*
                  State*
                  Zip*
                  Country
                  Phone
                  Email

          When the user is satisfied that the information for the plan is accurate, they can click the Save
          button to make the changes permanent. There is a Cancel button which will return the user to
          the previous screen without recording the payment plan.

          Saving will create a new record in the PaymentPlans table for this account. The user remains
          on this page so they can select the Print Agreement and/or Print Form Letter buttons to display
          those documents which the user will then print out and mail to the owner (or alternate payer if
          one is specified). Clicking these buttons will cause the database to update the
          dateAgreementSent or dateLetterSent fields in the PaymentPlans table with the current date.

          Possible error conditions:
          No changes are saved until all errors are corrected.

          Required field not filled in – If one of the required fields is left blank, an error message will be
          displayed and the user must enter the missing data and hit save again.

       7. Process Foreclosure 1 2
          This option enables the user to begin or continue processing a foreclosure for specific tracts of
          land owned by a past due owner account. Selecting this option will present the user with the
          same search form as described in the Inquire by Tract section above. After clicking the Find
          button and selecting a tract or account that will be foreclosed on, the user will click the
          Foreclosure button to go to the next screen. The accountID is passed to the Foreclosure
          screen.




Confidential                              Page 28 of 63                                              6/8/2005
          This screen is similar to the Account Summary screen described in the Inquire by Owner
          section above. The left side of the screen shows the same account summary information but
          instead of the detail information displaying on the right side of the screen when the user clicks
          a View Details button, the information is shown in a pop-up window. If no foreclosure
          process has already been started for the selected account, a listbox of all tracts owned by the
          selected account is displayed at the top of the right side of the form. The user highlights all
          tracts that are being foreclosed on then completes the remaining input fields for foreclosure
          information. If a foreclosure process has already been started, then the tracts that were
          originally designated as part of the foreclosure are displayed at the top of the right side of the
          screen and the user can update the remaining fields. They are listed below (an * indicates a
          required field):
               Deed holder* (POATRI or Brewster County)
               Date letter sent* (defaults to today‟s date)
               Date lien filed
               Lien certificate #
               Date of foreclosure
               Date Made Good (in the event the owner pays off the debt)
               Notes (any comment you want to add about this payment plan)


Confidential                             Page 29 of 63                                             6/8/2005
          When the user is satisfied that the information about the foreclosure is accurate, they can click
          the Save button to make the changes permanent. There is a Cancel button which will return
          the user to the previous screen without recording the foreclosure changes.

          Saving will create a new record or update the existing record in the Foreclosure table for this
          account. The user will be left on this screen where they can select the Print Demand Letter
          button or the Print Lien button (if the lien date is filled in) to display that document which the
          user will then print out and mail to the owner.

          Possible error conditions:
          No changes are saved until all errors are corrected.

          Required field not filled in – If one of the required fields is left blank, an error message will be
          displayed and the user must enter the missing data and hit save again.

       8. Enter Volunteer Info 1 2
          This option enables the user to flag an owner of exempt property as a volunteer account.
          Accounts set up with this information will be sent reminder letters at the same time that annual
          assessments are sent out. The amount requested in the reminder letter will be determined by
          the information entered via this option.

          Selecting this option will present the user with the same search form as described in the
          Inquire by Owner section above. After clicking the Find button and selecting a specific owner
          account, the user should click the Volunteer Info button to proceed to the next screen. The
          accountID is passed to this screen.




Confidential                              Page 30 of 63                                              6/8/2005
          This screen is similar to the Account Summary screen described in the Inquire by Owner
          section above. The left side of the screen shows the same account summary information but
          instead of the detail information displaying on the right side of the screen when the user clicks
          a View Details button, the information is shown in a pop-up window. The input fields are
          listed below (an * indicates a required field):
                A checkbox indicating that a reminder letter should be sent.
                A checkbox indicating that the amount requested should be calculated using the same
                  formula as is used for non-exempt property.
                Fixed Amount – if Use Non-Exempt Rate is not checked, then this is the fixed amount
                  that will be requested in the reminder letter.

          When the user is satisfied that the volunteer information is accurate, they can click the Save
          button to make the changes permanent and return the user to the previous search screen. There
          is a Cancel button which will return the user to the previous screen without recording any
          changes.

          Saving will update the above fields in the Account table for the selected owner account.

          Possible error conditions:

Confidential                             Page 31 of 63                                            6/8/2005
          No changes are saved until all errors are corrected.

          Non-numeric value in the Fixed Amount field – If the user tries to enter alpha characters in
          this field, an error message will be returned.

          No rate specified – If the user leaves the Use Non-Exempt Rate unchecked and does not enter
          a fixed amount to request, an error message will be returned.


       9. Update Deed Transfer Date 1 2
          When a new owner acquires property and they take ownership, sometimes the deed is held
          until some future time (like when a loan is paid off). In these cases, the Date Deed Transferred
          for the properties changing hands was left blank during the Transfer Ownership process
          described earlier. When the deed finally does transfer to the new owner, the admin user can go
          in and update the Date Deed Transferred fields for the affected tracts.

          Selecting this option will present the user with the same search form as described in the
          Inquire by Tract section above. After clicking the Find button and selecting a tract that is part
          of the Contract for Deed property, the user will click the Update Deed Transfer button to
          proceed to the next screen. The tractID is passed to this screen.




Confidential                             Page 32 of 63                                             6/8/2005
          This screen is similar to the Account Summary screen described in the Inquire by Owner
          section above. The left side of the screen shows the same account summary information but
          instead of the detail information displaying on the right side of the screen when the user clicks
          a View Details button, the information is shown in a pop-up window. Knowing the one
          tractID involved in the deed transfer will enable the program to find all tracts associated with
          that transfer. A listbox containing the tract number, ranch section, acreage, and exempt status
          of all tracts that are part of this deed will be displayed at the top of the right side of the screen.
          Below will be a display of the date ownership transferred for these properties. The following
          editable fields will also be displayed:
               Date Deed Transferred*
                   The date the user enters here will apply to all tracts listed at the top of the screen.
               Notes
          .

          When the user is satisfied that the volunteer information is accurate, they can click the Save
          button to make the changes permanent and return the user to the previous screen. There is a
          Cancel button which will return the user to the previous screen without recording any changes.

          Saving will update the above fields in the AccountTracts table for the selected tracts.
Confidential                               Page 33 of 63                                               6/8/2005
          Possible error conditions:
          No changes are saved until all errors are corrected.

          Required field not filled in – If one of the required fields is left blank, an error message will be
          displayed and the user must enter the missing data and hit save again.

          Invalid date entered in a date field – If the user enters a date that does not exist (6/31/2005) or
          enters the date in the wrong format (correct format is mm/dd/yyyy or mm/dd/yy), then an error
          message is returned.

   Yearly Activities 1
   This section of the menu is for those tasks performed once a year (or in the case of ballots maybe
   more than once but not often). Only users with administrative level access will see these options. The
   options are Annual Assessment, Assess Late Fees, Volunteer Reminders, and Ballots.

       1. Annual Assessment




          This function should be undertaken with great care, as it will create a new record in every
          accounts‟ ledger. The rates that will be used to calculate the annual assessment are displayed

Confidential                              Page 34 of 63                                              6/8/2005
          on the screen along with the date and an Assess Fees button to start the process. If the
          administrator has not entered the rates into the REFAnnualAssessments table for the next
          assessment year, then an error message will appear and no Assess Fees button will be
          displayed. The administrator must select the Edit Reference Tables function to enter the
          information.

          If the assessment for the current year has already been run, then a message indicating such will
          be displayed and no Assess Fees button will be displayed.

          When the user is ready, they may click the Assess Fees button to start the assessment process.
          During the processing, a status bar will be displayed so the user can monitor the progress of
          the assessments. This window will have a Cancel button that, if clicked, will cause all
          assessments made so far to be rolled back.

          The assessment process will do the following for every active account:
              Look at each tract record owned by that account.
              If all tracts are exempt, no fees are assessed and the total of exempt accounts is
                 incremented by one.
              When the first non-exempt tract is found, the flat fee is assessed
              For every non-exempt tract, the acreage is accumulated then the total is multiplied by
                 the per-acre rate specified. This amount is added to the flat fee to create the total
                 assessment fee.
              A new ledger record is created for this account. The assessment date, assessment fee,
                 and transactionTypeID of “annual assessment” are stored in this record.
              The total number of accounts assessed is incremented by one.
              The total annual assessment fee is incremented by this account‟s assessment fee.

          When it is complete, a summary will be displayed on the screen showing the total number of
          accounts assessed a fee, the total dollar amount assessed, and the total number of accounts that
          were completely exempt. A Detailed Report button and a Print Bills button will be displayed
          and the Assess Fees button will be hidden.

          If the user clicks the Detailed Report button a report with the following information for each
          account will be displayed:
                Account Number
                Owners with the contact indicated
                Address of the contact
                A list of each tract number with acreage and exempt/non-exempt flag
                Total non-exempt acres
                Total per-acre fee
                Flat fee
                Total assessment fee

          If the user clicks the Print Bills button, a report with the same information listed above but
          formatted as a bill, with the name and address placed so that it will show through a window



Confidential                             Page 35 of 63                                            6/8/2005
          envelope and the summary information repeated on a portion of the page which can be torn off
          and returned by the owner with the payment.

       2. Assess Late Charges




          When this option is selected, if it is between January 1st and March 30th, the late fees for the
          previous calendar year assessment will be processed. If it is between April 1st and December
          31st, the late fees for the current calendar year assessment will be processed. The data is
          examined to see if any late fees for the specified year have already been assessed. If they
          have, a message will be displayed indicating such. Otherwise a button for assessing the 90-
          day and/or 365-day late fee is displayed along with the rate at which the fee will be calculated.

          If the 90-day late fee is selected, all accounts that had a positive account balance as of April 1st
          are assessed a late fee.

          If the 365-day late fee is selected, a test is done to be sure it is past January 1st. If the test
          passes, all accounts that had a positive account balance as of January 1st for fees assessed the
          previous year are assessed a late fee.



Confidential                              Page 36 of 63                                              6/8/2005
          Assessing the late fee will cause a new record to be created in the Ledger table for each
          account meeting the criteria specified above. The account balance is multiplied by the
          appropriate late fee percentage found in the REFAnnualAssessment table for the specified
          year. This rate will be displayed with a list of all accounts that have been assessed a late fee.

          If the 90-day late fee option was selected, then a Print Bills button is displayed. Clicking this
          button will display a report formatted as a bill to be mailed to the owners of each account who
          were assessed fees.

       3. Volunteer Reminders




          When this option is selected, the user will see a list of all accounts that have indicated they
          would like to receive a reminder each year to make a payment, even though the property they
          own is exempt. The list will show the following columns of information:
              Account Number
              Name of contact for the account
              Organization name
              Flag indicating if the amount should be calculated like non-exempt property
              Fixed amount to request if the previous flag is set to No.

Confidential                              Page 37 of 63                                             6/8/2005
          If the user clicks the Detailed Report button, they will see a summary for each volunteer
          account showing the owner names, all tracts with a flag indicating which are exempt, and the
          amount requested as a voluntary payment. This amount will either be calculated using the
          non-exempt formula on the exempt property or be the fixed amount noted in their account
          record. A note will indicate which method was used. A total at the end of the report will show
          how much was requested from all volunteers.

          If the user clicks the Print Reminder Letters button, a letter to each volunteer will be printed
          indicating the property they own which is exempt and the amount being requested as a
          voluntary payment. Additionally, when this button is clicked, a ledger record with the amount,
          current date, and transaction type of Volunteer Debit will be added for each account in the list.


       4. Ballots
          Periodically, the property owners are asked to vote for candidates to the Board of Directors,
          amendments to the Maintenance Agreement and other issues. The process for this balloting is
          as follows:
               Ballots are mailed to all property owners, one ballot for each owner account. If the
                  voting is for candidates to the Board of Directors, each property owner is allowed to
                  vote for three candidates. The ballots are returned to a separate Post Office Box, whose
                  key is given to an independent vote counter who is not a property owner.
               Two weeks before the January meeting of the Board of Directors, the administrator of
                  the owner database enters the candidates and/or issues and amendments that will
                  appear on the ballot. Then a voter list is compiled and all are exported to an
                  independent Ballot database. This voter list describes the voting eligibility of each
                  property owner. They are eligible if they are “in good standing”, meaning that their
                  account has no past due balance. After the list is exported, a change to the status of the
                  account will not affect the eligibility of a property owner.
               The vote counter counts the ballots, recording the vote count for each candidate, as
                  well as yes/no votes on amendments and issues.
               The results of the ballot count are presented by the vote counter at the January meeting
                  of the Board of Directors.

          The Ballot database will assist the vote counter in his/her tasks. It will record votes for each
          candidate and/or issue and compile totals and statistics. The database will be created at the
          same time as the voter list, two weeks before the January meeting. Because it is to be delivered
          to a person outside of the POS security zone, it can contain no sensitive information that would
          violate the privacy of the property owners.

          An Access database was created last year to accommodate this need. What follows is a
          description of that database. We will use this existing database as a basis for the ballot
          functionality in the new system. The new data tables will be used and the code will be
          modified to accommodate the new schema.
          The Database Schema
          The Ballot database will consist of the following tables:
Confidential                              Page 38 of 63                                            6/8/2005
                      Ballots
               AccountID         AccountTracts            AccountOwners
               GoodStanding     AccountID                AccountID
               Valid            TractID                  OwnerID
               BallotReceived   TractNumber              IsContact
               Candidate_1      Acres                    Organization
               Candidate_2                               LastName
               Candidate_3                               FirstName
               Amendment_1                               Address1
               Amendment_2                               Address2
               etc. ...                                  City
               Issue_1                                   State
               Issue_2                                   Zip
               etc. ...                                  Country


          Creating the Ballot Database
          The ballot database will be created by someone with the highest authorization, such as the
          Operations Manager. There will be three steps involved in the creation of the database: the
          specification of the candidates for the Board of Directors, the specification of amendments and
          issues and the creation of the database. The three steps will be integrated through the creation
          of a wizard that guides the user through the process.

          Entering Candidate Names
          After the introductory screen of the wizard, the user is presented with a screen that enables
          him/her to enter the names, if any, of the candidates for election to the Board of Directors.




Confidential                             Page 39 of 63                                            6/8/2005
          When all the candidates have been entered, the user selects one of the following options:
           Cancel – aborts the process of creating the database without saving the names of the
            candidates
           Back – returns to the previous screen database without saving the names of the candidates
           Next – saves the names of the candidates and proceeds to the next step
           Finish – saves the names of the candidates and terminates the process of database creation.

          Entering Amendments and Issues
          The next screen of the wizard presents the user with the ability to enter the names of
          amendments and/or issues that will appear on the ballot.




Confidential                             Page 40 of 63                                             6/8/2005
          When all the amendments/issues have been entered, the user selects one of the following
          options:
           Cancel – aborts the process of creating the database without saving the names of the
              amendments/issues
           Back – returns to the previous screen database without saving the names of the
              amendments/issues
           Finish – saves the names of the amendments/issues and terminates the process of database
              creation.

          Creation of the database
          If the user selects Create Ballots from the main ballot menu, the system will launch a process
          that exports the Ballots, AccountTracts, and AccountOwners tables. Both the Ballots table and
          the Ballot Entry Form must be created dynamically, since the number and names of the
          possible amendments and issues are dynamic. The process will be stoppable. It should also
          allow the user to restart it without having to re-enter the names of the candidates and issues.

          The process will read the owner database and extract the necessary data for the Ballots,
          AccountTracts, and AccountOwners tables. It will compute the eligibility (Good Standing) of
          each owner using the algorithm that computes current receivables. If that algorithm indicates a
          past due amount, then the owner is not in good standing and is not eligible.


Confidential                            Page 41 of 63                                           6/8/2005
          When complete, the system should notify the user that the process was completed successfully.

          The Voter List
          The voter list is a report based upon the Ballots Table. It should clearly state the date on which
          the list was compiled and list the following fields:
           Account
           Name of the primary contact
           Eligibility of the account

          A sample voter list is shown below.




Confidential                             Page 42 of 63                                             6/8/2005
          Entering Ballots
          In this part of the Ballot process, the user is the vote counter. The process involved is
          deceptively simple: find the Account in the database that matches the information on the ballot
          and record the votes.

          There are several factors which make this process more difficult than it might at first appear:
           The ballot is a generic one. It does not indicate the Account number.
           The person submitting the ballot is responsible for writing his/her name. The name is
             sometimes illegible. In addition, it might be submitted by someone other than the primary
             contact for the Account.
           On some ballots the vote counter must search for the account by tract number, which isn‟t
             necessarily listed on the ballot. In some cases, the tract number is incorrect.

          These factors place additional usability requirements on the search capability.

          Searching for Owners
          The user can begin the search by entering the first name and/or last name that is on the paper
          ballot. Partial names are permitted.




Confidential                             Page 43 of 63                                           6/8/2005
          The search finds all owners with the specified first and/or last names. All records are returned
          that match the search criteria.

          If the user inputs a tract number, then the search includes all owners who own the specified
          tract. Partial tract numbers are permitted, in which case the search includes all owners who
          own a tract whose number contains the specified partial tract number.

          In either case, the user will have the ability to select a record from the search results and either
          go directly to the Ballot Entry Form or to view the Owner Details, which will include any
          details about the owner which are not present on the summary screen, such as a list of all tracts
          owned.

          If the search finds exactly one record matching the search criteria, then the user will be
          presented with the Ballot Entry Form

          The Ballot Entry Form
          This is the form in which the user records the votes from the paper ballot. It is created
          dynamically during the creation of the ballot database as described in the first part of this
          section. It will record the following information for each property owner:
               Whether or not a ballot was received
               Whether or not the property owner was in good standing
               Whether or not the received ballot was valid
               The vote for each candidate
               The vote for each amendment/issue

Confidential                              Page 44 of 63                                             6/8/2005
          The following screenshot shows the Ballot Entry Form for an account that is in good standing.




          < Screen shot removed to protect the privacy of the owner >




          The user must first indicate that the Ballot has been received and whether or not it is valid. The
          validity of the ballot is based upon factors, known to the user, which are not relevant to this
          design document. If the user checks that the ballot was received and is valid, then the
          Candidate drop down lists and the Amendment/Issue option groups are enabled, allowing the
          user to record the votes cast by the owner. The candidate drop-down lists are populated with
          the names that were entered during the creation of the ballot database as described in the first
          part of this section. The Amendments and Issues option groups also correspond to the names
          that were entered at the same time.




Confidential                             Page 45 of 63                                            6/8/2005
          < Screen shot removed to protect the privacy of the owner >




          The user can also enter the name of a write in candidate.




Confidential                            Page 46 of 63                   6/8/2005
          < Screen shot removed to protect the privacy of the owner >




          The Ballot Entry Form does not allow the owner to vote for a candidate more than once.

          The following screenshot shows the Ballot Entry Form for an account that is not in good
          standing.




Confidential                            Page 47 of 63                                         6/8/2005
          < Screen shot removed to protect the privacy of the owner >




          The only option given to the user is to indicate whether or not a ballot was received. The other
          fields are not enabled if the user clicks the Yes checkbox.

          The Summary Report
          The summary report is available anytime during the ballot entry process. It is a report that lists
          the vote count and compiles some elementary statistics. Since the names of the write-in
          candidates are continually changing, the fields of the Summary Report itself must be generated
          dynamically each time the report is viewed. The following fields of the report are static and
          can be generated from template, if desired:
               Total Number of Property Owners
               Property owners in good standing
               Total Ballots Received
                  o Valid Ballots
                  o Invalid Ballots
                      Not in good standing
                      Other reasons


Confidential                             Page 48 of 63                                             6/8/2005
          The following screenshot shows the required statistics.




          The Detail Report
          The detail report describes the votes cast by each Owner Account. The following screenshot
          shows the required fields.




Confidential                            Page 49 of 63                                         6/8/2005
          < Screen shot removed to protect the privacy of the owners >




   Reports 1 2 3 4
      This section of the menu allows the user to choose a report to view on the screen. When a report
      is displayed, the user will have the option of printing it or exporting it to a Word document or an
      Excel spreadsheet. If any of the reports allow the user to filter the data reported or select sorting
      options, then a screen requesting that info will appear before the report is generated.

       1. Membership List 4
          Filter options: None

          Sort options: By default, this report will display in alphabetical order by last name of the
          owner designated as the contact for the account. The user may select any of the sort option
          checkboxes to have the report display in a different order. The choices are:
              Name (last name, first name)
              Tract Number
              Account Status

          This report will list a summary of all tracts on the ranch. Listed for each account will be:
              Tract Number
              Tract Acreage
              Name (last name, first name) of contact for the account
              Address of contact
              Account status (In Good Standing or Not In Good Standing)



Confidential                              Page 50 of 63                                            6/8/2005
       2. Delinquent Owner List
          Filter options: The user must specify the date on which the delinquent status of an owner
          account is determined. The default value will be the current date.

          Sort options: By default the list will be sorted by Account number. The user may select any of
          the sort option checkboxes to have the report display in a different order. The choices are:
               Name (last name, first name)
               Past due amount

          The report will display a list of all owner accounts that are delinquent as of the date specified.
          Listed for each account will be:
               Account Number
               Owners of the account with the contact indicated
               Address of the contact
               Past due amount
               Any late fees that have been assessed
               Account balance
          The balance of all delinquent accounts will be totaled at the end of the report.

       3. Exempt Owners
          Filter options: None

          Sort options: By default the list will be sorted by Account number. The user may select any of
          the sort option checkboxes to have the report display in a different order. The choices are:
               Name (last name, first name)

          The report will display a list of all owner accounts that have exempt property as part of their
          account. Listed for each account will be:
              Account Number
              Owners of the account with the contact indicated
              Address of the contact
              All exempt tract numbers

       4. Volunteer Owners
          Filter options: None

          Sort options: By default the list will be sorted by Account number. No other sort options are
          available.

          The report will display a list of all owner accounts that have exempt property as part of their
          account but have elected to pay a voluntary fee anyway. Listed for each account will be:
              Account Number
              Owners of the account with the contact indicated
              Address of the contact
              All exempt tract numbers

Confidential                              Page 51 of 63                                             6/8/2005
                  Amount of the voluntary payment

       5. Contract for Deed Owners
          Filter options: None

          Sort options: By default the list will be sorted by Account number. No other sort options are
          available.

          The report will display a list of accounts that own tracts where the ownership of the property
          has changed hands but the deed has not yet been transferred. Listed for each account will be:
               Tract Numbers awaiting a deed
               Owners of the account with the contact indicated
               Deed holder name (i.e. contact from previous owner account)

       6. Foreclosure Status
          Filter options: None

          Sort options: By default the list will be sorted by foreclosure status. No other sort options are
          available.

          The report will display a list of owners who have tracts that are in some stage of foreclosure.
          They will be grouped so that those who have been sent demand letters are first, those that have
          had liens filed are second, and those that have been foreclosed on are third. Listed for each
          account will be:
              AccountID
              Owners of the account with the contact indicated
              Tract Numbers involved in the foreclosure process
              Date demand letter was sent
              Date lien was filed (if it has been)
              Date foreclosure finalized (if it has been)
              Deed holder (POATRI or Brewster County)
              Account balance

       7. Account Statement
          Filter options: Selecting this report will present the user with the same account search form as
          described in the Inquire by Owner section above. After clicking the Find button and selecting
          an account, the user will click the View Bill button to see a bill addressed to the owner
          designated as contact.

          Sort options: By default the list will be sorted by foreclosure status. No other sort options are
          available.

          The current account balance will be billed with all account activity to date for the year
          included. This report will likely be used in the case of a statement being sent to a new owner
          after a transfer of property, or to an existing owner who wants an up-to-date statement for their
          account.

Confidential                             Page 52 of 63                                             6/8/2005
       8. Receivables Aging Report
           Filter options: The user must first specify the date on which the aging report is determined.
           The default value will be the current date.

               Sort options: By default the list will be sorted by Account number. The user may select any
               of the sort option checkboxes to have the report display in a different order. The choices are:
                  Name (last name, first name)

          The report will display a list of all owner accounts that have a balance due as of the date
          specified. Listed for each account will be:
              Account Number
              Owner of the account designated as contact
              Amount due that is less than 30 days old
              Amount due that is > 30 days but less than 60 days old
              Amount due that is > 60 days but less than 120 days old
              Amount due that > 120 days old
              Amount involved in foreclosure proceedings (with foreclosure status Demand, Lien,
                  Foreclosed)
              Amount of write-off adjustments (should these be aged or do they not get written off
                  until they are way beyond the 120 day mark?)
              Total Due
          The balance of each of the aging columns will be totaled at the end of the report.

       9. Bank Deposits Report
          Filter options: The user must first specify a date, start time, and end time for a list of payments
          that can be turned in to the bank.

          Sort options: The payments will be listed in chronological order. No other sorting options are
          available.

          The program will then look in the ledger table for any payment records that have a payment
          date matching that specified by the user and a payment time falling between the start and end
          times specified. Only those payments whose payment type is check, or money order will be
          included. Cash and credit cards are handled separately. The report will display the following
          information:
               Account Number
               Owner name (contact)
               Method of payment (check, money order)
               Reference number (check number, money order number)
               Amount
          The amount column will be totaled at the bottom.




Confidential                                Page 53 of 63                                            6/8/2005
TRPOA Data Transfer

Introduction
This section describes the TRPOA DB2 tables and, where applicable, the fields of the new tables to which
each field of the TRPOA tables map.

Owners
The Owners table contains information about people who own property, such as name and address. It also
contains financial information that applies to the current financial year.

Field Name                                Disposition of Data
Owner          This field corresponds to the notion of an “Account” in POMS. It is
               defunct. However, it will be saved in order to facilitate reconstruction
               of corrupt data.
LastName       Maps to the lastName field of the POMS Owners table
FirstName      Maps to the firstName field of the POMS Owners table
Co-owner       This field contains the names of the people whose names, together
               with the primary owner, appear on the deed(s) that are associated with
               this account. The co-owners constitute an account in POMS. This field
               must be parsed to obtain the first and last names of the co-owners,
               which then map to the firstName and lastName fields, respectively, of
               the POMS Owners table. The parsing of this field will require
               considerable ad hoc programming.
Addr1          Maps to the address1 field of the POMS Addresses table
Addr2          Maps to the address2 field of the POMS Addresses table
City           Maps to the city field of the POMS Addresses table
State          Maps to the state field of the POMS Addresses table
Zip            Maps to the zip field of the POMS Addresses table
NPO            Non Property Owner. This field has not been well-maintained in
               TRPOA. It is defunct.
Bankrupt       This field is defunct.
Badaddr        Bad address. This field is defunct.
InitDate       Maps to the dateCreated field of the POMS Accounts table and the
               dateCreated field of the POMS Owners table.
LastChg        Maps to the dateModified field of the POMS Owners table.
Operator       The person who made the last change to the TRPOA Owner record.
               Defunct.
Fees           The current assessment. Defunct. Replaced by a ledger entry.
Donations      Defunct.
PastDue        The amount past due. Defunct. Calculated from the ledger table.
CFD            The amount of voluntary payment made by the owner of exempt
               property. Defunct.
LateChg        The current late charges. Defunct. Calculated from the ledger table.
Payments       The current payments received. Defunct. Calculated from the ledger
               table.
Adjust         The current adjustments. Defunct. Calculated from the ledger table.

Confidential                             Page 54 of 63                                         6/8/2005
LastPay         The date of the most recent payment. Defunct.
LegFlg          Legal Flag. Defunct.
LegRef          Defunct.
Collagent       The collection agency. Defunct.
Deceased        Defunct
AllExempt       Defunct
Volunteer       TBD.
Newsletter      Defunct

                                                   Ledger
The Ledger table contains information about financial transactions associated with each owner. Due to a
lack of referential integrity in TRPOA, there are numerous records in the Ledger table which refer to
nonexistent owners in the Owners table.

Field Name                                   Disposition of Data
Owner           Foriegn Key to the Owner field in the Owners table. This will be replaced
                by a foreign key to the AccountID in the POMS Accounts table. There are
                many values of this field for which there is no corresponding Owner field
                in the Owners table. Reconstruction of those Owner records will require
                user intervention and is not possible through programming.
TranDate        Maps to the transactionDate field of the POMS Ledger table
TranType        The POMS RefTransactionType Table will contain the specific
                transactions used in this table. This value of this field will specify the
                transactionTypeID in the POMS Ledger table. The values of this field also
                contain notes which will have to be parsed and mapped to the notes field of
                the POMS Ledger table.
TranAmt         This field maps to the amount field of the POMS Ledger table.

Tracts
The Tracts table contains the information about Tracts, such as size, whether or not it‟s exempt, the
current owner and the previous owner.

Field Name                                   Disposition of Data
TractNum        Maps to the tractNumber field of the POMS Tracts table
CurrOwner       This field is a foreign key to the TRPOA Owners table. It will be used to
                find an accountID of the POMS Accounts table. This accountID will be
                used to create or find a record in the POMS AccountTracts table whose
                accountID will correspond to the accountID of the POMS Account table.
                The isCurrent field of the POMS AccountTracts table will be set to True to
                signify that the specified account is the current owner of the tract. Due to
                TRPOA data corruption and lack of referential integrity, there are values of
                the CurrOwner field that have no corresponding Owner in the Owners
                table.
DeedOwner       The new system will have a date in the DateOwnershipTransferred field of
                the AccountTracts table but the DateDeedTransferred will be left blank to
                indicate that the previous owner still holds the deed for the specified tract.

Confidential                              Page 55 of 63                                            6/8/2005
PrevOwner      This field is a foreign key to the TRPOA Owners table. It will be used to
               find an accountID of the POMS Accounts table. This accountID will be
               used to create or find a record in the POMS AccountTracts table whose
               accountID will correspond to the accountID of the POMS Account table.
               The isCurrent field of the POMS AccountTracts table will be set to False to
               signify that the specified account is not the current owner of the tract. Due
               to TRPOA data corruption and lack of referential integrity, there are values
               of the CurrOwner field that have no corresponding Owner in the Owners
               table.
Acres          Maps to the acreage field of the POMS Tracts table
Exemption      Possible values of this field are:
                  N – Not exempt
                  V – Exempt, but the tract is owned by a person who pays voluntarily
                  Y – Exempt
                  X – Defunct. Means the same thing as N
               Maps to the isExempt field of the POMS Tracts table
Ownership      Possible values of this field are:
                  B – No meaning in the current system
                  C – No meaning in the current system
                  D – Deeded
                  I – Contract for Deed
               The values „B‟ and „C‟ will map to either a D or an I. Analysis is being
               performed to determine the mapping.

           The field in the POMS table(s) to which this field maps is still TBD.
Annual_CFD TBD
InitDate   Maps to the dateCreated field of the POMS Tracts table
LastChg    Maps to the dateModified field of the POMS Tracts table
Operator   Maps to the userID field of the POMS Tracts table
LastSold   Defunct

TractHis
The TractHis table contains the information about deed and ownership transfers of all tracts. It names the
buyer, seller, date of the transaction and the type of transaction, either a contract for deed or a deed
transfer.

The information in this table will be used primarily to create records in the POMS AccountTracts table.

Field Name                                   Disposition of Data
TractNum       This field is used to connect the tractID of the POMS AccountTracts table
               with tractID of the POMS Tracts table.
Buyer          This field is used to find the accountID of the buyer of the tract. This
               accountID maps to the accountID of the POMS AccountTracts table
Seller         This field is used to find the accountID of the seller of the tract. This
               accountID maps to the previousAccountID of the POMS AccountTracts
               table

Confidential                              Page 56 of 63                                           6/8/2005
TranDate        If the TranType value is Contract for Deed then the TranDate field maps to
                the dateOwnershipTransferred field of the POMS AccountTracts table,
                otherwise it maps to the dateDeedTransferred field of the POMS
                AccountTracts table.
TranType        Possible values of this field are:
                   Contract for Deed
                    Deeded


Any data which cannot be mapped to the new system tables will be stored in the new system in tables
whose schema matches the current TRPOA tables but are renamed with the prefix “Lost”. This data will
have to be dealt with manually at some future time and the disposition of it is not included in this design
spec.

The TRPOA system will be archived and removed from the office LAN once the new system is in place.
Reports will be generated from the closing state of the old system and compared to reports generated from
the newly imported data in the new system.

QA
There should be two phases of testing. The first, will consist of entering a dozen or so test accounts with
single and multiple owners, individuals and organizations. A few tracts will be entered and assigned to
owner accounts. These records will be the basis for testing all the other functions. Most of this phase of
testing will be done by the programmers as they implement the design features discussed in this
document.

As development nears completion, the second phase of testing will include the testing of the conversion of
existing TRPOA data. This data will be pulled into the new system and, leaving problem data aside for
the time being, new functions will be tested using this “real” test data. Much of this testing will be done
by POATRI staff who are familiar with the accounts and can compare the results with those of the
existing system.

Testing will include system tests as well as functional testing, i.e. several users accessing data
simultaneously from different security level versions of the application.

Deployment
The new application will consist of multiple Access files. The back-end data file will live on the server of
the local area network located in the POATRI office. The other Access files are the user front-end
applications and there will be one for each security level. These will be copied to the appropriate user‟s
desktop machine which is also connected to the LAN.

The administrator will have to add the individual user to the appropriate groups so that they may have a
login to the application.

Training and Documentation
Training will include documents, demonstrations, and hands on practice. There will be several documents
provided with the new system.

Confidential                               Page 57 of 63                                             6/8/2005
      User guides that describe all of the forms and reports and their functions for each security level of
       user.
      Technical documentation on installation of the system including setting up users and groups for
       security access.
      Technical documentation of the structure of the system -- this design document modified as
       needed to include any changes made during implementation.
      Maintenance documentation which describes how to backup and restore the system in event of
       software or hardware failure. Since Access is a file based system, this is easily accomplished by
       copying the backend database file to tape or cd on a daily basis. The program applications will not
       be changing and therefore should not need backing up on a regular basis – initially and after any
       changes are made only.

Much of the training will come during the second testing phase of implementation when POATRI staff
are involved.

Deliverables
    Access database files
    Documentation
    Ballot database template


Time and Cost
The manpower estimate for completing this project as defined in this document is 1786 hours or 44.65
weeks. Using a $40/hour billing rate that comes to $71,440. In arriving at this estimate, time for the
unexpected was built in so we will use this figure as a cap and only bill for hours actually worked up to
this amount. That way POATRI will not have to pay for any overestimates yet will be protected from
overruns.

Milestones
To spread the payments out over the life of the project and across fiscal years, invoices will be sent as
these various milestones are completed (not necessarily in this order):
       Acceptance of work order:                                                            $5,000
       User security, main navigation, and Account Inquiries section completed:             $10,000
       Account Activities section completed:                                                $15,000
       Yearly Activities section completed:                                                 $10,000
       Reports and Database Administration sections completed:                              $8,500
       TRPOA data converted (no disposition of lost data included here):                    $15,000
       Project Completion (testing and installation of all security levels completed):      $7,940

Note: The amounts here reflect the estimated hours, but the invoices will be for actual hours worked not
to exceed the total amount.




Confidential                               Page 58 of 63                                            6/8/2005
Appendix A – Data Definitions
                             Table: Accounts
FieldName                     Type      Size Properties
accountID                     Long Integer    4   Primary Key
isActive                      Yes/No          1
sendReminder                  Yes/No          1
useNonExemptRate              Yes/No          1
volunteerPayment              Currency       8
dateLastReminderSent          Date/Time      8
notes                         Memo            -
TRPOAID                       Text           15   from the TRPOA database
userName                      Text           20   last user to modify record
dateCreated                   Date/Time      8
dateModified                  Date/Time      8



                          Table: AccountTracts
FieldName                     Type      Size Properties
accountTractID                Long Integer   4    Primary Key
tractID                       Long Integer   4    link to Tracts table
accountID                     Long Integer   4    link to Accounts table
dateOwnershipTransfered       Date/Time      8
dateDeedTransfered            Date/Time      8
isCurrent                     Yes/No         1
previousAccountID             Long Integer   4    link to Accounts table
notes                         Memo            -
userName                      Text           20   last user to modify record
dateCreated                   Date/Time      8
dateModified                  Date/Time      8



                          Table: AccountOwners
FieldName                     Type      Size Properties
accountOwnerID                Long Integer   4    Primary Key
ownerID                       Long Integer   4    link to Owners table
accountID                     Long Integer   4    link to Accounts table
isContact                     Yes/No         1    Primary contact for account
notes                         Memo            -
userName                      Text           20   last user to modify record
dateCreated                   Date/Time      8
dateModified                  Date/Time      8



                            Table: Addresses
FieldName                     Type      Size Properties
addressID                     Long Integer    4 Primary Key
ownerID                       Long Integer    4 link to Owners table
address1                      Text           255
address2                      Text           255
inCareOf                      Text           255
city                          Text           100
state                         Text           50
zip                           Text           20
country                       Text           100
isCurrent                     Yes/No          1
notes                         Memo            -
userName                      Text           20 last user to modify record
dateCreated                   Date/Time       8
dateModified                  Date/Time       8

Confidential                                 Page 59 of 63                      6/8/2005
                       Table: Ledger
FieldName              Type       Size Properties
ledgerID               Long Integer   4    Primary Key
accountID              Long Integer   4    link to Accounts table
transactionTypeID      Long Integer   4    link to REFTransactionType table
amount                 Currency       8
transactionDate        Date/Time      8
paymentMethodID        Long Integer   4    link to REFPaymentMethods table
referenceNumber        Text           20   check or money order number
batchNumber            Integer        2    To group payments for the bank
notes                  Memo            -
userName               Text           20   last user to modify record
dateCreated            Date/Time      8
dateModified           Date/Time      8



                    Table: Foreclosures
FieldName              Type       Size Properties
foreclosureID          Long Integer   4    Primary Key
accountTractID         Long Integer   4    link to AccountTracts table
dateLetterSent         Date/Time      8
dateLienFiled          Date/Time      8
LienCertication        Text           20
dateOfForeclosure      Date/Time      8
dateMadeGood           Date/Time      8
deedHolder             Text           20   POATRI or Brewster County
notes                  Memo            -
userName               Text           20   last user to modify record
dateCreated            Date/Time      8
dateModified           Date/Time      8



                       Table: Owners
FieldName               Type     Size Properties
ownerID                Long Integer   4    Primary Key
firstName              Text           40
lastName               Text           40
middleName             Text           30
suffix                 Text           10
organization           Text           50
homePhone              Text           50
businessPhone          Text           50
cellPhone              Text           50
fax                    Text           50
email1                 Text           70
email2                 Text           70
notes                  Memo            -
userName               Text           20   last user to modify record
dateCreated            Date/Time      8
dateModified           Date/Time      8



                    Table: PaymentPlans
FieldName              Type      Size Properties
planID                 Long Integer   4    Primary Key
accountID              Long Integer   4
totalDue               Currency       8

Confidential                          Page 60 of 63                           6/8/2005
paymentAmount                   Currency        8
frequencyID                     Long Integer    4 link to REFFrequency table
otherFrequency                  Text           20
numberPayments                  Long Integer    4
dateStarted                     Date/Time       8
dateAgreementSent               Date/Time       8
dateLetterSent                  Date/Time       8
notes                           Memo            -
altName                         Text           100
altAddress1                     Text           255
altAddress2                     Text           255
altCity                         Text           100
altState                        Text           50
altZip                          Text           20
altCountry                      Text           100
altPhone                        Text           50
altEmail                        Text           70
userName                        Text           20 last user to modify record
dateCreated                     Date/Time       8
dateModified                    Date/Time       8



                                Table: Tracts
FieldName                       Type       Size Properties
tractID                         Long Integer    4 Primary Key
tractNumber                     Text           15
sectionID                       Long Integer    4 link to REFSections table
legalDescription                Text           255
acerage                         Double          8
isExempt                        Yes/No          1
parentID                        Long Integer    4
isSubdivided                    Yes/No          1
xCoord                          Double          8
yCoord                          Double          8
notes                           Memo            -
userName                        Text           20 last user to modify record
dateCreated                     Date/Time       8
dateModified                    Date/Time       8



                        Table: REFAnnualAssessments
FieldName                        Type    Size Properties
assessmentID                    Long Integer   4    Primary Key
assessmentYear                  Integer        2
flatFee                         Currency       8
perAcreRate                     Currency       8
dateAssessed                    Date/Time      8
90DayLateFee                    Double         8
365DayLateFee                   Double         8
Date90DayFeeAssessed            Date/Time      8
Date365DayFeeAssessed           Date/Time      8
userName                        Text           20   last user to modify record
dateCreated                     Date/Time      8
dateModified                    Date/Time      8



                            Table: REFFrequency
FieldName                       Type      Size Properties
frequencyID                     Long Integer    4   Primary Key


Confidential                                   Page 61 of 63                     6/8/2005
description                Text           50
sortOrder                  Integer        2



                    Table: REFPaymentMethods
FieldName                   Type    Size Properties
paymentMethodID            Long Integer    4   Primary Key
description                Text           50
sortOrder                  Integer        2



                        Table: REFSection
FieldName                 Type      Size Properties
sectionID                  Long Integer   4    Primary Key
description                Text           50
sortOrder                  Integer        2



                    Table: REFTransactionType
FieldName                  Type     Size Properties
transactionTypeID          Long Integer   4    Primary Key
description                Text           50
sortOrder                  Integer        2
showOnAdjustments          Yes/No         1    Show on adjustment screen



                       Table: Amendments
FieldName                 Type     Size Properties
amendmentID                Long Integer   4    Primary Key
amendment                  Text           50



                        Table: Candidates
FieldName                 Type       Size Properties
candidateID                Long Integer   4    Primary Key
candidate                  Text           50



                        Table: BallotTracts
FieldName                 Type        Size Properties
accountID                  Long Integer   4
tractID                    Long Integer   4
tractNumber                Text           15
acres                      Double         8



                       Table: BallotOwners
FieldName                 Type       Size Properties
accountID                  Long Integer    4
ownerID                    Long Integer    4
IsContact                  Yes/No          1 Primary contact for account
firstname                  Text           30
lastname                   Text           30
organization               Text           50
address1                   Text           50
address2                   Text           50
city                       Text           100

Confidential                              Page 62 of 63                    6/8/2005
state                               Text           50
zip                                 Text           20
country                             Text           100



                                    Table: Ballots
FieldName                           Type       Size Properties
accountID                           Long Integer   4
good-standing                       Yes/No         1
valid                               Yes/No         1
BallotReceived                      Yes/No         1
<plus 1 field for each candidate>   Text           50
<plus 1 field for each amendment>   Text           50




Confidential                                       Page 63 of 63   6/8/2005

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:522
posted:7/5/2011
language:English
pages:63