The HIPAA Claim Master does not come with a database. But it can link to any ODBC- or OleDB-compliant database. Database integration is a component of the HIPAA Claim Master and can be licensed as add-in.

Most businesses that do HIPAA transactions have a database engine on their premises. Microsoft SQL Server is probably the most widely used business database; Oracle or IBM database also have broad acceptance. The open source and free database MySQL finds more and more acceptance in the business world and is great for developers.

The HIPAA Claim Master uses two database tables for the claim information:

Scripts are provided to create these tables for some database types. You might have to modify the scripts to suit your actual taste of the SQL language.

Once these tables are created, claims from EDI files can be loaded into this database. Your application can take the claim information from there and you can run your business logic on these records. The HIPAA Claim Master can in turn read the database record and re-create the claim image from the database. In the next implementation you will even be able to create an 837 EDI file from those database records.

The HIPAA Claim Master lets you set up two connections, to a test and to a production database. The deciding factor on which database is used is the ISA_15 data element of the 837 file. This indicator is either a "P" for production or "T" for test. If you need only one database, set up both connections identically.

The "Data Base" menu

The sequence of actions to setup the data export is the following:

How to Save Data to the Database

The HIPAA Claim Master provides the "Data Import" option. You can import the claim data to a database while printing or imaging claims (see Processing an Electronic Claim, Printing Electronic Claims). In the "Screen" mode, the data import will does not work since you can interrupt the viewing of EDI claims at any time and this might lead to incomplete data import.

Notice: The image file will be created based on the pre-defined settings. Read more in

The "Data Import" check-box

The data is exported once the user clicks on the "Run" button. The data is written to the database once the "Run" button has been clicked and only if the "Data Import" checkbox is activated.

The "Processing Results" window will display notification about data export.

The "Processing Results" window

Now you can browse exported data.

The HIPAA Claim Master relies on ODBC; or in the case of Microsoft SQL server on OleDB to connect to a database.

Tip: If you use ODBC for the connection, you will need to set up the ODBC connection before using HIPAA Claim Master with the database. Typically, ODBC connections are established in the Windows Control Panel Administrative Tools Data Sources (ODBC)setup screen. Setting up the ODBC connection varies from database to database.

The "ODBC Data Source Administrator" screen in Windows

Select Data Base Connection and Fields in the main menu.

The "Connection and Fields" menu

2. You will be confronted with the following screen where you can set up the database connection and enter the names of the database tables.

The "Database Server Setup for OLE/ODBC Connection

On the left side of the screen, define the database connection properties.

There are two tabs:

Each tab has the following fields:

We will configure the Test Connection and show the process on the screen-shots.

The connection properties fields

To create tables in your database, click on the "You can create the tables through a separate screen" link. For instructions, refer to Creating Tables.

A link to create tables

Once the connection parameters have been entered, click "Save Connection Information."

The "Save Connection Information" button

To verify if the entered parameters are correct, click on the "Test Connection" button. You cannot export claims before this connection tests successfully.

If the test has passed successfully, you will see the following notification:

The success message

Once the test has passed successfully, the "Chеcked" box becomes enabled.

The "Checked" checkbox

Once you have the database connection defined and tested, you can enter the table names. See Selecting Fields to Export.

If you need to create the tables first, then see Creating Tables.

Before Creating Tables...

The scripts that are supplied with the HIPAA Claim Master will generate every field that is available for import. These are more than 260 fields in the claim header. You will not need all these fields. You can edit the scripts to only use the fields that your adjudication system requires. System performance will be better if you only select the needed fields.

Not all databases are supported. Most likely only Microsoft SQL server and MYSQL scripts are supplied. And even here different versions of the database behave differently. If you have database expertise, please modify the scripts accordingly. We invite you to submit improvements and requests.

If you encounter persistent errors with the script, we recommend you to work with the scripts within the database environment. SQL Server Enterprise Manager or SQLyog for example have an SQL query analyzer that helps you tweak the scripts until they run.

Also, feel free to contact us if you have any problems.

 

How to Create Tables

In order to facilitate the development, HIPAA Claim Master allows you to create the staging tables for your database where HIPAA Claim Master will export the claim data.

Before data export, you need to create the tables through SQL table statements. You can do it using the built-in function of the HIPAA Claim Master. Creating these tables is a quite intimidating task. Once you have your connection defined and tested, follow the instructions below.

