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
}
} // 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