PennERA Proposals Collection Snapshots
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 years
2008, 2014, and 2015. These three snapshots seem to have a greater success rate
than usual 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 most 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:
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:
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:
- 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.
|
