Penn Computing

University of Pennsylvania
Penn Computing << go backback
PENNERA_PROP_STAT_HIST Table   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

PENNERA_PROP_STAT_HIST Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.

 

Data element Definition
HISTORY_STATUS

Indexed - yes
Format - char (1)
May be null? yes

A one-character code indicating whether the record stores the latest (current) status for the proposal, or an earlier (historical) status for the proposal.

Values:
C The record stores the current (latest) 
  status for the proposal
H The record stores a historical (past)
  status for the proposal
INSTITUTION_NO

Indexed - yes
Format - char (8)
May be null? no

The eight-digit sequence number used by the University to uniquely identify the proposal whose status history is tracked by this PENNERA_PROP_STAT_HIST record. Note that the value for INSTITUTION_NO includes leading zeroes.

Records with an
INSTITUTION_NO less than or equal to 05017400 were converted from the Research Services System (RSS, the system used to track proposals and awards before October 14, 2003). Other records are for proposals created in PennERA.

There are two different unique identifiers for a proposal: INSTITUTION_NO and PROP_NO. INSTITUTION_NO is used by the University. PROP_NO is used internally by the PennERA system. For a given record, INSTITUTION_NO does not have the same value as PROP_NO. While either one may be used when joining PennERA Proposals tables, INSTITUTION_NO is the one recommended for display in reports.

There is one PENNERA_PROP_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per PROP_STAT_SEQUENCE_NO

Example: 00216424

Values:
List of values not available

PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): any screen for the proposal; the Proposal Header (unlabelled, stationary section at the top of the screen); the number in the Proposal box in the upper right corner of the screen

LAST_EXTRACT_DATE

Indexed - no
Format - date
May be null? no

The date this proposal status history record was extracted from the PennERA Proposal Tracking system and loaded into the Warehouse.

Example: 10/28/2003

Values:
List of values not available.
MODIFIED_BY

Indexed - no
Format - char (8)
May be null? yes

In most cases, the value of MODIFIED_BY is the PennID of the person who entered (or last modified) the proposal status history record by hand. If the proposal status history record was created in PennERA via the SOMERA Interface (and not modified since then), MODIFIED_BY is ‘10080118’. (SOMERA is the School of Medicine’s Electronic Research Administration system.) Otherwise, if the proposal status record was last modified by a program (rather than by hand), MODIFIED_BY is null.

The name of the person who modified the proposal status is PENNERA_PEOPLE.NAME where PENNERA_PEOPLE.PENN_ID = PENNERA_PROPOSAL.MODIFIED_BY

Example: ‘10014514’

Values:
List of values not available

PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): General; Summary; Status link; below the Add section; the row for the proposal status history record; the PennID of the person whose name is shown in the Recorded By column (the PennID is not shown)

PROP_NO

Indexed - yes
Format - varchar2 (20)
May be null? no
The 10-digit sequence number used internally by the PennERA system to uniquely identify the proposal whose status history is tracked by this PENNERA_PROP_STAT_HIST record. Note that the value for PROP_NO includes leading zeroes.

There are two different unique identifiers for a proposal: INSTITUTION_NO and PROP_NO. INSTITUTION_NO is used by the University. PROP_NO is used internally by the PennERA system. For a given record, INSTITUTION_NO does not have the same value as PROP_NO. While either one may be used when joining PennERA Proposals tables, INSTITUTION_NO is the one recommended for display in reports.

There is one PENNERA_PROP_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per PROP_STAT_SEQUENCE_NO

Example: ‘0000000217’

Values:
List of values not available

PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): any screen for the proposal; the Proposal Header (unlabelled, stationary section at the top of the screen); the PROP_NO for the INSTITUTION_NO (INSTITUTION_NO is the number in the Proposal box in the upper right corner of the screen; PROP_NO is not shown)

PROP_STAT_SEQUENCE_NO

Indexed - yes
Format - number (20)
May be null? no
A sequence number uniquely identifying the status history record within the proposal to which it applies. Each proposal (INSTITUTION_NO) has a PROP_STAT_SEQUENCE_NO ‘1’. The maximum PROP_STAT_SEQUENCE_NO for a proposal is the number of status history records for that proposal.

There is one PENNERA_PROP_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per PROP_STAT_SEQUENCE_NO

Examples: 1, 5

Values:
List of values not available

PennERA's Proposal Tracking module does not display the proposal status sequence number.

PROPOSAL_STATUS

Indexed - no
Format - varchar2 (24)
May be null? yes
The status of the proposal, per the sponsor’s response to the first request associated with the proposal, as of the PROPOSAL_STATUS_DATE
Values:
Advance Account—one or more accounts have been 
   created to track an advance paid by the department 
   in expectation of an award
Awarded--the sponsor has awarded funding for the 
   proposal
Closed--the accounts for the proposal have been  
   closed in BEN
In Process--the initial status for all proposal 
   records (and only those proposal records) 
   created in PennERA on or after Sept. 6, 2005 
   via the SOMERA Interface (PennERA’s interface 
   with the School of Medicine’s Electronic 
   Research Administration system); the proposal 
   requires further processing before it is approved 
   for submission to the sponsor.  (This is true 
   even though there may be a non-null value for 
   PENNERA_PROPOSAL.SUBMITTED_DATE.)
Negotiation—the University and the sponsor 
   are negotiating the terms of the proposal
Negotiation Complete— the University and the
   sponsor have agreed upon the terms of the 
   proposal, and the University is awaiting 
   notification from the sponsor regarding awarded
   funding for the proposal
Pending—the Office of Research Services has 
   given its approval for the proposal to be
   submitted to the sponsor; the proposal is pending
   a decision by the sponsor
Revised Budget Requested—the sponsor has 
   requested a revised budget for the proposal
Under Development— the proposal record has 
   been created in the Proposal Development
   module of PennERA, but the proposal has not
   yet been submitted for University approval
Under Review—the proposal record (created
   in the Proposal Development module of 
   PennERA) has been submitted for University 
   approval (required before the proposal can be 
   submitted to the sponsor). Note: Proposals 
   that were created before Oct. 9, 2006 may have
   status history records with PROPOSAL_STATUS 
   ‘Under Review’ in cases where it ought to be 
   ‘Negotiation’.
Unfunded—the sponsor has notified the
   University that it will not fund the proposal
Withdrawn—the Principal Investigator has 
   withdrawn the proposal from consideration by 
   the sponsor

PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): General; Summary; Status link; below the Add section; the row for the proposal status history record; the value shown in the Status column

PROPOSAL_STATUS_DATE

Indexed - no
Format - date
May be null? yes

The date when the PROPOSAL_STATUS went into effect.

In addition to the PROPOSAL_STATUS_DATE, PennERA stores the RECORDED_DATE (the date the proposal status history record was created). When a proposal status history record is being created by a PennERA user, the values for the PROPOSAL_STATUS_DATE and the RECORDED_DATE have the system date as their default value. Before the record is saved, the PROPOSAL_STATUS_DATE is edited (if necessary) to reflect the date when the PROPOSAL_STATUS went into effect. The value of the RECORDED_DATE, however, is always the date when the proposal status history record was created. For example, if a proposal was Awarded as of 10/30/2003, and that change in status was recorded in PennERA on 11/13/2003, the proposal status history record's PROPOSAL_STATUS_DATE is 10/30/2003, and its RECORDED_DATE is 11/13/2003.

Some proposal status history records have a null value for the PROPOSAL_STATUS_DATE, the RECORDED_DATE, or both of them.

If the proposal status history record was created in PennERA via the SOMERA Interface, the PROPOSAL_STATUS_DATE was initially populated with the value of the Status Date in SOMERA. (SOMERA is the School of Medicine’s Electronic Research Administration system.)

Example: 10/30/2003

Values:
List of values not available.
PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): General; Summary; Status link; below the Add section; the row for the proposal status history record; the value shown in the Date column
PROPOSAL_STATUS_FY

Indexed - no
Format - char (4)
May be null? yes
The fiscal year at Penn in which the PROPOSAL_STATUS_DATE falls. Penn's fiscal year begins July 1 of one calendar year and ends June 30 of the next calendar year.

Example: 2004 (for a proposal status history record whose
PROPOSAL_STATUS_DATE is 10/30/2003)

Values:
List of values not available.
RECORDED_DATE

Indexed - no
Format - date
May be null? yes

The date when the proposal status history record was created in PennERA.

In addition to the RECORDED_DATE, PennERA stores the PROPOSAL_STATUS_DATE (the date the status went into effect). When a proposal status history record is being created by a PennERA user, the values for the PROPOSAL_STATUS_DATE and the RECORDED_DATE have the system date as their default value. Before the record is saved, the PROPOSAL_STATUS_DATE is edited (if necessary) to reflect the date when the PROPOSAL_STATUS went into effect. The value of the RECORDED_DATE, however, is always the date when the proposal status history record was created. For example, if a proposal was Awarded as of 10/30/2003, and that change in status was recorded in PennERA on 11/13/2003, the proposal status history record's PROPOSAL_STATUS_DATE is 10/30/2003, and its RECORDED_DATE is 11/13/2003.

Some proposal status history records have a null value for the PROPOSAL_STATUS_DATE, the RECORDED_DATE, or both of them.

Example: 11/13/2003

Values:
List of values not available.
PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): General; Summary; Status link; below the Add section; the row for the proposal status history record; the value shown in the Recorded Date column

 

PENNERA_PROP_STAT_HIST Table   Tables and Data Elements  PennERA Proposals Home   Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania