金蝶云星空历史库存信息批量计算生成

一、业务背景
今天是2024年07月30日，系统2024年01月01日启用，导入初始库存。

二、需求背景
需要快速查询库存组织=供应链中心下，某仓库某物料的库存数。后面还需要按照过去时间范围查询每一天的库存量以监控变化。

三、参考
《库存汇总表》《库存余额》《库存账龄分析》《物料收发明细表》

四、开发步骤
1、创建单据《历史库存信息》
字段：日期，单据编号，数据状态，库存组织，仓库，物料编码，库存数，锁库数

 

 表结构

CREATE TABLE [XXXX_T_STK_HisInventory](
    [FID] [BIGINT] NOT NULL PRIMARY KEY,
    [FBILLNO] [NVARCHAR](30) NOT NULL,
    [FDOCUMENTSTATUS] [CHAR](1) NOT NULL,
    [F_XXXX_DATE] [DATETIME] NULL,
    [F_XXXX_CREATEDATE] [DATETIME] NULL,
    [F_XXXX_CREATORID] [INT] NOT NULL,
    [F_XXXX_APPROVEDATE] [DATETIME] NULL,
    [F_XXXX_MODIFYDATE] [DATETIME] NULL,
    [F_XXXX_MODIFIERID] [INT] NOT NULL,
    [F_XXXX_APPROVERID] [INT] NOT NULL,
    [F_XXXX_STOCKORGID] [INT] NOT NULL,
    [F_XXXX_STOCKID] [INT] NOT NULL,
    [F_XXXX_STOCKQTY] [DECIMAL](23, 10) NOT NULL,
    [F_XXXX_SYSLOCKQTY] [DECIMAL](23, 10) NOT NULL,
    [F_XXXX_MATERIALID] [INT] NOT NULL)

 

2、需要生成日期库存信息的日期范围存储为表

后台生成2024-01-01至2024-07-21之间的数据。
#### 创建日期表

CREATE TABLE date_tables2 (
    date_column DATE PRIMARY KEY,--库存日期
    begindate DATE,--关账后第一天
    enddate DATE--库存日期+1
);

 

#### 使用递归插入数据

;WITH DateRange AS
(
SELECT '2024-01-01' AS DateValue
UNION ALL
SELECT CONVERT(VARCHAR(10),DATEADD(Day,1,DateValue) ,23)   AS DateValue
FROM DateRange
WHERE DateValue<='2024-07-20'
)
INSERT INTO date_tables2
SELECT DateValue
,CONVERT(VARCHAR(10)
,dateadd(day,-day(DateValue)+1,DateValue),120)
,DATEADD(DAY,1,DateValue) 
FROM DateRange OPTION(MAXRECURSION 0)

 

注意如果报错“语句被终止。完成执行语句前已用完最大递归 100。”
解决方案 是 添加 OPTION(MAXRECURSION 0)

 

3、创建存储过程1，根据日期生成计算当日库存，生成《历史库存信息》

CREATE PROCEDURE  [dbo].[XXXX_PR_STK_STOCKQTYALLGENERALINIT](
@DTIME VARCHAR(20),
@BEGINTIME VARCHAR(20),
@ENDTIME VARCHAR(20)
)
AS
BEGIN
DECLARE @BALTYPE INT
SET @BALTYPE=0

--获取当前关账的最后日期
DECLARE @fclosedate1 VARCHAR(10);
SET @fclosedate1=CONVERT(VARCHAR(10), DATEADD(D,-1,@BEGINTIME),23)
--PRINT '@fclosedate1:'+@fclosedate1


--SELECT CONVERT(VARCHAR(10), DATEADD(D,-1,'2024-01-01'),23)
IF(@BEGINTIME='2024-01-01')
BEGIN
SET @fclosedate1=@BEGINTIME
SET @BALTYPE=1
END

DECLARE  @DATE DATETIME
SET @DATE=CAST(@DTIME AS DATETIME)

SET @BEGINTIME=@BEGINTIME+' 00:00:00'
SET @ENDTIME=@ENDTIME+' 00:00:00'
PRINT @BEGINTIME
PRINT @ENDTIME
--SELECT LEN('2024-06-01 00:00:00')
--DECLARE @BEGINTIME VARCHAR(20)
--SET @BEGINTIME='2024-06-01 00:00:00'

