/*
Run this script on:

        DESKTOP-Q8R1K35.VCCB_AMS_STABLE    -  This database will be modified

to synchronize it with:

        DESKTOP-Q8R1K35.VCCB_AMS

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.1.6.5463 from Red Gate Software Ltd at 3/27/2023 3:25:59 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQ_PAY_AUTO_GET_REQ_PAYMENT_NOT_ACCOUNTING]'
GO
CREATE PROC [dbo].[TR_REQ_PAY_AUTO_GET_REQ_PAYMENT_NOT_ACCOUNTING]
@p_REQ_PAY_AUTO_TYPE varchar(20) = NULL
AS
BEGIN	
	SELECT A.REQ_PAY_ID
	FROM TR_REQ_PAYMENT A
	WHERE 1=1
	AND A.AUTH_STATUS = 'A'
	AND A.AUTH_STATUS_KT = 'A' 
	AND A.IS_CREATE_AUTO = 'Y' 
	AND A.TYPE_AUTO = @p_REQ_PAY_AUTO_TYPE
	AND (A.IS_CREATE_AUTO_DONE = 'N' OR A.IS_CREATE_AUTO_DONE = NULL)
	AND A.RECORD_STATUS = '1' 

END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQ_PAY_AUTO_UPD_STATUS_DONE_TYPE_B]'
GO
CREATE PROC [dbo].[TR_REQ_PAY_AUTO_UPD_STATUS_DONE_TYPE_B]
@p_REQ_PAY_ID varchar(20) = NULL
AS
BEGIN	
	UPDATE TR_REQ_PAYMENT 
	SET IS_CREATE_AUTO_DONE = 'Y' 
	WHERE 1=1
	AND REQ_PAY_ID = @p_REQ_PAY_ID 
	AND ISNULL(SYSTEM_INVOICE_ID, '') <> ''
	AND ISNULL(IS_CREATE_AUTO, '') = 'Y'
	AND AUTH_STATUS = 'A'
	AND AUTH_STATUS_KT = 'A'
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[DB_STATUS_ASSET_VALUE_BAR_CHECK]'
GO

-- BIEU DO O1 TING TRANG TAI SAN (GIA TRI)
CREATE   PROC [dbo].[DB_STATUS_ASSET_VALUE_BAR_CHECK]
@p_USER_LOGIN VARCHAR(250) = NULL,
@p_TYPE_ID VARCHAR(5) = NULL,
@p_GROUP_ID VARCHAR(15) = NULL,
@p_BRANCH_ID VARCHAR(15) = NULL,
@p_FROM_DATE VARCHAR(100) = NULL,
@p_TO_DATE VARCHAR(100) = NULL,
@p_AMORT_DATE_CHECK VARCHAR(100) = NULL,
@p_USE_DATE_KT_CHECK VARCHAR(100) = NULL,
@p_FILTER VARCHAR(100) = NULL
AS
BEGIN
	DECLARE @BRANCH_LOGIN varchar(15) ='', @DEP_LOGIN varchar(15) =''
	SELECT @DEP_LOGIN = SECUR_CODE, @BRANCH_LOGIN = TLSUBBRID FROM TL_USER WHERE TLNANME = @p_USER_LOGIN

  IF(NOT EXISTS(SELECT * FROM CM_DEPARTMENT cd WHERE cd.BRANCH_ID = @BRANCH_LOGIN AND cd.DEP_ID = @DEP_LOGIN AND (cd.DEP_CODE = '0690604' OR cd.DEP_CODE = '0690802' OR cd.DEP_CODE = '0690803')))
  BEGIN
    IF(@BRANCH_LOGIN = 'DV0001') SET @p_BRANCH_ID = @DEP_LOGIN
    ELSE SET @p_BRANCH_ID = @BRANCH_LOGIN
  END


	IF(@p_TYPE_ID = 'ALL') SET @p_TYPE_ID=''

		--IF(@p_USE_DATE_KT_CHECK = '1')
		--BEGIN
		--	SELECT DISTINCT 
		--		YEAR(USE_DATE_KT) AS [YEAR],ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) AS BUY_PRICE
		--	FROM ASS_MASTER A
		--	WHERE 1=1
		--	AND A.BUY_DATE_KT IS NOT NULL
		--	--AND (CONVERT(date,A.USE_DATE_KT,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
		--	AND (CONVERT(date,A.USE_DATE_KT,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
		--	AND (A.TYPE_ID=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
		--	AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))
		--	AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
		--	GROUP BY YEAR(USE_DATE_KT)
		--	ORDER BY YEAR(USE_DATE_KT) ASC
		--END
		--ELSE IF(@p_AMORT_DATE_CHECK = '1')
		--BEGIN
		--	SELECT DISTINCT 
		--		YEAR(AMORT_START_DATE) AS [YEAR],ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) AS BUY_PRICE
		--	FROM ASS_MASTER A
		--	WHERE 1=1
		--	AND A.BUY_DATE_KT IS NOT NULL
		--	--AND (CONVERT(date,A.AMORT_START_DATE,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
		--	AND (CONVERT(date,A.AMORT_START_DATE,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
		--	AND (A.TYPE_ID=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
		--	AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))
		--	AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
		--	GROUP BY YEAR(AMORT_START_DATE)
		--	ORDER BY YEAR(AMORT_START_DATE) ASC
		--END
		
		DECLARE @YEAR INT
		DECLARE @TABLE TABLE(
			USE_DATE_KT NVARCHAR(100) NULL,
			BUY_PRICE DECIMAL(18,2) NULL,
			[YEAR] VARCHAR(10) NULL
		)
		DECLARE @TBALE_YEAR_FILTER TABLE(
			[YEAR] VARCHAR(10) 
		)
		DECLARE @YEAR_IN INT = YEAR(CONVERT(date,@p_TO_DATE,103))
		INSERT INTO @TBALE_YEAR_FILTER VALUES(YEAR(CONVERT(date,@p_TO_DATE,103)))
		WHILE(@YEAR_IN > YEAR(CONVERT(date,@p_FROM_DATE,103)))
		BEGIN
			SET @YEAR_IN = @YEAR_IN - 1
			INSERT INTO @TBALE_YEAR_FILTER VALUES(@YEAR_IN)
		END
		SELECT [YEAR],0 AS BUY_PRICE FROM @TBALE_YEAR_FILTER ORDER BY [YEAR] 
		DECLARE CUR_FILTER_YEAR CURSOR FOR
		SELECT [YEAR] FROM @TBALE_YEAR_FILTER
		--SELECT DISTINCT 
		--	CASE WHEN  @p_USE_DATE_KT_CHECK = '1' THEN YEAR(USE_DATE_KT) 
		--		ELSE YEAR(AMORT_START_DATE) END
		--	AS USE_DATE_KT
		--FROM ASS_MASTER A WHERE 1=1
		--AND USE_DATE_KT IS NOT NULL
		--AND ((@p_USE_DATE_KT_CHECK = '1' AND (CONVERT(date,A.USE_DATE_KT,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='') AND (CONVERT(date,A.USE_DATE_KT,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE =''))
		--	OR (@p_AMORT_DATE_CHECK = '1' AND (CONVERT(date,A.AMORT_START_DATE,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='') AND (CONVERT(date,A.AMORT_START_DATE,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE =''))
		--	)
		--AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
		--AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))
		--AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
		OPEN CUR_FILTER_YEAR

		IF(@p_USE_DATE_KT_CHECK = '1')
		BEGIN
			FETCH NEXT FROM CUR_FILTER_YEAR
				  INTO @YEAR

			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO @TABLE(USE_DATE_KT,BUY_PRICE,[YEAR])
				(
					SELECT N'0 - 3 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A
                    LEFT JOIN
            	(
            		SELECT X.ASSET_ID,ISNULL(SUM(CASE X.CRDR  WHEN 'C' THEN X.ASSET_AMT WHEN 'D' THEN -X.ASSET_AMT END),0) AS PHAT_SINH_DAU 
            		FROM ASS_VALUES X
            		INNER JOIN ASS_MASTER Y ON X.ASSET_ID = Y.ASSET_ID
            		WHERE (X.TRN_DT < CONVERT(DATE,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE = '') 
            		--(X.CREATE_DT >= Y.CREATE_DT) 
            		--AND (X.CREATE_DT <= CONVERT(DATETIME,@p_Fromdate,103) OR @p_Fromdate IS NULL OR @p_Fromdate = '')
            		GROUP BY X.ASSET_ID
            	) CC ON A.ASSET_ID = CC.ASSET_ID                 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
					--AND (CONVERT(date,A.USE_DATE_KT,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
					AND ((A.[TYPE_ID] = 'TSCD' AND  CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,@p_FROM_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 
							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,@p_TO_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
					AND (CONVERT(date,A.USE_DATE_KT,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='' OR (A.TYPE_ID ='TSCD' AND  (A.USE_DATE_KT IS NULL OR A.USE_DATE_KT ='')))
					AND (@YEAR-YEAR(USE_DATE_KT) >= 0 OR (A.TYPE_ID ='TSCD' AND  (A.USE_DATE_KT IS NULL OR A.USE_DATE_KT ='')))
					AND (@YEAR-YEAR(USE_DATE_KT) <= 3 OR (A.TYPE_ID ='TSCD' AND  (A.USE_DATE_KT IS NULL OR A.USE_DATE_KT ='')))
					AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
				AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,1) =@p_GROUP_ID) 
							OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,2) =@p_GROUP_ID) OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,3) =@p_GROUP_ID))
					AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID OR (A.TYPE_ID ='TSCD' AND ISNULL(A.BRANCH_CREATE,'') =@p_BRANCH_ID))
				)
				UNION ALL
				(
					SELECT N'3 - 5 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A
					WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
					--AND (CONVERT(date,A.USE_DATE_KT,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
					AND ((A.[TYPE_ID] = 'TSCD' AND  CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,@p_FROM_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 
							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,@p_TO_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
					AND (CONVERT(date,A.USE_DATE_KT,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
					AND (@YEAR-YEAR(USE_DATE_KT) > 3)
					AND (@YEAR-YEAR(USE_DATE_KT) <= 5)
					AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
				AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,1) =@p_GROUP_ID) 
							OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,2) =@p_GROUP_ID) OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,3) =@p_GROUP_ID))
					AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID OR (A.TYPE_ID ='TSCD' AND ISNULL(A.BRANCH_CREATE,'') =@p_BRANCH_ID))
				)
				UNION ALL
				(
					SELECT N'Trên 5 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A
					WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
					--AND (CONVERT(date,A.USE_DATE_KT,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
					AND ((A.[TYPE_ID] = 'TSCD' AND  CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,@p_FROM_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 
							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,@p_TO_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
					AND (CONVERT(date,A.USE_DATE_KT,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
					AND (@YEAR-YEAR(USE_DATE_KT) > 5)
					AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
				AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,1) =@p_GROUP_ID) 
							OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,2) =@p_GROUP_ID) OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,3) =@p_GROUP_ID))
					AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID OR (A.TYPE_ID ='TSCD' AND ISNULL(A.BRANCH_CREATE,'') =@p_BRANCH_ID))
				)

				FETCH NEXT FROM CUR_FILTER_YEAR
					  INTO @YEAR
			END
		END
		ELSE IF(@p_AMORT_DATE_CHECK = '1')
		BEGIN 
			FETCH NEXT FROM CUR_FILTER_YEAR
				  INTO @YEAR

			WHILE @@FETCH_STATUS = 0
			BEGIN
				INSERT INTO @TABLE(USE_DATE_KT,BUY_PRICE,[YEAR])
				(
					SELECT N'0 - 3 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A
					WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
					--AND (CONVERT(date,A.AMORT_START_DATE,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
					AND ((A.[TYPE_ID] = 'TSCD' AND  CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,@p_FROM_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 
							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,@p_TO_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
					AND (CONVERT(date,A.AMORT_START_DATE,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
					AND (@YEAR-YEAR(AMORT_START_DATE) >= 0)
					AND (@YEAR-YEAR(AMORT_START_DATE) <= 3)
					AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
					AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,1) =@p_GROUP_ID) 
							OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,2) =@p_GROUP_ID) OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,3) =@p_GROUP_ID))
					AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
				)
				UNION ALL
				(
					SELECT N'3 - 5 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A
					WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
					--AND (CONVERT(date,A.AMORT_START_DATE,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
					AND ((A.[TYPE_ID] = 'TSCD' AND  CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,@p_FROM_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 
							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,@p_TO_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
					AND (CONVERT(date,A.AMORT_START_DATE,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
					AND (@YEAR-YEAR(AMORT_START_DATE) > 3)
					AND (@YEAR-YEAR(AMORT_START_DATE) <= 5)
					AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
					AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,1) =@p_GROUP_ID) 
							OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,2) =@p_GROUP_ID) OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,3) =@p_GROUP_ID))
					AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
				)
				UNION ALL
				(
					SELECT N'Trên 5 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A
					WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y'
					--AND (CONVERT(date,A.AMORT_START_DATE,103) >= CONVERT(date,@p_FROM_DATE,103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE ='')
					AND ((A.[TYPE_ID] = 'TSCD' AND  CONVERT(date,A.LIQUIDATION_DT,103) >= CONVERT(date,@p_FROM_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 
							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  CONVERT(date,A.LIQUIDATION_DT,103) > CONVERT(date,@p_TO_DATE,103) OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))
					AND (CONVERT(date,A.AMORT_START_DATE,103) <= CONVERT(date,@p_TO_DATE,103) OR @p_TO_DATE IS NULL OR @p_TO_DATE ='')
					AND (@YEAR-YEAR(AMORT_START_DATE) > 5)
					AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')
					AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,1) =@p_GROUP_ID) 
							OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,2) =@p_GROUP_ID) OR (DBO.[FN_GET_GROUPID] (A.GROUP_ID,3) =@p_GROUP_ID))
					AND (A.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
				)

				FETCH NEXT FROM CUR_FILTER_YEAR
					  INTO @YEAR
			END
		END
		CLOSE CUR_FILTER_YEAR
		DEALLOCATE CUR_FILTER_YEAR

		SELECT * FROM @TABLE	
		ORDER BY [YEAR] ASC
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQ_PAYMENT_Search_2]'
GO

CREATE   PROCEDURE [dbo].[TR_REQ_PAYMENT_Search_2]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	nvarchar(20)	= NULL,
@p_EXEC_USER_KT	nvarchar(20)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CONFIRM_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15) = NULL,
@p_TOP INT = 300,
@p_LEVEL varchar(10) = NULL,
@p_FRMDATE VARCHAR(20)= NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
@P_IS_TRANSFER VARCHAR(15) = NULL,
@p_TERM_ID VARCHAR(15) = NULL,
@P_USER_LOGIN VARCHAR(15)= NULL,
@p_FUNCTION VARCHAR(15) = NULL,
@p_TYPE_SEARCH VARCHAR(15) = NULL
AS
BEGIN -- PAGING
--SET @p_TOP = NULL
IF(ISNULL(@p_REQ_PAY_ID, '') <> '')
BEGIN
	SET @p_DEP_ID = NULL
END
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
declare @tmp_Login table(BRANCH_ID varchar(15))
insert into @tmp_Login  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
DECLARE @ROLE_ID VARCHAR(20) , @DEP_ID_LG VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15)
DECLARE @BRANCH_TYPE VARCHAR(15)
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
DECLARE @TMP_DEP TABLE(DEP_ID VARCHAR(15))
SET @COST_LG =(SELECT TOP 1 COST_ID FROM PL_COSTCENTER_DT WHERE DEP_ID =@DEP_ID_LG)
SET @DVDM_ID =(SELECT TOP 1 DVDM_ID FROM PL_COSTCENTER WHERE COST_ID =@COST_LG)
INSERT INTO @TMP_DEP SELECT B.DEP_ID FROM PL_COSTCENTER_DT B  WHERE COST_ID =@COST_LG
DECLARE @BRANCH_TYPE_LG VARCHAR(15)
SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
-- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  
INSERT INTO @TABLE_ROLE VALUES (@ROLE_ID)
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID AND TLNAME =@P_USER_LOGIN  
AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))
INSERT INTO @DEP_AUTH VALUES (@DEP_ID_LG)
INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN AND 
CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
--- LUCTV 19.10.2022 BO SUNG THEM PHONG BAN CHA SE THAY DANH SACH PHONG BAN CON
INSERT INTO @DEP_AUTH SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@DEP_ID_LG
-- KHAI BAO PHẦN ỦY QUYỀN ĐƠN VỊ
DECLARE @BRANCH_AUTH TABLE (BRN_AUTH VARCHAR(15))
INSERT INTO @BRANCH_AUTH VALUES (@p_BRANCH_LOGIN)
INSERT INTO @BRANCH_AUTH SELECT BRANCH_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN AND 
CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
--- NEU USER KHONG CHON TU NGAY THI TU NGAY BANG NGAY 1 1 HANG THANG 20211116
DECLARE @DATE DATE
IF ((@p_FRMDATE IS NULL OR @p_FRMDATE ='') AND ISNULL(@p_REQ_PAY_ID,'')='' AND @p_AUTH_STATUS ='') -- MỤC ĐÍCH NẾU NHƯ XEM CHI TIẾT 1 PĐN THANH TOÁN DẠNG POPUP THÌ BỎ QUA ĐIỀU KIỆN NÀY, TỪ NGÀY VẪN LÀ NULL
BEGIN
	--DECLARE @MONTH INT, @YEAR INT
	--SET @MONTH = MONTH(GETDATE()) -1
	--SET @YEAR = YEAR(GETDATE())
	--BEGIN
	--	SET @p_FRMDATE ='01/'+CONVERT(VARCHAR,@MONTH,5) +'/'+ CONVERT(VARCHAR,@YEAR,5)
	--END
	SET @DATE = CONVERT(DATE,GETDATE(),103)
	SET @DATE = DATEADD(MONTH,-2,@DATE)
END
ELSE
BEGIN
	SET @DATE = CONVERT(DATE,@p_FRMDATE,103)
END
	--DOANPTT: XAC DINH USER CO DUOC XEM HET PHIEU DE NGHI THANH TOAN CUA PHONG MINH HAY KHONG
	DECLARE @IS_SEE_ALL_HC VARCHAR(1) 
	IF((SELECT COUNT(*) FROM PL_ROLE_DATA_CONFIG WHERE BRANCH_ID = @P_USER_LOGIN AND ROLE_TYPE = 'TR_REQ_PAYMENT') > 0)
	BEGIN
		SET @IS_SEE_ALL_HC = 'Y'
	END
	ELSE
	BEGIN
		SET @IS_SEE_ALL_HC = 'N'
	END
-- HẾT KHAI BÁO
SET  @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD'))
BEGIN
	PRINT @ROLE_ID
END
ELSE
BEGIN
	SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)
	IF(@ROLE_ID IS NULL OR @ROLE_ID ='')
	BEGIN
			SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN))
	END
END
INSERT INTO @TABLE_ROLE VALUES (@ROLE_ID)--2021823
--SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)
IF(@p_TOP IS NULL OR @p_TOP=0)
BEGIN
-- PAGING BEGIN
		SELECT A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, C.AUTH_STATUS_KT_DESC,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL8.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		--BR.BRANCH_NAME AS BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
		--CASE WHEN PC3.TLNAME IS NOT NULL AND PC3.TLNAME <>'' THEN @P_USER_LOGIN ELSE PC3.TLNAME END AS EXEC_USER,
		@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,
		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		--doanptt 300622
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
			 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
			 ELSE TL2.TLNANME END AS EXEC_USER,
		dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.TRANSFER_DT, GETDATE()) AS NUMBER_OF_TRANSFER,
		PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN ISNULL(A.CREATE_DT_KT, '') <> '' AND A.AUTH_STATUS_KT IN('P', 'S')  THEN dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.CREATE_DT_KT, GETDATE())
			 ELSE 0 END AS NUMBER_OF_SEND_APPR
-- SELECT END
        FROM TR_REQ_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE ='REQ'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN 
			 (
			 SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			 LEFT JOIN TL_USER TL8 ON PC5.TLNAME = TL8.TLNANME
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			 LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
        WHERE 1=1 
			  AND A.IS_CREATE_AUTO = 'Y'
			  AND A.IS_CREATE_AUTO_DONE = 'Y'
			  AND(A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL)
			  AND(	A.AUTH_STATUS=@p_AUTH_STATUS 
					OR @p_AUTH_STATUS='' 
					OR @p_AUTH_STATUS IS NULL 
					OR	(	@p_AUTH_STATUS = 'G' 
							AND ISNULL(A.PROCESS, '') = '0' 
							AND A.AUTH_STATUS = 'U'
							AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
						) 
					OR	(	@p_AUTH_STATUS = 'W' 
							AND ISNULL(A.PROCESS, '') = '' 
							AND A.AUTH_STATUS = 'U'
							AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
						)
				)
			  AND(A.MAKER_ID =@p_MAKER_ID OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
			  AND(A.MAKER_ID_KT = @p_MAKER_ID_KT OR @p_MAKER_ID_KT = '' OR @p_MAKER_ID_KT IS NULL OR PC3.TLNAME = @p_MAKER_ID_KT)
			  AND(PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
			  AND(A.DEP_ID=@p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND	(	(	@p_LEVEL='ALL' AND	(	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
													OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
												)
						) -- LUCTV 20211012: BO SUNG CAU HINH DE XU LY UY QUYEN KIEM NHIEM
						OR	(	(	@p_LEVEL='UNIT' 
									AND A.BRANCH_ID=@p_BRANCH_ID
								)
								OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL) 
								OR (	@BRANCH_TYPE_LG <> 'HS' AND
										EXISTS	(	SELECT * 
													FROM TR_REQ_ADVANCE_DT 
													WHERE REQ_PAY_ID = A.REQ_PAY_ID
													AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
													AND AUTH_STATUS_KT ='A'
												)
									)
							)
					)
			  AND	(	(	@p_LEVEL='ALL' 
							AND (	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
									OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
								)
						) -- - LUCTV 20211012: BO SUNG CAU HINH DE XU LY UY QUYEN KIEM NHIEM
						OR	(	(	@p_LEVEL='UNIT' 
									AND A.BRANCH_ID=@p_BRANCH_ID
								)
								OR	(	@p_BRANCH_ID='' 
										OR @p_BRANCH_ID IS NULL
									)
																 OR	(	@BRANCH_TYPE_LG <> 'HS' 
																		AND EXISTS	(	SELECT * 
																						FROM TR_REQ_ADVANCE_DT 
																						WHERE REQ_PAY_ID = A.REQ_PAY_ID 
																						AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
																						AND AUTH_STATUS_KT ='A'
																					)
																	)
							)
					)
			  AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
						OR	(	(	A.MAKER_ID_KT IS NULL 
									AND @p_IS_UPDATE_KT='N'
								)
							)
							OR @p_IS_UPDATE_KT IS NULL 
							OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @DATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL)
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL)
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
			  --AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  AND	(	(@p_BRANCH_ID <> @p_BRANCH_LOGIN AND A.BRANCH_ID = @p_BRANCH_ID) 
						OR @p_BRANCH_ID = @p_BRANCH_LOGIN 
						OR @p_BRANCH_ID='' 
						OR @p_BRANCH_ID IS NULL
					)
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND	(	(	A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT 
							OR @p_AUTH_STATUS_KT='' 
							OR @p_AUTH_STATUS_KT IS NULL
						) 
						OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S'))
					)
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  --AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              --OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
			  -- Neu ben phan he ke toan thi chi lay nhung user co maker_id khac null
			  AND  (
						(
							@p_FUNCTION ='KT' 
							AND (
									EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE REQ_ID = A.REQ_PAY_ID 
									AND (
											X.TLNAME= @p_USER_LOGIN 
											OR X.TLNAME =@p_EXEC_USER_KT)
										)
								) 
							OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
							OR A.CHECKER_ID_KT ='admin' 
							OR A.AUTH_STATUS_KT='A' 
						)
						OR @p_FUNCTION ='' 
						OR @p_FUNCTION IS NULL 
						OR @p_FUNCTION ='TF' 
					)	  
				AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='' OR A.MAKER_ID_KT =@p_TRASFER_USER_RECIVE)
			 -- AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='')
				AND	(	(	@p_IS_TRANSFER='Y' 
							AND	(	EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) 
									OR A.AUTH_STATUS_KT ='A'
								)
						) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
						OR	(	(	@p_IS_TRANSFER='N' 
									AND (	NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID )) 
											AND A.AUTH_STATUS_KT <>'A'
								)
							)
						OR @p_IS_TRANSFER IS NULL 
						OR @p_IS_TRANSFER=''
					)	
				AND
				( /*0*/
				  A.MAKER_ID =@p_USER_LOGIN  
				  OR (A.DEP_ID = @p_DEP_ID)
				  OR ( A.TRASFER_USER_RECIVE = @P_USER_LOGIN 
						AND A.AUTH_STATUS NOT IN ('E','R')
					 )
				  OR	(	(	@IS_SEE_ALL_HC = 'Y'			--DOANPTT 20/12/22: CHO A XUAN XEM TOAN BO PHIEU THUOC PHONG HANH CHINH
								AND A.DEP_ID = @DEP_ID_LG
								AND A.BRANCH_ID = 'DV0001'
								AND A.AUTH_STATUS IN ('U','R', 'A')
								AND @p_TYPE_SEARCH ='HC'
							)
							OR	(	@IS_SEE_ALL_HC = 'Y'			--DOANPTT 20/12/22: CHO A XUAN XEM TOAN BO PHIEU THUOC PHONG HANH CHINH
									AND A.BRANCH_ID = @p_BRANCH_LOGIN
									AND A.AUTH_STATUS IN ('U','R', 'A')
									AND @p_TYPE_SEARCH ='HC'
								)
						)
				  OR (	A.AUTH_STATUS <>'E' /*1*/
						AND
						(/*2*/
							(	@p_TYPE_SEARCH ='HC'		/*3*/ 
								AND @BRANCH_TYPE_LG ='HS'  
								AND (/*4*/A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
										OR (/*5*/
												EXISTS (	SELECT * 
															FROM @TABLE_ROLE 
															WHERE ROLE_AUTH IN ('GDDV','GDK','KTT','TPTC','TP','PP','TC')
													   )
												AND    (
															(	A.BRANCH_ID =@p_BRANCH_LOGIN 
																OR A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
															) 
															AND (	A.DEP_ID = @DEP_ID_LG 
																	OR  ( 
																			A.DEP_ID  IN ('DEP000000000051','DEP000000000733') 
																			AND @p_USER_LOGIN ='cuongpv2'
																		) 
																	OR A.DEP_ID IN (
																						SELECT * 
																						FROM @DEP_AUTH
																					)
																)
														)
												AND ( 
														A.TRASFER_USER_RECIVE IS NULL 
														OR A.TRASFER_USER_RECIVE ='' 
														OR (
																A.TRASFER_USER_RECIVE IS NOT NULL 
																AND A.TRASFER_USER_RECIVE <>'' 
																AND A.PROCESS IS NOT NULL 
																AND A.PROCESS <>''
															)
													)
											)/*5*/
										)/*4*/
								)/*3*/
							OR(/*trong 2*/
									@p_TYPE_SEARCH ='HC' 
									AND @BRANCH_TYPE_LG = 'CN' 
									AND (
											@ROLE_ID IN ('GDDV','PGD','TPGD','PPGD') 
											AND A.BRANCH_ID =@p_BRANCH_LOGIN 
											AND
												(
													(
														A.TRASFER_USER_RECIVE IS NOT NULL 
														AND A.TRASFER_USER_RECIVE <>'' 
														AND A.PROCESS IS NOT NULL 
														AND A.PROCESS <>''
													 ) 
													OR A.TRASFER_USER_RECIVE ='' 
													OR A.TRASFER_USER_RECIVE IS NULL
												 )
											OR (
													(
														A.BRANCH_CREATE <> @p_BRANCH_LOGIN 
														AND A.TRASFER_USER_RECIVE IS NOT NULL 
															AND A.TRASFER_USER_RECIVE <>''
													) 
													OR A.BRANCH_CREATE = @p_BRANCH_LOGIN
												)
											AND A.BRANCH_ID =@p_BRANCH_ID
										)
								)
							 OR
								(
									@p_TYPE_SEARCH ='HC' 
									AND @BRANCH_TYPE_LG = 'PGD' 
									AND (
											@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') 
											AND A.BRANCH_ID =@p_BRANCH_LOGIN
										)
								)
							 OR
								(
									@p_TYPE_SEARCH ='HC' 
									AND(
											@ROLE_ID IN ('KSV','GDV','NVTC') 
											OR @DEP_ID_LG ='DEP000000000022'
										) 
									AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
								)
							OR	(
									@p_TYPE_SEARCH='KT' 
									AND @p_BRANCH_LOGIN ='DV0001' 
									AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
								)
							OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH =''
						)/*2*/
				)/*1*/
				  OR
				  (
						(
							@ROLE_ID IN ('KSV','GDV','NVTC') 
							OR @DEP_ID_LG ='DEP000000000022'
						) 
						AND @p_BRANCH_LOGIN ='DV0001' 
						AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
				  )
				  -- NEU DUOC UY QUYEN DUYỆT CHO 1 ĐƠN VỊ KHÁC HỘI SỞ THÌ CHỈ SETUP ĐƠN VỊ, KHÔNG CẦN SETUP PHÒNG BAN
				  OR(	@p_TYPE_SEARCH ='HC' 
						AND A.BRANCH_CREATE <> 'DV0001' 
						AND A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
					)
				  -- LUCTV 25 05 BO SUNG CAU HINH CHO PHEP DVKD DUOC THAY NHUNG PDN THANH TOAN DO HO TẠO
				  OR (
						@p_TYPE_SEARCH ='HC' 
						AND @BRANCH_TYPE_LG <> 'HS' 
						AND
						EXISTS (
									SELECT * 
									FROM TR_REQ_ADVANCE_DT 
									WHERE REQ_PAY_ID = A.REQ_PAY_ID AND REF_ID IN (
																					SELECT CONTRACT_ID 
																					FROM TR_CONTRACT
																					WHERE BRANCH_ID =@p_BRANCH_LOGIN
																				  ) 
									AND AUTH_STATUS_KT ='A'
								)
					)
			 )/*0*/
		ORDER BY A.CREATE_DT DESC
