|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[PL_PROCESS_CURRENT_SEARCH]
|
|
3
|
@p_REQ_ID VARCHAR(20)=NULL,
|
|
4
|
@p_USER_LOGIN VARCHAR(20)=NULL,
|
|
5
|
@p_TYPE VARCHAR(20)=NULL
|
|
6
|
AS
|
|
7
|
BEGIN
|
|
8
|
|
|
9
|
DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT
|
|
10
|
|
|
11
|
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'
|
|
12
|
|
|
13
|
IF(@p_TYPE LIKE 'TTCT%')
|
|
14
|
BEGIN
|
|
15
|
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
|
|
16
|
BEGIN
|
|
17
|
|
|
18
|
DECLARE @SIGN_USER VARCHAR(15), @CURENT_PROCESS VARCHAR(50)
|
|
19
|
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)
|
|
20
|
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
|
|
21
|
|
|
22
|
-- Nếu có cấp phê duyệt trung gian
|
|
23
|
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
|
|
24
|
BEGIN
|
|
25
|
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
|
|
26
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
27
|
LEFT JOIN
|
|
28
|
(
|
|
29
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
30
|
LEFT JOIN(
|
|
31
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
32
|
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
|
|
33
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME='')))
|
|
34
|
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
35
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
36
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
37
|
LEFT JOIN dbo.PL_REQUEST_DOC RD ON RD.REQ_ID = PL.REQ_ID
|
|
38
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
39
|
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = RD.SIGN_USER)
|
|
40
|
|
|
41
|
END
|
|
42
|
ELSE
|
|
43
|
BEGIN
|
|
44
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
|
|
45
|
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
|
|
46
|
TempU.TLNANME AS TLNAME,
|
|
47
|
TempU.TLFullName,
|
|
48
|
PL.NOTES
|
|
49
|
FROM dbo.PL_REQUEST_PROCESS PL
|
|
50
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
51
|
LEFT JOIN
|
|
52
|
(
|
|
53
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
54
|
LEFT JOIN(
|
|
55
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
56
|
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
|
|
57
|
UNION ALL
|
|
58
|
SELECT TU.TLNAME,US.TLFullName,TU.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
|
|
59
|
FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
60
|
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
|
|
61
|
LEFT JOIN(
|
|
62
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
63
|
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
|
|
64
|
WHERE CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
65
|
) TempU ON (TempU.RoleName=PL.ROLE_USER)
|
|
66
|
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
67
|
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='')
|
|
68
|
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
|
|
69
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
70
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
71
|
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
|
|
72
|
END
|
|
73
|
|
|
74
|
END
|
|
75
|
|
|
76
|
ELSE IF(@IS_HAS_CHILD=1)
|
|
77
|
BEGIN
|
|
78
|
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 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
|
|
79
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
80
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
81
|
LEFT JOIN
|
|
82
|
(
|
|
83
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
84
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
85
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
86
|
)TempC ON TempC.PROCESS_ID =PL.ID AND TempC.REQ_ID = PL.REQ_ID AND PL.IS_HAS_CHILD=1
|
|
87
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
88
|
LEFT JOIN
|
|
89
|
(
|
|
90
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
91
|
LEFT JOIN(
|
|
92
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
93
|
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
|
|
94
|
UNION ALL
|
|
95
|
SELECT TU.TLNAME,US.TLFullName,TU.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
96
|
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
|
|
97
|
LEFT JOIN(
|
|
98
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
99
|
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
|
|
100
|
WHERE CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
101
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
102
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
103
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
104
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
105
|
END
|
|
106
|
END
|
|
107
|
ELSE IF(@p_TYPE LIKE 'PYC-XE')
|
|
108
|
BEGIN
|
|
109
|
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
|
|
110
|
|
|
111
|
BEGIN
|
|
112
|
|
|
113
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES
|
|
114
|
FROM dbo.PL_REQUEST_PROCESS PL
|
|
115
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
116
|
LEFT JOIN
|
|
117
|
(
|
|
118
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,TU.SECUR_CODE,TU.TLSUBBRID
|
|
119
|
FROM (
|
|
120
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
121
|
dbo.TL_USER TS
|
|
122
|
UNION ALL
|
|
123
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
124
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
125
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
126
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
127
|
) TU
|
|
128
|
) TempU ON (TempU.RoleName =PL.ROLE_USER OR PL.ROLE_USER=(SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
129
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
130
|
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
|
|
131
|
|
|
132
|
END
|
|
133
|
ELSE IF(@IS_HAS_CHILD=1)
|
|
134
|
BEGIN
|
|
135
|
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 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
|
|
136
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
137
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
138
|
LEFT JOIN
|
|
139
|
(
|
|
140
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
141
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
142
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
143
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
144
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
145
|
LEFT JOIN
|
|
146
|
(
|
|
147
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
148
|
FROM (
|
|
149
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
150
|
dbo.TL_USER TS
|
|
151
|
UNION ALL
|
|
152
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
153
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
154
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
155
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
156
|
) TU
|
|
157
|
LEFT JOIN(
|
|
158
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
159
|
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
|
|
160
|
) TempU ON (TempU.RoleName IN (SELECT VALUE FROM WSISPLIT(PL.ROLE_USER,'-')) OR EXISTS (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_NEW IN (SELECT VALUE FROM WSISPLIT(PL.ROLE_USER,'-')) AND ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
161
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
162
|
|
|
163
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
164
|
END
|
|
165
|
END
|
|
166
|
|
|
167
|
ELSE IF(@p_TYPE LIKE 'PYC%')
|
|
168
|
BEGIN
|
|
169
|
IF(@PROCESS_CURR='DMMS')
|
|
170
|
BEGIN
|
|
171
|
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 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
|
|
172
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
173
|
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
|
|
174
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
175
|
LEFT JOIN
|
|
176
|
(
|
|
177
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
178
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
179
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
180
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
181
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
182
|
LEFT JOIN
|
|
183
|
(
|
|
184
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
|
|
185
|
FROM
|
|
186
|
(
|
|
187
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
188
|
dbo.TL_USER TS
|
|
189
|
UNION ALL
|
|
190
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
191
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
192
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
193
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
194
|
) TU
|
|
195
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
196
|
LEFT JOIN(
|
|
197
|
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
|
|
198
|
UNION ALL
|
|
199
|
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
|
|
200
|
(
|
|
201
|
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
|
|
202
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
|
|
203
|
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
|
|
204
|
WHERE CD.IS_KHOI <>1
|
|
205
|
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
|
|
206
|
) DVDM
|
|
207
|
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
208
|
|
|
209
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
|
|
210
|
|
|
211
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
212
|
END
|
|
213
|
ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
|
|
214
|
|
|
215
|
BEGIN
|
|
216
|
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
|
|
217
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
218
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
219
|
LEFT JOIN
|
|
220
|
(
|
|
221
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
222
|
FROM (
|
|
223
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
224
|
dbo.TL_USER TS
|
|
225
|
UNION ALL
|
|
226
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
227
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
228
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
229
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
230
|
) TU
|
|
231
|
LEFT JOIN
|
|
232
|
(
|
|
233
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
234
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
|
|
235
|
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
236
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
237
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
238
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
239
|
WHERE REQ_ID =@p_REQ_ID AND STATUS='C'
|
|
240
|
|
|
241
|
END
|
|
242
|
ELSE IF(@IS_HAS_CHILD=1)
|
|
243
|
BEGIN
|
|
244
|
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 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
|
|
245
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
246
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
247
|
LEFT JOIN
|
|
248
|
(
|
|
249
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
250
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
251
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
252
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
253
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
254
|
LEFT JOIN
|
|
255
|
(
|
|
256
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
257
|
FROM (
|
|
258
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
259
|
dbo.TL_USER TS
|
|
260
|
UNION ALL
|
|
261
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
262
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
263
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
264
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
265
|
) TU
|
|
266
|
LEFT JOIN(
|
|
267
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
268
|
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
|
|
269
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
270
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
271
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
272
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
273
|
END
|
|
274
|
END
|
|
275
|
|
|
276
|
ELSE IF(@p_TYPE = 'INVENT')
|
|
277
|
BEGIN
|
|
278
|
SET @SIGN_USER = (SELECT SIGN_USER FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_REQ_ID)
|
|
279
|
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
|
|
280
|
|
|
281
|
-- Nếu có cấp phê duyệt trung gian
|
|
282
|
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
|
|
283
|
BEGIN
|
|
284
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID, BR.BRANCH_NAME AS DVDM_NAME,TU.TLNANME AS TLNAME,TU.TLFullName,PL.NOTES
|
|
285
|
FROM dbo.PL_REQUEST_PROCESS PL
|
|
286
|
LEFT JOIN dbo.ASS_INVENTORY_MASTER AI ON AI.INVENT_ID = PL.REQ_ID
|
|
287
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
288
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = AI.SIGN_USER
|
|
289
|
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND PL.PROCESS_ID = 'SIGN'
|
|
290
|
END
|
|
291
|
ELSE IF(@CURENT_PROCESS = 'APPNEW')
|
|
292
|
BEGIN
|
|
293
|
SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TmpU.TLNANME, TmpU.TLFullName, PL.NOTES
|
|
294
|
FROM dbo.PL_REQUEST_PROCESS PL
|
|
295
|
LEFT JOIN (
|
|
296
|
SELECT * FROM [dbo].[FN_GET_USER_BY_ROLE] ('', '', '')
|
|
297
|
) TmpU ON 1=1
|
|
298
|
LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = PL.BRANCH_ID
|
|
299
|
WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS = 'C'
|
|
300
|
AND ((TmpU.ROLE_OLD = PL.ROLE_USER OR TmpU.ROLE_NEW = PL.ROLE_USER) OR PL.ROLE_USER IS NULL OR PL.ROLE_USER = '')
|
|
301
|
AND ((BR.BRANCH_TYPE = 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID AND TmpU.DEP_ID = PL.DEP_ID)
|
|
302
|
OR (BR.BRANCH_TYPE <> 'HS' AND TmpU.BRANCH_ID = PL.BRANCH_ID)
|
|
303
|
OR (PL.BRANCH_ID IS NULL OR PL.BRANCH_ID = '')
|
|
304
|
)
|
|
305
|
END
|
|
306
|
ELSE IF(@CURENT_PROCESS <> 'APPNEW')
|
|
307
|
BEGIN
|
|
308
|
SELECT PL.REQ_ID, Pl.PROCESS_ID,BR.BRANCH_NAME AS DVDM_NAME, TU.TLNANME, TU.TLFullName, PL.NOTES
|
|
309
|
FROM dbo.PL_REQUEST_PROCESS PL
|
|
310
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME = PL.ROLE_USER
|
|
311
|
LEFT JOIN dbo.CM_BRANCH BR ON BR.BRANCH_ID = TU.TLSUBBRID
|
|
312
|
WHERE PL.REQ_ID = @p_REQ_ID AND PL.STATUS = 'C'
|
|
313
|
END
|
|
314
|
END
|
|
315
|
|
|
316
|
----------------BAODNQ 6/6/2022 TỜ TRÌNH CHỈ ĐỊNH THẦU--------------
|
|
317
|
ELSE IF (@p_TYPE = 'TT_CDT')
|
|
318
|
BEGIN
|
|
319
|
SET @SIGN_USER = (SELECT SIGN_USER FROM PL_APPOINT_CONTRACTOR WHERE REQ_ID = @p_REQ_ID)
|
|
320
|
SET @CURENT_PROCESS = (
|
|
321
|
SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS = 'C'
|
|
322
|
)
|
|
323
|
-------------CÓ CẤP DUYỆT TRUNG GIAN-----------
|
|
324
|
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
|
|
325
|
BEGIN
|
|
326
|
SELECT DISTINCT
|
|
327
|
PRP.REQ_ID,
|
|
328
|
PRP.PROCESS_ID,
|
|
329
|
CASE
|
|
330
|
WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
|
|
331
|
ELSE CB.BRANCH_NAME
|
|
332
|
END AS DVDM_NAME,
|
|
333
|
TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES
|
|
334
|
FROM PL_REQUEST_PROCESS PRP
|
|
335
|
LEFT JOIN CM_DVDM CD ON PRP.DVDM_ID = CD.DVDM_ID
|
|
336
|
LEFT JOIN(
|
|
337
|
SELECT
|
|
338
|
TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE,
|
|
339
|
TU.DEP_ID, TU.TLSUBBRID
|
|
340
|
FROM TL_USER TU
|
|
341
|
LEFT JOIN (
|
|
342
|
SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID
|
|
343
|
FROM PL_COSTCENTER PC
|
|
344
|
LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID
|
|
345
|
) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID
|
|
346
|
|
|
347
|
) TLUSER ON (
|
|
348
|
TLUSER.RoleName = PRP.ROLE_USER
|
|
349
|
OR PRP.ROLE_USER IN (
|
|
350
|
SELECT ROLE_NEW
|
|
351
|
FROM TL_SYS_ROLE_MAPPING TM
|
|
352
|
WHERE TM.ROLE_OLD = TLUSER.RoleName
|
|
353
|
AND TM.TLNAME IS NULL OR TM.TLNAME = ''
|
|
354
|
)
|
|
355
|
)
|
|
356
|
AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '')
|
|
357
|
AND (TLUSER.DEP_ID = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '')
|
|
358
|
AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '')
|
|
359
|
LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON PRP.REQ_ID = PAC.REQ_ID
|
|
360
|
LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID
|
|
361
|
WHERE PRP.REQ_ID = @p_REQ_ID
|
|
362
|
AND STATUS = 'C'
|
|
363
|
AND (PRP.PROCESS_ID = 'SIGN' AND TLUSER.TLNANME = PAC.SIGN_USER)
|
|
364
|
END
|
|
365
|
ELSE
|
|
366
|
----------KO CÓ CẤP DUYỆT TRUNG GIAN-------------
|
|
367
|
BEGIN
|
|
368
|
SELECT DISTINCT
|
|
369
|
PRP.REQ_ID,
|
|
370
|
PRP.PROCESS_ID,
|
|
371
|
CASE
|
|
372
|
WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME
|
|
373
|
ELSE CB.BRANCH_NAME
|
|
374
|
END AS DVDM_NAME,
|
|
375
|
TLUSER.TLNANME AS TLNAME, TLUSER.TLFullName, PRP.NOTES
|
|
376
|
FROM PL_REQUEST_PROCESS PRP
|
|
377
|
LEFT JOIN CM_DVDM CD ON CD.DVDM_ID = PRP.DVDM_ID
|
|
378
|
LEFT JOIN
|
|
379
|
(
|
|
380
|
SELECT TU.TLNANME, TU.TLFullName, TU.RoleName, PCOST.DVDM_ID, TU.SECUR_CODE,
|
|
381
|
TU.DEP_ID, TU.TLSUBBRID
|
|
382
|
FROM TL_USER TU
|
|
383
|
LEFT JOIN (
|
|
384
|
SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID
|
|
385
|
FROM PL_COSTCENTER PC
|
|
386
|
LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID
|
|
387
|
) PCOST ON TU.TLSUBBRID = PCOST.BRANCH_ID AND TU.DEP_ID = PCOST.DEP_ID
|
|
388
|
UNION ALL
|
|
389
|
SELECT TM.TLNAME, TLU.TLFullName, TM.ROLE_NEW AS RoleName, PCOST.DVDM_ID,
|
|
390
|
TM.DEP_ID AS SECUR_CODE, TM.DEP_ID AS DEP_ID, TM.BRANCH_ID AS TLSUBBRID
|
|
391
|
FROM TL_SYS_ROLE_MAPPING TM
|
|
392
|
LEFT JOIN TL_USER TLU ON TM.TLNAME = TLU.TLNANME
|
|
393
|
LEFT JOIN (
|
|
394
|
SELECT PC.DVDM_ID, PD.BRANCH_ID, PD.DEP_ID
|
|
395
|
FROM PL_COSTCENTER PC
|
|
396
|
LEFT JOIN PL_COSTCENTER_DT PD ON PD.COST_ID = PC.COST_ID
|
|
397
|
) PCOST ON TM.BRANCH_ID = PCOST.BRANCH_ID AND TM.DEP_ID = PCOST.DEP_ID
|
|
398
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
399
|
) TLUSER ON (TLUSER.RoleName = PRP.ROLE_USER)
|
|
400
|
AND (TLUSER.TLSUBBRID = PRP.BRANCH_ID OR PRP.BRANCH_ID IS NULL OR PRP.BRANCH_ID = '')
|
|
401
|
AND (
|
|
402
|
((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) = 'HS'
|
|
403
|
AND TLUSER.SECUR_CODE = PRP.DEP_ID OR PRP.DEP_ID IS NULL OR PRP.DEP_ID = '')
|
|
404
|
OR (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE PRP.BRANCH_ID = BRANCH_ID) <> 'HS'
|
|
405
|
)
|
|
406
|
AND (TLUSER.DVDM_ID = PRP.DVDM_ID OR PRP.DVDM_ID IS NULL OR PRP.DVDM_ID = '')
|
|
407
|
LEFT JOIN CM_BRANCH CB ON PRP.BRANCH_ID = CB.BRANCH_ID
|
|
408
|
WHERE PRP.REQ_ID = @p_REQ_ID
|
|
409
|
AND STATUS = 'C'
|
|
410
|
END
|
|
411
|
END
|
|
412
|
|
|
413
|
-----------------------BAODNQ 13/7/2022 ĐÁNH GIÁ NCC---------------------------
|
|
414
|
ELSE IF (@p_TYPE = 'RATE_SUP')
|
|
415
|
BEGIN
|
|
416
|
IF(@PROCESS_CURR='DMMS')
|
|
417
|
BEGIN
|
|
418
|
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 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
|
|
419
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
420
|
LEFT JOIN TR_RATE_SUPPLIER_MASTER TR ON TR.RATE_ID=PL.REQ_ID
|
|
421
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
422
|
LEFT JOIN
|
|
423
|
(
|
|
424
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
425
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
426
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
427
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
428
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
429
|
LEFT JOIN
|
|
430
|
(
|
|
431
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
|
|
432
|
FROM
|
|
433
|
(
|
|
434
|
SELECT TS.TLNANME,TS.TLFullName,
|
|
435
|
--TS.RoleName,
|
|
436
|
AR.DisplayName AS RoleName,
|
|
437
|
TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
438
|
dbo.TL_USER TS
|
|
439
|
JOIN AbpUserRoles AU ON TS.ID = AU.UserId
|
|
440
|
JOIN AbpRoles AR ON AU.RoleId = AR.Id
|
|
441
|
UNION ALL
|
|
442
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
443
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
444
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
445
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
446
|
) TU
|
|
447
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
448
|
LEFT JOIN(
|
|
449
|
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
|
|
450
|
UNION ALL
|
|
451
|
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
|
|
452
|
(
|
|
453
|
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
|
|
454
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
|
|
455
|
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
|
|
456
|
WHERE CD.IS_KHOI <>1
|
|
457
|
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
|
|
458
|
) DVDM
|
|
459
|
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
460
|
|
|
461
|
) TempU ON (
|
|
462
|
(TempU.RoleName=PL.ROLE_USER
|
|
463
|
OR (TempU.RoleName='KSV'
|
|
464
|
AND NOT EXISTS(SELECT TOP 1 PP.CHECKER_ID FROM PL_PROCESS PP WHERE PP.REQ_ID = @p_REQ_ID AND PP.PROCESS_ID = 'DMMS' AND PP.CHECKER_ID = TempU.TLNANME ORDER BY PP.APPROVE_DT DESC)
|
|
465
|
)
|
|
466
|
)
|
|
467
|
OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
|
|
468
|
|
|
469
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
470
|
END
|
|
471
|
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
|
|
472
|
BEGIN
|
|
473
|
SET @SIGN_USER = (SELECT SIGN_USER FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_ID = @p_REQ_ID)
|
|
474
|
SET @CURENT_PROCESS = (SELECT TOP 1 PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND STATUS='C')
|
|
475
|
|
|
476
|
-- Nếu có cấp phê duyệt trung gian
|
|
477
|
IF(@SIGN_USER IS NOT NULL AND @SIGN_USER <> '' AND @CURENT_PROCESS = 'SIGN')
|
|
478
|
BEGIN
|
|
479
|
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
|
|
480
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
481
|
LEFT JOIN
|
|
482
|
(
|
|
483
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
|
|
484
|
LEFT JOIN(
|
|
485
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
486
|
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
|
|
487
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE TM.ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME='')))
|
|
488
|
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
489
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
490
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
491
|
LEFT JOIN dbo.TR_RATE_SUPPLIER_MASTER TRS ON TRS.RATE_ID = PL.REQ_ID
|
|
492
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
493
|
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C' AND (PL.PROCESS_ID = 'SIGN' AND TempU.TLNANME = TRS.SIGN_USER)
|
|
494
|
|
|
495
|
END
|
|
496
|
ELSE
|
|
497
|
BEGIN
|
|
498
|
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,
|
|
499
|
CASE WHEN CD.DVDM_NAME IS NOT NULL AND CD.DVDM_NAME <> '' THEN CD.DVDM_NAME ELSE BR.BRANCH_NAME END AS DVDM_NAME,
|
|
500
|
TempU.TLNANME AS TLNAME,
|
|
501
|
TempU.TLFullName,
|
|
502
|
PL.NOTES
|
|
503
|
FROM dbo.PL_REQUEST_PROCESS PL
|
|
504
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
505
|
LEFT JOIN
|
|
506
|
(
|
|
507
|
SELECT TU.TLNANME,TU.TLFullName,
|
|
508
|
--TU.RoleName,
|
|
509
|
AR.DisplayName AS RoleName,
|
|
510
|
Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
511
|
FROM dbo.TL_USER TU
|
|
512
|
JOIN AbpUserRoles AU ON TU.ID = AU.UserId
|
|
513
|
JOIN AbpRoles AR ON AU.RoleId = AR.Id
|
|
514
|
LEFT JOIN(
|
|
515
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
516
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
|
|
517
|
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
518
|
UNION ALL
|
|
519
|
SELECT TU.TLNAME,US.TLFullName,TU.ROLE_NEW RoleName,Temp.DVDM_ID,TU.DEP_ID SECUR_CODE,TU.BRANCH_ID TLSUBBRID
|
|
520
|
FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
521
|
LEFT JOIN dbo.TL_USER US ON US.TLNANME=TU.TLNAME
|
|
522
|
LEFT JOIN(
|
|
523
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
524
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
|
|
525
|
) Temp ON Temp.DEP_ID=TU.DEP_ID AND Temp.BRANCH_ID=TU.BRANCH_ID
|
|
526
|
WHERE CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
527
|
) TempU ON (TempU.RoleName=PL.ROLE_USER)
|
|
528
|
AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
529
|
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='')
|
|
530
|
OR (SELECT TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID = PL.BRANCH_ID) <> 'HS')
|
|
531
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
532
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
533
|
WHERE PL.REQ_ID = @p_REQ_ID AND STATUS='C'
|
|
534
|
END
|
|
535
|
END
|
|
536
|
ELSE IF(@IS_HAS_CHILD=1)
|
|
537
|
BEGIN
|
|
538
|
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 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
|
|
539
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
540
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
541
|
LEFT JOIN
|
|
542
|
(
|
|
543
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
544
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
545
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
546
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
547
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
548
|
LEFT JOIN
|
|
549
|
(
|
|
550
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
551
|
FROM (
|
|
552
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
553
|
dbo.TL_USER TS
|
|
554
|
UNION ALL
|
|
555
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
556
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
557
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
558
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
559
|
) TU
|
|
560
|
LEFT JOIN(
|
|
561
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
562
|
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
|
|
563
|
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING TM WHERE ROLE_OLD=TempU.RoleName AND (TM.TLNAME IS NULL OR TM.TLNAME=''))) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
|
|
564
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
565
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
566
|
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C'
|
|
567
|
END
|
|
568
|
END
|
|
569
|
|
|
570
|
END
|
|
571
|
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100
|