--DECLARE @ENDTIME VARCHAR(20)
--SET @ENDTIME='2024-06-03 00:00:00'

--DECLARE @STOCKID INT
--SET @STOCKID=493513

--获取当前关账最后日期
--DECLARE @fclosedate1 VARCHAR(10);
--SET @fclosedate1=(SELECT CONVERT(VARCHAR(10), MAX(FCLOSEDATE),23) fclosedate 
--FROM T_STK_CLOSEPROFILE WHERE ((FORGID = (100006) AND FCATEGORY = 'STK') 
--AND (FCLOSEDATE < '2024-06-01 00:00:00')) GROUP BY FORGID)
--PRINT '@fclosedate1:'+ @fclosedate1

SELECT m.fmaterialid,m.fstockid,SUM(m.fqtyaddoption*m.fbaseqty) fqty 
INTO #m
FROM (
SELECT 'SAL_OUTSTOCK' fformid
, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
,t2.FSTOCKID
,  t2.FBASEUNITQTY fbaseqty
FROM T_SAL_OUTSTOCK t0 
LEFT OUTER JOIN T_SAL_OUTSTOCKFIN t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_SAL_OUTSTOCKENTRY t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st238 ON t2.FMATERIALID = st238.FMATERIALID 
LEFT OUTER JOIN t_BD_MaterialBase st239 ON t2.FMATERIALID = st239.FMATERIALID 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND ((t1.FISGENFORIOS = '0' AND (st238.FERPCLSID <> '6')) 
AND (st239.FSUITE <> '1'))) AND t0.FOBJECTTYPEID = 'SAL_OUTSTOCK') AND t0.FCANCELSTATUS = 'A') 
--AND t2.FSTOCKID=@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
UNION ALL
SELECT 'SAL_RETURNSTOCK' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEUNITQTY fbaseqty
FROM T_SAL_RETURNSTOCK t0 LEFT OUTER JOIN T_SAL_RETURNSTOCKENTRY t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_SAL_RETURNSTOCKFIN t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN T_BD_MATERIAL st11 ON t1.FMATERIALID = st11.FMATERIALID 
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st129 ON t1.FMATERIALID = st129.FMATERIALID 
LEFT OUTER JOIN t_BD_MaterialBase st130 ON t1.FMATERIALID = st130.FMATERIALID 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (((t2.FISGENFORIOS = '0' AND (st129.FERPCLSID <> '6'))
AND (st130.FSUITE <> '1')) AND (t1.FRETURNTYPE <> '0fa6270ab70b416cb2a7141a8f182d64'))) 
AND t0.FOBJECTTYPEID = 'SAL_RETURNSTOCK') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID=@STOCKID
--AND t1.FMATERIALID IN (SELECT  FMATERIALID FROM dbo.T_BD_MATERIAL WHERE fnumber=@MATERNUMBER)
--AND t1.FMATERIALID=669072
--AND ((((st11.FNUMBER >= '015102042') AND (st11.FNUMBER <= '015102042')) 
--AND (st17.FNUMBER >= '02')) AND (st17.FNUMBER <= '02')))
)
UNION ALL
SELECT 'STK_InStock' fformid
, 1 fqtyaddoption --1 入库 0出库
, t2.FMATERIALID fmaterialid
,t2.FSTOCKID
,t2.FBASEUNITQTY fbaseqty
FROM t_STK_InStock t0 LEFT OUTER JOIN t_STK_InStockFin t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_STK_INSTOCKENTRY t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN t_BD_Stock st24 ON t2.FSTOCKID = st24.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st241 ON t2.FMATERIALID = st241.FMATERIALID 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) 
AND (t1.FISGENFORIOS = '0' AND st241.FISINVENTORY = 1)) 
AND t0.FOBJECTTYPEID = 'STK_InStock') 
AND t0.FCANCELSTATUS = 'A') 
--AND t2.FSTOCKID=@STOCKID
--AND ((st24.FNUMBER >= '02') 
--AND (st24.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_InStock' fformid
, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid 
,t2.FRECEIVESTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM t_STK_InStock t0 
LEFT OUTER JOIN t_STK_InStockFin t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_STK_INSTOCKENTRY t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN t_BD_Stock st228 ON t2.FRECEIVESTOCKID = st228.FStockId 
WHERE ((((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) 
AND (((t2.FRECEIVESTOCKSTATUS <> 0) 
AND t2.FSRCBILLTYPEID = 'PUR_ReceiveBill') 
AND t1.FISGENFORIOS = '0')) AND t0.FOBJECTTYPEID = 'STK_InStock') 
AND t0.FCANCELSTATUS = 'A') 
--AND t2.FRECEIVESTOCKID =@STOCKID
--AND ((st228.FNUMBER >= '02') AND (st228.FNUMBER <= '02'))
)
AND t2.FRECEIVESTOCKFLAG = '1')
UNION ALL
SELECT 'PRD_PickMtrl' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
,t1.FSTOCKID fstockid
, t1_A.FBASESTOCKACTUALQTY fbaseqty 
FROM T_PRD_PICKMTRL t0
LEFT OUTER JOIN T_PRD_PICKMTRLDATA t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_PRD_PICKMTRLDATA_A t1_A ON t1.FENTRYID = t1_A.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FSTOCKID = st15.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND ((t1_A.FBASESTOCKACTUALQTY <> 0) OR (t1.FSECACTUALQTY <> 0))) 
AND t0.FFORMID = 'PRD_PickMtrl') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PRD_FeedMtrl' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
,t1.FSTOCKID
, t1_Q.FBASESTOCKACTUALQTY fbaseqty
FROM T_PRD_FEEDMTRL t0 
LEFT OUTER JOIN T_PRD_FEEDMTRLDATA t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_PRD_FEEDMTRLDATA_Q t1_Q ON t1.FENTRYID = t1_Q.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st11 ON t1.FSTOCKID = st11.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND ((t1_Q.FBASESTOCKACTUALQTY <> 0) OR (t1_Q.FSECACTUALQTY <> 0))) 
AND t0.FFORMID = 'PRD_FeedMtrl') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st11.FNUMBER >= '02') AND (st11.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_StockCountLoss' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASELOSSQTY fbaseqty
FROM T_STK_STKCOUNTLOSS t0 
LEFT OUTER JOIN T_STK_STKCOUNTLOSSENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
WHERE ((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FOBJECTTYPEID = 'STK_StockCountLoss') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MISCELLANEOUS' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISCELLANEOUS t0 
LEFT OUTER JOIN T_STK_MISCELLANEOUSENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'GENERAL') 
AND t0.FOBJECTTYPEID = 'STK_MISCELLANEOUS') 
AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MISCELLANEOUS' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISCELLANEOUS t0 
LEFT OUTER JOIN T_STK_MISCELLANEOUSENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'RETURN') 
AND t0.FOBJECTTYPEID = 'STK_MISCELLANEOUS') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MisDelivery' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid 
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISDELIVERY t0 
LEFT OUTER JOIN T_STK_MISDELIVERYENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'GENERAL') 
AND t0.FOBJECTTYPEID = 'STK_MisDelivery') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MisDelivery' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISDELIVERY t0 
LEFT OUTER JOIN T_STK_MISDELIVERYENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'RETURN') 
AND t0.FOBJECTTYPEID = 'STK_MisDelivery') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--ND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PUR_ReceiveBill' fformid, 1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM T_PUR_Receive t0 
LEFT OUTER JOIN T_PUR_ReceiveEntry t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FOBJECTTYPEID = 'PUR_ReceiveBill') AND t0.FCANCELSTATUS = 'A') 
--AND t2.FSTOCKID =@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
) 
AND t2.FSTOCKFLAG = '1')
UNION ALL
SELECT 'STK_AssembledApp' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st19 ON t1.FSTOCKID = st19.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Assembly') 
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st19.FNUMBER >= '02') AND (st19.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_AssembledApp' fformid, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0 
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_STK_ASSEMBLYSUBITEM t2 ON t1.FENTRYID = t2.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Assembly') 
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_AssembledApp' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0 
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st19 ON t1.FSTOCKID = st19.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Dassembly') 
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st19.FNUMBER >= '02') AND (st19.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_AssembledApp' fformid, 1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0 
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_STK_ASSEMBLYSUBITEM t2 ON t1.FENTRYID = t2.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Dassembly') 
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFEROUT' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFEROUT t0 
LEFT OUTER JOIN T_STK_STKTRANSFEROUTENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSRCSTOCKID = st14.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FISGENFORIOS = '0') AND t0.FOBJECTTYPEID = 'STK_TRANSFEROUT') 
AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_TRANSFEROUT' fformid, 1 fqtyaddoption
, t1.FDESTMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFEROUT t0 
LEFT OUTER JOIN T_STK_STKTRANSFEROUTENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FDESTSTOCKID = st15.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) 
AND (t0.FDATE < @ENDTIME)) AND t0.FSTOCKINORGID = 100006) 
AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'B')) 
AND t0.FOBJECTTYPEID = 'STK_TRANSFEROUT') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_TRANSFEROUT' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFEROUT t0 
LEFT OUTER JOIN T_STK_STKTRANSFEROUTENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSRCSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'A')) 
AND t0.FOBJECTTYPEID = 'STK_TRANSFEROUT') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_StockConvert' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_StockConvert t0 
LEFT OUTER JOIN T_STK_StockConvertEntry t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
WHERE ((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'B') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_StockConvert' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_StockConvert t0 
LEFT OUTER JOIN T_STK_StockConvertEntry t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
WHERE ((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'A') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFERIN' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FDESTSTOCKID = st15.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND (t0.FISGENFORIOS = '0' AND (t1.FBASEQTY <> 0))) 
AND t0.FOBJECTTYPEID = 'STK_TRANSFERIN') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFERIN' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1_T.FBASETRANSFERQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY_T t1_T ON t1.FENTRYID = t1_T.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FDESTSTOCKID = st15.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'B')) 
AND t0.FOBJECTTYPEID = 'STK_TRANSFERIN') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_TRANSFERIN' fformid, -1 fqtyaddoption
, t1.FSRCMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1_T.FBASETRANSFERQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY_T t1_T ON t1.FENTRYID = t1_T.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSRCSTOCKID = st14.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKOUTORGID = 100006) AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'A')) 
AND t0.FOBJECTTYPEID = 'STK_TRANSFERIN') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)

