Skip to end of metadata
Go to start of metadata
You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 8
Next »
Auto Territory Coverage
Section I. Tables
Policy
| Field | Table | Attribute | Definition |
|---|
| policy id | policy | id | pk for policy |
| policy number | policy | number | policy number from source system |
| policy | f_line_of_business_id | fk for line_of_business |
| policy | f_subline_code_id | fk for subline |
Line of Business
| Field | Table | Attribute | Definition |
|---|
| line of business id | line_of_business | id | pk for line_of_business |
| line of business code | line_of_business | code | short hand for LOB |
| line of business name | line_of_business | name | human readable line of business |
Transaction
| Field | Table | Attribute | Definition |
|---|
| transaction id | transaction | id | PK for transaction |
| deductible amount | transaction | deductible_amount | Deductible amount that applies to the Physical Damage coverages |
| accounting year | transaction | accounting_year | Reflects reporting year |
| exposure_amount | transaction | exposure_amount | The actual number of vehicles covered by the premium posted in the statistical record. Usually this will be one, although it is possible for the same coverage to be provided in the same policy on more than one vehicle, in which case the Exposure entry would be the number of vehicles covered under the coding entry. |
| transaction type | transaction | type |
|
| deductible_amount | transaction | deductible_amount | Deductible amount that applies to the Physical Damage coverages |
| zipcode | transaction | zipcode | policy zipcode |
| transaction | f_transactional_code_id | FK for tbl: transaction_code |
| transaction | f_state_id | FK for tbl: state |
|---|
| transaction | f_coverage_code_id | FK for tbl: coverage_code |
| transaction | f_policy_id | FK for tbl: policy |
Claim Detail
| Field | Table | Attribute | Definition |
|---|
| clam detail id | claim_detail | id | PK for claim details |
| claim count | claim_detail | claim_count | Actual number of new claims reported in a given reporting period. The rules for negative counts are the same as those for the Exposure field. The claim count on a paid loss record should be 1 only when the first payment is made provided no reserve had been established in a previous quarter |
| accident date year | claim_detail | accident_date_year | Date on which the accident occurred (except for grouped claims) |
| loss amount | claim_detail | loss_amount | Outstanding loss and outstanding allocated loss adjustment expense records should always have the amount reserved for the claim as of the end of the quarter. This amount must be reported each quarter it remains outstanding. If a partial payment is made on an outstanding loss, the new reserve amount must be reported if the claim is not closed by the end of the quarter. |
| claim_detail | f_transaction_id | FK to relate to parent transaction |
Premium Detail
| Field | Table | Attribute | Definition |
|---|
| premium detail id | premium_detail | id | PK for premium details |
| premium amount | premium_detail | amount | Amount paid |
| premium_detail | f_transaction_id | FK to parent transaction |
Coverage Code
| Field | Table | Attribute | Definition |
|---|
| coverage id | coverage_code | id | PK for coverage table |
| coverage code | coverage_code | code | Code that identifies the exposure coverage associated with the manual class code. |
| coverage name | coverage_code | name | Human readable name of coverage |
| coverage note | coverage_code | note | Notes on coverage |
State
| Field | Table | Attribute | Definition |
|---|
| state id | state | id | PK for state |
| state name | state | name | Human readable name |
| state code | state | code | State Abbreviation |
Transaction Code
| Field | Table | Attribute | Definition |
|---|
| transaction id | transaction_code | id | PK for transaction codes |
| transaction code | transaction_code | code | Identifies a loss transaction on limited coded records. The field will be populated on loss-related Transaction Codes if the record is not fully coded. |
| transaction name | transaction_code | name | Human readable name for transaction code |
Subline
| Field | Table | Attribute | Definition |
|---|
| subline id | subline | id | PK for subline |
| subline name | subline | name | Human readable name for subline |
| subline code | subline | code | Short hand for subline code |
Company
| Field | Table | Attribute | Definition |
|---|
| company id | company | id | PK for company |
| company name | company | name | name of company |
| company description | company | description | company description |
type_of_business
| Field | Table | Attribute | Definition |
|---|
| type_of_business id | type_of_business | id | PK for type_of_business |
| type_of_business name | type_of_business | name | name of type_of_business |
| type_of_business description | type_of_business | description | type_of_business description |
class_group
| Field | Table | Attribute | Definition |
|---|
| class_group id | class_group | id | PK for class_group |
| class_group name | class_group | name | name of class_group |
| class_group description | class_group | description | class_group description |
| class_group type | class_group | class_group_type | voluntary risk, assigned risk, miscellaneous personal vehicles |
class_group_type
| Field | Table | Attribute | Definition |
|---|
| class_group_type id | class_group_type | id | PK for class_group_type |
| class_group_type name | class_group_type | name | name of class_group_type |
| class_group_type description | class_group_type | description | class_group description |
Section II: Data
class_group_type
| id | name | description |
|---|
| 1 | Voluntary Risk |
|
| 2 | Assigned Risk | Utilize state assigned risk classification plans. |
| 3 | Miscellaneous Personal Vehicles | includes motorcycles |
class_group
| id | name | description | type |
|---|
| 1 | 1 | No Male driver under 25; non-business use | 1 |
| 2 | 1F | No male driver under 25; Farm use | 1 |
| 3 | 1A | No male driver under 25; non-business use, non farm, no driving to work | 1 |
| 4 | 1B | No male driver under 25; non-business use; drives to work - limited number of miles each day | 1 |
| 5 | 1C | No male driver under 25; non-business use; drives to work - over a limit of miles each way | 1 |
| 6 | 1D | No male driver under 25; non-business use, drives to work - no distance restriction | 1 |
| 7 | 3 | No male driver under 25; business use | 1 |
| 8 | 2 | Driver under 25; business and non business use | 1 |
| 9 | 2A | Male driver under 25; business and non business use; married or single male not owner or principal driver | 1 |
| 10 | 2B | Married male driver under 25; business and non-business use | 1 |
| 11 | 2C | Unmarried male driver under 25; business and non-business; owner or principal driver | 1 |
| 12 | 2D | Female driver under 25 | 1 |
| 13 | 2E | Single male operator age 25-29 inclusive; owner or principal operator | 1 |
| 14 | Sr. Citizen | Operator age 65 and over | 1 |
| 15 | All Other PPNF | No class plan or other class plan used; military post coverage, all other private passener cars | 1 |
Model Acronyms/Definitions
| id | Acronym | Explanation |
|---|
| 1 | PK | primary key, the unique identifier for a row |
| 2 | FK | foreign key, a reference to a unique row that is defined in another table. |