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

Example 6 with MInventory

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

the class ImportInventory method doIt.

//	prepare
/**
	 *  Perform process.
	 *  @return Message
	 *  @throws Exception
	 */
protected String doIt() throws java.lang.Exception {
    log.info("M_Locator_ID=" + p_M_Locator_ID + ",MovementDate=" + p_MovementDate);
    if (p_UpdateCosting) {
        if (p_C_AcctSchema_ID <= 0) {
            throw new IllegalArgumentException("Accounting Schema required!");
        }
        if (p_M_CostType_ID <= 0) {
            throw new IllegalArgumentException("Cost Type required!");
        }
        if (p_M_CostElement_ID <= 0) {
            throw new IllegalArgumentException("Cost Element required!");
        }
        if (p_AD_OrgTrx_ID < 0) {
            throw new IllegalArgumentException("AD_OrgTrx required!");
        }
        acctSchema = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID, get_TrxName());
    }
    StringBuffer sql = null;
    int no = 0;
    String clientCheck = " AND AD_Client_ID=" + p_AD_Client_ID;
    //	Delete Old Imported
    if (p_DeleteOldImported) {
        sql = new StringBuffer("DELETE I_Inventory " + "WHERE I_IsImported='Y'").append(clientCheck);
        no = DB.executeUpdate(sql.toString(), get_TrxName());
        log.fine("Delete Old Imported=" + no);
    }
    //	Set Client, Org, Location, IsActive, Created/Updated
    sql = new StringBuffer("UPDATE I_Inventory " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append(p_AD_Client_ID).append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").append(p_AD_Org_ID).append("),");
    if (p_MovementDate != null)
        sql.append(" MovementDate = COALESCE (MovementDate,").append(DB.TO_DATE(p_MovementDate)).append("),");
    sql.append(" IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = ' '," + //	reset
    " M_Warehouse_ID = NULL," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.info("Reset=" + no);
    sql = new StringBuffer("UPDATE I_Inventory o " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("Invalid Org=" + no);
    //	Location
    sql = new StringBuffer("UPDATE I_Inventory i " + "SET M_Locator_ID=(SELECT MAX(M_Locator_ID) FROM M_Locator l" + " WHERE i.LocatorValue=l.Value AND i.AD_Client_ID=l.AD_Client_ID) " + "WHERE M_Locator_ID IS NULL AND LocatorValue IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Locator from Value =" + no);
    sql = new StringBuffer("UPDATE I_Inventory i " + "SET M_Locator_ID=(SELECT MAX(M_Locator_ID) FROM M_Locator l" + " WHERE i.X=l.X AND i.Y=l.Y AND i.Z=l.Z AND i.AD_Client_ID=l.AD_Client_ID) " + "WHERE M_Locator_ID IS NULL AND X IS NOT NULL AND Y IS NOT NULL AND Z IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Locator from X,Y,Z =" + no);
    if (p_M_Locator_ID != 0) {
        sql = new StringBuffer("UPDATE I_Inventory " + "SET M_Locator_ID = ").append(p_M_Locator_ID).append(" WHERE M_Locator_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
        no = DB.executeUpdate(sql.toString(), get_TrxName());
        log.fine("Set Locator from Parameter=" + no);
    }
    sql = new StringBuffer("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Location, ' " + "WHERE M_Locator_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("No Location=" + no);
    //	Set M_Warehouse_ID
    sql = new StringBuffer("UPDATE I_Inventory i " + "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Locator l WHERE i.M_Locator_ID=l.M_Locator_ID) " + "WHERE M_Locator_ID IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Warehouse from Locator =" + no);
    sql = new StringBuffer("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Warehouse, ' " + "WHERE M_Warehouse_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("No Warehouse=" + no);
    //	Product
    sql = new StringBuffer("UPDATE I_Inventory i " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND Value IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Product from Value=" + no);
    sql = new StringBuffer("UPDATE I_Inventory i " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Product from UPC=" + no);
    sql = new StringBuffer("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Product, ' " + "WHERE M_Product_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("No Product=" + no);
    //	No QtyCount
    sql = new StringBuffer("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Qty Count, ' " + "WHERE QtyCount IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("No QtyCount=" + no);
    commitEx();
    /*********************************************************************/
    MInventory inventory = null;
    int noInsert = 0;
    int noInsertLine = 0;
    //	Go through Inventory Records
    sql = new StringBuffer("SELECT * FROM I_Inventory " + "WHERE I_IsImported='N'").append(clientCheck).append(" ORDER BY M_Warehouse_ID, TRUNC(MovementDate), I_Inventory_ID");
    try {
        PreparedStatement preparedStatement = DB.prepareStatement(sql.toString(), get_TrxName());
        ResultSet resultSet = preparedStatement.executeQuery();
        //
        int warehouseId = -1;
        Timestamp lastMovementDate = null;
        while (resultSet.next()) {
            X_I_Inventory importInventory = new X_I_Inventory(getCtx(), resultSet, get_TrxName());
            Timestamp movementDate = TimeUtil.getDay(importInventory.getMovementDate());
            if (inventory == null || importInventory.getM_Warehouse_ID() != warehouseId || !movementDate.equals(lastMovementDate)) {
                inventory = new MInventory(getCtx(), 0, get_TrxName());
                inventory.setClientOrg(importInventory.getAD_Client_ID(), importInventory.getAD_Org_ID());
                inventory.setDescription("I " + importInventory.getM_Warehouse_ID() + " " + movementDate);
                inventory.setM_Warehouse_ID(importInventory.getM_Warehouse_ID());
                inventory.setMovementDate(movementDate);
                //
                if (!inventory.save()) {
                    log.log(Level.SEVERE, "Inventory not saved");
                    break;
                }
                warehouseId = importInventory.getM_Warehouse_ID();
                lastMovementDate = movementDate;
                noInsert++;
            }
            //	Line
            int attributeSetInstanceId = 0;
            if (importInventory.getLot() != null || importInventory.getSerNo() != null) {
                MProduct product = MProduct.get(getCtx(), importInventory.getM_Product_ID());
                if (product.isInstanceAttribute()) {
                    MAttributeSet attributeSet = product.getAttributeSet();
                    MAttributeSetInstance attributeSetInstance = new MAttributeSetInstance(getCtx(), 0, attributeSet.getM_AttributeSet_ID(), get_TrxName());
                    if (attributeSet.isLot() && importInventory.getLot() != null)
                        attributeSetInstance.setLot(importInventory.getLot(), importInventory.getM_Product_ID());
                    if (attributeSet.isSerNo() && importInventory.getSerNo() != null)
                        attributeSetInstance.setSerNo(importInventory.getSerNo());
                    attributeSetInstance.setDescription();
                    attributeSetInstance.saveEx();
                    attributeSetInstanceId = attributeSetInstance.getM_AttributeSetInstance_ID();
                }
            }
            MInventoryLine inventoryLine = new MInventoryLine(inventory, importInventory.getM_Locator_ID(), importInventory.getM_Product_ID(), attributeSetInstanceId, importInventory.getQtyBook(), importInventory.getQtyCount());
            inventoryLine.saveEx();
            importInventory.setI_IsImported(true);
            importInventory.setM_Inventory_ID(inventoryLine.getM_Inventory_ID());
            importInventory.setM_InventoryLine_ID(inventoryLine.getM_InventoryLine_ID());
            importInventory.setProcessed(true);
            importInventory.saveEx();
            noInsertLine++;
            //@Trifon update Product cost record if Update costing is enabled
            if (p_UpdateCosting) {
                inventoryLine.setCurrentCostPrice(importInventory.getCurrentCostPrice());
                inventoryLine.setCurrentCostPriceLL(importInventory.getCurrentCostPriceLL());
                inventoryLine.saveEx();
            }
        }
        resultSet.close();
        preparedStatement.close();
    } catch (Exception e) {
        log.log(Level.SEVERE, sql.toString(), e);
    }
    //	Set Error to indicator to not imported
    sql = new StringBuffer("UPDATE I_Inventory " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    addLog(0, null, new BigDecimal(no), "@Errors@");
    //
    addLog(0, null, new BigDecimal(noInsert), "@M_Inventory_ID@: @Inserted@");
    addLog(0, null, new BigDecimal(noInsertLine), "@M_InventoryLine_ID@: @Inserted@");
    return "";
}

Example 7 with MInventory

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

the class InventoryCountCreate method doIt.

//	prepare
/**
	 * 	Process
	 *	@return message
	 *	@throws Exception
	 */
protected String doIt() throws Exception {
    log.info("M_Inventory_ID=" + p_M_Inventory_ID + ", M_Locator_ID=" + p_M_Locator_ID + ", LocatorValue=" + p_LocatorValue + ", ProductValue=" + p_ProductValue + ", M_Product_Category_ID=" + p_M_Product_Category_ID + ", QtyRange=" + p_QtyRange + ", DeleteOld=" + p_DeleteOld);
    m_inventory = new MInventory(getCtx(), p_M_Inventory_ID, get_TrxName());
    if (m_inventory.get_ID() == 0)
        throw new AdempiereSystemError("Not found: M_Inventory_ID=" + p_M_Inventory_ID);
    if (m_inventory.isProcessed())
        throw new AdempiereSystemError("@M_Inventory_ID@ @Processed@");
    //
    if (p_DeleteOld) {
        //Added Line by armen
        String sql1 = "DELETE FROM M_InventoryLineMA ma WHERE EXISTS " + "(SELECT * FROM M_InventoryLine l WHERE l.M_InventoryLine_ID=ma.M_InventoryLine_ID" + " AND Processed='N' AND M_Inventory_ID=" + p_M_Inventory_ID + ")";
        int no1 = DB.executeUpdate(sql1, get_TrxName());
        log.fine("doIt - Deleted MA #" + no1);
        //End of Added Line
        String sql = "DELETE M_InventoryLine WHERE Processed='N' " + "AND M_Inventory_ID=" + p_M_Inventory_ID;
        int no = DB.executeUpdate(sql, get_TrxName());
        log.fine("doIt - Deleted #" + no);
    }
    //	Create Null Storage records
    if (p_QtyRange != null && p_QtyRange.equals("=")) {
        String sql = "INSERT INTO M_Storage " + "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID," + " QtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) " + "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0," + " l.M_Locator_ID, p.M_Product_ID, 0," + " 0,0,0,null " + "FROM M_Locator l" + " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) " + "WHERE l.M_Warehouse_ID=" + m_inventory.getM_Warehouse_ID();
        if (p_M_Locator_ID != 0)
            sql += " AND l.M_Locator_ID=" + p_M_Locator_ID;
        sql += " AND l.IsDefault='Y'" + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'" + " AND NOT EXISTS (SELECT * FROM M_Storage s" + " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) " + "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID" + " AND s.M_Product_ID=p.M_Product_ID)";
        int no = DB.executeUpdate(sql, get_TrxName());
        log.fine("'0' Inserted #" + no);
    }
    StringBuffer sql = new StringBuffer("SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID," + " s.QtyOnHand, p.M_AttributeSet_ID " + "FROM M_Product p" + " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE l.M_Warehouse_ID=?" + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'");
    //
    if (p_M_Locator_ID != 0)
        sql.append(" AND s.M_Locator_ID=?");
    //
    if (p_LocatorValue != null && (p_LocatorValue.trim().length() == 0 || p_LocatorValue.equals("%")))
        p_LocatorValue = null;
    if (p_LocatorValue != null)
        sql.append(" AND UPPER(l.Value) LIKE ?");
    //
    if (p_ProductValue != null && (p_ProductValue.trim().length() == 0 || p_ProductValue.equals("%")))
        p_ProductValue = null;
    if (p_ProductValue != null)
        sql.append(" AND UPPER(p.Value) LIKE ?");
    //
    if (p_M_Product_Category_ID != 0)
        sql.append(" AND p.M_Product_Category_ID IN (" + getSubCategoryWhereClause(p_M_Product_Category_ID) + ")");
    //	Do not overwrite existing records
    if (!p_DeleteOld)
        sql.append(" AND NOT EXISTS (SELECT * FROM M_InventoryLine il " + "WHERE il.M_Inventory_ID=?" + " AND il.M_Product_ID=s.M_Product_ID" + " AND il.M_Locator_ID=s.M_Locator_ID" + " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0))");
    //	+ " AND il.M_AttributeSetInstance_ID=s.M_AttributeSetInstance_ID)");
    //
    //	Locator/Product
    sql.append(" ORDER BY l.Value, p.Value, s.QtyOnHand DESC");
    //
    int count = 0;
    PreparedStatement pstmt = null;
    try {
        pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
        int index = 1;
        pstmt.setInt(index++, m_inventory.getM_Warehouse_ID());
        if (p_M_Locator_ID != 0)
            pstmt.setInt(index++, p_M_Locator_ID);
        if (p_LocatorValue != null)
            pstmt.setString(index++, p_LocatorValue.toUpperCase());
        if (p_ProductValue != null)
            pstmt.setString(index++, p_ProductValue.toUpperCase());
        if (!p_DeleteOld)
            pstmt.setInt(index++, p_M_Inventory_ID);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            int M_Product_ID = rs.getInt(1);
            int M_Locator_ID = rs.getInt(2);
            int M_AttributeSetInstance_ID = rs.getInt(3);
            BigDecimal QtyOnHand = rs.getBigDecimal(4);
            if (QtyOnHand == null)
                QtyOnHand = Env.ZERO;
            int M_AttributeSet_ID = rs.getInt(5);
            //
            int compare = QtyOnHand.compareTo(Env.ZERO);
            if (p_QtyRange == null || (p_QtyRange.equals(">") && compare > 0) || (p_QtyRange.equals("<") && compare < 0) || (p_QtyRange.equals("=") && compare == 0) || (p_QtyRange.equals("N") && compare != 0)) {
                count += createInventoryLine(M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, QtyOnHand, M_AttributeSet_ID);
            }
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, sql.toString(), e);
    }
    try {
        if (pstmt != null)
            pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        pstmt = null;
    }
    //	Set Count to Zero
    if (p_InventoryCountSetZero) {
        String sql1 = "UPDATE M_InventoryLine l " + "SET QtyCount=0 " + "WHERE M_Inventory_ID=" + p_M_Inventory_ID;
        int no = DB.executeUpdate(sql1, get_TrxName());
        log.info("Set Cont to Zero=" + no);
    }
    //
    return "@M_InventoryLine_ID@ - #" + count;
}

Example 8 with MInventory

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

the class InventoryUtil method createInventory.

public static MInventory createInventory(MMDocument doc, String trxName) {
    Properties ctx = Env.getCtx();
    int AD_Org_ID = getFirst_Org_ID();
    MProduct product = getCreateProduct(doc);
    MLocator locator = getCreateLocator(AD_Org_ID, doc.LocatorValue, doc.LocatorValue);
    //
    MInventory inv = new MInventory(ctx, 0, trxName);
    inv.setAD_Org_ID(AD_Org_ID);
    //inv.setIsInternalUseInventory(true); // TODO: arhipac: teo_sarca - add this column to trunk
    inv.setMovementDate(doc.Date);
    inv.setM_Warehouse_ID(locator.getM_Warehouse_ID());
    setGeneratedTag(inv);
    inv.saveEx();
    //
    MInventoryLine line = new MInventoryLine(inv, locator.get_ID(), product.get_ID(), 0, null, null);
    line.setQtyInternalUse(doc.Qty);
    line.setC_Charge_ID(getCreateCharge("junit-charge").get_ID());
    line.saveEx();
    //
    doc.document = inv;
    processDocument(doc, MInventory.DOCACTION_Complete, MInventory.DOCSTATUS_Completed);
    if (!Util.isEmpty(doc.ASI)) {
        line.load(trxName);
        doc.scenario.registerASICode(doc.ASI, line.getM_AttributeSetInstance_ID(), line.getQtyInternalUse().signum() <= 0);
    }
    checkLineCosts(doc, line, doc.Qty.signum() >= 0);
    return inv;
}

Example 9 with MInventory

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

the class CostResult method createPhisicalInventory.

public MInventory createPhisicalInventory(Timestamp documentDate, BigDecimal qty, BigDecimal qtyBook) {
    int M_Locator_ID = 101;
    MInventory inventory = new MInventory(getCtx(), 0, trxName);
    inventory.setAD_Org_ID(Env.getAD_Org_ID(getCtx()));
    inventory.setM_Warehouse_ID(w.getM_Warehouse_ID());
    inventory.setC_DocType_ID((MDocType.getDocType(MDocType.DOCBASETYPE_MaterialPhysicalInventory)));
    inventory.setMovementDate(documentDate);
    inventory.saveEx();
    MInventoryLine inventoryLine = new MInventoryLine(getCtx(), 0, trxName);
    inventoryLine.setAD_Org_ID(Env.getAD_Org_ID(getCtx()));
    inventoryLine.setM_Inventory_ID(inventory.getM_Inventory_ID());
    inventoryLine.setM_Product_ID(product.getM_Product_ID());
    inventoryLine.setQtyBook(qtyBook);
    inventoryLine.setInventoryType(MInventoryLine.INVENTORYTYPE_InventoryDifference);
    inventoryLine.setQtyCount(qty);
    // Default HQ Locator
    inventoryLine.setM_Locator_ID(M_Locator_ID);
    inventoryLine.saveEx();
    inventory.processIt(DocAction.ACTION_Complete);
    inventory.saveEx();
    return inventory;
}

Example 10 with MInventory

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

the class InventoryUtil method createInventory.

public static MInventory createInventory(MMDocument doc, String trxName) {
    Properties ctx = Env.getCtx();
    int AD_Org_ID = getFirst_Org_ID();
    MProduct product = getCreateProduct(doc.ProductValue, null);
    MLocator locator = getCreateLocator(AD_Org_ID, doc.LocatorValue, doc.LocatorValue);
    //
    MInventory inv = new MInventory(ctx, 0, trxName);
    inv.setAD_Org_ID(AD_Org_ID);
    //inv.setIsInternalUseInventory(true);
    inv.setMovementDate(doc.Date);
    inv.setM_Warehouse_ID(locator.getM_Warehouse_ID());
    setGeneratedTag(inv);
    inv.saveEx();
    //
    MInventoryLine line = new MInventoryLine(inv, locator.get_ID(), product.get_ID(), 0, null, null);
    line.setQtyInternalUse(doc.Qty);
    line.setC_Charge_ID(getCreateCharge("junit-charge").get_ID());
    line.saveEx();
    //
    doc.document = inv;
    processDocument(doc, MInventory.DOCACTION_Complete, MInventory.DOCSTATUS_Completed);
    if (!Util.isEmpty(doc.ASI)) {
        line.load(trxName);
        doc.scenario.registerASICode(doc.ASI, line.getM_AttributeSetInstance_ID(), line.getQtyInternalUse().signum() <= 0);
    }
    return inv;
}