Base Views (Deprecated)

The reporting views in this section are deprecated. They are replaced with the base views described in Overview.

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 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),

  • 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 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 Developer Guide. 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:

Table 1. View Naming
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 Developer Guide) 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 "Data Model" sections of the Developer Guide. 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.

Technical Columns

The underlying tables for entities have several technical columns. These are included in the views. These columns indicate information such as when records were created or last updated and by which user.

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 Claims) and would require a detailed understanding of the 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.

Table 2. 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 native 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 …​
Table 3. 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 native 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 …​
Table 4. 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 native 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 Claims table alias + '_'
the dynamic field usage name. The 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 Claims table alias for CLA_CLAIMS is 'CLAI'.

Table 5. 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

View Naming

Entity base view names are composed of 3 components as follows:

  • 'FCOD_'+

  • Flex Code Name

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

Table 6. 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.