|
1
|
|
|
2
|
|
|
3
|
ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_Search]
|
|
4
|
@p_REQ_ID varchar(15) = NULL,
|
|
5
|
@p_REQ_CODE varchar(50) = NULL,
|
|
6
|
@p_PL_REQ_CODE varchar(50) = NULL,
|
|
7
|
@p_REQ_NAME nvarchar(200) = NULL,
|
|
8
|
@p_REQ_DT DATETIME = NULL,
|
|
9
|
@p_REQ_CONTENT NVARCHAR(1000)=NULL,
|
|
10
|
@p_TOTAL_AMT decimal = NULL,
|
|
11
|
@p_NOTES nvarchar(1000) = NULL,
|
|
12
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
13
|
@p_MAKER_ID varchar(12) = NULL,
|
|
14
|
@p_CREATE_DT DATETIME = NULL,
|
|
15
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
16
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
17
|
@p_APPROVE_DT DATETIME = NULL,
|
|
18
|
@p_BRANCH_DO VARCHAR(15) = NULL,
|
|
19
|
@p_BRANCH_CREATE VARCHAR(15) = NULL,
|
|
20
|
@p_USER_REQUEST VARCHAR(15) = NULL,
|
|
21
|
@p_BRANCH_LOGIN VARCHAR(15)=NULL,
|
|
22
|
@p_TLNAME_USER VARCHAR(20)=NULL,
|
|
23
|
@p_ROLE_USER VARCHAR(20),
|
|
24
|
@p_TOP INT = 10,
|
|
25
|
@p_PROCESS_STATUS varchar(50) = NULL,
|
|
26
|
@p_FR_DATE DATETIME = NULL,
|
|
27
|
@p_TO_DATE DATETIME = NULL,
|
|
28
|
@p_TYPE VARCHAR(15),
|
|
29
|
@p_TYPE_TRANFER VARCHAR(15)=NULL,
|
|
30
|
@p_YEAR INT = NULL,
|
|
31
|
@p_IS_TRANSFER VARCHAR(10) = NULL,
|
|
32
|
@p_NGUOIXULY NVARCHAR(15) = NULL,
|
|
33
|
@p_IS_KT bit = NULL,
|
|
34
|
@p_PL_REQ_ID VARCHAR(20)=NULL
|
|
35
|
|
|
36
|
AS
|
|
37
|
BEGIN
|
|
38
|
|
|
39
|
|
|
40
|
IF(@p_ROLE_USER ='KTT')
|
|
41
|
SET @p_ROLE_USER ='GDDV'
|
|
42
|
DECLARE @DEP_ID VARCHAR(15),@BRANCH_TYPE VARCHAR(20)
|
|
43
|
DECLARE
|
|
44
|
@COST_ID TABLE (
|
|
45
|
COST_ID VARCHAR(15),
|
|
46
|
DVDM_ID VARCHAR(15)
|
|
47
|
)
|
|
48
|
SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME_USER)
|
|
49
|
INSERT INTO @COST_ID
|
|
50
|
SELECT DT.COST_ID,PC.DVDM_ID FROM dbo.PL_COSTCENTER_DT DT
|
|
51
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
52
|
WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN
|
|
53
|
|
|
54
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
|
|
55
|
IF(EXISTS(SELECT DEP_ID FROM dbo.CM_DEPARTMENT WHERE DEP_ID=@DEP_ID AND DEP_CODE LIKE '069%'))
|
|
56
|
SET @BRANCH_TYPE='HS'
|
|
57
|
|
|
58
|
DECLARE @AUTHOR TABLE
|
|
59
|
(
|
|
60
|
ROLE_ID VARCHAR(100),
|
|
61
|
BRANCH_ID VARCHAR(20),
|
|
62
|
DEP_ID VARCHAR(20),
|
|
63
|
BRANCH_TYPE VARCHAR(20)
|
|
64
|
)
|
|
65
|
|
|
66
|
DECLARE @AUTHOR_DVDM TABLE
|
|
67
|
(
|
|
68
|
ROLE_ID VARCHAR(100),
|
|
69
|
BRANCH_ID VARCHAR(20),
|
|
70
|
DEP_ID VARCHAR(20),
|
|
71
|
DVDM_ID VARCHAR(20)
|
|
72
|
)
|
|
73
|
DECLARE @AUTHOR_DMMS TABLE
|
|
74
|
(
|
|
75
|
ROLE_ID VARCHAR(100),
|
|
76
|
BRANCH_ID VARCHAR(20),
|
|
77
|
DEP_ID VARCHAR(20),
|
|
78
|
DMMS_ID VARCHAR(20)
|
|
79
|
)
|
|
80
|
INSERT INTO @AUTHOR
|
|
81
|
(
|
|
82
|
ROLE_ID,
|
|
83
|
BRANCH_ID,
|
|
84
|
DEP_ID,
|
|
85
|
BRANCH_TYPE
|
|
86
|
)
|
|
87
|
SELECT RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
|
|
88
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
89
|
WHERE TLNANME=@p_TLNAME_USER
|
|
90
|
UNION ALL
|
|
91
|
SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
|
|
92
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
93
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
94
|
WHERE TLNANME=@p_TLNAME_USER
|
|
95
|
UNION ALL
|
|
96
|
SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
97
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
98
|
WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
99
|
UNION ALL
|
|
100
|
SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
101
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
102
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
103
|
WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
104
|
|
|
105
|
INSERT INTO @AUTHOR_DVDM
|
|
106
|
(
|
|
107
|
ROLE_ID,
|
|
108
|
BRANCH_ID,
|
|
109
|
DEP_ID,
|
|
110
|
DVDM_ID
|
|
111
|
)
|
|
112
|
SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
113
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
114
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
115
|
WHERE TU.TLNANME=@p_TLNAME_USER
|
|
116
|
UNION ALL
|
|
117
|
SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
118
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
119
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
120
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
121
|
WHERE TU.TLNANME=@p_TLNAME_USER
|
|
122
|
UNION ALL
|
|
123
|
SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
124
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
125
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
126
|
WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
127
|
UNION ALL
|
|
128
|
SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
129
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
130
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
131
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
132
|
WHERE TU.TLNAME=@p_TLNAME_USER AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
133
|
|
|
134
|
INSERT INTO @AUTHOR_DMMS
|
|
135
|
(
|
|
136
|
ROLE_ID,
|
|
137
|
BRANCH_ID,
|
|
138
|
DEP_ID,
|
|
139
|
DMMS_ID
|
|
140
|
)
|
|
141
|
SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
142
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
143
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
144
|
WHERE TLNANME=@p_TLNAME_USER
|
|
145
|
UNION ALL
|
|
146
|
SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
147
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
148
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
149
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TU.SECUR_CODE=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
150
|
WHERE TLNANME=@p_TLNAME_USER
|
|
151
|
UNION ALL
|
|
152
|
SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
153
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
154
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
155
|
WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
156
|
UNION ALL
|
|
157
|
SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
158
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
159
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
160
|
LEFT JOIN dbo.CM_DMMS MS ON MS.BRANCH_ID = CB.BRANCH_ID AND (TM1.DEP_ID=MS.DEP_ID OR CB.BRANCH_TYPE <>'HS')
|
|
161
|
WHERE TM1.TLNAME=@p_TLNAME_USER AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
162
|
|
|
163
|
|
|
164
|
----NGUOI XU LY 18022021
|
|
165
|
DECLARE @lstREQUEST TABLE (
|
|
166
|
REQ_ID VARCHAR(20),
|
|
167
|
PROCESS_ID VARCHAR(50),
|
|
168
|
DVDM_NAME NVARCHAR(200),
|
|
169
|
TLNAME VARCHAR(200),
|
|
170
|
TLFullName NVARCHAR(200),
|
|
171
|
NOTES NVARCHAR(200)
|
|
172
|
)
|
|
173
|
INSERT INTO @lstREQUEST
|
|
174
|
( REQ_ID,PROCESS_ID, DVDM_NAME,TLNAME,TLFullName, NOTES)
|
|
175
|
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
|
|
176
|
FROM
|
|
177
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
178
|
WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID)
|
|
179
|
AND PRC.PROCESS_ID ='DMMS' AND PRC.STATUS='C'
|
|
180
|
) PL
|
|
181
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
182
|
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
|
|
183
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
184
|
LEFT JOIN
|
|
185
|
(
|
|
186
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
187
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
188
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
189
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
190
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
191
|
LEFT JOIN
|
|
192
|
(
|
|
193
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
|
|
194
|
FROM
|
|
195
|
(
|
|
196
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
197
|
dbo.TL_USER TS
|
|
198
|
UNION ALL
|
|
199
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
200
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
201
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
202
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
203
|
) TU
|
|
204
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
205
|
LEFT JOIN(
|
|
206
|
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
|
|
207
|
UNION ALL
|
|
208
|
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
|
|
209
|
(
|
|
210
|
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
|
|
211
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
|
|
212
|
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
|
|
213
|
WHERE CD.IS_KHOI <>1
|
|
214
|
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
|
|
215
|
) DVDM
|
|
216
|
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
217
|
) 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
|
|
218
|
WHERE PL.STATUS='C'
|
|
219
|
UNION ALL
|
|
220
|
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
|
|
221
|
FROM
|
|
222
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
223
|
WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID)
|
|
224
|
AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE'
|
|
225
|
AND PRC.STATUS='C' AND (PRC.IS_HAS_CHILD=0 OR PRC.IS_HAS_CHILD IS NULL)
|
|
226
|
) PL
|
|
227
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
228
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
229
|
LEFT JOIN
|
|
230
|
(
|
|
231
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
232
|
FROM (
|
|
233
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
234
|
dbo.TL_USER TS
|
|
235
|
UNION ALL
|
|
236
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
237
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
238
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
239
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
240
|
) TU
|
|
241
|
LEFT JOIN
|
|
242
|
(
|
|
243
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
244
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID
|
|
245
|
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
246
|
) 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='')
|
|
247
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
248
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
249
|
WHERE STATUS='C'
|
|
250
|
UNION ALL
|
|
251
|
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
|
|
252
|
FROM
|
|
253
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
254
|
WHERE EXISTS(SELECT TRD.REQ_ID FROM dbo.TR_REQUEST_DOC TRD WHERE TRD.REQ_ID=PRC.REQ_ID)
|
|
255
|
AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE'
|
|
256
|
AND PRC.STATUS='C' AND PRC.IS_HAS_CHILD=1
|
|
257
|
) PL
|
|
258
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
259
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
260
|
LEFT JOIN
|
|
261
|
(
|
|
262
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
263
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
264
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
265
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
266
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
267
|
LEFT JOIN
|
|
268
|
(
|
|
269
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
270
|
FROM (
|
|
271
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
272
|
dbo.TL_USER TS
|
|
273
|
UNION ALL
|
|
274
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
275
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
276
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
277
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
278
|
) TU
|
|
279
|
LEFT JOIN(
|
|
280
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
281
|
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
|
|
282
|
) 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='')
|
|
283
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
284
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
285
|
WHERE PL.STATUS='C'
|
|
286
|
----
|
|
287
|
|
|
288
|
IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0 OR @P_TOP>0)
|
|
289
|
BEGIN
|
|
290
|
IF(@p_TYPE='DVKD' )
|
|
291
|
BEGIN
|
|
292
|
|
|
293
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.IS_KT,
|
|
294
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
295
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
296
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
297
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
298
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
299
|
RP.ROLE_USER,
|
|
300
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
301
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
302
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
303
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
304
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
305
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
306
|
PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT ,
|
|
307
|
'' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, FORMAT(PLRD.TOTAL_AMT,'#,###') AS NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID,
|
|
308
|
RPN.STATUS AS STATUS_NEXT,
|
|
309
|
RP.STATUS AS STATUS_CURR,
|
|
310
|
'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,
|
|
311
|
A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
312
|
--NGUOI XU LY
|
|
313
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
314
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
315
|
FROM TR_REQUEST_DOC A
|
|
316
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
317
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
318
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
319
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
320
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
321
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
322
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
323
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
324
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
325
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
326
|
LEFT JOIN
|
|
327
|
(
|
|
328
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
329
|
dbo.PL_REQUEST_PROCESS
|
|
330
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
331
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
332
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
333
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
334
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
335
|
LEFT JOIN
|
|
336
|
(
|
|
337
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
338
|
dbo.CM_DMMS
|
|
339
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
340
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
341
|
UNION ALL
|
|
342
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
343
|
FROM dbo.CM_DVDM
|
|
344
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
345
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
346
|
LEFT JOIN
|
|
347
|
(
|
|
348
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
349
|
FROM @lstREQUEST RE
|
|
350
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
351
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
352
|
STUFF((select ', ' + RE.TLNAME
|
|
353
|
FROM @lstREQUEST RE
|
|
354
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
355
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
356
|
FROM @lstREQUEST Results
|
|
357
|
GROUP BY REQ_ID
|
|
358
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
359
|
|
|
360
|
WHERE 1 = 1
|
|
361
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
362
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
363
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
364
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
365
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
366
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
367
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
368
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
369
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
370
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
371
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
372
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
373
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
374
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
375
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
376
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
|
|
377
|
OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
|
|
378
|
)
|
|
379
|
AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
380
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
|
|
381
|
AND A.RECORD_STATUS = '1'
|
|
382
|
AND (A.MAKER_ID=@p_TLNAME_USER OR
|
|
383
|
(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS PLRP WHERE A.REQ_ID=PLRP.REQ_ID AND PLRP.PROCESS_ID='APPNEW'
|
|
384
|
AND EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND AUTH.BRANCH_ID=PLRP.BRANCH_ID
|
|
385
|
AND (AUTH.DEP_ID=PLRP.DEP_ID OR AUTH.BRANCH_TYPE <>'HS')) ))
|
|
386
|
OR (A.SIGN_USER IS NOT NULL AND A.SIGN_USER <>'' AND A.SIGN_USER =@p_TLNAME_USER AND A.AUTH_STATUS <>'E'))
|
|
387
|
|
|
388
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
389
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
390
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
391
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
392
|
AND(
|
|
393
|
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
|
|
394
|
OR
|
|
395
|
(dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
|
|
396
|
)
|
|
397
|
|
|
398
|
ORDER BY A.CREATE_DT DESC
|
|
399
|
END
|
|
400
|
ELSE IF(@p_TYPE='TFJOB')
|
|
401
|
BEGIN
|
|
402
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
403
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
404
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
405
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
406
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
407
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
408
|
RP.ROLE_USER,
|
|
409
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
410
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
411
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
412
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
413
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
414
|
CASE WHEN DVC.BRANCH_TYPE ='HS' THEN DVC.BRANCH_NAME ELSE DEP.DEP_NAME END AS BRANCH_CREATE_NAME,
|
|
415
|
--DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
416
|
PLRD.REQ_NAME AS PL_REQ_NAME,ISNULL(RPC.TYPE_JOB,'KS') AS TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
417
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, FORMAT(PLRD.TOTAL_AMT,'#,###') AS NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
|
|
418
|
RPN.STATUS AS STATUS_NEXT,
|
|
419
|
PLRP.STATUS AS STATUS_CURR,
|
|
420
|
'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,
|
|
421
|
--CDC.DEP_NAME AS DEP_CREATE_NAME,
|
|
422
|
CASE WHEN DVC.BRANCH_TYPE ='HS' THEN CDC.DEP_NAME ELSE DVC.BRANCH_NAME END AS DEP_CREATE_NAME,
|
|
423
|
CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
424
|
|
|
425
|
--NGUOI XU LY
|
|
426
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
427
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
428
|
FROM TR_REQUEST_DOC A
|
|
429
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C' AND PLRP.IS_HAS_CHILD=1
|
|
430
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
431
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
432
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
433
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
434
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
435
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
436
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
437
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
438
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
439
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
440
|
LEFT JOIN CM_DEPARTMENT DEP ON UM.SECUR_CODE = DEP.DEP_ID
|
|
441
|
LEFT JOIN
|
|
442
|
(
|
|
443
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
444
|
dbo.PL_REQUEST_PROCESS
|
|
445
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
446
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
447
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
448
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
449
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
450
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
451
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
452
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
453
|
LEFT JOIN
|
|
454
|
(
|
|
455
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
456
|
dbo.CM_DMMS
|
|
457
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
458
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
459
|
UNION ALL
|
|
460
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
461
|
FROM dbo.CM_DVDM
|
|
462
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
463
|
|
|
464
|
|
|
465
|
|
|
466
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
467
|
LEFT JOIN
|
|
468
|
(
|
|
469
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
470
|
FROM @lstREQUEST RE
|
|
471
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
472
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
473
|
STUFF((select ', ' + RE.TLNAME
|
|
474
|
FROM @lstREQUEST RE
|
|
475
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
476
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
477
|
FROM @lstREQUEST Results
|
|
478
|
GROUP BY REQ_ID
|
|
479
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
480
|
WHERE 1 = 1
|
|
481
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
482
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
483
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
484
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
485
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
486
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
487
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
488
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
489
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
490
|
|
|
491
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
492
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
493
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
494
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
495
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
496
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
497
|
--AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
|
|
498
|
--OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
|
|
499
|
--)
|
|
500
|
AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
501
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
|
|
502
|
AND A.RECORD_STATUS = '1'
|
|
503
|
AND (
|
|
504
|
EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND PLRP.DVDM_ID=AUTH.DVDM_ID)
|
|
505
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS')
|
|
506
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS')
|
|
507
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS')
|
|
508
|
)
|
|
509
|
|
|
510
|
--AND ( ( ( (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) )
|
|
511
|
--OR ((A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND (DEP_ID=@DEP_ID OR @BRANCH_TYPE <>'HS'))
|
|
512
|
--OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID)) AND A.PROCESS_ID='DMMS'))))
|
|
513
|
--OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS') ))
|
|
514
|
|
|
515
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
516
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
517
|
AND (RPC.TYPE_JOB <>'TP' OR RPC.TYPE_JOB IS NULL OR RPC.TYPE_JOB ='')
|
|
518
|
AND (
|
|
519
|
((RPC.REQ_ID IS NOT NULL OR RPC.REQ_ID <> '') AND @p_IS_TRANSFER = 'Y')
|
|
520
|
OR ((RPC.REQ_ID IS NULL OR RPC.REQ_ID = '') AND @p_IS_TRANSFER = 'N')
|
|
521
|
OR @p_IS_TRANSFER = '' OR @p_IS_TRANSFER IS NULL
|
|
522
|
)
|
|
523
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
524
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
525
|
--AND(
|
|
526
|
-- (dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
|
|
527
|
-- OR
|
|
528
|
-- (dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
|
|
529
|
--)
|
|
530
|
--LUCTV 10-03-2021 Ơ MAN HINH DIEU PHOI PYCMS NEU PHIEU DA DUOC NHAN VIEN XU LY GUI PHE DUYET THI KHONG CAN HIEN THI
|
|
531
|
AND ((NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P' AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_ID))) OR A.PROCESS_ID ='DVCM')
|
|
532
|
ORDER BY A.CREATE_DT DESC
|
|
533
|
END
|
|
534
|
ELSE IF(@p_TYPE='DMMS')
|
|
535
|
BEGIN
|
|
536
|
|
|
537
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
538
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
539
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
540
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
541
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
542
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
543
|
RP.ROLE_USER,
|
|
544
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
545
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
546
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
547
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
548
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
549
|
CASE WHEN DVC.BRANCH_TYPE ='HS' THEN DVC.BRANCH_NAME ELSE DEP.DEP_NAME END AS BRANCH_CREATE_NAME,
|
|
550
|
--DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
551
|
PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
552
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, FORMAT(PLRD.TOTAL_AMT,'#,###') AS NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
|
|
553
|
RPN.STATUS AS STATUS_NEXT,
|
|
554
|
PLRP.STATUS AS STATUS_CURR,
|
|
555
|
RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,
|
|
556
|
CASE WHEN DVC.BRANCH_TYPE ='HS' THEN CDC.DEP_NAME ELSE DVC.BRANCH_NAME END AS DEP_CREATE_NAME,
|
|
557
|
CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
558
|
|
|
559
|
--NGUOI XU LY
|
|
560
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
561
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
562
|
FROM TR_REQUEST_DOC A
|
|
563
|
LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <>'U' AND PLRP.PROCESS_ID = 'DMMS'
|
|
564
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
565
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
566
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
567
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
568
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
569
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
570
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
571
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
572
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
573
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
574
|
LEFT JOIN CM_DEPARTMENT DEP ON UM.SECUR_CODE = DEP.DEP_ID
|
|
575
|
LEFT JOIN
|
|
576
|
(
|
|
577
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
578
|
dbo.PL_REQUEST_PROCESS
|
|
579
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
580
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
581
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
582
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
583
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
584
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT'
|
|
585
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
586
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
587
|
LEFT JOIN
|
|
588
|
(
|
|
589
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
590
|
dbo.CM_DMMS
|
|
591
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
592
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
593
|
UNION ALL
|
|
594
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
595
|
FROM dbo.CM_DVDM
|
|
596
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
597
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
598
|
LEFT JOIN
|
|
599
|
(
|
|
600
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
601
|
FROM @lstREQUEST RE
|
|
602
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
603
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
604
|
STUFF((select ', ' + RE.TLNAME
|
|
605
|
FROM @lstREQUEST RE
|
|
606
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
607
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
608
|
FROM @lstREQUEST Results
|
|
609
|
GROUP BY REQ_ID
|
|
610
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
611
|
|
|
612
|
|
|
613
|
WHERE 1 = 1
|
|
614
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
615
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
616
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
617
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
618
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
619
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
620
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
621
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
622
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
623
|
|
|
624
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
625
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
626
|
|
|
627
|
AND (@p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS=''
|
|
628
|
OR (RPC.TYPE_JOB LIKE '%' + @p_PROCESS_STATUS + '%' AND (@p_PROCESS_STATUS='KS' OR @p_PROCESS_STATUS='TP')
|
|
629
|
AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD Temp WHERE Temp.REQ_ID=A.REQ_ID AND Temp.STATUS_JOB='P' AND Temp.TYPE_JOB='XL')
|
|
630
|
OR (RPC.TYPE_JOB LIKE '%' + @p_PROCESS_STATUS + '%' AND @p_PROCESS_STATUS='XL')
|
|
631
|
OR A.USER_DVMS =@p_TLNAME_USER
|
|
632
|
)
|
|
633
|
)
|
|
634
|
|
|
635
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
636
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
637
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
638
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
|
|
639
|
OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
|
|
640
|
)
|
|
641
|
AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
642
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
|
|
643
|
AND A.RECORD_STATUS = '1'
|
|
644
|
|
|
645
|
AND (
|
|
646
|
EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS')
|
|
647
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS')
|
|
648
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DMMS')
|
|
649
|
OR(A.USER_DVMS =@p_TLNAME_USER)
|
|
650
|
)
|
|
651
|
|
|
652
|
--AND ( ((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER))
|
|
653
|
|
|
654
|
--AND ( ( A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND (DEP_ID=@DEP_ID OR @BRANCH_TYPE <>'HS'))
|
|
655
|
--OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID) )))
|
|
656
|
|
|
657
|
--OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DMMS')))
|
|
658
|
|
|
659
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
660
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
661
|
--AND(
|
|
662
|
-- (dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
|
|
663
|
-- OR
|
|
664
|
-- (dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',1) LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = '')
|
|
665
|
--)
|
|
666
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
667
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
668
|
-- LUCTV: 10-03-2021 BO SUNG DIEU KIEN NEU CHUA DIEU PHOI THI KHONG TIM THAY O MAN HINH DMMS
|
|
669
|
AND ((EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TLNAME = @p_TLNAME_USER AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_ID)) OR LEN(A.REQ_CODE) >15))
|
|
670
|
--AND (A.USER_DVMS =@p_TLNAME_USER)
|
|
671
|
ORDER BY A.CREATE_DT DESC
|
|
672
|
END
|
|
673
|
|
|
674
|
ELSE IF(@p_TYPE='PDYC')
|
|
675
|
BEGIN
|
|
676
|
|
|
677
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
678
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
679
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
680
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
681
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
682
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
683
|
PLRP.ROLE_USER,
|
|
684
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
685
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
686
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
687
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
688
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
689
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
690
|
PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
691
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
|
|
692
|
RPN.STATUS AS STATUS_NEXT,
|
|
693
|
PLRP.STATUS AS STATUS_CURR,
|
|
694
|
RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,
|
|
695
|
A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
696
|
|
|
697
|
--NGUOI XU LY
|
|
698
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
699
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
700
|
FROM TR_REQUEST_DOC A
|
|
701
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U'
|
|
702
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
703
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
704
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
705
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
706
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
707
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
708
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
709
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
710
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
711
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
712
|
LEFT JOIN
|
|
713
|
(
|
|
714
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
715
|
dbo.PL_REQUEST_PROCESS
|
|
716
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
717
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
718
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
719
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
720
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
721
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.REQ_ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
722
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
723
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
724
|
LEFT JOIN
|
|
725
|
(
|
|
726
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
727
|
dbo.CM_DMMS
|
|
728
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
729
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
730
|
UNION ALL
|
|
731
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
732
|
FROM dbo.CM_DVDM
|
|
733
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
734
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
735
|
LEFT JOIN
|
|
736
|
(
|
|
737
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
738
|
FROM @lstREQUEST RE
|
|
739
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
740
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
741
|
STUFF((select ', ' + RE.TLNAME
|
|
742
|
FROM @lstREQUEST RE
|
|
743
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
744
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
745
|
FROM @lstREQUEST Results
|
|
746
|
GROUP BY REQ_ID
|
|
747
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
748
|
|
|
749
|
WHERE 1 = 1
|
|
750
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
751
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
752
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
753
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
754
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
755
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
756
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
757
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
758
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
759
|
|
|
760
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
761
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
762
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
763
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
764
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
765
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
766
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
|
|
767
|
OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
|
|
768
|
)
|
|
769
|
AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
770
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
|
|
771
|
AND A.RECORD_STATUS = '1'
|
|
772
|
--AND (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW' AND (
|
|
773
|
-- (PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL))
|
|
774
|
|
|
775
|
AND (EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND (PLRP.DVDM_ID=AUTH.DVDM_ID OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL) AND PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW'))
|
|
776
|
|
|
777
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
778
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
779
|
|
|
780
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
781
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
782
|
|
|
783
|
ORDER BY A.CREATE_DT DESC
|
|
784
|
END
|
|
785
|
ELSE IF(@p_TYPE='DVCM')
|
|
786
|
BEGIN
|
|
787
|
|
|
788
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
789
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,ISNULL(A.TOTAL_AMT,0.00) AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
790
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
791
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
792
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
793
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
794
|
PLRP.ROLE_USER,
|
|
795
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
796
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
797
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
798
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
799
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
800
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
801
|
PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
802
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
|
|
803
|
RPN.STATUS AS STATUS_NEXT,
|
|
804
|
PLRP.STATUS AS STATUS_CURR,
|
|
805
|
RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
806
|
--NGUOI XU LY
|
|
807
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
808
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
809
|
FROM TR_REQUEST_DOC A
|
|
810
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U'
|
|
811
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
812
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
813
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
814
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
815
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
816
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
817
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
818
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
819
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
820
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
821
|
LEFT JOIN
|
|
822
|
(
|
|
823
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
824
|
dbo.PL_REQUEST_PROCESS
|
|
825
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
826
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
827
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
828
|
|
|
829
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
830
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
831
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT'
|
|
832
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
833
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
834
|
LEFT JOIN
|
|
835
|
(
|
|
836
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
837
|
dbo.CM_DMMS
|
|
838
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
839
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
840
|
UNION ALL
|
|
841
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
842
|
FROM dbo.CM_DVDM
|
|
843
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
844
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
845
|
LEFT JOIN
|
|
846
|
(
|
|
847
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
848
|
FROM @lstREQUEST RE
|
|
849
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
850
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
851
|
STUFF((select ', ' + RE.TLNAME
|
|
852
|
FROM @lstREQUEST RE
|
|
853
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
854
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
855
|
FROM @lstREQUEST Results
|
|
856
|
GROUP BY REQ_ID
|
|
857
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
858
|
|
|
859
|
|
|
860
|
WHERE 1 = 1
|
|
861
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
862
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
863
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
864
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
865
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
866
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
867
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
868
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
869
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
870
|
|
|
871
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
872
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
873
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
874
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
875
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
876
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
877
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
|
|
878
|
OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
|
|
879
|
)
|
|
880
|
AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
881
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
|
|
882
|
AND A.RECORD_STATUS = '1'
|
|
883
|
|
|
884
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
885
|
|
|
886
|
|
|
887
|
AND(((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) )))
|
|
888
|
OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DVCM') ))
|
|
889
|
|
|
890
|
AND (
|
|
891
|
EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND PLRP.DVDM_ID=AUTH.DVDM_ID)
|
|
892
|
|
|
893
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DVCM')
|
|
894
|
)
|
|
895
|
|
|
896
|
|
|
897
|
|
|
898
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
899
|
|
|
900
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
901
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
902
|
ORDER BY A.CREATE_DT DESC
|
|
903
|
END
|
|
904
|
ELSE IF(@p_TYPE='PLDVCM')
|
|
905
|
BEGIN
|
|
906
|
|
|
907
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
908
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,ISNULL(A.TOTAL_AMT,0.00) AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
909
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
910
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
911
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
912
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
913
|
--PLRP.ROLE_USER,
|
|
914
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
915
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
916
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
917
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
918
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
919
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
920
|
PLRD.REQ_NAME AS PL_REQ_NAME,
|
|
921
|
--RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
922
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,
|
|
923
|
--PLRP.ID AS REF_ID,
|
|
924
|
RPN.STATUS AS STATUS_NEXT,
|
|
925
|
--PLRP.STATUS AS STATUS_CURR,
|
|
926
|
--RPC.STATUS_JOB AS STATUS_JOB,
|
|
927
|
A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
928
|
--NGUOI XU LY
|
|
929
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
930
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
931
|
FROM TR_REQUEST_DOC A
|
|
932
|
--INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U'
|
|
933
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
934
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
935
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
936
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
937
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
938
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
939
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
940
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
941
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
942
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
943
|
LEFT JOIN
|
|
944
|
(
|
|
945
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
946
|
dbo.PL_REQUEST_PROCESS
|
|
947
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
948
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
949
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
950
|
|
|
951
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
952
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
953
|
--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT'
|
|
954
|
--LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
955
|
--LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
956
|
LEFT JOIN
|
|
957
|
(
|
|
958
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
959
|
dbo.CM_DMMS
|
|
960
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
961
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
962
|
UNION ALL
|
|
963
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
964
|
FROM dbo.CM_DVDM
|
|
965
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
966
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
967
|
LEFT JOIN
|
|
968
|
(
|
|
969
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
970
|
FROM @lstREQUEST RE
|
|
971
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
972
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
973
|
STUFF((select ', ' + RE.TLNAME
|
|
974
|
FROM @lstREQUEST RE
|
|
975
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
976
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
977
|
FROM @lstREQUEST Results
|
|
978
|
GROUP BY REQ_ID
|
|
979
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
980
|
|
|
981
|
|
|
982
|
WHERE 1 = 1
|
|
983
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
984
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
985
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
986
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
987
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
988
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
989
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
990
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
991
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
992
|
|
|
993
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
994
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
995
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
996
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
997
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
998
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
999
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = ''
|
|
1000
|
OR A.DEP_CREATE LIKE '%' + @p_BRANCH_CREATE + '%'
|
|
1001
|
)
|
|
1002
|
AND (PLRD.REQ_CODE LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
1003
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_ID + '%' OR @p_PL_REQ_ID IS NULL OR @p_PL_REQ_ID = '')
|
|
1004
|
AND A.RECORD_STATUS = '1'
|
|
1005
|
|
|
1006
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
1007
|
|
|
1008
|
|
|
1009
|
AND( EXISTS (
|
|
1010
|
SELECT * FROM PL_REQUEST_COSTCENTER PRC
|
|
1011
|
INNER JOIN @COST_ID COST ON PRC.COST_ID = COST.DVDM_ID
|
|
1012
|
WHERE PRC.REQ_ID = A.PL_REQ_ID
|
|
1013
|
)
|
|
1014
|
OR @DEP_ID='DEP000000000022'
|
|
1015
|
|
|
1016
|
)
|
|
1017
|
|
|
1018
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
1019
|
|
|
1020
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
1021
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
1022
|
--AND(((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) )))
|
|
1023
|
-- OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID) ))
|
|
1024
|
|
|
1025
|
ORDER BY A.CREATE_DT DESC
|
|
1026
|
END
|
|
1027
|
|
|
1028
|
ELSE IF(@p_TYPE='DVKD_PARENT' )
|
|
1029
|
BEGIN
|
|
1030
|
|
|
1031
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
1032
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
1033
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
1034
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
1035
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
1036
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
1037
|
RP.ROLE_USER,
|
|
1038
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
1039
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
1040
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
1041
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
1042
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
1043
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
1044
|
PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT ,
|
|
1045
|
'' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID,
|
|
1046
|
RPN.STATUS AS STATUS_NEXT,
|
|
1047
|
RP.STATUS AS STATUS_CURR,
|
|
1048
|
'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
1049
|
--NGUOI XU LY
|
|
1050
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
1051
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
1052
|
FROM TR_REQUEST_DOC A
|
|
1053
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
1054
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
1055
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
1056
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
1057
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
1058
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
1059
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
1060
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
1061
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
1062
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
1063
|
LEFT JOIN
|
|
1064
|
(
|
|
1065
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
1066
|
dbo.PL_REQUEST_PROCESS
|
|
1067
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
1068
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
1069
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
1070
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
1071
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
1072
|
LEFT JOIN
|
|
1073
|
(
|
|
1074
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
1075
|
dbo.CM_DMMS
|
|
1076
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
1077
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
1078
|
UNION ALL
|
|
1079
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
1080
|
FROM dbo.CM_DVDM
|
|
1081
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
1082
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
1083
|
LEFT JOIN
|
|
1084
|
(
|
|
1085
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
1086
|
FROM @lstREQUEST RE
|
|
1087
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
1088
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
1089
|
STUFF((select ', ' + RE.TLNAME
|
|
1090
|
FROM @lstREQUEST RE
|
|
1091
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
1092
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
1093
|
FROM @lstREQUEST Results
|
|
1094
|
GROUP BY REQ_ID
|
|
1095
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
1096
|
|
|
1097
|
|
|
1098
|
WHERE 1 = 1
|
|
1099
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
1100
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
1101
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
1102
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
1103
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
1104
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
1105
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
1106
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
1107
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
1108
|
|
|
1109
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
1110
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
1111
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
1112
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
1113
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
1114
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
1115
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
|
|
1116
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
1117
|
|
|
1118
|
AND A.RECORD_STATUS = '1'
|
|
1119
|
AND (A.BRANCH_CREATE =@p_BRANCH_LOGIN AND (A.DEP_CREATE=@DEP_ID OR A.DEP_CREATE ='' OR A.DEP_CREATE IS NULL))
|
|
1120
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
1121
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
1122
|
AND (A.REQ_PARENT_ID IS NULL OR A.REQ_PARENT_ID='')
|
|
1123
|
|
|
1124
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
1125
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
1126
|
|
|
1127
|
ORDER BY A.CREATE_DT DESC
|
|
1128
|
END
|
|
1129
|
ELSE IF(@p_TYPE='DMMS_PARENT')
|
|
1130
|
BEGIN
|
|
1131
|
|
|
1132
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
1133
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
1134
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
1135
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
1136
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
1137
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
1138
|
RP.ROLE_USER,
|
|
1139
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
1140
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
1141
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
1142
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
1143
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
1144
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
1145
|
PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID,
|
|
1146
|
'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID,
|
|
1147
|
RPN.STATUS AS STATUS_NEXT,
|
|
1148
|
PLRP.STATUS AS STATUS_CURR,
|
|
1149
|
RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
1150
|
--NGUOI XU LY
|
|
1151
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
1152
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
1153
|
FROM TR_REQUEST_DOC A
|
|
1154
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <>'U'
|
|
1155
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
1156
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
1157
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
1158
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
1159
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
1160
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
1161
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
1162
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
1163
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
1164
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
1165
|
LEFT JOIN
|
|
1166
|
(
|
|
1167
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
1168
|
dbo.PL_REQUEST_PROCESS
|
|
1169
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
1170
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
1171
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
1172
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
1173
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
1174
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT'
|
|
1175
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
1176
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
1177
|
LEFT JOIN
|
|
1178
|
(
|
|
1179
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
1180
|
dbo.CM_DMMS
|
|
1181
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
1182
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
1183
|
UNION ALL
|
|
1184
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
1185
|
FROM dbo.CM_DVDM
|
|
1186
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
1187
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
1188
|
LEFT JOIN
|
|
1189
|
(
|
|
1190
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
1191
|
FROM @lstREQUEST RE
|
|
1192
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
1193
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
1194
|
STUFF((select ', ' + RE.TLNAME
|
|
1195
|
FROM @lstREQUEST RE
|
|
1196
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
1197
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
1198
|
FROM @lstREQUEST Results
|
|
1199
|
GROUP BY REQ_ID
|
|
1200
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
1201
|
|
|
1202
|
|
|
1203
|
WHERE 1 = 1
|
|
1204
|
AND (A.IS_KT = @p_IS_KT OR @p_IS_KT IS NULL)
|
|
1205
|
AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
|
|
1206
|
AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '')
|
|
1207
|
AND (A.REQ_REASON LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '')
|
|
1208
|
AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
|
|
1209
|
AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '')
|
|
1210
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
1211
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
1212
|
AND (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
1213
|
|
|
1214
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
1215
|
AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
1216
|
AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='')
|
|
1217
|
--AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID)))
|
|
1218
|
AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE))
|
|
1219
|
AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE))
|
|
1220
|
AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '')
|
|
1221
|
AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '')
|
|
1222
|
|
|
1223
|
AND A.RECORD_STATUS = '1'
|
|
1224
|
AND (((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ( A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND (DEP_ID=@DEP_ID OR @BRANCH_TYPE <>'HS')) OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID) )OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID) )) OR A.USER_DVMS=@p_TLNAME_USER)
|
|
1225
|
AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
1226
|
AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR OR @p_YEAR = '' OR @p_YEAR IS NULL OR @p_YEAR = 0)
|
|
1227
|
AND (A.REQ_PARENT_ID IS NULL OR A.REQ_PARENT_ID='')
|
|
1228
|
|
|
1229
|
AND( NXL.NGUOIXULYTLNAME LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
1230
|
OR NXL.NGUOIXULY LIKE '%' + @p_NGUOIXULY + '%')
|
|
1231
|
|
|
1232
|
ORDER BY A.CREATE_DT DESC
|
|
1233
|
END
|
|
1234
|
ELSE IF(@p_TYPE='TTCT' )
|
|
1235
|
BEGIN
|
|
1236
|
|
|
1237
|
SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE,A.IS_KT,
|
|
1238
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
1239
|
A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME,
|
|
1240
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
1241
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
1242
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
1243
|
RP.ROLE_USER,
|
|
1244
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
1245
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
1246
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
1247
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
1248
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
1249
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
1250
|
PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT ,
|
|
1251
|
'' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID,
|
|
1252
|
RPN.STATUS AS STATUS_NEXT,
|
|
1253
|
RP.STATUS AS STATUS_CURR,
|
|
1254
|
'' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER,
|
|
1255
|
--NGUOI XU LY
|
|
1256
|
--dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'PYC-DVKD',0) AS NGUOIXULY
|
|
1257
|
NXL.NGUOIXULY AS NGUOIXULY
|
|
1258
|
FROM TR_REQUEST_DOC A
|
|
1259
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
1260
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
1261
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
1262
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
1263
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
1264
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
1265
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
1266
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
1267
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
1268
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
1269
|
LEFT JOIN
|
|
1270
|
(
|
|
1271
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
1272
|
dbo.PL_REQUEST_PROCESS
|
|
1273
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
1274
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
1275
|
LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
1276
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
1277
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
1278
|
LEFT JOIN
|
|
1279
|
(
|
|
1280
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
1281
|
dbo.CM_DMMS
|
|
1282
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
1283
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
1284
|
UNION ALL
|
|
1285
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
1286
|
FROM dbo.CM_DVDM
|
|
1287
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
1288
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
1289
|
LEFT JOIN
|
|
1290
|
(
|
|
1291
|
SELECT Results.REQ_ID,STUFF((select ', ' + RE.TLFullName
|
|
1292
|
FROM @lstREQUEST RE
|
|
1293
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
1294
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULY,
|
|
1295
|
STUFF((select ', ' + RE.TLNAME
|
|
1296
|
FROM @lstREQUEST RE
|
|
1297
|
WHERE RE.REQ_ID=Results.REQ_ID
|
|
1298
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOIXULYTLNAME
|
|
1299
|
FROM @lstREQUEST Results
|
|
1300
|
GROUP BY REQ_ID
|
|
1301
|
) NXL ON NXL.REQ_ID=A.REQ_ID
|
|
1302
|
|
|
1303
|
|
|
1304
|
WHERE 1 = 1
|
|
1305
|
AND A.PL_REQ_ID=@p_PL_REQ_ID
|
|
1306
|
|
|
1307
|
ORDER BY A.CREATE_DT DESC
|
|
1308
|
END
|
|
1309
|
END
|
|
1310
|
END
|
|
1311
|
|