AMS - VB

ALTER PROCEDURE dbo.ASS_USE_CONFIRM_MASTER_Ins

@p_BRANCH_ID	varchar(15)  = NULL,

@p_DEP_ID	varchar(15)  = NULL,

@p_USE_EXPORT_DT	VARCHAR(20) = NULL,

@p_USER_MASTER_ID varchar(15) = null,

@p_USER_EXPORT	nvarchar(200)  = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(100)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(100)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL,

@p_AUTH_STATUS_KT	varchar(15)  = NULL,

@p_CREATE_DT_KT	varchar(100) = NULL,

@p_APPROVE_DT_KT	VARCHAR(20) = NULL,

@p_MAKER_ID_KT	varchar(100)  = NULL,

@p_CHECKER_ID_KT	varchar(100)  = NULL,

@p_REPORT_STATUS	varchar(15)  = NULL,

@p_BRANCH_CREATE varchar(15)  = NULL,

@p_XmlData XML = NUL

AS

--START MOVE TỪ APPROVE HS QUA

DECLARE @l_BRANCH_ID VARCHAR(15)

	DECLARE @l_DEPT_ID VARCHAR(15)

	DECLARE @l_EMP_ID VARCHAR(15)

	DECLARE @l_DIVISION_ID	VARCHAR(15)

	DECLARE @l_VALUE_ID	VARCHAR(15)

	--DECLARE @l_LOCATION nvarchar(500)

	DECLARE @l_LOCHIST_ID	VARCHAR(15)

	DECLARE @l_ASSET_ID VARCHAR(15)

	DECLARE @l_WAHDT_ID VARCHAR(15)

	DECLARE @l_WAH_ID VARCHAR(15)

	DECLARE @l_MAKER_ID			varchar(15)

	DECLARE @l_AMORT_MONTH decimal(18,2)

	DECLARE @l_AMORT_START_DATE datetime, @l_AMORT_END_DATE datetime

	DECLARE @l_FIRST_AMORT_AMT	numeric(18,0)

	DECLARE @l_MONTHLY_AMT 	numeric(18,0)

	DECLARE @l_ASS_AMORT_AMT numeric(18,0)

	DECLARE @sToday varchar(10) = convert(varchar(10), getdate(), 103)

	DECLARE @l_ENTRY_BOOKED varchar(1)

	DECLARE @l_HO_BRN_ID varchar(15)	

	DECLARE @l_AMORT_AMT decimal(18)  = NULL	

	DECLARE @l_BUY_PRICE decimal(18)  = NULL	

	DECLARE @l_ET_ID varchar(15)

	DEClare @p_ADDNEW_ID varchar(15)

	DECLARE @l_SUPPEND_GL varchar(50)

	DECLARE @l_ASSET_GL  varchar(50)

	declare @l_ASSET_VALUE decimal(18,0)	

	DECLARE @l_GROUP_ID varchar(15)	

	DECLARE @l_CORE_NOTE NVARCHAR(500)

	DECLARE @l_TYPE_ID	varchar(15)  = NULL

	declare @l_TRN_REF_NO varchar(20)

	declare @l_DO_BRANCH_ID varchar(15)

	DECLARE @l_AMORT_STATUS VARCHAR(15) = 'CKH'

	DECLARE @l_AMORT_MONTH_ASS_USE DECIMAL(18,2) = NULL

	declare @l_AUTH_STATUS varchar(15) = ''

  declare @l_ASSHIST_ID varchar(15)

	--Lay thong tin kho mac dinh

	SELECT @l_WAH_ID = P.ParaValue FROM SYS_PARAMETERS P WHERE P.ParaKey='ASSET_WAREHOUSE'

	--BRN_ID HOI SO

	SELECT @l_HO_BRN_ID = BRANCH_ID FROM CM_BRANCH A WHERE A.BRANCH_TYPE = 'HS'

	--Lay branch_id cua user duyet

	select @l_DO_BRANCH_ID = a.TLSUBBRID from TL_USER a where a.TLNANME = @p_CHECKER_ID	

	--Lay suppend GL

	SELECT @l_SUPPEND_GL = A.ParaValue FROM SYS_PARAMETERS A WHERE A.ParaKey='ASSET_SUPPEND_GL'

	--ASSET_GL

	SELECT @l_ASSET_GL = ASSET_ACCTNO FROM ASS_GROUP A WHERE A.GROUP_ID = @l_GROUP_ID

	IF (SELECT AUTH_STATUS_KT FROM ASS_USE_MULTI_MASTER WHERE [USER_MASTER_ID] = @p_USER_MASTER_ID) = 'A'

	BEGIN

		SELECT '-1' as Result, (SELECT ErrorDesc from SYS_ERROR WHERE ErrorCode = 'ASS-99998') ErrorDesc

		RETURN '-1'

	END

  IF(EXISTS(SELECT 1 FROM ASS_USE_CONFIRM_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID))

  BEGIN

		SELECT '-1' as Result, N'Phiếu xuất sử dụng đã được xác nhận' ErrorDesc

		RETURN '-1'

	END

--END MOVE TỪ APPROVE HS QUA

DECLARE  @TBL_GROUP_CODE_ASS_USE TABLE (GROUP_ID VARCHAR(100))

BEGIN TRANSACTION

DECLARE @l_USER_CONFIRM_MASTER_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'ASS_CONFIRM_USE_MASTER', @l_USER_CONFIRM_MASTER_ID out

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

		INSERT INTO ASS_USE_CONFIRM_MASTER

		(

			[USER_CONFIRM_MASTER_ID],

			[USER_MASTER_ID],

			[USE_EXPORT_DT],

			[BRANCH_ID],

			[DEP_ID],

			[MAKER_ID],

			[CONFIRM_DT],

			[CONFIRM_STATUS]

		)

		VALUES

		(

			@l_USER_CONFIRM_MASTER_ID,

			@p_USER_MASTER_ID,

			CONVERT(DATETIME, @p_USE_EXPORT_DT, 103),

			@p_BRANCH_ID,

			@p_DEP_ID,

			@p_MAKER_ID,

			GETDATE(),

			'Y'

		)

		IF @@Error <> 0 GOTO ABORT

		-- GIANT INSERT TO TABLE PL_PROCESS

		INSERT INTO dbo.PL_PROCESS

					(

					    REQ_ID,

					    PROCESS_ID,

					    CHECKER_ID,

					    APPROVE_DT,

					    PROCESS_DESC,

					    NOTES

					)

					VALUES

					(   @p_USER_MASTER_ID,       

						'CONFIRM',

					    @p_MAKER_ID,        

					    GETDATE(), 

					    N'Xác nhận xuất sử dụng tài sản ' ,      

					    N'Xác nhận xuất sử dụng tài sản thành công'       

					 )

    --START MOVE TỪ APPROVE HS QUA

      --LUCTV: 26-20-2018 KIEM TRA NEU TINH TRANG DANG LA 'R' THI KHONG CHO PHEP DUYET

  		IF(EXISTS(SELECT * FROM ASS_USE_MULTI_MASTER WHERE AUTH_STATUS ='R' AND USER_MASTER_ID =@p_USER_MASTER_ID))

  		BEGIN

  					ROLLBACK TRANSACTION

  					SELECT '-1' as Result, N'Thông tin xuất sử dụng tài sản đang bị trả về. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc

  					RETURN '-1'

  		END

      DECLARE @USE_MULTI_ID varchar(15)

      		DECLARE @ASSET_ID varchar(15)

      		DECLARE DataCusor SCROLL CURSOR 

      		FOR  

      		SELECT A.USE_MULTI_ID,A.ASSET_ID

      		FROM [dbo].[ASS_USE_MULTI_DT] A

      		WHERE A.USER_MASTER_ID=@p_USER_MASTER_ID		

      		OPEN DataCusor  

      FETCH NEXT FROM DataCusor INTO  @USE_MULTI_ID,@ASSET_ID

      WHILE @@FETCH_STATUS = 0

      BEGIN		

      	--Lay thong tin giao dich

      	SELECT	@l_ASSET_ID = ASSET_ID, @l_BRANCH_ID = BRANCH_ID, @l_DEPT_ID = DEPT_ID, 

      			@l_EMP_ID = EMP_ID, @l_DIVISION_ID = DIVISION_ID, @l_CORE_NOTE = CORE_NOTE, @l_AUTH_STATUS = AUTH_STATUS,

      			@l_AMORT_START_DATE = AMORT_START_DATE, @l_MAKER_ID = MAKER_ID, @l_AMORT_MONTH_ASS_USE = AMORT_MONTH,

      			@l_AMORT_END_DATE = AMORT_END_DATE

      	FROM [dbo].[ASS_USE_MULTI_DT]

      	WHERE [USE_MULTI_ID] = @USE_MULTI_ID

      	--Lay thong tin so thang khau hao

      	SELECT @l_AMORT_MONTH = A.AMORT_MONTH, @l_ASS_AMORT_AMT = AMORT_AMT, @l_ENTRY_BOOKED = ENTRY_BOOKED,

      			@l_AMORT_AMT = A.AMORT_AMT, @l_BUY_PRICE = BUY_PRICE, @l_GROUP_ID = GROUP_ID,@l_TYPE_ID = A.[TYPE_ID]

      	FROM ASS_MASTER A 

      	WHERE A.ASSET_ID = @l_ASSET_ID

      	--TINH SO THANG THEO THUC TE KHI XUAT SU DUNG

      	SET @l_AMORT_MONTH = @l_AMORT_MONTH_ASS_USE

      	--IF @l_AMORT_MONTH_ASS_USE IS NULL--TRUONG HOP CCLD DO PHCQT XUAT

      	--BEGIN

      	--	SET @l_AMORT_END_DATE = NULL;		

      	--	SET @l_MONTHLY_AMT = 0;

      	--	SET @l_FIRST_AMORT_AMT = 0;

      	--	SET @l_AMORT_STATUS = 'VNM'

      	--END

      	--ELSE

      	--IF @l_AMORT_MONTH = 0

      	--BEGIN

      	--	SET @l_AMORT_END_DATE = NULL;		

      	--	SET @l_MONTHLY_AMT = 0;

      	--	SET @l_FIRST_AMORT_AMT = 0;

      	--	SET @l_AMORT_STATUS = 'KKH'

      	--END

      	--ELSE

      	--BEGIN

      	--	--Tinh ngay ket thuc khau hao

      	--	IF @l_TYPE_ID = 'TSCD'

      	--	BEGIN

      	--		IF @l_AMORT_END_DATE IS NULL OR @l_AMORT_END_DATE = ''

      	--			SET @l_AMORT_END_DATE = DATEADD(MONTH, @l_AMORT_MONTH, @l_AMORT_START_DATE) - 1

      	--		--CAP NHAT TRANG THAI KHAU HAO

      	--		SET @l_AMORT_STATUS = 'CKH'

      	--	END

      	--	ELSE

      	--	BEGIN			

      	--		DECLARE @l_ENDDATE_TEMP DATETIME = (CONVERT(VARCHAR(10),(YEAR(@l_AMORT_START_DATE))) + '-' + CONVERT(VARCHAR(10),MONTH(@l_AMORT_START_DATE))+'-' + '1')

      	--		SET @l_AMORT_END_DATE = DATEADD(MONTH, @l_AMORT_MONTH, @l_ENDDATE_TEMP ) - 1		

      	--		--CAP NHAT TRANG THAI KHAU HAO

      	--		SET @l_AMORT_STATUS = 'VNM'

      	--	END

      	--	--Tinh so tien khau hao thang dau tien va so tien khau hao hang thang

      	--	SET @l_MONTHLY_AMT = ROUND(@l_ASS_AMORT_AMT / @l_AMORT_MONTH, -3)

      	--	SET @l_FIRST_AMORT_AMT = ROUND(@l_MONTHLY_AMT/dbo.[FN_GetDaysInMonth](@l_AMORT_START_DATE) * (DATEDIFF(day,@l_AMORT_START_DATE, dbo.FN_GetLastDayOfMonth(@l_AMORT_START_DATE)) + 1), -3)

      	--	----CAP NHAT TRANG THAI KHAU HAO

      	--	--SET @l_AMORT_STATUS = 'CKH'

      	--END

      		--COMMIT TRANSACTION

      		--BEGIN TRANSACTION

      		IF @@Error <> 0 GOTO ABORT

      		DECLARE @l_AMORT_RATE DECIMAL(18,2)

      		IF @l_AMORT_MONTH > 0 AND @l_AMORT_MONTH IS NOT NULL

      			SET @l_AMORT_RATE = ROUND((100 /@l_AMORT_MONTH) * 12, 2)

      		ELSE 

      		BEGIN

      			SET @l_AMORT_RATE = 0

      			IF(@l_TYPE_ID = 'CCLD')

      				SET @l_AMORT_MONTH = 1

      		END

      		--Update tai san da xuat su dung

      		UPDATE ASS_MASTER

      		SET	BRANCH_ID = @l_BRANCH_ID,

      			DEPT_ID = @l_DEPT_ID,

      			EMP_ID = @l_EMP_ID,

      			DIVISION_ID = @l_DIVISION_ID,

      		--	AMORT_START_DATE = @l_AMORT_START_DATE,

      		--	AMORT_END_DATE = @l_AMORT_END_DATE,

      		--	FIRST_AMORT_AMT = @l_FIRST_AMORT_AMT,

      		--	AMORT_MONTH = @l_AMORT_MONTH,

      		--	MONTHLY_AMORT_AMT = @l_MONTHLY_AMT,

      		--	AMORTIZED_AMT = ISNULL(AMORTIZED_AMT,0),

      		--	AMORTIZED_MONTH = 0,

      		--	AMORT_STATUS = @l_AMORT_STATUS,--'CKH', --Update trang thai cho khau hao

      			USE_DATE = CONVERT(DATETIME, @sToday, 103),

            USE_DATE_KT = NULL

      		--	ENTRY_BOOKED = @l_ENTRY_BOOKED,

      		--	AMORT_RATE = @l_AMORT_RATE

      		WHERE ASSET_ID = @l_ASSET_ID

      --		UPDATE ASS_MASTER

      --		SET	USE_DATE = CONVERT(DATETIME, @sToday, 103),

      --			ENTRY_BOOKED = @l_ENTRY_BOOKED,

      --			AMORT_RATE = @l_AMORT_RATE

      --		WHERE ASSET_ID = @l_ASSET_ID

      		IF @@Error <> 0 GOTO ABORT

      	--Phat sinh Asset_ID

      	EXEC SYS_CodeMasters_Gen 'ASS_MASTER_HIST', @l_ASSHIST_ID out

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

      	  INSERT INTO ASS_MASTER_HIST

      		SELECT @l_ASSHIST_ID, a.*

      		FROM ASS_MASTER a

      		where a.ASSET_ID = @l_ASSET_ID

      	IF @@Error <> 0 GOTO ABORT

      		/***THIEUVQ - 10/09/2014 CAP NHAT LAI DON VI KHI XUAT SU DUNG NEU TS LA XE***/

      		--UPDATE CAR_MASTER SET BRANCH_ID = @l_BRANCH_ID WHERE ASSET_ID = @l_ASSET_ID

      		--Insert phan xuat kho

      		SELECT @l_WAHDT_ID = WAHDT_ID FROM ASS_WAREHOUSE_DT A WHERE A.ASSET_ID = @l_ASSET_ID AND A.STATUS='I'

      		UPDATE ASS_WAREHOUSE_DT

      		SET OUT_DATE = GETDATE(),

      			STATUS = 'O'

      		WHERE WAHDT_ID = @l_WAHDT_ID

      		IF @@Error <> 0 GOTO ABORT

      		----Insert bang ASS_LOCATION_HIST

      		EXEC SYS_CodeMasters_Gen 'ASS_LOCATION_HIST', @l_LOCHIST_ID out

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

      		INSERT INTO ASS_LOCATION_HIST

      		(

      			LOCHIST_ID, ASSET_ID, USE_START_DT, USE_END_DT, BRANCH_ID, DEPT_ID, 

      			EMP_ID, LOCATION, ISLEAF, PARENT_ID

      		)

      		VALUES

      		(

      			@l_LOCHIST_ID, @l_ASSET_ID, GETDATE(), NULL, @l_BRANCH_ID, @l_DEPT_ID,

      			@l_EMP_ID, '', 'Y', NULL

      		)

      		IF @@Error <> 0 GOTO ABORT

      		--INSERT VAO BANG ASS_TRANSACTIONS

      		INSERT INTO ASS_TRANSACTIONS(ASSET_ID, TRN_ID, TRN_TYPE, TRN_DATE, RECORD_STATUS, AUTH_STATUS, 	

      				[MAKER_ID], [CREATE_DT], [CHECKER_ID], [APPROVE_DT],ASSHIST_ID, LOCHIST_ID

      		)VALUES

      		(

      			@l_ASSET_ID, @USE_MULTI_ID, 'ADD_USE', CONVERT(DATETIME, @sToday, 103), '1', 'A', 

      			@l_MAKER_ID, CONVERT(DATETIME, @sToday, 103), @p_CHECKER_ID, 	CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@l_ASSHIST_ID,@l_LOCHIST_ID

      		)

      		IF @@Error <> 0 GOTO ABORT	

      		FETCH NEXT FROM DataCusor INTO  @USE_MULTI_ID,@ASSET_ID

      		END

      		CLOSE DataCusor

      		DEALLOCATE DataCusor	

          --START PHUCVH 07/10/22 UPDATE ALLOCATED PHIẾU YÊU CẦU DT

          DECLARE @TBL_CHECK_UPD TABLE (REQDT_ID VARCHAR(15), REQ_DOC_ID VARCHAR(15), ASS_GROUP_ID VARCHAR(15), ASS_ID VARCHAR(15), REQ_DT_TYPE VARCHAR(20), TYPE_XL VARCHAR(20))

          INSERT INTO @TBL_CHECK_UPD

          SELECT DISTINCT A.REQDT_ID, A.REQ_DOC_ID, A.ASS_GROUP_ID, A.ASS_ID, A.REQ_DT_TYPE, A.TYPE_XL

          FROM ASS_USE_MULTI_DT B

          LEFT JOIN TR_REQUEST_SHOP_DOC_DT A ON B.REQ_ID = A.REQ_DOC_ID 

          WHERE B.USER_MASTER_ID = @p_USER_MASTER_ID AND B.REQ_ID IS NOT NULL AND B.REQ_ID <> ''

                AND(A.REQ_DT_TYPE = 'BUYNEW' 

                  OR (A.REQ_DT_TYPE = 'XKSD' AND A.TYPE_XL = 'CPTK')) 

          DECLARE @C_REQDT_ID VARCHAR(15), @C_REQ_DOC_ID VARCHAR(15), @C_ASS_GROUP_ID VARCHAR(15), @C_ASS_ID VARCHAR(15), @C_REQ_DT_TYPE VARCHAR(15), @C_TYPE_XL VARCHAR(20)

          DECLARE @IS_MULTI_GROUP VARCHAR(1) = '0'

          DECLARE DATA_CURSOR_CHECK_UPD CURSOR FOR

          SELECT * FROM @TBL_CHECK_UPD

          OPEN DATA_CURSOR_CHECK_UPD

          FETCH NEXT FROM DATA_CURSOR_CHECK_UPD INTO

          @C_REQDT_ID,@C_REQ_DOC_ID,@C_ASS_GROUP_ID,@C_ASS_ID,@C_REQ_DT_TYPE,@C_TYPE_XL

          DECLARE @ALLOCATED INT, @ALLOCATED_CHECK INT, @ALLOCATED_OLD INT, @QTY_ETM INT

          WHILE @@FETCH_STATUS = 0

          BEGIN

            SET @IS_MULTI_GROUP = '0'

            DELETE @TBL_GROUP_CODE_ASS_USE

            INSERT INTO @TBL_GROUP_CODE_ASS_USE (GROUP_ID)

            SELECT A.GROUP_ID FROM ASS_GROUP A WHERE A.GROUP_CODE IN (SELECT B.GROUP_CODE FROM ASS_GROUP B WHERE B.GROUP_ID  = @C_ASS_GROUP_ID)

            IF((SELECT COUNT(*) FROM @TBL_GROUP_CODE_ASS_USE WHERE GROUP_ID IN (SELECT trsdd.ASS_GROUP_ID FROM TR_REQUEST_SHOP_DOC_DT trsdd WHERE trsdd.REQ_DOC_ID = @C_REQ_DOC_ID AND(trsdd.REQ_DT_TYPE = 'BUYNEW' 

                      OR (trsdd.REQ_DT_TYPE = 'XKSD' AND trsdd.TYPE_XL = 'CPTK')) )) > 1)

            BEGIN

                SET @IS_MULTI_GROUP = '1'

            END

              IF (@C_REQ_DT_TYPE = 'BUYNEW')

              BEGIN

              SET @ALLOCATED = (SELECT SUM(TMP.COUNT) FROM (

                                    (SELECT COUNT(*) AS COUNT

                                    FROM ASS_USE_MULTI_DT A

                                    LEFT JOIN ASS_MASTER C ON A.ASSET_ID = C.ASSET_ID

                                    WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID

                                    GROUP BY A.REQ_ID, C.GROUP_ID, C.REQ_ID

                                    HAVING A.REQ_ID = @C_REQ_DOC_ID

                                      AND C.GROUP_ID IN (SELECT GROUP_ID FROM @TBL_GROUP_CODE_ASS_USE WHERE ((@IS_MULTI_GROUP = '1' AND C.GROUP_ID = @C_ASS_GROUP_ID) OR @IS_MULTI_GROUP = '0'))

                                      AND ((@C_REQ_DT_TYPE = 'BUYNEW' AND C.REQ_ID = @C_REQ_DOC_ID))))TMP)

              END

              ELSE IF (@C_TYPE_XL = 'CPTK')

              BEGIN

              SET @ALLOCATED = (SELECT SUM(TMP.COUNT) FROM (

                                (SELECT COUNT(*) AS COUNT

                                FROM ASS_USE_MULTI_DT A

                                LEFT JOIN ASS_MASTER C ON A.ASSET_ID = C.ASSET_ID

                                WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID

                                GROUP BY A.REQ_ID, C.GROUP_ID, C.REQ_ID

                                HAVING A.REQ_ID = @C_REQ_DOC_ID

                                  AND C.GROUP_ID IN (SELECT GROUP_ID FROM @TBL_GROUP_CODE_ASS_USE WHERE ((@IS_MULTI_GROUP = '1' AND C.GROUP_ID = @C_ASS_GROUP_ID) OR @IS_MULTI_GROUP = '0'))

                                  AND ((@C_TYPE_XL = 'CPTK' AND (C.REQ_ID IS NULL OR C.REQ_ID = '')))))TMP)

              END

              SELECT TOP 1 @ALLOCATED_OLD = trsdd.ALLOCATED, @QTY_ETM = trsdd.QTY_ETM FROM TR_REQUEST_SHOP_DOC_DT trsdd WHERE trsdd.REQDT_ID = @C_REQDT_ID  

              IF(@QTY_ETM < (ISNULL(@ALLOCATED_OLD,0) + ISNULL(@ALLOCATED,0)))

              BEGIN

              	ROLLBACK TRANSACTION

                CLOSE DATA_CURSOR_CHECK_UPD

                DEALLOCATE DATA_CURSOR_CHECK_UPD

              	SELECT '-1' as Result, '' USER_MASTER_ID, N'Số lượng cấp phát vượt yêu cầu của đơn vị' ErrorDesc

              	RETURN '-1'

              END

              UPDATE TR_REQUEST_SHOP_DOC_DT 

              SET ALLOCATED = ALLOCATED + ISNULL(@ALLOCATED,0)

              WHERE REQDT_ID = @C_REQDT_ID

              IF @@Error <> 0 GOTO ABORT2

              FETCH NEXT FROM DATA_CURSOR_CHECK_UPD INTO

              @C_REQDT_ID,@C_REQ_DOC_ID,@C_ASS_GROUP_ID,@C_ASS_ID,@C_REQ_DT_TYPE,@C_TYPE_XL

          END

          CLOSE DATA_CURSOR_CHECK_UPD

          DEALLOCATE DATA_CURSOR_CHECK_UPD

          --END PHUCVH 07/10/22 UPDATE ALLOCATED PHIẾU YÊU CẦU DT

    --END MOVE TỪ APPROVE HS QUA

    --PHUCVH 11/10/22 DONE PHIẾU YÊU CẦU

      DECLARE MYCURSOR CURSOR FOR

      SELECT aumd.REQ_ID 

      FROM ASS_USE_MULTI_DT aumd 

      WHERE aumd.USER_MASTER_ID= @p_USER_MASTER_ID AND aumd.REQ_ID IS NOT NULL AND aumd.REQ_ID <> ''

      GROUP BY aumd.REQ_ID

      OPEN MYCURSOR

      DECLARE @p_REQ_ID VARCHAR(20)

      FETCH NEXT FROM MYCURSOR INTO @p_REQ_ID

      WHILE @@FETCH_STATUS = 0 

      BEGIN

        IF(NOT EXISTS(SELECT TOP 1 A.REQDT_ID 

                  FROM TR_REQUEST_SHOP_DOC_DT A 

                  WHERE A.REQ_DOC_ID = @p_REQ_ID 

                        AND ( A.QTY_ETM <> A.ALLOCATED OR (A.ALLOCATED IS NULL OR A.ALLOCATED = '' OR A.ALLOCATED = 0))

                        AND ((A.REQ_DT_TYPE = 'XKSD' AND A.TYPE_XL = 'CPDC')

                              OR (A.REQ_DT_TYPE = 'XKSD' AND A.TYPE_XL = 'CPTK')

                              OR (A.REQ_DT_TYPE = 'BUYNEW'))

                    ))

        BEGIN

          UPDATE TR_REQUEST_SHOP_DOC SET IS_DONE = '1', STATUS = 'DONE' WHERE REQ_ID = @p_REQ_ID

          UPDATE PL_REQUEST_PROCESS SET STATUS = 'P' WHERE PROCESS_ID = 'APPROVE' AND REQ_ID = @p_REQ_ID

          INSERT INTO PL_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, IS_LEAF)

          VALUES (@p_REQ_ID,'DONE','C','Y')

        END

        IF @@Error <> 0 GOTO ABORT3

        FETCH NEXT FROM MYCURSOR INTO @p_REQ_ID

      END

      CLOSE MYCURSOR

      DEALLOCATE MYCURSOR

