ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_Ins
@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_SIGN_USER	varchar(50)  = 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

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 
*/

	/****PGD KHONG DUOC PHEP THUC HIEN KIEM KE***/
	DECLARE @l_TYPE_CREATE VARCHAR(200)
	SET @l_TYPE_CREATE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_CREATE)
	IF  @l_TYPE_CREATE <> 'CN' AND @l_TYPE_CREATE <> 'HS'
	BEGIN
		SELECT '-1' as Result, '' INVENT_ID, N'Đơn vị không được phép tạo đợ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.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.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 
	(
		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		VARCHAR(100),
		IS_DONE		VARCHAR(100),
		IS_MAIN		VARCHAR(100)
	)
	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

	DECLARE @l_INVENT_ID VARCHAR(15)
	EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_MASTER', @l_INVENT_ID out
	IF @l_INVENT_ID='' OR @l_INVENT_ID IS NULL GOTO ABORT
	INSERT INTO ASS_INVENTORY_MASTER([INVENT_ID],[INVENTORY_DT],[TERM],[BRANCH_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS_DVKD],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[BRANCH_CREATE],[SIGN_USER],[DEPT_ID],[AUTH_STATUS])
	VALUES(@l_INVENT_ID ,CONVERT(DATETIME, @p_INVENTORY_DT, 103) ,@p_TERM ,@p_BRANCH_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS_DVKD ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_BRANCH_CREATE,@p_SIGN_USER,@p_DEPT_ID,'E')
	IF @@Error <> 0 GOTO ABORT
	
	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 @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 = @l_INVENT_ID
	--END
	--------
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT', @INVENTDT_ID out
			IF @INVENTDT_ID ='' OR @INVENTDT_ID IS NULL GOTO ABORT
			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,@l_INVENT_ID,@ASSET_ID,@ASSET_STATUS,@INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE)
			-- PHONGNT 7/7/2022 Thêm vào ASS_INVENTORY_DT_RPT dùng cho app kiểm kê
			INSERT INTO ASS_INVENTORY_DT_RPT([INVENTDT_ID],[INVENT_ID],[ASSET_ID],[ASSET_STATUS],[INVENT_DESC], [NOTES], [BRANCH_USE], [DEPT_USE], [REMAIN_VALUE])
			VALUES(@INVENTDT_ID,@l_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 @ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE
		END
	CLOSE InventDetail
	DEALLOCATE InventDetail

	-- GiaNT 23/08/2021	
	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 ,@l_INVENT_ID, @PARTY_NAME, @PARTY_ROLE,@IS_RECIVE_MAIL,'0',@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

	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 ,@l_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, @l_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