UNION ALL
SELECT 'PRD_ReturnMtrl' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid 
, t1_A.FBASESTOCKQTY fbaseqty
FROM T_PRD_RETURNMTRL t0 
LEFT OUTER JOIN T_PRD_RETURNMTRLENTRY t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_PRD_RETURNMTRLENTRY_A t1_A ON t1.FENTRYID = t1_A.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FSTOCKID = st15.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND ((t1_A.FBASESTOCKQTY <> 0) OR (t1.FSECSTOCKQTY <> 0)))
AND t0.FFORMID = 'PRD_ReturnMtrl') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_LOTADJUST' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_LOTADJUST t0 
LEFT OUTER JOIN T_STK_LOTADJUSTENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st16 ON t1.FSTOCKID = st16.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'A') AND t0.FOBJECTTYPEID = 'STK_LOTADJUST') 
AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st16.FNUMBER >= '02') AND (st16.FNUMBER <= '02'))
)

UNION ALL
SELECT 'STK_LOTADJUST' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_LOTADJUST t0 
LEFT OUTER JOIN T_STK_LOTADJUSTENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st16 ON t1.FSTOCKID = st16.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'B') AND t0.FOBJECTTYPEID = 'STK_LOTADJUST') 
AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st16.FNUMBER >= '02') AND (st16.FNUMBER <= '02'))
)

