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