Search Results amw_ctrl_interface

Overview

AMW_CTRL_INTERFACE is a staging and interface table owned by the AMW schema, which supports the Internal Controls Manager product in Oracle E-Business Suite. In the 12.1.1 and 12.2.2 documentation sets, this table is classified as part of an obsolete module, meaning the surrounding AMW functionality has been superseded in later release architectures. Its documented purpose is to hold Control data captured through WebADI upload, serving as the transient landing area where records are validated, transformed, and then transferred into the permanent AMW control repository.

Structurally, the table is a wide, flat interface structure with 169 documented physical columns. This width is the distinguishing characteristic of the object: rather than normalizing repetitive attributes into child tables, the interface design flattens 15 descriptive flexfield segments, 30 control objectives, 30 assertions, 30 components, and up to 30 control purposes into parallel columns. From a Data Vault modeling perspective, the mined foreign-key structure yields no meaningful parent-child links, and the metadata classifies this object heuristically as standalone. This classification should be treated as a modeling suggestion rather than a firm constraint: the table behaves as a staging satellite attached to the WebADI batch process, but it does not exhibit the parent-key dependencies that would define it as a formal hub or link.

Key Information Stored

The primary key is AMW_CTRL_INTERFACE_PK, defined on CTRL_INTERFACE_ID. A matching unique index, AMW_CTRL_INTERFACE_U1, is documented on the same column, so the surrogate identifier is the only unambiguous business-key candidate available. The most operationally significant columns are:

• CTRL_INTERFACE_ID — the surrogate primary key that uniquely identifies each staged control record.
• BATCH_ID — groups records belonging to a single WebADI upload run, enabling batch-level error review and reprocessing.
• INTERFACE_STATUS, PROCESS_FLAG, ERROR_FLAG — the lifecycle and error-state controls that gate record consumption.
• CONTROL_NAME, CONTROL_DESCRIPTION, CONTROL_SOURCE, CONTROL_SOURCE_TYPE, CONTROL_TYPE_CODE — the descriptive identity of the control being loaded.
• CONTROL_ORG_SYS_REF, CONTROL_LOCATION_CODE, CONTROL_APPLICATION_ID, CONTROL_JOB_ID — organizational and application context for the control.
• CONTROL_FREQUENCY, UOM_CODE, CONTROL_AUTOMATION_TYPE_CODE, CONTROL_APPROVAL_STATUS_CODE — execution and approval characteristics.
• PREVENTIVE_CONTROL, DETECTIVE_CONTROL, DISCLOSURE_CONTROL, KEY_MITIGATING — control-classification flags.
• CLASSIFICATION, REVISE_CTRL_FLAG — categorization and re-upload indicators.
• REQUESTOR_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard WHO audit columns identifying who submitted the upload and when.
• CONTROL_ATTRIBUTE1–15, CONTROL_OBJ1–30, CONTROL_ASSERT1–30, CONTROL_COMP1–30, CTRL_PURPOSE1–30 — the parallel flexfield and multi-value arrays that carry the bulk of the 169 columns.

Common Use Cases and Queries

The primary use case is diagnosing and monitoring WebADI control uploads. Support and implementation teams typically examine failed or pending rows within a batch, then correlate status codes with error flags to determine whether records require correction and reload. A representative query retrieves all batches still holding unprocessed or errored records:

• SELECT BATCH_ID, COUNT(*) FROM AMW_CTRL_INTERFACE WHERE NVL(PROCESS_FLAG,'N') = 'N' OR ERROR_FLAG = 'Y' GROUP BY BATCH_ID;
• SELECT CTRL_INTERFACE_ID, CONTROL_NAME, INTERFACE_STATUS, ERROR_FLAG FROM AMW_CTRL_INTERFACE WHERE BATCH_ID = :batch_id ORDER BY CTRL_INTERFACE_ID;
• SELECT CONTROL_TYPE_CODE, CONTROL_FREQUENCY, COUNT(*) FROM AMW_CTRL_INTERFACE GROUP BY CONTROL_TYPE_CODE, CONTROL_FREQUENCY;

Because the table is documented as not implemented in the database, these patterns apply primarily to historical or reference analysis rather than active production monitoring. Where the object is absent, equivalent reporting should target the corresponding live AMW control repository tables.

Related Objects

The mined foreign-key structure shows no enforced parent relationships, and the metadata describes this object as standalone. The most significant logical dependencies are therefore functional rather than declarative:

• AMW_CTRL_INTERFACE_PK / AMW_CTRL_INTERFACE_U1 — the constraint and unique index bound to CTRL_INTERFACE_ID.
• WebADI upload framework — the client process that populates rows keyed by BATCH_ID and REQUESTOR_ID.
• The target AMW control repository tables — the destination objects to which validated interface rows are transferred after error-free processing.
• AMW control lookup and flexfield definitions — the reference sources that constrain CONTROL_TYPE_CODE, INTERFACE_STATUS, and the CONTROL_ATTRIBUTE1–15 segments.

No foreign-key links to other AMW tables are documented, which is consistent with an interface staging object whose relationships are resolved procedurally during the upload rather than enforced by the schema.