--
--

SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: amapp; Type: SCHEMA; Schema: -; Owner: amapp
--

CREATE SCHEMA amapp;


ALTER SCHEMA amapp OWNER TO amapp;

--
-- Name: biomart; Type: SCHEMA; Schema: -; Owner: biomart
--

CREATE SCHEMA biomart;


ALTER SCHEMA biomart OWNER TO biomart;

--
-- Name: biomart_user; Type: SCHEMA; Schema: -; Owner: biomart_user
--

CREATE SCHEMA biomart_user;


ALTER SCHEMA biomart_user OWNER TO biomart_user;

--
-- Name: deapp; Type: SCHEMA; Schema: -; Owner: deapp
--

CREATE SCHEMA deapp;


ALTER SCHEMA deapp OWNER TO deapp;

--
-- Name: fmapp; Type: SCHEMA; Schema: -; Owner: fmapp
--

CREATE SCHEMA fmapp;


ALTER SCHEMA fmapp OWNER TO fmapp;

--
-- Name: galaxy; Type: SCHEMA; Schema: -; Owner: galaxy
--

CREATE SCHEMA galaxy;


ALTER SCHEMA galaxy OWNER TO galaxy;

--
-- Name: i2b2demodata; Type: SCHEMA; Schema: -; Owner: i2b2demodata
--

CREATE SCHEMA i2b2demodata;


ALTER SCHEMA i2b2demodata OWNER TO i2b2demodata;

--
-- Name: i2b2metadata; Type: SCHEMA; Schema: -; Owner: i2b2metadata
--

CREATE SCHEMA i2b2metadata;


ALTER SCHEMA i2b2metadata OWNER TO i2b2metadata;

--
-- Name: searchapp; Type: SCHEMA; Schema: -; Owner: searchapp
--

CREATE SCHEMA searchapp;


ALTER SCHEMA searchapp OWNER TO searchapp;

--
-- Name: tm_cz; Type: SCHEMA; Schema: -; Owner: tm_cz
--

CREATE SCHEMA tm_cz;


ALTER SCHEMA tm_cz OWNER TO tm_cz;

--
-- Name: tm_lz; Type: SCHEMA; Schema: -; Owner: tm_lz
--

CREATE SCHEMA tm_lz;


ALTER SCHEMA tm_lz OWNER TO tm_lz;

--
-- Name: tm_wz; Type: SCHEMA; Schema: -; Owner: tm_wz
--

CREATE SCHEMA tm_wz;


ALTER SCHEMA tm_wz OWNER TO tm_wz;

--
-- Name: ts_batch; Type: SCHEMA; Schema: -; Owner: ts_batch
--

CREATE SCHEMA ts_batch;


ALTER SCHEMA ts_batch OWNER TO ts_batch;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: am_tag_value_uid(bigint); Type: FUNCTION; Schema: amapp; Owner: amapp
--

CREATE FUNCTION amapp.am_tag_value_uid(tag_value_id bigint) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
	BEGIN
	  RETURN 'TAG:' || TAG_VALUE_ID::text;
	END;
$$;


ALTER FUNCTION amapp.am_tag_value_uid(tag_value_id bigint) OWNER TO amapp;

--
-- Name: tf_trg_am_tag_item_id(); Type: FUNCTION; Schema: amapp; Owner: amapp
--

CREATE FUNCTION amapp.tf_trg_am_tag_item_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.TAG_ITEM_ID is null then
 select nextval('amapp.SEQ_AMAPP_DATA_ID') into NEW.TAG_ITEM_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION amapp.tf_trg_am_tag_item_id() OWNER TO amapp;

--
-- Name: tf_trg_am_tag_temp_assoc_id(); Type: FUNCTION; Schema: amapp; Owner: amapp
--

CREATE FUNCTION amapp.tf_trg_am_tag_temp_assoc_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.ID is null then
 select nextval('amapp.SEQ_AMAPP_DATA_ID') into NEW.ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION amapp.tf_trg_am_tag_temp_assoc_id() OWNER TO amapp;

--
-- Name: tf_trg_am_tag_template_id(); Type: FUNCTION; Schema: amapp; Owner: amapp
--

CREATE FUNCTION amapp.tf_trg_am_tag_template_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.TAG_TEMPLATE_ID is null then
 select nextval('amapp.SEQ_AMAPP_DATA_ID') into NEW.TAG_TEMPLATE_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION amapp.tf_trg_am_tag_template_id() OWNER TO amapp;

--
-- Name: tf_trg_am_tag_value_id(); Type: FUNCTION; Schema: amapp; Owner: amapp
--

CREATE FUNCTION amapp.tf_trg_am_tag_value_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.TAG_VALUE_ID is null then
 select nextval('amapp.SEQ_AMAPP_DATA_ID') into NEW.TAG_VALUE_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION amapp.tf_trg_am_tag_value_id() OWNER TO amapp;

--
-- Name: tf_trg_am_tag_value_uid(); Type: FUNCTION; Schema: amapp; Owner: amapp
--

CREATE FUNCTION amapp.tf_trg_am_tag_value_uid() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  rec_count bigint;
BEGIN
  SELECT COUNT(*) INTO rec_count 
  FROM amapp.am_data_uid 
  WHERE am_data_id = new.tag_value_id;
  
  if rec_count = 0 then
    insert into amapp.am_data_uid (am_data_id, unique_id, am_data_type)
    values (NEW.tag_value_id, amapp.am_tag_value_uid(NEW.tag_value_id), 'AM_TAG_VALUE');
  end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION amapp.tf_trg_am_tag_value_uid() OWNER TO amapp;

