Пример #1
0
 /**
  * Find first Year Period of DateAcct based on Client Calendar
  *
  * @param ctx context
  * @param C_Calendar_ID calendar
  * @param DateAcct date
  * @return active first Period
  */
 public static MPeriod getFirstInYear(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
   MPeriod retValue = null;
   String sql =
       "SELECT * "
           + "FROM C_Period "
           + "WHERE C_Year_ID IN "
           + "(SELECT p.C_Year_ID "
           + "FROM C_Year y"
           + " INNER JOIN C_Period p ON (y.C_Year_ID=p.C_Year_ID) "
           + "WHERE y.C_Calendar_ID=?"
           + "	AND ? BETWEEN StartDate AND EndDate)"
           + " AND IsActive='Y' AND PeriodType='S' "
           + "ORDER BY StartDate";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, (Trx) null);
     pstmt.setInt(1, C_Calendar_ID);
     pstmt.setTimestamp(2, DateAcct);
     rs = pstmt.executeQuery();
     if (rs.next()) // 	first only
     retValue = new MPeriod(ctx, rs, null);
   } catch (SQLException e) {
     s_log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeStatement(pstmt);
     DB.closeResultSet(rs);
   }
   return retValue;
 } //	getFirstInYear
Пример #2
0
 /**
  * Get Accessible Goals
  *
  * @param ctx context
  * @return array of goals
  */
 public static MGoal[] getGoals(Ctx ctx) {
   ArrayList<MGoal> list = new ArrayList<MGoal>();
   String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' " + "ORDER BY SeqNo";
   sql =
       MRole.getDefault(ctx, false)
           .addAccessSQL(sql, "PA_Goal", false, true); // 	RW to restrict Access
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, (Trx) null);
     rs = pstmt.executeQuery();
     while (rs.next()) {
       MGoal goal = new MGoal(ctx, rs, null);
       goal.updateGoal(false);
       list.add(goal);
     }
   } catch (Exception e) {
     s_log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeStatement(pstmt);
     DB.closeResultSet(rs);
   }
   MGoal[] retValue = new MGoal[list.size()];
   list.toArray(retValue);
   return retValue;
 } //	getGoals
Пример #3
0
  /**
   * Returns the previous period
   *
   * @param period MPeriod
   * @param periodCount Count
   * @param trx trx
   * @param ctx Ctx
   * @return MPeriod
   */
  public static MPeriod getPreviousPeriod(MPeriod period, Ctx ctx, Trx trx) {

    MPeriod newPeriod = null;
    String sql =
        "SELECT * FROM C_Period WHERE "
            + "C_Period.IsActive='Y' AND PeriodType='S' "
            + "AND C_Period.C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) "
            + "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) "
            + " < ((? * 1000) + ?) ORDER BY C_Period.C_Year_ID DESC, C_Period.PeriodNo DESC";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, period.getC_Calendar_ID());
      pstmt.setInt(2, period.getC_Year_ID());
      pstmt.setInt(3, period.getPeriodNo());
      rs = pstmt.executeQuery();
      if (rs.next()) newPeriod = new MPeriod(ctx, rs, trx);
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    return newPeriod;
  }
Пример #4
0
  /**
   * Find all the year records in a Calendar, it need not be a standard period (used in MRP)
   *
   * @param C_Calendar_ID calendar
   * @param ctx context
   * @param trx trx
   * @return MYear[]
   */
  public static MYear[] getAllYearsInCalendar(int C_Calendar_ID, Ctx ctx, Trx trx) {

    List<MYear> years = new ArrayList<MYear>();
    String sql = "SELECT * FROM C_Year WHERE " + "IsActive='Y' AND C_Calendar_ID = ? ";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, C_Calendar_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) years.add(new MYear(ctx, rs, trx));

    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {

      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }

    MYear[] retValue = new MYear[years.size()];
    years.toArray(retValue);
    return retValue;
  }
Пример #5
0
  /**
   * Find the periods in a calendar year it need not be a standard period (used in MRP)
   *
   * @param C_Year_ID Year
   * @param periodType Period Type
   * @param ctx context
   * @param trx trx
   * @return MPeriod[]
   */
  public static MPeriod[] getAllPeriodsInYear(int C_Year_ID, String periodType, Ctx ctx, Trx trx) {

    List<MPeriod> periods = new ArrayList<MPeriod>();
    String sql = "SELECT * FROM C_Period WHERE IsActive='Y'";

    sql = sql + " AND C_Year_ID = ?";

    if (periodType != null) sql = sql + " AND PeriodType = ? ";

    sql = sql + " order by StartDate ";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, C_Year_ID);

      if (periodType != null) pstmt.setString(2, periodType);

      rs = pstmt.executeQuery();
      while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    MPeriod[] retValue = new MPeriod[periods.size()];
    periods.toArray(retValue);
    return retValue;
  }
Пример #6
0
 /**
  * ************************************************************************ Create Missing
  * Document Types
  *
  * @param ctx context
  * @param AD_Client_ID client
  * @param sp server process
  * @param trx transaction
  */
 public static void createDocumentTypes(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) {
   s_log.info("AD_Client_ID=" + AD_Client_ID);
   String sql =
       "SELECT rl.Value, rl.Name "
           + "FROM AD_Ref_List rl "
           + "WHERE rl.AD_Reference_ID=183"
           + " AND rl.IsActive='Y' AND NOT EXISTS "
           + " (SELECT * FROM C_DocType dt WHERE dt.AD_Client_ID=? AND rl.Value=dt.DocBaseType)";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, trx);
     pstmt.setInt(1, AD_Client_ID);
     rs = pstmt.executeQuery();
     while (rs.next()) {
       String name = rs.getString(2);
       String value = rs.getString(1);
       s_log.config(name + "=" + value);
       MDocType dt = new MDocType(ctx, value, name, trx);
       if (dt.save()) {
         if (sp != null) sp.addLog(0, null, null, name);
         else s_log.fine(name);
       } else {
         if (sp != null) sp.addLog(0, null, null, "Not created: " + name);
         else s_log.warning("Not created: " + name);
       }
     }
   } catch (Exception e) {
     s_log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeResultSet(rs);
     DB.closeStatement(pstmt);
   }
 } //	createDocumentTypes
