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);
}
}
