Project

General

Profile

Download (23 KB)

TR_REQ_DOC_APP.txt

Luc Tran Van, 12/24/2020 02:26 PM

 
1 
??
2
3 
EXEC SP_HELPTEXT 'TR_REQUEST_DOC_APP'
4
5 
Tr?ng th?i: Select th?nh c?ng
6
7
8
9 
Text
10 
CREATE PROCEDURE [dbo].[TR_REQUEST_DOC_App]
11 
@p_REQ_ID VARCHAR(15) = NULL,
12 
@p_AUTH_STATUS VARCHAR(1) = NULL,
13 
@p_CHECKER_ID varchar(15) = NULL,
14 
@p_APPROVE_DT DATETIME = NULL,
15 
@p_ROLE_LOGIN VARCHAR(50) = NULL,
16 
@p_BRANCH_LOGIN VARCHAR(15),
17 
@p_PROCESS_DES NVARCHAR(500)
18
19 
AS
20 
--Validation is here
21 
DECLARE @ERRORSYS NVARCHAR(15) = ''
22 
IF ( NOT EXISTS ( SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
23 
SET @ERRORSYS = 'REQ-00002'
24 
IF @ERRORSYS <> ''
25 
BEGIN
26 
SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
27 
RETURN '0'
28 
END
29 
BEGIN TRANSACTION
30 
-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC
31 
IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))
32 
BEGIN
33 
ROLLBACK TRANSACTION
34 
SELECT '-1' Result, N'Phi?u y?u c?u mua s?m s?: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' ang b? t? ch?i. Vui l?ng ?i nh?n vi?n x? l? phi?u v? g?i ph? duy?t l?i!' ErrorDesc
35 
RETURN '-1'
36 
END
37 
--- PHE DUYET TRUNG GIAN
38 
DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE_N VARCHAR(20) ,@DEP_CREATE_N VARCHAR(20),@BRANCH_TYPE VARCHAR(10),
39 
@BRANCH_CREATE_TYPE VARCHAR(10)
40 
SELECT @BRANCH_CREATE_N=BRANCH_CREATE,@DEP_CREATE_N=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
41 
SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
42 
SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N)
43 
IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN'))
44 
BEGIN
45 
DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID
46 
INSERT INTO dbo.PL_PROCESS
47 
(
48 
REQ_ID,
49 
PROCESS_ID,
50 
CHECKER_ID,
51 
APPROVE_DT,
52 
PROCESS_DESC,NOTES
53 
)
54 
VALUES
55 
( @p_REQ_ID, -- REQ_ID - varchar(15)
56 
'SIGN', -- PROCESS_ID - varchar(10)
57 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
58 
@p_APPROVE_DT , -- APPROVE_DT - datetime
59 
N'C?p ph? duy?t trung gian x?c nh?n phi?u y?u c?u mua s?m',
60 
N'C?p ph? duy?t trung gian'
61 
)
62 
--- DUA CAP PHE DUYET TRUONG DON VI
63 
IF(@BRANCH_CREATE_TYPE='PGD')
64 
SET @BRANCH_CREATE_N=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N)
65
66 
INSERT INTO dbo.PL_REQUEST_PROCESS
67 
(
68 
REQ_ID,
69 
PROCESS_ID,
70 
STATUS,
71 
ROLE_USER,
72 
BRANCH_ID,
73 
DEP_ID,
74 
CHECKER_ID,
75 
APPROVE_DT,
76 
PARENT_PROCESS_ID,
77 
IS_LEAF,
78 
COST_ID,
79 
DVDM_ID,
80 
NOTES,
81 
IS_HAS_CHILD
82 
)
83 
VALUES
84 
( @p_REQ_ID, -- REQ_ID - varchar(15)
85 
'APPNEW', -- PROCESS_ID - varchar(10)
86 
'C', -- STATUS - varchar(5)
87 
'GDDV', -- ROLE_USER - varchar(50)
88 
@BRANCH_CREATE_N,
89 
@DEP_CREATE_N, -- BRANCH_ID - varchar(15)
90 
'', -- CHECKER_ID - varchar(15)
91 
NULL, -- APPROVE_DT - datetime
92 
'', -- PARENT_PROCESS_ID - varchar(10)
93 
'N', -- IS_LEAF - varchar(1)
94 
'', -- COST_ID - varchar(15)
95 
'', -- DVDM_ID - varchar(15)
96 
N'Ch? tr??ng ?n v? ph? duy?t', -- NOTES - nvarchar(500)
97 
NULL -- IS_HAS_CHILD - bit
98 
)
99 
--- UPDATE PROCESS_ID VE APP_NEW
100 
UPDATE TR_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID
101 
END
102 
ELSE
103 
--- PHE DUYET GIU NGUYEN NHU THUONG
104 
BEGIN
105 
--- KIEM TRA XEM DA PHE DUYET TRUNG GIAN HAY CHUA
106 
IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> ''))
107 
BEGIN
108 
IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ_ID))
109 
BEGIN
110 
ROLLBACK TRANSACTION
111 
SELECT -1 Result, N'Phi?u y?u c?u mua s?m s?: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' ang ?i c?p ph? duy?t trung gian x?c nh?n. Vui l?ng ?i nh?n vi?n '+(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' x?c
112 
nh?n phi?u!' ErrorDesc
113 
RETURN -1
114 
END
115 
IF(@p_CHECKER_ID = (SELECT TOP 1 SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))
116 
BEGIN
117 
ROLLBACK TRANSACTION
118 
SELECT '-1' Result, N'Phi?u y?u c?u mua s?m s?: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' ang ?i tr??ng ?n v? ph? duy?t. B?n kh?ng c? th?m quy?n ph? duy?t c?p tr??ng ?n v?! Vui l?ng xem l?ch s? x? l? phi?u' ErrorDesc
119 
RETURN '-1'
120 
END
121 
END
122 
DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID
123 
DECLARE @CURR_PROCESS VARCHAR(20),@TOTAL_AMT DECIMAL(18,2),
124 
@LIMIT_VALUE DECIMAL(18,0), @LIMIT_DVCM DECIMAL(18,0),@IS_NEXT BIT,@DMMS_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@BRANCH_HS VARCHAR(20),@DEP_CREATE VARCHAR(15)
125 
SET @BRANCH_HS= (SELECT TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_TYPE='HS')
126 
SET @LIMIT_VALUE=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PYCMS_DVKD')
127 
SET @LIMIT_DVCM=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PYCMS_DVCM')
128 
SET @TOTAL_AMT=(SELECT TOTAL_AMT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
129
130 
SET @CURR_PROCESS = (SELECT PROCESS_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
131
132 
UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='A',REQ_TYPE=0, CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CAST(@p_APPROVE_DT AS DATE),BRANCH_DVMS=BRANCH_CREATE, PROCESS_ID='DMMS' WHERE REQ_ID=@p_REQ_ID
133
134 
-- SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))
135
136 
SET @BRANCH_CREATE=(SELECT BRANCH_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
137 
SET @DEP_CREATE=(SELECT DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
138
139 
IF(@BRANCH_CREATE_TYPE='PGD')
140 
SET @BRANCH_CREATE=(SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
141
142 
IF(EXISTS (SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS'))
143 
BEGIN
144 
IF(@TOTAL_AMT <= @LIMIT_DVCM)
145 
BEGIN
146 
SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT
147 
LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)
148 
UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID
149 
END
150 
ELSE
151 
BEGIN
152 
SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)
153 
UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID
154 
END
155 
END
156 
ELSE
157 
BEGIN
158 
IF(EXISTS( SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD
159 
LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID
160 
LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID
161 
WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID))
162 
BEGIN
163 
IF(@TOTAL_AMT <= @LIMIT_DVCM)
164 
BEGIN
165 
SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT
166 
LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)
167 
UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID
168 
END
169 
ELSE
170 
BEGIN
171 
SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)
172 
UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID
173 
END
174 
END
175 
ELSE
176 
BEGIN
177 
IF(@TOTAL_AMT<=@LIMIT_VALUE)
178 
BEGIN
179 
SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)
180 
UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID
181 
END
182 
ELSE
183 
BEGIN
184 
SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)
185 
UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID
186 
END
187 
END
188 
END
189
190
191
192
193
194 
--IF(EXISTS( SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD
195 
-- LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID
196 
-- LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID
197 
-- WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID) OR @BRANCH_CREATE=@BRANCH_HS)
198 
-- BEGIN
199 
-- IF(@TOTAL_AMT<=@LIMIT_VALUE AND NOT EXISTS(SELECT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT
200 
-- LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID
201 
-- GROUP BY HC.DVCM_ID HAVING COUNT(REQDT_ID)>1))
202 
-- BEGIN
203 
-- SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT
204 
-- LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID)
205 
-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID
206 
-- END
207 
-- ELSE
208 
-- BEGIN
209 
-- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)
210 
-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID
211 
-- END
212 
-- END
213 
-- ELSE
214 
-- BEGIN
215 
-- IF(@TOTAL_AMT<=@LIMIT_VALUE)
216 
-- BEGIN
217 
-- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE)
218 
-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID
219 
-- END
220 
-- ELSE
221 
-- BEGIN
222 
-- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS)
223 
-- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID
224 
-- END
225 
-- END
226
227
228 
INSERT INTO dbo.PL_REQUEST_PROCESS
229 
(
230 
REQ_ID,
231 
PROCESS_ID,
232 
STATUS,
233 
ROLE_USER,
234 
BRANCH_ID,
235 
CHECKER_ID,
236 
APPROVE_DT,
237 
PARENT_PROCESS_ID,
238 
IS_LEAF,
239 
COST_ID,
240 
DVDM_ID,
241 
NOTES,
242 
IS_HAS_CHILD,
243 
DEP_ID
244 
)
245 
VALUES
246 
( @p_REQ_ID, -- REQ_ID - varchar(15)
247 
'APPNEW', -- PROCESS_ID - varchar(10)
248 
'P', -- STATUS - varchar(5)
249 
'GDDV', -- ROLE_USER - varchar(50)
250 
@BRANCH_CREATE, -- BRANCH_ID - varchar(15)
251 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
252 
GETDATE(), -- APPROVE_DT - datetime
253 
'', -- PARENT_PROCESS_ID - varchar(10)
254 
'', -- IS_LEAF - varchar(1)
255 
'', -- COST_ID - varchar(15)
256 
'', -- DVDM_ID - varchar(15)
257 
N'Tr??ng ?n v? ph? duy?t', -- NOTES - nvarchar(500)
258 
0 , -- IS_HAS_CHILD - bit
259 
@DEP_CREATE
260 
)
261
262 
INSERT INTO dbo.PL_REQUEST_PROCESS
263 
(
264 
REQ_ID,
265 
PROCESS_ID,
266 
STATUS,
267 
ROLE_USER,
268 
BRANCH_ID,
269 
CHECKER_ID,
270 
APPROVE_DT,
271 
PARENT_PROCESS_ID,
272 
IS_LEAF,
273 
COST_ID,
274 
DVDM_ID,
275 
NOTES,
276 
IS_HAS_CHILD
277 
)
278 
VALUES
279 
( @p_REQ_ID, -- REQ_ID - varchar(15)
280 
'DMMS', -- PROCESS_ID - varchar(10)
281 
'C', -- STATUS - varchar(5)
282 
'GDDV', -- ROLE_USER - varchar(50)
283 
'', -- BRANCH_ID - varchar(15)
284 
'', -- CHECKER_ID - varchar(15)
285 
GETDATE(), -- APPROVE_DT - datetime
286 
'APPNEW', -- PARENT_PROCESS_ID - varchar(10)
287 
'', -- IS_LEAF - varchar(1)
288 
'', -- COST_ID - varchar(15)
289 
'', -- DVDM_ID - varchar(15)
290 
N'Ch? ?u m?i mua s?m x? l?', -- NOTES - nvarchar(500)
291 
1 -- IS_HAS_CHILD - bit
292 
)
293
294 
INSERT INTO dbo.PL_PROCESS
295 
(
296 
REQ_ID,
297 
PROCESS_ID,
298 
CHECKER_ID,
299 
APPROVE_DT,
300 
PROCESS_DESC,NOTES
301 
)
302 
VALUES
303 
( @p_REQ_ID, -- REQ_ID - varchar(15)
304 
'APPNEW', -- PROCESS_ID - varchar(10)
305 
@p_CHECKER_ID, -- CHECKER_ID - varchar(15)
306 
@p_APPROVE_DT , -- APPROVE_DT - datetime
307 
@p_PROCESS_DES,
308 
N'Tr??ng ?n v? ph? duy?t' -- PROCESS_DESC - nvarchar(1000)
309 
)
310 
END
311 
IF @@Error <> 0 GOTO ABORT
312
313 
COMMIT TRANSACTION
314 
IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))
315 
BEGIN
316 
SELECT '0' as Result, '' ErrorDesc
317 
RETURN '0'
318 
END
319 
ELSE
320 
BEGIN
321 
SELECT '4' as Result, N'Phi?u y?u c?u mua s?m s?: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' ? ??c ?i c?p ph? duy?t trung gian x?c nh?n th?nh c?ng. Vui l?ng ?i tr??ng ?n v? ph? duy?t' ErrorDesc
322 
RETURN '4'
323 
END
324 
ABORT:
325 
BEGIN
326 
PRINT 'ERROR'
327 
ROLLBACK TRANSACTION
328 
SELECT '-1' as Result, '' ErrorDesc
329 
RETURN '-1'
330 
End
331
332 
Result ErrorDesc
333 
SUCCESSFULL
334