details: https://code.openbravo.com/erp/devel/pi/rev/f96d16904892 changeset: 27887:f96d16904892 user: David Miguelez <david.miguelez <at> openbravo.com> date: Fri Nov 13 13:33:58 2015 +0100 summary: Issue 31080
details: https://code.openbravo.com/erp/devel/pi/rev/54816dff1cfb changeset: 27888:54816dff1cfb user: David Miguelez <david.miguelez <at> openbravo.com> date: Mon Nov 16 12:31:48 2015 +0100 summary: Related to Issue 31080. Code Review changes. Takes into account if a Product is defined as Production when filtering the queries by Organization and Warehouse. diffstat: src-db/database/sourcedata/AD_FIELD.xml | 3 +- src/org/openbravo/costing/CostAdjustmentProcess.java | 38 +- src/org/openbravo/costing/CostAdjustmentUtils.java | 97 +- src/org/openbravo/costing/CostingAlgorithmAdjustmentImp.java | 5 +- src/org/openbravo/costing/CostingServer.java | 132 ++-- src/org/openbravo/costing/CostingUtils.java | 38 + src/org/openbravo/costing/StandardAlgorithm.java | 36 +- src/org/openbravo/costing/StandardCostAdjustment.java | 336 ++++++---- 8 files changed, 418 insertions(+), 267 deletions(-) diffs (truncated from 971 to 300 lines): diff -r dcc7fba68b20 -r 54816dff1cfb src-db/database/sourcedata/AD_FIELD.xml --- a/src-db/database/sourcedata/AD_FIELD.xml Wed Nov 11 17:48:28 2015 +0100 +++ b/src-db/database/sourcedata/AD_FIELD.xml Mon Nov 16 12:31:48 2015 +0100 @@ -239716,7 +239716,7 @@ <!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <AD_COLUMN_ID><![CDATA[0DCB49039F4541E8907BAE1588D7385F]]></AD_COLUMN_ID> <!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <IGNOREINWAD><![CDATA[N]]></IGNOREINWAD> <!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED> -<!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <DISPLAYLOGIC><![CDATA[@Backdatedtrxsfixed@='N' & @isvalidated@='Y' & @M_Costing_Algorithm_ID@!'6A39D8B46CD94FE682D48758D3B7726B']]></DISPLAYLOGIC> +<!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <DISPLAYLOGIC><![CDATA[@Backdatedtrxsfixed@='N' & @isvalidated@='Y']]></DISPLAYLOGIC> <!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <DISPLAYLENGTH><![CDATA[1]]></DISPLAYLENGTH> <!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <ISREADONLY><![CDATA[N]]></ISREADONLY> <!--7EA7829A9B8442B28AA7D433ABE8B1B9--> <SEQNO><![CDATA[60]]></SEQNO> @@ -306505,7 +306505,6 @@ <!--FAA9ED0D2AEA444F83D09DF2FE479785--> <AD_COLUMN_ID><![CDATA[480CC1DA0E0F432CACE9360D07842B65]]></AD_COLUMN_ID> <!--FAA9ED0D2AEA444F83D09DF2FE479785--> <IGNOREINWAD><![CDATA[N]]></IGNOREINWAD> <!--FAA9ED0D2AEA444F83D09DF2FE479785--> <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED> -<!--FAA9ED0D2AEA444F83D09DF2FE479785--> <DISPLAYLOGIC><![CDATA[@M_Costing_Algorithm_ID@!'6A39D8B46CD94FE682D48758D3B7726B']]></DISPLAYLOGIC> <!--FAA9ED0D2AEA444F83D09DF2FE479785--> <DISPLAYLENGTH><![CDATA[1]]></DISPLAYLENGTH> <!--FAA9ED0D2AEA444F83D09DF2FE479785--> <ISREADONLY><![CDATA[N]]></ISREADONLY> <!--FAA9ED0D2AEA444F83D09DF2FE479785--> <SEQNO><![CDATA[50]]></SEQNO> diff -r dcc7fba68b20 -r 54816dff1cfb src/org/openbravo/costing/CostAdjustmentProcess.java --- a/src/org/openbravo/costing/CostAdjustmentProcess.java Wed Nov 11 17:48:28 2015 +0100 +++ b/src/org/openbravo/costing/CostAdjustmentProcess.java Mon Nov 16 12:31:48 2015 +0100 @@ -41,12 +41,15 @@ import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBCriteria; import org.openbravo.dal.service.OBDal; +import org.openbravo.dal.service.OBQuery; import org.openbravo.erpCommon.utility.OBMessageUtils; import org.openbravo.financial.FinancialUtils; import org.openbravo.model.financialmgmt.calendar.Period; import org.openbravo.model.materialmgmt.cost.CostAdjustment; import org.openbravo.model.materialmgmt.cost.CostAdjustmentLine; import org.openbravo.model.materialmgmt.cost.TransactionCost; +import org.openbravo.model.materialmgmt.transaction.InventoryCount; +import org.openbravo.model.materialmgmt.transaction.InventoryCountLine; import org.openbravo.model.materialmgmt.transaction.MaterialTransaction; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -141,19 +144,32 @@ } } + /** + * Permanently adjusted Transactions with and adjustment amount different than zero should not be + * adjusted. The only exception are the Opening Inventories. Due to backdated Transactions that + * can modify the stock or the stock valuation, it is necessary to adjust this Opening Inventories + * to fix rounding issues. + */ + private void checkPermanentelyAdjustedTrx(String strCostAdjId) throws OBException { - OBCriteria<CostAdjustmentLine> critLines = OBDal.getInstance().createCriteria( - CostAdjustmentLine.class); - critLines.createAlias(CostAdjustmentLine.PROPERTY_INVENTORYTRANSACTION, "trx"); - critLines.createAlias(CostAdjustmentLine.PROPERTY_COSTADJUSTMENT, "ca"); - critLines.add(Restrictions.eq("ca.id", strCostAdjId)); - critLines.add(Restrictions.eq("trx." + MaterialTransaction.PROPERTY_ISCOSTPERMANENT, - Boolean.TRUE)); - critLines.add(Restrictions.ne(CostAdjustmentLine.PROPERTY_ADJUSTMENTAMOUNT, BigDecimal.ZERO)); - critLines.add(Restrictions.eq(CostAdjustmentLine.PROPERTY_UNITCOST, Boolean.TRUE)); - critLines.addOrder(Order.asc(CostAdjustmentLine.PROPERTY_LINENO)); + StringBuffer where = new StringBuffer(); + where.append(" as cal"); + where.append(" join cal." + CostAdjustmentLine.PROPERTY_COSTADJUSTMENT + " as ca"); + where.append(" join cal." + CostAdjustmentLine.PROPERTY_INVENTORYTRANSACTION + " as trx"); + where.append(" left join trx." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + " as il"); + where.append(" left join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); + where.append(" where ca." + CostAdjustment.PROPERTY_ID + " = :strCostAdjId"); + where.append(" and coalesce(i." + InventoryCount.PROPERTY_INVENTORYTYPE + ", 'N') <> 'O'"); + where.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTPERMANENT + " = true"); + where.append(" and cal." + CostAdjustmentLine.PROPERTY_ADJUSTMENTAMOUNT + " <> 0"); + where.append(" and cal." + CostAdjustmentLine.PROPERTY_UNITCOST + " = true"); + where.append(" order by cal." + CostAdjustmentLine.PROPERTY_LINENO); - ScrollableResults lines = critLines.scroll(ScrollMode.FORWARD_ONLY); + OBQuery<CostAdjustmentLine> qry = OBDal.getInstance().createQuery(CostAdjustmentLine.class, + where.toString()); + qry.setNamedParameter("strCostAdjId", strCostAdjId); + + ScrollableResults lines = qry.scroll(ScrollMode.FORWARD_ONLY); long count = 1L; try { String strLines = ""; diff -r dcc7fba68b20 -r 54816dff1cfb src/org/openbravo/costing/CostAdjustmentUtils.java --- a/src/org/openbravo/costing/CostAdjustmentUtils.java Wed Nov 11 17:48:28 2015 +0100 +++ b/src/org/openbravo/costing/CostAdjustmentUtils.java Mon Nov 16 12:31:48 2015 +0100 @@ -11,7 +11,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2014 Openbravo SLU + * All portions are Copyright (C) 2014-2015 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************* @@ -49,6 +49,8 @@ import org.openbravo.model.materialmgmt.cost.CostAdjustmentLine; import org.openbravo.model.materialmgmt.cost.CostingRule; import org.openbravo.model.materialmgmt.cost.TransactionCost; +import org.openbravo.model.materialmgmt.transaction.InventoryCount; +import org.openbravo.model.materialmgmt.transaction.InventoryCountLine; import org.openbravo.model.materialmgmt.transaction.MaterialTransaction; import org.openbravo.model.materialmgmt.transaction.ShipmentInOut; import org.openbravo.model.materialmgmt.transaction.ShipmentInOutLine; @@ -249,16 +251,15 @@ Set<String> orgs = OBContext.getOBContext().getOrganizationStructureProvider() .getChildTree(org.getId(), true); - StringBuffer select = new StringBuffer(); StringBuffer subSelect = new StringBuffer(); - select - .append(" select sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") as stock"); - select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); - select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); - select.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product"); - subSelect.append("select min(trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + ")"); + subSelect.append(" select min(case when coalesce(i." + InventoryCount.PROPERTY_INVENTORYTYPE + + ", 'N') <> 'N' then trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " else trx." + + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " end)"); subSelect.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); subSelect.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); + subSelect.append(" left join trx." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + + " as il"); + subSelect.append(" left join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); subSelect.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product"); subSelect.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " > :date"); // Include only transactions that have its cost calculated @@ -275,22 +276,34 @@ trxsubQry.setParameter("warehouse", costDimensions.get(CostDimension.Warehouse).getId()); } trxsubQry.setParameterList("orgs", orgs); + Object trxprocessDate = trxsubQry.uniqueResult(); + + StringBuffer select = new StringBuffer(); + select + .append(" select sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") as stock"); + select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); + select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); + Date backdatedTrxFrom = null; if (backdatedTransactionsFixed) { CostingRule costRule = CostingUtils.getCostDimensionRule(org, date); backdatedTrxFrom = CostingUtils.getCostingRuleFixBackdatedFrom(costRule); } - Object trxprocessDate = trxsubQry.uniqueResult(); if (trxprocessDate != null && (!backdatedTransactionsFixed || ((Date) trxprocessDate).before(backdatedTrxFrom))) { date = (Date) trxprocessDate; - select.append(" and trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE - + " < :date"); + select.append(" left join trx." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + + " as il"); + select.append(" left join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); + select.append(" where case when coalesce(i." + InventoryCount.PROPERTY_INVENTORYTYPE + + ", 'N') <> 'N' then trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " else trx." + + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " end < :date"); } else { - select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <= :date"); + select.append(" where trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <= :date"); } + select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product"); // Include only transactions that have its cost calculated select.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = true"); if (costDimensions.get(CostDimension.Warehouse) != null) { @@ -458,29 +471,15 @@ costDimensions = CostingUtils.getEmptyDimensions(); } - StringBuffer select = new StringBuffer(); StringBuffer subSelect = new StringBuffer(); - select.append(" select sum(case"); - select.append(" when trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY - + " < 0 then -tc." + TransactionCost.PROPERTY_COST); - select.append(" else tc." + TransactionCost.PROPERTY_COST + " end ) as cost"); - select.append(" , tc." + TransactionCost.PROPERTY_CURRENCY + ".id as currency"); - select.append(" , coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx." - + MaterialTransaction.PROPERTY_MOVEMENTDATE + ") as mdate"); - select.append(" , sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") as stock"); - - select.append(" from " + TransactionCost.ENTITY_NAME + " as tc"); - select.append(" join tc." + TransactionCost.PROPERTY_INVENTORYTRANSACTION + " as trx"); - select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); - select.append(" left join trx." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as line"); - select.append(" left join line." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as sr"); - - select.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + " = :product"); - // Include only transactions that have its cost calculated - select.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = true"); - subSelect.append("select min(trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + ")"); + subSelect.append(" select min(case when coalesce(i." + InventoryCount.PROPERTY_INVENTORYTYPE + + ", 'N') <> 'N' then trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " else trx." + + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " end)"); subSelect.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); subSelect.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); + subSelect.append(" left join trx." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + + " as il"); + subSelect.append(" left join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); subSelect.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product"); subSelect.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " > :date"); // Include only transactions that have its cost calculated @@ -497,21 +496,47 @@ trxsubQry.setParameter("warehouse", costDimensions.get(CostDimension.Warehouse).getId()); } trxsubQry.setParameterList("orgs", orgs); + Object trxprocessDate = trxsubQry.uniqueResult(); + + StringBuffer select = new StringBuffer(); + select.append(" select sum(case"); + select.append(" when trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + + " < 0 then -tc." + TransactionCost.PROPERTY_COST); + select.append(" else tc." + TransactionCost.PROPERTY_COST + " end ) as cost"); + select.append(" , tc." + TransactionCost.PROPERTY_CURRENCY + ".id as currency"); + select.append(" , coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx." + + MaterialTransaction.PROPERTY_MOVEMENTDATE + ") as mdate"); + select.append(" , sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") as stock"); + + select.append(" from " + TransactionCost.ENTITY_NAME + " as tc"); + select.append(" join tc." + TransactionCost.PROPERTY_INVENTORYTRANSACTION + " as trx"); + select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); + select.append(" left join trx." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as line"); + select.append(" left join line." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as sr"); + Date backdatedTrxFrom = null; if (backdatedTransactionsFixed) { CostingRule costRule = CostingUtils.getCostDimensionRule(org, date); backdatedTrxFrom = CostingUtils.getCostingRuleFixBackdatedFrom(costRule); } - Object trxprocessDate = trxsubQry.uniqueResult(); if (trxprocessDate != null && (!backdatedTransactionsFixed || ((Date) trxprocessDate).before(backdatedTrxFrom))) { date = (Date) trxprocessDate; - select.append(" and trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE - + " < :date"); + select.append(" left join trx." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + + " as il"); + select.append(" left join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); + select.append(" where case when coalesce(i." + InventoryCount.PROPERTY_INVENTORYTYPE + + ", 'N') <> 'N' then trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " else trx." + + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " end < :date"); } else { - select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <= :date"); + select.append(" where trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <= :date"); } + + select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + " = :product"); + // Include only transactions that have its cost calculated + select.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = true"); + if (costDimensions.get(CostDimension.Warehouse) != null) { select.append(" and locator." + Locator.PROPERTY_WAREHOUSE + ".id = :warehouse"); } diff -r dcc7fba68b20 -r 54816dff1cfb src/org/openbravo/costing/CostingAlgorithmAdjustmentImp.java --- a/src/org/openbravo/costing/CostingAlgorithmAdjustmentImp.java Wed Nov 11 17:48:28 2015 +0100 +++ b/src/org/openbravo/costing/CostingAlgorithmAdjustmentImp.java Mon Nov 16 12:31:48 2015 +0100 @@ -139,8 +139,9 @@ costAdjLine = getCostAdjLine(); } - // Backdated transactions are inserted with a null adjustment amount. - if (costAdjLine.isBackdatedTrx()) { + // Backdated transactions are inserted with a null adjustment amount, in case we are not + // adjusting a Inventory Amount Update transaction. + if (costAdjLine.isBackdatedTrx() && costAdjLine.getAdjustmentAmount() == null) { calculateBackdatedTrxAdjustment(costAdjLine); } diff -r dcc7fba68b20 -r 54816dff1cfb src/org/openbravo/costing/CostingServer.java --- a/src/org/openbravo/costing/CostingServer.java Wed Nov 11 17:48:28 2015 +0100 +++ b/src/org/openbravo/costing/CostingServer.java Mon Nov 16 12:31:48 2015 +0100 @@ -231,9 +231,40 @@ } } - // With Standard Algorithm, no cost adjustment is needed + // With Standard Algorithm, only BDT are checked if (StringUtils.equals(transaction.getCostingAlgorithm().getJavaClassName(), "org.openbravo.costing.StandardAlgorithm")) { + + if (transaction.getMovementDate().after( + CostingUtils.getCostingRuleStartingDate(getCostingRule())) + && CostAdjustmentUtils.isNeededBackdatedCostAdjustment(transaction, getCostingRule() + .isWarehouseDimension(), CostingUtils.getCostingRuleStartingDate(getCostingRule()))) { + + // Case transaction backdated (modifying the stock in the past) + if (trxType != TrxType.InventoryClosing && trxType != TrxType.InventoryOpening + && getCostingRule().isBackdatedTransactionsFixed()) { + // BDT = Backdated transaction + createAdjustment("BDT", BigDecimal.ZERO); + } + + // Case Inventory Amount Update backdated (modifying the cost in the past) + if (trxType == TrxType.InventoryOpening) { + OBDal.getInstance().refresh(transaction.getPhysicalInventoryLine().getPhysInventory()); + if (transaction.getPhysicalInventoryLine().getPhysInventory() + .getInventoryAmountUpdateLineInventoriesInitInventoryList().size() > 0 + && CostingUtils.isLastOpeningTransaction(transaction, getCostingRule() + .isWarehouseDimension())) { ------------------------------------------------------------------------------ Presto, an open source distributed SQL query engine for big data, initially developed by Facebook, enables you to easily query your data on Hadoop in a more interactive manner. Teradata is also now providing full enterprise support for Presto. Download a free open source copy now. http://pubads.g.doubleclick.net/gampad/clk?id=250295911&iu=/4140 _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
