Managing Transactions Using Queries 1 Todays Webinar Webinar - PowerPoint PPT Presentation

Managing Transactions Using Queries 1

Today’s Webinar Webinar format • Presentation, followed by Q&A • Use the chat window to type your questions • We will answer them in the order they were received • during the Q&A segment The webinar recording will be posted on ccinfo.unc.edu • 2

Purpose The purpose of this webinar is to introduce a set of queries you can use to manage transactions that are in error or in unposted status. 3

Objectives This webinar covers the following topics: • Why – why run these queries • What – what queries are available • How: − How to run a query − How to quickly scan query results • How often – how often should you run queries 4

Why run these queries? • To identify transactions with your Department ID that: − Have edit or budget errors − Have not been approved − Have not been posted • To avoid having Accounting Services delete or post your unposted journal entries at Month End 5

Transaction Process steps: Create to Post Create Edit Budget Check transaction Process Process Post in Post to Approval subsystem General Process (AP, AR, PO) Ledger 6

Queries for Managing Transactions Transaction Query Requisitions NC_REQ_EDIT_ERRORS Edit error queries Purchase Orders NC_PO_EDIT_ERRORS Vouchers NC_AP_EDIT_ERRORS_VOUCHERS_DPT Deposits NC_AR_EDIT_ERROR Campus Journals (GL) NC_GL_JOURNAL_EDIT_ERRORS_DEPT Budget error queries Requisitions NC_KK_ERRORS_REQ_DEPT Purchase Orders NC_KK_ERRORS_PO_DEPT Vouchers NC_KK_ERRORS_VOUCHERS_DEPT Deposits NC_KK_ERRORS_AR_MISCPAY Campus Journals (GL) NC_KK_ERRORS_JOURNALS_DEPT Budget Journals NC_KK_ERRORS_BUD_JRNLS Payroll NC_KK_ERRORS_PAYROLL Requisitions NC_REQ_APPROVAL Approval Worklist queries Vouchers NC_VCHR_APPROVAL Campus Journals (GL) NC_JRNL_APPROVAL GL Journals not in NC_GL_JRNL_NOT_POSTED_DEPT1 Posted status 7

Query Viewer • Tool for viewing queries Campus can run any public queries that reference • secured tables • Note! Only use the queries listed on the previous page. They have been specifically designed for campus. 8

Wildcard • The % sign is a wildcard search criteria You can put the % sign before, after, or in between a • search value Examples: • − 3% - returns all values that begin with 3 − %3 - returns all values that end with 3 − %3% - returns all values that contain 3 • Putting the % sign by itself in a search field returns all values in that field

Underscore • The underscore sign (_) is a wildcard for a specific position • It is typically used when you know some but not all values • Examples: – 4114 _ returns all departments that start with 4114 – 411 _ 00 returns all departments from 411 1 00 thru 411 9 00

Query Viewer • Navigation: Finance Menu > Reporting Tools > Main Menu > Query > Query Viewer

Query Viewer 1. Enter the name of the query. • You can enter part of the name. Query Viewer searches for queries that begin with the values you enter. • Examples: − NC returns all queries that begin with NC − NC_AP_EDIT returns all queries that begin with NC_AP_EDIT 2. Click Search.

Query Viewer 3. Click the HTML link for the query you want to view. Note! You will see other queries besides the one listed on slide – 7. Only use the ones on slide 7. The other ones may not be complete or useable. – The Excel link opens the query directly to Excel – The XML link is not useful for these queries – The Schedule link is not available – The Favorite link saves the query to a Favorites list within Query Viewer

Query Viewer 4. Enter uncch or uncga in the Unit field. – For GL Journals, the Business Unit is uncch, uncga, or a foundation business unit. For all other transactions, the Business Unit is uncch or uncga. – Business Unit is required. – The wildcard cannot be used for the Business Unit. 5. Enter a department, or a partial department using the wildcard or underscore. The Dept field cannot be blank. – 6. Click View Results.

Edit queries: key fields • Online = combo edit error (invalid or missing chartfields) • AP_MATCH = match error • APVCHREDIT = various edit errors Voucher and (amount, combo edit, accounting date) Line The chartfields within the combo edit rule that failed Number of lines in error Indicates the system was the last “user”

