/// <summary> /// Get Inventory Line with parameters /// </summary> /// <param name="inventory">inventory</param> /// <param name="M_Locator_ID">locator</param> /// <param name="M_Product_ID">product</param> /// <param name="M_AttributeSetInstance_ID">asi</param> /// <returns>line or null</returns> public static MInventoryLine Get(MInventory inventory, int M_Locator_ID, int M_Product_ID, int M_AttributeSetInstance_ID) { MInventoryLine retValue = null; String sql = "SELECT * FROM M_InventoryLine " + "WHERE M_Inventory_ID=@invenid AND M_Locator_ID=@locid" + " AND M_Product_ID=@prodid AND M_AttributeSetInstance_ID=@asiid"; try { SqlParameter[] param = new SqlParameter[4]; param[0] = new SqlParameter("@invenid", inventory.GetM_Inventory_ID()); param[1] = new SqlParameter("@locid", M_Locator_ID); param[2] = new SqlParameter("@prodid", M_Product_ID); param[3] = new SqlParameter("@asiid", M_AttributeSetInstance_ID); DataSet ds = DataBase.DB.ExecuteDataset(sql, param); if (ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { retValue = new MInventoryLine(inventory.GetCtx(), dr, inventory.Get_TrxName()); } } } catch (Exception e) { _log.Log(Level.SEVERE, sql, e); } return(retValue); }
/** * Parent Constructor * @param parent parent * @param M_AttributeSetInstance_ID asi * @param MovementQty qty */ public MInventoryLineMA(MInventoryLine parent, int M_AttributeSetInstance_ID, Decimal MovementQty) : this(parent.GetCtx(), 0, parent.Get_TrxName()) { SetClientOrg(parent); SetM_InventoryLine_ID(parent.GetM_InventoryLine_ID()); // SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); SetMovementQty(MovementQty); }
/// <summary> /// Create Material Allocations for new Instances /// </summary> /// <param name="updateQtyBooked"></param> public void CreateMA(bool updateQtyBooked) { VAdvantage.Model.MInventoryLine INVLine = new VAdvantage.Model.MInventoryLine(GetCtx(), GetM_InventoryLine_ID(), Get_TrxName()); int delMA = MInventoryLineMA.DeleteInventoryLineMA(GetM_InventoryLine_ID(), Get_TrxName()); log.Info("DeletedMA=" + delMA); MStorage[] storages = MStorage.GetAll(GetCtx(), GetM_Product_ID(), GetM_Locator_ID(), Get_TrxName()); bool allZeroASI = true; for (int i = 0; i < storages.Length; i++) { if (storages[i].GetM_AttributeSetInstance_ID() != 0) { allZeroASI = false; break; } } if (allZeroASI) { return; } MInventoryLineMA ma = null; Decimal sum = Env.ZERO; for (int i = 0; i < storages.Length; i++) { MStorage storage = storages[i]; // nnayak - ignore negative layers if (Env.Signum(storage.GetQtyOnHand()) <= 0) { continue; } if (ma != null && ma.GetM_AttributeSetInstance_ID() == storage.GetM_AttributeSetInstance_ID()) { ma.SetMovementQty(Decimal.Add(ma.GetMovementQty(), storage.GetQtyOnHand())); } else { ma = new MInventoryLineMA(INVLine, storage.GetM_AttributeSetInstance_ID(), storage.GetQtyOnHand()); } if (!ma.Save()) { ; } sum = Decimal.Add(sum, storage.GetQtyOnHand()); } if (updateQtyBooked && sum.CompareTo(GetQtyBook()) != 0) { log.Warning("QtyBook=" + GetQtyBook() + " corrected to Sum of MA=" + sum); SetQtyBook(sum); } }
/// <summary> /// Is Used to Get or Create Instance of MInventoryLineMA (Attribute) /// </summary> /// <param name="line"></param> /// <param name="M_AttributeSetInstance_ID"></param> /// <param name="MovementQty"></param> /// <param name="DateMaterialPolicy"></param> /// <returns></returns> public static MInventoryLineMA GetOrCreate(MInventoryLine line, int M_AttributeSetInstance_ID, Decimal MovementQty, DateTime?DateMaterialPolicy) { MInventoryLineMA retValue = null; String sql = "SELECT * FROM M_InventoryLineMA " + @" WHERE M_InventoryLine_ID = " + line.GetM_InventoryLine_ID() + @" AND MMPolicyDate = " + GlobalVariable.TO_DATE(DateMaterialPolicy, true) + @" AND "; 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; } DataTable dt = null; IDataReader idr = null; try { idr = DB.ExecuteReader(sql, null, line.Get_Trx()); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) { retValue = new MInventoryLineMA(line.GetCtx(), dr, line.Get_Trx()); } } catch (Exception ex) { if (idr != null) { idr.Close(); } _log.Log(Level.SEVERE, sql, ex); } finally { if (idr != null) { idr.Close(); } dt = null; } if (retValue == null) { retValue = new MInventoryLineMA(line, M_AttributeSetInstance_ID, MovementQty, DateMaterialPolicy); } else { retValue.SetMovementQty(Decimal.Add(retValue.GetMovementQty(), MovementQty)); } return(retValue); }
/// <summary> /// Parent Constructor /// </summary> /// <param name="parent"></param> /// <param name="M_AttributeSetInstance_ID"></param> /// <param name="movementQty"></param> /// <param name="MMPloicyDate"></param> public MInventoryLineMA(MInventoryLine parent, int M_AttributeSetInstance_ID, Decimal movementQty, DateTime?MMPloicyDate) : this(parent.GetCtx(), 0, parent.Get_TrxName()) { SetClientOrg(parent); SetM_InventoryLine_ID(parent.GetM_InventoryLine_ID()); // SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); SetMovementQty(movementQty); if (MMPloicyDate == null) { MMPloicyDate = parent.GetParent().GetMovementDate(); } SetMMPolicyDate(MMPloicyDate); }
/// <summary> /// Create Difference Document. /// Creates one or two inventory lines /// </summary> /// <param name="move">movement</param> /// <param name="confirm">confirm line</param> /// <returns>true if created</returns> private Boolean CreateDifferenceDoc(MMovement move, MMovementLineConfirm confirm) { string query = ""; int result = 0; decimal currentQty = 0; MMovementLine mLine = confirm.GetLine(); // Difference - Create Inventory Difference for Source Location if (Env.ZERO.CompareTo(confirm.GetDifferenceQty()) != 0) { // Get Warehouse for Source MLocator loc = MLocator.Get(GetCtx(), mLine.GetM_Locator_ID()); if (_inventoryFrom != null && _inventoryFrom.GetM_Warehouse_ID() != loc.GetM_Warehouse_ID()) { _inventoryFrom = null; } if (_inventoryFrom == null) { MWarehouse wh = MWarehouse.Get(GetCtx(), loc.GetM_Warehouse_ID()); _inventoryFrom = new MInventory(wh); _inventoryFrom.SetDescription(Msg.Translate(GetCtx(), "M_MovementConfirm_ID") + " " + GetDocumentNo()); if (!_inventoryFrom.Save(Get_TrxName())) { _processMsg += "Inventory not created"; return(false); } // First Inventory if (GetM_Inventory_ID() == 0) { SetM_Inventory_ID(_inventoryFrom.GetM_Inventory_ID()); _inventoryInfo = _inventoryFrom.GetDocumentNo(); } else { _inventoryInfo += "," + _inventoryFrom.GetDocumentNo(); } } log.Info("createDifferenceDoc - Difference=" + confirm.GetDifferenceQty()); MInventoryLine line = new MInventoryLine(_inventoryFrom, mLine.GetM_Locator_ID(), mLine.GetM_Product_ID(), mLine.GetM_AttributeSetInstance_ID(), confirm.GetDifferenceQty(), Env.ZERO); //Added By amit 11-jun-2015 //Opening Stock , Qunatity Book => CurrentQty From Transaction of MovementDate //As On Date Count = Opening Stock - Diff Qty //Qty Count = Qty Book - Diff Qty query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate = " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @" AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID(); result = Util.GetValueOfInt(DB.ExecuteScalar(query)); if (result > 0) { query = @"SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID = (SELECT MAX(M_Transaction_ID) FROM M_Transaction WHERE movementdate = (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate <= " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @" AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @") AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @") AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID(); currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(query)); } else { query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @" AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID(); result = Util.GetValueOfInt(DB.ExecuteScalar(query)); if (result > 0) { query = @"SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID = (SELECT MAX(M_Transaction_ID) FROM M_Transaction WHERE movementdate = (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @" AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @") AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @") AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID(); currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(query)); } } //End line.SetAdjustmentType("D"); line.SetDifferenceQty(Util.GetValueOfDecimal(confirm.GetDifferenceQty())); line.SetQtyBook(currentQty); line.SetOpeningStock(currentQty); line.SetAsOnDateCount(Decimal.Subtract(Util.GetValueOfDecimal(line.GetOpeningStock()), Util.GetValueOfDecimal(confirm.GetDifferenceQty()))); line.SetQtyCount(Decimal.Subtract(Util.GetValueOfDecimal(line.GetQtyBook()), Util.GetValueOfDecimal(confirm.GetDifferenceQty()))); line.SetDescription(Msg.Translate(GetCtx(), "DifferenceQty")); if (!line.Save(Get_TrxName())) { _processMsg += "Inventory Line not created"; return(false); } confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID()); } // Difference // Scrapped - Create Inventory Difference for TarGet Location if (Env.ZERO.CompareTo(confirm.GetScrappedQty()) != 0) { // Get Warehouse for TarGet MLocator loc = MLocator.Get(GetCtx(), mLine.GetM_LocatorTo_ID()); if (_inventoryTo != null && _inventoryTo.GetM_Warehouse_ID() != loc.GetM_Warehouse_ID()) { _inventoryTo = null; } if (_inventoryTo == null) { MWarehouse wh = MWarehouse.Get(GetCtx(), loc.GetM_Warehouse_ID()); _inventoryTo = new MInventory(wh); _inventoryTo.SetDescription(Msg.Translate(GetCtx(), "M_MovementConfirm_ID") + " " + GetDocumentNo()); if (!_inventoryTo.Save(Get_TrxName())) { _processMsg += "Inventory not created"; return(false); } // First Inventory if (GetM_Inventory_ID() == 0) { SetM_Inventory_ID(_inventoryTo.GetM_Inventory_ID()); _inventoryInfo = _inventoryTo.GetDocumentNo(); } else { _inventoryInfo += "," + _inventoryTo.GetDocumentNo(); } } log.Info("CreateDifferenceDoc - Scrapped=" + confirm.GetScrappedQty()); MInventoryLine line = new MInventoryLine(_inventoryTo, mLine.GetM_LocatorTo_ID(), mLine.GetM_Product_ID(), mLine.GetM_AttributeSetInstance_ID(), confirm.GetScrappedQty(), Env.ZERO); line.SetDescription(Msg.Translate(GetCtx(), "ScrappedQty")); if (!line.Save(Get_TrxName())) { _processMsg += "Inventory Line not created"; return(false); } confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID()); } // Scrapped return(true); }
/** * Create Difference Document * @param inout shipment/receipt * @param confirm confirm line * @return true if created */ private bool CreateDifferenceDoc(MInOut inout, MInOutLineConfirm confirm) { if (_processMsg == null) { _processMsg = ""; } else if (_processMsg.Length > 0) { _processMsg += "; "; } // Credit Memo if linked Document if (Env.Signum(confirm.GetDifferenceQty()) != 0 && !inout.IsSOTrx() && !inout.IsReturnTrx() && inout.GetRef_InOut_ID() != 0) { log.Info("Difference=" + confirm.GetDifferenceQty()); if (_creditMemo == null) { _creditMemo = new MInvoice(inout, null); _creditMemo.SetDescription(Msg.Translate(GetCtx(), "M_InOutConfirm_ID") + " " + GetDocumentNo()); _creditMemo.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APCREDITMEMO); if (!_creditMemo.Save(Get_TrxName())) { _processMsg += "Credit Memo not created"; return(false); } SetC_Invoice_ID(_creditMemo.GetC_Invoice_ID()); } MInvoiceLine line = new MInvoiceLine(_creditMemo); line.SetShipLine(confirm.GetLine()); line.SetQty(confirm.GetDifferenceQty()); // Entered/Invoiced if (!line.Save(Get_TrxName())) { _processMsg += "Credit Memo Line not created"; return(false); } confirm.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID()); } // Create Inventory Difference if (Env.Signum(confirm.GetScrappedQty()) != 0) { log.Info("Scrapped=" + confirm.GetScrappedQty()); if (_inventory == null) { MWarehouse wh = MWarehouse.Get(GetCtx(), inout.GetM_Warehouse_ID()); _inventory = new MInventory(wh); _inventory.SetDescription(Msg.Translate(GetCtx(), "M_InOutConfirm_ID") + " " + GetDocumentNo()); if (!_inventory.Save(Get_TrxName())) { _processMsg += "Inventory not created"; return(false); } SetM_Inventory_ID(_inventory.GetM_Inventory_ID()); } MInOutLine ioLine = confirm.GetLine(); MInventoryLine line = new MInventoryLine(_inventory, ioLine.GetM_Locator_ID(), ioLine.GetM_Product_ID(), ioLine.GetM_AttributeSetInstance_ID(), confirm.GetScrappedQty(), Env.ZERO); if (!line.Save(Get_TrxName())) { _processMsg += "Inventory Line not created"; return(false); } confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID()); } // if (!confirm.Save(Get_TrxName())) { _processMsg += "Confirmation Line not saved"; return(false); } return(true); }