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)
· Deploying Insurance Policy Transactions Tables on Hive
· Populating Insurance Policy Transactions Dimension Tables
· Populating Insurance Policy Transactions T2T Result Tables
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.
Insurance Policy Transaction Dimension table names and their description are given here.
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.
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 |
Insurance Policy Transactions T2Ts and their description are given here.
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.
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 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.
Policy Commission Dimension table names and their description are given here.
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.
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 |
Policy Commission T2Ts and their description are given here.
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.
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 |
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
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.
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.
You can see the following topics related to other function-specific tables: