Skip to content

Instantly share code, notes, and snippets.

@cataphract

cataphract/export.sql

Created July 18, 2013 08:38
Show Gist options
  • Save cataphract/6027718 to your computer and use it in GitHub Desktop.
Save cataphract/6027718 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
export.sql
--------------------------------------------------------
-- File created - Wednesday-July-17-2013
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Procedure RWG_ADD_SEARCH_TERM
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_ADD_SEARCH_TERM"
(
New_Term Varchar2,
category_name Varchar2,
category_display varchar2,
currentJobID NUMBER := null
)
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.
******************************************************************/
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
Parent_Id Int;
new_Term_Id Int;
keyword_id int;
Lcount Int;
Ncount Int;
v_category_display varchar2(200);
Existing_Term Exception;
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
cz_write_audit(jobId,databaseName,procedureName,'Start Procedure:' || New_Term ,SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
/*
1. Check if term exists in Search_Keyword_term
2. Insert term into Searchapp.search_keyword
3. Insert term into Searchapp.Search_Keyword_term
*/
/*
-- check if the new term exists
Select Count(*)
into Ncount
From Searchapp.Search_Keyword
where upper(Keyword) like upper(New_Term)
and upper(Display_Data_Category) like upper(category_name);
If(Ncount>0) Then
RAISE Existing_Term;
END IF;
*/
if category_display is null then
v_category_display := category_name;
else
v_category_display := category_display;
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)
Select category_name, New_Term, 'RWG:'|| category_name || ':' || New_Term,
'RWG_ADD_SEARCH_TERM', v_category_display
From dual
where not exists
(select 1 from searchapp.search_keyword x
where upper(x.data_category) = upper(category_name)
and upper(x.keyword) = upper(New_Term));
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term added to Searchapp.Search_Keyword',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
-- 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(New_Term)
and upper(data_category) = upper(category_name);
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_Keyword_Term
Insert Into Searchapp.Search_Keyword_Term
(Keyword_Term, Search_Keyword_Id, Rank, Term_Length)
select New_Term, Keyword_Id, 1, Length(New_Term) from dual
where not exists
(select 1 from searchapp.search_keyword_term x
where upper(x.keyword_term) = upper(New_Term)
and x.search_keyword_id = Keyword_Id);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term added to Searchapp.Search_Keyword_Term',Sql%Rowcount,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;
/
GRANT EXECUTE ON "TM_CZ"."RWG_ADD_SEARCH_TERM" TO "SEARCHAPP";
--------------------------------------------------------
-- DDL for Procedure RWG_ADD_TAXONOMY_TERM
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_ADD_TAXONOMY_TERM"
(
New_Term_in Varchar2,
parent_term_in Varchar2,
category_term_in varchar2,
currentJobID NUMBER := null
)
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.
******************************************************************/
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
Parent_Id Int;
New_Term_in_Id Int;
keyword_id int;
Lcount Int;
Ncount Int;
Existing_Term Exception;
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
cz_write_audit(jobId,databaseName,procedureName,'Term: '|| New_Term_in,SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
cz_write_audit(jobId,databaseName,procedureName,'Category: '|| category_term_in,SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
cz_write_audit(jobId,databaseName,procedureName,'Parent: '|| parent_term_in,SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
/*
0. Check if term exists in Search_Keyword_term
1. Insert term into Searchapp.search_keyword
2. Insert term into Searchapp.Search_Keyword_term
3. Find parent
4. Insert new term into Searchapp.Search_Taxonomy
5. Find id of new term
6. Insert relationship into searchapp.search_taxonomy_rels
*/
-- Get the data category using the parent term
/*
Select distinct(data_category)
into category_term_in
From Searchapp.Search_Keyword Where Upper(Keyword)
like upper(parent_term_in) or upper(display_data_category) like upper(parent_term_in);
*/
-- check if the new term exists (use the keyword AND the category, as the same
-- term name may be used in more than 1 category
Select Count(*)
into Ncount
From Searchapp.Search_Keyword
Where Upper(Keyword) = Upper(New_Term_in)
and upper(display_data_category) like upper(category_term_in);
cz_write_audit(jobId,databaseName,procedureName,'Ncount: '|| Ncount,SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
--If(Ncount>0) Then
-- RAISE Existing_Term;
--END IF;
-- Insert taxonomy term into searchapp.search_keyword
if Ncount = 0 then
Insert Into Searchapp.Search_Keyword (Data_Category, Keyword, Unique_Id, Source_Code, Display_Data_Category)
Select distinct(data_category), New_Term_in, 'RWG:'|| data_category || ':' || New_Term_in,
'RWG_ADD_TAXONOMY_TERM', Display_Data_Category
From Searchapp.Search_Keyword
Where upper(display_data_category) like upper(category_term_in);
/*
Insert Into Searchapp.Search_Keyword (Data_Category, Keyword, Unique_Id, Source_Code, Display_Data_Category)
Select category_term_in, New_Term_in, 'RWG:'|| category_term_in || ':' || New_Term_in, 'RWG_ADD_TAXONOMY_TERM', category_term_in from dual;
*/
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term added to Searchapp.Search_Keyword',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
commit;
end if;
-- Get the ID of the new term in Search_Keyword
Select max(Search_Keyword_Id) Into Keyword_Id
From Searchapp.Search_Keyword Where Upper(Keyword) = Upper(New_Term_in)
and upper(display_data_category) like upper(category_term_in);
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_Keyword_Term
Insert Into Searchapp.Search_Keyword_Term
(Keyword_Term, Search_Keyword_Id, Rank, Term_Length)
select New_Term_in, Keyword_Id, 1, Length(New_Term_in) from dual
where not exists
(select 1 from searchapp.search_keyword_term x
where x.search_keyword_id = Keyword_Id);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term added to Searchapp.Search_Keyword_Term',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
commit;
-- Get the ID of the parent term
Select Distinct(Term_Id)
Into Parent_Id
From Searchapp.Search_Taxonomy
Where upper(Term_Name) Like upper(parent_term_in);
if coalesce(Parent_Id,-1) > 0 then
-- Insert the new term into the taxonomy
insert into Searchapp.Search_Taxonomy (term_name, source_cd, import_date, search_keyword_id)
Select New_Term_in, parent_term_in||':'||New_Term_in, Sysdate, Keyword_Id From dual
where not exists
(select 1 from searchapp.search_taxonomy x
where x.search_keyword_id = Keyword_Id);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term added to Searchapp.Search_Taxonomy',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
commit;
-- Get the ID of the new term
Select Distinct(Term_Id)
Into New_Term_in_Id
From Searchapp.Search_Taxonomy
Where upper(Term_Name) Like upper(New_Term_in);
Insert Into Searchapp.Search_Taxonomy_Rels (Child_Id, Parent_Id)
select New_Term_in_Id, Parent_Id from dual
where not exists
(select 1 from searchapp.search_taxonomy_rels x
where x.child_id = New_Term_in_Id
and x.parent_id = Parent_id);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Term relationship added to Searchapp.Search_Taxonomy_Rels',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
commit;
end if;
---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;
/
--------------------------------------------------------
-- DDL for Procedure RWG_CREATE_ANALYSIS_ENTRY
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_CREATE_ANALYSIS_ENTRY"
(
trialID varchar2,
delete_flag varchar2:=null,
currentJobID NUMBER := null
)
AS
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
Cursor Cdelete Is
select distinct(baa.bio_assay_analysis_id)
from Biomart.bio_assay_analysis baa
where upper(baa.etl_id) like upper(trialID) || ':%' ;
cDeleteRow cDelete%rowtype;
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
-- If this flag is set to 'D', all study data from biomart.bio_assay_analysis and biomart.bio_assay_analysis_data
if(upper(delete_flag) = 'D')
THEN
cz_write_audit(jobId,databaseName,procedureName,'Start Delete bio_assay_analysis_data Loop',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
For Cdeleterow In Cdelete Loop
Delete From biomart.bio_assay_analysis_data baad
where baad.bio_assay_analysis_id = cDeleteRow.bio_assay_analysis_id;
dbms_output.put_line('Delete count for ' || cDeleteRow.bio_assay_analysis_id || '=' || SQL%ROWCOUNT);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records for analysis: ' || cDeleteRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end loop;
delete from Biomart.bio_assay_analysis baa
where upper(baa.etl_id) like upper(trialID) || ':%';
cz_write_audit(jobId,databaseName,procedureName,'Delete existing records from Biomart.bio_assay_analysis',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
END IF;
Insert Into Biomart.Bio_Assay_Analysis
(ANALYSIS_NAME, Short_Description,Long_Description,
Fold_Change_Cutoff, Pvalue_Cutoff, lsmean_cutoff,
Analysis_Method_Cd, Bio_Assay_Data_Type,
Etl_Id, Qa_Criteria, analysis_create_date, analysis_update_date)
Select
rwg.analysis_id,
rwg.Short_Desc,
rwg.Long_Desc,
rwg.foldchange_cutoff, pvalue_cutoff, lsmean_cutoff, --fold_chage, pvalue, lsmean cutoffs
rwg.Analysis_Type,
rwg.Data_Type,
rwg.study_id || ':RWG',
'(Abs(fold Change) > '||rwg.foldchange_cutoff ||
' OR fold_chage is null)'||
' AND pvalue < '||pvalue_cutoff||
' AND Max(LSMean) >' ||lsmean_cutoff,
sysdate,sysdate
From tm_lz.rwg_analysis rwg
where upper(rwg.study_id)=upper(trialID);
cz_write_audit(jobId,databaseName,procedureName,'Insert records into Biomart.Bio_Assay_Analysis',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
commit;
/* Update tm_lz.Rwg_Analysis with the newly created bio_assay_analysis_Id */
update tm_lz.Rwg_Analysis rwg
set rwg.bio_assay_analysis_id =
(select baa.bio_assay_analysis_id from Biomart.Bio_Assay_Analysis baa
where baa.analysis_name = rwg.analysis_id
and upper(baa.etl_id) like upper(trialID||':%')
and upper(rwg.study_id) like upper(trialID))
where upper(rwg.study_id) like upper(trialID);
cz_write_audit(jobId,databaseName,procedureName,'Update records in tm_lz.Rwg_Analysis with bio_assay_analysis_id ',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
commit;
---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;
/
--------------------------------------------------------
-- DDL for Procedure RWG_IMPORT_FROM_EXT
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_IMPORT_FROM_EXT"
(
trialID varchar2
,currentJobID NUMBER := null
)
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.
******************************************************************/
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
delete from TM_LZ.Rwg_Analysis where upper(study_id) =upper(trialID);
cz_write_audit(jobId,databaseName,procedureName,'Delete existing records from TM_LZ.Rwg_Analysis',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
delete from TM_LZ.Rwg_Cohorts where upper(study_id) =upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from TM_LZ.Rwg_Cohorts',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
delete from TM_LZ.Rwg_Samples where upper(study_id) =upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from TM_LZ.Rwg_Samples',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
-- not used??
-- delete from TM_LZ.RWG_BAAD_ID where upper(study_id) =upper(trialID);
--Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from TM_LZ.RWG_BAAD_ID',Sql%Rowcount,Stepct,'Done');
--stepCt := stepCt + 1;
--commit;
--Insert Analysis
INSERT INTO TM_LZ.Rwg_Analysis
(
Study_Id,
Cohorts,
ANALYSIS_ID,
pvalue_cutoff,
foldchange_cutoff ,
lsmean_cutoff,
Analysis_Type,
Data_Type,
Platform,
Long_Desc,
Short_Desc,
import_date
)
Select
Upper(Replace( Study_Id,'"','')),
REGEXP_REPLACE(upper(Replace( Cohorts,'"','')), '\s*', ''),
Replace( ANALYSIS_ID ,'"',''),
Replace( pvalue_cutoff ,'"',''),
Replace( foldchange_cutoff ,'"',''),
Replace( lsmean_cutoff ,'"',''),
Replace( Analysis_Type,'"',''),
Replace( Data_Type,'"',''),
Replace( Platform,'"',''),
Replace( Long_Desc,'"',''),
Replace( short_desc,'"',''),
Sysdate
From TM_LZ.Rwg_Analysis_Ext
where upper(study_id)=upper(trialID);
cz_write_audit(jobId,databaseName,procedureName,'Insert records from TM_LZ.Rwg_Analysis_Ext to TM_LZ.Rwg_Analysis',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
commit;
-- update bio_assay_analysis_id for any existing analysis_id (20130111 JEA)
update tm_lz.rwg_analysis t
set bio_assay_analysis_id=(select b.bio_assay_analysis_id
from biomart.bio_assay_analysis b
where b.etl_id = trialID || ':RWG'
and b.analysis_name = t.analysis_id)
where t.study_id = trialID
and exists
(select 1 from biomart.bio_assay_analysis x
where x.etl_id = trialID || ':RWG'
and t.analysis_id = x.analysis_name);
cz_write_audit(jobId,databaseName,procedureName,'Update bio_assay_analysis_id on existing rwg_analysis records',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
commit;
--Insert Cohorts
INSERT
INTO TM_LZ.Rwg_Cohorts
(
Study_Id,
Cohort_Id,
Cohort_Title, Disease, Long_Desc,
Organism, Pathology, Sample_Type, Short_Desc, Treatment,IMPORT_DATE
)
Select
Upper(Replace( Study_Id,'"','')),
trim(upper(Replace( Cohort_Id,'"',''))),
Replace( Cohort_Title, '"',''),
Replace( Disease, '"',''),
Replace( Long_Desc, '"',''),
Replace( Organism, '"',''),
Replace( Pathology, '"',''),
Replace( Sample_Type, '"',''),
Replace( Short_Desc, '"',''),
Replace( Treatment,'"',''),
Sysdate
From TM_LZ.Rwg_Cohorts_Ext
where upper(study_id)=upper(trialID);
cz_write_audit(jobId,databaseName,procedureName,'Insert records from TM_LZ.Rwg_Cohorts_Ext to TM_LZ.Rwg_Cohorts',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
commit;
--Insert samples
INSERT
INTO TM_LZ.Rwg_Samples
(
study_id, COHORTS, EXPR_ID, IMPORT_DATE
)
Select
Upper(Replace( Study_Id,'"','')),
trim(upper(Replace( Cohorts,'"',''))),
Replace( Expr_Id, '"',''),
Sysdate
From TM_LZ.Rwg_Samples_Ext
where upper(study_id)=upper(trialID);
cz_write_audit(jobId,databaseName,procedureName,'Insert records from TM_LZ.Rwg_Samples_Ext to TM_LZ.Rwg_Samples',SQL%ROWCOUNT,stepCt,'Done');
stepCt := stepCt + 1;
commit;
---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;
/
--------------------------------------------------------
-- DDL for Procedure RWG_LOAD_ANALYSIS_DATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_LOAD_ANALYSIS_DATA"
(
trialID varchar2
,currentJobID NUMBER := null
,inPlatformID number := null
,rtn_code OUT number
)
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.
******************************************************************/
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
vWZcount integer;
vLZcount integer;
vPlatformID integer;
vExpID integer;
Cursor Cdelete Is
Select distinct(bio_assay_analysis_id)
from tm_lz.Rwg_Analysis
-- From BIOMART.bio_analysis_cohort_xref
where upper(study_id) = upper(trialID);
cDeleteRow cDelete%rowtype;
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
rtn_code := 0;
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;
cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
For Cdeleterow In Cdelete Loop
Delete From BIOMART.bio_assay_analysis_data
where bio_assay_analysis_id = cDeleteRow.bio_assay_analysis_id;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records from BIOMART.bio_assay_analysis_data for analysis: ' || cDeleteRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
Delete From tm_lz.rwg_analysis_data
where bio_assay_analysis_id = cDeleteRow.bio_assay_analysis_id;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records from tm_lz.rwg_analysis_data for analysis: ' || cDeleteRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end loop;
execute immediate('truncate table tm_wz.bio_assay_analysis_data_new');
cz_write_audit(jobId,databaseName,procedureName,'Truncate tm_wz.bio_assay_analysis_data_new',0,stepCt,'Done');
stepCt := stepCt + 1;
commit;
execute immediate('truncate table tm_wz.tmp_assay_analysis_metrics');
cz_write_audit(jobId,databaseName,procedureName,'Truncate tm_wz.tmp_assay_analysis_metrics',0,stepCt,'Done');
stepCt := stepCt + 1;
commit;
-- not used ???
--delete from tm_lz.RWG_BAAD_ID where upper(study_id) =upper(trialID);
--Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from tm_lz.RWG_BAAD_ID',Sql%Rowcount,Stepct,'Done');
--stepCt := stepCt + 1;
--commit;
if (inPlatformID is null)
THEN
select max(bap.bio_assay_platform_id) into vPlatformID
from DEAPP.de_subject_sample_mapping ssm, DEAPP.de_gpl_info gpl, BIOMART.bio_assay_platform bap
where upper(ssm.gpl_id) = upper(gpl.platform)
and upper(ssm.trial_name) = upper(trialID)
and ssm.platform = 'MRNA_AFFYMETRIX'
and upper(bap.platform_accession) like '%'|| upper(gpl.platform) || '%';
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Get bio_assay_platform_ID: ' || vPlatformID,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
ELSE
vPlatformID := inPlatformID;
END IF;
select exp.bio_experiment_id into vExpID
from BIOMART.bio_experiment exp
where upper(accession) = upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Get bio_experiment_id: ' || vExpID,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
select count(*) into vLZcount from TM_LZ.RWG_ANALYSIS_DATA_EXT;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Count for TM_LZ.RWG_ANALYSIS_DATA_EXT = ' || vLZcount,0,Stepct,'Done');
stepCt := stepCt + 1;
-- count number of data records with non-numeric data in preferred_pvalue or fold_change and log
select count(*) into vLZcount
from tm_lz.rwg_analysis_data_ext
where is_number(preferred_pvalue) = 1
or is_number(fold_change) = 1;
cz_Write_Audit(Jobid,Databasename,Procedurename,'Data records dropped for non-numeric preferred_pvalue or fold_change',vLZcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
-- insert data into rwg_analysis_data, skip records with non-numeric data in preferred_pvalue or fold_change
-- change all other non-numeric data to null
insert into tm_lz.rwg_analysis_data
(study_id
,probeset
,fold_change
,pvalue
,raw_pvalue
,adjusted_pvalue
,min_lsmean
,max_lsmean
,analysis_cd
,bio_assay_analysis_id)
select rwg.study_id
,ext.probeset
,ext.fold_change
,ext.preferred_pvalue
,case when is_number(ext.raw_pvalue) = 1 then null else ext.raw_pvalue end
,case when is_number(ext.adjusted_pvalue) = 1 then null else ext.adjusted_pvalue end
,case when is_number(ext.lsmean_1) = 1 or is_number(ext.lsmean_1) = 1 then null
when ext.lsmean_1>ext.lsmean_2 then ext.lsmean_2
else ext.lsmean_1 end --min
,case when is_number(ext.lsmean_1) = 1 or is_number(ext.lsmean_1) = 1 then null
when ext.lsmean_1>ext.lsmean_2 then ext.lsmean_1
else ext.lsmean_2 end --max
,ext.analysis_id
,rwg.bio_assay_analysis_id
from TM_LZ.RWG_ANALYSIS_DATA_EXT ext
,tm_lz.rwg_analysis rwg
where trim(upper(ext.analysis_id)) = trim(upper(rwg.analysis_id))
and upper(rwg.study_id) = upper(trialID)
and is_number(ext.preferred_pvalue) = 0
and is_number(ext.fold_change) = 0;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert records into rwg_analysis_data',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
select count(*) into vWZcount from tm_lz.rwg_analysis_data
where study_id = upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Count for tm_lz.rwg_analysis_data = ' || vWZcount,0,Stepct,'Done');
stepCt := stepCt + 1;
insert into tm_wz.BIO_ASSAY_ANALYSIS_DATA_NEW(fold_change_ratio, raw_pvalue, adjusted_pvalue,
bio_assay_analysis_id, feature_group_name, bio_experiment_id, bio_assay_platform_id,
etl_id, preferred_pvalue,lsmean1, lsmean2, bio_assay_feature_group_id)
select rad.fold_change, rad.raw_pvalue,rad.adjusted_pvalue, rad.bio_assay_analysis_id, rad.probeset, vExpID, vPlatformID,
rad.study_id || ':RWG',rad.pvalue, rad.min_lsmean, rad.max_lsmean, bafg.bio_assay_feature_group_id
from tm_lz.rwg_analysis_data rad, BIOMART.bio_assay_feature_group bafg
where rad.study_id = upper(trialID) -- 20121212 JEA
and rad.probeset = bafg.feature_group_name(+);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert records into BIO_ASSAY_ANALYSIS_DATA_NEW',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
/*Calculate TEA Values */
insert into tm_wz.tmp_assay_analysis_metrics
select ad.bio_assay_analysis_id, count(*) data_ct,
avg(ad.fold_change_ratio) fc_mean,
Stddev(Ad.Fold_Change_Ratio) Fc_Stddev
from tm_wz.BIO_ASSAY_ANALYSIS_DATA_NEW ad join biomart.bio_assay_analysis a
on ad.bio_assay_analysis_id = a.bio_assay_analysis_id
where ad.fold_change_ratio is not null and a.bio_assay_data_type <> 'RBM'
group by ad.bio_assay_analysis_id
order by data_ct;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert records into tm_wz.tmp_assay_analysis_metrics ',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
merge into tm_wz.BIO_ASSAY_ANALYSIS_DATA_NEW d
using tm_wz.tmp_assay_analysis_metrics m
on (d.bio_assay_analysis_id = m.bio_assay_analysis_id and d.fold_change_ratio is not null)
when matched then
update set d.tea_normalized_pvalue = TEA_NPV_PRECOMPUTE(d.fold_change_ratio, m.fc_mean, m.fc_stddev);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update TEA records in tm_lz.BIO_ASSAY_ANALYSIS_DATA_NEW',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
/* Final Insert */
For Cdeleterow In Cdelete Loop
insert into biomart.bio_assay_analysis_data(
FOLD_CHANGE_RATIO, RAW_PVALUE,ADJUSTED_PVALUE,BIO_ASSAY_ANALYSIS_ID,
FEATURE_GROUP_NAME,BIO_EXPERIMENT_ID,BIO_ASSAY_PLATFORM_ID ,
ETL_ID,PREFERRED_PVALUE,TEA_NORMALIZED_PVALUE,BIO_ASSAY_FEATURE_GROUP_ID,
LSMEAN1,LSMEAN2 )
select
FOLD_CHANGE_RATIO,RAW_PVALUE,ADJUSTED_PVALUE,BIO_ASSAY_ANALYSIS_ID,
FEATURE_GROUP_NAME,BIO_EXPERIMENT_ID,BIO_ASSAY_PLATFORM_ID ,
ETL_ID,PREFERRED_PVALUE,TEA_NORMALIZED_PVALUE,BIO_ASSAY_FEATURE_GROUP_ID,
LSMEAN1,LSMEAN2
from tm_wz.BIO_ASSAY_ANALYSIS_DATA_NEW
where bio_assay_analysis_id = cDeleteRow.bio_assay_analysis_id;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert records into biomart.bio_assay_analysis_data for analysis: ' || cDeleteRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
--Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert records into biomart.bio_assay_analysis_data',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end loop;
cz_write_audit(jobId,databaseName,procedureName,'Procedure Complete',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
commit;
---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');
rtn_code := 16;
END;
/
--------------------------------------------------------
-- DDL for Procedure RWG_LOAD_ANALYSIS_METADATA
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_LOAD_ANALYSIS_METADATA"
(
trialID varchar2
,i_study_data_category varchar2 := 'Study'
,i_study_category_display varchar2
,currentJobID NUMBER := null
,rtn_code OUT number
)
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.
******************************************************************/
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
Dcount Int;
lcount int;
analysisCount int;
resultCount int;
ANALYSIS_COUNT_MISMATCH EXCEPTION;
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
rtn_code := 0;
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') INTO databaseName FROM dual;
procedureName := $$PLSQL_UNIT;
cz_write_audit(jobId,databaseName,procedureName,'trialID='|| trialID || ',i_study_data_category=' || i_study_data_category || ',i_study_category_display=' || i_study_category_display || ',currentJobID=' || currentJobID,0,0,'Done');
--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;
cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
/*** ***/
/* 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
select count(*) into analysisCount
from TM_LZ.Rwg_Analysis
where study_id = Upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Analysis count from TM_LZ.Rwg_Analysis =',analysisCount,Stepct,'Done');
Stepct := Stepct + 1;
--see how many of the analysees match by using the cohort analysis name
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);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Join analysis.Cohorts to Baa.Analysis_Name, Analysis count =',resultCount,Stepct,'Done');
Stepct := Stepct + 1;
IF (analysisCount != resultCount)
THEN
RAISE ANALYSIS_COUNT_MISMATCH;
END IF;
-- check if the analysis counts match. If so, skip statement. If not, keep trying
/*
IF (analysisCount = resultCount)
THEN
update TM_LZ.rwg_analysis analysis
set analysis.bio_assay_analysis_id = (
select baa.bio_assay_analysis_id
from Biomart.Bio_Assay_Analysis Baa
Where trim(upper(analysis.Cohorts))= trim(upper(Baa.Analysis_Name))
And Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%'
and analysis.study_id = Upper(trialID)
)
where analysis.study_id = Upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update TM_LZ.rwg_analysis with BAA ID, matching on cohorts ID',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
ELSE
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Analysis count did not match. Enter ELSE statement',0,Stepct,'Done');
Stepct := Stepct + 1;
--if the counts above do not match, try matching on short_desc
select count(*) into resultCount
from TM_LZ.Rwg_Analysis analysis, Biomart.Bio_Assay_Analysis Baa
Where trim(upper(analysis.short_desc))= trim(upper(baa.short_description))
and analysis.study_id = Upper(trialID)
And Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%';
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Try matching on baa.short_description, Analysis count =',resultCount,Stepct,'Done');
Stepct := Stepct + 1;
--check if the counts match
IF (analysisCount =resultCount)
THEN
--if the counts do match, update the records in bio_assay_analysis to the new analysis name
update BIOMART.bio_assay_analysis baa
set baa.analysis_name = (
select upper(trim(analysis.cohorts)) from TM_LZ.Rwg_Analysis analysis
Where trim(upper(analysis.short_desc))= trim(upper(baa.short_description))
and analysis.study_id = Upper(trialID)
) where Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%';
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update BIOMART.bio_assay_analysis with new analysis name',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
update TM_LZ.rwg_analysis analysis
set analysis.bio_assay_analysis_id = (
select baa.bio_assay_analysis_id
from Biomart.Bio_Assay_Analysis Baa
Where trim(upper(analysis.short_desc))= trim(upper(baa.short_description))
And Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%'
and analysis.study_id = Upper(trialID)
)
where analysis.study_id = Upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update TM_LZ.rwg_analysis with BAA ID, matching on short desc',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
ELSE
--If matching the short_desc did not work, try again using the long_desc
select count(*) into resultCount
from TM_LZ.Rwg_Analysis analysis, Biomart.Bio_Assay_Analysis Baa
Where trim(upper(analysis.long_desc))= trim(upper(baa.long_description))
and analysis.study_id = Upper(trialID)
And Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%';
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Try matching on baa.long_description, Analysis count =',resultCount,Stepct,'Done');
Stepct := Stepct + 1;
--check if the counts match
IF (analysisCount =resultCount)
THEN
--if the counts do match, update the records in bio_assay_analysis to the new analysis name
update BIOMART.bio_assay_analysis baa
set baa.analysis_name = (
select upper(trim(analysis.cohorts)) from TM_LZ.Rwg_Analysis analysis
Where trim(upper(analysis.long_desc))= trim(upper(baa.long_description))
and analysis.study_id = Upper(trialID)
) where Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%';
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update BIOMART.bio_assay_analysis with new analysis name',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
update TM_LZ.rwg_analysis analysis
set analysis.bio_assay_analysis_id = (
select baa.bio_assay_analysis_id
from Biomart.Bio_Assay_Analysis Baa
Where trim(upper(analysis.long_desc))= trim(upper(baa.long_description))
And Upper(Baa.Etl_Id) Like '%' || Upper(Trialid) || '%'
and analysis.study_id = Upper(trialID)
)
where analysis.study_id = Upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update TM_LZ.rwg_analysis with BAA ID, matching on long desc',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
ELSE
--if these counts do not match, then throw exception
RAISE ANALYSIS_COUNT_MISMATCH;
END IF;
END IF;
END IF;
*/
delete from Biomart.Bio_Analysis_Cohort_Xref where upper(study_id) =upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.Bio_Analysis_Cohort_Xref',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
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));
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.Bio_Analysis_Attribute_Lineage',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from Biomart.Bio_Analysis_Attribute where upper(study_id) =upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
/** Delete study from biomart.bio_assay_cohort table **/
delete from Biomart.Bio_Assay_Cohort where upper(study_id) = upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.Bio_Assay_Cohort',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
/** Populate biomart.bio_assay_cohort table **/
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, Sysdate From TM_LZ.Rwg_Cohorts
Where Upper(Study_Id) = Upper(trialID);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert into Biomart.Bio_Assay_Cohort',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
Select NVL(Max(Length(Regexp_Replace(analysis.Cohorts,'[^;]'))),0)+1
into dcount
FROM TM_LZ.Rwg_Analysis analysis;
for lcount in 1 .. dcount
Loop
Stepct := Stepct + 1;
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;
Stepct := Stepct + 1;
cz_write_audit(jobId,databaseName,procedureName,'Insert COHORTS into into Biomart.Bio_Analysis_Cohort_Xref',SQL%ROWCOUNT,stepCt,'Done');
commit;
end loop;
/*************************************/
/** POPULATE Bio_Analysis_Attribute **/
/*************************************/
-- delete study from cz_rwg_invalid_terms 20121220 JEA
delete cz_rwg_invalid_terms
where upper(study_id) = upper(trialID);
commit;
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing data from cz_rwg_invalid_terms',Sql%Rowcount,Stepct,'Done');
commit;
-- insert study as search_term term, sp will check if already exists
rwg_add_search_term(upper(trialID),i_study_data_category,i_study_category_display,jobId);
--rwg_add_taxonomy_term(upper(trialID),i_study_data_category,i_study_category_display,jobId);
-- 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));
-- sample_type: 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('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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert SAMPLE_TYPE into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
-- disease: 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), '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));
-- disease: 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('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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert disease into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
-- pathology: 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), '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));
-- pathology: 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('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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert pathology into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
-- LOOP FOR TREATMENT
Select NVL(Max(Length(Regexp_Replace(Cohort.Treatment,'[^;]'))),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;
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
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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert treatment into Bio_Analysis_Attribute (LOOP)',Sql%Rowcount,Stepct,'Done');
commit;
End Loop;
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'END TREATMENT LOOP',Sql%Rowcount,Stepct,'Done');
Commit;
-- END TREATMENT LOOP
-- organism: 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), '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));
-- organism: 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('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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert organism into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
Commit;
-- data_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(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));
-- data_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('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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert data_type into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
-- platform: 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 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));
-- platform: 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('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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert platform into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
-- LOOP FOR ANALYSIS TYPE
Select NVL(Max(Length(Regexp_Replace(analysis.Analysis_Type,'[^;]'))),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;
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
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;
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'LOOP: Insert ANALYSIS_TYPE into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
End Loop;
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'END ANALYSIS_TYPE LOOP',Sql%Rowcount,Stepct,'Done');
Commit;
-- END ANALYSIS TYPE
-- search_area: 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(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));
-- search_area: insert terms into attribute table
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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert search_area into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
Commit;
-- data source: 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(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));
-- data source: insert terms into attribute table
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);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert DATA_SOURCE into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
Commit;
-- study_design: 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(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));
-- study_design: insert terms into attribute table
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(
decode(Ext.experimental_design,'Clinical','Clinical Trial',
ext.experimental_design)
)= Upper(Tax.Term_Name)
and upper(ext.study_id) =upper(trialID);
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert study_design into Bio_Analysis_Attribute',Sql%Rowcount,Stepct,'Done');
commit;
-- populate biomart.bio_analysis_attribute_lineage in one shot
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;
Stepct := Stepct + 1;
cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert attribute links into bio_analysis_attribute_lineage',Sql%Rowcount,Stepct,'Done');
commit;
/* END populate */
/*Update the 'analysis_update_date' in bio_assay_analysis (this date is used by solr for incremental updates*/
update BIOMART.bio_assay_analysis baa
set baa.ANALYSIS_UPDATE_DATE = sysdate
where upper(baa.etl_id) like upper(trialID||'%');
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Update ANALYSIS_UPDATE_DATE with sysdate',Sql%Rowcount,Stepct,'Done');
commit;
cz_write_audit(jobId,databaseName,procedureName,'End Procedure',SQL%ROWCOUNT,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 ANALYSIS_COUNT_MISMATCH then
Stepct := Stepct + 1;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Check for analysis in rwg_analysis not in biomart.bio_assay_analysis',Sql%Rowcount,Stepct,'Done');
--Handle errors.
cz_error_handler (jobID, procedureName);
--End Proc
cz_end_audit (jobID, 'FAIL');
rtn_code := 16;
WHEN OTHERS THEN
--Handle errors.
cz_error_handler (jobID, procedureName);
--End Proc
cz_end_audit (jobID, 'FAIL');
rtn_code := 16;
END;
/
--------------------------------------------------------
-- DDL for Procedure RWG_LOAD_FROM_DEV
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_LOAD_FROM_DEV"
(
trialID varchar2,
updateRefData integer := 0,
currentJobID NUMBER := null
)
AS
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
sqlText varchar(500);
partExists integer(1);
EXPERIMENT_ID_MISSING EXCEPTION;
V_BIO_EXP_ID number(18,0);
cursor cInsert is
select distinct(dev.bio_assay_analysis_id)
from BIOMART.bio_analysis_cohort_xref@DEVLINK dev
where upper(dev.study_id) = upper(trialID);
cInsertRow cInsert%rowtype;
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
select bio_experiment_id into V_BIO_EXP_ID from biomart.bio_experiment@DEVLINK
where upper(accession) like upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Retreived exp id ' ||V_BIO_EXP_ID || ' for trial ' || upper(trialID),Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
--check that the experiment id exists
if (V_BIO_EXP_ID < 0)
THEN
RAISE EXPERIMENT_ID_MISSING;
END IF;
/************* Delete existing records for study ******************/
delete from biomart.bio_analysis_attribute_lineage baal
where baal.bio_analysis_attribute_id in (select baa.bio_analysis_attribute_id
from biomart.bio_analysis_attribute baa
where upper(baa.study_id) = upper(trialID)) ;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_analysis_attribute_lineage',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_analysis_attribute baa
where upper(baa.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_analysis_attribute',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_analysis_cohort_xref bacx
where upper(bacx.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_analysis_cohort_xref',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_cohort_exp_xref bcex
where upper(bcex.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_cohort_exp_xref',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_assay_cohort bac
where upper(bac.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_assay_cohort',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_assay_analysis_data baad
where baad.bio_experiment_id = V_BIO_EXP_ID;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_assay_analysis_data',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
/* because the data from bio_assay_analysis_data is deleted first, need to find the
bio_assay_analysis_ids to delete by referring back to dev */
delete from biomart.bio_assay_analysis baa
where baa.bio_assay_analysis_id in (select devbaa.bio_assay_analysis_id
from biomart.bio_assay_analysis@DEVLINK devbaa, biomart.bio_assay_analysis_data@DEVLINK devbaad
where devbaa.bio_assay_analysis_id = devbaad.bio_assay_analysis_id
and devbaad.bio_experiment_id =V_BIO_EXP_ID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_assay_analysis',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
/*************************************************/
/************ Update Reference data ***************/
if (updateRefData = 1)
THEN
insert into biomart.bio_assay_feature_group
select * from biomart.bio_assay_feature_group@DEVLINK dev
where dev.bio_assay_feature_group_id not in
(select bafg.bio_assay_feature_group_id
from biomart.bio_assay_feature_group bafg);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_assay_feature_group',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_marker
select * from biomart.bio_marker@DEVLINK dev
where dev.bio_marker_id not in
(select bm.bio_marker_id from biomart.bio_marker bm);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_marker',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_data_correl_descr
select * from biomart.bio_data_correl_descr@DEVLINK dev
where dev.bio_data_correl_descr_id not in (
select bdcd.bio_data_correl_descr_id
from biomart.bio_data_correl_descr bdcd);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_data_correl_descr',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_data_correlation
select * from biomart.bio_data_correlation@DEVLINK dev
where dev.bio_data_id >
(select max(bdc.bio_data_id)
from biomart.bio_data_correlation bdc);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_data_correlation',SQL%ROWCOUNT,stepCt,'Done');
commit;
/* NOTE: This statement will not update changes to existing bio_assay_feature_group_ids, only new IDs */
insert into biomart.bio_assay_data_annotation
select * from biomart.bio_assay_data_annotation@DEVLINK dev
where dev.bio_assay_feature_group_id >
(select max(bada1.bio_assay_feature_group_id) from biomart.bio_assay_data_annotation bada1);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_assay_data_annotation',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_asy_analysis_pltfm
select * from biomart.bio_asy_analysis_pltfm@DEVLINK dev
where dev.bio_asy_analysis_pltfm_id not in
(select baap.bio_asy_analysis_pltfm_id from biomart.bio_asy_analysis_pltfm baap);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_asy_analysis_pltfm',SQL%ROWCOUNT,stepCt,'Done');
commit;
END IF;
/*** End Update Reference data ***/
insert into searchapp.search_keyword
select * from searchapp.search_keyword@DEVLINK dev
where dev.SEARCH_KEYWORD_ID not in (
select sk.SEARCH_KEYWORD_ID from searchapp.search_keyword sk)
and dev.UNIQUE_ID || dev.DATA_CATEGORY not in
( select sk2.UNIQUE_ID || sk2.DATA_CATEGORY from searchapp.search_keyword sk2);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into searchapp.search_keyword',SQL%ROWCOUNT,stepCt,'Done');
commit;
/* This should be revised later - the second "in" clause should not be needed
but has been added due to data issues between dev/qa */
insert into searchapp.search_keyword_term
select * from searchapp.search_keyword_term@DEVLINK dev
where dev.search_keyword_term_id not in (
select skt.search_keyword_term_id
from searchapp.search_keyword_term skt)
and dev.search_keyword_id in (select sk.search_keyword_id from searchapp.search_keyword sk);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into searchapp.search_keyword_term',SQL%ROWCOUNT,stepCt,'Done');
commit;
For cInsertRow In cInsert Loop
insert into biomart.bio_assay_analysis (ANALYSIS_NAME,SHORT_DESCRIPTION,ANALYSIS_CREATE_DATE,ANALYST_ID,BIO_ASSAY_ANALYSIS_ID,ANALYSIS_VERSION,
FOLD_CHANGE_CUTOFF,PVALUE_CUTOFF,RVALUE_CUTOFF,BIO_ASY_ANALYSIS_PLTFM_ID,BIO_SOURCE_IMPORT_ID,
ANALYSIS_TYPE,ANALYST_NAME,ANALYSIS_METHOD_CD,BIO_ASSAY_DATA_TYPE,ETL_ID,LONG_DESCRIPTION,QA_CRITERIA,
DATA_COUNT,TEA_DATA_COUNT,ANALYSIS_UPDATE_DATE,LSMEAN_CUTOFF)
select ANALYSIS_NAME,SHORT_DESCRIPTION,ANALYSIS_CREATE_DATE,ANALYST_ID,BIO_ASSAY_ANALYSIS_ID,ANALYSIS_VERSION,
FOLD_CHANGE_CUTOFF,PVALUE_CUTOFF,RVALUE_CUTOFF,BIO_ASY_ANALYSIS_PLTFM_ID,BIO_SOURCE_IMPORT_ID,
ANALYSIS_TYPE,ANALYST_NAME,ANALYSIS_METHOD_CD,BIO_ASSAY_DATA_TYPE,ETL_ID,LONG_DESCRIPTION,QA_CRITERIA,
DATA_COUNT,TEA_DATA_COUNT,sysdate,LSMEAN_CUTOFF
from biomart.bio_assay_analysis@DEVLINK dev2
where dev2.bio_assay_analysis_id = cInsertRow.bio_assay_analysis_id;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert bio_assay_analysis records for ' || cInsertRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
insert into biomart.bio_assay_analysis_data
select *
from biomart.bio_assay_analysis_data@DEVLINK dev
where dev.bio_assay_analysis_id = cInsertRow.bio_assay_analysis_id;
Stepct := Stepct + 1;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert bio_assay_analysis_data records for ' || cInsertRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
commit;
end loop;
insert into biomart.bio_analysis_attribute
select * from biomart.bio_analysis_attribute@DEVLINK dev
where upper(dev.study_id) = upper(trialID);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_analysis_attribute',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_analysis_attribute_lineage
select * from biomart.bio_analysis_attribute_lineage@DEVLINK dev
where dev.bio_analysis_attribute_id in (
select dev2.bio_analysis_attribute_id
from biomart.bio_analysis_attribute@DEVLINK dev2
where upper(dev2.study_id) = upper(trialID) );
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_analysis_attribute_lineage',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_analysis_cohort_xref
select * from biomart.bio_analysis_cohort_xref@DEVLINK dev
where upper(dev.study_id) = upper(trialID);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_analysis_cohort_xref',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_cohort_exp_xref
select * from biomart.bio_cohort_exp_xref@DEVLINK dev
where upper(dev.study_id) = upper(trialID);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_cohort_exp_xref',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into biomart.bio_assay_cohort
select * from biomart.bio_assay_cohort@DEVLINK dev
where upper(dev.study_id) = upper(trialID);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_assay_cohort',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into searchapp.search_taxonomy
select * from searchapp.search_taxonomy@DEVLINK dev
where dev.term_id not in (
select st.term_id
from searchapp.search_taxonomy st);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into searchapp.search_taxonomy',SQL%ROWCOUNT,stepCt,'Done');
commit;
insert into searchapp.search_taxonomy_rels
select * from searchapp.search_taxonomy_rels@DEVLINK dev
where dev.search_taxonomy_rels_id not in (
select str.search_taxonomy_rels_id
from searchapp.search_taxonomy_rels str);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into searchapp.search_taxonomy_rels',SQL%ROWCOUNT,stepCt,'Done');
commit;
/* non-clinical studies (in-vivo/in-vitro) will not get loaded to this table
during the standard ETL process for DSE studies, but this is needed for the
RWG results view. This might change in future releases, and if so, this will be removed */
insert into biomart.bio_clinical_trial (TRIAL_NUMBER,STUDY_OWNER,STUDY_PHASE,BLINDING_PROCEDURE,
STUDYTYPE,DURATION_OF_STUDY_WEEKS,NUMBER_OF_PATIENTS,NUMBER_OF_SITES,
ROUTE_OF_ADMINISTRATION,DOSING_REGIMEN,GROUP_ASSIGNMENT,TYPE_OF_CONTROL,
COMPLETION_DATE,PRIMARY_END_POINTS,SECONDARY_END_POINTS,
SUBJECTS,GENDER_RESTRICTION_MFB,MIN_AGE,MAX_AGE,
SECONDARY_IDS,BIO_EXPERIMENT_ID,DEVELOPMENT_PARTNER,
GEO_PLATFORM,MAIN_FINDINGS,PLATFORM_NAME,SEARCH_AREA)
select TRIAL_NUMBER,STUDY_OWNER,STUDY_PHASE,BLINDING_PROCEDURE,
STUDYTYPE,DURATION_OF_STUDY_WEEKS,NUMBER_OF_PATIENTS,NUMBER_OF_SITES,
ROUTE_OF_ADMINISTRATION,DOSING_REGIMEN,GROUP_ASSIGNMENT,TYPE_OF_CONTROL,
COMPLETION_DATE,PRIMARY_END_POINTS,SECONDARY_END_POINTS,
SUBJECTS,GENDER_RESTRICTION_MFB,MIN_AGE,MAX_AGE,
SECONDARY_IDS,BIO_EXPERIMENT_ID,DEVELOPMENT_PARTNER,
GEO_PLATFORM,MAIN_FINDINGS,PLATFORM_NAME,SEARCH_AREA
from biomart.bio_clinical_trial@DEVLINK dev
where dev.trial_number not in
(select bct.trial_number from biomart.bio_clinical_trial bct);
Stepct := Stepct + 1;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Insert into biomart.bio_clinical_trial',SQL%ROWCOUNT,stepCt,'Done');
commit;
/***********************************/
/**** Populate Heat_map_resultes ***/
/***********************************/
/* Check if partition exist, truncate if so, create it if not */
select count(*) into partExists
from all_tables
where table_name = 'HEAT_MAP_RESULTS'
and owner = 'BIOMART'
and partitioned = 'YES';
if partExists > 0 then
-- check if partition exists
select count(*)
into partExists
from all_tab_partitions
where table_name = 'HEAT_MAP_RESULTS'
and table_owner = 'BIOMART'
and partition_name = upper(trialID);
if partExists = 0 then
-- needed to add partition to table
sqlText := 'alter table BIOMART.HEAT_MAP_RESULTS add PARTITION "' || upper(trialID) || '" VALUES (' || '''' || upper(trialID) || '''' || ') ' ||
'PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING ' ||
'STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 ' ||
'PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ';
execute immediate(sqlText);
cz_write_audit(jobId,databaseName,procedureName,'Adding partition to BIOMART.HEAT_MAP_RESULTS ',0,stepCt,'Done');
stepCt := stepCt + 1;
else
--truncate partition
sqlText := 'alter table BIOMART.HEAT_MAP_RESULTS truncate partition ' || upper(trialID);
execute immediate(sqlText);
cz_write_audit(jobId,databaseName,procedureName,'Truncate partition in BIOMART.HEAT_MAP_RESULTS',0,stepCt,'Done');
stepCt := stepCt + 1;
end if;
else
-- table is not partitioned so just do regular delete
delete from biomart.heat_map_results
where upper(trial_name) = upper(trialID);
cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records for study from heat_map_results',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end if;
/* Populate the partition from DEV */
For cInsertRow In cInsert Loop
insert into biomart.heat_map_results
select * from biomart.heat_map_results@DEVLINK dev
where upper(dev.trial_name) = upper(trialID)
and dev.bio_assay_analysis_id = cInsertRow.bio_assay_analysis_id;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert heat_map_results records for ' || cInsertRow.bio_assay_analysis_id,Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end loop;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'End Procedure',SQL%ROWCOUNT,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;
/
--------------------------------------------------------
-- DDL for Procedure RWG_LOAD_HEAT_MAP_RESULTS
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_LOAD_HEAT_MAP_RESULTS"
(
In_Study_Id In Varchar2
,currentJobID NUMBER := null
) 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.
******************************************************************/
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
sqlText varchar(500);
partExists integer(1);
/*
cursor cInsert is
Select Distinct Decode(B1.Study_Id, 'C0524T03_RWG', 'C0524T03', B1.Study_Id) Study_Id, B1.Bio_Assay_Analysis_Id, Cohort_Id
from biomart.bio_analysis_attribute b1, biomart.bio_analysis_cohort_xref b2
Where B1.Bio_Assay_Analysis_Id = B2.Bio_Assay_Analysis_Id
and upper(b1.study_id)=upper(in_study_id);
Cursor Cdelete Is
Select Distinct Bio_Assay_Analysis_Id
From biomart.Heat_Map_Results
where upper(trial_name) = upper(in_study_id);
cursor ctaIds is
select bio_assay_analysis_id
from biomart.bio_assay_analysis
where etl_id = upper(in_study_id) || ':RWG';
cInsertRow cInsert%rowtype;
cDeleteRow cDelete%rowtype;
cCtaId ctaIds%rowtype;
*/
i integer;
begin
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Start Procedure',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
select count(*) into partExists
from all_tables
where table_name = 'HEAT_MAP_RESULTS'
and owner = 'BIOMART'
and partitioned = 'YES';
if partExists > 0 then
-- check if partition exists
select count(*)
into partExists
from all_tab_partitions
where table_name = 'HEAT_MAP_RESULTS'
and table_owner = 'BIOMART'
and partition_name = upper(in_study_id);
if partExists = 0 then
-- needed to add partition to table
sqlText := 'alter table BIOMART.HEAT_MAP_RESULTS add PARTITION "' || upper(in_study_id) || '" VALUES (' || '''' || upper(in_study_id) || '''' || ') ' ||
'PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING ' ||
'STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 ' ||
'PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ';
execute immediate(sqlText);
cz_write_audit(jobId,databaseName,procedureName,'Adding partition to BIOMART.HEAT_MAP_RESULTS ',0,stepCt,'Done');
stepCt := stepCt + 1;
else
--truncate partition
sqlText := 'alter table BIOMART.HEAT_MAP_RESULTS truncate partition ' || upper(in_study_id);
execute immediate(sqlText);
cz_write_audit(jobId,databaseName,procedureName,'Truncate partition in BIOMART.HEAT_MAP_RESULTS',0,stepCt,'Done');
stepCt := stepCt + 1;
end if;
else
-- table is not partitioned so just do regular delete
delete from biomart.heat_map_results
where upper(trial_name) = upper(in_study_id);
cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records for study from heat_map_results',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end if;
-- Delete existing data for study from cta_results
delete from biomart.cta_results
where bio_assay_analysis_id in (select x.bio_assay_analysis_id from biomart.bio_assay_analysis x where x.etl_id = upper(in_study_id) || ':RWG');
cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records for study from cta_results',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
-- changed to use sql instead of view, view pulled back all studies 20121203 JEA
insert into biomart.heat_map_results
(subject_id
,log_intensity
,cohort_id
,probe_id
,bio_assay_feature_group_id
,fold_change_ratio
,tea_normalized_pvalue
,bio_marker_name
,bio_marker_id
,search_keyword_id
,bio_assay_analysis_id
,trial_name
,significant
,gene_id
,assay_id
,preferred_pvalue
)
select replace(replace(pd.sourcesystem_cd,xref.study_id,''),':','') as subject_id
,md.log_intensity
,cex.cohort_id
,dma.probeset
,baad.bio_assay_feature_group_id
,baad.Fold_Change_Ratio
,baad.tea_normalized_pvalue as tea_normalized_pvalue
,f.bio_marker_name
,f.bio_marker_id
,i.SEARCH_KEYWORD_ID
,xref.bio_assay_analysis_id
,xref.study_id
,case when (Abs(baaD.Fold_Change_Ratio) > baa.Fold_Change_Cutoff Or baaD.Fold_Change_Ratio Is Null)
AND NVL(baad.preferred_pvalue, baad.tea_normalized_pvalue) < baa.pvalue_cutoff
AND ((baad.lsmean1 > baa.lsmean_cutoff OR baad.lsmean2 > baa.lsmean_cutoff) OR (baad.lsmean1 is null AND baad.lsmean2 is null))
THEN 1
Else 0
End Significant
,f.Primary_External_Id
,sm.assay_id
,baad.preferred_pvalue
from biomart.bio_analysis_cohort_xref xref
inner join biomart.bio_cohort_exp_xref cex
on xref.study_id = cex.study_id
and xref.cohort_id = cex.cohort_id
inner join deapp.de_subject_sample_mapping sm
on xref.study_id = sm.trial_name
and cex.exp_id = sm.assay_id
inner join deapp.de_subject_microarray_data md
on md.trial_source = xref.study_id || ':' || coalesce(sm.source_cd,'STD')
and cex.exp_id = md.assay_id
inner join tm_cz.probeset_deapp dma -- use tm_cz.probeset_deapp because there is only a single record for the probeset
on md.probeset_id = dma.probeset_id
inner join i2b2demodata.patient_dimension pd
on sm.patient_id = pd.patient_num
inner join biomart.bio_assay_analysis_data baad
on xref.bio_assay_analysis_id = baad.bio_assay_analysis_id
and baad.feature_group_name = dma.probeset
INNER JOIN biomart.bio_assay_data_annotation e on e.bio_assay_feature_group_id = baad.bio_assay_feature_group_id
INNER JOIN biomart.bio_marker f on f.bio_marker_id = e.bio_marker_id
inner join biomart.bio_assay_analysis baa
on xref.bio_assay_analysis_id = baa.bio_assay_analysis_id
Inner Join biomart.bio_marker_correl_mv h ON f.bio_marker_id = h.asso_bio_marker_id AND h.correl_type in ('GENE', 'HOMOLOGENE_GENE', 'PROTEIN TO GENE')
Inner Join searchapp.search_keyword i ON f.bio_marker_id = i.bio_data_id
where xref.study_id = upper(in_study_id);
Cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert study to heat_map_results',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
update BIOMART.bio_assay_analysis baa
set baa.analysis_update_date = sysdate
where baa.bio_assay_analysis_id in (select x.bio_assay_analysis_id from biomart.bio_assay_analysis x where x.etl_id = upper(in_study_id) || ':RWG');
cz_Write_Audit(Jobid,Databasename,Procedurename,'Updated analysis_update_date for analyses of study',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
insert into biomart.cta_results
(bio_assay_analysis_id,
search_keyword_id,
keyword,
bio_marker_id,
bio_marker_name,
gene_id,
probe_id,
fold_change,
preferred_pvalue,
organism
)
select distinct h.bio_assay_analysis_id
,h.search_keyword_id
,upper(s.keyword)
,h.bio_marker_id
,b.bio_marker_name
,b.primary_external_id
,h.probe_id
,h.fold_change_ratio
,h.preferred_pvalue
,b.organism
from biomart.heat_map_results h
,biomart.bio_marker b
,searchapp.search_keyword s
where h.trial_name = upper(in_study_id)
and h.bio_marker_id=b.bio_marker_id
and h.search_keyword_id=s.search_keyword_id;
cz_Write_Audit(Jobid,Databasename,Procedurename,'Insert records for study into cta_results',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
cz_write_audit(jobId,databaseName,procedureName,'Procedure Complete',0,stepCt,'Done');
Stepct := Stepct + 1;
commit;
---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;
/
--------------------------------------------------------
-- DDL for Procedure RWG_REMOVE_STUDY
--------------------------------------------------------
set define off;
CREATE OR REPLACE PROCEDURE "TM_CZ"."RWG_REMOVE_STUDY"
(
trialID varchar2,
currentJobID NUMBER := null
)
AS
--Audit variables
newJobFlag INTEGER(1);
databaseName VARCHAR(100);
procedureName VARCHAR(100);
jobID number(18,0);
stepCt number(18,0);
sqlText varchar(500);
partExists integer(1);
EXPERIMENT_ID_MISSING EXCEPTION;
V_BIO_EXP_ID number(18,0);
BEGIN
--Set Audit Parameters
newJobFlag := 0; -- False (Default)
jobID := currentJobID;
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;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'Start Procedure',SQL%ROWCOUNT,stepCt,'Done');
Stepct := Stepct + 1;
select bio_experiment_id into V_BIO_EXP_ID from biomart.bio_experiment
where upper(accession) like upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Retreived exp id ' ||V_BIO_EXP_ID || ' for trial ' || upper(trialID),Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
--check that the experiment id exists
if (V_BIO_EXP_ID < 0)
THEN
RAISE EXPERIMENT_ID_MISSING;
END IF;
/************* Delete existing records for study ******************/
delete from biomart.bio_analysis_attribute_lineage baal
where baal.bio_analysis_attribute_id in (select baa.bio_analysis_attribute_id
from biomart.bio_analysis_attribute baa
where upper(baa.study_id) = upper(trialID)) ;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_analysis_attribute_lineage',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_analysis_attribute baa
where upper(baa.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_analysis_attribute',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_analysis_cohort_xref bacx
where upper(bacx.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_analysis_cohort_xref',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_cohort_exp_xref bcex
where upper(bcex.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_cohort_exp_xref',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_assay_cohort bac
where upper(bac.study_id) = upper(trialID);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_assay_cohort',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_assay_analysis_data baad
where baad.bio_experiment_id = V_BIO_EXP_ID;
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_assay_analysis_data',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
delete from biomart.bio_assay_analysis baa
where baa.bio_assay_analysis_id in (
select distinct(hmr.bio_assay_analysis_id)
from biomart.heat_map_results hmr
where hmr.trial_name = upper(trialID)
);
tm_cz.Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from Biomart.bio_assay_analysis',Sql%Rowcount,Stepct,'Done');
Stepct := Stepct + 1;
/***********************************/
/**** Remove Heat_map_resultes ***/
/***********************************/
/* Check if partition exist, truncate if so, create it if not */
select count(*) into partExists
from all_tables
where table_name = 'HEAT_MAP_RESULTS'
and owner = 'BIOMART'
and partitioned = 'YES';
if partExists > 0 then
--truncate partition
sqlText := 'alter table BIOMART.HEAT_MAP_RESULTS truncate partition ' || upper(trialID);
execute immediate(sqlText);
cz_write_audit(jobId,databaseName,procedureName,'Truncate partition in BIOMART.HEAT_MAP_RESULTS',0,stepCt,'Done');
stepCt := stepCt + 1;
else
-- table is not partitioned so just do regular delete
delete from biomart.heat_map_results
where upper(trial_name) = upper(trialID);
cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete records for study from heat_map_results',Sql%Rowcount,Stepct,'Done');
stepCt := stepCt + 1;
commit;
end if;
tm_cz.cz_write_audit(jobId,databaseName,procedureName,'End Procedure',SQL%ROWCOUNT,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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment