Anonymous | Login
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] 04. Warehouse managementmajorhave not tried2017-03-02 17:382017-03-10 12:16
ReportervmromanosView Statuspublic 
Assigned Todmiguelez 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision89dd0dcb369f
ProjectionnoneETAnoneTarget Version3.0PR17Q1
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Todmiguelez
Web browser
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo

0035479: Generate Aggregated Data Background doesn't work in Oracle

DescriptionThe Generate Aggregated Data Background process doesn't work in Oracle. It raises the following exception:

7ec67d76 473236 [DefaultQuartzScheduler_Worker-6] ERROR org.openbravo.materialmgmt.ResetValuedStockAggregated - Error in insertValuesIntoValuedStockAggregated() method of ResetValuedStockAggregated class
javax.servlet.ServletException: @CODE=907@ORA-00907: falta el paréntesis derecho

    at org.openbravo.materialmgmt.GenerateValuedStockAggregatedData.insertData(
    at org.openbravo.materialmgmt.ResetValuedStockAggregated.insertValuesIntoValuedStockAggregated(
    at org.openbravo.materialmgmt.GenerateAggregatedDataBackground.doExecute(
    at org.openbravo.service.db.DalBaseProcess.execute(

Attached query for reference:
INTO m_valued_stock_agg
  'Y' AS active,
  SUM(stock) AS stock,
  SUM(valuation) AS valuation,
  (SELECT ad_client_id,
    MAX(createdby) AS createdby,
    MAX(updatedby) AS updatedby,
    SUM(movementqty) AS stock,
    SUM(a.trxcost) AS valuation
    (SELECT trx.ad_client_id,
      0 AS trxcost
    FROM m_transaction trx
    WHERE trx.movementdate <= TO_DATE(?)
    AND 2 =2
    AND trx.movementdate > TO_DATE(?)
    AND 4 =4
    AND trx.trxprocessdate >= to_timestamp(?, 'DD-MM-YYYY HH24:MI:SS')
    AND 5 =5
    AND trx.ad_client_id IN (?)
    AND trx.ad_org_id IN ('0', '7BABA5FF80494CAFA54DEBD22EC46F01', 'BAE22373FEBE4CCCA24517E23F0C8A48', '19404EAD144C49A0AF37D54377CF452D', '2E60544D37534C0B89E765FE29BC0B43')
    SELECT t.ad_client_id,
      0 AS movementqty,
      CASE t.iscostcalculated
        WHEN 'Y'
        THEN C_CURRENCY_CONVERT_PRECISION (tc.trxcost, tc.c_currency_id, ?, tc.movementdate, NULL, t.ad_client_id, ?,'C')
        ELSE NULL
      END AS trxcost
      (SELECT SUM(
          WHEN t2.movementqty < 0
          THEN -cost
          ELSE cost
        END) AS trxcost,
        COALESCE(dateacct, costdate) AS movementdate
      FROM m_transaction_cost tc2
      JOIN m_transaction t2
      ON (tc2.m_transaction_id = t2.m_transaction_id)
      WHERE dateacct <= TO_DATE(?)
      AND 3 =3
      AND dateacct > TO_DATE(?)
      AND 6 =6
      AND t2.trxprocessdate >= to_timestamp(?, 'DD-MM-YYYY HH24:MI:SS')
      AND 7 =7
      AND t2.ad_client_id IN (?)
      AND t2.ad_org_id IN ('0', '7BABA5FF80494CAFA54DEBD22EC46F01', 'BAE22373FEBE4CCCA24517E23F0C8A48', '19404EAD144C49A0AF37D54377CF452D', '2E60544D37534C0B89E765FE29BC0B43')
      GROUP BY t2.m_transaction_id,
        COALESCE(dateacct, costdate)
      ) tc
    LEFT JOIN m_transaction t
    ON (t.m_transaction_id = tc.m_transaction_id)
    ) AS a
  GROUP BY a.ad_client_id,
  SELECT ad_client_id,
    MAX(createdby) AS createdby,
    MAX(updatedby) AS updatedby,
    SUM(stock) AS stock,
    SUM(valuation) AS valuation
  FROM m_valued_stock_agg agg
  WHERE ad_org_id IN ('0', '7BABA5FF80494CAFA54DEBD22EC46F01', 'BAE22373FEBE4CCCA24517E23F0C8A48', '19404EAD144C49A0AF37D54377CF452D', '2E60544D37534C0B89E765FE29BC0B43')
  AND dateto =
    (SELECT MAX(agg2.dateto)
    FROM m_valued_stock_agg agg2
    WHERE agg2.dateto <= TO_DATE(?)
    AND agg2.ad_org_id IN ('0', '7BABA5FF80494CAFA54DEBD22EC46F01', 'BAE22373FEBE4CCCA24517E23F0C8A48', '19404EAD144C49A0AF37D54377CF452D', '2E60544D37534C0B89E765FE29BC0B43')
    AND m_costing_rule_id = ?
  GROUP BY ad_client_id,
  ) AS B
GROUP BY ad_client_id,
Steps To ReproduceIn an Oracle instance, log as Openbravo Admin.
Go to Process Request window.
Create a new record:
 Org: *
 Process: Generate Aggregated Data Background
 Timing: Run Immediately
Schedule the process.
See exceptin in openbravo.log (not visible in Process Monitor)
Verify that m_valued_stock_agg table has no records.

Proposed SolutionNote that this works fine in PG. Verify it keeps on working in PG after fixing this issue
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
blocks defect 0035415 closeddmiguelez Generate Aggregated Data Background doesn't work in Oracle 

-  Notes
hgbot (developer)
2017-03-10 12:15

Repository: erp/backports/3.0PR17Q1
Changeset: 89dd0dcb369fc0f6d36716629ddc009c5bb2d438
Author: David Miguelez <david.miguelez <at>>
Date: Fri Mar 10 12:14:46 2017 +0100
URL: [^]

Fixes Issue 35479. Fixes problem while setting alias in a subquery in Oracle.

M src/org/openbravo/materialmgmt/GenerateValuedStockAggregated_data.xsql
dmiguelez (developer)
2017-03-10 12:16

Code Review + Testing Ok

- Issue History
Date Modified Username Field Change
2017-03-10 11:47 dmiguelez Type defect => backport
2017-03-10 11:47 dmiguelez Target Version => 3.0PR17Q1
2017-03-10 12:15 hgbot Checkin
2017-03-10 12:15 hgbot Note Added: 0094908
2017-03-10 12:15 hgbot Status scheduled => resolved
2017-03-10 12:15 hgbot Resolution open => fixed
2017-03-10 12:15 hgbot Fixed in SCM revision => [^]
2017-03-10 12:16 dmiguelez Review Assigned To => dmiguelez
2017-03-10 12:16 dmiguelez Note Added: 0094911
2017-03-10 12:16 dmiguelez Status resolved => closed

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker