金蝶K3 SQL报表系列-供应商科目余额表

创建存储过程：exec sp_get_supply_balance

代码如下：

create procedure [dbo].[sp_get_supply_balance]
as
set nocount on
declare @curYear int
declare @curPeriod int

select @curYear=FValue from t_SystemProfile where Fkey='CurrentYear' and FCategory='GL'
select @curPeriod=FValue from t_SystemProfile where Fkey='CurrentPeriod' and FCategory='GL'


select
k3.FNumber 供应商代码,
k3.FName 供应商名称,
k2.FNumber 科目代码,
k2.FFullName 科目名称,
k.FEndBalance 余额
 from
(

select
r.FAccountID,r.FDetailID,SUM(FEndBalance) as FEndBalance
from 
(
	select 
	u1.FAccountID,u1.FDetailID,u1.FEndBalance*t1.FDC as FEndBalance
	from t_Balance  u1
	inner join t_Account t1 on u1.FAccountID=t1.FAccountID
	where u1.FAccountID in 
	(
		select a.FAccountID from  t_Account a inner join t_ItemDetail b on a.FDetailID=b.FDetailID and b.F8<>0
	)
	and  FYear=@curYear and FPeriod=@curPeriod and u1.FCurrencyID=0 and u1.FDetailID<>0
	union all
	select
	t1.FAccountID,t1.FDetailID,(CASE WHEN t1.FDC=1 THEN 1 ELSE -1 END)*FAmount*t2.FDC  as FEndBalance
	from  t_Voucher u1
	inner join t_VoucherEntry t1 on u1.FVoucherID=t1.FVoucherID
	inner join t_Account t2 on t2.FAccountID=t1.FAccountID
	where t1.FAccountID in 
	(
		select a.FAccountID from  t_Account a inner join t_ItemDetail b on a.FDetailID=b.FDetailID and b.F8<>0
	)
	and u1.FPosted=0
) r
 group by r.FAccountID,r.FDetailID

) k
inner join t_ItemDetail k1 on k1.FDetailID=k.FDetailID
inner join t_Account k2  on k2.FAccountID=k.FAccountID
inner join t_Item k3 on k3.FItemID=k1.F8
order by k3.FNumber,k2.FNumber