CREATE PROCEDURE [DBO].[REP_STOCK_REPORT]
@ITEM_NUM_START VARCHAR(30),@ITEM_NUM_END VARCHAR(30),@ADDRESS_ID VARCHAR(30),@AREA_ID VARCHAR(30),@LOC_ID VARCHAR(30),@SYS_ID VARCHAR(30),@Check_date varchar(12),@Check_flig varchar(10),@sum_type varchar(10)
AS
DECLARE @BAK_TIME Datetime ----备份时间
--SET @ITEM_NUM='%'+@ITEM_NUM+'%'
SET @ADDRESS_ID='%'+@ADDRESS_ID+'%'
SET @AREA_ID='%'+@AREA_ID+'%'
SET @LOC_ID='%'+@LOC_ID+'%'
SET @SYS_ID='%'+@SYS_ID+'%'
IF @ITEM_NUM_START=''
BEGIN
SET @ITEM_NUM_START='0'
END
IF @ITEM_NUM_END=''
BEGIN
SET @ITEM_NUM_END='Z'
END
--RAISERROR('%s',16,1,@sum_type)
select @BAK_TIME=max(bak_time) from STOCK_DETAIL_HIS where memo='每天自动备份' and conver
t(varchar(10),bak_time,120)<=@Check_date
IF @Check_flig='NowStock' --当前库存查询 His 为历史库存查询
BEGIN
SELECT A.ITEM_NUM,A.ADDRESS_ID,A.QTY,C.LOC_ID,D.SYSTEM_ID,Getdate() as StopTime into #tmp FROM STOCK_DETAIL A
LEFT JOIN LOCATION_ADDRESS B ON B.ADDRESS_ID=A.ADDRESS_ID
LEFT JOIN LOCATION_AREA C ON C.AREA_ID=B.AREA_ID
LEFT JOIN LOCATION D ON D.LOC_ID =C.LOC_ID
WHERE (A.ITEM_NUM Between @ITEM_NUM_START AND @ITEM_NUM_END) AND A.ADDRESS_ID LIKE @ADDRESS_ID AND B.AREA_ID LIKE @AREA_ID
AND D.LOC_ID LIKE @LOC_ID AND ISNULL(D.SYSTEM_ID,'') LIKE @SYS_ID
ORDER BY A.ITEM_NUM,A.ADDRESS_ID
IF @sum_type='明细'
BEGIN
SELECT ITEM_NUM,ADDRESS_ID,QTY,LOC_ID,SYSTEM_ID,
StopTime FROM #tmp where qty<>0
END
ELSE
BEGIN
SELECT ITEM_NUM,'汇总' AS ADDRESS_ID,SUM(QTY) AS QTY,LOC_ID,SYSTEM_ID,StopTime FROM #tmp where qty<>0 GROUP BY ITEM_NUM,LOC_ID,SYSTEM_ID,StopTime
END
END