Using the IBIS Financial Database


AIS HOME \| ABOUT US \| CONTACT US \| HELP INDEX \| FAQ INDEX \| SITE INDEX \| GLOSSARY \| AIS INTRANET

Types of Subobjects
Detail Tables in the IBIS Financial Database
IBIS Detail 2 Data Table Field Definitions
Operators
Joins Relationships (Watch video clip on this process. RealOne Player required)
For an expanded report set please see the reports provided by the Financial Information Services.

Types of Subobjects

In order to determine which table in the IBIS Financial Database contains the information you need, it is necessary to know what types of subobjects are related to an account. Since more than one type of subobject can be related to an account, it is necessary to separate them into different tables. The 6 types of subobjects are:

Subobject Type {Abbreviation}	Value in Tables ibis_detail#1, ibis_detail#2, subobject#1, subobject#2	Screen 3 - AUSO Option
Account {Acct-Sub}	SACT	F5
Cost Center {Cctr-Sub}	CCTR	F6
Cost Center/Activity		F7
Agency (Global)	SAGC	F8
Fund Type (Global)	SFTY	F9
Budget Fund Range (Global)	SBUD	F10

The following AUSO screens take you through the steps involved in relating subobjects.

Screen 1:

AUSO- Subobject screen

Screen 2:

AUSO- Subobject Relationship Creator screen

AUSO - Relating Subobjects to the Organization Unit

Screen 3:

AUSO- Subobjects related to organizational unit

Detail Tables in the IBIS Financial Database

IBIS_DETAIL#1 Table
(with Account Subobjects where applicable)

Contains ALL accounts.
Contains Account Subobject details (Acct-Sub) when they exist.
If there is no Account Subobject detail (Acct-Sub), then Account/University
Detail (Univ) is used.

IBIS_DETAIL#2 Table
(with Cost Centers and Cost Center Subobjects where applicable)

Contains ALL accounts.
Contains Cost Center Subobject details (Cctr-Sub) when they exist.
If there is no Cost Center Subobject detail (Cctr-Sub), then
Cost Center detail (Cctr) is used.
If there is no Cost Center Subobject detail and no Cost Center
detail, then Account/University Detail (Univ) is used.

> Notes:

Cost Center/Activity Subobjects (F7 from AUSO Screen 3) do not currently exist.
The Globally assigned subobjects (listed below) will be handled differently and do NOT go into IBIS_DETAIL #1. These types of subobjects will be in the SUBOBJECT#1 table:
- Agency Subobjects (SAGC)
- Fund Type Subobjects (SFTY)
- Budget Fund Range Subobjects (SBUD)

To view a list of field names, labels, descriptions, values etc. for the IBIS_DETAIL tables, visit the Field Definitions, Formats and Values for IBIS_DETAIL#2 page.

Example of IBIS_DETAIL #1 and IBIS_DETAIL #2 Table Contents
Sample Layout:

Admin Area: 046 Account: 0803311 UP10010
	Cost Centers: Project X Project Y
		Cost Center Subobjects to Project Y: Object Code 0336 Smith Jones Object Code 0452 PC Mac
	Account Subobjects: Object Code 0030 UP Non-UP
Account: 0104259 UP10010 (no Cost Centers, no Subobjects)

The IBIS_DETAIL #1 Table would look like the following with some selected sample data from the account layout example.

IBIS_DETAIL#1 Table:

FISCAL YEAR	ACCOUNT	OBJECT CODE	SUBOBJECT	AMOUNT	(Abbrev)
19971998 19971998	0803311 UP10010 0803311 UP10010	0030 0030	UP NON-UP	5000.00 5000.00	Acct-Sub Acct-Sub
19971998	0803311 UP10010	0452		6500.00	Univ
19971998	0803311 UP10010	0301		100.00	Univ
19971998	0104259 UP10010	0301		50.00	Univ

Each row represents a transaction.

The first transaction for $10,000 was resolved into 2 Account Subobjects, UP for $5,000 and NON-UP for $5,000.
The second row is a $6,500 transaction.
The third row is a $100 transaction.
The fourth row is a $50 transaction.

IBIS_DETAIL #1 Table Rules:

Contains ALL accounts.
Contains Account Subobject details when they exist.
If there is no Account Subobject detail (Acct-Sub), then Account/University Detail (Univ) is used.

