public string RetriveInventoryTransfer(RetrieveFromStorage objPallet) { string msg = ""; try { InventoryTransferServices it = new InventoryTransferServices(); InvTransfer invTransfer = new InvTransfer(); invTransfer.company = objPallet.Company; invTransfer.plant = objPallet.Plant; //invTransfer.id = objPallet.Id; //invTransfer.id = objPallet.Id.ToString(); invTransfer.id = "Loose Picking : Retrieve " + objPallet.Id.ToString(); invTransfer.partNum = objPallet.PartNum; invTransfer.qty = objPallet.Qty; invTransfer.uom = objPallet.UOMCode; invTransfer.frmWarehouse = objPallet.FromWareHouse; invTransfer.frmBinNum = objPallet.FromBinNum; invTransfer.frmLotNum = objPallet.LotNum; invTransfer.toWarehouse = objPallet.ToWareHouse; invTransfer.toBinNum = objPallet.ToBinNum; invTransfer.toLotNum = objPallet.LotNum; it.doInvTransfer(invTransfer); } catch (Exception ex) { msg = ex.Message.ToString(); } return(msg); }
public RetrieveFromStorage GetLotFromStorage(string Company, string PartNum, string LotNum) { RetrieveFromStorage objRetreive = new RetrieveFromStorage(); DAL objDAL = new DAL(); objRetreive = objDAL.GetLotFromStorage(Company, PartNum, LotNum); return(objRetreive); }
public string InsertLPRetrieveFromStorage(RetrieveFromStorage objInventory) { string msg = string.Empty; try { if (string.IsNullOrEmpty(objInventory.Company)) { throw new Exception("Company is Require!"); } if (objInventory.SONum < 1) { throw new Exception("SONum is Require!"); } if (string.IsNullOrEmpty(objInventory.LotNum)) { throw new Exception("LotNum is Require!"); } if (objInventory.Qty <= 0) { throw new Exception("Qty is Require!"); } msg = RetriveInventoryTransfer(objInventory); if (msg == "") { DAL objDAL = new DAL(); msg = objDAL.InsertInventory(objInventory); } } catch (Exception ex) { //msg = ex.Message.ToString(); throw new Exception(ex.Message.ToString()); } return(msg); }
public string InsertInventory(RetrieveFromStorage objInventory) { string msg = string.Empty; bool isSqlSucess = true; SqlTransaction Trans = null; SqlCommand cmd = null; try { using (SqlConnection Conn = new SqlConnection(StagingConStr)) { Conn.Open(); Trans = Conn.BeginTransaction(); StringBuilder strQry = null; //Header SQL if (objInventory != null) { strQry = new StringBuilder(); strQry.Append(" INSERT INTO [WOLPRetrieveFromStorageTbl] "); strQry.Append("([Company],[Plant],[UD108Key1],[SONum],[PartNum],[PartDesc], "); strQry.Append("[LotNum],[Qty],[UOMCode],[FromWareHouse],[FromBinNum],[ToWareHouse] "); strQry.Append(",[ToBinNum],[UserId]) "); strQry.Append("Values "); strQry.Append($" ( '{objInventory.Company}' , '{objInventory.Plant}' , '{objInventory.UD108Key1}' , "); strQry.Append($" {objInventory.SONum} , '{objInventory.PartNum}' , '{objInventory.PartDesc}' , "); strQry.Append($" '{objInventory.LotNum}' , {objInventory.Qty} , '{objInventory.UOMCode}' ,'{objInventory.FromWareHouse}' , '{objInventory.FromBinNum}' , "); strQry.Append($" '{objInventory.ToWareHouse}','{objInventory.ToBinNum}' , "); strQry.Append($" '{objInventory.UserId}' ) "); //Execute insert Query cmd = new SqlCommand(strQry.ToString(), Conn, Trans); cmd.ExecuteNonQuery(); //Pending... //Epicor POHeader and PODtl ud columns updations //UpdateInventoryEpicor(objInventory); } Trans.Commit(); // msg += Label.Generate(objPOHeader); } } catch (Exception ex) { msg += $"\n{ ex.Message.ToString()}"; isSqlSucess = false; throw new Exception(msg); } finally { try { if (Trans != null && !isSqlSucess) { Trans.Rollback(); } } catch (Exception ex) { string exmsg = ex.Message.ToString(); } } return(msg); }
public RetrieveFromStorage GetLotFromStorage(string Company, string PartNum, string LotNum) { RetrieveFromStorage objRetrieve = null; StringBuilder sbQry = new StringBuilder(); //Added by AMIN Start //Issue #155_1 sbQry = new StringBuilder(); sbQry.Append("Select case"); sbQry.Append($" when (select top 1 PartNum from erp.partlot where Company = '{Company}' and PartNum = '{PartNum}' and LotNum='{LotNum}') is null then 'Lot No {LotNum} does not belong to the selected product!'"); sbQry.Append($" when (select top 1 PartNum from erp.PartBin pb inner join WhseBin w on pb.Company = w.Company "); sbQry.Append($" and pb.WarehouseCode = w.WarehouseCode and pb.BinNum = w.BinNum where pb.Company = '{Company}'"); sbQry.Append($" and pb.PartNum='{PartNum}' and (w.FS_DefaultLoose_c = 1 or FS_Loading_c = 1 or FS_DefaultReserve_c = 1) and pb.LotNum='{LotNum}') is not null then 'Lot No {LotNum} is not in the Main Warehouse!'"); sbQry.Append(" else '0' end as ErrMsg"); string ErrMsg = string.Empty; using (SqlConnection connection = new SqlConnection(EpicorConStr)) { connection.Open(); SqlCommand command = new SqlCommand(sbQry.ToString(), connection); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { ErrMsg = reader["ErrMsg"].ToString(); } } } if (ErrMsg != "0") { throw new Exception(ErrMsg); } //Added by AMIN End else { sbQry = new StringBuilder(); sbQry.Append("Select x.Company,x.PartNum,x.LotNum,(x.OnhandQty-x.AllocatedQty) as OnhandQty,x.WarehouseCode as FromWareHouse,x.BinNum as FromBinNum "); sbQry.Append(", isnull((Select top 1 a.WarehouseCode from WhseBin a where a.Company=x.Company and a.FS_DefaultLoose_c=1),'') as ToWareHouse "); sbQry.Append(", isnull((Select top 1 a.BinNum from WhseBin a where a.Company=x.Company and a.FS_DefaultLoose_c=1),'') as ToBinNum "); sbQry.Append("from erp.PartBin x inner join WhseBin z on x.Company=z.Company and x.WarehouseCode=z.WarehouseCode and x.BinNum=z.BinNum "); sbQry.Append($"where z.FS_DefaultLoose_c=0 and z.FS_DefaultReserve_c=0 and z.FS_Loading_c=0 and x.Company='{Company}' and x.PartNum='{PartNum}' and x.LotNum='{LotNum}' "); using (SqlConnection connection = new SqlConnection(EpicorConStr)) { connection.Open(); SqlCommand command = new SqlCommand(sbQry.ToString(), connection); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { objRetrieve = new RetrieveFromStorage(); objRetrieve.Company = reader["Company"].ToString(); objRetrieve.PartNum = reader["PartNum"].ToString(); objRetrieve.PartDesc = string.Empty; objRetrieve.LotNum = reader["LotNum"].ToString(); objRetrieve.Qty = Convert.ToDecimal(reader["OnhandQty"]); objRetrieve.FromWareHouse = reader["FromWareHouse"].ToString(); objRetrieve.FromBinNum = reader["FromBinNum"].ToString(); objRetrieve.ToWareHouse = reader["ToWareHouse"].ToString(); objRetrieve.ToBinNum = reader["ToBinNum"].ToString(); } } } return(objRetrieve); } }