@Override protected Object[] getParameters() { List<Object> params = new ArrayList<Object>(); params.add(Env.getAD_Client_ID(getCtx())); if (!Util.isEmpty(getOrgID(), true)) { params.add(getOrgID()); } if (!Util.isEmpty(getBpartnerID(), true)) { params.add(getBpartnerID()); } if (getDateFrom() != null) { params.add(getDateFrom()); } if (getDateTo() != null) { params.add(getDateTo()); } params.add(Env.getAD_Client_ID(getCtx())); if (!Util.isEmpty(getOrgID(), true)) { params.add(getOrgID()); } if (!Util.isEmpty(getBpartnerID(), true)) { params.add(getBpartnerID()); } if (getDateFrom() != null) { params.add(getDateFrom()); } if (getDateTo() != null) { params.add(getDateTo()); } return params.toArray(); }
/** Inicialización de datos adicionales al ProcessInfo */ private void initilizeProcessInfoAditionals() { if (!Util.isEmpty(m_pi.getTable_ID(), true) && !Util.isEmpty(m_pi.getRecord_ID(), true)) { Integer docTypeID = null; String documentNo = null; M_Table table = M_Table.get(Env.getCtx(), m_pi.getTable_ID()); PO po = table.getPO(m_pi.getRecord_ID(), table.get_TrxName()); // Verifico si en la tabla actual existe la columna C_DocTypeTarget_ID o // C_DocType_ID, si es así entonces obtengo su valor String docTypeColumnName = "C_DocTypeTarget_ID"; int docTypeIndex = po.get_ColumnIndex(docTypeColumnName); // Si no existe C_DocTypeTarget_ID, busco C_DocType_ID if (docTypeIndex == -1) { docTypeColumnName = "C_DocType_ID"; docTypeIndex = po.get_ColumnIndex(docTypeColumnName); } // Si tengo la columna con el tipo de documento, entonces obtengo su valor if (docTypeIndex > -1) { docTypeID = (Integer) po.get_Value(docTypeIndex); } // Verifico si en la tabla actual existe la columna DocumentNo, si es // asi obtengo su valor int documentNoIndex = po.get_ColumnIndex("DocumentNo"); if (documentNoIndex > -1) { documentNo = (String) po.get_Value(documentNoIndex); } // Si tengo el tipo de documento, entonces guardo el jasperDTO en el Process Info if (!Util.isEmpty(docTypeID, true)) { JasperReportDTO jasperDTO = m_pi.new JasperReportDTO(); jasperDTO.setDocTypeID(docTypeID); jasperDTO.setDocumentNo(documentNo); m_pi.setJasperReportDTO(jasperDTO); } } }
protected MOrder getOrder() { if (order == null) { MOrder the_order = null; // 1) Obtengo el pedido desde el parámetro if (getParameterValue("C_Order_ID") != null) { the_order = new MOrder(getCtx(), (Integer) getParameterValue("C_Order_ID"), get_TrxName()); } // 2) Si no existe, entonces lo busco por la factura if (the_order == null && getInvoice() != null && !Util.isEmpty(getInvoice().getC_Order_ID(), true)) { the_order = new MOrder(getCtx(), getInvoice().getC_Order_ID(), get_TrxName()); } setOrder(the_order); } return order; }
@Override public void addReportParameters(Properties ctx, Map<String, Object> params) { // Inventario MInventory inventory = new MInventory(ctx, ((BigDecimal) params.get("M_Inventory_ID")).intValue(), null); params.put("INVENTORY_DOCUMENTNO", inventory.getDocumentNo()); // Opción de visualización: Todos o Sólo con diferencia params.put( "VISUAL_OPTION_DESCRIPTION", MRefList.getListName( ctx, getVisualOptionReferenceID(), (String) params.get("Visual_Option"))); // Línea de artículo BigDecimal productLinesID = (BigDecimal) params.get("M_Product_Lines_ID"); if (!Util.isEmpty(productLinesID, true)) { MProductLines productLines = new MProductLines(ctx, productLinesID.intValue(), null); params.put("PRODUCT_LINES_VALUE", productLines.getValue()); params.put("PRODUCT_LINES_NAME", productLines.getName()); } }
/** * Mantenimiento de la tabla de inventario para que no crezca en cada importación * * @throws Exception */ protected int maintainInventoryImportTable(String securityCheck) throws Exception { String maintenanceMonth = MPreference.searchCustomPreferenceValue( INVENTORY_MAINTENANCE_PREFERENCE, getAD_Client_ID(), Env.getAD_Org_ID(getCtx()), Env.getAD_User_ID(getCtx()), true); // Si la preference no tiene nada, no se elimina nada if (Util.isEmpty(maintenanceMonth, true)) { return 0; } Integer months = 0; try { months = Integer.parseInt(maintenanceMonth); } catch (Exception e) { throw new Exception( "La preferencia o valor predeterminado " + INVENTORY_MAINTENANCE_PREFERENCE + " no esta existo o contiene un valor no numerico"); } months = months * -1; // Eliminación de registros anteriores a los meses de tolerancia hacia atrás String sql = "DELETE FROM " + X_I_Inventory.Table_Name + " WHERE date_trunc('month',?::date) >= date_trunc('month',created) " + securityCheck; Calendar toleranceDate = Calendar.getInstance(); toleranceDate.setTimeInMillis(Env.getDate().getTime()); toleranceDate.add(Calendar.MONTH, months); PreparedStatement ps = new CPreparedStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, sql, null, true); ps.setTimestamp(1, new Timestamp(toleranceDate.getTimeInMillis())); return ps.executeUpdate(); }
@Override protected String getQuery() { String invoiceDateColumn = null; String paymentDateColumn = null; StringBuffer var1 = new StringBuffer(); var1.append("SELECT dt.printname, "); var1.append(" bp.name AS proveedor, "); var1.append(" pt.name as regla, "); var1.append(" ci.description, "); var1.append(" oi.documentno, "); var1.append(" oi.c_bpartner_id, "); var1.append(" oi.c_invoice_id, "); var1.append(" oi.c_invoicepayschedule_id, "); var1.append(" oi.c_currency_id, "); var1.append(" oi.issotrx, "); var1.append(" TRUNC(oi.dateinvoiced) as dateinvoiced, "); var1.append(" oi.netdays, "); var1.append(" oi.duedate, "); var1.append(" oi.daysdue, "); var1.append( " case when substring(dt.docbasetype from 3 for 1)='I' then oi.openamt else oi.openamt*-1 end as openamt , "); var1.append(" oi.ad_org_id, "); var1.append(" CASE "); var1.append(" WHEN daysdue IS NULL THEN '' "); var1.append(" WHEN daysdue <=- 30 THEN '3 - Mayor 30 dias' "); var1.append(" WHEN daysdue >- 30 "); var1.append(" AND daysdue <=- 7 THEN '2 - Proximos 30d' "); var1.append(" WHEN daysdue >- 7 "); var1.append(" AND daysdue <= 0 THEN '1 - Esta semana' "); var1.append(" WHEN daysdue > 0 THEN '0 - Vencidos' "); var1.append(" END AS estado "); var1.append("FROM rv_openitem oi "); var1.append(" INNER JOIN c_bpartner bp "); var1.append(" ON ( oi.c_bpartner_id = bp.c_bpartner_id ) "); var1.append(" INNER JOIN c_doctype dt "); var1.append(" ON ( oi.c_doctypetarget_id = dt.c_doctype_id ) "); var1.append(" INNER JOIN c_paymentterm pt "); var1.append(" ON ( pt.c_paymentterm_id = oi.c_paymentterm_id ) "); var1.append(" INNER JOIN c_invoice ci "); var1.append(" ON ( ci.c_invoice_id = oi.c_invoice_id) "); var1.append("WHERE oi.ad_client_id=? "); if (!getTrxType().equals("B")) { var1.append(" AND oi.issotrx = '").append(getTrxType().equals("C") ? "Y" : "N").append("' "); } if (!Util.isEmpty(getOrgID(), true)) { var1.append(" AND oi.ad_org_id = ? "); } if (!Util.isEmpty(getBpartnerID(), true)) { var1.append(" AND oi.c_bpartner_id = ? "); } if (getDateFrom() != null) { invoiceDateColumn = getDateFilter().equals("T") ? "oi.dateinvoiced" : "oi.duedate"; var1.append(" AND ").append(invoiceDateColumn).append(" >= ?::date "); } if (getDateTo() != null) { invoiceDateColumn = invoiceDateColumn != null ? invoiceDateColumn : getDateFilter().equals("T") ? "oi.dateinvoiced" : "oi.duedate"; var1.append(" AND ").append(invoiceDateColumn).append(" <= ?::date "); } var1.append(" AND oi.docstatus IN ( 'CO', 'CL' ) "); var1.append(" AND oi.openamt > 0 "); var1.append("UNION "); var1.append("SELECT dt.printname, "); var1.append(" bp.name AS proveedor, "); var1.append(" pt.name as regla, "); var1.append(" ci.description, "); var1.append(" pi.documentno, "); var1.append(" pi.c_bpartner_id, "); var1.append(" NULL, "); var1.append(" NULL, "); var1.append(" pi.c_currency_id, "); var1.append(" pi.isreceipt, "); var1.append(" TRUNC(pi.datetrx) as dateinvoiced, "); var1.append(" 0, "); var1.append(" pi.dateacct, "); var1.append(" NULL, "); var1.append(" pi.availableamt *- 1 AS pago, "); var1.append(" pi.ad_org_id, "); var1.append(" '' AS estado "); var1.append("FROM rv_payment pi "); var1.append(" INNER JOIN c_bpartner bp "); var1.append(" ON ( pi.c_bpartner_id = bp.c_bpartner_id ) "); var1.append(" INNER JOIN c_doctype dt "); var1.append(" ON ( pi.c_doctype_id = dt.c_doctype_id ) "); var1.append(" INNER JOIN c_invoice ci "); var1.append(" ON ( ci.c_invoice_id = pi.c_invoice_id) "); var1.append(" INNER JOIN c_paymentterm pt "); var1.append(" ON ( pt.c_paymentterm_id = ci.c_paymentterm_id ) "); var1.append(" WHERE "); var1.append(" pi.docstatus IN ( 'CO', 'CL' ) "); var1.append(" AND pi.ad_client_id = ? "); if (!getTrxType().equals("B")) { var1.append(" AND pi.isreceipt = '") .append(getTrxType().equals("C") ? "Y" : "N") .append("' "); } if (!Util.isEmpty(getOrgID(), true)) { var1.append(" AND pi.ad_org_id=? "); } if (!Util.isEmpty(getBpartnerID(), true)) { var1.append(" AND pi.c_bpartner_id = ? "); } var1.append(" AND pi.availableamt > 0 "); if (getDateFrom() != null) { paymentDateColumn = getDateFilter().equals("T") ? "pi.datetrx" : "pi.dateacct"; var1.append(" AND ").append(paymentDateColumn).append(" >= ?::date "); } if (getDateTo() != null) { paymentDateColumn = paymentDateColumn != null ? paymentDateColumn : getDateFilter().equals("T") ? "oi.dateinvoiced" : "oi.duedate"; var1.append(" AND ").append(paymentDateColumn).append(" <= ?::date "); } var1 = new StringBuffer("Select * from (").append(var1); if (new String("Fecha").equals(getSubtotales_por())) { var1.append(") AS T1 ORDER BY dateinvoiced "); } else { var1.append(") AS T1 ORDER BY proveedor "); } return var1.toString(); }
/** * Descripción de Método * * @return * @throws java.lang.Exception */ protected String doIt() throws java.lang.Exception { String errorLocatorNotFound = "'" + getMsg("LocatorNotFound") + ". '"; String errorWarehouseNotFound = "'" + getMsg("WarehouseNotFound") + ". '"; String errorInvalidLocator = "'" + getMsg("InvalidLocatorWarehouse") + ". '"; String errorProductNotFound = "'" + getMsg("ProductNotFound") + ". '"; String errorInvalidQtyCount = "'" + getMsg("InvalidQtyCount") + ". '"; String errorNoInventory = "'" + getMsg("NoExistsInventory") + ". '"; log.info("M_Locator_ID=" + p_M_Locator_ID + ",MovementDate=" + p_MovementDate); StringBuffer sql = null; int no = 0; String securityCheck = " AND AD_Client_ID=" + p_AD_Client_ID + " AND IsActive = 'Y' "; maintainInventoryImportTable(securityCheck); // Delete Old Imported if (p_DeleteOldImported) { sql = new StringBuffer("DELETE I_Inventory " + "WHERE I_IsImported='Y'").append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Delete Old Impored =" + no); } // Reset 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( ")," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL, " + " I_IsImported = 'N' " + " WHERE I_IsImported <> 'Y' OR I_IsImported IS NULL") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.info("Reset=" + no); // sql = new StringBuffer( "UPDATE I_Inventory i " + "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 i.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()); // if( no != 0 ) { // log.warning( "Invalid Org=" + no ); // } ////////////////////////////////////////////////////////////////////////////////////// // Set Movement Date ////////////////////////////////////////////////////////////////////////////////////// sql = new StringBuffer(" UPDATE I_Inventory " + " SET MovementDate = ") .append(DB.TO_DATE(p_MovementDate)) .append(" WHERE I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Movement Date = " + no); ////////////////////////////////////////////////////////////////////////////////////// // Set Warehouse and Locator ////////////////////////////////////////////////////////////////////////////////////// // Warehouse From Value sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_Warehouse_ID = " + " (SELECT M_Warehouse_ID " + " FROM M_Warehouse w" + " WHERE (TRIM(i.WarehouseValue) = TRIM(w.Value) OR" + " TRIM(i.WarehouseValue) = TRIM(w.Name)) AND " + " i.AD_Client_ID = w.AD_Client_ID AND " + " w.IsActive = 'Y' AND " + " ROWNUM=1) " + " WHERE WarehouseValue IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Warehouse from Value = " + no); // Locator From Value sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_Locator_ID = " + " (SELECT M_Locator_ID " + " FROM M_Locator l" + " WHERE i.LocatorValue = l.Value AND " + " i.AD_Client_ID = l.AD_Client_ID AND " + " l.IsActive = 'Y' AND " + " ROWNUM=1) " + " WHERE LocatorValue IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Locator from Value = " + no); // locator from X,Y,Z sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_Locator_ID=" + " (SELECT 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 AND " + " l.IsActive = 'Y' AND " + " ROWNUM=1) " + " 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(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Locator from X,Y,Z = " + no); // Warehouse From Locator 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_Warehouse_ID IS NULL AND " + " M_Locator_ID IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Warehouse from Locator = " + no); // locator default from warehouse sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_Locator_ID = " + " (SELECT M_Locator_ID " + " FROM M_Locator l " + " WHERE i.M_Warehouse_ID=l.M_Warehouse_ID" + " ORDER BY isdefault DESC " + " LIMIT 1) " + " WHERE M_Warehouse_ID IS NOT NULL AND " + " M_Locator_ID IS NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Locator Default from Warehouse = " + no); // Locator From parameter (default locator) if (p_M_Locator_ID != 0) { sql = new StringBuffer(" UPDATE I_Inventory " + " SET M_Locator_ID = ") .append(p_M_Locator_ID) .append( ", " + " LocatorValue = (SELECT Value FROM M_Locator WHERE M_Locator_ID = ") .append(p_M_Locator_ID) .append(") " + " WHERE M_Locator_ID IS NULL AND " + " I_IsImported<>'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Locator from Parameter = " + no); } // No locator detection sql = new StringBuffer( " UPDATE I_Inventory " + " SET I_IsImported = 'E', " + " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || " + errorLocatorNotFound + " WHERE M_Locator_ID IS NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); if (no != 0) { log.warning("Locator not found = " + no); } // No Warehouse detection sql = new StringBuffer( " UPDATE I_Inventory " + " SET I_IsImported = 'E', " + " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || " + errorWarehouseNotFound + " WHERE M_Warehouse_ID IS NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); if (no != 0) { log.warning("Warehouse not found = " + no); } // Locator warehouse not the line warehouse detection sql = new StringBuffer( " UPDATE I_Inventory i " + " SET I_IsImported = 'E', " + " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || " + errorInvalidLocator + " WHERE i.M_Warehouse_ID IS NOT NULL AND " + " i.M_Locator_ID IS NOT NULL AND " + " i.I_IsImported <> 'Y' AND " + " i.M_Warehouse_ID <> (SELECT l.M_Warehouse_ID " + " FROM M_Locator l " + " WHERE l.M_Locator_ID = i.M_Locator_ID) ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); if (no != 0) { log.warning("Locator Warehouse not the line Warehouse = " + no); } ////////////////////////////////////////////////////////////////////////////////////// // Set Product ////////////////////////////////////////////////////////////////////////////////////// // ... From Value sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_Product_ID = " + " (SELECT M_Product_ID " + " FROM M_Product p" + " WHERE i.Value = p.Value AND " + " i.AD_Client_ID = p.AD_Client_ID AND " + " p.IsActive = 'Y' AND " + " ROWNUM=1) " + " WHERE Value IS NOT NULL AND " + " I_IsImported <> 'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Product from Value=" + no); // ... From UPC Instance sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_AttributeSetInstance_ID = " + " (SELECT M_AttributeSetInstance_ID " + " FROM M_Product_UPC_Instance p " + " WHERE i.UPC = p.UPC AND " + " i.M_Product_ID = p.M_Product_ID AND " + " i.AD_Client_ID = p.AD_Client_ID AND " + " p.IsActive = 'Y' AND " + " ROWNUM=1) " + " WHERE M_AttributeSetInstance_ID IS NULL AND " + " UPC IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set AttributeInstance from UPC = " + no); sql = new StringBuffer( " UPDATE I_Inventory i " + " SET INSTANCE_DESCRIPTION = " + " (SELECT Description " + " FROM M_AttributeSetInstance p " + " WHERE i.M_AttributeSetInstance_ID = p.M_AttributeSetInstance_ID AND " + " i.AD_Client_ID = p.AD_Client_ID AND " + " p.IsActive = 'Y' AND " + " ROWNUM=1) " + " WHERE INSTANCE_DESCRIPTION IS NULL AND " + " M_AttributeSetInstance_ID IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Instance_Description from AttributeInstance = " + no); if (no == 0) { // ... From UPC sql = new StringBuffer( " UPDATE I_Inventory i " + " SET M_Product_ID = " + " (SELECT M_Product_ID " + " FROM M_Product p " + " WHERE i.UPC = p.UPC AND " + " i.AD_Client_ID = p.AD_Client_ID AND " + " p.IsActive = 'Y' AND " + " ROWNUM=1) " + " WHERE M_Product_ID IS NULL AND " + " UPC IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Product from UPC = " + no); } // No product detection sql = new StringBuffer( " UPDATE I_Inventory " + " SET I_IsImported = 'E', " + " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || " + errorProductNotFound + " WHERE M_Product_ID IS NULL AND " + " I_IsImported<>'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); if (no != 0) { log.warning("Product not found = " + no); } // No QtyCount sql = new StringBuffer( " UPDATE I_Inventory " + " SET I_IsImported = 'E', " + " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || " + errorInvalidQtyCount + " WHERE QtyCount IS NULL AND " + " I_IsImported <> 'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); if (no != 0) { log.warning("No QtyCount = " + no); } ////////////////////////////////////////////////////////////////////////////////////// // Set Organization from VALUE ////////////////////////////////////////////////////////////////////////////////////// sql = new StringBuffer( " UPDATE I_Inventory i " + " SET AD_Org_ID = " + " (SELECT AD_Org_ID " + " FROM AD_Org p" + " WHERE i.OrgValue = p.Value AND " + " i.AD_Client_ID = p.AD_Client_ID AND " + " p.IsActive = 'Y'" + " ) " + " WHERE OrgValue IS NOT NULL AND " + " I_IsImported <> 'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Org from OrgValue=" + no); ////////////////////////////////////////////////////////////////////////////////////// // Set Organization ////////////////////////////////////////////////////////////////////////////////////// sql = new StringBuffer( " UPDATE I_Inventory i " + " SET AD_Org_ID = " + " (SELECT AD_Org_ID " + " FROM M_Warehouse p " + " WHERE i.M_Warehouse_ID = p.M_Warehouse_ID AND " + " ROWNUM=1) " + " WHERE M_Warehouse_ID IS NOT NULL AND " + " I_IsImported <> 'Y' ") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Organization from Warehouse = " + no); ////////////////////////////////////////////////////////////////////////////////////// // Set Inventory ////////////////////////////////////////////////////////////////////////////////////// MDocType docTypePI = MDocType.getDocType(getCtx(), MDocType.DOCTYPE_MaterialPhysicalInventory, null); sql = new StringBuffer( "UPDATE I_Inventory i " + "SET M_Inventory_ID = (SELECT m.m_inventory_id " + " FROM m_inventory m " + " WHERE m.documentno = i.inventory_documentno " + " AND m.m_warehouse_id = i.m_warehouse_id" + " AND m.docstatus IN ('DR','IP') " + " AND m.inventorykind = '" + MInventory.INVENTORYKIND_PhysicalInventory + "'" + " AND m.c_doctype_id = " + docTypePI.getC_DocType_ID() + " LIMIT 1) " + "WHERE inventory_documentno is not null " + " AND M_Warehouse_ID IS NOT NULL " + " AND I_IsImported <> 'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Inventory ID From Inventory DocumentNo = " + no); // Error si tiene un nro de documento pero no existe tal inventario sql = new StringBuffer( " UPDATE I_Inventory " + " SET I_IsImported = 'E', " + " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || " + errorNoInventory + " WHERE inventory_documentno is not null AND " + " M_Inventory_ID IS NULL AND " + " I_IsImported <> 'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); if (no != 0) { log.warning("No Inventory ID = " + no); } ////////////////////////////////////////////////////////////////////////////////////// // Set Inventory Line ////////////////////////////////////////////////////////////////////////////////////// sql = new StringBuffer( "UPDATE I_Inventory i " + "SET M_InventoryLine_ID = (SELECT m.m_inventoryline_id " + " FROM m_inventoryline m " + " WHERE m.m_product_id = i.m_product_id " + " AND m.m_inventory_id = i.m_inventory_id" + " AND (CASE WHEN i.m_attributesetinstance_id is null THEN m.m_attributesetinstance_id = 0" + " WHEN i.m_attributesetinstance_id = 0 THEN m.m_attributesetinstance_id = 0" + " ELSE m.m_attributesetinstance_id = i.m_attributesetinstance_id " + " END)" + " AND m.isactive = 'Y'" + " LIMIT 1) " + "WHERE M_Inventory_ID is not null " + " AND M_Product_ID is not null " + " AND I_IsImported <> 'Y'") .append(securityCheck); no = DB.executeUpdate(sql.toString()); log.fine("Set Inventory Line ID = " + no); MInventory inventory = null; MInventoryLine line = null; int noInsert = 0; int noUpdate = 0; int noInsertLine = 0; int noUpdateLine = 0; boolean insertLine = false; // Go through Inventory Records sql = new StringBuffer( " SELECT I_Inventory_ID " + " FROM I_Inventory " + " WHERE I_IsImported = 'N'") .append(securityCheck) .append( " ORDER BY M_Inventory_ID, M_Warehouse_ID, TRUNC(MovementDate), I_Inventory_ID"); try { PreparedStatement pstmt = DB.prepareStatement(sql.toString()); ResultSet rs = pstmt.executeQuery(); // int x_M_Warehouse_ID = -1; Timestamp x_MovementDate = null; X_I_Inventory imp = null; while (rs.next()) { imp = new X_I_Inventory(getCtx(), rs.getInt("I_Inventory_ID"), null); Timestamp MovementDate = TimeUtil.getDay(imp.getMovementDate()); // Inventario existente if (!Util.isEmpty(imp.getM_Inventory_ID(), true)) { if (inventory == null || inventory.getM_Inventory_ID() != imp.getM_Inventory_ID()) { inventory = new MInventory(getCtx(), imp.getM_Inventory_ID(), null); manageInventory(inventory); noUpdate++; } } else if ((inventory == null) || (imp.getM_Warehouse_ID() != x_M_Warehouse_ID) || !MovementDate.equals(x_MovementDate)) { inventory = new MInventory(getCtx(), 0, null); inventory.setClientOrg(imp.getAD_Client_ID(), imp.getAD_Org_ID()); inventory.setDescription("I " + imp.getM_Warehouse_ID() + " " + MovementDate); inventory.setM_Warehouse_ID(imp.getM_Warehouse_ID()); inventory.setMovementDate(MovementDate); manageInventory(inventory); if (!inventory.save()) { log.log(Level.SEVERE, "Inventory not saved: " + CLogger.retrieveErrorAsString()); break; } x_M_Warehouse_ID = imp.getM_Warehouse_ID(); x_MovementDate = MovementDate; noInsert++; } // Line // Added by Lucas Hernandez - Kunan // int M_AttributeSetInstance_ID = 0; // Línea de inventario existente if (!Util.isEmpty(imp.getM_InventoryLine_ID(), true)) { line = new MInventoryLine(getCtx(), imp.getM_InventoryLine_ID(), null); line.setQtyCount(imp.getQtyCount()); line.setQtyInternalUse(line.getQtyBook().subtract(imp.getQtyCount())); } else { line = new MInventoryLine( inventory, imp.getM_Locator_ID(), imp.getM_Product_ID(), imp.getM_AttributeSetInstance_ID(), imp.getQtyBook(), imp.getQtyCount()); } insertLine = line.getM_InventoryLine_ID() == 0; if (line.save()) { no = DB.executeUpdate( "UPDATE I_Inventory SET i_isimported='Y',i_errormsg=null,m_inventory_id=" + line.getM_Inventory_ID() + ",M_InventoryLine_ID=" + line.getM_InventoryLine_ID() + ",processed='Y' WHERE i_inventory_id = " + imp.getID()); // imp.setI_IsImported( true ); // imp.setI_ErrorMsg(null); // imp.setM_Inventory_ID( line.getM_Inventory_ID()); // imp.setM_InventoryLine_ID( line.getM_InventoryLine_ID()); // imp.setProcessed( true ); // if( imp.save()) { if (no == 1) { if (insertLine) { noInsertLine++; } else { noUpdateLine++; } } } else { no = DB.executeUpdate( "UPDATE I_Inventory SET i_isimported='E',i_errormsg=" + CLogger.retrieveErrorAsString() + " WHERE i_inventory_id = " + imp.getID()); } } rs.close(); pstmt.close(); } catch (Exception e) { log.log(Level.SEVERE, "doIt", 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(securityCheck); no = DB.executeUpdate(sql.toString()); addLog(0, null, new BigDecimal(no), "@Errors@"); // Post Import postImport(); addLog(0, null, new BigDecimal(noInsert), "@M_Inventory_ID@: @Inserted@"); addLog(0, null, new BigDecimal(noUpdate), "@M_Inventory_ID@: @Updated@"); addLog(0, null, new BigDecimal(noInsertLine), "@M_InventoryLine_ID@: @Inserted@"); addLog(0, null, new BigDecimal(noUpdateLine), "@M_InventoryLine_ID@: @Updated@"); return ""; } // doIt