public ArrayList<StruckAccountsCPFGToSiebelBean> getStruckAccountsCPFGToSiebel() {
    ArrayList<StruckAccountsCPFGToSiebelBean> struckAccountsCPFGToSiebelBeanList =
        new ArrayList<StruckAccountsCPFGToSiebelBean>();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    try {
      String originatingSystem = "CPFG";
      conn = DBDatasource.getConnection("eai/DS/EAIXRef");
      String queryString =
          "SELECT CI.COMPONENT_NAME as PROCESS_NAME, CI.CIKEY as instance_id, substr(title , instr(title, ':',1,1)+2,\n"
              + "(instr(title,';',1,1)-(instr(title, ':',1,1)+2)))  as TRUST_ACCOUNT, to_char(CI.MODIFY_DATE,'dd-Mon-yy hh24:mi:ss')  as MODIFY_DATE \n"
              + ", BVSV.varchar2_value as ERROR_MESSAGE FROM soa_soainfra.CUBE_INSTANCE CI , soa_soainfra.VARIABLE_SENSOR_VALUES BVSV \n"
              + "WHERE CI.COMPONENT_NAME = 'CCRMAM_PropagateTRUSTAccount' AND CI.STATE = 5 \n"
              + "AND regexp_like(CI.TITLE,'SBL-AP-Flt'||'$') AND CI.CIKEY = BVSV.process_instance_id \n"
              + "and CI.modify_date>sysdate-15 AND BVSV.SENSOR_NAME = 'ActSenrcvTRUSTAccountUpdate' ORDER BY  to_char(CI.MODIFY_DATE,'dd-Mon-yy hh24:mi:ss') DESC\n";

      stmt = conn.createStatement();
      rset = stmt.executeQuery(queryString);
      String errorMessage = "";
      while (rset.next()) {
        StruckAccountsCPFGToSiebelBean struckAccountsCPFGToSiebelBean =
            new StruckAccountsCPFGToSiebelBean();
        struckAccountsCPFGToSiebelBean.setProcessName(rset.getString(1));
        struckAccountsCPFGToSiebelBean.setInstanceId(rset.getString(2));
        struckAccountsCPFGToSiebelBean.setTrustAccount(rset.getString(3));
        struckAccountsCPFGToSiebelBean.setModifiedDate(rset.getString(4));
        // struckAccountsCPFGToSiebelBean.setErrorMessage(rset.getString(5));
        if (rset.getString(5) == null) {
          struckAccountsCPFGToSiebelBean.setErrorMessage(errorMessage);
        } else {
          struckAccountsCPFGToSiebelBean.setErrorMessage(rset.getString(5));
        }
        struckAccountsCPFGToSiebelBean.setOriginatingSystem(originatingSystem);
        struckAccountsCPFGToSiebelBean.setResolvingSystem(originatingSystem);
        struckAccountsCPFGToSiebelBeanList.add(struckAccountsCPFGToSiebelBean);
      }

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rset != null) {
          rset.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return struckAccountsCPFGToSiebelBeanList;
  }
  public ArrayList<StruckAccountsSiebelToELMBean> getStruckAccountsSiebelToELM(String referenceId) {
    ArrayList<StruckAccountsSiebelToELMBean> struckAccountsSiebelToELMBeanList =
        new ArrayList<StruckAccountsSiebelToELMBean>();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    String queryString = "";
    try {

      String originatingSystem = "Siebel";
      conn = DBDatasource.getConnection("eai/ds/EAIReference");
      if (referenceId == null) {
        queryString =
            "SELECT ib.interface_id INTERFACE_ID,ib.created_by,ib.REFERENCE_ID, to_char(ib.LAST_UPDATED,'dd-Mon-yy hh24:mi:ss') LAST_MODIFIED,ib.error_message,ib.state,ib.updated_by \n"
                + "FROM SOA_EAIOWNER.Interface_buffer ib  WHERE ib.INTERFACE_ID ='ELMAM' and ib.state  in ('E','R','W') and ib.LAST_UPDATED > sysdate-15\n"
                + " ORDER BY to_char(ib.LAST_UPDATED ,'dd-Mon-yy hh24:mi:ss') DESC\n";
      } else {
        queryString =
            "SELECT ib.interface_id INTERFACE_ID,ib.created_by,ib.REFERENCE_ID, to_char(ib.LAST_UPDATED,'dd-Mon-yy hh24:mi:ss') LAST_MODIFIED,ib.error_message,ib.state,ib.updated_by\n"
                + "FROM SOA_EAIOWNER.Interface_buffer ib  WHERE ib.INTERFACE_ID ='ELMAM' and ib.state  in ('E','R','W') and ib.LAST_UPDATED > sysdate-15 and ib.REFERENCE_ID='"
                + referenceId
                + "' ORDER BY to_char(ib.LAST_UPDATED ,'dd-Mon-yy hh24:mi:ss') DESC";
      }
      stmt = conn.createStatement();
      rset = stmt.executeQuery(queryString);
      String errorMessage = "";
      while (rset.next()) {
        StruckAccountsSiebelToELMBean struckAccountsSiebelToELMBean =
            new StruckAccountsSiebelToELMBean();
        struckAccountsSiebelToELMBean.setInterfaceId(rset.getString(1));
        struckAccountsSiebelToELMBean.setCreatedBy(rset.getString(2));
        struckAccountsSiebelToELMBean.setSiebelAccounts(rset.getString(3));
        struckAccountsSiebelToELMBean.setLastModified(rset.getString(4));
        if (rset.getString(5) == null) {
          struckAccountsSiebelToELMBean.setErrorMessage(errorMessage);
        } else {
          struckAccountsSiebelToELMBean.setErrorMessage(rset.getString(5));
        }
        struckAccountsSiebelToELMBean.setOriginatingSystem(originatingSystem);
        struckAccountsSiebelToELMBean.setResolvingSystem(originatingSystem);
        struckAccountsSiebelToELMBean.setState(rset.getString(6));
        struckAccountsSiebelToELMBean.setUpdatedBy(rset.getString(7));
        struckAccountsSiebelToELMBeanList.add(struckAccountsSiebelToELMBean);
      }

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rset != null) {
          rset.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return struckAccountsSiebelToELMBeanList;
  }