Results for “cstbv_matl_txn_distributions”

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

Overview

CSTBV_MATL_TXN_DISTRIBUTIONS is a read-only view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is documented under the BOM (Bills of Material) product family and carries the description tag "Retrofitted," indicating it was created or adapted during the ETRM (E-Business Suite Technical Reference Manual) remediation cycle to expose legacy column and lookup semantics in a consistent, forward-compatible form. The view presents material transaction accounting distributions — the cost accounting entries generated when inventory, work-in-process, or receiving transactions are processed — by projecting a curated subset of columns from MTL_TRANSACTION_ACCOUNTS.

Its primary role is to serve as a reporting and integration interface. Reporting tools, custom concurrent programs, and downstream interfaces query the view rather than the base table so that consumer code remains insulated from changes in the base schema. The view is declared WITH READ ONLY, which prevents DML through the view and confirms its intent as a query surface. The presence of "_LA:" prefixed columns (accounting attribute descriptors) and a security predicate ('_SEC:MTA.ORGANIZATION_ID' IS NOT NULL) reflects the standard EBS view-generation pattern for multi-org security and descriptive flexfield lookup resolution.

Underlying Base Objects

The view is defined over a single referenced base object: MTL_TRANSACTION_ACCOUNTS (accessed through a SYNONYM), aliased as MTA in the view text. All columns in the SELECT list are drawn directly from that table; the view introduces no joins, unions, or aggregations. Filtering is limited to the multi-org security predicate that requires ORGANIZATION_ID to be non-null. Because it is a one-to-one projection of a single base table with a read-only constraint, the view performs efficiently and can be indexed effectively when queries supply selective predicates on the base table columns such as TRANSACTION_ID, ORGANIZATION_ID, or INVENTORY_ITEM_ID.

Key Columns

The user search term "txn_source_id" corresponds to the transactional source reference exposed by this view. Depending on the consumer's naming, this maps to the underlying TRANSACTION_SOURCE_ID column (which the documented column list presents as TXN_SOURCE_ID in the view's metadata). It identifies the source transaction that generated the accounting distribution and is frequently used in drill-down and reconciliation queries.

• TXN_SOURCE_ID / TRANSACTION_SOURCE_ID — Reference to the originating transaction source. Paired with TRANSACTION_SOURCE_TYPE_ID, which is resolved through the MT_L_DISPOSITION lookup for a meaningful description.
• TRANSACTION_ID — Identifier of the parent material transaction; the principal join key to MTL_MATERIAL_TRANSACTIONS.
• ORGANIZATION_ID — Operating unit/inventory organization context, enforced by the view's security predicate.
• TRANSACTION_DATE, TRANSACTION_VALUE, BASE_TRANSACTION_VALUE, PRIMARY_QUANTITY — Core accounting values: the accounting date, the transaction amount, its base-currency equivalent, and the associated quantity.
• INVENTORY_ITEM_ID, COST_ELEMENT_ID, RESOURCE_ID, ACTIVITY_ID — Costing and manufacturing references linking the distribution to an item, cost element, resource, or activity.
• ACCOUNTING_LINE_TYPE and BASIS_TYPE — Lookup-derived attributes resolved through CST_ACCOUNTING_LINE_TYPE and CST_BASIS respectively, describing the nature of the accounting line.
• CURRENCY_CODE and related conversion columns — Currency, conversion date, type, and rate for the transaction value.
• GL_BATCH_ID, REFERENCE_ACCOUNT, ENCUMBRANCE_TYPE_ID — General Ledger transfer and account references.
• WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY for auditability.

Common Use Cases and Queries

Typical scenarios include inventory valuation reporting, cost accounting reconciliation, and drilling from a distribution back to its originating transaction. Because the view is read-only and org-secured, it is safe for ad-hoc reporting under a responsibility that grants the appropriate organization access.

To retrieve distributions for a specific item in an organization:

SELECT transaction_id, txn_source_id, transaction_date, transaction_value, accounting_line_type
FROM cstbv_matl_txn_distributions
WHERE organization_id = :org_id
AND inventory_item_id = :item_id;

To reconcile a distribution to its source transaction type using the disposition lookup:

SELECT d.transaction_id, d.txn_source_id, d.transaction_value, m.meaning source_type
FROM cstbv_matl_txn_distributions d, mfg_lookups m
WHERE d.transaction_source_type_id = m.lookup_code
AND m.lookup_type = 'MTL_DISPOSITION';

These patterns exploit the view's principal strengths: a stable, security-aware projection of material transaction accounting data suitable for both operational reporting and interface extraction in 12.1.1 and 12.2.2 environments.