Results for “cst_inv_distribution_v”

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

Overview

CST_INV_DISTRIBUTION_V is a single-organization reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Bills of Material (BOM) product family. It consolidates material transaction distribution information by joining inventory transaction header and distribution (accounting) lines against item, transaction type, reason, resource, department, and currency conversion reference data. The view exposes the accounting distribution detail generated when material transactions are processed in Inventory, WIP, and Cost Management, making it suitable for costing, valuation, and reconciliation reporting.

The designation "SINGLE-ORG view" is significant. The view is not secured by organization access controls applied through a multi-org policy at the view level; instead it exposes the organization identifier as an explicit column that consumers must filter. This design supports reporting tools, custom concurrent programs, and integration extracts that need a flattened, denormalized projection of transaction distribution data without navigating the full MTL_TRANSACTION_ACCOUNTS and MTL_MATERIAL_TRANSACTIONS relationship directly.

Underlying Base Objects

The view is defined over the following documented objects: MTL_MATERIAL_TRANSACTIONS (the transaction header, aliased MMT), MTL_TRANSACTION_ACCOUNTS (the accounting distribution lines, aliased MTA), MTL_SYSTEM_ITEMS_VL (item master, MSI), MTL_TRANSACTION_TYPES (MTT), MTL_TXN_SOURCE_TYPES (MTST), MTL_TRANSACTION_REASONS (MTR), MTL_PARAMETERS, BOM_DEPARTMENTS (BD), BOM_RESOURCES (BR), CST_ACTIVITIES (CA), GL_DAILY_CONVERSION_TYPES (GLT), and MFG_LOOKUPS (LU1, LU2). Most are referenced through APPS synonyms; MTL_SYSTEM_ITEMS_VL and MFG_LOOKUPS are views in their own right.

The driving relationship is MMT to MTA. MTL_MATERIAL_TRANSACTIONS supplies the transaction header attributes—item, subinventory, locator, transaction type, quantity, UOM, and date—while MTL_TRANSACTION_ACCOUNTS supplies the accounting distribution rows, including primary quantity, transaction value, base transaction value, currency, conversion rate, accounting line type, and the reference account. Item description and primary UOM come from MTL_SYSTEM_ITEMS_VL. Departments and resources are resolved through BOM_DEPARTMENTS and BOM_RESOURCES for WIP-related distributions, and CST_ACTIVITIES provides the activity name.

Key Columns

• TRANSACTION_ORGANIZATION_ID — Aliased from MMT.ORGANIZATION_ID, the organization in which the material transaction occurred. This is the organization users filter on when they search for "transaction_organization_id".
• ORGANIZATION_ID — Exposed from MTA.ORGANIZATION_ID, the organization on the accounting distribution line.
• TRANSACTION_ID, INVENTORY_ITEM_ID, REVISION, SUBINVENTORY_CODE, LOCATOR_ID — Core transaction identification and inventory location attributes.
• TRANSACTION_TYPE_ID / TRANSACTION_TYPE_NAME, TRANSACTION_SOURCE_TYPE_ID / TRANSACTION_SOURCE_TYPE_NAME, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_NAME — Classification of the transaction and its originating source, joined from MTL_TRANSACTION_TYPES and MTL_TXN_SOURCE_TYPES.
• TRANSACTION_DATE, TRANSACTION_QUANTITY, TRANSACTION_UOM — Timing and quantity of the transaction in the transaction UOM.
• PRIMARY_QUANTITY and computed quantity — A DECODE expression returns the signed primary quantity for accounting line types 1, 14, and 3, and otherwise applies the sign of BASE_TRANSACTION_VALUE. A companion DECODE derives a unit cost from BASE_TRANSACTION_VALUE divided by PRIMARY_QUANTITY.
• CURRENCY_CODE, CURRENCY_CONVERSION_DATE, USER_CONVERSION_TYPE, CURRENCY_CONVERSION_RATE — Currency and conversion detail, with the conversion type resolved from GL_DAILY_CONVERSION_TYPES.
• ACCOUNTING_LINE_TYPE and MEANING, TRANSACTION_VALUE, BASE_TRANSACTION_VALUE, COST_ELEMENT_ID, RATE_OR_AMOUNT, REFERENCE_ACCOUNT, GL_BATCH_ID — Costing and general ledger distribution attributes.
• DEPARTMENT_CODE, RESOURCE_CODE, ACTIVITY, REASON_NAME, OPERATION_SEQ_NUM, TRANSACTION_REFERENCE — WIP, activity, and reason context for the distribution.
• Standard WHO columns — ROWID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

The view is typically used to report material distribution accounting for a specific organization and date range, to reconcile perpetual inventory balances against general ledger distributions, and to extract costing detail for external systems. The most common filter is the transaction organization. A typical query:

SELECT transaction_id,
       transaction_organization_id,
       inventory_item_id,
       transaction_date,
       transaction_quantity,
       transaction_uom,
       transaction_value,
       base_transaction_value,
       accounting_line_type
FROM   apps.cst_inv_distribution_v
WHERE  transaction_organization_id = :org_id
AND    transaction_date BETWEEN :start_date AND :end_date
ORDER BY transaction_id;

A second common pattern aggregates distributed value by transaction type for a given period:

SELECT transaction_type_name,
       SUM(base_transaction_value) total_base_value
FROM   apps.cst_inv_distribution_v
WHERE  transaction_organization_id = :org_id
AND    transaction_date >= :start_date
GROUP BY transaction_type_name;

Because the view exposes ORGANIZATION_ID from both the transaction header and the accounting line, joins or filters should always specify TRANSACTION_ORGANIZATION_ID explicitly to avoid cross-organization result sets. When querying for "transaction_organization_id," remember it is an alias for MMT.ORGANIZATION_ID, so it reflects the organization where the material transaction was performed, which may differ from the accounting line organization in inter-org or transfer scenarios.