Penn Computing

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

PENNERA_PROP_REGULATORY_APPR Table - Data Element Index

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

 

Data element Definition
APPROVAL_DATE

Indexed - no
Format - date
May be null? yes

If known, this is the date approval was granted for the protocol (or other aspect of the proposal that is subject to regulatory oversight) that is identified by the REGULATORY_TYPE and the REFERENCE_NO. The value may be null.

If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, APPROVAL_DATE reflects what is shown on the Summary screen in the relevant PennERA protocol tracking module. Otherwise, the information reflects what is shown on the Approvals screen in PennERA's Proposal Tracking module, where the approval details might not be kept up-to-date.

Example: 11/15/2003

Values:
List of values not available.
PennERA source: If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the Human Subjects or Animal Use protocol tracking module; Summary screen; Summary section; Approval box; Date. For other PENNERA_PROP_REGULATORY_APPR
records,
Proposal Tracking module; Approvals screen; the Approved column in the row for the REGULATORY_TYPE and REFERENCE_NO.
APPROVAL_SEQUENCE_NO

Indexed - yes
Format - number (27)
May be null? no

A number that uniquely identifies the protocol (or other aspect of the proposal that is subject to regulatory oversight), within the proposal to which it applies. The PENNERA_PROP_REGULATORY_APPR table is set up to store one record per proposal (INSTITUTION_NO or PROP_NO), per APPROVAL_SEQUENCE_NO. However, at present, no two records have the same value for the APPROVAL_SEQUENCE_NO.

Examples: 0, 1672222

Values:
List of values not available. 
APPROVAL_STATUS

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

If known, this indicates the status of the approval for the protocol (or other aspect of the proposal that is subject to regulatory oversight) that is identified by the REGULATORY_TYPE and the REFERENCE_NO. The value may be null.

If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, APPROVAL_STATUS reflects what is shown on the Summary screen in the relevant PennERA protocol tracking module. Otherwise, the information reflects what is shown on the Approvals screen in PennERA's Proposal Tracking module, where the approval details might not be kept up-to-date.

