31 Insurance Claims Tables

This chapter details the Insurance Claims tables in the Oracle Insurance Data Foundation application.

Topics:

· About Insurance Claims

· About Insurance Claims Dimension Tables

· About Insurance Claims T2Ts (Result Tables)

· Deploying Insurance Claims Tables on Hive

· Populating Insurance Claims Dimension Tables

· Populating Insurance Claims T2Ts (Result Tables)

· Related Topics

About Insurance Claims

An insurance claim is a formal request to an Insurance Company for coverage or compensation for a covered loss or policy event. The Insurance Company validates the claim and, after approval, issues payment to the insured or to an approved interested Party on behalf of the insured.

The OIDF application sources data from the claim systems after the claim is generated.

The Claims tables store the claim process performed to date including the claim dates, status, and amounts.

The following table depicts the difference between the Statutory Insurance and the Non-statutory Insurance:

Table 282: Difference between the Statutory Insurance and the Non-statutory Insurance
Statutory Insurance	Non-statutory Insurance
This is a type of insurance, which the insured is required to buy under a country, state, or federal law.	This is a type of insurance, which is up to the buyer to purchase or not.

An Illustration of the Insurance Claims Process Flow

This section contains an illustration of the Insurance Claims process flow in chronological order.

This is an example of the Insurance Claims process after the fire occurrence in a technology park.

Assume that, in a Tier I city, in a technology park, in a company building of four floors, there is a fire incident on the second floor. Due to the extent of the fire, the building has collapsed into its underground parking area. As a result, there are damages and destruction caused to the equipment and furniture inside the building, a few injured people, and a few fire-related deaths. In addition, the vehicles inside the building parking area are damaged or destroyed. During the building collapse, the adjacent building is damaged. As the collapsed building, the adjacent damaged building, and the technology park are insured, they apply for the Insurance Claim for recovery.

The Insurance Company checks their reserve and the details of the Insured. The Insurance Company Assessor is sent to the collapsed building location to assess the extent of damage and destruction in detail. The Assessor analyzes the fire incident location, the injured, and the deceased lives. Then the Assessor creates a detailed report, which also includes the damage due to the water used to put out the fire and the disabilities caused. The Assessor cross-verifies the report with the Insurance Policy terms. All these damages, destructions, and loss of lives are caused due to one fire incident. For an incidence to be an occurrence, there must be common traits across multiple claims, same date, same location, and then the traits are grouped together for future use. The insurer can set aside a calculated amount of money for a similar incidence that can occur the next time.

Even though the Insurance Company focuses on completing the claims process on time, there can be possible flaws in the process due to human error or undiscovered reasons. For example, the Nominee is not the Beneficiary, or an employee was not even in the incident premises, however, that employee was hurt outside due to some other reason and is claimed as injured by the insured company, and so on.

Based on the assessment of the policy terms, report, and claim terms, the claim is processed or rejected.

Insurance Claims Process Flow

This is the representation of the conceptual model of Insurance Policy Claims.

Figure 130: The representation of the conceptual model of Insurance Policy Claims

This illustration shows the Insurance Policy Claims conceptual model representation. The explanation follows this illustration.

The process flow of an Insurance Policy Claim is as follows:

1. A Claim is created and the data is stored in the Claim Dimension table.

2. The claim policy map is used to store the relationship between the claim and the policy for which it is created. The mapping details are stored in the Claim Party Map table.

3. The details of a claim from the policyholder, the amount paid by the insurer, and the summary of the claim’s transactions to date are stored in the Claim Details table.

4. The insurer must conduct the claim investigation. The insurer obtains information to evaluate an insurance claim. The investigators look for evidence whether a claim is legitimate or illegitimate. The investigation details are stored in the Claim Investigation Details table.

5. After the claim investigation is complete, the claim amount is estimated and the claim breakup is done. The claim breakup is where an insurer sets aside a claim amount for a policy.

6. The transactions related to a claim are stored in the Claims Transactions table.

NOTE:

The Claim Identifier column is added to the tables FCT_PARTY_MEDICAL_CONDITN_DTLS, FCT_PARTY_MEDICAL_TREATMENTS, and FCT_PARTY_DISABILITY_DETAILS to link the party medical attributes to the insurance claims.

About Insurance Claims Dimension Tables

This section provides mapping details and descriptions of the Insurance Claims Dimension tables.

About Insurance Claim Details Dimension Tables

The Insurance Claims Details Dimension table names and their description are as follows.

Table 283: The Insurance Claims Details Dimension table names and their description
Logical Dimension Table Name	Dimension Table Description
Dimension Claim Status	Stores the list of all status codes and descriptions that are applicable for a claim transaction.
Claim Dimension	Stores the list of all claims.
Catastrophe Events Dimension	Stores the catastrophic event details for a claim.
Claim Feature Status Dimension	Stores the status of a claim, based on which several calculations change.
Premium Plan Type Dimension	Stores the premium plan type details. Sample values: AUDIT and EST.
Claim Offer Dimension	Stores details of the offer given on a claim.
Claim Risk Category Dimension	Stores the category details of the claim risk. This table is used to specify the situations, where the claim risk category may be based on several criteria such as the claim amount, the manner of loss, length of the time policy is in force, contestability or other investigative issues, and many other factors. Sample values: OTHER, UNKNOWN, HIGH, LOW, and MEDIUM.
Claim Status Reason Dimension	Stores details of the claim status reason. This table provides additional details about the reason for the current claim status. Sample values: ACTIVEREVIEW, ADDINVEST, BACKPROC, INVALIDBENE, LITIGATION, RECOVERED, and so on.
Claim Type Dimension	Stores details of the types of claims types. Sample values: DEATH, DISMEMBERMENT, DISABILITY, ILLNESS, and UNEMPLOYMENT.
International Statistical Classification Of Diseases Dimension	Stores classification details of the international statistics of diseases. The ICD is the International Statistical Classification of Diseases and Related Health Problems. The ICD is a medical classification list maintained by the World Health Organization (WHO). The list contains codes for diseases, signs, and symptoms, abnormal findings, complaints, social circumstances, and external causes of injury or diseases.

The mapping details for the Insurance Claims Details Dimension tables are as follows.

Table 284: The mapping details for the Insurance Claims Details Dimension tables
Map Reference Number	Source Table Name	Logical Stage Table Name	Dimension Table Name	Logical Dimension Table Name
234	STG_CLAIM_STATUS_MASTER	Stage Claim Status Master	DIM_CLAIM_STATUS	Dimension Claim Status
239	STG_CLAIM_DETAILS	Stage Claim Details	DIM_CLAIM	Claim Dimension
241	STG_CATASTROPHE_EVENTS_MASTER	Stage Catastrophe Events Master	DIM_CATASTROPHE_EVENTS	Catastrophe Events Dimension
605	STG_CLAIM_FEATURE_STTS_MASTER	Stage Claim Feature Status Master	DIM_CLAIM_FEATURE_STATUS	Claim Feature Status Dimension
784	STG_PREM_PLAN_TYPE_MASTER	Stage Premium Plan Type Master	DIM_PREM_PLAN_TYPE	Premium Plan Type Dimension
792	STG_CLAIM_OFFER_MASTER	Stage Claim Offer Master	DIM_CLAIM_OFFER	Claim Offer Dimension
793	STG_CLAIM_RISK_CATEGORY_MASTER	Stage Claim Risk Category Master	DIM_CLAIM_RISK_CATEGORY	Claim Risk Category Dimension
794	STG_CLAIM_STATUS_REASON_MASTER	Stage Claim Status Reason Master	DIM_CLAIM_STATUS_REASON	Claim Status Reason Dimension
795	STG_CLAIM_TYPE_MASTER	Stage Claim Type Master	DIM_CLAIM_TYPE	Claim Type Dimension
812	STG_INT_CLSFCTN_DISEASE_MASTER	Stage International Statistical Classification Of Diseases Master	DIM_INT_CLASSIFICTION_DISEASES	International Statistical Classification Of Diseases Dimension

