Search Results amw_process_organization_u4

Overview

AMW_PROCESS_ORGANIZATION is a transactional table owned by the AMW schema within Oracle E-Business Suite, and it forms part of the Application Management Workbench / Enterprise Governance, Risk, and Compliance (EGRC) data model. The table stores the association between business processes and the organizations that own, execute, or are otherwise responsible for them. Each row represents a process-to-organization assignment at a specific revision, together with the compliance posture of that assignment: approval status, certification status, audit status, control counts, and risk counts. Because process ownership and risk assessment in EGRC are organization-specific, this table is central to scoping which processes are relevant for each operating unit, legal entity, or business group recorded in HR_ALL_ORGANIZATION_UNITS.

Based on the foreign key structure and the presence of both surrogate identifiers and a rich set of descriptive and status attributes, the heuristic Data Vault classification for this object is satellite-leaning. It behaves less like a pure hub or link and more like a descriptive satellite attached to a process–organization link, carrying revision, approval, certification, audit, and status history. This classification should be treated as a modeling suggestion rather than a firm architectural dictum.

Key Information Stored

The table has 63 documented columns. The most important are:

• PROCESS_ORGANIZATION_ID — the surrogate unique identifier for the association record; it is the leading column of unique index AMW_PROCESS_ORGANIZATION_U4 when combined with REVISION_NUMBER.
• PROCESS_ID — the identifier of the associated process; part of business-key candidate AMW_PROCESS_ORGANIZATION_U2 along with ORGANIZATION_ID and REVISION_NUMBER.
• ORGANIZATION_ID — the organization with which the process is associated; part of U2 and of the alternate business-key candidate AMW_PROCESS_ORGANIZATION_U5. It carries a foreign key to HR_ALL_ORGANIZATION_UNITS.
• REVISION_NUMBER — the revision of the process–organization association, allowing multiple historical versions per process and organization combination.
• PROCESS_ORG_REV_ID — a revision-level identifier that is the sole column of unique index AMW_PROCESS_ORGANIZATION_U1.
• STANDARD_PROCESS_FLAG and STANDARD_VARIATION — flags indicating whether the process is a standard process and whether it represents a standard variation; used in nonunique indexes N2 and N4.
• APPROVAL_STATUS, CERTIFICATION_STATUS, and LAST_AUDIT_STATUS — workflow and compliance state indicators driving EGRC certification and audit cycles.
• CONTROL_COUNT and RISK_COUNT — cached counts of controls and risks associated with the organization; RISK_COUNT_LATEST and CONTROL_COUNT_LATEST hold the most recent counts.
• APPROVAL_DATE, APPROVAL_END_DATE, START_DATE, END_DATE, and DELETION_DATE — effective dating and lifecycle bounds used heavily by the unique and nonunique indexes.
• TOP_PROCESS_ID — the top-level process identifier in the process hierarchy.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant and security-group scoping.
• PROCESS_TYPE, PROCESS_CATEGORY_CODE, and CLASSIFICATION — classification attributes for reporting and segmentation.
• LAST_CERTIFICATION_DATE, LAST_AUDIT_DATE, and NEXT_AUDIT_DATE — scheduling columns for audit and certification planning.
• Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and the 15 ATTRIBUTE flex columns for extensibility.

Common Use Cases and Queries

The primary reporting use case is identifying which processes apply to a given organization and their current compliance state. A representative query joins to HR_ALL_ORGANIZATION_UNITS and filters by active dates:

SELECT p.PROCESS_ORGANIZATION_ID, p.PROCESS_ID, p.REVISION_NUMBER,
       p.APPROVAL_STATUS, p.CERTIFICATION_STATUS, p.RISK_COUNT, p.CONTROL_COUNT
FROM   AMW.AMW_PROCESS_ORGANIZATION p
WHERE  p.ORGANIZATION_ID = :org_id
AND    TRUNC(SYSDATE) BETWEEN p.START_DATE AND NVL(p.END_DATE, SYSDATE)
AND    p.DELETION_DATE IS NULL;

Other common patterns include retrieving the latest revision per process/organization pair, extracting standard processes using STANDARD_PROCESS_FLAG, generating audit schedules from NEXT_AUDIT_DATE, and reconciling cached risk and control counts against the underlying risk and control tables. Query predicates should align with the existing indexes: U1 on PROCESS_ORG_REV_ID, U2 on (PROCESS_ID, ORGANIZATION_ID, REVISION_NUMBER), U4 on (PROCESS_ORGANIZATION_ID, REVISION_NUMBER), U5 on (ORGANIZATION_ID, PROCESS_ID, END_DATE, DELETION_DATE, APPROVAL_DATE, APPROVAL_END_DATE), and nonunique indexes N1 (PROCESS_ID, END_DATE, DELETION_DATE, APPROVAL_DATE), N2 (ORGANIZATION_ID, PROCESS_ID, STANDARD_PROCESS_FLAG, END_DATE), and N4 (STANDARD_VARIATION, STANDARD_PROCESS_FLAG, ORGANIZATION_ID).

Related Objects

• HR_ALL_ORGANIZATION_UNITS — joined on AMW_PROCESS_ORGANIZATION.ORGANIZATION_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID, providing security scoping.
• AMW_PROCESSES (or the corresponding process definition table) — joined on PROCESS_ID to obtain process names and hierarchy.
• AMW_REVISIONS / revision tables — joined on REVISION_NUMBER or PROCESS_ORG_REV_ID to resolve revision metadata.
• Risk and control assignment tables — used to validate RISK_COUNT and CONTROL_COUNT against actual associated records.
• FND_APPLICATION and FND_PROGRAM — referenced via PROGRAM_APPLICATION_ID and PROGRAM_ID for concurrent program auditing.

Because it carries effective dates, approval states, and organization scoping, AMW_PROCESS_ORGANIZATION is a foundational table for EGRC compliance reporting, audit scheduling, and process ownership analysis across Oracle EBS 12.1.1 and 12.2.2.