The Department on Energy (DOE) had not received a clean audit opinion in several years prior to the implementation of a new Oracle Federal Financial system. After implementation of the new Oracle Federal Financial system, the DOE utilized its own technical resources to develop custom financial statements using Oracle Reports. These custom reports were taking an extremely long time to complete and were unreliable. For example, a balance sheet report took over 24 hours to complete. Summit2Sea was requested to assist with improving the reporting capability of the new Oracle Federal Financials system.
Objectives & Goals
- Obtain a clean financial audit for the Department of Energy
- Design and build financial statement reports with reliable auditable results
- Improve processing performance of the financial statement reports substantially.
- Provide end users the capability to modify the financial statement without subsequent development work or code changes.
- Reduce time required to complete a change to a financial statement from months/weeks to days
Recommendations & Advice
After a thorough analysis, S2S provided the following recommendation/prototype as a solution to the DOE financial statement reporting issues. The recommendations were to create the following:
- Use the standard Oracle Financial Statement Generator (FSG) row set forms; create the definitions for each of the financial statements. These reports include the Balance Sheet, Net Cost, Net Position, Statement of Financing, Custodial Activity, Statement of Budgetary Resources, and the SF133. Utilizing this functionality instead of custom financial statements allows the end users to modify the report definitions without the need of developers for any coding. For example, additional SGLs could be added or removed to a certain report line, or add additional lines to the reports. For total lines, a value set was created for each financial statement row set, and the child values defined determined what rows were summed up for the total.
- Create a nightly (or periodic) program that consolidates portions of the Accounting Flexfield (AFF) and gl_je_lines table information needed for the financial statements by period into a manageable number. Currently, gl_je_lines have over 271 million lines, and the rollup table has 3.5 million lines. The gl_balances table could not be used as pieces of information on DFF gl_je_lines were needed to get additional information such as trading partner. This rollup information is refreshed for closed periods periodically. The individual reports look at open periods to get current reporting information.
- Create PL/SQL programs for the financial statements that passed in an array of parameters to allow flexibility of what information is returned. Then using the report definitions from the row sets, and the consolidated data, the report information was created into a custom table.
- Once the pl/sql program was finished, an Oracle report program was spawned that returned the data that inserted into the custom table.
The following positive impacts resulted from this solution:
- No future development intervention was required for changing report definitions by end users.
- Development resources were able to work on other priority issues.
- Reduced the amount of time from weeks or months to day(s) required to make a change to an existing financial statement.
- Significantly improved performance, financial statements completed in less than 15 minutes.
- Improved response time of DOE staff during month end close and financial system audits.
- The Department of Energy has received a clean opinion on the audits every year since completion of this effort by Summit2Sea Consulting.