I use a while-loop to call a function to insert the supervisor's employee data to the table. The function will use a while-loop to call oneself. However, the function will not back to the while loop to insert another supervisor's employee.
This is the PeopleCode.
Function insert_PS_HR_DIRECT_REP_5(&SUPERVISORS, &EMPL)
Local number &i;
Local array of string &SUP_LIST;
&SUP_LIST = &SUPERVISORS;
/* Get employee data */
SQLExec(SQL.Z_GET_EMPL_SQL, &EMPL, %Date, &EMPLID, &EMPL_RCD, &EMPL_EFFDT, &EMPL_STATUS);
/* Using for loop to insert the data */
For &i = 1 To &SUP_LIST.Len
/* Get all supervisors data one by one */
SQLExec(SQL.Z_GET_EMPL_SQL, &SUP_LIST [&i], %Date, &SUPERVISORID, &SUPERVISORS_RCD, &SUPERVISOR_EFFDT, &SUPERVISOR_STATUS);
/* Using to check the PS_HR_DIRECT_REP_5 will not have same record */
SQLExec(SQL.Z_SEARCH_EMPL_SUP_SQL, &SUPERVISORID, &SUPERVISORS_RCD, &EMPLID, &EMPL_RCD, &RESULT);
/* If have not same record, insert the data to the PS_HR_DIRECT_REP_5 */
If &RESULT = "" Then
/* Insert into table */
&REC = CreateRecord(Record.HR_DIRECT_REP_5);
&REC.SUPERVISOR_ID.value = &SUPERVISORID;
&REC.SUPERVIS_EMPL_RCD.value = &SUPERVISORS_RCD;
&REC.EMPLID.value = &EMPLID;
&REC.EMPL_RCD.value = &EMPL_RCD;
&REC.EMPL_STATUS.value = &EMPL_STATUS;
&REC.HR_DR_LEVEL.value = "1";
&REC.JOB_EFFDT.value = &EMPL_EFFDT;
&REC.SUPERVISOR_FLAG.value = "N";
&REC.DRILL_DOWN_FLAG.value = "Y";
&sql_insert = CreateSQL("%insert(:1)");
&sql_insert.Execute(&REC);
End-If;
End-For;
/* Check supervisor's employee count */
SQLExec(SQL.Z_COUNT_EMPL_SQL, %Date, &EMPL, &COUNT);
If &COUNT <> 0 Then
/* Push the employee to the array (to be supervisors)*/
&SUP_LIST.Push(&EMPL);
/* Find the employee of the supervisor */
&SQL = CreateSQL(SQL.Z_SUP_EMPL_SQL, %Date, &EMPL);
&SQL.ReuseCursor = True;
/* If the employee is a supervisor(lead employee), call this function do again to insert the data */
While &SQL.fetch(&EMPL_EMPL)
insert_PS_HR_DIRECT_REP_5(&SUP_LIST, &EMPL_EMPL);
End-While;
End-If;
End-Function;