| 解决方案： |
查询其他账表并与此两张表对比，发现销售毛利分析中数据是正确的，而进销存统计表数据不对。用SQL跟踪发现进销存统计表是调用存储过程Sa_moveSRMDetail进行数据查询的，仔细分析Sa_moveSRMDetail存储过程，发现其日期过滤条件的判断为大于起始日期，小于等于结束日期，也就是起始日期这一天给舍弃了，导致统计数据不对，改成大于等于起始日期后查询正确。
if exists(select * from sysobjects where name='sa_MoveSRMDetail' and type='P')
drop PROCEDURE sa_MoveSRMDetail
go
-- U821版
-- 2003.12.5
-- 南京用友 丁德安
-- 销售系统中进销存统计表的销售数量、销售收入不等于销售毛利分析中的本期数量和售价影响
-- 经分析后发现是起始日期判断条件为'>'，应为'>='
CREATE PROCEDURE sa_MoveSRMDetail (
@chrtablename varchar (50) = null ,
@chrWhere varchar (255 ) = null ,
@chrStartDate varchar(20) = null ,
@chrendDate varchar(20) = null ,
@chrChecker varchar(40) = null ,
@chrDep varchar(50) = null ,
@chrWareHouse varchar(20)
)
AS
declare @chrsql varchar(4000)
declare @chrCost varchar(40)
declare @chrSaleDate varchar (50)
-- 删除临时表
if exists (select name from tempdb..sysobjects where name ='tmp_srm') drop table tempdb..tmp_srm --删除进销存明细账
--1 组织期初数据
--1.1 计算所有发出成本
if exists (select name from tempdb..sysobjects where name ='tmp_srmcost' ) drop table tempdb..tmp_srmcost --删除成本明细
--从成本明细账中取得销售成本和其他出库成本
set @chrsql= ' Select dkeepdate as ddate ,
cVouType AS cvoutype,
cInvcode, cAccDep ,
ia_subsidiary.cDepCode,
bMoneyFlag, bSale, iMonth ,
Warehouse.cWhValueStyle AS cWhValueStyle ,
ia_subsidiary.cWhcode , iAOutQuantity ,
case when (cWhvaluestyle=''计划价法''or cWhvalueStyle=''售价法'') and bMoneyFlag=1 then
isnull(iAOutPrice,0)- isnull(iDebitDifCost,0) + isnull(iCreditDifCost,0)
else
isnull ( iAOutPrice,0 )
end
as iAOutPrice
into tempdb..tmp_srmcost from ia_subsidiary
inner join warehouse on ia_subsidiary.cwhcode = warehouse.cwhcode
where brdflag = 0 '
if ltrim(rtrim(@chrwhere )) <> '' and not ( @chrwhere is null ) set @chrsql = @chrsql + ' and ' + @chrwhere
if ltrim(rtrim(@chrdep )) <> '' and not ( @chrdep is null ) set @chrsql = @chrsql + ' and cAccdep ' + @chrdep
if ltrim(rtrim(@chrenddate )) <> '' and not (@chrenddate is null ) set @chrsql = @chrsql + ' and dkeepdate<='''+ @chrenddate +''''
if ltrim(rtrim(@chrWareHouse)) <> '' and not (@chrWareHouse is null ) set @chrsql = @chrsql +' and ia_subsidiary.cwhcode='''+ ltrim(rtrim(@chrWareHouse)) +''''
exec (@chrsql )
--计算非自填成本
set @chrCost=(SELECT cValue FROM AccInformation WHERE (cSysID = 'ia') AND (cName = 'cvaluestyle'))
if @chrCost ='按部门核算'
begin
update tempdb..tmp_srmcost set tempdb..tmp_srmcost.iAOutPrice=
case when (tempdb..tmp_srmcost.cWhvalueStyle='售价法' ) then
tempdb..tmp_srmcost.iAoutPrice*(1 - isnull(ia_summary.iDifRate,0))
else
tempdb..tmp_srmcost.iAoutPrice*(1 + isnull(ia_summary.iDifRate,0))
end
from tempdb..tmp_srmcost inner join ia_summary
on tempdb..tmp_srmcost.iMonth = ia_summary.iMonth and
tempdb..tmp_srmcost.cDepCode = ia_summary.cDepCode and
tempdb..tmp_srmcost.cInvCode = ia_summary.cInvCode
where (tempdb..tmp_srmcost.cWhvaluestyle='计划价法'or tempdb..tmp_srmcost.cWhvalueStyle='售价法')
and tempdb..tmp_srmcost.bMoneyFlag=0 and tempdb..tmp_srmcost.bSale<> 1
end
else
begin
update tempdb..tmp_srmcost set tempdb..tmp_srmcost.iAOutPrice=
case when (tempdb..tmp_srmcost.cWhvalueStyle='售价法' ) then
tempdb..tmp_srmcost.iAoutPrice*(1 - isnull(ia_summary.iDifRate,0))
else
tempdb..tmp_srmcost.iAoutPrice*(1 + isnull(ia_summary.iDifRate,0))
end
from tempdb..tmp_srmcost inner join ia_summary
on tempdb..tmp_srmcost.iMonth = ia_summary.iMonth and
tempdb..tmp_srmcost.cWhCode = ia_summary.cWhCode and
tempdb..tmp_srmcost.cInvCode = ia_summary.cInvCode
where (tempdb..tmp_srmcost.cWhvaluestyle='计划价法'or tempdb..tmp_srmcost.cWhvalueStyle='售价法')
and tempdb..tmp_srmcost.bMoneyFlag=0 and tempdb..tmp_srmcost.bSale<> 1
end
--完成成本计算所有的出库成本保存在临时文件 tempdb..tmp_srccost中
--1.3计算期初入库金额
--创建临时表tmp_srm保存查询统计的临时结果
--临时表结构 存货编号 ，部门编号 ，期初数量，期初金额，采购数量，采购金额
-- 暂诂数量，暂诂金额，其他入库数量，其他入库金额，销售数量
-- 销售金额，其他出库数量，其他出库金额，调拨数量，调拨金额
-- 收入金额，出库数量，成本
set @chrsql = 'Select cInvCode , caccdep ,
iAinQuantity as qc_iQuantity ,
(iAinPrice + isnull( iDebitDifCost,0) -isnull(iCreditDifCost,0) ) as qc_imoney ,
(iAinPrice - iAinPrice) as iQuantityCG ,
(iAinPrice - iAinPrice) as iMoneyCG ,
(iAinPrice - iAinPrice) as iQuantityZG,
(iAinPrice - iAinPrice) as iMoneyZG ,
(iAinPrice - iAinPrice) as iQuantityQTRK ,
(iAinPrice - iAinPrice) as iMoneyQTRK ,
(iAinPrice - iAinPrice) as iQuantityXS ,
(iAinPrice - iAinPrice) as iMoneyXS ,
(iAinPrice - iAinPrice) as iQuantityQTCK ,
(iAinPrice - iAinPrice) as iMoneyQTCK ,
(iAinPrice - iAinPrice) as iQuantityDB ,
(iAinPrice - iAinPrice) as iMoneyDB ,
(iAinPrice - iAinPrice) as iMoneySR ,
(iAinPrice - iAinPrice) as iQuantityCK ,
(iAinPrice - iAinPrice) as iMoneyCB ,
cwhcode
into tempdb..tmp_srm
from ia_subsidiary '
if ltrim(rtrim(@chrstartdate)) = '' or @chrstartdate is null
begin --无期初日期时只计算上年结转
set @chrsql = @chrsql + ' where iMonth=0 and brdflag = 1 and cVoutype <>''33'' '
end
else
begin --有期初日期时的情况
set @chrsql = @chrsql + ' where brdflag = 1 and ( (iMonth = 0 and cVoutype <>''33'' ) or dkeepdate <='''
+@chrstartdate +''' and ( cVoutype in (''01'',''24'',''30'',''08'',''20'',''22'',''10'') or (cvoutype =''20'' and cbustype=''暂估报销'' ) ) ) '
end --加入日期和部门条件
if ltrim(rtrim(@chrwhere )) <> '' and not (@chrwhere is null )
set @chrsql = @chrsql + ' and ' + @chrwhere
if ltrim(rtrim(@chrdep )) <> '' and not ( @chrdep is null ) set @chrsql = @chrsql + ' and cAccdep ' + @chrdep
--加入仓库条件
if ltrim(rtrim(@chrWareHouse)) <> '' and not (@chrWareHouse is null ) set @chrsql = @chrsql +' and ia_subsidiary.cwhcode='''+ ltrim(rtrim(@chrWareHouse)) +''''
exec ( @chrsql )
--1.2 生成期初出库成本
if ltrim(rtrim(@chrstartdate)) <> '' and not ( @chrstartdate is null )
begin --如果无期初日期不应该计算期初发出内容
set @chrsql = 'insert into tempdb..tmp_srm
Select cInvCode , caccdep ,
- iAoutQuantity as qc_iQuantity ,
- iAoutPrice as qc_imoney ,
0 as iQuantityCG , 0 as iMoneyCG ,
0 as iQuantityZG, 0 as iMoneyZG ,
0 as iQuantityQTRK , 0 as iMoneyQTRK ,
0 as iQuantityXS , 0 as iMoneyXS ,
0 as iQuantityQTCK , 0 as iMoneyQTCK ,
0 as iQuantityDB , 0 as iMoneyDB ,
0 as iMoneySR , 0 as iQuantityCK ,
0 as iMoneyCB , cWhcode
from tempdb..tmp_srmcost where cvoutype in (''09'',''11'',''21'',''26'',''27'',''28'',''29'',''32'') '
set @chrsql = @chrsql + ' and ddate <=''' + @chrstartdate + ''''
exec(@chrsql )
end --完成期初数据的准备
--2 组织本期数据
--2.1 统计本期采购入库数据：采购入库数量，采购入库金额，暂诂入库数量，暂诂入库金额，其它入库数量，其他入库金额
set @chrsql = 'insert into tempdb..tmp_srm
Select cInvCode , caccdep ,
0 as qc_iQuantity ,
0 as qc_imoney ,
case when cvoutype in(''01'',''24'',''30'') or (cvoutype =''20'' and cbustype=''暂估报销'' ) then
iAinQuantity
else
0
end iQuantityCG ,
case when cvoutype in(''01'',''24'',''30'') or (cvoutype =''20'' and cbustype=''暂估报销'' ) then
iAinPrice + isnull( iDebitDifCost,0) -isnull(iCreditDifCost,0)
else
0
end as iMoneyCG ,
case when ( cvoutype = ''01'' and bflag = 1 ) or cvoutype in(''24'',''30'') or (cvoutype =''20'' and cbustype=''暂估报销'' ) then
iAinQuantity
else
0
end as iQuantityZG,
case when ( cvoutype = ''01'' and bflag = 1 ) or cvoutype in(''24'',''30'') or (cvoutype =''20'' and cbustype=''暂估报销'' ) then
iAinPrice + isnull( iDebitDifCost,0) -isnull(iCreditDifCost,0)
else
0
end as iMoneyZG ,
case when cVoutype in (''08'',''20'',''22'',''10'') then
iAinQuantity
else
0
end as iQuantityQTRK ,
case when cVoutype in (''08'',''20'',''22'',''10'') then
iAinPrice + isnull( iDebitDifCost,0) -isnull(iCreditDifCost,0)
else
0
end as iMoneyQTRK ,
0 as iQuantityXS , 0 as iMoneyXS ,
0 as iQuantityQTCK , 0 as iMoneyQTCK ,
0 as iQuantityDB , 0 as iMoneyDB ,
0 as iMoneySR , 0 as iQuantityCK ,
0 as iMoneyCB , cWhcode
from ia_subsidiary
Where bRdflag = 1 and iMonth > 0 and
( cVoutype in (''01'',''24'',''30'',''08'',''20'',''22'',''10'') or (cvoutype =''20'' and cbustype=''暂估报销'' ) ) '
--追加开始日期条件
if ltrim(rtrim(@chrstartdate)) <> '' and not ( @chrstartdate is null )
set @chrsql = @chrsql + ' and dkeepdate>''' + @chrstartdate + ''''
--追加结束日期条件
if ltrim(rtrim(@chrenddate)) <> ''and not ( @chrenddate is null )
set @chrsql = @chrsql + ' and dkeepdate<=''' + @chrenddate + ''''
--追加其他条件
if ltrim(rtrim(@chrwhere)) <> '' and not @chrwhere is null
set @chrsql = @chrsql + ' and ' + @chrwhere
if ltrim(rtrim(@chrdep )) <> '' and not ( @chrdep is null ) set @chrsql = @chrsql + ' and cAccdep ' + @chrdep
--加入仓库条件
if ltrim(rtrim(@chrWareHouse)) <> '' and not (@chrWareHouse is null ) set @chrsql = @chrsql +' and ia_subsidiary.cwhcode='''+ ltrim(rtrim(@chrWareHouse)) +''''
exec(@chrsql ) --end 本期采购部分计算结束
--2.3 统计本期销售数量，销售金额，销售调拨数量，销售调拨金额
-- 取得销售系统启用日期
set @chrsaledate = (select isnull(cValue,'1900-01-01') from accinformation where cSysid='Sa' and cName='dStartDate' )
if @chrsaledate <> ''
begin
if len(ltrim(rtrim(@chrsaledate))) <= 8 set @chrsaledate = convert(varchar(10),convert(smalldatetime,@chrsaledate,2),121)
set @chrsaleDate = ' and SalebillVouch.dDate>=''' + @chrsaledate + ''''
end
--计算本期销售数据
set @chrsql = 'insert into tempdb..tmp_srm
Select cInvCode , cdepcode as caccdep ,
0 as qc_iQuantity , 0 as qc_imoney ,
0 as iQuantityCG , 0 as iMoneyCG ,
0 as iQuantityZG, 0 as iMoneyZG ,
0 as iQuantityQTRK , 0 as iMoneyQTRK ,
iQuantity as iQuantityXS ,
iNatSum as iMoneyXS ,
0 as iQuantityQTCK , 0 as iMoneyQTCK ,
case when cvouchtype = ''28'' then
iQuantity
else
0
end as iQuantityDB ,
case when cvouchtype =''28'' then
iNatMoney
else
0
end as iMoneyDB ,
iNatMoney as iMoneySR , 0 as iQuantityCK ,
0 as iMoneyCB , Salebillvouchs.cWhcode
From Salebillvouchs inner join Salebillvouch
on salebillvouchs.sbvid = salebillvouch.sbvid
where isnull(Salebillvouch.cinvalider ,'''')='''' ' + @chrsaledate
--增加审核条件
if ltrim(rtrim(@chrchecker)) <> '' and not (@chrchecker is null )
set @chrsql = @chrsql + ' and ' + @chrchecker
--增加开始日期条件
if ltrim(rtrim(@chrstartdate)) <> '' and not (@chrstartdate is null )
-- 2003.12.5 丁德安
-- set @chrsql = @chrsql + ' and ddate >''' + @chrstartdate + ''''
set @chrsql = @chrsql + ' and ddate >=''' + @chrstartdate + ''''
--增加结束日期条件
if ltrim(rtrim(@chrenddate )) <> '' and not (@chrenddate is null )
set @chrsql = @chrsql + ' and ddate <=''' + @chrenddate + ''''
--增加一般条件
if ltrim(rtrim(@chrwhere)) <> '' and not (@chrwhere is null )
set @chrsql = @chrsql + ' and ' + @chrwhere
if ltrim(rtrim(@chrdep )) <> '' and not ( @chrdep is null ) set @chrsql = @chrsql + ' and cdepCode ' + @chrdep
--加入仓库条件
if ltrim(rtrim(@chrWareHouse)) <> '' and not (@chrWareHouse is null ) set @chrsql = @chrsql +' and Salebillvouchs.cwhcode='''+ ltrim(rtrim(@chrWareHouse)) +''''
exec (@chrsql ) --本期收入计算终止
--2.4 统计本期销售成本，销售收入，出库数量
set @chrsql = 'insert into tempdb..tmp_srm
Select cInvCode , caccdep ,
0 as qc_iQuantity , 0 as qc_imoney ,
0 as iQuantityCG , 0 as iMoneyCG ,
0 as iQuantityZG, 0 as iMoneyZG ,
0 as iQuantityQTRK , 0 as iMoneyQTRK ,
0 as iQuantityXS , 0 as iMoneyXS ,
case when cvoutype =''09'' or cvoutype =''11'' or (cvoutype = ''21'' and bSale = 0 ) then
iAoutQuantity
else
0
end as iQuantityQTCK ,
case when cvoutype =''09'' or cvoutype =''11'' or (cVoutype =''21'' and bSale = 0 ) then
iAoutPrice
else
0
end as iMoneyQTCK ,
0 as iQuantityDB , 0 as iMoneyDB , 0 as iMoneySR ,
case when cvoutype in (''26'',''27'',''28'',''29'',''32'') or ( cvoutype =''21'' and bsale=1) then
iAoutQuantity
else
0
end as iQuantityCK ,
case when cvoutype in (''26'',''27'',''28'',''29'',''32'') or ( cvoutype =''21'' and bsale=1) then
iAoutPrice
else
0
end as iMoneyCB , cwhcode
from tempdb..tmp_srmcost Where iMonth > 0 and cvoutype in (''09'',''11'',''21'',''26'',''27'',''28'',''29'',''32'') '
if ltrim(rtrim(@chrstartdate)) <> '' and not (@chrstartdate is null )
set @chrsql = @chrsql + ' and ddate >'''+ @chrstartdate + ''''
exec( @chrsql )
--3 按存货分类展开处理
/*存货分类按编码级次展开 */
declare @chrfieldlist varchar(4000)
declare @chrInner varchar (4000)
declare @chrbmjc varchar(20)
declare @i int
declare @n int
/*存货分类按编码级次展开 */
/*取得存货分类编码级次 */
set @chrbmjc = ''
set @chrbmjc = (select cValue from accInformation where cName ='cGoodClass')
/*连接展开字符串*/
set @chrfieldlist = ''
set @chrInner = ''
set @i =1
set @n = 0
while (@i <= 8 )
begin
set @n = @n + substring(@chrbmjc,@i,1) /* 分解级次 */
/* 组合字段列表 */
set @chrfieldlist = @chrfieldlist +'c'+ ltrim(rtrim(str(@i)))
+ '.cInvCName as cInvCName' + ltrim(rtrim(@i)) +','
/* 组合关联 */
if (@i <= len(@chrbmjc))
begin
set @chrinner = @chrinner + ' left join inventoryClass c'
+ ltrim(rtrim(str(@i)))
+ ' on c'+ ltrim(rtrim(str(@i)))
+'.cInvCCode = left( b.cInvCCode,'
+ltrim(rtrim(str(@n)))+')'
end
else
begin
set @chrinner = @chrinner +
' left join inventoryClass c'
+ ltrim(rtrim(str(@i)))
+ ' on b.cInvCCode = c'
+ ltrim(rtrim(str(@i)))+ '.cInvCCode '
end
set @i = @i +1
end
--4 生成最终的临时表
if exists(select name from tempdb..sysobjects where name = @chrtablename ) exec ('drop table tempdb..' + @chrtablename )
set @chrsql = 'Select a.* ,
Department.cDepName ,
b.cinvname , b.cinvStd ,
b.cinvM_Unit ,'+@chrfieldlist +'
b.cInvDefine1 , b.cInvDefine2 ,
b.cInvDefine3 , 0 as iProfit ,
0 as iProfittax, 0 as qm_iQuantity ,
0 as qm_iMoney , 0 as iPrice , 0 as iPriceTax ,
vendor.cvenname , warehouse.cWhname
into tempdb..'+@chrtablename+'
from tempdb..tmp_srm a
left join inventory b on a.cInvCode = b.cInvCode
left join Department on a.caccdep = department.cDepcode
left join vendor on b.cvencode = vendor.cvencode
left join warehouse on a.cwhcode = warehouse.cwhcode '
+ @chrInner
exec(@chrsql) |