欢迎支持

DROP SPECIFIC PROCEDURE SPEARBATCH.GET_DEAL_LIST
;
CREATE PROCEDURE SPEARBATCH.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
;

顶啊顶啊顶