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

Chapter 3. Solution: Sending 837P Data

The previous chapter looked at inbound 837P data. This chapter will look at outbound 837P data. It will introduce how to pull data from a source SQL Server database, map that data to an outbound 837P document, and batch the results into one file with multiple claims per ST/SE. There will be important information presented about how best to interact with SQL from BizTalk, how to structure the source data you'll be mapping from, and how to batch the data using configurations available in the BizTalk Party Agreement. The architectural overview of this solution is shown in Figure 3-1.

Figure 3-1. Outbound 837P Solution Overview

It is important to note here the pattern for batching. First, the data will be retrieved by a SQL Adapter, and then be sent out to a file directory by a Send Port in the 837P format. Over the course of a day, the SQL Adapter will run every hour, and during that hour all claims that may have stacked up will be retrieved and written out to a single 837P file. BizTalk batching will be set to trigger once a day, which means there could be up to 24 individual 837P files that will be written to the file directory and picked up by the batching Receive Port. When the BizTalk batch triggers, the 24 individual 837P files will be combined into a single 837P file with 24 ST/SE segments, each containing one or more records.

Note There are a wide variety of batching patterns that may be required and that can be built and configured in BizTalk with varying degrees of difficulty. This solution will demonstrate how to create one batched 837P file with multiple ST/SE segments containing one or more claim records.

Visual Studio Solution

As discussed in the previous chapter, namespaces and project structure are essential to a successful project, so always take time in the beginning to think through all of the components that will be required and how best to name and organize them. Trying to change namespaces and project organization later in the development cycle is especially frustrating with BizTalk, given the number of components and complexity of testing. For this solution, the following Visual Studio projects and namespaces will be used:

· Solution Name: Company.BizTalk. You can use the same solution you used for the project in Chapter 2.

· Schemas. There are two schemas that will be used in this solution, as follows:

o The 837P Schema which will be contained in its own project. This project was created in Chapter 2, and is called Company.BizTalk.Schemas.X837P

o The schema that matches the source SQL data result set. This project’s name will be Company.BizTalk.Schemas.SQLData.

· Maps. The map project will contain all maps and XSLT required by the solution, and will have a namespace of Company.BizTalk.Maps.X837.Outbound

The Schema Projects

Two schemas are required for this project. The first is the 837P 5010 schema that ships with BizTalk, which was discussed in Chapter 2, “Solution: Receiving 837P Data”. The second schema project will contain the schema which matches how data will be pulled from SQL Server. This is a structure that you have complete control over, and therefore should match as closely the structure of the outbound 837P schema as is possible.

The closer you are able to match the structure and hierarchy, the less complicated the mapping will be. You are not required to make it look the same, but the less effort you spend on creating a solid source structure that patterns your claim data properly, the more effort you will spend in mapping and creating code to get this claim data into the target EDI document.

In order to be able to define the schema for the source data coming from SQL, you'll want to work through the creation of a result set. The approach that allows for the most extensive flexibility around retrieving data, performing some level of data validation or cleansing, and formatting it in a “BizTalk friendly” way is through the use of a stored procedure.

If you have not used XML within SQL Server before, you are going to want to learn, as it is essential to building solid, easy to maintain solutions within BizTalk. The stored procedure that you will be using for this current solution is structured in the following way:

· Retrieve data directly from databases and structure it in a mapping ready format.

· Use FOR XML PATH to give a specific structure to the data and return the result set in XML.

· Use WITH XMLNAMESPACES to ensure it has a unique namespace and contains namespace prefixes that are immediately useable by a BizTalk Schema.

By using a stored procedure, you have endless options on structuring and returning data. You can use a single stored procedure to return data for all outbound trading partners, you can limit which trading partner is pulled at any given time through a parameter (or set of parameters), and you can apply specific logic to individual fields in order to prepare them for being delivered in the outbound 837P. An example of a stored procedure that can pull results in the XML format required by BizTalk is shown in Listing 3-1.

The actual structure of the data shown here is only to demonstrate how to write the procedure. You will want a much more robust and complex structure to the source XML which matches the requirements of what you will in order to successfully create the outbound 837P document.

