Extending Tables

The purpose of extending tables is to enable customers to add a field to the Oracle Health Insurance data model with market, business line and customer specific fields. Use these fields to simply persist information. Use them to affect the way a claim, a person, a policy, an authorization or a contract is processed. This page explains how to extend the Oracle Health Insurance data model.

An entity can be extended by additional fields or additional details. A field can either be a free value or bound by a predefined domain, like a date field or a secondary ID field. A detail is the equivalent of adding a new table to the subject entity.

Adding Dynamic Fields and Records

A dynamic field usage is the link between a table (entity) and the configured dynamic field that extends that table. Not all tables can be extended.

Some tables that can be extended, can only be extended sometimes have limitations to the type of fields that can be extended with. For example, whether they could be time valid and whether the table supports details. A dynamic field usage has the following attributes:

Table 1. Adding Dynamic Fields and Records
Field Description

Table

The table that is extended

Name

The name that is used by interfaces and dynamic logic scripts. The name can be specified in mixed case, to support the coding conventions in use. The name for a dynamic field usage cannot be equal to the name of a native field or another dynamic field usage for the same table

Display Name

The label of the field as shown in the user interface

Sub Type

Field, Code or Record

Field

The field that defines this dynamic field

Code Definition

The single flex code definition or flex code set that represents the domain of allowed flex codes

Record Definition

The dynamic record definition that defines what kind of records the table is extended with

Sequence

Drives the placement of this dynamic field in standard user interface

Condition

Determines whether a dynamic field or record value may be set

Mandatory Condition

Determines whether a dynamic field or record value is mandatory

Mandatory?

A dynamic field or record value must be specified

Unique?

The value of the field must be unique across all records in the extended table

Multiple values?

The dynamic field or record can store more than one value

Time valid?

The dynamic field or record values are stored with a start and end date

Search?

The field appears in the advanced search feature for the extended table
For JET: Indicates if this field is searchable.

In LOV?

The dynamic field is displayed in the LOV on the extended table.

Search in LOV?

If checked, then it is possible to search on that dynamic field in the default LOV.

Display in Overflow?

This indicates whether the dynamic field can be displayed in the overflow area in a multi record block

Number Format Type

The format in which a numeric dynamic field can be displayed

Access Restriction

Determines whether the access to the usage is restricted

Start date

The first date that a value can be set for the specified dynamic field

End date

The last date that a value can be set for the specified dynamic field

PII?

The field or record contains Personal Identifiable Information (PII)

Depending on the sub-type, a dynamic field usage refers to either a field definition, a code definition or a record definition.

Only usages for fields can be configured as unique. A dynamic field usage of the type Field cannot be both unique and have multiple values. The unique indicator can no longer be checked once actual values have been stored for that usage.

Once the usage has been created and the dynamic field has been set up to add a field to a floor plan, the Floor Plan Configuration page explains how to add a field to a floor plan to control what is displayed on the UI to the end user.

Start and End Date

The start and end date on a usage represent the period in which it is possible to populate the dynamic field with a value. The reference date that is used depends on the extended entity.

Table 2. Start and End Date
Component Reference Date

Oracle Health Insurance Claims Adjudication and Pricing

claim date for claims, start date for claim lines, start date for authorizations, start date for procedure setting, start date for diagnosis setting and the system date for all other tables.

Oracle Health Insurance Enterprise Policy Administration

start date for policy enrollment product.

The start and end date of a 'flex code' dynamic field usage are bound by the start and end date of the referenced flex code definition.

Time Valid?

If the time validity indicator in the usage is checked, then the values that are stored in the dynamic field have a start and end date. Consider the following example:

In order to keep track of a person’s income, the user wants to extend the relation table with a field that stores an income. The time validity indicator on the dynamic field usage is checked. It is now possible for the same person record to have an income of $ 5000 from 2018-01-01 through 2018-12-31 and income of $8000 from 2019-0-01 through 2019-12-31 through a single dynamic field 'income'.

Creating a time valid dynamic field is like adding a detail to a table, in which each record in the detail table has a start and end date. Once a value has been stored for the field or record usage, the time-valid indicator can no longer be checked or unchecked.

Multiple Values?

It is possible for a user to set up a dynamic field that contains more than a single value. Consider the following example:

In order to keep track of a person’s hobbies, the user extends the relation table with a dynamic field named hobbies. Because it is possible that a person can have multiple hobbies, the user checks the multi value indicator in the dynamic field usage. This setup allows a relation to be imported with a hobby field that has two or more values, for example, the field hobbies has the values rock climbing and bungee jumping.

Creating a multi value dynamic field is similar to adding a detail table to a primary table. Multi value dynamic fields are restricted such that no two values can be identical within the list.

It is possible for a dynamic field to be both time valid AND have multiple values. A single-valued time valid dynamic field can have multiple values over time, but no more than one at any one point. A multi-valued time valid dynamic field can have multiple values over time and multiple values may be time valid at any one point.

