|
1
|
|
|
2
|
|
|
3
|
ALTER PROCEDURE [dbo].[CM_GOODS_Search]
|
|
4
|
@p_GD_CODE varchar(100) = NULL,
|
|
5
|
@p_GD_NAME nvarchar(200) = NULL,
|
|
6
|
@p_GD_TYPE_ID varchar(15) = NULL,
|
|
7
|
@p_DESCRIPTION nvarchar(2000) = NULL,
|
|
8
|
@p_SUP_ID varchar(15) = NULL,
|
|
9
|
@p_PRICE decimal(18) = NULL,
|
|
10
|
@p_UNIT_ID varchar(15) = NULL,
|
|
11
|
@p_NOTES nvarchar(1000) = NULL,
|
|
12
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
13
|
@p_MAKER_ID varchar(12) = NULL,
|
|
14
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
15
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
16
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
17
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
18
|
@P_TOP INT = 1000
|
|
19
|
AS
|
|
20
|
BEGIN
|
|
21
|
IF(@P_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
|
|
22
|
SELECT A.*, B.UNIT_CODE, B.UNIT_NAME, ALLCODE.CONTENT AS GD_TYPE_NAME_ALLCODE, C.GD_TYPE_NAME, C.GD_TYPE_CODE, D.SUP_CODE, D.SUP_NAME, '' CD_ID--Column all NULL
|
|
23
|
FROM CM_GOODS A
|
|
24
|
left join CM_UNIT B on A.UNIT_ID = B.UNIT_ID
|
|
25
|
left join CM_GOODSTYPE C on A.GD_TYPE_ID = C.GD_TYPE_ID
|
|
26
|
left join CM_SUPPLIER D on D.SUP_ID = A.SUP_ID
|
|
27
|
left join CM_ALLCODE ALLCODE on ALLCODE.CDVAL = A.GD_TYPE_ID AND ALLCODE.CDNAME = 'HMNS_TYPE'
|
|
28
|
WHERE 1=1
|
|
29
|
AND (A.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
|
|
30
|
AND (A.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
|
|
31
|
AND (A.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
|
|
32
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
33
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
34
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
35
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
36
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
37
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
38
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
39
|
AND A.RECORD_STATUS = '1'
|
|
40
|
ELSE
|
|
41
|
SELECT TOP(@P_TOP)A.*, B.UNIT_CODE, B.UNIT_NAME, ALLCODE.CONTENT AS GD_TYPE_NAME_ALLCODE, C.GD_TYPE_NAME, C.GD_TYPE_CODE, D.SUP_CODE, D.SUP_NAME, '' CD_ID--Column all NULL
|
|
42
|
FROM CM_GOODS A
|
|
43
|
left join CM_UNIT B on A.UNIT_ID = B.UNIT_ID
|
|
44
|
left join CM_GOODSTYPE C on A.GD_TYPE_ID = C.GD_TYPE_ID
|
|
45
|
left join CM_SUPPLIER D on D.SUP_ID = A.SUP_ID
|
|
46
|
left join CM_ALLCODE ALLCODE on ALLCODE.CDVAL = A.GD_TYPE_ID AND ALLCODE.CDNAME = 'HMNS_TYPE'
|
|
47
|
WHERE 1=1
|
|
48
|
AND (A.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
|
|
49
|
AND (A.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
|
|
50
|
AND (A.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
|
|
51
|
AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
52
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
53
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
54
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
55
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
56
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
57
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
58
|
AND A.RECORD_STATUS = '1'
|
|
59
|
END
|
|
60
|
--EXEC CM_GOODS_Search '','','','','',NULL,'','','','','','','','',''
|
|
61
|
|
|
62
|
|
|
63
|
|
|
64
|
|
|
65
|
|
|
66
|
|
|
67
|
|
|
68
|
ALTER PROCEDURE [dbo].[CM_GOODS_Upd]
|
|
69
|
@p_GD_ID varchar(15) = null ,
|
|
70
|
@p_GD_CODE varchar(100) = NULL ,
|
|
71
|
@p_GD_NAME nvarchar(200) = NULL ,
|
|
72
|
@p_GD_TYPE_ID varchar(15) = NULL ,
|
|
73
|
@p_DESCRIPTION nvarchar(2000) = NULL ,
|
|
74
|
@p_SUP_ID varchar(15) = NULL ,
|
|
75
|
@p_PRICE decimal(17) = NULL ,
|
|
76
|
@p_UNIT_ID varchar(15) = NULL ,
|
|
77
|
@p_NOTES nvarchar(1000) = NULL ,
|
|
78
|
@p_RECORD_STATUS varchar(1) = NULL ,
|
|
79
|
@p_MAKER_ID varchar(12) = NULL ,
|
|
80
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
81
|
@p_AUTH_STATUS varchar(50) = NULL ,
|
|
82
|
@p_CHECKER_ID varchar(12) = NULL ,
|
|
83
|
@p_APPROVE_DT VARCHAR(20) = NULL
|
|
84
|
AS
|
|
85
|
BEGIN TRANSACTION
|
|
86
|
UPDATE CM_GOODS SET [GD_CODE] = @p_GD_CODE,[GD_NAME] = @p_GD_NAME,[GD_TYPE_ID] = @p_GD_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)
|
|
87
|
WHERE GD_ID= @p_GD_ID
|
|
88
|
IF @@Error <> 0 GOTO ABORT
|
|
89
|
COMMIT TRANSACTION
|
|
90
|
SELECT 0 as Result, @p_GD_ID GD_ID, '' ErrorDesc
|
|
91
|
RETURN 0
|
|
92
|
ABORT:
|
|
93
|
BEGIN
|
|
94
|
ROLLBACK TRANSACTION
|
|
95
|
SELECT -1 as Result, '' GD_ID, '' ErrorDesc
|
|
96
|
RETURN -1
|
|
97
|
End
|
|
98
|
|
|
99
|
|
|
100
|
|
|
101
|
|
|
102
|
|
|
103
|
|
|
104
|
|
|
105
|
ALTER PROCEDURE [dbo].[CM_GOODS_Ins]
|
|
106
|
@p_GD_CODE varchar(100) = NULL,
|
|
107
|
@p_GD_NAME nvarchar(200) = NULL,
|
|
108
|
@p_GD_TYPE_ID varchar(15) = NULL,
|
|
109
|
@p_DESCRIPTION nvarchar(2000) = NULL,
|
|
110
|
@p_SUP_ID varchar(15) = NULL,
|
|
111
|
@p_PRICE decimal(18) = NULL,
|
|
112
|
@p_UNIT_ID varchar(15) = NULL,
|
|
113
|
@p_NOTES nvarchar(1000) = NULL,
|
|
114
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
115
|
@p_MAKER_ID varchar(12) = NULL,
|
|
116
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
117
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
118
|
@p_CHECKER_ID varchar(12) = NULL,
|
|
119
|
@p_APPROVE_DT VARCHAR(20) = NULL
|
|
120
|
AS
|
|
121
|
DECLARE @l_GD_ID VARCHAR(15)
|
|
122
|
|
|
123
|
BEGIN TRANSACTION
|
|
124
|
|
|
125
|
EXEC SYS_CodeMasters_Gen 'CM_GOODS', @l_GD_ID out
|
|
126
|
IF @l_GD_ID='' OR @l_GD_ID IS NULL GOTO ABORT
|
|
127
|
|
|
128
|
INSERT INTO CM_GOODS([GD_ID],[GD_CODE],[GD_NAME],[GD_TYPE_ID],[DESCRIPTION],[SUP_ID],[PRICE],[UNIT_ID],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
|
|
129
|
VALUES(@l_GD_ID ,@p_GD_CODE ,@p_GD_NAME ,@p_GD_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) )
|
|
130
|
IF @@Error <> 0 GOTO ABORT
|
|
131
|
|
|
132
|
COMMIT TRANSACTION
|
|
133
|
SELECT 0 as Result, @l_GD_ID GD_ID, '' ErrorDesc
|
|
134
|
RETURN 0
|
|
135
|
ABORT:
|
|
136
|
BEGIN
|
|
137
|
ROLLBACK TRANSACTION
|
|
138
|
SELECT -1 as Result, '' GD_ID, '' ErrorDesc
|
|
139
|
RETURN -1
|
|
140
|
End
|
|
141
|
|
|
142
|
|
|
143
|
|
|
144
|
|
|
145
|
|
|
146
|
|