Search Results po_notification_controls_pk

Overview

PO_NOTIFICATION_CONTROLS is a Purchasing (PO) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores notification control definitions for blanket agreements and planned purchase orders. In the procurement lifecycle, these two document types differ from standard purchase orders in that they represent long-running or tentative commitments — blanket agreements establish negotiated pricing and terms against which releases are later issued, while planned purchase orders serve as tentative commitments that can be converted to firm orders. The notification controls recorded in this table govern when the system raises alerts or notifications relative to defined thresholds, allowing buyers and sourcing managers to monitor consumption against a blanket agreement or monitor a planned order approaching its committed value or quantity.

Each row is anchored to a specific purchasing document through PO_HEADER_ID, which references PO_HEADERS_ALL, so the table functions as a child or detail structure beneath the document header. The heuristic Data Vault classification mined from the foreign-key structure is satellite-leaning: the table is primarily descriptive attribute data attached to a parent business key (the purchasing document), rather than an independent hub of business entities or a link resolving a many-to-many relationship.

Key Information Stored

The table contains 32 documented columns in the ETRM 12.2.2 physical schema. The most operationally significant are:

• NOTIFICATION_ID — the surrogate primary key, enforced by PO_NOTIFICATION_CONTROLS_PK and also backed by the unique index PO_NOTIFICATION_CONTROLS_U1. It is the single business-key candidate documented for the table.
• PO_HEADER_ID — foreign key to PO_HEADERS_ALL, identifying the blanket agreement or planned purchase order to which the control applies.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective date range during which the notification control is active; outside this window the control is not evaluated.
• NOTIFICATION_AMOUNT — the monetary threshold that triggers the notification, typically expressed against the agreement or planned order value.
• NOTIFICATION_CONDITION_CODE — the code determining the condition under which notification is raised (for example, when consumed amount or quantity crosses the defined threshold).
• NOTIFICATION_QTY_PERCENTAGE — the quantity or percentage threshold used as an alternative or complement to the amount-based trigger.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard WHO audit columns recording who created and last maintained the control record, and when.
• PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — concurrent program and request context identifying the process that last touched the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns, available for client-specific extensions.

Common Use Cases and Queries

The table is typically queried when investigating why a notification was or was not raised for a blanket agreement, or when reporting on threshold configuration across a set of agreements. A representative query joining the control to its parent document is:

• SELECT pn.notification_id, pn.po_header_id, ph.segment1, pn.notification_amount, pn.notification_condition_code, pn.notification_qty_percentage, pn.start_date_active, pn.end_date_active FROM po.po_notification_controls pn, po.po_headers_all ph WHERE pn.po_header_id = ph.po_header_id AND ph.type_lookup_code IN ('BLANKET','PLANNED') ORDER BY ph.segment1;
• Reporting on active versus expired controls: filter on SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE + 1).
• Audit analysis of who changed thresholds: group by LAST_UPDATED_BY with LAST_UPDATE_DATE ranges.
• Extensibility reporting against ATTRIBUTE_CATEGORY and the ATTRIBUTE1–15 columns where clients have configured DFF segments.

Related Objects

The primary related objects, based on the documented foreign-key relationship and normal PO module dependencies, include:

• PO_HEADERS_ALL — joined on PO_NOTIFICATION_CONTROLS.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID; the parent purchasing document header.
• PO_LINES_ALL and PO_LINE_LOCATIONS_ALL — joined through the header, providing the line and shipment detail against which consumption thresholds are measured.
• PO_DISTRIBUTIONS_ALL — the accounting and receipt distributions that drive actual consumption figures.
• PO_RELEASES_ALL — records releases issued against blanket agreements, central to evaluating whether notification thresholds have been reached.
• PO_NOTIFICATION_CONTROLS_PK / PO_NOTIFICATION_CONTROLS_U1 — the primary key constraint and unique index governing row identity.
• Purchasing forms and the PO notification concurrent processes that read this table when evaluating and dispatching alerts.

Because the table is satellite-leaning and dependent on PO_HEADERS_ALL, referential integrity should always be checked against the header before relying on any control record.