AWE: RSPriorApprover and RSPriorApprovers in 9.1 Workaround

RSPriorApprover and RSPriorApprovers were 2 user lists that were removed when AWE processing was moved from the SAC_AW to EOAW_CORE.

It seems when the AWE classes that were spread around each Module were reworked into the new EOAW classes, that the processing order of events changed, resulting in these 2 notification classes no longer worked as designed. Rather than fixing them - they were removed. I had a need to direct workflow and approvals to the previous approver and approvers so I recreated them. In order to get them to work though, it requires some code on each transaction and action you intend to utilize the lists from. That means Approve, Deny, Pushback and Override Buttons will all need the 2 lines of code added in order for it to work. If you don't want to utilize these lists for those actions, don't add the code.

On my custom Event Handler App Package I added 3 new classes, UserBase, UserListPriorApprover and UserListPriorApprovers. UserBase will be instantiated on each action that will trigger the Approval Workflow Engine(AWE). The new user lists reference my UserListPriorApprover and UserListPriorApprovers classes to get the users. UserListPriorApprover looks for the top approver in an array created by UserBase and UserListPriorApprovers looks at all users in the array. If the array can't be found - I look at USERINST to try and determine who has approved. The issue I found is that USERINST is already updated by the time the EventHandler is triggered. I think this is why it was removed in 9.1. Someone reorderd the processing order which affected the abililty to reference the USERINST rows that were present. By filling the array on each action with what is in USERINST - before it is updated we can see who was in the approval chain. Not the most elegant solution - but it works!

To make the User lists work point them at the relevant new custom class, UserListPriorApprover or UserListPriorApprover.

AWE_EVENT_HNDLR.UserBase

/* John Mahon Base class to check prior approvers called from 
Component Peoplecode in 4 transactions before pushback */
import EOAW_CORE:DEFN:*;
import EOAW_CORE:LaunchManager;

class UserBase
   property string PrcsId get;
   property string HeaderRec get;
   property string XrefRec get;
   property array of string PriorApprover get;
   
   method UserBase();
   method Approver() Returns array of string;
   method GetPriorApprovers() Returns array of string;
private
   instance EOAW_CORE:LaunchManager &LaunchManager;
   instance Record &recUserListDef, &recThread;
   instance array of string &aryPrevOprs;
end-class;

Global array of string &PriorApprovers;

get PrcsId
   /+ Returns String +/
   Local string &prcsid;
   SQLExec("SELECT EOAWPRCS_ID FROM %TABLE(:1) WHERE EOAWAPPR_COMPONENT = :2"
, Record.EOAW_TXN, %Component, &prcsid);
   Return &prcsid;
end-get;

get HeaderRec
   /+ Returns String +/
   Local string &headerrec;
   SQLExec("SELECT RECNAME FROM %TABLE(:1) WHERE EOAWPRCS_ID = :2 AND EOAWLEVEL = 0"
, Record.EOAW_TXN_LVL, %This.PrcsId, &headerrec);
   Return &headerrec;
end-get;

get XrefRec
   /+ Returns String +/
   Local string &xrefrec;
   SQLExec("SELECT EOAWRECNAME_XREF FROM %TABLE(:1) WHERE EOAWPRCS_ID = :2"
, Record.EOAW_TXN, &LaunchManager.appInst.appDef.txn.awprcs_id, &xrefrec);
   &xrefrec = "PS_" | &xrefrec;
   Return &xrefrec;
end-get;

get PriorApprover
   /+ Returns Array of String +/
   Local array of string &approver = %This.Approver();
   Return &approver;
end-get;

method UserBase
   &PriorApprovers = %This.PriorApprover;
end-method;

method Approver
   /+ Returns Array of String +/
   Local array of string &aryUsers = CreateArrayRept("", 0);
   Local Record &headerRec = CreateRecord(@("Record." | %This.HeaderRec));
   GetLevel0()(1).GetRecord(@("Record." | %This.HeaderRec)).CopyFieldsTo(&headerRec);
   
   &LaunchManager = create EOAW_CORE:LaunchManager(%This.PrcsId, &headerRec, %OperatorId);
   If &LaunchManager.hasAppInst Then
      &aryUsers = %This.GetPriorApprovers();
   End-If;
   
   Return &aryUsers;
end-method;

