-- ================================================
-- 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_GetItemMasterWhDetail'))
    DROP PROC SP_Cust_PDA_GetItemMasterWhDetail
GO

CREATE PROCEDURE SP_Cust_PDA_GetItemMasterWhDetail
	-- 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

	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], 
		A2.[Code] as [Pro_Code],
		A7.[Name] as [Pro_Name], 
		A.[SeiBanNo] as [W_SeiBanNo],
		A8.[CombineName] as [BinSeg_CombineName],
		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],
		A4.[Round_Precision] as [Round1_Precision],
		sum((A.[StoreQty] + A.[ToRetStQty])) as [BalQty],
		A3.[Segment1] as [BinSeg_Segment1], 
		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 [CBO_Project] as A2 on (A.[Project] = A2.[ID]) 
		left join [CBO_Bin] as A3 on (A.[BinInfo_Bin] = A3.[ID]) 
		left join [Base_UOM] as A4 on (A.[StoreUOM] = A4.[ID])  
		left join [Base_Organization] as A5 on (A.[LogisticOrg] = A5.[ID]) 
		left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID]) 
		left join [CBO_Project_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A2.[ID] = A7.[ID])
		left join [CBO_Bin_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and (A3.[ID] = A8.[ID]) 
	where (((((A1.[Name] is not null and (A1.[Name] != '')) 
		and (A5.[Code] = @OrgCode))
		and (A6.[Code] = @WhCode)) 
		and (A.[ItemInfo_ItemCode] LIKE N'%' + @Filter + '%'))
		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.[Code],
		A7.[Name],
		A.[SeiBanNo],
		A8.[CombineName],
		A4.[Round_Precision], 
		A3.[Segment1]
	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,
		A2.[Code] ASC,
		A7.[Name] ASC,
		A.[SeiBanNo] ASC,
		A3.[Segment1] ASC
	OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY

	select A.Item_ItemCode,
		A.ItemSeg_Name,
		A.ItemID_SPECS,
		A.Pro_Code,
		A.Pro_Name,
		A.W_SeiBanNo,
		A.BinSeg_CombineName,
		sum(A.CanUseQty) as [CanUseQty], 
		A.Round1_Precision, 
		sum(A.BalQty) as [BalQty],
		A.BinSeg_Segment1,
		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.Pro_Code,
		A.Pro_Name, 
		A.W_SeiBanNo,
		A.BinSeg_CombineName, 
		A.Round1_Precision,
		A.BinSeg_Segment1 
	having (sum(A.BalQty) > '0')
	order by A.Item_ItemCode asc,
		A.ItemSeg_Name asc,
		A.ItemID_SPECS asc,
		A.Pro_Code asc,
		A.Pro_Name asc,
		A.W_SeiBanNo asc,
		A.BinSeg_Segment1 asc

END
GO

--EXEC SP_Cust_PDA_GetItemMasterWhDetail @OrgCode = N'110', @WhCode = N'110301', @BinCode = N'', @Filter = N'M2120601-00035', @PageSize = 15, @PageIndex = 0