Penn Computing

University of Pennsylvania
Penn Computing << go backback
PENNERA_FUND_ADJ Table - Data Element Index    Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

PENNERA_FUND_ADJ Table

DWADMIN Schema

Explanation
Records adjustments to the special budgets for sponsored project funds (5- funds). The PENNERA_FUND_ADJ table stores the adjustments as posted by the Office of Research Services (ORS) in the PennERA Proposal Tracking system. Adjustments may be made during the life of the fund or as part of the fund closeout process. These adjustments are fed to the General Ledger (BEN Financials), where the special budgets for each sponsored project fund are stored under Object codes PBUD, PBIL, and PTCS. PBUD is the authorized project budget, the total amount that may be spent on the project. PBIL is the authorized project billable amount, the total amount that may be billed to the sponsor for the project (the fund sponsor). PTCS is authorized project total cost sharing, the total amount provided for the project by a source other than the project's sponsor. (For example, say the University receives a grant for a project whose total budget is $100,000. The sponsor agrees to pay 75% ($75,000) and the University agrees to pay 25% ($25,000). The $25,000 is the PTCS.)

There is one record per fund per special budget object per adjustment. A record is uniquely identified by its FUND_ADJ_PK. For example, if two closeout adjustments are posted on the same day to PBUD for fund 512345, there are two PENNERA_FUND_ADJ records, each with its unique FUND_ADJ_PK.

Common Uses

  • Reporting on sponsored project fund adjustments by adjustment type. ("How much of the money in this fund comes from carryover?")
  • Calculating a special budget balance per PennERA, for comparison with the special budget balance in the General Ledger.
Primary Key Indexed Data Elements Related Tables
FUND_ADJ_PK
ADJUSTMENT_TYPE
AWARD_FUND
FUND_ADJ_PK
FUND_RESPONSIBLE_ORG
INSTITUTION_NO
PARENT_PI_ERA_PRI_ORG_CODE
PARENT_PI_HOME_ORG_CODE
PARENT_PI_INV_ORG_CODE
PAR_PI_PRIACADAPPT_ORG_CODE
PARENT_PI_PRI_APPT_ORG_CODE
PARENT_PROP_RESP_ORG_CODE
PENDING
PI_ERA_PRIMARY_ORG_CODE
PI_HOME_ORG_CODE
PI_INVESTIGATOR_ORG_CODE
PI_PRI_ACAD_APPT_ORG_CODE
PI_PRI_APPT_ORG_CODE
PROP_NO
PROPOSAL_RESP_ORG_CODE
SPECIAL_BUDGET_OBJECT
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_INCREMENT


