29 Transactions and Accounting Tables

This section provides information about Insurance Policy Transactions tables in the Oracle Insurance Data Foundation application.

This consists of primarily two sections. Policy Transactions generated out policy life cycle accounting events and policy commission which are specific to producer or agents business and are generated out of underwriting and some period in the policy life cycle. However, they are not expected to overlap with policy transactions which exclude any producer or agent-specific transactions.

Topics:

·        About Insurance Policy Transactions Dimension Tables

·        About Insurance Policy Transactions T2Ts (Result Tables)

·        Policy Commission Tables

·        Deploying Insurance Policy Transactions Tables on Hive 

·        Populating Insurance Policy Transactions Dimension Tables

·        Populating Insurance Policy Transactions T2T Result Tables

·        Related Topics

Insurance Policy Transactions are about accounting transactions done for a given policy. They are sourced from respective accounting systems in the Insurance Company.

The purpose of Insurance Policy Transactions tables is to store attributes pertaining to policy transactions related data on an 'as-is' basis received from the source system for further operation reporting. OIDF holds for every product processor or contract table, one transaction table. 

About Insurance Policy Transactions Dimension Tables

Insurance Policy Transaction Dimension table names and their description are given here.

Table 270: Insurance Policy Transaction Dimension table names and their description

Logical Dimension Table Name

Dimension Table Description

Policy Transaction Type Dimension

This table stores the details of types of policy transactions.

Premium Offset Method Dimension

This table stores the details about the premium offset method. One option is called Premium Offset Plan or POP.

Sample values: AUTOPREMPYMT, LOAN, NETOFFSET, NONE, PARTIAL, and so on.

Premium Suspense Reason Dimension

This table stores the details of Premium Suspense Reason code, and documents the reason behind the premium suspense amount recorded in Premium Suspense Amount.

Sample values: BALDUE, LOANINT, MULTIPLE, PREM, and PREMREFUND.

 

The mapping details for the Insurance Policy Transaction Dimension tables are given here.

Table 271: The mapping details for the Insurance Policy Transaction Dimension tables

Map Reference Number

Source Table Name

Logical Stage Table Name

Dimension Table Name

Logical Dimension Table Name

693

STG_ANNUITY_TXNS

Stage Annuity Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

694

STG_HEALTH_INS_POLICY_TXNS

Stage Health Insurance Policy Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

695

STG_LIFE_INS_POLICY_TXNS

Stage Life Insurance Policy Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

696

STG_PROP_CASU_POLICY_TXNS

Stage Property and Casualty Policy Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

697

STG_RETIREMENT_ACCOUNTS_TXNS

Stage Retirement Account Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

698

STG_REINSURANCE_ISSUED_TXNS

Stage Reinsurance Issued Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

699

STG_REINSURANCE_HELD_TXNS

Stage Reinsurance Held Transactions

DIM_POLICY_TXN_TYPE

Policy Transaction Type Dimension

729

STG_PREM_OFFSET_METHOD_MASTER

Stage Premium Offset Method Master

DIM_PREM_OFFSET_METHOD

Premium Offset Method Dimension

731

STG_PREM_SUSP_REASON_MASTER

Stage Premium Suspense Reason Master

DIM_PREM_SUSP_REASON

Premium Suspense Reason Dimension

 

About Insurance Policy Transactions T2Ts (Result Tables)

Insurance Policy Transactions T2Ts and their description are given here.

Table 272: Insurance Policy Transactions T2Ts and their description

T2T Name

T2T Description

T2T_FCT_POLICY_TRANSACTIONS_HLD

This T2T stores the reinsurance held transaction details.

T2T_FCT_POLICY_TRANSACTIONS_ISS

This T2T stores the reinsurance issued transaction details.

T2T_FPT_STG_RETIREMENT_ACCOUNTS_TXNS

This T2T stores retirement accounts transactions.

T2T_FPT_STG_PROP_CASU_POLICY_TXNS

This T2T stores the transaction details for property and casualty policy.

T2T_FPT_STG_LIFE_INS_POLICY

This T2T stores the transaction details for the life insurance policy.

T2T_FPT_STG_HEALTH_INS_TXNS

This T2T stores the transaction details for a health insurance policy.

T2T_FPT_STG_ANNUITY_TXNS

This T2T stores transactions details for annuity contracts.

 

The mapping details for the Insurance Policy Transactions T2Ts are given here.

Table 273: The mapping details for the Insurance Policy Transactions T2Ts

Source Table Name

Logical Stage Table Name

Fact Table Name

Logical Fact Table Name

T2T Name

STG_REINSURANCE_HELD_TXNS

Stage Reinsurance Held Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FCT_POLICY_TRANSACTIONS_HLD

STG_REINSURANCE_ISSUED_TXNS

Stage Reinsurance Issued Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FCT_POLICY_TRANSACTIONS_ISS

STG_RETIREMENT_ACCOUNTS_TXNS

Stage Retirement Accounts Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FPT_STG_RETIREMENT_ACCOUNTS_TXNS

STG_PROP_CASU_POLICY_TXNS

Stage Property Casualty Policy Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FPT_STG_PROP_CASU_POLICY_TXNS

STG_LIFE_INS_POLICY_TXNS

Stage Life Insurance Policy Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FPT_STG_LIFE_INS_POLICY

STG_HEALTH_INS_POLICY_TXNS

Stage Health Insurance Policy Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FPT_STG_HEALTH_INS_TXNS

STG_ANNUITY_TXNS

Stage Annuity Transactions

FCT_POLICY_TRANSACTIONS

Fact Policy Transactions

T2T_FPT_STG_ANNUITY_TXNS

 

Policy Commission Tables