Values:
Acknowledged
Acknowledged (CHOP/Pen
Administrative Ina
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Administrative Inactiv
Approved
Approved (CHOP/Penn)
Approved Status Wi
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Approved Status Withhe
Completed
Conditional Re-app
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Conditional Re-approva
Conditionally Appr
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Conditionally approved
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Disapproved
Expired
Issue Identified
Logged
New Protocol
Pending
Tabled
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Terminated
Unknown
Withdrawn Submissi
   (used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR)
Withdrawn Submission
Withheld Approval
[null]
PennERA source: If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the Human Subjects or Animal Use protocol tracking module; Summary screen; Summary section; Approval box; Status. For other PENNERA_PROP_REGULATORY_APPR
records,
Proposal Tracking module; Approvals screen; the Status column in the row for the REGULATORY_TYPE and REFERENCE_NO.
APPROVED_FROM

Indexed - no
Format - date
May be null? yes

If known, this indicates the date of the beginning of the period for which approval has been granted for the protocol (or other aspect of the proposal that is subject to regulatory oversight) that is identified by the REGULATORY_TYPE and the REFERENCE_NO. The value may be null.

If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, APPROVED_FROM reflects what is shown on the Summary screen in the relevant PennERA protocol tracking module. Otherwise, the information reflects what is shown on the Approvals screen in PennERA's Proposal Tracking module, where the approval details might not be kept up-to-date.

Example: 01/01/2004

Values:
List of values not available.
PennERA source: If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the Human Subjects or Animal Use protocol tracking module; Summary screen; Summary section; Approval box; Approved From. For other PENNERA_PROP_REGULATORY_APPR
records,
Proposal Tracking module; Approvals screen; click on the View icon in the row for the REGULATORY_TYPE and REFERENCE_NO; Approved From.
APPROVED_TO

Indexed - no
Format - date
May be null? yes

If known, this indicates the date of the end of the period for which approval has been granted for the protocol (or other aspect of the proposal that is subject to regulatory oversight) that is identified by the REGULATORY_TYPE and the REFERENCE_NO. The value may be null.

If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, APPROVED_FROM reflects what is shown on the Summary screen in the relevant PennERA protocol tracking module. Otherwise, the information reflects what is shown on the Approvals screen in PennERA's Proposal Tracking module, where the approval details might not be kept up-to-date.

Example: 12/31/2004

Values:
List of values not available.
PennERA source: If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the Human Subjects or Animal Use protocol tracking module; Summary screen; Summary section; Approval box; Approved To. For other PENNERA_PROP_REGULATORY_APPR
records,
Proposal Tracking module; Approvals screen; click on the View icon in the row for the REGULATORY_TYPE and REFERENCE_NO; Approved To.
INSTITUTION_NO

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

The eight-digit sequence number used by the University to uniquely identify the PennERA proposal for the project period (funding cycle) that has an aspect that is subject to regulatory oversight.

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 will 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_REGULATORY_APPR record per proposal (INSTITUTION_NO or PROP_NO), per APPROVAL_SEQUENCE_NO.

Example: '04076500'

Values:
Refer to the PENNERA_PROP_REGULATORY_APPR 
Table for values.
PennERA source: For HS and LA protocols that were linked to the proposal via the protocol tracking module, the Human Subjects or Animal Use protocol tracking module; the Summary / Linked Components screen; the Number column in the row for the proposal. For other PENNERA_PROP_REGULATORY_APPR
records,
the Proposal Tracking module; Approvals screen; the Proposal box in the upper right corner of the screen
LAST_EXTRACT_DATE

Indexed - no
Format - date
May be null? yes

The date this proposal regulatory approval record was extracted from the PennERA system and loaded into the Warehouse.

Example: 10/28/2003

Values:
List of values not available.
PROP_NO

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

The 10-digit number used internally by the PennERA system to uniquely identify the proposal for the project period (funding cycle) that has an aspect that is subject to regulatory oversight. 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 will 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_REGULATORY_APPR record per proposal (INSTITUTION_NO or PROP_NO), per APPROVAL_SEQUENCE_NO

Example: ‘0000033499’

Values:
List of values not available. 

PennERA source: For HS and LA protocols that were linked to the proposal via the protocol tracking module, the Human Subjects or Animal Use protocol tracking module; the Summary / Linked Components screen; the prop_no for the proposal (the Number column in the row for the proposal shows the proposal's Institution No.; prop_no is not shown). For other PENNERA_PROP_REGULATORY_APPR
records,
the Proposal Tracking module; Approvals screen; the Proposal box in the upper right corner of the screen; the prop_no for the proposal (the Proposal box shows the proposal's Institution No.; prop_no is not shown)

PROT_NO

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

The 10-digit number used internally by the PennERA system to identify the Human Subjects (HS) or Lab Animals (LA) protocol that is denoted by the REGULATORY_TYPE and the REFERENCE_NO. Note that the value for PROT_NO includes leading zeroes.

PROT_NO is null if:

  • the REGULATORY_TYPE is not 'Human Subjects' or 'Lab Animals', or
  • the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' but the REFERENCE_NO is not found as the Institution No. for a protocol in the relevant PennERA protocol tracking module.

PROT_NO does not uniquely identify an HS or LA protocol within the PENNERA_PROP_REGULATORY_APPR
table. No two HS protocols have the same PROT_NO, and no two LA protocols have the same PROT_NO, but the PROT_NO for an HS protocol could also be used for an LA protocol.

There are two different identifiers for an HS or LA protocol in the PENNERA_PROP_REGULATORY_APPR table: REFERENCE_NO and PROT_NO. REFERENCE_NO is the 6-digit Institution No. assigned to the protocol by the University and used to identify the protocol in the relevant PennERA protocol tracking module. PROT_NO is the identification number used internally by the PennERA system. For a given record, REFERENCE_NO will not have the same value as PROT_NO. REFERENCE_NO is the data element recommended for display in reports.

Example: ‘0000352900’

Values: List of values not available. 

PennERA source: the Human Subjects or Animal Use protocol tracking module; Summary screen; the prot_no for the protocol whose 6-digit Institution No. is shown on the first line of the header section at the top of the screen (the 6-digit Institution No. is the University's identifier for the protocol; prot_no is not shown)
PROTOCOL_ATTACHED

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

In combination with PROT_NO, this indicates whether or not the proposal has been linked to the protocol in the PennERA system. The proposal is identified by the INSTITUTION_NO. The protocol (or other aspect of the proposal that is subject to regulatory oversight) is identified by the REGULATORY_TYPE and the REFERENCE_NO.

‘Human Subjects’ (HS) and ‘Lab Animals’ (LA) are the only REGULATORY_TYPEs whose protocols can be linked to the proposal in the PennERA system. The protocol is linked to the proposal if PROTOCOL_ATTACHED is 'Attached' and PROT_NO is not null. The value for PROTOCOL_ATTACHED ought to be ‘Not Attached’ or null for other REGULATORY_TYPEs.

Some sponsors require that, if the proposal involves any protocols, those protocols must be approved before the proposal is submitted. Other sponsors have a Just In Time policy—proposals submitted to those sponsors must have their protocols approved before funding is awarded. In PennERA, the approved HS and LA protocols for a proposal also ought to be linked to the proposal before it is awarded.

Values: 
Attached 
   the proposal has been linked in the PennERA 
   system to the protocol that is identified by 
   the REGULATORY_TYPE and the REFERENCE_NO
(Note: this value is valid only for HS and
LA protocols, and only if PROT_NO is not null.)
Future the protocol that is identified by the REGULATORY_TYPE and the REFERENCE_NO has
not been linked to the proposal in the
PennERA system, but is expected to be linked
to it at some future date
. (Note: this
value is valid only for HS and LA protocols,
and ought to be used only in the annual
snapshot table, SPFY_PENNERA_PROP_REG_APPR.)
Not Attached the proposal has not been linked in the PennERA system to the protocol (or other aspect of the proposal that is subject to regulatory oversight) that is identified by the REGULATORY_TYPE and the REFERENCE_NO
[null] the protocol (or other aspect of the
proposal that is subject to regulatory
oversight) is not an HS or LA protocol, or
it is not known whether or not the proposal
has been linked in the PennERA system to
the protocol that is identified by
the REGULATORY_TYPE and the REFERENCE_NO
PennERA source: For HS and LA protocols that were linked to the proposal via the protocol tracking module, the Human Subjects or Animal Use protocol tracking module; the Summary / Linked Components screen; if the proposal is listed, it is 'Attached'. For other PENNERA_PROP_REGULATORY_APPR
records,
Proposal Tracking module; Approvals screen; click on the View icon in the row for the REGULATORY_TYPE and REFERENCE_NO; the value for the selected Protocol radio button.
REFERENCE_NO

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

A character string, whose value is the code used by the relevant regulatory office to identify the protocol (or other aspect of the proposal that is subject to regulatory oversight). If it is not null, the value for REFERENCE_NO may contain letters, numerals, and other characters.

REFERENCE_NO does not uniquely identify a protocol or activity within the PENNERA_PROP_REGULATORY_APPR
table. For example, no two Human Subjects (HS) protocols have the same REFERENCE_NO, and no two Lab Animals (LA) protocols have the same REFERENCE_NO, but the REFERENCE_NO for an HS protocol could also be used for an LA protocol or for another protocol.

If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, REFERENCE_NO is the 6-digit Institution No. assigned to the protocol by the University and used to identify the protocol in the relevant PennERA protocol tracking module. Note: (1) the REFERENCE_NO for an HS or LA protocol uses only numerals, and includes leading zeroes; (2) a given HS or LA protocol may be associated with more than one proposal.

The PROT_NO is the protocol identification number used internally by PennERA. For a given record, REFERENCE_NO will not have the same value as PROT_NO. REFERENCE_NO is the data element recommended for display in reports.

Examples: ‘702802’, '08-284', 'V016 37-0018-07'

Values: List of values not available. 

PennERA source: For HS and LA protocols that were linked to the proposal via the protocol tracking module, the Human Subjects or Animal Use protocol tracking module; the Summary screen; Summary section; Institution Number. For other PENNERA_PROP_REGULATORY_APPR
records,
the Proposal Tracking module; Approvals screen; Reference column
REGULATORY_TYPE

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

Describes the area of the proposal that is subject to regulatory oversight, or names the administrative group responsible for approving the protocol (or other aspect of the proposal that is subject to regulatory oversight).

If the REGULATORY_TYPE is not ‘Human Subjects’ or ‘Lab Animals’, the proposal has, at most, one record per REGULATORY_TYPE.

If the REGULATORY_TYPE is ‘Human Subjects’ or ‘Lab Animals’, and the REFERENCE_NO is not null, the proposal has one record per REGULATORY_TYPE per REFERENCE_NO. Note: a given ‘Human Subjects’ or ‘Lab Animals’ protocol may be associated with more than one proposal.

In some instances, the proposal has one or more ‘Human Subjects’ or ‘Lab Animals’ records where the REFERENCE_NO is null. This is the case when it is known that the proposal will involve one or more such protocols, but the protocols' Institution Nos. (REFERENCE_NOs) are unknown.

Values:
Biosafety Committee
   used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR
Carcinogens, teratogens or mutagens
CL APPROVAL 
   clinical trial; used only in the annual 
   snapshot table, SPFY_PENNERA_PROP_REG_APPR
Clinical Trial
Environmental Health & Radiation Safety 
   used only in the annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR
Hazardous Materials
Human Subjects
In vitro formation of recombinant DNA
Lab Animals
Potentially Infectious Agents
Radiation Safety
rDNA 
   recombinant DNA; used only in the 
   annual snapshot table, 
   SPFY_PENNERA_PROP_REG_APPR
Select Agents 
   biological agents or toxins deemed 
   by the federal government to be a 
   threat to the public, animal or plant 
   health, or to animal or plant products
PennERA source: For HS and LA protocols that were linked to the proposal via the protocol tracking module, 'Human Subjects' (for records exported from the Human Subjects protocol tracking module) or 'Lab Animals' (for records exported from the Animal Use protocol tracking module). For other PENNERA_PROP_REGULATORY_APPR
records,
the Proposal Tracking module; Approvals screen; Type column
REVIEW_CATEGORY

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

This indicates the type of review conducted by the University office that monitors compliance in matters involving the protocol (or other aspect of the proposal that is subject to regulatory oversight) that is identified by the REGULATORY_TYPE and the REFERENCE_NO. The value of REVIEW_CATEGORY may be null.

If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the REVIEW_CATEGORY reflects what is shown on the Summary screen in the relevant PennERA protocol tracking module. Otherwise, the information reflects what is shown on the Approvals screen in PennERA's Proposal Tracking module, where the approval details might not be kept up-to-date.

Values:
Administrative 
   used for activities that do not require
   board review, but do require administrative 
   acknowledgement--for example, the use of publicly 
   available data with no information that could
   be used to identify an individual person
Designated (IACUC)
   valid for 'Lab Animals' [LA] protocols only
Exempt 
   valid for 'Human Subjects' [HS] protocols 
   only; used for protocols involving non-invasive, 
   minimal-risk procedures
Expedited 
   valid for HS protocols only; used for 
   protocols with minor modifications or amendments
Full 
   reviewed at a full board meeting of the IRB 
   (Institutional Review Board, for HS protocols) 
   or the IACUC (Institutional Animal Care and 
   Use Committee, for LA protocols)
[null] 
   the review category is unknown
PennERA source: If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the Human Subjects or Animal Use protocol tracking module; Summary screen; Summary section; Review Category. For other PENNERA_PROP_REGULATORY_APPR
records,
Proposal Tracking module; Approvals screen; the Review column in the row for the REGULATORY_TYPE and REFERENCE_NO.
PENNERA_PROP_REGULATORY_APPR 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