Group Pay Code and Sum Pay Amount
Posted: Wed Nov 15, 2023 1:36 am
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:
XSLT:
Output needed:
How can I achieve this?
Your assistance is greatly appreciated.
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>
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="
">
<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>
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="
">
<EARN_3_CODE>100</EARN_3_CODE>
<EARN_3_AMOUNT>75</EARN_3_AMOUNT>
</DETAIL>
<DETAIL xtt:separator="," xtt:endTag="
">
<EARN_3_CODE>C0</EARN_3_CODE>
<EARN_3_AMOUNT>75</EARN_3_AMOUNT>
</DETAIL>
<DETAIL xtt:separator="," xtt:endTag="
">
<EARN_3_CODE>CR0</EARN_3_CODE>
<EARN_3_AMOUNT>75</EARN_3_AMOUNT>
</DETAIL>
<DETAIL xtt:separator="," xtt:endTag="
">
<EARN_3_CODE>I</EARN_3_CODE>
<EARN_3_AMOUNT>60</EARN_3_AMOUNT>
</DETAIL>
</FILE>
Your assistance is greatly appreciated.