Integrity Checks and What Matters - PowerPoint PPT Presentation

integrity checks and what matters n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Integrity Checks and What Matters PowerPoint Presentation
Download Presentation
Integrity Checks and What Matters

play fullscreen
1 / 37
Integrity Checks and What Matters
239 Views
Download Presentation
braima
Download Presentation

Integrity Checks and What Matters

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Integrity Checks and What Matters Julie Thompson, ITS September 29, 2011

  2. Agenda • We will be talking about Integrity, Month End, and Year End queries collectively • We’ll review what the queries are showing you • Tips for finding variances • Effect variances can have

  3. 5 Primary Types of Integrity Checks • Summary vs. Detail • Integrity within Commitment Control • (APPROP vs ORG vs DETAIL) • Integrity between Commitment Control and General Ledgers • (DETAIL_EX vs ACTUALS) • Checks for things that should not exist • Checks for things that should exist

  4. Primary Players Tables Summary LEDGER_KK LEDGER APPROP, ORG, DETAIL, PROJ_GRT, PRMST_EXP, PRMST_REV, REVEST Ledger Groups ACTUALS, CAPITAL, GAAP, ENCUMB KK_ACTIVITY_LOG JRNL_LN Detail

  5. Type 1: Summary vs. Detail LEDGER_KK LEDGER KK_ACTIVITY_LOG JRNL_LN Integrity 05: BOR_CHK_KKLEDG_ACTV Compares detail activity in KK_ACTIVITY_LOG to summary amount in LEDGER_KK

  6. Type 1: Summary vs. Detail LEDGER_KK LEDGER KK_ACTIVITY_LOG JRNL_LN Integrity 08: BOR_CHK_LEDG_JRNL Compares detail activity in JRNL_LN to summary amount in LEDGER

  7. Type 1: Summary vs. Detail LEDGER_KK LEDGER KK_BUDGET_LN KK_ACTIVITY_LOG JRNL_LN Integrity 06: BOR_CHK_KKLEDG_BUD Compares detail activity in KK_BUDGET_LN with summary amount in LEDGER_KK

  8. Summary vs Detail data example: Integrity Check 05: BOR_CHK_KKLEDG_ACTV LEDGER_KK KK_ACTIVITY_LOG

  9. Summary vs Detail data example: Integrity Check 08: BOR_CHK_LEDG_JRNL LEDGER JRNL_LN

  10. Type 1: Summary vs. Detail • These queries should never have a variance • A DBI is usually, but not always required to correct • Update LEDGER_KK or LEDGER to reflect correct activity, or • Clean bad data out of KK_ACTIVITY_LOG or JRNL_LN, or • Can sometimes resolve functionally by clearing Budget Check Exception

  11. Type 2: Integrity within Commitment Control APPROP ORG DETAIL PRMST_EXP PRMST_REV PROJ_GRT REVEST LEDGER_KK Integrity 01: BOR_CHK_PROJ_ENC_LEG - Encumbrances between PRMST, PROJ_GRT, and DETAIL Integrity 04: BOR_CHK_UNREST_LEDGER - Encumbrances and Expense between APPROP, ORG, and DETAIL Integrity 10: BOR_CHK_REVAPPROP_BUD - Budgets between REVEST and APPROP

  12. Integrity within Commitment Control data examples: Integrity Check 01: BOR_CHK_PROJ_ENC_LEG LEDGER_KK

  13. Type 2: Integrity within Commitment Control • These queries should never have variances since all ledgers should be updated at the same time upon a valid budget check • Corrective Action is usually a journal entry directly to Commitment Control • If the variance is for a Project ledger, we will usually opt to rebuild the project ledgers because many schools still have residual variances from 8.9 upgrade

  14. So, how do you find these variances? • For variances in the Summary vs Detail and Integrity within Commitment Control queries find the accounting period where the variance exists • Then use a detail query to identify the specific transaction(s)

  15. Finding variances … Integrity Check 05: BOR_CHK_KKLEDG_ACTV

  16. Finding variances …

  17. Finding variances … Integrity Check 05: BOR_CHK_KKLEDG_ACTV

  18. Finding variances … • Once you know the accounting period, you can use the BOR_KK_ACTIVITY query to identify the transaction • You may want to tweak it a bit. I recommend adding a Business Unit prompt (improves performance), and changing the LEDGER_GROUP = ‘DETAIL’ criteria to LEDGER = prompt

  19. Finding variances …

  20. So now what? • Once you’ve found the transaction, check for any budget errors. If they exist, clear them. • Even if the transaction does not show as having a Budget Error, re-budget checking can sometimes clear the issue (may need to trick the system to reset the budget flags) • If the variance persists, submit a ticket to ITS

  21. Type 3: Integrity between Commitment Control and GL Ledgers LEDGER_KK LEDGER Integrity 02: BOR_CHK_PROJ_EXP_LEG - Expense between PRMST, PROJ_GRT, DETAIL, and ACTUALS Integrity 03: BOR_CHK_PROJ_REV_LEG - Revenue between PRMST, DETAIL, and ACTUALS Integrity 07: BOR_CHK_DETL_ACTLS - Expense between DETAIL and ACTUALS

  22. Type 3: Integrity between Commitment Control and GL Ledgers LEDGER_KK LEDGER Integrity 09: BOR_CHK_REV_LEDGER - Revenue between REVEST, DETAIL, and ACTUALS Integrity 11: BOR_CHK_DTL_ENC - “Encumbrance” between DETAIL and ENCUMB - Corrective Action to re-run ENCUMB ledger build - ENCUMB ledger is not closed and can be rebuilt at any time

  23. Integrity between KK and GL Ledger data example: Integrity 03: BOR_CHK_PROJ_REV_LEG LEDGER_KK LEDGER_KK LEDGER

  24. Integrity between KK and GL Ledger data example: Integrity 07: BOR_CHK_DETL_ACTLS LEDGER_KK LEDGER

  25. Type 3: Integrity between Commitment Control and GL Ledgers • Variances between Commitment Control and ACTUALS very common • Timing Issue - Any transaction that has been budget checked and not posted will be a variance • If running throughout year, monitor for lingering variances • Will probably only be clear at Fiscal Year End • If “true” variance, then journals to Commitment Control are usually needed

  26. Finding variances… • 3 primary sources of variances: • Vouchers budget checked, but not yet posted (BOR_AP_UNPOSTED_VCHR) • Expense Reports budget checked, but not yet posted (BOR_EX_UNPOSTED_ACCRUALS) • Expense Reports posting for different amount than budget checked

  27. Finding variances… • “Known Issue” with Expense Reports not re-budget checking when mileage is changed • Workaround: uncheck/recheck “approve expense” box • If you are an Expense user, add BOR_KK_EX_ER_RECON to your month end list of queries to run • This query will return any Expense Reports budget checked for a different amount than posted for • Relatively easy DBI to fix if caught before Expense Reports are closed

  28. Type 4: Checks for Things that ShouldNotExist • Month End 01: BOR_CHK_JE_PENDING - Returns transactions not journal generated or journals with errors • Month End 02: BOR_CHK_UNPOST_SUBSYSTEM - Returns any subsystem journal that has been unposted (ability to unpost subsystem journals has been removed) - Will cause FDM errors

  29. Type 4: Checks for Things that ShouldNotExist • Month End 03: BOR_CHK_CASH_ENCUMB - Returns cash encumbrances (ITS can run utility to delete them) - Coming from Encumbrance journals on which the ‘DEFAULT’ transaction code is used. Can be either Payroll Encumbrance process of manual Encumbrance journals - Will cause your BOR_BTA_ENCUMB_PAYABLE query to be out - Will cause FDM editor errors for period 0

  30. Type 4: Checks for Things that ShouldNotExist • Month End 04: BOR_CHK_REST_WOUT_PROJ - Fund 20000 transactions without a Project • Month End 05: BOR_CHK_ERROR_ENCUMB - Encumbrances in Revenue or Balance Sheet accounts • Month End 06: BOR_CHK_NEG_ENCUMB - Negative Encumbrances

  31. Type 4: Checks for Things that ShouldNotExist • Year End 01: BOR_CHK_12000_BALANCES • Balances in Fund 12000 (inactive fund) • Year End 02: BOR_CHK_FDM_CAPITAL_CF • Returns CAPITAL transactions where CFs are missing and Fund Code not equal to 52000 • Year End 04: BOR_CHK_PERSERV_ENCUM • Returns Personal Services Encumbrances (only “shouldn’t exist” if YE and Zero Personal Services process has been run) • Integrity 12: BOR_CHK_BUD_REF_FISCAL_YEAR - Returns transactions with Budget Ref greater than FY entered

  32. Type 5: Checks for Things that ShouldExist • Year End 03: BOR_CHK_PERIOD0_POPULATED - Ensure that ACTUALS, CAPITAL, GAAP, DETAIL_EN, and ENCUMB have beginning balances • Year End 05: BOR_NET_ASSET_ACCOUNT - Returns beginning balances for Net Asset accounts

  33. Why do we care? • Variances undermine the intent of Commitment Control • If you have Encumbrances or Expenses recorded in KK_ACTIVITY_LOG, but not LEDGER, transactions may pass budget checking that shouldn’t • If you have Encumbrances or Expenses recorded in DETAIL, but not APPROP, transactions may pass budget checking that shouldn’t, since we control at APPROP

  34. Why do we care? • Can cause overspending or underspending • Year End Reporting of Expenses does come from ACTUALS, but if you’re spending based on KK, then your numbers may not be what you expected • Causes inaccurate reporting to your departments

  35. Why do we care? • Encumbrance errors can affect your ENCUMB ledger which affects the BTA_ENCUMB_PAYABLE query and the Budgetary Compliance Report • Encumbrance errors will affect your Surplus/Deficit reporting • Bad data can cause FDM editor errors

  36. Wrap-Up • Run Summary vs Detail, Integrity within Commitment Control, and Checks for things that shouldn’t exist throughout the year, don’t wait until Year End • Begin the hunt for variances by adding accounting period to queries • You may still need ITS to find or correct the variance

  37. Questions