111企业年金基金监督管理系统系统（二期）维护指南(4).docx

《111企业年金基金监督管理系统系统（二期）维护指南(4).docx》由会员分享，可在线阅读，更多相关《111企业年金基金监督管理系统系统（二期）维护指南(4).docx（77页珍藏版）》请在第一文库网上搜索。

1、中国人寿养老保险股份有限公司China Life Pension Company Limited企业年金基金监督管理系统系统维护指南修订页编号章节名称修订内容简述修订日期修订前版本号修订后版本号修订人核定人1所有章节方案整理2013-0541V1.0张战胜、兰伯、张东辉第一章系统日常检查、每日巡检1.1检查基金监督系统数据库备份情况10.3.229.14:d2期数据库备份1.2估值表挪动情况查看-东辉1、首先要缺表托管行都挪动2、其次查看“错误”、“警告”、“提示”三种提示。其中“错误”一般是文件错误，“警告” 一般是配置错误。select * from tgzb_log where l_ty

2、pe=提示and t-date-z2013-01-0;.（这个里面日志分三种:警告，提示，错误）13登录基金监督系统，处理待运维业务用admin02登录，产看任务池1.4 检查已挪未导的估值表（执行时修改一下日期）-东辉这里可以直接看tgzb log的错误日志了，巡检有问题就看这个日志就行。his.path字段记录了未挪成功的路径。select obj planName, obj. prtName, obj. custName, a. * from v_obj_mapping obj,(SELECT d. *FROM (SELECTFROMa. *(SELECTo code,TO CHARTO

3、 CHAR(TO DATE (beg_date, yyyy-mrdd),，yyyy-un-dd,)beg_date,(TO_DATE (end date, , yyyy-mm-dd,),，yyyy-mm-dd,)end_dateFROM(SELECTFROMtgzb_result where beg_date = 201304-101)a,a *(SELECTFROMa.port code, a. beg date AS gzlostdate(SELECT tp.port code, beg dateFROM (SELECTFROMWHEREANDbeg_datev trcide_daysxr

4、i skbeg_date = 2013-04-10,beg_date = 2013-04-101AND begdate = tp.p_date) aLEFT JOIN(SELECT port_code, beg_dateFROM (SELECT port code, beg dateFROM tcrp_hldxriskWHERE beg_date = 2013-04-10,AND beg_date = 2013-04-10,AND beg date = 2013-04-10-and a. beg date = 92011-12-3ND ( SUBSTR (obj. prtcode, 2, 2)

5、 07OR obj. phncode = , T120l2010000000000005,-)AND b. beg,date IS NULL ) a,tprt bWHERE a. port code = b. o codeAND b. p_flag 2AND gzlostdate =DECODE (b. trans_date,NLLL, b. beg date,b.transdate)bWHERE a o_code = b. port_codeAND b. gzlostdate = a beg_date) dGROUP BY o code, beg date, end date) awhere

6、 obj. prtCode=a. o code1.5 检查组合现金流校验-东辉1、“新增计划组合任务”和“现金流校验”是同时执行的。可以通过TCASH_LOG来查看。select * from tcash_log tl order by tl. savedate desc1.6 检查未维护的现金流-东辉使用下面脚本，可以通过后台查看是否有未维护的现金流：select obj. planName, obj. custName, obj. prtname, a. * from(select *from (select a *,rank() over(partition by port code o

7、rder by date asc) as seqidfrom (select a *, b. id1、从tcrp_size表中查份额变化数不为0的from (select *from (select port_code,p date,p_size -nvl (lag(p_size, 1)over (partition by port-codeorder by p_date),0) as ch sizefrom tcrp sizexriskwhere (port_code like S% orport_code in(select ocodefrom tprtwhere ISSTPORT = 1

8、)-and end_date2010-01 -01 -add by xx 20110311)awhere a ch_size 0) a一2、左关联tcrp sgsh tzjg(现金流表里的数据)，条件组合代码一样,R期一left join tcr sgsh tzjgxrisk bon a. port_code = b. port_codecind a. p date = b. p date)a一3、查看是否有tcrp_size表中有数据,而tci、p_sgsh_tzjg没有数据,认为是现金流丢失where a. id is null)where seqid 1)aleft join v_obj

9、_mapping objon a. port code=obj. rtcodewhere a. p-date 2012-08-01order by obj. custCode, obj. prtCode, a. p date1.7 更新委托余额begin*删除所有非手工输入的委托余额数据*delete from tcrp_trust_acctinfoxrisk where pipe_id T;*重新生成所有委托余额数据*insert into tcrp trust acctinfoxrisk(beg_date,end_date,port_code,trust_amount,currency,i

10、mpdate,pipe_id)select a. p_date as beg_date,lead(a. date, 1, 2050-12一31) over(partition by a. port code order by a. p date asc)as end date,a. port_code,nvl (b. trust_amount, 0) +sum(nvl(a. sg amount, 0) - nvl (a.sh amount, 0) over(partition by a.port_code order bya. p date asc) as t_amount,CNY as cu

11、rrency,to_char (sysdate, yyyy-nm-dd) as imp_date,999 as pipe_idfrom tcrp_sgsh_tzjgxri sk aleft join (select trust amount, beg date, port codefrom tcrp_trus t_acc t i nfoxri skwhere pipe_id = -1) b on a. port_code = b. port_code;*截断所有手工输入的委托余额数据*update tcrp_trust_acctinfoxrisk aset a. end date = (sel

12、ect nvl(min(b. beg date), 2050-12-3)from tcrp trust acctinfoxrisk bwhere a. port_code = b. port_codeand b.pipe_id -1)where a. pipe id = 一1;commit;end;1.8 更新风控现金流日期begindelete from timp_chk_instancexrisk where rulecode = PRT CASH;insert into timp_chk_instancexrisk(rule_code, id name, port_code, maxdate, basedate)select P