Automating Governmental Budgetary Accounting in Oracle Federal Financials

1. Automating Governmental Budgetary Accounting in Oracle Federal Financials A Case Study

2. Introduction Bryan Eckle Summit2Sea Consulting, LLC 703.582.3665 bryan.eckle@sum2sea.com www.sum2sea.com Expert Oracle Solutions

3. What Will I Learn Today? • How budgetary accounting works in Oracle Federal Financials • Why Oracle Federal Financials has inherent governmental accounting limitations • How to automate and validate the Transaction Code selection process, which drives federal Standard General Ledger (SGL) budgetary accounting

4. Presentation Outline • Background • Issues • Contributing Factors • Agency Requirements • Solution Details • Solution Examples • Questions

5. Audience Participation - Encouraged Who here is currently facing edit issues at their agency?

6. Definition of Terms • SGL (Standard General Ledger) – The US federal government mandated natural account • Transaction Code (TC) – Code used by Oracle Federal Financials to drive dual entry governmental accounting. The TC designates the SGL legs • AFF – Accounting Flexfield, chart of account segment values used in every accounting transaction

7. Definition of Terms • Funds Checking (Reservation) – A process by which specified transactions cannot reduce their respective summary account balances below $0 • i.e. Every purchase is verified to ensure that a budget is not overspent • LOV (List of Values) – Oracle form feature that limits a selection for a particular field

8. Background • US Federal Government Agency • Oracle Federal Financials 11.5.9

9. Background • Absolute Budgetary Control via summary templates • Budgetary SGL Accounting driven by Transaction Codes (TCs) • TCs specify the government mandated dual entry accounting

10. Issues • Limited validation checks to catch or prevent incorrect accounting entries • ‘Edit Checks’ performed after the fact via reporting • Audit concerns

11. Issues • Errors in accounting not discovered until entered in the system • Time consuming fixes for Accounting Errors – “After the fact”

12. Contributing Factors • Process design limitations • Performs combination and balance edit checks, not upon entry / interface, but after the fact

13. Contributing Factors • Oracle Federal Financials limitations • Uses account generator to automatically populate the proprietary AFF based upon pre-defined rules. However, the dual entry budgetary requirements of federal agencies rely on the manual selection of the proper transaction codes by users.

14. Contributing Factors • Oracle Federal Financials limitations • TCs not required within the forms, but necessary for funds checking and budgetary (dual entry) accounting • Cross Validation Rules not checked against the TC legs • TCs modified after transactions have already been accounted and interfaced to the GL • Enhanced Transaction Codes still required users to select the correct TC

15. Contributing Factors - User Driven • User driven limitations • How does a typical user know the proper TC for budgetary accounting?

16. Issues - Examples • User can enter an Obligation (PO) and leave the TC blank. If the TC is blank, then funds checking does not occur. • It is possible to obligate more than what is allocated

17. Issues - Examples • User can select an improper TC whose SGL legs violate a cross validation rule • Unless these issues cause a lack of funding, incorrect accounting can occur

18. Issues - Examples • The user can select the wrong TC and initiate incorrect budgetary accounting

19. Agency Requirements • Standardize TC usage (budgetary SGL accounting) across the agency

20. Agency Requirements • Prevent incorrect accounting at the point of entry, whether it be a user form or interface

21. Agency Requirements • Provide a means to determine the correct budgetary accounting without user intervention

22. Agency Requirements • For TC decision points, limit the TC LOV to only the applicable TC values

23. Agency Requirements • Validate TC SGL legs against cross validation rules

24. Solution • The account generator populates proprietary accounting for the user • How can we streamline the TC selection (budgetary accounting) process for users who have little knowledge of SGL accounting?

25. Solution • For example, in a Purchase Order obligation, if the fund is 00500 (General Fund), then the only possible TC is ‘Obligation-Reg’ • Why can’t we auto-populate the TC with “Obligation-Reg?” • The “Obligation-Reg” TC drives the budgetary natural accounts • DR 461013 • CR 480100

26. Solution • Define agency wide rules for TC usage • Rules are based on… • Transaction Type (i.e. Requisition, Obligation, AP Invoice, AP Payment, AR Invoice, AR Receipt) • Fund (i.e. 00500) • Object Class (expense type)

27. Solution TC Rules – Transaction Type ‘O’ = Obligation

28. Solution • Create a custom form and table to hold the TC rules • The form is user maintainable as needs (rules) change

29. Solution • TCs required on all forms • Forms populate the TC once the requisite information is in place

30. Solution • For example, once the AFF populates based on the account generator, the TC automatically populates • The TC cannot be modified once a transaction is accounted for and interfaced with the GL

31. Solution • Set up CVRs to restrict incorrect AFF combinations • Validate (form enhancement) the TC legs against the cross validation rules

32. Solution • Interfaces also use the table based rules to determine the correct TCs • If the TC cannot be found due to a gap in the TC rule definition, an error message displays – sometimes occurs with new funds

33. Solution • TCs autopopulate on the following forms • Purchase Order • Requisition • PO Receipt • AP invoice • AP Payments • AR Invoices • AR Receipts • Treasury Confirmation

34. Solution – Technical Details • The CUSTOM.pll is a form enhancement that does not require the customization of the underlying form. The CUSTOM.pll is called during several triggers from every Oracle form • The usage of the CUSTOM.pll isolates these enhancements from future upgrades

35. Solution – Technical Details • Creation of a PL/SQL function that accepts attributes from the form / interface (such as transaction type, fund, object class) and returns the TC value • Function calls from the forms via CUSTOM.pll and interfaces

36. Solution – Technical Details • Creation of a single custom form to allow the modification of TC rules

37. Solution - Example PO Obligation Entry – Auto-populate the TC (General Fund)

38. Solution - Example TC Rules • Transaction Type of ‘R’ = PO Receipt

39. Solution - Example TC Definition - Standard

40. Solution - Example TC Definition - Standard

41. Solution - Example PO Obligation Entry – Auto-populate the TC (Reimbursable Fund)

42. Solution - Example In the PO, change the AFF to use an expired fund…

43. Solution Example and tab…

44. Solution - Example A PO Receipt against an expired obligation…

45. Solution - Example …populates with the Receipt-Expired TC

46. Solution - Example TC Rules

47. Solution - Example PO Receipt Entry – Reduce TC LOV for easier selection

48. Solution - Example AR Invoice Entry – CVR Validation on TCs

49. Solution Benefits • Takes budgetary account (TC) selection out of user’s hands – Improved data accuracy • Provides up-front validations that limit audit and reconciliation issues • Increases financial statement reporting accuracy and timeliness

50. Solution Benefits • Decreases number of errors per month edits per month by 80% • Improves audit results • Drives Agency – wide budgetary accounting consistency