Results for “amw_audit_procedures_b”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMW_AUDIT_PROCEDURES_B is the base (non-translatable) table in the AMW schema of Oracle E-Business Suite, belonging to the Internal Controls Manager product. It holds the master record for every audit procedure defined within the application, storing the revision-controlled definition of each procedure along with its approval state, security scope, and descriptive reference data. The table is the anchor of the audit procedure entity: it is the parent of the translation table AMW_AUDIT_PROCEDURES_TL and of the association and execution tables that link procedures to audits, risks, and controls.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this object as hub-leaning. In practice, AMW_AUDIT_PROCEDURES_B behaves as the central hub for the audit procedure business key, with revision, approval, and descriptive attributes layered onto it. Designers building a dimensional or Data Vault representation of Internal Controls Manager data should treat the procedure identifier as the durable business key and model revision- and status-level detail as dependent satellite content.

Key Information Stored

The table carries 35 documented columns in the ETRM 12.1.1 physical schema. The most significant are:

• AUDIT_PROCEDURE_REV_ID — the revision-level surrogate identifier and the single column of the primary key constraint AMW_AUDIT_PROCEDURES_B_PK.
• AUDIT_PROCEDURE_ID — the procedure-level identifier shared across revisions; together with AUDIT_PROCEDURE_REV_ID it forms the unique index AMW_AUDIT_PROCEDURES_B_U2, the closest documented business-key candidate.
• AUDIT_PROCEDURE_REV_NUM — the revision number of the procedure record.
• APPROVAL_DATE, APPROVAL_STATUS, and CURR_APPROVED_FLAG — approval lifecycle data indicating when and whether the revision was approved and is the currently effective version.
• LATEST_REVISION_FLAG — identifies the most recent revision of a procedure.
• END_DATE — the date through which the revision remains effective.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS that scopes the record for access control and reporting.
• ORIG_SYSTEM_REFERENCE — the reference to the originating system for records created through interfaces.
• REQUESTOR_ID — the user or role associated with the procedure request.
• PROJECT_ID — the project context associated with the procedure.
• CLASSIFICATION — the classification category assigned to the procedure.
• OBJECT_VERSION_NUMBER — the row-versioning column used by the framework for optimistic locking.
• Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and the 15 ATTRIBUTE/ATTRIBUTE_CATEGORY flex columns (ATTRIBUTE1 through ATTRIBUTE15) provide lineage and extensibility.

Descriptive, translatable text is not held here; it resides in AMW_AUDIT_PROCEDURES_TL, keyed by AUDIT_PROCEDURE_REV_ID.

Common Use Cases and Queries

Reporting and integration scenarios typically begin with constraint-aware queries that pin the desired revision. A current-revision report joins base and translation rows and filters on the latest or currently approved flag:

• SELECT b.audit_procedure_id, b.audit_procedure_rev_num, t.name, b.approval_status FROM amw_audit_procedures_b b, amw_audit_procedures_tl t WHERE b.audit_procedure_rev_id = t.audit_procedure_rev_id AND b.latest_revision_flag = 'Y' AND t.language = USERENV('LANG')
• Listing pending approvals: filter AMW_AUDIT_PROCEDURES_B by APPROVAL_STATUS and order by APPROVAL_DATE.
• Security-scoped extracts: constrain SECURITY_GROUP_ID to the active responsibility's group.
• Interface reconciliation: match ORIG_SYSTEM_REFERENCE against AMW_AUDIT_PROCEDURE_INTERFACE rows to confirm staging records loaded into the base table.
• Execution coverage: join to AMW_AP_EXECUTIONS on AUDIT_PROCEDURE_REV_ID to report procedures never executed.

Because historical revisions persist, reports that ignore LATEST_REVISION_FLAG or END_DATE will double-count procedures.

Related Objects

The following objects depend on or reference AMW_AUDIT_PROCEDURES_B through documented key relationships:

• AMW_AUDIT_PROCEDURES_TL — translation table; joined on AUDIT_PROCEDURE_REV_ID to retrieve name and description text.
• AMW_AUDIT_PROCEDURE_INTERFACE — interface/staging table keyed by AUDIT_PROCEDURE_REV_ID for inbound procedure data.
• AMW_AP_ASSOCIATIONS — maps audit procedures to related objects using AUDIT_PROCEDURE_REV_ID.
• AMW_AP_EXECUTIONS — records execution instances of a procedure via AUDIT_PROCEDURE_REV_ID.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID to enforce row-level security.

Collectively these relationships establish AMW_AUDIT_PROCEDURES_B as the hub through which audit procedures are defined, translated, associated, and executed within Internal Controls Manager.