Data Providers

Data Providers

A number of data providers are available for selection, but every kind of ‘configurable’ data is not covered. Additional data providers may be added to the framework at relatively low cost.

In the following, data provider specific details are described.

Data Providers for Reference Data #

Entities supported:

REFERENCE_DATA
REF_TO_REF
REF_DATA_NAME
REF_DATA_TYPE
REF_DATA_DEP_TYPE

For notes attached to REFERENCE_DATA items, the link goes via a join table REF_TO_NOTE. This may seem to imply that one REFERENCE_DATA item can have several notes. However, this is not the case. SICS ensures that each REFERENCE_DATA item will have at most one note. This means it is possible to simplify the data provider for REFERENCE_DATA, by creating a “virtual” column for the note string. When there is a difference in the note string, and SQL is to be generated for the REFERENCE_DATA entity, the data provider will create the necessary SQL for REF_TO_NOTE, CNU_NOTE and BIG_STRING_SUB.

Note that REF_DATA_NAME has a dependency to REFERENCE_DATA not only via the FRK/FSK_REF_DATA columns, but also via the LANGUAGE_CODE column, which is actually the CODE of a REFERENCE_DATA row with SUBCLASS 7.

Data Providers for Entry Codes #

Entities supported:

ENTRY_CODE
ENT_CODE_REF_DATA
EC_DETAIL_REF_DATA
EC_VS_BC
ENTRY_CODE_GROUP
CODE_VS_GROUP
ECG_TO_ECG
ENTRY_CODE_USAGE

Data Providers for General System Parameter Key/Value Pairs #

Several system parameters (from the System Parameters GUI) are stored in the database using a complex data structure. EMessage Rules is but one example. There are numerous others.

On the other hand, there are system parameters which are stored simply as key/value pairs in the SYS_PARAM_VALUES table. Some of these are global system parameters whith no link to a specific base company or reporting unit. Others do have a link to a specific base company or reporting unit.

The data providers for general system parameter key/value pairs cover the key/value pairs in the SYS_PARAM_VALUES table and their parent registries in SYSTEM_PARAMETERS.

Let the mouse rest over a value in the PARAM_KEY column in order to display hover help describing details about where in the System Parameters GUI this system parameter is displayed / edited.

System Parameter Registries #

Each key/value pair in SYS_PARAM_VALUES belongs to a specific registry (in SYSTEM_PARAMETERS). A the top level is the Global Registry. Then there may be zero or more Base Company Specific Registries (actually, base company and optionally a reporting unit). Note that there is no link (no foreign key) between the GR and the BCRs.

Each Base Company Specific Registry may have zero or one Booking Terms Registry. A Booking Terms Registry may be linked to more than one Base Company Specific Registry. Note that such BTR parameters also exist for the Global Registry, in which case they are directly linked to the GR (internally, the BTR for the GR is the GR itself).

Each Base Company Specific Registry may also have one or more Delegated Registries. A Delegated Registry captures a distinct set of business related key/value parameters. Note that such parameters also exist for the Global Registry, in which case they are directly linked to the GR (the GR does not utilize the concept of DRs).

In addition to having simple key/value pairs, most registries can also hold relationship to more complex system parameters stored in other tables.

The following graphic depicts the registries, the general key/value parameters and the complex parameters

As mentioned, BCRs and DRs contain a Base Company reference and optionally a Reporting Unit reference. Such references are OBJECT_ID foreign keys. More likely than not, the ‘same’ BC (or RU) does not have the same OBJECT_ID in the source and target schemas. Consquently, the IDENTIFIER will be used identify a Base Company, and the CODE will be used to identify a Reporting Unit. If a category 3 (see Data Providers) source registry rowrefers to a BC/RU which does not exist (same IDENTIFIER/CODE) in the target schema, then the user must “manually” create the missing BC/RU in the target schema using the normal Create Business Partner’ / ‘Create Reporting Unit’ functionality.

Default Values #

One should note that most key/value system parameters have a hard-coded default value and that these parameters are NOT present in SYS_PARAM_VALUES after installing SICS.

For example, the system parameter “Allow Proportional OCC Placement above 100%” on the Business tab has key 'ALLOW_ABE_PL', but it is not initially present. The default value is 0 (false). Let us assume that this parameter was never used neither in source or target schema. This means that the key 'ALLOW_ABE_PL' is missing in both schema and there is no difference.

Now, if in the source schema this parameter is turned on, there will be a key 'ALLOW_ABE_PL' with value 1 (true) in SYS_PARAM_VALUES. If it is then turned off again, the key will still be there, now with value 0. So now, if one compares the source and target schema, the configuration manager will report that there is a difference, even though the effect in SICS of having 'ALLOW_ABE_PL' = 0 and not having 'ALLOW_ABE_PL', is the same. In other words, one can safely copy this difference to the target schema. The configuration manager will not make any attempt to hide / suppress such “irrelevant” differences.

Technical Details #

Global Registry

A row R in SYSTEM_PARAMETERS represents the GR if and only if