Combo Edit Errors Comb Edit Rule Description Message field in query AFSD_IV Account, Fund, Source, and Department are required on every line. Account/Fund/Source/Dept ACCOUNT/ CLASS_FLD/ DEPTID/ FU Rule 1 AF_VI Revenue on State funds and all Expense lines must have a fund with a Purpose code. The Fund cannot end in “00”, such as 20100 and 21100. Account/Fund Rule 2 ACCOUNT/ FUND_CODE Fund codes 29900, 29910, 29920, or 29930 cannot be used with Business Unit UNCCH. A foundation Business Unit must be used with these funds. FAJ_VV Lines with OSR or Capital Improvement fund codes must have a Project ID. Fund/Account/Project ID FUND_CODE/ PROJECT_ID/ ACCOUNT FS_IV Fund and Source combination must be valid. Fund/Source FUND_CODE/CLASS_FLD Rule 1 SAP_RQ_VV The Source/Account combination requires a specific Program code. Source/Program/Account Rule 2 The Source/Account combination requires the Program field to be entered CLASS_FLD/ PROGRAM_CODE/ (not be left blank). ACCOU Rule 3 The Source/Account combination requires the Program field be blank. 16

Combo Edit Errors Combo Edits when a foundation Business Unit is used Comb Edit Rule Description Message field in query AF_UNIT Foundation business units can only use these funds: 29900, Account/Fund 29910, 29920 & 29930. FS_CHASF/FS_CHATH… Foundation business units require a source code unique to the Fund/Source business unit. For example, CHASF can only use Sources that start with a 'C‘. Combo Edits for Data Collect Batches only Comb Edit Rule Description Message field in query SF_VI_BO Budget Only Source/Fund combinations cannot be entered Source/Fund on Data Collect Batch files. DF_VI Budget Only Dept/Fund combinations cannot be entered Dept/Fund on Data Collect Batch files. AF_VI* Occurs when a Budget Only account is entered on the file. Account/Fund Falls under the same AF_VI Budget Only accounts are not allowed. rule above. Combo Edits for Athletics only Comb Edit Rule Description Message field in query P1_ATH Requires a valid Program/Cost Code 1 combination for Program/Cost Code Athletics SD_ATH_IV Requires a valid Source/Department combination for Source/Department Athletics. 17

Budget queries: key fields Ledger Group Voucher ID Error message Where the voucher originated Person who created the voucher

Budget Errors Most common Error Description E1 Exceeds budget and is over tolerance E2 No budget exists E3 Budget closed E6 Budget date is out of bounds Error Description E35 Required key CF (chartfield) is blank E36 Chartfield combination errors exist E45 Non key CF (chartfield) has a value E48 Chartfield value not at Tree Level E49 Key Chartfield is blank E64 Parent budget does not exist E91 Translation Tree Error 19

Unposted journals: key fields • Period is by fiscal year month. 1 = July, 2 = August, and so on through 12 = June • No fields can be blank Returns anything not in Current approval status Posted status, so V, E, or N and most recent approver 20

Approval Worklist queries • Shows transactions currently in a Worklist • If a transaction has been approved or denied, it will not appear on this query • The approval queries are keyed by approver ID. The approver must be logged in as himself in order for transactions to display. • You can only view one person’s Worklist at a time Approver ID 21

How often should you run these queries? • Frequently! Daily is not too often. • Some transaction types are more likely to have errors because of the way they are processed: • Check daily: • Accounts Payable • Campus Journals • Check at least weekly: • Requisitions • Purchase orders • Deposits • Budget journals and transfers 22

Month End Close At month end, Accounting Services treats finance transactions per the table below: Finance Transaction What happens at end of month Budget journals/transfers Accounting Services deletes or posts. Note: You will receive an email if a journal is Campus Journals a candidate for deletion, so you have an opportunity to fix it. AP vouchers Accounting Services rolls forward to the next month. The Accounting Date is changed to the new month. Requisitions and purchase orders Posted in a future month, whenever they are processed. Deposits and billing entries Posted in current month. 23

Month End Close Manage transactions and fix errors throughout the month Campus Objectives: Depts. • No edit errors • No budget errors • No transactions hung up in approvals All subsystems close for new entries at a time specified by system Accounting Services. Central Office 1 15 31 1 5 current month new month 24