2b Creating 999 File Tables
Once you have configured the database connection (Setting up Database Connection), follow the instructions below.
| 1. | Select EDI Exchange  Create 999 Files Tables in the main menu. |
The menu item to create the necessary tables
2. The "Create Tables for 999 Files" screen will appear. Table creation and/or modification for your database is handled here.
The screen to create the tables
3.Select the database type you use for your host HIPAA application.
| • | Database |
| • | Microsoft SQL Server (SQL Server 2008 and above) |
| • | Oracle |
| • | MySQL |
Note: In case your database is not listed, modify the scripts or ask your database administrator to make the necessary modifications.
| 1. | The following tables are part of EDI Exchange: |
| • | ImplementationAck — SQL statements to create the "ImplementationAck" table in your database. This table contains information about Acknowledgments. |
| • | TransactionResponse — SQL statements to create the "TransactionResponse" table in your database. This table contains individual transactions contained in 999 files. |
| • | SegmentErrors — SQL statements to create the "SegmentErrors" table in your database. This table contains individual segments in error contained in 999 transactions. |
| • | ElementNotes — SQL statements to create the "ElementNotes" table in your database. The table contains the elements in error in a specific segment. |
| 5. | You can modify the scripts so that they run on your specific database. Once you have you modified the script, click "Save." |
Tip: Every database system has their own little syntax idiosyncrasies and the scripts might require tweaking. You can edit the table scripts in this screen and save your modified scripts. One example are 'date' and 'time' or 'money' data types that do not exist in SQL Server 2005. You can just rename those types to 'datetime' and save you script and it will run fine.
The "Save" button
| 6. | For each script on every tab, click "Execute Script" to create the corresponding table in the database. The "Execute Script" button |
Notice: Creating tables means clicking the "Execute Script" button in all four tabs of the "Create Tables for 999 Files" window. Then close this window.
| 7. | Once the table has been created successfully, you will see the following notification: The Create Table script success message |
Warning: Double-execution of a script wipes out the previous table you have created. A prompt will warn you before deleting an existing table. To Add/Remove fields use the "Compare Script..." button. Remove the script files once you have created the tables so nobody can destroy the tables by accident.
The double-execution warning message.
Make sure there are no error messages and the table creation has been completed successfully.
Compare Script with existing Table
HIPAAsuite products go through continual development and improvements. Often these changes lead to new fields in the database. While it is easy to drop a table and regenerate it with the new fields, you will loose all the data in the table. To avoid this trouble there is the button "Compare Script with existing Table". I you click this, the table structure in your database will be compared with the script. There are two possible outcomes. Your table is up to date
Table is up to date
or if your table is missing recently added fields, you will see a window pop up that shows an 'Alter Table' script with which you can add those fields to the table without interfering with existing data. In the latter case, you can click the "Execute Script" button and the field will be added and a message will confirm your changes
Alter Table statement successfully executed.
No questions yet.