Search Results amw_process_u3

Overview

AMW.AMW_PROCESS is a transactional table within the Oracle E-Business Suite Application Management Workspace (AMW) schema. It stores master and revision-level information about internal control processes managed through Oracle's Enterprise Risk and Compliance (ETRM) functionality. Each row represents a distinct process definition or revision, capturing ownership, approval state, certification state, categorization, and aggregated control and risk counts used for governance reporting.

The table resides in the APPS_TS_TX_DATA tablespace and is classified as VALID in the ETRM 12.1.1 and 12.2.2 documentation. Based on heuristic Data Vault modeling analysis derived from foreign-key structure, AMW_PROCESS is best treated as a standalone object, though it functionally behaves as a hub with satellite-style attributes attached. It is not a link table; no junction relationships are implied by its key structure.

Key Information Stored

The surrogate primary key is PROCESS_REV_ID, enforced by the unique index AMW_PROCESS_U1. Two additional unique indexes represent business-key candidates: AMW_PROCESS_U2 on (PROCESS_ID, REVISION_NUMBER) and AMW_PROCESS_U3 on (PROCESS_CODE, REVISION_NUMBER). The user-searched index AMW_PROCESS_U3 is therefore the canonical business-key lookup for resolving a process by its code and revision.

• PROCESS_REV_ID — Surrogate revision identifier and primary key.
• PROCESS_ID — Stable identifier shared across revisions of the same process.
• PROCESS_CODE and REVISION_NUMBER — Business-key pair used by AMW_PROCESS_U3.
• NAME — Human-readable process name.
• PROCESS_CATEGORY, PROCESS_TYPE, CLASSIFICATION — Grouping and taxonomy attributes.
• APPROVAL_STATUS, CERTIFICATION_STATUS — Workflow state indicators.
• PROCESS_OWNER_ID, FINANCE_OWNER_ID, APPLICATION_OWNER_ID — Ownership assignments.
• CONTROL_COUNT, RISK_COUNT, CONTROL_COUNT_LATEST, RISK_COUNT_LATEST — Aggregated governance metrics.
• START_DATE, END_DATE, APPROVAL_DATE, APPROVAL_END_DATE, DELETION_DATE — Lifecycle timestamps.
• SECURITY_GROUP_ID — Row-level security grouping, referencing FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — Optimistic locking control.
• REQUEST_ID — Populated by the Java concurrent program that uploads processes.

Common Use Cases and Queries

Typical reporting scenarios include listing active processes, identifying owners, tracking certification status, and auditing process revisions. A common query resolves the current revision of a process:

SELECT process_id, process_code, revision_number, name, approval_status
FROM amw.amw_process
WHERE process_code = :code
ORDER BY revision_number DESC;

Aggregation queries summarize the control and risk footprint per category or owner:

SELECT process_category, SUM(control_count_latest) control_total,
      SUM(risk_count_latest) risk_total
FROM amw.amw_process
WHERE deletion_date IS NULL
GROUP BY process_category;

Lifecycle and data-quality checks leverage the date columns and status flags. Security-aware queries filter by SECURITY_GROUP_ID to enforce ETRM's row-level access model.

Related Objects

• FND_SECURITY_GROUPS — Joined via AMW_PROCESS.SECURITY_GROUP_ID for row-level security enforcement.
• AMW_PROCESS_CONTROLS — Control-to-process associations linked by PROCESS_ID / PROCESS_REV_ID.
• AMW_PROCESS_RISKS — Risk-to-process associations sharing the same process keys.
• AMW_PROCESS_ORGS — Org-unit scope for each process revision.
• FND_USER — Resolves PROCESS_OWNER_ID, FINANCE_OWNER_ID, and APPLICATION_OWNER_ID to user identities.
• FND_CONCURRENT_REQUESTS — Correlates REQUEST_ID with the upload program execution.
• FND_APPLICATION — Resolves PROGRAM_APPLICATION_ID for the concurrent program context.

Together these objects support end-to-end ETRM process governance reporting across revisions, owners, controls, and risks.