AWARD Table - Data Element Index    Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home

AWARD Table

Explanation
Records data about each sponsored award or advance account, including information such as the financial account, the school and department administering the award, the duration of the award, whether animals or human subjects are used, and various funding amounts. There is one record per award per funding increment per account. For example, a three year NIH award split between two accounts each year would have six records. Note: If the grant was awarded by the National Institute of Health (NIH), related data elements have values; otherwise, they are null.

Common Uses

• Finding awards from a particular sponsor. ("Which awards are from agency 1043, the Peace Corps?")
• Listing awards for studies in which animals are used. ("Which of our grants involve animals?")
• Listing data about a particular award based on the award id. ("What information is there about award 142750201?")
• Finding all awards for an account number for a particular time frame. ("What awards were received that went into account 525187 in fiscal year 1996?")

AWARD_ID

ACCOUNT_NUMBER

AWARD_ACCOUNT_NUMBER

AWARD_CNAC

AWARD_CREF

AWARD_FUND

AWARD_ORG

AWARD_PROGRAM

AWARD_ID

AWARD_RESP_ORG

AWARD_RESP_SCHOOL

CO_PI_HOME_ORG

CO_PI_HOME_SCHOOL

CURRENT_BUDGET_END

CURRENT_BUDGET_START

FISCAL_YR_END

FISCAL_YR_START

FISCAL_YR_STATUS

FUND_RESPONSIBLE_ORG

ORG_LOG_NUMBER

PI_HOME_ORG

PI_HOME_SCHOOL

PI_PRI_APPT_ORG

PI_PRI_APPT_SCHOOL

PROJECT_ID

PROJECT_SEGMENT_ID

PROPOSAL_ID

PROP_RESP_ORG

PROP_RESP_SCHOOL

AGENCY

BALANCES

CENTER_REF_CODES

CNAC_CODES

FUND_CODES

MONTHLY_EXPENDITURE

ORA_PURPOSE

ORG_CODES

ORG_OLD_TO_NEW

PROGRAM_CODES

PROJECT_EXPENDITURE

PROPOSAL

SPON_PROJ_INVESTIGATOR

SPON_PROJ_SUBCONTRACTOR