UNION ALL
SELECT 'PRD_INSTOCK' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEREALQTY fbaseqty
FROM T_PRD_INSTOCK t0 
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND t0.FENTRUSTINSTOCKID = 0) 
AND t0.FFORMID = 'PRD_INSTOCK') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL

SELECT 'STK_TransferDirect' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st110 ON t1.FDESTSTOCKID = st110.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st133 ON t1.FSRCMATERIALID = st133.FMATERIALID 
LEFT OUTER JOIN t_BD_MaterialBase st134 ON t1.FSRCMATERIALID = st134.FMATERIALID 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND ((st133.FERPCLSID <> '6') AND (st134.FSUITE <> '1'))) 
AND t0.FOBJECTTYPEID = 'STK_TransferDirect') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st110.FNUMBER >= '02') AND (st110.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TransferDirect' fformid, -1 fqtyaddoption
, t1.FSRCMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0 
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID 
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FSRCSTOCKID = st15.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st133 ON t1.FSRCMATERIALID = st133.FMATERIALID 
LEFT OUTER JOIN t_BD_MaterialBase st134 ON t1.FSRCMATERIALID = st134.FMATERIALID 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKOUTORGID = 100006) AND ((st133.FERPCLSID <> '6') AND (st134.FSUITE <> '1'))) 
AND t0.FOBJECTTYPEID = 'STK_TransferDirect') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)

