Mar 302007
 

A while ago I taught a Brown Bag event for Microsoft about the Cross Referencing I have been blogging about recently.

You can view the presentation here. I will be the first one to admit, vcialis 40mg it was not that good, health there were some technical difficulties in getting it up and running and then my tweaking the night before caused me some on-air glitches.

Anyway – not the best presentation, but I got through it. I gave the same presentation at the Southern California Code Camp and it went much better.

Feb 122007
 

A few weeks ago, I spent quite a bit of time crafting the XML to load data into the databases. I have a tool that will do all of the heavy lifting. It is pretty self explanatory, you create a list of application types, instances where you associate it with the particular application type, create the IDXrefs and then create the elements. Here you choose the IDXref it’s associated Application Instance and the the CommonID and the AppID. Then to make sure you don’t over write another set of documents, you give it a base name, choose the folder, and the folder you want to save it in, and it will generate all of the files for you, including the setup.xml file. If there are duplicates on either the CommonID or AppID it will add a period to the end of the data.

Here are some screen shots of what it does:

Which results in the following in the xml files in the directory specified

C:\sample\example.listOfAppType.xml

C:\sample\example.listOfAppInstance.xml

C:\sample\example.listOfAppIDXRef.xml

C:\sample\example.listOfIDXRefData.xml

And the best part:

C:\sample\example.setup.xml

If you would like this application, click this:

Feb 072007
 

Tom did a good job of explaining the database structure and following up on this post, getting the data into the database is somewhat a challenging process.

So in putting together the import process, I was trying to get my head around how to create the various xml files to import. Since I am a visual guy, I put this little relationship diagram so I can easily relate my data elements to the example in the documentation xml files of listOfAppType.xml, listOfAppInstance.xml, listOfIDXref.xml, and listOfIDXrefData.xml. (It is a big picture, so click on it and then click on the picture in the window that opens up to see the origina size (at least in IE7))

Some other things to be aware of:

When importing the data, there is the case where you will need to map a BA rule as the following verbiage ‘for input value of A map an X, for value of E map Z for all others map Y’ or you are given the following table:

Incoming value   Output Value

A                        X

B                        Y

C                        Y

D                        Y

E                        Z

Since there is the following constraints on the xref_IDXRefData table

CONSTRAINT [IX_xref_IDXRefData_appID] UNIQUE NONCLUSTERED
(
[appID],
[idXRefID],
[appInstanceID]
) ON [PRIMARY] ,
CONSTRAINT [IX_xref_IDXRefData_commonID] UNIQUE NONCLUSTERED
(
[commonID],
[idXRefID],
[appInstanceID]
) ON [PRIMARY]

on either the commonID or the appInstanceID column can’t have a duplicate value. The first work around was to import the data with spaces in the following example

<listOfIDXRefData>
      <idXRef name=”Subscriber”>
          <appInstance name=”AppInstance_01″>
              <appID commonID=”X”>A</appID>
              <appID commonID=”Y”>B</appID>
              <appID commonID=”Y “>C</appID>
              <appID commonID=”Y  “>D</appID>
              <appID commonID=”Z”>E</appID>
          </appInstance>
     </idXRef>
</listOfIDXRefData>

In the map I would have the following logic where it would retrieve the appID and I would simply trim the spaces off of the right

However I was unable to import the data using the BTSXRefImport.exe, stating that a key contraint was violated.

I then changed the data to look like this (notice the periods instead of the spaces):

<listOfIDXRefData>
      <idXRef name=”Subscriber”>
          <appInstance name=”AppInstance_01″>
              <appID commonID=”X”>A</appID>
              <appID commonID=”Y”>B</appID>
              <appID commonID=”Y.”>C</appID>
              <appID commonID=”Y..”>D</appID>
              <appID commonID=”Z”>E</appID>
          </appInstance>
     </idXRef>
</listOfIDXRefData>

And in the map I took the first byte of the result of the appID

This worked out great for me except in the following situation

Incoming value   Output Value

A                        XX

B                        Y

C                        Y

D                        YY

E                        Z

I first created the following xml structure

