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