Пример #7
0
 /**
  * Get Restriction Lines
  *
  * @param reload reload data
  * @return array of lines
  */
 public MGoalRestriction[] getRestrictions(boolean reload) {
   if (m_restrictions != null && !reload) return m_restrictions;
   ArrayList<MGoalRestriction> list = new ArrayList<MGoalRestriction>();
   //
   String sql =
       "SELECT * FROM PA_GoalRestriction "
           + "WHERE PA_Goal_ID=? AND IsActive='Y' "
           + "ORDER BY Org_ID, C_BPartner_ID, M_Product_ID";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, get_Trx());
     pstmt.setInt(1, getPA_Goal_ID());
     rs = pstmt.executeQuery();
     while (rs.next()) list.add(new MGoalRestriction(getCtx(), rs, get_Trx()));
   } catch (Exception e) {
     log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeStatement(pstmt);
     DB.closeResultSet(rs);
   }
   //
   m_restrictions = new MGoalRestriction[list.size()];
   list.toArray(m_restrictions);
   return m_restrictions;
 } //	getRestrictions
  private void fullAssociated() {

    boolean associated = true;

    String SQL =
        "Select * "
            + "from XX_VMR_REFERENCEMATRIX "
            + "where M_product IS NULL AND XX_VMR_PO_LINEREFPROV_ID="
            + LineRefProv.get_ID();

    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        associated = false;
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    if (associated == true) {
      String SQL10 =
          "UPDATE XX_VMR_PO_LineRefProv "
              + " SET XX_ReferenceIsAssociated='Y'"
              + " WHERE XX_VMR_PO_LineRefProv_ID="
              + LineRefProv.getXX_VMR_PO_LineRefProv_ID();

      DB.executeUpdate(null, SQL10);

      //			LineRefProv.setXX_ReferenceIsAssociated(true);
      //			LineRefProv.save();

      int dialog = Env.getCtx().getContextAsInt("#Dialog_Associate_Aux");

      if (dialog == 1) {
        ADialog.info(m_WindowNo, m_frame, "MustRefresh");
        Env.getCtx().remove("#Dialog_Associate_Aux");
      }

    } else {
      String SQL10 =
          "UPDATE XX_VMR_PO_LineRefProv "
              + " SET XX_ReferenceIsAssociated='N'"
              + " WHERE XX_VMR_PO_LineRefProv_ID="
              + LineRefProv.getXX_VMR_PO_LineRefProv_ID();

      DB.executeUpdate(null, SQL10);
      //			LineRefProv.setXX_ReferenceIsAssociated(false);
      //			LineRefProv.save();
    }
  }
Пример #9
0
  /**
   * Find standard Period of DateAcct based on Client Calendar
   *
   * @param ctx context
   * @param C_Calendar_ID calendar
   * @param DateAcct date
   * @return active Period or null
   */
  public static MPeriod getOfCalendar(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
    if (DateAcct == null) {
      s_log.warning("No DateAcct");
      return null;
    }
    if (C_Calendar_ID == 0) {
      s_log.warning("No Calendar");
      return null;
    }
    //	Search in Cache first
    Iterator<MPeriod> it = s_cache.values().iterator();
    while (it.hasNext()) {
      MPeriod period = it.next();
      if (period.getC_Calendar_ID() == C_Calendar_ID
          && period.isStandardPeriod()
          && period.isInPeriod(DateAcct)) return period;
    }

    //	Get it from DB
    MPeriod retValue = null;
    String sql =
        "SELECT * FROM C_Period "
            + "WHERE C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)"
            + " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')"
            + " AND IsActive='Y' AND PeriodType='S'";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, (Trx) null);
      pstmt.setInt(1, C_Calendar_ID);
      pstmt.setTimestamp(2, TimeUtil.getDay(DateAcct));
      rs = pstmt.executeQuery();
      while (rs.next()) {
        MPeriod period = new MPeriod(ctx, rs, null);
        Integer key = Integer.valueOf(period.getC_Period_ID());
        s_cache.put(key, period);
        if (period.isStandardPeriod()) retValue = period;
      }
    } catch (SQLException e) {
      s_log.log(Level.SEVERE, "DateAcct=" + DateAcct, e);
    } finally {
      DB.closeStatement(pstmt);
      DB.closeResultSet(rs);
    }
    if (retValue == null)
      s_log.warning(
          "No Standard Period for " + DateAcct + " (C_Calendar_ID=" + C_Calendar_ID + ")");
    return retValue;
  } //	get
Пример #10
0
 /**
  * Get Goals with Measure
  *
  * @param ctx context
  * @param PA_Measure_ID measure
  * @return goals
  */
 public static MGoal[] getMeasureGoals(Ctx ctx, int PA_Measure_ID) {
   ArrayList<MGoal> list = new ArrayList<MGoal>();
   String sql = "SELECT * FROM PA_Goal WHERE IsActive='Y' AND PA_Measure_ID=? " + "ORDER BY SeqNo";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, (Trx) null);
     pstmt.setInt(1, PA_Measure_ID);
     rs = pstmt.executeQuery();
     while (rs.next()) list.add(new MGoal(ctx, rs, null));
   } catch (Exception e) {
     s_log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeStatement(pstmt);
     DB.closeResultSet(rs);
   }
   MGoal[] retValue = new MGoal[list.size()];
   list.toArray(retValue);
   return retValue;
 } //	getMeasureGoals
Пример #11
0
  /**
   * gets all Periods in the Range
   *
   * @param startPeriod
   * @param endPeriod
   * @param calendar_ID
   * @return MPeriod[]
   */
  public static MPeriod[] getAllPeriodsInRange(
      MPeriod startPeriod, MPeriod endPeriod, int calendar_ID, Ctx ctx, Trx trx) {
    if ((startPeriod.getC_Calendar_ID() != calendar_ID)
        || (endPeriod.getC_Calendar_ID() != calendar_ID)) {
      log.saveError("Error", "Periods do not belong to the calendar");
      return null;
    }

    ArrayList<MPeriod> periods = new ArrayList<MPeriod>();
    String sql =
        "SELECT * FROM C_Period WHERE "
            + "C_Period.IsActive='Y' AND PeriodType='S' "
            + "AND C_Period.C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) "
            + // calendar_ID
            "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) BETWEEN"
            + " (? * 1000 + ?) AND (? * 1000 + ? )"
            + // start Period year ID, Period Number , End Period Year ID, Period Number
            " ORDER BY C_Period.C_Year_ID ASC, C_Period.PeriodNo ASC";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, calendar_ID);
      pstmt.setInt(2, startPeriod.getC_Year_ID());
      pstmt.setInt(3, startPeriod.getPeriodNo());
      pstmt.setInt(4, endPeriod.getC_Year_ID());
      pstmt.setInt(5, endPeriod.getPeriodNo());
      rs = pstmt.executeQuery();
      while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    MPeriod[] retValue = new MPeriod[periods.size()];
    periods.toArray(retValue);
    return retValue;
  }
