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

BRIM_MASTER Table

Explanation
A snapshot of the status of each invoice, cash receipt, and credit memo as of the end of the accounting period snapshot. Includes information on the balance for each invoice, cash receipt, and credit memo, and the date it was last involved in a cash or credit application. There is usually one record per invoice (or cash receipt or credit memo) per accounting period snapshot. (For voided invoices, there are two records.)

Common Uses

• Reporting the current status of an invoice, cash receipt, or credit memo, or its status as of the end of a prior accounting period.
• Checking the receivable amount (balance) for an invoice, or the date cash was last applied to it.
• Checking the unapplied cash or credit (balance) for a cash receipt or credit memo, or the date that it was last involved in a cash or credit application.
• Reporting on expense-based invoices, or on automatic payment invoices.
• Analyzing the total invoiced, or the total receivables (balance), for a fund or sponsor.
  

ACCT_PER_SNAPSHOT
BRIM_ORIGINAL_TRANS_ID
MAINT

ACCOUNTANT_NAME
ACCT_DESK_NUMBER
ACCT_PER_APPLIED
ACCT_PER_INVOICE_DATED
ACCT_PER_SNAPSHOT
AGE
AGE_RANGE
BILLING_FORMAT_CODE
BILLING_FREQUENCY_CODE
BRIM_ORIGINAL_TRANS_ID
CALENDAR_YEAR_SNAPSHOT
CALENDAR_YR_APPLIED
CALENDAR_YR_INVOICE_DATED
COMMENT_ID
FISCAL_MON_SEQ_APPLIED
FISCAL_MON_SEQ_INV_DATED
FISCAL_MON_SEQ_SNAPSHOT
FISCAL_YR_APPLIED
FISCAL_YR_INVOICE_DATED
FISCAL_YR_SNAPSHOT
FUND_CODE
FUND_INVOICE_NUMBER
FUND_RESP_ORG
INTERNAL_REF_NUMBER
MONTH_APPLIED
MONTH_INVOICE_DATED
MONTH_SNAPSHOT
RECEIPT_LINK
SCHOOL
SORT_PERIOD_APPLIED
SORT_PERIOD_INVOICE_DATED
SORT_PERIOD_SNAPSHOT
SPONSOR_CODE

BRIM_FUNDS
BRIMCASH_RECEIPTS
BRIMFUND_OBJ_EXP
BRIMFUND_SB_EXP

• The BRIM_MASTER 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_PER_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)



• There is usually, but not always, one record per BRIM_ORIGINAL_TRANS_ID and ACCT_PER_SNAPSHOT. Because there will be two such records for a voided invoice (one for the original invoice, and one backing out the invoice amount), MAINT is also included in the primary key.

• Because MAINT is part of the primary key, which uniquely identifies a BRIM_MASTER record, its value cannot be null. If its value is null or blank in BRIM, its value in BRIM_MASTER is '.'. (The value is usually '.', but will be 'A' for one of the records for a voided invoice.)

• BRIM does not store the date that an invoice was voided. However, by comparing the records for an invoice for various ACCT_PER_SNAPSHOTs, one can see the last accounting period when the invoice was not voided, and the first accounting period when it was voided.

• For records with BRIM_HISTORY_STATUS = ‘C’, the data for VOID_INVOICE_FLAG is reliable only if the snapshot is for October, 2005 or for a later accounting period. (For example, ACCT_PER_SNAPSHOT= 'OCT-05'.) For BRIM_MASTER records with BRIM_HISTORY_STATUS = ‘H’, the data for VOID_INVOICE_FLAG is reliable for all snapshots.

• BRIM_HISTORY_STATUS indicates whether the record for the invoice, cash receipt, or credit memo existed in the BRIM history tables (ARYMST and ARYTRN) as of the end of the accounting period snapshot. If BRIM_HISTORY_STATUS is 'H' and the balance is not 0, the record existed in the current BRIM tables (ARMAST and ARTRAN) as well as in the history tables when its snapshot was taken for BRIM_MASTER. In such cases, BRIM_MASTER stores the record as it was in the current tables. (The records may be different-for example, cash may have been applied to an invoice between the time the record was copied to the history tables and the time its snapshot was taken for BRIM_MASTER.)

• All receipts have '_RECEIPT' as their INTERNAL_REF_NO. To distinguish one receipt from another, use the BRIM_ORIGINAL_TRANS_ID.

• If the INTERNAL_REF_NO is not '_RECEIPT', the record is usually for an invoice. However, if INTERNAL_REF_NO is not '_RECEIPT' and RECORD_TYPE is 'C', the record is for a credit memo.

• For an invoice record, INVOICE_DATE is the date of the invoice. However, for a cash receipt, it is the date of the cash receipt, and for a credit memo, it is the date of the credit memo.

• For an invoice record, INVOICE_TOTAL is the total amount of the invoice. However, for a cash receipt, it is the total amount of the cash receipt, and for a credit memo, it is the total amount of the credit memo.

• For an invoice record, PAID_AMT is the total amount of cash or credit applied to the invoice. However, for a cash receipt or credit memo, it is the same as the balance for the cash receipt or credit memo-the amount unapplied.

• If a receipt is entered in BRIM, and is immediately applied in full, there will be no record for the receipt in BRIM_MASTER or in BRIMCASH_RECEIPTS. (BRIMCASH_RECEIPTS will contain records only for the invoices (or other receipts) to which the receipt was applied.)

