金蝶K3 SQL报表系列-供应商应付/预付账款明细表

最新推荐文章于 2022-11-08 19:11:19 发布

宝贝啊！

最新推荐文章于 2022-11-08 19:11:19 发布

阅读量1.9k

点赞数

原文链接：https://blog.csdn.net/hzfw2008/article/details/77149226

版权

、创建供应商应付预付账款明细总表存储过程：sp_getAccount_detail，代码如下：

create procedure [dbo].[sp_getAccount_detail]
@StartYear int,
@StartPeriod int,
@EndYear int,
@EndPeriod int,
@FAcctNumber nvarchar(20),
@FItemNumber nvarchar(200)
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'
create table #report_sum
(
FID int identity(1,1),
FSuppID int,
FAcctountID int,
FDC int,
FIndex int,
FYear int,
FPeriod int,
FDate datetime,
FTrnasDate datetime,
FVoucherID int,
FExplanation nvarchar(1000),
FDebit decimal(21,10),
FCredit decimal(21,10),
FEndBalance decimal(21,10)
)
insert into #report_sum
(FSuppID,FAcctountID,FDC,FIndex,FYear,FPeriod,FEndBalance)
select
F8,u1.FAccountID,t3.FDC,-1,u1.FYear,u1.FPeriod,u1.FBeginBalance*t3.FDC
from t_Balance u1
inner join t_ItemDetail t1 on u1.FDetailID=t1.FDetailID
inner join t_Item t2 on t2.FItemID=t1.F8
inner join t_Account t3 on t3.FAccountID=u1.FAccountID
where u1.FCurrencyID=0
and u1.FYear*12+u1.FPeriod>=@StartYear*12+@StartPeriod
and u1.FYear*12+u1.FPeriod<=@EndYear*12+@EndPeriod
and u1.FYear*12+u1.FPeriod<=@curYear*12+@curPeriod
and t2.FNumber=@FItemNumber
and t3.FNumber like @FAcctNumber
and t3.FDetail=1
insert into #report_sum
(FSuppID,FAcctountID,FDC,FIndex,FYear,FPeriod,FEndBalance)
select
F8,u1.FAccountID,t3.FDC,1,u1.FYear,u1.FPeriod,u1.FEndBalance*t3.FDC
from t_Balance u1
inner join t_ItemDetail t1 on u1.FDetailID=t1.FDetailID
inner join t_Item t2 on t2.FItemID=t1.F8
inner join t_Account t3 on t3.FAccountID=u1.FAccountID
where u1.FCurrencyID=0
and u1.FYear*12+u1.FPeriod>=@StartYear*12+@StartPeriod
and u1.FYear*12+u1.FPeriod<@EndYear*12+@EndPeriod
and u1.FYear*12+u1.FPeriod<@curYear*12+@curPeriod
and t2.FNumber=@FItemNumber
and t3.FNumber like @FAcctNumber
and t3.FDetail=1
insert into #report_sum
(FSuppID,FAcctountID,FDc,FIndex,FYear,FPeriod,FDate,FTrnasDate,FVoucherID,FExplanation,FDebit,FCredit)
select
F8,t1.FAccountID,t4.FDC,0,u1.FYear,u1.FPeriod,u1.FDate,u1.FTransDate,u1.FVoucherID,t1.FExplanation,
CASE WHEN t1.FDC=1 THEN t1.FAmount ELSE 0 END as FDebit, CASE WHEN t1.FDC=0 THEN t1.FAmount ELSE 0 END as FCredit
from t_Voucher u1
inner join t_VoucherEntry t1 on u1.FVoucherID=t1.FVoucherID
inner join t_ItemDetail t2 on t2.FDetailID=t1.FDetailID
inner join t_Item t3 on t3.FItemID=t2.F8
inner join t_Account t4 on t4.FAccountID=t1.FAccountID
where u1.FYear*12+u1.FPeriod>=@StartYear*12+@StartPeriod
and u1.FYear*12+u1.FPeriod<=@EndYear*12+@EndPeriod
and t4.FNumber like @FAcctNumber
and t3.FNumber=@FItemNumber
and t4.FDetail=1
order by u1.FYear,u1.FPeriod,u1.Fdate,u1.FVoucherID
declare @FID int, @Findex int,@FDC int,@FYear int,@FPeriod int,@FDebit decimal(21,10),
@FCredit decimal(21,10),@FEndBalance decimal(21,10),@FCurBanalce decimal(21,10),
@FOldAccountID int,@FCurAccountID int
set @FCurBanalce=0
set @FEndBalance=0
set @FOldAccountID=0
declare cur_list cursor for
select FAcctountID,FID,FIndex,FDC,FYear,FPeriod,isnull(FDebit,0),isnull(FCredit,0),FEndBalance from #report_sum order by FAcctountID,FYear,FPeriod,FIndex,FID
open cur_list
fetch next from cur_list
into @FCurAccountID,@FID,@Findex, @FDC,@FYear,@FPeriod,@FDebit,@FCredit,@FEndBalance
while @@FETCH_STATUS=0
begin
if @FOldAccountID<>@FCurAccountID
begin
set @FCurBanalce=0
set @FOldAccountID=@FCurAccountID
end
if @Findex=-1
begin
set @FCurBanalce=@FEndBalance
end
if @Findex=0
begin
set @FCurBanalce=@FCurBanalce+@FDC*@FDebit-@FDC*@FCredit
update u1
set u1.FEndBalance=@FCurBanalce
from #report_sum u1
where u1.FID=@FID
end
fetch next from cur_list
into @FCurAccountID,@FID,@Findex, @FDC,@FYear,@FPeriod,@FDebit,@FCredit,@FEndBalance
end
close cur_list
deallocate cur_list
select
t4.FNumber 供应商代码,
t4.FName 供应商名称,
u1.FYear 年度,
u1.FPeriod 期间,
t1.FNumber 科目代码,
t1.FName 科目名称,
u1.FDate 凭证日期,
u1.FTrnasDate 业务日期,
t3.FName+'-'+convert(nvarchar(20),t2.FNumber) 凭证字号,
isnull(u1.FExplanation,'')+(case when u1.FIndex=-1 then '期初' when u1.FIndex=1 then '结存' else '' end ) 摘要,
u1.FDebit 借方金额,
u1.FCredit 贷方金额,
u1.FEndBalance 余额
from #report_sum u1
inner join t_account t1 on u1.FAcctountID=t1.FAccountID
left join t_Voucher t2 on t2.FVoucherID=u1.FVoucherID
left join t_VoucherGroup t3 on t3.FGroupID=t2.FGroupID
left join t_Item t4 on t4.FItemID=u1.FSuppID
order by u1.FAcctountID,u1.FYear,u1.FPeriod,u1.FIndex,u1.FID
drop table #report_sum

