Here is the XSLT
Code:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:nr1="urn:com.axonglobal.mcc.academy.payments.ap.inbound" version="2.0">
<xsl:output method="xml" encoding="UTF-8" indent="yes" />
<xsl:template match="/">
<root>
<xsl:apply-templates />
</root>
</xsl:template>
<xsl:template match="nr1:*">
<xsl:variable name="chqdate" select="concat(substring(invoiceheader[1]/postingdate,1,4),'-',substring(invoiceheader[1]/postingdate,5,2),'-',substring(invoiceheader[1]/postingdate,7,2))" />
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>1</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader)" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>2</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[paymentmethod = 'C' and invoicetotal < 0.50])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[paymentmethod = 'C' and invoicetotal < 0.50]/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>3</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[paymentmethod = 'B' and invoicetotal < 0.50])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[paymentmethod = 'B' and invoicetotal < 0.50]/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>4</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[paymentmethod = 'C'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[paymentmethod = 'C']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>5</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[paymentmethod = 'B'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[paymentmethod = 'B']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>6</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[vendorid = 'ACADLAND'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[vendorid = 'ACADLAND']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>7</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[vendorid = 'ACADLAND' and paymentmethod = 'C'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[vendorid = 'ACADLAND' and paymentmethod = 'C']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>8</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[vendorid = 'ACADLAND' and paymentmethod = 'B'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[vendorid = 'ACADLAND' and paymentmethod = 'B']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>9</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[vendorid = 'ACADBENS'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[vendorid = 'ACADBENS']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>10</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[vendorid = 'ACADBENS' and paymentmethod = 'C'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[vendorid = 'ACADBENS' and paymentmethod = 'C']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
<tblWeeklyHBSummary>
<dtmChequeDate>
<xsl:value-of select="$chqdate" />
</dtmChequeDate>
<intSeqNumber>11</intSeqNumber>
<intRecordCount>
<xsl:value-of select="count(invoiceheader[vendorid = 'ACADBENS' and paymentmethod = 'B'])" />
</intRecordCount>
<curTotal>
<xsl:value-of select="format-number(sum(invoiceheader[vendorid = 'ACADBENS' and paymentmethod = 'B']/invoicetotal),'###,###,##0.00')" />
</curTotal>
</tblWeeklyHBSummary>
</xsl:template>
</xsl:stylesheet>
An example of the XML is below
Code:
<?xml version="1.0" encoding="UTF-8"?>
<nr1:mt_generic_ap_source_file_xml xmlns:nr1="xxx">
<fileheader>
<datatype>H0</datatype>
<sourcesystem>ACADEMY Benefits</sourcesystem>
<interfaceid>0130</interfaceid>
<date>20161219</date>
<filename>Capita Benefits Payments</filename>
<runnumber>20160051</runnumber>
</fileheader>
<invoiceheader>
<datatype>H1</datatype>
<invoicereference>1486322</invoicereference>
<vendorid>xxx</vendorid>
<legacyvendorid/>
<invoicetotal>7.34</invoicetotal>
<currencycode>GBP</currencycode>
<discount/>
<paymentterm/>
<paymentmethod>C</paymentmethod>
<paymentsupplement/>
<invoicedate>20161226</invoicedate>
<postingdate>20161226</postingdate>
<businessarea/>
<headertext>03|91238</headertext>
<contactname/>
<contactnumber/>
<vendorname1>xxxx</vendorname1>
<vendorname2>1486322</vendorname2>
<vendorname3/>
<vendorname4/>
<houseandstreet>xxxx</houseandstreet>
<city>xxx</city>
<postcode>M12 3AB:</postcode>
<pobox/>
<poboxpostcode/>
<country>GB</country>
<faxnumber/>
<headerremittance>
<datatype>R1</datatype>
<textid>MA</textid>
<text>.....</text>
<text>XYZ</text>
<text>XYZ</text>
<text>XYZ</text>
<text>XYZ</text>
</headerremittance>
<headerremittance>
<datatype>R1</datatype>
<textid>PA</textid>
<text>xxxxx</text>
</headerremittance>
<invoiceitem>
<datatype>D2</datatype>
<itemnumber>0000000001</itemnumber>
<amount>7.34</amount>
<taxamount/>
<taxcode>V3</taxcode>
<costcentre>105417</costcentre>
<glcode>462000</glcode>
<companycode>1000</companycode>
<businessarea/>
<itemtext/>
</invoiceitem>
</invoiceheader>
</nr1:mt_generic_ap_source_file_xml>
I should point out that there is nothing wrong with the template, it works in 2003 and has done for years, and nothing has changed except we are now using 2013...