There does NOT exist a row S in SYS_PARAM_VALUES such that S.FK_OWNER = R.OBJECT_ID AND S.PARAM_KEY = 'QUALIFIER'
R.FK_BASE_COMP = NULL
R.FK_REP_UNIT = NULL

The GR is always linked with the default BTR. In fact, the GR contains the default BTR. It appears that there is indeed a row S in SYS_PARAM_VALUES such that S.FK_OWNER = GR.OBJECT_ID AND S.PARAM_KEY = 'BOOKING_TERM_ID', and the S.PARAM_VALUE contains the OBJECT_ID of the GR itself.

Note that the GR is not flagged as a BTR in the normal sense (using a ‘IS_BOOKING_TERM’ flag as described for BTR below). Nor does the GR have a 'QUALIFIER' parameter.

The name of the default BTR is always ‘’. This is not represented as a key/value pair in SYS_PARAM_VALUES, but is hardcoded in Java and cannot be changed.

The GR can have a

Base Company Specific Registry

A row R in SYSTEM_PARAMETERS represents a BCR if and only if

There does NOT exist a row S in SYS_PARAM_VALUES such that S.FK_OWNER = R.OBJECT_ID AND S.PARAM_KEY = 'QUALIFIER'
R.FK_BASE_COMP NOT = NULL OR R.FK_REP_UNIT NOT = NULL

The BCR has an explicit link to a BTR if and only if there exists a row S in SYS_PARAM_VALUES such that S.FK_OWNER = R.OBJECT_ID AND S.PARAM_KEY = 'BOOKING_TERM_ID'. In such a case, S.PARAM_VALUE contains the OBJECT_ID of the SYSTEM_PARAMETER row which represents the BTR.

NOTE: When a BTR is deleted, the rows in SYS_PARAM_VALUES where PARAM_KEY = 'BOOKING_TERM_ID' and PARAM_VALUE equals the SYSTEM_PARAMETERS.OBJECT_ID of the deleted BTR will NOT be removed. In a way, these BCRs now point to a missing BTR. This is interpreted as using the default BTR (which is contained in the GR).

Booking Terms Registry

A row R in SYSTEM_PARAMETERS represents a BTR (except the default one, which is contained in the GR) if and only if there exists a row S in SYS_PARAM_VALUES such that S.FK_OWNER = R.OBJECT_ID AND S.PARAM_KEY = 'IS_BOOKING_TERM'.

The name of the BTR is given by the value of the SYS_PARAM_VALUES row with key='REGISTRY_NAME'.

A BTR also has a row in SYS_PARAM_VALUES with key='QUALIFIER' and value='bookingTermsDefinition'.

Regarding deletion of a BTR, please see note under BCR above.

Delegated Registry

A row R in SYSTEM_PARAMETERS represents a DR if and only if

there exists a row S in SYS_PARAM_VALUES such that S.FK_OWNER = R.OBJECT_ID AND S.PARAM_KEY = 'QUALIFIER'
there does NOT exist a row S in SYS_PARAM_VALUES such that S.FK_OWNER = R.OBJECT_ID AND S.PARAM_KEY = 'IS_BOOKING_TERM'.

The DR is implicitly linked to a BCR, namely the one where BCR.FK_BASE_COMP = DR.FK_BASE_COMP AND BCR.FK_REP_UNIT = DR.FK_REP_UNIT.

Note that the GR does not have any DR’s. Instead, the DR parameters are directly linked to the GR (in other words, these parameters’ FK_OWNER do not point to any DR (there is none), but to the GR).

Data Providers foreMessaging Rules #

Each rule set (default rule set or rule set template, table EMES_RULE_SET) consists of zero, one or more rule configurations (tables EMES_RULE_CONF + EMES_RULE_DATA). In fact, the EMES_RULE_CONF table acts as a join table between EMES_RULE_SET and EMES_RULE_DATA.

A rule set can be:

a factory rule set (linked to reference data)
a template rule set (linked to reference data)
a specific rule set linked to either reference data, business partner, insured period or claim section.

Comparison is available at

Rule set level - EMES_RULE_SET row comparison. For subclass 3 and 4, using an extended key (EK) consisting of FRK_LAYER, FRK_OWNER_RD, FRK_OWNER_RD2, and SUBCLASS.

For subclass 1, using an extended key consisting of FK_OWNER_IP.

For subclass 2, using an extended key consisting of FK_OWNER_PARTY.

For subclass 6, using an extended key consisting of FK_OWN_CLM_SEC.

NOTE: The columns FK_OWNER_IP, FK_OWNER_PARTY and FK_OWN_CLM_SEC contain OBJECT_ID references to InsuredPeriod, Party and ClaimSection, respectively. These three columns are in use if and only if the SUBCLASS is 1, 2 or 6. Therefore, rows with SUBCLASS 1, 2 or 6 will be compared using OBJECT_ID values. This will only work successfully if the source schema was once created as a copy of the target schema (or vice versa).

