diff --git a/src-db/database/model/triggers/M_INVENTORYLINE_TRG.xml b/src-db/database/model/triggers/M_INVENTORYLINE_TRG.xml --- a/src-db/database/model/triggers/M_INVENTORYLINE_TRG.xml +++ b/src-db/database/model/triggers/M_INVENTORYLINE_TRG.xml @@ -10,6 +10,7 @@ v_RO NUMBER; V_STOCKED NUMBER; v_AttrSetValueType M_Product.AttrSetValueType%TYPE; + v_isRegularizationInventory NUMBER:= 0; /****************************************************************************** * The contents of this file are subject to the Compiere License Version 1.1 * ("License"); You may not use this file except in compliance with the License @@ -22,7 +23,7 @@ * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved. * Contributor(s): Openbravo SLU - * Contributions are Copyright (C) 2001-2012 Openbravo S.L.U. + * Contributions are Copyright (C) 2001-2016 Openbravo S.L.U. ******************************************************************************/ BEGIN @@ -30,13 +31,21 @@ IF AD_isTriggerEnabled()='N' THEN RETURN; END IF; - -- Get ID IF(UPDATING OR INSERTING) THEN v_ID:=:NEW.M_Inventory_ID; ELSE v_ID:=:OLD.M_Inventory_ID; END IF; + + IF (INSERTING) THEN + SELECT count(i.m_inventory_id) + INTO v_isRegularizationInventory + FROM M_Inventory i + WHERE i.m_inventory_id = v_ID + AND (i.inventory_type = 'O' OR i.inventory_type = 'C'); + END IF; + -- ReadOnly Check SELECT COUNT(*) INTO v_RO @@ -48,7 +57,22 @@ RAISE_APPLICATION_ERROR(-20000, '@20501@') ; END IF; -- Updating inventory - IF UPDATING OR DELETING THEN + IF (DELETING OR (UPDATING AND ( + (COALESCE(:OLD.M_INVENTORYLINE_ID, '0') <> COALESCE(:NEW.M_INVENTORYLINE_ID, '0')) OR + (COALESCE(:OLD.AD_CLIENT_ID, '0') <> COALESCE(:NEW.AD_CLIENT_ID, '0')) OR + (COALESCE(:OLD.AD_ORG_ID, '0') <> COALESCE(:NEW.AD_ORG_ID, '0')) OR + (COALESCE(:OLD.ISACTIVE, 'Y') <> COALESCE(:NEW.ISACTIVE, 'Y')) OR + (COALESCE(:OLD.M_INVENTORY_ID, '0') <> COALESCE(:NEW.M_INVENTORY_ID, '0')) OR + (COALESCE(:OLD.M_LOCATOR_ID, '0') <> COALESCE(:NEW.M_LOCATOR_ID, '0')) OR + (COALESCE(:OLD.M_PRODUCT_ID, '0') <> COALESCE(:NEW.M_PRODUCT_ID, '0')) OR + (COALESCE(:OLD.QTYBOOK, '0') <> COALESCE(:NEW.QTYBOOK, '0')) OR + (COALESCE(:OLD.QTYCOUNT, '0') <> COALESCE(:NEW.QTYCOUNT, '0')) OR + (COALESCE(:OLD.M_ATTRIBUTESETINSTANCE_ID, '0') <> COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0')) OR + (COALESCE(:OLD.M_PRODUCT_UOM_ID, '0') <> COALESCE(:NEW.M_PRODUCT_UOM_ID, '0')) OR + (COALESCE(:OLD.QUANTITYORDER, '0') <> COALESCE(:NEW.QUANTITYORDER, '0')) OR + (COALESCE(:OLD.QUANTITYORDERBOOK, '0') <> COALESCE(:NEW.QUANTITYORDERBOOK, '0')) OR + (COALESCE(:OLD.RELATEDINVENTORYLINEID, '0') <> COALESCE(:NEW.RELATEDINVENTORYLINEID, '0')) + ))) THEN SELECT COUNT(*) INTO V_STOCKED FROM M_PRODUCT @@ -59,7 +83,22 @@ M_UPDATE_INVENTORY(:OLD.AD_CLIENT_ID, :OLD.AD_ORG_ID, :OLD.UPDATEDBY, :OLD.M_PRODUCT_ID, :OLD.M_LOCATOR_ID, :OLD.M_ATTRIBUTESETINSTANCE_ID, :OLD.C_UOM_ID, :OLD.M_PRODUCT_UOM_ID, NULL, NULL, NULL, -(:OLD.QTYCOUNT-:OLD.QTYBOOK), :OLD.QUANTITYORDER) ; END IF; END IF; - IF INSERTING OR UPDATING THEN + IF ((INSERTING AND v_isRegularizationInventory = 0) OR (UPDATING AND ( + (COALESCE(:OLD.M_INVENTORYLINE_ID, '0') <> COALESCE(:NEW.M_INVENTORYLINE_ID, '0')) OR + (COALESCE(:OLD.AD_CLIENT_ID, '0') <> COALESCE(:NEW.AD_CLIENT_ID, '0')) OR + (COALESCE(:OLD.AD_ORG_ID, '0') <> COALESCE(:NEW.AD_ORG_ID, '0')) OR + (COALESCE(:OLD.ISACTIVE, 'Y') <> COALESCE(:NEW.ISACTIVE, 'Y')) OR + (COALESCE(:OLD.M_INVENTORY_ID, '0') <> COALESCE(:NEW.M_INVENTORY_ID, '0')) OR + (COALESCE(:OLD.M_LOCATOR_ID, '0') <> COALESCE(:NEW.M_LOCATOR_ID, '0')) OR + (COALESCE(:OLD.M_PRODUCT_ID, '0') <> COALESCE(:NEW.M_PRODUCT_ID, '0')) OR + (COALESCE(:OLD.QTYBOOK, '0') <> COALESCE(:NEW.QTYBOOK, '0')) OR + (COALESCE(:OLD.QTYCOUNT, '0') <> COALESCE(:NEW.QTYCOUNT, '0')) OR + (COALESCE(:OLD.M_ATTRIBUTESETINSTANCE_ID, '0') <> COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0')) OR + (COALESCE(:OLD.M_PRODUCT_UOM_ID, '0') <> COALESCE(:NEW.M_PRODUCT_UOM_ID, '0')) OR + (COALESCE(:OLD.QUANTITYORDER, '0') <> COALESCE(:NEW.QUANTITYORDER, '0')) OR + (COALESCE(:OLD.QUANTITYORDERBOOK, '0') <> COALESCE(:NEW.QUANTITYORDERBOOK, '0')) OR + (COALESCE(:OLD.RELATEDINVENTORYLINEID, '0') <> COALESCE(:NEW.RELATEDINVENTORYLINEID, '0')) + ))) THEN SELECT COUNT(*) INTO V_STOCKED FROM M_PRODUCT diff --git a/src-db/database/model/triggers/M_TRANSACTION_TRG.xml b/src-db/database/model/triggers/M_TRANSACTION_TRG.xml --- a/src-db/database/model/triggers/M_TRANSACTION_TRG.xml +++ b/src-db/database/model/triggers/M_TRANSACTION_TRG.xml @@ -38,12 +38,20 @@ v_doIncrease BOOLEAN:= FALSE; v_doDecrease BOOLEAN:= FALSE; v_stockdiff NUMBER:= 0; - v_checkuom NUMBER:= 0; + v_isRegularizationInventory NUMBER:= 0; BEGIN IF AD_isTriggerEnabled()='N' THEN RETURN; END IF; + IF (INSERTING AND :new.M_InventoryLine_ID IS NOT NULL) THEN + SELECT count(i.m_inventory_id) + INTO v_isRegularizationInventory + FROM M_Inventory i JOIN M_InventoryLine il + ON i.m_inventory_id = il.m_inventory_id + WHERE il.m_inventoryline_id = :new.M_InventoryLine_ID + AND (i.inventory_type = 'O' OR i.inventory_type = 'C'); + END IF; IF INSERTING OR UPDATING THEN SELECT P.NAME, P.C_UOM_ID, P.M_ATTRIBUTESET_ID, A.ISONEATTRSETVALREQUIRED, P.ATTRSETVALUETYPE @@ -51,53 +59,52 @@ FROM M_PRODUCT P LEFT JOIN M_ATTRIBUTESET A ON A.M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID WHERE P.M_PRODUCT_ID=:NEW.M_PRODUCT_ID; - - IF UPDATING THEN - IF (COALESCE(:old.m_transaction_id, '0') <> COALESCE(:new.m_transaction_id, '0')) - OR(COALESCE(:old.movementtype, '.') <> COALESCE(:new.movementtype, '.')) - OR(COALESCE(:old.m_locator_id, '0') <> COALESCE(:new.m_locator_id, '0')) - OR(COALESCE(:old.m_product_id, '0') <> COALESCE(:new.m_product_id, '0')) - OR(COALESCE(:old.movementdate, v_DateNull) <> COALESCE(:new.movementdate, v_DateNull)) - OR(COALESCE(:old.movementqty, '0') <> COALESCE(:new.movementqty, '0')) - OR(COALESCE(:old.m_inventoryline_id, '0') <> COALESCE(:new.m_inventoryline_id, '0')) - OR(COALESCE(:old.m_movementline_id, '0') <> COALESCE(:new.m_movementline_id, '0')) - OR(COALESCE(:old.m_inoutline_id, '0') <> COALESCE(:new.m_inoutline_id, '0')) - OR(COALESCE(:old.m_productionline_id, '0') <> COALESCE(:new.m_productionline_id, '0')) - OR(COALESCE(:old.c_projectissue_id, '0') <> COALESCE(:new.c_projectissue_id, '0')) - OR(COALESCE(:old.m_attributesetinstance_id, '0') <> COALESCE(:new.m_attributesetinstance_id, '0')) - OR(COALESCE(:old.m_product_uom_id, '0') <> COALESCE(:new.m_product_uom_id, '0')) - OR(COALESCE(:old.quantityorder, '0') <> COALESCE(:new.quantityorder, '0')) - OR(COALESCE(:old.c_uom_id, '0') <> COALESCE(:new.c_uom_id, '0')) - OR(COALESCE(:old.m_internal_consumptionline_id ,'0') <> COALESCE(:new.m_internal_consumptionline_id ,'0')) THEN + + IF UPDATING THEN + IF (COALESCE(:old.m_transaction_id, '0') <> COALESCE(:new.m_transaction_id, '0')) + OR(COALESCE(:old.movementtype, '.') <> COALESCE(:new.movementtype, '.')) + OR(COALESCE(:old.m_locator_id, '0') <> COALESCE(:new.m_locator_id, '0')) + OR(COALESCE(:old.m_product_id, '0') <> COALESCE(:new.m_product_id, '0')) + OR(COALESCE(:old.movementdate, v_DateNull) <> COALESCE(:new.movementdate, v_DateNull)) + OR(COALESCE(:old.movementqty, '0') <> COALESCE(:new.movementqty, '0')) + OR(COALESCE(:old.m_inventoryline_id, '0') <> COALESCE(:new.m_inventoryline_id, '0')) + OR(COALESCE(:old.m_movementline_id, '0') <> COALESCE(:new.m_movementline_id, '0')) + OR(COALESCE(:old.m_inoutline_id, '0') <> COALESCE(:new.m_inoutline_id, '0')) + OR(COALESCE(:old.m_productionline_id, '0') <> COALESCE(:new.m_productionline_id, '0')) + OR(COALESCE(:old.c_projectissue_id, '0') <> COALESCE(:new.c_projectissue_id, '0')) + OR(COALESCE(:old.m_attributesetinstance_id, '0') <> COALESCE(:new.m_attributesetinstance_id, '0')) + OR(COALESCE(:old.m_product_uom_id, '0') <> COALESCE(:new.m_product_uom_id, '0')) + OR(COALESCE(:old.quantityorder, '0') <> COALESCE(:new.quantityorder, '0')) + OR(COALESCE(:old.c_uom_id, '0') <> COALESCE(:new.c_uom_id, '0')) + OR(COALESCE(:old.m_internal_consumptionline_id ,'0') <> COALESCE(:new.m_internal_consumptionline_id ,'0')) THEN IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) THEN - RAISE_APPLICATION_ERROR(-20000, '@20111@') ; + RAISE_APPLICATION_ERROR(-20000, '@20111@') ; END IF; - IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0') THEN - RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ; + + IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0' AND v_isRegularizationInventory = 0) THEN + RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ; END IF; + --Does not allow to change the attribute set value -- for products which attribute set value type is Fixed IF (:NEW.M_PRODUCT_ID IS NOT NULL AND COALESCE(:new.M_AttributeSetInstance_ID,'0')!='0') THEN - SELECT ATTRSETVALUETYPE - INTO v_AttrSetValueType - FROM M_PRODUCT - WHERE M_PRODUCT_ID=:NEW.M_PRODUCT_ID; - IF (v_AttrSetValueType='F') THEN - RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@'); - END IF; + SELECT ATTRSETVALUETYPE + INTO v_AttrSetValueType + FROM M_PRODUCT + WHERE M_PRODUCT_ID=:NEW.M_PRODUCT_ID; + IF (v_AttrSetValueType='F') THEN + RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@'); + END IF; END IF; + END IF; END IF; - - IF INSERTING THEN - IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) THEN + + IF INSERTING THEN + IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0') AND v_isRegularizationInventory = 0) THEN IF (:new.M_InventoryLine_ID IS NOT NULL) THEN - SELECT il.line, CASE WHEN i.inventory_type = 'O' OR i.inventory_type = 'C' THEN 1 ELSE 0 END - INTO v_DocumentLineNo, v_checkuom - FROM M_Inventory i JOIN M_InventoryLine il - ON i.m_inventory_id = il.m_inventory_id - WHERE il.m_inventoryline_id = :new.M_InventoryLine_ID; + SELECT line INTO v_DocumentLineNo FROM M_InventoryLine WHERE M_InventoryLine_ID = :new.M_InventoryLine_ID; END IF; IF (:new.M_MovementLine_ID IS NOT NULL) THEN SELECT line INTO v_DocumentLineNo FROM M_MovementLine WHERE M_MovementLine_ID = :new.M_MovementLine_ID; @@ -111,28 +118,28 @@ IF (:new.M_Internal_ConsumptionLine_ID IS NOT NULL) THEN SELECT line INTO v_DocumentLineNo FROM M_Internal_ConsumptionLine WHERE M_Internal_ConsumptionLine_ID = :new.M_Internal_ConsumptionLine_ID; END IF; + RAISE_APPLICATION_ERROR(-20000, '@DocUOMMismatch@' || ' ' || COALESCE(v_DocumentLineNo, 0)); + END IF; - IF (v_checkuom = 0) THEN - RAISE_APPLICATION_ERROR(-20000, '@DocUOMMismatch@' || ' ' || COALESCE(v_DocumentLineNo, 0)); - END IF; - END IF; - IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0') THEN + IF(v_attributeset_id IS NOT NULL AND (v_AttrSetValueType IS NULL OR v_AttrSetValueType <> 'F') AND v_IsOneAtSetValReq = 'Y' AND COALESCE(:NEW.M_ATTRIBUTESETINSTANCE_ID, '0') = '0' AND v_isRegularizationInventory = 0) THEN RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ; - END IF; + END IF; + --Does not allow to change the attribute set value -- for products which attribute set value type is Fixed IF (:NEW.M_PRODUCT_ID IS NOT NULL AND COALESCE(:new.M_AttributeSetInstance_ID,'0')!='0') THEN - SELECT ATTRSETVALUETYPE - INTO v_AttrSetValueType + SELECT ATTRSETVALUETYPE + INTO v_AttrSetValueType FROM M_PRODUCT WHERE M_PRODUCT_ID=:NEW.M_PRODUCT_ID; IF (v_AttrSetValueType='F') THEN - RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@'); + RAISE_APPLICATION_ERROR(-20000, '@AttrSetValueFixed@'); END IF; - END IF; - END IF; + END IF; + + END IF; END IF; - + -- Updating inventory IF DELETING OR (UPDATING AND ( (COALESCE(:OLD.M_TRANSACTION_ID, '0') <> COALESCE(:NEW.M_TRANSACTION_ID, '0')) OR @@ -169,7 +176,7 @@ END IF; -- END FIXME END IF; - IF INSERTING OR (UPDATING AND ( + IF INSERTING AND v_isRegularizationInventory = 0 OR (UPDATING AND ( (COALESCE(:OLD.M_TRANSACTION_ID, '0') <> COALESCE(:NEW.M_TRANSACTION_ID, '0')) OR (COALESCE(:OLD.AD_CLIENT_ID, '0') <> COALESCE(:NEW.AD_CLIENT_ID, '0')) OR (COALESCE(:OLD.AD_ORG_ID, '0') <> COALESCE(:NEW.AD_ORG_ID, '0')) OR diff --git a/src/org/openbravo/costing/CostingRuleProcess.java b/src/org/openbravo/costing/CostingRuleProcess.java --- a/src/org/openbravo/costing/CostingRuleProcess.java +++ b/src/org/openbravo/costing/CostingRuleProcess.java @@ -37,6 +37,7 @@ import org.openbravo.base.provider.OBProvider; import org.openbravo.dal.core.DalUtil; import org.openbravo.dal.core.OBContext; +import org.openbravo.dal.core.TriggerHandler; import org.openbravo.dal.security.OrganizationStructureProvider; import org.openbravo.dal.service.OBDal; import org.openbravo.dal.service.OBQuery; @@ -49,7 +50,6 @@ import org.openbravo.model.common.enterprise.Locator; import org.openbravo.model.common.enterprise.Organization; import org.openbravo.model.common.enterprise.Warehouse; -import org.openbravo.model.common.plm.AttributeSetInstance; import org.openbravo.model.common.plm.Product; import org.openbravo.model.common.plm.ProductUOM; import org.openbravo.model.common.uom.UOM; @@ -78,6 +78,7 @@ OBError msg = new OBError(); msg.setType("Success"); msg.setTitle(OBMessageUtils.messageBD("Success")); + TriggerHandler.getInstance().disable(); try { OBContext.setAdminMode(false); final String ruleId = (String) bundle.getParams().get("M_Costing_Rule_ID"); @@ -102,12 +103,8 @@ // Product configured to have cost not calculated cannot have transactions with cost // calculated. checkNoTrxWithCostCalculated(naturalOrgs, childOrgs); - if (rule.getStartingDate() != null) { - // First rule of an instance that does not need migration. Old transactions costs are not - // calculated. They are initialized with ZERO cost. - initializeOldTrx(childOrgs, rule.getStartingDate()); - } } + // Inventories are only needed: // - if the costing rule is updating a previous rule // - or legacy cost was never used and the first validated rule has a starting date different @@ -127,10 +124,6 @@ if (rule.getFixbackdatedfrom() == null && rule.isBackdatedTransactionsFixed()) { rule.setFixbackdatedfrom(startingDate); } - createCostingRuleInits(ruleId, childOrgs, startingDate); - - // Update cost of inventories and process starting physical inventories. - updateInventoriesCostAndProcessInitInventories(ruleId, startingDate, existsPreviousRule); } if (rule.getStartingDate() != null && rule.getFixbackdatedfrom() != null @@ -139,6 +132,38 @@ throw new OBException("@FixBackdateFromBeforeStartingDate@"); } + if (existsPreviousRule || rule.getStartingDate() != null) { + + // Create opening/closing physical inventory lines + createInventoryLines(ruleId, childOrgs, rule.getStartingDate(), + getStockLines(childOrgs, rule.getStartingDate(), rule.isWarehouseDimension())); + + // Process closing physical inventories + processInventory(getPhysicalInventories(ruleId, true)); + + if (existsPreviousRule) { + // Update cost of closing physical inventory transactions + updateInventoriesCost(ruleId, rule.getStartingDate()); + } + + else { + // Update closing physical inventory transactions date + updateInventoriesTrxDate(ruleId, DateUtils.addSeconds(rule.getStartingDate(), -1), true); + + // Initialize old transactions with zero cost, if previous rule does not exist + initializeOldTrx(childOrgs, rule.getStartingDate()); + } + + // Process opening physical inventories + processInventory(getPhysicalInventories(ruleId, false)); + + if (!existsPreviousRule) { + // Update opening physical inventory transactions date + updateInventoriesTrxDate(ruleId, rule.getStartingDate(), false); + } + + } + // Reload rule after possible session clear. rule = OBDal.getInstance().get(CostingRule.class, ruleId); rule.setValidated(true); @@ -165,6 +190,7 @@ bundle.setResult(msg); } finally { OBContext.restorePreviousMode(); + TriggerHandler.getInstance().enable(); } bundle.setResult(msg); long end = System.currentTimeMillis(); @@ -295,7 +321,7 @@ insert.append(" left join t." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as iol"); insert.append(" left join iol." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as io"); insert.append(" where t." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); - insert.append(" and t." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " < :date"); + insert.append(" and t." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " < :date"); insert.append(" and t." + MaterialTransaction.PROPERTY_ISPROCESSED + " = false"); insert.append(" and t." + MaterialTransaction.PROPERTY_ACTIVE + " = true"); insert.append(" and t." + MaterialTransaction.PROPERTY_CLIENT + ".id = :client"); @@ -317,7 +343,7 @@ update.append(", " + MaterialTransaction.PROPERTY_CURRENCY + " = :currency"); update.append(", " + MaterialTransaction.PROPERTY_ISPROCESSED + " = true"); update.append(" where " + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); - update.append(" and " + MaterialTransaction.PROPERTY_MOVEMENTDATE + " < :date"); + update.append(" and " + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " < :date"); update.append(" and " + MaterialTransaction.PROPERTY_ISPROCESSED + " = false"); update.append(" and " + MaterialTransaction.PROPERTY_ACTIVE + " = true"); update.append(" and " + MaterialTransaction.PROPERTY_CLIENT + ".id = :client"); @@ -331,7 +357,7 @@ log4j.debug("InitializeOldTrx updated " + n2 + " records. Took: " + (System.currentTimeMillis() - t2) + " ms."); - OBDal.getInstance().flush(); + OBDal.getInstance().getSession().flush(); OBDal.getInstance().getSession().clear(); } @@ -341,36 +367,96 @@ } protected void createCostingRuleInits(String ruleId, Set childOrgs, Date date) { + // Create opening/closing physical inventory lines + CostingRule rule = OBDal.getInstance().get(CostingRule.class, ruleId); + createInventoryLines(ruleId, childOrgs, date, + getStockLines(childOrgs, date, rule.isWarehouseDimension())); + + // Process closing physical inventories + processInventory(getPhysicalInventories(ruleId, true)); + } + + private ScrollableResults getStockLines(Set childOrgs, Date date, + boolean warehouseDimension) { long t1 = System.currentTimeMillis(); + + StringBuffer select = new StringBuffer(); + select.append("select trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); + select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); + select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); + select.append(", sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ")"); + select.append(", sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ")"); + if (warehouseDimension) { + select.append(", locator." + Locator.PROPERTY_WAREHOUSE + ".id"); + } + select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); + if (warehouseDimension) { + select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); + } + select.append(" where trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); + if (date != null) { + select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " < :date"); + } + select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".productType = 'I'"); + select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".stocked = true"); + select.append(" group by trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); + select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); + select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); + if (warehouseDimension) { + select.append(", locator." + Locator.PROPERTY_WAREHOUSE + ".id"); + } + select.append(" having "); + select.append(" sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") <> 0"); + select.append(" or sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ") <> 0"); + if (warehouseDimension) { + select.append(" order by locator." + Locator.PROPERTY_WAREHOUSE + ".id"); + select.append(", trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); + } else { + select.append(" order by trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); + } + select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); + select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); + + Query stockLinesQry = OBDal.getInstance().getSession().createQuery(select.toString()); + stockLinesQry.setParameterList("orgs", childOrgs); + if (date != null) { + stockLinesQry.setTimestamp("date", date); + } + stockLinesQry.setFetchSize(1000); + ScrollableResults stockLines = stockLinesQry.scroll(ScrollMode.FORWARD_ONLY); + + log4j.debug("GetStockLines took: " + (System.currentTimeMillis() - t1) + " ms."); + return stockLines; + } + + private void createInventoryLines(String ruleId, Set childOrgs, Date date, + ScrollableResults stockLines) { + long t = System.currentTimeMillis(); CostingRule rule = OBDal.getInstance().get(CostingRule.class, ruleId); - ScrollableResults stockLines = getStockLines(childOrgs, date); - log4j.debug("GetStockLines took: " + (System.currentTimeMillis() - t1) + " ms."); // The key of the Map is the concatenation of orgId and warehouseId - long t2 = System.currentTimeMillis(); Map initLines = new HashMap(); Map maxLineNumbers = new HashMap(); InventoryCountLine closingInventoryLine = null; InventoryCountLine openInventoryLine = null; + String warehouseId = rule.isWarehouseDimension() ? null : getWarehouseWithLocator(childOrgs); int i = 0; try { while (stockLines.next()) { - long t3 = System.currentTimeMillis(); Object[] stockLine = stockLines.get(); String productId = (String) stockLine[0]; - String attrSetInsId = (String) stockLine[1]; - String uomId = (String) stockLine[2]; - String orderUOMId = (String) stockLine[3]; - String locatorId = (String) stockLine[4]; - String warehouseId = (String) stockLine[5]; - BigDecimal qty = (BigDecimal) stockLine[6]; - BigDecimal orderQty = (BigDecimal) stockLine[7]; + String uomId = (String) stockLine[1]; + String orderUOMId = (String) stockLine[2]; + BigDecimal qty = (BigDecimal) stockLine[3]; + BigDecimal orderQty = (BigDecimal) stockLine[4]; + if (rule.isWarehouseDimension()) { + warehouseId = (String) stockLine[5]; + } String criId = initLines.get(warehouseId); CostingRuleInit cri = null; if (criId == null) { cri = createCostingRuleInitLine(rule, warehouseId, date); - initLines.put(warehouseId, cri.getId()); } else { cri = OBDal.getInstance().get(CostingRuleInit.class, criId); @@ -385,19 +471,18 @@ // By doing so the difference between both quantities remains the same and no negative // values have been inserted. - openInventoryLine = insertInventoryLine(cri.getInitInventory(), productId, attrSetInsId, - uomId, orderUOMId, locatorId, qty, BigDecimal.ZERO, orderQty, BigDecimal.ZERO, - lineNo, null); - insertInventoryLine(cri.getCloseInventory(), productId, attrSetInsId, uomId, orderUOMId, - locatorId, BigDecimal.ZERO, qty, BigDecimal.ZERO, orderQty, lineNo, openInventoryLine); + openInventoryLine = insertInventoryLine(cri.getInitInventory(), productId, uomId, + orderUOMId, qty, BigDecimal.ZERO, orderQty, BigDecimal.ZERO, lineNo, null); + insertInventoryLine(cri.getCloseInventory(), productId, uomId, orderUOMId, + BigDecimal.ZERO, qty, BigDecimal.ZERO, orderQty, lineNo, openInventoryLine); } else { - openInventoryLine = insertInventoryLine(cri.getInitInventory(), productId, attrSetInsId, - uomId, orderUOMId, locatorId, BigDecimal.ZERO, qty.abs(), BigDecimal.ZERO, - orderQty == null ? null : orderQty.abs(), lineNo, closingInventoryLine); - insertInventoryLine(cri.getCloseInventory(), productId, attrSetInsId, uomId, orderUOMId, - locatorId, qty == null ? null : qty.abs(), BigDecimal.ZERO, orderQty == null ? null - : orderQty.abs(), BigDecimal.ZERO, lineNo, openInventoryLine); + openInventoryLine = insertInventoryLine(cri.getInitInventory(), productId, uomId, + orderUOMId, BigDecimal.ZERO, qty.abs(), BigDecimal.ZERO, orderQty == null ? null + : orderQty.abs(), lineNo, closingInventoryLine); + insertInventoryLine(cri.getCloseInventory(), productId, uomId, orderUOMId, + qty == null ? null : qty.abs(), BigDecimal.ZERO, + orderQty == null ? null : orderQty.abs(), BigDecimal.ZERO, lineNo, openInventoryLine); } i++; @@ -407,76 +492,23 @@ // Reload rule after clear session. rule = OBDal.getInstance().get(CostingRule.class, ruleId); } - - log4j.debug("Create closing/opening inventory line took: " - + (System.currentTimeMillis() - t3) + " ms."); } } finally { stockLines.close(); } - log4j.debug("Create " + i + " closing/opening inventory lines took: " - + (System.currentTimeMillis() - t2) + " ms."); + log4j.debug("CreateInventoryLines created " + i + " closing/opening inventory lines and took: " + + (System.currentTimeMillis() - t) + " ms."); + } - // Process closing physical inventories. - long t4 = System.currentTimeMillis(); - rule = OBDal.getInstance().get(CostingRule.class, ruleId); - i = 0; - for (CostingRuleInit cri : rule.getCostingRuleInitList()) { - long t5 = System.currentTimeMillis(); - new InventoryCountProcess().processInventory(cri.getCloseInventory(), false); - log4j.debug("Processing closing inventory took: " + (System.currentTimeMillis() - t5) - + " ms."); + private void processInventory(List inventories) { + long t = System.currentTimeMillis(); + int i = 0; + for (InventoryCount inventory : inventories) { + new InventoryCountProcess().processInventory(inventory, false); i++; } - log4j.debug("Processing " + i + " closing inventories took: " - + (System.currentTimeMillis() - t4) + " ms."); - - log4j - .debug("CreateCostingRuleInits method took: " + (System.currentTimeMillis() - t1) + " ms."); - } - - private ScrollableResults getStockLines(Set childOrgs, Date date) { - StringBuffer select = new StringBuffer(); - select.append("select trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); - select.append(", loc." + Locator.PROPERTY_WAREHOUSE + ".id"); - select.append(", sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ")"); - select.append(", sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ")"); - select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); - select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as loc"); - select.append(" where trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); - if (date != null) { - select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " < :date"); - } - select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".productType = 'I'"); - select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".stocked = true"); - select.append(" group by trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); - select.append(", loc." + Locator.PROPERTY_WAREHOUSE + ".id"); - select.append(" having "); - select.append(" sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") <> 0"); - select.append(" or sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ") <> 0"); - select.append(" order by loc." + Locator.PROPERTY_WAREHOUSE + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); - select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); - - Query stockLinesQry = OBDal.getInstance().getSession().createQuery(select.toString()); - stockLinesQry.setParameterList("orgs", childOrgs); - if (date != null) { - stockLinesQry.setTimestamp("date", date); - } - stockLinesQry.setFetchSize(1000); - ScrollableResults stockLines = stockLinesQry.scroll(ScrollMode.FORWARD_ONLY); - return stockLines; + log4j.debug("ProcessInventory processed " + i + " inventories and took: " + + (System.currentTimeMillis() - t) + " ms."); } private CostingRuleInit createCostingRuleInitLine(CostingRule rule, String warehouseId, Date date) { @@ -527,18 +559,16 @@ } private InventoryCountLine insertInventoryLine(InventoryCount inventory, String productId, - String attrSetInsId, String uomId, String orderUOMId, String locatorId, BigDecimal qtyCount, - BigDecimal qtyBook, BigDecimal orderQtyCount, BigDecimal orderQtyBook, Long lineNo, + String uomId, String orderUOMId, BigDecimal qtyCount, BigDecimal qtyBook, + BigDecimal orderQtyCount, BigDecimal orderQtyBook, Long lineNo, InventoryCountLine relatedInventoryLine) { InventoryCountLine icl = OBProvider.getInstance().get(InventoryCountLine.class); icl.setClient(inventory.getClient()); icl.setOrganization(inventory.getOrganization()); icl.setPhysInventory(inventory); icl.setLineNo(lineNo); - icl.setStorageBin((Locator) OBDal.getInstance().getProxy(Locator.ENTITY_NAME, locatorId)); + icl.setStorageBin(inventory.getWarehouse().getLocatorList().get(0)); icl.setProduct((Product) OBDal.getInstance().getProxy(Product.ENTITY_NAME, productId)); - icl.setAttributeSetValue((AttributeSetInstance) OBDal.getInstance().getProxy( - AttributeSetInstance.ENTITY_NAME, attrSetInsId)); icl.setQuantityCount(qtyCount); icl.setBookQuantity(qtyBook); icl.setUOM((UOM) OBDal.getInstance().getProxy(UOM.ENTITY_NAME, uomId)); @@ -556,100 +586,51 @@ return icl; } - private void updateInventoriesCostAndProcessInitInventories(String ruleId, Date startingDate, - boolean existsPreviousRule) { - long t1 = System.currentTimeMillis(); - CostingRule rule = OBDal.getInstance().get(CostingRule.class, ruleId); + private void updateInventoriesCost(String ruleId, Date startingDate) { + long t = System.currentTimeMillis(); + ScrollableResults trxs = getInventoryLineTransactions(ruleId, true); int i = 0; - for (CostingRuleInit cri : rule.getCostingRuleInitList()) { - long t2 = System.currentTimeMillis(); - ScrollableResults trxs = getInventoryLineTransactions(cri.getCloseInventory()); - log4j.debug("GetInventoryLineTransactions took: " + (System.currentTimeMillis() - t2) - + " ms."); - long t3 = System.currentTimeMillis(); - int j = 0; - try { - while (trxs.next()) { - long t4 = System.currentTimeMillis(); - MaterialTransaction trx = (MaterialTransaction) trxs.get(0); - // Remove 1 second from transaction date to ensure that cost is calculated with previous - // costing rule. - trx.setTransactionProcessDate(DateUtils.addSeconds(startingDate, -1)); - BigDecimal trxCost = BigDecimal.ZERO; - BigDecimal cost = null; - Currency cur = FinancialUtils.getLegalEntityCurrency(trx.getOrganization()); - if (existsPreviousRule) { - trxCost = CostingUtils.getTransactionCost(trx, startingDate, true, cur); - if (trx.getMovementQuantity().compareTo(BigDecimal.ZERO) != 0) { - cost = trxCost.divide(trx.getMovementQuantity().abs(), cur.getCostingPrecision() - .intValue(), RoundingMode.HALF_UP); - trx = OBDal.getInstance().get(MaterialTransaction.class, trx.getId()); - } - } else { - // Insert transaction cost record big ZERO cost. - cur = trx.getClient().getCurrency(); - TransactionCost transactionCost = OBProvider.getInstance().get(TransactionCost.class); - transactionCost.setInventoryTransaction(trx); - transactionCost.setCostDate(trx.getTransactionProcessDate()); - transactionCost.setClient(trx.getClient()); - transactionCost.setOrganization(trx.getOrganization()); - transactionCost.setCost(BigDecimal.ZERO); - transactionCost.setCurrency(trx.getClient().getCurrency()); - transactionCost.setAccountingDate(trx.getGoodsShipmentLine() != null ? trx - .getGoodsShipmentLine().getShipmentReceipt().getAccountingDate() : trx - .getMovementDate()); - List trxCosts = trx.getTransactionCostList(); - trxCosts.add(transactionCost); - trx.setTransactionCostList(trxCosts); - OBDal.getInstance().save(trx); - } + try { + while (trxs.next()) { - trx.setCostCalculated(true); - trx.setCostingStatus("CC"); - trx.setCurrency(cur); - trx.setTransactionCost(trxCost); - OBDal.getInstance().save(trx); + // Remove 1 second from transaction date to ensure that cost is calculated with previous + // costing rule. + MaterialTransaction trx = (MaterialTransaction) trxs.get(0); + trx.setTransactionProcessDate(DateUtils.addSeconds(startingDate, -1)); - InventoryCountLine initICL = trx.getPhysicalInventoryLine().getRelatedInventory(); - initICL.setCost(cost); - OBDal.getInstance().save(initICL); + BigDecimal trxCost = BigDecimal.ZERO; + BigDecimal cost = null; + Currency cur = FinancialUtils.getLegalEntityCurrency(trx.getOrganization()); + trxCost = CostingUtils.getTransactionCost(trx, startingDate, true, cur); + if (trx.getMovementQuantity().compareTo(BigDecimal.ZERO) != 0) { + cost = trxCost.divide(trx.getMovementQuantity().abs(), cur.getCostingPrecision() + .intValue(), RoundingMode.HALF_UP); + trx = OBDal.getInstance().get(MaterialTransaction.class, trx.getId()); + } - j++; - if ((j % 100) == 0) { - OBDal.getInstance().flush(); - OBDal.getInstance().getSession().clear(); - cri = OBDal.getInstance().get(CostingRuleInit.class, cri.getId()); - } + trx.setCostCalculated(true); + trx.setCostingStatus("CC"); + trx.setCurrency(cur); + trx.setTransactionCost(trxCost); + trx.setProcessed(true); + OBDal.getInstance().save(trx); - log4j.debug("Update inventory line cost took: " + (System.currentTimeMillis() - t4) - + " ms."); + InventoryCountLine initICL = trx.getPhysicalInventoryLine().getRelatedInventory(); + initICL.setCost(cost); + OBDal.getInstance().save(initICL); + + i++; + if ((i % 100) == 0) { + OBDal.getInstance().flush(); + OBDal.getInstance().getSession().clear(); } - } finally { - trxs.close(); } - OBDal.getInstance().flush(); - log4j.debug("Update " + j + "inventory line costs took: " + (System.currentTimeMillis() - t3) - + " ms."); - - long t5 = System.currentTimeMillis(); - cri = OBDal.getInstance().get(CostingRuleInit.class, cri.getId()); - new InventoryCountProcess().processInventory(cri.getInitInventory(), false); - log4j.debug("Processing opening inventory took: " + (System.currentTimeMillis() - t5) - + " ms."); - i++; + } finally { + trxs.close(); } - log4j.debug("Processing " + i + " opening inventories took: " - + (System.currentTimeMillis() - t1) + " ms."); - - if (!existsPreviousRule) { - long t6 = System.currentTimeMillis(); - updateInitInventoriesTrxDate(startingDate, ruleId); - log4j.debug("UpdateInitInventoriesTrxDate took: " + (System.currentTimeMillis() - t6) - + " ms."); - } - - log4j.debug("UpdateInventoriesCostAndProcessInitInventories method took: " - + (System.currentTimeMillis() - t1) + " ms."); + OBDal.getInstance().flush(); + log4j.debug("UpdateInventoriesCost updated " + i + "inventory line cost and took: " + + (System.currentTimeMillis() - t) + " ms."); } protected MaterialTransaction getInventoryLineTransaction(InventoryCountLine icl) { @@ -688,38 +669,87 @@ return iclQry.uniqueResult(); } - private ScrollableResults getInventoryLineTransactions(InventoryCount inventory) { - StringBuffer where = new StringBuffer(); - where.append(MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + "." - + InventoryCountLine.PROPERTY_PHYSINVENTORY + "." + InventoryCount.PROPERTY_ID - + " = :inventory"); - OBQuery trxQry = OBDal.getInstance().createQuery( - MaterialTransaction.class, where.toString()); - trxQry.setNamedParameter("inventory", inventory.getId()); - return trxQry.scroll(ScrollMode.FORWARD_ONLY); - } - - private void updateInitInventoriesTrxDate(Date startingDate, String ruleId) { + private void updateInventoriesTrxDate(String ruleId, Date date, boolean closing) { + long t = System.currentTimeMillis(); StringBuffer update = new StringBuffer(); update.append(" update " + MaterialTransaction.ENTITY_NAME + " as trx"); update.append(" set trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " = :date"); update.append(" where exists ("); - update.append(" select 1"); - update.append(" from " + InventoryCountLine.ENTITY_NAME + " as il"); - update.append(" join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); - update.append(" join i." + InventoryCount.PROPERTY_COSTINGRULEINITINITINVENTORYLIST - + " as cri"); - update.append(" where cri." + CostingRuleInit.PROPERTY_COSTINGRULE + ".id = :cr"); - update.append(" and il." + InventoryCountLine.PROPERTY_ID + " = trx." + update.append(" select 1"); + update.append(" from " + InventoryCountLine.ENTITY_NAME + " as il"); + update.append(" join il." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as i"); + if (closing) { + update.append(" join i." + InventoryCount.PROPERTY_COSTINGRULEINITCLOSEINVENTORYLIST + + " as cri"); + } else { + update.append(" join i." + InventoryCount.PROPERTY_COSTINGRULEINITINITINVENTORYLIST + + " as cri"); + } + update.append(" where cri." + CostingRuleInit.PROPERTY_COSTINGRULE + ".id = :cr"); + update.append(" and il." + InventoryCountLine.PROPERTY_ID + " = trx." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + ".id"); update.append(" )"); Query queryUpdate = OBDal.getInstance().getSession().createQuery(update.toString()); - queryUpdate.setDate("date", startingDate); + queryUpdate.setDate("date", date); queryUpdate.setString("cr", ruleId); - queryUpdate.executeUpdate(); + int n = queryUpdate.executeUpdate(); + log4j.debug("UpdateInventoriesTrxDate updated " + n + " records. Took: " + + (System.currentTimeMillis() - t) + " ms."); OBDal.getInstance().flush(); OBDal.getInstance().getSession().clear(); } + + private List getPhysicalInventories(String ruleId, boolean closing) { + StringBuffer where = new StringBuffer(); + where.append(" as ic"); + if (closing) { + where.append(" join ic." + InventoryCount.PROPERTY_COSTINGRULEINITCLOSEINVENTORYLIST + + " as cri"); + } else { + where.append(" join ic." + InventoryCount.PROPERTY_COSTINGRULEINITINITINVENTORYLIST + + " as cri"); + } + where.append(" join cri." + CostingRuleInit.PROPERTY_COSTINGRULE + " as cr"); + where.append(" where cr." + CostingRule.PROPERTY_ID + " = :ruleId"); + OBQuery trxQry = OBDal.getInstance().createQuery(InventoryCount.class, + where.toString()); + trxQry.setNamedParameter("ruleId", ruleId); + return trxQry.list(); + } + + private ScrollableResults getInventoryLineTransactions(String ruleId, boolean closing) { + StringBuffer where = new StringBuffer(); + where.append(" as t"); + where.append(" join t." + MaterialTransaction.PROPERTY_PHYSICALINVENTORYLINE + " as icl"); + where.append(" join icl." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " as ic"); + if (closing) { + where.append(" join ic." + InventoryCount.PROPERTY_COSTINGRULEINITCLOSEINVENTORYLIST + + " as cri"); + } else { + where.append(" join ic." + InventoryCount.PROPERTY_COSTINGRULEINITINITINVENTORYLIST + + " as cri"); + } + where.append(" join cri." + CostingRuleInit.PROPERTY_COSTINGRULE + " as cr"); + where.append(" where cr." + CostingRule.PROPERTY_ID + " = :ruleId"); + OBQuery trxQry = OBDal.getInstance().createQuery( + MaterialTransaction.class, where.toString()); + trxQry.setNamedParameter("ruleId", ruleId); + return trxQry.scroll(ScrollMode.FORWARD_ONLY); + } + + private String getWarehouseWithLocator(Set childOrgs) { + StringBuffer where = new StringBuffer(); + where.append(" select w." + Warehouse.PROPERTY_ID); + where.append(" from " + Warehouse.ENTITY_NAME + " as w"); + where.append(" where w." + Warehouse.PROPERTY_ORGANIZATION + ".id in :orgs"); + where.append(" and exists (select 1 from " + Locator.ENTITY_NAME + " where " + + Locator.PROPERTY_ORGANIZATION + ".id = w." + Warehouse.PROPERTY_ORGANIZATION + ".id)"); + where.append(" order by w." + Warehouse.PROPERTY_NAME); + Query qry = OBDal.getInstance().getSession().createQuery(where.toString()); + qry.setParameterList("orgs", childOrgs); + qry.setMaxResults(1); + return (String) qry.uniqueResult(); + } } diff --git a/src/org/openbravo/materialmgmt/InventoryCountProcess.java b/src/org/openbravo/materialmgmt/InventoryCountProcess.java --- a/src/org/openbravo/materialmgmt/InventoryCountProcess.java +++ b/src/org/openbravo/materialmgmt/InventoryCountProcess.java @@ -11,6 +11,7 @@ import javax.enterprise.inject.Instance; import javax.inject.Inject; +import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.time.DateUtils; import org.apache.log4j.Logger; import org.hibernate.Query; @@ -289,6 +290,7 @@ if (inventory.isProcessed()) { throw new OBException(OBMessageUtils.parseTranslation("@AlreadyPosted@")); } + // Products without attribute set. StringBuffer where = new StringBuffer(); where.append(" as icl"); @@ -300,14 +302,18 @@ where.append(" and coalesce(icl." + InventoryCountLine.PROPERTY_ATTRIBUTESETVALUE + ", '0') = '0'"); where.append(" order by icl." + InventoryCountLine.PROPERTY_LINENO); - OBQuery iclQry = OBDal.getInstance().createQuery(InventoryCountLine.class, - where.toString()); - iclQry.setNamedParameter("inventory", inventory.getId()); - iclQry.setMaxResult(1); - Object icl = iclQry.uniqueResult(); - if (icl != null) { - throw new OBException(OBMessageUtils.parseTranslation("@Inline@ " - + ((InventoryCountLine) icl).getLineNo() + " @productWithoutAttributeSet@")); + + if (!StringUtils.equals(inventory.getInventoryType(), "O") + && !StringUtils.equals(inventory.getInventoryType(), "C")) { + OBQuery iclQry = OBDal.getInstance().createQuery( + InventoryCountLine.class, where.toString()); + iclQry.setNamedParameter("inventory", inventory.getId()); + iclQry.setMaxResult(1); + Object icl = iclQry.uniqueResult(); + if (icl != null) { + throw new OBException(OBMessageUtils.parseTranslation("@Inline@ " + + ((InventoryCountLine) icl).getLineNo() + " @productWithoutAttributeSet@")); + } } // duplicated product @@ -334,7 +340,8 @@ where.append(", icl." + InventoryCountLine.PROPERTY_STORAGEBIN); where.append(", icl." + InventoryCountLine.PROPERTY_ORDERUOM); where.append(", icl." + InventoryCountLine.PROPERTY_LINENO); - iclQry = OBDal.getInstance().createQuery(InventoryCountLine.class, where.toString()); + OBQuery iclQry = OBDal.getInstance().createQuery(InventoryCountLine.class, + where.toString()); iclQry.setNamedParameter("inventory", inventory.getId()); List iclList = iclQry.list(); if (!iclList.isEmpty()) {