use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.
the class ReplenishReportProduction method createRequisition.
// createPO
/**
* Create Requisition
*/
private void createRequisition() {
int noReqs = 0;
String info = "";
//
MRequisition requisition = null;
MWarehouse wh = null;
X_T_Replenish[] replenishs = getReplenish("M_WarehouseSource_ID IS NULL AND C_BPartner_ID > 0");
for (int i = 0; i < replenishs.length; i++) {
X_T_Replenish replenish = replenishs[i];
if (wh == null || wh.getM_Warehouse_ID() != replenish.getM_Warehouse_ID())
wh = MWarehouse.get(getCtx(), replenish.getM_Warehouse_ID());
//
if (requisition == null || requisition.getM_Warehouse_ID() != replenish.getM_Warehouse_ID()) {
requisition = new MRequisition(getCtx(), 0, get_TrxName());
requisition.setAD_User_ID(getAD_User_ID());
requisition.setC_DocType_ID(p_C_DocType_ID);
requisition.setDescription(Msg.getMsg(getCtx(), "Replenishment"));
// Set Org/WH
requisition.setAD_Org_ID(wh.getAD_Org_ID());
requisition.setM_Warehouse_ID(wh.getM_Warehouse_ID());
if (!requisition.save())
return;
log.fine(requisition.toString());
noReqs++;
info += " - " + requisition.getDocumentNo();
}
//
MRequisitionLine line = new MRequisitionLine(requisition);
line.setM_Product_ID(replenish.getM_Product_ID());
line.setC_BPartner_ID(replenish.getC_BPartner_ID());
line.setQty(replenish.getQtyToOrder());
line.setPrice();
line.save();
}
m_info = "#" + noReqs + info;
log.info(m_info);
}
use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.
the class ReplenishReportProduction method createPO.
// fillTable
/**
* Create PO's
*/
private void createPO() {
int noOrders = 0;
String info = "";
//
MOrder order = null;
MWarehouse wh = null;
X_T_Replenish[] replenishs = getReplenish("M_WarehouseSource_ID IS NULL AND C_BPartner_ID > 0");
for (int i = 0; i < replenishs.length; i++) {
X_T_Replenish replenish = replenishs[i];
if (wh == null || wh.getM_Warehouse_ID() != replenish.getM_Warehouse_ID())
wh = MWarehouse.get(getCtx(), replenish.getM_Warehouse_ID());
//
if (order == null || order.getC_BPartner_ID() != replenish.getC_BPartner_ID() || order.getM_Warehouse_ID() != replenish.getM_Warehouse_ID()) {
order = new MOrder(getCtx(), 0, get_TrxName());
order.setIsSOTrx(false);
order.setC_DocTypeTarget_ID(p_C_DocType_ID);
MBPartner bp = new MBPartner(getCtx(), replenish.getC_BPartner_ID(), get_TrxName());
order.setBPartner(bp);
order.setSalesRep_ID(getAD_User_ID());
order.setDescription(Msg.getMsg(getCtx(), "Replenishment"));
// Set Org/WH
order.setAD_Org_ID(wh.getAD_Org_ID());
order.setM_Warehouse_ID(wh.getM_Warehouse_ID());
if (!order.save())
return;
log.fine(order.toString());
noOrders++;
info += " - " + order.getDocumentNo();
}
MOrderLine line = new MOrderLine(order);
line.setM_Product_ID(replenish.getM_Product_ID());
line.setQty(replenish.getQtyToOrder());
line.setPrice();
line.save();
}
m_info = "#" + noOrders + info;
log.info(m_info);
}
use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.
the class ReplenishReportProduction method createProduction.
// create Distribution Order
/**
* Create Production
*/
private void createProduction() {
int noProds = 0;
String info = "";
//
MProduction production = null;
MWarehouse wh = null;
X_T_Replenish[] replenishs = getReplenish("M_WarehouseSource_ID IS NULL " + "AND EXISTS (SELECT * FROM M_Product p WHERE p.M_Product_ID=T_Replenish.M_Product_ID " + "AND p.IsBOM='Y' AND p.IsManufactured='Y') ");
for (int i = 0; i < replenishs.length; i++) {
X_T_Replenish replenish = replenishs[i];
if (wh == null || wh.getM_Warehouse_ID() != replenish.getM_Warehouse_ID())
wh = MWarehouse.get(getCtx(), replenish.getM_Warehouse_ID());
BigDecimal batchQty = null;
BigDecimal qtyToProduce = replenish.getQtyToOrder();
while (qtyToProduce.compareTo(Env.ZERO) > 0) {
BigDecimal qty = qtyToProduce;
if (batchQty != null && batchQty.compareTo(Env.ZERO) > 0 && qtyToProduce.compareTo(batchQty) > 0) {
qty = batchQty;
qtyToProduce = qtyToProduce.subtract(batchQty);
} else {
qtyToProduce = Env.ZERO;
}
production = new MProduction(getCtx(), 0, get_TrxName());
production.setDescription(Msg.getMsg(getCtx(), "Replenishment"));
// Set Org/WH
production.setAD_Org_ID(wh.getAD_Org_ID());
production.setM_Locator_ID(wh.getDefaultLocator().get_ID());
production.setM_Product_ID(replenish.getM_Product_ID());
production.setProductionQty(qty);
production.setMovementDate(Env.getContextAsDate(getCtx(), "#Date"));
production.saveEx();
// Process
production.setDocAction(DocAction.ACTION_Complete);
production.processIt(DocAction.ACTION_Complete);
production.saveEx(get_TrxName());
log.fine(production.toString());
noProds++;
info += " - " + production.getDocumentNo();
}
}
m_info = "#" + noProds + info;
log.info(m_info);
}
use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.
the class ReplenishReportProduction method fillTable.
// prepareTable
/**
* 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) " + "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 += " FROM M_Replenish r" + " INNER JOIN M_Product_PO po ON (r.M_Product_ID=po.M_Product_ID) " + " INNER JOIN M_Product p ON (p.M_Product_ID=po.M_Product_ID) " + // Only Current Vendor
"WHERE po.IsCurrentVendor='Y'" + " 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;
if (p_M_Product_Category_ID != 0)
sql += " AND p.M_Product_Category_ID=" + p_M_Product_Category_ID;
if (isKanban != null)
sql += " AND p.IsKanban = '" + isKanban + "' ";
int no = DB.executeUpdate(sql, get_TrxName());
log.finest(sql);
log.fine("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) " + "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 += " FROM M_Replenish r " + " INNER JOIN M_Product p ON (p.M_Product_ID=r.M_Product_ID) " + "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() + ")";
if (p_M_Product_Category_ID != 0)
sql += " AND p.M_Product_Category_ID=" + p_M_Product_Category_ID;
if (isKanban != null)
sql += " AND p.IsKanban = '" + isKanban + "' ";
no = DB.executeUpdate(sql, 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 (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.fine("Update #" + no);
// add production lines
sql = "UPDATE T_Replenish t SET " + "QtyReserved = QtyReserved - COALESCE((SELECT COALESCE(SUM(MovementQty),0) FROM M_ProductionLine p, M_Locator l WHERE t.M_Product_ID=p.M_Product_ID" + " AND l.M_Locator_ID=p.M_Locator_ID AND l.M_Warehouse_ID=t.M_Warehouse_ID AND MovementQty < 0 AND p.Processed = 'N'),0)," + "QtyOrdered = QtyOrdered + COALESCE((SELECT COALESCE(SUM(MovementQty),0) FROM M_ProductionLine p, M_Locator l WHERE t.M_Product_ID=p.M_Product_ID" + " AND l.M_Locator_ID=p.M_Locator_ID AND l.M_Warehouse_ID=t.M_Warehouse_ID AND MovementQty > 0 AND p.Processed = 'N'),0)";
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.fine("Update #" + no);
// Delete inactive products and replenishments
sql = "DELETE 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 rr.M_Warehouse_ID=" + p_M_Warehouse_ID + " ))" + " AND AD_PInstance_ID=" + getAD_PInstance_ID();
no = DB.executeUpdate(sql, 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 = 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.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 = DB.executeUpdate(sql, get_TrxName());
if (no != 0)
log.fine("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.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 QtyToOrder > 0" + " AND AD_PInstance_ID=" + getAD_PInstance_ID();
no = DB.executeUpdate(sql, 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 = DB.executeUpdate(sql, get_TrxName());
if (no != 0)
log.fine("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.fine("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());
}
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)) {
BigDecimal 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();
}
}
}
// Delete rows where nothing to order
sql = "DELETE T_Replenish " + "WHERE QtyToOrder < 1" + " AND AD_PInstance_ID=" + getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0)
log.fine("Delete No QtyToOrder=" + no);
}
use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.
the class ReplenishReport method getReplenishDO.
// getReplenish
/**
* Get Replenish Records
* @return replenish
*/
private X_T_Replenish[] getReplenishDO(String where) {
String sql = "SELECT * FROM T_Replenish " + "WHERE AD_PInstance_ID=? ";
if (where != null && where.length() > 0)
sql += " AND " + where;
sql += " ORDER BY M_Warehouse_ID, M_WarehouseSource_ID, C_BPartner_ID";
ArrayList<X_T_Replenish> list = new ArrayList<X_T_Replenish>();
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getAD_PInstance_ID());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) list.add(new X_T_Replenish(getCtx(), rs, get_TrxName()));
rs.close();
pstmt.close();
pstmt = null;
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
}
try {
if (pstmt != null)
pstmt.close();
pstmt = null;
} catch (Exception e) {
pstmt = null;
}
X_T_Replenish[] retValue = new X_T_Replenish[list.size()];
list.toArray(retValue);
return retValue;
}
Aggregations