REFERENCE Technical Documentation of AIRS system

Document Sample
REFERENCE Technical Documentation of AIRS system Powered By Docstoc

REFERENCE: Technical Documentation of AIRS system.

Naming Conventions:

AIRS: AIDS Institute Reporting System
URS: Uniform Reporting System – the original name of AIRS.
AI: AIDS Institute of NYS DOH.
Client: A person receiving services.
Providers: An agency or facility providing services to clients. Services can be provided
one time or on an ongoing basis. Providers may range from community based
organizations to certified medical care facilities.
Services: These are client interventions such as counseling, housing, case management,
medical care, tests, etc. that are defined by the AIDS Institute.
Histories: Any information maintained as an ongoing record over time.
Assessments: Detailed information on client status collected over time. Maintained as
FPW: FoxPro for Windows
VFP: Visual FoxPro 9

Platform and development background
FoxPro for Windows was chosen as the development platform for the original URS
system because it provided a Windows-compatible GUI product with acceptable
database performance a low cost / no cost solution to providers. Visual FoxPro was
selected as the (re)development tool for AIRS because of its ability to utilize legacy code
with little modification, reducing development timelines and cost.

System Overview
AIRS is developed in two components: 1) the main data collection application; and 2)
utilities. The data collection application is used for administrative tasks, collecting data
on individuals and groups, preparing reports and data extracts, tracking runtime errors,
etc. The utilities application is used to perform database maintenance operations,
application version upgrades, failsafe login.

Wherever possible object modeling and OOP techniques was used. However many,
many programs were simply ported into AIRS from FPW with minimal changes to
accommodate modern program techniques and SQL. Programs where (URS)
performance was an issue, were enhanced or rewritten to increase performance by
using optimize SQL or old style SCAN; whichever is the better performer.

A core feature of AIRS is keeping confidential client information from the view of
unauthorized users. This is accomplished by two methods: 1. An internal security
module which defines staff (user) access to people and functionality. 2. Data Encryption;
encrypting any identifiers that could be used to indentify an individual person.
Encryption takes place within the table; the table itself is not encrypted. Encryption is
optional at the installation and can be enabled or disabled by the AIRS system
Application Security
It is always suggested that providers use network best practices to limit the accessibility
of the AIRS folders as basic confidentiality protocol. General recommendations are that
network administrators define 2 work groups; AIRS Users and AIRS Administrators and
assign network users accordingly. Security within the AIRS application is controlled by
means of a separate security module, functionality of which is described in the User
Manual in the appendices. This module controls user access to clients and application
functionality. A user flagged as a system administrator has access to all clients, all
system functions and the utilities application.

Development management
The VFP Project Manager was used to create and manage the (application) project.
The application is versioned with each build (test or release) incrementing the revision
numbers are set in agreement with the AI. Documentation is prepared and reviewed by
the developer and the AI for each version release. The AIRS Version release is
commonly referred to as a ‘Version Upgrade’. Version Upgrades are performed by the
agency with occasional assistance from the AI or product developer.

Application Testing Process Description
New (AIRS) development items include a test plan as part of the specifications. Test
plans are usually prepared by the developer as part of the design specification.
Additionally, the AIRS application has an extensive internal error trapping and logging
module to trap runtime errors. Technical support to providers usually cannot proceed
without the specific error log entry, which provides a memory and status dump of the
AIRS system at the time of the error. Runtime errors logs are used as documentation to
identify specific problems for program fixes to be included in the upcoming release.
Test Plan documentation is updated as the development cycle proceeds and as new
items become part of the module. Typically, a regression testing model is used to test
each release.

Product testing and customer support are tightly integrated with many product
enhancements coming directly from providers. Customer support associates and
application developers typically have weekly meetings to discuss technical aspects of
customer support and separate program errors from data quality issues. Development
tracking tickets are created if data issues are caused by programming errors.

Alpha testing is completed as a phase 1 test by programmers and other technical staff.
This testing phase addresses all reported runtime errors, either in-house or from
providers, documentation, or module test plan to verify that new development items are
in accordance with the specifications, syntax checking, instructions and information
forms, cosmetics and graphics, alignment, spelling, reports, etc.

