Mapping an 837P with an HL Looping Structure

Mapping an 837 can appear like a daunting task, especially when dealing with a large schema and the hierarchical looping that is associated with it. However, if you know some XSLT, you are already ahead of the game. In this example we will a proprietary XML format to an 837P and focus on the HL Looping structure.

You will notice the lack of mapping lines in this example. This is due to the fact that we are only mapping the header section with the BizTalk Mapper, and most of the functionality is rolled into custom scripting functoids.

In order to create our HL Looping we need a few things:

  • Variable to hold our counter
  • Function to increment our counter
  • Function to return our counter
  • Variable to hold our billing provider HL01(XSLT)
  • Variable to hold our subscriber HL01(XSLT)

These can all be located in one custom C# scripting functoid:

Billing provider HL01:

The first HL Loop is the billing provider. This is the easiest component.

Then we just call the GetHL01 function and hard code the rest.

Next up is the Subscriber loop:

XSLT breakdown:

  1. Incrementing our HL01 value
  2. Setting the parent ID number which for Subscriber is the Billing HL01
  3. Hard coding the HL03 value, 22 for subscriber
  4. Testing if there is a patient or not.
    1. In our proprietary format there is a patient flag, but if there is not a flag, you can simply test if there is a patient subordinate to the subscriber.
  5. Setting the current HL01 for the subscriber

Last is the Patient Loop:

XSLT breakdown:

  1. Increment the HL01 value
  2. Set the Parent ID which is ‘Subscriber’ for Patient
  3. Hard Code HL03 and HL04 Values

Invalid Character Errors When Stepping up 834 EDI Document

When stepping up the 4010 834 Document to 5010 834, one requirement is for the outgoing message to have a “^” for the ISA 11 segment. Normally we would select the “Repetition Separator” radio button in the Agreement Properties for this to be added in the outgoing envelope.

In the 4010 834 the ISA 11 uses a “U” for the repetition separator. However, when we tested and the message was sent through, we received invalid character errors. I checked all the segments that were containing errors, and I did not find any invalid characters.

In the EDI document I was using, all the segments were in caps. After playing around with the data, I noticed I did not receive errors when the data was in lowercase. On a hunch, I then set all the lowercase u’s to caps, and the errors went away.

As the final step, I created a simple send pipeline component that sets the repetition separator from a “U” to a “^”, and set the envelope setting to use “Standard Identifier”. With this pipeline in place, the message processed without errors.

Matching SSN to Member ID on 834 EDI Document with Exception Handling

Recently a client submitted the requirement of matching an incoming SSN to a Member ID on a 834 EDI document. They also requested we create a text file to report any members that did not have a match. Our solution was to create an orchestration to handle all of the requirements.

The first task was to create a map for the source 834 to the destination schema. Since this is not the focus of this blog post, I will not go into any details of the mapping, but focus on the requirement in question.

In the map we needed to do the matching of the SSN to the Member ID. This was handled by using an external assembly that calls a stored procedure. The stored procedure does the entire member matching and inserts pertinent data on the member that does not have a member id match. In our scenario, if a match exists, we return the member ID. If a match does not exist, we return ‘no match’, and insert the member’s SSN and Birthdate into a table that contains non-matches.

For the . txt file output we also used an external assembly to call a stored procedure to create our file. This stored procedure accepts 1 input of our filename and uses the “bcp” utility http://msdn.microsoft.com/en-us/library/ms162802.aspx to copy our table information into a .txt file.

Our orchestration ended up looking something like this:

EDI Orchestration

Performing Database Lookups

One common task in a BizTalk map is performing some type of database lookup. While there are several database functoids available for this, quite often the easiest (and most appropriate) way is to create an external assembly to do the lookup.  An external assembly allows for the full power of the .NET framework and the flexibility to test and develop outside of the BizTalk platform.

An example of an external assembly for lookup is shown below.  This method accepts two input parameters:

  • The first is the value to pass to the stored procedure
  • The second is the connection string to the database.  The  connection string is added as an input so that the value can be passed to the map for testing. In production, a blank value can be passed and the connection string will be set through the BizTalk configuration file.

The method creates the necessary SQL objects to create a sqlcommand, add parameters, connect to the database and retrieve a scalar result.

Once the code is complete and tested, sign the assembly, build it and add a reference to it in the mapping project.  The code can then be called from a map using a custom Scripting  functoid.  Select External Assembly, the correct class name, and the appropriate method – as shown below:

The input parameters can be added as shown here (for the connection string either add the data source or add a blank input so that the setting in the BizTalk configuration file can be used).

Interpreting Validation Errors

When EDI documents arrive in BizTalk, they are validated against the party settings and the schema associated with the document type received.  If the document cannot be validated, the specific error will be caught in BizTalk, and made available through the suspended messages reporting area and in the Windows Event log.  An example of an error is shown in the following screen shot – this states that the error is in position 32.  This position corresponds to the actual XML segment location in the corresponding XML document (all EDI documents are handled as XML within BizTalk.

Here is the corresponding XML: