Kingdee 云星空 C即时库存、三月用量、一年用量

最新推荐文章于 2023-01-04 14:45:25 发布

蜗牛～turbo

最新推荐文章于 2023-01-04 14:45:25 发布

阅读量269

点赞数

分类专栏： Kingdee ERP 文章标签：数据库操作临时表库存管理出库量数据更新

本文链接：https://blog.csdn.net/weixin_41120428/article/details/124180270

版权

Kingdee ERP 专栏收录该内容

16 篇文章 5 订阅

订阅专栏


 IF object_id('tempdb..#FMATERIALTABLE') is not null
	BEGIN
	DROP TABLE tempdb..#FMATERIALTABLE
	END
CREATE TABLE #FMATERIALTABLE
(
 FMATERIALNO NVARCHAR(30)
,FOWNERID INT
)
INSERT INTO #FMATERIALTABLE(FMATERIALNO,FOWNERID)
SELECT i.FNUMBER,i.FUSEORGID FROM dbo.T_BD_MATERIAL i WHERE   i.FMATERIALID in (SELECT FMATERIALID FROM t_BD_MaterialBase WHERE  FISINVENTORY=1)
AND i.FUSEORGID IN(1,100436)

IF object_id('tempdb..#udt_FMATERIALTABLE') is not null
	BEGIN
	DROP TABLE tempdb..#udt_FMATERIALTABLE
	END


SELECT udt.FMATERIALNO,udt.FOWNERID
   ,ISNULL(i.FINVQTY,0) FINVQTY,
   ISNULL(m.FMOUTQTY,0) FMOUTQTY ,
   ISNULL(y.FYOUTQTY,0) FYOUTQTY 
   INTO #udt_FMATERIALTABLE
   FROM #FMATERIALTABLE udt 
	LEFT JOIN (SELECT FMATERIALNO,SUM(FOUTQTY) FMOUTQTY FROM [dbo].[GET_OUTSTOCKENTRY_FilterBEDate] (DATEDIFF(MONTH,-3,GETDATE()),GETDATE()) GROUP BY FMATERIALNO
	) m ON m.FMATERIALNO = udt.FMATERIALNO
	LEFT JOIN (SELECT FMATERIALNO,SUM(FOUTQTY) FYOUTQTY FROM [dbo].[GET_OUTSTOCKENTRY_FilterBEDate] (DATEDIFF(MONTH,-3,GETDATE()),GETDATE()) GROUP BY FMATERIALNO
	) y ON y.FMATERIALNO = udt.FMATERIALNO
	LEFT JOIN (SELECT fmaterialid_no,fownerid_id,SUM(fbaseqty) FINVQTY FROM [dbo].[Get_INV_STK_Inventory_UnFilter] () GROUP BY fmaterialid_no,fownerid_id ) i ON i.fmaterialid_no=udt.FMATERIALNO AND i.fownerid_id=udt.FOWNERID
 IF object_id('tempdb..#FMATERIALTABLE') is not null
	BEGIN
	DROP TABLE tempdb..#FMATERIALTABLE
	END

UPDATE T_PLN_PLANORDER SET F_BHR_JSKC=udt.FINVQTY,F_BHR_MTHQTY=udt.FMOUTQTY,F_BHR_YTHQTY=udt.FYOUTQTY
 FROM T_PLN_PLANORDER l
LEFT JOIN dbo.T_BD_MATERIAL m ON l.FMATERIALID=m.FMATERIALID
LEFT JOIN #udt_FMATERIALTABLE udt ON m.FNUMBER=udt.FMATERIALNO AND udt.FOWNERID = l.FOWNERID
WHERE l.FDOCUMENTSTATUS='A'

IF object_id('tempdb..#udt_FMATERIALTABLE') is not null
	BEGIN
	DROP TABLE tempdb..#udt_FMATERIALTABLE
	END