/** * 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; }
/** * 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; }
/** * 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; }