Search Results hr_summary_item_type




Overview
Explore in AI Chat

Overview

HR_SUMMARY_ITEM_TYPE is an APPS-owned, VALID database view within the PER – Human Resources product of Oracle E-Business Suite (12.1.1 and 12.2.2). It exposes the catalogue of summary data item types used by Oracle HRMS summary and calculation processing. As stated in the ETRM metadata, the view "will hold the name of each of the summary data items," and each item type additionally "stores the default SQL statement this will provide the superset of all data that should be processed within any given calculation."

Functionally, the view acts as a configuration and reference layer rather than a transactional store. It gives developers, integrators, and report authors a readable projection of the seeded item type definitions that drive HR summary calculations, instead of requiring direct reads against the underlying HR_SUMMARY table with its abstract lookup-driven code columns. Because the item types are seeded and controlled through the GSP_ITEM_TYPE lookup (Application ID 800), the view is best treated as read-only reference data.

Underlying Base Objects

The documented view metadata identifies the following referenced base objects: HR_API (PACKAGE), HR_LOOKUPS (VIEW), and HR_SUMMARY (SYNONYM).

The ETRM view text shows the view is defined over a join of HR_SUMMARY and HR_LOOKUPS:

  • HR_SUMMARY — stores the summary definitions themselves; the view restricts these rows with S.TYPE = 'ITEM_TYPE', meaning only item-type summary records are surfaced.
  • HR_LOOKUPS — provides the decoded meaning for each item type; the join is on S.TEXT_VALUE1 = L.LOOKUP_CODE, constrained by L.APPLICATION_ID = 800 and L.LOOKUP_TYPE = 'GSP_ITEM_TYPE'.
  • HR_API — the HRMS PL/SQL API package associated with the summary infrastructure; it underpins the maintenance and validation of summary data, though the view itself is defined purely over the HR_SUMMARY and HR_LOOKUPS join as shown.

The select list also carries S.ROWID as ROW_ID, aligning the view with the standard Oracle EBS row-identifier pattern used by the HRMS Forms and API layer for row-level update coordination.

Key Columns

  • ROW_ID — the ROWID of the underlying HR_SUMMARY row; used for row-level identification.
  • ITEM_TYPE_ID — the unique identifier of the summary item type.
  • BUSINESS_GROUP_ID — the business group owning the definition; defines the data partition within multi-organization HR installations.
  • OBJECT_VERSION_NUMBER — optimistic locking / versioning column used by the HRMS API layer.
  • NAME — the name of the summary data item.
  • TITLE — the descriptive title presented for the item type.
  • UNITS — the unit of measure associated with the summary item.
  • DATATYPE — the data type of the value the item type produces.
  • COUNT_CLAUSE1, COUNT_CLAUSE2 — SQL fragments used to build the count portion of the item's default statement.
  • WHERE_CLAUSE — the restriction fragment contributing to the default SQL statement that yields the superset of data processed per calculation.
  • SEEDED_DATA — indicates whether the row is Oracle-seeded or user-defined; seeded rows are typically not modifiable.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns maintained by the EBS who-columns convention.

Note that the ETRM view text aliases TEXT_VALUE1 through TEXT_VALUE7 and L.MEANING; in the documented column listing these map to the functional columns such as NAME, TITLE, UNITS, DATATYPE, and the clause columns.

Common Use Cases and Queries

The view is typically queried to inspect which summary item types exist, what SQL they generate, and whether they are seeded. A representative query lists the item types and their decoded meaning:

SELECT item_type_id, name, title, units, datatype,
where_clause, seeded_data
FROM apps.hr_summary_item_type
WHERE business_group_id = :p_business_group_id
ORDER BY name;

To isolate seeded versus custom definitions:

SELECT name, title, seeded_data
FROM apps.hr_summary_item_type
WHERE seeded_data = 'Y';

Because the view is read-only reference data, it is useful in diagnostics (confirming the default statement fragments), in integrations that must map item types to external systems, and in reports that document the configuration of HR summary processing. All access should be granted through the APPS schema with the standard EBS security model, and no DML should be issued directly against the view; item types are maintained through the HR_API package and the supported HRMS setup forms.