Request: A client recently asked for assistance in balancing their 401(k) deductions. The challenge is they needed information from the EDF (employee deduction) table and the DDS (deduction detail) reports. Specifically they wanted to pull percentages and YTD amounts from the EDF table and current payroll deduction amounts from the DDS report. They needed to do this for 19 deduction codes. Given the complexity of the balancing routine they needed this information to be presented in Excel spreadsheets for analysis. And they needed this done in a timely manner during the payroll calculate before the payroll update occurred.
Following is a diagram depicting the clients request:
Solution: The solution is fairly detailed and will be presented in summary here with links to the detail in subsequent articles.
One challenge was how to report the DDS information given that during the payroll process the information has not yet updated a database table which can easily be reported against. We looked a creating a view to read the raw columns on the PCF tables. We quickly looked at alternatives and settled on a VB script which parsed the DDS report and created SQL necessary to update a temporary table.
A job was created which did the following:
(1) Creates a temporary PYT file requesting a DDS report be ran for the deduction codes requested.
(2) Runs the delivered emPath DDS program (pr852) with no modifications.
(3) Runs a VB script to parse the DDS reports.
(4) Runs a SQL script to create a temporary table (temp_ddx).
(5) Runs SQL statements created in step 3 and updated the temp_ddx table created in step 4
(6) Runs a SQL script to create a temporary table (named temp_401k_dds). Note that I created the temp_401k_dds table and then copied the temp_ddx table created in step 5. The reason is because I hope to use the temp_ddx table other dds reports beyond the 401(k) request.
(7) Runs a SQL script to create two views.
(8) Defined universes to Business Objects such that the user can create spreadsheets based on the views created in step 7.
Note that as detail technical articles are created, for the steps above, they will be referenced as related articles and attached to this article.