查询某科目某个公司下的往来单位的余额的sql语句

最新推荐文章于 2024-06-17 10:36:22 发布

原创

最新推荐文章于 2024-06-17 10:36:22 发布 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

该SQL语句用于查询指定科目和公司下所有往来单位的余额。通过连接多个表格，包括会计辅助明细记录、凭证、科目视图、机构、币种、客户等，筛选科目和机构条件，并计算每个往来单位的净额。结果按机构号、科目编码、币种编码、往来单位编码和名称进行分组，并排除余额为零的记录。

select e.FNumber as 机构号, g.FNumber AS 科目编码, n.FNumber as 币种编码,
s.FNumber as 往来单位编码, s.FName_L2 as 往来单位名称,
--b.fentrydc,a.foriginalamount
nvl(sum(decode(b.fentrydc,1,a.foriginalamount,-a.foriginalamount)),0) as amount
from t_gl_voucherassistrecord a
left join t_gl_voucher f on a.fbillid=f.fid
left join t_gl_voucherentry b on f.fid=b.fbillid and a.fentryid=b.fid
left join t_bd_assistanthg c on a.fassgrpid=c.fid
left join t_bd_accountview g on b.faccountid=g.fid
left join t_org_baseunit e on e.fid=f.fcompanyid
left join t_bd_currency n on b.fcurrencyid=n.fid
--left join T_PAC_FinancingChar z on c.FFINANCINGCHARID=z.Fid --资金性质
left join t_bd_customer s on c.FCustomerID=s.Fid --办事处及客户
where 1=1
and g.fnumber like '133.01'--更改科目
and f.fbizstatus<>0 and f.fbizstatus<>2
--and f.fsourcetype<>1 1代表结转损益
and e.fnumber like 'A1004' -- 更改组织
--and s.FNumber like '100158' --客户及办事处
group by e.fnumber,e.FNumber,g.FNumber,s.fname_l2,s.FNumber,n.FNumber
having sum(decode(b.fentrydc,1,a.foriginalamount,-a.foriginalamount))<>0