ESIS AWARDS

DATA VIEW NAME
ESIS_AWARDS
OVERVIEW
This view displays all Research Administration award information from the ESIS system.
LOAD FREQUENCY
The information in this view is no longer being refreshed and is current only to February 2008.
DATA CUSTODIAN
Research and Sponsored Programs
Contact: Mark Sweet
AUTHORIZATION
You must receive approval to access data from the data custodian. For information about how to get authorized for InfoAccess data views, connect to the InfoAccess Home Page
SPECIAL
CONSIDERATIONS
NOTE: Data on awards records that were created between 1971 and 1981 may have values in fields that do not conform to today’s definitions due to different standards and definitions used during that time period.
QUESTIONS
Contact: Division of Information Technology Help Desk
Phone: 264-HELP (4357)
LAST UPDATED
01/95 Added new field (AWARD NUMBER) to view documentation.
02/96 Added new field (PROJECT_DESC250) to view
documentation.
09/97 Added 15 new fields – detail documentation see field list:
Field
Format
DONOR_ADDRESS1 CHAR 50
DONOR_ADDRESS2 CHAR 50
EXPENDITURE_END CHAR 2
COST_SHARE_IND CHAR 1
FRINGE_BENEFIT CHAR 1
SENIOR_ACCT CHAR 4
HUMAN_SUBJECTS CHAR 1
HUMAN_SUBJ_DATE CHAR 4
TUITION CHAR 1
FISCAL_ACTION CHAR 2
PAYMENT_METHOD CHAR 1
CFDA_NUMBER CHAR 5

4/01 Added 2 new fields:

Field
Format
FISACAL_ACTION_INVOICING CHAR 1
FISACAL_ACTION_REPORTING CHAR 1
FDP CHAR 1
APPROPREATION_NUM CHAR 12
OVERHEAD_NUMBER PS   9.2

Renamed data field FEE_REMISSION to FEE
08/00 Added 5 new fields:

Field
Format
ESHS_IRB_NUMBER CHAR 1
ESHS_PROTOCOL_NUMBER CHAR 11
ES_ANIMAL_SUBJECT CHAR 1
ES_ANIMAL_SUBJECT_DATE CHAR 4
ESAN_PROTOCOL_NUMBER CHAR 11

05/04 Added 3 new fields:

Field
Format
PROPOSAL_NUMBER VARCHAR2(40)
ROUTING_NUMBER VARCHAR2(6)
DONOR_DESC200 VARCHAR2(200)

2/08 c4j View is no longer being updated.

