Search Results effective_completion_date

Overview

APPS.AMW_ASSESSMENTS_ALL_VL is a public Oracle E-Business Suite view owned by the APPS schema and registered in FND Design Data under the application short name AMW, which corresponds to the Audit Management module within the Enterprise Risk and Compliance Management (ERM) product family. The view exposes information about Audit Assessments, presenting one row per assessment owned by an operating unit or security context. Its designation as an "_ALL_VL" view indicates that it presents all rows visible to the current security context while resolving translated, language-dependent columns from an underlying multilingual (ML) table pair — a base table and a translation table joined through a view-layer mechanism that presents values in the session's language.

In Oracle EBS 12.1.1 and 12.2.2, AMW_ASSESSMENTS_ALL_VL serves as the primary read interface for audit assessment headers. It is not an interface or open-interface view; it is a query-only presentation layer intended for custom reporting, extracts, and integration queries. Because it is a view and not a table, insert, update, and delete operations against it are not supported unless it is defined with an INSTEAD OF trigger, which audit assessment views are not. Oracle's documented guidance classifies it as a view "which may be useful for custom reporting or other data requirements."

Underlying Base Objects

The documented ETRM metadata for this object does not enumerate referenced base objects and lists no dependent tables explicitly. Based on the standard Oracle EBS naming conventions and the AMW schema design, AMW_ASSESSMENTS_ALL_VL is constructed over a base table named AMW_ASSESSMENTS (holding ASSESSMENT_ID, ASSESSMENT_TYPE_CODE, ASSESSMENT_OWNER_ID, the IES integration columns, ASSESSMENT_STATUS_CODE, and the WHO and descriptive flexfield columns) together with its associated translation table AMW_ASSESSMENTS_TL, which supplies language-dependent descriptive text such as assessment names or titles keyed by ASSESSMENT_ID and LANGUAGE. The "_ALL" suffix further implies that the view participates in Multi-Org or security-group filtering through SECURITY_GROUP_ID for hosted environments. The ROW_ID column, based on the ROWID pseudocolumn, provides a unique row locator that custom code can use to target a specific base-table row.

Key Columns

• ASSESSMENT_ID — Primary identifier for the assessment; the join key to child assessment detail, question, and response tables.
• ASSESSMENT_TYPE_CODE — A VARCHAR2(30) code classifying the assessment (for example, distinct audit assessment types configured through AMW lookups). This is the column most frequently searched by developers and reporting users.
• ASSESSMENT_OWNER_ID — Identifies the owner (typically a person or party) responsible for the assessment.
• IES_SURVEY_ID, IES_CYCLE_ID, IES_DEPLOYMENT_ID — Foreign references into the Oracle iLearning/IEC (IES) survey framework, linking the audit assessment to a deployed survey, its cycle, and its deployment instance.
• ASSESSMENT_STATUS_CODE — A VARCHAR2(30) code indicating the lifecycle state of the assessment.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, present for audit trail and incremental extract logic.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield structure and segment columns available for customer-defined assessment attributes.
• SECURITY_GROUP_ID — Used primarily in hosted (multi-tenant) deployments to partition data.

Common Use Cases and Queries

Typical use cases include custom audit assessment status reports, reconciliation of assessment records against deployed IES surveys, and integration extracts feeding data warehouses or external GRC systems. A common query filters by assessment type and status:

• SELECT assessment_id, assessment_type_code, assessment_status_code, assessment_owner_id, last_update_date FROM apps.amw_assessments_all_vl WHERE assessment_type_code = :p_type AND assessment_status_code = :p_status;
• SELECT a.assessment_id, a.ies_survey_id, a.ies_cycle_id, a.ies_deployment_id FROM apps.amw_assessments_all_vl a WHERE a.ies_survey_id IS NOT NULL;
• SELECT attribute_category, attribute1, attribute2 FROM apps.amw_assessments_all_vl WHERE assessment_id = :p_id;

Because the view lacks an INSTEAD OF trigger, all such queries are read-only. Callers should observe standard EBS security by ensuring the connecting user has appropriate AMW responsibility and data access, particularly in environments where SECURITY_GROUP_ID filtering applies.