Oracle APEX Notification System Tutorial

Introduction

I built this to deliver quick, actionable updates that behave like a lightweight to-do list inside my HRMS. If a contract or attachment is about to expire, managers and HR get a nudge. If an employee submits leave -manager, HR, and the employee get updates immediately.

The core idea is simple: everything runs around one table. Any time a domain event happens. whether it’s part of a human task, inside a workflow, or completely automated via a job. we insert one row per user into APP_USER_NOTIFICATIONS. The UI needs no special wiring: the global bell shows the unread count everywhere, and a modal “Notifications” page lets users act fast, click = mark read + navigate - without leaving their current flow.

Under the hood, one package (app_notifications) keeps it boring in the best way: create, mark read, mark all, unread count, retention, and a couple of realistic notifiers for documents and contracts. Links are session-safe (no surprise logouts), and daily jobs keep the queue lean.

Top view (at a glance)

Lean architecture overview - single-table queue (APP_USER_NOTIFICATIONS) + one core package (app_notifications) + a modal Notifications page + a global NavBar bell.
Why: one lightweight queue table keeps writes cheap, the trigger centralizes retention.
Core logic - app_notifications package (create, mark read/mark all, unread count, URL prep, purge, plus sample document & contract notifiers).
Why: single entry point = testable, reusable, UI-agnostic. Any row inserted into APP_USER_NOTIFICATIONS for a target audience becomes a notification - add more notifiers as needed.
Global bell - NavBar list entry + Static CSS/JS, badge fed by app-level GET_UNREAD_COUNT.
Why: visible on every page with zero per-page wiring.
Notifications page - Modal Drawer with Classic Report (Comments-style) and a “Mark all” button, clicking a row marks read and navigates.
Why: fastest UX, server decides state; minimal client code.
Ajax processes - GET_UNREAD_COUNT (application), MARK_READ and MARK_ALL_READ (Notifications Page).
Why: tiny text responses, simple and fast.
Session & security - prepare_public_url(url) wraps APEX_UTIL.PREPARE_URL (p_checksum_type=>'SESSION', p_plain_url=>TRUE) + Rejoin Sessions = Enabled; inbox query scoped by USER_ID = :G_USER_ID.
Why: prevents logout on click, preserves session across deep links, no cross-user reads.
Jobs — Daily 02:00: purge expired, document- and contract-expiration notifiers.
Why: keeps the table lean and alerts timely without peak-hour load.
Styling & UX assets - inline CSS for unread dot, subtle unread tint, tidy empty state; full row-template markup.
Extension notes - how to “go crazy”: add any domain notifier or drop rows into the table, and the UI/JS picks them up automatically.

Architecture (high‑level)

Event sources
  • Scheduled jobs: NOTIFY_DOCUMENT_EXPIRATION, NOTIFY_CONTRACT_EXPIRATION
  • App code: app_notifications.create_notification(...)
  • (Optional) other modules enqueue directly

           │
           ▼
   app_notifications  (single core package)
   ├─ prepare_public_url(url)   -- session-safe deep links
   ├─ create_notification(...)  -- INSERT into queue
   ├─ mark_read / mark_all_read -- update read state
   ├─ unread_count(...)         -- badge count
   └─ purge_expired             -- retention

           │
           ▼
 APP_USER_NOTIFICATIONS  (single queue table)
           │
           ├─────────────► APEX PWA push (optional): APEX_PWA.SEND_PUSH_NOTIFICATION
           │
           ▼
 In-App UX
   • Global NavBar bell (Static CSS/JS) → GET_UNREAD_COUNT
   • Modal “Notifications” page (Classic Report):
       click = mark_read + navigate (session stays intact)

Why this shape

Type‑driven: add/disable notification types without edits to core code
Channel‑agnostic: same event can fan out to Email/SMS/In‑App
Idempotent: dedupe rules prevent double-sending
Auditable: everything is queryable from logs

Backend (Data model + Core package)

Data model - `APP_USER_NOTIFICATIONS`

What it is

A single, lightweight queue table that stores every in-app notification. It’s intentionally simple so writes stay cheap and reads can be shaped by indexes.

DDL

-- APP_USER_NOTIFICATIONS (core in‑app queue)
CREATE TABLE "APP_USER_NOTIFICATIONS"
( "NOTIFICATION_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 926 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"APPLICATION_ID" NUMBER NOT NULL ENABLE,
"TITLE" VARCHAR2(200),
"BODY" VARCHAR2(4000),
"URL" VARCHAR2(2000),
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"ICON_CLASS" VARCHAR2(100),
"NOTIFICATION_TYPE" VARCHAR2(100),
"PAYLOAD_JSON" CLOB,
"EXPIRATION_DATE" DATE,
"SOURCE_MODULE" VARCHAR2(100),
"READ_ON" TIMESTAMP (6),
"PRIORITY" NUMBER DEFAULT 3,
"USER_ID" NUMBER,
CONSTRAINT "CHK_PAYLOAD_JSON_IS_JSON" CHECK (payload_json IS JSON) ENABLE NOVALIDATE,
PRIMARY KEY ("NOTIFICATION_ID")
USING INDEX ENABLE
);