Beta testing is performed by the product developer and the AI once Alpha testing is
complete. A prerelease of the Version Upgrade is provided to the AI for their testing. If
errors are found the AI returns the appropriate error message to the developer for
evaluation or repair. During this testing phase a version upgrade is performed on one or
more provider systems. The product developer must have a confidentiality clause as
part of the contract with the AI which allow them to obtain provider data for the purposes
of troubleshooting and testing. Provider data can be retained for a maximum of 10 days
and must be stored in encrypted (zip) archive files. Removal of provider data form the
offices is prohibited. Provider data must be destroyed according to AI protocol once the
issues are resolved and testing phase is complete.

The AIRS product does not support automated testing. Testing is performed solely by
staff. Several tools that are proprietary to the current product developer have been
developed to assist in the testing process.

The Object Model
Many topics were taken under consideration during the redevelopment of URS. Data
quality, resource overhead, new features and budget were all considerations when
development began. A CASE tool was not used to develop or maintain the database.
However a tool was used to create an ERD for the purposes of this document. Most
FPW code was moved to methods in various class libraries. Class libraries were
developed based on functionality and visibility. The data collection application is broken
into modules. Visibility is limited to the activity that can be performed within that module.
U.I. access to the individual module is controlled by a single table – rpt_tree_list. This
table is the heart of module functionality. It serves as the ‘menu’ which allows for
navigation within the module, creates the necessary objects, controls user security
(access to pages, add/edit/delete). There is a single class (form) MODULE_FORM
which uses the rpt_tree_list table. The process is approximately 90% automated.
MODULE_FORM in conjunction with rpt_tree_list controls saving, deleting, reverting
information, and enabling the add/edit/delete/print buttons.

The following class libraries have functionality outside of the module scheme.
    Urs: Holds objects whose major functionality is to service AIRS specifically
    Standard: Holds objects whose major functional is to the system
    zip_utilities: Used by the DynaZIP application to achieve file compression.
    thermo: Displays progress bar.
    Frxpreview: customized FoxPro report preview utility.
    Frxcontrols: FoxPro report controls
    _frxcursor: FoxPro library to dynamic convert a report into a cursor.
    Listener: FoxProx Report listener utility

AIRS also uses the following automation servers:
    Richtx32.ocx – Displays RTF files in the information panel.
    ePDFDirector.dll – Saves FoxPro reports as PDF files
    ereports.dll – Used by ePDFDirector.dll
    urn32.dll – Creates a standard URN for each client; used on client intake and
    ROBOEX32.DLL – Assists in displaying AIRS Help information.
Additionally, calls are made to the win32api for system colors and report previews.

Most global variables were retained as-is from the URS system. These were retained to
accommodate the FPW URS programs that were not rewritten. These are declared in
the main program and global in scope. New variables are stored as properties in the
‘app’ class in the urs class library. Variable naming conventions follow the [scope] +
[data type] + [name] format i.e.: glFromPEMS.
Module         Menu Selection / Name                          Class Library       Class
AGENCY         Agency Options:
AGENCY         Agency Information                             agency              agency_page
AGENCY         Program Information                            agency              program_page
AGENCY         Site Information                               agency              site_page
AGENCY         Staff Information                              agency              staff_page
AGENCY         Referral Library                               agency              referral_page
AGENCY         Group Setups                                   agency              group_page
AGENCY         Counseling And Testing Setup                   agency              cts_setup
AGENCY         RDR:
AGENCY         RDR Information Form...                        cadr                cadrinfo
AGENCY         RDR Service Information Form...                cadr                cadrserv
AGENCY         MAI:
AGENCY         Agency MAI Information Form...                 mai                 mai_info
AGENCY         Contract & Grant:
AGENCY         Contract/Grant Information                     grand_tracking      contrinf
AGENCY         Service Targets                                grand_tracking      serv_targets
AGENCY         Contract Type & Service Type Link              grand_tracking      conser
AGENCY         Define Services to Contract/Service Types      grand_tracking      cserdef
AGENCY         Contract/Program Services Definition           grand_tracking      consd

