Search Results get_last_refresh_date

Overview

BIV_DBI_BAK_AGE_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite. It belongs to the Oracle Business Intelligence (BIV/DBI) product family and serves the backlog aging reporting function, specifically the "Backlog Age" dashboard and its associated reports. The package acts as a report-generation and query-construction engine: it assembles and returns SQL statements that drive the aging analysis of open backlog transactions, supplies parameter and attribute metadata required by the Oracle BI reporting framework, and computes the date boundaries used to bucket backlog items into aging periods.

In EBS 12.1.1 and 12.2.2, the BIV/DBI packages are widely used to populate the Oracle Daily Business Intelligence dashboards, including the Order Management and Supply Chain backlog views. This package is classified as OTHER in the ETRM metadata, indicating that it is not a public outbound API but an internal utility package invoked by the reporting layer to generate its data sets. Its status is VALID, and it depends on standard reporting infrastructure objects such as BIS_PMV_PAGE_PARAMETER_TBL and BIS_QUERY_ATTRIBUTES_TBL.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions within this package. Their names indicate a clear division of purpose:

• GET_TBL_SQL — Returns the SQL statement that produces the tabular (table-based) backlog aging data set for the dashboard.
• GET_DBN_TBL_SQL — Returns the SQL statement for the tabular data set filtered or tailored for a specific dashboard or drill-down context.
• GET_TRD_SQL — Returns the SQL statement that generates the trend, or time-series, view of backlog aging across the reporting periods.
• GET_DBN_TRD_SQL — Returns the SQL statement for the trend data set within a particular dashboard context.
• GET_DETAIL_SQL — Returns the SQL statement used to retrieve the detail-level rows behind a summarized aging figure, supporting drill-down from the dashboard.
• GET_LAST_REFRESH_DATE — Retrieves the timestamp of the most recent data refresh, enabling the report to display data currency information.
• CURRENT_REPORT_START_DATE — Determines and returns the effective start date for the current report run, establishing the baseline from which aging buckets are calculated.

These routines do not accept documented parameter lists in the available metadata; they are described here by purpose only, consistent with the ETRM record.

Tables Accessed

The package reads from and writes to the following tables, referenced through APPS synonyms:

• BIV_DBI_BACKLOG_AGE_DATES — The primary data store holding the backlog aging date definitions and bucket boundaries used by the report.
• BIS_REFRESH_LOG — Log of BI collection refresh runs, consulted by GET_LAST_REFRESH_DATE to report data currency.
• FII_TIME_DAY, FII_TIME_WEEK, FII_TIME_ENT_PERIOD, FII_TIME_ENT_QTR, FII_TIME_ENT_YEAR — The FII time dimension tables that supply day, week, period, quarter, and year calendar attributes for the trend and aging calculations.
• PLITBLM — The standard Oracle Applications PL/SQL table type used for passing array parameters within the report framework.

In addition, the package references BIS_PMV_PAGE_PARAMETER_TBL and BIS_QUERY_ATTRIBUTES_TBL, the framework tables that hold page parameter definitions and query attribute metadata for BI dashboards.

Usage Notes

BIV_DBI_BAK_AGE_PKG is invoked by the Oracle Daily Business Intelligence reporting framework rather than directly by end users. It is typically called from the DBI dashboard page definitions and the accompanying concurrent programs that refresh backlog aging data for the Order Management and Supply Chain Intelligence dashboards. The package is not listed as referenced by any other package, confirming its role as a leaf-level generator of SQL strings and date values consumed by the generic BIS reporting engine.

Because it is an APPS-owned internal package, it should not be called from custom application code or extended without careful consideration, as its behavior is governed by reporting framework expectations that may change between patch levels. Standard usage occurs during dashboard page rendering and during scheduled BI collection refresh, where GET_LAST_REFRESH_DATE and CURRENT_REPORT_START_DATE provide the temporal framing for the aging analysis presented to the user.