即时库存查询脚本示例

SELECT
TI.FSTOCKORGID
,OL.FNAME AS FSTOCKORGNAME
,M.FNUMBER AS FMATERIALNUMBER --物料编码
,ML.FNAME AS FMATERIALNAME --物料名称
,TSL.FNAME AS FSTOCKNAME --仓库
,TI.FSTOCKLOCID --仓位ID
,TUL0.FNAME AS FSTOCKUNITNAME --库存主单位
,TUL1.FNAME AS FBASEUNITNAME --基本单位
,TI.FBASEQTY
,TUL2.FNAME AS FSECUNITNAME --库存辅单位
,TI.FSECQTY
,TL.FNUMBER AS FLOTNUMBER
,TI.FAUXPROPID
,(CASE WHEN TMS.FISEXPPARTOFLOT = '1' THEN TO_CHAR(TL.FPRODUCEDATE,'yyyy-MM-dd') ELSE TO_CHAR(TI.FPRODUCEDATE,'yyyy-MM-dd') END) AS FPRODUCEDATE
,(CASE WHEN TMS.FISEXPPARTOFLOT = '1' THEN TO_CHAR(TL.FEXPIRYDATE,'yyyy-MM-dd') ELSE TO_CHAR(TI.FEXPIRYDATE,'yyyy-MM-dd') END) AS FEXPIRYDATE
,TB.FNUMBER AS FBOMNUMBER --BOM编号
,TSSL.FNAME AS FSTOCKSTATUS
,TI.FOWNERTYPEID
,VO_L.FNAME AS FOWNERNAME
,TI.FKEEPERTYPEID --保管者类型
,VK_L.FNAME AS FKEEPERNAME
,TMS.FSTOREURNUM
,TMS.FSTOREURNOM
,TMS.FISSNMANAGE
,TSK.FALLOWMINUSQTY
,TUS.FPRECISION AS FSTKPRECISION
,TUS.FROUNDTYPE AS FROUNDTYPE
,TUE.FPRECISION AS FSECPRECISION
,TI.FMTONO
,TI.FPROJECTNO
,TSUB.FBASELOCKQTY AS FBASELOCKQTY
,TSUB.FSECLOCKQTY AS FSECLOCKQTY
,(CASE WHEN VFF100001.FNAME IS NOT NULL THEN '货架:'||TO_CHAR(VFF100001.FNAME)||';' ELSE '' END)||(CASE WHEN VFF100002.FNAME IS NOT NULL THEN '层:'||TO_CHAR(VFF100002.FNAME)||';' ELSE '' END)||(CASE WHEN VFF100003.FNAME IS NOT NULL THEN '排:'||TO_CHAR(VFF100003.FNAME)||';' ELSE '' END) AS FSTOCKLOCNAME
FROM T_STK_INVENTORY TI
LEFT JOIN T_ORG_ORGANIZATIONS_L OL ON TI.FSTOCKORGID=OL.FORGID AND OL.FLOCALEID=2052 --机构名称
INNER JOIN T_BD_MATERIAL M ON ( M.FMASTERID = TI.FMATERIALID AND (M.FUSEORGID = TI.FSTOCKORGID
OR EXISTS (SELECT 1 FROM T_META_BASEDATATYPE BT WHERE BT.FBASEDATATYPEID= 'BD_MATERIAL' AND BT.FSTRATEGYTYPE=1))) --物料
LEFT JOIN T_BD_MATERIAL_L ML ON M.FMATERIALID=ML.FMATERIALID AND ML.FLOCALEID=2052 --物料名称、规格型号
INNER JOIN T_BD_MATERIALSTOCK TMS ON M.FMATERIALID = TMS.FMATERIALID
LEFT JOIN T_ENG_BOM TB ON ( TB.FMASTERID = TI.FBOMID AND (TB.FUSEORGID = TI.FSTOCKORGID
OR EXISTS (SELECT 1 FROM T_META_BASEDATATYPE BT WHERE BT.FBASEDATATYPEID= 'ENG_BOM' AND BT.FSTRATEGYTYPE=1)))
LEFT JOIN T_BD_STOCKSTATUS_L TSSL ON TI.FSTOCKSTATUSID = TSSL.FSTOCKSTATUSID AND TSSL.FLOCALEID = 2052
LEFT JOIN T_BD_LOTMASTER TL ON TI.FLOT = TL.FMASTERID AND TI.FSTOCKORGID=TL.FUSEORGID AND TL.FBIZTYPE = '1'
LEFT JOIN T_BD_UNIT TUS ON TMS.FSTOREUNITID = TUS.FUNITID
LEFT JOIN T_BD_UNIT_L TUL0 ON TMS.FSTOREUNITID = TUL0.FUNITID AND TUL0.FLOCALEID = 2052
INNER JOIN T_BD_STOCK TSK ON ( TSK.FMASTERID = TI.FSTOCKID AND (TSK.FUSEORGID = TI.FSTOCKORGID
OR EXISTS (SELECT 1 FROM T_META_BASEDATATYPE BT WHERE BT.FBASEDATATYPEID= 'BD_STOCK' AND BT.FSTRATEGYTYPE=1)))
LEFT JOIN T_BD_STOCK_L TSL ON TSL.FSTOCKID = TI.FSTOCKID AND TSL.FLOCALEID = 2052
LEFT JOIN T_BD_UNIT_L TUL1 ON TI.FBASEUNITID = TUL1.FUNITID AND TUL1.FLOCALEID = 2052
LEFT JOIN T_BD_UNIT TUE ON TMS.FAUXUNITID = TUE.FUNITID
LEFT JOIN T_BD_UNIT_L TUL2 ON TI.FSECUNITID = TUL2.FUNITID AND TUL2.FLOCALEID = 2052
LEFT JOIN V_ITEMCLASS_OWNER VO ON VO.FMASTERID=TI.FOWNERID AND VO.FFORMID=TI.FOWNERTYPEID
AND (VO.FUSEORGID = TI.FSTOCKORGID OR VO.FUSEORGID = 0
OR EXISTS (SELECT 1 FROM T_META_BASEDATATYPE BT WHERE BT.FBASEDATATYPEID=VO.FFORMID AND BT.FSTRATEGYTYPE=1)
) LEFT JOIN V_ITEMCLASS_OWNER_L VO_L
ON VO.FITEMID = VO_L.FITEMID AND VO_L.FLOCALEID = 2052
LEFT JOIN V_ITEMCLASS_KEEPER VK ON VK.FMASTERID=TI.FKEEPERID AND VK.FFORMID=TI.FKEEPERTYPEID
AND (VK.FUSEORGID = TI.FSTOCKORGID OR VK.FUSEORGID = 0
OR EXISTS (SELECT 1 FROM T_META_BASEDATATYPE BT WHERE BT.FBASEDATATYPEID=VK.FFORMID AND BT.FSTRATEGYTYPE=1)
) LEFT JOIN V_ITEMCLASS_KEEPER_L VK_L
ON VK.FITEMID = VK_L.FITEMID AND VK_L.FLOCALEID = 2052
--以下仓位
LEFT JOIN T_BAS_FLEXVALUESDETAIL FVD ON TI.FSTOCKLOCID=FVD.FID

