Search Results pon_umbrella_programs




Overview
Explore in AI Chat

Overview

The PON_UMBRELLA_PROGRAMS table resides in the PON schema, which supports the Oracle Sourcing module within Oracle E-Business Suite. It serves as the master repository for umbrella programs — negotiated purchasing agreements that establish ceiling amounts, validity periods, and currency terms under which downstream procurement and sourcing documents operate. In practice, an umbrella program acts as a governing envelope: purchase orders, sourcing auctions, and draft or interface header records reference a single umbrella program to inherit its commercial constraints, most notably the ceiling amount and effective date range.

From a Data Vault modeling perspective, the mined heuristic classification of this table is standalone. It has no outbound foreign keys of its own; instead, it functions as a hub-like reference or lookup table from which numerous transactional tables draw a shared business concept. Its primary key, PON_UMBRELLA_PROGRAMS_PK, is defined on the UMBRELLA_PROGRAM_ID column, making that surrogate identifier the anchor for all dependent references.

Key Information Stored

The table carries 31 documented columns. The most operationally significant are:

  • UMBRELLA_PROGRAM_ID — surrogate primary key, system-generated identifier.
  • UMBRELLA_PROGRAM_NAME — the human-readable business name of the program; the principal business-key candidate.
  • DESCRIPTION — free-text elaboration of the program's purpose and scope.
  • START_DATE and END_DATE — define the active validity window during which associated documents may reference the program.
  • CURRENCY_CODE — the currency in which the ceiling amount is denominated.
  • CEILING_AMOUNT — the maximum committed spend permitted under the program.
  • LANGUAGE and SOURCE_LANG — language of the stored descriptive text and its source language, supporting multilingual installations.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Oracle WHO-column audit trail.
  • CONTEXT, ATTRIBUTE_CATEGORY, ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF/flexfield block for customer-specific extensions.

The business-key candidate is UMBRELLA_PROGRAM_NAME, though the metadata documents only the primary key constraint explicitly. The surrogate ID should always be used in joins; the name is suited to lookup and display.

Common Use Cases and Queries

Typical usage involves validating that a purchasing document references a currently effective program and that cumulative spend remains within the ceiling. A common reporting query retrieves active programs:

  • Listing programs effective on a given date: SELECT umbrella_program_id, umbrella_program_name, ceiling_amount, currency_code FROM pon_umbrella_programs WHERE TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, TRUNC(SYSDATE));
  • Resolving a purchase order's governing program: join po_headers_all to pon_umbrella_programs on umbrella_program_id to expose the program name and ceiling alongside the order.
  • Exposure analysis: aggregate committed amounts from po_headers_all grouped by umbrella_program_id and compare against ceiling_amount.
  • Sourcing context: join pon_auction_headers_all to attribute negotiated auctions to their umbrella program.
  • Integration monitoring: inspect po_headers_interface rows for unresolved or invalid umbrella_program_id values before import.

Related Objects

Five dependent tables reference this table through the UMBRELLA_PROGRAM_ID foreign key, spanning both transactional and interface layers:

These relationships confirm the table's role as a shared reference hub within the procurement and sourcing flow, ensuring consistent ceiling and validity enforcement across purchase orders and auctions alike.