Project

General

Profile

Download (21.9 KB)

TR_REQ_APP.txt

Luc Tran Van, 12/09/2020 11:03 AM

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