details: https://code.openbravo.com/erp/devel/pi/rev/562b56e3da87 changeset: 21180:562b56e3da87 user: Pandeeswari Ramakrishnan <pandeeswari.ramakrishnan <at> openbravo.com> date: Sun Sep 22 15:09:06 2013 +0530 summary: Fixes Issue 24664: Customer Statement report is not working on ORACLE
details: https://code.openbravo.com/erp/devel/pi/rev/adbac66770f0 changeset: 21181:adbac66770f0 user: Pandeeswari Ramakrishnan <pandeeswari.ramakrishnan <at> openbravo.com> date: Sun Sep 22 19:12:38 2013 +0530 summary: Fixes Issue 24784: Missing 'Join' in UpdateTransactionBPExchangeRate modulescript diffstat: modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRate_data.xsql | 14 +++-- modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRateData.java | 12 ++- src/org/openbravo/erpCommon/ad_reports/CustomerStatement.jrxml | 26 +++++----- 3 files changed, 28 insertions(+), 24 deletions(-) diffs (145 lines): diff -r 57d7ed05f9b6 -r adbac66770f0 modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRate_data.xsql --- a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRate_data.xsql Sun Sep 22 15:14:43 2013 +0530 +++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRate_data.xsql Sun Sep 22 19:12:38 2013 +0530 @@ -12,7 +12,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2012 Openbravo SLU + * All portions are Copyright (C) 2012-2013 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* @@ -32,8 +32,9 @@ <Sql> <![CDATA[ SELECT COUNT(1) AS NAME FROM DUAL - WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION, FIN_PAYMENT - WHERE FIN_FINACC_TRANSACTION.C_BPARTNER_ID IS NULL AND FIN_PAYMENT.C_BPARTNER_ID IS NOT NULL) + WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION FT + LEFT JOIN FIN_PAYMENT FP ON FP.FIN_PAYMENT_ID = FT.FIN_PAYMENT_ID + WHERE FT.C_BPARTNER_ID IS NULL AND FP.C_BPARTNER_ID IS NOT NULL) ]]> </Sql> </SqlMethod> @@ -42,9 +43,10 @@ <Sql> <![CDATA[ UPDATE FIN_FINACC_TRANSACTION SET C_BPARTNER_ID = (SELECT C_BPARTNER_ID FROM FIN_PAYMENT WHERE FIN_PAYMENT_ID = FIN_FINACC_TRANSACTION.FIN_PAYMENT_ID) - WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION FT, FIN_PAYMENT - WHERE FT.C_BPARTNER_ID IS NULL AND FIN_PAYMENT.C_BPARTNER_ID IS NOT NULL - AND FT.FIN_FINACC_TRANSACTION_ID = FIN_FINACC_TRANSACTION.FIN_FINACC_TRANSACTION_ID) + WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION FT + LEFT JOIN FIN_PAYMENT FP ON FP.FIN_PAYMENT_ID = FT.FIN_PAYMENT_ID + WHERE FT.C_BPARTNER_ID IS NULL AND FP.C_BPARTNER_ID IS NOT NULL + AND FT.FIN_FINACC_TRANSACTION_ID = FIN_FINACC_TRANSACTION.FIN_FINACC_TRANSACTION_ID) ]]> </Sql> </SqlMethod> diff -r 57d7ed05f9b6 -r adbac66770f0 modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRateData.java --- a/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRateData.java Sun Sep 22 15:14:43 2013 +0530 +++ b/modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/src/org/openbravo/advpaymentmngt/modulescript/UpdateTransactionBPExchangeRateData.java Sun Sep 22 19:12:38 2013 +0530 @@ -87,8 +87,9 @@ String strSql = ""; strSql = strSql + " SELECT COUNT(1) AS NAME FROM DUAL" + - " WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION, FIN_PAYMENT " + - " WHERE FIN_FINACC_TRANSACTION.C_BPARTNER_ID IS NULL AND FIN_PAYMENT.C_BPARTNER_ID IS NOT NULL)"; + " WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION FT" + + " LEFT JOIN FIN_PAYMENT FP ON FP.FIN_PAYMENT_ID = FT.FIN_PAYMENT_ID " + + " WHERE FT.C_BPARTNER_ID IS NULL AND FP.C_BPARTNER_ID IS NOT NULL)"; ResultSet result; boolean boolReturn = false; @@ -122,9 +123,10 @@ String strSql = ""; strSql = strSql + " UPDATE FIN_FINACC_TRANSACTION SET C_BPARTNER_ID = (SELECT C_BPARTNER_ID FROM FIN_PAYMENT WHERE FIN_PAYMENT_ID = FIN_FINACC_TRANSACTION.FIN_PAYMENT_ID)" + - " WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION FT, FIN_PAYMENT " + - " WHERE FT.C_BPARTNER_ID IS NULL AND FIN_PAYMENT.C_BPARTNER_ID IS NOT NULL" + - " AND FT.FIN_FINACC_TRANSACTION_ID = FIN_FINACC_TRANSACTION.FIN_FINACC_TRANSACTION_ID)"; + " WHERE EXISTS(SELECT 1 FROM FIN_FINACC_TRANSACTION FT " + + " LEFT JOIN FIN_PAYMENT FP ON FP.FIN_PAYMENT_ID = FT.FIN_PAYMENT_ID" + + " WHERE FT.C_BPARTNER_ID IS NULL AND FP.C_BPARTNER_ID IS NOT NULL" + + " AND FT.FIN_FINACC_TRANSACTION_ID = FIN_FINACC_TRANSACTION.FIN_FINACC_TRANSACTION_ID)"; int updateCount = 0; PreparedStatement st = null; diff -r 57d7ed05f9b6 -r adbac66770f0 src/org/openbravo/erpCommon/ad_reports/CustomerStatement.jrxml --- a/src/org/openbravo/erpCommon/ad_reports/CustomerStatement.jrxml Sun Sep 22 15:14:43 2013 +0530 +++ b/src/org/openbravo/erpCommon/ad_reports/CustomerStatement.jrxml Sun Sep 22 19:12:38 2013 +0530 @@ -2,9 +2,9 @@ <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"; name="Customer Statement" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"> <property name="ireport.scriptlethandling" value="0"/> <property name="ireport.encoding" value="UTF-8"/> - <property name="ireport.zoom" value="4.0"/> - <property name="ireport.x" value="869"/> - <property name="ireport.y" value="547"/> + <property name="ireport.zoom" value="1.0"/> + <property name="ireport.x" value="0"/> + <property name="ireport.y" value="0"/> <import value="net.sf.jasperreports.engine.*"/> <import value="java.util.*"/> <import value="org.openbravo.erpCommon.ReportsUtility"/> @@ -68,7 +68,7 @@ <defaultValueExpression><![CDATA[$P{REPORT_FORMAT_FACTORY}.createDateFormat("", $P{REPORT_LOCALE}, $P{REPORT_TIME_ZONE})]]></defaultValueExpression> </parameter> <queryString> - <![CDATA[SELECT trunc(f.dateacct) as date, + <![CDATA[SELECT trunc(f.dateacct) as dateacct, CASE WHEN f.c_doctype_id IS NOT NULL THEN (SELECT MIN(name) FROM c_doctype WHERE c_doctype_id = f.c_doctype_id) @@ -82,7 +82,7 @@ f.amtacctcr as credit, (f.amtacctdr-f.amtacctcr) as net, bp.name as bpname, o.ad_org_id as organizationid, - loc.address1 || COALESCE(' ' || loc.address2, '') as org_address, + loc.address1 || ' ' || COALESCE(TO_CHAR(loc.address2), TO_CHAR('')) as org_address, loc.city || ', ' || ad_column_identifier('C_Region', loc.c_region_id, 'en_US') || ' ' || loc.postal || ' ' || ad_column_identifier('C_Country', loc.c_country_id, 'en_US') AS org_postal, (SELECT MAX(email) FROM ad_user WHERE ad_user_id=oinfo.ad_user_id) as email, @@ -109,16 +109,16 @@ AND c_acctschema_id = $P{C_AcctSchema_ID}))) AND f.c_bpartner_id = $P{C_BPartner_ID} AND f.c_acctschema_id = $P{C_AcctSchema_ID} - AND CASE WHEN ($P{DateFrom} IS NULL OR $P{DateFrom}='') THEN trunc(f.dateacct) >= TO_DATE('01-01-0001') - ELSE trunc(f.dateacct) >=TO_DATE($P{DateFrom}) END - AND CASE WHEN ($P{DateTo} IS NULL OR $P{DateTo}='') THEN trunc(f.dateacct) <= TO_DATE('09-09-9999') - ELSE trunc(f.dateacct) <=TO_DATE($P{DateTo}) END + AND trunc(f.dateacct) >= (CASE WHEN ($P{DateFrom} IS NULL OR $P{DateFrom}='') THEN TO_DATE('01-01-0001') + ELSE TO_DATE($P{DateFrom}) END) + AND trunc(f.dateacct) <= (CASE WHEN ($P{DateTo} IS NULL OR $P{DateTo}='') THEN TO_DATE('09-09-9999') + ELSE TO_DATE($P{DateTo}) END) AND f.ad_table_id IN ('318','D1A97202E832470285C9B1EB026D54E2','4D8C3B3C31D1410DA046140C9F024D17','B1B7075C46934F0A9FD4C4D0F1457B42') ORDER BY f.dateacct, f.created]]> </queryString> <field name="org_address" class="java.lang.String"/> <field name="org_postal" class="java.lang.String"/> - <field name="date" class="java.util.Date"/> + <field name="dateacct" class="java.util.Date"/> <field name="organizationid" class="java.lang.String"/> <field name="documenttype" class="java.lang.String"/> <field name="description" class="java.lang.String"/> @@ -132,10 +132,10 @@ <variableExpression><![CDATA[$F{net}]]></variableExpression> </variable> <variable name="DateFrom" class="java.util.Date" calculation="Lowest"> - <variableExpression><![CDATA[$F{date}]]></variableExpression> + <variableExpression><![CDATA[$F{dateacct}]]></variableExpression> </variable> <variable name="DateTo" class="java.util.Date" calculation="Highest"> - <variableExpression><![CDATA[$F{date}]]></variableExpression> + <variableExpression><![CDATA[$F{dateacct}]]></variableExpression> </variable> <variable name="initialBalance" class="java.math.BigDecimal"> <variableExpression><![CDATA[ReportsUtility.getBeginningBalance($P{AD_Org_ID}, $P{C_AcctSchema_ID}, $P{C_BPartner_ID}, $P{DateFrom})]]></variableExpression> @@ -329,7 +329,7 @@ <font fontName="DejaVu Sans" size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[DateFormat.getDateInstance(DateFormat.SHORT, - $P{LOCALE}).format($F{date})]]></textFieldExpression> + $P{LOCALE}).format($F{dateacct})]]></textFieldExpression> </textField> <textField isStretchWithOverflow="true" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" x="47" y="0" width="81" height="14"/> ------------------------------------------------------------------------------ LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99! 1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint 2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13. http://pubads.g.doubleclick.net/gampad/clk?id=58041151&iu=/4140/ostg.clktrk _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
