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.
(Side 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.)
Once we have the operator ID and the run control ID, we can read the run control table to retrieve any other parameters we need to run the SQR.
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.
How Peoplesoft Inc. Did It
Peoplesoft Inc. wrote hundreds of SQR programs. I don’t think Oracle Corporation wrote as many since acquiring Peoplesoft in December 2004.
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. It’s hard to generalize across hundreds of programs and dozens of modules, but I’ll give my impression.
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.
This was an admirable approach to encapsulation and code reuse, but there were a few things about it I disliked.
- The database column names and variable names were not visible in the main program. They were used later without their origin being clear. I could tell from the procedure names that we were working with an as-of date, but was it $asofdate, $as-of-dt, $asof_date, or something else.
- The editting and validation supplied with the include files was probably optimal, but it meant I couldn’t tell exactly what the user supplied, and it might not be the editting and validation I needed.
- Despite having reusable code tucked away in include files, we seemed to be bogged down with the mechanics of choosing database versus keyboard and choosing two specific procedures for each parameter from two sets of procedures.
- Developing new programs seemed clumsy. Instead of diving straight into the heart of my customer’s requirements, I was bouncing between my text editor and the Application Designer. I had to find or create the run control record and find or create the run control page, while finding or coding the procedures to read the run control table. And if the customer added a requirement that needed a new parameter, I had to cycle back to the two window (App Designer and text editor) process.
How I Did It
I had several goals.
- Streamline the process of getting the parameters and make those parameters visible in the program.
- Validate the common inputs (process instance, operator ID, run control ID). Don’t let the program run if the run control process doesn’t work.
- Eliminate repetitive coding; writing a new, slightly different library of procedures for each run control table.
- Minimize the overt coupling between the SQR program and the PeopleTools objects.
- Report the results in the SQR log file.
Here’s the code. There is additional explanation in the comments. Note that it calls procedure parse_filename() that I published on January 11, 2009 (How To Parse A Filename in SQR).
!***************************************************************************
! This procedure obtains a value for one parameter from the run control
! table or an input statement, as appropriate. Call it as follows:
!
! do get_parameter('RUN_ID', 'Enter the pay run id: ', $run_id)
!
! OR
!
! do get_parameter('to_char(PAGE_NUM)', 'Enter page number: ', $page_num)
! move $page_num to #page_num
!
! The calling program must meet the following requirements:
! 1. Call procedure "stdapi-init" before calling this procedure.
! 2. Do not set a variable named "$run_control_table".
!***************************************************************************
begin-procedure get_parameter($columnname, $input_prompt, :$value)
evaluate ''
when = $_prcs_process_instance
! When global variable $prcs_process_instance is null, the SQR is executing
! from the command line, not from the Peoplesoft Process Scheduler. We
! should use the input command to get the value.
show $input_prompt noline
input $value noprompt
break
when = $_prcs_oprid
! $prcs_process_instance was not null, so the SQR is executing from the
! Peoplesoft Process Scheduler. Check for null value of global variable
! $prcs_oprid.
show 'OPRID is null'
stop
when = $_prcs_run_cntl_id
! Check for null variable for the global variable $prcs_run_cntl_id.
show 'RUN_CNTL_ID is null'
stop
when = $_run_control_table
! The first time this procedure (get_parameter) runs, the global variable
! $run_control_table will be null. In that case, determine the name of the
! run control table. First, get the name of the SQR program that called
! this procedure by parsing the global variable $sqr-program.
do parse_filename($_sqr-program, $path, $prcsname, $ext)
uppercase $prcsname
! Now search three tables. First, PS_PRCSDEFNPNL is a child table of the
! process definition table. Look for the panel group / component named
! for this process. Second, PSPNLGROUP is the table of panels / pages
! that belong to a particular panel group / component. Third, PSPNLFIELD
! is a list of fields on a panel. Look for a record (RECNAME) that
! appears on one of the panels / pages and contains the field OPRID.
begin-select distinct
A03.RECNAME
let $_run_control_table = 'PS_' || &A03.RECNAME
from PS_PRCSDEFNPNL A01, PSPNLGROUP A02, PSPNLFIELD A03
where A01.PRCSTYPE = 'SQR Report'
and A01.PRCSNAME = $prcsname
and A02.PNLGRPNAME = A01.PNLGRPNAME
and A03.PNLNAME = A02.PNLNAME
and A03.RECNAME <> ' '
and A03.FIELDNAME <> ' '
and exists
(select 'x'
from PSRECFIELD
where RECNAME = A03.RECNAME
and FIELDNAME = 'OPRID'
and FIELDNUM = 1)
end-select
show 'Run control table: ' $_run_control_table
show 'Operator ID: ' $_prcs_oprid
show 'Run control ID: ' $_prcs_run_cntl_id
show 'Process instance: ' $_prcs_process_instance
when = $_run_control_table
! Make sure the prior process succeeded.
show 'Cannot determine run control table'
stop
when <> $columnname
! Now that we have the table name, if the $columnname is not null, we
! try to select the proper column from the proper row of the proper
! table.
move {true} to #not_found
move '' to $value
begin-select
[$columnname] &VALUE=char
move {false} to #not_found
move &VALUE to $value
from [$_run_control_table] A04
where A04.OPRID = $_prcs_oprid
and A04.RUN_CNTL_ID = $_prcs_run_cntl_id
end-select
if #not_found
show 'Cannot find ' $_run_control_table ' row for OPRID = "'
$_prcs_oprid '" and RUN_CNTL_ID = "' $_prcs_run_cntl_id '"'
stop
end-if
show 'Run control parameter ' $columnname ' = ' $value
end-evaluate
end-procedure get_parameter
Looking Ahead
There are 31,200 results on Google for “poor workman blames his tools.” I don’t know the original source of that aphorism, but it was probably someone who sold tools – bad tools. When I first started programming, I was absolutely sure that my compiler was buggy and my code was flawless. It wasn’t until twenty years later that I actually found a compiler bug, confirmed by the compiler’s maker.
Yet, when I switched from programming in C to programming in SQR, suddenly I stopped writing null-pointer bugs … and started to forget to increment my loop counters. Different languages do pose different challenges. Next week I’ll risk being labelled a “poor workman,” and discuss the easiest bugs to write in SQR.
Recent Comments