Group Pay Code and Sum Pay Amount

[325526]

Hi,

I am trying to Sum the Pay Amount that belongs to the same Pay Code, but it has to exclude CYE, WCS and MBO pay reasons while summing.

I am unable to sum the Pay Amount even if I am able to group it.

Sample XML:

Code: Select all

<pi:Payroll_Extract_Employees xmlns:pi="urn:com.workday/picof">
<pi:PayGroup>
<pi:Header>
<pi:Pay_Group_ID>89R</pi:Pay_Group_ID>
</pi:Header>
<pi:Employee>
<pi:Summary>
<pi:Employee_ID>300000</pi:Employee_ID>
</pi:Summary>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_SQB</pi:Pay_Reason>
<pi:Code pi:PriorValue="">100</pi:Code>
<pi:Amount pi:PriorValue="">75</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_SQB_CYE</pi:Pay_Reason>
<pi:Code pi:PriorValue="">100</pi:Code>
<pi:Amount pi:PriorValue="">80</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_Commission</pi:Pay_Reason>
<pi:Code pi:PriorValue="">C0</pi:Code>
<pi:Amount pi:PriorValue="">75</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_Commission_CYE</pi:Pay_Reason>
<pi:Code pi:PriorValue="">C0</pi:Code>
<pi:Amount pi:PriorValue="">80</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_SRB</pi:Pay_Reason>
<pi:Code pi:PriorValue="">CR0</pi:Code>
<pi:Amount pi:PriorValue="">75</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_SRB_CYE</pi:Pay_Reason>
<pi:Code pi:PriorValue="">CR0</pi:Code>
<pi:Amount pi:PriorValue="">80</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_One_Time_Payment_MisB</pi:Pay_Reason>
<pi:Code pi:PriorValue="">I</pi:Code>
<pi:Amount pi:PriorValue="">15</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_One_Time_Payment_RefB</pi:Pay_Reason>
<pi:Code pi:PriorValue="">I</pi:Code>
<pi:Amount pi:PriorValue="">15</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_One_Time_Payment_RB</pi:Pay_Reason>
<pi:Code pi:PriorValue="">I</pi:Code>
<pi:Amount pi:PriorValue="">15</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_One_Time_Payment_Night</pi:Pay_Reason>
<pi:Code pi:PriorValue="">I</pi:Code>
<pi:Amount pi:PriorValue="">15</pi:Amount>
</pi:Pay_Data>
<pi:Pay_Data>
<pi:Pay_Reason pi:PriorValue="">One_Time_Payment_SRB_CYE</pi:Pay_Reason>
<pi:Code pi:PriorValue="">I</pi:Code>
<pi:Amount pi:PriorValue="">15</pi:Amount>
</pi:Pay_Data>
</pi:Employee>
</pi:PayGroup>
</pi:Payroll_Extract_Employees>

XSLT:

Code: Select all

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs"
    xmlns:xtt="urn:com.workday/xtt" xmlns:this="urn:this-stylesheet"
    xmlns:pi="urn:com.workday/picof" version="2.0">

    <xsl:output indent="no" method="xml" encoding="UTF-8"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="pi:Payroll_Extract_Employees">
        <FILE xtt:quotes="always" xtt:quoteStyle="double">
            

            <!-- Call templates for Header and Employees -->
            <xsl:apply-templates/>

        </FILE>
    </xsl:template>

    <!-- Do nothing for pi:Header Sections -->
    <xsl:template match="pi:PayGroup/pi:Header"/>

    <!-- For each employee, call the pi:Pay_Data template -->
    <xsl:template match="pi:PayGroup">
        <xsl:for-each select="pi:Employee">
            <xsl:if test="exists(./pi:Pay_Data)">
                <xsl:for-each-group select="./pi:Pay_Data" group-by="./pi:Code">

                    <DETAIL xtt:separator="," xtt:endTag="&#xA;">
                        
                        
                        <EARN_3_CODE>
                            <xsl:value-of select="pi:Code"/>
                      
                        </EARN_3_CODE>
                        

                        <xsl:variable name="amount">

                            <xsl:for-each select="current-group()">
                              
                                <xsl:if
                                    test="
                                        ((pi:Pay_Reason != 'One_Time_Payment_Commission_CYE')
                                        and (pi:Pay_Reason != 'One_Time_Payment_SRB_CYE')
                                        and (pi:Pay_Reason != 'One_Time_Payment_SQB_CYE')
                                        and (pi:Pay_Reason != 'One_Time_Payment_MBO_Bonus_CYE_WDay_Only_Load')
                                        and (pi:Pay_Reason != 'One_Time_Payment_WCS_Bonus_CYE_WDay_Only_Load'))">
                                    
                                    <xsl:choose>
                                        <xsl:when
                                            test="pi:Amount/@pi:PriorValue != '' and pi:Amount/@pi:PriorValue != 0">
                                            <xsl:value-of
                                                select="sum(pi:Amount + pi:Amount/@pi:PriorValue)"
                                            />
                                        </xsl:when>
                                        <xsl:otherwise>
                                            <xsl:value-of select="sum(pi:Amount)"/>
                                        </xsl:otherwise>
                                    </xsl:choose>
                                </xsl:if>
                            </xsl:for-each>
                        </xsl:variable>
                       
                        <EARN_3_AMOUNT>

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

                        </EARN_3_AMOUNT>
                    </DETAIL>

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

  
</xsl:stylesheet>

Output needed:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<FILE xmlns:xtt="urn:com.workday/xtt" xmlns:this="urn:this-stylesheet"
    xmlns:pi="urn:com.workday/picof" xtt:quotes="always" xtt:quoteStyle="double">
    <DETAIL xtt:separator="," xtt:endTag="&#xA;">
        <EARN_3_CODE>100</EARN_3_CODE>
        <EARN_3_AMOUNT>75</EARN_3_AMOUNT>
    </DETAIL>
    <DETAIL xtt:separator="," xtt:endTag="&#xA;">
        <EARN_3_CODE>C0</EARN_3_CODE>
        <EARN_3_AMOUNT>75</EARN_3_AMOUNT>
    </DETAIL>
    <DETAIL xtt:separator="," xtt:endTag="&#xA;">
        <EARN_3_CODE>CR0</EARN_3_CODE>
        <EARN_3_AMOUNT>75</EARN_3_AMOUNT>
    </DETAIL>
    <DETAIL xtt:separator="," xtt:endTag="&#xA;">
        <EARN_3_CODE>I</EARN_3_CODE>
        <EARN_3_AMOUNT>60</EARN_3_AMOUNT>
    </DETAIL>
</FILE>

How can I achieve this?

Your assistance is greatly appreciated.

[325526]

Thank you!

I tried with the code you provided and it is not adding up the amount. Its just pasting the value as below:
<DETAIL xtt:separator="," xtt:endTag="&#xA;">
<EARN_3_CODE>I</EARN_3_CODE>
<EARN_3_AMOUNT>15151515</EARN_3_AMOUNT>

I found the different solution for this. Thank you so much for your response.

Regards,
Dsouza