Search Results oki_load_yra_pvt




Overview
Explore in AI Chat

Overview

OKI_LOAD_YRA_PVT is a private PL/SQL package in the APPS schema that supports the Oracle E-Business Suite Service Contracts (OKI) module, specifically the year-over-year renewal amount reporting and analysis feature. Its stated purpose, per the header comments in the package specification, is to load the OKI_YOY_RENEWAL_AMT table. This table stores the contract amounts used to compare renewal activity across periods, organizations, and subclass codes, which enables Service Contracts users to analyze renewal trends year over year.

The package is classified as a PVT (private) API. It is not intended to be called directly by external code or end users, but rather to be invoked by a public wrapper or a concurrent program that controls execution. Consistent with its private designation and its AUTHID CURRENT_USER declaration, the package executes with the privileges of the calling user, and the APPS synonyms for the referenced objects must be accessible from the invoking session.

The package version documented in ETRM for 12.1.1 carries the header identifier OKIRYRAS.pls, last modified in 2002. It remains part of the seeded Service Contracts code set in both 12.1.1 and 12.2.2, though it is an older, low-change component.

Key Procedures and Functions

The package specification documents a single public procedure: CRT_YOY_RNWL. As the name implies, it creates the year-over-year renewal records. The procedure accepts parameters that identify the period context for the calculation, including a period set name and a period type, which are used to derive the valid accounting periods from GL_PERIODS.

The processing logic described in the package comments follows a cascading aggregation pattern:

  • For each distinct month, year, organization, and subclass code found in OKI_SALES_K_HDRS, the procedure derives the valid period from GL_PERIODS and calculates the contract amount.
  • It then repeats the aggregation for each distinct month, year, and subclass code, omitting the organization dimension.
  • Finally, it aggregates for each distinct month and year alone.

In each pass, the procedure determines whether a corresponding row already exists in OKI_YOY_RENEWAL_AMT and performs an insert or update accordingly. After the data load completes, the procedure updates the OKI_REFRESHS table with concurrent manager statistics, recording the request ID, program application ID, program ID, and program update date held in the package global variables.

Tables Accessed

  • OKI_SALES_K_HDRS — the source of sales contract header data. The package reads distinct month, year, organization, and subclass combinations from this table to drive the aggregation.
  • GL_PERIODS — supplies the valid accounting period definitions used to map transaction dates to periods for the period set and period type passed into the procedure.
  • OKI_YOY_RENEWAL_AMT — the target table that receives the calculated year-over-year renewal amounts via insert or update.
  • OKI_YOY_RENEWAL_AMT_S1 — a supporting sequence or secondary object associated with the target table, used in the write path.
  • OKI_REFRESHS — updated with concurrent manager execution statistics to track the job run.
  • DUAL — referenced for singleton queries and PL/SQL constructs.

Usage Notes

Because OKI_LOAD_YRA_PVT is a private package, it is not exposed in the Oracle EBS user interface and should not be called from custom code as a supported interface. It is normally invoked indirectly, either by a public Service Contracts API or by a concurrent program that loads the year-over-year renewal amounts during a scheduled refresh or reporting cycle. The package is documented as being referenced by one other package, which is the expected caller.

Administrators and developers troubleshooting renewal amount discrepancies should trace the population of OKI_YOY_RENEWAL_AMT back to this package and verify that the GL_PERIODS definitions for the requested period set and period type are open and correctly defined. Because it updates OKI_REFRESHS, execution can be monitored through the standard concurrent manager request and refresh history views. Direct invocation should be avoided in favor of the supported wrapper, and any extension of the aggregation logic should follow Oracle's documented customization standards for seeded Service Contracts packages.