BILLING        Setup:
BILLING        Payers Setup                                   billing             payerpage
BILLING        Programs and Sites Setup                       billing             programs_sites_setup
BILLING        Setup Rates                                    billing             setup_rates
BILLING        Setup Encounters and Services                  billing             setup_enc_serv
BILLING        Medicaid Eligibility Check (270/271)
BILLING        Create Medicaid Inquiry File (270)             billing             create_270
BILLING        Import Medicaid Response File (271)            billing             import_271
BILLING        Medicaid Status Report                         reports             report_271
BILLING        Processing
BILLING        Process Claims                                 billing             process_claims
BILLING        Disk Log                                       billing             disk_log
BILLING        Claim Maintenance                              billing             claim_maintenance
BILLING        Remittance
BILLING        Import Electronic Remittance (835-)            billing             import_835
BILLING        Import Supplemental Remittance (835S)          billing             import_835s
BILLING        Remittance Receipts                            billing             cash_log
BILLING        Mark Claim Results                             billing             remittance
Module         Menu Selection / Name                          Class Library       Class
BILLING       Reports
BILLING       Claims Detail Report                           reports             claim_report
BILLING       Claim Aging Report                             reports             claim_age
BILLING       Encounters & Services Billing Report           reports             enc_serv_bill
BILLING       ADHC Attendance Billing Report                 reports             adhc_bill
BILLING       COBRA Services Billing Report                  reports             cobra_bill
BILLING       Payers/Providers Setup Report                  reports             prov_rp_report
BILLING       Rates Setup Report                             reports             rate_rp_report

CLIENT_MODULE Intake Information:
CLIENT_MODULE Agency Intake                                   client_intake       intake_page
CLIENT_MODULE Case Management Intake                          client_intake       casemgnt_intake
CLIENT_MODULE Enrollments / Assignments:
CLIENT_MODULE Status Changes...                               client              status_page
CLIENT_MODULE Program Enrollments...                          client              program_enrollment
CLIENT_MODULE Worker Assignments...                           client              worker_assignment
CLIENT_MODULE Group Enrollments...                            client              group_enrollment
CLIENT_MODULE Site Assignments...                             client              site_assignment
CLIENT_MODULE Historical Information:
CLIENT_MODULE Collateral Information...
CLIENT_MODULE Diagnosis Information...                        client_history      diagnosis_page
CLIENT_MODULE Financial Information...                        client_history      financial_page
CLIENT_MODULE HIV Status Information...                       client_history      HIV_status_page
CLIENT_MODULE HIV/AIDS Risk History...                        client_history      HIV_risk_history
CLIENT_MODULE Insurance Information...                        client_history      insurance_page
CLIENT_MODULE Laboratory & Psychological Tests...             client_history      labtests_page
CLIENT_MODULE Medication History...                           client_history      medications_page
CLIENT_MODULE Placement / Visit History...                    client_history      placement_page
CLIENT_MODULE Pregnancy Information...                        client_history      pregnancy_page
CLIENT_MODULE Primary Care Physician Information...           client_history      pcp_page
CLIENT_MODULE Referral Tracking...                            services            referral_tracking
CLIENT_MODULE Substance Use History...                        client_history      substance_use_page
CLIENT_MODULE TB Status...                                    client_history      TB_status_page
CLIENT_MODULE Assessments:
CLIENT_MODULE HIV Prevention Outcome Monitoring
CLIENT_MODULE Treatment Adherence
Module         Menu Selection / Name                          Class Library       Class
CLIENT_MODULE Services Form...                                services            service_page3
CLIENT_MODULE    Legal Services...                      services           legal_services
CLIENT_MODULE    Counseling Testing and Referrals...

CTR              C.T.R.
CTR              Pre-Test (Part A/Set Header )          ctr                form_a
CTR              Local Variables                        ctr                form_c
CTR              Testing (Part B 1-3)                   ctr                form_b
CTR              Post-Test (Part D)                     ctr                form_d
CTR              Medication History...                  client_history     medications_page

EXTRACTS         Extracts
EXTRACTS         AIDS Institute Extract                 extracts           ai_extract
EXTRACTS         Connecticut Extract                    extracts           ai_extract
EXTRACTS         MHRA Extract                           extracts           mhra_extract
EXTRACTS         Ryan White Data Report (RDR)/Extract   extracts           cadr_extract
EXTRACTS         HIVQUAL Extract                        extracts           hivqual_extract
EXTRACTS         Electronic MPR                         extracts           empr_extract

NGRP_ACTIVITY2   Group Activities:
NGRP_ACTIVITY2   Batch-Group Enrollments...             group_activities   batch_enrollments
NGRP_ACTIVITY2   Group Activities...                    group_activities   group_activity_page1
NGRP_ACTIVITY2   Fast Track Data Entry:
NGRP_ACTIVITY2   Fast Track Form...                     fast_track         fast_track1
NGRP_ACTIVITY2   Session Encounters...                  outreach           outreach_page
NGRP_ACTIVITY2   Syringe Exchange:
NGRP_ACTIVITY2   Syringe Exchange Information           syringe            syringe_page

