modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/ad_reports/OutstandingPayment.jrxml
author Harpreet Singh <harpreet@openbravo.com>
Fri, 10 Dec 2010 19:47:45 +0100
changeset 9076 c4f1a5c7d7c2
child 10772 802557f42def
permissions -rw-r--r--
Added 3.0 modules to modules dir and changed the VERSION number
harpreet@9076
     1
<?xml version="1.0" encoding="UTF-8"  ?>
harpreet@9076
     2
<!-- Created with iReport - A designer for JasperReports -->
harpreet@9076
     3
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
harpreet@9076
     4
<jasperReport
harpreet@9076
     5
		 name="OutstandingPayment"
harpreet@9076
     6
		 columnCount="1"
harpreet@9076
     7
		 printOrder="Vertical"
harpreet@9076
     8
		 orientation="Portrait"
harpreet@9076
     9
		 pageWidth="495"
harpreet@9076
    10
		 pageHeight="802"
harpreet@9076
    11
		 columnWidth="495"
harpreet@9076
    12
		 columnSpacing="0"
harpreet@9076
    13
		 leftMargin="0"
harpreet@9076
    14
		 rightMargin="0"
harpreet@9076
    15
		 topMargin="0"
harpreet@9076
    16
		 bottomMargin="0"
harpreet@9076
    17
		 whenNoDataType="NoPages"
harpreet@9076
    18
		 isTitleNewPage="false"
harpreet@9076
    19
		 isSummaryNewPage="false">
harpreet@9076
    20
	<property name="ireport.scriptlethandling" value="0" />
harpreet@9076
    21
	<property name="ireport.encoding" value="UTF-8" />
harpreet@9076
    22
	<import value="java.util.*" />
harpreet@9076
    23
	<import value="net.sf.jasperreports.engine.*" />
harpreet@9076
    24
	<import value="net.sf.jasperreports.engine.data.*" />
harpreet@9076
    25
harpreet@9076
    26
	<parameter name="RECONCILIATION_ID" isForPrompting="false" class="java.lang.String"/>
harpreet@9076
    27
	<parameter name="NUMBERFORMAT" isForPrompting="false" class="java.text.DecimalFormat"/>
harpreet@9076
    28
	<queryString><![CDATA[SELECT trans.statementdate as TRANS_DATE, coalesce(TO_CHAR(bp.name), TO_CHAR(gl.name), '') as DESCRIPTION,
harpreet@9076
    29
       coalesce(TO_CHAR(p.documentno), '') as REF, trans.paymentamt as AMOUNT
harpreet@9076
    30
FROM fin_finacc_transaction trans
harpreet@9076
    31
     left join fin_payment p on (trans.fin_payment_id = p.fin_payment_id)
harpreet@9076
    32
     left join c_bpartner bp on (p.c_bpartner_id = bp.c_bpartner_id)
harpreet@9076
    33
     left join c_glitem gl on (trans.c_glitem_id = gl.c_glitem_id)
harpreet@9076
    34
where trans.fin_financial_account_id = (SELECT rec.fin_financial_account_id
harpreet@9076
    35
                                        FROM fin_reconciliation rec
harpreet@9076
    36
                                        WHERE fin_reconciliation_id=$P{RECONCILIATION_ID})
harpreet@9076
    37
      and (trans.fin_reconciliation_id is null
harpreet@9076
    38
           or trans.fin_reconciliation_id in
harpreet@9076
    39
                                          (SELECT r.fin_reconciliation_id
harpreet@9076
    40
                                           FROM fin_reconciliation r
harpreet@9076
    41
                                           WHERE r.created >(SELECT rec.created
harpreet@9076
    42
                                                            FROM fin_reconciliation rec
harpreet@9076
    43
                                                            WHERE fin_reconciliation_id=$P{RECONCILIATION_ID})))
harpreet@9076
    44
      and trans.depositamt = 0 and trans.paymentamt > 0
harpreet@9076
    45
      and trans.statementdate <= (SELECT rec.dateto
harpreet@9076
    46
                                  FROM fin_reconciliation rec 
harpreet@9076
    47
                                  WHERE fin_reconciliation_id=$P{RECONCILIATION_ID})
harpreet@9076
    48
ORDER BY TRANS_DATE, DESCRIPTION, AMOUNT]]></queryString>
harpreet@9076
    49
