public Dictionary <string, string> GetProduct(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
//Assign parameter value
int M_Product_ID = Util.GetValueOfInt(paramValue[0].ToString());
int M_Warehouse_ID = 0;
if (paramValue.Length > 1)
{
M_Warehouse_ID = Util.GetValueOfInt(paramValue[1].ToString());
}
//End Assign parameter value
MProduct product = MProduct.Get(ctx, M_Product_ID);
Dictionary <string, string> result = new Dictionary <string, string>();
result["C_UOM_ID"] = product.GetC_UOM_ID().ToString();
result["IsStocked"] = product.IsStocked() ? "Y" : "N";
if (M_Product_ID > 0)
{
if (M_Warehouse_ID > 0)
{
result["M_Locator_ID"] = MProductLocator.GetFirstM_Locator_ID(product, M_Warehouse_ID).ToString();
}
}
return(result);
}
/// <summary>
/// Create/Add to Inventory Line Query
/// </summary>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">asi</param>
/// <param name="qtyOnHand">quantity</param>
/// <param name="M_AttributeSet_ID">attribute set</param>
/// <returns>lines added</returns>
private string InsertInventoryLine(int M_Locator_ID, int M_Product_ID, int lineNo,
int M_AttributeSetInstance_ID, Decimal qtyOnHand, int M_AttributeSet_ID)
{
MInventoryLine line = new MInventoryLine(GetCtx(), 0, Get_Trx());
int line_ID = DB.GetNextID(GetCtx(), "M_InventoryLine", Get_Trx());
string qry = "select m_warehouse_id from m_locator where m_locator_id=" + M_Locator_ID;
int M_Warehouse_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry, null, Get_Trx()));
MWarehouse wh = MWarehouse.Get(GetCtx(), M_Warehouse_ID);
if (wh.IsDisallowNegativeInv() == true)
{
if (qtyOnHand < 0)
{
return("");
}
}
MProduct product = MProduct.Get(GetCtx(), M_Product_ID);
if (product != null)
{
int precision = product.GetUOMPrecision();
if (Env.Signum(qtyOnHand) != 0)
{
qtyOnHand = Decimal.Round(qtyOnHand, precision, MidpointRounding.AwayFromZero);
}
}
string sql = @"INSERT INTO M_InventoryLine (AD_Client_ID, AD_Org_ID,IsActive, Created, CreatedBy, Updated, UpdatedBy, Line, M_Inventory_ID, M_InventoryLine_ID,
M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, QtyBook, QtyCount, OpeningStock, AsOnDateCount, DifferenceQty, AdjustmentType";
if (line.Get_ColumnIndex("C_UOM_ID") > 0)
{
sql += ", QtyEntered, C_UOM_ID";
}
if (line.Get_ColumnIndex("IsFromProcess") > 0)
{
sql += ",IsFromProcess";
}
sql += " ) VALUES ( " + _inventory.GetAD_Client_ID() + "," + _inventory.GetAD_Org_ID() + ",'Y'," + GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," +
GlobalVariable.TO_DATE(DateTime.Now, true) + "," + 0 + "," + lineNo + "," + _m_Inventory_ID + "," + line_ID + "," + M_Locator_ID + "," + M_Product_ID + "," +
M_AttributeSetInstance_ID + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + qtyOnHand + "," + 0 + ",'A'";
if (line.Get_ColumnIndex("C_UOM_ID") > 0)
{
sql += "," + qtyOnHand + "," + product.GetC_UOM_ID();
}
if (line.Get_ColumnIndex("IsFromProcess") > 0)
{
sql += ",'Y'";
}
string insertQry = " BEGIN execute immediate('" + sql.Replace("'", "''") + ")'); exception when others then null; END;";
return(insertQry);
}
/// <summary>
/// Quantity UOM
/// </summary>
/// <returns>Transaction or Storage M_UOM_ID</returns>
public int GetC_UOM_ID()
{
// Trx UOM
int index = _po.Get_ColumnIndex("C_UOM_ID");
if (index != -1)
{
int?ii = (int?)_po.Get_Value(index);
if (ii != null)
{
return(Utility.Util.GetValueOfInt(ii));//.intValue();
}
}
// Storage UOM
MProduct product = GetProduct();
if (product != null)
{
return(product.GetC_UOM_ID());
}
//
return(0);
}
public bool SetProductQty(int recordID, string keyColName, List <string> product, List <string> attribute, List <string> qty, List <string> qtybook, List <string> oline_ID, int ordID, List <string> locID, int lineID, VAdvantage.Utility.Ctx ctx)
{
if (keyColName.ToUpper().Trim() == "C_ORDER_ID")
{
MOrder ord = new MOrder(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MOrderLine oline = new MOrderLine(ctx, lineID, null);
oline.SetAD_Client_ID(ord.GetAD_Client_ID());
oline.SetAD_Org_ID(ord.GetAD_Org_ID());
oline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
oline.SetQty(Util.GetValueOfDecimal(qty[i]));
oline.SetC_Order_ID(recordID);
if (Util.GetValueOfInt(attribute[i]) != 0)
{
oline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!ord.IsSOTrx())
{
MProduct pro = new MProduct(ctx, oline.GetM_Product_ID(), null);
String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + oline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + ord.GetC_BPartner_ID();
int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom));
if (pro.GetC_UOM_ID() != 0)
{
if (pro.GetC_UOM_ID() != uom && uom != 0)
{
decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + oline.GetM_Product_ID() + " AND IsActive='Y'"));
if (Res > 0)
{
oline.SetQtyEntered(oline.GetQtyEntered() * Res);
//OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty);
}
else
{
decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'"));
if (res > 0)
{
oline.SetQtyEntered(oline.GetQtyEntered() * res);
//OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty);
}
}
oline.SetC_UOM_ID(uom);
}
else
{
oline.SetC_UOM_ID(pro.GetC_UOM_ID());
}
}
}
if (!oline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "C_INVOICE_ID")
{
MInvoice inv = new MInvoice(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MInvoiceLine invline = new MInvoiceLine(ctx, lineID, null);
invline.SetAD_Client_ID(inv.GetAD_Client_ID());
invline.SetAD_Org_ID(inv.GetAD_Org_ID());
invline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
invline.SetQty(Util.GetValueOfDecimal(qty[i]));
invline.SetC_Invoice_ID(recordID);
if (Util.GetValueOfInt(attribute[i]) != 0)
{
invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!inv.IsSOTrx())
{
MProduct pro = new MProduct(ctx, invline.GetM_Product_ID(), null);
String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + invline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + inv.GetC_BPartner_ID();
int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom));
if (pro.GetC_UOM_ID() != 0)
{
if (pro.GetC_UOM_ID() != uom && uom != 0)
{
decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + invline.GetM_Product_ID() + " AND IsActive='Y'"));
if (Res > 0)
{
invline.SetQtyEntered(invline.GetQtyEntered() * Res);
//OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty);
}
else
{
decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'"));
if (res > 0)
{
invline.SetQtyEntered(invline.GetQtyEntered() * res);
//OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty);
}
}
invline.SetC_UOM_ID(uom);
}
else
{
invline.SetC_UOM_ID(pro.GetC_UOM_ID());
}
}
}
if (!invline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "M_INOUT_ID")
{
MInOut inv = new MInOut(ctx, recordID, null);
if (ordID > 0)
{
inv.SetC_Order_ID(ordID);
}
if (inv.Save())
{
for (int i = 0; i < product.Count; i++)
{
MInOutLine ioline = new MInOutLine(ctx, lineID, null);
ioline.SetAD_Client_ID(inv.GetAD_Client_ID());
ioline.SetAD_Org_ID(inv.GetAD_Org_ID());
ioline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
ioline.SetQty(Util.GetValueOfDecimal(qty[i]));
ioline.SetM_InOut_ID(recordID);
ioline.SetC_OrderLine_ID(Util.GetValueOfInt(oline_ID[i]));
ioline.SetM_Locator_ID(Util.GetValueOfInt(locID[i]));
if (Util.GetValueOfInt(attribute[i]) != 0)
{
ioline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
if (!inv.IsSOTrx())
{
MProduct pro = new MProduct(ctx, ioline.GetM_Product_ID(), null);
String qryUom = "SELECT vdr.C_UOM_ID FROM M_Product p LEFT JOIN M_Product_Po vdr ON p.M_Product_ID= vdr.M_Product_ID WHERE p.M_Product_ID=" + ioline.GetM_Product_ID() + " AND vdr.C_BPartner_ID = " + inv.GetC_BPartner_ID();
int uom = Util.GetValueOfInt(DB.ExecuteScalar(qryUom));
if (pro.GetC_UOM_ID() != 0)
{
if (pro.GetC_UOM_ID() != uom && uom != 0)
{
decimal?Res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND M_Product_ID= " + ioline.GetM_Product_ID() + " AND IsActive='Y'"));
if (Res > 0)
{
ioline.SetQtyEntered(ioline.GetQtyEntered() * Res);
//OrdQty = MUOMConversion.ConvertProductTo(GetCtx(), _M_Product_ID, UOM, OrdQty);
}
else
{
decimal?res = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT trunc(multiplyrate,4) FROM C_UOM_Conversion WHERE C_UOM_ID = " + pro.GetC_UOM_ID() + " AND C_UOM_To_ID = " + uom + " AND IsActive='Y'"));
if (res > 0)
{
ioline.SetQtyEntered(ioline.GetQtyEntered() * res);
//OrdQty = MUOMConversion.Convert(GetCtx(), prdUOM, UOM, OrdQty);
}
}
ioline.SetC_UOM_ID(uom);
}
else
{
ioline.SetC_UOM_ID(pro.GetC_UOM_ID());
}
}
}
if (!ioline.Save())
{
}
}
}
}
else if (keyColName.ToUpper().Trim() == "M_PACKAGE_ID")
{
MPackage pkg = new MPackage(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MPackageLine mline = new MPackageLine(ctx, lineID, null);
mline.SetAD_Client_ID(pkg.GetAD_Client_ID());
mline.SetAD_Org_ID(pkg.GetAD_Org_ID());
mline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
mline.SetQty(Util.GetValueOfDecimal(qty[i]));
if (Util.GetValueOfInt(oline_ID[i]) > 0)
{
mline.SetM_MovementLine_ID(Util.GetValueOfInt(oline_ID[i]));
MMovementLine mov = new MMovementLine(ctx, Util.GetValueOfInt(oline_ID[i]), null);
mline.SetDTD001_TotalQty(mov.GetMovementQty());
}
if (Util.GetValueOfInt(attribute[i]) != 0)
{
mline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
mline.SetM_Package_ID(recordID);
if (!mline.Save())
{
}
}
}
else if (keyColName.ToUpper().Trim() == "M_INVENTORY_ID")
{
MInventory inv = new MInventory(ctx, recordID, null);
for (int i = 0; i < product.Count; i++)
{
MInventoryLine invline = new MInventoryLine(ctx, lineID, null);
invline.SetAD_Client_ID(inv.GetAD_Client_ID());
invline.SetAD_Org_ID(inv.GetAD_Org_ID());
invline.SetM_Locator_ID(Util.GetValueOfInt(locID[i]));
invline.SetM_Product_ID(Util.GetValueOfInt(product[i]));
invline.SetQtyCount(Util.GetValueOfDecimal(qty[i]));
invline.SetQtyBook(Util.GetValueOfDecimal(qtybook[i]));
invline.SetAsOnDateCount(Util.GetValueOfDecimal(qty[i]));
invline.SetOpeningStock(Util.GetValueOfDecimal(qtybook[i]));
invline.SetM_Inventory_ID(recordID);
if (Util.GetValueOfInt(attribute[i]) != 0)
{
invline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(attribute[i]));
}
else
{
invline.SetM_AttributeSetInstance_ID(0);
}
if (!invline.Save())
{
}
}
}
return(true);
}
/// <summary>
/// Create/Add to Inventory Line
/// </summary>
/// <param name="M_Locator_ID">locator</param>
/// <param name="M_Product_ID">product</param>
/// <param name="M_AttributeSetInstance_ID">asi</param>
/// <param name="qtyOnHand">quantity</param>
/// <param name="M_AttributeSet_ID">attribute set</param>
/// <returns>lines added</returns>
private int CreateInventoryLine(int M_Locator_ID, int M_Product_ID,
int M_AttributeSetInstance_ID, Decimal qtyOnHand, Decimal currentQty, int M_AttributeSet_ID)
{
Boolean oneLinePerASI = false;
if (M_AttributeSet_ID != 0)
{
MAttributeSet mas = MAttributeSet.Get(GetCtx(), M_AttributeSet_ID);
oneLinePerASI = mas.IsInstanceAttribute();
}
if (oneLinePerASI)
{
MInventoryLine line = new MInventoryLine(_inventory, M_Locator_ID,
M_Product_ID, M_AttributeSetInstance_ID,
qtyOnHand, qtyOnHand); // book/count
line.SetOpeningStock(currentQty);
line.SetAsOnDateCount(line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (line.Get_ColumnIndex("QtyEntered") > 0)
{
line.Set_Value("QtyEntered", line.GetQtyCount());
}
if (line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (line.Get_ColumnIndex("IsFromProcess") >= 0)
{
line.SetIsFromProcess(true);
}
if (line.Save())
{
return(1);
}
return(0);
}
if (Env.Signum(qtyOnHand) == 0)
{
M_AttributeSetInstance_ID = 0;
}
if (_line != null &&
_line.GetM_Locator_ID() == M_Locator_ID &&
_line.GetM_Product_ID() == M_Product_ID)
{
if (Env.Signum(qtyOnHand) == 0)
{
return(0);
}
// Same ASI (usually 0)
if (_line.GetM_AttributeSetInstance_ID() == M_AttributeSetInstance_ID)
{
_line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
_line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
_line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
_line.SetAsOnDateCount(_line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.Set_Value("QtyEntered", _line.GetQtyCount());
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
_line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
{
_line.SetIsFromProcess(true);
}
_line.Save();
return(0);
}
// Save Old Line info
else if (_line.GetM_AttributeSetInstance_ID() != 0)
{
MInventoryLineMA ma = new MInventoryLineMA(_line,
_line.GetM_AttributeSetInstance_ID(), _line.GetQtyBook());
if (!ma.Save())
{
;
}
}
_line.SetM_AttributeSetInstance_ID(0);
_line.SetQtyBook(Decimal.Add(_line.GetQtyBook(), qtyOnHand));
_line.SetQtyCount(Decimal.Add(_line.GetQtyCount(), qtyOnHand));
_line.SetOpeningStock((Decimal.Add(_line.GetOpeningStock(), currentQty)));
_line.SetAsOnDateCount(_line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.Set_Value("QtyEntered", _line.GetQtyCount());
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
_line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
{
_line.SetIsFromProcess(true);
}
_line.Save();
//
MInventoryLineMA ma1 = new MInventoryLineMA(_line, M_AttributeSetInstance_ID, qtyOnHand);
if (!ma1.Save())
{
;
}
return(0);
}
// new line
_line = new MInventoryLine(_inventory, M_Locator_ID, M_Product_ID,
M_AttributeSetInstance_ID, qtyOnHand, qtyOnHand); // book/count
_line.SetOpeningStock(currentQty);
_line.SetAsOnDateCount(_line.GetQtyCount());
// JID_0903: On creating the lines from physical inventory count system is not updating the UOM and Qty on physical inventory line.
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.Set_Value("QtyEntered", _line.GetQtyCount());
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0)
{
MProduct prd = new MProduct(GetCtx(), M_Product_ID, Get_Trx());
_line.Set_Value("C_UOM_ID", prd.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("IsFromProcess") >= 0)
{
_line.SetIsFromProcess(true);
}
if (_line.Save())
{
return(1);
}
return(0);
}
/// <summary>
/// Process
/// </summary>
/// <returns>info</returns>
protected override String DoIt()
{
log.Info("M_Movement_ID=" + _m_Movement_ID
+ ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID
+ ", M_Product_ID=" + _m_product_ID
+ ", M_Product_Category_ID=" + _m_Product_Category_ID
+ ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld);
_movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx());
if (_movement.Get_ID() == 0)
{
throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID);
}
if (_movement.IsProcessed())
{
throw new SystemException("@M_Movement_ID@ @Processed@");
}
// is used to check Container applicable into system
isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx());
//
String sqlQry = "";
if (_deleteOld)
{
sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' "
+ "AND M_Movement_ID=" + _m_Movement_ID;
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("doIt - Deleted #" + no);
}
// Create Null Storage records
if (_qtyRange != null && _qtyRange.Equals("="))
{
sqlQry = "INSERT INTO M_Storage "
+ "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,"
+ " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) "
+ "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0,"
+ " l.M_Locator_ID, p.M_Product_ID, 0,"
+ " 0,0,0,null "
+ "FROM M_Locator l"
+ " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetM_Warehouse_ID();
if (_m_Locator_ID != 0)
{
sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID;
}
sqlQry += " AND l.IsDefault='Y'"
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"
+ " AND NOT EXISTS (SELECT * FROM M_Storage s"
+ " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) "
+ "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID"
+ " AND s.M_Product_ID=p.M_Product_ID)";
int no = DB.ExecuteQuery(sqlQry, null, Get_Trx());
log.Fine("'0' Inserted #" + no);
}
StringBuilder sql = null;
if (!isContainerApplicable)
{
sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " s.qtyOnHand, p.M_AttributeSet_ID, 0 AS M_ProductContainer_ID "
+ "FROM M_Product p"
+ " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)"
+ " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID()
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
else
{
sql = new StringBuilder(
"SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,"
+ " NVL(SUM(s.Qty) , 0) AS qtyOnHand , p.M_AttributeSet_ID, s.M_ProductContainer_ID "
+ "FROM M_Product p"
+ " INNER JOIN M_ContainerStorage s ON (s.M_Product_ID=p.M_Product_ID)"
+ " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) "
+ "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID()
+ " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
}
//
if (_m_Locator_ID != 0)
{
sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID);
}
//
if (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID))
{
sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") ");
}
//
if (_m_Product_Category_ID != 0)
{
sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID);
}
// Do not overwrite existing records
if (!_deleteOld)
{
sql.Append(" AND NOT EXISTS (SELECT * FROM M_MovementLine il "
+ "WHERE il.M_Movement_ID=" + _m_Movement_ID
+ " AND il.M_Product_ID=s.M_Product_ID"
+ " AND il.M_Locator_ID=s.M_Locator_ID"
+ " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0)");
if (!isContainerApplicable)
{
sql.Append(@" ) ");
}
else
{
sql.Append(@" AND COALESCE(il.M_ProductContainer_ID,0)=COALESCE(s.M_ProductContainer_ID,0) ) ");
}
}
//
if (!isContainerApplicable)
{
sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product
}
else
{
sql.Append(@" GROUP BY s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID, s.M_ProductContainer_ID, s.Qty
ORDER BY s.M_Locator_ID, s.M_Product_ID, s.Qty DESC, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID,s.M_ProductContainer_ID"); // Locator/Product
}
//
int count = 0;
IDataReader idr = null;
DataTable dt = null;
MProduct product = null;
try
{
idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// while (idr.Read())
{
int M_Product_ID = Util.GetValueOfInt(dr[0]);
product = MProduct.Get(GetCtx(), M_Product_ID);
int M_Locator_ID = Util.GetValueOfInt(dr[1]);
int M_AttributeSetInstance_ID = Util.GetValueOfInt(dr[2]);
Decimal qtyOnHand = Util.GetValueOfDecimal(dr[3]);
//if (qtyOnHand == null) commented by manjot Because Decimal is Never equals to Null
// qtyOnHand = Env.ZERO;
int M_AttributeSet_ID = Util.GetValueOfInt(dr[4]);
//container
int container_Id = Util.GetValueOfInt(dr[5]);
//
int compare = qtyOnHand.CompareTo(Env.ZERO);
if (_qtyRange == null ||
(_qtyRange.Equals(">") && compare > 0) ||
(_qtyRange.Equals("<") && compare < 0) ||
(_qtyRange.Equals("=") && compare == 0) ||
(_qtyRange.Equals("N") && compare != 0))
{
//Save data on Movement Line
_line = new MMovementLine(GetCtx(), 0, Get_Trx());
_line.SetAD_Client_ID(_movement.GetAD_Client_ID());
_line.SetAD_Org_ID(_movement.GetAD_Org_ID());
_line.SetM_Movement_ID(_m_Movement_ID);
_line.SetM_Locator_ID(_m_Locator_ID);
_line.SetM_LocatorTo_ID(_m_LocatorTo_ID);
_line.SetMovementQty(qtyOnHand);
_line.SetProcessed(false);
_line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
_line.SetM_Product_ID(M_Product_ID);
if (isContainerApplicable && _line.Get_ColumnIndex("M_ProductContainer_ID") > 0)
{
_line.SetM_ProductContainer_ID(container_Id);
}
if (_line.Get_ColumnIndex("C_UOM_ID") > 0 && product != null)
{
_line.SetC_UOM_ID(product.GetC_UOM_ID());
}
if (_line.Get_ColumnIndex("QtyEntered") > 0)
{
_line.SetQtyEntered(qtyOnHand);
}
if (!_line.Save())
{
return(GetRetrievedError(_line, "Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID));
//log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID);
}
else
{
count = count + 1;
}
}
}
//idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
//
return("@M_MovementLine_ID@ - #" + count);
}
// Added by mohit to get product UOM- 12 June 2018
/// <summary>
/// Get product UOM
/// </summary>
/// <param name="ctx"></param>
/// <param name="field"></param>
/// <returns></returns>
public string GetproductUOM(Ctx ctx, string field)
{
MProduct Product = new MProduct(ctx, Util.GetValueOfInt(field), null);
return(Product.GetC_UOM_ID().ToString());
}