details: https://code.openbravo.com/erp/devel/pi/rev/20cd44382a65 changeset: 34236:20cd44382a65 user: Armaignac <collazoandy4 <at> gmail.com> date: Wed Jun 20 18:17:10 2018 -0400 summary: Fixes issue 38685:It is not possible to process a Physical Inventory for a product with attributes that has negative stock without attributes
When a new physical inventory was process with a product without an attribute set defined an exception was throw aborting the process. Now a physical inventory can be process if the product have not attribute set defined and no available stock in the bin. details: https://code.openbravo.com/erp/devel/pi/rev/8da978492eeb changeset: 34237:8da978492eeb user: David Miguelez <david.miguelez <at> openbravo.com> date: Mon Jun 25 16:11:54 2018 +0200 summary: Related to Issue 38685. Code Review changes details: https://code.openbravo.com/erp/devel/pi/rev/fcd2b0c47081 changeset: 34238:fcd2b0c47081 user: David Miguelez <david.miguelez <at> openbravo.com> date: Mon Jun 25 17:15:16 2018 +0200 summary: Related to Issue 38685. Code Review changes: * When querying for exising stock, take into account that the uom is the same one as the uom of the inventory line * When querying for existing stock, check that there is no stock assign to draft transactions * When checking for existing stock, do it only when the quantitycount is 0 * Changed the query to be more readable * Added explanatory commentary details: https://code.openbravo.com/erp/devel/pi/rev/91b8d9df5a3b changeset: 34239:91b8d9df5a3b user: David Miguelez <david.miguelez <at> openbravo.com> date: Mon Jun 25 17:57:48 2018 +0200 summary: Related to Issue 38685. Refactor of runChecks method * Split method into smaller ones * Renamed variables with obtuse names * Delete unnecessary exceptions thrown * Changed queries to make them more readable details: https://code.openbravo.com/erp/devel/pi/rev/79f006ced359 changeset: 34240:79f006ced359 user: David Miguelez <david.miguelez <at> openbravo.com> date: Tue Jun 26 11:42:34 2018 +0200 summary: Related to Issue 38685. Code Review changes: * Formatted code to make it more readable * Added check of quantity in draft transactions to query that retrieves stock * Changed logic to check if the attribute can be null * I the QuantityCount of the line is <> 0, attribute is mandatory * If there is no stock with emtpy attribute, attribute is mandatory * If there is previous stock with empty attribute and the quantity count is 0 then the attribute can be null diffstat: src-db/database/model/triggers/M_TRANSACTION_TRG.xml | 128 +++++-- src/org/openbravo/materialmgmt/InventoryCountProcess.java | 217 ++++++++----- 2 files changed, 217 insertions(+), 128 deletions(-) diffs (truncated from 432 to 300 lines): diff -r 062df9369cb9 -r 79f006ced359 src-db/database/model/triggers/M_TRANSACTION_TRG.xml --- a/src-db/database/model/triggers/M_TRANSACTION_TRG.xml Tue Jun 26 11:46:32 2018 +0000 +++ b/src-db/database/model/triggers/M_TRANSACTION_TRG.xml Tue Jun 26 11:42:34 2018 +0200 @@ -18,13 +18,14 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU -* All portions are Copyright (C) 2001-2016 Openbravo SLU +* All portions are Copyright (C) 2001-2018 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************/ v_DATEINVENTORY DATE; v_UOM_ID VARCHAR2(32) ; v_ATTRIBUTESET_ID VARCHAR2(32); + v_M_LOCATOR_ID VARCHAR2(32); v_AttrSetValueType M_Product.AttrSetValueType%TYPE; v_Name M_Product.Name%TYPE; v_IsOneAtSetValReq M_Attributeset.Isoneattrsetvalrequired%TYPE; @@ -38,8 +39,9 @@ v_doDecrease BOOLEAN:= FALSE; v_stockdiff NUMBER:= 0; v_checkuom NUMBER:= 0; + v_stockCount NUMBER; + v_inventoryLineQtyCount M_INVENTORYLINE.QTYCOUNT%TYPE; BEGIN - IF AD_isTriggerEnabled()='N' THEN RETURN; END IF; @@ -69,55 +71,97 @@ 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@') ; - 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) ; - 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; - END IF; + IF(COALESCE(v_UOM_ID, '0') <> COALESCE(:NEW.C_UOM_ID, '0')) THEN + 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 + IF(:NEW.M_INVENTORYLINE_ID IS NOT NULL) THEN + SELECT IL.QTYCOUNT INTO v_inventoryLineQtyCount + FROM M_INVENTORYLINE IL + WHERE IL.M_INVENTORYLINE_ID = :NEW.M_INVENTORYLINE_ID; + + SELECT COUNT(1) INTO v_stockCount + FROM DUAL + WHERE EXISTS (SELECT 1 + FROM M_STORAGE_DETAIL SD + WHERE SD.M_PRODUCT_ID = :NEW.M_PRODUCT_ID + AND SD.M_LOCATOR_ID = :NEW.M_LOCATOR_ID + AND SD.M_ATTRIBUTESETINSTANCE_ID = '0' + AND SD.C_UOM_ID = :NEW.C_UOM_ID + AND SD.QTYONHAND <> 0 + AND SD.PREQTYONHAND <> 0); + + IF(v_inventoryLineQtyCount <> 0 OR (v_inventoryLineQtyCount = 0 AND v_stockCount = 0)) THEN + RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ; + END IF; + ELSE + 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 + 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 (: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; - 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; - END IF; - IF (:new.M_InOutLine_ID IS NOT NULL) THEN - SELECT line INTO v_DocumentLineNo FROM M_InOutLine WHERE M_InOutLine_ID = :new.M_InOutLine_ID; - END IF; - IF (:new.M_ProductionLine_ID IS NOT NULL) THEN - SELECT line INTO v_DocumentLineNo FROM M_ProductionLine WHERE M_ProductionLine_ID = :new.M_ProductionLine_ID; - END IF; - 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; + 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; + 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; + END IF; + IF (:new.M_InOutLine_ID IS NOT NULL) THEN + SELECT line INTO v_DocumentLineNo FROM M_InOutLine WHERE M_InOutLine_ID = :new.M_InOutLine_ID; + END IF; + IF (:new.M_ProductionLine_ID IS NOT NULL) THEN + SELECT line INTO v_DocumentLineNo FROM M_ProductionLine WHERE M_ProductionLine_ID = :new.M_ProductionLine_ID; + END IF; + 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; - IF (v_checkuom = 0) THEN - 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 - RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ; - END IF; + IF(:NEW.M_INVENTORYLINE_ID IS NOT NULL) THEN + SELECT IL.QTYCOUNT INTO v_inventoryLineQtyCount + FROM M_INVENTORYLINE IL + WHERE IL.M_INVENTORYLINE_ID = :NEW.M_INVENTORYLINE_ID; + + SELECT COUNT(1) INTO v_stockCount + FROM DUAL + WHERE EXISTS (SELECT 1 + FROM M_STORAGE_DETAIL SD + WHERE SD.M_PRODUCT_ID = :NEW.M_PRODUCT_ID + AND SD.M_LOCATOR_ID = :NEW.M_LOCATOR_ID + AND SD.M_ATTRIBUTESETINSTANCE_ID = '0' + AND SD.C_UOM_ID = :NEW.C_UOM_ID + AND SD.QTYONHAND <> 0 + AND SD.PREQTYONHAND <> 0); + + IF(v_inventoryLineQtyCount <> 0 OR (v_inventoryLineQtyCount = 0 AND v_stockCount = 0)) THEN + RAISE_APPLICATION_ERROR(-20000, '@20112@' || ' - ' || v_Name) ; + END IF; + ELSE + 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 diff -r 062df9369cb9 -r 79f006ced359 src/org/openbravo/materialmgmt/InventoryCountProcess.java --- a/src/org/openbravo/materialmgmt/InventoryCountProcess.java Tue Jun 26 11:46:32 2018 +0000 +++ b/src/org/openbravo/materialmgmt/InventoryCountProcess.java Tue Jun 26 11:42:34 2018 +0200 @@ -49,10 +49,8 @@ import org.openbravo.materialmgmt.hook.InventoryCountProcessHook; import org.openbravo.model.ad.access.User; import org.openbravo.model.common.enterprise.Organization; -import org.openbravo.model.common.plm.AttributeSet; import org.openbravo.model.common.plm.AttributeSetInstance; import org.openbravo.model.common.plm.Product; -import org.openbravo.model.financialmgmt.calendar.Period; import org.openbravo.model.financialmgmt.calendar.PeriodControl; import org.openbravo.model.materialmgmt.onhandquantity.StorageDetail; import org.openbravo.model.materialmgmt.transaction.InventoryCount; @@ -270,118 +268,165 @@ } private void runChecks(InventoryCount inventory) throws OBException { - try { executeHooks(inventoryCountChecks, inventory); - } catch (Exception e) { - OBException obException = new OBException(e.getMessage(), e.getCause()); - throw obException; + } catch (Exception genericException) { + throw new OBException(genericException.getMessage(), genericException.getCause()); } + checkInventoryAlreadyProcessed(inventory); + checkMandatoryAttributesWithoutVavlue(inventory); + checkDuplicatedProducts(inventory); + Organization org = inventory.getOrganization(); + checkIfOrganizationIsReady(org); + checkOrganizationAllowsTransactions(org); + checkDifferentLegalInLinesAndHeader(inventory, org); + checkPeriodsNotAvailable(inventory, org); + } + private void checkInventoryAlreadyProcessed(InventoryCount inventory) { if (inventory.isProcessed()) { throw new OBException(OBMessageUtils.parseTranslation("@AlreadyPosted@")); } - // Products without attribute set. - StringBuffer where = new StringBuffer(); - where.append(" as icl"); - where.append(" join icl." + InventoryCountLine.PROPERTY_PRODUCT + " as p"); - where.append(" join p." + Product.PROPERTY_ATTRIBUTESET + " as aset"); - where.append(" where icl." + InventoryCountLine.PROPERTY_PHYSINVENTORY + ".id = :inventory"); - where.append(" and aset." + AttributeSet.PROPERTY_REQUIREATLEASTONEVALUE + " = true"); - where.append(" and coalesce(p." + Product.PROPERTY_USEATTRIBUTESETVALUEAS + ", '-') <> 'F'"); - where.append(" and coalesce(icl." + InventoryCountLine.PROPERTY_ATTRIBUTESETVALUE - + ", '0') = '0'"); - where.append(" order by icl." + InventoryCountLine.PROPERTY_LINENO); - OBQuery<InventoryCountLine> iclQry = OBDal.getInstance().createQuery(InventoryCountLine.class, + } + + private void checkMandatoryAttributesWithoutVavlue(InventoryCount inventory) { + InventoryCountLine inventoryLine = getLineWithMandatoryAttributeWithoutValue(inventory); + if (inventoryLine != null) { + throw new OBException(OBMessageUtils.parseTranslation("@Inline@ " + (inventoryLine).getLineNo() + + " @productWithoutAttributeSet@")); + } + } + + private InventoryCountLine getLineWithMandatoryAttributeWithoutValue(InventoryCount inventory) { + StringBuilder where = new StringBuilder(); + where.append(" as icl "); + where.append(" join icl.product as p "); + where.append(" join icl.storageBin as sb "); + where.append(" join p.attributeSet as aset "); + where.append(" where icl.physInventory.id = :inventory "); + where.append(" and aset.requireAtLeastOneValue = true "); + where.append(" and coalesce(p.useAttributeSetValueAs, '-') <> 'F' "); + where.append(" and coalesce(icl.attributeSetValue, '0') = '0' "); + // Allow to regularize to 0 any existing Stock without attribute for this Product + // (this situation can happen when there is a bug in a different part of the code, + // but the user should be able always to zero this stock) + where.append(" and (icl.quantityCount <> 0 "); + where.append(" or (icl.quantityCount = 0 "); + where.append(" and not exists (select 1 from MaterialMgmtStorageDetail sd "); + where.append(" where sd.storageBin.id = sb.id "); + where.append(" and sd.product.id = p.id "); + where.append(" and sd.attributeSetValue = '0' "); + where.append(" and sd.uOM.id = icl.uOM.id "); + where.append(" and sd.quantityOnHand <> 0 "); + where.append(" and sd.quantityInDraftTransactions <> 0 ) "); + where.append(" ) "); + where.append(" ) "); + where.append(" order by icl.lineNo "); + OBQuery<InventoryCountLine> query = 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@")); - } + query.setNamedParameter("inventory", inventory.getId()); + query.setMaxResult(1); + return query.uniqueResult(); + } - // duplicated product - where = new StringBuffer(); - where.append(" as icl"); - where.append(" where icl." + InventoryCountLine.PROPERTY_PHYSINVENTORY + ".id = :inventory"); - where.append(" and exists (select 1 from " + InventoryCountLine.ENTITY_NAME + " as icl2"); - where.append(" where icl." + InventoryCountLine.PROPERTY_PHYSINVENTORY + " = icl2." - + InventoryCountLine.PROPERTY_PHYSINVENTORY); - where.append(" and icl." + InventoryCountLine.PROPERTY_PRODUCT + " = icl2." - + InventoryCountLine.PROPERTY_PRODUCT); - where.append(" and coalesce(icl." + InventoryCountLine.PROPERTY_ATTRIBUTESETVALUE - + ", '0') = coalesce(icl2." + InventoryCountLine.PROPERTY_ATTRIBUTESETVALUE + ", '0')"); - where.append(" and coalesce(icl." + InventoryCountLine.PROPERTY_ORDERUOM - + ", '0') = coalesce(icl2." + InventoryCountLine.PROPERTY_ORDERUOM + ", '0')"); - where.append(" and coalesce(icl." + InventoryCountLine.PROPERTY_UOM + ", '0') = coalesce(icl2." - + InventoryCountLine.PROPERTY_UOM + ", '0')"); - where.append(" and icl." + InventoryCountLine.PROPERTY_STORAGEBIN + " = icl2." - + InventoryCountLine.PROPERTY_STORAGEBIN); - where.append(" and icl." + InventoryCountLine.PROPERTY_LINENO + " <> icl2." - + InventoryCountLine.PROPERTY_LINENO + ")"); - where.append(" order by icl." + InventoryCountLine.PROPERTY_PRODUCT); - where.append(", icl." + InventoryCountLine.PROPERTY_ATTRIBUTESETVALUE); - 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()); - iclQry.setNamedParameter("inventory", inventory.getId()); - List<InventoryCountLine> iclList = iclQry.list(); - if (!iclList.isEmpty()) { - String lines = ""; - for (InventoryCountLine icl2 : iclList) { ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