OUTREACH         Outreach:
OUTREACH         Outreach Information                   outreach           outreach_page
System Installation
Each Provider installation is provided a unique 2 character ID which is provided by the
AI. System installation is provided by an InstallShield script which calls the Windows
installer. New Installations are required to provide an activation confirmation, which
guarantees that the system is correct for the provider.

AIRS Database
AIRS is not a client-server managed system. Requests are formulated from the client
workstation, results sets are delivered to the client. Currently all stored procedures are
stored in a procedure library, compiled into the exe, not the DBC. There are no FK
constraints, cascade deleted, or triggers contained in the database. When writing to the
database ‘Begin transaction’ is not used. Thus, the application must monitor the
database health and data integrity. The primary reason for this is the nature of the
legacy data. Wherever possible Unique Keys were added to each table. Incremental
(database generate) primary keys are rarely used, however, they do exist.

The VFP database is OLE compliant and can be accessed from other applications.
However, due to the confidential nature of client information many columns will display in
their encrypted form. There are several views which call the decryption method to
properly display client identifiers. These views will also not display properly or at all.
Access to the production database from third party applications such as Access is not
advisable and not supported. If connections to the database are needed, a copy of the
database and all its files must be made in a separate disk location.

Unique record Keys are mostly generated internally from a table. The getnextid()
method is called to obtain a new unique key. Keys are specific to the table and
generally contain the 2 character provider id + 3 to 8 characters. These keys are
character in nature, not integers, which can have a negative effect on optimization.

The external tool StoneField Database Toolkit (SDT) – developed by StoneField is used
to maintain the health of the database container and repair damaged/corrupted table
headers. The Table Repair option in the AIRS Utilities uses the Metadata prepared by
SDT to determine the correct table header for each table. During each upgrade cycle a
new metadata folder is delivered to the provider.

Workstation Setup
Workstation setup is achieved via an InstallShield script. Runtime drivers and other
servers are installed on the workstation, desktop short cuts are setup.

AIDA System
Periodically Providers are required to send AIRS data to the AI. This data is imported
into a MS SQL Server system known as AIDA. All AIRS data extracts are created from
the Extracts module, results are usually in XML or comma delimited text. After each
AIRS Version Upgrade the application developer prepares a script to add / manage
AIDA SQL Server objects. There are some AIRS tables for which unique keys are
generated solely for use by AIDA. It can’t be assumed that all the AIRS primary keys
translate directly to AIDA.

Access to the application from outside the LAN can only be achieved via a Remote
Control solution such as Terminal Server and CITRIX. WAN access to the data is
possible but performance and data quality issues should preclude this option as a viable
method. Network infrastructure is important since the application generates a lot of
network traffic. A draft document of suggested Remote Control settings is included here.

AIRS Utilities Application
1) Main features:
   a) Protected by AIRS internal security.
   b) Backup, DBC/Table Repair, Table (Re)Indexing, Version Upgrade in one
   c) UI for repairing individual tables.
   d) All activity logged in individual log files.
   e) (Re)Indexing & Backup can be scheduled via Windows Scheduled Tasks or other
      task program.
   f) Prevents other users from logging into the AIRS system while processing.
   g) Version Upgrade logs are exported and can be sent to technical support for
   h) Failsafe login feature which allows an administrator to reset the password of
      another user, without logging into AIRS. Very useful if no one in the agency
      knows the ADMIN password. Replaces getpass.exe in URS.
   i) Email notification of Backup/(Re)Indexing is completion status – Successful or
   j) Table archiving…
      i) This feature will move all Encounters, Services, Sessions and/or Needle
           Exchange records with an effective date less than or equal to the date
           entered below from your production system into archive tables.
      ii) This includes associated Referrals, CTR, Group Activates, Collaterals
           Involved, and Problems Encountered.
      iii) Archived records are permanently removed from the production tables but
           can be accessed by opening their corresponding archive. Refer to the
           system documentation for archive table names.

2) Security:
   a) Only a user flagged as a system administrator can log into the system.
   b) Backup files can be password protected using the same password.

3) Scheduled Task Support:
   a) Backup and (Re)Indexing can be setup with the Windows ‘Scheduled Tasks’ to
      run these processes in an unattended mode. Windows network security
      authentication is used to execute these programs.
      i) Pass BACKUP or INDEX as parameters to signify the process to setup.

