Search Results prj_svng_trend_sql

Overview

POA_DBI_NEG_PKG is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite. Its classification in the ETRM repository is OTHER, indicating that it is not a published public API but an internal helper object belonging to the Oracle Procurement/Purchasing Analytics (POA) family of database objects. The package supports the Procurement Dashboard and related Business Intelligence reporting pages by supplying the SQL text and filter logic used to populate the negotiating and sourcing analytics regions of those dashboards.

The package is tightly coupled with the POA_DBI utility infrastructure. It references POA_DBI_SUTIL_PKG (a settings/utilization helper), POA_DBI_TEMPLATE_PKG (the dashboard template renderer), and POA_DBI_UTIL_PKG (general dashboard utilities), together with the dashboard parameter structures BIS_PMV_PAGE_PARAMETER_REC, BIS_PMV_PAGE_PARAMETER_TBL, and BIS_QUERY_ATTRIBUTES_TBL. This dependency pattern confirms that POA_DBI_NEG_PKG acts as a query provider: each function returns a SQL statement string, which the template package formats and executes against the dashboard's page parameter set. FND_GLOBAL is referenced to resolve the runtime session context (user, responsibility, and organization) used to secure the returned queries.

Key Procedures and Functions

The package exposes twelve documented program units. Ten are SQL-generator functions, each returning the text of a specific analytical query:

• STATUS_SQL — returns the query behind the negotiation status summary region.
• AWD_STATUS_SQL — returns the query presenting awarded negotiation status.
• REALIZED_STATUS_SQL — returns the query for realized (completed) negotiation status.
• AVG_CYCLE_TIME_SQL — returns the query computing average negotiation cycle time.
• AWD_TREND_SQL — returns the award volume/value trend query.
• AVG_CYCLE_TREND_SQL — returns the trend query for average cycle time over time.
• PRJ_SVNG_TREND_SQL — returns the projected savings trend query.
• PRJ_SVNG_LN_TREND_SQL — returns the projected savings trend query at line level.
• REAL_SVNG_TREND_SQL — returns the realized savings trend query.
• NEG_PO_TREND_SQL — returns the trend query linking negotiations to purchase order activity.
• DTL_SQL — returns the drill-down detail query used when a user navigates from a summary region into underlying transaction rows.
• GET_DTL_FILTER — returns the filter predicate applied to the detail query, deriving its values from the current page parameters.

All SQL-generation functions follow the same internal contract: they build and return a statement string that the POA_DBI template package then executes. No parameter lists are documented in the ETRM metadata, and none should be assumed.

Tables Accessed

The only explicitly documented table reference is PLITBLM, the standard EBS PL/SQL integer table type defined in the SYS.STANDARD package, which the package uses to hold arrays of identifiers (for example, negotiation or document type IDs) when assembling dynamic filter predicates and IN-list fragments.

The view POA_NEG_DOCTYPES_V is a documented dependency. This view is the object most commonly located by the search term "poa_neg_doctypes_v" and is the source from which POA_DBI_NEG_PKG resolves the negotiation document types that scope its dashboard queries. The functions in this package reference the view indirectly, through the SQL text they emit, rather than through direct DML.

Usage Notes

POA_DBI_NEG_PKG is invoked at runtime by the Procurement Dashboard pages, not directly by end users or by forms. The front-end dashboard regions call POA_DBI_TEMPLATE_PKG, which in turn calls the appropriate SQL-generator function in this package to obtain the query text and the associated filter. It is therefore an internal dependency: the ETRM metadata records that the package is not referenced by any database object, confirming that no other stored program depends on it.

The package is valid in both Oracle EBS 12.1.1 and 12.2.2. Customizations should not modify this package directly, as it is Oracle-shipped and may be patched. Where additional analytics are required, standard practice is to create a custom copy or extend the dashboard definitions rather than editing the delivered package body. The dependency on FND_GLOBAL means behavior is sensitive to the active responsibility and organization context at the time the dashboard is rendered.