understanding scrolls and grids and how to traverse the data

Traversing Through Scroll Levels Using PeopleCode – © Copyright 2012

Understanding Grids and Scroll Areas and How to

Access and Traverse the Data within Them


Copyright © 2012

Printed in the United States of America.

All rights reserved. Except as permitted under the United States

Copyright Act of 1976, no part of this publication may be reproduced

or distributed in any form or by any means, or stored in a database

or retrieval system, without the prior written permission of the

publisher. The views expressed in this book are solely those of the

author and do not represent the views of any other party or parties.

PeopleSoft, PeopleTools, PeopleCode, PeopleBooks and nVision are

registered trademarks of PeopleSoft Inc. This book is an independent

creation of the author and has been published without license,

authorization or participation of ORACLE.

Information contained in this work had been obtained from sources

believed to be reliable. However, neither DMT SOLUTIONS, INC. nor

the author guarantee the accuracy or completeness of any information

published herein and shall not be responsible for any errors,

omissions, or damage arising from the use of this publication. This

work is published with the understanding that DMT Solutions, Inc.

and its authors are supplying information but are not attempting to

render engineering or other professional services. If such services

are required, the assistance of an appropriate professional should

be sought.

You are permitted to print one copy of this eBook for yourself. It

is illegal to distribute or copy this book in any part.


Table Of Contents

Introduction ........................................... 4

Understanding Scroll Levels and Grids .................. 5

How The Component Buffer Loads Data into Scrolls ....... 7

Accessing Scroll and Grid Data with PeopleCode ........ 10

Bonus Section ......................................... 19


Introduction

This short yet informative guide has been created to assist you with

the ability to learn how to access various levels of a scroll or

grid on a PeopleSoft component. I’m sure you have had many

instances where you needed to modify an existing page or component

or even create a new one that involved grids and multiple scroll

levels. It is very important to understand how to navigate through

the data in scroll areas, especially when there are multiple scroll

areas and grids on a page.

This guide is broken down into a few different areas. First, we

will discuss what grids and scroll levels are and how PeopleSoft

views them and accesses them. Secondly, we will walk through the

entire process of how you access each scroll level using PeopleCode,

step by step.

This will provide you with the specific code that you can use over

and over for any new component or page that you either build or

modify. So, with all of that said let’s get started with

understanding what scroll levels and grids are.


Understanding Scroll Levels and Grids

I would like to first, talk briefly about the various scroll levels

of a component. A scroll level is similar to the scroll areas or

grids on a page and the level where they reside. For the purpose of

this guide, I am assuming that you understand what a component and

page is and how it is created with the various records and fields in

Application Designer.

Think of a Scroll area and grid as a type of hierarchical container

of multiple fields that are organized. Mostly, all of the fields

within a scroll or a grid belong to one table. There may be other

fields from other records. However, they are usually there as

display only and are somehow related or are work records. But for

now, let’s just assume we’re talking about one record per scroll or

grid.

For scroll areas, the fields are not necessarily displayed in any

specific uniform fashion. In other words, they can be placed on the

page as individual fields in a free flowing manner. Grids, however,

are displayed in a uniform fashion. In fact, they are similar to a

spreadsheet where the fields are displayed in columns.

Scroll levels in the page definition allow PeopleSoft to maintain

common key information for child record definitions. You can

usually identify a scroll area by the plus and minus icons in the

upper right corner of the object.

When you see these, you are usually in a scroll area. The plus and

minus control buttons allow you to insert and delete rows within the

scroll area.


Every component has at least one level 0 record definition. There

can only be one row of data for each level 0 record definition

because scroll areas begin at scroll level 1.

However, what we want to focus on are the PeopleSoft pages and

components that include multiple scroll areas and grids. There can

be multiple scroll areas on a page at the same level. And there can

also be multiple scroll areas one within another. When you have

scroll areas within a scroll area, you are dealing with a parent

child relationship. It seems confusing at times, but the higher

scroll area on the page is really the lower level, starting with

level 1. Then, if there is another scroll area within that scroll,

it is known to be at level 2, and so on.

The fields controlled by one scroll area or grid are associated with

only one record. Here are some basic rules for scroll areas or

scroll levels. The keys of a scroll level 1 record definition must

include the same search keys as level 0, with at least one

additional key. This is simply the same rule or definition of a

child record.

PeopleSoft allows only one primary record definition per scroll or

grid. The purpose of a scroll is to control input into a single SQL

