当发货单关闭时发货结算勾对表未结数量为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 )