Search Results amw_violations_v

Overview

AMW_VIOLATIONS_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Internal Controls Manager (AMW) module. Its documented purpose is to return information about Segregation of Duties (SoD) constraint violations. In an EBS environment such as 12.1.1 or 12.2.2, Segregation of Duties enforcement is a core function of Internal Controls Manager: the application evaluates responsibility, role, and user assignments against configured constraints and records any breach as a violation. This view consolidates the essential attributes of those violations into a single, denormalized result set, making it suitable for reporting, inquiry screens, and downstream integration.

Rather than storing data itself, the view joins transactional violation records with constraint definitions, employee identities, and lookup values. This design shields reporting consumers from the underlying join logic and lookup decoding required to present a violation in human-readable form, which is the primary value the view delivers.

Underlying Base Objects

According to the documented view text, AMW_VIOLATIONS_V is defined over the following objects:

• AMW_VIOLATIONS (aliased V) — the primary fact object holding the recorded violation rows, including the violation identifier, status, request context, and the violated constraint revision.
• AMW_CONSTRAINTS_VL (aliased C) — the constraint definition view, supplying the constraint name and type code. It is joined to AMW_VIOLATIONS on CONSTRAINT_REV_ID.
• AMW_EMPLOYEES_CURRENT_V (aliased PARTIES) — resolves the requesting party to a full name.
• AMW_LOOKUPS (aliased AMWLKUPV and AMWLKUPC) — joined twice: once against lookup type AMW_VIOLATION_STATUS to decode the violation status, and once against lookup type AMW_CONSTRAINT_TYPE to decode the constraint type.

The join to AMW_EMPLOYEES_CURRENT_V is an outer join (indicated by the (+) operator on REQUESTED_BY_ID), so a violation is still returned even when the requesting party cannot be resolved. The lookup joins are inner joins, meaning a violation surfaces only when valid lookup codes exist for its status and constraint type.

Key Columns

• CONSTRAINT_NAME — the name of the Segregation of Duties constraint that was violated.
• CONSTRAINT_REV_ID — the unique identifier of the constraint revision, useful for linking back to the constraint definition.
• REQUEST_ID / REQUEST_DATE — the request context under which the violation was detected and the date it was recorded.
• VIOLATOR_NUM — the numeric identifier of the violating party.
• REQUESTED_BY_ID / REQUESTED_BY_NAME — the party identifier and resolved full name of the requester. REQUESTED_BY_NAME originates from PARTIES.FULL_NAME.
• STATUS_CODE / STATUS_MEANING — the violation status code and its decoded lookup meaning from the AMW_VIOLATION_STATUS lookup type.
• TYPE_DESCRIPTION — the decoded constraint type description from AMW_CONSTRAINT_TYPE.
• VIOLATION_ID — the primary identifier for the individual violation record.

Common Use Cases and Queries

The view is typically used for SoD violation reporting, audit review, and dashboards that surface outstanding or resolved breaches. A common requirement is extracting open violations with their constraint and status descriptions:

SELECT constraint_name,
       violation_id,
       requested_by_name,
       status_meaning,
       type_description,
       request_date
FROM   apps.amw_violations_v
WHERE  status_code = 'OPEN'
ORDER  BY request_date DESC;

Auditors frequently summarize violations by constraint to identify systemic control weaknesses:

SELECT constraint_name,
       type_description,
       COUNT(*) AS violation_count
FROM   apps.amw_violations_v
GROUP  BY constraint_name, type_description
ORDER  BY violation_count DESC;

Because the view already decodes status and type through AMW_LOOKUPS, and resolves the requester through AMW_EMPLOYEES_CURRENT_V, consumers avoid replicating lookup joins. It should be queried with the APPS schema prefix and, for performance, filtered by request_date or constraint_name where possible.