|
1
|
ALTER PROCEDURE [dbo].[TR_ROLE_NOTIFI_ID]
|
|
2
|
@PO_ID varchar(500),
|
|
3
|
@TYPE VARCHAR(100)
|
|
4
|
AS
|
|
5
|
PRINT @TYPE
|
|
6
|
|
|
7
|
DECLARE
|
|
8
|
@BRANCH_CREATE VARCHAR(15),
|
|
9
|
@DEP_CREATE VARCHAR(15),
|
|
10
|
@BRANCH_TYPE VARCHAR(15),
|
|
11
|
@FATHER_ID VARCHAR(15),
|
|
12
|
@FLAG VARCHAR(1), -- FLAG = 1: THANH TOÁN / TẠM ỨNG
|
|
13
|
@AUTH_STATUS VARCHAR(10),
|
|
14
|
@PROCESS VARCHAR(10),
|
|
15
|
@MAKER_ID VARCHAR(15)
|
|
16
|
|
|
17
|
DECLARE @l_LST_REQ_ID TABLE (
|
|
18
|
[ID] [int] IDENTITY(1,1) NOT NULL,
|
|
19
|
[REQ_PAY_ID] [VARCHAR](15) NULL)
|
|
20
|
INSERT INTO @l_LST_REQ_ID SELECT VALUE FROM WSISPLIT(@PO_ID,',')
|
|
21
|
DECLARE @LST_POID TABLE(ID VARCHAR(15))
|
|
22
|
DECLARE @LST_USER_RECIVE TABLE (TLNAME VARCHAR(10))
|
|
23
|
--- DECLARE TABLE NHAN DU LIEU LA USER NAME DANG O BUOC PHE DUYET HIEN TAI
|
|
24
|
DECLARE @PL_PROCESS_CURRENT_SEARCH_TEMP TABLE
|
|
25
|
(
|
|
26
|
REQ_ID varchar(15),
|
|
27
|
PROCESS_ID varchar(10),
|
|
28
|
DVDM_NAME nvarchar(500),
|
|
29
|
TLNAME nvarchar(255),
|
|
30
|
TLFullName nvarchar(255),
|
|
31
|
NOTES nvarchar(500)
|
|
32
|
)
|
|
33
|
IF @TYPE = 'PO'
|
|
34
|
BEGIN
|
|
35
|
INSERT INTO @LST_POID VALUES(@PO_ID)
|
|
36
|
SET @FLAG = 0
|
|
37
|
END ELSE
|
|
38
|
IF @TYPE = 'USE'
|
|
39
|
BEGIN
|
|
40
|
INSERT INTO @LST_POID SELECT B.PO_ID
|
|
41
|
FROM ASS_MASTER_PO B WHERE B.ASSET_ID = (SELECT A.ASSET_ID FROM ASS_USE A WHERE A.USE_ID = @PO_ID)
|
|
42
|
SET @FLAG = 0
|
|
43
|
END ELSE
|
|
44
|
IF @TYPE = 'USE_MUILT'
|
|
45
|
BEGIN
|
|
46
|
INSERT INTO @LST_POID SELECT B.PO_ID
|
|
47
|
FROM ASS_MASTER_PO B WHERE B.ASSET_ID IN (SELECT A.ASSET_ID FROM ASS_USE_MULTI_DT A WHERE A.USER_MASTER_ID = @PO_ID)
|
|
48
|
GROUP BY B.PO_ID
|
|
49
|
SET @FLAG = 0
|
|
50
|
END ELSE
|
|
51
|
IF @TYPE = 'USE_ADDNEW'
|
|
52
|
BEGIN
|
|
53
|
INSERT INTO @LST_POID SELECT B.PO_ID
|
|
54
|
FROM ASS_ADDNEW_PO B WHERE B.ADDNEW_ID = @PO_ID
|
|
55
|
SET @FLAG = 0
|
|
56
|
END
|
|
57
|
-- Tạm ứng
|
|
58
|
ELSE IF (@TYPE = 'TR_REQ_ADVANCE_PAYMENT_SEND_APR')
|
|
59
|
BEGIN
|
|
60
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
61
|
--IF(@AUTH_STATUS = 'U')
|
|
62
|
--BEGIN
|
|
63
|
-- INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
64
|
-- (SELECT TRASFER_USER_RECIVE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
65
|
-- SET @FLAG = 1
|
|
66
|
--END
|
|
67
|
IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@PO_ID AND (TRASFER_USER_RECIVE IS NOT NULL AND TRASFER_USER_RECIVE <>'')))
|
|
68
|
BEGIN
|
|
69
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
70
|
(SELECT TRASFER_USER_RECIVE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
71
|
END
|
|
72
|
ELSE
|
|
73
|
BEGIN
|
|
74
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
75
|
SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
76
|
SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
77
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
78
|
SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
79
|
IF(@BRANCH_TYPE = 'PGD' )
|
|
80
|
BEGIN
|
|
81
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
82
|
SELECT TLNANME FROM TL_USER
|
|
83
|
WHERE 1=1
|
|
84
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
85
|
AND (RoleName IN ('TPGD','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('TPGD','PP')))
|
|
86
|
|
|
87
|
END
|
|
88
|
ELSE IF(@BRANCH_TYPE = 'CN' )
|
|
89
|
BEGIN
|
|
90
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
91
|
SELECT TLNANME FROM TL_USER
|
|
92
|
WHERE 1=1
|
|
93
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
94
|
AND (RoleName IN ('GDDV','PGD') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PGD')))
|
|
95
|
END
|
|
96
|
ELSE IF(@BRANCH_TYPE = 'HS' )
|
|
97
|
BEGIN
|
|
98
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
99
|
SELECT TLNANME FROM TL_USER
|
|
100
|
WHERE 1=1
|
|
101
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
102
|
AND SECUR_CODE = @DEP_CREATE
|
|
103
|
AND (RoleName IN ('GDDV','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PP')))
|
|
104
|
END
|
|
105
|
END
|
|
106
|
END
|
|
107
|
ELSE IF (@TYPE = 'TR_REQ_ADVANCE_PAYMENT_CONFIRM')
|
|
108
|
BEGIN
|
|
109
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
110
|
SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
111
|
SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
112
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
113
|
SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
114
|
IF(@BRANCH_TYPE = 'PGD' )
|
|
115
|
BEGIN
|
|
116
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
117
|
SELECT TLNANME FROM TL_USER
|
|
118
|
WHERE 1=1
|
|
119
|
AND TLSUBBRID = @FATHER_ID
|
|
120
|
AND (RoleName IN ('TPGD','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('TPGD','PP')))
|
|
121
|
|
|
122
|
END
|
|
123
|
ELSE IF(@BRANCH_TYPE = 'CN' )
|
|
124
|
BEGIN
|
|
125
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
126
|
SELECT TLNANME FROM TL_USER
|
|
127
|
WHERE 1=1
|
|
128
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
129
|
AND (RoleName IN ('GDDV','PGD') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PGD')))
|
|
130
|
END
|
|
131
|
ELSE IF(@BRANCH_TYPE = 'HS' )
|
|
132
|
BEGIN
|
|
133
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
134
|
SELECT TLNANME FROM TL_USER
|
|
135
|
WHERE 1=1
|
|
136
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
137
|
AND SECUR_CODE = @DEP_CREATE
|
|
138
|
AND (RoleName IN ('GDDV','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PP')))
|
|
139
|
END
|
|
140
|
SET @FLAG = 1
|
|
141
|
END
|
|
142
|
ELSE IF (@TYPE = 'TR_REQ_ADVANCE_PAYMENT_APR')
|
|
143
|
BEGIN
|
|
144
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
145
|
SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
146
|
SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
147
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
148
|
SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
149
|
IF(@AUTH_STATUS='A')
|
|
150
|
BEGIN
|
|
151
|
IF (@BRANCH_TYPE <> 'HS')
|
|
152
|
BEGIN
|
|
153
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
154
|
SELECT TLNANME FROM TL_USER
|
|
155
|
WHERE 1=1
|
|
156
|
--AND TLSUBBRID = @FATHER_ID
|
|
157
|
AND (RoleName = 'KSV' OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW = 'KSV'))
|
|
158
|
AND TLSUBBRID = 'DV0001' AND SECUR_CODE ='DEP000000000022'
|
|
159
|
END
|
|
160
|
ELSE IF (@BRANCH_TYPE = 'HS')
|
|
161
|
BEGIN
|
|
162
|
--IF((SELECT REQ_TYPE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID) = 'I')
|
|
163
|
--BEGIN
|
|
164
|
|
|
165
|
--END
|
|
166
|
--ELSE
|
|
167
|
--BEGIN
|
|
168
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
169
|
SELECT TLNANME FROM TL_USER
|
|
170
|
WHERE 1=1
|
|
171
|
--AND TLSUBBRID = @BRANCH_CREATE
|
|
172
|
AND (RoleName = 'KSV' OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW = 'KSV'))
|
|
173
|
AND TLSUBBRID = 'DV0001' AND SECUR_CODE ='DEP000000000022'
|
|
174
|
--END
|
|
175
|
END
|
|
176
|
END
|
|
177
|
ELSE
|
|
178
|
BEGIN
|
|
179
|
DECLARE @ROLE_CURRENT VARCHAR(15), @USER_RECIVE_MAIL VARCHAR(15)
|
|
180
|
SET @ROLE_CURRENT =(SELECT TOP 1 ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@PO_ID AND STATUS='C')
|
|
181
|
SET @USER_RECIVE_MAIL =(SELECT TLNANME FROM TL_USER WHERE RoleName =@ROLE_CURRENT)
|
|
182
|
INSERT INTO @LST_USER_RECIVE (TLNAME) VALUES (@USER_RECIVE_MAIL)
|
|
183
|
END
|
|
184
|
SET @FLAG = 1
|
|
185
|
END
|
|
186
|
-- Điều phối tạm ứng/ thanh toán PL_REQUEST_PROCESS_CHILD
|
|
187
|
ELSE IF (@TYPE = 'TR_REQ_PAYMENT_TRANSFER')
|
|
188
|
BEGIN
|
|
189
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
190
|
SELECT TOP 1 TLNAME FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID IN (SELECT REQ_PAY_ID FROM @l_LST_REQ_ID) AND TYPE_JOB = 'XL'
|
|
191
|
END
|
|
192
|
--
|
|
193
|
ELSE IF (@TYPE = 'TR_REQ_ADVANCE_PAYMENT_KT_SEND_APR')
|
|
194
|
BEGIN
|
|
195
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
196
|
SELECT TOP 1 TLNAME FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @PO_ID AND TYPE_JOB = 'KS'
|
|
197
|
END
|
|
198
|
ELSE IF (@TYPE = 'TR_REQ_ADVANCE_PAYMENT_KT_APPR')
|
|
199
|
BEGIN
|
|
200
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
201
|
SELECT TOP 1 MAKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID
|
|
202
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
203
|
SELECT TOP 1 MAKER_ID_KT FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @PO_ID
|
|
204
|
SET @FLAG = 1
|
|
205
|
END
|
|
206
|
-- Thanh toán
|
|
207
|
ELSE IF (@TYPE = 'TR_REQ_PAYMENT_SEND_APR')
|
|
208
|
BEGIN
|
|
209
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
210
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@PO_ID AND (TRASFER_USER_RECIVE IS NOT NULL AND TRASFER_USER_RECIVE <>'')))
|
|
211
|
BEGIN
|
|
212
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
213
|
(SELECT TRASFER_USER_RECIVE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
214
|
END
|
|
215
|
ELSE
|
|
216
|
BEGIN
|
|
217
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
218
|
SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
219
|
SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
220
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
221
|
SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
222
|
IF(@BRANCH_TYPE = 'PGD' )
|
|
223
|
BEGIN
|
|
224
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
225
|
SELECT TLNANME FROM TL_USER
|
|
226
|
WHERE 1=1
|
|
227
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
228
|
AND (RoleName IN ('TPGD','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('TPGD','PP')))
|
|
229
|
|
|
230
|
END
|
|
231
|
ELSE IF(@BRANCH_TYPE = 'CN' )
|
|
232
|
BEGIN
|
|
233
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
234
|
SELECT TLNANME FROM TL_USER
|
|
235
|
WHERE 1=1
|
|
236
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
237
|
AND (RoleName IN ('GDDV','PGD') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PGD')))
|
|
238
|
END
|
|
239
|
ELSE IF(@BRANCH_TYPE = 'HS' )
|
|
240
|
BEGIN
|
|
241
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
242
|
SELECT TLNANME FROM TL_USER
|
|
243
|
WHERE 1=1
|
|
244
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
245
|
AND SECUR_CODE = @DEP_CREATE
|
|
246
|
AND (RoleName IN ('GDDV','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PP')))
|
|
247
|
END
|
|
248
|
END
|
|
249
|
SET @FLAG = 1
|
|
250
|
END
|
|
251
|
ELSE IF(@TYPE = 'TR_REQ_PAYMENT_APR')
|
|
252
|
BEGIN
|
|
253
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
254
|
IF(@AUTH_STATUS = 'A')
|
|
255
|
BEGIN
|
|
256
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
257
|
SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
258
|
SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
259
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
260
|
SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
261
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
262
|
SELECT TLNANME FROM TL_USER
|
|
263
|
WHERE 1=1
|
|
264
|
--AND TLSUBBRID = @BRANCH_CREATE
|
|
265
|
AND (RoleName = 'KSV' OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW = 'KSV'))
|
|
266
|
AND TLSUBBRID = 'DV0001' AND SECUR_CODE ='DEP000000000022'
|
|
267
|
--IF (@BRANCH_TYPE <> 'HS')
|
|
268
|
--BEGIN
|
|
269
|
-- INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
270
|
-- SELECT TLNANME FROM TL_USER
|
|
271
|
-- WHERE 1=1
|
|
272
|
-- AND TLSUBBRID = @FATHER_ID
|
|
273
|
-- AND (RoleName = 'KSV' OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW = 'KSV'))
|
|
274
|
--END
|
|
275
|
--ELSE IF (@BRANCH_TYPE = 'HS')
|
|
276
|
--BEGIN
|
|
277
|
-- INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
278
|
-- SELECT TLNANME FROM TL_USER
|
|
279
|
-- WHERE 1=1
|
|
280
|
-- AND TLSUBBRID = @BRANCH_CREATE
|
|
281
|
-- AND (RoleName = 'KSV' OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW = 'KSV'))
|
|
282
|
--END
|
|
283
|
SET @FLAG = 1
|
|
284
|
END
|
|
285
|
END
|
|
286
|
ELSE IF(@TYPE = 'TR_REQ_PAYMENT_CONFIRM')
|
|
287
|
BEGIN
|
|
288
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
289
|
SET @PROCESS = ( SELECT TOP 1 PROCESS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
290
|
IF(@AUTH_STATUS = 'U' AND @PROCESS = '0')
|
|
291
|
BEGIN
|
|
292
|
SET @AUTH_STATUS =( SELECT TOP 1 AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
293
|
SET @BRANCH_CREATE = (SELECT BRANCH_CREATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
294
|
SET @DEP_CREATE = (SELECT DEP_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID)
|
|
295
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
296
|
SET @FATHER_ID = (SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_CREATE)
|
|
297
|
IF(@BRANCH_TYPE = 'PGD' )
|
|
298
|
BEGIN
|
|
299
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
300
|
SELECT TLNANME FROM TL_USER
|
|
301
|
WHERE 1=1
|
|
302
|
AND TLSUBBRID = @FATHER_ID
|
|
303
|
AND (RoleName IN ('TPGD','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('TPGD','PP')))
|
|
304
|
|
|
305
|
END
|
|
306
|
ELSE IF(@BRANCH_TYPE = 'CN' )
|
|
307
|
BEGIN
|
|
308
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
309
|
SELECT TLNANME FROM TL_USER
|
|
310
|
WHERE 1=1
|
|
311
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
312
|
AND (RoleName IN ('GDDV','PGD') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PGD')))
|
|
313
|
END
|
|
314
|
ELSE IF(@BRANCH_TYPE = 'HS' )
|
|
315
|
BEGIN
|
|
316
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
317
|
SELECT TLNANME FROM TL_USER
|
|
318
|
WHERE 1=1
|
|
319
|
AND TLSUBBRID = @BRANCH_CREATE
|
|
320
|
AND SECUR_CODE = @DEP_CREATE
|
|
321
|
AND (RoleName IN ('GDDV','PP') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW IN ('GDDV','PP')))
|
|
322
|
END
|
|
323
|
SET @FLAG = 1
|
|
324
|
END
|
|
325
|
END
|
|
326
|
ELSE IF(@TYPE = 'TR_REQ_PAYMENT_KT_SEND_APR')
|
|
327
|
BEGIN
|
|
328
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
329
|
SELECT TOP 1 TLNAME FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @PO_ID AND TYPE_JOB = 'KS'
|
|
330
|
SET @FLAG = 1
|
|
331
|
END
|
|
332
|
ELSE IF(@TYPE = 'TR_REQ_PAYMENT_KT_APR')
|
|
333
|
BEGIN
|
|
334
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
335
|
SELECT TOP 1 MAKER_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID
|
|
336
|
INSERT INTO @LST_USER_RECIVE (TLNAME)
|
|
337
|
SELECT TOP 1 MAKER_ID_KT FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @PO_ID
|
|
338
|
SET @FLAG = 1
|
|
339
|
END
|
|
340
|
-- Tờ trình chủ trương
|
|
341
|
ELSE IF(@TYPE = 'PL_SEND_APP' OR @TYPE ='PL_REQUEST_DOC_App' OR @TYPE ='REQ_PROCESS_CHILD_Upd' OR @TYPE='REQ_PROCESS_CHILD_App')
|
|
342
|
BEGIN
|
|
343
|
SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
344
|
INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
345
|
EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @PO_ID,@MAKER_ID,'TTCT-DVKD'
|
|
346
|
SET @FLAG = 2
|
|
347
|
END
|
|
348
|
ELSE IF(@TYPE='PL_REQ_PROCESS_CHILD_Ins')
|
|
349
|
BEGIN
|
|
350
|
DECLARE @PLREQ_ID VARCHAR(15)
|
|
351
|
WHILE((SELECT COUNT(*) FROM @l_LST_REQ_ID) >0)
|
|
352
|
BEGIN
|
|
353
|
SET @PLREQ_ID =(SELECT TOP 1 REQ_PAY_ID FROM @l_LST_REQ_ID)
|
|
354
|
SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
355
|
INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
356
|
EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @PLREQ_ID,@MAKER_ID,'TTCT-DVKD'
|
|
357
|
DELETE FROM @l_LST_REQ_ID WHERE REQ_PAY_ID =@PLREQ_ID
|
|
358
|
END
|
|
359
|
SET @FLAG = 2
|
|
360
|
END
|
|
361
|
ELSE IF(@TYPE='REQUEST_DOC_PROCESS_Approve')
|
|
362
|
BEGIN
|
|
363
|
--DECLARE @REQ_ID VARCHAR(15)
|
|
364
|
--WHILE((SELECT COUNT(*) FROM @l_LST_REQ_ID) >0)
|
|
365
|
--BEGIN
|
|
366
|
--SET @REQ_ID =(SELECT TOP 1 REQ_PAY_ID FROM @l_LST_REQ_ID)
|
|
367
|
--SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
368
|
--INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
369
|
--EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @REQ_ID,@MAKER_ID,'TTCT-DVKD'
|
|
370
|
--DELETE FROM @l_LST_REQ_ID WHERE REQ_PAY_ID =@REQ_ID
|
|
371
|
--END
|
|
372
|
-- NEU TOI TGD THI GUI MAIL CHO ANH THIEU, ANH HUNG
|
|
373
|
--IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID=@PO_ID AND PROCESS_ID ='TGD'))
|
|
374
|
--BEGIN
|
|
375
|
-- INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP SELECT @PO_ID,'','','thieuvq','',''
|
|
376
|
-- INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP SELECT @PO_ID,'','','hungdv','',''
|
|
377
|
--END
|
|
378
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID AND PROCESS_ID ='APPROVE'))
|
|
379
|
BEGIN
|
|
380
|
IF(NOT EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID AND PROCESS_ID ='TGD'))
|
|
381
|
BEGIN
|
|
382
|
SET @FLAG = 2
|
|
383
|
--SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
384
|
INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
385
|
EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @PO_ID,@MAKER_ID,'TTCT-DVKD'
|
|
386
|
END
|
|
387
|
END
|
|
388
|
ELSE
|
|
389
|
BEGIN
|
|
390
|
SET @FLAG = 3
|
|
391
|
SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM PL_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
392
|
--INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
393
|
--EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @REQ_ID,@MAKER_ID,'TTCT-DVKD'
|
|
394
|
END
|
|
395
|
END
|
|
396
|
-------------------------------------------------------------------------------------------------------
|
|
397
|
----- PHIẾU YÊU CẦU MUA SẮM --------------
|
|
398
|
ELSE IF(@TYPE = 'TR_REQUEST_DOC_SendApp' OR @TYPE ='TR_REQUEST_DOC_App' OR @TYPE ='TR_REQ_PROCESS_CHILD_App' OR @TYPE ='TR_REQ_PROCESS_CHILD_Upd' OR @TYPE='REQ_PROCESS_CHILD_App')
|
|
399
|
BEGIN
|
|
400
|
SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
401
|
INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
402
|
EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @PO_ID,@MAKER_ID,'PYCMS-DVKD'
|
|
403
|
SET @FLAG = 2
|
|
404
|
END
|
|
405
|
ELSE IF(@TYPE='TR_REQ_PROCESS_CHILD_Ins')
|
|
406
|
BEGIN
|
|
407
|
|
|
408
|
DECLARE @REQ_DOC_ID VARCHAR(15)
|
|
409
|
WHILE((SELECT COUNT(*) FROM @l_LST_REQ_ID) >0)
|
|
410
|
BEGIN
|
|
411
|
SET @REQ_DOC_ID =(SELECT TOP 1 REQ_PAY_ID FROM @l_LST_REQ_ID)
|
|
412
|
SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
413
|
INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
414
|
EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @REQ_DOC_ID,@MAKER_ID,'PYCMS-DVKD'
|
|
415
|
DELETE FROM @l_LST_REQ_ID WHERE REQ_PAY_ID =@REQ_DOC_ID
|
|
416
|
END
|
|
417
|
SET @FLAG = 2
|
|
418
|
END
|
|
419
|
ELSE IF(@TYPE='TR_REQUEST_DOC_PROCESS_Approve')
|
|
420
|
BEGIN
|
|
421
|
IF(NOT EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@PO_ID AND PROCESS_ID ='APPROVE'))
|
|
422
|
BEGIN
|
|
423
|
--SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
424
|
INSERT INTO @PL_PROCESS_CURRENT_SEARCH_TEMP
|
|
425
|
EXEC [dbo].[PL_PROCESS_CURRENT_SEARCH] @PO_ID,@MAKER_ID,'PYCMS-DVKD'
|
|
426
|
SET @FLAG = 3
|
|
427
|
END
|
|
428
|
ELSE
|
|
429
|
BEGIN
|
|
430
|
SET @MAKER_ID = (SELECT TOP 1 MAKER_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@PO_ID)
|
|
431
|
END
|
|
432
|
END
|
|
433
|
----- END PYC MUA SẮM --------------------
|
|
434
|
IF(@FLAG = 0)
|
|
435
|
BEGIN
|
|
436
|
SELECT A.*,B.TLFullName,B.EMAIL
|
|
437
|
FROM TL_ROLE_NOTIFICATION A
|
|
438
|
LEFT JOIN TL_USER B ON A.TL_NAME=B.TLNANME
|
|
439
|
WHERE EXISTS(SELECT * FROM @LST_POID WHERE ID = A.PO_ID)
|
|
440
|
AND B.TLNANME <>'trungnq1'
|
|
441
|
END
|
|
442
|
ELSE IF(@FLAG = 1)
|
|
443
|
BEGIN
|
|
444
|
SELECT B.*,A.TLFullName,A.EMAIL
|
|
445
|
FROM TL_USER A
|
|
446
|
LEFT JOIN TL_ROLE_NOTIFICATION B ON B.TL_NAME=A.TLNANME
|
|
447
|
WHERE A.TLNANME IN (SELECT * FROM @LST_USER_RECIVE)
|
|
448
|
AND A.TLNANME <>'trungnq1'
|
|
449
|
END
|
|
450
|
-- NEU LA TO TRINH CHU TRUONG, PYC MS THÌ TRA VE FLAG =2
|
|
451
|
ELSE IF(@FLAG = 2)
|
|
452
|
BEGIN
|
|
453
|
SELECT B.*,A.TLFullName,A.EMAIL
|
|
454
|
FROM TL_USER A
|
|
455
|
LEFT JOIN TL_ROLE_NOTIFICATION B ON B.TL_NAME=A.TLNANME
|
|
456
|
WHERE A.TLNANME IN (SELECT TLNAME FROM @PL_PROCESS_CURRENT_SEARCH_TEMP)
|
|
457
|
AND A.TLNANME <>'trungnq1'
|
|
458
|
END
|
|
459
|
-- SAU KHI TT CHU TRUONG DUOC PHE DUYET THI THONG BAO CHO NGUOI TAO TO TRINH
|
|
460
|
ELSE IF(@FLAG = 3)
|
|
461
|
BEGIN
|
|
462
|
SELECT B.*,A.TLFullName,A.EMAIL
|
|
463
|
FROM TL_USER A
|
|
464
|
LEFT JOIN TL_ROLE_NOTIFICATION B ON B.TL_NAME=A.TLNANME
|
|
465
|
WHERE (A.TLNANME =@MAKER_ID OR A.TLNANME IN (SELECT TLNAME FROM @PL_PROCESS_CURRENT_SEARCH_TEMP))
|
|
466
|
AND A.TLNANME <>'trungnq1'
|
|
467
|
END
|
|
468
|
|
|
469
|
|