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?")
Primary Key |
Indexed Data Elements |
Related Tables |
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.
Questions about this page? Email us at da-staff@isc.upenn.edu
|