-- PAGING END
    END;
    ELSE 
	BEGIN
-- PAGING BEGIN
        SELECT TOP(CONVERT(INT,@p_TOP)) A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, C.AUTH_STATUS_KT_DESC,
		TL1.TLFullName APPROVE_FULLNAME,
		CASE WHEN ISNULL(TL2.TLFullName,'') <>'' THEN TL2.TLFullName ELSE TL8.TLFullName END AS CREATE_FULLNAME_KT,
		TL3.TLFullName APPROVE_FULLNAME_KT,
		CASE WHEN ISNULL(A.IS_PERIOD,'') <> '' AND A.IS_PERIOD ='Y' AND  EXISTS (SELECT TOP 1 X.* FROM TR_REQ_ADVANCE_DT X 
										INNER JOIN TR_CONTRACT CT ON X.REF_ID = CT.CONTRACT_ID
										WHERE X.REQ_PAY_ID = A.REQ_PAY_ID AND CT.BRANCH_ID <>  @p_BRANCH_LOGIN AND CT.BRANCH_ID IS NOT NULL AND CT.BRANCH_ID <> '') THEN 
										BR.BRANCH_NAME + ISNULL(' - '+ (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT 
										WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
										ELSE BR.BRANCH_NAME END BRANCH_NAME_REQ,
		--BR.BRANCH_NAME AS BRANCH_NAME_REQ,
		BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
		--CASE WHEN PC3.TLNAME IS NOT NULL AND PC3.TLNAME <>'' THEN @P_USER_LOGIN ELSE PC3.TLNAME END AS EXEC_USER,
		@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR,
		BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE,'' AS BRANCH_CODE_CONTRACT,
		ISNULL((SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID IN (SELECT TOP 1 REF_ID FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID = A.REQ_PAY_ID))),'')
		AS BRANCH_NAME_CONTRACT,
		--doanptt 300622
		CASE WHEN PC3.TLNAME IS NOT NULL THEN PC3.TLNAME 
			 WHEN PC3.TLNAME <>'' THEN PC3.TLNAME 
			 ELSE TL2.TLNANME END AS EXEC_USER,
		dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.TRANSFER_DT, GETDATE()) AS NUMBER_OF_TRANSFER,
		PC3.TLNAME AS EXEC_USER_KT,
		CASE WHEN ISNULL(A.CREATE_DT_KT, '') <> '' AND A.AUTH_STATUS_KT IN('P', 'S')  THEN dbo.FN_DATEDIFF_WITHOUT_WEEKEND(A.CREATE_DT_KT, GETDATE())
			 ELSE 0 END AS NUMBER_OF_SEND_APPR