The IBIS_DETAIL #2 Table would look like the following with some selected sample data from the previous account layout example.

IBIS_DETAIL#2 Table:

FISCAL YEAR	ACCOUNT	OBJECT CODE	COST CENTER	SUBOBJECT	AMOUNT	(Abbrev)
19971998	0803311 UP10010	0030	PROJECT Y		10000.00	Cctr
19971998 19971998	0803311 UP10010 0803311 UP10010	0452 0452	PROJECT Y PROJECT Y	PC MAC	3000.00 3500.00	Cctr-Sub Cctr-Sub
19971998 19971998	0803311 UP10010 0803311 UP10010	0301 0301	PROJECT X PROJECT Y		75.00 25.00	Cctr Cctr
19971998	0104259 UP10010	0301			50.00	Univ

Each row represents a transaction.

The first transaction for $10,000 was resolved into the Cost Center Project Y.
The second row is a $6,500 transaction which was resolved into the PC Subobject to Cost Center Project Y for $3,000 (Cctr-Sub) and the MAC Subobject to Cost Center Project Y for $3,500 (Cctr-Sub).
In the third row, the $100 transaction was resolved into Cost Center Project X for $75 (Cctr) and into Cost Center Project Y for the remaining $25 (Cctr).
Since Account 0104259 UP10010 on the fourth row does not have any Cost Centers or any type of Subobjects whatsoever, it will always go directly into a University (Central Accounting) detail which covers this $50 transaction.

IBIS_DETAIL #2 Table Rules:

Contains ALL accounts.
Contains Cost Center Subobject (Cctr-Sub) details when they exist.
If there is no Cost Center Subobject detail (Cctr-Sub), then Cost Center detail (Cctr) is used.
If there is no Cost Center Subobject detail and no Cost Center detail, then Account/University Detail (Univ) is used.

Comparison between IBIS_DETAIL#1 and IBIS_DETAIL#2

What one can observe when comparing the contents of IBIS_DETAIL#1 and IBIS_DETAIL#2 is that the grand total amounts should equal. It doesn't matter how complicated the resolution was, the total transaction amount is always the same. The grand total for Account 8 33 11 in the IBIS_DETAIL#1 example is $16,650, and the grand total for Account 8 33 11 in the IBIS_DETAIL#2 example is also $16,650. There are different subtotal amounts, but the total by transaction and by the grand total should be equal.

Transactions for Account 8 33 11:

Transaction #	Transaction Total	IBIS_DETAIL#1	IBIS_DETAIL#2
1	$10,000	$5,000 $5,000	$10,000
2	$6,500	$6,500	$3,000 $3,500
3	$100	$100	$75 $25
4	$50	$50	$50

IBIS_Detail#2 Data Table Field Definitions

Index	Field Name	Label	Type	Length	Format Example	Values	Description
I	CODE-BUDG-ADMIN-AREA	Admin Area	A	3.0	015 (need all 3 characters)		Admin area associated with the budget contained in the NUMB-ACCT field.
I	DEPT-MNEMONIC	Dept Code	A	7.0			Department-level mnemonic on which security is based
I	NUMB-ACCT	Account	A	16.0	0803311 UP10010		The number of the account. The first 10 bytes is the CODE-CBUD (budget number). The last 6 bytes is the CODE-FUND (fund number).
I	DATE-FNST-FISCAL-YR	Fiscal Year	A	8.0	19961997		The fiscal year
	DATE-FNST-DTL-POSTING	Post Date	D	8.0	m/d/yy or m/d/yyyy**: 1/1/97, 2/28/2000		Date when encumbrance is committed or when the income/expense is realized.
	CODE-COBJ	Object Code	A	4.0	0105 (need all 4 characters)		Numerical code assigned by the University to identify and categorize different types of income and expenses.
