Search Results amw_audit_procedure_interface

Overview

AMW_AUDIT_PROCEDURE_INTERFACE is a staging and interface table in the Oracle E-Business Suite Internal Controls Manager (AMW) module. It serves as the inbound landing area for audit task and audit procedure results uploaded through Oracle WebADI, the desktop-integrated spreadsheet loader used extensively across EBS for bulk data entry. Records deposited in this table are subsequently validated and transferred into the base AMW audit execution tables (procedures, procedures revisions, audit projects, and audit-plan associations). It exists in both Oracle EBS 12.1.1 and 12.2.2 with the same ownership and structure.

The documented schema is remarkably wide: 72 columns owned by the AMW schema. In addition to raw upload payload columns, the table carries the standard EBS who-columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and the flexible ATTRIBUTE_CATEGORY plus ATTRIBUTE1–ATTRIBUTE15 descriptive-flexfield block, indicating the table participates in standard EBS audit and DFF conventions.

From a Data Vault modeling perspective, the heuristic classification is standalone, mined from the foreign-key structure. That classification is a modeling suggestion only: despite several outbound foreign keys, the table functions primarily as a transient interface/landing object rather than a conformed hub, link, or satellite. Its rows are typically short-lived — created by the WebADI upload process and removed or superseded once the interface program promotes them to the base tables.

Key Information Stored

The 72 columns partition into three functional groups. The most important are:

• BATCH_ID — groups all rows belonging to one WebADI upload session; the natural handle for reconciliation and restart logic.
• AP_INTERFACE_ID — foreign key to AMW_AP_INTERFACE; the header-level interface record to which the detail row belongs.
• AUDIT_PROCEDURE_ID / AUDIT_PROCEDURE_REV_ID — surrogates linking the row to the audit procedure and its specific revision (AMW_AUDIT_PROCEDURES_B).
• AUDIT_PROJECT_ID — the engagement/audit project context (AMW_AUDIT_PROJECTS).
• ORGANIZATION_ID and CONTROL_ID — the org and control being tested (CONTROL_ID references CST_REVENUE_COGS_CONTROL), and AP_ASSOCIATION_ID links to AMW_AP_ASSOCIATIONS.
• TASK_ID / STEP_ID — identifies which procedure task and step the uploaded result applies to.
• AP_STATUS / STEP_STATUS / INTERFACE_STATUS / PROCESSED_FLAG / ERROR_FLAG — the interface control columns driving whether a row has been consumed, and whether it failed validation.
• DESIGN_EFFECTIVENESS / OP_EFFECTIVENESS / CONTROL_OPINION / CONTROL_OPINION_ID — the substantive conclusion data being uploaded.
• AP_EXECUTED_BY / AP_EXECUTED_ON / STEP_EXECUTED_BY / STEP_EXECUTED_ON — execution evidence (who performed and when).
• STEPS_ATTACHMENT_URL — pointer to supporting evidence attachments.

The metadata does not document a primary key constraint or unique index, so business-key candidates are inferred: the tuple of AUDIT_PROCEDURE_REV_ID, TASK_ID, STEP_ID, ORGANIZATION_ID, and BATCH_ID is the natural candidate for uniqueness. Attribution of surrogate-vs-business key here should be treated as a suggestion, not a documented fact.

Common Use Cases and Queries

Typical operational queries reconcile an upload batch, identify failed rows, or audit what was staged before promotion:

• Count staged vs. succeeded rows per batch:
  SELECT BATCH_ID, INTERFACE_STATUS, COUNT(*) FROM AMW.AMW_AUDIT_PROCEDURE_INTERFACE GROUP BY BATCH_ID, INTERFACE_STATUS;
• Find error rows pending correction: SELECT * FROM AMW.AMW_AUDIT_PROCEDURE_INTERFACE WHERE ERROR_FLAG = 'Y' OR PROCESSED_FLAG = 'N';
• Join to the procedure revision and project to report execution outcomes: join AUDIT_PROCEDURE_REV_ID to AMW_AUDIT_PROCEDURES_B and AUDIT_PROJECT_ID to AMW_AUDIT_PROJECTS, projecting DESIGN_EFFECTIVENESS, OP_EFFECTIVENESS, and CONTROL_OPINION.
• Trace a control's uploaded results: filter by CONTROL_ID and join CST_REVENUE_COGS_CONTROL for control names/descriptions.

Reporting use cases include upload throughput monitoring, error-rate trending by BATCH_ID, and audit-trail reconstruction of who executed which task/step and when.

Related Objects

• AMW_AP_INTERFACE — via AP_INTERFACE_ID (the parent interface header).
• AMW_AUDIT_PROCEDURES_B — via AUDIT_PROCEDURE_REV_ID (the procedure revision being satisfied).
• AMW_AUDIT_PROJECTS — via AUDIT_PROJECT_ID (the engagement).
• AMW_AP_ASSOCIATIONS — via AP_ASSOCIATION_ID (procedure-to-org/control association).
• CST_REVENUE_COGS_CONTROL — via CONTROL_ID (the tested control).

These five referenced tables define the interface's context, and the standard who-columns, DFF attributes, and control flags govern its lifecycle within the WebADI-to-AMW integration flow.