/// <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");
}
/// <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");
}
/// <summary>
/// Is used to do a reverse entry of "Production Record" into the system
/// </summary>
/// <returns>message successfuly created or not</returns>
protected override string DoIt()
{
if (M_Production_ID > 0)
{
//Copy Production Header
X_M_Production production = new X_M_Production(GetCtx(), M_Production_ID, Get_Trx());
//check production is Reversed or not, if Reversed then not to do anything
if (production.IsReversed())
{
return(Msg.GetMsg(GetCtx(), "AlreadyReversed"));
}
//Get data from Production Plan
dsProductionPlan = DB.ExecuteDataset(@"SELECT AD_CLIENT_ID , AD_ORG_ID , DESCRIPTION , LINE , M_LOCATOR_ID ,
M_PRODUCT_ID , M_PRODUCTIONPLAN_ID , M_PRODUCTION_ID , PROCESSED , PRODUCTIONQTY M_WAREHOUSE_ID FROM M_ProductionPlan
WHERE IsActive = 'Y' AND M_PRODUCTION_ID = " + M_Production_ID, null, Get_Trx());
//get data from production Line
dsProductionLine = DB.ExecuteDataset(@"SELECT AD_CLIENT_ID , AD_ORG_ID , DESCRIPTION , LINE , M_ATTRIBUTESETINSTANCE_ID , M_LOCATOR_ID ,
M_PRODUCT_ID , M_PRODUCTIONLINE_ID, M_PRODUCTIONPLAN_ID , M_PRODUCTION_ID , PROCESSED , MOVEMENTQTY ,
C_UOM_ID , PLANNEDQTY , M_WAREHOUSE_ID FROM M_ProductionLine
WHERE IsActive = 'Y' AND M_PRODUCTION_ID = " + M_Production_ID, null, Get_Trx());
// Create New record of Production Header with Reverse Entry
X_M_Production productionTo = new X_M_Production(production.GetCtx(), 0, production.Get_Trx());
try
{
productionTo.Set_TrxName(production.Get_Trx());
PO.CopyValues(production, productionTo, production.GetAD_Client_ID(), production.GetAD_Org_ID());
productionTo.SetName("{->" + productionTo.GetName() + ")");
if (production.Get_ColumnIndex("DocumentNo") > 0)
{
productionTo.Set_Value("DocumentNo", ("{->" + production.Get_Value("DocumentNo") + ")"));
}
productionTo.SetMovementDate(production.GetMovementDate()); //SI_0662 : not to create reverse record in current date, it should be created with the same date.
productionTo.SetProcessed(false);
if (!productionTo.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production reverse entry");
}
else
{
#region create new record of Production Plan
if (dsProductionPlan != null && dsProductionPlan.Tables.Count > 0 && dsProductionPlan.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsProductionPlan.Tables[0].Rows.Count; i++)
{
//Original Line
fromProdPlan = new X_M_ProductionPlan(GetCtx(), Util.GetValueOfInt(dsProductionPlan.Tables[0].Rows[i]["M_PRODUCTIONPLAN_ID"]), Get_Trx());
// Create New record of Production Plan with Reverse Entry
toProdPlan = new X_M_ProductionPlan(production.GetCtx(), 0, production.Get_Trx());
try
{
toProdPlan.Set_TrxName(production.Get_Trx());
PO.CopyValues(fromProdPlan, toProdPlan, fromProdPlan.GetAD_Client_ID(), fromProdPlan.GetAD_Org_ID());
toProdPlan.SetProductionQty(Decimal.Negate(toProdPlan.GetProductionQty()));
toProdPlan.SetM_Production_ID(productionTo.GetM_Production_ID());
toProdPlan.SetProcessed(false);
if (!toProdPlan.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production Plan reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production Plan reverse entry");
}
else
{
#region check record exist on production line
if (dsProductionLine != null && dsProductionLine.Tables.Count > 0 && dsProductionLine.Tables[0].Rows.Count > 0)
{
//check record exist on production line against production plan
drProductionLine = dsProductionLine.Tables[0].Select("M_ProductionPlan_ID = " + fromProdPlan.GetM_ProductionPlan_ID());
if (drProductionLine.Length > 0)
{
for (int j = 0; j < drProductionLine.Length; j++)
{
//Original Line
fromProdline = new X_M_ProductionLine(GetCtx(), Util.GetValueOfInt(drProductionLine[j]["M_PRODUCTIONLINE_ID"]), Get_Trx());
// Create New record of Production line with Reverse Entry
toProdline = new X_M_ProductionLine(production.GetCtx(), 0, production.Get_Trx());
try
{
toProdline.Set_TrxName(production.Get_Trx());
PO.CopyValues(fromProdline, toProdline, fromProdPlan.GetAD_Client_ID(), fromProdPlan.GetAD_Org_ID());
toProdline.SetMovementQty(Decimal.Negate(toProdline.GetMovementQty()));
toProdline.SetPlannedQty(Decimal.Negate(toProdline.GetPlannedQty()));
toProdline.SetM_Production_ID(productionTo.GetM_Production_ID());
toProdline.SetM_ProductionPlan_ID(toProdPlan.GetM_ProductionPlan_ID());
toProdline.SetM_ProductContainer_ID(fromProdline.GetM_ProductContainer_ID()); // bcz not a copy record
toProdline.SetReversalDoc_ID(fromProdline.GetM_ProductionLine_ID()); //maintain refernce of Orignal record on reversed record
toProdline.SetProcessed(false);
if (!CheckQtyAvailablity(GetCtx(), toProdline.GetM_Warehouse_ID(), toProdline.GetM_Locator_ID(), toProdline.GetM_ProductContainer_ID(), toProdline.GetM_Product_ID(), toProdline.GetM_AttributeSetInstance_ID(), toProdline.GetMovementQty(), Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
if (!string.IsNullOrEmpty(pp.GetName()))
{
throw new Exception("Could not create Production line reverse entry, " + pp.GetName());
}
else
{
throw new Exception("Could not create Production line reverse entry");
}
}
if (!toProdline.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production Line reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production line reverse entry");
}
else
{
// Create New record of Production line Policy (Material Policy) with Reverse Entry
sql.Clear();
sql.Append(@"INSERT INTO M_ProductionLineMA
( AD_CLIENT_ID, AD_ORG_ID , CREATED , CREATEDBY , ISACTIVE , UPDATED , UPDATEDBY ,
M_PRODUCTIONLINE_ID , M_ATTRIBUTESETINSTANCE_ID , MMPOLICYDATE , M_PRODUCTCONTAINER_ID, MOVEMENTQTY )
(SELECT AD_CLIENT_ID, AD_ORG_ID , sysdate , CREATEDBY , ISACTIVE , sysdate , UPDATEDBY ,
" + toProdline.GetM_ProductionLine_ID() + @" , M_ATTRIBUTESETINSTANCE_ID , MMPOLICYDATE , M_PRODUCTCONTAINER_ID, -1 * MOVEMENTQTY
FROM M_ProductionLineMA WHERE M_ProductionLine_ID = " + fromProdline.GetM_ProductionLine_ID() + @" ) ");
int no = DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
_log.Info("No of records saved on Meterial Policy against Production line ID : " + toProdline.GetM_ProductionLine_ID() + " are : " + no);
}
}
catch (Exception ex)
{
_log.Info("Error Occured during Production Reverse " + ex.ToString());
if (dsProductionLine != null)
{
dsProductionLine.Dispose();
}
if (dsProductionPlan != null)
{
dsProductionPlan.Dispose();
}
return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result));
}
}
}
}
#endregion
}
}
catch (Exception ex)
{
_log.Info("Error Occured during Production Reverse " + ex.ToString());
if (dsProductionLine != null)
{
dsProductionLine.Dispose();
}
if (dsProductionPlan != null)
{
dsProductionPlan.Dispose();
}
return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result));
}
}
}
#endregion
result = productionTo.GetName();
}
// To check weather future date records are available in Transaction window
// this check implement after "SetCompletedDocumentNo" function, because this function overwrit movement date
string _processMsg = MTransaction.CheckFutureDateRecord(productionTo.GetMovementDate(),
productionTo.Get_TableName(), productionTo.GetM_Production_ID(), production.Get_Trx());
if (!string.IsNullOrEmpty(_processMsg))
{
production.Get_Trx().Rollback();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. Future Date record Exists - " + _processMsg);
throw new Exception("Could not create Production reverse entry");
}
//set Reversed as True
productionTo.SetIsReversed(true);
productionTo.SetIsCreated(true);
if (!productionTo.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production reverse entry");
}
//Set reversed as true, Reverse Refernce on Orignal Document
production.SetIsReversed(true);
production.SetM_Ref_Production(productionTo.GetM_Production_ID());
if (!production.Save(production.Get_Trx()))
{
production.Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Log(Level.SEVERE, "Could Not create Production reverse entry. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName());
throw new Exception("Could not create Production reverse entry");
}
}
catch (Exception ex)
{
_log.Info("Error Occured during Production Reverse " + ex.ToString());
if (dsProductionLine != null)
{
dsProductionLine.Dispose();
}
if (dsProductionPlan != null)
{
dsProductionPlan.Dispose();
}
return(Msg.GetMsg(GetCtx(), "DocumentNotReversed" + result));
}
}
return(Msg.GetMsg(GetCtx(), "DocumentReversedSuccessfully" + result));
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Movement_ID=" + _m_Movement_ID
+ ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID
+ ", M_Product_ID=" + _m_product_ID
+ ", M_Product_Category_ID=" + _m_Product_Category_ID
+ ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld);
_movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx());
if (_movement.Get_ID() == 0)
{
throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID);
}
if (_movement.IsProcessed())
{
throw new SystemException("@M_Movement_ID@ @Processed@");
}
// is used to check Container applicable into system
isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx());
//
String sqlQry = "";
if (_deleteOld)
{
sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' "
+ "AND M_Movement_ID=" + _m_Movement_ID;
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
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=" + _movement.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 = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("'0' Inserted #" + no);
}
StringBuilder sql = null;
if (!isContainerApplicable)
{
sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " s.qtyOnHand, p.M_AttributeSet_ID, 0 AS M_ProductContainer_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=" + _movement.GetDTD001_MWarehouseSource_ID()
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
else
{
sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " NVL(SUM(s.Qty) , 0) AS qtyOnHand , p.M_AttributeSet_ID, s.M_ProductContainer_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) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_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 (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID))
{
sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") ");
}
//
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_MovementLine il "
+ "WHERE il.M_Movement_ID=" + _m_Movement_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 (!isContainerApplicable)
{
sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
}
else
{
sql.Append(@" GROUP BY s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID, s.M_ProductContainer_ID, s.Qty
ORDER BY s.M_Locator_ID, s.M_Product_ID, s.Qty DESC, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID,s.M_ProductContainer_ID"); // Locator/Product
}
//
int count = 0;
IDataReader idr = null;
DataTable dt = null;
MProduct product = null;
try
{
idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// while (idr.Read())
{
int M_Product_ID = Util.GetValueOfInt(dr[0]);
product = MProduct.Get(GetCtx(), M_Product_ID);
int M_Locator_ID = Util.GetValueOfInt(dr[1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(dr[2]);
Decimal qtyOnHand = Util.GetValueOfDecimal(dr[3]);
//if (qtyOnHand == null) commented by manjot Because Decimal is Never equals to Null
// qtyOnHand = Env.ZERO;
int M_AttributeSet_ID = Util.GetValueOfInt(dr[4]);
//container
int container_Id = Util.GetValueOfInt(dr[5]);
//
int compare = qtyOnHand.CompareTo(Env.ZERO);
if (_qtyRange == null ||
(_qtyRange.Equals(">") && compare > 0) ||
(_qtyRange.Equals("<") && compare < 0) ||
(_qtyRange.Equals("=") && compare == 0) ||
(_qtyRange.Equals("N") && compare != 0))
{
//Save data on Movement Line
_line = new MMovementLine(GetCtx(), 0, Get_Trx());
_line.SetAD_Client_ID(_movement.GetAD_Client_ID());
_line.SetAD_Org_ID(_movement.GetAD_Org_ID());
_line.SetM_Movement_ID(_m_Movement_ID);
_line.SetM_Locator_ID(_m_Locator_ID);
_line.SetM_LocatorTo_ID(_m_LocatorTo_ID);
_line.SetMovementQty(qtyOnHand);
_line.SetProcessed(false);
_line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
_line.SetM_Product_ID(M_Product_ID);
if (isContainerApplicable && _line.Get_ColumnIndex("M_ProductContainer_ID") > 0)
{
_line.SetM_ProductContainer_ID(container_Id);
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0 && product != null)
{
_line.SetC_UOM_ID(product.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.SetQtyEntered(qtyOnHand);
}
if (!_line.Save())
{
return(GetRetrievedError(_line, "Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID));
//log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID);
}
else
{
count = count + 1;
}
}
}
//idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
//
return("@M_MovementLine_ID@ - #" + count);
}
