A recent client had a high profile issue that took some interesting mapping to get working. I thought that I would write about it, as it is the second client that I have come across that has needed it. Since it has happened twice, it warrants a blog entry.
Requirement: I don’t know where a particular number is going to show up, but I need to find it and place it in this particular column in the output.
Specifically: I know that the Medicaid or Medicare number is going to show up in 1 of 4 places, I need to look for it and where ever it is, I need to place it in these two places in the output.
It is going to show up in the REF02 where the REF01 is 1C (Medicare) and 1D (Medicaid) in the following locations: 2420A, 2310B, 2010BA, 2010AA. I want to look first in the 2420A level, if it is not there, then I want to search the 2310B level, 2010BA, and finally the 2010AA and since there might (most likely) be multiple REF segments, I need it to search all of the REF segments (not just the first one).
Here is a few snippets of the xml where the data can reside:
<ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A> <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier> <REF02__RenderingProviderSecondaryIdentifier>2420A Medicaid</REF02__RenderingProviderSecondaryIdentifier> </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A> <ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A> <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier> <REF02__RenderingProviderSecondaryIdentifier>2420A Medicare</REF02__RenderingProviderSecondaryIdentifier> </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A>
and
<ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B> <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier> <REF02__RenderingProviderSecondaryIdentifier>2310B Medicaid</REF02__RenderingProviderSecondaryIdentifier> </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B> <ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B> <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier> <REF02__RenderingProviderSecondaryIdentifier>2310B Medicare</REF02__RenderingProviderSecondaryIdentifier> </ns0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B>
and
<ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB> <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier> <REF02__PaytoProviderIdentifier>2010AB Medicaid</REF02__PaytoProviderIdentifier> </ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB> <ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB> <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier> <REF02__PaytoProviderIdentifier>2010AB Medicare</REF02__PaytoProviderIdentifier> </ns0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB>
and
<ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop> <ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA> <REF01__ReferenceIdentificationQualifier>1D</REF01__ReferenceIdentificationQualifier> <REF02__BillingProviderAdditionalIdentifier>2010AA Medicaid</REF02__BillingProviderAdditionalIdentifier> </ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA> <ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA> <REF01__ReferenceIdentificationQualifier>1C</REF01__ReferenceIdentificationQualifier> <REF02__BillingProviderAdditionalIdentifier>2010AA Medicare</REF02__BillingProviderAdditionalIdentifier> </ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA> </ns0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop>
So for the purposes of this blog entry I have created an output schema that creates an output row for each service line:
I first create some mapping so we can ensure that the map is creating the output correctly.
Which creates this (so far so good):
<ns0:Root xmlns:ns0="http://BubbleUp.Output"> <ServiceLine ChargeAmt="914"></ServiceLine> <ServiceLine ChargeAmt="5299"></ServiceLine> </ns0:Root>
Now we get into the ’real work’
What we need to do is make the map query various portions of the xml document and because I am lazy, lets have the mapper do the heavy lifting for us:
Lets drag the REF02 from the 2420A loop into the MedicaidId and lets look at the XSL:
and the resulting xsl is:
<xsl:attribute name="MedicaidId"> <xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A/REF02__RenderingProviderSecondaryIdentifier/text()" /> </xsl:attribute>
and since we are going to want it from the other loops, let’s get the other REF segments in xsl:
<xsl:attribute name="MedicaidId"> <xsl:value-of select="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B/REF02__RenderingProviderSecondaryIdentifier/text()" /> </xsl:attribute>
<xsl:attribute name="MedicaidId"> <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB/REF02__PaytoProviderIdentifier/text()" /> </xsl:attribute>
<xsl:attribute name="MedicaidId"> <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA/REF02__BillingProviderAdditionalIdentifier/text()" /> </xsl:attribute>
Now we need to write the ’bubble up’ logic:
The first thing is we need to query the correct REF01 qualifier
<xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__RenderingProviderSecondaryIdentifier/text()" />
and the other values in the other segments the same logic.
After that we merge all of these queries into a variable in a choose function:
<!--Lets create the Mediciad ID variable--> <xsl:variable name="medicaidID"> <xsl:choose> <xsl:when test="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A/REF01__ReferenceIdentificationQualifier/text()='1D'"> <xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__RenderingProviderSecondaryIdentifier/text()" /> </xsl:when> <xsl:when test="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B/REF01__ReferenceIdentificationQualifier/text()='1D'"> <xsl:value-of select="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__RenderingProviderSecondaryIdentifier/text()" /> </xsl:when> <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB/REF01__ReferenceIdentificationQualifier/text()='1D'"> <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__PaytoProviderIdentifier/text()" /> </xsl:when> <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA/REF01__ReferenceIdentificationQualifier/text()='1D'"> <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA[REF01__ReferenceIdentificationQualifier/text()='1D']/REF02__BillingProviderAdditionalIdentifier/text()" /> </xsl:when> <xsl:otherwise> <xsl:value-of select="'Mediciad Id Not found'" /> </xsl:otherwise> </xsl:choose> </xsl:variable>
Now lets take the result of the result of the choose function and populate the attribute:
<!--Now that we have found it, lets put the value as the attribute--> <xsl:attribute name="MedicaidId"> <xsl:value-of select="$medicaidID" /> </xsl:attribute>
So the last step in this is to implement the code in the map, because like all of the maps I have ever worked in, I already have logic I have created, I just need to enhance what I already have there. I copy the above xsl into a scripting (inline xslt) functiod and attach it to the MediaidId attribute:
Now I need to find the MedicareId, so I simply replace the ID with 1C and change the variable name and attribute name I would have the following code:
<xsl:variable name="medicareID"> <xsl:choose> <xsl:when test="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A/REF01__ReferenceIdentificationQualifier/text()='1C'"> <xsl:value-of select="s0:TS837Q2_2420_SubLoop/s0:TS837Q2_2420A_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2420A[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__RenderingProviderSecondaryIdentifier/text()" /> </xsl:when> <xsl:when test="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B/REF01__ReferenceIdentificationQualifier/text()='1C'"> <xsl:value-of select="../s0:TS837Q2_2310_SubLoop/s0:TS837Q2_2310B_Loop/s0:REF_RenderingProviderSecondaryIdentification_TS837Q2_2310B[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__RenderingProviderSecondaryIdentifier/text()" /> </xsl:when> <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB/REF01__ReferenceIdentificationQualifier/text()='1C'"> <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AB_Loop/s0:REF_PaytoProviderSecondaryIdentificationNumber_TS837Q2_2010AB[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__PaytoProviderIdentifier/text()" /> </xsl:when> <xsl:when test="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA/REF01__ReferenceIdentificationQualifier/text()='1C'"> <xsl:value-of select="../../../s0:TS837Q2_2010A_SubLoop/s0:TS837Q2_2010AA_Loop/s0:REF_TS837Q2_2010AA_SubLoop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA_Loop/s0:REF_BillingProviderSecondaryIdentificationNumber_TS837Q2_2010AA[REF01__ReferenceIdentificationQualifier/text()='1C']/REF02__BillingProviderAdditionalIdentifier/text()" /> </xsl:when> <xsl:otherwise> <xsl:value-of select="'Medicare Id Not found'" /> </xsl:otherwise> </xsl:choose> </xsl:variable> <xsl:attribute name="MedicareId"> <xsl:value-of select="$medicareID" /> </xsl:attribute>
Put it into a scripting functiod and you would have the following map:
The output looks like this:
<ns0:Root xmlns:ns0="http://BubbleUp.Output"> <ServiceLine ChargeAmt="914" MedicaidId="2420A Medicaid" MedicareId="2420A Medicare"></ServiceLine> <ServiceLine ChargeAmt="5299" MedicaidId="2310B Medicaid" MedicareId="2010AB Medicare"></ServiceLine> </ns0:Root>
As a bonus: the client wanted to sum up Deductible and CoInsurance amounts from the CAS segment where the qualifier was 1 and 2 respectively, so the following example has that xsl included in it.
Here is the solution (BizTalk 2009 solution only: please don’t ask for 2006, as I don’t have time to make a version for that!)