Penn Computing

University of Pennsylvania
Penn Computing << go backback
BRIMFUND_OBJ_EXP Table - Data Element Index   Tables and Data Elements   BRIM Home   Data Warehouse Home

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.

BRIMFUND_OBJ_EXP Table - Data Element Index   Tables and Data Elements   BRIM 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