TREE generation using PeopleCode

Creating the tree for security related purpose; you might have came across the same, in the course of development.

We create it under: Menu > Tree manager > Tree manager

Note: For getting an overview of tree manager: PeopleBooks > PeopleTools 8.51 PeopleBooks: PeopleSoft Tree Manager > Introduction to PeopleSoft Tree Manager

Note: Download page and record design document to see the corresponding structures

Note: Download peoplecode and SQL for the tree generation

Opening any tree you can see the nodes. Parent node, child nodes, sibling nodes and whole other tree properties.

Every time there happens a department structure change, when we take the example of department tree as an example, when the numbers of employees are more in the organization, it is pretty much tough affair to structure all the employees under the new management structure. What if we have a code to fetch the employees reporting to a manager/supervisor of a department and assign them through peoplecode?

Below is the code to create a tree using peoplecode. This one is tested and verified code. If you have any doubts related to the same, you can drop a e-mail.

Note: Keep the Tree API open before you go through the code below: PeopleBooks > PeopleTools 8.51 PeopleBook: PeopleCode API Reference > Tree Classes

Note: Keep this tip in the mind. The parent node is similar to the level0 fields, the child rows are similar to level1, level2 fields. We can have only upto 3 levels for a component, but can have n number of levels in case of tree. I have used upto 12 levels in this code.

Consider the tree creation using peoplecode as one part. The tree will be created in the database. How to display this tree in a custom page, where when a person enters, consider a supervisor comes to this page, he should be able to view the details of his subordinates only. This cannot be achieved using row level security. Can you tell the reason? (Just think about it.)

The code given below meets the above two requirements.

The application engine peoplecode is to create the tree structure using the tree api objects.
The component level peoplecode is to render the tree structure, based on the employee who logs in to the custom page. When a supervisor logs in, the tree structure data starting from his position is fetched from the database and displayed. Hence the supervisor becomes the parent node and all the subordinates are the child rows.

You can see one HTML area (refer to the record AS_DERIVED_HR in the design doc). That while embedded in the custom page, renders the tree structure as we can see it in the tree manager page. TREECLTEVENT (refer to the record AS_DERIVED_HR in the design doc), is like the push button which get the control from the HTML area while expanding/collapsing the area.

Go through the code. Feel free to comment to this post, in case of any doubts. It will be answered.

See the peoplecode and SQL below:

Appendix 1: PeopleCode
Field: OPRID
Event: ROWINIT
AS_SUPER_RUN.OPRID = %OperatorId

Field: RUN_CNTL_ID
Event: SAVEEDIT
&POS = Find(" ", AS_SUPER_RUN.RUN_CNTL_ID);
If &POS > 0 Then
Error MsgGet(65, 33, "Run Control Ids may not contain embedded spaces");
End-If;
&POS = Find("%",AS_SUPER_RUN.RUN_CNTL_ID);
If &POS > 0 Then
Error MsgGet(99, 99, "Run Control Ids may not contain %");
End-If;

