金蝶云星空生产入库单找仓库仓位编码SQL脚本

业务背景

有时候开发者需要后台导出生产入库单数据，仓库仓位内码需要转成编码和名称

 

SQL脚本

 SELECT a.fid,a.FBILLNO,b.FENTRYID,b.FMATERIALID,c.FSERIALID,c.FSERIALNO
,b.FSTOCKID,d.FNUMBER FSTOCKNUMBER,b.FSTOCKLOCID
,T3.FNUMBER FSTOCKLOCNUMBER, T31.FNAME FSTOCKLOCNAME
 ,10101 FUnitID, 'Pcs' FUnitNumber
FROM T_PRD_INSTOCK a JOIN T_PRD_INSTOCKENTRY b ON a.fid=b.fid 
JOIN  T_PRD_INSTOCKMTRLSERIAL c ON c.FENTRYID=b.FENTRYID
LEFT JOIN dbo.T_BD_STOCK d ON d.FSTOCKID=b.FSTOCKID
left JOIN T_BAS_FLEXVALUESDETAIL T2 ON b.FSTOCKLOCID = T2.FID -- 值集资料维度数据
left join T_BAS_FLEXVALUESENTRY T3 ON T3.FENTRYID = T2.FF100004   -- 仓位值
left join T_BAS_FLEXVALUESENTRY_L T31 ON T31.FENTRYID=T3.FENTRYID and T31.FLOCALEID=2052 --仓位值
WHERE   --a.fid={1} 
a.FBILLNO='SCRK2405200016'
AND c.FSERIALID>0 

 

 

查询结果