Penn Computing

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

PENNERA_PROP_STAT_HIST Table

DWADMIN Schema

Explanation
Records information on how the status of a proposal (funding cycle) for a sponsored project has changed over time. Includes the proposal's ID (INSTITUTION_NO), the status, the effective date of that status, and information on when and by whom the status data was entered. There is one PENNERA_PROP_STAT_HIST record per proposal for each change in the proposal's status.

Common Uses

  • Reporting on proposals based on how their status has changed over time. ("How many proposals with Advance Accounts were eventually Awarded?")
Primary Key Indexed Data Elements Related Tables
PROP_NO
PROP_STAT_SEQUENCE_NO
PROP_NO
PROP_STAT_SEQUENCE_NO
PENNERA_INCREMENT
PENNERA_PERIOD
PENNERA_PERIOD_STAT_HIST
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_PROPOSAL_INVESTIGATOR
PENNERA_PROPOSAL_PARENT
PENNERA_PROP_REGULATORY_APPR
PENNERA_REQUEST


Cautions

  • There are two different unique identifiers for a proposal (funding cycle): INSTITUTION_NO and PROP_NO. INSTITUTION_NO is the identification number assigned by the University. PROP_NO is the identification number 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.
  • 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.
  • Some proposal status data is also stored in the PENNERA_PROPOSAL table: the current status for the proposal, and (where applicable) the date the proposal was submitted to the sponsor, the date it was disapproved by the sponsor, and the initial notification date (the Notice of Award date for the first award increment). For certain kinds of status analysis (such as seeing how long it takes for submitted proposals to be awarded), it may be easier to use the PENNERA_PROPOSAL table than to use the PENNERA_PROP_STAT_HIST table.
  • The PENNERA_PROP_STAT_HIST table stores information on how the status of a proposal (funding cycle) has changed over time. For the current status for a request (or increment) within the funding cycle, use the PENNERA_REQUEST (or PENNERA_INCREMENT) table. For information on how the request's (or increment’s) status has changed over time, use the PENNERA_REQ_STAT_HIST (or PENNERA_INCR_STAT_HIST) table.
  • PROPOSAL_STATUS has an initial value of ‘In Process’ 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). A proposal that is ‘In Process’ 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.) After the hard copy of the proposal is received in the Office of Research Services (ORS) and the necessary additional data is entered by ORS staff, the PROPOSAL_STATUS will be changed from ‘In Process’ to ‘Pending’. The SOMERA Interface creates records in PennERA only for proposals whose PROPOSAL_TYPE is ‘New Project’, ‘Competing (Renewal)’, ‘Change of Grantee Inst’, or ‘Revision’. The SOMERA Interface is not used to update data in PennERA.
  • 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’.
  • PennERA stores two dates in the proposal status history record: the PROPOSAL_STATUS_DATE (the date the status went into effect), and 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.
  • The Research Services System, or RSS, was used before October 14, 2003 to track information on sponsored projects. Records converted from this legacy system might have minimal information available in the PENNERA_PROP_STAT_HIST table.
  • Note: data for parent proposals is not currently entered in PennERA. The PennERA Proposals Data Collection has been designed to accommodate that data when it becomes available. Note that a program project will include more than one PENNERA_PROPOSAL record. There will be one for the program project as a whole, and one for each sub-project. The PENNERA_PROP_STAT_HIST records for a sub-project proposal may differ from the records for its parent proposal. They may also differ from the records for another sub-project of the same parent proposal. For example, if a parent has two sub-projects, the parent and both sub-projects might have been Pending at the same time, but when the Notice of Award arrives, the status of the parent and one sub-project might have changed to Awarded, while the status of the other sub-project might have changed to Unfunded.

Source
The Proposal Tracking module of PennERA, used at Penn since October 14, 2003 to track information on proposed and awarded grants and contracts.

PennERA is the University's electronic research administration system, providing support to faculty and research administrators for all sponsored project activity at the University of Pennsylvania.

PENNERA_PROP_STAT_HIST Table - Data Element Index    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