Project Profile: Position Control Solution Using Standard JDE Applications and Modern Development Tools

We recently had the opportunity to solve a business problem for one of our clients using simple, standard functionality in JDE combined with a variety of User Defined Objects (UDO’s) and orchestrations.  The result was fit-for-purpose functionality that significantly increased data quality, ease of maintenance, and reportability for analysis and to a broad user group.

The Problem

Our client required the ability to track Positions in their organization, regardless of whether the position was currently being filled or was vacant.  They were not using JDE for Human Resources or Payroll, but rather had implemented a third-party system.  This system had the ability to define what position an employee held but could not track positions that were vacant.  Our client had been using Excel to track this information, including the movement of employees into and out of positions.  The Excel spreadsheet had become unwieldy; it was difficult to maintain, prone to keying errors and difficult to report to stakeholders.  In addition, the information on the spreadsheet could not be joined to other system data like payroll costs to create robust reporting for salary costs by position.  

The Solution

Once our client was able to clearly articulate the requirements of the solution and the additional features they wanted to have the solution facilitate (i.e. salary costs by position), the ERP-One consulting team devised a solution using:  

1. Very standard Applications in JDE – like Address Book and Supplemental Data.

1. User Defined Objects (UDOs) - such as CaféOne, personalized forms, grid formats and form extensions.  

1. Orchestrations - to initially load the data from the spreadsheet into the JDE Position Control solution.

1. Data Access Studio (DAS) reports - to report the information in combination with various other standard JDE tables (Business Units, Address Book, various Category Codes, payroll costs in the GL) to create fulsome reports with rigorous master data consistency.

Positions in Address Book

The JDE Address Book was used to store the Positions for our client.  A custom Search Type (PC) was created to define the Positions.  In the existing solution, the Positions had been defined as two pieces of data separated by a dash (-):

• 5 digit “Job Code” – which described the type of position.
• 4 digit “Position Number” – which created a unique position within a Job Code.

For example:  

In the JDE Address Book solution, we utilized the Address Book number (AN8) to house the Position Number.  As the Address Book number (AN8) is a numeric 8-digit data item, we altered the Position Number by removing the dash and the first leading zero in the suffix.  The Position Numbers translated to the following:

With this design, our client was able to maintain their familiar position numbering in a visible field (Address Number AN8) using a standard JDE application that they were familiar with.

Various master data on the Address Book Position record was used to describe and categorize the data as needed:

• Business Unit defined the “Home BU” or “Location” for the position.  
• Various AB Category Codes described additional features of the position, like Pay Grade, Union Code, Job Code and Job Subcode.
• The User Reserved Amount field (F0101 URAT) was used to track the number of hours budgeted for the position.  

A Personal Form was created on Address Book Revisions (P01012/W01012A) to show the relevant information on a single tab rather than scattered across various tabs.  In addition, the labels were changed on the personal form to give them user-friendly descriptions.

Original Address Book Layout

Problem:  The original, standard Address Book Layout contained many tabs and the relevant data was scattered throughout the tabs.  

Position Control Layout with Personal Form and Form Extension UDOs

Solution:  A personalized form was created so that all relevant information was on a single tab on the main form.  Data labels were changed to be meaningful.  A form extension exposed the additional data item to allow tracking of Budgeted Hours for the position.    

Supplemental Data for Employees in Positions

The JDE Supplemental Data functionality (P00091 and P00092) was utilized to create a custom data repository for Employees in Positions.  The solution tracks both current and historical employees in positions, along with effective dates.   Vacancies are tracked by creating an Employee Address Book record for the “Vacant” employee.

CaféOne to join Address Book to Supplemental Data

A CaféOne layout was utilized on the Address Book for Positions to join to the Supplemental Data showing Employees in Positions. Our client can search for Positions in the Address Book and on the same screen, can see the employee history of people in that position.  The form allows them to maximize the Supplemental Data to update or view in a larger form and then return to the joined CaféOne layout.  Alternatively, they are able to update the data directly in the CaféOne layout.  

Orchestrations to Load Data

Several related orchestrations were developed to load the data from a spreadsheet into JDE.  These fit-for-purpose orchestrations were for the initial data load only; therefore, they were built without notifications or other features that would be used for an orchestration intended to run daily or more frequently.

Reporting using Data Access Studio (DAS)

Several DAS reports were designed to provide this data in a meaningful way to both those maintaining the data as well as other stakeholders making business decisions about the data. An example of the DAS report for people currently in positions is shown in the screen shot below.  This report joins data from the following tables:

• Address Book (F0101) – for position data
• Supplemental Data (F00092) – for people in positions

• Business Unit Master (F0006) – for category codes and descriptions related to the Home BU to allow sorting and summarizing by these reporting codes.

Note the following:

• DAS facilitates reporting by Home BU concatenated to BU Description (first column), and the data can be grouped and sorted by this field.
• The report could also be grouped and sorted by Job Codes (Positions) or any of the other fields on the report.
• It is easy to search for all Vacant positions by keying in positions where the current “employee” is 29999999.  

• Hours variance reporting can be done, where the hours budgeted to the position (AB record) can be compared to the actual hours the employee is scheduled (Supplemental Data), and then using DAS formulas to calculate the variance.

*Employee Names are usually part of the report – based on Employee Address Book Number – but are hidden for this article.

Payroll Details by Employee and by Position

Our client also loads payroll GL data from their third-party payroll system into JDE.  We created an orchestration to load these payroll details by employee AB# into a custom JDE table first, and then create the summarized Journal Entry (without employee AB# data) as a second step.  This process achieved automation of the payroll file load using orchestration, but also provided a JDE data repository of Payroll Costs by Employee AND by Position. In DAS, the payroll details by Employee can be joined to the Position Control data outlined in this article to provide some comprehensive and robust reporting of salary costs by employee and position for budgeting, analysis and reconciliation purposes.    

Conclusion

This case study provides a distinct example of how standard JDE applications and tools like UDOs and orchestrations, combined with reporting options like Data Access Studio (DAS) can cleanly and simply solve business requirements.