Component: AS_SUPVTREE
Event: PostBuild
Component Rowset &rsTree;
Local Rowset &rsNode;
Local ApiObject &apiRootNode;
Local ApiObject &apiSession;
Local ApiObject &apiTree;
Local Record &recTree;
Local boolean &boolRes;
Local string &boolFlag;
Local date &dtEffDt;
&rsNode = CreateRowset(Record.AS_SUPVTREE_DTL);
&rsTree = CreateRowset(Record.AS_SUPVTREE_HDR, &rsNode);
&rsTree.InsertRow(1);
&recTree = &rsTree.GetRow(2).GetRecord(1);
&recTree.GetField(Field.PAGE_NAME).Value = "AS_SUPVTREE";
&recTree.GetField(Field.PAGE_FIELD_NAME).Value = "TREECTLEVENT";
&recTree.GetField(Field.PAGE_SIZE).Value = 500;
&recTree.GetField(Field.DISPLAY_LEVELS).Value = 12;
&recTree.GetField(Field.COLLAPSED_IMAGE).Value = "PT_TREE_COLLAPSED";
&recTree.GetField(Field.EXPANDED_IMAGE).Value = "PT_TREE_EXPANDED";
&recTree.GetField(Field.END_NODE_IMAGE).Value = "PT_TREE_END_NODE";
&recTree.GetField(Field.LEAF_IMAGE).Value = "PT_TREE_LEAF";
&recTree.GetField(Field.IMAGE_WIDTH).Value = 15;
&recTree.GetField(Field.IMAGE_HEIGHT).Value = 12;
&recTree.GetField(Field.INDENT_PIXELS).Value = 20;
&apiSession = %Session;
&apiTree = &apiSession.GetTree();
SQLExec("SELECT %DateOut(A.EFFDT) FROM PSTREEDEFN_VW A WHERE
A.TREE_NAME=:1", "AS_SUPERVISOR1", &dtEffDt);
&boolRes = &apiTree.Open("", "", "AS_SUPERVISOR1", &dtEffDt, "", False);
SQLExec("SELECT TREE_NODE FROM PS_AS_NODE_EMPL WHERE EMPLID=:1",
%EmployeeId, &strTreeNode);
rem &apiRootNode = &apiTree.FindnODE(&strTreeNode, "");
&apiRootNode = &apiTree.FindnODE(%EmployeeId, "");
If All(&apiRootNode) Then
If &apiRootNode.HasChildren Then
&strFlag = "X";
Else
&strFlag = "N";
End-If;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&rsNode.InsertRow(1);
&recTree = &rsNode.GetRow(2).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "N";
SQLExec("SELECT DEPTID FROM PS_JOB_CURR_VW WHERE EMPLID=:1",
&apiRootNode.NAME, &strTreeNode);
&recTree.GetField(Field.TREE_NODE).Value = &strTreeNode;
&recTree.GetField(Field.TREE_NODE_NEW).Value = &apiRootNode.NAME;
&recTree.GetField(Field.DESCR).Value = &apiRootNode.DESCRIPTION;
&recTree.GetField(Field.RANGE_FROM).Value = "";
&recTree.GetField(Field.RANGE_TO).Value = "";
&recTree.GetField(Field.DYNAMIC_FLAG).Value = "N";
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = &strFlag;
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = 0;
&apiTree.Close();
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree);
Else
MessageBox(0, "", 20006, 30, "Message not Found");
End-If;

