SA-6215

Summary

Error generated when Leveled or Skilled Group Router activity is used in Pega 7.1.6 Application.

Error Messages

There was an error running the pickBalancedOperator query: There was a problem getting a list: code: 0 SQLState: 42883 Message: ERROR: function newid() does not exist.

Steps to Reproduce

1. Create an Assignment.
2. Use "ToSkilledGroup" Router activity - Map Workgroup and Skill parameter.
3. Run the flow, the case moves to Workgroup manager instead of the user present in the Workgroup with the associated Skills.


ROOTCAUSE    

The SQL generated in pickBalancedOperator does not handle postgresql related functions correctly and hence the error is generated.

Resolution

The check for DB (to add DB specific functions) is incorrect. For Microsoft SQL Server and PostgreSQL there is “SQL” string and hence the query generated is for Microsoft SQL Server but is run on PostgreSQL. Hence, no results are generated.

pickBalancedOpertor is an available function. The below code contains the  required changes.


ClipboardPage dbPage = tools.createPage("Code-Pega-List", "pyPBO");
ClipboardProperty cpPreparedValues = dbPage.getProperty(".pyPreparedValues");

// Get desired and required skills
ClipboardProperty cpDesiredSkillsPL = assignPage.getProperty("pxSkillsDesired");
ClipboardProperty cpRequisiteSkillsPL = assignPage.getProperty("pxSkillsRequired");

String aGoalTime = assignPage.getString(".pxGoalTime");
String actionTime = assignPage.getString(".pyActionTime");
int aEffort = workPage.getProperty("pyEffortEstimate").toInteger() - workPage.getProperty("pyEffortActual").toInteger();

// get the database product name
String strDBProdName = "";
try {
    String strClassName = "Data-Admin-DB-Name";
    strDBProdName = tools.getDatabase().getClassDef(strClassName).getDBProductName(tools).toLowerCase();
} catch (DatabaseException ex) {
    oLog.error(ex.getMessage());
    strDBProdName = "";
}

// Are we going to check the workload?
boolean bCheckWorkload = true;
if(pastGoalMultiplier == 0 && pastDeadlineMultiplier == 0)
    bCheckWorkload = false;

// Assignment weight column override
if(assignmentWeightColumn == null || assignmentWeightColumn.equals(""))
    assignmentWeightColumn = "pxUrgencyAssign";

// make sure the assignment weight column is an actual scalar property in the system
ImmutablePropertyInfo ipi = tools.getDictionary().getImmutablePropertyInfo("Assign-Worklist", assignmentWeightColumn);
if (ipi == null || ipi.getMode() != ImmutablePropertyInfo.MODE_STRING)
{
    dbPage.removeFromClipboard();
    return "";
}

// Prepare the query
// Select operators, join with required skills, left join with desired skills, left join with workload
int iRequisiteSkillCount = 0;
int iDesiredSkillCount = 0;
java.util.Iterator itRequisiteSkills = cpRequisiteSkillsPL.iterator();
java.util.Iterator itDesiredSkills = cpDesiredSkillsPL.iterator();
StringBuffer sql = new StringBuffer();

sql.append("SELECT ops.pzInsKey AS \"pzInsKey\", ops.pyUserIdentifier AS \"pyUserIdentifier\", ops.pyOpAvailable AS \"pyOpAvailable\", s4.pxDesiredCount AS \"pxDesiredCount\" ");
sql.append("FROM {CLASS:Data-Admin-Operator-Id} ops ");