I	CODE-CCTR	Cost Center	A	10.0			A 10 byte character code created by the dept/college to establish projects, tasks, cost centers.
	DESC-FNST-SUB-OBJ	Subobject	A	30.0			This field will contain the description of the sub-object. The field must be unique to CODE-COBJ and organizational entity.
	CODE-UNIV-REC-TYPE	Record Type	A	4.0		IPBD ITBD IACT IENC IENS IENP PPBD PTBD PACT PENC PENS PENP RPBD RTBD RACT RENC RENS RENP	Code identifying a specific record type within the Univ database file. Codes starting with I mean the detail was resolved on CRFN already. Codes starting with P or R mean the detail was resolved automatically to Central Acctg, but it is still on CRFN.
	AMNT-FNST-DTL	Amount	N	11.0			This field will contain the dollar amount that the user enters at resolution time.
	CODE-FNST-DTL-TYPE	Detail Type	A	4.0		SRFC, VRES, PAYS, WAGC	The type of detail document, such as ROCR, SRFC, IDCC, etc.
	TEXT-FNST-DTL-DEPT- FREE-SPACE	Free Space	A	15.0			15 bytes of space which the dept can use as they please.
	DESC-FNST-DTL	Description	A	10+30.0			The description of the detail.
	NUMB-FORM-ID	Form #	N	8 0			This is the document number created on EASY forms.
	DATE-FORM-ENTERED		D	8.0	m/d/yy or m/d/yyyy**: 1/1/97, 2/28/2000		The date the form was entered.
	NUMB-DOCT-FNST	Document #	N	8.0			The system-generated number of the document (starts w/8).
	NUMB-ACCT-ENCM	Encumbrance #	N	4.0			Contains an encumbrance number unique to each account number.
	NUMB-FNST-DTL-TIEBACK		N	8.0			Detail tieback #
	NUMB-FNST-DTL-CENT-REF	Central Reference #	A	7.0			A temporary field representing the central reference number
	AMNT-FNST-DTL-DISCOUNT		N	9.0			Discount amount
	DATE-FNST-DTL-DEPOSIT	Deposit Date	D	8.0	m/d/yy or m/d/yyyy**: 1/1/97, 2/28/2000		The date of the deposit. (This can be different from the posting or recording dates.)
	NUMB-VEND	Vendor #	N	10.0			Permanent or misc vendor number
	NAME-VEND-ABBREV	Vendor Code	A	10.0			The code for the vendor from whom this order will be purchased.
	NUMB-VEND-DET-INVOICE	Vendor Invoice #	A	11.0			The number of the vendor's invoice.
	NUMB-ORDER-FNST	PO #	N	9.0			The number of the purchase order.
	NUMB-CHCK-FNST	Check #	N	10.0			The number of the check.
	INDC-FNST-DTL-PARTIAL- RECG-RPT		A	1.0			Indicates whether the items received are only a portion of the entire order LABEL: PARTIAL RECG RPT.
	CODE-FNST-SOBJ-TYPE	Subobject Type	A	4.0		SACT, CCTR, SAGC, SFTY, SBUD	Type of Subobject assignment: Account, Cost Center, Agency (Global), Fund Type (Global), Budget Fund Range (Global).
	NAME-FNST-SOBJ	Subobject Group Name	A	15.0			A name given to a set of department subobjects.
	NUMB-FNST-GROUP		N	5.0			This field will contain a number that is in numerical order for an organizational entity and object code. It is used to determine a break in subobject resolution.
	NUMB-FNST-SUB-OBJ		N	13.0			This field will contain a system-generated number of the subobject. It will be retrieved from codeset "CSYSNUM".
	CODE-CBUD-ACCESS1- MNEMONIC	Mnemonic	A	7.0		OTC, CESDEAN	Mnemonic associated with the budget contained in the NUMB-ACCT field.
	CODE-FNST-DTL-FO		A	1.0
	NUMB-DOCT-ACCT-SEQ		N	4.0			Sequences accounts within a document.
	NUMB-ACCT-DTL-SEQ		N	4.0			Sequences resolved details within an account.
	INDC-FNST-AUDIT		A	1.0		Y, N	Indicates that a detail record is an audit record.
I	DATE-RECD-LAST-UPDATE-TRANS		D	8.0	m/d/yy or m/d/yyyy**: 1/1/97, 2/28/2000		Date the record was last updated.
	NUMB-FNST-DTL	Detail #	N	15.0			This field will contain a system generated number that will be unique for every detail.

I = Index

* The subobject#2 table contains the same fields. The fields for the ibis_detail#1 table and the subobject#1 table are the same except that the CODE_CCTR (Cost Center) field is not in these tables.

* For dates less than or equal to 12/31/99, the m/d/yy format is correct. For dates greater than or equal to 1/1/2000, the m/d/yyyy date format is correct.

Other Tables in the IBIS Financial Database

IBIS_CODESETS Table

