解决方案: |
先不按任何条件分组,查询某货物的销售统计表,将数据记录下来,共有四行数据,其中两行为成本的数据,单价为空,另两行为销售数据,单价相同;再按货物分组查询销售统计表,只有一行数据,与前面记录下来的数据对比,发现单价为未分组的一半,跟踪查询过程,发现当不按任何条件分组时,系统直接查询出单价,未经过加工,此单价应是正确的,但成本一行中单价为空,而当按货物分组时,系统对单价取值时用的是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_MoveSaleDetail
GO
/*
版本: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( /*取得销售统计表存储过程 作者:刘小东 */
@chrtable varchar(200) = null , /*保存销售明细账临时表名称*/
@chrWhere1 varchar(255) =null , /*查询条件名称*/
@chrSaleDate varchar(100) , --开票日期
@chrKeepDate varchar(100), --结算日期
@chrDep varchar(50) , --部门条件
@chrChecker varchar(50) , --审核条件
@chrwarehouse varchar (40) --仓库条件
)
AS
declare @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_SaleIncome
set @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_SaleInCome
FROM 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_SaleCostsec
set @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
end
else
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
end
if 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,
cdefine27
into 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,
cdefine27
from 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.cCCCode
left join DistrictClass on customer.cDCCode = DistrictClass.cDCCode '
exec ( @chrsql) /* 执行语句生成销售明细账临时表 */ |