Examples with X_T_Replenish org.compiere.model.X_T_Replenish used on opensource projects

Example 11 with X_T_Replenish

use of org.compiere.model.X_T_Replenish 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);
}

Example 12 with X_T_Replenish

use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.

the class ReplenishReportProduction method createMovements.

//	createRequisition
/**
	 * 	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 AND C_BPartner_ID > 0");
    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.fine(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());
            //	from
            line.setM_Locator_ID(storage.getM_Locator_ID());
            line.setM_AttributeSetInstance_ID(storage.getM_AttributeSetInstance_ID());
            //	to
            line.setM_LocatorTo_ID(M_LocatorTo_ID);
            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.warning(m_info);
    } else {
        m_info = "#" + noMoves + info;
        log.info(m_info);
    }
}

Example 13 with X_T_Replenish

use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.

the class ReplenishReportProduction method getReplenish.

//	createRequisition
/**
	 * 	Get Replenish Records
	 *	@return replenish
	 */
private X_T_Replenish[] getReplenish(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;
}

Example 14 with X_T_Replenish

use of org.compiere.model.X_T_Replenish in project adempiere by adempiere.

the class ReplenishReportProduction method createDO.

//	Create Inventory Movements
/**
	 * 	Create Distribution Order
	 */
private void createDO() throws Exception {
    int noMoves = 0;
    String info = "";
    //
    MClient client = null;
    MDDOrder order = 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 (X_T_Replenish replenish : replenishs) {
        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 (order == 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();
            order = new MDDOrder(getCtx(), 0, get_TrxName());
            order.setC_DocType_ID(p_C_DocType_ID);
            order.setDescription(Msg.getMsg(getCtx(), "Replenishment") + ": " + whSource.getName() + "->" + wh.getName());
            //	Set Org
            order.setAD_Org_ID(whSource.getAD_Org_ID());
            // Set Org Trx
            MOrg orgTrx = MOrg.get(getCtx(), wh.getAD_Org_ID());
            order.setAD_OrgTrx_ID(orgTrx.getAD_Org_ID());
            int C_BPartner_ID = orgTrx.getLinkedC_BPartner_ID(get_TrxName());
            if (C_BPartner_ID == 0)
                throw new AdempiereUserError(Msg.translate(getCtx(), "C_BPartner_ID") + " @FillMandatory@ ");
            MBPartner bp = new MBPartner(getCtx(), C_BPartner_ID, get_TrxName());
            // Set BPartner Link to Org
            order.setBPartner(bp);
            order.setDateOrdered(new Timestamp(System.currentTimeMillis()));
            //order.setDatePromised(DatePromised);
            order.setDeliveryRule(MDDOrder.DELIVERYRULE_Availability);
            order.setDeliveryViaRule(MDDOrder.DELIVERYVIARULE_Delivery);
            order.setPriorityRule(MDDOrder.PRIORITYRULE_Medium);
            order.setIsInDispute(false);
            order.setIsApproved(false);
            order.setIsDropShip(false);
            order.setIsDelivered(false);
            order.setIsInTransit(false);
            order.setIsPrinted(false);
            order.setIsSelected(false);
            order.setIsSOTrx(false);
            // Warehouse in Transit
            MWarehouse[] whsInTransit = MWarehouse.getForOrg(getCtx(), whSource.getAD_Org_ID());
            for (MWarehouse whInTransit : whsInTransit) {
                if (whInTransit.isInTransit())
                    order.setM_Warehouse_ID(whInTransit.getM_Warehouse_ID());
            }
            if (order.getM_Warehouse_ID() == 0)
                throw new AdempiereUserError("Warehouse inTransit is @FillMandatory@ ");
            if (!order.save())
                return;
            log.fine(order.toString());
            noMoves++;
            info += " - " + order.getDocumentNo();
        }
        //	To
        int M_LocatorTo_ID = wh.getDefaultLocator().getM_Locator_ID();
        int M_Locator_ID = whSource.getDefaultLocator().getM_Locator_ID();
        if (M_LocatorTo_ID == 0 || M_Locator_ID == 0)
            throw new AdempiereUserError(Msg.translate(getCtx(), "M_Locator_ID") + " @FillMandatory@ ");
        //	From: Look-up Storage
        /*MProduct product = MProduct.get(getCtx(), replenish.getM_Product_ID());
			MProductCategory pc = MProductCategory.get(getCtx(), product.getM_Product_Category_ID());
			String MMPolicy = pc.getMMPolicy();
			if (MMPolicy == null || MMPolicy.length() == 0)
				MMPolicy = client.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();
				//
				MDDOrderLine line = new MDDOrderLine(order);
				line.setM_Product_ID(replenish.getM_Product_ID());
				line.setQtyEntered(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.setIsInvoiced(false);
				line.save();
				//
				target = target.subtract(moveQty);
				if (target.signum() == 0)
					break;
			}*/
        MDDOrderLine line = new MDDOrderLine(order);
        line.setM_Product_ID(replenish.getM_Product_ID());
        line.setQty(replenish.getQtyToOrder());
        if (replenish.getQtyToOrder().compareTo(replenish.getQtyToOrder()) != 0)
            line.setDescription("Total: " + replenish.getQtyToOrder());
        //	from
        line.setM_Locator_ID(M_Locator_ID);
        line.setM_AttributeSetInstance_ID(0);
        //	to
        line.setM_LocatorTo_ID(M_LocatorTo_ID);
        line.setM_AttributeSetInstanceTo_ID(0);
        line.setIsInvoiced(false);
        line.save();
    }
    if (replenishs.length == 0) {
        m_info = "No Source Warehouse";
        log.warning(m_info);
    } else {
        m_info = "#" + noMoves + info;
        log.info(m_info);
    }
}