PENNERA_PROPOSAL_INTL_LOCATION Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

PENNERA_PROPOSAL_INTL_LOCATION Table

DWSP Schema

Explanation
For proposals that entail activities occurring outside the United States of America, the PENNERA_PROPOSAL_INTL_LOCATION table stores information on the country or countries where each proposal's international activities take place, and the regions of the world where those countries are located. There is one PENNERA_PROPOSAL_INTL_LOCATION record per proposal (INSTITUTION_NO) per country (COUNTRY_OF_ACTIVITY).

Common Uses

• Reporting on the countries or regions where a proposal has international activities. ("Where does proposal 12345678 have international activities?")
• Identifying proposals that have international activities in a particular country or region. ("Which proposals have activities in Australia?")
  

This table has no
physical primary key, but
the logical primary key
is the combination of
INSTITUTION_NO and
COUNTRY_OF_ACTIVITY

None

PENNERA_FUND_ADJ
PENNERA_INCR_STAT_HIST
PENNERA_INCREMENT
PENNERA_PERIOD
PENNERA_PROP_REGULATORY_APPR
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_PROPOSAL_INVESTIGATOR
PENNERA_PROPOSAL_PARENT
PENNERA_REQ_STAT_HIST
PENNERA_REQUEST

Cautions

• The PENNERA_PROPOSAL_INTL_LOCATION table stores data only for proposals that entail activities occurring outside the United States of America. Each of these proposals has a record in the PENNERA_PROPOSAL table with INTERNATIONAL_ACTIVITY_FLAG = 'Y'. PENNERA_PROPOSAL_INTL_LOCATION data is not available for:
  • proposals that were not created using PennERA's Proposal Development (PD) module. See PENNERA_PROPOSAL.PD_FLAG.
  • proposals whose Certification by Principal Investigator was completed after April 19, 2010. (At that time, PennERA did not capture international location data.) The Data Warehouse does not store information on when the Certification by Principal Investigator was completed.
• The PENNERA_PROPOSAL_INTL_LOCATION table has more than one record for a proposal (INSTITUTION_NO) if that proposal has international activities in more than one country (COUNTRY_OF_ACTIVITY).
• A REGION_OF_ACTIVITY includes more than one COUNTRY_OF_ACTIVITY. A COUNTRY_OF_ACTIVITY is located within a single REGION_OF_ACTIVITY.
• The values for COUNTRY_OF_ACTIVITY and REGION_OF_ACTIVITY are stored in mixed case. Business Objects users will see that the PennERA Proposal Intl Location folder (in the PennERA Proposals and PennERA Balances Universes) includes Country of Activity UC (INTL) and Region of Activity UC (INTL). These objects convert the values for COUNTRY_OF_ACTIVITY and REGION_OF_ACTIVITY to upper case. You might prefer to specify the upper case versions of these data elements when they are used for sorting or record selection conditions.
  • If you sort by COUNTRY_OF_ACTIVITY (Country of Activity (INTL)), 'FYR Macedonia' is listed before 'Fiji'. If you sort by Country of Activity UC (INTL), 'FIJI' is listed before 'FYR MACEDONIA'.
  • Using the upper case versions of COUNTRY_OF_ACTIVITY and REGION_OF_ACTIVITY prevents errors in capitalization when their values are specified in record selection conditions. For example, you could use the caps lock key when typing in the desired value, and specify the condition Country of Activity UC (INTL) Equal to 'FIJI'. If you aren't sure whether the COUNTRY_OF_ACTIVITY is 'St. Vincent & the Grenadines' or 'St. Vincent & The Grenadines', you could specify the condition Country of Activity UC (INTL) Equal to 'ST. VINCENT & THE GRENADINES'.
• Use caution when joining PENNERA_PROPOSAL_INTL_LOCATION to a related table.
  • The PENNERA_PROPOSAL_INTL_LOCATION table is designed to be joined to the PENNERA_PROPOSAL table based on the INSTITUTION_NO. (Business Objects users will see that these tables are joined for them in the PennERA Proposals and PennERA Balances Universes.)
  • Although the PENNERA_PROPOSAL_INTL_LOCATION table may be joined to a related table other than PENNERA_PROPOSAL based on the INSTITUTION_NO, such joins are not recommended. The PENNERA_PROPOSAL table has one record per proposal, but the other related tables may have multiple records per proposal. For example, say proposal 12345678 has three periods, and has activities in two countries. If a query were to join the PENNERA_PERIOD table to the PENNERA_PROPOSAL_INTL_LOCATION table, the results would show six rows for proposal 1234578. If that query revised to add the condition WHERE PERIOD_NUMBER=1, the results would show two rows for proposal 12345678 (one for each of the two countries where the proposal has international activities).
    

Source
The Proposal Development module of PennERA. PennERA has been used at Penn since October 14, 2003 to track information on proposed and awarded grants and contracts.

PennERA is the University's electronic research administration system, providing support to faculty and research administrators for all sponsored project activity at the University of Pennsylvania.

PENNERA_PROPOSAL_INTL_LOCATION Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home