What's in the tables?

(Here are many of the major tables in PRISM, and the major fields you'll find in them)
*These are not in alphabetical order. I simply listed the major tables in the order (roughly) of how often they're used by users.

Sponsor Financial Detail

Sponsor Status Code (field is SponStatusCd): This is a very important field. It determines whether the dollar amounts/time periods, etc. that you retrieve from this table, are from the Proposed, Awarded, or Committed Budget rows in the table.

0 (zero) = Proposed row
1 = Awarded row
2 = a Committed Budget Row (there can be more than one Committed Budget for an award)

Money

SponTotCostAmt: a dollar total (from the sponsor) for each Proposal #'s proposal, award, and each of its Committed Budgets
SponDirCostAmt: direct costs for each of these
SponIDCAmt: indirect costs for each of these

Time Periods:

SponBegDt and SponEndDt:
start and end of proposal
start and end of award
start and end of each Committed Budget

Proposal_to_Person

EmplID of Principal Investigator and each Co-Investigator

PerRoleTypCd: Role of each person associated with the proposal (either "PI" for Principal Investigator or "CO" for Co-Investigator)

Proc_DeptID: the deptID associated with either the PI or Co-Investigator on the proposal (whether it's the PI or Co-Investigator, depends on the value in the field PerRoleTypCd)

Proposal

ProjRepCd: Project Rep who handles the Proposal#

Direct_Sponsor: direct sponsor code for each Proposal #

Prime_Sponsor: prime sponsor code for each Proposal #

PrpslCurStatCd: current status for each Proposal#

Class: proposal type, e.g. On-campus research, Off-campus research, Instructional, Other Sponsored Activity, etc.

PrpslSubmitDt: date-submitted for each Proposal # (date when proposal was submitted to sponsor)

PrpslAwdDt: date-awarded for each award (date the sponsor awarded the proposal)

PrpslTitle: proposal title

PrpslTitle_Srch: Proposal title (in special form that allows easier searching than the PrpslTitle field does, because Oracle is case-senstive).

PrpslIDCRt: Proposed indirect cost rate

Financial Characteristics

Project_Grant: Project Grant Number

SponCntrctTxt: Sponsor's ID number for a Committed Budget (often known as "Contract#" or "Grant#", e.g. many NIH grant#s begin with "1 RO1...")

ComBdgtIDCRt: Indirect cost rate for a Committed Budget, which indicates a percent rate, e.g. 52 means 52%

Person

Data on all the people who have been loaded from HR into PRISM; only people with certain jobclasses have been loaded into PRISM:

EmplID

Last_Name, First_Name, Middle_Name: Name (in parts, e.g. Last Name, First Name, etc.)

Last_Name_Srch: Last Name (special field that allows easier searching than the Last_Name field does).

First_Name_Srch: First Name (special field that allows easier searching than the First_Name field does).

Work_Address1txt, Work_Address2txt, Work_Address3txt, Work_Address4txt: Office Work_Address (e.g. Dept Name is "Work_Address1txt")

Sex: Gender

Ethnic_Group: Race

Uniqname

Organization

Data on all the departments stored in PRISM (which are mainly those departments who have had a Principal Investigator or Co-Investigator on a proposal):

DeptID: department code
Dept_Descr: Department name

DeptDRDARowNum: A code ("Row Number") assigned by DRDA that identifies each School or College, e.g Nursing = 15)

DeptDRDARowName: The name ("Row Name") associated with above code, e.g. name for Row 15 is Nursing

Sponsor

Data on all sponsors stored in PRISM (which are mainly those sponsors that have appeared on at least one proposal):

Sponsor: Sponsor code from FinOps
Sponsor_Descr100: Sponsor name

Country: Country of sponsor (for foreign sponsors only)

Sponsor_Class: Sponsor class code (e.g. 1 = federal, 7 = U.S. Industry, 3 = U.S. Foundation, etc.

SponRptgSrtCd: Sponsor Sort Code (for federal sponsors only, e.g. 3 = Dept. of Defense, which covers several sponsors, including U.S. Army and U.S. Navy)

Proposal to Keyterm

Keytermtxt: Key terms (words or phrases) typed under the title on a Proposal Approval Form by the department submitting the PAF (e.g. magnetic resonance imaging). These are different from any words/phrases that appear in the proposal title, so as to facilitate text-searching.

Keytermtxt_Srch: contains the same values as Keytermtxt, except that they are in a format that facilitates searching (because Oracle is case-sensitive)

UM Financial Characteristics

Cost-sharing data on each Proposal# that has cost-sharing:

UM Status Code (field is UMStatusCd): This is an important field. It determines whether the cost-sharing data that you retrieve from this table, is from the Proposed, Awarded, or Committed Budget rows in the table.

0 (zero) = Proposed row (has "proposed cost-sharing" data)
1 = Awarded row (has "awarded cost-sharing" data)
2 = a Committed Budget Row (has "Committed Budget cost-sharing" data)
NOTE: There can be more than one Committed Budget for an award.

UM_Cs_DeptID: DeptID(s) doing the cost-sharing at Proposal, Award and Committed Budget stages

UMTotCostAmt: total cost-sharing dollars that each dept is responsible for, at each of these stages

UMDirCostAmt: direct costs contributed by each department, at each of these stages

UMImputedIDCAmt: imputed indirect costs contributed by each department, at each of these stages

3-28-03 JMB