|
1
|
ALTER PROCEDURE dbo.TR_REQ_PROCESS_CHILD_Ins
|
|
2
|
@p_MAKER_ID VARCHAR(20),
|
|
3
|
@p_XMLData XML
|
|
4
|
AS
|
|
5
|
BEGIN TRANSACTION
|
|
6
|
--- KHAI BAO CHUOI ID
|
|
7
|
DECLARE @ID_RETURN_FOR_EMAIL VARCHAR(500) =''
|
|
8
|
-----------------------------------------------------
|
|
9
|
|
|
10
|
DECLARE @p_ROW_NUM INT = 0
|
|
11
|
DECLARE @p_REQ_CODE VARCHAR(50)
|
|
12
|
|
|
13
|
Declare @hdoc INT
|
|
14
|
Exec sp_xml_preparedocument @hdoc Output,@p_XMLData
|
|
15
|
DECLARE ListREQ CURSOR FOR
|
|
16
|
SELECT *
|
|
17
|
FROM OPENXML(@hDoc,'/Root/ListREQ',2)
|
|
18
|
WITH
|
|
19
|
(
|
|
20
|
REQ_ID varchar(20) ,
|
|
21
|
PROCESS_ID varchar(20),
|
|
22
|
TLNAME VARCHAR(20),
|
|
23
|
TYPE_JOB VARCHAR(20),
|
|
24
|
REF_ID INT
|
|
25
|
|
|
26
|
)
|
|
27
|
OPEN ListREQ
|
|
28
|
DECLARE @LEVEL INT,@REQ_ID VARCHAR(20),@PROCESS_ID VARCHAR(20),@TYPE_JOB VARCHAR(20),@TLNAME VARCHAR(20),@REF_ID INT, @TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)
|
|
29
|
|
|
30
|
FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID
|
|
31
|
WHILE @@FETCH_STATUS=0
|
|
32
|
BEGIN
|
|
33
|
|
|
34
|
|
|
35
|
SET @p_ROW_NUM = @p_ROW_NUM + 1
|
|
36
|
SET @p_REQ_CODE = (SELECT TOP 1 REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID = @REQ_ID)
|
|
37
|
|
|
38
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME = @p_MAKER_ID AND REQ_ID = @REQ_ID AND TYPE_JOB = 'KS' AND STATUS_JOB = 'C' AND @TYPE_JOB ='KS'))
|
|
39
|
BEGIN
|
|
40
|
CLOSE ListREQ
|
|
41
|
DEALLOCATE ListREQ
|
|
42
|
ROLLBACK TRANSACTION
|
|
43
|
SELECT '-1' as Result,
|
|
44
|
N'Phiếu yêu cầu số ' +@p_REQ_CODE+
|
|
45
|
N'. Bạn đang là kiểm soát viên nên chỉ được điều phối người xử lý.' ErrorDesc
|
|
46
|
RETURN '-1'
|
|
47
|
END
|
|
48
|
IF(@TLNAME IS NULL OR @TLNAME = '')
|
|
49
|
BEGIN
|
|
50
|
CLOSE ListREQ
|
|
51
|
DEALLOCATE ListREQ
|
|
52
|
ROLLBACK TRANSACTION
|
|
53
|
SELECT '-1' as Result,
|
|
54
|
N'Điều phối phiếu yêu cầu số ' +@p_REQ_CODE+
|
|
55
|
N'. Vui lòng chọn tên người được giao xử lý' ErrorDesc
|
|
56
|
RETURN '-1'
|
|
57
|
END
|
|
58
|
|
|
59
|
-- BAT DAU GAN GIA TRI
|
|
60
|
SET @ID_RETURN_FOR_EMAIL = @ID_RETURN_FOR_EMAIL +','+@REQ_ID
|
|
61
|
--NEU LA DVKD THI MAC DINH LA CAP XU LY PHIEU - LUCTV
|
|
62
|
-- XOA QUYEN CU CUA USER
|
|
63
|
DELETE FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_ID AND TLNAME =@TLNAME AND (STATUS_JOB ='C' OR PROCESS_ID =@REF_ID)
|
|
64
|
|
|
65
|
DELETE FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_ID AND TYPE_JOB = 'XL' AND (STATUS_JOB ='C' OR PROCESS_ID =@REF_ID)
|
|
66
|
DELETE FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_ID AND TYPE_JOB = 'KS'
|
|
67
|
AND EXISTS(SELECT TLNAME FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_ID AND TLNAME =@p_MAKER_ID AND PROCESS_ID =@REF_ID AND TYPE_JOB = 'TP')
|
|
68
|
|
|
69
|
IF(EXISTS(SELECT * FROM CM_DMMS WHERE BRANCH_ID <> 'DV0001' AND DMMS_ID =(SELECT TOP 1 DMMS_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@REQ_ID)))
|
|
70
|
BEGIN
|
|
71
|
IF(@TYPE_JOB ='KS')
|
|
72
|
BEGIN
|
|
73
|
SET @TYPE_JOB ='XL'
|
|
74
|
END
|
|
75
|
END
|
|
76
|
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ID =@REF_ID AND REQ_ID=@REQ_ID AND DVDM_ID <>'DM0000000000003') AND EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@REQ_ID AND PROCESS_ID='DVCM'))
|
|
77
|
BEGIN
|
|
78
|
IF(@TYPE_JOB ='KS')
|
|
79
|
BEGIN
|
|
80
|
SET @TYPE_JOB ='XL'
|
|
81
|
END
|
|
82
|
END
|
|
83
|
-----------------------------------------------------------
|
|
84
|
SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB
|
|
85
|
FROM dbo.PL_REQUEST_PROCESS_CHILD
|
|
86
|
WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID
|
|
87
|
ORDER BY LEVEL_JOB DESC),0)
|
|
88
|
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB ))
|
|
89
|
BEGIN
|
|
90
|
IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB AND TLNAME <> @TLNAME ))
|
|
91
|
BEGIN
|
|
92
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@TLNAME,MAKER_ID=@p_MAKER_ID,TRANFER_DT=GETDATE() WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB
|
|
93
|
SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL = @TYPE_JOB)
|
|
94
|
SET @FULLNAME =(SELECT TLFullName FROM dbo.TL_USER WHERE TLNANME=@TLNAME)
|
|
95
|
|
|
96
|
|
|
97
|
INSERT INTO dbo.PL_PROCESS
|
|
98
|
(
|
|
99
|
REQ_ID,
|
|
100
|
PROCESS_ID,
|
|
101
|
CHECKER_ID,
|
|
102
|
APPROVE_DT,
|
|
103
|
PROCESS_DESC,
|
|
104
|
NOTES
|
|
105
|
)
|
|
106
|
VALUES
|
|
107
|
( @REQ_ID, -- REQ_ID - varchar(15)
|
|
108
|
@PROCESS_ID, -- PROCESS_ID - varchar(10)
|
|
109
|
@p_MAKER_ID, -- CHECKER_ID - varchar(15)
|
|
110
|
GETDATE(), -- APPROVE_DT - datetime
|
|
111
|
N'Nhân viên tiếp nhận ' + @FULLNAME + ' - ' + @TLNAME, -- PROCESS_DESC - nvarchar(1000)
|
|
112
|
N'Điều phối '+@TYPE_JOB_NAME -- NOTES - nvarchar(1000)
|
|
113
|
)
|
|
114
|
END
|
|
115
|
END
|
|
116
|
ELSE
|
|
117
|
BEGIN
|
|
118
|
IF(@LEVEL=0)
|
|
119
|
BEGIN
|
|
120
|
SET @LEVEL=@LEVEL +1
|
|
121
|
INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
|
|
122
|
(
|
|
123
|
REQ_ID,
|
|
124
|
PROCESS_ID,
|
|
125
|
TLNAME,
|
|
126
|
TYPE_JOB,
|
|
127
|
LEVEL_JOB,
|
|
128
|
STATUS_JOB,
|
|
129
|
RECORD_STATUS
|
|
130
|
)
|
|
131
|
VALUES
|
|
132
|
( @REQ_ID, -- REQ_ID - varchar(20)
|
|
133
|
@REF_ID, -- PROCESS_ID - varchar(20)
|
|
134
|
@p_MAKER_ID, -- TLNAME - varchar(50)
|
|
135
|
'TP', -- TYPE_JOB - varchar(50)
|
|
136
|
@LEVEL, -- LEVEL_JOB - int
|
|
137
|
'U', -- STATUS_JOB - varchar(10)
|
|
138
|
'1' -- RECORD_STATUS - varchar(15)
|
|
139
|
)
|
|
140
|
|
|
141
|
END
|
|
142
|
|
|
143
|
IF(@TYPE_JOB='KS' AND EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL'))
|
|
144
|
BEGIN
|
|
145
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB=@LEVEL + 1 WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL'
|
|
146
|
SET @LEVEL=@LEVEL-1
|
|
147
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL
|
|
148
|
IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL' AND STATUS_JOB='C'))
|
|
149
|
BEGIN
|
|
150
|
INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
|
|
151
|
(
|
|
152
|
REQ_ID,
|
|
153
|
PROCESS_ID,
|
|
154
|
TLNAME,
|
|
155
|
TYPE_JOB,
|
|
156
|
LEVEL_JOB,
|
|
157
|
STATUS_JOB,
|
|
158
|
RECORD_STATUS,
|
|
159
|
MAKER_ID,
|
|
160
|
TRANFER_DT
|
|
161
|
)
|
|
162
|
VALUES
|
|
163
|
( @REQ_ID, -- REQ_ID - varchar(20)
|
|
164
|
@REF_ID, -- PROCESS_ID - varchar(20)
|
|
165
|
@TLNAME, -- TLNAME - varchar(50)
|
|
166
|
@TYPE_JOB, -- TYPE_JOB - varchar(50)
|
|
167
|
@LEVEL + 1, -- LEVEL_JOB - int
|
|
168
|
'U', -- STATUS_JOB - varchar(10)
|
|
169
|
'1' , -- RECORD_STATUS - varchar(15)
|
|
170
|
@p_MAKER_ID,
|
|
171
|
GETDATE()
|
|
172
|
)
|
|
173
|
END
|
|
174
|
ELSE
|
|
175
|
BEGIN
|
|
176
|
INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
|
|
177
|
(
|
|
178
|
REQ_ID,
|
|
179
|
PROCESS_ID,
|
|
180
|
TLNAME,
|
|
181
|
TYPE_JOB,
|
|
182
|
LEVEL_JOB,
|
|
183
|
STATUS_JOB,
|
|
184
|
RECORD_STATUS,
|
|
185
|
MAKER_ID,
|
|
186
|
TRANFER_DT
|
|
187
|
)
|
|
188
|
VALUES
|
|
189
|
( @REQ_ID, -- REQ_ID - varchar(20)
|
|
190
|
@REF_ID, -- PROCESS_ID - varchar(20)
|
|
191
|
@TLNAME, -- TLNAME - varchar(50)
|
|
192
|
@TYPE_JOB, -- TYPE_JOB - varchar(50)
|
|
193
|
@LEVEL + 1, -- LEVEL_JOB - int
|
|
194
|
'C', -- STATUS_JOB - varchar(10)
|
|
195
|
'1' , -- RECORD_STATUS - varchar(15)
|
|
196
|
@p_MAKER_ID,
|
|
197
|
GETDATE()
|
|
198
|
)
|
|
199
|
END
|
|
200
|
|
|
201
|
END
|
|
202
|
ELSE
|
|
203
|
BEGIN
|
|
204
|
|
|
205
|
UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL
|
|
206
|
INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
|
|
207
|
(
|
|
208
|
REQ_ID,
|
|
209
|
PROCESS_ID,
|
|
210
|
TLNAME,
|
|
211
|
TYPE_JOB,
|
|
212
|
LEVEL_JOB,
|
|
213
|
STATUS_JOB,
|
|
214
|
RECORD_STATUS,
|
|
215
|
MAKER_ID,
|
|
216
|
TRANFER_DT
|
|
217
|
)
|
|
218
|
VALUES
|
|
219
|
( @REQ_ID, -- REQ_ID - varchar(20)
|
|
220
|
@REF_ID, -- PROCESS_ID - varchar(20)
|
|
221
|
@TLNAME, -- TLNAME - varchar(50)
|
|
222
|
@TYPE_JOB, -- TYPE_JOB - varchar(50)
|
|
223
|
@LEVEL + 1, -- LEVEL_JOB - int
|
|
224
|
'C', -- STATUS_JOB - varchar(10)
|
|
225
|
'1' , -- RECORD_STATUS - varchar(15)
|
|
226
|
@p_MAKER_ID,
|
|
227
|
GETDATE()
|
|
228
|
)
|
|
229
|
END
|
|
230
|
SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@TYPE_JOB)
|
|
231
|
SET @FULLNAME =(SELECT TLFullName FROM dbo.TL_USER WHERE TLNANME=@TLNAME)
|
|
232
|
|
|
233
|
|
|
234
|
INSERT INTO dbo.PL_PROCESS
|
|
235
|
(
|
|
236
|
REQ_ID,
|
|
237
|
PROCESS_ID,
|
|
238
|
CHECKER_ID,
|
|
239
|
APPROVE_DT,
|
|
240
|
PROCESS_DESC,
|
|
241
|
NOTES
|
|
242
|
)
|
|
243
|
VALUES
|
|
244
|
( @REQ_ID, -- REQ_ID - varchar(15)
|
|
245
|
@PROCESS_ID, -- PROCESS_ID - varchar(10)
|
|
246
|
@p_MAKER_ID, -- CHECKER_ID - varchar(15)
|
|
247
|
GETDATE(), -- APPROVE_DT - datetime
|
|
248
|
N'Nhân viên tiếp nhận ' + @FULLNAME + ' - ' + @TLNAME, -- PROCESS_DESC - nvarchar(1000)
|
|
249
|
N'Điều phối '+@TYPE_JOB_NAME -- NOTES - nvarchar(1000)
|
|
250
|
)
|
|
251
|
END
|
|
252
|
FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID
|
|
253
|
END
|
|
254
|
CLOSE ListREQ
|
|
255
|
|
|
256
|
DEALLOCATE ListREQ
|
|
257
|
|
|
258
|
|
|
259
|
IF @@Error <> 0 GOTO ABORT
|
|
260
|
COMMIT TRANSACTION
|
|
261
|
SELECT 0 as Result, @ID_RETURN_FOR_EMAIL ErrorDesc
|
|
262
|
RETURN 0
|
|
263
|
ABORT:
|
|
264
|
BEGIN
|
|
265
|
ROLLBACK TRANSACTION
|
|
266
|
SELECT -1 as Result, '' ErrorDesc
|
|
267
|
RETURN -1
|
|
268
|
End
|