1. Oracle Enterprise Risk and Finance Insurance Products Container Pack
  2. Post Docker Deployment Activities
  3. Post Installation Steps for OILM

3.8 Post Installation Steps for OILM

Run the following scripts in Atomic Schema after installing OILM:

Script 1

MERGE INTO FSI_DIM_LOADER_SETUP_DETAILS T

USING (

SELECT (select DIMENSION_ID from rev_dimensions_TL where dimension_name='Product') N_DIMENSION_ID,'STG_PRODUCTS_B_INTF' V_INTF_B_TABLE_NAME,'N_PRODUCT_DISPLAY_CODE' V_INTF_MEMBER_COLUMN,'STG_PRODUCTS_TL_INTF' V_INTF_TL_TABLE_NAME,'STG_PRODUCTS_ATTR_INTF' V_INTF_ATTR_TABLE_NAME,'STG_PRODUCTS_HIER_INTF' V_INTF_HIER_TABLE_NAME,to_date(sysdate,'dd-mm-yyyy') D_START_TIME,to_date(sysdate,'dd-mm-yyyy') D_END_TIME,'' V_COMMENTS,'' V_STATUS,'V_PRODUCT_NAME' V_INTF_MEMBER_NAME_COL,'' V_GEN_SKEY_FLAG,'V_PRODUCT_CODE' V_STG_MEMBER_COLUMN,'' V_STG_MEMBER_NAME_COL,'' V_STG_MEMBER_DESC_COL,'V_PROD_CODE' V_STG_INTF_MEMBER_COLUMN FROM DUAL

UNION ALL

SELECT (select DIMENSION_ID from rev_dimensions_TL where dimension_name='Location') N_DIMENSION_ID,'STG_LOCATION_B_INTF' V_INTF_B_TABLE_NAME,'N_LOCATION_DISPLAY_CODE' V_INTF_MEMBER_COLUMN,'STG_LOCATION_TL_INTF' V_INTF_TL_TABLE_NAME,'STG_LOCATION_ATTR_INTF' V_INTF_ATTR_TABLE_NAME,'STG_LOCATION_HIER_INTF' V_INTF_HIER_TABLE_NAME,to_date(sysdate,'dd-mm-yyyy') D_START_TIME,to_date(sysdate,'dd-mm-yyyy') D_END_TIME,'' V_COMMENTS,'' V_STATUS,'V_LOCATION_NAME' V_INTF_MEMBER_NAME_COL,'' V_GEN_SKEY_FLAG,'V_LOCATION_CODE' V_STG_MEMBER_COLUMN,'' V_STG_MEMBER_NAME_COL,'' V_STG_MEMBER_DESC_COL,'V_LOCATION_CODE' V_STG_INTF_MEMBER_COLUMN FROM DUAL

UNION ALL

SELECT (select DIMENSION_ID from rev_dimensions_TL where dimension_name='Zone') N_DIMENSION_ID,'STG_ECONOMIC_ZONE_MASTER' V_INTF_B_TABLE_NAME,'V_ECONOMIC_ZONE_CODE' V_INTF_MEMBER_COLUMN,'STG_ECONOMIC_ZONE_MASTER' V_INTF_TL_TABLE_NAME,'STG_ECONOMIC_ZONE_MASTER' V_INTF_ATTR_TABLE_NAME,'STG_ECONOMIC_ZONE_MASTER' V_INTF_HIER_TABLE_NAME,to_date(sysdate,'dd-mm-yyyy') D_START_TIME,to_date(sysdate,'dd-mm-yyyy') D_END_TIME,'' V_COMMENTS,'' V_STATUS,'V_ECONOMIC_ZONE_CODE' V_INTF_MEMBER_NAME_COL,'' V_GEN_SKEY_FLAG,'V_ECONOMIC_ZONE_CODE' V_STG_MEMBER_COLUMN,'V_ECONOMIC_ZONE_CODE' V_STG_MEMBER_NAME_COL,'V_ECONOMIC_ZONE_DESC' V_STG_MEMBER_DESC_COL,'V_ECONOMIC_ZONE_CODE' V_STG_INTF_MEMBER_COLUMN FROM DUAL

UNION ALL

SELECT (select DIMENSION_ID from rev_dimensions_TL where dimension_name='Country') N_DIMENSION_ID,'STG_COUNTRY_MASTER' V_INTF_B_TABLE_NAME,'V_CCY_CODE' V_INTF_MEMBER_COLUMN,'STG_COUNTRY_MASTER' V_INTF_TL_TABLE_NAME,'STG_COUNTRY_MASTER' V_INTF_ATTR_TABLE_NAME,'STG_COUNTRY_MASTER' V_INTF_HIER_TABLE_NAME,to_date(sysdate,'dd-mm-yyyy') D_START_TIME,to_date(sysdate,'dd-mm-yyyy') D_END_TIME,'' V_COMMENTS,'' V_STATUS,'V_COUNTRY_NAME' V_INTF_MEMBER_NAME_COL,'' V_GEN_SKEY_FLAG,'V_CCY_CODE' V_STG_MEMBER_COLUMN,'V_COUNTRY_NAME' V_STG_MEMBER_NAME_COL,'V_COUNTRY_DESC' V_STG_MEMBER_DESC_COL,'V_CCY_CODE' V_STG_INTF_MEMBER_COLUMN FROM DUAL

) S

