public ActionForward createBlackListed(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws OperationException, ApplicationException {
ActionForward fwd = init(mapping, form, request, response);
if (fwd != null) return fwd;
Properties ctx = TmkJSPEnv.getCtx(request);
DefaultForm df = (DefaultForm) form;
BlackListedBean bean = (BlackListedBean) df.getBean();
Trx trx = Trx.get(TrxPrefix.getPrefix(), true);
try {
trx.start();
BlackListedManager.createBlackListed(ctx, bean, trx.getTrxName());
trx.commit();
} catch (OperationException ex) {
trx.rollback();
throw ex;
} finally {
trx.close();
}
request.setAttribute(Constants.BLACKLISTED_DETAILS, bean);
return mapping.findForward(CREATE_BLACKLISTED);
}
public ActionForward editCheque(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws ApplicationException, OperationException {
ActionForward fwd = init(mapping, form, request, response);
if (fwd != null) return fwd;
Properties ctx = TmkJSPEnv.getCtx(request);
BlackListForm f = (BlackListForm) form;
BlackListedBean bean = (BlackListedBean) f.getBean();
Integer blacklistedID = bean.getBlackListedId();
if (blacklistedID == null) {
throw new OperationException(
"Cannot load Cheque details. Cause BlacklistedId cannot be null!");
}
Trx trx = Trx.get(TrxPrefix.getPrefix(), true);
trx.start();
try {
BlackListedManager.editBlackListed(ctx, bean, trx.getTrxName());
trx.commit();
} catch (OperationException ex) {
trx.rollback();
throw ex;
} finally {
trx.close();
}
request.getSession().setAttribute(Constants.BLACKLISTED_DETAILS, bean);
return mapping.findForward(EDIT_CHEQUE);
}
@Override
public void init(int WindowNo, FormFrame frame) {
m_WindowNo = WindowNo;
m_frame = frame;
log.info(
"WinNo="
+ m_WindowNo
+ " - AD_Client_ID="
+ m_AD_Client_ID
+ ", AD_Org_ID="
+ m_AD_Org_ID
+ ", By="
+ m_by);
Env.getCtx().setIsSOTrx(m_WindowNo, false);
trans = Trx.get("XX_ModifyDebtCredit");
try {
// UI
jbInit();
dynInit();
frame.getContentPane().add(mainPanel, BorderLayout.CENTER);
frame.getContentPane().add(statusBar, BorderLayout.SOUTH);
} catch (Exception e) {
log.log(Level.SEVERE, "", e);
}
}
private DBIndex getDBIndex(String tableName, String indexName, final String trxName)
throws SQLException {
final String[] indexColsFromDB = new String[30];
int indexLengthDB = 0;
DBIndex dbIndex = null;
final DatabaseMetaData md = Trx.get(trxName, true).getConnection().getMetaData();
if (md.storesUpperCaseIdentifiers()) tableName = tableName.toUpperCase();
else if (md.storesLowerCaseIdentifiers()) tableName = tableName.toLowerCase();
final String catalog = "REFERENCE";
final String schema = null;
ResultSet rs = null;
try {
rs = md.getIndexInfo(catalog, schema, tableName, false, true);
while (rs.next()) {
final String dbIndexName = rs.getString("INDEX_NAME");
if (dbIndexName != null && indexName.equalsIgnoreCase(dbIndexName)) {
if (dbIndex == null) {
dbIndex = new DBIndex();
dbIndex.name = dbIndexName;
dbIndex.isUnique = true;
}
String columnName = rs.getString("COLUMN_NAME");
int pos = rs.getShort("ORDINAL_POSITION");
if (pos > 0) {
// EDB returns varchar index columns wrapped with double
// quotes, hence comparing
// after stripping the quotes
if (columnName.startsWith("\"") && columnName.endsWith("\"")) {
columnName = columnName.substring(1, columnName.length() - 1);
}
indexColsFromDB[pos - 1] = columnName;
if (pos > indexLengthDB) indexLengthDB = pos;
}
boolean isNonUnique = rs.getBoolean("NON_UNIQUE");
if (isNonUnique) dbIndex.isUnique = false;
dbIndex.filterCondition = rs.getString("FILTER_CONDITION");
}
}
} finally {
DB.close(rs);
rs = null;
}
//
if (dbIndex == null || indexLengthDB <= 0) return null;
//
dbIndex.columnNames = new String[indexLengthDB];
for (int i = 0; i < indexLengthDB; i++) {
dbIndex.columnNames[i] = indexColsFromDB[i];
}
// Arrays.sort(dbIndex.columnNames);
//
return dbIndex;
}
/**
* Notify users
*
* @param users AD_User_ID list
* @param subject email subject
* @param message email message
* @param attachments
* @return how many email were sent
*/
private int notifyUsers(
Collection<Integer> users, String subject, String message, Collection<File> attachments) {
int countMail = 0;
for (int user_id : users) {
MUser user = MUser.get(getCtx(), user_id);
if (user.isNotificationEMail()) {
if (m_client.sendEMailAttachments(user_id, subject, message, attachments)) {
countMail++;
}
}
if (user.isNotificationNote()) {
Trx trx = null;
try {
trx = Trx.get(Trx.createTrxName("AP_NU"), true);
// Notice
int AD_Message_ID = 52244; /* TODO - Hardcoded message=notes */
MNote note = new MNote(getCtx(), AD_Message_ID, user_id, trx.getTrxName());
note.setClientOrg(m_model.getAD_Client_ID(), m_model.getAD_Org_ID());
note.setTextMsg(message);
note.saveEx();
// Attachment
MAttachment attachment =
new MAttachment(getCtx(), MNote.Table_ID, note.getAD_Note_ID(), trx.getTrxName());
for (File f : attachments) {
attachment.addEntry(f);
}
attachment.setTextMsg(message);
attachment.saveEx();
countMail++;
trx.commit();
} catch (Throwable e) {
if (trx != null) trx.rollback();
} finally {
if (trx != null) trx.close();
}
}
}
return countMail;
}
public ActionForward deactivateCheque(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws ApplicationException, OperationException {
ActionForward fwd = init(mapping, form, request, response);
if (fwd != null) return fwd;
Properties ctx = TmkJSPEnv.getCtx(request);
BlackListForm f = (BlackListForm) form;
f.validate(mapping, request);
BlackListedBean bean = (BlackListedBean) f.getBean();
Integer blackListedId = bean.getBlackListedId();
if (blackListedId == null) {
throw new OperationException(
"Cannot deactivate Blacklisted Cheques. Cause BlackListed id cannot be null!");
}
Trx trx = Trx.get(TrxPrefix.getPrefix(), true);
try {
trx.start();
BlackListedManager.deactivateBListedCheque(ctx, blackListedId.intValue(), trx.getTrxName());
trx.commit();
} catch (OperationException e) {
trx.rollback();
postGlobalError("error.deactivate.customer", request);
mapping.getInputForward();
} finally {
trx.close();
}
return mapping.findForward(DEACTIVATE_CHEQUE);
}
/**
* Process Alert
*
* @param alert alert
* @return true if processed
*/
private boolean processAlert(MAlert alert) {
if (!alert.isValid()) return false;
log.info("" + alert);
StringBuffer message = new StringBuffer(alert.getAlertMessage()).append(Env.NL);
//
boolean valid = true;
boolean processed = false;
ArrayList<File> attachments = new ArrayList<File>();
MAlertRule[] rules = alert.getRules(false);
for (int i = 0; i < rules.length; i++) {
if (i > 0) message.append(Env.NL);
String trxName = null; // assume r/o
MAlertRule rule = rules[i];
if (!rule.isValid()) continue;
log.fine("" + rule);
// Pre
String sql = rule.getPreProcessing();
if (sql != null && sql.length() > 0) {
int no = DB.executeUpdate(sql, false, trxName);
if (no == -1) {
ValueNamePair error = CLogger.retrieveError();
rule.setErrorMsg("Pre=" + error.getName());
m_errors.append("Pre=" + error.getName());
rule.setIsValid(false);
rule.save();
valid = false;
break;
}
} // Pre
// The processing
sql = rule.getSql(true);
try {
String text = null;
if (MSysConfig.getBooleanValue(
"ALERT_SEND_ATTACHMENT_AS_XLS", true, Env.getAD_Client_ID(getCtx())))
text = getExcelReport(rule, sql, trxName, attachments);
else text = getPlainTextReport(rule, sql, trxName, attachments);
if (text != null && text.length() > 0) {
message.append(text);
processed = true;
}
} catch (Exception e) {
rule.setErrorMsg("Select=" + e.getLocalizedMessage());
m_errors.append("Select=" + e.getLocalizedMessage());
rule.setIsValid(false);
rule.save();
valid = false;
break;
}
// Post
sql = rule.getPostProcessing();
if (sql != null && sql.length() > 0) {
int no = DB.executeUpdate(sql, false, trxName);
if (no == -1) {
ValueNamePair error = CLogger.retrieveError();
rule.setErrorMsg("Post=" + error.getName());
m_errors.append("Post=" + error.getName());
rule.setIsValid(false);
rule.save();
valid = false;
break;
}
} // Post
/** Trx */
if (trxName != null) {
Trx trx = Trx.get(trxName, false);
if (trx != null) {
trx.commit();
trx.close();
}
}
} // for all rules
// Update header if error
if (!valid) {
alert.setIsValid(false);
alert.save();
return false;
}
// Nothing to report
if (!processed) {
m_summary.append(alert.getName()).append("=No Result - ");
return true;
}
//
// Report footer - Date Generated
DateFormat df = DisplayType.getDateFormat(DisplayType.DateTime);
message.append("\n\n");
message
.append(Msg.translate(getCtx(), "Date"))
.append(" : ")
.append(df.format(new Timestamp(System.currentTimeMillis())));
Collection<Integer> users = alert.getRecipientUsers();
int countMail = notifyUsers(users, alert.getAlertSubject(), message.toString(), attachments);
m_summary.append(alert.getName()).append(" (EMails+Notes=").append(countMail).append(") - ");
return valid;
} // processAlert
/** @param e */
public void valueChange(ValueChangeEvent e) {
if (gridTab.isProcessed()) // only active records
{
Object source = e.getSource();
if (source instanceof WEditor) {
// Elaine 2009/05/06
WEditor editor = (WEditor) source;
GridField gridField = editor.getGridField();
if (gridField != null) {
if (!gridField.isEditable(true)) {
logger.config("(" + gridTab.toString() + ") " + e.getPropertyName());
return;
}
} else if (!editor.isReadWrite()) {
logger.config("(" + gridTab.toString() + ") " + e.getPropertyName());
return;
}
} else {
logger.config("(" + gridTab.toString() + ") " + e.getPropertyName());
return;
}
} // processed
logger.config(
"("
+ gridTab.toString()
+ ") "
+ e.getPropertyName()
+ "="
+ e.getNewValue()
+ " ("
+ e.getOldValue()
+ ") "
+ (e.getOldValue() == null ? "" : e.getOldValue().getClass().getName()));
// Get Row/Col Info
GridTable mTable = gridTab.getTableModel();
int row = gridTab.getCurrentRow();
int col = mTable.findColumn(e.getPropertyName());
//
if (e.getNewValue() == null
&& e.getOldValue() != null
&& e.getOldValue().toString().length() > 0) // some editors return "" instead of null
// this is the original code from GridController, don't know what it does there but
// it breaks ignore button for web ui
// mTable.setChanged (true);
mTable.setValueAt(e.getNewValue(), row, col);
else {
Object newValue = e.getNewValue();
Integer newValues[] = null;
if (newValue instanceof Integer[]) {
newValues = ((Integer[]) newValue);
newValue = newValues[0];
if (newValues.length > 1) {
Integer valuesCopy[] = new Integer[newValues.length - 1];
System.arraycopy(newValues, 1, valuesCopy, 0, valuesCopy.length);
newValues = valuesCopy;
} else {
newValues = null;
}
} else if (newValue instanceof Object[]) {
logger.severe("Multiple values can only be processed for IDs (Integer)");
throw new IllegalArgumentException(
"Multiple Selection values not available for this field. " + e.getPropertyName());
}
mTable.setValueAt(newValue, row, col);
// Force Callout
if (e.getPropertyName().equals("S_ResourceAssignment_ID")) {
GridField mField = gridTab.getField(col);
if (mField != null && mField.getCallout().length() > 0) {
gridTab.processFieldChange(mField); // Dependencies & Callout
}
}
if (newValues != null && newValues.length > 0) {
// Save data, since record need to be used for generating clones.
if (!gridTab.dataSave(false)) {
throw new AdempiereException("SaveError");
}
// Retrieve the current record ID
int recordId = gridTab.getKeyID(gridTab.getCurrentRow());
Trx trx = Trx.get(Trx.createTrxName(), true);
trx.start();
try {
saveMultipleRecords(
Env.getCtx(),
gridTab.getTableName(),
e.getPropertyName(),
recordId,
newValues,
trx.getTrxName());
trx.commit();
gridTab.dataRefreshAll();
} catch (Exception ex) {
trx.rollback();
logger.severe(ex.getMessage());
throw new AdempiereException("SaveError");
} finally {
trx.close();
}
}
}
} // ValueChange
/**
* Save LOB. see also org.compiere.session.ServerBean#updateLOB
*
* @param trxName trx name
* @return true if saved
*/
public boolean save(String trxName) {
if (m_value == null
|| (!(m_value instanceof String || m_value instanceof byte[]))
|| (m_value instanceof String && m_value.toString().length() == 0)
|| (m_value instanceof byte[] && ((byte[]) m_value).length == 0)) {
StringBuffer sql =
new StringBuffer("UPDATE ")
.append(m_tableName)
.append(" SET ")
.append(m_columnName)
.append("=null WHERE ")
.append(m_whereClause);
int no = DB.executeUpdate(sql.toString(), trxName);
log.fine("save [" + trxName + "] #" + no + " - no data - set to null - " + m_value);
if (no == 0) log.warning("[" + trxName + "] - not updated - " + sql);
return true;
}
StringBuffer sql =
new StringBuffer("UPDATE ")
.append(m_tableName)
.append(" SET ")
.append(m_columnName)
.append("=? WHERE ")
.append(m_whereClause);
//
log.fine("[" + trxName + "] - Local - " + m_value);
// Connection
Trx trx = null;
if (trxName != null) trx = Trx.get(trxName, false);
Connection con = null;
// Create Connection
if (trx != null) con = trx.getConnection();
if (con == null) con = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED);
if (con == null) {
log.log(Level.SEVERE, "Could not get Connection");
return false;
}
PreparedStatement pstmt = null;
boolean success = true;
try {
pstmt = con.prepareStatement(sql.toString());
if (DisplayType.isText(m_displayType)) pstmt.setString(1, (String) m_value);
else pstmt.setBytes(1, (byte[]) m_value);
int no = pstmt.executeUpdate();
if (no != 1) {
log.warning("[" + trxName + "] - Not updated #" + no + " - " + sql);
success = false;
}
} catch (Throwable e) {
log.log(Level.SEVERE, "[" + trxName + "] - " + sql, e);
success = false;
} finally {
DB.close(pstmt);
pstmt = null;
}
// Success - commit local trx
if (success) {
if (trx != null) {
trx = null;
con = null;
} else {
try {
con.commit();
} catch (Exception e) {
log.log(Level.SEVERE, "[" + trxName + "] - commit ", e);
success = false;
} finally {
try {
con.close();
} catch (SQLException e) {
}
con = null;
}
}
}
// Error - roll back
if (!success) {
log.severe("[" + trxName + "] - rollback");
if (trx != null) {
trx.rollback();
trx = null;
con = null;
} else {
try {
con.rollback();
} catch (Exception ee) {
log.log(Level.SEVERE, "[" + trxName + "] - rollback", ee);
} finally {
try {
con.close();
} catch (SQLException e) {
}
con = null;
}
}
}
return success;
} // save
/**
* Process
*
* @return message
* @throws Exception
*/
protected String doIt() throws Exception {
// TODO Error handling
String sql = null;
try {
int no;
Trx trx = Trx.get(get_TrxName(), false);
// Create Elements from ColumnNames
sql =
"SELECT DISTINCT ColumnName, Name, Description, Help, EntityType "
+ "FROM AD_COLUMN c WHERE NOT EXISTS "
+ "(SELECT 1 FROM AD_ELEMENT e "
+ " WHERE UPPER(c.ColumnName)=UPPER(e.ColumnName))"
+ " AND c.isActive = 'Y'";
PreparedStatement pstmt = DB.prepareStatement(sql, get_TrxName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String columnName = rs.getString(1);
String name = rs.getString(2);
String desc = rs.getString(3);
String help = rs.getString(4);
String entityType = rs.getString(5);
M_Element elem = new M_Element(getCtx(), columnName, entityType, get_TrxName());
elem.setDescription(desc);
elem.setHelp(help);
elem.setPrintName(name);
elem.save();
}
pstmt.close();
rs.close();
trx.commit(true);
// Create Elements for Process Parameters which are centrally maintained
sql =
"SELECT DISTINCT ColumnName, Name, Description, Help, EntityType "
+ " FROM AD_PROCESS_PARA p "
+ " WHERE NOT EXISTS "
+ " (SELECT 1 FROM AD_ELEMENT e "
+ " WHERE UPPER(p.ColumnName)=UPPER(e.ColumnName))"
+ " AND p.isCentrallyMaintained = 'Y'"
+ " AND p.isActive = 'Y'";
pstmt = DB.prepareStatement(sql, get_TrxName());
rs = pstmt.executeQuery();
while (rs.next()) {
String columnName = rs.getString(1);
String name = rs.getString(2);
String desc = rs.getString(3);
String help = rs.getString(4);
String entityType = rs.getString(5);
// TODO AD_SEQ system !!!
M_Element elem = new M_Element(getCtx(), columnName, entityType, get_TrxName());
elem.setDescription(desc);
elem.setHelp(help);
elem.setPrintName(name);
elem.save();
}
pstmt.close();
rs.close();
trx.commit(true);
log.info("Adding missing Element Translations");
sql =
"INSERT INTO AD_ELEMENT_TRL (AD_Element_ID, AD_LANGUAGE, AD_Client_ID, AD_Org_ID,"
+ " IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " Name, PrintName, Description, Help, IsTranslated)"
+ " SELECT m.AD_Element_ID, l.AD_LANGUAGE, m.AD_Client_ID, m.AD_Org_ID,"
+ " m.IsActive, m.Created, m.CreatedBy, m.Updated, m.UpdatedBy,"
+ " m.Name, m.PrintName, m.Description, m.Help, 'N'"
+ " FROM AD_ELEMENT m, AD_LANGUAGE l"
+ " WHERE l.IsActive = 'Y' AND l.IsSystemLanguage = 'Y'"
+ " AND AD_Element_ID || AD_LANGUAGE NOT IN "
+ " (SELECT AD_Element_ID || AD_LANGUAGE FROM AD_ELEMENT_TRL)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
log.info("Creating link from Element to Column");
sql =
"UPDATE AD_COLUMN c"
+ " SET AD_Element_id ="
+ " (SELECT AD_Element_ID FROM AD_ELEMENT e"
+ " WHERE UPPER(c.ColumnName)=UPPER(e.ColumnName))"
+ " WHERE AD_Element_ID IS NULL";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
log.info("Deleting unused Elements");
sql =
"DELETE AD_ELEMENT_TRL"
+ " WHERE AD_Element_ID IN"
+ " (SELECT AD_Element_ID FROM AD_ELEMENT e "
+ " WHERE NOT EXISTS"
+ " (SELECT 1 FROM AD_COLUMN c WHERE UPPER(e.ColumnName)=UPPER(c.ColumnName))"
+ " AND NOT EXISTS"
+ " (SELECT 1 FROM AD_PROCESS_PARA p WHERE UPPER(e.ColumnName)=UPPER(p.ColumnName)))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows deleted: " + no);
trx.commit(true);
sql =
"DELETE AD_ELEMENT e"
+ " WHERE AD_Element_ID >= 1000000 AND NOT EXISTS"
+ " (SELECT 1 FROM AD_COLUMN c WHERE UPPER(e.ColumnName)=UPPER(c.ColumnName))"
+ " AND NOT EXISTS"
+ " (SELECT 1 FROM AD_PROCESS_PARA p WHERE UPPER(e.ColumnName)=UPPER(p.ColumnName))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows deleted: " + no);
trx.commit(true);
// Columns
log.info("Synchronize Column");
sql =
" UPDATE AD_COLUMN c"
+ " SET (ColumnName, Name, Description, Help) ="
+ " (SELECT ColumnName, Name, Description, Help"
+ " FROM AD_ELEMENT e WHERE c.AD_Element_ID=e.AD_Element_ID),"
+ " Updated = SYSDATE"
+ " WHERE EXISTS (SELECT 1 FROM AD_ELEMENT e "
+ " WHERE c.AD_Element_ID=e.AD_Element_ID"
+ " AND (c.ColumnName <> e.ColumnName OR c.Name <> e.Name "
+ " OR NVL(c.Description,' ') <> NVL(e.Description,' ') OR NVL(c.Help,' ') <> NVL(e.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Fields should now be synchronized
log.info("Synchronize Field");
sql =
" UPDATE AD_FIELD f"
+ " SET (Name, Description, Help) = "
+ " (SELECT e.Name, e.Description, e.Help"
+ " FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Updated = SYSDATE"
+ " WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND EXISTS (SELECT 1 FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE f.AD_Column_ID=c.AD_Column_ID"
+ " AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL"
+ " AND (f.Name <> e.Name OR NVL(f.Description,' ') <> NVL(e.Description,' ') OR NVL(f.Help,' ') <> NVL(e.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Field Translations
log.info("Synchronize Field Translations");
sql =
"UPDATE AD_FIELD_TRL trl"
+ " SET Name = (SELECT e.Name FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID "
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " Description = (SELECT e.Description FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID "
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " Help = (SELECT e.Help FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID "
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " IsTranslated = (SELECT e.IsTranslated FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID "
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " Updated = SYSDATE"
+ " WHERE EXISTS (SELECT 1 FROM AD_FIELD f, AD_ELEMENT_TRL e, AD_COLUMN c"
+ " WHERE trl.AD_Field_ID=f.AD_Field_ID"
+ " AND f.AD_Column_ID=c.AD_Column_ID"
+ " AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL"
+ " AND trl.AD_LANGUAGE=e.AD_LANGUAGE"
+ " AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND (trl.Name <> e.Name OR NVL(trl.Description,' ') <> NVL(e.Description,' ') OR NVL(trl.Help,' ') <> NVL(e.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Fields should now be synchronized
log.info("Synchronize PO Field");
sql =
"UPDATE AD_FIELD f"
+ " SET Name = (SELECT e.PO_Name FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Description = (SELECT e.PO_Description FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Help = (SELECT e.PO_Help FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Updated = SYSDATE"
+ " WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND EXISTS (SELECT 1 FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE f.AD_Column_ID=c.AD_Column_ID"
+ " AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL"
+ " AND (f.Name <> e.PO_Name OR NVL(f.Description,' ') <> NVL(e.PO_Description,' ') OR NVL(f.Help,' ') <> NVL(e.PO_Help,' '))"
+ " AND e.PO_Name IS NOT NULL)"
+ " AND EXISTS (SELECT 1 FROM AD_TAB t, AD_WINDOW w"
+ " WHERE f.AD_Tab_ID=t.AD_Tab_ID"
+ " AND t.AD_Window_ID=w.AD_Window_ID"
+ " AND w.IsSOTrx='N')";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Field Translations
log.info("Synchronize PO Field Translations");
sql =
" UPDATE AD_FIELD_TRL trl"
+ " SET Name = (SELECT e.PO_Name FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " Description = (SELECT e.PO_Description FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID "
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " Help = (SELECT e.PO_Help FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " IsTranslated = (SELECT e.IsTranslated FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_FIELD f"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE AND e.AD_Element_ID=c.AD_Element_ID "
+ " AND c.AD_Column_ID=f.AD_Column_ID AND f.AD_Field_ID=trl.AD_Field_ID),"
+ " Updated = SYSDATE"
+ " WHERE EXISTS (SELECT 1 FROM AD_FIELD f, AD_ELEMENT_TRL e, AD_COLUMN c"
+ " WHERE trl.AD_Field_ID=f.AD_Field_ID"
+ " AND f.AD_Column_ID=c.AD_Column_ID"
+ " AND c.AD_Element_ID=e.AD_Element_ID AND c.AD_Process_ID IS NULL"
+ " AND trl.AD_LANGUAGE=e.AD_LANGUAGE"
+ " AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND (trl.Name <> e.PO_Name OR NVL(trl.Description,' ') <> NVL(e.PO_Description,' ') OR NVL(trl.Help,' ') <> NVL(e.PO_Help,' '))"
+ " AND e.PO_Name IS NOT NULL)"
+ " AND EXISTS (SELECT 1 FROM AD_FIELD f, AD_TAB t, AD_WINDOW w"
+ " WHERE trl.AD_Field_ID=f.AD_Field_ID"
+ " AND f.AD_Tab_ID=t.AD_Tab_ID"
+ " AND t.AD_Window_ID=w.AD_Window_ID"
+ " AND w.IsSOTrx='N')";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Fields from Process
log.info("Synchronize Field from Process");
sql =
"UPDATE AD_FIELD f"
+ " SET Name = (SELECT p.Name FROM AD_PROCESS p, AD_COLUMN c WHERE p.AD_Process_ID=c.AD_Process_ID"
+ " AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Description = (SELECT p.Description FROM AD_PROCESS p, AD_COLUMN c WHERE p.AD_Process_ID=c.AD_Process_ID"
+ " AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Help = (SELECT p.Help FROM AD_PROCESS p, AD_COLUMN c WHERE p.AD_Process_ID=c.AD_Process_ID"
+ " AND c.AD_Column_ID=f.AD_Column_ID),"
+ " Updated = SYSDATE"
+ " WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND EXISTS (SELECT 1 FROM AD_PROCESS p, AD_COLUMN c"
+ " WHERE c.AD_Process_ID=p.AD_Process_ID AND f.AD_Column_ID=c.AD_Column_ID"
+ " AND (f.Name<>p.Name OR NVL(f.Description,' ')<>NVL(p.Description,' ') OR NVL(f.Help,' ')<>NVL(p.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Field Translations from Process
log.info("Synchronize Field Trl from Process Trl");
sql =
"UPDATE AD_FIELD_TRL trl"
+ " SET Name = (SELECT p.Name FROM AD_PROCESS_TRL p, AD_COLUMN c, AD_FIELD f"
+ " WHERE p.AD_Process_ID=c.AD_Process_ID AND c.AD_Column_ID=f.AD_Column_ID"
+ " AND f.AD_Field_ID=trl.AD_Field_ID AND p.AD_LANGUAGE=trl.AD_LANGUAGE),"
+ " Description = (SELECT p.Description FROM AD_PROCESS_TRL p, AD_COLUMN c, AD_FIELD f"
+ " WHERE p.AD_Process_ID=c.AD_Process_ID AND c.AD_Column_ID=f.AD_Column_ID"
+ " AND f.AD_Field_ID=trl.AD_Field_ID AND p.AD_LANGUAGE=trl.AD_LANGUAGE),"
+ " Help = (SELECT p.Help FROM AD_PROCESS_TRL p, AD_COLUMN c, AD_FIELD f "
+ " WHERE p.AD_Process_ID=c.AD_Process_ID AND c.AD_Column_ID=f.AD_Column_ID"
+ " AND f.AD_Field_ID=trl.AD_Field_ID AND p.AD_LANGUAGE=trl.AD_LANGUAGE),"
+ " IsTranslated = (SELECT p.IsTranslated FROM AD_PROCESS_TRL p, AD_COLUMN c, AD_FIELD f"
+ " WHERE p.AD_Process_ID=c.AD_Process_ID AND c.AD_Column_ID=f.AD_Column_ID"
+ " AND f.AD_Field_ID=trl.AD_Field_ID AND p.AD_LANGUAGE=trl.AD_LANGUAGE),"
+ " Updated = SYSDATE"
+ " WHERE EXISTS (SELECT 1 FROM AD_PROCESS_TRL p, AD_COLUMN c, AD_FIELD f"
+ " WHERE c.AD_Process_ID=p.AD_Process_ID AND f.AD_Column_ID=c.AD_Column_ID"
+ " AND f.AD_Field_ID=trl.AD_Field_ID AND p.AD_LANGUAGE=trl.AD_LANGUAGE"
+ " AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND (trl.Name<>p.Name OR NVL(trl.Description,' ')<>NVL(p.Description,' ') OR NVL(trl.Help,' ')<>NVL(p.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Sync Parameter ColumnName
sql =
"UPDATE AD_PROCESS_PARA f"
+ " SET ColumnName = (SELECT e.ColumnName FROM AD_ELEMENT e"
+ " WHERE UPPER(e.ColumnName)=UPPER(f.ColumnName))"
// +" WHERE e.ColumnName=f.ColumnName)"
+ " WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND EXISTS (SELECT 1 FROM AD_ELEMENT e"
+ " WHERE UPPER(e.ColumnName)=UPPER(f.ColumnName)"
+ " AND e.ColumnName<>f.ColumnName)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Parameter Fields
sql =
"UPDATE AD_PROCESS_PARA p"
+ " SET IsCentrallyMaintained = 'N'"
+ " WHERE IsCentrallyMaintained <> 'N'"
+ " AND NOT EXISTS (SELECT 1 FROM AD_ELEMENT e WHERE p.ColumnName=e.ColumnName)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Parameter Fields
log.info("Synchronize Process Parameter");
sql =
"UPDATE AD_PROCESS_PARA f"
+ " SET Name = (SELECT e.Name FROM AD_ELEMENT e"
+ " WHERE e.ColumnName=f.ColumnName),"
+ " Description = (SELECT e.Description FROM AD_ELEMENT e"
+ " WHERE e.ColumnName=f.ColumnName),"
+ " Help = (SELECT e.Help FROM AD_ELEMENT e"
+ " WHERE e.ColumnName=f.ColumnName),"
+ " Updated = SYSDATE"
+ " WHERE f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND EXISTS (SELECT 1 FROM AD_ELEMENT e"
+ " WHERE e.ColumnName=f.ColumnName"
+ " AND (f.Name <> e.Name OR NVL(f.Description,' ') <> NVL(e.Description,' ') OR NVL(f.Help,' ') <> NVL(e.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Parameter Translations
log.info("Synchronize Process Parameter Trl");
sql =
"UPDATE AD_PROCESS_PARA_TRL trl"
+ " SET Name = (SELECT et.Name FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f"
+ " WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE AND et.AD_Element_ID=e.AD_Element_ID"
+ " AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),"
+ " Description = (SELECT et.Description FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f"
+ " WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE AND et.AD_Element_ID=e.AD_Element_ID"
+ " AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),"
+ " Help = (SELECT et.Help FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f"
+ " WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE AND et.AD_Element_ID=e.AD_Element_ID"
+ " AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),"
+ " IsTranslated = (SELECT et.IsTranslated FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f"
+ " WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE AND et.AD_Element_ID=e.AD_Element_ID"
+ " AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),"
+ " Updated = SYSDATE"
+ " WHERE EXISTS (SELECT 1 FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f"
+ " WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE AND et.AD_Element_ID=e.AD_Element_ID"
+ " AND e.ColumnName=f.ColumnName AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID"
+ " AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'"
+ " AND (trl.Name <> et.Name OR NVL(trl.Description,' ') <> NVL(et.Description,' ') OR NVL(trl.Help,' ') <> NVL(et.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Workflow Node - Window
log.info("Synchronize Workflow Node from Window");
sql =
"UPDATE AD_WF_NODE n"
+ " SET Name = (SELECT w.Name FROM AD_WINDOW w"
+ " WHERE w.AD_Window_ID=n.AD_Window_ID),"
+ " Description = (SELECT w.Description FROM AD_WINDOW w"
+ " WHERE w.AD_Window_ID=n.AD_Window_ID),"
+ " Help = (SELECT w.Help FROM AD_WINDOW w"
+ " WHERE w.AD_Window_ID=n.AD_Window_ID)"
+ " WHERE n.IsCentrallyMaintained = 'Y'"
+ " AND EXISTS (SELECT 1 FROM AD_WINDOW w"
+ " WHERE w.AD_Window_ID=n.AD_Window_ID"
+ " AND (w.Name <> n.Name OR NVL(w.Description,' ') <> NVL(n.Description,' ') OR NVL(w.Help,' ') <> NVL(n.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Workflow Translations - Window
log.info("Synchronize Workflow Node Trl from Window Trl");
sql =
"UPDATE AD_WF_NODE_TRL trl"
+ " SET Name = (SELECT t.Name FROM AD_WINDOW_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE),"
+ " Description = (SELECT t.Description FROM AD_WINDOW_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE),"
+ " Help = (SELECT t.Help FROM AD_WINDOW_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_WINDOW_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Window_ID=t.AD_Window_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE AND n.IsCentrallyMaintained='Y' AND n.IsActive='Y'"
+ " AND (trl.Name <> t.Name OR NVL(trl.Description,' ') <> NVL(t.Description,' ') OR NVL(trl.Help,' ') <> NVL(t.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Workflow Node - Form
log.info("Synchronize Workflow Node from Form");
sql =
"UPDATE AD_WF_NODE n"
+ " SET (Name, Description, Help) = (SELECT f.Name, f.Description, f.Help"
+ " FROM AD_FORM f"
+ " WHERE f.AD_Form_ID=n.AD_Form_ID)"
+ " WHERE n.IsCentrallyMaintained = 'Y'"
+ " AND EXISTS (SELECT 1 FROM AD_FORM f"
+ " WHERE f.AD_Form_ID=n.AD_Form_ID"
+ " AND (f.Name <> n.Name OR NVL(f.Description,' ') <> NVL(n.Description,' ') OR NVL(f.Help,' ') <> NVL(n.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Workflow Translations - Form
log.info("Synchronize Workflow Node Trl from Form Trl");
sql =
" UPDATE AD_WF_NODE_TRL trl"
+ " SET (Name, Description, Help) = (SELECT t.Name, t.Description, t.Help"
+ " FROM AD_FORM_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Form_ID=t.AD_Form_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_FORM_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Form_ID=t.AD_Form_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE AND n.IsCentrallyMaintained='Y' AND n.IsActive='Y'"
+ " AND (trl.Name <> t.Name OR NVL(trl.Description,' ') <> NVL(t.Description,' ') OR NVL(trl.Help,' ') <> NVL(t.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Workflow Node - Report
log.info("Synchronize Workflow Node from Process");
sql =
"UPDATE AD_WF_NODE n"
+ " SET (Name, Description, Help) = (SELECT f.Name, f.Description, f.Help"
+ " FROM AD_PROCESS f"
+ " WHERE f.AD_Process_ID=n.AD_Process_ID)"
+ " WHERE n.IsCentrallyMaintained = 'Y'"
+ " AND EXISTS (SELECT 1 FROM AD_PROCESS f"
+ " WHERE f.AD_Process_ID=n.AD_Process_ID"
+ " AND (f.Name <> n.Name OR NVL(f.Description,' ') <> NVL(n.Description,' ') OR NVL(f.Help,' ') <> NVL(n.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Workflow Translations - Form
log.info("Synchronize Workflow Node Trl from Process Trl");
sql =
"UPDATE AD_WF_NODE_TRL trl"
+ " SET (Name, Description, Help) = (SELECT t.Name, t.Description, t.Help"
+ " FROM AD_PROCESS_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Process_ID=t.AD_Process_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_PROCESS_TRL t, AD_WF_NODE n"
+ " WHERE trl.AD_WF_Node_ID=n.AD_WF_Node_ID AND n.AD_Process_ID=t.AD_Process_ID"
+ " AND trl.AD_LANGUAGE=t.AD_LANGUAGE AND n.IsCentrallyMaintained='Y' AND n.IsActive='Y'"
+ " AND (trl.Name <> t.Name OR NVL(trl.Description,' ') <> NVL(t.Description,' ') OR NVL(trl.Help,' ') <> NVL(t.Help,' ')))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Need centrally maintained flag here!
log.info("Synchronize PrintFormatItem Name from Element");
sql =
"UPDATE AD_PRINTFORMATITEM pfi"
+ " SET Name = (SELECT e.Name "
+ " FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=pfi.AD_Column_ID)"
+ " WHERE pfi.IsCentrallyMaintained='Y'"
+ " AND EXISTS (SELECT 1 "
+ " FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=pfi.AD_Column_ID"
+ " AND e.Name<>pfi.Name)"
+ " AND EXISTS (SELECT 1 FROM AD_CLIENT"
+ " WHERE AD_Client_ID=pfi.AD_Client_ID AND IsMultiLingualDocument='Y')";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
log.info("Synchronize PrintFormatItem PrintName from Element");
sql =
"UPDATE AD_PRINTFORMATITEM pfi"
+ " SET PrintName = (SELECT e.PrintName "
+ " FROM AD_ELEMENT e, AD_COLUMN c"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=pfi.AD_Column_ID)"
+ " WHERE pfi.IsCentrallyMaintained='Y'"
+ " AND EXISTS (SELECT 1 "
+ " FROM AD_ELEMENT e, AD_COLUMN c, AD_PRINTFORMAT pf"
+ " WHERE e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=pfi.AD_Column_ID"
+ " AND LENGTH(pfi.PrintName) > 0"
+ " AND e.PrintName<>pfi.PrintName"
+ " AND pf.AD_PrintFormat_ID=pfi.AD_PrintFormat_ID"
+ " AND pf.IsForm='N' AND IsTableBased='Y')"
+ " AND EXISTS (SELECT 1 FROM AD_CLIENT "
+ " WHERE AD_Client_ID=pfi.AD_Client_ID AND IsMultiLingualDocument='Y')";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
log.info("Synchronize PrintFormatItem Trl from Element Trl (Multi-Lingual)");
sql =
"UPDATE AD_PRINTFORMATITEM_TRL trl"
+ " SET PrintName = (SELECT e.PrintName"
+ " FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_PRINTFORMATITEM pfi"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE"
+ " AND e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=pfi.AD_Column_ID"
+ " AND pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID)"
+ " WHERE EXISTS (SELECT 1 "
+ " FROM AD_ELEMENT_TRL e, AD_COLUMN c, AD_PRINTFORMATITEM pfi, AD_PRINTFORMAT pf"
+ " WHERE e.AD_LANGUAGE=trl.AD_LANGUAGE"
+ " AND e.AD_Element_ID=c.AD_Element_ID"
+ " AND c.AD_Column_ID=pfi.AD_Column_ID"
+ " AND pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID"
+ " AND pfi.IsCentrallyMaintained='Y'"
+ " AND LENGTH(pfi.PrintName) > 0"
+ " AND (e.PrintName<>trl.PrintName OR trl.PrintName IS NULL)"
+ " AND pf.AD_PrintFormat_ID=pfi.AD_PrintFormat_ID "
+ " AND pf.IsForm='N' AND IsTableBased='Y')"
+ " AND EXISTS (SELECT 1 FROM AD_CLIENT "
+ " WHERE AD_Client_ID=trl.AD_Client_ID AND IsMultiLingualDocument='Y')";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
log.info("Synchronize PrintFormatItem Trl (Not Multi-Lingual)");
sql =
"UPDATE AD_PRINTFORMATITEM_TRL trl"
+ " SET PrintName = (SELECT pfi.PrintName"
+ " FROM AD_PRINTFORMATITEM pfi"
+ " WHERE pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID)"
+ " WHERE EXISTS (SELECT 1 "
+ " FROM AD_PRINTFORMATITEM pfi, AD_PRINTFORMAT pf"
+ " WHERE pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID"
+ " AND pfi.IsCentrallyMaintained='Y'"
+ " AND LENGTH(pfi.PrintName) > 0"
+ " AND pfi.PrintName<>trl.PrintName"
+ " AND pf.AD_PrintFormat_ID=pfi.AD_PrintFormat_ID "
+ " AND pf.IsForm='N' AND pf.IsTableBased='Y')"
+ " AND EXISTS (SELECT 1 FROM AD_CLIENT "
+ " WHERE AD_Client_ID=trl.AD_Client_ID AND IsMultiLingualDocument='N')";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
log.info("Reset PrintFormatItem Trl where not used in base table");
sql =
"UPDATE AD_PRINTFORMATITEM_TRL trl"
+ " SET PrintName = NULL"
+ " WHERE PrintName IS NOT NULL"
+ " AND EXISTS (SELECT 1"
+ " FROM AD_PRINTFORMATITEM pfi"
+ " WHERE pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID"
+ " AND pfi.IsCentrallyMaintained='Y'"
+ " AND (LENGTH (pfi.PrintName) = 0 OR pfi.PrintName IS NULL))";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
/**
* SELECT e.PrintName "Element", pfi.PrintName "FormatItem", trl.AD_Language, trl.PrintName
* "Trl" FROM AD_Element e INNER JOIN AD_Column c ON (e.AD_Element_ID=c.AD_Element_ID) INNER
* JOIN AD_PrintFormatItem pfi ON (c.AD_Column_ID=pfi.AD_Column_ID) INNER JOIN
* AD_PrintFormatItem_Trl trl ON (pfi.AD_PrintFormatItem_ID=trl.AD_PrintFormatItem_ID) WHERE
* pfi.AD_PrintFormatItem_ID=?
*/
// Sync Names - Window
log.info("Synchronizing Menu with Window");
sql =
"UPDATE AD_MENU m"
+ " SET Name = (SELECT Name FROM AD_WINDOW w WHERE m.AD_Window_ID=w.AD_Window_ID),"
+ " Description = (SELECT Description FROM AD_WINDOW w WHERE m.AD_Window_ID=w.AD_Window_ID)"
+ " WHERE m.AD_Window_ID IS NOT NULL"
+ " AND m.Action = 'W'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
sql =
"UPDATE AD_MENU_TRL mt"
+ " SET Name = (SELECT wt.Name FROM AD_WINDOW_TRL wt, AD_MENU m "
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID "
+ " AND mt.AD_LANGUAGE=wt.AD_LANGUAGE),"
+ " Description = (SELECT wt.Description FROM AD_WINDOW_TRL wt, AD_MENU m "
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID "
+ " AND mt.AD_LANGUAGE=wt.AD_LANGUAGE),"
+ " IsTranslated = (SELECT wt.IsTranslated FROM AD_WINDOW_TRL wt, AD_MENU m "
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID "
+ " AND mt.AD_LANGUAGE=wt.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_WINDOW_TRL wt, AD_MENU m "
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Window_ID=wt.AD_Window_ID "
+ " AND mt.AD_LANGUAGE=wt.AD_LANGUAGE"
+ " AND m.AD_Window_ID IS NOT NULL"
+ " AND m.Action = 'W'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'"
+ ")";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Sync Names - Process
log.info("Synchronizing Menu with Processes");
sql =
"UPDATE AD_MENU m"
+ " SET Name = (SELECT p.Name FROM AD_PROCESS p WHERE m.AD_Process_ID=p.AD_Process_ID),"
+ " Description = (SELECT p.Description FROM AD_PROCESS p WHERE m.AD_Process_ID=p.AD_Process_ID)"
+ " WHERE m.AD_Process_ID IS NOT NULL"
+ " AND m.Action IN ('R', 'P')"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
sql =
"UPDATE AD_MENU_TRL mt"
+ " SET Name = (SELECT pt.Name FROM AD_PROCESS_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE),"
+ " Description = (SELECT pt.Description FROM AD_PROCESS_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE),"
+ " IsTranslated = (SELECT pt.IsTranslated FROM AD_PROCESS_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_PROCESS_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Process_ID=pt.AD_Process_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE"
+ " AND m.AD_Process_ID IS NOT NULL"
+ " AND m.Action IN ('R', 'P')"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'"
+ ")";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Sync Names = Form
log.info("Synchronizing Menu with Forms");
sql =
"UPDATE AD_MENU m"
+ " SET Name = (SELECT Name FROM AD_FORM f WHERE m.AD_Form_ID=f.AD_Form_ID),"
+ " Description = (SELECT Description FROM AD_FORM f WHERE m.AD_Form_ID=f.AD_Form_ID)"
+ " WHERE m.AD_Form_ID IS NOT NULL"
+ " AND m.Action = 'X'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
sql =
"UPDATE AD_MENU_TRL mt"
+ " SET Name = (SELECT ft.Name FROM AD_FORM_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE),"
+ " Description = (SELECT ft.Description FROM AD_FORM_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE),"
+ " IsTranslated = (SELECT ft.IsTranslated FROM AD_FORM_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_FORM_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Form_ID=ft.AD_Form_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE"
+ " AND m.AD_Form_ID IS NOT NULL"
+ " AND m.Action = 'X'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'"
+ ")";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Sync Names - Workflow
log.info("Synchronizing Menu with Workflows");
sql =
"UPDATE AD_MENU m"
+ " SET Name = (SELECT p.Name FROM AD_WORKFLOW p WHERE m.AD_Workflow_ID=p.AD_Workflow_ID),"
+ " Description = (SELECT p.Description FROM AD_WORKFLOW p WHERE m.AD_Workflow_ID=p.AD_Workflow_ID)"
+ " WHERE m.AD_Workflow_ID IS NOT NULL"
+ " AND m.Action = 'F'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
sql =
"UPDATE AD_MENU_TRL mt"
+ " SET Name = (SELECT pt.Name FROM AD_WORKFLOW_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE),"
+ " Description = (SELECT pt.Description FROM AD_WORKFLOW_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE),"
+ " IsTranslated = (SELECT pt.IsTranslated FROM AD_WORKFLOW_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_WORKFLOW_TRL pt, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Workflow_ID=pt.AD_Workflow_ID"
+ " AND mt.AD_LANGUAGE=pt.AD_LANGUAGE"
+ " AND m.AD_Workflow_ID IS NOT NULL"
+ " AND m.Action = 'F'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'"
+ ")";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Sync Names = Task
log.info("Synchronizing Menu with Tasks");
sql =
"UPDATE AD_MENU m"
+ " SET Name = (SELECT Name FROM AD_TASK f WHERE m.AD_Task_ID=f.AD_Task_ID),"
+ " Description = (SELECT Description FROM AD_TASK f WHERE m.AD_Task_ID=f.AD_Task_ID)"
+ " WHERE m.AD_Task_ID IS NOT NULL"
+ " AND m.Action = 'T'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
sql =
"UPDATE AD_MENU_TRL mt"
+ " SET Name = (SELECT ft.Name FROM AD_TASK_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE),"
+ " Description = (SELECT ft.Description FROM AD_TASK_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE),"
+ " IsTranslated = (SELECT ft.IsTranslated FROM AD_TASK_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 FROM AD_TASK_TRL ft, AD_MENU m"
+ " WHERE mt.AD_Menu_ID=m.AD_Menu_ID AND m.AD_Task_ID=ft.AD_Task_ID"
+ " AND mt.AD_LANGUAGE=ft.AD_LANGUAGE"
+ " AND m.AD_Task_ID IS NOT NULL"
+ " AND m.Action = 'T'"
+ " AND m.IsCentrallyMaintained='Y' AND m.IsActive='Y'"
+ ")";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Column Name + Element
log.info("Synchronizing Column with Element");
sql =
"UPDATE AD_COLUMN c"
+ " SET (Name,Description,Help) ="
+ " (SELECT e.Name,e.Description,e.Help "
+ " FROM AD_ELEMENT e WHERE c.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE EXISTS "
+ " (SELECT 1 FROM AD_ELEMENT e "
+ " WHERE c.AD_Element_ID=e.AD_Element_ID"
+ " AND c.Name<>e.Name)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
sql =
"UPDATE AD_COLUMN_TRL ct"
+ " SET Name = (SELECT e.Name"
+ " FROM AD_COLUMN c INNER JOIN AD_ELEMENT_TRL e ON (c.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE ct.AD_Column_ID=c.AD_Column_ID AND ct.AD_LANGUAGE=e.AD_LANGUAGE)"
+ " WHERE EXISTS "
+ " (SELECT 1 FROM AD_COLUMN c INNER JOIN AD_ELEMENT_TRL e ON (c.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE ct.AD_Column_ID=c.AD_Column_ID AND ct.AD_LANGUAGE=e.AD_LANGUAGE"
+ " AND ct.Name<>e.Name)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" rows updated: " + no);
trx.commit(true);
// Table Name + Element
log.info("Synchronizing Table with Element");
sql =
"UPDATE AD_TABLE t "
+ "SET (Name,Description) = (SELECT e.Name,e.Description FROM AD_ELEMENT e "
+ "WHERE t.TableName||'_ID'=e.ColumnName) "
+ "WHERE EXISTS (SELECT 1 FROM AD_ELEMENT e "
+ "WHERE t.TableName||'_ID'=e.ColumnName "
+ "AND t.Name<>e.Name)";
no = DB.executeUpdate(sql, false, get_TrxName());
trx.commit(true);
log.info(" rows updated: " + no);
sql =
"UPDATE AD_TABLE_TRL tt"
+ " SET Name = (SELECT e.Name "
+ " FROM AD_TABLE t INNER JOIN AD_ELEMENT ex ON (t.TableName||'_ID'=ex.ColumnName)"
+ " INNER JOIN AD_ELEMENT_TRL e ON (ex.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_LANGUAGE=e.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 "
+ " FROM AD_TABLE t INNER JOIN AD_ELEMENT ex ON (t.TableName||'_ID'=ex.ColumnName)"
+ " INNER JOIN AD_ELEMENT_TRL e ON (ex.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_LANGUAGE=e.AD_LANGUAGE"
+ " AND tt.Name<>e.Name)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" trl rows updated: " + no);
trx.commit(true);
// Trl Table Name + Element
sql =
"UPDATE AD_TABLE t"
+ " SET (Name,Description) = (SELECT e.Name||' Trl', e.Description "
+ " FROM AD_ELEMENT e "
+ " WHERE SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=e.ColumnName)"
+ " WHERE TableName LIKE '%_Trl'"
+ " AND EXISTS (SELECT 1 FROM AD_ELEMENT e "
+ " WHERE SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=e.ColumnName"
+ " AND t.Name<>e.Name)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" trl rows updated: " + no);
trx.commit(true);
sql =
" UPDATE AD_TABLE_TRL tt"
+ " SET Name = (SELECT e.Name || ' **'"
+ " FROM AD_TABLE t INNER JOIN AD_ELEMENT ex ON (SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=ex.ColumnName)"
+ " INNER JOIN AD_ELEMENT_TRL e ON (ex.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_LANGUAGE=e.AD_LANGUAGE)"
+ " WHERE EXISTS (SELECT 1 "
+ " FROM AD_TABLE t INNER JOIN AD_ELEMENT ex ON (SUBSTR(t.TableName,1,LENGTH(t.TableName)-4)||'_ID'=ex.ColumnName)"
+ " INNER JOIN AD_ELEMENT_TRL e ON (ex.AD_Element_ID=e.AD_Element_ID)"
+ " WHERE tt.AD_Table_ID=t.AD_Table_ID AND tt.AD_LANGUAGE=e.AD_LANGUAGE"
+ " AND t.TableName LIKE '%_Trl'"
+ " AND tt.Name<>e.Name)";
no = DB.executeUpdate(sql, false, get_TrxName());
log.info(" trl rows updated: " + no);
trx.commit(true);
} catch (Exception e) {
log.log(Level.SEVERE, "@Failed@: " + e.getLocalizedMessage(), e);
throw e;
}
return "@OK@";
}
private Trx getTrx() {
return trxName == null ? null : Trx.get(trxName, false);
}
/** Save */
public void onOK() {
log.config("Activity=" + m_activity);
if (m_activity == null) {
Clients.showBusy(null, false);
return;
}
int AD_User_ID = Env.getAD_User_ID(Env.getCtx());
String textMsg = fTextMsg.getValue();
//
MWFNode node = m_activity.getNode();
Object forward = null; // fForward.getValue();
// ensure activity is ran within a transaction - [ 1953628 ]
Trx trx = null;
try {
trx = Trx.get(Trx.createTrxName("FWFA"), true);
m_activity.set_TrxName(trx.getTrxName());
if (forward != null) {
log.config("Forward to " + forward);
int fw = ((Integer) forward).intValue();
if (fw == AD_User_ID || fw == 0) {
log.log(Level.SEVERE, "Forward User="******"CannotForward");
trx.rollback();
trx.close();
return;
}
}
// User Choice - Answer
else if (MWFNode.ACTION_UserChoice.equals(node.getAction())) {
if (m_column == null) m_column = node.getColumn();
// Do we have an answer?
int dt = m_column.getAD_Reference_ID();
String value = fAnswerText.getText();
if (dt == DisplayType.YesNo || dt == DisplayType.List) {
ListItem li = fAnswerList.getSelectedItem();
if (li != null) value = li.getValue().toString();
}
if (value == null || value.length() == 0) {
FDialog.error(m_WindowNo, this, "FillMandatory", Msg.getMsg(Env.getCtx(), "Answer"));
trx.rollback();
trx.close();
return;
}
//
log.config("Answer=" + value + " - " + textMsg);
try {
m_activity.setUserChoice(AD_User_ID, value, dt, textMsg);
} catch (Exception e) {
log.log(Level.SEVERE, node.getName(), e);
FDialog.error(m_WindowNo, this, "Error", e.toString());
trx.rollback();
trx.close();
return;
}
}
// User Action
else {
log.config("Action=" + node.getAction() + " - " + textMsg);
try {
// ensure activity is ran within a transaction
m_activity.setUserConfirmation(AD_User_ID, textMsg);
} catch (Exception e) {
log.log(Level.SEVERE, node.getName(), e);
FDialog.error(m_WindowNo, this, "Error", e.toString());
trx.rollback();
trx.close();
return;
}
}
trx.commit();
} finally {
Clients.showBusy(null, false);
if (trx != null) trx.close();
}
// Next
loadActivities();
display(-1);
} // onOK
// @emmie custom
public String generate(
IStatusBar statusBar, KeyNamePair docTypeKNPair, int C_POS_ID, String docActionSelected)
// @emmie custom
{
String info = "";
String trxName = Trx.createTrxName("IVG");
Trx trx = Trx.get(trxName, true); // trx needs to be committed too
setSelectionActive(false); // prevents from being called twice
statusBar.setStatusLine(Msg.getMsg(Env.getCtx(), "InvGenerateGen"));
statusBar.setStatusDB(String.valueOf(getSelection().size()));
// Prepare Process
int AD_Process_ID = 0;
if (docTypeKNPair.getKey() == MRMA.Table_ID) {
AD_Process_ID =
PROCESS_C_INVOICE_GENERATERMA_MANUAL; // C_Invoice_GenerateRMA -
// org.adempiere.process.InvoiceGenerateRMA
} else {
AD_Process_ID = PROCESS_C_INVOICE_GENERATE_MANUAL; // HARDCODED C_InvoiceCreate
}
MPInstance instance = new MPInstance(Env.getCtx(), AD_Process_ID, 0);
if (!instance.save()) {
info = Msg.getMsg(Env.getCtx(), "ProcessNoInstance");
return info;
}
// insert selection
StringBuffer insert = new StringBuffer();
insert.append("INSERT INTO T_SELECTION(AD_PINSTANCE_ID, T_SELECTION_ID) ");
int counter = 0;
for (Integer selectedId : getSelection()) {
counter++;
if (counter > 1) insert.append(" UNION ");
insert.append("SELECT ");
insert.append(instance.getAD_PInstance_ID());
insert.append(", ");
insert.append(selectedId);
insert.append(" FROM DUAL ");
if (counter == 1000) {
if (DB.executeUpdate(insert.toString(), trxName) < 0) {
String msg = "No Invoices"; // not translated!
info = msg;
log.config(msg);
trx.rollback();
return info;
}
insert = new StringBuffer();
insert.append("INSERT INTO T_SELECTION(AD_PINSTANCE_ID, T_SELECTION_ID) ");
counter = 0;
}
}
if (counter > 0) {
if (DB.executeUpdate(insert.toString(), trxName) < 0) {
String msg = "No Invoices"; // not translated!
info = msg;
log.config(msg);
trx.rollback();
return info;
}
}
ProcessInfo pi = new ProcessInfo("", AD_Process_ID);
pi.setAD_PInstance_ID(instance.getAD_PInstance_ID());
// Add Parameters
MPInstancePara para = new MPInstancePara(instance, 10);
para.setParameter("Selection", "Y");
if (!para.save()) {
String msg = "No Selection Parameter added"; // not translated
info = msg;
log.log(Level.SEVERE, msg);
return info;
}
para = new MPInstancePara(instance, 20);
para.setParameter("DocAction", docActionSelected);
if (!para.save()) {
String msg = "No DocAction Parameter added"; // not translated
info = msg;
log.log(Level.SEVERE, msg);
return info;
}
// @emmie custom
para = new MPInstancePara(instance, 30);
para.setParameter("C_POS_ID", C_POS_ID);
if (!para.save()) {
String msg = "No C_POS_ID Parameter added"; // not translated
info = msg;
log.log(Level.SEVERE, msg);
return info;
}
// @emmie custom
setTrx(trx);
setProcessInfo(pi);
return info;
} // generateInvoices
/**
* Check if column exists in database and modify. If not create column.
*
* @param tablename
* @param columnname
* @param v_AD_Reference_ID
* @param v_FieldLength
* @param v_DefaultValue
* @param v_IsMandatory
*/
private int createColumn(Properties ctx, MTable table, MColumn column, boolean doAlter) {
int no = 0;
String sql = null;
ResultSet rst = null;
ResultSet rsc = null;
Connection conn = null;
Trx trx = Trx.get(getTrxName(ctx), true);
if (!trx.commit()) return 0;
try {
// Find Column in Database
conn = trx.getConnection();
DatabaseMetaData md = conn.getMetaData();
String catalog = DB.getDatabase().getCatalog();
String schema = DB.getDatabase().getSchema();
String tableName = table.getTableName();
String columnName = column.getColumnName();
if (DB.isOracle()) {
tableName = tableName.toUpperCase();
columnName = columnName.toUpperCase();
} else if (DB.isPostgreSQL()) {
tableName = tableName.toLowerCase();
columnName = columnName.toLowerCase();
}
rst = md.getTables(catalog, schema, tableName, new String[] {"TABLE"});
if (!rst.next()) {
// table doesn't exist
sql = table.getSQLCreate();
} else {
//
rsc = md.getColumns(catalog, schema, tableName, columnName);
if (rsc.next()) {
if (doAlter) {
// update existing column
boolean notNull = DatabaseMetaData.columnNoNulls == rsc.getInt("NULLABLE");
sql = column.getSQLModify(table, column.isMandatory() != notNull);
}
} else {
// No existing column
sql = column.getSQLAdd(table);
}
rsc.close();
rsc = null;
}
rst.close();
rst = null;
// execute modify or add if needed
if (sql != null && sql.trim().length() > 0) {
log.info(sql);
if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1) {
no = DB.executeUpdate(sql, false, trx.getTrxName());
if (no == -1) return 0;
} else {
String statements[] = sql.split(DB.SQLSTATEMENT_SEPARATOR);
for (int i = 0; i < statements.length; i++) {
int count = DB.executeUpdate(statements[i], false, trx.getTrxName());
if (count == -1) {
return 0;
}
no += count;
}
}
}
trx.commit(true);
} catch (SQLException e) {
log.log(Level.SEVERE, e.getLocalizedMessage(), e);
if (rsc != null) {
try {
rsc.close();
} catch (SQLException e1) {
}
rsc = null;
}
if (rst != null) {
try {
rst.close();
} catch (SQLException e1) {
}
rst = null;
}
trx.rollback();
return 0;
}
return 1;
}