ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_Upd
@P_INVENT_ID VARCHAR(15) = NULL,
@p_INVENTORY_DT	VARCHAR(20) = NULL,
@p_TERM	nvarchar(20)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_DEPT_ID	varchar(15)  = NULL,
@p_NOTES	NVARCHAR(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_AUTH_STATUS_DVKD	varchar(1)  = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@P_INVENTDETAILS XML = NULL,
@p_BRANCH_CREATE	VARCHAR(15) = NULL,
@p_PARTYDETAILS XML = NULL,
@p_UNSTOCKEDDETAILS XML = NULL,
@p_SIGN_USER VARCHAR(20) = NULL
AS
--Validation is here
/*
DECLARE @ERRORSYS NVARCHAR(15) = '' 
  IF ( NOT EXISTS ( SELECT * FROM ASS_INVENTORY_MASTER WHERE ))
	 SET @ERRORSYS = ''
IF @ERRORSYS <> '' 
BEGIN
	SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '0'
END 
*/
IF (SELECT AUTH_STATUS FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID) = 'A'
BEGIN
	SELECT '-1' as Result, '' INVENT_ID, N'Thông tin đã được duyệt nên không được phép chỉnh sửa!' ErrorDesc
	return '-1'
END
IF (EXISTS(SELECT * FROM ASS_INVENTORY_MASTER aim WHERE aim.BRANCH_ID = @p_BRANCH_ID
  AND aim.INVENT_ID <> @P_INVENT_ID
  AND aim.TERM = @p_TERM AND aim.INVENTORY_DT = CONVERT(DATETIME, @p_INVENTORY_DT, 103)))
BEGIN
  SELECT '-1' as Result, '' INVENT_ID, N'Đơn vị đã tồn tại kỳ kiểm kê có cùng ngày và đợt kiểm kê.' ErrorDesc
  RETURN '-1'
END
IF (EXISTS(SELECT * FROM ASS_INVENTORY_MASTER aim 
  WHERE aim.BRANCH_ID = @p_BRANCH_ID
    AND aim.INVENT_ID <> @P_INVENT_ID
    AND (aim.AUTH_STATUS <> 'A' OR aim.AUTH_STATUS IS NULL OR aim.AUTH_STATUS_DVKD <> 'A' OR aim.AUTH_STATUS_DVKD IS NULL)))
BEGIN
  SELECT '-1' as Result, '' INVENT_ID, N'Đơn vị có kỳ kiểm kê chưa hoàn thành nên không thể tạo thêm kỳ kiểm kê mới.' ErrorDesc
  RETURN '-1'
END

	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@P_INVENTDETAILS
	DECLARE InventDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/InventDetail',2)
	WITH 
	(
		INVENTDT_ID VARCHAR(15),
		ASSET_ID	varchar(15)  ,
		ASSET_STATUS	nvarchar(20) ,
		INVENT_DESC	nvarchar(1000) ,
		NOTES nvarchar(500),
		BRANCH_USE	varchar(15) ,
		DEPT_USE	varchar(15) ,
		REMAIN_VALUE decimal(18,0)
	)
	OPEN InventDetail

	-- GiaNT 23/08/2021
	Declare @hdocParty INT
	Exec sp_xml_preparedocument @hdocParty Output,@p_PARTYDETAILS
		DECLARE PartyDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hdocParty,'/Root/PartyDetail',2)
	WITH 
	(
		PARTY_ID		VARCHAR(15),
		INVENT_ID		VARCHAR(15),
		PARTY_NAME		NVARCHAR(100),
		PARTY_ROLE		NVARCHAR(100),
		IS_RECIVE_MAIL		NVARCHAR(100),
		IS_DONE		NVARCHAR(100),
		IS_MAIN	NVARCHAR(1)


	)
	OPEN PartyDetail
	
	Declare @hdocUnstocked INT
	Exec sp_xml_preparedocument @hdocUnstocked Output,@p_UNSTOCKEDDETAILS
		DECLARE UnstockedDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hdocUnstocked,'/Root/UnstockedDetail',2)
	WITH 
	(
		UNSTOCKED_ID	VARCHAR(15),
		INVENT_ID		VARCHAR(15),
		ASS_NAME		NVARCHAR(1000),
		SERIAL			NVARCHAR(MAX),
		BRANCH_ID		NVARCHAR(50),
		DEPT_ID			NVARCHAR(50),
		USE_DATE		VARCHAR(20)
	)
	OPEN UnstockedDetail
	
