Penn Computing

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

BRIMFUND_SB_EXP Table

Explanation
Line items for expense-based invoices for sponsored project funds. There is one record per invoice per line item.

Common Uses

  • Reporting on the line items for an invoice, rather than on the total for the invoice as a whole.
  • Comparing the actual amount for an invoice or its line items to the billed amount.
  • Retrieving the variance between the actual amount and the invoiced amount, for a line item, or for an invoice as a whole.
  • Getting the project-to-date or fiscal year-to-date totals as of an invoice date, for a line item, or for an invoice as a whole.

Primary Key Indexed Data Elements Related Tables
INTERNAL_REF_NO
SB_OBJECT_PARENT
ACCT_PER_INVOICE_DATED
CALENDAR_YEAR_INVOICE_DATED
FISCAL_MON_SEQ_INV_DATED
FISCAL_YR_INVOICE_DATED
FUND_CODE
FUND_ INVOICE_NUMBER
FUND_RESP_ORG
INTERNAL_REF_NO
MONTH_INVOICE_DATED
SB_OBJECT_PARENT
SORT_PERIOD_INVOICE_DATED
SPONSOR_CODE
BRIM_EXP_CODES
BRIM_FUNDS
BRIM_MASTER
BRIMFUND_OBJ_EXP

Cautions
  • If there is no project-to-date activity as of the invoice date for a line item on an invoice, there is no record for that line item for that invoice.


  • The BRIMFUND_SB_EXP table contains records only for expense-based invoices. Auto pay invoices will not have any records in the BRIMFUND_SB_EXP table. However, an auto pay fund may have some expense-based invoices, and those invoices will have records in the BRIMFUND_SB_EXP table.

  • The ACTUAL_AMTs in the BRIMFUND_SB_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 expense start and end dates for an invoice (INTERNAL_REFERENCE_NO) are not available in BRIMFUND_SB_EXP, but may be found in BRIM_MASTER.

  • An expense-based invoice may be created even though BRIM has no record of any unbilled actual amounts that fall between the expense start and end dates. In such cases, the variance is suspect.

  • BRIM is not run based on accounting periods. Therefore, there are no invoices assigned to the ADJ period.

  • The beginning and ending dates of an accounting period are not the same as the first and last day of the calendar month. Dates that fall during or after JUL-93 are assigned to the correct accounting period. However, dates that fall during or before JUN-93 are assigned to the accounting period that matches the calendar month and year.

  • To report on invoices 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 CALENDAR_YEAR_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_SB_EXP is the source of some summarized amounts stored in BRIM_FUNDS. These summarized amounts are BRIM_FYTD_INVOICE_VARIANCE, BRIM_PERIOD_INVOICE_VARIANCE, BRIM_PJTD_INVOICE_VARIANCE, FYTD_ACTUAL_AMOUNT, PERIOD_ACTUAL_AMOUNT, and PJTD_ACTUAL_AMOUNT. Unlike BRIM_FUNDS, the BRIMFUND_SB_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_SB_EXP. However, for a closed accounting period, the values may not match. The likely reason for the discrepancy is that an invoice has been voided since the end of the closed accounting period.

  • To report on project-to-date actual amounts for expense-based invoices as of a particular accounting period (say, OCT-02), select ACTUAL_AMT where (VOID_INVOICE_FLAG = 'N') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of PJTD_ACTUAL_AMOUNT in BRIM_FUNDS.

  • To report on fiscal year-to-date actual amounts for expense-based invoices as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select ACTUAL_AMT where (VOID_INVOICE_FLAG = 'N') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of FYTD_ACTUAL_AMOUNT in BRIM_FUNDS.

  • To report on actual amounts for expense-based invoices dated during a particular accounting period (say, OCT-02), select ACTUAL_AMT where (VOID_INVOICE_FLAG = 'N') AND (SORT_PERIOD_INVOICE_DATED = '200210'). This is the basis of PERIOD_ACTUAL_AMOUNT in BRIM_FUNDS.

  • To report on project-to-date invoice variances stored in BRIM for expense-based invoices as of a particular accounting period (say, OCT-02), select INVOICE_VARIANCE where (VOID_INVOICE_FLAG = 'N') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of BRIM_PJTD_INVOICE_VARIANCE in BRIM_FUNDS.

  • To report on fiscal year-to-date invoice variances stored in BRIM for expense-based invoices as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select INVOICE_VARIANCE where (VOID_INVOICE_FLAG = 'N') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of BRIM_FYTD_INVOICE_VARIANCE in BRIM_FUNDS.

  • To report on invoice variances stored in BRIM for expense-based invoices dated during a particular accounting period (say, OCT-02), select INVOICE_VARIANCE where (VOID_INVOICE_FLAG = 'N') AND (SORT_PERIOD_INVOICE_DATED = '200210'). This is the basis of PERIOD_INVOICE_VARIANCE 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_SB_EXP records. Users with Organization-based access may see only those records which they are authorized to access based on the FUND_RESP_ORG.

  • Do not attempt to join this table to BALANCES or to GL_DETAIL.

Source

The AREXCF and AREXHF tables in BRIM, the Billing and Receivables Information Management system used by the Office of Research Services.

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_SB_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