|
|
问题现象：在用友U8erp系统中用友U8版本号：U8软件的供应链产品线销售管理模块出现如下问题：在U821销售系统中查询销售统计表时，按货物分组查询出的单价不对，可能会是售价的一半等等。请帮忙解答！
原因分析：存储过程Sa_MoveSaleDetail有问题。
解决方案：在用友erp 系统版本U8软件中先不按任何条件分组，查询某货物的销售统计表，将数据记录下来，共有四行数据，其中两行为成本的数据，单价为空，另两行为销售数据，单价相同；再按货物分组查询销售统计表，只有一行数据，与前面记录下来的数据对比，发现单价为未分组的一半，跟踪查询过程，发现当不按任何条件分组时，系统直接查询出单价，未经过加工，此单价应是正确的，但成本一行中单价为空，而当按货物分组时，系统对单价取值时用的是avg()函数，即做了平均，以上面的数据为例，两行单价为零的加上两行单价不为零且相同的数据，再平均，正好是一半了；原因找到了，但如何解决问题呢？由于成本两行的单价为空，故再分析取成本数据的过程，发现系统调用了Sa_MoveSaleDetail存储过程，打开些存储过程分析，发现取成本单价的过程是直接写的0，0与另一数平均的话，当然减半了，由于用avg()函数取平均数，而avg()函数是不对NULL平均的，将取成本半价的SQL语句由0改成NULL后再查询销售统计表，发现问题解决了！修改后的Sa_MoveSaleDetail如下：IFEXISTS(SELECT*FROMsysobjectsWHEREname='Sa_MoveSaleDetail'ANDtype='P')DROPPROCEDURESa_MoveSaleDetailGO/*版本：U821当仓库编码为'01'、'001'等前面为'0'时销售统计表统计不到数据修改仓库条件set@chrsql=@chrsql+'andSalebillvouchs.cWhCode='+ltrim(rtrim(@chrwarehouse))为set@chrsql=@chrsql+'andSalebillvouchs.cWhCode='''+ltrim(rtrim(@chrwarehouse))+''''U820无此问题，因U820此过程只有前面两个参数2003.10.14销售统计表按存货分组时单价不对，可能会变成一半2004.12.13南京用友维护部丁德安*/CREATEPROCEDURESa_MoveSaleDetail(/*取得销售统计表存储过程作者：刘小东*/@chrtablevarchar(200)=null,/*保存销售明细账临时表名称*/@chrWhere1varchar(255)=null,/*查询条件名称*/@chrSaleDatevarchar(100),--开票日期@chrKeepDatevarchar(100),--结算日期@chrDepvarchar(50),--部门条件@chrCheckervarchar(50),--审核条件@chrwarehousevarchar(40)--仓库条件)ASdeclare@chrSQLvarchar(4000)declare@chrCostvarchar(50)declare@chrWherevarchar(1000)declare@chrStartDatevarchar(50)ifltrim(rtrim(@chrwhere1))='newReport_ParameterFromTempTable'begin/*条件参数通过临时表传递*/set@chrwhere=(selectnamefromtempdb..newReportParameter)endelse/*条件参数直接传递*/beginset@chrwhere=rtrim(ltrim(@chrwhere1))end/*取销售系统启用日期*/set@chrStartDate=(selectisnull(cValue,'1900-01-01')fromaccinformationwherecSysid='Sa'andcName='dStartDate')if@chrStartDate''beginiflen(ltrim(rtrim(@chrStartDate)))='''+@chrStartDate+''''end/*连查发票主表，取得当前收入*/ifexists(select*fromtempdb..sysobjectswherename='Temp_SaleIncome')droptabletempdb..Temp_SaleIncomeset@chrSQL='SELECTSaleBillVouchs.AutoIDASAutoID,SaleBillVouchs.cWhCodeAScWhCode,SalebillVouch.cSTCodeascSTCode,SaleBillVouch.dDateASdDate,SaleBillVouch.cDepCodeAScDepCode,SaleBillVouch.cVouchTypeascVouType,SaleBillVouch.cCusCodeAScCusCode,Customer.cCCCodeAScCCCode,Customer.cDCCodeAScDCCode,Customer.cCusHeadCodeAScCusHeadCode,SaleBillVouch.cPersonCodeAScPersonCode,SaleBillVouchs.cInvCodeAScInvCode,Inventory.cInvCCodeAScInvCCode,SaleBillVouchs.cFree1AScFree1,SaleBillVouchs.cFree2AScFree2,SaleBillVouchs.iQuantityASiQuantity,SaleBillVouchs.iNumASiNum,SaleBillVouchs.iNatUnitPriceASiNatUnitPrice,SaleBillVouchs.iNatMoneyASiNatMoney,SaleBillVouchs.iNatTaxASiNatTax,SaleBillVouchs.iNatSumASiNatSum,SaleBillVouchs.iNatDisCountASiNatDisCount,Inventory.bServiceasbService,Salebillvouch.cCheckerascChecker,Salebillvouch.cDefine1,Salebillvouch.cDefine2,Salebillvouch.cDefine3,Salebillvouch.cDefine4,Salebillvouch.cDefine5,Salebillvouch.cDefine6,Salebillvouch.cDefine7,Salebillvouch.cDefine8,Salebillvouch.cDefine9,Salebillvouch.cDefine10,Salebillvouchs.cDefine22,Salebillvouchs.cDefine23,Salebillvouchs.cDefine24,Salebillvouchs.cDefine25,Salebillvouchs.cDefine26,Salebillvouchs.cDefine27INTOtempdb..Temp_SaleInComeFROMSaleBillVouchsINNERJOINSaleBillVouchONSaleBillVouchs.SBVID=SaleBillVouch.SBVIDINNERJOINCustomerONSaleBillVouch.cCusCode=Customer.cCusCodeINNERJOINInventoryONSaleBillVouchs.cInvCode=Inventory.cInvCodeWhereisnull(Salebillvouch.cInvalider,'''')=''''andisnull(Salebillvouch.cSTCode,'''')'''''+@chrStartDate/*作废发票不能计算在内,销售类型不能为空,发票日期必须大于系统启用日期*/--加入条件--加入条件--日期条件ifnot@chrsaledateisnullandltrim(rtrim(@chrsaledate))''set@chrsql=@chrsql+'and'+ltrim(rtrim(@chrsaledate))--部门条件ifnot@chrdepisnullandltrim(rtrim(@chrdep))''set@chrsql=@chrsql+'andcDepCode'+ltrim(rtrim(@chrdep))--审核条件ifnot@chrCheckerisnullandltrim(rtrim(@chrchecker))''set@chrsql=@chrsql+'and'+ltrim(rtrim(@chrchecker))--仓库条件ifnot@chrwarehouseisnullandltrim(rtrim(@chrwarehouse))''set@chrsql=@chrsql+'andSalebillvouchs.cWhCode='''+ltrim(rtrim(@chrwarehouse))+''''--其他条件ifnot@chrwhereisnullandltrim(rtrim(@chrwhere))''set@chrsql=@chrsql+'and'+ltrim(rtrim(@chrwhere))exec(@chrsql)/*计算成本*//*数据准备*/ifexists(select*fromtempdb..sysobjectswherename='Temp_SaleCostsec')droptabletempdb..Temp_SaleCostsecset@chrsql='SELECTIA_Subsidiary.dKeepDateASddate,IA_Subsidiary.cWhCodeAScWhCode,IA_Subsidiary.cVouTypeAScvoutype,IA_Subsidiary.cInvCodeAScinvcode,IA_SubSidiary.cSTCodeascSTCode,Customer.cDCCodeascDCCode,Customer.cCusHeadCodeascCusHeadCode,Customer.cCCCodeascCCCode,Inventory.cInvCCodeascInvCCode,IA_Subsidiary.cCusCodeAScCusCode,IA_Subsidiary.cAccDepAScDepCode,IA_SubSidiary.cDepCodeascAccDep,IA_Subsidiary.cPersonCodeAScPersonCode,IA_Subsidiary.cFree1AScFree1,IA_Subsidiary.cFree2AScFree2,IA_Subsidiary.iMonthASiMonth,IA_Subsidiary.bMoneyFlagASbMoneyFlag,IA_Subsidiary.bSaleASbSale,Warehouse.cWhValueStyleAScWhValueStyle,IA_Subsidiary.cBillCodeAScBillCode,IA_Subsidiary.IDASid,IA_Subsidiary.cDLCodeAScDlCode,IA_Subsidiary.cDefine1,IA_Subsidiary.cDefine2,IA_Subsidiary.cDefine3,IA_Subsidiary.cDefine4,IA_Subsidiary.cDefine5,IA_Subsidiary.cDefine6,IA_Subsidiary.cDefine7,IA_Subsidiary.cDefine8,IA_Subsidiary.cDefine9,IA_Subsidiary.cDefine10,IA_Subsidiary.cDefine22,IA_Subsidiary.cDefine23,IA_Subsidiary.cDefine24,IA_Subsidiary.cDefine25,IA_Subsidiary.cDefine26,IA_Subsidiary.cDefine27,casewhen(cWhvaluestyle=''计划价法''orcWhvalueStyle=''售价法'')andIA_Subsidiary.bMoneyFlag=1thenisnull(IA_Subsidiary.iAOutPrice,0)-isnull(IA_Subsidiary.iDebitDifCost,0)+isnull(IA_Subsidiary.iCreditDifCost,0)elseisnull(IA_Subsidiary.iAOutPrice,0)endasiAOutPrice,Inventory.bServiceasbService,''复核''ascCheckerintotempdb..Temp_SaleCostsecFROMIA_SubsidiaryLEFTJOINWarehouseONIA_Subsidiary.cWhCode=Warehouse.cWhCodeINNERJOINCustomerOnIA_Subsidiary.cCusCode=Customer.cCusCodeINNERJOINInventoryOnIA_SubSidiary.cInvCode=Inventory.cInvCodeWHEREbRdFlag=0AND(cVouTypein(''26'',''27'',''28'',''29'',''32'')or(cVoutype=''21''andIa_subsidiary.bSale=1))'--加入条件--日期条件ifnot@chrKeepdateisnu。
|
|
|手机版|小黑屋|用友十万个为什么
( 赣ICP备13006939号 )
发表于 2016-9-25 15:52:40