ON (T.N_DIMENSION_ID = S.N_DIMENSION_ID)

WHEN MATCHED THEN

UPDATE

SET T.V_INTF_B_TABLE_NAME = S.V_INTF_B_TABLE_NAME,

T.V_INTF_MEMBER_COLUMN = S.V_INTF_MEMBER_COLUMN,

T.V_INTF_TL_TABLE_NAME = S.V_INTF_TL_TABLE_NAME,

T.V_INTF_ATTR_TABLE_NAME = S.V_INTF_ATTR_TABLE_NAME,

T.V_INTF_HIER_TABLE_NAME = S.V_INTF_HIER_TABLE_NAME,

T.D_START_TIME = S.D_START_TIME,

T.D_END_TIME = S.D_END_TIME,

T.V_COMMENTS = S.V_COMMENTS,

T.V_STATUS = S.V_STATUS,

T.V_INTF_MEMBER_NAME_COL = S.V_INTF_MEMBER_NAME_COL,

T.V_GEN_SKEY_FLAG = S.V_GEN_SKEY_FLAG,

T.V_STG_MEMBER_COLUMN = S.V_STG_MEMBER_COLUMN,

T.V_STG_MEMBER_NAME_COL = S.V_STG_MEMBER_NAME_COL,

T.V_STG_MEMBER_DESC_COL = S.V_STG_MEMBER_DESC_COL,

T.V_STG_INTF_MEMBER_COLUMN=S.V_STG_INTF_MEMBER_COLUMN

WHEN NOT MATCHED THEN

INSERT

(N_DIMENSION_ID,V_INTF_B_TABLE_NAME,V_INTF_MEMBER_COLUMN,V_INTF_TL_TABLE_NAME,V_INTF_ATTR_TABLE_NAME,V_INTF_HIER_TABLE_NAME,D_START_TIME,D_END_TIME,V_COMMENTS,V_STATUS,V_INTF_MEMBER_NAME_COL,V_GEN_SKEY_FLAG,V_STG_MEMBER_COLUMN,V_STG_MEMBER_NAME_COL,V_STG_MEMBER_DESC_COL,V_STG_INTF_MEMBER_COLUMN)

VALUES

(S.N_DIMENSION_ID,S.V_INTF_B_TABLE_NAME,S.V_INTF_MEMBER_COLUMN,S.V_INTF_TL_TABLE_NAME,S.V_INTF_ATTR_TABLE_NAME,S.V_INTF_HIER_TABLE_NAME,S.D_START_TIME,S.D_END_TIME,S.V_COMMENTS,S.V_STATUS,S.V_INTF_MEMBER_NAME_COL,S.V_GEN_SKEY_FLAG,S.V_STG_MEMBER_COLUMN,S.V_STG_MEMBER_NAME_COL,S.V_STG_MEMBER_DESC_COL,S.V_STG_INTF_MEMBER_COLUMN )

/

MERGE INTO fsi_dim_attribute_map t

USING ( SELECT

(select DIMENSION_ID from rev_dimensions_TL where dimension_name='Country') n_dimension_id,

'STG_COUNTRY_MASTER' v_stg_table_name,

'V_CCY_CODE' v_stg_column_name,

'Country' v_attribute_name,

'Y' v_update_b_code_flag

FROM

dual

)

s ON ( t.n_dimension_id = s.n_dimension_id

AND t.v_stg_table_name = s.v_stg_table_name

AND t.v_stg_column_name = s.v_stg_column_name

AND t.v_attribute_name = s.v_attribute_name )

WHEN MATCHED THEN UPDATE

SET t.v_update_b_code_flag = s.v_update_b_code_flag

WHEN NOT MATCHED THEN

INSERT (

n_dimension_id,

v_stg_table_name,

v_stg_column_name,

v_attribute_name,

v_update_b_code_flag )

VALUES

( s.n_dimension_id,

s.v_stg_table_name,

s.v_stg_column_name,

s.v_attribute_name,

s.v_update_b_code_flag )