COMMIT TRANSACTION

SELECT '0' as Result, @l_USER_CONFIRM_MASTER_ID  USER_CONFIM_MASTER_ID, N'Xác nhận xuất sử dụng tài sản thành công' ErrorDesc, @p_USER_MASTER_ID USER_MASTER_ID

RETURN '0'

ABORT:

BEGIN

		CLOSE XmlData

		DEALLOCATE XmlData

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End

ABORT2:

BEGIN

		CLOSE DATA_CURSOR_CHECK_UPD

		DEALLOCATE DATA_CURSOR_CHECK_UPD

		ROLLBACK TRANSACTION

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

		RETURN '-1'

END

ABORT3:

BEGIN

		CLOSE MYCURSOR

		DEALLOCATE MYCURSOR

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End

GO

ALTER PROCEDURE dbo.ASS_COLLECT_CONFIRM_MASTER_Ins

@p_BRANCH_ID	varchar(15)  = NULL,

@p_DEP_ID	varchar(15)  = NULL,

@p_COLLECT_DT	VARCHAR(20) = NULL,

@p_USER_COLLECT	nvarchar(200)  = NULL,

@p_MAKER_ID	varchar(100)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_COL_MULTI_MASTER_ID varchar(20) = null

AS

IF(EXISTS(SELECT 1 FROM ASS_COLLECT_CONFIRM_MASTER WHERE COL_MULTI_MASTER_ID = @p_COL_MULTI_MASTER_ID))