ALTER TABLE "APP_USER_NOTIFICATIONS" ADD CONSTRAINT "FK_NOTIFICATION_USERID" FOREIGN KEY ("USER_ID")
REFERENCES "HCM_USERS" ("USER_ID") ENABLE;


-- Helpful indexes
CREATE INDEX "AUN_USER_APP_READ_IDX" ON "APP_USER_NOTIFICATIONS" ("USER_ID", "APPLICATION_ID", "READ_ON");
CREATE INDEX "IDX_NOTIFY_EXPIRATION" ON "APP_USER_NOTIFICATIONS" ("EXPIRATION_DATE");
CREATE INDEX "IDX_NOTIFY_PRIORITY_RECENT" ON "APP_USER_NOTIFICATIONS" ("APPLICATION_ID", "PRIORITY", "CREATED_ON");


-- Default expiration via BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER "AUN_BI_SET_EXPIRATION"
BEFORE INSERT ON "APP_USER_NOTIFICATIONS"
FOR EACH ROW
BEGIN
IF :NEW.expiration_date IS NULL THEN
:NEW.expiration_date := SYSDATE + app_notifications.c_retention_days;
END IF;
END;
/
ALTER TRIGGER "AUN_BI_SET_EXPIRATION" ENABLE;

Why these columns (quick map)

TITLE, BODY, URL, ICON_CLASS → what the user sees & where they go.
USER_ID, APPLICATION_ID → scoping per user/app (and fast filtering).
NOTIFICATION_TYPE, SOURCE_MODULE, PRIORITY, PAYLOAD_JSON → flexible metadata for dedupe, grouping, and future channels.
CREATED_ON, READ_ON, EXPIRATION_DATE → lifecycle + retention.

Why these indexes

(USER_ID, APPLICATION_ID, READ_ON) → accelerates badge count and “my inbox” reads (unread vs read).
(APPLICATION_ID, PRIORITY, CREATED_ON) → “recent/high priority” lists.
(EXPIRATION_DATE) → purge job.

Why the trigger

Auto-sets EXPIRATION_DATE so you never forget retention; the package can still override it per row.

Core package: `app_notifications` (spec & body)

This is the single entry point the UI and jobs call. It centralizes creation, read state, URL building, purging, and two sample domain notifiers.

Spec

create or replace PACKAGE app_notifications IS
------------------------------------------------------------------------------
-- Constants
------------------------------------------------------------------------------
c_retention_days CONSTANT PLS_INTEGER := 90;


------------------------------------------------------------------------------
-- Read state
------------------------------------------------------------------------------
PROCEDURE mark_read(
p_notification_id IN NUMBER,
p_user_id IN NUMBER
);


PROCEDURE mark_all_read(
p_user_id IN NUMBER
);


FUNCTION unread_count(
p_user_id IN NUMBER
) RETURN NUMBER;


------------------------------------------------------------------------------
-- Purge expired
------------------------------------------------------------------------------
PROCEDURE purge_expired;


------------------------------------------------------------------------------
-- URL helpers
------------------------------------------------------------------------------
FUNCTION prepare_public_url(
p_raw IN VARCHAR2
) RETURN VARCHAR2;


------------------------------------------------------------------------------
-- Create notification
------------------------------------------------------------------------------
PROCEDURE create_notification(
p_user_id IN NUMBER,
p_application_id IN NUMBER,
p_title IN VARCHAR2,
p_body IN VARCHAR2,
p_url IN VARCHAR2,
p_icon_class IN VARCHAR2 DEFAULT 'fa fa-bell',
p_expires_in_days IN PLS_INTEGER DEFAULT c_retention_days,
p_build_url IN BOOLEAN DEFAULT FALSE,
p_notification_id OUT NUMBER
);


------------------------------------------------------------------------------
-- Domain notifiers
------------------------------------------------------------------------------
PROCEDURE notify_document_expiration;
PROCEDURE notify_contract_expiration;


END app_notifications;
/

What each API does (and why)

create_notification(...)
Inserts a row into APP_USER_NOTIFICATIONS. If p_build_url = TRUE, it wraps the URL via APEX_UTIL.PREPARE_URL so the link is session-safe.
Why: UI can be “dumb”—just render rows. Any process can enqueue a message without touching the pages.
mark_read(...), mark_all_read(p_user_id)
Update read state. The package commits - appropriate for user-initiated actions.
Why: guarantees fast feedback for the badge/inbox after a click.
unread_count(p_user_id)
Counts unread using the (USER_ID, APPLICATION_ID, READ_ON) index.
Why: powers the global bell badge.
prepare_public_url(p_raw)
Wraps APEX_UTIL.PREPARE_URL (p_checksum_type => 'SESSION', p_plain_url => TRUE).
Why: prevents the “click → logout” by keeping the current session (we also enable Rejoin Sessions).
purge_expired
Deletes rows past EXPIRATION_DATE; the scheduler runs this daily.
Why: keeps the table lean, queries fast.
notify_document_expiration / notify_contract_expiration
Domain jobs that select expiring items, dedupe using JSON_VALUE(...), insert notifications, and (optionally) send PWA push via APEX_PWA.SEND_PUSH_NOTIFICATION.
Why: realistic examples you can copy for new domains (anything you insert for a target audience shows up in the same UI automatically).

Body

create or replace PACKAGE BODY app_notifications IS

