Penn Computing

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

BRIMCASH_RECEIPTS Table

Explanation
Records for cash receipts, both unapplied and applied. There is one record per unapplied receipt. For applied receipts and credit memos, there is one record for each applied portion of the receipt or credit memo, and one record for each invoice or receipt to which the cash or credit was applied. For example, if a receipt for $100 is applied to two invoices, with $75 going to one invoice and $25 going to the other, there will be four records-one for the $75 being applied from the receipt, one for the $75 being applied to the first invoice, one for the $25 being applied from the receipt, and one for the $25 being applied to the second invoice.

Common Uses

  • Analyzing the total paid amount for an invoice, to see each application of cash or credit to the invoice
  • Analyzing the invoice amount and balance for a receipt, to see each application of cash that caused the receipt's balance to change from the invoice amount to the balance on record
Primary Key Indexed Data Elements Related Tables
RECORD_SEQ_NO
ACCT_PER_APPLIED
ACCT_PER_INVOICE_DATED
BILLING_FORMAT_CODE
FISCAL_MON_SEQ_APPLIED
FISCAL_MON_SEQ_INV_DATED
FISCAL_YR_APPLIED
FISCAL_YR_INVOICE_DATED
FUND_CODE
FUND_INVOICE_NUMBER
FUND_RESP_ORG
INTERNAL_REF_NO
MONTH_APPLIED
MONTH_INVOICE_DATED
RECEIPT_LINK
SORT_PERIOD_APPLIED
SORT_PERIOD_INVOICE_DATED
SPONSOR_CODE
BRIM_FUNDS
BRIM_MASTER

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


  • The CCATI is the cash/credit application transaction identification number. In most cases, there is one BRIMCASH_RECEIPTS record per CCATI, FUND_INVOICE_NUMBER, INTERNAL_REF_NO, and APPLIED_TO_ORIG_TRANS_ID. However, consider the case where a receipt for $100 is applied to two other receipts for the same fund, with $75 applied to 'R 1111111' and $25 applied to 'R 2222222'. There will be four records for the CCATI: (1) the $75 being applied from the receipt, (2) the $75 being applied to 'R 1111111', (3) the $25 being applied from the receipt, and (4) the $25 being applied to 'R 2222222'. The values for CCATI, FUND_INVOICE_NUMBER, INTERNAL_REF_NO, and APPLIED_TO_ORIG_TRANS_ID will be the same for records (1) and (3). Therefore, a record sequence number (RECORD_SEQ_NO) is used to uniquely identify a BRIMCASH_RECEIPTS record.
  • In most cases, cash from a receipt is applied to an invoice. However, cash from one receipt may be applied to another receipt. Credit memos can also be applied to invoices.


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


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


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

  • The CASH_RCPT_ORIG_TRANS_ID indicates the open cash receipt, or the cash receipt or credit memo that is the source of the cash or credit in a cash/credit application transaction. A record for an open (unapplied) cash receipt has a CASH_RCPT_ORIG_TRANS_ID beginning with R (for example, 'R 678'). (This matches the BRIM_ORIGINAL_TRANS_ID for the receipt in the BRIM_MASTER table.) When the receipt is applied, the records involved in the cash application transaction have a CASH_RCPT_ORIG_TRANS_ID like the one for the open receipt, but beginning with C (for example, 'C 678'). If a credit memo is applied, the records involved in the cash application transaction have a CASH_RCPT_ORIG_TRANS_ID that matches the BRIM_ORIGINAL_TRANS_ID for the credit memo record in the BRIM_MASTER table (for example, 'C 5678').


  • When selecting records based on a particular CASH_RCPT_ORIG_TRANS_ID or APPLIED_TO_ORIG_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'.


  • If the value of APPLIED_TO_ORIG_TRANS_ID is null or blank in BRIM, its value in BRIMCASH_RECEIPTS is '.'.
  • When selecting records based on a particular RECEIPT_LINK, be sure to include enough spaces in the middle of the value. For receipts and credit memos, from the first character to the last non-blank character, the value is 16 characters long (including internal blanks). For invoices, from the first character to the last non-blank character, the value is 9 characters long (including internal blanks). For example: '_RECEIPT 17763'; 'I 14569' (an invoice to which cash has been applied); '16828 16828' (a credit memo).


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


  • To report on project-to-date unapplied receipts as of a particular accounting period (say, OCT-02), select PAID_AMT where (INTERNAL_REF_NO = '_RECEIPT') AND (SORT_PERIOD_INVOICE_DATED <= '200210') AND (SORT_PERIOD_APPLIED <= '200210'). This is the basis of PJTD_UNAPPLIED_RECEIPTS in BRIM_FUNDS.


  • To report on project-to-date applied receipts as of a particular accounting period (say, OCT-02), select PAID_AMT where (INTERNAL_REF_NO <> '_RECEIPT') AND (SORT_PERIOD_APPLIED <= '200210'). This is the basis of PJTD_APPLIED_RECEIPTS in BRIM_FUNDS.


  • To report on fiscal year-to-date receipts as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select PAID_AMT where (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210'). This is the basis of FYTD_TOTAL_RECEIPTS in BRIM_FUNDS.


  • To report on fiscal year-to-date unapplied receipts as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select PAID_AMT where (INTERNAL_REF_NO = '_RECEIPT') AND (FISCAL_YR_INVOICE_DATED = '2003') AND (SORT_PERIOD_INVOICE_DATED <= '200210') AND (FISCAL_YR_APPLIED = '2003') AND (SORT_PERIOD_APPLIED <= '200210'). This is the basis of FYTD_UNAPPLIED_RECEIPTS in BRIM_FUNDS.


  • To report on fiscal year-to-date applied receipts as of a particular accounting period (say, OCT-02, which falls in fiscal year 2003), select PAID_AMT where (INTERNAL_REF_NO <> '_RECEIPT') AND (FISCAL_YR_APPLIED = '2003') AND (SORT_PERIOD_APPLIED <= '200210'). This is the basis of FYTD_APPLIED_RECEIPTS in BRIM_FUNDS.


  • To report on receipts for, say, OCT-02, select PAID_AMT where ACCT_PER_INVOICE_DATED = 'OCT-02'. This is the basis of PERIOD_TOTAL_RECEIPTS in BRIM_FUNDS.


  • To report on unapplied receipts for, say, OCT-02 (that is, to find out the amount still unapplied as of the end of the accounting period for receipts dated during the accounting period), select PAID_AMT where (INTERNAL_REF_NO = '_RECEIPT') AND (ACCT_PER_INVOICE_DATED = 'OCT-02') AND (ACCT_PER_APPLIED <= 'OCT-02'). This is the basis of PERIOD_UNAPPLIED_RECEIPTS in BRIM_FUNDS.


  • To report on applied receipts for, say, OCT-02 (that is, to find out the amount applied during the accounting period), select PAID_AMT where (INTERNAL_REF_NO <> '_RECEIPT') AND (ACCT_PER_APPLIED = 'OCT-02'). This is the basis of PERIOD_APPLIED_RECEIPTS 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 BRIMCASH_RECEIPTS 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 ARCASH and ARYCSH 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 unapplied 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 unapplied receipts were entered by ORS staff directly into BRIM. Records related to applied receipts were created by 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.

BRIMCASH_RECEIPTS 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