1/9用友股份-LE支持服务业务本部产品方案--《应收管理-计算期初和期末的算法》建立日期：2014-01-12修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-CP-2014-00332/9文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-01-13张晨产品支持部V1.0版本领域模块备注3/9目录应收管理-计算期初和期末的算法..........................................................................44/9应收管理-计算期初和期末的算法注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！在应收管理中，计算期初和期末分为四部分。由四个SQL语句组成，查询区间定为：2009-03-01到2009-03-02，如下：SQL1:selectFB.KSBM_CLFB_KSBM_CL,0.0jfshlje,0.0jfybje,0.0jffbje,0.0jfbbje,0.0dfshlje,0.0dfybje,0.0dffbje,0.0dfbbje,sum(fb.fx*fb.shlye)qcshlye,sum(fb.fx*fb.ybye)qcybye,sum(fb.fx*fb.fbye)qcfbye,sum(fb.fx*fb.bbye)qcbbyefromarap_djzbzbinnerjoinarap_djfbfbonzb.vouchid=fb.vouchidwhere(zb.dwbm='1001')and(fb.dwbm='1001')andzb.djzt>=-10000andzb.hzbzisnullandfb.wldx=0andfb.VERIFYFINISHEDDATE>='2009-03-01'and(fb.billdate<'2009-03-01')andfb.xgbh<>1and(fb.billdate<'2009-03-01')and(zb.djdl='ys'orzb.djdl='sk')and(fb.ksbm_clisnotnullandfb.ksbm_clin('0001V510000000003VYQ'))and(fb.dr=0andzb.dr=0)groupbyFB.KSBM_CL5/9此SQL计算单据辅表中在一定条件下制单日期在2009-03-01之前、核销日期在2009-03-01之后的应收单和收款单的余额之和。此计算结果作为2009-03-01的期初一部分。SQL2:selectFB.KSBM_CLFB_KSBM_CL,0.0jfshlje,0.0jfybje,0.0jffbje,0.0jfbbje,0.0dfshlje,0.0dfybje,0.0dffbje,0.0dfbbje,sum(clb.jfclshl-clb.dfclshl)qcshlye,sum(clb.jfclybje-clb.dfclybje)qcybye,sum(clb.jfclfbje-clb.dfclfbje)qcfbye,sum(clb.jfclbbje-clb.dfclbbje)qcbbyefromarap_djzbzbinnerjoinarap_djfbfbonzb.vouchid=fb.vouchidinnerjoinarap_djclbclbonfb.fb_oid=clb.fb_oidandclb.dr=fb.drwhere(zb.dwbm='1001')and(fb.dwbm='1001')and(clb.dwbm='1001')andzb.djzt>=-10000andzb.hzbzisnullandfb.wldx=0andfb.VERIFYFINISHEDDATE>='2009-03-01'and(fb.billdate<'2009-03-01')andclb.clrq>='2009-03-01'andclb.clbz<>6andfb.xgbh<>1and(fb.billdate<'2009-03-01')and(zb.djdl='ys'orzb.djdl='sk')and(fb.ksbm_clisnotnullandfb.ksbm_clin('0001V510000000003VYQ'))and(fb.dr=0andzb.dr=0andclb.dr=0)groupbyFB.KSBM_CL此SQL计算的是单据处理表中在一定条件下制单日期在2009-03-01之前、处理日期在2009-03-01之后的单据处理余额。此部分结果作为期初的一6/9部分，用来补回在SQL1中计算期初时忽略的单据处理金额，如核销、红蓝对冲等处理金额。SQL3:selectFB.KSBM_CLFB_KSBM_CL,sum(fb.jfshl)jfshlje,sum(fb.jfybje)jfybje,sum(fb.jffbje)jffbje,sum(fb.jfbbje)jfbbje,sum(jfybsj)jfybsj,sum(jffbsj)jffbsj,sum(jfbbsj)jfbbsj,sum(jfybwsje)jfybwsje,sum(wbfybje)jffbwsje,sum(wbfbbje)jfbbwsje,sum(dfshl)dfshlje,sum(fb.dfybje)dfybje,sum(dffbje)dffbje,sum(fb.dfbbje)dfbbje,sum(dfybsj)dfybsj,sum(dffbsj)dffbsj,sum(dfbbsj)dfbbsj,sum(dfybwsje)dfybwsje,sum(wbffbje)dffbwsje,sum(dfbbwsje)dfbbwsje,0.0qmshlye,0.0qmybye,0.0qmfbye,0.0qmbbyefromarap_djzbzbinnerjoinarap_djfbfbonzb.vouchid=fb.vouchidwhere(zb.dwbm='1001')and(fb.dwbm='1001')andzb.djzt>=-10000andzb.hzbzisnullandfb.wldx=0andfb.VERIFYFINISHEDDATE>='2009-03-01'and(fb.billdate>='2009-03-01'andfb.billdate<='2009-03-02')andfb.xgbh=-1and(zb.djdl='ys'orzb.djdl='sk')and(fb.ksbm_clisnotnullandfb.ksbm_clin('0001V510000000003VYQ'))7/9and(fb.dr=0andzb.dr=0)groupbyFB.KSBM_CL此SQL计算单据辅表中在一定条件下制单日期和核销日期都在2009-03-01与2009-03-02之间的应收单据和收款单据金额。此部分作为计算在查询日期区间2009-03-01到2009-03-02之间的本期应收单据总金额和本期收款单据总金额，用于计算期末的一部分。SQL4:selectFB.KSBM_CLFB_KSBM_CL,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.dfclshl2)elsesum(clb.jfclshl2)endjfshlje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.dfclybje2)elsesum(clb.jfclybje2)endjfybje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.dfclfbje2)elsesum(clb.jfclfbje2)endjffbje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.dfclbbje2)elsesum(clb.jfclbbje2)endjfbbje,0.0jfybsj,0.0jffbsj,0.0jfbbsj,0.0jfybwsje,0.0jffbwsje,0.0jfbbwsje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'then8/9sum(-clb.jfclshl2)elsesum(clb.dfclshl2)enddfshlje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.jfclybje2)elsesum(clb.dfclybje2)enddfybje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.jfclfbje2)elsesum(clb.dfclfbje2)enddffbje,casewhenclb.clbzin(-1,0)andclb.djdl='sk'thensum(-clb.jfclbbje2)elsesum(clb.dfclbbje2)enddfbbje,0.0dfybsj,0.0dffbsj,0.0dfbbsj,0.0dfybwsje,0.0dffbwsje,0.0dfbbwsje,0.0qmshlye,0.0qmybye,0.0qmfbye,0.0qmbbyefromarap_djclbclbinnerjoinarap_djfbfbonclb.fb_oid=fb.fb_oidinnerjoinarap_djzbzbonclb.vouchid=zb.vouchidleftouterjoinarap_djfbFB1onclb.dydjfbid=FB1.fb_oidwhere(zb.dwbm='1001')and(fb.dwbm='1001')and(clb.dwbm='1001')andzb.djzt>=-10000andzb.hzbzisnullandfb.wldx=0and(clb.clrq>='2009-03-01'andclb.clrq<=9/9'2009-03-02')andclb.djdlin('ys','sk')and(((fb.wldx<>fb1.wldxorFB.KSBM_CL<>FB1.KSBM_CLorFB1.KSBM_CLisnull)and(clb.samewldx='N'orclb.samehbbm='N')andclb.clbzin(0,10,2)orclb.clbzin(-1,-2,1,3,4,5,6)))and(fb.ksbm_clisnotnullandfb.ksbm_clin('0001V510000000003VYQ'))and(clb.dr=0andfb.dr=0andzb.dr=0andFB1.dr=0)groupbyFB.KSBM_CL,clb.clbz,clb.djdl此SQL计算单据处理表中在一定条件下单据处理日期在2009-03-01与2009-03-02之间单据的借方总金额和贷方总金额。计算这部分时涉及到的处理单据分为两类：一是：不同往来对象之间的同币种核销、红蓝对冲以及调差单价的处理单据；二是：核销折扣、异币种核销、汇兑损益、坏账发生、坏账收回、并帐转出以及并帐转入的处理单据；这部分结果代表在计算期初的SQL2涉及到的一些处理单据的处理日期在查询日期区间中，因此在计算期末时这部分处理单据金额需要减去。期初和期末的计算公式为：期初=部分一+部分二；期末=期初+部分三应收单-部分三收款单-部分四（借-贷）；
