Search Results amw_latest_revisions_v




Overview
Explore in AI Chat

Overview

AMW_LATEST_REVISIONS_V is a PL/SQL view owned by the APPS schema within Oracle E-Business Suite. It belongs to the AMW product family, Internal Controls Manager, which provides the risk, control, and process library framework used for compliance and internal audit reporting across the enterprise. The view exposes the most current revision of each risk library process, filtering the underlying process definition table so that only active rows are returned. Its functional role is to give downstream reports, concurrent programs, and integration touchpoints a stable, de-duplicated source for "latest revision" process data without requiring each consumer to implement its own revision resolution logic.

The view name reflects this intent: it presents the latest revisions of risk library processes, carrying both descriptive metadata (name, description) and operational attributes such as approval status, control count, risk count, and ownership assignments.

Underlying Base Objects

Although the documented metadata lists no referenced base objects, the view text reveals that AMW_LATEST_REVISIONS_V is defined as a join across two AMW tables:

  • AMW_PROCESS — aliased as A, the driving table that stores each process revision, including identifiers, revision numbers, approval attributes, counts, ownership references, audit columns, and descriptive flexfield segments ATTRIBUTE1 through ATTRIBUTE15.
  • AMW_PROCESS_NAMES_TL — aliased as AP_TL, the translated name table that supplies DISPLAY_NAME and DESCRIPTION in the session language.

The join condition is AP_TL.PROCESS_REV_ID = A.PROCESS_REV_ID combined with a language constraint of AP_TL.LANGUAGE = USERENV('LANG'), and a row filter of A.END_DATE IS NULL. That END_DATE predicate is the mechanism that isolates the latest or currently valid revision of a process from historical revisions. Because the translated table is joined, the view honors the runtime language of the session rather than returning a hard-coded locale.

The view inherits standard WHO audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — along with the standard request and program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) and SECURITY_GROUP_ID / OBJECT_VERSION_NUMBER for multi-organization and optimistic locking support.

Key Columns

Common Use Cases and Queries

The view is typically consumed in compliance dashboards, process inventories, and integration extracts that require one row per active process. A basic inventory query is:

SELECT process_code, display_name, revision_number,
       approval_status, control_count, risk_count
FROM   apps.amw_latest_revisions_v
ORDER BY display_name;

To identify processes awaiting approval or lacking controls:

SELECT process_code, display_name, approval_status
FROM   apps.amw_latest_revisions_v
WHERE  approval_status NOT IN ('APPROVED')
   OR  (control_count = 0 AND significant_process_flag = 'Y');

For owner-based reporting, join on PROCESS_OWNER_ID to PER_ALL_PEOPLE_F. Because the view already filters to the latest revision in the session language, it is well suited for multilingual deployments and for downstream ETL where a single canonical process row per code is required.