Base Views
The reporting views in this section are deprecated. They are replaced with the base views described in Base views. |
This section describes the working copy base views. The base views are intended for use by developers (for example, of interfaces) and possibly for access through business intelligence or other query tools. Use of the views requires knowledge of the OHI Claims entity model, dynamic field concepts as well as database concepts such as foreign keys. The base views are also used as a basis for the functional reporting views (which are described separately).
For the Claims Transaction Repository Base views please refer to the following document: Repository Base Views
The base views provide access to working copy data with dynamic fields presented as regular columns or tables and multilingual data resolved to the base language. Single value dynamic fields that are not time valid are represented as regular columns and other dynamic fields and dynamic records are presented as child tables. There is one base view for each:
-
functionally meaningful entity of the working copy data model,
-
dynamic field (except for 'free field' single value dynamic fields that are not time valid), and each
-
flex code definition.
The views are generated as part of system configuration and need to be regenerated when dynamic fields are added or changed or following releases of OHI Claims that have data model changes. View generation is documented separately.
There are separate claim transaction repository base views which are described separately. The repository base views are very similar to these working copy base views. The main differences are:
-
there are working copy base views for the entities that are referenced from working copy claim entities whereas the repository claim entities do not have referenced entities,
-
the working copy views resolve multi-lingual aspects of the working copy data model; this does not apply to the repository views as the repository is not multi-lingual, and
-
there are no functional views for the repository as the repository is already an 'external functional view' of a claim transaction.
The following sections describe each of the main types of views in detail.
Entity Base Views
The entity views correspond to the functional meaningful entities as described in the data model sections of the implementation guides. In many cases, a given 'entity' is implemented as three physical tables (one for attributes of the entity that are not multi-lingual, one for multi-lingual attributes, and one for dynamic field values). These sets of underlying tables are presented as a single entity base view and one or more dynamic field/record base views.
View Naming
Entity base view names are composed of 3 components as follows:
-
Internal Application Area Code
-
'_'+
-
Entity Name+
-
'_V'
The internal application area identifiers organize data model objects into the following categories:
Application Area Code | Application Area | Note |
---|---|---|
CLA |
Claims |
Main working copy claim entities. |
COD |
Dynamic Field Definitions |
Internal entities that implement the dynamic field feature. Probably not of interest to other systems. Include only for completeness. |
CTR |
Claims Transaction Repository |
Claim transaction repository entities. See separate CTR base view documentation for details. |
FIN |
Common Financial |
Entities related to financial transactions and their handling. |
OHI |
OHI Foundation |
Entities related to system configuration and reference data. |
PAS |
Products |
Entities related to products. |
RCL |
Claims Reference Data |
Entities related to claim specific reference data and configuration. |
REL |
Relations and Providers |
Entities related to relations and providers. |
The entity names correspond to the data model names (of the implementation guides) more or less directly.
View Columns
The view for each entity has:
-
a column holding the internal id of the entity,
-
a column holding the internal id of the immediate parent entity (except for 'root' entities)
-
a column for each fixed attribute of the entity,
-
a column for each foreign key of the entity,
-
a column for each single value dynamic field that is not time valid that has been configured for the entity, and
-
standard technical columns.
Each of these 'types' of columns is explained in more detail below:
Internal Id
The internal id column shows the arbitrary unique record id of the main underlying table record id.
Parent Internal Id
The parent internal id shows the internal id of a detail record’s parent record. The parent internal id is included for use in joining the view of an entity with its parent (e.g. to join the claim line view to the claim view).
Fixed Attribute Columns
The fixed attribute columns show a single language view of the columns that hold the fixed attributes of an entity. The fixed attributes are described in the implementation guide data model sections. These columns have the same names and data types as in the underlying table columns. The contents of columns are not transformed by the views.
Foreign Key Columns
The foreign key columns show the internal id of records that may be referenced from an entity. These columns are included to be able to join to the referenced entity base views (for example, from claim to the claimant relation).
Dynamic Field Columns
These columns hold the values of single value dynamic fields that are not time valid. There is one of these columns for each single value dynamic field that is not time valid (that has been defined for the entity of the view).
The columns for 'free field' dynamic fields hold the actual field values. For flex code dynamic fields, the columns hold the id (foreign key) of a value as follows:
-
for a standard flex code, the id is for a value that is accessible using the base dynamic field view for the flex code (FCOD_flex code name)
-
for a procedure flex code, the id is for a procedure that is accessible using the procedure base view (OHI_PROCEDURES_V)
-
for a diagnosis flex code, the id is for a diagnosis that is accessible using the diagnosis base view (OHI_DIAGNOSES_V)
-
for a provider flex code, the id is for a provider that is accessible using the provider base view (REL_PROVIDERS_V)
The names of these columns are based on field usage names. The name of a free field dynamic field column is the field usage name in upper case with each upper case letter in the field usage name preceded by an '_'. For example, a free field named 'mainOccupation' would become MAIN_OCCUPATION. For flex codes, the names have an '_ID' suffix. Since the suffix has a length of 3 characters, and a base view column name cannot be longer than 30 characters, dynamic field usages names for flex code fields should not be longer than 27 characters.
It is important for procedures to be in place to prevent changes to the 'field usage name' without considering the impact on 'saved' queries of the base views.
View Security
The base views must only be made available to fully 'trusted' users and applications. The base views do not restrict access to specific rows in any way. If a user has direct access to a view, they have access to all rows that can be accessed with the view. The access restriction labels are included in base views and could be used as a basis for enforcing any fine grained security that is needed in any applications or query tools that are using the views. All fine grained security would be completely implemented in these applications or tools (outside of OHI Claims) and would require a detailed understanding of the OHI Claims user access model and concepts.
View Examples
The following examples illustrate the views for the claim, claim line, and relation entities. For the purposes of this example, the dynamic fields for US Seed Data have been configured as well as a local 'National Id' single value dynamic field on relation.
CLA_CLAIMS_V
ID |
NUMBER |
Unique internal id for this claim. |
AUTHORIZATION_CODE |
VARCHAR2 |
|
CLAIM DATE |
DATE |
|
CLAIMANT_ADDRESS |
VARCHAR2 |
Non-matched |
CLAIMANT_DOB |
VARCHAR2 |
Non-matched |
CLAIMANT_FIELD NAME |
VARCHAR2 |
Non-matched |
CLAIMANT_FIELD VALUE |
VARCHAR2 |
Non-matched |
CLAIMANT_NAME |
VARCHAR2 |
Non-matched |
CLAIMANT_PROV_CODE |
VARCHAR2 |
Non-matched |
CLAIMANT_PROV_CODE_DEF |
VARCHAR2 |
Non-matched |
CLAIMANT_REL_CODE |
VARCHAR2 |
Non-matched |
CODE |
VARCHAR2 |
|
END_DATE |
DATE |
|
… all other fixed fields from Claim … |
||
CLAIMANT_REL_ID |
NUMBER |
|
… all other foreign keys from Claim … |
||
ACCIDENT_DATE |
DATE |
accidentDate |
ACCIDENT_STATE |
VARCHAR2 |
accidentState |
… all other US Seed single value dynamic fields … |
||
… locally defined single value dynamic fields … |
||
… technical columns … |
CLA_CLAIM_LINES_V
ID |
NUMBER |
Unique internal id for this claim. |
CLAIM_ID |
NUMBER |
Internal id of the immediate parent entity. |
ALLOWED_AMOUNT |
NUMBER |
|
AUTHORIZATION_CODE |
VARCHAR2 |
|
AUTHORIZATION_EXCEPTION_TYPE |
VARCHAR2 |
|
BENEFITS_INPUT_AMOUNT |
NUMBER |
|
BENEFITS_PROVIDER_ADDRESS |
VARCHAR2 |
Non-matched |
BENEFITS_PROVIDER_CODE |
VARCHAR2 |
Non-matched |
BENEFITS_PROVIDER_CODE_DEF |
VARCHAR2 |
Non-matched |
BENEFITS_PROVIDER_FIELD_NAME |
VARCHAR2 |
Non-matched |
BENEFITS_PROVIDER_FIELD_VALUE |
VARCHAR2 |
Non-matched |
BENEFITS_PROVIDER_NAME |
VARCHAR2 |
Non-matched |
CLAIMED_AMOUNT |
NUMBER |
|
… all other fixed fields from Claim Line … |
||
BENEFITS_PROVIDER_ID |
NUMBER |
|
… all other foreign keys from Claim Line … |
||
ANESTHESIA_MINUTES |
anesthesiaMinutes |
|
AREA_ORAL_CAVITY |
areaOralCavity |
|
… all other US Seed single value dynamic fields … |
||
… locally defined single value dynamic fields … |
||
… technical columns … |
REL_RELATIONS_V
ID |
NUMBER |
Unique internal id for this relation. |
BUSINESS_PHONE_NUMBER |
VARCHAR2 |
|
CODE |
VARCHAR2 |
|
EMAIL_ADDRESS_1 |
VARCHAR2 |
|
EMAIL_ADDRESS_2 |
VARCHAR2 |
|
END_DATE |
DATE |
|
FAX_NUMBER |
VARCHAR2 |
|
MOBILE_PHONE_NUMBER |
VARCHAR2 |
|
NAME |
VARCHAR2 |
|
… all other fixed fields from Relation … |
||
ORGANIZATION_PROVIDER_ID |
NUMBER |
|
… all other foreign keys from Relation … |
||
NATIONAL_ID |
VARCHAR2 |
|
… other locally defined single value dynamic fields … |
||
… technical columns … |
The following query can be used to retrieve a claim line with its claim details and the name of the claimant.
select claim.code , claim.version , claimant.name , claim_line.code , claim_line.allowed_amount , claim.accident_date , claim.accident_state from cla_claims_v claim , cla_claim_lines_v claim_line , rel_relation_v claimant where claim.id = claim_line.claim_id and claim.claimant_rel_id = claimant.id
Note that the joins between claim and claim line and relation are implemented using the internal ID columns.
Dynamic Field Base Views
Each dynamic field base view corresponds to a dynamic field definition. There is a dynamic field base view for each dynamic field that is either:
-
time valid and / or multivalue
-
a dynamic record
The view for each free field dynamic field has:
-
a column holding the internal id for a free field value
-
a column holding the internal id of the parent record has the free field value
-
a column holding the value
-
columns for start date and end date.
The internal id for a free field value is unlikely to be used. It is only included for consistency purposes.
The view for each flex code dynamic field has:
-
a column holding the internal id for the 'association' between a record and a flex code
-
a column holding the internal id of the parent record that has the flex code
-
if the view is for a standard flex code dynamic field, a column for the 'code' field of the flex code as well as one for the flex code’s internal id (foreign key)
-
if the view is for a procedure flex code, columns for the procedure code, descr, and id (foreign key)
-
if the view is for a diagnosis flex code, columns for the diagnosis code, descr, and id (foreign key)
-
if the view is for a provider flex code, columns for the provider code, flex code definition code, name, and id (foreign key)
-
columns for start date and end date.
The view for each dynamic record definition has:
-
a column holding the internal id for the dynamic record
-
a column holding the internal id of the parent record that has the dynamic record
-
configured columns
View Naming
The names of these views will be the internal OHI Claims table alias + '_'
the dynamic field usage name. The OHI Claims table alias is 4 characters.
In the case of flex code sets, there will be a separate view for each flex code of the set. The naming of these views will include the name of the set in the name to make it visible that a dynamic field belongs to a set.
When naming dynamic field usages, it is important to keep usage names short enough to be valid table names after addition of the table alias. The maximum length including prefix and suffix is 30. Therefore, the maximum length of a multi-value or time valid dynamic field usage name is 25.
Example
This example shows the view for a dynamic field defined on the claim entity with the following details:
-
Flex Code Name: Occupations
-
Flex Code Fields
-
1: Code
-
2: Description
-
3: Risk Level
-
-
Dynamic Field Usage Name: Person Occupations
Note that the internal OHI Claims table alias for CLA_CLAIMS is 'CLAI'.
CLAI_PERSON_OCCUPATIONS
ID |
NUMBER |
Unique internal id for a row of values |
CLAIM_ID |
NUMBER |
Internal id of the immediate parent entity |
FLEX_CODE_ID |
NUMBER |
Internal id of a flex code value record (join to FCOD_OCCUPATIONS, see below) |
START_DATE |
DATE |
Date values are effective from. |
The following query can be used to retrieve the list of person occupations for claim 123.
select claim.code, , occupation.code , occupation.risk_level , person_occupation.start_date from claims_b claim , clai_person_occupations person_occupation , fcod_occupations occupation where claim.id = person_occupation.claim_id and person_occupation.flex_code_id = occupation.id claim.code = '123'
Note that join between claim and person occupations is implemented using the internal ID columns.
Flex Code Base Views
Each flex code base view corresponds to a flex code definition. Each of these views lists the 'valid values' for a flex code
The view for each flex code definition has:
-
a column holding the internal id for the flex code
-
a column for each field that has been configured for the flex code
Example
This example shows the view for a flex code defined with the following details:
-
Flex Code Name: Occupations
-
Flex Code Fields:
-
1: Code
-
2: Description
-
3: Risk Level
-
FCOD_OCCUPATIONS
ID |
NUMBER |
Unique internal id for a flex code 'value' record |
CODE |
VARCHAR2 |
Flex code value 1 |
DESCRIPTION |
VARCHAR2 |
Flex code value 2 |
RISK_LEVEL |
VARCHAR2 |
Flex code value 3 |
Note that join between claim and person occupations is implemented using the internal ID columns.