I shouldn’t claim that this bug appears in every SQR program written for the Peoplesoft environment. It’s not even in every SQR program that takes run control parameters. But I bet it is in most of them.
The Peoplesoft Process Scheduler runs an SQR program according to the process definition of that program and the specifications of the“SQR Report” or “SQR Process” process type definitions. The Process Scheduler puts the OPRID and RUN_CNTL_ID on the command line of pssqr.exe and the STDAPI-INIT procedure inputs those values to SQR variables.
Our programs use the $prcs_oprid and $prcs_run_cntl_id values to select a row from the run control table. We usually assume that the run control row had FieldEdit or SaveEdit Peoplecode that validated the values we use. We (almost) always assume that there is a run control row; that the PIA (Peoplesoft Internet Architecture) page processor will automatically save a row when the user clicks the run button.
Unfortunately, there is a loophole through which a user can run a process – SQR, Application Engine, COBOL, etc. – without saving a run control row. We need to test for the absence of data because, in that case, SQR will proceed with undefined values in the column fields. Here is the loophole.
When the user searches for a RUN_CNTL_ID, Peoplesoft is checking the parent record, usually PSPRCSRUNCNTL or PS_PRCSRUNCNTL, not our program-specific data record. If there is no row in our run control table, the run control page will display with blank fields. Suppose the user ignores the blank fields and clicks the run button. The new, blank row has not changed, so it won’t be saved. There will be no row, not even a row with blank fields, for that OPRID and RUN_CNTL_ID.
It doesn’t help to have validation in FieldEdit or SaveEdit. If the row doesn’t change, those Peoplecode programs won’t run. The only reliable solution is to check in the SQR program whether the run control table selection returns a row.
We have made use of this PeopleSoft SQR quirk for as long as I have been coding SQRs.
We always put some code like this in the initialization routine:
if $prcs_process_instance = ”
do
else
do
end-if
This way we can test a program under development using SQRW and then develop the run control tables and page after the SQR has been unit tested.
That is an excellent idea. I’m not sure whether you’re responding to this particular post or just sharing some good advice. That practice isn’t enough to save you from the bug I discussed here – a missing run control row. The IF test detects whether there was a process instance number on the command line, which usually indicates whether the SQR program was run from the Peoplesoft Process Scheduler. (Of course, we can add a fake process instance number to the SQRW command line to test that code.)
We have programs that assumed the Run Control row was present and not the PeopleCode validating the data. So, the SQR would validate the parameters and abort with message in the log as needed.
I just fixed one where the Process Definition was a job containing an App Engine and an SQR using two different Run Control Tables. To work, both needed the same Run Control ID. User did not set up that way. SQR got no parameters because process instance was interogated as Stan The Man said.
The best cure I have is listed below.
Move ‘N’ To $RowInTbl
Begin-Select LOOPS = 1
param.BEGIN_TERM
param.END_TERM
Move ‘Y’ To $RowInTbl
FROM ps_fciap_rnctl param
WHERE param.OPRID = $prcs_oprid
AND param.RUN_CNTL_ID = $prcs_run_cntl_id
End-Select
!
If $RowInTbl = ‘N’
Show ‘This program must be run from the Process Monitor.’
Stop Quiet
End-If
(Any other validations go here)
(Continue your job)
Enjoy!
Thanks – that’s good code for us to emulate.
Now, the Do When action is like an If statement. Basically, if the select statement returns a row, that’s true. If no rows result, it’s false. So, if the Do When select statement returns a row or more, it executes the action. If no rows are returned, it moves on to the next Step without running any more actions in the current step.