The Bug in Every Peoplesoft SQR Program

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.