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;
