General Ledger Data Collection

The General Ledger collection is refreshed nightly, Monday through Saturday, following the completion of BEN Financials batch processing around midnight (updates should usually be completed by start of business).

The daily refresh affects all tables in the collection (Balances, Detail and all reference tables) the Overhead Rates (F&A Rate and ICR School Portion) columns in the Balances table, which are refreshed only on Sunday mornings. 

In months where the accounting period close falls during the business week, the General Ledger collection load to the Data Warehouse will occur during the same morning as the financial system month-end processing. At these times, the entire collection (including Summary Balances but not Overhead Rates) will be refreshed, and the collection will be unavailable for querying until the refresh is complete, which is usually by early afternoon. If BEN Financials month-end falls on a Saturday, the extract and update will begin after the Comptroller's Office declares the month closed. In that case any changes made up to that point will be included.

Source of the Data
The data in the data warehouse that is accessible via the Business Objects FINQUERY, FINSUMQ, and FINQO2N universes comes from the BEN Financials General Ledger system. Balances, General Ledger transactional data and reference tables are included. ISC Networking Billing Detail comes from the ISC Billing system, which feeds transactions to BEN Financials. Similarly, ISC Telecommunications data comes from the Telecommunications billing system, and Mainframe Charges are extracted from the ISC mainframe system.

Time Span Covered
Monthly snapshots of general ledger data are stored. The snapshots go as far back as the accounting periods of June of 1996 (JUN-96) and the fiscal year 1996 adjustment perioid (ADJ-96), and continue from that point, through the current open fiscal year (although actuals may not be available, budget data for the entire year is available). ISC Networking Billing Detail is available from August 2004 and on. Telecomunications data is available from fiscal year 2006 and on; Mainframe chargeback data is available from fiscal year 2007 and on.

The General Ledger Data Collection is described in the FINQUERY, FINQUERY Template Universe, FINSUMQ, and FINQO2N diagrams. Each diagram illustrates the architecture of a specific Business Objects universe and identifies the join criteria between tables.

• The FINQUERY diagram provides access to the General Ledger balances. In most instances, you will use this diagram when querying the General Ledger Data Collection.
• The FINQUERY Template Universe diagram depicts the assorted template tables, which can be used to roll up balances or mimic General Ledger reports, joined to the FINQUERY universe.
• The FINSUMQ diagram provides access to summary balances.
• The FINQO2N diagram helps you to translate financial query records into terms that were used before July 1 1996, when the present General Ledger accounting structure was implemented. For example, translate object codes into subject codes.

In addition, users of the General Ledger data collection are automatically authorized to view the following:

• The Express Mail diagram identifies the tables that make up the Express Mail universe and their join criteria. This diagram should be printed using landscape orientation.
• The ISC Billing Detail diagram provides access to ISC Networking Billing detail data.
• The ISC Telecommunications Detail diagram provides access to ISC Telecommunications invoice and charge data.
• The Travel and Expense Management diagram provides access to Concur Expense detail data.

The BusinessObjects repository contains reports which users can use to retrieve data to which they have access. The reports available vary by data collection and business use, as listed below. You will only be able to view or use reports in the public folders that you have access to. Documents should be renamed and saved to your own "My Favorites" folder on the portal if you need to edit them using Webi. Documents can only be edited in Webi when placed in your "My Favorites" folder.

General Use

CForm Transactions Detail presents information from the General Ledger Journal detail data available in the FINQUERY universe, and can be used to monitor C-Form payment journals (petty cash transactions and payments for items purchased by purchase order are excluded). Access to the General Ledger data in the Warehouse is required to run this report.

FINQRCM - Budget and Actuals displays a crosstab of year to date budget balances and actuals by month for RCM Categories for a given Center and Fund. It makes use of a union, which allows users to retrieve year to date budget balances in one column, along with actuals by month as the columns in the crosstab. It uses the RCM OBJECT TREE table in the Data Warehouse to mimic object groupings used in BEN Reports and BEN Financials reports.

