Search Results po_notification_ctrl_merge_v




Overview
Explore in AI Chat

Overview

PO_NOTIFICATION_CTRL_MERGE_V is an APPS-owned view in the Oracle E-Business Suite Purchasing (PO) module. It exposes the merged, unified result set of purchasing document notification controls, combining records held in the base notification control table with the pending changes staged in the notification control draft table. Its central purpose is to present the current "effective" control definition for a purchasing document while overlaying any in-flight draft modifications that have not yet been approved or completed.

The view is defined with a UNION ALL of two branches. The first branch returns draft rows still in progress (from PO_NOTIFICATION_CTRL_DRAFT joined to PO_DRAFTS), while the second branch returns the committed base rows from PO_NOTIFICATION_CONTROLS for which no open draft exists. In this way the view avoids duplicate output and provides a single, queryable source of truth for the notification controls currently governing a purchase order or agreement.

For reporting and integration purposes, the view is relevant because it surfaces the NOTIFICATION_AMOUNT column, which is the attribute a user searching on "notification_amount" would be attempting to locate. This column represents the monetary threshold that triggers a control notification. Because the view reconciles draft and approved states, it is the preferred object when a report must reflect controls as they will apply once outstanding changes are accepted.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over the following referenced base objects, all accessed through APPS synonyms:

  • PO_DRAFTS — the draft header table that tracks change sets, their owning role (OWNER_ROLE), owning user (OWNER_USER_ID) and workflow status (STATUS). It supplies DRAFT_ID, OWNER_USER_ID, OWNER_ROLE and STATUS to the first UNION branch.
  • PO_NOTIFICATION_CTRL_DRAFT — the draft-level staging table for notification controls. It provides the uncommitted control attributes, including NOTIFICATION_AMOUNT, NOTIFICATION_CONDITION_CODE, NOTIFICATION_QTY_PERCENTAGE, CHANGE_ACCEPTED_FLAG and DELETE_FLAG.
  • PO_NOTIFICATION_CONTROLS — the base (approved) notification control table, supplying the committed control rows in the second UNION branch, correlated by NOTIFICATION_ID.

The join condition PNCD.DRAFT_ID = DFT.DRAFT_ID links each staged control to its parent draft. The first branch filters on PNCD.DELETE_FLAG IS NULL, restricts rows to the current global role (DFT.OWNER_ROLE = PO_GLOBAL.ROLE), and excludes drafts whose status is 'COMPLETED'. The second branch uses a NOT EXISTS subquery so that base rows are returned only where no open draft for the same NOTIFICATION_ID exists — preventing the same control from appearing twice.

Key Columns

The view projects a consistent column list from both branches, with NULL placeholders in the second branch where draft-only attributes do not apply.

  • NOTIFICATION_ID, PO_HEADER_ID — identifiers for the control record and its owning purchasing document.
  • NOTIFICATION_AMOUNT — the monetary threshold for the control; the field central to the originating search.
  • NOTIFICATION_CONDITION_CODE — the condition operator (for example, amount or quantity based) governing when notification fires.
  • NOTIFICATION_QTY_PERCENTAGE — the quantity/percentage alternative to the amount threshold.
  • START_DATE_ACTIVE, END_DATE_ACTIVE — the effective date range of the control.
  • DRAFT_ID, OWNER_USER_ID, OWNER_ROLE, STATUS — draft context; populated only for rows sourced from the draft branch.
  • CHANGE_ACCEPTED_FLAG, DELETE_FLAG — draft processing indicators.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield segments.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID.

Common Use Cases and Queries

Typical uses include reporting on effective notification thresholds, pre-approval validation of pending control changes, and integrations that must read controls as they will apply post-approval.

  • Listing all controls for a document with their effective amount:

SELECT notification_id, po_header_id, notification_amount, notification_condition_code FROM apps.po_notification_ctrl_merge_v WHERE po_header_id = :p_po_header_id;

  • Identifying controls with pending draft changes for the current role:

SELECT notification_id, draft_id, status, notification_amount FROM apps.po_notification_ctrl_merge_v WHERE draft_id IS NOT NULL AND status <> 'COMPLETED';

  • Auditing thresholds above a given value across all purchasing documents:

SELECT po_header_id, notification_id, notification_amount FROM apps.po_notification_ctrl_merge_v WHERE notification_amount > :p_threshold;

Because the view blends draft and approved data, consumers should be aware that a single logical control may appear differently before and after a draft is completed, making the view particularly valuable for "what-if" reporting and approval-preparation screens.