// Join with the required skills
if(itRequisiteSkills.hasNext()) {    // If there are any required skills listed...
    sql.append("INNER JOIN ");
    sql.append("(SELECT pxInsIndexedKey, COUNT(DISTINCT pxSkillName) AS pxRequiredCount ");
    sql.append("FROM {CLASS:Index-OperatorSkills} s1 ");
    while(itRequisiteSkills.hasNext()) {
        ClipboardProperty skillProperty =(ClipboardProperty)itRequisiteSkills.next();
        if (skillProperty != null)
        {
            String skillName = skillProperty.getName();
            String skillValue = skillProperty.getStringValue();
            if (skillName != null && !skillName.equals("") && skillValue != null && !skillValue.equals(""))
            {
                if(iRequisiteSkillCount == 0)
                    sql.append("WHERE");
                else
                    sql.append("OR");
                sql.append(" (pxSkillName = '"+skillName+"') AND (pxSkillRating >= "+skillValue+") ");
                iRequisiteSkillCount++;
            }
        }
    }
    sql.append("GROUP BY s1.pxInsIndexedKey) s2 ");
    sql.append("ON (ops.pzInsKey = s2.pxInsIndexedKey) ");
}

// Join with the desired skills
sql.append("LEFT OUTER JOIN ");
sql.append("(SELECT pxInsIndexedKey, COUNT(DISTINCT pxSkillName) AS pxDesiredCount");

if(strDBProdName.indexOf("db2") != -1) {
    sql.append(", RAND() as IDX ");                                // For DB2, prep a random seed for the sorting at the end.
}

sql.append(" FROM {CLASS:Index-OperatorSkills} s3 ");

if(desiredSkillMultiplier != 0) {
    while(itDesiredSkills.hasNext()) {
        ClipboardProperty skillProperty =(ClipboardProperty)itDesiredSkills.next();
        if (skillProperty != null)
        {
            String skillName = skillProperty.getName();
            String skillValue = skillProperty.getStringValue();
            if (skillName != null && !skillName.equals("") && skillValue != null && !skillValue.equals(""))
            {
                if(iDesiredSkillCount == 0)
                    sql.append("WHERE");
                else
                    sql.append("OR");
                sql.append(" (pxSkillName = '"+skillName+"') AND (pxSkillRating >= "+skillValue+") ");
                iDesiredSkillCount++;
            }
        }
    }
}
sql.append("GROUP BY s3.pxInsIndexedKey) s4 ");
sql.append("ON (ops.pzInsKey = s4.pxInsIndexedKey) ");

if(bCheckWorkload) {
    // Join with the workload
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT pxAssignedOperatorID, SUM("+assignmentWeightColumn+") AS totalUrgency, ");

    if(strDBProdName.indexOf("oracle") != -1) {
        sql.append("sum( CASE ");
        sql.append("WHEN (pxGoalTime > sysdate) THEN "+assignmentWeightColumn+" ");                        // Assignment is on time
        sql.append("WHEN (pxDeadlineTime > sysdate) THEN ("+assignmentWeightColumn+" * "+pastGoalMultiplier+") ");    // Assignment is past goal
        sql.append("ELSE ("+assignmentWeightColumn+" * "+pastDeadlineMultiplier+") END) AS pxUserLoad ");                        // Assignment is past deadline
    } else if(strDBProdName.startsWith("microsoft")) {
        sql.append("sum( CASE ");
        sql.append("WHEN (pxGoalTime > getdate()) THEN "+assignmentWeightColumn+" ");                        // Assignment is on time
        sql.append("WHEN (pxDeadlineTime > getdate()) THEN ("+assignmentWeightColumn+" * "+pastGoalMultiplier+") ");        // Assignment is past goal
        sql.append("ELSE ("+assignmentWeightColumn+" * "+pastDeadlineMultiplier+") END) AS pxUserLoad ");                            // Assignment is past deadline
    } else if(strDBProdName.indexOf("db2") != -1) {
        sql.append("sum( CASE ");
        sql.append("WHEN (pxGoalTime > CURRENT TIMESTAMP) THEN "+assignmentWeightColumn+" ");    // Assignment is on time
        sql.append("WHEN (pxDeadlineTime > CURRENT TIMESTAMP) THEN ("+assignmentWeightColumn+" * "+pastGoalMultiplier+") ");    // Assignment is past goal
        sql.append("ELSE ("+assignmentWeightColumn+" * "+pastDeadlineMultiplier+") END) AS pxUserLoad, ");                                // Assignment is past deadline
        sql.append("RAND() as IDX ");                                // For DB2, prep a random seed
    }
    else if(strDBProdName.startsWith("postgresql")){
         sql.append("sum( CASE ");
        sql.append("WHEN (pxGoalTime > localtimestamp) THEN "+assignmentWeightColumn+" ");                        // Assignment is on time
        sql.append("WHEN (pxDeadlineTime > localtimestamp) THEN ("+assignmentWeightColumn+" * "+pastGoalMultiplier+") ");        // Assignment is past goal
        sql.append("ELSE ("+assignmentWeightColumn+" * "+pastDeadlineMultiplier+") END) AS pxUserLoad ");                            // Assignment is past deadline
    }
    else {
        sql.append(""+assignmentWeightColumn+" AS pxUserLoad ");
    }

    sql.append("FROM {CLASS:Assign-Worklist} s5 GROUP BY pxAssignedOperatorID ) s6 ON (ops.pyUserIdentifier = s6.pxAssignedOperatorID) ");
}
// If there were no required skills, just desired; we don't need to limit this.
cpPreparedValues.add(workgroup);
if(iRequisiteSkillCount > 0)
    sql.append("WHERE (s2.pxRequiredCount = "+iRequisiteSkillCount+" AND ops.pyWorkGroup={pyPBO.pyPreparedValues(1)} AND ops.pyOpAvailable='true') ");
