GL_REPORT_TEMPLATE Table - Data Element Index   Tables and Data Elements   General Ledger Home   Data Warehouse Home

GL_REPORT_TEMPLATE Table

Explanation
Report Templates are used in many of the standard BEN Financials General Ledger reports to provide consistency across reports with respect to row labels and object code groupings, and to reduce the amount of hardcoded special logic used in individual reports. Template information is also used to support the drill-down functionality in the custom Revenue/Expense Inquiry Form.

Most of the major BEN Financials General Ledger reports are structured according to the Responsibility Center Management (RCM) model, which documents how the University aggregates funds and object codes in budgets and financial reports, and how operating performance and changes in financial position are measured. The RCM templates, which form the structure of the BEN Financials reports, are stored in the Data Warehouse in the GL_REPORT_TEMPLATE table, which is updated weekly from the table of the same name that drives the BEN Financials reports. This table also includes templates for other reports which do not use the RCM groupings. (Please refer to the Office of the Comptroller website for more information about Responsibility Center Management, including specifics about fund code and object code aggregations which apply to the RCM templates.)

Common Uses
To retrieve BALANCES data in a form to match the BEN Financials 21X report series, or to view logic for other BEN Financials reports.

none

HREF

HREF_TYPE

TEMPLATE_NAME

BALANCES

OBJECT_CODES

PARENT_OBJECT_CODES

RCM_OBJECT_TREE

Cautions

• Users wishing to run reports using an abbreviated hierarchy should refer to the RCM_OBJECT_TREE table, which is derived from the GL_REPORT_TEMPLATE table. Users wishing to retrieve data from the Warehouse in the full RCM form should consult the following:

  The COA_OBJECT code segment of the 26-digit account rolls up to K-parent object groupings, which are reflected in the RCM template H_REF column (GL_REPORT_TEMPLATE.H_REF). The PARENT_OBJECT_CODES table provides the parent/child relationship necessary to group COA_OBJECTS properly according to the RCM templates. K-parents serve as both parents and children, depending upon the subtotal level in the templates. Although this makes retrieving the total lines themselves easier, it presents complications when users wish to view balances for both COA Objects and Parent Objects in the same report. Very specific logic is required to properly retrieve the levels of grouping depicted in the templates, as some COA_OBJECTS are not immediately grouped into parents except at subtotal levels.

  To support this logic, GL_REPORT_TEMPLATE is represented in the FINQUERY Template Universe for Business Objects use by two Business Objects classes, Template Parents and Template Children, to facilitate retrieving data for lines represented by parent objects and for lines represented by COA objects which do not roll directly to a K-parent. In order to create a query that reports both parent and child lines, users need to do a union (this is already done in the canned report stored in the Business Objects repository) in the query panel. The joins for these classes are created as follows:

  For COA_OBJECTS that are represented by K-parent objects:
  PARENT_OBJECT_CODES.PARENT_OBJECT = GL_REPORT_TEMPLATE.H_REF AND GL_REPORT_TEMPLATE.H_REF_TYPE = 'P'

  For COA_OBJECTS that are not represented by K-parent objects:
  PARENT_OBJECT_CODES.CHILD_OBJECT = GL_REPORT_TEMPLATE.H_REF AND GL_REPORT_TEMPLATE.H_REF_TYPE = 'C'

• Lines of sequence greater than 1580 in the RCM Templates employ use of additional program logic which is executed at the time the report is generated to display calculations such as Other Cash Effect, CarryForward and Prior Year Cash Position. Line 1600 (Other Cash Effect) has been represented in the Data Warehouse by parent object code K112, which allows you to group object codes together to arrive at the Cash Effect total. Note that this does not take in to account special point in time logic for open periods in the way that the BEN Financials reports do. Similarly, the logic for lines higher than 1600 is not stored in the template itself, so those lines can not reliably be replicated in the Warehouse without special handling in a query.
• Users should beware summing multiple lines, as the RCM template groupings already reflect various levels of aggregation. Therefore, SUMming a report using K-Parents as result objects in an attempt to arrive at a "grand total" will likely lead to double (or triple, etc.) counting balances.
• To find balances for K-parent objects, regardless of use in the RCM template or sort order, users can perform an ad hoc query using the PARENT_OBJECT_CODES table (the FINQUERY universes "Par Object Code P" class). Business Objects users may make use of an object called "Par_Obj_1st_Char" which, when used as a condition (='K'), will return only K-parents
• The BEN Financials 211 report (which uses RCM_TEMPLATE_1C to report on Sponsored Project funds) has an "Active Fund" flag. Unless you screen on the fund status (using FUND_STATUS/ Status) in the Warehouse, you'll see results for all funds, both active and inactive. To replicate a BEN Financials Active Status of 'Y', set the FUND_STATUS/Status condition = 'A'.
• There are multiple RCM templates, each of which is used to run reports for only certain fund groups. In general, RCM Template 1A is the master template. Object code groupings differ for RCM templates 1A and 1C, to reflect differences in treatment of sponsored project-based funds. Unlike other funds (which treat treat graduate and professional student aid as a discount to tuition, including it in template 1A as contra-revenue in the Tuition Income section), grants and contracts funds show graduate and professional student aid in the Student Expense section of template 1C. RCM Template 1B is used for endowment funds.

GL_REPORT_TEMPLATE Table - Data Element Index   Tables and Data Elements   General Ledger Home   Data Warehouse Home