harpreet@9076
    50
	<field name="TRANS_DATE" class="java.util.Date"/>
harpreet@9076
    51
	<field name="DESCRIPTION" class="java.lang.String"/>
harpreet@9076
    52
	<field name="REF" class="java.lang.String"/>
harpreet@9076
    53
	<field name="AMOUNT" class="java.math.BigDecimal"/>
harpreet@9076
    54
harpreet@9076
    55
	<variable name="SUM_LINEAMT" class="java.math.BigDecimal" resetType="Report" calculation="Sum">
harpreet@9076
    56
		<variableExpression><![CDATA[$F{AMOUNT}]]></variableExpression>
harpreet@9076
    57
	</variable>
harpreet@9076
    58
		<background>
harpreet@9076
    59
			<band height="0"  isSplitAllowed="true" >
harpreet@9076
    60
			</band>
harpreet@9076
    61
		</background>
harpreet@9076
    62
		<title>
harpreet@9076
    63
			<band height="0"  isSplitAllowed="true" >
harpreet@9076
    64
			</band>
harpreet@9076
    65
		</title>
harpreet@9076
    66
		<pageHeader>
harpreet@9076
    67
			<band height="0"  isSplitAllowed="true" >
harpreet@9076
    68
			</band>
harpreet@9076
    69
		</pageHeader>
harpreet@9076
    70
		<columnHeader>
harpreet@9076
    71
			<band height="0"  isSplitAllowed="true" >
harpreet@9076
    72
			</band>
harpreet@9076
    73
		</columnHeader>
harpreet@9076
    74
		<detail>
harpreet@9076
    75
			<band height="15"  isSplitAllowed="true" >
harpreet@9076
    76
				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
harpreet@9076
    77
					<reportElement
harpreet@9076
    78
						x="0"
harpreet@9076
    79
						y="0"
harpreet@9076
    80
						width="70"
harpreet@9076
    81
						height="15"
harpreet@9076
    82
						forecolor="#000000"
harpreet@9076
    83
						key="textField"/>
harpreet@9076
    84
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" leftPadding="5" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
harpreet@9076
    85
					<textElement textAlignment="Left" verticalAlignment="Middle">
harpreet@9076
    86
						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
harpreet@9076
    87
					</textElement>
harpreet@9076
    88
				<textFieldExpression   class="java.util.Date"><![CDATA[$F{TRANS_DATE}]]></textFieldExpression>
harpreet@9076
    89
				</textField>
harpreet@9076
    90
				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
harpreet@9076
    91
					<reportElement
harpreet@9076
    92
						x="70"
harpreet@9076
    93
						y="0"
harpreet@9076
    94
						width="188"
harpreet@9076
    95
						height="15"
harpreet@9076
    96
						forecolor="#000000"
harpreet@9076
    97
						key="textField"/>
harpreet@9076
    98
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" leftPadding="5" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
harpreet@9076
    99
					<textElement textAlignment="Left" verticalAlignment="Middle">
harpreet@9076
   100
						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
harpreet@9076
   101
					</textElement>
harpreet@9076
   102
				<textFieldExpression   class="java.lang.String"><![CDATA[$F{DESCRIPTION}]]></textFieldExpression>
harpreet@9076
   103
				</textField>
harpreet@9076
   104
				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
harpreet@9076
   105
					<reportElement
harpreet@9076
   106
						x="258"
harpreet@9076
   107
						y="0"
harpreet@9076
   108
						width="122"
harpreet@9076
   109
						height="15"
harpreet@9076
   110
						forecolor="#000000"
harpreet@9076
   111
						key="textField"/>
