|
|
Notes on 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
|
|
|
|
