Importing departments and creating the Department Tree

Some while ago I was asked to import departments from a Non-PeopleSoft system and to generate the department tree for these departments. As usual you start off by looking for Vanilla solutions. What I have understood, is that the department tree is usually created manually and using the TreeManager utility TreeMover they are copied across environments. So PeopleSoft has a Vanilla solution for importing and creating department trees. However, after examining the TreeMover, which is a powerfull tool I have to admit, it is a feasible solution only if already a department tree is created manually.

Why do I say this?

The TreeMover uses an input file with 7 different fixed position file layouts for one file. These file layouts are first of all not that easy to create and definitely not easy to maintain. To give you an idea, this is what an import/export file looks like.

So I decided to build my own department tree import and generation Application Engine, which should simplify this.

Input File

First decide on a simpler input format and what is easier than a CSV file in the following format:

SETID;DEPARTMENT;EFFDT;DESCR;DESCRSHORT;PARENT_DEPARTMENT
SHARE;40000;20120101;Corporate Headquarters;COR HQ;ROOT
SHARE;41000;20120101;Human Resources;HR;40000
SHARE;55500;20120101;Sales & Marketing;Sales;40000
SHARE;66600;20120101;Research & Development;R&D;40000
SHARE;44400;20120101;Business Services;BUS SERV;55500
SHARE;61100;20120101;Enginering;R&D;66600

What is important here, is that for each department the parent department is also defined on the same row. At the highest level the parent department is empty or the highest level is recognizable by a indicator (for example ROOT). This will become the highest level in the department tree and needed to generate a starting point, which I will elaborate later in this post.

Relation Record

Departments themselves do not store the hierarchical relationship and are standalone entities. You can see that the input file contains the parent department for each department. We will need to store this relation in a custom table to be used later on, to create the hierarchical view which will be the input for the department tree.

This custom table would only need to have two fields e.g. DEPTID and PARENT_DEPTID.

For each line of the file that will be read, this custom record will be filled with these two values.

Application Engine - Read file and create Departments

Now we need a to create an Application Engine which will read the input file, create Departments, preferably by using a Component Interface on component DEPARTMENT_TBL and fill the custom record with the relationship between departments.

PeopleCode could like something like this:

Local File &File;
Local array of string &aData;
Local string &Line;
Local string &sDelimiter = ";";
Local integer &i;
Local ApiObject &oSession, &oDepartmentTbl;
Local ApiObject &oDeptTblCollection, &oDeptTbl;
Local Record &rDeptTree;

/* Open file for reading */
&File = GetFile("ImportAndGenerateDeptTree.csv", "R");

If &File.IsOpen Then

   /* read entire file */
   While &File.ReadLine(&Line);
      /* create array to separate and store fiels from read line */
      &aData = CreateArrayRept("", 0);
      /* split read line by seperator and store in array */
      &aData = Split(&Line, &sDelimiter);

      &oSession = %Session;

      /* Set the PeopleSoft Session Error Message Mode */
      &oSession.PSMessagesMode = 1;

      /* Get CI */
      &oDepartmentTbl = &oSession.GetCompIntfc(CompIntfc.DEPARTMENT_TBL);

      /* Set CI Create Keys */
      &oDepartmentTbl.SETID = &aData [1];
      &oDepartmentTbl.DEPTID = &aData [2];

      /* Create CI */
      &oDepartmentTbl.Create();

      /* Set DEPT_TBL Collection Field Properties  */
      &oDeptTblCollection = &oDepartmentTbl.DEPT_TBL;
      &oDeptTbl = &oDeptTblCollection.Item(1);
      &oDeptTbl.EFFDT = Date(Value(&aData [3]));
      &oDeptTbl.EFF_STATUS = "A";
      &oDeptTbl.DESCR = &aData [4];
      &oDeptTbl.DESCRSHORT = &aData [5];

      /* Save CI */
      If &oDepartmentTbl.Save() Then
         /* Save department relation in custom record */
         &rDeptTree = CreateRecord(Record.CUSTOM_DEPT_TREE);
         &rDeptTree.DEPTID.Value = &aData [2];
         &rDeptTree.PARENT_DEPTID.Value = &aData [6];
         &rDeptTree.Insert();
      End-If;

   End-While;
End-If;

Hierarchical View

Before you create a Tree, you need to know the hierarchical relationship between departments. You need to create a parent department, before you can create a child department. So we need to sort the data in the custom relationship record, so that the highest level will be selected and processed before the child departments. This can be a pretty complicated procedure, because you have to reference relationships within the same table over rows without knowing how many levels of relationships there are. Fortunately Oracle has a statement for this, which will work perfectly in this case.

To accomplish this, create a view with the following SQL

SELECT LEVEL-1
, DEPTID
, PARENT_DEPTID
FROM PS_CUSTOM_DEPT_TREE
START WITH PARENT_DEPTID = 'ROOT'
CONNECT BY PRIOR DEPTID = PARENT_DEPTID;

This SQL statement will automatically create a hierarchical  tree starting at the highest level, selecting this first and selecting child departments after this repeatedly. Executing this SQL will have the following result:

PATHNO DEPTID     PARENT_DEPTID
------ ---------- ---------------
     0 40000      ROOT
     1 41000      40000
     1 55500      40000
     2 44400      55500
     1 66600      40000
     2 61100      66600

Application Engine - Read Hierarchical View and Generate Departments

Now we have created all departments and have a hierarchical SQL in place and are ready to actually create the Tree.

PeopleCode could look something like this

Local ApiObject &oSession;
Local ApiObject &Tree;
Local ApiObject &ParentNode, &ChildNode, &RootNode;
Local Rowset &rsDeptTree;

&oSession = %Session;

/* Get Tree Object */
&Tree = &oSession.GetTree();

If All(&Tree) Then
   /* Create empty Tree */
   &TreeReturn = &Tree.Create("SHARE", "", "DEPT_SECURITY",
                                      "2012-01-01", "DEPARTMENT");

   &Tree.Description = "Department Security";
   /* "N" - No levels */
   &Tree.LevelUse = "N";
   /* Create Rowset based on hierarchical view */
   &rsDeptTree = CreateRowset(Record.CUSTOM_DEPT_TREE_VW);
   &rsDeptTree.Fill();

   For &i = 1 To &rsDeptTree.ActiveRowCount

      &ParentNodeValue = &rsDeptTree(&i).GetRecord(1).PARENT_DEPTID.Value;
      &ChildNodeValue = &rsDeptTree(&i).GetRecord(1).DEPTID.Value;

      If &ParentNodeValue = "ROOT" Then
         /* add root node */
         &RootNode = &Tree.InsertRoot(&ChildNodeValue);
      Else
         If &RootNode.name = &ParentNodeValue Then
            &RootNode.InsertChildNode(&ChildNodeValue);
         Else
            &ParentNode = &Tree.FindNode(&ParentNodeValue, "");
            &ChildNode = &ParentNode.InsertChildNode(&ChildNodeValue);
         End-If;
      End-If;
   End-For;

   /* Save Tree */
   &ret = &Tree.Save();

End-If;

The code in this post is for explanatory purposes only. You will have to adjust and fit the code to be able to make it work on your environment.