Results for “pon_supplier_access_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PON.PON_SUPPLIER_ACCESS is a transactional table in the Oracle E-Business Suite Sourcing (PON) schema that records the lock status of a supplier within a negotiation. It tracks whether a specific supplier has been locked out of a negotiation or granted access to it, together with the audit trail of who performed the action and why. The table stores one row per lock or unlock event, using the ACTIVE_FLAG column to identify the most current status for a given supplier while retaining the full historical sequence of changes for the negotiation series.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification indicates this object behaves as a link table. It resolves the many-to-many relationship that arises across negotiations (auction headers), suppliers represented as trading partners, and the buyer contacts who perform lock or grant actions. Business key candidates are anchored on the unique index described below.

Key Information Stored

The documented physical schema contains 13 columns. The most significant are:

• AUCTION_HEADER_ID_ORIG_AMEND — The identifier of the first negotiation in a series of amendments; when the record belongs to the initial negotiation, it equals AUCTION_HEADER_ID.
• SUPPLIER_TRADING_PARTNER_ID — The supplier (trading partner) whose access is being locked or granted.
• LOCK_DATE — The date and time the lock or grant action was executed.
• BUYER_TP_CONTACT_ID — The buyer contact who performed the lock or access-granting action.
• LOCK_STATUS — Flag indicating LOCK (supplier locked out) or UNLOCK (supplier granted access).
• ACTIVE_FLAG — Marks the most current record; superseded rows for the same supplier carry an 'N'.
• AUCTION_HEADER_ID — The specific negotiation in which the lock was initiated, which may differ from the original amendment header.
• LOCK_REASON — Free-form (up to 4000 characters) justification entered by the buyer.

The surrogate primary key is PON_SUPPLIER_ACCESS_PK1, composed of (AUCTION_HEADER_ID_ORIG_AMEND, SUPPLIER_TRADING_PARTNER_ID, LOCK_DATE). The unique index PON_SUPPLIER_ACCESS_U1 carries the identical three-column business-key candidate set (AUCTION_HEADER_ID_ORIG_AMEND, SUPPLIER_TRADING_PARTNER_ID, LOCK_DATE), meaning a supplier can hold only one lock or access event per timestamp per negotiation series. Standard Who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) provide the audit envelope.

Common Use Cases and Queries

Typical reporting scenarios include identifying currently locked suppliers, reconstructing the history of access decisions across an amendment chain, and auditing buyer activity by lock reason. Because only the ACTIVE_FLAG = 'Y' row reflects the current state, most operational queries filter on this flag.

To list current locks for a negotiation series:

• SELECT supplier_trading_partner_id, lock_date, lock_status, lock_reason FROM pon_supplier_access WHERE auction_header_id_orig_amend = :p_header_id AND active_flag = 'Y';

To trace the full history of a single supplier's access changes:

• SELECT lock_date, lock_status, buyer_tp_contact_id, lock_reason FROM pon_supplier_access WHERE auction_header_id_orig_amend = :p_header_id AND supplier_trading_partner_id = :p_supplier_id ORDER BY lock_date DESC;

The unique index PON_SUPPLIER_ACCESS_U1 supports efficient lookups by these three leading columns.

Related Objects

The FK relationships documented in the metadata establish the following joins:

• PON.PON_AUCTION_HEADERS_ALL — referenced twice, via AUCTION_HEADER_ID_ORIG_AMEND and AUCTION_HEADER_ID, supplying negotiation context.
• HZ_PARTIES — referenced via SUPPLIER_TRADING_PARTNER_ID for the supplier identity.
• HZ_PARTIES — referenced via BUYER_TP_CONTACT_ID for the acting buyer contact.

Sourcing UI and negotiation management pages read this table to enforce supplier access restrictions, and concurrent programs that manage amendment series rely on the ACTIVE_FLAG pattern to preserve historical state.