获取截至某日期时的在库序列号原创
2人赞赏了该文章
479次浏览
编辑于2022年12月27日 16:27:59
如何查询获取截至某日期时的在库序列号
说明:
正常思路应该按照单据的收发情况来依次计算截至某日期时的在库序列号,但是此逻辑计算量可能会很大,且实现也很复杂,这里借助序列号的追踪记录信息判断获取截至某日期时的在库序列号。
脚本示例:
SELECT owne_L.fname '货主', ORG.FNUMBER '库存组织编码',ORG_L.FNAME '库存组织名称', TM.FNUMBER '物料编码',TM_L.FNAME '物料名称', ERPCL.FCAPTION '物料属性', BDMC_L.FNAME '存货类别', DBMG_L.FNAME '物料分组', TM_L.FSPECIFICATION '规格型号', TBS_L.FNAME '库存状态',isnull(tlot.FNUMBER,'') '批号',tb.FNUMBER '仓库编码',TB_L.FNAME '仓库名称', T0.FNUMBER '序列号',T1.FINVID '库存内码' --,TV.* FROM T_BD_SERIALMASTER T0 INNER JOIN T_BD_SERIALBILLTRACE T1 ON T0.FSERIALID = T1.FSERIALID INNER JOIN (SELECT MAX(tb.FBILLTRACEID) fbilltraceid FROM T_BD_SERIALBILLTRACE tb INNER JOIN T_BD_SERIALBILLTRACE_E tbe on tb.FBILLTRACEID = tbe.FBILLTRACEID WHERE tbe.FBILLDATE < '2022-08-01' AND ( ISNULL(tb.FINVID, ' ') <> ' ' ) GROUP BY tb.FSERIALID) T2 ON T1.FBILLTRACEID = T2.fbilltraceid inner join T_STK_INVENTORY TV on T1.FINVID = TV.FID inner join T_ORG_ORGANIZATIONS ORG on TV.FSTOCKORGID = ORG.FORGID inner join T_ORG_ORGANIZATIONS_L ORG_L on ORG_L.FORGID = ORG.FORGID and ORG_L.FLOCALEID = 2052 inner join T_BD_STOCK TB on TV.FSTOCKID = TB.FSTOCKID inner join T_BD_STOCK_L TB_L on TB_L.FSTOCKID = TB.FSTOCKID and TB_L.FLOCALEID = 2052 inner join T_bd_material TM on TV.FMATERIALID = TM.FMASTERID and TV.FSTOCKORGID = TM.FUSEORGID inner join T_BD_MATERIALBASE TMB on TMB.FMATERIALID = TM.FMATERIALID inner join T_bd_material_L TM_L on TM.FMATERIALID = TM_L.FMATERIALID and TM_L.FLOCALEID = 2052 left join T_BD_LOTMASTER tlot on tv.flot = tlot.FLOTID left join T_BD_STOCKSTATUS_L TBS_L on TV.FSTOCKSTATUSID = TBS_L.FSTOCKSTATUSID and TBS_L.FLOCALEID = 2052 left join (select a.FVALUE,b.FCAPTION from T_META_FORMENUMITEM a inner join T_META_FORMENUMITEM_L b on a.fenumid = b.fenumid where FID = 'ac14913e-bd72-416d-a50b-2c7432bbff63' and b.FLOCALEID = 2052) ERPCL on TMB.FERPCLSID = ERPCL.FVALUE left join T_BD_MATERIALCATEGORY_L BDMC_L on BDMC_L.FCATEGORYID = TMB.FCATEGORYID and BDMC_L.FLOCALEID = 2052 left join T_BD_MATERIALGROUP_L DBMG_L on DBMG_L.FID = TM.FMATERIALGROUP and DBMG_L.FLOCALEID = 2052 left join V_ITEMCLASS_OWNER owne on TV.FOWNERTYPEID = owne.fformid and owne.fitemid= TV.FOWNERID left join V_ITEMCLASS_OWNER_L owne_L on owne.fitemid = owne_L.fitemid and owne_L.FLOCALEID = 2052 WHERE ( T0.FFORBIDSTATUS = 'A' AND T1.FSTATE = '1') order by TM.FNUMBER,tb.FNUMBER,T0.FNUMBER
赞 2
2人点赞
还没有人点赞,快来当第一个点赞的人吧!
打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!