Search Results pay_element_type_usages_uk1

Overview

PAY_ELEMENT_TYPE_USAGES_F is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the relationship between element types and run types, defining which elements are either included in or excluded from a specific payroll run type. In effect, it acts as the configuration bridge that determines the set of earnings, deductions, and other payroll elements processed when a given run type (for example, a regular payroll run or a supplementary run) is executed.

The table is date-tracked, with effective start and end dates embedded in both its primary key and a unique business-key index, reflecting Oracle HRMS date-effective (datetrack) design conventions. The heuristic Data Vault classification mined from the foreign key structure is standalone, suggesting it may be modeled as a link-style relationship between element types and run types at a conceptual level; this classification should be treated as a modeling suggestion rather than a normative definition.

Key Information Stored

The table contains 16 documented columns. The most operationally significant are:

• ELEMENT_TYPE_USAGE_ID — the system-generated surrogate primary key uniquely identifying each usage record.
• ELEMENT_TYPE_ID — identifies the element type (earning, deduction, or other payroll element) whose usage is being defined. Part of the business-key unique index.
• RUN_TYPE_ID — identifies the payroll run type to which the element usage applies. Part of the business-key unique index.
• INCLUSION_FLAG — indicates whether the element is included in or excluded from the associated run type, which is the central behavioral attribute of the row.
• USAGE_TYPE — categorizes the nature of the usage relationship between the element type and run type.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — datetrack columns controlling the period during which the usage record is in force; both participate in the primary key and the unique index.
• BUSINESS_GROUP_ID — the enterprise/legislative grouping under which the usage is defined. Part of the business-key unique index.
• LEGISLATION_CODE — the legislation under which the usage configuration applies. Part of the business-key unique index.
• ZD_EDITION_NAME — an editioning/versioning attribute present in 12.2.x, also part of the unique index.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.
• OBJECT_VERSION_NUMBER — used for optimistic locking in the Oracle Application Framework layer.

The primary key PAY_ELEMENT_TYPE_USAGES_PK is defined on ELEMENT_TYPE_USAGE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The unique business key PAY_ELEMENT_TYPE_USAGES_UK1 is defined on ELEMENT_TYPE_ID, RUN_TYPE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID, LEGISLATION_CODE, and ZD_EDITION_NAME, ensuring a single effective usage definition per element type, run type, and legislative context.

Common Use Cases and Queries

Typical scenarios include diagnosing why an element is not being processed in a payroll run, auditing which elements are associated with a run type, and validating configuration before a payroll cycle. A common query retrieves all elements linked to a run type within a specific business group:

• Join PAY_ELEMENT_TYPE_USAGES_F to PAY_RUN_TYPES_F on RUN_TYPE_ID to list the elements and their INCLUSION_FLAG for a given run type.
• Filter by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE (or SYSDATE BETWEEN) to return only currently effective usage rows.
• Join to PAY_ELEMENT_TYPES_F on ELEMENT_TYPE_ID to obtain element names alongside the usage definition.
• Group by INCLUSION_FLAG to count included versus excluded elements per run type for reporting.
• Restrict by LEGISLATION_CODE and BUSINESS_GROUP_ID to scope results to a specific legislative environment.

Related Objects

• PAY_ELEMENT_TYPES_F — joined via ELEMENT_TYPE_ID; supplies the element type definition for each usage record.
• PAY_RUN_TYPES_F — joined via RUN_TYPE_ID; defines the run type to which the usage applies.
• PAY_ELEMENT_TYPE_USAGES_F datetrack views (e.g., standard _F/_D counterparts) — present the effective-dated view of the same data.
• FND_APPLICATION / business group tables — referenced through BUSINESS_GROUP_ID and LEGISLATION_CODE for scoping.
• Payroll run processing programs — read this table to determine which elements to include or exclude during a payroll run.

Because the table is classified as standalone with respect to its foreign keys, it serves as a configuration hub connecting element types and run types rather than a satellite of either.