Results for “ec_delivery_code”

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

Overview

IN_ADDR_MST is a table owned by the GMI schema (Process Manufacturing Inventory) in Oracle EBS 12.1.1 and 12.2.2. Its documented description identifies it as "EC Address Supplemental Details," holding Intrastat details relevant to a ship-to address. Intrastat is the European Union statistical reporting system for intra-community trade in goods, and this table stores the supplementary attributes required to satisfy those declarations at the address level. In practice, IN_ADDR_MST functions as a supplementary attribute store that extends the core address records used throughout Process Manufacturing shipping and distribution flows.

The documented physical schema for 12.2.2 records 16 columns with a single unique index, IN_ADDR_MST_PK, on ADDR_ID. The ETRM relationship data classifies this object heuristically as standalone in Data Vault terms, meaning no foreign-key links to other tables were mined from the FK structure. As a modeling suggestion, this suggests treating IN_ADDR_MST as a satellite-like store of descriptive address attributes keyed by an address identifier, rather than a hub or link that defines new business entities or relationships. The ADDR_ID column serves as the primary key and the singular business-key candidate, implying a one-row-per-address cardinality for the supplemental EC data.

Key Information Stored

The table's most significant columns fall into three functional groups — identity, Intrastat/regulatory attributes, and standard EBS audit/control columns.

• ADDR_ID — the surrogate primary key per IN_ADDR_MST_PK, linking the supplemental record to the underlying ship-to address. It is also the only documented unique-index (business-key candidate) column.
• VAT_REGN — the VAT registration number associated with the address, required for EC trade declarations.
• EC_COUNTRY_CODE and EC_REGION_CODE — the country and region codes used for Intrastat reporting and EC goods movement.
• EC_DELIVERY_CODE — the delivery term/condition code relevant to EC shipments.
• MOT_CODE — the mode-of-transport code, a mandatory Intrastat field describing how goods cross the border.
• PORT_CODE — the port or border crossing point associated with the movement.
• BRANCH_NO — branch or sub-entity indicator tied to the address registration.
• TEXT_CODE — a supplemental textual/classification code for the address record.
• TRANS_CNT — a transaction/version counter used for concurrency control.
• DELETE_MARK — the soft-delete flag indicating whether the supplemental record is logically active.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit columns capturing who created and last modified each row and when.

Common Use Cases and Queries

Typical usage centers on EU Intrastat reporting and EC shipment validation. Reporting analysts join IN_ADDR_MST to the primary address table on ADDR_ID to retrieve VAT registration and transport details per ship-to location. Reconciliation queries check for addresses missing Intrastat attributes, and validation scripts ensure mandatory fields such as MOT_CODE and EC_COUNTRY_CODE are populated before goods movement.

A representative query pattern retrieves active supplemental details for a given address:

• SELECT addr_id, vat_regn, ec_country_code, ec_region_code, mot_code, port_code FROM gmi.in_addr_mst WHERE addr_id = :p_addr_id AND delete_mark = 0;
• An exception report lists addresses lacking essential EC data: SELECT addr_id FROM gmi.in_addr_mst WHERE vat_regn IS NULL OR mot_code IS NULL;
• A data-aging report groups records by audit columns: SELECT last_updated_by, COUNT(*) FROM gmi.in_addr_mst GROUP BY last_updated_by;

Because ADDR_ID is unique, lookups return at most one supplemental row per address, simplifying join logic in queries and reporting extracts.

Related Objects

The metadata records no mined foreign-key relationships, classifying IN_ADDR_MST as standalone. The most significant related objects are therefore driven by the shared ADDR_ID identifier rather than declared FKs.

• Primary address master tables keyed by ADDR_ID (in the core EBS/Process Manufacturing address model), joined as address.addr_id = in_addr_mst.addr_id.
• Ship-to site tables that carry ADDR_ID references for order and delivery processing.
• Intrastat reporting views and concurrent programs that read VAT_REGN, MOT_CODE, and PORT_CODE.
• EC customer/supplier registration tables that share the VAT_REGN business attribute.
• Order and delivery transaction tables whose ship-to addresses resolve through the same ADDR_ID key.

Administrators should confirm any real-world join by verifying that the referenced objects expose a matching ADDR_ID column, since no enforced FK relationships are documented for this table.