Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0002316Openbravo ERP09. Financial managementpublic2007-11-27 13:302008-06-12 09:43
Reportercromero 
Assigned Tocromero 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version2.40alpha-r2 
Merge Request Status
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0002316: General Accounting Report very slow in postgres
DescriptionGeneral Accounting Report is very slow in postgres.

It's supposed to be the "selectPyG" in the GeneralAccountingReportsData file that is:

  <SqlMethod name="selectPyG" type="preparedStatement" return="String" default="">
      <SqlMethodComment></SqlMethodComment>
      <Sql><![CDATA[
      SELECT COALESCE(SUM(AMTACCTCR-AMTACCTDR), 0) AS NAME
      FROM FACT_ACCT, C_PERIOD, C_YEAR
      WHERE FACT_ACCT.C_PERIOD_ID = C_PERIOD.C_PERIOD_ID
      AND C_PERIOD.C_YEAR_ID = C_YEAR.C_YEAR_ID
      AND ACCOUNT_ID IN (SELECT ACCOUNT_ID
              FROM C_ELEMENTVALUE, C_VALIDCOMBINATION
              WHERE C_VALIDCOMBINATION.ACCOUNT_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID
              AND C_ELEMENTVALUE.ACCOUNTTYPE = ?)
      AND FACT_ACCT.FACTACCTTYPE <> 'R'
      AND 1=1
      AND C_YEAR.YEAR = ?
      AND FACT_ACCT.AD_ORG_ID IN ('1')
      ]]></Sql>
        <Parameter name="accountType"/>
        <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT >= TO_DATE(?)]]></Parameter>
        <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT < TO_DATE(?)]]></Parameter>
        <Parameter name="year"/>
        <Parameter name="adOrgId" optional="true" type="replace" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
   </SqlMethod>

and it would work better if it was:

  <SqlMethod name="selectPyG" type="preparedStatement" return="String" default="">
      <SqlMethodComment></SqlMethodComment>
      <Sql><![CDATA[
      SELECT COALESCE(SUM(AMTACCTCR-AMTACCTDR), 0) AS NAME
      FROM FACT_ACCT, C_PERIOD, C_YEAR, (SELECT ACCOUNT_ID
              FROM C_ELEMENTVALUE, C_VALIDCOMBINATION
              WHERE C_VALIDCOMBINATION.ACCOUNT_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID
              AND C_ELEMENTVALUE.ACCOUNTTYPE = ?) AA
      WHERE FACT_ACCT.C_PERIOD_ID = C_PERIOD.C_PERIOD_ID
      AND C_PERIOD.C_YEAR_ID = C_YEAR.C_YEAR_ID
      AND FACT_ACCT.ACCOUNT_ID = AA.ACCOUNT_ID
      AND FACT_ACCT.FACTACCTTYPE <> 'R'
      AND 1=1
      AND C_YEAR.YEAR = ?
      AND FACT_ACCT.AD_ORG_ID IN ('1')
      ]]></Sql>
        <Parameter name="accountType"/>
        <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT >= TO_DATE(?)]]></Parameter>
        <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT < TO_DATE(?)]]></Parameter>
        <Parameter name="year"/>
        <Parameter name="adOrgId" optional="true" type="replace" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
   </SqlMethod>

Carlos Romero
Openbravo Team
Steps To Reproduce
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange

Notes
(0005905)
user71   
2005-06-01 00:00   
(edited on: 2008-06-12 09:43)
This bug was originally reported in SourceForge bug tracker and then migrated to Mantis.

You can see the original bug report in:
https://sourceforge.net/support/tracker.php?aid=1839432 [^]
(0002723)
cromero   
2008-03-05 17:23   
(edited on: 2008-06-12 09:23)
Logged In: YES
user_id=1500614
Originator: YES

Fixed in revision 2672