Search Results calc_total_loss




Overview
Explore in AI Chat

Overview

APPS.HRI_DBI_WMV_CHANGES is a PL/SQL package that belongs to the Oracle Human Resources Intelligence (HRI) product family — the set of database objects that underpin the Oracle Daily Business Intelligence (DBI) and HR Analytics reporting offerings in Oracle E-Business Suite. The package is declared AUTHID CURRENT_USER, meaning its database objects are resolved under the privileges of the invoking user rather than the package owner, which is standard practice for APPS-owned analytics packages that must respect the querying user's security profile and organization access.

The business purpose of the package is to quantify workforce movement — gains and losses of employees within a supervisory hierarchy over a defined reporting period. This is the raw numerical input behind headcount change analytics: net headcount movement, hires, terminations, voluntary versus involuntary separations, and internal transfers, all scoped to a supervisor and a date range. The package also includes two refresh entry points that populate or maintain the materialized/aggregate structures that the analytic functions read against. The $Header indicating script version 120.0 dated 2005 identifies the package as originating in the 11i/early-R12 era; it is retained unchanged through 12.1.1 and 12.2.2 as part of the DBI HR schema.

Key Procedures and Functions

  • CALC_TOTAL_GAIN — returns the gross workforce gain attributable to a supervisor within a reporting period.
  • CALC_TOTAL_GAIN_HIRE — returns the gain component represented by new hires.
  • CALC_TOTAL_GAIN_TRANSFER — returns the gain component represented by inbound transfers into the supervisor's organization.
  • CALC_TOTAL_LOSS — returns the gross workforce loss for a supervisor in a reporting period.
  • CALC_TOTAL_LOSS_TERM — returns the loss component driven by terminations.
  • CALC_TOTAL_LOSS_TERM_VOL — returns the voluntary termination loss component.
  • CALC_TOTAL_LOSS_TERM_INVOL — returns the involuntary termination loss component.
  • CALC_TOTAL_LOSS_TRANSFER — returns the loss component represented by outbound transfers.
  • CALC_TOTAL_NET — returns the net movement (gain less loss) for the reporting period.
  • FULL_REFRESH — performs a complete rebuild of the package's staging or summary data for all supervisors and periods.
  • REFRESH_FROM_DELTAS — performs an incremental refresh, applying only changes accumulated since the previous run rather than rebuilding the full data set.

Each CALC_TOTAL_* function accepts a supervisor identifier and an effective start and end date, and returns a numeric result. No parameter lists are reproduced here beyond those documented in the package specification.

Tables Accessed

The ETRM metadata records no directly documented base tables for this package. At runtime it resolves APPS synonyms for the DBI HR summary and staging entities — typically the workforce-movement summary tables populated by FULL_REFRESH and REFRESH_FROM_DELTAS — alongside the standard HRMS supervisor hierarchy and assignment/termination sources (PER_ALL_ASSIGNMENTS_F, PER_PEOPLE_F, and related date-tracked tables) via those synonyms. All access is read-only from the perspective of the analytic functions; only the refresh procedures write to the summary structures. Security is enforced by the AUTHID CURRENT_USER clause combined with the DBI organization and supervisor security predicates.

Usage Notes

  • The CALC_TOTAL_* functions are typically invoked by DBI HR dashboard regions, discoverer/BIP report logic, or custom analytic queries that need period-over-period headcount movement for a supervisor.
  • REFRESH_FROM_DELTAS is the routine most commonly scheduled, either directly or through a DBI/HRMS concurrent program, to keep the movement summaries current without the cost of a full rebuild. FULL_REFRESH is used for initial population, recovery, or after structural changes to the source data.
  • Because the package is AUTHID CURRENT_USER, callers must be granted execute privilege on the package and must themselves hold access to the underlying HRMS data; results therefore vary with the caller's security profile.
  • No other documented package references HRI_DBI_WMV_CHANGES, so it is an entry point rather than a shared dependency. Customizations should call it rather than duplicate its logic, and any wrapper code should invoke REFRESH_FROM_DELTAS on the same cadence as the standard DBI collections jobs to avoid stale aggregates.