harpreet@9076
   112
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" leftPadding="5" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>
harpreet@9076
   113
					<textElement textAlignment="Left" verticalAlignment="Middle">
harpreet@9076
   114
						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
harpreet@9076
   115
					</textElement>
harpreet@9076
   116
				<textFieldExpression   class="java.lang.String"><![CDATA[$F{REF}]]></textFieldExpression>
harpreet@9076
   117
				</textField>
harpreet@9076
   118
				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
harpreet@9076
   119
					<reportElement
harpreet@9076
   120
						x="380"
harpreet@9076
   121
						y="0"
harpreet@9076
   122
						width="115"
harpreet@9076
   123
						height="15"
harpreet@9076
   124
						forecolor="#000000"
harpreet@9076
   125
						key="textField"/>
harpreet@9076
   126
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" rightPadding="5" bottomBorder="None" bottomBorderColor="#000000"/>
harpreet@9076
   127
					<textElement textAlignment="Right" verticalAlignment="Middle">
harpreet@9076
   128
						<font size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" isStrikeThrough="false" />
harpreet@9076
   129
					</textElement>
harpreet@9076
   130
				<textFieldExpression   class="java.lang.String"><![CDATA[($F{AMOUNT}!=null) ? $P{NUMBERFORMAT}.format($F{AMOUNT}).toString() : new String(" ")]]></textFieldExpression>
harpreet@9076
   131
				</textField>
harpreet@9076
   132
			</band>
harpreet@9076
   133
		</detail>
harpreet@9076
   134
		<columnFooter>
harpreet@9076
   135
			<band height="0"  isSplitAllowed="true" >
harpreet@9076
   136
			</band>
harpreet@9076
   137
		</columnFooter>
harpreet@9076
   138
		<pageFooter>
harpreet@9076
   139
			<band height="0"  isSplitAllowed="true" >
harpreet@9076
   140
			</band>
harpreet@9076
   141
		</pageFooter>
harpreet@9076
   142
		<summary>
harpreet@9076
   143
			<band height="25"  isSplitAllowed="true" >
harpreet@9076
   144
				<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Report" hyperlinkType="None"  hyperlinkTarget="Self" >
harpreet@9076
   145
					<reportElement
harpreet@9076
   146
						x="380"
harpreet@9076
   147
						y="0"
harpreet@9076
   148
						width="115"
harpreet@9076
   149
						height="15"
harpreet@9076
   150
						key="textField-2"/>
harpreet@9076
   151
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" rightPadding="5" bottomBorder="None" bottomBorderColor="#000000"/>
harpreet@9076
   152
					<textElement textAlignment="Right" verticalAlignment="Middle">
harpreet@9076
   153
						<font size="8"/>
harpreet@9076
   154
					</textElement>
harpreet@9076
   155
				<textFieldExpression   class="java.lang.String"><![CDATA[($V{SUM_LINEAMT}!=null) ? $P{NUMBERFORMAT}.format($V{SUM_LINEAMT}).toString() : new String(" ")]]></textFieldExpression>
harpreet@9076
   156
				</textField>
harpreet@9076
   157
				<staticText>
harpreet@9076
   158
					<reportElement
harpreet@9076
   159
						x="211"
harpreet@9076
   160
						y="0"
harpreet@9076
   161
						width="169"
harpreet@9076
   162
						height="15"
harpreet@9076
   163
						key="staticText-1"/>
harpreet@9076
   164
					<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" rightPadding="5" bottomBorder="None" bottomBorderColor="#000000"/>
harpreet@9076
   165
					<textElement textAlignment="Right" verticalAlignment="Middle">
harpreet@9076
   166
						<font size="8" isBold="false"/>
harpreet@9076
   167
					</textElement>
harpreet@9076
   168
				<text><![CDATA[Total Outstanding Payments]]></text>
harpreet@9076
   169
				</staticText>
harpreet@9076
   170
			</band>
harpreet@9076
   171
		</summary>
harpreet@9076
   172
</jasperReport>