Search Results amw_key_acc_interface

Overview

AMW_KEY_ACC_INTERFACE is a transient staging table in the Oracle E-Business Suite Internal Controls Manager (AMW) product. It stores inbound records uploaded through WebADI or batch processing that define the association between a Process and a Key Account. The table acts as an intermediate holding area: rows are written by WebADI uploads or feeder processes, validated, and then reconciled into the permanent Process-to-Key-Account relationship tables. In Oracle EBS 12.1.1 and 12.2.2, this object resides in the AMW schema and is classified as VALID.

The ETRM metadata describes the object as standalone. Under a Data Vault modeling heuristic, this suggests it is best treated as a hub-like or staging construct rather than a fully integrated link or satellite. Its role is closer to an interface hub: it carries the business identifiers of the parent process, financial statement, financial item, and natural account, grouped by a batch and processed in a controlled lifecycle. The presence of PROCESSED_FLAG, ERROR_FLAG, and INTERFACE_STATUS confirms its staging nature.

Key Information Stored

The table contains 31 documented columns. The most significant are:

• KEY_ACC_INTERFACE_ID — surrogate primary key, enforced by index AMW_KEY_ACC_INTERFACE_PK and also the documented unique index AMW_KEY_ACC_INTERFACE_U1.
• BATCH_ID — groups rows belonging to a single WebADI upload or batch run.
• PROCESS_ID — identifies the AMW process to which the key account is being associated.
• FINANCIAL_STATEMENT_ID — the financial statement context for the association.
• FINANCIAL_ITEM_ID — the financial line item being linked.
• NATURAL_ACCOUNT_ID and NATURAL_ACCOUNT_ACC_ID — the natural account and its flexible-value account identifier used in the association.
• PROCESSED_FLAG — indicates whether the interface row has been successfully processed.
• ERROR_FLAG — flags rows that failed validation or reconciliation.
• INTERFACE_STATUS — overall status of the interface record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle descriptive flexfield columns for extensions.

The surrogate primary key KEY_ACC_INTERFACE_ID is the only documented unique business-key candidate; there is no separate natural-key unique index on the business combination of PROCESS_ID and account identifiers in the supplied metadata.

Common Use Cases and Queries

Typical usage focuses on monitoring upload progress, diagnosing errors, and reporting unprocessed associations.

To review failed rows for a batch:

• SELECT KEY_ACC_INTERFACE_ID, PROCESS_ID, NATURAL_ACCOUNT_ID, ERROR_FLAG, INTERFACE_STATUS FROM AMW.AMW_KEY_ACC_INTERFACE WHERE BATCH_ID = :batch AND ERROR_FLAG = 'Y';
• SELECT PROCESS_ID, COUNT(*) FROM AMW.AMW_KEY_ACC_INTERFACE WHERE PROCESSED_FLAG = 'N' GROUP BY PROCESS_ID;
• SELECT * FROM AMW.AMW_KEY_ACC_INTERFACE WHERE INTERFACE_STATUS = 'ERROR' ORDER BY CREATION_DATE DESC;

These queries support WebADI reconciliation, batch cleaning, and audit reporting. The audit and DFF columns also allow traceability of who uploaded each association and any custom extensions captured during upload.

Related Objects

Because the model is standalone, no foreign keys are documented. The most relevant related objects are:

• AMW_KEY_ACCOUNTS — the permanent Process-to-Key-Account association table, joined on PROCESS_ID and NATURAL_ACCOUNT_ID.
• AMW_PROCESSES — the process master, joined on PROCESS_ID.
• AMW_FINANCIAL_STATEMENTS — joined on FINANCIAL_STATEMENT_ID.
• AMW_FINANCIAL_ITEMS — joined on FINANCIAL_ITEM_ID.
• AMW_NATURAL_ACCOUNTS — joined on NATURAL_ACCOUNT_ID.
• WebADI / AMW integration programs — the upload and validation routines that write to and read from this interface table.

These joins mirror the parent-child relationships implied by the identifier columns and support migration and reconciliation between the interface and the permanent AMW tables.