Penn Computing

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

GL_DETAIL Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.

 
Data element Definition
ACCOUNT_TYPE

Indexed - yes
Format - char (7)
May be null? yes

Means of categorizing accounts for use in financial reporting; reflects whether the account is revenue or expense. 
Values:
Asset

Equity

Expense

Liability

Revenue
ACCOUNTING_PERIOD

Indexed - yes
Format - char (6)
May be null? yes 

The Accounting Period stated in MON-YY format. MON is the calendar month (stored in upper case), and YY is the calendar year. Note that the beginning and ending dates of an ACCOUNTING_PERIOD are not the same as the first and last day of the calendar month. 

Example: JUL-99 (July 1999, which occurs in fiscal year 2000). 

Values:

List of values not available.
ACTUAL_AMOUNT

Indexed - no
Format - number (20,2)
May be null? yes

For transactions with a BALANCE_TYPE of 'Actual', the amount of the actual dollars for the COA_ACCOUNT for the transaction. When aggregating this column by COA_ACCOUNT for the accounting period, the sum should equal the ACTUAL_MONTH balance for the same period for the COA_ACCOUNT in the BALANCES table. This column will be populated with 0 for Budget and Encumbrance balance types.
Values:

-999,999,999,999,999,999.99 to 

999,999,999,999,999,999.99
ATTRIBUTE (1-20)

Indexed - no
Format - varchar2 (150)
May be null? yes

For Feeder transactions, attribute columns contain additional information about the transaction.

Usage of attribute columns varies, according to the individual Feeder source. For example, for Bookstore feeder transactions, attributes contain Request number, Transaction Date, Contact Person; for BEN Deposits, attributes contain Tracking number, Center/ORG, Line Number, Preparer; for FedEx, attributes contain Legacy Account, HUP Cost Center, Invoice number, Airbill number, and so on for other feeders.

Note that attribute usage may vary over time, and history will not be updated accordingly. Attribute columns are populated for transactions from February 7, 2007, and forward.

BALANCE_TYPE

Indexed - no
Format - char (11)
May be null? yes 

Indicates whether the journal entry is for an actual, budget, or encumbrance transaction.
 
Values:

Actual

Budget

Encumbrance
BATCH_ID

Indexed - yes
Format - number (15)
May be null? yes

Unique identifier for the journal batch.
 
Values:

List of values not available
CALENDAR_YEAR

Indexed - yes
Format - char (4)
May be null? yes

The calendar year for the ACCOUNTING_PERIOD. The calendar year begins January 1 and ends December 31. Example: 1999 (the CALENDAR_YEAR for ACCOUNTING_PERIOD JUL-99). 
Values:

List of values not available.
CONTEXT (CONTEXT 2-4)

Indexed - no
Format - varchar2 (150)
May be null? yes

For Feeder transactions, context columns (CONTEXT, CONTEXT2, CONTEXT3, CONTEXT4) contain additional information about the feeder source.

Context columns are populated for transactions from February 7, 2007, and forward.

COA_ACCOUNT

Indexed - yes
Format - char (26)
May be null? yes

The seven segment values that comprise the 26-position Accounting Flexfield. The segments are COA_CNAC, COA_ORG, COA_BC, COA_FUND, COA_OBJECT, COA_PROGRAM, and COA_CREF. 

Example: 40042274525799519120021438.

See also COA_CNAC, COA_ORG, COA_BC, COA_FUND, COA_OBJECT, COA_PROGRAM, and COA_CREF.

Values:

List of values not available. 
COA_BC

Indexed - no
Format - char (1)
May be null? yes

The budgetary control character that represents the level at which funds availability checking occurs. 

COA_BC is the third segment of the Accounting Flexfield. 

Examples: 1 (CNAC/ORG/FUND Year-To-Date); T (All Funds Checks). 

Values:

0 Project-to-Date for capital projects

1 CNAC/ORG/FUND Year-To-Date

2 CNAC/ORG/FUND/CREF Year-To-Date

4 CNAC/ORG/FUND/CREF Project-to-Date

A Funds Check parent of 1

B Funds Check parent of 2

D Funds Check parent of 4

T All Funds Checks
COA_CNAC

Indexed - yes
Format - char (3)
May be null? yes

The 3-character center Net Asset Class (NAC) code. The first two positions of the COA_CNAC identify the school or center. The last position identifies the Net Asset Class: 0 (Unrestricted); 1 (Temporarily Restricted); 2 (Permanently Restricted). 

COA_CNAC is the first segment of the Accounting Flexfield. 

Example: 880 (Medical center, Unrestricted) 

Values:

Refer to the CNAC_Codes table for values.
COA_CREF

Indexed - yes
Format - char (4)
May be null? yes 

The 4-character Center Reference code. This is an identifier uniquely defined by each school or center. 

COA_CREF is used to record information that is important to a school or center but is inappropriate for any other segment of the Accounting Flexfield. The value of the center Reference code is unique within a school or center. 

COA_CREF is the seventh and last segment of the Accounting Flexfield. 

See the definition for CENTER_REF_CODES / CENTER_REF_CODE. 

Examples: 4045 (Graduate Programs); 4091 (SAS Newsletter) 

Values:

Refer to the center_REF_CODES table for values. 
COA_FUND

Indexed - yes
Format - char (6)
May be null? yes

The 6-character fund number. A fund is the unique identifier for a specific set of financial resources that needs tracking or management. 

COA_FUND is the fourth segment of the Accounting Flexfield. 

Examples: 000000 (General Unrestricted Fund); 010201 (SAS: GEN FUND 1) 

Values:

Refer to the FUND_CODES table for values.
COA_OBJECT

Indexed - yes
Format - char (4)
May be null? yes 

The 4-character OBJECT code. Identifies the asset, liability, revenue, or expense. 

COA_OBJECT is the fifth segment of the Accounting Flexfield.

Examples: 5220 (EXTERNAL OFFICE SUPPLIES); 1149 (CASH SUSPENSE) 

Values:

Refer to the OBJECT_CODES table for values.
COA_ORG

Indexed - yes
Format - char (4)
May be null? yes

The 4-character ORGANIZATION code. This is a subdivision of the University created for management purposes. 

An ORGANIZATION belongs to only one responsibility center, and its ORGANIZATION code is unique. That is, no two ORGANIZATIONs will have the same values for COA_ORG. 

COA_ORG is the second segment of the Accounting Flexfield.

Examples: 0702 (Wharton Finance); 0705 (Wharton Legal Studies) 

Values:

Refer to the ORG_CODES table for values. 
COA_PROGRAM

Indexed - yes
Format - char (4)
May be null? no

The 4-character PROGRAM code. This is an activity or work process commonly defined across the University for tracking a PROGRAM's activity across schools and centers (or across organizations within a school or center).

COA_PROGRAM is the sixth segment of the Accounting Flexfield. 

Examples: 7311 (Alumni Relations); 0001 (Discretionary) 

Values:

Refer to the PROGRAM_CODES table for values. 
COA_RESPONSIBLE_ORG
Indexed - yes
Format - char (4)
May be null? yes
The 4-character code for the organization responsible for managing the fund. Many organizations may use the same fund, but only one organization -- the COA_RESPONSIBLE_ORG -- is accountable for managing the fund. Those who are authorized to access records for the COA_RESPONSIBLE_ORG for the fund may access all records for the fund, regardless of the value of the COA_ORG in the COA_ACCOUNT for the record. 

Examples: 0011 (NYC Penn Club); 0215 (French Institute). 

Values:

Refer to the ORG_CODES table for values. 
ENCUMBRANCE_AMOUNT

Indexed - no
Format - number (20,2)
May be null? yes

For transactions with a BALANCE_TYPE of 'Encumbrance', the amount of encumbered funds (encumbrance sources may be Salary Management, Purchasing, Payables, etc.) for the COA_ACCOUNT for the transaction. Encumbered funds have been set aside but have not yet been spent for a specific planned, approved expenditure. This column will be populated with 0 for Actual and Budget balance types. 
Values:

-999,999,999,999,999,999.99 to 

999,999,999,999,999,999.99
EXTRACT_DATE

Indexed - yes
Format - date (7)
May be null? yes