Record: AS_DERIVED_HR
Field: TREECTLEVENT
Event: FIELDCHANGE
Component Rowset &rsTree;
Local Rowset &rsNode;
Local Record &recTreeParent, &recTree;
Local integer &intRow, &intParentLevel, &intLevelOffset;
Local ApiObject &apiSession, &apiTree, &apiParentNode, &apiChildLeaf, &apiChildNode;
Local date &dtEffDt;
Local boolean &boolRes, &boolFirst;
Local string &strRangeFrom, &strRangeTo, &strDynamicRange, &strParentFlag, &strTemp;
If Left(AS_DERIVED_HR.TREECTLEVENT, 1) = "X" Then
&intRow = Value(Right(AS_DERIVED_HR.TREECTLEVENT,
Len(AS_DERIVED_HR.TREECTLEVENT) - 1)) + 1;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&recTreeParent = &rsNode.GetRow(&intRow).GetRecord(1);
&intParentLevel = &recTreeParent.GetField(Field.TREE_LEVEL_NUM).Value;
&intRow = &intRow + 1;
&apiSession = %Session;
&apiTree = &apiSession.GetTree();
SQLExec("SELECT %DateOut(A.EFFDT) FROM PSTREEDEFN_VW A WHERE
A.TREE_NAME=:1", "AS_SUPERVISOR1", &dtEffDt);
&boolRes = &apiTree.Open("", "", "AS_SUPERVISOR1", &dtEffDt, "", False);
&apiParentNode =
&apiTree.FindNode(&recTreeParent.GetField(Field.TREE_NODE_NEW).Value, "");
If &apiParentNode.HasChildren Then
&apiParentNode.Expand(2);
If &apiParentNode.HasChildLeaves Then
&boolFirst = True;
&apiChildLeaf = &apiParentNode.FirstChildLeaf;
While &boolFirst Or
&apiChildLeaf.HasNextSib
If &boolFirst Then
&boolFirst = False;
Else
&apiChildLeaf = &apiChildLeaf.NextSib;
End-If;
If &apiChildLeaf.Dynamic = True Then
&strRangeFrom = "";
&strRangeTo = "";
&strDynamicRange = "Y";
Else
&strRangeFrom = &apiChildLeaf.RangeFrom;
&strRangeTo = &apiChildLeaf.RangeTo;
&strDynamicRange = "N";
End-If;
&rsNode.InsertRow(&intRow - 1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "Y";
&recTree.GetField(Field.TREE_NODE).Value = "";
&recTree.GetField(Field.DESCR).Value = "";
SQLExec("SELECT DEPTNAME,JOBCODE_DESCR FROM PS_AS_SUPER_VW WHERE
SUPERVISOR_ID=:1", &strRangeFrom, &strDescrFrom, &strDescrTo);
&recTree.GetField(Field.RANGE_FROM).Value = &strDescrFrom;
&recTree.GetField(Field.RANGE_TO).Value = &strDescrTo;
&recTree.GetField(Field.DYNAMIC_FLAG).Value = &strDynamicRange;
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = "N";
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = &intParentLevel + 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = 0;
&intRow = &intRow + 1;
End-While;
End-If;
If &apiParentNode.HasChildNodes Then
&boolFirst = True;
&apiChildNode = &apiParentNode.FirstChildNode;
While &boolFirst Or
&apiChildNode.HasNextSib
If &boolFirst Then
&boolFirst = False;
Else
&apiChildNode = &apiChildNode.NextSib;
End-If;
If &apiChildNode.HasChildren Then
&strParentFlag = "X";
Else
&strParentFlag = "N";
End-If;
If &apiTree.LevelUse = "S" Then
&intLevelOffset = &apiChildNode.LevelNumber - &apiParentNode.LevelNumber - 1;
Else
&intLevelOffset = 0;
End-If;
&rsNode.InsertRow(&intRow - 1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "N";
SQLExec("SELECT DEPTID FROM PS_AS_SUPER_VW WHERE SUPERVISOR_ID=:1",
&apiChildNode.Name, &strTreeNode);
&recTree.GetField(Field.TREE_NODE).Value = &strTreeNode;
&recTree.GetField(Field.TREE_NODE_NEW).Value = &apiChildNode.Name;
&recTree.GetField(Field.DESCR).Value = &apiChildNode.Description;
&recTree.GetField(Field.RANGE_FROM).Value = "";
&recTree.GetField(Field.RANGE_TO).Value = "";
&recTree.GetField(Field.DYNAMIC_FLAG).Value = "N";
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = &strParentFlag;
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = &intParentLevel + 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = &intLevelOffset;
&intRow = &intRow + 1;
End-While;
End-If;
&recTreeParent.GetField(Field.PARENT_FLAG).Value = "Y";
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree,
AS_DERIVED_HR.TREECTLEVENT);
End-If;
&apiTree.Close();
Else
If Left(AS_DERIVED_HR.TREECTLEVENT, 1) = "S" Then
&intRow = Value(Right(AS_DERIVED_HR.TREECTLEVENT,
Len(AS_DERIVED_HR.TREECTLEVENT) - 1)) + 1;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
If &recTree.GetField(Field.LEAF_FLAG).Value = "N" Then
SQLExec("SELECT NAME_DISPLAY'-'DESCR100 FROM PS_AS_SUPER_VW A WHERE
A.SUPERVISOR_ID=:1", String(&recTree.GetField(Field.TREE_NODE_NEW).Value),
&strDescr100);
MessageBox(0, "", 0, 0, "The selected node is %1.", &strDescr100);
Else
If &recTree.GetField(Field.DYNAMIC_FLAG).Value = "N" Then
SQLExec("SELECT DESCR100 FROM PS_AS_SUPER_VW A WHERE
A.SUPERVISOR_ID=:1", String(&recTree.GetField(Field.TREE_NODE).Value),
&strDescr100);
REM &recTree.GetField(Field.RANGE_FROM).Value = &strDescr100;
REM &recTree.GetField(Field.RANGE_TO).Value = &strDescr100;
If &recTree.GetField(Field.RANGE_FROM).Value =
&recTree.GetField(Field.RANGE_TO).Value Then
&strTemp = "[" &recTree.GetField(Field.RANGE_FROM).Value "]";
Else
&strTemp = "[" &recTree.GetField(Field.RANGE_FROM).Value " - "
&recTree.GetField(Field.RANGE_TO).Value "]";
End-If;
REM &strTemp = &strDescr100;
Else
&strTemp = "[ ]";
End-If;
MessageBox(0, "", 0, 0, "The selected leaf is %1.", &strTemp);
End-If;
Else
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree,
AS_DERIVED_HR.TREECTLEVENT);
End-If;
End-If;
AS_DERIVED_HR.TREECTLEVENT = "";

Appendix 2: SQL
SQL ID: AS_EMPRPTEMP_SQL
SELECT A.EMPLID
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID =B.EMPLID
AND A.SUPERVISOR_ID=A.EMPLID
AND EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
PeopleCode and SQL’s mytechspeak.com
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=%CurrentDateIn) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND SUPERVISOR_ID<>' '
AND A.SUPERVISOR_ID=A.EMPLID
AND A.HR_STATUS='A'

SQL ID: AS_SUPER_EMP_SQL
SELECT A.EMPLID
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID=B.EMPLID
AND SUPERVISOR_ID=:1
AND EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=%CurrentDateIn) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND A.EMPLID<>A.SUPERVISOR_ID
AND SUPERVISOR_ID<>' '
AND A.HR_STATUS='A'
ORDER BY A.EMPLID

