internal note

Use the GenericOvertimeRule.java and WeeklyOvertimeLaborAllocationRule.java in the UTA Shared\UTA Code Templates\src\com\uta\rules\laborallocation

Use GRI1004_SPEC_UTA_WeeklyOvertimeLaborAllocationRule_48428 for weekly overtime labor allocation.

SQL AddBatch and ExecuteBatch Example in Scheduled Task

Using addBatch and executeBatch methods of a preparedStatement can increase performance. While it might be possible to simply execute the SQL to update all the records in one statement, should that take longer than the scheduled task allows, it would timeout. Therefore we need to iterate over a result set updating records and calling the isInterrupted method to insure that task does not time out. Also calling commit every once in a while prevents the SQL has not committed error.

private void updateTeamSupervisorDates(DBConnection conn, Map<String, Object> parameters)

throws Exception
{

  PreparedStatement psUpdate = null;

  Date domesticStartDate = getDateParameter(PARAM_DOMESTIC_START_DATE, parameters);
  Date intlStartDate = getDateParameter(PARAM_INTERNATIONAL_START_DATE, parameters);

  if (domesticStartDate == null || intlStartDate == null) {
    return;
  }

  if (logger.isDebugEnabled()) {
    logger.debug("domesticStartDate=" + domesticStartDate.toString());
  logger.debug("intlStartDate=" + intlStartDate.toString());
}

PreparedStatement ps = null;

ResultSet rs = null;

try {

String domesticDate = conn.encodeDate(domesticStartDate);
String intlDate = conn.encodeDate(intlStartDate);
StringBuilder b = new StringBuilder();

b.append("select WBUT_ID, WBG_FLAG5, ");
b.append("case WBG_FLAG5 ");
b.append("when 'I' then ").append(intlDate).append(" ");
b.append("when 'D' then ").append(domesticDate).append(" ");
b.append("else null end as NEW_SUPR_DATE ");
b.append("from workbrain_user_team wut with (nolock) ");
b.append("join workbrain_user wu with (nolock) on wut.WBU_ID = wu.WBU_ID ");
b.append("join WORKBRAIN_GROUP wg with (nolock) on wu.WBG_ID = wg.WBG_ID ");
b.append("where (WBG_FLAG5 = 'I' ");
b.append("and WBUT_START_DATE <> ").append(intlDate).append(" ");
b.append("and WBUT_END_DATE >= ").append(intlDate).append(") ");
b.append("or (WBG_FLAG5 = 'D' ");
b.append("and WBUT_START_DATE <> ").append(domesticDate).append(" ");
b.append("and WBUT_END_DATE >= ").append(domesticDate).append(") ");
b.append("and wut.WBU_ID > 3 ");

  if (logger.isDebugEnabled()) {
    logger.debug("SQL: " + b.toString());
  }

ps = conn.prepareStatement(b.toString());
psUpdate = conn.prepareStatement("UPDATE workbrain_user_team set WBUT_START_DATE = ?       WHERE WBUT_ID = ?");

int batchCount = 0;
rs = ps.executeQuery();

while(rs.next() && !isInterrupted()) {
  long wbutId = rs.getLong(1);
  Timestamp newStartDate = rs.getTimestamp(3);

  if`(newStartDate != null) {
    psUpdate.setTimestamp(1, newStartDate);
    psUpdate.setLong(2, wbutId);
    psUpdate.addBatch();

    if (!isInterrupted() && ((++batchCount) >= 100)) {
      psUpdate.executeBatch();
      conn.commit();
      batchCount = 0;

    }
  }
}

if (!isInterrupted() && (batchCount > 0)) {
  psUpdate.executeBatch();
  batchCount = 0;
  conn.commit();
}

  } finally {
    SQLUtil.cleanUp(ps, rs);
    SQLUtil.cleanUp(psUpdate);
  }
}

SQL AddBatch and ExecuteBatch Example in Scheduled Task

Using addBatch and executeBatch methods of a preparedStatement can increase performance. While it might be possible to simply execute the SQL to update all the records in one statement, should that take longer than the scheduled task allows, it would timeout. Therefore we need to iterate over a result set updating records and calling the isInterrupted method to insure that task does not time out. Also calling commit every once in a while prevents the SQL has not committed error.

private void updateTeamSupervisorDates(DBConnection conn, Map<String, Object> parameters)

throws Exception
{

  PreparedStatement psUpdate = null;

  Date domesticStartDate = getDateParameter(PARAM_DOMESTIC_START_DATE, parameters);
  Date intlStartDate = getDateParameter(PARAM_INTERNATIONAL_START_DATE, parameters);

  if (domesticStartDate == null || intlStartDate == null) {
    return;
  }

  if (logger.isDebugEnabled()) {
    logger.debug("domesticStartDate=" + domesticStartDate.toString());
  logger.debug("intlStartDate=" + intlStartDate.toString());
}

PreparedStatement ps = null;

ResultSet rs = null;

try {

String domesticDate = conn.encodeDate(domesticStartDate);
String intlDate = conn.encodeDate(intlStartDate);
StringBuilder b = new StringBuilder();

b.append("select WBUT_ID, WBG_FLAG5, ");
b.append("case WBG_FLAG5 ");
b.append("when 'I' then ").append(intlDate).append(" ");
b.append("when 'D' then ").append(domesticDate).append(" ");
b.append("else null end as NEW_SUPR_DATE ");
b.append("from workbrain_user_team wut with (nolock) ");
b.append("join workbrain_user wu with (nolock) on wut.WBU_ID = wu.WBU_ID ");
b.append("join WORKBRAIN_GROUP wg with (nolock) on wu.WBG_ID = wg.WBG_ID ");
b.append("where (WBG_FLAG5 = 'I' ");
b.append("and WBUT_START_DATE <> ").append(intlDate).append(" ");
b.append("and WBUT_END_DATE >= ").append(intlDate).append(") ");
b.append("or (WBG_FLAG5 = 'D' ");
b.append("and WBUT_START_DATE <> ").append(domesticDate).append(" ");
b.append("and WBUT_END_DATE >= ").append(domesticDate).append(") ");
b.append("and wut.WBU_ID > 3 ");

  if (logger.isDebugEnabled()) {
    logger.debug("SQL: " + b.toString());
  }

ps = conn.prepareStatement(b.toString());
psUpdate = conn.prepareStatement("UPDATE workbrain_user_team set WBUT_START_DATE = ?       WHERE WBUT_ID = ?");

int batchCount = 0;
rs = ps.executeQuery();

while(rs.next() && !isInterrupted()) {
  long wbutId = rs.getLong(1);
  Timestamp newStartDate = rs.getTimestamp(3);

  if`(newStartDate != null) {
    psUpdate.setTimestamp(1, newStartDate);
    psUpdate.setLong(2, wbutId);
    psUpdate.addBatch();

    if (!isInterrupted() && ((++batchCount) >= 100)) {
      psUpdate.executeBatch();
      conn.commit();
      batchCount = 0;
    }
  }
}

if (!isInterrupted() && (batchCount > 0)) {
  psUpdate.executeBatch();
  batchCount = 0;
  conn.commit();
}

  } finally {
    SQLUtil.cleanUp(ps, rs);
    SQLUtil.cleanUp(psUpdate);
  }
}

Method to get a count of days worked (by David Domenico)

I never knew there was a method to get a count of days worked based on eligible tcodes and hour types. E.g. wbData.getCountWorkSummaryRange

protected int getAvgMinutes(WBData wbData, Date start, Date end, ParametersResolved pars) throws SQLException {

  int daysWrkd = wbData.getCountWorkSummaryRange(wbData.getRuleData().getWorkSummary().getWrksId(), start, end, null, null, pars.avgTcodes, pars.avgTcodesInclusive, pars.avgHtypes, pars.avgHtypesInclusive, pars.detPrem, 1);

  int minsWrkd = wbData.getMinutesWorkDetailPremiumRange(start, end, null, null, pars.avgTcodes, pars.avgTcodesInclusive, pars.avgHtypes, pars.avgHtypesInclusive, pars.detPrem);

  if (logger.isDebugEnabled()) logger.debug("Days worked : " + daysWrkd + ", minsWorked :" + minsWrkd + " from :" + start + " to end :" + end);

                                int ret = 0;

                                if (daysWrkd != 0) {

                                                ret = minsWrkd / daysWrkd;

                                }

                                return ret;

                }

How to get the leave start and end dates from the xml

If you ever have to get the leave start and end dates from the xml of a business object here is one way to do it. This assumes you’re working in an alert or scheduled task.

  