method GetPriorApprovers
   /+ Returns Array of String +/
   Local array of string &users = CreateArrayRept("", 0);
   Local string &approver;
   Local number &stepinstance;
   
   Local SQL &priorsql = CreateSQL("select DISTINCT OPRID, EOAWSTEP_INSTANCE 
FROM %Table(:1) WHERE EOAWUSER_TYPE = :3 AND (EOAWSTEP_STATUS = :4 
OR EOAWSTEP_STATUS = :5) AND EOAWSTEP_INSTANCE IN (SELECT EOAWSTEP_INSTANCE 
FROM %Table(:2) WHERE EOAWTHREAD_ID IN (SELECT EOAWTHREAD_ID FROM " | %This.XrefRec | " 
WHERE EOAWTHREAD_ID =:6 OR EOAWPARENT_THREAD=:6) and EOAW_EXTERN_FLAG <> 'Y' 
AND EOAWPRCS_ID = :7) ORDER BY EOAWSTEP_INSTANCE DESC "
, Record.EOAW_USERINST, Record.EOAW_STEPINST, "A", "A", "U"
, &LaunchManager.appInst.thread.awthread_id
, &LaunchManager.appInst.appDef.txn.awprcs_id);
   
   While (&priorsql.Fetch(&approver, &stepinstance))
      &users.Push(&approver);
   End-While;
   
   Return &users;
end-method;

AWE_EVENT_HNDLR.UserListPriorAprover

/* John Mahon Prior Approver Class for RSPriorApprover User List */
import EOAW_CORE:DEFN:*;
import EOAW_CORE:LaunchManager;

class UserListPriorApprover extends EOAW_CORE:DEFN:UserListBase
   property string PrcsId get;
   property string HeaderRec get;
   property string XrefRec get;
   
   method UserListPriorApprover(&rec_ As Record);
   method GetUsers(&aryPrevOprs_ As array of string, &recThread_ As Record) 
Returns array of string;
   method getPriorApprover() Returns array of string;
private
   instance EOAW_CORE:LaunchManager &LaunchManager;
   instance array of string &aryPrevOprs;
   instance Record &recUserListDef, &recThread;
   instance array of string &priors;
end-class;

Global array of string &PriorApprovers;

get PrcsId
   /+ Returns String +/
   Local string &prcsid;
   SQLExec("SELECT EOAWPRCS_ID FROM %TABLE(:1) WHERE EOAWAPPR_COMPONENT = :2"
, Record.EOAW_TXN, %Component, &prcsid);
   Return &prcsid;
end-get;

get HeaderRec
   /+ Returns String +/
   Local string &headerrec;
   SQLExec("SELECT RECNAME FROM %TABLE(:1) WHERE EOAWPRCS_ID = :2 AND EOAWLEVEL = 0"
, Record.EOAW_TXN_LVL, %This.PrcsId, &headerrec);
   Return &headerrec;
end-get;

get XrefRec
   /+ Returns String +/
   Local string &xrefrec;
   SQLExec("SELECT EOAWRECNAME_XREF FROM %TABLE(:1) WHERE EOAWPRCS_ID = :2"
, Record.EOAW_TXN, %This.path.awprcs_id, &xrefrec);
   &xrefrec = "PS_" | &xrefrec;
   Return &xrefrec;
end-get;

method UserListPriorApprover
   /+ &rec_ as Record +/
   %Super = create EOAW_CORE:DEFN:UserListBase(&rec_);
   &recUserListDef = &rec_;
end-method;

method GetUsers
   /+ &aryPrevOprs_ as Array of String, +/
   /+ &recThread_ as Record +/
   /+ Returns Array of String +/
   /+ Extends/implements EOAW_CORE:DEFN:UserListBase.GetUsers +/
   
   &aryPrevOprs = &aryPrevOprs_;
   &recThread = &recThread_;
   Local number &i;
   Local array of string &aryUsers = CreateArrayRept("", 0);
   /* If &PriorApprovers.Len > 0 Then */
   If All(&PriorApprovers) Then
      rem MessageBox(0, "", 0, 0, "Found &PriorApprovers");
      /* Local number &index = 0;
      While &PriorApprovers.Next(&index);
         Local string &array_value = &PriorApprovers [&index];
         &aryUsers.Push(&array_value);
      End-While; */
      Local string &array_value = &PriorApprovers [1];
      &aryUsers.Push(&array_value);
   Else
      rem MessageBox(0, "", 0, 0, "Did not find &PriorApprovers");
      &aryUsers = %This.getPriorApprover();
   End-If;
   
   Local number &index = 0;
   While &aryUsers.Next(&index);
      &array_value = &aryUsers [&index];
      MessageBox(0, "", 0, 0, "&index: '" | &index | "', &array_value: '" | 
&array_value | "'");
   End-While;
   
   Return &aryUsers;
