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