2、创建供应商应付明细账存储过程：sp_getAccount_detail_2202，代码如下：

create procedure [dbo].[sp_getAccount_detail_2202]
@StartYear int,
@StartPeriod int,
@EndYear int,
@EndPeriod int,
@FItemNumber nvarchar(200)
as
begin
exec sp_getAccount_detail @StartYear,@StartPeriod,@EndYear,@EndPeriod,'2202%',@FItemNumber
end

3、创建供应商预付明细账存储过程：sp_getAccount_detail_1123，代码如下：

create procedure [dbo].[sp_getAccount_detail_1123]
@StartYear int,
@StartPeriod int,
@EndYear int,
@EndPeriod int,
@FItemNumber nvarchar(200)
as
begin
exec sp_getAccount_detail @StartYear,@StartPeriod,@EndYear,@EndPeriod,'1123%',@FItemNumber
end

4、K3查询分析工具先创建如下SQL关键字：

关键字标题数据类型数据来源字段是否允许录入
*StartYear* 开始年度数字型手工录入无允许录入
*StartPeriod* 开始期间数字型手工录入无允许录入
#EndYear# 截止年度数字型手工录入无允许录入
#EndPeriod# 截止期间数字型手工录入无允许录入
@SuppNumber@ 供应商代码字符串供应商代码允许录入

5、K3查询分析工具创建供应商应付账款明细表，sql语句为：

exec sp_getAccount_detail_2202 *StartYear*,*StartPeriod*,#EndYear#,#EndPeriod#,'@SuppNumber@'

6、K3查询分析工具创建供应商预付账款明细表，sql语句为：

exec sp_getAccount_detail_1123 *StartYear*,*StartPeriod*,#EndYear#,#EndPeriod#,'@SuppNumber@'

开发完毕。