Custom XSL example

Situation:

I have a single source schema that pulls in from our database. The result set will pull in a record set that contain a single family member and benefit enrollment information. The following is an example of what the data may look like. The actual data is more verbose.

Column Definition: EmployeeNumber, EmployeeName, PersonType, PersonNumber, PersonName, EnrolledBenefitName, EnrolledBenefitStartDate, EnrolledBenefitTierName

Record 1: 115432, Joss Gor, Employee, 115432, Joss Gor, Medical PPO, 01/01/2013, Employee + Spouse

Record 2: 115432, Joss Gor, Spouse, 115789, Anie Chun, Medical PPO, 01/01/2013, Employee + Spouse

Record 3: 115432, Joss Gor, Employee, 115432, Joss Gor, Dental HMO, 01/01/2013, Employee only

Record 4: 115432, Joss Gor, Employee, 115432, Joss Gor, Employee Life, 06/01/2013, Employee only

Record 5: 215789, Nicole Veen, Employee, 215789, Nicole Veen, Medical HMO, 01/01/2013, Employee + Domestic Partner + 1 Dependent

Record 6: 215789, Nicole Veen, Spouse, 215800, Nancy Veen, Medical HMO, 01/01/2013, Employee + Domestic Partner + 1 Dependent

Record 7: 215789, Nicole Veen, Dependent, 345879, Vash Veen, Medical HMO, 04/15/2013, Employee + Domestic Partner + 1 Dependent

Record 8: 215789, Nicole Veen, Employee, 215789, Nicole Veen, Child Life, 04/15/2013, Employee + 1

Record 9: 215789, Nicole Veen, Dependent, 345879, Vash Veen, Child Life, 04/15/2013, Employee + 1

Record 10: 215789, Nicole Veen, Employee, 215789, Nicole Veen, Medical FSA, 01/01/2013, Employee only

Depending on the file requirements of the receiving entities, I need to be able to order the data in many different ways:

1. Person-by-Person, all benefits on one line for each person (Person 1, EnrolledBenefit 1, EnrolledBenefit 2, EnrolledBenefit 3)

a. Joss Gor, Medical PPO, Dental HMO, Employee Life

b. Anie Chun, Medical PPO, ,

c. Nicole Veen, Medical HMO, Child Life, Medical FSA

d. Nancy Veen, Medical HMO, ,

e. Vash Veen, Medical HMO, Child Life,

2. Family-by-Benefit, each benefit on its own line, all family enrollees on the same line. (EnrolledPerson 1, EnrolledPerson 2, EnrolledPerson3, EnrolledBenefit)

a. Joss Gor, Anie Chun, , Medical PPO

b. Joss Gor, , , Dental HMO

c. Joss Gor, , , Employee Life

d. Nicole Veen, Nancy Veen, Vash Veen, Medical HMO

e. Nicole Veen, Vash Veen, , Child Life

f. Nicole Veen, , , Medical FSA

3. Person by Benefit, each Person/Benefit combination on its own line. (EnrolledPerson, EnrolledBenefit)

a. Joss Gor, Medical PPO

b. Joss Gor, Dental HMO

c. Joss Gor Employee Life

d. Anie Chun, Medical PPO

e. Nicole Veen, Medical HMO

f. Nicole Veen, Child Life

g. Nicole Veen, Medical FSA

h. Nancy Veen, Medical HMO

i. Vash Veen, Medical HMO

j. Vash Veen, Child Life

There are also occasions where I may need to choose the greatest enrollment date for an individual, so I would need to be able to compare a single column for an individual or family to determine proper output. This sort of comparison would occur for any other field, not just dates.

Because of the way we our building our process flows, it is a requirement that I be able to do all of this logic contained in a single data mapping if at all possible.

Solution:

All of the three are pretty similar and you need to load a separate schema for each case so that separate outputs will be generated. We are explaining you the first one and similarly you can take idea for implementing the other two. Please follow below steps:

Case 1. Person-by-Person, all benefits on one line for each person (Person 1, EnrolledBenefit 1, EnrolledBenefit 2, EnrolledBenefit 3)

1. Use a Custom XSL Before and Custom XSL After on the Record level of the target schema.

2. In the Custom XSL Before create a local variable and select the value as the PersonName. For Example :

<xsl:for-each select="$Input_EmployeeSchema/Root/Record">
<xsl:variable name="name" select="PersonName"/>

1. In the Custom XSL After end the For-each tag. For Example:

/xsl:for-each

1. Now in the textual rule at Record level apply IFF Condition to check that the position is equal to one and there is no sibling preceding the PersonName. For Example:

IFF CONDITION} Value =['true']

1. Now in the textual rule for Person1 field on the target schema provide PersonName that is selected in the Custom XSL. For Example:

PersonName

1. Now Use Custom XSL Before and Custom XSL After on the EnrolledBenefit field.

2. In Custom XSL Before create a local variable and select the value as EnrolledBenefitName. For Example:

<xsl:for-each select="../Record[PersonName = $name]">
<xsl:variable name="BName" select="EnrolledBenefitName"/>

8.In the Custom XSL After end the For-each tag. For Example:

/xsl:for-each

9.Now in the textual rule on EnrolledBenefit field apply IFF Condition:

IFF CONDITION} Value =[EnrolledBenefitName]