Controlled Budget Corrections

1
Controlled Budget Corrections
(The What, Why How of Budget Corrections and
Synchronization)

• Session 306
• Mon. March 17, 2003 345-445
• HEUG 2003 Conference Dallas, TX

2
Presenter Information
Patrick Wells
Sr. Application Specialist (Lead Developer)
Kentucky Community and Technical College System
Patrick.Wells_at_KCTCS.EDU
3
Presenter Background

• PeopleSoft Technical Developer for over 5 years.
• Experience Implementing and/or Upgrading the
  following Modules AM, AP, AR/BI, GL, GM, PO,
  Projects/Grants
• Currently on Version 7.52 EG (PeopleTools 7.62)
  Plus Several Hundred More Fixes

4
KCTCS Information

• KCTCS is Comprised of 16 Districts with 25
  Colleges
• 62 Campuses Open or Under Construction

5
KCTCS Information

• 68,800 Students in Credit Courses
• 69,000 Students in Fire Service, Rescue
  Training
• 3,800 Full-Time Faculty, Staff and
  Administrators
• 449 Million Budget

6
PeopleSoft Financials Information

• 32 Business Units
• 700 Financials Users
• Budgets Appropriation, Organization,
  Project/Grant and
• Revenue Estimate
• 10,000 Controlled Budget Chart Strings/Year
  (Ledger_Budg)
• 250,000 Budget Transactions/Year
  (Bcm_Trans_Dtl)

7
The What, Why and How
Controlled Budget Corrections

• What happened to the budget? Its wrong?!
• Why does this happen (some limited insight)
• How to find and figure out the problem Dig, Dig,
  Dig. Also, some SQL that may make it a little
  easier.
• And fix it (with a bolt-on application of course,
  or SQL for the masochists).

8
WHAT is going on(Symptoms of Problem)

• Examination of budget reveals inconsistencies.
• Negative encumbrances pre-encumbrances
• Double of amounts (mainly encumbrances and
  pre-encumbrances)

• Processes fail or arent allowed due to budget
  constraints
• Voucher wont budget-check because PO over
  liquidated
• Requisitions, Purchase Orders or Vouchers fail
  budget-check

9
More Problems - Analyze Controlled Budgets
10
More Problems - Purchasing

• 1. Double Amounts, Double Reversals or No
  Amounts
• During PORECON or Other Process, Amounts Get
  Doubled.
• During PO Sourcing, Encumbrances dont get
  Populated.

• 2. Another type of problem is with Stuck
  Encumbrances
• PO is in Status that should relieve the
  Encumbrance, but PORECON will not release the .
  (PO Completed, Cancelled, Closed)
• PO Wont Close or Complete, but it should.

11
More Problems - General

• Budget Doesnt Get Processed at all.
• (This is usually a Setup Error in
  Budgetary Control)

2. Ledger vs. Budget (Out of Sync) Delivered
Report Budgets/Actuals Reconciliation -
GLS8010 (SQR) This can help to identify problems,
but may lead you where there are no real issues,
can be very large and doesnt show encumbrances
or pre encumbrances.
12
WHY Does this Happen (Limited Insight)

• Setup Error in Budget Control
• New Account not setup for Budget Control
• Account had Budget Control turned off (security)
• Project Not Setup for Budget Control

• PeopleSoft Delivered
• Design does not take into account all
  permutations
• New Fix breaks something else (Limited Testing)

13
HOW TO FIND (This is the Hard Part)

• Analyze Controlled Budget Panels
• The Summary Budget Page can be somewhat helpful
• The Transaction Detail can be even more helpful
• But can be very time consuming
• May not be able to readily see the problem.

• SQL against BCM_TRANS_DTL (Budget Transaction
  Detail) LEDGER_BUDG (Budget Summaries) Tables

14
HOW TO FIND (SQL for the Die-Hard)

• SQL (The Matrix Phenomenon?)
• Very Powerful and Flexible
• Better able to see and analyze problems
• But, you have to know what to look at where to
  begin
• You must have a Database Login and SQL Tool

NEXT Some SQL Example of Problem ?
15
Some SQL (Requisitions)

• SELECT BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, REQ_ID,
  SUM(PRE_ENCUM_AMOUNT)
• FROM PS_BCM_TRANS_DTL
• WHERE BUSINESS_UNIT '41200'
• AND PROJECT_ID 'GRT42277'
• AND FISCAL_YEAR ltgt '9999'
• AND BUDGET_PERIOD 'ALLPER'
• AND JOURNAL_ID ' '
• GROUP BY BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, REQ_ID
• HAVING SUM(PRE_ENCUM_AMOUNT) ltgt 0
• ORDER BY REQ_ID

Results
16
Remember this from Earlier!!
17
More SQL (Req - Detail)

• SELECT BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, BCM_POST_DATE,
  FISCAL_YEAR,
• ACCOUNTING_PERIOD, BUSINESS_UNIT_APPL,
  JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ,
  JOURNAL_LINE, VOUCHER_ID, VOUCHER_LINE_NUM,
  PO_ID, REQ_ID, LINE_NBR,