-- SELECT END
        FROM TR_REQ_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE' AND AL.CDTYPE = 'REQ'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' AND AL1.CDTYPE = 'REQ'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN 
			 (
			 SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC5 ON PC5.REQ_ID = A.REQ_PAY_ID AND PC5.TYPE_JOB ='XL'
			 LEFT JOIN TL_USER TL8 ON PC5.TLNAME = TL8.TLNANME
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
			 LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID
        WHERE 1=1 
			  AND A.IS_CREATE_AUTO = 'Y'
			  AND A.IS_CREATE_AUTO_DONE = 'Y'
			  AND(A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL)
			  AND(	A.AUTH_STATUS=@p_AUTH_STATUS 
					OR @p_AUTH_STATUS='' 
					OR @p_AUTH_STATUS IS NULL 
					OR	(	@p_AUTH_STATUS = 'G' 
							AND ISNULL(A.PROCESS, '') = '0' 
							AND A.AUTH_STATUS = 'U'
							AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
						) 
					OR	(	@p_AUTH_STATUS = 'W' 
							AND ISNULL(A.PROCESS, '') = '' 
							AND A.AUTH_STATUS = 'U'
							AND @P_USER_LOGIN = A.TRASFER_USER_RECIVE
						)
				)
			  AND(A.MAKER_ID =@p_MAKER_ID OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
			  AND(A.MAKER_ID_KT = @p_MAKER_ID_KT OR @p_MAKER_ID_KT = '' OR @p_MAKER_ID_KT IS NULL OR PC3.TLNAME = @p_MAKER_ID_KT)
			  AND(PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
			  AND(A.DEP_ID=@p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND	(	(	@p_LEVEL='ALL' AND	(	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
													OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
												)
						) -- LUCTV 20211012: BO SUNG CAU HINH DE XU LY UY QUYEN KIEM NHIEM
						OR	(	(	@p_LEVEL='UNIT' 
									AND A.BRANCH_ID=@p_BRANCH_ID
								)
								OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL) 
								OR (	@BRANCH_TYPE_LG <> 'HS' AND
										EXISTS	(	SELECT * 
													FROM TR_REQ_ADVANCE_DT 
													WHERE REQ_PAY_ID = A.REQ_PAY_ID
													AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
													AND AUTH_STATUS_KT ='A'
												)
									)
							)
					)
			  AND	(	(	@p_LEVEL='ALL' 
							AND (	A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp) 
									OR A.BRANCH_CREATE IN (SELECT * FROM @BRANCH_AUTH)
								)
						) -- - LUCTV 20211012: BO SUNG CAU HINH DE XU LY UY QUYEN KIEM NHIEM
						OR	(	(	@p_LEVEL='UNIT' 
									AND A.BRANCH_ID=@p_BRANCH_ID
								)
								OR	(	@p_BRANCH_ID='' 
										OR @p_BRANCH_ID IS NULL
									)
																 OR	(	@BRANCH_TYPE_LG <> 'HS' 
																		AND EXISTS	(	SELECT * 
																						FROM TR_REQ_ADVANCE_DT 
																						WHERE REQ_PAY_ID = A.REQ_PAY_ID 
																						AND REF_ID IN (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE BRANCH_ID =@p_BRANCH_LOGIN) 
																						AND AUTH_STATUS_KT ='A'
																					)
																	)
							)
					)
			  AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
						OR	(	(	A.MAKER_ID_KT IS NULL 
									AND @p_IS_UPDATE_KT='N'
								)
							)
							OR @p_IS_UPDATE_KT IS NULL 
							OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @DATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL)
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL)
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
			  --AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  AND	(	(@p_BRANCH_ID <> @p_BRANCH_LOGIN AND A.BRANCH_ID = @p_BRANCH_ID) 
						OR @p_BRANCH_ID = @p_BRANCH_LOGIN 
						OR @p_BRANCH_ID='' 
						OR @p_BRANCH_ID IS NULL
					)
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND	(	(	A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT 
							OR @p_AUTH_STATUS_KT='' 
							OR @p_AUTH_STATUS_KT IS NULL
						) 
						OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S'))
					)
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  --AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              --OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
			  -- Neu ben phan he ke toan thi chi lay nhung user co maker_id khac null
			  AND  (
						(
							@p_FUNCTION ='KT' 
							AND (
									EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE REQ_ID = A.REQ_PAY_ID 
									AND (
											X.TLNAME= @p_USER_LOGIN 
											OR X.TLNAME =@p_EXEC_USER_KT)
										)
								) 
							OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
							OR A.CHECKER_ID_KT ='admin' 
							OR A.AUTH_STATUS_KT='A' 
						)
						OR @p_FUNCTION ='' 
						OR @p_FUNCTION IS NULL 
						OR @p_FUNCTION ='TF' 
					)	  
				AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='' OR A.MAKER_ID_KT =@p_TRASFER_USER_RECIVE)
			 -- AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='')
				AND	(	(	@p_IS_TRANSFER='Y' 
							AND	(	EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) 
									OR A.AUTH_STATUS_KT ='A'
								)
						) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
						OR	(	(	@p_IS_TRANSFER='N' 
									AND (	NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID )) 
											AND A.AUTH_STATUS_KT <>'A'
								)
							)
						OR @p_IS_TRANSFER IS NULL 
						OR @p_IS_TRANSFER=''
					)	
				AND
				( /*0*/
				  A.MAKER_ID =@p_USER_LOGIN  
				  OR ( A.TRASFER_USER_RECIVE = @P_USER_LOGIN 
						AND A.AUTH_STATUS NOT IN ('E','R')
					 )
				  OR	(	(	@IS_SEE_ALL_HC = 'Y'			--DOANPTT 20/12/22: CHO A XUAN XEM TOAN BO PHIEU THUOC PHONG HANH CHINH
								AND A.DEP_ID = @DEP_ID_LG
								AND A.BRANCH_ID = 'DV0001'
								AND A.AUTH_STATUS IN ('U','R', 'A')
								AND @p_TYPE_SEARCH ='HC'
							)
							OR	(	@IS_SEE_ALL_HC = 'Y'			--DOANPTT 20/12/22: CHO A XUAN XEM TOAN BO PHIEU THUOC PHONG HANH CHINH
									AND A.BRANCH_ID = @p_BRANCH_LOGIN
									AND A.AUTH_STATUS IN ('U','R', 'A')
									AND @p_TYPE_SEARCH ='HC'
								)
						)
				  OR (	A.AUTH_STATUS <>'E' /*1*/
						AND
						(/*2*/
							(	@p_TYPE_SEARCH ='HC'		/*3*/ 
								AND @BRANCH_TYPE_LG ='HS'  
								AND (/*4*/A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
										OR (/*5*/
												EXISTS (	SELECT * 
															FROM @TABLE_ROLE 
															WHERE ROLE_AUTH IN ('GDDV','GDK','KTT','TPTC','TP','PP','TC')
													   )
												AND    (
															(	A.BRANCH_ID =@p_BRANCH_LOGIN 
																OR A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
															) 
															AND (	A.DEP_ID = @DEP_ID_LG 
																	OR  ( 
																			A.DEP_ID  IN ('DEP000000000051','DEP000000000733') 
																			AND @p_USER_LOGIN ='cuongpv2'
																		) 
																	OR A.DEP_ID IN (
																						SELECT * 
																						FROM @DEP_AUTH
																					)
																)
														)
												AND ( 
														A.TRASFER_USER_RECIVE IS NULL 
														OR A.TRASFER_USER_RECIVE ='' 
														OR (
																A.TRASFER_USER_RECIVE IS NOT NULL 
																AND A.TRASFER_USER_RECIVE <>'' 
																AND A.PROCESS IS NOT NULL 
																AND A.PROCESS <>''
															)
													)
											)/*5*/
										)/*4*/
								)/*3*/
							OR(/*trong 2*/
									@p_TYPE_SEARCH ='HC' 
									AND @BRANCH_TYPE_LG = 'CN' 
									AND (
											@ROLE_ID IN ('GDDV','PGD','TPGD','PPGD') 
											AND A.BRANCH_ID =@p_BRANCH_LOGIN 
											AND
												(
													(
														A.TRASFER_USER_RECIVE IS NOT NULL 
														AND A.TRASFER_USER_RECIVE <>'' 
														AND A.PROCESS IS NOT NULL 
														AND A.PROCESS <>''
													 ) 
													OR A.TRASFER_USER_RECIVE ='' 
													OR A.TRASFER_USER_RECIVE IS NULL
												 )
											OR (
													(
														A.BRANCH_CREATE <> @p_BRANCH_LOGIN 
														AND A.TRASFER_USER_RECIVE IS NOT NULL 
															AND A.TRASFER_USER_RECIVE <>''
													) 
													OR A.BRANCH_CREATE = @p_BRANCH_LOGIN
												)
											AND A.BRANCH_ID =@p_BRANCH_ID
										)
								)
							 OR
								(
									@p_TYPE_SEARCH ='HC' 
									AND @BRANCH_TYPE_LG = 'PGD' 
									AND (
											@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') 
											AND A.BRANCH_ID =@p_BRANCH_LOGIN
										)
								)
							 OR
								(
									@p_TYPE_SEARCH ='HC' 
									AND(
											@ROLE_ID IN ('KSV','GDV','NVTC') 
											OR @DEP_ID_LG ='DEP000000000022'
										) 
									AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
								)
							OR	(
									@p_TYPE_SEARCH='KT' 
									AND @p_BRANCH_LOGIN ='DV0001' 
									AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
								)
							OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH =''
						)/*2*/
				)/*1*/
				  OR
				  (
						(
							@ROLE_ID IN ('KSV','GDV','NVTC') 
							OR @DEP_ID_LG ='DEP000000000022'
						) 
						AND @p_BRANCH_LOGIN ='DV0001' 
						AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)
				  )
				  -- NEU DUOC UY QUYEN DUYỆT CHO 1 ĐƠN VỊ KHÁC HỘI SỞ THÌ CHỈ SETUP ĐƠN VỊ, KHÔNG CẦN SETUP PHÒNG BAN
				  OR(	@p_TYPE_SEARCH ='HC' 
						AND A.BRANCH_CREATE <> 'DV0001' 
						AND A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH)
					)
				  -- LUCTV 25 05 BO SUNG CAU HINH CHO PHEP DVKD DUOC THAY NHUNG PDN THANH TOAN DO HO TẠO
				  OR (
						@p_TYPE_SEARCH ='HC' 
						AND @BRANCH_TYPE_LG <> 'HS' 
						AND
						EXISTS (
									SELECT * 
									FROM TR_REQ_ADVANCE_DT 
									WHERE REQ_PAY_ID = A.REQ_PAY_ID AND REF_ID IN (
																					SELECT CONTRACT_ID 
																					FROM TR_CONTRACT
																					WHERE BRANCH_ID =@p_BRANCH_LOGIN
																				  ) 
									AND AUTH_STATUS_KT ='A'
								)
					)
			 )/*0*/
		ORDER BY A.CREATE_DT DESC
-- PAGING END
    END;
END -- PAGING
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO