Penn Computing

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

PENNERA_REQ_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 request, or an earlier (historical) status for the request.

Values:
C The record stores the current (latest) status 
  for the request 
H The record stores a historical (past) status 
  for the request 
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 request status history is tracked by this PENNERA_REQ_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_REQ_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per REQUEST_NUMBER, per REQ_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 request status 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

The PennID of the person who entered (or last modified) the request status record by hand. The name of the person can be found in the PENNERA_PEOPLE table. If the request status 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.) If MODIFIED_BY is null, the request status record was entered via a program, rather than by hand.

Example: ‘10022206’

Values:
List of values not available.
PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): Budget; Requested; Requested Budget section; the row for the request (the first row in the Requested Budget section is for REQUEST_NUMBER 1, the second row is for REQUEST_NUMBER 2, etc.); Status link; below the Add section; the row for the request 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 request status history is tracked by this PENNERA_REQ_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_REQ_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per REQUEST_NUMBER, per REQ_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)

RECORDED_DATE

Indexed - no
Format - date
May be null? yes

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

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

Some request status history records have a null value for the REQUEST_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): Budget; Requested; Requested Budget section; the row for the request (the first row in the Requested Budget section is for REQUEST_NUMBER 1, the second row is for REQUEST_NUMBER 2, etc.); Status link; below the Add section; the row for the request status history record; the value shown in the Recorded Date column
REQUEST_NUMBER

Indexed - yes
Format - varchar2 (6)
May be null? no
A sequence number uniquely identifying the request within the proposal. Each proposal (INSTITUTION_NO) has a REQUEST_NUMBER ‘1’. The maximum REQUEST_NUMBER for a proposal is the number of requests that apply to that proposal.

There is one PENNERA_REQ_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per REQUEST_NUMBER, per REQ_STAT_SEQUENCE_NO

Examples: 3, 11

Values:
List of values not available

PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): Budget; Requested; Requested Budget section; the row number for the request. (The first row in the Requested Budget section is for REQUEST_NUMBER 1, the second row is for REQUEST_NUMBER 2, etc.)

REQUEST_STATUS

Indexed - no
Format - varchar2 (24)
May be null? yes

The status of the request, per the sponsor’s response to the request, effective as of the REQUEST_STATUS_DATE.

Note: proposal records created on or after Oct. 9, 2006 may have been created in the Proposal Development (PD) module of PennERA. If such a proposal has not yet been submitted to its sponsor, there are no PENNERA_REQ_STAT_HIST records for any of its requests. (If the proposal record was created in PD, PENNERA_PROPOSAL.PD_FLAG = 'Y'. If the proposal has been submitted to the sponsor, its proposal status history includes a record for the 'Pending' status. The proposal status history is stored in the PENNERA_PROP_STAT_HIST table.)

Values:

Awarded--the sponsor has awarded funding for the
   request
Future--the sponsor has notified the University
   of its intent to award funding for the request,
   which applies to a future period (“out year”)
Future Pending--the sponsor has notified
   the University of its intent to award funding
   for the request, and the Principal Investigator has
   applied for a non-competing continuation for 
   the period to which the request applies
Pending--the Office of Research Services has 
   given its approval for the request to be 
   submitted to the sponsor; the request is 
   pending a decision by the sponsor
Revised Budget Requested--the sponsor has asked that the
   Principal Investigator submit a revised budget for the 
   request
Unfunded--the sponsor has notified the University that it
   will not award funding for the request
Withdrawn--the Principal Investigator has withdrawn the 
   request from consideration by the sponsor
PennERA source (assuming the proposal record has been opened in the Proposal Tracking module): Budget; Requested; Requested Budget section; the row for the request (the first row in the Requested Budget section is for REQUEST_NUMBER 1, the second row is for REQUEST_NUMBER 2, etc.); Status link; below the Add section; the row for the request status history record; the value shown in the Status column

REQUEST_STATUS_DATE

Indexed - no
Format - date
May be null? yes

The date when the REQUEST_STATUS went into effect.

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

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

If the request status history record was created in PennERA via the SOMERA Interface, the REQUEST_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): Budget; Requested; Requested Budget section; the row for the request (the first row in the Requested Budget section is for REQUEST_NUMBER 1, the second row is for REQUEST_NUMBER 2, etc.); Status link; below the Add section; the row for the request status history record; the value shown in the Date column

REQUEST_STATUS_FY

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

The fiscal year at Penn in which the REQUEST_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 request status history record whose
REQUEST_STATUS_DATE is 10/30/2003)

Values:
List of values not available
REQ_STAT_SEQUENCE_NO

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

There is one PENNERA_REQ_STAT_HIST record per proposal (INSTITUTION_NO or PROP_NO), per REQUEST_NUMBER, per REQ_STAT_SEQUENCE_NO

Examples: 1, 5

Values:
List of values not available

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

 

PENNERA_REQ_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