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.
Questions about this page? Email us at da-staff@isc.upenn.edu
|