ALTER FUNCTION dbo.FN_GET_BRANCH_LEADER
( @BRANCH_ID VARCHAR(15), @DEPT_ID VARCHAR(50) )

RETURNS VARCHAR(MAX)

AS

BEGIN
DECLARE @BRANCH_TYPE VARCHAR(15), @TLNAME VARCHAR(100)
DECLARE @LST_USER_RECIVE TABLE (TLNAME VARCHAR(10))
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_ID)
IF(@BRANCH_TYPE = 'PGD' )
BEGIN
	SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID 
		AND RoleName IN ('TPGD','PPGD')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END
ELSE IF(@BRANCH_TYPE = 'CN' )
BEGIN
	SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID 
		AND RoleName IN ('GDDV','PGD')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END
ELSE IF(@BRANCH_TYPE = 'HS' )
BEGIN
	SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID
		AND SECUR_CODE = @DEPT_ID
		AND RoleName IN ('GDDV','PP','TP')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END

RETURN @TLNAME
END;
GO

ALTER PROC dbo.rpt_ASS_USE_MULTI_CONFIRM_Excel
@USER_MASTER_ID VARCHAR(15) = null,
@BRANCH_ID VARCHAR(15) = null,
@FROMDATE VARCHAR(15) = null,
@TODATE VARCHAR(15) = null,
@IS_CONFIRM VARCHAR(1) = null
as
BEGIN
	SELECT 
		B.ASSET_CODE, B.ASSET_NAME, B.AMORT_MONTH, B.AMORTIZED_MONTH, B.AMORT_AMT, B.AMORTIZED_AMT, AMORT_AMT - B.AMORTIZED_AMT AS GT_CL, B.NOTES,
		A.USER_MASTER_ID,CB.BRANCH_CODE,CB.BRANCH_NAME,1 AS SL,B.BUY_PRICE,'' AS UNIT,B.ASSET_SERIAL_NO AS SERIAL_NUMBER,
		dbo.FN_GET_BRANCH_LEADER(C.BRANCH_ID, c.DEPT_ID) AS TLFullName,D.DEP_CODE,D.DEP_NAME,E.REQ_CODE
	FROM ASS_USE_MULTI_MASTER A
		INNER JOIN ASS_USE_MULTI_DT C ON C.USER_MASTER_ID = A.USER_MASTER_ID
		INNER JOIN ASS_MASTER B ON B.ASSET_ID = C.ASSET_ID
		LEFT JOIN CM_BRANCH CB ON CB.BRANCH_ID = C.BRANCH_ID
    LEFT JOIN CM_DEPARTMENT D ON A.DEP_ID = D.DEP_ID
    LEFT JOIN TR_REQUEST_SHOP_DOC E ON C.REQ_ID = E.REQ_ID
		WHERE (A.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')
		AND A.AUTH_STATUS = 'A' 
		AND (C.BRANCH_ID = @BRANCH_ID OR @BRANCH_ID IS NULL OR @BRANCH_ID = '')
		AND ((@IS_CONFIRM = 'Y' AND A.USER_MASTER_ID IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)) 
		OR (@IS_CONFIRM = 'N' AND A.USER_MASTER_ID NOT IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)
		OR (@IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)))
		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')
		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')
END