Penn Computing

Penn Computing

Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

  
  UPDATES
Current Load Status
Regular Availability
  
  INFORMATION
FAQs & Tips
Password Changer
Support services
Security
About the Data Warehouse
Data Administration
  
  DATA COLLECTIONS
Advancement
Assets
BRIM
Express Mail
Facilities
Faculty
GAR
General Ledger
Infrastructure
ISSS-iOffice
Learning Management
Position Inventory
Research-PennERA Proposals
Salary Management
Student
Travel Expense Management
Tuition Distribution
Cross-Collection

PennERA Proposals Collection Snapshots

·Table-specific Notes and Cautions
· PennERA Proposals Snapshots (ERASPFY) Universe Notes and Cautions 

The PennERA Proposals snapshot tables were created to support longitudinal studies of sponsored projects. If a query accesses the current PennERA Proposals tables (the ones that are refreshed nightly), it can return different results on different days. (For example, a proposal that was pending a sponsor's funding decision yesterday might be awarded today.) Once the snapshot for a fiscal year has been taken, a query that accesses it in the PennERA Proposals snapshot tables will return the same results regardless of when it is run. Also, it can be cumbersome to use the current PennERA Proposals tables to retrieve historical information (such as what a proposal's status was at a particular point in the past). The PennERA Proposals snapshot tables make it easier to compare how information on sponsored projects changed from year to year.

