sql server 2008 R2安装实例名不适用

转载

数据小筑 2024-09-10 09:16:43

文章标签 sql 客户端计算机同名 sql 数据 Customer 文章分类 SQL Server 数据库

用友U8 在U821销售系统中查询销售统计表时，按货物分组查询出的单价不对，可能会是售价的一半等等。用友U8 在U821销售系统中查询销售统计表时，按货物分组查询出的单价不对，可能会是售价的一半等等。

问题原因:存储过程Sa_MoveSaleDetail有问题。解决方法:先不按任何条件分组，查询某货物的销售统计表，将数据记录下来，共有四行数据，其中两行为成本的数据，单价为空，另两行为销售数据，单价相同；再按货物分组查询销售统计表，只有一行数据，与前面记录下来的数据对比，发现单价为未分组的一半，跟踪查询过程，发现当不按任何条件分组时，系统直接查询出单价，未经过加工，此单价应是正确的，但成本一行中单价为空，而当按货物分组时，系统对单价取值时用的是avg()函数，即做了平均，以上面的数据为例，两行单价为零的加上两行单价不为零且相同的数据，再平均，正好是一半了；原因找到了，但如何解决问题呢？由于成本两行的单价为空，故再分析取成本数据的过程，发现系统调用了Sa_MoveSaleDetail存储过程，打开些存储过程分析，发现取成本单价的过程是直接写的0，0与另一数平均的话，当然减半了，由于用avg()函数取平均数，而avg()函数是不对NULL平均的，将取成本半价的SQL语句由0改成NULL后再查询销售统计表，发现问题解决了！修改后的Sa_MoveSaleDetail如下：IF EXISTS(SELECT * FROM sysobjects WHERE name='Sa_MoveSaleDetail' AND type='P')DROP PROCEDURE Sa_MoveSaleDetailGO/* 版本：U821 当仓库编码为'01'、'001'等前面为'0'时销售统计表统计不到数据修改仓库条件 set @chrsql = @chrsql + ' and Salebillvouchs.cWhCode=' + ltrim(rtrim(@chrwarehouse )) 为 set @chrsql = @chrsql + ' and Salebillvouchs.cWhCode=''' + ltrim(rtrim(@chrwarehouse )) + '''' U820无此问题，因U820此过程只有前面两个参数 2003.10.14 销售统计表按存货分组时单价不对，可能会变成一半 2004.12.13 南京用友维护部丁德安*/CREATE PROCEDURE Sa_MoveSaleDetail(/*取得销售统计表存储过程作者：刘小东 [email protected] varchar(200) = null , [email protected] varchar(255) =null , [email protected] varchar(100) ,--开票日期 @chrKeepDate varchar(100),--结算日期 @chrDep varchar(50),--部门条件 @chrChecker varchar(50),--审核条件 @chrwarehouse varchar (40)--仓库条件 ) ASdeclare @chrSQL varchar(4000)declare @chrCost varchar(50)declare @chrWhere varchar(1000) declare @chrStartDate varchar (50)if ltrim(rtrim(@chrwhere1 )) = 'newReport_ParameterFromTempTable' begin /*条件参数通过临时表传递 */set @chrwhere = (select name from tempdb..newReportParameter )end else /*条件参数直接传递 */begin set @chrwhere = rtrim(ltrim(@chrwhere1))end /* 取销售系统启用日期 */set @chrStartDate = (select isnull(cValue,'1900-01-01') from accinformation where cSysid='Sa' and cName='dStartDate' ) if @chrStartDate <> '' begin if len(ltrim(rtrim(@chrStartDate))) <= 8 set @chrStartDate = convert(varchar(10),convert(smalldatetime,@chrStartDate,2),121)set @chrStartDate = ' and SalebillVouch.dDate>=''' + @chrStartDate + ''''end /*连查发票主表，取得当前收入 */if exists(select * from tempdb..sysobjects where name='Temp_SaleIncome') drop table tempdb..Temp_SaleIncomeset @chrSQL ='SELECT SaleBillVouchs.AutoID AS AutoID, SaleBillVouchs.cWhCode AS cWhCode, SalebillVouch.cSTCode as cSTCode ,SaleBillVouch.dDate AS dDate, SaleBillVouch.cDepCode AS cDepCode, SaleBillVouch.cVouchType as cVouType, SaleBillVouch.cCusCode AS cCusCode, Customer.cCCCode AS cCCCode, Customer.cDCCode AS cDCCode, Customer.cCusHeadCode AS cCusHeadCode, SaleBillVouch.cPersonCode AS cPersonCode, SaleBillVouchs.cInvCode AS cInvCode, Inventory.cInvCCode AS cInvCCode, SaleBillVouchs.cFree1 AS cFree1, SaleBillVouchs.cFree2 AS cFree2, SaleBillVouchs.iQuantity AS iQuantity, SaleBillVouchs.iNum AS iNum, SaleBillVouchs.iNatUnitPrice AS iNatUnitPrice, SaleBillVouchs.iNatMoney AS iNatMoney, SaleBillVouchs.iNatTax AS iNatTax, SaleBillVouchs.iNatSum AS iNatSum, SaleBillVouchs.iNatDisCount AS iNatDisCount , Inventory.bService as bService , Salebillvouch.cChecker as cChecker , 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.cDefine27 INTO tempdb..Temp_SaleInComeFROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouchs.SBVID = SaleBillVouch.SBVID INNER JOIN Customer ON SaleBillVouch.cCusCode = Customer.cCusCode INNER JOIN Inventory ON SaleBillVouchs.cInvCode = Inventory.cInvCode Where isnull(Salebillvouch.cInvalider,'''')=''''and isnull(Salebillvouch.cSTCode,'''') <> '''' ' + @chrStartDate /* 作废发票不能计算在内,销售类型不能为空,发票日期必须大于系统启用日期 */--加入条件 --加入条件 --日期条件 if not @chrsaledate is null and ltrim(rtrim(@chrsaledate)) <> '' set @chrsql = @chrsql + ' and ' + ltrim(rtrim( @chrsaledate )) --部门条件 if not @chrdep is null and ltrim(rtrim(@chrdep)) <> '' set @chrsql = @chrsql + ' and cDepCode ' + ltrim(rtrim(@chrdep )) --审核条件 if not @chrChecker is null and ltrim(rtrim(@chrchecker))<> '' set @chrsql = @chrsql + ' and ' + ltrim(rtrim( @chrchecker ) ) --仓库条件 if not @chrwarehouse is null and ltrim(rtrim(@chrwarehouse))<> '' set @chrsql = @chrsql + ' and Salebillvouchs.cWhCode=''' + ltrim(rtrim(@chrwarehouse )) + ''''--其他条件 if not @chrwhere is null and ltrim(rtrim(@chrwhere)) <> '' set @chrsql = @chrsql + ' and ' + ltrim(rtrim(@chrwhere))exec (@chrsql )/* 计算成本 *//*数据准备*/if exists(select * from tempdb..sysobjects where name='Temp_SaleCostsec') drop table tempdb..Temp_SaleCostsecset @chrsql = 'SELECT IA_Subsidiary.dKeepDate AS ddate, IA_Subsidiary.cWhCode AS cWhCode, IA_Subsidiary.cVouType AS cvoutype, IA_Subsidiary.cInvCode AS cinvcode, IA_SubSidiary.cSTCode as cSTCode, Customer.cDCCode as cDCCode ,Customer.cCusHeadCode as cCusHeadCode ,Customer.cCCCode as cCCCode , Inventory.cInvCCode as cInvCCode , IA_Subsidiary.cCusCode AS cCusCode, IA_Subsidiary.cAccDep AS cDepCode, IA_SubSidiary.cDepCode as cAccDep , IA_Subsidiary.cPersonCode AS cPersonCode, IA_Subsidiary.cFree1 AS cFree1, IA_Subsidiary.cFree2 AS cFree2, IA_Subsidiary.iMonth AS iMonth, IA_Subsidiary.bMoneyFlag AS bMoneyFlag, IA_Subsidiary.bSale AS bSale, Warehouse.cWhValueStyle AS cWhValueStyle , IA_Subsidiary.cBillCode AS cBillCode, IA_Subsidiary.ID AS id, IA_Subsidiary.cDLCode AS cDlCode, 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 , case when (cWhvaluestyle=''计划价法''or cWhvalueStyle=''售价法'') and IA_Subsidiary.bMoneyFlag=1 then isnull(IA_Subsidiary.iAOutPrice,0)- isnull(IA_Subsidiary.iDebitDifCost,0) + isnull(IA_Subsidiary.iCreditDifCost,0) else isnull(IA_Subsidiary.iAOutPrice,0 ) end as iAOutPrice , Inventory.bService as bService , ''复核'' as cChecker into tempdb..Temp_SaleCostsec FROM IA_Subsidiary LEFT JOIN Warehouse ON IA_Subsidiary.cWhCode = Warehouse.cWhCode INNER JOIN Customer On IA_Subsidiary.cCusCode = Customer.cCusCode INNER JOIN Inventory On IA_SubSidiary.cInvCode = Inventory.cInvCode WHERE bRdFlag=0 AND (cVouType in (''26'',''27'',''28'',''29'',''32'') or (cVoutype=''21'' and Ia_subsidiary.bSale=1 ) ) '--加入条件 --日期条件 if not @chrKeepdate is null and ltrim(rtrim(@chrkeepdate)) <> '' set @chrsql = @chrsql + ' and ' + ltrim(rtrim( @chrkeepdate )) --部门条件 if not @chrdep is null and ltrim(rtrim(@chrdep)) <> '' set @chrsql = @chrsql + ' and IA_Subsidiary.cAccDep ' + ltrim(rtrim(@chrdep )) --仓库条件 if not @chrwarehouse is null and ltrim(rtrim(@chrwarehouse))<> '' set @chrsql = @chrsql + ' and ia_subsidiary.cWhCode=''' + ltrim(rtrim(@chrwarehouse )) + ''''--其他条件 if not @chrwhere is null and ltrim(rtrim(@chrwhere)) <> '' set @chrsql = @chrsql + ' and ' + ltrim(rtrim(@chrwhere))exec (@chrsql )/*取得成本明细账数据 *//*计算计划价/售价成本明细账*/set @chrCost=(SELECT cValue FROM AccInformation WHERE (cSysID = 'ia') AND (cName = 'cvaluestyle')) if @chrCost ='按部门核算'begin update tempdb..temp_salecostsec set tempdb..temp_salecostsec.iAOutPrice=case when (tempdb..temp_salecostsec.cWhvalueStyle='售价法' ) then tempdb..temp_salecostsec.iAoutPrice*(1 - isnull(ia_summary.iDifRate,0)) else tempdb..temp_salecostsec.iAoutPrice*(1 + isnull(ia_summary.iDifRate,0)) end from tempdb..temp_salecostsec inner join ia_summary on tempdb..temp_salecostsec.iMonth = ia_summary.iMonth and tempdb..temp_salecostsec.cAccDep = ia_summary.cDepCode and tempdb..temp_salecostsec.cInvCode = ia_summary.cInvCode where (tempdb..temp_salecostsec.cWhvaluestyle='计划价法'or tempdb..temp_salecostsec.cWhvalueStyle='售价法') and tempdb..temp_salecostsec.bMoneyFlag=0 and tempdb..temp_salecostsec.bSale<> 1 endelse begin update tempdb..temp_salecostsec set tempdb..temp_salecostsec.iAOutPrice=case when (tempdb..temp_salecostsec.cWhvalueStyle='售价法' ) then tempdb..temp_salecostsec.iAoutPrice*(1 - isnull(ia_summary.iDifRate,0)) else tempdb..temp_salecostsec.iAoutPrice*(1 + isnull(ia_summary.iDifRate,0)) end from tempdb..temp_salecostsec inner join ia_summary on tempdb..temp_salecostsec.iMonth = ia_summary.iMonth and tempdb..temp_salecostsec.cWhCode = ia_summary.cWhCode and tempdb..temp_salecostsec.cInvCode = ia_summary.cInvCode where (tempdb..temp_salecostsec.cWhvaluestyle='计划价法'or tempdb..temp_salecostsec.cWhvalueStyle='售价法') and tempdb..temp_salecostsec.bMoneyFlag=0 and tempdb..temp_salecostsec.bSale<> 1 endif exists(select * from tempdb..sysobjects where name='Temp_SaleInComeCost') drop table tempdb..Temp_SaleinComeCost /* 将收入项目追加到销售明细账,生成临时表 saleincomecost */select AutoID, cWhCode, dDate,cDepCode, cCusCode,cCCCode, cDCCode, cCusHeadCode,cPersonCode,cInvCode,cInvCCode,cFree1,cFree2,iQuantity,iNum,iNatUnitPrice, iNatMoney,iNatTax, iNatSum, iNatDisCount,iNatSum-iNatSum as iAOutPrice ,cSTCode,cVouType,cdefine1,cdefine2,cdefine3,cdefine4,cdefine5,cdefine6,cdefine7,cdefine8,cdefine9,cdefine10,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27into tempdb..temp_saleincomecost from tempdb..temp_saleincome /* 将成本内容追加到销售明细账,不包括调整单 */ /* 2004.12.13 当按存货分组时单价不对，将下面SQL语句中的单价由0改成NULL，这样当对单价取平均值时就不考虑成本了。(成本中的单价为0) 南京用友丁德安 */insert into tempdb..temp_saleincomecost (AutoID, cWhCode, dDate,cDepCode, cCusCode,cCCCode, cDCCode, cCusHeadCode,cPersonCode,cInvCode,cInvCCode,cFree1,cFree2,iQuantity,iNum,iNatUnitPrice, iNatMoney,iNatTax, iNatSum, iNatDisCount,iAOutPrice ,cSTCode,cVouType, cdefine1,cdefine2,cdefine3,cdefine4,cdefine5,cdefine6,cdefine7,cdefine8,cdefine9,cdefine10,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27)select 0, cWhCode, dDate,cDepCode, cCusCode,cCCCode, cDCCode, cCusHeadCode,cPersonCode,cInvCode,cInvCCode,cFree1,cFree2,0,0,NULL, -- 此处0改成NULL 2004.12.13 南京用友丁德安 0,0, 0, 0,iAOutPrice ,cSTCode,cVouType,cdefine1,cdefine2,cdefine3,cdefine4,cdefine5,cdefine6,cdefine7,cdefine8,cdefine9,cdefine10,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27from tempdb..temp_saleCostSec /*将临时表与其他基础信息关联生成销售明细账临时表 */if exists(select * from tempdb..sysobjects where name= @chrtable ) exec ('drop table tempdb..' + @chrTable )set @chrsql = 'Select a.* , a.iNatMoney - a.iAOutPrice as iProfit,inventory.cinvAddCode as cInvAddCode, inventory.cInvName as cinvName ,inventory.cVenCode as cVenCode, inventory.cinvStd as cinvStd , inventory.cinvM_unit as cinvM_unit , inventory.cinvA_unit as cinvA_unit , customerclass.cCCName as cCCName , inventoryclass.cInvCName as cInvCName , inventory.cInvDefine1 as cinvDefine1 , inventory.cinvDefine2 as cinvdefine2 , inventory.cinvdefine3 as cinvdefine3 , customer.cCusname as cCusname , customer.cCusAbbName as cCusAbbName , customer.cCusDefine1 as cCusDefine1 , customer.cCusDefine2 as cCusDefine2, customer.cCusDefine3 as cCusDefine3 , department.cDepname as cDepName , person.cPersonName as cPersonName ,DistrictClass.cDCName as cDCName,0 as iProfittax into tempdb..' + @chrTable + ' from tempdb..temp_saleinComeCost a left join inventory on a.cInvCode=inventory.cInvCode left join inventoryclass on inventory.cinvccode = inventoryClass.cinvccode left join customer on a.cCusCode = Customer.cCusCode left join department on a.cDepCode = Department.cDepCode left join person on a.cPersonCode = Person.cPersonCode left join customerClass on customer.cCCCode = CustomerClass.cCCCodeleft join DistrictClass on customer.cDCCode = DistrictClass.cDCCode 'exec ( @chrsql) /* 执行语句生成销售明细账临时表 */

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。