成品库存周转率报表（二）

成品库存周转率报表（二）

报表逻辑

组织=供应链中心，仓库=成品仓，区间可选，可单独搜索某物料,物料基础数据为使用组织=供应链中心，仓库=成品仓作为底表
（1）库存数取自《库存账龄分析表》或《库存汇总查询》-历史查询
（2）期初库存为起始日期的查询库存 期末库存为结束日期的查询库存
（3）平均库存数量=（期初库存数量+期末库存数量）/2
（4）生产入库数量为选定日期范围内生产入库单的数量（按审核日期）
（5）销售出库数量为选定日期范围内销售出库单数量（按审核日期）
（6）产品库存周转率=360/（结束日期-起始日期）*（销售出库数量/平均库存数量）
（7）产品库存周转天数=360/库存周转率

报表设计

跟《成品库存周转率报表（一）》实现差不多，
只有存储过程不同，直接替换即可

根据日期返回当天的库存

CREATE PROCEDURE XXXX_PR_STK_STOCKQTY(
@STOCKID INT,
@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)
IF(@BEGINTIME='2024-01-01')
BEGIN
SET @fclosedate1=@BEGINTIME
SET @BALTYPE=1
END
--PRINT '@fclosedate1:'+@fclosedate1


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,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=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 =&