|
1
|
ALTER PROCEDURE dbo.TR_REQUEST_SHOP_DOC_ById
|
|
2
|
@REQ_ID varchar(15),
|
|
3
|
@p_USERNAME VARCHAR(100),
|
|
4
|
@p_STATUS VARCHAR(20)
|
|
5
|
AS
|
|
6
|
DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USERNAME)
|
|
7
|
DECLARE @BRANCHID VARCHAR(20),@DEP_ID VARCHAR(20),@ROLE VARCHAR(20)
|
|
8
|
SELECT @BRANCHID=TLSUBBRID ,@DEP_ID=DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME
|
|
9
|
|
|
10
|
DECLARE @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
|
|
11
|
INSERT INTO @ROLE_LOGIN
|
|
12
|
SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr
|
|
13
|
|
|
14
|
DECLARE @lstCOST TABLE (
|
|
15
|
COST_ID VARCHAR(20)
|
|
16
|
)
|
|
17
|
|
|
18
|
INSERT INTO @lstCOST
|
|
19
|
SELECT COST_ID FROM dbo.TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID = @REQ_ID
|
|
20
|
|
|
21
|
--SET @p_STATUS = (SELECT STATUS FROM dbo.TR_REQUEST_SHOP_DOC WHERE REQ_ID=@REQ_ID)
|
|
22
|
SET @ROLE=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@REQ_ID AND STATUS='C')
|
|
23
|
|
|
24
|
DECLARE @REQ_ID_Temp TABLE(REQ_ID VARCHAR(20))
|
|
25
|
INSERT INTO @REQ_ID_Temp
|
|
26
|
SELECT A.REQ_ID FROM TR_REQUEST_SHOP_DOC A
|
|
27
|
LEFT JOIN PL_REQUEST_PROCESS B ON A.REQ_ID = B.REQ_ID AND B.STATUS = 'C'
|
|
28
|
WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID
|
|
29
|
AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001')
|
|
30
|
AND RL.ROLE_USER = B.ROLE_USER)
|
|
31
|
OR EXISTS(SELECT * FROM @ROLE_LOGIN RL
|
|
32
|
JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.REQ_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID
|
|
33
|
AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001')
|
|
34
|
AND RL.ROLE_USER = C.ROLE_USER)
|
|
35
|
OR A.MAKER_ID = @p_USERNAME)
|
|
36
|
GROUP BY A.REQ_ID
|
|
37
|
HAVING A.REQ_ID = @REQ_ID
|
|
38
|
|
|
39
|
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
|
|
40
|
,TU2.TLFullName AS MAKER_NAME, TU.TLFullName AS CHECKER_NAME
|
|
41
|
,CASE WHEN T.REQ_TYPE = 'CPTS' AND T.IS_DONE = '1' AND T.STATUS = 'DONE' THEN 'APPROVE' ELSE T.STATUS END AS STATUS
|
|
42
|
FROM TR_REQUEST_SHOP_DOC T
|
|
43
|
LEFT JOIN CM_BRANCH B ON T.BRANCH_ID = B.BRANCH_ID
|
|
44
|
LEFT JOIN CM_DEPARTMENT CD ON T.DEP_ID = CD.DEP_ID
|
|
45
|
LEFT JOIN PL_REQUEST_PROCESS PR ON PR.REQ_ID=T.REQ_ID AND PR.PROCESS_ID = 'APPNEW'
|
|
46
|
LEFT JOIN PL_REQUEST_PROCESS PL ON PL.REQ_ID=T.REQ_ID AND PL.PROCESS_ID = 'TTQLTS_D'
|
|
47
|
LEFT JOIN TL_USER TU ON T.CHECKER_ID = TU.TLNANME
|
|
48
|
LEFT JOIN TL_USER TU2 ON T.MAKER_ID = TU2.TLNANME
|
|
49
|
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')
|