------------------------------------------------------------------------------
-- mark_read
------------------------------------------------------------------------------
PROCEDURE mark_read(
p_notification_id IN NUMBER,
p_user_id IN NUMBER
) IS
BEGIN
UPDATE app_user_notifications
SET read_on = COALESCE(read_on, SYSTIMESTAMP)
WHERE notification_id = p_notification_id
AND user_id = p_user_id;


IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Notification not found for this user.');
END IF;


COMMIT; -- small, user-driven action
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END mark_read;

------------------------------------------------------------------------------
-- mark_all_read
------------------------------------------------------------------------------
PROCEDURE mark_all_read(
p_user_id IN NUMBER
) IS
BEGIN
UPDATE app_user_notifications
SET read_on = SYSTIMESTAMP
WHERE user_id = p_user_id
AND read_on IS NULL;


COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END mark_all_read;

------------------------------------------------------------------------------
-- unread_count
------------------------------------------------------------------------------
FUNCTION unread_count(
p_user_id IN NUMBER
) RETURN NUMBER IS
l_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_cnt
FROM app_user_notifications
WHERE user_id = p_user_id
AND read_on IS NULL;
RETURN l_cnt;
END unread_count;

------------------------------------------------------------------------------
-- purge_expired (exact-time)
------------------------------------------------------------------------------
PROCEDURE purge_expired IS
BEGIN
DELETE FROM app_user_notifications
WHERE expiration_date IS NOT NULL
AND expiration_date <= SYSDATE;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END purge_expired;