Note 2: The FK_PARENT column is a pointer to the parent Rule Set [Template]. The parent rule set is always a ‘basic’ one (SUBCLASS 3 or 4), meaning it can be linked to one or two reference data, but not to business partner, insured period or claim section. When comparing this column between a pair of source/target rows, the OBJECT_ID contained in FK_PARENT will not be used directly. Rather, the extended key of the rule set pointed to by FK_PARENT will be used.

Rule data level - EMES_RULE_DATA row comparison, using IDENTIFIER as extended key.
Rule configuration level - row comparison of EMES_RULE_CONF, using an extended key consisting of the extended keys from EMES_RULE_SET and EMES_RULE_DATA.

Data Providers foreMessagingMappings #

The system parameters for eMessaging mappings are complex.

Depending on the mapping type selected in the left-hand list, there will be different columns/data in the main container, and these are not general key/value pairs, but stored in a number of separate database tables.

The following eMessaging mapping types are included in this data provider:

Class of Business
Entry Code
Type of Participation

‘Class of Business’ and ‘Type of Participation’ mappings are stored in the EMES_MAPPING table, SUBCLASS 1 and 2, respectively. Note that the column FK_SICS_OBJECT is not used with SUBCLASS 1 and 2.

‘Entry Code’ mappings are stored in EMSG_EC_INFO.

Entities supported:

EMES_MAPPING (only Class of Business and Type of Participation)
EMSG_EC_INFO

Comparison is available for

Class of Business mapping type - EMES_MAPPING row comparison, SUBCLASS 1, using EMSG_REF_DATA (ACORD XML code) as extended key.
Type of Participation mapping type - EMES_MAPPING row comparison, SUBCLASS 2, using EMSG_REF_DATA (ACORD XML code) as extended key.
Entry Code mapping type - EMSG_EC_INFO row comparison, using Message Type, ACORD Accounting Code Set, ACORD Accounting Code, ACORD Amount Status Code Set, ACORD Amount Status, Message Function and TechAccount Direct as extended key.

Data Providers forCurrency #

Entities supported:

CNU_CURRENCY

Data Providers forLocations #

Entities supported:

LEGAL_AREA, SUBCLASS=1 (Country)
LEGAL_AREA, SUBCLASS=2 (State)
LEGAL_AREA, SUBCLASS=6 (City)
LEGAL_AREA, SUBCLASS=4 (Country Area Group)
LEGAL_AREA, SUBCLASS=5 (State Area Group)
AREA_GROUP_COUNTRY (Countries being part of a Country Area Group)
AREA_GROUP_STATE (States being part of a State Area Group)

Notes:

A country has several 1-many relations to details like states, areas, currencies, deductions, etc. Currently, only relations to other location entities are implemented (state, city, country area group, state area group).

Data Providers forReporting Units #

Entities supported:

RU_LEAF (Reporting Unit)
RU_TO_COB (Reporting Unit vs Class of Business)
RU_TO_LA (Reporting Unit vs Location)
RU_TO_BC (Reporting Unit vs Base Company)
REPORTING_UNIT, SUBCLASS 2, 3 AND 4 (Reporting Unit Hierarchy, root or group level)
REPORTING_UNIT, SUBCLASS 5 (Reporting Unit Hierarchy Leaf)

Notes: In the data model, RU_LEAF is the Reporting Unit Concrete Leaf. In SICS as an end user sees it, this is a Reporting Unit.

The hierarchy leaf (REPORTING_UNIT, SUBCLASS 5 ) is a kind of ‘join’ between a hierarchy group and a reporting unit [concrete leaf] (RU_LEAF). It allows a reporting unit to participate in more than one hierarchy group.

Data providers for Automated Document Handling #

Entities supported:

TRANS_TAB, (ADH Translation table)
TRANS_TAB_COL (ADH Translation table column definition)
TRANS_TAB_ROW (ADH Row)

Notes:

Data rows belonging to one specific header, do not have any kind of “hard” id. They only have translation values. However, when translation values are added to a TT, validation logic ensures that the tuple consisting of the input column values are always unique within the table. Hence this tuple can be used as part of the extended key.

The column definitions vary between tables. This means that row values stored in VAL_0, VAL_1, etc, have different interpretation depending on which table the row belongs to, and how the columns for that table are defined.Due to this dynamic nature of row values, all values that refer to other database objects (for example, Business Partners, Currencies, Entry Codes, etc) are treated as being dependent on external (outside the Configuration Manager) data, even if the CM has data providers for some of these objects.This means that unsatisfied external data dependencies (shown in olive color) must be separately corrected before translation row values can be synchonized between source and target.

Furthermore, this table shows an additional, ‘virtual’ column 'COL_DEF' at the far right. This column shows the hash value of the table’s column definitions, and serves to disable table row synching when source and target column definitions differ. The COL_DEF column is part of the row’s extended key.

COL_DEF values are shown in olive color when source and target column definitions differ. In such a case, column definitions must be completely synched first, before any rows can be synched.