金蝶-科目余额表包含未过账凭证

最新推荐文章于 2024-07-08 17:54:51 发布

改昵称比敲代码难

最新推荐文章于 2024-07-08 17:54:51 发布

阅读量151

点赞数 1

分类专栏：金蝶文章标签： sql

本文链接：https://blog.csdn.net/weixin_50880237/article/details/137331095

版权

金蝶专栏收录该内容

2 篇文章 0 订阅

订阅专栏

记录科目余额表包含未过账凭证sql
以1122为例，不同科目核算维度不同

select 
FYEAR '年',FPERIOD '期间',
account.FNUMBER '科目编码',account_l.FFULLNAME '科目名称',
cst.FNUMBER '客户编码',cst_l.FNAME '客户名称',
org.FNUMBER '组织编码',org_l.FNAME '组织名称',
sum(FDEBIT) '借方金额',sum(FCREDIT) '贷方金额',
sum(FYTDDEBIT) '年借方金额',sum(FYTDCREDIT) '年贷方金额',
case when sum(FENDBALANCE) > 0 then sum(FENDBALANCE) else 0 end '期末借方',
case when sum(FENDBALANCE) < 0 then -1*sum(FENDBALANCE) else 0 end '期末贷方'
from (
	--科目余额
	select 
	FYEAR,FPERIOD,FDETAILID,FACCOUNTID,
	FDEBIT,FCREDIT,
	FYTDDEBIT,FYTDCREDIT,
	FENDBALANCEFOR,FENDBALANCE
	from T_GL_BALANCE                 
	where FCURRENCYID = 0 -- 本位币
	and FYEARPERIOD = 202402
	and FYEARPERIOD = 202402
	and FACCOUNTBOOKID = 1234567 --账簿

	union all

	--未过账凭证
	select
	FYEAR,FPERIOD,FDETAILID,FACCOUNTID,
	t2.FDEBIT,t2.FCREDIT,
	t2.FDEBIT FYTDDEBIT,t2.FCREDIT FYTDCREDIT,
	t2.FDEBIT-t2.FCREDIT FENDBALANCEFOR,t2.FDEBIT-t2.FCREDIT FENDBALANCE
	from      T_GL_VOUCHER            t1
	join      T_GL_VOUCHERENTRY       t2   on t1.FVOUCHERID = t2.FVOUCHERID
	where t1.FDOCUMENTSTATUS <> 'Z'
	and FYEAR = 2024 and FPERIOD = 02
	and FACCOUNTBOOKID = 1234567 --账簿
	and FPOSTED = 0 -- 未过账
	and FISADJUSTVOUCHER = 0 -- 不是调整期

) balance
join T_BD_ACCOUNT                 account      on account.FACCTID = balance.FACCOUNTID
join T_BD_ACCOUNT_L               account_l    on account.FACCTID = account_l.FACCTID
left join T_BD_FLEXITEMDETAILV    flex         on balance.FDETAILID = flex.FID
left join T_BD_CUSTOMER           cst          on flex.FFLEX6 = cst.FCUSTID
left join T_BD_CUSTOMER_L         cst_l        on cst.FCUSTID = cst_l.FCUSTID and cst_l.FLOCALEID = 2052
join T_ORG_ORGANIZATIONS          org          on flex.FFLEX11 = org.FORGID
join T_ORG_ORGANIZATIONS_L        org_l        on org.FORGID = org_l.FORGID and org_l.FLOCALEID = 2052
where FISDETAIL <> 0 and account.FNUMBER like '1122%'  
group by cst.FNUMBER,cst_l.FNAME,account.FNUMBER,org.FNUMBER,org_l.FNAME,account_l.FFULLNAME,FYEAR,FPERIOD
having (sum(FENDBALANCEFOR) <> 0 and sum(FENDBALANCE ) <> 0)  --不包含余额为0