Beispiel #1
0
  /**
   * This method queries the database to get the sequence for the BP_ID.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBpid() {
    String query;
    String bpid = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select bp_id_seq.nextval from dual ";

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        bpid = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return bpid;
  }
  /**
   * This method queries the database to validate the passed in FRN for the selected Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean validateFRN(String frn, String year) {
    String query;
    boolean validfrn = false;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        validfrn = true;
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return false;
  }
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public long getInvID(String inv_no) {
    String query;
    long invID = 0;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_id from inv where inv_no=" + inv_no;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invID = rs.getLong(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return (invID);
  }
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getInvno(String invid) {
    String query;
    String invno = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invno = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return invno;
  }
  /**
   * This method queries the database to get the SPIN associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getSPINname(String invid) {
    String query;
    String spinnm = "";
    Statement stmt = null;
    ResultSet rs = null;

    //		query =  "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
    query =
        "select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
            + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        spinnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return spinnm;
  }
  /**
   * This method queries the database to validate the passed in FRN for the selected Funding Year
   * has been invoiced or not
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean checkFRNinv(String frn, String year) {
    String query;
    boolean validfrn = false;
    Statement stmt = null;
    ResultSet rs = null;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + frn, this, null);
    USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + year, this, null);

    query = " SELECT DISTINCT rhcc_inv_id_fk ";
    query = query + " FROM wrk_ordr, wrk_ordr_dets, wo_det_hsties ";
    query = query + " WHERE wo_id = wo_id_fk ";
    query = query + " AND wod_id = wod_id_fk ";
    query = query + " AND wdh_stat = 'P' ";
    query = query + " AND inv_stat = 'I' ";
    query = query + " AND wrk_ordr_no ='" + frn + "' ";
    query = query + " AND wrk_ordr.yr_fk =" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + query, this, null);

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      USFEnv.getLog().writeCrit("RhccDinvview: FRN  Invoiced" + rs.getFetchSize(), this, null);
      if (rs.next()) {
        USFEnv.getLog()
            .writeCrit("RhccDinvview: FRN  Invoiced" + rs.getString("rhcc_inv_id_fk"), this, null);
        validfrn = true;
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: FRN not Invoiced", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return false;
  }
  /**
   * This method queries the database to get the Invoice Numbers list for a particular FRN in a
   * Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getFrninvnos(String frn, String year) {
    String query;
    Vector invnos = new Vector();
    Statement stmt = null;
    ResultSet rs = null;

    USFEnv.getLog().writeWarn("Inside the block-->>RhccDinview Inview", this, null);

    query =
        " select distinct rhcc_inv_id_fk,rhcc_inv.inv_no||' - '||to_char(rhcc_inv.inv_dat,'Mon/DD/YYYY') ";
    query = query + " from wrk_ordr,wrk_ordr_dets,wo_det_hsties,rhcc_inv,fung_yr ";
    query = query + " where wo_id=wo_id_fk and wod_id=wod_id_fk and ";
    query = query + " wdh_stat='P' and ";
    query = query + " inv_stat is not null and ";
    query = query + " rhcc_inv_id_fk=rhcc_inv_id and ";
    query = query + " wrk_ordr_no='" + frn + "' and ";
    query = query + " wrk_ordr.yr_fk=fung_yr.yr and ";
    query = query + " wrk_ordr.yr_fk=" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number List Query:" + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        invnos.addElement(rs.getString(1));
        invnos.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog()
          .writeCrit("RhccDinvview: The Invoice Numbers List for FRN not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return invnos;
  }
Beispiel #8
0
  /**
   * This method queries the database to get the list of the Future Years.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getFutureyears() {
    String query;
    Vector years = new Vector();
    Statement stmt = null;
    ResultSet rs = null;

    query = " select yr,yr ||'-'|| (yr+1) from fung_yr ";
    query = query + "where yr >= to_number( to_char(sysdate,'YYYY') ) ";
    query = query + "and yr not in ";
    query = query + "( select yr from fung_yr where ";
    query = query + "((sysdate between strt_dat and end_dat) or ";
    query = query + "(sysdate-365 between strt_dat and end_dat) or ";
    query = query + "(sysdate-730 between strt_dat and end_dat)) ) ";

    USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Query :" + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        years.addElement(rs.getString(1));
        years.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Future Years List not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Vector size :" + years.size(), this, null);
    return years;
  }
Beispiel #9
0
  /**
   * This method queries the database to get the list of the Billing Systems.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getBlgsys() {
    String query;
    Vector blgsys = new Vector();
    Statement stmt = null;
    ResultSet rs = null;

    query =
        "select distinct decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord,";
    query =
        query
            + " rtrim(bs_nm||' '||bp_rgn),bs_id_fk||rtrim(bp_rgn) from blg_sys,blg_prd where bs_id=bs_id_fk order by ord";

    USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Query :" + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        blgsys.addElement(rs.getString(3));
        blgsys.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Billing System List not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
      USFEnv.getLog()
          .writeDebug("Dinvjrnl: Billing System Vector size :" + blgsys.size(), this, null);
    }

    return blgsys;
  }
  public Vector getFiledetails(String frn, String invid, String year) {

    String query;
    Vector inv = new Vector();
    Statement stmt = null;
    ResultSet rs = null;

    query = " SELECT rci_st, wrk_ordr_no, s.bs_nm, b.rbk_keys, rci_nm, wod_id, ";
    query = query + " srv_sub_cat_nm, h.bill_dat, TO_CHAR (h.bill_dat, 'MM/DD/YYYY'), ";
    query = query + " TO_CHAR (h.CRDT_AMT, 'FM999999990.00'), ";
    query = query + " inv_stat invstat ";
    query = query + " from ";
    query = query + " rhcc_cust_infos, ";
    query = query + " wo_det_hsties h, ";
    query = query + " wrk_ordr_dets, ";
    query = query + " wrk_ordr, ";
    query = query + " rhcc_blg_keys b, ";
    query = query + " srv_sub_cat, ";
    query = query + " fung_yr, ";
    query = query + " blg_sys s ";
    query = query + " WHERE wo_id = wo_id_fk ";
    query = query + " AND wod_id = wod_id_fk ";
    query = query + " AND rci_id = rci_id_fk ";
    query = query + " AND wdh_stat = 'P' ";
    query = query + " AND b.bs_id_fk != 2 ";
    query = query + " AND inv_stat IS NOT NULL ";
    query = query + " AND ssc_id = ssc_id_fk ";
    query = query + " AND wrk_ordr_dets.rbk_id_fk = b.rbk_id ";
    query = query + " AND b.bs_id_fk = s.bs_id ";
    query = query + " AND fung_yr.yr = wrk_ordr.yr_fk   and  ";

    query = query + "        fung_yr.yr=" + year + " and ";

    if ((frn != null) && (!(frn).equals(""))) {
      query = query + "        wrk_ordr_no='" + frn + "' and ";
    }

    query = query + "        rhcc_inv_id_fk=" + invid + " and  ";
    query = query + " wrk_ordr.sc_id_fk=5 ";

    query = query + " UNION ALL ";

    query = query + " select rci_st,wrk_ordr_no, s.bs_nm||'-'||substr(GETCONDITION( rm_rgn ";
    query = query + " , ";
    query = query + " substr(rbk_keys,1,3) ";
    query = query + " , ";
    query = query + " substr(rbk_keys,4,3) ";
    query = query + " ) ";
    query = query + " ,1,1),b.rbk_keys,rci_nm,wod_id,srv_sub_cat_nm,h.bill_dat, ";
    query =
        query
            + " to_char(h.bill_dat,'MM/DD/YYYY'),to_char(h.CRDT_AMT,'FM999999990.00'),inv_stat invstat ";
    query = query + " from rhcc_cust_infos,wo_det_hsties h,wrk_ordr_dets,wrk_ordr, ";
    query = query + " rhcc_blg_keys b,srv_sub_cat,rgn_map,fung_yr,blg_sys s ";
    query = query + " where wo_id = wo_id_fk ";
    query = query + " AND wod_id = wod_id_fk ";
    query = query + " AND rci_id = rci_id_fk and ";
    query = query + " wdh_stat='P' and ";
    query = query + " b.bs_id_fk=2 and ";
    query = query + " inv_stat is not null and ";
    query = query + " ssc_id=ssc_id_fk and ";
    query = query + " wrk_ordr_dets.RBK_ID_FK = b.RBK_ID AND ";
    query = query + " b.bs_id_fk=s.bs_id and ";
    query = query + " FUNG_YR.YR = wrk_ordr.YR_FK and ";

    query = query + "        fung_yr.yr=" + year + " and ";

    if ((frn != null) && (!(frn).equals(""))) {
      query = query + "        wrk_ordr_no='" + frn + "' and ";
    }

    query = query + "       rhcc_inv_id_fk=" + invid + " and ";
    query = query + " wrk_ordr.sc_id_fk='5' and ";
    query = query + " rm_npa = substr(b.rbk_keys,1,3) ";

    USFEnv.getLog().writeDebug("rhccDinvview:Query :" + query, this, null);

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        RhccDinvview linedet = new RhccDinvview(null);

        linedet.strCuststate = rs.getString(1);
        linedet.strFRN = rs.getString(2);
        linedet.strBlgsys = rs.getString(3);
        linedet.strBlgkey = rs.getString(4);
        linedet.strCustnm = rs.getString(5);
        linedet.strReconkey = rs.getString(6);
        linedet.strProdnm = rs.getString(7);
        linedet.strBdate = rs.getString(9);
        linedet.strProdcost = rs.getString(10);
        linedet.strInvstatus = rs.getString(11);

        inv.addElement(linedet);
        linedet = null;
      }

      if (rs != null) rs.close();
      if (stmt != null) stmt.close();

    } catch (SQLException ex) {
      USFEnv.getLog()
          .writeCrit("RhccDinvgen: The Details of the Line Item not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return inv;
  }