Results for “cst_pac_ael_gl_inv_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CST_PAC_AEL_GL_INV_V is an APPS-owned, VALID database view in Oracle E-Business Suite 12.1.1 and 12.2.2. It is catalogued under the BOM (Bills of Material) product family, which encompasses the costing and inventory transaction accounting infrastructure shared with Oracle Inventory and Oracle Cost Management. The view exposes the accounting event lines generated by inventory material transactions and their subsequent transfer to the General Ledger. In practice, it serves as a reporting and reconciliation layer that joins inventory transaction data from MTL_MATERIAL_TRANSACTIONS to the corresponding subledger accounting entries in CST_AE_HEADERS and CST_AE_LINES, and further to the GL journal entries produced by the transfer process.

Its stated role within Oracle EBS reporting is to present a unified, query-ready record of inventory transaction activity combined with its cost accounting and GL posting information. This makes it valuable for subledger-to-GL reconciliation, audit trails, period-close validation, and custom operational reporting where a single row summarizes the transaction, its accounting event, and its journal line details.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following referenced objects, most of which are synonyms resolving to the corresponding base tables in the APPS schema: CST_AE_HEADERS, CST_AE_LINES, CST_COST_ELEMENTS, CST_COST_GROUPS, CST_COST_GROUP_ASSIGNMENTS, CST_COST_TYPES, CST_LE_COST_TYPES, GL_DAILY_CONVERSION_TYPES, GL_IMPORT_REFERENCES, GL_JE_HEADERS, GL_JE_LINES, GL_PERIOD_STATUSES, GL_SETS_OF_BOOKS (a view, not a synonym), MFG_LOOKUPS (a view), MTL_ITEM_LOCATIONS, MTL_MATERIAL_TRANSACTIONS, MTL_PARAMETERS, MTL_TRANSACTION_REASONS, MTL_TRANSACTION_TYPES, and MTL_TXN_SOURCE_TYPES.

The view text confirms these relationships. It selects from cost type and cost group constructs (CST_COST_TYPES, CST_COST_GROUP_ASSIGNMENTS, CST_LE_COST_TYPES) to establish the costing context, from CST_AE_HEADERS and CST_AE_LINES for the accounting event and journal line detail, and from MTL_MATERIAL_TRANSACTIONS (aliased MMT) and related MTL lookup tables (MTL_TRANSACTION_TYPES, MTL_TXN_SOURCE_TYPES) for the inventory transaction facts. GL tables (GL_JE_HEADERS, GL_JE_LINES, GL_IMPORT_REFERENCES, GL_PERIOD_STATUSES, GL_SETS_OF_BOOKS, GL_DAILY_CONVERSION_TYPES) supply the general ledger and currency conversion context. MFG_LOOKUPS provides descriptive meanings.

Key Columns

The projected column list includes the following significant columns:

• LEGAL_ENTITY – the legal entity owning the inventory accounting entries.
• COST_GROUP_ID / COST_GROUP / COST_TYPE_ID / COST_TYPE – the cost group and cost type defining the costing basis used for the transaction.
• ORGANIZATION_ID / ORGANIZATION_CODE – the inventory organization for the transaction, resolved from MTL_PARAMETERS.
• PRIMARY_COST_METHOD – the costing method applied (for example Standard or Average).
• SET_OF_BOOKS_ID – the ledger identifier for the associated accounting entries, exposed as a literal 401 in the select list.
• TRANSACTION_TYPE_NAME / TRANSACTION_TYPE_ID / TRANSACTION_ACTION_ID – the material transaction type classification.
• TRANSACTION_DATE / TRANSACTION_REFERENCE – the date and reference of the material transaction.
• ACCOUNTING_EVENT_ID / AE_LINE_TYPE_CODE – identifiers linking the row to the subledger accounting event and line.
• CODE_COMBINATION_ID – the accounting flexfield combination for the journal line.
• CURRENCY_CODE, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR – currency and debit/credit amounts.
• CURRENCY_CONVERSION_DATE / CONVERSION_TYPE / CONVERSION_RATE – the currency conversion attributes for the transaction.
• GL_SL_LINK_ID indicator – a 'Y'/'N' flag computed via DECODE on NVL(CAL.GL_SL_LINK_ID, -1) showing whether the line has been linked to GL.
• GL_TRANSFER_RUN_ID – the GL transfer run identifier for reconciliation to the journal posting.

Notably, the view uses a DECODE on TRANSACTION_ACTION_ID to surface either the TRANSFER_TRANSACTION_ID, the ACCOUNTING_EVENT_ID, or the TRANSACTION_ID depending on the transaction action and the sign of PRIMARY_QUANTITY, producing a consistent "transaction reference" key across different transaction shapes.

Common Use Cases and Queries

Typical scenarios include reconciling inventory accounting events to GL journal entries, auditing cost accounting for a given organization and period, and building custom inventory valuation or subledger drill-down reports.

SELECT organization_code, transaction_type_name, transaction_date,
       entered_dr, entered_cred    , accounted_dr, accounted_cr,
       gl_transfer_run_id
FROM   apps.cst_pac_ael_gl_inv_v
WHERE  organization_id = :org_id
AND    transaction_date BETWEEN :from_date AND :to_date;

To identify accounting lines that have not yet been transferred to the General Ledger:

SELECT transaction_id, accounting_event_id, code_combination_id,
       accounted_dr, accounted_cr
FROM   apps.cst_pac_ael_gl_inv_v
WHERE  gl_transfer_run_id IS NULL;

Because the view joins many base objects, queries should be filtered by organization and date range to control execution cost. It is commonly used by developers extending Oracle Inventory and Cost Management reporting, and by functional analysts performing period-end subledger-to-GL tie-outs.