Пример #12
0
  /**
   * Get User Goals
   *
   * @param ctx context
   * @param AD_User_ID user
   * @return array of goals
   */
  public static MGoal[] getUserGoals(Ctx ctx) {
    int AD_Role_ID = ctx.getAD_Role_ID();
    MRole role = MRole.get(ctx, AD_Role_ID);
    int AD_User_ID = ctx.getAD_User_ID();

    if (AD_User_ID < 0) return getTestGoals(ctx);
    ArrayList<MGoal> list = new ArrayList<MGoal>();
    String sql =
        "SELECT * FROM PA_Goal g "
            + "WHERE IsActive='Y'"
            + " AND AD_Client_ID=?" //	#1
            + " AND (";
    if (!role.isWebStoreRole()) sql += " (AD_User_ID IS NULL AND AD_Role_ID IS NULL) OR ";
    sql +=
        " AD_User_ID=?" //	#2
            + " OR EXISTS (SELECT * FROM AD_User_Roles ur "
            + "WHERE ?=ur.AD_User_ID AND g.AD_Role_ID=ur.AD_Role_ID AND ur.IsActive='Y')) "
            + "ORDER BY SeqNo";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, (Trx) null);
      pstmt.setInt(1, ctx.getAD_Client_ID());
      pstmt.setInt(2, AD_User_ID);
      pstmt.setInt(3, AD_User_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        MGoal goal = new MGoal(ctx, rs, null);
        goal.updateGoal(false);
        list.add(goal);
      }
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    MGoal[] retValue = new MGoal[list.size()];
    list.toArray(retValue);
    return retValue;
  } //	getUserGoals
Пример #13
0
  public static MRequestAction[] getActions(MRequest request) {
    ArrayList<MRequestAction> retVal = new ArrayList<MRequestAction>();
    String sql = "SELECT * FROM R_RequestAction WHERE R_Request_ID = ? ";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, request.get_Trx());
      pstmt.setInt(1, request.getR_Request_ID());
      rs = pstmt.executeQuery();
      while (rs.next()) retVal.add(new MRequestAction(request.getCtx(), rs, request.get_Trx()));
    } catch (Exception e) {
      log.severe(e.toString());
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }

    MRequestAction[] actions = new MRequestAction[retVal.size()];
    retVal.toArray(actions);
    return actions;
  }
Пример #14
0
  /**
   * Load Regions (cached)
   *
   * @param ctx context
   */
  private static void loadAllRegions(Ctx ctx) {
    String sql = "SELECT * FROM C_Region WHERE IsActive='Y'";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, (Trx) null);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        MRegion r = new MRegion(ctx, rs, null);
        s_regions.put(String.valueOf(r.getC_Region_ID()), r);
        if (r.isDefault()) s_default = r;
      }
    } catch (SQLException e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }

    s_log.fine(s_regions.size() + " - default=" + s_default);
  } //	loadAllRegions
