Base Views (Deprecated)

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

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 Oracle Health Insurance entity model, dynamic field concepts as well as database concepts such as foreign keys.

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 Policiess that have data model changes. View generation is documented separately.

The entity views correspond to the functional meaningful entities as described in the data model sections of the Operations 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.

Naming Convention

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
POL	Policies	Entities related to policies
COD	Dynamic Field Definitions	Internal entities that implement the dynamic field feature. Probably not of interest to other systems. Include only for completeness.
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.
REL	Relations and Providers	Entities related to relations and providers.

Application Area Code

Application Area

Note

POL

Policies

Entities related to policies

COD

Dynamic Field Definitions

Internal entities that implement the dynamic field feature. Probably not of interest to other systems. Include only for completeness.

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.

REL

Relations and Providers

Entities related to relations and providers.

The entity names correspond to the data model names (of the Operations Guide) more or less directly.

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 (for example, to join the policy enrollment view to the policy 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 Operations 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 policy enrollment to the 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 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.

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 Policies) and would require a detailed understanding of the Policiesuser access model and concepts.

Dynamic Fields

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

The names of these views will be the internal Policies table alias + '_' + the dynamic field usage name. The Policiestable 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.

Flex Codes

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

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

'FCOD_'
Flex Code Name