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_at_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 cant 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
  users 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

51
Questions
Bryan Eckle Summit2Sea Consulting,
LLC 703.582.3665 bryan.eckle_at_sum2sea.com www.sum2s
ea.com