--
-- Name: bio_assay_analysis_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_assay_analysis_uid(analysis_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_experiment.

  RETURN 'BAA:' || coalesce(ANALYSIS_NAME, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_assay_analysis_uid(analysis_name character varying) OWNER TO biomart;

--
-- Name: bio_assay_platform_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_assay_platform_uid(platform_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_assay_platform

  RETURN 'BAP:' || coalesce(PLATFORM_NAME, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_assay_platform_uid(platform_name character varying) OWNER TO biomart;

--
-- Name: bio_asy_analysis_pltfm_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_asy_analysis_pltfm_uid(platform_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_asy_analysis_pltfm

  RETURN 'BAAP:' || coalesce(PLATFORM_NAME, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_asy_analysis_pltfm_uid(platform_name character varying) OWNER TO biomart;

--
-- Name: bio_compound_uid(character varying, character varying, character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_compound_uid(cas_registry character varying, jnj_number character varying, cnto_number character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Function to create compound_uid.

  RETURN 'COM:' || coalesce(CAS_REGISTRY, coalesce(JNJ_NUMBER, coalesce(CNTO_NUMBER, 'ERROR')));
END;
$_$;


ALTER FUNCTION biomart.bio_compound_uid(cas_registry character varying, jnj_number character varying, cnto_number character varying) OWNER TO biomart;

--
-- Name: bio_curation_dataset_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_curation_dataset_uid(bio_curation_type character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_curation_dataset.

  RETURN 'BCD:' || coalesce(BIO_CURATION_TYPE, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_curation_dataset_uid(bio_curation_type character varying) OWNER TO biomart;

--
-- Name: bio_disease_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_disease_uid(mesh_code character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates bio_disease_uid.

  RETURN 'DIS:' || coalesce(MESH_CODE, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_disease_uid(mesh_code character varying) OWNER TO biomart;

--
-- Name: bio_experiment_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_experiment_uid(primary_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_experiment.

  RETURN 'EXP:' || coalesce(PRIMARY_ID, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_experiment_uid(primary_id character varying) OWNER TO biomart;

--
-- Name: bio_jub_oncol_data_uid(numeric, character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_jub_oncol_data_uid(record_id numeric, bio_curation_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_jub_oncol_data.

  RETURN 'BJOD:' || coalesce(trim(TO_CHAR(RECORD_ID, '9999999999999999999')), 'ERROR') || ':' || coalesce(BIO_CURATION_NAME, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_jub_oncol_data_uid(record_id numeric, bio_curation_name character varying) OWNER TO biomart;

--
-- Name: bio_jub_oncol_sum_data_uid(numeric, character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.bio_jub_oncol_sum_data_uid(record_id numeric, bio_curation_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_jub_oncol_sum_data

  RETURN 'BJOS:' || coalesce(trim(TO_CHAR(RECORD_ID, '9999999999999999999')), 'ERROR') || ':' || coalesce(BIO_CURATION_NAME, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.bio_jub_oncol_sum_data_uid(record_id numeric, bio_curation_name character varying) OWNER TO biomart;

--
-- Name: biomarker_gene_uid(character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.biomarker_gene_uid(gene_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for biomarker_gene

  RETURN 'GENE:' || coalesce(GENE_ID, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.biomarker_gene_uid(gene_id character varying) OWNER TO biomart;

--
-- Name: biomarker_pathway_uid(character varying, character varying); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.biomarker_pathway_uid(p_source character varying, pathway_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for biomarker_pathway.

  RETURN 'PATHWAY:'|| P_SOURCE || ':' || coalesce(PATHWAY_ID, 'ERROR');
END;
$_$;


ALTER FUNCTION biomart.biomarker_pathway_uid(p_source character varying, pathway_id character varying) OWNER TO biomart;

--
-- Name: cum_normal_dist(numeric, numeric, numeric); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.cum_normal_dist(foldchg numeric, mu numeric, sigma numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE

 -------------------------------------------------------------------------------
  -- implementation of a cumalative normal distribution
  -- JWS@20090601 - First rev.
  -- param foldChg: fold change ratio from from analysis_data table
  -- param mu: mean of all analsyis_data records for a given analysis
  -- param sigma: std dev of all analsyis_data records for a given analysis
  -------------------------------------------------------------------------------

  -- temporary vars  
  t1 decimal;
  
  -- fractional error dist input
  fract_error_input decimal;
  
  -- return result (i.e. Prob [X<=x])
  ans decimal;  


BEGIN
  t1:= (foldChg-mu)/sigma;  
  fract_error_input:= t1/SQRT(2);
  ans:= 0.5 * (1.0 + biomart.fract_error_dist(fract_error_input));
  return ans; 
END;
$$;


ALTER FUNCTION biomart.cum_normal_dist(foldchg numeric, mu numeric, sigma numeric) OWNER TO biomart;

--
-- Name: fract_error_dist(numeric); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.fract_error_dist(norminput numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE


 -------------------------------------------------------------------------------
  -- implementation of fractional error distribution
  -- JWS@20090601 - First rev.
  -------------------------------------------------------------------------------
  -- temp var
  t1 decimal:= 1.0 / (1.0 + 0.5 * ABS(normInput));
  
  -- exponent input to next equation
  exponent_input decimal:= -normInput*normInput - 1.26551223 + 
                           t1*(1.00002368 + t1*(0.37409196 + t1*(0.09678418 + t1*(-0.18628806 + t1*(0.27886807 + t1*(-1.13520398 + t1*(1.48851587 + t1*(-0.82215223 + t1*(0.17087277)))))))));
  -- Horner's method
  ans decimal:= 1 - t1 * EXP(exponent_input);

  fractError decimal;


BEGIN
  -- handle sign
  IF normInput>0.0 THEN fractError:= ans; ELSE fractError:= -ans; END IF;
  return fractError;

END;
$$;


ALTER FUNCTION biomart.fract_error_dist(norminput numeric) OWNER TO biomart;

--
-- Name: refresh_bio_lit_int_model_mv(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.refresh_bio_lit_int_model_mv() RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN

  -- populate the fake "materialized view" (i.e. actually a table) by deleting everything from it and then re-populating
  -- eventually we need a smarter algorithm for doing this

    delete from biomart.bio_lit_int_model_mv;

    insert into biomart.bio_lit_int_model_mv
    (bio_lit_int_data_id, experimental_model)    
    select bio_lit_int_data_id, experimental_model
       from biomart.bio_lit_int_model_view;  

    return true;
END;
 
 
 
$$;


ALTER FUNCTION biomart.refresh_bio_lit_int_model_mv() OWNER TO biomart;

--
-- Name: refresh_bio_marker_correl_mv(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.refresh_bio_marker_correl_mv() RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN

  -- populate the fake "materialized view" (i.e. actually a table) by deleting everything from it and then re-populating
  -- eventually we need a smarter algorithm for doing this

    delete from biomart.bio_marker_correl_mv;

    insert into biomart.bio_marker_correl_mv
    (BIO_MARKER_ID, ASSO_BIO_MARKER_ID, CORREL_TYPE, mv_id)    
    select BIO_MARKER_ID, ASSO_BIO_MARKER_ID, CORREL_TYPE, mv_id
       from biomart.bio_marker_correl_view;  

    return true;
END;
$$;


ALTER FUNCTION biomart.refresh_bio_marker_correl_mv() OWNER TO biomart;

--
-- Name: tea_npv_precompute(numeric, numeric, numeric); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tea_npv_precompute(foldchg numeric, mu numeric, sigma numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE


 -------------------------------------------------------------------------------
  -- used for gene signature TEA algorithm which precomputes normalized p-values
  -- based ON biomart.fold change ratio from analysis_data records
  -- JWS@20090601 - First rev.
  -- param foldChg: input is fold change ratio from from analysis_data table
  -- param mu: mean of all analsyis_data records for a given analysis
  -- param sigma: std dev of all analsyis_data records for a given analysis
  -------------------------------------------------------------------------------
  npv decimal;
  outlier_cutoff decimal:=1.0e-5;
  

BEGIN
  -- normalized p-value 
  npv:= 1.0 - cum_normal_dist(abs(foldChg),mu,sigma);
  
  -- cap outliers to minimum value
  IF npv<outlier_cutoff THEN npv:= outlier_cutoff; END IF;
  
  RETURN npv;
  
END;
$$;


ALTER FUNCTION biomart.tea_npv_precompute(foldchg numeric, mu numeric, sigma numeric) OWNER TO biomart;

--
-- Name: tf_trg_bio_ad_hoc_prop_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_ad_hoc_prop_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.AD_HOC_PROPERTY_ID is null then
 select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.AD_HOC_PROPERTY_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_ad_hoc_prop_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_analysis_al_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_analysis_al_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	IF NEW.BIO_ANALYSIS_ATT_LINEAGE_ID IS NULL
		THEN
		SELECT nextval('BIOMART.SEQ_BIO_DATA_ID') INTO NEW.BIO_ANALYSIS_ATT_LINEAGE_ID;
	END IF;
RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_bio_analysis_al_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_analysis_attribute_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_analysis_attribute_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	BEGIN
		if NEW.BIO_ANALYSIS_ATTRIBUTE_ID IS NULL
			THEN
			SELECT nextval('BIOMART.SEQ_BIO_DATA_ID') INTO NEW.BIO_ANALYSIS_ATTRIBUTE_ID;
  		END IF;
	RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_bio_analysis_attribute_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_analysis_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_analysis_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASSAY_ANALYSIS_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASSAY_ANALYSIS_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_analysis_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_analysis_uid(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_analysis_uid() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  rec_count bigint;
BEGIN
  SELECT COUNT(*) INTO rec_count 
  FROM biomart.bio_data_uid 
  WHERE bio_data_id = new.bio_assay_analysis_id;
  
  if rec_count = 0 then
    insert into biomart.bio_data_uid (bio_data_id, unique_id, bio_data_type)
    values (NEW.bio_assay_analysis_id, biomart.bio_assay_analysis_uid(NEW.bio_assay_analysis_id::text), 'BIO_ASSAY_ANALYSIS');
  end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_analysis_uid() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_cohort_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_cohort_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF NEW.BIO_ASSAY_COHORT_ID IS NULL
		THEN
		SELECT nextval('BIOMART.SEQ_BIO_DATA_ID') INTO NEW.BIO_ASSAY_COHORT_ID;
	END IF;
RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_cohort_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASSAY_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASSAY_DATA_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_dataset_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_dataset_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASSAY_DATASET_ID is null then
         select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASSAY_DATASET_ID ;
    end if;
RETURN NEW;
end;

$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_dataset_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_f_g_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_f_g_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASSAY_FEATURE_GROUP_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASSAY_FEATURE_GROUP_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_f_g_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASSAY_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASSAY_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_assay_platform_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_assay_platform_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASSAY_PLATFORM_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASSAY_PLATFORM_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_assay_platform_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_asy_adi_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_asy_adi_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin     
      if coalesce(NEW.bio_asy_analysis_data_idx_id::text, '') = '' then          
        select nextval('biomart.seq_bio_data_id') into NEW.bio_asy_analysis_data_idx_id;       
      end if;       
       RETURN NEW;
  end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_asy_adi_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_asy_analysis_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_asy_analysis_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF NEW.BIO_ASY_ANALYSIS_DATA_ID IS NULL
		THEN  select nextval('SEQ_BIO_DATA_ID') INTO NEW.BIO_ASY_ANALYSIS_DATA_ID;
	END IF;
	RETURN NEW;
END
$$;


ALTER FUNCTION biomart.tf_trg_bio_asy_analysis_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_asy_analysis_pltfm_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_asy_analysis_pltfm_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_ASY_ANALYSIS_PLTFM_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_ASY_ANALYSIS_PLTFM_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_asy_analysis_pltfm_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_asy_dt_stats_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_asy_dt_stats_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	IF NEW.BIO_ASSAY_DATA_STATS_ID IS NULL
		THEN
		SELECT nextval('BIOMART.SEQ_BIO_DATA_FACT_ID') INTO NEW.BIO_ASSAY_DATA_STATS_ID;
	END IF;
RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_bio_asy_dt_stats_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_cell_line_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_cell_line_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CELL_LINE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CELL_LINE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_cell_line_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_cl_trl_time_pt_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_cl_trl_time_pt_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CLINC_TRIAL_TM_PT_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CLINC_TRIAL_TM_PT_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_cl_trl_time_pt_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_clin_trl_pt_grp_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_clin_trl_pt_grp_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CLINICAL_TRIAL_P_GROUP_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CLINICAL_TRIAL_P_GROUP_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_clin_trl_pt_grp_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_cln_trl_attr_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_cln_trl_attr_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CLINC_TRIAL_ATTR_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CLINC_TRIAL_ATTR_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_cln_trl_attr_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_compound_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_compound_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_COMPOUND_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_COMPOUND_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_compound_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_concept_code_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_concept_code_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CONCEPT_CODE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CONCEPT_CODE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_concept_code_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_content_ref_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_content_ref_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CONTENT_REFERENCE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CONTENT_REFERENCE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_content_ref_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_content_repo_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_content_repo_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CONTENT_REPO_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CONTENT_REPO_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_content_repo_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_curation_dataset_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_curation_dataset_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.BIO_CURATION_DATASET_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CURATION_DATASET_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_curation_dataset_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_data_attr_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_data_attr_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_DATA_ATTRIBUTE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_DATA_ATTRIBUTE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_data_attr_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_data_correl_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_data_correl_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_DATA_CORREL_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_DATA_CORREL_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_data_correl_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_data_ext_code_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_data_ext_code_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_DATA_EXT_CODE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_DATA_EXT_CODE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_data_ext_code_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_disease_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_disease_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
     if NEW.BIO_DISEASE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_DISEASE_ID ;
     end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_disease_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_experiment_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_experiment_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.BIO_EXPERIMENT_ID is null then
         select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_EXPERIMENT_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_experiment_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_experiment_uid(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_experiment_uid() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  rec_count bigint;
BEGIN
  SELECT COUNT(*) INTO rec_count 
  FROM biomart.bio_data_uid 
  WHERE bio_data_id = NEW.bio_experiment_id;
  
  if rec_count = 0 then
    insert into biomart.bio_data_uid (bio_data_id, unique_id, bio_data_type)
    values (NEW.bio_experiment_id, biomart.bio_experiment_uid(NEW.accession), 'BIO_EXPERIMENT');
  end if;
RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_bio_experiment_uid() OWNER TO biomart;

--
-- Name: tf_trg_bio_file_content_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_file_content_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_FILE_CONTENT_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_FILE_CONTENT_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_file_content_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_alt_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_alt_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_ALT_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_ALT_DATA_ID ;
       end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_alt_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_amd_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_amd_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_AMD_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_AMD_DATA_ID ;
       end if;
       RETURN NEW;
    end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_amd_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_inh_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_inh_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_INH_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_INH_DATA_ID ;
       end if; 
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_inh_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_int_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_int_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_INT_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_INT_DATA_ID ;
       end if; 
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_int_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_model_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_model_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_MODEL_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_MODEL_DATA_ID ;
       end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_model_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_pe_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_pe_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_PE_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_PE_DATA_ID ;
       end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_pe_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_ref_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_ref_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_REF_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_REF_DATA_ID ;
       end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_ref_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_lit_sum_data_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_lit_sum_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.BIO_LIT_SUM_DATA_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_LIT_SUM_DATA_ID ;
       end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_lit_sum_data_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_marker_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_marker_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin  if NEW.BIO_MARKER_ID is null then          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_MARKER_ID ;       end if;  RETURN NEW;  end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_marker_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_mkr_correl_descr_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_mkr_correl_descr_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_DATA_CORREL_DESCR_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_DATA_CORREL_DESCR_ID ;
    end if;
RETURN NEW;
end;

$$;


ALTER FUNCTION biomart.tf_trg_bio_mkr_correl_descr_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_observation_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_observation_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_OBSERVATION_ID is null then
        select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_OBSERVATION_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_observation_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_patient_event_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_patient_event_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_PATIENT_EVENT_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_PATIENT_EVENT_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_patient_event_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_patient_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_patient_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_PATIENT_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_PATIENT_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_patient_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_pt_evt_attr_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_pt_evt_attr_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_CLINIC_TRIAL_ATTRIBUTE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_CLINIC_TRIAL_ATTRIBUTE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_pt_evt_attr_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_sample_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_sample_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_SAMPLE_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_SAMPLE_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_sample_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_subject_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_subject_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.BIO_SUBJECT_ID is null then
          select nextval('biomart.SEQ_BIO_DATA_ID') into NEW.BIO_SUBJECT_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trg_bio_subject_id() OWNER TO biomart;

--
-- Name: tf_trg_bio_taxon_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_bio_taxon_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN IF NEW.BIO_TAXONOMY_ID IS NULL THEN
  select nextval('biomart.SEQ_BIO_DATA_ID') INTO NEW.BIO_TAXONOMY_ID ;
END IF;
RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_bio_taxon_id() OWNER TO biomart;

--
-- Name: tf_trg_ctd2_clin_inhib_effect(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_ctd2_clin_inhib_effect() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN     
 	IF NEW.CTD_CIE_SEQ IS NULL THEN 
		select nextval('biomart.SEQ_CLINICAL_TRIAL_DESIGN_ID') INTO NEW.CTD_CIE_SEQ ;  
	END IF;  
	RETURN NEW;  
END;
$$;


ALTER FUNCTION biomart.tf_trg_ctd2_clin_inhib_effect() OWNER TO biomart;

--
-- Name: tf_trg_ctd2_disease(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_ctd2_disease() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN     
	IF NEW.CTD_DISEASE_SEQ IS NULL THEN 
		select nextval('biomart.SEQ_CLINICAL_TRIAL_DESIGN_ID') INTO NEW.CTD_DISEASE_SEQ ;  
	END IF;    
	RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_ctd2_disease() OWNER TO biomart;

--
-- Name: tf_trg_ctd2_inhib_details(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_ctd2_inhib_details() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN     
	IF NEW.CTD_INHIB_SEQ IS NULL THEN 
		select nextval('biomart.SEQ_CLINICAL_TRIAL_DESIGN_ID') INTO NEW.CTD_INHIB_SEQ ;  
	END IF;    
	RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_ctd2_inhib_details() OWNER TO biomart;

--
-- Name: tf_trg_ctd2_study_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_ctd2_study_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN     
	IF NEW.CTD_STUDY_ID IS NULL THEN 
		select nextval('biomart.SEQ_CLINICAL_TRIAL_DESIGN_ID') INTO NEW.CTD_STUDY_ID ;  
	END IF;    
	RETURN NEW;
END;
$$;


ALTER FUNCTION biomart.tf_trg_ctd2_study_id() OWNER TO biomart;

--
-- Name: tf_trg_ctd2_trial_details(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trg_ctd2_trial_details() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN     
	IF NEW.CTD_TD_SEQ IS NULL THEN 
		select nextval('biomart.SEQ_CLINICAL_TRIAL_DESIGN_ID') INTO NEW.CTD_TD_SEQ ;  
	END IF;   
	RETURN NEW; 
END;
$$;


ALTER FUNCTION biomart.tf_trg_ctd2_trial_details() OWNER TO biomart;

--
-- Name: tf_trig_clinical_trial_design_id(); Type: FUNCTION; Schema: biomart; Owner: biomart
--

CREATE FUNCTION biomart.tf_trig_clinical_trial_design_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin     
    if NEW.CLINICAL_TRIAL_DESIGN_ID is null then          
      select nextval('biomart.SEQ_CLINICAL_TRIAL_DESIGN_ID') into NEW.CLINICAL_TRIAL_DESIGN_ID ;  
    end if; 
    RETURN NEW;
end;
$$;


ALTER FUNCTION biomart.tf_trig_clinical_trial_design_id() OWNER TO biomart;

--
-- Name: i2b2_bulk_add_search_term(bigint); Type: FUNCTION; Schema: biomart_user; Owner: biomart_user
--

CREATE FUNCTION biomart_user.i2b2_bulk_add_search_term(currentjobid bigint DEFAULT NULL::bigint) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE

/*************************************************************************
* Copyright 2008-2012 Janssen Research null, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/

	--Audit variables
	newJobFlag integer(1);
	databaseName varchar(100);
	procedureName varchar(100);
	jobID bigint;
	stepCt bigint;

	v_keyword_term	varchar(500);
  v_display_category varchar(500);
  v_data_category varchar(500);
  v_prefix varchar(20);
  v_unique_ID varchar(500);
  v_source_cd varchar(500);
  v_parent_term varchar(500);
  v_bio_data_id bigint;
  
	sqlText			varchar(2000);
	Parent_Id 		integer;
	new_Term_Id 	integer;
	keyword_id 		integer;
	Lcount 			integer; 
	Ncount 			integer;
  
	--v_category_display	varchar2(200);

  	type keyword_rec  is record
	(keywordName		varchar(500)),
   dataCategory varchar(500),
   UIDPrefix varchar(20),
   uniqueID varchar(500),
   sourceCd varchar(500),
   parentTerm varchar(500),
   bio_data_id bigint
	);

	type keyword_table is table of keyword_rec; 
	keyword_array keyword_table;
	
  

BEGIN

	--Set Audit Parameters
	newJobFlag := 0; -- False (Default)
	jobID := currentJobID;

	PERFORM sys_context('USERENV', 'CURRENT_SCHEMA') INTO databaseName ;
	procedureName := $$PLSQL_UNIT;

	--Audit JOB Initialization
	--If Job ID does not exist, then this is a single procedure run and we need to create it
	IF(coalesce(jobID::text, '') = '' or jobID < 1)
	THEN
		newJobFlag := 1; -- True
		cz_start_audit (procedureName, databaseName, jobID);
	END IF;
    	
	--stepCt := 0;
  
	--cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
	--Stepct := Stepct + 1;	
  
	  select keyword, display_category, data_category, UID_prefix, unique_id, source_cd, parent_term, bio_data_id
    bulk collect into keyword_array
  	from tm_lz.lt_src_search_keyword
    where (keyword IS NOT NULL AND keyword::text <> '');

 
	for i in keyword_array.first .. keyword_array.last
	loop
		v_keyword_term := keyword_array(i).keywordName;
    v_display_category := keyword_array(i).displayCategory;
    v_data_category := keyword_array(i).dataCategory;
    v_prefix := keyword_array(i).UIDPrefix;
    v_unique_ID := keyword_array(i).uniqueID;
    v_source_cd := keyword_array(i).sourceCd;
    v_parent_term := keyword_array(i).parentTerm;
    v_bio_data_id := keyword_array(i).bio_data_id;
    
    --dbms_output.put_line('keyword: ' || v_keyword_term);


		if (coalesce(v_display_category::text, '') = '') then
			v_display_category := v_data_category;
    end if;


    if (coalesce(v_unique_ID::text, '') = '') then
      if ((v_prefix IS NOT NULL AND v_prefix::text <> '')) then
        v_unique_ID := v_prefix || ':' || v_data_category || ':' || v_keyword_term;
      end if;
    end if;
   

		-- Insert taxonomy term into searchapp.search_keyword
		-- (searches Search_Keyword with the parent term to find the category to use)
		Insert Into Searchapp.Search_Keyword 
		(Data_Category
		,Keyword
		,Unique_Id
		,Source_Code
		,Display_Data_Category
    ,Bio_data_id)
		PERFORM v_data_category
			  ,v_keyword_term
			  ,v_unique_ID
			  ,v_source_cd
			  ,v_display_category
        ,v_bio_data_id
		
		where not exists
			(select 1 from searchapp.search_keyword x
			 where upper(x.data_category) = upper(v_data_category)
			   and upper(x.keyword) = upper(v_keyword_term)
         and upper(x.bio_data_id) = upper(v_bio_data_id));
		Cz_Write_Audit(Jobid,Databasename,Procedurename,v_keyword_term || ' added to Searchapp.Search_Keyword',Sql%Rowcount,Stepct,'Done');
		Stepct := Stepct + 1;	
		commit;
  
		-- Get the ID of the new term in Search_Keyword
		Select Search_Keyword_Id  Into keyword_Id 
		From  Searchapp.Search_Keyword 
		Where Upper(Keyword) = Upper(v_keyword_term)
		and upper(data_category) = upper(v_data_category)
    and upper(bio_data_id) = upper(v_bio_data_id);
		Cz_Write_Audit(Jobid,Databasename,Procedurename,'New search keyword ID stored in Keyword_Id',Sql%Rowcount,Stepct,'Done');
		Stepct := Stepct + 1;	
		
		-- Insert the new term into Searchapp.Search_v_keyword_term 
		insert into searchapp.search_keyword_term 
		(keyword_term
		,search_keyword_id
		,rank
		,term_length)
		PERFORM UPPER(v_keyword_term)
			  ,Keyword_Id
			  ,1
			  ,Length(v_keyword_term) 
		
		where not exists
			(select 1 from searchapp.search_keyword_term x
			 where upper(x.keyword_term) = upper(v_keyword_term)
			   and x.search_keyword_id = Keyword_Id);
		Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term added to Searchapp.Search_v_keyword_term',Sql%Rowcount,Stepct,'Done');
		Stepct := Stepct + 1;	
		commit;

	end loop;

	Cz_Write_Audit(Jobid,Databasename,Procedurename,'End '|| procedureName,0,Stepct,'Done');
	Stepct := Stepct + 1;  

 
     ---Cleanup OVERALL JOB if this proc is being run standalone    
  IF newJobFlag = 1
  THEN
    cz_end_audit (jobID, 'SUCCESS');
  END IF;
  
    EXCEPTION
  WHEN OTHERS THEN
    --Handle errors.
    cz_error_handler (jobID, procedureName);
    --End Proc
    cz_end_audit (jobID, 'FAIL');
  
END;
 
$_$;


ALTER FUNCTION biomart_user.i2b2_bulk_add_search_term(currentjobid bigint) OWNER TO biomart_user;

--
-- Name: i2b2_metabolomics_zscore_calc(character varying, character varying, character varying, numeric, character varying, numeric); Type: FUNCTION; Schema: biomart_user; Owner: biomart_user
--

CREATE FUNCTION biomart_user.i2b2_metabolomics_zscore_calc(trial_id character varying, source_cd character varying, run_type character varying DEFAULT 'L'::character varying, currentjobid numeric DEFAULT '-1'::integer, data_type character varying DEFAULT 'R'::character varying, log_base numeric DEFAULT 2) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE

/*************************************************************************
This Stored Procedure is used in ETL load METABOLOMICS data
Date:1/3/2014
******************************************************************/

  TrialID varchar(100);
  sourceCD	varchar(50);
  sqlText varchar(2000);
  runType varchar(10);
  dataType varchar(10);
  stgTrial varchar(100);
  idxExists integer;
  pExists	integer;
  nbrRecs integer;
  logBase numeric;
   
  --Audit variables
  newJobFlag integer(1);
  databaseName varchar(100);
  procedureName varchar(100);
  jobID numeric;
  stepCt integer;
  
  --  exceptions
  invalid_runType exception;
  trial_mismatch exception;
  trial_missing exception;
  

BEGIN

	TrialId := trial_id;
	runType := run_type;
	dataType := data_type;
	logBase := log_base;
	sourceCd := source_cd;
	  RAISE NOTICE 'DK0';
  --Set Audit Parameters
  newJobFlag := 0; -- False (Default)
  jobID := currentJobID;

  PERFORM sys_context('USERENV', 'CURRENT_SCHEMA') INTO databaseName ;
  procedureName := $$PLSQL_UNIT;

  --Audit JOB Initialization
  --If Job ID does not exist, then this is a single procedure run and we need to create it
  IF(coalesce(jobID::text, '') = '' or jobID < 1)
  THEN
    newJobFlag := 1; -- True
    cz_start_audit (procedureName, databaseName, jobID);
  END IF;
   
  stepCt := 0;
  
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Starting zscore calc for ' || TrialId || ' RunType: ' || runType || ' dataType: ' || dataType,0,stepCt,'Done');
  
	if runType != 'L' then
		stepCt := stepCt + 1;
		cz_write_audit(jobId,databaseName,procedureName,'Invalid runType passed - procedure exiting'
,SQL%ROWCOUNT,stepCt,'Done');
		raise invalid_runType;
	end if;
  
--	For Load, make sure that the TrialId passed as parameter is the same as the trial in stg_subject_METABOLOMICS_data
--	If not, raise exception

	if runType = 'L' then
		select distinct trial_name into stgTrial
		from WT_SUBJECT_MBOLOMICS_PROBESET;
		
		if stgTrial != TrialId then
			stepCt := stepCt + 1;
			cz_write_audit(jobId,databaseName,procedureName,'TrialId not the same as trial in WT_SUBJECT_MBOLOMICS_PROBESET - procedure exiting'
,SQL%ROWCOUNT,stepCt,'Done');
			--raise trial_mismatch;
		end if;
	end if;

/*	remove Reload processing
--	For Reload, make sure that the TrialId passed as parameter has data in de_subject_METABOLOMICS_data
--	If not, raise exception

	if runType = 'R' then
		select count(*) into idxExists
		from DE_SUBJECT_METABOLOMICS_DATA
  SELECT sys_context('USERENV', 'CURRENT_SCHEMA') INTO databaseName FROM dual;
  procedureName := $$PLSQL_UNIT;

  --Audit JOB Initialization
  --If Job ID does not exist, then this is a single procedure run and we need to create it
  IF(jobID IS NULL or jobID < 1)
  THEN
    newJobFlag := 1; -- True
    cz_start_audit (procedureName, databaseName, jobID);
  END IF;
   
  stepCt := 0;
  
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Starting zscore calc for ' || TrialId || ' RunType: ' || runType || ' dataType: ' || dataType,0,stepCt,'Done');
  
	if runType != 'L' then
		stepCt := stepCt + 1;
		cz_write_audit(jobId,databaseName,procedureName,'Invalid runType passed - procedure exiting',SQL%ROWCOUNT,stepCt,'Done');
		raise invalid_runType;
	end if;
  
		where trial_name = TrialId;
		
		if idxExists = 0 then
			stepCt := stepCt + 1;
			cz_write_audit(jobId,databaseName,procedureName,'No data for TrialId in DE_SUBJECT_METABOLOMICS_DATA - procedure exiting',SQL%ROWCOUNT,stepCt,'Done');
			raise trial_missing;
		end if;
	end if;
*/
   
--	truncate tmp tables

	EXECUTE('truncate table tm_wz.WT_SUBJECT_METABOLOMICS_LOGS');
	EXECUTE('truncate table tm_wz.WT_SUBJECT_METABOLOMICS_CALCS');
	EXECUTE('truncate table tm_wz.WT_SUBJECT_METABOLOMICS_MED');

	select count(*) 
	into idxExists
	from all_indexes
	where table_name = 'WT_SUBJECT_METABOLOMICS_LOGS'
	  and index_name = 'WT_SUBJECT_MBOLOMICS_LOGS_I1'
	  and owner = 'TM_WZ';

	if idxExists = 1 then
		EXECUTE('drop index tm_wz.WT_SUBJECT_MBOLOMICS_LOGS_I1');		
	end if;
	
	select count(*) 
	into idxExists
	from all_indexes
	where table_name = 'WT_SUBJECT_METABOLOMICS_CALCS'
	  and index_name = 'WT_SUBJECT_METABOLOMICS_CALCS_I1'
	  and owner = 'TM_WZ';

	if idxExists = 1 then
		EXECUTE('drop index tm_wz.WT_SUBJECT_METABOLOMICS_CALCS_I1');
	end if;

	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Truncate work tables in TM_WZ',0,stepCt,'Done');
	
	--	if dataType = L, use intensity_value as log_intensity
	--	if dataType = R, always use intensity_value


	if dataType = 'L' then

		insert into WT_SUBJECT_METABOLOMICS_LOGS 
			(probeset
			,intensity_value
			,assay_id
			,log_intensity
			,patient_id
		--	,sample_cd
			,subject_id
			)
			PERFORM probeset
				  ,intensity_value  
				  ,assay_id 
				  ,intensity_value
				  ,patient_id
			--	  ,sample_cd
				  ,subject_id
			from WT_SUBJECT_MBOLOMICS_PROBESET
			where trial_name = TrialId;
           
		--end if;
	else	

                	insert into WT_SUBJECT_METABOLOMICS_LOGS 
			(probeset
			,intensity_value
			,assay_id
			,log_intensity
			,patient_id
		--	,sample_cd
			,subject_id
			)
			PERFORM probeset
				  ,intensity_value 
				  ,assay_id 
				  ,log(2,intensity_value)
				  ,patient_id
		--		  ,sample_cd
				  ,subject_id
			from WT_SUBJECT_MBOLOMICS_PROBESET
			where trial_name = TrialId;
--		end if;

	end if;

	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Loaded data for trial in TM_WZ wt_subject_mirna_logs',SQL%ROWCOUNT,stepCt,'Done');

	commit;

	EXECUTE('create index tm_wz.WT_SUBJECT_MBOLOMICS_LOGS_I1 on tm_wz.WT_SUBJECT_METABOLOMICS_LOGS (trial_name, probeset) nologging  tablespace "INDX"');
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create index on TM_WZ WT_SUBJECT_MBOLOMICS_LOGS_I1',0,stepCt,'Done');
		
--	calculate mean_intensity, median_intensity, and stddev_intensity per experiment, probe

	insert into WT_SUBJECT_METABOLOMICS_CALCS
	(trial_name
	,probeset
	,mean_intensity
	,median_intensity
	,stddev_intensity
	)
	PERFORM d.trial_name 
		  ,d.probeset
		  ,avg(log_intensity)
		  ,median(log_intensity)
		  ,stddev(log_intensity)
	from WT_SUBJECT_METABOLOMICS_LOGS d 
	group by d.trial_name 
			,d.probeset;
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Calculate intensities for trial in TM_WZ WT_SUBJECT_METABOLOMICS_CALCS',SQL%ROWCOUNT,stepCt,'Done');

	commit;

	--execute immediate('create index tm_wz.wt_subject_METABOLOMICS_calcs_i1 on tm_wz.WT_SUBJECT_METABOLOMICS_CALCS (trial_name, probeset_id) nologging tablespace "INDX"');
	--stepCt := stepCt + 1;
	--cz_write_audit(jobId,databaseName,procedureName,'Create index on TM_WZ WT_SUBJECT_METABOLOMICS_CALCS',0,stepCt,'Done');
		
-- calculate zscore

	
        insert into WT_SUBJECT_METABOLOMICS_MED parallel 
	(probeset
	,intensity_value
	,log_intensity
	,assay_id
	,mean_intensity
	,stddev_intensity
	,median_intensity
	,zscore
	,patient_id
--	,sample_cd
	,subject_id
	)
	PERFORM d.probeset
		  ,d.intensity_value 
		  ,d.log_intensity 
		  ,d.assay_id  
		  ,c.mean_intensity 
		  ,c.stddev_intensity 
		  ,c.median_intensity 
		  ,(CASE WHEN stddev_intensity=0 THEN 0 ELSE (log_intensity - median_intensity ) / stddev_intensity END)
		  ,d.patient_id
	--	  ,d.sample_cd
		  ,d.subject_id
    from WT_SUBJECT_METABOLOMICS_LOGS d 
		,WT_SUBJECT_METABOLOMICS_CALCS c 
    where trim(d.probeset) = c.probeset;
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Calculate Z-Score for trial in TM_WZ WT_SUBJECT_METABOLOMICS_MED',SQL%ROWCOUNT,stepCt,'Done');

    commit;

/*
	select count(*) into n
	select count(*) into nbrRecs
	from WT_SUBJECT_METABOLOMICS_MED;
	
	if nbrRecs > 10000000 then
		i2b2_mrna_index_maint('DROP',,jobId);
		stepCt := stepCt + 1;
		cz_write_audit(jobId,databaseName,procedureName,'Drop indexes on DEAPP de_subject_METABOLOMICS_data',0,stepCt,'Done');
	else
		stepCt := stepCt + 1;
		cz_write_audit(jobId,databaseName,procedureName,'Less than 10M records, index drop bypassed',0,stepCt,'Done');
	end if;
*/
	


/*
	insert into DE_SUBJECT_METABOLOMICS_DATA
	(
        trial_source
        ,trial_name
	,metabolite_annotation_id
	--,component
	--,gene_symbol
	--,gene_id
	,assay_id
	,subject_id
	,raw_intensity 
        ,log_intensity
	,zscore
	,patient_id
		)
                select m.trial_name || ':' || mpp.source_cd,
                  TrialID
                ,d.Id
		  ,m.assay_id
                  ,m.subject_id 
                  ,m.intensity_value
                  ,log(2,m.intensity_value)
			  ,case when m.intensity_value < -2.5
			        then -2.5
					when m.intensity_value > 2.5
					then 2.5
					else m.intensity_value
			   end as zscore
                           ,m.patient_id
                from WT_SUBJECT_MBOLOMICS_PROBESET  m,
                (select distinct mp.source_cd From "TM_LZ"."LT_SRC_METABOLOMIC_MAP" mp where rownum = 1 and mp.trial_name =TrialID) mpp
                ,DEAPP.DE_METABOLITE_ANNOTATION d
		where m.trial_name = TrialID
                and d.biochemical_name = m.probeset;
        */
        insert into DE_SUBJECT_METABOLOMICS_DATA
	(
        trial_source
	,trial_name
	,metabolite_annotation_id
	--,component
	--,gene_symbol
	--,gene_id
	,assay_id
	,subject_id
	,raw_intensity 
        ,log_intensity
	,zscore
	,patient_id
	)
	PERFORM 
                  TrialId || ':' || mpp.source_cd,
                  TrialId
                 ,d.id
                 --,m.probeset_id 
                 --,d.hmdb_id
                 --,d.biomarker_id
                 ,m.assay_id
                 ,m.subject_id
	    --  ,decode(dataType,'R',m.intensity_value,'L',power(logBase, m.log_intensity),null)
                  ,m.intensity_value
		  ,round(m.log_intensity,4)
                  ,round(CASE WHEN m.zscore < -2.5 THEN -2.5 WHEN m.zscore >  2.5 THEN  2.5 ELSE round(m.zscore,5) END,5)		  
                   ,m.patient_id
	from WT_SUBJECT_METABOLOMICS_MED m,
        (select distinct mp.source_cd,mp.platform From "TM_LZ"."LT_SRC_METABOLOMIC_MAP" mp LIMIT 1 OFFSET 1 and mp.trial_name =TrialID) mpp                
       , DE_METABOLITE_ANNOTATION d
        where trim(d.biochemical_name) = trim(m.probeset)
        and d.gpl_id = mpp.platform;
        
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert data for trial in DEAPP DE_SUBJECT_METABOLOMICS_DATA',SQL%ROWCOUNT,stepCt,'Done');

  	commit;

--	add indexes, if indexes were not dropped, procedure will not try and recreate
/*
	i2b2_mrna_index_maint('ADD',,jobId);
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Add indexes on DEAPP de_subject_METABOLOMICS_data',0,stepCt,'Done');
*/
	
--	cleanup tmp_ files

	--execute immediate('truncate table tm_wz.WT_SUBJECT_METABOLOMICS_LOGS');
	--execute immediate('truncate table tm_wz.WT_SUBJECT_METABOLOMICS_CALCS');
	--execute immediate('truncate table tm_wz.WT_SUBJECT_METABOLOMICS_MED');

   	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Truncate work tables in TM_WZ',0,stepCt,'Done');
    
    ---Cleanup OVERALL JOB if this proc is being run standalone
  IF newJobFlag = 1
  THEN
    cz_end_audit (jobID, 'SUCCESS');
  END IF;

  EXCEPTION

  WHEN invalid_runType or trial_mismatch or trial_missing then
    --Handle errors.
    cz_error_handler (jobID, procedureName);
    --End Proc
  
    cz_end_audit (jobID, 'FAIL');
  when OTHERS THEN
    --Handle errors.
    cz_error_handler (jobID, procedureName);


    cz_end_audit (jobID, 'FAIL');
	
END;
 
$_$;


ALTER FUNCTION biomart_user.i2b2_metabolomics_zscore_calc(trial_id character varying, source_cd character varying, run_type character varying, currentjobid numeric, data_type character varying, log_base numeric) OWNER TO biomart_user;

--
-- Name: instr(character varying, character varying, character varying); Type: FUNCTION; Schema: biomart_user; Owner: biomart_user
--

CREATE FUNCTION biomart_user.instr(character varying, character varying, character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
BEGIN	
	return biomart_user.instr($1,$2,CAST($3 as int4));
END;
$_$;


ALTER FUNCTION biomart_user.instr(character varying, character varying, character varying) OWNER TO biomart_user;

--
-- Name: instr(character varying, character varying, integer, integer); Type: FUNCTION; Schema: biomart_user; Owner: biomart_user
--

CREATE FUNCTION biomart_user.instr(string character varying, string_to_search character varying, beg_index integer DEFAULT 1, occur_index integer DEFAULT 1) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $$

DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;

BEGIN
    IF beg_index > 0 THEN
        beg := beg_index;
        temp_str := substring(string FROM beg_index);

        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);

            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;

            temp_str := substring(string FROM beg + 1);
        END LOOP;

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;

    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;

                END IF;

            END IF;

            beg := beg - 1;

        END LOOP;

        RETURN 0;

    END IF;

END;

$$;


ALTER FUNCTION biomart_user.instr(string character varying, string_to_search character varying, beg_index integer, occur_index integer) OWNER TO biomart_user;

--
-- Name: dump_table_to_csv(character varying, character varying, character varying); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.dump_table_to_csv(p_tname character varying, p_dir character varying, p_filename character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE

        l_output        utl_file.file_type;
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar(4000);
        l_status        integer;
        l_query         varchar(1000)
                       default 'select * from ' || p_tname;
       l_colCnt        integer := 0;
       l_separator     varchar(1);
       l_descTbl       dbms_sql.desc_tab;
   
BEGIN
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
       EXECUTE 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''
';

       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

      for i in 1 .. l_colCnt loop
           utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
           dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
          l_separator := ',';
       end loop;
       utl_file.new_line( l_output );

       l_status := dbms_sql.execute(l_theCursor);

       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               utl_file.put( l_output, l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           utl_file.new_line( l_output );
       end loop;
       dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );

       EXECUTE 'alter session set nls_date_format=''dd-MON-yy'' ';
   exception
       when others then
           EXECUTE 'alter session set nls_date_format=''dd-MON-yy'' ';
           raise;
   end;
 
$$;


ALTER FUNCTION deapp.dump_table_to_csv(p_tname character varying, p_dir character varying, p_filename character varying) OWNER TO deapp;

--
-- Name: i2b2_process_rna_seq_data(character varying, character varying, character varying, character varying, numeric, character varying, numeric); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.i2b2_process_rna_seq_data(trial_id character varying, top_node character varying, data_type character varying DEFAULT 'R'::character varying, source_cd character varying DEFAULT 'STD'::character varying, log_base numeric DEFAULT 2, secure_study character varying DEFAULT NULL::character varying, currentjobid numeric DEFAULT '-1'::integer, OUT rtn_code numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
DECLARE

/*************************************************************************
* This stored procedure is for ETL to load RNA sequencing
* Date:10/23/2013
******************************************************************/
--	***  NOTE ***
--	The input file columns are mapped to the following table columns.  This is done so that the javascript for the advanced workflows
--	selects the correct data for the dropdowns.

--		tissue_type	=>	sample_type
--		attribute_1	=>	tissue_type
--		atrribute_2	=>	timepoint	

  TrialID		varchar(100);
  RootNode		varchar(2000);
  root_level	integer;
  topNode		varchar(2000);
  topLevel		integer;
  tPath			varchar(2000);
  study_name	varchar(100);
  sourceCd		varchar(50);
  secureStudy	varchar(1);

  dataType		varchar(10);
  sqlText		varchar(1000);
  tText			varchar(1000);
  gplTitle		varchar(1000);
  pExists		bigint;
  partTbl   	bigint;
  partExists 	bigint;
  sampleCt		bigint;
  idxExists 	bigint;
  logBase		bigint;
  pCount		integer;
  sCount		integer;
  tablespaceName	varchar(200);
  v_bio_experiment_id	bigint;
  
    --Audit variables
  newJobFlag integer(1);
  databaseName varchar(100);
  procedureName varchar(100);
  jobID bigint;
  stepCt bigint;
  
  --unmapped_patients exception;
  missing_platform	exception;
  missing_tissue	EXCEPTION;
  unmapped_platform exception;
  multiple_platform	exception;
  no_probeset_recs	exception;
  

  
	addNodes CURSOR FOR
	SELECT distinct t.leaf_node
          ,t.node_name
	from  wt_RNA_SEQ_nodes t
	where not exists
		 (select 1 from i2b2 x
		  where t.leaf_node = x.c_fullname);

 
--	cursor to define the path for delete_one_node  this will delete any nodes that are hidden after i2b2_create_concept_counts

  delNodes CURSOR FOR
  SELECT distinct c_fullname 
  from  i2b2
  where c_fullname like topNode || '%'
    and substring(c_visualattributes from 2 for 1) = 'H';
    --and c_visualattributes like '_H_';



BEGIN
	TrialID := upper(trial_id);
	secureStudy := upper(secure_study);
	
	if (secureStudy not in ('Y','N') ) then
		secureStudy := 'Y';
	end if;
	
	topNode := REGEXP_REPLACE('\' || top_node || '\','(\\){2,}', '\');	
	PERFORM length(topNode)-length(replace(topNode,'\','')) into topLevel ;
	
	if coalesce(data_type::text, '') = '' then
		dataType := 'R';
	else
		if data_type in ('R','T','L') then
			dataType := data_type;
		else
			dataType := 'R';
		end if;
	end if;
	
	logBase := log_base;
	sourceCd := upper(coalesce(source_cd,'STD'));

  --Set Audit Parameters
  newJobFlag := 0; -- False (Default)
  jobID := currentJobID;

  PERFORM sys_context('USERENV', 'CURRENT_SCHEMA') INTO databaseName ;
  procedureName := $$PLSQL_UNIT;

  --Audit JOB Initialization
  --If Job ID does not exist, then this is a single procedure run and we need to create it
  IF(coalesce(jobID::text, '') = '' or jobID < 1)
  THEN
    newJobFlag := 1; -- True
    cz_start_audit (procedureName, databaseName, jobID);
  END IF;
    	
	stepCt := 0;
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Starting i2b2_process_RNA_SEQ_data',0,stepCt,'Done');
	
	--	Get count of records in lt_src_RNA_SEQ_subj_samp_map
	
	select count(*) into sCount
	from lt_src_RNA_SEQ_subj_samp_map;
	
	--	check if all subject_sample map records have a platform, If not, abort run
	
	select count(*) into pCount
	from lt_src_RNA_SEQ_subj_samp_map
	where coalesce(platform::text, '') = '';
	
	if pCount > 0 then
		raise missing_platform;
	end if;
  
  	
	
	
	/*select count(*) into pCount
	from DE_gpl_info
	where platform in (select distinct m.platform from lt_src_RNA_SEQ_subj_samp_map m);
	*/
	/*if PCOUNT = 0 then
		RAISE UNMAPPED_platform;
	end if;*/
		
	--	check if all subject_sample map records have a tissue_type, If not, abort run
	
	select count(*) into pCount
	from lt_src_RNA_SEQ_subj_samp_map
	where coalesce(tissue_type::text, '') = '';
	
	if pCount > 0 then
		raise missing_tissue;
	end if;
	
	--	check if there are multiple platforms, if yes, then platform must be supplied in lt_src_RNA_SEQ_data
	
	select count(*) into pCount
	from (select sample_cd
		  from lt_src_RNA_SEQ_subj_samp_map
		  group by sample_cd
		  GROUP BY xd.concept_Cd
		  having Max(xf.valtype_cd) = 'N');
	
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName, HAVING count(distinct platform) > 1);
	
	if pCount > 0 then
		raise multiple_platform;
	end if;
		
	-- Get root_node from topNode
  
	PERFORM parse_nth_value(topNode, 2, '\') into RootNode ;
	
	select count(*) into pExists
	from table_access
	where c_name = rootNode;
	
	if pExists = 0 then
		i2b2_add_root_node(rootNode, jobId);
	end if;
	
	select c_hlevel into root_level
	from i2b2
	where c_name = RootNode;
	
	-- Get study name from topNode
  
	PERFORM parse_nth_value(topNode, topLevel, '\') into study_name ;
	
	--	Add any upper level nodes as needed
	
	tPath := REGEXP_REPLACE(replace(top_node,study_name,null),'(\\){2,}', '\');
	PERFORM length(tPath) - length(replace(tPath,'\',null)) into pCount ;

	if pCount > 2 then
		i2b2_fill_in_tree(null, tPath, jobId);
	end if;

	--	uppercase study_id in lt_src_RNA_SEQ_subj_samp_map in case curator forgot
	
	update lt_src_RNA_SEQ_subj_samp_map
	set trial_name=upper(trial_name);
	
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Uppercase trial_name in lt_src_RNA_SEQ_subj_samp_map',SQL%ROWCOUNT,stepCt,'Done');
	commit;	
	
	--	create records in patient_dimension for subject_ids if they do not exist
	--	format of sourcesystem_cd:  trial:[site:]subject_cd
	
	insert into patient_dimension
    ( patient_num,
      sex_cd,
      age_in_years_num,
      race_cd,
      update_date,
      download_date,
      import_date,
      sourcesystem_cd
    )
    PERFORM nextval('seq_patient_num')
		  ,x.sex_cd
		  ,x.age_in_years_num
		  ,x.race_cd
		  ,LOCALTIMESTAMP
		  ,LOCALTIMESTAMP
		  ,LOCALTIMESTAMP
		  ,x.sourcesystem_cd
	from (select distinct 'Unknown' as sex_cd,
				 0 as age_in_years_num,
				 null as race_cd,
				 regexp_replace(TrialID || ':' || s.site_id || ':' || s.subject_id,'(::){1,}', ':') as sourcesystem_cd
		 from lt_src_RNA_SEQ_subj_samp_map s
		    -- ,de_gpl_info g
		 where (s.subject_id IS NOT NULL AND s.subject_id::text <> '')
		   and s.trial_name = TrialID
		   and s.source_cd = sourceCD
		 --  and s.platform = g.platform
		   --and upper(g.marker_type) = 'GENE EXPRESSION'
		   and not exists
			  (select 1 from patient_dimension x
			   where x.sourcesystem_cd = 
				 regexp_replace(TrialID || ':' || s.site_id || ':' || s.subject_id,'(::){1,}', ':'))
		) x;
	
	pCount := SQL%ROWCOUNT;
	
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert subjects to patient_dimension',pCount,stepCt,'Done');
	commit;
	
	i2b2_create_security_for_trial(TrialId, secureStudy, jobID);

	--	Delete existing observation_fact data, will be repopulated
	
	delete from observation_fact obf
	where obf.concept_cd in
		 (select distinct x.concept_code
		  from de_subject_sample_mapping x
		  where x.trial_name = TrialId
		    and coalesce(x.source_cd,'STD') = sourceCD
		    and x.platform = 'RNA_AFFYMETRIX');

	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Delete data from observation_fact',SQL%ROWCOUNT,stepCt,'Done');
	commit;

	select count(*) into pExists
	from all_tables
	where table_name = 'DE_SUBJECT_RNA_DATA'
	  and partitioned = 'YES';
	  
	if pExists = 0 then
		--	dataset is not partitioned so must delete
		
		delete from de_subject_rna_data
		where trial_source = TrialId || ':' || sourceCd;
		stepCt := stepCt + 1;
		cz_write_audit(jobId,databaseName,procedureName,'Delete data from de_subject_rna_data',SQL%ROWCOUNT,stepCt,'Done');
		commit;
	else
		--	Create partition in de_subject_RNA_data if it doesn't exist else truncate partition
			
		select count(*)
			into pExists
			from all_tab_partitions
			where table_name = 'DE_SUBJECT_RNA_DATA'
			  and partition_name = TrialId || ':' || sourceCd;
			--10/30/2013  //modified
		if pExists = 0 then
					
			--	needed to add partition to de_subject_RNA_data

			sqlText := 'alter table deapp.de_subject_rna_data add PARTITION "' || TrialID || ':' || sourceCd || '"  VALUES (' || '''' || TrialID || ':' || sourceCd || '''' || ') ' ||
						   'NOLOGGING COMPRESS TABLESPACE "TRANSMART" ';
			EXECUTE(sqlText);
			stepCt := stepCt + 1;
			cz_write_audit(jobId,databaseName,procedureName,'Adding partition to de_subject_rna_data',0,stepCt,'Done');
				
		else
			sqlText := 'alter table deapp.de_subject_rna_data truncate partition "' || TrialID || ':' || sourceCd || '"';
			EXECUTE(sqlText);
			stepCt := stepCt + 1;
			cz_write_audit(jobId,databaseName,procedureName,'Truncating partition in de_subject_rna_data',0,stepCt,'Done');
		end if;
		
	end if;
		
	--	Cleanup any existing data in de_subject_sample_mapping.  

	delete from DE_SUBJECT_SAMPLE_MAPPING 
	where trial_name = TrialID 
	  and coalesce(source_cd,'STD') = sourceCd
	  and platform = 'RNA_AFFYMETRIX'; --Making sure only RNA_sequencing data is deleted
		  
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Delete trial from DEAPP de_subject_sample_mapping',SQL%ROWCOUNT,stepCt,'Done');

	commit;

--	truncate tmp node table

	EXECUTE('truncate table tm_wz.wt_RNA_SEQ_nodes');
	
--	load temp table with leaf node path, use temp table with distinct sample_type, ATTR2, platform, and title   this was faster than doing subselect
--	from wt_subject_RNA_sequencing_data

	EXECUTE('truncate table tm_wz.wt_RNA_SEQ_node_values');

	insert into wt_RNA_SEQ_node_values
	(category_cd
	,platform
	,tissue_type
	,attribute_1
	,attribute_2
	,title
	)
	PERFORM distinct a.category_cd
				   ,coalesce(a.platform,'GPL570')
				   ,coalesce(a.tissue_type,'Unspecified Tissue Type')
	               ,a.attribute_1
				   ,a.attribute_2
				   ,''--g.title
    from lt_src_RNA_SEQ_subj_samp_map a
	  --  ,de_gpl_info g 
	where a.trial_name = TrialID
	--  and nvl(a.platform,'GPL570') = g.platform
	  and a.source_cd = sourceCD
	 -- and a.platform = g.platform
	--  and upper(g.marker_type) = 'GENE EXPRESSION'
	--  and g.title = (select min(x.title) from de_gpl_info x where nvl(a.platform,'GPL570') = x.platform)
      -- and upper(g.organism) = 'HOMO SAPIENS'
	  ;
	--  and decode(dataType,'R',sign(a.intensity_value),1) = 1;	--	take all values when dataType T, only >0 for dataType R
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert node values into DEAPP wt_RNA_SEQ_node_values',SQL%ROWCOUNT,stepCt,'Done');
	commit;
	
	insert into wt_RNA_SEQ_nodes
	(leaf_node
	,category_cd
	,platform
	,tissue_type
	,attribute_1
    ,attribute_2
	,node_type
	)
	PERFORM distinct topNode || regexp_replace(replace(replace(replace(replace(replace(replace(
	       category_cd,'PLATFORM',title),'ATTR1',attribute_1),'ATTR2',attribute_2),'TISSUETYPE',tissue_type),'+','\'),'_',' ') || '\','(\\){2,}', '\') 
		  ,category_cd
		  ,platform as platform
		  ,tissue_type
		  ,attribute_1 as attribute_1
          ,attribute_2 as attribute_2
		  ,'LEAF'
	from  wt_RNA_SEQ_node_values;

    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create leaf nodes in DEAPP tmp_RNA_SEQ_nodes',SQL%ROWCOUNT,stepCt,'Done');
	commit;
	
	--	insert for platform node so platform concept can be populated
	
	insert into wt_RNA_SEQ_nodes
	(leaf_node
	,category_cd
	,platform
	,tissue_type
	,attribute_1
    ,attribute_2
	,node_type
	)
	PERFORM distinct topNode || regexp_replace(replace(replace(replace(replace(replace(replace(
	       substr(category_cd,1,instr(category_cd,'PLATFORM')+8),'PLATFORM',title),'ATTR1',attribute_1),'ATTR2',attribute_2),'TISSUETYPE',tissue_type),'+','\'),'_',' ') || '\',
		   '(\\){2,}', '\')
		  ,substr(category_cd,1,instr(category_cd,'PLATFORM')+8)
		  ,platform as platform
		  ,case when instr(substr(category_cd,1,instr(category_cd,'PLATFORM')+8),'TISSUETYPE') > 1 then tissue_type else null end as tissue_type
		  ,case when instr(substr(category_cd,1,instr(category_cd,'PLATFORM')+8),'ATTR1') > 1 then attribute_1 else null end as attribute_1
          ,case when instr(substr(category_cd,1,instr(category_cd,'PLATFORM')+8),'ATTR2') > 1 then attribute_2 else null end as attribute_2
		  ,'PLATFORM'
	from  wt_RNA_SEQ_node_values;
		   
    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create platform nodes in wt_RNA_SEQ_nodes',SQL%ROWCOUNT,stepCt,'Done');
	commit;
	
	--	insert for ATTR1 node so ATTR1 concept can be populated in tissue_type_cd
	
	insert into wt_RNA_SEQ_nodes
	(leaf_node
	,category_cd
	,platform
	,tissue_type
    ,attribute_1
	,attribute_2
	,node_type
	)
	PERFORM distinct topNode || regexp_replace(replace(replace(replace(replace(replace(replace(
	       substr(category_cd,1,instr(category_cd,'ATTR1')+5),'PLATFORM',title),'ATTR1',attribute_1),'ATTR2',attribute_2),'TISSUETYPE',tissue_type),'+','\'),'_',' ') || '\',
		   '(\\){2,}', '\')
		  ,substr(category_cd,1,instr(category_cd,'ATTR1')+5)
		  ,case when instr(substr(category_cd,1,instr(category_cd,'ATTR1')+5),'PLATFORM') > 1 then platform else null end as platform
		  ,case when instr(substr(category_cd,1,instr(category_cd,'ATTR1')+5),'TISSUETYPE') > 1 then tissue_type else null end as tissue_type
		  ,attribute_1 as attribute_1
          ,case when instr(substr(category_cd,1,instr(category_cd,'ATTR1')+5),'ATTR2') > 1 then attribute_2 else null end as attribute_2
		  ,'ATTR1'
	from  wt_RNA_SEQ_node_values
	where category_cd like '%ATTR1%'
	  and (attribute_1 IS NOT NULL AND attribute_1::text <> '');
		   
    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create ATTR1 nodes in wt_RNA_SEQ_nodes',SQL%ROWCOUNT,stepCt,'Done');
	commit;
	
	--	insert for ATTR2 node so ATTR2 concept can be populated in timepoint_cd

	insert into wt_RNA_SEQ_nodes
	(leaf_node
	,category_cd
	,platform
	,tissue_type
    ,attribute_1
	,attribute_2
	,node_type
	)
	PERFORM distinct topNode || regexp_replace(replace(replace(replace(replace(replace(replace(
	       substr(category_cd,1,instr(category_cd,'ATTR2')+5),'PLATFORM',title),'ATTR1',attribute_1),'ATTR2',attribute_2),'TISSUETYPE',tissue_type),'+','\'),'_',' ') || '\',
		   '(\\){2,}', '\')
		  ,substr(category_cd,1,instr(category_cd,'ATTR2')+5)
		  ,case when instr(substr(category_cd,1,instr(category_cd,'ATTR2')+5),'PLATFORM') > 1 then platform else null end as platform
		  ,case when instr(substr(category_cd,1,instr(category_cd,'ATTR1')+5),'TISSUETYPE') > 1 then tissue_type else null end as tissue_type
          ,case when instr(substr(category_cd,1,instr(category_cd,'ATTR2')+5),'ATTR1') > 1 then attribute_1 else null end as attribute_1
		  ,attribute_2 as attribute_2
		  ,'ATTR2'
	from  wt_RNA_SEQ_node_values
	where category_cd like '%ATTR2%'
	  and (attribute_2 IS NOT NULL AND attribute_2::text <> '');
		   
    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create ATTR2 nodes in wt_RNA_SEQ_nodes',SQL%ROWCOUNT,stepCt,'Done');
	commit;
	
	--	insert for tissue_type node so sample_type_cd can be populated 

	insert into wt_RNA_SEQ_nodes
	(leaf_node
	,category_cd
	,platform
	,tissue_type
	,attribute_1
    ,attribute_2
	,node_type
	)
	PERFORM distinct topNode || regexp_replace(replace(replace(replace(replace(replace(replace(
	       substr(category_cd,1,instr(category_cd,'TISSUETYPE')+10),'PLATFORM',title),'ATTR1',attribute_1),'ATTR2',attribute_2),'TISSUETYPE',tissue_type),'+','\'),'_',' ') || '\',
		   '(\\){2,}', '\')
		  ,substr(category_cd,1,instr(category_cd,'TISSUETYPE')+10)
		  ,case when instr(substr(category_cd,1,instr(category_cd,'TISSUETYPE')+10),'PLATFORM') > 1 then platform else null end as platform
		  ,tissue_type as tissue_type
		  ,case when instr(substr(category_cd,1,instr(category_cd,'TISSUETYPE')+10),'ATTR1') > 1 then attribute_1 else null end as attribute_1
          ,case when instr(substr(category_cd,1,instr(category_cd,'TISSUETYPE')+10),'ATTR2') > 1 then attribute_2 else null end as attribute_2
		  ,'TISSUETYPE'
	from  wt_RNA_SEQ_node_values
	where category_cd like '%TISSUETYPE%';

    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create ATTR2 nodes in wt_RNA_SEQ_nodes',SQL%ROWCOUNT,stepCt,'Done');
	commit;
				
	update wt_RNA_SEQ_nodes
	set node_name=parse_nth_value(leaf_node,length(leaf_node)-length(replace(leaf_node,'\',null)),'\');
		   
    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Updated node_name in DEAPP tmp_RNA_SEQ_nodes',SQL%ROWCOUNT,stepCt,'Done');
	commit;
		
--	add leaf nodes for RNA_sequencing data  The cursor will only add nodes that do not already exist.

	 FOR r_addNodes in addNodes Loop

    --Add nodes for all types (ALSO DELETES EXISTING NODE)

		i2b2_add_node(TrialID, r_addNodes.leaf_node, r_addNodes.node_name, jobId);
		stepCt := stepCt + 1;
		tText := 'Added Leaf Node: ' || r_addNodes.leaf_node || '  Name: ' || r_addNodes.node_name;
		
		cz_write_audit(jobId,databaseName,procedureName,tText,SQL%ROWCOUNT,stepCt,'Done');
		
		i2b2_fill_in_tree(TrialId, r_addNodes.leaf_node, jobID);

	END LOOP;  
	
--	update concept_cd for nodes, this is done to make the next insert easier

	update wt_RNA_SEQ_nodes t
	set concept_cd=(select c.concept_cd from concept_dimension c
	                where c.concept_path = t.leaf_node
				   )
    where exists
         (select 1 from concept_dimension x
	                where x.concept_path = t.leaf_node
				   )
	  and coalesce(t.concept_cd::text, '') = '';
	
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Update wt_RNA_SEQ_nodes with newly created concept_cds',SQL%ROWCOUNT,stepCt,'Done');
	commit;

  --Load the DE_SUBJECT_SAMPLE_MAPPING from wt_subject_RNA_sequencing_data

  --PATIENT_ID      = PATIENT_ID (SAME AS ID ON THE PATIENT_DIMENSION)
  --SITE_ID         = site_id
  --SUBJECT_ID      = subject_id
  --SUBJECT_TYPE    = NULL
  --CONCEPT_CODE    = from LEAF records in wt_RNA_SEQ_nodes
  --SAMPLE_TYPE    	= TISSUE_TYPE
  --SAMPLE_TYPE_CD  = concept_cd from TISSUETYPE records in wt_RNA_SEQ_nodes
  --TRIAL_NAME      = TRIAL_NAME
  --TIMEPOINT		= attribute_2
  --TIMEPOINT_CD	= concept_cd from ATTR2 records in wt_RNA_SEQ_nodes
  --TISSUE_TYPE     = attribute_1
  --TISSUE_TYPE_CD  = concept_cd from ATTR1 records in wt_RNA_SEQ_nodes
  --PLATFORM        = RNA_sequencing_AFFYMETRIX - this is required by ui code
  --PLATFORM_CD     = concept_cd from PLATFORM records in wt_RNA_SEQ_nodes
  --DATA_UID		= concatenation of concept_cd-patient_num
  --GPL_ID			= platform from wt_subject_RNA_SEQ_data
  --CATEGORY_CD		= category_cd that generated ontology
  --SAMPLE_ID		= id of sample (trial:S:[site_id]:subject_id:sample_cd) from patient_dimension, may be the same as patient_num
  --SAMPLE_CD		= sample_cd
  --SOURCE_CD		= sourceCd
  
  --ASSAY_ID        = generated by trigger

	insert into de_subject_sample_mapping
	(patient_id
	,site_id
	,subject_id
	,subject_type
	,concept_code
	,assay_id
	,sample_type
	,sample_type_cd
	,trial_name
	,timepoint
	,timepoint_cd
	,tissue_type
	,tissue_type_cd
	,platform
	,platform_cd
	,data_uid
	,gpl_id
	,sample_id
	,sample_cd
	,category_cd
	,source_cd
	,omic_source_study
	,omic_patient_id
    )
	PERFORM t.patient_id
		  ,t.site_id
		  ,t.subject_id
		  ,t.subject_type
		  ,t.concept_code
		  ,deapp.nextval('seq_assay_id')
		  ,t.sample_type
		  ,t.sample_type_cd
		  ,t.trial_name
		  ,t.timepoint
		  ,t.timepoint_cd
		  ,t.tissue_type
		  ,t.tissue_type_cd
		  ,t.platform
		  ,t.platform_cd
		  ,t.data_uid
		  ,t.gpl_id
		  ,t.sample_id
		  ,t.sample_cd
		  ,t.category_cd
		  ,t.source_cd
		  ,t.omic_source_study
		  ,t.omic_patient_id
	from (select distinct b.patient_num as patient_id
			  ,a.site_id
			  ,a.subject_id
			  ,null as subject_type
			  ,ln.concept_cd as concept_code
			  ,a.tissue_type as sample_type
			  ,ttp.concept_cd as sample_type_cd
			  ,a.trial_name
			  ,a.attribute_2 as timepoint
			  ,a2.concept_cd as timepoint_cd
			  ,a.attribute_1 as tissue_type
			  ,a1.concept_cd as tissue_type_cd
			  ,'RNA_AFFYMETRIX' as platform
			  ,pn.concept_cd as platform_cd
			  ,ln.concept_cd || '-' || to_char(b.patient_num) as data_uid
			  ,a.platform as gpl_id
			  ,coalesce(sid.patient_num,b.patient_num) as sample_id
			  ,a.sample_cd
			  ,coalesce(a.category_cd,'Biomarker_Data+RNA_SEQ+PLATFORM+TISSUETYPE+ATTR1+ATTR2') as category_cd
			  ,a.source_cd
			  ,TrialId as omic_source_study
			  ,b.patient_num as omic_patient_id
		from lt_src_RNA_SEQ_subj_samp_map a		
		--Joining to Pat_dim to ensure the ID's match. If not I2B2 won't work.
		inner join patient_dimension b
		  on regexp_replace(TrialID || ':' || a.site_id || ':' || a.subject_id,'(::){1,}', ':') = b.sourcesystem_cd
		inner join wt_RNA_SEQ_nodes ln
			on a.platform = ln.platform
			and a.tissue_type = ln.tissue_type
			and coalesce(a.attribute_1,'@') = coalesce(ln.attribute_1,'@')
			and coalesce(a.attribute_2,'@') = coalesce(ln.attribute_2,'@')
			and ln.node_type = 'LEAF'
		inner join wt_RNA_SEQ_nodes pn
			on a.platform = pn.platform
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'PLATFORM')+8),'TISSUETYPE') > 1 then a.tissue_type else '@' end = coalesce(pn.tissue_type,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'PLATFORM')+8),'ATTR1') > 1 then a.attribute_1 else '@' end = coalesce(pn.attribute_1,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'PLATFORM')+8),'ATTR2') > 1 then a.attribute_2 else '@' end = coalesce(pn.attribute_2,'@')
			and pn.node_type = 'PLATFORM'	  
		left outer join wt_RNA_SEQ_nodes ttp
			on a.tissue_type = ttp.tissue_type
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'TISSUETYPE')+10),'PLATFORM') > 1 then a.platform else '@' end = coalesce(ttp.platform,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'TISSUETYPE')+10),'ATTR1') > 1 then a.attribute_1 else '@' end = coalesce(ttp.attribute_1,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'TISSUETYPE')+10),'ATTR2') > 1 then a.attribute_2 else '@' end = coalesce(ttp.attribute_2,'@')
			and ttp.node_type = 'TISSUETYPE'		  
		left outer join wt_RNA_SEQ_nodes a1
			on a.attribute_1 = a1.attribute_1
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'ATTR1')+5),'PLATFORM') > 1 then a.platform else '@' end = coalesce(a1.platform,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'ATTR1')+5),'TISSUETYPE') > 1 then a.tissue_type else '@' end = coalesce(a1.tissue_type,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'ATTR1')+5),'ATTR2') > 1 then a.attribute_2 else '@' end = coalesce(a1.attribute_2,'@')
			and a1.node_type = 'ATTR1'		  
		left outer join wt_RNA_SEQ_nodes a2
			on a.attribute_2 = a1.attribute_2
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'ATTR2')+5),'PLATFORM') > 1 then a.platform else '@' end = coalesce(a2.platform,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'ATTR2')+5),'TISSUETYPE') > 1 then a.tissue_type else '@' end = coalesce(a2.tissue_type,'@')
			and case when instr(substr(a.category_cd,1,instr(a.category_cd,'ATTR2')+5),'ATTR1') > 1 then a.attribute_1 else '@' end = coalesce(a2.attribute_1,'@')
			and a2.node_type = 'ATTR2'			  
		left outer join patient_dimension sid
			on  regexp_replace(TrialId || 'S:' || a.site_id || ':' || a.subject_id || ':' || a.sample_cd,
							  '(::){1,}', ':') = sid.sourcesystem_cd
		where a.trial_name = TrialID
		  and a.source_cd = sourceCD
		  and  (ln.concept_cd IS NOT NULL AND ln.concept_cd::text <> '')) t;

	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert trial into DEAPP de_subject_sample_mapping',SQL%ROWCOUNT,stepCt,'Done');

  commit;
--	recreate de_subject_sample_mapping indexes

	--execute immediate('create index de_subject_smpl_mpng_idx1 on de_subject_sample_mapping(timepoint, patient_id, trial_name) parallel nologging'); 
	--execute immediate('create index de_subject_smpl_mpng_idx2 on de_subject_sample_mapping(patient_id, timepoint_cd, platform_cd, assay_id, trial_name) parallel nologging'); 
	--execute immediate('create bitmap index de_subject_smpl_mpng_idx3 on de_subject_sample_mapping(sample_type_cd) parallel nologging');
	--execute immediate('create index de_subject_smpl_mpng_idx4 on de_subject_sample_mapping(gpl_id) parallel nologging');
	--execute immediate('create index de_subject_smpl_mpng_idx4 on de_subject_sample_mapping(platform, gpl_id) parallel nologging');
    --stepCt := stepCt + 1;
	--cz_write_audit(jobId,databaseName,procedureName,'Recreate indexes on DEAPP de_subject_sample_mapping',0,stepCt,'Done');

--	Insert records for patients and samples into observation_fact

	insert into observation_fact
    (patient_num
	,concept_cd
	,modifier_cd
	,valtype_cd
	,tval_char
	,nval_num
	,sourcesystem_cd
	,import_date
	,valueflag_cd
	,provider_id
	,location_cd
	,units_cd
        ,INSTANCE_NUM
    )
    PERFORM distinct m.patient_id
		  ,m.concept_code
		  ,'@'
		  ,'T' -- Text data type
		  ,'E'  --Stands for Equals for Text Types
		  ,null	--	not numeric for RNA_sequencing
		  ,m.trial_name
		  ,LOCALTIMESTAMP
		  ,'@'
		  ,'@'
		  ,'@'
		  ,'' -- no units available
                  ,1
    from  de_subject_sample_mapping m
    where m.trial_name = TrialID 
	  and m.source_cd = sourceCD
      and m.platform = 'RNA_AFFYMETRIX';
	  
    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert patient facts into I2B2DEMODATA observation_fact',SQL%ROWCOUNT,stepCt,'Done');

    commit;
  
	--	Insert sample facts 
	
	insert into observation_fact
    (patient_num
	,concept_cd
	,modifier_cd
	,valtype_cd
	,tval_char
	,nval_num
	,sourcesystem_cd
	,import_date
	,valueflag_cd
	,provider_id
	,location_cd
	,units_cd
        ,INSTANCE_NUM
    )
    PERFORM distinct m.sample_id
		  ,m.concept_code
		  ,m.trial_name
		  ,'T' -- Text data type
		  ,'E'  --Stands for Equals for Text Types
		  ,null	--	not numeric for RNA_sequencing
		  ,m.trial_name
		  ,LOCALTIMESTAMP
		  ,'@'
		  ,'@'
		  ,'@'
		  ,'' -- no units available
                  ,1
    from  de_subject_sample_mapping m
    where m.trial_name = TrialID 
	  and m.source_cd = sourceCd
      and m.platform = 'RNA_AFFYMETRIX'
	  and m.patient_id != m.sample_id;
	  
    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert sample facts into I2B2DEMODATA observation_fact',SQL%ROWCOUNT,stepCt,'Done');

    commit;
    
	--Update I2b2 for correct data type
	
	update i2b2 t
	set c_columndatatype = 'T', c_metadataxml = null, c_visualattributes='FA'
	where t.c_basecode in (select distinct x.concept_cd from wt_RNA_SEQ_nodes x);
  
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Initialize data_type and xml in i2b2',SQL%ROWCOUNT,stepCt,'Done');
	commit;
	
	update i2b2
	SET c_columndatatype = 'N',
      --Static XML String
		c_metadataxml = '<?xml version="1.0"?><ValueMetadata><Version>3.02</Version><CreationDateTime>08/14/2008 01:22:59</CreationDateTime><TestID></TestID><TestName></TestName><DataType>PosFloat</DataType><CodeType></CodeType><Loinc></Loinc><Flagstouse></Flagstouse><Oktousevalues>Y</Oktousevalues><MaxStringLength></MaxStringLength><LowofLowValue>0</LowofLowValue><HighofLowValue>0</HighofLowValue><LowofHighValue>100</LowofHighValue>100<HighofHighValue>100</HighofHighValue><LowofToxicValue></LowofToxicValue><HighofToxicValue></HighofToxicValue><EnumValues></EnumValues><CommentsDeterminingExclusion><Com></Com></CommentsDeterminingExclusion><UnitValues><NormalUnits>ratio</NormalUnits><EqualUnits></EqualUnits><ExcludingUnits></ExcludingUnits><ConvertingUnits><Units></Units><MultiplyingFactor></MultiplyingFactor></ConvertingUnits></UnitValues><Analysis><Enums /><Counts /><New /></Analysis></ValueMetadata>'
	where c_basecode IN (
		  SELECT xd.concept_cd
		  from wt_RNA_SEQ_nodes xd
			  ,observation_fact xf
		  where xf.concept_cd = xd.concept_cd
		  procedureName,'Update c_columndatatype and c_metadataxml for numeric data types in I2B2METADATA i2b2',SQL%ROWCOUNT,stepCt,'Done');
	commit;
  
/*
	--UPDATE VISUAL ATTRIBUTES for Leaf Active (Default is folder)
	update i2b2 a
    set c_visualattributes = 'LA'
    where 1 = (
      select count(*)
      from i2b2 b
      where b.c_fullname like (a.c_fullname || '%'))
      and c_fullname like '%' || topNode || '%';
*/

	--UPDATE VISUAL ATTRIBUTES for Leaf Active (Default is folder)
	update i2b2 a
    set c_visualattributes = 'LAH'
	where a.c_basecode in (select distinct x.concept_code from de_subject_sample_mapping x
						   where x.trial_name = TrialId
						     and x.platform = 'RNA_AFFYMETRIX'
							 and (x.concept_code IS NOT NULL AND x.concept_code::text <> ''));
	  
	stepCt := stepCt + 1;
     
	cz_write_audit(jobId,databaseName,procedureName,'Update visual attributes for leaf nodes in I2B2METADATA i2b2',SQL%ROWCOUNT,stepCt,'Done');
  
	COMMIT;
   insert into probeset_deapp
   (
   probeset,
   platform 
   )select distinct s.probeset
               ,m.platform               
            from lt_src_rna_seq_data s,
                 lt_src_RNA_SEQ_subj_samp_map m  
                 where s.trial_name=m.trial_name  
                   and not exists
		 (select 1 from probeset_deapp x
		  where m.platform = x.platform
		    and s.probeset = x.probeset);
                    
                    stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert new probesets into probeset_deapp',SQL%ROWCOUNT,stepCt,'Done');
        
        

  --Build concept Counts
  --Also marks any i2B2 records with no underlying data as Hidden, need to do at Trial level because there may be multiple platform and there is no longer
  -- a unique top-level node for RNA_sequencing data
  
    i2b2_create_concept_counts(topNode ,jobID );
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Create concept counts',0,stepCt,'Done');
	
	--	delete each node that is hidden

	 FOR r_delNodes in delNodes Loop

    --	deletes hidden nodes for a trial one at a time

		i2b2_delete_1_node(r_delNodes.c_fullname);
		stepCt := stepCt + 1;
		tText := 'Deleted node: ' || r_delNodes.c_fullname;
		
		cz_write_audit(jobId,databaseName,procedureName,tText,SQL%ROWCOUNT,stepCt,'Done');

	END LOOP;  	

  --Reload Security: Inserts one record for every I2B2 record into the security table

    i2b2_load_security_data(jobId);
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Load security data',0,stepCt,'Done');

--	tag data with probeset_id from reference.probeset_deapp
  
	EXECUTE ('truncate table tm_wz.wt_subject_rna_probeset');
	
	--	note: assay_id represents a unique subject/site/sample


	insert into wt_subject_rna_probeset
	(probeset_id
--	,expr_id
	,intensity_value
	,patient_id
--	,sample_cd
--	,subject_id
	,trial_name
	,assay_id
	) select md.probeset
--		  ,sd.sample_cd
		 , avg(md.intensity_value) as intensity_value
		  ,sd.patient_id
--		  ,sd.sample_cd
--		  ,sd.subject_id
		 ,TrialId as trial_name
		  ,sd.assay_id
	from de_subject_sample_mapping sd
		,lt_src_RNA_SEQ_data md   
		,probeset_deapp gs
	where sd.sample_cd = md.expr_id
	  and sd.platform = 'RNA_AFFYMETRIX'
	  and sd.trial_name = TrialId
	  and sd.source_cd = sourceCd
	--  and sd.gpl_id = gs.platform
	and md.probeset = gs.probeset
	  and decode(dataType,'R',sign(md.intensity_value),1) = 1  --	take only >0 for dataType R
          --and rownum = 1
	group by md.probeset
		--  ,sd.sample_cd
		  ,sd.patient_id
		--  ,sd.sample_cd
		--  ,sd.subject_id
		  ,sd.assay_id;

	pExists := SQL%ROWCOUNT;
	
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'Insert into DEAPP wt_subject_rna_probeset',SQL%ROWCOUNT,stepCt,'Done');

	commit;		
	
	/*if pExists = 0 then
		raise no_probeset_recs;
	end if;*/

	--	insert into de_subject_rna_data when dataType is T (transformed)

	if dataType = 'T' then
		insert into de_subject_rna_data
		(trial_source
		,probeset_id
		,assay_id
		,patient_id
		--,sample_id
		--,subject_id
		,trial_name
		,zscore
		)
		PERFORM TrialId || ':' || sourceCd
			  ,probeset_id
			  ,assay_id
			  ,patient_id
			  --,sample_id
			  --,subject_id
			  ,trial_name
			  ,case when intensity_value < -2.5
			        then -2.5
					when intensity_value > 2.5
					then 2.5
					else intensity_value
			   end as zscore
		from wt_subject_rna_probeset 
		where trial_name = TrialID;
		stepCt := stepCt + 1;
		cz_write_audit(jobId,databaseName,procedureName,'Insert transformed into DEAPP de_subject_rna_data',SQL%ROWCOUNT,stepCt,'Done');

		commit;	
	else
		
	--	Calculate ZScores and insert data into de_subject_rna_data.  The 'L' parameter indicates that the RNA_sequencing  data will be selected from
	--	wt_subject_RNA_seq_probeset as part of a Load.  

		if dataType = 'R' or dataType = 'L' then
			I2B2_RNA_SEQ_ZSCORE_CALC(TrialID,'L',jobId,dataType,logBase,sourceCD);
			stepCt := stepCt + 1;
			cz_write_audit(jobId,databaseName,procedureName,'Calculate Z-Score',0,stepCt,'Done');
			commit;
		end if;
	
	end if;
	
    ---Cleanup OVERALL JOB if this proc is being run standalone
	
	stepCt := stepCt + 1;
	cz_write_audit(jobId,databaseName,procedureName,'End i2b2_process_RNA_SEQ_data',0,stepCt,'Done');

	IF newJobFlag = 1
	THEN
		cz_end_audit (jobID, 'SUCCESS');
	END IF;
	
	PERFORM 0 into rtn_code ;

	EXCEPTION
	--when unmapped_patients then
	--	cz_write_audit(jobId,databasename,procedurename,'No site_id/subject_id mapped to patient_dimension',1,stepCt,'ERROR');
	--	cz_error_handler(jobid,procedurename);
	--	cz_end_audit (jobId,'FAIL');
	when missing_platform then
		cz_write_audit(jobId,databasename,procedurename,'Platform data missing from one or more subject_sample mapping records',1,stepCt,'ERROR');
		cz_error_handler(jobid,procedurename);
		cz_end_audit (jobId,'FAIL');
		PERFORM 161 into rtn_code ;
	when missing_tissue then
		cz_write_audit(jobId,databasename,procedurename,'Tissue Type data missing from one or more subject_sample mapping records',1,stepCt,'ERROR');
		cz_error_handler(jobid,procedurename);
		CZ_END_AUDIT (JOBID,'FAIL');
		PERFORM 162 into rtn_code ;
	/*when unmapped_platform then
		cz_write_audit(jobId,databasename,procedurename,'Platform not found in de_RNA_annotation',1,stepCt,'ERROR');
		CZ_ERROR_HANDLER(JOBID,PROCEDURENAME);
		cz_end_audit (jobId,'FAIL');
		select 163 into rtn_code from dual;*/
	when multiple_platform then
		cz_write_audit(jobId,databasename,procedurename,'Multiple platforms for sample_cd in lt_src_RNA_SEQ_subj_samp_map',1,stepCt,'ERROR');
		CZ_ERROR_HANDLER(JOBID,PROCEDURENAME);
		cz_end_audit (jobId,'FAIL');
		PERFORM 164 into rtn_code ;
	/*when no_probeset_recs then
		cz_write_audit(jobId,databasename,procedurename,'Unable to match probesets to platform in probeset_deapp',1,stepCt,'ERROR');
		CZ_ERROR_HANDLER(JOBID,PROCEDURENAME);
		cz_end_audit (jobId,'FAIL');
		select 165 into rtn_code from dual;*/
	WHEN OTHERS THEN
		--Handle errors.
		cz_error_handler (jobID, procedureName);
		--End Proc
		cz_end_audit (jobID, 'FAIL');
		PERFORM 166 into rtn_code ;
END;
 
$_$;


ALTER FUNCTION deapp.i2b2_process_rna_seq_data(trial_id character varying, top_node character varying, data_type character varying, source_cd character varying, log_base numeric, secure_study character varying, currentjobid numeric, OUT rtn_code numeric) OWNER TO deapp;

--
-- Name: protein_comparison_qry(character varying, character varying, character varying, character varying, refcursor); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.protein_comparison_qry(patient_ids character varying, sample_types character varying, pathway_uid1 character varying, timepoints character varying, INOUT cv_1 refcursor) RETURNS refcursor
    LANGUAGE plpgsql
    AS $$
DECLARE

  --Counter to check if samples exist.
  sample_record_count integer;
  timepoint_count integer;


BEGIN
  -------------------------------------------------------------------------------
   -- Returns PROBESET, GENE_SYMBOL, REFSEQ, LOG10_INTENSITY, PVALUE, PATIENT_ID, ASSAY_ID
   -- result set for specifed Pathways filtered
   -- by Sample Types and Subject_id.
   -- KCR@20090206 - First rev.
   -- KCR@20090212 - Second rev.  Changed logic so that if Sampel is not found it returns Zero.
   -- KCR@20090206 - Third rev. Using Collections to hold parsed values instead of DB table.
   -- HX@20090317  - Replace pathway_name with pathway_uid
   -- HX@20090318  - Add pathway_uid column into DE_PATHWAY and populate data
   --                from BIOMART.BIO_DATA_UID

   -- 2009-05-04: replace refseq with probeset
   -- 2009-05-26: remove GENE_SYMBOL's concatenation and change probeset to refseq
   -- 2009-05-29: change LOG10_INTENSITY to LOG2_INTENSITY
   -- 2009-06-18: add raw_intensity and change log2_intensity to zscore
   -- 2009-06-23: Add timepoints as a parameter
   -------------------------------------------------------------------------------

-- Check if sample Types Exist
SELECT COUNT(*)
  INTO sample_record_count
  FROM DE_SUBJECT_SAMPLE_MAPPING
    WHERE concept_code IN
      --Passing string to Text parser Function
      (SELECT * from table(text_parser(sample_types)));

 --Sample Record Count is invalid or non existent.
  IF sample_record_count = 0
    THEN
    BEGIN

      select count(*) into timepoint_count
      from table(text_parser(timepoints));

      if timepoint_count=0 then

          OPEN cv_1 FOR
          SELECT distinct a.component, a.GENE_SYMBOL, a.zscore,
                 a.patient_ID, a.ASSAY_ID, a.intensity
              FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids)))
          order by a.GENE_SYMBOL, a.COMPONENT, a.patient_ID ;

      else

          OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 a.patient_ID, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint and
                a.assay_id=b.assay_id
          order by a.GENE_SYMBOL, a.COMPONENT, a.patient_ID;
      end if;
    END;

  --else use all filters (If Subject is non existent or invalid, then return
  ELSE
    BEGIN

    if timepoint_count=0 then
      OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL,  a.zscore,
                 a.patient_ID, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids)))
          order by a.GENE_SYMBOL, a.COMPONENT, a.patient_ID;

    else

      OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 a.patient_ID, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint
          order by a.GENE_SYMBOL, a.COMPONENT, a.patient_ID;

    end if;

    END;
  END IF;
END protein_comparison_qry;
 
$$;


ALTER FUNCTION deapp.protein_comparison_qry(patient_ids character varying, sample_types character varying, pathway_uid1 character varying, timepoints character varying, INOUT cv_1 refcursor) OWNER TO deapp;

--
-- Name: protein_dbl_comp_qry(character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, refcursor); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.protein_dbl_comp_qry(patient_ids1 character varying, patient_ids2 character varying, sample_types1 character varying, sample_types2 character varying, pathway_uid1 character varying, pathway_uid2 character varying, timepoints1 character varying, timepoints2 character varying, INOUT cv_1 refcursor) RETURNS refcursor
    LANGUAGE plpgsql
    AS $$
DECLARE

  sample_record_count1 integer;
  sample_record_count2 integer;
  timepoint_count1 integer;
  timepoint_count2 integer;

BEGIN
-- Check if sample Types Exist
SELECT COUNT(*)
  INTO sample_record_count1
  FROM DE_SUBJECT_SAMPLE_MAPPING
    WHERE concept_code IN
      --Passing string to Text parser Function
      (SELECT * from table(text_parser(sample_types1)));

SELECT COUNT(*)
  INTO sample_record_count2
  FROM DE_SUBJECT_SAMPLE_MAPPING
    WHERE concept_code IN
      --Passing string to Text parser Function
      (SELECT * from table(text_parser(sample_types2)));

 --Sample Record Count is invalid or non existent.
  IF sample_record_count1 = 0
    THEN
    BEGIN

      select count(*) into timepoint_count1
      from table(text_parser(timepoints1));

      select count(*) into timepoint_count2
      from table(text_parser(timepoints2));

      if ((timepoint_count1=0) and (timepoint_count2=0)) then

          OPEN cv_1 FOR
          SELECT distinct a.component, a.GENE_SYMBOL, a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
              FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1)))
          UNION
          SELECT distinct a.component, a.GENE_SYMBOL, a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
              FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2)))
          order by GENE_SYMBOL, COMPONENT, patient_ID ;
      elsif ((timepoint_count1>0) and (timepoint_count2=0)) then
          OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints1))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint and
                a.assay_id=b.assay_id
          UNION
          SELECT distinct a.component, a.GENE_SYMBOL, a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
              FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2)))
          order by GENE_SYMBOL, COMPONENT, patient_ID ;

      elsif ((timepoint_count1=0) and (timepoint_count2>0)) then
          OPEN cv_1 FOR
          SELECT distinct a.component, a.GENE_SYMBOL, a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
              FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1)))
          UNION
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints2))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint and
                a.assay_id=b.assay_id
          order by GENE_SYMBOL, COMPONENT, patient_ID ;

      else

          OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints1))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint and
                a.assay_id=b.assay_id
          UNION
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints2))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint and
                a.assay_id=b.assay_id
          order by GENE_SYMBOL, COMPONENT, patient_ID;
      end if;
    END;

  --else use all filters (If Subject is non existent or invalid, then return
  ELSE
    BEGIN

    if ((timepoint_count1=0) and (timepoint_count2=0)) then
      OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL,  a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types1))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1)))
          UNION
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL,  a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types2))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2)))
          order by GENE_SYMBOL, COMPONENT, patient_ID;
    elsif ((timepoint_count1>0) and (timepoint_count2=0)) then
      OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types1))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints1))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint
          UNION
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL,  a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types2))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2)))
          order by GENE_SYMBOL, COMPONENT, patient_ID;
    elsif ((timepoint_count1=0) and (timepoint_count2>0)) then
      OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL,  a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types1))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1)))
          UNION
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S2_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types2))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints2))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint
          order by GENE_SYMBOL, COMPONENT, patient_ID;
    else

      OPEN cv_1 FOR
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S1_' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types1))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids1))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints1))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint
          UNION
          SELECT distinct a.COMPONENT, a.GENE_SYMBOL, a.zscore,
                 'S2' || a.patient_ID as patient_id, a.ASSAY_ID, a.intensity
          FROM DE_SUBJECT_PROTEIN_DATA a, DE_pathway_gene c, de_pathway p,
               DE_subject_sample_mapping b
          where p.pathway_uid= pathway_uid1 and c.pathway_id= p.id and
                a.gene_symbol = c.gene_symbol and
                a.PATIENT_ID = b.PATIENT_ID and a.assay_id = b.assay_id and
                b.concept_code IN (SELECT * from table(text_parser(sample_types2))) and
                a.patient_id IN (SELECT * from table(text_parser(patient_ids2))) and
                b.TIMEPOINT_CD IN (SELECT * from table(text_parser(timepoints2))) and
                a.PATIENT_ID=b.patient_id and a.timepoint=b.timepoint
          order by GENE_SYMBOL, COMPONENT, patient_ID;

    end if;

    END;
  END IF;

