| 解决方案： |
修改存储过程SA_MoveOutIncome，在判断发货单是否已关闭的条件前加上abs，使判断条件考虑红、兰两种发货单后两处显示结果一致。
存储过程SA_MoveOutIncome：
/* 当有退补业务时发货结算勾对表查询无数据
江苏省药材公司
2003.06.27
当发货单关闭时发货结算勾对表未结数量为0
而当退货单关闭时发货结算勾对表未结数量为退货数量
江苏省中医药科技发展总公司
2003.07.18
南京用友维护部 丁德安
*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Sa_MoveOutIncome' and type='P')
DROP PROCEDURE Sa_MoveOutIncome
GO
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
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.iUnitPrice
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 )
--取得发票内容，发票按发货单子表ID汇总 --
print '开票'
if exists(select * from tempdb..sysobjects where name ='po_SaleBillFirst ')
drop table tempdb..po_salebillfirst
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
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
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
INTO tempdb..po_SaleBill
from tempdb..po_salebillfirst Group by Idlsid
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
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
a.iSettleQuantity - isnull(tempdb..po_salebill.js_iQuantity,0)
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
a.iSettleNum - isnull(tempdb..po_salebill.js_inum,0)
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 )
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 )
|