CREATE OR ALTER   PROCEDURE [dbo].[TR_REQUEST_DOC_GET_DVDM_AND_BRANCH]
	@p_DVDM_CODE	varchar(15)  = NULL,
	@p_DVDM_NAME	Nvarchar(300)  = NULL,
	@p_NOTES	nvarchar(1000)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(20)  = NULL,
	@p_CREATE_DT	NVARCHAR(25) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(20)  = NULL,
	@p_APPROVE_DT	NVARCHAR(25) = NULL,
	@p_TOP INT = 0
AS
BEGIN -- PAGING
-------------------LẤY DVDM Ở HỘI SỞ-------------------------
	-- PAGING BEGIN
	SELECT A.DVDM_ID, A.DVDM_CODE, 
	A.DVDM_NAME + ' - ' + (SELECT TOP 1 BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_TYPE = 'HS') AS DVDM_NAME, 
	A.NOTES, A.RECORD_STATUS, A.AUTH_STATUS, A.MAKER_ID, A.CREATE_DT, A.EDITER_ID, A.EDIT_DT,
	A.CHECKER_ID, A.APPROVE_DT, A.IS_DVDM, A.IS_KHOI, A.IS_DVCM, A.IS_PTGD, A.IS_GDK
	-- SELECT END
	FROM dbo.CM_DVDM A
		
	WHERE 1 = 1
	AND (A.DVDM_NAME like '%' + @p_DVDM_NAME + '%' OR @p_DVDM_NAME IS NULL OR @p_DVDM_NAME = '')
	AND (A.DVDM_CODE like + '%' + @p_DVDM_CODE + '%' OR @p_DVDM_CODE IS NULL OR @p_DVDM_CODE = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
	AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
	AND A.RECORD_STATUS = '1'
	AND A.IS_DVDM=1

	--------------------LẤY CHI NHÁNH, PGD --------------------------------
	UNION
	SELECT CDM.DMMS_ID AS DVDM_ID, A.BRANCH_CODE AS DVDM_CODE, A.BRANCH_NAME AS DVDM_NAME, A.NOTES, A.RECORD_STATUS, A.AUTH_STATUS, A.MAKER_ID, A.CREATE_DT,
	A.MAKER_ID AS EDITER_ID, A.CREATE_DT AS EDIT_DT, A.CHECKER_ID, A.APPROVE_DT, NULL, NULL, NULL, NULL, NULL
	FROM CM_BRANCH A
	INNER JOIN CM_DMMS CDM ON A.BRANCH_ID = CDM.BRANCH_ID
	WHERE 1 = 1
	AND(A.BRANCH_TYPE <> 'HS')
	AND(A.RECORD_STATUS = '1')
	AND(A.AUTH_STATUS = 'A')
	-- PAGING END
END -- PAGING
GO