Search Results trend_sql

Overview

APPS.POA_DBI_NCP_PKG is a PL/SQL package owned by the APPS schema that belongs to the Oracle Procurement Intelligence (POA) product family, specifically the Daily Business Intelligence (DBI) subsystem. Its role is to supply dynamically generated SQL statements to the Oracle EBS Daily Business Intelligence reporting framework for the Procurement "Non-Catalog Purchases" (NCP) business area. DBI pages are rendered through the BIS_PMV (Page Metadata Viewer) engine, which does not hard-code report queries. Instead, it calls registered PL/SQL callback packages at runtime, passing page and parameter context, and expecting a SQL text fragment plus a description of the columns that the query will return. POA_DBI_NCP_PKG is one such callback provider.

The package header records the source revision as $Header: poadbincps.pls 115.3 2003/11/13 14:25:30 sriswami noship $, indicating a relatively early DBI code line. The declaration uses AUTHID CURRENT_USER, so all unqualified object references in the generated SQL are resolved using the privileges of the invoking session rather than those of the package owner. This is standard practice for DBI data-generation packages, since the executing user must already hold the DBI reporting responsibilities and any associated grants.

Key Procedures and Functions

The package exposes two public procedures, both of which follow the same callback contract used throughout the BIS_PMV framework.

• STATUS_SQL — Produces the SQL used for the status (point-in-time or summary) view of the Non-Catalog Purchases DBI page. Given a parameter table, it returns the query text and the query-attribute metadata that describes the result set to the reporting engine.
• TREND_SQL — Produces the SQL used for the trend (period-over-period) view of the same NCP page. This is the procedure associated with the trend_sql search term. It follows the identical calling signature as STATUS_SQL, receiving page parameters and returning both the SQL string and its attribute definition.

Both procedures share the parameter style p_param in BIS_PMV_PAGE_PARAMETER_TBL with two OUT NOCOPY parameters of types VARCHAR2 and BIS_QUERY_ATTRIBUTES_TBL. No other procedures or functions are documented in the package specification.

Tables Accessed

The ETRM metadata records a single referenced table, accessed through an APPS synonym: PLITBLM. This is the standard Oracle Applications table used to persist multi-lingual (translated) display strings. DBI callback packages consult it to obtain language-specific labels, prompt text, and column headings for the generated report. No transactional purchasing tables are listed as directly referenced by this package; the actual fact and dimension queries are assembled as dynamic SQL text and executed later by the BIS_PMV engine, which is why they do not appear in the static dependency list.

Usage Notes

The package is invoked indirectly. It is not called from Oracle Forms or from user-written concurrent programs. Instead, it is registered as the data source for one or more DBI page regions, and the BIS_PMV runtime invokes STATUS_SQL or TREND_SQL when the corresponding page region is rendered, refreshed, or scheduled by a DBI collection/refresh program.

Because both procedures return SQL as text rather than result data, the pattern is useful for custom DBI regions: the caller supplies the parameter collection representing the user's page selections (ledger, operating unit, period range, and so on) and receives back a complete query and its column contract. Developers extending NCP reporting should treat these procedures as framework callbacks and not as general-purpose APIs. Any direct invocation must supply a correctly populated BIS_PMV_PAGE_PARAMETER_TBL, and must be prepared to handle the dynamic SQL and attribute collection returned. Because the package is AUTHID CURRENT_USER, the calling session's privileges determine visibility of the underlying DBI views and PLITBLM.

Finally, the metadata shows zero other packages referencing POA_DBI_NCP_PKG, confirming it sits at the edge of the dependency graph as a leaf-level provider consumed only by the DBI runtime engine rather than by other application code.