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
;