About Insurance Claims Investigation Dimension Tables

The Insurance Claims Investigation Dimension table names and their description are as follows.

Table 285: The Insurance Claims Investigation Dimension table names and their description
Logical Dimension Table Name	Dimension Table Description
Claim Investigation Dimension	Stores the Claim Investigation details. The claims investigation is a process in which the insurance companies, insurance examiners, or investigators obtain information to evaluate an insurance claim. The investigators look for evidence whether a claim is legitimate or illegitimate.
Investigation Status Reason Dimension	Stores details of the Investigation Status Reason. Sample values: IDF, NAIC1, NAIC2, PKEY1, PKEY2, POL1, SYSU, UREQ, and so on.
Claim Investigation Reason Dimension	Stores details of the claim investigation reason. This table is used to identify the aspect of the claim that leads the submitter to doubt the validity of a claim and refer the claim for further investigation. The source of this code list is the National Insurance Crime Bureau (NICB). Sample values: FL and SFL.
Claim Review Reason Dimension	Stores details of the reason for which the claim review took place. These reasons are appended at the end of the review. Sample values: OTHER, UNKNOWN, INITAL, SCHED, and UNSCHED.
Claim Submission Method Dimension	Stores details of the means through which the claimant informs the Insurance Company of any loss. Sample values: Internet, Call, Mail, and so on.
Claim Reopening Reason Type Dimension	Stores the reopening reason type. Sample values: R1 - Mathematical or computational mistake, R2 - Inaccurate data entry, D0 - Changes in-service date, E0 - Change in patient status, and so on.
Claim Handling Type Dimension	Stores the claim handling type details. This table is used to specify the situations, where claim handling can differ based on the claim amount, underwriting needs or other extenuating circumstances, or at the request of the claimant. Sample values: OTHER, UNKNOWN, ACCELERATED, COMPLEX, EXPRESS, and STANDARD.

The mapping details for the Insurance Claims Investigation Dimension tables are as follows.

Table 286: The mapping details for the Insurance Claims Investigation Dimension tables
Map Reference Number	Source Table Name	Logical Stage Table Name	Dimension Table Name	Logical Dimension Table Name
788	STG_CLAIM_INVESTIGATION_DTLS	Stage Claim Investigation Details	DIM_CLAIM_INVESTIGATION	Claim Investigation Dimension
805	STG_INVSTGN_STAT_REASON_MASTER	Stage Investigation Status Reason Master	DIM_INVSTGN_STAT_REASON	Investigation Status Reason Dimension
797	STG_CLM_INVSTGN_REASON_MASTER	Stage Claim Investigation Reason Master	DIM_CLM_INVSTGN_REASON	Claim Investigation Reason Dimension
799	STG_CLM_REVIEW_REASON_MASTER	Stage Claim Review Reason Master	DIM_CLM_REVIEW_REASON	Claim Review Reason Dimension
800	STG_CLM_SUBMSN_METHOD_MASTER	Stage Claim Submission Method Master	DIM_CLM_SUBMSN_METHOD	Claim Submission Method Dimension
648	STG_CLM_REOPEN_RSN_TYPE_MASTER	Stage Claim Reopening Reason Type Master	DIM_CLM_REOPEN_RSN_TYPE	Claim Reopening Reason Type Dimension
787	STG_CLAIM_HANDLING_TYPE_MASTER	Stage Claim Handling Type Master	DIM_CLAIM_HANDLING_TYPE	Claim Handling Type Dimension

About Insurance Claims Estimation Dimension Tables

The Insurance Claims Estimation Dimension table names and their description are as follows.