4) Repair:
   a) With every upgrade a new set of metadata tables are delivered which facilitate
      the easy repair of tables with damaged headers or memo fields. The special
      login REPAIRALL | NOW is provided to allow a user to (only) repair a specific
      table or all tables.

5) Version Upgrade:
   a) Version protected – The user can’t run the same version upgrade twice against
      the same AIRS system.
   b) Detailed error messages, in the event of a runtime error.
   c) Execution of patch / hot fix scripts – eliminating the need for full version upgrades
      when only simple changes are needed.
   d) Display Version Upgrade history.
   e) Each step and table are logged, these logs can be exported and sent to Defran
      for analysis. Data is not logged, only activity.
   f) Patch / Script delivery feature – Allows for the execution of scripts to fix data
      quality issues or manipulate the database in various ways. The AIRS system
      contains 2 version numbers EXE & DBC. These are the same after a build. We
      should probably increase the version build of the DBC if we deliver a patch or fix
      which affects the data. Fixing a problem for an individual system would not
      require this increase.

6) Clear Administrator Connection:
   a) Monitor active connection(s) to the AIRS database. Select the connection you
      want to clear and press the Clear button. Only administrators with active
      connections appear in this list.

7) AIRS Runtime Errors:
   a) Monitor the most recent 40 errors from AIRS

8) Failsafe Login:
   a) This feature replaces the URS getpass.exe function. An authorized user can
       reset the password for an administrator.
       i) When the Failsafe login is invoked the system first looks for staff identified as
           Administrator AND Support. In the event that nothing is found it searches
           for staff identified as an Administrator and account enabled.
   b) To change a password for a non-support administrator the user must know the
       Login Name.
Commonly used functions and stored procedures
Major / commonly used functions/procedures stored in the service library.

      Openfile(cFile, cTag, cAlias) – Utility to open or select a table.
      SetRelation(cParentFile, cChildFile, cChildOrder, cRelatExpr, lAdditive) – Sets a
       relationship between tables.
      Closefile(cFile) – Closes a table and selects the 1st area.
      Save_Env2(aFiles) – Creates an array of all open files and cursors with their tags
       and rec #’s.
      Rest_Env2(aFiles) – Closes all files not in array, those in array - sets the tags
       and rec #’s.
      Age(tdDate, tdDOB) – Returns age of a client
      TimeSpent(cBeg_tm, cBeg_am, cEnd_tm, cEnd_am) – Returns a formatted
       difference in time.
      TimeSpentD(cBeg_tm, cBeg_am, cEnd_tm, cEnd_am) – Returns Time spent in
       decimal hours.
      FormHours(nTime) – Display time (numeric) in HH:MM format.
      Name(cLast, cFirst, cMI, nDisplen) – Returns a formatted client name.
      Time12(cTime) – Returns time in 12 hr. format.
      GetNextID(mid) – Returns the next ID from the next_id file.
      RecLock(cAlias) Standard record locking function. If a lock can't be obtained it
       asks the user for retries.
      Msg2User(msgscheme, cparm1, cparm2) This function displays standard
       messages to the user, gets response or displays a standard message window.
      OK2Close() – Determines if a record must be saves before closing a window.
      Sele_staff() – Creates a cursor for staff that can be assigned cases
      All_Staff() – Creates a cursor of all staff [staffcur]
      CIN_CHK(mcin) – check digit routine for 8 digit CIN
      V_screen(cScreen2Use) – Standard routine for validating user input on a form.
      Address2(TCADDR1,TCADDR2,TCCITY,TCSTATE,TCZIP) – Returns a 1 line
       character string formatted as address based on specified
      Address3(TCADDR1,TCADDR2,TCCITY,TCSTATE,TCZIP) – Returns a multi-
       line character string formatted as address based on specified ADDRESS1,
      Base_init() – Opens tables that will stay open throughout the session.
      Base_clos() – Closes the tables opend by Base_init().
      IsDupSSNO(tcClientID, tcSSNo) – Checks for duplicated Social Security Number
      Num2Char(nNum) – Converts Numeric expression to character expr. based on
       capital letters of english alphabet and digits from "0" to "9"
      Char2Num36(cCharNum) – Converts base36 expr. back to numeric
      Char2Num26( cCharNum) – Converts base26 expr. back to numeric
      Time24(cTime, cAm_Pm) – Returns time in 24 hour format
      OPENEXCL(cFile, cTag, cAlias) – Takes same parameters as OPENFILE but
       opens the file in exclusive mode; gives the user a chance to retry opening.
      FUNCTION URS_DOW(dDate) * Returns day of week as Monday = 1, Sunday =