The date the GL_DETAIL record was extracted from BEN Financials and loaded into the Data Warehouse. In Business Objects, it is displayed as specified in the desktop operating system settings. It is recommended that users specify a format including 4 digits for the year. (To specify the date format in Windows, use the Control Panel's Regional Settings, selecting the Date tab and specifying the Short date style. On the Macintosh, use the Date & Time Control Panel.) 
Values:

List of values not available.
FISCAL_MONTH_SEQ

Indexed - yes
Format - char (2)
May be null? no

The month (or accounting period) of the fiscal year. This field is used for sorting.

Examples: 01 (July); 11 (May). See also FISCAL_YEAR 

Values:

01        July

02        August

03        September

04        October

05        November

06        December

07        January

08        February

09        March

10        April

11        May

12        June

13        Adjustment period
FISCAL_YEAR

Indexed - yes
Format - char (4)
May be null? yes

The financial year in which the ACCOUNTING_PERIOD falls. Penn's fiscal year begins July 1 of one calendar year and ends June 30 of the next calendar year. 

Example: 2000 (fiscal year that began July 1, 1999, and ended June 30, 2000) 

Values:

List of values not available. 
HOLD_INDICATOR

Indexed - no
Format - varchar2 (1)
May be null? yes

Indicates whether the invoice is on user hold. Populated for Purchasing and Payables transactions only; null for all other transactions. 
Values:

Y

N
INVOICE_DESCRIPTION

Indexed - no
Format - varchar2 (240)
May be null? yes

Contains invoice number and purchase order number for Purchasing and Payables transactions for certain vendors and ProCard transactions. Null for all other transactions. 
Values:

List of values not available. 
INVOICE_ID

Indexed - no
Format - number (15)
May be null? yes

The unique BEN Financials identifier for an invoice. When counting invoices, be sure to count by this column in order to capture all invoices that meet your criteria (since the INVOICE_NUMBER may be duplicated among different vendors). Populated for Purchasing and Payables transactions only; null for all other transactions. 
Values:

List of values not available.
INVOICE_NUMBER

Indexed - yes
Format - varchar2 (100)
May be null? yes 

The vendor's invoice number. This is not a unique identifier, as multiple vendors may use the same invoice numbers. For a unique identifer of an invoice within BEN Financials, refer to INVOICE_ID. Populated for Purchasing and Payables transactions only; null for all other transactions. 
Values:

List of values not available.
JE_HEADER_ID

Indexed - yes
Format - number (15)
May be null? yes

The unique identifier (within BEN Financials) for the journal entry header. 
Values:

List of values not available.
JOURNAL_BATCH_NAME

Indexed - yes
Format - varchar2 (100)
May be null? yes

For most transactions, identifies the journal batch. Ths column will be null for Salary Management encumbrances and Custom/Mass Allocation actual transactions. 

Examples: PRLPAY 001 19990618 PRL_PAYROLL 1351395: A; CJE: Purchasing 1310469: E 

Values:

List of values not available.
JOURNAL_CATEGORY

Indexed - yes
Format - varchar2 (25)
May be null? yes

The category of the journal entry. 

Examples: Feeder, Purchase Invoices, SAL-MGMT, Allocation 

Values:

List of values not available.
JOURNAL_LINE_DESC

Indexed - no
Format - varchar2 (240)
May be null? yes

The description from the General Ledger journal entry line. 
Values:

List of values not available.
JOURNAL_LINE_NUMBER

Indexed - no
Format - number
May be null? yes

The number of the General Ledger journal entry line. 
Values:

List of values not available.
JOURNAL_SOURCE_NAME

Indexed - yes
Format - varchar2 (25)
May be null? yes

The source of data for the journal entry. 

Examples: PRL_ENCUMBER, MassAllocation, Manual, TELECOMM, Purchasing 

Values:

List of values not available. 
JOURNAL_USER_NAME

Indexed - no
Format - varchar2 (100)
May be null? yes

The BEN Financials user name of the user that created the journal header. 
Values:

List of values not available.
LINE_USER_NAME

Indexed - no
Format - varchar2 (100)
May be null? yes

The BEN Financials user name of the user that created the journal line entry. 
Values:

List of values not available.
MONTH

Indexed - yes
Format - char (2)
May be null? yes

The number that identifies the calendar month (or accounting period). 

Example: 01 (January); 11 (November). 

Values:

01        January

02        February

03        March

04        April

05        May

06        June

07        July

08        August

09        September

10        October

11        November

12        December

13        Adjustment period
OPERATING_BUDGET_AMOUNT

Indexed - no
Format - number (20,2)
May be null? yes

For most transactions with a BALANCE_TYPE of 'Budget', the amount of the operating budget dollars for the COA_ACCOUNT for the transaction. When aggregating this column by COA_ACCOUNT for all transactions for the accounting period, the sum should equal the OPER_BUDGET_MONTH balance for the same period for the COA_ACCOUNT in the BALANCES table. This column will be populated with 0 for Actual and Encumbrance balance types. 
Values:

-999,999,999,999,999,999.99 to 

999,999,999,999,999,999.99
ORIGINAL_BUDGET_AMOUNT

Indexed - no
Format - number (20,2)
May be null? yes

For certain transactions with a BALANCE_TYPE of 'Budget', the amount of the original budget dollars for the COA_ACCOUNT for the transaction. This column will populated with 0 in most cases, as Original Budget entries are typically made via a Planning data load to BEN Financials, rather than via journal entry. This column will be populated with 0 for Actual and Encumbrance balance types. 
Values:

-999,999,999,999,999,999.99 to 

999,999,999,999,999,999.99
PAID_INDICATOR

Indexed - no
Format - varchar2 (1)
May be null? yes

Indicates whether the invoice was paid. Populated for Purchasing and Payables transactions only; null for all other transactions. 
Values:

Y

N
PO_HEADER_ID

Indexed - no
Format - number (20)
May be null? yes

The internal BEN Financials identifier for the purchase order. Populated for Purchasing and Payables transactions only; null for all other transactions. 
Values:

List of values not available.
PO_NUMBER

Indexed - yes
Format - varchar2 (30)
May be null? yes

The unique identifier for the purchase order. Populated for Purchasing and Payables transactions only; null for all other transactions. 
Values:

List of values not available.
QUARTER_SEQ

Indexed - no
Format - char (1)
May be null? yes

The quarter of the fiscal year in which the ACCOUNTING_PERIOD falls. 

Example: 1 (first quarter, July to September)

Values:

1     First quarter (July-September)

2     Second quarter (October-December)

3     Third quarter (January-March)

4     Fourth quarter (April-Adjustment period)
SEQUENCE_PK

Indexed - yes
Format - number (9)
May be null? no

The unique identifier for a row in the GL_DETAIL table. 
Values:

List of values not available.
SPECIAL_BUDGET_AMOUNT

Indexed - no
Format - number (20,2)
May be null? yes

For select transactions with a BALANCE_TYPE of 'Budget', the amount of the special budget for the COA_ACCOUNT for the accounting period. When aggregating this column by the COA_ACCOUNT for all transactions for the accounting period, the sum should equal the SPECIAL_BUDGET_MONTH balance for the same period for the COA_ACCOUNT in the BALANCES table. This column will be populated with 0 for Actual and Encumbrance balance types. 
Values:

-999,999,999,999,999,999.99 to 

999,999,999,999,999,999.99 
TRANSACTION_DATE

Indexed - yes
Format - date (7)
May be null? yes

The date the journal transaction was created, for all transactions other than Salary Management encumbrances and Custom/Mass Allocation transactions, for which it would be null. In Business Objects, it is displayed as specified in the desktop operating system settings. It is recommended that users specify a format including 4 digits for the year. (To specify the date format in Windows, use the Control Panel's Regional Settings, selecting the Date tab and specifying the Short date style. On the Macintosh, use the Date & Time Control Panel.) 
Values:

List of values not available.
VENDOR_NAME

Indexed - yes
Format - varchar2 (80)
May be null? yes

For a Purchasing or Payables transaction, the name of the vendor. Null for all other transactions. 
Values:

List of values not available.
VENDOR_NUMBER

Indexed - yes
Format - varchar2 (15)
May be null? yes

For a Purchasing or Payables transaction, the unique BEN Financials identifier for the vendor. Null for all other transactions. 
Values:

List of values not available.

 

GL_DETAIL Table   Tables and Data Elements   General Ledger Home   Data Warehouse Hom

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