end-method;

method getPriorApprover
   /+ Returns Array of String +/
   Local string &approver, &xref;
   &priors = CreateArrayRept("", 0);
   
   Local Record &headerRec = CreateRecord(@("Record." | %This.HeaderRec));
   GetLevel0()(1).GetRecord(@("Record." | %This.HeaderRec)).CopyFieldsTo(&headerRec);
   
   &LaunchManager = create EOAW_CORE:LaunchManager(%This.PrcsId, &headerRec, %OperatorId);
   If &LaunchManager.hasAppInst Then
      /* Getting the Max user instance with Status of approved doesn't work as 
the RSPriorApprover User list is built after the status has already been set. 
We need to get the Min pending status in order to return correct row. */
      Local SQL &priorsql = CreateSQL("SELECT DISTINCT OPRID 
FROM %table(:1) A WHERE A.EOAWSTEP_STATUS = :4 AND A.EOAWSTEP_INSTANCE 
IN (SELECT E.EOAWSTEP_INSTANCE FROM %table(:2) E WHERE E.EOAWTHREAD_ID 
IN (SELECT D.EOAWTHREAD_ID FROM " | %This.XrefRec | " D WHERE D.EOAWTHREAD_ID = :3 
OR D.EOAWPARENT_THREAD= :3)) AND A.EOAWSTEP_INSTANCE = 
(SELECT MIN(B.EOAWSTEP_INSTANCE) FROM %table(:2) B WHERE B.EOAWSTEP_STATUS = :4 
AND B.EOAWTHREAD_ID IN (SELECT EOAWTHREAD_ID FROM " | %This.XrefRec | " C 
WHERE C.EOAWTHREAD_ID = :3 OR C.EOAWPARENT_THREAD =:3 ))", Record.EOAW_USERINST
, Record.EOAW_STEPINST, %This.step.awthread_id
, %This.step.utils.STEP_STATUS_PENDING);
      
      While &priorsql.Fetch(&approver)
         &priors.Push(&approver);
      End-While;
   End-If;
   
   Return &priors;
end-method;

AWE_EVENT_HNDLR.UserListPriorAprovers

/* John Mahon Prior Approvers Class for RSPriorApprovers User List */
import EOAW_CORE:DEFN:*;
import EOAW_CORE:LaunchManager;

class UserListPriorApprovers extends EOAW_CORE:DEFN:UserListBase
   property string PrcsId get;
   property string HeaderRec get;
   property string XrefRec get;
   
   method UserListPriorApprovers(&rec_ As Record);
   method GetUsers(&aryPrevOprs_ As array of string, &recThread_ As Record) 
Returns array of string;
   method getPriorApprovers() Returns array of string;
private
   instance EOAW_CORE:LaunchManager &LaunchManager;
   instance Record &recUserListDef, &recThread;
   instance array of string &aryPrevOprs;
end-class;

Global array of string &PriorApprovers;

get PrcsId
   /+ Returns String +/
   Local string &prcsid;
   SQLExec("SELECT EOAWPRCS_ID FROM %TABLE(:1) WHERE EOAWAPPR_COMPONENT = :2"
, Record.EOAW_TXN, %Component, &prcsid);
   Return &prcsid;
end-get;

get HeaderRec
   /+ Returns String +/
   Local string &headerrec;
   SQLExec("SELECT RECNAME FROM %TABLE(:1) WHERE EOAWPRCS_ID = :2 AND EOAWLEVEL = 0"
, Record.EOAW_TXN_LVL, %This.PrcsId, &headerrec);
   Return &headerrec;
end-get;

get XrefRec
   /+ Returns String +/
   Local string &xrefrec;
   SQLExec("SELECT EOAWRECNAME_XREF FROM %TABLE(:1) WHERE EOAWPRCS_ID = :2"
, Record.EOAW_TXN, &LaunchManager.appInst.appDef.txn.awprcs_id, &xrefrec);
   &xrefrec = "PS_" | &xrefrec;
   Return &xrefrec;
end-get;

method UserListPriorApprovers
   /+ &rec_ as Record +/
   %Super = create EOAW_CORE:DEFN:UserListBase(&rec_);
   &recUserListDef = &rec_;
end-method;

