/// <summary>
/// Get Inventory Line with parameters
/// </summary>
/// <param name="inventory">inventory</param>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">asi</param>
/// <returns>line or null</returns>
public static MInventoryLine Get(MInventory inventory, int M_Locator_ID,
int M_Product_ID, int M_AttributeSetInstance_ID)
{
MInventoryLine retValue = null;
String sql = "SELECT * FROM M_InventoryLine "
+ "WHERE M_Inventory_ID=@invenid AND M_Locator_ID=@locid"
+ " AND M_Product_ID=@prodid AND M_AttributeSetInstance_ID=@asiid";
try
{
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@invenid", inventory.GetM_Inventory_ID());
param[1] = new SqlParameter("@locid", M_Locator_ID);
param[2] = new SqlParameter("@prodid", M_Product_ID);
param[3] = new SqlParameter("@asiid", M_AttributeSetInstance_ID);
DataSet ds = DataBase.DB.ExecuteDataset(sql, param);
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
retValue = new MInventoryLine(inventory.GetCtx(), dr, inventory.Get_TrxName());
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
return(retValue);
}
public ActionResult Create(MInventory pMInventory)
{
try
{
if (this.ModelState.IsValid)
{
var lMessage = this.LInventory.bll_InsertInventory(pMInventory.LNameInventory, Convert.ToInt32(this.Request.Form[EFields.LFieldListObject]), this.Request.Form[EFields.LFieldListStatus]);
if (lMessage == null)
{
return(this.RedirectToAction("Index"));
}
this.ListEmptyInventory(pMInventory);
pMInventory.LMessageException = lMessage;
return(this.View(pMInventory));
}
this.ListEmptyInventory(pMInventory);
return(this.View(pMInventory));
}
catch (Exception e)
{
this.ListEmptyInventory(pMInventory);
pMInventory.LMessageException = e.Message;
return(this.View(pMInventory));
}
}
/// <summary>
/// Get Parent
/// </summary>
/// <returns>parent</returns>
public MInventory GetParent()
{
if (_parent == null)
{
_parent = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_TrxName());
}
return(_parent);
}
/// <summary>
/// 转为描述数据
/// </summary>
/// <param name="directoryInfo">目录信息</param>
/// <param name="IsCalcSub">是否计算子级</param>
/// <returns>键: 清单文件路径, 值: 清单内容</returns>
public IDictionary <string, MInventory> ToMDirectory(DirectoryInfo directoryInfo, bool IsCalcSub = true)
{
if (directoryInfo == null || !directoryInfo.Exists)
{
return(null);
}
const string inventoryJSONName = "_inventory.json";
string inventory_json_path = $"{directoryInfo.FullName}\\{inventoryJSONName}";
inventory_json_path = FilePathExtend.ToAbsolutePath(inventory_json_path);
log.Info("计算JSON清单路径", new Dictionary <string, object>()
{
{ "inventory_json_path", inventory_json_path },
});
var dict = new Dictionary <string, MInventory>();
var items = new List <MItem>();
var sub_files = directoryInfo.GetFiles();
log.Info("添加文件类型项", new Dictionary <string, object>()
{
{ "sub_files.Length", sub_files.Length },
});
foreach (var file in sub_files)
{
if (file.Name == inventoryJSONName)
{
continue;
}
items.Add(ToMItem(file.Name, "File"));
}
var sub_directorys = directoryInfo.GetDirectories();
log.Info("添加目录类型项", new Dictionary <string, object>()
{
{ "directoryInfo.FullName", directoryInfo.FullName },
{ "sub_directorys.Length", sub_directorys.Length },
});
foreach (var dir in sub_directorys)
{
if (IsCalcSub)
{
var sub_dict = ToMDirectory(dir);
dict = dict.Union(sub_dict).ToDictionary(kv => kv.Key, kv => kv.Value);
}
items.Add(ToMItem(dir.Name, "Dir"));
}
var result = new MInventory
{
Name = directoryInfo.Name,
SubMItems = items.ToArray(),
};
dict[inventory_json_path] = result;
return(dict);
}
/// <summary>
/// Load Document Details
/// </summary>
/// <returns>error message or null</returns>
public override String LoadDocumentDetails()
{
SetC_Currency_ID(NO_CURRENCY);
MInventory inventory = (MInventory)GetPO();
SetDateDoc(inventory.GetMovementDate());
SetDateAcct(inventory.GetMovementDate());
// Contained Objects
_lines = LoadLines(inventory);
log.Fine("Lines=" + _lines.Length);
return(null);
}
public static List <MInventory> MListInventory(this List <BoInventory> oBListInventory)
{
List <MInventory> oMListInventory = new List <MInventory>();
oBListInventory.ForEach(x => {
var oMInventory = new MInventory
{
LIdInventory = x.LIdInventory,
LNameInventory = x.LNameInventory,
LCreationDate = x.LCreationDate
};
oMListInventory.Add(oMInventory);
});
return(oMListInventory);
}
private MInventory CurrentInventory(MInventory pMInventory, MInventory pMInventoryOld, int id, string pMessageException)
{
var oBInventory = this.LInventory.bll_GetInventoryById(id);
if (pMInventory != null)
{
pMInventory = oBInventory.MInventoryById();
pMInventory.LNameInventory = pMInventoryOld.LNameInventory;
pMInventory.LStatus.LIdStatus = pMInventoryOld.LStatus.LIdStatus;
pMInventory.LMessageException = pMessageException;
}
pMInventory = oBInventory.MInventoryById();
pMInventory.LNameInventory = pMInventoryOld.LNameInventory;
pMInventory.LStatus.LIdStatus = pMInventoryOld.LStatus.LIdStatus;
pMInventory.LMessageException = pMessageException;
return(pMInventory);
}
/// <summary>
/// GetMInventory
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Dictionary <string, string> GetMInventory(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int M_Inventory_ID;
//Assign parameter value
M_Inventory_ID = Util.GetValueOfInt(paramValue[0].ToString());
MInventory inv = new MInventory(ctx, M_Inventory_ID, null);
DateTime? MovementDate = inv.GetMovementDate();
int AD_Org_ID = inv.GetAD_Org_ID();
Dictionary <string, string> retDic = new Dictionary <string, string>();
retDic["MovementDate"] = MovementDate.ToString();
retDic["AD_Org_ID"] = AD_Org_ID.ToString();
return(retDic);
}
public ActionResult Delete(int id, MInventory pMInventory)
{
try
{
var lMessage = this.LInventory.bll_DeleteInventory(id);
if (lMessage == null)
{
return(this.RedirectToAction("Index"));
}
pMInventory.LMessageException = lMessage;
return(this.View(pMInventory));
}
catch (Exception e)
{
pMInventory.LMessageException = e.Message;
return(this.View(pMInventory));
}
}
/// <summary>
/// After Save
/// </summary>
/// <param name="newRecord">new</param>
/// <param name="success">success</param>
/// <returns>true</returns>
protected override bool AfterSave(bool newRecord, bool success)
{
// Create MA
if (newRecord && success &&
_isManualEntry && GetM_AttributeSetInstance_ID() == 0)
{
CreateMA(true);
}
if (!IsInternalUse())
{
MInventory inv = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
inv.SetIsAdjusted(false);
if (!inv.Save())
{
}
}
return(true);
}
public ActionResult Edit(int id, MInventory pMInventory)
{
try
{
var lMInventory = new MInventory();
if (this.ModelState.IsValid)
{
var lMessage = this.LInventory.bll_UpdateInventory(id, pMInventory.LNameInventory, Convert.ToInt32(this.Request.Form[EFields.LFieldListObject]), this.Request.Form[EFields.LFieldListStatus]);
if (lMessage == null)
{
return(this.RedirectToAction("Index"));
}
return(this.View(this.CurrentInventory(lMInventory, pMInventory, id, lMessage)));
}
return(this.View(this.CurrentInventory(lMInventory, pMInventory, id, EMessages.LMsgEditfieldsemtpy)));
}
catch (Exception e)
{
var lMInventory = new MInventory();
return(this.View(this.CurrentInventory(lMInventory, pMInventory, id, e.Message)));
}
}
/// <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);
}
public static MInventory MInventoryEmpty()
{
var oObject = LiUtilsLib.bll_GetObjectByName(MGlobalVariables.LNameObjectInventory);
var lMInventory = new MInventory
{
LObject = new MObject
{
LIdObject = oObject.LIdObject,
LNameObject = oObject.LNameObject
},
LStatus = new MStatus
{
LDsEstado = null,
LIdStatus = null
},
LListStatus = new List <SelectListItem>(),
LNameInventory = null,
LCreationDate = new DateTime()
};
lMInventory.LListStatus = LiStatus.Bll_getListStatusByIdObject(lMInventory.LObject.LIdObject).MListStatusWithSelect();
return(lMInventory);
}
/// <summary>
/// Detail Constructor.
/// Locator/Product/AttributeSetInstance must be unique
/// </summary>
/// <param name="inventory">parent</param>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">instance</param>
/// <param name="qtyBook">book value</param>
/// <param name="qtyCount">count value</param>
public MInventoryLine(MInventory inventory, int M_Locator_ID, int M_Product_ID,
int M_AttributeSetInstance_ID, Decimal qtyBook, Decimal qtyCount)
: this(inventory.GetCtx(), 0, inventory.Get_TrxName())
{
if (inventory.Get_ID() == 0)
{
throw new ArgumentException("Header not saved");
}
_parent = inventory;
SetM_Inventory_ID(inventory.GetM_Inventory_ID()); // Parent
SetClientOrg(inventory.GetAD_Client_ID(), inventory.GetAD_Org_ID());
SetM_Locator_ID(M_Locator_ID); // FK
SetM_Product_ID(M_Product_ID); // FK
SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
//
// if (qtyBook != null)
SetQtyBook(qtyBook);
if (qtyCount != null && Env.Signum(qtyCount) != 0)
{
SetQtyCount(qtyCount);
}
_isManualEntry = false;
}
public static MInventory MInventoryById(this BoInventory oBInventory)
{
var oMInventory = new MInventory
{
LObject = new MObject
{
LIdObject = oBInventory.LObject.LIdObject,
LNameObject = oBInventory.LObject.LNameObject
},
LStatus = new MStatus
{
LDsEstado = oBInventory.LStatus.LDsEstado,
LIdStatus = oBInventory.LStatus.LIdStatus
},
LNameInventory = oBInventory.LNameInventory,
LIdInventory = oBInventory.LIdInventory,
LCreationDate = oBInventory.LCreationDate,
LListStatus =
LiStatus.Bll_getListStatusByIdObject(oBInventory.LObject.LIdObject).MListAllStatus()
};
return(oMInventory);
}
/// <summary>
/// Set Parent
/// </summary>
/// <param name="parent">parent</param>
public void SetParent(MInventory parent)
{
_parent = parent;
}
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>
/// Before Save
/// </summary>
/// <param name="newRecord">new</param>
/// <returns>true if can be saved</returns>
protected override bool BeforeSave(bool newRecord)
{
Decimal VA024_ProvisionPrice = 0;
MInventory inventory = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
MProduct product = MProduct.Get(GetCtx(), GetM_Product_ID());
if (newRecord && _isManualEntry)
{
// Product requires ASI
if (GetM_AttributeSetInstance_ID() == 0)
{
if (product.GetM_AttributeSet_ID() != 0)
{
MAttributeSet mas = MAttributeSet.Get(GetCtx(), product.GetM_AttributeSet_ID());
//uncomment by Amit on behalf of Mandeep 7-3-2016
//if (mas.IsInstanceAttribute()
// && (mas.IsMandatory() || mas.IsMandatoryAlways()))
if (mas.IsMandatory() || mas.IsMandatoryAlways())
{
log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "M_AttributeSetInstance_ID"));
return(false);
}
}
} // No ASI
} // new or manual
// not to create Internal use Inventory with -ve qty -- but during reversal system will create record with -ve qty
// duing reversal -- ReversalDoc_ID contain refernce o orignal record id
if (IsInternalUse() && Get_ColumnIndex("ReversalDoc_ID") > 0 && GetReversalDoc_ID() == 0 && GetQtyInternalUse() < 0)
{
log.SaveError("", Msg.GetMsg(GetCtx(), "VIS_CantbeNegative"));
return(false);
}
// Set Line No
if (GetLine() == 0)
{
String sql = "SELECT COALESCE(MAX(Line),0)+10 AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + GetM_Inventory_ID();
int ii = DataBase.DB.GetSQLValue(Get_TrxName(), sql);
SetLine(ii);
}
// SI_0644.1 : Enforce UOM Precision - Rounding Quantities
if (newRecord || Is_ValueChanged("QtyInternalUse"))
{
SetQtyInternalUse(GetQtyInternalUse());
}
// change to set Converted Quantity in Internal Use Qty and AsonDateQty and difference qty if there is differnce in UOM of Base Product and UOM Selected on line
if (newRecord || Is_ValueChanged("QtyEntered") || Is_ValueChanged("C_UOM_ID"))
{
Decimal?qty = Util.GetValueOfDecimal(Get_Value("QtyEntered"));
if (product.GetC_UOM_ID() != Util.GetValueOfInt(Get_Value("C_UOM_ID")))
{
qty = MUOMConversion.ConvertProductFrom(GetCtx(), GetM_Product_ID(), Util.GetValueOfInt(Get_Value("C_UOM_ID")), Util.GetValueOfDecimal(Get_Value("QtyEntered")));
if (IsInternalUse())
{
SetQtyInternalUse(qty);
}
else
{
SetAsOnDateCount(qty);
SetDifferenceQty(qty);
}
}
}
// SI_0644 - As on date and difference should be according to the precision of UOM attached.
if (newRecord || Is_ValueChanged("AsOnDateCount"))
{
if (product != null)
{
int precision = product.GetUOMPrecision();
SetAsOnDateCount(Decimal.Round(GetAsOnDateCount(), precision, MidpointRounding.AwayFromZero));
}
}
// SI_0644 - As on date and difference should be according to the precision of UOM attached.
if (newRecord || Is_ValueChanged("DifferenceQty"))
{
if (product != null)
{
int precision = product.GetUOMPrecision();
SetDifferenceQty(Decimal.Round(GetDifferenceQty(), precision, MidpointRounding.AwayFromZero));
}
}
// SI_0682_1 Need to update the reserved qty on requisition line by internal use line save aslo and should work as work in inventory move.
if (Env.IsModuleInstalled("DTD001_") && IsInternalUse())
{
qtyReserved = Util.GetValueOfDecimal(Get_ValueOld("QtyInternalUse"));
}
int M_Warehouse_ID = 0; MWarehouse wh = null;
string qry = "select m_warehouse_id from m_locator where m_locator_id=" + GetM_Locator_ID();
M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_TrxName()));
wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID);
qry = "SELECT QtyOnHand FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID();
if (GetM_AttributeSetInstance_ID() != 0)
{
qry += " AND M_AttributeSetInstance_ID=" + GetM_AttributeSetInstance_ID();
}
OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry, null, Get_TrxName()));
// when record is in completed & closed stage - then no need to check qty availablity in warehouse
if (wh.IsDisallowNegativeInv() == true &&
(!(inventory.GetDocStatus() == "CO" || inventory.GetDocStatus() == "CL" ||
inventory.GetDocStatus() == "RE" || inventory.GetDocStatus() == "VO")))
{
#region DisallowNegativeInv = True
if (!IsInternalUse() && GetDifferenceQty() > 0)
{
if ((OnHandQty - GetDifferenceQty()) < 0)
{
log.SaveError("Info", product.GetName() + ", " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
return(false);
}
}
else if (IsInternalUse())
{
if ((OnHandQty - GetQtyInternalUse()) < 0)
{
log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty);
return(false);
}
}
#endregion
}
// Enforce Qty UOM
if (newRecord || Is_ValueChanged("QtyCount"))
{
SetQtyCount(GetQtyCount());
}
// InternalUse Inventory
if (IsInternalUse() && Env.Signum(GetQtyInternalUse()) == 0)
{
log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "QtyInternalUse"));
return(false);
}
if (Env.Signum(GetQtyInternalUse()) != 0)
{
if (!INVENTORYTYPE_ChargeAccount.Equals(GetInventoryType()))
{
SetInventoryType(INVENTORYTYPE_ChargeAccount);
}
//
if (GetC_Charge_ID() == 0)
{
log.SaveError("Error", Msg.GetMsg(GetCtx(), "InternalUseNeedsCharge"));
return(false);
}
}
else if (INVENTORYTYPE_ChargeAccount.Equals(GetInventoryType()))
{
if (GetC_Charge_ID() == 0)
{
log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "C_Charge_ID"));
return(false);
}
}
else if (GetC_Charge_ID() != 0)
{
SetC_Charge_ID(0);
}
// Set AD_Org to parent if not charge
if (GetC_Charge_ID() == 0)
{
SetAD_Org_ID(GetParent().GetAD_Org_ID());
}
// By Amit for Obsolete Inventory - 25-May-2016
if (Env.IsModuleInstalled("VA024_"))
{
//MInventory inventory = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
//shipment and Return to vendor
if (inventory.IsInternalUse() || (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0))
{
try
{
string qry1 = @"SELECT SUM(o.VA024_UnitPrice) FROM VA024_t_ObsoleteInventory o
WHERE o.IsActive = 'Y' AND o.M_Product_ID = " + GetM_Product_ID() + @" and
( o.M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID() + @" OR o.M_AttributeSetInstance_ID IS NULL )" +
" AND o.AD_Org_ID = " + GetAD_Org_ID();
//+" AND M_Warehouse_ID = " + inventory.GetM_Warehouse_ID();
VA024_ProvisionPrice = Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx()));
if (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0)
{
SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * (GetQtyBook() - GetQtyCount())));
}
else
{
SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * GetQtyInternalUse()));
}
if (!inventory.IsInternalUse() && (GetQtyBook() - GetQtyCount()) > 0)
{
qry1 = @"SELECT (ct.currentcostprice - " + VA024_ProvisionPrice + ") * " + (GetQtyBook() - GetQtyCount());
}
else
{
qry1 = @"SELECT (ct.currentcostprice - " + VA024_ProvisionPrice + ") * " + GetQtyInternalUse();
}
qry1 += @" FROM m_product p INNER JOIN va024_t_obsoleteinventory oi ON p.m_product_id = oi.M_product_ID
INNER JOIN m_product_category pc ON pc.m_product_category_ID = p.m_product_category_ID
INNER JOIN AD_client c ON c.AD_Client_ID = p.Ad_Client_ID INNER JOIN AD_ClientInfo ci ON c.AD_Client_ID = ci.Ad_Client_ID
INNER JOIN m_cost ct ON ( p.M_Product_ID = ct.M_Product_ID AND ci.C_AcctSchema1_ID = ct.C_AcctSchema_ID )
INNER JOIN c_acctschema asch ON (asch.C_AcctSchema_ID = ci.C_AcctSchema1_ID)
INNER JOIN va024_obsoleteinvline oil ON oil.va024_obsoleteinvline_ID = oi.va024_obsoleteinvline_ID ";
qry1 += @" WHERE ct.AD_Org_ID =
CASE WHEN ( pc.costinglevel IS NOT NULL AND pc.costinglevel = 'O') THEN " + GetAD_Org_ID() + @"
WHEN ( pc.costinglevel IS NOT NULL AND (pc.costinglevel = 'C' OR pc.costinglevel = 'B')) THEN 0
WHEN (pc.costinglevel IS NULL AND asch.costinglevel = 'O') THEN " + GetAD_Org_ID() + @"
WHEN ( pc.costinglevel IS NULL AND (asch.costinglevel = 'C' OR asch.costinglevel = 'B')) THEN 0 END
AND ct.m_costelement_id =
CASE WHEN ( pc.costingmethod IS NOT NULL AND pc.costingmethod != 'C') THEN (SELECT MIN(m_costelement_id) FROM m_costelement
WHERE m_costelement.costingmethod =pc.costingmethod AND m_costelement.Ad_Client_ID = oi.ad_client_id )
WHEN ( pc.costingmethod IS NOT NULL AND pc.costingmethod = 'C' ) THEN pc.m_costelement_id
WHEN ( pc.costingmethod IS NULL AND asch.costingmethod != 'C') THEN (SELECT MIN(m_costelement_id) FROM m_costelement
WHERE m_costelement.costingmethod = asch.costingmethod AND m_costelement.Ad_Client_ID = oi.ad_client_id )
WHEN ( pc.costingmethod IS NULL AND asch.costingmethod = 'C') THEN asch.m_costelement_id END
AND NVL(ct.M_Attributesetinstance_ID , 0) =
CASE WHEN ( pc.costinglevel IS NOT NULL AND pc.costinglevel = 'B') THEN " + GetM_AttributeSetInstance_ID() + @"
WHEN ( pc.costinglevel IS NOT NULL AND (pc.costinglevel = 'C' OR pc.costinglevel = 'O')) THEN 0
WHEN ( pc.costinglevel IS NULL AND asch.costinglevel = 'B') THEN " + GetM_AttributeSetInstance_ID() + @"
WHEN ( pc.costinglevel IS NULL AND (asch.costinglevel = 'C' OR asch.costinglevel = 'O')) THEN 0 END
AND p.M_Product_ID = " + GetM_Product_ID();
SetVA024_CostPrice(Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx())));
}
catch { }
}
}
return(true);
}
/// <summary>
/// After Save
/// </summary>
/// <param name="newRecord">new</param>
/// <param name="success">success</param>
/// <returns>true</returns>
protected override bool AfterSave(bool newRecord, bool success)
{
if (!success)
{
return(success);
}
// Create MA
if (newRecord && success &&
_isManualEntry && GetM_AttributeSetInstance_ID() == 0)
{
CreateMA(true);
}
if (!IsInternalUse())
{
MInventory inv = new MInventory(GetCtx(), GetM_Inventory_ID(), Get_Trx());
inv.SetIsAdjusted(false);
if (!inv.Save())
{
}
}
else // SI_0682_1 Need to update the reserved qty on requisition line by internal use line save aslo and should work as work in inventory move.
{
if (Env.IsModuleInstalled("DTD001_"))
{
if (!newRecord && GetM_RequisitionLine_ID() != 0)
{
MRequisitionLine requisition = new MRequisitionLine(GetCtx(), GetM_RequisitionLine_ID(), Get_Trx());
requisition.SetDTD001_ReservedQty(requisition.GetDTD001_ReservedQty() + (GetQtyInternalUse() - qtyReserved));
if (!requisition.Save())
{
return(false);
}
storage = MStorage.Get(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), Get_Trx());
if (storage == null)
{
storage = MStorage.GetCreate(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), Get_Trx());
}
storage.SetQtyReserved(storage.GetQtyReserved() + (GetQtyInternalUse() - qtyReserved));
if (!storage.Save())
{
return(false);
}
}
if (newRecord && GetM_RequisitionLine_ID() != 0 && GetDescription() != "RC")
{
MRequisitionLine requisition = new MRequisitionLine(GetCtx(), GetM_RequisitionLine_ID(), Get_Trx());
requisition.SetDTD001_ReservedQty(requisition.GetDTD001_ReservedQty() + GetQtyInternalUse());
if (!requisition.Save())
{
return(false);
}
storage = MStorage.Get(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), Get_Trx());
if (storage == null)
{
storage = MStorage.GetCreate(GetCtx(), GetM_Locator_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), Get_Trx());
}
storage.SetQtyReserved(storage.GetQtyReserved() + GetQtyInternalUse());
if (!storage.Save())
{
return(false);
}
}
}
}
return(true);
}
/// <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()
{
try
{
_log.Info("Start Imported Data completion for GulfOil : " + System.DateTime.Now);
#region complete Order Record
sql.Clear();
sql.Append("SELECT * FROM C_Order WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL') ORDER BY dateacct");
dsRecord = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
dataRow = dsRecord.Tables[0].Select("IsActive = 'Y' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
ViennaAdvantage.Model.MOrder order = null;
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of C_Order ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
order = new ViennaAdvantage.Model.MOrder(GetCtx(), Util.GetValueOfInt(dataRow[i]["C_Order_ID"]), Get_Trx());
order.CompleteIt();
if (order.GetDocAction() == "CL")
{
order.SetDocStatus("CO");
order.SetDocAction("CL");
if (!order.Save(Get_Trx()))
{
notCompletedRecord.Append(order.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + order.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(order.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Order not completed for this Record ID = " + order.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region complete Invoice Record
sql.Clear();
sql.Append("SELECT * FROM C_Invoice WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL') ORDER BY dateacct");
dsRecord = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
dataRow = dsRecord.Tables[0].Select("IsActive = 'Y' ", "dateacct");
if (dataRow != null && dataRow.Length > 0)
{
ViennaAdvantage.Model.MInvoice invoice = null;
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of C_Invoice ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
invoice = new ViennaAdvantage.Model.MInvoice(GetCtx(), Util.GetValueOfInt(dataRow[i]["C_Invoice_ID"]), Get_Trx());
invoice.CompleteIt();
if (invoice.GetDocAction() == "CL")
{
invoice.SetDocStatus("CO");
invoice.SetDocAction("CL");
if (!invoice.Save(Get_Trx()))
{
notCompletedRecord.Append(invoice.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Invoice Record ID = " + invoice.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(invoice.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Invoice not completed for this Record ID = " + invoice.GetDocumentNo() + " Message- " + invoice.GetProcessMsg());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
sql.Clear();
sql.Append("SELECT * FROM M_InOut WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL' , 'RE' , 'VO') ORDER BY movementdate");
dsInOut = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
sql.Clear();
sql.Append("SELECT * FROM M_Inventory WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL' , 'RE' , 'VO') ORDER BY movementdate");
dsInventory = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
sql.Clear();
sql.Append("SELECT * FROM M_Movement WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL' , 'RE' , 'VO') ORDER BY movementdate");
dsMovement = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
sql.Clear();
sql.Append("SELECT * FROM C_Payment WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL' , 'RE' , 'VO') ORDER BY dateacct");
dsPayment = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
sql.Clear();
sql.Append("SELECT * FROM C_Cash WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL' , 'RE' , 'VO') ORDER BY dateacct");
dsCashJournal = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
sql.Clear();
sql.Append("SELECT * FROM GL_Journal WHERE IsActive = 'Y' AND DocStatus NOT IN ('CO' , 'CL' , 'RE' , 'VO') ORDER BY dateacct");
dsGLJournal = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
// min date record from the transaction window
minDateRecord = SerachMinDate();
int diff = (DateTime.Now - minDateRecord.Value).Days;
for (int days = 0; days <= diff; days++)
{
if (days != 0)
{
minDateRecord = minDateRecord.Value.AddDays(1);
}
try
{
#region Physical Inventory
dataRow = dsInventory.Tables[0].Select("isinternaluse = 'N' AND movementdate = '" + minDateRecord + "'", "M_Inventory_ID");
if (dataRow != null && dataRow.Length > 0)
{
MInventory inventory = null;
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of Physical Inventory ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inventory = new MInventory(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_Inventory_ID"]), Get_Trx());
inventory.CompleteIt();
if (inventory.GetDocAction() == "CL")
{
inventory.SetDocStatus("CO");
inventory.SetDocAction("CL");
if (!inventory.Save(Get_Trx()))
{
notCompletedRecord.Append(inventory.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving Physical Inventory Record ID = " + inventory.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(inventory.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Physical Inventory not completed for this Record ID = " + inventory.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region Internal use inventory
dataRow = dsInventory.Tables[0].Select("isinternaluse = 'Y' AND movementdate = '" + minDateRecord + "'", "M_Inventory_ID");
if (dataRow != null && dataRow.Length > 0)
{
MInventory inventory = null;
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of Internal use inventory ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inventory = new MInventory(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_Inventory_ID"]), Get_Trx());
inventory.CompleteIt();
if (inventory.GetDocAction() == "CL")
{
inventory.SetDocStatus("CO");
inventory.SetDocAction("CL");
if (!inventory.Save(Get_Trx()))
{
notCompletedRecord.Append(inventory.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving Physical Inventory Record ID = " + inventory.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(inventory.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Physical Inventory not completed for this Record ID = " + inventory.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region complete material receipt
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'N' AND IsReturnTrx = 'N' AND DocStatus NOT IN ('CO' , 'CL') AND DateAcct = '" + minDateRecord + "'", "M_InOut_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of material receipt ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new ViennaAdvantage.Model.MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Material Receipt not completed for this Record ID = " + inout.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region complete Movement Record
dataRow = dsMovement.Tables[0].Select("movementdate = '" + minDateRecord + "'", "M_Movement_ID");
if (dataRow != null && dataRow.Length > 0)
{
ViennaAdvantage.Model.MMovement movement = null;
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of M_Movement ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
movement = new ViennaAdvantage.Model.MMovement(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_Movement_ID"]), Get_Trx());
movement.CompleteIt();
if (movement.GetDocAction() == "CL")
{
movement.SetDocStatus("CO");
movement.SetDocAction("CL");
if (!movement.Save(Get_Trx()))
{
notCompletedRecord.Append(movement.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Invoice Record ID = " + movement.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(movement.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Movement not completed for this Record ID = " + movement.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region complete shipment
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'Y' AND IsReturnTrx = 'N' AND DateAcct = '" + minDateRecord + "'", "M_Inout_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of shipment ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new ViennaAdvantage.Model.MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Shipment not completed for this Record ID = " + inout.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region complete Customer Return
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'Y' AND IsReturnTrx = 'Y' AND DateAcct = '" + minDateRecord + "'", "M_Inout_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of Customer Return ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new ViennaAdvantage.Model.MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Customer return not completed for this Record ID = " + inout.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region complete Return to Vendor
dataRow = dsInOut.Tables[0].Select("IsSoTrx = 'N' AND IsReturnTrx = 'Y' AND DateAcct = '" + minDateRecord + "'", "M_InOut_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of Return to Vendor ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
inout = new ViennaAdvantage.Model.MInOut(GetCtx(), Util.GetValueOfInt(dataRow[i]["M_InOut_ID"]), Get_Trx());
inout.CompleteIt();
if (inout.GetDocAction() == "CL")
{
inout.SetDocStatus("CO");
inout.SetDocAction("CL");
if (!inout.Save(Get_Trx()))
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Order Record ID = " + inout.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(inout.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Return to Vendor not completed for this Record ID = " + inout.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region Complete Payment
dataRow = dsPayment.Tables[0].Select("DateAcct = '" + minDateRecord + "'", "C_Payment_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of Payment ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
payment = new MPayment(GetCtx(), Util.GetValueOfInt(dataRow[i]["C_Payment_ID"]), Get_Trx());
payment.CompleteIt();
if (payment.GetDocAction() == "CL")
{
payment.SetDocStatus("CO");
payment.SetDocAction("CL");
if (!payment.Save(Get_Trx()))
{
notCompletedRecord.Append(payment.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Payment Record ID = " + payment.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(payment.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Payment not completed for this Record ID = " + payment.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region Complete Cash Journal
dataRow = dsCashJournal.Tables[0].Select("DateAcct = '" + minDateRecord + "'", "C_Cash_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of Cash Journal ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
cash = new MCash(GetCtx(), Util.GetValueOfInt(dataRow[i]["C_Cash_ID"]), Get_Trx());
cash.CompleteIt();
if (cash.GetDocAction() == "CL")
{
cash.SetDocStatus("CO");
cash.SetDocAction("CL");
if (!cash.Save(Get_Trx()))
{
notCompletedRecord.Append(cash.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Cash Record ID = " + cash.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(cash.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Cash Journal not completed for this Record ID = " + cash.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
#region Complete GL Journal
dataRow = dsGLJournal.Tables[0].Select("DateAcct = '" + minDateRecord + "'", "GL_Journal_ID");
if (dataRow != null && dataRow.Length > 0)
{
message.Clear();
notCompletedRecord.Clear();
message.Append("Records Of GL Journal ");
for (int i = 0; i < dataRow.Length; i++)
{
try
{
journal = new MJournal(GetCtx(), Util.GetValueOfInt(dataRow[i]["GL_Journal_ID"]), Get_Trx());
journal.CompleteIt();
if (journal.GetDocAction() == "CL")
{
journal.SetDocStatus("CO");
journal.SetDocAction("CL");
if (!journal.Save(Get_Trx()))
{
notCompletedRecord.Append(journal.GetDocumentNo() + " ,");
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error found for saving C_Cash Record ID = " + journal.GetDocumentNo() +
" Error Name is " + pp.GetName() + " And Error Type is " + pp.GetType());
}
else
{
Get_Trx().Commit();
}
}
else
{
notCompletedRecord.Append(journal.GetDocumentNo() + " ,");
Get_Trx().Rollback();
_log.Info("Cash Journal not completed for this Record ID = " + journal.GetDocumentNo());
}
}
catch { }
}
if (!string.IsNullOrEmpty(notCompletedRecord.ToString()))
{
errorMessage.Append(message.ToString() + " : " + notCompletedRecord.ToString());
}
}
#endregion
}
catch { }
}
_log.Info(" End Imported Data completion for GulfOil : " + System.DateTime.Now);
}
catch (Exception ex)
{
_log.Info("Error Occured during completion of record by using ImportedDataCompletion Process - " + ex.ToString());
return(Msg.GetMsg(GetCtx(), "NotCompleted"));
}
if (!string.IsNullOrEmpty(errorMessage.ToString()))
{
return("Not Completed Record : " + errorMessage.ToString());
}
else
{
return(Msg.GetMsg(GetCtx(), "SucessfullyCompleted"));
}
}
/// <summary>
/// Create Difference Document.
/// Creates one or two inventory lines
/// </summary>
/// <param name="move">movement</param>
/// <param name="confirm">confirm line</param>
/// <returns>true if created</returns>
private Boolean CreateDifferenceDoc(MMovement move, MMovementLineConfirm confirm)
{
string query = "";
int result = 0;
decimal currentQty = 0;
MMovementLine mLine = confirm.GetLine();
// Difference - Create Inventory Difference for Source Location
if (Env.ZERO.CompareTo(confirm.GetDifferenceQty()) != 0)
{
// Get Warehouse for Source
MLocator loc = MLocator.Get(GetCtx(), mLine.GetM_Locator_ID());
if (_inventoryFrom != null &&
_inventoryFrom.GetM_Warehouse_ID() != loc.GetM_Warehouse_ID())
{
_inventoryFrom = null;
}
if (_inventoryFrom == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), loc.GetM_Warehouse_ID());
_inventoryFrom = new MInventory(wh);
_inventoryFrom.SetDescription(Msg.Translate(GetCtx(), "M_MovementConfirm_ID") + " " + GetDocumentNo());
if (!_inventoryFrom.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
// First Inventory
if (GetM_Inventory_ID() == 0)
{
SetM_Inventory_ID(_inventoryFrom.GetM_Inventory_ID());
_inventoryInfo = _inventoryFrom.GetDocumentNo();
}
else
{
_inventoryInfo += "," + _inventoryFrom.GetDocumentNo();
}
}
log.Info("createDifferenceDoc - Difference=" + confirm.GetDifferenceQty());
MInventoryLine line = new MInventoryLine(_inventoryFrom,
mLine.GetM_Locator_ID(), mLine.GetM_Product_ID(), mLine.GetM_AttributeSetInstance_ID(),
confirm.GetDifferenceQty(), Env.ZERO);
//Added By amit 11-jun-2015
//Opening Stock , Qunatity Book => CurrentQty From Transaction of MovementDate
//As On Date Count = Opening Stock - Diff Qty
//Qty Count = Qty Book - Diff Qty
query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate = " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
result = Util.GetValueOfInt(DB.ExecuteScalar(query));
if (result > 0)
{
query = @"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(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(query));
}
else
{
query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
result = Util.GetValueOfInt(DB.ExecuteScalar(query));
if (result > 0)
{
query = @"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(move.GetMovementDate(), true) + @"
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID() + @")
AND M_Product_ID = " + mLine.GetM_Product_ID() + " AND M_Locator_ID = " + mLine.GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + mLine.GetM_AttributeSetInstance_ID();
currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(query));
}
}
//End
line.SetAdjustmentType("D");
line.SetDifferenceQty(Util.GetValueOfDecimal(confirm.GetDifferenceQty()));
line.SetQtyBook(currentQty);
line.SetOpeningStock(currentQty);
line.SetAsOnDateCount(Decimal.Subtract(Util.GetValueOfDecimal(line.GetOpeningStock()), Util.GetValueOfDecimal(confirm.GetDifferenceQty())));
line.SetQtyCount(Decimal.Subtract(Util.GetValueOfDecimal(line.GetQtyBook()), Util.GetValueOfDecimal(confirm.GetDifferenceQty())));
line.SetDescription(Msg.Translate(GetCtx(), "DifferenceQty"));
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
} // Difference
// Scrapped - Create Inventory Difference for TarGet Location
if (Env.ZERO.CompareTo(confirm.GetScrappedQty()) != 0)
{
// Get Warehouse for TarGet
MLocator loc = MLocator.Get(GetCtx(), mLine.GetM_LocatorTo_ID());
if (_inventoryTo != null &&
_inventoryTo.GetM_Warehouse_ID() != loc.GetM_Warehouse_ID())
{
_inventoryTo = null;
}
if (_inventoryTo == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), loc.GetM_Warehouse_ID());
_inventoryTo = new MInventory(wh);
_inventoryTo.SetDescription(Msg.Translate(GetCtx(), "M_MovementConfirm_ID") + " " + GetDocumentNo());
if (!_inventoryTo.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
// First Inventory
if (GetM_Inventory_ID() == 0)
{
SetM_Inventory_ID(_inventoryTo.GetM_Inventory_ID());
_inventoryInfo = _inventoryTo.GetDocumentNo();
}
else
{
_inventoryInfo += "," + _inventoryTo.GetDocumentNo();
}
}
log.Info("CreateDifferenceDoc - Scrapped=" + confirm.GetScrappedQty());
MInventoryLine line = new MInventoryLine(_inventoryTo,
mLine.GetM_LocatorTo_ID(), mLine.GetM_Product_ID(), mLine.GetM_AttributeSetInstance_ID(),
confirm.GetScrappedQty(), Env.ZERO);
line.SetDescription(Msg.Translate(GetCtx(), "ScrappedQty"));
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
} // Scrapped
return(true);
}
/// <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 = "";
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);
//}
StringBuilder 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))");
}
// + " AND il.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID)");
//
sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
//
int count = 0;
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 = "UPDATE M_InventoryLine l "
+ "SET QtyCount=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);
}
/// <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));
}
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);
}
/**
* Create Difference Document
* @param inout shipment/receipt
* @param confirm confirm line
* @return true if created
*/
private bool CreateDifferenceDoc(MInOut inout, MInOutLineConfirm confirm)
{
if (_processMsg == null)
{
_processMsg = "";
}
else if (_processMsg.Length > 0)
{
_processMsg += "; ";
}
// Credit Memo if linked Document
if (Env.Signum(confirm.GetDifferenceQty()) != 0 &&
!inout.IsSOTrx() && !inout.IsReturnTrx() && inout.GetRef_InOut_ID() != 0)
{
log.Info("Difference=" + confirm.GetDifferenceQty());
if (_creditMemo == null)
{
_creditMemo = new MInvoice(inout, null);
_creditMemo.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
_creditMemo.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APCREDITMEMO);
if (!_creditMemo.Save(Get_TrxName()))
{
_processMsg += "Credit Memo not created";
return(false);
}
SetC_Invoice_ID(_creditMemo.GetC_Invoice_ID());
}
MInvoiceLine line = new MInvoiceLine(_creditMemo);
line.SetShipLine(confirm.GetLine());
line.SetQty(confirm.GetDifferenceQty()); // Entered/Invoiced
if (!line.Save(Get_TrxName()))
{
_processMsg += "Credit Memo Line not created";
return(false);
}
confirm.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
}
// Create Inventory Difference
if (Env.Signum(confirm.GetScrappedQty()) != 0)
{
log.Info("Scrapped=" + confirm.GetScrappedQty());
if (_inventory == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), inout.GetM_Warehouse_ID());
_inventory = new MInventory(wh);
_inventory.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
//vikas new 13 jan 2016 1
_inventory.SetIsInternalUse(true);
if (_inventory.GetC_DocType_ID() == 0)
{
MDocType[] types = MDocType.GetOfDocBaseType(GetCtx(), MDocBaseType.DOCBASETYPE_MATERIALPHYSICALINVENTORY);
if (types.Length > 0)
{
// Get Internal Use Inv Doc Type
for (int i = 0; i < types.Length; i++)
{
int _count = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Count(*) FROM C_DocType WHERE IsActive='Y' AND IsInternalUse='Y' AND C_DocType_ID=" + types[i].GetC_DocType_ID()));
if (_count > 0)
{
_inventory.SetC_DocType_ID(types[i].GetC_DocType_ID());
break;
}
}
}
else
{
log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "@NotFound@ @C_DocType_ID@"));
return(false);
}
}
// 13 jan End
if (!_inventory.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
SetM_Inventory_ID(_inventory.GetM_Inventory_ID());
}
MInOutLine ioLine = confirm.GetLine();
MInventoryLine line = new MInventoryLine(_inventory,
ioLine.GetM_Locator_ID(), ioLine.GetM_Product_ID(),
ioLine.GetM_AttributeSetInstance_ID(),
confirm.GetScrappedQty(), Env.ZERO);
//new 15 jan
line.SetQtyInternalUse(line.GetQtyBook());
line.SetQtyBook(0);
line.SetIsInternalUse(true);
Tuple <String, String, String> mInfo = null;
if (Env.HasModulePrefix("DTD001_", out mInfo))
{
int _charge = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Charge_ID FROM C_Charge WHERE isactive='Y' AND DTD001_ChargeType='INV'"));
line.SetC_Charge_ID(_charge);
}
// End
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
}
//
if (!confirm.Save(Get_TrxName()))
{
_processMsg += "Confirmation Line not saved";
return(false);
}
return(true);
}
private void ListEmptyInventory(MInventory pMInventory)
{
pMInventory.LListStatus = new List <SelectListItem>();
pMInventory.LListStatus = this.LStatus.Bll_getListStatusByIdObject(pMInventory.LObject.LIdObject).MListAllStatus();
}
/// <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 DocAtions GetActions(int AD_Table_ID, int Record_ID, string docStatus, bool processing, string orderType, bool isSOTrx, string docAction, string tableName, List <string> _values, List <string> _names)
{
DocAtions action = new DocAtions();
string[] options = null;
int index = 0;
string defaultV = "";
action.DocStatus = docStatus;
VLogger.Get().Fine("DocStatus=" + docStatus
+ ", DocAction=" + docAction + ", OrderType=" + orderType
+ ", IsSOTrx=" + isSOTrx + ", Processing=" + processing
+ ", AD_Table_ID=" + AD_Table_ID + ", Record_ID=" + Record_ID);
options = new String[_values.Count()];
String wfStatus = MWFActivity.GetActiveInfo(ctx, AD_Table_ID, Record_ID);
if (wfStatus != null)
{
VLogger.Get().SaveError("WFActiveForRecord", wfStatus);
action.Error = "WFActiveForRecord";
return(action);
}
// Status Change
if (!CheckStatus(tableName, Record_ID, docStatus))
{
VLogger.Get().SaveError("DocumentStatusChanged", "");
action.Error = "DocumentStatusChanged";
return(action);
}
// if (processing != null)
{
bool locked = "Y".Equals(processing);
if (!locked && processing.GetType() == typeof(Boolean))
{
locked = ((Boolean)processing);
}
// do not show Unlock action on Production execution
if (locked && !(AD_Table_ID == ViennaAdvantage.Model.X_VAMFG_M_WrkOdrTransaction.Table_ID))
{
options[index++] = DocumentEngine.ACTION_UNLOCK;
}
}
// Approval required .. NA
if (docStatus.Equals(DocumentEngine.STATUS_NOTAPPROVED))
{
options[index++] = DocumentEngine.ACTION_PREPARE;
options[index++] = DocumentEngine.ACTION_VOID;
}
// Draft/Invalid .. DR/IN
else if (docStatus.Equals(DocumentEngine.STATUS_DRAFTED) ||
docStatus.Equals(DocumentEngine.STATUS_INVALID))
{
options[index++] = DocumentEngine.ACTION_COMPLETE;
// options[index++] = DocumentEngine.ACTION_Prepare;
options[index++] = DocumentEngine.ACTION_VOID;
}
// In Process .. IP
else if (docStatus.Equals(DocumentEngine.STATUS_INPROGRESS) ||
docStatus.Equals(DocumentEngine.STATUS_APPROVED))
{
options[index++] = DocumentEngine.ACTION_COMPLETE;
options[index++] = DocumentEngine.ACTION_VOID;
}
// Complete .. CO
else if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_CLOSE;
}
// Waiting Payment
else if (docStatus.Equals(DocumentEngine.STATUS_WAITINGPAYMENT) ||
docStatus.Equals(DocumentEngine.STATUS_WAITINGCONFIRMATION))
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_PREPARE;
}
// Closed, Voided, REversed .. CL/VO/RE
else if (docStatus.Equals(DocumentEngine.STATUS_CLOSED) ||
docStatus.Equals(DocumentEngine.STATUS_VOIDED) ||
docStatus.Equals(DocumentEngine.STATUS_REVERSED))
{
return(action);
}
int refIndex = index;
bool indexFromModule = true;
GetActionFromModuleClass(AD_Table_ID, docStatus, out index, options);
if (index == 0)
{
index = refIndex;
indexFromModule = false;
}
/********************
* Order
*/
if (AD_Table_ID == MOrder.Table_ID)
{
// Draft .. DR/IP/IN
if (docStatus.Equals(DocumentEngine.STATUS_DRAFTED) ||
docStatus.Equals(DocumentEngine.STATUS_INPROGRESS) ||
docStatus.Equals(DocumentEngine.STATUS_INVALID))
{
options[index++] = DocumentEngine.ACTION_PREPARE;
options[index++] = DocumentEngine.ACTION_CLOSE;
// Draft Sales Order Quote/Proposal - Process
if (isSOTrx &&
("OB".Equals(orderType) || "ON".Equals(orderType)))
{
docAction = DocumentEngine.ACTION_PREPARE;
}
}
// Complete .. CO
else if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_REACTIVATE;
}
else if (docStatus.Equals(DocumentEngine.STATUS_WAITINGPAYMENT))
{
options[index++] = DocumentEngine.ACTION_REACTIVATE;
options[index++] = DocumentEngine.ACTION_CLOSE;
}
}
/********************
* Shipment
*/
else if (AD_Table_ID == MInOut.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_REVERSE_CORRECT;
}
}
/********************
* Invoice
*/
else if (AD_Table_ID == MInvoice.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_REVERSE_CORRECT;
}
}
/********************
* Payment
*/
else if (AD_Table_ID == MPayment.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_REVERSE_CORRECT;
}
}
/********************
* GL Journal
*/
//else if (AD_Table_ID == MJournal.Table_ID || AD_Table_ID == MJournalBatch.Table_ID)
//{
// // Complete .. CO
// if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
// {
// options[index++] = DocumentEngine.ACTION_REVERSE_CORRECT;
// options[index++] = DocumentEngine.ACTION_REVERSE_ACCRUAL;
// }
//}
/********************
* Allocation
*/
else if (AD_Table_ID == MAllocationHdr.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_REVERSE_CORRECT;
}
}
/********************
* Bank Statement
*/
else if (AD_Table_ID == MBankStatement.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
options[index++] = DocumentEngine.ACTION_VOID;
}
}
/********************
* Inventory Movement, Physical Inventory
*/
else if (AD_Table_ID == MMovement.Table_ID ||
AD_Table_ID == MInventory.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
// SI_0622 : not to show VOID and REVERSE_CORRECT action on Physical Inventory
bool isPhysicalInventory = false;
if (AD_Table_ID == MInventory.Table_ID)
{
MInventory inventory = MInventory.Get(ctx, Record_ID);
isPhysicalInventory = !inventory.IsInternalUse();
}
if (!isPhysicalInventory)
{
options[index++] = DocumentEngine.ACTION_VOID;
options[index++] = DocumentEngine.ACTION_REVERSE_CORRECT;
}
}
}
// Added By Arpit
else if (AD_Table_ID == MMovementConfirm.Table_ID)
{
// Complete .. CO
if (docStatus.Equals(DocumentEngine.STATUS_COMPLETED))
{
// SI_0630 : System should not allow to void the Move confirmation after its completion
//options[index++] = DocumentEngine.ACTION_VOID;
}
}
//End
// /********************
//* Warehouse Task New Add by raghu 11 april,2011
//*/
// else if (AD_Table_ID == X_M_WarehouseTask.Table_ID
// || AD_Table_ID == X_M_TaskList.Table_ID)
// {
// // Draft .. DR/IP/IN
// if (docStatus.Equals(DocActionVariables.STATUS_DRAFTED)
// || docStatus.Equals(DocActionVariables.STATUS_INPROGRESS)
// || docStatus.Equals(DocActionVariables.STATUS_INVALID))
// {
// options[index++] = DocActionVariables.ACTION_PREPARE;
// }
// // Complete .. CO
// else if (docStatus.Equals(DocActionVariables.STATUS_COMPLETED))
// {
// options[index++] = DocActionVariables.ACTION_VOID;
// options[index++] = DocActionVariables.ACTION_REVERSE_CORRECT;
// }
// }
/********************
* Work Order New Add by raghu 11 april,2011
*/
else if (AD_Table_ID == ViennaAdvantage.Model.X_VAMFG_M_WorkOrder.Table_ID)
{
// Draft .. DR/IP/IN
if (docStatus.Equals(DocActionVariables.STATUS_DRAFTED) ||
docStatus.Equals(DocActionVariables.STATUS_INPROGRESS) ||
docStatus.Equals(DocActionVariables.STATUS_INVALID))
{
options[index++] = DocActionVariables.ACTION_PREPARE;
}
// Complete .. CO
else if (docStatus.Equals(DocActionVariables.STATUS_COMPLETED))
{
options[index++] = DocActionVariables.ACTION_VOID;
options[index++] = DocActionVariables.ACTION_REACTIVATE;
}
}
/********************
* Work Order Transaction New Add by raghu 11 april,2011
*/
else if (AD_Table_ID == ViennaAdvantage.Model.X_VAMFG_M_WrkOdrTransaction.Table_ID)
{
// Draft .. DR/IP/IN
if (docStatus.Equals(DocActionVariables.STATUS_DRAFTED) ||
docStatus.Equals(DocActionVariables.STATUS_INPROGRESS) ||
docStatus.Equals(DocActionVariables.STATUS_INVALID))
{
options[index++] = DocActionVariables.ACTION_PREPARE;
}
// Complete .. CO
else if (docStatus.Equals(DocActionVariables.STATUS_COMPLETED))
{
options[index++] = DocActionVariables.ACTION_VOID;
options[index++] = DocActionVariables.ACTION_REVERSE_CORRECT;
}
}
/* Obsolete Inventory New add by Amit 24-May-2016 */
//else if (tableName == "VA024_ObsoleteInventory")
//{
// // DR/IN
// if (docStatus.Equals(DocumentEngine.STATUS_DRAFTED)
// || docStatus.Equals(DocumentEngine.STATUS_INVALID))
// {
// options[index++] = DocumentEngine.ACTION_COMPLETE;
// options[index++] = DocumentEngine.ACTION_VOID;
// }
// // Complete
// else if (docStatus.Equals(DocActionVariables.STATUS_COMPLETED))
// {
// options[index++] = DocActionVariables.ACTION_VOID;
// options[index++] = DocActionVariables.ACTION_REVERSE_CORRECT;
// }
//}
/***For Primary thread***/
///**
// * Fill actionCombo
// */
//for (int i = 0; i < index; i++)
//{
// // Serach for option and add it
// bool added = false;
// for (int j = 0; j < _values.Length && !added; j++)
// if (options[i].Equals(_values[j]))
// {
// //actionCombo.addItem(_names[j]);
// vcmbAction.Items.Add(_names[j]);
// added = true;
// }
//}
// setDefault
if (docAction.Equals("--")) // If None, suggest closing
{
docAction = DocumentEngine.ACTION_CLOSE;
}
//if (C_DocType_ID == 0)
//{
// C_DocType_ID = C_DocTypeTarget_ID;
//}
//if (C_DocType_ID != null && C_DocType_ID > 0)
//{
// String[] docActionHolder = new String[] { docAction };
// MTable table = MTable.Get(ctx, AD_Table_ID);
// PO po = table.GetPO(ctx, Record_ID, null);
// if (po is DocOptions)
// index = ((DocOptions)po).customizeValidActions(docStatus, processing, orderType, isSOTrx ? "Y" : "N",
// AD_Table_ID, docActionHolder, options, index);
// // options = DocumentEngine.checkActionAccess(ctx, ctx.GetAD_Client_ID(), ctx.GetAD_Role_ID(), C_DocType_ID, options, ref index);
//}
for (int i = 0; i < _values.Count() && defaultV.Equals(""); i++)
{
if (docAction.Equals(_values[i]))
{
defaultV = _names[i];
}
}
action.Options = options.ToList();
if (indexFromModule)
{
action.Index = index + 1;
}
else
{
action.Index = index;
}
action.DefaultV = defaultV;
return(action);
/***For Primary thread***/
//if (!defaultV.Equals(""))
//{
// //vcmbAction.SelectedValue = defaultV;
// vcmbAction.SelectedItem = defaultV;
//}
}
