Search Results amw_assessments_b




Overview
Explore in AI Chat

Overview

AMW_ASSESSMENTS_B is a core transactional table within the Oracle E-Business Suite Internal Controls Manager (AMW) module. It stores the header-level definition and lifecycle information for assessments, which are structured evaluation activities used to test, document, and monitor the effectiveness of internal controls, business processes, and compliance obligations. In EBS 12.1.1 and 12.2.2, this table functions as the anchor record for each assessment, carrying both the descriptive attributes (type, owner, status, dates) and the integration pointers that link an assessment to related survey, cycle, and deployment artifacts managed by the IES (iSetup/Enterprise Survey) components.

The table is owned by the AMW schema and is documented as VALID in the ETRM repository, with 35 physical columns in the 12.1.1 schema. The heuristic Data Vault classification mined from the foreign key structure identifies AMW_ASSESSMENTS_B as standalone. In Data Vault modeling terms, this suggests treating the table primarily as a satellite or hub-like anchor keyed on ASSESSMENT_ID, with limited outward referential dependencies — the single documented foreign key (SECURITY_GROUP_ID to FND_SECURITY_GROUPS) is a security/multi-tenancy attribute rather than a business relationship to another transactional entity.

Key Information Stored

The most significant columns capture the identity, classification, ownership, and scheduling of an assessment:

  • ASSESSMENT_ID — the surrogate primary key, enforced by the AMW_ASSESSMENTS_B_PK constraint and mirrored by the unique index AMW_ASSESSMENTS_B_U1. This is the definitive business-key candidate and the value referenced by all dependent AMW child tables.
  • ASSESSMENT_TYPE_CODE — classifies the assessment (for example, control test, process review, or survey-based evaluation), driving downstream workflow and reporting behavior.
  • ASSESSMENT_STATUS_CODE — the current lifecycle state of the assessment (such as draft, in progress, or completed), central to status dashboards and approval logic.
  • ASSESSMENT_OWNER_ID — the party responsible for the assessment, typically resolved against the HR/per-user directory.
  • IES_SURVEY_ID, IES_CYCLE_ID, IES_DEPLOYMENT_ID — integration pointers that connect the assessment to its underlying survey definition, its assessment cycle, and the deployment instance used to collect responses.
  • START_DATE and EFFECTIVE_COMPLETION_DATE — the scheduled start and the effective completion date used for period-based compliance reporting.
  • PERIOD_NAME — the accounting or reporting period to which the assessment belongs, enabling period-over-period analysis.
  • EVALUATION_CONTEXT and EVALUATION_TYPE — describe the evaluation framework and method applied.
  • SECURITY_GROUP_ID — enforces multi-org/security-group visibility, with a documented foreign key to FND_SECURITY_GROUPS.
  • OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates.
  • The standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and the fifteen ATTRIBUTE1–15 descriptive flexfield columns provide auditability and extensibility.

Common Use Cases and Queries

Typical reporting includes open-assessment aging, completion-rate tracking by owner or period, and survey-to-assessment traceability. A representative query joining status and owner information might read:

  • SELECT a.ASSESSMENT_ID, a.ASSESSMENT_TYPE_CODE, a.ASSESSMENT_STATUS_CODE, a.ASSESSMENT_OWNER_ID, a.START_DATE FROM AMW.AMW_ASSESSMENTS_B a WHERE a.PERIOD_NAME = :period AND a.ASSESSMENT_STATUS_CODE <> 'COMPLETED';
  • Assessment-to-survey lineage: SELECT a.ASSESSMENT_ID, a.IES_SURVEY_ID, a.IES_CYCLE_ID, a.IES_DEPLOYMENT_ID FROM AMW.AMW_ASSESSMENTS_B a WHERE a.IES_SURVEY_ID IS NOT NULL;
  • Security-scoped extraction: SELECT a.ASSESSMENT_ID FROM AMW.AMW_ASSESSMENTS_B a, FND_SECURITY_GROUPS g WHERE a.SECURITY_GROUP_ID = g.SECURITY_GROUP_ID;
  • Concurrency-safe updates should validate OBJECT_VERSION_NUMBER before committing changes to ASSESSMENT_STATUS_CODE.

Related Objects

While AMW_ASSESSMENTS_B is heuristically classified as standalone, it participates in the following documented and implied relationships:

  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID, the only explicitly documented foreign key, used for data security and multi-org filtering.
  • AMW_ASSESSMENTS_B_PK / AMW_ASSESSMENTS_B_U1 — the primary key constraint and unique index over ASSESSMENT_ID that guarantee identity and support dependency resolution.
  • IES survey and deployment objects — referenced via IES_SURVEY_ID, IES_CYCLE_ID, and IES_DEPLOYMENT_ID, forming the assessment-to-survey integration path.
  • AMW child/detail tables — downstream AMW objects (assessment questions, responses, and results) that resolve parent assessments through ASSESSMENT_ID.
  • FND_USER and HR per-person sources — used to resolve ASSESSMENT_OWNER_ID and the standard WHO audit columns.

DBA/implementation access is granted as AMW.AMW_ASSESSMENTS_B, and the object is flagged as Oracle proprietary and confidential in the ETRM documentation.