How To Read Peoplesoft Run Control Parameters In SQR

Your rating: None Average: 4.5 (8 votes)

Peoplesoft allows the user to give direction to an SQR program by entering parameters on a webpage before the Process Scheduler runs PSSQR. Here is a new way to get those parameters to the SQR program
SQR Command Line Parameters
PSSQR is the SQR compiler bundled with Peoplesoft. It takes many command line parameters that control its behavior. It also passes along any number of strings to the SQR program that it compiles and executes. The ask command can read a string into a substitution variable (e.g. {database_name}) or the input command can read it into a regular variable (e.g. $database_name). Note that all ask commands will be executed during compilation, before any input commands are executed.

The Peoplesoft Process Scheduler runs PSSQR with a command line specified in the Process Type definition and the Process definition. Peoplesoft comes with a process type for “SQR Reports,” which includes four pass-along parameters:
1. Database name
2. Process Instance (the PRCSINSTANCE key field from the PSPRCSRQST table)
3. Operator ID (the OPRID key field from the run control table)
4. Run Control ID (the RUN_CNTL_ID key field from the run control table)

If the SQR includes the “stdapi.sqc” file and calls the “stdapi-init” procedure, those parameters will be input into the following variables: $database_name, $prcs_process_instance, $prcs_oprid, and $prcs_run_cntl_id.
Note: this interface has some redundancy because the database name, operator ID, and run control ID are all present in the PSPRCSRQST table, which needs only the PRCSINSTANCE key to access.

If PSSQR runs outside the Peoplesoft Process Scheduler, its command line shouldn’t have a process instance, operator ID, or run control ID. We need a series of input commands to get the parameters we need to run the SQR program. Most programmers call the “stdapi-init” procedure and then test the value of $prcs_process_instance.
If it is null, we assume the SQR ran outside the Process Scheduler and we have to use input commands for our parameters (its too late for ask commands). If $prcs_process_instance has a value, we assume the SQR ran from Peoplesoft, and our parameters are in a run control table.

After returning from the “stdapi-init” procedure, the SQR program would test $prcs_process_instance, and call either the “ask-values” procedure to perform input commands or the “get-values” procedure to read a run control row.

The “ask-values” procedure would call a separate procedure for each parameter. Those procedures were in SQC include files. The procedures input a value and did some validation and editting.

The “get-values” procedure would call a procedure, in an SQC include file, to read every field of a run control row. Then the “get-values” procedure would call procedures, in a related SQC include file, to validate and edit the fields that the SQR program would use.

run.JPG27.06 KB
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!


Have a question? Please ask it on the forum instead.
Lepa's picture
User offline. Last seen 2 years 20 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: How To Read Peoplesoft Run Control Parameters In SQR

Thanks for sharing that with us harsha, really appreciate it.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

calibra's picture
User offline. Last seen 10 years 15 weeks ago. Offline
Joined: 10/26/2010
Posts: 1
Re: How To Read Peoplesoft Run Control Parameters In SQR

PSSQR is the SQR compiler bundled with Peoplesoft....!!