--
-- Type: PROCEDURE; Owner: I2B2DEMODATA; Name: INSERT_PATIENT_MAP_FROMTEMP
--
  CREATE OR REPLACE PROCEDURE "I2B2DEMODATA"."INSERT_PATIENT_MAP_FROMTEMP" (tempPatientTableName IN VARCHAR,  upload_id IN NUMBER,
   errorMsg OUT VARCHAR )
IS
BEGIN

	--Create new patient mapping entry for HIVE patient's if they are not already mapped in mapping table
	execute immediate 'insert into patient_mapping (
		select distinct temp.patient_id, temp.patient_id_source,''A'',temp.patient_id ,' || upload_id || '
		from ' || tempPatientTableName ||'  temp
		where temp.patient_id_source = ''HIVE'' and
   		not exists (select patient_ide from patient_mapping pm where pm.patient_num = temp.patient_id and pm.patient_ide_source = temp.patient_id_source)
		)';

    --Create new visit for above inserted encounter's
	--If Visit table's encounter and patient num does match temp table,
	--then new visit information is created.
	execute immediate 'MERGE  INTO patient_dimension pd
		   USING ( select case when (ptemp.patient_id_source=''HIVE'') then to_number(ptemp.patient_id)
                                       else pmap.patient_num end patient_num,
                                  ptemp.VITAL_STATUS_CD,
                                  ptemp.BIRTH_DATE,
                                  ptemp.DEATH_DATE,
                                  ptemp.SEX_CD ,
                                  ptemp.AGE_IN_YEARS_NUM,
                                  ptemp.LANGUAGE_CD,
                                  ptemp.RACE_CD,
                                  ptemp.MARITAL_STATUS_CD,
                                  ptemp.RELIGION_CD,
                                  ptemp.ZIP_CD,
								  ptemp.STATECITYZIP_PATH ,
								  ptemp.PATIENT_BLOB,
								  ptemp.UPDATE_DATE,
								  ptemp.DOWNLOAD_DATE,
								  ptemp.IMPORT_DATE,
								  ptemp.SOURCESYSTEM_CD

                   from ' || tempPatientTableName || '  ptemp , patient_mapping pmap
                   where   ptemp.patient_id = pmap.patient_ide(+)
                   and ptemp.patient_id_source = pmap.patient_ide_source(+)
           ) temp
		   on (
				pd.patient_num = temp.patient_num
		    )
			when matched then
			 	update  set
			 		pd.VITAL_STATUS_CD= temp.VITAL_STATUS_CD,
                    pd.BIRTH_DATE= temp.BIRTH_DATE,
                    pd.DEATH_DATE= temp.DEATH_DATE,
                    pd.SEX_CD= temp.SEX_CD,
                    pd.AGE_IN_YEARS_NUM=temp.AGE_IN_YEARS_NUM,
                    pd.LANGUAGE_CD=temp.LANGUAGE_CD,
                    pd.RACE_CD=temp.RACE_CD,
                    pd.MARITAL_STATUS_CD=temp.MARITAL_STATUS_CD,
                    pd.RELIGION_CD=temp.RELIGION_CD,
                    pd.ZIP_CD=temp.ZIP_CD,
					pd.STATECITYZIP_PATH =temp.STATECITYZIP_PATH,
					pd.PATIENT_BLOB=temp.PATIENT_BLOB,
					pd.UPDATE_DATE=temp.UPDATE_DATE,
					pd.DOWNLOAD_DATE=temp.DOWNLOAD_DATE,
					pd.SOURCESYSTEM_CD=temp.SOURCESYSTEM_CD,
					pd.UPLOAD_ID = '||upload_id||'
                    where temp.update_date > pd.update_date
			 when not matched then
			 	insert (
					PATIENT_NUM,
					VITAL_STATUS_CD,
                    BIRTH_DATE,
                    DEATH_DATE,
                    SEX_CD,
                    AGE_IN_YEARS_NUM,
                    LANGUAGE_CD,
                    RACE_CD,
                    MARITAL_STATUS_CD,
                    RELIGION_CD,
                    ZIP_CD,
					STATECITYZIP_PATH,
					PATIENT_BLOB,
					UPDATE_DATE,
					DOWNLOAD_DATE,
					SOURCESYSTEM_CD,
					import_date,
	                upload_id
 					)
			 	values (
 					temp.PATIENT_NUM,
					temp.VITAL_STATUS_CD,
                    temp.BIRTH_DATE,
                    temp.DEATH_DATE,
                    temp.SEX_CD,
                    temp.AGE_IN_YEARS_NUM,
                    temp.LANGUAGE_CD,
                    temp.RACE_CD,
                    temp.MARITAL_STATUS_CD,
                    temp.RELIGION_CD,
                    temp.ZIP_CD,
					temp.STATECITYZIP_PATH,
					temp.PATIENT_BLOB,
					temp.UPDATE_DATE,
					temp.DOWNLOAD_DATE,
					temp.SOURCESYSTEM_CD,
					sysdate,
	     			'||upload_id||'
 				)';
EXCEPTION
	WHEN OTHERS THEN
		raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
 