Note: The tables are created via the "Drop Table" and "Create Table" commands. The scripts come from two files: EDI_Claims.sql and EDI_ClaimDetail.sql that are distributed with the application. Please, contact HIPAAsuite if you need help with the database setup.

Database

Environment

Scripts

On the following tabs there are SQL scripts used to create the appropriate tables:

Tip: You can modify the scripts so that they run on your specific database and to conform to your database's specific SQL syntax. For each script, click "Save."

The "Save" button

For each script, click "Execute Script" to create the corresponding table in the database.

Warning: Executing the scripts will wipe out any previous tables that you created and you will loose all data.

The "Execute Script" button

Once the table has been created successfully, you will see the following notification:

The success message

The HIPAA Claim Master can export all the fields that exist on the HCFA-1500 or the UB92 form. You might not want to export every field, but only those that you have in your database. You are really only limited by your database engine. In order to reassemble the claim from the database, it is better to have all the fields selected and available.

Once you have created created your tables and tested connection and table names, you can select which fields to export. Follow the instructions below.

 

Detail

 

Tip: We recommend to select all fields. This makes it possible to reassemble all the information in the EDI file and create the image.

 

Tip: Sometimes it might be necessary to rename a field or fields. Some legacy database want upper case only or lower case only field names. The HIPAA Claim Master allows you to rename any field in any table. Click twice (not double-click) on a field and you are in "Edit" mode. Read more in the following "Renaming Fields" sub-topic.

The field selection list in "Edit" Mode

 

Notice: Before you can save your field configuration, you have to test the setup to make sure that all the fields that you selected exist in the tables that you specified. The table scripts that come with the HIPAA Claim Master do not have all the fields because some of the fields are very specific and not of use to many people.

You should receive a message like this. Click "OK."

The success message

If there are errors, refer to the following "Troubleshooting Data Fields" sub-section.

Important! After you have checked the setup and got the success message, you still need to save the configuration otherwise nothing will be exported.

The "Save Field Configuration" button

 

Renaming Fields

There are a few reasons why you would want to use different field names than the ones given by the HIPAA Claim Master:

HIPAA Claim Master lets you change the names of the columns that you selected for export. All you have to do is:

 

The new value will be saved as long as this field is selected for Import. If you unselect the field and save your selection, the field will revert to its original name.

 

Notice: Field names must not contain spaces.

 

Troubleshooting Data Fields

Before you can save your field configuration, you have to test the setup to make sure that all the fields that you selected exist in the tables that you specified. The table scripts that come with the HIPAA Claim Master do not have all the fields because some of the fields are very specific and not of use to many people.

After clicking on the "Test Configuration" button, there maybe error messages. If the program finds errors, the "ODBC Error" screen will come up and tell you exactly which fields caused errors.

ODBC Setup Errors

Course

The table-created scripts that come with the HIPAA Claim Master do not contain all possible columns that the data export provides. You can add the fields manually in your database administration software. Some of the fields are very specialized and would not be suitable for general distribution and make the tables unnecessarily complicated. If the fields do not exist, an error message will come up and you have the option to deselect them.

ODBC Setup Errors

In the picture above, you can see the columns in error obviously do not exist in the table that was specified.

Solution

The possible solutions are:

 

Important! After you checked the setup and got the "Setup checks Okay" message, you still need to save the configuration otherwise nothing will be exported.

The HIPAA Claim Master can access the exported data and show it to the user. This might be helpful in tracking particular claims.

Select the Data Base  Claims in Database in the main menu.

The "Claims in Database" menu

You will see the following screen where you can see all the claims and review them. In addition, you can query the database for any of the displayed fields. For example, if you want to check if all the claims from a specific file were imported, you could type the filename into the appropriate field and click the "Query" button. The results will be returned.

Click on a payment on the left to load the claims associated with this payment on the right side.

Accessing the claims in the database

Editing Data

You can edit the data in the tables by typing in a cell. A dialog will ask you whether you want to save the changes.

Editing the field value

Viewing Claims

Click on the "View Claim" button to render the claim.

The following preview window will open.

Note: To preview the claims, you must have a printer installed on your PC.

A claim image created from a database record

EDI files do not carry the description of codes since EDI is all about saving space. But since the codes are available commercially or from CMS, you can link tables with those codes in your database application and have the HIPAA Claim Master look up the description. These tables needs to be in the database defined while Setting up Database Connection.

Descriptions in the standard Forms

