HP_RTBC:RTBCMain/HP_RTBC_INSTALL_DEPT_SEL Issues

After installing a series of updates (852825, 872267, 872523, 874418), I ran into a SQL error.  I am not sure which update it was, and actually, the find definition references pointed to 808749, which is HRMS 9.1 Bundle #1.

As I am not able to open a ticket on this issue currently, this is what I did to fix the problem …

I noticed this error in one of my CI logs.

SQLExec: 3 select list items in parameter list, but SQL has 1 select columns. (2,124) HP_RTBC.RTBCMain.OnExecute  Name:Job_RTBC  PCPC:4183  Statement:89
Called from:HP_RTBC.RTBCMain.OnExecute  Name:MainProcess  Statement:9
Called from:JOB.LASTUPDOPRID.SaveEdit  Statement:3(2,124)

As a result, I couldn’t hire anyone.  The problem appeared to be this line:

SQLExec(SQL.HP_RTBC_INSTALL_DEPT_SEL,
        &rsJobTmp(&i).JOB.SETID_DEPT.Value,
        &rsJobTmp(&i).JOB.DEPTID.Value,
        &rsJobTmp(&i).JOB.EFFDT.Value,
        &DeptUseEncumbrances,
        &ProcOption,
        &HP_AUTO_RTBC);

I decided to inspect the SQL, and I found this code:

SELECT USE_ENCUMBRANCES
FROM PS_DEPT_TBL A
WHERE A.SETID=:1
AND A.DEPTID =:2
AND A.EFFDT = (
SELECT MAX(B.EFFDT)
FROM PS_DEPT_TBL B
WHERE B.SETID=A.SETID
AND B.DEPTID = A.DEPTID
AND B.EFFDT<=%DateIn(:3))

That’s the problem.  The SQL only has 1 field in the select list (USE_ENCUMBRANCES), and the PeopleCode expects 3 for the 3 variables (&DeptUseEncumbrances, &ProcOption, &HP_AUTO_RTBC);

I did a little research, and as best I could tell, this PeopleCode is the only one that uses the SQL definition.

I couldn’t do a Find Definition References on the Application Package PeopleCode, but I could do a SQL query to determine which projects it was involved in:

SELECT * FROM PSPROJECTITEM
WHERE OBJECTVALUE1 = 'HP_RTBC'
AND OBJECTVALUE2 = 'RTBCMain';

These are some of the updates that I just installed, so I bet that was my problem.

So, I updated the HP_RTBC_INSTALL_DEPT_SEL SQL definition and added the two extra fields as best as I could figure.

SELECT USE_ENCUMBRANCES
 , HP_RTBC_OPTION
 , HP_AUTO_RTBC
 FROM PS_DEPT_TBL A
 WHERE A.SETID=:1
 AND A.DEPTID =:2
 AND A.EFFDT = (
 SELECT MAX(B.EFFDT)
 FROM PS_DEPT_TBL B
 WHERE B.SETID=A.SETID
 AND B.DEPTID = A.DEPTID
 AND B.EFFDT<=%DateIn(:3))