|
1
|
ALTER PROCEDURE dbo.TR_CHECK_ROLE_APPROVE
|
|
2
|
@TYPE VARCHAR(15) = NULL,
|
|
3
|
@p_REQ_ID varchar(15) = NULL,
|
|
4
|
@p_USER_LOGIN varchar(15) = NULL
|
|
5
|
AS
|
|
6
|
BEGIN TRANSACTION
|
|
7
|
IF(@TYPE <> 'ADV_PAY')
|
|
8
|
BEGIN
|
|
9
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN AND TYPE_JOB IN ('TP','KS')))
|
|
10
|
BEGIN
|
|
11
|
ROLLBACK TRANSACTION
|
|
12
|
SELECT '0' as Result, '' ErrorDesc
|
|
13
|
RETURN '0'
|
|
14
|
END
|
|
15
|
END
|
|
16
|
-- KHAI BAO THEM ROLE NHAN UY QUYEN
|
|
17
|
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))
|
|
18
|
DECLARE @TABLE_ROLE2 TABLE (ROLE_AUTH VARCHAR(50))
|
|
19
|
DECLARE @TABLE_DEP TABLE (ROLE_NEW VARCHAR(50), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))
|
|
20
|
DECLARE @ROLE_ID VARCHAR(50)
|
|
21
|
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
|
|
22
|
INSERT INTO @TABLE_ROLE SELECT @ROLE_ID
|
|
23
|
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW
|
|
24
|
FROM TL_SYS_ROLE_MAPPING
|
|
25
|
WHERE ROLE_OLD =@ROLE_ID
|
|
26
|
AND TLNAME =@p_USER_LOGIN
|
|
27
|
AND ( CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103)
|
|
28
|
AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
|
|
29
|
OR (EFF_DATE IS NULL OR EXP_DATE IS NULL)
|
|
30
|
)
|
|
31
|
INSERT INTO @TABLE_ROLE2 SELECT @ROLE_ID
|
|
32
|
INSERT INTO @TABLE_ROLE2 SELECT ROLE_NEW
|
|
33
|
FROM TL_SYS_ROLE_MAPPING
|
|
34
|
WHERE TLNAME =@p_USER_LOGIN
|
|
35
|
AND ( CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103)
|
|
36
|
AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
|
|
37
|
OR (EFF_DATE IS NULL OR EXP_DATE IS NULL)
|
|
38
|
)
|
|
39
|
INSERT INTO @TABLE_DEP SELECT ROLE_NEW, BRANCH_ID, DEP_ID
|
|
40
|
FROM TL_SYS_ROLE_MAPPING
|
|
41
|
WHERE TLNAME = @p_USER_LOGIN
|
|
42
|
AND ( CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103)
|
|
43
|
AND CONVERT(DATE, GETDATE(),103) <= CONVERT(DATE,EXP_DATE, 103)
|
|
44
|
OR (EFF_DATE IS NULL OR EXP_DATE IS NULL)
|
|
45
|
)
|
|
46
|
DECLARE @AUTHOR TABLE
|
|
47
|
(
|
|
48
|
ROLE_ID VARCHAR(20),
|
|
49
|
BRANCH_ID VARCHAR(20),
|
|
50
|
DEP_ID VARCHAR(20),
|
|
51
|
BRANCH_TYPE VARCHAR(20)
|
|
52
|
)
|
|
53
|
DECLARE @AUTHOR_DVDM TABLE
|
|
54
|
(
|
|
55
|
ROLE_ID VARCHAR(20),
|
|
56
|
BRANCH_ID VARCHAR(20),
|
|
57
|
DEP_ID VARCHAR(20),
|
|
58
|
DVDM_ID VARCHAR(20)
|
|
59
|
)
|
|
60
|
DECLARE @AUTHOR_DMMS TABLE
|
|
61
|
(
|
|
62
|
ROLE_ID VARCHAR(20),
|
|
63
|
BRANCH_ID VARCHAR(20),
|
|
64
|
DEP_ID VARCHAR(20),
|
|
65
|
DMMS_ID VARCHAR(20)
|
|
66
|
)
|
|
67
|
INSERT INTO @AUTHOR
|
|
68
|
(
|
|
69
|
ROLE_ID,
|
|
70
|
BRANCH_ID,
|
|
71
|
DEP_ID,
|
|
72
|
BRANCH_TYPE
|
|
73
|
)
|
|
74
|
SELECT RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
|
|
75
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
76
|
WHERE TLNANME=@p_USER_LOGIN
|
|
77
|
UNION ALL
|
|
78
|
SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_USER TU
|
|
79
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
80
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
81
|
WHERE TLNANME=@p_USER_LOGIN
|
|
82
|
UNION ALL
|
|
83
|
SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
84
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
85
|
WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
86
|
UNION ALL
|
|
87
|
SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,CB.BRANCH_TYPE FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
88
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
89
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
90
|
WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
91
|
INSERT INTO @AUTHOR_DVDM
|
|
92
|
(
|
|
93
|
ROLE_ID,
|
|
94
|
BRANCH_ID,
|
|
95
|
DEP_ID,
|
|
96
|
DVDM_ID
|
|
97
|
)
|
|
98
|
SELECT TU.RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
99
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
100
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
101
|
WHERE TU.TLNANME=@p_USER_LOGIN
|
|
102
|
UNION ALL
|
|
103
|
SELECT TM.ROLE_NEW RoleName,TU.TLSUBBRID,TU.SECUR_CODE,PC.DVDM_ID FROM dbo.TL_USER TU
|
|
104
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.TLSUBBRID AND DT.DEP_ID=TU.SECUR_CODE
|
|
105
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
106
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
107
|
WHERE TU.TLNANME=@p_USER_LOGIN
|
|
108
|
UNION ALL
|
|
109
|
SELECT TU.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
110
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
111
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
112
|
WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
113
|
UNION ALL
|
|
114
|
SELECT TM.ROLE_NEW RoleName,TU.BRANCH_ID TLSUBBRID,TU.DEP_ID SECUR_CODE,PC.DVDM_ID FROM dbo.TL_SYS_ROLE_MAPPING TU
|
|
115
|
LEFT JOIN dbo.PL_COSTCENTER_DT DT ON DT.BRANCH_ID=TU.BRANCH_ID AND DT.DEP_ID=TU.DEP_ID
|
|
116
|
LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID
|
|
117
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
118
|
WHERE TU.TLNAME=@p_USER_LOGIN AND CAST(TU.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
119
|
INSERT INTO @AUTHOR_DMMS
|
|
120
|
(
|
|
121
|
ROLE_ID,
|
|
122
|
BRANCH_ID,
|
|
123
|
DEP_ID,
|
|
124
|
DMMS_ID
|
|
125
|
)
|
|
126
|
SELECT RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
127
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
128
|
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')
|
|
129
|
WHERE TLNANME=@p_USER_LOGIN
|
|
130
|
UNION ALL
|
|
131
|
SELECT TM.ROLE_NEW RoleName,TLSUBBRID,SECUR_CODE,MS.DMMS_ID FROM dbo.TL_USER TU
|
|
132
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TU.RoleName AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
133
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
|
|
134
|
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')
|
|
135
|
WHERE TLNANME=@p_USER_LOGIN
|
|
136
|
UNION ALL
|
|
137
|
SELECT TM1.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
138
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
139
|
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')
|
|
140
|
WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
141
|
UNION ALL
|
|
142
|
SELECT TM.ROLE_NEW RoleName,TM1.BRANCH_ID TLSUBBRID,TM1.DEP_ID SECUR_CODE,MS.DMMS_ID FROM dbo.TL_SYS_ROLE_MAPPING TM1
|
|
143
|
LEFT JOIN dbo.TL_SYS_ROLE_MAPPING TM ON TM.ROLE_OLD=TM1.ROLE_NEW AND( TM.TLNAME IS NULL OR TM.TLNAME='')
|
|
144
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TM1.BRANCH_ID
|
|
145
|
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')
|
|
146
|
WHERE TM1.TLNAME=@p_USER_LOGIN AND CAST(TM1.EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE)
|
|
147
|
IF(@TYPE='ADV_PAY')
|
|
148
|
BEGIN
|
|
149
|
IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_ID))
|
|
150
|
BEGIN
|
|
151
|
ROLLBACK TRANSACTION
|
|
152
|
SELECT '-1' as Result, N'Phiếu đã duyệt xong. Vui lòng chờ các giao dịch tiếp theo để thực hiện phê duyệt' ErrorDesc
|
|
153
|
RETURN '-1'
|
|
154
|
END
|
|
155
|
-- NEU LA TPHC O HOI SO
|
|
156
|
IF( EXISTS(SELECT * FROM TL_USER WHERE TLNANME = @p_USER_LOGIN AND TLSUBBRID = 'DV0001' AND DEP_ID = 'DEP000000000014' AND RoleName = 'GDDV'))
|
|
157
|
--OR EXISTS(SELECT * FROM @TABLE_DEP WHERE ROLE_NEW = 'GDDV' AND BRANCH_ID = 'DV0001' AND DEP_ID = 'DEP000000000014')
|
|
158
|
BEGIN
|
|
159
|
IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND PROCESS <> '' AND PROCESS IS NOT NULL))
|
|
160
|
BEGIN
|
|
161
|
ROLLBACK TRANSACTION
|
|
162
|
SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
|
|
163
|
RETURN '-1'
|
|
164
|
END
|
|
165
|
END
|
|
166
|
--doanptt: THU KY DA DIEU PHOI CHO NHAN VIEN XU LY THI KHONG DUOC DUYET NUA
|
|
167
|
ELSE IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB <> 'C' AND TLNAME =@p_USER_LOGIN AND TYPE_JOB IN ('KS')))
|
|
168
|
BEGIN
|
|
169
|
ROLLBACK TRANSACTION
|
|
170
|
SELECT '-1' as Result, N'Phiếu đang được điều phối đến nhân viên xử lý' ErrorDesc
|
|
171
|
RETURN '-1'
|
|
172
|
END
|
|
173
|
ELSE IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS,'') = ''))
|
|
174
|
BEGIN
|
|
175
|
PRINT '1'
|
|
176
|
END
|
|
177
|
ELSE IF ( ( EXISTS(SELECT * FROM TL_USER WHERE TLNANME = @p_USER_LOGIN AND TLSUBBRID = 'DV0001' AND RoleName = 'GDDV')
|
|
178
|
OR EXISTS(SELECT * FROM @TABLE_DEP WHERE ROLE_NEW = 'GDDV' AND BRANCH_ID = 'DV0001')
|
|
179
|
)
|
|
180
|
OR EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS,'') = '')
|
|
181
|
)
|
|
182
|
BEGIN
|
|
183
|
ROLLBACK TRANSACTION
|
|
184
|
SELECT '0' as Result, '' ErrorDesc
|
|
185
|
RETURN '0'
|
|
186
|
END
|
|
187
|
-- CAC TRUONG HOP CON LAI
|
|
188
|
ELSE
|
|
189
|
BEGIN
|
|
190
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID) )
|
|
191
|
BEGIN
|
|
192
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND ROLE_USER IN (SELECT * FROM @TABLE_ROLE)))
|
|
193
|
BEGIN
|
|
194
|
ROLLBACK TRANSACTION
|
|
195
|
SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
|
|
196
|
RETURN '-1'
|
|
197
|
END
|
|
198
|
END
|
|
199
|
END
|
|
200
|
|
|
201
|
--IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND BRANCH_CREATE ='DV0001')
|
|
202
|
--BEGIN
|
|
203
|
-- IF(EXISTS(SELECT * FROM PL_PROCESS WHERE CHEC))
|
|
204
|
--END
|
|
205
|
--ELSE
|
|
206
|
--BEGIN
|
|
207
|
-- ROLLBACK TRANSACTION
|
|
208
|
-- SELECT '-1' as Result, '' ErrorDesc
|
|
209
|
-- RETURN '-1'
|
|
210
|
--END
|
|
211
|
END
|
|
212
|
ELSE IF(@TYPE='TR_REQ_PAYMENT')
|
|
213
|
BEGIN
|
|
214
|
IF((SELECT COUNT(*) FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '' AND AUTH_STATUS <> 'A') > 0)
|
|
215
|
BEGIN
|
|
216
|
ROLLBACK TRANSACTION
|
|
217
|
SELECT '0' as Result, N'' ErrorDesc
|
|
218
|
RETURN '0'
|
|
219
|
END
|
|
220
|
IF((SELECT COUNT(*) FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_ID AND TRASFER_USER_RECIVE = @p_USER_LOGIN AND ISNULL(PROCESS, '') = '0' AND AUTH_STATUS <> 'A') > 0)
|
|
221
|
BEGIN
|
|
222
|
ROLLBACK TRANSACTION
|
|
223
|
SELECT '-1' as Result, N'' ErrorDesc
|
|
224
|
RETURN '-1'
|
|
225
|
END
|
|
226
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_ID))
|
|
227
|
BEGIN
|
|
228
|
ROLLBACK TRANSACTION
|
|
229
|
SELECT '-1' as Result, N'Phiếu đã duyệt xong. Vui lòng chờ các giao dịch tiếp theo để thực hiện phê duyệt' ErrorDesc
|
|
230
|
RETURN '-1'
|
|
231
|
END
|
|
232
|
IF ( ( SELECT COUNT(*)
|
|
233
|
FROM @TABLE_ROLE2 A
|
|
234
|
WHERE A.ROLE_AUTH IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
|
|
235
|
) = 0
|
|
236
|
)
|
|
237
|
BEGIN
|
|
238
|
ROLLBACK TRANSACTION
|
|
239
|
SELECT '-1' as Result, N'Bạn không có quyền phê duyệt' ErrorDesc
|
|
240
|
RETURN '-1'
|
|
241
|
END
|
|
242
|
END
|
|
243
|
ELSE IF (@TYPE ='CORE')
|
|
244
|
BEGIN
|
|
245
|
IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT ='A') OR EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT ='A'))
|
|
246
|
BEGIN
|
|
247
|
IF(EXISTS(SELECT * FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REQ_ID AND ISNULL(REF_NO,'') <> ''))
|
|
248
|
BEGIN
|
|
249
|
ROLLBACK TRANSACTION
|
|
250
|
SELECT '6' as Result, N'Giao dịch được phê duyệt thành công trên AMS và hạch toán thành công vào hệ thống Core FC' ErrorDesc
|
|
251
|
RETURN '6'
|
|
252
|
END
|
|
253
|
ELSE
|
|
254
|
BEGIN
|
|
255
|
ROLLBACK TRANSACTION
|
|
256
|
SELECT '5' as Result, N'Giao dịch được phê duyệt thành công nhưng bắn vào Core FC thất bại. Vui lòng rà soát lại nội dung hạch toán (không được phép chứa kí tự & hoặc kí tự <)' ErrorDesc
|
|
257
|
RETURN '5'
|
|
258
|
END
|
|
259
|
END
|
|
260
|
ELSE
|
|
261
|
BEGIN
|
|
262
|
IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT <> 'A') AND EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_ID AND AUTH_STATUS_KT <>'A'))
|
|
263
|
BEGIN
|
|
264
|
ROLLBACK TRANSACTION
|
|
265
|
SELECT '7' as Result, '' ErrorDesc
|
|
266
|
RETURN '7'
|
|
267
|
END
|
|
268
|
END
|
|
269
|
END
|
|
270
|
ELSE IF(@TYPE ='CON_LAYOUT')
|
|
271
|
BEGIN
|
|
272
|
-- NEU CAP DUYET LA VAN PHONG THU KI TGD THI CO THE DUYET LUON
|
|
273
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKTGD' AND
|
|
274
|
(@ROLE_ID ='TKTGD' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKTGD'))))
|
|
275
|
BEGIN
|
|
276
|
ROLLBACK TRANSACTION
|
|
277
|
SELECT '0' as Result, '' ErrorDesc
|
|
278
|
RETURN '0'
|
|
279
|
END
|
|
280
|
-- NEU CAP DUYET LA VAN PHONG THU KI HDQT THI CO THE DUYET LUON
|
|
281
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKHDQT' AND (@ROLE_ID ='TKHDQT' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKHDQT'))))
|
|
282
|
BEGIN
|
|
283
|
ROLLBACK TRANSACTION
|
|
284
|
SELECT '0' as Result, '' ErrorDesc
|
|
285
|
RETURN '0'
|
|
286
|
END
|
|
287
|
IF(EXISTS(SELECT * FROM CON_LAYOUT_BLUEPRINT WHERE CON_LAYOUT_BLUEPRINT_ID = @p_REQ_ID AND PROCESS_ID ='APPROVE'))
|
|
288
|
BEGIN
|
|
289
|
ROLLBACK TRANSACTION
|
|
290
|
SELECT '-1' as Result, '' ErrorDesc
|
|
291
|
RETURN '-1'
|
|
292
|
END
|
|
293
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS PR WHERE PR.REQ_ID =@p_REQ_ID AND STATUS ='C' AND (
|
|
294
|
EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND AUTH.BRANCH_ID=PR.BRANCH_ID AND (AUTH.DEP_ID=PR.DEP_ID OR AUTH.BRANCH_TYPE<>'HS'))
|
|
295
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND PR.DVDM_ID=AUTH.DVDM_ID)
|
|
296
|
OR ( (PR.BRANCH_ID IS NULL OR PR.BRANCH_ID='') AND (PR.DVDM_ID ='' OR PR.DVDM_ID IS NULL) )
|
|
297
|
)))
|
|
298
|
BEGIN
|
|
299
|
print 'haha'
|
|
300
|
ROLLBACK TRANSACTION
|
|
301
|
SELECT '-1' as Result, '' ErrorDesc
|
|
302
|
RETURN '-1'
|
|
303
|
END
|
|
304
|
END
|
|
305
|
ELSE
|
|
306
|
BEGIN
|
|
307
|
-- NEU CAP DUYET LA VAN PHONG THU KI TGD THI CO THE DUYET LUON
|
|
308
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKTGD' AND
|
|
309
|
(@ROLE_ID ='TKTGD' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKTGD'))))
|
|
310
|
BEGIN
|
|
311
|
ROLLBACK TRANSACTION
|
|
312
|
SELECT '0' as Result, '' ErrorDesc
|
|
313
|
RETURN '0'
|
|
314
|
END
|
|
315
|
-- NEU CAP DUYET LA VAN PHONG THU KI HDQT THI CO THE DUYET LUON
|
|
316
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='TKHDQT' AND
|
|
317
|
(@ROLE_ID ='TKHDQT' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='TKHDQT'))))
|
|
318
|
BEGIN
|
|
319
|
ROLLBACK TRANSACTION
|
|
320
|
SELECT '0' as Result, '' ErrorDesc
|
|
321
|
RETURN '0'
|
|
322
|
END
|
|
323
|
-- NEU CAP DUYET LA DVCM THI CO THE DUYET LUON
|
|
324
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS A WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='DVCM' AND
|
|
325
|
EXISTS(SELECT * FROM @AUTHOR_DVDM B WHERE B.ROLE_ID = 'GDDV' AND B.DVDM_ID = A.DVDM_ID) AND
|
|
326
|
(@ROLE_ID ='GDDV' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='GDDV'))))
|
|
327
|
BEGIN
|
|
328
|
ROLLBACK TRANSACTION
|
|
329
|
SELECT '0' as Result, '' ErrorDesc
|
|
330
|
RETURN '0'
|
|
331
|
END
|
|
332
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='C' AND PROCESS_ID ='DVDC' AND
|
|
333
|
(@ROLE_ID ='GDDV' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='GDDV'))))
|
|
334
|
BEGIN
|
|
335
|
ROLLBACK TRANSACTION
|
|
336
|
SELECT '0' as Result, '' ErrorDesc
|
|
337
|
RETURN '0'
|
|
338
|
END
|
|
339
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN))
|
|
340
|
BEGIN
|
|
341
|
ROLLBACK TRANSACTION
|
|
342
|
SELECT '0' as Result, '' ErrorDesc
|
|
343
|
RETURN '0'
|
|
344
|
END
|
|
345
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS PR WHERE PR.REQ_ID =@p_REQ_ID AND STATUS ='C' AND (
|
|
346
|
EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND AUTH.BRANCH_ID=PR.BRANCH_ID AND (AUTH.DEP_ID=PR.DEP_ID OR AUTH.BRANCH_TYPE<>'HS'))
|
|
347
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND PR.DVDM_ID=AUTH.DVDM_ID)
|
|
348
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DMMS AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND EXISTS (SELECT * FROM dbo.TR_REQUEST_DOC A WHERE A.REQ_ID=@p_REQ_ID AND A.DMMS_ID=AUTH.DMMS_ID AND A.PROCESS_ID='DMMS'))
|
|
349
|
OR EXISTS(SELECT AUTH.ROLE_ID FROM @AUTHOR_DVDM AUTH WHERE AUTH.ROLE_ID=PR.ROLE_USER AND EXISTS (SELECT * FROM dbo.TR_REQUEST_DOC A WHERE A.REQ_ID=@p_REQ_ID AND A.DMMS_ID=AUTH.DVDM_ID AND A.PROCESS_ID='DMMS'))
|
|
350
|
OR EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PR.ID AND TLNAME=@p_USER_LOGIN AND TYPE_JOB='KS' AND STATUS_JOB='C')
|
|
351
|
OR ((PR.BRANCH_ID IS NULL OR PR.BRANCH_ID='') AND (PR.DVDM_ID ='' OR PR.DVDM_ID IS NULL) AND EXISTS(SELECT * FROM @AUTHOR AUTH WHERE AUTH.ROLE_ID = PR.ROLE_USER))
|
|
352
|
)))
|
|
353
|
BEGIN
|
|
354
|
ROLLBACK TRANSACTION
|
|
355
|
SELECT '-1' as Result, '' ErrorDesc
|
|
356
|
RETURN '-1'
|
|
357
|
END
|
|
358
|
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='APPROVE'))
|
|
359
|
BEGIN
|
|
360
|
ROLLBACK TRANSACTION
|
|
361
|
SELECT '-1' as Result, '' ErrorDesc
|
|
362
|
RETURN '-1'
|
|
363
|
END
|
|
364
|
IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND PROCESS_ID ='APPROVE'))
|
|
365
|
BEGIN
|
|
366
|
ROLLBACK TRANSACTION
|
|
367
|
SELECT '-1' as Result, '' ErrorDesc
|
|
368
|
RETURN '-1'
|
|
369
|
END
|
|
370
|
-- GiaNT
|
|
371
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='P' AND PROCESS_ID ='KT' AND
|
|
372
|
(@ROLE_ID ='KSV' OR EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH ='KSV'))))
|
|
373
|
BEGIN
|
|
374
|
ROLLBACK TRANSACTION
|
|
375
|
SELECT '-1' as Result, '' ErrorDesc
|
|
376
|
RETURN '-1'
|
|
377
|
END
|
|
378
|
END
|
|
379
|
COMMIT TRANSACTION
|
|
380
|
print 'haha'
|
|
381
|
SELECT '0' as Result, '' ErrorDesc
|
|
382
|
RETURN '0'
|
|
383
|
ABORT:
|
|
384
|
BEGIN
|
|
385
|
ROLLBACK TRANSACTION
|
|
386
|
SELECT '-1' as Result, '' ErrorDesc
|
|
387
|
RETURN '-1'
|
|
388
|
End
|