金蝶二开常用SQL语句-查询即时库存可用量
即时库存表的数据无法直接获取到可用量，最近做的一个项目需要使用到即时库存可用量。只好按照社区的即时库存示例增加自己需要查询库存可用量。通过下面SQL查询可以准确获取FAVBQTY 可用量和FBASEAVBQTY可用量（基本单位）的数据。
SELECT
TI.FSTOCKORGID,
TI.FKEEPERTYPEID,
TI.FKEEPERID,
TI.FOWNERTYPEID,
TI.FOWNERID,
TI.FMATERIALID,
TM.FMATERIALID AS MaterialId,
TM.FNUMBER AS MaterialNum,
TI.FSTOCKID,
TI.FSTOCKLOCID,
TI.FAUXPROPID,
TI.FSTOCKSTATUSID,
TI.FLOT,
TI.FBOMID,
TI.FMTONO,
TI.FPROJECTNO,
(
CASE
WHEN TMS.FISEXPPARTOFLOT = '1' THEN TL.FPRODUCEDATE
ELSE TI.FPRODUCEDATE
END
) AS FPRODUCEDATE,
(
CASE
WHEN TMS.FISEXPPARTOFLOT = '1' THEN TL.FEXPIRYDATE
ELSE TI.FEXPIRYDATE
END
) AS FEXPIRYDATE,
TI.FQTY,
--基本单位库存量、辅助单位库存量
TI.FBASEUNITID,
TI.FBASEQTY,
TI.FSECUNITID,
TI.FSECQTY --基本单位锁库量、辅助单位锁库量
,
TSUB.FBASELOCKQTY AS FBASELOCKQTY,
(CASE WHEN TMS.FSTOREURNUM=0 THEN (TI.FBASEQTY - ISNULL(TSUB.FBASELOCKQTY, 0)) ELSE (TI.FBASEQTY - ISNULL(TSUB.FBASELOCKQTY, 0))*TMS.FSTOREURNOM/TMS.FSTOREURNUM END) FAVBQTY ,
(TI.FBASEQTY - ISNULL(TSUB.FBASELOCKQTY, 0) ) FBASEAVBQTY,
TSUB.FSECLOCKQTY AS FSECLOCKQTY,
--库存单位数量
TMS.FSTOREURNUM fstoreurnum,
TMS.FSTOREURNOM fstoreurnom,
TUS.FPRECISION as fprecision,
Convert(decimal(23, 10), TI.FBASEQTY / TMS.FSTOREURNUM) fstockunitqty
FROM
T_STK_INVENTORY TI
INNER JOIN T_BD_MATERIAL TM ON TI.FMATERIALID = TM.FMASTERID
AND TI.FSTOCKORGID = TM.FUSEORGID
INNER JOIN T_BD_MATERIALSTOCK TMS ON TM.FMATERIALID = TMS.FMATERIALID
LEFT JOIN T_BD_LOTMASTER TL ON TI.FLOT = TL.FMASTERID
AND TI.FSTOCKORGID = TL.FUSEORGID
AND TL.FBIZTYPE = '1'
LEFT OUTER JOIN T_BD_UNIT TUS ON TMS.FSTOREUNITID = TUS.FUNITID
LEFT OUTER JOIN T_BD_UNIT_L TUL0 ON (
TMS.FSTOREUNITID = TUL0.FUNITID
AND TUL0.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
WHERE
TI.FISEFFECTIVED = '1'
--AND TM.FMATERIALID='328233' ---测试查询的物料内码
金蝶二开常用SQL语句-查询即时库存可用量
本文2024-09-16 17:11:09发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-14083.html
