## 查询即时库存信息
if(exists(select * from sys.objects where name='usp_MaterialKCMes'))
drop proc usp_MaterialKCMes
go
create proc [dbo].usp_MaterialKCMes
as
begin
drop table TMPF1B3F76485251111EA839838BAF86
SELECT st04.FNUMBER, t0.FQTY fqty, t0.FSTOCKUNITID fstockunitid, t0.FBASEUNITID fbaseunitid, t0.FBASEQTY fbaseqty, t0.FSECQTY fsecqty,
t0.FSECUNITID fsecunitid, t0.FLOCKQTY flockqty, t0.FBASELOCKQTY fbaselockqty, t0.FSECLOCKQTY fseclockqty,
t0.FAVBQTY favbqty, t0.FBASEAVBQTY fbaseavbqty, t0.FSECAVBQTY fsecavbqty, t0.FMATERIALID fmaterialid,
t0.FSTOCKID fstockid, t0.FLOT flot, t0.FSTOCKORGID fstockorgid, st013.FPRECISION fstockunitid_fprecision,
t0.FOBJECTTYPEID fobjecttypeid, t0.FID fid, ROW_NUMBER() OVER( ORDER BY t0.FID ASC) fidentityid into TMPF1B3F76485251111EA839838BAF86
FROM T_STK_INVENTORY t0 LEFT OUTER JOIN T_BD_MATERIAL st04 ON t0.FMATERIALID = st04.FMATERIALID LEFT OUTER JOIN t_BD_Stock_L st01_L ON
(t0.FSTOCKID = st01_L.FStockId AND st01_L.FLocaleId = 2052) LEFT OUTER JOIN T_BD_UNIT st013 ON t0.FSTOCKUNITID = st013.FUNITID
--可自行根据需求增加筛选条件 WHERE (((((st04.FNUMBER = N'000-BZXL.DZ.0060' AND (st01_L.FNAME LIKE N'%公司商品库%')) AND FISEFFECTIVED = '1') AND
--((t0.FBASEQTY <> 0) OR (t0.FSECQTY <> 0))) AND (t0.FSTOCKORGID = 0 OR (t0.FSTOCKORGID IN
--(100004) AND t0.FSTOCKORGID IN (1, 100004, 100005, 100006, 238078, 238079, 297955, 635994,
--764867)))) AND t0.FOBJECTTYPEID = 'STK_Inventory')
UPDATE TMPF1B3F76485251111EA839838BAF86 SET FBASEAVBQTY = FBASEQTY, FSECAVBQTY = FSECQTY
merge into TMPF1B3F76485251111EA839838BAF86 IT
USING (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
GROUP BY TKE.FSUPPLYINTERID) IT2
ON (IT.FID = IT2.FSUPPLYINTERID)
WHEN MATCHED THEN UPDATE SET
IT.FBASELOCKQTY = IT2.FBASELOCKQTY,IT.FSECLOCKQTY = IT2.FSECLOCKQTY,
IT.FBASEAVBQTY = IT.FBASEQTY - IT2.FBASELOCKQTY,IT.FSECAVBQTY = IT.FSECQTY - IT2.FSECLOCKQTY ;
declare @sql nvarchar(max);
set @sql='select * from TMPF1B3F76485251111EA839838BAF86 '
print @sql;
exec(@sql);
end
能否附上VS的截图呢？
