Penn Computing

Penn Computing

Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

 
  UPDATES
Current Load Status
Regular Availability
 
  INFORMATION
FAQs & Tips
Password Changer
Support services
Security
About the Data Warehouse
Data Administration
 
  DATA COLLECTIONS
Advancement
Assets
BBR
BRIM
Express Mail
Facilities
Faculty
GAR
General Ledger
Infrastructure
ISSS-iOffice
Learning Management
Position Inventory
Research-PennERA Proposals
Research Inventory
Salary Management
Student
Travel Expense Management
Tuition Distribution
World Travel
Cross-Collection

Notes on the PennERA Proposals Snapshots Diagram

·return to the PennERA Proposals Snapshots diagram
  • The diagram identifies the tables that are included in the PennERA Proposals Snapshots Universe and the join criteria for the tables.
  • Note that for each set of parent codes (for example, organization parents), there is only one physical table (for example, SPFY_PARENT_ORG_CODES). In Business Objects, the Universe has been designed with more than one Class for each parent table, to facilitate reporting on parents and grandparents at the same time. The Business Objects Class names for the parent Classes end in 'P'; those for the grandparent Classes end in 'Gp'.
  • The diagram and the Universe show SPFY Org Codes separately from SPFY Resp Org Codes, but there is only one physical table: SPFY_ORG_CODES.
  • An asterisk (*) in the diagram indicates that, in addition to being joined to the other table(s) as shown in the diagram, the table is joined to at least one reference table.
    • Usually, these are outer joins. For example, SPFY_ORG_CODES.SPFY_ORGANIZATION_CODE(+) = SPFY_PENNERA_PROPOSAL_V.PROPOSAL_RESP_ORG_CODE and SPFY_ORG_CODES.SPFY_SNAP_FY(+) = SPFY_PENNERA_PROPOSAL_V.SNAPSHOT_FY
    • The tables whose names begin with SPFY are the ones that are refreshed by the PennERA Proposals snapshot process. As illustrated in the bullet point above, when two SPFY tables are joined to each other, the snapshot fiscal year in one table matches the snapshot fiscal year in the other table. However,
      • when an SPFY reference table is joined to EMPLOYEE_GENERAL_SNAP, the SPFY table's snapshot fiscal year matches the calendar year in EMPLOYEE_GENERAL_SNAP. Also, because the SPFY tables generally store snapshots that were taken in December, the join includes a condition to retrieve EMPLOYEE_GENERAL_SNAP's December data. For example: SPFY_ORG_CODES.SPFY_ORGANIZATION_CODE(+) = EMPLOYEE_GENERAL_SNAP.PRI_ACAD_DEPT_ORG and SPFY_ORG_CODES.SPFY_SNAP_FY(+) = EMPLOYEE_GENERAL_SNAP.CALENDAR_YEAR and EMPLOYEE_GENERAL_SNAP.CALENDAR_MONTH=12
      • when an SPFY reference table is joined to BALANCES, the SPFY table's snapshot fiscal year + 1 matches the fiscal year in BALANCES. For example: SPFY_CNAC_CODES.SPFY_CNAC_CODE=BALANCES.COA_CNAC and to_number(SPFY_CNAC_CODES.SPFY_SNAP_FY)+1 = to_number(BALANCES.FISCAL_YEAR).
    • EMPLOYEE_GENERAL_SNAP is joined to
      • SPFY_CNAC_CODES
      • SPFY_ORG_CODES
    • SPFY_FUND_CODES is joined to
      • FUND_BILLING_FORMAT_CODES
      • FUND_BILLING_FREQ_CODES
      • FUND_FINAL_RPT_CODES
      • FUND_INVESTMENT_CODES
      • FUND_LOC_RPTG_CODES
      • FUND_RECLASS_CODES
      • FUND_RPT_FORMAT_CODES
      • FUND_RPT_FREQ_CODES
      • FUND_SFS_CATEGORY_CODES
      • FUND_SPONSORS
      • SPFY_CNAC_CODES
      • SPFY_ORG_CODES
      • SPFY_PURPOSE_CODES
    • SPFY_ORG_CODES is joined to SPFY_PURPOSE_CODES
    • SPFY_PENNERA_INCREMENT is joined to
      • SPFY_CENTER_REF_CODES
      • SPFY_CNAC_CODES
      • SPFY_FUND_CODES, which is joined to
        • FUND_BILLING_FORMAT_CODES
        • FUND_BILLING_FREQ_CODES
        • FUND_FINAL_RPT_CODES
        • FUND_INVESTMENT_CODES
        • FUND_LOC_RPTG_CODES
        • FUND_RECLASS_CODES
        • FUND_RPT_FORMAT_CODES
        • FUND_RPT_FREQ_CODES
        • FUND_SFS_CATEGORY_CODES
        • FUND_SPONSORS
        • SPFY_CNAC_CODES
        • SPFY_ORG_CODES
        • SPFY_PARENT_ORG_CODES
      • SPFY_ORG_CODES
      • SPFY_PENNERA_INVESTIGATOR (This join is used to get the name of the Principal Investigator (PI) based on the snapshot fiscal year, the Institution No. for the proposal, and the PennID of the PI, where the SPFY_PENNERA_INVESTIGATOR.PI_FLAG = 'Y'.)
      • SPFY_PENNERA_PEOPLE (This join is used to get the name of the Account Responsible Investigator based on the snapshot fiscal year and the investigator's Penn ID as stored in SPFY_PENNERA_INCREMENT.ACCOUNT_RESP_INVESTIGATOR. SPFY_PENNERA_INVESTIGATOR is not used for this join since it might have more than one record for the Account Responsible Investigator, because the investigator might have more than one investigator org. for the proposal, or might be associated with the proposal both as the PI and in a different role.)
      • SPFY_PENNERA_SPONSOR
      • SPFY_PROGRAM_CODES
    • SPFY_PENNERA_INVESTIGATOR is joined to
      • SPFY_CNAC_CODES
      • SPFY_ORG_CODES
    • SPFY_PENNERA_PEOPLE is joined to
      • SPFY_CNAC_CODES
      • SPFY_ORG_CODES
    • SPFY_PENNERA_PERIOD is joined to
      • SPFY_CNAC_CODES
      • SPFY_ORG_CODES
      • SPFY_PENNERA_INVESTIGATOR (This join is used to get the name of the Principal Investigator (PI) based on the snapshot fiscal year, the Institution No. for the proposal, and the PennID of the PI, where the SPFY_PENNERA_INVESTIGATOR.PI_FLAG = 'Y'.)
    • SPFY_PENNERA_PROPOSAL is joined to
      • SPFY_CNAC_CODES
      • SPFY_FUND_CODES, which is joined to
        • FUND_BILLING_FORMAT_CODES
        • FUND_BILLING_FREQ_CODES
        • FUND_FINAL_RPT_CODES
        • FUND_INVESTMENT_CODES
        • FUND_LOC_RPTG_CODES
        • FUND_RECLASS_CODES
        • FUND_RPT_FORMAT_CODES
        • FUND_RPT_FREQ_CODES
        • FUND_SFS_CATEGORY_CODES
        • FUND_SPONSORS
        • SPFY_CNAC_CODES
        • SPFY_ORG_CODES
        • SPFY_PARENT_ORG_CODES
      • SPFY_ORG_CODES
      • SPFY_PENNERA_INVESTIGATOR (Note: SPFY_PENNERA_PROPOSAL is joined to SPFY_PENNERA_INVESTIGATOR twice: (1) to get the name of the proposal's Principal Investigator (PI) based on the snapshot fiscal year, the Institution No. for the proposal, and the PennID of the PI, where the SPFY_PENNERA_INVESTIGATOR.PI_FLAG = 'Y', and (2) to get the name of the parent proposal's Principal Investigator (if any) based on the snapshot fiscal year and the Institution No. for the parent proposal, where the SPFY_PENNERA_INVESTIGATOR.PI_FLAG = 'Y'.
      • SPFY_PENNERA_SPONSOR
    • SPFY_PENNERA_PROPOSAL_ACTIVITY is joined to SPFY_PENNERA_PEOPLE
    • SPFY_PENNERA_REQUEST is joined to
      • GL_PERIODS
      • SPFY_CNAC_CODES
      • SPFY_ORG_CODES
      • SPFY_PENNERA_INVESTIGATOR (This join is used to get the name of the Principal Investigator (PI) based on the snapshot fiscal year, the Institution No. for the proposal, and the PennID of the PI, where the SPFY_PENNERA_INVESTIGATOR.PI_FLAG = 'Y'.)
      • SPFY_PENNERA_PEOPLE (This join is used to get the name of the Request Responsible Investigator based on the snapshot fiscal year and the investigator's Penn ID as stored in SPFY_PENNERA_REQUEST.REQUEST_RESP_INVESTIGATOR. SPFY_PENNERA_INVESTIGATOR is not used for this join since it might have more than one record for the Request Responsible Investigator, because the investigator might have more than one investigator org. for the proposal, or might be associated with the proposal both as the PI and in a different role.)
      • SPFY_PENNERA_SPONSOR
  • Most of the joins between BALANCES and other tables are not labelled in the diagram. Here are the conditions for those joins:
    • BALANCES.COA_CNAC = SPFY_CNAC_CODES.SPFY_CNAC_CODE and to_number(BALANCES.FISCAL_YEAR) = to_number(SPFY_CNAC_CODES.SPFY_SNAP_FY)+1
    • BALANCES.COA_ORG = SPFY_ORG_CODES.SPFY_ORGANIZATION_CODE and to_number(BALANCES.FISCAL_YEAR) = to_number(SPFY_ORG_CODES.SPFY_SNAP_FY)+1
    • BALANCES.COA_FUND = SPFY_FUND_CODES.SPFY_FUND_CODE and to_number(BALANCES.FISCAL_YEAR) = to_number(SPFY_FUND_CODES.SPFY_SNAP_FY)+1
    • BALANCES.COA_OBJECT = OBJECT_CODES.OBJECT_CODE
    • BALANCES.COA_OBJECT = SPFY_RCM_OBJECT_TREE.SPFY_OBJECT_CODE and to_number(BALANCES_V.FISCAL_YEAR) = to_number(SPFY_RCM_OBJECT_TREE.SPFY_SNAP_FY)+1
    • BALANCES.COA_PROGRAM=SPFY_PROGRAM_CODES.SPFY_PROGRAM_CODE and to_number(BALANCES.FISCAL_YEAR) = to_number(SPFY_PROGRAM_CODES.SPFY_SNAP_FY)+1
    • BALANCES.COA_CNAC = SPFY_CENTER_REF_CODES.SPFY_CNAC_CODE and BALANCES.COA_CREF = SPFY_CENTER_REF_CODES.SPFY_CENTER_REF_CODE and to_number(BALANCES.FISCAL_YEAR) = to_number(SPFY_CENTER_REF_CODES.SPFY_SNAP_FY)+1
    • BALANCES.COA_RESPONSIBLE_ORG = SPFY_ORG_CODES.SPFY_ORGANIZATION_CODE(+) and to_number(BALANCES.FISCAL_YEAR) = to_number(SPFY_ORG_CODES.SPFY_SNAP_FY(+))+1
    • BALANCES.FISCAL_MONTH_SEQ = GL_PERIODS.FISCAL_MONTH_SEQ and BALANCES.FISCAL_YEAR = GL_PERIODS.FISCAL_YEAR
top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


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