Пример #15
0
 /**
  * Get Period Control
  *
  * @param requery requery
  * @return period controls
  */
 public MPeriodControl[] getPeriodControls(boolean requery) {
   if (m_controls != null && !requery) return m_controls;
   //
   ArrayList<MPeriodControl> list = new ArrayList<MPeriodControl>();
   String sql = "SELECT * FROM C_PeriodControl " + "WHERE C_Period_ID=?";
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
     pstmt = DB.prepareStatement(sql, get_Trx());
     pstmt.setInt(1, getC_Period_ID());
     rs = pstmt.executeQuery();
     while (rs.next()) list.add(new MPeriodControl(getCtx(), rs, get_Trx()));
   } catch (Exception e) {
     log.log(Level.SEVERE, sql, e);
   } finally {
     DB.closeResultSet(rs);
     DB.closeStatement(pstmt);
   }
   m_controls = new MPeriodControl[list.size()];
   list.toArray(m_controls);
   return m_controls;
 } //	getPeriodControls
  /**
   * verifica si hay productos asociados
   *
   * @param table table
   */
  private boolean verify() {
    String sql =
        "SELECT XX_VMR_ReferenceMatrix_ID "
            + "FROM XX_VMR_ReferenceMatrix "
            + "WHERE XX_VMR_PO_LINEREFPROV_ID="
            + (Integer) LineRefProv.getXX_VMR_PO_LineRefProv_ID();

    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
      pstmt = DB.prepareStatement(sql, null);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        associatedReference_ID = rs.getInt("XX_VMR_ReferenceMatrix_ID");
        rs.close();
        pstmt.close();
        return true;
      }

    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    } finally {

      try {
        rs.close();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return false;
  } //  tableLoad
Пример #17
0
  /**
   * Find Period of Date based on Client Calendar, it need not be a standard period (used in MRP)
   *
   * @param ctx context
   * @param C_Calendar_ID calendar
   * @param Date date
   * @param trx trx
   * @return active Period or null
   */
  public static MPeriod getPeriod(Ctx ctx, int C_Calendar_ID, Timestamp Date, Trx trx) {
    if (Date == null) {
      s_log.warning("No Date");
      return null;
    }
    if (C_Calendar_ID == 0) {
      s_log.warning("No Calendar");
      return null;
    }

    //	Get it from DB
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    MPeriod retValue = null;
    String sql =
        "SELECT * FROM C_Period "
            + "WHERE C_Year_ID IN "
            + "(SELECT C_Year_ID FROM C_Year WHERE C_Calendar_ID=?)"
            + " AND ? BETWEEN TRUNC(StartDate,'DD') AND TRUNC(EndDate,'DD')"
            + " AND IsActive='Y' ";
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, C_Calendar_ID);
      pstmt.setTimestamp(2, TimeUtil.getDay(Date));
      rs = pstmt.executeQuery();
      if (rs.next()) {
        retValue = new MPeriod(ctx, rs, trx);
      }
    } catch (SQLException e) {
      s_log.log(Level.SEVERE, "DateAcct=" + Date, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    if (retValue == null)
      s_log.warning("No Period for " + Date + " (C_Calendar_ID=" + C_Calendar_ID + ")");
    return retValue;
  } //	getPeriod
  private boolean isInMatrix() {

    String SQL =
        "Select * "
            + "from XX_VMR_REFERENCEMATRIX "
            + "where XX_VMR_PO_LINEREFPROV_ID="
            + LineRefProv.get_ID();

    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
      pstmt = DB.prepareStatement(SQL, null);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        return true;
      }

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    } finally {

      try {
        rs.close();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return false;
  }
Пример #19
0
  /**
   * Set Resource Assignment - Callout
   *
   * @param oldS_ResourceAssignment_ID old value
   * @param newS_ResourceAssignment_ID new value
   * @param windowNo window
   * @throws Exception
   */
  @UICallout
  public void setS_ResourceAssignment_ID(
      String oldS_ResourceAssignment_ID, String newS_ResourceAssignment_ID, int windowNo)
      throws Exception {
    if (newS_ResourceAssignment_ID == null || newS_ResourceAssignment_ID.length() == 0) return;
    int S_ResourceAssignment_ID = Integer.parseInt(newS_ResourceAssignment_ID);
    if (S_ResourceAssignment_ID == 0) return;
    //
    super.setS_ResourceAssignment_ID(S_ResourceAssignment_ID);

    int M_Product_ID = 0;
    String Name = null;
    String Description = null;
    BigDecimal Qty = null;
    String sql =
        "SELECT p.M_Product_ID, ra.Name, ra.Description, ra.Qty "
            + "FROM S_ResourceAssignment ra"
            + " INNER JOIN M_Product p ON (p.S_Resource_ID=ra.S_Resource_ID) "
            + "WHERE ra.S_ResourceAssignment_ID=?";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = DB.prepareStatement(sql, get_Trx());
      pstmt.setInt(1, S_ResourceAssignment_ID);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        M_Product_ID = rs.getInt(1);
        Name = rs.getString(2);
        Description = rs.getString(3);
        Qty = rs.getBigDecimal(4);
      }
    } catch (SQLException e) {
      log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    log.fine(
        "S_ResourceAssignment_ID=" + S_ResourceAssignment_ID + " - M_Product_ID=" + M_Product_ID);
    if (M_Product_ID != 0) {
      setM_Product_ID(M_Product_ID);
      if (Description != null) Name += " (" + Description + ")";
      if (!".".equals(Name)) setDescription(Name);
      if (Qty != null) setQty(Qty);
    }
  } //	setS_ResourceAssignment_ID
  /** Process Button Pressed - Process Matching */
  private void cmd_newProduct() {
    // Selecciono el departamento
    int depart = 0;
    String SQL =
        "Select XX_VMR_DEPARTMENT_ID "
            + "from XX_VMR_VENDORPRODREF "
            + "where XX_VMR_VENDORPRODREF_ID="
            + LineRefProv.getXX_VMR_VendorProdRef_ID();

    try {
      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        depart = rs.getInt("XX_VMR_DEPARTMENT_ID");
      }

      rs.close();
      pstmt.close();

    } catch (Exception a) {
      log.log(Level.SEVERE, SQL, a);
    }

    MVMRVendorProdRef vendorProdRef =
        new MVMRVendorProdRef(Env.getCtx(), LineRefProv.getXX_VMR_VendorProdRef_ID(), null);

    if (vendorProdRef.getXX_VMR_ProductClass_ID() > 0
        && vendorProdRef.getXX_VMR_TypeLabel_ID() > 0) {
      MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);

      // Selecciono el departamento
      X_XX_VMR_Department dept =
          new X_XX_VMR_Department(Env.getCtx(), vendorProdRef.getXX_VMR_Department_ID(), null);
      int category = dept.getXX_VMR_Category_ID();

      // Selecciono la línea
      X_XX_VMR_Line line = new X_XX_VMR_Line(Env.getCtx(), vendorProdRef.getXX_VMR_Line_ID(), null);
      int typeInventory = line.getXX_VMR_TypeInventory_ID();

      MProduct newProduct =
          new MProduct(
              Env.getCtx(),
              vendorProdRef.getXX_VMR_Department_ID(),
              vendorProdRef.getXX_VMR_Line_ID(),
              vendorProdRef.getXX_VMR_Section_ID(),
              vendorProdRef.get_ID(),
              vendorProdRef.getC_TaxCategory_ID(),
              vendorProdRef.getXX_VME_ConceptValue_ID(),
              typeInventory,
              null);

      // Se buscará si por la referencia para producto ya existe para asignarle el Tipo de
      // Exhibición
      String sql =
          "select * from M_Product where XX_VMR_DEPARTMENT_ID = "
              + vendorProdRef.getXX_VMR_Department_ID()
              + " and "
              + "XX_VMR_LINE_ID = "
              + vendorProdRef.getXX_VMR_Line_ID()
              + " and XX_VMR_SECTION_ID = "
              + vendorProdRef.getXX_VMR_Section_ID()
              + " and "
              + "XX_VMR_VendorProdRef_id = "
              + vendorProdRef.getXX_VMR_VendorProdRef_ID()
              + " order by M_Product_ID desc";
      PreparedStatement pstmt = DB.prepareStatement(sql, null);
      ResultSet rs = null;
      try {
        rs = pstmt.executeQuery();
        if (rs.next())
          newProduct.setXX_VMR_TypeExhibition_ID(rs.getInt("XX_VMR_TypeExhibition_ID"));
      } catch (SQLException e) {

        e.printStackTrace();
      } finally {
        DB.closeResultSet(rs);
        DB.closeStatement(pstmt);
      }

      if (vendorProdRef.getXX_VMR_Section_ID() > 0) {
        X_XX_VMR_Section section =
            new X_XX_VMR_Section(Env.getCtx(), vendorProdRef.getXX_VMR_Section_ID(), null);
        newProduct.setName(section.getName());
      } else {
        newProduct.setName(vendorProdRef.getName());
      }
      newProduct.setXX_VMR_Category_ID(category);
      newProduct.setXX_VMR_LongCharacteristic_ID(vendorProdRef.getXX_VMR_LongCharacteristic_ID());
      newProduct.setXX_VMR_Brand_ID(vendorProdRef.getXX_VMR_Brand_ID());
      newProduct.setXX_VMR_UnitConversion_ID(vendorProdRef.getXX_VMR_UnitConversion_ID());
      newProduct.setXX_PiecesBySale_ID(vendorProdRef.getXX_PiecesBySale_ID());
      newProduct.setXX_VMR_UnitPurchase_ID(vendorProdRef.getXX_VMR_UnitPurchase_ID());
      newProduct.setXX_SaleUnit_ID(vendorProdRef.getXX_SaleUnit_ID());
      newProduct.setC_Country_ID(order.getC_Country_ID());
      newProduct.setIsActive(true);
      newProduct.setC_BPartner_ID(vendorProdRef.getC_BPartner_ID());
      newProduct.setXX_VMR_TypeLabel_ID(vendorProdRef.getXX_VMR_TypeLabel_ID());
      newProduct.setXX_VMR_ProductClass_ID(vendorProdRef.getXX_VMR_ProductClass_ID());
      newProduct.setM_AttributeSet_ID(Env.getCtx().getContextAsInt("#XX_L_P_ATTRIBUTESETST_ID"));
      newProduct.setProductType(X_Ref_M_Product_ProductType.ITEM.getValue());
      newProduct.save();

    } else {
      // Creo variables de sesion para atraparlas en la ventana producto
      Env.getCtx().setContext("#Depart_Aux", depart);
      Env.getCtx().setContext("#Section_Aux", LineRefProv.getXX_VMR_Section_ID());
      Env.getCtx().setContext("#Line_Aux", LineRefProv.getXX_VMR_Line_ID());
      Env.getCtx().setContext("#VendorRef_Aux", LineRefProv.getXX_VMR_VendorProdRef_ID());
      Env.getCtx().setContext("#FromProcess_Aux", "Y");

      AWindow window_product = new AWindow();
      Query query = Query.getNoRecordQuery("M_Product", true);
      window_product.initWindow(140, query);
      AEnv.showCenterScreen(window_product);

      // Obtenemos el GridController para setear la variable m_changed=true
      JRootPane jRootPane = ((JRootPane) window_product.getComponent(0));
      JLayeredPane jLayeredPane = (JLayeredPane) jRootPane.getComponent(1);
      JPanel jPanel = (JPanel) jLayeredPane.getComponent(0);
      APanel aPanel = (APanel) jPanel.getComponent(0);
      VTabbedPane vTabbedPane = (VTabbedPane) aPanel.getComponent(0);
      GridController gridController = (GridController) vTabbedPane.getComponent(0);
      GridTable mTable = gridController.getMTab().getTableModel();
      mTable.setChanged(true);

      MProduct.loadLineRefProv(LineRefProv, Env.getCtx());

      // Borro las variables de sesion creadas
      Env.getCtx().remove("#Depart_Aux");
      Env.getCtx().remove("#FromProcess_Aux");
      Env.getCtx().remove("#Line_Aux");
      Env.getCtx().remove("#Section_Aux");
      Env.getCtx().remove("#VendorRef_Aux");
    }
  } //  cmd_newProduct
Пример #21
0
  /**
   * Execute Process Instance and Lock UI. Calls lockUI and unlockUI if parent is a ASyncProcess
   *
   * <pre>
   * 	- Get Process Information
   *      - Call Class
   * 	- Submit SQL Procedure
   * 	- Run SQL Procedure
   * </pre>
   */
  public void run() {
    log.fine("AD_PInstance_ID=" + m_pi.getAD_PInstance_ID() + ", Record_ID=" + m_pi.getRecord_ID());

    //  Lock
    // lock();
    //	try {System.out.println(">> sleeping ..");sleep(20000);System.out.println(".. sleeping <<");}
    // catch (Exception e) {}

    //	Get Process Information: Name, Procedure Name, ClassName, IsReport, IsDirectPrint
    String ProcedureName = "";
    int AD_ReportView_ID = 0;
    int AD_Workflow_ID = 0;
    boolean IsReport = false;
    boolean IsDirectPrint = false;
    //
    String sql =
        "SELECT p.Name, p.ProcedureName,p.ClassName, p.AD_Process_ID," //	1..4
            + " p.isReport,p.IsDirectPrint,p.AD_ReportView_ID,p.AD_Workflow_ID," //	5..8
            + " CASE WHEN COALESCE(p.Statistic_Count,0)=0 THEN 0 ELSE p.Statistic_Seconds/p.Statistic_Count END CASE,"
            + " p.IsServerProcess "
            + "FROM AD_Process p"
            + " INNER JOIN AD_PInstance i ON (p.AD_Process_ID=i.AD_Process_ID) "
            + "WHERE p.IsActive='Y'"
            + " AND i.AD_PInstance_ID=?";
    if (!Env.isBaseLanguage(m_wscctx, "AD_Process"))
      sql =
          "SELECT t.Name, p.ProcedureName,p.ClassName, p.AD_Process_ID," //	1..4
              + " p.isReport, p.IsDirectPrint,p.AD_ReportView_ID,p.AD_Workflow_ID," //	5..8
              + " CASE WHEN COALESCE(p.Statistic_Count,0)=0 THEN 0 ELSE p.Statistic_Seconds/p.Statistic_Count END CASE,"
              + " p.IsServerProcess "
              + "FROM AD_Process p"
              + " INNER JOIN AD_PInstance i ON (p.AD_Process_ID=i.AD_Process_ID) "
              + " INNER JOIN AD_Process_Trl t ON (p.AD_Process_ID=t.AD_Process_ID"
              + " AND t.AD_Language='"
              + Env.getAD_Language(m_wscctx)
              + "') "
              + "WHERE p.IsActive='Y'"
              + " AND i.AD_PInstance_ID=?";
    //
    try {
      PreparedStatement pstmt =
          DB.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, null);
      pstmt.setInt(1, m_pi.getAD_PInstance_ID());
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) {
        m_pi.setTitle(rs.getString(1));
        if (m_waiting != null) m_waiting.setTitle(m_pi.getTitle());
        ProcedureName = rs.getString(2);
        m_pi.setClassName(rs.getString(3));
        m_pi.setAD_Process_ID(rs.getInt(4));
        //	Report
        if ("Y".equals(rs.getString(5))) {
          IsReport = true;
          if ("Y".equals(rs.getString(6)) && !Ini.isPropertyBool(Ini.P_PRINTPREVIEW))
            IsDirectPrint = true;
        }
        AD_ReportView_ID = rs.getInt(7);
        AD_Workflow_ID = rs.getInt(8);
        //
        int estimate = rs.getInt(9);
        if (estimate != 0) {
          m_pi.setEstSeconds(estimate + 1); //  admin overhead
          if (m_waiting != null) m_waiting.setTimerEstimate(m_pi.getEstSeconds());
        }
        m_IsServerProcess = "Y".equals(rs.getString(10));
      } else log.log(Level.SEVERE, "No AD_PInstance_ID=" + m_pi.getAD_PInstance_ID());
      rs.close();
      pstmt.close();
    } catch (SQLException e) {
      m_pi.setSummary(
          Msg.getMsg(m_wscctx, "ProcessNoProcedure") + " " + e.getLocalizedMessage(), true);
      // unlock();
      log.log(Level.SEVERE, "run", e);
      return;
    }

    //  No PL/SQL Procedure
    if (ProcedureName == null) ProcedureName = "";

    /** ******************************************************************** Workflow */
    if (AD_Workflow_ID > 0) {
      startWorkflow(AD_Workflow_ID);
      // unlock();
      return;
    }

    /** ******************************************************************** Start Optional Class */
    if (m_pi.getClassName() != null) {
      //	Run Class
      if (!startProcess()) {
        // unlock();
        return;
      }

      //  No Optional SQL procedure ... done
      if (!IsReport && ProcedureName.length() == 0) {
        // unlock ();
        return;
      }
      //  No Optional Report ... done
      if (IsReport && AD_ReportView_ID == 0) {
        // unlock ();
        return;
      }
    }

    //  If not a report, we need a prodedure name
    if (!IsReport && ProcedureName.length() == 0) {
      m_pi.setSummary(Msg.getMsg(m_wscctx, "ProcessNoProcedure"), true);
      // unlock();
      return;
    }

    /** ******************************************************************** Report submission */
    if (IsReport) {
      //	Optional Pre-Report Process
      if (ProcedureName.length() > 0) {
        if (!startDBProcess(ProcedureName)) {
          // unlock();
          return;
        }
      } //	Pre-Report

      //	Start Report	-----------------------------------------------
      boolean ok = ReportCtl.start(m_pi, IsDirectPrint);
      m_pi.setSummary("Report", !ok);
      // unlock ();
    }
    /** ******************************************************************** Process submission */
    else {
      if (!startDBProcess(ProcedureName)) {
        // unlock();
        return;
      }
      //	Success - getResult
      ProcessInfoUtil.setSummaryFromDB(m_pi);
      // unlock();
    } //	*** Process submission ***
    //	log.fine(Log.l3_Util, "ProcessCtl.run - done");
  } //  run
