Results for “de_pension_prov_type”

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

Overview

APPS.HR_DE_SOC_INS_ORG_INFO_V is a reporting view in Oracle E-Business Suite that exposes German social insurance organization information stored against HR organizations. It is a country-specific (Germany) localization object that surfaces the social insurance identifiers and provider classifications maintained for legal employers, health insurance funds, and pension institutions. The view returns rows from the HR_ORGANIZATION_INFORMATION table filtered to the organization information context DE_SOCIAL_INSURANCE_INFO, meaning it presents only those organization information records tagged as German social insurance data.

Because the data is held in the generic, flexfield-style HR_ORGANIZATION_INFORMATION structure, the underlying values are stored as positional attributes (ORG_INFORMATION1 through ORG_INFORMATION4) with no intrinsic business meaning at the database level. The view's principal role is therefore to translate this generic storage into named, meaningful columns and to decode the coded provider values into their descriptive equivalents using the HR_GENERAL.DECODE_LOOKUP function. This makes the view suitable for reporting, extracts, and integration interfaces that require employer social insurance numbers and provider type descriptions without needing to understand the physical layout of the organization information table. It is particularly relevant to German payroll and statutory reporting processes, where the Betriebsnummer (employer account number) and provider classifications are required outputs.

Underlying Base Objects

The view is defined over two documented objects:

• HR_ORGANIZATION_INFORMATION (SYNONYM) — referenced in the view as INF (HR_ORGANIZATION_INFORMATION INF). This is the base storage table for organization-related descriptive information. The view selects ORG_INFORMATION_ID, ORGANIZATION_ID, ORG_INFORMATION1 through ORG_INFORMATION4, and OBJECT_VERSION_NUMBER from it. The row set is restricted by the predicate INF.ORG_INFORMATION_CONTEXT = 'DE_SOCIAL_INSURANCE_INFO', so only German social insurance records are returned.
• HR_GENERAL (PACKAGE) — invoked through HR_GENERAL.DECODE_LOOKUP to convert stored lookup codes into their display meanings. Two lookups are used: DE_PENSION_PROV_TYPE for the pension provider code and DE_HEALTH_PROV_TYPE for the health provider code.

The view is owned by APPS and is read-only; it exposes no data of its own but projects and decodes columns from the base table. Because ORG_INFORMATION1 through ORG_INFORMATION4 are generic columns, the view is the authoritative mapping that assigns them German social insurance semantics.

Key Columns

• ORG_INFORMATION_ID — Primary identifier of the underlying organization information record.
• ORGANIZATION_ID — The HR organization (legal employer) to which the social insurance information belongs; the join key to organization and employer records.
• WEST_BETRIEBSNUMMER (ORG_INFORMATION1) — The West German employer account number (Betriebsnummer) used for statutory social insurance reporting.
• EAST_BETRIEBSNUMMER (ORG_INFORMATION2) — The East German employer account number, retained for the historical East/West distinction.
• HEALTH_PROVIDER_TYPE (ORG_INFORMATION3) — The stored code identifying the health insurance provider type.
• PENSION_PROVIDER_TYPE (ORG_INFORMATION4) — The stored code identifying the pension provider type. This column directly answers searches for the pension provider type.
• D_PENSION_PROVIDER_TYPE — The decoded, human-readable description of PENSION_PROVIDER_TYPE via lookup DE_PENSION_PROV_TYPE.
• D_HEALTH_PROVIDER_TYPE — The decoded description of HEALTH_PROVIDER_TYPE via lookup DE_HEALTH_PROV_TYPE.
• OBJECT_VERSION_NUMBER — Optimistic locking / row version column carried from the base record.

Common Use Cases and Queries

Typical uses include German statutory reporting extracts, employer master data validation, and integration with external social insurance or pension systems. The view avoids the need to decode context and lookup codes in the consuming query.

• Retrieve pension provider details for a specific legal employer:

SELECT organization_id,
       west_betriebsnummer,
       pension_provider_type,
       d_pension_provider_type
FROM   apps.hr_de_soc_ins_org_info_v
WHERE  organization_id = :p_organization_id;

• List all employers using a given pension provider type:

SELECT organization_id,
       west_betriebsnummer,
       d_pension_provider_type
FROM   apps.hr_de_soc_ins_org_info_v
WHERE  pension_provider_type = :p_provider_code;

• Bulk extract of employer account numbers and provider classifications for interfaces, joined to organization definitions via ORGANIZATION_ID.
• Data validation to detect records where decoded descriptions (D_PENSION_PROVIDER_TYPE or D_HEALTH_PROVIDER_TYPE) are null, indicating an invalid or missing lookup code.

Because the "D_" columns are produced by HR_GENERAL.DECODE_LOOKUP, consumers should be aware that decoding occurs at query time; the view always reflects current lookup definitions.