} // prepareTable
/// <summary>
/// Fill Table
/// </summary>
/// <param name="wh">warehouse</param>
private void FillTable(MWarehouse wh)
{
String sql = "INSERT INTO T_Replenish "
+ "(AD_PInstance_ID, M_Warehouse_ID, M_Product_ID, AD_Client_ID, AD_Org_ID,"
+ " ReplenishType, Level_Min, Level_Max, QtyOnHand,QtyReserved,QtyOrdered,"
+ " C_BPartner_ID, Order_Min, Order_Pack, QtyToOrder, ReplenishmentCreate) "
+ "SELECT " + GetAD_PInstance_ID()
+ ", r.M_Warehouse_ID, r.M_Product_ID, r.AD_Client_ID, r.AD_Org_ID,"
+ " r.ReplenishType, r.Level_Min, r.Level_Max, 0,0,0,"
+ " po.C_BPartner_ID, po.Order_Min, po.Order_Pack, 0, ";
if (_ReplenishmentCreate == null)
{
sql += "null";
}
else
{
sql += "'" + _ReplenishmentCreate + "'";
}
sql += " FROM M_Replenish r"
+ " INNER JOIN M_Product_PO po ON (r.M_Product_ID=po.M_Product_ID) "
+ "WHERE po.IsCurrentVendor='Y'" // Only Current Vendor
+ " AND r.ReplenishType<>'0'"
+ " AND po.IsActive='Y' AND r.IsActive='Y'"
+ " AND r.M_Warehouse_ID=" + _M_Warehouse_ID;
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
sql += " AND po.C_BPartner_ID=" + _C_BPartner_ID;
}
int no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
log.Finest(sql);
log.Fine("Insert (1) #" + no);
if (_C_BPartner_ID == 0 || _C_BPartner_ID == -1)
{
sql = "INSERT INTO T_Replenish "
+ "(AD_PInstance_ID, M_Warehouse_ID, M_Product_ID, AD_Client_ID, AD_Org_ID,"
+ " ReplenishType, Level_Min, Level_Max,"
+ " C_BPartner_ID, Order_Min, Order_Pack, QtyToOrder, ReplenishmentCreate) "
+ "SELECT " + GetAD_PInstance_ID()
+ ", r.M_Warehouse_ID, r.M_Product_ID, r.AD_Client_ID, r.AD_Org_ID,"
+ " r.ReplenishType, r.Level_Min, r.Level_Max,"
//jz + " null, 1, 1, 0, ";
+ DataBase.DB.NULL("I", Types.VARCHAR)
+ " , 1, 1, 0, ";
if (_ReplenishmentCreate == null)
{
sql += "null";
}
else
{
sql += "'" + _ReplenishmentCreate + "'";
}
sql += " FROM M_Replenish r "
+ "WHERE r.ReplenishType<>'0' AND r.IsActive='Y'"
+ " AND r.M_Warehouse_ID=" + _M_Warehouse_ID
+ " AND NOT EXISTS (SELECT * FROM T_Replenish t "
+ "WHERE r.M_Product_ID=t.M_Product_ID"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID() + ")";
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
log.Fine("Insert (BP) #" + no);
}
sql = "UPDATE T_Replenish t SET "
+ "QtyOnHand = (SELECT COALESCE(SUM(QtyOnHand),0) FROM M_Storage s, M_Locator l WHERE t.M_Product_ID=s.M_Product_ID"
+ " AND l.M_Locator_ID=s.M_Locator_ID AND l.M_Warehouse_ID=t.M_Warehouse_ID),"
+ "QtyReserved = (SELECT COALESCE(SUM(QtyReserved),0) FROM M_Storage s, M_Locator l WHERE t.M_Product_ID=s.M_Product_ID"
+ " AND l.M_Locator_ID=s.M_Locator_ID AND l.M_Warehouse_ID=t.M_Warehouse_ID),"
+ "QtyOrdered = (SELECT COALESCE(SUM(QtyOrdered),0) FROM M_Storage s, M_Locator l WHERE t.M_Product_ID=s.M_Product_ID"
+ " AND l.M_Locator_ID=s.M_Locator_ID AND l.M_Warehouse_ID=t.M_Warehouse_ID)";
if (_C_DocType_ID != 0 && _C_DocType_ID != -1)
{
sql += ", C_DocType_ID=" + _C_DocType_ID;
}
sql += " WHERE AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Update #" + no);
}
// Delete inactive products and replenishments
sql = "DELETE FROM T_Replenish r "
+ "WHERE (EXISTS (SELECT * FROM M_Product p "
+ "WHERE p.M_Product_ID=r.M_Product_ID AND p.IsActive='N')"
+ " OR EXISTS (SELECT * FROM M_Replenish rr "
+ " WHERE rr.M_Product_ID=r.M_Product_ID AND rr.M_Warehouse_ID=r.M_Warehouse_ID AND rr.IsActive='N'))"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Delete Inactive=" + no);
}
// Ensure Data consistency
sql = "UPDATE T_Replenish SET QtyOnHand = 0 WHERE QtyOnHand IS NULL";
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
sql = "UPDATE T_Replenish SET QtyReserved = 0 WHERE QtyReserved IS NULL";
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
sql = "UPDATE T_Replenish SET QtyOrdered = 0 WHERE QtyOrdered IS NULL";
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
// Set Minimum / Maximum Maintain Level
// X_M_Replenish.REPLENISHTYPE_ReorderBelowMinimumLevel
sql = "UPDATE T_Replenish"
+ " SET QtyToOrder = Level_Min - QtyOnHand + QtyReserved - QtyOrdered "
+ "WHERE ReplenishType='1'"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Update Type-1=" + no);
}
//
// X_M_Replenish.REPLENISHTYPE_MaintainMaximumLevel
sql = "UPDATE T_Replenish"
+ " SET QtyToOrder = Level_Max - QtyOnHand + QtyReserved - QtyOrdered "
+ "WHERE ReplenishType='2'"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Update Type-2=" + no);
} //dtd
// Delete rows where nothing to order
sql = "DELETE FROM T_Replenish "
+ "WHERE QtyToOrder < 1"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Delete No QtyToOrder=" + no);
}
//dtd// Minimum Order Quantity
//sql = "UPDATE T_Replenish"
// + " SET QtyToOrder = Order_Min "
// + "WHERE QtyToOrder < Order_Min"
// + " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
//no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
//if (no != 0)
//{
// log.Fine("Set MinOrderQty=" + no);
//}
// Even dividable by Pack
sql = "UPDATE T_Replenish"
+ " SET QtyToOrder = QtyToOrder - MOD(QtyToOrder, Order_Pack) + Order_Pack "
+ "WHERE MOD(QtyToOrder, Order_Pack) <> 0"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Set OrderPackQty=" + no);
}
// Source from other warehouse
if (wh.GetM_WarehouseSource_ID() != 0)
{
sql = "UPDATE T_Replenish"
+ " SET M_WarehouseSource_ID=" + wh.GetM_WarehouseSource_ID()
+ " WHERE AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Set Warehouse Source Warehouse=" + no);
}
}
// Replenishment on Product level overwrites
sql = "UPDATE T_Replenish "
+ "SET M_WarehouseSource_ID=(SELECT M_WarehouseSource_ID FROM M_Replenish r "
+ "WHERE r.M_Product_ID=T_Replenish.M_Product_ID"
+ " AND r.M_Warehouse_ID=" + _M_Warehouse_ID + ")"
+ "WHERE AD_PInstance_ID=" + GetAD_PInstance_ID()
+ " AND EXISTS (SELECT * FROM M_Replenish r "
+ "WHERE r.M_Product_ID=T_Replenish.M_Product_ID"
+ " AND r.M_Warehouse_ID=" + _M_Warehouse_ID
+ " AND r.M_WarehouseSource_ID > 0)";
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Set Product Source Warehouse=" + no);
}
// Check Source Warehouse
sql = "UPDATE T_Replenish"
+ " SET M_WarehouseSource_ID = NULL "
+ "WHERE M_Warehouse_ID=M_WarehouseSource_ID"
+ " AND AD_PInstance_ID=" + GetAD_PInstance_ID();
no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName());
if (no != 0)
{
log.Fine("Set same Source Warehouse=" + no);
}
// Custom Replenishment
String className = wh.GetReplenishmentClass();
if (className == null || className.Length == 0)
{
return;
}
// Get Replenishment Class
ReplenishInterface custom = null;
try
{
//Class<?> clazz = Class.forName(className);
Type clazz = Type.GetType(className);
custom = (ReplenishInterface)Activator.CreateInstance(clazz);//.newInstance();
}
catch (Exception e)
{
throw new Exception("No custom Replenishment class "
+ className + " - " + e.ToString());
}
X_T_Replenish[] replenishs = GetReplenish("ReplenishType='9'");
for (int i = 0; i < replenishs.Length; i++)
{
X_T_Replenish replenish = replenishs[i];
if (replenish.GetReplenishType().Equals(X_T_Replenish.REPLENISHTYPE_Custom))
{
Decimal?qto = null;
try
{
qto = custom.GetQtyToOrder(wh, replenish);
}
catch (Exception e)
{
log.Log(Level.SEVERE, custom.ToString(), e);
}
if (qto == null)
{
qto = Env.ZERO;
}
replenish.SetQtyToOrder(qto);
replenish.Save();
}
}
// fillTable
}