Atomic Warehouse Model Data Model

Entity

Entity Insurance Policy Detail

Description

An agreement (individual agreement or group agreement) between a healthcare insurer and a member based on a health plan product. The agreement specifies the obligation of the healthcare insurer to pay benefits and of the premium payer to pay premiums.

Attributes
Additional Fee Amt
Agreement Status Code Sk
Annual Deductible Amt
Annual Deductible Carryover Amt
Annual Deductible Out-of-Network Amt
Annual Out-of-Pocket Maximum Amt
Annual Out-of-Pocket Maximum Out-of-Network Amt
Bound Dt
Contract Year To Date Benefit Amt
Contract Year To Date Premium Amt
Co-Payment Amt
Co-Payment Out-of-Network Amt
Coverage Out-of-Network Pct
Coverage Pct
Discount Amt
Discount Category Code Sk
Effective From Dt
Effective To Dt
Entry Code Sk
Gross Premium Amt
Insurance Policy Descr
Insurance Policy End Dt
Insurance Policy Sk
Insurance Policy Start Dt
Insurance Policy Status Reason Code Sk
Line Of Business Code Sk
Load Info Sk
Maximum Benefit Amt
Membership Card Ind
Membership Eligibility Code Sk
Net Premium Amt
Next Renewal Dt
Next Review Dt
Number of Beneficiaries
Planned End Dt
Policy Type Code Sk
Premium Payment Frequency Code Sk
Premium Payment Method Code Sk
Rate Class Code Sk
Renewal Ind
Risk Type Code Sk
Selected Currency Code Sk
Source Code Sk
Tenant Sk
Valid From Ts
Valid To Ts

Relationship
Insurance Policy Detail_Insurance Policy_FK

Primary Key
Insurance Policy Detail PK

Dependencies

Reverse Dependencies

Attribute Details

Additional Fee Amt

Description	Identifies additional fees that are related to the policy, above those normally incorporated in the premium amount.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Agreement Status Code Sk

Description	Indicates the Insurance Policy's current status. For Example: Active Cancelled Expired Final In Force In Process Initial Lapsed Not taken up Offered Rejected Suspended Terminated Under Negotiation
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Deductible Amt

Description	The self-insured risk portion or amount of money that the policyholder is required to pay in a contract year before insurance policy benefits kick in. Applies to medical services or products received by an in-network provider where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Deductible Carryover Amt

Description	The deductible amount carried over from another insurance policy. For example where a policy switch occurs. Applies to benefits in-network where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Deductible Out-of-Network Amt

Description	The self-insured risk portion or amount of money that the policyholder is required to pay in a contract year before insurance policy benefits kick in. Applies to medical services or products received by an out-of-network provider.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Out-of-Pocket Maximum Amt

Description	The maximum amount payable as deductibles and co-payments in a contract year, under this insurance policy. Applies to medical services and products received by an in-network provider, where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Out-of-Pocket Maximum Out-of-Network Amt

Description	The maximum amount payable as deductibles and co-payments in a contract year, under this insurance policy. Applies to medical services and products received by an out-of-network provider.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Bound Dt

Description	The date upon which the Insurance Policy is considered legally binding, or in force. Depending on the legislation the bound date can be the date when the Insurance Policy is signed by both parties and/or when the first premium has been paid. Derived from the status related information.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Contract Year To Date Benefit Amt

Description	The total benefits paid year-to-date for this insurance policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Contract Year To Date Premium Amt

Description	The total premium paid year-to-date for this insurance policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Co-Payment Amt

Description	The co-payment amount that applies to all (in-network where applicable) covered medical services and products received under this insurance policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Co-Payment Out-of-Network Amt

Description	The co-payment amount that applies covered medical services and products received from an out-of-network provider, under this insurance policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Out-of-Network Pct

Description	Percent of charges payable under the contract, for covered medical services or products delivered by and out-of-network provider, under this insurance policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Pct

Description	Percent of charges payable under the contract, for in-network benefits where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Discount Amt

Description	Identifies the discount amount applicable to the policy premium.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Discount Category Code Sk

Description	Indicates a discount category that determines the reductions in premium or price from the standard rating structure based on the presence of factors that reduce the risk or increase profitability. For example: No claim No Discount Percentage of Usual and Customary Fee
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Effective From Dt