table. There can be more than one scroll on level 1. However, each

scroll has its own primary record definition. The rows of data

within one scroll are completely independent from the rows of data

within another scroll at level 1.

As we mentioned earlier, there can be additional scroll levels added

to a component on multiple levels. However, the maximum possible

value of a scroll level for a component is 3. Each Scroll Level is

subordinate to the one above it. The Scroll Levels are managed by

common keys between the tables using a parent/child relationship. In

other words, every page has a level 0. In addition, a page can have


a level 1, level 2, and level 3. However, that is as deep as it

goes.

These are known as:

Level 0

Level 1

Level 2

Level 3

This means there could be a level 1 on the page. The level 1 record

is subordinate to level 0, meaning that it is a child record of the

level 0 record. Furthermore, there could be 1 or more level 1

record on the component.

How The Component Buffer Loads Data into Scrolls When a page is loaded to the browser or into the PeopleSoft

component buffer, the component processor will work its way down

through each level. Once it loads the data from the first row of

level one, it looks to see if there is a level 2. If there is, it

will load the first row of the level 2 record. If there is not a

level 2, it will then load the remaining field rows at level one.

Once it loads the first row of level 2, it looks to see if there is

a level 3 row that is subordinate to itself. If there is it will

load the rows. So basically, if the component processor retrieves

all of the data that is subordinate to a specific-level, or when it

can’t find any more child record definitions, it will retrieve the

next row of data at the current level. Then, it will continue to

work its way down through the all of the levels on the page.

Picture a page that has 3 scroll areas associated with it. There is

a level 0, level 1, level 2 and a level 3.


In the following diagram, I have illustrated how the component

processor allocated the data that is stored in several different

tables. In this example there are 4 rows in the child table, 3 rows

in the grand-child table, and 1 row in the great grand-child record.

Notice, that I also labeled each row with a letter. I did this so

that I can list out the exact order that the component processor

will allocate and load the buffer. The order is as follows:

A,B,F,I,G,C,D,H,E. If you are interested in understanding the

component buffer and the component processor and how it works,

including knowing all of the PeopleCode events and the order in

which they are triggered, I suggest you download the guide

“Understanding the Component Processor and Component Buffer.”

This basically sums up how scroll areas, levels, and grids work

within a PeopleSoft page or component. Now, let’s dive into how to

access the data and navigate through each record and row at each

level on the page. This will involve object PeopleCode and the use

of Rowsets.

http://www.dmtsolutions.com/net/books/componentbuffer


I will break the next section down by the uses of rowsets and

navigating through scroll areas. For example, I will give you the

breakdown of code when you need to:

Access rows of data at level 0

Access rows of data that are not the main record

Fill rows of data into a scroll area

Access rows of data at multiple levels

Manipulate the data within scroll levels or grids


Accessing Scroll and Grid Data with PeopleCode

Before we begin the specific examples I mentioned above, I would

like to first explain the definition and uses of a RowSet.

A RowSet is a PeopleSoft object that can be instantiated into the

PeopleSoft component buffer. In addition, it is nothing more than a

collection of rows associated with the data that is in the buffer.

For example, the data within a specific scroll is a RowSet. In

addition, the data at level 0 can also be a RowSet, because by its

definition, it’s just a collection of Rows. In the case of the

level 0 scroll, it might be one row, or could be multiple rows if it

is an effective dated row.

When you are working with RowSets you basically have two types.

First, you have the type of RowSet that is populated with data from

the Component Buffer (or data on the page). Secondly, you have the

type of RowSet that can be a stand-alone RowSet. This type is

similar to an array. It’s basically an empty structure of a

collection of rows. Once you instantiate a stand-alone RowSet, you

can populate it with data using specific methods that I‘ll show you

in the upcoming chapters.

Instantiate a RowSet

The very first piece of PeopleCode that we must have is in relation

to the RowSet. We must first Instantiate a RowSet object before we

can do anything at all with the data in the scroll levels and grids.

When we speak of objects in PeopleCode we are really talking about

classes. RowSet is its own class that has its own functions,

methods, and properties. So once you instantiate the RowSet object

you can apply the functions, methods and properties to it. In other

words, once you create a space in the component buffer for the


RowSet you can play with it. For example, you can load data into

it, access specific rows, get the specific fields, and more

importantly, get to the next level or scroll area and fetch the data

there.

Declaring RowSet Variables

We must first declare our object to be what it is; a RowSet. When