Listing 3-1. An Example of a Stored Procedure to Return XML

CREATE PROCEDURE [dbo].[RetrieveClaimData]
-- namespace will be used within Schema and should match your pattern
WITH XMLNAMESPACES('http://Company.BizTalk.SQLSource' as "ns0")
-- top level is set to NULL
SELECT NULL
,(SELECT c.Type As [ns0:ClaimType]
,c.Number As [ns0:ClaimNumber]
,c.ServiceDate As [ns0:ServiceDate]
,(SELECT p.ProviderFirstName As [ns0:FName]
,p.ProviderLastName As [ns0:LName]
,p.ProviderID As [ns0:BDate]
FROM Providers p
WHERE p.ID = c.ProviderID
FOR XML PATH('ns0:Provider'), TYPE)
FROM SourceClaimInformation c
FOR XML PATH('ns0:Claim'), TYPE)
FOR XML PATH('ns0:SQLSourceClaims'), TYPE
END

Running this stored procedure from SQL Enterprise Manager will produce an XML document that can be used to generate your BizTalk schema, or you can create both manually. The creation of this stored procedure and the structuring of the outbound data should be the most difficult task that you have - not in development, but in business analysis and testing. If you get the structure right here, you will save a tremendous amount of time in the actual mapping and testing of data within BizTalk.

Once you have the source XML and schema structure worked out, you'll need to add it to a Visual Studio project. For this solution, it will be called Company.BizTalk.Schemas.SQLData.

The Map Project

You will have a single map for the outbound process, which will map the source data returned by SQL Server to the target 837P structure. Details for mapping the 837P are given in Chapter 4, “Mapping Data”.

Note If you are delivering multiple document types - such as 837 Professional, Institutional, and Dental - out to multiple trading partners, you might want to explore the idea of mapping the source data from SQL Server to a canonical structure, and the canonical structure to the target 837P. In some cases, you can reduce overall long-term development through the use of a canonical schema, though short term it will increase development efforts and testing requirements.

The map project structure should be as follows:

· Create a new project in Visual Studio called Company.BizTalk.Maps.X837.Outbound

· Add a reference to the two schema projects you have created, as you will be mapping from the source SQL data to the 837P schema

Setting up the BizTalk Components

There are fewer Visual Studio components in this outbound solution, but there is more that must be configured within BizTalk, especially around the batch settings in the Party Agreement. You will need to set up a SQL Receive Port to pull the data from the stored procedure, a File Send Port to write this data out as an individual 837P, another Receive Port to pick it back up for batching, and a second Send Port to send the batched data when the BizTalk batch fires.

Before configuring these components, you will want to deploy your Visual Studio assemblies to the BizTalk Application. Steps for doing this are outlined in Chapter 2.

SQL Receive Port

The stored procedure that you created earlier in this chapter returns XML. Now you need to set up the Receive Port to pull this data into BizTalk. In general, interacting with SQL Server from BizTalk is best done through custom .NET components - the use of the SQL Adapters generally cause bloated, difficult to maintain solutions. However, in the case of pulling XML data from a stored procedure, you can use the SQL Adapter as a simple pass through mechanism that can be schedule to run on a periodic basis. You won't have to create any of the additional schemas that generally come with using the SQL adapter to retrieve data. The steps for setting up the SQL Receive Port and Receive Location are as follows (see Figure 3-2):

· Create a Receive Port called Company.BizTalk.Receive.SourceSQLXML.

· Add a Receive Location to this port and call it the same thing as the Receive Port. Make it of type SQL. Click on Configure next to the SQL Type, and set the following properties:

o Poll While Data Found should be False

o Polling Interval and Polling Unit of Measure should be values that represent how often you want to pull the data from SQL.

o Connection String is the connection to the database where you stored procedure sits.

o Document Root Element Name is a wrapped element that is used ONLY to allow for the adapter to pull the results. It will need to be the root node of your schema that you have created for the stored procedure results.

