#53 Error in Inventory Valuation Report

open
nobody
None
9
2014-08-14
2009-04-30
Cristi P
No

When I run the Inventory Valuation Report, I get in console this error:

13:25:48.687 InventoryValue.doIt: Inserted Std=-1 [56]
13:25:48.687 InventoryValue.doIt: Constants=0 [56]
===========> DB.executeUpdate: INSERT INTO T_InventoryValue (AD_PInstance_ID, M_Warehouse_ID, M_Product_ID, M_AttributeSetInstance_ID, AD_Client_ID, AD_Org_ID, CostStandard) SELECT 1515233, w.M_Warehouse_ID, c.M_Product_ID, c.M_AttributeSetInstance_ID, w.AD_Client_ID, w.AD_Org_ID, c.CurrentCostPrice FROM M_Warehouse w INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID) INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID) INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID)) INNER JOIN M_CostElement ce ON (c.M_CostElement_ID=ce.M_CostElement_ID AND ce.CostingMethod='S' AND ce.CostElementType='M') WHERE w.M_Warehouse_ID=1000067 [SvrProcess_5e179f20-e4ae-451a-82e8-b7dd5ff78230] [56]
java.sql.SQLException: ORA-00001: unique constraint (ADEMPIERE.T_INVENTORYVALUE_KEY) violated
; State=23000; ErrorCode=1
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4185)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:9457)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3379)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3462)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at sun.reflect.GeneratedMethodAccessor20.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.compiere.db.StatementProxy.invoke(StatementProxy.java:100)
at $Proxy0.executeUpdate(Unknown Source)
at org.compiere.util.DB.executeUpdate(DB.java:933)
at org.compiere.util.DB.executeUpdate(DB.java:856)
at org.compiere.process.InventoryValue.doIt(InventoryValue.java:114)
at org.compiere.process.SvrProcess.process(SvrProcess.java:147)
at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:105)

===========> DB.saveError: DBExecuteError - ORA-00001: unique constraint (ADEMPIERE.T_INVENTORYVALUE_KEY) violated
[56]

Best regards,
CristiP - www.arhipac.ro

Discussion

  • Cristi P
    Cristi P
    2009-04-30

    • priority: 5 --> 7
     
  • Cristi P
    Cristi P
    2009-04-30

    This file solve the problem in Oracle.

     
  • Cristi P
    Cristi P
    2009-04-30

    This file solve the problem in PostgreSQL.

     
  • Carlos Ruiz
    Carlos Ruiz
    2009-04-30

    Hi Cristi,

    Primary key of t_inventoryvalue is:
    ad_pinstance_id, m_warehouse_id, m_product_id, m_attributesetinstance_id

    Please execute the query being inserted:

    + "SELECT ").append(getAD_PInstance_ID())
    .append(", w.M_Warehouse_ID, c.M_Product_ID, c.M_AttributeSetInstance_ID,"
    + " w.AD_Client_ID, w.AD_Org_ID, c.CurrentCostPrice "
    + "FROM M_Warehouse w"
    + " INNER JOIN AD_ClientInfo ci ON (w.AD_Client_ID=ci.AD_Client_ID)"
    + " INNER JOIN C_AcctSchema acs ON (ci.C_AcctSchema1_ID=acs.C_AcctSchema_ID)"
    + " INNER JOIN M_Cost c ON (acs.C_AcctSchema_ID=c.C_AcctSchema_ID AND acs.M_CostType_ID=c.M_CostType_ID AND c.AD_Org_ID IN (0, w.AD_Org_ID))"
    + " INNER JOIN M_CostElement ce ON (c.M_CostElement_ID=ce.M_CostElement_ID AND ce.CostingMethod='S' AND ce.CostElementType='M') "
    + "WHERE w.M_Warehouse_ID=").append(p_M_Warehouse_ID);

    And check the cause of returning duplicate values for such columns.

    It could be due to errors in your data (i.e. duplicated standard costs) - or a bug in the query.

    Regards,

    Carlos Ruiz

     
  • Carlos Ruiz
    Carlos Ruiz
    2009-04-30

    • milestone: --> 705909
    • priority: 7 --> 9
     
  • Carlos Ruiz
    Carlos Ruiz
    2009-04-30

    Hmmm - Just reviewed your patches.

    I don't understand them - currently the primary key on 342 and 353 is the same you're creating.

    So, maybe it was a customization in your database which caused this.

    Regards,

    Carlos Ruiz

     
  • Carlos Ruiz
    Carlos Ruiz
    2009-04-30

    • labels: 897033 -->
    • milestone: 705909 -->
     
  • Hi Cristi!

    I think that current report do not is useful, So I think that is best create a new implementation, so this are some the issue that I found.

    1. When you try get a report with Valuation date different to today the system calculate the qty on hand for different, and should use the M_Transaction to calculate.

    2.-You only can get a report the Material Cost Element Type, if you need get for all your element get the current error.

    3.-If you need the valuation to all your warehouse it do not is possible, then if you have 5 warehouse you need run 5 times the report and sum manually the total each report.

    4.-If you need a valuation with before date the system have not take the MCostDetail to get the history cost. so it report do not is right because is show with the current cost

    So I think we should create a new implantation for a more useful.

    What do you think?
    Victor Perez
    www.e-evolution.com