0035415: 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

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

related to	design defect	0032493		closed	vmromanos	Openbravo ERP	Pareto Product Report takes too long in an environment with big amount of data
depends on	backport	0035479	3.0PR17Q1	closed	dmiguelez	Openbravo ERP	Generate Aggregated Data Background doesn't work in Oracle
caused by	design defect	0031874		closed	Triage Omni OMS	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