• SCHED_NBR, DISTRIB_LINE_NUM, PRE_ENCUM_AMOUNT,
  ENCUMBERED_AMOUNT, EXPENDED_AMOUNT,
  RECOGNIZED_AMOUNT, COLLECTED_AMOUNT,
  PROCESS_INSTANCE
• FROM PS_BCM_TRANS_DTL
• WHERE BUSINESS_UNIT '41200
• AND PROJECT_ID 'GRT42277'
• AND FISCAL_YEAR ltgt '9999'
• AND REQ_ID '0000000688'
• ORDER BY BCM_POST_DATE,REQ_ID, PO_ID, VOUCHER_ID

Results
18
HOW TO FIX (The Bolt-On Customization)

• Features of Bolt-On
• Very, Very Powerful and Flexible
• Similar in Function to Analyze Controlled Budgets
• Direct Alters of Budget Information
• Keeps Transaction Detail in Sync (Optional)
• Creates Logs of Budget Transactions

OK, so how does it work ?
19
The Bolt-On
20
Bolt-On Processing
21
Now Back to Analyze Controlled Budgets
22
Transaction Detail
23
Back To SQL (Requisitions)

• SELECT BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, REQ_ID,
  SUM(PRE_ENCUM_AMOUNT)
• FROM PS_BCM_TRANS_DTL
• WHERE BUSINESS_UNIT '41200'
• AND PROJECT_ID 'GRT42277'
• AND FISCAL_YEAR ltgt '9999'
• AND BUDGET_PERIOD 'ALLPER'
• AND JOURNAL_ID ' '
• GROUP BY BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, REQ_ID
• HAVING SUM(PRE_ENCUM_AMOUNT) ltgt 0
• ORDER BY REQ_ID

Results
Line for Requisition 0000000688 is now gone!
24
Back to SQL (Req - Detail)

• SELECT BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, BCM_POST_DATE,
  FISCAL_YEAR,
• ACCOUNTING_PERIOD, BUSINESS_UNIT_APPL,
  JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ,
  JOURNAL_LINE, VOUCHER_ID, VOUCHER_LINE_NUM,
  PO_ID, REQ_ID, LINE_NBR,
• SCHED_NBR, DISTRIB_LINE_NUM, PRE_ENCUM_AMOUNT,
  ENCUMBERED_AMOUNT, EXPENDED_AMOUNT,
  RECOGNIZED_AMOUNT, COLLECTED_AMOUNT,
  PROCESS_INSTANCE
• FROM PS_BCM_TRANS_DTL
• WHERE BUSINESS_UNIT '41200
• AND PROJECT_ID 'GRT42277'
• AND FISCAL_YEAR ltgt '9999'
• AND REQ_ID '0000000688'
• ORDER BY BCM_POST_DATE,REQ_ID, PO_ID, VOUCHER_ID

Results
25
More SQL Slides

• The Next Slides are of additional SQL which may
  help with further analysis.

26
More SQL (Req Entire Business Unit)

• SELECT BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, REQ_ID,
  SUM(PRE_ENCUM_AMOUNT)
• FROM PS_BCM_TRANS_DTL
• WHERE BUSINESS_UNIT '41200'
• AND FISCAL_YEAR ltgt '9999'
• AND BUDGET_PERIOD IN (2003, 'ALLPER)
• AND BUDGET_TYPE IN ('PR','OR')
• AND JOURNAL_ID ' '
• GROUP BY BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, REQ_ID
• HAVING SUM(PRE_ENCUM_AMOUNT) ltgt 0
• ORDER BY REQ_ID

Results
27
More SQL (Purchase Orders)

• SELECT BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, PO_ID,
  SUM(ENCUMBERED_AMOUNT)
• FROM PS_BCM_TRANS_DTL
• WHERE BUSINESS_UNIT '41200'
• AND FISCAL_YEAR ltgt '9999'
• AND BUDGET_PERIOD 'ALLPER'
• AND JOURNAL_ID ' '
• GROUP BY BUSINESS_UNIT, BUDGET_TYPE, ACCOUNT,
  FUND_CODE, DEPTID, PROGRAM_CODE, CLASS_FLD,
  BUDGET_PERIOD, PROJECT_ID, PO_ID
• HAVING SUM(ENCUMBERED_AMOUNT) ltgt 0
• ORDER BY PO_ID

Results
28
More SQL
Projects/Grants not Setup with Budget Control

• SELECT A.PG_BUDGET_REQUIRED, A.
• FROM PS_PROJECT_HEADER A
• WHERE A.PG_BUDGET_REQUIRED 'N'
• OR A.PROJ_BUD_CNTL IS NULL
• ORDER BY 3

29
In Conclusion
There is no Silver Bullet, but these tools have
helped us immensely in terms of ability to
resolve issues quickly and easily.Patrick Wells
- Sr. Application Specialist (Lead
Developer)Kentucky Community and Technical
College SystemPatrick.Wells_at_KCTCS.EDUHTTP//hi
gheredsig.cua.edu/ (attendees may download
HEUG2003 presentations from the archives at this
location)
30
Download Option2
You may download this Presentation and other
Documentation _at_
http//unity.kctcs.edu/ dscgi/ds.py/View/Collectio
n-3443