WISDM Data View
Detail Accounting Transactions

Name of the View: DETAIL_ACCOUNTING_TRANSACTIONS.

Description: This view displays non-salary accounting transaction detail for the UW-Madison campus. Salary and fringe benefit data is included in this view at a funding string level, starting with FY 2005 payroll transaction. Prior to that, salary and fringe benefit information is stored by person. All transaction types are included in this view – Budgets, Expenses, Encumbrances and Revenue. The basis for this view is GL Star and the data is updated daily. This view contains data from FY2001 to date.

Data Custodian: Accounting Services: Hua Ramer – phone: 262-4858, or email: hramer@bussvc.wisc.edu

Authorization: You must receive approval to access this data view. Authorization forms and procedures can be found at the following web site: http://www.bussvc.wisc.edu/acct/sfs/.

Special Considerations

  • Starting July 2004, payroll detail will not be displayed by employee name or by appointment ID. Instead, it will be displayed at funding string summary level. If you need to query salary data by employee, you should use Detail_GL_Payroll_Trans view.
  • Starting July 2004, three new fields will be added to this view as the result of SFS direct payment implementation. These three new fields are: “VCH_VOUCHER_ID”, “VCH_INVOICE_ID”, and “VCH_VENDOR_NAME”. They display SFS payment voucher numbers, invoice numbers, and vendor names respectively. Please refer to Data View Contents below for specific field information.
  • When selecting data for GPR funds (e.g. funds 101-119), prior year requisitions that were brought forward to the new fiscal year will be included if you don’t take action to exclude them. They are identified with a project/grant number - for example, if you are querying FY 2003 data, 101A305 identifies FY 2002 fund 101 requisitions that were brought forward to fiscal year 2003.
  • Fringe benefit and longevity payments for GPR funds will also be included at the Organization level unless you take action to exclude them.
    • Starting with FY2003, these expenditures are recorded to the Organization and also to a project/grant (e.g. 101A000 for fringes and 101A001 for longevity). One way to exclude them would be to use the following statement in your selection: PROJECT GRANT EQ " " (Note that the spaces within the double quotes will have the effect of excluding all Project/Grants within the funds that you have selected.
    • For FY2001 & FY2002, these expenditures were recorded to the Organization and no project/grant was used. They need to be excluded by their account code range (1800-1999).
  • Fringe Benefit encumbrances for project/grants are calculated dynamically within WISDM based on the Salary encumbrances. There is a timing difference that is important for you to understand. Salary payments are made throughout a month causing the salary encumbrance to be reduced. The reduction in salary encumbrance also reduces the encumbrances for Fringes in WISDM. When the actual payments for Fringes are posted at the end of the month, the difference should disappear.  Thus if you are running a before-the-end-of-month balance query, the fringe encumbrances could be understated.
  • For Hyperion users, please do not use the “Show Values” option whenever possible, because this option will slow down your query processing and your query will be “timed out.”

Data View Contents

Data field
Lgth
Description
Example/Possible Values
ACCOUNTING_PERIOD
3
The period within the fiscal year.
001 - July
002 – August
003 – September
004 – October
005 – November
006 – December
007 – January
008 – February
009 – March
010 – April
011 – May
012 – June
013 – FY End Cleanup
FISCAL_YEAR
4
The year that the budget covers on state funds.
2001, 2002, etc.
LEGACY_REQ_NBR
10

The number of the requisition.  In the case of purchasing card transactions, PR- plus the first six characters of the cardholder’s last name is recorded in this field. In the case of appointment records prior to FY05, the appointment ID serves as the requisition number.

Data source for this field is the legacy accounting system and will not include any SFS PO numbers.  The PO_ID field should be used instead.
e.g. 345Q678, PR-ROMEO
FUND
3
A code identifying the source of funds being reported on. In all cases, except clearing accounts, it represents an appropriation by the state legislature.
101, 128, 133, 144, etc.
PROJECT_GRANT
7
A code used by records that are organized by project. This code is made up of the fund and the proj/grant# – e.g. 144AB12.  Normally used by gift and grant funds (133) Federal grant funds (144) and Trust funds (161).  Starting in February 2008, all newly created projects will begin with PRJ instead of the fund.
e.g.144GH01
DIVISION
2
The first and second characters of the organization code. They define the college or division.
e.g. 03
DEPARTMENT
2
The third and fourth characters of the organization code. They define the major department within a college or division.
e.g. 05
SUBDEPARTMENT
2
The fifth and sixth characters of the organization code. They define a sub-department of the major department.
e.g. 00
ORGANIZATION
6
A code identifying the combined codes for division, department and sub-department.
e.g. 030500
ORG_DESCR
30
The name of the organization code.
e.g. Business Svcs/Acctg Svcs
PROGRAM_CODE
1
A code that identifies the general purpose for which money is being spent.
0-Student Services
1–General Operations & Services
2-Instruction
3-Educational activities
4-Research
5-Extension & Public Service
6-Library, Learning resources & Media
7-Physical Plant Maint & Svcs
8–Auxiliary Enterprises & Svcs
9-Student Aid
F-Farm Operations
R-Revenue
ACCOUNT
4
For Budgets, accounts identify the category to which a budget is recorded. In expense accounting, accounts are used to classify financial transactions according to the object of the expenditure. In revenue accounting, accounts are used to classify revenue transactions according to source. For Budgets, accounts identify the category to which a budget is recorded. For encumbrances, accounts identify the purpose for which the money is encumbered.
e.g. 3100
ACCT_DESCR
30
Descriptive name associated with the account.
e.g. Supplies
TREE_NAME – Note that this field is not yet available.
?
Each fund is assigned to a default tree that organizes the account numbers into groups such as Salaries, Fringe Benefits, Services and Supplies and Capital. (Note: This field will not be available in the initial release of the data view).

e.g.
MSN_GPR_DEFAULT
MSN_FUNDORG_DEFAULT
MSN_PROJECTGRANT_DEFAULT

TREE_LEVEL_NAME– Note that this field is not yet available.
?
This identifies the “category” that the account number is assigned for the selected tree. This is very similar to a budget or summary category in the legacy system. (Note: This field will not be available in the initial release of the data view).
e.g. Salaries, Fringe Benefits, Services and Supplies, Capital
CLASS 4 Displays the class field, which can contain the building number, tuition remission code, or county sales tax county.

0034
F092
T013
DESCRIPTION
30
Description of the transactions such as a vendor name, or an employee name for pre-FY05 data, or payroll pay period information after FY04.
Vendor Name, payroll pay period.
JRNL_LN_REF 15 A catch-all field. Can have voucher number, PO number or other information depending on the type and the source of the transaction 22222AA
PR-ROMEO
B616545
LEGACY_VO_NBR
7

Number of the voucher that supports the payment or receipt.

Data source for this field is the legacy accounting system and will not contain any SFS voucher numbers.  VCH_VOUCHER_ID (mentioned later in the document) will contain both Legacy and SFS voucher numbers.
e.g. 22222AA
INVOICE_NUMBER
12

Number of the vendor invoice that supports the payment.

Data source for this field is the legacy accounting system and it will not contain any SFS invoice numbers.  VCH_INVOICE_ID (mentioned later in the document) will contain both Legacy and SFS invoice numbers.
e.g. 1774885800 or PC402555
BUDGET_REASON
2
A code that identifies the reason for a budget entry.
e.g. RB, PY
BUDGET_TRANSFER
4
The reference number of the document that supports the budget transfer.
e.g. L800
TRANSACTION_TYPE
11
Identifier for type of transaction.

Budget
Expense
Encumbrance
Revenue
BUDGET_AMOUNT
17.2
Posted budget amount.
00000000001000000.00
REVENUE_AMOUNT
17.2
Posted amount of revenue. This includes sales credits.
00000000001000000.00
EXPENSE_AMOUNT
17.2
Posted amount of expenditures.
00000000001000000.00
ENCUMBRANCE_ AMOUNT
17.2
Posted amount of encumbrances.
00000000001000000.00
FRINGE_ENC_AMT
17.2
Used for project/grants – this is a calculated amount based on the salary encumbrance and the appropriate rate from ESIS. Please see the Special Considerations section of this document for more information.
00000000001000000.00
TUITION_ENC_AMT
17.2

Used for project/grants – this is a calculated amount based on the salary encumbrance and the appropriate rate from ESIS.  Please see the Special Considerations section of this document for more information.

Note: due to a change in tuition remission policy, this field is no longer used as of January 2007.
00000000001000000.00
TRANSACTION_DATE
8
The date of the transaction formatted as YYYYMMDD. This will usually be the accounting date assigned to the transaction in SFS.
20020315
POSTED_DATE
8
The date that the transaction was posted to SFS - formatted YYYYMMDD.
20020315
JOURNAL_ID
10
A code that identifies a journal in SFS.  The beginning of this number has meaning as listed in the next column.

The beginning of this number has meaning as follows:

  • PJ = Payroll transactions
  • FB = Fringe benefit trans
  • JRT = Internal transfers and P-Card
  • JRB = Internal billings
  • AP = Vendor payments processed in SFS
  • PT = Salary cost transfers
  • BD = Budget transactions

 
PO_ID 10

The Purchase Order ID number.  This field will contain PO IDs for both Legacy (Fiscal Year 2007 and previous years) and SFS (Fiscal Year 2008 and forwards).  For P-Card transactions, the field will contain PR- plus the first seven characters of the card holder’s last name.

345Q678 (for Legacy)
345Q678001 (for SFS)
PR-PERTZBO (for P-Card)
VCH_VOUCHER_ID 8 Vendor payment voucher number.  This field will contain both Legacy and SFS voucher numbers.  For P-Card transactions, the field will contain the first seven characters of the card holder’s first name plus the last three digits of the card number.

10520AA (for Legacy)
00001234 (for SFS)
KATHLEE252 (for P-Card)
VCH_INVOICE_ID 30 Vendor payment invoice number. This field is will contain both Legacy and SFS invoice numbers.  For P-Card transactions, the field will contain the invoice date.

TER122504
PIR122504
2008-07-21 (for P-Card)
VCH_VENDOR_NAME 40

Vendor payment name.

This field is populated with the vendor name for payments processed in SFS starting July 2004. 		DOE,JOHN
SCENARIO 8 Budget scenario code. ADJUSTED
REDBOOK
PY_ENC


Business Services Home | 'How-To' Pages | Acronym Index