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