| Atomic Warehouse Model Data Model |
| Description | An agreement defining the way in which an intermediary can act on behalf of a healthcare insurer. For example: Broker agreement between Broker ABC and Acme Insurance Company. |
| Relationship | |
 Intermediary Agreement Detail_Intermediary Agreement_FK |
|
| Primary Key | |
 Intermediary Agreement Detail PK |
|
| Dependencies | |
 |
|
| |
|
| Reverse Dependencies | |
 |
|
| Attribute Details |
Agreement Descr
| Description | Textual description of the Agreement. |
| 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 |
Agreement End Dt
| Description | The date upon which the Intermediary Agreement 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 Intermediary Agreement 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 |
Agreement Start Dt
| Description | The date upon which the Intermediary Agreement first starts. For renewable agreements, this date represents the date of the original Intermediary Agreement 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 |
Agreement Status Code Sk
| Description | Indicates the Intermediary Agreement'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 |
Agreement Status Reason Code Sk
| Description | Indicates the motivation or cause for changing the Agreement Status of the intermediary agreement. 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 |
Agreement Type Code Sk
| Description | Identifies the type of the agreement. |
| 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 |
Bound Dt
| Description | The date upon which the intermediary agreement is considered legally binding, or in force. Depending on the legislation the bound date can be the date when the intermediary agreement 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 |
Commission Earned
| Description | Sum of commission earned under the terms of the intermediary agreement. |
| 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 |
Commission Rate Sacrificed
| Description | Rate representing the portion of commission given up by the sales entity to enhance the terms for their client. |
| 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 |
Commission Withheld
| Description | Sum of that part of the commissions which are not paid out to the sales entity, but kept as compensation for contracts which are prematurely terminated. |
| 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 |
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 |
Intermediary Agreement Sk
| Description | The generated surrogate key of the Intermediary Agreement 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 |
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 |
Next Renewal Dt
| Description | The next date at which the enforce status of the agreement 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 intermediary agreement 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 Payments Made
| Description | The number of commission payments made. |
| 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 intermediary agreement 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 |
Pre-issue Commission Ind
| Description | Indicates whether (1) or not (0) the pre-issue of commission applies to the agreement. Under this process, commission is paid to the intermediary's or sales rep's current account because he issued a new application for insurance. The pre-issue commission will be reversed when the commission for the new business comes on the books. When the new policy does not come on the books, the pre-issue commission will be recovered. This usually happens at a set point in time. The reason for allocating pre-issue commission is that it may take several weeks before the underwriting process (especially medical underwriting) can be completed. Without pre-issue commission, the intermediary would have to wait all this time before they receive the commission. Pre-issue commission is optional, as the health plan may or may not want to do this process. For example: 0 = Pre-Issue Commission Does Not Apply 1 = Pre-Issue Commission Applies |
| 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 |
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 |
Intermediary Agreement Detail_Intermediary Agreement_FK
| Is Identifying Relationship | true |
| Child Table | Intermediary Agreement 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 | Intermediary Agreement |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
| Primary Key Details |
Intermediary Agreement Detail PK
| Key Attribute | Intermediary Agreement Sk |
| Key Attribute | Valid From Ts |
| Atomic Warehouse Model Data Model |