------------------------------------------------------------------------------
-- prepare_public_url
------------------------------------------------------------------------------
FUNCTION prepare_public_url(
p_raw IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
RETURN APEX_UTIL.PREPARE_URL(
p_url => p_raw,
p_checksum_type => 'SESSION',
p_plain_url => TRUE
);
END prepare_public_url;

------------------------------------------------------------------------------
------------------------------------------------------------------------------
PROCEDURE create_notification(
p_user_id IN NUMBER,
p_application_id IN NUMBER,
p_title IN VARCHAR2,
p_body IN VARCHAR2,
p_url IN VARCHAR2,
p_icon_class IN VARCHAR2,
p_expires_in_days IN PLS_INTEGER,
p_build_url IN BOOLEAN,
p_notification_id OUT NUMBER
) IS
l_url VARCHAR2(4000);
l_exp TIMESTAMP;
BEGIN
-- internal APEX links get prepared (checksum/session); external left as-is
IF p_build_url THEN
l_url := APEX_UTIL.PREPARE_URL(p_url => p_url);
ELSE
l_url := p_url;
END IF;


-- exact-time expiration (now + N days); NULL ? no expiry
IF p_expires_in_days IS NOT NULL AND p_expires_in_days > 0 THEN
l_exp := SYSTIMESTAMP + NUMTODSINTERVAL(p_expires_in_days, 'DAY');
ELSE
l_exp := NULL;
END IF;


INSERT INTO app_user_notifications (
user_id,
application_id,
title,
body,
url,
icon_class,
created_on,
expiration_date
) VALUES (
p_user_id,
p_application_id,
p_title,
p_body,
l_url,
p_icon_class,
SYSTIMESTAMP,
l_exp
)
RETURNING notification_id INTO p_notification_id;


COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END create_notification;

  ------------------------------------------------------------------------------
  -- notify_document_expiration
  ------------------------------------------------------------------------------
  PROCEDURE notify_document_expiration IS
    ----------------------------------------------------------------
    -- Cursor: documents expired or expiring in next 7 days
    ----------------------------------------------------------------
    CURSOR c_expiring_docs IS
      SELECT
        v.FND_FILES_ID    AS file_id,
        f.DESCRIPTION     AS document_name,
        v.FND_FILE_NAME   AS file_name,
        v.EXPIRE_DATE     AS expiration_date,
        e.EMPLOYEE_ID     AS employee_id,
        e.EMPLOYEE_NUMBER AS employee_number,
        e.FULL_NAME_EN    AS full_name
      FROM FND_FILES_REFERENCES v
      JOIN HCM_EMPLOYEES      e ON v.SOURCE_DOC_ID   = e.EMPLOYEE_ID
      JOIN FND_FILES_CHECKLIST f ON v.DOCUMENT_TYPE = f.FND_FILES_CHECKLIST_ID
      WHERE v.EXPIRE_DATE <= SYSDATE + 7
      ORDER BY v.EXPIRE_DATE;

    ----------------------------------------------------------------
    -- Targets: HR_SPECIALIST + HCM_ADMIN
    ----------------------------------------------------------------
    TYPE t_user_rec IS RECORD (
      user_id  HCM_USERS.user_id%TYPE,
      username HCM_USERS.username%TYPE
    );
    TYPE t_user_table IS TABLE OF t_user_rec INDEX BY PLS_INTEGER;

    v_user_list     t_user_table;
    v_user_count    PLS_INTEGER := 0;

    ----------------------------------------------------------------
    -- Vars
    ----------------------------------------------------------------
    v_app_id                 NUMBER := 158; -- make sure this matches your APEX app id
    v_notification_id        NUMBER;
    v_title                  VARCHAR2(200);
    v_body                   CLOB;
    v_days                   NUMBER;
    v_push_url               VARCHAR2(2000);
    v_in_app_url             VARCHAR2(2000);
    v_payload_json           CLOB;
    v_existing_flag          VARCHAR2(1);
    v_is_expired             VARCHAR2(1);
    v_docs_scanned           PLS_INTEGER := 0;
    v_notifications_created  PLS_INTEGER := 0;
    v_error_msg              VARCHAR2(1000);
  BEGIN
    -- load targets
    v_user_count := 0;
    FOR usr IN (
      SELECT DISTINCT ur.user_id, u.username
      FROM HCM_USER_ROLES ur
      JOIN HCM_ROLES  r ON ur.role_id = r.role_id
      JOIN HCM_USERS  u ON ur.user_id = u.user_id
      WHERE UPPER(r.role_name) IN ('HR_SPECIALIST','HCM_ADMIN')
    ) LOOP
      v_user_count := v_user_count + 1;
      v_user_list(v_user_count).user_id  := usr.user_id;
      v_user_list(v_user_count).username := usr.username;
    END LOOP;

    IF v_user_count = 0 THEN
      INSERT INTO notification_expiry_run_log (docs_scanned, notifications_sent, errors)
      VALUES (0, 0, NULL);
      COMMIT;
      RETURN;
    END IF;

    -- process documents
FOR doc_rec IN c_expiring_docs LOOP
  v_days := TRUNC(doc_rec.expiration_date) - TRUNC(SYSDATE);

  IF v_days < 0 THEN
    -- expired
    v_title := doc_rec.document_name || ' expired: ' || doc_rec.employee_number;
    v_body  := doc_rec.full_name || '''s ' || LOWER(doc_rec.document_name) ||
               ' expired on ' || TO_CHAR(doc_rec.expiration_date,'YYYY-MM-DD') ||
               ' (' || ABS(v_days) || ' ' ||
               CASE WHEN ABS(v_days)=1 THEN 'day' ELSE 'days' END || ' ago).'
               || ' File: ' || doc_rec.file_name || '.';

  ELSIF v_days = 0 THEN
    -- expires today
    v_title := doc_rec.document_name || ' expires today: ' || doc_rec.employee_number;
    v_body  := doc_rec.full_name || '''s ' || LOWER(doc_rec.document_name) ||
               ' expires today (' || TO_CHAR(doc_rec.expiration_date,'YYYY-MM-DD') || ').'
               || ' File: ' || doc_rec.file_name || '.';

  ELSE
    -- future
    v_title := doc_rec.document_name || ' expires in ' || v_days || ' ' ||
               CASE WHEN v_days=1 THEN 'day' ELSE 'days' END || ': ' ||
               doc_rec.employee_number;
    v_body  := doc_rec.full_name || '''s ' || LOWER(doc_rec.document_name) ||
               ' expires on ' || TO_CHAR(doc_rec.expiration_date,'YYYY-MM-DD') || '.'
               || ' File: ' || doc_rec.file_name || '.';
  END IF;
      v_in_app_url :=
           'f?p=' || 158 || ':18::CLEAR_CACHE_FOR_PAGE_18:::P18_EMPLOYEE_ID:' ||
           doc_rec.employee_id || '#attachments';

      v_push_url := APEX_UTIL.PREPARE_URL(
                      p_url           => v_in_app_url,
                      p_checksum_type => 'PUBLIC_BOOKMARK',
                      p_plain_url     => TRUE
                    );

      v_payload_json := JSON_OBJECT(
        'file_id'         VALUE doc_rec.file_id,
        'employee_id'     VALUE doc_rec.employee_id,
        'expiration_date' VALUE TO_CHAR(doc_rec.expiration_date, 'YYYY-MM-DD'),
        'employee_number' VALUE doc_rec.employee_number,
        'is_expired'      VALUE CASE WHEN v_is_expired = 'Y' THEN 'true' ELSE 'false' END
      );

      -- fan-out to users
      FOR i IN 1 .. v_user_count LOOP
        DECLARE
          v_target_user_id  NUMBER       := v_user_list(i).user_id;
          v_target_username VARCHAR2(100) := UPPER(TRIM(v_user_list(i).username));
        BEGIN
          -- dedupe
          BEGIN
            SELECT 'Y'
              INTO v_existing_flag
              FROM app_user_notifications n
             WHERE n.source_module       = 'DOCUMENT_EXPIRY'
               AND n.notification_type   = 'ALERT'
               AND n.user_id             = v_target_user_id
               AND JSON_VALUE(n.payload_json,'$.file_id') = TO_CHAR(doc_rec.file_id)
               AND TRUNC(TO_DATE(JSON_VALUE(n.payload_json,'$.expiration_date'),'YYYY-MM-DD'))
                   = TRUNC(doc_rec.expiration_date)
               AND ROWNUM = 1;
            CONTINUE;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              NULL;
          END;
          -- insert
          INSERT INTO app_user_notifications (
            application_id,
            user_id,
            title,
            body,
            url,
            icon_class,
            source_module,
            notification_type,
            priority,
            payload_json,
            expiration_date
          ) VALUES (
            v_app_id,
            v_target_user_id,
            v_title,
            v_body,
            v_in_app_url,
            'fa-paperclip',
            'DOCUMENT_EXPIRY',
            'ALERT',
            1,
            v_payload_json,
            SYSDATE + 90
          )
          RETURNING notification_id INTO v_notification_id;

          v_notifications_created := v_notifications_created + 1;

          -- push
          BEGIN
            APEX_PWA.SEND_PUSH_NOTIFICATION(
              p_application_id => v_app_id,
              p_user_name      => v_target_username,
              p_title          => v_title,
              p_body           => v_body,
              p_icon_url       => NULL,
              p_target_url     => v_push_url
            );
          EXCEPTION
            WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Push failed for user ' || v_target_username ||
                                   ' file_id ' || doc_rec.file_id || ': ' || SQLERRM);
          END;
        END;
      END LOOP; -- users
    END LOOP; -- docs

    INSERT INTO notification_expiry_run_log (docs_scanned, notifications_sent, errors)
    VALUES (v_docs_scanned, v_notifications_created, NULL);

    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_error_msg := SUBSTR(SQLERRM, 1, 1000);
      BEGIN
        INSERT INTO notification_expiry_run_log (docs_scanned, notifications_sent, errors)
        VALUES (v_docs_scanned, v_notifications_created, v_error_msg);
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN NULL;
      END;
      DBMS_OUTPUT.PUT_LINE('Error in notify_document_expiration: ' || v_error_msg);
      ROLLBACK;
  END notify_document_expiration;


------------------------------------------------------------------------------
  -- notify_contract_expiration
  ------------------------------------------------------------------------------
  PROCEDURE notify_contract_expiration IS
    ----------------------------------------------------------------
    -- Contracts already expired or expiring within 30 days
    ----------------------------------------------------------------
    CURSOR c_contracts IS
      SELECT
        wr.employee_id,
        wr.end_date,
        e.employee_number,
        e.full_name_en
      FROM HCM_WORK_RELATIONS wr
      JOIN HCM_EMPLOYEES     e ON wr.employee_id = e.employee_id
      WHERE wr.end_date <= SYSDATE + 30
      ORDER BY wr.end_date;

    TYPE t_user_rec IS RECORD (
      user_id  HCM_USERS.user_id%TYPE,
      username HCM_USERS.username%TYPE
    );
    TYPE t_user_tab IS TABLE OF t_user_rec INDEX BY PLS_INTEGER;

    v_users      t_user_tab;
    v_user_count PLS_INTEGER := 0;

    v_app_id           NUMBER := 158;
    v_notification_id  NUMBER;
    v_title            VARCHAR2(200);
    v_body             CLOB;
    v_days             NUMBER;
    v_in_app_url       VARCHAR2(2000);
    v_push_url         VARCHAR2(2000);
    v_payload_json     CLOB;
    v_existing_flag    VARCHAR2(1);
    v_is_expired       CHAR(1);
  BEGIN
    -- preload users
    FOR usr IN (
      SELECT DISTINCT ur.user_id,
                      UPPER(TRIM(hu.username)) AS username
      FROM HCM_USER_ROLES ur
      JOIN HCM_ROLES       r  ON ur.role_id = r.role_id
      JOIN HCM_USERS       hu ON ur.user_id = hu.user_id
      WHERE UPPER(r.role_name) IN ('HR_SPECIALIST','HCM_ADMIN')
    ) LOOP
      v_user_count := v_user_count + 1;
      v_users(v_user_count).user_id  := usr.user_id;
      v_users(v_user_count).username := usr.username;
    END LOOP;

    IF v_user_count = 0 THEN
      RETURN;
    END IF;

    -- process contracts
  FOR rec IN c_contracts LOOP
    v_days := TRUNC(rec.end_date) - TRUNC(SYSDATE);

    IF v_days < 0 THEN
      -- expired
      v_title := 'Contract expired: ' || rec.employee_number;
      v_body  := rec.full_name_en || '''s contract ended on '
                 || TO_CHAR(rec.end_date,'YYYY-MM-DD')
                 || ' (' || ABS(v_days) || ' '
                 || CASE WHEN ABS(v_days)=1 THEN 'day' ELSE 'days' END
                 || ' ago).';

    ELSIF v_days = 0 THEN
      -- expires today
      v_title := 'Contract expires today: ' || rec.employee_number;
      v_body  := rec.full_name_en || '''s contract ends today ('
                 || TO_CHAR(rec.end_date,'YYYY-MM-DD') || ').';

    ELSE
      -- future (keep it concise like your example)
      v_title := 'Contract expires in ' || v_days || ' '
                 || CASE WHEN v_days=1 THEN 'day' ELSE 'days' END
                 || ': ' || rec.employee_number;
      v_body  := rec.full_name_en || '''s contract ends on '
                 || TO_CHAR(rec.end_date,'YYYY-MM-DD') || '.';
    END IF;
     v_payload_json := JSON_OBJECT(
        'employee_id' VALUE rec.employee_id,
        'end_date'    VALUE TO_CHAR(rec.end_date, 'YYYY-MM-DD'),
        'is_expired'  VALUE CASE WHEN v_is_expired = 'Y' THEN 'true' ELSE 'false' END
      );

      FOR i IN 1 .. v_user_count LOOP
        DECLARE
          v_uid   NUMBER        := v_users(i).user_id;
          v_uname VARCHAR2(100) := UPPER(TRIM(v_users(i).username));
        BEGIN
          -- dedupe
          BEGIN
            SELECT 'Y'
              INTO v_existing_flag
              FROM app_user_notifications n
             WHERE n.source_module            = 'CONTRACT_EXPIRY'
               AND n.notification_type        = 'ALERT'
               AND n.user_id                  = v_uid
               AND JSON_VALUE(n.payload_json, '$.employee_id') = TO_CHAR(rec.employee_id)
               AND TRUNC(TO_DATE(JSON_VALUE(n.payload_json,'$.end_date'),'YYYY-MM-DD'))
                    = TRUNC(rec.end_date)
               AND ROWNUM = 1;
            CONTINUE;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              NULL;
          END;

          -- insert
          INSERT INTO app_user_notifications (
            application_id,
            user_id,
            title,
            body,
            url,
            icon_class,
            source_module,
            notification_type,
            priority,
            payload_json,
            expiration_date
          ) VALUES (
            v_app_id,
            v_uid,
            v_title,
            v_body,
            v_in_app_url,
            'fa fa-calendar-alt',
            'CONTRACT_EXPIRY',
            'ALERT',
            1,
            v_payload_json,
            SYSDATE + 90
          )
          RETURNING notification_id INTO v_notification_id;
          -- push
          BEGIN
            APEX_PWA.SEND_PUSH_NOTIFICATION(
              p_application_id => v_app_id,
              p_user_name      => v_uname,
              p_title          => v_title,
              p_body           => v_body,
              p_icon_url       => NULL,
              p_target_url     => v_push_url
            );
          EXCEPTION
            WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Push error for ' || v_uname || ': ' || SQLERRM);
          END;
        END;
      END LOOP;
    END LOOP;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error in notify_contract_expiration: ' || SQLERRM);
      ROLLBACK;
  END notify_contract_expiration;

END app_notifications;
/

Scheduler jobs

BEGIN
-- Daily purge at 02:00
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'APP_NOTIF_PURGE_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN app_notifications.purge_expired; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
enabled => TRUE,
comments => 'Purge expired notifications'
);


