Search Results gl_batch_id




Overview
Explore in AI Chat

Overview

The table INV.ORG_GL_BATCHES serves as an audit and control repository within Oracle EBS (11.5.10 through 12.2.x) for every batch of accounting transactions that have been successfully transferred to the General Ledger interface table (GL_INTERFACE). Its primary role is to record the history of each GL transfer execution, whether for a period close or an ad-hoc GL Transfer run. By storing a unique GL_BATCH_ID per transfer event, this table ensures transactional integrity: once a set of inventory or work-in-process transactions has been sent to GL, the associated GL_BATCH_ID in the source accounting tables—specifically MTL_TRANSACTION_ACCOUNTS (Inventory) and WIP_TRANSACTION_ACCOUNTS (Work In Process)—is updated to that batch ID. This mechanism prevents the same transaction row from being re-interfaced, thereby eliminating duplicate postings to the General Ledger. The table is owned by the INV schema and resides in the APPS_TS_TX_DATA tablespace.

Key Information Stored

Each row in ORG_GL_BATCHES captures a distinct GL transfer event. The primary key is a composite of ORGANIZATION_ID and GL_BATCH_ID. Key columns include:

  • GL_BATCH_ID (NUMBER, mandatory): A unique, system-generated identifier for the batch. A value of 0 indicates "Processing" status, positive integers denote successful completion, and negative integers signal a failure during the transfer process.
  • ORGANIZATION_ID (NUMBER, mandatory): Identifies the inventory organization (from MTL_PARAMETERS) to which the batch pertains.
  • ACCT_PERIOD_ID (NUMBER): The accounting period ID (from ORG_ACCT_PERIODS) that was current when the GL batch was created, i.e., the period being closed or transferred.
  • GL_BATCH_DATE (DATE): The scheduled close date of the accounting period, often populated automatically based on the period’s end date.
  • DESCRIPTION (VARCHAR2, 50): An optional, user-friendly description of the batch.
  • Standard Who Columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN track system-level auditing.

Common Use Cases and Queries

The table is essential for reconciling GL transfers and troubleshooting failures. Common use cases include:

  • Verifying GL Transfer Success: Check whether a period-end transfer completed or failed by querying GL_BATCH_ID status for a given organization and period.
  • Identifying Duplicate Attempts: Detect organizations where multiple positive GL_BATCH_ID entries exist for the same period, which may indicate re-transfers.
  • Auditing Transfer History: For a specific organization, list all batch transfers sorted by date to review period closing history.

Example SQL patterns:

-- Check status of GL batches for a period
SELECT ORGANIZATION_ID, GL_BATCH_ID, GL_BATCH_DATE,
       CASE WHEN GL_BATCH_ID > 0 THEN 'Success'
            WHEN GL_BATCH_ID = 0 THEN 'Processing'
            WHEN GL_BATCH_ID < 0 THEN 'Failed'
       END AS status
FROM INV.ORG_GL_BATCHES
WHERE ACCT_PERIOD_ID = 12345;

-- Join to see which transactions were included in a specific batch
SELECT mta.*
FROM MTL_TRANSACTION_ACCOUNTS mta
WHERE mta.ORGANIZATION_ID = 101 
  AND mta.GL_BATCH_ID = 56789;

Related Objects

Based on the documented foreign key relationships, INV.ORG_GL_BATCHES interacts with the following tables:

  • Referenced Tables (Child looks up Parent):
    • INV.ORG_GL_BATCHES.ORGANIZATION_ID → MTL_PARAMETERS: Validates that the organization exists.
    • INV.ORG_GL_BATCHES.ACCT_PERIOD_ID → ORG_ACCT_PERIODS: Validates the accounting period.
  • Referencing Tables (Parent is referenced by Child):
    • MTL_TRANSACTION_ACCOUNTS.ORGANIZATION_ID + MTL_TRANSACTION_ACCOUNTS.GL_BATCH_ID → INV.ORG_GL_BATCHES: Tracks which inventory accounting transaction rows belong to a particular GL batch.
    • WIP_TRANSACTION_ACCOUNTS.ORGANIZATION_ID + WIP_TRANSACTION_ACCOUNTS.GL_BATCH_ID → INV.ORG_GL_BATCHES: Tracks which work-in-process accounting rows belong to the same batch.

These relationships ensure referential integrity between the batch history and the actual accounting entries, enabling SQL joins to trace all transactions included in a specific GL batch.