/// <summary> /// Deletes the transaction. /// </summary> /// <param name="miscIssueMasterId">The misc issue master id.</param> public void DeleteTransaction(int miscIssueMasterId) { const string methodName = This + ".DeleteTransaction()"; try { using (var trans = new TransactionScope()) { using (var db = new PCSDataContext(Utils.Instance.ConnectionString)) { var miscTranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.IVMiscellaneousIssue.ToString()).TranTypeID; var deleteTranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.DeleteTransaction.ToString()).TranTypeID; var serverDate = db.GetServerDate(); #region IV_MiscellaneousIssueMaster var issueMaster = db.IV_MiscellaneousIssueMasters.FirstOrDefault(e => e.MiscellaneousIssueMasterID == miscIssueMasterId); if (issueMaster == null) { return; } #endregion #region old transaction history to be updated var oldSourceHistory = db.MST_TransactionHistories.Where(e => e.RefMasterID == miscIssueMasterId && e.TranTypeID == miscTranTypeId); foreach (var history in oldSourceHistory) { // mark as delete transaction history.TranTypeID = deleteTranTypeId; history.UserName = SystemProperty.UserName; } #endregion #region cache data foreach (IV_MiscellaneousIssueDetail issueDetail in issueMaster.IV_MiscellaneousIssueDetails) { var allowNegative = issueDetail.ITM_Product.AllowNegativeQty.GetValueOrDefault(false); #region subtract from destionation cache if (issueMaster.DesMasLocationID.GetValueOrDefault(0) > 0) { #region bin cache var destBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.DesLocationID && e.BinID == issueMaster.DesBinID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (destBin == null || destBin.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, destBin == null ? 0 : destBin.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (destBin != null) { destBin.OHQuantity = destBin.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity; } else { // create new record destBin = new IV_BinCache { BinID = issueMaster.DesBinID.GetValueOrDefault(0), CCNID = issueMaster.CCNID, LocationID = issueMaster.DesLocationID.GetValueOrDefault(0), MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.Quantity }; db.IV_BinCaches.InsertOnSubmit(destBin); } #endregion #region location cache var destLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.DesMasLocationID && e.LocationID == issueMaster.DesLocationID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (destLocation == null || destLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, destLocation == null ? 0 : destLocation.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (destLocation != null) { destLocation.OHQuantity = destLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity; } else { // create new record destLocation = new IV_LocationCache { CCNID = issueMaster.CCNID, LocationID = issueMaster.DesLocationID.GetValueOrDefault(0), MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.Quantity }; db.IV_LocationCaches.InsertOnSubmit(destLocation); } #endregion #region master location cache var destMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.DesMasLocationID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (destMasLocation == null || destMasLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, destMasLocation == null ? 0 : destMasLocation.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (destMasLocation != null) { destMasLocation.OHQuantity = destMasLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity; } else { // create new record destMasLocation = new IV_MasLocCache { CCNID = issueMaster.CCNID, MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.Quantity }; db.IV_MasLocCaches.InsertOnSubmit(destMasLocation); } #endregion #region Transaction history var destHistory = new MST_TransactionHistory { CCNID = issueMaster.CCNID, StockUMID = issueDetail.StockUMID, MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, LocationID = issueMaster.DesLocationID, BinID = issueMaster.DesBinID, RefMasterID = issueMaster.MiscellaneousIssueMasterID, RefDetailID = issueDetail.MiscellaneousIssueDetailID, PostDate = issueMaster.PostDate, TransDate = serverDate, Quantity = -issueDetail.Quantity, UserName = SystemProperty.UserName, TranTypeID = miscTranTypeId, BinCommitQuantity = destBin.CommitQuantity, BinOHQuantity = destBin.OHQuantity, IssuePurposeID = issueMaster.IssuePurposeID, LocationCommitQuantity = destLocation.CommitQuantity, LocationOHQuantity = destLocation.OHQuantity, MasLocCommitQuantity = destMasLocation.CommitQuantity, MasLocOHQuantity = destMasLocation.OHQuantity }; db.MST_TransactionHistories.InsertOnSubmit(destHistory); #endregion } #endregion #region add to source cache #region bin cache var sourceBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.SourceLocationID && e.BinID == issueMaster.SourceBinID && e.ProductID == issueDetail.ProductID); if (sourceBin != null) { sourceBin.OHQuantity = sourceBin.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity; } else { // create new record sourceBin = new IV_BinCache { BinID = issueMaster.SourceBinID.GetValueOrDefault(0), CCNID = issueMaster.CCNID, LocationID = issueMaster.SourceLocationID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, OHQuantity = issueDetail.Quantity }; db.IV_BinCaches.InsertOnSubmit(sourceBin); } #endregion #region location cache var sourceLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.SourceMasLocationID && e.LocationID == issueMaster.SourceLocationID && e.ProductID == issueDetail.ProductID); if (sourceLocation != null) { sourceLocation.OHQuantity = sourceLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity; } else { // create new record sourceLocation = new IV_LocationCache { CCNID = issueMaster.CCNID, LocationID = issueMaster.SourceLocationID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, OHQuantity = issueDetail.Quantity }; db.IV_LocationCaches.InsertOnSubmit(sourceLocation); } #endregion #region master location cache var sourceMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.SourceMasLocationID && e.ProductID == issueDetail.ProductID); if (sourceMasLocation != null) { sourceMasLocation.OHQuantity = sourceMasLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity; } else { // create new record sourceMasLocation = new IV_MasLocCache { CCNID = issueMaster.CCNID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, OHQuantity = issueDetail.Quantity }; db.IV_MasLocCaches.InsertOnSubmit(sourceMasLocation); } #endregion #region Transaction history var sourceHistory = new MST_TransactionHistory { CCNID = issueMaster.CCNID, StockUMID = issueDetail.StockUMID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, LocationID = issueMaster.SourceLocationID, BinID = issueMaster.SourceBinID, RefMasterID = issueMaster.MiscellaneousIssueMasterID, RefDetailID = issueDetail.MiscellaneousIssueDetailID, PostDate = issueMaster.PostDate, TransDate = serverDate, Quantity = issueDetail.Quantity, UserName = SystemProperty.UserName, TranTypeID = miscTranTypeId, BinCommitQuantity = sourceBin.CommitQuantity, BinOHQuantity = sourceBin.OHQuantity, IssuePurposeID = issueMaster.IssuePurposeID, LocationCommitQuantity = sourceLocation.CommitQuantity, LocationOHQuantity = sourceLocation.OHQuantity, MasLocCommitQuantity = sourceMasLocation.CommitQuantity, MasLocOHQuantity = sourceMasLocation.OHQuantity }; db.MST_TransactionHistories.InsertOnSubmit(sourceHistory); #endregion #endregion db.IV_MiscellaneousIssueDetails.DeleteOnSubmit(issueDetail); } #endregion db.IV_MiscellaneousIssueMasters.DeleteOnSubmit(issueMaster); db.SubmitChanges(); trans.Complete(); } } } catch (PCSBOException ex) { if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex); } if (ex.mCode == ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE) { throw; } throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex); } }
public void SaveCompletion(DataTable dataSource, DateTime postDate, string multiTransNo, int masterLocationId, int shiftId, int purposeId) { const string methodName = This + ".SaveCompletion()"; try { using (var trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })) { using (var dataContext = new PCSDataContext(Utils.Instance.ConnectionString)) { int tranTypeId = dataContext.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionType.WORK_ORDER_COMPLETION).TranTypeID; var serverDate = dataContext.GetServerDate(); foreach (var woCompletion in from DataRow row in dataSource.Rows where row.RowState != DataRowState.Deleted && row.RowState != DataRowState.Detached select CreateNewCompletion(row, postDate, serverDate, multiTransNo, masterLocationId, shiftId, purposeId)) { // save work order completion transaction first dataContext.PRO_WorkOrderCompletions.InsertOnSubmit(woCompletion); dataContext.SubmitChanges(ConflictMode.ContinueOnConflict); #region get list of components and subtract quantity var components = dataContext.ITM_BOMs.Where(b => b.ProductID == woCompletion.ProductID); // DO NOT ALLOW USER COMPLETE THE WORK ORDER THAT NOT HAS COMPONENT if (components.Count() == 0) { throw new PCSBOException(ErrorCode.MESSAGE_NOT_HAS_COMPONENT_TO_COMPLETE, methodName, new Exception()); } var binCacheData = from binCache in dataContext.IV_BinCaches join bin in dataContext.MST_BINs on binCache.BinID equals bin.BinID where bin.LocationID == woCompletion.PRO_WorkOrderMaster.PRO_ProductionLine.LocationID.GetValueOrDefault(0) && bin.BinTypeID == (int?)BinTypeEnum.IN select binCache; int locationId = woCompletion.PRO_WorkOrderMaster.PRO_ProductionLine.LocationID.GetValueOrDefault(0); //Subtract quantity in buffer location foreach (var component in components) { var subtractQuantity = woCompletion.CompletedQuantity * component.Quantity; var binCache = binCacheData.FirstOrDefault(b => b.ProductID == component.ComponentID); // check onhand quantity in case component not allow negative onhand quantity if (!component.ITM_Product.AllowNegativeQty.GetValueOrDefault(false)) { // not enough quantity to subtract if (binCache == null || subtractQuantity > binCache.OHQuantity.GetValueOrDefault(0)) { var productError = new Hashtable { { PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD, woCompletion.WorkOrderDetailID } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, component.ITM_Product.Code, new Exception(), productError); } } #region substract Master Location Cache var masLocCache = dataContext.IV_MasLocCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.ProductID == component.ComponentID); if (masLocCache != null) { masLocCache.OHQuantity = masLocCache.OHQuantity.GetValueOrDefault(0) - subtractQuantity; } else { masLocCache = new IV_MasLocCache { CCNID = woCompletion.CCNID, MasterLocationID = woCompletion.MasterLocationID, ProductID = component.ComponentID, OHQuantity = -subtractQuantity, Lot = woCompletion.Lot }; dataContext.IV_MasLocCaches.InsertOnSubmit(masLocCache); } decimal?masLocOnHand = masLocCache.OHQuantity; decimal?masLocCommit = masLocCache.CommitQuantity; #endregion #region substract Location Cache var locCache = dataContext.IV_LocationCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == locationId && m.ProductID == component.ComponentID); if (locCache != null) { locCache.OHQuantity = locCache.OHQuantity.GetValueOrDefault(0) - subtractQuantity; } else { locCache = new IV_LocationCache { CCNID = woCompletion.CCNID, MasterLocationID = woCompletion.MasterLocationID, LocationID = locationId, ProductID = component.ComponentID, OHQuantity = -subtractQuantity, Lot = woCompletion.Lot }; dataContext.IV_LocationCaches.InsertOnSubmit(locCache); } decimal?locOnHand = locCache.OHQuantity; decimal?locCommit = locCache.CommitQuantity; #endregion #region substract Bin Cache var componentBinCache = dataContext.IV_BinCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == locationId && m.BinID == binCache.BinID && m.ProductID == component.ComponentID); if (componentBinCache != null) { componentBinCache.OHQuantity = componentBinCache.OHQuantity.GetValueOrDefault(0) - subtractQuantity; } else { componentBinCache = new IV_BinCache { CCNID = woCompletion.CCNID, MasterLocationID = woCompletion.MasterLocationID, LocationID = locationId, BinID = binCache.BinID, ProductID = component.ComponentID, OHQuantity = -subtractQuantity, Lot = woCompletion.Lot }; dataContext.IV_BinCaches.InsertOnSubmit(componentBinCache); } decimal?binOnHand = componentBinCache.OHQuantity; decimal?binCommit = componentBinCache.CommitQuantity; #endregion #region transaction history var transHistory = new MST_TransactionHistory { BinID = binCache.BinID, TransDate = serverDate, TranTypeID = tranTypeId, LocationID = locationId, ProductID = component.ComponentID, CCNID = woCompletion.CCNID, Lot = woCompletion.Lot, MasterLocationID = woCompletion.MasterLocationID, StockUMID = component.ITM_Product.StockUMID, Serial = woCompletion.Serial, PostDate = woCompletion.PostDate, RefMasterID = woCompletion.WorkOrderCompletionID, Quantity = -subtractQuantity, MasLocCommitQuantity = masLocCommit, BinCommitQuantity = binCommit, LocationCommitQuantity = locCommit, MasLocOHQuantity = masLocOnHand, LocationOHQuantity = locOnHand, BinOHQuantity = binOnHand, UserName = SystemProperty.UserName, IssuePurposeID = (int?)PurposeEnum.Completion }; dataContext.MST_TransactionHistories.InsertOnSubmit(transHistory); #endregion } #endregion #region add completion quantity to cache and add transaction history #region add Master Location Cache var masLoc = dataContext.IV_MasLocCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.ProductID == woCompletion.ProductID); if (masLoc != null) { masLoc.OHQuantity = masLoc.OHQuantity.GetValueOrDefault(0) + woCompletion.CompletedQuantity; } else { masLoc = new IV_MasLocCache { CCNID = woCompletion.CCNID, MasterLocationID = woCompletion.MasterLocationID, ProductID = woCompletion.ProductID, OHQuantity = woCompletion.CompletedQuantity, Lot = woCompletion.Lot }; dataContext.IV_MasLocCaches.InsertOnSubmit(masLoc); } decimal?masOnHand = masLoc.OHQuantity; decimal?masCommit = masLoc.CommitQuantity; #endregion #region add Location Cache var lCache = dataContext.IV_LocationCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == woCompletion.LocationID && m.ProductID == woCompletion.ProductID); if (lCache != null) { lCache.OHQuantity = lCache.OHQuantity.GetValueOrDefault(0) + woCompletion.CompletedQuantity; } else { lCache = new IV_LocationCache { CCNID = woCompletion.CCNID, MasterLocationID = woCompletion.MasterLocationID, LocationID = woCompletion.LocationID, ProductID = woCompletion.ProductID, OHQuantity = woCompletion.CompletedQuantity, Lot = woCompletion.Lot }; dataContext.IV_LocationCaches.InsertOnSubmit(lCache); } decimal?lOnhand = lCache.OHQuantity; decimal?lCommit = lCache.CommitQuantity; #endregion #region add Bin Cache var bCache = dataContext.IV_BinCaches.FirstOrDefault(m => m.MasterLocationID == woCompletion.MasterLocationID && m.LocationID == woCompletion.LocationID && m.BinID == woCompletion.BinID && m.ProductID == woCompletion.ProductID); if (bCache != null) { bCache.OHQuantity = bCache.OHQuantity.GetValueOrDefault(0) + woCompletion.CompletedQuantity; } else { bCache = new IV_BinCache { CCNID = woCompletion.CCNID, MasterLocationID = woCompletion.MasterLocationID, LocationID = woCompletion.LocationID, BinID = woCompletion.BinID.GetValueOrDefault(0), ProductID = woCompletion.ProductID, OHQuantity = woCompletion.CompletedQuantity, Lot = woCompletion.Lot }; dataContext.IV_BinCaches.InsertOnSubmit(bCache); } decimal?bOnhand = bCache.OHQuantity; decimal?bCommit = bCache.CommitQuantity; #endregion #region transaction history var transHistory1 = new MST_TransactionHistory { BinID = woCompletion.BinID, TransDate = serverDate, TranTypeID = tranTypeId, LocationID = woCompletion.LocationID, ProductID = woCompletion.ProductID, CCNID = woCompletion.CCNID, Lot = woCompletion.Lot, MasterLocationID = woCompletion.MasterLocationID, StockUMID = woCompletion.StockUMID, Serial = woCompletion.Serial, PostDate = woCompletion.PostDate, RefMasterID = woCompletion.WorkOrderCompletionID, RefDetailID = woCompletion.WorkOrderCompletionID, Quantity = woCompletion.CompletedQuantity, MasLocCommitQuantity = masCommit, BinCommitQuantity = bCommit, LocationCommitQuantity = lCommit, MasLocOHQuantity = masOnHand, LocationOHQuantity = lOnhand, BinOHQuantity = bOnhand, UserName = SystemProperty.UserName, IssuePurposeID = (int?)PurposeEnum.Completion }; dataContext.MST_TransactionHistories.InsertOnSubmit(transHistory1); #endregion #endregion #region update work order detail status if completed var orderQuantity = woCompletion.PRO_WorkOrderDetail.OrderQuantity; var totalCompleted = dataContext.PRO_WorkOrderCompletions.Where( w => w.WorkOrderDetailID == woCompletion.WorkOrderDetailID).Sum(w => w.CompletedQuantity); if (totalCompleted >= orderQuantity) { dataContext.PRO_WorkOrderDetails.FirstOrDefault(w => w.WorkOrderDetailID == woCompletion.WorkOrderDetailID).Status = (byte?)WOLineStatus.MfgClose; } #endregion // submit changes for each completion transaction dataContext.SubmitChanges(ConflictMode.ContinueOnConflict); #region refresh all changes made since we already submit changes var changeSet = dataContext.GetChangeSet(); var refreshedTables = new List <ITable>(); foreach (var item in changeSet.Updates) { var table = dataContext.GetTable(item.GetType()); if (refreshedTables.Contains(table)) { continue; } refreshedTables.Add(table); dataContext.Refresh(RefreshMode.OverwriteCurrentValues, table); } foreach (var item in changeSet.Deletes) { var table = dataContext.GetTable(item.GetType()); if (refreshedTables.Contains(table)) { continue; } refreshedTables.Add(table); dataContext.Refresh(RefreshMode.OverwriteCurrentValues, table); } foreach (var item in changeSet.Inserts) { var table = dataContext.GetTable(item.GetType()); if (refreshedTables.Contains(table)) { continue; } refreshedTables.Add(table); dataContext.Refresh(RefreshMode.OverwriteCurrentValues, table); } #endregion } } trans.Complete(); } } catch (SqlException ex) { if (ex.Errors.Count > 1) { if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex); } throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex); } throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex); } }
/// <summary> /// Adds the and return id. /// </summary> /// <param name="pobjMaster">The pobj master.</param> /// <param name="pdstData">The PDST data.</param> /// <param name="serverDate">The server date.</param> /// <returns></returns> public int AddAndReturnId(object pobjMaster, DataSet pdstData, DateTime serverDate) { const string methodName = This + ".AddAndReturnId()"; try { using (var trans = new TransactionScope()) { using (var db = new PCSDataContext(Utils.Instance.ConnectionString)) { var miscTranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.IVMiscellaneousIssue.ToString()).TranTypeID; #region IV_MiscellaneousIssueMaster var issueMaster = new IV_MiscellaneousIssueMaster(); var pobjMasterVO = (IV_MiscellaneousIssueMasterVO)pobjMaster; issueMaster.CCNID = pobjMasterVO.CCNID; issueMaster.Comment = pobjMasterVO.Comment; if (pobjMasterVO.DesBinID > 0) { issueMaster.DesBinID = pobjMasterVO.DesBinID; } if (pobjMasterVO.DesLocationID > 0) { issueMaster.DesLocationID = pobjMasterVO.DesLocationID; } if (pobjMasterVO.DesMasLocationID > 0) { issueMaster.DesMasLocationID = pobjMasterVO.DesMasLocationID; } issueMaster.IssuePurposeID = pobjMasterVO.IssuePurposeID; issueMaster.PartyID = null; issueMaster.PostDate = pobjMasterVO.PostDate; issueMaster.SourceBinID = pobjMasterVO.SourceBinID; issueMaster.SourceLocationID = pobjMasterVO.SourceLocationID; issueMaster.SourceMasLocationID = pobjMasterVO.SourceMasLocationID; issueMaster.TransNo = pobjMasterVO.TransNo; issueMaster.UserName = SystemProperty.UserName; issueMaster.LastChange = serverDate; #endregion #region Insert IV_MiscellaneousIssueDetail foreach (DataRow dr in pdstData.Tables[0].Rows) { if (dr.RowState == DataRowState.Deleted) { continue; } var objDetail = new IV_MiscellaneousIssueDetail { ProductID = Convert.ToInt32(dr[IV_MiscellaneousIssueDetailTable.PRODUCTID_FLD]), Quantity = Convert.ToDecimal(dr[IV_MiscellaneousIssueDetailTable.QUANTITY_FLD]), StockUMID = Convert.ToInt32(dr[IV_MiscellaneousIssueDetailTable.STOCKUMID_FLD]) }; if (dr[IV_MiscellaneousIssueDetailTable.LOT_FLD] != DBNull.Value) { objDetail.Lot = dr[IV_MiscellaneousIssueDetailTable.LOT_FLD].ToString(); } if (dr[IV_MiscellaneousIssueDetailTable.AVAILABLEQTY_FLD] != DBNull.Value) { objDetail.AvailableQty = Convert.ToDecimal(dr[IV_MiscellaneousIssueDetailTable.AVAILABLEQTY_FLD]); } issueMaster.IV_MiscellaneousIssueDetails.Add(objDetail); } // temporary save master and detail to database db.IV_MiscellaneousIssueMasters.InsertOnSubmit(issueMaster); db.SubmitChanges(); #endregion #region cache data foreach (var issueDetail in issueMaster.IV_MiscellaneousIssueDetails) { var allowNegative = issueDetail.ITM_Product.AllowNegativeQty.GetValueOrDefault(false); #region subtract from source cache #region bin cache var sourceBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.SourceLocationID && e.BinID == issueMaster.SourceBinID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (sourceBin == null || sourceBin.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, sourceBin == null ? 0 : sourceBin.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (sourceBin != null) { sourceBin.OHQuantity = sourceBin.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity; } else { // create new record sourceBin = new IV_BinCache { BinID = issueMaster.SourceBinID.GetValueOrDefault(0), CCNID = issueMaster.CCNID, LocationID = issueMaster.SourceLocationID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.Quantity }; db.IV_BinCaches.InsertOnSubmit(sourceBin); } #endregion #region location cache var sourceLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.SourceMasLocationID && e.LocationID == issueMaster.SourceLocationID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (sourceLocation == null || sourceLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, sourceLocation == null ? 0 : sourceLocation.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (sourceLocation != null) { sourceLocation.OHQuantity = sourceLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity; } else { // create new record sourceLocation = new IV_LocationCache { CCNID = issueMaster.CCNID, LocationID = issueMaster.SourceLocationID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.Quantity }; db.IV_LocationCaches.InsertOnSubmit(sourceLocation); } #endregion #region master location cache var sourceMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.SourceMasLocationID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (sourceMasLocation == null || sourceMasLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.Quantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, sourceMasLocation == null ? 0 : sourceMasLocation.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (sourceMasLocation != null) { sourceMasLocation.OHQuantity = sourceMasLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.Quantity; } else { // create new record sourceMasLocation = new IV_MasLocCache { CCNID = issueMaster.CCNID, MasterLocationID = issueMaster.SourceMasLocationID, ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.Quantity }; db.IV_MasLocCaches.InsertOnSubmit(sourceMasLocation); } #endregion #region Transaction history var sourceHistory = new MST_TransactionHistory { CCNID = pobjMasterVO.CCNID, StockUMID = issueDetail.StockUMID, MasterLocationID = pobjMasterVO.SourceMasLocationID, ProductID = issueDetail.ProductID, LocationID = pobjMasterVO.SourceLocationID, BinID = pobjMasterVO.SourceBinID, RefMasterID = issueMaster.MiscellaneousIssueMasterID, RefDetailID = issueDetail.MiscellaneousIssueDetailID, PostDate = pobjMasterVO.PostDate, TransDate = serverDate, Quantity = -issueDetail.Quantity, UserName = SystemProperty.UserName, TranTypeID = miscTranTypeId, BinCommitQuantity = sourceBin.CommitQuantity, BinOHQuantity = sourceBin.OHQuantity, IssuePurposeID = issueMaster.IssuePurposeID, LocationCommitQuantity = sourceLocation.CommitQuantity, LocationOHQuantity = sourceLocation.OHQuantity, MasLocCommitQuantity = sourceMasLocation.CommitQuantity, MasLocOHQuantity = sourceMasLocation.OHQuantity }; db.MST_TransactionHistories.InsertOnSubmit(sourceHistory); #endregion #endregion #region add to destination cache if (issueMaster.DesMasLocationID.GetValueOrDefault(0) > 0) { #region bin cache var destBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.DesLocationID && e.BinID == issueMaster.DesBinID && e.ProductID == issueDetail.ProductID); if (destBin != null) { destBin.OHQuantity = destBin.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity; } else { // create new record destBin = new IV_BinCache { BinID = issueMaster.DesBinID.GetValueOrDefault(0), CCNID = issueMaster.CCNID, LocationID = issueMaster.DesLocationID.GetValueOrDefault(0), MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, OHQuantity = issueDetail.Quantity }; db.IV_BinCaches.InsertOnSubmit(destBin); } #endregion #region location cache var destLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.DesMasLocationID && e.LocationID == issueMaster.DesLocationID && e.ProductID == issueDetail.ProductID); if (destLocation != null) { destLocation.OHQuantity = destLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity; } else { // create new record destLocation = new IV_LocationCache { CCNID = issueMaster.CCNID, LocationID = issueMaster.DesLocationID.GetValueOrDefault(0), MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, OHQuantity = issueDetail.Quantity }; db.IV_LocationCaches.InsertOnSubmit(destLocation); } #endregion #region master location cache var destMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.DesMasLocationID && e.ProductID == issueDetail.ProductID); if (destMasLocation != null) { destMasLocation.OHQuantity = destMasLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.Quantity; } else { // create new record destMasLocation = new IV_MasLocCache { CCNID = issueMaster.CCNID, MasterLocationID = issueMaster.DesMasLocationID.GetValueOrDefault(0), ProductID = issueDetail.ProductID, OHQuantity = issueDetail.Quantity }; db.IV_MasLocCaches.InsertOnSubmit(destMasLocation); } #endregion #region Transaction history var destHistory = new MST_TransactionHistory { CCNID = pobjMasterVO.CCNID, StockUMID = issueDetail.StockUMID, MasterLocationID = pobjMasterVO.DesMasLocationID, ProductID = issueDetail.ProductID, LocationID = pobjMasterVO.DesLocationID, BinID = pobjMasterVO.DesBinID, RefMasterID = issueMaster.MiscellaneousIssueMasterID, RefDetailID = issueDetail.MiscellaneousIssueDetailID, PostDate = pobjMasterVO.PostDate, TransDate = serverDate, Quantity = issueDetail.Quantity, UserName = SystemProperty.UserName, TranTypeID = miscTranTypeId, BinCommitQuantity = destBin.CommitQuantity, BinOHQuantity = destBin.OHQuantity, IssuePurposeID = issueMaster.IssuePurposeID, LocationCommitQuantity = destLocation.CommitQuantity, LocationOHQuantity = destLocation.OHQuantity, MasLocCommitQuantity = destMasLocation.CommitQuantity, MasLocOHQuantity = destMasLocation.OHQuantity }; db.MST_TransactionHistories.InsertOnSubmit(destHistory); #endregion } #endregion } #endregion db.SubmitChanges(); trans.Complete(); return(issueMaster.MiscellaneousIssueMasterID); } } } catch (PCSBOException ex) { if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex); } if (ex.mCode == ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE) { throw; } throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex); } }
public int AddAndReturnId(object pobjMaster, DataSet pdstDetailData) { const string methodName = "PCSComProduction.WorkOrder.BO.AddAndReturnID()"; try { using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted, Timeout = new TimeSpan(0, 1, 0) })) { using (var db = new PCSDataContext(Utils.Instance.ConnectionString)) { var serverDate = db.GetServerDate(); var tranTypeId = db.MST_TranTypes.FirstOrDefault(t => t.Code == TransactionTypeEnum.PROIssueMaterial.ToString()).TranTypeID; #region add the master first //add the master first var objMaster = (PRO_IssueMaterialMasterVO)pobjMaster; var issueMaster = new PRO_IssueMaterialMaster { MasterLocationID = objMaster.MasterLocationID, PostDate = objMaster.PostDate, IssueNo = objMaster.IssueNo, CCNID = objMaster.CCNID, IssuePurposeID = objMaster.IssuePurposeID, ToLocationID = objMaster.ToLocationID, ToBinID = objMaster.ToBinID, ShiftID = objMaster.ShiftID }; if (objMaster.WorkOrderMasterID > 0) { issueMaster.WorkOrderMasterID = objMaster.WorkOrderMasterID; } if (objMaster.WorkOrderDetailID > 0) { issueMaster.WorkOrderDetailID = objMaster.WorkOrderDetailID; } #endregion #region insert issue detail //update the detail foreach (var issueDetail in from DataRow dr in pdstDetailData.Tables[0].Rows where dr.RowState != DataRowState.Deleted select CreateIssueDetail(dr)) { issueMaster.PRO_IssueMaterialDetails.Add(issueDetail); } db.PRO_IssueMaterialMasters.InsertOnSubmit(issueMaster); db.SubmitChanges(); #endregion #region cache data foreach (var issueDetail in issueMaster.PRO_IssueMaterialDetails) { var allowNegative = issueDetail.ITM_Product.AllowNegativeQty.GetValueOrDefault(false); #region subtract from source cache #region bin cache var sourceBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueDetail.LocationID && e.BinID == issueDetail.BinID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (sourceBin == null || sourceBin.OHQuantity.GetValueOrDefault(0) < issueDetail.CommitQuantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, sourceBin == null ? 0 : sourceBin.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (sourceBin != null) { sourceBin.OHQuantity = sourceBin.OHQuantity.GetValueOrDefault(0) - issueDetail.CommitQuantity; } else { // create new record sourceBin = new IV_BinCache { BinID = issueDetail.BinID.GetValueOrDefault(0), CCNID = issueMaster.CCNID, LocationID = issueDetail.LocationID, MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.CommitQuantity }; db.IV_BinCaches.InsertOnSubmit(sourceBin); } #endregion #region location cache var sourceLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.LocationID == issueDetail.LocationID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (sourceLocation == null || sourceLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.CommitQuantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, sourceLocation == null ? 0 : sourceLocation.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (sourceLocation != null) { sourceLocation.OHQuantity = sourceLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.CommitQuantity; } else { // create new record sourceLocation = new IV_LocationCache { CCNID = issueMaster.CCNID, LocationID = issueDetail.LocationID, MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.CommitQuantity }; db.IV_LocationCaches.InsertOnSubmit(sourceLocation); } #endregion #region master location cache var sourceMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.ProductID == issueDetail.ProductID); if (!allowNegative && (sourceMasLocation == null || sourceMasLocation.OHQuantity.GetValueOrDefault(0) < issueDetail.CommitQuantity)) { var productError = new Hashtable { { ITM_ProductTable.PRODUCTID_FLD, issueDetail.ProductID }, { IV_BinCacheTable.OHQUANTITY_FLD, sourceMasLocation == null ? 0 : sourceMasLocation.OHQuantity.GetValueOrDefault(0) } }; throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, issueDetail.ITM_Product.Code, new Exception(), productError); } if (sourceMasLocation != null) { sourceMasLocation.OHQuantity = sourceMasLocation.OHQuantity.GetValueOrDefault(0) - issueDetail.CommitQuantity; } else { // create new record sourceMasLocation = new IV_MasLocCache { CCNID = issueMaster.CCNID, MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, OHQuantity = -issueDetail.CommitQuantity }; db.IV_MasLocCaches.InsertOnSubmit(sourceMasLocation); } #endregion #region Transaction history var sourceHistory = new MST_TransactionHistory { CCNID = issueMaster.CCNID, StockUMID = issueDetail.StockUMID, MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, LocationID = issueDetail.LocationID, BinID = issueDetail.BinID, RefMasterID = issueMaster.IssueMaterialMasterID, RefDetailID = issueDetail.IssueMaterialDetailID, PostDate = issueMaster.PostDate, TransDate = serverDate, Quantity = -issueDetail.CommitQuantity, UserName = SystemProperty.UserName, TranTypeID = tranTypeId, BinCommitQuantity = sourceBin.CommitQuantity, BinOHQuantity = sourceBin.OHQuantity, IssuePurposeID = issueMaster.IssuePurposeID, LocationCommitQuantity = sourceLocation.CommitQuantity, LocationOHQuantity = sourceLocation.OHQuantity, MasLocCommitQuantity = sourceMasLocation.CommitQuantity, MasLocOHQuantity = sourceMasLocation.OHQuantity }; db.MST_TransactionHistories.InsertOnSubmit(sourceHistory); #endregion #endregion #region add to destination cache #region bin cache var destBin = db.IV_BinCaches.FirstOrDefault(e => e.LocationID == issueMaster.ToLocationID && e.BinID == issueMaster.ToBinID && e.ProductID == issueDetail.ProductID); if (destBin != null) { destBin.OHQuantity = destBin.OHQuantity.GetValueOrDefault(0) + issueDetail.CommitQuantity; } else { // create new record destBin = new IV_BinCache { BinID = issueMaster.ToBinID.GetValueOrDefault(0), CCNID = issueMaster.CCNID, LocationID = issueMaster.ToLocationID.GetValueOrDefault(0), MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, OHQuantity = issueDetail.CommitQuantity }; db.IV_BinCaches.InsertOnSubmit(destBin); } #endregion #region location cache var destLocation = db.IV_LocationCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.LocationID == issueMaster.ToLocationID && e.ProductID == issueDetail.ProductID); if (destLocation != null) { destLocation.OHQuantity = destLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.CommitQuantity; } else { // create new record destLocation = new IV_LocationCache { CCNID = issueMaster.CCNID, LocationID = issueMaster.ToLocationID.GetValueOrDefault(0), MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, OHQuantity = issueDetail.CommitQuantity }; db.IV_LocationCaches.InsertOnSubmit(destLocation); } #endregion #region master location cache var destMasLocation = db.IV_MasLocCaches.FirstOrDefault(e => e.MasterLocationID == issueMaster.MasterLocationID && e.ProductID == issueDetail.ProductID); if (destMasLocation != null) { destMasLocation.OHQuantity = destMasLocation.OHQuantity.GetValueOrDefault(0) + issueDetail.CommitQuantity; } else { // create new record destMasLocation = new IV_MasLocCache { CCNID = issueMaster.CCNID, MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, OHQuantity = issueDetail.CommitQuantity }; db.IV_MasLocCaches.InsertOnSubmit(destMasLocation); } #endregion #region Transaction history var destHistory = new MST_TransactionHistory { CCNID = issueMaster.CCNID, StockUMID = issueDetail.StockUMID, MasterLocationID = issueMaster.MasterLocationID, ProductID = issueDetail.ProductID, LocationID = issueMaster.ToLocationID, BinID = issueMaster.ToBinID, RefMasterID = issueMaster.IssueMaterialMasterID, RefDetailID = issueDetail.IssueMaterialDetailID, PostDate = issueMaster.PostDate, TransDate = serverDate, Quantity = issueDetail.CommitQuantity, UserName = SystemProperty.UserName, TranTypeID = tranTypeId, BinCommitQuantity = destBin.CommitQuantity, BinOHQuantity = destBin.OHQuantity, IssuePurposeID = issueMaster.IssuePurposeID, LocationCommitQuantity = destLocation.CommitQuantity, LocationOHQuantity = destLocation.OHQuantity, MasLocCommitQuantity = destMasLocation.CommitQuantity, MasLocOHQuantity = destMasLocation.OHQuantity }; db.MST_TransactionHistories.InsertOnSubmit(destHistory); #endregion #endregion db.SubmitChanges(); } #endregion db.SubmitChanges(); trans.Complete(); return(issueMaster.IssueMaterialMasterID); } } } catch (PCSBOException ex) { if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex); } if (ex.mCode == ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE) { throw; } throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex); } }