Project

General

Profile

Download (85 KB)

New Text Document (2).txt

Luc Tran Van, 10/07/2024 11:58 AM

 
1 
CREATE PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH]
2 
@p_REQ_ID VARCHAR(20)=NULL,
3 
@p_USER_LOGIN VARCHAR(20)=NULL,
4 
@p_TYPE VARCHAR(20)=NULL,
5 
@MENU_PERMISSON NVARCHAR(500)
6 
AS
7 
BEGIN
8 
DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT, @AUTH_STATUS VARCHAR(10),@BRANCH_NAME_HS NVARCHAR(250)
9 
SELECT TOP 1 @BRANCH_NAME_HS = BRANCH_NAME FROM dbo.CM_BRANCH WHERE BRANCH_ID = 'DV0001'
10 
SELECT TOP 1 @PROCESS_CURR = PROCESS_ID,@IS_HAS_CHILD=IS_HAS_CHILD FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'
11 
DECLARE @SIGN_USER VARCHAR(15), @CURENT_PROCESS VARCHAR(50)
12 
IF(@p_TYPE LIKE 'TTCT%')
13 
BEGIN
14 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
15 
BEGIN
16 
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)
17 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
18 
-- Nếu có cấp phê duyệt trung gian
19 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
20 
BEGIN
21 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVD
22 
M_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
23 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID = PL.DVDM_ID
24 
LEFT JOIN
25 
(
26 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
27 
LEFT JOIN(
28 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
29 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
30 
) TempU ON (TempU.RoleName=PL.ROLE_USER)
31 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
32 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
33 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
34 
LEFT JOIN dbo.PL_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID
35 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
36 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
37 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)
38 
END
39 
ELSE
40 
BEGIN
41 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
42 
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,
43 
TempU.TLNANME AS TLNAME,
44 
TempU.TLFullName,
45 
PL.NOTES
46 
FROM dbo.PL_REQUEST_PROCESS PL
47 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
48 
LEFT JOIN
49 
(
50 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
51 
LEFT JOIN(
52 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
53 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
54 
UNION ALL
55 
SELECT TU.TLNAME, US.TLFullName, TU.RoleDisplayName RoleName, Temp.DVDM_ID, TU.DEP_ID SECUR_CODE, TU.BRANCH_ID TLSUBBRID
56 
FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
57 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
58 
LEFT JOIN(
59 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
60 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID = TU.BRANCH_ID
61 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
62 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
63 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
64 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
65 
---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
66 
UNION ALL
67 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
68 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
69 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
70 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
71 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
72 
WHERE RL.DisplayName <>'DISABLE'
73 
--- END LUCTV 19062023
74 
) TempU ON (TempU.RoleName=PL.ROLE_USER)
75 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
76 
AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
77 
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
78 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
79 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
80 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
81 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
82 
END
83 
END
84 
ELSE IF(@IS_HAS_CHILD=1)
85 
BEGIN
86 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAM
87 
E,
88 
CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,
89 
CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
90 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
91 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
92 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
93 
LEFT JOIN
94 
(
95 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
96 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
97 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
98 
)TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1
99 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
100 
LEFT JOIN
101 
(
102 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
103 
LEFT JOIN(
104 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
105 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
106 
UNION ALL
107 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
108 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
109 
LEFT JOIN(
110 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
111 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
112 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
113 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
114 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
115 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
116 
---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
117 
UNION ALL
118 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
119 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
120 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
121 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
122 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
123 
WHERE RL.DisplayName <>'DISABLE'
124 
--- END LUCTV 19062023
125 
) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
126 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
127 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
128 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
129 
END
130 
END
131 
ELSE IF(@p_TYPE LIKE 'PYC-XE')
132 
BEGIN
133 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
134 
BEGIN
135 
DECLARE @Role VARCHAR(50)
136 
DECLARE @BR VARCHAR(50)
137 
SELECT TOP 1 @Role = ROLE_USER, @BR = BRANCH_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'
138 
SET @Role= REPLACE(@Role,'-',',')
139 
SET @BR= REPLACE(@BR,'-',',')
140 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES
141 
FROM dbo.PL_REQUEST_PROCESS PL
142 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
143 
-- LEFT JOIN dbo.FN_GET_USER_BY_ROLE('','','') TempU ON (TempU.ROLE_OLD IN ( SELECT value from wsiSplit(@Role,',')) OR TempU.ROLE_NEW IN (SELECT value from wsiSplit(@Role,',')) OR TempU.TLNANME IN (SELECT value from wsiSplit(@Role,','))) AND (TempU.BRANC
144 
H_ID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.DEP_ID=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
145 
LEFT JOIN dbo.FN_GET_USER_BY_ROLE('', @BR, '', @MENU_PERMISSON) TempU ON (TempU.ROLE_OLD IN ( SELECT value from wsiSplit(@Role,',')) OR TempU.ROLE_NEW IN (SELECT value from wsiSplit(@Role,',')) OR TempU.TLNANME IN (SELECT value from wsiSplit(@Role,',')))
146 
AND (TempU.BRANCH_ID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='') AND (TempU.DEP_ID=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')-- NGUYENTD 10102023_SECRETKEY: FIX LỖI NGƯỜI XỬ LÝ TIẾP THEO KHÔNG LẤY ĐƯỢC USER CÓ ROLE GDDV
147 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
148 
-- UNION
149 
-- SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,tmp.CDVAL AS TLNAME,tmp.CONTENT,PL.NOTES
150 
-- FROM dbo.PL_REQUEST_PROCESS PL
151 
-- LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
152 
-- LEFT JOIN (SELECT ca.CDVAL,ca.CONTENT,tu.TLSUBBRID FROM CM_ALLCODE ca LEFT JOIN TL_USER tu ON ca.CDVAL= tu.TLNANME WHERE ca.CDTYPE ='TR' AND ca.CDNAME='REQCAR') tmp ON (PL.ROLE_USER='CVDDX' AND pl.BRANCH_ID=tmp.TLSUBBRID)
153 
-- WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
154 
END
155 
ELSE IF(@IS_HAS_CHILD=1)
156 
BEGIN
157 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TempC.TLNAME AS TLNANME , TU.TLFullName AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
158 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
159 
LEFT JOIN
160 
(
161 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
162 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='CAR_TYPE' AND CDTYPE='REQ'
163 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
164 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
165 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
166 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
167 
END
168 
END
169 
ELSE IF(@p_TYPE LIKE 'PYC%' AND @p_TYPE NOT LIKE 'PYC-KVL' AND @p_TYPE NOT LIKE 'PYCQLX')
170 
BEGIN
171 
IF(@PROCESS_CURR='DMMS')
172 
BEGIN
173 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
174 
CASE
175 
WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME
176 
WHEN CD1.DMMS_ID IS NOT NULL AND CD1.DMMS_ID <> '' THEN IIF(BR1.BRANCH_TYPE = 'HS',ISNULL(BR1.BRANCH_NAME,'') + ' - ' + ISNULL(DEP1.DEP_NAME,''),BR.BRANCH_NAME )
177 
ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME)
178 
END AS DVDM_NAME,
179 
CASE
180 
WHEN TempC.ID IS NULL THEN TempU.TLNANME
181 
ELSE TU.TLNANME
182 
END AS TLNAME,
183 
CASE
184 
WHEN TempC.ID IS NULL THEN TempU.TLFullName
185 
ELSE TU.TLFullName
186 
END AS TLFullName,
187 
CASE
188 
WHEN TempC.ID IS NULL THEN PL.NOTES
189 
ELSE TempC.CONTENT
190 
END AS NOTES
191 
FROM dbo.PL_REQUEST_PROCESS PL
192 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
193 
LEFT JOIN dbo.CM_DMMS CD1 ON CD1.DMMS_ID = PL.DVDM_ID
194 
LEFT JOIN dbo.CM_BRANCH BR1 ON CD1.BRANCH_ID = BR1.BRANCH_ID
195 
LEFT JOIN dbo.CM_DEPARTMENT DEP1 ON CD1.DEP_ID = DEP1.DEP_ID
196 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
197 
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
198 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
199 
LEFT JOIN
200 
(
201 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
202 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
203 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
204 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
205 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
206 
LEFT JOIN
207 
(
208 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
209 
FROM
210 
(
211 
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
212 
dbo.TL_USER TS
213 
UNION ALL
214 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
215 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
216 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
217 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
218 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
219 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
220 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
221 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
222 
UNION ALL
223 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU
224 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
225 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
226 
WHERE RL.DisplayName <>'DISABLE'
227 
--- END LUCTV 19062023
228 
) TU
229 
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
230 
LEFT JOIN(
231 
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
232 
UNION ALL
233 
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
234 
(
235 
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
236 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
237 
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
238 
WHERE CD.IS_KHOI <>1
239 
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
240 
) DVDM
241 
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
242 
) TempU ON TempU.RoleName = PL.ROLE_USER AND TempU.DMMS_ID = TR.DMMS_ID AND TempC.ID IS NULL
243 
WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS='C'
244 
END
245 
ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
246 
BEGIN
247 
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)
248 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
249 
-- Nếu có cấp phê duyệt trung gian
250 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
251 
BEGIN
252 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVD
253 
M_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
254 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
255 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
256 
LEFT JOIN
257 
(
258 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
259 
LEFT JOIN(
260 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
261 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
262 
) TempU ON TempU.RoleName = PL.ROLE_USER
263 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
264 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
265 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
266 
LEFT JOIN dbo.TR_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID
267 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
268 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)
269 
END
270 
ELSE
271 
BEGIN
272 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NA
273 
ME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
274 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
275 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
276 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
277 
LEFT JOIN
278 
(
279 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
280 
FROM (
281 
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
282 
dbo.TL_USER TS
283 
UNION ALL
284 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
285 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
286 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
287 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
288 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
289 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
290 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
291 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
292 
UNION ALL
293 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU
294 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
295 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
296 
WHERE RL.DisplayName <>'DISABLE'
297 
--- END LUCTV 19062023
298 
) TU
299 
LEFT JOIN
300 
(
301 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
302 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
303 
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
304 
) TempU ON TempU.RoleName = PL.ROLE_USER
305 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
306 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
307 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
308 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
309 
END
310 
END
311 
ELSE IF(@IS_HAS_CHILD=1)
312 
BEGIN
313 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAM
314 
E,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUES
315 
T_PROCESS PL
316 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
317 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
318 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
319 
LEFT JOIN
320 
(
321 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
322 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
323 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
324 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
325 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
326 
LEFT JOIN
327 
(
328 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
329 
FROM (
330 
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
331 
dbo.TL_USER TS
332 
UNION ALL
333 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
334 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
335 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
336 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
337 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
338 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
339 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
340 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
341 
UNION ALL
342 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU
343 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
344 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
345 
WHERE RL.DisplayName <>'DISABLE'
346 
--- END LUCTV 19062023
347 
) TU
348 
LEFT JOIN(
349 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
350 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
351 
) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
352 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
353 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
354 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
355 
END
356 
END
357 
ELSE IF(@p_TYPE = 'INVENT')
358 
BEGIN
359 
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_REQ_ID)
360 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
361 
-- Nếu có cấp phê duyệt trung gian
362 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
363 
BEGIN
364 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TU.TLNANME AS TLNAME,TU.TLFullName,PL.NOTES
365 
FROM dbo.PL_REQUEST_PROCESS PL
366 
LEFT JOIN dbo.ASS_INVENTORY_MASTER AI ON AI.INVENT_ID = PL.REQ_ID
367 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
368 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = AI.SIGN_USER
369 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND PL.PROCESS_ID = 'SIGN'
370 
END
371 
ELSE IF(@CURENT_PROCESS = 'APPNEW')
372 
BEGIN
373 
SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TmpU.TLNANME, TmpU.TLFullName, PL.NOTES
374 
FROM dbo.PL_REQUEST_PROCESS PL
375 
LEFT JOIN (
376 
SELECT * FROM [dbo].[FN_GET_USER_BY_ROLE] ('', '', '', @MENU_PERMISSON)
377 
) TmpU ON 1=1
378 
LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = PL.BRANCH_ID
379 
WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS = 'C'
380 
AND ((TmpU.ROLE_OLD = PL.ROLE_USER OR TmpU.ROLE_NEW = PL.ROLE_USER) OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '')
381 
AND ((BR.BRANCH_TYPE = 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID AND TmpU.DEP_ID = PL.DEP_ID)
382 
OR (BR.BRANCH_TYPE <> 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID)
383 
OR (PL.BRANCH_ID IS NULL OR PL.BRANCH_ID = '')
384 
)
385 
END
386 
ELSE IF(@CURENT_PROCESS <> 'APPNEW')
387 
BEGIN
388 
SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TU.TLNANME, TU.TLFullName, PL.NOTES
389 
FROM dbo.PL_REQUEST_PROCESS PL
390 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = PL.ROLE_USER
391 
LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = TU.TLSUBBRID
392 
WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS = 'C'
393 
END
394 
END
395 
----------------BAODNQ 6/6/2022 TỜ TRÌNH CHỈ ĐỊNH THẦU--------------
396 
ELSE IF (@p_TYPE = 'TT_CDT')
397 
BEGIN
398 
SET @SIGN_USER = (SELECT SIGN_USER FROM PL_APPOINT_CONTRACTOR WHERE REQ_ID = @p_REQ_ID)
399 
SET @CURENT_PROCESS = (
400 
SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS = 'C'
401 
)
402 
-------------CÓ CẤP DUYỆT TRUNG GIAN-----------
403 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
404 
BEGIN
405 
SELECT DISTINCT
406 
PRP.REQ_ID,
407 
PRP.PROCESS_ID,
408 
CASE
409 
WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
410 
ELSE CB.BRANCH_NAME
411 
END AS DVDM_NAME,
412 
TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES
413 
FROM PL_REQUEST_PROCESS PRP
414 
LEFT JOIN CM_DVDM CD ON PRP.DVDM_ID = CD.DVDM_ID
415 
LEFT JOIN(
416 
SELECT
417 
TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE,
418 
TU.DEP_ID, TU.TLSUBBRID
419 
FROM TL_USER TU
420 
LEFT JOIN (
421 
SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID
422 
FROM PL_COSTCENTER PC
423 
LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID
424 
) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID
425 
) TLUSER ON TLUSER.RoleName = PRP.ROLE_USER
426 
AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '')
427 
AND (TLUSER.DEP_ID = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '')
428 
AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '')
429 
LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON PRP.REQ_ID = PAC.REQ_ID
430 
LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID
431 
WHERE PRP.REQ_ID = @p_REQ_ID
432 
AND STATUS = 'C'
433 
AND (PRP.PROCESS_ID = 'SIGN' AND TLUSER.TLNANME = PAC.SIGN_USER)
434 
END
435 
ELSE
436 
----------KO CÓ CẤP DUYỆT TRUNG GIAN-------------
437 
BEGIN
438 
SELECT DISTINCT
439 
PRP.REQ_ID,
440 
PRP.PROCESS_ID,
441 
CASE
442 
WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
443 
ELSE CB.BRANCH_NAME
444 
END AS DVDM_NAME,
445 
TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES
446 
FROM PL_REQUEST_PROCESS PRP
447 
LEFT JOIN CM_DVDM CD ON CD.DVDM_ID = PRP.DVDM_ID
448 
LEFT JOIN
449 
(
450 
SELECT TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE,
451 
TU.DEP_ID, TU.TLSUBBRID
452 
FROM TL_USER TU
453 
LEFT JOIN (
454 
SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID
455 
FROM PL_COSTCENTER PC
456 
LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID
457 
) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID
458 
UNION ALL
459 
SELECT TM.TLNAME, TLU.TLFullName, TM.RoleDisplayName AS RoleName, PCOST.DVDM_ID,
460 
TM.DEP_ID AS SECUR_CODE, TM.DEP_ID AS DEP_ID, TM.BRANCH_ID AS TLSUBBRID
461 
FROM SYS_PERMISSIONS_PAGE_FOR_USER TM
462 
LEFT JOIN TL_USER TLU ON TM.TLNAME = TLU.TLNANME
463 
LEFT JOIN (
464 
SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID
465 
FROM PL_COSTCENTER PC
466 
LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID
467 
) PCOST ON TM.BRANCH_ID = PCOST.BRANCH_ID AND TM.DEP_ID = PCOST.DEP_ID
468 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
469 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
470 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
471 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
472 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
473 
UNION ALL
474 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE, TU.DEP_ID,TU.TLSUBBRID FROM TL_USER TU
475 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
476 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
477 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
478 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
479 
WHERE RL.DisplayName <>'DISABLE'
480 
--- END LUCTV 19062023
481 
) TLUSER ON (TLUSER.RoleName = PRP.ROLE_USER)
482 
AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '')
483 
AND (
484 
((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) = 'HS'
485 
AND TLUSER.SECUR_CODE = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '')
486 
OR (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) <> 'HS'
487 
)
488 
AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '')
489 
LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID
490 
WHERE PRP.REQ_ID = @p_REQ_ID
491 
AND STATUS = 'C'
492 
END
493 
END
494 
-----------------------BAODNQ 13/7/2022 ĐÁNH GIÁ NCC---------------------------
495 
ELSE IF (@p_TYPE = 'RATE_SUP')
496 
BEGIN
497 
IF(@PROCESS_CURR='DMMS')
498 
BEGIN
499 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS D
500 
VDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL
501 
_REQUEST_PROCESS PL
502 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
503 
LEFT JOIN TR_RATE_SUPPLIER_MASTER TR ON TR.RATE_ID=PL.REQ_ID
504 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
505 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
506 
LEFT JOIN
507 
(
508 
------------BAODNQ 11/10/2022 : Ở bước điều phối lần đầu tiên cả GDDV và KSV cùng thấy phiếu--------------
509 
--SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
510 
--LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
511 
--WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
512 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
513 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
514 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R'
515 
OR (PRC.STATUS_JOB = 'U' AND EXISTS(
516 
SELECT PC.ID FROM PL_REQUEST_PROCESS_CHILD PC
517 
WHERE PC.REQ_ID = PRC.REQ_ID AND PC.TYPE_JOB = 'KS'
518 
AND PC.MAKER_ID IS NULL AND PC.TRANFER_DT IS NULL
519 
)
520 
AND NOT EXISTS(SELECT PC_2.ID FROM PL_REQUEST_PROCESS_CHILD PC_2 WHERE PC_2.REQ_ID = PRC.REQ_ID AND PC_2.TYPE_JOB = 'XL')
521 
)
522 
)
523 
--------------------END BAODNQ 11/10/2022----------------
524 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
525 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
526 
LEFT JOIN
527 
(
528 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
529 
FROM
530 
(
531 
SELECT TS.TLNANME,TS.TLFullName,
532 
--TS.RoleName,
533 
AR.DisplayName AS RoleName,
534 
TS.TLSUBBRID,TS.SECUR_CODE FROM
535 
dbo.TL_USER TS
536 
JOIN AbpUserRoles AU ON TS.ID = AU.UserId
537 
JOIN AbpRoles AR ON AU.RoleId = AR.Id
538 
UNION ALL
539 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
540 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
541 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
542 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
543 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
544 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
545 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
546 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
547 
UNION ALL
548 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU
549 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
550 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
551 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
552 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
553 
WHERE RL.DisplayName <>'DISABLE'
554 
--- END LUCTV 19062023
555 
) TU
556 
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
557 
LEFT JOIN(
558 
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
559 
UNION ALL
560 
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
561 
(
562 
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
563 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
564 
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
565 
WHERE CD.IS_KHOI <>1
566 
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
567 
) DVDM
568 
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
569 
) TempU ON TempU.RoleName=PL.ROLE_USER AND TempU.DMMS_ID = TR.DMMS_ID AND TempC.ID IS NULL
570 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
571 
END
572 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
573 
BEGIN
574 
SET @SIGN_USER = (SELECT SIGN_USER FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_ID = @p_REQ_ID)
575 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
576 
-- Nếu có cấp phê duyệt trung gian
577 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
578 
BEGIN
579 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_
580 
NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
581 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
582 
LEFT JOIN
583 
(
584 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
585 
LEFT JOIN(
586 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
587 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
588 
) TempU ON TempU.RoleName = PL.ROLE_USER
589 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
590 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
591 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
592 
LEFT JOIN dbo.TR_RATE_SUPPLIER_MASTER TRS ON TRS.RATE_ID = PL.REQ_ID
593 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
594 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
595 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = TRS.SIGN_USER)
596 
END
597 
ELSE
598 
BEGIN
599 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
600 
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,
601 
TempU.TLNANME AS TLNAME,
602 
TempU.TLFullName,
603 
PL.NOTES
604 
FROM dbo.PL_REQUEST_PROCESS PL
605 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
606 
LEFT JOIN
607 
(
608 
SELECT TU.TLNANME,TU.TLFullName,
609 
--TU.RoleName,
610 
AR.DisplayName AS RoleName,
611 
Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
612 
FROM dbo.TL_USER TU
613 
JOIN AbpUserRoles AU ON TU.ID = AU.UserId
614 
JOIN AbpRoles AR ON AU.RoleId = AR.Id
615 
LEFT JOIN(
616 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
617 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
618 
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
619 
UNION ALL
620 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
621 
FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
622 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
623 
LEFT JOIN(
624 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
625 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
626 
) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
627 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
628 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
629 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
630 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
631 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
632 
UNION ALL
633 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
634 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
635 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
636 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
637 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
638 
WHERE RL.DisplayName <>'DISABLE'
639 
--- END LUCTV 19062023
640 
) TempU ON TempU.RoleName = PL.ROLE_USER
641 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
642 
AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
643 
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
644 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
645 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
646 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
647 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
648 
END
649 
END
650 
ELSE IF(@IS_HAS_CHILD=1)
651 
BEGIN
652 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NA
653 
ME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUE
654 
ST_PROCESS PL
655 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
656 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
657 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON PL.DEP_ID = DEP.DEP_ID
658 
LEFT JOIN
659 
(
660 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
661 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
662 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
663 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
664 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
665 
LEFT JOIN
666 
(
667 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
668 
FROM (
669 
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
670 
dbo.TL_USER TS
671 
UNION ALL
672 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
673 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
674 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
675 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
676 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
677 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
678 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
679 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
680 
UNION ALL
681 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID, TU.SECUR_CODE FROM TL_USER TU
682 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
683 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
684 
WHERE RL.DisplayName <>'DISABLE'
685 
--- END LUCTV 19062023
686 
) TU
687 
LEFT JOIN(
688 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
689 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
690 
) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID = PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
691 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
692 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
693 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
694 
END
695 
END
696 
-----------HIEUHM 15/11/2022 LAYOUT BẢN VẼ---
697 
ELSE IF(@p_TYPE LIKE 'LAYOUT_BV')
698 
BEGIN
699 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
700 
BEGIN
701 
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.CON_LAYOUT_BLUEPRINT WHERE CON_LAYOUT_BLUEPRINT_ID = @p_REQ_ID)
702 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
703 
-- Nếu có cấp phê duyệt trung gian
704 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
705 
BEGIN
706 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
707 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
708 
LEFT JOIN
709 
(
710 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
711 
LEFT JOIN(
712 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
713 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
714 
) TempU ON TempU.RoleName = PL.ROLE_USER
715 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
716 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
717 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
718 
LEFT JOIN dbo.CON_LAYOUT_BLUEPRINT RD ON RD.CON_LAYOUT_BLUEPRINT_ID = PL.REQ_ID
719 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
720 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)
721 
END
722 
ELSE
723 
BEGIN
724 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
725 
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
726 
TempU.TLNANME AS TLNAME,
727 
TempU.TLFullName,
728 
PL.NOTES
729 
FROM dbo.PL_REQUEST_PROCESS PL
730 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
731 
LEFT JOIN
732 
(
733 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
734 
LEFT JOIN(
735 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
736 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID)
737 
Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
738 
UNION ALL
739 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
740 
FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
741 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
742 
LEFT JOIN(
743 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
744 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
745 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
746 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
747 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
748 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
749 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
750 
UNION ALL
751 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
752 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
753 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
754 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
755 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
756 
WHERE RL.DisplayName <>'DISABLE'
757 
--- END LUCTV 19062023
758 
) TempU ON (TempU.RoleName=PL.ROLE_USER)
759 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
760 
AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
761 
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
762 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
763 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
764 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
765 
END
766 
END
767 
ELSE IF(@IS_HAS_CHILD=1)
768 
BEGIN
769 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID
770 
IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
771 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
772 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
773 
LEFT JOIN
774 
(
775 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
776 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
777 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
778 
)TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1
779 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
780 
LEFT JOIN
781 
(
782 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
783 
LEFT JOIN(
784 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
785 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
786 
UNION ALL
787 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
788 
FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
789 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
790 
LEFT JOIN(
791 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
792 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
793 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
794 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
795 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
796 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
797 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
798 
UNION ALL
799 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
800 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
801 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
802 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
803 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
804 
WHERE RL.DisplayName <>'DISABLE'
805 
--- END LUCTV 19062023
806 
) TempU ON TempU.RoleName = PL.ROLE_USER AND (TempU.TLSUBBRID = PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID = '')
807 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
808 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
809 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
810 
END
811 
END
812 
-----------HIEUHM 15/11/2022 LAYOUT BẢN VẼ---
813 
--NGUYENTD 20/07/2024 PHIẾU YÊU CẦU QUẢN LÝ XE
814 
ELSE IF(@p_TYPE LIKE 'PYCQLX')
815 
BEGIN
816 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
817 
BEGIN
818 
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.TR_REQUEST_CAR_COST WHERE REQ_COST_ID = @p_REQ_ID)
819 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
820 
-- Nếu có cấp phê duyệt trung gian
821 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
822 
BEGIN
823 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVD
824 
M_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
825 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
826 
LEFT JOIN
827 
(
828 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
829 
LEFT JOIN(
830 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
831 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
832 
) TempU ON TempU.RoleName=PL.ROLE_USER
833 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
834 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
835 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
836 
LEFT JOIN dbo.TR_REQUEST_CAR_COST RD ON RD.REQ_COST_ID = PL.REQ_ID
837 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
838 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_ID
839 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)
840 
END
841 
ELSE
842 
BEGIN
843 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NA
844 
ME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
845 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
846 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
847 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_ID
848 
LEFT JOIN
849 
(
850 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
851 
FROM (
852 
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
853 
dbo.TL_USER TS
854 
UNION ALL
855 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
856 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
857 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
858 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
859 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
860 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
861 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
862 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
863 
UNION ALL
864 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU
865 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
866 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
867 
WHERE RL.DisplayName <>'DISABLE'
868 
--- END LUCTV 19062023
869 
) TU
870 
LEFT JOIN
871 
(
872 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
873 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
874 
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
875 
) TempU ON TempU.RoleName=PL.ROLE_USER
876 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
877 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
878 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
879 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
880 
END
881 
END
882 
ELSE IF(@IS_HAS_CHILD=1)
883 
BEGIN
884 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN ISNULL(@BRANCH_NAME_HS,'') + ' - ' + CD.DVDM_NAME ELSE IIF(BR.BRANCH_TYPE = 'HS',BR.BRANCH_NAME + ' - ' + DEP.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAM
885 
E,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUES
886 
T_PROCESS PL
887 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
888 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
889 
LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID = PL.DEP_ID
890 
LEFT JOIN
891 
(
892 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
893 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
894 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
895 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
896 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
897 
LEFT JOIN
898 
(
899 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
900 
FROM (
901 
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
902 
dbo.TL_USER TS
903 
UNION ALL
904 
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.RoleDisplayName RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
905 
dbo.SYS_PERMISSIONS_PAGE_FOR_USER TM
906 
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
907 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TM.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TM.EffectiveDate IS NULL OR TM.EffectiveDate = '')
908 
AND (DATEDIFF(DAY, CONVERT(DATE, TM.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TM.ExpirationDate IS NULL OR TM.ExpirationDate = '')
909 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TM.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
910 
AND TM.AUTH_STATUS = 'A' AND TM.RECORD_STATUS = '1'
911 
---- LUCTV 19062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
912 
UNION ALL
913 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,TU.TLSUBBRID,TU.SECUR_CODE FROM TL_USER TU
914 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
915 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
916 
WHERE RL.DisplayName <>'DISABLE'
917 
--- END LUCTV 19062023
918 
) TU
919 
LEFT JOIN(
920 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
921 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
922 
) TempU ON TempU.RoleName=PL.ROLE_USER AND (TempU.TLSUBBRID = PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
923 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
924 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
925 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
926 
END
927 
END
928 
--NGUYENTD 20/07/2024 PHIẾU YÊU CẦU QUẢN LÝ XE
929 
-- PHIẾU YÊU CẦU KHO VẬT LIỆU --
930 
ELSE IF (@p_TYPE = 'PYC-KVL')
931 
BEGIN
932 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
933 
BEGIN
934 
SET @SIGN_USER = (SELECT USER_APPROVAL FROM dbo.MW_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)
935 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
936 
DECLARE @ID_KSV_PROCESS VARCHAR(20) = (SELECT TOP 1 ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_ID AND TYPE_JOB='KS')
937 
DECLARE @ID_KSV_CHILD VARCHAR(20) = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID=@ID_KSV_PROCESS)
938 
-- Nếu có cấp phê duyệt trung gian
939 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'MED_APP')
940 
BEGIN
941 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
942 
ELSE ISNULL(BR.BRANCH_NAME + ' - ' + CD1.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES
943 
FROM dbo.PL_REQUEST_PROCESS PL
944 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
945 
LEFT JOIN
946 
(
947 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
948 
LEFT JOIN(
949 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
950 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
951 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '')
952 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
953 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
954 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
955 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
956 
LEFT JOIN CM_DEPARTMENT CD1 ON PL.DEP_ID = CD1.DEP_ID
957 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'MED_APP' AND TempU.TLNANME = @SIGN_USER)
958 
END
959 
ELSE
960 
BEGIN
961 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
962 
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
963 
TempU.TLNANME AS TLNAME,
964 
TempU.TLFullName,
965 
PL.NOTES
966 
FROM dbo.PL_REQUEST_PROCESS PL
967 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
968 
LEFT JOIN
969 
(
970 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
971 
LEFT JOIN(
972 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
973 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
974 
UNION ALL
975 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
976 
FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
977 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
978 
LEFT JOIN(
979 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
980 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
981 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
982 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
983 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
984 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
985 
---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
986 
UNION ALL
987 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
988 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
989 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
990 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
991 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
992 
WHERE RL.DisplayName <>'DISABLE'
993 
--- END LUCTV 19062023
994 
) TempU ON (TempU.RoleName=PL.ROLE_USER)
995 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
996 
AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
997 
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
998 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
999 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
1000 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
1001 
END
1002 
END
1003 
ELSE IF(@IS_HAS_CHILD=1)
1004 
BEGIN
1005 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
1006 
CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,
1007 
CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES,
1008 
CASE WHEN TempC.LEVEL_JOB = 1 OR (TempC.TYPE_JOB = 'KS' AND EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND MAKER_ID=TempC.TLNAME AND TYPE_JOB='XL' AND STATUS_JOB='P'))
1009 
THEN 'Approve' ELSE NULL END AS STATUS
1010 
FROM dbo.PL_REQUEST_PROCESS PL
1011 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
1012 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
1013 
LEFT JOIN
1014 
(
1015 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT, PRC.LEVEL_JOB, PRC.TYPE_JOB FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
1016 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
1017 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
1018 
)TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1
1019 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
1020 
LEFT JOIN
1021 
(
1022 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
1023 
LEFT JOIN(
1024 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1025 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
1026 
UNION ALL
1027 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
1028 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
1029 
LEFT JOIN(
1030 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1031 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
1032 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
1033 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
1034 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
1035 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
1036 
---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
1037 
UNION ALL
1038 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
1039 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
1040 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
1041 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
1042 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
1043 
WHERE RL.DisplayName <>'DISABLE'
1044 
--- END LUCTV 19062023
1045 
) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
1046 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
1047 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
1048 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
1049 
END
1050 
END
1051 
-- END PHIẾU YÊU CẦU KHO VẬT LIỆU --
1052 
-- TỜ TRÌNH THANH LÝ TÀI SẢN --
1053 
ELSE IF (@p_TYPE LIKE 'TTTL')
1054 
BEGIN
1055 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
1056 
BEGIN
1057 
SET @SIGN_USER = (SELECT USER_APPROVAL FROM dbo.ASS_PLAN_REQUEST_LIQUID_MASTER WHERE REQ_ID = @p_REQ_ID)
1058 
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
1059 
-- Nếu có cấp phê duyệt trung gian
1060 
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'MED_APP')
1061 
BEGIN
1062 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
1063 
ELSE ISNULL(BR.BRANCH_NAME + ' - ' + CD1.DEP_NAME, BR.BRANCH_NAME) END AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES, PL.ROLE_USER
1064 
FROM dbo.PL_REQUEST_PROCESS PL
1065 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
1066 
LEFT JOIN
1067 
(
1068 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
1069 
LEFT JOIN(
1070 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1071 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
1072 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '')
1073 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
1074 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
1075 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
1076 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
1077 
LEFT JOIN CM_DEPARTMENT CD1 ON PL.DEP_ID = CD1.DEP_ID
1078 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'MED_APP' AND TempU.TLNANME = @SIGN_USER)
1079 
END
1080 
ELSE
1081 
BEGIN
1082 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
1083 
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
1084 
TempU.TLNANME AS TLNAME,
1085 
TempU.TLFullName,
1086 
PL.NOTES, PL.ROLE_USER
1087 
FROM dbo.PL_REQUEST_PROCESS PL
1088 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
1089 
LEFT JOIN
1090 
(
1091 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
1092 
LEFT JOIN(
1093 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1094 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
1095 
UNION ALL
1096 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
1097 
FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
1098 
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
1099 
LEFT JOIN(
1100 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1101 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
1102 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
1103 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
1104 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
1105 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
1106 
---- LUCTV 20062023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
1107 
UNION ALL
1108 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
1109 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
1110 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
1111 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
1112 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
1113 
WHERE RL.DisplayName <>'DISABLE'
1114 
--- END LUCTV 19062023
1115 
) TempU ON (TempU.RoleName=PL.ROLE_USER)
1116 
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
1117 
AND (((SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) = 'HS' AND TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
1118 
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
1119 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
1120 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
1121 
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
1122 
END
1123 
END
1124 
ELSE IF(@IS_HAS_CHILD=1)
1125 
BEGIN
1126 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
1127 
CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,
1128 
CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTES ELSE TempC.CONTENT END AS NOTES, PL.ROLE_USER,
1129 
CASE WHEN TempC.LEVEL_JOB = 1 OR (TempC.TYPE_JOB = 'KS' AND EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND MAKER_ID=TempC.TLNAME AND TYPE_JOB='XL' AND STATUS_JOB='P'))
1130 
THEN 'Approve' ELSE NULL END AS STATUS
1131 
FROM dbo.PL_REQUEST_PROCESS PL
1132 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
1133 
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
1134 
LEFT JOIN
1135 
(
1136 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT, PRC.LEVEL_JOB, PRC.TYPE_JOB FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
1137 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
1138 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
1139 
)TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1
1140 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
1141 
LEFT JOIN
1142 
(
1143 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
1144 
LEFT JOIN(
1145 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1146 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
1147 
UNION ALL
1148 
SELECT TU.TLNAME,US.TLFullName,TU.RoleDisplayName RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.SYS_PERMISSIONS_PAGE_FOR_USER TU
1149 
LEFT JOIN dbo.TL_USER US ON US.TLNANME = TU.TLNAME
1150 
LEFT JOIN(
1151 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
1152 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
1153 
WHERE (DATEDIFF(DAY, CONVERT(DATE, TU.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR TU.EffectiveDate IS NULL OR TU.EffectiveDate = '')
1154 
AND (DATEDIFF(DAY, CONVERT(DATE, TU.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR TU.ExpirationDate IS NULL OR TU.ExpirationDate = '')
1155 
AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = TU.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON)
1156 
AND TU.AUTH_STATUS = 'A' AND TU.RECORD_STATUS = '1'
1157 
---- LUCTV 24102023_SECRETKEY BỔ SUNG VÀO BẢNG ROLE LẤY TỪ GỐC ABPUSERROLES
1158 
UNION ALL
1159 
SELECT TU.TLNANME, TU.TLFullName, RL.DisplayName ,PC.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM TL_USER TU
1160 
INNER JOIN AbpUserRoles UR ON TU.ID = UR.UserId
1161 
INNER JOIN AbpRoles RL ON UR.RoleId = RL.Id
1162 
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
1163 
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
1164 
WHERE RL.DisplayName <>'DISABLE'
1165 
--- END LUCTV 19062023
1166 
) TempU ON (TempU.RoleName=PL.ROLE_USER) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
1167 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
1168 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
1169 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
1170 
END
1171 
END
1172 
-- END TỜ TRÌNH THANH LÝ TS --
1173 
-- BEGIN PHIẾU YÊU CẦU DỊCH VỤ VĂN PHÒNG HO --
1174 
ELSE IF (@P_TYPE = 'SHO')
1175 
BEGIN
1176 
SELECT @CURENT_PROCESS=PROCESS_ID, @AUTH_STATUS=AUTH_STATUS
1177 
FROM SHO_REQ_SERVICE
1178 
WHERE REQ_ID=@p_REQ_ID
1179 
-- LƯU NHÁP
1180 
IF(@CURENT_PROCESS IS NULL AND @AUTH_STATUS='E')
1181 
BEGIN
1182 
SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME
1183 
FROM SHO_REQ_SERVICE
1184 
WHERE REQ_ID=@p_REQ_ID
1185 
END
1186 
-- TỪ CHỐI
1187 
ELSE IF(@AUTH_STATUS='R')
1188 
BEGIN
1189 
SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME
1190 
FROM SHO_REQ_SERVICE A
1191 
WHERE A.REQ_ID=@p_REQ_ID AND A.AUTH_STATUS='R'
1192 
END
1193 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1194 
ELSE IF(@CURENT_PROCESS='SIGN')
1195 
BEGIN
1196 
SELECT A.PROCESS_ID, CHECKER_ID AS TLNAME
1197 
FROM SHO_REQUEST_PROCESS A
1198 
WHERE A.REQ_ID=@p_REQ_ID AND A.[STATUS]='C' AND A.PROCESS_ID=@CURENT_PROCESS AND A.CHECKER_ID=@p_USER_LOGIN
1199 
END
1200 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1201 
ELSE IF(@CURENT_PROCESS='CONFIRM')
1202 
BEGIN
1203 
SELECT 'CONFIRM' AS PROCESS_ID, ROLE_USER, A.CHECKER_ID AS TLNAME
1204 
FROM SHO_REQUEST_PROCESS A
1205 
WHERE A.REQ_ID=@p_REQ_ID
1206 
AND A.PROCESS_ID='APPNEW'
1207 
AND A.CHECKER_ID IS NOT NULL
1208 
AND A.CHECKER_ID=@p_USER_LOGIN
1209 
END
1210 
-- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT
1211 
ELSE IF(@CURENT_PROCESS='APPNEW')
1212 
BEGIN
1213 
-- TRƯỞNG ĐƠN VỊ MẶC ĐỊNH
1214 
SELECT A.PROCESS_ID, ROLE_USER, U.TLNANME AS TLNAME
1215 
FROM SHO_REQUEST_PROCESS A
1216 
INNER JOIN AbpRoles R ON A.ROLE_USER=R.DisplayName
1217 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1218 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID
1219 
WHERE A.REQ_ID=@p_REQ_ID
1220 
AND A.[STATUS]='C'
1221 
AND A.CHECKER_ID IS NULL
1222 
AND A.PROCESS_ID=@CURENT_PROCESS
1223 
AND U.TLNANME=@p_USER_LOGIN
1224 
-- ỦY QUYỀN TRƯỞNG ĐƠN VỊ
1225 
UNION
1226 
SELECT A.PROCESS_ID, ROLE_USER, U.TLNANME AS TLNAME
1227 
FROM SHO_REQUEST_PROCESS A
1228 
INNER JOIN FN_GET_USER_BY_ROLE(NULL,NULL,NULL, @MENU_PERMISSON) U ON A.ROLE_USER=U.ROLE_NEW AND A.DEP_ID=U.DEP_ID AND A.BRANCH_ID=U.BRANCH_ID
1229 
WHERE A.REQ_ID=@p_REQ_ID
1230 
AND A.[STATUS]='C'
1231 
AND A.CHECKER_ID IS NULL
1232 
AND A.PROCESS_ID=@CURENT_PROCESS
1233 
AND U.TLNANME=@p_USER_LOGIN
1234 
END
1235 
ELSE IF(@CURENT_PROCESS='DVCM')
1236 
BEGIN
1237 
-- TRƯỞNG ĐƠN VỊ CHUYÊN MÔN PHÊ DUYỆT
1238 
SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'TRUONG_DVCM' AS PROCESS_ID, ROLE_USER, A.CHECKER_ID AS TLNAME, NULL AS NOTES
1239 
FROM SHO_REQ_SERVICE_DVCM D
1240 
INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID
1241 
WHERE A.REQ_ID=@p_REQ_ID
1242 
AND A.[STATUS]='C'
1243 
AND A.CHECKER_ID=@p_USER_LOGIN
1244 
AND A.PROCESS_ID=@CURENT_PROCESS
1245 
AND D.STEP_APPROVE=1
1246 
UNION
1247 
-- KIỂM SOÁT VIÊN ĐƠN VỊ CHUYÊN MÔN PHÊ DUYỆT
1248 
SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'KSV_DVCM' AS PROCESS_ID, ROLE_USER, C.TLNAME AS TLNAME, NULL AS NOTES
1249 
FROM SHO_REQ_SERVICE_DVCM D
1250 
INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID
1251 
INNER JOIN SHO_REQUEST_PROCESS_CHILD C ON D.REQ_ID=C.REQ_ID AND A.ID=C.PROCESS_ID
1252 
WHERE D.REQ_ID=@p_REQ_ID
1253 
AND A.PROCESS_ID=@CURENT_PROCESS
1254 
AND C.TLNAME=@p_USER_LOGIN
1255 
AND A.CHECKER_ID IS NOT NULL
1256 
AND D.STEP_APPROVE IS NOT NULL
1257 
AND D.STEP_APPROVE=C.LEVEL_JOB+1
1258 
UNION
1259 
-- NHÂN VIÊN XỬ LÝ ĐƠN VỊ CHUYÊN MÔN
1260 
SELECT A.DVDM_ID, A.BRANCH_ID, A.DEP_ID, 'NVXL_DVCM' AS PROCESS_ID, C.TYPE_JOB AS ROLE_USER, C.TLNAME AS TLNAME, D.REF_CONTENT AS NOTES
1261 
FROM SHO_REQ_SERVICE_DVCM D
1262 
INNER JOIN SHO_REQUEST_PROCESS A ON D.REQ_ID=A.REQ_ID AND D.BRANCH_ID=A.BRANCH_ID AND D.DVCM_ID=A.DEP_ID
1263 
INNER JOIN SHO_REQUEST_PROCESS_CHILD C ON D.REQ_ID=C.REQ_ID AND A.ID=C.PROCESS_ID AND C.TYPE_JOB='XL'
1264 
WHERE D.REQ_ID=@p_REQ_ID
1265 
AND A.PROCESS_ID=@CURENT_PROCESS
1266 
AND A.CHECKER_ID IS NOT NULL
1267 
AND C.TLNAME=@p_USER_LOGIN
1268 
AND D.REQ_STATUS='E'
1269 
END
1270 
END
1271 
-- END PHIẾU YÊU CẦU DỊCH VỤ VĂN PHÒNG HO --
1272 
-- BEGIN PHIẾU YÊU CẦU Nhập kho tập trung --
1273 
ELSE IF (@P_TYPE = 'IN_DOC_QLK')
1274 
BEGIN
1275 
SELECT @AUTH_STATUS=AUTH_STATUS
1276 
FROM REQ_IN_DOCUMENT_GATHER ridg
1277 
WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID
1278 
-- LƯU NHÁP
1279 
IF(@AUTH_STATUS ='E')
1280 
BEGIN
1281 
SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME
1282 
FROM REQ_IN_DOCUMENT_GATHER ridg
1283 
WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID
1284 
END
1285 
-- TỪ CHỐI
1286 
ELSE IF(@AUTH_STATUS='R')
1287 
BEGIN
1288 
SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME
1289 
FROM REQ_IN_DOCUMENT_GATHER ridg
1290 
WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'
1291 
END
1292 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1293 
ELSE IF(@AUTH_STATUS='P')
1294 
BEGIN
1295 
SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME
1296 
FROM REQ_IN_DOCUMENT_GATHER ridg
1297 
WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'
1298 
END
1299 
-- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT
1300 
ELSE IF(@AUTH_STATUS IN ('U'))
1301 
BEGIN
1302 
SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1303 
FROM REQ_IN_DOCUMENT_GATHER A
1304 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1305 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1306 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND ( (A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID = 'DV0001') OR (A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID <> 'DV0001'))
1307 
WHERE A.REQ_IN_DOC_GATHER_ID=@p_REQ_ID
1308 
AND A.AUTH_STATUS='U'
1309 
AND U.TLNANME=@p_USER_LOGIN
1310 
END
1311 
ELSE IF(@AUTH_STATUS IN ('A'))
1312 
BEGIN
1313 
SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1314 
FROM REQ_IN_DOCUMENT_GATHER A
1315 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1316 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1317 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1318 
WHERE A.REQ_IN_DOC_GATHER_ID=@p_REQ_ID
1319 
AND A.AUTH_STATUS='A'
1320 
AND U.TLNANME=@p_USER_LOGIN
1321 
END
1322 
ELSE IF(@AUTH_STATUS IN ('N'))
1323 
BEGIN
1324 
SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1325 
FROM REQ_IN_DOCUMENT_GATHER A
1326 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1327 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1328 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1329 
WHERE A.REQ_IN_DOC_GATHER_ID=@p_REQ_ID
1330 
AND A.AUTH_STATUS='N'
1331 
AND U.TLNANME=@p_USER_LOGIN
1332 
END
1333 
ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))
1334 
BEGIN
1335 
-- Xử lý điều phối
1336 
SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME
1337 
FROM REQ_IN_DOCUMENT_GATHER ridg
1338 
WHERE ridg.REQ_IN_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T')
1339 
END
1340 
END
1341 
-- END PYC Nhập kho tập trung --
1342 
-- BEGIN PHIẾU YÊU CẦU mượn kho tập trung --
1343 
ELSE IF (@P_TYPE = 'BOR_DOC_QLK')
1344 
BEGIN
1345 
SELECT @AUTH_STATUS=AUTH_STATUS
1346 
FROM REQ_BOR_DOCUMENT_GATHER ridg
1347 
WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID
1348 
-- LƯU NHÁP
1349 
IF(@AUTH_STATUS ='E')
1350 
BEGIN
1351 
SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME
1352 
FROM REQ_BOR_DOCUMENT_GATHER ridg
1353 
WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID
1354 
END
1355 
-- TỪ CHỐI
1356 
ELSE IF(@AUTH_STATUS='R')
1357 
BEGIN
1358 
SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME
1359 
FROM REQ_BOR_DOCUMENT_GATHER ridg
1360 
WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'
1361 
END
1362 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1363 
ELSE IF(@AUTH_STATUS='P')
1364 
BEGIN
1365 
SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME
1366 
FROM REQ_BOR_DOCUMENT_GATHER ridg
1367 
WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'
1368 
END
1369 
-- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT
1370 
ELSE IF(@AUTH_STATUS IN ('U'))
1371 
BEGIN
1372 
SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1373 
FROM REQ_BOR_DOCUMENT_GATHER A
1374 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1375 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1376 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND ( (A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID = 'DV0001') OR (A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID <> 'DV0001'))
1377 
WHERE A.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID
1378 
AND A.AUTH_STATUS='U'
1379 
AND U.TLNANME=@p_USER_LOGIN
1380 
END
1381 
ELSE IF(@AUTH_STATUS IN ('A'))
1382 
BEGIN
1383 
SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1384 
FROM REQ_BOR_DOCUMENT_GATHER A
1385 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1386 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1387 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1388 
WHERE A.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID
1389 
AND A.AUTH_STATUS='A'
1390 
AND U.TLNANME=@p_USER_LOGIN
1391 
END
1392 
ELSE IF(@AUTH_STATUS IN ('N'))
1393 
BEGIN
1394 
SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1395 
FROM REQ_BOR_DOCUMENT_GATHER A
1396 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1397 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1398 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1399 
WHERE A.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID
1400 
AND A.AUTH_STATUS='N'
1401 
AND U.TLNANME=@p_USER_LOGIN
1402 
END
1403 
ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))
1404 
BEGIN
1405 
-- Xử lý điều phối
1406 
SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME
1407 
FROM REQ_BOR_DOCUMENT_GATHER ridg
1408 
WHERE ridg.REQ_BOR_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T')
1409 
END
1410 
END
1411 
-- END PYC mượn kho tập trung --
1412 
-- BEGIN PHIẾU YÊU CẦU xuất kho tập trung --
1413 
ELSE IF (@P_TYPE = 'OUT_DOC_QLK')
1414 
BEGIN
1415 
SELECT @AUTH_STATUS=AUTH_STATUS
1416 
FROM REQ_OUT_DOCUMENT_GATHER ridg
1417 
WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID
1418 
-- LƯU NHÁP
1419 
IF(@AUTH_STATUS ='E')
1420 
BEGIN
1421 
SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME
1422 
FROM REQ_OUT_DOCUMENT_GATHER ridg
1423 
WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID
1424 
END
1425 
-- TỪ CHỐI
1426 
ELSE IF(@AUTH_STATUS='R')
1427 
BEGIN
1428 
SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME
1429 
FROM REQ_OUT_DOCUMENT_GATHER ridg
1430 
WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'
1431 
END
1432 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1433 
ELSE IF(@AUTH_STATUS='P')
1434 
BEGIN
1435 
SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME
1436 
FROM REQ_OUT_DOCUMENT_GATHER ridg
1437 
WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'
1438 
END
1439 
-- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT
1440 
ELSE IF(@AUTH_STATUS IN ('U'))
1441 
BEGIN
1442 
SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1443 
FROM REQ_OUT_DOCUMENT_GATHER A
1444 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1445 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1446 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND ( (A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID = 'DV0001') OR (A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID <> 'DV0001'))
1447 
WHERE A.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID
1448 
AND A.AUTH_STATUS='U'
1449 
AND U.TLNANME=@p_USER_LOGIN
1450 
END
1451 
ELSE IF(@AUTH_STATUS IN ('A'))
1452 
BEGIN
1453 
SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1454 
FROM REQ_OUT_DOCUMENT_GATHER A
1455 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1456 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1457 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1458 
WHERE A.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID
1459 
AND A.AUTH_STATUS='A'
1460 
AND U.TLNANME=@p_USER_LOGIN
1461 
END
1462 
ELSE IF(@AUTH_STATUS IN ('N'))
1463 
BEGIN
1464 
SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1465 
FROM REQ_OUT_DOCUMENT_GATHER A
1466 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1467 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1468 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1469 
WHERE A.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID
1470 
AND A.AUTH_STATUS='N'
1471 
AND U.TLNANME=@p_USER_LOGIN
1472 
END
1473 
ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))
1474 
BEGIN
1475 
-- Xử lý điều phối
1476 
SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME
1477 
FROM REQ_OUT_DOCUMENT_GATHER ridg
1478 
WHERE ridg.REQ_OUT_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T')
1479 
END
1480 
END
1481 
-- END PYC xuất kho tập trung --
1482 
-- BEGIN PHIẾU YÊU CẦU hoàn kho tập trung --
1483 
ELSE IF (@P_TYPE = 'COL_DOC_QLK')
1484 
BEGIN
1485 
SELECT @AUTH_STATUS=AUTH_STATUS
1486 
FROM REQ_COL_DOCUMENT_GATHER ridg
1487 
WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID
1488 
-- LƯU NHÁP
1489 
IF(@AUTH_STATUS ='E')
1490 
BEGIN
1491 
SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME
1492 
FROM REQ_COL_DOCUMENT_GATHER ridg
1493 
WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID
1494 
END
1495 
-- TỪ CHỐI
1496 
ELSE IF(@AUTH_STATUS='R')
1497 
BEGIN
1498 
SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME
1499 
FROM REQ_COL_DOCUMENT_GATHER ridg
1500 
WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'
1501 
END
1502 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1503 
ELSE IF(@AUTH_STATUS='P')
1504 
BEGIN
1505 
SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME
1506 
FROM REQ_COL_DOCUMENT_GATHER ridg
1507 
WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'
1508 
END
1509 
-- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT
1510 
ELSE IF(@AUTH_STATUS IN ('U'))
1511 
BEGIN
1512 
SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1513 
FROM REQ_COL_DOCUMENT_GATHER A
1514 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1515 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1516 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND ( (A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID = 'DV0001') OR (A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID <> 'DV0001'))
1517 
WHERE A.REQ_COL_DOC_GATHER_ID=@p_REQ_ID
1518 
AND A.AUTH_STATUS='U'
1519 
AND U.TLNANME=@p_USER_LOGIN
1520 
END
1521 
ELSE IF(@AUTH_STATUS IN ('A'))
1522 
BEGIN
1523 
SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1524 
FROM REQ_COL_DOCUMENT_GATHER A
1525 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1526 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1527 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1528 
WHERE A.REQ_COL_DOC_GATHER_ID=@p_REQ_ID
1529 
AND A.AUTH_STATUS='A'
1530 
AND U.TLNANME=@p_USER_LOGIN
1531 
END
1532 
ELSE IF(@AUTH_STATUS IN ('N'))
1533 
BEGIN
1534 
SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1535 
FROM REQ_COL_DOCUMENT_GATHER A
1536 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1537 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1538 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1539 
WHERE A.REQ_COL_DOC_GATHER_ID=@p_REQ_ID
1540 
AND A.AUTH_STATUS='N'
1541 
AND U.TLNANME=@p_USER_LOGIN
1542 
END
1543 
ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))
1544 
BEGIN
1545 
-- Xử lý điều phối
1546 
SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME
1547 
FROM REQ_COL_DOCUMENT_GATHER ridg
1548 
WHERE ridg.REQ_COL_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T')
1549 
END
1550 
END
1551 
-- END PYC hoàn kho tập trung --
1552 
-- BEGIN PHIẾU YÊU CẦU hủy kho tập trung --
1553 
ELSE IF (@P_TYPE = 'DES_DOC_QLK')
1554 
BEGIN
1555 
SELECT @AUTH_STATUS=AUTH_STATUS
1556 
FROM REQ_DES_DOCUMENT_GATHER ridg
1557 
WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID
1558 
-- LƯU NHÁP
1559 
IF(@AUTH_STATUS ='E')
1560 
BEGIN
1561 
SELECT 'SEND_APPR' AS PROCESS_ID, MAKER_ID AS TLNAME
1562 
FROM REQ_DES_DOCUMENT_GATHER ridg
1563 
WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID
1564 
END
1565 
-- TỪ CHỐI
1566 
ELSE IF(@AUTH_STATUS='R')
1567 
BEGIN
1568 
SELECT 'REJECT' AS PROCESS_ID, MAKER_ID AS TLNAME
1569 
FROM REQ_DES_DOCUMENT_GATHER ridg
1570 
WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'R'
1571 
END
1572 
-- CHỜ CẤP TRUNG GIAN PHÊ DUYỆT
1573 
ELSE IF(@AUTH_STATUS='P')
1574 
BEGIN
1575 
SELECT 'MID_APP' AS PROCESS_ID, ridg.CHECKER_MID_ID AS TLNAME
1576 
FROM REQ_DES_DOCUMENT_GATHER ridg
1577 
WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS = 'P'
1578 
END
1579 
-- CHỜ TRƯỞNG ĐƠN VỊ PHÊ DUYỆT
1580 
ELSE IF(@AUTH_STATUS IN ('U'))
1581 
BEGIN
1582 
SELECT 'APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1583 
FROM REQ_DES_DOCUMENT_GATHER A
1584 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1585 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1586 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND ( (A.DEP_ID=U.SECUR_CODE AND A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID = 'DV0001') OR (A.BRANCH_ID=U.TLSUBBRID AND A.BRANCH_ID <> 'DV0001'))
1587 
WHERE A.REQ_DES_DOC_GATHER_ID=@p_REQ_ID
1588 
AND A.AUTH_STATUS='U'
1589 
AND U.TLNANME=@p_USER_LOGIN
1590 
END
1591 
ELSE IF(@AUTH_STATUS IN ('A'))
1592 
BEGIN
1593 
SELECT 'DPXL' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1594 
FROM REQ_DES_DOCUMENT_GATHER A
1595 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1596 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1597 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1598 
WHERE A.REQ_DES_DOC_GATHER_ID=@p_REQ_ID
1599 
AND A.AUTH_STATUS='A'
1600 
AND U.TLNANME=@p_USER_LOGIN
1601 
END
1602 
ELSE IF(@AUTH_STATUS IN ('N'))
1603 
BEGIN
1604 
SELECT 'GDDV_APPR' AS PROCESS_ID, 'GDDV' AS ROLE_USER, U.TLNANME AS TLNAME
1605 
FROM REQ_DES_DOCUMENT_GATHER A
1606 
INNER JOIN AbpRoles R ON R.DisplayName IN ('GDDV','TBP','KSV')
1607 
INNER JOIN AbpUserRoles UR ON R.Id=UR.RoleId
1608 
INNER JOIN TL_USER U ON UR.UserId=U.ID AND (A.BRANCH_MANAGER_ID= U.TLSUBBRID OR A.BRANCH_MANAGER_ID = U.SECUR_CODE)
1609 
WHERE A.REQ_DES_DOC_GATHER_ID=@p_REQ_ID
1610 
AND A.AUTH_STATUS='N'
1611 
AND U.TLNANME=@p_USER_LOGIN
1612 
END
1613 
ELSE IF(@AUTH_STATUS IN ('S','C','M','T'))
1614 
BEGIN
1615 
-- Xử lý điều phối
1616 
SELECT 'XL' AS PROCESS_ID, ridg.CHECKER_ID_XL AS TLNAME
1617 
FROM REQ_DES_DOCUMENT_GATHER ridg
1618 
WHERE ridg.REQ_DES_DOC_GATHER_ID=@p_REQ_ID AND @AUTH_STATUS IN ('S','C','M','T')
1619 
END
1620 
END
1621 
-- END PYC hủy kho tập trung --
1622 
END
1623 
----LUCTV: 19062023_SECRETKEY - FIX LỖI SHOW THIẾU TÊN GIÁM ĐỐC KHỐI