Results for “pqp_pension_types_f”

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

Overview

PQP_PENSION_TYPES_F is a dated, effective-dated configuration table owned by the HR schema and delivered under the PQP – Public Sector Payroll product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to store the pension types used by public sector payroll processing. Each row defines a pension plan or scheme together with the statutory, contribution, and tax treatment rules that payroll engines apply when processing an employee assignment or a payroll run.

The table is a DateTracked ("_F") entity. It carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE alongside its primary key, so multiple historical versions of the same pension type can coexist. The physical schema is substantial: the ETRM 12.2.2 metadata documents 99 columns, most of which are reserved descriptive flexfield segments (PTY_ATTRIBUTE1–20 and PTY_INFORMATION1–20) and its associated context columns PTY_ATTRIBUTE_CATEGORY and PTY_INFORMATION_CATEGORY.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone. This suggests treating PQP_PENSION_TYPES_F as a hub-style reference entity keyed by PENSION_TYPE_ID, optionally with a satellite capturing the dated descriptive and calculation attributes. It is not modeled as a link, as no foreign-key relationships are mined from its structure.

Key Information Stored

The primary key is PQP_PENSION_TYPES_F_PK, composed of PENSION_TYPE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. A second documented key, PQP_PENSION_TYPES_PK, uses the same three columns, indicating the effective-dated uniqueness constraint on the pension type over time. PENSION_TYPE_ID is the surrogate identifier; the business-key candidate pairs it with the effective date range.

• PENSION_TYPE_ID – surrogate identifier for the pension type.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – DateTrack range controlling which version applies at a given payroll date.
• PENSION_TYPE_NAME and DESCRIPTION – user-facing name and description.
• PENSION_CATEGORY / PENSION_SUB_CATEGORY / SPECIAL_PENSION_TYPE_CODE – classification of the plan.
• PENSION_PROVIDER_TYPE – identifies the provider arrangement.
• LEGISLATION_CODE – the payroll legislation under which the type is valid.
• SALARY_CALCULATION_METHOD and PENSION_BASIS_CALC_METHOD – rules used to derive pensionable pay.
• EE_CONTRIBUTION_PERCENT / ER_CONTRIBUTION_PERCENT – employee and employer percentage rates.
• EE_ANNUAL_LIMIT / ER_ANNUAL_LIMIT and the annual salary thresholds – statutory contribution ceilings.
• EE_CONTRIBUTION_BAL_TYPE_ID / ER_CONTRIBUTION_BAL_TYPE_ID – balance types credited with contributions.
• BALANCE_INIT_ELEMENT_TYPE_ID – element type used to initialize balances.
• BUSINESS_GROUP_ID – multi-tenant partition for the enterprise business group.
• MINIMUM_AGE / MAXIMUM_AGE / EE_AGE_THRESHOLD / ER_AGE_THRESHOLD – age-based eligibility and rate switches.
• PREVIOUS_YEAR_BONUS_INCLUDED, RECURRING_BONUS_PERCENT, NON_RECURRING_BONUS_PERCENT – bonus inclusion and treatment in pensionable pay.
• Standard WHO audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical applications include validating pension setup before a payroll run, auditing contribution rates, and reporting the pension types available to a business group for a given date. Because the table is effectivity-controlled, all queries should filter on the effective range against a target date.

• Current effective pension types — retrieve all active rows: SELECT pension_type_id, pension_type_name, legislation_code FROM pqp_pension_types_f WHERE business_group_id = :bg AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Contribution rate review — list employee and employer percentages by category for audit.
• Point-in-time lookup — resolve the version of a pension type in force at a historical payroll period by bounding the effective dates.
• Age-conditioned rates — identify types where EE_AGE_THRESHOLD or ER_AGE_THRESHOLD applies to drive age-band contribution logic.
• Legislation-scoped reporting — group plans by LEGISLATION_CODE to support public sector statutory reporting.

Report writers should join on BUSINESS_GROUP_ID to respect multi-tenant data security and avoid returning rows from other enterprises.

Related Objects

Because the metadata classifies PQP_PENSION_TYPES_F as standalone, no formal foreign keys are mined from its structure. The following objects are the most significant tables, balance types, and element references logically associated with it, joined on the documented columns:

• PAY_BALANCE_TYPES — joined via EE_CONTRIBUTION_BAL_TYPE_ID and ER_CONTRIBUTION_BAL_TYPE_ID to resolve contribution balance definitions.
• PAY_ELEMENT_TYPES_F — joined via BALANCE_INIT_ELEMENT_TYPE_ID to identify the balance initialization element.
• FND_ID_FLEX_STRUCTURES — the descriptive flexfield definition used by PTY_ATTRIBUTE_CATEGORY and PTY_INFORMATION_CATEGORY.
• PER_BUSINESS_GROUPS — joined via BUSINESS_GROUP_ID to scope pension types to an enterprise.
• FND_LEGAL_ENTITIES / HR_LEGAL_ENTITIES — context for LEGISLATION_CODE when reporting per legal entity.
• PAY_PENSION_TYPES — the base payroll pension type definition often aligned with this PQP record.
• PER_ASSIGNMENTS_F — assignment pension enrollment records that reference the pension type for payroll processing.

Consult the ETRM 12.2.2 documentation for the definitive, version-specific column and relationship list before extending these joins in production queries.