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

Notes
(0094908) hgbot (developer) 2017-03-10 12:15	Repository: erp/backports/3.0PR17Q1 Changeset: 89dd0dcb369fc0f6d36716629ddc009c5bb2d438 Author: David Miguelez <david.miguelez <at> openbravo.com> Date: Fri Mar 10 12:14:46 2017 +0100 URL: http://code.openbravo.com/erp/backports/3.0PR17Q1/rev/89dd0dcb369fc0f6d36716629ddc009c5bb2d438 [^] Fixes Issue 35479. Fixes problem while setting alias in a subquery in Oracle. --- M src/org/openbravo/materialmgmt/GenerateValuedStockAggregated_data.xsql ---

(0094911) 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	=> http://code.openbravo.com/erp/backports/3.0PR17Q1/rev/89dd0dcb369fc0f6d36716629ddc009c5bb2d438 [^]
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