DB2

 DROP SPECIFIC PROCEDURE GET_DEAL_LIST
;
CREATE PROCEDURE GET_DEAL_LIST(IN "@DEAL_ID" VARCHAR(40),
                                          IN "@STRFIELDS" VARCHAR(4000),
                                          IN "@STRFIELDSTYPES" VARCHAR(1000),
                                          IN "@TEMPLATEID" VARCHAR(200),
                                          IN "@STARTDATE" VARCHAR(200),
                                          IN "@ENDDATE" VARCHAR(200),
                                          IN "@ISINNUMBER" VARCHAR(100),
                                          IN "@CYCLE" VARCHAR(10),
                                          IN "@TRADEDATEFIELDID" VARCHAR(50),
                                          IN "@ISINFIELDID" VARCHAR(50))
    SPECIFIC GET_DEAL_LIST
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL
BEGIN

DECLARE i_count int;
DECLARE v_field_type int;
DECLARE v_field_id varchar(36);
DECLARE v_isFeature int;
DECLARE ii_count int;

DECLARE v_sql VARCHAR(4000);
DECLARE v_deal_id varchar(40);
DECLARE v_deal_id_list varchar(4000);
DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE i_indexNum int;
DECLARE v_fieldid varchar(40);
DECLARE v_featureid varchar(40);
DECLARE v_issuedate_id varchar(40);
DECLARE v_valuation_id varchar(40);
DECLARE v_kodate_id varchar(40);

DECLARE v_subfieldary varchar(1000);
DECLARE v_fieldtype varchar(40);
DECLARE v_subfieldtype varchar(100);
--DECLARE conTradeDateFieldId varchar(100);
--DECLARE conISINField varchar(100);
DECLARE v_sql_code int;
DECLARE v_stmt statement;
--second amendment

declare c2 cursor with return for v_stmt;
DECLARE c1 CURSOR  FOR 

WITH
    Descedents  (DEAL_NAME, DEAL_ID, PARENT_NODE_ID,IS_LEAF_NODE, DEAL_DESC,  LEVEL)  AS           
        (SELECT Root.DEAL_NAME, Root.DEAL_ID, Root.PARENT_NODE_ID,root.IS_LEAF_NODE, root.DEAL_DESC, 0 as LEVEL
          FROM SPEARBATCH.TBL_DEAL Root
          WHERE Root.DEAL_ID =@DEAL_ID   and Root.IS_LEAF_NODE=0                    
         UNION ALL                                          
         SELECT  de.DEAL_NAME, de.DEAL_ID, de.PARENT_NODE_ID,de.IS_LEAF_NODE, de.DEAL_DESC, Level+1
          FROM SPEARBATCH.TBL_DEAL de, Descedents CHAIN
          WHERE CHAIN.DEAL_ID = de.PARENT_NODE_ID   and de.IS_LEAF_NODE=0
        )
SELECT DEAL_ID FROM Descedents;

declare c3 cursor for
     select c.field_id from spearbatch.tbl_field c where c.field_name='Issue Date';
    
declare c4 cursor for
     select c.field_id from spearbatch.tbl_field c where c.field_name='Valuation Date';
    
 declare c5 cursor for
     select c.field_id from spearbatch.tbl_field c where c.field_name='KO Date';
 
 declare c6 cursor for
      select count(*) from spearbatch.tbl_column_header where template_id=@TEMPLATEID;
 
 declare c7 cursor for
       select field_id,field_type,feature_id from spearbatch.tbl_column_header where template_id=@TEMPLATEID ORDER BY column_order;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND set v_sql_code=-1;
