DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_App

@P_INVENT_ID VARCHAR(15),

@P_AUTH_STATUS VARCHAR(1),

@P_CHECKER_ID VARCHAR(15),

@P_APPROVE_DT VARCHAR(20),

@P_MESSAGE NVARCHAR(1000)

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, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

	RETURN '0'

END 

SELECT * FROM ASS_MASTER

SELECT * FROM ASS_INVENTORY_DT

*/

/****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 = (SELECT B.BRANCH_CREATE FROM ASS_INVENTORY_MASTER B WHERE B.INVENT_ID = @P_INVENT_ID))

	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 duyệt đợt kiểm kê.' ErrorDesc

		RETURN '-1'

	END

	IF EXISTS (

		SELECT A.ASSET_ID

		FROM ASS_INVENTORY_DT A

		WHERE A.INVENT_ID = @P_INVENT_ID AND A.ASSET_STATUS <> '1' AND (A.INVENT_DESC IS NULL OR A.INVENT_DESC = '')

	)

	BEGIN 

		SELECT '-1' as Result, '' INVENT_ID, N'Phải cập nhật hiện trạng cho các tài sản có tình trạng khác bình thường trước khi duyệt' ErrorDesc

		RETURN '-1'		

	END

BEGIN TRANSACTION

	DECLARE @l_ASSET_ID VARCHAR(15)

	DECLARE @l_ASSET_STATUS VARCHAR(10),@l_ASSET_STATUS_DESC NVARCHAR(1000),@l_ASSET_NOTES NVARCHAR(4000)

    DECLARE pCUR CURSOR FOR 

    	SELECT A.ASSET_ID,A.ASSET_STATUS,A.INVENT_DESC,A.NOTES

    	FROM ASS_INVENTORY_DT A

    	WHERE A.INVENT_ID = @P_INVENT_ID

    	--thieuvq them dieu kien khong cap nhat tai san THUA SO VOI SAO KE

    	AND A.ASSET_STATUS <> '6'

    	OPEN pCUR 

	-- GIANT XÁC NHẬN THÀNH PHẦN KIỂM KÊ

--	UPDATE ASS_INVENTORY_PARTY_DT SET APPROVE_DT = GETDATE()  

--	WHERE INVENT_ID = @P_INVENT_ID

	UPDATE ASS_INVENTORY_PARTY_DT SET COMMENT = @P_MESSAGE, IS_DONE = '1', CONFIRM_DT = GETDATE()

	WHERE INVENT_ID = @P_INVENT_ID AND IS_MAIN = '1' AND PARTY_NAME =@P_CHECKER_ID

	IF @@Error <> 0 GOTO ABORT

	DECLARE @PROCESS_ID_CUR VARCHAR(15) = (SELECT PROCESS_ID FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID)

	DECLARE @PROCESS_ID_NEXT VARCHAR(15) = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @P_INVENT_ID AND PARENT_PROCESS_ID = @PROCESS_ID_CUR)

	UPDATE dbo.ASS_INVENTORY_MASTER SET PROCESS_ID = @PROCESS_ID_NEXT WHERE INVENT_ID = @P_INVENT_ID

	UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'P',CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = GETDATE() WHERE REQ_ID = @P_INVENT_ID AND ROLE_USER = @P_CHECKER_ID AND PROCESS_ID = @PROCESS_ID_CUR

    IF(NOT EXISTS(SELECT 1 FROM ASS_INVENTORY_PARTY_DT aipd WHERE aipd.INVENT_ID=@P_INVENT_ID AND aipd.IS_MAIN='1' AND aipd.IS_DONE='0'))

    BEGIN

    	--CAP NHAT TRANG THAI TAI SAN SAU KHI KIEM KE	

    	FETCH NEXT FROM pCUR INTO @l_ASSET_ID,@l_ASSET_STATUS,@l_ASSET_STATUS_DESC,@l_ASSET_NOTES

    	WHILE @@FETCH_STATUS = 0

    	BEGIN

    		UPDATE ASS_MASTER SET ASS_STATUS = @l_ASSET_STATUS, ASS_STATUS_DESC = @l_ASSET_STATUS_DESC,NOTES= @l_ASSET_NOTES

    		WHERE ASSET_ID = @l_ASSET_ID

    		FETCH NEXT FROM pCUR INTO @l_ASSET_ID,@l_ASSET_STATUS,@l_ASSET_STATUS_DESC,@l_ASSET_NOTES

    	END

        UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @P_INVENT_ID AND PROCESS_ID = @PROCESS_ID_NEXT

        UPDATE ASS_INVENTORY_MASTER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

	    WHERE INVENT_ID = @P_INVENT_ID

    END

	-- GIANT 26/08/2021

	INSERT INTO dbo.PL_PROCESS

	(

		REQ_ID,

		PROCESS_ID,

		CHECKER_ID,

		APPROVE_DT,

		PROCESS_DESC,

		NOTES

	)

	VALUES

	(   @p_INVENT_ID,       

		'APPROVE',

		@P_CHECKER_ID,       

		GETDATE(), 

		N'Trưởng ban kiêm kê phê duyệt kiểm kê' ,     

		N'Phê duyệt thông tin kiêm kê'       

	)

CLOSE pCUR

DEALLOCATE pCUR

COMMIT TRANSACTION

SELECT '0' as Result, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN		

		CLOSE pCUR

		DEALLOCATE pCUR

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

End

GO

--SET QUOTED_IDENTIFIER ON|OFF

--SET ANSI_NULLS ON|OFF

--GO

ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_APPROVE_CONFIRM

@p_INVENT_ID	VARCHAR(20) = NULL,

@p_CHECKER_ID	VARCHAR(20) = NULL

AS

	BEGIN TRANSACTION

	DECLARE @PROCESS_ID_CUR VARCHAR(15) = (SELECT PROCESS_ID FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID)

    UPDATE dbo.ASS_INVENTORY_PARTY_DT SET IS_DONE = '1' WHERE INVENT_ID = @p_INVENT_ID AND PARTY_NAME = @p_CHECKER_ID

	UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'P',CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = GETDATE() WHERE REQ_ID = @p_INVENT_ID AND ROLE_USER = @p_CHECKER_ID AND PROCESS_ID = @PROCESS_ID_CUR

	IF(NOT EXISTS(SELECT 1 FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @p_INVENT_ID AND PROCESS_ID = @PROCESS_ID_CUR AND STATUS = 'C'))

	BEGIN

		DECLARE @PROCESS_ID_NEXT VARCHAR(15) = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @p_INVENT_ID AND PARENT_PROCESS_ID = @PROCESS_ID_CUR)

		UPDATE dbo.ASS_INVENTORY_MASTER SET PROCESS_ID = @PROCESS_ID_NEXT WHERE INVENT_ID = @p_INVENT_ID

		UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_INVENT_ID AND PROCESS_ID = @PROCESS_ID_NEXT

	END

	INSERT INTO dbo.PL_PROCESS

	(

		REQ_ID,

		PROCESS_ID,

		CHECKER_ID,

		APPROVE_DT,

		PROCESS_DESC,

		NOTES

	)

	VALUES

	(   @p_INVENT_ID,       

		'CONFIRM',

		@p_CHECKER_ID,       

		GETDATE(), 

		N'Thành phần kiểm kê xác nhận thành công' ,     

		N'Xác nhận thông tin kiểm kê'       

	)

	COMMIT TRANSACTION

	SELECT '0' as Result, @p_INVENT_ID  INVENT_ID, N'Xác nhận thành công' ErrorDesc

	RETURN '0'

GO

ALTER   PROC dbo.ASS_INVENTORY_MASTER_DVKD_App

@P_INVENT_ID VARCHAR(15),

@P_AUTH_STATUS VARCHAR(1),

@P_CHECKER_ID VARCHAR(15),

@P_APPROVE_DT VARCHAR(20)

AS

DECLARE @l_TYPE_CREATE VARCHAR(200)

	SET @l_TYPE_CREATE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = (SELECT B.BRANCH_CREATE FROM ASS_INVENTORY_MASTER B WHERE B.INVENT_ID = @P_INVENT_ID))

	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 duyệt đợt kiểm kê.' ErrorDesc

		RETURN '-1'

	END

	IF EXISTS (

		SELECT A.ASSET_ID

		FROM ASS_INVENTORY_DT A

		WHERE A.INVENT_ID = @P_INVENT_ID AND A.ASSET_STATUS <> '1' AND (A.INVENT_DESC IS NULL OR A.INVENT_DESC = '')

	)

	BEGIN 

		SELECT '-1' as Result, '' INVENT_ID, N'Phải cập nhật hiện trạng cho các tài sản có tình trạng khác bình thường trước khi duyệt' ErrorDesc

		RETURN '-1'		

	END

BEGIN TRANSACTION

DECLARE @l_ASSET_ID VARCHAR(15)

	DECLARE @l_ASSET_STATUS VARCHAR(10),@l_ASSET_STATUS_DESC NVARCHAR(1000),@l_ASSET_NOTES NVARCHAR(4000)

	DECLARE pCUR CURSOR FOR 

	SELECT A.ASSET_ID,A.ASSET_STATUS,A.INVENT_DESC,A.NOTES

	FROM ASS_INVENTORY_DT A

	WHERE A.INVENT_ID = @P_INVENT_ID

	AND A.ASSET_STATUS <> '6'

	OPEN pCUR

	DELETE FROM dbo.PL_REQUEST_PROCESS

	WHERE REQ_ID = @P_INVENT_ID;

	DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@STEP_PARENT VARCHAR(20)

	SELECT @BRANCH_ID =TLSUBBRID,@DEP_ID=SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=(SELECT MAKER_ID FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID)

	IF(EXISTS(SELECT * FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN'))

	BEGIN 

		DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@P_INVENT_ID

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,NOTES

		)

		VALUES

		(   @P_INVENT_ID,        -- REQ_ID - varchar(15)

			'SIGN',        -- PROCESS_ID - varchar(10)

			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)

			CONVERT(DATETIME,@p_APPROVE_DT,103) , -- APPROVE_DT - datetime

			N'Cấp phê duyệt trung gian xác nhận phiếu kiểm kê',

			N'Cấp phê duyệt trung gian'

		)

	--- DUA CAP PHE DUYET TRUONG DON VI

	    INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

			DEP_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		VALUES

		(   @P_INVENT_ID,        -- REQ_ID - varchar(15)

		    'APPNEW',        -- PROCESS_ID - varchar(10)

		    'C',        -- STATUS - varchar(5)

		    'GDDV',        -- ROLE_USER - varchar(50)

		    --@BRANCH_CREATE, 

			@BRANCH_ID,

			@DEP_ID,      -- BRANCH_ID - varchar(15)

		    '',        -- CHECKER_ID - varchar(15)

		    NULL,      -- APPROVE_DT - datetime

		    '',        -- PARENT_PROCESS_ID - varchar(10)

		    'N',        -- IS_LEAF - varchar(1)

		    '',        -- COST_ID - varchar(15)

		    '',        -- DVDM_ID - varchar(15)

		    N'Chờ trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)

		    NULL       -- IS_HAS_CHILD - bit

		 )

	--- UPDATE PROCESS_ID VE APP_NEW

		UPDATE ASS_INVENTORY_MASTER SET PROCESS_ID ='APPNEW' WHERE INVENT_ID = @P_INVENT_ID

	END

	ELSE

	BEGIN

		IF(EXISTS(SELECT 1 FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> ''))

		BEGIN

			IF(NOT EXISTS (SELECT 1 FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@P_INVENT_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' Result, N'Phiếu kiểm kê đang đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID)+N' xác nhận phiếu!' ErrorDesc

				RETURN '-1'

			END

			IF(@p_CHECKER_ID = (SELECT SIGN_USER FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID))

			BEGIN

				ROLLBACK TRANSACTION

				SELECT '-1' Result, N'Phiếu kiểm kê đang đợi trưởng đơn vị phê duyệt. Bạn không có thẩm quyền phê duyệt cấp trưởng đơn vị! Vui lòng xem lịch sử xử lý phiếu' ErrorDesc

				RETURN '-1'

			END

		END

		INSERT INTO dbo.PL_REQUEST_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			STATUS,

			ROLE_USER,

			BRANCH_ID,

			DEP_ID,

			CHECKER_ID,

			APPROVE_DT,

			PARENT_PROCESS_ID,

			IS_LEAF,

			NOTES

		)

		VALUES

		(   @p_INVENT_ID,               -- REQ_ID - varchar(15)

			'APPNEW',                  -- PROCESS_ID - varchar(10)

			'P',                     -- STATUS - varchar(5)

			'GDDV',                      -- ROLE_USER - varchar(50)

			@BRANCH_ID  ,

			@DEP_ID,                      -- BRANCH_ID - varchar(15)

			@p_CHECKER_ID,           -- CHECKER_ID - varchar(15)

			GETDATE() , -- APPROVE_DT - datetime

			NULL, 'N', N'Trưởng đơn vị phê duyệt'

		);

		SET @STEP_PARENT = 'APPNEW';

    -- BUOC TIEN HANH KIEM KE 

    DECLARE @MAKER_ID varchar(15)

    SET @MAKER_ID =(SELECT MAKER_ID FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @P_INVENT_ID)

    INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		VALUES ( @P_INVENT_ID, 'THKK', 'U', @MAKER_ID,'','',NULL,@STEP_PARENT,'N','','',N'Chờ thành phần kiểm kê tiến hành kiểm kê',NULL)

	SET @STEP_PARENT = 'THKK';

		-- BUOC XU LY CUA THANH PHAN KIEM KE

		INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		SELECT INVENT_ID, 'TPKK', 'U', PARTY_NAME,'','',NULL,@STEP_PARENT,'N','','',N'Chờ thành phần kiểm kê xác nhận',NULL

		FROM dbo.ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @P_INVENT_ID AND IS_RECIVE_MAIL = '1' AND IS_MAIN = '0'

		SET @STEP_PARENT = 'TPKK';

		-- BUOC XU LY CUA TRUONG BAN KIEM KE

		INSERT INTO dbo.PL_REQUEST_PROCESS

		(

		    REQ_ID,

		    PROCESS_ID,

		    STATUS,

		    ROLE_USER,

		    BRANCH_ID,

		    CHECKER_ID,

		    APPROVE_DT,

		    PARENT_PROCESS_ID,

		    IS_LEAF,

		    COST_ID,

		    DVDM_ID,

		    NOTES,

		    IS_HAS_CHILD

		)

		SELECT INVENT_ID, 'TBKK', 'U', PARTY_NAME,'','',NULL,@STEP_PARENT,'N','','',N'Chờ trưởng ban kiểm kê xác nhận',NULL

		FROM dbo.ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @P_INVENT_ID AND IS_MAIN = '1'

		SET @STEP_PARENT = 'TBKK';

		INSERT INTO dbo.PL_REQUEST_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			STATUS,

			ROLE_USER,

			BRANCH_ID,

			CHECKER_ID,

			APPROVE_DT,

			PARENT_PROCESS_ID,

			IS_LEAF,

			NOTES

		)

		VALUES

		(   @p_INVENT_ID, -- REQ_ID - varchar(15)

			'APPROVE', -- PROCESS_ID - varchar(10)

			'U',       -- STATUS - varchar(5)

			'',        -- ROLE_USER - varchar(50)

			'',        -- BRANCH_ID - varchar(15)

			'',        -- CHECKER_ID - varchar(15)

			NULL,      -- APPROVE_DT - datetime

			@STEP_PARENT, 'Y', N'Hoàn tất'

		);

		DECLARE @PROCESS_ID_CURR VARCHAR(10);

		SET @PROCESS_ID_CURR =

		(

			SELECT TOP 1 PROCESS_ID

			FROM dbo.PL_REQUEST_PROCESS

			WHERE REQ_ID = @P_INVENT_ID

				AND PARENT_PROCESS_ID = 'APPNEW'

		);

		UPDATE dbo.PL_REQUEST_PROCESS

		SET STATUS = 'C'

		WHERE PARENT_PROCESS_ID = 'APPNEW'

			  AND REQ_ID = @P_INVENT_ID;

		UPDATE ASS_INVENTORY_MASTER SET AUTH_STATUS_DVKD = 'A', CHECKER_ID_DVKD = @P_CHECKER_ID, APPROVE_DT_DVKD = GETDATE(),AUTH_STATUS = 'U',PROCESS_ID=@PROCESS_ID_CURR

		WHERE INVENT_ID = @P_INVENT_ID

		IF @@Error <> 0 GOTO ABORT

		-- GIANT 26/08/2021

		INSERT INTO dbo.PL_PROCESS

		(

			REQ_ID,

			PROCESS_ID,

			CHECKER_ID,

			APPROVE_DT,

			PROCESS_DESC,

			NOTES

		)

		VALUES

		(   @p_INVENT_ID,       

			'APPROVE',

			@P_CHECKER_ID,       

			GETDATE(), 

			N'Trưởng đơn vị đã phê duyệt thành công' ,     

			N'Trưởng đơn vị đã phê duyệt'       

		)

	END

CLOSE pCUR

DEALLOCATE pCUR

COMMIT TRANSACTION

IF(EXISTS(SELECT * FROM ASS_INVENTORY_MASTER WHERE AUTH_STATUS_DVKD ='A' AND INVENT_ID =@p_INVENT_ID))

BEGIN

	SELECT '0' AS Result,

       --@ROLE_USER_NOTIFI AS ROLE_NOTIFI,

       N'Phiếu kiểm kê đã được trưởng đơn vị phê duyệt thành công.' ErrorDesc;

	RETURN '0';

END

ELSE

BEGIN

	SELECT '0' as Result, 

	N'Phiếu kiểm kê đã được cấp phê duyệt trung gian xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt' ErrorDesc

	RETURN '0'

END

SELECT '0' as Result, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN		

		CLOSE pCUR

		DEALLOCATE pCUR

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

End

GO

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),

    INVENT_STATUS varchar(15)

	)

	OPEN InventDetail

  --PHONGNT 08/02/23 Xóa những tài sản không tồn tại 

  DELETE FROM ASS_INVENTORY_DT WHERE INVENT_ID = @P_INVENT_ID AND ASSET_ID NOT IN (SELECT ASSET_ID

	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),

    INVENT_STATUS varchar(15)

	))

	DELETE FROM dbo.ASS_INVENTORY_DT_RPT WHERE INVENT_ID = @P_INVENT_ID AND  ASSET_ID NOT IN (SELECT ASSET_ID

	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),

    INVENT_STATUS varchar(15)

	))

  --END

	-- 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),

        COMMENT NVARCHAR(500)

	)

	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 (EXISTS(SELECT 1 FROM ASS_INVENTORY_MASTER aim WHERE aim.INVENT_ID=@P_INVENT_ID AND aim.PROCESS_ID='THKK'))

    SET @p_AUTH_STATUS_DVKD ='A'

	IF @p_APPROVE_DT = '' SET @p_APPROVE_DT = NULL

	DECLARE @l_FILE_REF_Ids   NVARCHAR(MAX) =''

	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

	Declare @INVENTDT_ID VARCHAR(15),@ASSET_ID varchar(15), @ASSET_STATUS nvarchar(20),@INVENT_DESC nvarchar(1000), @NO INT = 0,

	@NOTES nvarchar(500), @DEPT_USE VARCHAR(15), @BRANCH_USE VARCHAR(15), @REMAIN_VALUE decimal(18,0),@INVENT_STATUS VARCHAR(10)

	FETCH NEXT FROM InventDetail INTO @INVENTDT_ID,@ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE,@INVENT_STATUS

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

        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)

  			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,@P_INVENT_ID,@ASSET_ID,@ASSET_STATUS,@INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE)

			END

      ELSE

      BEGIN

        UPDATE ASS_INVENTORY_DT SET [NOTES]=@NOTES,[ASSET_STATUS]=@ASSET_STATUS,[INVENT_DESC]=@INVENT_DESC  WHERE [INVENTDT_ID] = @INVENTDT_ID

        UPDATE ASS_INVENTORY_DT_RPT SET [NOTES]=@NOTES,[ASSET_STATUS]=@ASSET_STATUS,[INVENT_DESC]=@INVENT_DESC  WHERE [INVENTDT_ID] = @INVENTDT_ID

      END

      IF(@INVENT_STATUS='1')

      BEGIN

        DELETE ASS_INVENTORY_DT_LOG WHERE [INVENTDT_ID]= @INVENTDT_ID

        DECLARE @LOG_ID VARCHAR(20)

        EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT_LOG', @LOG_ID out

				IF @LOG_ID ='' OR @LOG_ID IS NULL GOTO ABORT

        INSERT INTO ASS_INVENTORY_DT_LOG (LOG_ID, INVENTDT_ID, INVENT_ID, RECORD_STATUS, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT)

        VALUES (@LOG_ID, @INVENTDT_ID, @P_INVENT_ID, '1', 'U', @p_MAKER_ID, GETDATE(), '', NULL);

        SET @INVENTDT_ID =NULL

      END

		-- next Group_Id

			IF @@ERROR <> 0 GOTO ABORT

      SET @l_FILE_REF_Ids = @l_FILE_REF_Ids + CASE WHEN @l_FILE_REF_Ids = '' THEN '' ELSE ',' END + CONCAT( @INVENTDT_ID + '-', @ASSET_ID)

			FETCH NEXT FROM InventDetail INTO @INVENTDT_ID,@ASSET_ID, @ASSET_STATUS, @INVENT_DESC, @NOTES, @BRANCH_USE, @DEPT_USE, @REMAIN_VALUE,@INVENT_STATUS

		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),

        @COMMENT        NVARCHAR(500)

	FETCH NEXT FROM PartyDetail INTO  @PARTY_ID,@INVENT_ID,@PARTY_NAME,@PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN,@COMMENT

		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],[COMMENT])

			VALUES(@l_PARTY_ID ,@p_INVENT_ID, @PARTY_NAME, @PARTY_ROLE,@IS_RECIVE_MAIL,@IS_DONE,@IS_MAIN,@COMMENT)

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

		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,@l_FILE_REF_Ids Ids

RETURN '0'

ABORT:

BEGIN

		CLOSE InventDetail

		DEALLOCATE InventDetail

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' INVENT_ID, '' ErrorDesc

		RETURN '-1'

End

GO

ALTER PROCEDURE dbo.ASS_INVENTORY_MASTER_Search

@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	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_TOP	INT = 10,

@p_BRANCH_CREATE	VARCHAR(15) = NULL,

@p_LEVEL	VARCHAR(50) = 'UNIT',

@p_USER_LOGIN	VARCHAR(20) = NULL,

@p_TYPE_SEARCH	VARCHAR(20) = NULL,

@p_AUTH_STATUS_CONFIRM VARCHAR(1) = NULL,

@p_FROMDATE VARCHAR(20) = NULL,

@p_TODATE VARCHAR(20) = NULL

AS

	--Validation is here

/*

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF ( [NOT] EXISTS ( SELECT * FROM [TABLE] WHERE  [CONDITION] ))

		 SET @ERRORSYS = ''

	IF @ERRORSYS <> '' 

	BEGIN

		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

		RETURN '0'

	END */

