1/13用友股份-LE支持服务业务本部产品方案--《三个应收算法后台语句整理》建立日期：2013-07-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-CP-2013-04142/13文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-07-01刘杰冉产品支持部V1.0版本领域模块备注3/13目录三个应收算法后台语句整理................................................................................4一、应用场景：...................................................................................................4二、语句汇总：...................................................................................................44/13三个应收算法后台语句整理一、应用场景：信用的三个应收重算是信用管理里经常用到的功能，三个应收的算法比较复杂。项目上经常会遇到信用不准确的时候，需要此功能来重算。如果有顾问想研究三个应收具体算法，分析三个应收错误原因，现在整理了三个应收后天抓取数据sql语句，供参考分析二、语句汇总：--订单未审核应收selectth.pk_corp,th.csaleid,csalecorpid,cdeptid,cemployeeid,cprolineid,cbiztype,ccustomerid,sum(nvl(tb.nsummny,0))mny,0,0,0,0,0fromso_saleorder_btb,so_salethwherefstatusin(1,7)andth.pk_corp='1001'andth.bretinvflag!='Y'andth.dr=0andtb.csaleid=th.csaleidandtb.blargessflag='N'andtb.dr=0andth.ccustomeridin('0001A11000000000031H')5/13groupbyth.pk_corp,th.csaleid,csalecorpid,cdeptid,cemployeeid,cprolineid,cbiztype,ccustomerid--订单已审核insertintot_soCredit_wasteNum(cgeneralbid,nwastenum)selecttb.csourcebillbid,sum(isnull(nnum,0)+isnull(nnorwastnum,0))fromic_wastagebillth,ic_wastagebill_btb,ic_general_htgh,so_crbusitypewheretb.cwastagebillid=th.cwastagebillidandtb.csourcebillid=tgh.cgeneralhidandso_crbusitype.cbiz=tgh.cbiztypeandtb.dr=0andth.dr=0andtgh.dr=0andtb.csourcetypecode='4C'and((so_crbusitype.isadjust='Y'andth.fstatusflag<>1)orso_crbusitype.isadjust='N')andth.pk_corp='1001'andtb.pk_corp='1001'groupbytb.csourcebillbid;------------------------------------------------订单已审核selectth.pk_corp,th.cgeneralhid,soh.csalecorpid,soh.cdeptid,th.cbizid,sob.cprolineid,soh.cbiztype,soh.ccustomerid,0,0,sum(case6/13when(abs(ISNULL(tb.noutnum,0))<=abs(ISNULL(bb3.nsaleinsettlenum,0)+ISNULL(bb3.nrushnum,0)))then-ISNULL(wasb.nwastenum,0)*ISNULL(tb.ntaxprice,0)elseISNULL(tb.ntaxmny,0)-(ISNULL(bb3.nsaleinsettlenum,0)+ISNULL(bb3.nrushnum,0)+ISNULL(wasb.nwastenum,0))*ISNULL(tb.ntaxprice,0)end)mny,0,0,0fromic_general_btbinnerjoinic_general_hthon(tb.pk_corp='1001'andtb.dr=0andtb.flargess!='Y'andtb.cgeneralhid=th.cgeneralhidandth.cbilltypecode='4C'andth.pk_corp='1001')innerjoinso_crbusitypetzon(tz.flag='Y'andtz.cbiz=th.cbiztype)innerjoinso_saleorder_bsobon(tb.cfirstbillbid=sob.corder_bidandsob.frowstatus!=6)innerjoinso_saleexecutesoexeon(isnull(soexe.bsquareendflag,'N')<>'Y'andtb.cfirstbillbid=soexe.csale_bid)innerjoinso_salesohon(tb.cfirstbillhid=soh.csaleidandsoh.pk_corp=th.pk_corpandsoh.cbiztype=th.cbiztypeandsoh.fstatus!=6andsoh.csaleid=sob.csaleidandsoh.csaleid=soexe.csaleid)leftouterjoint_soCredit_wasteNumwasbonwasb.cgeneralbid=tb.cgeneralbidinnerjoinic_general_bb3bb3on((abs(ISNULL(tb.noutnum,0))-abs((ISNULL(bb3.nsaleinsettlenum,0)+ISNULL(bb3.nrushnum,0)))>=0)andbb3.cgeneralbid=tb.cgeneralbid)where(1=1)andsoh.ccustomeridin('0001A11000000000031H')groupbyth.pk_corp,th.cgeneralhid,soh.csalecorpid,7/13soh.cdeptid,th.cbizid,sob.cprolineid,soh.cbiztype,soh.ccustomerid--统计退货申请单来源的出库单来源销售订单,退货申请单的出库单分别统计,因为使用内连接selectth.pk_corp,th.cgeneralhid,soh.csalecorpid,soh.cdeptid,th.cbizid,sob.pk_productline,soh.cbiztype,soh.ccustomerid,0,0,sum(casewhen(abs(nvl(tb.noutnum,0))<=abs((nvl(bb3.nsaleinsettlenum,0)+nvl(bb3.nrushnum,0)+nvl(tb.naccumwastnum,0))))then0elsenvl(tb.ntaxmny,0)-((nvl(bb3.nsaleinsettlenum,0)+nvl(bb3.nrushnum,0)+nvl(tb.naccumwastnum,0))*nvl(tb.ntaxprice,0))end)mny,0,0,0fromic_general_btbinnerjoinic_general_hthon(tb.cgeneralhid=th.cgeneralhidandtb.dr=0andtb.flargess!='Y'andth.cbilltypecode='4C'andth.pk_corp='1001')innerjoinso_applysohon(tb.cfirstbillhid=soh.pk_applyandsoh.pk_corp=th.pk_corpandsoh.cbiztype=th.cbiztype)innerjoinso_apply_bsobon(tb.cfirstbillbid=sob.pk_apply_bandsob.frowstatus!=6)andtb.cbodybilltypecode='4C'8/13innerjoinso_crbusitypetzon(soh.cbiztype=tz.cbizandth.cbiztype=tz.cbizandtz.flag='Y')andsoh.pk_apply=sob.pk_applyinnerjoinic_general_bb3bb3on(bb3.cgeneralbid=tb.cgeneralbidand(abs(nvl(tb.noutnum,0))-abs((nvl(bb3.nsaleinsettlenum,0)+nvl(bb3.nrushnum,0)+nvl(tb.naccumwastnum,0)))>0))where(1=1)andsoh.ccustomeridin('0001A11000000000031H')groupbyth.pk_corp,th.cgeneralhid,soh.csalecorpid,soh.cdeptid,th.cbizid,sob.pk_productline,soh.cbiztype,soh.ccustomerid--先票后货时本币价税合计-min(订单数量,累计开票数量)*订单本币含税净价不包含退货订单、开票结束、审批未通过订单、劳务折扣行selectth.pk_corp,th.csaleid,csalecorpid,cdeptid,cemployeeid,cprolineid,cbiztype,ccustomerid,0,sum(casewhen(abs(nvl(tb.nnumber,0))<=(abs(nvl(te.ntotalinventorynumber,0))-abs(nvl(te.ntranslossnum,0))))then0WHEN(abs(nvl(tb.nnumber,0))
0and(te.bifinventoryfinish<>'Y'andte.bifinventoryfinish<>'y')andth.ccustomeridin('0001A11000000000031H')groupbyth.pk_corp,th.csaleid,csalecorpid,cdeptid,cemployeeid,cprolineid,cbiztype,ccustomerid--数量不为0劳务折扣行数量不为0：本币价税合计-min(订单数量,累计开票数量)*订单本币含税净价10/13--不包含退货订单、开票结束、审批未通过订单、非劳务折扣行selectth.pk_corp,th.csaleid,csalecorpid,cdeptid,cemployeeid,cprolineid,cbiztype,ccustomerid,0,sum(casewhen(abs(nvl(tb.nnumber,0))<=abs(nvl(te.ntotalbalancenumber,0))and(tinv.laborflag<>'Y'ortinv.discountflag<>'Y'))then0elsenvl(tb.nsummny,0)-(nvl(te.ntotalbalancenumber,0)*nvl(tb.ntaxnetprice,0))end)mny,0,0,0,0fromso_saleorder_btb,bd_invbasdoctinv,so_saleth,so_saleexecutete,so_crbusitypetzwhereth.fstatusin(2,3)andth.pk_corp='1001'andth.bretinvflag!='Y'andth.dr=0andth.cbiztype=tz.cbizand(tz.flag='N'ortinv.laborflag='Y'ortinv.discountflag='Y')andtb.csaleid=th.csaleidandtb.cinvbasdocid=tinv.pk_invbasdocandtb.blargessflag='N'andtb.dr=0andtb.corder_bid=te.csale_bidandth.csaleid=te.csaleidandte.creceipttype='30'and(abs(nvl(tb.nnumber,0))-11/13abs(nvl(te.ntotalbalancenumber,0)))>=0and(nvl(te.bsquareendflag,'N')<>'Y'andnvl(te.bsquareendflag,'N')<>'y')andth.ccustomeridin('0001A11000000000031H')groupbyth.pk_corp,th.csaleid,csalecorpid,cdeptid,cemployeeid,cprolineid,cbiztype,ccustomeridselectth.dwbm,th.vouchid,tsa.csalecorpid,deptid,ywybm,tb.productline,xslxbm,isnull(ordercusmandoc,ksbm_cl),bd_cumandoc.pk_cumandoc,0,0,0,sum(casewhen(th.zgyf=1orth.zgyf=2)thenbbyeelse0end),--暂估应收sum(casewhen(th.zgyf=0)thenbbyeelse0end),--确认应收0fromarap_djfbtbinnerjoinarap_djzbthon(tb.dr=0andtb.wldx=0andtb.djdl='ys'and12/13tb.isverifyfinished='N'andtb.dwbm='1001'andtb.vouchid=th.vouchidandth.djdl='ys'andth.dr=0and(rtrim(tb.ph)='30'orrtrim(tb.ph)='3U'ortb.phisnull)and(th.zgyf=1orth.zgyf=2orth.zgyf=0)andth.hzbz='-1')innerjoinbd_cubasdocontb.ordercusmandoc=bd_cubasdoc.pk_cubasdocinnerjoinbd_cumandocon(bd_cumandoc.pk_cubasdoc=bd_cubasdoc.pk_cubasdocand(bd_cumandoc.custflag='2'orbd_cumandoc.custflag='0')andbd_cumandoc.pk_corp='1001')leftouterjoinso_saletsaontb.xyzh=tsa.csaleidwhere(tsa.pk_corp='1001'ortsa.pk_corpisnull)andtb.ordercusmandocin('0001A1100000000001LC')groupbyth.dwbm,th.vouchid,tsa.csalecorpid,deptid,ywybm,tb.productline,xslxbm,isnull(ordercusmandoc,ksbm_cl),bd_cumandoc.pk_cumandoc--冲减应收selectth.pk_corp,th.carsubid,null,th.cdeptid,th.cemployeeid,tb.cproducelineid,tb.cbiztypeid,th.ccustomerid,0,0,0,0,0,sum(nvl(tb.narsubmny,0)-nvl(tb.narsubinvmny,0))mny13/13fromso_arsubaccttb,so_arsubthwhereth.dr=0andth.pk_corp='1001'andtb.carsubid=th.carsubidandtb.dr=0andth.ccustomeridin('0001A11000000000031H')groupbyth.pk_corp,th.carsubid,th.cdeptid,th.cemployeeid,tb.cproducelineid,tb.cbiztypeid,th.ccustomerid;------------------------------------selectth.pk_corp,th.csaleid,null,null,null,tb.cprolineid,th.cbiztype,nvl(tb.ccustomerid,th.creceiptcorpid),0,0,0,0,0,sum(nvl(tb.nsubcursummny,0)-nvl(tb.nsummny,0))mnyfromso_saleinvoice_btb,so_saleinvoicethwhereth.dr=0andth.pk_corp='1001'andth.fstatusin(1,7,8)andtb.csaleid=th.csaleidandtb.blargessflag='N'andtb.dr=0andtb.ccustomeridin('0001A11000000000031H')groupbyth.pk_corp,th.csaleid,tb.cprolineid,th.cbiztype,nvl(tb.ccustomerid,th.creceiptcorpid)
