ALTER PROCEDURE [dbo].[CM_GOODS_Search]
	@p_GD_CODE	varchar(100)  = NULL,
	@p_GD_NAME	nvarchar(200)  = NULL,
	@p_GD_TYPE_ID	varchar(15)  = NULL,
	@p_DESCRIPTION	nvarchar(2000)  = NULL,
	@p_SUP_ID	varchar(15)  = NULL,
	@p_PRICE	decimal(18)  = NULL,
	@p_UNIT_ID	varchar(15)  = NULL,
	@p_NOTES	nvarchar(1000)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@P_TOP	INT = 1000
AS
BEGIN
	IF(@P_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
		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
		FROM CM_GOODS A
		left join CM_UNIT B on A.UNIT_ID = B.UNIT_ID
		left join CM_GOODSTYPE C on A.GD_TYPE_ID = C.GD_TYPE_ID 
		left join CM_SUPPLIER D on D.SUP_ID = A.SUP_ID
		left join CM_ALLCODE ALLCODE on ALLCODE.CDVAL = A.GD_TYPE_ID AND ALLCODE.CDNAME = 'HMNS_TYPE'
		WHERE 1=1
	    AND (A.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
		AND (A.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
		AND (A.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND A.RECORD_STATUS = '1'
   ELSE
		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
		FROM CM_GOODS A
		left join CM_UNIT B on A.UNIT_ID = B.UNIT_ID
		left join CM_GOODSTYPE C on A.GD_TYPE_ID = C.GD_TYPE_ID
		left join CM_SUPPLIER D on D.SUP_ID = A.SUP_ID
		left join CM_ALLCODE ALLCODE on ALLCODE.CDVAL = A.GD_TYPE_ID AND ALLCODE.CDNAME = 'HMNS_TYPE'
		WHERE 1=1
	    AND (A.GD_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
		AND (A.GD_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
		AND (A.GD_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND A.RECORD_STATUS = '1'		
END
--EXEC CM_GOODS_Search '','','','','',NULL,'','','','','','','','',''







ALTER PROCEDURE [dbo].[CM_GOODS_Upd]
@p_GD_ID	varchar(15) = null ,
@p_GD_CODE	varchar(100) = NULL ,
@p_GD_NAME	nvarchar(200) = NULL ,
@p_GD_TYPE_ID	varchar(15) = NULL ,
@p_DESCRIPTION	nvarchar(2000) = NULL ,
@p_SUP_ID	varchar(15) = NULL ,
@p_PRICE	decimal(17) = NULL ,
@p_UNIT_ID	varchar(15) = NULL ,
@p_NOTES	nvarchar(1000) = NULL ,
@p_RECORD_STATUS	varchar(1) = NULL ,
@p_MAKER_ID	varchar(12) = NULL ,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50) = NULL ,
@p_CHECKER_ID	varchar(12) = NULL ,
@p_APPROVE_DT	VARCHAR(20) = NULL
AS
BEGIN TRANSACTION
		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)
WHERE  GD_ID= @p_GD_ID
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
		SELECT 0 as Result, @p_GD_ID  GD_ID, '' ErrorDesc
		RETURN 0
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 as Result, '' GD_ID, '' ErrorDesc
		RETURN -1
End







ALTER PROCEDURE [dbo].[CM_GOODS_Ins]
	@p_GD_CODE	varchar(100)  = NULL,
	@p_GD_NAME	nvarchar(200)  = NULL,
	@p_GD_TYPE_ID	varchar(15)  = NULL,
	@p_DESCRIPTION	nvarchar(2000)  = NULL,
	@p_SUP_ID	varchar(15)  = NULL,
	@p_PRICE	decimal(18)  = NULL,
	@p_UNIT_ID	varchar(15)  = NULL,
	@p_NOTES	nvarchar(1000)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL
AS
	DECLARE @l_GD_ID VARCHAR(15)
	
BEGIN TRANSACTION

		EXEC SYS_CodeMasters_Gen 'CM_GOODS', @l_GD_ID out
		IF @l_GD_ID='' OR @l_GD_ID IS NULL GOTO ABORT
	
		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])
		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) )
		IF @@Error <> 0 GOTO ABORT
		
COMMIT TRANSACTION
SELECT 0 as Result, @l_GD_ID  GD_ID, '' ErrorDesc
RETURN 0
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 as Result, '' GD_ID, '' ErrorDesc
		RETURN -1
End