Search Results insurance_type




Overview
Explore in AI Chat

Overview

The PN_INSUR_REQUIRE_HISTORY_V view is a reporting and integration object in the Oracle E-Business Suite Property Manager (PN) module. It presents a consolidated, historized view of insurance requirements associated with leases. Insurance requirements in Property Manager capture the obligations placed on a lessee or lessor to maintain specific types of coverage — for example general liability, property, or workers' compensation — together with policy details such as insurer, policy number, insured amount, and required amount.

The view exists so that concurrent programs, forms, and external integrations can retrieve both the current active insurance requirement rows and their historical counterparts through a single query interface. A distinctive characteristic of this view is that it exposes a column named INSURANCE_HISTORY_ID, which is the term the user searched for. In the documented view text this column is materialized as TO_NUMBER (NULL) INSURANCE_HISTORY_ID for the current-requirement branch of the UNION ALL, while the historical branch supplies the genuine history identifier. This design lets downstream consumers filter or group by history without branching their own SQL against two different sources.

Because the object is registered in the APPS schema with a status of VALID, it is a supported read-only interface. It should not be used for direct DML; base-table maintenance is performed through the Property Manager application or its public APIs.

Underlying Base Objects

Per the documented 12.2.2 metadata, the view references four base objects:

  • PN_INSURANCE_REQUIREMENTS (synonym) — the primary transaction table holding the current or effective insurance requirement records for each lease and lease change.
  • PN_INSUR_REQUIRE_HISTORY (synonym) — the audit/history table that stores prior versions of insurance requirement rows, keyed by an insurance history identifier. This is the source of the INSURANCE_HISTORY_ID value.
  • FND_LOOKUPS (view) — the Oracle Application Object Library lookup view used to resolve the coded INSURANCE_TYPE_LOOKUP_CODE into a translated, user-facing meaning via lookup type PN_INSURANCE_TYPE.
  • FND_GLOBAL (package) — the standard EBS context package, typically referenced for organizational or user context (for example, FND_GLOBAL.ORG_ID) inside the view definition.

The view is defined as a UNION ALL of these sources, joining the requirements and history tables to lookups on the insurance type code. A constant 'Y' CURRENT_FLAG distinguishes current rows, while the history branch carries the persisted history identifier.

Key Columns

Common Use Cases and Queries

Typical scenarios include insurance compliance reporting, lease audit trails, and interfaces to treasury or risk systems. The following examples assume EBS 12.1.1 or 12.2.2 with the APPS schema.

1. Current insurance requirements for a lease:

  • SELECT insurance_requirement_id, insurance_type, insurer_name, policy_number, insured_amount, required_amount, policy_expiration_date FROM apps.pn_insur_require_history_v WHERE lease_id = :p_lease_id AND current_flag = 'Y';

2. Full history for a requirement, ordered by history identifier:

  • SELECT insurance_history_id, policy_start_date, policy_expiration_date, insured_amount, status FROM apps.pn_insur_require_history_v WHERE insurance_requirement_id = :p_req_id AND insurance_history_id IS NOT NULL ORDER BY insurance_history_id;

3. Coverage shortfall analysis (insured below required):

  • SELECT lease_id, insurance_type, required_amount, insured_amount FROM apps.pn_insur_require_history_v WHERE current_flag = 'Y' AND NVL(insured_amount,0) < NVL(required_amount,0);

4. Expiring policies in the next 30 days:

  • SELECT lease_id, insurer_name, policy_number, policy_expiration_date FROM apps.pn_insur_require_history_v WHERE current_flag = 'Y' AND policy_expiration_date BETWEEN SYSDATE AND SYSDATE+30;

Because the view unions current and historical rows, callers must always qualify queries with CURRENT_FLAG = 'Y' or a non-null INSURANCE_HISTORY_ID predicate to avoid double counting. Query performance is aided by the UNION ALL design, which permits the optimizer to push predicates into each branch independently.