Search Results pay_report_format_items_pk

Overview

PAY_REPORT_FORMAT_ITEMS_F is a date-tracked (datetrack) table owned by the HR schema in Oracle E-Business Suite, classified under the PAY (Payroll) product family. It stores the individual items that define a report mapping — the discrete formatting and selection directives that determine how payroll report content is assembled and presented. Each row represents one report item within a given report format, scoped by report type, qualifier, and category, and associated with a user entity.

The "_F" suffix indicates this is a date-effective ("f" for full datetrack) table. The presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, together with OBJECT_VERSION_NUMBER, confirms Oracle's standard date-tracked architecture: historical versions of a row are retained, and queries must anchor to a point in time (SYSDATE between the effective dates) to retrieve the current definition. The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the table functions largely as an independent reference of report-item definitions rather than as a hub or link in a normalized vault model. This classification should be treated as a modeling suggestion rather than a definitive architectural statement.

In EBS 12.1.1 and 12.2.2, the table's role is confined to the configuration and metadata layer of payroll reporting. It does not carry transactional payroll balances; instead it governs how report formats are decomposed into their constituent items, which are then consumed by the reporting engine when rendering output.

Key Information Stored

Seventeen columns are documented for version 12.2.2. The most operationally significant are described below.

• REPORT_FORMAT_ITEM_ID — Surrogate identifier for the individual report format item. Although the primary key is defined as a composite business key, this surrogate assists in referencing an item unambiguously.
• REPORT_TYPE — Business-key column identifying the category of report (for example, a specific payroll report family).
• REPORT_QUALIFIER — Business-key column refining the report identity beyond type.
• REPORT_CATEGORY — Business-key column providing a further classification dimension.
• USER_ENTITY_ID — Business-key column associating the item with a user-defined entity, typically the context or object to which the format item applies.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-track columns bounding the validity of each row version.
• ARCHIVE_TYPE — Indicates the archival behavior applicable to the item.
• UPDATABLE_FLAG — Flags whether the item may be modified by end users.
• DISPLAY_SEQUENCE — Controls the ordering in which items appear when rendered.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle framework to detect concurrent updates.
• ZD_EDITION_NAME — Editioning column supporting Oracle EBS 12.2 online patching; it participates in the unique index that defines the business key.
• Audit columns — LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide standard "who/when" traceability.

The primary key is PAY_REPORT_FORMAT_ITEMS_PK, defined over REPORT_TYPE, REPORT_QUALIFIER, REPORT_CATEGORY, USER_ENTITY_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The unique index additionally includes ZD_EDITION_NAME, reflecting the 12.2 editioning model. These columns constitute the business-key candidates; the date-track columns are integral to the key because multiple historical versions coexist.

Common Use Cases and Queries

Typical scenarios include auditing which items are configured for a given report type, verifying display ordering, and confirming whether an item is user-updatable. A representative query anchors to the current effective date and edition:

• Current items for a report type: SELECT report_format_item_id, report_qualifier, report_category, display_sequence, updatable_flag FROM pay_report_format_items_f WHERE report_type = :p_type AND SYSDATE BETWEEN effective_start_date AND effective_end_date AND zd_edition_name = 'SET1';
• Historical comparison: Compare DISPLAY_SEQUENCE or UPDATABLE_FLAG across EFFECTIVE_START_DATE versions to determine when a report mapping changed.
• Archival review: Filter on ARCHIVE_TYPE to identify items subject to particular retention behavior.
• Concurrency/version checks: Use OBJECT_VERSION_NUMBER when building custom DML that mirrors the framework's optimistic locking.

Because the table is date-tracked, reports built against it without an effective-date predicate will return duplicate rows across versions — a common source of confusion in ad-hoc SQL. Always constrain by both the effective-date range and the ZD_EDITION_NAME in 12.2 environments.

Related Objects

The mined relationship data classifies this table as standalone, indicating no foreign keys into other tables were identified in the documented model. Consequently, related objects should be understood through shared business-key columns rather than enforced referential constraints.

• PAY_REPORT_FORMAT_ITEMS (where present) — The non-dated or alternate repository sharing REPORT_FORMAT_ITEM_ID and the business-key columns.
• Report format definition tables — Parent format entities joined via REPORT_TYPE, REPORT_QUALIFIER, and REPORT_CATEGORY.
• User entity tables — Joined via USER_ENTITY_ID to resolve the entity to which a format item applies.
• Payroll reporting concurrent programs — Consume format items at runtime to render report output.
• Editioning metadata views — Object version and edition columns align with EBS 12.2 online patching views.

No explicit foreign-key relationships were documented in the ETRM metadata, so integrators should validate joins against the business-key columns and the editioning model before relying on referential integrity.