Application Tools
Over the course of development there have been many tools developed to assist in the
process of maintaining, building, testing and releasing the application.
A list of the commonly used tools follows:

MakeZIPCode – Custom app to imports standard ZIP Codes from a 3 party provider.
SDT (Stonefield Database Toolkit) – VFP wrapper developed to overcome the problems
when refreshing the METADATA.

MakeEmpty – VFP program to prepare an empty system for new providers or
conversions from URS.

BuldProject – VFP program to pack libraries and build new exe.

Installshield Express – Custom scripts developed to release a version upgrade, and
workstation setup.

Enc_Service_Setup.exe – Custom app for maintaining Service Category / Encounter /
Service linkage.

PrepareNewSystem.exe – Custom app to prepare an installation for a new AIRS

SyncAIDA.prg – Custom VFP script used to generate a script to itemize differences
between the AIDA control database and the newest release.
Sample Test Plan

1) Version Upgrade Testing Plan
   a) Network security should be part of the test plan. It was suggested, during all
      discussions, that the network administrator define at least 2 work groups that can
      access the AIRS system. Prior to performing a Version Upgrade the network
      administrator should deny access to the AIRS Users group for the duration of the
      upgrade. Adding a Network security layer to the application increases the HIPAA
   b) AIRS Network Security Groups
      i) AIRS Administrators
           (1) These would be network users with the authority to perform database
               tasks such as indexing, backup and upgrades
           (2) Not all AIRS administrators should be in this group, only those described
      ii) AIRS Users
           (1) These are the regular users in AIRS, AIRS administrators could be
   c) Version Protection: Normally the user can’t perform a Version Upgrade on a
      system of the same version as the release. However, the user can ‘Force’ the
      Version Upgrade to happen be selecting the ‘Force’ option from the Version
      Conflict dialog. This dialog is displayed when the system senses that both
      databases and/or exe’s are at the same version number. It is suggested that the
      user contact the support department for special instructions on re-running a
      Version Upgrade on an already upgraded system. Under most circumstances a
      Version Upgrade can be re-run without restoring the system from a backup. It’s
      important to remember that restoring the system from a backup is the safest way
      to proceed. Use the Force option as a last resort.
   d) Access Protection:
      i) Users do not have the ability to log onto AIRS if the ‘AIRS Utilities’ is running.
           A vital part of the test is to have various users attempt to log into AIRS before
           and during a Version Upgrade.
      ii) AIRS should return an error message stating that the ‘AIRS Utilities’ active.
      iii) During a Version Upgrade the database is repeatedly opened and closed. An
           attempt to log into AIRS and another instance of the ‘AIRS Utilities’ must not
           be successful. A test must be performed at each ‘AIRS Utilities’ step.
   e) Error Recovery:
      i) During a Version Upgrade the client workstation connection to the network
           should be terminated – an appropriate message should be logged.
The following outlines the steps that are performed when a version upgrade is performed.

Process                         Test                                                     Outcome
Checking Availability           1. After Utility login a user should attempt to access   1. AIRS user should be denied access.
                                AIRS.                                                    2. Utility access should be denied.
                                2. A user should be in AIRS, a supervisor attempts
                                to login to the Utility.
Production System Backup        Automatic.                                               Backup should be in the backup folder
                                                                                         with a file name vp_ datetime .zip
Renaming Application            1. User should attempt to launch AIRS.                   1. Users should receive an error message
(Production)                                                                             when attempting to launch the AIRS
Opening Databases               None                                                     None – App will generate a runtime error
                                                                                         if this step is unsuccessful.
Running Pre-Processing          A script (prg) is executed prior to any processing       Depends on the script.
Script                          This is a required script (Default script returns
                                success, no code).
Updating Table(s) Type: B1      Seed next id table should be updated with values         Compare to control set
                                from production.
                                                                                         Production tables should be overwritten
Updating Table(s) Type: O1      Tables are replaced; schema and contents.                with the contents of the tables in the
                                                                                         upgrade distribution.