• NIH fields are populated only if the agency is NIH (Agency Code 1110), and the award is a federal grant (Grant Contract Code is G). For other records, the NIH fields will be null. Note that the NIMH became an institute of the NIH in 1992. NIH fields are populated for NIMH grants for records dating from 1992 and thereafter, but not for records dating from before 1992. Also, prior to June, 2002, NIH Grant Serial Number was a 5-digit number. In June, 2002, NIH Grant Serial Number became a 6-digit number, and records for existing NIH grants had a leading zero added to their NIH Grant Serial Numbers.
• ACCOUNT_NUMBER stores 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 some systems.) AWARD_ACCOUNT_NUMBER stores ACCOUNT_NUMBER's counterpart, the 22-position (six segment) Accounting Flexfield used in the present General Ledger accounting structure (in use at Penn since July 1, 1996). It is the 26-position (7 segment) Accounting Flexfield, without the Object code segment. AWARD_ACCOUNT_NUMBER is the six segment combination assigned at the time of the award. This combination ought to be used for posting transactions to BEN Financials, but people may post to any combination of AWARD_CNAC, AWARD_ORG, AWARD_BC, AWARD_PROGRAM, and AWARD_CREF. Only the AWARD_FUND segment is reliable for finding postings in BEN Financials. (For awards that were not active July 1, 1996 and thereafter--AWARD_CNAC and AWARD_ORG are populated, but the other segments are null.)
• When counting awards, to avoid double counting, count by the first 5 digits of the AWARD_ID (the PROJECT_ID) or by the first 7 digits (the PROJECT_SEGMENT_ID), rather than by the AWARD_ID, and use SELECT DISTINCT (in Business Objects, using the Modify Query screen, select Options / No Duplicates).
• An award (PROJECT_SEGMENT_ID) may be split among 2 or more accounts-one prime account and one or more sub-accounts. The principal investigator associated with the prime account is the principal investigator for the award as a whole. (The prime accou nt has the AWARD_ACCOUNT_TYPE set to P.)
• COMMITTED_YEARS is the number of years beyond the END_DATE that the sponsor has committed to fund the project (not the award or account). Do not sum COMMITTED_YEARS.
• Note that it is possible to have an AWARD record with null values for PROPOSAL_ID and ORIGINAL_PROPOSAL_DATE. This means that the original proposal for the award cannot be determined.
• For an NIH program project that is split into sub-program projects, there is a separate AWARD record for each sub-program project.
• When reporting on Organizations, be sure to select the data element that meets your needs. The PROP_RESP_ORG and PROP_RESP_SCHOOL identify the Organization and School/Center responsible for the proposal at the time of the proposal. The AWARD_RESP_ORG and AWARD_RESP_SCHOOL identify the Organization and School/Center responsible for managing the science and the money for the award at the time of the award. The AWARD_RESP_ORG is designated as the FUND_RESPONSIBLE_ORG when the fund is set up by Research Accounting. The FUND_RESPONSIBLE_ORG identifies the Organization responsible for managing the fund. The PI_PRI_APPT_SCHOOL and PI_PRI_APPT_ORG identify the School/Center and Organization for the Principal Investigator's primary job appointment as of the time of the award.. The PI_HOME_SCHOOL, PI_HOME_ORG, CO_PI_HOME_SCHOOL, and CO_PI_HOME_ORG identify the home Schools/Centers and Organizations for the Principal Investigator and the Co-Principal Investigator as of the time of the award.. The home School/Center and Organization are the ones that own the employee's record and are responsible for its maintenance. AWARD_ORG is the Organization segment of AWARD_ACCOUNT_NUMBER, the 22-position Accounting Flexfield used in the present General Ledger accounting structure (in use at Penn since July 1, 1996).
• Users with University-wide access may see all the data elements for every AWARD record. Users with Organization-based access may see secured dollar amount data elements only for records which they are authorized to access based on the PROP_RESP_ORG, AWARD_RESP_ORG, FUND_RESPONSIBLE_ORG, PI_PRI_APPT_ORG, PI_HOME_ORG, CO_PI_HOME_ORG, or AWARD_ORG. (Secured dollar amount data elements are the INDIRECT_COST_AMOUNT, DIRECT_COST_AMOUNT, TOTAL_COMPENSATION_AMOUNT, TUITION_AMOUNT, EQUIPMENT_AMOUNT, FOREIGN_TRAVEL_AMOUNT, DOMESTIC_TRAVEL_AMOUNT, OTHER_EXPENSES_AMOUNT, UNIVERSITY_EXPENSES_AMOUNT, SUB_CONTRACTOR_AMOUNT, and PROJECT_COST_SHARING_AMOUNT.) Users with Organization-based access may see the all other data elements (including TOTAL_AGENCY_AMOUNT) for every record.
• To facilitate Organization-based security, null values for Organization codes will be changed to '.' in the Warehouse. The data elements that may contain '.' (instead of being null) are: PROP_RESP_ORG, AWARD_RESP_ORG, FUND_RESPONSIBLE_ORG, PI_PRI_APPT_ORG, PI_HOME_ORG, CO_PI_HOME_ORG, and AWARD_ORG.
• TOTAL_COMPENSATION_AMOUNT, TUITION_AMOUNT, EQUIPMENT_AMOUNT, FOREIGN_TRAVEL_AMOUNT, DOMESTIC_TRAVEL_AMOUNT, OTHER_EXPENSES_AMOUNT, and UNIVERSITY_EXPENSES_AMOUNT are not used for records dating from November 24, 1997 and thereafter. They are populated only for the -01 account for a given award and funds supplement, so that they will not be double counted when summarized by award.
• SUB_CONTRACTOR_AMOUNT is populated only for the -01 account for a given award and funding increment, so that it will not be double counted when summarized by award. For records dating from November 24, 1997 and thereafter, a breakdown of this amount by subcontractor is available in the SPON_PROJ_SUBCONTRACTOR table. The breakdown is not available for records dating from before November 24, 1997.
• PROJECT_COST_SHARING_AMOUNT is populated only for the -01 account for a given award and funding increment, so that it will not be double counted when summarized by award. PROJECT_COST_SHARING_SOURCE is also populated only for the -01 account for a given award and funding increment.
• The value for a fund's F_AND_A_RATE in the Balances table may differ from the INDIRECT_COST_RATE in the Proposal and Award tables. The INDIRECT_COST_RATE in the Proposal table is the rate to be used to calculate the indirect cost recovery amount, as quoted in the proposal for the project. Its value is stored as ###.## (for example, the value 12% would be stored as 12). The INDIRECT_COST_RATE in the Award table is the rate the sponsor awarded; its value is stored in the same format used in the Proposal table. The F_AND_A_RATE in the Balances table is stored in a different format (.########--for example, 12% is stored as .12). This is the rate actually used to calculate the indirect cost recovery amounts each week. In cases where the awarded rate actually floats based on the federally negotiated rate, the value for the F_AND_A_RATE in the Balances table may differ from the value for the INDIRECT_COST_RATE in the Award table.

AWARD Table - Data Element Index    Tables and Data Elements   Sponsored Projects Home   Data Warehouse Home