KFS General Ledger Query

This guide is specific to the DWQuery KFS-GL. For a generic guide on how to use DWQuery, see the Using DWQuery guide.

DWQuery KFS-GL Tabs

The DWQuery-KFS GL is divided into 6 different tabs at the top of the page. Each tab allows you to select different sets of criteria to filter your data on. Selecting a checkbox next to a field allows you to select what columns you want returned on your query.

Tab	Description
Context	This tab allows users to select the Fiscal Year and Period context of your query. Users can also query across multiple Fiscal Years up to 2015.
Campus Hierarchy	This tab has filters for Organizations that KFS Accounts belong to.
Full Accounting Unit	This tab has filters for key values that are part of the KFS Full Accounting Unit. This includes KFS Account, KFS Control Account, UC Fund, and Object Codes.
Additional Attributes	The Additional Attributes tab contains additional attributes for the KFS Account and KFS Project Codes that can help narrow down queries.
Ledger Detail	The Ledger Detail tab contains filters for attributes on the General Ledger. For example, users can filter on Document Number or GEC Document Number.
Amounts	This tab will allows users to select what entry amount columns to return or to to filter by on the amounts. Each column selected will add an additional column that represents the balance type. Entries represent the value regardless of balance type, and will show debits as positive and credits as negative.

Context Tab

The Context Tab allows users to choose the fiscal year and fiscal period. The Multi-Year Selected button allows you to build queries for multiple fiscal years. See the Understanding Fiscal Years and Fiscal Periods Guide for more information.

Selecting Multiple Fiscal Periods

Hold the Shift key down to select multiple adjacent Fiscal Periods at once or hold the CTRL key to select multiple non-adjacent individual Fiscal Periods at once.

Creating Multi-Year Queries

To create a Multi-Year query, click on the Multi-Year Select toggle.

When selecting from July, Fiscal Year 2015 to June Period 12, Fiscal Year 2016, DWQuery will return transactions from July 2015 to June 2016 across both Fiscal Years.

Campus Hierarchy Tab

KFS Org Rollup Level

Organizations in KFS are defined by a 4 character code. Each Organization Code can report to another Organization, creating a hierarchy. Rollup Organizations are Organizations that other Organizations report to.

For more information on Organizations and how the hierarchy works see our Chart of Accounts Organizations page.

Org Rollup Levels 3-6 can be used to view data from several organizations at once. Entering a Rollup Org code will return entries for accounts that belong to all organizations that report to Rollup Org. For example: Entering 8129 into the KFS Org Rollup Level 04 Code filed will return entries that belong to organization codes 6077, 6091, and all other organization codes that roll up to 8129.

This table is a quick explanation on Organization Codes and the level they're represented at.

Organizaion Level	Organization Code Range	Reports To
Org Level 03	9000-9999	IRVN
Org Level 04	8000-8999	Level 03
Org Level 05	7000-7999	Level 04
Org Level 06	6000-6999	Level 05

The Campus Hierarchy tab displays a field for Org Level 07. As of now, there are no level 07 orgs.

KFS Organization Code

When filtering on a KFS Organization Code, KFS DWQuery will not factor in the inherit org hierarchy. This means, whichever org is identified will only return Accounts directly under that organization, not accounts under organizations that report to the organization listed. In order to select multiple Organizations under a particular higher level Organization, use the KFS Org Rollup Codes.

Other Org Attributes

KFS Org Parent Org Code will return entries for accounts that belong to organization codes one level below the organization code entered. For example, entering a level 05 organization code will return entries from level 06 organization code accounts.

Selecting a KFS Org Type Code will return entries only from organizations with a specific Org Type Code.

Full Accounting Unit Tab

The Full Accounting Unit tab allows users to choose Full Accounting Unit attributes such as Account, Object Code, and Project Code.

KFS Account

Any open KFS Account number can be entered into the KFS Account Filed. To return entries from closed accounts users can select [Y] YES in the Closed Flag field.

Due to the large number of Accounts in the in the Kuali Financial System additional information is needed to retrieve a list of accounts when using the list button. To use the list button next to the account field one or more of the following attributes must be chosen in DWquery. For example, if you filter on any of the following attributes, you should be able to view a list of Accounts.

• KFS ORG Rollup Level 03 Code
• KFS ORG Rollup Level 04 Code
• KFS ORG Rollup Level 05 Code
• KFS ORG Rollup Level 06 Code
• KFS Organization Code
• KFS Control Account
• Higher Ed Function code
• UC Expense Account
• Account Group Code
• UC Fund
• Fiscal Officer UCINetID
• Account Supervisor UCINetID
• Account Manager UCINetID

Additional Attributes

The Additional Attributes tab includes filters for Account Attributes and Project Code Attributes.

Project Code Attributes

When setting up a Project Code, users can identify a Project Manager and a Project Organization Code. This is now available to query by in DWQuery. Project Codes are not restricted in any way to a Manager or Organization Code and the Project Code attributes are for identification purposes. This means anyone can use a Project Code for their transaction regardless of what Organization or Manager is defined for the Project Code.

Ledger Detail

The Ledger Detail tab allows users to filter on specific General Ledger elements. When creating a summarized query it’s important to remember to take caution in the amount of ledger detail that is selected.

Qualifiers like Origin Code and Doc Type can apply to multiple transactions and would return a clear summary.

Qualifiers like Ledger Entry ID and Document number are specific to individual transactions. These unique qualifiers would be beneficial when searching for specific transactions. Selecting these qualifiers will return a list of transactions and would not be ideal for creating a summarized report.

GEC FDOC Number indicates if a particular entry has had a General Error Correction done to it.

Amounts

The amounts tab allows users to select pivoted columns that represent specific Balance Types on the General Ledger. By default, Entries is selected and represents the amount for a General Ledger Entry Line, regardless of Balance Type. For more information on Balance Types, see Balance Type page in the General Ledger Guide.

Column Name	Description	Balance Type Represented
Current Budget	Current Budget is used for in-year budget vs. Actual information and Contracts & Grants Inception-to-Date Budgets.	CB
Actuals	Actuals is an actual number experienced through some point in time as opposed to a number that is budgeted or projected into the future. For example, expense and income transactions are recorded using the AC balance type. This will likely be the most commonly used balance type. Positive values represent an increase and negative values represent a decrease based on the Object Category Code. For example, a positive Actuals entry for an Income Object Code is a Credit, where a positive Actuals entry for an Expense Object code is a Debit.	AC
Encumbrances	Encumbrances are used as projected committed funds towards a future expense. The most common encumbrance are encumbrances generated by Purchase Orders.	EX, IE, CE, PE
MC Depreciation	Used only by the Medical Center for depreciation.	MD
Entries	Entries is the raw value amount of a particular transaction, regardless of balance type. All negatives are credits and all positives are debits, regardless of the Object Category (Accounting Category). For example, a negative Entry Amount for an Expense indicates a credit against that expense, and a negative Entry Amount for Income also indicates a credit.	All Balance Types
Base Budget	The Base Budget is developed incrementally throughout the current fiscal year and becomes the unit’s initial operating budget allocation for the next fiscal year. Only certain sub-fund group funds are “budgeted funds” and can be permanently budgeted. Extramural funds (contracts, grants, and gift) are not budgeted funds. The Base Budget for the KFS General Ledger only represents a summarized amount for the beginning of the eyar and individual transactions throughout the year with minimal detail. For more details, users should reference KBM reports.	BB