BRIM_FUNDS Table - Data Element Index   Tables and Data Elements   BRIM Home   Data Warehouse Home

BRIM_FUNDS Table

Explanation
A snapshot of the status of each fund as of the end of the accounting period snapshot. (The funds are sponsored project funds where the sponsors either send payments as they are billed for project expenses, or send payments based on a schedule.) BRIM_FUNDS includes information such as the fund sponsor, maximum amount that may be billed (PBIL), the ORS accountant who monitors the fund, and so on. It also includes summaries of the period, fiscal year-to-date, and project-to-date amounts for expenses (billed and unbilled), invoices (automatic payment, expense-based, and total), variances (calculated and per BRIM), and receipts (unapplied, applied, and total). There is one record per fund per accounting period snapshot.

Common Uses

• Checking the current status of a fund, or its status as of the end of a prior accounting period.
• Viewing fund attributes such as the responsible org. and school; the Principal Investigator's name and the project title; the sponsor and the sponsor reference ID; the account start and end dates, billing format and frequency, and PBIL; the ORS accountant name, desk number, and phone number; and the sequence number and date of the latest ("last") invoice for the fund.
• Reporting on summarized expense, invoice, variance, receipt, or balance figures for a fund or sponsor.
• Reconciling BRIM data with BEN Financials (General Ledger) data, for unbilled expenses, invoiced amounts, variances, and receipts.
  
  

ACCT_PERIOD_SNAPSHOT
FUND_CODE

ACCT_DESK_NUMBER
ACCT_PERIOD_SNAPSHOT
BILL_TO_CODE
BILLING_FORMAT_CODE
BILLING_FREQUENCY_CODE
CALENDAR_YR_SNAPSHOT
COMMENT_ID
FISCAL_MON_SEQ_SNAPSHOT
FISCAL_YR_SNAPSHOT
FUND_CODE
FUND_RESP_ORG
MONTH_SNAPSHOT
SCHOOL
SORT_PERIOD_SNAPSHOT
SPONSOR_CODE
SPONSOR_REF_ID

BRIM_MASTER
BRIMAUTOPAY_PROFILES
BRIMCASH_RECEIPTS
BRIMFUND_OBJ_EXP
BRIMFUND_SB_EXP

• The BRIM_FUNDS table is refreshed nightly. Records for the snapshot for the open accounting period are replaced nightly until the period is closed. Once the accounting period is closed, the data for the snapshot of that accounting period remains unchanged.




• Because this table contains snapshots, to avoid double-counting, always select records based on one specified accounting period. Records should be selected based on one of the following:

  • ACCT_PERIOD_SNAPSHOT (such as OCT-02)
  • SORT_PERIOD_SNAPSHOT (such as 200210)
  • MONTH_SNAPSHOT and CALENDAR_YEAR_SNAPSHOT (such as 10 and 2002, respectively)
  • FISCAL_MON_SEQ_SNAPSHOT and FISCAL_YR_SNAPSHOT (such as 4 and 2003, respectively)
  
  
  

• BRIM is not run based on accounting periods. Therefore, there is no snapshot for 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 expense based funds, select records where BILLING_FORMAT_CODE <= '06'. To report on automatic payment funds, select records where BILLING_FORMAT_CODE is '07' or '08'.

• Expense-based invoices (and, therefore, actual, billed, and variance amounts) may exist for automatic payment funds.

• BRIM_FUNDS includes information on unbilled and billed actual amounts, which 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.

• LAST_PONUM is the sequence number of the invoice (expense-based or automatic payment) last created for the FUND_CODE, as of the BRIM_EXTRACT_DATE. The value will be 0 if the fund has not yet been invoiced. Though leading zeros are used in the sequence number portion of the FUND_INVOICE_NUMBER in the BRIMAUTOPAY_PROFILES, BRIM_MASTER, BRIMFUND_SB_EXP, and BRIMCASH_RECEIPTS tables, LAST_PONUM does not include leading zeros.

• Users who re-calculate summarized values for a closed accounting period may find that the re-calculated value does not match what is stored in BRIM_FUNDS. The likely reason for the discrepancy is that an invoice has been voided since the end of the closed accounting period. Summarized values that may have discrepancies:

  • BRIM_FYTD_INVOICE_VARIANCE
  • BRIM_PERIOD_INVOICE_VARIANCE
  • BRIM_PJTD_INVOICE_VARIANCE
  • FYTD_ACTUAL_AMT
  • FYTD_UNBILLED_ACTUAL_EXP
  • PERIOD_ACTUAL_AMT
  • PERIOD_UNBILLED_ACTUAL_EXP
  • PJTD_ACTUAL_AMT
  • PJTD_UNBILLED_ACTUAL_EXP




• The summarized BRIM variance amounts are calculated based on the BRIMFUND_SB_EXP table rather than on the BRIM_MASTER table because, when an invoice is voided, the total variance amount is not backed out in BRIM_MASTER.

• The value of CALC_PJTD_INVOICE_VARIANCE may differ from the value of BRIM_PJTD_INVOICE_VARIANCE. Legacy (pre-BRIM) invoices have an invoice amount, but have 0 for both the actual amount and of the variance. Legacy invoices have an internal reference number of 1117 or lower.

• The COMMENT_ID identifies a comment associated with the fund. However, a reference table for these comment codes is not yet available in the Data Warehouse.

• The BILL_TO_CODE identifies the default mailing address used for invoices for the fund. However, a reference table for the fund billing addresses is not yet available in the Data Warehouse.

• Use caution when selecting records based on PROJECT_TITLE.

  • The value is the title of the sponsored project as typed into BRIM by ORS staff. Most values are in mixed case, but some values are in upper case. A double quote mark (‘”’) may or may not be the first character, and it may or may not be the last character.
  • PROJECT_TITLE is populated if the value for ACCT_PER_SNAPSHOT is ‘APR-06’ or later. Records for earlier snapshots have a null value for PROJECT_TITLE.



• 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 BRIM_FUNDS 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 including BRIM_FUNDS and BALANCES data in the same report. Be sure to SUM all amounts. (You may sum by sponsor or by fund.) Because there is more than one record in BALANCES per fund, if you do not SUM amounts by fund, the BRIM_FUNDS amounts will be duplicated for each of the BALANCES records with the same fund. This is true if you SUM amounts by any other segment of the COA_ACCOUNT besides the fund. For Business Objects users, the BRIM Universe handles the SUMming, but the users must be sure that, if any COA_ACCOUNT segment is included as a Result Object, it's the fund.

• Use caution when displaying BRIM_FUNDS and GL_DETAIL data in the same report. (It is better to use BALANCES instead of GL_DETAIL where possible, because GL_DETAIL has many records per fund, per Object, per accounting period.) 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.

Source

The ARINVT table 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 SBTFUND.txt and SBTASSET.txt extracts; see glsq2111b.sql and glsq2112f.sql). Some data for the BRIM_FUNDS records was entered by ORS staff directly into BRIM; other data is summarized based on other BRIM tables in the Data Warehouse.

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.

BRIM_FUNDS Table - Data Element Index   Tables and Data Elements   BRIM Home   Data Warehouse Home