Search code examples
xsltxslt-2.0xslt-grouping

XSLT Help - Store field mapping into a variable and group the input pay load based on that mapping

I have an XML file that needs to be converted to pipe delimited text file. In addition, the xml contains legacy pay codes which need to be translated to future state codes in the output file. The mapping between those codes in either 1:1 or M:1.For example, if 4 lines of input data with different codes correspond to one code in future state, the output should produce one row with amounts summed up. Here is an example,

xml:

<?xml version="1.0" encoding="UTF-8"?>
<wd:Report_Data xmlns:wd="urn:com.workday.report/ERP-PAY-CR_PAYROLL_BALANCES_-_CX">
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>102</wd:Pay_Component_Code>
<wd:Result_Line_Amount>14326.91</wd:Result_Line_Amount>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>123</wd:Pay_Component_Code>
<wd:Result_Line_Amount>1175.56</wd:Result_Line_Amount>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>126</wd:Pay_Component_Code>
<wd:Result_Line_Amount>2350.74</wd:Result_Line_Amount>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>217</wd:Pay_Component_Code>
<wd:Result_Line_Amount>0.85</wd:Result_Line_Amount>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>225</wd:Pay_Component_Code>
<wd:Result_Line_Amount>1.03</wd:Result_Line_Amount>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>353</wd:Pay_Component_Code>
<wd:Result_Line_Amount>21.84</wd:Result_Line_Amount>
</wd:Report_Entry>
<wd:Report_Entry>
<wd:Legal_Entity_Name wd:Descriptor="Wayne Enterprises">
<wd:ID wd:type="WID">66b06c2b4ec001df7d2d7875ea020e52</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">G01A</wd:ID>
<wd:ID wd:type="Company_Reference_ID">G01A</wd:ID>
</wd:Legal_Entity_Name>
<wd:Person_Number>2100003</wd:Person_Number>
<wd:Calendar_Quarter wd:Descriptor="2019-Q1">
<wd:ID wd:type="WID">da9970b23fce10001102e89a634e0024</wd:ID>
<wd:ID wd:type="Calendar_Quarter">2019-Q1</wd:ID>
</wd:Calendar_Quarter>
<wd:Pay_Component_Code>420</wd:Pay_Component_Code>
<wd:Result_Line_Amount>1632.61</wd:Result_Line_Amount>
</wd:Report_Entry>

</wd:Report_Data>

Mapping Reference: 

Legacy Codes   Future State Values
102            Regular
123            Regular
126            Regular
217            Diff Shift Prod
225            Diff Shift Prod
353            PTO Donation
420            PTO Donation

Expected output:

Legal Entity Name|Person Number|Calendar Quarter|Pay Component Code|Result Line Amount
Wayne Enterprises|2100003|2019-Q1|Regular|17853.21
Wayne Enterprises|2100003|2019-Q1|Diff Shift Prod|1.88
Wayne Enterprises|2100003|2019-Q1|PTO Donation|1654.45

Essentially the way I'd envision is to create a mapping variable and I'm okay hard coding all the mapping in my code and group the amounts based on the new code. Attaching my xsl code if it is of any help. Any help is greatly appreciated.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"
    xmlns:wd="urn:com.workday.report/ERP-PAY-CR_PAYROLL_BALANCES_-_CX"
    version="2.0">
    <xsl:output method="text"/>
    <xsl:variable name="linefeed" select="'&#xD;&#xA;'"/>
    <xsl:variable name="pipe" select="'|'"/>
    <xsl:variable name="EffectiveStartDate" select="'1951-01-01'"/>
    <xsl:variable name="EffectiveEndDate" select="'4712-12-31'"/>

    <xsl:param name="quote">"</xsl:param>
    <xsl:template match="/">
        <!--  File Header Record  -->
        <!-- ERP1 -->
        <xsl:call-template name="Write-Header-Record0"/>
        <!--  File Detail Layout  -->
        <xsl:for-each
            select="wd:Report_Data/wd:Report_Entry">

                <xsl:call-template name="Write-Detail-Record"/>

        </xsl:for-each>
    </xsl:template>

    <!-- File Header Record 0-->
    <xsl:template name="Write-Header-Record0">

        <xsl:text>Legal Entity Name|Person Number|Calendar Quarter|Pay Component Code|Result Line Amount</xsl:text>

        <xsl:value-of select="$linefeed"/>
    </xsl:template>

    <xsl:template name="Write-Detail-Record">

        <xsl:value-of select="wd:Legal_Entity_Name/@wd:Descriptor"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:Person_Number"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:Calendar_Quarter/@wd:Descriptor"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:Pay_Component_Code"/>
        <xsl:value-of select="$pipe"/>

        <xsl:value-of select="wd:Result_Line_Amount"/>



        <xsl:value-of select="$linefeed"/>


    </xsl:template>

</xsl:stylesheet>
Solution

  • Try this as your starting point:

    XSLT 2.0

    <xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:wd="urn:com.workday.report/ERP-PAY-CR_PAYROLL_BALANCES_-_CX">
<xsl:output method="text"/>

<xsl:variable name="payCodes">
    <payCode name="Regular">
        <legacyCode>102</legacyCode>
        <legacyCode>123</legacyCode>
        <legacyCode>126</legacyCode>
    </payCode>
    <payCode name="Diff Shift Prod">
        <legacyCode>217</legacyCode>
        <legacyCode>225</legacyCode>
    </payCode>
    <payCode name="PTO Donation">
        <legacyCode>353</legacyCode>
        <legacyCode>420</legacyCode>
    </payCode>
</xsl:variable>

<xsl:key name="paycode" match="payCode" use="legacyCode" />

<xsl:template match="/wd:Report_Data">
    <xsl:variable name="common">
        <xsl:value-of select="wd:Report_Entry[1]/wd:Legal_Entity_Name/@wd:Descriptor"/>
        <xsl:text>|</xsl:text>
        <xsl:value-of select="wd:Report_Entry[1]/wd:Person_Number"/>
        <xsl:text>|</xsl:text>
        <xsl:value-of select="wd:Report_Entry[1]/wd:Calendar_Quarter/@wd:Descriptor"/>
        <xsl:text>|</xsl:text>
    </xsl:variable>
    <!-- HEADER -->
    <xsl:text>Legal Entity Name|Person Number|Calendar Quarter|Pay Component Code|Result Line Amount&#xD;&#xA;</xsl:text>
    <!-- DATA -->
    <xsl:for-each-group select="wd:Report_Entry" group-by="key('paycode', wd:Pay_Component_Code, $payCodes)">
        <xsl:value-of select="$common"/>
        <xsl:value-of select="key('paycode', wd:Pay_Component_Code, $payCodes)/@name"/>
        <xsl:text>|</xsl:text>
        <xsl:value-of select="sum(current-group()/wd:Result_Line_Amount)"/>
        <xsl:text>&#xD;&#xA;</xsl:text>
    </xsl:for-each-group>
</xsl:template>

</xsl:stylesheet>

    Demo: https://xsltfiddle.liberty-development.net/6pS26mw