details: https://code.openbravo.com/erp/devel/pi/rev/8b4787d9d030 changeset: 27891:8b4787d9d030 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Wed Nov 11 11:30:33 2015 +0100 summary: Fixes issue 31080: Standard Algorithm is not working with Inventory Amount Update
Display "Backdated Transactions Fixed" flag and "Fix Backdated Transactions" button in Costing Rule window, also with "Standard Algorithm". With Standard cost, when creating a backdated transaction (which modifies the stock in the past) and in case we have "Backdated Transactions Fixed" flag activated, a BDT adjustment will be created related to any of the opening inventory transactions of each Inventory Amount Update created after backdated transaction. With Standard cost, when creating a backdated Inventory Amount Update (which modifies the cost in the past), a BDT adjustment will be created related to each transaction created after backdated Inventory Amount Update and before next Inventory Amount Update created, including closing inventory transactions related to next Inventory Amount Update created. details: https://code.openbravo.com/erp/devel/pi/rev/4e1551494c0e changeset: 27892:4e1551494c0e 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 8d73d0c271d5 -r 4e1551494c0e src-db/database/sourcedata/AD_FIELD.xml --- a/src-db/database/sourcedata/AD_FIELD.xml Mon Nov 16 13:45:51 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 8d73d0c271d5 -r 4e1551494c0e src/org/openbravo/costing/CostAdjustmentProcess.java --- a/src/org/openbravo/costing/CostAdjustmentProcess.java Mon Nov 16 13:45:51 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 8d73d0c271d5 -r 4e1551494c0e src/org/openbravo/costing/CostAdjustmentUtils.java --- a/src/org/openbravo/costing/CostAdjustmentUtils.java Mon Nov 16 13:45:51 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 8d73d0c271d5 -r 4e1551494c0e src/org/openbravo/costing/CostingAlgorithmAdjustmentImp.java --- a/src/org/openbravo/costing/CostingAlgorithmAdjustmentImp.java Mon Nov 16 13:45:51 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 8d73d0c271d5 -r 4e1551494c0e src/org/openbravo/costing/CostingServer.java --- a/src/org/openbravo/costing/CostingServer.java Mon Nov 16 13:45:51 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