The UB92 and UB04 as well as the dental forms have the "Description" field for the line item revenue or procedure codes. For the CMS-1500 forms one can put the description in the upper half of the field locator 24 D.

A part of a UB04 with revenue code descriptions

Dental Procedure Code Descriptions

A part of the CMS-1500 with the description filled in from the database

The CMS-1500 option is separate and can be selected in the Specific Options (the "CMS-1500: Display procedure description in 24D" check-box). Refer to Configuring Specific Options.

Linking the Procedure Code Table

Acceptable Codes:

 

Tip: Using the code look-up might slow down your application depending on the speed of your database. You can undo the code lookup by deleting the table name and clicking the "Save" button.

 

Follow the instructions below to show code descriptions.

If you don't have a table already with the procedure codes, you can create it in the claim master application. You see the sentence in blue "You can create the table....". Click on this and the following form will come up.

 

From now on, the HIPAA Claim Master will fill in the code description fields.

EDI is all about saving space and using code sets to represent more lengthy circumstances. But often it would be more convenient to have the descriptions of a revenue or procedure code visible instead of looking it up or knowing it by heart.

Important codes are

There are tens of thousands of codes and they are mostly available at the CMS web site or other web searches. The HIPAA Claim Master provides a database schema to create cross reference tables for those codes.

When we go to the menu item Database --> Code Look-up tables we see this screen

The Code Look-up form with tabs for Procedure, Revenue and Diagnosis codes

If you have already in your database a table that contains Procedure codes and their long and short descriptions, you can link to it in this form. Fill in your table name and open it and select the fields that contain the information.

If you don't have already a table with the procedure codes and and want to create a new table, you can go to Database --> Code Look-up table and then click on the the blue sentence "You can create..."

The following form will come up

The form to create and fill the look up table

Since the table does not exist we press the button :"Create table CODE" and we will see this form

The form with the script to create the look-up table

We click on the :Execute Script" button and create the table and close the form. Now the previous form has changed.

The form to manage procedure codes

The table is created but is empty, so now we can load the codes that we obtained separately. Maybe your company has a subscription to the code data as Ingenix, for example, sells, or you find them on the internet. We click on "Load from XLSX file" and navigate to the file. The spreadsheet has been created earlier and has the following layout:

First column the code, second the long description and third the short description.

Loading a previously prepared spread sheet with the HCPCS codes.

We navigate to the file and select it. Then we select the correct code type: HCPCS in our case and press on the button "Load Codes" Within a few seconds the file's codes are loaded into the database and now we can see the values.

The screen after the codes have been loaded

Procedure codes are now loaded and in the database. We can add other codes such as CPT and Dental codes to it. If you have duplicate codes in other files that you load, you will see a message that the code is already in the table and will not be loaded again.

 

Revenue Codes and Diagnosis Codes work very similar. Go to the menu Code Sets --> Revenue Codes

The menu Revenue Codes

Now the same form that you used for the procedure codes comes up only that it now points to a table "RevenueCodes"

The third table that gets created and filled the same way is the diagnosis code table. You can reach it form the Code Sets menu under "Diagnosis Codes"

When you start exporting claims into the staging tables, you may encounter errors in the beginning. This usually results from table definition issues. The HIPAA Claim Master displays a very detailed error message that tells you what claim has failed, at what field and, if applicable, what line. This information should help you to troubleshoot the problems. Sometimes you must fulfill serious detective work to find and correct issues.

Below are some of the most common errors.

 

Item Cannot Be Found

This error happens when a column that was selected for export does not exist in the Header table. In this particular case, it is the ClaimNo field. If you receive this error, go back to your table admin tool and check if the ClaimNofield is correct.

 

Multi-Step OleDB Operation Generated Errors

This is typically the message when the field definition in the database is different from the data. Here we see that the error occurred with the billing provider's specialty. The field definition in the table was set as varchar(10) and the value here "000000002331" has 12 digits causing the error.

 

Field Does Not Have a Default Value

The field ClaimNo is defined as "Not Null." But somehow this data was not present and the HIPAA Claim Master tries to update the table with a null ClaimNo.

 

Type Mismatch

This error occurs when the HIPAA Claim Master tries to "write a letter" to a field that is defined as "Integer," for example. The data type of the column does not match the data type of the value.

 

Claim Detail Errors

The errors with the claim detail table are similar; the error message indicates the line number where the error occurred.