public static MStorageDetail GetCreate(Ctx ctx, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, String type, Trx trx) { if (M_Locator_ID == 0) { throw new ArgumentException("M_Locator_ID=0"); } if (M_Product_ID == 0) { throw new ArgumentException("M_Product_ID=0"); } MStorageDetail retValue = GetForUpdate(ctx, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, type, trx); if (retValue != null) { return(retValue); } // Insert row based on locator MLocator locator = new MLocator(ctx, M_Locator_ID, trx); if (locator.Get_ID() != M_Locator_ID) { throw new ArgumentException("Not found M_Locator_ID=" + M_Locator_ID); } // retValue = new MStorageDetail(locator, M_Product_ID, M_AttributeSetInstance_ID, type); retValue.Save(trx); s_log.Fine("New " + retValue); return(retValue); }
private static MStorageDetail Get(Ctx ctx, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, String type, Boolean forUpdate, Trx trx) { MStorageDetail retValue = null; String sql = "SELECT * FROM M_StorageDetail " + "WHERE M_Locator_ID=" + M_Locator_ID + " AND M_Product_ID=" + M_Product_ID + " AND QtyType='" + type + "' AND ";//type.GetValue() if (M_AttributeSetInstance_ID == 0) { sql += "(M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID + " OR M_AttributeSetInstance_ID IS NULL)"; } else { sql += "M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID; } if (forUpdate) { sql += " FOR UPDATE "; } IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, trx); DataTable dt = new DataTable(); dt.Load(idr); idr.Close(); if (dt.Rows.Count > 0) { retValue = new MStorageDetail(ctx, dt.Rows[0], trx); } } catch (Exception ex) { s_log.Log(Level.SEVERE, sql, ex); } finally { if (idr != null) { idr.Close(); idr = null; } } if (retValue == null) { s_log.Fine("Not Found - M_Locator_ID=" + M_Locator_ID + ", M_Product_ID=" + M_Product_ID + ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID); } else { s_log.Fine("M_Locator_ID=" + M_Locator_ID + ", M_Product_ID=" + M_Product_ID + ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID); } return(retValue); }
/* public static Map<X_Ref_Quantity_Type, MStorageDetail> getAllForUpdate( * Ctx ctx, int M_Locator_ID, int M_Product_ID, * int M_AttributeSetInstance_ID, * Set<X_Ref_Quantity_Type> updateQtyTypes, Trx trx) { * Map<X_Ref_Quantity_Type, MStorageDetail> storageDetails = new HashMap<X_Ref_Quantity_Type, MStorageDetail>(); * for (X_Ref_Quantity_Type qtyType : X_Ref_Quantity_Type.values()) { * MStorageDetail store = MStorageDetail.Get(ctx, M_Locator_ID, * M_Product_ID, M_AttributeSetInstance_ID, qtyType, * (updateQtyTypes == null ? false : updateQtyTypes * .contains(qtyType)), trx); * storageDetails.put(qtyType, store); * } * return storageDetails; * }*/ public static Dictionary <String, MStorageDetail> GetAllForUpdate(Ctx ctx, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, List <String> updateQtyTypes, Trx trx) { Dictionary <String, MStorageDetail> storageDetails = new Dictionary <String, MStorageDetail>(); //Array qtyType = Enum.GetValues(typeof(X_Ref_Quantity_Type.X_Ref_Quantity_TypeEnum)); Array qtyType = X_Ref_Quantity_Type.Get().ToArray(); for (int i = 0; i < qtyType.Length; i++) { MStorageDetail store = MStorageDetail.Get(ctx, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, qtyType.GetValue(i).ToString(), (updateQtyTypes == null ? false : updateQtyTypes.Contains(qtyType.GetValue(i).ToString())), trx); //storageDetails.put(qtyType, store); storageDetails[qtyType.GetValue(i).ToString()] = store; } return(storageDetails); }
public static Boolean Set(Ctx ctx, int M_Warehouse_ID, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, int reservationAttributeSetInstance_ID, Decimal newQty, String type, Trx trx) { MStorageDetail retValue = null; String sql = "UPDATE M_StorageDetail " + "SET QTY=" + newQty + "WHERE M_Locator_ID=" + M_Locator_ID + " AND M_Product_ID=" + M_Product_ID + " AND QtyType='" + type + "' AND ";//type.GetValue() if (M_AttributeSetInstance_ID == 0) { sql += "(M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID + " OR M_AttributeSetInstance_ID IS NULL)"; } else { sql += "M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID; } try { int i = DB.ExecuteQuery(sql, null, trx); if (i != 1) { s_log.SaveError("M_StorageDetailNotUpdated", sql); } } catch (Exception ex) { s_log.Log(Level.SEVERE, sql, ex); } if (retValue == null) { s_log.Fine("Not Found - M_Locator_ID=" + M_Locator_ID + ", M_Product_ID=" + M_Product_ID + ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID); } else { s_log.Fine("M_Locator_ID=" + M_Locator_ID + ", M_Product_ID=" + M_Product_ID + ", M_AttributeSetInstance_ID=" + M_AttributeSetInstance_ID); } return(true); }
/// <summary> /// Before Save /// </summary> /// <param name="newRecord"></param> /// <returns></returns> protected override Boolean BeforeSave(Boolean newRecord) { //Check for lock on Locator and Product //if (Is_ValueChanged("Qty") && IsType(X_Ref_Quantity_Type.ON_HAND) && // MCycleCountLock.LockExists(GetCtx(), GetM_Product_ID(), GetM_Locator_ID(), Get_TrxName())) //{ // s_log.SaveError("Error", Msg.GetMsg(GetCtx(), "LocatorLocked")); // return false; //} if (isBulkUpdate) { // validation must already done at storage level not detail/type level // see Storage.Record.validate() return(base.BeforeSave(newRecord)); } // Negative Inventory check if (newRecord || (Is_ValueChanged("Qty") && (IsType(X_Ref_Quantity_Type.ON_HAND) || IsType(X_Ref_Quantity_Type.DEDICATED) || IsType(X_Ref_Quantity_Type.ALLOCATED) || IsType(X_Ref_Quantity_Type.EXPECTED))) ) { MWarehouse wh = MWarehouse.Get(GetCtx(), GetM_Warehouse_ID()); if (wh.IsDisallowNegativeInv()) { if (Env.Signum(GetQty()) < 0) { s_log.SaveError("Error", Msg.GetMsg(GetCtx(), "NegativeInventoryDisallowed")); return(false); } Decimal qtyOnHand = Env.ZERO; Decimal qtyDedicated = Env.ZERO; Decimal qtyAllocated = Env.ZERO; String sql = "SELECT SUM(QtyOnHand),SUM(QtyDedicated),SUM(QtyAllocated) " + "FROM M_Storage_V s" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE s.M_Product_ID=" + GetM_Product_ID() // #1 + " AND l.M_Warehouse_ID=" + GetM_Warehouse_ID() + " AND l.M_Locator_ID=" + GetM_Locator_ID(); IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, Get_TrxName()); if (idr.Read()) { qtyOnHand = VAdvantage.Utility.Util.GetValueOfDecimal(idr[0]); if (idr.IsDBNull(0))//if (idr.wasNull()) { qtyOnHand = Env.ZERO; } qtyDedicated = VAdvantage.Utility.Util.GetValueOfDecimal(idr[1]); if (idr.IsDBNull(1))// wasNull()) { qtyDedicated = Env.ZERO; } qtyAllocated = VAdvantage.Utility.Util.GetValueOfDecimal(idr[2]); if (idr.IsDBNull(2))// wasNull()) { qtyAllocated = Env.ZERO; } } idr.Close(); } catch (Exception e) { s_log.Log(Level.SEVERE, sql, e); } finally { if (idr != null) { idr.Close(); idr = null; } } Decimal?asiQtyOnHand = Decimal.Zero; Decimal?asiQtyDedicated = Decimal.Zero; Decimal?asiQtyAllocated = Decimal.Zero; if (IsType(X_Ref_Quantity_Type.ON_HAND)) { if (newRecord) { qtyOnHand = Decimal.Add(qtyOnHand, GetQty()); } else { qtyOnHand = Decimal.Subtract(Decimal.Add(qtyOnHand, GetQty()), Util.GetValueOfDecimal(Get_ValueOld("Qty"))); } asiQtyOnHand = GetQty(); asiQtyAllocated = MStorageDetail.GetQty(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), X_Ref_Quantity_Type.ALLOCATED, Get_TrxName()); asiQtyDedicated = MStorageDetail.GetQty(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), X_Ref_Quantity_Type.DEDICATED, Get_TrxName()); } else if (IsType(X_Ref_Quantity_Type.DEDICATED)) { if (newRecord) { qtyDedicated = Decimal.Add(qtyDedicated, GetQty()); } else { qtyDedicated = Decimal.Subtract(Decimal.Add(qtyDedicated, GetQty()), Util.GetValueOfDecimal(Get_ValueOld("Qty"))); } asiQtyOnHand = MStorageDetail.GetQty(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), X_Ref_Quantity_Type.ON_HAND, Get_TrxName()); asiQtyAllocated = MStorageDetail.GetQty(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), X_Ref_Quantity_Type.ALLOCATED, Get_TrxName()); asiQtyDedicated = GetQty(); } else if (IsType(X_Ref_Quantity_Type.ALLOCATED)) { if (newRecord) { qtyAllocated = Decimal.Add(qtyAllocated, GetQty()); } else { qtyAllocated = Decimal.Subtract(Decimal.Add(qtyAllocated, GetQty()), Util.GetValueOfDecimal(Get_ValueOld("Qty"))); } asiQtyOnHand = MStorageDetail.GetQty(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), X_Ref_Quantity_Type.ON_HAND, Get_TrxName()); asiQtyAllocated = GetQty(); asiQtyDedicated = MStorageDetail.GetQty(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), X_Ref_Quantity_Type.DEDICATED, Get_TrxName()); } asiQtyOnHand = asiQtyOnHand == null ? Env.ZERO : asiQtyOnHand; asiQtyDedicated = asiQtyDedicated == null ? Env.ZERO : asiQtyDedicated; asiQtyAllocated = asiQtyAllocated == null ? Env.ZERO : asiQtyAllocated; if (qtyOnHand.CompareTo(Decimal.Add(qtyDedicated, qtyAllocated)) < 0 || asiQtyOnHand.Value.CompareTo(Decimal.Add(asiQtyDedicated.Value, asiQtyAllocated.Value)) < 0) { s_log.SaveError("Error", Msg.GetMsg(GetCtx(), "NegativeInventoryDisallowed")); return(false); } } } return(base.BeforeSave(newRecord)); }
/// <summary> /// detail Add /// Warehouse must already be validated /// diffQty must always be positive; negative values are not processed /// </summary> /// <param name="ctx"></param> /// <param name="M_Warehouse_ID"></param> /// <param name="M_Locator_ID"></param> /// <param name="M_Product_ID"></param> /// <param name="M_AttributeSetInstance_ID"></param> /// <param name="reservationAttributeSetInstance_ID"></param> /// <param name="diffQty"></param> /// <param name="type"></param> /// <param name="trx"></param> /// <returns></returns> public static Boolean Add(Ctx ctx, int M_Warehouse_ID, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID, int reservationAttributeSetInstance_ID, Decimal diffQty, String type, Trx trx) { StringBuilder diffText = new StringBuilder("("); MStorageDetail storage = null; storage = MStorageDetail.GetCreate(ctx, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, type, trx); // Verify if (storage.GetM_Locator_ID() != M_Locator_ID && storage.GetM_Product_ID() != M_Product_ID && storage.GetM_AttributeSetInstance_ID() != M_AttributeSetInstance_ID) { s_log.Severe("No Storage found - M_Locator_ID=" + M_Locator_ID + ",M_Product_ID=" + M_Product_ID + ",ASI=" + M_AttributeSetInstance_ID); return(false); } MStorageDetail storageASI = null; if ((M_AttributeSetInstance_ID != reservationAttributeSetInstance_ID) && (type == X_Ref_Quantity_Type.RESERVED || type == X_Ref_Quantity_Type.ORDERED)) { int reservationM_Locator_ID = M_Locator_ID; if (reservationAttributeSetInstance_ID == 0) { MWarehouse wh = MWarehouse.Get(ctx, M_Warehouse_ID); reservationM_Locator_ID = wh.GetDefaultM_Locator_ID(); } storageASI = MStorageDetail.Get(ctx, reservationM_Locator_ID, M_Product_ID, reservationAttributeSetInstance_ID, type, true, trx); if (storageASI == null) // create if not existing - should not happen { MProduct product = MProduct.Get(ctx, M_Product_ID); int xM_Locator_ID = MProductLocator.GetFirstM_Locator_ID(product, M_Warehouse_ID); if (xM_Locator_ID == 0) { MWarehouse wh = MWarehouse.Get(ctx, M_Warehouse_ID); xM_Locator_ID = wh.GetDefaultM_Locator_ID(); } storageASI = MStorageDetail.GetCreate(ctx, xM_Locator_ID, M_Product_ID, reservationAttributeSetInstance_ID, type, trx); } } Boolean changed = false; if (Env.Signum(diffQty) != 0) { if (storageASI == null) { storage.SetQty(Decimal.Add(storage.GetQty(), diffQty)); } else { storageASI.SetQty(Decimal.Add(storageASI.GetQty(), diffQty)); } diffText.Append(type.ToString()).Append("=").Append(diffQty); changed = true; } if (changed) { diffText.Append(") -> ").Append(storage.ToString()); s_log.Fine(diffText.ToString()); if (storageASI != null) { storageASI.Save(trx); // No AttributeSetInstance } // (reserved/ordered) return(storage.Save(trx)); } return(true); }