create or replace function ft_getcontintrate( as_coopid char , as_contno varchar , adtm_operate date ) return number
is
ldc_intrate number;
begin
 if as_coopid is null or as_contno is null or adtm_operate is null then
  return 0.00;
 end if;

/*
int_continttype 1 -> fix ??????????????? int_contintrate 2 -> fix ????????????? int_continttabcode ?????????? lccfloanintratedet 3 -> fix ?????????????????? lncontintspc 4 -> ??????????????????
int_contintrate ??????????????????????????????
int_contintincrease ??????????????????????????????????????????
int_intsteptype ??????????????????? 1 ???????????????? 2 ????????????????(??????????????????????????????????????????????)
*/

select  ( case lm.int_continttype
 when 1 then nvl( lm.int_contintrate , 0 )
 when 2 then ft_getintrate( lm.coop_id, lm.int_continttabcode, case lm.int_intsteptype when 1 then lm.loanapprove_amt else lm.principal_balance end, adtm_operate )
 when 3 then ( select case lsp.int_continttype
       when 1 then nvl( lsp.int_contintrate, 0 )
       when 2 then ft_getintrate( lsp.coop_id, lsp.int_continttabcode, case lm.int_intsteptype when 1 then lm.loanapprove_amt else lm.principal_balance end, adtm_operate ) + nvl( lsp.int_contintincrease, 0 )
       else 0 end
       from lncontintspc lsp
       where lsp.coop_id = lm.coop_id
       and lsp.loancontract_no = lm.loancontract_no
       and adtm_operate between lsp.effective_date and lsp.expire_date )
 else 0 end ) as intrate
 into ldc_intrate
 from lncontmaster lm
 where lm.coop_id = as_coopid
 and lm.loancontract_no = as_contno;


 return ldc_intrate;
end ;