<?php
	$RETURN_METHOD = 'OUTVAR'; /* RETURN or OUTVAR */
	require __DIR__ . '/../_scripts/macros.php';
?>
-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4
-- Copyright 2000-2013 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=mydb.mydom.fr;sid=SIDNAME


CREATE OR REPLACE FUNCTION tm_cz.rwg_load_analysis_metadata (
	trialID                  text,
	i_study_data_category    text   DEFAULT 'Study',
	i_study_category_display text   DEFAULT null,
	currentJobID             bigint DEFAULT null,
	rtn_code                 OUT    bigint
)
RETURNS bigint AS $body$
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.
******************************************************************/
  --Audit variables
	<?php standard_vars() ?>

	Dcount        integer;
	lcount        integer;
	analysisCount integer;
	resultCount   integer;
	innerRet      bigint;

BEGIN
	<?php func_start('RWG_LOAD_ANALYSIS_METADATA') ?>

	/*
	 * Before starting, ensure that the incoming analysis IDs match to the
	 * biomart.bio_assay_analysis name.
	 * If not, try to match using the short_desc. Update the analysis_name if
	 * this work; otherwise, quit
	 */
    /*
	 * NOTE: Due to a change in the curation/etl procedures, this step should no
	 * longer be needed.  The bio_assay_analysis_id is updated in
	 * TM_LZ.Rwg_Analysis at time of creation. A check is done, and if the IDs
	 * match, then this step is bypassed
	 */
  
  
	-- get the count of the incoming analysis data
	<?php step_begin() ?>
	SELECT count(*)
	INTO analysisCount 
	FROM TM_LZ.Rwg_Analysis
	WHERE study_id =  Upper(trialID);

	<?php step_end('Analysis count from TM_LZ.Rwg_Analysis =(see count)', 'analysisCount') ?>

	--see how many of the analysees match by using the cohort analysis name
	<?php step_begin() ?>
	SELECT
		COUNT ( * )
		INTO resultCount
	FROM
		TM_LZ.Rwg_Analysis analysis,
		Biomart.Bio_Assay_Analysis Baa
	WHERE
		analysis.bio_assay_analysis_id = baa.bio_assay_analysis_id --bio_assay_analysis_id in 'TM_LZ.Rwg_Analysis analysis' should already exist
		AND UPPER ( analysis.study_id ) = UPPER ( trialID );

	<?php step_end('Join analysis.Cohorts to Baa.Analysis_Name, Analysis count =(see count)', 'resultCount') ?>

	IF analysisCount != resultCount THEN  
	  RAISE 'Analysis count mismatch' USING ERRCODE = 'AA001';
	END IF;


	<?php step_begin() ?>
	DELETE FROM Biomart.Bio_Analysis_Cohort_Xref WHERE upper(study_id) = upper(trialID);

	<?php step_end('Delete existing records from Biomart.Bio_Analysis_Cohort_Xref') ?>

	<?php step_begin() ?>
	DELETE FROM Biomart.Bio_Analysis_Attribute_Lineage Baal
	WHERE Baal.Bio_Analysis_Attribute_Id IN
	(SELECT DISTINCT Baa.Bio_Analysis_Attribute_Id
	FROM Biomart.Bio_Analysis_Attribute baa WHERE upper(study_id) = upper(trialID));

	<?php step_end('Delete existing records from Biomart.Bio_Analysis_Attribute_Lineage') ?>

	<?php step_begin() ?>
	DELETE FROM Biomart.Bio_Analysis_Attribute  WHERE upper(study_id) = upper(trialID);

	<?php step_end('Delete existing records from Biomart.Bio_Analysis_Attribute') ?>

	/** Delete study from biomart.bio_assay_cohort table **/
	<?php step_begin() ?>
	DELETE FROM Biomart.Bio_Assay_Cohort WHERE upper(study_id) = upper(trialID);

	<?php step_end('Delete existing records from Biomart.Bio_Assay_Cohort') ?>

	/** Populate biomart.bio_assay_cohort table **/
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Assay_Cohort (
		Study_Id,
		Cohort_Id,
		Disease,
		Sample_Type,
		Treatment,
		Organism,
		Pathology,
		Cohort_Title,
		Short_Desc,
		Long_Desc,
		Import_Date )
	SELECT
		Study_Id,
		Cohort_Id,
		Disease,
		Sample_Type,
		Treatment,
		Organism,
		Pathology,
		Cohort_Title,
		Short_Desc,
		Long_Desc,
		now()
	FROM
		TM_LZ.Rwg_Cohorts
	WHERE
		UPPER ( Study_Id ) = UPPER ( trialID );
	
	<?php step_end('Insert into Biomart.Bio_Assay_Cohort') ?>

	SELECT coalesce(Max(Length(Regexp_Replace(analysis.Cohorts,'[^;]','g'))),0)+1
	INTO dcount
	FROM TM_LZ.Rwg_Analysis analysis;

	FOR lcount IN 1 .. dcount
		LOOP

		<?php step_begin() ?>
		PERFORM cz_write_audit(jobId,databaseName,procedureName,'Starting Bio_Analysis_Cohort_Xref LOOP, pass: ',lcount,stepCt,'Done');
		INSERT INTO Biomart.Bio_Analysis_Cohort_Xref(
			Study_Id,
			Analysis_Cd,
			Cohort_Id,
			Bio_Assay_Analysis_Id
		)
		SELECT
			upper(analysis.Study_Id),
			analysis.Cohorts,
			trim(Parse_Nth_Value(analysis.Cohorts,lcount,';')) AS cohort,
			baa.bio_assay_analysis_id
		FROM TM_LZ.Rwg_Analysis analysis, Biomart.Bio_Assay_Analysis Baa
		WHERE analysis.bio_assay_analysis_id= Baa.bio_assay_analysis_id
			AND Upper(Baa.Etl_Id) LIKE '%' || Upper(Trialid) || '%'
			AND Upper(analysis.Study_Id) LIKE '%' || Upper(Trialid) || '%'
			AND Trim(Parse_Nth_Value(analysis.Cohorts,lcount,';')) IS NOT NULL;

		<?php step_end('Insert COHORTS into  into Biomart.Bio_Analysis_Cohort_Xref') ?>
	END LOOP;


	/*************************************/
	/** POPULATE Bio_Analysis_Attribute **/
	/*************************************/
	--	delete study from cz_rwg_invalid_terms 20121220 JEA
	<?php step_begin() ?>
	DELETE FROM cz_rwg_invalid_terms
	WHERE upper(study_id) = upper(trialID);

	<?php step_end('Delete existing data from cz_rwg_invalid_terms') ?>

	--	insert study as search_term term, sp will check if already exists
	SELECT rwg_add_search_term(upper(trialID),i_study_data_category,i_study_category_display,jobId) INTO innerRet;
	IF innerRet <> 0
		THEN
		errorNumber := '000000';
		errorMessage := 'RWG_ADD_SEARCH_TERM() failed. Arguments: ' ||
				upper(trialID) || ', ' || i_study_data_category ||
				', ' || i_study_category_display  || ', ' || jobId;
		PERFORM cz_error_handler(jobID, procedureName, errorNumber, errorMessage);
		PERFORM cz_end_audit (jobID, 'FAIL');
		rtn_code := -16;
		RETURN;
	END IF;

	PERFORM cz_write_audit(jobId, databaseName, procedureName,
		'Insert study as search_term', rowCt, stepCt, 'Done');
	stepCt := stepCt + 1;

	<?php step_begin() ?>
	-- sample_type: check for any records that do not have a match in the taxonomy
	INSERT INTO cz_rwg_Invalid_Terms(
		Study_Id,
		Category_Name,
		Term_Name)
	SELECT DISTINCT
		upper(Cohort.Study_Id),
		upper('sample_type'),
		cohort.sample_type
	FROM TM_LZ.Rwg_Cohorts Cohort
	WHERE upper(Cohort.Study_Id)=upper(trialID)
		AND NOT EXISTS 
			(SELECT Upper(Tax.Term_Name) FROM Searchapp.Search_Taxonomy Tax
			 WHERE Upper(Cohort.sample_type) = Upper(Tax.Term_Name));
	
	<?php step_end('sample_type: register invalid terms (with no match in the taxonomy)') ?>

	-- sample_type: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute(
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd)
	SELECT DISTINCT
		upper(Cohort.Study_Id),
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id, Upper('sample_type:' || Cohort.sample_type) 
	FROM
		TM_LZ.Rwg_Cohorts Cohort,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE upper(Cohort.Cohort_Id) = upper(Xref.Cohort_Id)
		AND upper(Xref.Study_Id) = upper(Cohort.Study_Id)
		AND Upper(Cohort.Sample_Type) = Upper(Tax.Term_Name)
		AND Cohort.Study_Id =upper(trialID); 

	<?php step_end('Insert SAMPLE_TYPE into Bio_Analysis_Attribute') ?>

	-- disease: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( Cohort.Study_Id ),
		'disease',
		cohort.disease
	FROM
		TM_LZ.Rwg_Cohorts Cohort
	WHERE
		UPPER ( Cohort.Study_Id )
		= UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( Cohort.disease ) = UPPER ( Tax.Term_Name ) );

	<?php step_end('disease: register invalid terms (with no match in the taxonomy)') ?>

	-- disease: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT DISTINCT
		UPPER ( Cohort.Study_Id ) ,
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'disease:' || Cohort.disease )
	FROM
		TM_LZ.Rwg_Cohorts Cohort,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE
		UPPER ( Cohort.Cohort_Id ) = UPPER ( Xref.Cohort_Id )
		AND UPPER ( Xref.Study_Id ) = UPPER ( Cohort.Study_Id )
		AND UPPER ( Cohort.Disease ) = UPPER ( Tax.Term_Name )
		AND UPPER ( cohort.study_id ) = UPPER ( trialID ); 

	<?php step_end('Insert disease into Bio_Analysis_Attribute') ?>

	-- pathology: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( Cohort.Study_Id ),
		'pathology',
		cohort.pathology
	FROM
		TM_LZ.Rwg_Cohorts Cohort
	WHERE
		UPPER ( Cohort.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( Cohort.pathology ) = UPPER ( Tax.Term_Name ) );

	<?php step_end('pathology: register invalid terms (with no match in the taxonomy)') ?>

	-- pathology: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT DISTINCT
		UPPER ( Cohort.Study_Id ),
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'pathology:' || Cohort.pathology )
	FROM
		TM_LZ.Rwg_Cohorts Cohort,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE
		UPPER ( Cohort.Cohort_Id ) = UPPER ( Xref.Cohort_Id )
		AND UPPER ( Xref.Study_Id ) = UPPER ( Cohort.Study_Id )
		AND UPPER ( Cohort.Pathology ) = UPPER ( Tax.Term_Name )
		AND UPPER ( cohort.study_id ) = UPPER ( trialID );

	<?php step_end('Insert pathology into Bio_Analysis_Attribute') ?>

	-- LOOP FOR TREATMENT
	SELECT coalesce(Max(Length(Regexp_Replace(Cohort.Treatment,'[^;]', 'g'))),0)+1
	INTO Dcount
	FROM TM_LZ.Rwg_Cohorts Cohort
	WHERE upper(Cohort.Study_Id)=upper(trialID);
	FOR lcount IN 1 .. dcount
		LOOP	
		stepCt := stepCt + 1;
		PERFORM cz_write_audit(jobId,databaseName,procedureName,'Starting COHORT TREATMENT LOOP, pass: ',lcount,stepCt,'Done');

		-- treatment: check for any records that do not have a match in the taxonomy
		<?php step_begin() ?>
		INSERT INTO Cz_Rwg_Invalid_Terms (
			Study_Id,
			Category_Name,
			Term_Name )
		SELECT
			DISTINCT UPPER ( Cohort.Study_Id ) ,
			'treatment',
			TRIM ( Parse_Nth_Value ( cohort.treatment, lcount, ';' ) )
		FROM
			TM_LZ.Rwg_Cohorts Cohort
		WHERE
			UPPER ( Cohort.Study_Id ) = UPPER ( trialID )
			AND NOT EXISTS (
				SELECT
					UPPER ( Tax.Term_Name )
				FROM
					Searchapp.Search_Taxonomy Tax
				WHERE
					UPPER ( TRIM ( Parse_Nth_Value ( cohort.treatment, lcount, ';' ) ) )
					= UPPER ( Tax.Term_Name ) )
			AND TRIM ( Parse_Nth_Value ( cohort.treatment, lcount, ';' ) ) IS NOT NULL;

		-- treatment: insert terms into attribute table
		INSERT INTO biomart.bio_analysis_attribute (
			study_id,
			bio_assay_analysis_id,
			term_id,
			source_cd )
		SELECT DISTINCT
			UPPER ( cohort.study_id ) ,
			xref.bio_assay_analysis_id,
			tax.term_id,
			UPPER ( 'treatment:' || TRIM ( parse_nth_value ( cohort.treatment,
						lcount,
						';' ) ) )
		FROM
			tm_lz.rwg_cohorts cohort,
			biomart.bio_analysis_cohort_xref xref,
			searchapp.search_taxonomy tax
		WHERE
			UPPER ( cohort.cohort_id ) = UPPER ( xref.cohort_id )
			AND UPPER ( xref.study_id ) = UPPER ( cohort.study_id )
			AND UPPER ( TRIM ( parse_nth_value ( cohort.treatment, lcount, ';' ) ) )
				= UPPER ( tax.term_name )
			AND UPPER ( cohort.study_id ) = UPPER ( trialid );

		<?php step_end('Insert treatment into Bio_Analysis_Attribute (LOOP)') ?>

	END LOOP;

	stepCt := stepCt + 1;
	GET DIAGNOSTICS rowCt := ROW_COUNT;
	PERFORM cz_write_audit(Jobid,Databasename,Procedurename,'END TREATMENT LOOP',rowCt,stepCt,'Done');
	-- END TREATMENT LOOP

	-- organism: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( Cohort.Study_Id ) ,
		'organism',
		Cohort.organism
	FROM
		TM_LZ.Rwg_Cohorts Cohort
	WHERE
		UPPER ( Cohort.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( Cohort.organism ) = UPPER ( Tax.Term_Name ) );

	<?php step_end('organism: register invalid terms (with no match in the taxonomy)') ?>

	-- organism: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT DISTINCT
		UPPER ( Cohort.Study_Id ),
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'organism:' || Cohort.Organism )
	FROM
		TM_LZ.Rwg_Cohorts Cohort,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE
		UPPER ( Cohort.Cohort_Id ) = UPPER ( Xref.Cohort_Id )
		AND UPPER ( Xref.Study_Id ) = UPPER ( Cohort.Study_Id )
		AND UPPER ( Cohort.Organism ) = UPPER ( Tax.Term_Name )
		AND UPPER ( cohort.study_id ) = UPPER ( trialID );

	<?php step_end('Insert organism into Bio_Analysis_Attribute') ?>

	-- data_type: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( analysis.Study_Id ),
		'data_type',
		analysis.data_type
	FROM
		TM_LZ.Rwg_Analysis analysis
	WHERE
		UPPER ( analysis.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( analysis.data_type ) = UPPER ( Tax.Term_Name ) );

	<?php step_end('data_type: register invalid terms (with no match in the taxonomy)') ?>

	-- data_type: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT
		UPPER ( analysis.Study_Id ),
		Baa.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'data_type:' || analysis.Data_Type )
	FROM
		TM_LZ.Rwg_Analysis analysis,
		Searchapp.Search_Taxonomy Tax,
		Biomart.Bio_Assay_Analysis Baa
	WHERE
		UPPER ( analysis.Data_Type ) = UPPER ( Tax.Term_Name )
		AND analysis.bio_assay_analysis_id = Baa.bio_assay_analysis_id
		AND UPPER ( Baa.Etl_Id )
			LIKE '%' || UPPER ( analysis.Study_Id ) || '%'
		AND UPPER ( analysis.Study_Id ) = UPPER ( trialID );

	<?php step_end('Insert data_type into Bio_Analysis_Attribute') ?>

	-- platform: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		study_id,
		category_name,
		term_name )
	SELECT DISTINCT
		analysis.Study_Id,
		'platform',
		analysis.platform
	FROM
		TM_LZ.Rwg_Analysis analysis
	WHERE
		UPPER ( analysis.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( analysis.platform ) = UPPER ( Tax.Term_Name ) );

	<?php step_end('platform: register invalid terms (with no match in the taxonomy)') ?>

	-- platform: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT
		UPPER ( analysis.Study_Id ),
		Baa.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'platform:' || analysis.Platform )
	FROM
		TM_LZ.Rwg_Analysis analysis,
		Searchapp.Search_Taxonomy Tax,
		Biomart.Bio_Assay_Analysis Baa
	WHERE
		UPPER ( analysis.Platform ) = UPPER ( Tax.Term_Name )
		AND analysis.bio_assay_analysis_id = Baa.bio_assay_analysis_id
		AND UPPER ( Baa.Etl_Id )
			LIKE '%' || UPPER ( analysis.Study_Id ) || '%'
		AND UPPER ( analysis.Study_Id ) = UPPER ( trialID );

	<?php step_end('Insert platform into Bio_Analysis_Attribute') ?>

	-- LOOP FOR ANALYSIS TYPE
	SELECT coalesce(Max(Length(Regexp_Replace(analysis.Analysis_Type,'[^;]', 'g'))),0)+1
	INTO Dcount
	FROM TM_LZ.Rwg_Analysis analysis
	WHERE upper(analysis.Study_Id)=upper(trialID);

	FOR Lcount IN 1 .. Dcount
		LOOP	 
		stepCt := stepCt + 1;
		PERFORM cz_write_audit(jobId,databaseName,procedureName,'Starting ANALYSIS_TYPE LOOP, pass: ',lcount,stepCt,'Done');

		-- Analysis_Type: check for any records that do not have a match in the taxonomy
		<?php step_begin() ?>
		INSERT INTO Cz_Rwg_Invalid_Terms (
			Study_Id,
			Category_Name,
			Term_Name )
		SELECT DISTINCT
			UPPER ( analysis.Study_Id ),
			'Analysis_Type',
			TRIM ( Parse_Nth_Value ( analysis.Analysis_Type, lcount, ';' ) )
		FROM
			TM_LZ.Rwg_Analysis analysis
		WHERE
			UPPER ( analysis.Study_Id ) = UPPER ( trialID )
			AND NOT EXISTS (
				SELECT
					UPPER ( Tax.Term_Name )
				FROM
					Searchapp.Search_Taxonomy Tax
				WHERE
					UPPER ( TRIM ( Parse_Nth_Value ( analysis.Analysis_Type, Lcount, ';' ) ) )
						= UPPER ( Tax.Term_Name ) )
			AND TRIM ( Parse_Nth_Value ( analysis.Analysis_Type, Lcount, ';' ) ) IS NOT NULL;

		-- Analysis_Type: insert terms into attribute table
		INSERT INTO Biomart.Bio_Analysis_Attribute (
			Study_Id,
			Bio_Assay_Analysis_Id,
			Term_Id,
			Source_Cd )
		SELECT
			UPPER ( analysis.study_id ),
			baa.bio_assay_analysis_id,
			tax.term_id,
			UPPER ( 'ANALYSIS_TYPE:' || TRIM ( Parse_Nth_Value ( analysis.Analysis_Type,
						lcount,
						';' ) ) )
		FROM
			TM_LZ.Rwg_Analysis analysis,
			Searchapp.Search_Taxonomy Tax,
			Biomart.Bio_Assay_Analysis Baa
		WHERE
			UPPER ( TRIM ( Parse_Nth_Value ( analysis.Analysis_Type,
						Lcount,
						';' ) ) )
				= UPPER ( Tax.Term_Name )
			AND analysis.bio_assay_analysis_id = Baa.bio_assay_analysis_id
			AND UPPER ( Baa.Etl_Id )
				LIKE '%' || UPPER ( analysis.Study_Id ) || '%'
			AND UPPER ( analysis.Study_Id ) = UPPER ( Trialid )
			AND TRIM ( Parse_Nth_Value ( analysis.Analysis_Type, Lcount, ';' ) ) IS NOT NULL;

		<?php step_end('LOOP: Insert ANALYSIS_TYPE into Bio_Analysis_Attribute') ?>
	END LOOP;

	stepCt := stepCt + 1;
	GET DIAGNOSTICS rowCt := ROW_COUNT;
	PERFORM cz_write_audit(Jobid,Databasename,Procedurename,'END ANALYSIS_TYPE LOOP',rowCt,stepCt,'Done');
	-- END ANALYSIS TYPE

	-- search_area: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( ext.Study_Id ),
		'search_area',
		ext.search_area
	FROM
		TM_LZ.clinical_trial_metadata_ext Ext
	WHERE
		UPPER ( ext.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( ext.search_area ) = UPPER ( Tax.Term_Name ) );

	<?php step_end('search_area: register invalid terms (with no match in the taxonomy)') ?>

	-- search_area: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT DISTINCT
		UPPER ( Ext.Study_Id ),
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'search_area:' || Ext.search_area )
	FROM
		TM_LZ.Clinical_Trial_Metadata_Ext Ext,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE
		UPPER ( Xref.Study_Id ) = UPPER ( ext.Study_Id )
		AND UPPER ( Ext.Search_Area ) = UPPER ( Tax.Term_Name )
		AND UPPER ( ext.study_id ) = UPPER ( trialID );

	<?php step_end('Insert search_area into Bio_Analysis_Attribute') ?>

	-- data source: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( ext.Study_Id ),
		'DATA_SOURCE',
		ext.data_source
	FROM
		TM_LZ.Clinical_Trial_Metadata_Ext Ext
	WHERE
		UPPER ( ext.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( ext.data_source ) = UPPER ( Tax.Term_Name ) );
	
	<?php step_end('data source: register invalid terms (with no match in the taxonomy)') ?>
	
	-- data source: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT DISTINCT
		UPPER ( Ext.Study_Id ),
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'DATA_SOURCE:' || Ext.data_source )
	FROM
		TM_LZ.Clinical_Trial_Metadata_Ext Ext,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE
		UPPER ( Xref.Study_Id ) = UPPER ( ext.Study_Id )
		AND UPPER ( Ext.data_source ) = UPPER ( Tax.Term_Name )
		AND UPPER ( ext.study_id ) = UPPER ( trialID );

	<?php step_end('Insert DATA_SOURCE into Bio_Analysis_Attribute') ?>

	-- study_design: check for any records that do not have a match in the taxonomy
	<?php step_begin() ?>
	INSERT INTO Cz_Rwg_Invalid_Terms (
		Study_Id,
		Category_Name,
		Term_Name )
	SELECT DISTINCT
		UPPER ( ext.Study_Id ),
		'study_design',
		ext.study_design
	FROM
		TM_LZ.Clinical_Trial_Metadata_Ext Ext
	WHERE
		UPPER ( ext.Study_Id ) = UPPER ( trialID )
		AND NOT EXISTS (
			SELECT
				UPPER ( Tax.Term_Name )
			FROM
				Searchapp.Search_Taxonomy Tax
			WHERE
				UPPER ( ext.experimental_design ) = UPPER ( Tax.Term_Name ) );
	
	<?php step_end('study design: register invalid terms (with no match in the taxonomy)') ?>

	-- study_design: insert terms into attribute table
	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Analysis_Attribute (
		Study_Id,
		Bio_Assay_Analysis_Id,
		Term_Id,
		Source_Cd )
	SELECT DISTINCT
		UPPER ( Ext.Study_Id ),
		Xref.Bio_Assay_Analysis_Id,
		Tax.Term_Id,
		UPPER ( 'study_design:' || Ext.experimental_design )
	FROM
		TM_LZ.Clinical_Trial_Metadata_Ext Ext,
		Biomart.Bio_Analysis_Cohort_Xref Xref,
		Searchapp.Search_Taxonomy Tax
	WHERE
		UPPER ( Xref.Study_Id ) = UPPER ( ext.Study_Id )
		AND UPPER ( ( CASE
					WHEN Ext.experimental_design = 'Clinical' THEN 'Clinical Trial'
					ELSE ext.experimental_design
				END ) )
			= UPPER ( Tax.Term_Name )
		AND UPPER ( ext.study_id ) = UPPER ( trialID );

	<?php step_end('Insert study_design into Bio_Analysis_Attribute') ?>

	--	populate biomart.bio_analysis_attribute_lineage in one shot
	<?php step_begin() ?>
	INSERT INTO biomart.bio_analysis_attribute_lineage (
		bio_analysis_attribute_id,
		ancestor_term_id,
		ancestor_search_keyword_id )
	SELECT
		baa.bio_analysis_attribute_id,
		baa.term_id,
		st.search_keyword_id
	FROM
		biomart.bio_analysis_attribute baa,
		searchapp.search_taxonomy st
	WHERE
		UPPER ( baa.study_id ) = UPPER ( trialID )
		AND baa.term_id = st.term_id;

	<?php step_end('Insert attribute links into bio_analysis_attribute_lineage') ?>
	/* END populate */

	/*Update the 'analysis_update_date' in bio_assay_analysis (this date is used by solr for incremental updates*/
	<?php step_begin() ?>
	UPDATE BIOMART.bio_assay_analysis baa
	SET
		ANALYSIS_UPDATE_DATE = now()
	WHERE
		UPPER ( baa.etl_id ) LIKE UPPER ( trialID || '%' );

	<?php step_end('Update ANALYSIS_UPDATE_DATE with LOCALTIMESTAMP') ?>
	PERFORM cz_write_audit(jobId,databaseName,procedureName,'End FUNCTION',0,stepCt,'Done');

	---Cleanup OVERALL JOB if this proc is being run standalone    
	IF newJobFlag = 1
		THEN
		PERFORM cz_end_audit (jobID, 'SUCCESS');
	END IF;
EXCEPTION
	WHEN SQLSTATE 'AA001' then
		stepCt := stepCt + 1;
		GET DIAGNOSTICS rowCt := ROW_COUNT;
		PERFORM cz_write_audit(Jobid,Databasename,Procedurename,'ERR: Check for analysis in rwg_analysis not in biomart.bio_assay_analysis',rowCt,stepCt,'Done');
		--Handle errors.
		PERFORM cz_error_handler (jobID, procedureName, SQLSTATE, SQLERRM);
		--End Proc
		PERFORM cz_end_audit (jobID, 'FAIL');
		rtn_code := 16;		
	WHEN OTHERS THEN
	<?php error_handle_body(); ?>
END;

$body$
LANGUAGE PLPGSQL;