Note The schema in Company.BizTalk.Schemas.SQLData should match exactly the structure of the XML returned by the stored procedure, except in that the root node must match what you set in the Document Root Element Name property. So, if the root node of your XML result set from the stored procedure is SQLSourceClaims, and the Document Root Element Name property is SQLRoot, then your schema will need to be SQLRoot/SQLSourceClaims. SQLRoot is just a wrapper node.

o Document Target Namespace should match the namespace declared in your stored procedure and schema. In this case, it should be set to http://Company.BizTalk.SQLSource

o The SQL Command is the full execute statement that you would use in SQL Enterprise Manager to run your stored procedure. If you have a parameter, you can specify it here. For example, to call the stored procedure that was created, this property would be set to “exec RetrieveClaimData”. Or, if you had a parameter specifying the Trading Partner name to query, you could hard code it here as “exec RetrieveClaimData ‘TPName’”. In the case of a hardcoded parameter, you would want to create one SQL Receive Port per trading partner.

o The URI can be set to anything, as long as it is unique. Something descriptive can be helpful, such as SQL://SOURCEDB/EDI/TradingPartnerName.

· Set the Receive Pipeline on the Receive Location to XMLReceive.

Figure 3-2. The Configured SQL Receive Settings

Once you have the SQL Receive Port fully configured, you can enable it and immediately see data arriving on the BizTalk Message Box (assuming there are results available). You may not have a subscriber set up yet, so the data will suspend, but you'll know your data is being retrieved successfully.

File Send Port

The File Send Port is set up to push out a single 837P file whenever data comes in on the SQL adapter. This means that if the SQL Adapter is set up to trigger every 15 minutes, a file could be written every 15 minutes (assuming there is data returned). This File Send Port should be set up as follows:

· Create a Send Port called Company.BizTalk.Send.TradingPartnerName.X837P.Unbatched.

· Set the Send Pipeline to EdiSend.

· Set the Type to File and write this file to a directory on your computer.

· On the Outbound Maps tab, select the map you created in Company.BizTalk.Maps.X837.Outbound. The source and target document schemas should automatically be selected when you choose the map.

· On the Filters tab, set the following property: BTS.ReceivePortName == Company.BizTalk.Receive.SourceSQLXML

Starting this Send Port will now allow you to test that every time the SQL Receive Port returns data it get successfully mapped and transformed into an individual ST/SE 837P document. The actual batching of the data will happen in the following components.

File Receive Port

The second receive port in this pattern is the File Receive Port which picks up the individual 837P documents that are getting created each time the SQL Receive Port executes and returns data. When this File Receive Port picks up the data, the batching settings on the BizTalk Party Agreement will cause the document to queue up and not be delivered until the batch executes. Setting up this Receive Port can be done using the following steps:

· Create a new Receive Port and Receive Location combination, both named Company.BizTalk.Receive.TradingPartner.ToBeBatched.

· Set the Type to File and point it to the directory where the File Send Port is writing out the individual 837P files.

· Set the Receive Pipeline to EdiReceive.

Note There is no requirement that you design your batching outbound flow using this exact pattern of Send and Receive Ports. It is possible to batch without ever physically writing out the individual 837P documents to a file directory. However, by splitting the steps into smaller pieces, you'll find that development, testing, and troubleshooting are greatly simplified, since you have access to the document at all stages of the cycle.

SFTP Send Port

The final Port that will be created is the SFTP Send Port that delivers the batched data out to the trading partner. This Port needs to be configured in conjunction with the Batch configuration on the BizTalk Party - it references properties set in the configuration, and the configuration references this Send Port. The Send Port is covered first in this chapter, but you may want to develop them both at the same time using two BizTalk Administration Console windows.

The SFTP Send Port listens directly to the BizTalk Message Box for the batched 837P EDI file that was created when the Agreement's Batch triggered it (see the next section for details on this configuration). The Send Port uses several filters, and can be configured using the following steps:

· Create a new Send Port named Company.BizTalk.Receive.TradingPartner.Batched.

· Set the Type to SFTP. Details for configuring the SFTP adapter are given in Chapter 5, “Adapters, AS2 and Acks”.

