|
根據(jù)物流系統(tǒng)生成的憑證與物流系統(tǒng)實(shí)際的單據(jù)進(jìn)行對帳,，以檢查出哪些單據(jù)的憑證和實(shí)際單據(jù)金額不符
  /**//****** Object: Stored Procedure dbo.stkd_財務(wù)_財務(wù)物流對帳 Script Date: 2006-12-11 14:55:39 ******/ create proc stkd_財務(wù)_財務(wù)物流對帳@period int,@year int,@acc varchar(10)asset nocount on create table #aa(fvoucherid int,fnumber int,faccountid int,faccountnumber varchar(10),famount decimal(18,2),fwamount decimal(18,2),fdiff decimal(18,2))insert into #aa(fvoucherid,fnumber,faccountid,faccountnumber,famount)select t1.fvoucherid,t1.fnumber,t2.faccountid,T3.Fnumber,sum(case when t2.fdc=1 then t2.famount when t2.fdc=0 then -t2.famount else 0 end)from t_voucher t1,t_voucherentry t2,t_account t3where t1.fvoucherid=t2.fvoucherid and t2.faccountid=t3.faccountidand t1.fperiod=@period and t1.fyear=@year and t1.finternalind='industry' and t3.fnumber=@accgroup by t1.fvoucherid,t1.fnumber,t2.faccountid,t3.fnumberupdate t1 set t1.fwamount=t2.famountfrom #aa t1,(select t1.fvchinterid,t3.facctid,sum(case when t1.ftrantype in (1,2,3,5,10,40) then t2.famount when t1.ftrantype in (21,24,28,29,43) then -t2.famount else 0 end) as famountfrom icstockbill t1,icstockbillentry t2,t_icitem t3where t1.finterid=t2.finterid and month(T1.fdate)=@period and year(t1.fdate)=@yearand t2.fitemid=t3.fitemid group by t1.fvchinterid,t3.facctid)t2where t1.fvoucherid=t2.fvchinterid and t1.faccountid=t2.facctidUPDATE #aa set fdiff=famount-fwamountselect fnumber as 憑證號,faccountnumber as 科目代碼,famount as 憑證金額,fwamount as 單據(jù)金額,fdiff as 差異 from #aa order by fnumberdrop table #aa GO |
|
|
