查询即时库存
SELECT
TI.FSTOCKORGID,
OL.FNAME fstockorgname,
M.FNUMBER '物料编码', --物料编码
ML.FNAME '物料名称',--物料名称
TSL.FNAME '仓库',--仓库
TI.FSTOCKLOCID '仓位ID',--仓位ID
TUL0.FNAME '库存主单位',--库存主单位
TI.FQTY '主单位库存量',
TUL1.FNAME '基本单位', --基本单位
TI.FBASEQTY '基本单位库存量',
TUL2.FNAME '库存辅单位',--库存辅单位
TI.FSECQTY '库存辅单位库存量',
TL.FNUMBER flotnumber,
TI.FAUXPROPID,
CASE
WHEN TMS.FISEXPPARTOFLOT = '1' THEN
CONVERT (
CHAR (10),
TL.FPRODUCEDATE,
20
)
ELSE
CONVERT (
CHAR (10),
TI.FPRODUCEDATE,
20
)
END fproducedate,
CASE
WHEN TMS.FISEXPPARTOFLOT = '1' THEN
CONVERT (CHAR(10), TL.FEXPIRYDATE, 20)
ELSE
CONVERT (CHAR(10), TI.FEXPIRYDATE, 20)
END fexpirydate,
TB.FNUMBER fbomnumber,--BOM编号
TSSL.FNAME fstockstatus,
TI.FOWNERTYPEID,
VO_L.FNAME fownername,
TI.FKEEPERTYPEID,--保管者类型
VK_L.FNAME fkeepername,
TMS.FSTOREURNUM,
TMS.FSTOREURNOM,
TMS.FISSNMANAGE,
TSK.FALLOWMINUSQTY,
TUS.FPRECISION fstkprecision,
TUS.FROUNDTYPE froundtype,
TUE.FPRECISION fsecprecision,
TI.FMTONO,
TI.FPROJECTNO,
TSUB.FBASELOCKQTY fbaselockqty,
TSUB.FSECLOCKQTY fseclockqty,
'' fstocklocname
FROM
AIS20231009213721.dbo.T_STK_INVENTORY TI
LEFT OUTER 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 OUTER 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 OUTER 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 OUTER JOIN T_BD_STOCKSTATUS_L TSSL ON (
TI.FSTOCKSTATUSID = TSSL.FSTOCKSTATUSID
AND TSSL.FLOCALEID = 2052
)
LEFT OUTER 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
)
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 OUTER JOIN T_BD_STOCK_L TSL ON (
TSL.FSTOCKID = TI.FSTOCKID
AND TSL.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BD_UNIT_L TUL1 ON (
TI.FBASEUNITID = TUL1.FUNITID
AND TUL1.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BD_UNIT TUE ON TMS.FAUXUNITID = TUE.FUNITID
LEFT OUTER JOIN T_BD_UNIT_L TUL2 ON (
TI.FSECUNITID = TUL2.FUNITID
AND TUL2.FLOCALEID = 2052
)
LEFT OUTER 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 OUTER JOIN V_ITEMCLASS_OWNER_L VO_L ON (
VO.FITEMID = VO_L.FITEMID
AND VO_L.FLOCALEID = 2052
)
LEFT OUTER 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 OUTER JOIN V_ITEMCLASS_KEEPER_L VK_L ON (
VK.FITEMID = VK_L.FITEMID
AND VK_L.FLOCALEID = 2052
)
--以下仓位
LEFT OUTER JOIN T_BAS_FLEXVALUESDETAIL FVD ON TI.FSTOCKLOCID = FVD.FID
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L VFF100001 ON (
FVD.FOPCODE = VFF100001.FENTRYID
AND VFF100001.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L VFF100002 ON (
FVD.FOPCODE = VFF100002.FENTRYID
AND VFF100002.FLOCALEID = 2052
)
LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L VFF100003 ON (
FVD.FOPCODE = VFF100003.FENTRYID
AND VFF100003.FLOCALEID = 2052
)
--以下锁库和预留
LEFT OUTER JOIN (
SELECT
TLKE.FSUPPLYINTERID finventryid,
SUM (TLKE.FBASEQTY) fbaselockqty,
SUM (TLKE.FSECQTY) 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.FBASEQTY > 0
推荐阅读