PeopleSoft :: Run control Page for running an sqr

People soft Run control Page for running an sqr

There are two methods of running an SQR which are: 
1. Running through process scheduler: In this case SQR uses run control parameters which are passed from run control page and are stored in run control table. 
2. Running from command prompt: In this case SQR uses the run control parameters which are passed to command prompt and for this user is asked to enter parameters using INPUT command of SQR. This method is used when we are in need of running the program from outside the system.

By checking the value of $prcs_process_instance which a key field of run control table (PROCESS_INSTANCE) program comes to know whether SQR is being run from process scheduler (in the system) or from outside the system. Empty value of $prcs_process_instance specifies that SQR program is being run from command prompt (Outside the system).

if $prcs_process_instance = '' 
! Write INPUT commands and procedures to run from command prompt 
! using sqrw or other tools. 
else 
! Call procedure or write statements to read run control parameters 
! from run control table and process the SQR procedures. 
end-if

Now we have a question: Where does this $prcs_process_instance come from? 
For this we include prcsapi.sqc and call the procedures DEFINE-PRCS-VARS and Get-Run-Control-Parms which get this value from Process Scheduler when the program is being run from Process scheduler. 

Note that different procedures are used for this in different systems. For example, in HRMS system stdapi.sqc is included and STDAPI-INIT procedure is called for this. The procedure also sets the values of $prcs_oprid and $prcs_run_cntl_id. These values are used as the keys for getting run control parameters from run control table.

PeopleSoft Custom Run Control Pages

Every PeopleSoft developer will eventually need to create an SQR or App Engine process which can be executed from a Run Control page with custom parameters.  Here is a quick cheat sheet for creating the Run Control page, and how to retrieve the Run Control parameters from within an SQR or App Engine program.

Creating a Custom Run Control Page

This step is the same regardless of whether you will ultimately use the Run Control page with an SQR or App Engine program.  Do the following in Application Designer:

Creating the Run Control Record

1. Open Record definition PRCSRUNCNTL and make a copy by using "Save As" and giving the Record a new name (for example, "MY_PRCS_RC").
• When prompted to save a copy of the PeopleCode associated with PRCSRUNCNTL, choose "Yes".
2. Update the following Record PeopleCode definitions on your new Run Control Record, replacing references to PRCSRUNCNTL with your custom Run Control Record name:  a) OPRID.RowInit     b) RUN_CNTL_ID.SaveEdit
3. Add any desired additional fields to your custom Run Control Record (for example, "STRM").
4. Build the Record.

Creating the Run Control Page

1. Create a new Page definition and save it with a new name (for example, "MY_PRCS_RC").
2. Insert a Subpage onto your page, and choose "PRCSRUNCNTL_SBP".
• On the Insert Subpage dialog, change the "Subpage Record Name Substitution" value to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Drag your custom Run Control fields onto the Page (i.e., "STRM").
• Note:  You do not need to add the default Run Control fields onto the page (OPRID, RUN_CNTL_ID, etc).  These values will be populated automatically through PeopleCode

Run Control Record

Run Control Page

Creating the Run Control Component

1. Create a new Component definition.
2. Set the Component Search Record to your custom Run Control Record (i.e., "MY_PRCS_RC").
3. Save the Component with a new name (for example, "My PRCS_RC").
4. Register your Component using the Registration Wizard (Tools > Register Component).

Creating the Process Definition

In this step, we create a Process definition entry for our SQR or App Engine program, and associate the process definition with our custom Run Control component.

1. In the PIA, navigate to:  PeopleTools, Process Scheduler, Processes
2. Click the "Add a New Value" tab and enter the Process Type and Process Name.
• Note:  The Process Name must exactly match the name of your App Engine program, or in the case of an SQR, must exactly match the file name of your SQR file (without the ".sqr" extension suffix).
3. On the "Process Definition Options" tab:
1. Add your custom Run Control Component under the "Component" grid area on the bottom left of the page.
2. Add whichever Process Groups are appropriate to grant process security to the appropriate persons.

You should now be able to navigate to your Run Control page in the PIA, fill out the Run Control parameters, and schedule your process to run on the Process Scheduler.

Retrieving Run Control Parameters

Now that we have a way to provide Run Control parameters via a Run Control page, we need to be able retrieve and use those parameters from within a program.  This part of the process is different for App Engine and SQR programs.

Run Control Parameters in App Engine Programs

The standard way to store Run Control parameters in an App Engine program is to use a State Record.  To set up the State Record:

1. A requirement in naming State Records is that they must have "_AET" as their suffix.  Make a copy of your Run Control record, giving it a new name (for example, "MY_PRCS_AET").
1. When prompted to save a copy of the PeopleCode associated with the original Record, choose "No".
2. Change the Record Type from "SQL Table" to "Derived/Work".  A Derived/Work Record doesn't persist any data to the database.  Instead, it acts as an in-memory data structure while the program is running on the Process Scheduler.
3. Open your App Engine program, and navigate to: File, Definition Properties
4. On the "State Records" tab search for your State Record definition, and click the "Add" button to move the definition from the left-hand panel to the right-hand panel.

Now that the State Record is created and associated with the App Engine program, we need to populate it.

1. Add an SQL step as the first step in the App Engine program.  To populate the State Record with values from the Run Control Record, we use the Meta-SQL %Select function.  For example:
   
   %Select(OPRID, RUN_CNTL_ID, STRM)
   FROM PS_MY_PRCS_RC
   WHERE OPRID = %OperatorId
   AND RUN_CNTL_ID = %RunControl

Now that the Run Control parameters have been stored into the State Record, they can be referenced from PeopleCode via the State Record.  For example, the following would write the STRM Run Control parameter value to the message log:

MessageBox(0, "", 0 , 0, "STRM=" | MY_PRCS_AET.STRM);

Run Control Parameters in SQR Programs

In an SQR program, there are no State Records.  Typically, Run Control parameter values are simply stored using variables.  The following steps will allow you to retrieve the Run Control parameters in an SQR program.  Please refer to the SQR code sample following the step descriptions to see this in action.

1. Make sure you have added an include statement to include "stdapi.sqc" in your SQR program.  This delivered SQC provides routines which update the Process Scheduler with process status, as well as facilitating the retrieval of the Operator ID and Run Control ID values for the currently running SQR process instance.
2. Ensure that the very first step of your program is to run the "Stdapi-Init" procedure, and that the very last step of your program is to run the "Stdapi-Term" procedure.
3. Create a procedure to retrieve Run Control parameters, referencing the Operator ID and Run Control ID as "$prcs_oprid" and "$prcs_run_cntl_id" variables respectively.  The "Stdapi-Init" procedure initializes these variables.

The following code sample illustrates the steps above:

! Program Name:  MYPRCS.SQR

! Program Descr: My SQR Process

! Author:        Mark Stramaglia

#include 'stdapi.sqc'   ! Update status and get system parameters

BEGIN-PROGRAM

  DO Stdapi-Init

  DO Get-Run-Control-Parameters

  DO Main-Process

  DO Stdapi-Term

END-PROGRAM

BEGIN-PROCEDURE Get-Run-Control-Paramters

  BEGIN-SELECT

  RC.STRM

    LET $strm = &RC.STRM

  FROM PS_MY_PRCS_RC RC

  WHERE RC.OPRID = $prcs_oprid

  AND RC.RUN_CNTL_ID = $prcs_run_cntl_id

  END-SELECT

END-PROCEDURE Get-Run-Control-Parameters

BEGIN-PROCEDURE Main-Process

  SHOW 'STRM=' $strm

END-PROCEDURE Main-Process