Search Results po_notification_controls_n1

Overview

PO.PO_NOTIFICATION_CONTROLS is a transactional table in the Oracle E-Business Suite Purchasing (PO) schema that stores notification control rules for blanket, planned, and contract purchase orders. Each row represents a single notification control rule created by an employee for a specific purchase order header. Oracle Alert consumes these rules to determine the type of notification to send, the effective date range during which the notification is active, and the document amount threshold that triggers notification delivery. The table is classified as VALID in the ETRM 12.2.2 repository and resides in the APPS_TS_TX_DATA tablespace with PCT Free 10.

From a Data Vault modeling perspective, the mined foreign key structure suggests a satellite-leaning classification. The table hangs off PO_HEADERS_ALL via the PO_HEADER_ID foreign key and carries descriptive, time-bounded attributes (effective dates, amount thresholds, notification condition codes) rather than acting as an independent business hub. In practice, it behaves as a dependent child of the purchase order header.

Key Information Stored

NOTIFICATION_ID is the unique system-generated surrogate primary key, enforced by the unique index PO_NOTIFICATION_CONTROLS_U1 and the primary key constraint PO_NOTIFICATION_CONTROLS_PK. It is invisible to the user and serves only as the row identifier. PO_HEADER_ID is the business-key foreign key linking the rule to the parent blanket, planned, or contract purchase order header. It is indexed by the non-unique index PO_NOTIFICATION_CONTROLS_N1.

• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective and expiration dates that bound the period during which the notification applies.
• NOTIFICATION_AMOUNT — the document total threshold that triggers the notification.
• NOTIFICATION_CONDITION_CODE — a VARCHAR2(25) code identifying the notification type evaluated by Oracle Alert.
• NOTIFICATION_QTY_PERCENTAGE — the percentage of the document total used as an alternate trigger criterion.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LAST_UPDATED_BY, CREATED_BY, and CREATION_DATE provide audit traceability.
• Concurrent program columns — PROGRAM_ID, PROGRAM_APPLICATION_ID, REQUEST_ID, and PROGRAM_UPDATE_DATE identify the concurrent request that created or modified the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the DFF-enabled descriptive flexfield segments available for client-specific extensions.

The documented schema contains 32 columns in total, consistent with the standard EBS Who columns, DFF columns, and concurrent program audit columns layered onto the core business attributes.

Common Use Cases and Queries

Typical reporting scenarios include identifying which purchase orders have active notification rules, auditing threshold amounts and validity windows, and validating rules before period-close or notification batch runs.

SELECT p.segment1, n.notification_condition_code,
       n.notification_amount, n.notification_qty_percentage,
       n.start_date_active, n.end_date_active
FROM   po.po_notification_controls n,
       po.po_headers_all p
WHERE  n.po_header_id = p.po_header_id
AND    n.start_date_active <= SYSDATE
AND    NVL(n.end_date_active, SYSDATE + 1) >= SYSDATE;

A second pattern retrieves all rules for a specific document header via the N1 index path, and a third counts rules per notification condition code to profile notification coverage across the purchasing population. Oracle Alert subscribes to this table to generate notifications when amounts or quantities cross the configured thresholds.

Related Objects

• PO.PO_HEADERS_ALL — the parent purchase order header table; joined on PO_HEADER_ID and the documented foreign key reference.
• PO.PO_LINES_ALL — line-level detail for the same header, used when quantifying document totals that drive NOTIFICATION_AMOUNT triggers.
• PO.PO_HEADERS_ARCHIVE_ALL and associated archive tables — retain historical notification control rules for closed or archived documents.
• PO.PO_DOCUMENT_TYPES_ALL — classifies the document types (blanket, planned, contract) eligible for notification control rules.
• FND_USER — referenced by LAST_UPDATED_BY and CREATED_BY for audit reconciliation.
• FND_LOGINS — referenced by LAST_UPDATE_LOGIN to identify the operating system login session.
• ALR_ALERTS and Oracle Alert registry objects — consume the condition codes and thresholds defined here to drive notification delivery.

Because the table is satellite-leaning, referential integrity with PO_HEADERS_ALL is the dominant relationship; cascading deletes or header purges should be evaluated carefully, since orphaned control rules will be ignored by Oracle Alert but may cause residual reporting noise.