UNION ALL
SELECT 'SUB_RETURNMTRL' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASESTOCKQTY fbaseqty
FROM T_SUB_RETURNMTRL t0 
LEFT OUTER JOIN T_SUB_RETURNMTRLENTRY t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_SUB_RETURNMTRLENTRY_A t1_A ON t1.FENTRYID = t1_A.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) 
AND ((t1.FBASESTOCKQTY <> 0) OR (FSECQTY <> 0))) AND t0.FFORMID = 'SUB_RETURNMTRL') 
AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)


UNION ALL
SELECT 'SUB_PickMtrl' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASESTOCKACTUALQTY fbaseqty
FROM T_SUB_PICKMTRL t0 
LEFT OUTER JOIN T_SUB_PICKMTRLDATA t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_SUB_PICKMTRLDATA_A t1_A ON t1.FENTRYID = t1_A.FENTRYID 
--LEFT OUTER JOIN t_BD_Stock st111 ON t1.FSTOCKID = st111.FStockId 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND ((t1.FBASESTOCKACTUALQTY <> 0) OR (t1.FSECACTUALQTY <> 0))) 
AND t0.FFORMID = 'SUB_PickMtrl') AND t0.FCANCELSTATUS = 'A') 
--AND t1.FSTOCKID =@STOCKID
--AND ((st111.FNUMBER >= '02') AND (st111.FNUMBER <= '02'))
)

UNION ALL
SELECT 'PUR_MRB' fformid, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM t_PUR_MRB t0 
LEFT OUTER JOIN T_PUR_MRBFIN t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_PUR_MRBENTRY t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN t_BD_Stock st26 ON t2.FSTOCKID = st26.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st236 ON t2.FMATERIALID = st236.FMATERIALID 
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND (((st236.FISINVENTORY = 1 AND (t2.FSTOCKSTATUSID <> t2.FRECEIVESTOCKSTATUSID)) 
AND t1.FISGENFORIOS = '0') AND t0.FMRTYPE = 'B')) 
AND t0.FOBJECTTYPEID = 'PUR_MRB') AND t0.FCANCELSTATUS = 'A') 
--AND t2.FSTOCKID =@STOCKID
--AND ((st26.FNUMBER >= '02') AND (st26.FNUMBER <= '02'))
)
UNION ALL

