BRIMFUND_OBJ_EXP Table
Explanation
A lightly summarized version of the General Ledger detail records, with the
actual amounts pertaining to sponsored project funds whose sponsors send payments
as they are billed for project expenses. A sequence number is used to uniquely
identify each record.
Common Uses
- Analyzing "billed expenses" (the actual daily amounts for an expense-based
invoice or its line items), rather than reporting the total for the invoice
or for its line items
- Analyzing "unbilled expenses" (the actual daily amounts that,
as of the time being analyzed, had yet to be included in invoices for funds
whose sponsors send payments as they are billed for project expenses)
Primary Key |
Indexed Data Elements |
Related Tables |
BRMFUND_OBJEXP_SEQNO
|
ACCT_PER_INVOICE_DATED ACCT_PER_POSTED CAL_YEAR_POSTED CAL_YR_INVOICE_DATED FISCAL_MON_SEQ_INV_DATED FISCAL_MON_SEQ_POSTED FISCAL_YEAR_POSTED FISCAL_YR_INVOICE_DATED FUND_RESP_ORG GL_EXTRACT_DATE INTERNAL_REF_NO MONTH_INVOICE_DATED MONTH_POSTED OBJECT_CODE SB_OBJECT_PARENT SORT_PERIOD_INVOICE_DATED SORT_PERIOD_POSTED
|
BRIM_EXP_CODES BRIM_FUNDS BRIM_MASTER BRIMFUND_SB_EXP
|
Cautions
- There is usually, but not always, one record per FUND_CODE, ACCT_PER_POSTED,
OBJECT_CODE, and GL_EXTRACT_DATE. Because there can sometimes be more than
one, BRMFUND_OBJEXP_SEQNO is used to uniquely identify a record.
- The detail actuals in the BRIMFUND_OBJ_EXP table are often referred to as
expenses. In reality, they include amounts for expense, revenue, and asset
Object codes that are relevant to invoices. The relevant Object codes are
children of parent Objects ranging from SB01 to SB41.
- The BRIMFUND_OBJ_EXP table contains records only for funds which, according
to BEN Financials, had a billing format code between 01 and 06 at the time
the General Ledger detail actuals were extracted from the General Ledger to
be fed to BRIM. (That is, detail actuals are extracted if the billing format
fund attribute in the General Ledger has a value between 01 and 06, inclusive.)
If the BEN Financials record for the fund had a billing format of 07 or 08
at the time the detail actuals extract job ran, no detail actuals for that
fund were fed to BRIM. The GL_EXTRACT_DATE indicates when the detail actuals
were extracted from the General Ledger.
- Some BRIMFUND_OBJ_EXP records may remain unbilled if detail actuals were
extracted for the fund before the fund's billing format code changed from
a value between 01 and 06 to 07 or 08.
- Some BRIM_MASTER and BRIMFUND_SB_EXP records may not have any related BRIMFUND_OBJ_EXP
records if an expense-based invoice was created for the fund before the fund's
billing format code changed from 07 or 08 to a value between 01 and 06. BRIM_MASTER
and BRIMFUND_SB_EXP records that were converted from the system used before
BRIM will also not have any related BRIMFUND_OBJ_EXP records.
- The beginning and ending dates of an accounting period are not the same
as the first and last day of the calendar month. Invoice dates that fall during
or after JUL-93 are assigned to the correct accounting period. However, invoice
dates that fall during or before JUN-93 are assigned to the accounting period
that matches the calendar month and year.
- BRIM is not run based on accounting periods. Therefore, any actuals that
were posted to the ADJ period in the General Ledger are assigned to the JUN
period in BRIM.
- To report on billed expenses for one or more specified accounting periods,
records should be selected based on one of the following:
- ACCT_PER_INVOICE_DATED (such as OCT-02)
- SORT_PERIOD_INVOICE_DATED (such as 200210)
- MONTH_INVOICE_DATED and CAL_YR_INVOICE_DATED (such as 10 and 2002,
respectively)
- FISCAL_MON_SEQ_INV_DATED and FISCAL_YR_INVOICE_DATED (such as 4 and
2003, respectively)
- BRIMFUND_OBJ_EXP is the source of some summarized amounts stored in BRIM_FUNDS.
These summarized amounts are PERIOD_UNBILLED_ACTUAL_EXP, FYTD_UNBILLED_ACTUAL_EXP,
and PJTD_UNBILLED_ACTUAL_EXP. Unlike BRIM_FUNDS, the BRIMFUND_OBJ_EXP table
is not a snapshot table, but is replaced nightly. For the current accounting
period, the summarized values in BRIM_FUNDS match the totals of the relevant
records in BRIMFUND_OBJ_EXP. However, for a closed accounting period, the
values might not match. The likely reason for the discrepancy is that an invoice
has been voided since the end of the closed accounting period (so the actual
amounts associated with the invoice changed status from billed to unbilled).
- To report on project-to-date unbilled expenses as of a particular accounting
period (say, OCT-02), select ACTUAL_AMOUNT where (SORT_PERIOD_POSTED <=
'200210') AND ((SORT_PERIOD_INVOICE_DATED > '200210') OR (SORT_PERIOD_INVOICE_DATED
IS NULL)). This is the basis of PJTD_UNBILLED_ACTUAL_EXP in BRIM_FUNDS.
- To report on fiscal year-to-date unbilled expenses as of a particular accounting
period (say, OCT-02, which falls in fiscal year 2003), select ACTUAL_AMOUNT
where (FISCAL_YR_POSTED = '2003') (SORT_PERIOD_POSTED <= '200210') AND
((SORT_PERIOD_INVOICE_DATED > '200210') OR (SORT_PERIOD_INVOICE_DATED IS
NULL)). This is the basis of FYTD_UNBILLED_ACTUAL_EXP in BRIM_FUNDS.
- To report on unbilled expenses for, say, OCT-02 (that is, to find expenses
posted during the accounting period that were still unbilled as of the end
of the accounting period), select ACTUAL_AMOUNT where (SORT_PERIOD_POSTED
= '200210') AND ((SORT_PERIOD_INVOICE_DATED > '200210') OR (SORT_PERIOD_INVOICE_DATED
IS NULL)). This is the basis of PERIOD_UNBILLED_ACTUAL_EXP in BRIM_FUNDS.
- BRIM_EXTRACT_DATE is the date when the record was loaded into the Data Warehouse.
This is usually the same date that the record was extracted from BRIM
- Users with University-wide access may see all the BRIMFUND_OBJ_EXP records.
Users with Organization-based access may see only those records which they
are authorized to access based on the FUND_RESP_ORG.
- Use caution when displaying BRIMFUND_OBJ_EXP and GL_DETAIL data in the same
report. Do not join the tables-both tables may have more than one record per
fund, per Object, per date. Business Objects users can create a report with
two data providers. For each data provider, be sure to specify the same desired
accounting periods or date range. If a linked report is desired, both data
providers should include SUMmed amounts as well as fund code and Object code
in the list of Result Objects, and the data providers should be linked based
on the fund code and Object code.
- Do not attempt to join BRIMFUND_OBJ_EXP to BALANCES. Both tables have more
than one record per fund, per Object, per accounting period.
Source
The AREXIM and AREXYM tables in BRIM, the Billing and Receivables Information
Management system used by the Office of Research Services. The ultimate source
of the records, and of most of the data in those records, is the General Ledger
in BEN Financials (via the SBTEXP.txt extract; see glsq2112d.sql and glsq2112g.sql).
BRIM is used to track award payments due and received for sponsored project
funds where the sponsors either send payments as they are billed for project
expenses, or send payments based on a schedule. BRIM does not track sponsored
project funds where payment is handled via letters of credit.
Questions about this page? Email us at da-staff@isc.upenn.edu
|