/

MERGE INTO rev_dim_attributes_b t

USING (

SELECT

'5005' attribute_id,

'5005' attribute_varchar_label,

(select DIMENSION_ID from rev_dimensions_TL where dimension_name='Country') dimension_id,

'' attribute_dimension_id,

'VARCHAR_ASSIGN_VALUE' attribute_value_column_name,

'VARCHAR2' attribute_data_type_code,

'N' allow_multiple_assignment_flag,

'N' attribute_required_flag,

'N' use_inheritance_flag,

'Y' queryable_for_reporting_flag,

'' default_assignment,

'-1' last_modified_by,

to_timestamp('31-08-2021 18:36:41.000000', 'dd-mm-yyyy hh24:mi:ss.ff') last_modified_date,

'-1' created_by,

to_timestamp('31-08-2021 18:36:41.000000', 'dd-mm-yyyy hh24:mi:ss.ff') creation_date,

'US' definition_language,

'' scale

FROM

dual u

)

s ON ( t.dimension_id = s.dimension_id

AND t.attribute_id = s.attribute_id )

WHEN MATCHED THEN UPDATE

SET t.attribute_varchar_label = s.attribute_varchar_label,

t.attribute_dimension_id = s.attribute_dimension_id,

t.attribute_value_column_name = s.attribute_value_column_name,

t.attribute_data_type_code = s.attribute_data_type_code,

t.allow_multiple_assignment_flag = s.allow_multiple_assignment_flag,

t.attribute_required_flag = s.attribute_required_flag,

t.use_inheritance_flag = s.use_inheritance_flag,

t.queryable_for_reporting_flag = s.queryable_for_reporting_flag,

t.default_assignment = s.default_assignment,

t.last_modified_by = s.last_modified_by,

t.last_modified_date = s.last_modified_date,

t.created_by = s.created_by,

t.creation_date = s.creation_date,

t.definition_language = s.definition_language,

t.scale = s.scale

WHEN NOT MATCHED THEN

INSERT (

attribute_id,

attribute_varchar_label,

dimension_id,

attribute_dimension_id,

attribute_value_column_name,

attribute_data_type_code,

allow_multiple_assignment_flag,

attribute_required_flag,

use_inheritance_flag,

queryable_for_reporting_flag,

default_assignment,

last_modified_by,

last_modified_date,

created_by,

creation_date,

definition_language,

scale )

VALUES

( s.attribute_id,

s.attribute_varchar_label,

s.dimension_id,

s.attribute_dimension_id,

s.attribute_value_column_name,

s.attribute_data_type_code,

s.allow_multiple_assignment_flag,

s.attribute_required_flag,

s.use_inheritance_flag,

s.queryable_for_reporting_flag,

s.default_assignment,

s.last_modified_by,

s.last_modified_date,

s.created_by,

s.creation_date,

s.definition_language,

s.scale )

/

MERGE INTO rev_dim_attributes_tl t

USING (

SELECT

(select DIMENSION_ID from rev_dimensions_TL where dimension_name='Country') dimension_id,

'5005' attribute_id,

'Country' attribute_name,

'Country' description,

'-1' last_modified_by,

to_timestamp('31-08-2021 18:36:41.000000', 'dd-mm-yyyy hh24:mi:ss.ff') last_modified_date,

'-1' created_by,

to_timestamp('31-08-2021 18:36:41.000000', 'dd-mm-yyyy hh24:mi:ss.ff') creation_date,

'US' language,

'' source_lang

FROM

dual u

)

s ON ( t.language = s.language

AND t.dimension_id = s.dimension_id

AND t.attribute_id = s.attribute_id )

WHEN MATCHED THEN UPDATE

SET t.attribute_name = s.attribute_name,

t.description = s.description,

t.last_modified_by = s.last_modified_by,

t.last_modified_date = s.last_modified_date,

t.created_by = s.created_by,

t.creation_date = s.creation_date,

t.source_lang = s.source_lang

WHEN NOT MATCHED THEN

INSERT (

dimension_id,

attribute_id,

attribute_name,

description,

last_modified_by,

last_modified_date,

created_by,

creation_date,

language,

source_lang )

VALUES

( s.dimension_id,

s.attribute_id,

s.attribute_name,

s.description,

s.last_modified_by,

s.last_modified_date,

s.created_by,

s.creation_date,

s.language,

s.source_lang )

/

MERGE INTO fsi_dim_attribute_map t

