From: <jwi...@ea...> - 2011-02-15 12:13:06
|
Author: jwickers Date: 2011-02-15 05:15:17 -0800 (Tue, 15 Feb 2011) New Revision: 15757 Modified: modules/concur_integration/versions/1.4/trunk/webapp/concur-integration/reports/repository/TotalAmountByConcurBatchReport.jrxml Log: #1933 Add Status and Difference columns to the Total by Concur Batch report Modified: modules/concur_integration/versions/1.4/trunk/webapp/concur-integration/reports/repository/TotalAmountByConcurBatchReport.jrxml =================================================================== --- modules/concur_integration/versions/1.4/trunk/webapp/concur-integration/reports/repository/TotalAmountByConcurBatchReport.jrxml 2011-02-15 00:17:23 UTC (rev 15756) +++ modules/concur_integration/versions/1.4/trunk/webapp/concur-integration/reports/repository/TotalAmountByConcurBatchReport.jrxml 2011-02-15 13:15:17 UTC (rev 15757) @@ -4,7 +4,7 @@ <property name="ireport.encoding" value="UTF-8"/> <property name="net.sf.jasperreports.export.xls.white.page.background" value="Boolean.FALSE"/> <property name="ireport.zoom" value="1.2396694214876098"/> - <property name="ireport.x" value="141"/> + <property name="ireport.x" value="29"/> <property name="ireport.y" value="0"/> <import value="net.sf.jasperreports.engine.*"/> <import value="java.util.*"/> @@ -14,10 +14,13 @@ H.`BATCH_ID`, H.`BATCH_DATE`, H.`HASH_TOTAL`, + S.`DESCRIPTION` AS BATCH_STATUS, SUM(IF(A.`DEBIT_CREDIT_FLAG` = 'C', A.`AMOUNT`, 0)) AS CREDITS, SUM(IF(A.`DEBIT_CREDIT_FLAG` = 'D', A.`AMOUNT`, 0)) AS DEBITS FROM `DATA_IMPORT_CONCUR_HEADER` H +LEFT JOIN `STATUS_ITEM` S + ON H.IMPORT_STATUS_ID = S.STATUS_ID LEFT JOIN `DATA_IMPORT_CONCUR_DETAIL` D ON H.`BATCH_ID` = D.`BATCH_ID` LEFT JOIN `ACCTG_TRANS_ENTRY` A @@ -25,14 +28,14 @@ AND A.CONCUR_BATCH_SEQ_NUM = D.SEQUENCE_NUMBER AND A.CONCUR_BATCH_DATE = D.BATCH_DATE GROUP BY BATCH_ID -ORDER BY BATCH_ID; -]]> +ORDER BY BATCH_ID;]]> </queryString> <field name="BATCH_ID" class="java.lang.String"/> <field name="BATCH_DATE" class="java.util.Date"/> <field name="HASH_TOTAL" class="java.math.BigDecimal"/> <field name="DEBITS" class="java.math.BigDecimal"/> <field name="CREDITS" class="java.math.BigDecimal"/> + <field name="BATCH_STATUS" class="java.lang.String"/> <variable name="creditsTotal" class="java.math.BigDecimal" calculation="Sum"> <variableExpression><![CDATA[$F{CREDITS}]]></variableExpression> </variable> @@ -91,47 +94,61 @@ </graphicElement> </rectangle> <textField> - <reportElement x="4" y="3" width="100" height="17"/> + <reportElement x="4" y="3" width="90" height="17"/> <textElement textAlignment="Left"> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$R{Concur.BatchId}]]></textFieldExpression> </textField> <textField> - <reportElement x="104" y="3" width="100" height="17"/> + <reportElement x="94" y="3" width="90" height="17"/> <textElement textAlignment="Left"> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$R{Concur.BatchDate}]]></textFieldExpression> </textField> <textField> - <reportElement x="205" y="3" width="140" height="17"/> + <reportElement x="274" y="3" width="100" height="17"/> <textElement textAlignment="Right"> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$R{Concur.HashTotal}]]></textFieldExpression> </textField> <textField> - <reportElement x="345" y="3" width="140" height="17"/> + <reportElement x="374" y="3" width="100" height="17"/> <textElement textAlignment="Right"> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$R{Concur.DebitsSum}]]></textFieldExpression> </textField> <textField> - <reportElement x="485" y="3" width="140" height="17"/> + <reportElement x="474" y="3" width="100" height="17"/> <textElement textAlignment="Right"> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$R{Concur.CreditsSum}]]></textFieldExpression> </textField> <textField> - <reportElement x="625" y="3" width="140" height="17"/> + <reportElement x="574" y="3" width="100" height="17"/> <textElement textAlignment="Right"> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$R{Concur.Net}]]></textFieldExpression> </textField> + <textField> + <reportElement x="674" y="2" width="90" height="17"/> + <textElement textAlignment="Right"> + <font isBold="true"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$R{OpentapsDifference}]]></textFieldExpression> + </textField> + <textField> + <reportElement x="184" y="3" width="90" height="17"/> + <textElement textAlignment="Left"> + <font isBold="true"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$R{CommonStatus}]]></textFieldExpression> + </textField> </band> </columnHeader> <detail> @@ -143,7 +160,7 @@ </graphicElement> </line> <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true"> - <reportElement key="textField" x="4" y="4" width="100" height="10"/> + <reportElement key="textField" x="4" y="4" width="90" height="10"/> <box leftPadding="2" rightPadding="2"> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> @@ -156,7 +173,7 @@ <textFieldExpression class="java.lang.String"><![CDATA[$F{BATCH_ID}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="MM/dd/yyyy" isBlankWhenNull="true"> - <reportElement key="textField" x="104" y="4" width="100" height="10"/> + <reportElement key="textField" x="94" y="4" width="90" height="10"/> <box leftPadding="2" rightPadding="2"> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> @@ -169,7 +186,7 @@ <textFieldExpression class="java.util.Date"><![CDATA[$F{BATCH_DATE}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="¤ #,##0.00" isBlankWhenNull="true"> - <reportElement key="textField" x="205" y="4" width="140" height="10"/> + <reportElement key="textField" x="274" y="4" width="100" height="10"/> <box leftPadding="2" rightPadding="2"> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> @@ -182,7 +199,7 @@ <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{HASH_TOTAL}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="¤ #,##0.00" isBlankWhenNull="true"> - <reportElement key="textField" x="345" y="4" width="140" height="10"/> + <reportElement key="textField" x="374" y="4" width="100" height="10"/> <box leftPadding="2" rightPadding="2"> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> @@ -195,7 +212,7 @@ <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{DEBITS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="¤ #,##0.00" isBlankWhenNull="true"> - <reportElement key="textField" x="485" y="4" width="140" height="10"/> + <reportElement key="textField" x="474" y="4" width="100" height="10"/> <box leftPadding="2" rightPadding="2"> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> @@ -208,7 +225,7 @@ <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{CREDITS}]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" pattern="¤ #,##0.00" isBlankWhenNull="true"> - <reportElement key="textField" x="625" y="4" width="140" height="10"/> + <reportElement key="textField" x="574" y="4" width="100" height="10"/> <box leftPadding="2" rightPadding="2"> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> @@ -220,6 +237,32 @@ </textElement> <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{DEBITS}.subtract($F{CREDITS})]]></textFieldExpression> </textField> + <textField isStretchWithOverflow="true" pattern="¤ #,##0.00" isBlankWhenNull="true"> + <reportElement key="textField" x="674" y="4" width="90" height="10"/> + <box leftPadding="2" rightPadding="2"> + <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + </box> + <textElement textAlignment="Right"> + <font fontName="Times-Roman" size="10" pdfFontName="DejaVuSans.ttf" pdfEncoding="Identity-H" isPdfEmbedded="true"/> + </textElement> + <textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{HASH_TOTAL}.subtract($F{DEBITS}.subtract($F{CREDITS}))]]></textFieldExpression> + </textField> + <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true"> + <reportElement key="textField" x="184" y="4" width="90" height="10"/> + <box leftPadding="2" rightPadding="2"> + <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> + </box> + <textElement textAlignment="Left"> + <font fontName="Times-Roman" size="10" pdfFontName="DejaVuSans.ttf" pdfEncoding="Identity-H" isPdfEmbedded="true"/> + </textElement> + <textFieldExpression class="java.lang.String"><![CDATA[$F{BATCH_STATUS}]]></textFieldExpression> + </textField> </band> </detail> <columnFooter> |