Search Results gl_alloc_formula_lines_u1

Overview

GL.GL_ALLOC_FORMULA_LINES is a General Ledger table within the Oracle E-Business Suite that stores the individual formula lines belonging to MassAllocation and MassBudget definitions. Whenever a user defines a MassAllocation or MassBudget in the Formula window of the Define MassAllocations or Define MassBudgets forms, each line of that formula is persisted here as a separate row. A MassAllocation formula contains five lines, while a MassBudget formula contains four or five lines, depending on whether an offset line is defined.

The table is registered under the GL schema with FND Design Data reference SQLGL.GL_ALLOC_FORMULA_LINES and holds VALID status in release 12.1.1 and 12.2.2. It is provisioned in the APPS_TS_TX_DATA tablespace with PCT Free 10, while its unique index resides in APPS_TS_TX_IDX. Its role is fundamentally transactional and configuration-oriented: it defines how allocation amounts are computed before the Allocation engine expands those rules into journal entries. From a Data Vault modeling perspective, the mined foreign-key structure classifies this object as satellite-leaning, suggesting it is best modeled as a descriptive satellite attached to the GL_ALLOC_FORMULAS hub, with the formula line as the changeable descriptive attribute set.

Key Information Stored

Only the most consequential columns are highlighted below; the physical schema documents 56 columns in total.

• ALLOCATION_FORMULA_ID — the Mass allocation formula defining column and part of the composite primary key. It is a foreign key to GL_ALLOC_FORMULAS and is the primary correlation key used throughout the allocation engine.
• LINE_NUMBER — the formula line number (1, 2, 3, 4, or 5). Lines 1 through 4 map visually to rows A, B, C, and T on the Define MassAllocations and Define MassBudgets forms, while line 5 corresponds to row O.
• LINE_TYPE — the formula line type. The value is 'E' (Entered) for lines 1, 2, and 3, 'T' (Target) for line 4, and 'O' (Offset) for line 5.
• AMOUNT — the formula line amount used in the calculation of the allocation.
• SEGMENT_TYPES_KEY — a concatenated string holding the segment types (C for constant, L for loop, S for summary) that govern how each segment of the accounting flexfield is interpreted for the line.
• SEGMENT1 … SEGMENT30 — the individual segment value columns appropriate to the chart of accounts structure for the ledger. These hold the account combinations to which the allocation is applied.
• RELATIVE_PERIOD — the relative position of the accounting period whose balance is to be used in the allocation, enabling period-relative formulas.
• PERIOD_NAME — the explicit accounting period associated with the formula line where a relative period is not used.
• CURRENCY_CODE, ENTERED_CURRENCY, LEDGER_CURRENCY, TRANSACTION_CURRENCY — currency qualifiers referenced against FND_CURRENCIES, controlling which currency balance the formula draws upon.
• ACTUAL_FLAG, BUDGET_VERSION_ID, ENCUMBRANCE_TYPE_ID — flags and identifiers that determine whether the line reads from actuals, a specific budget version (GL_BUDGET_VERSIONS), or an encumbrance type (GL_ENCUMBRANCE_TYPES).
• LEDGER_ID and LEDGER_ACTION_CODE — the ledger context and the action the allocation engine performs for the line.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN provide the audit trail.

The surrogate unique index GL_ALLOC_FORMULA_LINES_U1 enforces uniqueness on the business key (ALLOCATION_FORMULA_ID, LINE_NUMBER), which mirrors the primary key GL_ALLOC_FORMULA_LINES_PK. OPERATOR is always 'E' and is not used, and SEGMENT_BREAK_KEY is likewise not used.

Common Use Cases and Queries

The table is central to diagnosing allocation behavior. A typical reconstruction of a formula retrieves all lines in display order:

• Retrieve formula lines for a specific allocation: SELECT line_number, line_type, amount, relative_period, segment1, segment2 FROM gl_alloc_formula_lines WHERE allocation_formula_id = :formula_id ORDER BY line_number;
• Identify target and offset lines across a ledger: SELECT f.formula_name, l.line_number, l.line_type, l.amount FROM gl_alloc_formula_lines l JOIN gl_alloc_formulas f ON f.allocation_formula_id = l.allocation_formula_id WHERE l.line_type IN ('T','O');
• Audit budget-based allocations by joining to GL_BUDGET_VERSIONS on BUDGET_VERSION_ID, or encumbrance-based lines via GL_ENCUMBRANCE_TYPES.
• Reconcile the entered currency and ledger currency columns to verify multi-currency allocations before running the MassAllocation program.
• Support migration or configuration reviews by extracting SEGMENT_TYPES_KEY alongside SEGMENT1–SEGMENT30 to validate loop and constant segment design.

Beware that segment columns are generic across the flexfield structure; interpretation requires the chart of accounts context of the associated ledger.

Related Objects

The formula lines depend on and are referenced by several GL objects:

• GL_ALLOC_FORMULAS — the parent header table; join on ALLOCATION_FORMULA_ID. This is the primary master-detail relationship.
• GL_BUDGET_VERSIONS — referenced via BUDGET_VERSION_ID for budget formulas.
• GL_ENCUMBRANCE_TYPES — referenced via ENCUMBRANCE_TYPE_ID.
• FND_CURRENCIES — referenced through CURRENCY_CODE, TRANSACTION_CURRENCY, ENTERED_CURRENCY, and LEDGER_CURRENCY.
• GL_ALLOC_INTERIM_1, GL_ALLOC_INTERIM_2, and GL_ALLOC_INTERIM_3 — interim allocation tables whose FORMULA_ID column points back to this table during allocation processing.
• MassAllocations and MassBudgets forms — the Formula window reads and writes this table directly, and the MassAllocation/MassBudget concurrent programs consume it when generating allocation journals.