FINQRCM - Previous comparison uses a simple crosstab, along with the BusinessObjects Previous function and categories from RCM_OBJECT_TREE to present a crosstab of RCM categories compared to previous periods. The three tabs of the report show the progression from raw data, to a simple crosstab (columns sorted by fiscal year and fiscal month, rows by RCM summary and category sequences), to the final crosstab including a column with variable comparing the data to the previous period. Note that the Previous function allows for comparison to the previous row (in a standard table format), or the previous column (in a crosstab format). The comparison could be between anything that occurs in consecutive rows or columns - this query just uses it to compare accounting periods year to year.

FINQUERY - Encumbrances by Org shows encumbrances by org, fund, object and PO number, for a given fiscal year and org(s).

FINQUERY - Year over year comparison is actually comprised of two separate queries (Current Period and Comparison Period), linked by the Balances COA_Account segment to allow comparison of balances between two periods. The queries are each represented separately on the appropriately named tabs, and, once linked, the combined results are presented on the Combined tab. Data in this report can be verified using BEN Financials online account inquiry, or comparing to BEN Financials 10X or 15X reports.

FINQUERY Template Universe - RCM 210 intended to allow users to mimic the BEN Financials RCM 210 report using the Data Warehouse BALANCES and GL_REPORT_TEMPLATE tables. Two queries (Financial Data and RCM Labels) are linked together (on the Sequence column), to join balances with the full skeleton of the RCM report. Therefore, the file consists of three tabs - the first (Financial Data) shows only the template lines for which the query retrieves balances. The second tab (RCM Labels) shows the skeleton of the RCM template. The final tab (RCM report) shows the entire RCM template, with balances populated where available. The Financial Data query makes use of a union, to allow users to retrieve all rows in the template - those represented by a parent object, and those represented by COA objects which do not roll directly to a K-parent. For more on the template table, refer to gl_report_template.t.html. Data in this report can be verified using the BEN Financials 210 report.

Market Value: Displays current balances (available cash, book value, expense) for endowment funds as of a particular accounting period. In addition to retrieving the actual balances from the Warehouse, the report also calculates Expected Income for the fund based on an input AIF rate.

Grants Management

Fund Summary (fundsummary.rep) gives a summary of the entire grant fund similar to the BEN Financials 115 report (listing key attributes) and also lists totals by object code for Budget, Month, Fiscal Year, PJTD Actual Expenditures, Encumbrances and BBA. A separate tab is provided to break down expenditures further by ORG/CREF. Another tab is used to project what the unobligated balance as a percentage of the current year budget will be at the end of the current budget year. The calculation is based on the average expenditures per day. This report is useful for administrators preparing NIH noncompeting continuations since NIH will want to know if an unobligated balance greater than 25% exists. If the projection shows a balance greater than 25% of the current year's budget, the administrator needs to investigate further before answering "no" to the question about whether an unobligated balance greater than 25% exists. This is the quickest way to look at any fund in its entirety.**

Fund Detail (Fund Detail.rep) Provides accounting period summaries of all expenditures and payroll for a given fund and any period of time. Separate tabs provided for: Accounting Period Summary, Payroll Period Summary, Payroll by Object Code, Payroll by Person, Exp Detail by Obj, Detail by ORG, Detail by CREF, Detail by Acct Period, and Detail by PO/User. [NOTE: This report is comprised of two separate queries, one of which queries the Salary Management data collection. If you do not have access to Salary Management data in the Warehouse, you will receive errors when trying to view the "Payroll" tabs of the report.]*

ORG Summary (ORG Summary.rep) gives a summary of all grant funds for which the ORG is the RESP ORG. Results, listed by fund, include fund description, PI name, account end date, and balances for PBIL, Operating Budget, Sum Grant Expenditures, Surplus/(Deficit), Revenue, Receivable, and Cash Received. Separate tabs are provided to identify grants sorted by fund, PI, account end date, Receivables only, Overdrafts only, funds due to freeze in the next 90 days, analysis of expired funds, late or due FSRs, funds with illegal object code charges, and a comparison of budgeted payroll to actual payments for each fund. This report can be used to identify a variety of potential problems (i.e.accounts with large overdrafts, receivables, revenue recognition, etc.) as well as provide the user with a quick summary of all his/her organization's grant funds. Additional queries have also been developed to list all funds where another ORG has charged funds of the RESP ORG (otherorgschgs.rep) and where the ORG has charged funds not under its RESP ORG (subbudgets.rep); when running those reports, the user should specify his/her own org at both prompts. *