SELECT 'PUR_MRB' fformid, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FRECEIVESTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM t_PUR_MRB t0 
LEFT OUTER JOIN T_PUR_MRBFIN t1 ON t0.FID = t1.FID 
LEFT OUTER JOIN T_PUR_MRBENTRY t2 ON t0.FID = t2.FID 
--LEFT OUTER JOIN t_BD_Stock st225 ON t2.FRECEIVESTOCKID = st225.FStockId 
LEFT OUTER JOIN t_BD_MaterialBase st236 ON t2.FMATERIALID = st236.FMATERIALID 
WHERE ((((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME)) 
AND t0.FSTOCKORGID = 100006) AND (((st236.FISINVENTORY = 1 
AND (t2.FRECEIVESTOCKSTATUSID <> 0)) AND t2.FSTOCKSTATUSID = t2.FRECEIVESTOCKSTATUSID) 
AND t1.FISGENFORIOS = '0')) AND t0.FOBJECTTYPEID = 'PUR_MRB') 
AND t0.FCANCELSTATUS = 'A') 
--AND t2.FRECEIVESTOCKID =@STOCKID
--AND ((st225.FNUMBER >= '02') AND (st225.FNUMBER <= '02'))
) 
AND t2.FRECEIVESTOCKFLAG = '1')
UNION ALL
SELECT 'STK_INVBAL' fformid
, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
,t.FSTOCKID
,  SUM(t.FBASEQTY)  fbaseqty
FROM (
    SELECT TM.FNUMBER
    ,ISNULL(TI.FBASEENDQTY,0) FBASEQTY
    ,ISNULL(TI.FSECENDQTY,0) FSECQTY,TI.FMATERIALID FMATERIALID1
    ,TI.FSTOCKORGID
    ,TI.FAUXPROPID,TI.FKEEPERID,TI.FKEEPERTYPEID,TI.FLOT,TI.FOWNERID
    ,TI.FOWNERTYPEID,TI.FSTOCKID,TI.FSTOCKLOCID
    ,TI.FSTOCKSTATUSID,TI.FBOMID,TI.FMTONO,TI.FPROJECTNO,TI.FPRODUCEDATE
    ,TI.FEXPIRYDATE,TI.FBASEUNITID,TI.FSECUNITID,TI.FCOMBINEID
     FROM T_STK_INVBAL TI  JOIN T_BD_MATERIAL TM ON TM.FMATERIALID=TI.FMATERIALID
     WHERE TI.FSTOCKORGID = 100006 
     AND TI.FBALTYPE =  @BALTYPE --余额类型：初始库存余额1,关账=1
     --AND  TI.FBALDATE = @BEGINTIME --结存日期
     AND CONVERT(VARCHAR(10),TI.FBALDATE,23) =  @fclosedate1  --结存日期
     --AND TI.FSTOCKID=@STOCKID
 ) t    JOIN T_BD_MATERIAL t1 ON t1.FNUMBER=t.FNUMBER AND t1.FUSEORGID=100006
 GROUP BY t1.FMATERIALID,t.FSTOCKID
) m GROUP BY m.fmaterialid,m.FSTOCKID


 
 