Пример #22
0
  /**
   * Create Period Controls
   *
   * @param ctx context
   * @param AD_Client_ID client
   * @param sp server process
   * @param trx transaction
   */
  public static void createPeriodControls(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) {
    s_log.info("AD_Client_ID=" + AD_Client_ID);

    //	Delete Duplicates
    String sql =
        "DELETE FROM C_PeriodControl "
            + "WHERE (C_Period_ID, DocBaseType) IN "
            + "(SELECT C_Period_ID, DocBaseType "
            + "FROM C_PeriodControl pc2 "
            + "GROUP BY C_Period_ID, DocBaseType "
            + "HAVING COUNT(*) > 1)"
            + " AND C_PeriodControl_ID NOT IN "
            + "(SELECT MIN(C_PeriodControl_ID) "
            + "FROM C_PeriodControl pc3 "
            + "GROUP BY C_Period_ID, DocBaseType)";
    int no = DB.executeUpdate(trx, sql);
    s_log.info("Duplicates deleted #" + no);

    //	Insert Missing
    sql =
        "SELECT DISTINCT p.AD_Client_ID, p.C_Period_ID, dbt.DocBaseType "
            + "FROM C_Period p, "
            + "C_DocBaseType dbt "
            + "WHERE p.AD_Client_ID=? "
            + " AND NOT EXISTS"
            + " (SELECT * FROM C_PeriodControl pc "
            + "WHERE pc.C_Period_ID=p.C_Period_ID AND pc.DocBaseType=dbt.DocBaseType)"
            + " AND (dbt.AD_Client_ID = 0 OR p.AD_Client_ID = dbt.AD_Client_ID)";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int counter = 0;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, AD_Client_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        int Client_ID = rs.getInt(1);
        int C_Period_ID = rs.getInt(2);
        String DocBaseType = rs.getString(3);
        s_log.config(
            "AD_Client_ID="
                + Client_ID
                + ", C_Period_ID="
                + C_Period_ID
                + ", DocBaseType="
                + DocBaseType);
        //
        MPeriodControl pc = new MPeriodControl(ctx, Client_ID, C_Period_ID, DocBaseType, trx);
        if (pc.save()) {
          counter++;
          s_log.fine(pc.toString());
        } else s_log.warning("Not saved: " + pc);
      }
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    if (sp != null) sp.addLog(0, null, new BigDecimal(counter), "@C_PeriodControl_ID@ @Created@");
    s_log.info("Inserted #" + counter);
  } //	createPeriodControls
  /** Dispose */
  public void dispose() {
    if (m_frame != null) m_frame.dispose();
    m_frame = null;

    MOrder order = new MOrder(Env.getCtx(), LineRefProv.getC_Order_ID(), null);
    String oS = order.getXX_OrderStatus();
    String compS = order.getDocStatus();
    if (!oS.equals("AN") && !compS.equals("CO")) {

      if (isInMatrix()) {

        fullAssociated();

      } else {
        String SQL10 =
            "UPDATE XX_VMR_PO_LineRefProv "
                + " SET XX_ReferenceIsAssociated='N'"
                + " WHERE XX_VMR_PO_LineRefProv_ID="
                + LineRefProv.getXX_VMR_PO_LineRefProv_ID();

        DB.executeUpdate(null, SQL10);
        //				LineRefProv.setXX_ReferenceIsAssociated(false);
        //				LineRefProv.save();
      }
    }

    // pelle

    String SQL =
        ("SELECT A.XX_REFERENCEISASSOCIATED AS ASOCIATE "
            + "FROM XX_VMR_PO_LINEREFPROV A , C_ORDER B "
            + "WHERE A.C_ORDER_ID = B.C_ORDER_ID "
            + "AND A.C_ORDER_ID = '"
            + LineRefProv.getC_Order_ID()
            + "' ");

    try {

      PreparedStatement pstmt = DB.prepareStatement(SQL, null);
      ResultSet rs = pstmt.executeQuery();

      BigDecimal cifTotal = new BigDecimal(0);
      Boolean check = true;
      Integer maritimo = 0;
      Integer aereo = 0;
      Integer terrestre = 0;
      BigDecimal montoBs = new BigDecimal(0);
      BigDecimal rate = new BigDecimal(0);
      BigDecimal Seguro = new BigDecimal(0);
      BigDecimal perfleteInt = new BigDecimal(0);
      BigDecimal fleteInternac = new BigDecimal(0);

      Integer productid = 0;
      Integer poline = 0;
      BigDecimal costoitem = new BigDecimal(0);
      BigDecimal porcentitem = new BigDecimal(0);
      BigDecimal cifuni = new BigDecimal(0);
      BigDecimal percentarancel = new BigDecimal(0);
      BigDecimal arancelitem = new BigDecimal(0);
      BigDecimal rateseniat = new BigDecimal(0);
      BigDecimal montoseniat = new BigDecimal(0);
      BigDecimal ratetesoreria = new BigDecimal(0);
      BigDecimal montotesoreria = new BigDecimal(0);
      BigDecimal tasaaduana = new BigDecimal(0);
      BigDecimal part1 = new BigDecimal(0);
      BigDecimal part2 = new BigDecimal(0);
      BigDecimal iva = new BigDecimal(0);
      BigDecimal ivaactual = new BigDecimal(0);
      BigDecimal part = new BigDecimal(0);
      BigDecimal montesorerianac = new BigDecimal(0);

      while (rs.next()) {
        if (rs.getString("ASOCIATE").trim().equalsIgnoreCase("N")) {
          check = false;
        }
      }
      rs.close();
      pstmt.close();
      if (check == true) {

        String SQL2 =
            ("SELECT (A.TOTALLINES * B.MULTIPLYRATE) + A.XX_INTNACESTMEDAMOUNT + A.XX_ESTEEMEDINSURANCEAMOUNT  AS CIFTOTAL "
                + "FROM C_ORDER A, C_CONVERSION_RATE B "
                + "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
                + "AND A.C_ORDER_ID = '"
                + LineRefProv.getC_Order_ID()
                + "' "
                + "AND A.AD_Client_ID IN(0,"
                + Env.getCtx().getAD_Client_ID()
                + ") "
                + "AND B.AD_Client_ID IN(0,"
                + Env.getCtx().getAD_Client_ID()
                + ")");

        PreparedStatement pstmt2 = DB.prepareStatement(SQL2, null);
        ResultSet rs2 = pstmt2.executeQuery();

        if (rs2.next()) {
          // Calculo del CIF Total

          cifTotal = rs2.getBigDecimal("CIFTOTAL");
        }
        rs2.close();
        pstmt2.close();

        // Busco la via de despacho de la O/C
        String SQL3 =
            ("SELECT XX_L_DISPATCHROUTE AS AEREO, XX_L_DISPATCHROUTEMAR AS MARITIMO, XX_L_DISPATCHROUTETER AS TERRESTRE "
                + "FROM XX_VSI_KEYNAMEINFO ");

        PreparedStatement pstmt3 = DB.prepareStatement(SQL3, null);
        ResultSet rs3 = pstmt3.executeQuery();

        if (rs3.next()) {
          maritimo = rs3.getInt("MARITIMO");
          terrestre = rs3.getInt("TERRESTRE");
          aereo = rs3.getInt("AEREO");
        }
        rs3.close();
        pstmt3.close();

        String SQL4 =
            ("SELECT (A.TOTALLINES * B.MULTIPLYRATE) AS MONTO "
                + "FROM C_ORDER A, C_CONVERSION_RATE B "
                + "WHERE B.C_CONVERSION_RATE_ID = A.XX_CONVERSIONRATE_ID "
                + "AND A.C_ORDER_ID = '"
                + LineRefProv.getC_Order_ID()
                + "' ");

        PreparedStatement pstmt4 = DB.prepareStatement(SQL4, null);
        ResultSet rs4 = pstmt4.executeQuery();

        if (rs4.next()) {
          // Monto de la O/C en Bs
          montoBs = rs4.getBigDecimal("MONTO");
        }
        rs4.close();
        pstmt4.close();

        if (order.getXX_VLO_DispatchRoute_ID() == maritimo) {

          String SQL5 =
              ("SELECT XX_RATE AS RATE "
                  + "FROM XX_VLO_DispatchRoute "
                  + "WHERE XX_VLO_DispatchRoute_ID = '"
                  + maritimo
                  + "' ");

          PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
          ResultSet rs5 = pstmt5.executeQuery();

          if (rs5.next()) {
            rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100));
            // (new BigDecimal(100), 2, RoundingMode.HALF_UP);

            // Calculo del Seguro con Via Maritimo
            Seguro = montoBs.multiply(rate);
          }
          rs5.close();
          pstmt5.close();

        } else if (order.getXX_VLO_DispatchRoute_ID() == aereo) {
          String SQL5 =
              ("SELECT XX_RATE AS RATE "
                  + "FROM XX_VLO_DispatchRoute "
                  + "WHERE XX_VLO_DispatchRoute_ID = '"
                  + aereo
                  + "' ");

          PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
          ResultSet rs5 = pstmt5.executeQuery();

          if (rs5.next()) {
            rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);

            // Calculo del Seguro con Via aereo
            Seguro = montoBs.multiply(rate);
          }
          rs5.close();
          pstmt5.close();
        } else if (order.getXX_VLO_DispatchRoute_ID() == terrestre) {
          String SQL5 =
              ("SELECT XX_RATE AS RATE "
                  + "FROM XX_VLO_DispatchRoute "
                  + "WHERE XX_VLO_DispatchRoute_ID = '"
                  + terrestre
                  + "' ");

          PreparedStatement pstmt5 = DB.prepareStatement(SQL5, null);
          ResultSet rs5 = pstmt5.executeQuery();

          if (rs5.next()) {
            rate = rs5.getBigDecimal("RATE").divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);

            // Calculo del Seguro con Via terrestre
            Seguro = montoBs.multiply(rate);
          }
          rs5.close();
          pstmt5.close();
        }

        // Busco el porcentaje del flete internacional segun el proveedor, puerto y pais de la O/C

        String SQL6 =
            ("SELECT DISTINCT A.XX_INTERFREESTIMATEPERT AS PERTFLEINTER "
                + "FROM XX_VLO_COSTSPERCENT A, C_ORDER B "
                + "WHERE B.XX_VLO_ARRIVALPORT_ID = '"
                + order.getXX_VLO_ArrivalPort_ID()
                + "' "
                + "AND B.C_BPARTNER_ID = '"
                + order.getC_BPartner_ID()
                + "' "
                + "AND B.C_COUNTRY_ID = '"
                + order.getC_Country_ID()
                + "' "
                + "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
                + "AND B.XX_VLO_ARRIVALPORT_ID = A.XX_VLO_ARRIVALPORT_ID "
                + "AND B.C_BPARTNER_ID = A.C_BPARTNER_ID "
                + "AND B.C_COUNTRY_ID = A.C_COUNTRY_ID ");

        PreparedStatement pstmt6 = DB.prepareStatement(SQL6, null);
        ResultSet rs6 = pstmt6.executeQuery();

        if (rs6.next()) {
          // Porcentaje del flete internacional
          perfleteInt =
              rs6.getBigDecimal("PERTFLEINTER")
                  .divide(new BigDecimal(100), 4, RoundingMode.HALF_UP);
          // Flete Internacional
          fleteInternac = montoBs.multiply(perfleteInt);
        }
        rs6.close();
        pstmt6.close();

        // Busco los productos que tienen asociados las Ref de la O/C

        String SQL7 =
            ("SELECT C.M_PRODUCT AS PRODUCT, A.XX_VMR_PO_LINEREFPROV_ID AS POLINE "
                + "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C "
                + "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
                + "AND A.C_ORDER_ID = B.C_ORDER_ID "
                + "AND A.C_ORDER_ID = '"
                + LineRefProv.getC_Order_ID()
                + "' ");

        PreparedStatement pstmt7 = DB.prepareStatement(SQL7, null);
        ResultSet rs7 = pstmt7.executeQuery();

        while (rs7.next()) {
          // Calculo del Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
          productid = rs7.getInt("PRODUCT");
          poline = rs7.getInt("POLINE");

          // Costo de cada item Bs

          /*String SQL8 = ("SELECT (A.XX_UNITPURCHASEPRICE / B.XX_UNITCONVERSION) AS COSTO " +
          "FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_UNITCONVERSION B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D " +
          "WHERE A.XX_VMR_UNITCONVERSION_ID = B.XX_VMR_UNITCONVERSION_ID " +
          "AND A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID " +
          "AND C.M_PRODUCT = D.M_PRODUCT_ID " +
          "AND C.M_PRODUCT = '"+productid+"' " +
          "AND A.XX_VMR_PO_LINEREFPROV_ID = '"+poline+"' ");*/

          String SQL8 =
              ("SELECT (A.LINENETAMT * E.MULTIPLYRATE) AS COSTO "
                  + "FROM XX_VMR_PO_LINEREFPROV A, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D, C_CONVERSION_RATE E, C_ORDER F  "
                  + "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
                  + "AND C.M_PRODUCT = D.M_PRODUCT_ID "
                  + "AND E.C_CONVERSION_RATE_ID = F.XX_CONVERSIONRATE_ID "
                  + "AND A.C_ORDER_ID = F.C_ORDER_ID "
                  + "AND C.M_PRODUCT = '"
                  + productid
                  + "' "
                  + "AND A.XX_VMR_PO_LINEREFPROV_ID = '"
                  + poline
                  + "' ");

          PreparedStatement pstmt8 = DB.prepareStatement(SQL8, null);
          ResultSet rs8 = pstmt8.executeQuery();

          if (rs8.next()) {
            costoitem = rs8.getBigDecimal("COSTO");

            // Porcentaje de cada item = costo de cada item Bs / Monto O/C Bs
            porcentitem = (costoitem.divide(montoBs, 8, RoundingMode.HALF_UP));

            // CIF Unitario = Porcentaje de cada Item * CIF total
            cifuni = porcentitem.multiply(cifTotal);

            // Busco Porcentaje Arancelario
            String SQL9 =
                ("SELECT (D.XX_PERCENTAGETARIFF/100) AS PERARANCEL "
                    + "FROM XX_VMR_PO_LINEREFPROV A, C_ORDER B, XX_VMR_REFERENCEMATRIX C, M_PRODUCT D "
                    + "WHERE A.XX_VMR_PO_LINEREFPROV_ID = C.XX_VMR_PO_LINEREFPROV_ID "
                    + "AND A.C_ORDER_ID = B.C_ORDER_ID "
                    + "AND C.M_PRODUCT = D.M_PRODUCT_ID "
                    + "AND D.M_PRODUCT_ID = '"
                    + productid
                    + "' "
                    + "AND A.C_ORDER_ID = '"
                    + LineRefProv.getC_Order_ID()
                    + "' ");

            PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
            ResultSet rs9 = pstmt9.executeQuery();

            if (rs9.next()) {
              // Porcentaje Arancelario
              percentarancel = rs9.getBigDecimal("PERARANCEL");

              // Arancel de cada item = CIF unitario * Porcentaje Arancelario
              arancelitem = cifuni.multiply(percentarancel).add(arancelitem);
            }
            rs9.close();
            pstmt9.close();
          }
          rs8.close();
          pstmt8.close();

          // cif total(Creo que no hace falta) t arancel item se usan abajo
        } // end While rs7
        rs7.close();
        pstmt7.close();

        String SQL9 =
            ("SELECT XX_RATE/100 AS RATESENIAT FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio de aduanas SENIAT' "
                + "And AD_Client_ID IN(0,"
                + Env.getCtx().getAD_Client_ID()
                + ")");

        PreparedStatement pstmt9 = DB.prepareStatement(SQL9, null);
        ResultSet rs9 = pstmt9.executeQuery();

        if (rs9.next()) {
          rateseniat = rs9.getBigDecimal("RATESENIAT");
          montoseniat = arancelitem.multiply(rateseniat);

          String SQL10 =
              ("SELECT XX_RATE/100 AS RATETESORERIA FROM XX_VLO_IMPORTRATE WHERE NAME = 'Tasa de Servicio Aduana Tesorería' "
                  + "And AD_Client_ID IN(0,"
                  + Env.getCtx().getAD_Client_ID()
                  + ")");

          PreparedStatement pstmt10 = DB.prepareStatement(SQL10, null);
          ResultSet rs10 = pstmt10.executeQuery();

          if (rs10.next()) {
            ratetesoreria = rs10.getBigDecimal("RATETESORERIA");
            montotesoreria = arancelitem.multiply(ratetesoreria);

            // Monto Tasa aduanera =monto tasa seniat + monto tasa tesoreria
            tasaaduana = montoseniat.add(montotesoreria);
          }
          rs10.close();
          pstmt10.close();
        }
        rs9.close();
        pstmt9.close();

        // Calculo del IVA = (CIF total + Impuesto de importación + tasa aduanera) *%iva actual

        String SQL11 =
            ("SELECT MAX (VALIDFROM) AS FECHA, A.RATE/100 AS IVACT "
                + "FROM C_TAX A, C_TAXCATEGORY B "
                + "WHERE A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
                + "AND B.DESCRIPTION = 'Impuesto al Valor Agregado' "
                + "AND A.C_TAXCATEGORY_ID = B.C_TAXCATEGORY_ID "
                + "GROUP BY A.RATE ");

        PreparedStatement pstmt11 = DB.prepareStatement(SQL11, null);
        ResultSet rs11 = pstmt11.executeQuery();

        if (rs11.next()) {
          part1 = cifTotal.add(arancelitem);
          part2 = part1.add(tasaaduana);
          iva = part2.multiply(rs11.getBigDecimal("IVACT"));
        }
        rs11.close();
        pstmt11.close();

        // Monto tesorería Nacional = IVA + impuesto de importación + monto tasa tesoreria

        part = iva.add(arancelitem);
        montesorerianac = part.add(montotesoreria);

        // redondeo los BigDecimal
        montesorerianac = montesorerianac.setScale(2, BigDecimal.ROUND_UP);
        montoseniat = montoseniat.setScale(2, BigDecimal.ROUND_UP);

        String sql =
            "UPDATE C_Order po"
                + " SET XX_NatTreasuryEstAmount="
                + montesorerianac
                + "    , XX_SENIATESTEEMEDAMUNT="
                + montoseniat
                + " WHERE po.C_Order_ID="
                + LineRefProv.getC_Order_ID();
        DB.executeUpdate(null, sql);

      } // end if Check
      else {
        // alguna referencia no tiene producto asociado
        String sql =
            "UPDATE C_Order po"
                + " SET XX_NatTreasuryEstAmount="
                + 0
                + "    , XX_SENIATESTEEMEDAMUNT="
                + 0
                + " WHERE po.C_Order_ID="
                + LineRefProv.getC_Order_ID();
        DB.executeUpdate(null, sql);
      }

    } // end try
    catch (Exception e) {

    }
  } //	dispose