public static SEPeriodNoData[] getPeriodNo(
ConnectionProvider connectionProvider, String CYearId, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT C_Period_ID AS ID, Name AS NAME"
+ " FROM C_Period"
+ " WHERE C_Year_ID=?"
+ " ORDER BY StartDate ASC";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, CYearId);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
SEPeriodNoData objectSEPeriodNoData = new SEPeriodNoData();
objectSEPeriodNoData.id = UtilSql.getValue(result, "ID");
objectSEPeriodNoData.name = UtilSql.getValue(result, "NAME");
objectSEPeriodNoData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectSEPeriodNoData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
SEPeriodNoData objectSEPeriodNoData[] = new SEPeriodNoData[vector.size()];
vector.copyInto(objectSEPeriodNoData);
return (objectSEPeriodNoData);
}
public static String selectHasDefaults(
ConnectionProvider connectionProvider,
String tablename,
String parentClause,
String currentClause,
String orgId)
throws ServletException {
String strSql = "";
strSql = strSql + " SELECT count(*) as total" + " FROM ";
strSql = strSql + ((tablename == null || tablename.equals("")) ? "" : tablename);
strSql = strSql + " WHERE isDefault = 'Y'" + " AND 1=1 ";
strSql = strSql + ((parentClause == null || parentClause.equals("")) ? "" : parentClause);
strSql = strSql + " AND 3=3 ";
strSql = strSql + ((currentClause == null || currentClause.equals("")) ? "" : currentClause);
strSql = strSql + " AND AD_Org_ID = ?";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
if (tablename != null && !(tablename.equals(""))) {}
if (parentClause != null && !(parentClause.equals(""))) {}
if (currentClause != null && !(currentClause.equals(""))) {}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, orgId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "TOTAL");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
public static int delete(
ConnectionProvider connectionProvider,
String param1,
String mProductionId,
String adUserClient,
String adOrgClient)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " DELETE FROM M_ProductionPlan"
+ " WHERE M_ProductionPlan.M_ProductionPlan_ID = ? "
+ " AND M_ProductionPlan.M_Production_ID = ? "
+ " AND M_ProductionPlan.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND M_ProductionPlan.AD_Org_ID IN (";
strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient);
strSql = strSql + ") ";
int updateCount = 0;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, param1);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adOrgClient != null && !(adOrgClient.equals(""))) {}
updateCount = st.executeUpdate();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (updateCount);
}
public static String existsCost(
ConnectionProvider connectionProvider, String date, String mProductId)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT COUNT(M_COSTING_ID)"
+ " FROM M_COSTING"
+ " WHERE TO_DATE(?) BETWEEN DATEFROM AND DATETO"
+ " AND COSTTYPE NOT IN ('STA', 'AVA')"
+ " AND M_PRODUCT_ID = ?";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, date);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "COUNT(M_COSTING_ID)");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
/** return the parent ID */
public static String selectParentID(ConnectionProvider connectionProvider, String key)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT M_ProductionPlan.M_Production_ID AS NAME"
+ " FROM M_ProductionPlan"
+ " WHERE M_ProductionPlan.M_ProductionPlan_ID = ?";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, key);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "NAME");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
public static String getCurrentDBTimestamp(ConnectionProvider connectionProvider, String id)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT to_char(Updated, 'YYYYMMDDHH24MISS') as Updated_Time_Stamp"
+ " FROM M_ProductionPlan"
+ " WHERE M_ProductionPlan.M_ProductionPlan_ID = ? ";
ResultSet result;
String strReturn = null;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, id);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "UPDATED_TIME_STAMP");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
/** Select for parent field */
public static String selectParentTrl(ConnectionProvider connectionProvider, String mProductionId)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (TO_CHAR(COALESCE(TO_CHAR(table1.Name), '')) || ' - ' || TO_CHAR(COALESCE(TO_CHAR(table1.DocumentNo), '')) || ' - ' || TO_CHAR(table1.MovementDate, 'DD-MM-YYYY')) AS NAME FROM M_Production left join (select M_Production_ID, Name, DocumentNo, MovementDate from M_Production) table1 on (M_Production.M_Production_ID = table1.M_Production_ID) WHERE M_Production.M_Production_ID = ? ";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "NAME");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
/** Select for auxiliar field */
public static String selectDef802026(ConnectionProvider connectionProvider, String MA_WRPhase_ID)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (QUANTITY - DONEQUANTITY) AS DefaultValue FROM MA_WRPhase WHERE MA_WRPhase_ID = ? ";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, MA_WRPhase_ID);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "DEFAULTVALUE");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
/** Select for auxiliar field */
public static String selectDef4761_1(ConnectionProvider connectionProvider, String UpdatedByR)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT ( COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table2.Name), ''))), '') ) as UpdatedBy FROM AD_User left join (select AD_User_ID, Name from AD_User) table2 on (AD_User.AD_User_ID = table2.AD_User_ID) WHERE AD_User.isActive='Y' AND AD_User.AD_User_ID = ? ";
ResultSet result;
String strReturn = "";
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, UpdatedByR);
result = st.executeQuery();
if (result.next()) {
strReturn = UtilSql.getValue(result, "UPDATEDBY");
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (strReturn);
}
public int insert(Connection conn, ConnectionProvider connectionProvider)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " INSERT INTO M_ProductionPlan "
+ " (Line, MA_Wrphase_ID, Starttime, Endtime, MA_Costcenter_Version_ID, Neededquantity, ProductionQty, Secondaryunit, Conversionrate, Secondaryqty, Rejectedquantity, MA_Costcenteruse, Usedmaterial, Outsourced, Processed, Estimatedtime, Runtime, Closephase, Description, M_Product_ID, M_Locator_ID, AD_Client_ID, AD_Org_ID, M_Production_ID, M_ProductionPlan_ID, IsActive, created, createdby, updated, updatedBy)"
+ " VALUES (TO_NUMBER(?), (?), TO_TIMESTAMP(?, ?), TO_TIMESTAMP(?, ?), (?), TO_NUMBER(?), TO_NUMBER(?), (?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), (?), (?), (?), TO_NUMBER(?), TO_NUMBER(?), (?), (?), (?), (?), (?), (?), (?), (?), (?), now(), ?, now(), ?)";
int updateCount = 0;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(conn, strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, line);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, maWrphaseId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, starttime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, endtime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, maCostcenterVersionId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, neededquantity);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, productionqty);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, secondaryunit);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, conversionrate);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, secondaryqty);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, rejectedquantity);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, maCostcenteruse);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, usedmaterial);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, outsourced);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, processed);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, estimatedtime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, runtime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, closephase);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, description);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mLocatorId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adClientId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adOrgId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionplanId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, isactive);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, createdby);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, updatedby);
updateCount = st.executeUpdate();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releaseTransactionalPreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (updateCount);
}
public int update(Connection conn, ConnectionProvider connectionProvider)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " UPDATE M_ProductionPlan"
+ " SET Line = TO_NUMBER(?) , MA_Wrphase_ID = (?) , Starttime = TO_TIMESTAMP(?, ?) , Endtime = TO_TIMESTAMP(?, ?) , MA_Costcenter_Version_ID = (?) , Neededquantity = TO_NUMBER(?) , ProductionQty = TO_NUMBER(?) , Secondaryunit = (?) , Conversionrate = TO_NUMBER(?) , Secondaryqty = TO_NUMBER(?) , Rejectedquantity = TO_NUMBER(?) , MA_Costcenteruse = TO_NUMBER(?) , Usedmaterial = (?) , Outsourced = (?) , Processed = (?) , Estimatedtime = TO_NUMBER(?) , Runtime = TO_NUMBER(?) , Closephase = (?) , Description = (?) , M_Product_ID = (?) , M_Locator_ID = (?) , AD_Client_ID = (?) , AD_Org_ID = (?) , M_Production_ID = (?) , M_ProductionPlan_ID = (?) , IsActive = (?) , updated = now(), updatedby = ? "
+ " WHERE M_ProductionPlan.M_ProductionPlan_ID = ? "
+ " AND M_ProductionPlan.M_Production_ID = ? "
+ " AND M_ProductionPlan.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND M_ProductionPlan.AD_Org_ID IN (";
strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient);
strSql = strSql + ") ";
int updateCount = 0;
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(conn, strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, line);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, maWrphaseId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, starttime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, endtime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, maCostcenterVersionId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, neededquantity);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, productionqty);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, secondaryunit);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, conversionrate);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, secondaryqty);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, rejectedquantity);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, maCostcenteruse);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, usedmaterial);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, outsourced);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, processed);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, estimatedtime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, runtime);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, closephase);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, description);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mLocatorId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adClientId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adOrgId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionplanId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, isactive);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, updatedby);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionplanId);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adOrgClient != null && !(adOrgClient.equals(""))) {}
updateCount = st.executeUpdate();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releaseTransactionalPreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (updateCount);
}
public static WarehouseComboData[] select(
ConnectionProvider connectionProvider,
String rol,
String client,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT M_Warehouse.M_Warehouse_ID, M_Warehouse.Name AS Name "
+ " FROM M_Warehouse, AD_ORG , AD_ROLE_ORGACCESS"
+ " WHERE M_Warehouse.AD_ORG_ID = AD_ORG.AD_ORG_ID"
+ " AND AD_ORG.AD_ORG_ID = AD_ROLE_ORGACCESS.AD_ORG_ID"
+ " AND M_Warehouse.IsActive='Y' "
+ " AND AD_ROLE_ORGACCESS.AD_ROLE_ID = ? "
+ " AND M_Warehouse.AD_Client_ID = ? "
+ " ORDER BY M_Warehouse.Value";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, rol);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, client);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
WarehouseComboData objectWarehouseComboData = new WarehouseComboData();
objectWarehouseComboData.mWarehouseId = UtilSql.getValue(result, "M_WAREHOUSE_ID");
objectWarehouseComboData.name = UtilSql.getValue(result, "NAME");
objectWarehouseComboData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectWarehouseComboData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
WarehouseComboData objectWarehouseComboData[] = new WarehouseComboData[vector.size()];
vector.copyInto(objectWarehouseComboData);
return (objectWarehouseComboData);
}
public static ReportDebtPaymentData[] selectNoBpartner(
ConnectionProvider connectionProvider,
String cCurrencyConv,
String adLanguage,
String adUserClient,
String adUserOrg,
String cBpartnerId,
String dateFrom,
String dateTo,
String AmountFrom,
String AmountTo,
String paymentRule,
String isReceipt,
String status,
String isSettle,
String cbankaccount,
String ord,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT (BANK.NAME || '/' || BANK.CODEBRANCH || BANK.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL) AS BANKACC,"
+ " (CASE BA.showspanish WHEN 'Y' then BA.CODEACCOUNT ELSE "
+ " CASE BA.SHOWGENERIC when 'Y' then GENERICACCOUNT ELSE "
+ " CASE BA.SHOWIBAN WHEN 'Y' THEN BA.IBAN END END END) AS ACCOUNTSTR,"
+ " (CASE WHEN I.C_INVOICE_ID IS NULL THEN '0' ELSE I.C_INVOICE_ID END) AS C_INVOICE_ID, (CASE WHEN DP.C_DEBT_PAYMENT_ID IS NULL THEN '0' ELSE DP.C_DEBT_PAYMENT_ID END) AS C_DEBT_PAYMENT_ID, B.NAME AS BPARTNER, (CASE WHEN I.DOCUMENTNO IS NOT NULL THEN I.DOCUMENTNO||' ('||I.DATEINVOICED||')' END) AS INVOICE, DP.DATEPLANNED,"
+ " DP.AMOUNT, DP.WRITEOFFAMT, C.ISO_CODE AS CURRENCY,"
+ " (CASE WHEN S1.DOCUMENTNO IS NOT NULL THEN S1.DOCUMENTNO||' ('||S1.DATETRX||')' END) AS DEBTCANCEL, (CASE WHEN S2.DOCUMENTNO IS NOT NULL THEN S2.DOCUMENTNO||' ('||S2.DATETRX||')' END) AS DEBTGENERATE, COALESCE(RLT.NAME, RL.NAME) AS PAYMENTRULE,"
+ " C_CURRENCY_CONVERT(DP.AMOUNT, DP.C_CURRENCY_ID, ?, TO_DATE(I.DATEINVOICED), NULL, I.AD_CLIENT_ID, I.AD_ORG_ID) AS CONVAMOUNT,"
+ " C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, "
+ " C_CURRENCY_ISOSYM(?) AS CONVISOSYM, "
+ " (CASE C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, "
+ " DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) WHEN 'I' THEN AD_MESSAGE_GET2('DPR_INVALID',?) WHEN 'C' THEN AD_MESSAGE_GET2('DPR_CONCILIATED',?) WHEN 'W' THEN AD_MESSAGE_GET2('DPR_CANCELLED',?) WHEN 'A' THEN AD_MESSAGE_GET2('DPR_SETTLE',?) WHEN "
+ " 'P' THEN AD_MESSAGE_GET2('DPR_PENDING',?) END) AS STATUS,"
+ " DP.DESCRIPTION, AD_MESSAGE_GET2(DP.ISPAID, ?) AS ISPAID, "
+ " (CASE DP.ISRECEIPT WHEN 'Y' THEN 'SalesInvoice' ELSE 'PurchaseInvoice' END) AS INVOICE_TYPE, "
+ " (CASE s2.SETTLEMENTTYPE WHEN 'I' THEN '../ManualSettlement/CreatePayment_Relation.html' ELSE '../Settlement/CreatedPayments_Relation.html' END) AS PATH, COALESCE(RLT2.NAME, RL2.NAME) AS DPSTATUS, URS.NAME AS SALESREPNAME"
+ " FROM C_DEBT_PAYMENT DP left join C_SETTLEMENT S1 on DP.C_SETTLEMENT_CANCEL_ID = S1.C_SETTLEMENT_ID"
+ " left join C_SETTLEMENT S2 on DP.C_SETTLEMENT_GENERATE_ID = S2.C_SETTLEMENT_ID"
+ " left join C_BPARTNER B on DP.C_BPARTNER_ID = B.C_BPARTNER_ID"
+ " left join C_BANKACCOUNT BA on DP.C_BANKACCOUNT_ID = BA.C_BANKACCOUNT_ID"
+ " left join C_BANK BANK ON BA.C_BANK_ID = BANK.C_BANK_ID"
+ " left join C_INVOICE I on DP.C_INVOICE_ID = I.C_INVOICE_ID left join AD_USER URS on I.SALESREP_ID = URS.AD_USER_ID,"
+ " AD_REF_LIST RL left join AD_REF_LIST_TRL RLT on RL.AD_REF_LIST_ID = RLT.AD_REF_LIST_ID"
+ " AND RLT.AD_LANGUAGE = ?,"
+ " AD_REF_LIST RL2 left join AD_REF_LIST_TRL RLT2 on RL2.AD_REF_LIST_ID = RLT2.AD_REF_LIST_ID"
+ " AND RLT2.AD_LANGUAGE = ?,"
+ " C_CURRENCY C"
+ " WHERE DP.C_CURRENCY_ID = C.C_CURRENCY_ID "
+ " AND DP.PAYMENTRULE = RL.VALUE"
+ " AND RL.AD_REFERENCE_ID = '195'"
+ " AND DP.STATUS = RL2.VALUE"
+ " AND RL2.AD_REFERENCE_ID = '800070'"
+ " AND DP.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND DP.AD_Org_ID IN (";
strSql = strSql + ((adUserOrg == null || adUserOrg.equals("")) ? "" : adUserOrg);
strSql = strSql + ") " + " AND DP.IsActive='Y'" + " AND DP.IsValid='Y'";
strSql =
strSql
+ ((cBpartnerId == null || cBpartnerId.equals(""))
? ""
: " AND dp.C_BPARTNER_ID IN " + cBpartnerId);
strSql =
strSql
+ ((dateFrom == null || dateFrom.equals(""))
? ""
: " AND dp.Dateplanned >= to_date(?) ");
strSql =
strSql
+ ((dateTo == null || dateTo.equals("")) ? "" : " AND dp.Dateplanned < to_date(?) ");
strSql =
strSql
+ ((AmountFrom == null || AmountFrom.equals(""))
? ""
: " AND dp.amount >= TO_NUMBER(?) ");
strSql =
strSql
+ ((AmountTo == null || AmountTo.equals(""))
? ""
: " AND dp.amount <= TO_NUMBER(?) ");
strSql =
strSql
+ ((paymentRule == null || paymentRule.equals("")) ? "" : " AND dp.paymentrule = ? ");
strSql = strSql + ((isReceipt == null || isReceipt.equals("")) ? "" : " AND dp.isreceipt=? ");
strSql = strSql + ((status == null || status.equals("")) ? "" : " AND dp.status=? ");
strSql =
strSql
+ ((isSettle == null || isSettle.equals(""))
? ""
: " AND C_DEBT_PAYMENT_STATUS(DP.C_Settlement_Cancel_ID, DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID) IN"
+ isSettle);
strSql =
strSql
+ ((cbankaccount == null || cbankaccount.equals(""))
? ""
: " AND DP.C_BANKACCOUNT_ID = ? ");
strSql =
strSql
+ " GROUP BY (BANK.NAME || '/' || BANK.CODEBRANCH || BANK.DIGITCONTROL || BA.CODEACCOUNT || '.' || BA.DIGITCONTROL), (CASE BA.showspanish WHEN 'Y' then BA.CODEACCOUNT ELSE CASE BA.SHOWGENERIC when 'Y' then GENERICACCOUNT ELSE CASE BA.SHOWIBAN WHEN 'Y' THEN BA.IBAN END END END),"
+ " CASE WHEN I.C_INVOICE_ID IS NULL THEN '0' ELSE I.C_INVOICE_ID END, (CASE WHEN DP.C_DEBT_PAYMENT_ID IS NULL THEN '0' ELSE DP.C_DEBT_PAYMENT_ID END), B.NAME, (CASE WHEN I.DOCUMENTNO IS NOT NULL THEN I.DOCUMENTNO||' ('||I.DATEINVOICED||')' END), DP.DATEPLANNED, DP.AMOUNT,"
+ " DP.C_CURRENCY_ID, I.AD_CLIENT_ID, I.AD_ORG_ID, I.DATEINVOICED,"
+ " DP.WRITEOFFAMT, C.ISO_CODE, (CASE WHEN S1.DOCUMENTNO IS NOT NULL THEN S1.DOCUMENTNO||' ('||S1.DATETRX||')' END), (CASE WHEN S2.DOCUMENTNO IS NOT NULL THEN S2.DOCUMENTNO||' ('||S2.DATETRX||')' END), RLT.NAME, RL.NAME, RLT2.NAME, RL2.NAME, DP.C_Settlement_Cancel_ID,"
+ " DP.CANCEL_PROCESSED, DP.GENERATE_PROCESSED, DP.ISPAID, DP.ISVALID, DP.C_CASHLINE_ID, DP.C_BANKSTATEMENTLINE_ID, DP.DESCRIPTION, (CASE DP.ISRECEIPT WHEN 'Y' THEN 'SalesInvoice' ELSE 'PurchaseInvoice' END),s2.SETTLEMENTTYPE, DP.STATUS, URS.NAME"
+ " ORDER BY ";
strSql = strSql + ((ord == null || ord.equals("")) ? "" : ord);
strSql = strSql + ",STATUS, DP.DATEPLANNED";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cCurrencyConv);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, adLanguage);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adUserOrg != null && !(adUserOrg.equals(""))) {}
if (cBpartnerId != null && !(cBpartnerId.equals(""))) {}
if (dateFrom != null && !(dateFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateFrom);
}
if (dateTo != null && !(dateTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTo);
}
if (AmountFrom != null && !(AmountFrom.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AmountFrom);
}
if (AmountTo != null && !(AmountTo.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, AmountTo);
}
if (paymentRule != null && !(paymentRule.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paymentRule);
}
if (isReceipt != null && !(isReceipt.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, isReceipt);
}
if (status != null && !(status.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, status);
}
if (isSettle != null && !(isSettle.equals(""))) {}
if (cbankaccount != null && !(cbankaccount.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, cbankaccount);
}
if (ord != null && !(ord.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ReportDebtPaymentData objectReportDebtPaymentData = new ReportDebtPaymentData();
objectReportDebtPaymentData.bankacc = UtilSql.getValue(result, "BANKACC");
objectReportDebtPaymentData.accountstr = UtilSql.getValue(result, "ACCOUNTSTR");
objectReportDebtPaymentData.cInvoiceId = UtilSql.getValue(result, "C_INVOICE_ID");
objectReportDebtPaymentData.cDebtPaymentId = UtilSql.getValue(result, "C_DEBT_PAYMENT_ID");
objectReportDebtPaymentData.bpartner = UtilSql.getValue(result, "BPARTNER");
objectReportDebtPaymentData.invoice = UtilSql.getValue(result, "INVOICE");
objectReportDebtPaymentData.dateplanned =
UtilSql.getDateValue(result, "DATEPLANNED", "dd-MM-yyyy");
objectReportDebtPaymentData.amount = UtilSql.getValue(result, "AMOUNT");
objectReportDebtPaymentData.writeoffamt = UtilSql.getValue(result, "WRITEOFFAMT");
objectReportDebtPaymentData.currency = UtilSql.getValue(result, "CURRENCY");
objectReportDebtPaymentData.debtcancel = UtilSql.getValue(result, "DEBTCANCEL");
objectReportDebtPaymentData.debtgenerate = UtilSql.getValue(result, "DEBTGENERATE");
objectReportDebtPaymentData.paymentrule = UtilSql.getValue(result, "PAYMENTRULE");
objectReportDebtPaymentData.convamount = UtilSql.getValue(result, "CONVAMOUNT");
objectReportDebtPaymentData.convsym = UtilSql.getValue(result, "CONVSYM");
objectReportDebtPaymentData.convisosym = UtilSql.getValue(result, "CONVISOSYM");
objectReportDebtPaymentData.status = UtilSql.getValue(result, "STATUS");
objectReportDebtPaymentData.description = UtilSql.getValue(result, "DESCRIPTION");
objectReportDebtPaymentData.ispaid = UtilSql.getValue(result, "ISPAID");
objectReportDebtPaymentData.invoiceType = UtilSql.getValue(result, "INVOICE_TYPE");
objectReportDebtPaymentData.path = UtilSql.getValue(result, "PATH");
objectReportDebtPaymentData.dpstatus = UtilSql.getValue(result, "DPSTATUS");
objectReportDebtPaymentData.salesrepname = UtilSql.getValue(result, "SALESREPNAME");
objectReportDebtPaymentData.rownum = Long.toString(countRecord);
objectReportDebtPaymentData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectReportDebtPaymentData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ReportDebtPaymentData objectReportDebtPaymentData[] = new ReportDebtPaymentData[vector.size()];
vector.copyInto(objectReportDebtPaymentData);
return (objectReportDebtPaymentData);
}
public static DocInOutData[] selectRegistro(
ConnectionProvider connectionProvider,
String client,
String id,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT I.AD_CLIENT_ID, I.AD_ORG_ID, I.C_BPARTNER_ID, I.AD_ORGTRX_ID, I.C_PROJECT_ID,"
+ " I.C_CAMPAIGN_ID, I.C_ACTIVITY_ID, I.USER1_ID, I.USER2_ID, I.C_COSTCENTER_ID, I.DOCUMENTNO, I.DATEACCT,"
+ " I.C_DOCTYPE_ID, I.C_CHARGE_ID, I.CHARGEAMT, I.POSTED, I.MOVEMENTDATE, I.C_BPARTNER_LOCATION_ID"
+ " FROM M_INOUT I"
+ " WHERE AD_Client_ID=? "
+ " AND M_INOUT_ID=?";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, client);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, id);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
DocInOutData objectDocInOutData = new DocInOutData();
objectDocInOutData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectDocInOutData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectDocInOutData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectDocInOutData.adOrgtrxId = UtilSql.getValue(result, "AD_ORGTRX_ID");
objectDocInOutData.cProjectId = UtilSql.getValue(result, "C_PROJECT_ID");
objectDocInOutData.cCampaignId = UtilSql.getValue(result, "C_CAMPAIGN_ID");
objectDocInOutData.cActivityId = UtilSql.getValue(result, "C_ACTIVITY_ID");
objectDocInOutData.user1Id = UtilSql.getValue(result, "USER1_ID");
objectDocInOutData.user2Id = UtilSql.getValue(result, "USER2_ID");
objectDocInOutData.cCostcenterId = UtilSql.getValue(result, "C_COSTCENTER_ID");
objectDocInOutData.documentno = UtilSql.getValue(result, "DOCUMENTNO");
objectDocInOutData.dateacct = UtilSql.getDateValue(result, "DATEACCT", "dd-MM-yyyy");
objectDocInOutData.cDoctypeId = UtilSql.getValue(result, "C_DOCTYPE_ID");
objectDocInOutData.cChargeId = UtilSql.getValue(result, "C_CHARGE_ID");
objectDocInOutData.chargeamt = UtilSql.getValue(result, "CHARGEAMT");
objectDocInOutData.posted = UtilSql.getValue(result, "POSTED");
objectDocInOutData.movementdate =
UtilSql.getDateValue(result, "MOVEMENTDATE", "dd-MM-yyyy");
objectDocInOutData.cBpartnerLocationId = UtilSql.getValue(result, "C_BPARTNER_LOCATION_ID");
objectDocInOutData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectDocInOutData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
DocInOutData objectDocInOutData[] = new DocInOutData[vector.size()];
vector.copyInto(objectDocInOutData);
return (objectDocInOutData);
}
public static DocInOutData[] select(
ConnectionProvider connectionProvider, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT '' AS M_INOUT_ID, '' AS AD_CLIENT_ID, '' AS AD_ORG_ID, '' AS ISACTIVE, '' AS CREATED,"
+ " '' AS CREATEDBY, '' AS UPDATED, '' AS UPDATEDBY, '' AS ISSOTRX, '' AS DOCUMENTNO, '' AS DOCACTION,"
+ " '' AS DOCSTATUS, '' AS POSTED, '' AS PROCESSING, '' AS PROCESSED, '' AS C_DOCTYPE_ID, '' AS DESCRIPTION,"
+ " '' AS C_ORDER_ID, '' AS DATEORDERED, '' AS ISPRINTED, '' AS MOVEMENTTYPE, '' AS MOVEMENTDATE, '' AS DATEACCT,"
+ " '' AS C_BPARTNER_ID, '' AS C_BPARTNER_LOCATION_ID, '' AS M_WAREHOUSE_ID, '' AS POREFERENCE, '' AS DELIVERYRULE,"
+ " '' AS FREIGHTCOSTRULE, '' AS FREIGHTAMT, '' AS DELIVERYVIARULE, '' AS M_SHIPPER_ID, '' AS C_CHARGE_ID,"
+ " '' AS CHARGEAMT, '' AS PRIORITYRULE, '' AS DATEPRINTED, '' AS C_INVOICE_ID, '' AS CREATEFROM, '' AS GENERATETO,"
+ " '' AS SENDEMAIL, '' AS AD_USER_ID, '' AS SALESREP_ID, '' AS NOPACKAGES, '' AS PICKDATE, '' AS SHIPDATE,"
+ " '' AS TRACKINGNO, '' AS AD_ORGTRX_ID, '' AS C_PROJECT_ID, '' AS C_CAMPAIGN_ID, '' AS C_ACTIVITY_ID, '' AS USER1_ID,"
+ " '' AS USER2_ID, '' AS C_COSTCENTER_ID, '' AS UPDATELINES, '' AS M_COSTING_ID, '' AS stdprecision"
+ " FROM DUAL";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
DocInOutData objectDocInOutData = new DocInOutData();
objectDocInOutData.mInoutId = UtilSql.getValue(result, "M_INOUT_ID");
objectDocInOutData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectDocInOutData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectDocInOutData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectDocInOutData.created = UtilSql.getValue(result, "CREATED");
objectDocInOutData.createdby = UtilSql.getValue(result, "CREATEDBY");
objectDocInOutData.updated = UtilSql.getValue(result, "UPDATED");
objectDocInOutData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectDocInOutData.issotrx = UtilSql.getValue(result, "ISSOTRX");
objectDocInOutData.documentno = UtilSql.getValue(result, "DOCUMENTNO");
objectDocInOutData.docaction = UtilSql.getValue(result, "DOCACTION");
objectDocInOutData.docstatus = UtilSql.getValue(result, "DOCSTATUS");
objectDocInOutData.posted = UtilSql.getValue(result, "POSTED");
objectDocInOutData.processing = UtilSql.getValue(result, "PROCESSING");
objectDocInOutData.processed = UtilSql.getValue(result, "PROCESSED");
objectDocInOutData.cDoctypeId = UtilSql.getValue(result, "C_DOCTYPE_ID");
objectDocInOutData.description = UtilSql.getValue(result, "DESCRIPTION");
objectDocInOutData.cOrderId = UtilSql.getValue(result, "C_ORDER_ID");
objectDocInOutData.dateordered = UtilSql.getValue(result, "DATEORDERED");
objectDocInOutData.isprinted = UtilSql.getValue(result, "ISPRINTED");
objectDocInOutData.movementtype = UtilSql.getValue(result, "MOVEMENTTYPE");
objectDocInOutData.movementdate = UtilSql.getValue(result, "MOVEMENTDATE");
objectDocInOutData.dateacct = UtilSql.getValue(result, "DATEACCT");
objectDocInOutData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectDocInOutData.cBpartnerLocationId = UtilSql.getValue(result, "C_BPARTNER_LOCATION_ID");
objectDocInOutData.mWarehouseId = UtilSql.getValue(result, "M_WAREHOUSE_ID");
objectDocInOutData.poreference = UtilSql.getValue(result, "POREFERENCE");
objectDocInOutData.deliveryrule = UtilSql.getValue(result, "DELIVERYRULE");
objectDocInOutData.freightcostrule = UtilSql.getValue(result, "FREIGHTCOSTRULE");
objectDocInOutData.freightamt = UtilSql.getValue(result, "FREIGHTAMT");
objectDocInOutData.deliveryviarule = UtilSql.getValue(result, "DELIVERYVIARULE");
objectDocInOutData.mShipperId = UtilSql.getValue(result, "M_SHIPPER_ID");
objectDocInOutData.cChargeId = UtilSql.getValue(result, "C_CHARGE_ID");
objectDocInOutData.chargeamt = UtilSql.getValue(result, "CHARGEAMT");
objectDocInOutData.priorityrule = UtilSql.getValue(result, "PRIORITYRULE");
objectDocInOutData.dateprinted = UtilSql.getValue(result, "DATEPRINTED");
objectDocInOutData.cInvoiceId = UtilSql.getValue(result, "C_INVOICE_ID");
objectDocInOutData.createfrom = UtilSql.getValue(result, "CREATEFROM");
objectDocInOutData.generateto = UtilSql.getValue(result, "GENERATETO");
objectDocInOutData.sendemail = UtilSql.getValue(result, "SENDEMAIL");
objectDocInOutData.adUserId = UtilSql.getValue(result, "AD_USER_ID");
objectDocInOutData.salesrepId = UtilSql.getValue(result, "SALESREP_ID");
objectDocInOutData.nopackages = UtilSql.getValue(result, "NOPACKAGES");
objectDocInOutData.pickdate = UtilSql.getValue(result, "PICKDATE");
objectDocInOutData.shipdate = UtilSql.getValue(result, "SHIPDATE");
objectDocInOutData.trackingno = UtilSql.getValue(result, "TRACKINGNO");
objectDocInOutData.adOrgtrxId = UtilSql.getValue(result, "AD_ORGTRX_ID");
objectDocInOutData.cProjectId = UtilSql.getValue(result, "C_PROJECT_ID");
objectDocInOutData.cCampaignId = UtilSql.getValue(result, "C_CAMPAIGN_ID");
objectDocInOutData.cActivityId = UtilSql.getValue(result, "C_ACTIVITY_ID");
objectDocInOutData.user1Id = UtilSql.getValue(result, "USER1_ID");
objectDocInOutData.user2Id = UtilSql.getValue(result, "USER2_ID");
objectDocInOutData.cCostcenterId = UtilSql.getValue(result, "C_COSTCENTER_ID");
objectDocInOutData.updatelines = UtilSql.getValue(result, "UPDATELINES");
objectDocInOutData.mCostingId = UtilSql.getValue(result, "M_COSTING_ID");
objectDocInOutData.stdprecision = UtilSql.getValue(result, "STDPRECISION");
objectDocInOutData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectDocInOutData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
DocInOutData objectDocInOutData[] = new DocInOutData[vector.size()];
vector.copyInto(objectDocInOutData);
return (objectDocInOutData);
}
/** Select for edit */
public static ProductionRunData[] selectEdit(
ConnectionProvider connectionProvider,
String dateTimeFormat,
String paramLanguage,
String mProductionId,
String key,
String adUserClient,
String adOrgClient,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT to_char(M_ProductionPlan.Created, ?) as created, "
+ " (SELECT NAME FROM AD_USER u WHERE AD_USER_ID = M_ProductionPlan.CreatedBy) as CreatedByR, "
+ " to_char(M_ProductionPlan.Updated, ?) as updated, "
+ " to_char(M_ProductionPlan.Updated, 'YYYYMMDDHH24MISS') as Updated_Time_Stamp, "
+ " M_ProductionPlan.UpdatedBy, "
+ " (SELECT NAME FROM AD_USER u WHERE AD_USER_ID = M_ProductionPlan.UpdatedBy) as UpdatedByR,"
+ " M_ProductionPlan.Line, "
+ "M_ProductionPlan.MA_Wrphase_ID, "
+ "(CASE WHEN M_ProductionPlan.MA_Wrphase_ID IS NULL THEN '' ELSE (COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table2.DocumentNo), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(table2.StartDate, 'DD-MM-YYYY')),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table1.SeqNo), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table3.Name), ''))),'') ) END) AS MA_Wrphase_IDR, "
+ "TO_CHAR(M_ProductionPlan.Starttime, ?) AS Starttime, "
+ "TO_CHAR(M_ProductionPlan.Endtime, ?) AS Endtime, "
+ "M_ProductionPlan.MA_Costcenter_Version_ID, "
+ "(CASE WHEN M_ProductionPlan.MA_Costcenter_Version_ID IS NULL THEN '' ELSE (COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table4.DocumentNo), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(COALESCE(TO_CHAR(table5.Name), ''))),'') || ' - ' || COALESCE(TO_CHAR(TO_CHAR(table4.ValidFrom, 'DD-MM-YYYY')),'') ) END) AS MA_Costcenter_Version_IDR, "
+ "M_ProductionPlan.Neededquantity, "
+ "M_ProductionPlan.ProductionQty, "
+ "M_ProductionPlan.Secondaryunit, "
+ "M_ProductionPlan.Conversionrate, "
+ "M_ProductionPlan.Secondaryqty, "
+ "M_ProductionPlan.Rejectedquantity, "
+ "M_ProductionPlan.MA_Costcenteruse, "
+ "M_ProductionPlan.Usedmaterial, "
+ "COALESCE(M_ProductionPlan.Outsourced, 'N') AS Outsourced, "
+ "COALESCE(M_ProductionPlan.Processed, 'N') AS Processed, "
+ "M_ProductionPlan.Estimatedtime, "
+ "M_ProductionPlan.Runtime, "
+ "COALESCE(M_ProductionPlan.Closephase, 'N') AS Closephase, "
+ "M_ProductionPlan.Description, "
+ "M_ProductionPlan.M_Product_ID, "
+ "M_ProductionPlan.M_Locator_ID, "
+ "M_ProductionPlan.AD_Client_ID, "
+ "M_ProductionPlan.AD_Org_ID, "
+ "M_ProductionPlan.M_Production_ID, "
+ "M_ProductionPlan.M_ProductionPlan_ID, "
+ "COALESCE(M_ProductionPlan.IsActive, 'N') AS IsActive, "
+ " ? AS LANGUAGE "
+ " FROM M_ProductionPlan left join (select MA_Wrphase_ID, MA_Workrequirement_ID, SeqNo, MA_Process_ID from MA_Wrphase) table1 on (M_ProductionPlan.MA_Wrphase_ID = table1.MA_Wrphase_ID) left join (select MA_Workrequirement_ID, DocumentNo, StartDate from MA_Workrequirement) table2 on (table1.MA_Workrequirement_ID = table2.MA_Workrequirement_ID) left join (select MA_Process_ID, Name from MA_Process) table3 on (table1.MA_Process_ID = table3.MA_Process_ID) left join (select MA_Costcenter_Version_ID, DocumentNo, MA_Costcenter_ID, ValidFrom from MA_Costcenter_Version) table4 on (M_ProductionPlan.MA_Costcenter_Version_ID = table4.MA_Costcenter_Version_ID) left join (select MA_Costcenter_ID, Name from MA_Costcenter) table5 on (table4.MA_Costcenter_ID = table5.MA_Costcenter_ID)"
+ " WHERE 2=2 "
+ " AND 1=1 ";
strSql =
strSql
+ ((mProductionId == null || mProductionId.equals(""))
? ""
: " AND M_ProductionPlan.M_Production_ID = ? ");
strSql =
strSql
+ " AND M_ProductionPlan.M_ProductionPlan_ID = ? "
+ " AND M_ProductionPlan.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND M_ProductionPlan.AD_Org_ID IN (";
strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient);
strSql = strSql + ") ";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, dateTimeFormat);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
if (mProductionId != null && !(mProductionId.equals(""))) {
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, mProductionId);
}
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, key);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adOrgClient != null && !(adOrgClient.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
ProductionRunData objectProductionRunData = new ProductionRunData();
objectProductionRunData.created = UtilSql.getValue(result, "CREATED");
objectProductionRunData.createdbyr = UtilSql.getValue(result, "CREATEDBYR");
objectProductionRunData.updated = UtilSql.getValue(result, "UPDATED");
objectProductionRunData.updatedTimeStamp = UtilSql.getValue(result, "UPDATED_TIME_STAMP");
objectProductionRunData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectProductionRunData.updatedbyr = UtilSql.getValue(result, "UPDATEDBYR");
objectProductionRunData.line = UtilSql.getValue(result, "LINE");
objectProductionRunData.maWrphaseId = UtilSql.getValue(result, "MA_WRPHASE_ID");
objectProductionRunData.maWrphaseIdr = UtilSql.getValue(result, "MA_WRPHASE_IDR");
objectProductionRunData.starttime = UtilSql.getValue(result, "STARTTIME");
objectProductionRunData.endtime = UtilSql.getValue(result, "ENDTIME");
objectProductionRunData.maCostcenterVersionId =
UtilSql.getValue(result, "MA_COSTCENTER_VERSION_ID");
objectProductionRunData.maCostcenterVersionIdr =
UtilSql.getValue(result, "MA_COSTCENTER_VERSION_IDR");
objectProductionRunData.neededquantity = UtilSql.getValue(result, "NEEDEDQUANTITY");
objectProductionRunData.productionqty = UtilSql.getValue(result, "PRODUCTIONQTY");
objectProductionRunData.secondaryunit = UtilSql.getValue(result, "SECONDARYUNIT");
objectProductionRunData.conversionrate = UtilSql.getValue(result, "CONVERSIONRATE");
objectProductionRunData.secondaryqty = UtilSql.getValue(result, "SECONDARYQTY");
objectProductionRunData.rejectedquantity = UtilSql.getValue(result, "REJECTEDQUANTITY");
objectProductionRunData.maCostcenteruse = UtilSql.getValue(result, "MA_COSTCENTERUSE");
objectProductionRunData.usedmaterial = UtilSql.getValue(result, "USEDMATERIAL");
objectProductionRunData.outsourced = UtilSql.getValue(result, "OUTSOURCED");
objectProductionRunData.processed = UtilSql.getValue(result, "PROCESSED");
objectProductionRunData.estimatedtime = UtilSql.getValue(result, "ESTIMATEDTIME");
objectProductionRunData.runtime = UtilSql.getValue(result, "RUNTIME");
objectProductionRunData.closephase = UtilSql.getValue(result, "CLOSEPHASE");
objectProductionRunData.description = UtilSql.getValue(result, "DESCRIPTION");
objectProductionRunData.mProductId = UtilSql.getValue(result, "M_PRODUCT_ID");
objectProductionRunData.mLocatorId = UtilSql.getValue(result, "M_LOCATOR_ID");
objectProductionRunData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectProductionRunData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectProductionRunData.mProductionId = UtilSql.getValue(result, "M_PRODUCTION_ID");
objectProductionRunData.mProductionplanId = UtilSql.getValue(result, "M_PRODUCTIONPLAN_ID");
objectProductionRunData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectProductionRunData.language = UtilSql.getValue(result, "LANGUAGE");
objectProductionRunData.adUserClient = "";
objectProductionRunData.adOrgClient = "";
objectProductionRunData.createdby = "";
objectProductionRunData.trBgcolor = "";
objectProductionRunData.totalCount = "";
objectProductionRunData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectProductionRunData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
ProductionRunData objectProductionRunData[] = new ProductionRunData[vector.size()];
vector.copyInto(objectProductionRunData);
return (objectProductionRunData);
}
public static SLIsDefaultData[] select(
ConnectionProvider connectionProvider, String tableId, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT TableName, ColumnName, '' as total"
+ " FROM AD_TABLE T, AD_Column C"
+ " WHERE T.AD_Table_ID = ?"
+ " AND C.AD_Table_ID = T.AD_Table_ID"
+ " AND (C.Iskey = 'Y' or C.IsSecondaryKey = 'Y') ";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, tableId);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
SLIsDefaultData objectSLIsDefaultData = new SLIsDefaultData();
objectSLIsDefaultData.tablename = UtilSql.getValue(result, "TABLENAME");
objectSLIsDefaultData.columnname = UtilSql.getValue(result, "COLUMNNAME");
objectSLIsDefaultData.total = UtilSql.getValue(result, "TOTAL");
objectSLIsDefaultData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectSLIsDefaultData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
SLIsDefaultData objectSLIsDefaultData[] = new SLIsDefaultData[vector.size()];
vector.copyInto(objectSLIsDefaultData);
return (objectSLIsDefaultData);
}
/** Select Organization */
public static OrganizationData[] select(
ConnectionProvider connectionProvider,
String paramLanguage,
String adUserClient,
String adOrgClient,
int firstRegister,
int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT "
+ " AD_OrgInfo.AD_Client_ID, "
+ " (CASE WHEN AD_OrgInfo.AD_Client_ID IS NULL THEN '' ELSE (TO_CHAR(client.Name) ) END) AS AD_Client_IDR, "
+ " AD_OrgInfo.AD_Org_ID, "
+ " (CASE WHEN AD_OrgInfo.AD_Org_ID IS NULL THEN '' ELSE (TO_CHAR(org.Name) ) END) AS AD_Org_IDR, "
+ " COALESCE(AD_OrgInfo.IsActive, 'N') AS IsActive, "
+ " AD_OrgInfo.C_Location_ID, "
+ " (CASE WHEN AD_OrgInfo.C_Location_ID IS NULL THEN '' ELSE (TO_CHAR(loc.Address1) || ' - ' || TO_CHAR(loc.Address2) || ' - ' || TO_CHAR(loc.Postal) || ' - ' || TO_CHAR(loc.City) || ' - ' || TO_CHAR(reg.Name) || ' - ' || TO_CHAR((CASE WHEN countryTrl.Name IS NULL THEN TO_CHAR(country.Name) ELSE TO_CHAR(countryTrl.Name) END)) ) END) AS C_Location_IDR, "
+ " AD_OrgInfo.DUNS, "
+ " AD_OrgInfo.TaxID, "
+ " AD_OrgInfo.C_BPartner_ID, "
+ " (CASE WHEN AD_OrgInfo.C_BPartner_ID IS NULL THEN '' ELSE (TO_CHAR(bpart.Name) ) END) AS C_BPartner_IDR, "
+ " AD_OrgInfo.Logo, "
+ " COALESCE(AD_OrgInfo.Reference_Order, 'N') AS Reference_Order, "
+ " '' AS TR_BGCOLOR , "
+ " AD_OrgInfo.Created, "
+ " AD_OrgInfo.CreatedBy, "
+ " AD_OrgInfo.Updated, "
+ " AD_OrgInfo.UpdatedBy, ? AS LANGUAGE "
+ " FROM "
+ " AD_OrgInfo "
+ " left join (select AD_Client_ID, Name from AD_Client) client on (AD_OrgInfo.AD_Client_ID = client.AD_Client_ID) "
+ " left join (select AD_Org_ID, Name from AD_Org) org on (AD_OrgInfo.AD_Org_ID = org.AD_Org_ID) "
+ " left join (select C_Location_ID, Address1, Address2, Postal, City, C_Region_ID, C_Country_ID from C_Location) loc on (AD_OrgInfo.C_Location_ID = loc.C_Location_ID) "
+ " left join (select C_Region_ID, Name from C_Region) reg on (loc.C_Region_ID = reg.C_Region_ID) "
+ " left join (select C_Country_ID, Name from C_Country) country on (loc.C_Country_ID = country.C_Country_ID) "
+ " left join (select C_Country_ID,AD_Language, Name from C_Country_TRL) countryTrl on (country.C_Country_ID = countryTrl.C_Country_ID and countryTrl.AD_Language = ?) "
+ " left join (select C_BPartner_ID, Name from C_BPartner) bpart on (AD_OrgInfo.C_BPartner_ID = bpart.C_BPartner_ID)"
+ " WHERE "
+ " AD_OrgInfo.AD_Client_ID IN (";
strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient);
strSql = strSql + ") " + " AND AD_OrgInfo.AD_Org_ID IN (";
strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient);
strSql = strSql + ")";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, paramLanguage);
if (adUserClient != null && !(adUserClient.equals(""))) {}
if (adOrgClient != null && !(adOrgClient.equals(""))) {}
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
OrganizationData objectOrganizationData = new OrganizationData();
objectOrganizationData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID");
objectOrganizationData.adClientIdr = UtilSql.getValue(result, "AD_CLIENT_IDR");
objectOrganizationData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID");
objectOrganizationData.adOrgIdr = UtilSql.getValue(result, "AD_ORG_IDR");
objectOrganizationData.isactive = UtilSql.getValue(result, "ISACTIVE");
objectOrganizationData.cLocationId = UtilSql.getValue(result, "C_LOCATION_ID");
objectOrganizationData.cLocationIdr = UtilSql.getValue(result, "C_LOCATION_IDR");
objectOrganizationData.duns = UtilSql.getValue(result, "DUNS");
objectOrganizationData.taxid = UtilSql.getValue(result, "TAXID");
objectOrganizationData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID");
objectOrganizationData.cBpartnerIdr = UtilSql.getValue(result, "C_BPARTNER_IDR");
objectOrganizationData.logo = UtilSql.getValue(result, "LOGO");
objectOrganizationData.referenceOrder = UtilSql.getValue(result, "REFERENCE_ORDER");
objectOrganizationData.trBgcolor = UtilSql.getValue(result, "TR_BGCOLOR");
objectOrganizationData.created = UtilSql.getDateValue(result, "CREATED", "dd-MM-yyyy");
objectOrganizationData.createdby = UtilSql.getValue(result, "CREATEDBY");
objectOrganizationData.updated = UtilSql.getDateValue(result, "UPDATED", "dd-MM-yyyy");
objectOrganizationData.updatedby = UtilSql.getValue(result, "UPDATEDBY");
objectOrganizationData.language = UtilSql.getValue(result, "LANGUAGE");
objectOrganizationData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectOrganizationData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
OrganizationData objectOrganizationData[] = new OrganizationData[vector.size()];
vector.copyInto(objectOrganizationData);
return (objectOrganizationData);
}