 FS Course 402 - Learning the Data in the FS Query Data Base
|
What do I need to know about the data to build a successful ad-hoc query?
Learning about the data objects and their attributes
These are the GL Trans Open data attributes that are available for performing local ad-hoc queries. Review the data
reminders and querying hints before trying the hands-on exercise below. See the
Data Dictionary for detail definitions.
Data Reminders - Querying Hints
- This data object is a "historied" file, which means that each transaction will
retain all its original attributes and can not be changed by subsequent
transactions. Each transaction within the open transaction file contains a
"string" of information that is unique to that transaction. That "string" contains
all the attributes shown within the open transaction object. The attributes can
be viewed in parts or all together.
- This data object contains the current month transactions that have NOT been
through month-end FS processing . It also contains the open balance
transactions that are controlled by the reference or source number, this
includes open commitment, and source and reference control accounts.
- During the fiscal year end closing, all June transactions remain in the open file until after the Final Ledger is
processed. To identify preliminary, interim, final or post closing entries, use
the fiscal year end processing indicator FYE_proc_ind = P, I, F, or C.
- A link can NOT be created between GL Balances, GL Trans Closed or GL
Trans Open objects.
- A transaction query outcome can be altered when linking historied files to
current tables (this can happen mainly when querying prior year transactions).
Some accounts, funds or department codes may have been deleted and no longer
exist on the current table. This will result in inaccurate results. For prior
year queries use the "history" FS-QDB_h.
|
|
- Using trans_ctgy_gl will identify appropriation, expenditures, encumbrances
and memo-liens transactions. The transaction file does not have separate
columns for appropriations, financials, encumbrances or memo-liens, as does
the balance file. When using transaction categories, which identifies the four
major categories, either print out the code and then sort by it or qualify it to
include or exclude a category of transactions.
Posting_date and effective_date are day specific. To get an entire month or range of months
as it pertains to ledger month processing, use ledger_year_month.
Document date, posting date, or effective date can be useful when trying to identify when a transaction was created,
processed or affected a ledger balance.
General Steps To Complete the Hands-on Exercise
- Get a blank FS Query Data Model Worksheet and think about which objects and attributes are required to complete
this exercise, one part at a time.
- Put a check-mark next to the appropriate attributes and note any necessary qualifications.
- Bring up the Hummingbird BI-Query User FS QDB Model, choose the appropriate object(s), then
fill in the selection screen.
- Check your column order and sort order before submitting you query.
Submit your query.
Hands-on Exercise No. 5: Complete a 2 part ad-hoc query as follows:
PART 1: Create a listing by vendor name showing how much
department 3520 currently owes to vendors with outstanding purchase
orders (*query hints* you can identify the vendor name by using the description field; set the FUNCTION beside
trans_amount_gl to SUM; and use trans_ctgy_gl to qualify encumbrances).
PART 2: Continuing your query with the results from Part 1, enhance your query so you can verify and reconcile the results
more easily to general ledger. Include the fields you would normally see on the GL inquiry balances by sub. (*query hint*
reorder columns and sort by account- fund- sub); then create a report for printing from these query results by selecting the
Results-Show as a Report-BI/Query Standard option off the tool bar. Subtotal the results at the account-fund-sub code level now
giving you an outcome that can be reconciled to a GL Inquiry. [Solution] [Reconciliation]
[Reconciliation] [Back to data objects index]
©Copyright 1996 - Regents of the University of California;
Prepared by Greg Partipilo and Terri Kirkman; updated 09/10/03