Table 287: The Insurance Claims Estimation Dimension table names and their description
Logical Dimension Table Name	Dimension Table Description
Claim Loss Type Dimension	Stores the codes that identify the type of loss that occurred. This table uses the Loss Kind code list. Sample values: Fire, Flood, Hail, Lightning, Other, Theft, and WNDST.
Human Body Parts Dimension	Stores details of the injured human body parts. Sample values: Eye, Nose, Legs, Hands, and so on.
Injury Nature Type Dimension	Stores details of the injury nature type. Sample values: NoPhysicalInjury, Amputation, AnginaPectoris, Inflammation, PoisoningGeneral, and so on.
Liability Loss Control Event Dimension	Stores details of the code that signifies the Loss Control Event category. Here, the event is the occurrence categorization that initiates the claim and uses the Liability Loss Control Event code list. Sample values: 100-PremisesHousekeeping, 200-OperationsFailureOfEquipment, 300-DefectiveProductOrWork, 410-Libel, and so on.
Claim Basis Dimension	Stores the code that signifies the basis for the claim, where the claim relates to the Coverage Kind of Loss payment. This table uses the Liability Claim Basis code list. Sample values: Contractual Liability, Negligence, Nuisance, Strict Liability, Vicarious Liability, and Voluntary Payments.
Claim Estimate Type Dimension	Stores details of the type of claim estimate that is being provided. Sample values: OTHER, UNKNOWN, INITIALESTCOST, INITIALESTDURATION, REVISEDESTCOST, and REVISEDESTDURATION.

The mapping details for the Insurance Claims Estimation Dimension tables are as follows.

Table 288: The mapping details for the Insurance Claims Estimation Dimension tables
Map Reference Number	Source Table Name	Logical Stage Table Name	Dimension Table Name	Logical Dimension Table Name
789	STG_CLAIM_LOSS_TYPE_MASTER	Stage Claim Loss Type Master	DIM_CLAIM_LOSS_TYPE	Claim Loss Type Dimension
803	STG_HUMAN_BODY_PARTS_MASTER	Stage Human Body Parts Master	DIM_HUMAN_BODY_PARTS	Human Body Parts Dimension
804	STG_INJURY_NATURE_TYPE_MASTER	Stage Injury Nature Type Master	DIM_INJURY_NATURE_TYPE	Injury Nature Type Dimension
806	STG_LIAB_LOSS_CTRL_MASTER	Stage Liability Loss Control Event Master	DIM_LIAB_LOSS_CTRL	Liability Loss Control Event Dimension
813	STG_CLAIM_BASIS_MASTER	Stage Claim Basis Master	DIM_CLAIM_BASIS	Claim Basis Dimension
814	STG_CLAIM_ESTIMATE_TYPE_MASTER	Stage Claim Estimate Type Master	DIM_CLAIM_ESTIMATE_TYPE	Claim Estimate Type Dimension

About Insurance Claims Occurrence Dimension Tables

The Insurance Claims Occurrence Dimension table names and their description are as follows.

Table 289: The Insurance Claims Occurrence Dimension table names and their description
Logical Dimension Table Name	Dimension Table Description
Claim Outside Authority Condition Dimension	Stores the codes that define the outside authority conditions applicable to the claim. This table uses the Outside Authority Condition code list. Sample values: DC, DU, EG, LI, SU, and WP.
Claim Occurrence Dimension	Stores the Claim Occurrence. An Occurrence policy covers claims made for injuries sustained during the life of an insurance policy, even if the claim is filed after the policy is canceled or lapsed. The policy must be active when the incident occurred. An Occurrence is an event that can result in the filing of an insurance claim.
Claim Cause Of Loss Dimension	Stores details of the cause of the loss or the peril triggering the loss for a claim. Sample values: fire, flood, sprinkler leakage, and so on.
Claim Occurrence Type Dimension	Stores details of the claim occurrence type. Sample values: accident, incident, loss, occurrence, injury, and illness.

The mapping details for the Insurance Claims Occurrence Dimension tables are as follows.

