Results for “calc_abv_lookup”

8 results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The APPS.HRI_OLTP_DISC_WRKFRC package body is a workforce budgeting utility that supplies Oracle Discoverer (Oracle Business Intelligence) with calculated workforce data. It belongs to the HRI (Human Resources Intelligence) family of packages, whose prefix denotes HRMS reporting and analytics infrastructure rather than a user-facing application. The package acts as a bridge between the Oracle HRMS FastFormula engine and the Discoverer end-user layer; it resolves FastFormula definitions that map to budget names, evaluates those formulas against actual and budget assignment data, and returns values that Discoverer worksheets display as workforce plan versus actual comparisons.

The source header identifies the file as hriodwrk.pkb, last revised in 2003, which is consistent with the long-lived nature of HRMS analytics code carried forward into release 12.1.1 and 12.2.2. The package declares package-level global variables that cache the last business group, formula identifier, and formula name, so that repeated Discoverer lookups against the same formula avoid redundant queries against the formula tables. Two additional globals, g_no_valid_formula and g_no_valid_formula_id, hold null values that signal to Discoverer when no applicable or compiled FastFormula exists.

Key Procedures and Functions

  • GET_FORMULA_ID — Determines the identifier of a FastFormula for a given business group and formula name. It first checks the cached global values, then attempts to resolve a customer-defined formula prefixed with BUDGET_, falling back to a seeded template formula prefixed with TEMPLATE_ where the business group is null.
  • GET_MANPOWER_FORMULA_ID — Returns the formula identifier associated with manpower budgeting, following the same customer-then-template resolution pattern.
  • GET_FF_ACTUAL_VALUE — Evaluates a FastFormula to produce the actual value for a workforce measure, typically for a person or assignment within a period.
  • GET_FF_ACTUAL_VALUE_PQH — A parallel variant of the actual-value function used in the public-sector (PQH) budgeting model.
  • GET_ASG_BUDGET_VALUE — Retrieves the budgeted value associated with an assignment, returning the plan-side figure used alongside the actual value.
  • DIRECT_REPORTS — Returns the direct reports for a given person, enabling Discoverer to aggregate workforce data along reporting hierarchies.
  • CALC_ABV_LOOKUP — Performs a lookup for a business group and meaning pair, populating the cached g_bmt_code and g_bmt_meaning globals.
  • GET_PERIOD_SERVICE_IN_MONTHS — Calculates length of service for a period expressed in months.
  • GET_PERIOD_SERVICE_IN_YEARS — Calculates length of service for a period expressed in years.

Tables Accessed

The package reads FastFormula definitions from FF_FORMULAS_F and the associated business-group-aware view FF_FORMULAS_X in order to resolve formula identifiers. Assignment and person data come from PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F, with PER_ASSIGNMENT_STATUS_TYPES providing status context for filtering active records. Budget figures are sourced from PER_BUDGETS and PER_BUDGET_ELEMENTS, while the public-sector equivalents PQH_BUDGETS and PQH_BUDGET_PERIODS support the PQH-specific function. Period-of-service and date calculations rely on PER_PERIODS_OF_SERVICE and PER_TIME_PERIODS. All access is performed through APPS synonyms and is effectively read-only.

Usage Notes

HRI_OLTP_DISC_WRKFRC is not intended for direct invocation by end users or by standard concurrent programs. It is called by Discoverer workbook logic—through custom folders or PL/SQL functions registered against the EUL—and is referenced by one other package within the HRMS intelligence layer. The package is relevant to the "frp a12 v1" search context because it forms part of the workforce planning and budgeting enablement delivered with the HRMS FastFormula and Discoverer integration. Implementations upgrading from 12.1.1 to 12.2.2 should note that the online tables (PER_*) are date-tracked and that callers supply effective dates through the surrounding Discoverer query rather than through package parameters. Because the globals cache the last formula lookup, callers that iterate across multiple business groups in a single session should be aware that the cache is keyed on both business group and formula name.