BizTalk 2013 EDI for Health Care: HIPAA-Compliant 834 (Enrollment) and 837 (Claims) Solutions (2014)

Chapter 8. Custom Business Rules

The BizTalk Rule Engine (BRE) is often looked at as an ideal option for building out business rules in BizTalk Server. However, the actual use, maintenance, and deployment of the BRE is lacking in several aspects – and when you really want to have complete control over your business rules, with the added ability to easily modify, maintain, and test these rules – the best approach is to go with a custom rules engine solution, ideally based within SQL Server. The solution outlined in Chapter 6 “Solution: Receiving 834 Data” shows the inbound 834 data being sent to a business rules stored procedure. This chapter will outline the details behind this type of implementation, and will show how to work directly with the 834 XML data to apply business rule checks. The end result of the approach outlined in this chapter is a solution that is extremely easy to test (you can do all of your work from a SQL query window) and to maintain, especially post-production (modification of a stored procedure is all that is required – no updates to DLLs, BizTalk components, schemas, etc.).

Figure 8-1 shows an overview of what the custom business rule solution looks like, and how it integrates with the inbound 834 data (this could also be applied to other document types, like the 837, if needed).

Figure 8-1. Custom Business Rule Flow

Figure 8-1 shows the basic flow for this solution. The inbound 834 arrives on the orchestration (after the split of the original 834 occurs – see Chapter 7 “Pipelines for 834 Processing” for details on splitting an 834 into individual subscriber data feeds), and there are several steps that are then taken. The first step is to do a lookup of the historical data related to this 834. The lookup in this demo will be based on the subscriber’s SSN. The next step is to push both the historical XML and the current 834 XML to the business rule stored procedure for processing.

Among other things, the business rules are used to determine if the inbound data exists in the current internal database system (which means this is going to be an update to data) or if it does not exist (which means that it is an addition to the existing data). Drops, reinstates, additions, changes, terminations – whatever the 834 is saying is happening can be validated against the internal historical data. The business rules ensure that the data is handled properly – so, even if the inbound 834 says that the record is an “Update” to existing data, the business rules see that the individual never existing in the system before, so it is actually an “Add”. In addition to determining what is occurring with the data, the business rules also check to see (in the case of a change) what data has changed in the current 834 from the historical data in the system.

Note This chapter builds on concepts outlined in Chapters 6 and 7 for the inbound 834.

The Business Rules

For this solution, the following business rules will be checked:

· Which of the following are accurate for the inbound records (subscriber or dependents) – is it an:

o Add

o Change

o Drop

o Termination

o Reinstate

o Has no change occurred on the data?

· If it is a Change, Termination, or Reinstate, have any of the following fields changed?

o First Name

o Last Name

o Date of Birth

o Date of Death

o Ethnicity

The Stored Procedure

The structure of the stored procedure for this solution is as follows:

· It has two input parameters and one output parameter

o @historicalData – this is the first input parameter, of type XML, and represents the historical data in XML format.

o @current834 – this is the second input parameter of type XML, and it contains the 834 XML passed in from the orchestration.

o @error – this is an output parameter of type nvarchar(max), and will contain the text of any error that occurs during the processing of the data.

· The inbound XML documents are both queried (using XQuery) for their data and the data is inserted into traditional SQL tables (in this case, temporary tables) for ease of querying and sorting.

· The inserting and updating of data (for example, the setting of flags in a table showing the outcome of the various business rule checks) is all contained within a single transaction. This ensures that the data is either fully checked, or everything is rolled back so that it can be resubmitted once the error has been addressed. If the transaction fails, and an error occurs, this error will be returned in the @error output parameter.

Listing 8-1 shows the framework for the stored procedure. The discussions in this chapter will show code that will be added to this procedure to make it a fully functional business rule processor. As code is described, the text of this chapter will say where in the following stored procedure it will need to be added. For example, the section on inserting the historical data from the XML into a temporary table will say that it should be inserted into the block [INSERT HISTORICAL HERE], which you can see in Listing 8-1 is one of the four commented sections.

Listing 8-1. Stored Procedure Framework

CREATE PROCEDURE [dbo].[Custom_834_Business_Rules]
(
@historical xml
,@current xml
,@errorDescription nvarchar(max) output
)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @docHandle int
,@currentDate datetime

SELECT @currentDate = GETDATE()

BEGIN TRAN
BEGIN TRY

-- [INSERT HISTORICAL HERE]
-- [INSERT CURRENT 834 HERE]
-- [CHECK TYPE OF RECORD]
-- [CHECK IF FIELDS HAVE CHANGED]

