CustApi_and_Plugin_zky/Sql/SP_Cust_PDA_GetItemMasterWh.sql

-- ================================================
-- Template generated FROM Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill IN the parameter 
-- values below.
--
-- This block of comments will NOT be included IN
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		曹嘉杰
-- Create date: 2022/10/16
-- Description:	查询料品存储信息
-- =============================================
IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'SP_Cust_PDA_GetItemMasterWh'))
    DROP PROC SP_Cust_PDA_GetItemMasterWh
GO

CREATE PROCEDURE SP_Cust_PDA_GetItemMasterWh
	-- Add the parameters for the stored procedure here
	@OrgCode NVARCHAR(50),
	@WhCode NVARCHAR(50),
	@BinCode NVARCHAR(50),
	@Filter NVARCHAR(500),
	@PageSize INT,
	@PageIndex INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets FROM
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF OBJECT_ID('tempdb..#TempTableQoh') IS NOT NULL
	BEGIN
		DROP TABLE #TempTableQoh
	END

	DECLARE @ItemCode AS NVARCHAR(255)
	IF EXISTS (SELECT 1 FROM BC_BarCode WHERE Code = @Filter AND BarCodeType = 3)
	BEGIN
		SELECT @ItemCode = Segment1 FROM BC_BarCode WHERE Code = @Filter AND BarCodeType = 3
	END
	ELSE BEGIN
		SET @ItemCode = @Filter
	END

	select A.[ItemInfo_ItemCode] as [Item_ItemCode],
		isnull( case  when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') as [ItemSeg_Name],
		A1.[SPECS] as [ItemID_SPECS],
		sum((((A.[StoreQty] - A.[ResvStQty]) - A.[ResvOccupyStQty]) - case  when ((((A.[IsProdCancel] = 1) or (A.[MO_EntityID] != 0)) or A.[ProductDate] is not null) or (A.[WP_EntityID] != 0)) then A.[StoreQty] else convert(decimal(24,9),0) end )) as [CanUseQty], 
		A2.[Round_Precision] as [Round1_Precision],
		sum((A.[StoreQty] + A.[ToRetStQty])) as [BalQty],
		sum((A.[StoreMainQty] + A.[ToRetStMainQty])) as [BalQty_Main],
		sum((((((A.[StoreQty] - A.[ResvStQty]) - A.[ResvOccupyStQty]) - case  when ((((A.[IsProdCancel] = 1) or (A.[MO_EntityID] != 0)) or A.[ProductDate] is not null) or (A.[WP_EntityID] != 0)) then A.[StoreQty] else convert(decimal(24,9),0) end ) + A.[SupplyQtySU]) - A.[DemandQtySU])) as [Temp_PAB],
		convert(bigint,0) as [Item_ItemID],
		convert(bigint,0) as [W_Uom], 
		convert(bigint,0) as [MainBaseSU_ID] 
	into #TempTableQoh 
	from InvTrans_WhQoh as A 
		left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])  
		left join [Base_UOM] as A2 on (A.[StoreUOM] = A2.[ID])
		left join [Base_Organization] as A3 on (A.[LogisticOrg] = A3.[ID])  
		left join [CBO_Wh] as A4 on (A.[Wh] = A4.[ID])
	where (((((A1.[Name] is not null and (A1.[Name] != '')) 
		and (A3.[Code] = @OrgCode))
		and (A4.[Code] = @WhCode))
		and (A.[ItemInfo_ItemCode] LIKE N'%' + @ItemCode + '%')) 
		and (A.[BinInfo_Code] LIKE N'%' + @BinCode + '%'))
	group by A.[ItemInfo_ItemCode], 
		isnull( case  when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,''), 
		A1.[SPECS], 
		A2.[Round_Precision]
	HAVING (sum((A.[StoreQty] + A.[ToRetStQty])) > '0')
	ORDER BY A.[ItemInfo_ItemCode] ASC,
		isnull( case  when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') ASC,
		A1.[SPECS] ASC
	OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY

	IF NOT EXISTS (SELECT 1 FROM #TempTableQoh)
	BEGIN
		--INSERT INTO #TempTableQoh
		select A.[ItemInfo_ItemCode] as [Item_ItemCode],
		isnull( case  when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') as [ItemSeg_Name],
		A1.[SPECS] as [ItemID_SPECS],
		sum((((A.[StoreQty] - A.[ResvStQty]) - A.[ResvOccupyStQty]) - case  when ((((A.[IsProdCancel] = 1) or (A.[MO_EntityID] != 0)) or A.[ProductDate] is not null) or (A.[WP_EntityID] != 0)) then A.[StoreQty] else convert(decimal(24,9),0) end )) as [CanUseQty], 
		A2.[Round_Precision] as [Round1_Precision],
		sum((A.[StoreQty] + A.[ToRetStQty])) as [BalQty],
		sum((A.[StoreMainQty] + A.[ToRetStMainQty])) as [BalQty_Main],
		sum((((((A.[StoreQty] - A.[ResvStQty]) - A.[ResvOccupyStQty]) - case  when ((((A.[IsProdCancel] = 1) or (A.[MO_EntityID] != 0)) or A.[ProductDate] is not null) or (A.[WP_EntityID] != 0)) then A.[StoreQty] else convert(decimal(24,9),0) end ) + A.[SupplyQtySU]) - A.[DemandQtySU])) as [Temp_PAB],
		convert(bigint,0) as [Item_ItemID],
		convert(bigint,0) as [W_Uom], 
		convert(bigint,0) as [MainBaseSU_ID] 
		from InvTrans_WhQoh as A 
			left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])  
			left join [Base_UOM] as A2 on (A.[StoreUOM] = A2.[ID])
			left join [Base_Organization] as A3 on (A.[LogisticOrg] = A3.[ID])  
			left join [CBO_Wh] as A4 on (A.[Wh] = A4.[ID])
		where (((((A1.[Name] is not null and (A1.[Name] != '')) 
			and (A3.[Code] = @OrgCode))
			and (A4.[Code] = @WhCode))
			and (A.[ItemInfo_ItemName] LIKE N'%' + @ItemCode + '%' OR A1.[Name] LIKE N'%' + @ItemCode + '%'))
			and (A.[BinInfo_Code] LIKE N'%' + @BinCode + '%'))
		group by A.[ItemInfo_ItemCode], 
			isnull( case  when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,''), 
			A1.[SPECS], 
			A2.[Round_Precision]
		HAVING (sum((A.[StoreQty] + A.[ToRetStQty])) > '0')
		ORDER BY A.[ItemInfo_ItemCode] ASC,
			isnull( case  when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') ASC,
			A1.[SPECS] ASC
		OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY

		PRINT @ItemCode
	END

	select A.Item_ItemCode,
		A.ItemSeg_Name,
		A.ItemID_SPECS, 
		sum(A.CanUseQty) as [CanUseQty],
		A.Round1_Precision,
		sum(A.BalQty) as [BalQty], 
		sum(A.BalQty_Main) as [BalQty_Main],
		sum(A.Temp_PAB) as [Temp_PAB]
	from  #TempTableQoh as A 
	group by A.Item_ItemCode,
		A.ItemSeg_Name, 
		A.ItemID_SPECS, 
		A.Round1_Precision 
	having (sum(A.BalQty) > '0')
	order by A.Item_ItemCode asc,
		A.ItemSeg_Name asc,
		A.ItemID_SPECS asc

END
GO

EXEC SP_Cust_PDA_GetItemMasterWh @OrgCode = N'110', @WhCode = N'110301', @BinCode = N'', @Filter = N'沙发', @PageSize = 15, @PageIndex = 1
--EXEC SP_Cust_PDA_GetItemMasterWh @OrgCode = N'110', @WhCode = N'110301', @BinCode = N'5-01-1-1', @Filter = N'', @PageSize = 3, @PageIndex = 2