金蝶二开常用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