Description	Establishes a period where a set of attributes are true according to the business.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Effective To Dt

Description	Ends a period of effectivity.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Entry Code Sk

Description	Identifies whether the insurance policy was entered by an external party or by a party within the modeled organization. For example: Agency entered Branch entered
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Gross Premium Amt

Description	The premium amount for the policy for its effective period.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Insurance Policy Descr

Description	Textual description of the Insurance Policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Insurance Policy End Dt

Description	The date upon which the Insurance Policy actually terminates. This may pre- or post-date the Planned End Date. For example, early termination of a contract due to poor performance. The termination date of the Insurance Policy is also represented in the status date for the terminated status.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Insurance Policy Sk

Description	The generated surrogate key of the Insurance Policy anchor.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Insurance Policy Start Dt

Description	The date on which the Insurance Policy first starts. For renewable Insurance Policy, this date represents the date of the original Insurance Policy before any renewal. Derived from the status related information.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Insurance Policy Status Reason Code Sk

Description	Indicates the motivation or cause for changing the agreement status of the Insurance Policy. For example: Not eligible (if agreement rejected) Not taken up (if agreement rejected)
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Line Of Business Code Sk

Description	Indicates the nature of commercial enterprise of the modeled organization.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Load Info Sk

Description	The surrogate key of the load information entry describing the details regarding the loading of the row.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Maximum Benefit Amt

Description	The maximum amount that can be paid out in benefits under this insurance policy. The Limit Applicability Type (supported by Stipulation Applicability Type) specifies whether the maximum amount is applicable to a life-time or annual limit.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Membership Card Ind

Description	Indicates whether membership cards are to be issued to members under the policy. For example: Not required. To be issued.
Data Type	Standards - Data Domains.ddm/Data Domains/Alphanumeric Small [VARCHAR(6)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Membership Eligibility Code Sk

Description	Indicates the qualification conditions for membership to an insurance policy. For example: Full Time Employees Full Time Employees Only Subscriber and Dependents Subscriber and Spouse Subscriber Only
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Net Premium Amt

Description	Net premium for the policy for its effective period, when certain deductions are applied, for example, net of re-insurance amounts.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Next Renewal Dt

Description	The next date at which the enforce status of the Insurance Policy must be re-evaluated or re-conducted.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Next Review Dt

Description	The date upon which a review of the Insurance Policy is planned.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Number of Beneficiaries

Description	The number of persons receiving benefits under the insurance policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Small Counter [SHORT]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned End Dt

Description	The date upon which the end of the Insurance Policy is planned.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Policy Type Code Sk

Description	Identifies the type of the Insurance Policy.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Premium Payment Frequency Code Sk

Description	Indicates the frequency with which the payment of the premium will occur. For example: Annually
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Premium Payment Method Code Sk

Description	Indicates the payment method that will be used to pay the premiums for the policy. For example: Check Direct debit mandate Electronic funds transfer
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Rate Class Code Sk

Description	Indicates the category that determines premium rates or rating structures for the insurance policy. For example: Single Male Principal Operator Age 20-25
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Renewal Ind

Description	Indicates whether or not the insurance policy is a renewal. If not, it indicates that the insurance policy is in its first renewal period.
Data Type	Standards - Data Domains.ddm/Data Domains/Boolean Indicator [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Risk Type Code Sk

Description	Indicates the types of risk or financial exposure to the health plan.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Selected Currency Code Sk

Description	Indicates the currency as selected by the customer to be used on any monetary information provided to the customer.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Source Code Sk

Description	The origin of the data identifying the actual load source, vendor, manual key entry, or context of the data in a specific row in the database.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Tenant Sk

Description	The surrogate key of the entry identifying the legal owner of the data.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid From Ts

Description	Establishes a period where a set of attributes are true in the source system. This would be populated with the transaction timestamp and would be used for the snapshot date.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid To Ts

Description	Ends a period of validity.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Relationship Details

Insurance Policy Detail_Insurance Policy_FK

Is Identifying Relationship	true
Child Table	Insurance Policy Detail
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Insurance Policy
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Insurance Policy Detail PK

Key Attribute	Insurance Policy Sk
Key Attribute	Valid From Ts

Overview

Top

Package

Entity

Atomic Warehouse Model Data Model