• Cash receipts recorded in the General Ledger detail table in BEN Financials are extracted and loaded into BRIM. However, the extract file does not include the accounting period to which the receipt was posted. The ACCT_PER_INVOICE_DATED is based on the invoice date (the date of the receipt).

• 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.

• BRIM is not run based on accounting periods. Therefore, there are no records for the ADJ period, and there is no snapshot for the ADJ period.

• When selecting records based on a particular BRIM_ORIGINAL_TRANS_ID, remember that all 9 characters are used. Be sure to include enough spaces between the first letter and the number so that the specified value is 9 characters long. For example: 'I 14569'.

• To report on expense based invoices, select records where BILLING_FORMAT_CODE <= '06'. To report on automatic payment invoices, select records where BILLING_FORMAT_CODE is '07' or '08'.

• AGE is the age of the invoice, cash receipt, or credit memo, in days. Its value is determined by subtracting the INVOICE_DATE from the BRIM_EXTRACT_DATE. However, for voided invoices, for invoices paid in full, and for cash receipts and credit memos applied in full, AGE is 0.

• To report on project-to-date receivables as of a particular accounting period (say, OCT-02), select BALANCE where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of PJTD_RECEIVABLES in BRIM_FUNDS.

• To report on fiscal year to date receivables-receivables as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003) for invoices dated from the beginning of the fiscal year through the accounting period-select BALANCE where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of FYTD_RECEIVABLES in BRIM_FUNDS.

• To report on project-to-date invoices (regardless of invoice type) as of a particular accounting period (say, OCT-02), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of PJTD_TOTAL_INVOICED in BRIM_FUNDS.

• To report on project-to-date automatic payment invoices as of a particular accounting period (say, OCT-02), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (BILLING_FORMAT_CODE is '07' OR '08') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of PJTD_AUTOPAY_INVOICED in BRIM_FUNDS.

• To report on project-to-date expense-based invoices as of a particular accounting period (say, OCT-02), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (BILLING_FORMAT_CODE <= '06') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of PJTD_EXPENSE_BASED_INVOICED in BRIM_FUNDS.

• To report on fiscal year-to-date invoices (regardless of invoice type) as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of FYTD_TOTAL_INVOICED in BRIM_FUNDS.

• To report on fiscal year-to-date automatic payment invoices as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (BILLING_FORMAT_CODE is '07' OR '08') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of FYTD_AUTOPAY_INVOICED in BRIM_FUNDS.

• To report on fiscal year-to-date expense-based invoices as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (BILLING_FORMAT_CODE <= '06') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of FYTD_EXPENSE_BASED_INVOICED in BRIM_FUNDS.

• To report on invoices (regardless of invoice type) dated during the accounting period snapshot (say, OCT-02), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (SORT_PERIOD_INVOICE_DATED = '200210'). This is the basis of PERIOD_TOTAL_INVOICED in BRIM_FUNDS.

• To report on automatic payment invoices dated during the accounting period snapshot (say, OCT-02), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (BILLING_FORMAT_CODE is '07' OR '08') AND (SORT_PERIOD_INVOICE_DATED = '200210'). This is the basis of PERIOD_AUTOPAY_INVOICED in BRIM_FUNDS.

• To report on expense-based invoices dated during the accounting period snapshot (say, OCT-02), select INVOICE_TOTAL where (ACCT_PER_SNAPSHOT = 'OCT-02') AND (INTERNAL_REF_NO <> '_RECEIPT') AND (BILLING_FORMAT_CODE <= '06') AND (SORT_PERIOD_INVOICE_DATED = '200210'). This is the basis of PERIOD_EXPENSE_BASED_INVOICED in BRIM_FUNDS.

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

• The CUSTOM_INVOICE_ADDR_FLAG identifies whether a customized mailing address was used for the invoice. However, reference tables for the invoice addresses and fund billing addresses are not yet available in the Data Warehouse.

• If there is more than one expense-based invoice for a fund, whose expense period (from the EXPENSE_START_DATE through the EXPENSE_END_DATE) includes the date of a particular actual expense, only the first invoice created for the expense period will include that actual expense.

• The final expense-based invoice displays the beginning and end dates of the expense period covered by that invoice. For final invoices posted during or before APR-04, these are the EXPENSE_START_DATE and EXPENSE_END_DATE. For final invoices posted during or after MAY-04, these are the DISPLAY_EXP_START_DTE and DISPLAY_EXP_END_DTE. Note: the data for DISPLAY_EXP_START_DTE and DISPLAY_EXP_END_DTE is reliable only if the snapshot is for February, 2006 or for a later accounting period. (For example, ACCT_PER_SNAPSHOT= 'FEB-06'.)

• Use caution when selecting records based on PROJECT_TITLE. The value is the title as typed into BRIM by ORS staff. Some values are in upper case, while others are in mixed case. Also note that, due to a system error, records created prior to June 15, 2002 may have a PROJECT_TITLE whose value is actually the SPONSOR_REF_ID.

• 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.



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

Source

The ARMAST, ARTRAN, ARYMST, and ARYTRN tables in BRIM, the Billing and Receivables Information Management system used by the Office of Research Services. The ultimate source of most of the records for receipts, and of some of the data in those records, is the General Ledger in BEN Financials (via the SBTASSET.txt and SBTFUND.txt extracts; see glsq2112f.sql and glsq2111b.sql). Some records for receipts were entered by ORS staff directly into BRIM. Records for invoices and credit memos were entered by ORS staff directly into BRIM.

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