Cautions

  • Each adjustment to a special budget for a sponsored project fund includes information on the proposal to which that fund belongs. There are two different unique identifiers for a proposal (funding cycle): INSTITUTION_NO and PROP_NO. INSTITUTION_NO is the identification number assigned by the University. PROP_NO is the identification number used internally by the PennERA system. For a given record, INSTITUTION_NO will not have the same value as PROP_NO. While either one may be used, INSTITUTION_NO is the one recommended for display in reports.
  • Users with University-wide access may see all the data elements for every PENNERA_FUND_ADJ record. Users with Organization-based access may see most of the data elements for a given PENNERA_FUND_ADJ record, but may see the ADJUSTMENT_AMOUNT only if they are authorized to see that record based on one or more of the orgs. in that record.
    • An Organization is a subdivision of the University created for management purposes. Examples of orgs. are the Leonard Davis Institute (org. code 0791) and Biochemistry (org. code 5103).
    • The orgs. that apply to Organization-based access are the FUND_RESPONSIBLE_ORG, PROPOSAL_RESP_ORG_CODE, PI_HOME_ORG_CODE, PI_PRI_APPT_ORG_CODE, PI_PRI_ACAD_APPT_ORG_CODE, PI_ERA_PRIMARY_ORG_CODE, PI_INVESTIGATOR_ORG_CODE, PARENT_PROP_RESP_ORG_CODE, PARENT_PI_HOME_ORG_CODE, PARENT_PI_PRI_APPT_ORG_CODE, PAR_PI_PRIACADAPPT_ORG_CODE, PARENT_PI_ERA_PRI_ORG_CODE, and PARENT_PI_INV_ORG_CODE.
    • To facilitate Organization-based security, the orgs. listed above have their values set to '.' if they would otherwise be null.
  • The ADJUSTMENT_DATE includes both the date and the time. For example, the value might be 8/31/2004 03:34:33 PM. Business Objects hint: to select adjustments for a particular date (say, 8/31/2004), set your query conditions to ADJUSTMENT_DATE is greater than or equal to 8/31/2004 and ADJUSTMENT_DATE is less than 9/1/2004. (The system will supply the time--12:00:00 AM--for both dates.) By default, the time will not appear in your report, but you can change the format of the cell if you desire.
  • A PENNERA_FUND_ADJ record reflects an adjustment to a given special budget for a given fund. Some types of adjustment (such as a closeout adjustment) imply a change to just one fund. Other types of adjustment (such as a carry-over adjustment) imply changes to more than one fund--for example, adjustment 1 to subtract money from fund 512345, and adjustment 2 to add money to fund 523456. The PENNERA_FUND_ADJ table is not designed to group adjustment 1 with adjustment 2. The ADJUSTMENT_COMMENTS for adjustment 1 might or might not include information on the fund affected by adjustment 2, and vice versa.
  • Because the PENNERA_FUND_ADJ table stores detail-level data, it is not recommended that the table be joined to any other table. Instead, summarize the data from the 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 PENNERA_FUND_ADJ summary, and link the summaries based on that data element. (Business Objects users may use the reporting feature to link data providers.)
  • The increments for a fund (in the PENNERA_INCREMENT table) plus the adjustments for the SPECIAL_BUDGET_OBJECT for the fund (in the PENNERA_FUND_ADJ table) should match the fund's special budget project-to-date balance in the General Ledger as follows:
    • include just the adjustments for the desired SPECIAL_BUDGET_OBJECT that have been posted to the General Ledger (PENDING='N')
    • when checking PBUD, include just the sponsor dollars (AWARDED_TOT_SPON_COSTS) for increments for periods whose status is 'Awarded' or 'Advance Account'
    • when checking PBIL, include just the sponsor dollars (AWARDED_TOT_SPON_COSTS) for increments for periods whose status is 'Awarded'
    • when checking PTCS, include just the non-sponsor dollars (AWARDED_NONSPON_COSTS) for increments for periods whose status is 'Awarded' or 'Advance Account'
    • when checking the special budget project-to-date balance for a given fund, select the BALANCES record where:
      • the accounting period is the current open period
      • the org. segment of the account number is the same as the fund responsible org. (BALANCES.COA_ORG = BALANCES.COA_RESPONSIBLE_ORG)
      • the budget check segment of the account number is '4'
      • the fund segment of the account number is the desired fund
      • the object segment of the account number is the desired special budget object code
      • the program segment of the account number is '0000, and
      • the center reference (CREF) segment of the account number is '0000'
  • A sponsored project fund (5- fund) either is a cost sharing fund, or it isn't. If it has a PBUD or PBIL balance, it ought not to have a PTCS balance, and vice versa. For example, say the University receives a grant for a project whose total budget is $100,000. The sponsor agrees to pay 75% ($75,000) and the University agrees to pay 25% ($25,000). Fund 500001 is set up with a PBUD account ($75,000) and a PBIL account ($75,000). No PTCS account is created for fund 500001. Fund 500002 is the cost sharing fund. It is set up with a PTCS account ($25,000). Neither a PBUD account nor a PBIL account is created for fund 500002.
  • Special budget adjustments should be posted to the General Ledger only by the Office of Research Services (ORS) using the PennERA Proposal Tracking system. No special budget adjustments ought to be posted to future accounting periods.

Source
The Proposal Tracking module of PennERA, 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_FUND_ADJ Table - Data Element Index    Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania