Search Results pn_insurance_type




Overview
Explore in AI Chat

Overview

APPS.PN_INSUR_REQUIRE_HISTORY_V is a reporting view in the Oracle E-Business Suite Property Manager (PN) module. It presents insurance requirement records together with their associated insurance type descriptions and a historical/current indicator. The view combines data from the live insurance requirements table with a lookup-driven translation of the insurance type code, and it exposes history-oriented columns that allow callers to distinguish current records from prior versions. In Oracle EBS 12.1.1 and 12.2.2 the object is owned by APPS and is typically consumed by concurrent programs, Oracle Reports, OBIEE/XML Publisher extracts, and custom integrations that need a denormalized, human-readable view of lease insurance requirements.

The name reflects its intended purpose: to serve as a "history" view over insurance requirements. A synthetic CURRENT_FLAG column of value 'Y' identifies rows sourced from the current requirement set, while the historical branch (the UNION ALL portion referencing PN_INSUR_REQUIRE_HISTORY) surfaces prior versions. This design lets a single query retrieve both current and historical insurance requirement data without the caller joining to the underlying history table directly.

Underlying Base Objects

The documented base objects are:

  • PN_INSURANCE_REQUIREMENTS (referenced via synonym) — the current insurance requirements table, aliased ins in the view text.
  • PN_INSUR_REQUIRE_HISTORY (referenced via synonym) — the historical insurance requirements table combined through UNION ALL.
  • FND_LOOKUPS (view) — aliased look, used to resolve insurance_type_lookup_code into a display meaning.
  • FND_GLOBAL (package) — recorded as a referenced object, consistent with standard EBS views that call FND_GLOBAL for org/session context.

The core join condition is look.lookup_type = 'PN_INSURANCE_TYPE' and look.lookup_code = ins.insurance_type_lookup_code. This makes the view the canonical place to obtain the insurance type meaning for any requirement row, rather than joining FND_LOOKUPS manually.

Key Columns

  • insurance_requirement_id — primary identifier of the requirement row.
  • insurance_type_lookup_code — the stored code from the PN_INSURANCE_TYPE lookup; matched by the user's search term "pn_insurance_type".
  • insurance_type — the lookup meaning, i.e., the translated, user-facing insurance type.
  • lease_id and lease_change_id — the lease and lease change the requirement belongs to.
  • policy_start_date, policy_expiration_date — policy validity window.
  • insurer_name, policy_number — insurer and policy identifiers.
  • insured_amount, required_amount — coverage amounts and the contractually required amount.
  • status — requirement status.
  • attribute_category and attribute1 through attribute15 — the standard EBS descriptive flexfield columns.
  • INSURANCE_HISTORY_ID, NEW_LEASE_CHANGE_ID, CURRENT_FLAG — synthetic columns; the first two are emitted as to_number(NULL) in the current branch, and CURRENT_FLAG is 'Y'.

Common Use Cases and Queries

Typical uses include reporting on lease insurance compliance, auditing coverage against required amounts, and exposing insurance type descriptions without a lookup join. A representative query filtering by insurance type meaning is:

  • SELECT insurance_type, lease_id, insurer_name, policy_number, insured_amount, required_amount, status FROM apps.pn_insur_require_history_v WHERE insurance_type = 'General Liability';
  • SELECT lease_id, COUNT(*) FROM apps.pn_insur_require_history_v WHERE CURRENT_FLAG = 'Y' GROUP BY lease_id;
  • SELECT * FROM apps.pn_insur_require_history_v WHERE insurance_type_lookup_code = :p_code AND policy_expiration_date < SYSDATE;

Because the view spans both the current and history tables, it is well suited to point-in-time audits and integration extracts where both present-state and prior insurance requirement records are needed in a single result set.