Search Results audit_procedure_rev_num

Overview

AMW_AUDIT_PROCEDURES_VL is a validation view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMW – Internal Controls Manager product family. It returns descriptive and revision-level information about audit procedures, which are the reusable control-testing definitions that Internal Controls Manager uses to evaluate business processes and internal controls. As a "_VL" (validation list) view, it conforms to the Oracle Applications multi-language support (MLS) convention: it joins a base table to its translation table and restricts the translated rows to the session language. This design allows forms, LOVs, and reports to display the audit procedure name and description in the language of the current user without exposing untranslated rows.

In EBS 12.1.1 and 12.2.2 the object is documented as VALID, and its structure is consistent across both releases; 12.2.2 adds Online Patching (ADOP) awareness and, where applicable, editioning views over the underlying base table, but the logical column set exposed by AMW_AUDIT_PROCEDURES_VL remains unchanged. The view is query-only from a functional perspective and is most often consumed by AMW forms, region LOVs, and custom inquiry or audit-trail reporting rather than by transactional processing.

Underlying Base Objects

The view is defined over two documented AMW tables:

• AMW_AUDIT_PROCEDURES_B – the base table holding revision-level and administrative attributes such as surrogate keys, revision numbering, approval tracking, descriptive flexfields, and security grouping.
• AMW_AUDIT_PROCEDURES_TL – the translation table supplying the language-dependent NAME and DESCRIPTION columns.

The join condition is B.AUDIT_PROCEDURE_REV_ID = TL.AUDIT_PROCEDURE_REV_ID combined with TL.LANGUAGE = USERENV('LANG'). Because the join is on the revision identifier, each row in the view corresponds to a single audit procedure revision expressed in the user's session language. Only the columns required for display are projected; the translation table's own administrative columns are not exposed. Because the view is read-only, inserts and updates must be performed against the base and translation tables directly.

Key Columns

• AUDIT_PROCEDURE_ID – primary identifier of the audit procedure across all revisions.
• AUDIT_PROCEDURE_REV_ID – identifier of the specific revision; the join key to the TL table.
• AUDIT_PROCEDURE_REV_NUM – sequential revision number for the procedure.
• END_DATE – effective end date of the revision, supporting date-tracked history.
• APPROVAL_DATE and APPROVAL_STATUS – approval audit fields indicating when and in what state the revision was approved.
• CURR_APPROVED_FLAG and LATEST_REVISION_FLAG – flags identifying the currently approved revision and the most recent revision, respectively.
• NAME and DESCRIPTION – translated display values sourced from AMW_AUDIT_PROCEDURES_TL.
• PROJECT_ID, CLASSIFICATION, REQUESTOR_ID, and ORIG_SYSTEM_REFERENCE – classification and ownership context, enabling linkage to projects or external source systems.
• SECURITY_GROUP_ID and OBJECT_VERSION_NUMBER – multi-organization security and optimistic locking support.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 – descriptive flexfield segments available for customer extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – standard "WHO" audit columns.

Common Use Cases and Queries

Typical uses include LOV population on AMW audit procedure forms, reporting on the current approved revision of each procedure, and integration extracts that feed external GRC or audit tools. The following query lists the latest revision of every audit procedure for the current language:

SELECT audit_procedure_id,
       audit_procedure_rev_num,
       name,
       approval_status,
       approval_date
  FROM apps.amw_audit_procedures_vl
 WHERE latest_revision_flag = 'Y'
   AND curr_approved_flag   = 'Y'
 ORDER BY name;

To retrieve the full revision history of a specific procedure:

SELECT audit_procedure_rev_num,
       name,
       end_date,
       latest_revision_flag,
       approval_status
  FROM apps.amw_audit_procedures_vl
 WHERE audit_procedure_id = :procedure_id
 ORDER BY audit_procedure_rev_num DESC;

Because the view applies USERENV('LANG'), reports automatically return translated text for the session language; no additional language predicate is required. Custom queries should filter on LATEST_REVISION_FLAG and CURR_APPROVED_FLAG when only active, approved definitions are needed, and should join on AUDIT_PROCEDURE_REV_ID when linking to revision-dependent child records.