Search Results ego_aml_intf_u1

Overview

EGO.EGO_AML_INTF is an open interface table in the EGO (Enterprise Global Order / Product Information Management) schema, residing in the APPS_TS_INTERFACE tablespace. Its documented purpose is to store interface records for AML — Approved Manufacturer List — processing in Oracle E-Business Suite 12.1.1 and 12.2.2. It is a critical staging vehicle that lets external systems or bulk-load programs insert manufacturer-part approval data, which is subsequently validated and consumed by AML concurrent programs that write into the production AML tables (such as EGO_MTL_SY_ITEMS_AML_INTF and its downstream base tables).

From a Data Vault modeling perspective, this table does not behave as a classic hub, link, or satellite. The metadata-driven heuristic classifies it as a standalone object, largely because the only documented foreign-key relationship (MANUFACTURER_ID to MTL_MANUFACTURERS) is a transactional reference rather than a full dimensional dependency chain. The recommended interpretation is that EGO_AML_INTF functions as a raw staging/staging-hub artifact: TRANSACTION_ID acts as the surrogate key and it accumulates pre-validated reference attributes destined for AML business entities.

Key Information Stored

The most important columns fall into three groups:

• Transaction control: TRANSACTION_ID (the unique key, backed by unique index EGO_AML_INTF_U1) identifies each record. DATA_SET_ID groups records processed together (indexed by EGO_AML_INTF_N1). REQUEST_ID ties the row to the concurrent request that loaded it. PROCESS_FLAG indicates the processing status, and TRANSACTION_TYPE records the intended action (e.g., add, update, delete) on production AML data.
• Item and organization context: INVENTORY_ITEM_ID (FK to MTL_SYSTEM_ITEMS_B), ITEM_NUMBER (concatenated segments from MTL_SYSTEM_ITEMS_B_KFV), ORGANIZATION_ID (FK to MTL_PARAMETERS), and ORGANIZATION_CODE.
• Manufacturer and part attributes: MANUFACTURER_ID (documented FK to MTL_MANUFACTURERS), MANUFACTURER_NAME, MFG_PART_NUM, DESCRIPTION, MRP_PLANNING_CODE, START_DATE, and END_DATE. Status information is captured by FIRST_ARTICLE_STATUS with its meaning column and APPROVAL_STATUS with its meaning column.

The surrogate/business primary key is TRANSACTION_ID (unique index EGO_AML_INTF_U1); DATA_SET_ID and MANUFACTURER_ID are documented non-unique supporting relationships. Standard EBS audit and descriptive-flexfield columns (CREATED_BY, LAST_UPDATE_DATE, ATTRIBUTE1–15) are also present but are secondary to the functional columns above.

Common Use Cases and Queries

Typical scenarios include bulk loading approved manufacturer lists from external PLM or partner systems, reprocessing failed AML interface rows, and reporting on approval status by item or manufacturer.

• Load validation: SELECT COUNT(*), SUM(DECODE(PROCESS_FLAG,1,1,0)) FROM EGO.EGO_AML_INTF WHERE REQUEST_ID = :p_request_id;
• Pending records for a data set: SELECT TRANSACTION_ID, INVENTORY_ITEM_ID, MFG_PART_NUM, APPROVAL_STATUS FROM EGO.EGO_AML_INTF WHERE DATA_SET_ID = :p_data_set AND PROCESS_FLAG = 1;
• Manufacturer-part reporting: join to MTL_MANUFACTURERS on MANUFACTURER_ID and to MTL_SYSTEM_ITEMS_B on INVENTORY_ITEM_ID to resolve names and segments.

Related Objects

• MTL_MANUFACTURERS — joined via EGO_AML_INTF.MANUFACTURER_ID (documented FK).
• MTL_SYSTEM_ITEMS_B — referenced by INVENTORY_ITEM_ID.
• MTL_SYSTEM_ITEMS_B_KFV — source of ITEM_NUMBER concatenated segments.
• MTL_PARAMETERS — referenced by ORGANIZATION_ID, supplies ORGANIZATION_CODE.
• FND_LOOKUP_VALUES — supplies meaning values for FIRST_ARTICLE_STATUS and APPROVAL_STATUS.
• EGO_MTL_SY_ITEMS_AML_INTF / corresponding AML base tables — downstream production targets populated by the AML concurrent program that reads this table.
• FND_CONCURRENT_REQUESTS — related through REQUEST_ID for loader auditing.