you program using PeopleCode, you should always declare your objects

as Local so as to not cause problems when users navigate from

component to component. So with that said, let’s declare a RowSet

object.

Local Rowset &MyRowSet_0;




I have basically declared two variables named MyRowSet_0 and

MyRowSet_1 as an object type of Rowset, and so on. Now, my

intention is to use the first variable for accessing the Level 0

scroll and the second for accessing the Level 1 scroll. You should

probably use a more descriptive variable name in your actual

program. I recommend using the actual record that is associated

with the scroll or grid.

Instantiate Your RowSet Object

There are two different ways to Instantiate a RowSet. I mentioned

this earlier. The first way is to Instantiate it from data that is

in the component buffer. You do this by using the function

GetLevel0. This grabs the data at Level 0 and stores it in the

MyRowSet_0 RowSet object. So, if I was dealing with the JOB_DATA

page and I issued this code, I would have data from the PER_ORG_ASGN

Record. Believe it or not, the JOB record is actually at level 1.


You can always open a component in Application Designer and click on

the Structure tab to see the various levels.

Here is the code to instantiate the data from Level 0

&MyRowSet_0 = GetLevel0();

The other way to Instantiate a RowSet object is to create a stand-

alone RowSet that contains an empty structure of the Level 0 record.

You do this with the following code:

&MyRowSet_0 = CreateRowset(RECORD.PER_ORG_ASGN);

This created placed the structure of the PER_ORG_ASGN record into my

RowSet.


OK, now that we have instantiated our RowSet object, we can actually

get to the Level 1 RowSets and also get to the data at Level 0.

There is no other way to get to Level 1 without first getting the

RowSet for Level 0.

Access rows of data at level 0

Now that we have instantiated our object we can access any or all of

the rows of data at Level 0. We do this through the GetRow method.

However, since this is the default method for the RowSet we really

don’t need to call the method by name; it is implied.

Therefore, if we wanted to access the third row of the Level 0

scroll we can either issue this code:

&MyRow = &MyRowSet_0(3);

&MyRow = &MyRowSet_0.GetRow(3);

Both lines of code do the same thing.

Once you have the RowSet, you can invoke any of its methods. Here is

a link to the PeopleBooks for RowSet Methods.

Now, you can access the fields and data within your Level 0 RowSet

as such:

&MyEMPLID = &MyRowSet_0.GetRow(5).PER_ORG_ASGN.EMPLID.Value;

Access rows of data that are not the main record

Let’s assume that your scroll area or Grid has a main record of

PER_ORG_ASGN but there are also other records and fields in the grid

or Scroll. You still access them the same way as I indicated above.

However, you indicate the record and field not the main record. For

example, our JOB_DATA component has the PER_ORG_ASGN as the main

record at Level 0 but also has several other records like

DERIVED_NAME.

http://docs.oracle.com/cd/E26239_01/pt851h3/eng/psbooks/tpcr/book.htm?File=tpcr/htm/tpcr37.htm#H3002


Let’s assume we want to get the PERSON_NAME data from the Scroll

Level 0. Here is the code:

Local RowSet &MyRowSet_0;

&MyRowSet_0 = CreateRowset(RECORD.PER_ORG_ASGN);

&MyName = &MyRowSet_0.GetRow(3).DERIVED_NAME.PERSON_NAME.Value;

Fill rows of data into a scroll area

Regarding our stand-alone RowSet, you can fill the rows with data

using the following fill method:

&EMPLID = '100001';

&MyRowSet_0.Fill("where EMPLID = :1", &EMPLID);

The above code fills the RowSet with rows where the EMPLID is equal

to 100001. Once the RowSet is filled you can use other methods for

copying, comparing, or performing other manipulation of the data.

Access rows of data at multiple levels

Now that you have instantiated the RowSet and have the Rows for

Level 0, you can now traverse through the data to get to the other

scroll levels. For example, let’s say that I want to go through the

various levels of Tax Data on the JOB_DATA component. See the

Structure and Levels in the image below.


Basically, we have 4 Levels here and they are:

Level 0 – PER_ORG_ASGN

Level 1 - FED_TAX_DATA

Level 2 – STATE_TAX_DATA

Level 3 – LOCAL_TAX_DATA

If I want to get to the data from the LOCAL_TAX_DATA record on the

page, I will first need to have a Level 2 RowSet, which Means I need

a Level 1 RowSet, which means I need a Level 0 RowSet. Get the