Table 290: The mapping details for the Insurance Claims Occurrence Dimension tables
Map Reference Number	Source Table Name	Logical Stage Table Name	Dimension Table Name	Logical Dimension Table Name
790	STG_CLAIM_OA_CONDITION_MASTER	Stage Claim Outside Authority Condition Master	DIM_CLAIM_OA_CONDITION	Claim Outside Authority Condition Dimension
791	STG_INS_OCCURRENCE_DETAILS	Stage Insurance Occurrence Details	DIM_CLAIM_OCCURRENCE	Claim Occurrence Dimension
796	STG_CLM_CAUSE_OF_LOSS_MASTER	Stage Claim Cause Of Loss Master	DIM_CLM_CAUSE_OF_LOSS	Claim Cause Of Loss Dimension
798	STG_CLM_OCCR_TYPE_MASTER	Stage Claim Occurrence Type Master	DIM_CLM_OCCR_TYPE	Claim Occurrence Type Dimension

About Insurance Claims T2Ts (Result Tables)

This section provides mapping details and descriptions of the Claims T2Ts Result tables.

NOTE:

The Claim Identifier column is added to the FCT_PARTY_MEDICAL_CONDITN_DTLS, FCT_PARTY_MEDICAL_TREATMENTS, and FCT_PARTY_DISABILITY_DETAILS tables to link the Party Medical attributes to the Insurance Claims.

About Insurance Claim Details T2T (Result Table)

The Insurance Claim Details T2T and its description are as follows.

Table 291: The Insurance Claim Details T2T and its description
T2T Name	T2T Description
T2T_FCT_CLAIM_DETAILS	Stores all the attributes of a policy claim paid until date along with the summary of transactions.

The mapping details for the Insurance Claim Details T2T is as follows.

Table 292: The mapping details for the Insurance Claim Details T2T
Source Table Name	Logical Stage Table Name	Fact Table Name	Logical Fact Table Name	T2T Name
STG_CLAIM_DETAILS	Stage Claim Details	FCT_CLAIM_DETAILS	Fact Claim Details	T2T_FCT_CLAIM_DETAILS

About Insurance Claim Party Map T2T (Result Table)

The Insurance Claim Party Map T2T and its description are as follows.

Table 293: The Insurance Claim Party Map T2T and its description
T2T Name	T2T Description
T2T_FCT_CLAIM_PARTY_MAP	Stores the mapping information between the Claim, Party, and Insurance.

The mapping details for the Insurance Claim Party Map T2T is as follows.

Table 294: The mapping details for the Insurance Claim Party Map T2T
Source Table Name	Logical Stage Table Name	Fact Table Name	Logical Fact Table Name	T2T Name
STG_CLAIM_PARTY_MAP	Stage Claim Party Map	FCT_CLAIM_PARTY_MAP	Fact Claim Party Map	T2T_FCT_CLAIM_PARTY_MAP

About Insurance Claims Occurrence T2T (Result Table)

The Insurance Claims Occurrence T2T and its description are as follows.

Table 295: The Insurance Claims Occurrence T2T and its description
T2T Name	T2T Description
T2T_FCT_INS_OCCURRENCE_DETAILS	Stores the insurance occurrence details. An Occurrence policy covers claims made for injuries sustained during the life of an insurance policy, even if the claim is filed after the policy is canceled. An occurrence is an event that can result in the filing of an insurance claim.

The mapping details for the Insurance Claims Occurrence T2T is as follows.

Table 296: The mapping details for the Insurance Claims Occurrence T2T
Source Table Name	Logical Stage Table Name	Fact Table Name	Logical Fact Table Name	T2T Name
STG_INS_OCCURRENCE_DETAILS	Stage Insurance Occurrence Details	FCT_INS_OCCURRENCE_DETAILS	Fact Insurance Occurrence Details	T2T_FCT_INS_OCCURRENCE_DETAILS

About Insurance Claims Investigation T2T (Result Table)

The Insurance Claims Investigation T2T and its description are as follows.

Table 297: The Insurance Claims Investigation T2T and its description
T2T Name	T2T Description
T2T_FCT_CLAIM_INVESTIGATION_DTLS	Stores the Claim Investigation details. The claims investigation is a process in which the insurance companies, insurance examiners, or investigators obtain information to evaluate an insurance claim. The investigators look for evidence whether a claim is legitimate or illegitimate.