BEGIN -- PAGING

declare @tmp table(BRANCH_ID varchar(15))

	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_CREATE)

	IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)

	-- PAGING BEGIN

	SELECT A.INVENT_ID ,A.INVENTORY_DT ,A.TERM,A.BRANCH_ID,A.NOTES,A.RECORD_STATUS,A.AUTH_STATUS,A.MAKER_ID,A.CREATE_DT,A.CHECKER_ID ,A.APPROVE_DT,A.BRANCH_CREATE,A.DEPT_ID,DE.DEP_CODE AS DEPT_CODE,DE.DEP_NAME AS DEPT_NAME,A.NQL,A.DECISION,A.PROM_DT,A.COMMENT,A.PENTITION,A.ISSUED_DATE,A.AUTH_STATUS_DVKD,A.CHECKER_ID_DVKD,A.APPROVE_DT_DVKD,A.SIGN_USER,A.PROCESS_ID,C.BRANCH_CODE, C.BRANCH_NAME, T.TERM_CODE, T.TERM_NAME,D.CONTENT AS AUTH_STATUS_DVKD_NAME,TL.TLFullName AS CHECKER_ID_DVKD_NAME,

	CASE 

	WHEN EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN AND IS_DONE = '1')

		THEN N'Đã xác nhận'

		-- PHONGNT 27/06/22

	WHEN @p_TYPE_SEARCH='APPR' AND EXISTS (SELECT 1 FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1')

		THEN N'Đã xác nhận'

	ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS,

	CASE @p_TYPE_SEARCH 

		-- END

	WHEN 'CF' 

		THEN (SELECT TOP(1) CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN)

	ELSE (SELECT TOP(1) CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1' AND CONFIRM_DT IS NOT NULL) END AS CONFIRM_DT,

	(SELECT TLFullName FROM TL_USER WHERE TLNANME = ( SELECT TOP(1) PARTY_NAME FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_MAIN = '1')) AS APPROVE_NAME,

	(SELECT TOP(1) tu.TLFullName FROM TL_USER tu WHERE tu.TLNANME = A.SIGN_USER) AS SIGN_USER_NAME,

  CASE WHEN A.ASSET_TYPE IS NULL OR A.ASSET_TYPE ='' THEN 'TSCD/CCLD' ELSE A.ASSET_TYPE END AS ASSET_TYPE

	-- SELECT END

	FROM ASS_INVENTORY_MASTER A 

	LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS 

	LEFT JOIN CM_BRANCH C ON C.BRANCH_ID = A.BRANCH_ID

	--PHONGNT 27/8/22 Bổ sung phòng ban

	LEFT JOIN CM_DEPARTMENT DE ON DE.DEP_ID = A.DEPT_ID

	--END

--	LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS_DVKD = D.AUTH_STATUS 

	LEFT JOIN CM_ALLCODE D ON (D.CDVAL=A.AUTH_STATUS_DVKD AND D.CDNAME='ASS_INVENTORY')

	LEFT JOIN CM_TERM T ON T.TERM_ID = A.TERM

	LEFT JOIN TL_USER TL ON TL.TLNANME = A.CHECKER_ID_DVKD

	 WHERE 1 = 1

		AND (A.INVENT_ID LIKE '%' + @p_INVENT_ID + '%' OR  @p_INVENT_ID IS NULL OR @p_INVENT_ID = '')

		AND (DATEDIFF(DAYOFYEAR,A.INVENTORY_DT,CONVERT(DATETIME, @p_INVENTORY_DT, 103)) = 0 OR  @p_INVENTORY_DT IS NULL OR @p_INVENTORY_DT = '')

		AND (A.TERM LIKE '%' + @p_TERM + '%' OR  @p_TERM IS NULL OR @p_TERM = '')

		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')

		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')

		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')

		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')

		AND (DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')

		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')

		AND (DATEDIFF(DAYOFYEAR,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'

		AND ((@P_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))

			OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @p_BRANCH_CREATE) 

			OR (@p_BRANCH_CREATE = '' OR @p_BRANCH_CREATE IS NULL)))

		-- GIANT 25/08/2021

		AND ((@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'U' 

					AND EXISTS ((SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1 

						AND ( (@p_AUTH_STATUS_CONFIRM = 'Y' AND IS_DONE = 1) OR (@p_AUTH_STATUS_CONFIRM = 'N' AND IS_DONE = 0) OR (@p_AUTH_STATUS_CONFIRM = '' OR @p_AUTH_STATUS_CONFIRM IS NULL) )

					))

				OR	(@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL

			))

      OR ( 

				(@p_TYPE_SEARCH = 'PC' AND A.AUTH_STATUS = 'U' 

					AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1)  )

				OR	(@p_TYPE_SEARCH = 'PC' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1)  )

				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) 

        AND A.PROCESS_ID='THKK'

			)

			OR ( 

				(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS_DVKD = 'A' AND  NOT EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE IS_RECIVE_MAIL = 1 AND IS_DONE = 0 AND INVENT_ID = A.INVENT_ID)

					AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

				OR	(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS_DVKD = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) 

			)

		)

		AND (A.AUTH_STATUS_DVKD LIKE '%' + @p_AUTH_STATUS_DVKD + '%' OR  @p_AUTH_STATUS_DVKD IS NULL OR @p_AUTH_STATUS_DVKD = '')

		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT, A.CREATE_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')

		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT, A.CREATE_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')

		-- HUYHT 27/04/2022 Khi trạng thái duyệt là "Nháp", "Từ chối" thì chỉ người tạo tìm thấy phiếu

		AND ((A.AUTH_STATUS_DVKD IN ('E','R') AND (A.MAKER_ID = @p_USER_LOGIN OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = ''))

			OR A.AUTH_STATUS_DVKD NOT IN ('E','R'))

		ORDER BY A.CREATE_DT DESC

	-- PAGING END

	ELSE

	-- PAGING BEGIN

	SELECT TOP(CONVERT(INT,@P_TOP))A.INVENT_ID ,A.INVENTORY_DT ,A.TERM,A.BRANCH_ID,A.NOTES,A.RECORD_STATUS,A.AUTH_STATUS,A.MAKER_ID,A.CREATE_DT,A.CHECKER_ID ,A.APPROVE_DT,A.BRANCH_CREATE,A.DEPT_ID,DE.DEP_CODE AS DEPT_CODE,DE.DEP_NAME AS DEPT_NAME,A.NQL,A.DECISION,A.PROM_DT,A.COMMENT,A.PENTITION,A.ISSUED_DATE,A.AUTH_STATUS_DVKD,A.CHECKER_ID_DVKD,A.APPROVE_DT_DVKD,A.SIGN_USER,A.PROCESS_ID,B.AUTH_STATUS_NAME,C.BRANCH_CODE, C.BRANCH_NAME, T.TERM_CODE, T.TERM_NAME,D.CONTENT AS AUTH_STATUS_DVKD_NAME,TL.TLFullName AS CHECKER_ID_DVKD_NAME,

	CASE 

		WHEN EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN AND IS_DONE = '1')

			THEN N'Đã xác nhận'

			-- PHONGNT 27/06/22 Bổ sung màn hình phê duyệt ngày

		WHEN @p_TYPE_SEARCH='APPR' AND EXISTS (SELECT CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1')

			THEN N'Đã xác nhận'

		ELSE N'Chưa xác nhận' END AS CONFIRM_STATUS,

	CASE @p_TYPE_SEARCH 

		WHEN 'CF' 

			THEN (SELECT TOP(1) CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND PARTY_NAME = @p_USER_LOGIN)

		ELSE (SELECT TOP(1) CONFIRM_DT FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_DONE = '1' AND CONFIRM_DT IS NOT NULL ORDER BY CONFIRM_DT) END AS CONFIRM_DT,

	(SELECT TLFullName FROM TL_USER WHERE TLNANME = ( SELECT TOP(1) PARTY_NAME FROM ASS_INVENTORY_PARTY_DT WHERE A.INVENT_ID = INVENT_ID AND IS_MAIN = '1')) AS APPROVE_NAME,

	(SELECT TOP(1) tu.TLFullName FROM TL_USER tu WHERE tu.TLNANME = A.SIGN_USER) AS SIGN_USER_NAME,

  CASE WHEN A.ASSET_TYPE IS NULL OR A.ASSET_TYPE ='' THEN 'TSCD/CCLD' ELSE A.ASSET_TYPE END AS ASSET_TYPE

	-- SELECT END

	FROM ASS_INVENTORY_MASTER A 

	LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS

	--LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS_DVKD = D.AUTH_STATUS 

	--PHONGNT 27/8/22 Bổ sung phòng ban

	LEFT JOIN CM_DEPARTMENT DE ON DE.DEP_ID = A.DEPT_ID

	--END

	LEFT JOIN CM_ALLCODE D ON (D.CDVAL=A.AUTH_STATUS_DVKD AND D.CDNAME='ASS_INVENTORY')

	LEFT JOIN CM_BRANCH C ON C.BRANCH_ID = A.BRANCH_ID 

	LEFT JOIN CM_TERM T ON T.TERM_ID = A.TERM

	LEFT JOIN TL_USER TL ON TL.TLNANME = A.CHECKER_ID_DVKD

	 WHERE 1 = 1

		AND (A.INVENT_ID LIKE '%' + @p_INVENT_ID + '%' OR  @p_INVENT_ID IS NULL OR @p_INVENT_ID = '')

		AND (DATEDIFF(DAYOFYEAR,A.INVENTORY_DT,CONVERT(DATETIME, @p_INVENTORY_DT, 103)) = 0 OR  @p_INVENTORY_DT IS NULL OR @p_INVENTORY_DT = '')

		AND (A.TERM LIKE '%' + @p_TERM + '%' OR  @p_TERM IS NULL OR @p_TERM = '')

		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')

		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')

		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')

		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')

		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')

		AND (DATEDIFF(DAYOFYEAR,A.CREATE_DT,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')

		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')

		AND (DATEDIFF(DAYOFYEAR,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'

		AND ((@P_LEVEL = 'ALL' AND A.BRANCH_CREATE IN (SELECT BRANCH_ID from @tmp))

			OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_CREATE = @p_BRANCH_CREATE) 

			OR (@p_BRANCH_CREATE = '' OR @p_BRANCH_CREATE IS NULL)))

		-- GIANT 25/08/2021

		--AND (

		--	(@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'U' 

		--		AND EXISTS ((SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1 

		--			AND ( (@p_AUTH_STATUS_CONFIRM = 'Y' AND IS_DONE = 1) OR (@p_AUTH_STATUS_CONFIRM = 'N' AND IS_DONE = 0) OR (@p_AUTH_STATUS_CONFIRM = '' OR @p_AUTH_STATUS_CONFIRM IS NULL) )

		--		))

		--		OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL

		--	))			

		--	OR ( 

		--		(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS = 'U' AND  NOT EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE IS_RECIVE_MAIL = 1 AND IS_DONE = 0 AND INVENT_ID = A.INVENT_ID)

		--			AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

		--		OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) )

		--	)

				AND ((@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'U' 

					AND EXISTS ((SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1 

						AND ( (@p_AUTH_STATUS_CONFIRM = 'Y' AND IS_DONE = 1) OR (@p_AUTH_STATUS_CONFIRM = 'N' AND IS_DONE = 0) OR (@p_AUTH_STATUS_CONFIRM = '' OR @p_AUTH_STATUS_CONFIRM IS NULL) )

					))

				OR	(@p_TYPE_SEARCH = 'CF' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL

			))

      OR ( 

				(@p_TYPE_SEARCH = 'PC' AND A.AUTH_STATUS = 'U' 

					AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1)  )

				OR	(@p_TYPE_SEARCH = 'PC' AND A.AUTH_STATUS = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_RECIVE_MAIL = 1)  )

				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) 

        AND A.PROCESS_ID='THKK'

			)

			OR ( 

				(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS_DVKD = 'A' AND  NOT EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE IS_RECIVE_MAIL = 1 AND IS_DONE = 0 AND INVENT_ID = A.INVENT_ID)

					AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

				OR	(@p_TYPE_SEARCH = 'APPR' AND A.AUTH_STATUS_DVKD = 'A' AND EXISTS (SELECT * FROM ASS_INVENTORY_PARTY_DT WHERE PARTY_NAME = @p_USER_LOGIN AND INVENT_ID = A.INVENT_ID AND IS_MAIN = 1)  )

				OR (@p_TYPE_SEARCH = '' OR @p_TYPE_SEARCH IS NULL) 

			)

		)

		AND (A.AUTH_STATUS_DVKD LIKE '%' + @p_AUTH_STATUS_DVKD + '%' OR  @p_AUTH_STATUS_DVKD IS NULL OR @p_AUTH_STATUS_DVKD = '')

		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT,A.CREATE_DT),103) >=CONVERT(DATE, @p_FROMDATE, 103) OR  @p_FROMDATE IS NULL OR @p_FROMDATE = '')

		AND (CONVERT(DATE,ISNULL(A.INVENTORY_DT, A.CREATE_DT),103) <=CONVERT(DATETIME, @p_TODATE, 103) OR  @p_TODATE IS NULL OR @p_TODATE = '')

		-- HUYHT 27/04/2022 Khi trạng thái duyệt là "Nháp", "Từ chối" thì chỉ người tạo tìm thấy phiếu

		AND ((A.AUTH_STATUS_DVKD IN ('E','R') AND (A.MAKER_ID = @p_USER_LOGIN OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = ''))

			OR A.AUTH_STATUS_DVKD NOT IN ('E','R'))

		ORDER BY A.CREATE_DT DESC

	-- PAGING END