BEGIN

  SELECT '-1' as Result, N'Phiếu thu hồi đã được xác nhận' ErrorDesc

  RETURN '-1'

END

BEGIN TRANSACTION

DECLARE @l_COL_MULTI_MASTER_CONFIRM_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'ASS_COLLECT_CONFIRM_MASTER', @l_COL_MULTI_MASTER_CONFIRM_ID out

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

		INSERT INTO ASS_COLLECT_CONFIRM_MASTER

		(

			[COL_MULTI_MASTER_CONFIRM_ID],

			[COL_MULTI_MASTER_ID],

			[BRANCH_ID],

			[DEP_ID],

			[COLLECT_DT],

			[MAKER_ID],

			[CONFIRM_DT],

			[CONFIRM_STATUS]			

		)

		VALUES

		(

			@l_COL_MULTI_MASTER_CONFIRM_ID,

			@p_COL_MULTI_MASTER_ID ,

			@p_BRANCH_ID,

			@p_DEP_ID,

			CONVERT(DATETIME, @p_COLLECT_DT, 103),

			@p_MAKER_ID,

			GETDATE(),

			'Y'

		)

		IF @@Error <> 0 GOTO ABORT

		-- GIANT Insert to table PL_PROCESS

		INSERT INTO dbo.PL_PROCESS

					(

					    REQ_ID,

					    PROCESS_ID,

					    CHECKER_ID,

					    APPROVE_DT,

					    PROCESS_DESC,

					    NOTES

					)

					VALUES

					(   @p_COL_MULTI_MASTER_ID,       

						'CONFIRM',

					    @p_MAKER_ID,        

					    GETDATE(), 

					    N'Xác nhận phiếu thu hồi tài sản ' ,      

					    N'Xác nhận phiếu thu hồi tài sản thành công'       

					 )

COMMIT TRANSACTION

SELECT '0' as Result, @l_COL_MULTI_MASTER_CONFIRM_ID  CONFIRM_COL_MULTI_MASTER_ID,@p_COL_MULTI_MASTER_ID COL_MULTI_MASTER_ID, N'Xác nhận bàn giao tài sản thu hồi thành công' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		CLOSE XmlData

		DEALLOCATE XmlData

		ROLLBACK TRANSACTION

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

		RETURN '-1'

END