/** Create Inventory Movements */
private void createMovements() {
int noMoves = 0;
String info = "";
//
MClient client = null;
MMovement move = null;
int M_Warehouse_ID = 0;
int M_WarehouseSource_ID = 0;
MWarehouse whSource = null;
MWarehouse wh = null;
X_T_Replenish[] replenishs = getReplenish("M_WarehouseSource_ID IS NOT NULL");
for (int i = 0; i < replenishs.length; i++) {
X_T_Replenish replenish = replenishs[i];
if (whSource == null
|| whSource.getM_WarehouseSource_ID() != replenish.getM_WarehouseSource_ID())
whSource = MWarehouse.get(getCtx(), replenish.getM_WarehouseSource_ID());
if (wh == null || wh.getM_Warehouse_ID() != replenish.getM_Warehouse_ID())
wh = MWarehouse.get(getCtx(), replenish.getM_Warehouse_ID());
if (client == null || client.getAD_Client_ID() != whSource.getAD_Client_ID())
client = MClient.get(getCtx(), whSource.getAD_Client_ID());
//
if (move == null
|| M_WarehouseSource_ID != replenish.getM_WarehouseSource_ID()
|| M_Warehouse_ID != replenish.getM_Warehouse_ID()) {
M_WarehouseSource_ID = replenish.getM_WarehouseSource_ID();
M_Warehouse_ID = replenish.getM_Warehouse_ID();
move = new MMovement(getCtx(), 0, get_TrxName());
move.setC_DocType_ID(p_C_DocType_ID);
move.setDescription(
Msg.getMsg(getCtx(), "Replenishment")
+ ": "
+ whSource.getName()
+ "->"
+ wh.getName());
// Set Org
move.setAD_Org_ID(whSource.getAD_Org_ID());
if (!move.save()) return;
log.debug(move.toString());
noMoves++;
info += " - " + move.getDocumentNo();
}
// To
int M_LocatorTo_ID = wh.getDefaultLocator().getM_Locator_ID();
// From: Look-up Storage
MProduct product = MProduct.get(getCtx(), replenish.getM_Product_ID());
String MMPolicy = product.getMMPolicy();
MStorage[] storages =
MStorage.getWarehouse(
getCtx(),
whSource.getM_Warehouse_ID(),
replenish.getM_Product_ID(),
0,
0,
true,
null,
MClient.MMPOLICY_FiFo.equals(MMPolicy),
get_TrxName());
//
BigDecimal target = replenish.getQtyToOrder();
for (int j = 0; j < storages.length; j++) {
MStorage storage = storages[j];
if (storage.getQtyOnHand().signum() <= 0) continue;
BigDecimal moveQty = target;
if (storage.getQtyOnHand().compareTo(moveQty) < 0) moveQty = storage.getQtyOnHand();
//
MMovementLine line = new MMovementLine(move);
line.setM_Product_ID(replenish.getM_Product_ID());
line.setMovementQty(moveQty);
if (replenish.getQtyToOrder().compareTo(moveQty) != 0)
line.setDescription("Total: " + replenish.getQtyToOrder());
line.setM_Locator_ID(storage.getM_Locator_ID()); // from
line.setM_AttributeSetInstance_ID(storage.getM_AttributeSetInstance_ID());
line.setM_LocatorTo_ID(M_LocatorTo_ID); // to
line.setM_AttributeSetInstanceTo_ID(storage.getM_AttributeSetInstance_ID());
line.save();
//
target = target.subtract(moveQty);
if (target.signum() == 0) break;
}
}
if (replenishs.length == 0) {
m_info = "No Source Warehouse";
log.warn(m_info);
} else {
m_info = "#" + noMoves + info;
log.info(m_info);
}
} // createRequisition
/**
* Fill Table
*
* @param wh warehouse
*/
private void fillTable(MWarehouse wh) throws Exception {
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,"
+ " C_BPartner_ID, Order_Min, Order_Pack, QtyToOrder, ReplenishmentCreate,"
+ " C_Calendar_ID, C_Period_ID, TimeToMarket)"
+ "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,"
+ " po.C_BPartner_ID, po.Order_Min, po.Order_Pack, 0, ";
if (p_ReplenishmentCreate == null) sql += "null";
else sql += "'" + p_ReplenishmentCreate + "'";
sql += ", r.C_Calendar_ID, r.C_Period_ID, r.TimeToMarket";
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="
+ p_M_Warehouse_ID;
if (p_C_BPartner_ID != 0) sql += " AND po.C_BPartner_ID=" + p_C_BPartner_ID;
int no = DB.executeUpdate(sql, get_TrxName());
log.trace(sql);
log.debug("Insert (1) #" + no);
if (p_C_BPartner_ID == 0) {
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,"
+ " C_Calendar_ID, C_Period_ID, TimeToMarket)"
+ "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, 1, 1, 0, ";
if (p_ReplenishmentCreate == null) sql += "null";
else sql += "'" + p_ReplenishmentCreate + "'";
sql += ", r.C_Calendar_ID, r.C_Period_ID, r.TimeToMarket";
sql +=
" FROM M_Replenish r "
+ "WHERE r.ReplenishType<>'0' AND r.IsActive='Y'"
+ " AND r.M_Warehouse_ID="
+ p_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 = DB.executeUpdate(sql, get_TrxName());
log.debug("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 (p_C_DocType_ID != 0) sql += ", C_DocType_ID=" + p_C_DocType_ID;
sql += " WHERE AD_PInstance_ID=" + getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("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.IsActive='N'))"
+ " AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("Deleted Inactive=" + no);
// Ensure Data consistency
sql = "UPDATE T_Replenish SET QtyOnHand = 0 WHERE QtyOnHand IS NULL";
no = DB.executeUpdate(sql, get_TrxName());
sql = "UPDATE T_Replenish SET QtyReserved = 0 WHERE QtyReserved IS NULL";
no = DB.executeUpdate(sql, get_TrxName());
sql = "UPDATE T_Replenish SET QtyOrdered = 0 WHERE QtyOrdered IS NULL";
no = DB.executeUpdate(sql, get_TrxName());
// Set Minimum / Maximum Maintain Level
// X_M_Replenish.REPLENISHTYPE_ReorderBelowMinimumLevel
sql =
"UPDATE T_Replenish"
+ " SET QtyToOrder = CASE WHEN QtyOnHand - QtyReserved + QtyOrdered <= Level_Min "
+ " THEN Level_Max - QtyOnHand + QtyReserved - QtyOrdered "
+ " ELSE 0 END "
+ "WHERE ReplenishType='1'"
+ " AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("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 = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("Update Type-2=" + no);
// Minimum Order Quantity
sql =
"UPDATE T_Replenish"
+ " SET QtyToOrder = Order_Min "
+ "WHERE QtyToOrder < Order_Min"
+ " AND QtyToOrder > 0"
+ " AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("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 QtyToOrder > 0"
+ " AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("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 = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("Set 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 = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("Set same Source Warehouse=" + no);
// Custom Replenishment
String className = wh.getReplenishmentClass();
if (className != null && className.length() > 0) {
// Get Replenishment Class
ReplenishInterface custom = null;
try {
Class<?> clazz = Class.forName(className);
custom = (ReplenishInterface) clazz.newInstance();
} catch (Exception e) {
throw new AdempiereUserError(
"No custom Replenishment class " + className + " - " + e.toString());
}
excecReplenishInterface(wh, custom, X_M_Replenish.REPLENISHTYPE_Custom);
}
// metas
// X_M_Replenish.REPLENISHTYPE_EnsureFutureCapacity
final ReplenishInterface replenishForFutureCapacity = new ReplenishForFutureQtyServiceInvoker();
excecReplenishInterface(
wh, replenishForFutureCapacity, Constants.REPLENISHTYPE_EnsureFutureQty);
// metas end
// Delete rows where nothing to order
sql =
"DELETE FROM T_Replenish "
+ "WHERE QtyToOrder < 1"
+ " AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.debug("Deleted No QtyToOrder=" + no);
} // fillTable
