Name of the View: DETAIL GL PAYROLL TRANS

WISDM Data View

Detail GL Payroll Transactions

Name of the View: DETAIL_GL_PAYROLL_TRANS.

Description: This view displays all accounting transaction details for the UW-Madison campus. It is created for those users who wish to list salary accounting detail data by employees. All types of transactions are included – Budgets, Expenses, Encumbrances and Revenue. The basis for this view is WISDM data that combines data in General Ledger and Salary Fringe Detail (SFD). The data is updated daily and contains data since the beginning of FY2005.

Data Custodian: Accounting Services: Hua Ramer – phone: 262-4858, or email:

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:

·  For those who do not need salary accounting data by employees, you may continue using Detail Accounting Transactions view and therefore, there is no need for you to change your query.

·  Fringe benefit and longevity payments for GPR funds 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.

·  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 may 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. / 2004, 2005, etc.
PERSON_ID / 8 / Person ID is assigned in the IADS system. An employee may have multiple Appointment IDs connected to one Person ID. / e.g. 00123456
APPT_REQ_NBR / 10? / For Salaries, the appointment id is assigned in the IADS system. In the case of purchasing card transactions, PR- plus the first seven characters of the cardholder’s last name is recorded in this field. / e.g. 00123456, 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. 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 (non-numeric) 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 encumbrances, accounts identify the purpose for which the money is encumbered. / e.g. SALPRM, 3100, 9500
ACCT_DESCR / 30 / Descriptive name associated with the account. / e.g. Supplies
CLASS / 4 / Displays the class field, which can contain the building number, tuition remission code, or county sales tax county. / 0034
F092
T013
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_DFLT
MSN_FUNDORG_DFLT
MSN_RSP_PJ_GT_DFLT
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
SCENARIO / 8 / Budget scenario code. / ADJUSTED
REDBOOK
PY_ENC
DESCRIPTION / 30 / Name of the employee that the transaction applies to or description of the transaction such as a vendor name. / Employee or Vendor Name
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
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
ORIGINAL_ENC_AMT / 17.2 / Posted amount of the original salary encumbrance. Will be NULL if associated transaction does not originate from the Salary Fringe Detail (SFD) file. / 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. / 00000000000010000.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. / 00000000000010000.00
APPT_EFF_DATE / 8 / Beginning Date of Appointment within Fiscal Year. / 20040701
APPT_END_DATE / 8 / End Date of Appointment within Fiscal Year. / 20050630
APPT_BASE_RATE / The base rate for the appointment. / Either an hourly rate or a yearly salary.
PAY_BASIS / 1 / Pay Basis / “A” for Academic; “C” for classified.
TITLE_CODE / 5 / Numeric Title Code / e.g. 00265
TITLE_DESCR / 30 / Description connected to the Title Code / e.g. Accountant-Advanced
OPR_ID / 32 / SFS operator ID. You may only see this in salary cash transfers.
SALARY_COST_TRANSFER / 3 / Indicates if the transaction was a Salary Cost Transfer / A value of “SCT” indicates the transaction is a Salary Cost Transfer.
CALC_ID / 9 / Payroll Calc ID. YYYYMM plus a number. / e.g. 200406100
CALC_DESCRIPTION / 30 / Description of Calc ID / e.g. “May Regular Unclassified”.
CALC_EARN_PER / 6 / Period of earnings related to the Calc ID. / e.g. 200405, 200405A, 200405B.
CALC_PERIOD_TYPE / 1 / Indicates the period and type of calc. / “A” for the A Student or Classified calc; “B” for the B Student or Classified calc; “C” for the C Student or Classified calc; “M” for the Regular Unclassified.
CALC_PAY_DATE / Pay Date relating to the Calc ID. / e.g. 20040601
TRANSACTION_DATE / 8 / The date of the transaction formatted as YYYYMMDD. This will usually be the accounting date assigned to the transaction in SFS. / 20040715
POSTED_DATE / 8 / The date that the transaction was posted to SFS – formatted YYYYMMDD. / 20040715
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

Last Updated: November 20, 2008 Page 7 of 7