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

Chapter 9. Advanced 834 Mapping

Depending on what trading partner you are integrating with, and what the requirements are of your integration, you may find that there are some complexities to the architecture required to deliver a fully functional solution. One such pattern that is very common, yet can be difficult to build out, is related to the outbound 834 maintenance (or effectuation/reconciliation). The pattern here is that the outbound 834 data must contain all of the original inbound 834 information related to a specific subscriber/dependent along with all of the updated information from an internal system. The complexity here lies in the fact that in general, the inbound 834 data is not mapped in its entirety to a backend system within an organization. There are aspects to the data that are irrelevant to internal processes, and often have no place to be stored. In this case, the original 834 data must be stored so that it can be referenced at a later time when the outbound data ships out.

Figure 9-1 gives a high-level overview of what this architecture looks like, and this chapter will describe the components used, the most critical of which is the outbound 834 map. This map is probably unlike any map you’ve done in the past. It allows for multiple lookups and handles looping – items that are traditionally relegated to orchestrations or other components. By handling it in the map, the outbound flow of data can rely on a simple base of components, and will allow for grouping and batching of data based on trading partner requirements.

Figure 9-1. High-Level Overview of Data Flow

Inbound Components

Chapters 6, 7, and 8 have gone into detail about various components for inbound processing. The only thing that deserves mention about the architecture here is the writing of the 837 XML to a database table. The purpose of writing the individually split XML documents to a table is to ensure that this original data can be referenced and pulled from the outbound map. In this case, you will need to add a unique identifier to the row where the XML is written, so that it can be easily cross referenced from the outbound data. For example, if SSN is a unique identifier which will be available on all records, you could use that. More than likely, it will need to be a combination of the group policy number and the member number, which you will need to pull out of the XML data itself in order to capture it in its own fields in the table. There may be some cases where you will have no unique field within the data, and you’ll have to create a unique identifier (GUID) and store it not only in this lookup/archive table, but also in your target data repository, so that it can be sent out and used to repopulate the data on the outbound portion of the process.

The table for the inbound data which houses the 834 XML should look similar to that shown in Figure 9-2. An example of writing this data to a table using C# and a stored procedure is shown in Listings 9-1 and 9-2.

Figure 9-2. Inbound 834 XML Lookup Table Structure

Listing 9-1. Stored Procedure to Archive 834 XML for Lookup

CREATE PROCEDURE ArchiveEDI
-- you can pass your XML as a varchar or as xml.
-- If you want to do pre-processing on the string
-- such as stripping out characters or doing string replaces,
-- then you may want to pass in as string
@UniqueLookupId as uniqueidentifier,
@FileName as nvarchar(max),
@XML as nvarchar(max),

AS
BEGIN
SET NOCOUNT ON;

-- do REPLACE operations here, perhaps on namespaces, ns0:, etc.
INSERT INTO [X834_Archived]
(ID
,UniqueLookupID
,834_XML
,OriginalFileName
,CreateDate
)
VALUES
(NEWID()
,@UniqueLookupID
,CAST(@XML as xml)
,@FileName
,GETDATE()
)
END

Listing 9-2. C# Called from Orchestration/Pipeline to Call Stored Procedure

public void ArchiveEdi(string fileName, string xml, string uniqueLookupID)
{
string strConnection =
System.Configuration.ConfigurationManager.AppSettings["EDI.Database"];
string sproc = "ArchiveEDI";

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

SqlParameter parameterID = new SqlParameter();
parameterID.Direction = ParameterDirection.Input;
parameterID.ParameterName = "@UniqueLookupId";
parameterID.Value = new Guid(uniqueLookupID);
sqlCommand.Parameters.Add(parameterUniqueLookupID);

SqlParameter parameterFileName = new SqlParameter();
parameterFileName.Direction = ParameterDirection.Input;
parameterFileName.ParameterName = "@FileName";
parameterFileName.Value = fileName;
sqlCommand.Parameters.Add(parameterFileName);

SqlParameter parameterXML = new SqlParameter();
parameterXML.Direction = ParameterDirection.Input;
parameterXML.ParameterName = "@XML";
parameterXML.Value = xml;
sqlCommand.Parameters.Add(parameterXML);

sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}