Policy Commission refers to the compensation for the sale of policies to Producers or Agents. This compensation can be a percentage of the premium paid through Agents to Insurance Companies. Many insurance carriers base this commission on several criteria such as Coverage, the product, and the marketing methods. A standard commission is a specific dollar amount or percentage commission on the premium set at the time of or advance of the purchase, renewal, and placement or servicing of a particular insurance policy as applicable. Therefore, Commissions are payable to Producers or agents for bringing the business to the Insurance Company. Here, the Insurance policies cover all types of Insurance Contracts for which services are used.

There are two Policy Commission entities. They are Commission Details and Commission Transactions.

·        Stage Policy Commission Details refers to as of date picture of several commission events for a given policy, coverage and commission event type. To arrive at producer or agent level summary one needs to aggregate over given policy for all or specific commission event type. The Source of this information is expected to be an underwriting or producer management systems.

·        Stage Policy Commission Transactions holds the set of individual transactions incurred or paid for a given policy. The Source of this information is expected to be accounting systems.

About Policy Commission Dimension Tables

Policy Commission Dimension table names and their description are given here.

Table 274: Policy Commission Dimension table names and their description

Logical Dimension Table Name

Dimension Table Description

Commission Type Dimension

This table stores the general type or category of the commission details.

Sample values: ADV, COMM, DEBT, DEFCOMM, DEFOVER, ERA, ERAOVRD, and so on.

Commission Basis Type Dimension

This table stores the commission basis type and specifies what rate category is applied to this commission.

Sample values: ANNTYPAYOUTAMT, ASSVAL, ASSVALCHG, COMMPREM, COI, FLAT, FUNDPREM, and so on.

Commission Event Type Dimension

This table stores the commission event type, and the event that triggers generating a commission.

Sample values: ANNIVPOL, CHGSTATUS, APLPREM, SUBMITTED, and so on.

Commission Payment Status Dimension

This table stores the current status of a commission payment details.

Sample values: ACTIVE, CANCELLED, COMPLETE, NOTDONE, HISTORY, PENDING, SUSPENDED, VOID, OTHER, and UNKNOWN.

Commission Transaction Type Dimension

This table stores the Commission transaction type details.

Sample values: ADJ, BONUS, CHRGPAYBLE, CHRGEARNED, COMMEARNED, and so on.

Transaction Process Event Type Dimension

This table stores the type of processing this event represents.

Sample values: ORIG, OTHER, REAPPLY, REVERSAL, and UNKNOWN.

Insurance Producer Appointment Dimension

This table stores the Insurance Producer Appointment details.

Commission Option Type Dimension

This table stores the details of the commission option selected for the policy. The commission description, which correlates to the commission code or compensation plan, which the original writing agents choose to be compensated by when the policy was sold.

Sample values: OTHER, UNKNOWN, NOTRL, and so on.

 

The mapping details for the Policy Commission Dimension tables are given here.

Table 275: The mapping details for the Policy Commission Dimension tables

Map Reference Number

Source Table Name

Logical Stage Table Name

Dimension Table Name

Logical Dimension Table Name

706

STG_COMMISION_TYPE_MASTER

Stage Commission Type Master

DIM_COMMISION_TYPE

Commission Type Dimension

707

STG_COMMN_BASIS_TYPE_MASTER

Stage Commission Basis Type Master

DIM_COMMN_BASIS_TYPE

Commission Basis Type Dimension

708

STG_COMMN_EVNT_TYPE_MASTER

Stage Commission Event Type Master

DIM_COMMN_EVNT_TYPE

Commission Event Type Dimension

709

STG_COMMN_PYMNT_STATUS_MASTER

Stage Commission Payment Status Master

DIM_COMMN_PYMNT_STATUS

Commission Payment Status Dimension

710

STG_COMMN_TRANS_TYPE_MASTER

Stage Commission Transaction Type Master

DIM_COMMN_TRANS_TYPE

Commission Transaction Type Dimension

711

STG_TXN_PROC_EVNT_TYPE_MASTER

Stage Transaction Process Event Type Master

DIM_TXN_PROC_EVNT_TYPE

Transaction Process Event Type Dimension

712

STG_COMMN_OPTION_TYPE_MASTER

Stage Commission Option Type Master

DIM_COMMN_OPTION_TYPE

Commission Option Type Dimension

 

About Policy Commission T2Ts (Result Tables)

Policy Commission T2Ts and their description are given here.

Table 276: Policy Commission T2Ts and their description

T2T Name

T2T Description

T2T_FCT_POLICY_COMMISSION_DETLS

This T2T stores the policy commission details.

T2T_FCT_POLICY_COMMISSION_TXNS

This T2T stores the policy commission transaction details.

 

The mapping details for the Policy Commission T2Ts are given here.

Table 277: The mapping details for the Policy Commission T2Ts

Source Table Name

Logical Stage Table Name

Fact Table Name

Logical Fact Table Name

T2T Name

STG_POLICY_COMMISSION_DETAILS

Stage Policy Commission Details

FCT_POLICY_COMMISSION_DETAILS

Fact Policy Commission Details

T2T_FCT_POLICY_COMMISSION_DETLS

STG_POLICY_COMMISSION_TXNS

Stage Policy Commission Transactions

FCT_POLICY_COMMISSION_TXNS

Fact Policy Commission Transactions

T2T_FCT_POLICY_COMMISSION_TXNS

 

Deploying Insurance Policy Transactions 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 Policy Transactions 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 Policy Transactions T2T Result Tables

 

NOTE:   

As a prerequisite, ensure to load the DIM_STD_TRANSACTION_TYPE seeded table.

 

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.

Related Topics

You can see the following topics related to other function-specific tables:

·        Insurance Contracts Tables

·        Loan Account Summary Tables

·        Insurance Claims Tables