Cannot correct Inventory OnHand ORA-01861

Description

General Description: When complete a Material Receipt an its contains a least one product with Attribute Set the next error is thrown

"Cannot correct Inventory OnHand [1] - DBExecuteError:ORA-01861: literal does not match format string"

Steps:

  • With GardenAdmin

  1. Create a new Material Receipt

  2. Add new Receipt Line

  3. Choose the product "pchar - Patio Chair"

  4. In Attribute Set Instance, set a serial number

  5. Save the Material Receipt

  6. In Document Action select Complete

After press OK button, the error is thrown: 10000511: (#1) - Cannot correct Inventory OnHand [PChair] - DBExecuteError:ORA-01861: el literal no coincide con la cadena de formato

I have read the issue with other user in google groups: https://groups.google.com/forum/#!topic/idempiere/BqzMfjFrJRY

The log:

Grave: UPDATE M_StorageOnHand SET QtyOnHand=?,Updated=?,UpdatedBy=? WHERE DateMaterialPolicy='2014-08-01 00:00:00.0' AND M_AttributeSetInstance_ID=1000000 AND M_Locator_ID=101 AND M_Product_ID=133 [WFP_40f55c84-eec6-4d72-878d-a2b38b7acde0]
java.sql.SQLDataException: ORA-01861: el literal no coincide con la cadena de formato

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4644)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:10410)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3574)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.compiere.db.StatementProxy.invoke(StatementProxy.java:128)
at com.sun.proxy.$Proxy0.executeUpdate(Unknown Source)
at org.compiere.util.DB.executeUpdate(DB.java:1039)
at org.compiere.util.DB.executeUpdate(DB.java:1008)
at org.compiere.model.PO.doUpdate(PO.java:2658)
at org.compiere.model.PO.saveUpdate(PO.java:2391)
at org.compiere.model.PO.save(PO.java:2143)
at org.compiere.model.PO.save(PO.java:2318)
at org.compiere.model.MStorageOnHand.add(MStorageOnHand.java:743)
at org.compiere.model.MInOut.completeIt(MInOut.java:1427)
at org.compiere.process.DocumentEngine.completeIt(DocumentEngine.java:494)
at org.compiere.process.DocumentEngine.processIt(DocumentEngine.java:311)
at org.compiere.process.DocumentEngine.processIt(DocumentEngine.java:275)
at org.compiere.model.MInOut.processIt(MInOut.java:1078)
at org.compiere.wf.MWFActivity.performWork(MWFActivity.java:987)
at org.compiere.wf.MWFActivity.run(MWFActivity.java:874)
at org.compiere.wf.MWFProcess.startNext(MWFProcess.java:373)
at org.compiere.wf.MWFProcess.checkActivities(MWFProcess.java:283)
at org.compiere.wf.MWFActivity.setWFState(MWFActivity.java:277)
at org.compiere.wf.MWFActivity.run(MWFActivity.java:891)
at org.compiere.wf.MWFProcess.startNext(MWFProcess.java:373)
at org.compiere.wf.MWFProcess.checkActivities(MWFProcess.java:283)
at org.compiere.wf.MWFActivity.setWFState(MWFActivity.java:277)
at org.compiere.wf.MWFActivity.run(MWFActivity.java:891)
at org.compiere.wf.MWFProcess.startWork(MWFProcess.java:505)
at org.compiere.wf.MWorkflow.start(MWorkflow.java:687)
at org.adempiere.util.ProcessUtil.startWorkFlow(ProcessUtil.java:313)
at org.compiere.apps.AbstractProcessCtl.startWorkflow(AbstractProcessCtl.java:391)
at org.compiere.apps.AbstractProcessCtl.run(AbstractProcessCtl.java:209)
at org.adempiere.webui.apps.WProcessCtl.process(WProcessCtl.java:170)
at org.adempiere.webui.apps.AbstractProcessDialog$ProcessDialogRunnable.doRun(AbstractProcessDialog.java:631)
at org.adempiere.util.ContextRunnable.run(ContextRunnable.java:38)
at org.adempiere.webui.apps.DesktopRunnable.run(DesktopRunnable.java:40)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)

Solution:

The root problem is the date format incompatibility between idempiere/java and my oracle database configuration.

I have modified PO.java from org.adempiere.base, creating a specific process to Timestamp Id

I haven't tested it with postgresql, but reading the documentation the code is compatible

org/compiere/model/PO.java

Environment

java.version=1.7.0_51
OS=win32 & Linux, ARCH=x86_64, NL=es_MX
Database: Oracle 11.2.0.2

Assignee

Carlos Ruiz

Reporter

David Garcia

Labels

Tested By

None

Components

Affects versions

Priority

Minor
Configure