| 解决方案： |
此为Sa_MoveOutIncome存储过程未考虑到退补业务的特殊性而产生的错误，修改Sa_MoveOutIncome如下即可解决问题：
/*
版本：U821
当有退补业务时发货结算勾对表查询无数据
江苏省药材公司
2003.06.27
当发货单关闭时发货结算勾对表未结数量为0
而当退货单关闭时发货结算勾对表未结数量为退货数量
江苏省中医药科技发展总公司
2003.07.18
南京用友维护部 丁德安
与U821年结补丁： 821发货结算钩对表补丁（修改上年发货数据没有减问题）.sql
相结合
2004.1.31
南京用友维护部 丁德安
*/
CREATE PROCEDURE Sa_MoveOutIncome( -- 取得发货明细账存储过程 作者：刘小东 --
@chrtable varchar(200) = null , --保存发货结算勾兑明细临时表--
@chrWhere1 varchar(255) =null , --查询条件名称--
@chrOutDate varchar(100) = null , -- 发货日期 --
@chrSaleDate varchar(100) = null, -- 开票日期 --
@chrVouchType varchar(100) = null -- 单据类型 --
)
AS
declare @chrSQL varchar(8000)
declare @chrCost varchar(50)
declare @chrWhere varchar(1000)
declare @chrstartDate varchar(50)
declare @chrstartDate1 varchar(50)
declare @chrstartdate2 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 @chrstartDate1 = ' Dispatchlist.dDate>=''' + @chrstartdate + ''''
set @chrstartDate2 = ' and SalebillVouch.dDate>=''' + @chrstartdate + ''''
end
if exists(select * from tempdb..sysobjects where name ='po_DispatchFirst ')
drop table tempdb..po_DispatchFirst
print '发货'
--取得发货单内容 --
--2003.06.27 增加两字段iTB,TBQuantity
--2003.09.03 再增加一字段TBNum
--南京用友维护部 丁德安
--根据补丁增加字段ijcQuantity
set @chrsql = 'Select
Dispatchlist.DLID as DLID ,
Dispatchlist.cDLCode AS cDLCode,
Dispatchlist.cSTCode AS cSTCode ,
Dispatchlist.ddate AS ddate ,
Dispatchlist.cDepCode as cDepCode,
Dispatchlist.cPersonCode as cPersonCode ,
Dispatchlist.cCusCode as cCusCode,
Dispatchlist.iExchRate as iExchRate ,
Dispatchlist.bFirst as bFirst ,
Dispatchlist.cVerifier as cVerifier,
Dispatchlist.cMaker as cMaker,
Dispatchlists.iDlsid as iDlsid,
Dispatchlists.cWhCode as cWHCode ,
Dispatchlists.cInvCode as cInvCode ,
Dispatchlists.iQuantity as iQuantity ,
Dispatchlists.iNum as iNum ,
Dispatchlists.iTaxUnitPrice as iTaxUnitPrice ,
Dispatchlists.iMoney as iMoney ,
Dispatchlists.iTax as iTax ,
Dispatchlists.iSum as iSum ,
Dispatchlists.iDisCount as iDisCount ,
Dispatchlists.iNatUnitPrice as iNatUnitPrice,
Dispatchlists.iNatMoney as iNatMoney ,
Dispatchlists.iNatTax as iNatTax ,
Dispatchlists.iNatSum as iNatSum ,
Dispatchlists.iNatDisCount as iNatDisCount ,
Dispatchlists.cBatch as cBatch ,
Dispatchlists.cFree1 as cFree1,
Dispatchlists.cFree2 as cFree2 ,
Dispatchlists.iTaxRate as iTaxRate ,
Dispatchlists.cDefine22 as cDefine22 ,
Dispatchlists.cDefine23 as cDefine23,
Dispatchlists.cDefine24 as cDefine24,
Dispatchlists.cDefine25 as cDefine25,
Dispatchlists.cDefine26 as cDefine26 ,
Dispatchlists.cDefine27 as cDefine27 ,
Dispatchlist.cVouchtype as cVouchtype,
Dispatchlist.cDefine1 as cDefine1 ,
Dispatchlist.cDefine2 as cDefine2 ,
Dispatchlist.cDefine3 as cDefine3 ,
Dispatchlist.cDefine4 as cDefine4 ,
Dispatchlist.cDefine5 as cDefine5 ,
Dispatchlist.cDefine6 as cDefine6 ,
Dispatchlist.cDefine7 as cDefine7 ,
Dispatchlist.cDefine8 as cDefine8 ,
Dispatchlist.cDefine9 as cDefine9 ,
Dispatchlist.cDefine10 as cDefine10 ,
Dispatchlists.bSettleall as bsettleall ,
Dispatchlists.iSettleQuantity as iSettleQuantity ,
Dispatchlists.iSettleNum as iSettlenum ,
Dispatchlists.iTB as iTB ,
Dispatchlists.TBQuantity as TBQuantity ,
Dispatchlists.TBNum as TBNum ,
Dispatchlists.iUnitPrice ,
isnull(iquantity,0)-isnull(iquantity,0) as ijcQuantity
INTO tempdb..po_DispatchFirst
From Dispatchlists inner join Dispatchlist On DispatchLists.DLID = Dispatchlist.DLID
WHERE ( Dispatchlist.bFirst=1 or ' + @chrstartdate1 + ' ) '
--追加单据类型条件
if not (@chrVouchtype is null ) and ltrim(rtrim(@chrVouchType)) <> ''
set @chrsql = @chrsql + ' and ' + @chrVouchtype
if not (@chrOutDate is null ) and ltrim(rtrim(@chrOutDate)) <> ''
set @chrsql = @chrsql + ' and ' + @chrOutDate
if not (@chrwhere is null ) and ltrim(rtrim(@chrwhere)) <> ''
set @chrsql = @chrsql + ' and ' + @chrWhere
exec (@chrsql )
-----------------------------------------------------------------------------------------------------------
--期初发货单去掉上年已经结算的数据 （补丁）
set @chrsql ='update tempdb..po_DispatchFirst set ijcQuantity= isnull( iSettleQuantity,0)-
isnull ( (select sum(iQuantity) from Salebillvouchs inner join salebillvouch
on salebillvouchs.sbvid = salebillvouch.sbvid
where salebillvouchs.idlsid =tempdb..po_DispatchFirst.idlsid and isnull(Salebillvouch.cInvalider,'''')='''' ) ,0)
where tempdb..po_DispatchFirst.bfirst =1 '
exec(@chrsql)
--by zzg
set @chrsql ='update tempdb..po_DispatchFirst set ijcQuantity= iQuantity
where iQuantity *( iQuantity - ijcQuantity)<0'
-- 2004.3.5
-- 南京用友维护部
-- 南京蓝光燃气
-- 当期初发货单的结算数量>发货数量时数据不显示，故注释掉下行语句
--exec(@chrsql)
--by zzg
set @chrsql ='update tempdb..po_DispatchFirst set iQuantity = iQuantity - ijcQuantity ,
iNum = convert(decimal(20,2),iNum * (iQuantity - ijcQuantity ) / iQuantity) ,
iMoney = convert(decimal(20,2),iMoney * (iQuantity - ijcQuantity ) / iQuantity) ,
iTax = convert(decimal(20,2),iTax * (iQuantity - ijcQuantity ) / iQuantity) ,
iSum = convert(decimal(20,2),iMoney * (iQuantity - ijcQuantity ) / iQuantity)+convert(decimal(20,2),iTax * (iQuantity - ijcQuantity ) / iQuantity) ,
iDiscount = convert(decimal(20,2),iDiscount * (iQuantity - ijcQuantity ) / iQuantity) ,
iNatMoney = convert(decimal(20,2),iNatMoney * (iQuantity - ijcQuantity ) / iQuantity) ,
iNatTax = convert(decimal(20,2),iNatTax * (iQuantity - ijcQuantity ) / iQuantity) ,
iNatSum = convert(decimal(20,2),iNatMoney * (iQuantity - ijcQuantity ) / iQuantity)+convert(decimal(20,2),iNatTax * (iQuantity - ijcQuantity ) / iQuantity) ,
iNatDiscount = convert(decimal(20,2),iNatDiscount * (iQuantity - ijcQuantity ) / iQuantity)
where ijcQuantity <> 0 and iQuantity <>0 '
exec(@chrsql)
set @chrsql='create index ix_idlsid on tempdb..po_dispatchfirst (idlsid)'
exec(@chrsql)
-----------------------------------------------------------------------------------------------------------
--取得发票内容，发票按发货单子表ID汇总 --
print '开票'
if exists(select * from tempdb..sysobjects where name ='po_SaleBillFirst ')
drop table tempdb..po_salebillfirst
--考虑退补问题
--2003.09.03 增加三字段iTB,TBQuantity,TBNum
--南京用友维护部 丁德安
set @chrsql = 'Select
SaleBillVouchs.iDLSID as iDLSID ,
SaleBillVouchs.iQuantity as iQuantity ,
SaleBillVouchs.iNum as iNum ,
SaleBillVouchs.iMoney as iMoney ,
SaleBillVouchs.iSum as iSum ,
SaleBillVouchs.iTax as iTax ,
SaleBillVouchs.iNatMoney as iNatMoney ,
SaleBillVouchs.iNatSum as iNatSum ,
SaleBillVouchs.iNatTax as iNatTax ,
SaleBillVouchs.TBQuantity as TBQuantity ,
SaleBillVouchs.TBNum as TBNum,
SaleBillVouchs.iTB as iTB
INTO tempdb..po_SaleBillfirst
From SaleBillVouchs inner join SaleBillVouch On SalebillVouchs.SBVID = SalebillVouch.SBVID
inner join tempdb..po_dispatchfirst on tempdb..po_dispatchfirst.idlsid = Salebillvouchs.idlsid
Where Salebillvouchs.idlsid <> 0 and Salebillvouchs.idlsid is not null
and isnull(Salebillvouch.cInvalider,'''')=''''
and isnull(Salebillvouch.cSTCode,'''') <> ''''
' + @chrstartdate2 --作废发票不能计算在内,开票日期必须大于系统起用日期 --
if not (@chrSaleDate is null ) and ltrim(rtrim(@chrSaleDate)) <> ''
set @chrsql = @chrsql + ' and ' + @chrSaleDate
--不要其它条件
--if not (@chrwhere is null ) and ltrim(rtrim(@chrwhere)) <> '' set @chrsql = @chrsql + ' and ' + @chrwhere
exec( @chrsql )
-- 发票内容按发货单子表ID号合计--
if exists(select * from tempdb..sysobjects where name ='po_SaleBill') Drop Table tempdb..po_salebill
-- 考虑退补问题
-- 增加三字段iTB,TBQuantity,TBNum
-- 2003.09.03
-- 南京用友维护部 丁德安
Select iDlsid ,
Sum( iQuantity ) as js_iQuantity,
Sum( iNum ) as js_iNum ,
Sum( iMoney ) as js_iMoney ,
Sum( iSum ) as js_iSum ,
Sum( iTax ) as js_iTax,
Sum( iNatMoney ) as js_iNatMoney,
Sum( iNatSum ) as js_iNatSum ,
Sum( iNatTax ) as js_iNatTax,
Sum( TBQuantity ) as js_TBQuantity,
Sum( TBNum ) as js_TBNum,
iTB
INTO tempdb..po_SaleBill
from tempdb..po_salebillfirst Group by iDLsID,iTB
--加索引 （补丁）
set @chrsql='create index ix_idlsid on tempdb..po_salebill (idlsid)'
exec (@chrsql)
if exists( select * from tempdb..sysobjects where name='po_DispSale') Drop table tempdb..po_DispSale
-- 关联发货单和结算单 --
-- ye_iNatSum,ye_iNatTax 考虑退补问题
-- 2003.06.27
-- 退货单关闭时问题，
-- case when a.bsettleall = 1 and ( a.iQuantity -a.iSettleQuantity ) > 0 then
-- 改成 case when a.bsettleall = 1 and abs( a.iQuantity -a.iSettleQuantity ) > 0 then
-- 2003.07.18
-- ye_iQuantity,ye_iNum 考虑退补问题
-- 2003.09.03
-- 南京用友维护部 丁德安
set @chrsql = 'Select
a.DLID as DLID ,
a.cDLCode AS cDLCode,
a.cSTCode AS cSTCode ,
a.ddate AS ddate ,
a.cDepCode as cDepCode,
a.cPersonCode as cPersonCode ,
a.cCusCode as cCusCode,
a.iExchRate as iExchRate ,
a.bFirst as bFirst ,
a.cVerifier as cVerifier,
a.cMaker as cMaker,
a.cWhCode as cWHCode ,
a.cInvCode as cInvCode ,
a.iQuantity as iQuantity ,
a.iNum as iNum ,
a.iTaxUnitPrice as iTaxUnitPrice ,
a.iMoney as iMoney ,
a.iTax as iTax ,
a.iSum as iSum ,
a.iDisCount as iDisCount ,
a.iNatUnitPrice as iNatUnitPrice,
a.iNatMoney as iNatMoney ,
a.iNatTax as iNatTax ,
a.iNatSum as iNatSum ,
a.iNatDisCount as iNatDisCount ,
a.cBatch as cBatch ,
a.cFree1 as cFree1,
a.cFree2 as cFree2 ,
a.iTaxRate as iTaxRate ,
a.cDefine22 as cDefine22 ,
a.cDefine23 as cDefine23,
a.cDefine24 as cDefine24,
a.cDefine25 as cDefine25,
a.cDefine26 as cDefine26 ,
a.cDefine27 as cDefine27 ,
a.cVouchtype as cVouchtype ,
tempdb..po_SaleBill.js_iQuantity as js_iQuantity,
tempdb..po_SaleBill.js_iNum as js_iNum ,
tempdb..po_SaleBill.js_iMoney as js_iMoney ,
tempdb..po_SaleBill.js_iSum as js_iSum ,
tempdb..po_SaleBill.js_iTax as js_iTax,
tempdb..po_SaleBill.js_iNatMoney as js_iNatMoney,
tempdb..po_SaleBill.js_iNatSum as js_iNatSum ,
tempdb..po_SaleBill.js_iNatTax as js_iNatTax ,
case when a.bsettleall = 1 and abs( a.iQuantity -a.iSettleQuantity ) > 0 then
case when a.iTB=1 then
a.iSettleQuantity - isnull(tempdb..po_salebill.js_TBQuantity,0)
else
a.iSettleQuantity - isnull(tempdb..po_salebill.js_iQuantity,0)
end
else
a.iQuantity - isnull(tempdb..po_salebill.js_iQuantity,0)
end
as ye_iQuantity,
case when a.bSettleall =1 and abs( a.iQuantity -a.iSettleQuantity ) > 0 then
case when a.iTB=1 then
a.iSettleNum - isnull(tempdb..po_salebill.js_TBnum,0)
else
a.iSettleNum - isnull(tempdb..po_salebill.js_inum,0)
end
else
a.iNum - isnull(tempdb..po_salebill.js_inum,0 )
end
as ye_iNum ,
case when a.bSettleall =1 and abs( a.iQuantity - a.iSettleQuantity ) > 0 then
iUnitPrice * a.iSettleQuantity - isnull( tempdb..po_salebill.js_iMoney,0 )
else
a.iMoney - isnull(tempdb..po_salebill.js_iMoney, 0 )
end as ye_iMoney ,
case when a.bSettleall =1 and abs( a.iQuantity - a.iSettleQuantity ) > 0 then
a.iTaxUnitPrice * a.iSettleQuantity - isnull( tempdb..po_salebill.js_isum , 0 )
else
a.isum - isnull(tempdb..po_salebill.js_isum , 0 )
end
as ye_iSum ,
case when a.bSettleall =1 and abs( a.iQuantity - a.iSettleQuantity ) > 0 then
( a.iTaxUnitPrice - a.iUnitPrice) * a.iSettleQuantity
- isnull (tempdb..po_salebill.js_itax, 0 )
else
a.iTax - isnull(tempdb..po_salebill.js_iTax,0 )
end
as ye_iTax,
case when a.bSettleall =1 and abs( a.iQuantity - a.iSettleQuantity ) > 0 then
a.iNatUnitPrice * a.iSettleQuantity - isnull( tempdb..po_salebill.js_iNatMoney,0 )
else
a.iNatMoney - isnull(tempdb..po_salebill.js_iNatMoney,0)
end
as ye_iNatMoney,
case when a.bSettleall =1 and abs( a.iQuantity - a.iSettleQuantity ) > 0 then
case when a.iTB=1 then
(a.iNatSum / a.TBQuantity) * a.iSettleQuantity - isnull( tempdb..po_salebill.js_iNatSum,0 )
else
(a.iNatSum / a.iQuantity) * a.iSettleQuantity - isnull( tempdb..po_salebill.js_iNatSum,0 )
end
else
a.iNatSum - isnull(tempdb..po_salebill.js_iNatSum,0 )
end as ye_iNatSum ,
case when a.bSettleall =1 and abs( a.iQuantity - a.iSettleQuantity ) > 0 then
case when a.iTB=1 then
((a.iNatSum - a.iNatMoney) / a.TBQuantity) * a.iSettleQuantity
- isnull(tempdb..po_salebill.js_iNatTax, 0 )
else
((a.iNatSum - a.iNatMoney) / a.iQuantity) * a.iSettleQuantity
- isnull(tempdb..po_salebill.js_iNatTax, 0 )
end
else
a.iNatTax - isnull(tempdb..po_salebill.js_iNatTax, 0 )
end as ye_iNatTax ,
a.cDefine1 ,a.cDefine2 , a.cDefine3 ,a.cDefine4 ,a.cDefine5 ,
a.cDefine6 ,a.cDefine7 ,a.cDefine8 ,a.cDefine9 ,a.cDefine10
into tempdb..po_DispSale
from tempdb..po_dispatchfirst a left join tempdb..po_salebill
On a.idlsid = tempdb..po_salebill.iDLSID '
exec (@chrsql )
-----------------------------------------------------------------------------------------------------------
--by zzg （补丁）
set @chrsql='update tempdb..po_DispSale set ye_iQuantity=0,ye_iNum=0,ye_iMoney=0,ye_iSum=0,
ye_iTax=0,ye_iNatMoney=0,ye_iNatSum=0,ye_iNatTax=0
where isnull(tempdb..po_DispSale.iquantity,0)* isnull(tempdb..po_DispSale.ye_iQuantity,0)<0
or (iquantity=0 and isnull(tempdb..po_DispSale.iSum,0)* isnull(tempdb..po_DispSale.ye_iSum,0)<0)'
exec (@chrsql )
set @chrsql='update tempdb..po_DispSale set ye_iMoney=0,
ye_iTax=0,ye_iNatMoney=0,ye_iNatSum=0,ye_iNatTax=0
where isnull(ye_iSum,0)=0'
exec (@chrsql )
-----------------------------------------------------------------------------------------------------------
print '结果'
-- 生成最终的明细账临时表 --
if exists(select * from tempdb..sysobjects where name =@chrtable ) exec('drop table tempdb..' + @chrtable )
set @chrsql = 'Select tempdb..po_DispSale.* ,
inventory.cInvName as cInvName ,
inventory.cInvStd as cInvStd ,
inventory.cInvM_Unit as cInvM_Unit ,
inventory.cinvCCode as cInvCCode ,
Customer.cCusName as cCusName ,
Customer.cCCCode as cCCCode ,
SaleType.cSTName as cSTName,
Department.cDepName as cDepName ,
Person.cPersonName as cPersonname ,
WareHouse.cWHName as cWHName ,
Customer.cCusAbbName as cCusAbbName ,
Vouchtype.cVouchName as cVouchName
into tempdb..'+ @chrtable + ' From tempdb..po_Dispsale
left join inventory on tempdb..po_dispsale.cInvCode = inventory.cInvCode
left join customer on tempdb..po_dispsale.cCusCode = Customer.cCusCode
left join saletype on tempdb..po_dispsale.cSTCode = SaleType.cSTCode
left join department on tempdb..po_dispsale.cDepCode = Department.cDepCode
left join person on tempdb..po_dispsale.cPersonCode = person.cPersonCode
left join warehouse on tempdb..po_dispsale.cWhCode = warehouse.cWhCode
left join Vouchtype on tempdb..po_dispsale.cVouchtype = Vouchtype.cVouchtype
order by tempdb..po_dispsale.ddate,tempdb..po_dispsale.dlid '
exec ( @chrsql )
|