point? So Let’s get started and get the LOCALITY Field Value from

the LOCAL_TAX_DATA scroll on the page.

Here are the steps and code to get there assuming we are going after

the first row of each level. I’ll show you how to loop through each

row later.






&MyRowSet_1 = GetLevel0()(1).GetRowset(Scroll.FED_TAX_DATA);

&MyRowSet_2 = &MyRowSet_1(1).GetRowset(Scroll.STATE_TAX_DATA);

&MyRowSet_3 = &MyRowSet_2(1).GetRowset(Scroll.LOCAL_TAX_DATA);

&MyLocality = &MyRowSet_3(1).LOCAL_TAX_DATA.LOCALITY.Value;

There you have it. Now you know how to get to each scroll area and

the data and fields in them. Of course, this is assuming you are

going after the first row in each. Next we’ll assume that we have a

grid or scroll area with multiple rows that we need to loop through.

Loop Through and Manipulate the data within scroll levels or grids

There are many occasions when there are multiple scroll levels each

having multiple rows of data. In some cases we need to touch every

row on every level. For example, let’s assume that I wanted to loop

through all of the rows at every level and update some flag or

enable or disable some property.

We walked through getting the first row of each scroll level in the

previous example. Now, we’ll loop through all of the rows at each

level.





&MyRowSet_1 = GetLevel0()(1).GetRowset(Scroll.FED_TAX_DATA);

For &a = 1 to &MyRowSet_1.ActiveRowCount

/* Get the RowSet for the Next Level */

&MyRowSet_2 = &MyRowSet_1(&a).GetRowset(Scroll.STATE_TAX_DATA);

For &b = 1 to &MyRowSet_2.ActiveRowCount

/* Get the RowSet for the Next Level */

&MyRowSet_3 = &MyRowSet_2(&b).GetRowset(Scroll.LOCAL_TAX_DATA);

For &c = 1 to &MyRowSet_3.ActiveRowCount

/* Get the value for each row at Level 3 */

&MyLocality = &MyRowSet_3(&c).LOCAL_TAX_DATA.LOCALITY.Value;

End-For;

End-For;

End-For;


If you notice from the above we have a loop within a loop within a

loop. Essentially I get the first Row from level 0 and then Get my

Level one RowSet. Then I loop through every row in Level 1. For

each Row in level 1 I get the RowSet for Level 2 and loop through

those rows and so on. Of course, you can manipulate the data,

retrieve the data, or change and set properties of the fields and

data at each level.

You can also insert and delete rows of data at any scroll level by

implementing the following methods.

&MyRowSet_2.DeleteRow(&b);

&MyRowSet_2.InsertRow(&b);

OK so we have Scrolled through, pulled data out inserted data in,

deleted and inserted data into a scroll on the page, what else might

we want to do?

Manipulating Data In A Grid

When you work with Grids, there are times when you need to wipe out

the data and fill the grid with other data that you want. This is a

common practice that a lot of developers use. Below is an example of

taking data within a grid, flushing it (or deleting it) and re-

filling it with new data. Again, we still need to instantiate the

RowSet before we can do anything at all with the data in the Grid.

Local Rowset &COMPETENCIES;

&MyRowSet_1 =GetLevel0()(1).GetRowset(Scroll.FED_TAX_DATA);

Once we have instantiated the object we can use the Flush method to

erase all of the data in the buffer and on the page, followed by

filling the data with other specific data we want.


&MyRowSet_1.Flush();

&MyRowSet_1.Select(Record.FED_TAX_DATA, "WHERE EMPLID = :1 AND EFFDT

= %DATEIN(:2) AND COMPANY = :3 AND STATE = :4", &EMPLID, &EFFDT,

&COMPANY, &STATE );

This flushes all of the data and re-fills the grid (in the component

buffer) with the data returned in my select statement. This never

updates the database unless you save the component.

One other important tip for working in Grids and Scroll areas is to

remember that inserting and deleting rows will shift data up on the

page. So it is best to always delete from the bottom up making your

for loop work backwards from the ActiveRowCount to the first row.


Bonus Section

Here is a simple example of an Application Engine program that loops

through a RowSet of employees and sends a batch email.

Introduction

Since the inception of PeopleTools version 8, many new features and

tools related enhancements have been introduced. One of the best

enhancements we have found is inside the Application Engine.

PeopleSoft has made the application engine very powerful toolset

with a wide range of capabilities.

Prior to version eight Many powerful application were written using