-- Documents expiring (runs daily 02:00)
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_NOTIFY_DOCUMENT_EXPIRATION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN APP_NOTIFICATIONS.NOTIFY_DOCUMENT_EXPIRATION; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
enabled => TRUE,
comments => 'Notify HR of docs expired/expiring in 7 days'
);


-- Contracts expiring (runs daily 02:00)
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_NOTIFY_CONTRACT_EXPIRATION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN APP_NOTIFICATIONS.NOTIFY_CONTRACT_EXPIRATION; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
enabled => TRUE,
comments => 'Notify HR of contracts expired/expiring in 30 days'
);
END;
/

How the backend plugs into the UI (quick map)

Bell badge → GET_UNREAD_COUNT → app_notifications.unread_count(:G_USER_ID).
Click a notification → inline JS calls MARK_READ → app_notifications.mark_read(...) then navigates.
Mark all → MARK_ALL_READ → app_notifications.mark_all_read(:G_USER_ID); then refresh region + badge.
Links → prepare_public_url(url) keeps SESSION checksum and avoids logout; Rejoin Sessions = Enabled as safety net.
Jobs → daily purge_expired, document & contract notifiers.

APEX pages (end user)

0) Pre-reqs

app_notifications package is compiled (we use unread_count, mark_read, mark_all_read, and prepare_public_url).
Security → Rejoin Sessions = Enabled (prevents logout when clicking a notification link).
Global app process GET_UNREAD_COUNT exists (code below).

