How to use Call Section

There are multiple scenarios when we need to call section in an Application Engine. There are two ways to call section:

1. By using Call Section Action
2. Dynamic Call Section

Now discuss both in detail

1. By using Call Section Action

This is very simple and commonly used way to call section. In this we have to select only Section Name and Program ID

Custom Run Control Page

Sometime we have requirement to run specific Application Engine or SQR through Run Control page with custom parameters. It's very easy to create custom run control page juts follow the below mentioned steps:

Step 1: Create Run Control Record

Open delivered record definition "PRCSRUNCNTL" and create a copy of this record. While saving the record, it will ask for to save PeopleCode also click on YES. Now we have to replace the PeopleCode record definition name references as it contains references of "PRCSRUNCNTL" record to our new custom record definition i.e.. "DM_RUN_CNTL"

As we can see, PeopleCode is written on:

• OPRID.RowInit
• RUN_CNTL_ID.SaveEdit
• LANGUAGE_CD.RowInit
• LANGUAGE_OPTION.FieldChange

After changing, build the record.

Step 2: Create Run Control Page

First, create new page and insert subpage "PRCSRUNCNTL_SBP". After insert, double click on subpage and its properties will open. We have to change the Subpage Record Name Definition value to our new custom run control record.

Now save the page. There is no need to add the default Run Control fields onto the page (OPRID, RUN_CNTL_ID, etc..).  These values will be populated automatically through PeopleCode. 

Step 3: Create Run Control Component

• Create a new Component definition.
• Set the Component Search Record to your custom Run Control Record.
• Save the Component with a new name.
• Register your Component using the Registration Wizard.

That's it our new custom run control page is ready.

Optional Prompt in PS Query

Sometimes we need to create a PS Query with optional prompt i.e. if there are 3 prompt in  PS Query then if user enter a value, the query fetches relevant rows otherwise it fetches all the rows. 

For example we created a PS Query which have 3 prompt as shown in below screen

Now if we only pass birthdate then we will get this result

Now there is way to bypass this error and fetch the data on the basis of birthdate. To achieve this we need to add expression in our PS Query. If criteria is character type then we have to add expression ' ' like this

*Please make sure, there should be space between single quotes (' '). 

 If criteria is number type then we have to add expression with 0 like this

After adding expression, final step is we need to group criteria and expression like this

Make sure, while grouping the criteria the logical action should be OR not AND. Now, check whether we get data after passing the same birthdate or not

We get the data only after passing birthdate. In short for optional prompt first we need to set expression and then grouping of that criteria.

PeopleCode Events

Below is the list of PeopleCode Events in the order of their execution:

1.  SearchInit  

2.  SearchSave

3.  RowSelect

4.  PreBuild

5.  FieldDefault

6.  FieldFormula

7.  RowInit

8.  PostBuild

9.  Activate

10. FieldEdit

11. FieldChange     

12. RowInsert

13. RowDelete

14. SaveEdit

15. SavePreChange.

16. WorkFlow

17. SavePostChange

Above mention events can be written on the following PeopleCode event locations:

1.  Page Event

2.  Menu Event

3.  Record Event

4.  Component Event

5. Component Record Level

6. Component Record Field Level

Auto increment of sequence number

Sometime in grid or scroll, we need to write peoplecode on a field which stores unique number every time when we click on add new row button. Below code can be used to achieve this functionality:

Local Rowset &rowset;

&rowset = GetLevel0()(1).GetRowset(Scroll.TEST);

&count = 0;

For &i = 1 To &rowset.ActiveRowCount
   If &count < &rowset(&i).TEST.SEQNBR.Value Then
      &count = &rowset(&i).TEST.SEQNBR.Value;
   End-If;
End-For;

&rowset(CurrentRowNumber()).TEST.SEQNBR.Value = &count + 1;


TEST is name of grid or scroll. Auto increment number will start from 0 if you want that it will start from 1 or any other number, assigned that value &count variable after declaring rowset.

Field level Audit

Field level audit track changes to one or more fields on a PeopleSoft record and stores the results in the delivered PSAUDIT table. You can enable as many as fields for auditing in a record. Field level audit only applicable to data updated through online and not directly through the database. So auditing will not apply to data updated by SQRs and SQL scripts.

