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

1

Managing Transactions Using Queries

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

3

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.

Objectives

4

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

Why run these queries?

5

• 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

Transaction Process steps: Create to Post

6

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

Queries for Managing Transactions

7

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

Query Viewer

8

• 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.

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 411100 thru 411900

Query Viewer

• Navigation:

Reporting Tools > Finance Menu > 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

Voucher and Line

• Online = combo edit error (invalid or

missing chartfields)

• AP_MATCH = match error
• APVCHREDIT = various edit errors

(amount, combo edit, accounting date) Number of lines in error The chartfields within the combo edit rule that failed Indicates the system was the last “user”

Combo Edit Errors

16

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 AF_VI Rule 1 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 ACCOUNT/ FUND_CODE Rule 2 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 SAP_RQ_VV Rule 1 The Source/Account combination requires a specific Program code. Source/Program/Account CLASS_FLD/ PROGRAM_CODE/ ACCOU Rule 2 The Source/Account combination requires the Program field to be entered (not be left blank). Rule 3 The Source/Account combination requires the Program field be blank.

Combo Edit Errors

17

Comb Edit Rule Description Message field in query

AF_UNIT Foundation business units can only use these funds: 29900, 29910, 29920 & 29930. Account/Fund FS_CHASF/FS_CHATH… Foundation business units require a source code unique to the business unit. For example, CHASF can only use Sources that start with a 'C‘. Fund/Source

Comb Edit Rule Description Message field in query P1_ATH Requires a valid Program/Cost Code 1 combination for Athletics Program/Cost Code SD_ATH_IV Requires a valid Source/Department combination for Athletics. Source/Department Comb Edit Rule Description Message field in query SF_VI_BO Budget Only Source/Fund combinations cannot be entered

• n Data Collect Batch files.

Source/Fund DF_VI Budget Only Dept/Fund combinations cannot be entered

• n Data Collect Batch files.

Dept/Fund AF_VI* Falls under the same AF_VI rule above. Occurs when a Budget Only account is entered on the file. Budget Only accounts are not allowed. Account/Fund

Combo Edits for Data Collect Batches only Combo Edits for Athletics only Combo Edits when a foundation Business Unit is used

Budget queries: key fields

Voucher ID Ledger Group Error message Person who created the voucher Where the voucher originated

Budget Errors

19

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 Error Description E1 Exceeds budget and is over tolerance E2 No budget exists E3 Budget closed E6 Budget date is out of bounds Most common

Unposted journals: key fields

20

• 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 Posted status, so V, E, or N Current approval status and most recent approver

Approval Worklist queries

21

Approver ID

• 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

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

23

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 a candidate for deletion, so you have an

• pportunity to fix it.

Campus Journals 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.

Month End Close

system Campus Depts. Central Office

All subsystems close for new entries at a time specified by Accounting Services. 1 15 31 1 5

current month new month Objectives:

• No edit errors
• No budget errors
• No transactions hung up in approvals

Manage transactions and fix errors throughout the month

24

Queries for Managing Transactions

25

Navigation to individual transactions Requisitions: Main Menu > Finance Menu> eProcurement > Manage Requisitions Purchase Orders: Main Menu > Finance Menu > Purchasing > Purchase Orders > Review PO Information > Purchase Orders Vouchers: Main Menu > Finance Menu > Accounts Payable > Review Accounts Payable Info > Vouchers > Voucher Deposits: Main Menu > Accounts Receivable > Payments > Online Payments > Regular Deposit Budget transfers/journals: Main Menu > Finance Menu > Commitment Control > Budget Journals > Enter Budget Journals/Enter Budget Transfers Campus Journals: Main Menu > UNC Campus > Campus Journals > Campus Journal Validation

Queries for Chartfield Attributes

26

Description Query Attributes for any chartfield NC_GL_ATTRIBUTE_LIST

• Shows all attributes for a chartfield

Queries for Chartfield Attributes

27

Search fields

• SetID – enter uncch or uncga
• Field Name - enter one of the

following:

• Account
• Dept ID
• Class_FLD (This is the Source chartfield.)
• Fund_Code
• Attribute and Chartfield – use

wildcard or a value if you know it

• No fields can be blank

Summary

28

• Queries for:
• Edit errors
• Budget errors
• Unposted journals
• Unapproved transactions
• Chartfield attributes
• Focus on key fields when looking at query results
• Be familiar with the chartfields in each combo edit rule,

and the common budget errors

• Run queries often to catch errors and things not

approved or posted

• If you run a query and there are no results, clear the

search fields and run it again to make sure

Cash Advance Reference information

• Finance Training Website

http://ccinfo.unc.edu/training/

• Recording of the webinar:
• Managing Transaction Using Query Viewer
• Resource document:
• Managing Transactions Using Query Viewer

29

Reference Document on ccinfo

30

• Reference document lists all query fields and definitions
• Key fields to focus on are highlighted in yellow

31

Q&A

32

Thank You!

Combo Edit Errors

33

• AFSD_IV
• AF_VI
• FAJ_VV
• FS_IV
• SAP_RQ_VV
• Others for Foundations, Athletics, and

Data Collect Batches