Why this design

We ship the bell via Static Files (CSS/JS) and a Navigation Bar entry so it’s available on every page. The app-level Ajax GET_UNREAD_COUNT feeds the badge without any page-level wiring.

Navigation Bar list entry:

List: Desktop Navigation Bar
Image/Class: fa fa-bell
Attributes: id="navNotif"
Target Page: 268 (Notifications)

no unread notifications

unread notifications

Static Application File - CSS:

/* keep the label hidden by default; we’ll show it via JS when unread > 0 */
#navNotif ~ .t-Button-label,
#navNotif ~ .t-NavBar-itemLabel,
#navNotif ~ .t-NavigationBar-itemLabel { display: none; }
/* default: white bell, white number (if any) */
#navNotif .notif-badge { color: #ffffff; }
/* when JS adds has-unread: make bell + number red */
#navNotif.has-unread,
#navNotif.has-unread .notif-badge { color: #e31b0c !important; }

Static Application File - JS:

(function () {
var link = document.getElementById('navNotif');
if (!link) return;


// add the badge span once
if (!link.querySelector('.notif-badge')) {
var b = document.createElement('span');
b.className = 'notif-badge';
b.id = 'notifCount';
link.appendChild(b);
}


function refreshBadge() {
apex.server.process(
"GET_UNREAD_COUNT",
{},
{
dataType: "text",
success: function (resp) {
var n = parseInt(resp, 10);
if (isNaN(n)) n = 0;


var badge = document.getElementById('notifCount');
if (!badge) return;


if (n > 0) {
badge.textContent = (n > 99 ? "99+" : String(n));
link.classList.add('has-unread');
} else {
badge.textContent = "";
link.classList.remove('has-unread');
}
}
}
);
}


// duplicate-guard + hide label in various UT markups
if (!link.querySelector('.notif-badge')) {
var b2 = document.createElement('span');
b2.className = 'notif-badge';
b2.id = 'notifCount';
link.appendChild(b2);
}
var a = link.closest('a');
if (a) {
var lbl = a.querySelector('.t-Button-label, .t-NavBar-itemLabel, .t-NavigationBar-itemLabel');
if (lbl) lbl.style.display = 'none';
}


// expose for other pages
window.refreshBadge = refreshBadge;


refreshBadge();
setInterval(refreshBadge, 60000);
})();

