Results for “state_code”

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

Overview

PQP_ALIEN_STATE_TREATIES_F is a date-tracked (effective-dated) table owned by the HR schema within the PQP – Public Sector Payroll product family of Oracle E-Business Suite 12.1.1 and 12.2.2. As its description states, it stores State Alien treaty details — the configuration that determines whether a state-level tax treaty with an alien (non-resident) jurisdiction is honored for payroll tax withholding purposes. Public sector and higher-education employers frequently process pay for non-resident alien employees, and the applicability of a treaty exemption often varies by state and by the effective window in which the treaty is recognized. This table provides the reference data that payroll processes consult when evaluating state withholding treatment for such employees.

In Data Vault modeling terms, the mined relationship metadata classifies this object as standalone. This is a heuristic suggestion: no FK dependencies were detected linking it to other tables, so it is not naturally a hub or link. Its effective-dated, attribute-bearing nature means that, if modeled as a Data Vault artifact, it would most resemble a satellite carrying descriptive treaty attributes keyed by state code and effective dates. Implementers should treat this classification as a modeling suggestion rather than a definitive architectural directive.

Key Information Stored

The table contains 12 documented columns. The most important are:

• ALIEN_STATE_TREATY_INFO_ID — the system-generated surrogate identifier for a treaty record, forming part of the primary key PQP_ALIEN_STATE_TREATIES_PK.
• STATE_CODE — the state to which the alien treaty applies; a core business identifier.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date range during which the treaty configuration is valid, forming the date-tracked portion of the key.
• TREATY_HONORED_FLAG — a flag indicating whether the treaty is honored, which drives the payroll withholding decision.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns recording who last changed the row and when.
• CREATED_BY, CREATION_DATE — creation audit columns.
• OBJECT_VERSION_NUMBER — the optimistic-locking version counter used by the OAF/BC4J framework to detect concurrent updates.
• ZD_EDITION_NAME — the editioning metadata column associated with the EBS online patching (edition-based redefinition) model in 12.2.

The documented primary key is PQP_ALIEN_STATE_TREATIES_FK on (STATE_CODE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). The unique index PQP_ALIEN_STATE_TREATIES_UK (STATE_CODE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ZD_EDITION_NAME) is a business-key candidate, extending the key with the editioning column. Note that the surrogate ALIEN_STATE_TREATY_INFO_ID and the business date-tracked key are distinct; the surrogate is the row identifier while STATE_CODE plus effective dates form the business identity.

Common Use Cases and Queries

Typical scenarios include determining whether a treaty is honored for a given state during a payroll run, auditing treaty configuration changes over time, and building date-effective reporting. A representative query retrieves the currently active treaty row for a state:

• SELECT state_code, treaty_honored_flag, effective_start_date, effective_end_date FROM pqp_alien_state_treaties_f WHERE state_code = :p_state_code AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Historical/date-effective reporting joins payroll results to this table on state_code and the pay-period date falling within the effective window.
• Audit reports select last_updated_by and last_update_date to track configuration maintenance.

Reporting consumers typically filter on TREATY_HONORED_FLAG and constrain by effective dates to avoid returning multiple overlapping versions of the same state treaty.

Related Objects

The mined metadata classifies this table as standalone, so no formal FK relationships were detected. In practice, it is referenced through logical joins rather than enforced constraints. The most significant related objects include:

• Payroll/State Tax processing logic in PQP — validates treaty applicability using STATE_CODE and the effective dates against payroll run dates.
• State Tax Reference tables (e.g., state tax rules/balances) — joined logically on STATE_CODE to align treaty treatment with state withholding rules.
• Non-resident alien employee tax information records — used to associate an employee's alien status with the applicable state treaty.
• Payroll action/run results tables — receive the treaty-honored determination as an input to net pay calculations.
• Date-tracked configuration views — typically expose the current effective row filtered by effective_start_date and effective_end_date.

Because the metadata documents no enforced FKs, developers should confirm logical join keys (principally STATE_CODE) against their specific implementation before relying on referential integrity.