Search Results oki_dbi




Overview
Explore in AI Chat

Overview

OKI_DBI_LOAD_CLEB_PVT is a private PL/SQL package body in the APPS schema that supports Oracle E-Business Suite's Contracts Intelligence / DBI (Daily Business Intelligence) reporting layer for Oracle Contracts (OKC) and related order and installment data. The "CLEB" designation refers to the Contract Line Event/Expenditure/Balance extraction logic that feeds the contract profitability and obligations subject areas within the DBI data warehouse. The package orchestrates incremental and full (initial) loads from the transactional contracts tables into DBI staging and base tables, performing delta detection, currency conversion, deletion handling, and parallelism through worker processes.

Documented at version 120.28 (header dated 2008/01/10), the body carries module globals that reveal its design intent: date boundary globals (g_4712_date, g_9999_date, g_0001_date), a Euro conversion start date, a global start date, worker-list collections, batch size, and counters for contracts, deletions, and true incremental processing. A G_CHILD_PROCESS_ISSUE exception signals the use of child workers via DBMS_JOB or a similar mechanism, allowing the load to scale across parallel sessions.

Key Procedures and Functions

The package exposes twenty-one documented programs:

  • RLOG — Writes diagnostic messages to the DBI log, delegating to BIS_COLLECTION_UTILITIES and raising OKC_API.G_EXCEPTION_UNEXPECTED_ERROR on failure.
  • ROUT — Writes messages to the DBI output stream via BIS_COLLECTION_UTILITIES.
  • RESET_BASE_TABLES — Clears or initializes the DBI base tables prior to a load cycle.
  • DIRECT_LOAD — Performs a full, direct load of contract data into the DBI structures.
  • INCR_LOAD — Drives the incremental load path for changed records only.
  • POPULATE_INC_TABLE_INIT — Populates the incremental staging table during the initial load.
  • POPULATE_INC_TABLE_INC — Populates the incremental staging table during subsequent incremental cycles.
  • REPORT_MISSING_CURRENCIES — Identifies currencies absent from conversion setup and reports them.
  • PROCESS_DELETES — Removes DBI records corresponding to deleted source contracts.
  • LOAD_CURRENCIES — Loads currency conversion information used by the reporting layer.
  • POPULATE_REN_REL — Populates renewal relationships between contract headers.
  • LOAD_STAGING — Moves staged data into the DBI target structures.
  • UPDATE_LHS / UPDATE_RHS — Update the left-hand-side and right-hand-side of the relationship/aggregation model.
  • POPULATE_PREV_INC — Captures the previous incremental snapshot for delta comparison.
  • DELTA_CHANGES — Compares snapshots to determine changed contracts.
  • UPDATE_STAGING — Applies changes to staging records.
  • WORKER / LAUNCH_WORKER — Manage parallel worker sessions that execute the load across batches.
  • INITIAL_LOAD — Coordinates the initial full load, invoking direct load, staging, and worker sequencing.

Tables Accessed

The package reads and writes a documented set of APPS synonyms representing contract, order, and setup entities. Contracts transactional tables — OKC_K_HEADERS_ALL_B, OKC_K_LINES_B, OKC_K_ITEMS, OKC_K_PARTY_ROLES_B, OKC_K_REL_OBJS, OKC_CONTACTS, and OKC_CLASS_OPERATIONS — supply the contract structure, lines, parties, relationships, and class operations required for DBI dimensions and facts. OE_AGREEMENTS_B and OE_AGREEMENTS_TL provide agreement headers and translations, while OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL supply order data linked to contracts for revenue and obligation reporting. CSI_ITEM_INSTANCES provides installed-base instance data used in contract coverage reporting. JTF_RS_SALESREPS and MTL_PARAMETERS furnish sales representative and inventory organization references for dimensional enrichment. FINANCIALS_SYSTEM_PARAMS_ALL supplies system-level financial parameters, including currency-related settings used by LOAD_CURRENCIES. Because these are APPS synonyms, the underlying OKC, OE, CSI, JTF, MTL, and FND tables are accessed according to standard EBS naming conventions.

Usage Notes

OKI_DBI_LOAD_CLEB_PVT is a private (PVT) package and is not intended for direct customer invocation. It is referenced by two other packages in the documented dependency set, which are the public entry points invoked by DBI/Contracts Intelligence concurrent programs and by the DBI collection framework. In Oracle EBS 12.1.1 and 12.2.2 environments, the load is typically scheduled through daily or on-demand concurrent requests that drive the initial load and subsequent incremental cycles. The worker architecture (WORKER, LAUNCH_WORKER, g_no_of_workers, g_batch_size) allows large contract volumes to be split into batches processed by parallel sessions, with G_CHILD_PROCESS_ISSUE raised when child process spawning encounters a problem. Administrators should ensure currency conversion setups are complete, since REPORT_MISSING_CURRENCIES will highlight gaps that disrupt LOAD_CURRENCIES. Because the package maintains previous-snapshot and delta state (POPULATE_PREV_INC, DELTA_CHANGES), re-running loads out of sequence can produce inconsistent incremental results. Customizations should never modify this body; extensions belong in the calling public packages or in the DBI collection configuration.