-- SELECT t1.FMATERIALID,SUM(t.FBASEQTY) fqty 
--SELECT 'STK_INVBAL' fformid
--, 1 fqtyaddoption
--, t1.FMATERIALID fmaterialid
----,t2.FSTOCKID
--,  SUM(t.FBASEQTY)  fbaseqty
--FROM (
--    SELECT TM.FNUMBER
--    ,ISNULL(TI.FBASEENDQTY,0) FBASEQTY
--    ,ISNULL(TI.FSECENDQTY,0) FSECQTY,TI.FMATERIALID FMATERIALID1
--    ,TI.FSTOCKORGID
--    ,TI.FAUXPROPID,TI.FKEEPERID,TI.FKEEPERTYPEID,TI.FLOT,TI.FOWNERID
--    ,TI.FOWNERTYPEID,TI.FSTOCKID,TI.FSTOCKLOCID
--    ,TI.FSTOCKSTATUSID,TI.FBOMID,TI.FMTONO,TI.FPROJECTNO,TI.FPRODUCEDATE
--    ,TI.FEXPIRYDATE,TI.FBASEUNITID,TI.FSECUNITID,TI.FCOMBINEID
--     FROM T_STK_INVBAL TI  JOIN T_BD_MATERIAL TM ON TM.FMATERIALID=TI.FMATERIALID
--     WHERE TI.FSTOCKORGID = 100006 
--     AND TI.FBALTYPE = 0 --余额类型：关账余额
--     --AND  TI.FBALDATE = @BEGINTIME --结存日期
--     AND CONVERT(VARCHAR(10),TI.FBALDATE,23) =  '2024-03-31' --结存日期
--     AND TI.FSTOCKID=493513
-- ) t    JOIN T_BD_MATERIAL t1 ON t1.FNUMBER=t.FNUMBER AND t1.FUSEORGID=100006
-- GROUP BY t1.FMATERIALID


 --SELECT * FROM #m

 --SELECT  'LSKC'+CONVERT(VARCHAR(8),@DATE,112) 
 INSERT INTO XXXX_T_STK_HisInventory
  (FID,F_XXXX_Date,FBILLNO,FDOCUMENTSTATUS,F_XXXX_StockOrgId,F_XXXX_StockId
  ,F_XXXX_MATERIALID,F_XXXX_StockQty,F_XXXX_SysLockQty
  ,F_XXXX_CreatorId,F_XXXX_CreateDate,F_XXXX_ModifierId,F_XXXX_ModifyDate,F_XXXX_ApproverId,F_XXXX_ApproveDate) 
  SELECT  ROW_NUMBER() OVER ( ORDER BY T.FMATERIALID,T.FSTOCKID )
  +(SELECT ISNULL(MAX(FID),0) FROM XXXX_T_STK_HisInventory) AS FID,
          @DATE FDate,
          CONVERT(
        VARCHAR(40),
        'LSKC'+CONVERT(VARCHAR(8),@DATE,112) 
        +CONVERT(VARCHAR(10),T.FSTOCKID) 
        + CONVERT(VARCHAR(10), T.FMATERIALID)) FBillNo
            ,'C' 
            ,T.FSTOCKORGID
            ,T.FSTOCKID
            ,T.FMATERIALID
            ,T.FQty
            ,0 FSysLockQty
            ,16394 AS F_XXXX_CreatorId
            ,GETDATE() F_XXXX_CreateDate
            ,16394 AS F_XXXX_ModifierId
            ,GETDATE() F_XXXX_ModifyDate
            ,16394 AS F_XXXX_ApproverId
            ,GETDATE() F_XXXX_ApproveDate
FROM (SELECT  100006 AS FSTOCKORGID
  ,c.FSTOCKID
  ,c.fqty
,ISNULL(d.FMATERIALID,c.fmaterialid) FMATERIALID
 FROM (
  SELECT 
 a.*,b.FNUMBER
  FROM #m a LEFT JOIN T_BD_MATERIAL b ON a.fmaterialid=b.FMATERIALID WHERE a.fqty>0
  ) c LEFT JOIN T_BD_MATERIAL d ON d.FNUMBER=c.FNUMBER AND d.FUSEORGID=1) T

END
GO

 

4、创建存储过程2，根据日期范围循环调用存储过程1，生成《历史库存信息》

CREATE PROCEDURE [XXXX_PR_STK_GeneralStockQtyByDate]
@BEGINTIME VARCHAR(20),
@ENDTIME VARCHAR(20)
AS
BEGIN 
    DECLARE @date VARCHAR(50)
    DECLARE @bd VARCHAR(50)
    DECLARE @ed VARCHAR(50)
    DECLARE @i INT
    -- 声明一个游标
    DECLARE ghCursor CURSOR FOR
    --需要处理的结果集 T_PRD_PPBOMENTRY0619
    SELECT  *  FROM date_tables2 WHERE date_column BETWEEN @BEGINTIME  AND @ENDTIME

    OPEN ghCursor

    FETCH NEXT FROM ghCursor INTO @date, @bd,@ed
    SET @i=0
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC XXXX_PR_STK_STOCKQTYALLGENERALINIT  @date,@bd,@ed
    SET @i= @i+1
    FETCH NEXT FROM ghCursor INTO @date, @bd,@ed
    END
    CLOSE ghCursor
    DEALLOCATE ghCursor
    PRINT '处理行数:'+ CAST(@i AS NVARCHAR(10))
END
GO

 

5、执行生成

EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-01-01','2024-01-31'
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-02-01','2024-02-29'
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-03-01','2024-03-31'
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-04-01','2024-04-30'
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-05-01','2024-05-31'
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-06-01','2024-06-30'
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-07-01','2024-07-21'

 6、查询数据