Search Results amw_ctrl_interface_n1

Overview

AMW.AMW_CTRL_INTERFACE is a WebADI (Web Applications Desktop Integrator) interface staging table owned by the AMW schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as the inbound landing zone for Control records uploaded through WebADI spreadsheets, where users populate controls in a downloadable workbook and submit them for validation and eventual import into the Oracle Internal Controls Manager (OICM) base tables. The table resides in the APPS_TS_INTERFACE tablespace with a PCTFREE of 10, reflecting its role as a transient, high-churn staging area rather than a transactional store of record.

The documented physical schema lists 169 columns, with a primary key of AMW_CTRL_INTERFACE_PK on CTRL_INTERFACE_ID and a unique index AMW_CTRL_INTERFACE_U1 also on CTRL_INTERFACE_ID. A nonunique index AMW_CTRL_INTERFACE_N1 covers BATCH_ID and CREATED_BY, supporting batch-level retrieval and per-user upload tracking. The metadata classifies the object as standalone under heuristic Data Vault mining, meaning no foreign-key dependencies were detected. From a modeling perspective this suggests a staging or interface satellite pattern rather than a hub or link; the surrogate key CTRL_INTERFACE_ID identifies individual staged rows, while BATCH_ID groups them into the logical upload unit.

Key Information Stored

The columns most relevant to day-to-day use include the following. CTRL_INTERFACE_ID is the surrogate primary key uniquely identifying each staged control row. BATCH_ID is the business-key grouping identifier for a WebADI upload batch and, together with CREATED_BY, forms the access path of the N1 index. CONTROL_NAME, CONTROL_DESCRIPTION (VARCHAR2 4000), CONTROL_TYPE_CODE, CONTROL_AUTOMATION_TYPE_CODE, CONTROL_APPROVAL_STATUS_CODE, and CONTROL_SOURCE/CONTROL_SOURCE_TYPE capture the core attributes of the control being uploaded.

The descriptive flexfield segments CONTROL_ATTRIBUTE_CATEGORY, CONTROL_ATTRIBUTE1 through CONTROL_ATTRIBUTE15 provide the DFF structure that the user searched for under "control_attribute1". The presence of CONTROL_ATTRIBUTE1 as a VARCHAR2(150) column indicates the table supports the Control DFF context; the value of CONTROL_ATTRIBUTE_CATEGORY determines which segment the attribute holds.

Interface processing state is tracked by INTERFACE_STATUS, ERROR_FLAG, PROCESS_FLAG, and REQUESTOR_ID. Additional control metadata appears in CONTROL_FREQUENCY, UOM_CODE, PREVENTIVE_CONTROL, DETECTIVE_CONTROL, DISCLOSURE_CONTROL, KEY_MITIGATING, CONTROL_APPLICATION_ID, and the CTRL_PURPOSE1–CTRL_PURPOSE30 set. Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) are standard who-columns.

Common Use Cases and Queries

Typical scenarios center on validating WebADI submissions and troubleshooting import failures. A common pattern isolates the failing rows within a batch:

SELECT CTRL_INTERFACE_ID, CONTROL_NAME, INTERFACE_STATUS, ERROR_FLAG
FROM   AMW.AMW_CTRL_INTERFACE
WHERE  BATCH_ID = :batch_id
AND    INTERFACE_STATUS <> 'PROCESSED';

Auditors and control owners frequently query the DFF attributes to reconcile uploaded values:

SELECT CONTROL_ATTRIBUTE_CATEGORY, CONTROL_ATTRIBUTE1
FROM   AMW.AMW_CTRL_INTERFACE
WHERE  CONTROL_ATTRIBUTE1 IS NOT NULL
AND    CREATED_BY = :user_id;

Reporting use cases include batch throughput monitoring (count of rows per BATCH_ID and status) and per-requestor validation summaries. Because this is an interface table, stale rows may require purging after successful import.

Related Objects

The metadata records no FK dependencies, so relating this object relies on shared business keys and the AMW control model. The primary relationships are: AMW_CTRL_INTERFACE_U1 and AMW_CTRL_INTERFACE_PK (both on CTRL_INTERFACE_ID); the staging table consumes records destined for the OICM control base tables in the AMW schema (for example, AMW_CONTROLS and its descriptive flexfield tables, joined via CONTROL_NAME or control identifier). WebADI itself links here through the FND Design Data entry AMW.AMW_CTRL_INTERFACE, and the standard who-columns connect back to FND_USER through CREATED_BY and LAST_UPDATED_BY. Batch tracking aligns with the uploaded WebADI workbook identified by BATCH_ID. When building custom reports, join interface rows to the target control tables and to FND_LOOKUPS on CONTROL_APPROVAL_STATUS_CODE, CONTROL_TYPE_CODE, and CONTROL_AUTOMATION_TYPE_CODE to resolve lookup meanings.