Account:

[Henry]

Hi,

After updated the Current Cost, i try to reposting the Matched Invoice. However, we get this error.

Please help !

Thx !

Regards,
Henry Lau

12:06:20,277 ERROR [DB] executeUpdate - UPDATE M_Product_Costing pc SET (CostSta
ndardCumQty,CostStandardCumAmt, CostAverageCumQty,CostAverageCumAmt) = (SELECT p
c.CostStandardCumQty + m.Qty,pc.CostStandardCumAmt + C_Currency_Convert(il.Price
Actual,i.C_Currency_ID,a.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD
_Client_ID,i.AD_Org_ID)*m.Qty, pc.CostAverageCumQty + m.Qty,pc.CostAverageCumAmt
+ C_Currency_Convert(il.PriceActual,i.C_Currency_ID,a.C_Currency_ID,i.DateInvoi
ced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*m.Qty FROM M_MatchInv m IN
NER JOIN C_InvoiceLine il ON (m.C_InvoiceLine_ID=il.C_InvoiceLine_ID) INNER JOIN
C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID), C_AcctSchema a WHERE pc.C_Acct
Schema_ID=a.C_AcctSchema_ID AND pc.M_Product_ID=m.M_Product_ID AND m.M_MatchInv_
ID=1003568)WHERE pc.C_AcctSchema_ID=1000000 AND EXISTS (SELECT * FROM M_MatchInv
m WHERE pc.M_Product_ID=m.M_Product_ID AND m.M_MatchInv_ID=1003568) [PostMXI100
3568]
java.sql.SQLException: ORA-01407: cannot update ("COMPIERE"."M_PRODUCT_COSTING".
"COSTSTANDARDCUMAMT") to NULL

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:125)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.
java:181)
        at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedS
tatement.java:543)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1028)
        at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatem
ent.java:3753)
        at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate
(OraclePreparedStatement.java:8829)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:2886)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
edStatement.java:2960)
        at org.compiere.util.CPreparedStatement.executeUpdate(CPreparedStatement
.java:159)
        at org.compiere.util.DB.executeUpdate(DB.java:1288)
        at org.compiere.util.DB.executeUpdate(DB.java:1254)
        at org.compiere.acct.Doc_MatchInv.updateProductInfo(Doc_MatchInv.java:31
2)
        at org.compiere.acct.Doc_MatchInv.createFact(Doc_MatchInv.java:198)
        at org.compiere.acct.Doc.postLogic(Doc.java:513)
        at org.compiere.acct.Doc.post(Doc.java:439)
        at org.compiere.acct.Doc.post(Doc.java:396)
        at org.compiere.acct.Doc.postImmediate(Doc.java:266)
        at org.compiere.session.ServerBean.postImmediate(ServerBean.java:108)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(S
tatelessSessionContainer.java:683)
        at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invo
ke(CachedConnectionInterceptor.java:185)
        at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInte
rceptor.java:84)
        at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxI
nterceptorBMT.java:144)
        at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:6
2)
        at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(Stat
elessSessionInstanceInterceptor.java:72)
        at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.
java:118)
        at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
        at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFacto
ryFinderInterceptor.java:122)
        at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessi
onContainer.java:331)
        at org.jboss.ejb.Container.invoke(Container.java:700)
        at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBea
nDispatcher.java:284)
        at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
        at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:
367)
        at sun.reflect.GeneratedMethodAccessor34.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
        at sun.rmi.transport.Transport$1.run(Transport.java:148)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:4
60)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport
.java:701)
        at java.lang.Thread.run(Thread.java:534)
12:06:20,437 ERROR [Log] DBExecuteError - ORA-01407: cannot update ("COMPIERE"."
M_PRODUCT_COSTING"."COSTSTANDARDCUMAMT") to NULL

12:06:20,848 INFO  [Doc_MatchInv] postCommit Sta=Y DT=MXI ID=1003568
12:06:21,439 INFO  [Trx] commit **** PostMXI1003568

[belozoglu]

Hi,

I dont't know if you are doing the right steps funcionality but in the code level you can correct or be prepared for this error changing the sql:

you must change the code

pc.CostStandardCumAmt + C_Currency_Convert(il.PriceActual,i.C_Currency_ID,a.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*m.Qty,

by

pc.CostStandardCumAmt + COALESCE(C_Currency_Convert(il.PriceActual,i.C_Currency_ID,a.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*m.Qty,0),

and never more it will try to set null the CostStandardCumAmt, because the COALESCE function takes the first not null value from left to right.

Hope this helps.

Jose.