PeopleTools | Using Population Select on a run control page.
A. Run Control Parameters – Then and Now
Traditionally, a PeopleSoft run control page would be set up with a variety of input parameters, such as ‘Business Unit’, ‘Employee ID’ or ‘Transaction Date’. This gives the user the ability to specify exactly which data they would like to run the process for. Although the parameters help to keep things simple, users are obviously restricted in the way they can select the data. Furthermore, most run controls only allow one value to be entered per parameter field. There is no option to select multiple ‘sets’ of data in one go.
This is where Population Selection enters the picture. Users can now create their own queries and select whatever data they want. The sky is really the limit when it comes to using Pop Select. The only proviso is that the query needs to have some link to the process in question. If, for instance, your process selects customers with outstanding balances, it would make no sense to use a query that selects the balances of general ledger accounts. Fortunately, the Pop Select structure provides a mechanism – the ‘bind record’ – to ensure that only relevant queries are accessible to the Pop Select mechanism.
B. The Eleven Steps of Pop Select
The easiest way to explain how Pop Select works is go through setting up an example step-by-step. Pop Select actually gives you three options to select data: Equation Editor, External File, and PS Query. For the purposes of this example, we will stick to PS Query being accessed via an App Engine process.
This example assumes that you have already created a run control record, page, App Engine process, and process definition. Now you are at the point where you’d like to add the Pop Select functionality to that process.
(1) Pop Select Subrecord
Start by adding the Pop Select Subrecord ‘SCCPS_RCNTL_SBR’ into your run control record:
For the sake of convenience, you can also add the same sub-record to the State record (AET) of your App Engine process.
(2) Pop Select Page
Now add the ‘SCCPS_RUNCNTL_SBP’ to the run control page:
Double-click on the sub-page and set the ‘Subpage Record Name Substitution’ field equal to your own run control record (which now contains the Pop Select fields from the sub-record).
(3) Create PeopleCode for Pop Select Functionality
The run control record created in Step 1 will need PeopleCode added to it in order to activate all the Pop Select functionality. The simplest way to do this is copy the PeopleCode that already exists on a delivered component. For reasons of copyright, I can’t include this PeopleCode here, but fortunately there is an easy way to get hold of it.
Open up the ‘SCCPS_RCNTL_SBR’ sub-record mentioned in Step 1 above and perform a ‘Find Definition References’ in App Designer. Select any of the delivered records, and then continue doing ‘Find Definition References’ until you locate the component. Against the component you should find Pop Select PeopleCode at the following levels: Component Record PeopleCode (RowInit, SaveEdit, SavePreChange) and Component Record Field PeopleCode (five fields in total: SCCPS_CNTXT_ID, SCCPS_FILE_PATH, SCCPS_POP_SEL, SCCPS_QUERY_NAME, and SCCPS_TOOL_ID). All this PeopleCode needs to be copied to your own run control component.
(4) Create Bind Record
This requires you to decide how you wish to select the data for the process. In many cases, this is usually the key fields of the underlying primary database record. If, for instance, you are designing an employee leave report, the bind field would be EMPLID. If it’s a summary of customer sales on any one day, the bind fields would be CUST_ID and TRANS_DT. Your bind record therefore contains all your key fields in the form of an SQL View.
The text of the SQL view must be written to select from the ‘real’ database record. Fortunately, this view text can be kept fairly minimal (even a simple ‘select emplid from ps_person’ might be enough in some cases). Since PS Query will perform the main job of filtering the data, the SQL View text for the bind record can be kept as basic as possible.
The PeopleSoft convention is for the bind record to be given a name ending in ‘BND’.
(5) Create Target Record
Once the bind record has done the job and selected the data you’re after, you must now decide where you want to temporarily store the selected data. The ‘target record’ takes the form of a normal ‘SQL table’, as it’s highly likely the PS Query will select multiple rows of data, plus you will probably want to save the data for subsequent processing. The target record normally matches the structure of the bind record, with the addition of ‘PROCESS_INSTANCE’ as a primary key. Since the data in the target record can build up over time, it’s best to perform a delete from this table once you’re done with the data.
Again, the PeopleSoft convention is for the target record to end with a name of ‘TGT’.
(6) Create Context Definition
The next step is to link the bind record and target record to the component containing the Pop Select functionality. This is done by creating a ‘Context Definition’. Navigate to the following page in the system (this example is from Campus Solutions, your navigation path may differ slightly):
Set Up SACR -> System Administration -> Utilities -> Population Selection -> Context Definition
Select ‘Add a New Value’ and then fill in the fields on the two pages presented.
Firstly, fill in the following details on the ‘Context Definition’ tab:
• Context Name: a description of your process
• Process Type: Application Engine
• Process Name
• Menu / Component of the run control page
• Applicable Selection Tools – in this case, we are using PS Query only, but you can set up additional rows for ‘External File’ and ‘Equation Editor’
Then, on the ‘Selection Mapping’ page, enter the following details:
• Results Record – the name of your TGT target record
• Required Fields Record – the name of your BND record
• Data Source Records – again, the name of your BND record
• Required Fields Mapping – this should automatically default to the correct values. The only value you might need to set is for PROCESS_INSTANCE. Set the ‘Mapping Action’ field to ‘Process Instance’.
(7) Set up Query Security
Query Security must be configured to allow the bind record to appear in the ‘Records’ tab of the PS Query definition. This is done by navigating to the following page in the system:
PeopleTools -> Security -> Query Security -> Query Access Manager
Choose any of your custom security trees (or create a new tree if no custom tree currently exists). Then, insert your bind record under any one of the nodes:
Save the Query Tree.
(8) Check for Pop Select Flag in App Engine
In your App Engine, you will now need a mechanism to call the Pop Select code if the user has ticked field SCCPS_POP_SEL on the run control record (this field should have been inserted by the Pop Select sub-record). One option might be to do a dynamic Call Section, calling Section ‘POP_SEL’ for those cases when the flag field is equal to ‘Y’; or calling Section ‘MAN_SEL’ when the flag field is ‘N’.
If RUN_CNTL_AET.SCCPS_POP_SEL.Value = "Y" Then /* Pop Select */ XX_TEST_AE.AE_SECTION.Value = "POP_SEL"; Else /* Manual Select */ XX_TEST_AE.AE_SECTION.Value = "MAN_SEL"; End-If;
Refer to Tip 027 for performing a dynamic Call Section in an App Engine programme.
(9) Write Code to Populate the Target Table
Once in your Pop Select section of the App Engine, include the following code in a PeopleCode Step and Action. Replace ‘XX_TEST_AET’ with the name of your App Engine state record:
import SCC_POP_SELECT:MODEL:PopSelectFacade; Local SCC_POP_SELECT:MODEL:PopSelectFacade &PopSelectFacade; /* Create an instance of the Pop Select Facade Class */ &PopSelectFacade = create SCC_POP_SELECT:MODEL:PopSelectFacade( XX_TEST_AET.SCCPS_TOOL_ID, XX_TEST_AET.SCCPS_CNTXT_ID, XX_TEST_AET.OPRID, XX_TEST_AET.RUN_CNTL_ID, XX_TEST_AET.PROCESS_INSTANCE); /* Set the Query Name */ &PopSelectFacade.QueryName = XX_TEST_AET.SCCPS_QUERY_NAME; /* Select the parameter data */ &PopSelectFacade.GetParmData(); /* Run Pop Select to the target record */ &PopSelectFacade.ExecuteToRecord();
The result of running this code is that your TGT record will now be populated with the matching data. The Process Instance field in the TGT record will also be set to the current Process Instance. You can now access and process the results data in whatever way you wish, for instance, by performing a ‘DoSelect’ and looping through each of the records.
At this point, the developer has completed their part of the Pop Select process. However, the end user must now do a couple more things to ensure Pop Select works as designed…
(10) Write the Pop Select Query
The user must create a PS Query to select the required data. This is pretty much the same as writing any query, with one important difference. The query definition must be associated with the Pop Select for the specific component. This is done by inserting the bind record into the Query definition. For ease of reference, it helps to insert the bind record first and then select all fields of the bind record so that they appear in the ‘Fields’ tab. With the bind record set up, the user can join any other tables they wish in order to filter the data.
As a result, the ‘Query’ tab should appear something like this:
The user can also insert their own parameters into the PS Query via the use of ‘Prompts’. When the App Engine process is scheduled, the specific parameter values will need to be entered into the run control page.
(11) Run the Process
With the PS Query written and saved, the user can now go into the run control page and tick the ‘Population Selection’ check-box. Set the Selection Tool to ‘PS Query’ and enter the name of the query. The selection prompt is limited to queries that contain the bind record linked to the current component. As a result, you do not see every PS Query in the system.
At the point, the user can also click ‘Preview Selection Results’ to see what records will be selected by the query. If the results are not quite right, the user can click the ‘Launch Query Manager’ link to amend the query.
With the run control data has been saved, the user can go ahead and run the process. Success, hopefully.