Search Results ws_get_amount




Overview
Explore in AI Chat

Overview

The APPS.PSB_DISCOVERER_FUNCTIONS package is a public PL/SQL package in Oracle E-Business Suite that supports Oracle Discoverer-based reporting and analysis against data originating from the Public Sector Budgeting (PSB) modules. The package is declared with AUTHID CURRENT_USER, meaning that its functions execute with the privileges of the invoking session rather than the privileges of the APPS owner; this is consistent with its role as a Discoverer-facing utility package where end-user reporting sessions must resolve object references through APPS synonyms under their own security context.

The package exposes a small, purpose-built API for translating raw budgeting figures into Discoverer-ready values. Its name and contents indicate that it serves primarily as a computation and lookup layer for workbook calculations that cannot be expressed directly in Discoverer SQL. The header comment ($Header: PSBVDISS.pls 115.2 ...) confirms the file is part of the PSB views/Discoverer family and was last shipped at version 115.2.

Key Procedures and Functions

ETRM documents two callable program units in this package:

  • WS_GET_AMOUNT — A FUNCTION returning a NUMBER. It computes an amount based on stage sequencing and a year-to-date amount. Its parameters convey a required stage sequence, a start stage sequence, a current stage sequence, and a year-to-date (YTD) amount. The function therefore appears to derive a period- or stage-specific budget amount from cumulative figures, which is a common requirement when Discoverer workbooks display budget by stage rather than as an outright YTD total. It is declared with PRAGMA RESTRICT_REFERENCES(..., WNDS, WNPS), meaning it writes neither database state nor package state — a purity guarantee required for functions called from SQL inside Discoverer queries.
  • GET_GL_BALANCE — A FUNCTION returning a NUMBER. It retrieves a General Ledger balance given a revision type, balance type, set of books, XBC-enabled flag, GL period name, GL budget version, currency code, and code combination ID. In practice this function resolves a balance for a specified account combination and period, supporting reconciliation between PSB budgeting data and GL actuals or budgets within Discoverer reports. Note that the source excerpt places the RESTRICT_REFERENCES pragma referencing ws_get_amount immediately after this declaration; this is an artifact of the original file rather than a reflection of GET_GL_BALANCE's own purity.

Tables Accessed

The ETRM metadata does not enumerate specific tables referenced through APPS synonyms for this package. Based on the function signatures, GET_GL_BALANCE necessarily queries GL balance and accounting period data (such as GL_BALANCES and GL_CODE_COMBINATIONS) keyed by set of books, period name, currency, and code combination. WS_GET_AMOUNT operates entirely on its scalar inputs and, subject to its purity pragmas, does not read or write database tables. Because the package runs AUTHID CURRENT_USER, all such references must resolve through APPS synonyms or be fully qualified to schemas visible to the calling session.

Usage Notes

This package is designed to be invoked from Oracle Discoverer workbooks, typically as a calculation or function call within the workbook's SQL or as a registered database function exposed to Discoverer end users. Because both entries are functions returning NUMBER and WS_GET_AMOUNT carries WNDS/WNPS purity assertions, they are safe for use in SQL SELECT lists. No other packages in the environment reference PSB_DISCOVERER_FUNCTIONS according to ETRM, indicating it is a leaf-level utility rather than a shared library. Sites may also call these functions from custom concurrent programs, forms personalizations, or ad hoc SQL that mimics Discoverer calculations; however, because the package assumes the caller's identity and privileges, appropriate grants and synonym availability must be maintained for any such usage.