Condition

It is possible for a user to set up a condition on the usage of a dynamic field. This condition controls whether it is possible to set the dynamic field. The condition is typically used for subtypes. Consider the following scenario:

In order to keep track of person’s occupation, a user wants to extend the relation table with a field that stores an occupation. However, the relation table contains both persons and organizations. Since it is logical to extend a person with an occupation, the user sets up a dynamic logic condition that checks if the relation record represents a person. This condition is evaluated whenever someone tries to set a value for the occupation field. If the records does not represent a person, then it is not allowed to provide a value for the 'occupation' dynamic field.

A dynamic logic condition can be tied to a message. This message is generated whenever the dynamic condition evaluates false. In this event, the substitution parameter {0} holds the dynamic field usage display name by default. Consider the following example.

dateOfDeath is a dynamic logic condition that ensures that this dynamic field can only be entered for relations of the subtype person.

return relation.isPerson()

The dynamic field usage dateOfDeath with the display name "Date of Death" specifies a dynamic field that is tied to this condition. The message that is tied to the condition reads: "It is not allowed to specify a {0} for this relation". When (through the relation integration point) a relation request message attempts to specify the Date of Death dynamic field for a relation of the subtype "organization", the system responds with the message: "It is not allowed to specify a Date of Death for this relation".

Number Format Type

Any fixed number column is assigned a number format type that specifies the way it is displayed in the UI; the following types exist:

Table 3. Number Format Type
Type Description Example

Amount

Includes a thousands separator in combination with 2 decimals

1234 is displayed as 1,234.00 and 1234.5 is displayed as 1,234.50

Number

Includes a thousands separator in combination with as many decimals as are specified

1234 is displayed as 1,234 and 1234.5 is displayed as 1,234.5

Integer

Includes no thousands separator

1234 is shown as 1234 (useful for example, a house number in an address)

Formatted Integer

Includes a thousands separator

1234 is shown as 1,234 (useful for example, a maximum number of units)

Dynamic Field Values

Dynamic field values can be set and updated through both the user interface pages and integration points.

For additional information on how Oracle Health Insurance handles dynamic fields on incoming HTTP API Integration Point messages, refer to the Developer Guide.

Changing Usages when Values Exist

After values have been set, not all usage settings can be changed. The following restrictions apply:

  • A dynamic field usage cannot be deleted once a value for that dynamic field and table exists

  • A dynamic field usage cannot be marked as unique, time valid or multi value once a value for that dynamic field and table exists

  • A dynamic field usage cannot be unmarked as multi value once a value exists

  • It is not possible to decrease the length or change the decimal value for a field when flex codes exist

When changing mandatory setting that does not trigger an immediate check in the system to see if all values have been set. It means that whenever new values are added the mandatory restrictions apply.

The same applies for fields in dynamic records. Add mandatory fields to a dynamic record or mark existing field usage as mandatory but the system does not enforce the 'mandatory-check' on occurrences of that dynamic record until the next update of that dynamic record.

Examples

Example 1

A customer wants to extend the relation table with a field that captures the Body Mass Index (BMI). The first step is to create a field. The field represents a data type, in this case a number.

bmi

The user creates a new field with the code 'bodyMassIndex'. The description is a 'Number and two decimal points', data type is 'num' and maximum length is '5'.

The user creates a new dynamic field usage for the relation table. The name is 'bodyMassIndex' and the display name is 'BMI'. The customer sets the display sequence to 1, meaning that in the standard pages for persons, this is the first dynamic field listed in the designated section. The free field 'C05 is referenced'.

The value of the BMI field is not unique, has only a single value, is not (conditionally) mandatory and is not time valid. There is no condition on whether a value is allowed. There are no time constraints. The result of our setup is that the Relation page displays a new field, labeled 'BMI' that can contain up to 5 characters.

Example 2

For some health plans, the system must know whether the member has a pre-existing medical condition. This helps to ascertain if the member is eligible for certain benefits or not.

A 'Federal Poverty Level Tier' (fplTier) indicator is only applicable to a person and not an organization.

The user sets up a flex code system named 'fplTier' that allows the following values:

  • N, meaning 'No',

  • Y, meaning 'Yes',

For additional information on how to set up a flex code system, refer to Flex Code Definitions. The user creates a new dynamic field usage for the 'Claim diagnosis' table, that is, the table that connects a diagnosis with a claim.

The dynamic field usage is set up as follows:

  • Name - fplTier

  • Display name - Federal Poverty Level Tier'

  • Type - Code

  • Flex code system - fplTier

  • Condition - ONLY_PERSON

The following fields remain un-checked:

  • Multivalue

  • Time Validity

  • Unique

  • Mandatory

  • Search

The condition 'ONLY_PERSON' is a dynamic logic condition that represents that the relation is a 'person'. This way, the user ensures that only persons with a Federal Poverty Level Tier indicator to their attached diagnosis are considered.