DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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