5.02版薪资定调资数据同步到人员信息子集实现说明云南分公司NC实施部刘鹏ynlp@ufida.com.cn解决问题：1、定调资数据不能在卡片报表中引用，不能生成调资审批表；2、跨公司调配后，目标公司看不到历史定调资数据；实现思路：在定调资数据表hi_psndoc_wadoc上定义一个触发器，该表数据变化时，将数据同步到一个自定义信息子集来保存定调资数据，实现实时更新。要点：1、使用前需要将hi_psndoc_wadoc表中的数据全部复制到信息子集中；2、建议在单据模板中把该子集的信息项定义为只读；实现步骤：1、自定义一个信息子集，如下信息项如下：其中，金额和试用期工资，数据类型小数，长度20，小数位2位，其余均为字符型。将定义好的信息项添加到模板。2、在单据模板设置中，找到该信息集对应的数据库表名：找到每个信息项的字段名：记录下每个信息项对应的字段名，例如：数据库表名信息项对应的字段名groupdef1,是否谈判工资groupdef2,是否来自流程（即走审批）pk_psndoc_sub,系统自带，人员子表主键（实际是pk_psndoc）pk_psndoc,系统自带，人员主键recordnum,系统自带，记录序号begindate,系统自带，起始时间enddate,系统自带，结束时间groupdef3,薪资年度（无用）groupdef4,薪资月度（无用）groupdef5,业务类型lastflag,系统自带，最新标志groupdef6,薪资项目groupdef7,薪资级别groupdef8,薪资档别groupdef9,金额groupdef10,薪资等级类别groupdef11,发放标志groupdef12,薪资调整日期groupdef13,变动原因groupdef14,试用期工资groupdef15,依据文件groupdef16,备注pk_psnbasdoc,系统自带，人员基本档案主键3、将已有的定调资数据复制到自定义的子集中（初始化数据）我是通过pl/sqldeveloper把数据粘贴进去的，也可通过写脚本直接复制数据。我的方法是，首先把定调资数据表hi_psndoc_wadoc和自定义子集数据表的字段进行对应（以hi_psndoc_wadoc为准），将hi_psndoc_wadoc的数据导出到excel，脚本如下：--翻译数据selectcasehi_psndoc_wadoc.negotiation_wagewhen1then'是'when0then'否'else'否'endastpgz,casehi_psndoc_wadoc.workflowflagwhen1then'是'when0then'否'else'否'endaslc,hi_psndoc_wadoc.pk_psndoc_subaspk_psndoc_sub,hi_psndoc_wadoc.pk_psndocaspk_psndoc,hi_psndoc_wadoc.recordnumasrecordnum,hi_psndoc_wadoc.begindateasbegindate,hi_psndoc_wadoc.enddateasenddate,hi_psndoc_wadoc.cyearascyear,hi_psndoc_wadoc.cperiodascperiod,casehi_psndoc_wadoc.iadjustmatterwhen0then'试用'when1then'定级'when2then'调级'when3then'调配'else'无'endasywlx,hi_psndoc_wadoc.lastflagaszxbz,wa_item.vnameasvname,wa_prmlv.levelnameaslevelname,wa_seclv.levelnameaslevelnameD,round(hi_psndoc_wadoc.nmoney,2)asnmoney,wa_grade.wagradenameaswagradename,hi_psndoc_wadoc.waflagasffba,hi_psndoc_wadoc.changedateaschangedate,hi_psndoc_wadoc.pk_changecauseaspk_changecause,round(hi_psndoc_wadoc.nprobationwage,2)asnprobationwage,hi_psndoc_wadoc.vbasefileasvbasefile,hi_psndoc_wadoc.vnoteasvnotefromhi_psndoc_wadocleftouterjoinwa_itemonhi_psndoc_wadoc.pk_wa_item=wa_item.pk_wa_itemleftouterjoinwa_seclvonhi_psndoc_wadoc.pk_wa_seclv=wa_seclv.pk_wa_seclvleftouterjoinwa_prmlvonhi_psndoc_wadoc.pk_wa_pralv=wa_prmlv.pk_wa_prmlvleftouterjoinwa_gradeonhi_psndoc_wadoc.pk_wa_grd=wa_grade.pk_wa_grd再把数据粘贴到自定义子集中（以自定义子集对应数据库物理表为hi_psndoc_grpdef16为例），通过下面脚本查询数据后粘贴：selectgroupdef1,groupdef2,pk_psndoc_sub,pk_psndoc,recordnum,begindate,enddate,groupdef3,groupdef4,groupdef5,lastflag,groupdef6,groupdef7,groupdef8,groupdef9,groupdef10,groupdef11,groupdef12,groupdef13,groupdef14,groupdef15,groupdef16,pk_psnbasdocfromhi_psndoc_grpdef16forupdate,注意，脚本里面的列顺序要和导出的excel表列顺序一致。4、更新人员基本档案主键定调资数据表中没有pk_psnbasdoc，所以要手工更新一次，脚本如下：update(selectp.pk_psnbasdocpp,h.pk_psnbasdochpfrombd_psndocp,hi_psndoc_grpdef16hwherep.pk_psndoc=h.pk_psndoc)sethp=pp5、执行创建触发器脚本：createorreplacetriggertrigger_wadoc_copyafterinsertorupdateordeleteonhi_psndoc_wadocforeachrow/*在定调资数据表hi_psndoc_wadoc上创建一个名为trigger_wadoc_copy的触发器，该触发器在表hi_psndoc_wadoc发生插入、修改、删除操作后开始执行*/beginifinsertingthen/*当hi_psndoc_wadoc有插入操作时，将插入后的数据进行处理（把主键翻译为文本），同时插入定义的子集表。:new表示增删改后的新数据*/insertintohi_psndoc_grpdef16(groupdef1,groupdef2,pk_psndoc_sub,pk_psndoc,recordnum,begindate,enddate,groupdef3,groupdef4,groupdef5,lastflag,groupdef6,groupdef7,groupdef8,groupdef9,groupdef10,groupdef11,groupdef12,groupdef13,groupdef14,groupdef15,groupdef16)values(case:new.negotiation_wagewhen1then'是'when0then'否'else'否'end,case:new.workflowflagwhen1then'是'when0then'否'else'否'end,:new.pk_psndoc_sub,:new.pk_psndoc,:new.recordnum,:new.begindate,:new.enddate,:new.cyear,:new.cperiod,case:new.iadjustmatterwhen0then'试用'when1then'定级'when2then'调级'when3then'调配'else'无'end,:new.lastflag,case:new.pk_wa_itemwhen'000152100000000016WS'then'岗位工资'else'无'end,case:new.pk_wa_seclvwhen'000152100000000017EA'then'一级'when'000152100000000017EB'then'二级'when'000152100000000019ML'then'三级'when'000152100000000019MZ'then'四级'when'000152100000000019ND'then'五级'when'000152100000000019NR'then'六级'else'无'end,case:new.pk_wa_pralvwhen'000152100000000017U6'then'一岗'when'000152100000000018A2'then'二岗'when'000152100000000018A5'then'二岗副'when'000152100000000018A8'then'三岗'when'000152100000000018AB'then'四岗'when'000152100000000019LX'then'五岗'when'000152100000000019M0'then'六岗'when'000152100000000019M3'then'七岗'when'000152100000000019M6'then'八岗'when'000152100000000019M9'then'九岗'when'000152100000000019MC'then'十岗'when'000152100000000019MF'then'十一岗'when'000152100000000019MI'then'十二岗'else'无'end,round(:new.nmoney,2),case:new.pk_wa_grdwhen'000152100000000017E9'then'岗位工资标准'else'无'end,:new.waflag,:new.changedate,:new.pk_changecause,round(:new.nprobationwage,2),:new.vbasefile,:new.vnote);elsifupdatingthen/*当hi_psndoc_wadoc有更新（修改）操作时，将修改后的数据进行处理（把主键翻译为文本），同时修改定义的子集表数据。:new表示增删改后的新数据*/updatehi_psndoc_grpdef16setgroupdef1=case:new.negotiation_wagewhen1then'是'when0then'否'else'否'end,groupdef2=case:new.workflowflagwhen1then'是'when0then'否'else'否'end,pk_psndoc_sub=:new.pk_psndoc_sub,pk_psndoc=:new.pk_psndoc,recordnum=:new.recordnum,begindate=:new.begindate,enddate=:new.enddate,groupdef3=:new.cyear,groupdef4=:new.cperiod,groupdef5=case:new.iadjustmatterwhen0then'试用'when1then'定级'when2then'调级'when3then'调配'else'无'end,lastflag=:new.lastflag,groupdef6=case:new.pk_wa_itemwhen'000152100000000016WS'then'岗位工资'else'无'end,groupdef7=case:new.pk_wa_seclvwhen'000152100000000017EA'then'一级'when'000152100000000017EB'then'二级'when'000152100000000019ML'then'三级'when'000152100000000019MZ'then'四级'when'000152100000000019ND'then'五级'when'000152100000000019NR'then'六级'else'无'end,groupdef8=case:new.pk_wa_pralvwhen'000152100000000017U6'then'一岗'when'000152100000000018A2'then'二岗'when'000152100000000018A5'then'二岗副'when'000152100000000018A8'then'三岗'when'000152100000000018AB'then'四岗'when'000152100000000019LX'then'五岗'when'000152100000000019M0'then'六岗'when'000152100000000019M3'then'七岗'when'000152100000000019M6'then'八岗'when'000152100000000019M9'then'九岗'when'000152100000000019MC'then'十岗'when'000152100000000019MF'then'十一岗'when'000152100000000019MI'then'十二岗'else'无'end,groupdef9=round(:new.nmoney,2),groupdef10=case:new.pk_wa_grdwhen'000152100000000017E9'then'岗位工资标准'else'无'end,groupdef11=case:new.waflagwhen'Y'then'是'when'N'then'否'else'否'end,groupdef12=:new.changedate,groupdef13=:new.pk_changecause,groupdef14=round(:new.nprobationwage,2),groupdef15=:new.vbasefile,groupdef16=:new.vnotewherepk_psndoc=:old.pk_psndocandrecordnum=:old.recordnum;elsifdeletingthen/*当hi_psndoc_wadoc有删除操作时，同时删除定义的子集表数据。:new表示增删改后的新数据，:old表示操作前的数据*/deletehi_psndoc_grpdef16wherepk_psndoc=:old.pk_psndocandrecordnum=:old.recordnum;endif;update(selectp.pk_psnbasdocpp,h.pk_psnbasdochpfrombd_psndocp,hi_psndoc_grpdef16hwherep.pk_psndoc=h.pk_psndoc)sethp=pp;/*更新人员基本档案主键，若无此主键，子集数据不能显示*/end;6、应用以上脚本时要按照实际的数据环境做相应修改。7、效果