END PROTEIN_DBL_COMP_QRY;
 
 
 
$$;


ALTER FUNCTION deapp.protein_dbl_comp_qry(patient_ids1 character varying, patient_ids2 character varying, sample_types1 character varying, sample_types2 character varying, pathway_uid1 character varying, pathway_uid2 character varying, timepoints1 character varying, timepoints2 character varying, INOUT cv_1 refcursor) OWNER TO deapp;

--
-- Name: rbm_comparison_qry(character varying, character varying, character varying, refcursor); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.rbm_comparison_qry(patient_ids character varying, concept_cds character varying, timepoints character varying, INOUT cv_1 refcursor) RETURNS refcursor
    LANGUAGE plpgsql
    AS $$
DECLARE


  tp_cnt integer;

BEGIN
  -------------------------------------------------------------------------------
   -- Returns ANTIGENE, GENE_SYMBOL, VALUE, PATIENT_ID, ASSAY_ID
   -- result set for specifed Pathways filtered
   -- by Sample Types and Subject_id.
   -- HX@20090317 - First rev.
   -- HX@20090319 - Rename DE_RBM_DATA to DE_SUBJECT_RBM_DATA and move CONCEPT_CD
   --     from DE_RBM_DATA to DE_SUBJECT_SAMPLE_MAPPING, and make the query
   --     and table names are  consistent with MicroArray's one

   -- 2009-06-18: change normalized_value to zscore
   -- 2009-06-23: Add timepoints as parameter
   -- 2009-06-30: Remove t1.zscore is not null condition
   -------------------------------------------------------------------------------

   select count(*) into tp_cnt
   from de_subject_sample_mapping
   where timepoint_cd in
       (select * from table(text_parser(timepoints)));


   if (tp_cnt=0) then
        OPEN cv_1 FOR
           SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                 t1.patient_id, t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1
           where -- t1.zscore is not null and
              t1.patient_id IN (SELECT * from table(text_parser(patient_ids)))
               order by t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.patient_id;
   else
        OPEN cv_1 FOR
           SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                  t1.patient_id, t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1, de_subject_sample_mapping t2
           where --t1.zscore is not null and
              t2.patient_id IN (SELECT * from table(text_parser(patient_ids))) and
              t2.timepoint_cd in (select * from TABLE(text_parser(timepoints))) and
              t1.data_uid = t2.data_uid and t1.assay_id=t2.assay_id
              order by t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.patient_id;
    end if;
END RBM_comparison_qry;
 
 
 
$$;


ALTER FUNCTION deapp.rbm_comparison_qry(patient_ids character varying, concept_cds character varying, timepoints character varying, INOUT cv_1 refcursor) OWNER TO deapp;

--
-- Name: rbm_dbl_comp_qry(character varying, character varying, character varying, character varying, character varying, character varying, refcursor); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.rbm_dbl_comp_qry(patient_ids1 character varying, patient_ids2 character varying, concept_cds1 character varying, concept_cds2 character varying, timepoints1 character varying, timepoints2 character varying, INOUT cv_1 refcursor) RETURNS refcursor
    LANGUAGE plpgsql
    AS $$
DECLARE

  tp_cnt1 integer;
  tp_cnt2 integer;

BEGIN
   select count(*) into tp_cnt1
   from de_subject_sample_mapping
   where timepoint_cd in
       (select * from table(text_parser(timepoints1)));

   select count(*) into tp_cnt2
   from de_subject_sample_mapping
   where timepoint_cd in
       (select * from table(text_parser(timepoints2)));


   if ((tp_cnt1=0) and (tp_cnt2=0)) then
        OPEN cv_1 FOR
           SELECT distinct ANTIGEN_NAME, GENE_SYMBOL, zscore as value,
                 'S1_' || patient_id as patient_id, assay_id
           FROM DE_SUBJECT_RBM_DATA
           where -- t1.zscore is not null and
              patient_id IN (SELECT * from table(text_parser(patient_ids1)))
          UNION
          SELECT distinct ANTIGEN_NAME, GENE_SYMBOL, zscore as value,
                 'S2_' || patient_id as patient_id, assay_id
           FROM DE_SUBJECT_RBM_DATA t1
           where -- t1.zscore is not null and
              patient_id IN (SELECT * from table(text_parser(patient_ids2)))
               order by ANTIGEN_NAME, GENE_SYMBOL, patient_id;
   elsif ((tp_cnt1=0) and (tp_cnt2 > 0)) then
           OPEN cv_1 FOR
           SELECT distinct ANTIGEN_NAME, GENE_SYMBOL, zscore as value,
                 'S1_' || patient_id as patient_id, assay_id
           FROM DE_SUBJECT_RBM_DATA t1
           where -- zscore is not null and
              patient_id IN (SELECT * from table(text_parser(patient_ids1)))
          UNION
           SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                  'S1_' || t1.patient_id as patient_id, t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1, de_subject_sample_mapping t2
           where --t1.zscore is not null and
              t2.patient_id IN (SELECT * from table(text_parser(patient_ids2))) and
              t2.timepoint_cd in (select * from TABLE(text_parser(timepoints2))) and
              t1.data_uid = t2.data_uid and t1.assay_id=t2.assay_id
              order by ANTIGEN_NAME, GENE_SYMBOL, patient_id;

   elsif ((tp_cnt1 > 0 ) and (tp_cnt2 = 0)) then
        OPEN cv_1 FOR
           SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                  'S1_' || t1.patient_id as patient_id  , t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1, de_subject_sample_mapping t2
           where --t1.zscore is not null and
              t2.patient_id IN (SELECT * from table(text_parser(patient_ids1))) and
              t2.timepoint_cd in (select * from TABLE(text_parser(timepoints1))) and
              t1.data_uid = t2.data_uid and t1.assay_id=t2.assay_id
          UNION
          SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                 'S2_' || patient_id as patient_id, t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1
           where -- t1.zscore is not null and
              t1.patient_id IN (SELECT * from table(text_parser(patient_ids2)))
               order by ANTIGEN_NAME, GENE_SYMBOL, patient_id;
   else
        OPEN cv_1 FOR
          SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                  'S1_' || t1.patient_id as patient_id, t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1, de_subject_sample_mapping t2
           where --t1.zscore is not null and
              t2.patient_id IN (SELECT * from table(text_parser(patient_ids1))) and
              t2.timepoint_cd in (select * from TABLE(text_parser(timepoints1))) and
              t1.data_uid = t2.data_uid and t1.assay_id=t2.assay_id
          UNION
          SELECT distinct t1.ANTIGEN_NAME, t1.GENE_SYMBOL, t1.zscore as value,
                  'S2_' || t1.patient_id as patient_id, t1.assay_id
           FROM DE_SUBJECT_RBM_DATA t1, de_subject_sample_mapping t2
           where --t1.zscore is not null and
              t2.patient_id IN (SELECT * from table(text_parser(patient_ids2))) and
              t2.timepoint_cd in (select * from TABLE(text_parser(timepoints2))) and
              t1.data_uid = t2.data_uid and t1.assay_id=t2.assay_id
              order by ANTIGEN_NAME, GENE_SYMBOL, patient_id;
    end if;

END RBM_DBL_COMP_QRY;
 
$$;


ALTER FUNCTION deapp.rbm_dbl_comp_qry(patient_ids1 character varying, patient_ids2 character varying, concept_cds1 character varying, concept_cds2 character varying, timepoints1 character varying, timepoints2 character varying, INOUT cv_1 refcursor) OWNER TO deapp;

