Search Results to_org_code

Overview

MTL_MOVEMENT_STATISTICS_V is an Inventory (INV) module view in Oracle E-Business Suite 12.1.1 and 12.2.2 that presents a consolidated, denormalized picture of material movement statistics with associated financial and invoicing context. Unlike operational transaction tables that store raw movement rows, this view joins inventory movement records to lookup meanings, legal entities, customers, invoice batches, receivables transactions, and organization codes, producing a single reporting-ready result set. Its principal role is analytical and reporting-oriented: it allows inventory, order management, and receivables data to be correlated without the developer writing the full join tree. Each row carries a ROW_ID pseudo-column derived from the underlying movement table, enabling a stable reference to the source movement record.

Note that the ETRM metadata records this view as "Not implemented in this database." In practice, the view may not be deployed in all environments or may be absent from certain editions, so availability should be verified against the target instance before use in custom reports or integrations.

Underlying Base Objects

The documented view text references no explicitly enumerated base tables in the ETRM metadata, but the column aliasing and MMS prefix confirm that the primary driving object is the Inventory movement statistics table (the alias MMS, i.e., MTL_MOVEMENT_STATISTICS). The view reaches several secondary objects through outer or decode-based joins:

• FND_LOOKUP_VALUES (aliases FL0, FL1, FL2) — resolves MOVEMENT_STATUS, MOVEMENT_TYPE, and DOCUMENT_SOURCE_TYPE into their user-facing meanings.
• HR_LEGAL_ENTITIES (HLE) — supplies the legal entity name for the owning organization.
• RA_CUSTOMERS (RC1) and HR_LOCATIONS/site-use objects (RSU1) — provide bill-to customer name, number, and site location.
• RA_BATCHES (AB), RA_CUSTOMER_TRX_ALL (AI), and RA_CUSTOMER_TRX_LINES_ALL (RCTL) — supply invoice number, date, document number, line number, invoiced quantity, unit price, and extended amount.
• RA_CUSTOMER_TRX / transaction header (RCT) — provides sales-order-referenced transaction number and date.
• MTL_PARAMETERS (MP1, MP2) and organization location objects (HOU1) — resolve from/to organization codes and names.

Because the view consolidates movement, receivable, and organization data, it behaves as a reporting convenience layer over the movement statistics entity rather than a normalized operational table.

Key Columns

The view exposes a wide set of identifiers and descriptive attributes. Movement identity and status columns include MOVEMENT_ID, ORGANIZATION_ID, MOVEMENT_STATUS with MOVEMENT_STATUS_MEANING, MOVEMENT_TYPE with MOVEMENT_TYPE_MEANING, and DOCUMENT_SOURCE_TYPE with its meaning. Customer and billing columns include BILL_TO_CUSTOMER_ID, CUSTOMER_BILL_TO, CUSTOMER_BILL_TO_NUMBER, and CUSTOMER_BILL_TO_SITE.

Invoicing columns are central to the user's search term. INVOICE_QUANTITY is computed with a DECODE that branches on DOCUMENT_SOURCE_TYPE: for miscellaneous ('MISC') sources it returns the stored MMS.INVOICE_QUANTITY; otherwise it returns NVL(MMS.INVOICE_QUANTITY, RCTL.QUANTITY_INVOICED), meaning the movement's own invoice quantity is preferred, falling back to the receivables transaction line's invoiced quantity. Related columns are INVOICE_UNIT_PRICE, INVOICE_LINE_EXT_VALUE, INVOICE_NUM, INVOICE_DATE_REFERENCE, INVOICE_LINE_NUM, and DOCUMENT_NUM. From/to organization columns include FROM_ORGANIZATION_ID, FROM_ORG_CODE, FROM_ORG_NAME, TO_ORGANIZATION_ID, and TO_ORG.

Common Use Cases and Queries

Typical uses include reconciling invoiced quantities against movement records, reporting bill-to customer invoicing by legal entity, and analyzing movement status by organization. Two representative queries follow.

• List invoiced movements for a customer with quantity and value:

SELECT INVOICE_NUM,
       INVOICE_DATE_REFERENCE,
       CUSTOMER_BILL_TO,
       INVOICE_QUANTITY,
       INVOICE_UNIT_PRICE,
       INVOICE_EXT_VALUE
FROM   MTL_MOVEMENT_STATISTICS_V
WHERE  BILL_TO_CUSTOMER_ID = :p_customer_id
AND    TRANSACTION_DATE BETWEEN :p_from AND :p_to;

• Summarize movement statistics by type and status:

SELECT MOVEMENT_TYPE_MEANING,
       MOVEMENT_STATUS_MEANING,
       FROM_ORG_CODE,
       TO_ORG,
       COUNT(*)            row_count,
       SUM(INVOICE_QUANTITY) total_invoiced_qty
FROM   MTL_MOVEMENT_STATISTICS_V
GROUP  BY MOVEMENT_TYPE_MEANING,
          MOVEMENT_STATUS_MEANING,
          FROM_ORG_CODE,
          TO_ORG;

Because INVOICE_QUANTITY depends on DOCUMENT_SOURCE_TYPE and on the availability of receivables line data, filters should account for 'MISC' sources separately, and the view's presence in the target instance must be confirmed given the "not implemented" metadata note.