|
1
|
-- PROCEDURE NAME: TR_RATE_SUPPLIER_MASTER_Search
|
|
2
|
|
|
3
|
DECLARE @p_RATE_ID varchar(15) = NULL,
|
|
4
|
@p_RATE_REQ_NO varchar(30) = '000006/2023/PÐG-0690903',
|
|
5
|
@p_SUP_ID varchar(15) = NULL,
|
|
6
|
@p_RATE_FROM_DT varchar(20) = NULL,
|
|
7
|
@p_RATE_TO_DT varchar(20) = NULL,
|
|
8
|
@p_HH_ID varchar(15) = NULL,
|
|
9
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
10
|
@p_CREATE_DT varchar(20) = NULL,
|
|
11
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
12
|
@p_MAKER_ID varchar(15) = NULL,
|
|
13
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
14
|
@p_APPROVE_DT varchar(20) = NULL,
|
|
15
|
@p_PROCESS_STATUS varchar(15) = NULL,
|
|
16
|
@p_BRANCH_ID varchar(15) = N'',
|
|
17
|
@p_DEP_ID varchar(15) = NULL,
|
|
18
|
@p_FROM_DT varchar(20) = NULL,
|
|
19
|
@p_TO_DT varchar(20) = NULL,
|
|
20
|
@p_TYPE varchar(15) = N'DVKD',
|
|
21
|
@p_NGUOIXULY varchar(20) = NULL,
|
|
22
|
@p_IS_TRANSFER varchar(1) = NULL,
|
|
23
|
@p_USER_LOGIN varchar(15) = N'',
|
|
24
|
@p_TOP int = NULL
|
|
25
|
|
|
26
|
|
|
27
|
DECLARE @p_USER_LOGIN_ROLE VARCHAR(15) = (
|
|
28
|
SELECT RoleName FROM TL_USER WHERE TLNANME = @p_USER_LOGIN
|
|
29
|
)
|
|
30
|
DECLARE @p_BRANCH_TYPE VARCHAR(15) = (
|
|
31
|
SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID
|
|
32
|
)
|
|
33
|
------------------Bảng DVDM-------------
|
|
34
|
DECLARE @AUTHOR_DVDM TABLE
|
|
35
|
(
|
|
36
|
ROLE_ID VARCHAR(100),
|
|
37
|
BRANCH_ID VARCHAR(20),
|
|
38
|
DEP_ID VARCHAR(20),
|
|
39
|
DVDM_ID VARCHAR(20)
|
|
40
|
)
|
|
41
|
|
|
42
|
INSERT INTO @AUTHOR_DVDM
|
|
43
|
(
|
|
44
|
ROLE_ID,
|
|
45
|
BRANCH_ID,
|
|
46
|
DEP_ID,
|
|
47
|
DVDM_ID
|
|
48
|
)
|
|
49
|
SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
50
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
51
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
52
|
WHERE TU.TLNANME=@p_USER_LOGIN
|
|
53
|
UNION ALL
|
|
54
|
SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
55
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
56
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
57
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
58
|
WHERE TU.TLNANME=@p_USER_LOGIN
|
|
59
|
UNION ALL
|
|
60
|
SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
61
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
62
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
63
|
WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
64
|
UNION ALL
|
|
65
|
SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
66
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
67
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
68
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
69
|
WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
70
|
|
|
71
|
---------------------Bảng DMMS------------------
|
|
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
|
INSERT INTO @AUTHOR_DMMS
|
|
80
|
(
|
|
81
|
ROLE_ID,
|
|
82
|
BRANCH_ID,
|
|
83
|
DEP_ID,
|
|
84
|
DMMS_ID
|
|
85
|
)
|
|
86
|
--SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
87
|
--LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
88
|
--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')
|
|
89
|
--WHERE TLNANME=@p_USER_LOGIN
|
|
90
|
SELECT TUR.ROLE_ID AS RoleName, TUR.BRANCH_ID AS TLSUBBRID, TUR.DEPT_ID AS SECUR_CODE, MS.DMMS_ID
|
|
91
|
FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) TUR
|
|
92
|
LEFT JOIN CM_BRANCH CB ON CB.BRANCH_ID = TUR.BRANCH_ID
|
|
93
|
LEFT JOIN CM_DMMS MS ON MS.BRANCH_ID = TUR.BRANCH_ID AND (TUR.DEPT_ID = MS.DEP_ID OR CB.BRANCH_TYPE <> 'HS')
|
|
94
|
UNION ALL
|
|
95
|
SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
96
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
97
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
98
|
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')
|
|
99
|
WHERE TLNANME=@p_USER_LOGIN
|
|
100
|
UNION ALL
|
|
101
|
SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
102
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
103
|
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')
|
|
104
|
WHERE TM1.TLNAME=@p_USER_LOGIN 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,MS.DMMS_ID 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
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
109
|
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')
|
|
110
|
WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
111
|
|
|
112
|
|
|
113
|
----------BẢNG NGƯỜI XỬ LÝ TIẾP THEO-----------
|
|
114
|
DECLARE @t_NEXT_PROCESS_USER TABLE(
|
|
115
|
REQ_ID VARCHAR(15),
|
|
116
|
PROCESS_ID VARCHAR(50),
|
|
117
|
DVDM_NAME NVARCHAR(200),
|
|
118
|
TLNAME VARCHAR(50),
|
|
119
|
TL_FULLNAME NVARCHAR(200),
|
|
120
|
NOTES NVARCHAR(500)
|
|
121
|
)
|
|
122
|
INSERT INTO @t_NEXT_PROCESS_USER (REQ_ID, PROCESS_ID, DVDM_NAME, TLNAME, TL_FULLNAME, NOTES)
|
|
123
|
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
|
|
124
|
FROM
|
|
125
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
126
|
WHERE EXISTS(SELECT TRD.RATE_ID FROM dbo.TR_RATE_SUPPLIER_MASTER TRD WHERE TRD.RATE_ID=PRC.REQ_ID)
|
|
127
|
AND PRC.PROCESS_ID ='DMMS' AND PRC.STATUS='C'
|
|
128
|
) PL
|
|
129
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
130
|
LEFT JOIN dbo.TR_RATE_SUPPLIER_MASTER TR ON TR.RATE_ID=PL.REQ_ID
|
|
131
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
132
|
LEFT JOIN
|
|
133
|
(
|
|
134
|
------------BAODNQ 11/10/2022 : Ở bước điều phối lần đầu tiên cả GDDV và KSV cùng thấy phiếu--------------
|
|
135
|
--SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
136
|
--LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
137
|
--WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
138
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
139
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
140
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R'
|
|
141
|
OR (PRC.STATUS_JOB = 'U' AND EXISTS(
|
|
142
|
SELECT PC.ID FROM PL_REQUEST_PROCESS_CHILD PC
|
|
143
|
WHERE PC.REQ_ID = PRC.REQ_ID AND PC.TYPE_JOB = 'KS'
|
|
144
|
AND PC.MAKER_ID IS NULL AND PC.TRANFER_DT IS NULL
|
|
145
|
)
|
|
146
|
AND NOT EXISTS(SELECT PC_2.ID FROM PL_REQUEST_PROCESS_CHILD PC_2 WHERE PC_2.REQ_ID = PRC.REQ_ID AND PC_2.TYPE_JOB = 'XL')
|
|
147
|
)
|
|
148
|
)
|
|
149
|
------------END BAODNQ 11/10/2022----------------
|
|
150
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
151
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
152
|
LEFT JOIN
|
|
153
|
(
|
|
154
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID
|
|
155
|
FROM
|
|
156
|
(
|
|
157
|
SELECT TS.TLNANME,TS.TLFullName,
|
|
158
|
--TS.RoleName,
|
|
159
|
AR.DisplayName AS RoleName,
|
|
160
|
TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
161
|
dbo.TL_USER TS
|
|
162
|
JOIN AbpUserRoles AU ON TS.ID = AU.UserId
|
|
163
|
JOIN AbpRoles AR ON AU.RoleId = AR.Id
|
|
164
|
UNION ALL
|
|
165
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
166
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
167
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
168
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
169
|
) TU
|
|
170
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
171
|
LEFT JOIN(
|
|
172
|
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
|
|
173
|
UNION ALL
|
|
174
|
SELECT DVDM.DVDM_ID AS DMMS_ID,DVDM.DEP_ID,DVDM.BRANCH_ID FROM
|
|
175
|
(
|
|
176
|
SELECT CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID FROM dbo.CM_DVDM CD
|
|
177
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.DVDM_ID=CD.DVDM_ID
|
|
178
|
LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID
|
|
179
|
WHERE CD.IS_KHOI <>1
|
|
180
|
GROUP BY CD.DVDM_ID,PCDT.BRANCH_ID,PCDT.DEP_ID
|
|
181
|
) DVDM
|
|
182
|
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
183
|
) TempU ON (
|
|
184
|
--(TempU.RoleName=PL.ROLE_USER OR TempU.RoleName = 'KSV')
|
|
185
|
(TempU.RoleName=PL.ROLE_USER
|
|
186
|
--OR (TempU.RoleName = 'KSV' AND NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = PL.REQ_ID AND TempU.TLNANME = TLNAME AND TYPE_JOB = 'KS' AND STATUS_JOB = 'P')))
|
|
187
|
--OR (TempU.RoleName = 'KSV' AND NOT EXISTS(SELECT TOP 1 PP.CHECKER_ID FROM PL_PROCESS PP WHERE PP.REQ_ID = PL.REQ_ID AND PP.PROCESS_ID = 'DMMS' AND PP.CHECKER_ID = TempU.TLNANME ORDER BY PP.APPROVE_DT DESC))
|
|
188
|
)
|
|
189
|
--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
|
|
190
|
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 TM.AUTH_STATUS = 'A')
|
|
191
|
)
|
|
192
|
AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
|
|
193
|
WHERE PL.STATUS='C'
|
|
194
|
UNION ALL
|
|
195
|
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
|
|
196
|
FROM
|
|
197
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
198
|
WHERE EXISTS(SELECT TRD.RATE_ID FROM dbo.TR_RATE_SUPPLIER_MASTER TRD WHERE TRD.RATE_ID=PRC.REQ_ID)
|
|
199
|
AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE'
|
|
200
|
AND PRC.STATUS='C' AND (PRC.IS_HAS_CHILD=0 OR PRC.IS_HAS_CHILD IS NULL)
|
|
201
|
) PL
|
|
202
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
203
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
204
|
LEFT JOIN
|
|
205
|
(
|
|
206
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
207
|
FROM (
|
|
208
|
SELECT TS.TLNANME,TS.TLFullName,
|
|
209
|
--TS.RoleName,
|
|
210
|
AR.DisplayName AS RoleName,
|
|
211
|
TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
212
|
dbo.TL_USER TS
|
|
213
|
JOIN AbpUserRoles AU ON TS.ID = AU.UserId
|
|
214
|
JOIN AbpRoles AR ON AU.RoleId = AR.Id
|
|
215
|
UNION ALL
|
|
216
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
217
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
218
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
219
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
220
|
) TU
|
|
221
|
LEFT JOIN
|
|
222
|
(
|
|
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
|
|
225
|
) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
226
|
) 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='')
|
|
227
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
228
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
229
|
WHERE STATUS='C'
|
|
230
|
UNION ALL
|
|
231
|
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
|
|
232
|
FROM
|
|
233
|
(SELECT * FROM dbo.PL_REQUEST_PROCESS PRC
|
|
234
|
WHERE EXISTS(SELECT TRD.RATE_ID FROM dbo.TR_RATE_SUPPLIER_MASTER TRD WHERE TRD.RATE_ID=PRC.REQ_ID)
|
|
235
|
AND PRC.PROCESS_ID <>'DMMS' AND PRC.PROCESS_ID <> 'NEW' AND PRC.PROCESS_ID <>'APPROVE'
|
|
236
|
AND PRC.STATUS='C' AND PRC.IS_HAS_CHILD=1
|
|
237
|
) PL
|
|
238
|
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
|
|
239
|
LEFT JOIN CM_BRANCH BR ON PL.BRANCH_ID = BR.BRANCH_ID
|
|
240
|
LEFT JOIN
|
|
241
|
(
|
|
242
|
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
|
|
243
|
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
|
|
244
|
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
|
|
245
|
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
|
|
246
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
|
|
247
|
LEFT JOIN
|
|
248
|
(
|
|
249
|
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID
|
|
250
|
FROM (
|
|
251
|
SELECT TS.TLNANME,TS.TLFullName,TS.RoleName,TS.TLSUBBRID,TS.SECUR_CODE FROM
|
|
252
|
dbo.TL_USER TS
|
|
253
|
UNION ALL
|
|
254
|
SELECT TM.TLNAME TLNANME,TS.TLFullName,TM.ROLE_NEW RoleName,TM.BRANCH_ID TLSUBBRID,TM.DEP_ID SECUR_CODE FROM
|
|
255
|
dbo.TL_SYS_ROLE_MAPPING TM
|
|
256
|
LEFT JOIN dbo.TL_USER TS ON TS.TLNANME=TM.TLNAME
|
|
257
|
WHERE CAST(TM.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
258
|
) TU
|
|
259
|
LEFT JOIN(
|
|
260
|
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
|
|
261
|
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
|
|
262
|
) TempU ON (TempU.RoleName=PL.ROLE_USER 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='')
|
|
263
|
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
|
|
264
|
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
|
|
265
|
WHERE PL.STATUS='C'
|
|
266
|
|
|
267
|
BEGIN
|
|
268
|
--------DVKD XỬ LÝ-----------
|
|
269
|
IF(@p_TYPE = 'DVKD')
|
|
270
|
BEGIN
|
|
271
|
-- PAGING BEGIN
|
|
272
|
BEGIN
|
|
273
|
SELECT COUNT(*) -- SELECT END
|
|
274
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
275
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
276
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
277
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
278
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
279
|
--LEFT JOIN CM_ALLCODE PS ON A.PROCESS_STATUS = PS.CDVAL AND PS.CDTYPE = 'RATE_SUP' AND PS.CDNAME = 'PROCESS_STATUS'
|
|
280
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
281
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
282
|
LEFT JOIN
|
|
283
|
(
|
|
284
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
285
|
dbo.PL_REQUEST_PROCESS
|
|
286
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
287
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
288
|
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.RATE_ID
|
|
289
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
290
|
LEFT JOIN(
|
|
291
|
SELECT RESULT.REQ_ID,
|
|
292
|
STUFF(
|
|
293
|
(SELECT ', ' + NPU.TLNAME
|
|
294
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
295
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
296
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
297
|
STUFF(
|
|
298
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
299
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
300
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
301
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
302
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
303
|
GROUP BY REQ_ID
|
|
304
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
305
|
|
|
306
|
WHERE 1=1
|
|
307
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
308
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
309
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
310
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
311
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
312
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
313
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
314
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
315
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
316
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
317
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
318
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
319
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
320
|
AND (
|
|
321
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
322
|
OR(@p_PROCESS_STATUS LIKE 'DMMS%'
|
|
323
|
AND RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS'
|
|
324
|
)
|
|
325
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
326
|
)
|
|
327
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
328
|
AND ((@p_BRANCH_TYPE = 'HS' AND A.DEP_ID = @p_DEP_ID)
|
|
329
|
OR @p_BRANCH_TYPE <> 'HS'
|
|
330
|
OR @p_DEP_ID IS NULL OR @p_DEP_ID = ''
|
|
331
|
)
|
|
332
|
AND (
|
|
333
|
A.MAKER_ID = @p_USER_LOGIN
|
|
334
|
OR (A.SIGN_USER = @p_USER_LOGIN AND A.AUTH_STATUS <> 'E' AND A.PROCESS_STATUS <> 'NEW')
|
|
335
|
OR (@p_USER_LOGIN IN (SELECT TLNAME FROM @t_NEXT_PROCESS_USER WHERE REQ_ID = A.RATE_ID))
|
|
336
|
OR (@p_USER_LOGIN IN (SELECT CHECKER_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND STATUS = 'P'))
|
|
337
|
)
|
|
338
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
339
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
340
|
|
|
341
|
|
|
342
|
;WITH QUERY_DATA AS (
|
|
343
|
SELECT A.*,
|
|
344
|
B.SUP_NAME,
|
|
345
|
C.HH_NAME,
|
|
346
|
D.BRANCH_NAME,
|
|
347
|
E.CONTENT AS DECISION_NAME,
|
|
348
|
MK.TLFullName AS MAKER_NAME,
|
|
349
|
RP.NOTES AS PROCESS_STATUS_NAME, --tình trạng xử lý
|
|
350
|
RPN.NOTES AS PROCESS_STATUS_NEXT, -- bước xử lý tiếp theo
|
|
351
|
CASE
|
|
352
|
WHEN A.PROCESS_STATUS ='SIGN'
|
|
353
|
THEN TL.TLFullName
|
|
354
|
ELSE NXL.NGUOI_XU_LY_NAME
|
|
355
|
END AS NGUOIXULY, -- người xử lý tiếp theo
|
|
356
|
--PAD.APPROVE_DT AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
357
|
CASE
|
|
358
|
WHEN RPN.PROCESS_ID = 'APPROVE' THEN RP.APPROVE_DT
|
|
359
|
ELSE NULL
|
|
360
|
END
|
|
361
|
AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
362
|
RP.ID AS REF_ID
|
|
363
|
|
|
364
|
, ROW_NUMBER() OVER (ORDER BY A.CREATE_DT DESC
|
|
365
|
) AS __ROWNUM-- SELECT END
|
|
366
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
367
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
368
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
369
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
370
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
371
|
--LEFT JOIN CM_ALLCODE PS ON A.PROCESS_STATUS = PS.CDVAL AND PS.CDTYPE = 'RATE_SUP' AND PS.CDNAME = 'PROCESS_STATUS'
|
|
372
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
373
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
374
|
LEFT JOIN
|
|
375
|
(
|
|
376
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
377
|
dbo.PL_REQUEST_PROCESS
|
|
378
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
379
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
380
|
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.RATE_ID
|
|
381
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
382
|
LEFT JOIN(
|
|
383
|
SELECT RESULT.REQ_ID,
|
|
384
|
STUFF(
|
|
385
|
(SELECT ', ' + NPU.TLNAME
|
|
386
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
387
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
388
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
389
|
STUFF(
|
|
390
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
391
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
392
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
393
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
394
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
395
|
GROUP BY REQ_ID
|
|
396
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
397
|
|
|
398
|
WHERE 1=1
|
|
399
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
400
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
401
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
402
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
403
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
404
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
405
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
406
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
407
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
408
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
409
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
410
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
411
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
412
|
AND (
|
|
413
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
414
|
OR(@p_PROCESS_STATUS LIKE 'DMMS%'
|
|
415
|
AND RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS'
|
|
416
|
)
|
|
417
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
418
|
)
|
|
419
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
420
|
AND ((@p_BRANCH_TYPE = 'HS' AND A.DEP_ID = @p_DEP_ID)
|
|
421
|
OR @p_BRANCH_TYPE <> 'HS'
|
|
422
|
OR @p_DEP_ID IS NULL OR @p_DEP_ID = ''
|
|
423
|
)
|
|
424
|
AND (
|
|
425
|
A.MAKER_ID = @p_USER_LOGIN
|
|
426
|
OR (A.SIGN_USER = @p_USER_LOGIN AND A.AUTH_STATUS <> 'E' AND A.PROCESS_STATUS <> 'NEW')
|
|
427
|
OR (@p_USER_LOGIN IN (SELECT TLNAME FROM @t_NEXT_PROCESS_USER WHERE REQ_ID = A.RATE_ID))
|
|
428
|
OR (@p_USER_LOGIN IN (SELECT CHECKER_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND STATUS = 'P'))
|
|
429
|
)
|
|
430
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
431
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
432
|
|
|
433
|
|
|
434
|
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
|
|
435
|
END-- PAGING END
|
|
436
|
END
|
|
437
|
---------GDK/PTGDK XỬ LÝ-----------
|
|
438
|
ELSE IF(@p_TYPE LIKE 'GDK%')
|
|
439
|
BEGIN
|
|
440
|
|
|
441
|
-- PAGING BEGIN
|
|
442
|
BEGIN
|
|
443
|
SELECT COUNT(*) -- SELECT END
|
|
444
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
445
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
446
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
447
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
448
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
449
|
--LEFT JOIN CM_ALLCODE PS ON A.PROCESS_STATUS = PS.CDVAL AND PS.CDTYPE = 'RATE_SUP' AND PS.CDNAME = 'PROCESS_STATUS'
|
|
450
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
451
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
452
|
LEFT JOIN
|
|
453
|
(
|
|
454
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
455
|
dbo.PL_REQUEST_PROCESS
|
|
456
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
457
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
458
|
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.RATE_ID
|
|
459
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
460
|
LEFT JOIN(
|
|
461
|
SELECT RESULT.REQ_ID,
|
|
462
|
STUFF(
|
|
463
|
(SELECT ', ' + NPU.TLNAME
|
|
464
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
465
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
466
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
467
|
STUFF(
|
|
468
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
469
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
470
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
471
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
472
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
473
|
GROUP BY REQ_ID
|
|
474
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
475
|
--INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.RATE_ID AND PLRP.STATUS <> 'U'
|
|
476
|
INNER JOIN (
|
|
477
|
SELECT REQ_ID, ROLE_USER, DVDM_ID, STATUS FROM PL_REQUEST_PROCESS
|
|
478
|
WHERE STATUS <> 'U'
|
|
479
|
GROUP BY REQ_ID, ROLE_USER, DVDM_ID, STATUS
|
|
480
|
) PLRP ON PLRP.REQ_ID = A.RATE_ID
|
|
481
|
|
|
482
|
WHERE 1=1
|
|
483
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
484
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
485
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
486
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
487
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
488
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
489
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
490
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
491
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
492
|
AND (
|
|
493
|
A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%'
|
|
494
|
OR (@p_AUTH_STATUS = 'U' AND A.PROCESS_STATUS <> 'APPROVE' AND PLRP.STATUS = 'C')
|
|
495
|
OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''
|
|
496
|
)
|
|
497
|
--AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
498
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
499
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
500
|
AND (
|
|
501
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
502
|
OR(@p_PROCESS_STATUS LIKE 'DMMS%'
|
|
503
|
AND RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS'
|
|
504
|
)
|
|
505
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
506
|
)
|
|
507
|
AND (EXISTS(
|
|
508
|
SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH
|
|
509
|
WHERE AUTH.ROLE_ID=PLRP.ROLE_USER
|
|
510
|
--AND (PLRP.DVDM_ID=AUTH.DVDM_ID OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL)
|
|
511
|
AND (PLRP.DVDM_ID=AUTH.DVDM_ID)
|
|
512
|
--AND PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW' AND PLRP.PROCESS_ID <> 'DVCM'
|
|
513
|
))
|
|
514
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
515
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
516
|
|
|
517
|
|
|
518
|
;WITH QUERY_DATA AS (
|
|
519
|
SELECT A.*,
|
|
520
|
B.SUP_NAME,
|
|
521
|
C.HH_NAME,
|
|
522
|
D.BRANCH_NAME,
|
|
523
|
E.CONTENT AS DECISION_NAME,
|
|
524
|
MK.TLFullName AS MAKER_NAME,
|
|
525
|
RP.NOTES AS PROCESS_STATUS_NAME, --tình trạng xử lý
|
|
526
|
RPN.NOTES AS PROCESS_STATUS_NEXT, -- bước xử lý tiếp theo
|
|
527
|
CASE
|
|
528
|
WHEN A.PROCESS_STATUS ='SIGN'
|
|
529
|
THEN TL.TLFullName
|
|
530
|
ELSE NXL.NGUOI_XU_LY_NAME
|
|
531
|
END AS NGUOIXULY, -- người xử lý tiếp theo
|
|
532
|
--RP.APPROVE_DT AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
533
|
CASE
|
|
534
|
WHEN RPN.PROCESS_ID = 'APPROVE' THEN RP.APPROVE_DT
|
|
535
|
ELSE NULL
|
|
536
|
END
|
|
537
|
AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
538
|
RP.ID AS REF_ID
|
|
539
|
, ROW_NUMBER() OVER (ORDER BY A.CREATE_DT DESC
|
|
540
|
) AS __ROWNUM-- SELECT END
|
|
541
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
542
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
543
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
544
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
545
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
546
|
--LEFT JOIN CM_ALLCODE PS ON A.PROCESS_STATUS = PS.CDVAL AND PS.CDTYPE = 'RATE_SUP' AND PS.CDNAME = 'PROCESS_STATUS'
|
|
547
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
548
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
549
|
LEFT JOIN
|
|
550
|
(
|
|
551
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
552
|
dbo.PL_REQUEST_PROCESS
|
|
553
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
554
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
555
|
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.RATE_ID
|
|
556
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
557
|
LEFT JOIN(
|
|
558
|
SELECT RESULT.REQ_ID,
|
|
559
|
STUFF(
|
|
560
|
(SELECT ', ' + NPU.TLNAME
|
|
561
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
562
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
563
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
564
|
STUFF(
|
|
565
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
566
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
567
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
568
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
569
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
570
|
GROUP BY REQ_ID
|
|
571
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
572
|
--INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.RATE_ID AND PLRP.STATUS <> 'U'
|
|
573
|
INNER JOIN (
|
|
574
|
SELECT REQ_ID, ROLE_USER, DVDM_ID, STATUS FROM PL_REQUEST_PROCESS
|
|
575
|
WHERE STATUS <> 'U'
|
|
576
|
GROUP BY REQ_ID, ROLE_USER, DVDM_ID, STATUS
|
|
577
|
) PLRP ON PLRP.REQ_ID = A.RATE_ID
|
|
578
|
|
|
579
|
WHERE 1=1
|
|
580
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
581
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
582
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
583
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
584
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
585
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
586
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
587
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
588
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
589
|
AND (
|
|
590
|
A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%'
|
|
591
|
OR (@p_AUTH_STATUS = 'U' AND A.PROCESS_STATUS <> 'APPROVE' AND PLRP.STATUS = 'C')
|
|
592
|
OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''
|
|
593
|
)
|
|
594
|
--AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
595
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
596
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
597
|
AND (
|
|
598
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
599
|
OR(@p_PROCESS_STATUS LIKE 'DMMS%'
|
|
600
|
AND RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS'
|
|
601
|
)
|
|
602
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
603
|
)
|
|
604
|
AND (EXISTS(
|
|
605
|
SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH
|
|
606
|
WHERE AUTH.ROLE_ID=PLRP.ROLE_USER
|
|
607
|
--AND (PLRP.DVDM_ID=AUTH.DVDM_ID OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL)
|
|
608
|
AND (PLRP.DVDM_ID=AUTH.DVDM_ID)
|
|
609
|
--AND PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW' AND PLRP.PROCESS_ID <> 'DVCM'
|
|
610
|
))
|
|
611
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
612
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
613
|
|
|
614
|
|
|
615
|
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
|
|
616
|
END-- PAGING END
|
|
617
|
END
|
|
618
|
------------------ĐIỀU PHỐI------------
|
|
619
|
ELSE IF(@p_TYPE = 'TFJOB')
|
|
620
|
BEGIN
|
|
621
|
-- PAGING BEGIN
|
|
622
|
BEGIN
|
|
623
|
SELECT COUNT(*) -- SELECT END
|
|
624
|
|
|
625
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
626
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
627
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
628
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
629
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
630
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
631
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
632
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.RATE_ID AND PLRP.STATUS = 'C' AND PLRP.IS_HAS_CHILD = 1
|
|
633
|
LEFT JOIN
|
|
634
|
(
|
|
635
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
636
|
dbo.PL_REQUEST_PROCESS
|
|
637
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
638
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
639
|
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.RATE_ID
|
|
640
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
641
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.RATE_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
642
|
LEFT JOIN TL_USER TFU ON TFU.TLNANME = RPC.MAKER_ID
|
|
643
|
LEFT JOIN TL_USER TU ON TU.TLNANME = RPC.TLNAME
|
|
644
|
LEFT JOIN(
|
|
645
|
SELECT RESULT.REQ_ID,
|
|
646
|
STUFF(
|
|
647
|
(SELECT ', ' + NPU.TLNAME
|
|
648
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
649
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
650
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
651
|
STUFF(
|
|
652
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
653
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
654
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
655
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
656
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
657
|
GROUP BY REQ_ID
|
|
658
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
659
|
|
|
660
|
|
|
661
|
WHERE 1=1
|
|
662
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
663
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
664
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
665
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
666
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
667
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
668
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
669
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
670
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
671
|
AND (
|
|
672
|
A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%'
|
|
673
|
OR(@p_AUTH_STATUS = 'A' AND A.PROCESS_STATUS = 'APPROVE')
|
|
674
|
OR(@p_AUTH_STATUS = 'U' AND A.PROCESS_STATUS <> 'APPROVE')
|
|
675
|
OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''
|
|
676
|
)
|
|
677
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
678
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
679
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
680
|
AND (
|
|
681
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
682
|
OR(@p_PROCESS_STATUS = 'DMMS_XL'
|
|
683
|
AND (
|
|
684
|
(RPC.TYPE_JOB = 'XL' OR RPC.TYPE_JOB = 'KS')
|
|
685
|
AND RPC.STATUS_JOB = 'C'
|
|
686
|
--OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS')
|
|
687
|
)
|
|
688
|
)
|
|
689
|
OR(@p_PROCESS_STATUS = 'DMMS_APP'
|
|
690
|
AND (
|
|
691
|
RPC.TYPE_JOB = 'TP'
|
|
692
|
AND RPC.STATUS_JOB = 'C'
|
|
693
|
OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS' AND NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.RATE_ID AND TYPE_JOB = 'TP' AND STATUS_JOB = 'C'))
|
|
694
|
)
|
|
695
|
)
|
|
696
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
697
|
)
|
|
698
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
699
|
AND ((@p_BRANCH_TYPE = 'HS' AND A.DEP_ID = @p_DEP_ID)
|
|
700
|
OR @p_BRANCH_TYPE <> 'HS'
|
|
701
|
OR @p_DEP_ID IS NULL OR @p_DEP_ID = ''
|
|
702
|
)
|
|
703
|
AND (
|
|
704
|
((RPC.REQ_ID IS NOT NULL AND RPC.REQ_ID <> '' AND (RPC.MAKER_ID = @p_USER_LOGIN AND RPC.MAKER_ID IS NOT NULL)) AND @p_IS_TRANSFER = '1')
|
|
705
|
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_USER_LOGIN OR RPC.MAKER_ID IS NULL))) AND @p_IS_TRANSFER = '0')
|
|
706
|
OR @p_IS_TRANSFER = '' OR @p_IS_TRANSFER IS NULL
|
|
707
|
)
|
|
708
|
AND (
|
|
709
|
EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND PLRP.DVDM_ID=AUTH.DVDM_ID)
|
|
710
|
OR EXISTS(
|
|
711
|
SELECT AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH
|
|
712
|
WHERE A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_STATUS='DMMS'
|
|
713
|
AND (AUTH.ROLE_ID=PLRP.ROLE_USER OR AUTH.ROLE_ID = 'KSV')
|
|
714
|
)
|
|
715
|
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_STATUS='DMMS')
|
|
716
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_USER_LOGIN AND TYPE_JOB='KS')
|
|
717
|
)
|
|
718
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
719
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
720
|
----------BAODNQ 2/12/2022 : Nếu PĐG NCC đã được NVXL gửi phê duyệt /đã dc KSV phê duyệt(TH ko điều phối)/ đã dc DMMS duyệt hoàn tất
|
|
721
|
----------------không tìm thấy phiếu ở màn hình điều phối-----------------
|
|
722
|
AND(
|
|
723
|
NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.RATE_ID
|
|
724
|
AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND PROCESS_ID = 'DMMS')
|
|
725
|
AND (TYPE_JOB = 'XL' OR TYPE_JOB = 'KS') AND STATUS_JOB = 'P'
|
|
726
|
)
|
|
727
|
)
|
|
728
|
|
|
729
|
|
|
730
|
;WITH QUERY_DATA AS (
|
|
731
|
SELECT A.*,
|
|
732
|
B.SUP_NAME,
|
|
733
|
C.HH_NAME,
|
|
734
|
D.BRANCH_NAME,
|
|
735
|
E.CONTENT AS DECISION_NAME,
|
|
736
|
MK.TLFullName AS MAKER_NAME,
|
|
737
|
RP.NOTES AS PROCESS_STATUS_NAME, --tình trạng xử lý
|
|
738
|
RPN.NOTES AS PROCESS_STATUS_NEXT, -- bước xử lý tiếp theo
|
|
739
|
CASE
|
|
740
|
WHEN A.PROCESS_STATUS ='SIGN'
|
|
741
|
THEN TL.TLFullName
|
|
742
|
ELSE NXL.NGUOI_XU_LY_NAME
|
|
743
|
END AS NGUOIXULY, -- người xử lý tiếp theo
|
|
744
|
--RP.APPROVE_DT AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
745
|
CASE
|
|
746
|
WHEN RPN.PROCESS_ID = 'APPROVE' THEN RP.APPROVE_DT
|
|
747
|
ELSE NULL
|
|
748
|
END
|
|
749
|
AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
750
|
ISNULL(RPC.TYPE_JOB, 'KS') AS TYPE_JOB, RPC.TLNAME AS USER_JOB, TU.TLFullName AS USER_JOB_NAME,
|
|
751
|
TFU.TLFullName AS TRANSFER_MAKER_NAME, RPC.TLNAME AS TRANSFER_MAKER, RPC.TRANFER_DT,
|
|
752
|
PLRP.ID AS REF_ID
|
|
753
|
, ROW_NUMBER() OVER (ORDER BY A.CREATE_DT DESC
|
|
754
|
) AS __ROWNUM-- SELECT END
|
|
755
|
|
|
756
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
757
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
758
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
759
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
760
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
761
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
762
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
763
|
INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.RATE_ID AND PLRP.STATUS = 'C' AND PLRP.IS_HAS_CHILD = 1
|
|
764
|
LEFT JOIN
|
|
765
|
(
|
|
766
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
767
|
dbo.PL_REQUEST_PROCESS
|
|
768
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
769
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
770
|
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.RATE_ID
|
|
771
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
772
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.RATE_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
773
|
LEFT JOIN TL_USER TFU ON TFU.TLNANME = RPC.MAKER_ID
|
|
774
|
LEFT JOIN TL_USER TU ON TU.TLNANME = RPC.TLNAME
|
|
775
|
LEFT JOIN(
|
|
776
|
SELECT RESULT.REQ_ID,
|
|
777
|
STUFF(
|
|
778
|
(SELECT ', ' + NPU.TLNAME
|
|
779
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
780
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
781
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
782
|
STUFF(
|
|
783
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
784
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
785
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
786
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
787
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
788
|
GROUP BY REQ_ID
|
|
789
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
790
|
|
|
791
|
|
|
792
|
WHERE 1=1
|
|
793
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
794
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
795
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
796
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
797
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
798
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
799
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
800
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
801
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
802
|
AND (
|
|
803
|
A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%'
|
|
804
|
OR(@p_AUTH_STATUS = 'A' AND A.PROCESS_STATUS = 'APPROVE')
|
|
805
|
OR(@p_AUTH_STATUS = 'U' AND A.PROCESS_STATUS <> 'APPROVE')
|
|
806
|
OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''
|
|
807
|
)
|
|
808
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
809
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
810
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
811
|
AND (
|
|
812
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
813
|
OR(@p_PROCESS_STATUS = 'DMMS_XL'
|
|
814
|
AND (
|
|
815
|
(RPC.TYPE_JOB = 'XL' OR RPC.TYPE_JOB = 'KS')
|
|
816
|
AND RPC.STATUS_JOB = 'C'
|
|
817
|
--OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS')
|
|
818
|
)
|
|
819
|
)
|
|
820
|
OR(@p_PROCESS_STATUS = 'DMMS_APP'
|
|
821
|
AND (
|
|
822
|
RPC.TYPE_JOB = 'TP'
|
|
823
|
AND RPC.STATUS_JOB = 'C'
|
|
824
|
OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS' AND NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.RATE_ID AND TYPE_JOB = 'TP' AND STATUS_JOB = 'C'))
|
|
825
|
)
|
|
826
|
)
|
|
827
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
828
|
)
|
|
829
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
830
|
AND ((@p_BRANCH_TYPE = 'HS' AND A.DEP_ID = @p_DEP_ID)
|
|
831
|
OR @p_BRANCH_TYPE <> 'HS'
|
|
832
|
OR @p_DEP_ID IS NULL OR @p_DEP_ID = ''
|
|
833
|
)
|
|
834
|
AND (
|
|
835
|
((RPC.REQ_ID IS NOT NULL AND RPC.REQ_ID <> '' AND (RPC.MAKER_ID = @p_USER_LOGIN AND RPC.MAKER_ID IS NOT NULL)) AND @p_IS_TRANSFER = '1')
|
|
836
|
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_USER_LOGIN OR RPC.MAKER_ID IS NULL))) AND @p_IS_TRANSFER = '0')
|
|
837
|
OR @p_IS_TRANSFER = '' OR @p_IS_TRANSFER IS NULL
|
|
838
|
)
|
|
839
|
AND (
|
|
840
|
EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PLRP.ROLE_USER AND PLRP.DVDM_ID=AUTH.DVDM_ID)
|
|
841
|
OR EXISTS(
|
|
842
|
SELECT AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH
|
|
843
|
WHERE A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_STATUS='DMMS'
|
|
844
|
AND (AUTH.ROLE_ID=PLRP.ROLE_USER OR AUTH.ROLE_ID = 'KSV')
|
|
845
|
)
|
|
846
|
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_STATUS='DMMS')
|
|
847
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_USER_LOGIN AND TYPE_JOB='KS')
|
|
848
|
)
|
|
849
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
850
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
851
|
----------BAODNQ 2/12/2022 : Nếu PĐG NCC đã được NVXL gửi phê duyệt /đã dc KSV phê duyệt(TH ko điều phối)/ đã dc DMMS duyệt hoàn tất
|
|
852
|
----------------không tìm thấy phiếu ở màn hình điều phối-----------------
|
|
853
|
AND(
|
|
854
|
NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.RATE_ID
|
|
855
|
AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND PROCESS_ID = 'DMMS')
|
|
856
|
AND (TYPE_JOB = 'XL' OR TYPE_JOB = 'KS') AND STATUS_JOB = 'P'
|
|
857
|
)
|
|
858
|
)
|
|
859
|
|
|
860
|
|
|
861
|
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
|
|
862
|
END-- PAGING END
|
|
863
|
END
|
|
864
|
|
|
865
|
-----------------------------DMMS------------------
|
|
866
|
ELSE IF(@p_TYPE = 'DMMS')
|
|
867
|
BEGIN
|
|
868
|
-- PAGING BEGIN
|
|
869
|
BEGIN
|
|
870
|
SELECT COUNT(*) -- SELECT END
|
|
871
|
|
|
872
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
873
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
874
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
875
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
876
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
877
|
--LEFT JOIN CM_ALLCODE PS ON A.PROCESS_STATUS = PS.CDVAL AND PS.CDTYPE = 'RATE_SUP' AND PS.CDNAME = 'PROCESS_STATUS'
|
|
878
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
879
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
880
|
LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.RATE_ID AND PLRP.STATUS <> 'U' AND PLRP.PROCESS_ID = 'DMMS'
|
|
881
|
LEFT JOIN
|
|
882
|
(
|
|
883
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
884
|
dbo.PL_REQUEST_PROCESS
|
|
885
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
886
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
887
|
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.RATE_ID
|
|
888
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
889
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.RATE_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
890
|
LEFT JOIN TL_USER TFU ON TFU.TLNANME = RPC.MAKER_ID
|
|
891
|
LEFT JOIN TL_USER TU ON TU.TLNANME = RPC.TLNAME
|
|
892
|
LEFT JOIN(
|
|
893
|
SELECT RESULT.REQ_ID,
|
|
894
|
STUFF(
|
|
895
|
(SELECT ', ' + NPU.TLNAME
|
|
896
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
897
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
898
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
899
|
STUFF(
|
|
900
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
901
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
902
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
903
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
904
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
905
|
GROUP BY REQ_ID
|
|
906
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
907
|
|
|
908
|
|
|
909
|
WHERE 1=1
|
|
910
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
911
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
912
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
913
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
914
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
915
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
916
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
917
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
918
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
919
|
AND (
|
|
920
|
A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%'
|
|
921
|
OR (@p_AUTH_STATUS = 'U' AND A.PROCESS_STATUS <> 'APPROVE' AND PLRP.STATUS = 'C')
|
|
922
|
OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''
|
|
923
|
)
|
|
924
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
925
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
926
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
927
|
AND (
|
|
928
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
929
|
OR(@p_PROCESS_STATUS = 'DMMS_XL'
|
|
930
|
AND (
|
|
931
|
(RPC.TYPE_JOB = 'XL' OR RPC.TYPE_JOB = 'KS')
|
|
932
|
AND RPC.STATUS_JOB = 'C'
|
|
933
|
--OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS')
|
|
934
|
)
|
|
935
|
)
|
|
936
|
OR(@p_PROCESS_STATUS = 'DMMS_APP'
|
|
937
|
AND (
|
|
938
|
RPC.TYPE_JOB = 'TP'
|
|
939
|
AND RPC.STATUS_JOB = 'C'
|
|
940
|
OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS' AND NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.RATE_ID AND TYPE_JOB = 'TP' AND STATUS_JOB = 'C'))
|
|
941
|
)
|
|
942
|
)
|
|
943
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
944
|
)
|
|
945
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
946
|
AND ((@p_BRANCH_TYPE = 'HS' AND A.DEP_ID = @p_DEP_ID)
|
|
947
|
OR @p_BRANCH_TYPE <> 'HS'
|
|
948
|
OR @p_DEP_ID IS NULL OR @p_DEP_ID = ''
|
|
949
|
)
|
|
950
|
--AND (
|
|
951
|
-- A.MAKER_ID = @p_USER_LOGIN
|
|
952
|
-- OR (A.SIGN_USER = @p_USER_LOGIN AND A.AUTH_STATUS <> 'E' AND A.PROCESS_STATUS <> 'NEW')
|
|
953
|
-- OR (@p_USER_LOGIN IN (SELECT TLNAME FROM @t_NEXT_PROCESS_USER WHERE REQ_ID = A.RATE_ID))
|
|
954
|
-- OR (@p_USER_LOGIN IN (SELECT CHECKER_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND STATUS = 'P'))
|
|
955
|
--)
|
|
956
|
--AND(
|
|
957
|
-- (RPC.TYPE_JOB = 'KS' OR RPC.TYPE_JOB = 'TP')
|
|
958
|
-- AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS_CHILD TMP WHERE TMP.REQ_ID = A.RATE_ID AND TMP.STATUS_JOB = 'P' AND TMP.TYPE_JOB = 'XL')
|
|
959
|
-- OR(RPC.TYPE_JOB = 'XL')
|
|
960
|
--)
|
|
961
|
AND (
|
|
962
|
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_STATUS='DMMS' OR A.PROCESS_STATUS <> 'DMMS'))
|
|
963
|
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_STATUS='DMMS')
|
|
964
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_USER_LOGIN AND PLRP.PROCESS_ID = 'DMMS')
|
|
965
|
)
|
|
966
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
967
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
968
|
-----------BAODNQ 2/12/2022 : Chỉ tìm thấy phiếu ở màn hình DMMS khi đã có điều phối cho NVXL / khi KSV đã phê duyệt(TH ko điều phối)--------
|
|
969
|
AND(
|
|
970
|
EXISTS(
|
|
971
|
SELECT ID FROM PL_REQUEST_PROCESS_CHILD
|
|
972
|
WHERE REQ_ID = A.RATE_ID
|
|
973
|
AND PROCESS_ID = (SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND PROCESS_ID = 'DMMS')
|
|
974
|
AND (TYPE_JOB = 'XL' OR (TYPE_JOB = 'KS' AND STATUS_JOB = 'P'))
|
|
975
|
)
|
|
976
|
)
|
|
977
|
|
|
978
|
|
|
979
|
;WITH QUERY_DATA AS (
|
|
980
|
SELECT A.*,
|
|
981
|
B.SUP_NAME,
|
|
982
|
C.HH_NAME,
|
|
983
|
D.BRANCH_NAME,
|
|
984
|
E.CONTENT AS DECISION_NAME,
|
|
985
|
MK.TLFullName AS MAKER_NAME,
|
|
986
|
RP.NOTES AS PROCESS_STATUS_NAME, --tình trạng xử lý
|
|
987
|
RPN.NOTES AS PROCESS_STATUS_NEXT, -- bước xử lý tiếp theo
|
|
988
|
CASE
|
|
989
|
WHEN A.PROCESS_STATUS ='SIGN'
|
|
990
|
THEN TL.TLFullName
|
|
991
|
ELSE NXL.NGUOI_XU_LY_NAME
|
|
992
|
END AS NGUOIXULY, -- người xử lý tiếp theo
|
|
993
|
--RP.APPROVE_DT AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
994
|
CASE
|
|
995
|
WHEN RPN.PROCESS_ID = 'APPROVE' THEN RP.APPROVE_DT
|
|
996
|
ELSE NULL
|
|
997
|
END
|
|
998
|
AS PROCESS_APP_DT, --ngày duyệt hoàn tất
|
|
999
|
ISNULL(RPC.TYPE_JOB, 'KS') AS TYPE_JOB, RPC.TLNAME AS USER_JOB, TU.TLFullName AS USER_JOB_NAME,
|
|
1000
|
TFU.TLFullName AS TRANSFER_MAKER_NAME, RPC.TLNAME AS TRANSFER_MAKER, RPC.TRANFER_DT,
|
|
1001
|
PLRP.ID AS REF_ID
|
|
1002
|
, ROW_NUMBER() OVER (ORDER BY A.CREATE_DT DESC
|
|
1003
|
) AS __ROWNUM-- SELECT END
|
|
1004
|
|
|
1005
|
FROM TR_RATE_SUPPLIER_MASTER A
|
|
1006
|
LEFT JOIN CM_SUPPLIER B ON A.SUP_ID = B.SUP_ID
|
|
1007
|
LEFT JOIN CM_HANGHOA C ON A.HH_ID = C.HH_ID
|
|
1008
|
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
|
|
1009
|
LEFT JOIN CM_ALLCODE E ON A.DECISION = E.CDVAL AND E.CDTYPE = 'RATE_SUP' AND E.CDNAME = 'DECISION_SUP'
|
|
1010
|
--LEFT JOIN CM_ALLCODE PS ON A.PROCESS_STATUS = PS.CDVAL AND PS.CDTYPE = 'RATE_SUP' AND PS.CDNAME = 'PROCESS_STATUS'
|
|
1011
|
LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME
|
|
1012
|
LEFT JOIN TL_USER MK ON A.MAKER_ID = MK.TLNANME
|
|
1013
|
LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.RATE_ID AND PLRP.STATUS <> 'U' AND PLRP.PROCESS_ID = 'DMMS'
|
|
1014
|
LEFT JOIN
|
|
1015
|
(
|
|
1016
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
1017
|
dbo.PL_REQUEST_PROCESS
|
|
1018
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
1019
|
) RPN ON RPN.REQ_ID=A.RATE_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
1020
|
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.RATE_ID
|
|
1021
|
AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)
|
|
1022
|
LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.RATE_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
|
|
1023
|
LEFT JOIN TL_USER TFU ON TFU.TLNANME = RPC.MAKER_ID
|
|
1024
|
LEFT JOIN TL_USER TU ON TU.TLNANME = RPC.TLNAME
|
|
1025
|
LEFT JOIN(
|
|
1026
|
SELECT RESULT.REQ_ID,
|
|
1027
|
STUFF(
|
|
1028
|
(SELECT ', ' + NPU.TLNAME
|
|
1029
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
1030
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
1031
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')NGUOI_XU_LY,
|
|
1032
|
STUFF(
|
|
1033
|
(SELECT ', ' + NPU.TL_FULLNAME
|
|
1034
|
FROM @t_NEXT_PROCESS_USER NPU
|
|
1035
|
WHERE NPU.REQ_ID = RESULT.REQ_ID
|
|
1036
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') NGUOI_XU_LY_NAME
|
|
1037
|
FROM @t_NEXT_PROCESS_USER RESULT
|
|
1038
|
GROUP BY REQ_ID
|
|
1039
|
) NXL ON NXL.REQ_ID = A.RATE_ID
|
|
1040
|
|
|
1041
|
|
|
1042
|
WHERE 1=1
|
|
1043
|
AND (A.RATE_ID LIKE '%' + @p_RATE_ID + '%' OR @p_RATE_ID IS NULL OR @p_RATE_ID = '')
|
|
1044
|
AND (A.RATE_REQ_NO LIKE '%' + @p_RATE_REQ_NO + '%' OR @p_RATE_REQ_NO IS NULL OR @p_RATE_REQ_NO = '')
|
|
1045
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
1046
|
AND (DATEDIFF(DAY, A.RATE_FROM_DT, CONVERT(DATETIME, @p_RATE_FROM_DT,103)) = 0 OR @p_RATE_FROM_DT IS NULL OR @p_RATE_FROM_DT = '')
|
|
1047
|
AND (DATEDIFF(DAY, A.RATE_TO_DT, CONVERT(DATETIME, @p_RATE_TO_DT,103)) = 0 OR @p_RATE_TO_DT IS NULL OR @p_RATE_TO_DT = '')
|
|
1048
|
AND (A.CREATE_DT >= CONVERT(DATETIME, @p_FROM_DT, 103) OR @p_FROM_DT IS NULL OR @p_FROM_DT = '')
|
|
1049
|
AND (A.CREATE_DT <= CONVERT(DATETIME, @p_TO_DT, 103) OR @p_TO_DT IS NULL OR @p_TO_DT = '')
|
|
1050
|
AND (A.HH_ID LIKE '%' + @p_HH_ID + '%' OR @p_HH_ID IS NULL OR @p_HH_ID = '')
|
|
1051
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
1052
|
AND (
|
|
1053
|
A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%'
|
|
1054
|
OR (@p_AUTH_STATUS = 'U' AND A.PROCESS_STATUS <> 'APPROVE' AND PLRP.STATUS = 'C')
|
|
1055
|
OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = ''
|
|
1056
|
)
|
|
1057
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
1058
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
1059
|
AND (DATEDIFF(DAY, A.APPROVE_DT, CONVERT(DATETIME, @p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
1060
|
AND (
|
|
1061
|
A.PROCESS_STATUS = @p_PROCESS_STATUS
|
|
1062
|
OR(@p_PROCESS_STATUS = 'DMMS_XL'
|
|
1063
|
AND (
|
|
1064
|
(RPC.TYPE_JOB = 'XL' OR RPC.TYPE_JOB = 'KS')
|
|
1065
|
AND RPC.STATUS_JOB = 'C'
|
|
1066
|
--OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS')
|
|
1067
|
)
|
|
1068
|
)
|
|
1069
|
OR(@p_PROCESS_STATUS = 'DMMS_APP'
|
|
1070
|
AND (
|
|
1071
|
RPC.TYPE_JOB = 'TP'
|
|
1072
|
AND RPC.STATUS_JOB = 'C'
|
|
1073
|
OR(RPN.STATUS = 'C' AND RPN.PROCESS_ID = 'DMMS' AND NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.RATE_ID AND TYPE_JOB = 'TP' AND STATUS_JOB = 'C'))
|
|
1074
|
)
|
|
1075
|
)
|
|
1076
|
OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS = ''
|
|
1077
|
)
|
|
1078
|
AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
|
|
1079
|
AND ((@p_BRANCH_TYPE = 'HS' AND A.DEP_ID = @p_DEP_ID)
|
|
1080
|
OR @p_BRANCH_TYPE <> 'HS'
|
|
1081
|
OR @p_DEP_ID IS NULL OR @p_DEP_ID = ''
|
|
1082
|
)
|
|
1083
|
--AND (
|
|
1084
|
-- A.MAKER_ID = @p_USER_LOGIN
|
|
1085
|
-- OR (A.SIGN_USER = @p_USER_LOGIN AND A.AUTH_STATUS <> 'E' AND A.PROCESS_STATUS <> 'NEW')
|
|
1086
|
-- OR (@p_USER_LOGIN IN (SELECT TLNAME FROM @t_NEXT_PROCESS_USER WHERE REQ_ID = A.RATE_ID))
|
|
1087
|
-- OR (@p_USER_LOGIN IN (SELECT CHECKER_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND STATUS = 'P'))
|
|
1088
|
--)
|
|
1089
|
--AND(
|
|
1090
|
-- (RPC.TYPE_JOB = 'KS' OR RPC.TYPE_JOB = 'TP')
|
|
1091
|
-- AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS_CHILD TMP WHERE TMP.REQ_ID = A.RATE_ID AND TMP.STATUS_JOB = 'P' AND TMP.TYPE_JOB = 'XL')
|
|
1092
|
-- OR(RPC.TYPE_JOB = 'XL')
|
|
1093
|
--)
|
|
1094
|
AND (
|
|
1095
|
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_STATUS='DMMS' OR A.PROCESS_STATUS <> 'DMMS'))
|
|
1096
|
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_STATUS='DMMS')
|
|
1097
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_USER_LOGIN AND PLRP.PROCESS_ID = 'DMMS')
|
|
1098
|
)
|
|
1099
|
AND( NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%' OR @p_NGUOIXULY IS NULL OR @p_NGUOIXULY = ''
|
|
1100
|
OR NXL.NGUOI_XU_LY LIKE '%' + @p_NGUOIXULY + '%')
|
|
1101
|
-----------BAODNQ 2/12/2022 : Chỉ tìm thấy phiếu ở màn hình DMMS khi đã có điều phối cho NVXL / khi KSV đã phê duyệt(TH ko điều phối)--------
|
|
1102
|
AND(
|
|
1103
|
EXISTS(
|
|
1104
|
SELECT ID FROM PL_REQUEST_PROCESS_CHILD
|
|
1105
|
WHERE REQ_ID = A.RATE_ID
|
|
1106
|
AND PROCESS_ID = (SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.RATE_ID AND PROCESS_ID = 'DMMS')
|
|
1107
|
AND (TYPE_JOB = 'XL' OR (TYPE_JOB = 'KS' AND STATUS_JOB = 'P'))
|
|
1108
|
)
|
|
1109
|
)
|
|
1110
|
|
|
1111
|
|
|
1112
|
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
|
|
1113
|
END-- PAGING END
|
|
1114
|
END
|
|
1115
|
|
|
1116
|
|
|
1117
|
END
|
|
1118
|
|