金蝶二开常用SQL语句-查询即时库存可用量

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'  ---测试查询的物料内码