Search Results update_oki_refresh

Overview

OKI_REFRESH_PVT is a private (PVT class) PL/SQL package body owned by APPS in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the Oracle Knowledge Base / Contracts Intelligence (OKI) module, an operational data-store layer that denormalizes contract, sales, and installed-base data from the transactional OKC and CSI schemas into flattened reporting tables. The package header identifies it as a bulk refresh utility, with version $Header: OKIRRFHB.pls 115.74 2004/08/13. The body defines bulk collection types (vc_tab_type, numlist, rowlist, datelist), a constant LIMIT of 1,000 rows to bound batch sizes, and a base-currency lookup via fnd_profile.value('OKI_BASE_CURRENCY'). Its principal role is to rebuild the OKI reporting tables nightly or on demand, converting multi-currency contract values using GL rates so that downstream OKI dashboards and reports present consistent figures. The user search term "update_oki_refresh" corresponds to the documented procedure UPDATE_OKI_REFRESH, which likely maintains a refresh-control or run-status record rather than performing the refresh itself.

Key Procedures and Functions

The package exposes 25 documented procedures and functions. The refresh workers mirror the target tables: REFRESH_SALES_K_HDRS, REFRESH_ADDRS, REFRESH_SOLD_ITM_LINES, REFRESH_COV_PRD_LINES, REFRESH_EXPIRED_LINES, REFRESH_K_SALESREPS, REFRESH_K_CONV_RATES, and REFRESH_K_PRICING_RULES. Each populates its corresponding OKI denormalized table. Parallel FAST_ variants (FAST_SALES_K_HDRS, FAST_SOLD_ITM_LINES, FAST_COV_PRD_LINES, FAST_EXPIRED_LINES, FAST_K_SALESREPS, FAST_ADDRS) perform the same loads using a bulk/incremental path — the FAST_ prefix conventionally denotes a faster, direct-path or chunked collection strategy leveraging the numeric and rowid collection types declared above.

REFRESH_DAILY_RATES loads GL daily conversion rates, and GET_CONVERSION_RATE retrieves a single rate for a given currency and date. JOB_START and JOB_END bracket a refresh run, recording start and completion in OKI_JOB_RUNS (evidenced by the g_latest_job_run_id_csr cursor selecting MAX(job_run_id)). INITIAL_JOB_CHECK validates preconditions before a run begins. UPDATE_OKI_REFRESH maintains the refresh-status record consumed by the framework. Supporting cursors — index_info_csr, index_name_csr, and the create_indices procedure (referenced in the 11510 change note) — manage table indexes, with obsolete tables oki_addresses and oki_expired_lines removed as denormalization progressed.

Tables Accessed

The package reads source data from the OKC contracts schema: OKC_K_HEADERS_B, OKC_K_LINES_B, OKC_K_ITEMS, OKC_K_PARTY_ROLES_B, OKC_K_REL_OBJS, OKC_K_VERS_NUMBERS, OKC_OPERATION_LINES, OKC_STATUSES_B, OKC_CONTACTS, and OKC_GOVERNANCES. Installed-base and organizational context come from CSI_ITEM_INSTANCES, HR_ORGANIZATION_INFORMATION, and FND_CURRENCIES. Currency conversion draws on GL_DAILY_RATES and GL_PERIODS. The package writes to OKI reporting tables and to OKI_JOB_RUNS for run logging. APPS synonyms front all objects, so access is granted only to the APPS schema.

Usage Notes

Because OKI_REFRESH_PVT is classified PVT, it is not a supported public API; callers should invoke it only through the OKI concurrent programs that Oracle ships to refresh the operational data store — typically scheduled nightly after contract and order processing. The 11 dependent packages reference these private procedures, so direct invocation from custom code is discouraged and may break on patching. DBAs monitoring a refresh should query OKI_JOB_RUNS using the latest job_run_id and inspect the UPDATE_OKI_REFRESH status. In 12.2.2, the online patching (adop) model means the package is compiled into the run and patch editions; edition-specific grants must exist for any custom wrapper. Performance tuning focuses on the 1,000-row bulk limit and the indexes managed by create_indices.