Apr 032007
 

A recent request I got was for information regarding retrieving error informaiton in a more readable format:

We have several clients submitting valid X12 documents but recently we started receiving X12 from a client which is not conforming to the standard. For these messages BizTalk is throwing an error into eventlog and does nothing. Unless some one monitors the eventlog there is no way we could identify that a message failed. At the same time we want to report to client in a nice way that the message they sent us is invalid. Something similar to EDIFECS or FREDI. We cannot find a simpler way to do that. Do you have any information about how we can validate X12 and reject in BizTalk in a better way?

I actually have, what I have is an orchestration that picks up the file, interrogates the sender/reciever id and then drops the file off for the HIPAA accelerator to validate. It then queries the HIPAA database and extracts the information and sends out an email. It has a delay before it runs the stored procedure that is determined by the Xref table (around 10 minutes) and then if it does not receive the XML version in the correlation set, it runs the following logic.

Here is the stored procedure that is on the HIPAA_EDIDb

CREATE PROCEDURE dbo.captureDetails @senderQual varchar(2), @senderId varchar(15), @controlNumber varchar(9) AS SELECT errtxt.descrp AS description, errors.descrp1+errors.descrp2 AS details FROM audin INNER JOIN errors ON audin.icin = errors.msgnr INNER JOIN errtxt ON errors.etc = errtxt.etc WHERE (audin.sid = @senderId) AND (audin.icr = @controlNumber) AND (audin.sidcdq =@senderQual) AND (errors.inout = '2') for xml raw--, xmldata GO

Here is the schema that is created by this stored procedure:

 

<?xml version="1.0"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://captureErrors" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:annotation> <xs:appinfo> <msbtssql:sqlScript value="exec [captureDetails] @controlNumber=&quot; &quot;, @senderId=&quot; &quot;, @senderQual=&quot; &quot;" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" /> </xs:appinfo> </xs:annotation> <xs:element name="Request"> <xs:complexType> <xs:sequence> <xs:element name="captureDetails"> <xs:complexType> <xs:attribute name="controlNumber" type="xs:string" /> <xs:attribute name="senderId" type="xs:string" /> <xs:attribute name="senderQual" type="xs:string" /> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="Response"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" maxOccurs="unbounded" name="row" xmlns:q1="http://captureErrors" type="q1:rowType" /> </xs:sequence> </xs:complexType> </xs:element> <xs:complexType name="rowType"> <xs:attribute name="description" type="xs:string" /> <xs:attribute name="details" type="xs:string" /> </xs:complexType> </xs:schema>

 

I have a flat file schema

 

<?xml version="1.0" encoding="utf-16"?> <xs:schema xmlns:ns0="http://captureErrors" xmlns="http://notificationEmail.email" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://notificationEmail.email" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:import schemaLocation=".\errorinfo.xsd" namespace="http://captureErrors" /> <xs:annotation> <xs:appinfo> <b:schemaInfo count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" standard="Flat File" root_reference="email" /> <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" /> <b:references> <b:reference targetNamespace="http://captureErrors" /> </b:references> </xs:appinfo> </xs:annotation> <xs:element name="email"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_order="postfix" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" /> <b:properties> <b:property distinguished="true" xpath="/*[local-name()='email' and namespace-uri()='http://notificationEmail.email']/*[local-name()='FileName' and namespace-uri()='']/@*[local-name()='name' and namespace-uri()='']" /> <b:property distinguished="true" xpath="/*[local-name()='email' and namespace-uri()='http://notificationEmail.email']/*[local-name()='Count' and namespace-uri()='']/@*[local-name()='number' and namespace-uri()='']" /> </b:properties> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element name="Envelope"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Sender (ISA06:ISA05:GS02): " sequence_number="1" /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="id" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element name="ControlNumber"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" child_order="infix" child_delimiter_type="char" child_delimiter="," tag_name="Control Number: " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="ControlNo" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element name="Count"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="3" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Count: " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="number" type="xs:int"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element name="FileName"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="4" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Filename: " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:attribute name="name" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:attribute> </xs:complexType> </xs:element> <xs:element maxOccurs="1" name="Data"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="5" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="prefix" child_delimiter_type="hex" child_delimiter="0x0D 0x0A 0x0D 0x0A" /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element maxOccurs="unbounded" name="Details"> <xs:annotation> <xs:appinfo> <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="Error #" child_order="infix" child_delimiter_type="char" child_delimiter="= " sequence_number="1" /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element maxOccurs="1" name="Number" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:element> <xs:element maxOccurs="1" name="Information"> <xs:annotation> <xs:appinfo> <b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="infix" child_delimiter_type="char" child_delimiter=" " /> </xs:appinfo> </xs:annotation> <xs:complexType> <xs:sequence> <xs:annotation> <xs:appinfo> <b:groupInfo sequence_number="0" /> </xs:appinfo> </xs:annotation> <xs:element maxOccurs="1" name="Info" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="1" justification="left" /> </xs:appinfo> </xs:annotation> </xs:element> <xs:element maxOccurs="1" name="Detail" type="xs:string"> <xs:annotation> <xs:appinfo> <b:fieldInfo sequence_number="2" justification="left" /> </xs:appinfo> </xs:annotation> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>

 

The orchestration runs and calls the stored procedure, maps the data, determines who gets it (based on a role link) and send out the email.

The email’s contents look like this:

Sender (ISA06:ISA05:GS02): 1234:ZZ:1234

Control Number: 166

Count: 0

Filename: c:\import\8371234564789.txt Error #1= The check condition has not been met. Contact the sender.; source format: [5 00401 ,X12-4010]

source document: [837 004010DEFAULT X X098A1,Health Care Claim: Professional]

source segment: [data#9945,def#22,tag=NM1 ,name=TS837Q1_2010BA_NM1_SubscriberName_A1>DEFAULT]

source element: [def#9,elm#9,comp#0,name=TS837Q1_2010BA_NM109__SubscriberPrimaryIdentifier_A1>DEFAULT],

value: []

undefined!

(((TS837Q1_2010BA_NM108__IdentificationCodeQualifier_A1>DEFAULT == [] AND TS837Q1_2010BA_NM109__SubscriberPrimaryIdentifier_A1>DEFAULT == [] ) OR

You have to be using the HIPAA receive location, and not the pipeline component, for reason I have blogged about earlier.