Introduction to Ledgers in OAKS

1
Introduction to Ledgers in OAKS

• Robert Cooperman
• OBM Training and Communications Manager
• robert.cooperman_at_obm.state.oh.us

2
Whats Inside?

• The ledgers and ledger groups found in OAKS
• How ledgers interact
• All about budget check
• Tracking an expenditure from PO to Voucher and
  how it posts to the General Ledger
• Tracking a revenue transaction the same way
• Commitment Control reporting
• Understanding difficulties in reporting

3
Whats a Ledger?
The General Ledger
Commitment Control Ledgers
4

• Commitment Control Ledgers
• Used in budget checking
• Stores an agencys budget
• Saves transaction details if budget check is
  successful

5

• General Ledger
• Known as the Actuals ledger
• Records accounting entries from sub-modules
• Creates the financial picture of the State of
  Ohio

6
Posting to the CC Ledgers
7
Posting to the GL
Voucher AR Update Pay Cycle
8
Posting Payroll
9
Posting POs
10
These are Ledger GROUPS!

• Two Types
• Expense
• Revenue

Check out our suffixes!
11
Expense Ledger Types
12
CC_APR_BUD
CC_APR_ENC
CC_APR_EXP
Connected to Fund, ALI, Account, Department ID

• Highest level of budget
• Based on legislative authority
• Parent of Allotment

13
CC_ALT_BUD
CC_ALT_ENC
CC_ALT_EXP
Connected to Fund, ALI, Account, Department ID

• Child of Appropriation
• Cannot overspend what its parent provides

14
CC_ATR_BUD
CC_ATR_ENC
CC_ATR_EXP
Connected to Fund, ALI, Account, Department ID,
Program (in some rulesets), and Budget
Reference (in some rulesets)

• Issues a warning for insufficient budget

15
CC_ATR_BUD
CC_ATR_ENC
CC_ATR_EXP
Connected to Fund, ALI, Account, Department ID,
Program (in some rulesets), and Budget
Reference (in some rulesets)

• Will stop a transaction for insufficient budget

16
CC_CSH_BUD
CC_CSH_EXP

• There is no ENC ledger because cash does not
  impact encumbrances

17
The CC_CASH Formula
Beginning Cash Balance Revenue Collected
Expenses Available Cash Balance
18
CC_GR1_BUD
CC_GR1_ENC
CC_GR1_EXP
Connected to Fund, Department ID, Grant/Project

• Budget set up for federal grants
• Budget Period Life of the grant
• Can be set up as a control budget

19
CC_PR1_BUD
CC_PR1_ENC
CC_PR1_EXP
Connected to Department ID, Project

• How agencies track expenditures on a capital
  project

20
CC_DET_ENC
CC_DET_EXP
Connected to All ChartFields linked to an
expenditure or encumbrance transaction

• Budget is not tracked
• Tracks at the transaction level

21
CC_MON_BUD
CC_PLN_BUD
CC_PLN_ENC
CC_PLN_EXP

• Used by OBM Budget section
• Limited use for FIN end users

22
Revenue Ledger Types
23
CC_AGY_COL
CC_AGY_EST
CC_AGY_REC
Connected to Fund, Account, Department ID

• All revenue transactions hit this ledger group
• Budget Period 1 month

24
CC_CSH_COL

• Tracks collected cash revenue

25
CC_STA_COL
CC_STA_EST
CC_STA_REC
Connected to Fund, Account

• Essentially the same as CC_AGY_REV, except for
  ChartFields connected to it

26
Revenue Transactions
27
EXCEEDS BUDGET TOLERANCE
Insufficient Budget
28
NO BUDGET EXISTS
ChartField Combination Does Not Exist
29
Control Budget Rulesets
30
Track Budget Rulesets
31
A Simple Budget Check Example
32
(No Transcript)
33
Using Track and Control
PO/EXPENDITURE
This passes budget check! Why?
34
CC Ledgers Updated
35
Expense Appropriation
36
(No Transcript)
37
The Budget Detail Inquiry
Enter only your required ChartFields!
38
The Budget Detail Inquiry
If there are budget exceptions, a link will
appear here
39
The Budget Overview Inquiry
Use the wildcard () or enter specific values
40
The Results Are In!
41
Downloaded to Excel
If you have trouble downloading to Excel, hold
down your CTRL key until Excel opens.
42
The Budget Status Report
Select your ledger group. This will determine
the ChartField values youll have to choose from
43
Setting Parameters
Be sure to check Include CF! Youll get no
data if you put in ChartField values that are not
part of your budget structure!
44
Heres the Report!

• Uses a Run Control when run Ad Hoc
• Can also be retrieved weekly
• You must have a budget security role to run this
  report
• Not for reconciliation against the GL!

45
PO 12/01/2007
PO 12/1/07
46
Fields Updated in LEDGER_KK Table
Date of PO (12/01/2007) converted to Fiscal Year
and Accounting Period
47
Accounting Periods
48
Voucher 12/3/2007
49
LEDGER_KK When Vouchering
The encumbrance is relieved
50
Finished with LEDGER_KK!(on to Voucher Post)
51
What the Journal Generator Does 1

• Assigns a Journal ID to both the VCHR_ACCTG_LINE
  table and the JRNL_HEADER and JRNL_LN tables

Journal Generator
Journal ID
Journal ID
JRNL_HEADER JRNL_LN
52
What the Journal Generator Does 2

