/// <summary> /// Create/Add to Inventory Line Query /// </summary> /// <param name="M_Locator_ID">locator</param> /// <param name="M_Product_ID">product</param> /// <param name="M_AttributeSetInstance_ID">asi</param> /// <param name="qtyOnHand">quantity</param> /// <param name="M_AttributeSet_ID">attribute set</param> /// <returns>lines added</returns> private string InsertInventoryLine(int M_Locator_ID, int M_Product_ID, int lineNo, int M_AttributeSetInstance_ID, Decimal qtyOnHand, int M_AttributeSet_ID) { MInventoryLine line = new MInventoryLine(GetCtx(), 0, Get_Trx()); int line_ID = DB.GetNextID(GetCtx(), "M_InventoryLine", Get_Trx()); string qry = "select m_warehouse_id from m_locator where m_locator_id=" + M_Locator_ID; int M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_Trx())); MWarehouse wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID); if (wh.IsDisallowNegativeInv() == true) { if (qtyOnHand < 0) { return(""); } } MProduct product = MProduct.Get(GetCtx(), M_Product_ID); if (product != null) { int precision = product.GetUOMPrecision(); if (Env.Signum(qtyOnHand) != 0) { qtyOnHand = Decimal.Round(qtyOnHand, precision, MidpointRounding.AwayFromZero); } } string sql = @"INSERT INTO M_InventoryLine (AD_Client_ID, AD_Org_ID,IsActive, Created, CreatedBy, Updated, UpdatedBy, Line, M_Inventory_ID, M_InventoryLine_ID, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, QtyBook, QtyCount, OpeningStock, AsOnDateCount, DifferenceQty, AdjustmentType"; if (line.Get_ColumnIndex("C_UOM_ID") > 0) { sql += ", QtyEntered, C_UOM_ID"; } if (line.Get_ColumnIndex("IsFromProcess") > 0) { sql += ",IsFromProcess"; } sql += " ) VALUES ( " + _inventory.GetAD_Client_ID() + "," + _inventory.GetAD_Org_ID() + ",'Y'," + GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," + GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," + lineNo + "," + _m_Inventory_ID + "," + line_ID + "," + M_Locator_ID + "," + M_Product_ID + "," + M_AttributeSetInstance_ID + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + 0 + ",'A'"; if (line.Get_ColumnIndex("C_UOM_ID") > 0) { sql += "," + qtyOnHand + "," + product.GetC_UOM_ID(); } if (line.Get_ColumnIndex("IsFromProcess") > 0) { sql += ",'Y'"; } string insertQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + ")'); exception when others then null; END;"; return(insertQry); }
/// <summary> /// Create/Add to Inventory Line Query /// </summary> /// <param name="M_Locator_ID">locator</param> /// <param name="M_Product_ID">product</param> /// <param name="M_AttributeSetInstance_ID">asi</param> /// <param name="currentQty">quantity</param> /// <param name="M_AttributeSet_ID">attribute set</param> /// <returns>lines added</returns> private string UpdateInventoryLine(int M_InventoryLine_ID, int M_Product_ID, int M_Locator_ID, Decimal currentQty, string AdjustType, Decimal AsOnDateCount, Decimal DiffQty) { string qry = "select m_warehouse_id from m_locator where m_locator_id=" + M_Locator_ID; int M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_Trx())); MWarehouse wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID); if (wh.IsDisallowNegativeInv() == true) { if (currentQty < 0) { return(""); } } MProduct product = MProduct.Get(GetCtx(), M_Product_ID); if (product != null) { int precision = product.GetUOMPrecision(); if (Env.Signum(currentQty) != 0) { currentQty = Decimal.Round(currentQty, precision, MidpointRounding.AwayFromZero); } } if (AdjustType == "A") { DiffQty = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, AsOnDateCount)); } else if (AdjustType == "D") { AsOnDateCount = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, DiffQty)); } Decimal QtyCount = Util.GetValueOfDecimal(Decimal.Subtract(currentQty, DiffQty)); // Changes by Bharat on 02 Aug 2017 as issue given by Ravikant string sql = @"UPDATE M_InventoryLine SET QtyBook = " + currentQty + ",QtyCount = " + QtyCount + ",OpeningStock = " + currentQty + ",AsOnDateCount = " + AsOnDateCount + ",DifferenceQty = " + DiffQty + " WHERE M_InventoryLine_ID = " + M_InventoryLine_ID; string updateQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + "'); exception when others then null; END;"; return(updateQry); }