ESIS AWARD GEN

DATA VIEW NAME

ESIS_AWARD_GEN

OVERVIEW

This view displays Research Administration award information from the ESIS system. All information is displayed except for the social security numbers of the principal and corresponding principal investigators.

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 during 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 Name	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
FDP	CHAR 1
APPROPRIATION_NUM	CHAR 12
OVERHEAD_AMOUNT	PS 9.2

Renamed data field FEE_REMISSION to FEE
04/04 Added 3 new fields:

PROPOSAL_NUMBER	Varchar2 (40)
ROUTING_NUMBER	Varchar2 (6)
DONOR_DESC200	Varchar2 (200)

05/04 Added 5 new fields:

ESHS_IRB_NUMBER	Varchar2 (1)
ESHS_PROTOCOL_NUMBER	Varchar2 (11)
ES_ANIMAL_SUBJECT	Varchar2 (1)
ES_ANIMAL_SUBJECT_DATE	Varchar2 (4)
ESAN_PROTOCOL_NUMBER	Varchar2 (11)

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. The State fiscal year of this project.
Valid values are:
Blank
0-9

YEAR

Char(1)

The State fiscal year of this project.
Valid values are:
Blank
0-9

ACCT

Char(4)

The account number assigned to this award. This is used for records 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 are:

	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 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 are :

	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

suspense

overhead

faculty-staff salary support

student salary support

employee-interchange agreement

named professorships

research-health related (non-federal)

research-industrial (non-federal)

research-other (non-federal)

research (federal)

doctoral dissertation research

research career awards

research training program educational research training program traineeships

Research participation

institutes (academic, summer, advanced study etc.)

preparation of personnel in the education of handicapped

educational personnel development program

instruction-training-general

instruction-training-women

instruction-training-minority

instruction-training-in service

faculty developmen

fellowship-predoctoral

fellowship-postdoctoral

fellowship-special

fellowship-international

“NDEA Title IV”

“NDEA Title VI”

fellowship-graduate

G1	assigned
G2	disadvantaged
G3	campus fellowships
G4	E.B. Fred
G5	grad special
G6	supplement
G7	travel

fellowship-science faculty

fellowship supply allowance

fellowship-miscellaneous

construction-remodeling-renovations

landscaping-planting

exhibits

miscellaneous

patient care

equipment

computer usage

radio-television production

lecture programs

scholarships

short course programs

prizes, awards

graduate student support

loans

work-study

tuition and fees only

curriculum improvement and development

libraries

books, journals

language and area centers

travel

Title I of Higher Education Act of 1965

Title II of Higher Education Act of 1965

Title III of Higher Education Act of 1965

headstart program

upward bound program

conference, workshop, symposium

publications

editorial-secretarial expenses

institutional programs

international programs

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 just the first 50 characters of the description which 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_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_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 are:

	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 are:

	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 are

	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 are:

	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 at 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 are:

	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 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 for 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