Search Results rej_rsn_sql




Overview
Explore in AI Chat

Overview

APPS.POA_DBI_REJ_PKG is a PL/SQL package belonging to the Oracle E-Business Suite Procurement (POA) module, specifically within the DBI (Daily Business Intelligence) reporting subsystem. It operates under AUTHID CURRENT_USER, meaning its SQL statements execute with the privileges of the invoking user rather than the package owner, which is the standard security model for DBI-related packages that assemble dynamic reporting queries at runtime. The package's header, first delivered as version 115.0 dated October 2003 during the 11i development cycle, indicates it predates the 12.1.1 and 12.2.2 releases and has been carried forward largely unchanged into the R12 codebase.

The central purpose of the package is to construct custom SQL statements dynamically in response to parameters supplied by a DBI reporting page. This is a well-established DBI pattern: a reporting page calls a package entry point, passes a table of page-level parameters, and receives back a fully formed SQL text plus a table of query attributes describing how that SQL should be rendered. POA_DBI_REJ_PKG specialises this pattern for rejection-related procurement data, supplying the query text that drives rejection reason and rejection status analytics.

Key Procedures and Functions

The ETRM metadata documents two public procedures in the package specification, both conforming to the standard DBI dynamic-query contract. Parameter lists are deliberately omitted here and only the documented roles are described.

  • STATUS_SQL — The entry point returned by the user's search for "status_sql". As indicated by its header comment block, it accepts a parameter table (P_PARAM), and returns the generated SQL statement through an OUT VARCHAR2 variable (X_CUSTOM_SQL) together with a table of query attributes (X_CUSTOM_OUTPUT). The comments note that all input parameters are passed via the parameter table, and that the resulting SQL text is passed back on X_CUSTOM_SQL. It is a procedure rather than a function, so all outputs are delivered through NOCOPY OUT parameters.
  • REJ_RSN_SQL — A sibling procedure following the same contract and, per the header comments, the same P_PARAM / X_CUSTOM_SQL / X_CUSTOM_OUTPUT signature pattern. Its name indicates it generates the SQL that underpins rejection-reason analysis, complementing the status-oriented query produced by STATUS_SQL. The two procedures together give the DBI rejection page both a status-centric and a reason-centric query option.

Tables Accessed

The only documented table reference associated with this package, via an APPS synonym, is PLITBLM. This is an Oracle-supplied intermediate/PLSQL utility table used in dynamic SQL construction, consistent with the package's role of assembling SQL text at runtime rather than performing direct transactional DML. No base application tables are named in the documentation excerpt; the actual tables touched by the generated SQL are resolved only when the returned statement is executed by the DBI reporting engine, not by the package itself. There are no documented writes to business data.

Usage Notes

This package is not intended for direct invocation from forms or concurrent programs. It is invoked internally by the Daily Business Intelligence reporting framework, which calls the procedure, supplies the parameter table drawn from the active page context, then takes the returned SQL string and query attributes and submits them to the reporting engine for execution. The AUTHID CURRENT_USER clause means the caller's schema must hold the necessary privileges on any underlying objects referenced by the generated SQL.

Because the package is a DBI helper, customisations should be avoided; Oracle supports extending DBI queries through the documented BIS custom-SQL hooks rather than editing this package. No other application packages are documented as referencing it, reinforcing that its sole consumer is the DBI runtime itself.