即时库存查询
SELECT (CASE WHEN d.FSTOREURNUM=0 THEN (a.FBASEQTY - ISNULL(b.FBASELOCKQTY, 0)) ELSE (a.FBASEQTY - ISNULL(b.FBASELOCKQTY, 0))*d.FSTOREURNOM/d.FSTOREURNUM END) FAVBQTY2,
( a.FBASEQTY - ISNULL(b.FBASELOCKQTY, 0) ) FBASEAVBQTY2 ,
a.FID ,
a.FSTOCKORGID ,
a.FKEEPERTYPEID ,
a.FKEEPERID ,
a.FOWNERTYPEID ,
a.FOWNERID ,
a.FSTOCKID ,
a.FSTOCKLOCID ,
a.FAUXPROPID ,
a.FSTOCKSTATUSID ,
a.FLOT ,
a.FBOMID ,
a.FMTONO ,
a.FPROJECTNO ,
(CASE WHEN d.FISEXPPARTOFLOT='1' THEN e.FPRODUCEDATE ELSE a.FPRODUCEDATE END) FPRODUCEDATE ,
(CASE WHEN d.FISEXPPARTOFLOT='1' THEN e.FEXPIRYDATE ELSE a.FEXPIRYDATE END) FEXPIRYDATE ,
a.FBASEUNITID ,
a.FBASEQTY ,
a.FSECQTY ,
a.FSTOCKUNITID ,
a.FMATERIALID ,
(CASE WHEN d.FSTOREURNUM=0 THEN a.FBASEQTY ELSE a.FBASEQTY*d.FSTOREURNOM/d.FSTOREURNUM END) FQTY ,
(CASE WHEN d.FSTOREURNUM=0 THEN ISNULL(b.FBASELOCKQTY, 0) ELSE ISNULL(b.FBASELOCKQTY, 0)*d.FSTOREURNOM/d.FSTOREURNUM END) FLOCKQTY ,
a.FSECUNITID ,
'STK_INVENTORYCUS' AS FOBJECTTYPEID ,
(CASE WHEN d.FSTOREURNUM=0 THEN (a.FBASEQTY - ISNULL(b.FBASELOCKQTY, 0)) ELSE (a.FBASEQTY - ISNULL(b.FBASELOCKQTY, 0))*d.FSTOREURNOM/d.FSTOREURNUM END) AVBQTY ,
a.FUPDATETIME ,
a.FISEFFECTIVED,
ISNULL(b.FBASELOCKQTY, 0) FBASELOCKQTY ,
ISNULL(b.FSECLOCKQTY, 0) FSECLOCKQTY ,
( a.FBASEQTY - ISNULL(b.FBASELOCKQTY, 0) ) FBASEAVBQTY ,
( a.FSECQTY - ISNULL(b.FSECLOCKQTY, 0) ) FSECAVBQTY
FROM T_STK_INVENTORY a
INNER JOIN T_BD_MATERIAL c ON c.FMASTERID=a.FMATERIALID and c.FUSEORGID=a.FSTOCKORGID
INNER JOIN T_BD_MATERIALSTOCK d ON d.FMATERIALID=c.FMATERIALID
LEFT JOIN T_BD_LOTMASTER e on e.FLOTID=a.FLOT
LEFT JOIN ( SELECT TKE.FSUPPLYINTERID ,
SUM(ISNULL(TKE.FBASEQTY, 0)) AS FBASELOCKQTY ,
SUM(ISNULL(TKE.FSECQTY, 0)) AS FSECLOCKQTY
FROM T_PLN_RESERVELINKENTRY TKE
WHERE TKE.FSUPPLYFORMID = 'STK_Inventory'
AND TKE.FBASEQTY > 0
AND ((
TKE.FLINKTYPE = '4' AND 1= (
SELECT COUNT(1) FROM T_BAS_SYSPARAMETER WHERE fparameterobjid='MFG_PLNParam' AND fparameters.exist('/BillHead[IsEnableReserve="True"]')=0)
) OR (1=1 AND 0=(SELECT COUNT(1) FROM T_BAS_SYSPARAMETER WHERE fparameterobjid='MFG_PLNParam' AND fparameters.exist('/BillHead[IsEnableReserve="True"]')=0))
)
GROUP BY TKE.FSUPPLYINTERID
) b ON b.FSUPPLYINTERID = a.FID
即时库存查询
本文2024-09-16 17:43:45发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-17577.html