END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN

SELECT @errorNumber = ERROR_NUMBER()
,@errorDescription = ERROR_MESSAGE()

END CATCH
IF @@TRANCOUNT > 0
COMMIT TRAN

Working with the Historical Data

For purposes of this discussion, the historical data is being queried from Oracle, transformed to XML (in this case directly from the procedure in Oracle querying the data), and is passed in as the first parameter to the stored procedure. If the historical data in your environment resides in SQL Server, then there is no need to pass it in as a separate input parameter – you can do your lookups directly against the database. If the data (such as in this case) resides in Oracle or another data model (flat file, XML, web service, etc.), then you will want to pass it in as a parameter so that you can do side by side comparisons right in the business rule stored procedure.

For purposes of illustration, the document shown in Listing 8-2 is an example of what the XML could look like from the historical data repository. Unlike the 834 XML, this XML can be tailored to any pattern of your choosing, since you have control over the structure of it. The goal is to make this structure as simplistic as possible, matching the way in which you want to work with it in the business rule comparisons. Listing 8-3 shows how to extract this information into a temporary SQL table for processing.

Only a few nodes are shown in Listings 8-2 and 8-3, as you should be able to see the pattern in how to structure and query your data. The fields that actually populate the historical temporary table (#historical) should match exactly what is populated in the field list that is pulled from the 834 (shown in Listing 8-4) so that your comparisons can be done against two identically structured tables (this isn’t a requirement, but will certainly simplify your business rule logic).

Listing 8-2. Sample Historical XML Structure

Listing 8-3. Extracting the Data from the Historical XML

-- insert this in the [INSERT HISTORICAL HERE] block of the procedure
-- in Listing 8-1

SELECT NEWID() As ID –- give each record a temp unique ID
,T.c.query('SSN').value('.','varchar(100)') As SSN
,T.c.query('FIRSTNAME').value('.','varchar(100)') As FIRSTNAME
,T.c.query('LASTNAME').value('.','varchar(100)') As LASTNAME
INTO #Historical
FROM @historical.nodes('/ResultSet/MemberInfo') T(c)

Working with the Current 834 Data

The 834 XML data that is passed in as the second parameter to the stored procedure is the 834 directly from the orchestration, based on the 834 XSD. The code in Listing 8-4 shows how to pull a number of key fields from the 834 XML, and gives node paths to each (there are a variety of fields listed here to show where the data comes from in the 834 XML). The query extracts each of the unique sets of data from the inbound 834 and inserts them into a temporary table (named #current). For example, if there is one subscriber and four dependents, then there will be a total of five records inserted into the temporary table.

Listing 8-4. Extracting the Data from the 834 XML

-- insert this in the [INSERT CURRENT 834 HERE] block of the procedure
-- in Listing 8-1
SELECT NEWID() As ID –- give each record a temp unique ID

,T.c.query('*[local-name()=''NM1_SubLoop'']/*[local-name()
=''TS834_2100A_Loop'']/*[local-name()=''NM1_MemberName'']/
NM109_MemberIdentifier').value('.','varchar(100)') As SSN

,T.c.query('*[local-name()=''NM1_SubLoop'']/*[local-name()
=''TS834_2100A_Loop'']/*[local-name()=''NM1_MemberName'']/
NM104_MemberFirstName').value('.','varchar(100)') As FIRSTNAME

,T.c.query('*[local-name()=''NM1_SubLoop'']/*[local-name()
=''TS834_2100A_Loop'']/*[local-name()=''NM1_MemberName'']/
NM103_MemberLastName').value('.','varchar(100)') As LASTNAME

,T.c.query('*[local-name()=''NM1_SubLoop'']/*[local-name()
=''TS834_2100A_Loop'']/*[local-name()=''DMG_MemberDemographics'']/
DMG02_MemberBirthDate').value('.','varchar(100)') As DATEOFBIRTH

,T.c.query('*[local-name()=''INS_MemberLevelDetail'']/*[local-name()
=''INS12_MemberIndividualDeathDate'']').value('.','varchar(100)')
As DATEOFDEATH

,T.c.query('*[local-name()=''NM1_SubLoop'']/*[local-name()
=''TS834_2100A_Loop'']/*[local-name()=''DMG_MemberDemographics'']
/*[local-name()=''C056_CompositeRaceorEthnicityInformation'']
/C05601_RaceorEthnicityCode').value('.','varchar(100)') As ETHNICITY

,T.c.query('*[local-name()=''TS834_2300_Loop'']/*[local-name()
=''DTP_HealthCoverageDates'']/*[local-name()=''DTP03_CoveragePeriod'' and
../*[local-name()=''DTP01_DateTimeQualifier''] =
''348'']').value('.','varchar(100)') As EFFECTIVEDATE

,T.c.query('*[local-name()=''TS834_2300_Loop'']/*[local-name()
=''DTP_HealthCoverageDates'']/*[local-name()=''DTP03_CoveragePeriod'' and
../*[local-name()=''DTP01_DateTimeQualifier''] =
''349'']').value('.','varchar(100)') As LIMITDATE

INTO #current
FROM @current.nodes('//*[local-name()=''TS834_2000_Loop'']') T(c)

As you can see from the code in Listing 8-4, the data comes from a variety of locations in the 834 XML. Some of the nodes are obvious, others can only really be identified by referencing the 834 implementation guide specific to the trading partner(s) you are working with. Listing 8-5 shows a snippet of the 834 XML that contains the member’s first name, which is one of the fields pulled in the preceding code.

Listing 8-5. A Snippet of the 834 XML Showing the Location of Member Name

<TS834_2000_Loop>
...
<NM1_SubLoop>
<TS834_2100A_Loop>
<NM1_MemberName>
<NM101_EntityIdentifierCode>IL</NM101_EntityIdentifierCode>
<NM102_EntityTypeQualifier>1</NM102_EntityTypeQualifier>
<NM103_MemberLastName>BOB</NM103_MemberLastName>
<NM104_MemberFirstName>JONES</NM104_MemberFirstName>
...

Determining Adds, Changes, Drops, Terms, Etc.

Now that the data has been inserted into two temporary tables, it is fairly easy to do comparisons and checks for the various business rules. Some sample implementations of the rules are shown in Listings 8-6 through 8-10. As you can see, the queries are extremely simple, even though the original structure of the data was lodged in various locations in the hierarchical inbound 834 data, and comes from two different sources (historical and current). By moving this information down to the SQL level, basic data comparisons are greatly simplified over any alternative approach (including the BRE!).

Note The comparisons here are all based on matching on the SSN, but any field or combinations of fields that make a record unique in both the 834 XML and the historical data will work. In most cases with 834 data, you will need to come up with a combination of fields to match on, which include first name, last name, date of birth, and so on as not all records will have SSN information.

Listing 8-6. Determining Adds

-- insert this in the [CHECK TYPE OF RECORD] block of the procedure
-- in Listing 8-1

-- show all records that are in the current, but not in the historical data

SELECT A.ID As Person_ID
,’Add’ As Type
FROM #historical A
WHERE A.SSN NOT IN (SELECT B.SSN FROM #current B)

Listing 8-7. Determining Drops

-- insert this in the [CHECK TYPE OF RECORD] block of the procedure
-- in Listing 8-1

-- show all records that are in the historical, but not in the current data

SELECT A.ID As Person_ID
,’Drop’ As Type
FROM #historical A
WHERE A.SSN NOT IN (SELECT B.SSN FROM #current B)
AND DATEDIFF(DAY, @currentDate, A.LIMITDATE) >= 0
–- only want those with limit dates in the future

Listing 8-8. Determining Terminations

-- insert this in the [CHECK TYPE OF RECORD] block of the procedure
-- in Listing 8-1

SELECT A.ID As Person_ID
,’Termination’ As Type
FROM #current A
LEFT JOIN #historical B ON A.SSN = B.SSN
WHERE DATEDIFF(DAY, @currentDate, B.LIMITDATE) >= 0
-- only want those with limit dates in the future
AND DATEDIFF(DAY, A.LIMITDATE, B.LIMITDATE) != 0
-- limits must be different

Listing 8-9. Checking if the First Name has Changed

-- insert this in the [CHECK IF FIELDS HAVE CHANGED] block of the procedure
-- in Listing 8-1

SELECT A.ID As Person_ID
,’First Name Changed’ As Flag
FROM #current A
LEFT JOIN #historical B ON A.SSN = B.SSN
WHERE CASE WHEN A.FIRSTNAME = B.FIRSTNAME THEN 0 ELSE 1 END = 1

Listing 8-10. Checking if the Ethnicity has Changed

-- insert this in the [CHECK IF FIELDS HAVE CHANGED] block of the procedure
-- in Listing 8-1

SELECT A.ID As Person_ID
,’Ethnicity Changed’ As Flag
FROM #current A
LEFT JOIN #historical B ON A.SSN = B.SSN
WHERE CASE WHEN A.ETHNICITY = B.ETHNICITY THEN 0 ELSE 1 END = 1

Testing the Stored Procedure

Because the two input parameters to the stored procedure are XML, it can be a little tricky to test the stored procedure. Listing 8-11 shows one approach, which can be executed directly from a SQL query window. This is a great way to test, as you can alter the XML easily and test the results of the stored procedure without having to write any external code. However, you may decide you want a more robust test harness – in which case writing a simple C# forms application will be the next best approach. Try to stay out of BizTalk while testing this portion of the code, as it will only add overhead to your processing and validation activities.

Listing 8-11. Testing the Stored Procedure

DECLARE @historical xml
,@current xml
,@errorDescription varchar(max)
SELECT @historical = ‘<pasteHistoricalXMLHere/>’

SELECT @current = ‘<pasteCurrentXMLHere/>’

EXEC Custom_834_Business_Rules @historical, @current, @errorDescription out

SELECT @errorDescription

Handling Business Rule Execution Results

The business rules are simple to work through – but you must determine what you want to do with the results. For example, if you have an inbound 834 that has one subscriber who has no change to the data, and three dependents (one add, one change, and one reinstate), how do you handle these records?

One approach is to write to a series of tables in SQL at the end of the stored procedure, and let further processing occur to move this data into the target environment. Another approach is to return an XML document to the original calling orchestration so that it can continue to process things as needed. Figure 8-2 illustrates the flow to several potential options around the continued processing of this data.

Figure 8-2. Potential Options for Processing After Business Rule Validations

The goal is to ensure that you are doing things with the least amount of code, and in the most efficient way possible. Obviously, you will need to have a strategy before you ever begin your business rules architecture. Much will depend on how your internal data is structured, and how your business needs to deal with the various entities.

Calling the Stored Procedure from the Orchestration

Calling the stored procedure is done using a C# method in a .NET helper class, referenced by the orchestration. There is no need to use any type of adapter, as adapters just add complexity and overhead to most solutions. Listing 8-12 shows the basic code for calling the stored procedure, passing in two parameters of type XML, and getting back an error message.

Listing 8-12. C# Method Called from Orchestration to Execute Stored Procedure

public void callBusinessRules(string historicalXML, string currentXML, out
string strErrorDescription)
{
currentXML = currentXML.Replace("encoding=\"utf-8\"", "");
string connection =
System.Configuration.ConfigurationManager.AppSettings["ConnectionStringKey"];
string sproc = "Custom_834_Business_Rules";

SqlConnection sqlConnection = new SqlConnection(connection);
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(sproc, sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameterHistXML = new SqlParameter();
parameterHistXML.Direction = ParameterDirection.Input;
parameterHistXML.ParameterName = "@historical";
parameterHistXML.SqlDbType = SqlDbType.Xml;
parameterHistXML.Value = historicalXML;
sqlCommand.Parameters.Add(parameterHistXML);

SqlParameter parameterCurrXML = new SqlParameter();
parameterCurrXML.Direction = ParameterDirection.Input;
parameterCurrXML.ParameterName = "@current";
parameterCurrXML.SqlDbType = SqlDbType.Xml;
parameterCurrXML.Value = currentXML;
sqlCommand.Parameters.Add(parameterCurrXML);

SqlParameter parameterErrorDesc = new SqlParameter();
parameterErrorDesc.Direction = ParameterDirection.Output;
parameterErrorDesc.DbType = DbType.String;
parameterErrorDesc.Size = -1;
parameterErrorDesc.ParameterName = "@errorDescription";
sqlCommand.Parameters.Add(parameterErrorDesc);

SqlDataReader reader = sqlCommand.ExecuteReader();

strErrorDescription =
sqlCommand.Parameters["@errorDescription"].Value.ToString();

sqlConnection.Close();
}

Conclusion

The purpose of this chapter was to walk through an example of implementing an easy to maintain, highly customizable approach to business rule management that is common to inbound 834 implementations. By using a SQL Server stored procedure, and housing all of the rules in SQL, the rules engine becomes a tool that can be developed and modified by virtually any resource – most developers know T-SQL. It also is something that can be tested and changed on the fly, with no reliance on compiled or deployed code. If a rule needs to change after the solution has been deployed to production, it is as easy as modifying a procedure. Looking at a highly maintainable, easy to use business rule engine will be a relief, compared with looking at the advanced mapping for outbound 834 data that is required for many trading partner communications – this topic is covered in the next chapter.