|
1
|
|
|
2
|
|
|
3
|
/*INSERT INTO SYS_PREFIX(ID,Prefix,[Description]) VALUES('CM_REJECT_LOG','CMJL','')
|
|
4
|
INSERT INTO SYS_CODEMASTERS(Prefix,CurValue,Active) VALUES('CMJL','1','1')*/
|
|
5
|
ALTER PROC [dbo].[CM_REJECT_LOG_ByType_Ins]
|
|
6
|
@p_LOG_ID varchar(15) = NULL,
|
|
7
|
@p_STAGE varchar(5) = NULL,
|
|
8
|
@p_TRN_ID varchar(15) = NULL,
|
|
9
|
@p_TRN_TYPE nvarchar(100) = NULL,
|
|
10
|
@p_LOG_DT VARCHAR = NULL,
|
|
11
|
@p_AUTH_STAT varchar(3) = NULL,
|
|
12
|
@p_REASON nvarchar(500) = NULL,
|
|
13
|
@p_IS_LATEST varchar(1) = NULL,
|
|
14
|
@p_REJECTED_BY varchar(20) = NULL,
|
|
15
|
@p_REJECTED_DT VARCHAR = NULL,
|
|
16
|
@p_TYPE VARCHAR(20) =NULL,
|
|
17
|
@p_PROCESS_ID VARCHAR(20),
|
|
18
|
@p_REF_ID INT=NULL
|
|
19
|
AS
|
|
20
|
BEGIN TRANSACTION
|
|
21
|
DECLARE @PARENT_PROCESS_ID VARCHAR(20),@ROLE_ID VARCHAR(20),@ROLE_DES NVARCHAR(200),@ROLE_PARENT_ID VARCHAR(20),@ROLE_DES_PARENT NVARCHAR(200),@COMPLETE BIT
|
|
22
|
|
|
23
|
SET @COMPLETE=0
|
|
24
|
DECLARE @p_MESSAGE NVARCHAR(100) = NULL
|
|
25
|
IF(EXISTS (SELECT * FROM CM_REJECT_LOG WHERE TRN_ID = @p_TRN_ID))
|
|
26
|
BEGIN
|
|
27
|
UPDATE CM_REJECT_LOG SET IS_LATEST ='N' WHERE TRN_ID =@p_TRN_ID
|
|
28
|
END
|
|
29
|
|
|
30
|
-- END PHIEU DE THI THANH TOAN 19-11-2019
|
|
31
|
|
|
32
|
--- PHIEU YEU CAU MUA SAM
|
|
33
|
IF(@p_TRN_TYPE='PL_REQUEST_DOC')
|
|
34
|
BEGIN IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID='APPROVE'))
|
|
35
|
BEGIN
|
|
36
|
SET @p_MESSAGE =N'Trả về thất bại! Tờ trình chủ trương đã được duyệt'
|
|
37
|
ROLLBACK TRANSACTION
|
|
38
|
SELECT '0' as Result, @p_LOG_ID LOG_ID ,@p_MESSAGE ErrorDesc
|
|
39
|
RETURN '0'
|
|
40
|
END
|
|
41
|
ELSE
|
|
42
|
BEGIN
|
|
43
|
IF(@p_TYPE='CREATE')
|
|
44
|
BEGIN
|
|
45
|
UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID='REJECT',AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID
|
|
46
|
UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=N'Tờ trình bị trả về' WHERE PROCESS_ID='APPNEW' AND REQ_ID=@p_TRN_ID
|
|
47
|
UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID <>'APPNEW' AND REQ_ID=@p_TRN_ID
|
|
48
|
INSERT INTO dbo.PL_PROCESS
|
|
49
|
(
|
|
50
|
REQ_ID,
|
|
51
|
PROCESS_ID,
|
|
52
|
CHECKER_ID,
|
|
53
|
APPROVE_DT,
|
|
54
|
PROCESS_DESC,
|
|
55
|
NOTES
|
|
56
|
)
|
|
57
|
VALUES
|
|
58
|
( @p_TRN_ID, -- REQ_ID - varchar(15)
|
|
59
|
'REJECT', -- PROCESS_ID - varchar(10)
|
|
60
|
@p_REJECTED_BY, -- CHECKER_ID - varchar(15)
|
|
61
|
GETDATE(), -- APPROVE_DT - datetime
|
|
62
|
@p_REASON,
|
|
63
|
N'Trả về người tạo'
|
|
64
|
-- PROCESS_DESC - nvarchar(1000)
|
|
65
|
)
|
|
66
|
SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
|
|
67
|
SET @COMPLETE=1
|
|
68
|
END
|
|
69
|
ELSE IF(@p_TYPE='NVXL')
|
|
70
|
BEGIN
|
|
71
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID
|
|
72
|
--UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'
|
|
73
|
--LUCTV 28 09 2020 DIEU CHINH LOI TRA VE NHUNG NHAN VIEN XU LY KHONG DUOC PHEP TRA VE TIEP
|
|
74
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'
|
|
75
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'))
|
|
76
|
BEGIN
|
|
77
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB ='KS'
|
|
78
|
END
|
|
79
|
INSERT INTO dbo.PL_PROCESS
|
|
80
|
(
|
|
81
|
REQ_ID,
|
|
82
|
PROCESS_ID,
|
|
83
|
CHECKER_ID,
|
|
84
|
APPROVE_DT,
|
|
85
|
PROCESS_DESC,
|
|
86
|
NOTES
|
|
87
|
)
|
|
88
|
VALUES
|
|
89
|
( @p_TRN_ID, -- REQ_ID - varchar(15)
|
|
90
|
'REJECT', -- PROCESS_ID - varchar(10)
|
|
91
|
@p_REJECTED_BY, -- CHECKER_ID - varchar(15)
|
|
92
|
GETDATE(), -- APPROVE_DT - datetime
|
|
93
|
@p_REASON,
|
|
94
|
N'Trả về nhân viên xử lý'
|
|
95
|
-- PROCESS_DESC - nvarchar(1000)
|
|
96
|
)
|
|
97
|
SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
|
|
98
|
SET @COMPLETE=1
|
|
99
|
END
|
|
100
|
ELSE IF(@p_TYPE='PRE_APP')
|
|
101
|
BEGIN
|
|
102
|
--SET @PARENT_PROCESS_ID =(SELECT TOP 1 PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
|
|
103
|
--SET @ROLE_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
|
|
104
|
--SET @ROLE_PARENT_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID)
|
|
105
|
--SET @ROLE_DES=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)
|
|
106
|
--SET @ROLE_DES_PARENT=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_PARENT_ID)
|
|
107
|
|
|
108
|
--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=@ROLE_DES + N' đã trả về, chờ ' + @ROLE_DES_PARENT +N' xử lý' WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID
|
|
109
|
|
|
110
|
--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID
|
|
111
|
--UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PARENT_PROCESS_ID WHERE REQ_ID=@p_TRN_ID
|
|
112
|
--IF(@PARENT_PROCESS_ID='APPNEW')
|
|
113
|
-- UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U' WHERE REQ_ID=@p_TRN_ID
|
|
114
|
|
|
115
|
--INSERT INTO dbo.PL_PROCESS
|
|
116
|
--(
|
|
117
|
--REQ_ID,
|
|
118
|
--PROCESS_ID,
|
|
119
|
--CHECKER_ID,
|
|
120
|
--APPROVE_DT,
|
|
121
|
--PROCESS_DESC,
|
|
122
|
--NOTES
|
|
123
|
--)
|
|
124
|
--VALUES
|
|
125
|
--( @p_TRN_ID, -- REQ_ID - varchar(15)
|
|
126
|
-- 'REJECT', -- PROCESS_ID - varchar(10)
|
|
127
|
-- @p_REJECTED_BY, -- CHECKER_ID - varchar(15)
|
|
128
|
-- GETDATE(), -- APPROVE_DT - datetime
|
|
129
|
-- @p_REASON,N'Trả về ' + @ROLE_DES_PARENT
|
|
130
|
-- -- PROCESS_DESC - nvarchar(1000)
|
|
131
|
--)
|
|
132
|
SET @p_MESSAGE =N'Tạm thời bạn nên trả phiếu về người tạo'
|
|
133
|
SET @COMPLETE=0
|
|
134
|
END
|
|
135
|
-- SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
|
|
136
|
END
|
|
137
|
END
|
|
138
|
ELSE IF(@p_TRN_TYPE='TR_REQUEST_DOC')
|
|
139
|
BEGIN
|
|
140
|
--IF(@p_STAGE='DVMS')
|
|
141
|
--BEGIN
|
|
142
|
--END
|
|
143
|
|
|
144
|
IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID='APPROVE'))
|
|
145
|
BEGIN
|
|
146
|
SET @p_MESSAGE =N'Trả về thất bại! Phiếu yêu cầu đã được duyệt'
|
|
147
|
ROLLBACK TRANSACTION
|
|
148
|
SELECT '0' as Result, @p_LOG_ID LOG_ID ,@p_MESSAGE ErrorDesc
|
|
149
|
RETURN '0'
|
|
150
|
END
|
|
151
|
ELSE
|
|
152
|
BEGIN
|
|
153
|
IF(@p_TYPE='CREATE')
|
|
154
|
BEGIN
|
|
155
|
UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID='REJECT',AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID
|
|
156
|
UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=N'Phiếu bị trả về' WHERE PROCESS_ID='APPNEW' AND REQ_ID=@p_TRN_ID
|
|
157
|
UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID <>'APPNEW' AND REQ_ID=@p_TRN_ID
|
|
158
|
INSERT INTO dbo.PL_PROCESS
|
|
159
|
(
|
|
160
|
REQ_ID,
|
|
161
|
PROCESS_ID,
|
|
162
|
CHECKER_ID,
|
|
163
|
APPROVE_DT,
|
|
164
|
PROCESS_DESC,
|
|
165
|
NOTES
|
|
166
|
)
|
|
167
|
VALUES
|
|
168
|
( @p_TRN_ID, -- REQ_ID - varchar(15)
|
|
169
|
'REJECT', -- PROCESS_ID - varchar(10)
|
|
170
|
@p_REJECTED_BY, -- CHECKER_ID - varchar(15)
|
|
171
|
GETDATE(), -- APPROVE_DT - datetime
|
|
172
|
@p_REASON,N'Trả về người tạo'
|
|
173
|
-- PROCESS_DESC - nvarchar(1000)
|
|
174
|
)
|
|
175
|
SET @COMPLETE=1
|
|
176
|
SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
|
|
177
|
END
|
|
178
|
ELSE IF(@p_TYPE='NVXL')
|
|
179
|
BEGIN
|
|
180
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID
|
|
181
|
--UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'
|
|
182
|
--LUCTV 28 09 2020 DIEU CHINH LOI TRA VE NHUNG NHAN VIEN XU LY KHONG DUOC PHEP TRA VE TIEP
|
|
183
|
--KIEM TRA NGUOI DIEU PHOI - NEU LA TP - TRA VE CHO NHAN VIEN XU LY THI SE TRA VE CHO
|
|
184
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'
|
|
185
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'))
|
|
186
|
BEGIN
|
|
187
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB ='KS'
|
|
188
|
END
|
|
189
|
INSERT INTO dbo.PL_PROCESS
|
|
190
|
(
|
|
191
|
REQ_ID,
|
|
192
|
PROCESS_ID,
|
|
193
|
CHECKER_ID,
|
|
194
|
APPROVE_DT,
|
|
195
|
PROCESS_DESC,
|
|
196
|
NOTES
|
|
197
|
)
|
|
198
|
VALUES
|
|
199
|
( @p_TRN_ID, -- REQ_ID - varchar(15)
|
|
200
|
'REJECT', -- PROCESS_ID - varchar(10)
|
|
201
|
@p_REJECTED_BY, -- CHECKER_ID - varchar(15)
|
|
202
|
GETDATE(), -- APPROVE_DT - datetime
|
|
203
|
@p_REASON,N'Trả về nhân viên xử lý'
|
|
204
|
-- PROCESS_DESC - nvarchar(1000)
|
|
205
|
)
|
|
206
|
SET @COMPLETE=1
|
|
207
|
SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
|
|
208
|
END
|
|
209
|
ELSE IF(@p_TYPE='PRE_APP')
|
|
210
|
BEGIN
|
|
211
|
--SET @PARENT_PROCESS_ID =(SELECT TOP 1 PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
|
|
212
|
--SET @ROLE_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
|
|
213
|
--SET @ROLE_PARENT_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID)
|
|
214
|
--SET @ROLE_DES=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)
|
|
215
|
--SET @ROLE_DES_PARENT=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_PARENT_ID)
|
|
216
|
|
|
217
|
--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=@ROLE_DES + N' đã trả về, chờ ' + @ROLE_DES_PARENT +N' xử lý' WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID
|
|
218
|
--IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID=@PARENT_PROCESS_ID AND IS_HAS_CHILD=1))
|
|
219
|
--BEGIN
|
|
220
|
-- UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID IN (SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID=@PARENT_PROCESS_ID AND IS_HAS_CHILD=1) AND LEVEL_JOB=1
|
|
221
|
--END
|
|
222
|
|
|
223
|
--IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID='DVCM'))
|
|
224
|
--BEGIN
|
|
225
|
-- UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID
|
|
226
|
--END
|
|
227
|
|
|
228
|
--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID
|
|
229
|
--UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PARENT_PROCESS_ID WHERE REQ_ID=@p_TRN_ID
|
|
230
|
--IF(@PARENT_PROCESS_ID='APPNEW')
|
|
231
|
-- UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U' WHERE REQ_ID=@p_TRN_ID
|
|
232
|
|
|
233
|
|
|
234
|
--INSERT INTO dbo.PL_PROCESS
|
|
235
|
--(
|
|
236
|
--REQ_ID,
|
|
237
|
--PROCESS_ID,
|
|
238
|
--CHECKER_ID,
|
|
239
|
--APPROVE_DT,
|
|
240
|
--PROCESS_DESC,NOTES
|
|
241
|
--)
|
|
242
|
--VALUES
|
|
243
|
--( @p_TRN_ID, -- REQ_ID - varchar(15)
|
|
244
|
-- 'REJECT', -- PROCESS_ID - varchar(10)
|
|
245
|
-- @p_REJECTED_BY, -- CHECKER_ID - varchar(15)
|
|
246
|
-- GETDATE(), -- APPROVE_DT - datetime
|
|
247
|
-- @p_REASON,N'Trả về ' + @ROLE_DES_PARENT
|
|
248
|
-- -- PROCESS_DESC - nvarchar(1000)
|
|
249
|
--)
|
|
250
|
SET @p_MESSAGE =N'Tạm thời bạn nên trả phiếu về người tạo'
|
|
251
|
SET @COMPLETE=0
|
|
252
|
END
|
|
253
|
|
|
254
|
|
|
255
|
END
|
|
256
|
|
|
257
|
END
|
|
258
|
|
|
259
|
-- END IF
|
|
260
|
ELSE
|
|
261
|
BEGIN
|
|
262
|
SET @p_MESSAGE =N'Trả về giao dịch thành công'
|
|
263
|
END
|
|
264
|
|
|
265
|
--- INSERT VAO BANG CM_REJECT_LOG
|
|
266
|
IF(@COMPLETE=1)
|
|
267
|
BEGIN
|
|
268
|
EXEC SYS_CodeMasters_Gen 'CM_REJECT_LOG', @p_LOG_ID out
|
|
269
|
IF @p_LOG_ID='' OR @p_LOG_ID IS NULL GOTO ABORT
|
|
270
|
INSERT INTO [dbo].[CM_REJECT_LOG] VALUES (@p_LOG_ID,@p_STAGE,@p_TRN_ID,@p_TRN_TYPE,CONVERT(DATETIME,GETDATE(),103),'R',@p_REASON,@p_IS_LATEST,@p_REJECTED_BY,CONVERT(DATETIME,GETDATE(),103))
|
|
271
|
IF @@Error <> 0 GOTO ABORT
|
|
272
|
END
|
|
273
|
COMMIT TRANSACTION
|
|
274
|
|
|
275
|
SELECT '0' as Result, @p_LOG_ID LOG_ID ,@p_MESSAGE ErrorDesc
|
|
276
|
RETURN '0'
|
|
277
|
ABORT:
|
|
278
|
BEGIN
|
|
279
|
|
|
280
|
ROLLBACK TRANSACTION
|
|
281
|
SELECT '-1' as Result, '' LOG_ID, '' ErrorDesc
|
|
282
|
RETURN '-1'
|
|
283
|
END
|