public bool SetProductQty1(int recordID, string keyColName, List <string> product, List <string> attribute, List <string> qty, List <string> oline_ID, List <string> locID, int LocToID, List <string> AssetID, int lineID, VAdvantage.Utility.Ctx ctx) { if (keyColName.ToUpper().Trim() == "M_MOVEMENT_ID") { MMovement inv = new MMovement(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MMovementLine invline = new MMovementLine(ctx, lineID, null); invline.SetAD_Client_ID(inv.GetAD_Client_ID()); invline.SetAD_Org_ID(inv.GetAD_Org_ID()); invline.SetM_Locator_ID(Util.GetValueOfInt(locID[i])); invline.SetM_LocatorTo_ID(LocToID); invline.SetM_Product_ID(Util.GetValueOfInt(product[i])); invline.SetMovementQty(Util.GetValueOfDecimal(qty[i])); invline.SetM_Movement_ID(recordID); if (Util.GetValueOfInt(AssetID[i]) > 0) { invline.SetA_Asset_ID(Util.GetValueOfInt(AssetID[i])); } if (Util.GetValueOfInt(oline_ID[i]) > 0) { invline.SetM_RequisitionLine_ID(Util.GetValueOfInt(oline_ID[i])); } if (Util.GetValueOfInt(attribute[i]) != 0) { invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } if (!invline.Save()) { } } } else if (keyColName.ToUpper().Trim() == "M_INVENTORY_ID") { MInventory inv = new MInventory(ctx, recordID, null); for (int i = 0; i < product.Count; i++) { MInventoryLine invline = new MInventoryLine(ctx, lineID, null); invline.SetAD_Client_ID(inv.GetAD_Client_ID()); invline.SetAD_Org_ID(inv.GetAD_Org_ID()); invline.SetM_Product_ID(Util.GetValueOfInt(product[i])); invline.SetQtyInternalUse(Util.GetValueOfDecimal(qty[i])); invline.SetC_Charge_ID(Util.GetValueOfInt(AssetID[i])); invline.SetM_Inventory_ID(recordID); invline.SetIsInternalUse(true); invline.SetM_RequisitionLine_ID(Util.GetValueOfInt(oline_ID[i])); if (Util.GetValueOfInt(attribute[i]) != 0) { invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i])); } else { invline.SetM_AttributeSetInstance_ID(0); } if (!invline.Save()) { } } } return(true); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { log.Info("M_Movement_ID=" + _m_Movement_ID + ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID + ", M_Product_ID=" + _m_product_ID + ", M_Product_Category_ID=" + _m_Product_Category_ID + ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld); _movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx()); if (_movement.Get_ID() == 0) { throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID); } if (_movement.IsProcessed()) { throw new SystemException("@M_Movement_ID@ @Processed@"); } // String sqlQry = ""; if (_deleteOld) { sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' " + "AND M_Movement_ID=" + _m_Movement_ID; int no = DB.ExecuteQuery(sqlQry, null, Get_Trx()); log.Fine("doIt - Deleted #" + no); } // Create Null Storage records if (_qtyRange != null && _qtyRange.Equals("=")) { sqlQry = "INSERT INTO M_Storage " + "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID," + " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) " + "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0," + " l.M_Locator_ID, p.M_Product_ID, 0," + " 0,0,0,null " + "FROM M_Locator l" + " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetM_Warehouse_ID(); if (_m_Locator_ID != 0) { sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID; } sqlQry += " AND l.IsDefault='Y'" + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'" + " AND NOT EXISTS (SELECT * FROM M_Storage s" + " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) " + "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID" + " AND s.M_Product_ID=p.M_Product_ID)"; int no = DB.ExecuteQuery(sqlQry, null, Get_Trx()); log.Fine("'0' Inserted #" + no); } StringBuilder sql = new StringBuilder( "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID," + " s.qtyOnHand, p.M_AttributeSet_ID " + "FROM M_Product p" + " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); // if (_m_Locator_ID != 0) { sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID); } // if (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID)) { sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") "); } // if (_m_Product_Category_ID != 0) { sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID); } // Do not overwrite existing records if (!_deleteOld) { sql.Append(" AND NOT EXISTS (SELECT * FROM M_MovementLine il " + "WHERE il.M_Movement_ID=" + _m_Movement_ID + " AND il.M_Product_ID=s.M_Product_ID" + " AND il.M_Locator_ID=s.M_Locator_ID" + " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0))"); } // + " AND il.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID)"); // sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product // int count = 0; IDataReader idr = null; try { idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx()); while (idr.Read()) { int M_Product_ID = Util.GetValueOfInt(idr[0]); int M_Locator_ID = Util.GetValueOfInt(idr[1]); int M_AttributeSetInstance_ID = Util.GetValueOfInt(idr[2]); Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]); if (qtyOnHand == null) { qtyOnHand = Env.ZERO; } int M_AttributeSet_ID = Util.GetValueOfInt(idr[4]); // int compare = qtyOnHand.CompareTo(Env.ZERO); if (_qtyRange == null || (_qtyRange.Equals(">") && compare > 0) || (_qtyRange.Equals("<") && compare < 0) || (_qtyRange.Equals("=") && compare == 0) || (_qtyRange.Equals("N") && compare != 0)) { //Save data on Movement Line _line = new MMovementLine(GetCtx(), 0, Get_Trx()); _line.SetAD_Client_ID(_movement.GetAD_Client_ID()); _line.SetAD_Org_ID(_movement.GetAD_Org_ID()); _line.SetM_Movement_ID(_m_Movement_ID); _line.SetM_Locator_ID(_m_Locator_ID); _line.SetM_LocatorTo_ID(_m_LocatorTo_ID); _line.SetMovementQty(qtyOnHand); _line.SetProcessed(false); _line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); _line.SetM_Product_ID(M_Product_ID); if (!_line.Save()) { log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID); } else { count = count + 1; } } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // return("@M_MovementLine_ID@ - #" + count); }
} // createRequisition /// <summary> /// Create Inventory Movements /// </summary> private void CreateMovements() { int noMoves = 0; String info = ""; // MClient client = null; MMovement move = null; int M_Warehouse_ID = 0; int M_WarehouseSource_ID = 0; MWarehouse whSource = null; MWarehouse whTarget = null; string param = ""; if (_M_WareSource != null) { param = _M_WareSource; } else { param = "M_WarehouseSource_ID IS NOT NULL"; } X_T_Replenish[] replenishs = GetReplenish(param);; for (int i = 0; i < replenishs.Length; i++) { X_T_Replenish replenish = replenishs[i]; if (whSource == null || whSource.GetM_WarehouseSource_ID() != replenish.GetM_WarehouseSource_ID()) { whSource = MWarehouse.Get(GetCtx(), replenish.GetM_WarehouseSource_ID()); } if (whTarget == null || whTarget.GetM_Warehouse_ID() != replenish.GetM_Warehouse_ID()) { whTarget = MWarehouse.Get(GetCtx(), replenish.GetM_Warehouse_ID()); } if (client == null || client.GetAD_Client_ID() != whSource.GetAD_Client_ID()) { client = MClient.Get(GetCtx(), whSource.GetAD_Client_ID()); } // if (move == null || M_WarehouseSource_ID != replenish.GetM_WarehouseSource_ID() || M_Warehouse_ID != replenish.GetM_Warehouse_ID()) { M_WarehouseSource_ID = replenish.GetM_WarehouseSource_ID(); M_Warehouse_ID = replenish.GetM_Warehouse_ID(); move = new MMovement(GetCtx(), 0, Get_TrxName()); move.SetC_DocType_ID(_C_DocType_ID); move.SetDescription(Msg.GetMsg(GetCtx(), "Replenishment") + ": " + whSource.GetName() + "->" + whTarget.GetName()); // Set Org move.SetAD_Org_ID(whSource.GetAD_Org_ID()); if (!move.Save()) { return; } log.Fine(move.ToString()); noMoves++; info += " - " + move.GetDocumentNo(); } MProduct product = MProduct.Get(GetCtx(), replenish.GetM_Product_ID()); // To int M_LocatorTo_ID = GetLocator_ID(product, whTarget); // From: Look-up Storage MProductCategory pc = MProductCategory.Get(GetCtx(), product.GetM_Product_Category_ID()); String MMPolicy = pc.GetMMPolicy(); if (MMPolicy == null || MMPolicy.Length == 0) { MMPolicy = client.GetMMPolicy(); } // MStorage[] storages = MStorage.GetWarehouse(GetCtx(), whSource.GetM_Warehouse_ID(), replenish.GetM_Product_ID(), 0, 0, true, null, MClient.MMPOLICY_FiFo.Equals(MMPolicy), Get_TrxName()); if (storages == null || storages.Length == 0) { AddLog("No Inventory in " + whSource.GetName() + " for " + product.GetName()); continue; } // Decimal target = replenish.GetQtyToOrder(); for (int j = 0; j < storages.Length; j++) { MStorage storage = storages[j]; //if (storage.GetQtyOnHand().signum() <= 0) if (Env.Signum(storage.GetQtyOnHand()) <= 0) { continue; } Decimal moveQty = target; if (storage.GetQtyOnHand().CompareTo(moveQty) < 0) { moveQty = storage.GetQtyOnHand(); } // MMovementLine line = new MMovementLine(move); line.SetM_Product_ID(replenish.GetM_Product_ID()); line.SetMovementQty(moveQty); if (replenish.GetQtyToOrder().CompareTo(moveQty) != 0) { line.SetDescription("Total: " + replenish.GetQtyToOrder()); } line.SetM_Locator_ID(storage.GetM_Locator_ID()); // from line.SetM_AttributeSetInstance_ID(storage.GetM_AttributeSetInstance_ID()); line.SetM_LocatorTo_ID(M_LocatorTo_ID); // to line.SetM_AttributeSetInstanceTo_ID(storage.GetM_AttributeSetInstance_ID()); line.Save(); // //target = target.subtract(moveQty); target = Decimal.Subtract(target, moveQty); //if (target.signum() == 0) if (Env.Signum(target) == 0) { break; } } if (Env.Signum(target) != 0) { AddLog("Insufficient Inventory in " + whSource.GetName() + " for " + product.GetName() + " Qty=" + target); } } if (replenishs.Length == 0) { _info = "No Source Warehouse"; log.Warning(_info); } else { _info = "#" + noMoves + info; log.Info(_info); } } // createRequisition
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { log.Info("M_Movement_ID=" + _m_Movement_ID + ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID + ", M_Product_ID=" + _m_product_ID + ", M_Product_Category_ID=" + _m_Product_Category_ID + ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld); _movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx()); if (_movement.Get_ID() == 0) { throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID); } if (_movement.IsProcessed()) { throw new SystemException("@M_Movement_ID@ @Processed@"); } // is used to check Container applicable into system isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx()); // String sqlQry = ""; if (_deleteOld) { sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' " + "AND M_Movement_ID=" + _m_Movement_ID; int no = DB.ExecuteQuery(sqlQry, null, Get_Trx()); log.Fine("doIt - Deleted #" + no); } // Create Null Storage records if (_qtyRange != null && _qtyRange.Equals("=")) { sqlQry = "INSERT INTO M_Storage " + "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID," + " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) " + "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0," + " l.M_Locator_ID, p.M_Product_ID, 0," + " 0,0,0,null " + "FROM M_Locator l" + " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetM_Warehouse_ID(); if (_m_Locator_ID != 0) { sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID; } sqlQry += " AND l.IsDefault='Y'" + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'" + " AND NOT EXISTS (SELECT * FROM M_Storage s" + " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) " + "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID" + " AND s.M_Product_ID=p.M_Product_ID)"; int no = DB.ExecuteQuery(sqlQry, null, Get_Trx()); log.Fine("'0' Inserted #" + no); } StringBuilder sql = null; if (!isContainerApplicable) { sql = new StringBuilder( "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID," + " s.qtyOnHand, p.M_AttributeSet_ID, 0 AS M_ProductContainer_ID " + "FROM M_Product p" + " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } else { sql = new StringBuilder( "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID," + " NVL(SUM(s.Qty) , 0) AS qtyOnHand , p.M_AttributeSet_ID, s.M_ProductContainer_ID " + "FROM M_Product p" + " INNER JOIN M_ContainerStorage s ON (s.M_Product_ID=p.M_Product_ID)" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } // if (_m_Locator_ID != 0) { sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID); } // if (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID)) { sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") "); } // if (_m_Product_Category_ID != 0) { sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID); } // Do not overwrite existing records if (!_deleteOld) { sql.Append(" AND NOT EXISTS (SELECT * FROM M_MovementLine il " + "WHERE il.M_Movement_ID=" + _m_Movement_ID + " AND il.M_Product_ID=s.M_Product_ID" + " AND il.M_Locator_ID=s.M_Locator_ID" + " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0)"); if (!isContainerApplicable) { sql.Append(@" ) "); } else { sql.Append(@" AND COALESCE(il.M_ProductContainer_ID,0)=COALESCE(s.M_ProductContainer_ID,0) ) "); } } // if (!isContainerApplicable) { sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product } else { sql.Append(@" GROUP BY s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID, s.M_ProductContainer_ID, s.Qty ORDER BY s.M_Locator_ID, s.M_Product_ID, s.Qty DESC, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID,s.M_ProductContainer_ID"); // Locator/Product } // int count = 0; IDataReader idr = null; DataTable dt = null; MProduct product = null; try { idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx()); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) // while (idr.Read()) { int M_Product_ID = Util.GetValueOfInt(dr[0]); product = MProduct.Get(GetCtx(), M_Product_ID); int M_Locator_ID = Util.GetValueOfInt(dr[1]); int M_AttributeSetInstance_ID = Util.GetValueOfInt(dr[2]); Decimal qtyOnHand = Util.GetValueOfDecimal(dr[3]); //if (qtyOnHand == null) commented by manjot Because Decimal is Never equals to Null // qtyOnHand = Env.ZERO; int M_AttributeSet_ID = Util.GetValueOfInt(dr[4]); //container int container_Id = Util.GetValueOfInt(dr[5]); // int compare = qtyOnHand.CompareTo(Env.ZERO); if (_qtyRange == null || (_qtyRange.Equals(">") && compare > 0) || (_qtyRange.Equals("<") && compare < 0) || (_qtyRange.Equals("=") && compare == 0) || (_qtyRange.Equals("N") && compare != 0)) { //Save data on Movement Line _line = new MMovementLine(GetCtx(), 0, Get_Trx()); _line.SetAD_Client_ID(_movement.GetAD_Client_ID()); _line.SetAD_Org_ID(_movement.GetAD_Org_ID()); _line.SetM_Movement_ID(_m_Movement_ID); _line.SetM_Locator_ID(_m_Locator_ID); _line.SetM_LocatorTo_ID(_m_LocatorTo_ID); _line.SetMovementQty(qtyOnHand); _line.SetProcessed(false); _line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); _line.SetM_Product_ID(M_Product_ID); if (isContainerApplicable && _line.Get_ColumnIndex("M_ProductContainer_ID") > 0) { _line.SetM_ProductContainer_ID(container_Id); } if (_line.Get_ColumnIndex("C_UOM_ID") > 0 && product != null) { _line.SetC_UOM_ID(product.GetC_UOM_ID()); } if (_line.Get_ColumnIndex("QtyEntered") > 0) { _line.SetQtyEntered(qtyOnHand); } if (!_line.Save()) { return(GetRetrievedError(_line, "Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID)); //log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID); } else { count = count + 1; } } } //idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // return("@M_MovementLine_ID@ - #" + count); }
/// <summary> /// Move stock to location /// </summary> /// <param name="target">storage</param> /// <returns>no of movements</returns> private int Move(MStorage target) { log.Info(target.ToString()); Decimal qty = Decimal.Negate(target.GetQtyOnHand());//.negate(); // Create Movement MMovement mh = new MMovement(GetCtx(), 0, Get_Trx()); mh.SetC_DocType_ID(_C_DocType_ID); mh.SetDescription(GetName()); if (!mh.Save()) { return(0); } int lines = 0; MStorage[] sources = GetSources(target.GetM_Product_ID(), target.GetM_Locator_ID()); for (int i = 0; i < sources.Length; i++) { MStorage source = sources[i]; // Movement Line MMovementLine ml = new MMovementLine(mh); ml.SetM_Product_ID(target.GetM_Product_ID()); ml.SetM_LocatorTo_ID(target.GetM_Locator_ID()); ml.SetM_AttributeSetInstanceTo_ID(target.GetM_AttributeSetInstance_ID()); // From ml.SetM_Locator_ID(source.GetM_Locator_ID()); ml.SetM_AttributeSetInstance_ID(source.GetM_AttributeSetInstance_ID()); Decimal qtyMove = qty; if (qtyMove.CompareTo(source.GetQtyOnHand()) > 0) { qtyMove = source.GetQtyOnHand(); } ml.SetMovementQty(qtyMove); // lines++; ml.SetLine(lines * 10); if (!ml.Save()) { return(0); } qty = Decimal.Subtract(qty, qtyMove); if (Env.Signum(qty) <= 0) { break; } } // for all movements // Process //mh.ProcessIt(MMovement.ACTION_Complete); mh.ProcessIt(DocActionVariables.ACTION_COMPLETE); mh.Save(); AddLog(0, null, new Decimal(lines), "@M_Movement_ID@ " + mh.GetDocumentNo() + " (" + MRefList.Get(GetCtx(), MMovement.DOCSTATUS_AD_Reference_ID, mh.GetDocStatus(), Get_Trx()) + ")"); EliminateReservation(target); return(lines); }