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.

[sourcecode]
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)
[/sourcecode]

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

[sourcecode]
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);
[/sourcecode]

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

[sourcecode language=”sql”]
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))
[/sourcecode]

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:

[sourcecode language=”sql”]
SELECT * FROM PSPROJECTITEM
WHERE OBJECTVALUE1 = ‘HP_RTBC’
AND OBJECTVALUE2 = ‘RTBCMain’;
[/sourcecode]

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.

[sourcecode]
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))
[/sourcecode]

And, that fixed my problem!

Leave a Comment

Your email address will not be published. Required fields are marked *