BEGIN TRANSACTION

	IF @p_APPROVE_DT = '' SET @p_APPROVE_DT = NULL
	
	UPDATE ASS_INVENTORY_MASTER SET [INVENTORY_DT] = CONVERT(DATETIME, @p_INVENTORY_DT, 103),[TERM] = @p_TERM,[BRANCH_ID] = @p_BRANCH_ID,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[AUTH_STATUS_DVKD] = @p_AUTH_STATUS_DVKD,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),
	BRANCH_CREATE=@p_BRANCH_CREATE,SIGN_USER=@p_SIGN_USER
	WHERE  INVENT_ID= @p_INVENT_ID
	IF @@Error <> 0 GOTO ABORT
	
	DELETE FROM ASS_INVENTORY_DT WHERE INVENT_ID = @P_INVENT_ID 
	Declare @INVENTDT_ID VARCHAR(15),@ASSET_ID varchar(15), @ASSET_STATUS nvarchar(20),@INVENT_DESC nvarchar(1000), 
	@NOTES nvarchar(500), @DEPT_USE VARCHAR(15), @BRANCH_USE VARCHAR(15), @REMAIN_VALUE decimal(18,0)
	FETCH NEXT FROM InventDetail INTO @INVENTDT_ID,@ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE
	--------THIEUVQ THEM PHAN DEPT_ID CHO DOT KIEM KE, TAM THOI LAY THEO TAI SAN DAU TIEN TRONG BANG INVENTORY_DT
	IF @p_BRANCH_ID = 'DV0001'
	BEGIN
		UPDATE ASS_INVENTORY_MASTER SET DEPT_ID = @DEPT_USE WHERE INVENT_ID = @P_INVENT_ID
	END
	--------
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			IF(LEN (@INVENTDT_ID) = 0 )
			BEGIN
				EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT', @INVENTDT_ID out
				IF @INVENTDT_ID ='' OR @INVENTDT_ID IS NULL GOTO ABORT
			END
			INSERT INTO ASS_INVENTORY_DT([INVENTDT_ID],[INVENT_ID],[ASSET_ID],[ASSET_STATUS],[INVENT_DESC], [NOTES], [BRANCH_USE], [DEPT_USE], [REMAIN_VALUE])
			VALUES(@INVENTDT_ID,@P_INVENT_ID,@ASSET_ID,@ASSET_STATUS,@INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE)
		-- next Group_Id
			IF @@ERROR <> 0 GOTO ABORT
			FETCH NEXT FROM InventDetail INTO @INVENTDT_ID,@ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE
		END
	CLOSE InventDetail
	DEALLOCATE InventDetail


	-- GiaNT 23/08/2021
	DELETE FROM ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @P_INVENT_ID
	
	DECLARE		
		@PARTY_ID			VARCHAR(15),
		@INVENT_ID			VARCHAR(15),
		@PARTY_NAME			NVARCHAR(200),
		@PARTY_ROLE			NVARCHAR(200),
		@IS_RECIVE_MAIL		VARCHAR(1),
		@IS_DONE		VARCHAR(1),
		@IS_MAIN		VARCHAR(1)


	FETCH NEXT FROM PartyDetail INTO  @PARTY_ID,@INVENT_ID,@PARTY_NAME,@PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN
		WHILE @@FETCH_STATUS = 0
		BEGIN			
			DECLARE @l_PARTY_ID VARCHAR(15)	
			EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_PARTY_DT', @l_PARTY_ID out
			IF @l_PARTY_ID='' OR @l_PARTY_ID IS NULL 
			BEGIN
				GOTO ABORT
			END

			INSERT INTO ASS_INVENTORY_PARTY_DT([PARTY_ID], [INVENT_ID], [PARTY_NAME], [PARTY_ROLE],[IS_RECIVE_MAIL],[IS_DONE],[IS_MAIN])
			VALUES(@l_PARTY_ID ,@p_INVENT_ID, @PARTY_NAME, @PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN)
			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM PartyDetail INTO  @PARTY_ID,@INVENT_ID,@PARTY_NAME,@PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN
		END
	CLOSE PartyDetail
	DEALLOCATE PartyDetail

	DELETE FROM dbo.ASS_INVENTORY_UNSTOCKED WHERE INVENT_ID = @P_INVENT_ID

	DECLARE
		@UNSTOCKED_ID	VARCHAR(15),
		@ASS_NAME		NVARCHAR(1000),
		@SERIAL			NVARCHAR(MAX),
		@BRANCH_ID		NVARCHAR(50),
		@DEPT_ID		NVARCHAR(50),	
		@USE_DATE		VARCHAR(20)

	FETCH NEXT FROM UnstockedDetail INTO @UNSTOCKED_ID,@INVENT_ID,@ASS_NAME,@SERIAL,@BRANCH_ID,@DEPT_ID,@USE_DATE
		WHILE @@FETCH_STATUS = 0
		BEGIN			
			DECLARE @l_UNSTOCKED_ID VARCHAR(15)	
			EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_UNSTOCKED', @l_UNSTOCKED_ID out
			IF @l_UNSTOCKED_ID='' OR @l_UNSTOCKED_ID IS NULL 
			BEGIN
				GOTO ABORT
			END

			INSERT INTO ASS_INVENTORY_UNSTOCKED([UNSTOCKED_ID], [INVENT_ID], [ASS_NAME], [SERIAL],[BRANCH_ID],[DEPT_ID],[USE_DATE])
			VALUES(@l_UNSTOCKED_ID ,@p_INVENT_ID, @ASS_NAME, @SERIAL,@BRANCH_ID,@DEPT_ID,CONVERT(DATETIME,@USE_DATE,103))
			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM UnstockedDetail INTO  @UNSTOCKED_ID,@INVENT_ID,@ASS_NAME,@SERIAL,@BRANCH_ID,@DEPT_ID,@USE_DATE
		END
	CLOSE UnstockedDetail
	DEALLOCATE UnstockedDetail

COMMIT TRANSACTION
SELECT '0' as Result, @P_INVENT_ID  INVENT_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE InventDetail
		DEALLOCATE InventDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' INVENT_ID, '' ErrorDesc
		RETURN '-1'
End