create or replace function fn_getbondinterest(p_i_fund_id number, --资产ID
p_vc_Stock_Code varchar2, --债券代码
p_i_Date number, --日期
p_i_flag number := 0, --是否计尾：0 不计算，1计算
p_en_year_rate number := -1 --浮息债的年票面利率
) return number is
/*****************************/
--功能：返回某个日期点的付息现金
--auther: lin 09.06.22
--edit :lin 09.07.30 解决 利息截止且没有兑付的情况下，返回0 的问题
--根据 计息方式（按平均值/实际天数）不同而配置不同的脚本
/*****************************/
----------------临时参数---------------------
pvar_i_Result number(22, 12) := 0; --临时结果
pvar_i_Date number := 0; --对应：利息截止日过了，但又没有兑付的债券，输入日期取利息截止日
pvar_vc_SQL varchar(500); --SQL
pvar_i_HasResult number := 0; --历史数据
pvar_vc_Script varchar2(200); --脚本计算公式
pvar_i_quoteway number := 0; --报价方法(0-全价；1-净价)
pvar_InsStartDate number := 0; --记息开始日
pvar_InsEndDate number := 0; --加工后记息截止日
pvar_InsEndDate_old number := 0; --原始记息截止日
pvar_payInsDays number := 0; --付息间隔天数
pvar_lastInsDays number := 0; --上一次付息的日期
pvar_I_CASH_DATE number := 0; --债券兑付日
pvar_i_Months number := 0; --记息开始日距离当前日期的月份
pvar_InsMaybe number := 0; --可能的
-- pvar_en_year_rate number := 0; --付息间隔（年）ok
-- pvar_interest_type number := 0; --计息方式（按平均值/实际天数）
----------------公式参数-------------------
pvar_i_c number := 0; --每百元面值年利息 ok
pvar_i_t integer := 0; --起息日或上一付息日至估值日的实际天数 ok
pvar_i_tt integer := 0; --起息日至到期兑付日的实际天数 ok
pvar_i_TS integer := 0; --本付息周期的实际天数 ok
pvar_i_TY number := 0; --本付息周期所在计息年度的实际天数 ok
pvar_i_M number := 0; --百元面值当前剩余本金值(资产支持证券)=取债券面值 ok
pvar_i_mm number := 0; --每百元面值到期兑付额（贴现债券）=取债券面值 ok
pvar_i_f number := 0; --一年内付息次数 ok
pvar_i_K number := 0; --债券起息日至估值日的整年数 ok
pvar_i_Pd number := 0; --债券发行价 ok
pvar_i_redeem integer := 0; --是否可赎回，0不可赎回 1可赎回
pvar_i_redemption integer := 0; -- 是否可回售，0不可回售 1可回售
pvar_i_redemptiondate number := 0; --回售日期
pvar_i_redeemdate number := 0; --赎回日期
pvar_vc_coupontype varchar2(10);
pvar_vc_interesttype varchar2(10);
pvar_i_trade_date integer; --系统交易日
begin
pvar_i_HasResult := 0;
Pvar_i_Date := p_i_Date; ---add:lin 20090730
select a.i_trade_date into pvar_i_trade_date from systeminfo a;
----------------------获取参数数值-----证券不关联帐户--------------------
if pvar_i_HasResult = 0 then
begin
select t.i_start_date, --记息开始日
t.i_end_date, --记息截止日
t.i_end_date,
t.i_payamount, --付息间隔（年）
t.i_quoteway, --报价方法(0-全价；1-净价)
t.en_year_rate, --票面利率
t.en_price, --发行价格
t.I_CASH_DATE, --兑付日
t.en_facevalue, --每百元面值到期兑付额（贴现债券）=面值
t.i_redeem,
t.i_redemption,
t.i_redeemdate,
t.i_redemptiondate,
t.vc_coupontype,
t.vc_interesttype
into pvar_InsStartDate,
pvar_InsEndDate,
pvar_InsEndDate_old,
pvar_i_f,
pvar_i_quoteway,
pvar_i_c,
pvar_i_Pd,
pvar_I_CASH_DATE,
pvar_i_mm,
pvar_i_redeem, --是否可赎回，0不可赎回 1可赎回
pvar_i_redemption, -- 是否可回售，0不可回售 1可回售
pvar_i_redeemdate, --赎回日期
pvar_i_redemptiondate, --回售日期
pvar_vc_coupontype,
pvar_vc_interesttype
from (select a.i_start_date, --记息开始日
a.i_end_date, --记息截止日
a.i_payamount, --付息间隔（年）
a.i_quoteway, --报价方法(0-全价；1-净价)
a.en_year_rate, --票面利率
a.en_price, --发行价格
a.I_CASH_DATE, --兑付日
a.en_facevalue, --每百元面值到期兑付额（贴现债券）=面值
a.i_redeem,
a.i_redemption,
a.i_redeemdate,
a.i_redemptiondate,
a.vc_coupontype,
a.vc_interesttype
from bondparams a
where (a.i_account_id = 0 or (p_i_fund_id = a.i_account_id))
and a.vc_stock_code = p_vc_Stock_Code
order by a.i_account_id desc) t
where rownum < 2; --只取首记录;
exception
when no_data_found then
return 0;
end;
end if;
--浮动利率
if pvar_vc_interesttype = '2' then
if p_en_year_rate <> -1 then
pvar_i_c := p_en_year_rate;
else
--从浮动利率表取票面利率
begin
select en_year_rate
into pvar_i_c
from (select a.en_year_rate
from bondparams_floatrate a
where (a.i_account_id = 0 or (p_i_fund_id = a.i_account_id))
and a.vc_stock_code = p_vc_Stock_Code
and a.i_change_date <= p_i_Date
order by a.i_change_date desc)
where rownum = 1;
exception
when others then
pvar_i_c := 0;
end;
end if;
end if;
------如果输入的日期小于记息开始日，或者大于兑付日，返回0-----------------
if (Pvar_i_Date < pvar_InsStartDate) or (Pvar_i_Date > pvar_I_CASH_DATE) then
return 0;
end if;
------------------0：表示零息券----------------------
if pvar_i_f = 0 then
pvar_i_f := 1; ---xp 默认0息券的付息间隔为 1
end if;
if pvar_vc_coupontype = '2' then
--息票品种 为 附息券的
begin
----------------------------------------------------------
/*xp 20091128 增加赎回、回售日期 含权的判断，使得与中债数据对上
pvar_i_redeem, --是否可赎回，0不可赎回 1可赎回
pvar_i_redemption, -- 是否可回售，0不可回售 1可回售
pvar_i_redeemdate, --赎回日期
pvar_i_redemptiondate--回售日期
*/
if (pvar_i_redeem = 1) then
begin
if (pvar_i_redeemdate < pvar_InsEndDate) and
(pvar_i_redeemdate > Pvar_i_Date) then
pvar_InsEndDate := pvar_i_redeemdate;
end if;
if (pvar_i_redeemdate < pvar_InsEndDate) and
(pvar_i_redeemdate < Pvar_i_Date) then
pvar_InsStartDate := pvar_i_redeemdate;
end if;
end;
end if;
if (pvar_i_redemption = 1) then
begin
if (pvar_i_redeemdate < pvar_InsEndDate) and
(pvar_i_redemptiondate > Pvar_i_Date) then
pvar_InsEndDate := pvar_i_redemptiondate;
end if;
if (pvar_i_redeemdate < pvar_InsEndDate) and
(pvar_i_redemptiondate < Pvar_i_Date) then
pvar_InsStartDate := pvar_i_redemptiondate;
end if;
end;
end if;
----------------------------------------------------------
----------------对于计息已经停止，且没有兑付的债券，日期取利息截止日期---add:2009.07.30---------------------------
if (Pvar_i_Date >= pvar_InsEndDate) and
(Pvar_i_Date <= pvar_I_CASH_DATE) then
Pvar_i_Date := pvar_InsEndDate;
end if;
-------通过计算得到的参数--------
pvar_payInsDays := 12 / pvar_i_f; --付息间隔月数
if pvar_payInsDays = 0 then
pvar_i_Result := 0;
return pvar_i_Result;
end if;
--获取已经付息的次数
/*
如果计息开始日 与 付息日 的 日期号(dd)对不上。则开始计息日的月份+1
*/
if (to_char(to_date(pvar_InsStartDate, 'yyyymmdd'), 'mm') <>
to_char(to_date(pvar_InsEndDate, 'yyyymmdd'), 'mm')) then
pvar_InsStartDate := to_number(to_char(add_months(to_date(pvar_InsStartDate,
'yyyymmdd'),
1),
'yyyymm') ||
to_char(to_date(pvar_InsEndDate,
'yyyymmdd'),
'dd'));
end if;
select floor(months_between(to_date(pvar_i_Date, 'yyyymmdd'),
to_date(pvar_InsStartDate, 'yyyymmdd')) /
pvar_payInsDays)
into pvar_i_Months
FROM DUAL;
/*
--xp 20091128 解决起息日 与 付息截止日，日期不一样，导致利息计算出错
if ( to_char(to_date(pvar_InsStartDate, 'yyyymmdd'),'mm') <> to_char(to_date(pvar_InsEndDate, 'yyyymmdd'),'mm') ) then
if (pvar_InsEndDate = pvar_InsEndDate_old) then
pvar_i_Months := pvar_i_Months-1;
end if;
end if;
*/
------------------------------------------------------------------
--xp 20091128 解决日期为2月28，加入月份后，日期非28，为31的情况，导致计算结果存在误差。
--pvar_lastInsDays := to_number(to_char(add_months(to_date(pvar_InsStartDate, 'yyyymmdd'),(pvar_payInsDays *pvar_i_Months)),'yyyymm')
-- || to_char(to_date(pvar_InsEndDate,'yyyymmdd'),'dd')); --上一次付息的日期
------------------------------------------------------------------
--暂时先不按照xp上面方法，上面构造出的日期会有问题
pvar_InsMaybe := to_number(to_char(add_months(to_date(pvar_InsStartDate,
'yyyymmdd'),
(pvar_payInsDays *
pvar_i_Months)),
'yyyymmdd'));
if to_number(to_char(to_date(pvar_InsMaybe, 'yyyymmdd'), 'dd')) <>
to_number(to_char(to_date(pvar_InsStartDate, 'yyyymmdd'), 'dd')) then
begin
if to_number(to_char(to_date(pvar_InsMaybe, 'yyyymmdd'), 'dd')) >
to_number(to_char(to_date(pvar_InsStartDate, 'yyyymmdd'), 'dd')) then
pvar_i_quoteway := to_number(to_char(to_date(pvar_InsStartDate,
'yyyymmdd'),
'dd'));
else
pvar_i_quoteway := to_number(to_char(to_date(pvar_InsMaybe,
'yyyymmdd'),
'dd'));
end if;
pvar_InsMaybe := to_number(to_char(add_months(to_date(pvar_InsStartDate,
'yyyymmdd'),
(pvar_payInsDays *
pvar_i_Months)),
'yyyymm') ||
to_char(pvar_i_quoteway));
end;
end if;
pvar_lastInsDays := pvar_InsMaybe;
--xp 20091128 本次付息日=传入交易日，那么当日不计息
if to_number(to_char(add_months(to_date(pvar_lastInsDays, 'yyyymmdd'),
pvar_payInsDays),
'yyyymmdd')) = pvar_i_Date then
return 0;
end if;
pvar_i_t := to_date(pvar_i_Date, 'yyyymmdd') -
to_date(pvar_lastInsDays, 'yyyymmdd') + p_i_flag; --起息日或上一付息日至估值日的实际天数
/*
if pvar_InsEndDate <> to_number(to_char(add_months(to_date(pvar_lastInsDays, 'yyyymmdd'),pvar_payInsDays),'yyyymmdd')) then
pvar_i_TS := to_date(pvar_InsEndDate, 'yyyymmdd')
- to_date(pvar_lastInsDays, 'yyyymmdd'); ---本付息周期的实际天数
else
pvar_i_TS := add_months(to_date(pvar_lastInsDays, 'yyyymmdd'),pvar_payInsDays)
- to_date(pvar_lastInsDays, 'yyyymmdd'); ---本付息周期的实际天数
end if;
*/
pvar_InsMaybe := to_number(to_char(add_months(to_date(pvar_lastInsDays,
'yyyymmdd'),
pvar_payInsDays),
'yyyymmdd'));
if to_number(to_char(to_date(pvar_InsMaybe, 'yyyymmdd'), 'dd')) <>
to_number(to_char(to_date(pvar_InsStartDate, 'yyyymmdd'), 'dd')) then
begin
if to_number(to_char(to_date(pvar_InsMaybe, 'yyyymmdd'), 'dd')) >
to_number(to_char(to_date(pvar_InsStartDate, 'yyyymmdd'), 'dd')) then
pvar_i_quoteway := to_number(to_char(to_date(pvar_InsStartDate,
'yyyymmdd'),
'dd'));
else
pvar_i_quoteway := to_number(to_char(to_date(pvar_InsMaybe,
'yyyymmdd'),
'dd'));
end if;
pvar_InsMaybe := to_number(to_char(add_months(to_date(pvar_lastInsDays,
'yyyymmdd'),
pvar_payInsDays),
'yyyymm') ||
to_char(pvar_i_quoteway));
end;
end if;
if pvar_i_f=2 then
--本付息周期的实际天数，根据兑息日在上半年还是下半年来计算
if pvar_lastInsDays<=substring(to_char(pvar_lastInsDays), 1, 4) || '0630' then
pvar_i_TS := to_date(substring(to_char(pvar_lastInsDays), 1, 4) || '0630','yyyymmdd')
-to_date(substring(to_char(pvar_lastInsDays), 1, 4) || '0101','yyyymmdd')+1;
else
pvar_i_TS := to_date(substring(to_char(pvar_lastInsDays), 1, 4) || '1231','yyyymmdd')
-to_date(substring(to_char(pvar_lastInsDays), 1, 4) || '0701','yyyymmdd')+1; ---本付息周期的实际天数
end if;
else
pvar_i_TS := to_date(pvar_InsMaybe, 'yyyymmdd') -
to_date(pvar_lastInsDays, 'yyyymmdd'); ---本付息周期的实际天数
end if;
end;
else
begin
pvar_i_t := to_date(pvar_i_Date, 'yyyymmdd') -
to_date(pvar_InsStartDate, 'yyyymmdd') + p_i_flag; --起息日至估值日的实际天数
end;
end if;
------------------------------------------------------------------
pvar_i_tt := to_date(pvar_InsEndDate, 'yyyymmdd') -
to_date(pvar_InsStartDate, 'yyyymmdd'); --+ 1; --起息日至到期兑付日的实际天数
select add_months(trunc(sysdate, 'year'), 12) - trunc(sysdate, 'year')
into pvar_i_TY
from dual; ----本付息周期所在计息年度的实际天数
-- pvar_i_f := 1 / pvar_en_year_rate; --一年内付息次数
pvar_i_K := floor((to_date(pvar_i_Date, 'yyyymmdd') -
to_date(pvar_InsStartDate, 'yyyymmdd')) / 365); --债券起息日至估值日的整年数
pvar_i_M := pvar_i_mm;
--------------获取脚本计算公式------------
begin
select VC_FORMULA
into pvar_vc_Script
from (select nvl(b.VC_FORMULA, ' ') as VC_FORMULA
from BONDPARAMS a
left join CALCINSMETIC b on a.Vc_Formulacode = b.vc_cim_code
where (a.i_account_id = 0 or (p_i_fund_id = a.i_account_id))
and a.vc_stock_code = p_vc_Stock_Code
order by a.i_account_id desc) t
where rownum < 2; --只取首记录;
exception
when no_data_found then
return pvar_i_Result;
end;
if pvar_vc_Script = ' ' then
return 0;
end if;
------------------参数传入公式计算-------------------------
pvar_vc_Script := upper(pvar_vc_Script);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_c'), pvar_i_c);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_TS'), pvar_i_TS);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_tt'), pvar_i_tt);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_TY'), pvar_i_TY);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_t'), pvar_i_t);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_mm'), pvar_i_mm);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_M'), pvar_i_M);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_f'), pvar_i_f);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_K'), pvar_i_K);
pvar_vc_Script := replace(pvar_vc_Script, upper('pvar_i_Pd'), pvar_i_Pd);
pvar_vc_SQL := replace('select pvar_vc_Script from dual',
'pvar_vc_Script',
pvar_vc_Script);
begin
execute immediate pvar_vc_SQL
into pvar_i_Result;
exception
when others then
pvar_i_Result := 0;
end;
return pvar_i_Result;
end;
扫一扫
3821
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
