public JsonResult GetMInventoryLine(string fields)
{
string retJSON = "";
if (Session["ctx"] != null)
{
VAdvantage.Utility.Ctx ctx = Session["ctx"] as Ctx;
string[] paramValue = fields.Split(',');
int M_InventoryLine_ID;
//Assign parameter value
M_InventoryLine_ID = Util.GetValueOfInt(paramValue[0].ToString());
MInventoryLine iLine = new MInventoryLine(ctx, M_InventoryLine_ID, null);
int M_Product_ID = iLine.GetM_Product_ID();
int M_Locator_ID = iLine.GetM_Locator_ID();
List <int> retlst = new List <int>();
retlst.Add(M_Product_ID);
retlst.Add(M_Locator_ID);
retJSON = JsonConvert.SerializeObject(retlst);
}
return(Json(retJSON, JsonRequestBehavior.AllowGet));
// return Json(new { result = retJSON, error = retError }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// GetMInventoryLine
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Dictionary <string, string> GetMInventoryLine(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int M_InventoryLine_ID;
//Assign parameter value
M_InventoryLine_ID = Util.GetValueOfInt(paramValue[0].ToString());
//End Assign parameter value
MInventoryLine iLine = new MInventoryLine(ctx, M_InventoryLine_ID, null);
int M_Product_ID = iLine.GetM_Product_ID();
int M_Locator_ID = iLine.GetM_Locator_ID();
Dictionary <string, string> retDic = new Dictionary <string, string>();
retDic["M_Product_ID"] = M_Product_ID.ToString();
retDic["M_Locator_ID"] = M_Locator_ID.ToString();
return(retDic);
}
/// <summary>
/// Update Inventory Lines With Material Allocation
/// </summary>
/// <returns>no update</returns>
private int UpdateWithMA()
{
int no = 0;
//
String sql = "SELECT * FROM M_InventoryLine WHERE M_Inventory_ID=@iid AND COALESCE(M_AttributeSetInstance_ID,0)=0 ";
try
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@iid", _m_Inventory_ID);
DataSet ds = DataBase.DB.ExecuteDataset(sql, param, null);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MInventoryLine il = new MInventoryLine(GetCtx(), dr, Get_TrxName());
Decimal onHand = Env.ZERO;
MStorage[] storages = MStorage.GetAll(GetCtx(), il.GetM_Product_ID(), il.GetM_Locator_ID(), Get_TrxName());
MInventoryLineMA ma = null;
for (int i = 0; i < storages.Length; i++)
{
MStorage storage = storages[i];
if (Env.Signum(storage.GetQtyOnHand()) == 0)
{
continue;
}
onHand = Decimal.Add(onHand, storage.GetQtyOnHand());
// No ASI
if (storage.GetM_AttributeSetInstance_ID() == 0 &&
storages.Length == 1)
{
continue;
}
// Save ASI
ma = new MInventoryLineMA(il,
storage.GetM_AttributeSetInstance_ID(), storage.GetQtyOnHand());
if (!ma.Save())
{
;
}
}
il.SetQtyBook(onHand);
il.SetQtyCount(onHand);
if (il.Save())
{
no++;
}
}
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql, e);
}
//
log.Info("#" + no);
return(no);
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Inventory_ID=" + _m_Inventory_ID);
MInventory inventory = new MInventory(GetCtx(), _m_Inventory_ID, Get_TrxName());
if (inventory.Get_ID() == 0)
{
throw new SystemException("Not found: M_Inventory_ID=" + _m_Inventory_ID);
}
// Multiple Lines for one item
//jz simple the SQL so that Derby also like it. To avoid testing Oracle by now, leave no change for Oracle
String sql = null;
if (DataBase.DB.IsOracle())
{
sql = "UPDATE M_InventoryLine SET IsActive='N' "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID
+ " AND (M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID) IN "
+ "(SELECT M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID "
+ "FROM M_InventoryLine "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID
+ " GROUP BY M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID "
+ "HAVING COUNT(*) > 1)";
}
else
{
sql = "UPDATE M_InventoryLine SET IsActive='N' "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID
+ " AND EXISTS "
+ "(SELECT COUNT(*) "
+ "FROM M_InventoryLine "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID
+ " GROUP BY M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID "
+ "HAVING COUNT(*) > 1)";
}
int multiple = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
log.Info("Multiple=" + multiple);
int delMA = MInventoryLineMA.DeleteInventoryMA(_m_Inventory_ID, Get_TrxName());
log.Info("DeletedMA=" + delMA);
// ASI
sql = "UPDATE M_InventoryLine l "
+ "SET (QtyBook,QtyCount) = "
+ "(SELECT QtyOnHand,QtyOnHand FROM M_Storage s "
+ "WHERE s.M_Product_ID=l.M_Product_ID AND s.M_Locator_ID=l.M_Locator_ID"
+ " AND s.M_AttributeSetInstance_ID=l.M_AttributeSetInstance_ID),"
+ " Updated=SysDate,"
+ " UpdatedBy=" + GetAD_User_ID()
//
+ " WHERE M_Inventory_ID=" + _m_Inventory_ID
+ " AND EXISTS (SELECT * FROM M_Storage s "
+ "WHERE s.M_Product_ID=l.M_Product_ID AND s.M_Locator_ID=l.M_Locator_ID"
+ " AND s.M_AttributeSetInstance_ID=l.M_AttributeSetInstance_ID)";
int no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
log.Info("Update with ASI =" + no);
// No ASI
int noMA = UpdateWithMA();
// Set Count to Zero
if (_inventoryCountSetZero)
{
sql = "UPDATE M_InventoryLine l "
+ "SET QtyCount=0 "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID;
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
log.Info("Set Count to Zero =" + no);
}
if (_AdjustinventoryCount)
{
MInventoryLine[] lines = inventory.GetLines(true);
for (int i = 0; i < lines.Length; i++)
{
decimal currentQty = 0;
string query = "", qry = "";
int result = 0;
MInventoryLine iLine = lines[i];
int M_Product_ID = Utility.Util.GetValueOfInt(iLine.GetM_Product_ID());
int M_Locator_ID = Utility.Util.GetValueOfInt(iLine.GetM_Locator_ID());
int M_AttributeSetInstance_ID = Util.GetValueOfInt(iLine.GetM_AttributeSetInstance_ID());
query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate = " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @"
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID;
result = Util.GetValueOfInt(DB.ExecuteScalar(query));
if (result > 0)
{
qry = @"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(inventory.GetMovementDate(), true) + @"
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @")
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @")
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID;
currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry));
}
else
{
query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @"
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID;
result = Util.GetValueOfInt(DB.ExecuteScalar(query));
if (result > 0)
{
qry = @"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(inventory.GetMovementDate(), true) + @"
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @")
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @")
AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID;
currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry));
}
}
iLine.SetQtyBook(currentQty);
iLine.SetOpeningStock(currentQty);
if (iLine.GetAdjustmentType() == "A")
{
iLine.SetDifferenceQty(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetAsOnDateCount()));
}
else if (iLine.GetAdjustmentType() == "D")
{
iLine.SetAsOnDateCount(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty()));
}
iLine.SetQtyCount(Util.GetValueOfDecimal(iLine.GetQtyBook()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty()));
if (!iLine.Save())
{
}
}
//sql = "UPDATE M_InventoryLine "
// + "SET QtyCount = QtyBook - NVL(DifferenceQty,0),OpeningStock = " + currentQty
// + "WHERE M_Inventory_ID=" + _m_Inventory_ID;
//no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
//log.Info("Inventory Adjustment =" + no);
}
inventory.SetIsAdjusted(true);
if (!inventory.Save())
{
}
if (multiple > 0)
{
return("@M_InventoryLine_ID@ - #" + (no + noMA) + " --> @InventoryProductMultiple@");
}
return("@M_InventoryLine_ID@ - #" + (no + noMA));
}
/// <summary>
/// Create/Add to Inventory Line
/// </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 int CreateInventoryLine(int M_Locator_ID, int M_Product_ID,
int M_AttributeSetInstance_ID, Decimal qtyOnHand, Decimal currentQty, int M_AttributeSet_ID)
{
Boolean oneLinePerASI = false;
if (M_AttributeSet_ID != 0)
{
MAttributeSet mas = MAttributeSet.Get(GetCtx(), M_AttributeSet_ID);
oneLinePerASI = mas.IsInstanceAttribute();
}
if (oneLinePerASI)
{
MInventoryLine line = new MInventoryLine(_inventory, M_Locator_ID,
M_Product_ID, M_AttributeSetInstance_ID,
qtyOnHand, qtyOnHand); // book/count
line.SetOpeningStock(currentQty);
if (line.Save())
{
return(1);
}
return(0);
}
if (Env.Signum(qtyOnHand) == 0)
{
M_AttributeSetInstance_ID = 0;
}
if (_line != null &&
_line.GetM_Locator_ID() == M_Locator_ID &&
_line.GetM_Product_ID() == M_Product_ID)
{
if (Env.Signum(qtyOnHand) == 0)
{
return(0);
}
// Same ASI (usually 0)
if (_line.GetM_AttributeSetInstance_ID() == M_AttributeSetInstance_ID)
{
_line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
_line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
_line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
_line.Save();
return(0);
}
// Save Old Line info
else if (_line.GetM_AttributeSetInstance_ID() != 0)
{
MInventoryLineMA ma = new MInventoryLineMA(_line,
_line.GetM_AttributeSetInstance_ID(), _line.GetQtyBook());
if (!ma.Save())
{
;
}
}
_line.SetM_AttributeSetInstance_ID(0);
_line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
_line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
_line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
_line.Save();
//
MInventoryLineMA ma1 = new MInventoryLineMA(_line, M_AttributeSetInstance_ID, qtyOnHand);
if (!ma1.Save())
{
;
}
return(0);
}
// new line
_line = new MInventoryLine(_inventory, M_Locator_ID, M_Product_ID,
M_AttributeSetInstance_ID, qtyOnHand, qtyOnHand); // book/count
_line.SetOpeningStock(currentQty);
if (_line.Save())
{
return(1);
}
return(0);
}
/// <summary>
/// Create/Add to Inventory Line
/// </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 int CreateInventoryLine(int M_Locator_ID, int M_Product_ID,
int M_AttributeSetInstance_ID, Decimal qtyOnHand, Decimal currentQty, int M_AttributeSet_ID)
{
Boolean oneLinePerASI = false;
if (M_AttributeSet_ID != 0)
{
MAttributeSet mas = MAttributeSet.Get(GetCtx(), M_AttributeSet_ID);
oneLinePerASI = mas.IsInstanceAttribute();
}
if (oneLinePerASI)
{
MInventoryLine line = new MInventoryLine(_inventory, M_Locator_ID,
M_Product_ID, M_AttributeSetInstance_ID,
qtyOnHand, qtyOnHand); // book/count
line.SetOpeningStock(currentQty);
line.SetAsOnDateCount(line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (line.Get_ColumnIndex("QtyEntered") > 0)
{
line.Set_Value("QtyEntered", line.GetQtyCount());
}
if (line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (line.Get_ColumnIndex("IsFromProcess") >= 0)
{
line.SetIsFromProcess(true);
}
if (line.Save())
{
return(1);
}
return(0);
}
if (Env.Signum(qtyOnHand) == 0)
{
M_AttributeSetInstance_ID = 0;
}
if (_line != null &&
_line.GetM_Locator_ID() == M_Locator_ID &&
_line.GetM_Product_ID() == M_Product_ID)
{
if (Env.Signum(qtyOnHand) == 0)
{
return(0);
}
// Same ASI (usually 0)
if (_line.GetM_AttributeSetInstance_ID() == M_AttributeSetInstance_ID)
{
_line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
_line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
_line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
_line.SetAsOnDateCount(_line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.Set_Value("QtyEntered", _line.GetQtyCount());
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
_line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
{
_line.SetIsFromProcess(true);
}
_line.Save();
return(0);
}
// Save Old Line info
else if (_line.GetM_AttributeSetInstance_ID() != 0)
{
MInventoryLineMA ma = new MInventoryLineMA(_line,
_line.GetM_AttributeSetInstance_ID(), _line.GetQtyBook());
if (!ma.Save())
{
;
}
}
_line.SetM_AttributeSetInstance_ID(0);
_line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
_line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
_line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
_line.SetAsOnDateCount(_line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.Set_Value("QtyEntered", _line.GetQtyCount());
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
_line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
{
_line.SetIsFromProcess(true);
}
_line.Save();
//
MInventoryLineMA ma1 = new MInventoryLineMA(_line, M_AttributeSetInstance_ID, qtyOnHand);
if (!ma1.Save())
{
;
}
return(0);
}
// new line
_line = new MInventoryLine(_inventory, M_Locator_ID, M_Product_ID,
M_AttributeSetInstance_ID, qtyOnHand, qtyOnHand); // book/count
_line.SetOpeningStock(currentQty);
_line.SetAsOnDateCount(_line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.Set_Value("QtyEntered", _line.GetQtyCount());
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
_line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
{
_line.SetIsFromProcess(true);
}
if (_line.Save())
{
return(1);
}
return(0);
}
