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

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0035479

Openbravo ERP

04. Warehouse management

public

2017-03-02 17:38

2017-03-10 12:16

Reporter

vmromanos

Assigned To

dmiguelez

Priority

normal

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

3.0PR17Q1

Fixed in Version

Merge Request Status

Review Assigned To

dmiguelez

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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

Description

The 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 Reproduce

In 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 Solution

Note that this works fine in PG. Verify it keeps on working in PG after fixing this issue

Additional Information