ALTER VIEW VIEW_GL_BALANCE
as
Select FF100005,SUM(FENDBALANCE) as 'FENDBALANCE'
From(
Select 
t0.FACCOUNTID,t1.FF100005,FENDBALANCE
From 
T_GL_BALANCE t0 join t_bd_flexitemdetailv t1 on t1.fid = t0.FDETAILID
Where t0.FCURRENCYID!=0 --AND ISNULL(t1.FFLEX5,0)=0 AND ISNULL(t1.FFLEX7,0)=0
UNION ALL
Select 
t0.FACCOUNTID,t1.FF100005,FDEBIT
From
T_GL_VOUCHERENTRY t0 join t_bd_flexitemdetailv t1 on t0.fdetailid=t1.fid
Where --ISNULL(t1.FFLEX5,0)=0 AND ISNULL(t1.FFLEX7,0)=0 AND 
t1.FF100005 not in (Select FF100005 From VIEW_GL_BALANCE_ID)
) W Where W.FACCOUNTID in (Select a1.FACCTID From T_BD_ACCOUNT a1 
JOIN T_BD_ACCOUNT_L a2 on a1.FACCTID = a2.FACCTID Where FNUMBER like '5401.%' AND a2.FLOCALEID = 2052)
GROUP BY FF100005
go

--34096.05
Select * From T_GL_BALANCE Where FENDBALANCE = 34096.05

go----------------测试数据完善度
Select * From T_BAS_PREBDONE Where FNUMBER ='70.02/S202303010002X/PL2062'

Select * From t_bd_flexitemdetailv Where FID = 103458

Select * From T_GL_BALANCE Where FDETAILID = 103223
go
-------------------
ALTER VIEW VIEW_GL_BALANCE_ID--过滤科目余额已存在的数据
as
Select t1.FF100005 From T_GL_BALANCE t0 join t_bd_flexitemdetailv t1 on t1.fid = t0.FDETAILID
Where t0.FCURRENCYID!=0 AND ISNULL(t1.FFLEX5,0)=0 AND ISNULL(t1.FFLEX7,0)=0
AND ISNULL(t1.FF100005,0) != 0 AND t0.FACCOUNTID in (Select a1.FACCTID From T_BD_ACCOUNT a1 
JOIN T_BD_ACCOUNT_L a2 on a1.FACCTID = a2.FACCTID Where FNUMBER like '5401.%' AND a2.FLOCALEID = 2052)

go
Select * From VIEW_GL_BALANCE Where FF100005 = 1119332
---------------------
Select FF100005,FENDBALANCE as 'FENDBALANCE'
From(
Select 
t0.FACCOUNTID,t1.FF100005,FENDBALANCE
From 
T_GL_BALANCE t0 join t_bd_flexitemdetailv t1 on t1.fid = t0.FDETAILID
Where t0.FCURRENCYID!=0 AND ISNULL(t1.FFLEX5,0)=0 AND ISNULL(t1.FFLEX7,0)=0
UNION ALL
Select 
t0.FACCOUNTID,t1.FF100005,FDEBIT
From
T_GL_VOUCHERENTRY t0 join t_bd_flexitemdetailv t1 on t0.fdetailid=t1.fid
Where ISNULL(t1.FFLEX5,0)=0 AND ISNULL(t1.FFLEX7,0)=0 AND t1.FF100005 not in (Select FF100005 From VIEW_GL_BALANCE_ID)
) W Where W.FACCOUNTID in (Select a1.FACCTID From T_BD_ACCOUNT a1 
JOIN T_BD_ACCOUNT_L a2 on a1.FACCTID = a2.FACCTID Where FNUMBER like '5401.%' AND a2.FLOCALEID = 2052)
AND FF100005 = 1119332