Search Results call_wf_process_to_send_notif




Overview
Explore in AI Chat

Overview

PON_REMINDER_NOTIFICATION_PKG is a PL/SQL package owned by APPS that supports the Oracle Sourcing (PON) module by dispatching reminder notifications tied to auction events. Its principal role is to evaluate the dates on which auction-related reminders must fire — before a close date, after an open date, or after a preview date — and to route those notifications through Oracle Workflow. The package builds on the auction header and bidding party data maintained by Sourcing, identifies the auctions that qualify for a given reminder cycle, and then invokes Workflow to deliver the actual message. It is declared AUTHID CURRENT_USER, meaning its unqualified object references resolve against the invoker's schema privileges. The package depends on the Workflow items infrastructure and Sourcing business tables, and it is not referenced by any other documented package, indicating it functions as an entry point invoked externally rather than a reusable library.

Key Procedures and Functions

  • SEND_NOTIFICATION_WRAPPER — The top-level entry point and the object most commonly searched. It accepts a p_send_when indicator identifying which auction date the reminder is measured against, together with a comma-separated list of day offsets (for example 11,12,15). The wrapper converts that CSV string into an indexed number table and passes it to SEND_NOTIFICATION. It also calls the purge routine, making it the primary orchestration point of the package.
  • SEND_NOTIFICATION — Receives the same p_send_when indicator plus the already-parsed number table of day offsets. It selects the auctions that satisfy the reminder criteria and calls CALL_WF_PROCESS_TO_SEND_NOTIF, passing the relevant auction header identifier.
  • CALL_WF_PROCESS_TO_SEND_NOTIF — Given a p_send_when value and a single day value, this routine prepares and initiates the Workflow notification for the specific auction header it was called with.
  • START_WF_PROCESS — Starts the underlying Workflow process that performs notification delivery.
  • PURGE_NOTIF_WF — Purges completed or obsolete notification Workflow items, preventing the Workflow tables from accumulating stale reminder records.
  • LOG_MESSAGE — Writes diagnostic information to the FND_LOG_MESSAGES table for troubleshooting reminder execution.

Tables Accessed

  • PON_AUCTION_HEADERS_ALL — The primary source of auction dates (open, preview, and close) used to determine whether a reminder is due.
  • PON_BIDDING_PARTIES — Identifies bidders and invitees who should receive the auction notifications.
  • PON_BID_HEADERS — Provides supporting bid information associated with the auction.
  • WF_ITEMS — Workflow item records created, queried, and purged as notifications are sent.
  • FND_LOG_MESSAGES — Destination for messages emitted by LOG_MESSAGE.
  • FND_USER and PER_ALL_PEOPLE_F — Resolve the user and person identities of notification recipients.
  • DBMS_RANDOM, DUAL, and PLITBLM — Utility dependents used for random generation, singleton queries, and message-list handling respectively.

Usage Notes

This package is normally invoked by Sourcing's scheduled reminder processing rather than by direct user interaction. In a typical Oracle EBS 12.1.1 or 12.2.2 deployment it is driven from a concurrent program, which supplies the p_send_when indicator and the day-offset list so that reminders fire on the configured schedule. Administrators and developers call SEND_NOTIFICATION_WRAPPER directly only when manually replaying a reminder run or during diagnostics, because it performs both the send and the purge. Custom code that calls the package must hold execute privileges on it and adequate privileges on the underlying Sourcing and Workflow tables. Because AUTHID CURRENT_USER is in effect, unqualified references resolve at runtime, so invoker grants matter. Failure messages surface through the Workflow notification error path and the FND log; the FND_LOG_MESSAGES entries written by LOG_MESSAGE are the first place to inspect when expected reminders do not appear.