Automating Governmental Budgetary Accounting in Oracle Federal Financials

A Case Study

Introduction Bryan Eckle

Summit2Sea Consulting, LLC

703.582.3665

[email protected]

www.sum2sea.com

Expert Oracle Solutions

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

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

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

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

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

Background • US Federal Government Agency • Oracle Federal Financials 11.5.9

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

Agency Requirements • Validate TC SGL legs against cross validation rules

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?

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

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)

Solution TC Rules – Transaction Type ‘O’ = Obligation

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

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

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

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

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

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

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

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

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

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

Solution - Example TC Rules – Transaction Type of ‘R’ = PO Receipt

Solution - Example TC Definition - Standard

Solution - Example TC Definition - Standard

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

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

and tab… Solution Example

Solution - Example A PO Receipt against an expired obligation…

Solution - Example …populates with the Receipt-Expired TC

TC Rules Solution - Example

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

Solution - Example AR Invoice Entry – CVR Validation on TCs

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

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

Questions Bryan Eckle Summit2Sea Consulting, LLC 703.582.3665

[email protected]

www.sum2sea.com