Mapping the SPONPROJ Universe to the PennERA Proposals Universe
last updated October 28, 2003
The SPONPROJ Universe describes data in the Data Warehouse that was extracted from the Research Services system (RSS), the system used
before PennERA Proposal Tracking was implemented.  The PennERA Proposals Universe describes data in the Data Warehouse that is extracted
from the Proposal Tracking module of PennERA, implemented on October 14, 2003.  The chart below is designed to help those who need to convert
queries from using SPONPROJ to using  the PennERA Proposals Universe.
NOTE:  few data elements match exactly; exact matches are preceded by an asterisk (*)
SPONPROJ Table	SPONPROJ Element	PennERA Proposals Equivalent (Table.Element)
AGENCY	AGENCY_CODE	PENNERA_SPONSOR.SPONSOR_CODE (but this is a 5-character code)
AGENCY	AGENCY_NAME	PENNERA_SPONSOR.SPONSOR_DESC (but this is a 150-character value)
AGENCY	PARENT_AGENCY_CODE	no longer used
AWARD	ACCOUNT_NUMBER	* PENNERA_INCREMENT.AWARD_LEGACY_ACCOUNT
AWARD	AGENCY_CODE	PENNERA_INCREMENT.SPONSOR_CODE
AWARD	AGENCY_REF_NUMBER	* PENNERA_INCREMENT.SPONSOR_AWARD_ID
AWARD	AIS_PRINT_DATE	PENNERA_INCREMENT.AIS_ISSUE_DATE
AWARD	ANIMALS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Lab Animals'
AWARD	ANIMALS_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system.  However, PennERA Proposals records involving animal protocols will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Lab Animals'.  As protocol data is linked to proposal data in PennERA, protocol ID's will become available in the PennERA Proposals collection in the Warehouse.
AWARD	AWARD_ACCOUNT_NUMBER	PENNERA_INCREMENT.AWARD_GL_ACCOUNT (includes Object code 3000)
AWARD	AWARD_ACCOUNT_TYPE	no longer used
AWARD	AWARD_BC	* PENNERA_INCREMENT.AWARD_BC
AWARD	AWARD_CNAC	* PENNERA_INCREMENT.AWARD_CNAC
AWARD	AWARD_CREATE_DATE	PENNERA_INCREMENT.AWARD_DATE (this is not the system date when the increment record was created, but is the award date per the Notice of Award)
AWARD	AWARD_CREF	* PENNERA_INCREMENT.AWARD_CREF
AWARD	AWARD_FUND	* PENNERA_INCREMENT.AWARD_FUND
AWARD	AWARD_ID	the combination of PENNERA_INCREMENT.INSTITUTION_NO, PENNERA_INCREMENT.PERIOD_NUMBER, and PENNERA_INCREMENT.INCREMENT_NUMBER
AWARD	AWARD_ORG	* PENNERA_INCREMENT.AWARD_ORG
AWARD	AWARD_PROGRAM	* PENNERA_INCREMENT.AWARD_PROGRAM
AWARD	AWD_RESP_ORG	* PENNERA_INCREMENT.INCREMENT_RESP_ORG_CODE
AWARD	AWD_RESP_SCHOOL	* PENNERA_INCREMENT.INCREMENT_RESP_SCHOOL_CODE
AWARD	CARCINOGENIC_MATERIALS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Carcinogens/teratogens/mutagens'
AWARD	CLINICAL_TRIAL_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'CL APPROVAL'
AWARD	CLINICAL_TRIAL_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system (RSS).  However, PennERA Proposals records for clinical trials that were converted from RSS will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'CL APPROVAL'.  For proposals created in PennERA, see PENNERA_PROPOSAL.CLINICAL_TRIAL_PROTOCOL_ID
AWARD	CO_PI_HOME_ORG	PENNERA_PROPOSAL_INVESTIGATOR.HOME_ORG_CODE where PENNERA_PROPOSAL_INVESTIGATOR.PI_FLAG = 'N'  (NOTE:  co-investigators are linked to the PennERA proposal (for the project period), not to the award increment; there can be more than one co-investigator per PennERA proposal)
AWARD	CO_PI_HOME_SCHOOL	PENNERA_PROPOSAL_INVESTIGATOR.HOME_SCHOOL_CODE where PENNERA_PROPOSAL_INVESTIGATOR.PI_FLAG = 'N'  (NOTE:  co-investigators are linked to the PennERA proposal (for the project period), not to the award increment; there can be more than one co-investigator per PennERA proposal)
AWARD	COMMITTED_YEARS	Not converted. The meaning of "committed years" depends on the sponsor.  SPONPROJ stored the number of committed years beyond the year (budget period) of the AWARD record.  For records for proposals (for project periods) created in PennERA, data for each committed year (budget period) is stored in a PENNERA_PERIOD record with a PERIOD_STATUS of 'Future' or 'Future Pending'.
AWARD	CREATE_DATE	AWARD.CREATE_DATE is the system date when the the record was created in RSS for the proposal (budget period request) associated with the AWARD.  PennERA Proposal Tracking does not store this information as such,  but for converted records, the date for the earliest RSS proposal (for the budget period) within the PennERA proposal (for the project period) is stored in PENNERA_PROPOSAL.PROCESSED_DATE
AWARD	CURRENT_BUDGET_END (formerly known as END_DATE)	* PENNERA_INCREMENT.AWARDED_PERIOD_END_DATE
AWARD	CURRENT_BUDGET_START (formerly known as START_DATE)	* PENNERA_INCREMENT.AWARDED_PERIOD_START_DATE
AWARD	DATE_AWARDED	* PENNERA_INCREMENT.AWARD_DATE
AWARD	DIRECT_COST_AMOUNT	* PENNERA_INCREMENT.AWARDED_DIRECT_COSTS
AWARD	DNA_SUBJECT_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'rDNA'
AWARD	DOMESTIC_TRAVEL_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD	EQUIPMENT_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD	EXTENSION_CODE	PENNERA_PROPOSAL.PROPOSAL_TYPE
AWARD	FISCAL_YEAR_END	* PENNERA_INCREMENT.AWARDED_PERIOD_END_FY
AWARD	FISCAL_YEAR_START	* PENNERA_INCREMENT.AWARDED_PERIOD_START_FY
AWARD	FISCAL_YR_STATUS	This is the fiscal year in which AWARD.DATE_AWARDED fell.  The equivalent in the PennERA Proposals collection is the fiscal year in which the PENNERA_INCREMENT.AWARD_DATE fell.  If the month of the AWARD_DATE falls between January and June, inclusive, the fiscal year is the same as the year of the AWARD_DATE; otherwise, it is 1 + the year of the AWARD_DATE.
AWARD	FOREIGN_TRAVEL_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD	FUND_RESPONSIBLE_ORG	* PENNERA_INCREMENT.FUND_RESPONSIBLE_ORG
AWARD	GRANT_CONTRACT_CODE	PENNERA_PROPOSAL.INSTRUMENT_TYPE
AWARD	HUMAN_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system.  However, PennERA Proposals records involving protocols for human subjects will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Human Subjects'.  As protocol data is linked to proposal data in PennERA, protocol ID's will become available in the PennERA Proposals collection in the Warehouse.
AWARD	HUMAN_SUBJECTS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Human Subjects'
AWARD	INDIRECT_COST_AMOUNT	* PENNERA_INCREMENT.AWARDED_INDIRECT_COSTS
AWARD	INDIRECT_COST_RATE	PENNERA_INCREMENT.INCREMENT_F_AND_A_RATE (Note that in SPONPROJ, AWARD.INDIRECT_COST_RATE was the rate specific to that AWARD (increment).  PENNERA_INCREMENT.INCREMENT_F_AND_A_RATE is designed to store the rate specific to the increment, but, for records converted from the Research Services system (RSS), it is the awarded rate that was stored at the RSS proposal (budget period request) level.  It is unlikely, but possible, that an RSS proposal had more than one AWARD, each with a different indirect cost rate.  In such cases, the value of PENNERA_INCREMENT.INCREMENT_F_AND_A_RATE may differ from the value of AWARD.INDIRECT_COST_RATE for a given AWARD (increment).)
AWARD	INFECTIOUS_AGENTS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Potentially Infectious Agents'
AWARD	LAST_EXTRACT_DATE	* PENNERA_INCREMENT.LAST_EXTRACT_DATE
AWARD	MODIFY_DATE	RSS stored the system date when an RSS proposal (budget period request) record was modified.  The PennERA Proposals data collection does not store this information at the increment level.  For the date of the latest modification to any information related to the PennERA proposal (for the project period), its budget periods, its budget period requests, or its increments, see PENNERA_PROPOSAL.MODIFY_DATE.
AWARD	NIH_GRANT_ACTIVITY_CODE	* PENNERA_INCREMENT.NIH_GRANT_ACTIVITY_CODE
AWARD	NIH_GRANT_ADMIN_ORG	* PENNERA_INCREMENT.NIH_GRANT_ADMIN_ORG
AWARD	NIH_GRANT_APPLICATION_TYPE	* PENNERA_INCREMENT.NIH_GRANT_APPLICATION_TYPE
AWARD	NIH_GRANT_SERIAL_NUMBER	* PENNERA_INCREMENT.NIH_GRANT_SERIAL_NUMBER
AWARD	NIH_GRANT_SUPPLEMENT	* PENNERA_INCREMENT.NIH_GRANT_SUPPLEMENT
AWARD	NIH_GRANT_YEAR	* PENNERA_INCREMENT.NIH_GRANT_YEAR
AWARD	ORA_CONTACT_INITIALS	Not converted.  There are plans to create a table in Proposal Tracking that will list the Office of Research Services contact (and other contacts) by org.  When that table is available, the data will be exported to the Warehouse.
AWARD	ORA_CONTACT	Not converted.  There are plans to create a table in Proposal Tracking that will list the Office of Research Services contact (and other contacts) by org.  When that table is available, the data will be exported to the Warehouse.
AWARD	ORA_PURPOSE_CODE	PENNERA_PROPOSAL.PROGRAM_TYPE
AWARD	ORG_LOG_NUMBER	There is no equivalent for this data element in the PENNERA_INCREMENT table.  In PennERA, a request for a budget period (and its log number) cannot be linked to an increment.  For the log number for a request, see PENNERA_REQUEST.SCHOOL_LOG_NUMBER.
AWARD	ORIGINAL_PROPOSAL_DATE	There is no equivalent for this data element in the PENNERA_INCREMENT table.  In PennERA, a request for a budget period (and the date it was submitted to the sponsor) cannot be linked to an increment.  For the submission date for a request, see PENNERA_REQUEST.REQUEST_SUBMITTED_DATE
AWARD	OTHER_EXPENSES_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD	PI_HOME_ORG	PENNERA_INCREMENT.PI_HOME_ORG_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for  the AWARD_GL_ACCOUNT (the ACCOUNT_RESP_INVESTIGATOR).  The home org. for the ACCOUNT_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.HOME_ORG_CODE where PENNERA_INCREMENT.ACCOUNT_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_INCREMENT.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
AWARD	PI_HOME_SCHOOL	PENNERA_INCREMENT.PI_HOME_SCHOOL_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for  the AWARD_GL_ACCOUNT (the ACCOUNT_RESP_INVESTIGATOR).  The home school for the ACCOUNT_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.HOME_SCHOOL_CODE where PENNERA_INCREMENT.ACCOUNT_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_INCREMENT.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
AWARD	PI_PRI_APPT_ORG	PENNERA_INCREMENT.PI_PRI_APPT_ORG_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for  the AWARD_GL_ACCOUNT (the ACCOUNT_RESP_INVESTIGATOR).  The primary appointment org. for the ACCOUNT_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_ORG_CODE where PENNERA_INCREMENT.ACCOUNT_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_INCREMENT.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
AWARD	PI_PRI_APPT_SCHOOL	PENNERA_INCREMENT.PI_PRI_APPT_SCHOOL_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for  the AWARD_GL_ACCOUNT (the ACCOUNT_RESP_INVESTIGATOR).  The primary appointment school for the ACCOUNT_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_SCHOOL_CODE where PENNERA_INCREMENT.ACCOUNT_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_INCREMENT.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
AWARD	PROJECT_COST_SHARING_AMOUNT	* PENNERA_PROPOSAL.AWARDED_COST_SHR_TOTAL
AWARD	PROJECT_COST_SHARING_SOURCE	not converted; not used consistently in RSS
AWARD	PROJECT_ID	PENNERA_PROPOSAL.FIRST_INSTITUTION_NO
AWARD	PROJECT_SEGMENT_ID	the combination of PENNERA_INCREMENT.INSTITUTION_NO and PENNERA_INCREMENT.PERIOD_NUMBER
AWARD	PROP_RESP_ORG	PENNERA_INCREMENT.PROPOSAL_RESP_ORG_CODE.  (NOTE:  This is the org. responsible for the PennERA proposal (for the project period), NOT the org. responsible for the request (for the budget period).  In PennERA, a request (and its responsible org.) cannot be linked to an increment. )
AWARD	PROP_RESP_SCHOOL	PENNERA_INCREMENT.PROPOSAL_RESP_SCHOOL_CODE (NOTE:  This is the school responsible for the PennERA proposal (for the project period), NOT the school responsible for the request (for the budget period).  In PennERA, a request (and its responsible school) cannot be linked to an increment.)
AWARD	PROPOSAL_ID	the combination of PENNERA_PERIOD.INSTITUTION_NO and PENNERA_PERIOD.PERIOD_NUMBER (an award increment cannot be linked to a request)
AWARD	RADIOACTIVE_MATERIAL_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Radiation Safety'
AWARD	RADIOACTIVE_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system.  However, PennERA Proposals records for projects involving radioactive materials will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Radiation Safety'.
AWARD	SUB_CONTRACTOR_AMOUNT	In SPONPROJ, AWARD.AWARD-SUBCONTRATOR-AMOUNT is the total amount of all subcontracts combined for the PROPOSAL_ID.  In SPONPROJ, this is stored only in the AWARD record for the prime account, to prevent double-counting the amount.  AWARD.AWARD-SUBCONTRATOR-AMOUNT was not converted, but information on each subcontract was converted.  Documentation on subcontract data is under development.
AWARD	TITLE	PENNERA_PROPOSAL.PROJECT_TITLE (the value may be longer than the value stored in SPONPROJ)
AWARD	TOTAL_AGENCY_AMOUNT	* PENNERA_INCREMENT.AWARDED_TOT_SPON_COSTS
AWARD	TOTAL_COMPENSATION_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD	TUTION_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD	UNIVERSITY_EXPENSES_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
AWARD_CARRYOVER		Not converted.  In PennERA, carryover data is stored in the same table as other Special Budget data, with carryover-specific transaction types.  This data will be available in the Warehouse in the PENNERA_FUND_ADJ table, which is currently under development.
CENTER_REF_CODES		* same table used for both Universes
CNAC_CODES		* same table used for both Universes
Note:  EMPLOYEE_GENERAL is included in the PennERA Proposals Universe, but has data only for University employees.  As records are created in PennERA on
Oct. 14, 2003 and thereafter, some will include information on investigators who are not University employees.  The list below is a guide to the PENNERA_PEOPLE table, which
has current information on all those who may have PennERA records, whether or not they are University employees.
EMPLOYEE_GENERAL	CAMPUS_MAIL_CODE	PENNERA_PEOPLE.MAILSTOP
EMPLOYEE_GENERAL	CURRENTLY_EMPLOYED	PENNERA_PEOPLE.STATUS (this is the person's status per Penn Community; it does NOT indicate whether the person is currently employed by the University)
EMPLOYEE_GENERAL	EMAIL_ADDRESS	PENNERA_PEOPLE.EMAIL
EMPLOYEE_GENERAL	EMPLOYMENT_STATUS	PENNERA_PEOPLE.STATUS (this is the person's status per Penn Community; it does NOT indicate the person's current status as a University employee [active, on leave, etc.] )
EMPLOYEE_GENERAL	EMPLOYMENT_STATUS_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	HOME_DEPARTMENT	PENNERA_PEOPLE.HOME_ORG (available for University employees only; this is the 4-character org. code, not the Payroll department code)
EMPLOYEE_GENERAL	HOME_DEPT_ORG	* PENNERA_PEOPLE.HOME_ORG (available for University employees only)
EMPLOYEE_GENERAL	HOME_SCHOOL	PENNERA_PEOPLE.HOME_SCH_CTR (available for University employees only; this is the GL code for the school/center, not the Payroll school code)
EMPLOYEE_GENERAL	HOME_SCHOOL_CTR	* PENNERA_PEOPLE.HOME_SCH_CTR (available for University employees only)
EMPLOYEE_GENERAL	LAST_EXTRACT_DATE	This is the date the data was last extracted from the Payroll system and loaded into the Employee_General table in the Warehouse. The PennERA_People table has no equivalent.
EMPLOYEE_GENERAL	NAME	* PENNERA_PEOPLE.NAME
EMPLOYEE_GENERAL	NAME_FLIPPED	* PENNERA_PEOPLE.NAME_FLIPPED
EMPLOYEE_GENERAL	NAME_PREFIX	* PENNERA_PEOPLE.SALUTATION
EMPLOYEE_GENERAL	NAME_SUFFIX	* PENNERA_PEOPLE.SUFFIX
EMPLOYEE_GENERAL	PENN_ID	* PENNERA_PEOPLE.PENN_ID
EMPLOYEE_GENERAL	PREVIOUS_SSN	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRI_ACAD_APPT_BEGIN_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRI_ACAD_APPT_END_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRI_ACAD_APPT_JOB_CLASS	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRI_ACAD_DEPT_ORG	* PENNERA_PEOPLE.PRI_ACAD_ORG (available for Penn employees only)
EMPLOYEE_GENERAL	PRI_ACAD_JCLS_ENT_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRI_ACAD_JOB_LINK_ID	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRI_ACAD_UNIV_POS_NO	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRIMARY_APPT_BEGIN_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRIMARY_APPT_END_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRIMARY_APPT_JOB_CLASS	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRIMARY_DEPARTMENT	PENNERA_PEOPLE.PRI_APPT_ORG (available for University employees only; this is the 4-character org. code, not the Payroll department code)
EMPLOYEE_GENERAL	PRIMARY_DEPT_ORG	* PENNERA_PEOPLE.PRI_APPT_ORG (available for University employees only)
EMPLOYEE_GENERAL	PRIMARY_JOB_CLASS_ENTRY_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRIMARY_JOB_LINK_ID	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PRIMARY_UNIV_POSITION_NUMBER	no equivalent; use EMPLOYEE_GENERAL for University employees
EMPLOYEE_GENERAL	PROFESSIONAL_NAME	PENNERA_PEOPLE.NAME (for employees, this is the legal name, NOT the name used by the employee for professional purposes.  For example, it would be 'CLEMENS,SAMUEL' (legal name), NOT 'TWAIN,MARK' (professional name).)
EMPLOYEE_GENERAL	PROFESSIONAL_NAME_FLIPPED	PENNERA_PEOPLE.NAME_FLIPPED (for employees, this is the legal name, NOT the name used by the employee for professional purposes.  For example, it would be 'SAMUEL CLEMENS' (legal name), NOT 'MARK TWAIN' (professional name).)
EMPLOYEE_GENERAL	PROFESSIONAL_NAME_PREFIX	PENNERA_PEOPLE.SALUTATION (Populated only for University employees.  This is the prefix for the legal name, not the prefix for the professional name.)
EMPLOYEE_GENERAL	PROFESSIONAL_NAME_SUFFIX	PENNERA_PEOPLE.SUFFIX (Populated only for University employees.  This is the suffix for the legal name, not the prefix for the professional name.)
EMPLOYEE_GENERAL	SSN	PENNERA_PEOPLE.PENN_ID (SSN is not stored in PennERA)
EMPLOYEE_GENERAL	TERMINATION_DATE	no equivalent; use EMPLOYEE_GENERAL for University employees
FUND_CODES		* same table used for both Universes
GL_PART_OLD_TO_NEW		table not included in the Universe; applies to financial data from before July, 1996
JOB_CLASS_GENERAL		* same table used for both Universes
MONTHLY_EXPENDITURES		not converted; financial data from before July, 1996
ORG_CODES		* same table used for both Universes
ORG_OLD_TO_NEW		table not included in the Universe; applies to financial data from before July, 1996
PROGRAM_CODES		* same table used for both Universes
PROJECT_EXPENDITURES		not converted; financial data from before July, 1996
PROPOSAL	AGENCY_CODE	PENNERA_REQUEST.SPONSOR_CODE
PROPOSAL	AGENCY_REF_NUMBER	There is no equivalent for this data element in the PENNERA_REQUEST table.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  For the sponsor-assigned identifier for the award payment, see PENNERA_INCREMENT.SPONSOR_AWARD_ID.
PROPOSAL	ANIMALS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Lab Animals'
PROPOSAL	ANIMALS_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system.  However, PennERA Proposals records involving animal protocols will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Lab Animals'.  As protocol data is linked to proposal data in PennERA, protocol ID's will become available in the PennERA Proposals collection in the Warehouse.
PROPOSAL	CARCINOGENIC_MATERIALS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Carcinogens/teratogens/mutagens'
PROPOSAL	CLINICAL_TRIAL_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'CL APPROVAL'
PROPOSAL	CLINICAL_TRIAL_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system (RSS).  However, PennERA Proposals records for clinical trials that were converted from RSS will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'CL APPROVAL'.  For proposals created in PennERA, see PENNERA_PROPOSAL.CLINICAL_TRIAL_PROTOCOL_ID
PROPOSAL	CO_PI_HOME_ORG	PENNERA_PROPOSAL_INVESTIGATOR.HOME_ORG_CODE where PENNERA_PROPOSAL_INVESTIGATOR.PI_FLAG = 'N'  (NOTE:  co-investigators are linked to the PennERA proposal (for the project period), not to the request (for the budget period); there can be more than one co-investigator per PennERA proposal)
PROPOSAL	CO_PI_HOME_SCHOOL	PENNERA_PROPOSAL_INVESTIGATOR.HOME_SCHOOL_CODE where PENNERA_PROPOSAL_INVESTIGATOR.PI_FLAG = 'N'  (NOTE:  co-investigators are linked to the PennERA proposal (for the project period), not to the request (for the budget period); there can be more than one co-investigator per PennERA proposal)
PROPOSAL	COMMITTED_YEARS	Not converted.  The SPONPROJ data for PROPOSAL.COMMITTED_YEARS came from PROPOSED-COMMITTED-YEARS in the Research Services system (RSS).  However, PROPOSED-COMMITTED-YEARS was not used consistently in RSS.  For records created in PennERA, the total number of proposed periods is available in PENNERA_PROPOSAL.REQUESTED_PERIODS, and data for each committed year (budget period) is stored in a PENNERA_PERIOD record with a PERIOD_STATUS of 'Future' or 'Future Pending'.
PROPOSAL	CREATE_DATE	PROPOSAL.CREATE_DATE is the system date when the RSS proposal (budget period request) record was created.  PennERA Proposal Tracking does not store this information as such,  but for converted records, the date for the earliest RSS proposal (for the budget period) within the PennERA proposal (for the project period) is stored in PENNERA_PROPOSAL.PROCESSED_DATE
PROPOSAL	DIRECT_COST_AMOUNT	* PENNERA_REQUEST.REQUESTED_DIRECT_COSTS
PROPOSAL	DNA_SUBJECT_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'rDNA'
PROPOSAL	DOMESTIC_TRAVEL_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
PROPOSAL	END_DATE	* PENNERA_REQUEST.REQUESTED_PERIOD_END_DATE
PROPOSAL	EQUIPMENT_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
PROPOSAL	EXTENSION_CODE	PENNERA_PROPOSAL.PROPOSAL_TYPE
PROPOSAL	FISCAL_YEAR_END	* PENNERA_REQUEST.REQUESTED_PERIOD_END_FY
PROPOSAL	FISCAL_YEAR_START	* PENNERA_REQUEST.REQUESTED_PERIOD_START_FY
PROPOSAL	FISCAL_YR_STATUS	In SPONPROJ, this is the fiscal year when the RSS proposal (budget period request) was submitted to the sponsor. The equivalent in the PennERA Proposals collection is the fiscal year in which the PENNERA_REQUEST.REQUEST_SUBMITTED_DATE fell.  If the month of the REQUEST_SUBMITTED_DATE falls between January and June, inclusive, the fiscal year is the same as the year of the REQUEST_SUBMITTED_DATE; otherwise, it is 1 + the year of the REQUEST_SUBMITTED_DATE.  For the fiscal year when the PennERA proposal (for the project period) was submitted to the sponsor, see PENNERA_PROPOSAL.SUBMITTED_FY.
PROPOSAL	FOREIGN_TRAVEL_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
PROPOSAL	GRANT_CONTRACT_CODE	PENNERA_PROPOSAL.INSTRUMENT_TYPE
PROPOSAL	HUMAN_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system.  However, PennERA Proposals records involving protocols for human subjects will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Human Subjects'.  As protocol data is linked to proposal data in PennERA, protocol ID's will become available in the PennERA Proposals collection in the Warehouse.
PROPOSAL	HUMAN_SUBJECTS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Human Subjects'
PROPOSAL	INDIRECT_COST_AMOUNT	* PENNERA_REQUEST.REQUESTED_INDIRECT_COSTS
PROPOSAL	INDIRECT_COST_RATE	PennERA does not store the indirect cost rate (F&A rate) at the request level.  It is available at the increment level, in PENNERA_INCREMENT.INCREMENT_F_AND_A_RATE.  Note that in PennERA, an increment (and its F&A rate) cannot be linked to a request.
PROPOSAL	INFECTIOUS_AGENTS_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Potentially Infectious Agents'
PROPOSAL	LAST_EXTRACT_DATE	* PENNERA_REQUEST.LAST_EXTRACT_DATE
PROPOSAL	MODIFY_DATE	RSS stored the system date when an RSS proposal (budget period request) record was modified.  The PennERA Proposals data collection does not store this information at the request level.  For the date of the latest modification to any information related to the PennERA proposal (for the project period), its budget periods, its budget period requests, or its increments, see PENNERA_PROPOSAL.MODIFY_DATE.
PROPOSAL	NIH_GRANT_ACTIVITY_CODE	NIH_GRANT_ACTIVITY_CODE is not available in the PENNERA_REQUEST table, because it is based on the sponsor-assigned identifier for the award payment.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  NIH_GRANT_ACTIVITY_CODE is stored in the PENNERA_INCREMENT table.
PROPOSAL	NIH_GRANT_ADMIN_ORG	NIH_GRANT_ADMIN_ORG is not available in the PENNERA_REQUEST table, because it is based on the sponsor-assigned identifier for the award payment.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  NIH_GRANT_ADMIN_ORG is stored in the PENNERA_INCREMENT table.
PROPOSAL	NIH_GRANT_APPLICATION_TYPE	NIH_GRANT_APPLICATION_TYPE is not available in the PENNERA_REQUEST table, because it is based on the sponsor-assigned identifier for the award payment.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  NIH_GRANT_APPLICATION_TYPE is stored in the PENNERA_INCREMENT table.
PROPOSAL	NIH_GRANT_SERIAL_NUMBER	NIH_GRANT_SERIAL_NUMBER is not available in the PENNERA_REQUEST table, because it is based on the sponsor-assigned identifier for the award payment.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  NIH_GRANT_SERIAL_NUMBER is stored in the PENNERA_INCREMENT table.
PROPOSAL	NIH_GRANT_SUPPLEMENT	NIH_GRANT_SUPPLEMENT is not available in the PENNERA_REQUEST table, because it is based on the sponsor-assigned identifier for the award payment.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  NIH_GRANT_SUPPLEMENT is stored in the PENNERA_INCREMENT table.
PROPOSAL	NIH_GRANT_YEAR	NIH_GRANT_YEAR is not available in the PENNERA_REQUEST table, because it is based on the sponsor-assigned identifier for the award payment.  In PennERA, an increment (and its sponsor-assigned identifier for the award payment) cannot be linked to a request.  NIH_GRANT_YEAR is stored in the PENNERA_INCREMENT table.
PROPOSAL	ORA_CONTACT_INITIALS	Not converted.  There are plans to create a table in Proposal Tracking that will list the Office of Research Services contact (and other contacts) by org.  When that table is available, the data will be exported to the Warehouse.
PROPOSAL	ORA_CONTACT	Not converted.  There are plans to create a table in Proposal Tracking that will list the Office of Research Services contact (and other contacts) by org.  When that table is available, the data will be exported to the Warehouse.
PROPOSAL	ORA_PURPOSE_CODE	PENNERA_PROPOSAL.PROGRAM_TYPE
PROPOSAL	ORG_LOG_NUMBER	* PENNERA_REQUEST.SCHOOL_LOG_NUMBER
PROPOSAL	ORIGINAL_PROPOSAL_DATE	PENNERA_REQUEST.REQUEST_SUBMITTED_DATE
PROPOSAL	OTHER_EXPENSES_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
PROPOSAL	PI_HOME_ORG	PENNERA_REQUEST.PI_HOME_ORG_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for the request for the budget period (the REQUEST_RESP_INVESTIGATOR).  The home org. for the REQUEST_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.HOME_ORG_CODE where PENNERA_REQUEST.REQUEST_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_REQUEST.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
PROPOSAL	PI_HOME_SCHOOL	PENNERA_REQUEST.PI_HOME_SCHOOL_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for the request for the budget period (the REQUEST_RESP_INVESTIGATOR).  The home school for the REQUEST_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.HOME_SCHOOL_CODE where PENNERA_REQUEST.REQUEST_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_REQUEST.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
PROPOSAL	PI_PRI_APPT_ORG	PENNERA_REQUEST.PI_PRI_APPT_ORG_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for the request for the budget period (the REQUEST_RESP_INVESTIGATOR).  The primary appointment org. for the REQUEST_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_ORG_CODE where PENNERA_REQUEST.REQUEST_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_REQUEST.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
PROPOSAL	PI_PRI_APPT_SCHOOL	PENNERA_REQUEST.PI_PRI_APPT_SCHOOL_CODE (NOTE:  This is information on the PI for the proposal (for the project period), not for the investigator responsible for the request for the budget period (the REQUEST_RESP_INVESTIGATOR).  The primary appointment school for the REQUEST_RESP_INVESTIGATOR is in PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_SCHOOL_CODE where PENNERA_REQUEST.REQUEST_RESP_INVESTIGATOR = PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID and PENNERA_REQUEST.INSTITUTION_NO = PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO )
PROPOSAL	PROJECT_COST_SHARING_AMOUNT	* PENNERA_REQUEST.REQUESTED_COST_SHR_TOTAL
PROPOSAL	PROJECT_COST_SHARING_SOURCE	not converted; not used consistently in RSS
PROPOSAL	PROJECT_ID	PENNERA_PROPOSAL.FIRST_INSTITUTION_NO
PROPOSAL	PROJECT_SEGMENT_ID	the combination of PENNERA_REQUEST.INSTITUTION_NO and PENNERA_REQUEST.PERIOD_NUMBER
PROPOSAL	PROP_RESP_ORG	* PENNERA_REQUEST.REQUEST_RESP_ORG_CODE
PROPOSAL	PROP_RESP_SCHOOL	* PENNERA_REQUEST.REQUEST_RESP_SCHOOL_CODE
PROPOSAL	PROPOSAL_ID	the combination of PENNERA_REQUEST.INSTITUTION_NO, PENNERA_REQUEST.PERIOD_NUMBER, and PENNERA_REQUEST.REQUEST_NUMBER
PROPOSAL	RADIOACTIVE_MATERIAL_FLAG	PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Radiation Safety'
PROPOSAL	RADIOACTIVE_PROTOCOL_ID	Not converted.  The protocol ID's were not used consistently in the Research Services system.  However, PennERA Proposals records for projects involving radioactive materials will have PENNERA_PROP_REGULATORY_APPR.REGULATORY_TYPE = 'Radiation Safety'.
PROPOSAL	START_DATE	* PENNERA_REQUEST.REQUESTED_PERIOD_START_DATE
PROPOSAL	STATUS_DATE	PENNERA_REQUEST.REQUEST_SUBMITTED_DATE
PROPOSAL	STATUS	PENNERA_PERIOD.PERIOD_STATUS
PROPOSAL	SUB_CONTRACTOR_AMOUNT	Only for records dating from before November 24, 1997, RSS stored PROPOSED-SUBCONTRATOR-AMOUNT, the total amount of all subcontracts combined, for each AWARD-NUMBER (RSS proposal).  PROPOSED-SUBCONTRATOR-AMOUNT was not converted, but information on each subcontract was converted.  Documentation on subcontract data is under development.
PROPOSAL	TITLE	PENNERA_PROPOSAL.PROJECT_TITLE (the value may be longer than the value stored in SPONPROJ)
PROPOSAL	TOTAL_AGENCY_AMOUNT	* PENNERA_REQUEST.REQUESTED_TOT_SPON_COSTS
PROPOSAL	TOTAL_COMPENSATION_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
PROPOSAL	TUTION_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
PROPOSAL	UNIVERSITY_EXPENSES_AMOUNT	No equivalent.  For records dating from November 24, 1997 and thereafter, the Research Services system does not store data for this field.
ORA_PURPOSE		no equivalent for this reference table; decoded values are stored for program type
SPON_PROJ_INVESTIGATOR	LAST_EXTRACT_DATE	* PENNERA_PROPOSAL_INVESTIGATOR.LAST_EXTRACT_DATE
SPON_PROJ_INVESTIGATOR	ORA_INVESTIGATOR_NAME	PENNERA_PROPOSAL_INVESTIGATOR.NAME (Note that there is no space after the comma that separates the last name from the first name.)
SPON_PROJ_INVESTIGATOR	PENN_ID	* PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID
SPON_PROJ_INVESTIGATOR	PRIMARY_APPT	* PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_JOB_CLASS (available for Penn employees only)
SPON_PROJ_INVESTIGATOR	PRIMARY_DEPT	PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_ORG_CODE (available for Penn employees only; this is the 4-character org. code, not the Payroll department code)
SPON_PROJ_INVESTIGATOR	PRIMARY_ORG_CODE	* PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_ORG_CODE (available for Penn employees only)
SPON_PROJ_INVESTIGATOR	PRIMARY_SCHOOL_CODE	* PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_SCHOOL_CODE (available for Penn employees only)
SPON_PROJ_INVESTIGATOR	PROJECT_ID	PENNERA_PROPOSAL.FIRST_INSTITUTION_NO
SPON_PROJ_INVESTIGATOR	PROPOSAL_ID	PENNERA_PROPOSAL_INVESTIGATOR.INSTITUTION_NO
SPON_PROJ_INVESTIGATOR	RANK	* PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_APPT_JOB_TITLE (available for Penn employees only)
SPON_PROJ_INVESTIGATOR	RESEARCHER_TYPE	PENNERA_PROPOSAL_INVESTIGATOR.PI_FLAG (value is Y or N rather than P or C)
SPON_PROJ_INVESTIGATOR	SSN	PENNERA_PROPOSAL_INVESTIGATOR.PENN_ID  (SSN is not stored in PennERA)
SPON_PROJ_INVESTIGATOR	TRACK	PENNERA_PROPOSAL_INVESTIGATOR.PRIMARY_ACADEMIC_FACULTY_CLASS (available for Penn employees only).  This is a 3-character code that indicates the faculty classification of the investigator's primary academic appointment.  It is NOT a 2-character code indicating whether the investigator is a Tenure Track, Clinician Educator, or Research Faculty member, based on the investigator's primary appointment.
Note:  the PENNERA_SUBCONTRACT table, and accompanying documentation, are currently under development.
SPON_PROJ_SUBCONTRACTOR	LAST_EXTRACT_DATE
SPON_PROJ_SUBCONTRACTOR	PROJECT_ID
SPON_PROJ_SUBCONTRACTOR	PROPOSAL_ID
SPON_PROJ_SUBCONTRACTOR	SUB_CONTRACTOR_AMOUNT	In RSS, the amount awarded by the University to the subcontractor for the proposal. Depending on the status of the RSS proposal, this amount is the proposed or the awarded amount.  In RSS, this is populated only for records dating from November 24, 1997 and thereafter.
SPON_PROJ_SUBCONTRACTOR	SUB_CONTRACTOR_CODE	no equivalent; use subcontractor name
SUB_CONTRACTOR		no equivalent for this reference table; subcontractor names (not codes) are stored in the PennERA Proposals tables.