--
-- Name: tf_de_parent_cd_trg(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_de_parent_cd_trg() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  SELECT nextval('deapp.de_parent_cd_seq')
  INTO new.parent_cd
  ;
RETURN NEW;
END;
$$;


ALTER FUNCTION deapp.tf_de_parent_cd_trg() OWNER TO deapp;

--
-- Name: tf_rbm_id_trigger(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_rbm_id_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.ID is null then
 select nextval('deapp.RBM_ANNOTATION_ID') into NEW.ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_rbm_id_trigger() OWNER TO deapp;

--
-- Name: tf_trg_de_gene_source_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_gene_source_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.GENE_SOURCE_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.GENE_SOURCE_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_gene_source_id() OWNER TO deapp;

--
-- Name: tf_trg_de_mrna_annotation_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_mrna_annotation_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.DE_MRNA_ANNOTATION_ID is null then
 select nextval('deapp.SEQ_DE_MRNA_ANNOTATION_ID') into NEW.DE_MRNA_ANNOTATION_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_mrna_annotation_id() OWNER TO deapp;

--
-- Name: tf_trg_de_pathway_gene_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_pathway_gene_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.ID is null then
 select nextval('deapp.SEQ_DATA_ID') into NEW.ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_pathway_gene_id() OWNER TO deapp;

--
-- Name: tf_trg_de_pathway_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_pathway_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.ID is null then
 select nextval('deapp.SEQ_DATA_ID') into NEW.ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_pathway_id() OWNER TO deapp;

--
-- Name: tf_trg_de_snp_info_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_snp_info_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.SNP_INFO_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_INFO_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_snp_info_id() OWNER TO deapp;

--
-- Name: tf_trg_de_snp_probe_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_snp_probe_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.SNP_PROBE_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_PROBE_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_snp_probe_id() OWNER TO deapp;

--
-- Name: tf_trg_de_snp_probe_sorted_def_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_snp_probe_sorted_def_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.SNP_PROBE_SORTED_DEF_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_PROBE_SORTED_DEF_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_snp_probe_sorted_def_id() OWNER TO deapp;

--
-- Name: tf_trg_de_subj_rbm_data_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_subj_rbm_data_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.ID is null then
 select nextval('deapp.DE_SUBJECT_RBM_DATA_SEQ') into NEW.ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_subj_rbm_data_id() OWNER TO deapp;

--
-- Name: tf_trg_de_subject_snp_dataset_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_subject_snp_dataset_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.SUBJECT_SNP_DATASET_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.SUBJECT_SNP_DATASET_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_subject_snp_dataset_id() OWNER TO deapp;

--
-- Name: tf_trg_de_subject_sorted_def_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_de_subject_sorted_def_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
if coalesce(NEW.SNP_SUBJECT_SORTED_DEF_ID::text, '') = '' then
select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_SUBJECT_SORTED_DEF_ID ;
end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_de_subject_sorted_def_id() OWNER TO deapp;

--
-- Name: tf_trg_snp_data_by_patient_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_snp_data_by_patient_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.SNP_DATA_BY_PATIENT_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_DATA_BY_PATIENT_ID ;
  end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_snp_data_by_patient_id() OWNER TO deapp;

--
-- Name: tf_trg_snp_data_by_probe_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_snp_data_by_probe_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
      if NEW.SNP_DATA_BY_PROBE_ID is null then
         select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_DATA_BY_PROBE_ID ;
      end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_snp_data_by_probe_id() OWNER TO deapp;

--
-- Name: tf_trg_snp_data_dataset_loc_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_snp_data_dataset_loc_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.SNP_DATA_DATASET_LOC_ID is null then
 select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_DATA_DATASET_LOC_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_snp_data_dataset_loc_id() OWNER TO deapp;

--
-- Name: tf_trg_snp_subject_sorted_def_id(); Type: FUNCTION; Schema: deapp; Owner: deapp
--

CREATE FUNCTION deapp.tf_trg_snp_subject_sorted_def_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
if coalesce(NEW.SNP_SUBJECT_SORTED_DEF_ID::text, '') = '' then
select nextval('deapp.SEQ_DATA_ID') into NEW.SNP_SUBJECT_SORTED_DEF_ID ;
end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION deapp.tf_trg_snp_subject_sorted_def_id() OWNER TO deapp;

--
-- Name: fm_file_uid(character varying); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.fm_file_uid(file_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for fm_file.

  RETURN 'FIL:' || coalesce(FILE_ID, 'ERROR');
END;
$_$;


ALTER FUNCTION fmapp.fm_file_uid(file_id character varying) OWNER TO fmapp;

--
-- Name: fm_folder_uid(numeric); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.fm_folder_uid(folder_id numeric) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
	BEGIN
		RETURN 'FOL:' || FOLDER_ID;
	END;
$$;


ALTER FUNCTION fmapp.fm_folder_uid(folder_id numeric) OWNER TO fmapp;

--
-- Name: fm_get_folder_full_name(bigint); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.fm_get_folder_full_name(p_folder_id bigint) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
  v_parent_id bigint;
  v_folder_full_name character varying(1000);
begin

  select parent_id into v_parent_id
  from fm_folder
  where folder_id = p_folder_id;
  
  v_folder_full_name := fm_folder_uid(p_folder_id) || '\';
  
  while v_parent_id is not null
  loop
    v_folder_full_name := fm_folder_uid(v_parent_id) || '\' || v_folder_full_name;

    select parent_id into v_parent_id
    from fm_folder
    where folder_id = v_parent_id;
  end loop;

  v_folder_full_name := '\' || v_folder_full_name;
  
  return v_folder_full_name;  
end;
$$;


ALTER FUNCTION fmapp.fm_get_folder_full_name(p_folder_id bigint) OWNER TO fmapp;

--
-- Name: fm_update_folder_full_name(); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.fm_update_folder_full_name() RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
  v_folder_full_name character varying(1000);
  folder_ids CURSOR is
    select folder_id
    from fm_folder;
    
begin
  for folder_rec in folder_ids
  loop
    select fm_get_folder_full_name(folder_rec.folder_id) into v_folder_full_name
    from dual;
    
    update fm_folder set folder_full_name = v_folder_full_name
    where folder_id = folder_rec.folder_id;
  end loop;
end;
$$;


ALTER FUNCTION fmapp.fm_update_folder_full_name() OWNER TO fmapp;

--
-- Name: tf_trg_fm_file_id(); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.tf_trg_fm_file_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.FILE_ID is null then
 select nextval('fmapp.SEQ_FM_ID') into NEW.FILE_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION fmapp.tf_trg_fm_file_id() OWNER TO fmapp;

--
-- Name: tf_trg_fm_file_uid(); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.tf_trg_fm_file_uid() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  rec_count bigint;
BEGIN
  SELECT COUNT(*) INTO rec_count 
  FROM fmapp.fm_data_uid 
  WHERE fm_data_id = new.file_id;
  
  if rec_count = 0 then
    insert into fmapp.fm_data_uid (fm_data_id, unique_id, fm_data_type)
    values (NEW.file_id, fm_file_uid(NEW.file_id::text), 'FM_FILE');
  end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION fmapp.tf_trg_fm_file_uid() OWNER TO fmapp;

--
-- Name: tf_trg_fm_folder_id(); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.tf_trg_fm_folder_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	begin
      if coalesce(NEW.FOLDER_ID::text, '') = '' then
        select nextval('FMAPP.SEQ_FM_ID') into NEW.FOLDER_ID ;
      end if;
	  if coalesce(NEW.FOLDER_FULL_NAME::text, '') = '' then
		if coalesce(NEW.PARENT_ID::text, '') = '' then
			select '\' || fm_folder_uid(NEW.folder_id) || '\' into NEW.FOLDER_FULL_NAME ;
		else
			select folder_full_name || fm_folder_uid(NEW.folder_id) || '\' into NEW.FOLDER_FULL_NAME 
      from fmapp.fm_folder
      where folder_id = NEW.parent_id;
		end if;
      end if;
      RETURN NEW;
  end;
$$;


ALTER FUNCTION fmapp.tf_trg_fm_folder_id() OWNER TO fmapp;

--
-- Name: tf_trg_fm_folder_uid(); Type: FUNCTION; Schema: fmapp; Owner: fmapp
--

CREATE FUNCTION fmapp.tf_trg_fm_folder_uid() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  rec_count bigint;
BEGIN
  SELECT COUNT(*) INTO rec_count 
  FROM fmapp.fm_data_uid 
  WHERE fm_data_id = new.folder_id;
  
  if rec_count = 0 then
    insert into fmapp.fm_data_uid (fm_data_id, unique_id, fm_data_type)
    values (NEW.folder_id, fm_folder_uid(NEW.folder_id), 'FM_FOLDER');
  end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION fmapp.tf_trg_fm_folder_uid() OWNER TO fmapp;

--
-- Name: create_temp_concept_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_concept_table(tempconcepttablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$

BEGIN 
execute 'create table ' ||  tempConceptTableName || ' (
        CONCEPT_CD VARCHAR(50) NOT NULL, 
	CONCEPT_PATH VARCHAR(900) NOT NULL , 
	NAME_CHAR VARCHAR(2000), 
	CONCEPT_BLOB TEXT, 
	UPDATE_DATE date, 
	DOWNLOAD_DATE DATE, 
	IMPORT_DATE DATE, 
	SOURCESYSTEM_CD VARCHAR(50)
	 )';

 execute 'CREATE INDEX idx_' || tempConceptTableName || '_pat_id ON ' || tempConceptTableName || '  (CONCEPT_PATH)';
  
   

EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_concept_table(tempconcepttablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_eid_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_eid_table(temppatientmappingtablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$

BEGIN 
execute 'create table ' ||  tempPatientMappingTableName || ' (
	ENCOUNTER_MAP_ID       	VARCHAR(200) NOT NULL,
    ENCOUNTER_MAP_ID_SOURCE	VARCHAR(50) NOT NULL,
    PATIENT_MAP_ID          VARCHAR(200), 
	PATIENT_MAP_ID_SOURCE   VARCHAR(50), 
    ENCOUNTER_ID       	    VARCHAR(200) NOT NULL,
    ENCOUNTER_ID_SOURCE     VARCHAR(50) ,
    ENCOUNTER_NUM           NUMERIC, 
    ENCOUNTER_MAP_ID_STATUS    VARCHAR(50),
    PROCESS_STATUS_FLAG     CHAR(1),
	UPDATE_DATE DATE, 
	DOWNLOAD_DATE DATE, 
	IMPORT_DATE DATE, 
	SOURCESYSTEM_CD VARCHAR(50)
)';

execute 'CREATE INDEX idx_' || tempPatientMappingTableName || '_eid_id ON ' || tempPatientMappingTableName || '  (ENCOUNTER_ID, ENCOUNTER_ID_SOURCE, ENCOUNTER_MAP_ID, ENCOUNTER_MAP_ID_SOURCE, ENCOUNTER_NUM)';

 execute 'CREATE INDEX idx_' || tempPatientMappingTableName || '_stateid_eid_id ON ' || tempPatientMappingTableName || '  (PROCESS_STATUS_FLAG)';  
    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_eid_table(temppatientmappingtablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_modifier_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_modifier_table(tempmodifiertablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN
EXECUTE 'create table ' ||  tempModifierTableName || ' (
        MODIFIER_CD varchar(50) NOT NULL,
	MODIFIER_PATH varchar(900) NOT NULL ,
	NAME_CHAR varchar(2000),
	MODIFIER_BLOB text,
	UPDATE_DATE timestamp,
	DOWNLOAD_DATE timestamp,
	IMPORT_DATE timestamp,
	SOURCESYSTEM_CD varchar(50)
	 )';

 EXECUTE 'CREATE INDEX idx_' || tempModifierTableName || '_pat_id ON ' || tempModifierTableName || '  (MODIFIER_PATH)';

EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '%%%', SQLSTATE,  ' - ' , SQLERRM;
END;
 
$$;


ALTER FUNCTION i2b2demodata.create_temp_modifier_table(tempmodifiertablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_patient_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_patient_table(temppatientdimensiontablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 
	-- Create temp table to store encounter/visit information
	execute 'create table ' ||  tempPatientDimensionTableName || ' (
		PATIENT_ID VARCHAR(200), 
		PATIENT_ID_SOURCE VARCHAR(50),
		PATIENT_NUM NUMERIC(38,0),
	    VITAL_STATUS_CD VARCHAR(50), 
	    BIRTH_DATE DATE, 
	    DEATH_DATE DATE, 
	    SEX_CD CHAR(50), 
	    AGE_IN_YEARS_NUM NUMERIC(5,0), 
	    LANGUAGE_CD VARCHAR(50), 
		RACE_CD VARCHAR(50 ), 
		MARITAL_STATUS_CD VARCHAR(50), 
		RELIGION_CD VARCHAR(50), 
		ZIP_CD VARCHAR(50), 
		STATECITYZIP_PATH VARCHAR(700), 
		PATIENT_BLOB TEXT, 
		UPDATE_DATE DATE, 
		DOWNLOAD_DATE DATE, 
		IMPORT_DATE DATE, 
		SOURCESYSTEM_CD VARCHAR(50)
	)';

execute 'CREATE INDEX idx_' || tempPatientDimensionTableName || '_pat_id ON ' || tempPatientDimensionTableName || '  (PATIENT_ID, PATIENT_ID_SOURCE,PATIENT_NUM)';
  
     
    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_patient_table(temppatientdimensiontablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_pid_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_pid_table(temppatientmappingtablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$

BEGIN 
execute 'create table ' ||  tempPatientMappingTableName || ' (
	   	PATIENT_MAP_ID VARCHAR(200), 
		PATIENT_MAP_ID_SOURCE VARCHAR(50), 
		PATIENT_ID_STATUS VARCHAR(50), 
		PATIENT_ID  VARCHAR(200),
	    PATIENT_ID_SOURCE varchar(50),
		PATIENT_NUM NUMERIC(38,0),
	    PATIENT_MAP_ID_STATUS VARCHAR(50), 
		PROCESS_STATUS_FLAG CHAR(1), 
		UPDATE_DATE DATE, 
		DOWNLOAD_DATE DATE, 
		IMPORT_DATE DATE, 
		SOURCESYSTEM_CD VARCHAR(50)

	 )';

execute 'CREATE INDEX idx_' || tempPatientMappingTableName || '_pid_id ON ' || tempPatientMappingTableName || '  ( PATIENT_ID, PATIENT_ID_SOURCE )';

execute 'CREATE INDEX idx_' || tempPatientMappingTableName || 'map_pid_id ON ' || tempPatientMappingTableName || '  
( PATIENT_ID, PATIENT_ID_SOURCE,PATIENT_MAP_ID, PATIENT_MAP_ID_SOURCE,  PATIENT_NUM )';
 
execute 'CREATE INDEX idx_' || tempPatientMappingTableName || 'stat_pid_id ON ' || tempPatientMappingTableName || '  
(PROCESS_STATUS_FLAG)';


    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_pid_table(temppatientmappingtablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_provider_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_provider_table(tempprovidertablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 

execute 'create table ' ||  tempProviderTableName || ' (
    PROVIDER_ID VARCHAR(50) NOT NULL, 
	PROVIDER_PATH VARCHAR(700) NOT NULL, 
	NAME_CHAR VARCHAR(2000), 
	PROVIDER_BLOB TEXT, 
	UPDATE_DATE DATE, 
	DOWNLOAD_DATE DATE, 
	IMPORT_DATE DATE, 
	SOURCESYSTEM_CD VARCHAR(50), 
	UPLOAD_ID NUMERIC(*,0)
	 )';
 execute 'CREATE INDEX idx_' || tempProviderTableName || '_ppath_id ON ' || tempProviderTableName || '  (PROVIDER_PATH)';

    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_provider_table(tempprovidertablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_table(temptablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 
	execute 'create table ' ||  tempTableName || '  (		
		encounter_id 		varchar(200) not null, 
        encounter_id_source varchar(50) not null,
		encounter_num  		NUMERIC(38,0),
		concept_cd 	 		VARCHAR(50) not null, 
        patient_num 		NUMERIC(38,0), 
		patient_id  		varchar(200) not null,
        patient_id_source  	varchar(50) not null,
		provider_id   		VARCHAR(50),
 		start_date   		DATE, 
		modifier_cd 		VARCHAR(100),
	    instance_num 		NUMERIC(18,0),
 		valtype_cd 			VARCHAR(50),
		tval_char 			varchar(255),
 		nval_num 			NUMERIC(18,5),
		valueflag_cd 		CHAR(50),
 		quantity_num 		NUMERIC(18,5),
		confidence_num 		NUMERIC(18,0),
 		observation_blob 	TEXT,
		units_cd 			VARCHAR(50),
 		end_date    		DATE,
		location_cd 		VARCHAR(50),
 		update_date  		DATE,
		download_date 		DATE,
 		import_date 		DATE,
		sourcesystem_cd 	VARCHAR(50) ,
 		upload_id 			INTEGER
	)';

    
    execute 'CREATE INDEX idx_' || tempTableName || '_pk ON ' || tempTableName || '  ( encounter_num,patient_num,concept_cd,provider_id,start_date,modifier_cd,instance_num)';
    execute 'CREATE INDEX idx_' || tempTableName || '_enc_pat_id ON ' || tempTableName || '  (encounter_id,encounter_id_source, patient_id,patient_id_source )';
    
    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_table(temptablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: create_temp_visit_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.create_temp_visit_table(temptablename character varying, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 
	-- Create temp table to store encounter/visit information
	execute 'create table ' ||  tempTableName || ' (
		encounter_id 			VARCHAR(200) not null,
		encounter_id_source 	VARCHAR(50) not null, 
		patient_id  			VARCHAR(200) not null,
		patient_id_source 		VARCHAR(50) not null,
		encounter_num	 		    NUMERIC(38,0), 
		inout_cd   			VARCHAR(50),
		location_cd 			VARCHAR(50),
		location_path 			VARCHAR(900),
 		start_date   			DATE, 
 		end_date    			DATE,
 		visit_blob 				TEXT,
 		update_date  			DATE,
		download_date 			DATE,
 		import_date 			DATE,
		sourcesystem_cd 		VARCHAR(50)
	)';

    execute 'CREATE INDEX idx_' || tempTableName || '_enc_id ON ' || tempTableName || '  ( encounter_id,encounter_id_source,patient_id,patient_id_source )';
    execute 'CREATE INDEX idx_' || tempTableName || '_patient_id ON ' || tempTableName || '  ( patient_id,patient_id_source )';
    
    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.create_temp_visit_table(temptablename character varying, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_concept_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_concept_fromtemp(tempconcepttablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 
	--Delete duplicate rows with same encounter and patient combination
	-- smuniraju: Rowid not supported in postgres, using combination of ctid (and gp_segment_id for greenplum)
	-- execute 'DELETE FROM ' || tempConceptTableName || ' t1 WHERE rowid > 
	-- 				   (SELECT  min(rowid) FROM ' || tempConceptTableName || ' t2
	-- 				     WHERE t1.concept_cd = t2.concept_cd 
    --                                         AND t1.concept_path = t2.concept_path
    --                                         )';
	execute 'DELETE FROM ' || tempConceptTableName || ' t1 WHERE ( ctid) not in ( 
			   SELECT   max(ctid) FROM ' || tempConceptTableName || ' t2
			   group by concept_path,concept_cd)';
						
    execute ' 	UPDATE concept_dimension cd set 
				name_char= temp.name_char,
				concept_blob= temp.concept_blob,
				update_date= temp.update_date,
				import_date = now(),
				DOWNLOAD_DATE=temp.DOWNLOAD_DATE,
				SOURCESYSTEM_CD=temp.SOURCESYSTEM_CD,
				UPLOAD_ID = '|| upload_id || '
				from ' || tempConceptTableName || ' temp
				where cd.concept_path = temp.concept_path
				and temp.update_date >= cd.update_date';
   
    --Create new patient(patient_mapping) if temp table patient_ide does not exists 
	-- in patient_mapping table.
	-- smuniraju: not exists results in co-related query.
	-- execute 'insert into concept_dimension  --(concept_cd,concept_path,name_char,concept_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
	-- 		    select concept_cd, concept_path, name_char,concept_blob, update_date,download_date,
    --          sysdate,sourcesystem_cd, ' || upload_id || '  
	--			from ' || tempConceptTableName || '  temp
	-- 			where not exists (select concept_cd from concept_dimension cd where cd.concept_path = temp.concept_path)';
	execute 'insert into concept_dimension  (concept_cd,concept_path,name_char,concept_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
			    select  temp.concept_cd, temp.concept_path, temp.name_char,temp.concept_blob, temp.update_date,temp.download_date, current_timestamp,temp.sourcesystem_cd, ' || upload_id || '  
				from ' || tempConceptTableName || '  temp left outer join concept_dimension cd
				on cd.concept_path = temp.concept_path
				where cd.concept_path is null';					
    
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;

$$;


ALTER FUNCTION i2b2demodata.insert_concept_fromtemp(tempconcepttablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_eid_map_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_eid_map_fromtemp(tempeidtablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$

DECLARE
 existingEncounterNum VARCHAR(32);
 maxEncounterNum NUMERIC;
 -- TYPE distinctEIdCurTyp IS CURSOR;
 -- distinctEidCur   distinctEIdCurTyp;
 distinctEidCur CURSOR;
 sql_stmt  VARCHAR(400); 
 disEncounterId VARCHAR(100); 
 disEncounterIdSource VARCHAR(100);

BEGIN
 sql_stmt := ' SELECT distinct encounter_id,encounter_id_source from ' || tempEidTableName ||' ';

-- smuniraju: rowid not supported in postgres/greenplum, instead using ctid (and gp_segment_id in greenplum)
-- execute ' delete  from ' || tempEidTableName ||  ' t1  where 
-- rowid > (select min(rowid) from ' || tempEidTableName || ' t2 
-- where t1.encounter_map_id = t2.encounter_map_id
-- and t1.encounter_map_id_source = t2.encounter_map_id_source
-- and t1.encounter_id = t2.encounter_id
-- and t1.encounter_id_source = t2.encounter_id_source) ';

 execute 'delete  from ' || tempEidTableName ||  ' t1  
		  where ( ctid) NOT IN (select  max(ctid) from ' || tempEidTableName || ' t2 
		  group by  encounter_map_id,encounter_map_id_source,encounter_id,encounter_id_source )';


 LOCK TABLE  encounter_mapping IN EXCLUSIVE MODE NOWAIT;
 select max(encounter_num) into maxEncounterNum from encounter_mapping ; 
 
if maxEncounterNum is null then 
  maxEncounterNum := 0;
end if;

  open distinctEidCur for EXECUTE(sql_stmt) ;
 
   loop
     FETCH distinctEidCur INTO disEncounterId, disEncounterIdSource;
      -- smuniraju: %NOTFOUND not supported in postgres.
	  -- EXIT WHEN distinctEidCur%NOTFOUND;
	 EXIT WHEN NOT FOUND;
      -- dbms_output.put_line(disEncounterId);
        
  if  disEncounterIdSource = 'HIVE'  THEN    
   begin
    --check if hive NUMERIC exist, if so assign that NUMERIC to reset of map_id's within that pid
    select encounter_num into existingEncounterNum from encounter_mapping where encounter_num = disEncounterId and encounter_ide_source = 'HIVE';
    
    EXCEPTION  
       when NO_DATA_FOUND THEN
           existingEncounterNum := null;
    end;
   
   if existingEncounterNum is not null then 
		-- smuniraju: NOT EXISTS clause reults in a co-related queries which are not supported in greenplum
        -- execute ' update ' || tempEidTableName ||' set encounter_num = encounter_id, process_status_flag = ''P''
        -- where encounter_id = ' || disEncounterId || ' and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id
        -- and em.encounter_ide_source = encounter_map_id_source)';		
		execute ' 	update ' || tempEidTableName ||' set 
					encounter_num = encounter_id::numeric, process_status_flag = ''P''
					from encounter_mapping em 
					where em.encounter_ide = encounter_map_id
					and em.encounter_ide_source = encounter_map_id_source
					and encounter_id = ' || disEncounterId || ' 
					and em.encounter_ide is null
					and em.encounter_ide_source is null ';
   else 
        -- generate new patient_num i.e. take max(_num) + 1 
        if maxEncounterNum < disEncounterId then 
            maxEncounterNum := disEncounterId;
        end if ;
		-- smuniraju : NOT EXISTS clause reults in a co-related queries which are not supported in greenplum
        -- execute ' update ' || tempEidTableName ||' set encounter_num = encounter_id, process_status_flag = ''P'' where 
        -- encounter_id = ' || disEncounterId || ' and encounter_id_source = ''HIVE'' and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id
        -- and em.encounter_ide_source = encounter_map_id_source)' ;		
		execute '   update ' || tempEidTableName ||' set 
					encounter_num = encounter_id::numeric, process_status_flag = ''P''
					from encounter_mapping em 
					where em.encounter_ide = encounter_map_id
					and em.encounter_ide_source = encounter_map_id_source
					and encounter_id = ' || disEncounterId || ' 
					and encounter_id_source = ''HIVE'' 
					and em.encounter_ide is null
					and em.encounter_ide_source is null ';      
   end if;    
   
   -- test if record fectched
   -- dbms_output.put_line(' HIVE ');

 else 
    begin
       select encounter_num into existingEncounterNum from encounter_mapping where encounter_ide = disEncounterId and 
        encounter_ide_source = disEncounterIdSource ; 

       -- test if record fetched. 
       EXCEPTION
           WHEN NO_DATA_FOUND THEN
           existingEncounterNum := null;
       end;
       if existingEncounterNum is not  null then 
			-- smuniraju: NOT EXISTS clause reults in a co-related queries which are not supported in greenplum
            -- execute ' update ' || tempEidTableName ||' set encounter_num = ' || existingEncounterNum || ' , process_status_flag = ''P''
            -- where encounter_id = ' || disEncounterId || ' and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id
			-- and em.encounter_ide_source = encounter_map_id_source)' ;
		execute ' update ' || tempEidTableName ||' set 
				  encounter_num = ' || existingEncounterNum || '::numeric , process_status_flag = ''P''
				  from encounter_mapping em 
				  where em.encounter_ide = encounter_map_id and em.encounter_ide_source = encounter_map_id_source
				  and encounter_id = ' || disEncounterId || ' 
				  and em.encounter_ide is null 
				  and em.encounter_ide_source is null' ;
       else 
            maxEncounterNum := maxEncounterNum + 1 ;
			
			--TODO : add update colunn
             execute ' insert into ' || tempEidTableName ||' (encounter_map_id,encounter_map_id_source,encounter_id,encounter_id_source,encounter_num,process_status_flag
             ,encounter_map_id_status,update_date,download_date,import_date,sourcesystem_cd) 
             values(' || maxEncounterNum || ',''HIVE'',' || maxEncounterNum || ',''HIVE'',' || maxEncounterNum ||',''P'',''A'',current_timestamp,current_timestamp,current_timestamp,''edu.harvard.i2b2.crc'')'; 
             
            -- smuniraju: NOT EXISTS clause reults in a co-related queries which are not supported in greenplum
	        -- execute ' update ' || tempEidTableName ||' set encounter_num =  ' || maxEncounterNum || ' , process_status_flag = ''P'' 
            -- where encounter_id = ' || disEncounterId || ' and  not exists (select 1 from 
            -- encounter_mapping em where em.encounter_ide = encounter_map_id
            -- and em.encounter_ide_source = encounter_map_id_source)' ;
			execute ' update ' || tempEidTableName ||' set 
					  encounter_num = ' || maxEncounterNum || ' , process_status_flag = ''P''
					  from encounter_mapping em 
					  where em.encounter_ide = encounter_map_id and em.encounter_ide_source = encounter_map_id_source
					  and encounter_id = ' || disEncounterId || ' 
					  and em.encounter_ide is null 
					  and em.encounter_ide_source is null' ;
            
       end if ;
 end if; 

END LOOP;
close distinctEidCur ;

-- smuniraju Postgres doesn't allow commit within procedures because it is explicity done upon 'END;'
-- commit;

 -- do the mapping update if the update date is old
   execute 'update encounter_mapping em set 
			encounter_num = temp.encounter_id::numeric,
			patient_ide = temp.patient_map_id ,
			patient_ide_source  = temp.patient_map_id_source ,
			encounter_ide_status = temp.encounter_map_id_status  ,
			update_date = temp.update_date,
			download_date = temp.download_date ,
			import_date = now() ,
			sourcesystem_cd  = temp.sourcesystem_cd ,
			upload_id = ' || upload_id || ' 
			from ' || tempEidTableName || ' temp
            where em.encounter_ide = temp.encounter_map_id 
			and em.encounter_ide_source = temp.encounter_map_id_source 
			and temp.encounter_id_source = ''HIVE'' and temp.process_status_flag is null  
			and coalesce(em.update_date,to_date(''01-JAN-1900'',''DD-MON-YYYY''))<= coalesce(temp.update_date,to_date(''01-JAN-1900'',''DD-MON-YYYY'')) ' ;

-- insert new mapping records i.e flagged P

execute ' insert into encounter_mapping (encounter_ide,encounter_ide_source,encounter_ide_status,encounter_num,patient_ide,patient_ide_source,update_date,download_date,import_date,sourcesystem_cd,upload_id) 
			select 	encounter_map_id,encounter_map_id_source,encounter_map_id_status,encounter_num,patient_map_id,patient_map_id_source,update_date,download_date,current_timestamp,sourcesystem_cd,' || upload_id || ' 
			from ' || tempEidTableName || '  
			where process_status_flag = ''P'' ' ; 

-- smuniraju Postgres doesn't allow commit within procedures because it is explicity done upon 'END;'
-- commit;
EXCEPTION
   WHEN OTHERS THEN
	  -- smuniraju
      /*if distinctEidCur%isopen then
          close distinctEidCur;
      end if;*/
	  RAISE EXCEPTION 'An error was encountered - % -ERROR- %', SQLSTATE, SQLERRM;
	  
	  begin
		close distinctEidCur;
		EXCEPTION
			WHEN OTHERS THEN
			RAISE NOTICE 'Error occurred when attempting to close cursor.';
	  end;	
      -- smuniraju Postgres doesn't allow rollback within procedures because it is explicity when a transaction fails.
      -- rollback;
      
end;
$$;


ALTER FUNCTION i2b2demodata.insert_eid_map_fromtemp(tempeidtablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_encountervisit_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_encountervisit_fromtemp(temptablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

DECLARE
maxEncounterNum NUMERIC; 
BEGIN 

    --Delete duplicate rows with same encounter and patient combination
	-- smuniraju
	-- execute  'DELETE FROM ' || tempTableName || ' t1 WHERE rowid > 
	-- 				   (SELECT  min(rowid) FROM ' || tempTableName || ' t2
	-- 				     WHERE t1.encounter_id = t2.encounter_id 
    --                   AND t1.encounter_id_source = t2.encounter_id_source
    --                   AND nvl(t1.patient_id,'''') = nvl(t2.patient_id,'''')
    --                   AND nvl(t1.patient_id_source,'''') = nvl(t2.patient_id_source,''''))';
	execute  'DELETE FROM ' || tempTableName || ' t1 
			  WHERE ( ctid) NOT IN (
					SELECT  max(ctid) FROM ' || tempTableName || ' t2
					GROUP BY  encounter_id,encounter_id_source)';
	
	 LOCK TABLE  encounter_mapping IN EXCLUSIVE MODE NOWAIT;
    -- select max(encounter_num) into maxEncounterNum from encounter_mapping ;

	--Create new patient(patient_mapping) if temp table patient_ide does not exists 
	-- in patient_mapping table.
	-- smuniraju
    --  execute  ' insert into encounter_mapping (encounter_ide,encounter_ide_source,encounter_num,patient_ide,patient_ide_source,encounter_ide_status, upload_id)
    --  	(select distinctTemp.encounter_id, distinctTemp.encounter_id_source, distinctTemp.encounter_id,  distinctTemp.patient_id,distinctTemp.patient_id_source,''A'',  '|| upload_id ||'
	-- 			from 
	-- 				(select distinct encounter_id, encounter_id_source,patient_id,patient_id_source from ' || tempTableName || '  temp
	-- 				where 
	-- 			     not exists (select encounter_ide from encounter_mapping em where em.encounter_ide = temp.encounter_id and em.encounter_ide_source = temp.encounter_id_source)
	-- 				 and encounter_id_source = ''HIVE'' )   distinctTemp) ' ;

	execute  ' insert into encounter_mapping (encounter_ide,encounter_ide_source,encounter_num,patient_ide,patient_ide_source,encounter_ide_status, upload_id) (
					select distinctTemp.encounter_id, distinctTemp.encounter_id_source, distinctTemp.encounter_id::numeric,  distinctTemp.patient_id,distinctTemp.patient_id_source,''A'',  '|| upload_id ||'
					from (
						select distinct encounter_id, encounter_id_source,patient_id,patient_id_source 
						from ' || tempTableName || ' temp left outer join encounter_mapping em
						on em.encounter_ide = temp.encounter_id and em.encounter_ide_source = temp.encounter_id_source
						where em.encounter_ide_source is null
						and  em.encounter_ide is null
						and encounter_id_source = ''HIVE'' )   distinctTemp) ' ;
	
	-- update patient_num for temp table
	-- smuniraju: Greenplum doesn't support sub query for setting a column value.
	-- update patient_num for temp table
	-- execute  ' UPDATE ' ||  tempTableName || ' SET encounter_num = (SELECT em.encounter_num
	-- 	     FROM encounter_mapping em
	-- 	     WHERE em.encounter_ide = '|| tempTableName ||'.encounter_id
    --       and em.encounter_ide_source = '|| tempTableName ||'.encounter_id_source 
	-- 	     and coalesce(em.patient_ide_source,'''') = coalesce('|| tempTableName ||'.patient_id_source,'''')
	-- 	     and coalesce(em.patient_ide,'''')= coalesce('|| tempTableName ||'.patient_id,''''))
	-- 	     WHERE EXISTS (SELECT em.encounter_num
	-- 	     FROM encounter_mapping em
	-- 	     WHERE em.encounter_ide = '|| tempTableName ||'.encounter_id
    --   	 and em.encounter_ide_source = '||tempTableName||'.encounter_id_source
	-- 	     and coalesce(em.patient_ide_source,'''') = coalesce('|| tempTableName ||'.patient_id_source,'''')
	-- 	     and coalesce(em.patient_ide,'''')= coalesce('|| tempTableName ||'.patient_id,''''))';	
		     
	execute  ' UPDATE ' ||  tempTableName || ' temp SET encounter_num = em.encounter_num
		     FROM encounter_mapping em
		     WHERE em.encounter_ide = '|| tempTableName ||'.encounter_id
             and em.encounter_ide_source = '|| tempTableName ||'.encounter_id_source 
		     and coalesce(em.patient_ide_source,'''') = coalesce(temp.patient_id_source,'''')
		     and coalesce(em.patient_ide,'''')= coalesce(temp.patient_id,'''')
		     and EXISTS (
				SELECT em.encounter_num
				FROM encounter_mapping em
				WHERE em.encounter_ide = '|| tempTableName ||'.encounter_id
				and em.encounter_ide_source = '||tempTableName||'.encounter_id_source
				and coalesce(em.patient_ide_source,'''') = coalesce(temp.patient_id_source,'''')
				and coalesce(em.patient_ide,'''')= coalesce(temp.patient_id,''''))';	

	 execute  'UPDATE visit_dimension vd set 
				inout_cd = temp.inout_cd,
				location_cd = temp.location_cd,
				location_path = temp.location_path,
				start_date = temp.start_date,
				end_date = temp.end_date,
				visit_blob = temp.visit_blob,
				update_date = temp.update_date,
				download_date = temp.download_date,
				import_date = now(),
				sourcesystem_cd = temp.sourcesystem_cd
				from ' || tempTableName || ' temp
				where vd.encounter_num = temp.encounter_num
				and temp.update_date >= vd.update_date';

	-- smuniraju
    -- execute  'insert into visit_dimension  (encounter_num,patient_num,START_DATE,END_DATE,INOUT_CD,LOCATION_CD,VISIT_BLOB,UPDATE_DATE,DOWNLOAD_DATE,IMPORT_DATE,SOURCESYSTEM_CD, UPLOAD_ID)
	--  	select temp.encounter_num, pm.patient_num, temp.START_DATE,temp.END_DATE,temp.INOUT_CD,temp.LOCATION_CD,temp.VISIT_BLOB,
	-- 		temp.update_date, temp.download_date, sysdate, -- import date temp.sourcesystem_cd, '|| upload_id ||'
	-- 		from ' || tempTableName || '  temp , patient_mapping pm 
	-- 		where temp.encounter_num is not null and 
	-- 	      	 not exists (select encounter_num from visit_dimension vd where vd.encounter_num = temp.encounter_num) and 
	-- 			 pm.patient_ide = temp.patient_id and pm.patient_ide_source = temp.patient_id_source
	-- ';
	execute  'insert into visit_dimension (encounter_num,patient_num,START_DATE,END_DATE,INOUT_CD,LOCATION_CD,VISIT_BLOB,UPDATE_DATE,DOWNLOAD_DATE,IMPORT_DATE,SOURCESYSTEM_CD, UPLOAD_ID)
				select temp.encounter_num, pm.patient_num, temp.START_DATE,temp.END_DATE,temp.INOUT_CD,temp.LOCATION_CD,temp.VISIT_BLOB,
				temp.update_date, temp.download_date, current_timestamp, -- import date temp.sourcesystem_cd, '|| upload_id ||'
				from ' || tempTableName || '  temp left outer join patient_mapping pm  
				on	pm.patient_ide = temp.patient_id and pm.patient_ide_source = temp.patient_id_source
				left outer join visit_dimension vd 
				on vd.encounter_num = temp.encounter_num
				where temp.encounter_num is not null 				
				and vd.encounter_num  is null'; 
				
-- smuniraju: Postgres doesn't allow commit within procedures because it is explicity done upon 'END;'	 
-- commit;
		        
EXCEPTION
	WHEN OTHERS THEN
		-- smuniraju: Postgres doesn't allow rollback within procedures because it is explicity when a transaction fails.	 
		-- rollback;
		Raise exception 'An error(-20001) was encountered - % -ERROR- %', SQLSTATE, SQLERRM;	
END;
$$;


ALTER FUNCTION i2b2demodata.insert_encountervisit_fromtemp(temptablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_modifier_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_modifier_fromtemp(tempmodifiertablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 
	--Delete duplicate rows 
	-- smuniraju rowid not implemented in postgres
	-- execute  'DELETE FROM ' || tempModifierTableName || ' t1 WHERE rowid > 
	-- 				(SELECT  min(rowid) FROM ' || tempModifierTableName || ' t2
	-- 				 WHERE t1.modifier_cd = t2.modifier_cd 
    --               AND t1.modifier_path = t2.modifier_path)';
	execute  'DELETE FROM ' || tempModifierTableName || ' t1 WHERE ( ctid) NOT IN  
			   (SELECT   max(ctid) FROM ' || tempModifierTableName || ' t2
				GROUP BY  modifier_path,modifier_cd)';
						 
    execute 'UPDATE modifier_dimension cd set 
			name_char= temp.name_char,
			modifier_blob= temp.modifier_blob,
			update_date= temp.update_date,
			import_date = now(),
			DOWNLOAD_DATE=temp.DOWNLOAD_DATE,
			SOURCESYSTEM_CD=temp.SOURCESYSTEM_CD,
			UPLOAD_ID = '|| upload_id || '
			from ' || tempModifierTableName || ' temp
			where cd.modifier_path = temp.modifier_path
			and temp.update_date >= cd.update_date';

    --Create new modifier if temp table modifier_path does not exists 
	-- in modifier dimension table.
	-- smuniraju: not exists => co-related query
	-- execute  'insert into modifier_dimension  (modifier_cd,modifier_path,name_char,modifier_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
	-- 		    select  modifier_cd, modifier_path,
    --                     name_char,modifier_blob,
    --                     update_date,download_date,
    --                     sysdate,sourcesystem_cd,
    --                      ' || upload_id || '  from ' || tempModifierTableName || '  temp
	-- 				where not exists (select modifier_cd from modifier_dimension cd where cd.modifier_path = temp.modifier_path)					 
	-- 	';
	
	execute  'insert into modifier_dimension  (modifier_cd,modifier_path,name_char,modifier_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
			    select  modifier_cd, modifier_path, name_char,modifier_blob, update_date,download_date, current_timestamp, sourcesystem_cd,' || upload_id || '  
					from ' || tempModifierTableName || '  temp left outer join modifier_dimension cd
					on cd.modifier_path = temp.modifier_path 
					where cd.modifier_path is null)';
	 
EXCEPTION
	WHEN OTHERS THEN
		raise exception 'An error(-20001) was encountered - %-ERROR- %', SQLSTATE, SQLERRM;	
END;
$$;


ALTER FUNCTION i2b2demodata.insert_modifier_fromtemp(tempmodifiertablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_patient_map_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_patient_map_fromtemp(temppatienttablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

BEGIN 
	
	-- Create new patient mapping entry for HIVE patient's if they are not already mapped in mapping table
	-- smuniraju: not exists => co-related
	-- execute '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) 
	-- 	)'; 
    
	execute 'insert into patient_mapping (patient_ide, patient_ide_source, patient_ide_status, patient_num, upload_id) (
				select distinct temp.patient_id, temp.patient_id_source,''A'',temp.patient_id::numeric ,' || upload_id || '
				from ' || tempPatientTableName ||'  temp left outer join patient_mapping pm
				on pm.patient_num = temp.patient_id and pm.patient_ide_source = temp.patient_id_source			
				where temp.patient_id_source = ''HIVE'' 
				and pm.patient_num is null 
				and pm.patient_ide_source is null)'; 
		
    --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 'UPDATE patient_dimension pd set 
			 VITAL_STATUS_CD= temp.VITAL_STATUS_CD,
			 BIRTH_DATE= temp.BIRTH_DATE,
			 DEATH_DATE= temp.DEATH_DATE,
			 SEX_CD= temp.SEX_CD,
			 AGE_IN_YEARS_NUM=temp.AGE_IN_YEARS_NUM,
			 LANGUAGE_CD=temp.LANGUAGE_CD,
			 RACE_CD=temp.RACE_CD,
			 MARITAL_STATUS_CD=temp.MARITAL_STATUS_CD,
			 RELIGION_CD=temp.RELIGION_CD,
			 ZIP_CD=temp.ZIP_CD,
			 STATECITYZIP_PATH =temp.STATECITYZIP_PATH,
			 PATIENT_BLOB=temp.PATIENT_BLOB,
			 UPDATE_DATE=temp.UPDATE_DATE,
			 DOWNLOAD_DATE=temp.DOWNLOAD_DATE,
			 SOURCESYSTEM_CD=temp.SOURCESYSTEM_CD,
			 UPLOAD_ID = '|| upload_id || '
			 from ' || tempPatientTableName || ' temp
			 where pd.patient_num = temp.patient_num
			 and temp.update_date > pd.update_date';
EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'An error was encountered - % -ERROR- %', SQLSTATE, SQLERRM;	
END;

$$;


ALTER FUNCTION i2b2demodata.insert_patient_map_fromtemp(temppatienttablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_pid_map_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_pid_map_fromtemp(temppidtablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ 

DECLARE
 existingPatientNum VARCHAR(32);
 maxPatientNum NUMERIC;
 --TYPE distinctPidCurTyp IS CURSOR;
 --distinctPidCurTyp refcursor;
 --distinctPidCur   distinctPidCurTyp;
 distinctPidCur CURSOR;
 sql_stmt  VARCHAR(400);
disPatientId VARCHAR(100); 
disPatientIdSource VARCHAR(100);

BEGIN
 sql_stmt := ' SELECT distinct patient_id,patient_id_source from ' || tempPidTableName ||' ';
 
  --delete the data if they miss 
  -- smuniraju: rowid not implemented in postgres/ greenplum
  -- execute ' delete  from ' || tempPidTableName ||  ' t1  
  --			where rowid > (select min(rowid) from ' || tempPidTableName || ' t2 
  -- 			where t1.patient_map_id = t2.patient_map_id
  -- 			and t1.patient_map_id_source = t2.patient_map_id_source) ';
  execute 'delete  from ' || tempPidTableName ||  ' t1  
		   where ( ctid) 
		    not in (select  max(ctid) from  ' || tempPidTableName ||  ' 
			group  BY patient_map_id,patient_map_id_source,patient_id,patient_id_source)';
  
  LOCK TABLE  patient_mapping IN EXCLUSIVE MODE NOWAIT;
  select max(patient_num) into maxPatientNum from patient_mapping ; 
 
 -- set max patient num to zero of the value is null
  if maxPatientNum is null then 
    maxPatientNum := 0;
  end if;

  open distinctPidCur for execute(sql_stmt);

  loop   
     FETCH distinctPidCur INTO disPatientId, disPatientIdSource;
      -- smuniraju: %NOTFOUND is not supported in POSTGRES
	  -- EXIT WHEN distinctPidCur%NOTFOUND;
	  EXIT WHEN NOT FOUND;    
        
	  if  disPatientIdSource = 'HIVE' THEN 
		begin
		 --check if hive NUMERIC exist, if so assign that NUMERIC to reset of map_id's within that pid
		 select patient_num into existingPatientNum from patient_mapping where patient_num = disPatientId and patient_ide_source = 'HIVE';
		   EXCEPTION  
			 when NO_DATA_FOUND THEN
			   existingPatientNum := null;
		end;
   
		if existingPatientNum is not null then 
			-- smuniraju: not exists results in corelated queries not supported by greenplum
			-- execute ' update ' || tempPidTableName ||' set patient_num = patient_id, process_status_flag = ''P''
			-- where patient_id = ' || disPatientId || ' and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id
			-- and pm.patient_ide_source = patient_map_id_source)';
			execute 'update ' || tempPidTableName ||' temp set patient_num = patient_id::numeric, process_status_flag = ''P''
					 from patient_mapping pm 
					 where pm.patient_ide = temp.patient_map_id
					 and pm.patient_ide_source = temp.patient_map_id_source
					 and pm.patient_ide is null
					 and patient_id = ' || disPatientId || '';
		else 
			-- generate new patient_num i.e. take max(patient_num) + 1 
			if maxPatientNum < disPatientId then 
			   maxPatientNum := disPatientId;
			end if ;
	
		-- smuniraju: not exists results in corelated queries not supported by greenplum
		-- execute ' update ' || tempPidTableName ||' set patient_num = patient_id, process_status_flag = ''P'' where 
		-- patient_id = ' || disPatientId || ' and patient_id_source = ''HIVE'' and not exists (select 1 from patient_mapping pm where pm.patient_ide -- = patient_map_id and pm.patient_ide_source = patient_map_id_source)';
		execute 'update ' || tempPidTableName ||' temp set patient_num = patient_id::numeric, process_status_flag = ''P'' 
				 from patient_mapping pm
				 where pm.patient_ide = temp.patient_map_id
				 and pm.patient_ide_source = temp.patient_map_id_source
				 and pm.patient_ide is null and  pm.patient_ide_source is null
				 and patient_id = ' || disPatientId || ' and patient_id_source = ''HIVE'''; 
	end if;    
    
	  -- test if record fectched
	  -- dbms_output.put_line(' HIVE ');
	else 
		begin
		   select patient_num into existingPatientNum from patient_mapping where patient_ide = disPatientId and 
			patient_ide_source = disPatientIdSource ; 

		   -- test if record fetched. 
		   EXCEPTION
			   WHEN NO_DATA_FOUND THEN
			   existingPatientNum := null;
		   end;
		   if existingPatientNum is not null then 
				-- smuniraju: not exists results in corelated queries not supported by greenplum
				-- execute ' update ' || tempPidTableName ||' set patient_num = ' || existingPatientNum || ', process_status_flag = ''P''
				-- where patient_id = ' || disPatientId || ' and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id
				-- and pm.patient_ide_source = patient_map_id_source)' ;
				execute 'update ' || tempPidTableName ||' temp set patient_num = ' || existingPatientNum || '::numeric, process_status_flag = ''P''
						 from patient_mapping pm 
						 where pm.patient_ide = temp.patient_map_id
						 and pm.patient_ide_source = temp.patient_map_id_source
						 and pm.patient_ide is null and pm.patient_ide_source is null
						 and patient_id = ' || disPatientId || '';
		   else 
				maxPatientNum := maxPatientNum + 1 ; 
				 execute 'insert into ' || tempPidTableName ||' (patient_map_id,patient_map_id_source,patient_id,patient_id_source,patient_num,process_status_flag
				 ,patient_map_id_status,update_date,download_date,import_date,sourcesystem_cd) 
				 values(' || maxPatientNum || ',''HIVE'',' || maxPatientNum || ',''HIVE'',' || maxPatientNum || ',''P'',''A'',current_timestamp,current_timestamp,current_timestamp,''edu.harvard.i2b2.crc'')'; 
			   
			   -- smuniraju: not exists results in corelated queries not supported by greenplum
			   -- execute 'update ' || tempPidTableName ||' set patient_num =  ' || maxPatientNum || ' , process_status_flag = ''P'' 
			   -- where patient_id = ' || disPatientId || ' and  not exists (select 1 from 
			   --  patient_mapping pm where pm.patient_ide = patient_map_id
			   --  and pm.patient_ide_source = patient_map_id_source)' ;
				execute 'update ' || tempPidTableName ||' temp set patient_num =  ' || maxPatientNum || ' , process_status_flag = ''P'' 
						 from patient_mapping pm 
						 where pm.patient_ide = temp.patient_map_id
						 and pm.patient_ide_source = temp.patient_map_id_source
						 and pm.patient_ide is null 
						 and pm.patient_ide_source is null
						 and patient_id = ' || disPatientId || ' ';						
		   end if ;
      -- dbms_output.put_line(' NOT HIVE ');
	end if; 
  END LOOP;
  close distinctPidCur ;
  -- smuniraju: Postgres doesn't allow commit and rollback within procedures because it is explicity done upon 'END;'	 
  -- commit;

  -- do the mapping update if the update date is old
  -- smuniraju: merge resulting in errors.
   /* execute ' merge into patient_mapping
      using ' || tempPidTableName ||' temp
      on (temp.patient_map_id = patient_mapping.patient_IDE 
  		  and temp.patient_map_id_source = patient_mapping.patient_IDE_SOURCE
	   ) when matched then 
  		update set patient_num = temp.patient_id,
    	patient_ide_status	= temp.patient_map_id_status  ,
    	update_date = temp.update_date,
    	download_date  = temp.download_date ,
		import_date = sysdate ,
    	sourcesystem_cd  = temp.sourcesystem_cd ,
		upload_id = ' || upload_id ||'  
    	where  temp.patient_id_source = ''HIVE'' and temp.process_status_flag is null  and
        nvl(patient_mapping.update_date,to_date(''1900-01-01'',''YYYY-MM-DD''))<= nvl(temp.update_date,to_date(''1900-01-01'',''YYYY-MM-DD'')) ' ;
	*/
  execute ' update patient_mapping pm set 
			patient_num = temp.patient_id::numeric,
			patient_ide_status	= temp.patient_map_id_status  ,
			update_date = temp.update_date,
			download_date  = temp.download_date ,
			import_date = now() ,
			sourcesystem_cd  = temp.sourcesystem_cd ,
			upload_id = ' || upload_id ||'  	
			from ' || tempPidTableName ||' temp
			where pm.patient_ide = temp.patient_map_id and pm.patient_ide_source = temp.patient_map_id_source
			and temp.patient_id_source = ''HIVE'' and temp.process_status_flag is null  and 
			coalesce(pm.update_date,to_date(''1900-01-01'',''YYYY-MM-DD''))<= coalesce(temp.update_date,to_date(''1900-01-01'',''YYYY-MM-DD'')) ' ;
	
  -- insert new mapping records i.e flagged P
  execute ' insert into patient_mapping (patient_ide,patient_ide_source,patient_ide_status,patient_num,update_date,download_date,import_date,sourcesystem_cd,upload_id) 
				select patient_map_id,patient_map_id_source,patient_map_id_status,patient_num,update_date,download_date,now(),sourcesystem_cd,' || upload_id ||' from '|| tempPidTableName || ' 
				where process_status_flag = ''P'' ' ; 
  -- smuniraju: Postgres doesn't allow commit and rollback within procedures because it is explicity done upon 'END;'	 
  -- commit;
  EXCEPTION
   WHEN OTHERS THEN
		RAISE EXCEPTION'An error was encountered - % -ERROR- %', SQLSTATE, SQLERRM;
		  -- postgres  doesn't support isOpen
		  -- if distinctPidCur%isopen then
		  --    close distinctPidCur;
		  -- end if;
	  begin
		close distinctPidCur;
		EXCEPTION
			WHEN OTHERS THEN
				RAISE NOTICE 'Error occured closing cursor.';
	  end;
      -- smuniraju: Postgres doesn't allow rollback within procedures because it is explicity when a transaction fails.
	  -- rollback;      
end;

$$;


ALTER FUNCTION i2b2demodata.insert_pid_map_fromtemp(temppidtablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: insert_provider_fromtemp(character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.insert_provider_fromtemp(tempprovidertablename character varying, upload_id numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$

BEGIN 
	--Delete duplicate rows with same encounter and patient combination
	-- smuniraju: rowid not supported
	-- execute 'DELETE FROM ' || tempProviderTableName || ' t1 WHERE rowid > 
	-- 				   (SELECT  min(rowid) FROM ' || tempProviderTableName || ' t2
	-- 				     WHERE t1.provider_id = t2.provider_id 
    --                                         AND t1.provider_path = t2.provider_path
    --                                         )';
	execute 'DELETE FROM ' || tempProviderTableName || ' t1 
			 WHERE ( ctid) NOT IN ( 
				SELECT  max(ctid)
				FROM ' || tempProviderTableName || ' t2
				GROUP BY  provider_path)';
	
 execute 'UPDATE patient_dimension set 
			provider_id = temp.provider_id,
			name_char = temp.name_char,
			provider_blob = provider_blob,
			IMPORT_DATE=now(),
			UPDATE_DATE=temp.UPDATE_DATE,
			DOWNLOAD_DATE=temp.DOWNLOAD_DATE,
			SOURCESYSTEM_CD=temp.SOURCESYSTEM_CD,
			UPLOAD_ID = '||  upload_id || '
			from provider_dimension pd 
			inner join ' || tempProviderTableName || ' temp
			on  pd.provider_path = temp.provider_path
			where temp.update_date >= pd.update_date) ';

   
    --Create new patient(patient_mapping) if temp table patient_ide does not exists 
	-- in patient_mapping table.
	-- smuniraju: not exists => co-related query.
	-- execute 'insert into provider_dimension  (provider_id,provider_path,name_char,provider_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
	-- 		    select  provider_id,provider_path, 
    --                     name_char,provider_blob,
    --                     update_date,download_date,
    --                     now(),sourcesystem_cd, ' || upload_id || '	                    
    --                      from ' || tempProviderTableName || '  temp
	-- 				where not exists (select provider_id from provider_dimension pd where pd.provider_path = temp.provider_path )';
	execute 'insert into provider_dimension  (provider_id,provider_path,name_char,provider_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
			    select  provider_id,provider_path, 
                        name_char,provider_blob,
                        update_date,download_date,
                        now(),sourcesystem_cd, ' || upload_id || '	                    
                        from ' || tempProviderTableName || '  temp left outer join provider_dimension pd 
						on pd.provider_path = temp.provider_path 
						where pd.provider_path is null';   
EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'An error was encountered - % -ERROR- %', SQLSTATE, SQLERRM;	
END;

$$;


ALTER FUNCTION i2b2demodata.insert_provider_fromtemp(tempprovidertablename character varying, upload_id numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: remove_temp_table(character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.remove_temp_table(temptablename character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN 
	execute 'drop table ' || tempTableName || ' cascade';
	
EXCEPTION
	WHEN OTHERS THEN
		RAISE NOTICE '% - %', SQLSTATE, SQLERRM;
END;
$$;


ALTER FUNCTION i2b2demodata.remove_temp_table(temptablename character varying) OWNER TO i2b2demodata;

--
-- Name: sp_xtab(character varying, integer, character varying); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.sp_xtab(v_variable character varying, v_protocol integer, v_subject character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE


/******************************************************************************
   NAME:       sf_xtab
   PURPOSE:    This function serves to flatten the SAS to Oracle Conversion via
               the SLM Process. 

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        5/14/2009   George Kuebrich

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     sf_xtab
      Sysdate:         5/14/2009
      Date and Time:   5/14/2009, 8:33:45 AM, and 5/14/2009 8:33:45 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
sql_stmt varchar(4000);

BEGIN
   --tmpVar := null;
   sql_stmt := 'select distinct value into STRICT  tmpVar
     from sideshow_eav a,
          protocol b,
          variable c
    where a.protocol_id=b.protocol_id
      and a.protocol_id=c.protocol_id
      and a.protocol_id='||v_protocol|| 
    ' and a.variable_id=c.variable_id
      and a.subject_id='||v_subject||
    ' and c.variable_name in ('''||v_variable||''')';
      
   RAISE NOTICE '%', SQL_STMT;
  
   --RETURN tmpVar;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END sp_xtab; 
 
$$;


ALTER FUNCTION i2b2demodata.sp_xtab(v_variable character varying, v_protocol integer, v_subject character varying) OWNER TO i2b2demodata;

--
-- Name: sync_clear_concept_table(character varying, character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.sync_clear_concept_table(tempconcepttablename character varying, backupconcepttablename character varying, uploadid numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ DECLARE

DECLARE
interConceptTableName  varchar(400);

BEGIN 
	interConceptTableName := backupConceptTableName || '_inter';
	
		--Delete duplicate rows with same encounter and patient combination
		-- smuniraju
	-- execute 'DELETE FROM ' || tempConceptTableName || ' t1 WHERE rowid > 
	-- 				   (SELECT  min(rowid) FROM ' || tempConceptTableName || ' t2
	-- 				     WHERE t1.concept_cd = t2.concept_cd 
    --                                         AND t1.concept_path = t2.concept_path
    --                                         )';
	execute 'DELETE FROM ' || tempConceptTableName || ' t1 
	         WHERE ( ctid) NOT IN  
					   (SELECT   max(rowid) FROM ' || tempConceptTableName || ' t2
					     GROUP BY  concept_path,concept_cd)';
						 
    execute 'create table ' ||  interConceptTableName || ' (
    CONCEPT_CD          VARCHAR(50) NOT NULL,
	CONCEPT_PATH    	VARCHAR(700) NOT NULL,
	NAME_CHAR       	VARCHAR(2000) NULL,
	CONCEPT_BLOB        TEXT NULL,
	UPDATE_DATE         DATE NULL,
	DOWNLOAD_DATE       DATE NULL,
	IMPORT_DATE         DATE NULL,
	SOURCESYSTEM_CD     VARCHAR(50) NULL,
	UPLOAD_ID       	NUMERIC(38,0) NULL,
    CONSTRAINT '|| interConceptTableName ||'_pk  PRIMARY KEY(CONCEPT_PATH)
	 )';
    
    --Create new patient(patient_mapping) if temp table patient_ide does not exists 
	-- in patient_mapping table.
	execute 'insert into '|| interConceptTableName ||'  (concept_cd,concept_path,name_char,concept_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
			    select  concept_cd, substr(concept_path,1,700),
                        name_char,concept_blob,
                        update_date,download_date,
                        current_timestamp,sourcesystem_cd,
                         ' || uploadId || '  from ' || tempConceptTableName || '  temp ';
	--backup the concept_dimension table before creating a new one
	execute 'alter table concept_dimension rename to ' || backupConceptTableName  ||'' ;
    
	-- add index on upload_id 
    execute 'CREATE INDEX ' || interConceptTableName || '_uid_idx ON ' || interConceptTableName || '(UPLOAD_ID)';

    -- add index on upload_id 
    execute 'CREATE INDEX ' || interConceptTableName || '_cd_idx ON ' || interConceptTableName || '(concept_cd)';

    
    --backup the concept_dimension table before creating a new one
	execute 'alter table ' || interConceptTableName  || ' rename to concept_dimension' ;
 
EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'An error was encountered - % -ERROR- %', SQLSTATE, SQLERRM;	
END;
$$;


ALTER FUNCTION i2b2demodata.sync_clear_concept_table(tempconcepttablename character varying, backupconcepttablename character varying, uploadid numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: sync_clear_modifier_table(character varying, character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.sync_clear_modifier_table(tempmodifiertablename character varying, backupmodifiertablename character varying, uploadid numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE


interModifierTableName  varchar(400);


BEGIN
	interModifierTableName := backupModifierTableName || '_inter';

	--Delete duplicate rows with same modifier_path and modifier cd
	EXECUTE 'DELETE FROM ' || tempModifierTableName || ' t1 WHERE oid >
					   (SELECT  min(oid) FROM ' || tempModifierTableName || ' t2
					     WHERE t1.modifier_cd = t2.modifier_cd
                                            AND t1.modifier_path = t2.modifier_path
                                            )';

    EXECUTE 'create table ' ||  interModifierTableName || ' (
        MODIFIER_CD          varchar(50) NOT NULL,
	MODIFIER_PATH    	varchar(700) NOT NULL,
	NAME_CHAR       	varchar(2000) NULL,
	MODIFIER_BLOB        text NULL,
	UPDATE_DATE         timestamp NULL,
	DOWNLOAD_DATE       timestamp NULL,
	IMPORT_DATE         timestamp NULL,
	SOURCESYSTEM_CD     varchar(50) NULL,
	UPLOAD_ID       	numeric(38,0) NULL,
    CONSTRAINT '|| interModifierTableName ||'_pk  PRIMARY KEY(MODIFIER_PATH)
	 )';

    --Create new patient(patient_mapping) if temp table patient_ide does not exists
	-- in patient_mapping table.
	EXECUTE 'insert into '|| interModifierTableName ||'  (modifier_cd,modifier_path,name_char,modifier_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
			    PERFORM  modifier_cd, substring(modifier_path from 1 for 700),
                        name_char,modifier_blob,
                        update_date,download_date,
                        LOCALTIMESTAMP,sourcesystem_cd,
                         ' || uploadId || '  from ' || tempModifierTableName || '  temp ';
	--backup the modifier_dimension table before creating a new one
	EXECUTE 'alter table modifier_dimension rename to ' || backupModifierTableName  ||'' ;

	-- add index on upload_id
    EXECUTE 'CREATE INDEX ' || interModifierTableName || '_uid_idx ON ' || interModifierTableName || '(UPLOAD_ID)';

    -- add index on upload_id
    EXECUTE 'CREATE INDEX ' || interModifierTableName || '_cd_idx ON ' || interModifierTableName || '(modifier_cd)';

    --backup the modifier_dimension table before creating a new one
	EXECUTE 'alter table ' || interModifierTableName  || ' rename to modifier_dimension' ;

EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'An error was encountered - % -ERROR- %',SQLSTATE,SQLERRM;
END;
 
$$;


ALTER FUNCTION i2b2demodata.sync_clear_modifier_table(tempmodifiertablename character varying, backupmodifiertablename character varying, uploadid numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: sync_clear_provider_table(character varying, character varying, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.sync_clear_provider_table(tempprovidertablename character varying, backupprovidertablename character varying, uploadid numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$ DECLARE 

DECLARE
interProviderTableName  varchar(400);

BEGIN 
	interProviderTableName := backupProviderTableName || '_inter';
	
	--Delete duplicate rows with same encounter and patient combination
	-- smuniraju: rowid not supported in postgres	
	-- execute 'DELETE FROM ' || tempProviderTableName || ' t1 WHERE rowid > 
	-- 				   (SELECT  min(rowid) FROM ' || tempProviderTableName || ' t2
	-- 				     WHERE t1.provider_id = t2.provider_id 
    --                                        AND t1.provider_path = t2.provider_path
    --                                         )';
	execute 'DELETE FROM ' || tempProviderTableName || ' t1 
			 WHERE ( ctid) NOT IN (
				SELECT  max(ctid) 
				FROM ' || tempProviderTableName || ' t2
				GROUP BY   provider_path,provider_id)';				
											
    execute 'create table ' ||  interProviderTableName || ' (
    PROVIDER_ID         VARCHAR(50) NOT NULL,
	PROVIDER_PATH       VARCHAR(700) NOT NULL,
	NAME_CHAR       	VARCHAR(850) NULL,
	PROVIDER_BLOB       TEXT NULL,
	UPDATE_DATE     	DATE NULL,
	DOWNLOAD_DATE       DATE NULL,
	IMPORT_DATE         DATE NULL,
	SOURCESYSTEM_CD     VARCHAR(50) NULL,
	UPLOAD_ID        	NUMERIC(38,0) NULL ,
    CONSTRAINT  ' || interProviderTableName || '_pk PRIMARY KEY(PROVIDER_PATH,provider_id)
	 )';
    
    --Create new patient(patient_mapping) if temp table patient_ide does not exists 
	-- in patient_mapping table.
	execute 'insert into ' ||  interProviderTableName || ' (provider_id,provider_path,name_char,provider_blob,update_date,download_date,import_date,sourcesystem_cd,upload_id)
			    select  provider_id,provider_path, 
                        name_char,provider_blob,
                        update_date,download_date,
                        now(),sourcesystem_cd, ' || uploadId || '
	                     from ' || tempProviderTableName || '  temp ';
					
	--backup the concept_dimension table before creating a new one
	execute 'alter table provider_dimension rename to ' || backupProviderTableName  ||'' ;
    
	-- add index on provider_id, name_char 
    execute 'CREATE INDEX ' || interProviderTableName || '_id_idx ON ' || interProviderTableName  || '(Provider_Id,name_char)';
    execute 'CREATE INDEX ' || interProviderTableName || '_uid_idx ON ' || interProviderTableName  || '(UPLOAD_ID)';

	--backup the concept_dimension table before creating a new one
	execute 'alter table ' || interProviderTableName  || ' rename to provider_dimension' ;
 
EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'An error was encountered - % -ERROR- %', SQLSTATE, SQLERRM;	
END;

$$;


ALTER FUNCTION i2b2demodata.sync_clear_provider_table(tempprovidertablename character varying, backupprovidertablename character varying, uploadid numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: tf_trg_concept_dimension_cd(); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.tf_trg_concept_dimension_cd() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	 if NEW.CONCEPT_CD is null then
	 select nextval('CONCEPT_ID') into NEW.CONCEPT_CD;
	 end if;
	 RETURN NEW;
	 end;

$$;


ALTER FUNCTION i2b2demodata.tf_trg_concept_dimension_cd() OWNER TO i2b2demodata;

--
-- Name: tf_trg_encounter_num(); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.tf_trg_encounter_num() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.ENCOUNTER_NUM is null then
 select nextval('i2b2demodata.SEQ_ENCOUNTER_NUM') into NEW.ENCOUNTER_NUM ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION i2b2demodata.tf_trg_encounter_num() OWNER TO i2b2demodata;

--
-- Name: tf_trg_patient_dimension(); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.tf_trg_patient_dimension() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.PATIENT_NUM is null then
 select nextval('i2b2demodata.SEQ_PATIENT_NUM') into NEW.PATIENT_NUM ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION i2b2demodata.tf_trg_patient_dimension() OWNER TO i2b2demodata;

--
-- Name: update_observation_fact(character varying, numeric, numeric); Type: FUNCTION; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE FUNCTION i2b2demodata.update_observation_fact(upload_temptable_name character varying, upload_id numeric, appendflag numeric, OUT errormsg character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN



--Delete duplicate records(encounter_ide,patient_ide,concept_cd,start_date,modifier_cd,provider_id)
-- smuniraju: rowid not implemented in postgres
-- execute 'DELETE FROM ' || upload_temptable_name ||'  t1 
--  where rowid > (select min(rowid) from ' || upload_temptable_name ||' t2 
--    where t1.encounter_id = t2.encounter_id  
--          and
--          t1.encounter_id_source = t2.encounter_id_source
--          and
--          t1.patient_id = t2.patient_id 
--          and 
--          t1.patient_id_source = t2.patient_id_source
--          and 
--          t1.concept_cd = t2.concept_cd                
--          and 
--          t1.start_date = t2.start_date
--          and 
--          nvl(t1.modifier_cd,''xyz'') = nvl(t2.modifier_cd,''xyz'')
--		  and 
--		  t1.instance_num = t2.instance_num
--          and 
--          t1.provider_id = t2.provider_id)';
execute 'DELETE FROM ' || upload_temptable_name ||'  
		 WHERE ( ctid) not in (
			SELECT  max(ctid) FROM ' || upload_temptable_name ||' 
			GROUP BY  encounter_id,encounter_id_source,patient_id,patient_id_source, concept_cd,start_date,modifier_cd,provider_id,instance_num ORDER BY encounter_id,encounter_id_source)';
          
--Delete records having null in start_date
execute 'DELETE FROM ' || upload_temptable_name ||'  t1           
 where t1.start_date is null';
           
           
--One time lookup on encounter_ide to get encounter_num 
-- smuniraju: Greenplum doesn't support sub query for assigning values.
-- execute 'UPDATE ' ||  upload_temptable_name
--  || ' SET encounter_num = (SELECT em.encounter_num
-- 		     FROM encounter_mapping em
-- 		     WHERE em.encounter_ide = ' || upload_temptable_name||'.encounter_id
--                   and em.encounter_ide_source = '|| upload_temptable_name||'.encounter_id_source) 
--                   WHERE EXISTS (SELECT em.encounter_num
-- 		     FROM encounter_mapping em
-- 		     WHERE em.encounter_ide = '|| upload_temptable_name||'.encounter_id
--		     and em.encounter_ide_source = '||upload_temptable_name||'.encounter_id_source)';		     

execute 'UPDATE ' ||  upload_temptable_name || ' SET encounter_num = em.encounter_num
	FROM encounter_mapping em
	WHERE em.encounter_ide = ' || upload_temptable_name||'.encounter_id
        and em.encounter_ide_source = '|| upload_temptable_name||'.encounter_id_source) 
        and EXISTS (
		SELECT em.encounter_num
		FROM encounter_mapping em
		WHERE em.encounter_ide = '|| upload_temptable_name||'.encounter_id
                and em.encounter_ide_source = '||upload_temptable_name||'.encounter_id_source)';	     

--One time lookup on patient_ide to get patient_num 
-- smuniraju: Greenplum doesn't support sub query for assigning values.
-- execute 'UPDATE ' ||  upload_temptable_name
--  || ' SET patient_num = (SELECT pm.patient_num
-- 		     FROM patient_mapping pm
-- 		     WHERE pm.patient_ide = '|| upload_temptable_name||'.patient_id
--                      and pm.patient_ide_source = '|| upload_temptable_name||'.patient_id_source
-- 	 	    )WHERE EXISTS (SELECT pm.patient_num 
-- 		     FROM patient_mapping pm
-- 		     WHERE pm.patient_ide = '|| upload_temptable_name||'.patient_id
--                      and pm.patient_ide_source = '||upload_temptable_name||'.patient_id_source)';		     

execute 'UPDATE ' ||  upload_temptable_name || ' SET patient_num = pm.patient_num
	FROM patient_mapping pm
	WHERE pm.patient_ide = '|| upload_temptable_name||'.patient_id
        and pm.patient_ide_source = '|| upload_temptable_name||'.patient_id_source
	and EXISTS (
		SELECT pm.patient_num 
		FROM patient_mapping pm
		WHERE pm.patient_ide = '|| upload_temptable_name||'.patient_id
                and pm.patient_ide_source = '||upload_temptable_name||'.patient_id_source)';		     

IF (appendFlag = 0) THEN
--Archive records which are to be deleted in observation_fact table
execute 'INSERT ALL INTO  archive_observation_fact 
		SELECT obsfact.*, ' || upload_id ||' archive_upload_id 
		FROM observation_fact obsfact
		WHERE obsfact.encounter_num IN 
			(SELECT temp_obsfact.encounter_num
			FROM  ' ||upload_temptable_name ||' temp_obsfact
                        group by temp_obsfact.encounter_num  
            )';


--Delete above archived row from observation_fact
execute 'DELETE  observation_fact 
		 WHERE EXISTS (
				SELECT archive.encounter_num
				FROM archive_observation_fact  archive
				where archive.archive_upload_id = '||upload_id ||'
                AND archive.encounter_num=observation_fact.encounter_num
				AND archive.concept_cd = observation_fact.concept_cd
				AND archive.start_date = observation_fact.start_date
         )';
END IF;

-- if the append is true, then do the update else do insert all
IF (appendFlag = 0) THEN

--Transfer all rows from temp_obsfact to observation_fact
execute 'INSERT ALL INTO observation_fact(encounter_num,concept_cd, patient_num,provider_id, start_date,modifier_cd,instance_num,valtype_cd,tval_char,nval_num,valueflag_cd,
quantity_num,confidence_num,observation_blob,units_cd,end_date,location_cd, update_date,download_date,import_date,sourcesystem_cd,
upload_id) 
SELECT encounter_num,concept_cd, patient_num,provider_id, start_date,modifier_cd,instance_num,valtype_cd,tval_char,nval_num,valueflag_cd,
quantity_num,confidence_num,observation_blob,units_cd,end_date,location_cd, update_date,download_date,current_timestamp import_date,sourcesystem_cd,
temp.upload_id 
FROM ' || upload_temptable_name ||' temp
where temp.patient_num is not null and  temp.encounter_num is not null';
ELSE				
	execute ' UPDATE observation_fact  set 
			valtype_cd = temp.valtype_cd,
            tval_char = temp.tval_char,
			nval_num = temp.nval_num ,
			valueflag_cd = temp.valueflag_cd,
			quantity_num = temp.quantity_num,
			confidence_num = temp.confidence_num ,
			observation_blob = temp.observation_blob,
			units_cd = temp.units_cd,
			end_date = temp.end_date,
			location_cd = temp.location_cd,
			update_date= temp.update_date,
			download_date = temp.download_date,
			import_date = now(),
			sourcesystem_cd = temp.sourcesystem_cd,
			UPLOAD_ID = ' || upload_id || '
			from observation_fact obsfact 
			inner join ' || upload_temptable_name || ' temp
			on  obsfact.encounter_num = temp.encounter_num 
			and obsfact.patient_num = temp.patient_num
			and obsfact.concept_cd = temp.concept_cd
			and obsfact.start_date = temp.start_date
			and obsfact.provider_id = temp.provider_id
			and obsfact.modifier_cd = temp.modifier_cd
			and obsfact.instance_num = temp.instance_num
			where coalesce(observation_fact.update_date,to_date(''1900-01-01'',''YYYY-MM-DD''))<= coalesce(temp.update_date,to_date(''1900-01-01'',''YYYY-MM-DD'')) ';
END IF;

EXCEPTION
	WHEN OTHERS THEN
		RAISE EXCEPTION 'An error was encountered - % -ERROR- % ', SQLSTATE, SQLERRM;	
END;
$$;


ALTER FUNCTION i2b2demodata.update_observation_fact(upload_temptable_name character varying, upload_id numeric, appendflag numeric, OUT errormsg character varying) OWNER TO i2b2demodata;

--
-- Name: add_ontology_node(character varying, character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE FUNCTION i2b2metadata.add_ontology_node(parent_path_src character varying, node_name character varying, is_leaf_src character varying, is_number character varying, prefix character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  declare
    max_basecode numeric;
    parent_path varchar(255);
    is_leaf varchar(30);
    ct numeric;
    hlevel numeric;
    concept_code varchar(30);
    xml varchar(1000);
    data_type varchar(30);
    sourcesyscd varchar(30);
  begin
  
------------------------------------------------------------
-- get parent path
------------------------------------------------------------  
    --parent_path := '&parent_path';
    
      select count(*) into ct
      from i2b2metadata.i2b2
      where c_fullname=parent_path_src;
      
      if (ct=0) then
        RAISE NOTICE 'Supplied path: % does not exist', parent_path_src;
        return;
      end if;
      
    if (substring(parent_path from length(parent_path) for 1)!='\') then
      parent_path := parent_path_src||'\';
    else parent_path := parent_path_src;
    end if;
   
    sourcesyscd := substr(parent_path, 2, instr(parent_path, '\', 2)-2);
    RAISE NOTICE 'parent path: %, source system code: %', parent_path,  sourcesyscd;
------------------------------------------------------------
-- get the name of the node
------------------------------------------------------------
  --node_name := '&node_name';
    RAISE NOTICE 'node name: %', node_name;
------------------------------------------------------------
-- find out whether the node supplied is a leaf or a folder
------------------------------------------------------------
  --is_leaf := '&IsLeaf_Y_N';
  ----------------------------------------------------------
  -- if node is a leaf, set attribute value to LA, set 
  -- concept code and find out what data type the leaf is
  ----------------------------------------------------------
    is_leaf := is_leaf_src;
    
    if (upper(is_leaf) = 'Y') then
      
      is_leaf := 'LA';
      
      PERFORM prefix||nextval('seq_i2b2_data_id')
      into concept_code ;
      
      --is_number :='&is_number';
      if (upper(is_number) = 'Y') then
        xml := '<?xml version="1.0"?><ValueMetadata><Version>3.02</Version><CreationDateTime>08/14/2008 01:22:59</CreationDateTime><TestID></TestID><TestName></TestName><DataType>PosFloat</DataType><CodeType></CodeType><Loinc></Loinc><Flagstouse></Flagstouse><Oktousevalues>Y</Oktousevalues><MaxStringLength></MaxStringLength><LowofLowValue>0</LowofLowValue><HighofLowValue>0</HighofLowValue><LowofHighValue>100</LowofHighValue>100<HighofHighValue>100</HighofHighValue><LowofToxicValue></LowofToxicValue><HighofToxicValue></HighofToxicValue><EnumValues></EnumValues><CommentsDeterminingExclusion><Com></Com></CommentsDeterminingExclusion><UnitValues><NormalUnits>ratio</NormalUnits><EqualUnits></EqualUnits><ExcludingUnits></ExcludingUnits><ConvertingUnits><Units></Units><MultiplyingFactor></MultiplyingFactor></ConvertingUnits></UnitValues><Analysis><Enums /><Counts /><New /></Analysis></ValueMetadata>';
        data_type := 'N';
      elsif (upper(is_number) = 'N') then
        data_type := 'T';
      else 
        RAISE NOTICE 'IS_NUMBER prompt requires ''Y'' or ''N'' input';
        return;
      end if;
    elsif (upper(is_leaf) ='N') then 
      is_leaf := 'FA';
      data_type := 'T';
    else 
      RAISE NOTICE 'IS_LEAF prompt requires ''Y'' or ''N'' input';
      return;
    end if;
    RAISE NOTICE 'node type: %, data type: %, concept code: %', is_leaf, data_type, concept_code;
  
------------------------------------------------------------
-- obtail the proper level for the node by finding the 
-- level of its parent and adding one.
------------------------------------------------------------
    select c_hlevel+1 into hlevel
    from i2b2metadata.i2b2
    where upper(c_fullname)=upper(parent_path);
    RAISE NOTICE 'level: %', hlevel;
------------------------------------------------------------
-- add the new node into the i2b2 table
------------------------------------------------------------
    insert into i2b2demodata.i2b2(
    C_HLEVEL, C_FULLNAME, C_NAME
    , C_SYNONYM_CD, C_VISUALATTRIBUTES, C_TOTALNUM
    , C_BASECODE, C_METADATAXML, C_FACTTABLECOLUMN
    , C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE
    , C_OPERATOR, C_DIMCODE, C_COMMENT
    , C_TOOLTIP, UPDATE_DATE, DOWNLOAD_DATE
    , IMPORT_DATE, SOURCESYSTEM_CD, VALUETYPE_CD 
    )
    values (hlevel, parent_path||node_name||'\', node_name
    , 'N', is_leaf, 0
    , concept_code, xml, 'concept_cd'
    , 'concept_dimension', 'concept_path', data_type
    , 'LIKE', parent_path||node_name||'\', null
    , parent_path||node_name, LOCALTIMESTAMP, LOCALTIMESTAMP
    , LOCALTIMESTAMP, sourcesyscd, null);
    commit;
  end;
end;
 
$$;


ALTER FUNCTION i2b2metadata.add_ontology_node(parent_path_src character varying, node_name character varying, is_leaf_src character varying, is_number character varying, prefix character varying) OWNER TO i2b2metadata;

--
-- Name: tf_trg_i2b2_id(); Type: FUNCTION; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE FUNCTION i2b2metadata.tf_trg_i2b2_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.I2B2_ID is null then
 select nextval('i2b2metadata.I2B2_ID_SEQ') into NEW.I2B2_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION i2b2metadata.tf_trg_i2b2_id() OWNER TO i2b2metadata;

--
-- Name: tf_trg_i2b2_tag_id(); Type: FUNCTION; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE FUNCTION i2b2metadata.tf_trg_i2b2_tag_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.TAG_ID is null then
 select nextval('i2b2metadata.SEQ_I2B2_DATA_ID') into NEW.TAG_ID ;
end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION i2b2metadata.tf_trg_i2b2_tag_id() OWNER TO i2b2metadata;

--
-- Name: assign_permissions_1(text, text, character, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.assign_permissions_1(name text, schema_name text, type character, owner text) RETURNS aclitem
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF schema_name = owner OR type <> 'r' /* table */ THEN
        RETURN NULL;
    END IF;

    RETURN 'user ' || schema_name || '=arwdDxt/' || (SELECT current_user);
END;
$$;


ALTER FUNCTION public.assign_permissions_1(name text, schema_name text, type character, owner text) OWNER TO postgres;

--
-- Name: assign_permissions_2(text, text, character, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.assign_permissions_2(name text, schema_name text, type character, owner text) RETURNS aclitem[]
    LANGUAGE plpgsql
    AS $$
DECLARE
    allPermissions text;
    result         aclitem[];
BEGIN
    allPermissions :=
        CASE type
            WHEN 'r' THEN 'arwdDxt'
            WHEN 'S' THEN 'rwU'
            WHEN 'v' THEN 'arwdDxt'
            WHEN 'f' THEN 'X'
            WHEN 'a' THEN 'X'
            WHEN 's' THEN 'UC'
            WHEN 'T' THEN 'C'
        END ||
        '/' || (SELECT current_user);

    IF owner IS NOT NULL THEN
        result := ARRAY[
            ('user ' || owner || '=' || allPermissions)::aclitem
        ];
    END IF;
    IF owner <> 'tm_cz' OR owner IS NULL THEN
        result := result || ('user tm_cz=' || allPermissions)::aclitem;
    END IF;

    RETURN result;
END;
$$;


ALTER FUNCTION public.assign_permissions_2(name text, schema_name text, type character, owner text) OWNER TO postgres;

--
-- Name: assign_permissions_3(text, text, character, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.assign_permissions_3(name text, schema_name text, type character, owner text) RETURNS aclitem
    LANGUAGE plpgsql
    AS $$
DECLARE
    dummy record;
BEGIN
    IF NOT schema_name = ANY(ARRAY['i2b2demodata', 'i2b2metadata', 'deapp',
            'searchapp', 'galaxy', 'biomart', 'amapp', 'fmapp', 'biomart_user']) THEN
        RETURN NULL;
    END IF;

    IF schema_name = 'searchapp' AND type = 'r' /* table */ THEN
        RETURN 'user biomart_user=arwdDxt/' || (SELECT current_user);
    END IF;

    IF type = 'r' /* table */ THEN
        SELECT *
        INTO dummy
        FROM public.biomart_write_tables B
        WHERE B.nschema = schema_name
            AND B.ntable = name;

        IF FOUND THEN
            -- append (insert), read (select), write (update), delete
            RETURN 'user biomart_user=arwd/' || (SELECT current_user);
        END IF;
    END IF;

    RETURN 'user biomart_user=' ||
            CASE type
                WHEN 'r' THEN 'r'
                WHEN 'S' THEN 'rwU' -- from the docs, w or U would suffice
                WHEN 'v' THEN 'r'
                WHEN 'f' THEN 'X'
                WHEN 'a' THEN 'X'
                WHEN 's' THEN 'U' -- usage only; not create
            END ||
        '/' || (SELECT current_user);
END;
$$;


ALTER FUNCTION public.assign_permissions_3(name text, schema_name text, type character, owner text) OWNER TO postgres;

--
-- Name: assign_permissions_4(text, text, character, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.assign_permissions_4(name text, schema_name text, type character, owner text) RETURNS aclitem[]
    LANGUAGE plpgsql
    AS $$
DECLARE
    res aclitem[];
BEGIN
    SELECT
        array_accum(
                ('user ' || nuser || '=' || nperm || '/'
                        || (SELECT current_user))::aclitem)
    INTO res
    FROM
        public.ts_misc_permissions
    WHERE
        nschema = schema_name
        AND nname = name
        AND ntype = type;

    RETURN res;
END;
$$;


ALTER FUNCTION public.assign_permissions_4(name text, schema_name text, type character, owner text) OWNER TO postgres;

--
-- Name: equal_aclitem_arrays(aclitem[], aclitem[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.equal_aclitem_arrays(aclitems1 aclitem[], aclitems2 aclitem[]) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
    ret boolean;
BEGIN
    -- aclexplode can't handle empty arrays
    IF aclitems1 = '{}'::aclitem[] THEN
        IF aclitems2 = aclitems1 THEN
            RETURN true;
        ELSE
            RETURN false;
        END IF;
    END IF;

    SELECT
        NOT EXISTS (
            SELECT
                A.grantee,
                A.privilege_type,
                COUNT(*)
            FROM
                (
                    SELECT DISTINCT
                        (R.rec).grantee,
                        (R.rec).privilege_type
                    FROM
                        (SELECT ACLEXPLODE(aclitems1)) AS R(rec)

                    UNION ALL

                    SELECT DISTINCT
                        (R.rec).grantee,
                        (R.rec).privilege_type
                    FROM
                        (SELECT ACLEXPLODE(aclitems2)) AS R(rec)
                ) A
            GROUP BY
                A.grantee,
                A.privilege_type
            HAVING
                COUNT(*) <> 2
         )
     INTO ret;

    RETURN ret;
END;
$$;


ALTER FUNCTION public.equal_aclitem_arrays(aclitems1 aclitem[], aclitems2 aclitem[]) OWNER TO postgres;

--
-- Name: grant_aclitems(aclitem[], aclitem[], text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.grant_aclitems(new_aclitems aclitem[], old_aclitems aclitem[], type text, objname text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    grantee text;
    priv    text;
    command text;
    objtype text;
BEGIN
    objtype :=
        CASE type
            WHEN 'r' THEN 'TABLE'
            WHEN 'v' THEN 'TABLE'
            WHEN 'S' THEN 'SEQUENCE'
            WHEN 'f' THEN 'FUNCTION'
            WHEN 'a' THEN 'FUNCTION' -- It's actually AGGREGATE, but we have to reference to it as FUNCTION in DDL
            WHEN 's' THEN 'SCHEMA'
            WHEN 'T' THEN 'TABLESPACE'
            ELSE 'BAD TYPE: ' || type
        END;

    -- first revoke everything
    FOR grantee IN
            SELECT
                DISTINCT R.rolname
            FROM
                (
                    SELECT (R.rec).*
                    FROM
                        (
                            SELECT
                                aclexplode(old_aclitems)
                        ) AS R (rec)
                ) AS B
                INNER JOIN pg_roles R ON (B.grantee = R.oid) LOOP

        command := 'REVOKE ALL PRIVILEGES ON ' || objtype ||
                ' ' || objname || ' FROM ' || grantee;
        EXECUTE(command);
    END LOOP;

    -- then grant the permissions individually
    FOR grantee, priv IN
            SELECT
                R.rolname, B.privilege_type
            FROM
                (
                    SELECT (R.rec).*
                    FROM
                        (
                            SELECT
                                aclexplode(new_aclitems)
                        ) AS R (rec)
                ) AS B
                INNER JOIN pg_roles R ON (B.grantee = R.oid) LOOP

        command := 'GRANT ' || priv || ' ON ' || objtype ||
                ' ' || objname || ' TO ' || grantee;
        EXECUTE(command);
    END LOOP;
END;
$$;


ALTER FUNCTION public.grant_aclitems(new_aclitems aclitem[], old_aclitems aclitem[], type text, objname text) OWNER TO postgres;

--
-- Name: bio_clinical_trial_uid(character varying, character varying, character varying); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.bio_clinical_trial_uid(trial_number character varying, title character varying, condition character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN
  RETURN coalesce(TRIAL_NUMBER || '|', '') || coalesce(TITLE || '|', '') || coalesce(CONDITION, '');
END;
 
 
 
$$;


ALTER FUNCTION searchapp.bio_clinical_trial_uid(trial_number character varying, title character varying, condition character varying) OWNER TO searchapp;

--
-- Name: bio_compound_uid(character varying, character varying, character varying); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.bio_compound_uid(cas_registry character varying, jnj_number character varying, cnto_number character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN
  RETURN coalesce(CAS_REGISTRY || '|', '') || coalesce(JNJ_NUMBER || '|', '') || coalesce(CNTO_NUMBER, '');
END;
 
 
 
 
 
$$;


ALTER FUNCTION searchapp.bio_compound_uid(cas_registry character varying, jnj_number character varying, cnto_number character varying) OWNER TO searchapp;

--
-- Name: refresh_search_bio_mkr_correl_fast_mv(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.refresh_search_bio_mkr_correl_fast_mv() RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN

  -- populate the fake "materialized view" (i.e. actually a table) by deleting everything from it and then re-populating
  -- eventually we need a smarter algorithm for doing this

    delete from searchapp.search_bio_mkr_correl_fast_mv;

    insert into searchapp.search_bio_mkr_correl_fast_mv
    (domain_object_id, asso_bio_marker_id, correl_type, value_metric,  mv_id)    
    select domain_object_id, asso_bio_marker_id, correl_type, value_metric,  mv_id
       from searchapp.search_bio_mkr_correl_fast_view;  

    return true;
END;
 
 
 
$$;


ALTER FUNCTION searchapp.refresh_search_bio_mkr_correl_fast_mv() OWNER TO searchapp;

--
-- Name: tf_trg_saved_faceted_search_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_saved_faceted_search_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF NEW.saved_faceted_search_id IS NULL THEN
		SELECT nextval('SEARCHAPP.SEQ_saved_faceted_search_id') INTO NEW.saved_faceted_search_id;
	END IF;
	IF NEW.create_dt IS NULL THEN
		NEW.create_dt := now();
	END IF;
	RETURN NEW;
END;
$$;


ALTER FUNCTION searchapp.tf_trg_saved_faceted_search_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_a_u_sec_access_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_a_u_sec_access_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.SEARCH_AUTH_USER_SEC_ACCESS_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_AUTH_USER_SEC_ACCESS_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_a_u_sec_access_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_au_obj_access_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_au_obj_access_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.AUTH_SEC_OBJ_ACCESS_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.AUTH_SEC_OBJ_ACCESS_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_au_obj_access_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_au_prcpl_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_au_prcpl_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin     
 if(coalesce(NEW.ID::text, '') = '' or NEW.ID = -2000) then       
 select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.ID ;      
 end if;     RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_au_prcpl_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_cust_fil_item_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_cust_fil_item_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin 
    if NEW.SEARCH_CUSTOM_FILTER_ITEM_ID is null then
        select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_CUSTOM_FILTER_ITEM_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_cust_fil_item_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_custom_filter_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_custom_filter_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin 
    if NEW.SEARCH_CUSTOM_FILTER_ID is null then
        select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_CUSTOM_FILTER_ID ;

    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_custom_filter_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_form_layout_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_form_layout_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.FORM_LAYOUT_ID is null then
 select nextval('searchapp.SEQ_SEARCH_FORM_LAYOUT_ID') into NEW.FORM_LAYOUT_ID ;
 end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_form_layout_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_keyword_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_keyword_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.SEARCH_KEYWORD_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_KEYWORD_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_keyword_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_keyword_term_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_keyword_term_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.SEARCH_KEYWORD_TERM_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_KEYWORD_TERM_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_keyword_term_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_sec_acc_level_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_sec_acc_level_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.SEARCH_SEC_ACCESS_LEVEL_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_SEC_ACCESS_LEVEL_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_sec_acc_level_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_sec_obj_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_sec_obj_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.SEARCH_SECURE_OBJECT_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_SECURE_OBJECT_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_sec_obj_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_sec_obj_path_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_sec_obj_path_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
    if NEW.SEARCH_SECURE_OBJ_PATH_ID is null then
          select nextval('searchapp.SEQ_SEARCH_DATA_ID') into NEW.SEARCH_SECURE_OBJ_PATH_ID ;
    end if;
RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_sec_obj_path_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_taxonomy_rels_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_taxonomy_rels_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF NEW.SEARCH_TAXONOMY_RELS_ID IS NULL THEN
		SELECT nextval('SEARCHAPP.SEQ_SEARCH_DATA_ID') INTO NEW.SEARCH_TAXONOMY_RELS_ID;
	END IF;
	RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_search_taxonomy_rels_id() OWNER TO searchapp;

--
-- Name: tf_trg_search_taxonomy_term_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_search_taxonomy_term_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF NEW.TERM_ID IS NULL THEN
		SELECT nextval('SEARCHAPP.SEQ_SEARCH_DATA_ID') INTO NEW.TERM_ID;
	END IF;
	RETURN NEW;
END;
$$;


ALTER FUNCTION searchapp.tf_trg_search_taxonomy_term_id() OWNER TO searchapp;

--
-- Name: tf_trg_srch_req_up_field_id(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_srch_req_up_field_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
       if NEW.REQUIRED_UPLOAD_FIELD_ID is null then
 select nextval('searchapp.SEQ_REQ_UPLOAD_FIELD_ID') into NEW.REQUIRED_UPLOAD_FIELD_ID ;
 end if;
       RETURN NEW;
end;
$$;


ALTER FUNCTION searchapp.tf_trg_srch_req_up_field_id() OWNER TO searchapp;

--
-- Name: tf_trg_upd_saved_faceted_search(); Type: FUNCTION; Schema: searchapp; Owner: searchapp
--

CREATE FUNCTION searchapp.tf_trg_upd_saved_faceted_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF NEW.modified_dt IS NULL THEN
		NEW.modified_dt := now();
	END IF;
	RETURN NEW;
END;
$$;


ALTER FUNCTION searchapp.tf_trg_upd_saved_faceted_search() OWNER TO searchapp;

--
-- Name: _final_median(double precision[]); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz._final_median(double precision[]) RETURNS double precision
    LANGUAGE sql IMMUTABLE
    AS $_$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$_$;


ALTER FUNCTION tm_cz._final_median(double precision[]) OWNER TO tm_cz;

--
-- Name: _final_median(anyarray); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz._final_median(anyarray) RETURNS numeric
    LANGUAGE sql IMMUTABLE
    AS $_$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$_$;


ALTER FUNCTION tm_cz._final_median(anyarray) OWNER TO tm_cz;

--
-- Name: bio_assay_analysis_uid(bigint); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_assay_analysis_uid(analysis_id bigint) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN

  -- $Id$
  -- Creates uid for bio_assay_analysis.

  RETURN 'BAA:' || coalesce(ANALYSIS_ID, -1);
END;

$_$;


ALTER FUNCTION tm_cz.bio_assay_analysis_uid(analysis_id bigint) OWNER TO tm_cz;

--
-- Name: bio_assay_analysis_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_assay_analysis_uid(analysis_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_experiment.

  RETURN 'BAA:' || coalesce(ANALYSIS_NAME, 'ERROR');
END bio_assay_analysis_uid;

$_$;


ALTER FUNCTION tm_cz.bio_assay_analysis_uid(analysis_name character varying) OWNER TO tm_cz;

--
-- Name: bio_assay_platform_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_assay_platform_uid(platform_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_assay_platform.

  RETURN 'BAP:' || coalesce(PLATFORM_NAME, 'ERROR');
END bio_assay_platform_uid;

$_$;


ALTER FUNCTION tm_cz.bio_assay_platform_uid(platform_name character varying) OWNER TO tm_cz;

--
-- Name: bio_asy_analysis_pltfm_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_asy_analysis_pltfm_uid(platform_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_asy_analysis_pltfm.

  RETURN 'BAAP:' || coalesce(PLATFORM_NAME, 'ERROR');
END bio_asy_analysis_pltfm_uid;
 
$_$;


ALTER FUNCTION tm_cz.bio_asy_analysis_pltfm_uid(platform_name character varying) OWNER TO tm_cz;

--
-- Name: bio_compound_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_compound_uid(jnj_number character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Function to create compound_uid.

  RETURN 'COM:' || JNJ_NUMBER;
END BIO_COMPOUND_UID;
 
$_$;


ALTER FUNCTION tm_cz.bio_compound_uid(jnj_number character varying) OWNER TO tm_cz;

--
-- Name: bio_curation_dataset_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_curation_dataset_uid(bio_curation_type character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_curation_dataset.

  RETURN 'BCD:' || coalesce(BIO_CURATION_TYPE, 'ERROR');
END BIO_CURATION_DATASET_UID;
 
$_$;


ALTER FUNCTION tm_cz.bio_curation_dataset_uid(bio_curation_type character varying) OWNER TO tm_cz;

--
-- Name: bio_disease_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_disease_uid(mesh_code character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates bio_disease_uid.

  RETURN 'DIS:' || coalesce(MESH_CODE, 'ERROR');
END BIO_DISEASE_UID;
 
$_$;


ALTER FUNCTION tm_cz.bio_disease_uid(mesh_code character varying) OWNER TO tm_cz;

--
-- Name: bio_experiment_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_experiment_uid(primary_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_experiment.

  RETURN 'EXP:' || coalesce(PRIMARY_ID, 'ERROR');
END bio_experiment_uid;
 
$_$;


ALTER FUNCTION tm_cz.bio_experiment_uid(primary_id character varying) OWNER TO tm_cz;

--
-- Name: bio_jub_oncol_data_uid(numeric, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_jub_oncol_data_uid(record_id numeric, bio_curation_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_jub_oncol_data.

  RETURN 'BJOD:' || coalesce(TO_CHAR(RECORD_ID), 'ERROR') || ':' || coalesce(BIO_CURATION_NAME, 'ERROR');
END BIO_JUB_ONCOL_DATA_UID;
 
$_$;


ALTER FUNCTION tm_cz.bio_jub_oncol_data_uid(record_id numeric, bio_curation_name character varying) OWNER TO tm_cz;

--
-- Name: bio_jub_oncol_sum_data_uid(numeric, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.bio_jub_oncol_sum_data_uid(record_id numeric, bio_curation_name character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for bio_jub_oncol_sum_data.

  RETURN 'BJOS:' || coalesce(TO_CHAR(RECORD_ID), 'ERROR') || ':' || coalesce(BIO_CURATION_NAME, 'ERROR');
END BIO_JUB_ONCOL_SUM_DATA_UID;
 
$_$;


ALTER FUNCTION tm_cz.bio_jub_oncol_sum_data_uid(record_id numeric, bio_curation_name character varying) OWNER TO tm_cz;

--
-- Name: biomarker_gene_uid(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.biomarker_gene_uid(gene_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for biomarker_gene.

  RETURN 'GENE:' || coalesce(GENE_ID, 'ERROR');
END biomarker_gene_uid;
 
$_$;


ALTER FUNCTION tm_cz.biomarker_gene_uid(gene_id character varying) OWNER TO tm_cz;

--
-- Name: biomarker_pathway_uid(character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.biomarker_pathway_uid(p_source character varying, pathway_id character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
  -- $Id$
  -- Creates uid for biomarker_pathway.

  RETURN 'PATHWAY:'|| P_SOURCE || ':' || coalesce(PATHWAY_ID, 'ERROR');
END biomarker_pathway_uid;
 
$_$;


ALTER FUNCTION tm_cz.biomarker_pathway_uid(p_source character varying, pathway_id character varying) OWNER TO tm_cz;

--
-- Name: create_synonyms(character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.create_synonyms(fromdb character varying, todb character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE

  cTableList CURSOR FOR
    SELECT
      UPPER(owner) AS OWNER,
      UPPER(table_name) AS TABLE_NAME
    from
      all_tables
    order by owner, table_name;

  dbCount bigint;
  sourceDB varchar(200);
  targetDB varchar(200);

  dynamicSQL varchar(2000);


BEGIN
  -------------------------------------------------------------------------------
    --Create or replace Synonyms Point to DB A (TO) From DB B (FROM)
    --Input: From DB, TO DB
    --Output: Nothing
   -- KCR@20090310 - First rev.
   -------------------------------------------------------------------------------

/* CANT READ FROM DBA_TABLESPACES
    --Check that DB's exist
    select count(*) into dbCount from dba_tablespaces where tablespace_name = upper(fromDB);
    if dbCOunt > 1
      then
      dbms_output.put_line('From DB is invalid!: ' || fromDB);
   end if;

    if dbCount > 1
      then
      dbms_output.put_line('TO DB is invalid!: ' || toDB);
   end if;
*/

  sourceDB := UPPER(fromDB);
  targetDB := UPPER(toDB);



    --Loop through full list of results (All table for all schemas)
    for r_cTableList in cTableList
    loop
      --if The current owner(DB) matched the toDB then begin creating Synonyms.
      if r_cTableList.owner = targetDB
        then
        dynamicSQL := 'CREATE or REPLACE  OR REPLACE SYNONYM "' || sourceDB || '"."' || r_cTableList.table_name || '" FOR "' || targetDB || '"."' || r_cTableList.table_name || '"';
        RAISE NOTICE '%', dynamicSQL;
        EXECUTE dynamicSQL;
     end if;
    commit;
    end loop; --Loops through full resultset

END;
 
$$;


ALTER FUNCTION tm_cz.create_synonyms(fromdb character varying, todb character varying) OWNER TO tm_cz;

--
-- Name: cum_normal_dist(double precision, double precision, double precision); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cum_normal_dist(foldchg double precision, mu double precision, sigma double precision) RETURNS double precision
    LANGUAGE plpgsql IMMUTABLE
    AS $$
DECLARE
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/

 -------------------------------------------------------------------------------
  -- param foldChg: fold change ratio from from analysis_data table
  -- param mu: mean of all analsyis_data records for a given analysis
  -- param sigma: std dev of all analsyis_data records for a given analysis
  -------------------------------------------------------------------------------

  -- temporary vars
  t1 DOUBLE PRECISION;

  -- fractional error dist input
  fract_error_input DOUBLE PRECISION;

  -- return result (i.e. Prob [X<=x])
  ans DOUBLE PRECISION;

BEGIN
  SELECT (foldChg-mu)/sigma INTO t1;
  SELECT t1/SQRT(2) INTO fract_error_input;
  SELECT 0.5 * (1.0 + tm_cz.fract_error_dist(fract_error_input)) INTO ans;
  return ans;
END
$$;


ALTER FUNCTION tm_cz.cum_normal_dist(foldchg double precision, mu double precision, sigma double precision) OWNER TO tm_cz;

--
-- Name: cz_audit_example(bigint); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_audit_example(currentjobid bigint) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE

  --Audit variables
  newJobFlag integer(1);
  databaseName varchar(100);
  procedureName varchar(100);
  jobID bigint;

BEGIN
  --Set Audit Parameters
  newJobFlag := 0; -- False (Default)
  jobID := currentJobID;

  PERFORM sys_context('USERENV', 'CURRENT_SCHEMA') INTO databaseName ;
  procedureName := $$PLSQL_UNIT;

  --Audit JOB Initialization
  --If Job ID does not exist, then this is a single procedure run and we need to create it
  IF(coalesce(jobID::text, '') = '' or jobID < 1)
  THEN
    newJobFlag := 1; -- True
    RAISE NOTICE '%%', 'Here' ,  to_char(jobID);
    cz_start_audit (procedureName, databaseName, jobID);
    RAISE NOTICE '%%', 'Here2' ,  to_char(jobID);
  END IF;

  --Step Audit
  cz_write_audit (jobID, databaseName, procedureName, 'Start loading some data', SQL%ROWCOUNT, 1, 'PASS');

  update cz_job_master set job_name = job_name;

  --Step Audit
  cz_write_audit (jobID, databaseName, procedureName, '# of rows on the cz_job_master table', SQL%ROWCOUNT, 2, 'PASS');


  cz_write_info (jobID, 1, 39, procedureName, 'Writing a message');



  --invalid statement
  insert into az_test_run(dw_version_id)
    values('a');


  --Step Audit
  cz_write_audit (jobID, databaseName, procedureName, 'Should have caused an error!', SQL%ROWCOUNT, 3, 'PASS');


  ---Cleanup OVERALL JOB if this proc is being run standalone
  IF newJobFlag = 1
  THEN
    cz_end_audit (jobID, 'SUCCESS');
  END IF;

  EXCEPTION
  WHEN OTHERS THEN
    --Handle errors.
    cz_error_handler (jobID, procedureName);
    --End Proc
    cz_end_audit (jobID, 'FAIL');

END;
 
$_$;


ALTER FUNCTION tm_cz.cz_audit_example(currentjobid bigint) OWNER TO tm_cz;

--
-- Name: cz_end_audit(numeric, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_end_audit(jobid numeric, jobstatus character varying) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
declare
	endDate timestamp;
	rtnCd	numeric;

BEGIN
  
	select current_timestamp into endDate;
  
	begin
	update tm_cz.cz_job_master
		set 
			active='N',
			end_date = endDate,
			time_elapsed_secs = coalesce(((DATE_PART('day', endDate - START_DATE) * 24 + 
				   DATE_PART('hour', endDate - START_DATE)) * 60 +
				   DATE_PART('minute', endDate - START_DATE)) * 60 +
				   DATE_PART('second', endDate - START_DATE),0),
			job_status = jobStatus		
		where active='Y' 
		and job_id=jobID;
	end;
	
	return 1;
	
	exception 
	when OTHERS then
		--raise notice 'proc failed state=%  errm=%', SQLSTATE, SQLERRM;
		select tm_cz.cz_write_error(jobId,SQLSTATE,SQLERRM,null,null) into rtnCd;
		return -16;
END;
$$;


ALTER FUNCTION tm_cz.cz_end_audit(jobid numeric, jobstatus character varying) OWNER TO tm_cz;

--
-- Name: cz_error_handler(numeric, character varying, character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_error_handler(jobid numeric, procedurename character varying, errornumber character varying, errormessage character varying) RETURNS integer
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
Declare
	databaseName VARCHAR(100);
	--errorNumber		character varying;	--	PostgreSQL SQLSTATE is alphanumeric
	--errorNumber NUMBER(18,0);
	--errorMessage VARCHAR(1000);
	errorStack VARCHAR(4000);
	errorBackTrace VARCHAR(4000);
	stepNo numeric(18,0);
	
	rtnCd	integer;

BEGIN
	--Get DB Name
	select database_name INTO databaseName
	from tm_cz.cz_job_master 
	where job_id=jobID;

	--Get Latest Step
	select max(step_number) into stepNo from tm_cz.cz_job_audit where job_id = jobID;
  
	--Get all error info, passed in as parameters, only available from EXCEPTION block
	--errorNumber := SQLSTATE;
	--errorMessage := SQLERRM;
	
	--	No corresponding functionality in PostgreSQL
	--errorStack := dbms_utility.format_error_stack;
	--errorBackTrace := dbms_utility.format_error_backtrace;

	--Update the audit step for the error
	select tm_cz.cz_write_audit(jobID, databaseName,procedureName, 'Job Failed: See error log for details',1, stepNo, 'FAIL') into rtnCd;
  
	--write out the error info
	select tm_cz.cz_write_error(jobID, errorNumber, errorMessage, errorStack, errorBackTrace) into rtnCd;
	
	return 1;

END;

$$;


ALTER FUNCTION tm_cz.cz_error_handler(jobid numeric, procedurename character varying, errornumber character varying, errormessage character varying) OWNER TO tm_cz;

--
-- Name: cz_info_handler(numeric, numeric, numeric, character varying, character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_info_handler(jobid numeric, messageid numeric, messageline numeric, messageprocedure character varying, infomessage character varying, stepnumber character varying) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE 
	databaseName VARCHAR(100);
	rtnCd	numeric;
BEGIN

	select database_name INTO databaseName
  	from tm_cz.cz_job_master 
	where job_id=jobID;
    
  select tm_cz.cz_write_audit( jobID, databaseName, messageProcedure, 'Step contains more details', 0, stepNumber, 'Information' ) into rtnCd;
  select tm_cz.cz_write_info(jobID, messageID, messageLine, messageProcedure, infoMessage ) into rtnCd;

return rtnCd;
  
END;
$$;


ALTER FUNCTION tm_cz.cz_info_handler(jobid numeric, messageid numeric, messageline numeric, messageprocedure character varying, infomessage character varying, stepnumber character varying) OWNER TO tm_cz;

--
-- Name: cz_start_audit(character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_start_audit(jobname character varying, databasename character varying) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
declare
	rtnCd	integer;
	jobId	numeric;
BEGIN
	begin
		insert into tm_cz.cz_job_master
			(start_date, 
			active, 
			database_name,
			job_name,
			job_status) 
		VALUES(
			CURRENT_TIMESTAMP,
			'Y', 
			databaseName,
			jobName,
			'Running')
	  RETURNING job_id INTO jobID;
	end;
  
  return jobID;
  
  exception 
	when OTHERS then
		select tm_cz.cz_write_error(jobId,SQLERRML,SQLSTATE,SQLERRM,null,null) into rtnCd;
		return -16;

END;

$$;


ALTER FUNCTION tm_cz.cz_start_audit(jobname character varying, databasename character varying) OWNER TO tm_cz;

--
-- Name: cz_start_audit(character varying, character varying, bigint); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_start_audit(jobname character varying, databasename character varying, jobid bigint) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
declare
	rtnCd	integer;
BEGIN
	begin
		insert into tm_cz.cz_job_master
			(start_date, 
			active, 
			database_name,
			job_name,
			job_status) 
		VALUES(
			CURRENT_TIMESTAMP,
			'Y', 
			databaseName,
			jobName,
			'Running')
	  RETURNING job_id INTO jobID;
	end;
  
  return jobID;
  
  exception 
	when OTHERS then
		select tm_cz.cz_write_error(jobId,SQLERRML,SQLSTATE,SQLERRM,null,null) into rtnCd;
		return -16;

END;

$$;


ALTER FUNCTION tm_cz.cz_start_audit(jobname character varying, databasename character varying, jobid bigint) OWNER TO tm_cz;

--
-- Name: cz_write_audit(numeric, character varying, character varying, character varying, numeric, numeric, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_write_audit(jobid numeric, databasename character varying, procedurename character varying, stepdesc character varying, recordsmanipulated numeric, stepnumber numeric, stepstatus character varying) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
DECLARE
        lastTime timestamp;
        currTime timestamp;
        elapsedSecs        numeric;
        rtnCd                numeric;
        
BEGIN

        select max(job_date)
    into lastTime
    from tm_cz.cz_job_audit
    where job_id = jobID;
        
        --        clock_timestamp() is the current system time
        
        select clock_timestamp() into currTime;

        elapsedSecs :=        coalesce(((DATE_PART('day', currTime - lastTime) * 24 + 
                                   DATE_PART('hour', currTime - lastTime)) * 60 +
                                   DATE_PART('minute', currTime - lastTime)) * 60 +
                                   DATE_PART('second', currTime - lastTime),0);

        begin
        insert         into tm_cz.cz_job_audit
        (job_id
        ,database_name
         ,procedure_name
         ,step_desc
        ,records_manipulated
        ,step_number
        ,step_status
    ,job_date
    ,time_elapsed_secs
        )
        values(
                 jobId,
                substring(databaseName from 1 for 50),
                procedureName,
                stepDesc,
                recordsManipulated,
                stepNumber,
                stepStatus,
                currTime,
                elapsedSecs);
        exception 
        when OTHERS then
                --raise notice 'proc failed state=%  errm=%', SQLSTATE, SQLERRM;
                select tm_cz.cz_write_error(jobId,SQLSTATE,SQLERRM,null,null) into rtnCd;
                return -16;
        end;
        
        return 1;
END;
$$;


ALTER FUNCTION tm_cz.cz_write_audit(jobid numeric, databasename character varying, procedurename character varying, stepdesc character varying, recordsmanipulated numeric, stepnumber numeric, stepstatus character varying) OWNER TO tm_cz;

--
-- Name: cz_write_error(numeric, character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_write_error(jobid numeric, errornumber character varying, errormessage character varying, errorstack character varying, errorbacktrace character varying) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/

BEGIN

	begin
	insert into tm_cz.cz_job_error(
		job_id,
		error_number,
		error_message,
		error_stack,
		error_backtrace,
		seq_id)
	select
		jobID,
		errorNumber,
		errorMessage,
		errorStack,
		errorBackTrace,
		max(seq_id) 
  from tm_cz.cz_job_audit 
  where job_id=jobID;
  
  end;
  
  return 1;
 
  exception 
	when OTHERS then
		raise notice 'proc failed state=%  errm=%', SQLSTATE, SQLERRM;
		return -16; 

END;
$$;


ALTER FUNCTION tm_cz.cz_write_error(jobid numeric, errornumber character varying, errormessage character varying, errorstack character varying, errorbacktrace character varying) OWNER TO tm_cz;

--
-- Name: cz_write_info(numeric, numeric, numeric, character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.cz_write_info(jobid numeric, messageid numeric, messageline numeric, messageprocedure character varying, infomessage character varying) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$

BEGIN
    begin
	insert into tm_cz.cz_job_message
    (
      job_id,
      message_id,
      message_line,
      message_procedure,
      info_message,
      seq_id
    )
	select
      jobID,
      messageID,
      messageLine,
      messageProcedure,
      infoMessage,
      max(seq_id)
  from
    tm_cz.cz_job_audit
  where
    job_id = jobID;
    end;
  
  COMMIT;
  return 1;

END;

$$;


ALTER FUNCTION tm_cz.cz_write_info(jobid numeric, messageid numeric, messageline numeric, messageprocedure character varying, infomessage character varying) OWNER TO tm_cz;

--
-- Name: czf_init_cap(character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.czf_init_cap(text_to_parse character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE

   start_pos 		integer;
   end_pos   		integer;
   string_length 	integer;
   string_tokens 	varchar(32676);
   counter 			integer;
   token_value 		varchar(1000);
   text_delimiter 	char(1);
   noInitCap 		boolean;

   --	create array to hold strings that will not be initcapped

	type excluded_aat is table of category_path_excluded_words%ROWTYPE index by integer;
	excludedText excluded_aat;
	exclCt integer;

   --	text to return
   initcap_text varchar(1000);


BEGIN
  -------------------------------------------------------------------------------
   -- Performs custom initcap for category paths where specific text strings are
   -- excluded from the process.  Strings are delimited by a space.  The \ in
   -- the category path are converted to ' \ ' before parsing.

   -- JEA@20091001 - First rev.
   -- Copyright ? 2009 Recombinant Data Corp.
   -------------------------------------------------------------------------------

	--	Load exclusion text

	select excluded_text
		bulk collect into excludedText
		from category_path_excluded_words;

	--	Add a delimiter to the end of the string so we dont lose last value and
	--	surround \ with spaces

	text_delimiter := ' ';
	string_tokens := replace(text_to_parse,'\',' \ ') || text_delimiter;

	--get length of string
	string_length := length(string_tokens);

	--set start and end for first token
	start_pos := 1;
	end_pos   := instr(string_tokens,text_delimiter,1,1);
	counter := 1;

	LOOP
		--	Get substring
		token_value := substr(string_tokens, start_pos, end_pos - start_pos);

		--	check if token_value is in excludedText, if yes, set indicator

		noInitCap := false;
		exclCt := excludedText.FIRST;

		while ((exclCt IS NOT NULL AND exclCt::text <> '') and not noInitCap)
		loop
			if token_value = excludedText(exclCt).excluded_text then
				noInitCap := true;
			end if;
			exclCt := excludedText.NEXT (exclCt);
		end loop;

		if noInitCap then
			initcap_text := initcap_text || token_value || ' ';
		else
			initcap_text := initcap_text || initcap(token_value) || ' ';
		end if;

		--Check to see if we are done
		IF end_pos = string_length
		THEN
			initcap_text := replace(rtrim(initcap_text,' '),' \ ','\');
			EXIT;
		ELSE
			-- Increment Start Pos and End Pos
			start_pos := end_pos + 1;
			--	increment counter
			counter := counter + 1;
			end_pos := instr(string_tokens, text_delimiter,1, counter);

		END IF;
  END LOOP;

  return initcap_text;

END CZF_INIT_CAP;
 
$$;


ALTER FUNCTION tm_cz.czf_init_cap(text_to_parse character varying) OWNER TO tm_cz;

--
-- Name: czf_num_occurances(character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.czf_num_occurances(input_str character varying, search_str character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE

  num integer;

BEGIN
  num := 0;
  while instr(input_str, search_str, 1, num + 1) > 0 loop
    num := num + 1;
  end loop;
  return num;
end;
 
$$;


ALTER FUNCTION tm_cz.czf_num_occurances(input_str character varying, search_str character varying) OWNER TO tm_cz;

--
-- Name: czf_parse_nth_value(character varying, numeric, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.czf_parse_nth_value(pvalue character varying, location numeric, delimiter character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE

   v_posA integer;
   v_posB integer;


BEGIN

   if location = 1 then
      v_posA := 1; -- Start at the beginning
   else
      v_posA := instr (pValue, delimiter, 1, location - 1);
      if v_posA = 0 then
         return null; --No values left.
      end if;
      v_posA := v_posA + length(delimiter);
   end if;

   v_posB := instr (pValue, delimiter, 1, location);
   if v_posB = 0 then -- Use the end of the file
      return substring(pValue from v_posA);
   end if;

   return substr (pValue, v_posA, v_posB - v_posA);

end;
 
$$;


ALTER FUNCTION tm_cz.czf_parse_nth_value(pvalue character varying, location numeric, delimiter character varying) OWNER TO tm_cz;

--
-- Name: czx_array_sort(anyarray); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.czx_array_sort(anyarray) RETURNS anyarray
    LANGUAGE sql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'tm_lz', 'tm_wz', 'i2b2demodata', 'i2b2metadata', 'deapp', 'pg_temp'
    AS $_$   
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
SELECT ARRAY(   
    SELECT $1[s.i] AS "foo"   
    FROM   
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)   
    ORDER BY foo   
);   
$_$;


ALTER FUNCTION tm_cz.czx_array_sort(anyarray) OWNER TO tm_cz;

--
-- Name: czx_end_audit(numeric, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.czx_end_audit(jobid numeric, jobstatus character varying) RETURNS numeric
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/

declare
	endDate timestamp;
	rtnCd	numeric;

BEGIN
  
	select clock_timestamp() into endDate;
  
	begin
	update tm_cz.cz_job_master
		set 
			active='N',
			end_date = endDate,
			time_elapsed_secs = coalesce(((DATE_PART('day', endDate - START_DATE) * 24 + 
				   DATE_PART('hour', endDate - START_DATE)) * 60 +
				   DATE_PART('minute', endDate - START_DATE)) * 60 +
				   DATE_PART('second', endDate - START_DATE),0),
			job_status = jobStatus		
		where active='Y' 
		and job_id=jobID;
	end;
	
	return 1;
	
	exception 
	when OTHERS then
		--raise notice 'proc failed state=%  errm=%', SQLSTATE, SQLERRM;
		select tm_cz.cz_write_error(jobId,SQLSTATE,SQLERRM,null,null) into rtnCd;
		return -16;
END;
$$;


ALTER FUNCTION tm_cz.czx_end_audit(jobid numeric, jobstatus character varying) OWNER TO tm_cz;

--
-- Name: czx_error_handler(numeric, character varying, character varying, character varying); Type: FUNCTION; Schema: tm_cz; Owner: tm_cz
--

CREATE FUNCTION tm_cz.czx_error_handler(jobid numeric, procedurename character varying, errornumber character varying, errormessage character varying) RETURNS integer
    LANGUAGE plpgsql SECURITY DEFINER
    SET search_path TO 'tm_cz', 'pg_temp'
    AS $$
/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
Declare
	databaseName VARCHAR(100);
	--errorNumber		character varying;	--	PostgreSQL SQLSTATE is alphanumeric
	--errorNumber NUMBER(18,0);
	--errorMessage VARCHAR(1000);
	errorStack VARCHAR(4000);
	errorBackTrace VARCHAR(4000);
	stepNo numeric(18,0);
	
	rtnCd	integer;

BEGIN
	--Get DB Name
	select database_name INTO databaseName
	from tm_cz.cz_job_master 
	where job_id=jobID;

	--Get Latest Step
	select max(step_number) into stepNo from tm_cz.cz_job_audit where job_id = jobID;
  
	--Get all error info, passed in as parameters, only available from EXCEPTION block
	--errorNumber := SQLSTATE;
	--errorMessage := SQLERRM;
	
	--	No corresponding functionality in PostgreSQL
	--errorStack := dbms_utility.format_error_stack;
	--errorBackTrace := dbms_utility.format_error_backtrace;

	--Update the audit step for the error
	select tm_cz.czx_write_audit(jobID, databaseName,procedureName, 'Job Failed: See error log for details',1, stepNo, 'FAIL') into rtnCd;
  
	--write out the error info
	select tm_cz.czx_write_error(jobID, errorNumber, errorMessage, errorStack, errorBackTrace) into rtnCd;
	
	return 1;

END;

$$;


ALTER FUNCTION tm_cz.czx_error_handler(jobid numeric, procedurename character varying, errornumber character varying, errormessage character varying) OWNER TO tm_cz;

--
