1. Installation Guide
  2. Upgrade
  3. Data Model Upgrade
  4. Data Migration

Data Migration

To upgrade from a previous Oracle Healthcare Foundation version or to migrate the data from Healthcare Data Warehouse Foundation 6.1, follow the steps below:

Parent topic: Data Model Upgrade

Check the Migration Scripts

Data migration for interface tables and data warehouse schema is not performed by the installer. You need to review the data migration scripts in INSTALL_HOME/dm/hdi_install/post_ddl_upgrade.sql and INSTALL_HOME/dm/hdm_install/post_ddl_upgrade.sql (note that there are references to other scripts within the main script). You can modify the scripts before executing them.

Note:

Migration scripts execution is mandatory and needs to be completed before the ETL execution.

Note:

Both sections below (Upgrading from Oracle Healthcare Foundation versions earlier than 7.0.1 and Upgrading from an Oracle Healthcare Foundation Version before 7.1) are applicable for migrating from Healthcare Data Warehouse 6.1 to Oracle Healthcare Foundation 8.2.2.

Parent topic: Data Migration

Upgrading from Oracle Healthcare Foundation versions earlier than 7.0.1

Note:

Not Applicable when upgrading from Oracle Healthcare Foundation 7.1.
  • Specimen.Intervention ID/VN is deprecated and data is migrated to Intervention Specimen.
  • Specimen Processing Procedure. Specimen Processing Procedure Subtype is added as a mandatory attribute to the Specimen Processing Procedure. This is initialized with the appropriate seed data coded value 'SPP_SUBTYP_GEN' during the migration.
  • Schedule Block.Schedule Block Subtype is added as a mandatory attribute to Schedule Block. This is initialized with the appropriate seed data coded value 'SCH_BLCK_SUBTYP_GEN' during the migration.
  • Schedule.Service Provider is deprecated and data is transferred to Schedule Service Provider.
  • Observation. Specimen ID/VN is deprecated and data is transferred to Observation Specimen. On the interface tables schema, data is transferred from Observation, Assessment, and Image Instance to Observation Specimen.
  • Data is migrated from Encounter Service Provider to Encounter. Responsible Service Provider for Encounter Service Provider Relationship Type="Responsible Service Provider". If the same encounter is associated to multiple service providers for a given value of Encounter and Relationship Type="Responsible Service Provider", the service provider that was inserted last is migrated to Encounter.
  • The integration ID of the parent table is used wherever a new table is populated based on an existing table.
  • The migration scripts use the seed data values given below provided by Oracle. If you have used a different set of seed data values in Encounter Service Provider to represent a Responsible Service Provider relationship, change it in the migration script for both Interface Tables and Data Warehouse schema, and run post_ddl_upgrade.sql.
  • The following are the seed data values that are used for filtering Encounter Service Provider. Encounter Service Provider Relationship Type in the migration scripts:
    • HDI_ENC_SVCPRV.ENC_SVCPRV_RLTYP_CD='ENC_SVCPRV_RLSHPTYP_RSPNSBL_SVCPRV' (CD_NM='Responsible Service Provider' AND INTEGRATION_ID='ENC_SVCPRV_RLSHPTYP_RSPNSBL_SVCPRV~EHA_CUSTOM_CD_SYS~1.0' AND SYS_INT_ID='EHA_CUSTOM_CD_SYS~1.0' AND SYS_DAT_SRC_NUM_ID=1 AND CREATED_BY_USER_ID='EHA_USER' AND CREATED_BY_USER_DS_NUM_ID=1)
    • On the Data Warehouse schema, it corresponds to HDM_ENC_SVCPRV.ENC_SVCPRV_RLSHPTYP_ID/VN resolving to the following code:

      CD='ENC_SVCPRV_RLSHPTYP_RSPNSBL_SVCPRV' AND CD_NM='Responsible Service Provider' AND INTEGRATION_ID='ENC_SVCPRV_RLSHPTYP_RSPNSBL_SVCPRV~EHA_CUSTOM_CD_SYS~1.0'

    The values mentioned above are presented in a logical way and are not the exact physical representation of the values present in the migration scripts. See sql files INSTALL_HOME/dm/hdi_install/pkb/hdi_upgrade_7_0.pkb and INSTALL_HOME/dm/hdm_install/pkb/hdm_upgrade_7_0.pkb for the exact seed data values.

  • Key steps related to data migration are logged into the HDI_RSLT_LOG and HDM_RSLT_LOG tables in the respective schemas.

