![summary-icon](https://cdn-vip.kingdee.com/statics/webfront/summary_icon.png)
本文提供了两种方案来获取金蝶云·星空系统中的存货收发数据:一是推荐通过web API取数,提供了相关入门教程和接口说明书的链接;二是通过SQL脚本自行查询,包括存货收发汇总表的查询示例和注意事项,如如何关联费用项目余额表和查询即时成本等。此外,还提供了金蝶云·星空存货数据结构讲解的链接和启用分表后取数的参考链接,以及星空数据表及字段说明的数据字典工具链接。
方案一:web API取数参考(推荐方案)
https://vip.kingdee.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://vip.kingdee.com/school/3611?productLineId=1&isKnowledge=2
启用分表后取数参考: https://vip.kingdee.com/article/332860922261711360?productLineId=1
--星空数据表以及字段说明
星空数据字典:https://open.kingdee.com/K3Cloud/WenKu/DocumentView.aspx?docId=102613
打开数据字典的工具:https://vip.kingdee.com/article/224631081512138496