二开获取存货收发汇总表，核算单据，即时成本等报表数据参考方案
方案一：web API取数参考（推荐方案）
https://wenku.my7c.com/article/208642328108816640?productLineId=1&isKnowledge=2
方案二：SQL参考脚本,需自行加工验证获取想要的数据
--存货收发汇总表
注意：查询本期需要先核算，单价=金额/数量
--按库存维度查询
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'--物料编码
注意点：
1、如要查询费用项目余额，请自行在上述脚本的基础上，分别关联T_HS_INIVBALANCEexp （库存维度） 、T_HS_BALANCEexp（核算维度）表，
关联关系如下 T_HS_INIVBALANCE.fentryid=T_HS_INIVBALANCEexp .fentryid
T_HS_BALANCE.fentryid=T_HS_INIVBALANCEexp .fentryid
2、后台余额表（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 ='物料编码'
金蝶云·星空存货数据结构讲解：https://wenku.my7c.com/school/3611?productLineId=1&isKnowledge=2
启用分表后取数参考： https://wenku.my7c.com/article/332860922261711360?productLineId=1
--星空数据表以及字段说明
星空数据字典：https://open.kingdee.com/K3Cloud/WenKu/DocumentView.aspx?docId=102613
打开数据字典的工具：https://wenku.my7c.com/article/224631081512138496
大佬 加不统计核算组织内调拨单据这个过滤条件在sql脚本里咋加呀
请问是怎么根据物料编码获取对应单价信息的急用求求了
大佬受累咨询一下 这个库存状态是怎么取出来的
大老，我出来的结果怎么是空的？
大佬，怎么我的sql查询结果的数量都和金蝶云查的数量不一致呀...
二开获取存货收发汇总表，核算单据，即时成本等报表数据参考方案
本文2024-09-16 17:17:55发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-14805.html