ORG Wayward Summary (ORG Wayward Summary.rep) is the first of two queries required to identify wayward transactions. The other query is ORG Wayward Detail. ORG Wayward Summary is a query that is used to alert the user of the ORG/Fund wayward transactions that have occurred and require attention.ORG Wayward Detail (ORG Wayward Detail.rep) provides the line item detail for the ORG/Fund combination shown in the Org Wayward Summary Query.*

Detail Reports are used to provide transaction detail for any range of accounting periods after FY2000 for a given object code (DETAIL OBJECT RPT.rep ) or for all objects (DETAIL RPT.Rep) for a particular fund. Separate queries are used for Operating (DETAIL OBJECT RPT(Operating Bdgt).rep ) and Special Budget (DETAIL OBJECT RPT(Special Bdgt).rep), Cash (DETAILOBJRPTCASH.rep ) and Subcontracts (DETAILsubcontract.rep ) entries. Also available are reports by PI (DETAIL RPT (All grants of PI).rep) or RespOrg(DETAIL RPT (All grants of RESP ORG).rep), which are ideal for running monthly detail for all funds for a PI or RespOrg (since these queries bring back data for several funds we recommend that you run them for periods less than 3 months).*

Encumbrances (Encumbrances(RESP ORG).rep) identifies all currently open encumbrances for your Resp Org. Separate tabs are provided for All Outstanding, Outstanding on Unfrozen Funds, Outstanding on Frozen Funds, and Outstanding by PI. All Encumbrances and PO's need to be closed on frozen funds because a fund can not be disabled unless there are no encumbrances remaining on the fund. Encumbrances(FUND).rep provides all current outstanding encumbrances for one particular fund.*

717 Report (717 Org Report.rep) is tailored after the BEN Financials 717 report used by Research Services to identify funds that have been reported (or have no reporting requirement), but have not yet been disabled due to varying reasons. ***

4822 Writeoffs (4822writeoffs.rep) is used to identify all grant funds (by ORG) for which there has been a credit entry to object code 4822 during the current fiscal year (both MTD and FYTD totals are provided). This report indicates funds where there have been sharing entries or a write off of the direct cost overdraft (separate Object Codes are not used to distinguish between the two). 4822 writeoffs are to departmental unrestricted funds. *

Funds Reported includes two reports: Rptsissued.rep lists all funds (by ORG) that have been reported on by Research Services, with the amount reported. FSRDI.rep lists the current differences between actual Direct and Indirect amounts and what was reported by Research Services. *

Funds Disabled (disablebyorg.rep) lists all funds (by ORG) that have been disabled by Research Services. This report can be very helpful, as there is no option in BEN Financials currently to print only enabled funds; BEN Financials will generate reports for all active grants for each ORG. *

PBIL Violate (PBIL Violate.rep) can be used to identify all funds for which PBIL does not equal PBUD. Accounts where PBUD has a value and PBIL doesn't usually indicates the existence of an advance account. *

Proposal Reports (proposal.rep) can be used to identify all proposals processed for any given period of time, and whether they were awarded or are pending. The report can also be used to identify all advance account proposals processed. *
Note: you must have access to the Sponsored Projects collection in the Data Warehouse to retrieve results for this report.

Grant Monitor (GrantMonitor.rep) is a series of reports that alert the user of funds which may need attention, ie., overdrafts, discrepancies between PBIL/PBUD and Budget, unallowable charges, NIH noncompeting continuations which are due, potential problems with clinical trial cash, clinical trials which may have residual balances, receivables, funds where AISs may need to be updated because the sponsor sends out one notice for multiyear awards, or funds sponsored by the federal government which may have problems with cash draw-down. Click here to view more detailed documentation of each report tab.*

*Reports contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine.
**Report contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine; original development by Jason Molli, of the Office of Research Services.
***Report contributed by Marilyn Becker and Rich Snyder, of the Office of Research Support Services in the School of Medicine; original development by Nick Majuri, in the School of Medicine.
****Report contributed by Jenny Sherwood, Department of Medicine, Division of Rheumatology; original development by Rich Snyder, of the Office of Research Support Services in the School of Medicine.

Self-Serve, Online Training Options

Workday Learning: Introduction to the General Ledger Data Collection and Using the FINQUERY Universe at Penn (optional)

