Results for “po_notifications_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PO_NOTIFICATIONS_ALL is a Purchasing (PO) module table that stores notification records associated with employees and purchasing documents in Oracle E-Business Suite 12.1.1 and 12.2.2. Each row records that a designated employee has an active or historical notification tied to a specific purchasing object — a purchase order, a release, or a requisition header — with applicability controlled by start and end dates and an action code. The table therefore acts as the persistence layer behind notification-driven purchasing workflows such as buyer alerts, approval routing, and document activity tracking.

The documented relationship structure indicates that a single notification binds three business entities: an employee, a purchasing object, and an object type. Applying Data Vault modeling heuristics to the observed foreign-key structure, PO_NOTIFICATIONS_ALL is best classified as a link table, resolving the many-to-many association between employees and purchasing documents. This classification is a modeling suggestion only; in native EBS terms the table is a standard transactional intersection table in the PO schema.

Key Information Stored

The physical schema documents 18 columns, of which the following carry the substantive business content:

• EMPLOYEE_ID — the recipient of the notification; participant in the composite primary key and both unique indexes.
• OBJECT_ID — the identifier of the referenced purchasing document. Its meaning is polymorphic, resolved by OBJECT_TYPE_LOOKUP_CODE, and it participates in foreign keys to PO_HEADERS_ALL, PO_RELEASES_ALL, and PO_REQUISITION_HEADERS_ALL.
• OBJECT_TYPE_LOOKUP_CODE — the discriminator that determines which purchasing entity OBJECT_ID denotes (for example, purchase order, release, or requisition). This is the column most frequently used to filter and segment notification data by document class.
• ACTION_LOOKUP_CODE — the notification action or category; included in the unique index PO_NOTIFICATIONS_U1 and therefore part of the effective business key.
• OBJECT_CREATION_DATE — the creation date of the referenced purchasing object, retained locally for reporting without a join.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the date range during which the notification is active, supporting time-sliced querying.
• ORG_ID — the operating unit, supporting multi-org data access.
• INSERTED_BY_FND — a flag indicating whether the row was created by the FND notification framework rather than by Purchasing application logic.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context identifying the process that inserted or last updated the row.

The surrogate-style primary key is PO_NOTIFICATIONS_PK, comprising EMPLOYEE_ID, OBJECT_ID, and OBJECT_TYPE_LOOKUP_CODE. The unique index PO_NOTIFICATIONS_U1 extends this with ACTION_LOOKUP_CODE, identifying it as the broader business-key candidate.

Common Use Cases and Queries

Typical usage includes identifying all notifications for a buyer, auditing which purchasing documents generated notifications, and reporting notification volumes by object type. Because OBJECT_ID is polymorphic, queries must always constrain OBJECT_TYPE_LOOKUP_CODE before joining to a specific document table:

SELECT employee_id, object_id, object_type_lookup_code, action_lookup_code
FROM po_notifications_all
WHERE object_type_lookup_code = 'PO'
AND org_id = :org_id
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);

A second common pattern joins the notifications for purchase orders back to PO_HEADERS_ALL on OBJECT_ID to retrieve document numbers and status. Reporting use cases include notification aging analyses, recipient workload summaries, and reconciliation of FND-generated rows (INSERTED_BY_FND) against application-generated rows.

Related Objects

• PO_HEADERS_ALL — joined via PO_NOTIFICATIONS_ALL.OBJECT_ID = PO_HEADERS_ALL.PO_HEADER_ID for purchase order notifications.
• PO_RELEASES_ALL — joined via OBJECT_ID for release-level notifications.
• PO_REQUISITION_HEADERS_ALL — joined via OBJECT_ID for requisition notifications.
• PER_ALL_PEOPLE_F / HR employees — resolved via EMPLOYEE_ID to identify notification recipients.
• FND lookup definitions — resolve OBJECT_TYPE_LOOKUP_CODE and ACTION_LOOKUP_CODE to their display meanings.
• PO_NOTIFICATION_CONTROLS and notification workflow activity tables — configuration and process context governing when notifications are generated.
• PO_HEADERS_ALL audit and action history tables — frequently queried alongside notifications to reconstruct document lifecycle events.

Because Oracle maintains each foreign key to a different purchasing document table over the same OBJECT_ID column, any integration or extract logic must resolve OBJECT_TYPE_LOOKUP_CODE first; joining directly to a single document table without that filter will produce incorrect or ambiguous results.