ALTER PROCEDURE dbo.TR_REQUEST_SHOP_DOC_ById
@REQ_ID	varchar(15),
@p_USERNAME VARCHAR(100),
@p_STATUS VARCHAR(20)
AS
DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USERNAME)
DECLARE @BRANCHID VARCHAR(20),@DEP_ID VARCHAR(20),@ROLE VARCHAR(20)
SELECT @BRANCHID=TLSUBBRID ,@DEP_ID=DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME

DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
INSERT INTO @ROLE_LOGIN
SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr

DECLARE @lstCOST TABLE (
	COST_ID VARCHAR(20)
)

INSERT INTO @lstCOST
SELECT COST_ID FROM dbo.TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID = @REQ_ID

--SET @p_STATUS = (SELECT STATUS FROM dbo.TR_REQUEST_SHOP_DOC WHERE REQ_ID=@REQ_ID)
SET @ROLE=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@REQ_ID AND STATUS='C')

DECLARE @REQ_ID_Temp TABLE(REQ_ID VARCHAR(20))
INSERT INTO @REQ_ID_Temp
SELECT A.REQ_ID FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN PL_REQUEST_PROCESS B ON A.REQ_ID = B.REQ_ID AND B.STATUS = 'C'
WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
    AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = B.ROLE_USER)
  OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
    JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.REQ_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID
    AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
    AND RL.ROLE_USER = C.ROLE_USER)
  OR A.MAKER_ID = @p_USERNAME)
GROUP BY A.REQ_ID
HAVING A.REQ_ID = @REQ_ID

SELECT T.*, B.BRANCH_NAME, B.BRANCH_TYPE AS BRANCH_CRTYPE, PR.DVKD_USER_APP, PL.DVKD_USER_APP AS QLTS_USER_APP, CD.DEP_CODE, CD.DEP_NAME, 'Admin' AS DVKD_USER_APP
,TU2.TLFullName AS MAKER_NAME, TU.TLFullName AS CHECKER_NAME
,CASE WHEN T.REQ_TYPE = 'CPTS' AND T.IS_DONE = '1' AND T.STATUS = 'DONE' THEN 'APPROVE' ELSE T.STATUS END AS STATUS
FROM TR_REQUEST_SHOP_DOC T
LEFT JOIN CM_BRANCH B ON T.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_DEPARTMENT CD ON T.DEP_ID = CD.DEP_ID
LEFT JOIN PL_REQUEST_PROCESS PR ON PR.REQ_ID=T.REQ_ID AND PR.PROCESS_ID = 'APPNEW'
LEFT JOIN PL_REQUEST_PROCESS PL ON PL.REQ_ID=T.REQ_ID AND PL.PROCESS_ID = 'TTQLTS_D'
LEFT JOIN TL_USER TU ON T.CHECKER_ID = TU.TLNANME
LEFT JOIN TL_USER TU2 ON T.MAKER_ID = TU2.TLNANME
WHERE (EXISTS(SELECT * FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = T.REQ_ID) OR (@ROLE_KT IN ('GDV','KSV') AND T.AUTH_STATUS = 'A') OR @ROLE_KT = 'QLTS')