Before you consider questions specific to querying PennERA Proposals
data,
make sure you are familiar with the General Guidelines for Executing Queries.
Questions to Ask Before Writing PennERA Proposals Queries
Note: Items in italics refer to data that is not currently entered
in PennERA. The PennERA Proposals data collection is designed to accommodate
this data once it is entered.
Questions you should ask when developing a query accessing PennERA
Proposals data:
- Do you want codes or English?
Many data elements, such as for sponsors and organization, are stored
as codes. Are you familiar enough with the codes that you want to
see them in your report, or do you want to see the associated descriptions?
- Do you want summarized or detailed information on what was
requested or awarded?
The PENNERA_PROPOSAL and PENNERA_PERIOD tables have summarized information.
For detailed information on requests, use the PENNERA_REQUEST table.
For detailed information on award increments, use the PENNERA_INCREMENT
table.
(Note: the awarded amounts stored in the PENNERA_PROPOSAL, PENNERA_PERIOD,
and PENNERA_INCREMENT tables reflect the increment amounts regardless
of the INCREMENT_STATUS. For example, you might be interested in
only the awarded amounts for increments whose INCREMENT_STATUS
is 'Awarded'.)
- When you say, “proposal,” what do you
mean?
- There is one PennERA proposal record per funding cycle
or project period. For example, a principal investigator might
have a proposal
whose project
period runs from Jan. 1, 2004 through Dec. 31, 2008. Check the PROPOSAL_STATUS
in the PENNERA_PROPOSAL table to see whether the proposal has been
funded.
- A proposal can have one or more budget periods that fall within
the project period. (A ‘budget period’ is often simply
called a ‘period’ in PennERA.) The proposal in the example
above might include 5 budget periods (for 2004, 2005, 2006, 2007,
and 2008).
- A request in PennERA is a request for funding for a budget period,
or for a supplement to a budget period. Check the REQUEST_STATUS
in the PENNERA_REQUEST table to see how the sponsor has responded
to the request. Note that, even if the REQUEST_STATUS is 'Awarded',
the requested amounts reflect what was requested.
- Be sure you’re using the correct data element for the
proposed amount. The funding requested from the sponsor is the
requested sponsor costs amount. The requested total
costs amount includes
both
funding from the sponsor and cost sharing.
- When you say, “award,” what do you mean?
- There is one PennERA proposal record per funding cycle or project
period. For example, a principal investigator might have a proposal
whose project
period runs from Jan. 1, 2004 through Dec. 31, 2008. Check the
PROPOSAL_STATUS in the PENNERA_PROPOSAL table to see whether the
proposal has been
funded.
- A proposal can have one or more budget periods that fall within
the project period. (A ‘budget period’ is often simply
called a ‘period’ in PennERA.) The proposal in the
example above might include 5 budget periods (for 2004, 2005,
2006, 2007,
and 2008).
- An award increment in PennERA is the portion of an award payment
(or of an advance paid in expectation of an award) that has been
assigned
to a General Ledger account. For example, say Dr. Doe and Dr.
Smith are working on a project, and the University receives
an award
check for
$10,000 for that project. Dr. Doe is assigned $6,000, and Dr.
Smith is assigned $4,000. The $6,000 is added to a General
Ledger account
for
Dr. Doe's part of the project, and the $4,000 is added to a separate
General Ledger account for Dr. Smith's part of the project. In
this scenario, we have two increment records. Check the INCREMENT_STATUS
in the PENNERA_INCREMENT table to see whether the awarded amounts
reflect 'Advance Account', 'Future', or 'Awarded' funding.
- The awarded amounts stored in the PENNERA_PROPOSAL, PENNERA_PERIOD,
and PENNERA_INCREMENT tables reflect the increment amounts regardless
of the INCREMENT_STATUS. For example, you might be interested in
only
those increments whose INCREMENT_STATUS is 'Awarded'.
- Be sure you’re using the correct data element for the
awarded amount. The funding from the sponsor is the awarded
sponsor costs amount. The awarded total costs amount
includes both funding from the sponsor and cost sharing.
- Do you want to compare what was requested to what was awarded?
- The PENNERA_PROPOSAL table has information on what was requested
and what was awarded for the entire project period.
- The PENNERA_PERIOD table has information on what was requested
and what was awarded for a particular budget period.
- The awarded amounts stored in the PENNERA_PROPOSAL and PENNERA_PERIOD
tables reflect the increment amounts regardless
of the INCREMENT_STATUS. For example, you might want to summarize
the awarded amounts for increments whose INCREMENT_STATUS
is 'Awarded'.
- A given PENNERA_REQUEST record cannot be tied to a PENNERA_INCREMENT
record. For example, a period could have two requests and
four increments. The first request might have been awarded,
and
the award check was split
among four accounts; the second request might still be pending.
Or, the award for the first request might have been assigned
to one account,
but the award for the second request was split among three
accounts. These are just two of the possible explanations for
how there
came to be four increments for the period. Do not attempt to
link a request with
an increment.
- Business Objects users can retrieve summarized PENNERA_REQUEST
data and summarized PENNERA_INCREMENT data separately, and link
the results. For example, you could include the PENNERA_PROPOSAL.PROPOSAL_TYPE
in the PENNERA_REQUEST results, and create
a variable, "Link Type": if PERIOD_NUMBER = 1 then PROPOSAL_TYPE
else if REQUEST_TYPE = 'Budget Period' then 'Non-Competing
Continuation' else REQUEST_TYPE. Link the summarized
PENNERA_REQUEST query to the summarized PENNERA_INCREMENT query
(linking INSTITUTION_NO
to INSTITUTION_NO, PERIOD_NUMBER to PERIOD_NUMBER, Link
Type to AWARD_TYPE, and REQUEST_STATUS to INCREMENT_STATUS). This
is a rough sketch--in reality, the Business Objects report would
need to take into account the following points:
- some PROPOSAL_TYPE values vary slightly from their corresponding
AWARD_TYPE values (for example, 'Change of Grantee Inst' vs.
'Change of Grantee Institution')
- some AWARD_TYPE values (such as 'Cost
Sharing') have no corresponding Link Type value,
- there is an INCREMENT_STATUS ('Advance Account') that is
not a REQUEST_STATUS, and
- there are REQUEST_STATUS values that are not INCREMENT_STATUS
values. REQUEST_STATUS 'Future Pending' implies
that there is at least one increment for the period with
INCREMENT_STATUS 'Future'. REQUEST_STATUS values 'Pending',
'Revised Budget Requested', 'Unfunded', and
'Withdrawn' are not related to any INCREMENT_STATUS.
- How do you want to handle program projects? (See
Note, above.)
A program project will have more than one PENNERA_PROPOSAL record.
There will be one for the program project as a whole, and one for each
sub-project.
The PENNERA_PROPOSAL record for the program project as a whole
will have a null value for PARENT_PROP_NO. Its dollar amounts will
be the
total of the sub-project amounts plus anything specific to the
program project as a whole.
- To retrieve the proposal for the program project as a
whole, but omit the proposals for the subprojects, set the
condition
that PARENT_PROP_NO
is null. (Note that it will also be null for ‘freestanding’ proposals—proposals
that are neither for program projects nor for sub-projects. To
retrieve only parent proposals, set the condition that SUBPROJECT_COUNT
is
greater than 0.)
- To retrieve the proposal for the program project as a whole
as well as the proposals for its subprojects, specify that
the PARENT_INSTITUTION_NO
in the PENNERA_PROPOSAL_PARENT table must be the institution
number of
the proposal for the program project as a whole.
- Do you want all the records, or are you interested in a particular
time frame?
- For the following time frames, there’s the requested
version and the awarded version.
- The project start and end dates refer to the proposal (funding
cycle).
- The period start and end dates refer to a budget period
for a proposal.
- For a request, the request submitted date refers to the date
when the request was approved to be submitted to the sponsor.
- For an 'Awarded' or 'Future' increment, the award date
refers to the date on the Notice of Award (NOA)--or, if there is
no NOA, the date on the award check. For an 'Advance Account'
increment, the award date refers to the date the increment's 'Advance
Account' status
became effective.
- When you say, “account,” do you mean
the 26-digit General Ledger account, or the 6-digit account used
in the legacy accounting
system?
AWARD_GL_ACCOUNT stores the 26-position (seven-segment) Accounting
Flexfield used in the present General Ledger accounting structure.
AWARD_LEGACY_ACCOUNT
stores AWARD_GL_ACCOUNT's counterpart, the legacy account number
in the 6-digit format used in SUBLED (the subledger system used
at Penn before
July 1, 1996). (Legacy accounts in the 6-digit format are still
used for grants and contracts in the Bursar system.)
- When you say, “project,” what do you
mean?
- If you mean “a piece of research planned to be funded for a particular
funding cycle,” a project is stored in PennERA as a proposal,
and is identified by the INSTITUTION_NO.
- If you mean “a piece of research that spans funding cycles,” a
project includes multiple proposals, and is identified by the FIRST_INSTITUTION_NO.
- If you mean a “program project” or a set of
projects proposed as a group, its PENNERA_PROPOSAL record will
have a SUBPROJECT_COUNT
that is greater than 0. (See
Note, above.)
- If you mean a “sub-project” or a member of a
set of projects proposed as a group, its PENNERA_PROPOSAL record
will have
a PARENT_PROP_NO
that is not null. (See Note, above.)
- When you say, “investigator” or “principal
investigator,” what do you mean?
- A proposal may have more than one associated PENNERA_PROPOSAL_INVESTIGATOR
record. Each proposal ought to have exactly one PENNERA_PROPOSAL_INVESTIGATOR
record with information on the Principal Investigator, or PI
(PI_FLAG='Y'). A proposal may have any number of PENNERA_PROPOSAL_INVESTIGATOR
records (including zero records) with information on the co-investigator(s),
if any (PI_FLAG='N').
- When including PENNERA_PROPOSAL_INVESTIGATOR information in
reports, note that multiple records will be returned for a given
proposal
if there are any co-investigators for the proposal. To avoid
having multiple records
returned, ask for information for only one of the following:
- the PI (the Principal Investigator for the proposal)
- the request
responsible investigator (the one responsible for a particular
request; it may or may not be the proposal PI)
- the fund
responsible investigator
(the one responsible for managing the entire 5- fund
(AWARD_FUND); it may or may not be the proposal PI)
- the account
responsible
investigator (the one responsible for managing a portion
of an award payment
that is being tracked by the AWARD_GL_ACCOUNT; it may
or may not be the
proposal PI)
- the parent PI, if any (the Principal Investigator
for the parent proposal--for example, the PI for the program
project proposal). See
Note, above.
-
If you want information on an investigator, do you want it as it was
at the time of the proposal, or do you want the latest available information?
PENNERA_PROPOSAL_INVESTIGATOR stores the investigator's name, primary
job appointment, and department (org.) data as it was at the time of
the
proposal (funding
cycle). For the current information about an investigator, see the
PENNERA_PEOPLE table where the PennID of the desired investigator matches
the PENN_ID
in the PENNERA_PEOPLE table. For investigators who are employees, EMPLOYEE_GENERAL
can also be used to retrieve current information.
- When you say, “organization,” which one
do you mean?
- Organizations for investigators: The home org. is the one that
owns the investigator's employee record and is responsible for
its maintenance.
The primary appointment org. is the one where the investigator
has his or her primary job appointment--roughly speaking, the one
with
the highest pay. The primary academic appointment org. is the one
where the investigator has his or her primary academic job appointment—the
highest-ranking faculty job appointment. The ERA primary org.
is the one used by default for the investigator in PennERA. The
investigator
org. is the one with
which he or she is affiliated for the purposes of the proposal.
- Organizations for the proposal (funding cycle): The PROPOSAL_RESP_ORG_CODE
identifies the org. responsible for the proposal at the time of
the proposal. If the proposal is for a sub-project, its
PROPOSAL_RESP_ORG_CODE may
differ from the PROPOSAL_RESP_ORG_CODE of its parent proposal.
To support organization-based security, the orgs. for the PI
and the parent PI are
also stored at the proposal level. (See Note, above)
- Organizations for the request (under a budget period): The
REQUEST_RESP_ORG_CODE identifies the org. responsible for the
request at the time of
the request. To support organization-based security, the orgs.
for the PI, the parent
PI, and the request responsible investigator are also stored
at the request level. (See Note, above)
- Organizations for the increment (under a budget period): The
INCREMENT_RESP_ORG_CODE identifies the org. responsible for
managing the science and the money
for the award increment at the time of the increment. The FUND_RESPONSIBLE_ORG
identifies the org. responsible for managing the General Ledger
fund (the increment's AWARD_FUND). The AWARD_ORG is the org.
segment of the
increment's AWARD_GL_ACCOUNT, the 26-position (seven-segment)
Accounting Flexfield used in the present General Ledger accounting
structure
(in use at Penn since July 1, 1996). To support organization-based
security,
the orgs. for the PI, the parent PI, and the account
responsible investigator are also stored at the increment level. (See
Note, above)
- For every org. code, the PennERA Proposals data collection
stores the associated school/center code. In some cases, it
might be easier
to query by school/center rather than by org
.
- When you say, “status,” what do you mean?
- The PENNERA_PROPOSAL table stores the PROPOSAL_STATUS—the
current status of the proposal (for the funding cycle or project
period). For
example, a proposal that had a status of 'In Process' yesterday
might have a status of 'Pending' today.
- The PENNERA_PROP_STAT_HIST table stores information on how
the proposal’s
status has changed over time.
- The PENNERA_REQUEST table stores the REQUEST_STATUS—the
current status of the request. For example, a request that
had a status
of 'Future' yesterday might have a status of 'Future Pending' today.
- The PENNERA_REQ_STAT_HIST table stores information on
how the request’s
status has changed over time.
- The PENNERA_INCREMENT table stores the INCREMENT_STATUS—the
current status of the increment. For example, a request that had
a status of 'Advance Account' yesterday might have a status of
'Awarded' today.
- The PENNERA_INCR_STAT_HIST table stores information on how the
increment’s status has changed over time.
- You might be interested in the PROPOSAL_TYPE ('New Project',
'Change of Grantee Inst', etc.), the REQUEST_TYPE
('Budget Period' or 'Supplement'), or the AWARD_TYPE ('New Project',
'Supplement'', 'Cost Sharing', etc.).
- When you say, “sponsor,” what do you
mean?
- The SPONSOR_CODE identifies the agency that was asked to fund
the proposal.
- If the proposal sponsor is covering the sponsor costs using
funding from another source, the ORIGINATING_SPONSOR_CODE in
the PENNERA_PROPOSAL
table identifies the sponsor that is the source of the funding.
- How do you want the query results sorted?
It is more efficient to sort in the Business Objects report than in the
query, when possible. If you must sort in the query, it can be more
efficient to sort on an indexed data element.
- How will I know if the query results are correct?
- Sometimes, just looking at the query results can help you spot
glaring errors. (“Why am I getting so many lines per proposal? Oops—I
forgot to ask for information about the principal investigator only!”)
- If you have access to the Proposal Tracking module of PennERA,
you can use it to check some query results.
- When do you want the report?
|
