Using the Database

No questions yet.

Exporting the Data

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:

a claim header table
a claim detail table with the line item 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.

No questions yet.

Setting up Database Connection

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 arrow Control Panel arrow Administrative Tools arrow 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 arrow 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:

Production Connection
Test Connection

Each tab has the following fields:

Database Type - Select the type of the database from the drop-down menu: is either ODBC or Microsoft SQL Server either with Windows Authentication or with explicit credentials.
Note: If you need other types, please contact us for customizations.
MS SQL Server
MS SQL Server Integrated Security
ODBC Connection
ODBC Connection for Oracle
Database Server Name — Specify the database server name or DSN. In case of SQL Server, it is the IP address or the name of the database server; for ODBC it is the Data Source Name (DSN) that is defined through the ODBC setup in the Control Panel of Windows.
Database - Enter the database name under the above connection. Typically, this field will be empty for ODBC setup.
Username - Specify the user name that has privileges to the database. Typically, this field will be empty for ODBC setup.
Password - Specify user's password. Typically, this field will be empty for ODBC setup.
Claim Header Table Name - Enter the name that you give to your Claim header table. The default name is suggested but can be changed.
Claim Detail Table Name - Enter the name of the table that contains the line information. A default name is suggested but can be changed.

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.

No questions yet.

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.

1. Select the Data Base arrow Create SQL Table in the main menu. 

The "Create SQL Table" menu
2. The following window will be displayed.

The "Create SQL Tables" window
3. Define the following options:


Microsoft SQL Server


Live Database
Test Database


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

Claim Header Tables Script
Claim Detail Table Script

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

No questions yet.

Selecting Fields to Export

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.

1. Select Data Base arrow 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
3. The right side of the screen shows all the available database fields and columns. Choose fields to be exported by selecting checkboxes in front of the fields in the following tables:

File Level Data
Billing Provider
Pay-to Provider
Pay-to Payer
Rendering Provider
Attending Provider
Operating Provider
Other Provider
Referring Provider
Primary Care Provider
Supervising Provider
Assistant Surgeon
Pick-up Location
Drop-off Location
Destination Payer
COB Payer 1
COB Payer 2
Claim Data
CN1 — Contact Information
Dental Claims
Diagnosis Information
Procedure Information
Value Code
Condition Codes
Occurrence Codes
CR1 — Ambulance Transport Information
Ambulance Certification



Service Information


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


4. Once you have made your selection or selected all you have to test your setup, click on the "Test Configuration" button. 

The "Test Configuration" button

The program will connect to the database and read the table definitions. Afterwards all the fields that you selected will be tested.

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.

5. Only after the configuration have been checked out, are you allowed to save it. Click the "Save Field Configuration" button.

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:

Your database cannot handle long column names such as "CA Clipper" or "IBM AS 400."
You already have a staging table and want to import the data straight into your own table which has different field names.

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

1. Check the box near the field for import.
2. Click the field name twice. Do not double-click, but slowly click twice. You will see that the field name is now editable.
3. Change the name to your value and click somewhere outside the field.
4. Your field name has now been changed.
edit1 Changed to: edit2
Editing a field name


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


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.


The possible solutions are:

1. To unselect the fields that course errors in the HIPAA Claim Master.
2. To create missing fields in the database and then try again.


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.

No questions yet.

Browsing Exported Data

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 arrow 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

No questions yet.

Displaying the Revenue or Procedure Code Descriptions

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

CMS Description

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:

HCPCS codes
CPT procedure codes.
All dental procedure 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.

1. Select the Data Base arrow Code Look-up Table in the main menu.

The "Code Look-up Table" menu
2. You will see the following screen:

The "Code Look-up" screen

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.


3. Type the name of the table and click the "Open Table" button.

The "Open Table" button
4. If the table is found, then the drop-down boxes for "Code Field" and "Description Field" will be populated with all the available fields in your table. You can then choose the field that will carry the code and description.

The "Code Field" and "Description Field" values populated
5. Click "Save." 

The "Save" button

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

No questions yet.

Creating Code Look-up Tables

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

Procedure Codes (HCPCS,CPT, Dental and ICD procedure codes)
Revenue Codes
Diagnosis Codes (ICD-9 and ICD-10)

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"

No questions yet.

Troubleshooting Data Export

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.

No questions yet.

Publish modules to the "offcanvs" position.