Search Results set_by_clause




Overview
Explore in AI Chat

Overview

GHR_BREAKDOWN_RPT is an Oracle EBS Human Resources (GHR) reporting package that produces organization/position breakdown reports against the HRMS schema. The package assembles hierarchical reporting data by resolving organization structure versions, positions, grades, jobs and analyses, then materializes the result set into a temporary results table for display or printing. It exposes a set of setter/getter utilities that allow the calling report or form to parameterize the query before execution, including selection of the effective date, the agency/subelement filter, the BY, WITHIN and FOR clauses used to build dynamic SQL, and the hierarchy to be traversed.

The header revision is ghbrkdwn.pkh 120.1 2005/07/01, indicating that the package is an older, relatively stable HRMS utility that has been carried forward into the 12.1.1 and 12.2.2 code lines without significant change. It is owned by APPS and is classified as an OTHER API in ETRM, meaning it is not part of the officially supported public API surface even though it is callable from the APPS schema.

Key Procedures and Functions

  • SET_EFFECTIVE_DATE / EFFECTIVE_DATE — Sets and returns the effective date used to drive date-tracked queries against assignments, positions and analyses.
  • SET_AGENCY / AGENCY_SUBELEMENT — Stores the agency and subelement filter values used to restrict the breakdown population.
  • SET_BY_CLAUSE, SET_WITHIN_CLAUSE, SET_FOR_CLAUSE, SET_EXTRA_CLAUSE / GET_FOR_CLAUSE — Build the dynamic SQL fragments that define grouping, within-group ordering, the FOR dimension and any additional predicate appended to the generated query.
  • SET_HIERARCHY — Records the organization structure version identifier used for hierarchical expansion.
  • GET_HIERARCHY_LEVEL — Returns the level of a position within the selected hierarchy at a given effective date.
  • GET_HIERARCHY_CODES — Returns the hierarchy codes (parent set by default, controlled by the p_mode parameter) for an assignment rowid at a given effective date.
  • PROCESS — The main driver. Accepts an optional breakdown criteria identifier and populates the breakdown results for the report.
  • DELETE_TEMP_DATA — Cleans up the temporary breakdown data after the report completes.
  • DECODE_LOOKUP — Resolves a lookup code against a lookup type for display purposes.
  • RETURN_SPECIAL_INFORMATION — Returns a ghr_api.special_information_type record for a given person and structure, respecting the effective date. This is the entry point surfaced by the user's search term and is used to retrieve person-level special information (for example, statutory or negotiated details) for inclusion in the breakdown output.
  • GET_ORG_STRUCT_NAME — Returns the organization structure name for a given structure identifier.

Several functions carry PRAGMA RESTRICT_REFERENCES(..., WNDS, WNPS) declarations, confirming that the getters do not write package state or database state and are therefore safe to call from SQL contexts.

Tables Accessed

The package reads structure metadata from PER_ORG_STRUCTURE_VERSIONS, PER_ORGANIZATION_STRUCTURES, PER_ORG_STRUCTURE_ELEMENTS, FND_ID_FLEX_STRUCTURES and FND_ID_FLEX_STRUCTURES_TL; person and assignment data from PER_ALL_ASSIGNMENTS_F and PER_PEOPLE_EXTRA_INFO; position and grade data from HR_ALL_POSITIONS_F, PER_POSITION_EXTRA_INFO, PER_GRADES and PER_GRADE_DEFINITIONS; and analysis data from PER_ANALYSIS_CRITERIA and PER_PERSON_ANALYSES. PER_JOBS supplies job attributes referenced by the breakdown. The temporary breakdown result set is written to GHR_BREAKDOWN_RESULTS and cleared by DELETE_TEMP_DATA. Access to these objects is via APPS synonyms.

Usage Notes

GHR_BREAKDOWN_RPT is not referenced by any other documented package (zero inbound dependencies in ETRM), so it is not part of a supported integration chain. It is invoked directly, typically from the HRMS breakdown report concurrent program or from a custom report that needs hierarchical organization breakdown by position, grade or job. When calling from custom code, the caller must first establish the parameter state (effective date, agency, hierarchy and clause settings) before invoking PROCESS, and should invoke DELETE_TEMP_DATA afterwards. Because RETURN_SPECIAL_INFORMATION returns an OUT NOCOPY parameter of type ghr_api.special_information_type, callers must declare a matching PL/SQL variable and ensure a valid person identifier and structure name are supplied along with an effective date. The package is not an officially supported API; customers extending it should be aware that the pragma declarations, the absence of inbound dependencies and the 120.1 header revision all indicate a legacy utility with no compatibility guarantee across future releases.