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

ALTER PROCEDURE [dbo].[TR_BUDGET_BRANCH_TAKE_COST_Search] 

@p_TRN_Date VARCHAR(20) =NULL, 

@p_BRANCH_ID VARCHAR(20) = NULL,

@p_DEP_ID VARCHAR(20) = NULL,

@p_TLNAME VARCHAR(20) = NULL,

@p_TOP INT=NULL

AS 

BEGIN -- PAGING

	DECLARE @BRANCH_LOGIN VARCHAR(20)

	SELECT @BRANCH_LOGIN = TLSUBBRID FROM TL_USER WHERE TLNANME = @p_TLNAME

	DECLARE @tmp TABLE(ID INT IDENTITY(1,1), TLNAME VARCHAR(20), TLFullName NVARCHAR(250), RoleName VARCHAR(20), BRANCH_ID VARCHAR(20), BRANCH_CODE VARCHAR(20), BRANCH_NAME NVARCHAR(250), BRANCH_TYPE VARCHAR(5), DEP_ID VARCHAR(20), DEP_CODE VARCHAR(20), DEP_NAME NVARCHAR(250), KHOI_ID VARCHAR(20), KHOI_NAME NVARCHAR(250), BRANCH_KIND VARCHAR(20), BRANCH_KIND_NAME NVARCHAR(250))

	INSERT INTO @tmp

	SELECT H.*

	FROM

	(	SELECT '' TLNAME, '' TLFullName, '' RoleName, A.BRANCH_ID, A.BRANCH_CODE, A.BRANCH_NAME, A.BRANCH_TYPE, '' DEP_ID, '' DEP_CODE, '' DEP_NAME, '' KHOI_ID, '' KHOI_NAME, '' BRANCH_KIND, '' BRANCH_KIND_NAME

		FROM CM_BRANCH A

		WHERE A.BRANCH_ID = 'DV0001'

		UNION

		SELECT '' TLNAME, '' TLFullName, '' RoleName, A.BRANCH_ID, A.BRANCH_CODE, A.BRANCH_NAME, A.BRANCH_TYPE, '' DEP_ID, '' DEP_CODE, '' DEP_NAME, '' KHOI_ID, '' KHOI_NAME, 'A' BRANCH_KIND, N'Chung' BRANCH_KIND_NAME

		FROM CM_BRANCH A

		WHERE A.BRANCH_ID <> 'DV0001'

		UNION

		SELECT '' TLNAME, '' TLFullName, '' RoleName, A.BRANCH_ID, A.BRANCH_CODE, A.BRANCH_NAME, A.BRANCH_TYPE, '' DEP_ID, '' DEP_CODE, '' DEP_NAME, '' KHOI_ID, '' KHOI_NAME, 'Y' BRANCH_KIND, N'Cá nhân' BRANCH_KIND_NAME

		FROM CM_BRANCH A

		WHERE A.BRANCH_ID <> 'DV0001'

		UNION

		SELECT '' TLNAME, '' TLFullName, '' RoleName, A.BRANCH_ID, A.BRANCH_CODE, A.BRANCH_NAME, A.BRANCH_TYPE, '' DEP_ID, '' DEP_CODE, '' DEP_NAME, '' KHOI_ID, '' KHOI_NAME, 'C' BRANCH_KIND, N'Doanh nghiệp' BRANCH_KIND_NAME

		FROM CM_BRANCH A

		WHERE A.BRANCH_ID <> 'DV0001'

		UNION

		SELECT '' TLNAME, '' TLFullName, '' RoleName, A.BRANCH_ID, B.BRANCH_CODE, B.BRANCH_NAME, B.BRANCH_TYPE, A.DEP_ID DEP_ID, A.DEP_CODE, A.DEP_NAME DEP_NAME, A.KHOI_ID KHOI_ID, C.DVDM_NAME KHOI_NAME, '' BRANCH_KIND, '' BRANCH_KIND_NAME

		FROM CM_DEPARTMENT A

		LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID

		LEFT JOIN CM_DVDM C ON A.KHOI_ID = C.DVDM_ID AND C.IS_KHOI = 1

		WHERE A.BRANCH_ID = 'DV0001'

		UNION

		SELECT TLNANME TLNAME, TLFullName, RoleName, TLSUBBRID BRANCH_ID, B.BRANCH_CODE, B.BRANCH_NAME, B.BRANCH_TYPE, A.DEP_ID, C.DEP_CODE, C.DEP_NAME, C.KHOI_ID, D.DVDM_NAME KHOI_NAME, '' BRANCH_KIND, '' BRANCH_KIND_NAME

		FROM TL_USER A

		LEFT JOIN CM_BRANCH B ON A.TLSUBBRID = B.BRANCH_ID

		LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID

		LEFT JOIN CM_DVDM D ON C.KHOI_ID = D.DVDM_ID AND D.IS_KHOI = 1

		WHERE A.RoleName IN('PTGD', 'GDK') 

	) H

	WHERE 1=1

	AND (H.BRANCH_ID = @p_BRANCH_ID OR ISNULL(@p_BRANCH_ID, '') = '')

	AND (H.DEP_ID = @p_DEP_ID OR ISNULL(@p_DEP_ID, '') = '')

	AND (H.TLNAME LIKE @p_TLNAME OR ISNULL(@p_TLNAME, '') = '' OR H.BRANCH_ID in (SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID] (@BRANCH_LOGIN)))

-- PAGING BEGIN

		SELECT A.*

-- SELECT END

		FROM @tmp A

		WHERE 1=1 

-- PAGING END

END -- PAGING