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>
/// 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>
/// 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>
/// 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>
/// Load Invoice Line
/// </summary>
/// <param name="inventory"></param>
/// <returns>DocLine Array</returns>
private DocLine[] LoadLines(MInventory inventory)
{
List <DocLine> list = new List <DocLine>();
MInventoryLine[] lines = inventory.GetLines(false);
for (int i = 0; i < lines.Length; i++)
{
MInventoryLine line = lines[i];
// nothing to post
if (line.GetQtyBook().CompareTo(line.GetQtyCount()) == 0 &&
Env.Signum(line.GetQtyInternalUse()) == 0)
{
continue;
}
//
DocLine docLine = new DocLine(line, this);
Decimal Qty = line.GetQtyInternalUse();
if (Env.Signum(Qty) != 0)
{
Qty = Decimal.Negate(Qty); // Internal Use entered positive
}
else
{
Decimal QtyBook = line.GetQtyBook();
Decimal QtyCount = line.GetQtyCount();
Qty = Decimal.Subtract(QtyCount, QtyBook);
}
docLine.SetQty(Qty, false); // -5 => -5
//
log.Fine(docLine.ToString());
list.Add(docLine);
}
// Return Array
DocLine[] dls = new DocLine[list.Count];
dls = list.ToArray();
return(dls);
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx());
log.Info("M_Inventory_ID=" + _m_Inventory_ID);
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 "
+ (isContainerApplicable ? " , M_ProductContainer_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 "
+ (isContainerApplicable ? " , M_ProductContainer_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())
// {
// }
// }
// Work done by Bharat on 26/12/2016 for optimization
if (isContainerApplicable)
{
sql = @"SELECT m.M_InventoryLine_ID, m.M_Locator_ID, m.M_Product_ID, m.M_AttributeSetInstance_ID, m.AdjustmentType, m.AsOnDateCount, m.DifferenceQty,
nvl(mt.CurrentQty, 0) as CurrentQty FROM M_InventoryLine m LEFT JOIN (SELECT DISTINCT t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_ProductContainer_ID,
FIRST_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID, NVL(t.M_ProductContainer_ID, 0) ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM M_Transaction t
INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) +
" AND t.AD_Client_ID = " + inventory.GetAD_Client_ID() + @") mt ON m.M_Product_ID = mt.M_Product_ID AND nvl(m.M_AttributeSetInstance_ID, 0) = nvl(mt.M_AttributeSetInstance_ID, 0)
AND m.M_Locator_ID = mt.M_Locator_ID AND nvl(m.M_ProductContainer_ID, 0) = nvl(mt.M_ProductContainer_ID, 0)
WHERE m.M_Inventory_ID = " + _m_Inventory_ID + " ORDER BY m.Line";
}
else
{
sql = @"SELECT m.M_InventoryLine_ID, m.M_Locator_ID, m.M_Product_ID, m.M_AttributeSetInstance_ID, m.AdjustmentType, m.AsOnDateCount, m.DifferenceQty,
nvl(mt.CurrentQty, 0) as CurrentQty FROM M_InventoryLine m LEFT JOIN (SELECT DISTINCT t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID,
FIRST_VALUE(t.CurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM M_Transaction t
INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) +
" AND t.AD_Client_ID = " + inventory.GetAD_Client_ID() + @") mt ON m.M_Product_ID = mt.M_Product_ID AND nvl(m.M_AttributeSetInstance_ID, 0) = nvl(mt.M_AttributeSetInstance_ID, 0)
AND m.M_Locator_ID = mt.M_Locator_ID WHERE m.M_Inventory_ID = " + _m_Inventory_ID + " ORDER BY m.Line";
}
int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_InventoryLine_ID) FROM ( " + sql + " ) t", null, null));
int pageSize = 500;
int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1);
int count = 0;
DataSet ds = null;
StringBuilder updateSql = new StringBuilder();
try
{
if (totalRec > 0)
{
log.Info(" =====> Physical Inventory update process Started at " + DateTime.Now.ToString());
if (!_SkipBL)
{
for (int pageNo = 1; pageNo <= TotalPage; pageNo++)
{
ds = DB.GetDatabase().ExecuteDatasetPaging(sql, pageNo, pageSize, 0);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
decimal currentQty = 0;
int line_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]);
currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][7]);
MInventoryLine iLine = new MInventoryLine(GetCtx(), line_ID, Get_TrxName());
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())
{
}
else
{
count++;
}
}
ds.Dispose();
log.Info(" =====> records updated at " + DateTime.Now.ToString() + " are = " + count + " <===== ");
}
}
}
else
{
for (int pageNo = 1; pageNo <= TotalPage; pageNo++)
{
//updateSql.Clear();
ds = DB.GetDatabase().ExecuteDatasetPaging(sql, pageNo, pageSize, 0);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
//updateSql.Append("BEGIN ");
//for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
//{
// int line_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]);
// int locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]);
// int product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]);
// string AdjustType = Util.GetValueOfString(ds.Tables[0].Rows[j][4]);
// decimal AsonDateCount = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][5]);
// decimal DiffQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][6]);
// decimal currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][7]);
// string updateQry = UpdateInventoryLine(line_ID, product_ID, locator_ID, currentQty, AdjustType, AsonDateCount, DiffQty);
// if (updateQry != "")
// {
// updateSql.Append(updateQry);
// }
//}
//ds.Dispose();
//updateSql.Append(" END;");
//int cnt = DB.ExecuteQuery(updateSql.ToString(), null, Get_Trx());
//log.Info(" =====> records updated at " + DateTime.Now.ToString() + " are = " + count + " <===== ");
string updateQry = DBFunctionCollection.UpdateInventoryLine(GetCtx(), ds, Get_Trx());
ds.Dispose();
int cnt = DB.ExecuteQuery(updateQry, null, Get_Trx());
}
}
}
log.Info(" =====> Physical Inventory update process end at " + DateTime.Now.ToString());
}
}
catch (Exception e)
{
if (ds != null)
{
ds.Dispose();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
}
inventory.SetIsAdjusted(true);
if (!inventory.Save())
{
}
if (multiple > 0)
{
return("@M_InventoryLine_ID@ - #" + no + " --> @InventoryProductMultiple@");
}
//return "@M_InventoryLine_ID@ - #" + (no + noMA);
return("Physical Inventory Updated");
}
public bool SetProductQty1(int recordID, string keyColName, List <string> product, List <string> attribute, List <string> qty, List <string> oline_ID, List <string> locID, int LocToID, List <string> AssetID, int lineID, VAdvantage.Utility.Ctx ctx)
{
if (keyColName.ToUpper().Trim() == "M_MOVEMENT_ID")
{
MMovement inv = new MMovement(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MMovementLine invline = new MMovementLine(ctx, lineID, null);
invline.SetAD_Client_ID(inv.GetAD_Client_ID());
invline.SetAD_Org_ID(inv.GetAD_Org_ID());
invline.SetM_Locator_ID(Util.GetValueOfInt(locID[i]));
invline.SetM_LocatorTo_ID(LocToID);
invline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
invline.SetMovementQty(Util.GetValueOfDecimal(qty[i]));
invline.SetM_Movement_ID(recordID);
if (Util.GetValueOfInt(AssetID[i]) > 0)
{
invline.SetA_Asset_ID(Util.GetValueOfInt(AssetID[i]));
}
if (Util.GetValueOfInt(oline_ID[i]) > 0)
{
invline.SetM_RequisitionLine_ID(Util.GetValueOfInt(oline_ID[i]));
}
if (Util.GetValueOfInt(attribute[i]) != 0)
{
invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!invline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "M_INVENTORY_ID")
{
MInventory inv = new MInventory(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MInventoryLine invline = new MInventoryLine(ctx, lineID, null);
invline.SetAD_Client_ID(inv.GetAD_Client_ID());
invline.SetAD_Org_ID(inv.GetAD_Org_ID());
invline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
invline.SetQtyInternalUse(Util.GetValueOfDecimal(qty[i]));
invline.SetC_Charge_ID(Util.GetValueOfInt(AssetID[i]));
invline.SetM_Inventory_ID(recordID);
invline.SetIsInternalUse(true);
invline.SetM_RequisitionLine_ID(Util.GetValueOfInt(oline_ID[i]));
if (Util.GetValueOfInt(attribute[i]) != 0)
{
invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
else
{
invline.SetM_AttributeSetInstance_ID(0);
}
if (!invline.Save())
{
}
}
}
return(true);
}
public bool SetProductQty(int recordID, string keyColName, List <string> product, List <string> attribute, List <string> qty, List <string> qtybook, List <string> oline_ID, int ordID, List <string> locID, int lineID, VAdvantage.Utility.Ctx ctx)
{
if (keyColName.ToUpper().Trim() == "C_ORDER_ID")
{
MOrder ord = new MOrder(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MOrderLine oline = new MOrderLine(ctx, lineID, null);
oline.SetAD_Client_ID(ord.GetAD_Client_ID());
oline.SetAD_Org_ID(ord.GetAD_Org_ID());
oline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
oline.SetQty(Util.GetValueOfDecimal(qty[i]));
oline.SetC_Order_ID(recordID);
if (Util.GetValueOfInt(attribute[i]) != 0)
{
oline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!ord.IsSOTrx())
{
MProduct pro = new MProduct(ctx, oline.GetM_Product_ID(), null);
String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + oline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + ord.GetC_BPartner_ID();
int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom));
if (pro.GetC_UOM_ID() != 0)
{
if (pro.GetC_UOM_ID() != uom && uom != 0)
{
decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + oline.GetM_Product_ID() + " AND IsActive='Y'"));
if (Res > 0)
{
oline.SetQtyEntered(oline.GetQtyEntered() * Res);
//OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty);
}
else
{
decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'"));
if (res > 0)
{
oline.SetQtyEntered(oline.GetQtyEntered() * res);
//OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty);
}
}
oline.SetC_UOM_ID(uom);
}
else
{
oline.SetC_UOM_ID(pro.GetC_UOM_ID());
}
}
}
if (!oline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "C_INVOICE_ID")
{
MInvoice inv = new MInvoice(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MInvoiceLine invline = new MInvoiceLine(ctx, lineID, null);
invline.SetAD_Client_ID(inv.GetAD_Client_ID());
invline.SetAD_Org_ID(inv.GetAD_Org_ID());
invline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
invline.SetQty(Util.GetValueOfDecimal(qty[i]));
invline.SetC_Invoice_ID(recordID);
if (Util.GetValueOfInt(attribute[i]) != 0)
{
invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!inv.IsSOTrx())
{
MProduct pro = new MProduct(ctx, invline.GetM_Product_ID(), null);
String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + invline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + inv.GetC_BPartner_ID();
int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom));
if (pro.GetC_UOM_ID() != 0)
{
if (pro.GetC_UOM_ID() != uom && uom != 0)
{
decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + invline.GetM_Product_ID() + " AND IsActive='Y'"));
if (Res > 0)
{
invline.SetQtyEntered(invline.GetQtyEntered() * Res);
//OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty);
}
else
{
decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'"));
if (res > 0)
{
invline.SetQtyEntered(invline.GetQtyEntered() * res);
//OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty);
}
}
invline.SetC_UOM_ID(uom);
}
else
{
invline.SetC_UOM_ID(pro.GetC_UOM_ID());
}
}
}
if (!invline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "M_INOUT_ID")
{
MInOut inv = new MInOut(ctx, recordID, null);
if (ordID > 0)
{
inv.SetC_Order_ID(ordID);
}
if (inv.Save())
{
for (int i = 0; i < product.Count; i++)
{
MInOutLine ioline = new MInOutLine(ctx, lineID, null);
ioline.SetAD_Client_ID(inv.GetAD_Client_ID());
ioline.SetAD_Org_ID(inv.GetAD_Org_ID());
ioline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
ioline.SetQty(Util.GetValueOfDecimal(qty[i]));
ioline.SetM_InOut_ID(recordID);
ioline.SetC_OrderLine_ID(Util.GetValueOfInt(oline_ID[i]));
ioline.SetM_Locator_ID(Util.GetValueOfInt(locID[i]));
if (Util.GetValueOfInt(attribute[i]) != 0)
{
ioline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!inv.IsSOTrx())
{
MProduct pro = new MProduct(ctx, ioline.GetM_Product_ID(), null);
String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + ioline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + inv.GetC_BPartner_ID();
int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom));
if (pro.GetC_UOM_ID() != 0)
{
if (pro.GetC_UOM_ID() != uom && uom != 0)
{
decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + ioline.GetM_Product_ID() + " AND IsActive='Y'"));
if (Res > 0)
{
ioline.SetQtyEntered(ioline.GetQtyEntered() * Res);
//OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty);
}
else
{
decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'"));
if (res > 0)
{
ioline.SetQtyEntered(ioline.GetQtyEntered() * res);
//OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty);
}
}
ioline.SetC_UOM_ID(uom);
}
else
{
ioline.SetC_UOM_ID(pro.GetC_UOM_ID());
}
}
}
if (!ioline.Save())
{
}
}
}
}
else if (keyColName.ToUpper().Trim() == "M_PACKAGE_ID")
{
MPackage pkg = new MPackage(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MPackageLine mline = new MPackageLine(ctx, lineID, null);
mline.SetAD_Client_ID(pkg.GetAD_Client_ID());
mline.SetAD_Org_ID(pkg.GetAD_Org_ID());
mline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
mline.SetQty(Util.GetValueOfDecimal(qty[i]));
if (Util.GetValueOfInt(oline_ID[i]) > 0)
{
mline.SetM_MovementLine_ID(Util.GetValueOfInt(oline_ID[i]));
MMovementLine mov = new MMovementLine(ctx, Util.GetValueOfInt(oline_ID[i]), null);
mline.SetDTD001_TotalQty(mov.GetMovementQty());
}
if (Util.GetValueOfInt(attribute[i]) != 0)
{
mline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
mline.SetM_Package_ID(recordID);
if (!mline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "M_INVENTORY_ID")
{
MInventory inv = new MInventory(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MInventoryLine invline = new MInventoryLine(ctx, lineID, null);
invline.SetAD_Client_ID(inv.GetAD_Client_ID());
invline.SetAD_Org_ID(inv.GetAD_Org_ID());
invline.SetM_Locator_ID(Util.GetValueOfInt(locID[i]));
invline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
invline.SetQtyCount(Util.GetValueOfDecimal(qty[i]));
invline.SetQtyBook(Util.GetValueOfDecimal(qtybook[i]));
invline.SetAsOnDateCount(Util.GetValueOfDecimal(qty[i]));
invline.SetOpeningStock(Util.GetValueOfDecimal(qtybook[i]));
invline.SetM_Inventory_ID(recordID);
if (Util.GetValueOfInt(attribute[i]) != 0)
{
invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
else
{
invline.SetM_AttributeSetInstance_ID(0);
}
if (!invline.Save())
{
}
}
}
return(true);
}
/// <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);
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Inventory_ID=" + _m_Inventory_ID
+ ", M_Locator_ID=" + _m_Locator_ID + ", LocatorValue=" + _locatorValue
+ ", ProductValue=" + _productValue
+ ", M_Product_Category_ID=" + _m_Product_Category_ID
+ ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld);
_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);
}
if (_inventory.IsProcessed())
{
throw new SystemException("@M_Inventory_ID@ @Processed@");
}
//
String sqlQry = "";
StringBuilder sql = null;
int count = 0;
if (_deleteOld)
{
sqlQry = "DELETE FROM M_InventoryLine WHERE Processed='N' "
+ "AND M_Inventory_ID=" + _m_Inventory_ID;
int no = DataBase.DB.ExecuteQuery(sqlQry, null, Get_TrxName());
log.Fine("doIt - Deleted #" + no);
}
// Create Null Storage records
//if (_qtyRange != null && _qtyRange.Equals("="))
//{
// sqlQry = "INSERT INTO M_Storage "
// + "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
// + " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,"
// + " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) "
// + "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0,"
// + " l.M_Locator_ID, p.M_Product_ID, 0,"
// + " 0,0,0,null "
// + "FROM M_Locator l"
// + " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) "
// + "WHERE l.M_Warehouse_ID=" + _inventory.GetM_Warehouse_ID();
// if (_m_Locator_ID != 0)
// sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID;
// sqlQry += " AND l.IsDefault='Y'"
// + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"
// + " AND NOT EXISTS (SELECT * FROM M_Storage s"
// + " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) "
// + "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID"
// + " AND s.M_Product_ID=p.M_Product_ID)";
// int no = DataBase.DB.ExecuteQuery(sqlQry, null, Get_TrxName());
// log.Fine("'0' Inserted #" + no);
//}
//string sqlstock = "SELECT Count(*) FROM user_procedures WHERE object_type='FUNCTION' AND OBJECT_NAME='GETSTOCKOFWAREHOUSE'";
//int countStock = Util.GetValueOfInt(DB.ExecuteScalar(sqlstock, null, null));
//if (countStock > 0)
//{
sql = new StringBuilder(
@"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty FROM
(SELECT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, SUM(t.CurrentQty) keep (dense_rank last
ORDER BY t.MovementDate, t.M_Transaction_ID) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID
WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) +
@" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() +
@" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @" GROUP BY t.M_Product_ID,l.M_Warehouse_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID)
GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID )
SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, s.QtyOnHand, p.M_AttributeSet_ID FROM M_Product p
INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID) INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)
JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0))
WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
//
if (_m_Locator_ID != 0)
{
sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID);
}
//else
// sql.Append(" AND s.m_Locator_Id IN (SELECT M_Locator_ID FROM M_Locator WHERE M_Warehouse_ID=" + _inventory.GetM_Warehouse_ID() + ")");
//
if (_locatorValue != null &&
(_locatorValue.Trim().Length == 0 || _locatorValue.Equals("%")))
{
_locatorValue = null;
}
if (_locatorValue != null)
{
sql.Append(" AND UPPER(l.Value) LIKE '" + _locatorValue.ToUpper() + "'");
}
//
if (_productValue != null &&
(_productValue.Trim().Length == 0 || _productValue.Equals("%")))
{
_productValue = null;
}
if (_productValue != null)
{
sql.Append(" AND UPPER(p.Value) LIKE '" + _productValue.ToUpper() + "'");
}
if (_m_Product_Category_ID != 0)
{
sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID);
}
// Do not overwrite existing records
if (!_deleteOld)
{
sql.Append(" AND NOT EXISTS (SELECT * FROM M_InventoryLine il "
+ "WHERE il.M_Inventory_ID=" + _m_Inventory_ID
+ " AND il.M_Product_ID=s.M_Product_ID"
+ " AND il.M_Locator_ID=s.M_Locator_ID"
+ " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0))");
}
// + " AND il.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID)");
//
//sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
if (_qtyRange == "N")
{
sql.Append(" AND mt.currentqty != 0");
}
/*SI_0631 : System is giving error ORA-00920: invalid relational operator if User does not select any value from Inventory Quantity list.
* Physical Inventory Window -- button -- Create inventory count list*/
else if (!String.IsNullOrEmpty(_qtyRange))
{
sql.Append(" AND mt.currentqty " + _qtyRange + " 0");
}
int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_Product_ID) FROM ( " + sql.ToString() + " )", null, null));
int pageSize = 500;
int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1);
StringBuilder insertSql = new StringBuilder();
DataSet ds = null;
try
{
if (totalRec > 0)
{
log.Info(" =====> Physical Inventory process Started at " + DateTime.Now.ToString());
if (!_SkipBL)
{
for (int pageNo = 1; pageNo <= TotalPage; pageNo++)
{
ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
decimal currentQty = 0;
int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]);
int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]);
//Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]);
int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]);
currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]);
count += CreateInventoryLine(M_Locator_ID, M_Product_ID,
M_AttributeSetInstance_ID, currentQty, currentQty, M_AttributeSet_ID);
}
ds.Dispose();
log.Info(" =====> records inserted at " + DateTime.Now.ToString() + " are = " + count + " <===== ");
}
}
}
else
{
for (int pageNo = 1; pageNo <= TotalPage; pageNo++)
{
insertSql.Clear();
ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
sqlQry = "SELECT COALESCE(MAX(Line),0) AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + _m_Inventory_ID;
int lineNo = DB.GetSQLValue(Get_Trx(), sqlQry);
insertSql.Append("BEGIN ");
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
decimal currentQty = 0;
int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]);
int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]);
//Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]);
int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]);
currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]);
lineNo = lineNo + 10;
string insertQry = InsertInventoryLine(M_Locator_ID, M_Product_ID, lineNo, M_AttributeSetInstance_ID, currentQty, M_AttributeSet_ID);
if (insertQry != "")
{
insertSql.Append(insertQry);
}
}
ds.Dispose();
insertSql.Append(" END;");
int no = DB.ExecuteQuery(insertSql.ToString(), null, Get_Trx());
}
}
}
log.Info(" =====> Physical Inventory process end at " + DateTime.Now.ToString());
}
}
catch (Exception e)
{
if (ds != null)
{
ds.Dispose();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// }
// else
// {
// sql = new StringBuilder(
// "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
// + " s.qtyOnHand, p.M_AttributeSet_ID "
// + "FROM M_Product p"
// + " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)"
// + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) "
// + "WHERE l.M_Warehouse_ID=" + _inventory.GetM_Warehouse_ID()
// + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
// //
// if (_m_Locator_ID != 0)
// sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID);
// //
// if (_locatorValue != null &&
// (_locatorValue.Trim().Length == 0 || _locatorValue.Equals("%")))
// _locatorValue = null;
// if (_locatorValue != null)
// sql.Append(" AND UPPER(l.Value) LIKE '" + _locatorValue.ToUpper() + "'");
// //
// if (_productValue != null &&
// (_productValue.Trim().Length == 0 || _productValue.Equals("%")))
// _productValue = null;
// if (_productValue != null)
// sql.Append(" AND UPPER(p.Value) LIKE '" + _productValue.ToUpper() + "'");
// //
// if (_m_Product_Category_ID != 0)
// sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID);
// // Do not overwrite existing records
// if (!_deleteOld)
// sql.Append(" AND NOT EXISTS (SELECT * FROM M_InventoryLine il "
// + "WHERE il.M_Inventory_ID=" + _m_Inventory_ID
// + " AND il.M_Product_ID=s.M_Product_ID"
// + " AND il.M_Locator_ID=s.M_Locator_ID"
// + " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0))");
// sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
// //
// IDataReader idr = null;
// try
// {
// idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
// while (idr.Read())
// {
// decimal currentQty = 0;
// string query = "", qry = "";
// int result = 0;
// int M_Product_ID = Utility.Util.GetValueOfInt(idr[0]);
// int M_Locator_ID = Utility.Util.GetValueOfInt(idr[1]);
// int M_AttributeSetInstance_ID = Utility.Util.GetValueOfInt(idr[2]);
// Decimal qtyOnHand = Utility.Util.GetValueOfDecimal(idr[3]);
// int M_AttributeSet_ID = Utility.Util.GetValueOfInt(idr[4]);
// 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));
// }
// }
// int compare = currentQty.CompareTo(Env.ZERO);
// if (_qtyRange == null
// || (_qtyRange.Equals(">") && compare > 0)
// || (_qtyRange.Equals("<") && compare < 0)
// || (_qtyRange.Equals("=") && compare == 0)
// || (_qtyRange.Equals("N") && compare != 0))
// {
// count += CreateInventoryLine(M_Locator_ID, M_Product_ID,
// M_AttributeSetInstance_ID, currentQty, currentQty, M_AttributeSet_ID);
// }
// }
// idr.Close();
// }
// catch (Exception e)
// {
// if (idr != null)
// {
// idr.Close();
// }
// log.Log(Level.SEVERE, sql.ToString(), e);
// }
// }
// Set Count to Zero
if (_inventoryCountSetZero)
{
String sql1 = "";
MInventoryLine inv = new MInventoryLine(GetCtx(), 0, null);
if (inv.Get_ColumnIndex("IsFromProcess") >= 0)
{
sql1 = "UPDATE M_InventoryLine l "
+ "SET QtyCount=0,AsOnDateCount=0 "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND IsFromProcess = 'Y'";
}
else
{
sql1 = "UPDATE M_InventoryLine l "
+ "SET QtyCount=0,AsOnDateCount=0 "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID;
}
int no = DataBase.DB.ExecuteQuery(sql1, null, Get_TrxName());
log.Info("Set Cont to Zero=" + no);
}
//
//return "@M_InventoryLine_ID@ - #" + count;
return("Physical Inventory Created");
}
protected override string DoIt()
{
sql = @"SELECT M_Product_ID , M_Locator_ID , M_AttributeSetInstance_ID , M_Transaction_ID , M_InventoryLine_ID ,
CurrentQty , MovementQty , MovementType , movementdate , TO_Char(Created, 'DD-MON-YY HH24:MI:SS')
FROM M_Transaction WHERE IsActive= 'Y' ";
//if (orgId != null && !string.IsNullOrEmpty(orgId))
//{
// sql += " AND AD_Org_ID IN ( " + orgId + " )";
//}
if (productId != null && !string.IsNullOrEmpty(productId))
{
sql += " AND M_Product_ID IN ( " + productId + " )";
}
sql += " ORDER BY M_Product_ID , M_Locator_ID , M_AttributeSetInstance_ID , movementdate , M_Transaction_ID ASC ";
dsTransaction = new DataSet();
try
{
dsTransaction = DB.ExecuteDataset(sql, null, Get_Trx());
if (dsTransaction != null)
{
if (dsTransaction.Tables.Count > 0)
{
if (dsTransaction.Tables[0].Rows.Count > 0)
{
int i = 0;
for (i = 0; i < dsTransaction.Tables[0].Rows.Count; i++)
{
if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]) &&
Util.GetValueOfString(dsTransaction.Tables[0].Rows[i]["MovementType"]) == "I+" &&
Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_InventoryLine_ID"]) > 0)
{
//update Quantity Book at inventory line
inventoryLine = new MInventoryLine(GetCtx(), Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_InventoryLine_ID"]), Get_Trx());
inventory = new MInventory(GetCtx(), Util.GetValueOfInt(inventoryLine.GetM_Inventory_ID()), null);
if (!inventory.IsInternalUse())
{
inventoryLine.SetQtyBook(_currentQty);
inventoryLine.SetOpeningStock(_currentQty);
inventoryLine.SetDifferenceQty(Decimal.Subtract(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"])));
if (!inventoryLine.Save())
{
log.Info("Quantity Book Not Updated at Inventory Line Tab <===> " + Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_InventoryLine_ID"]));
Rollback();
continue;
}
else
{
Commit();
}
// update movement Qty at Transaction for the same record
transaction = new VAdvantage.Model.MTransaction(GetCtx(), Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]), Get_Trx());
// transaction.SetMovementQty(Decimal.Subtract(inventoryLine.GetQtyCount(), _currentQty));
transaction.SetMovementQty(Decimal.Negate(Decimal.Subtract(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]))));
if (!transaction.Save())
{
log.Info("Current Quantity Not Updated at Transaction Tab <===> " + Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]));
}
else
{
Commit();
_currentQty = Util.GetValueOfDecimal(transaction.GetCurrentQty());
continue;
}
}
else
{
transaction = new VAdvantage.Model.MTransaction(GetCtx(), Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]), Get_Trx());
transaction.SetCurrentQty(Decimal.Add(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["MovementQty"])));
if (!transaction.Save())
{
log.Info("Current Quantity Not Updated at Transaction Tab <===> " + Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]));
}
else
{
Commit();
_currentQty = Decimal.Add(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["MovementQty"]));
continue;
}
}
}
else if (Util.GetValueOfString(dsTransaction.Tables[0].Rows[i]["MovementType"]) == "I+" &&
Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_InventoryLine_ID"]) > 0)
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
transaction = new VAdvantage.Model.MTransaction(GetCtx(), Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]), Get_Trx());
transaction.SetCurrentQty(Decimal.Add(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["MovementQty"])));
if (!transaction.Save())
{
log.Info("Current Quantity Not Updated at Transaction Tab <===> " + Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]));
}
else
{
Commit();
log.Info("Current Quantity Updated at Transaction Tab <===> " + Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]));
_currentQty = Decimal.Add(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["MovementQty"]));
}
}
//when Attribute not matched
else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
// when Locator not Matched
else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
//when Product , Locator n Attribute both not matched (means First Record)
else if (_M_Product_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
//when Locator n Attribute both not matched
else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
// when product and Locator not Matched
else if (_M_Product_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
// when product and Attribute not Matched
else if (_M_Product_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
_M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
_M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
{
if (_M_Product_ID > 0)
{
UpdateStorage(_M_Product_ID, _M_Locator_ID, _M_AttributeSetInstance_ID, _currentQty);
}
_currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
_M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
_M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
_M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
continue;
}
}
}
}
}
dsTransaction.Dispose();
}
catch
{
if (dsTransaction != null)
{
dsTransaction.Dispose();
}
return(Msg.GetMsg(GetCtx(), "NotCompleted"));
}
finally
{
if (dsTransaction != null)
{
dsTransaction.Dispose();
}
}
return(Msg.GetMsg(GetCtx(), "SucessfullyCompleted"));
#region
// sql = @"SELECT M_Product_ID , M_Locator_ID , M_AttributeSetInstance_ID , M_Transaction_ID ,
// CurrentQty , MovementQty , MovementType , TO_Char(Created, 'DD-MON-YY HH24:MI:SS')
// FROM M_Transaction WHERE IsActive= 'Y' ";
// //if (orgId != null && !string.IsNullOrEmpty(orgId))
// //{
// // sql += " AND AD_Org_ID IN ( " + orgId + " )";
// //}
// if (productId != null && !string.IsNullOrEmpty(productId))
// {
// sql += " AND M_Product_ID IN ( " + productId + " )";
// }
// sql += " ORDER BY M_Product_ID , M_Locator_ID , M_AttributeSetInstance_ID , Created ASC ";
// dsTransaction = new DataSet();
// try
// {
// dsTransaction = DB.ExecuteDataset(sql, null, null);
// if (dsTransaction != null)
// {
// if (dsTransaction.Tables.Count > 0)
// {
// if (dsTransaction.Tables[0].Rows.Count > 0)
// {
// int i = 0;
// for (i = 0; i < dsTransaction.Tables[0].Rows.Count; i++)
// {
// if (Util.GetValueOfString(dsTransaction.Tables[0].Rows[i]["MovementType"]) == "I+")
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// transaction = new VAdvantage.Model.MTransaction(GetCtx(), Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]), null);
// transaction.SetCurrentQty(Decimal.Add(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["MovementQty"])));
// if (!transaction.Save())
// {
// log.Info("Current Quantity Not Updated at Transaction Tab <===> " + Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Transaction_ID"]));
// }
// else
// {
// Commit();
// _currentQty = Decimal.Add(_currentQty, Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["MovementQty"]));
// }
// }
// //when Attribute not matched
// else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// // when Locator not Matched
// else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// //when Product , Locator n Attribute both not matched (means First Record)
// else if (_M_Product_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// //when Locator n Attribute both not matched
// else if (_M_Product_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// // when product and Locator not Matched
// else if (_M_Product_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// // when product and Attribute not Matched
// else if (_M_Product_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]) &&
// _M_Locator_ID == Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]) &&
// _M_AttributeSetInstance_ID != Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]))
// {
// _currentQty = Util.GetValueOfDecimal(dsTransaction.Tables[0].Rows[i]["CurrentQty"]);
// _M_Product_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Product_ID"]);
// _M_Locator_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_Locator_ID"]);
// _M_AttributeSetInstance_ID = Util.GetValueOfInt(dsTransaction.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]);
// continue;
// }
// }
// }
// }
// }
// dsTransaction.Dispose();
// }
// catch
// {
// if (dsTransaction != null)
// {
// dsTransaction.Dispose();
// }
// return Msg.GetMsg(GetCtx(), "NotCompleted");
// }
// finally
// {
// if (dsTransaction != null)
// {
// dsTransaction.Dispose();
// }
// }
// return Msg.GetMsg(GetCtx(), "SucessfullyCompleted");
#endregion
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
// is used to check Container applicable into system
isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx());
log.Info("M_Inventory_ID=" + _m_Inventory_ID
+ ", M_Locator_ID=" + _m_Locator_ID + ", LocatorValue=" + _locatorValue
+ ", ProductValue=" + _productValue
+ ", M_Product_Category_ID=" + _m_Product_Category_ID
+ ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld);
_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);
}
if (_inventory.IsProcessed())
{
throw new SystemException("@M_Inventory_ID@ @Processed@");
}
//
String sqlQry = "";
StringBuilder sql = null;
int count = 0;
if (_deleteOld)
{
sqlQry = "DELETE FROM M_InventoryLine WHERE Processed='N' "
+ "AND M_Inventory_ID=" + _m_Inventory_ID;
int no = DataBase.DB.ExecuteQuery(sqlQry, null, Get_TrxName());
log.Fine("doIt - Deleted #" + no);
}
if (!isContainerApplicable)
{
sql = new StringBuilder(
@"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty FROM
(SELECT DISTINCT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, FIRST_VALUE(t.CurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID
ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID
WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) +
@" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() +
@" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @") t GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID )
SELECT DISTINCT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, s.QtyOnHand, p.M_AttributeSet_ID FROM M_Product p
INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID) INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)
JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0))
WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
else
{
sql = new StringBuilder(@"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty, M_ProductContainer_ID
FROM (SELECT DISTINCT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, NVL(t.M_ProductContainer_ID , 0) AS M_ProductContainer_ID,
FIRST_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID, NVL(t.M_ProductContainer_ID, 0) ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty
FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID
WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) +
@" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() +
@" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @") t GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, M_ProductContainer_ID )
SELECT DISTINCT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, mt.M_ProductContainer_ID, p.M_AttributeSet_ID FROM M_Product p
INNER JOIN M_ContainerStorage s ON (s.M_Product_ID=p.M_Product_ID) INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)
JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0) AND mt.M_ProductContainer_ID = NVL(s.M_ProductContainer_ID , 0))
WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
//
if (_m_Locator_ID != 0)
{
sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID);
}
if (_locatorValue != null &&
(_locatorValue.Trim().Length == 0 || _locatorValue.Equals("%")))
{
_locatorValue = null;
}
if (_locatorValue != null)
{
sql.Append(" AND UPPER(l.Value) LIKE '" + _locatorValue.ToUpper() + "'");
}
//
if (_productValue != null &&
(_productValue.Trim().Length == 0 || _productValue.Equals("%")))
{
_productValue = null;
}
if (_productValue != null)
{
sql.Append(" AND UPPER(p.Value) LIKE '" + _productValue.ToUpper() + "'");
}
if (_m_Product_Category_ID != 0)
{
sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID);
}
// Do not overwrite existing records
if (!_deleteOld)
{
sql.Append(" AND NOT EXISTS (SELECT * FROM M_InventoryLine il "
+ "WHERE il.M_Inventory_ID=" + _m_Inventory_ID
+ " AND il.M_Product_ID=s.M_Product_ID"
+ " AND il.M_Locator_ID=s.M_Locator_ID"
+ " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0)");
if (!isContainerApplicable)
{
sql.Append(" ) ");
}
else
{
sql.Append(@" AND COALESCE(il.M_ProductContainer_ID,0)=COALESCE(s.M_ProductContainer_ID,0) )");
}
}
//
if (_qtyRange == "N")
{
sql.Append(" AND mt.currentqty != 0");
}
/*SI_0631 : System is giving error ORA-00920: invalid relational operator if User does not select any value from Inventory Quantity list.
* Physical Inventory Window -- button -- Create inventory count list*/
else if (!String.IsNullOrEmpty(_qtyRange))
{
sql.Append(" AND mt.currentqty " + _qtyRange + " 0");
}
int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_Product_ID) FROM ( " + sql.ToString() + " ) t", null, null));
int pageSize = 500;
int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1);
StringBuilder insertSql = new StringBuilder();
DataSet ds = null;
try
{
if (totalRec > 0)
{
log.Info(" =====> Physical Inventory process Started at " + DateTime.Now.ToString());
if (!_SkipBL)
{
for (int pageNo = 1; pageNo <= TotalPage; pageNo++)
{
ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
decimal currentQty = 0;
int container_ID = 0;
int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]);
int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]);
int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]);
if (isContainerApplicable)
{
container_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["M_ProductContainer_ID"]);
}
currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]);
count += CreateInventoryLine(M_Locator_ID, M_Product_ID,
M_AttributeSetInstance_ID, currentQty, currentQty, M_AttributeSet_ID, container_ID);
}
ds.Dispose();
log.Info(" =====> records inserted at " + DateTime.Now.ToString() + " are = " + count + " <===== ");
}
}
}
else
{
for (int pageNo = 1; pageNo <= TotalPage; pageNo++)
{
//insertSql.Clear();
ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
sqlQry = "SELECT COALESCE(MAX(Line),0) AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + _m_Inventory_ID;
int lineNo = DB.GetSQLValue(Get_Trx(), sqlQry);
//insertSql.Append("BEGIN ");
//for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
//{
// decimal currentQty = 0;
// int container_ID = 0;
// int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]);
// int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]);
// int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]);
// int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]);
// if (isContainerApplicable)
// {
// container_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["M_ProductContainer_ID"]);
// }
// currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]);
// lineNo = lineNo + 10;
// string insertQry = InsertInventoryLine(M_Locator_ID, M_Product_ID, lineNo, M_AttributeSetInstance_ID, currentQty, M_AttributeSet_ID, container_ID);
// if (insertQry != "")
// {
// insertSql.Append(insertQry);
// }
//}
//ds.Dispose();
//insertSql.Append(" END;");
string insertQry = DBFunctionCollection.InsertInventoryLine(GetCtx(), _inventory, ds, lineNo, isContainerApplicable, Get_Trx());
ds.Dispose();
int no = DB.ExecuteQuery(insertQry, null, Get_Trx());
}
}
}
log.Info(" =====> Physical Inventory process end at " + DateTime.Now.ToString());
}
}
catch (Exception e)
{
if (ds != null)
{
ds.Dispose();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Set Count to Zero
if (_inventoryCountSetZero)
{
String sql1 = "";
MInventoryLine inv = new MInventoryLine(GetCtx(), 0, null);
if (inv.Get_ColumnIndex("IsFromProcess") >= 0)
{
sql1 = "UPDATE M_InventoryLine l "
+ "SET QtyCount=0,AsOnDateCount=0 "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND IsFromProcess = 'Y'";
}
else
{
sql1 = "UPDATE M_InventoryLine l "
+ "SET QtyCount=0,AsOnDateCount=0 "
+ "WHERE M_Inventory_ID=" + _m_Inventory_ID;
}
int no = DataBase.DB.ExecuteQuery(sql1, null, Get_TrxName());
log.Info("Set Cont to Zero=" + no);
}
return("Physical Inventory Created");
}
} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>Info</returns>
protected override String DoIt()
{
log.Info("M_Locator_ID=" + _M_Locator_ID + ",MovementDate=" + _MovementDate);
//
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID;
// **** Prepare ****
// Delete Old Imported
if (_DeleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_Inventory "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set Client, Org, Location, IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append("),"
+ " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),");
if (_MovementDate != null)
{
sql.Append(" MovementDate = COALESCE (MovementDate,").Append(DataBase.DB.TO_DATE(_MovementDate)).Append("),");
}
sql.Append(" IsActive = COALESCE (IsActive, 'Y'),"
+ " Created = COALESCE (Created, SysDate),"
+ " CreatedBy = COALESCE (CreatedBy, 0),"
+ " Updated = COALESCE (Updated, SysDate),"
+ " UpdatedBy = COALESCE (UpdatedBy, 0),"
+ " I_ErrorMsg = NULL,"
+ " M_Warehouse_ID = NULL," // reset
+ " I_IsImported = 'N' "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Reset=" + no);
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_Inventory o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '"
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Org=" + no);
}
// gwu: bug 1703137
// if Warehouse key provided, get Warehouse ID
sql = new StringBuilder("UPDATE I_Inventory i "
+ "SET M_Warehouse_ID=(SELECT MAX(M_Warehouse_ID) FROM M_Warehouse w"
+ " WHERE i.WarehouseValue=w.Value AND i.AD_Client_ID=w.AD_Client_ID) "
+ "WHERE M_Warehouse_ID IS NULL AND WarehouseValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Warehouse from Value =" + no);
// Location
sql = new StringBuilder("UPDATE I_Inventory i "
+ "SET M_Locator_ID=(SELECT MAX(M_Locator_ID) FROM M_Locator l"
+ " WHERE i.LocatorValue=l.Value AND COALESCE (i.M_Warehouse_ID, l.M_Warehouse_ID)=l.M_Warehouse_ID AND i.AD_Client_ID=l.AD_Client_ID) "
+ "WHERE M_Locator_ID IS NULL AND LocatorValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Locator from Value =" + no);
sql = new StringBuilder("UPDATE I_Inventory i "
+ "SET M_Locator_ID=(SELECT MAX(M_Locator_ID) FROM M_Locator l"
+ " WHERE i.X=l.X AND i.Y=l.Y AND i.Z=l.Z AND COALESCE (i.M_Warehouse_ID, l.M_Warehouse_ID)=l.M_Warehouse_ID AND i.AD_Client_ID=l.AD_Client_ID) "
+ "WHERE M_Locator_ID IS NULL AND X IS NOT NULL AND Y IS NOT NULL AND Z IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Locator from X,Y,Z =" + no);
if (_M_Locator_ID != 0)
{
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET M_Locator_ID = ").Append(_M_Locator_ID).Append(
" WHERE M_Locator_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Locator from Parameter=" + no);
}
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Location, ' "
+ "WHERE M_Locator_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No Location=" + no);
}
// Set M_Warehouse_ID
sql = new StringBuilder("UPDATE I_Inventory i "
+ "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Locator l WHERE i.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE M_Locator_ID IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Warehouse from Locator =" + no);
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Warehouse, ' "
+ "WHERE M_Warehouse_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No Warehouse=" + no);
}
// Product
sql = new StringBuilder("UPDATE I_Inventory i "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND Value IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product from Value=" + no);
sql = new StringBuilder("UPDATE I_Inventory i "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product from UPC=" + no);
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Product, ' "
+ "WHERE M_Product_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No Product=" + no);
}
// No QtyCount
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Qty Count, ' "
+ "WHERE QtyCount IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No QtyCount=" + no);
}
Commit();
/*********************************************************************/
MInventory inventory = null;
int noInsert = 0;
int noInsertLine = 0;
// Go through Inventory Records
sql = new StringBuilder("SELECT * FROM I_Inventory "
+ "WHERE I_IsImported='N'").Append(clientCheck)
.Append(" ORDER BY M_Warehouse_ID, TRUNC(MovementDate,'DD'), I_Inventory_ID");
IDataReader idr = null;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString (), Get_TrxName());
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
//
int x_M_Warehouse_ID = -1;
DateTime?x_MovementDate = null;
while (idr.Read())
{
X_I_Inventory imp = new X_I_Inventory(GetCtx(), idr, Get_TrxName());
DateTime? MovementDate = TimeUtil.GetDay(imp.GetMovementDate());
if (inventory == null ||
imp.GetM_Warehouse_ID() != x_M_Warehouse_ID ||
!MovementDate.Equals(x_MovementDate))
{
inventory = new MInventory(GetCtx(), 0, Get_TrxName());
inventory.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID());
inventory.SetDescription("I " + imp.GetM_Warehouse_ID() + " " + MovementDate);
inventory.SetM_Warehouse_ID(imp.GetM_Warehouse_ID());
inventory.SetMovementDate(MovementDate);
//
if (!inventory.Save())
{
log.Log(Level.SEVERE, "Inventory not saved");
break;
}
x_M_Warehouse_ID = imp.GetM_Warehouse_ID();
x_MovementDate = MovementDate;
noInsert++;
}
// Line
int M_AttributeSetInstance_ID = 0;
if (imp.GetLot() != null || imp.GetSerNo() != null)
{
MProduct product = MProduct.Get(GetCtx(), imp.GetM_Product_ID());
if (product.IsInstanceAttribute())
{
MAttributeSet mas = product.GetAttributeSet();
MAttributeSetInstance masi = new MAttributeSetInstance(GetCtx(), 0, mas.GetM_AttributeSet_ID(), Get_TrxName());
if (mas.IsLot() && imp.GetLot() != null)
{
masi.SetLot(imp.GetLot(), imp.GetM_Product_ID());
}
if (mas.IsSerNo() && imp.GetSerNo() != null)
{
masi.SetSerNo(imp.GetSerNo());
}
masi.SetDescription();
masi.Save();
M_AttributeSetInstance_ID = masi.GetM_AttributeSetInstance_ID();
}
}
MInventoryLine line = new MInventoryLine(inventory,
imp.GetM_Locator_ID(), imp.GetM_Product_ID(), M_AttributeSetInstance_ID,
imp.GetQtyBook(), imp.GetQtyCount());
if (line.Save())
{
imp.SetI_IsImported(X_I_Inventory.I_ISIMPORTED_Yes);
imp.SetM_Inventory_ID(line.GetM_Inventory_ID());
imp.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
imp.SetProcessed(true);
if (imp.Save())
{
noInsertLine++;
}
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_Inventory "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
//
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@M_Inventory_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@M_InventoryLine_ID@: @Inserted@");
return("");
} // doIt
