Search Results biv_dbi_backlog_age_dates

Overview

APPS.BIV_DBI_BAK_AGE_PKG is a backlog aging reporting package belonging to the Oracle EBS Business Intelligence (BIV) family of database objects. Its stated purpose is to generate the SQL statements that drive the Daily Business Intelligence backlog aging tables and reports, covering backlog aging summaries, detailed backlog breakdowns, and the trend and drill-down variants of those summaries. In Oracle EBS 12.1.1 and 12.2.2, the package operates entirely on the read side of the reporting pipeline: it constructs dynamic query text that is submitted at runtime by the DBI collection and report generation framework, rather than performing DML itself.

The package body is versioned by the header comment $Header: bivsrvrbagb.pls 120.0 2005/05/25 ... and carries the noship designation, consistent with DBI server-side reporting objects that are not installed as standalone shipped entities. The package is not referenced by any other documented PL/SQL package, indicating that it is invoked directly by the report scheduling layer rather than through an application programming interface.

Key Procedures and Functions

• GET_TBL_SQL — Returns the SQL text used to populate the primary backlog aging summary table for the reporting period.
• GET_DBN_TBL_SQL — Returns the SQL text for the drill-down (detail-by-number) variant of the backlog aging summary table.
• GET_TRD_SQL — Generates the SQL for the backlog aging trend query across reporting periods.
• GET_DBN_TRD_SQL — Generates the SQL for the drill-down trend variant, combining the summary trend and detail structure.
• GET_DETAIL_SQL — Produces the SQL that retrieves the individual backlog aging detail rows underlying a selected summary.
• GET_LAST_REFRESH_DATE — Determines the most recent successful data refresh date, used to bound the reporting or "as of" window.
• CURRENT_REPORT_START_DATE — Returns the effective start date of the current reporting period for the same windowing logic.

Internally, the body defines helper function STATUS_SQL, which accepts a fact name, a WHERE clause, join table definitions, a windowing flag, column mappings, and optional filter and view-by parameters, and assembles the final query string from its constituent FROM, WHERE, and GROUP BY fragments. It also declares the constants g_c_aging_as_of_date_balance and g_p_aging_as_of_date_balance (bound to &AGE_CURRENT_ASOF_DATE and &AGE_PREVIOUS_ASOF_DATE) and the bit-map constant g_inlist_bal (value 16, bit 4) that control balance-level aging windows.

Tables Accessed

• BIS_REFRESH_LOG — Read to establish when the collection last completed, feeding GET_LAST_REFRESH_DATE.
• BIV_DBI_BACKLOG_AGE_DATES — Supplies the current and previous "as of" dates that define the aging comparison windows.
• FII_TIME_DAY, FII_TIME_WEEK, FII_TIME_ENT_PERIOD, FII_TIME_ENT_QTR, FII_TIME_ENT_YEAR — The FII time dimension hierarchy, used to align backlog aging results to day, week, period, quarter, and year reporting periods.
• PLITBLM — A general PL/SQL table used in the query construction logic.

Usage Notes

The package is invoked by the DBI backlog aging collection and report subroutines when the aging summary, trend, or detail reports are refreshed. Its functions are not exposed as callable business APIs and should not be invoked directly from forms or custom code. The dynamic SQL strings it returns are executed by the calling DBI framework. Because the package depends on the FII time hierarchy and on BIV_DBI_BACKLOG_AGE_DATES, the aging date table and time dimension must be current before the report generation functions are called; otherwise the resulting queries return stale or empty aging buckets.