Search Results assessment_status_code

Overview

AMW_ASSESSMENTS_ALL_VL is a multilingual (ML) validation view owned by the APPS schema in Oracle E-Business Suite. It is delivered as part of the AMW — Internal Controls Manager product, the module that underpins Oracle Audit Management and the broader governance, risk, and compliance (GRC) capability within EBS. The view presents a consolidated, translated view of audit assessment records, and is intended primarily as a reporting and integration access point rather than an operational data-entry object.

The "_ALL_VL" naming convention carries specific meaning in EBS. The "_ALL" fragment denotes that the view is not restricted by organizational or security context at the database level, while "_VL" indicates that it joins a base ("_B") table with a translation ("_TL") table and filters translated rows by the session language. This design allows consumers to query assessment data in the language of the current user without writing translation-aware SQL themselves.

Because the view exposes the assessment grain — one row per assessment — it serves as a convenient single source for reporting tools, concurrent programs, and interfaces that need assessment-level detail combined with its translated name and description.

Underlying Base Objects

The documented view text shows that AMW_ASSESSMENTS_ALL_VL is defined over two AMW base tables joined on ASSESSMENT_ID:

• AMW_ASSESSMENTS_B — the base (non-translated) table holding the assessment's operational attributes, including identifiers, type, owner, survey/cycle/deployment references, status, descriptive flexfield attributes, WHO audit columns, security group, object version number, and effective completion date.
• AMW_ASSESSMENTS_TL — the translation table supplying language-specific NAME and DESCRIPTION values.

The join condition is B.ASSESSMENT_ID = T.ASSESSMENT_ID AND T.LANGUAGE = USERENV('LANG'), which restricts the translated side to the language of the current session. This is the standard EBS ML join pattern and is the reason the view returns only one translation row per assessment at runtime.

Note that the ETRM metadata documents the base objects through the view text rather than as formally registered "referenced base objects," and no dependency list is separately published.

Key Columns

The view exposes the following significant columns:

• ROW_ID / ASSESSMENT_ID — the row identifier and the primary key of the assessment; ASSESSMENT_ID is the join key to the translation table.
• ASSESSMENT_TYPE_CODE, ASSESSMENT_OWNER_ID, ASSESSMENT_STATUS_CODE — the classification, responsible owner, and current workflow status of the assessment.
• IES_SURVEY_ID, IES_CYCLE_ID, IES_DEPLOYMENT_ID — references to the associated Oracle iLearning / survey deployment artifacts, linking the assessment to its instrument, cycle, and deployment.
• EFFECTIVE_COMPLETION_DATE — the date the assessment was effectively completed. This is the column most relevant to the searched term and is the authoritative completion timestamp carried on the base table.
• START_DATE, PERIOD_NAME — the assessment's start date and the accounting or audit period to which it belongs.
• NAME, DESCRIPTION — translated values drawn from AMW_ASSESSMENTS_TL.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 … ATTRIBUTE15 — the descriptive flexfield segments available for customer extension.
• SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER — used for multi-org security and optimistic locking.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical uses include assessment status reporting, completion tracking, integration feeds to external GRC systems, and reconciliation of assessments against their survey deployments.

Listing active assessments with their completion dates:

SELECT assessment_id, name, assessment_type_code,
       assessment_status_code, effective_completion_date
FROM   apps.amw_assessments_all_vl
WHERE  assessment_status_code = 'COMPLETED'
ORDER BY effective_completion_date DESC;

Counting assessments completed within a period:

SELECT period_name, COUNT(*) completed_count
FROM   apps.amw_assessments_all_vl
WHERE  effective_completion_date BETWEEN
         TO_DATE(:start_date,'YYYY-MM-DD')
         AND TO_DATE(:end_date,'YYYY-MM-DD')
GROUP BY period_name;

Joining to deployment and survey context:

SELECT a.assessment_id, a.name, a.ies_survey_id,
       a.ies_cycle_id, a.ies_deployment_id
FROM   apps.amw_assessments_all_vl a
WHERE  a.effective_completion_date IS NULL;

Queries should generally be issued against the APPS synonym, and consumers should be aware that the "_VL" filter returns translated values only for the session language, so reporting that requires all languages must query the translation table directly.