COBOL and SQR. However, PeopleSoft is beginning to convert most of

the COBOL and SQR programs into application engine programs.

Prior to version eight Peoplesoft’s application engine was not built

into the application designer toolset. It was very cumbersome and

difficult to use. Since adding the application engine into the

application designer, there are many more options and capabilities.

For example, you can now use PeopleCode objects and SQL objects

within your application engine program.

Designing Application Engine programs has become easier than ever,

and is a definite candidate to replace SQR programs. Our favorite

feature is the ability to include PeopleCode within your App engine

programs. With that said, I want to show you a quick and easy way to

send emails from within an Application Engine

program.

Example of Sending Batch Emails from Application Engine


If you want to efficiently write useful application engine code,

then you will find this code example, extremely useful. Did you know

you could easily send email messages from within your app engine

programs?

Well you can with just a few lines of code. You may find it useful

to send an email message to your PS administrator when a certain

process is completed. Or perhaps you want to send an email to all

employees indicating that their paychecks are available to be viewed

via the Employee Self Service ePay application.

Either way, you can easily create a simple application engine

program that will send your email message with these few lines of

code that I am about to show you. And, once you complete your

application, you can easily add the AE program to a PS job stream so

that it will execute after your Pay Confirm process.

You will first need to create a New Application Engine program. Here

is how to do this..

From the Application Designer choose

1) File New and select Application Engine.

2) You will see new definition created for you containing the MAIN

section with a step entitled STEP01.

3) Simply right click your mouse on the step (STEP01) and choose

Insert Action.

4) A new action will be inserted and will default to SQL.

5) Change this action to PeopleCode.

6) Save your program


7) Then double click on the PeopleCode action. Now you are ready to

insert the code below. I will fully explain this code.

REM **********************************************;

REM ** Instantiate the record and SQL objects to obtain data *;

REM **********************************************;

&MY_DATA_REC = CreateRecord(Record.EMPLOYEES);

&MY_DATA_SQL = CreateSQL("%selectall(:1) WHERE PAYGROUP = :2");

&MY_DATA_SQL.Execute(&MY_DATA_REC, "OUR_PAYGROUP");

REM **********************************************;

REM ** Loop through all of the data one row at a time for *;

REM ** all rows *;

REM **********************************************;

While &MY_DATA_SQL.Fetch(&MY_DATA_REC);

REM **********************************************;

REM ** Set all necessary email parameters

REM **********************************************;

&MAIL_FLAGS = 0;

&MAIL_TO = &MY_DATA_REC.EMAILID.Value;

&MAIL_CC = "";

&MAIL_BCC = "";

&MAIL_SUBJECT = "Pay check is Available Online for Viewing";

&NAME = &MY_DATA_REC.NAME.Value;

&MAIL_TEXT_BODY = "Dear " | &NAME | ", Your paycheck is now available for viewing

online.";

&MAIL_FILES = "";

&MAIL_TITLES = "";


REM **********************************************;

REM ** Send the email message for the current employee;

REM **********************************************;

&RET_CODE = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC,

&MAIL_SUBJECT, &MAIL_TEXT_BODY, &MAIL_FILES, &MAIL_TITLES);

REM ********************************************************;

REM ** Check the status code to ensure the mail was sent successfully;

REM ********************************************************;

If Not (&RET_CODE = 0) Then

WinMessage("Return status from mail = " | &RET_CODE);

End-If;

End-While;

REM ** Don�t forget to close the SQL stream **;

&MY_DATA_SQL.Close();

As you can see from the above code, it is extremely simple to loop

through data with your set criteria and send email messages from

within your application Engine program.

Basically, I have used PeopleCode to create a record MY_DATA_REC

from the EMPLOYEES table. After this, I created a SQL statement that

basically selects from the record PS_EMPLOYEES where the PAYGROUP is

some value.

Next, I use a while loop to fetch each row from the record, one by

one. As I grab the employee�s data I build my email message and use

the SendMail PeopleCode function.


Notice the Parameters that get passed to the SendMail command. You

can send file attachments by simply giving a file path/name to the

&MAIL_FILES variable.

This is a very basic example of how to send emails. Once, you

determine what you need to do, you can always, add more

functionality and get more creative with your code.

understanding scrolls and grids and how to traverse the data

Documents

understanding scroll

multiple scroll levels

peoplecode copyright

multiple scroll areas

various levels

understanding grids

existing page

peoplesoft component