· Set the Send Pipeline to EdiSend, or to a custom pipeline that replaces the GS08 version with the appropriate value.

Note The GS08 and batching 837P 5010 documents has proved interesting. You may find that your data always fails batching due to a generic error. If you are certain that your configuration is correct, check the version in the GS08 on your outbound individual EDI document written by the File Send Port. If it is 005010X222A1, you may find that you need to set it to 005010X222 in the original outbound File Send and replace it with the correct value of 005010X222A1 in a custom pipeline on the final outbound SFTP Send Port. This is likely a bug in BizTalk Server batching, and may be corrected with a future patch.

· On the Filters tab, set the following filters:

o EDI.ToBeBatched == false

o EDI.BatchName == TradingPartner_Professional [should match the batch name configured in the Agreement]

o EDI.DestinationPartyName == TradingPartner [should match the value of DestinationPartyName on the Identifiers tab of the BizTalk Agreement]

The final two bullets listed above are critical to the success of your batching. It can get very complicated when dealing with multiple document types across multiple parties that have various batch configurations. In the case of the solution at hand, the DestinationPartyName property on the Identifiers tab in the Agreement that contains the Batch settings is only set for batching purposes. You will not have set this property unless you are configuring batching. The values for both can be anything that you want them to be; they just have to match exactly with one another.

Batch Settings in the Party Agreement

In the previous chapter, you looked at configuring the base settings for the BizTalk Party and Agreement on inbound data. You don't need to configure any new parties or Agreements for the outbound solution, but you will need to configure these same settings on the tab that represents the outbound data, from the Company party to the Trading Partner party.

Once you have the envelope settings and various core settings configured (which should match almost exactly what you have for the inbound settings), you can look at configuring the actual batch - which is unique to this outbound solution. In order to configure the batch, open the Agreement you created in Chapter 2 and take these steps:

· On the Identifiers tab of the Agreement, set the DestinationPartyName to TradingPartner. This needs to be a unique value across all of your trading partners.

· On the Batch Configuration tab of the Agreement, click the New Batch button.

· Set the Batch name property to TradingPartner_Professional.

· Set the Batch filter property with the following filters:

o BTS.ReceivePortName == Company.BizTalk.Receive.TradingPartner.ToBeBatched [this should match the name of the File Receive Port you created earlier in this chapter]

o EDI.ST01 != 997

· Use the Scheduler to configure the appropriate outbound schedule. In the case of this solution, the batch should go out every 24 hours or once a day at a specific time. You can set this to whatever is required for your solution. When the specified time comes, if anything has queued up since the last time the batch executed then it will be immediately released.

Note During development and testing, you will find that clicking the Override button on the Batch will be of great help. It forces a batch to be produced as soon as it is clicked.

· Click Start to allow the batch to begin processing. You may have to click OK or Apply and reopen the Agreement in order to get the batch to fully start. It can take several minutes the first time to get started.

· Check the running instances in your BizTalk Group Hub reports to ensure that your batch is started - you will see an orchestration running for every batch that you have started.

· Click on the Send Ports tab of the Agreement and select the SFTP Send Port you created earlier in this chapter - Company.BizTalk.Receive.TradingPartner.Batched.

· Click OK to save all of your settings.

Note Getting the batch and Send Port to align with one another may take several stops and starts of the batch and of the BizTalk Host Instance. The batch functionality is pretty impressive, and it works great once it is running, but it can take some patience to get it right when you are first setting it up.

Enabling and Running the Solution

In order for this solution to work, both of the Receive Locations must be enabled, both of the Send Ports must be started, and the BizTalk Batch on the Agreement must be running. You will want to restart the BizTalk Host Instance to ensure that all of the most recent configurations and components are loaded into memory.

Conclusion

This chapter covered some critical aspects of developing BizTalk EDI solutions within the health care space. Most notably batching data and structuring your outbound data in SQL Server to prepare it for mapping. You will likely find that the requirements for your specific implementation vary from the specific pattern outlined in this solution, but you should have more than enough information now to be able to architect an efficient and highly maintainable outbound solution.