Contains Central Object Codes (CODE_VALUE field).
Contains Central Object Description.
Contains Category Indicator (see below).

Category	General Fund Category Indicator	Restricted Fund Category Indicator
Income	000B	000B
Salaries	010B	010B
Wages	020B	020B
Department Allotment	030B	030B
Equipment	030B	070B
Capital	030B	080B
Overhead	030B	090B

IBIS_ HEADER Table

Contains "Demographic" data about an account such as:
Award Amount, Begin and End Dates.
Contains Predominantly Restricted Accounts.

SUBOBJECT#1 Table
(with All Types of Account/Global Subobject details)

Contains Account Subobject (Acct-Sub, SACT) details.
Contains Agency Subobject (SAGC) details.
Contains Fund Type Subobject (SFTY) details.
Contains Budget Fund Range Subobject (SBUD) details.

Same fields as IBIS_DETAIL #1

SUBOBJECT#2 Table
(with Cost Center Subobject details)

Contains Cost Center Subobject details.

Same fields as IBIS_DETAIL #2

Operators

An operator tells a query to perform an operation. For example, the + operator tells a query to perform an arithmetic operation (addition). Query tools allow you to use arithmetic, comparison and logical operators as described below in the tables below.

> Note:
Arithmetic operators may vary, depending upon your data source, but most data sources support the standard arithmetic operators. Some data sources support additional operators not listed in the tables. To find out which operators your data source supports, see the documentation that comes with the driver your data source uses or contact the driver vendor.

Arithmetic Operators:

Arithmetic operators are used to perform numeric calculations.

Example:
The following expression uses the + operator to calculate the sum of Permanent Budget and Temporary Budget: IPBD + ITBD.

Arithmetic Operator:	Used to:
*	Multiply two numbers
+	Add two numbers
-	Subtract one number from another
/	Divide one number by another

Comparison Operators:

Comparison operators are used to compare two values.

Example:
The following expression uses the <= operator to determine Object Codes in the Income category: CODE-COBJ < =' cellSpacing=3 cellPadding=4 align=left border=1 0099?< B>.

Comparison Operator	Meaning
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
=	Equal
<>	Not equal to

Logical Operators

Logical operators are used with expressions that evaluate to true or false.

In the following example, the And operator combines the expression CODE-FNST-DTL-TYPE = 'SRFC' with the expression CODE-COBJ = '0336'. The entire expression evaluates to true if the CODE-FNST-DTL-TYPE value equals "SRFC" and the CODE-COBJ is "0336": CODE-FNST-DTL-TYPE = 'SRFC' And CODE-COBJ= '0336'.

Logical Operator	Meaning
AND	This criterion and another criterion must be true for the records in the result set. One criterion as well as another criterion must be true.
OR	This criterion or another criterion must be true for the records in the result set. Either one criterion or another criterion may be true.
NOT	This criterion must not be true for the records in the result set.

Other Operators

The following table lists other operators you can use.

Operator	Example	How it's used
BETWEEN	Between 7/1/97 And 7/31/97	To determine if a value falls within a certain range.
IN	In ('IPBD', 'ITBD', 'PPBD', 'PTBD', 'RPBD','RTBD')	To determine if a value is equal to any of several values in a list.
IS	Is Null, Is Not Null	With the keyword Null to determine if a value is null (has no value) or not null (has a value).
LIKE	Like '??????????10010'	To compare two values using a wildcard character of a specific length (?).
LIKE	Like 'Sm*'	To compare two values using a wildcard character of variable length (*).

Joins/RelationshipsJOIN allows the user to get data from more than one table as long as there is at least one field that is common between 2 tables at a time. If you JOIN 2 tables, there must be at least one field that occurs in both of the tables. If you JOIN 3 tables, then Table 1 and Table 2 must have at least one field in common and Table 2 and Table 3 (or Table 1 and Table 3) must have at least one field in common. The field that is shared by Table 1 and Table 2 does NOT have to be the same field that is shared by Table 2 and Table 3 (or Table 1 and Table 3). Example 2 below demonstrates this last scenario.

Example 1:

IBIS_DETAIL #1 Table		IBIS_HEADER Table
NUMB-ACCT 0201544 UP508A0	<- Field that is common to both tables ->	NUMB-ACCT 0201544 UP508A0

Example 2:

