The client that I am working at wanted email notification when incoming HIPAA files were processed. I was able to put this in place. The only issue is that when files would not validate, all I could do was to wait and then finally send out an email stating that the file did not validate in time. This would immediately spin up calls asking me why it did not validate, and my love of looking around for the TA1 and 997 and then interpreting it to the business analyst was not my favorite thing to do especially since I knew that a more human readable version was available. I finally had enough time to look into this. I had the Sender Qualifier (ISA05) Sender Identifier (ISA06) and the control number (ISA13). I finally determined the relationship between the audin table and the the errors table. I eventually determined that I needed the errtxt table also. I created the following stored procedure to capture the description and details as long as you provide the above mentioned values.
Again, this will work for both the BizTalkEDIDb and the BizTalk_HIPAAEDIDb as the table structures are the same.
CREATE PROCEDURE dbo.captureDetails
@senderQual varchar(2),
@senderId varchar(15),
@controlNumber varchar(9)
AS
SELECT errtxt.descrp AS description, errors.descrp1 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
*As a note, if a duplicate file (duplicate ISA13) is sentthis stored procedure will return all of those records, the first one, and then the subsequent duplicated files, if you wanted to, you could capture the date from the audin table, but then it becomes a matter of tolerance on how long you want to go back.