Search Results amw_proc_cert_status

Overview

The AMW.AMW_CERTIFICATION_B table is the certifications base table within the Oracle E-Business Suite Advanced Management Workbench (AMW) module. It stores the master definition of each certification — the record that captures who owns the certification, what type it is, its status, the accountable period, and its outcome. In Oracle EBS 12.1.1 and 12.2.2 the table is owned by the AMW schema, is registered in FND Design Data as AMW.AMW_CERTIFICATION_B, and resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10. Its primary key is AMW_CERTIFICATION_B_PK on CERTIFICATION_ID, and the documented table metadata lists 39 columns, including the standard WHO audit columns, a descriptive flexfield block, and Oracle EBS multi-org and OAF columns.

From a Data Vault modeling perspective, the mined heuristic classification of this object is hub-leaning. This is a modeling suggestion rather than a physical implementation: CERTIFICATION_ID behaves as a durable business key that anchors the certification, while the descriptive and status-bearing columns would conventionally be split into one or more satellite tables on the same hub. The presence of foreign keys from AMW_CERT_DASHBOARD_SUM, AMW_EXECUTION_SCOPE, and AMW_PROC_CERT_EVAL_SUM reinforces the hub role, since these dependent objects reference the certification as a whole rather than redefining it.

Key Information Stored

The surrogate primary key of the table is CERTIFICATION_ID (NUMBER), enforced by the primary key constraint AMW_CERTIFICATION_B_PK. A separate unique index, AMW_CERTIFICATION_B_U1, is defined on the same CERTIFICATION_ID column in the APPS_TS_TX_IDX tablespace; this is the business-key candidate documented for the object and the index referenced by the search term amw_certification_b_u1. The most significant descriptive and business columns are:

• CERTIFICATION_OWNER_ID — the owner of the certification.
• CERTIFICATION_TYPE — validated against the AMW_PROC_CERT_TYPE lookup in AMW_LOOKUPS.
• CERTIFICATION_STATUS — validated against the AMW_PROC_CERT_STATUS lookup.
• CERTIFICATION_CREATION_DATE, TARGET_COMPLETION_DATE, and CERTIFICATION_COMPLETION_DATE — the certification lifecycle dates.
• CERTIFICATION_PERIOD_NAME and CERTIFICATION_PERIOD_SET_NAME — the accounting period, sourced from GL Periods.
• CERTIFICATION_RESULT — the recorded outcome of the certification.
• CERTIFICATION_REMINDER — the number of days used to schedule automatic workflow reminders.
• SECURITY_GROUP_ID — the security group reference to FND_SECURITY_GROUPS.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the descriptive flexfield structure and segments.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical usage centers on certification status reporting, owner workload analysis, and period-based compliance tracking. A common pattern joins the certification to GL periods for period-name validation and aggregates by status:

• SELECT CERTIFICATION_TYPE, CERTIFICATION_STATUS, COUNT(*) FROM AMW_CERTIFICATION_B GROUP BY CERTIFICATION_TYPE, CERTIFICATION_STATUS;
• Open certifications past target date: filter WHERE CERTIFICATION_STATUS != 'COMPLETE' AND TARGET_COMPLETION_DATE < SYSDATE.
• Reminder scheduling: query CERTIFICATION_REMINDER and LAST_REMINDER_DATE to identify certifications due for workflow notification.
• Owner reporting: join on CERTIFICATION_OWNER_ID to the relevant person or employee view to report outstanding certifications per owner.
• Period analysis: join CERTIFICATION_PERIOD_NAME and CERTIFICATION_PERIOD_SET_NAME to GL_PERIODS to reconcile certifications to their accounting calendar.

Related Objects

Several objects depend on or reference this table through documented foreign key relationships:

• AMW_CERT_DASHBOARD_SUM — references CERTIFICATION_ID; supplies dashboard summary metrics.
• AMW_EXECUTION_SCOPE — references ENTITY_ID back to the certification, defining scope for execution.
• AMW_PROC_CERT_EVAL_SUM — references CERTIFICATION_ID; stores evaluation summaries.
• GL_PERIODS — referenced via CERTIFICATION_PERIOD_SET_NAME for calendar validation.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for data security.
• AMW_LOOKUPS — provides the AMW_PROC_CERT_TYPE and AMW_PROC_CERT_STATUS validation values for certification type and status.