发货单列表：未开票金额如何计算？用友软件股份有限公司姓名：牛春艳2010年08月18日问题描述总结分析过程问题：版本872。在热线中，常常有客户反映，在发货单列表中显示的未开票金额数据不对。由于帮助文档中，也没有说明该栏目的金额计算过程。所以本专题的研究内容是：未开票金额的计算过程。问题描述首先，查询发货单列表时，通过事件探查器跟踪：分析过程select''asselcol,cdlcode,ddate,(casewhenbfirst=1then'是'else'否'end)asbfirst,isale,(casewhenbreturnflag=1then'是'else'否'end)asbreturnflag,itb,csettleall,cvouchtype,cbustype,cstcode,cstname,ccuscode,ccusabbname,ccusname,cdepcode,cdepname,cpersoncode,cpersonname,cexch_name,iexchrate,ccontractid,ccontracttagcode,(Sales_FHD_W.cSoCode)asbodysocode,(cordercode)ascordercode,(iorderrowno)asiorderrowno,ccorcode,csbvcode,cwhcode,cwhname,cinvcode,cinvaddcode,cinvname,ccusinvcode,ccusinvname,cinvstd,cvenabbname,iinvweight,cbatch,dmdate,imassdate,cmassunit,dvdate,ccode,(cvmivencode)ascvmivencode,(cvmivenname)ascvmivenname,(iexpiratdatecalcu)asiexpiratdatecalcu,(cbatchproperty1)ascbatchproperty1,(cbatchproperty10)ascbatchproperty10,(cbatchproperty2)ascbatchproperty2,(cbatchproperty3)ascbatchproperty3,(cbatchproperty4)ascbatchproperty4,(cbatchproperty5)ascbatchproperty5,(cbatchproperty6)ascbatchproperty6,(cbatchproperty7)ascbatchproperty7,(cbatchproperty8)ascbatchproperty8,(cbatchproperty9)ascbatchproperty9,(cexpirationdate)ascexpirationdate,igrouptype,cgroupcode,cinvm_unit,iquantity,cunitid,cinva_unit,iinvexchrate,inum,iquotedprice,itaxunitprice,iunitprice,imoney,(Sales_FHD_W.iTaxRate)asbodytaxrate,itax,isum,inatunitprice,inatmoney,inattax,inatsum,iinvlscost,fsalecost,fsaleprice,kl,kl2,dkl1,dkl2,idiscount,inatdiscount,iretquantity,foutquantity,foutnum,(fsumsignquantity)asfsumsignquantity,(fSumSignNum)asfsumsignnum,isettlequantity,isettlenum,(iexchsum)asiexchsum,(imoneysum)asimoneysum,iqanum,iqaquantity,bqachecked,bqachecking,bqaneedcheck,bgsp,bqaurgency,(ccreditcuscode)asccreditcuscode,(ccreditcusname)asccreditcusname,ccuspaycond,icuscreline,iarmoney,ccrechpname,cpaycode,(bcredit)asbcredit,cpayname,(cgatheringplan)ascgatheringplan,(cgatheringplanname)ascgatheringplanname,(dcreditstart)asdcreditstart,(icreditdays)asicreditdays,(dgatheringdate)asdgatheringdate,(Sales_FHD_T.cSoCode)asheadsocode,(Sales_FHD_T.iTaxRate)asheadtaxrate,cmaker,cverifier,cbaccounter,ccloser,csccode,ccuspostcode,cscname,ccusphone,ccusperson,(cdeliverunit)ascdeliverunit,(caddcode)ascaddcode,cshipaddress,(ccontactname)asccontactname,(cofficephone)ascofficephone,(cmobilephone)ascmobilephone,(casewhenbinvbatch=1then'是'else'否'end)asbinvbatch,(casewhenbinvquality=1then'是'else'否'end)asbinvquality,btrack,citem_class,citem_cname,citemcode,citemname,(Sales_FHD_T.cMemo)ascmemo,(funsignquantity)asfunsignquantity,(funsignnum)asfunsignnum,icreditstate,ireturncount,iswfcontrolled,iverifystate,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,cinvdefine1,cinvdefine2,cinvdefine3,cinvdefine4,cinvdefine5,cinvdefine6,cinvdefine7,cinvdefine8,cinvdefine9,cinvdefine10,cinvdefine11,cinvdefine12,cinvdefine13,cinvdefine14,cinvdefine15,cinvdefine16,cdefine1,cdefine2,cdefine3,cdefine4,cdefine5,cdefine6,cdefine7,cdefine8,cdefine9,cdefine10,cdefine11,cdefine12,cdefine13,cdefine14,cdefine15,cdefine16,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,ccusdefine1,ccusdefine2,ccusdefine3,ccusdefine4,ccusdefine5,ccusdefine6,ccusdefine7,ccusdefine8,ccusdefine9,ccusdefine10,ccusdefine11,ccusdefine12,ccusdefine13,ccusdefine14,ccusdefine15,ccusdefine16,isosid,ibatch,ivtid,ufts,(Sales_FHD_T.DLID)asdlid,icorid,idlsid,(ioutgolden)asioutgolden,cmodifier,dmoddate,dverifydate,dcreatesystime,dverifysystime,dmodifysystime,(cast(isnull(iquantity,0)-isnull(foutquantity,0)asdecimal(26,9)))asWCSL,(cast(isnull(iquantity,0)-isnull(isettlequantity,0)asdecimal(26,9)))asWKSL,(cast(casewhenisnull(iquantity,0)=0thenisnull(isum,0)-isnull(isettlenum,0)elsecasewhenisnull(iquantity,0)-isnull(isettlequantity,0)-isnull(iretquantity,0)=0then0else(isnull(iquantity,0)-isnull(iretquantity,0)-isnull(isettlequantity,0))*itaxunitpriceendendasdecimal(26,9)))asWKJE,(cast(isnull(isum,0)-isnull(iexchsum,0)asdecimal(26,9)))asWSKYB,(cast(isnull(inatsum,0)-isnull(imoneysum,0)asdecimal(26,9)))asWSKBB,(caccode)ascaccode,(cacname)ascacname,(cdemandcode)ascdemandcode,(cdemandid)ascdemandid,(cdemandmemo)ascdemandmemo,(dblpreexchmomey)asdblpreexchmomey,(dblpremomey)asdblpremomey,(idemandseq)asidemandseq,(idemandtype)asidemandtype分析过程from#divTable0446D776BD054AAF87CEA3FD5509F344InnerJoinSales_FHD_TinnerjoinSales_FHD_WonSales_FHD_T.dlid=Sales_FHD_W.dlidOn#divTable0446D776BD054AAF87CEA3FD5509F344.tmpPrimaryID=Sales_FHD_W.idlsidwheredivID>0anddivID<=500orderbycdlcodeASC分析过程提取涉及到“未开票金额”的SQL跟踪语句：(cast(casewhenisnull(iquantity,0)=0thenisnull(isum,0)-isnull(isettlenum,0)elsecasewhenisnull(iquantity,0)-isnull(isettlequantity,0)-isnull(iretquantity,0)=0then0else(isnull(iquantity,0)-isnull(iretquantity,0)-isnull(isettlequantity,0))*itaxunitpriceendendasdecimal(26,9)))asWKJE解析：先判断发货单数量，如果数量为空，未开票金额=原币价税合计-结算金额；如果数量不为空，当（发货数量-开票数量-退货数量）为0，则未开票金额=0；如果如果数量不为空，并且当（发货数量-开票数量-退货数量）不为0，未开票金额=（发货数量-开票数量-退货数量）*原币含税单价。分析过程根据上面的语句，分以下三种情况分析：1、发货单数量为NULL；由于目前软件中，折扣、劳务、退补型存货，数量不能为空，因此该情况不存在。2、数量不为空，又分两种：2.1当（发货数量-开票数量-退货数量）为0，则未开票金额=0；2.2当（发货数量-开票数量-退货数量）不为0，未开票金额=（发货数量-开票数量-退货数量）*原币含税单价。对于第2种情况，实质上，是从发货单角度出发考虑的，并未考虑到退货单。即如果有一张退货单时，系统判断结果是满足2.2条件，未开票金额就是退货单金额。这样如果单纯是手工录入的退货单，没有问题；但是如果是参照发货单生成的退货单，就有问题；如下图所示：分析过程发货单：02001，发货数量10，退货数量1。则该发货单实际的未开票金额=（10-1）*含税单价=891。退货单：02001，退货数量-1，未开票金额=99。二者软件中显示的合计却是：未开票金额=792。针对此问题，872已经出补丁：CP-U872-3002-100520-SA.分析过程补充：修改后，发货单未开票金额的取数公式进行如下修改：(casewhenisnull(icorid,0)=0then(cast(casewhenisnull(iquantity,0)=0thenisnull(isum,0)-isnull(fretsum,0)-isnull(isettlenum,0)elsecasewhenisnull(iquantity,0)-isnull(isettlequantity,0)-isnull(iretquantity,0)=0then0else(isnull(iquantity,0)-isnull(iretquantity,0)-isnull(isettlequantity,0))*itaxunitpriceendendasdecimal(26,9)))else0end)asWKJE分析该语句：Sales_FHD_W发货单子表视图中，icorid是指：原发货单退货单子表标识。上述语句的含义：先判断：原发货单子表标识不为空，则未开票金额为0；如果原发货单子表标识为空，则判断发货单数量，如果数量为空，未开票金额=原币价税合计-退货金额-结算金额；如果数量不为空，当（发货数量-开票数量-退货数量）为0，则未开票金额=0；如果如果数量不为空，并且当（发货数量-开票数量-退货数量）不为0，未开票金额=（发货数量-开票数量-退货数量）*原币含税单价。分析过程对比之前与修改后的语句：增加了原发货单子表标识的判断条件，如果原发货单子表标识不为空，则说明该单据是有来源发货单，则默认未开票金额为0。分析过程另外：过滤条件中，发货单执行情况—开票未完成也进行改进：isnull(icorid,0)=0andcast(casewhenisnull(iQuantity,0)<>0then(abs(isnull(iQuantity,0))-abs(isnull(iRetQuantity,0))-abs(isnull(iSettleQuantity,0)))else(abs(isnull(inatsum,0))-abs(isnull(fretsum,0))-abs(isnull(isettlenum,0)))endasdecimal(26,9))>0解析：首先，也是判断原发货单子表标识，只过滤没有“原发货单子表标识”的记录。然后再判断：当数量不为空，要求：（发货数量-开票数量-退货数量）>0;当数量为空，要求：（本币价税合计-退货金额-累计结算金额）>0。注：发货单列表中，默认过滤时，已经关闭的记录，仍然可以过滤出来，所以为了显示正确的未开票金额，过滤条件中，关闭标志中选择未关闭。分析过程发货单列表中，为了显示正确的未开票金额，需要的条件是：1、872要打上补丁：CP-U872-3002-100520-SA；890没有问题。2、过滤条件中，发货单执行情况：开票未完成；关闭标志：未关闭。总结
