private String getMonth(String month) {
String query;
String i_month = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";
USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
rs = pstmt.executeQuery();
if (rs.next()) {
i_month = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return i_month;
}
/**
* 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 name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* 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 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 check if the passed start date for the Journal Month starts
* exactly after the Previous end date.
*
* @exception SQLException, if query fails
* @author
*/
public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
if (month.length() > ((new Integer("2")).intValue())) {
month = getMonth(month);
}
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn = ?";
} else {
query = query + " and bp_rgn is null";
}
query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";
query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";
USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
pstmt.setString(2, month);
pstmt.setString(3, blgsys);
int m = 4;
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(m, rgn);
m = m + 1;
}
pstmt.setString(m, month);
pstmt.setString(m + 1, year);
pstmt.setString(m + 2, year);
pstmt.setString(m + 3, strtdat);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Journal Dates for the passed in Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getJrnldts(String year) {
String query;
Vector jrnllst = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
query =
query
+ "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
query =
query
+ "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
query = query + "from blg_prd,blg_sys ";
query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
query = query + "(select strt_dat from fung_yr where yr = ?) and ";
query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
query = query + "order by ord,nm,bp_strt_dat ";
USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
pstmt.setString(2, year);
pstmt.setString(3, year);
rs = pstmt.executeQuery();
while (rs.next()) {
Dinvjrnl jrnldts = new Dinvjrnl(null);
jrnldts.strBpid = rs.getString(1);
jrnldts.strBlgsys = rs.getString(3);
jrnldts.strBlgmnth = rs.getString(5);
jrnldts.strBlgstrtdt = rs.getString(6);
jrnldts.strBlgenddt = rs.getString(7);
jrnllst.addElement(jrnldts);
jrnldts = null;
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
return jrnllst;
}
/** This method used to connect to the Conection pool */
public void connect() throws ConnectException, RemoteException {
try {
conn = null;
conn = dbconn.open();
USFEnv.getLog().writeDebug("created db connection(), ref: " + conn.toString(), this, null);
} catch (Exception e) {
throw new ConnectException("Database connection failure");
}
}
/** This method used to release the conection back to the Connection Pool */
public void release() throws ConnectException, RemoteException {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (Exception ex) {
throw new ConnectException("Database connection not released in workorderdetailBean");
}
}
/**
* Business unit of work This method is used to view a particular selected Customer
*
* @param customer Id
* @return customer details
*/
public RhccCustInfos viewCustomer(String customerId) throws RemoteException {
RhccCustInfos cTmp = null;
USFEnv.getLog().writeWarn(" Inside viewCustomer", this, null);
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
if (conn != null) {
cTmp = rhcc_obj.searchCustomerInfo(customerId);
USFEnv.getLog()
.writeDebug("viewCustomer DB connection, ref: " + conn.toString(), this, null);
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return cTmp;
}
/**
* Business unit of work This method is used to delete an existing customer
*
* @param customer Id
* @return true or false whether the customer is deleted or not
*/
public boolean delCustomer(String customerId) throws RemoteException {
USFEnv.getLog().writeWarn(" Inside delCustomer", this, null);
boolean bTmp = false;
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
if (conn != null) {
bTmp = rhcc_obj.deleteCustomer(Long.parseLong(customerId));
USFEnv.getLog()
.writeDebug("delCustomer DB connection, ref: " + conn.toString(), this, null);
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return bTmp;
}
/**
* Business unit of work This method is used to update an existing customer after editing
*
* @param customer details
* @return true or false whether the customer is updated or not
*/
public boolean updateCustomer(RhccCustInfos updateCustomer) throws RemoteException {
USFEnv.getLog().writeWarn(" Inside updateCustomer", this, null);
boolean bTmp = false;
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
if (conn != null) {
bTmp = rhcc_obj.updateCustomerInfo(updateCustomer);
USFEnv.getLog()
.writeDebug("newCustomer DB connection, ref: " + conn.toString(), this, null);
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return bTmp;
}
/**
* 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;
}
/**
* Business unit of work This method show all the RHCC customers for a funding year
*
* @param year - funding year
* @return vCustnmid - Customers for a Funding Year
*/
public Vector getAllRHCCCustomers(short year) throws RemoteException, NullPointerException {
Vector vCustnmid = null;
USFEnv.getLog().writeWarn("Inside getAllRHCCCustomers", this, null);
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos();
try {
vCustnmid = rhcc_obj.getAllRHCCCustomers(year);
USFEnv.getLog()
.writeDebug("getAllRHCCCustomers DB connection, ref: " + conn.toString(), this, null);
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return vCustnmid;
}
/**
* 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;
}
/**
* Business unit of work This method is used to save a new Customer or an existing customer after
* editing
*
* @param new customer details
* @return true or false whether the customer is saved or not
*/
public boolean saveCustomer(RhccCustInfos saveCustomer, Hashtable custErrors)
throws RemoteException {
USFEnv.getLog().writeWarn(" Inside saveCustomer", this, null);
long customerId;
boolean bCustErr = false;
boolean bTmp = false;
// Get the connection
RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
try {
customerId = saveCustomer.getCustomerId();
if (conn != null) {
USFEnv.getLog()
.writeDebug("saveCustomer DB connection, ref: " + conn.toString(), this, null);
}
// Check business logic field value validation
bCustErr = rhcccustValidate.customerBusVal(saveCustomer, custErrors);
// Check if critial error occured
if (!bCustErr) {
// Saving a new customer
if (customerId == 0) {
// One time funding year set for new customer record
bTmp = newCustomer(saveCustomer);
USFEnv.getLog()
.writeWarn(
"new RhccCustInfo created, RCI_ID: " + saveCustomer.getCustomerId(), this, null);
} else {
// Saving an exiting customer
bTmp = updateCustomer(saveCustomer);
}
}
} catch (Exception e) {
USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
}
return bTmp;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkDuplicate(String blgsys, String year, String month, String rgn) {
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from blg_prd where bs_id_fk=" + blgsys + " and bp_year=" + year;
query = query + " and bp_month=" + month;
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn='" + rgn + "'";
} else {
query = query + " and bp_rgn is null";
}
USFEnv.getLog().writeDebug("Dinvjrnl:check Duplicate- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
pstmt.setString(2, year);
pstmt.setString(3, month);
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(4, rgn);
}
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Comparison Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method Inserts Journal Dates in BLG_PRD table.
*
* @exception SQLException, if Insertion fails
* @author
*/
public boolean insertJrnldts(
String bpid,
String blgsys,
String rgn,
String strtdt,
String enddt,
String month,
String year) {
String query;
boolean insert_flag = false;
PreparedStatement pstmt = null;
query =
"Insert into blg_prd (bp_id,bs_id_fk,bp_year,bp_month,bp_strt_dat,bp_end_dat,bp_rgn) values ";
query = query + "(?,?,?,?,to_date(?,'MM/DD/YYYY') ,to_date(?,'MM/DD/YYYY') ,?) ";
USFEnv.getLog().writeDebug("Dinvjrnl:Insertion - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
pstmt.setString(2, blgsys);
pstmt.setString(3, year);
pstmt.setString(4, month);
pstmt.setString(5, strtdt);
pstmt.setString(6, enddt);
pstmt.setString(7, rgn);
insert_flag = (pstmt.executeUpdate() != 0) ? true : false;
if (pstmt != null) pstmt.close();
return insert_flag;
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: The Insertion of Journal Dates failed", this, ex);
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method updates the BLG_PRD table.
*
* @exception SQLException, if query fails
* @author
*/
public boolean updateJrnldts(String bpid, String strtdt, String enddt) {
String query;
boolean update_flag = false;
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"update blg_prd set bp_strt_dat=to_date(?,'MM/DD/YYYY') , bp_end_dat=to_date(?,'MM/DD/YYYY') where bp_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl:Updation- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, strtdt);
pstmt.setString(2, enddt);
pstmt.setString(3, bpid);
update_flag = (pstmt.executeUpdate() != 0) ? true : false;
if (pstmt != null) pstmt.close();
return update_flag;
} catch (SQLException ex) {
try {
if (pstmt != null) pstmt.close();
} catch (Exception e) {
USFEnv.getLog().writeCrit("Unable to close statement", this, e);
}
USFEnv.getLog().writeCrit("Dinvjrnl: The Updation of Journal Dates failed", this, ex);
} catch (Exception e) {
try {
if (pstmt != null) pstmt.close();
} catch (Exception ex) {
USFEnv.getLog().writeCrit("Unable to close statement", this, ex);
}
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
return false;
}
/**
* 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 sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkDates(String strtdt, String enddt, String year) {
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from fung_yr where to_date(?,'MM/DD/YYYY') < to_date(?,'MM/DD/YYYY') ";
query = query + "and yr = ? and strt_dat-10 <= to_date(?,'MM/DD/YYYY') ";
query = query + "and end_dat >= to_date(?,'MM/DD/YYYY') ";
USFEnv.getLog().writeDebug("Dinvjrnl: Check Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, strtdt);
pstmt.setString(2, enddt);
pstmt.setString(3, year);
pstmt.setString(4, strtdt);
pstmt.setString(5, enddt);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Comparison Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* 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;
}
/**
* This method searches the tables FRNS, FRN_DETS, FRN_DET_HSTIES for the presence of a record in
* a specific query. It returns the invoice line number.
*
* @param <code>StageUsacForm</code> The STAGE_USAC object.
* @return long containing the invoice line number.
*/
public long searchForAmount(java.sql.Connection conn) {
// the buffer for the query
StringBuffer sbQuery = null;
long returnAmt = 0;
// Vector invline=null;
PreparedStatement psStmt = null;
ResultSet rsResult = null;
try {
// init the buffer
sbQuery = new StringBuffer("");
// build the query
sbQuery.append("SELECT max(INV_LN_NO) AS INV_LN_NO_MAX ");
sbQuery.append("FROM (SELECT sum(CRDT_AMT) AMT, INV_LN_NO ");
sbQuery.append("FROM FRNS, FRN_DETS, FRN_DET_HSTIES, INV ");
sbQuery.append("WHERE ");
sbQuery.append("FRN_ID=FRN_ID_FK AND FD_ID=FD_ID_FK AND INV_STAT='I' AND ");
sbQuery.append("INV_ID_FK=INV_ID");
// sbQuery.append(" AND FRN = ?'");
sbQuery.append(" AND FRN = ? ");
// sbQuery.append(this.getFrn());
// sbQuery.append("' GROUP BY INV_LN_NO) " );
sbQuery.append(" GROUP BY INV_LN_NO) ");
sbQuery.append(" WHERE AMT = ?");
// sbQuery.append(this.getAmtPaid());
// write to log if in debug mode
USFEnv.getLog()
.writeDebug(
"searchForAmount(StageUsacForm) " + "sbQuery is " + sbQuery.toString() + "\n",
this,
null);
psStmt = conn.prepareStatement(sbQuery.toString());
// psStmt.setLong(1,this.getFrn());
psStmt.setString(1, String.valueOf(this.getFrn()));
psStmt.setDouble(2, this.getAmtPaid());
rsResult = psStmt.executeQuery();
if (rsResult.next()) {
returnAmt = rsResult.getLong(1);
}
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (Exception e) {
try {
if (rsResult != null) rsResult.close();
if (psStmt != null) psStmt.close();
} catch (Exception ex) {
USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, ex);
}
USFEnv.getLog()
.writeCrit("searchForAmount(StageUsacForm) Failed Query:" + sbQuery.toString(), this, e);
}
/* try
{
if( rsResult != null)
rsResult.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
}
try
{
if( psStmt != null)
psStmt.close();
}
catch(Exception e)
{
USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
}*/
return (returnAmt);
} // searchForAmount
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;
}