Search Results get_approved_date_pll




Overview
Explore in AI Chat

Overview

POA_OLTP_GENERIC_PKG is a public PL/SQL package owned by the APPS schema in Oracle E-Business Suite, classified under the Purchasing (POA) module as an "OTHER" API. Its declared purpose is to supply approval-date derivation logic for archived Purchasing transactional entities. In Oracle EBS 12.1.1 and 12.2.2, the Advanced Procurement and Purchasing archival components move historical purchasing documents — headers, releases, and line locations — into companion archive tables. The original operational records often carry incomplete or implicitly derived approval timestamps, and POA_OLTP_GENERIC_PKG provides a centralized, deterministic function to resolve the effective approval date for a given archived record.

The package is declared with AUTHID CURRENT_USER, meaning its procedures and functions execute with the privileges of the invoking user rather than the package owner. This design choice lets the package be called safely from multiple application contexts, including concurrent programs and custom imports, without exposing the APPS schema's object privileges to every caller. The package body's header references a 2002 source revision (115.3), indicating a long-stable interface that has persisted across multiple EBS releases.

Key Procedures and Functions

The package exposes exactly three public functions, all of which return a DATE value representing the approved date for an archived purchasing document:

  • GET_APPROVED_DATE_POH — Resolves the approved date for an archived purchase order header. It accepts the document's creation date and the purchase order header identifier as inputs and returns the effective approval date.
  • GET_APPROVED_DATE_POR — Resolves the approved date for an archived purchase order release. It accepts the creation date and the purchase order release identifier and returns the corresponding approval date.
  • GET_APPROVED_DATE_PLL — Resolves the approved date for an archived purchase order line location. It accepts the creation date and the line location identifier and returns the approval date for that line-level shipment record.

Each function carries the PRAGMA RESTRICT_REFERENCES(..., WNDS) directive, guaranteeing that the function writes no database state. This makes the functions safe for use inside SQL statements and views, and allows the PL/SQL optimizer to treat them as read-only expressions. The p_creation_date parameter appears across all three functions, suggesting it serves as a fallback when an explicit approval timestamp is absent from the archive record.

Tables Accessed

The package reads from three archive tables through APPS synonyms:

Because the package is declared WNDS, access is strictly read-only. The archive tables are populated by the Purchasing archival concurrent programs and are structured as mirrored containers of the live PO tables, so the package performs no DML against them.

Usage Notes

POA_OLTP_GENERIC_PKG is referenced by eleven other packages within the EBS codebase, which indicates it is a shared utility layer rather than an end-user-facing API. It is typically invoked by archival and reporting components when approval timestamps must be reconstructed for historical purchase orders, releases, and line locations, particularly in reporting extracts and data migration routines where the original approval date is not directly stored.

Callers should invoke the appropriate function based on the entity type being processed and pass the record's creation date and primary identifier. Because the functions are WNDS-compliant, they may also be embedded in SQL queries and custom views. There is no documented exception handling or commit behavior, consistent with read-only utility packages. Organizations upgrading from 12.1.1 to 12.2.2 can rely on the interface remaining stable, as no signature changes are documented.