Parent topic: Check the Migration Scripts

Upgrading from an Oracle Healthcare Foundation Version before 7.1

Note:

Not applicable when upgrading from Oracle Healthcare Foundation 7.1.
  • Bill Line Item.Bill Diagnosis ID/VN is deprecated and the corresponding data is migrated to Bill Diagnosis as new records.
  • Claim Line Item.ClaimDiagnosis ID/VN is deprecated and the corresponding data is migrated to Claim Diagnosis as new records.

    The values mentioned above are presented in a logical way and are not the exact physical representation of the values in the migration scripts. For exact details, see the SQL files:

    INSTALL_HOME/dm/hdi_install/pkb/hdi_upgrade_7_1.pkb and INSTALL_HOME/dm/hdm_install/pkb/hdm_upgrade_7_1.pkb.

  • HDM_INTVN_SPCMN. INTVN_SPCMN_RLSHPTYP_ID/VN is populated with the value -1 corresponding to the default value of CODEID_NAV configured in the HMC schema. If you have modified the seed data, change the script INSTALL_HOME/dm/hdm_install/post_ddl_upgrade.sql accordingly.
  • Review the integration ID pattern for records populated using the migration scripts and change it accordingly if needed.
  • It is assumed that the time stamps for SRC_CHANGED_ON_DT, match from the parent to the child reference on the interface schema. For example, the value of HDI_ENC_SVCPRV. ENC_SRC_CHANGED_ON_DT should be the same as that of the parent. That is, HDI_ENC.SRC_CHANGED_ON_DT for the same record being referred in both tables right up to the seconds. If it is not the same, change the migration script accordingly.

Parent topic: Check the Migration Scripts

Upgrading from Oracle Healthcare Foundation Versions Earlier than 8.0

  • Bill Line Item.Service Provider ID/VN is deprecated and the corresponding data is migrated to Bill Line Item Service Provider as new records.

    The values mentioned above are presented in a logical way and are not the exact physical representation of the values in the migration scripts. For exact details, see the SQL files: <INSTALL_HOME>/dm/hdi_install/pkb/hdi_upgrade_8_0.pkb and <INSTALL_ HOME>/dm/hdm_install/pkb/hdm_upgrade_8_0.pkb.

  • Review the integration ID pattern for records populated using the migration scripts and change it accordingly if needed.

  • It is assumed that the time stamps for SRC_CHANGED_ON_DT match from the parent to the child reference on the interface schema. For example, the value of HDI_ENC_SVCPRV. ENC_SRC_CHANGED_ON_DT should be same as that of the parent, that is, HDI_ENC.SRC_CHANGED_ON_DT for the same record being referred in both tables right up to the seconds. If it is not the same, change the migration script accordingly.

Parent topic: Check the Migration Scripts

Execute Schema Migration Scripts After Modifications

Follow the instructions below to migrate the Interface Tables schema:

- Steps

Checkbox

Open the command prompt and navigate to the INSTALL_HOME/dm/hdi_install/ folder.

Checkbox

Connect to the interface tables schema using the SQL*Plus utility.

Checkbox

Execute the script using the command '@post_ddl_upgrade.sql'.

Follow the instructions below to migrate the Data Warehouse schema:

- Steps

Checkbox

Open the command prompt and navigate to the INSTALL_HOME/dm/hdm_install/ folder.

Checkbox

Connect to the Data Warehouse schema using the SQL*Plus utility.

Checkbox

Execute the script using the command '@post_ddl_upgrade.sql'.

Parent topic: Data Migration