Search Results count_clause1

Overview

APPS.HR_SUMMARY_ITEM_TYPE is a supplementary view in the Oracle E-Business Suite Applications (APPS) schema, registered under FND Design Data as PER.HR_SUMMARY_ITEM_TYPE. It exposes configuration metadata used by the HR Summary / HRMS information-type framework, which drives the Summary and Detail business components in Oracle HRMS forms. The view simplifies forms coding by presenting the attributes of each summary item type — its internal name, user-facing title, datatype, unit of measure, and the SQL fragments (COUNT_CLAUSE1, COUNT_CLAUSE2, WHERE_CLAUSE) that the application concatenates into runtime queries.

In Oracle EBS 12.1.1 and 12.2.2, views of this class are identified as a "supplementary view used to simplify forms coding." Oracle explicitly warns that the view is not intended for direct querying or data manipulation by customers, and that its definition may change dramatically in subsequent minor or major releases. Consequently, it should be treated as a read-only, version-sensitive object for diagnostic and reporting purposes only, never as an integration point for custom code.

Underlying Base Objects

The documented dependencies for APPS.HR_SUMMARY_ITEM_TYPE are HR_API (PACKAGE), HR_LOOKUPS (VIEW), and HR_SUMMARY (SYNONYM). These reflect the framework's design: HR_LOOKUPS supplies the lookup-based titles and seeded values, HR_API provides the PL/SQL encapsulation logic that the form uses to resolve item type metadata, and HR_SUMMARY is the synonym through which the summary data model is accessed. The view inherits the Standard Who audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) that Oracle EBS applies consistently across transactional and setup entities, together with OBJECT_VERSION_NUMBER for optimistic locking during concurrent updates.

Key Columns

• ROW_ID — ROWID pseudo-column identifying the physical row.
• ITEM_TYPE_ID — System-generated primary key for the item type; the join key to dependent metadata.
• BUSINESS_GROUP_ID — Foreign key to HR_ORGANIZATION_UNITS, providing the multi-tenant (Business Group) partitioning typical of HRMS data.
• NAME — Internal name of the item type, used by the form layer.
• TITLE — User-defined display name, sourced from FND_LOOKUP_VALUES via HR_LOOKUPS.
• UNITS — Unit of the value returned by the item type's SQL statement.
• DATATYPE — Datatype of the item type's result.
• COUNT_CLAUSE1 and COUNT_CLAUSE2 — SQL group functions applied when the item type aggregates data. The user's search term count_clause2 targets the second of these two aggregation fragments, which is combined with COUNT_CLAUSE1 when building the summary query.
• WHERE_CLAUSE — Up to 4000 characters defining the base SQL joins that produce the superset of processable data for the item type.
• SEEDED_DATA — Indicator distinguishing Oracle-seeded item types from customer-defined ones.

Common Use Cases and Queries

The primary legitimate use is diagnostic: identifying which group functions are applied by a given summary item type, particularly when investigating aggregation behaviour driven by COUNT_CLAUSE2.

SELECT item_type_id,
       name,
       title,
       datatype,
       units,
       count_clause1,
       count_clause2
FROM   apps.hr_summary_item_type
WHERE  count_clause2 IS NOT NULL
AND    business_group_id = :p_business_group_id
ORDER BY name;

A second scenario inspects the WHERE_CLAUSE to understand the joins that constrain the underlying summary query:

SELECT item_type_id,
       name,
       where_clause
FROM   apps.hr_summary_item_type
WHERE  seeded_data = 'Y'
AND    name LIKE '&pattern';

Because the view is a forms-support interface, any custom use should be restricted to read-only investigation under Oracle Support guidance, with the expectation that column semantics and availability may change between 12.1.1 and 12.2.2 and beyond.