create procedure "cmsprd".getiflongtermBC(pSerialNo varchar(32))
returning varchar(4) ;
define iDays int;
define iBDDays int;
define pCycleFlag varchar(4);
define pFlag varchar(4);
let iDays = 0 ;
let iBDDays = 0 ;
select date(substr(maturity,6,5)||'/'||substr(maturity,1,4))-date(substr(putoutdate,6,5)||'/'||substr(putoutdate,1,4)) into iDays
from BUSINESS_CONTRACT where serialno = pSerialNo ;
select CycleFlag into pCycleFlag from BUSINESS_CONTRACT where serialno = pSerialNo ;
select max(date(substr(ActualMaturity,6,5)||'/'||substr(ActualMaturity,1,4))-date(substr(putoutdate,6,5)||'/'||substr(putoutdate,1,4))) into iBDDays
from BUSINESS_DUEBILL where relativeserialno2= pSerialNo ;
if iDays<=366 then
let pFlag = '2' ; -- 不满一年 直接给否
else
if pCycleFlag='2' then
let pFlag='1'; --是否循环为否 且合同期限为1年以上 则直接返回 是
else
if iBDDays>366 then
let pFlag='1' ;
else
let pFlag='2' ;
end if
end if
end if;
return pFlag;
end procedure ;