CREATE OR REPLACE procedure temp_annual
as

s1 sys_refcursor;
s2 sys_refcursor;

str1 varchar2(4000);
str2 varchar2(4000);
str3 varchar2(4000);
str4 varchar2(4000);
str5 varchar2(4000);
str6 varchar2(4000);
str7 varchar2(4000);
     
type t1 is table of number(15);
type t2 is table of varchar2(70);

l_caseseq_nt1 t1 := t1();
l_caseseq_nt2 t1 := t1();
l_caseseq_nt3 t1 := t1();
l_as_reported_nt t2 := t2();

l_versionno case_version.version_number%type;
l_caseid case_version.case_id%type;
l_caseseq case_version.case_seq%TYPE;
l_maxverno case_version.version_number%TYPE;
l_idx_counter1 PLS_INTEGER   := 0;
l_asreported case_drug.as_reported%type;


BEGIN

str1:= 
'select cv.case_seq 
from case_version cv,adverse_event ae
where study_code = '||''''||'SA-NXY-0007'||''''||
'and cv.case_seq = ae.case_seq
and ae.serious_yn_code = '||''''||'Y'||'''' ;


str2:= 
'select rd.case_seq
from report_detail rd,report_master rm
where rd.report_master_id = rm.report_master_id
and rm.cntry_drug_apprval_numb_id in (SELECT cntry_drug_apprval_numb_id
								  	  FROM cntry_drug_approval_numb, drug_approval_number                                                                                                                                                             WHERE                                                                                                                                                                                                                                    cntry_drug_approval_numb.drug_approval_number_id = drug_approval_number.drug_approval_number_id
									  AND drug_approval_number.drug_approval_number in ('||''''||'61,021'||''''||','||''''||'14-763'||''''||')
									  )
and rm.report_type_code In ('||''''||'15 DAY IND'||''''||','||''''||'7 DAY IND'||''''||')' ;


str3:= '('||str1 ||' UNION '|| str2||')';


str4:= 

'select cv.case_seq 
from case_version cv
where 
      cv.ds_initial_received_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')

OR cv.d_case_birth_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')

OR cv.report_clock_start_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')

OR cv.periodic_rep_clock_start_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')' ;


str5:= 
'select rd.case_seq
from report_detail rd,report_master rm
where rd.report_master_id = rm.report_master_id
and rm.report_type_code In ('||''''||'15 DAY IND'||''''||','||''''||'7 DAY IND'||''''||')
and rm.submitted_date BETWEEN TO_DATE('
         || ''''
         || '01-JAN-2004'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'') AND TO_DATE('
         || ''''
         || '04-JAN-2007'
         || ''''
         || ',''DD-MON-YYYY HH:MI:SS AM'')' ;



str6:= '('||str4 ||' UNION '|| str5||')';


str7 := 'select case_seq from ( '||str3||' intersect'||str6||')';


open s1 for str7;
fetch s1 bulk collect into l_caseseq_nt1;
close s1;


for i in 1..l_caseseq_nt1.count
loop

select version_number,case_id,case_seq into l_versionno,l_caseid,l_caseseq
from case_version 
where case_seq = l_caseseq_nt1(i) ;

select max(version_number) into l_maxverno
from case_version
where case_id = l_caseid
and status_code = 'CLD';

IF l_versionno = l_maxverno
THEN


open s1 for select upper(as_reported) 
from case_drug
where drug_type_code in ('S','I') 
and (priority_order_no = 1 OR key_ingredient_id = 276 )
and case_seq = l_caseseq ;

fetch s1 INTO l_asreported;

close s1;

l_idx_counter1 := l_idx_counter1 + 1;
l_as_reported_nt.EXTEND;
l_as_reported_nt(l_idx_counter1) := l_asreported ;

END IF;
END LOOP;

END;
/