Search Results approvals_sql




Overview
Explore in AI Chat

Overview

The APPS.BIS_PMV_APPROVALS_PVT package is a private PL/SQL API that supplies the approvals-related query definitions used by the Process Manufacturing Viewer (PMV) and its associated Oracle E-Business Suite intelligence dashboards. PMV is a consolidated reporting and inquiry framework that presents process-manufacturing transactions, batches, specifications, and workflow statuses to end users and managers. Within this framework, approval actions—such as approvals still pending against a batch or a formula, and the detailed history of those approvals—must be surfaced through dynamically constructed SQL.

This package encapsulates that SQL construction logic. Rather than hard-coding query text in a form or dashboard region, the PMV rendering engine calls these procedures at runtime, passing the prevailing page parameters, and receives back both a SQL statement and a set of query attributes describing how the result set should be formatted and bound. Declared with AUTHID CURRENT_USER, it executes under the privileges of the calling session, consistent with Oracle Applications privacy and security conventions. The package is classified as a private (PVT) API, meaning it is intended for internal use by the PMV framework rather than direct invocation by external or customer-written code.

Key Procedures and Functions

The package exposes two documented procedures:

  • APPROVALS_SQL — Returns the custom SQL text for the approvals list region of PMV. It accepts an incoming page parameter table (typed BIS_PMV_PAGE_PARAMETER_TBL) and returns two OUT NOCOPY parameters: the generated SQL string (x_custom_sql) and the corresponding query attributes collection (x_custom_attr of type BIS_QUERY_ATTRIBUTES_TBL). The query attributes describe column metadata, display properties, and relationship-to-column mappings so the PMV engine can render the list consistently.
  • APPROVALS_DETAIL_SQL — Performs the analogous function for the approvals detail region, producing the drill-down SQL used to display the full attribute set of a selected approval or notification. Like its companion procedure, it takes the page parameter table and returns the generated SQL and query attributes through its OUT NOCOPY parameters. The split between list and detail procedures allows the PMV framework to apply different filters, joins, and column sets depending on the user's navigation context, while keeping both queries parameter-driven and reusable across pages.

Tables Accessed

Both procedures build their result sets by referencing the following objects through APPS synonyms:

  • WF_NOTIFICATIONS — Supplies the core approval/workflow notification records, including notification identifiers, status, subject, recipient, and date information that drive the approvals list and detail views.
  • WF_NOTIFICATION_ATTRIBUTES — Provides the individual attribute values (name/value pairs) associated with each notification, enabling the detail view to display the substantive content of an approval request.
  • FND_LOOKUP_VALUES — Resolves coded values into user-meaningful descriptions, such as translating notification status or type codes into displayable lookup labels.
  • PLITBLM — The standard Oracle Applications PL/SQL index-by table used for passing collections, including the parameter and query-attribute tables manipulated by these procedures.

Usage Notes

Because this package is a private API under the BIS (Business Intelligence System) product, it is not intended for direct calls from customer extensions. It is normally invoked indirectly by the PMV framework when a PMV page or an associated HTML/Forms-based dashboard session requests the approvals list or an approval detail view. The PMV engine supplies the current page parameter table, calls the appropriate procedure, and then executes the returned SQL text while applying the returned query attributes.

Custom implementations that wish to surface workflow approval information in a PMV style can mimic this pattern—calling APPROVALS_SQL or APPROVALS_DETAIL_SQL and consuming the OUT parameters—but must recognize that no formal support or upgrade guarantee applies to these private entry points. The package was created in 2000 and is documented as essentially stable since that time, so customers on 12.1.1 and 12.2.2 should treat its signatures as fixed within those releases.