Search Results bil_bi_opdtl_f_pkg




Overview
Explore in AI Chat

Overview

BIL_BI_OPDTL_F_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite (12.1.1 and 12.2.2). It supports the Business Intelligence (BI) staging and fact-loading infrastructure for opportunity detail data drawn from the Oracle Sales (AS) and Bilateral (BIL) modules. Its purpose is to populate and refresh the fact table BIL_BI_OPDTL_F, which holds opportunity-level detail records used by BI reporting and analytics. The package implements the standard EBS dimensional-load pattern: a one-time full initialization followed by ongoing incremental refreshes, so that analytical content stays current without requiring a complete rebuild. The $Header comment (bilos1s.pls 120.1, dated 2005/09/22) indicates the file originated as a consolidated BI load script and has been retained largely unchanged across releases.

Key Procedures and Functions

The package exposes two public procedures, both with error-handling signatures (errbuf/retcode) that conform to the concurrent-program standard so the routine can be driven by the Concurrent Manager.

  • INIT_LOAD — Performs the initial, full load of opportunity detail data into the BI fact structure. It accepts parameters for a from-date and a truncate option, allowing the caller to bound the extraction window and to optionally clear existing fact rows before loading. INIT_LOAD is intended for first-time population or for controlled rebuilds.
  • INCR_LOAD — Performs the incremental load, picking up only new or changed source records since the prior successful run. INCR_LOAD is the procedure referenced by the "incr_load" search term and is the routine normally scheduled as a recurring concurrent request.

No private functions are exposed in the package specification, and the specification contains no other public entry points.

Tables Accessed

Metadata records the following objects, referenced through APPS synonyms:

  • BIL_BI_OPDTL_STG — the staging table where extracted source data is landed before transformation into the fact table.
  • BIL_BI_OPDTL_F — the target fact table for opportunity detail.
  • AS_SALES_CREDITS and AS_SALES_CREDITS_DENORM — source tables providing sales credit allocations and the denormalized credit view used when attributing opportunity revenue to salespersons.
  • AS_SALES_LEAD_OPPORTUNITY — the driving source of opportunity and lead records.
  • BIL_BI_CURRENCY_RATE — supplies exchange rates for currency conversion of amount columns.
  • BIS_REFRESH_LOG — the BI refresh log used to record load runs and to determine the last successful refresh point for incremental processing.
  • DBMS_SQL — the Oracle-supplied dynamic SQL package, used internally (typically for dynamic query construction or cursor handling) rather than as a data source.

Reads are concentrated on the AS_ tables, BIL_BI_OPDTL_STG, BIL_BI_CURRENCY_RATE, and BIS_REFRESH_LOG; writes target BIL_BI_OPDTL_STG, BIL_BI_OPDTL_F, and BIS_REFRESH_LOG.

Usage Notes

The package is not referenced by any other documented package, so it functions as a load entry point rather than a shared utility. It is normally invoked in one of two ways: (1) through concurrent programs registered against the INIT_LOAD and INCR_LOAD procedures, with the Concurrent Manager supplying errbuf and retcode; or (2) from custom PL/SQL that calls the procedures directly, passing the standard concurrent parameters. INIT_LOAD runs once at implementation or during a controlled rebuild, using its from-date and truncate arguments to scope and reset the data. INCR_LOAD is then scheduled as a recurring request, relying on BIS_REFRESH_LOG to identify the incremental high-water mark. Because both procedures use the IN OUT NOCOPY concurrent error signature, callers must declare and inspect errbuf and retcode to detect failures. No alternate overloads of these procedures are documented in the 12.1.1 metadata.