View: AS_SUPER_VW
SELECT DISTINCT A.EMPLID
,B.FIRST_NAME
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID=B.EMPLID
AND A.EFFDT= (
SELECT MAX (EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=SYSDATE) AND A.EFFSEQ= ( SELECT MAX (EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND SUPERVISOR_ID<>' '
UNION
SELECT 'SUPER'
,'super'
FROM DUAL

SQL ID: AS_EMP_SUP_SQL
SELECT DISTINCT EMPLID
, SUPERVISOR_ID
,LEVEL
FROM PS_AS_EMP_SUP_TBL START WITH SUPERVISOR_ID=:1 CONNECT BY NOCYCLE
PRIOR EMPLID=SUPERVISOR_ID
ORDER BY LEVEL

SQL ID: AS_INSERT_SUP
INSERT INTO PS_AS_EMP_SUP_TBL
SELECT EMPLID
,SUPERVISOR_ID
FROM PS_JOB A
WHERE EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=SYSDATE) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND A.HR_STATUS='A' AND A.PER_ORG='EMP'

Appendix D: AppEngine
Action: PeopleCode
Local ApiObject &apiSession, &apiLevel;
Component ApiObject &treeSupervisor;
Local SQL &sqlSuperEmployees, &sqlSwSupEmpSql;
Local number &numReturnDelete, &counter, &numLevel;
Local string &strEmplid, &strSupervisor;
SQLExec(SQL.AS_EMPRPTEMP_SQL, &strEmployeeSupervisor);
&apiSession = %Session;
&treeSupervisor = &apiSession.GetTree("", "", "", "");
&treeSupervisor.Delete("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE");
If Not &treeSupervisor.Create("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE") = 0 Then
&numReturnDelete = &treeSupervisor.Open("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE");
Else
&treeSupervisor.Description = "Supervisor Tree";
&treeSupervisor.Category = "DEFAULT";
&apiLevelCollection = &treeSupervisor.levels;
For &numI = 1 To 12
&apiLevel = &apiLevelCollection.add("SUPLVL:" &numI);
&apiLevel.description = "Level" &numI;
End-For;
End-If;
SQLExec("DELETE FROM PS_AS_SUPER_TBL");
SQLExec("INSERT INTO PS_AS_SUPER_TBL SELECT
SUPERVISOR_ID,substr(NAME_DISPLAY,1,30) FROM PS_AS_SUPER_VW");
SQLExec("DELETE FROM PS_AS_EMP_SUP_TBL");
SQLExec(SQL.AS_INSERT_SUP);
&nodeRootNode1 = &treeSupervisor.InsertRoot(&strEmployeeSupervisor);
&nodeRootNode1.InsertChildLeaf(&strEmployeeSupervisor, &strEmployeeSupervisor);
&treeSupervisor.Save();
AS_SUPER_AET.SUPERVISOR_ID = &strEmployeeSupervisor;

Step: SupTree2
Action: DoSelect
%Select(EMPLID,SUPERVISOR_ID ,LEVELS)
SELECT DISTINCT EMPLID
, SUPERVISOR_ID
,LEVEL
FROM PS_AS_EMP_SUP_TBL START WITH SUPERVISOR_ID=%Bind(SUPERVISOR_ID)
CONNECT BY NOCYCLE PRIOR EMPLID=SUPERVISOR_ID
ORDER BY LEVEL

Step: SupTree2
Action: Peoplecode

Component ApiObject &treeSupervisor;
&nodeRootNode1 = &treeSupervisor.FindNode(AS_SUPER_AET.SUPERVISOR_ID, "");
&counter = &counter + 1;
If All(&nodeRootNode1) And
&treeSupervisor.NodeExists(AS_SUPER_AET.EMPLID) Then
&nodeRootNode1 = &nodeRootNode1.InsertChildNode(AS_SUPER_AET.EMPLID);
&nodeRootNode1.InsertChildLeaf(AS_SUPER_AET.EMPLID, AS_SUPER_AET.EMPLID);
&treeSupervisor.Save();
SQLExec("commit");End-If;


Manivannan Ramar