END -- PAGING

GO

ALTER   PROC dbo.ASS_INVENTORY_MASTER_SendApp

@p_INVENT_ID VARCHAR(20),

@p_USERLOGIN VARCHAR(50),

@p_TYPE VARCHAR(15),

@p_AUTH_STATUS VARCHAR(1)

AS

BEGIN TRANSACTION

    DECLARE @Mes NVARCHAR(MAX)

	IF(EXISTS(SELECT * FROM ASS_INVENTORY_MASTER aim WHERE aim.INVENT_ID=@p_INVENT_ID AND aim.PROCESS_ID IS NULL OR aim.PROCESS_ID=''))

  BEGIN

  	IF(EXISTS(SELECT INVENT_ID FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID AND AUTH_STATUS_DVKD = @p_AUTH_STATUS ) )

  	BEGIN

  			ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  INVENT_ID, N'Phiếu kiểm kê đã được gửi phê duyệt trước đó.' ErrorDesc

  			RETURN '-1'

  	END

  	IF (NOT EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @p_INVENT_ID))

  	BEGIN

  			ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  INVENT_ID, N'Danh sách thành phần kiểm kê không được để trống.' ErrorDesc

  			RETURN '-1'

  	END

  	IF (NOT EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @p_INVENT_ID AND IS_MAIN = '1'))

  	BEGIN

  			ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  INVENT_ID, N'Danh sách thành phần kiểm kê phải có ít nhất một trưởng bản kiểm kê.' ErrorDesc

  			RETURN '-1'

  	END

  	IF (NOT EXISTS (SELECT INVENT_ID FROM ASS_INVENTORY_PARTY_DT WHERE INVENT_ID = @p_INVENT_ID AND IS_RECIVE_MAIL = '1' AND IS_MAIN <> '1'))

  	BEGIN

  			ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  INVENT_ID, N'Danh sách thành phần kiểm kê phải có ít nhất 1 người nhận mail ngoại trừ trưởng ban.' ErrorDesc

  			RETURN '-1'

  	END

  	DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20)

  	UPDATE ASS_INVENTORY_MASTER SET AUTH_STATUS_DVKD = @p_AUTH_STATUS, PROCESS_ID='APPNEW' WHERE INVENT_ID = @p_INVENT_ID

  	SELECT @BRANCH_ID =TLSUBBRID,@DEP_ID=SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_USERLOGIN

  	DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_INVENT_ID

  	IF(EXISTS(SELECT * FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>''))

  	BEGIN

  		DECLARE @BRANCH_SIGN_ID VARCHAR(20),@DEP_SIGN_ID VARCHAR(20),@BRANCH_SIGN_TYPE VARCHAR(10),@ROLE_SIGN VARCHAR(20)

  		SELECT @BRANCH_SIGN_ID=TLSUBBRID,@DEP_SIGN_ID=SECUR_CODE,@BRANCH_SIGN_TYPE=BRANCH_TYPE FROM dbo.TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID)

  		UPDATE ASS_INVENTORY_MASTER SET AUTH_STATUS_DVKD = @p_AUTH_STATUS, PROCESS_ID='SIGN' WHERE INVENT_ID = @p_INVENT_ID

  		INSERT INTO dbo.PL_REQUEST_PROCESS

  		(

  		    REQ_ID,

  		    PROCESS_ID,

  		    STATUS,

  		    ROLE_USER,

  		    BRANCH_ID,

  			DEP_ID,

  		    CHECKER_ID,

  		    APPROVE_DT,

  		    PARENT_PROCESS_ID,

  		    IS_LEAF,

  		    COST_ID,

  		    DVDM_ID,

  		    NOTES,

  		    IS_HAS_CHILD

  		)

  		VALUES

  		(   @p_INVENT_ID,        -- REQ_ID - varchar(15)

  		    'SIGN',        -- PROCESS_ID - varchar(10)

  		    'C',        -- STATUS - varchar(5)

  		    '',  -- ROLE_USER - varchar(50)

  		    @BRANCH_SIGN_ID,  

  			CASE WHEN @BRANCH_SIGN_TYPE='HS' THEN @DEP_SIGN_ID ELSE '' END,      -- BRANCH_ID - varchar(15)

  		    '',        -- CHECKER_ID - varchar(15)

  		    NULL,      -- APPROVE_DT - datetime

  		    '',        -- PARENT_PROCESS_ID - varchar(10)

  		    'N',        -- IS_LEAF - varchar(1)

  		    '',        -- COST_ID - varchar(15)

  		    '',        -- DVDM_ID - varchar(15)

  		    N'Chờ cấp phê duyệt trung gian xác nhận phiếu',       -- NOTES - nvarchar(500)

  		    NULL       -- IS_HAS_CHILD - bit

  		 )

  	END

  	ELSE

  	BEGIN

  		INSERT INTO dbo.PL_REQUEST_PROCESS

  		(

  		    REQ_ID,

  		    PROCESS_ID,

  		    STATUS,

  		    ROLE_USER,

  		    BRANCH_ID,

  			DEP_ID,

  		    CHECKER_ID,

  		    APPROVE_DT,

  		    PARENT_PROCESS_ID,

  		    IS_LEAF,

  		    COST_ID,

  		    DVDM_ID,

  		    NOTES,

  		    IS_HAS_CHILD

  		)

  		VALUES

  		(   @p_INVENT_ID,        -- REQ_ID - varchar(15)

  		    'APPNEW',        -- PROCESS_ID - varchar(10)

  		    'C',        -- STATUS - varchar(5)

  		    'GDDV',        -- ROLE_USER - varchar(50)

  		    @BRANCH_ID,  

  			@DEP_ID,      -- BRANCH_ID - varchar(15)

  		    '',        -- CHECKER_ID - varchar(15)

  		    NULL,      -- APPROVE_DT - datetime

  		    '',        -- PARENT_PROCESS_ID - varchar(10)

  		    'N',        -- IS_LEAF - varchar(1)

  		    '',        -- COST_ID - varchar(15)

  		    '',        -- DVDM_ID - varchar(15)

  		   -- N'Chờ trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)

  		    N'Chờ trưởng đơn vị phê duyệt' ,

  		    NULL       -- IS_HAS_CHILD - bit

  		)

  	END

  	INSERT INTO dbo.PL_PROCESS

  	(

  		REQ_ID,

  		PROCESS_ID,

  		CHECKER_ID,

  		APPROVE_DT,

  		PROCESS_DESC,

  		NOTES

  	)

  	VALUES

  	(   @p_INVENT_ID,       

  		'SEND',

  		@p_USERLOGIN,       

  		GETDATE(), 

  		N'Người tạo phiếu kiểm kê gửi phê duyệt thành công' ,     

  		N'Nhân viên gửi phê duyệt '       

  	)

    SET @Mes=N'Phiếu kiểm kê: '+(SELECT INVENT_ID FROM ASS_INVENTORY_MASTER WHERE INVENT_ID  = @p_INVENT_ID)+N' đã được gửi xác nhận thành công'

  	IF @@Error <> 0 GOTO ABORT

  END

  ELSE

    BEGIN

      DECLARE @PROCESS_ID_CUR VARCHAR(15) = (SELECT PROCESS_ID FROM dbo.ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID)

  	  UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'P',CHECKER_ID = @p_USERLOGIN, APPROVE_DT = GETDATE() WHERE REQ_ID = @p_INVENT_ID AND ROLE_USER = @p_USERLOGIN AND PROCESS_ID = @PROCESS_ID_CUR

  	IF(NOT EXISTS(SELECT 1 FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @p_INVENT_ID AND PROCESS_ID = @PROCESS_ID_CUR AND STATUS = 'C'))

  	BEGIN

  		DECLARE @PROCESS_ID_NEXT VARCHAR(15) = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID = @p_INVENT_ID AND PARENT_PROCESS_ID = @PROCESS_ID_CUR)

  		UPDATE dbo.ASS_INVENTORY_MASTER SET PROCESS_ID = @PROCESS_ID_NEXT WHERE INVENT_ID = @p_INVENT_ID

  		UPDATE dbo.PL_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_INVENT_ID AND PROCESS_ID = @PROCESS_ID_NEXT

  	END

  	INSERT INTO dbo.PL_PROCESS

  	(

  		REQ_ID,

  		PROCESS_ID,

  		CHECKER_ID,

  		APPROVE_DT,

  		PROCESS_DESC,

  		NOTES

  	)

  	VALUES

  	(   @p_INVENT_ID,       

  		'THKK',

  		@p_USERLOGIN,       

  		GETDATE(), 

  		N'Người tạo gửi duyệt kỳ kiểm kê' ,     

  		N'Thực hiện kiểm kê'       

  	)

    SET @Mes=N'Phiếu kiểm kê: '+(SELECT INVENT_ID FROM ASS_INVENTORY_MASTER WHERE INVENT_ID  = @p_INVENT_ID)+N' đã được gửi phê duyệt thành công'

    END

COMMIT TRANSACTION

SELECT '0' as Result,'' REQ_CODE,''REQ_ID, @Mes ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc

		RETURN '-1'

End

GO