根据物流系统生成的凭证与物流系统实际的单据进行对帐,以检查出哪些单据的凭证和实际单据金额不符 /**//****** Object: Stored Procedure dbo.stkd_财务_财务物流对帐 Script Date: 2006-12-11 14:55:39 ******/ create proc stkd_财务_财务物流对帐 @period int, @year int, @acc varchar(10) as set 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 t3 where t1.fvoucherid=t2.fvoucherid and t2.faccountid=t3.faccountid and t1.fperiod=@period and t1.fyear=@year and t1.finternalind='industry' and t3.fnumber=@acc group by t1.fvoucherid,t1.fnumber,t2.faccountid,t3.fnumber update t1 set t1.fwamount=t2.famount from #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 famount from icstockbill t1,icstockbillentry t2,t_icitem t3 where t1.finterid=t2.finterid and month(T1.fdate)=@period and year(t1.fdate)=@year and t2.fitemid=t3.fitemid group by t1.fvchinterid,t3.facctid) t2 where t1.fvoucherid=t2.fvchinterid and t1.faccountid=t2.facctid UPDATE #aa set fdiff=famount-fwamount select fnumber as 凭证号,faccountnumber as 科目代码,famount as 凭证金额,fwamount as 单据金额,fdiff as 差异 from #aa order by fnumber drop table #aa GO |
|