Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0035415
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 04. Warehouse managementmajorhave not tried2017-03-02 17:382017-03-15 20:21
ReportervmromanosView Statuspublic 
Assigned Todmiguelez 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision62fc67af48c0
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Todmiguelez
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035415: 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(GenerateValuedStockAggregatedData.java:219)
    at org.openbravo.materialmgmt.ResetValuedStockAggregated.insertValuesIntoValuedStockAggregated(ResetValuedStockAggregated.java:190)
    at org.openbravo.materialmgmt.GenerateAggregatedDataBackground.doExecute(GenerateAggregatedDataBackground.java:105)
    at org.openbravo.service.db.DalBaseProcess.execute(DalBaseProcess.java:82)



Attached query for reference:
INSERT
INTO m_valued_stock_agg
  (
    m_valued_stock_agg_id,
    ad_client_id,
    ad_org_id,
    isactive,
    created,
    createdby,
    updated,
    updatedby,
    m_product_id,
    m_locator_id,
    c_uom_id,
    c_period_id,
    datefrom,
    dateto,
    stock,
    valuation,
    c_currency_id,
    m_costing_rule_id
  )
SELECT GET_UUID(),
  ad_client_id,
  ?,
  'Y' AS active,
  NOW(),
  MAX(createdby),
  NOW(),
  MAX(updatedby),
  m_product_id,
  m_locator_id,
  c_uom_id,
  ?,
  TO_DATE(?),
  TO_DATE(?),
  SUM(stock) AS stock,
  SUM(valuation) AS valuation,
  ?,
  ?
FROM
  (SELECT ad_client_id,
    MAX(createdby) AS createdby,
    MAX(updatedby) AS updatedby,
    m_product_id,
    m_locator_id,
    c_uom_id,
    SUM(movementqty) AS stock,
    SUM(a.trxcost) AS valuation
  FROM
    (SELECT trx.ad_client_id,
      trx.ad_org_id,
      trx.createdby,
      trx.updatedby,
      trx.m_product_id,
      trx.m_locator_id,
      trx.c_uom_id,
      trx.movementqty,
      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')
    UNION ALL
    SELECT t.ad_client_id,
      t.ad_org_id,
      t.createdby,
      t.updatedby,
      t.m_product_id,
      t.m_locator_id,
      t.c_uom_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
    FROM
      (SELECT SUM(
        CASE
          WHEN t2.movementqty < 0
          THEN -cost
          ELSE cost
        END) AS trxcost,
        t2.m_transaction_id,
        tc2.c_currency_id,
        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,
        tc2.c_currency_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,
    a.m_product_id,
    a.c_uom_id,
    a.m_locator_id
  UNION ALL
  SELECT ad_client_id,
    MAX(createdby) AS createdby,
    MAX(updatedby) AS updatedby,
    m_product_id,
    m_locator_id,
    c_uom_id,
    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,
    ad_org_id,
    m_product_id,
    m_locator_id,
    c_uom_id
  ) AS B
GROUP BY ad_client_id,
  m_product_id,
  m_locator_id,
  c_uom_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 ]
related to design defect 0032493 closedvmromanos Openbravo ERP Pareto Product Report takes too long in an environment with big amount of data 
depends on backport 00354793.0PR17Q1 closeddmiguelez Openbravo ERP Generate Aggregated Data Background doesn't work in Oracle 
caused by design defect 0031874 closedTriage Finance Modules Performance problems in Valued Stock Report Enhanced 

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

Repository: erp/devel/pi
Changeset: 62fc67af48c0e1a92079aa9310834b322d8e223a
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Fri Mar 10 12:13:30 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/62fc67af48c0e1a92079aa9310834b322d8e223a [^]

Fixes Issue 35415. Fixes problem while setting the alias of a subquery in Oracle.

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

Code Review + Testing Ok
(0095288)
hudsonbot (developer)
2017-03-15 20:21

A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/54e102bef53e [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2017-03-02 17:38 vmromanos New Issue
2017-03-02 17:38 vmromanos Assigned To => Triage Finance
2017-03-02 17:38 vmromanos Modules => Core
2017-03-02 17:38 vmromanos Triggers an Emergency Pack => No
2017-03-02 17:40 vmromanos Relationship added caused by 0031874
2017-03-02 17:40 vmromanos Relationship added related to 0032493
2017-03-10 11:46 dmiguelez Assigned To Triage Finance => dmiguelez
2017-03-10 11:47 dmiguelez Status new => scheduled
2017-03-10 12:15 hgbot Checkin
2017-03-10 12:15 hgbot Note Added: 0094909
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 => http://code.openbravo.com/erp/devel/pi/rev/62fc67af48c0e1a92079aa9310834b322d8e223a [^]
2017-03-10 12:16 dmiguelez Review Assigned To => dmiguelez
2017-03-10 12:16 dmiguelez Note Added: 0094910
2017-03-10 12:16 dmiguelez Status resolved => closed
2017-03-15 20:21 hudsonbot Checkin
2017-03-15 20:21 hudsonbot Note Added: 0095288


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker