Search Results amw_latest_rev_org_v

Overview

AMW_LATEST_REV_ORG_V is a reporting view belonging to the Oracle E-Business Suite product AMW – Internal Controls Manager. The Internal Controls Manager module is documented as obsolete in the ETRM metadata, meaning the object is retained for backward compatibility and reference rather than being an actively developed or newly provisioned component in Oracle EBS 12.1.1 or 12.2.2. The view presents all the latest revisions of organization-level processes. In practice, it exposes the most current revision of each process that has been associated with a given organization, providing a flattened, current-state perspective across a set of organization process definitions.

Its role in EBS reporting and integration is that of a denormalized read layer. Rather than requiring report developers or integration routines to resolve revision history and language translation manually, the view filters the organization process data so that only the open-ended (latest) revision rows are visible, and it joins translated process name and description text for the session language. This makes it convenient for dashboards, extracts, audit reporting, and downstream integrations that need a single row per current organization process revision.

Underlying Base Objects

The ETRM metadata documents no base objects for this view, and the recorded implementation note states that the view is not implemented in the reference database. The view text, however, identifies its two source objects explicitly:

• AMW_PROCESS_ORGANIZATION – aliased as A; supplies the process revision, organization, status, date, and classification attributes.
• AMW_PROCESS_NAMES_TL – aliased as AP_TL; supplies the translated DISPLAY_NAME and DESCRIPTION columns.

The join is established on AP_TL.PROCESS_REV_ID = A.RL_PROCESS_REV_ID, restricted additionally by AP_TL.LANGUAGE = USERENV('LANG'), so only the translated name rows matching the session language are returned. The defining predicate A.END_DATE IS NULL is what isolates the latest revision, since superseded revisions carry an end date. Consequently, the view is effectively a filtered, translated projection over AMW_PROCESS_ORGANIZATION rather than an aggregation.

Key Columns

• PROCESS_ORG_REV_ID – primary identifier of the organization process revision row.
• PROCESS_ID, ORGANIZATION_ID, RL_PROCESS_REV_ID, PROCESS_ORGANIZATION_ID – keys linking the row to the process, the owning organization, and the underlying revision.
• DISPLAY_NAME, DESCRIPTION – translated text sourced from AMW_PROCESS_NAMES_TL.
• REVISION_NUMBER, START_DATE, END_DATE – revision identity and validity window; END_DATE is null for current rows.
• APPROVAL_STATUS, APPROVAL_DATE, APPROVAL_END_DATE, CERTIFICATION_STATUS, LAST_CERTIFICATION_DATE – workflow and certification state of the revision.
• LAST_AUDIT_STATUS, LAST_AUDIT_DATE, NEXT_AUDIT_DATE – audit tracking attributes.
• PROCESS_TYPE, PROCESS_CATEGORY_CODE, PROCESS_CATEGORY, CONTROL_ACTIVITY_TYPE, STANDARD_VARIATION – classification columns.
• STANDARD_PROCESS_FLAG, SIGNIFICANT_PROCESS_FLAG – yes/no indicators flagging standard and significant processes.
• CONTROL_COUNT, RISK_COUNT – pre-computed counts of associated controls and risks.
• DELETION_DATE – soft-deletion marker.

Common Use Cases and Queries

Typical scenarios include listing current processes for an organization, identifying significant processes lacking certification, and extracting control/risk counts for audit readiness reporting. Because the view already restricts to open-ended revisions and the session language, queries remain simple.

• List current processes for an organization:
  SELECT PROCESS_ORG_REV_ID, DISPLAY_NAME, APPROVAL_STATUS, CERTIFICATION_STATUS FROM AMW_LATEST_REV_ORG_V WHERE ORGANIZATION_ID = :org_id ORDER BY DISPLAY_NAME;
• Find significant processes not certified:
  SELECT PROCESS_ID, DISPLAY_NAME, LAST_CERTIFICATION_DATE FROM AMW_LATEST_REV_ORG_V WHERE SIGNIFICANT_PROCESS_FLAG = 'Y' AND CERTIFICATION_STATUS <> 'CERTIFIED';
• Summarize control and risk exposure:
  SELECT PROCESS_CATEGORY, COUNT(*) processes, SUM(CONTROL_COUNT) controls, SUM(RISK_COUNT) risks FROM AMW_LATEST_REV_ORG_V GROUP BY PROCESS_CATEGORY;

Because AMW is marked obsolete, consumers should confirm availability in the target instance before relying on the view.