Examples with ReplenishInterface org.compiere.util.ReplenishInterface used on opensource projects

Search in sources :

Example 1 with ReplenishInterface

use of org.compiere.util.ReplenishInterface 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);
}
Also used : AdempiereUserError(org.compiere.util.AdempiereUserError) ReplenishInterface(org.compiere.util.ReplenishInterface) X_T_Replenish(org.compiere.model.X_T_Replenish) BigDecimal(java.math.BigDecimal)

Example 2 with ReplenishInterface

use of org.compiere.util.ReplenishInterface in project adempiere by adempiere.

the class ReplenishReport 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) " + //	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;
    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 " + "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.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);
    //	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.saveEx();
            }
        }
    }
    //	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);
}
Also used : AdempiereUserError(org.compiere.util.AdempiereUserError) ReplenishInterface(org.compiere.util.ReplenishInterface) X_T_Replenish(org.compiere.model.X_T_Replenish) BigDecimal(java.math.BigDecimal)

Aggregations

BigDecimal (java.math.BigDecimal)2 X_T_Replenish (org.compiere.model.X_T_Replenish)2 AdempiereUserError (org.compiere.util.AdempiereUserError)2 ReplenishInterface (org.compiere.util.ReplenishInterface)2
About Me
UseOf

Java Tutorials :

Simple Java RabbitMQ Example with Spring
Simple Springboot JPA Eclipeslink Example
Simple SpringBoot JPA Hibernate Example
Jackson JSON @JsonIgnore and @JsonIgnoreProperties Examples
Java Infinite recursion (StackOverflowError) Jackson solutions
Simple Java Jackson Serialize Objects to JSON and convert them back To Object
ElasticSearch 5 - Upload files using Java API in binary field Example
Java JAXB marshall unmarshall Examples from String and Stream
Java 8 forEach List and Map examples
ElasticSearch 5 Java API SearchRequestBuilder examples
Java ElasticSearch 5 examples with node index and mapping - running local
Convert String to int or Integer Java 8
Java 9 in action - JShell - Ubuntu
Java - removing invalid characters from a file name
Hello world!? or Hello Tutorial