This course will orient you to GL data, its organization, and refresh cycle as well as help you to use SAP BusinessObjects (InfoView & Webi) to

• Modify the available corporate (or "canned") reports by correctly using filters and prompts
• Create your own queries using variables, filters, etc. to control the data
• Select the correct balances or journal detail tables (classes) and objects (data elements) to include in your own specific queries

After taking this course, you will also know where to turn for answers and additional help with the General Ledger Data Collection.

Website: General Ledger Tutorial (optional)

The lessons in this tutorial are arranged sequentially. However, if you have experience in writing and executing queries, select the lessons you need to know more about. If you do not have experience in writing and executing queries, read the lessons in the order in which they are listed. To help retain what you've learned, complete the activities for each lesson.

See how to build several queries with GL data from simple business questions.

• How can I review our entertainment costs for last year?
• I need to run monthly reports on how we are doing with current expenses. Are we within budget or not?
• What ProCard transactions have been posted this month and the previous two months from my ORG and to what accounts have they been posted?
• How many funds in our school are currently frozen and what are the current encumbrances on those funds?
• How do our mail costs this year compare to last year?

Office Hours

Occasionally classroom activities and BusinessObjects 'office hours' will be announced and offered. It is recommended to take advantage of the online training opportunities since they are always available at your convenience.

In general, individuals must have BEN Financials General Ledger inquiry/reporting functions before they can be granted access to the General Ledger collection in the Data Warehouse. Users must supply their BEN Financials ID in the space provided on the General Ledger Data Access eForm, and obtain supervisor and school access administrator (or senior business officer) approval. BEN Financials users will then be granted access to view data in the Warehouse for the same organization(s) for they can perform account inquiry or reporting functions in BEN Financials.

Those individuals who do not already have access to the source system (BEN Financials), but who need ad hoc access to the General Ledger collection in the Data Warehouse, must complete BEN Financials Chart of Accounts training along with the General Ledger Data Access eForm. They should indicate on the form the level of access they need by supplying the appropriate ORG or Parent ORG code(s). Chart of Accounts training schedules and registration information are available at http://www.finance.upenn.edu/ftd/).

Releasing Data Outside the University 
The only two offices that may disclose General Ledger data outside the University are the Office of the Comptroller and Institutional Research. If you do not work for those offices and you receive a request for data to be sent outside the University, refer the requester to the Office of the Comptroller.

Releasing Data Within the University
Within the University, General Ledger data may be disclosed only if it is needed to do the business of the University, and only to those who need to know the information in order to do their jobs. If you are not sure whether to fulfill a request for General Ledger Data, call the Office of the Comptroller.

Query Results
If you save your query results in Excel, pdf, or any other local file type, you must see to it that any sensitive data stored on your peronal computer is safeguarded through physical security, access control software, or encryption. When a computer is left signed to an account, it is easy for someone to gain unauthorized access. Either sign off from your account before you leave your computer or restrict access by some other means (physical security or access control software).

For more information on security and privacy, contact the Office of Information Security.

General Ledger data is complex, and a listserv and adhoc meetings are available to help you query the Warehouse and retrieve General Ledger data efficiently and effectively.

Whenever possible, canned queries and reports are provided to give you standardized perspectives on Warehouse data. Before creating your own query, check to see if a canned query or report already exists for the information you want.

Subscription to the listserv. When you are granted access to the General Ledger data in the Warehouse, a subscription is automatically set up for you to participate in an E-mail listserv called FINQUERY-WH@LISTS.UPENN.EDU. (Those with subscriptions to the list are called list members. If any member sends an E-mail note to the list, every member of the list receives a copy.)

When you have a question. If you have a question about the General Ledger data, please send E-mail to the list -- and if you have the answer to a list members question, please E-mail your reply to the list so all the members can benefit from the discussion.

The list is also the place to send E-mail if you notice that some documentation needs updating. For documentation published on the Web, please specify the URL for the documentation that needs to be updated. For BusinessObjects documentation (help for Objects), please specify the Universe, Class, and Object.

Notification about general and special meetings. From time to time, notes are sent to the list to invite list members to general meetings to discuss the General Ledger data in the Warehouse, or to special interest group meetings to discuss specific issues, such as grant year reporting.

• Past Financial User Group agendas