An approach to processing an EDI 999 functional acknowledgment file

So you send an EDI file to your trading partner and wait for a 999 functional ack file to be returned in order to determine whether they received the file and if it was either rejected or accepted. You receive the 999 file and inspect the code in the AK901 data element to see if it's an "A" - accepted or an "R" rejected. (and by the way, there are more allowable codes for this data element such as an "E" - accepted but with errors, etc). 

So next you move the file to some archive location and that's that right?

Well should it end there?
How about an alternative solution: along with determining whether your trading partner either accepted or rejected the file - what about storing the entire contents of each 999 file (both inbound and outbound) in a normalized relational structure which can be easily queried for analysis and insight into which of your trading partners are sending out rejections and error notes and which trading partners are accepting all your error-free transactions?

Maybe the following questions can also improve the quality of your data and processes:
1) What's in the other segments other than only the AK -  such as the IK segments and the associated data errors and error types/codes?
2) What are the actual segments and data elements that result in regular rejections and what are the percentages of those various errors?

One could utilize this analytic information to perhaps go into one's system of record or billing application to fix these problems at the source? Or perhaps it's a balancing issue and there needs to be a programmatic fix in some code instead of you (the EDI operator/analyst) "hand-editing" the raw EDI file and re sending it!

Well that is where a database that is normalized around the actual 999 transaction comes in very handy.

This link http://REMOVED allows you access to download a sql script (zipped) that creates table structures (and stored procedures that can be used for data inserts) for a complete 999 transaction including all its loops, segments, data elements and envelope information.
Follow the link and download the zipped file. One file inside the zipped file is the sql script and the other txt file contains the installation instructions. This is for Microsoft SQL Server only. Any version of MS SQL Server is supported.

UPDATE: I had to disabled the link above because too many unethical people were downloading my database and telling their boss - "hey look what I did" without giving me (the real architect and developer) any recognition whatsoever. Sorry everyone but I've worked years building these EDI solutions - so email me for the scripts.

Steps for installing the EDI_999 database schema (includes DDL and DML):

1) Create an empty database called EDI_999 on your DEV sql server.
2) Run the script EDI_999.sql with the newly created, empty sql database selected in the drop down list in SQL Server Management Studio (SSMS).
3) The DDL script has a USE EDI_999 statement at the beginning of the script in order to avoid accidentally installing the database into some location other than the newly created empty database.

DISCLAIMER: I am not responsible for any unintentional harm this script could cause if it is modified and somehow pollutes your master database with unwanted sql objects or any other issue. So follow the instructions and you will be fine. The script is "transactionalized" so it will either all succeed and the database objects created or it will all fail and nothing will be created. And please use your DEV sandbox for any installation and experimentation.

I know this is a long post but I would like to leave you with one more opinion:

"Many programmers, especially those who are new to EDI, have the erroneous idea that it is easier to "dump" data from an EDI file into a flat table so that they can later manipulate, organize and transfer the data into a relational database. They also incorrectly think that this method avoids them from having to know the EDI file structure. But not only is this thinking wrong (since it takes just about the same amount of coding to dump data into a flat table, as it would be to translate an EDI file directly into a normalized relational database); but it also destroys the parent-child relationships, and the many hierarchies that exist in HIPAA EDI transactions."

This is the reason behind the decision to create a database structure that "mirrors" the hierarchical structure of  the EDI transaction itself. A "relational representation" of the EDI file structure per se. So your data dictionary and ERD are self describing if you already know the EDI file structure and it's loop/segment/element hierarchies and parent-child relationships which, along with other data rules & business cases, are already defined - in depth -  in the 999 implementation guide (TR3).

When I hear the phrases "claim header" and "claim header details" I cringe because healthcare finance, billing and reimbursement does NOT resemble other types of billing, such as retail billing or invoicing AT ALL. One's Target or Walmart purchases are not "adjudicated" based on various fee schedules, contractual obligations and reimbursement rules at the cashier checkout station?

Need a parser? Message me. (Try to hire developers with technical skills AND healthcare finance skills when it comes to HIPAA EDI and HL7 positions - we are out there).