The following are notes and cautions on the snapshot tables in general. Please refer to the table-specific notes and cautions for caveats particular to individual tables.

  • The PennERA Proposals snapshot is taken annually, and is identified by the snapshot fiscal year--the latest closed fiscal year as of the date when the snapshot job was run. (The University's fiscal year runs from July 1 through June 30. For example, fiscal year 2005 ended on June 30, 2005.)
  • The PennERA Proposals snapshot includes all of the tables in the PennERA Proposals data collection, plus many of the reference tables for the BALANCES table (in the General Ledger data collection). The tables that store the snapshots of the PennERA Proposals tables include annual snapshots for snapshot fiscal year 2004 and thereafter. The tables that store the snapshots of the reference tables for the BALANCES table include annual snapshots for snapshot fiscal year 2002 and thereafter. (The 2002 and 2003 snapshots of the reference tables for the BALANCES table are used with the annual snapshots of the tables in the Sponsored Projects data collection.)
  • The snapshot job is not automatically scheduled, but is run upon Data Administration's request--usually in January. For example, in January, 2007, the data for snapshot fiscal year 2006 was loaded in the Warehouse. (By January, 2007, for most of the proposals submitted during fiscal year 2006, the sponsors had let the University know whether they had awarded funding or not.) This table provides the snapshot date for each PennERA Proposals snapshot that has been taken since PennERA was implemented (October 14, 2003).
    • Note: the data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. Use caution when comparing success rates for other snapshot fiscal years with the rates for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. To see when a snapshot was taken, see this table.
  • All those who have access to the PennERA Proposals data collection have access to the PennERA Proposals snapshots. The data security that applies to the PennERA Proposals tables that store current data also applies to the tables that store the snapshot data.
  • A snapshot is a static copy of all of the records that were in the source tables when the snapshot job was run. The source tables--the current PennERA Proposals tables in the Data Warehouse--include records for currently active proposals and awards, as well as historical data going as far back as approximately 1982. (Historical data from before 1982 is spotty. Also, data on administrative activities (stored in the PENNERA_PROPOSAL_ACTIVITY table) is available only for activities whose effective date is Jan. 22, 2008 or later.) In a PennERA Proposals snapshot table, a proposal whose record was created on or before the date when the first snapshot of the PennERA Proposals tables was taken (February 1, 2005) will appear in all of the snapshots. A proposal created after February 1, 2005 will appear in the snapshot for the fiscal year when the proposal record was created, and in subsequent snapshots. (In the unlikely event that a proposal record was created one year and deleted a year or more later, the proposal record will not appear in the snapshot for the fiscal year when the proposal record was deleted, and will not appear in subsequent snapshots.)
  • Over time, as changes have been made to the structure of the data in the Proposal Tracking module of PennERA, changes have been made to the structure of the PennERA Proposals tables in the Data Warehouse. (Changes have also been made over time to the structure of the reference tables for the BALANCES table.) Because the tables for the snapshot data store what was in the tables for the current data when the snapshot job was run, some snapshots include tables or data elements that are not available in other snapshots. Please refer to the individual table notes (below) for details.
  • Since the snapshots capture data annually, the snapshot for a given fiscal year includes the net result of all of the data changes that were made since the snapshot for the previous fiscal year. These changes are not applied to previous snapshots, to preserve the accuracy of the snapshot as a view of the data at a point in time. There may, therefore, be cases where data (such as budget period start and end dates) appears incorrectly in one snapshot, and appears correctly in a later snapshot after it was corrected in the source system.
  • Because a snapshot captures each record as it appeared when the snapshot was taken, individual changes to a given record over the course of a single fiscal year are not stored in the snapshot. For example, if a proposal’s Principal Investigator was Dr. Doe at the start of the year, but was changed to Dr. Smith in the middle of the year, and changed again to Dr. Jones just before the end of the year, the snapshot stores only the fact that Dr. Jones was the Principal Investigator at the time of the snapshot.
  • The columns included in the snapshot tables are identical to those in the PennERA Proposals tables that store current data, with the addition of columns to identify the snapshot. These columns are:
    • Snapshot fiscal year (SNAPSHOT_FY in the snapshots of the PennERA Proposals tables; SPFY_SNAP_FY in the snapshots of the reference tables for the BALANCES table year)--the latest closed fiscal year as of the date when the snapshot job was run
    • Snapshot date (SNAPSHOT_DATE in the snapshots of the PennERA Proposals tables; SPFY_SNAP_DATE in the snapshots of the reference tables for the BALANCES table year)--the date when the snapshot of the record was taken
  • The PennERA Proposals snapshot process takes snapshots of the following tables:

    Table  Snapshot Name  Snapshot Schema Data Collection
    CENTER_REF_CODES SPFY_CENTER_REF_CODES DWADMIN General Ledger
    CNAC_CODES SPFY_CNAC_CODES DWADMIN General Ledger
    FUND_CODES SPFY_FUND_CODES DWADMIN General Ledger
    INTL_LOCATION_LIST SPFY_INTL_LOCATION_LIST DWSP PennERA Proposals
    ORG_CODES SPFY_ORG_CODES DWADMIN General Ledger
    PARENT_CNAC_CODES SPFY_PARENT_CNAC_CODES DWADMIN General Ledger
    PARENT_FUND_CODES SPFY_PARENT_FUND_CODES DWADMIN General Ledger
    PARENT_ORG_CODES SPFY_PARENT_ORG_CODES DWADMIN General Ledger
    PARENT_PROGRAM_CODES SPFY_PARENT_PROGRAM_CODES DWADMIN General Ledger
    PENNERA_FUND_ADJ SPFY_PENNERA_FUND_ADJ DWSP PennERA Proposals
    PENNERA_INCREMENT SPFY_PENNERA_INCREMENT DWSP PennERA Proposals
    PENNERA_INCR_STAT_HIST SPFY_PENNERA_INCR_STAT_HIST DWSP PennERA Proposals
    PENNERA_PEOPLE SPFY_PENNERA_PEOPLE DWSP PennERA Proposals
    PENNERA_PERIOD SPFY_PENNERA_PERIOD DWSP PennERA Proposals
    PENNERA_PERIOD_STAT_HIST SPFY_PENNERA_PERIOD_STAT_HIST DWSP PennERA Proposals
    PENNERA_PROPOSAL SPFY_PENNERA_PROPOSAL DWSP PennERA Proposals
    PENNERA_PROPOSAL_ACTIVITY SPFY_PENNERA_PROPOSAL_ACTIVITY DWSP PennERA Proposals
    PENNERA_PROPOSAL_INTL_LOCATION SPFY_PENNERA_PROP_INTL_LOCTN DWSP PennERA Proposals
    PENNERA_PROPOSAL_INVESTIGATOR SPFY_PENNERA_PROP_INVESTIGATOR DWSP PennERA Proposals
    PENNERA_PROPOSAL_PARENT SPFY_PENNERA_PROPOSAL_PARENT DWSP PennERA Proposals
    PENNERA_PROP_REGULATORY_APPR SPFY_PENNERA_PROP_REG_APPR DWSP PennERA Proposals
    PENNERA_PROP_STAT_HIST SPFY_PENNERA_PROP_STAT_HIST DWSP PennERA Proposals
    PENNERA_REQUEST SPFY_PENNERA_REQUEST DWSP PennERA Proposals
    PENNERA_REQ_STAT_HIST SPFY_PENNERA_REQ_STAT_HIST DWSP PennERA Proposals
    PENNERA_SPONSOR SPFY_PENNERA_SPONSOR DWSP PennERA Proposals
    PROGRAM_CODES SPFY_PROGRAM_CODES DWADMIN General Ledger
    PURPOSE_CODES SPFY_PURPOSE_CODES DWADMIN General Ledger
    RCM_OBJECT_TREE SPFY_RCM_OBJECT_TREE DWADMIN General Ledger

Click here for information on EMPLOYEE_GENERAL_SNAP and JOB_CLASS_GENERAL_SNAP.

Table-Specific Notes and Cautions

SPFY_CENTER_REF_CODES

  • The following data elements are available only for snapshot fiscal years 2011 and thereafter:
    • CREATION_DATE
    • CREATED_BY
    • LAST_UPDATE_DATE
    • LAST_UPDATED_BY
  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the CNAC_CODE data element in the CENTER_REF_CODES table is called SPFY_CNAC_CODE in the SPFY_CENTER_REF_CODES table.

SPFY_CNAC_CODES

  • Center 40 changed its name from 'School of Medicine' to 'Perelman School of Medicine' as of July, 2011. SPFY_CENTER_NAME reflects the new name for snapshot fiscal years 2011 and thereafter; for earlier snapshot fiscal years, it reflects the old name.
  • The following data elements are available only for snapshot fiscal years 2011 and thereafter:
    • CREATION_DATE
    • CREATED_BY
    • LAST_UPDATE_DATE
    • LAST_UPDATED_BY
  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the CNAC_CODE data element in the CNAC_CODES table is called SPFY_CNAC_CODE in the SPFY_CNAC_CODES table.

SPFY_FUND_CODES

  • The following data elements are available only for snapshot fiscal years 2011 and thereafter:
    • CREATION_DATE
    • CREATED_BY
    The following data elements are available only for snapshot fiscal years 2005 and thereafter:
    • FEDERAL_FLAG
    • LAST_UPDATE_DATE
    • LAST_UPDATED_BY
    • LEGACY_FUND_SPONSOR_CODE
  • Most of the data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the FUND_DESC data element in the FUND_CODES table is called SPFY_FUND_DESC in the SPFY_FUND_CODES table. The exceptions to this rule are:
    • ACCOUNT_END (SPFY_ACCOUNT_END_DATE)
    • ACCOUNT_START (SPFY_ACCOUNT_START_DATE)
    • CURRENT_BUDGET_END (SPFY_CURRENT_BUDGET_END_DATE)
    • DAYS_TO_SUBMIT_FINAL_RPT_CODE (SPFY_DAYS_SUBMIT_FINAL_RPT_CD)
    • DONOR_RESTRICT_PROGRAM (SPFY_DONOR_RESTRICT_PRGM)
    • FEDERAL_FLAG (FEDERAL_FLAG)
    • INTERNAL_DESIGNATED_CODE (SPFY_INTERNAL_DESIGNATED_CD)
    • INVESTMENT_REV_TREATMENT_CODE (SPFY_INVEST_REV_TREATMENT_CD)
    • LAST_UPDATE_DATE (LAST_UPDATE_DATE)
    • LAST_UPDATED_BY (LAST_UPDATED_BY)
    • LEGACY_FUND_SPONSOR_CODE (LEGACY_FUND_SPONSOR_CODE)
    • REQUIRED_FIN_RPT_FREQ_CODE (SPFY_REQD_FIN_RPT_FREQ_CD)

SPFY_INTL_LOCATION_LIST

  • Before April 19, 2010, the Proposal Development module of PennERA did not store information specific to proposals that entail activities occurring outside the United States of America. Therefore, SPFY_INTL_LOCATION_LIST has data only for snapshot fiscal year 2010 and thereafter.

SPFY_ORG_CODES

  • Center 40 changed its name from 'School of Medicine' to 'Perelman School of Medicine' as of July, 2011. CENTER_NAME reflects the new name for snapshot fiscal years 2011 and thereafter; for earlier snapshot fiscal years, it reflects the old name.
  • The following data elements are available only for snapshot fiscal years 2006 and thereafter:
    • CENTER_NAME
    • ENABLED
    • PAYROLL_FLAG
    • SPACE_FLAG
  • Except for the four data elements named above, the data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the ORGANIZATION_CODE data element in the ORG_CODES table is called SPFY_ORGANIZATION_CODE in the SPFY_ORG_CODES table.

SPFY_PARENT_CNAC_CODES

  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the CHILD_CNAC data element in the PARENT_CNAC_CODES table is called SPFY_CHILD_CNAC in the SPFY_PARENT_CNAC_CODES table.

SPFY_PARENT_FUND_CODES

  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the CHILD_FUND data element in the PARENT_FUND_CODES table is called SPFY_CHILD_FUND in the SPFY_PARENT_FUND_CODES table.

SPFY_PARENT_ORG_CODES

  • Center 40 changed its name from 'School of Medicine' to 'Perelman School of Medicine' as of July, 2011. SPFY_PARENT_ORG_DESC for SPFY_PARENT_ORG 40XX reflects the new name for snapshot fiscal years 2011 and thereafter; for earlier snapshot fiscal years, it reflects the old name.
  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the CHILD_ORG data element in the PARENT_ORG_CODES table is called SPFY_CHILD_ORG in the SPFY_PARENT_ORG_CODES table.

SPFY_PARENT_PROGRAM_CODES

  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the CHILD_PROGRAM data element in the PARENT_PROGRAM_CODES table is called SPFY_CHILD_PROGRAM in the SPFY_PARENT_PROGRAM_CODES table.

SPFY_PENNERA_INCREMENT

  • The rules for populating NIH_GRANT_APPLICATION_TYPE, NIH_GRANT_ACTIVITY_CODE, NIH_GRANT_ADMIN_ORG, NIH_GRANT_SERIAL_NUMBER, NIH_GRANT_YEAR, and NIH_GRANT_SUPPLEMENT have changed over time.
    • For snapshot fiscal years 2011 and 2012, these data elements are populated only if the sponsor or the sponsor's parent is the National Institutes of Health (SPONSOR_CODE or SPONSOR_PARENT is '09470') and the proposal's INSTRUMENT_TYPE is 'Grant'.
    • For snapshot fiscal years 2010 and earlier snapshot fiscal years, these data elements are populated only if the sponsor is the National Institutes of Health (SPONSOR_CODE is '09470') and the proposal's INSTRUMENT_TYPE is 'Grant'.
  • The ARRA_FUNDED_INCREMENT_FLAG data element is populated in the records for snapshot fiscal year 2009 and thereafter. Its value is null for earlier snapshot fiscal years.
  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. Use caution when comparing success rates for other snapshot fiscal years with the rates for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • For all snapshots, the following points apply to the data elements for orgs. and schools/centers. (In January, 2009, records for snapshot fiscal years 2004 - 2007 were updated as needed to meet these specifications.)
    • To facilitate security by org., if a data element for an org. would otherwise have a null value, its value is set to '.'. The affected data elements:

      ACCTRESPINV_ERA_PRI_ORG_CODE
      ACCTRESPINV_HOME_ORG_CODE
      ACCTRESPINV_INV_ORG_CODE
      ACCTRESPINV_PRI_APPT_ORG_CODE
      ACCTRESPINV_PRIACADAP_ORG_CODE
      AWARD_ORG
      FUND_RESPONSIBLE_ORG
      INCREMENT_RESP_ORG_CODE
      PAR_PI_PRIACADAPPT_ORG_CODE
      PARENT_PI_ERA_PRI_ORG_CODE
      PARENT_PI_HOME_ORG_CODE
      PARENT_PI_INV_ORG_CODE
      PARENT_PI_PRI_APPT_ORG_CODE
      PARENT_PROP_RESP_ORG_CODE
      PI_ERA_PRIMARY_ORG_CODE
      PI_HOME_ORG_CODE
      PI_INVESTIGATOR_ORG_CODE
      PI_PRI_ACAD_APPT_ORG_CODE
      PI_PRI_APPT_ORG_CODE
      PROPOSAL_RESP_ORG_CODE

    • If a data element for an org. has the value '.', the corresponding school/center data element is set to '.'. (This rule does not apply to school/center data elements related to the parent proposal.) The affected data elements:

      ACCTRESPINV_ERA_PRI_SCH_CODE
      ACCTRESPINV_HOME_SCH_CODE
      ACCTRESPINV_INV_SCH_CODE
      ACCTRESPINV_PRI_APPT_SCH_CODE
      ACCTRESPINV_PRIACADAP_SCH_CODE
      INCREMENT_RESP_SCHOOL_CODE
      PI_ERA_PRIMARY_SCHOOL_CODE
      PI_HOME_SCHOOL_CODE
      PI_INVESTIGATOR_SCHOOL_CODE
      PI_PRI_ACAD_APPT_SCHOOL_CODE
      PI_PRI_APPT_SCHOOL_CODE
      PROPOSAL_RESP_SCHOOL_CODE

  • In August, 2008, records for snapshot fiscal years 2004 - 2007 were updated as needed so that ACCTRESPINV_PRIACADAP_SCH_CODE and INCREMENT_RESP_SCHOOL_CODE accurately reflect the school/center for the corresponding org. data element.
  • Beginning in Feb., 2007, NOTICE_OF_AWARD_NO could be stored in PennERA, but as of Dec. 31, 2007, it was not used. NOTICE_OF_AWARD_NO was added to the SPFY_PENNERA_INCREMENT table as of snapshot fiscal year 2007, but this data element is not populated for snapshot fiscal year 2008 and earlier snapshots.
  • The Proposal Tracking module of PennERA did not begin to store increment status data until April 27, 2005. (Before that time, instead of increment status data, the Proposal Tracking module stored period status data.) Therefore, the INCREMENT_STATUS data element is populated in the SPFY_PENNERA_INCREMENT table in the snapshots for snapshot fiscal year 2005 and thereafter. (It is not populated for snapshot fiscal year 2004.) See the notes on the SPFY_PENNERA_PERIOD table.

SPFY_PENNERA_INCR_STAT_HIST

  • The RECORDED_DATE data element is populated in the records for snapshot fiscal year 2009 and thereafter. Its value is null for earlier snapshot fiscal years.
  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. When analyzing status history information, use caution when comparing data for other snapshot fiscal years with the data for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate, and the time between Advance Account and Awarded status might seem unusually long in some cases, because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • A HISTORY_STATUS of 'C' indicates that the record stores the status for the increment as of the date when the snapshot was taken (the SNAPSHOT_DATE). It does not indicate the latest (current) status for the increment.
  • The Proposal Tracking module of PennERA did not begin to store increment status data until April 27, 2005. (Before that time, instead of increment status data, the Proposal Tracking module stored period status data.) Therefore, the SPFY_INCR_STAT_HIST table has snapshots for snapshot fiscal year 2005 and thereafter. Unlike most of the PennERA Proposals snapshot tables, it does not have data for snapshot fiscal year 2004. See the notes on the SPFY_PENNERA_PERIOD_STAT_HIST table.

SPFY_PENNERA_PEOPLE

  • COMMONS_ID is available only for snapshot fiscal year 2008 and thereafter.
  • For all snapshots, the following points apply to the data elements for orgs. and schools/centers. (In January, 2009, records for snapshot fiscal years 2004 - 2007 were updated as needed to meet these specifications.)
    • If a data element for an org. would otherwise have a null value, its value is set to '.'. The affected data elements:

      ERA_PRIMARY_ORG
      HOME_ORG
      PRI_ACAD_ORG
      PRI_APPT_ORG

    • If a data element for an org. has the value '.', the corresponding school/center data element is set to '.'. The affected data elements:

      ERA_PRIMARY_ORG_SCH_CTR
      HOME_SCH_CTR
      PRI_ACAD_SCH_CTR
      PRI_APPT_SCH_CTR

  • The following data elements are available only for snapshot fiscal year 2007 and thereafter:
    • LNAME_AS_IS
    • FNAME_AS_IS
    • M_INITIAL_AS_IS
    • NAME_AS_IS
    • NAME_FLIPPED_AS_IS
    • SALUTATION_AS_IS
    • SUFFIX_AS_IS
  • For snapshot fiscal year 2006 and earlier snapshots, note that the values of the data elements for the person’s name might be in mixed case, or might be in upper case. The affected data elements: LAST_NAME, FIRST_NAME, M_INITIAL, NAME, NAME_FLIPPED, SALUTATION, and SUFFIX. For snapshot fiscal year 2007 and thereafter, the values of these data elements are all in upper case.

SPFY_PENNERA_PERIOD

  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. Use caution when comparing success rates for other snapshot fiscal years with the rates for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • For all snapshots, the following points apply to the data elements for orgs. and schools/centers. (In January, 2009, records for snapshot fiscal years 2004 - 2007 were updated as needed to meet these specifications.)
    • To facilitate security by org., if a data element for an org. would otherwise have a null value, its value is set to '.'. The affected data elements:

      PAR_PI_PRIACADAPPT_ORG_CODE
      PARENT_PI_ERA_PRI_ORG_CODE
      PARENT_PI_HOME_ORG_CODE
      PARENT_PI_INV_ORG_CODE
      PARENT_PI_PRI_APPT_ORG_CODE
      PARENT_PROP_RESP_ORG_CODE
      PI_ERA_PRIMARY_ORG_CODE
      PI_HOME_ORG_CODE
      PI_INVESTIGATOR_ORG_CODE
      PI_PRI_ACAD_APPT_ORG_CODE
      PI_PRI_APPT_ORG_CODE
      PROPOSAL_RESP_ORG_CODE

    • If a data element for an org. has the value '.', the corresponding school/center data element is set to '.'. (This rule does not apply to school/center data elements related to the parent proposal.) The affected data elements:

      PI_ERA_PRIMARY_SCHOOL_CODE
      PI_HOME_SCHOOL_CODE
      PI_INVESTIGATOR_SCHOOL_CODE
      PI_PRI_ACAD_APPT_SCHOOL_CODE
      PI_PRI_APPT_SCHOOL_CODE
      PROPOSAL_RESP_SCHOOL_CODE

  • The Proposal Tracking module of PennERA originally stored period status data, but that data was removed from the Proposal Tracking module in April, 2005. (At that time, instead of period status data, the Proposal Tracking module began to store request status data and increment status data. The period status data was converted to request status data and increment status data.) Therefore, the PERIOD_STATUS data element is populated in the SPFY_PENNERA_PERIOD table in the snapshot for snapshot fiscal year 2004 only. See the notes on the SPFY_PENNERA_REQ_STAT_HIST and SPFY_PENNERA_INCR_STAT_HIST tables.

SPFY_PENNERA_PERIOD_STAT_HIST

  • The Proposal Tracking module of PennERA originally stored period status data, but that data was removed from the Proposal Tracking module in April, 2005. (At that time, instead of period status data, the Proposal Tracking module began to store request status data and increment status data. The period status data was converted to request status data and increment status data.) Therefore, the SPFY_PENNERA_PERIOD_STAT_HIST table has data for snapshot fiscal year 2004 only. See the notes on the SPFY_PENNERA_REQ_STAT_HIST and SPFY_PENNERA_INCR_STAT_HIST tables.
  • A HISTORY_STATUS of 'C' indicates that the record stores the status for the period as of the date when the snapshot was taken (the SNAPSHOT_DATE). It does not indicate the last known status for the period (the status as of April 26, 2005).

SPFY_PENNERA_PROPOSAL

  • Before January, 2011, the Proposal Development module of PennERA did not include the SOM PI Research Categorization Form. Therefore, following data elements are populated in the records for snapshot fiscal year 2011 and thereafter. Their value is null for earlier snapshot fiscal years.
    • PCT_HUMAN_PATIENTS_RESEARCH
    • PCT_HUMAN_DATA_RESEARCH
    • PCT_HUMAN_BIO_SPECIMENS_RSRCH
    • PCT_BASIC_SCIENCE_RESEARCH
    • PCT_OTHER_NON_IND_RESEARCH
    • SOM_PI_RESEARCH_CATEG_FLAG
  • The following data elements are populated in the records for snapshot fiscal year 2010 and thereafter. Their value is null for earlier snapshot fiscal years.
    • INTERNATIONAL_COMPONENT_FLAG
    • INTERNATIONAL_ACTIVITY_FLAG
    • INTERNATIONAL_SUBCONTRACT_FLAG
    • EXPORT_CONTROL_LAW_IMPACT_FLAG
    • SPONSOR_PROJECT_AWARD_NO
  • In the records for snapshot fiscal year 2010 and thereafter, the value for INSTRUMENT_TYPE can have up to 35 characters. For earlier snapshot fiscal years, the value can have up to 28 characters.
  • The following data elements are populated in the records for snapshot fiscal year 2009 and thereafter. Their value is null for earlier snapshot fiscal years.
    • ARRA_AWARD_FLAG
    • ARRA_SUBMISSION_FLAG
    • PROJECT_MAJOR_GOALS
    • RESP_BA_EMAIL
    • RESP_BA_NAME
    • RESP_BA_PHONE
  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. Use caution when comparing success rates for other snapshot fiscal years with the rates for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • For all snapshots, the following points apply to the data elements for orgs. and schools/centers. (In January, 2009, records for snapshot fiscal years 2004 - 2007 were updated as needed to meet these specifications.)
    • To facilitate security by org., if a data element for an org. would otherwise have a null value, its value is set to '.'. The affected data elements:

      PAR_PI_PRI_ACAD_APPT_ORG_CODE
      PARENT_PI_ERA_PRI_ORG_CODE
      PARENT_PI_HOME_ORG_CODE
      PARENT_PI_INV_ORG_CODE
      PARENT_PI_PRI_APPT_ORG_CODE
      PARENT_PROP_RESP_ORG_CODE
      PI_ERA_PRIMARY_ORG_CODE
      PI_HOME_ORG_CODE
      PI_INVESTIGATOR_ORG_CODE
      PI_PRI_ACAD_APPT_ORG_CODE
      PI_PRI_APPT_ORG_CODE
      PROPOSAL_RESP_ORG_CODE

    • If a data element for an org. has the value '.', the corresponding school/center data element is set to '.'. (This rule does not apply to school/center data elements related to the parent proposal.) The affected data elements:

      PI_ERA_PRIMARY_SCHOOL_CODE
      PI_HOME_SCHOOL_CODE
      PI_INVESTIGATOR_SCHOOL_CODE
      PI_PRI_ACAD_APPT_SCHOOL_CODE
      PI_PRI_APPT_SCHOOL_CODE
      PROPOSAL_RESP_SCHOOL_CODE

  • PD_FLAG is available only for snapshot fiscal year 2007 and thereafter.
  • Beginning with snapshot fiscal year 2006, this table includes proposal records that were created in the Proposal Development module of PennERA.
    • If you wish to retrieve records only for proposals that were submitted to their sponsors, set a record selection condition on PROPOSAL_STATUS. If a proposal has not been submitted to its sponsor, its PROPOSAL_STATUS is ‘Under Development’, ‘Under Review’, or ‘In Process’. You might also want to exclude ‘Withdrawn’ proposals.
      • Beginning with snapshot fiscal year 2008, the SPFY_PENNERA_PROPOSAL table excludes records for proposals whose PROPOSAL_STATUS was ‘Under Development' at the time the snapshot was taken.
    • The PROPOSAL_TYPE indicates the reason for requesting funding for this funding cycle. For example, its value might be ‘New Project’ or ‘Competing (Renewal)’. However, its value will be null if the PROPOSAL_STATUS is ‘Under Development’ and information on the proposal type had not yet been entered for the proposal at the time the snapshot was taken.
  • For snapshot fiscal year 2006 and earlier snapshots, the data for CONFLICT_OF_INT_FLAG is not reliable.
  • For snapshot fiscal year 2005 and earlier snapshots, ‘Under Review’ may appear in cases where the PROPOSAL_STATUS ought to be ‘Negotiation’.
  • Beginning with snapshot fiscal year 2005, the SPFY_PENNERA_PROPOSAL table includes proposal records that were fed to PennERA from SOMERA (the School of Medicine’s Electronic Research Administration system). A record that was fed from SOMERA has PROPOSAL_STATUS ‘In Process’ until the Office of Research Services enters additional data and changes the PROPOSAL_STATUS to ‘Pending’. Even though there may be a non-null value for SUBMITTED_DATE, the proposal has not been approved for submission to the sponsor until it reaches ‘Pending’ status.

SPFY_PENNERA_PROPOSAL_ACTIVITY

  • The Proposal Tracking module of PennERA did not begin to store data on administrative activities until Jan. 22, 2008. Therefore, SPFY_PENNERA_PROPOSAL_ACTIVITY has data only for snapshot fiscal year 2008 and thereafter.

SPFY_PENNERA_PROPOSAL_PARENT

  • This table may be ignored. Before the Proposal Tracking module of PennERA was implemented, the Office of Research Services (ORS) considered entering more than one proposal record for a program project: one for the program project as a whole, and one for each sub-project. However, ORS has never entered more than one proposal record for a program project. Instead, it enters one proposal record for the program project, and enters the data for its sub-project awards at the increment level. The PENNERA_PROPOSAL_PARENT table was designed to facilitate reporting on program projects along with other sponsored projects that are not program projects. Currently, every proposal looks like one for a sponsored project that is not a program project. In the PENNERA_PROPOSAL_PARENT table, the PARENT_INSTITUTION_NO is always the same as the SUBPROJ_INSTITUTION_NO. Users of the PennERA Proposals data collection ought to ignore the PENNERA_PROPOSAL_PARENT and SPFY_PENNERA_PROPOSAL_PARENT tables.

SPFY_PENNERA_PROP_INTL_LOCTN

  • Before April 19, 2010, the Proposal Development module of PennERA did not store information specific to proposals that entail activities occurring outside the United States of America. Therefore, SPFY_PENNERA_PROP_INTL_LOCTN has data only for snapshot fiscal year 2010 and thereafter.

SPFY_PENNERA_PROP_INVESTIGATOR

  • For snapshot fiscal year 2007 and earlier snapshots, the SPFY_PENNERA_PROP_INVESTIGATOR table had one record per SNAPSHOT_FY, per proposal, per investigator, per INVESTIGATOR_ORG_CODE. For snapshot fiscal year 2008 and later snapshots, the primary key (PK) of the table was changed, so that it now stores one record per SNAPSHOT_FY, per proposal, per investigator, per INVESTIGATOR_ORG_CODE, per PI_FLAG.
  • For all snapshots, the following points apply to the data elements for orgs. and schools/centers. (In January, 2009, records for snapshot fiscal years 2004 - 2007 were updated as needed to meet these specifications.)
    • If a data element for an org. would otherwise have a null value, its value is set to '.'. The affected data elements:

      ERA_PRIMARY_ORG_CODE
      HOME_ORG_CODE
      INVESTIGATOR_ORG_CODE
      PRIMARY_ACADEMIC_ORG_CODE
      PRIMARY_APPT_ORG_CODE

    • If a data element for an org. has the value '.', the corresponding school/center data element is set to '.'. The affected data elements:

      ERA_PRIMARY_SCHOOL_CODE
      HOME_SCHOOL_CODE
      INVESTIGATOR_SCHOOL_CODE
      PRIMARY_ACADEMIC_SCHOOL_CODE
      PRIMARY_APPT_SCHOOL_CODE

SPFY_PENNERA_PROP_REG_APPR

  • In the Summer of 2009, some changes were made to the process for exporting proposal regulatory approval data to the Data Warehouse. As described below, the SPFY_PENNERA_PROP_REG_APPR table stores data one way for snapshot fiscal year 2009 and thereafter, and another way for earlier snapshot fiscal years.
    • Beginning with snapshot fiscal year 2009, the table includes not only information from the Approvals screen in PennERA's Proposal Tracking (PT) module, but also information on all Human Subjects (HS) and Lab Animals (LA) protocols that are linked to proposals in the PennERA system (even if they are not listed on the PT Approvals screen). For snapshot fiscal year 2008 and earlier snapshots, the table includes only information from the PT Approvals screen.
    • For all snapshot fiscal years, the combination of PROTOCOL_ATTACHED and PROT_NO indicates whether or not the proposal has been linked to the protocol in the PennERA system. 'Human Subjects' and 'Lab Animals' are the only REGULATORY_TYPEs whose protocols can be linked to the proposal in the PennERA system. The proposal is linked to the protocol if PROTOCOL_ATTACHED is 'Attached' and PROT_NO is not null. (The HS and LA protocols for a proposal ought to be approved and linked to the proposal in PennERA before the proposal is awarded.)
    • If the REGULATORY_TYPE is not 'Human Subjects' or 'Lab Animals',
      • beginning with snapshot fiscal year 2009, a proposal may have only one record in the snapshot for that REGULATORY_TYPE. REVIEW_CATEGORY is null.
      • for snapshot fiscal year 2008 and earlier snapshots, regardless of the value of REGULATORY_TYPE, a proposal may have multiple records in the snapshot for that REGULATORY_TYPE. REVIEW_CATEGORY reflects what was shown on the PT Approvals screen, which might or might not be correct.
      • for all snapshot fiscal years, APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, REVIEW_CATEGORY, and PROTOCOL_ATTACHED reflect what was shown on the PT Approvals screen, which might or might not be correct. PROT_NO is null or ' '.
    • If the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals',
      • beginning with snapshot fiscal year 2009, if the REFERENCE_NO is not null, the proposal may have only one record in the snapshot for each combination of REGULATORY_TYPE and REFERENCE_NO. For snapshot fiscal year 2008 and earlier snapshots, a proposal may have multiple records per REGULATORY_TYPE regardless of the value of the REFERENCE_NO.
      • beginning with snapshot fiscal year 2009, if the REFERENCE_NO is the protocol's Institution No. as stored in the PennERA protocol tracking module for the relevant kind of protocol, PROT_NO, APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, and REVIEW_CATEGORY reflect the data stored in the relevant PennERA protocol tracking module. If the proposal was linked to the protocol in the relevant PennERA protocol tracking module, PROTOCOL_ATTACHED is ‘Attached’; otherwise, PROTOCOL_ATTACHED reflects what was shown on the PT Approvals screen. (If, at the time of the snapshot, the PENNERA_PROP_REGULATORY_APPR table has multiple records for a proposal with the same REGULATORY_TYPE and REFERENCE_NO, and the proposal was linked to the protocol via the PT Approvals screen, and at least one of the records has PROTOCOL_ATTACHED set to ‘Attached’, SPFY_PENNERA_PROP_REG_APPR.PROTOCOL_ATTACHED is ‘Attached’.) For snapshot fiscal year 2008 and earlier snapshots (regardless of the value of REFERENCE_NO), APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, REVIEW_CATEGORY, and PROTOCOL_ATTACHED reflect what was shown on the PT Approvals screen, which might or might not be correct. PROT_NO might or might not be null, and might or might not be correct.
      • beginning with snapshot fiscal year 2009, if the REFERENCE_NO is not null, but is not a protocol's Institution No. stored in the PennERA protocol tracking module for the relevant kind of protocol, PROT_NO, APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, REVIEW_CATEGORY are null. PROTOCOL_ATTACHED reflects what was shown on the PT Approvals screen, which might or might not be correct. For snapshot fiscal year 2008 and earlier snapshots (regardless of the value of REFERENCE_NO), APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, REVIEW_CATEGORY, and PROTOCOL_ATTACHED reflect what was shown on the PT Approvals screen, which might or might not be correct. PROT_NO might or might not be null, and might or might not be correct.
      • if the REFERENCE_NO is null,
        • the proposal may have one or more records in the snapshot for each combination of REGULATORY_TYPE and REFERENCE_NO, for all snapshot fiscal years. (When a proposal will involve a human or animal protocol, but the protocol's Institution No. is unknown, the REFERENCE_NO is legitimately null.)
        • beginning with snapshot fiscal year 2009, PROT_NO, APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, REVIEW_CATEGORY, and PROTOCOL_ATTACHED are null.
        • for snapshot fiscal year 2008 and earlier snapshots, APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, REVIEW_CATEGORY, and PROTOCOL_ATTACHED reflect what was shown on the PT Approvals screen, which might or might not be correct. PROT_NO might or might not be null, and might or might not be correct.
  • For snapshot fiscal year 2007 and earlier snapshots, the value for REFERENCE_NO is 6 characters long at most. For snapshot fiscal year 2008 and later snapshots, the value for REFERENCE_NO can be up to 60 characters long.
  • For snapshot fiscal year 2007 and earlier snapshots, SPFY_PENNERA_PROP_REG_APPR stores only the information from the Approvals screen in PennERA's Proposal Tracking (PT) module. For snapshot fiscal year 2008 and later snapshots, the table also stores information on all Human Subjects (HS) and Lab Animals (LA) protocols that were linked to proposals in the PennERA system (even if they were not listed on the PT Approvals screen).
  • For snapshot fiscal year 2007 and earlier snapshots, the Approvals data was not scrubbed as described below. For snapshot fiscal year 2008 and later snapshots, the data was scrubbed.
    • Number of records
      • For snapshot fiscal year 2007 and earlier snapshots, SPFY_PENNERA_PROP_REG_APPR stores the same number of records for the proposal as were shown on the Approvals screen, regardless of the REGULATORY_TYPE.
      • For snapshot fiscal year 2008 and later snapshots,
        • 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 HS or LA protocol may be associated with more than one proposal.)
        • In some instances, the proposal has one or more HS or LA 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.
    • PROTOCOL_ATTACHED
      • For snapshot fiscal year 2007 and earlier snapshots, PROTOCOL_ATTACHED stores the value that was shown on the Approvals screen (for example, 'Attached' or 'Not Attached').
      • For snapshot fiscal year 2008 and later snapshots, the combination of PROTOCOL_ATTACHED and PROT_NO indicates whether or not the proposal was linked to the protocol in the PennERA system. ‘Human Subjects’ and ‘Lab Animals’ are the only REGULATORY_TYPEs whose protocols can be linked to the proposal in the PennERA system. The protocol was linked to the proposal if PROTOCOL_ATTACHED is 'Attached' and PROT_NO is not null. (The approved HS and LA protocols for a proposal ought to be linked to the proposal in PennERA before it is awarded.) The value for PROTOCOL_ATTACHED ought to be ‘Not Attached’ or null for other REGULATORY_TYPEs.
    • Approval details for the protocol (or other aspect of the proposal that is subject to regulatory oversight): APPROVAL_DATE, APPROVAL_STATUS, APPROVED_FROM, APPROVED_TO, and REVIEW_CATEGORY
      • For snapshot fiscal year 2007 and earlier snapshots, the columns for the approval details store the values that were shown on the Approvals screen.
      • For snapshot fiscal year 2008 and later snapshots, if the REGULATORY_TYPE is 'Human Subjects' or 'Lab Animals' and the PROT_NO is not null, the columns for the approval details reflect what was shown on the Summary screen in PennERA's Human Subjects or Animal Use protocol tracking module. Otherwise, the information reflects what was shown on the Approvals screen in PennERA's Proposal Tracking module, where the approval details might not have been kept up-to-date.

SPFY_PENNERA_PROP_STAT_HIST

  • The RECORDED_DATE data element is populated in the records for snapshot fiscal year 2009 and thereafter. Its value is null for earlier snapshot fiscal years.
  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. When analyzing status history information, use caution when comparing data for other snapshot fiscal years with the data for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate, and the time between Pending and Awarded status might seem unusually long in some cases, because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • A HISTORY_STATUS of 'C' indicates that the record stores the status for the proposal as of the date when the snapshot was taken (the SNAPSHOT_DATE). It does not indicate the latest (current) status for the proposal.
  • For snapshot fiscal year 2005 and earlier snapshots, ‘Under Review’ may appear in cases where the PROPOSAL_STATUS ought to be ‘Negotiation’.

SPFY_PENNERA_REQUEST

  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. Use caution when comparing success rates for other snapshot fiscal years with the rates for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • For all snapshots, the following points apply to the data elements for orgs. and schools/centers. (In January, 2009, records for snapshot fiscal years 2004 - 2007 were updated as needed to meet these specifications.)
    • To facilitate security by org., if a data element for an org. would otherwise have a null value, its value is set to '.'. The affected data elements:

      PAR_PI_PRIACADAPPT_ORG_CODE
      PARENT_PI_ERA_PRI_ORG_CODE
      PARENT_PI_HOME_ORG_CODE
      PARENT_PI_INV_ORG_CODE
      PARENT_PI_PRI_APPT_ORG_CODE
      PARENT_PROP_RESP_ORG_CODE
      PI_ERA_PRIMARY_ORG_CODE
      PI_HOME_ORG_CODE
      PI_INVESTIGATOR_ORG_CODE
      PI_PRI_ACAD_APPT_ORG_CODE
      PI_PRI_APPT_ORG_CODE
      PROPOSAL_RESP_ORG_CODE
      REQRESPINV_ERA_PRI_ORG_CODE
      REQRESPINV_HOME_ORG_CODE
      REQRESPINV_INV_ORG_CODE
      REQRESPINV_PRI_APPT_ORG_CODE
      REQRESPINV_PRIACADAP_ORG_CODE
      REQUEST_RESP_ORG_CODE

    • If a data element for an org. has the value '.', the corresponding school/center data element is set to '.'. (This rule does not apply to school/center data elements related to the parent proposal.) The affected data elements:

      PI_ERA_PRIMARY_SCHOOL_CODE
      PI_HOME_SCHOOL_CODE
      PI_INVESTIGATOR_SCHOOL_CODE
      PI_PRI_ACAD_APPT_SCHOOL_CODE
      PI_PRI_APPT_SCHOOL_CODE
      PROPOSAL_RESP_SCHOOL_CODE
      REQRESPINV_ERA_PRI_SCH_CODE
      REQRESPINV_HOME_SCH_CODE
      REQRESPINV_INV_SCH_CODE
      REQRESPINV_PRI_APPT_SCH_CODE
      REQRESPINV_PRIACADAP_SCH_CODE
      REQUEST_RESP_SCHOOL_CODE

  • Beginning with snapshot fiscal year 2006, this table includes request records for proposals that were created in the Proposal Development module of PennERA.
    • If such a proposal had not been submitted to its sponsor when the snapshot was taken, its SPFY_PENNERA_REQUEST records have a null value for REQUEST_STATUS.
    • Note that the value of REQUEST_RESP_ORG_CODE may be inaccurate for some records. For request records created on or after Oct. 10, 2006, in PennERA's Proposal Development (PD) module, the REQUEST_RESP_ORG_CODE reflects the ERA primary org. of the person who created the request record. That person's ERA primary org. might or might not be the org. that is responsible for the request.
  • The Proposal Tracking module of PennERA did not begin to store request status data until April 27, 2005. (Before that time, instead of request status data, the Proposal Tracking module stored period status data.) Therefore, the REQUEST_STATUS data element is populated in the SPFY_PENNERA_REQUEST table in the snapshots for snapshot fiscal year 2005 and thereafter. (It is not populated for snapshot fiscal year 2004.) See the notes on the SPFY_PENNERA_PERIOD table.

SPFY_PENNERA_REQ_STAT_HIST

  • The RECORDED_DATE data element is populated in the records for snapshot fiscal year 2009 and thereafter. Its value is null for earlier snapshot fiscal years.
  • The data for snapshot fiscal year 2008 was loaded in the Warehouse in June, 2009. When analyzing status history information, use caution when comparing data for other snapshot fiscal years with the data for snapshot fiscal year 2008. The 2008 snapshot seems to have a greater success rate, and the time between Pending and Awarded status might seem unusually long in some cases, because the sponsors had more time to notify the University about awards before the snapshot was taken. For example, for proposals submitted in June, 2008, the sponsors had almost 12 months to notify the University about awards before the snapshot was taken; for proposals submitted in June in other years, they had about 6 months. For information on when the snapshot was taken, see the SNAPSHOT_DATE.
  • A HISTORY_STATUS of 'C' indicates that the record stores the status for the request as of the date when the snapshot was taken (the SNAPSHOT_DATE). It does not indicate the latest (current) status for the request.
  • Beginning with snapshot fiscal year 2006, this table includes request status history records for proposals that were created in the Proposal Development module of PennERA. If such a proposal had not been submitted to its sponsor when the snapshot was taken, it has no records in the SPFY_PENNERA_REQ_STAT_HIST table.
  • The Proposal Tracking module of PennERA did not begin to store request status data until April 27, 2005. (Before that time, instead of request status data, the Proposal Tracking module stored period status data.) Therefore, the SPFY_REQ_STAT_HIST table has snapshots for snapshot fiscal year 2005 and thereafter. Unlike most of the PennERA Proposals snapshot tables, it does not have data for snapshot fiscal year 2004. See the notes on the SPFY_PENNERA_PERIOD_STAT_HIST table.

SPFY_PENNERA_SPONSOR

  • The following data elements are available only for snapshot fiscal year 2007 and thereafter:
    • SPONSOR_DESC_AS_IS
    • SPONSOR_PARENT
    • SPONSOR_GRANDPARENT
  • For snapshot fiscal year 2006 and earlier snapshots, note that the value of SPONSOR_DESC might be in mixed case, or might be in upper case. For snapshot fiscal year 2007 and thereafter, all values of SPONSOR_DESC are in upper case.

SPFY_PROGRAM_CODES

  • The following data elements are available only for snapshot fiscal years 2011 and thereafter:
    • CREATION_DATE
    • CREATED_BY
  • As of Jan. 29, 2010, the data element formerly named SPFY_FIMS_BUILDING_CODE is named SPACE_BUILDING_CODE. For snapshot fiscal years 2009 and thereafter, its value may have up to 5 characters; for earlier snapshots, its value may have up to 4 characters.
  • With the exception of the data elements listed under the next bullet item, the data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the PROGRAM_CODE data element in the PROGRAM_CODES table is called SPFY_PROGRAM_CODE in the SPFY_PROGRAM_CODES table.
  • The following data elements are available only for snapshot fiscal years 2006 and thereafter:
    • CAPITAL_ADVISORY_GROUP_DATE
    • CAPITAL_COUNCIL_APPROVAL_DATE
    • CAPITAL_PROJECT_TYPE
    • CERTIFIED_PROJECT_START_DATE
    • CURRENT_CONSTRUCT_FINISH_DATE
    • CURRENT_OCCUPANCY_DATE
    • CURRENT_PROJ_COMPLETION_DATE
    • FACILITIES_RENEWAL_FUNDED
    • LAST_UPDATE_DATE
    • LAST_UPDATED_BY
    • PARENT_PROGRAM_CODE
    • PLANNED_CONSTRUCT_FINISH_DATE
    • PLANNED_PROJ_COMPLETION_DATE
    • PLANNED_PROJECT_START_DATE
    • PROGRAM_NOTE
    • PROJECT_CLOSEOUT_DATE
    • PROJECT_SCOPE_DEFINITION
    • SCHOOL_CENTER
    • TRUSTEES_APPROVAL_DATE

SPFY_PURPOSE_CODES

  • The data elements in this snapshot table have the SPFY_ prefix added to their names. For example, the PURPOSE_CODE data element in the PURPOSE_CODES table is called SPFY_PURPOSE_CODE in the SPFY_PURPOSE_CODES table.

SPFY_RCM_OBJECT_TREE

  • Note that the RCM object tree may change from time to time. For example, a report for a given SPFY_SNAP_FY may summarize data for a given object code under one RCM row, but the same report for a different SPFY_SNAP_FY may summarize data for that same object code under a different RCM row.
  • Most of the data elements in SPFY_RCM_OBJECT_TREE have the SPFY_ prefix added to their names. For example, the RCM_ROW data element in the RCM_OBJECT_TREE table is called SPFY_RCM_ROW in the SPFY_RCM_OBJECT_TREE table. The exceptions to this rule are:
    • RCM_CATEGORY_DESCRIPTION (SPFY_RCM_CATEGORY_DESC)
    • RCM_CATEGORY_PARENT_OBJECT (SPFY_RCM_CAT_PARENT_OBJECT)
    • RCM_SUMMARY_DESCRIPTION (SPFY_RCM_SUMMARY_DESC)
    • RCM_SUMMARY_PARENT_OBJECT (SPFY_RCM_SUM_PARENT_OBJECT)
  • The SPFY_SNAP_DATE is the date when the data was extracted from the snapshot's source table (DTA_RCM_OBJECT_TREE). For the date the data was extracted from the RCM_OBJECT_TREE table, select DTA_RCM_OBJECT_TREE.EXTRACT_DATE where DTA_RCM_OBJECT_TREE.TERM = SPFY_RCM_OBJECT_TREE.SPFY_SNAP_FY | ‘A’ and DTA_RCM_OBJECT_TREE.EXTRACT = ‘F’
  • SPFY_DTA_RCM_TREE_PK is available only for the snapshot fiscal years 2006 and thereafter. It uniquely identifies a row within the snapshot's source table (DTA_RCM_OBJECT_TREE). In SPFY_RCM_OBJECT_TREE, however, a row is uniquely identified by the SPFY_OBJECT_CODE and SPFY_SNAP_FY.
  • The SPFY_RCM_OBJECT_TREE table has snapshots for snapshot fiscal year 2002 and thereafter, but is missing data for snapshot fiscal year 2003. (The first available snapshot for the PennERA Proposals tables is for snapshot fiscal year 2004. However, the lack of SPFY_RCM_OBJECT_TREE data for snapshot fiscal year 2003 is of concern to those who are using the annual snapshots of the tables in the Sponsored Projects data collection.)

PennERA Proposals Snapshots (ERASPFY) Universe Notes and Cautions

A special Business Objects Universe, PennERA Proposals Snapshots (short name ERASPFY) is available for querying the snapshot tables. The PennERA Proposals Snapshots Universe is structured very similarly to the PennERA Proposals Universe, with the following differences:

  • The snapshot Universe includes data from both the PennERA Proposals data collection and the General Ledger data collection. Data Warehouse users who are authorized to access data from both of these data collections can access all of the data elements that are included in the snapshot Universe. Those who are authorized to access data from only one of these data collections can access only the data elements from that data collection that are included in the snapshot Universe.
  • Instead of the PennERA Proposals tables that are refreshed nightly, the snapshot Universe includes the snapshot versions of those tables. (These snapshots are taken annually by the PennERA Proposals snapshot process.)
  • The snapshot Universe includes SPFY_PENNERA_FUND_ADJ, the snapshot version of the PENNERA_FUND_ADJ table. Because SPFY_PENNERA_FUND_ADJ stores detail-level data, it is not joined to any other table. To use it with another table, summarize the data from the SPFY_PENNERA_FUND_ADJ table by AWARD_FUND or by INSTITUTION_NO, summarize the data from the other table by the same data element used in the SPFY_PENNERA_FUND_ADJ summary, and link the data providers based on the shared data element.
  • The snapshot Universe includes SPFY_PENNERA_PERIOD_STAT_HIST, the snapshot version of the PENNERA_PERIOD_STAT_HIST table. SPFY_PENNERA_PERIOD_STAT_HIST is joined to SPFY_PENNERA_PERIOD, to provide status history information for the budget period (grant year) within the proposal. Note that the PENNERA_PERIOD_STAT_HIST table is no longer used. Data is available in SPFY_PENNERA_PERIOD_STAT_HIST for SNAPSHOT_FY 2004 only.
  • Instead of EMPLOYEE_GENERAL and JOB_CLASS_GENERAL (Salary Management tables that are refreshed nightly), the snapshot Universe includes the snapshot versions of those tables. (These snapshots are taken monthly, as part of the Salary Management snapshot process.)
  • Instead of CENTER_REF_CODES, CNAC_CODES, FUND_CODES, ORG_CODES, PROGRAM_CODES, and PURPOSE_CODES (General Ledger tables that are refreshed nightly), the snapshot Universe includes the snapshot versions of those tables. (These snapshots are taken annually, as part of the PennERA Proposals snapshot process.)
  • The snapshot Universe includes BALANCES (a General Ledger table that is refreshed nightly).
    • BALANCES is joined to GL_PERIODS, to provide reference information for the ACCOUNTING_PERIOD. (GL_PERIODS is a General Ledger table that is refreshed nightly.)
    • BALANCES is joined to OBJECT_CODES, to provide reference information for the COA_OBJECT. (OBJECT_CODES is a General Ledger table that is refreshed nightly.)
    • BALANCES is joined to the snapshot version of RCM_OBJECT_TREE, to facilitate summarizing data based on COA_OBJECT groups. (RCM_OBJECT_TREE is a General Ledger table that is refreshed nightly. Its snapshot is taken annually as part of the PennERA Proposals snapshot process.)
    • BALANCES is joined to the snapshot versions of CENTER_REF_CODES, CNAC_CODES, FUND_CODES, ORG_CODES, and PROGRAM_CODES, to provide reference information for the COA_RESPONSIBLE_ORG and the segments of the Accounting Flexfield.
      • SPFY_CNAC_CODES is joined to SPFY_PARENT_CNAC_CODES (which is joined to itself to get the 'grandparent'). SPFY_PARENT_CNAC_CODES is the snapshot version of PARENT_CNAC_CODES, a General Ledger table that is refreshed nightly. Its snapshot is taken annually as part of the PennERA Proposals snapshot process.
      • SPFY_FUND_CODES is joined to SPFY_PARENT_FUND_CODES (which is joined to itself to get the 'grandparent'). SPFY_PARENT_FUND_CODES is the snapshot version of PARENT_FUND_CODES, a General Ledger table that is refreshed nightly. Its snapshot is taken annually as part of the PennERA Proposals snapshot process.
      • SPFY_FUND_CODES is also joined to FUND_BILLING_FORMAT_CODES, FUND_BILLING_FREQ_CODES, FUND_FINAL_RPT_CODES, FUND_INVESTMENT_CODES, FUND_LOC_RPTG_CODES, FUND_RECLASS_CODES, FUND_RPT_FORMAT_CODES, FUND_RPT_FREQ_CODES, FUND_SFS_CATEGORY_CODES, and FUND_SPONSORS. (These are a General Ledger reference tables that are refreshed nightly.) In addition, SPFY_FUND_CODES is joined to reference tables SPFY_CNAC_CODES, SPFY_ORG_CODES, and SPFY_PURPOSE_CODES.
      • SPFY_ORG_CODES is joined to SPFY_PARENT_ORG_CODES (which is joined to itself to get the 'grandparent'). SPFY_PARENT_ORG_CODES is the snapshot version of PARENT_ORG_CODES, a General Ledger table that is refreshed nightly. Its snapshot is taken annually as part of the PennERA Proposals snapshot process.
      • SPFY_PROGRAM_CODES is joined to SPFY_PARENT_PROGRAM_CODES (which is joined to itself to get the 'grandparent'). SPFY_PARENT_PROGRAM_CODES is the snapshot version of PARENT_PROGRAM_CODES, a General Ledger table that is refreshed nightly. Its snapshot is taken annually as part of the PennERA Proposals snapshot process.
    • BALANCES is also joined to SPFY_PENNERA_INCREMENT. Be careful when using this join. Unless there are no BALANCES records for the fund named in a SPFY_PENNERA_INCREMENT record, there are likely to be many BALANCES records for each SPFY_PENNERA_INCREMENT record, and vice versa. You might prefer to summarize the data from SPFY_PENNERA_INCREMENT by AWARD_FUND, summarize the data from BALANCES by COA_FUND, and link the data providers based on their fund data elements.
  • The joins involve the snapshot fiscal year, which reflects the latest closed fiscal year at the time the snapshot was taken by the PennERA Proposals snapshot process. In the snapshot Universe, the tables whose names begin with SPFY are the ones that are refreshed by the PennERA Proposals snapshot process.
    • In joins between two SPFY tables, the snapshot fiscal year in one table matches the snapshot fiscal year in the other table.
    • When an SPFY table is joined to EMPLOYEE_GENERAL_SNAP, the snapshot fiscal year in the SPFY table matches the calendar year in EMPLOYEE_GENERAL_SNAP. Also, because the SPFY tables store snapshots that were mostly taken in December, the join includes a condition to retrieve EMPLOYEE_GENERAL_SNAP's December data. For example, in December, 2005, the data for snapshot fiscal year 2005 was loaded in the Warehouse. Since December, 2005 falls in calendar year 2006, the Universe joins SPFY_PENNERA_PEOPLE records for SNAPSHOT_FY '2005' and PENN_ID '12345678' to EMPLOYEE_GENERAL_SNAP records for CALENDAR_YEAR '2005', CALENDAR_MONTH '12' (December), and PENN_ID '12345678'.
    • When an SPFY table is joined to BALANCES, 1 plus the numeric value of the snapshot fiscal year in the SPFY table matches the numeric value of the fiscal year in BALANCES. For example, in December, 2005, the data for snapshot fiscal year 2005 was loaded in the Warehouse. Since December, 2005 falls in fiscal year 2006, the Universe joins SPFY_CNAC_CODE records for SPFY_SNAP_FY '2005' and SPFY_CNAC_CODE '400' to BALANCES records for FISCAL_YEAR '2006' and COA_CNAC '400'.
      • Also, because the SPFY_PENNERA_INCREMENT table stores snapshots that were mostly taken in December, the join between it and BALANCES includes a condition to retrieve BALANCES for the DEC-XX accounting period. For example, in December, 2005, the data for snapshot fiscal year 2005 was loaded in the Warehouse. Since December, 2005 falls in fiscal year 2006, the Universe joins SPFY_PENNERA_INCREMENT records for SNAPSHOT_FY '2005' and AWARD_FUND '512345' to BALANCES records for FISCAL_YEAR '2006', FISCAL_MONTH_SEQ '06' (December), and COA_FUND '512345'.

You can re-direct a PennERA Proposals query to the snapshot Universe (and vice versa). For information on how to do this, see this FAQ page, from the Business Objects at Penn > Tips and FAQs.

top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


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