LEFT JOIN T_BAS_FLEXVALUESENTRY_L AS VFF100001
ON FVD.FF100001=VFF100001.FENTRYID AND VFF100001.FLOCALEID = 2052
LEFT JOIN T_BAS_FLEXVALUESENTRY_L AS VFF100002
ON FVD.FF100002=VFF100002.FENTRYID AND VFF100002.FLOCALEID = 2052
LEFT JOIN T_BAS_FLEXVALUESENTRY_L AS VFF100003
ON FVD.FF100003=VFF100003.FENTRYID AND VFF100003.FLOCALEID = 2052
--以下锁库和预留
LEFT JOIN (SELECT TLKE.FSUPPLYINTERID AS FINVENTRYID, SUM(TLKE.FBASEQTY) AS FBASELOCKQTY,
SUM(TLKE.FSECQTY) AS FSECLOCKQTY
FROM T_PLN_RESERVELINKENTRY TLKE INNER JOIN T_PLN_RESERVELINK TLKH ON TLKE.FID = TLKH.FID
WHERE TLKE.FSUPPLYFORMID = 'STK_Inventory' AND TLKE.FLINKTYPE = '4'
GROUP BY TLKE.FSUPPLYINTERID) TSUB ON TI.FID = TSUB.FINVENTRYID

以上是关联基础资料表查询即时库存的脚本示例
注：
1、辅助属性部分，只取了内码，编码和名称需要根据辅助属性维度再次拼接
2、即时库存表中的库存单位的数量是不准确的，是在查询时根据基本单位数量重新换算的，物料的表库存信息表T_BD_MATERIALSTOCK 中冗余的有基本单位和库存单位之间的换算分子和换算分母，可以用来计算库存单位数量，但是也要考虑库存单位的精度和舍入类型
3、即时库存表中的所有锁库数量也是不准确的，是在查询时才在预留关系表中抓取的，SQL中的锁库和预留部分，其中红色的部分需要根据系统是否启用预留来决定是否需要添加该条件，启用预留则不需要该条件，不启用预留时该条件限定只获取锁库数据
4、仓位处理部分是根据系统启用仓位值集的情况动态拼接出来的，大致类似，但是不同的账套会有所不同
5、上述脚本为KSQL示例，执行之前需要通过KSQL翻译为对应数据库脚本

6、Kingdee.K3.SCM.App.Core的StockInvService有提供查询即时库存的一些接口函数，在Kingdee.K3.SCM.ServiceHelper中也有一些函数可以使用