USING ( SELECT

(select DIMENSION_ID from rev_dimensions_TL where dimension_name='Zone') n_dimension_id,

'STG_ECONOMIC_ZONE_MASTER' v_stg_table_name,

'V_ECONOMIC_ZONE_CODE' v_stg_column_name,

'Zone' v_attribute_name,

'Y' v_update_b_code_flag

FROM

dual u

)

s ON ( t.n_dimension_id = s.n_dimension_id

AND t.v_stg_table_name = s.v_stg_table_name

AND t.v_stg_column_name = s.v_stg_column_name

AND t.v_attribute_name = s.v_attribute_name )

WHEN MATCHED THEN UPDATE

SET t.v_update_b_code_flag = s.v_update_b_code_flag

WHEN NOT MATCHED THEN

INSERT (

n_dimension_id,

v_stg_table_name,

v_stg_column_name,

v_attribute_name,

v_update_b_code_flag )

VALUES

( s.n_dimension_id,

s.v_stg_table_name,

s.v_stg_column_name,

s.v_attribute_name,

s.v_update_b_code_flag )

/

Script 2

create or replace FUNCTION get_dimension_id(dimCode VARCHAR2,userId VARCHAR2,filterOrder NUMBER

) RETURN NUMBER AS

dimId NUMBER DEFAULT 0;

nameOfDimension VARCHAR2(100) DEFAULT 'NA';

mapKey NUMBER DEFAULT -1;

BEGIN

IF dimCode is NULL THEN

RETURN 0;

END IF;

select DIMENSION_MAP_KEY into mapKey from FSI_OILM_CONFIGURATION_DETAILS where user_id=userId and rownum=1;

select DIMENSION_NAME into nameOfDimension from FSI_OILM_CONF_DIMENSION_MAPPING where DIMENSION_MAP_KEY=mapKey AND ORDER_ID=filterOrder and rownum=1;

IF nameOfDimension = 'Legal Entity' THEN

select LEGAL_ENTITY_ID into dimId from DIM_LEGAL_ENTITY_B where LEGAL_ENTITY_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Product' THEN

select PRODUCT_ID into dimId from DIM_PRODUCTS_B where PRODUCT_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Line of Business' THEN

select LOB_ID into dimId from DIM_LOB_B where LOB_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Business Unit' THEN

select BUSINESS_UNIT_ID into dimId from DIM_BUSINESS_UNIT_B where BUSINESS_UNIT_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Sub Product' THEN

select SUBPRODUCT_ID into dimId from FSI_OILM_SUBPRDT_B where SUBPRODUCT_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Coverage' THEN

select COVERAGE_ID into dimId from FSI_OILM_COVERAGE_B where COVERAGE_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Location' THEN

select LOCATION_ID into dimId from DIM_LOCATION_B where LOCATION_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Development' THEN

select DEVELOPMENT_ID into dimId from FSI_OILM_DEVLPMNT_B where DEVELOPMENT_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Loss Type' THEN

select LOSS_TYPE_ID into dimId from FSI_OILM_LOSSTYPE_B where LOSS_TYPE_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Zone' THEN

select ZONE_ID into dimId from FSI_OILM_ZONE_B where ZONE_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Country' THEN

select COUNTRY_ID into dimId from DIM_COUNTRY_B where COUNTRY_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Source or Agent or Broker' THEN

select AGENT_BROKER_ID into dimId from FSI_OILM_AGNTBR_B where AGENT_BROKER_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Claim Manager' THEN

select CLAIM_MANAGER_ID into dimId from FSI_OILM_CLM_MNGR_B where CLAIM_MANAGER_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'UnderWritter' THEN

select UNDERWRITTER_ID into dimId from FSI_OILM_UDR_WRTR_B where UNDERWRITTER_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Segment' THEN

select segment_ID into dimId from Dim_Segment_B where segment_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Primay or Excess Layer' THEN

select PRIMARY_EXCESS_LAYER_ID into dimId from FSI_OILM_PREXSLYR_B where PRIMARY_EXCESS_LAYER_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Co Insuarnce Share' THEN

select COINSRSHAREPCT_ID into dimId from FSI_OILM_COINSSHR_B where COINSRSHAREPCT_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Lead Follower' THEN

select LEAD_FOLLOWER_ID into dimId from FSI_OILM_LEAD_FLWR_B where LEAD_FOLLOWER_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Reinsurance' THEN

select REINSURANCE_ID into dimId from FSI_OILM_REINSRNC_B where REINSURANCE_CODE=dimCode and rownum=1;

ELSIF nameOfDimension = 'Currency' THEN

select CURRENCY_ID into dimId from FSI_OILM_CURRENCY_B where CURRENCY_CODE=dimCode and rownum=1;

END IF;

RETURN dimId;

END get_dimension_id;

/