lesson 1 gl new staff member - oracle general...
TRANSCRIPT
General Ledger (GL) Inquiries
Balances by Consolidation
Document Summary
Product Owner / Author Antonet Müller
Editor Name Chimoné Barends
Date 2014/12/01
Time 08:00 AM
Template Verbose
Lesson Objectives
On completion of this lesson you should be able to:
Understand the definition and purpose of a Balances by Consolidation Inquiry
Run a Balances by Consolidation Inquiry
Understand and interpret the search results of the Balances by Consolidation Inquiry
Drilldown to an E-doc
Export search results to an Excel spreadsheet
Lesson Scenario
As an existing staff member of the NWU you are required to work through this lesson, make some
notes if necessary and print out the documentation as a guideline.
This lesson will show you how to do Balances by Consolidation Inquiries, understand and interpret
the search results and be able to give correct information to those you report to.
The examples in this lesson display account balances at the highest reporting level (the
summarized consolidation level) and allows you to drill down to the next reporting level. You can
continue this drill down process to arrive at the actual e-doc where the transaction began (assuming
the transaction originated with a KFS e-doc and not from an external system).
Please note : The exact examples in this manual might not exist anymore on the database at the
time you work through this lesson. This is only for demonstration purposes. It is recommended that
you work through the E-doc manuals first and use your own E-doc examples in the Inquiry manuals.
Lesson Prerequisites
You need to be a NWU staff member that is registered on KFS as a user.
You need to understand that you have a responsibility towards the financial reporting of your
department and the university to interpret, understand and report in a responsible manner on these
Balances by Consolidation Inquiry results.
Balances by Consolidation Inquiry Page: 2 of 35
Table of Contents
1. Definition and purpose of a Balances by Consolidation Inquiry
2. Navigation
3. Process overview
4. Lookup Criteria
5. Running a Balances by Consolidation Inquiry
5.1. Running Inquiry by Account
5.2. Wildcard
5.3. Balance Types
5.4. Pending Entries
5.5. Consolidation or Detail
6. Understanding and interpretation of the search results
6.1. Additional information
6.2. Sorting the inquiry results
6.3. Debit / Credit view
7. Drilldown to an E-doc
8. Export search results to an Excel spreadsheet
Balances by Consolidation Inquiry Page: 3 of 35
1. Definition and purpose of a Balances by Consolidation Inquiry
The Balances by Consolidation inquiry displays account balances at the highest reporting level (the
summarized consolidation level) and allows you to drill down to the next reporting level. You can continue
this drill down process to arrive at the actual e-doc where the transaction began (assuming the transaction
originated with a KFS e-doc and not from an external system).
Please note : The Balances by Consolidation Inquiry does not take the SOF rules into consideration. It
only indicates the variance between Budget, Actuals and Encumbrances. It also only includes Income and
Expense object types.
The drilldown pattern of this inquiry is as follows:
o Balances by Consolidation
o Balances by Level
o Balances by Object
o General Ledger Balance
o GL Entry Lookup
o E-Doc
2. Navigation
KFS navigation: Main Menu > Balance Inquiries > General Ledger > Balances by Consolidation
Figure 1
Figure 2
Balances by Consolidation Inquiry Page: 4 of 35
3. Process overview
Figure 3
4. Lookup Criteria
Figure 4
A number of lookup criteria are displayed
Balances by Consolidation Inquiry Page: 5 of 35
Displays the account balances
at the highest reporting level
(consolidation) and allow you to drill
down to the e-doc.
Balances by Consolidation
InquiryRequested inquiry
resultsReports back on
inquiry
Figure 5
Fill in at least
o Fiscal Year, Chart Code and Account Number
The lookup criteria default to
o Current fiscal year
o Consolidation
o Cost Share Sub-Accounts Excluded (Cost Share will not be used by NWU)
o Pending entries not included
Default values may be changed.
Use the lookup icon as necessary to select the criteria.
Select the desired Consolidation Option that is, what level of detail you would like the report to present.
o Consolidation displays an aggregate total for all sub-accounts for the selected chart and account
number.
o Detail displays the balances for each sub-account for the chart and account number selected.
Select the desired Pending Ledger Entry Approved Indicator option. The Pending General Ledger inquiry offers
three alternatives:
o The ability to exclude all pending entries by selecting No
o Only those pending entries that are fully approved by selecting Approved or
o The ability to include all pending entries by selecting All.
Cancelled and disapproved pending entries are not pulled into the balance inquiry results.
Please note: Choosing to include all pending ledger entries in an inquiry can generate misleading results
because saved documents may contain incomplete or unbalanced accounting entries. Choosing to view all
pending ledger entries should be done with the understanding that unapproved entries may change drastically
prior to actually posting or they may not post at all.
5. Running a Balances by Consolidation InquiryBalances by Consolidation Inquiry Page: 6 of 35
5.1 Running Inquiry by Account
Figure 6
Complete the lookup criteria
The Balances by Consolidation Inquiry are run for an Account. The results include all the income and/or expense transactions for that account per consolidation, comparing
Budget, Actuals and Encumbrances and calculating the Variance.
1. Chart: Type I or I
2. Account: Type 1A00001 or 1A00001
3. Consolidation Option: Consolidation
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: No
6. Click
5.2 Wildcard
Balances by Consolidation Inquiry Page: 7 of 35
Figure 7
Unlike the other inquiries, it is not possible to run the Balances by Consolidation Inquiry by using the wildcard * for inquiring on all Accounts starting for example with 1 (SOF 1). The message that will be displayed is:
1. Chart: Type I or I
2. Account: Type 1* for all source of funds 1 accounts.
3. Consolidation Option: Consolidation
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: No
6. Click
5.3 Balance Types
Balances by Consolidation Inquiry Page: 8 of 35
Figure 8
All the Balance Types are included in the Balances by Consolidation inquiry results.
Balance Types include Current Budget (CB), Actuals (AC) and Encumbrances (EX or PE).
Only specific Balance Types, for example Pre-encumbrances (PE) or Actuals (AC) cannot be requested in this
inquiry.
You can drilldown on all the Balance Types.
In Figure 8 the following is displayed:
o Current budget of R320
o Actual Amount of -R40
o Encumbrance Amount of R100
o Budget – Actuals – Encumbrances = Variance
320 - (-40) - 100 = 260
1. Chart: Type V or V
2. Account Number: Type 1A00009 or 1A00009
3. Consolidation Option: Consolidation
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: All
6. Click
5.4 Pending Entries
Balances by Consolidation Inquiry Page: 9 of 35
Figure 9
The option also exists to include pending entries.
The Pending General Ledger inquiry offers two alternatives for including the pending ledger entries:
o the ability to include all pending entries by selecting All or
o only those pending entries that are fully approved by selecting Approved.
Cancelled and disapproved pending entries are not pulled into the balance inquiry results.
Please note: Choosing to include all pending ledger entries in an inquiry can generate misleading results
because saved documents may contain incomplete or unbalanced accounting entries. Choosing to view all
pending ledger entries should be done with the understanding that unapproved entries may change drastically
prior to actually posting or they may not post at all.
1. Chart: Type I or I
2. Account Number: Type 1A00001
3. Consolidation Option: Consolidation
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: All
6. Click
Balances by Consolidation Inquiry Page: 10 of 35
Figure 10
When you drilldown on the OPER consolidation in Figure 9 the Balances by Level are displayed.
Figure 11
When you drilldown again on the Lookup By Object the Balances by Object are displayed. In the example in
Figure 11 four objects are displayed which include pending lines. Total amount = R16 736.22
Balances by Consolidation Inquiry Page: 11 of 35
Figure 12
When choosing to exclude pending entries, the number of items retrieved could be less (if there was pending
lines that are now excluded) and the amounts could also differ.
In Figure 9 the amount for the OPER consolidation was R16 736.22. When excluding the pending lines, the
amount in Figure 12 is R18 736.22 for the same input criteria. A pending credit amount of R2 000 is not
included.
Only the transactions already posted to GL through the nightly processes are displayed when excluding the
pending entries.
1. Chart: Type I or I
2. Account Number: Type 1A00001
3. Consolidation Option: Consolidation
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: No
6. Click
Balances by Consolidation Inquiry Page: 12 of 35
Figure 13
When you drilldown again on the OPER consolidation in Figure 9 the Balances by Level are displayed.
Figure 14
When you drilldown again on the Lookup By Object the Balances By Object are displayed. In Figure 14 only
3 objects are now displayed which exclude pending lines. Total amount = R18 736.22
The number of items retrieved as well as the amounts differs from Figure 11 where the pending lines were
included.
5.5 Consolidation or Detail
Balances by Consolidation Inquiry Page: 13 of 35
Figure 15
Figure 16
The Consolidation Option allows you to select the level of detail you would like the report to present.
Consolidation produce a report aggregated at a high level while choosing Detail will result in very granular and
detailed results.
When choosing Consolidation the Sub-Account and Sub-Object would indicate *All*
The Cost Share Sub-Accounts will not be used by NWU. Therefor keep the Exclude default setting.
1. Chart: Type I or I
2. Account Number: Type 1A00001
3. Consolidation Option: Consolidation
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: All
6. Click
Balances by Consolidation Inquiry Page: 14 of 35
Figure 17
Figure 18
When choosing Detail the Sub-Account and Sub-Object would be displayed if any exists. If not, instead of
*All*, only a dashed line --- would be displayed.
1. Chart: Type I or I
2. Account Number: Type 1A00001
3. Consolidation Option: Detail
4. Include Cost Share Sub-Accounts: Exclude
5. Include Pending Ledger Entry: All
6. Click
6. Understanding and interpretation of the search results
Balances by Consolidation Inquiry Page: 15 of 35
Figure 19
Figure 20
The inquiry results for the Balances by Consolidation inquiry consist of 2 parts. (Figure 19 & Figure 20)
The first part indicates the Budget, Actual, Encumbrance and Variance amounts per Consolidation.
The second part summarizes the Income & Income from Transfers and Expenses & Expenses from Transfers
to calculate the Available Balance.
Please note : the Available Balance does not take the NWU SOF rules into consideration. For example, in
SOF 3 the Budget Amount is not “real” money and not available for spending. The Balance by Consolidation
inquiry results is only a comparison between Budget, Actuals and Encumbrances to calculate an Available
Balance. The Available Balance is however not available for spending in NWU terms.
Figure 21
The interpretation of the inquiry results’ columns are
Column Description
Balances by Consolidation Inquiry Page: 16 of 35
Fiscal Year Used for the purposes of financial reporting. Indicates the fiscal year to which General Ledger entries were posted.
Chart Code One character that indicates the campus / high-level structure of the KFS Chart of Accounts that the Account are specific to.
Account Number An account identifies a pool of funds assigned to a specific university
organizational entry for a specific purpose. Indicates to which Account the transaction was allocated to.
Sub-Account Number Certain transactions could have been allocated to an optional finer level of detail within a particular account.
Reporting Sort Code The Reporting Sort Code is an additional attribute for reporting purposes – centrally used.
Consolidation Code An attribute of Object Level that is used to group similar object levels into
categories for reporting. Each Object Level belongs to a single Object Consolidation.
Lookup By Level A drilldown option to drilldown to the Balances by Level.
Budget Amount The current budget amount in this account for the selected fiscal year.
Actuals Amount The total of all actual activity for a given balance line of the selected fiscal year.
Encumbrance Amount The total of all encumbrance activity for a given account for the selected fiscal year.
Variance The difference between the available budget and the actual amounts, plus encumbrances for an object code or category.
Account Group A smaller group of accounts within an organization to which the transaction belongs to.
Please note: The variance column in this inquiry does not take the SOF in consideration. The variance is calculated, but it does not reflect funds available for spending. For example: In a SOF 3 Account the budget would be part of the variance calculation, but SOF 3 budget is not available funds, it is only a guideline.
The inquiry results list the balances per consolidation that meet your lookup criteria. From here you can drill
down to the General Ledger Entry Lookup and then to the E-doc that created the transaction.
6.1 Additional information
Balances by Consolidation Inquiry Page: 17 of 35
Figure 22
The underlined inquiry results will provide more information when you click on it.
1. Click on
2. Click after viewing additional information.
Figure 23
Balances by Consolidation Inquiry Page: 18 of 35
Figure 24
3. Click on
4. Click after viewing additional information.
Figure 25
6.2 Sorting the inquiry results
Balances by Consolidation Inquiry Page: 19 of 35
Figure 26
The inquiry results can be sorted by any of the columns’ values by clicking on the underlined column heading.
1. Click on for example. The results will be sorted according to the amounts. From negatives to positives. For example -1, 0, 1.
Balances by Consolidation Inquiry Page: 20 of 35
Figure 28
When you drilldown on the Consolidation Code, the detail will display whether it is a debit or a credit type
Consolidation Code. The Consolidation Object Name will start with a D or a C.
1. Click on
Figure 29
2. Click on
Balances by Consolidation Inquiry Page: 22 of 35
Figure 30
The Debit/Credit view, as in the General Ledger Entry inquiry, is not part of the Balances by Consolidation
inquiry.
It means that you have to take the type of Consolidation / Object into consideration to know if an amount is a
debit or a credit.
If an amount is a positive amount, it acts according to the Consolidation/Object type.
o If the Consolidation/Object type is a credit, and the amount is positive, then it is a credit amount.
o If the Consolidation/Object type is a debit, and the amount is positive, then it is a debit amount.
If an amount is a negative amount, then it acts against the Consolidation/Object type.
o If the Consolidation/Object type is a credit, and the amount is a negative, then it is a debit amount.
o If the Consolidation/Object type is a debit, and the amount is a negative, then it is a credit amount.
In the example in Figure 28:
o Line 1 of the results: OPER Consolidation: Operating expenses consolidation is a debit consolidation
type. The Actual amount of R16 736.22 is a positive amount, which means it acts according to the
consolidation type which is a debit. Therefor the R16 736.22 is a debit amount.
o Line 2 of the results: CINC Consolidation: Current Income consolidation is a credit consolidation
type. The Actual amount of R1 100.00 is a positive amount, which means it acts according to the
consolidation type which is a credit. Therefor the R1 100.00 is a credit amount.
7. Drilldown to an E-doc
Balances by Consolidation Inquiry Page: 23 of 35
Figure 31
You may drill down into detail when there is the Drill Down link available, or if the amount or other attribute (e-
doc, for example) is a hyperlink.
The drilldown pattern for the Balances by Consolidation Inquiry is as follows:
o By Consolidation > By Level > By Object > GL Balance > GL Entry Lookup > E-Doc
1. Click on in the Lookup By Level column.
Figure 32
1. Click on in the Lookup By Object column.
Balances by Consolidation Inquiry Page: 24 of 35
Figure 33
You can drilldown on any of the amounts in the Budget, Actual or Encumbrance column, or on Drill Down in
the See Pending Entry columns.
1. Click on in the Actuals Amount column.
Figure 34
When you drilldown on the Actual Amount, the next screen is the General Ledger Balance Lookup.
1. If you want to do an inquiry for example on the R8000 in March, click on
Figure 35
Balances by Consolidation Inquiry Page: 25 of 35
The next screen is the General Ledger Entry Lookup.
In the General Ledger Entry inquiry you may drill down to the actual e-doc from the document Number link.
1.Click on the document number to open the document.
2. Click on after reviewing the document.
Figure 36
Figure 37
You may also drill down on the See Pending Entry column.
1. Click on
Balances by Consolidation Inquiry Page: 26 of 35
Figure 38
If there isn’t any pending lines for that object, the message No values match this search will be displayed.
Figure 39
If there are pending lines for that object, the next screen will be the General Ledger Pending Entry Lookup.
In the General Ledger Pending Entry Lookup you may drill down to the actual e-doc from the document
Number link.
1.Click on the document number to open the document.
2. Click on after reviewing the document.
Balances by Consolidation Inquiry Page: 27 of 35
Figure 40
Figure 41
You may also drilldown on Budget Amounts.
1. Click on in the Lookup By Level column.
Balances by Consolidation Inquiry Page: 28 of 35
Figure 42
The next Screen is the Balances by Level Lookup.
1. Click on in the Lookup By Object column.
Figure 43
The next Screen is the Balances by Object Lookup.
1. Click on in the Budget Amount column.
Figure 44
The next Screen is the General Ledger Balance Lookup.
1. Click on next to March.
Figure 45
Balances by Consolidation Inquiry Page: 29 of 35
The next screen is the General Ledger Entry Lookup.
Usually in the General Ledger Entry inquiry you may drill down to the actual e-doc from the Document Number
link - if the transaction originated in KFS. The Budget in KFS will be imported from IDU, therefor it does not
originate in KFS and further drilldown is not possible.
Figure 46
You may also drilldown on Encumbrance Amounts.
1. Click on in the Lookup By Level column.
Figure 47
The next Screen is the Balances by Level Lookup.
1. Click on in the Lookup By Object column.
Balances by Consolidation Inquiry Page: 30 of 35
Figure 48
The next Screen is the Balances by Object Lookup.
1. Click on in the Encumbrance Amount column.
Figure 49
The next Screen is the General Ledger Balance Lookup.
1. Click on next to March.
Figure 50
The next screen is the General Ledger Entry Lookup.
In the General Ledger Entry inquiry you may drill down to the actual e-doc from the document Number link.
1.Click on the document number to open the document.
2. Click on after reviewing the document.
Balances by Consolidation Inquiry Page: 31 of 35
Figure 51
8. Export search results to an Excel spreadsheet
Balances by Consolidation Inquiry Page: 32 of 35
Figure 52
To export the entire result list in .csv, spreadsheet, or .xml format, scroll to the bottom of the screen and click a
link to choose from among these options:
1. Click on at the bottom of the inquiry results.
Figure 53
2. Click on at the bottom of the screen.
Figure 54
3. Click on in the next screen in Excel.
Balances by Consolidation Inquiry Page: 33 of 35
Figure 55
Please note : to be able to do calculations in Excel, you will first have to insert columns to change the amounts
to a debit and credit view. In KFS all amounts are positive for debit or credit objects. You will have to change
the credits to negatives in order to do calculations in Excel. Please also refer to: 6.3 Debit / Credit view in this
manual.
4.Save the document on your computer. You can now use Excel functions like sort, fi lter etc.
to process the data according to your reporting needs.
Lesson Summary
Having completed this lesson you should be able to:
Understand the definition and purpose of a Balances by Consolidation Inquiry
Run a Balances by Consolidation Inquiry
Balances by Consolidation Inquiry Page: 34 of 35