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