Results for “source_system”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The PAY_US_CONTRIBUTION_HISTORY table, owned by the HR schema within the Oracle E-Business Suite Payroll (PAY) module, stores yearly consolidated contribution data for a person, per Government Reporting Entity (GRE), per contribution type. It is a US-specific legislative table that supports payroll processing for benefit and deduction contribution tracking, including limits such as maximum contribution thresholds. In Release 12.1.1 and 12.2.2, this table is a VALID, documented object with a physical schema of 49 columns.

From a data modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is standalone. This suggests that the table can be modeled as an independent structure, or, where consolidation by person, GRE, and contribution type is central, as a satellite attached to a person/GRE hub-link combination. The absence of documented FK dependencies reinforces its role as a consolidated history store rather than a transactional detail.

Key Information Stored

The table uses a surrogate primary key, CONTR_HISTORY_ID, defined by the PAY_US_CONTRIBUTION_HISTORY_PK constraint. A separate unique index, PAY_US_CONTRIBUTION_HISTORY_UK, establishes the business key across CONTR_TYPE, DATE_FROM, DATE_TO, PERSON_ID, and TAX_UNIT_ID. This combination guarantees that a person’s contribution history for a given contribution type, GRE (TAX_UNIT_ID), and effective date range is uniquely represented.

• CONTR_HISTORY_ID — Surrogate primary key for each history record.
• PERSON_ID — Identifies the person whose contributions are being tracked.
• CONTR_TYPE — Contribution type (for example, a benefit or deduction category) being consolidated.
• TAX_UNIT_ID — The Government Reporting Entity (GRE) associated with the contribution.
• DATE_FROM / DATE_TO — Effective date range of the consolidated contribution record, typically the yearly period.
• AMT_CONTR — The actual contribution amount consolidated for the period.
• MAX_CONTR_ALLOWED — The maximum contribution permitted, used for limit tracking.
• INCLUDABLE_COMP — The includable compensation base used in contribution calculations.
• BUSINESS_GROUP_ID — Business group context for the record.
• LEGISLATION_CODE — Legislative context, typically US.
• SOURCE_SYSTEM — Origin of the contribution data.
• CONTR_INFORMATION_CATEGORY and CONTR_INFORMATION1–30 — Descriptive flexfield (DFF) segments holding additional, client-configurable contribution attributes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — Standard WHO audit and concurrency columns.

Common Use Cases and Queries

Typical reporting scenarios include year-end contribution summaries, contribution limit monitoring, and reconciliation of consolidated contributions by GRE and contribution type. A common pattern filters by person and date range:

• Querying a person’s contribution totals for a tax year: SELECT CONTR_TYPE, TAX_UNIT_ID, SUM(AMT_CONTR) FROM HR.PAY_US_CONTRIBUTION_HISTORY WHERE PERSON_ID = :p_person AND DATE_FROM >= :p_start AND DATE_TO <= :p_end GROUP BY CONTR_TYPE, TAX_UNIT_ID;
• Identifying contributions approaching limits: SELECT PERSON_ID, CONTR_TYPE, AMT_CONTR, MAX_CONTR_ALLOWED FROM HR.PAY_US_CONTRIBUTION_HISTORY WHERE AMT_CONTR >= MAX_CONTR_ALLOWED * 0.9;
• GRE-level aggregation for compliance reporting, grouped by TAX_UNIT_ID and BUSINESS_GROUP_ID.
• Audit extraction using CREATION_DATE and LAST_UPDATE_DATE to isolate recently changed records.

Related Objects

The metadata identifies the table as standalone with no documented foreign-key relationships. Practically, it is joined to core payroll and HR entities through its business columns:

• PER_ALL_PEOPLE_F — joined on PERSON_ID for person attributes.
• PAY_US_TAX_UNITS or the GRE definition table — joined on TAX_UNIT_ID for GRE details.
• PAY_ELEMENT_TYPES_F — referenced indirectly via CONTR_TYPE mapping to contribution elements.
• PAY_PAYROLL_ACTIONS / PAY_RUN_RESULTS — source transactional data consolidated into this history.
• PAY_US_CONTRIBUTION_DEFINITIONS — defines contribution types and limits applied here.
• PAY_BALANCES / PAY_BALANCE_FEEDS — balance infrastructure that aggregates contribution amounts.