App process - GET_UNREAD_COUNT

BEGIN
HTP.P( app_notifications.unread_count( p_user_id => :G_USER_ID ) );
END;

2) Notifications page (Page 268)

Mode: Modal Dialog → Template: Drawer
Why: keeps the user in context; fast peek/act UX.
Tip: If 25% width feels tight, set Template Options → Size = Small (or adjust width in the dialog section).

2.1 Classic Report “Notifications”

Region Type: Classic Report
Region Template: Blank with Attributes
Region Static ID: rptNotifications
Report Appearance Template: create a copy of Comments named Notifications (see row template below).
Template Options: Style = Speech Bubbles
Important: In the Classic Report columns, set Escape Special Characters = Yes for COMMENT_TEXT unless you intentionally store trusted HTML.

Row template (for the Notifications appearance)

<li class="t-Comments-item #ROW_CLASS#">
  <div class="t-Comments-icon">
    <span class="notif-dot" aria-hidden="true"></span>
    <div class="t-Comments-userIcon" aria-hidden="true">
      <i class="#USER_ICON#"></i>
    </div>
  </div>
  <div class="t-Comments-body">
    <a href="#URL#"
       class="notification-link"
       onclick="
         (function(a){
           if(a.dataset.busy){return false;}
           a.dataset.busy='1';
           apex.server.process(
             'MARK_READ',
             { x01: '#NOTIFICATION_ID#' },
             {
               dataType: 'text',
               success: function(){ apex.navigation.dialog.close(true, a.href); },
               error:   function(){ apex.navigation.dialog.close(true, a.href); }
             }
           );
         })(this);
         return false;
       ">
      <div class="t-Comments-comment">
        <strong class="notification-title">#USER_NAME#</strong><br>
        #COMMENT_TEXT#
      </div>
      <div class="t-Comments-info">
        <span class="t-Comments-date">#COMMENT_DATE#</span>
      </div>
    </a>
  </div>
</li>

Region SQL (session-safe URLs)

prepare_public_url(url) wraps APEX_UTIL.PREPARE_URL with p_checksum_type => 'SESSION' and p_plain_url => TRUE, so clicking a notification keeps the user in the same session (no logout).

select NOTIFICATION_ID,
APPLICATION_ID,
TITLE AS USER_NAME,
BODY AS COMMENT_TEXT,
app_notifications.prepare_public_url(URL) AS URL,
apex_util.get_since(created_on) AS COMMENT_DATE,
ICON_CLASS AS USER_ICON,
NOTIFICATION_TYPE,
PAYLOAD_JSON,
EXPIRATION_DATE,
SOURCE_MODULE,
PRIORITY,
USER_ID,
CASE WHEN read_on IS NULL THEN 'is-unread' ELSE 'is-read' END AS ROW_CLASS
from APP_USER_NOTIFICATIONS
WHERE USER_ID = :G_USER_ID
order by created_on DESC, NOTIFICATION_ID DESC

Optional - add this to When no data found message

<div class="notif-empty">
  <i class="fa fa-check-circle" aria-hidden="true"></i>
  <div class="notif-empty-title">You're all caught up</div>
  <div class="notif-empty-sub">No new notifications right now.</div>
  <button type="button"
          class="t-Button t-Button--link t-Button--small t-Button--iconLeft"
          onclick="apex.region('rptNotifications').refresh()">
    <span class="t-Icon fa fa-rotate-right" aria-hidden="true"></span>
    <span class="t-Button-label">Refresh</span>
  </button>
</div>

2.3 Page on-demand processes

MARK_READ

BEGIN
  app_notifications.mark_read(
    p_notification_id => TO_NUMBER(apex_application.g_x01),
    p_user_id         => :G_USER_ID
  );
  HTP.P('OK');
END;

MARK_ALL_READ

BEGIN
  app_notifications.mark_all_read(
    p_user_id => :G_USER_ID
  );
  HTP.P('OK');
END;

2.4 Execute-when-Page-Loads (JS)

(function(){
var btn = document.getElementById('btnMarkAll');
if(!btn) return;


btn.addEventListener('click', function(){
if (btn.dataset.busy) return;
btn.dataset.busy = "1";


apex.server.process(
'MARK_ALL_READ',
{},
{
dataType: 'text',
success: function(){
// refresh the list
apex.region('rptNotifications').refresh();


// update the navbar badge if present
var b = document.getElementById('notifCount');
if (b) {
// if you already have a global badge refresher, call it:
if (typeof refreshBadge === 'function') { try { refreshBadge(); } catch(e){} }
else { b.textContent = ''; }
}
},
complete: function(){
btn.dataset.busy = "";
}
}
);
});
})();