Outbound Components

The outbound components for this solution include the stored procedure or text file that consists of the data making up the source enrollment information (which represents the internal data stored within your company’s systems), the map which maps the internal data to the target 834 schema and also does the lookup of data from the original 834 inbound archived data, and the standard BizTalk artifacts (party configurations, receive and send ports, etc.).

Source Data

The source data can come in a variety of forms, including database result set, flat file, and XML file. Your ideal scenario is to get the data into an XML format prior to delivering to BizTalk. Pulling an XML formatted result set from the database has been discussed in Chapter 3“Solution: Sending 837P Data”, and is a critical component for simple architectures. For example, if you can retrieve data in XML, you can often eliminate orchestrations from your solution entirely. In the case of the current solution, we are pulling data from two sources – the data that is coming in and represents the outbound enrollment data and also the original inbound 834 that contains the remainder of the required 834 information. Though there are two documents and multiple database calls, there is no need for an orchestration. Given the complexity of the mapping, eliminating other components allows you (the developer) to focus your energies entirely on mapping and not on building unnecessary components.

Note One common approach to pulling data in XML from a database and rendering it in a file format for ease of pickup from BizTalk is to create an SSIS process. This process formats the data and writes it to a file on a directory where BizTalk is listening using a standard File Receive location.

Listing 9-3 shows an example of outbound enrollment data that represents the source data which can come from either a file drop or an XML query from a database. This data contains all of the information that the internal system stored related to enrollment information, but it does not contain all of the information required in the mapping of the outbound 834 data. The remainder of this data will come from the original archived 834 data which was written as shown above in Figure 9-1, and would be looked up using the UniqueLookupID in the XML (this could also be a combination of standard fields like SSN, member ID, name, etc.).

Listing 9-3. Sample Outbound Enrollment Data

<Enrollments>
<Enrollment>
<TargetTradingPartnerID>{name of destination party in BizTalk parties}
</TargetTradingPartnerID>
<Subscriber>
<UniqueLookupID>{this matches what is in Figure 9-1}</UniqueLookupID>
<MemberID>1112223333</MemberID>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
<Dependents>
<Dependent>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
...

The Map

The map, which consists of the source data schema and the target 834 XSD, will consist of two functoids. The first functoid is a global C# Inline Scripting functoid and the second is an XSLT Inline Scripting functoid (see Chapter 4 “Mapping Data” for details around both of these concepts). The XSLT creates the target schema line by line, and uses C# to retrieve the data, format the data, and determine whether the target data values come from the source data or from the original 834 lookup data. Figure 9-3 shows the map.

Figure 9-3. The Map with All Functoids

The XSLT Scripting functoid is where most of the code for this mapping solution is at. For nodes that must be mapped in the target 834, and for which data exists in the source data schema (Enrollments, on the left side of the map in Figure 9-3), data can be mapped using standard XSLT. For nodes that are not in the source data, but must be looked up in the original 834 archived XML, data can be mapped using the $original834Enrollment parameter and the GetNode inline C# method. Listing 9-4 shows sample XSLT for mapping some of the member information in the 834 using data from both the source schema and the original 834 lookup data. Listing 9-5 shows the GetNode method, which allows for a source document along with the specific XPath to the node to be passed in and returns the string value (if the node is found).

Listing 9-4. Populating the Member Information in the 834

<xsl:element name ="TS834_2100A_Loop">
<xsl:element name ="NM1_MemberName">
<NM101_EntityIdentifierCode>
<xsl:value-of select="userCSharp:GetNode(
$original834Enrollment,'//*/*/*/*[local-
name()="NM101_EntityIdentifierCode"]')"/>
</NM101_EntityIdentifierCode>
<NM102_EntityTypeQualifier>
<xsl:value-of select="userCSharp:GetNode(
$original834Enrollment,'//*/*/*/*[local-
name()="NM102_EntityTypeQualifier"]')"/>
</NM102_EntityTypeQualifier>
<NM103_MemberLastName>
<xsl:value-of select ="./*/*[local-name()='LastName']"/>
</NM103_MemberLastName>
<xsl:if test ="./*/*[local-name()='FirstName']">
<NM104_MemberFirstName>
<xsl:value-of select ="./*/*[local-name()='FirstName']"/>
</NM104_MemberFirstName>
</xsl:if>

