AMS - VB

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 EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US

                WHERE US.TLNANME = @p_USERNAME))