/// <summary>
/// is used to set value for Container Storage
/// </summary>
/// <param name="containerStorage">object of container storage</param>
/// <param name="dr">data row</param>
/// <param name="isPhysicalInventory">is record created for physical inventory</param>
/// <returns>object of container storage</returns>
private X_M_ContainerStorage InsertContainerStorage(X_M_ContainerStorage containerStorage, DataRow dr, bool isPhysicalInventory)
{
containerStorage.SetAD_Client_ID(Convert.ToInt32(dr["AD_Client_ID"]));
containerStorage.SetAD_Org_ID(Convert.ToInt32(dr["AD_Org_ID"]));
containerStorage.SetM_Locator_ID(Convert.ToInt32(dr["M_Locator_ID"]));
containerStorage.SetM_Product_ID(Convert.ToInt32(dr["M_Product_ID"]));
containerStorage.SetM_AttributeSetInstance_ID(Convert.ToInt32(dr["M_AttributeSetInstance_ID"]));
// when storage dont have DateLastInventory -- means not any physical inventory
if (dr["DateLastInventory"] == DBNull.Value)
{
containerStorage.SetMMPolicyDate(DateTime.Now);
containerStorage.SetQty(Convert.ToDecimal(dr["QtyOnHand"]));
}
else
{
// when (QtyOnhand - qty) > 0 means storage having more qty than last physical inventory
Decimal qty = Decimal.Subtract(Convert.ToDecimal(dr["QtyOnHand"]), Convert.ToDecimal(dr["qty"]));
if (isPhysicalInventory)
{
containerStorage.SetMMPolicyDate(Convert.ToDateTime(dr["DateLastInventory"]));
containerStorage.SetIsPhysicalInventory(true);
if (qty >= 0)
{
// when storage having more qty than last physical inventory then did entry on container storage with (Physical Inventory Qty)
containerStorage.SetQty(Convert.ToDecimal(dr["qty"]));
}
else
{
// when storage having less qty than last physical inventory then did entry on container storage with (Physical Inventory Qty - qty on storage)
//containerStorage.SetQty(Decimal.Subtract(Convert.ToDecimal(dr["qty"]), Convert.ToDecimal(dr["QtyOnHand"])));
containerStorage.SetQty(Convert.ToDecimal(dr["QtyOnHand"]));
}
}
else
{
containerStorage.SetMMPolicyDate(DateTime.Now);
containerStorage.SetQty(qty);
}
}
return(containerStorage);
}
/// <summary>
/// This function is used to create or update record on Container Storage
/// </summary>
/// <param name="containerStorage">Container Storage Object</param>
/// <param name="dr">data row</param>
/// <returns>container Storage object</returns>
private X_M_ContainerStorage InsertContainerStorage(X_M_ContainerStorage containerStorage, DataRow dr)
{
if (containerStorage.Get_ID() <= 0)
{
containerStorage.SetAD_Client_ID(Convert.ToInt32(dr["AD_Client_ID"]));
containerStorage.SetAD_Org_ID(MLocator.Get(containerStorage.GetCtx(), Convert.ToInt32(dr["M_Locator_ID"])).GetAD_Org_ID());
containerStorage.SetM_Locator_ID(Convert.ToInt32(dr["M_Locator_ID"]));
containerStorage.SetM_Product_ID(Convert.ToInt32(dr["M_Product_ID"]));
containerStorage.SetM_AttributeSetInstance_ID(Convert.ToInt32(dr["M_AttributeSetInstance_ID"]));
containerStorage.SetMMPolicyDate(Convert.ToDateTime(dr["MovementDate"]));
containerStorage.SetIsPhysicalInventory(Convert.ToString(dr["PhysicalInventory"]).Equals("Y") ? true : false);
containerStorage.SetQty(Convert.ToDecimal(dr["MovementQty"]));
}
else
{
containerStorage.SetQty(Decimal.Add(containerStorage.GetQty(), Convert.ToDecimal(dr["MovementQty"])));
}
return(containerStorage);
}
/// <summary>
/// This Method is used to Activate Product container functionlaity into Vienna System
/// </summary>
/// <returns>Message - Container is Applicabled or not</returns>
protected override string DoIt()
{
// check container applicable or not
if (Util.GetValueOfString(GetCtx().GetContext("#PRODUCT_CONTAINER_APPLICABLE")).Equals("N"))
{
// verify document is closed or not
if (!VerifyDocumentStatus())
{
return(Msg.GetMsg(GetCtx(), "VIS_NotClosedocument"));
}
// update container qty with current qty on all records on transaction
int no = DB.ExecuteQuery(@"UPDATE M_TRANSACTION SET ContainerCurrentQty = CurrentQty", null, Get_Trx());
if (no > 0)
{
// delete all record from container storage
no = DB.ExecuteQuery(@"DELETE FROM M_ContainerStorage", null, Get_Trx());
// get all data from storage having OnHandQty != 0, and create recod on container storage with locator, product and qty details
sql.Clear();
sql.Append(@"SELECT M_Transaction.AD_client_ID , M_Transaction.AD_Org_ID , M_Transaction.M_Locator_ID , M_Transaction.M_Product_ID ,
NVL(M_Transaction.M_AttributeSetInstance_ID , 0) AS M_AttributeSetInstance_ID , M_Transaction.M_Transaction_ID ,
M_Transaction.MovementDate , NVL(M_Transaction.MovementQty , 0) AS MovementQty , M_Transaction.CurrentQty , M_Transaction.MovementType ,
CASE WHEN M_Transaction.M_InventoryLine_ID > 0
AND (SELECT IsInternalUse FROM M_InventoryLine
WHERE M_InventoryLine.M_InventoryLine_ID = M_Transaction.M_InventoryLine_ID ) = 'N'
THEN 'Y' ELSE 'N' END AS PhysicalInventory,
(SELECT MMPolicy FROM M_Product_Category WHERE M_Product_Category.M_Product_Category_ID = M_Product.M_Product_Category_ID) AS MMPolicy
FROM M_Transaction
INNER JOIN M_Product ON M_Product.M_Product_ID = M_Transaction.M_Product_ID
ORDER BY M_Transaction.AD_Client_ID , M_Transaction.M_Locator_ID , M_Transaction.M_Product_ID ,
M_Transaction.M_AttributeSetInstance_ID , M_Transaction.MovementDate , M_Transaction.M_Transaction_ID ASC");
DataSet dsStorage = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
if (dsStorage != null && dsStorage.Tables.Count > 0 && dsStorage.Tables[0].Rows.Count > 0)
{
X_M_ContainerStorage containerStorage = null;
int M_ContainerStorage_ID = 0;
for (int i = 0; i < dsStorage.Tables[0].Rows.Count; i++)
{
// get dataRow from dataset
DataRow dr = dsStorage.Tables[0].Rows[i];
if (Convert.ToInt32(dr["MovementQty"]) > 0)
{
// Quantity IN
M_ContainerStorage_ID = GetContainerStorage(Convert.ToInt32(dr["M_Locator_ID"]), Convert.ToInt32(dr["M_Product_ID"]),
Convert.ToInt32(dr["M_AttributeSetInstance_ID"]), Convert.ToDateTime(dr["MovementDate"]),
Convert.ToString(dr["PhysicalInventory"]));
containerStorage = new X_M_ContainerStorage(GetCtx(), M_ContainerStorage_ID, Get_Trx()); // object of container storage
// Set or Update Values
containerStorage = InsertContainerStorage(containerStorage, dr);
if (!containerStorage.Save(Get_Trx()))
{
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
throw new ArgumentException((Msg.GetMsg(GetCtx(), "VIS_ContainerStorageNotSave") + (pp != null && !String.IsNullOrEmpty(pp.GetName()) ? pp.GetName() : " ")));
}
else
{
// after insertion, system will check if qty = 0 and not a physical inventory record then delete record
if (containerStorage.GetQty() == 0 && !containerStorage.IsPhysicalInventory())
{
containerStorage.Delete(true, Get_Trx());
}
}
}
else
{
// Quantity Out
ConsumeQtyFromStorage(dr);
}
}
}
}
// Update setting as Container Applicable into the system
no = DB.ExecuteQuery("UPDATE AD_SysConfig SET VALUE = 'Y' WHERE Name='PRODUCT_CONTAINER_APPLICABLE'", null, Get_Trx());
}
else
{
return(Msg.GetMsg(GetCtx(), "VIS_AlreadyActivateContainer"));
}
return(Msg.GetMsg(GetCtx(), "VIS_ActivatedContainer"));
}
/// <summary>
/// This funcion is used to get record from container storage based on Material Policy
/// </summary>
/// <param name="dr">data row object</param>
/// <returns>true/false</returns>
private bool ConsumeQtyFromStorage(DataRow dr)
{
Decimal Qty = Math.Abs(Convert.ToDecimal(dr["MovementQty"]));
int no;
String sql = @"SELECT M_ContainerStorage_ID , Qty , IsPhysicalInventory FROM M_ContainerStorage WHERE Qty > 0
AND M_Locator_ID = " + Convert.ToInt32(dr["M_Locator_ID"]) +
@" AND M_Product_ID = " + Convert.ToInt32(dr["M_Product_ID"]) +
@" AND NVL(M_AttributeSetInstance_ID , 0) = " + Convert.ToInt32(dr["M_AttributeSetInstance_ID"]);
if (Convert.ToString(dr["MMPolicy"]).Equals(MProductCategory.MMPOLICY_LiFo))
{
sql += " ORDER BY MMPolicyDate DESC , M_ContainerStorage_ID DESC";
}
else
{
sql += " ORDER BY MMPolicyDate , M_ContainerStorage_ID ASC";
}
DataSet ds = DB.ExecuteDataset(sql, null, Get_Trx());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Decimal qtyStorage = 0.0M;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
qtyStorage = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["Qty"]);
if (Qty >= qtyStorage)
{
// when consume qty is greater than current traverse row qty, then delete this record if not physical record else set qty = 0
if (Util.GetValueOfString(ds.Tables[0].Rows[i]["IsPhysicalInventory"]).Equals("Y"))
{
// update record - bcz it is physical inventory record
no = DB.ExecuteQuery("Update M_ContainerStorage SET QTY = 0 , IsPhysicalInventory = 'Y' WHERE M_ContainerStorage_ID = "
+ Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["M_ContainerStorage_ID"]), null, Get_Trx());
}
else
{
// delete record
no = DB.ExecuteQuery("DELETE FROM M_ContainerStorage WHERE M_ContainerStorage_ID = "
+ Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["M_ContainerStorage_ID"]), null, Get_Trx());
}
// set remaining qty which is to be reduce
Qty = decimal.Subtract(Qty, qtyStorage);
}
else
{
// subtract qty from the current traverse record
no = DB.ExecuteQuery("Update M_ContainerStorage SET QTY = Qty - " + Qty + @" WHERE M_ContainerStorage_ID = "
+ Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["M_ContainerStorage_ID"]), null, Get_Trx());
Qty = 0;
break;
}
}
}
// after consumption, if any qty left than need to create record there with -ve remaining qty
if (Qty != 0)
{
X_M_ContainerStorage containerStorage = new X_M_ContainerStorage(GetCtx(), 0, Get_Trx()); // object of container storage
containerStorage = InsertContainerStorage(containerStorage, dr);
containerStorage.SetQty(Decimal.Negate(Qty));
if (!containerStorage.Save(Get_Trx()))
{
Get_Trx().Rollback();
ValueNamePair pp = VLogger.RetrieveError();
throw new ArgumentException((Msg.GetMsg(GetCtx(), "VIS_ContainerStorageNotSave") + (pp != null && !String.IsNullOrEmpty(pp.GetName()) ? pp.GetName() : " ")));
}
}
return(true);
}
