--有时候做表或者后台看即时库存经常忘记辅助属性、可用量的取值办法 记下来备忘
--补充仓位值集
SELECT wl.FNUMBER 物料编码,wlm.FNAME 物料名称,ck.FNUMBER 仓库编码,ISNULL(ph.FNUMBER,'') 批号
,FBASEQTY 库存量,ISNULL(tFF100001_l.FDATAVALUE,'') 辅助属性1,ISNULL(tFF100002_l.FDATAVALUE,'') 辅助属性2
,FBASEQTY-ISNULL(TSUB.FBASELOCKQTY,0) 可用量,ISNULL(cwzj.FNUMBER,'') 仓位值
FROM T_STK_INVENTORY JSKC
LEFT JOIN T_BD_STOCK ck ON ck.FSTOCKID=JSKC.FSTOCKID
LEFT JOIN T_BD_STOCK_L ckm ON ck.FSTOCKID=ckm.FSTOCKID AND ckm.FLOCALEID=2052
LEFT JOIN T_BD_LOTMASTER ph ON ph.FLOTID=jskc.FLOT
--物料相关字段
LEFT JOIN T_BD_MATERIAL wl ON wl.FMASTERID=JSKC.FMATERIALID AND wl.FUSEORGID=ck.FUSEORGID
LEFT JOIN T_BD_MATERIAL_l wlm ON wl.FMATERIALID=wlm.FMATERIALID AND wlm.FLOCALEID=2052
LEFT JOIN T_BD_MATERIALBASE jb ON jb.FMATERIALID = wl.FMATERIALID
LEFT JOIN T_BD_MATERIALSTOCK kc ON kc.FMATERIALID = jb.FMATERIALID
LEFT JOIN T_ORG_ORGANIZATIONS zz ON zz.FORGID=JSKC.FOWNERID --货主
--辅助属性
left join T_BD_FLEXSITEMDETAILV aux ON JSKC.FAuxPropId = aux.FID
left join T_BAS_AssistantDataEntry tFF100001
on aux.FF100001 = tFF100001.FENTRYID
left join T_BAS_AssistantDataEntry_l tFF100001_l
on (tFF100001.FENTRYID = tFF100001_l.FENTRYID and tFF100001_l.FLocaleId = 2052)
left join T_BAS_AssistantDataEntry tFF100002
on aux.FF100002 = tFF100002.FENTRYID
left join T_BAS_AssistantDataEntry_l tFF100002_l
on (tFF100002.FENTRYID = tFF100002_l.FENTRYID and tFF100002_l.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
GROUP BY TLKE.FSUPPLYINTERID ) TSUB ON JSKC.FID = TSUB.FINVENTRYID
left join T_BAS_FLEXVALUESDETAIL CW on CW.FID=JSKC.FSTOCKLOCID--值集资料维度数据与仓位ID关联
left join T_BAS_FLEXVALUESENTRY CWZJ on CW.FF100010=CWZJ.FENTRYID--仓位值集-
WHERE wl.FNUMBER='物料编码'
赞 9
打赏