else
    sql.append("WHERE (ops.pyWorkGroup={pyPBO.pyPreparedValues(1)} AND ops.pyOpAvailable='true') ");

// Order by the desired count but make sure nulls are last.  Then order by the user load
String sAppend = ", ";
if(iDesiredSkillCount > 0 && bCheckWorkload)
    sql.append("ORDER BY CASE WHEN s6.pxUserLoad IS NULL THEN (s4.pxDesiredCount * -1) ELSE (s6.pxUserLoad- (s4.pxDesiredCount * "+desiredSkillMultiplier+")) END,  CASE WHEN s6.pxUserLoad IS NULL THEN 0 ELSE s6.pxUserLoad END DESC, s4.pxDesiredCount DESC");
else if (bCheckWorkload)
    sql.append("ORDER BY CASE WHEN s6.pxUserLoad IS NULL THEN 0 ELSE 1 END, s6.pxUserLoad");
else if (iDesiredSkillCount > 0)
    sql.append("ORDER BY s4.pxDesiredCount DESC");
else
    sAppend = "ORDER BY ";

// Add random sort to the end
if(strDBProdName.indexOf("oracle") != -1) {
    sql.append(sAppend + "dbms_random.value");
} else if(strDBProdName.startsWith("microsoft")) {
    sql.append(sAppend + "NEWID() ");
} else if(strDBProdName.indexOf("db2") != -1) {
    sql.append(sAppend + "s4.IDX ");
} else if(strDBProdName.startsWith("postgresql")){
 sql.append(sAppend + "random()");
}

int i;
try { i = tools.getDatabase().executeRDB(sql.toString(), dbPage); }
catch (DatabaseException ex)
{
    oLog.error("There was an error running the pickBalancedOperator query: " + ex.getMessage());
    dbPage.removeFromClipboard();
    return "";
}

ClipboardProperty results = dbPage.getProperty("pxResults");
java.util.Iterator iterator = results.iterator();
java.util.ArrayList operatorObjects = new java.util.ArrayList(results.size());

while(iterator.hasNext())
{
    String strUserID = ((ClipboardProperty)iterator.next()).getPageValue().getString("pyUserIdentifier");
            if(pega_procom_routing.isAvailable(strUserID, aGoalTime, actionTime, aEffort, tools))
            {
                dbPage.removeFromClipboard();
                return strUserID;
            }
}
dbPage.removeFromClipboard();
return "";