method GetUsers
   /+ &aryPrevOprs_ as Array of String, +/
   /+ &recThread_ as Record +/
   /+ Returns Array of String +/
   /+ Extends/implements EOAW_CORE:DEFN:UserListBase.GetUsers +/
   
   &aryPrevOprs = &aryPrevOprs_;
   &recThread = &recThread_;
   Local number &i;
   Local array of string &aryUsers = CreateArrayRept("", 0);
   /* If &PriorApprovers.Len > 0 Then */
   If All(&PriorApprovers) Then
      rem MessageBox(0, "", 0, 0, "Found &PriorApprovers");
      Local number &index = 0;
      While &PriorApprovers.Next(&index);
         Local string &array_value = &PriorApprovers [&index];
         &aryUsers.Push(&array_value);
      End-While;
   Else
      rem MessageBox(0, "", 0, 0, "Did not find &PriorApprovers");
      &aryUsers = %This.getPriorApprovers();
   End-If;
   
   &index = 0;
   While &aryUsers.Next(&index);
      &array_value = &aryUsers [&index];
      MessageBox(0, "", 0, 0, "&index: '" | &index | "', &array_value: '" | 
&array_value | "'");
   End-While;
   
   Return &aryUsers;
end-method;

method getPriorApprovers
   /+ Returns Array of String +/
   Local array of string &users = CreateArrayRept("", 0);
   Local string &approver;
   Local number &stepinstance;
   
   Local Record &headerRec = CreateRecord(@("Record." | %This.HeaderRec));
   GetLevel0()(1).GetRecord(@("Record." | %This.HeaderRec)).CopyFieldsTo(&headerRec);
   
   &LaunchManager = create EOAW_CORE:LaunchManager(%This.PrcsId, &headerRec
, %OperatorId);
   If &LaunchManager.hasAppInst Then
      Local SQL &priorsql = CreateSQL("select DISTINCT OPRID, EOAWSTEP_INSTANCE 
FROM %Table(:1) WHERE EOAWUSER_TYPE = :3 AND (EOAWSTEP_STATUS = :4 
OR EOAWSTEP_STATUS = :5) AND EOAWSTEP_INSTANCE IN (SELECT EOAWSTEP_INSTANCE 
FROM %Table(:2) WHERE EOAWTHREAD_ID IN (SELECT EOAWTHREAD_ID FROM " | %This.XrefRec | 
" WHERE EOAWTHREAD_ID =:6 OR EOAWPARENT_THREAD=:6) and EOAW_EXTERN_FLAG <> 'Y' 
AND EOAWPRCS_ID = :7) ORDER BY EOAWSTEP_INSTANCE DESC ", Record.EOAW_USERINST
, Record.EOAW_STEPINST, "A", "A", "U"
, &LaunchManager.appInst.thread.awthread_id
, &LaunchManager.appInst.appDef.txn.awprcs_id);
      
      While (&priorsql.Fetch(&approver, &stepinstance))
         &users.Push(&approver);
      End-While;
   End-If;
   
   Return &users;
end-method;

Now all I need to do to use these new UserLists as part of my AWE definitions is to call UserBase on my triggering action. In the case of the Recruiting Components - you can see below that it's called on the Approval Field Change.

import HRS_JOB_OPENING_MANAGER:CMP_HRS_JOB_OPENING:BUS:JOController;

/* John Mahon Logic for RSPriorApprover and RSPriorApprovers User Lists */
import TU_AWE_EVNT_HNDLR:UserBase;

/* END John Mahon */

Component HRS_JOB_OPENING_MANAGER:CMP_HRS_JOB_OPENING:BUS:JOController &JO;

/* John Mahon Logic for RSPriorApprover and RSPriorApprovers User Lists */
&userbase = create TU_AWE_EVNT_HNDLR:UserBase();
/* END John Mahon */

&JO.doFldAction(&JO.FieldChangeAction, GetRecord().Name, GetField().Name);

RSPriorApproverCode.txt

Reporting Structure Report with Excel Output

I received a request for a Position Data Report. The client called it a Span of Control Report and it was to show how many direct reports each manager had. The client also wanted this report to open in Excel while still being formatted. Here is what I came up with. After a little bit of work I came up with an App Engine that uses a recursive function that gets all Reports To and does some processing. I could have used CONNECT BY but didn't for a reason I don't recall. SQR wasn't an option for me either since the functions aren't naturally recursive (the variables aren't local within the stack) and I wanted to perform some additional logic and it would have made it unnecessarily complex. We then use OLE to open and write to an Excel File.

Report Output(Obviously modified so totals are off)

In PeopleSoft, the top position in a Business Unit is defined as one that reports to itself. Within our Org, that is not the case so in order to properly show BUs for this report, I need to give users the ability to define the top per BUSINESS_UNIT. We also allow the user running the report to define the colors they want for the reporting rows and whether they want a full list of employees or only those with Direct Reports. Since we are using OLE, we need an NT Process Scheduler with Excel installed.

Run Control

Report Config Page on Run Control

Defaults Configuration

To achieve the desired affect we are going to instantiate an excel object, use it to open a template file, save it as our new output file and fill it with formatted data. To make things easier I use a template file that is already setup for the report. I set my column header, column formats and even header image in the template file and then upload the Template file to the Process Scheduler at runtime (if one is not uploaded at runtime we use a default that is stored separately.) We allow our clients to upload new Template files on their own through a management component that also sets defaults in case the users does not define them on the Run Control. The management component also allows us to format row colors.

The Template File

We begin the process by setting initial AE variables and instantiating record, sql and array objects. We then move on to Open the Excel Workbook.

/* *************************************** */
/* Begin Process                           */
/* *************************************** */
GetExcelObject();

The GetExcelObject() function instantiates our COM object, opens the template and saves it as a new file. &template_path is the full path and filename of my template file. We use a getattachment to retrieve it from the a File.DB.

   
/* *************************************** */
/* GetExcelObject()                        */
/* *************************************** */
Function GetExcelObject() 
   &OBJ_excel = CreateObject("COM", "Excel.Application");
   &OBJ_excel.DisplayAlerts = "False";
   &OBJ_excel.UserControl = "False";
   &OBJ_workbooks = ObjectGetProperty(&OBJ_excel, "Workbooks");
   &OBJ_workbook = ObjectDoMethod(&OBJ_workbooks, "Open", &template_path);
   
   /* SaveAs New File */
   SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = :1",
   &process_instance, &outputdir);
   If Substring(&outputdir, Len(&outputdir), 1) <> "\" Then
      &outputdir = &outputdir | "\";
   End-If;
   &output_path = &outputdir | &EXCEL_FileName;
   
   &OBJ_excel.ActiveWorkBook.SaveAs(&output_path);
End-Function;

I give my users the ability to run 1 or all BUSINESS_UNITs, so i push all into an array and loop through that.

/* Fill Business Unit Array */
If All(&rc_business_unit) Then
   &ARRAY_business_units.Push(&rc_business_unit);
Else
   &sql8.Execute(&REC_bus_unit_tbl_hr);
   While &sql8.Fetch(&REC_bus_unit_tbl_hr)
      &ARRAY_business_units.Push(&REC_bus_unit_tbl_hr.BUSINESS_UNIT.Value);
   End-While;
End-If;
/* Loop through BUSINESS_UNIT Array */

&bu_index = 0;
While &ARRAY_business_units.Next(&bu_index);
   &business_unit = &ARRAY_business_units [&bu_index];
   
   If All(&rc_positon_nbr) Then
      &where_string = "AND PD.POSITION_NBR = '" | &rc_positon_nbr | "'"
   Else
      &where_string = "AND PD.REPORTS_TO = PD.POSITION_NBR        
AND PD.BUSINESS_UNIT = '" | &business_unit | "'";
   End-If;
   
   &row = 10;
   
   GetExcelWorksheet();

For each BUSINESS_UNIT the function GetExcelWorksheet() will copy the template worksheet and save it as a new name.

/* *************************************** */
/* GetExcelWorksheet()                     */
/* *************************************** */
Function GetExcelWorksheet()
   
   If &bu_index = 1 Then
      &OBJ_worksheets = ObjectGetProperty(&OBJ_workbook, "Worksheets");
   End-If;
   
   &OBJ_worksheet = ObjectGetProperty(&OBJ_worksheets, "Item", &bu_index);
   ObjectDoMethod(&OBJ_worksheet, "Copy", &OBJ_worksheet);
   &OBJ_worksheet = ObjectGetProperty(&OBJ_worksheets, "Item", &bu_index);
   ObjectSetProperty(&OBJ_worksheet, "Name", &business_unit);
   
End-Function;

Then we set the Header. I use an array to store my format settings.

/* *************************************** */
/* SetWorksheetHeader()                    */
/* *************************************** */
Function SetWorksheetHeader()
   
   /* Set Output Header */
   &OBJ_worksheet.Cells(1, 1).Value = "Temple University Health System";
   &OBJ_worksheet.Cells(2, 1).Value = "Span of Control Summary";
   &OBJ_worksheet.Cells(3, 1).Value = "Org " | &business_unit | " Supervisors";
   &OBJ_worksheet.Cells(4, 1).Value = &asofdate;
   
End-Function;

/* *************************************** */
/* PopulateFormatArray()                   */
/* *************************************** */
Function PopulateFormatArray()
   
   /** Check Run Control page, if settings is missing use default  **/
   Local Record &REC_span_cfg = CreateRecord(Record.TU_HR_SPAN_CFG);
   SQLExec("SELECT 'Y' FROM %TABLE(:1) WHERE OPRID = :2 AND RUN_CNTL_ID = :3",
 &REC_span_cfg, &oprid, &run_cntl_id, &CFG_exist);
   
   If &CFG_exist = "Y" Then
      MessageBox(0, "", 0, 0, "Using User Defined configuration 
for Level and Color");
      &sql8 = CreateSQL("%SELECTALL(:1) WHERE OPRID = :2 AND RUN_CNTL_ID = :3
ORDER BY LEVELNUM ASC", &REC_span_cfg, &oprid, &run_cntl_id);
      &ARRAY_properties = CreateArrayRept(CreateArrayRept("", 0), 0);
      
      &lineno = 1;
      While &sql8.Fetch(&REC_span_cfg)
         &ARRAY_properties.Push(&REC_span_cfg.LEVELNUM.Value);
         &ARRAY_properties [&lineno].Push(&REC_span_cfg.FLAG.Value);
         &ARRAY_properties [&lineno].Push(&REC_span_cfg.COLOR.Value);
         
         /* Set Header key values */
         If &lineno <= 7 Then
            If &REC_span_cfg.FLAG.Value = "Y" Then
               &OBJ_worksheet.Cells(&lineno, 3).Font.Bold = True;
            Else
               &OBJ_worksheet.Cells(&lineno, 3).Font.Bold = False;
            End-If;
            &OBJ_worksheet.Cells(&lineno, 3).Interior.Colorindex =
            
&REC_span_cfg.COLOR.Value;
         End-If;
         &lineno = &lineno + 1;
      End-While;
   Else
      Local Record &REC_span_dft = CreateRecord(Record.TU_HR_SPAN_DFT);
      MessageBox(0, "", 0, 0, "Use Default configuration for Level and Color");
      &sql8 = CreateSQL("%SELECTALL(:1) WHERE ATTACH_SEQ_NBR = :2
              ORDER BY LEVELNUM ASC", &REC_span_dft, &ATTACH_SEQ_NBR);
      &ARRAY_properties = CreateArrayRept(CreateArrayRept("", 0), 0);
      
      &lineno = 1;
      While &sql8.Fetch(&REC_span_dft)
         &ARRAY_properties.Push(&REC_span_dft.LEVELNUM.Value);
         &ARRAY_properties [&lineno].Push(&REC_span_dft.FLAG.Value);
         &ARRAY_properties [&lineno].Push(&REC_span_dft.COLOR.Value);
         
         /* Set Header key values */
         If &lineno <= 7 Then
            If &REC_span_dft.FLAG.Value = "Y" Then
               &OBJ_worksheet.Cells(&lineno, 3).Font.Bold = True;
            Else
               &OBJ_worksheet.Cells(&lineno, 3).Font.Bold = False;
            End-If;
            &OBJ_worksheet.Cells(&lineno, 3).Interior.Colorindex = 
            &REC_span_dft.COLOR.Value;
         End-If;
         &lineno = &lineno + 1;
      End-While;
   End-If;
End-Function;

We then can get our Top level Supervisor. In our case we look for POSITION_NBR = REPORTS_TO and when we don't find it we reference our override table. We then output it to our report and then begin our calls to our recursive GetReportingPositions(0, &asofdate, &top_level_supv) function.

/* **************************************************************** */
/* GetReportingPositions(&x As number, &in_date, &in_position_nbr); */
/* **************************************************************** */
Function GetReportingPositions(&x As number, &in_date, &in_position_nbr);
   Local string &fill;
   Local Record &REC_position_data = CreateRecord(Record.POSITION_DATA);
   
   /** Update order by based on Description **/
   Local SQL &sql2 = CreateSQL("%SELECTALL(:1 PD) WHERE PD.EFFDT = 
(SELECT MAX(PD_ED.EFFDT) FROM %TABLE(:1) PD_ED WHERE PD_ED.POSITION_NBR 
 = PD.POSITION_NBR  AND PD_ED.EFFDT <= %DATEIN(:2)) AND PD.EFF_STATUS = 'A' 
 AND PD.POSN_STATUS = 'A' AND PD.REPORTS_TO = :3 AND 
 PD.POSITION_NBR <> :3 ORDER BY PD.DESCR ASC");
   &sql2.ReuseCursor = True;
   
   Local number &in_level = &x + 1;
   &fill = "";
   While Len(&fill) < &in_level * 4
      &fill = &fill | "-";
   End-While;
   
   &sql2.Execute(&REC_position_data, &in_date, &in_position_nbr);
   
   While &sql2.Fetch(&REC_position_data)
      
      /* Does Subordinate have Positions reporting to them - if yes output */
      If &sql7test = "N" Then
         &sql7 = CreateSQL("SELECT 'Y' FROM %TABLE(:1) PD WHERE PD.EFFDT = 
        (SELECT MAX(PD_ED.EFFDT) FROM %TABLE(:1) PD_ED WHERE PD_ED.POSITION_NBR = 
        D.POSITION_NBR AND PD_ED.EFFDT <= %DATEIN(:2)) AND PD.REPORTS_TO = :3 AND 
        D.POSITION_NBR <> :3 AND PD.EFF_STATUS = 'A' AND PD.POSN_STATUS = 'A'",              
        REC_position_data, &in_date, &REC_position_data.POSITION_NBR.Value);
         &sql7.ReuseCursor = True;
         &sql7test = "Y";
      Else
         &sql7.Execute(&REC_position_data, &in_date,
&REC_position_data.POSITION_NBR.Value);
      End-If;
      If &sql7.Fetch(&test) Or
            &rc_show_all_flag = "Y" Then
         /* Get Counts of Reports to and add to summary array */
         &count = 0;
         If &sql9test = "N" Then
            &sql9 = GetSQL(SQL.TU_SPAN_POS_INCUMBENT_COUNT, Record.POSITION_DATA,
 Record.JOB, &asofdate, &REC_position_data.POSITION_NBR.Value);
            &sql9.ReuseCursor = True;
            &sql9test = "Y";
         Else
            &sql9.Execute(Record.POSITION_DATA, Record.JOB, &in_date,
 &REC_position_data.POSITION_NBR.Value);
         End-If;
         
         While &sql9.Fetch(&report_bu, &report_count);
            PopulateSummaryArray(&report_bu, &report_count);
            &count = &count + &report_count;
         End-While;
         
         &test = "N";
         &sql3.Execute(&REC_job, &in_date, &REC_position_data.POSITION_NBR.Value);
         While &sql3.Fetch(&REC_job)
            GetName(&asofdate, &REC_job.EMPLID.Value, &name);
            GetDept(&asofdate, &REC_job.SETID_DEPT.Value, 
&REC_job.DEPTID.Value, &dept_descr);
            PopulateExcelObject(&in_level, &row
, &fill | &REC_position_data.POSITION_NBR.Value
, &fill | &REC_position_data.DESCR.Value
, &name, &REC_job.BUSINESS_UNIT.Value, &REC_job.DEPTID.Value
, &dept_descr, &count);
            &row = &row + 1;
            &test = "Y";
         End-While;
         If &test = "N" Then
            MessageBox(0, "", 0, 0, "Vacant: Could not get PS_JOB " |
REC_position_data.POSITION_NBR.Value);
            SQLExec("SELECT SETID FROM %TABLE(:1) WHERE SETCNTRLVALUE = :2"
, Record.SET_DEPT_BU_VW, &REC_position_data.BUSINESS_UNIT.Value
, &setid_dept);
            GetDept(&asofdate, &setid_dept, &REC_position_data.DEPTID.Value
, &dept_descr);
            PopulateExcelObject(&in_level, &row
, &fill | &REC_position_data.POSITION_NBR.Value
, &fill | &REC_position_data.DESCR.Value, "Vacant"
, &REC_position_data.BUSINESS_UNIT.Value, &REC_position_data.DEPTID.Value
, &dept_descr, &count);
            &row = &row + 1;
         End-If;
      End-If;
      
      /* Does Subordinate's reporting positions have Positions reporting to them 
- if yes go down another level */
      If &sql6test = "N" Then
         &sql6 = CreateSQL("SELECT 'Y' FROM %TABLE(:1) PD WHERE PD.EFFDT = 
(SELECT MAX(PD_ED.EFFDT) FROM %TABLE(:1) PD_ED WHERE PD_ED.POSITION_NBR = 
PD.POSITION_NBR AND PD_ED.EFFDT <= %DATEIN(:2)) AND 
PD.REPORTS_TO IN (SELECT PD1.POSITION_NBR FROM %TABLE(:1) PD1 
WHERE PD1.EFFDT = (SELECT MAX(PD1_ED.EFFDT) FROM %TABLE(:1) PD1_ED 
WHERE PD1_ED.POSITION_NBR = PD1.POSITION_NBR AND PD1_ED.EFFDT <= %DATEIN(:2)) 
AND PD1.REPORTS_TO = :3 AND PD1.POSITION_NBR <> :3 AND PD1.EFF_STATUS = 'A' 
AND PD1.POSN_STATUS = 'A') AND PD.EFF_STATUS = 'A' AND PD.POSN_STATUS = 'A'"
, &REC_position_data, &in_date, &REC_position_data.POSITION_NBR.Value);
         &sql6.ReuseCursor = True;
         &sql6test = "Y";
      Else
         &sql6.Execute(&REC_position_data, &in_date
, &REC_position_data.POSITION_NBR.Value);
      End-If;
      If &sql6.Fetch(&test) Or
            &rc_show_all_flag = "Y" Then
         GetReportingPositions(&in_level, &in_date
, &REC_position_data.POSITION_NBR.Value);
      End-If;
      
   End-While;
   &sql2.Close();
End-Function;

/* *************************************** */
/* PopulateExcelObject()                   */
/* *************************************** */
Function PopulateExcelObject(&level As number, &x As number, &in_pos_nbr
, &in_pos_descr, &in_name, &in_bu, &in_dept, &in_dept_descr
, &in_count As number)
   
   /* Reset Function variables */
   &bold = False;
   &colorindex = 2;
   
   /* Check Level Properties */
   &index = 0;
   While &ARRAY_properties.Next(&index);
      &test = Value(&ARRAY_properties [&index][1]);
      &test2 = &ARRAY_properties [&index][2];
      &test3 = &ARRAY_properties [&index][3];
      If &level = &test Then
         &bold = &ARRAY_properties [&index][2];
         &colorindex = &ARRAY_properties [&index][3];
         If &bold = "Y" Then
            &bold = True;
         Else
            &bold = False;
         End-If;
      End-If;
   End-While;
   
   &range = "A" | &x | ":G" | &x;
   &OBJ_worksheet.Range(&range).Font.Bold = &bold;
   &OBJ_worksheet.Range(&range).Interior.Colorindex = &colorindex;
   
   &OBJ_worksheet.Cells(&x, 1).Value = &in_pos_nbr;
   &OBJ_worksheet.Cells(&x, 1).Value = &in_pos_nbr;
   &OBJ_worksheet.Cells(&x, 2).Value = &in_pos_descr;
   &OBJ_worksheet.Cells(&x, 3).Value = &in_name;
   &OBJ_worksheet.Cells(&x, 4).Value = &in_bu;
   &OBJ_worksheet.Cells(&x, 5).Value = &in_dept;
   &OBJ_worksheet.Cells(&x, 6).Value = &in_dept_descr;
   &OBJ_worksheet.Cells(&x, 7).Value = &in_count;
   
End-Function;

After we've populated the report, we need to ensure that we close it and any open cursors since we are re-using them for performance. If we don't close the excel object we will clog the server with open EXCEL.exe instances.

/* *************************************** */
/* CloseExcelObject()                      */
/* *************************************** */
Function CloseExcelObject()
   
   /* Delete "Template" Worksheet */
   &OBJ_worksheet = ObjectGetProperty(&OBJ_worksheets, "Item", "Template");
   ObjectDoMethod(&OBJ_worksheet, "Delete");
   
   /* Save Excel file and quit */
   &OBJ_excel.ActiveWorkBook.Save();
   &OBJ_excel.ActiveWorkBook.Close();
   &OBJ_excel.DisplayAlerts = "True";
   &OBJ_excel.Quit();
   
End-Function;

I've attached a complete copy of the Step if you want to copy it, but you will probably benefit from re-writing it, as it will be tailored to your environment.
Step2 Complete