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: 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:
- 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 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 AidF-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 |
| 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 |
| 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 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 |
| 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. | 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 |
| 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:
|
| 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) |
| 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) |
| 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 |
| 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 |