• Converts the run date of the Journal Generator
  process to a Fiscal Year and Accounting Period
  for the VCHR_ACCTG_LINE table and the JRNL_HEADER
  and JRNL_LN tables

12/3/07 FY 2008 Accounting Period 6
53
What the Journal Generator Does 3

• Summarizes entries from VCHR_ACCTG_LINE table by
  ChartField and inserts them into the JRNL_HEADER
  and JRNL_LN tables

JRNL_HEADER JRNL_LN
Entries summarized by ChartField
54
What the Journal Generator Does 4

• It creates journals in the Journal Transaction
  Reporting Table, a necessary step to get the
  transaction posted to the Actuals ledger

JOURNALS
55
Converted Dates and Journal IDs
56
Payment Posting
57
Revenue Posting
Commitment Control Ledgers
Entries are made to the appropriate Commitment
Control ledgers. Because we are recognizing
revenue (but not collecting cash) the revenue
ledgers have a suffix of REC
A R U P D A T E
Accounting Entries
INVOICE 12/1/07
An invoice is created and the transaction passes
budget check via AR Update. The date of the
transaction is the date the pending item posted
(Posted Date)
AR Update converts the pending item creation date
to a Fiscal Year and Accounting Period
58
Revenue in LEDGER_KK
59
AR UPDATE Invoice Entries
60
Revenue in Actuals
Date the Journal Generator runs converted to
Fiscal Year and Accounting Period
61
Customer Payment
62
Payment Posting in LEDGER_KK
63
AR UPDATE Payment Entries
Journal Generator
Journal Post Process
AR UPDATE
General Ledger (Actuals)
Journal Entries
Accounting Entries
ITEM_DIST updates for all payment tied to
pending items PAY_MISC_DIST updates for items
NOT tied to pending items
64
Posting Cash to the Actuals Ledger
65
Ledgers in the Database
Well be chatting about these reporting tables!
66
PO Transaction to EPM
Entries copy overnight
67
Fields in the Commitment Control Ledger Reporting
Table (see The Data Dictionary)
68
Voucher Entries to EPM
B U D G E T C H E C K
Accounting Entries
Commitment Control Ledgers
VOUCHER 12/3/07
Entries copy overnight
OAKS FIN LIVE
69
Voucher/Payment Post to EPM
OAKS FIN LIVE
Voucher/ Payment Post Process
Journal Post Process
Journal Generator
General Ledger (Actuals)
Accounting Entries
Journal Entries
Entries Copy Overnight
70
Posting Revenue to EPM
AR UPDAT E
Commitment Control Ledger Reporting Table (EPM)
Commitment Control Ledgers
Accounting Entries
INVOICE 12/1/07
Entries copy overnight
OAKS FIN LIVE
71
AR Update Entries to EPM
72
Customer Payment to EPM
AR UPDATE
Commitment Control Ledgers
Commitment Control Ledger Reporting Table (EPM)
Accounting Entries
CUSTOMER PAYMENT 12/1/07
Entries copy overnight
OAKS FIN LIVE
73
AR Update Entries to EPM
OAKS FIN LIVE
AR UPDATE
Journal Post Process
Journal Generator
General Ledger (Actuals)
Accounting Entries
Journal Entries
Entries Copy Overnight
Receivables Payment Information Reporting Table
(EPM)
Journal Transaction Reporting Table (EPM)
Ledger Balance Reporting Table (EPM)
74
Reporting Scenario 1
A PO is created on 11/30/2007
The voucher is created on 12/01/2007
75
Reporting Scenario 1 (cont.)
Journal Generator runs on 1/3/08
The payment is processed on 1/4/08
76
Why Theres Confusion

• PO date is 11/30 Accounting Period 5
• Voucher date is 12/30 Accounting Period 6
• Journal Generator date is 1/3 Accounting Period
  7
• Payment date is 1/4 Accounting Period 7

77
Reporting Scenario 2
A PO is created on 12/01/2007 but does not pass
budget check until March 1, 2008
The Commitment Control Ledger will reflect the
original date of the PO, not the date it finally
passed budget check!
78
Reporting Scenario 3
I received this deposit on March 31! Why didnt
it show up on my March report??
79
Reporting Scenario 4
How do you find the specific transactions that
make up the first line?
80
What To Do?
Query off of the EPM reporting tables!
81
Step 1 Run a Journal Report in EPM
This will lead you to the Journal IDs for that
specific ChartField combination in that
Accounting Period and Fiscal Year
82
Step 2 Run Transaction-Based Reports from EPM
This will give you the specific transactions!
83
Reporting Scenario 5
Why cant I reconcile the GLS8020 with the
OHGL015 and the OHGL052???
84
The GLS8020 Run for CC_CASH
85
Its all in the timing!
Theyll never reconcile!!
86
Resources
The OBM website http//www.obm.ohio.gov/ The
OAKS homepage http//www.oaks.ohio.gov/ OAKS
Training Online http//oakspmo.ohio.gov/oaks/trai
ning/ The FIN Data Dictionary
http//www.oakspmo.ohio.gov/Extranet/Documents /F
inDataDictionaries/ OAKS_EPM_FIN_MAIN_Reporting_Da
ta_Dictionary.xls The EPM Lab
OAKS.EPMReportLab_at_OAKS.state.oh.us Cognos
Sign-In https//cognos.ohio.gov/cognos8/cgi-bin/
cognosisapi.dll