Search Results fnd_notifications_pk

Overview

FND_NOTIFICATIONS is a table physically owned by the PO schema within the Oracle E-Business Suite database. According to the ETRM metadata, it is documented as obsolete as of Release 11, meaning its original purpose—storage of worklist notification records tied to an employee, an application, and a message name—has been superseded by the Oracle Workflow / Notification Mailer architecture (WF_NOTIFICATIONS and related objects). Its presence in Release 12.1.1 and 12.2.2 environments is therefore largely historical; it persists as a legacy or archived artifact.

Storage metadata places the table and all of its indexes in the APPS_TS_ARCHIVE tablespace with PCTFREE 10, which is consistent with an object retained for reference or migration rather than active transactional use. Although the columns were retired, the structure retains the standard descriptive flexfield attribute block (ATTRIBUTE1 through ATTRIBUTE32) and the standard WHO audit columns, indicating it followed Oracle's standard table-creation conventions.

The metadata carries a heuristic Data Vault classification of standalone. In Data Vault modeling terms this suggests the table behaves as a hub-like or reference entity keyed by a single surrogate identifier, with no resolved link participation other than the documented EMPLOYEE_ID reference. This classification should be treated as a modeling suggestion rather than a definitive architectural statement.

Key Information Stored

The 59-column structure begins with the surrogate primary key NOTIFICATION_ID, a NUMBER column that is also the sole column of the unique index FND_NOTIFICATIONS_PK. Because the table is obsolete, no documented business-key candidate other than this primary key exists among the unique indexes; the two foreign-key indexes are nonunique.

The substantive columns documented in the metadata are:

• NOTIFICATION_ID — surrogate primary key; the PK constraint is reported as SYS_C0013522, with FND_NOTIFICATIONS_PK as the backing unique index.
• EMPLOYEE_ID — identifies the recipient employee; indexed by the nonunique FND_NOTIFICATIONS_FK1 and documented as referencing PSB_EMPLOYEES.
• MESSAGE_NAME — VARCHAR2(30) naming the notification or message type; indexed by the nonunique FND_NOTIFICATIONS_FK2.
• APPLICATION_ID — numeric identifier of the owning application context.
• STATUS — VARCHAR2(30) holding the notification's processing state.
• FROM_ID — identifier of the originating party.
• DELETABLE and OFF_LINE — control flags governing deletion and offline handling of the notification.
• START_EFFECTIVE_DATE and END_EFFECTIVE_DATE — the effective-dating range for the record.
• DOC_CREATION_DATE — creation date of the associated document.
• PRIORITY — numeric ranking used to order notification delivery.
• DATE1–DATE4 — generic date placeholders retained for extensibility.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.
• ATTRIBUTE1–ATTRIBUTE32 — descriptive flexfield segments, VARCHAR2(240).

Document-oriented columns DOC_TYPE, DOC_NUMBER, OBJECT_ID, AMOUNT, CURRENCY, and NOTE also appear in the full 59-column listing, reflecting the earlier design where notifications carried an embedded document reference.

Common Use Cases and Queries

Because the table is obsolete in Release 11 onward, production use cases center on historical analysis, migration validation, and archival reconciliation rather than application processing. A typical pattern verifies whether legacy notification rows correspond to active Workflow notifications:

SELECT n.NOTIFICATION_ID, n.EMPLOYEE_ID, n.MESSAGE_NAME, n.STATUS
FROM PO.FND_NOTIFICATIONS n
WHERE n.END_EFFECTIVE_DATE IS NULL;

Joining to PSB_EMPLOYEES on EMPLOYEE_ID supports recipient-level reporting such as volume of notifications per employee or per message name. Grouping by MESSAGE_NAME and STATUS produces a legacy message distribution report, while filtering PRIORITY isolates high-priority items. The effective-date pair supports as-of-date reconstruction of the notification set, and the flexfield attributes can be queried for any segment values that were populated before retirement.

Before any purge or archive decision, the standard WHO columns should be inspected to determine the latest activity on the table; a stale LAST_UPDATE_DATE across all rows is a reliable indicator that the object has been dormant since the upgrade to Release 11.

Related Objects

The metadata documents one explicit foreign-key relationship and three indexes that define the most significant dependencies:

• PSB_EMPLOYEES — referenced through EMPLOYEE_ID; the primary join path for recipient information.
• FND_NOTIFICATIONS_PK — unique index on NOTIFICATION_ID defining the primary key.
• FND_NOTIFICATIONS_FK1 — nonunique index on EMPLOYEE_ID supporting the employee join.
• FND_NOTIFICATIONS_FK2 — nonunique index on MESSAGE_NAME supporting message-type lookups.
• APPS.FND_MESSAGES / FND_APPLICATION — semantic lookups for MESSAGE_NAME and APPLICATION_ID values, since these columns carry no enforced database constraints.
• WF_NOTIFICATIONS — the Oracle Workflow table that functionally replaced this object after Release 11, and the principal migration target for any surviving rows.

Any customization or report that still queries PO.FND_NOTIFICATIONS should be reviewed and re-pointed to the corresponding Workflow notification objects during upgrade or remediation activity, given the table's obsolete status in both Release 12.1.1 and 12.2.2.