WISDM Data View
ESIS
AWARD & ACCOUNTING DATA
Name of the View: ESIS_AWARD_ACCOUNTING.
Description: This view displays ACTIVE Research Administration award information that has summarized accounting data associated with it. Data is taken from both the ESIS system and WISDM and is updated daily. This data view contains all Fund-Accts that are included in the ESIS system: 133, 135, 142, 144, 145, 146, and 148. It also includes certain Grad School funds - 101 Research Awards and Fellowships recorded to funds 107 & 403.
Data Custodian: Research & Sponsored Programs: Bob Andresen – phone: 262-3822, email: rspinfo@rsp.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/data.html.
Special Considerations:
- As of May 3, 2007: this data view contains records for accounting periods dating to the beginning of fiscal year 2007.
- When writing queries you can use the PERIOD_IND field to retrieve information for the current (period_ind = 'C') or previous (period_ind = P') accounting periods. You can obtain data for any accounting periods in the view (including the current and previous ones) by using a combination of ACCOUNTING_PERIOD and ACCOUNTING_FISCAL_YEAR fields in your selection criteria. To correctly limit the data returned by your query, please use either PERIOD_IND or the ACCOUTNING_PERIOD and ACCOUNTING_FISCAL_YEAR.
- Date values for ESIS data have derived century values. If you notice erroneous dates, please contact the data custodian with the fund account number so the record can be corrected.
- Encumbrance amounts are derived daily from both SFS encumbrance transactions (create, reduce, cancel, etc.) and SFS salary payment liquidations. Salary encumbrances are liquidated (reduced) at the same time that these transactions post, however the timing with Fringe Benefit and Tuition/Fee Remission payments is more complex. These payments are not computed and posted until after month end, while their encumbrances are liquidated at the same time the salary payment posts. Because these encumbrances are liquidated before the offsetting charge has posted, the available balance (UNENCUMBERED_CASH_ITD, etc) may be overstated in the current month, and also briefly for the previous month, until the Fringe Benefit and Tuition/Fee Remission payments post in SFS.
- For Brio 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: Note that the fields in the shaded areas are taken from the Accounting System. All other fields are taken from the ESIS system.
| Data Fields | Lgth | Example | |
|---|---|---|---|
| Fund | 3 | The fund that has been assigned to this award. A fund is an independent fiscal and accounting entity with a self-balancing set of accounts and a stated purpose or set of objectives. | 144 |
| Year | 4 | Four digit State fiscal year of this project. | e.g. 2004, 2005, 2006, etc. A blank value indicates this field does not apply to the record. |
| Acct | 4 | The account (project/grant) number assigned to this award. This is used for records that are organized by project rather than by organizational entity. This is normally used for gift & grant funds. | GH01 |
| Status | 1 | This describes the status of the award. | P-pending W-Withdrawn R-Rejected by Agency A-Approved by Agency N-Approved, but not funded I-Inactive |
| Accept_Date | DATE | Date of the formal acceptance of the project by the Board of Regents. Must be entered EXCEPT for account (project/grant) numbers assigned as a result of an 88-1 request. In that instance the field should be left blank until the fully executed award arrives. | Null value indicates no accept_date specified in ESIS |
| Commit_Date | DATE | The sponsoring agency has funded this project for a certain length of time and conditionally committed itself to additional funding through this date. | Null value indicates no commit_date specified in ESIS |
| Award_Date | DATE | The date an award is obligated by an agency or donor. | Null value indicates no award_date specificed in ESIS |
| Begin_Date | DATE | The beginning date of the project. | A begin date of "1/1/1900" is equivalent to "no begin date". |
| End_Date | DATE | The ending date of the project. | An end date of "12/31/2099" is equivalent to "no end date". |
| Agency | 17 | The abbreviation of an agency providing funding. This is the full 17-character name. | |
| Donor_Description | 200 | The description of the donor. This could be the donor's name or an agency translation. | |
| Donor_Type | 1 | A classification of the donor or granting agency. | Blank Not known A - Corporation-Domestic B - Companies-Domestic-Nonmanufacturing (American Family etc) C - Business Cooperatives, Associations, Commissions, Institutes D - Health related organization E - University of Wisconsin Foundation F - Foundations-Corporate G - Foundations-Philanthropic, Other H - Foreign Corporations, Universities, Hospitals, & Other Business Ventures I Wisconsin Alumni Research Foundation (WARF) J - Government-Federal (All 144 145 146 147 148 Accts) K - Multiple donors L - Government - State - WI M - Government - County - WI N - Government - City - WI O Government - Domestic (Other State, County, City) P - Government - Foreign (United Nations World Bank) Q - University, School, Hospital - Public R - University, School, Hospital - Private S Public Broadcasting (The Friends of WHA & others) T - Public Broadcasting (CPB, PBS, NPR, public radio and TV stations) U - Individuals, Trusts & Estates (not-alumni/Brittingham) V - Non-business Associations, Clubs, Committees, Societies, Councils, Guilds, Leagues (social and professional) W - Alumni including trusts and estates (Vilas) X - Other UW System Foundations Z - Overhead, Suspense, Revolving |
| Project_Type | 2 | This code is used to identify types of projects. Not all projects will have a value in this field. | 01=suspense 02=overhead 03=faculty-staff salary support 04=student salary support 05=employee-interchange agreement 06=named professorships 07=research-health related (non-federal) 08=research-industrial (non-federal) 09=research-other (non-federal) 10=research (federal) 11=doctoral dissertation research 12=research career awards 20=research training program, educational research training program, traineeships 21=research participation 22=institutes (academic, summer, advanced study etc.) 23=preparation of personnel in the education of handicapped 24=educational personnel development program 25=instruction-training-general 26=instruction-training-women 27=instruction-training-minority 28=instruction-training-in service 29=faculty development 30=fellowship-predoctoral 31=fellowship-postdoctoral 32=fellowship-special 33=fellowship-international 34="NDEA Title IV" 35="NDEA Title VI" 36=fellowship-graduate G1 assigned G2 disadvantaged G3 campus fellowships G4 E.B. Fred G5 grad special G6 supplement G7 travel 37=fellowship-science faculty 38=fellowship supply allowance 39=fellowship-miscellaneous 40=construction-remodeling-renovations 41=landscaping-planting 44=exhibits 45=miscellaneous 49=patient care 50=equipment 51=computer usage 52=radio-television production 53=lecture programs 60=scholarships 61=short course programs 62=prizes, awards 63=graduate student support 66=loans 68=work-study 69=tuition and fees only 70=curriculum improvement and development 71=libraries 72=books, journals 73=language and area centers 74=travel 75=Title I of Higher Education Act of 1965 76=Title II of Higher Education Act of 1965 77=Title III of Higher Education Act of 1965 85=headstart program 86=upward bound program 90=conference, workshop, symposium 91=publications 92=editorial-secretarial expenses 95=institutional programs 97=international programs 98=discretionary-unrestricted |
| Project_Category | 2 | This code is used to identify specific kinds of projects. There are no edits on this field, so any combinations are valid. | |
| Project_UDDS | 7 | The UDDS charged with administering the project for the principal investigator. It may or may not be the UDDS where the principal investigator resides. | e.g. A340710 |
| Project_Unit_Div | 3 | The unit and division portions of the Project UDDS. | e.g. A34 |
| Project_Desc | 250 | The title of the project or award. | |
| PI_NAME | 30 | The name of the principal investigator for the project. A principal investigator is the person who administers the grant. | Format: LASTNAME,FIRSTNAME MI No space between comma and first name.
This is the SFS format for names. In some instances PI Name may be a role (e.g., DEAN, CHAIR) rather than an individual. |
| PI_HR_EMP_ID | 8 | The HR system Person ID or Employee ID number of the PI | |
| PI_HR_NAME | 30 | The name of the principal investigator matched from the HR system. | Format: LASTNAME, FIRSTNAME MI There is a space between the comma and
first name. This is the HR system format for names. In cases where he PI_NAME is a role, this field will be one single blank space. |
| PI_UDDS | 7 | The UDDS where the PI is assigned. | e.g. A340710 |
| Budget_Amount | 9.2 | This is the amount the donor has committed for funding the project. | 100000 |
| Overhead_Base | 1 | This identifies the items of direct cost on which a calculation for overhead may be made as part of the University's monthly overhead generation process. | Blank-Not known A-No calculation to be made B-Salary and wages C-Total direct Cost D-Total direct cost with certain exceptions (var1) E-Total direct cost with certain exceptions (var2) |
| Overhead_Rate | 3.2 | The percentage to be applied against the appropriate direct cost items to calculate indirect cost. | e.g. 45.5 |
| Regent_Category | 1 | These categories are used to group awards in a specific order for reports to the Regents. | Blank-Not known I-Instruction R-Research S-Student aid P-Physical plant G-Gift-in-kind L-Libraries U-Unrestricted M-Miscellaneous E-Extension and Public Svcs |
| Document_Type | 1 | Funding instrument used to make the award. | Blank-Not known |
| Ag_Number | 6 | A project code referring to a school of agriculture project. This project number is used to link other federal awards with the agriculture school project subsystem. | |
| Grad_Number | 6 | ||
| Routing_Number | 6 | A unique number assigned to proposals from PALS, the internal RSP tracking system. | |
| User_Number | 6 | The initials of the person that last updated the record. | |
| Nondirect | 2 | Identifies Federal funds awards to the UW through a non-federal agency acting as prime contractor. | If ND is entered, then Federal Agency is the Agency Code field and prime contractor is in Donor Description. |
| Predecessor | 4 | The account (project/grant) number that identified the project during the previous year. | e.g. ED01 |
| Successor | 4 | The account (project/grant) number that will be assigned to the project if it is renewed next year. | e.g. HY03 |
| Property_Title | 1 | Indicates whether the University or the sponsor has title to equipment purchased or fabricated with project funds. | 0-Not Known 1-University of Wisconsin has Title 2-ND Sponsor has Title 3-Federal Agency has Title 4-No Equipment Purchase Allowed 5-Non-Federal Sponsor has Title |
| Property_Recs | 5 | Identifies the minimum dollar amount in whole dollars at which title to equipment is retained by the sponsor. | |
| Fee | 1 | Controls whether tuition remissions are charged or not charged to this project. | Blank-Unknown/NA N-No Provisions W-Waived D-Deferred |
| CO_PI | 30 | The name of the second person working on project. | Format: LASTNAME,FIRSTNAME MI No space between comma and first name. This is the SFS format for names. |
| CO_HR_EMP_ID | 8 | The HR system Person ID or Employee ID number of the CO-Investigator | |
| CO_HR_NAME | 30 | The name of the principal investigator matched from the HR system. | Format: LASTNAME, FIRSTNAME MI There is a space between the comma and first name. This is the HR system format for names. |
| CO_UDDS | 7 | UDDS of second person working on project | |
| Request_88_1 | 1 | Indicates whether an account (project/grant) number was assigned in advance of receipt of a fully executed award. The field is named after the form used to request an account number in advance. Note: This field is also directly associated with the Award Date field. | N-No (Default) Y-Yes A-Awarded |
| Award_Number | 25 | Number assigned to a project by the granting agency – not all records will have award numbers. | |
| Donor_Address | 100 | Address of the donor of the award. | |
| Expenditure_End | 2 | The number of months past the project end date that expenditures may be charged against this project. | Blank if Unknown or Not Applicable 00 through 99 |
| Cost_Share_Ind | 1 | This code indicates how this fund should be handled in relation to Research & Sponsored Programs Cost Sharing Report. | Blank-Not Applicable C-Contractual Cost Sharing Account N-Shouldn't appear on Institutional Cost Sharing Report I-Should appear on Institutional Cost Sharing Report |
| Fringe_Benefit | 1 | This code indicates whether fringe benefits costs are to be charged directly to the project. | Blank-Not Known Y-Charged directly to project N-Not charged directly to project |
| Senior_Acct | 4 | The control account (project/grant) for a project that has one or more sub-accounts. This account number is being used in addition to the current account number for this project. This account number is used to point to other sibling accounts where each senior account field points to another account. | GH01 |
| Human_Subjects | 1 | This switch indicates that human subjects are to be used in this project and the necessary approval of such must be obtained. | Y-Human subjects are to be used N-Human subjects will not be used |
| Human_Subj_Date | 4 | The date the approval was given to use human subjects in the project. | |
| ESHS_IRB_NUMBER | 1 | Identifies which of the 4 campus institutional review boards (IRBs) have reviewed and approved activities involving human subjects. | 1,2,3,4 = allowable entries (must be nonblank if human subjects is Y). |
| ESHS_PROTOCOL_NUMBER | 11 | A transaction number assigned by the IRB that documents approval of an investigator's plan for conducting a medical or scientific experiment. | |
| ES_ANIMAL_SUBJECT | 1 | Indicates whether or not the project involves the use of vertebrate animals. | 'Y' - Animal subjects are to be used ‘N' - Animal subjects will not be used |
| ES_ANIMAL_SUBJECT_DATE | 4 | If the animal subject switch is set to 'Y' this field will contain the date the Animal Care Committee approved the use of vertebrate animals. Must be nonblank if animal subjects switch = 'Y'. | |
| ESAN_PROTOCOLNUMBER | 11 | A transaction number assigned by the Animal Care Committee that documents approval of an investigator's plan for conducting a medical or scientific experiment. | |
| Tuition | 1 | This code indicates what provisions are made for tuition by this award. | Blank-Unknown or Not Applicable N-No provisions in award W-Waived D-Deferred |
| Fiscal_Action | 2 | This code indicates at what intervals fiscal reports/bills are due to the sponsoring agency from the fiscal coordinators. If the reporting is not done at regular intervals the code will be instead the number of months past the project begin date that the next fiscal report/bill is due. | Blank-Unknown or Not Applicable M-Monthly Q-Quarterly S-Semi Annual A-Annual F-Final Only 00 through 99-Number of Months |
| Payment_Method | 1 | This code denotes the method of payment used for this award. | Blank-Not Known or Not Applicable A-Award Generates Payment (no action required) B-Bill/Invoice sent to the Sponsoring Agency C-Letter of Credit |
| CFDA_Number | 5 | Catalog of Federal Domestic Assistance Number | |
| FDP | 1 | Identifies whether the project is part of the Federal Demonstration Project (FDP) | Y-Projects Subject to FDP Regulations N-All Others |
| Document_Num | 12 | This is the number assigned to an award by the granting agency in addition to the award number. | |
| Overhead_Amount | 9.2 | This is the minimum amount of overhead that may be taken against this account (project/grant). | 100 |
| Intellectual_Property | 1 | This field identifies sponsoring agreements that have restrictive language on patent and/or copyrights. | N-No Restriction (default) P-Patent Restrictions C-Copyright Restrictions B-Both Copyright & Patent Restrictions |
| Fiscal_Action_Invoicing | 1 | Indicates the frequency that the sponsor requires RSP to submit –OR- the frequency that the sponsor will make under a fixed payment schedule. | Blank-No invoicing needed M-Monthly Q-Quarterly S-Semi-Annual A-Annual F-Final (one invoice at the end of the project) U-Up-front (one invoice at the beginning of the project) R-On-request (based on Sponsor's request) O-Other (based on deliverables or other frequencies not listed above) |
| Fiscal_Action_Reporting | 1 | Indicates the frequency that the sponsor requires RSP to submit financial reports. | Blank-No reporting needed M-Monthly Q-Quarterly S-Semi-Annual A-Annual F-Final (one report at the end of the project) U-Up-front (one report at the beginning of the project) R-On-request (based on Sponsor's request) O-Other (based on deliverables or other frequencies not listed above) |
| ACCOUNTING_PERIOD | 3 | The period within the fiscal year. Amounts will be ITD through the selected period. NOTE: Historic data is available for Accounting data only. ESIS information included in this data view is current. |
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 |
| ACCOUNTING_FISCAL_YEAR | 4 | The year that the budget covers on state funds. | 2005, 2006 etc. |
| Period_Ind | 1 | This indicator identifies the current & prior Accounting periods. | C = Current P = Prior O = Other |
| SFS_PROJECT_GRANT | 7 | This code is made up of the fund and the legacy account#. | e.g. 144GH01 |
| SFS_PROJECTS_LINKED | varies | Text representation of the internal link(s) between SFS and ESIS projects which are inferred by WISDM. These links determine which SFS transactions are included in view amount columns. The text displayed give the values you would specify in the WISDM Project Search to display detail for this project online. The value is a comma delimited list where each list item is of the form "[SFS Business Unit]-[SFS Project Code]([SFS Fiscal Year])" | e.g. For ESIS project 133-FJ21: "UWMSN-133FJ21(2006), UWMSN-133FJ21(2005),
UWMSN-133FJ21(2004), UWMSN-133FJ21(2003)" |
| BUDGET_ITD | 17.2 | The sum of all budget transactions posted since the beginning of the project/grant, up to and including the requested accounting period. | 00000000000010000.00 |
| ENCUMBRANCE_ITD | 17.2 | This is the net result of all changes to the open encumbrance balance that have occurred since the beginning of the project/grant. Please see the Special Considerations section of this document for more information on fringe and tuition encumbrances. | 00000000000010000.00 |
| EXPENSE_ITD | 17.2 | The sum of all payment transactions posted since the beginning of the project/grant, up to and including the requested accounting period. This amount does not include Sales Credits. | 00000000000010000.00 |
| RECEIPTS_ITD | 17.2 | The sum of all revenue transactions (excluding sales credits) posted since the beginning of the project/grant, up to and including the requested accounting period. | 00000000000010000.00 |
| SALES_CREDIT_ITD | 17.2 | The sum of all sales credit transactions posted since the beginning of the project/grant, up to and including the requested accounting period. | 00000000000010000.00 |
| EXP_AND_SALES_CR_ ITD | 17.2 | The sum of all payments and sales credit transactions posted since the beginning of the project/grant, up to and including the requested accounting period. Sales Credits are an offset to payments. | 00000000000010000.00 |
| CASH_BALANCE_ITD | 17.2 | The sum of revenues ITD less expenses ITD plus sales credit ITD. This is a calculated field indicating the cash balance available for a project grant at a point in time without consideration for encumbrances. | 00000000000010000.00 |
| UNENCUMBERED_CASH ITD | 17.2 | The sum of revenues ITD less expenses ITD less encumbrances ITD plus sales credit ITD. This is a calculated field indicating the unencumbered cash balance available for a project grant at a point in time. | 00000000000010000.00 |