|
1
|
DECLARE @REQ_ID VARCHAR(15)
|
|
2
|
SET @REQ_ID = (SELECT REQ_ID FROM PL_REQUEST_DOC WHERE REQ_CODE ='0002/2021/TTr-0690903')
|
|
3
|
EXEC dbo.PL_REQUEST_PROCESS_App @p_REQ_ID = @REQ_ID, -- varchar(15)
|
|
4
|
@p_AUTH_STATUS = 'A', -- varchar(1)
|
|
5
|
@p_CHECKER_ID = 'taila', -- varchar(15)
|
|
6
|
@p_APPROVE_DT = '2021-03-21 8:15:56 AM', -- datetime
|
|
7
|
@p_ROLE_LOGIN = 'HQDT', -- varchar(50)
|
|
8
|
@p_BRANCH_LOGIN = 'DV0001', -- varchar(15)
|
|
9
|
@p_PROCESS_DESC = N'Phê duyệt' -- nvarchar(max)
|
|
10
|
|
|
11
|
¿
|
|
12
|
|
|
13
|
ALTER PROCEDURE [dbo].[CM_HANGHOA_Upd]
|
|
14
|
@p_HH_ID varchar(15) = null ,
|
|
15
|
@p_HH_CODE varchar(15) = NULL ,
|
|
16
|
@p_HH_NAME nvarchar(200) = NULL ,
|
|
17
|
@p_HH_TYPE_ID varchar(15) = NULL ,
|
|
18
|
@p_DESCRIPTION nvarchar(2000) = NULL ,
|
|
19
|
@p_SUP_ID varchar(15) = NULL ,
|
|
20
|
@p_PRICE decimal(17) = NULL ,
|
|
21
|
@p_UNIT_ID varchar(15) = NULL ,
|
|
22
|
@p_NOTES nvarchar(1000) = NULL ,
|
|
23
|
@p_RECORD_STATUS varchar(1) = NULL ,
|
|
24
|
@p_MAKER_ID varchar(12) = NULL ,
|
|
25
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
26
|
@p_AUTH_STATUS varchar(50) = NULL ,
|
|
27
|
@p_CHECKER_ID varchar(12) = NULL ,
|
|
28
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
29
|
@p_GD_ID varchar(15) = null,
|
|
30
|
@p_GROUP_ID_TTCT varchar(15) = NULL,
|
|
31
|
@p_GROUP_ID_CDT varchar(15) = NULL,
|
|
32
|
@p_DVCM_ID varchar(15) = NULL
|
|
33
|
|
|
34
|
AS
|
|
35
|
BEGIN TRANSACTION
|
|
36
|
|
|
37
|
declare @OLD_DVCM_ID NVARCHAR(15) = NULL
|
|
38
|
SELECT @OLD_DVCM_ID = DVCM_ID FROM CM_HANGHOA_DVCM WHERE HH_ID = @p_HH_ID
|
|
39
|
|
|
40
|
--kIEM TRA MA LA DUY NHAT
|
|
41
|
IF EXISTS (SELECT * FROM CM_HANGHOA WHERE HH_CODE = @p_HH_CODE AND HH_ID <> @p_HH_ID)
|
|
42
|
BEGIN
|
|
43
|
ROLLBACK TRANSACTION
|
|
44
|
SELECT -1 as Result, '' HH_ID, N'Mã hàng hóa đã tồn tại trong hệ thống' ErrorDesc
|
|
45
|
RETURN -1
|
|
46
|
END
|
|
47
|
--kIEM TRA MA LA DUY NHAT
|
|
48
|
IF(@p_HH_CODE IS NULL OR @p_HH_CODE ='')
|
|
49
|
BEGIN
|
|
50
|
ROLLBACK TRANSACTION
|
|
51
|
SELECT -1 as Result, '' HH_ID, N'Mã hàng hóa chưa tự động phát sinh. Vui lòng nhập mã hàng hóa' ErrorDesc
|
|
52
|
RETURN -1
|
|
53
|
END
|
|
54
|
IF(@p_MAKER_ID NOT IN ('vanpt2', 'quanvta','phuongvn','cuongvd','tuyenlt','tanvt','tungnt4'))
|
|
55
|
BEGIN
|
|
56
|
ROLLBACK TRANSACTION
|
|
57
|
SELECT -1 as Result, '' HH_ID, N'Bạn không nằm trong danh sách những user được phép thêm mới hàng hóa. Vui lòng liên hệ bộ phận Mua Hàng - Phòng Hành Chính Hội Sở' ErrorDesc
|
|
58
|
RETURN -1
|
|
59
|
END
|
|
60
|
UPDATE CM_HANGHOA SET [HH_CODE] = @p_HH_CODE,[HH_NAME] = @p_HH_NAME,[HH_TYPE_ID] = @p_HH_TYPE_ID,[DESCRIPTION] = @p_DESCRIPTION,[SUP_ID] = @p_SUP_ID,[PRICE] = @p_PRICE,[UNIT_ID] = @p_UNIT_ID,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103), [GD_ID] = @p_GD_ID
|
|
61
|
WHERE HH_ID = @p_HH_ID
|
|
62
|
|
|
63
|
DELETE SYS_HH_GROUP_LIMIT WHERE HH_ID = @p_HH_ID
|
|
64
|
INSERT INTO SYS_HH_GROUP_LIMIT(HH_ID, GROUP_LIMIT_ID, TYPE_LIMIT)
|
|
65
|
VALUES (@p_HH_ID, @p_GROUP_ID_TTCT, 'TTCT')
|
|
66
|
INSERT INTO SYS_HH_GROUP_LIMIT(HH_ID, GROUP_LIMIT_ID, TYPE_LIMIT)
|
|
67
|
VALUES (@p_HH_ID, @p_GROUP_ID_CDT, 'CDT')
|
|
68
|
DELETE CM_HANGHOA_DVCM WHERE HH_ID = @p_HH_ID AND DVCM_ID = @OLD_DVCM_ID
|
|
69
|
INSERT INTO CM_HANGHOA_DVCM(HH_ID, DVCM_ID)
|
|
70
|
VALUES (@p_HH_ID, @p_DVCM_ID)
|
|
71
|
|
|
72
|
IF @@Error <> 0 GOTO ABORT
|
|
73
|
COMMIT TRANSACTION
|
|
74
|
SELECT 0 as Result, @p_HH_ID HH_ID, '' ErrorDesc
|
|
75
|
RETURN 0
|
|
76
|
ABORT:
|
|
77
|
BEGIN
|
|
78
|
ROLLBACK TRANSACTION
|
|
79
|
SELECT -1 as Result, '' HH_ID, N'Cập nhật thất bại' ErrorDesc
|
|
80
|
RETURN -1
|
|
81
|
End
|
|
82
|
|
|
83
|
¿
|
|
84
|
|
|
85
|
ALTER PROCEDURE [dbo].[CM_HANGHOA_Ins]
|
|
86
|
@p_HH_CODE varchar(50) = NULL,
|
|
87
|
@p_HH_NAME nvarchar(200) = NULL,
|
|
88
|
@p_HH_TYPE_ID varchar(15) = NULL,
|
|
89
|
@p_DESCRIPTION nvarchar(2000) = NULL,
|
|
90
|
@p_SUP_ID varchar(15) = NULL,
|
|
91
|
@p_PRICE decimal(18) = NULL,
|
|
92
|
@p_UNIT_ID varchar(15) = NULL,
|
|
93
|
@p_NOTES nvarchar(1000) = NULL,
|
|
94
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
95
|
@p_MAKER_ID varchar(15) = NULL,
|
|
96
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
97
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
98
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
99
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
100
|
@p_GD_ID varchar(15) = NULL,
|
|
101
|
@p_GROUP_ID_TTCT varchar(15) = NULL,
|
|
102
|
@p_GROUP_ID_CDT varchar(15) = NULL,
|
|
103
|
@p_DVCM_ID varchar(15) = NULL
|
|
104
|
AS
|
|
105
|
DECLARE @l_HH_ID VARCHAR(15)
|
|
106
|
|
|
107
|
BEGIN TRANSACTION
|
|
108
|
|
|
109
|
--kIEM TRA MA LA DUY NHAT
|
|
110
|
IF EXISTS (SELECT * FROM CM_HANGHOA WHERE HH_CODE = @p_HH_CODE)
|
|
111
|
BEGIN
|
|
112
|
ROLLBACK TRANSACTION
|
|
113
|
SELECT -1 as Result, '' HH_ID, N'Mã hàng hóa đã tồn tại trong hệ thống' ErrorDesc
|
|
114
|
RETURN -1
|
|
115
|
END
|
|
116
|
IF(@p_MAKER_ID NOT IN ('vanpt2', 'quanvta','phuongvn','cuongvd','tuyenlt','tanvt','tungnt4'))
|
|
117
|
BEGIN
|
|
118
|
ROLLBACK TRANSACTION
|
|
119
|
SELECT -1 as Result, '' HH_ID, N'Bạn không nằm trong danh sách những user được phép thêm mới hàng hóa. Vui lòng liên hệ bộ phận Mua Hàng - Phòng Hành Chính Hội Sở' ErrorDesc
|
|
120
|
RETURN -1
|
|
121
|
END
|
|
122
|
IF(@p_HH_CODE IS NULL OR @p_HH_CODE ='')
|
|
123
|
BEGIN
|
|
124
|
ROLLBACK TRANSACTION
|
|
125
|
SELECT -1 as Result, '' HH_ID, N'Mã hàng hóa chưa tự động phát sinh. Vui lòng nhập mã hàng hóa' ErrorDesc
|
|
126
|
RETURN -1
|
|
127
|
END
|
|
128
|
EXEC SYS_CodeMasters_Gen 'CM_HANGHOA', @l_HH_ID out
|
|
129
|
IF @l_HH_ID='' OR @l_HH_ID IS NULL GOTO ABORT
|
|
130
|
|
|
131
|
SET @p_AUTH_STATUS = 'A'
|
|
132
|
IF EXISTS (SELECT * FROM TL_MENU WHERE MENU_NAME_EL = N'hanghoa' and ISAPPROVE_FUNC = 1)
|
|
133
|
BEGIN
|
|
134
|
SET @p_AUTH_STATUS = 'U'
|
|
135
|
END
|
|
136
|
|
|
137
|
INSERT INTO CM_HANGHOA([HH_ID],[HH_CODE],[HH_NAME],[HH_TYPE_ID],[DESCRIPTION],[SUP_ID],[PRICE],[UNIT_ID],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[GD_ID])
|
|
138
|
VALUES(@l_HH_ID ,@p_HH_CODE ,@p_HH_NAME ,@p_HH_TYPE_ID ,@p_DESCRIPTION ,@p_SUP_ID ,@p_PRICE ,@p_UNIT_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_GD_ID)
|
|
139
|
|
|
140
|
DELETE SYS_HH_GROUP_LIMIT WHERE HH_ID = @l_HH_ID
|
|
141
|
INSERT INTO SYS_HH_GROUP_LIMIT(HH_ID, GROUP_LIMIT_ID, TYPE_LIMIT)
|
|
142
|
VALUES (@l_HH_ID, @p_GROUP_ID_TTCT, 'TTCT')
|
|
143
|
INSERT INTO SYS_HH_GROUP_LIMIT(HH_ID, GROUP_LIMIT_ID, TYPE_LIMIT)
|
|
144
|
VALUES (@l_HH_ID, @p_GROUP_ID_CDT, 'CDT')
|
|
145
|
DELETE CM_HANGHOA_DVCM WHERE HH_ID = @l_HH_ID AND DVCM_ID = @p_DVCM_ID
|
|
146
|
INSERT INTO CM_HANGHOA_DVCM(HH_ID, DVCM_ID)
|
|
147
|
VALUES (@l_HH_ID, @p_DVCM_ID)
|
|
148
|
|
|
149
|
IF @@Error <> 0 GOTO ABORT
|
|
150
|
|
|
151
|
COMMIT TRANSACTION
|
|
152
|
SELECT 0 as Result, @l_HH_ID HH_ID, '' ErrorDesc
|
|
153
|
RETURN 0
|
|
154
|
ABORT:
|
|
155
|
BEGIN
|
|
156
|
ROLLBACK TRANSACTION
|
|
157
|
SELECT -1 as Result, '' HH_ID, N'Thêm thất bại' ErrorDesc
|
|
158
|
RETURN -1
|
|
159
|
End
|
|
160
|
|
|
161
|
|
|
162
|
|
|
163
|
|
|
164
|
|
|
165
|
|
|
166
|
|
|
167
|
|