USE [CAPDB_CORP_SIT]
GO
/****** Object:  StoredProcedure [dbo].[usp_ProfitMarginCriteriaTempGetList]    Script Date: 4/28/2021 9:08:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Santisuk Kamlangyong>
-- Create date: <2020-01-09>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[usp_ProfitMarginCriteriaMOUTempGetList]
	 @TempId	UNIQUEIDENTIFIER = NULL
	,@ProfitGroupCode NVARCHAR(10) = NULL
	,@TypeCode	NVARCHAR(MAX) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN
		SELECT   TempRunningId
				,RowNo
				,TempId	
				,IsCheck
				,pgmProfitId
				,pgmProMid
				,ProfitGroupCode
				,TypeCode
				,TypeDesc
				,DescCode
				,DescText
				,incFrom
				,incTo
				,profitIncome
				,profitRteCde
				,profitRteNam
				,recsts
				,createBy
				,createDte
				,createWrkstn
				,updateBy
				,updateDte
				,updateWrkstn
				,CountTMP
		FROM (
			SELECT	 CONVERT(INT,ROW_NUMBER() OVER(ORDER BY IsCheck DESC,TypeDesc ASC,incTo ASC)) AS RowNo
					,TempRunningId
					,TMP062.TempId	
					,IsCheck
					,TMP062.pgmProfitId
					,pgmProMid
					,ProfitGroupCode
					,TypeCode
					,TypeDesc
					,DescCode
					,DescText
					,incFrom
					,incTo
					,CONVERT(NVARCHAR(50),incFrom) +' - '+ CONVERT(NVARCHAR(50),incTo) AS profitIncome
					,TMP063.profitRteCde
					,TMP063.profitRteNam
					,recsts
					,createBy
					,createDte
					,createWrkstn
					,updateBy
					,updateDte
					,updateWrkstn
					,TMP.CountTMP
			FROM CBTRTMP062 (NOLOCK) AS TMP062
			LEFT JOIN (
				SELECT	 TempId
						,COUNT(1) AS CountTMP
				FROM CBTRTMP062
				WHERE TempId = @TempId
				AND ProfitGroupCode = @ProfitGroupCode
				AND (@TypeCode IS NULL OR TypeCode IN (SELECT VALUE FROM STRING_SPLIT(@TypeCode,',')))
				GROUP BY TempId
			) AS TMP ON TMP062.TempId = TMP.TempId
			LEFT JOIN (
				SELECT DISTINCT TempId
					,pgmMid
					,pgmProfitId
					,profitRteCde
					,profitRteNam 
				FROM CBTRTMP063 WHERE TempId = @TempId
			) AS TMP063 ON TMP062.TempId = TMP063.TempId AND TMP062.pgmProMid = TMP063.pgmMid AND TMP062.pgmProfitId = TMP063.pgmProfitId
			WHERE TMP062.TempId = @TempId
			AND ProfitGroupCode = @ProfitGroupCode
			AND (@TypeCode IS NULL OR TypeCode IN (SELECT VALUE FROM STRING_SPLIT(@TypeCode,',')))
			--ORDER BY IsCheck DESC,TypeDesc ASC
		) AS TMP
		--ORDER BY TMP.IsCheck,TMP.TypeDesc
	END
END