<listOfIDXRefData>
      <idXRef name=”Subscriber”>
          <appInstance name=”AppInstance_01″>
              <appID commonID=”XX”>A</appID>
              <appID commonID=”Y”>B</appID>
              <appID commonID=”Y.”>C</appID>
              <appID commonID=”YY”>D</appID>
              <appID commonID=”Z”>E</appID>
          </appInstance>
     </idXRef>
</listOfIDXRefData>

The problem was that I could not just take the right two bytes because that would represent the following table and create a ‘Y.’ if ‘C’ was sent in. 

Incoming value   Output Value

A                        XX

B                        Y

C                        Y.

D                        YY

E                        Z

I finally created the final output

<listOfIDXRefData>
      <idXRef name=”FirstValue”>
          <appInstance name=”AppInstance_01″>
              <appID commonID=”X”>A</appID>
              <appID commonID=”Y”>B</appID>
              <appID commonID=”Y.”>C</appID>
              <appID commonID=”Y..”>D</appID>
              <appID commonID=”Z”>E</appID>
          </appInstance>
     </idXRef>
</listOfIDXRefData>

<listOfIDXRefData>
      <idXRef name=”SecondValue”>
          <appInstance name=”AppInstance_01″>
              <appID commonID=”X”>A</appID>
              <appID commonID=”Y”>D</appID>
           </appInstance>
     </idXRef>
</listOfIDXRefData>

I extracted the data, took the first character from each getCommonID result and then concatonated the values together:

Jan 232007
 

Drafting from Tom’s explaination on Cross Referencing, I wanted to step you through how I was able to use the functionality that is built into BizTalk and save myself a lot of coding.

Scenario: Take original filename of an 837 transaction, look up the actual pickup date based on the filename from a table and place it in the encounter staging table for every row that represented a claim.

Issues: HIPAA accelerator does not retain file name

There is no place in the data that I can use to pull the recieved date from within the data content. Only context data, and even at that I have to refer to issue point 1.

Oh, ya, these 837s were all custom 837 messages coming in.

I created a process that picks up the EDI file and starts an orchestration where it extracts the ISA05, ISA06, ISA13, and GS02 using a flat file pipeline (I will explain how I do that sometime) so I can retrieve the filename from the context of the message. Having that, I call a seperate orchestration that takes the enveloping information and filename and looks up the file recieved date. Once I had that file received date, I really did not need to know the filename anymore, I had the enveloping information in the file, I had the received date, now all I needed to do was store it and retrieve it.

The first thing I did was set up the necessary XML documents to setup the XRef tabes in the BizTalkMgmtDb. They are as follows:

ListofAppType.xml

ListofAppInstance.xml

 

ListOfIDXref.xml

Since I am not going to ‘preload’ the data into the database, it is going to be built on the fly, I did not create the ListOfIDXRefData.xml.

Now to the exciting part. In order to insert this information into the tables, we need somehow to actually load it. Oddly enough there is a executable in the BizTalk folder that will do just that: BTSXRefImport.exe

Tom has this really nifty (is that a kosher word?) batch file that will load the data, but I need to learn to crawl before I start running with this.

So how does the BTSXRefImport.exe import the data and set up the relationships between these three (four in Toms case) xml files? There needs to be a setup file that is called as an argument. Here is the setup xml file I used and in the comments, you can see how it is supposed to be invoked c:\>btsxrefimport -file=setupfile.xml

Now I need to write the data to the XRef table in an orchestration, right after I get the response from the stored procedure. The first thing I need to do is in this orchestration, I need to add a reference to the Microsoft.BizTalk.CrossReferencing.dll (located in the BizTalk install folder). I need to create my unique key, I do that by concatonating the ISA05,06,13, and GS02 together and then putting the string version of the date as the second argument. My logic is as follows:

So now I have the data in the XRef table, now all I need to do is extract it in the map.

And the arguments to the Get Common ID functoid are as follows:

So to resolve the issues, when the file is originally picked up, I go and extract the envloping information, look up the recieved date and store those two bits (enveloping information and file pickup date) of information and then send it to the HIPAA accelerator.

Once the HIPAA accelerator deserilizes it to its custom 837 schema, I have seperate maps for each provider that maps it into the standard schema, and then there is the final 837 to encounter map that I then use the Get Common ID value and by concatonating the envelope fields together, I can get the recieved date and put it in the staging table directly.