BRS_DETAIL Table - Data Element Index   Tables and Data Elements   BRS Home   Data Warehouse Home

BRS_DETAIL Table

Explanation
The BRS_DETAIL table contains all dollar transactions (charges, payments, and adjustments) processed by BRS. It provides the necessary information for billing, accounting, and reporting on any BRS account.

Common Usage examples

• List and summarize all tuition charges for students in a specified term, by student division.
• Find students in a specified degree program who have not been billed anything yet in a specified term.
  
  

BATCH_DATE
BRS_TERM
SSN
SUBCODE
SUBCODE_SEQ


     

ACCOUNT_2_CNAC
ACCOUNT_2_CREF
ACCOUNT_2_FUND
ACCOUNT_2_OBJ
ACCOUNT_2_ORG
ACCOUNT_2_PROG
BATCH_DATE
BRS_TERM
PENN_ID
SSN
SUBCODE
SUBCODE_SEQ

BRS_SUBCODE

• Although the BRS system stores on SSN for a student, the load to the Warehouse populates the corresponding Penn ID, which should be used at all possible in queries and reports to preserve confidentiality of the SSN.



• The Term code structure varies between SRS and BRS. Both BRS and corresponding SRS terms are provided in the BRS_DETAIL table in the Warehouse, to facilitate joining to the Student Data Collection. Terms are mapped as follows: Since the BRS_Term is in TYY format (where the first digit represents the term, and the second two digits the calendar year), the BRS_Term values of 1 through 4 (1=Fall, 2=Spring, 3=Summer1, 4=Summer2) are translated to the appropriate letter term, and the 2-digit year is converted to a 4-digit format. For example, the SRS 2003C term, which represents Fall 2003, corresponds to the BRS_Term 103; SRS 2003A term, representing Spring 2003, correponds to BRS_Term 203; SRS 2003B term, representing Summer 2003, correponds to BRS_Terms 303 and 403, representing Summer 1 and 2 sessions, respectively)
  
  
• Subcodes can be added, changed or deleted on a daily basis at any time. If you do not receive the anticipated results, please contact Student Accounts in Student Financial Services.



• The BRS system uses 9-digit legacy financial account numbers, which are mapped to the 26-digit BEN Financials account numbers when the data is loaded to the Warehouse. If you do no receive the anticipated results, contact someone in General Accounting/Comptroller or SFS to determine if any recent changes have been made.



• Payments not always made and/or posted in the same term as the charge. Payments cannot be applied to specific charges.



• Any given subcode can have different descriptions, if being posted from a feeder (e.g., PennCard), Student Account Adjustments in SFSEASI, etc.
  
  
• BRS_DETAIL contains attributes for subcode data as it existed at the point in time that the Detail record was originally created. Since subcode attributes, such as the desciption, user code, etc., can vary over time, these attributes are not updated in BRS_DETAIL once the record has been created. Refer to the BRS_SUBCODE table for a list of current subcodes and their attributes.
There may exist records in the BRS_DETAIL table with subcodes that are no longer in existence, and therefore not available in the BRS_SUBCODE lookup table.


• Although the BRS_DETAIL table is refreshed nightly, Monday - Friday, note that feed from the BRS source system to the General Ledger only occur once a week, on Thursday nights.



• Student transactions posted to the Bursar System after the BRS Month End closing will not be fed to BEN Financials until the end of the 1st week in the new month.



• Student transactions posted to the Bursar System after the BRS Year End closing will not be fed to BEN Financials until the end of the 1st week of July each year. Activity will all be considered new FY data.



• If needing to review/report on data posted in line with the BRS monthly bills, please refer to the Annual billing schedule, which is distributed by SFS.



• To find transactions that have been sent to collections, refer to records where the BATCH_REF begins with "COL%" or the BRS_DESC text is "WRITE-OFF COLLECTIONS" (some of these may actually have a BATCH_REF beginning with "SAJ". Note that the BRS_DESC text is entered manually, and so may contains variations in spelling and/or spacing (try a query where BRS_DESC begins with "WRI%").
  
• BRS data was initially loaded into the Data Warehouse in June 2004. This data included BRS Detail, Intermediate and History files, which contained historical data for all students, as well as students who graduated in the previous few years. From that point on, all new transactions have been added on an ongoing basis. Older historical data from the BRS Inert archive files was not loaded in June 2004 due to anticipated storage and processing requirements.



• The DETAIL column indicates whether a record can be found in the current BURSAR.DETAIL file on the mainframe (Detail ='Y'). Records with a DETAIL value of 'N' indicate that the data has been purged from the mainframe BURSAR.DETAIL file during the annual BRS purge, and now resides in any of the Intermediate, History or Inert files. In order to be purged on the mainframe, all of the following must be true: 1) the PAID_APP_DATE is not null, 2) the record is fully paid and applied, i.e., AMOUNT = APP_AMOUNT, 3) the BATCH_DATE value must be up to and including the last bill date of the BRS fiscal year (the billing cycle as of date in BR00720J), 4) the record is not from the Summer2 or Fall terms of the current year, and 5) BILL_DATE is not null.
  
  
• While the information for each student in the BRS collection should be historically complete, the file does not contain students who left and whose accounts were fully paid pior to Fall 2002, since these students were purged to the Inert archive file, which was not loaded into the Warehouse. Thus, any term-based analysis by account or subcode for terms prior to Fall 2002 may have incomplete data.

• The annual purge of the BRS files is run in July or August of each year, but has no direct impact on the BRS collection in the Data Warehouse, although it should be noted that, on the night of the purge, Student Financial Services (SFS) does not run a regular update of the BRS system.

• Student Name is stored in the view of the BRS Detail table used by the BRS_Combined universe, so it is not necessary to get the Name from the Person table.

Source

• The source of the data for ongoing updates is the UPSFS.BURSAR.DETAIL file in the BRS system. The initial population of historical records (in 2004) was extracted from the UPSFS.BURSAR.INTRMED.CUM and UPSFS.BURSAR.HISTORY.CUM.

BRS_DETAIL Table - Data Element Index   Tables and Data Elements   BRS Home   Data Warehouse Home