2.5 Inline CSS (page)

/* Title weight */
.notification-title {
  font-weight: 600;    /* 400 = normal, 600 = semi-bold, 700 = bold */
}

/* =========================================================================================
   Bubble Settings
   ========================================================================================= */
/* Position the dot relative to the avatar column */
.t-Comments-icon { position: relative; }

/* Dot sits to the LEFT of the avatar circle, vertically centered */
.t-Comments-item .notif-dot {
  display: none;                 /* shown only for unread */
  position: absolute;
  top: 50%;
  left: -8px;                    /* push it outside to the far-left */
  transform: translateY(-50%);
  width: 8px; height: 8px;
  border-radius: 50%;
  background: #3b7ddd;           /* your brand color */
  box-shadow: 0 0 0 2px #fff;    /* small white ring so it pops */
  pointer-events: none;
}

/* Only show the dot for unread rows */
.t-Comments-item.is-unread .notif-dot { display: block; }

/* Revert tint to the COMMENT BUBBLE only (not the whole row) */
.t-Comments-item.is-unread .t-Comments-comment { background: #eef6ff; }

/* =========================================================================================
   You’re all caught up
   ========================================================================================= */

/* remove the default “no data” box spacing */
#rptNotifications .t-Report-noDataMsg {
  background: transparent;
  border: 0;
  padding: 0;
}

/* centered, tidy empty state */
#rptNotifications .notif-empty {
  display: flex;
  flex-direction: column;
  align-items: center;
  gap: 8px;
  padding: 24px 12px;
  text-align: center;
}

#rptNotifications .notif-empty .fa-check-circle {
  font-size: 28px;
  color: #16a34a;   /* green check – tweak if you prefer */
}

#rptNotifications .notif-empty-title {
  font-weight: 600;
  color: #111827;
}

#rptNotifications .notif-empty-sub {
  font-size: 12px;
  color: #6b7280;
}

#rptNotifications .notif-empty .t-Button { margin-top: 6px; }

3) Why each piece matters (recap)

Navbar bell: globally visible state; one JS file keeps the badge in sync everywhere.
App process (GET_UNREAD_COUNT): single canonical source for the badge; no per-page duplication.
Dialog page: fast “peek and act” UX; doesn’t yank the user away from their current task.
Classic Report + custom row template: simplest way to render a comment-style inbox; the anchor’s inline MARK_READ call means click = read + navigate.
prepare_public_url() + Rejoin Sessions: prevents the “click → logout” issue by keeping the same session and a valid checksum.
Two on-demand processes (MARK_READ, MARK_ALL_READ): all state changes happen server-side; client stays tiny and reliable.
Inline CSS: small “unread” dot, subtle tint, and a tidy empty state for good UX.

4) Nice-to-haves you can add later

Keyboard/ARIA: add role="list" to the UL and role="listitem" to LI, and aria-live="polite" on the badge if you want SRs to announce changes.
Auto refresh: add apex.region('rptNotifications').refresh() on a timer if you want the list to update while the drawer is open.
Rate limit: disable the Mark-All button until the previous call completes (we already do with data-busy).

That’s the whole pattern: insert once, render everywhere. A single queue table + one package gives you a clean backbone for in-app notifications that doubles as a to-do stream. From here you can:

add more “notifiers” (any module that can insert a row can participate),
expand channels (keep in-app as the default; add PWA push today, email/SMS later),
and layer on observability (simple run-logs, retries, even a dead-letter table if you need it).

Steal what you like, adapt the rest, and ship fast—your users will feel the difference the first time the bell lights up.

Building a Production‑Ready Notification System in Oracle APEX (No Plugins)

Introduction

Top view (at a glance)

Architecture (high‑level)

Why this shape

Backend (Data model + Core package)

Data model - `APP_USER_NOTIFICATIONS`

Why these columns (quick map)

Why these indexes

Why the trigger

Core package: `app_notifications` (spec & body)

Spec

What each API does (and why)

Body

Scheduler jobs

How the backend plugs into the UI (quick map)

APEX pages (end user)

0) Pre-reqs

Why this design

2) Notifications page (Page 268)

2.1 Classic Report “Notifications”

Region SQL (session-safe URLs)

2.3 Page on-demand processes

3) Why each piece matters (recap)

4) Nice-to-haves you can add later

Comments

More from this blog

How to added a Spline 3D designs to an Oracle APEX page

Oracle APEX Tutorial: How to Trigger On-Demand Processes from Oracle APEX Menu Popup Lists

Command Palette

Introduction

Top view (at a glance)

Architecture (high‑level)

Why this shape

Backend (Data model + Core package)

Data model - APP_USER_NOTIFICATIONS

Why these columns (quick map)

Why these indexes

Why the trigger

Core package: app_notifications (spec & body)

Spec

What each API does (and why)

Body

Scheduler jobs

How the backend plugs into the UI (quick map)

APEX pages (end user)

0) Pre-reqs

Why this design

2) Notifications page (Page 268)

2.1 Classic Report “Notifications”

Region SQL (session-safe URLs)

2.3 Page on-demand processes

3) Why each piece matters (recap)

4) Nice-to-haves you can add later

Comments

More from this blog

Data model - `APP_USER_NOTIFICATIONS`

Core package: `app_notifications` (spec & body)