Penn Computing

University of Pennsylvania
Penn Computing << go backback
GL_DETAIL Table - Data Element Index   Tables and Data Elements   General Ledger Home   Data Warehouse Home

GL_DETAIL Table

Explanation
Contains posted General Ledger transaction-level data, by date within an accounting period. Transactions may be slightly summarized; some have been aggregated and a total is reported, while others are listed detail by detail.

 Actuals include all journal entry transactions posted to the General Ledger via the BEN Financials online screens, internal General Ledger processes (e.g., mass allocations), feeder systems (e.g., Mail Services, service centers, ProCard, etc.), and the BEN Financials Accounts Payable and Purchasing systems. Encumbrances include entries created from the Purchasing system and Salary Management as well as manual encumbrances created online. Accounts Payable invoice encumbrances are excluded. Budget entries include only those budget journals created online.

When actual or operating budget or special budget amounts are summarized by the 26-digit account number for a closed accounting period, total amounts should equal the corresponding monthly balances for the accounts in the BALANCES table.

Common Uses
Reporting on transaction level detail from the General Ledger for all object codes.  

Primary Key Indexed Data Elements Related Tables
SEQUENCE_PK
ACCOUNTING_PERIOD
ACCOUNT_TYPE
CALENDAR_YEAR
COA_ACCOUNT
COA_CNAC
COA_CREF
COA_FUND
COA_OBJECT
COA_ORG
COA_PROGRAM
COA_RESPONSIBLE_ORG
EXTRACT_DATE
FISCAL_MONTH_SEQ
FISCAL_YEAR
INVOICE_NUMBER 
JOURNAL_BATCH_NAME 
JOURNAL_CATEGORY 
JOURNAL_SOURCE_NAME 
MONTH 
PO_NUMBER
SEQUENCE_PK
TRANSACTION_DATE 
VENDOR_NAME 
VENDOR_NUMBER
BALANCES
CENTER_REF_CODES
CNAC_CODES
FUND_CODES
OBJECT_CODES
ORG_CODES
PARENT_CNAC_CODES
PARENT_FUND_CODES
PARENT_OBJECT_CODES
PARENT_ORG_CODES
PARENT_PROGRAM_CODES
PROGRAM_CODES

Cautions

  • This table, which contains transaction-level detail for all object codes, is updated daily, to remain consistent with the Data Warehouse BALANCES, which contains Balances for all 26-digit account combinations. As with BALANCES, this table will not reflect any changes made during the day in the online system (BEN Financials) until after the following data load.
  • Please note that the BEN Financials 155 extract, on which this table was originally modelled, only includes data for NAC 0 revenue/expense transactions, as grouped by parent object codes C544 (revenue) and C545 (expense). The GL_DETAIL table, in contrast, includes transactions for all object codes and all Net Asset Classes (NACS 0,1,2).


  • This table contains rows for accounting periods back to JUL-96, up through the end of the current fiscal year. There can be rows for future periods through the end of the fiscal year for accounts that have future budget transactions.
  • Attribute and Context fields for Feeder transactions are populated only from February 7, 2007 and forward, although the other data elements for historical feeder data are available. Note also that usage of the Attribute and Context columns varies by feeder source.
  • Encumbrance amounts are for the transaction date only, and will not sum up to the Total_Encumbered amount in the BALANCES table, which represents a fiscal-year-to-date encumbrance for each account combination. In addition, Accounts Payable invoice encumbrance transactions are excluded.
  • Original Budget amounts are for the transaction date only, and the ORIGINAL_BUDGET_AMOUNT column reflects only journal entry transactions. Since Original Budget entries are typically made in BEN Financials via a direct load from Planning, rather than by journal entry, Original Budget amounts in GL_DETAIL will therefore likely not sum up to the Orig_Budget_Month amount in the BALANCES table.
  • Although actual, operating budget and special budget amounts for a given account will sum up to their corresponding Balances for a closed accounting period, it is possible (due to timing issues) that detail and balance-level totals will not match for an open period.
  • When counting invoices, be sure to count by INVOICE_ID in order to capture all invoices that meet your criteria (since the INVOICE_NUMBER may be duplicated among different vendors).
  • Some batches, such as benefits allocations, and grant revenue, are, on BEN Financials 15X reports, summarized in one line per accounting period. The GL_DETAIL table may contain multiple lines for these batches, since the data for this table is extracted weekly rather than monthly.
  • In addition, the following transactions have been summarized in some fashion for storage in this table:

  •  1. Accounts Payable actuals (Identified as Journal Source Name = 'Payables', Journal Category = 'Purchase Invoices'):
    Transactions are summarized by account code combination, purchase order number (if applicable), invoice number, transaction date (transaction creation date), and batch name. This provides the amount of the expense by PO, eliminating all of the detail distribution lines from the PO.

     2. Purchase Order encumbrances and encumbrance reversals (Identified by Journal Source Name = 'Purchasing'):
    Transactions are summarized by account code combination, PO number, transaction date (transaction creation date) and batch name.

     3. Salary Management encumbrances (Identified as Journal Category = 'SAL-MGMT' and Journal Source Name = 'PRL_ENCUMBER' or 'PRL_REVENCUMBER'):
    Transactions are summarized by the account code combination.

     4. Custom / Mass Allocation actual transactions:
    Transactions are summarized by the account code combination. Transactions are identified as:
    Employee Benefits (EB) Mass Allocation (Journal Source Name = 'MassAllocation', Journal Category = 'Allocation', object code = '5190' or '5191')
    Grant & Contract Revenue (Journal Source Name = 'GRANTREV', Journal Category = 'Revenue')
    Grant & Contract Overhead (Journal Source Name = 'GRANTOH', Journal Category = 'Allocation')

     5. All other transactions:
    Transactions are stored at the detail level. In other words, the transactions are identified by the account code combination, balance type, batch name, transaction date (transaction creation date) and line level.
     

  • It is often easiest to use ACCOUNTING_PERIOD when setting the record selection conditions for queries. If, instead, MONTH or FISCAL_MONTH_SEQ are used in the record selection conditions for a query, FISCAL_YEAR ought to be used as well.
  • When reporting on organizations, be sure to select the data element that meets your needs. COA_ORG is the organization segment of the COA_ACCOUNT (the 26-position Accounting Flexfield). COA_RESPONSIBLE_ORG is the code for the organization responsible for managing the fund.
GL_DETAIL Table - Data Element Index   Tables and Data Elements   General Ledger 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