OSCAR to OSCAR Migration

Joining forces can be exciting, and is a thorough workout of your OSCAR SQL knowledge
Preface

There are several ways to transfer charts.
CMS transfer standard. This works well for individual patients or a family
BUT the standard is incomplete does not include several important parts of the chart such as eForms
Print to pdf and attach the file
Integrator. This allows for pulling data from one OSCAR to another.
Custom SQL merging of data.
Note that these later options are bread and butter for OSCAR Service Providers (OSP's)
The principles of SQL transfer and a partially tested script that transfers only a few elements are described here.
Document Version History

v1.0 – initial public release on oscarmanual.org – Oct 10, 2014
This document is copyright by Peter Hutten-Czapski 2014 © under the Creative Commons Attribution-Share Alike 3.0 Unported License
Contents
Preface
1. Document Version History
Prerequisites
How to convert demographics to OSCAR
Export to Spreadsheet
Cleanup
Load to MySQL
Account for Duplicates
Add the Patients
Admit then to the OSCAR program
Finish
Prerequisites

It is assumed that you or the implementation person(s) will require
EXTENSIVE Knowledge of data structures of OSCAR and their relationships
a thorough understanding of the MySQL command line
a TEST database to test your queries against
A number of days to write test and deploy the scripts
A backup of your OSCAR system that you can revert to!
A Sample Script

Assuming that you have an origin OSCAR 12_1 schema called oscar_elgadi loaded into MySQL on a system that also has a DEB installed oscar_12_1 COPY of a production database to merge it to
NOTE DO NOT DO THIS DIRECTLY TO A PRODUCTION oscar_12_1 without testing or you will be GUARANTEED to be reaching for a backup to get you you back to where you started.
Remember, among other issues, the script below is only a Partial conversion. Many tables have Not been transferred here.
USE oscar_elgadi;

-- Add some columns for reference
ALTER TABLE oscar_elgadi.`demographic` ADD `new_demo_no` INT( 10 ) NOT NULL COMMENT 'matches demographic_no in oscar_12_1' AFTER `demographic_no`;
ALTER TABLE oscar_elgadi.`demographic` ADD `merged` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'indicates if matching demographic existed in OSCAR Haileybury' AFTER `new_demo_no`;

-- add in site KME to E  

INSERT INTO oscar_12_1.`site`(`name`, `short_name`, `phone`, `fax`, `bg_color`, `address`, `city`, `province`, `postal`, `status`, `providerId_from`, `providerId_to`) 
SELECT "surgery clinic", ed.`short_name`, ed.`phone`, ed.`fax`, ed.`bg_color`, ed.`address`, ed.`city`, ed.`province`, ed.`postal`, ed.`status`, ed.`providerId_from`, ed.`providerId_to`
FROM oscar_elgadi.site ed
WHERE ed.name="KME";

-- first add in the providers making sure you do not overwrite ours  DECISION all of them ? yes

INSERT INTO oscar_12_1.`provider`(`provider_no`, `last_name`, `first_name`, `provider_type`, `specialty`, `team`, `sex`, `dob`, `address`, `phone`, `work_phone`, `ohip_no`, `rma_no`, `billing_no`, `hso_no`, `status`, `comments`, `provider_activity`, `practitionerNo`, `init`, `job_title`, `email`, `title`, `lastUpdateUser`, `lastUpdateDate`, `signed_confidentiality`)
SELECT ed.`provider_no`, ed.`last_name`, ed.`first_name`, ed.`provider_type`, ed.`specialty`, ed.`team`, ed.`sex`, ed.`dob`, ed.`address`, ed.`phone`, ed.`work_phone`, ed.`ohip_no`, ed.`rma_no`, ed.`billing_no`, ed.`hso_no`, ed.`status`, ed.`comments`, ed.`provider_activity`, ed.`practitionerNo`, ed.`init`, ed.`job_title`, ed.`email`, ed.`title`, ed.`lastUpdateUser`, ed.`lastUpdateDate`, ed.`signed_confidentiality`
FROM oscar_elgadi.provider ed
ON DUPLICATE KEY UPDATE last_name=ed.last_name;

-- 11 rows

INSERT INTO oscar_12_1.`program_provider`(`program_id`, `provider_no`, `role_id`, `team_id`) 
SELECT  ed.`program_id`, ed.`provider_no`, ed.`role_id`, ed.`team_id`
FROM oscar_elgadi.`program_provider ed`;

-- 10 rows

INSERT INTO oscar_12_1.`secUserRole`(`provider_no`, `role_name`, `orgcd`, `activeyn`, `lastUpdateDate`) 
SELECT  ed.`provider_no`, ed.`role_name`, ed.`orgcd`, ed.`activeyn`, ed.`lastUpdateDate`
FROM oscar_elgadi.`secUserRole`;

-- copy over the oscar_12_1 demographic_no to elgadi new_demo_no where the hin match and are not blank
UPDATE oscar_elgadi.demographic de, oscar_12_1.demographic d SET de.new_demo_no = d.demographic_no, de.merged=TRUE 
WHERE de.hin = d.hin AND de.`hin` <> '';

-- 2135 rows

-- to transfer the 30 or so that have a blank hin you should try to match those by name and DOB
UPDATE oscar_elgadi.demographic de, oscar_12_1.demographic d SET de.new_demo_no = d.demographic_no, de.merged=TRUE 
WHERE de.`hin` = '' AND d.last_name=de.last_name AND d.first_name=de.first_name AND d.year_of_birth=de.year_of_birth AND d.month_of_birth=de.month_of_birth AND d.date_of_birth=de.date_of_birth;

-- 7 rows

-- update TODO check if there are other differences in his with common pts
UPDATE oscar_12_1.`demographic` d, oscar_elgadi.demographic ed SET d.`family_doctor`=ed.`family_doctor`
WHERE ed.last_name = d.last_name and ed.first_name = d.first_name and ed.year_of_birth=d.year_of_birth and ed.hin = d.hin
AND d.`family_doctor`="<rdohip></rdohip><rd></rd>";


-- 1293 rows affected. ( Query took 0.2034 sec )


-- copy over all the demographics from elgadi where there is no match TODO the 30 for the hin is ''  TODO check if there are differences in his with common pts
INSERT INTO oscar_12_1.`demographic`( `title`, `last_name`, `first_name`, `address`, `city`, `province`, `postal`, `phone`, `phone2`, `email`, `myOscarUserName`, `year_of_birth`, `month_of_birth`, `date_of_birth`, `hin`, `ver`, `roster_status`, `patient_status`, `date_joined`, `chart_no`, `official_lang`, `spoken_lang`, `provider_no`, `sex`, `end_date`, `eff_date`, `pcn_indicator`, `hc_type`, `hc_renew_date`, `family_doctor`, `alias`, `previousAddress`, `children`, `sourceOfIncome`, `citizenship`, `sin`, `country_of_origin`, `newsletter`, `anonymous`, `roster_date`, `lastUpdateUser`, `lastUpdateDate`, `patient_status_date`, `roster_termination_date`, `roster_termination_reason`)
SELECT ed.`title`, ed.`last_name`, ed.`first_name`, ed.`address`, ed.`city`, ed.`province`, ed.`postal`, ed.`phone`, ed.`phone2`, ed.`email`, ed.`myOscarUserName`, ed.`year_of_birth`, ed.`month_of_birth`, ed.`date_of_birth`, ed.`hin`, ed.`ver`, ed.`roster_status`, ed.`patient_status`, ed.`date_joined`, ed.`chart_no`, ed.`official_lang`, ed.`spoken_lang`, ed.`provider_no`, ed.`sex`, ed.`end_date`, ed.`eff_date`, ed.`pcn_indicator`, ed.`hc_type`, ed.`hc_renew_date`, ed.`family_doctor`, ed.`alias`, ed.`previousAddress`, ed.`children`, ed.`sourceOfIncome`, ed.`citizenship`, ed.`sin`, ed.`country_of_origin`, ed.`newsletter`, ed.`anonymous`, ed.`roster_date`, ed.`lastUpdateUser`, ed.`lastUpdateDate`, ed.`patient_status_date`, ed.`roster_termination_date`, ed.`roster_termination_reason`
FROM oscar_elgadi.demographic ed
WHERE ed.merged=FALSE;

-- 2850 rows inserted. Inserted row id: 15448 ( Query took 0.1360 sec )

-- now the cell numbers and such in the ext table
INSERT INTO oscar_12_1.`demographicExt`(`demographic_no`, `provider_no`, `key_val`, `value`, `date_time`, `hidden`) 
SELECT ed.`new_demo_no`, ex.`provider_no`, ex.`key_val`, ex.`value`, ex.`date_time`, ex.`hidden`
FROM  oscar_elgadi.demographicExt ex, oscar_elgadi.demographic ed
WHERE ex.demographic_no=ed.demographic_no;

-- 2179 rows inserted. Inserted row id: 15267 ( Query took 0.0952 sec )

-- now notes and alerts, fail on Dup Key
INSERT INTO  oscar_12_1.`demographiccust`(`demographic_no`, `cust1`, `cust2`, `cust3`, `cust4`, `content`) 
SELECT ed.`new_demo_no`, ec.`cust1`, ec.`cust2`, ec.`cust3`, ec.`cust4`, ec.`content`
FROM  oscar_elgadi.demographiccust ec, oscar_elgadi.demographic ed
WHERE ec.demographic_no=ed.demographic_no
ON DUPLICATE KEY UPDATE cust4=oscar_12_1.`demographiccust`.cust4 ;

-- admit the new demographics
INSERT INTO oscar_12_1.`admission`(client_id,program_id,provider_no,admission_date,admission_status,team_id,temporary_admission_flag,lastUpdateDate)  
SELECT d.demographic_no,'10016',ed.provider_no,'2014-10-01','current',0,0,'2014-10-01 00:00:00'
FROM oscar_elgadi.demographic ed, oscar_12_1.`demographic` d
WHERE ed.merged=FALSE AND ed.last_name = d.last_name and ed.first_name = d.first_name and ed.year_of_birth=d.year_of_birth and ed.hin = d.hin;

-- 2850 rows inserted. Inserted row id: 15445 ( Query took 0.1749 sec )

-- now can add in the other cases
UPDATE oscar_elgadi.demographic ed, oscar_12_1.demographic d SET ed.new_demo_no = d.demographic_no WHERE ed.merged=FALSE AND ed.last_name = d.last_name and ed.first_name = d.first_name and ed.year_of_birth=d.year_of_birth and ed.hin = d.hin;

-- 2850 rows affected. ( Query took 0.0942 sec )


-- now with all cases cross referenced we can copy over tables

-- appointment... well those from 2014 on  hard code them to Temiskaming Hospital location

INSERT INTO oscar_12_1.`appointment`(`provider_no`, `appointment_date`, `start_time`, `end_time`, `name`, `demographic_no`, `program_id`, `notes`, `reason`, `location`, `resources`, `type`, `style`, `billing`, `status`, `createdatetime`, `updatedatetime`, `creator`, `lastupdateuser`, `remarks`, `urgency`, `imported_status`, `creatorSecurityId`, `bookingSource`)
SELECT ed.`provider_no`, ed.`appointment_date`, ed.`start_time`, ed.`end_time`, ed.`name`, ea.new_demo_no, ed.`program_id`, ed.`notes`, ed.`reason`, "surgery clinic", ed.`resources`, ed.`type`, ed.`style`, ed.`billing`, ed.`status`, ed.`createdatetime`, ed.`updatedatetime`, ed.`creator`, ed.`lastupdateuser`, ed.`remarks`, ed.`urgency`, ed.`imported_status`, ed.`creatorSecurityId`, ed.`bookingSource`
FROM oscar_elgadi.demographic ea, oscar_elgadi.appointment ed
WHERE ed.demographic_no =ea.demographic_no; 

-- 4249 rows inserted. ( Query took 3.4170 sec ) for 2014 and newer
-- 11642 rows inserted. Inserted row id: 265965 ( Query took 1.2880 sec ) for all

-- add in all the notes from the other oscar  
INSERT INTO oscar_12_1.casemgmt_note( `update_date`, `observation_date`, `demographic_no`, `provider_no`, `note`, `signed`, `include_issue_innote`, `signing_provider_no`, `encounter_type`, `billing_code`, `program_no`, `reporter_caisi_role`, `reporter_program_team`, `history`, `password`, `locked`, `archived`, `position`, `uuid`, `appointmentNo`, `hourOfEncounterTime`, `minuteOfEncounterTime`, `hourOfEncTransportationTime`, `minuteOfEncTransportationTime`) SELECT  ec.`update_date`, ec.`observation_date`, ed.new_demo_no, ec.`provider_no`, ec.`note`, ec.`signed`, ec.`include_issue_innote`, ec.`signing_provider_no`, ec.`encounter_type`, ec.`billing_code`, ec.`program_no`, ec.`reporter_caisi_role`, ec.`reporter_program_team`, ec.`history`, ec.`password`, ec.`locked`, ec.`archived`, ec.`position`, ec.`uuid`, ec.`appointmentNo`, ec.`hourOfEncounterTime`, ec.`minuteOfEncounterTime`, ec.`hourOfEncTransportationTime`, ec.`minuteOfEncTransportationTime`
FROM oscar_elgadi.casemgmt_note ec, oscar_elgadi.demographic ed
WHERE ec.demographic_no = ed.demographic_no 
AND ed.new_demo_no >0;

-- 17453 rows inserted. 5sec  row count is ok
-- 17532 rows inserted. 30.3205 sec

-- eforms
INSERT INTO oscar_12_1.`eform_data`( `fid`, `form_name`, `subject`, `demographic_no`, `status`, `form_date`, `form_time`, `form_provider`, `form_data`, `patient_independent`, `roleType`)
SELECT 
ed.`fid`, ed.`form_name`, ed.`subject`, edemo.new_demo_no, ed.`status`, ed.`form_date`, ed.`form_time`, ed.`form_provider`, ed.`form_data`, ed.`patient_independent`, ed.`roleType`
FROM oscar_elgadi.eform_data ed , oscar_elgadi.demographic edemo
WHERE edemo.demographic_no=ed.demographic_no;

-- in this transfer there is only one unique eform, CONSULT 
INSERT INTO oscar_12_1.`eform`(`fid`, `form_name`, `file_name`, `subject`, `form_date`, `form_time`, `form_creator`, `status`, `form_html`, `patient_independent`, `roleType`) 
SELECT `fid`, `form_name`, `file_name`, `subject`, `form_date`, `form_time`, `form_creator`, `status`, `form_html`, `patient_independent`, `roleType`
FROM oscar_elgadi.`eform` ee
WHERE ee.`form_name`="CONSULT";

SET @consult_id = LAST_INSERT_ID();

-- specific for this migration set the correct fid where form_name=CONSULT
UPDATE oscar_12_1.`eform_data` SET `fid`=@consult_id WHERE `form_name`="CONSULT";

-- 11543 rows inserted. Inserted row id: 52211 ( Query took 173.2314 sec )
-- 17532 rows inserted. Inserted row id: 503073 ( Query took 5.9798 sec )

ALTER TABLE oscar_elgadi.`document` ADD `new_doc_no` INT( 10 ) NOT NULL COMMENT 'matches document_no in oscar_12_1' AFTER `document_no`;
ALTER TABLE oscar_elgadi.`document` ADD `new_demo_no` INT( 10 ) NOT NULL COMMENT 'matches demographic_no in oscar_12_1' AFTER `document_no`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `doDOCS` $$
CREATE PROCEDURE `doDOCS`()
BEGIN
	DECLARE v_document_no INT(20);
	DECLARE v_doctype VARCHAR(60);
	DECLARE v_docdesc VARCHAR(255);
	DECLARE v_docxml TEXT;
	DECLARE v_docfilename VARCHAR(255);
	DECLARE v_doccreator VARCHAR(30);
	DECLARE v_responsible VARCHAR(30);
	DECLARE v_source VARCHAR(60);
	DECLARE v_program_id INT(11);
 	DECLARE v_updatedatetime DATETIME;
	DECLARE v_status CHAR(1);
	DECLARE v_contenttype VARCHAR(60);
	DECLARE v_public1 INT(1);
 	DECLARE v_observationdate DATE;
	DECLARE v_reviewer VARCHAR(30);
 	DECLARE v_reviewdatetime DATETIME;
	DECLARE v_number_of_pages INT(6);
	DECLARE v_docClass VARCHAR(60);
	DECLARE v_docSubClass VARCHAR(60);
	DECLARE v_appointment_no INT(11);
	DECLARE v_sourceFacility VARCHAR(120);
	DECLARE done INT DEFAULT FALSE;
	DECLARE cur1 CURSOR FOR SELECT `document_no`, `doctype`, `docdesc`, `docxml`, `docfilename`, `doccreator`, `responsible`, `source`, `program_id`, `updatedatetime`, `status`, `contenttype`, `public1`, `observationdate`, `reviewer`, `reviewdatetime`, `number_of_pages`, `docClass`, `docSubClass`, `appointment_no`, `sourceFacility`
	FROM oscar_elgadi.`document`;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	OPEN cur1;
	the_loop: LOOP
		FETCH cur1 INTO v_document_no, v_doctype, v_docdesc, v_docxml, v_docfilename, v_doccreator, v_responsible, v_source, v_program_id, v_updatedatetime, v_status, v_contenttype, v_public1, v_observationdate, v_reviewer, v_reviewdatetime, v_number_of_pages, v_docClass, v_docSubClass, v_appointment_no, v_sourceFacility;
		IF done THEN
		      LEAVE the_loop;
		END IF;
		#Do some post processing logic here if you want such as a duplicate check

		INSERT INTO oscar_12_1.`document`( `doctype`, `docdesc`, `docxml`, `docfilename`, `doccreator`, `responsible`, `source`, `program_id`, `updatedatetime`, `status`, `contenttype`, `public1`, `observationdate`, `reviewer`, `reviewdatetime`, `number_of_pages`, `docClass`, `docSubClass`, `appointment_no`, `sourceFacility`) VALUES ( v_doctype, v_docdesc, v_docxml, CONCAT('../elgadi/', v_docfilename), v_doccreator, v_responsible, v_source, v_program_id, v_updatedatetime, v_status, v_contenttype, v_public1, v_observationdate, v_reviewer, v_reviewdatetime, v_number_of_pages, v_docClass, v_docSubClass, v_appointment_no, v_sourceFacility);
		
		SET @new_document_no = LAST_INSERT_ID();
		UPDATE oscar_elgadi.document de SET de.new_doc_no = @new_document_no
		WHERE de.document_no= v_document_no;

		INSERT INTO oscar_12_1.`ctl_document`(`module`, `module_id`, `document_no`, `status`)
		SELECT ec.`module`, ed.`new_demo_no`, @new_document_no, ec.`status`
		FROM oscar_elgadi.`ctl_document` ec, oscar_elgadi.`demographic` ed
		WHERE ec.`module`="demographic" AND ed.demographic_no = ec.module_id AND ec.`document_no`=v_document_no;

		INSERT INTO oscar_12_1.`casemgmt_note_link`( `table_name`, `table_id`, `note_id`, `other_id`) 
		SELECT "5", @new_document_no ,ono.note_id , NULL
		FROM oscar_elgadi.`casemgmt_note_link` el, oscar_elgadi.casemgmt_note en, oscar_12_1.casemgmt_note ono
		WHERE el.`table_id`=v_document_no
		AND el.note_id=en.note_id
		AND ono.update_date = en.update_date
		AND ono.observation_date = en.observation_date;

	END LOOP the_loop;
	CLOSE cur1;
END $$

DELIMITER ;

CALL doDOCS();

#/*
-- TODO add in ? a subdirectory prefix  CONCAT("../elgadi/", ed.`docfilename`)
INSERT INTO oscar_12_1.`document`(`doctype`, `docdesc`, `docxml`, `docfilename`, `doccreator`, `responsible`, `source`, `program_id`, `updatedatetime`, `status`, `contenttype`, `public1`, `observationdate`, `reviewer`, `reviewdatetime`, `number_of_pages`, `docClass`, `docSubClass`, `appointment_no`, `sourceFacility`) 
SELECT
ed.`doctype`, ed.`docdesc`, ed.`docxml`, CONCAT("../elgadi/", ed.`docfilename`), ed.`doccreator`, ed.`responsible`, ed.`source`, ed.`program_id`, ed.`updatedatetime`, ed.`status`, ed.`contenttype`, ed.`public1`, ed.`observationdate`, ed.`reviewer`, ed.`reviewdatetime`, ed.`number_of_pages`, ed.`docClass`, ed.`docSubClass`, ed.`appointment_no`, ed.`sourceFacility`
FROM oscar_elgadi.document ed ;

-- 16468 rows inserted.  Inserted row id: 84872 ( Query took 0.5104 sec )

UPDATE oscar_elgadi.document de, oscar_12_1.document d SET de.new_doc_no = d.`document_no`
WHERE de.docfilename= d.docfilename;

-- 16468 rows affected. ( Query took 439.6069 sec )  TO FIX!!!
-- 16468 rows affected. ( Query took 405.6307 sec )

UPDATE oscar_elgadi.ctl_document dc, oscar_elgadi.demographic de,  oscar_elgadi.document dd 
SET dd.new_demo_no = de.new_demo_no 
WHERE dc.module="demographic" AND dc.document_no= dd.document_no AND de.demographic_no=dc.module_id;

-- 16393 rows affected. ( Query took 0.2657 sec )


CREATE TABLE `oscar_elgadi`.`ctl_documenttemp` (
`module` varchar( 30 ) NOT NULL DEFAULT '',
`module_id` int( 6 ) NOT NULL DEFAULT '0',
`document_no` int( 6 ) NOT NULL DEFAULT '0',
`status` char( 1 ) DEFAULT NULL ,
KEY `module` ( `module` ) ,
KEY `document_no` ( `document_no` )
);

INSERT INTO oscar_elgadi.`ctl_documenttemp`(`module`, `module_id`, `document_no`, `status`)
SELECT "demographic", dd.`new_demo_no`, dd.`new_doc_no`, cd.`status`
FROM oscar_elgadi.`ctl_document` cd, oscar_elgadi.`document` dd
WHERE dd.document_no=cd.document_no and cd.module="demographic";

-- 16469 rows inserted. ( Query took 0.1697 sec )


INSERT INTO oscar_12_1.`ctl_document`(`module`, `module_id`, `document_no`, `status`)
SELECT *
FROM oscar_elgadi.`ctl_documenttemp` oe
ON DUPLICATE KEY UPDATE status=oe.status;

INSERT INTO oscar_12_1.`casemgmt_note_link`( `table_name`, `table_id`, `note_id`, `other_id`) 
SELECT "5", ed.new_doc_no ,ono.note_id , NULL
FROM oscar_elgadi.`casemgmt_note_link` el, oscar_elgadi.`document` ed , oscar_elgadi.casemgmt_note en, oscar_12_1.casemgmt_note ono
WHERE ono.update_date = en.update_date
AND el.note_id=en.note_id
AND ed.document_no=el.table_id;

-- 16589 rows inserted. Inserted row id: 87373 ( Query took 515.9762 sec )

*/#

DELIMITER $$

DROP PROCEDURE IF EXISTS `doHL7` $$
CREATE PROCEDURE `doHL7`()
BEGIN
	DECLARE v_lab_id INT(10);
	DECLARE v_fileUploadCheck_id INT(10);
	DECLARE v_message LONGTEXT;
	DECLARE v_type VARCHAR(100);
	DECLARE v_serviceName VARCHAR(100);
	DECLARE v_created DATETIME;
	DECLARE done INT DEFAULT FALSE;
	DECLARE cur1 CURSOR FOR SELECT `lab_id`, `fileUploadCheck_id`, `message`, `type`, `serviceName`, `created`
	FROM oscar_elgadi.`hl7TextMessage`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	OPEN cur1;
	the_loop: LOOP
		FETCH cur1 INTO v_lab_id, v_fileUploadCheck_id, v_message, v_type, v_serviceName, v_created;
		IF done THEN
		      LEAVE the_loop;
		END IF;
		#Do some post processing logic here if you want such as a duplicate check

		INSERT INTO oscar_12_1.`hl7TextMessage` (`fileUploadCheck_id`, `message`, `type`, `serviceName`, `created`)
		VALUES (v_fileUploadCheck_id, v_message, v_type, v_serviceName, v_created);

		SET @laboratory_id = LAST_INSERT_ID();

		INSERT INTO oscar_12_1.`hl7TextInfo` (`lab_no`, `sex`, `health_no`, `result_status`, `final_result_count`, `obr_date`, `priority`, `requesting_client`, `discipline`, `last_name`, `first_name`, `report_status`, `accessionNum`) 
		SELECT @laboratory_id, si.`sex`, si.`health_no`, si.`result_status`, si.`final_result_count`, si.`obr_date`, si.`priority`, si.`requesting_client`, si.`discipline`, si.`last_name`, si.`first_name`, si.`report_status`, si.`accessionNum` 
		FROM oscar_elgadi.`hl7TextInfo` si WHERE si.`lab_no`=v_lab_id;

		INSERT INTO oscar_12_1.`providerLabRouting` (`provider_no`, `lab_no`, `status`, `comment`, `timestamp`, `lab_type`) 
		SELECT spr.`provider_no`, @laboratory_id, spr.`status`, spr.`comment`, spr.`timestamp`, spr.`lab_type`
		FROM oscar_elgadi.`providerLabRouting` spr
		WHERE spr.`lab_no`=v_lab_id ;

		INSERT INTO oscar_12_1.`patientLabRouting` (`demographic_no`, `lab_no`, `lab_type`, `created`) 
		SELECT sd.`new_demo_no`, @laboratory_id, spat.`lab_type`, spat.`created`
		FROM oscar_elgadi.`patientLabRouting` spat, oscar_elgadi.`demographic` sd
		WHERE spat.`demographic_no`= sd.`demographic_no` AND spat.`lab_no`=v_lab_id ;

	END LOOP the_loop;
	CLOSE cur1;
END $$

DELIMITER ;

CALL doHL7();


--  secUserrole

INSERT INTO oscar_12_1.`secUserRole`(`id`, `provider_no`, `role_name`, `orgcd`, `activeyn`, `lastUpdateDate`) 
SELECT el.`provider_no`, el.`role_name`, el.`orgcd`, el.`activeyn`, el.`lastUpdateDate`
FROM oscar_elgadi.`secUserRole` el;


-- ProviderPreference

INSERT INTO oscar_12_1.`ProviderPreference`(`providerNo`, `startHour`, `endHour`, `everyMin`, `myGroupNo`, `colourTemplate`, `newTicklerWarningWindow`, `defaultServiceType`, `defaultCaisiPmm`, `defaultNewOscarCme`, `printQrCodeOnPrescriptions`, `lastUpdated`, `appointmentScreenLinkNameDisplayLength`, `defaultDoNotDeleteBilling`, `defaultDxCode`, `eRxEnabled`, `eRx_SSO_URL`, `eRxUsername`, `eRxPassword`, `eRxFacility`, `eRxTrainingMode`, `encryptedMyOscarPassword`) 
SELECT ep.`providerNo`, ep.`startHour`, ep.`endHour`, ep.`everyMin`, ep.`myGroupNo`, ep.`colourTemplate`, ep.`newTicklerWarningWindow`, ep.`defaultServiceType`, ep.`defaultCaisiPmm`, ep.`defaultNewOscarCme`, ep.`printQrCodeOnPrescriptions`, ep.`lastUpdated`, ep.`appointmentScreenLinkNameDisplayLength`, ep.`defaultDoNotDeleteBilling`, ep.`defaultDxCode`, ep.`eRxEnabled`, ep.`eRx_SSO_URL`, ep.`eRxUsername`, ep.`eRxPassword`, ep.`eRxFacility`, ep.`eRxTrainingMode`, ep.`encryptedMyOscarPassword`
FROM oscar_elgadi.`ProviderPreference` ep;

-- providersite 5 and 6 hardcoded for this transfer
INSERT INTO oscar_12_1.`providersite`(`provider_no`, `site_id`) 
SELECT ep.`provider_no`, 5
FROM oscar_elgadi.`provider` ep;

INSERT INTO oscar_12_1.`providersite`(`provider_no`, `site_id`) 
SELECT ep.`provider_no`, 6
FROM oscar_elgadi.`provider` ep;

-- transfer over selected custom billing templates
INSERT INTO oscar_12_1.`ctl_billingservice`(`servicetype_name`, `servicetype`, `service_code`, `service_group_name`, `service_group`, `status`, `service_order`)
SELECT `servicetype_name`, `servicetype`, `service_code`, `service_group_name`, `service_group`, `status`, `service_order`
FROM oscar_elgadi.ctl_billingservice ed
WHERE ed.servicetype IN ('GS2','GSG');

INSERT INTO oscar_12_1.`ctl_billingtype`(`servicetype`, `billtype`)
SELECT `servicetype`, `billtype`
FROM oscar_elgadi.ctl_billingtype ed
WHERE ed.servicetype IN ('GS2','GSG');