IBIS_HEADER Table		IBIS_DETAIL #1 Table		IBIS_CODESETS Table
NUMB-ACCT	<- Field that is common to both tables ->	NUMB-ACCT
		CODE-COBJ	<- Field that is common to both tables ->	CODE-VALUE (Object Code)

Tips for Microsoft Access Querying in the IBIS Financial Database

> Notes:

If you have some tips that would be helpful to others and which you would like to have published at this web site, please send your suggestions to: njb121@psu.edu.
The IBIS Financial Database Listserv is available for financial database users. For information on subscribing, visit the IBIS Financial Database Listserv page.

Operational Tips

For each query, set the query property ODBC Timeout to 0 (zero)
To optimize your database file performance, compact and repair your database file on a regular basis. This can be done while in the database file. Use the tools menu item, select Database Utilities, and select Compact and Repair.
The IBISFin database is scheduled to update nightly. A total refresh is typically scheduled for the second Friday of the month and incremental updates occur the rest of the month.
The warehouse is taken off-line at 5pm each day in preparation for nightly maintenance. The server is down only momentarily and is available again within a minute.
Use Fields which are indexes in every query. Searching on an indexed field is much faster. Fields which are indexes are:
- IBIS_DETAIL#1 and IBIS_DETAIL#2 Tables:
  - CODE-BUDG-ADMIN-AREA
  - DEPT-MNEMONIC
  - NUMB-ACCT
  - DATE-FNST-FISCAL-YR
  - CODE-CCTR
  - NUMB-FNST-DTL
- HEADER Table:
  - NUMB-ACCT

When using the In statement or operand, make sure the text criteria is enclosed in either single or double quotes with a comma between multiple criteria. For example, In('IPBD','ITBD').

Value Definitions

CODE-UNIV-REC-TYPE Values:
IPBD	Permanent Budget
PPBD	Pending Permanent Budget
RPBD	Resolved Permanent Budget
ITBD	Temporary Budget
PTBD	Pending Temporary Budget
RTBD	Resolved Temporary Budget
IACT	Actual
PACT	Pending Actual
RACT	Resolved Actual
IENC	Ordinary Encumbrance
PENC	Pending Ordinary Encumbrance
RENC	Resolved Ordinary Encumbrance
IENP	Planned Encumbrance
PENP	Pending Planned Encumbrance
RENP	Resolved Planned Encumbrance
IENS	Standing Encumbrance
PENS	Pending Standing Encumbrance
RENS	Resolved Standing Encumbrance

> Notes:

The record types that start with I have been posted BOTH to the university and they have been resolved through CRFN.
The record types that start with either P or R have ONLY been posted to the university and they have NOT been resolved through CRFN.

Criteria Tips:

Income Object Codes are <=0099 and Expense Object Codes are >0099.

Remember to run separate queries for Income information and for Expense information.
Don't combine Budget amounts with Actual or Encumbrance amounts.
- Use: Not In ('IPBD','ITBD','PPBD','PTBD','RPBD','RTBD') as criteria for the CODE-UNIV-REC-TYPE field when you want Actual and Encumbrance data.
- Use: In ('IPBD','ITBD','PPBD','PTBD','RPBD','RTBD') as criteria for the CODE-UNIV-REC-TYPE field when you want Permanent and Temporary Budget data.
Since all text data is in uppercase letters, remember to use upper case when querying. If you run a query with the criteria in lower case letters, you won't get any results.
Remember to include the fiscal year of interest (ex: ³19971998´) in the criteria for the DATE-FNST-FISCAL-YR field.
The account format is: 0803311 UP10010
>Notes:
- There must be a space between the budget and location code if the budget is only 7 characters long.
- There is NOT a space between the location code and the fund.
- The location code is in CAPS

Logic Tips

AND	X as well as Y	Both conditions must be true to get back results.
OR	Either X or Y	One or the other condition must be true to get back results.

Joining Tables

Table 1	Table 2	Field from Table 1	Field from Table 2
IBIS_DETAIL (1 or 2)	HEADER	NUMB-ACCT	NUMB-ACCT
IBIS_DETAIL (1 or 2)	HEADER	CODE-BUDG-ADMIN-AREA	CODE-CBUD-ACCT-ADMN-AREA
IBIS_DETAIL (1 or 2)	IBIS_CODESETS	CODE-COBJ	COBJ-VALUE

Questions: AIS Support