This lesson should take about 50 minutes to complete. After completing this lesson, you should be able to recall the fourteen questions you need to ask when writing a General Ledger query.

Before you consider questions specific to querying General Ledger data, make sure you are familiar with the General Guidelines for Executing Queries.

Lesson 4. Questions to Ask Before Writing General Ledger Queries

Activity 1
Below are questions you should ask before writing a query to retrieve General Ledger data. Read the questions and their accompanying explanations. (Besides asking these questions, check the cautions in the table documentation in case additional considerations have been noted there that are not included in the questions below.

1. What accounting period do you want?
   
2. Do you want detail level balances, or summary level balances?
   
3. Are you summarizing on amounts?
   
4. Are you screening on school or center? For which net asset classes?
   
5. What do you mean by "my Organization?"
   
6. What do you mean by "active fund"?
   
7. What do you mean by "restricted fund"?
   
8. Do you want parents?
   
9. Do you want center reference codes? For which school or center?
   
10. Do you want codes, or English?
    
11. Are you using the old-to-new tables?
    
12. Are you doing grant year reporting?
13. How do you want the query results sorted?
    
14. When do you want the report?

1. What accounting period do you want?
   Accounting periods are identified by the month and calendar year. For example, the accounting period JUL-96 refers to the calendar month July, 1996, which is the first month of fiscal year 1997.

   One way to identify the accounting period you want is to use ACCOUNTING_PERIOD when setting the record selection conditions for queries involving the BALANCES table or the SUMMARY_BALANCES table. 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.

   Beware summarizing beginning balance, ending balance, fiscal year-to-date, project year-to-date, fiscal year total, or project totals for multiple accounting periods.

2. Do you want detail level balances, or summary level balances?
   Use the BALANCES table if you want balances for object codes at the detail account level (for example, for object 5412, WATER&SEWER). Use the SUMMARY_BALANCES table if you want balances for object codes at the summary account level (for example, for object TEXP).
   
   Business Objects users will find the BALANCES table in the FINQUERY Universe, and the SUMMARY_BALANCES table in the FINSUMQ Universe.
   
   
3. Are you summarizing on amounts?
   Beware summarizing beginning balance, ending balance, fiscal year-to-date, project-to-date, fiscal year total, or project total amounts for multiple accounting periods. For example, if you summarize fiscal year-to-date actuals for JUL-97 and AUG-97 together, you will double count the money for JUL-97.
   
   
4. Are you screening on school or center? For which net asset classes?
   Remember that CNAC is a combination of Center and Net Asset Class.

   If you want all the balances for a given school or center (such as Wharton, 07), ask for records where the CNAC matches a pattern (such as 07%).

   If you want a particular school and net asset class, your record selection conditions should specifiy that the CNAC is equal to what you want (such as 070), or is in the list of what you want (such as 071 and 072).

   Remember that the first two characters of the organization code are not necessarily the same as the code for the school or center.

5. What do you mean by "my Organization?"
   For the organization of record for a balance, use the COA_ORG in the BALANCES or SUMMARY_BALANCES table.

   For the organization responsible for managing the fund of record for a balance, use the COA_RESPONSIBLE_ORG in the BALANCES or SUMMARY_BALANCES table.

   When reporting on funds, the FUND_RESPONSIBLE_ORG in the FUND_CODES table is the organization responsible for managing the fund; the INTERNAL_DESIGNATED_CODE is the organization responsible for setting University restrictions on how the fund may be spent.

   Some organizations are surrogates, used to summarize data for related Organizations. To exclude surrogate organizations from your query, specify that SURROGATE_ORG (in the ORG_CODES table) is equal to 'N'.

   The attributes of an ORG_CODE may change over time. However, only the latest information for an ORG_CODE is kept. This may pose a problem when reading historical data for an ORG_CODE. For example, if you are querying balances for an organization for the period JAN-97, and are retrieving the Description for the organization, if the effective date for the ORG_CODE is later than Jan. 31, 1997, the value of Description might not be what it was during January, 1997. Check the effective and expiration dates for the code in the ORG_CODES table to see if the attributes were in effect at the desired time.

   The ORGANIZATION_TYPE in the ORG_CODES table indicates the hierarchical type of the organization. From the top of the hierarchy on down, the four organization types are SC (schools or centers), UN (units), SU (subunits), or SS (sub-subunits). An organization is referred to as the parent, grandparent, or ancestor of the organizations that come below it in the hierarchy, and it is referred to as the child of its parents, grandparents, or ancestors. In some cases (for example, some Medical School organizations), a parent organization and its children may have the same ORGANIZATION_TYPE. This leads to the next question.

6. What do you mean by "active fund?"
   If by "active fund" you mean funds that may be used in BEN Financials transactions, the value (for the record for the fund in the FUND_CODES table) for Enabled will be 'Y'. If the value for Enabled is 'N', the fund code may not be used in BEN Financials transactions after the END_DATE.

   Funds that may be used in BEN Financials transactions are not necessarily allowed for budget journal entries or manual journal entries. Check BUDGET_ENTRY_ALLOWED and POSTING_ALLOWED.

   Keep in mind however, that within the BEN Financials General Ledger, there is no single simple criteria for what constitutes an "active" account. This is especially true for University Research Foundation (URF) accounts and for grants/contracts, both of which have activity that crosses the fiscal year. To effectively manage these accounts, it is necessary to be able to report on them for some period of time after they have expired - even though they may be disabled (with ENABLED flag set to 'N'). In order to satify this requirement, while at the same time ensuring that expired accounts eventually "drop off" the normal monthly reports, a standard definition of "active" is used for these accounts by all of the BEN Financials General Ledger reports. The definition is slightly different for URF accounts than for grants/contracts.

   This BEN Financials reporting definition of "active" status for grant/contract funds is reflected in the Data Warehouse in the FUND_STATUS table, which stores fund status ('Y' indicates that it is active, 'N' denotes inactive) for a given accounting period/COA_ORG/COA_FUND combination. So, if you are trying to match a BEN Financials report which was run with the "Active Flag" parameter set to 'Y', be sure to screen on STATUS in the FUND_STATUS table.

   Finally, the "Freeze grant" functionality in BEN Financials allows business administrators and managers to limit activity against a grant both during the life of the project and during the grant’s close-out or adjustment period. Authorized users can designate an entire fund as frozen for all transaction sources (online journal entries, new purchase orders, C-Forms, feeder systems, and Payroll) or for a specific transaction source. BEN Financials will automatically freeze a grant at project end for new purchase orders and at the end of the adjustment period for all other transaction sources. Unfreeze and refreeze functionality is also included. A fund's current freeze status information may be fund in the FUND_FREEZE table, while all historical freeze transactions are stored in FUND_FREEZE_HISTORY.

7. What do you mean by "restricted fund?"
   In the FUND_CODES table, fund use may be restricted by the donor or by the University. For information on restrictions, see DONOR_RESTRICT_ORG, DONOR_RESTRICT_PROGRAM, DONOR_RESTRICT_OBJECT, PENN_RESTRICT_ORG, and PENN_RESTRICT_PROGRAM.
   
   
8. Do you want parents?
   Some segments of the Accounting Flexfield may be grouped together under parent codes. Some parent codes are roll-up groups -- that is, summary balances are stored for the parent code. (For example, summary balances are not stored for parent object code 5XXX (OBJECT VALUES 5000-5999), but they are stored for parent object code TEXP (Total Expenses for Funds Checking).) The tables listing parent codes and associated child codes are PARENT_CNAC_CODES, PARENT_ORG_CODES, PARENT_FUND_CODES, PARENT_OBJECT-CODES, and PARENT_PROGRAM_CODES.

   A given code may have more than one parent associated with it, and may be the child of more than one code. For example, an organization that is a unit may be the parent of other units, subunits, or sub-subunits, and may be the child of another unit and of a school or center.

   If you are using the parent tables, expect the query to take a while to run.

   Remember that the parent tables are only as good as their data. Spurious parents may not have been removed from the table, and some parents may have yet to be added to the table.

   If you are using Business Objects, the joins to the parent tables are done for you, making it easy to retrieve three "generations" (for example, the "grandparent" organization, the "parent" organization, and the "child" organization, along with the balance amounts for the child). If you are using another query tool, use a self-join to create a hierarchy. (Connect by prior may work but is not recommended.)

   If you are using the parent table for organizations, note that data is kept only for the latest mapping of the organizations and their parents. Information on the parent of an organization is available only if the organization's expiration date (in the ORG_CODES Table) has not passed.

   Note that data is kept in the parent tables only for codes in use. For example, the parent object code 5XXX includes object codes 5000-5999, but (as of May, 1998), object code 5005 is not used, and there is no record in the PARENT_OBJECT_CODES table for object code 5005.
   

9. Do you want center reference codes? For which school or center?
   Remember that a given CENTER_REF_CODE will have different meanings for different CNAC's (Center/Net Asset Classes). Check the CNAC to ensure you are retrieving the appropriate data.

   Note that, while most values for CENTER_REF_CODE consist of 4 numerals, some values have fewer characters and some are not be numeric.
   

10. Do you want codes, or English?
    For example, do you want to see the code for the fund sponsor, or the name of the fund sponsor? If the latter, you'll need to use the decode table (in this case, the FUND_SPONSORS table).

    The main decode tables are CNAC_CODES, ORG_CODES, FUND_CODES, OBJECT_CODES, PROGRAM_CODES, CENTER_REF_CODES, and PURPOSE_CODES.

    There are also decode tables for codes associated with funds: FUND_BILLING_FORMAT_CODES, FUND_BILLING_FREQ_CODES, FUND_FINAL_RPT_CODES, FUND_INVESTMENT_CODES, FUND_LOC_RPTG_CODES, FUND_RECLASS_CODES, FUND_RPT_FORMAT_CODES, FUND_RPT_FREQ_CODES, and FUND_SPONORS.

    If you want the name of an organization, remember that the attributes of an ORG_CODE may change over time. Check the effective and expiration dates for the code in the ORG_CODES table to see if the name was in effect at the desired time.

    Which decoded value do you want? The FUND_CODES table has the 30-character FUND_DESC and the 70-character FUND_LONG_DESCRIPTION. The ORG_CODES table has the 20-character ORG_SHO_NAME and the 60-character DESCRIPTION. The PROGRAM_CODES table has the 60-character PROGRAM_DESC and the 70-character PROGRAM_LONG_DESC.
    

11. Are you using the old-to-new tables?
    If so, do you care whether the legacy accounts were actually used before July 1, 1996, the date when the the present General Ledger accounting structure went into production? To distinguish the legacy accounts used before July 1, 1996 from those that were not, use the GENERATED_FLAG.

    Do you want only the Accounting Flexfields or object codes that are currently active in BEN Financials? If so, set a condition on the ENABLED_FLAG. The ENABLED_DATE indicates the last time the ENABLED_FLAG was updated.

    The legacy account codes may map to different BEN Financials codes at different times. Check START_DATE_ACTIVE and END_DATE_ACTIVE to ensure that the query returns records that are valid for the desired time frame.

    More than one legacy account number may map to a given Accounting Flexfield, but a given Accounting Flexfield maps back to only one legacy account. When querying to determine the legacy account equivalents for Accounting Flexfields, be sure to check the NEWTOOLD_FLAG.

    When using the GL_PART_OLD_TO_NEW table, note that LEGACY_FUND is not a 6-character code. The 6-character code identifying the fund in BEN Financials is called FUND_LEGACY_LEDGER_CODE. concatenated with LEGACY_FUND is the 6-character code used to indicate the ledger and fund in the financial system used before the present General Ledger accounting structure was implemented.

    When using the ORG_OLD_TO_NEW table, check both the ORGANZIAITON_CODE and the PROGRAM_CODE -- while most schools, centers, and departments in another system are represented in BEN Financials as organizations, some may be represented as programs. Also, be sure to specify the desired LEGACY_ORG_APP (FIMS, the facilities system; FIN, the financial system used before July 1, 1996; or PAY, the payroll system). The legacy organization code sets are similar, but a given legacy organization code may have different meanings in different legacy application systems.

12. Are you doing grant year reporting?
    It may loosely be called a "grant year," but it's really a budget period -- it may be a year, less than a year, or more than a year. The BUDGET_PERIODS table is one of the ones you'll need.

    If you are using Business Objects, the join between the BALANCES table and the BUDGET_PERIODS table is done for you. If you are using another query tool, the criteria for joining these tables are that the fund codes match, and that the accounting period falls within the budget period. (That is, FISCAL_YEAR_BEGIN|FISCAL_MONTH_SEQ_BEGIN <= FISCAL_YEAR|FISCAL_MONTH_SEQ, and FISCAL_YEAR_END|FISCAL_MONTH_SEQ_END >= FISCAL_YEAR\FISCAL_MONTH_SEQ.)

    Which budget period do you want? For the budget period currently in effect, the value for CURRENT_PERIOD_FLAG will be Y. For the earliest budget period, the value for BUDGET_PERIOD_CHRON_SEQ will be 1. For the latest budget period, the value for BUDGET_PERIOD_REV_CHRON_SEQ will be 1--but that does not necessarily mean that the budget period is current. If the value for BUDGET_PERIOD_REV_CHRON_SEQ is 1, the period might have already ended, or it might not have begun yet. For the budget period in effect as of a given date, be sure the date falls between the BUDGET_PERIOD_BEGIN_DATE and the BUDGET_PERIOD_END_DATE.

    Are you trying to re-create a report for an old budget period (say, BUDGET_PERIOD_CHRON_SEQ1)? It is possible that you might not get the same results as you did when the report was first run, since the values for the BUDGET_PERIOD_BEGIN_DATE and the BUDGET_PERIOD_END_DATE may change over time. (For more information see the BUDGET_PERIODS table.)

    Are you working with a budget period that ends mid-month? For a fund with two budget periods --the first ending mid-month, the second beginning mid-month-- the beginning month for the second period is set to the following month, to avoid double counting balances. For example, if budget period 1 ends September 14, 1997 and budget period 2 begins September 15, 1997, the beginning month for the second period is set to October, 1997, to avoid double counting balances for September, 1997. Note that this means that some activity for the second budget period will be reported as occurring during the first budget period, and that the budget for the second budget period should be posted to its calculated begin month, not to the month in the budget period begin date.

    Are you working with budget periods that are not contiguous? The budget period begin month is set to the month following the end month for the previous budget period, even if it makes the budget period appear longer than it is. For example, in the unlikely event that a sponsor first funds a project for Jan. 1, 1996 through June 30, 1996, and then renews funding for Jan. 1, 1997 through June 30, 1997, the budget period begin month for budget period 2 will be calculated as July, 1996 (not Jan., 1997).

13. How do you want the query results sorted?
    The query will run faster if the results are sorted in the Business Objects report rather than in the query. However, if the results are to be exported for use by another software package, it is better to specify the sort in the query.
    
    
14. When do you want the report?
    Besides helping you plan your workload so you can produce the report on time, you may want to ask this question if the report pertains to the currently open period. You may want to discuss changing the report due date so you run the report on a Monday (or as soon as possible after the weekly refresh), or postpone running it until the period has closed.

Acitivity 2
Fill-in-the blank with the appropriate word to recall the questions you need to ask before writing a General Ledger query. Review the questions at the top of this page to determine if your answers are correct.

1. What accounting __________ do you want??
   
2. Do you want _____________ level balances or _____________ level balances?
   
3. Are you summarizing on _____________?
   
4. Are you screening on school or _____________? For which net asset classes?
   
5. What do you mean by "my _____________?"
   
6. What do you mean by "_____________ " fund?
   
7. What do you mean by "_____________" fund?
   
8. Do you want _____________?
   
9. Do you want center _____________ codes? For which school or center?
   
10. Do you want _____________, or English?
    
11. Are you using the _____________ tables?
    
12. Are you doing _______________ year reporting?
    
13. How do you want the query results _____________?
    
14. _____________ do you want the report?