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

on Data Collect Batch files. Source/Fund

DF_VI Budget Only Dept/Fund combinations cannot be entered on 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 opportunity 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