Listing 9-5. The GetNode Inline C# Function

public string GetNode(string xml ,string xpath)
{
string result ="";
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.LoadXml(xml.Replace("ns0:", ""));
System.Xml.XmlNode node;
System.Xml.XmlElement root = xmlDoc.DocumentElement;
node = root.SelectSingleNode(path);

if (node!=null)
{
result = node.InnerXml;
}

return result;
}

Listing 9-4 uses the $original834Enrollment parameter, which is populated in the Inline XSLT through the code shown in Listing 9-6. It uses a C# method in the BizTalk helper assembly (rather than in the Inline C#) to do this, as it is a database lookup that is performed. Referencing a .NET assembly method from Inline XSLT requires two additional steps – the first is the creation of an XML document (referenced in your Visual Studio file) that is an Extension Object which tells the XSLT where to find the assembly (an example of this is shown inListing 9-7). This XML file must then be referenced in the map properties in the Custom Extension XML property, as shown in Figure 9-4.

Listing 9-6. Retrieving the Original 834 XML in XSLT

<xsl:variable name="original834Enrollment"
xmlns:CompanyNS="http://companyname.com/xslt/extensions"
select="CompanyNS:getEdiMessage($lookup,'Data Source=MDB;Initial Catalog=CompanyLookup;User Id=BTService;
Password=123pass')" />

Note The PublicKeyToken in Listing 9-7 should match the key on your assembly. You can get this value from the properties in the GAC.

Listing 9-7. Custom Extension XML

(for Referencing External Assembly)
<ExtensionObjects>
<ExtensionObject Namespace="http://companyname.com/xslt/extensions"
AssemblyName="Company.BizTalk.Utilities, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=eeee12345678b123" ClassName="Company.BizTalk.Utilities.Helper"
/>
</ExtensionObjects>

Figure 9-4. Referencing the Custom Extension XML

Note Click on any white space on the map surface in order to access the properties where the Customer Extension XML property can be set.

The C# code used in the external assembly to do the original lookup of the 834 XML is shown in Listing 9-8.

Listing 9-8. External Assembly XML Lookup

public static string GetEDIMessage(string uniqueLookupID)
{
string result = string.Empty;
string sproc = "GetOriginal834";
string connectionString = //pull this from the config file or other location

using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sproc;
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();

SqlParameter paramReferenceNumber = new SqlParameter();
paramReferenceNumber.ParameterName = "@UniqueLookupID";
paramReferenceNumber.Direction = ParameterDirection.Input;
paramReferenceNumber.Value = uniqueLookupID;
cmd.Parameters.Add(paramReferenceNumber);
var scalar = cmd.ExecuteScalar();
result = scalar as string;
}
}
return result;
}

Ports/Party Configuration/Etc.

It is possible to create an outbound 834 solution that does not use an orchestration, and relies solely on ports, pipelines, and maps. The solution outlined in this chapter can take this approach, as long as the source data is delivered in a file drop (or similar) format in XML. In this case, it is a receive port/location that picks up the XML, along with a map that transforms the source XML into an EDI 834 document. Configuring the trading partner’s agreement and party settings in BizTalk, along with send ports and filters, will determine how the data is routed and processed. There are a number of examples of routing outbound data, shown in Chapters 10 and 11.

Conclusion

A complex architectural requirement – using archived historical data and source data to create a target 834 without the use of an orchestration – has been outlined in this solution. The advanced mapping required to handle this scenario takes some forethought, and relies heavily on XSLT, and requires integration with C# Inline scripts and an external C# assembly, but allows for substantial freedom in development. Being able to use these advanced mapping techniques will come in handy as you work through receiving and sending your 834 data. The next two chapters will focus on patterns for sending this data, and you can determine how best to incorporate your mapping into these solutions.