//Start_Date20130225 000000

//End_Date20130228 000000

BOFormInstance bo = new BOFormInstance();

bo.setXML(busObjXml);

String startDate = WorkflowUtil.getFieldValueAsString(bo, "Start_Date");

String endDate = WorkflowUtil.getFieldValueAsString(bo, "End_Date");

There You have it.

Thanks Dave!

New Workbrain WB5.1 Release Notes! (Infor Workforce Management Workbrain)

Be aware of some major updates in this release. Make sure you review the latest release notes before upgrading your PRODUCTION environments. While they are great updates they will cause you few headaches.

Here a list to mention a few:

• Java 6 (JDK 1.6)

• JDBC 4

• Genrics

• Third Party Libraries

• Web Services

• ID DataTypes - ID value that had a data type of Integer (or int) have been changed to use a data type of Long (or long).

• Cognos 8

• Application Server Platforms - WAS 7.0 & BEA 10.3.2

• Database Platforms - DB2 v9.5.0.3 / DB2 for z/OS v8.11 / Oracle 11g R1 / MS SQL Server 2008

Make sure you get the latest release notes from Infor's support website and review it all in detail.

How to change ActionProcess / Interaction / Workflow Message Subjects

To configure the workflow message subjects sometimes you need to look in Error Translations, I know strange place... but it's there...

Go to Main. -> System Administration -> Data Localization -> Error Translation

To change the Interaction message subject text search for eg.: AP_USER_ACTION_SUBJECT

Here few others:

AP_USER_ACTION_SUBJECT

AP_USER_GROUP_ACTION_SUBJECT

AP_ROLE_ACTION_SUBJECT

AP_ROLE_GROUP_ACTION_SUBJECT

AP_EXCEPTION_ACTION_SUBJECT

AP_EMP_GROUP_ACTION_SUBJECT

AP_EMP_ACTION_SUBJECT

Get list of employee's entitlement policys entitlements which have custom unit dates

select * from ENT_ENTITLEMENT
where ENT_ID in (
select ENT_ID from ENT_POLICY_ENTITLEMENT
where ENTPOL_ID in (
select ENTPOL_ID from ENT_EMP_POLICY
where EMP_ID = ?
and ENTEMPPOL_ENABLED = 'Y'
and ? between ENTEMPPOL_START_DATE and ENTEMPPOL_END_DATE
)
and ? between ENTPOLENT_START_DATE and ENTPOLENT_END_DATE
)
and ? between ENT_START_DATE and ENT_END_DATE
and ENT_APPLY_ON_UNIT = 'CUSTOM'

Limiting Entitlement Carry overs

To limit the amount carried over on an entitlement, Max Value and Action on Max is used.

If an employee is setup to receive 40 hour vacation per year and only takes 20 hours, then the next year they would receive another 40 hours giving them a total of 60. If you wanted to not carry over the 20 hours from the previous year, set Max Value to 40 and set Action on Max to com.workbrain.app.modules.entitlements.DefaultEntAction

Did you know?

Rule Builder Editor – If you get an error when trying to access this tool (or any of the tools), go to System Registry settings and change the 'forcehttpsredirect' registry
parameter to be ‘false’.