Advanced PeopleSoft Technical Development: Part 2

 

In the first part of this series, we covered fundamental PeopleSoft coding techniques, including looping through rowsets, SQLExec, debugging, and batch processing using Application Engine. In this second part, we’ll dive deeper into advanced PeopleSoft development concepts with more hands-on examples.

---

1. Dynamic SQL in PeopleCode Using SQL Objects

Problem: Hardcoded SQL statements make maintenance difficult.

Solution: Use SQL objects for better modularity and reusability.

Steps to Implement:

1. Create an SQL Object in App Designer

   • Navigate to File → New → SQL Object
   • Name it SQL_GET_ACTIVE_EMPLOYEES
   • Add the following SQL query:

   SELECT EMPLID, EMPL_NAME FROM PS_EMPLOYEES WHERE STATUS = 'ACTIVE'
   

2. Use It in PeopleCode

   Local SQL &sql;
   Local Record &rec;
   
   &sql = CreateSQL("%SQL(SQL_GET_ACTIVE_EMPLOYEES)");
   
   While &sql.Fetch(&rec)
       MessageBox(0, "", 0, 0, "Employee: " | &rec.EMPL_NAME.Value);
   End-While;
   

✅ This allows SQL reuse without embedding queries directly into PeopleCode.

---

2. Using File Layouts for CSV File Import

Problem: Manually processing CSV files is error-prone and time-consuming.

Solution: Use a File Layout to parse CSV files efficiently.

Steps to Implement:

1. Create a File Layout in App Designer

   • File → New → File Layout
   • Set File Type = CSV
   • Define fields (e.g., EMPLID, EMPL_NAME, DEPT)

2. Write PeopleCode to Read CSV File

   Local File &file;
   Local Record &rec;
   Local FileLayout &layout;
   
   &file = GetFile("C:\employee_data.csv", "R", %FilePath_Absolute);
   &layout = GetFileLayout(FileLayout.EMPLOYEE_FILE);
   
   While &file.ReadLine()
       &rec = &layout.GetRecord();
       &layout.SetRecord(&rec, &file.GetTextLine());
       MessageBox(0, "", 0, 0, "Loaded: " | &rec.EMPL_NAME.Value);
   End-While;
   
   &file.Close();
   

✅ This automates data imports and reduces human errors.

---

3. Triggering Workflow Approvals in PeopleSoft

Problem: Manually handling approvals is inefficient.

Solution: Use SendWorkflowEvent() to trigger approvals dynamically.

Steps to Implement:

Local string &emplID = "12345";
Local string &workflowName = "EMP_APPROVAL";

TriggerBusinessEvent(&workflowName, &emplID);

✅ This initiates workflow approvals automatically when an event occurs.

---

4. Using Rowsets for Multi-Level Data Processing

Problem: Handling hierarchical data with loops can be complex.

Solution: Use nested Rowsets for structured data processing.

Example: Fetching Employee and their associated Department Data

Local Rowset &level0, &level1;
Local number &i, &j;

&level0 = GetLevel0();

For &i = 1 To &level0.ActiveRowCount
    &level1 = &level0.GetRow(&i).GetRowset(Scroll.DEPARTMENTS);
    For &j = 1 To &level1.ActiveRowCount
        MessageBox(0, "", 0, 0, "Department: " | &level1.GetRow(&j).GetRecord(Record.DEPARTMENTS).DEPT_NAME.Value);
    End-For;
End-For;

✅ This makes processing multi-level hierarchical data easier.

---

5. Sending Automated Emails from PeopleCode

Problem: Manually notifying users is inefficient.

Solution: Use SendMail() to send emails programmatically.

Example: Sending an Email Notification

Local string &to = "employee@company.com";
Local string &subject = "Your Timesheet is Approved";
Local string &body = "Dear Employee,\n\nYour timesheet has been approved successfully.";

SendMail(&to, "", "", &subject, &body, %PlainText, "", "");

✅ This improves communication efficiency in PeopleSoft workflows.

---

Conclusion

By implementing these advanced PeopleSoft techniques, you can enhance automation, improve code maintainability, and streamline complex processes. Keep experimenting and refining your PeopleSoft development skills! 🚀

SHARE

Ayesha Wee

• 
• 
• 
• 
•