DATA FORMATS
Primary Key Columns are identified with an asterisk(*)
Alphanumeric Data
VARCHAR2( N) (N) = maximum length. Field contains letters and/or numbers. Trailing blanks are eliminated.
Example: VARCHAR2 (5) could contain ‘ABC12’ or ‘ABC’ or ‘A’
Numeric Data
NUMBER (N) (N) = length. Field contains only numbers.
NUMBER (X,Y) = format for numbers with decimal positions.
X = total number of digits
Y = the number of digits after the decimal
Examples: NUMBER (6) represents 250012 NUMBER (6,2) represents 2500.12
Date
DATE – Date can be in various formats. Date functions available with your query tool can be used to format the date.
Examples: DATE (YYYYMMDD), DATE (YYDDD), DATE (DD-MM-YY)
Column Name Format Description
FUND Char(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.
YEAR Char(1) The State fiscal year of this project.
Valid values:
Blank
0-9
ACCT Char(4) The account 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 and grant funds.
STATUS Char(1) This describes the status of the proposal.
Valid codes:

Status Code
Definition
P
pending
W
withdrawn
R
rejected by Agency
A
approved by Agency
N
approved, but not funded
I
inactive
PROPOSAL_NUMBER Varchar2(40) A computer assigned sequential number that identifies the proposal.
ROUTING_NUMBER Varchar2(6) A unique number assigned to proposals from PALS, the internal RSP tracking
system.
ACCEPT_DATE Char(6) Date of the formal acceptance of the project by The Board of Regents.  Must be entered EXCEPT for account 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.
Format: YYMMDD
COMMIT_DATE Char(6) The sponsoring agency has funded this project for a certain length of time and conditionally committed itself to additional funding through this date.
Format: YYMMDD.
AWARD_DATE Char(6) The date an award is obligated by an agency or donor.
Format: YYMMDD.
AWARD_NUMBER Char(25) Number assigned to a project by the granting agency – not all records will have award numbers.
REQUEST_88_1 Char(1) Indicates whether an account 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.
Default    “N” = NO
“Y” = YES
If an account number is assigned as a result of an 88-1 request form, change the “N” to “Y” for Yes and enter the date the Dean  signed the 88-1 request form in the AWARD_DATE field. “A” = AWARDED and when the fully executed award is received, change the “Y” to “A” for awarded and change the AWARD_DATE field to the date of the Sponsor’s award.
BEGIN_DATE Char(6) The beginning date of the project.
Format: YYMMDD.
END_DATE Char(6) The ending date of the project.
Format: YYMMDD.
AGENCY Char(17) The abbreviation of an agency providing funding.
AGENCY1 Varchar2(5) The abbreviation of an agency providing funding.
AGENCY2 Varchar2(4) The abbreviation of an agency providing funding.
AGENCY3 Varchar2(4) The abbreviation of an agency providing funding.
DONOR_TYPE Char(1) A classification of the donor or granting agency.
Valid values:

Donor Type
Definition
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 Char(2) This code is used to identify types of projects.  Not all projects will have a value in this field

Project Code
Description
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 developmen
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_UDDS Char(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.
PROJECT_UNIT Varchar2(1) The first character of the UDDS charged with administering the project for the principal investigator.  It may or may not be the UDDS where the principal investigator resides.
PROJECT_UNIT_DIV Varchar2(3) The first three characters of the UDDS charged with administering the project for the principal investigator.  It may or may not be the UDDS where the principal investigator resides.
PROJECT_DESC Varchar2(50) The description of the project or award.  This is the first 50 characters of the description that is actually 250 characters long.
PI_NAME Char(30) The name of the principal investigator for the project.  A principal investigator is the person who administers the grant or submitted the proposal.
PI_SSN Char(9) The social security number of the principal investigator.
PI_UDDS Char(7) The UDDS (unit, division, department, and sub-department).
CO_PI Char(30) The name of the second person working on the project.
CO_SSN Char(9) The social security number of the second person working on the project.
CO_UDDS Char(7) The UDDS (unit, division, department, and sub-department) of the second person working on the project.
BUDGET_AMOUNT Number(11,2) This is the amount the donor has committed for funding the project.
OVERHEAD_BASE Char(1) This identifies the items of direct cost on which a calculation for overhead may be made as a part of the University’s monthly overhead generation process.
Valid values:

Base Codes
Description
Blank
not known
A
no calculation to be made by computer
B
salary and wages
C
total direct cost
D
total direct cost with certain exceptions (VAR 1)
E
total direct cost with certain exceptions (VAR 2)
OVERHEAD_RATE Number(5,2) The percentage to be applied against the appropriate direct cost items to calculate indirect cost.
REGENT_CATEGORY Char(1) These categories are used to group awards in a specific order for reports to the regents.  Valid codes:

Regent Codes
Description
I
instruction
R
research
S
student aid
P
physical plant
G
gift-in-kind
L
libraries
U
unrestricted
M
miscellaneous
E
extension and public services
Blank
not known
DOCUMENT_TYPE Char(1) Funding instrument used to make the award.
Valid Values:

Type Codes
Description
Blank
unknown
1
grant for funds 144, 145, 146, 149
2
gift/grant for fund 133
3
cost reimbursable contract
4
fixed price contract
5
endowment for fund 161
6
other agreements – purchase orders  etc.
AG_NUMBER Char(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.
USER_NUMBER Char(6) The initials of the person that last updated the record.
DONOR_DESC Char(50) The description of the donor.  This could be the donor’s name or an agency translation.
DONOR_DESC200 Varchar2(200) The full 200 character description of the donor. This could be the donor’s name or an agency translation.
NONDIRECT Char(2) Identifies Federal funds awards to the UW through a Non-federal agency acting as prime contractor.  If ‘ND’ entered then Federal agency is the AGENCY_CODE field, and prime contractor is in DONOR_DESCRIPTION.
PREDECESSOR Char(4) The account number that identified the project during the previous year.
SUCCESSOR Char(4) The account number that will be assigned to the project if it is renewed next year.
PROPERTY_TITLE Char(1) Indicates whether the University or the sponsor has title to equipment purchased or fabricated with project funds.
Valid values:

Title Codes
Description
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 Number(5) Identifies the minimum dollar amount in whole dollars by which title to equipment is retained by the sponsor.
FEE Char(1) Controls whether tuition remissions are charged or not charged to this project.
Valid values:

Fee Codes
Description
N
No provisions
W
Waived
D
Deferred
‘    ‘
Unknown / NA
LOADDATE Date The date of the most recent data load.
Format: YYYYMMDD HH24:MI:SS.
PROJECT_DESC250 Varchar2(250) This is the full 250 character description of the project description.
DONOR_ADDRESS Varchar2(100) Address of the donor of the award.
EXPENDITURE_END Char(2) The number of months past the project-end-date that expenditures may be charged against this project.
Valid Values:

00 – 99
‘   ‘
If unknown or not applicable
COST_SHARE_IND Char(1) This code indicates how this fund should be handled in relation to Research Administration’s Cost Sharing Report.
Valid Values:

C
Contractual Cost Sharing Account
N
Shouldn’t appear on Institutional Cost Sharing Report
I
Should appear on Institutional  Cost Sharing Report
‘    ‘ Not Applicable
FRINGE_BENEFIT Char(1) This code indicates whether fringe benefits costs are to be charged directly to the project
Valid Values:

‘    ‘
Not known (not allowed for 133, 144, and 149 funds)
Y
Charged directly to project
N
Not charged directly to project
SENIOR_ACCT Char(4) 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.
HUMAN_SUBJECTS Char(1) This switch indicates that human subjects are to be used in this project and the necessary approval of such must be obtained.
Valid Values:

Y
Human subjects are to be used
N
Human subjects will not be used
HUMAN_SUBJ_DATE Char(4) The date the approval was given to use human subjects in the project.
ESHS_IRB_NUMBER Char(1) Identifies which of the four 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 source.)
ESHS_PROTOCOL_NUMBER Char(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 Char(1) Indicates whether or not the project involves the use of vertebrate animals.
Valid values:
Y – Animal subjects are to be used
N – Animal subjects will not be used
ES_ANIMAL_SUBJECT_DATE Char(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_PROTOCOL_NUMBER Char(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 Char(1) This code indicates what provisions are made for tuition by this award.
Valid Values:

Tuition Codes
Description
N
No provisions in award
W
Waived
D
Deferred
‘     ‘
Unknown or Not Applicable
FISCAL_ACTION Char(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.
Valid Values:

Action Codes
Description
M
Monthly
Q
Quarterly
S
Semi Annual
A
Annual
F
Final Only
00 – 99
Number of Months
‘    ‘
Unknown or Not Applicable
PAYMENT_METHOD Char(1) This code denotes the method of payment used for this award.
Valid Values:

Method Codes
Description
A
Award generates payment (No action required)
B
Bill/Invoice sent to the sponsoring agency
C
Letter of Credit
‘    ‘
Not Known or Not Applicable
CFDA_NUMBER Char(5) Catalog of Federal Domestic Assistance number.
FDP Char(1) Identifies whether the project is part of the Federal Demonstration Project (FDP). Projects subject to FDP regulations will have a ‘Y’ all others will be N’.
DOCUMENT_NUMBER Char(12) This is the number assigned to an award by the granting agency in addition to the award-number.
OVERHEAD_AMOUNT Number(11,2) This is the minimum amount of overhead that may be taken against this account.
INTELLECTUAL_PROPERTY Char(1) This field identifies sponsoring agreements that have restrictive language on patents and/or copyrights.
Values allowed:

Property Codes
Description
N
No restriction (default)
P
Patent restrictions
C
Copyright restrictions
B
Both copyright and patent restrictions
FISCAL_ACTION_REPORTING Char(1) Indicates the frequency that the sponsor requires RSP to submit financial reports.
Valid values (ESIS will edit this field to verify that one of these are entered):

Action
Definition
M
Monthly
Q
Quarterly
S
Semi-annual (invoices every six months)
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)
Blank
No invoicing needed

NOTE: In some cases, both financial reporting and invoicing will be required. Example: American Heart requires annual financial reports (“A” in Fiscal Action – Financial Reporting) and provides payment on a quarterly basis (“Q” in Fiscal Action – Invoicing).

FISCAL_ACTION_INVOICING Char(1) Indicates the frequency that the sponsor requires RSP to submit – OR – the frequency that the sponsor will make under a fixed payment schedule.
Valid values (ESIS will edit this field to verify that one of these is entered):

Action
Definition
M
Monthly
Q
Quarterly
S
Semi-annual (invoices every six months)
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)
Blank
No invoicing needed
Skip to content