Updating Table(s) Type: O2      None in the system                                       None
Updating Table(s) Type: R1      Update database & table schema.                          All clients, activities and services from
                                                                                         prior to the upgrade should still be
                                                                                         available in the upgraded system.
Updating Table(s) Type: R2      None in the system                                       None
Updating Table(s) Type: R3                                                               1. Table schema is updated; new rows
                                                                                         2. Existing rows are retained.
Updating Table(s) Type: R4      None in the system                                       None
Updating Table(s) Type: R5      None in the system                                       None
Closing Databases               Automatic.                                               None
Running Post-Processing       A script (prg) is executed after tables are updated.   Depends on the script.
Script                        This is a required script.
Removing: Production                                                                 Files from the data folder are deleted from
Database & Tables                                                                    disk.
Removing: Production                                                                 Files from the metadata folder are deleted
MetaData                                                                             from disk.
Removing: Production          URS.EXE is deleted from production                     URS.EXE is deleted
Move »» Production: Updated                                                          Updated data and dbc is moved into the
Database & Tables                                                                    data folder.
Move »» Production:                                                                  Updated data and dbc is moved into the
MetaData                                                                             metadata folder.
Database Validation                                                                  VFP Validate Database command is
(Production)                                                                         executed. Should return no errors.
Replacing Editable Files      ONLY when editable files are included as part of       Newest editable files appear in the
                              the upgrade                                            editable_files folder.
Generating Index Keys         Intensive indexing is performed -                      1. No ‘seek-offset’ or ‘Index tags’ not
(Production)                                                                         found’ errors should be displayed after
                                                                                     2. An entry (success/failure) into Index log
                                                                                     is made.
Move »» Production:                                                                  Newest URS.EXE, help and urn32.dll
Application                                                                          should be in the root of AIRS.
Running Cleanup Script        Reserved – not at this time                            None
When performing Version upgrade testing the following special items must be examined
    1) Control Set: A good data control set must be developed to contain all possible
        data scenarios for all types of tables including look-up tables and PEMS to URS
    2) Next ID: You must compare the upgraded (next_id) table with that from the pre-
        upgrade control set.
    3) ZIP Code table: If a new table is delivered (quarterly), new rows must be in the
        system. The table is populated by a program and generally there should be the
        same number of more rows in the new table. The table must also contain a
        ‘99999’ row for ‘Other’.
    4) Table Type Actions: A selected group of ‘R1’, ‘R3’ and ‘O1’ table types should
        be compared; pre & post upgrade systems.
    5) Pre & Post Upgrade processing: Pre & Post Version upgrade scripts are
        required for each upgrade cycle. As such, a template was created which simply
        returns success. As a test Pre & Post scripts should be developed to perform an
        innocuous task like changing the name of a client. Various objects are available
        to the program and this should be part of testing. Changes to the original data
        and data in the upgraded system should be included in the Test process.
    6) Index Generation: An intensive index generation process is performed on the
        upgraded database from the definitions in the metadata. To test this, the tester
        must be aware of an index key that is not present in the pre-upgraded system.
        This key should be present in the post-upgraded database.
    7) Version Number: The version information in the system table must match the
        version in the release notes.
    8) Script / Fix / Patch Options: A scenario should be developed (probably by the
        tester) in the following areas:
            a. DBC contains incorrect schema: Deliver a patch to fix this (systemic for all
                 agencies; increase build number).
            b. DBC view incorrect: Send script to update view definition (systemic for all
                 agencies; increase build number).
            c. Individual agency has misaligned id’s or need to update a flag; no
                 increase of build is expected.

       System Entity Relationship Diagrams (ERDs) and table content are attached as
       Appendix XX.

       Database Tables are attached as Appendix XX.

       Software User Manuals that describe functional specifications (user functionality)
       including interface behavior, administrative setup, security settings, and other
       system features are attached as Appendix XX.

       All technical appendices are available for download from
Specifications for any updates or changes to interface, database, table
content, data exchange or export and reports will be provided at the
beginning of the contract year. These changes are subject to information
from the Federal Government on grant requirements that is periodically
updated and may not be available at the time of RFP release.

A test copy of the AIRS software is available on request. Full source code
for the existing system will be provided to the successful RFP applicant.

Source code for the AIRS and AIDA systems, including any future
amendments or additions made under the terms of this solicitation, remain
the property of Health Research Incorporated. All existing license
agreements remain in full force.