set v_sql_code=0;
--set conISINField='60377be5-7023-483e-a706-3060cf97e022';
--set conTradeDateFieldId='8c305f27-4080-433a-84a2-a94f9a15c2d4';
SET v_sql= 'select d.DEAL_ID,d.DEAL_NAME,d.MODIFIED_TIME,ri.INSTANCE_ID,';
set i_indexNum=0;

  open c3;
   fetch c3 into v_issuedate_id;
  close c3;
 
  open c4;
   fetch c4 into v_valuation_id;
  close c4;
 
   open c5;
   fetch c5 into v_kodate_id;
  close c5;
 
  open c7;
  open c6;
   fetch c6 into i_count;
   set ii_count=i_count;
     while i_count>0 do
       fetch c7 into  v_field_id,v_field_type,v_featureid;
        if  v_featureid IS NULL  then 
         if v_field_type=1 then
           set v_sql = v_sql || ' rv' ||  cast(i_indexNum as char(2)) || '.FIELD_VALUE_STRING  as FIELD_VALUE' ||  cast(i_indexNum as char(2))|| ', ' ;
         elseif v_field_type=2 then
           set v_sql = v_sql ||  ' char(rv' || cast(i_indexNum as char(2))|| '.FIELD_VALUE_STRING) as FIELD_VALUE' ||  cast(i_indexNum as char(2)) || ', ' ;
         elseif v_field_type=3 then
           set v_sql =  v_sql || ' SPEARBATCH.DECIMAL_TO_VARCHAR(cast(ROUND(rv' || cast(i_indexNum as char(2))|| '.FIELD_VALUE_FLOAT,5) as decimal(20,5))) as FIELD_VALUE' ||  cast(i_indexNum as char(2))|| ', ' ;
         elseif v_field_type=4 then 
        set v_sql =  v_sql || ' char(rv' ||cast(i_indexNum as char(2)) || '.FIELD_VALUE_DATE,USA) as FIELD_VALUE' ||cast(i_indexNum as char(2)) || ', ' ;
      end if;
     else
      set v_sql =  v_sql || '''0'' as IELD_VALUE' ||  cast(i_indexNum as char(2))|| ', ' ;

     end if;
    set i_indexNum=i_indexNum+1;
    set i_count=i_count-1;
  end  while;
close c7;

set v_sql= v_sql || ' (select doc.DOC_NAME from spearbatch.tbl_document doc where d.DEAL_ID= doc.REQUEST_INSTANCE_DEAL_ID and doc.DOC_TYPE=''DOC'' order by doc.REPORT_ID fetch first 1 rows only) as WORD ';
set v_sql= v_sql || ' ,(select doc.REPORT_ID from spearbatch.tbl_document doc where d.DEAL_ID= doc.REQUEST_INSTANCE_DEAL_ID and doc.DOC_TYPE=''DOC'' order by doc.REPORT_ID fetch first 1 rows only) WORD_ID ';
set v_sql= v_sql || '  ,(select doc.DOC_NAME from spearbatch.tbl_document doc where d.DEAL_ID= doc.REQUEST_INSTANCE_DEAL_ID and doc.DOC_TYPE=''PDF'' order by doc.REPORT_ID fetch first 1 rows only) as PDF ';
set v_sql= v_sql || '  ,(select doc.REPORT_ID from spearbatch.tbl_document doc where d.DEAL_ID= doc.REQUEST_INSTANCE_DEAL_ID and doc.DOC_TYPE=''PDF'' order by doc.REPORT_ID fetch first 1 rows only) as PDF_ID  ';
set v_sql= v_sql || ' ,(select field_value_float from spearbatch.tbl_request_values_history a,spearbatch.tbl_field b  where a.instance_id =ri.instance_id and a.field_id =b.field_id and b.field_name=''MTM Price'' order by version desc  fetch first 1 rows only)  as MTM  from spearbatch.tbl_deal d ';
set v_sql= v_sql || ' join spearbatch.tbl_request_instance as ri  on ri.DEAL_ID=d.DEAL_ID and ri.product_id= ''' || @TEMPLATEID ||'''';

set i_indexNum=0;

open c7;
while   ii_count>0  do
    fetch c7 into v_field_id,v_field_type,v_featureid;
 if  v_featureid IS NULL  then
 set v_sql = v_sql || ' left join spearbatch.tbl_request_values  rv' ||cast(i_indexNum as char(2))||' on rv'||cast(i_indexNum as char(2)) || '.FIELD_ID=''' || v_field_id ||''' and rv' || cast(i_indexNum as char(2)) || '.INSTANCE_ID=ri.INSTANCE_ID';
 else
 end if;
 set i_indexNum=i_indexNum+1;
    set ii_count=ii_count-1;
end while;
close c7;
close c6;
if @STARTDATE IS NOT NULL then  
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvs on rvs.FIELD_ID=''' || @TRADEDATEFIELDID || ''' and rvs.INSTANCE_ID=ri.INSTANCE_ID and rvs.FIELD_VALUE_DATE>=date(''' || @STARTDATE || ''')';
end if;

if @ENDDATE IS NOT NULL then  
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rve on rve.FIELD_ID=''' || @TRADEDATEFIELDID || ''' and rve.INSTANCE_ID=ri.INSTANCE_ID and rve.FIELD_VALUE_DATE<=date(''' || @ENDDATE || ''')';
end if;

if @CYCLE ='NEW' then  
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvi on rvi.FIELD_ID=''' || v_issuedate_id || ''' and rvi.INSTANCE_ID=ri.INSTANCE_ID and rvi.FIELD_VALUE_DATE>=CURRENT DATE';
elseif @CYCLE ='EXPIRED' then 
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvv on rvv.FIELD_ID=''' || v_valuation_id || ''' and rvv.INSTANCE_ID=ri.INSTANCE_ID and rvv.FIELD_VALUE_DATE<CURRENT DATE';
elseif @CYCLE ='LIVE' then 
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvi on rvi.FIELD_ID=''' || v_issuedate_id || ''' and rvi.INSTANCE_ID=ri.INSTANCE_ID and rvi.FIELD_VALUE_DATE<CURRENT DATE';
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvv on rvv.FIELD_ID=''' || v_valuation_id || ''' and rvv.INSTANCE_ID=ri.INSTANCE_ID and rvv.FIELD_VALUE_DATE>=CURRENT DATE';
elseif @CYCLE ='KODATE' then 
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvv on rvv.FIELD_ID=''' || v_kodate_id || ''' and rvv.INSTANCE_ID=ri.INSTANCE_ID and rvv.FIELD_VALUE_DATE is NOT NULL';
end if;

if @ISINNUMBER IS NOT NULL then  
set v_sql = v_sql || ' join spearbatch.tbl_request_values  rvt on rvt.FIELD_ID=''' || @ISINFIELDID || ''' and rvt.INSTANCE_ID=ri.INSTANCE_ID and rvt.FIELD_VALUE_STRING=''' || @ISINNUMBER || '''';
end if;

set v_sql= v_sql || ' where 1=1 and ';

open c1;
fetch c1 into v_deal_id;
set v_deal_id_list= '''' || v_deal_id || '''';

while v_sql_code=0 do
fetch c1 into v_deal_id;
set v_deal_id_list=v_deal_id_list  || ',''' || v_deal_id || '''';
end while;
close c1;
set v_sql= v_sql || ' d.PARENT_NODE_ID IN (' || v_deal_id_list || ') ';
set v_sql=  v_sql || ' order by D.DEAL_ID';
--insert into spearbatch.test_proc(v_sql) values(v_sql);
  prepare v_stmt from v_sql;
open c2;
END
;
GRANT EXECUTE ON SPECIFIC PROCEDURE SPEARBATCH.GET_DEAL_LIST TO USER CREQSPRD WITH GRANT OPTION
;