Search Results edi_transaction_date




Overview
Explore in AI Chat

Overview

APPS.ECE_MTL_MOV_STAT_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes aggregated statistical movement data drawn from the Oracle Inventory movement statistics tables, filtered and joined against the EDI/E-Commerce Gateway configuration lookups. The view is part of the ECE (E-Commerce Gateway) family of database objects, and its name reflects that heritage: ECE for the E-Commerce Gateway module, MTL for the Inventory (MTL) tables, and MOV_STAT for movement statistics.

Its functional purpose is to present a consolidated, grouped summary of movement statistics records that have been marked as verified or finalized (MOVEMENT_STATUS values of 'V' or 'F') but have not yet been flagged as transmitted to an external trading partner. In practical terms, this view acts as a staging/reporting feed for Intrastat-style or similar regulatory statistical reporting, and for EDI movement-statistics interfaces that must transmit period movement data to customs or partner systems. Because the view aggregates (SUM and COUNT) rather than presenting row-level detail, it is primarily intended for reporting, reconciliation, and interface extraction rather than for transactional processing.

Underlying Base Objects

According to the documented view metadata for release 12.2.2, this view is defined over the following referenced base objects:

  • MTL_MOVEMENT_STATISTICS (SYNONYM) — the primary fact source. This table stores the individual movement statistics records, including period, movement type, transaction reference, values, weights, and status. The view aliases it as MMS.
  • FND_LOOKUPS (VIEW) — the Oracle Application Object Library lookups view, aliased as FL. It is used to validate the source document type of each movement record against the lookup type MVT_SOURCE_DOCUMENT_TYPES.
  • FND_GLOBAL (PACKAGE) — referenced as part of the view's runtime environment, providing session and context information such as organization and responsibility context at query time.

The join between MTL_MOVEMENT_STATISTICS and FND_LOOKUPS is an inner join on FL.LOOKUP_TYPE = 'MVT_SOURCE_DOCUMENT_TYPES' and FL.LOOKUP_CODE = MMS.DOCUMENT_SOURCE_TYPE, so only movement records whose source document type corresponds to a valid, defined lookup value are returned. The view further restricts output to records whose MOVEMENT_STATUS is 'V' or 'F' and whose EDI_SENT_FLAG is 'N' or null.

Key Columns

Common Use Cases and Queries

Typical scenarios include extracting non-transmitted movement statistics for EDI transmission, reconciling verified/finalized movement values by period and organization, and feeding regulatory statistical declarations. A representative query selecting aggregated statistics for a period and organization would be of the form:

  • SELECT period_name, movement_type, organization_id, TOTAL_STATISTICAL_VALUE, TOTAL_TOTAL_WEIGHT, TOTAL_RECORDS FROM APPS.ECE_MTL_MOV_STAT_V WHERE period_name = :period AND organization_id = :org_id ORDER BY movement_type;
  • SELECT period_name, SUM(TOTAL_STATISTICAL_VALUE) FROM APPS.ECE_MTL_MOV_STAT_V WHERE MOVEMENT_STATUS IN ('V','F') GROUP BY period_name;
  • SELECT * FROM APPS.ECE_MTL_MOV_STAT_V WHERE EDI_TRANSACTION_REFERENCE IS NULL AND period_name = :period;

Because the view already filters on MOVEMENT_STATUS and EDI_SENT_FLAG, queries generally need only supply period, organization, or reference predicates. The view should be treated as read-only; all maintenance of movement statistics occurs against MTL_MOVEMENT_STATISTICS.