DATA IN

1
DATA INDATA OUT

• Vanessa Peoples
• Office of the Vice Provost for Research
  Graduate Studies
• Amy Connell
• General Accounting

2
OBJECTIVES

• Review Data Structure
• Reporting Tools
• Web Statement
• EZQ
• GMQ
• Wheres My Cash?

3
DATA STRUCTURE

• Speedtype
• Usually mirrors purpose
• Auto populates chartfields
• Fund identifies the type of UD funding to which
  a certain purpose belongs implies a set of
  budgetary and accounting rules that pertain to
  those purposes (eg. OPBAL Operating Balance
  OPBAS Operating Basic Budget)
• Department - identifies the administrative budget
  unit responsible for the purpose

4
DATA STRUCTURE

• Program - identifies the origination of revenue
  or the destination of expenses (eg. INST1
  Instruction)
• Project ID - identifies contract and grant
  activities and facilities projects (eg.
  ARTC37211199000)
• Purpose - identifies a discrete block of money
  used to make expenditures, collect revenue, or
  track assets and liabilities (eg. PLSC42XXXX)
• Source - tags and tracks contract and grant
  agency information, and capital projects (eg.
  33041101 Office of Education)

5
(No Transcript)
6
REPORTING FLOWCHART
Grant transactions happen....
General Ledger tables updated in FIPRD
Grants Management tables updated in FIPRD
FIPRD tables copied to FIRPT Query tables/views
overnight

• Grants Management data found in
• UOD_PROJ_RES_VW UOD_PRJ_VW UOD_PRP_ATRB_VW
  UOD_PRP_BUD_VWUOD_AWD_ATRB_VW UOD_PROJ_FUND_VW
  
• GMQs

• General Ledger data found in
• UOD_BALANCES
• UOD_TRANS_DTL
• EZQs

7
EZQs

• Row level security by purpose
• Provides purpose fund balances and transaction
  details
• Purpose is king
• 2 main tables
• UOD_TRANS_DTL
• UOD_BALANCES

8
GMQs

• Row level security by DeptID or purpose
• Provides project balances, project transaction
  details, proposal and award data
• Project is king

9
GMQs

• Created by combining most commonly used grants
  tables
• 6 main tables
• UOD_AWD_ATRB_VW
• UOD_PRP_BUD_VW
• UOD_PBUD_DTL_VW
• UOD_PRJ_VW
• UOD_PRJ_RES_VW
• UOD_PROJ_FUND_VW

10
WHY IS PROJECT IMPORTANT?

• All grant transactions MUST have a project id
• Grant billing is based on project id
• Revenue recognition is based on project id
• FA is based on project Id

11
WHY IS PROJECT IMPORTANT?

• Non grant purpose with a grant project will be
  treated as cost share
• Future of the journal voucher webform

12
JV WEBFORM(Current)
13
JV WEBFORM(Future)
14
WEB STATEMENT(YTD)
15
WEB STATEMENT(PTD)
16
WEB STATEMENT(TRANS)
17
WHICH QUERY SHOULD I USE?

• Balances
• EZQ_BAL_PROJ_QUERY
• EZQ_BAL_PROJ_SUMMARY
• EZQ_BAL_PROJ_TOTAL
• GMQ_CLOSE_OUT_REPORT_BAL

18
EZQ - BALANCES
19
EZQ - BALANCES
20
EZQ vs GMQ
21
WHY ARE THERE DISCREPANCIES?

• FA
• Updated in project resources nightly on every
  transaction
• Updated in GL once at month end close
• Purpose/project error

22
WHICH QUERY SHOULD I USE?

• Transactions
• EZQ_TRANS_ALL
• EZQ_TRANS_BUDGET_QUERY
• EZQ_TRANS_NONBUDGETPURP
• GMQ_TRANSACTIONS

23
GMQ_TRANSACTIONS
24
WHERES MY CASH?

• Legacy system grant revenue recognized when cash
  was received
• PS grant revenue is recognized when expenses are
  incurred

25
REVENUE RECOGNITION
26
WHERES MY CASH?

• 3 GMQ queries provide the total cash for a
  project or contract
• GMQ_CASH_THRU_2002
• Total Cash as of 7/1/02
• Prompt purpose
• GMQ_CASH_NOT_PS_BILLED
• Cash rcvd outside of PS billing
• Prompt purpose
• GMQ_CASH_PS_BILLED
• Sum of PS billing activity
• Prompt Award number

27
GMQ_CASH_THRU_2002
28
GMQ_CASH_NOT_PS_BILL
29
GMQ_CASH_PS_BILLED
30
QUESTIONS???????