金蝶二开常用SQL语句-查询即时库存可用量原创
金蝶云社区-黄昏前黎明后
黄昏前黎明后
86人赞赏了该文章 1,550次浏览 未经作者许可,禁止转载编辑于2024年05月06日 14:47:01


       即时库存表的数据无法直接获取到可用量,最近做的一个项目需要使用到即时库存可用量。只好按照社区的即时库存示例增加自己需要查询库存可用量。通过下面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'  ---测试查询的物料内码


图标赞 86
86人点赞
还没有人点赞,快来当第一个点赞的人吧!
图标打赏
1人打赏
还没有人打赏,快来当第一个打赏的人吧!