How to enable Field Level Audit

To enable field level auditing, open the record containing the chosen field and go to the Record field properties of the field(s). In the Audit group box, Field Add, Field Delete, Field Change are the available options. Check the boxes to choose appropriate level of audit.

Type of Field Level Audit

Field Add option will track whenever a value is added to that selected field. 

Field Change option will create an entry whenever you change the value of the field.

Field Delete option, a new row is created in the audit record whenever the field value is deleted.

The record PSAUDIT stores the new and old value of the field along with the audit action. It also stores the record name and key fields of of row in the PeopleSoft record that was modified so that you can track back to it. These are the values that appear in the fields KEY1, KEY2, KEY3….

AUDIT_ACTN field stores what was the change made to the field. The values of this field can be interpreted as follows.

A – Added new value or row

C – Changed the existing value or row

D – Deleted the old value or row

K – Row updated, Old Value

N –Row Updated, New Value

O – Original Value

Query to check Field Level Audit

select * from psaudit where recname = '< Record Name>' and fieldname = '< Field Name>' and key1 = '<First Key Value>'

You can add KEY1, KEY2 on the basis of keys in your base record i.e.. the record on which field level audit is applied.

List of Common PeopleSoft Tables

Below are the commonly used PeopleSoft tables which i used in my daily work. Please comment if any important table is missed.

Pages

* PSPNLDEFN — Page header table

* PSPNLFIELD — Page controls (field types/FIELDTYPE)

* PSPNLHTMLAREA — Static HTML Areas on Pages

Fields

*PSDBFIELD — Fields in the system

* PSXLATITEM — Translate Values

Records

* PSRECDEFN — Record header table

* PSRECFIELD — Fields in the record (subrecords not expanded)

* PSRECFIELDALL — Fields in the record (subrecords expanded)

* PSKEYDEFN — Indexes

* PSTBLSPCCAT — Tablespaces

* PSRECTBLSPC — Records’ tablespace assignments

Components

* PSPNLGRPDEFN — Component header table

* PSPNLGROUP — Pages in the components

Projects

* PSPROJECTDEFN — Project header table

* PSPROJECTITEM — Definitions in the project

Component Interface

* PSBCDEFN — header record; one row for each component interface

* PSBCITEM — one row for each property

Menus

* PSMENUDEFN — Menu header table

* PSMENUITEM — Items (components) on the menu

Process Scheduler

* PS_PRCSDEFN — Process Definition Header

* PS_PRCSDEFNGRP — Process Group

* PS_PRCSDEFNPNL — Component

* PS_PRCSJOBDEFN — Job Header

* PSPRCSRQST — Process Request Instances

Security

* PSCLASSDEFN — Permission List header table

* PSAUTHITEM — Menu items granted security by permission lists

* PSROLEDEFN — Role header table

* PSROLECLASS — Permission Lists in roles

* PSOPRDEFN — User ID header table

* PSROLEUSER — Roles granted to users

Portal

* PSPRSMDEFN — Content References and Folders

* PSPRUHTABPGLT — Portal User HP Tab Pagelet

Change Control

* PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description

* PSCHGCTLLOCK — shows definitions that are currently locked

Application Engine

* PSAEAPPLDEFN — header record; 1 row per app engine

* PSAEAPPLSTATE — state records assigned to app engines

* PSAEAPPLTEMPTBL — temp tables assigned to app engines

* PSAESECTDEFN — sections

* PSAESTEPDEFN — steps

* PSAESTEPMSGDEFN

* PSAESTMTDEFN — actions (action types)

HTML Definitions

* PSCONTDEFN — header record; last update time, etc.

* PSCONTENT — stores actual text in the HTML definition

SQL Definitions

* PSSQLDEFN — header record; last update time, etc.

* PSSQLTEXTDEFN — stores actual text in the SQL definition

File Layout Definitions

* PSFLDDEFN — header record; last update time, etc.

* PSFLDSEGDEFN — stores the segments for each layout

* PSFLDFIELDDEFN — stores the fields for each layout