Results for “fa_ins_mst_pols”

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

Overview

FA_INS_MST_POLS is the master policy table for insurance within the Oracle E-Business Suite Assets module (OFA), owned by the FA schema. It stores the header-level definition of insurance policies associated with fixed assets, capturing the policy identity, the vendor that underwrites the coverage, and the calculation method applied to derive insured values. In Oracle EBS 12.1.1 and 12.2.2, this table acts as a configuration and reference point that links asset-level insurance records to the broader policy framework maintained in FA_INS_POLICIES.

From a Data Vault modeling perspective, the mined foreign key structure suggests a satellite-leaning classification. FA_INS_MST_POLS carries descriptive attributes and a single foreign key to FA_INS_POLICIES, which is characteristic of a satellite attached to a parent hub or link rather than an independent hub or a pure link table. This classification is a modeling heuristic, not a mandated EBS construct, and should be treated as a suggested pattern for downstream warehouse design.

Key Information Stored

The table is documented with ten columns in ETRM 12.2.2. The principal identifiers and descriptive attributes include:

• ASSET_POLICY_ID — The surrogate primary key for the record. It is also the foreign key referencing FA_INS_POLICIES.ASSET_POLICY_ID, anchoring this master policy to its parent policy record. A unique index, FA_INS_MST_POLS_U1, enforces uniqueness on this column.
• POLICY_NUMBER — The business identifier for the insurance policy as issued by the underwriter. Together with VENDOR_ID, it forms the second unique business-key candidate, enforced by FA_INS_MST_POLS_U2.
• VENDOR_ID — The identifier of the insurance vendor or carrier providing the coverage. This is part of the composite unique key with POLICY_NUMBER.
• VENDOR_SITE_ID — The specific vendor site associated with the policy, distinguishing among multiple locations or contacts for the same vendor.
• CALCULATION_METHOD — The method used to compute the insured value for assets under this policy. This governs how coverage amounts are derived.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns recording the most recent modification timestamp, user, and login session.
• CREATION_DATE, CREATED_BY — Standard audit columns capturing when and by whom the record was first inserted.

The distinction between the surrogate key (ASSET_POLICY_ID) and the business-key candidates (POLICY_NUMBER plus VENDOR_ID) is important for integration and reporting, as external extracts typically key on the business identifier while internal joins rely on the surrogate.

Common Use Cases and Queries

Typical usage centers on insurance reporting, coverage audits, and reconciliation of asset insurance records. A common query joins the master policy to its parent policy record to retrieve full policy context:

• Listing all master policies for a given vendor: SELECT POLICY_NUMBER, VENDOR_ID, CALCULATION_METHOD FROM FA_INS_MST_POLS WHERE VENDOR_ID = :vendor_id.
• Joining to the parent policy table to obtain asset-level detail: SELECT m.POLICY_NUMBER, p.ASSET_ID FROM FA_INS_MST_POLS m JOIN FA_INS_POLICIES p ON m.ASSET_POLICY_ID = p.ASSET_POLICY_ID.
• Data warehouse loads that stage the table as a satellite, using ASSET_POLICY_ID as the link to the policy hub and POLICY_NUMBER plus VENDOR_ID as the natural business key for change detection.

Auditors frequently query CALCULATION_METHOD distribution to verify that insured values are computed consistently across policies. Reporting extracts often filter on LAST_UPDATE_DATE to capture incremental changes for downstream feeds.

Related Objects

The most significant related object is the parent policy table referenced by the documented foreign key:

• FA_INS_POLICIES — Referenced via FA_INS_MST_POLS.ASSET_POLICY_ID = FA_INS_POLICIES.ASSET_POLICY_ID. This is the primary dependent relationship and the anchor for asset-level insurance detail.
• FA_INS_POLICIES (asset-level rows) — Provides the granular asset-to-policy association that the master policy governs.
• PO_VENDORS / AP_SUPPLIERS — Logical reference for VENDOR_ID and VENDOR_SITE_ID, used when resolving carrier names in reports.
• FA_ADDITIONS — Joins indirectly through the policy hierarchy to associate insured assets with their descriptive asset records.
• Standard FA audit and concurrent program interfaces that read master policy data when processing insurance valuation runs.

These relationships should be validated against the specific EBS release in use, as column availability and indexing may vary between 12.1.1 and 12.2.2.