助手视图应用问题销售订单应收余额取不到数用友软件股份有限公司王小朋Thursday,March28,2024案例分享一、错误现象二、分析/跟踪过程三、最终解决方案四、总结一、错误现象环境：U890+最新销售补丁客户来电：增加销售订单时选择表头客户，在助手视图中显示客户信息，所有客户应收余额是0.00？实际客户都有应收余额的；问题现象：销售订单选中表头客户时，助手视图中的应收余额显示为0，没有取到客户档案中的应收余额，将表头“客户应收余额”显示出来是1500一、错误现象二、分析/跟踪过程没有报错的信息，从上向下查看跟踪语句，首先定位到敏感信息：调用信用余额表报表取客户应收余额、信用余额，之前有调用助手的存储过程。首先执行存储过程：execusp_sa_getCusInfoForVouchHelp@cCusCode=N'1‘拿该语句在查询分析器中执行，如下，有相应的报错信息：二、分析/跟踪过程打开存储过程[usp_sa_getCusInfoForVouchHelp]，分析如下：ALTERproc[dbo].[usp_sa_getCusInfoForVouchHelp](@cCusCodeasnvarchar(60)=N'')asdeclare@iTotalMoneydecimal(20,6)--累计购货金额declare@iArBalancedecimal(20,6)--应收余额declare@iCreBalancedecimal(20,6)--信用余额declare@chrWhereCusnvarchar(200)--客户条件set@chrWhereCus=N'andccuscode=N'''+@cCusCode+N'''‘--删除临时表ifexists(selectnamefromtempdb..sysobjectswhereid=object_id(N'tempdb..UFTmpTable_sa_credit999'))droptabletempdb..UFTmpTable_sa_credit999--调用信用余额表报表取客户应收余额、信用余额execSa_saleCreReportN‘[UFTmpTable_sa_credit999]’,1,N‘’,@chrWhereCus,N‘’,N‘’,N‘SA’,N‘‘（主要是执行该存储过程）selectisnull(sum(应收余额),0),isnull(sum(信用余额),0)fromtempdb..UFTmpTable_sa_credit999二、分析/跟踪过程--计算累计购货金额select@iTotalMoney=isnull(sum(imoney),0)fromsalebillvouchsbinnerjoinsalebillvouchhonb.sbvid=h.sbvidwhereh.ccuscode=@ccuscode--返回数据selectcCusCode,ccusoaddressascCusAddress,--客户地址ccuspersonascContact,--联系人ccusppersonascManageEmp,--主管业务员customerkcodeasCLevel,--客户等级@iTotalMoneyasdTotalBuyAmt,--累计购货金额@iArBalanceasdeCreditBal,--应收余额@iCreBalanceasdeARBal--信用余额fromcustomerwherecCusCode=@cCusCodeGO二、分析/跟踪过程打开该存储继续分析：execSa_saleCreReportN‘[UFTmpTable_sa_credit999]’,1,N‘’,@chrWhereCus,N‘’,N‘’,N‘SA’,N‘‘（主要是执行该存储过程）再分析报错信息：消息156，级别15，状态：1，第6行关键字'and'附近有语法错误。消息208，级别16，状态1，第1行对象名'tempdb..tmp_EADCFAAC_BCE1_4194_B128_BFF236E109BC'无效。消息207，级别16，状态1，第5行列名'bverify'无效。二、分析/跟踪过程在该报错信息前加入Print语句，将查询语句显示出来：单击此处编辑母版文本样式if@bInluechecked=1set@chrsql=N'selectisnull(sum(isnull(INatMoney,0)),0)asExSum,max(ddate)asddate,max(a.cevcode)ascsbvcode,max(a.cCusCode)ascCusCode,max(cDepCode)ascDepCode,max(cPersonCode)ascPersonCodeintotempdb..'+@tmp_ExFirst+N'fromexpensevouchawith(nolock)innerjoinexpensevouchsbona.id=b.idleftjoin(selectccuscodeasccuscode1,cCusCreditCompanyfromcustomer)customerona.ccuscode=customer.ccuscode1whereisnull(cVerifier,N'''')=N'''''+@chrWhere+N'groupbya.id'elseset@chrsql=N'selectisnull(sum(isnull(INatMoney,0)),0)asExSum,max(ddate)asddate,max(a.cevcode)ascsbvcode,max(a.cCusCode)ascCusCode,max(cDepCode)ascDepCode,max(cPersonCode)ascPersonCodeintotempdb..'+@tmp_ExFirst+N'fromexpensevouchawith(nolock)innerjoinexpensevouchsbona.id=b.idleftjoin(selectccuscodeasccuscode1,cCusCreditCompanyfromcustomer)customerona.ccuscode=customer.ccuscode1leftjoinap_vouchona.cvouchtype=ap_vouch.cvouchtypeanda.cvouchid=ap_vouch.cvouchidwhereisnull(cVerifier,N'''')<>N''''andisnull(ccheckman,N'''')=N'''''+@chrWhere+N'groupbya.id'print@chrsql二、分析/跟踪过程再执行：exec[usp_sa_getCusInfoForVouchHelp]'1’Print出的语句如下：selectisnull(sum(isnull(INatMoney,0)),0)asExSum,max(ddate)asddate,max(a.cevcode)ascsbvcode,max(a.cCusCode)ascCusCode,max(cDepCode)ascDepCode,max(cPersonCode)ascPersonCodeintotempdb..tmp_EADCFAAC_BCE1_4194_B128_BFF236E109BCfromexpensevouchawith(nolock)innerjoinexpensevouchsbona.id=b.idleftjoin(selectccuscodeasccuscode1,cCusCreditCompanyfromcustomer)customerona.ccuscode=customer.ccuscode1leftjoinap_vouchona.cvouchtype=ap_vouch.cvouchtypeanda.cvouchid=ap_vouch.cvouchidwhereisnull(cVerifier,N'')<>N''andisnull(ccheckman,N')=N'andccuscode=N'1'groupbya.id二、分析/跟踪过程拿以上语句再执行，找到报错信息，那么，这样错误信息就定位到了，isnull(ccheckman,N‘)=N’，该语句缺少两个单引号，加入后再执行修改[Sa_saleCreReport]存储过程即可，问题解决。二、分析/跟踪过程三、最终解决方案ALTERprocedure[dbo].[Sa_saleCreReport]if@bInluechecked=1set@chrsql=N'selectisnull(sum(isnull(INatMoney,0)),0)asExSum,max(ddate)asddate,max(a.cevcode)ascsbvcode,max(a.cCusCode)ascCusCode,max(cDepCode)ascDepCode,max(cPersonCode)ascPersonCodeintotempdb..'+@tmp_ExFirst+N'fromexpensevouchawith(nolock)innerjoinexpensevouchsbona.id=b.idleftjoin(selectccuscodeasccuscode1,cCusCreditCompanyfromcustomer)customerona.ccuscode=customer.ccuscode1whereisnull(cVerifier,N'''')=N'''''+@chrWhere+N'groupbya.id'elseset@chrsql=N'selectisnull(sum(isnull(INatMoney,0)),0)asExSum,max(ddate)asddate,max(a.cevcode)ascsbvcode,max(a.cCusCode)ascCusCode,max(cDepCode)ascDepCode,max(cPersonCode)ascPersonCodeintotempdb..'+@tmp_ExFirst+N'fromexpensevouchawith(nolock)innerjoinexpensevouchsbona.id=b.idleftjoin(selectccuscodeasccuscode1,cCusCreditCompanyfromcustomer)customerona.ccuscode=customer.ccuscode1leftjoinap_vouchona.cvouchtype=ap_vouch.cvouchtypeanda.cvouchid=ap_vouch.cvouchidwhereisnull(cVerifier,N‘’‘’)<>N‘’‘’andisnull(ccheckman,N‘’‘’)=N‘’‘’‘（主要修改的地方）+@chrWhere+N'groupbya.id'exec(@chrsql)--计算应收账款本币价税合计金额if@bArUse=1and@bnvarChAr=1beginset@chrsql=N'INSERTintotempdb..'+@tmp_SACrdetail+N'selectddateasddate,Ap_CreditDetail.cCusCodeascCusCode,cDepCodeascDepCode,cPersonCodeascPersonCode,cCodeascDJCode,0asSOSum,0asfhsum,0asbillsum,0asexsum,Exsumasarsum,0ascontractsum,0asexordersum,0asexconsignsum,0asexinvoicesum,ExsumasTotalSum,cVouchTypeasvouchName,nullasiCrLine,nullasicredate,-1*ExsumasiCrYe,5asitypefromAp_CreditDetailwith(nolock)leftjoin(selectccuscodeasccuscode1,cCusCreditCompanyfromcustomer)customeronAp_CreditDetail.ccuscode=customer.ccuscode1whereExsum<>0‘+casewhen@cInluechecked=1thenN’‘else’and1=1‘end+@chrwhere（主要修改的地方）exec(@chrsql)end三、最终解决方案三、最终解决方案四、总结该助手的取值，如果销售选项中信用控制，信用检查点选择“单据保存”，则890+SA补丁，可以显示出应收余额，若信用检查点选择“单据审核”，则需要按照本案例进行相应的修改。助手视图，以其便捷的应用性在客户使用较普遍，烦请大家多多关注，谢谢！！
