--存货收发汇总表
注意：查询本期需要先核算，单价=金额/数量
--按库存维度查询
select d.FYEAR 年份 , FPERIOD 期间 ,k.FNUMBER 会计核算体系, h.fnumber 货主,h.fnumber 库存组织,a.fnumber 物料编码, g.fnumber 仓库编码,
f.fnumber 批号,isnull( c.fqty ,0) 期初数量,isnull(c.FAMOUNT ,0) 期初金额,c1.FQTY 期末数量,c1.FAMOUNT 期末金额,c1.FOUTSTOCKQTY 发出数量,c1.FCURRENTOUTAMOUNT ,
c1.FCURRENTINQTY 收入数量,c1.FCURRENTINAMOUNT 收入金额
fROM T_BD_MATERIal a
join T_HS_inivSTOCKDIMENSION b on a.FMATERIALID =b.FMATERIALID
join T_HS_inivBALANCE c1 --查询历史期间需要将此表改为 T_HS_INIVBALANCE_H
on b.FENTRYID =c1.FDIMEENTRYID and c1.FENDINITKEY =1
left join T_HS_INIVBALANCE c --查询历史期间需要将此表改为 T_HS_INIVBALANCE_H
on b.FENTRYID =c.FDIMEENTRYID and c.FENDINITKEY =0 and c.fid=c1.fid
left join T_BD_STOCK g on b.fstockid=g.fstockid
left join T_BD_LOTMASTER f on b.flot=f.flotid
left join T_ORG_ORGANIZATIONS h on b.FCARGOOWNERID=h.FORGID
left join T_ORG_ORGANIZATIONS h1 on b.FSTOCKORGID=h1.FORGID
left join T_HS_CALDIMENSIONS j on c1.fdimensionid=j.fdimensionid
left join T_ORG_ACCountsystem k on j.FACCTSYSTEMID =k.FACCTSYSTEMID
join T_HS_OUTACCTG d on d.fid=c1.fid
where d.FYEAR='2019'--年份
and FPERIOD='6'--期间
and k.FNUMBER =''--会计核算体系编码
and h.fnumber=''--核算组织编码
and a.fnumber ='1.01.001'--物料编码
--按核算维度
select d.FYEAR 年份 , FPERIOD 期间 ,k.FNUMBER 会计核算体系, h.fnumber 货主,h.fnumber 库存组织,a.fnumber 物料编码, g.fnumber 仓库编码,
f.fnumber 批号,isnull( c.fqty,0) 期初数量,isnull(c.FAMOUNT,0) 期初金额,c1.FQTY 期末数量,c1.FAMOUNT 期末金额,c1.FOUTSTOCKQTY 发出数量,c1.FCURRENTOUTAMOUNT ,
c1.FCURRENTINQTY 收入数量,c1.FCURRENTINAMOUNT 收入金额
fROM T_BD_MATERIal a
join T_HS_inivSTOCKDIMENSION b on a.FMATERIALID =b.FMATERIALID
join T_HS_BALANCE c1 --查询历史期间需要将此表改为 T_HS_BALANCE_H
on b.FDIMEENTRYID =c1.FDIMEENTRYID and c1.FENDINITKEY =1
left join T_HS_BALANCE c --查询历史期间需要将此表改为 T_HS_BALANCE_H
on b.FDIMEENTRYID =c.FDIMEENTRYID and c.FENDINITKEY =0 and c.fid=c1.fid
left join T_BD_STOCK g on b.fstockid=g.fstockid
left join T_BD_LOTMASTER f on b.flot=f.flotid
left join T_ORG_ORGANIZATIONS h on b.FCARGOOWNERID=h.FORGID
left join T_ORG_ORGANIZATIONS h1 on b.FSTOCKORGID=h1.FORGID
left join T_HS_CALDIMENSIONS j on c1.fdimensionid=j.fdimensionid
left join T_ORG_ACCountsystem k on j.FACCTSYSTEMID =k.FACCTSYSTEMID
join T_HS_OUTACCTG d on d.fid=c1.fid
where d.FYEAR='2019'--年份
and FPERIOD='6'--期间
and k.FNUMBER =''--会计核算体系编码
and h.fnumber=''--核算组织编码
and a.fnumber ='1.01.001'--物料编码
另外注意一下，后台余额表（T_HS_INIVBALANCE 、T_HS_BALANCE等）结存都不会包含当期业务类型为期末余额调整的成本调整单的金额，需要另外关联查询，参考语句如下
select b.FADJUSTMENTAMOUNT--调整金额
, * from T_HS_ADJUSTMENTBILL a
join T_HS_ADJUSTMENTBILLENTRY b on a.fid=b.fid
join T_HS_inivSTOCKDIMENSION c on c.FENTRYID =b.FDIMEENTRYID
join T_HS_OUTACCTG aaa on a.FACCTGID = aaa.fid
where a.FBUSINESSTYPE='0'
select b.FADJUSTMENTAMOUNT--调整金额
, * from T_HS_ADJUSTMENTBILL a
join T_HS_ADJUSTMENTBILLENTRY b on a.fid=b.fid
join T_HS_STOCKDIMENSION c on c.FENTRYID =b.FACCTGDIMEENTRYID
join T_HS_OUTACCTG aaa on a.FACCTGID = aaa.fid
where a.FBUSINESSTYPE='0'
where fbillno='单据编号'
select a.* from T_HS_TIMELYPROCESS a join T_BD_MATERIAL b on a.FMATERIALID =b.FMATERIALID where FNUMBER ='物料编码'
推荐阅读