APPENDIX A 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 histories. 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 administrator. 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 edit. 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 CAPC Assessment CLIENT_MODULE HIV Prevention Outcome Monitoring CLIENT_MODULE Treatment Adherence CLIENT_MODULE COBRA Outcomes Module Menu Selection / Name Class Library Class -------------------------------------------------------------------------------------------- CLIENT_MODULE Services: 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: 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. Considerations 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 application. 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 analysis. 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 Failure. 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 ADDRESS1,ADDRESS2,CITY,STATE, AND ZIP FIELDS Address3(TCADDR1,TCADDR2,TCCITY,TCSTATE,TCZIP) – Returns a multi- line character string formatted as address based on specified ADDRESS1, ADDRESS2, CITY, STATE, AND ZIP. 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 = 7 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 provider. 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 compliance. 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 above. ii) AIRS Users (1) These are the regular users in AIRS, AIRS administrators could be included. 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 system. 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 delivered. 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 Application 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 processing. 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 (Custom) When performing Version upgrade testing the following special items must be examined individually: 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 contracts. 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 www.xxxxxxxxx.com. 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.