The mapping details for the Insurance Claims Investigation T2T is as follows.

Table 298: The mapping details for the Insurance Claims Investigation T2T
Source Table Name	Logical Stage Table Name	Fact Table Name	Logical Fact Table Name	T2T Name
STG_CLAIM_INVESTIGATION_DTLS	Stage Claim Investigation Details	FCT_CLAIM_INVESTIGATION_DTLS	Fact Claim Investigation Details	T2T_FCT_CLAIM_INVESTIGATION_DTLS

About Insurance Claims Estimation T2T (Result Table)

The Insurance Claims Estimation T2T and its description are as follows.

Table 299: The Insurance Claims Estimation T2T and its description
T2T Name	T2T Description
T2T_FCT_CLAIM_BREAK_UP_DETAILS	Stores reserve details of the claim break up. A claims reserve is the money set aside by the insurance companies to pay the policyholders, who have filed or are expected to file legitimate claims on their policies. Insurers use the fund to pay out incurred claims that are yet to be settled.

The mapping details for the Insurance Claims Estimation T2T is as follows.

Table 300: The mapping details for the Insurance Claims Estimation T2T
Source Table Name	Logical Stage Table Name	Fact Table Name	Logical Fact Table Name	T2T Name
STG_CLAIM_BREAK_UP_DETAILS	Stage Claim Break Up Reserve Details	FCT_CLAIM_BREAK_UP_DETAILS	Fact Claim Break Up Reserve Details	T2T_FCT_CLAIM_BREAK_UP_DETAILS

About Insurance Claim Transactions T2T (Result Table)

The Insurance Claim Transactions T2T and its description are as follows.

Table 301: The Insurance Claim Transactions T2T and its description
T2T Name	T2T Description
T2T_FCT_CLAIM_TRANSACTIONS	Stores all the transactions related to claims reported with the entity.

The mapping details for the Insurance Claim Transactions T2T are as follows.

Table 302: The mapping details for the Insurance Claim Transactions T2T
Source Table Name	Logical Stage Table Name	Fact Table Name	Logical Fact Table Name	T2T Name
STG_CLAIM_TXNS	Stage Claim Transactions	FCT_CLAIM_TRANSACTION	Fact Claim Transaction	T2T_FCT_CLAIM_TRANSACTIONS

Deploying Insurance Claims Tables on Hive

All RDBMS related Result tables can also be deployed on Hive (Stage and Results). Deploy the Hive T2Ts using the Rules Run Framework. For more information, see the Rules Run Framework section in the Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.1.0.0.

NOTE:

In general, Stage and Result tables are also supported in Hive. However, there are some exceptions. For a list of tables that are not supported in Hive, see List of Unsupported T2Ts

Populating Insurance Claims Dimension Tables

Follow this SCD process to populate data into a Dimension table:

NOTE:

You can also follow this SCD process to populate data into any Hive-related Dimension table.

1. To populate data into a Dimension table, execute the SCD batch. For a detailed procedure, see the Slowly Changing Dimension (SCD) Process.

2. To check the SCD batch execution status of a Dimension table, follow the procedure Check the Execution Status of the SCD Batch.

3. To verify log files, and check the error messages (if any), follow the procedure Verify Log Files and Check Error Messages.

Populating Insurance Claims T2Ts (Result Tables)

Follow this T2T process to populate data into any T2T Result table:

NOTE:

Only RDBMS T2Ts can be executed using the PMF.

1. To populate data into any T2T Result table, execute the PMF process for that T2T. For a detailed procedure, see the following sections:

a. Prerequisites for loading T2T.

b. Select the Run Parameters and Execute the Run.

2. To check the T2T execution status and verify the log files of any Result table, follow the procedure in the Verify the Run Execution section.

3. To check the error messages, if any, follow the procedure in the Check Error Messages section.