AMS - VB

ALTER PROCEDURE [dbo].[MW_REQ_CloseREQ]

  @p_REQ_ID VARCHAR(15),

  @p_CHECKER_ID VARCHAR(200),

  @p_APPROVE_DT VARCHAR(20)

AS 

  DECLARE @Auth VARCHAR(1),@CheckerId VARCHAR(15),@status VARCHAR(20), @BranchId VARCHAR(15), @DepId VARCHAR(15),@IS_DONE VARCHAR(1)

  SELECT @Auth = mr.AUTH_STATUS,

         @CheckerId = mr.CHECKER_ID,

         @status = mr.STATUS,

         @IS_DONE = mr.IS_DONE

  FROM MW_REQ mr 

  WHERE mr.REQ_ID = @p_REQ_ID

  IF(@Auth = 'A' AND @IS_DONE = '1')

  BEGIN

      SELECT '0' as Result, '' ErrorDesc, 'Phiếu đã được đóng trước đó' NOTIFICATION

		  RETURN 0

  END

BEGIN TRANSACTION

  IF(@status = 'DONE')

    BEGIN  

    	  SET @Auth = 'A'

        SET @status = 'DONE'

         IF(EXISTS(SELECT 1 FROM MW_REQ_PROCESS mrp WHERE mrp.REQ_ID = @p_REQ_ID AND ISNULL(mrp.AUTH_STATUS,'') <> 'A'))

        BEGIN

          GOTO ABORT3

        END

        SELECT @BranchId =  tugr.BRANCH_ID, @DepId =  tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) tugr

        UPDATE MW_REQ 

        SET AUTH_STATUS = @Auth, STATUS = @status, APPROVE_DT = GETDATE(), CHECKER_ID = @CheckerId ,IS_DONE = '1'

        WHERE REQ_ID = @p_REQ_ID	IF @@Error <> 0 GOTO ABORT

        UPDATE MW_REQUEST_PROCESS

        SET STATUS = 'P'

        WHERE REQ_ID = @p_REQ_ID

        INSERT INTO MW_REQUEST_PROCESS (REQ_ID,CHECKER_ID, PROCESS_ID,PROCESS_NAME,BRANCH_ID,DEP_ID,RECEPTION_DT, STATUS, PARENT_PROCESS_ID, NOTES) 

        VALUES (@p_REQ_ID,@p_CHECKER_ID, @status,N'Hoàn thành phiếu',@BranchId,@DepId,GETDATE() , 'P', 'SENDAPP', N'Đóng phiếu yêu cầu ở bước đơn vị đầu mối')

    END

  ELSE

    BEGIN

        GOTO ABORT2

    END

  INSERT INTO dbo.MW_PROCESS

							(

								REQ_ID,

								PROCESS_ID,

								CHECKER_ID,

								APPROVE_DT,

								PROCESS_DESC,

								NOTES

							)

							VALUES

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

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

								@p_CHECKER_ID,        -- CHECKER_ID - varchar(100)

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

								N'Đóng phiếu yêu cầu',       -- PROCESS_DESC - nvarchar(1000)

								N'Đóng phiếu yêu cầu thành công'        -- NOTES - nvarchar(1000)

							)

COMMIT TRANSACTION

SELECT '0' as Result, N'Phiếu '+ @p_REQ_ID + N' Được đóng thành công' NOTIFICATION, '' ErrorDesc

RETURN '0'

ABORT: 

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

END

ABORT2:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, N'Phiếu ' + @p_REQ_ID + N' Không thể đóng phiếu khi chưa đén bước xử lý này hoặc không được đóng phiếu' ErrorDesc

		RETURN '-1'

END

ABORT3:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, N'Phiếu đã có cấp xử lý tiếp theo không thể hủy phiếu tại bước này' ErrorDesc

		RETURN '-1'

End

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REQ_SendApp]'

GO

ALTER PROCEDURE [dbo].[MW_REQ_SendApp]

  @p_REQ_ID VARCHAR(20),

  @p_REQ_CODE varchar(15),

  @p_AUTH_STATUS VARCHAR(1),

  @p_STATUS VARCHAR(15),

  @p_CHECKER_ID VARCHAR(25)

AS

BEGIN TRANSACTION

--  DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USER_LOGIN)

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

--  DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))

--  

--  INSERT INTO @ROLE_LOGIN

--  SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr

--  

--  SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)

--  SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)

IF(EXISTS(SELECT 1 FROM MW_REQ WHERE REQ_ID = @p_REQ_ID AND STATUS = 'DONE'))

BEGIN

    ROLLBACK TRANSACTION

    SELECT '-1' as Result,'' REQ_ID,''REQ_ID,N'Phiếu yêu cầu đã được phê duyệt hoàn tất. Gửi phê duyệt thất bại.' NOTICATION

    RETURN '-1'

END

DECLARE @STATUS VARCHAR(10), @WARE_CODE VARCHAR(20)

SELECT TOP 1 @STATUS = A.STATUS, @WARE_CODE = C.WARE_CODE

FROM MW_REQ A 

LEFT JOIN CM_REQ_TYPE B ON A.REQ_TYPE = B.REQ_TYPE_ID

LEFT JOIN CM_WARE C ON B.WARE_TYPE = C.WARE_ID

WHERE A.REQ_ID = @p_REQ_ID

IF(EXISTS(SELECT REQ_ID FROM MW_REQ WHERE REQ_ID = @p_REQ_ID AND AUTH_STATUS = @p_AUTH_STATUS ) )

	BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' AS Result , ''  REQ_CODE, N'Phiếu yêu cầu đã được gửi phê duyệt trước đó.' NOTICATION

			RETURN '-1'

	END

    IF(NOT EXISTS(SELECT REQ_ID FROM MW_REQ_DT WHERE REQ_ID = @p_REQ_ID))

    BEGIN

        ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  REQ_ID, N'Nội dung chi tiết đơn vị yêu cầu không được để trống.' NOTICATION

  			RETURN '-1'

    END

    IF( EXISTS( SELECT MATERIAL_ID,BRANCH_USE,DEP_USE

                FROM MW_REQ_DT

                WHERE REQ_ID = @p_REQ_ID

                GROUP BY MATERIAL_ID, BRANCH_USE,DEP_USE HAVING COUNT(*)>1))

    BEGIN

        ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  REQ_ID, N'Nội dung chi tiết đơn vị yêu cầu bị  trùng lặp: loại vật liệu và đơn vị sử dụng.' NOTICATION

  			RETURN '-1'

    END

   IF(NOT EXISTS(SELECT * FROM MW_REQ_PROCESS mrp WHERE mrp.REQ_ID = @p_REQ_ID) AND (SELECT A.STATUS FROM MW_REQ A WHERE REQ_ID = @p_REQ_ID) = 'DMTN')

    BEGIN

       	ROLLBACK TRANSACTION

    		SELECT '-1' as Result,'' REQ_ID,''REQ_ID,N'Cấp xử lý phải được lưu lại trước khi gửi duyệt' NOTICATION

    		RETURN '-1'

    END

    IF((SELECT A.STATUS FROM MW_REQ A WHERE A.REQ_ID = @p_REQ_ID) = 'ADDNEW')

    BEGIN

      SET @p_AUTH_STATUS = 'U'

      SET @p_STATUS = 'SendApp'

      UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, STATUS = @p_STATUS WHERE REQ_ID = @p_REQ_ID

      DECLARE @p_BRANCH_ID varchar(50) = (SELECT BRANCH_ID FROM MW_REQ WHERE REQ_ID = @p_REQ_ID)

      DECLARE @p_DEP_ID VARCHAR(50) = (SELECT DEP_ID FROM MW_REQ WHERE REQ_ID = @p_REQ_ID)

      UPDATE MW_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'ADDNEW'

--      INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID)

--      VALUES(@p_REQ_CODE,'SENDAPP',N'GỬI TĐV DUYỆT','P','GDDV',@p_BRANCH_ID,'ADDNEW',@p_DEP_ID, 'Approve',GETDATE(),@p_CHECKER_ID)

--  	  INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,NOTES)

--      VALUES(@p_REQ_CODE,'APPNEW',N'TDV DUYỆT','C','GDDV',@p_BRANCH_ID,'ADDNEW',@p_DEP_ID, 'Approve',GETDATE(), N'CHỜ DUYỆT')

      INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID,NOTES)

      VALUES(@p_REQ_ID,'SENDAPP',N'Gửi Trưởng đơn vị phê duyệt','P','GDDV',@p_BRANCH_ID,'ADDNEW',@p_DEP_ID, 'Approve',GETDATE(),@p_CHECKER_ID, N'Nhân viên tạo phiếu và gửi đến Trưởng đơn vị phê duyệt')

      --UPDATE MW_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_REQ_CODE AND PROCESS_ID = 'APPNEW'

    END

    --ĐỐI VỚI 3 KHO THẺ. TỪ TTKD GỬI LÊN, BẮT BUỘC PHẢI CHỌN CẤP XỬ LÝ TIẾP THEO

    IF(@STATUS = 'DMTN' AND @WARE_CODE IN ('02TE','03CT','04VT'))

    BEGIN

    	  IF(NOT EXISTS(SELECT 1 FROM MW_REQ_PROCESS WHERE REQ_ID = @p_REQ_ID))

        BEGIN

            ROLLBACK TRANSACTION

        		SELECT '-1' as Result,'' REQ_ID,''REQ_ID,N'Phiếu yêu cầu đối với kho vật liệu thẻ, phôi thẻ trắng, phôi thẻ cá thể hóa, bắt buộc chọn cấp xử lý tiếp theo.' NOTICATION

        		RETURN '-1'

        END

    END

    IF((SELECT A.STATUS FROM MW_REQ A WHERE REQ_ID = @p_REQ_ID) = 'DMTN' AND EXISTS(SELECT * FROM MW_REQ_PROCESS WHERE REQ_ID = @p_REQ_ID))

    BEGIN

      IF(NOT EXISTS(SELECT * FROM MW_REQ_PROCESS mrp WHERE mrp.REQ_ID = @p_REQ_ID))

      BEGIN

          GOTO ABORT2

      END

      SET @p_AUTH_STATUS = 'U'

      SET @p_STATUS = 'LEVEL'

      UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, STATUS = @p_STATUS WHERE REQ_ID = @p_REQ_ID

      UPDATE MW_REQUEST_PROCESS SET STATUS = 'P' WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DMTN'

      INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID,NOTES)

      VALUES(@p_REQ_ID,'DMTN',N'Gửi các cấp xử lý tiếp theo','P',@p_BRANCH_ID,'APPNEW',@p_DEP_ID, 'SendApp',GETDATE(),@p_CHECKER_ID, N'Đầu mối tiếp nhập thêm cấp xử lý và gửi đến các cấp xử lý')

      INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID,PROCESS_NAME, STATUS, BRANCH_ID, PARENT_PROCESS_ID, IS_LEAF, IS_HAS_CHILD,DEP_ID, DVKD_USER_APP,  PROCESS_TYPE, STAGE, CHECKER_ID, NOTES, RECEPTION_DT)

      SELECT mrp.REQ_ID, 'LEVEL',N'CẤP XỬ LÝ TIẾP THEO', CASE WHEN ROW_NUMBER() OVER	(ORDER BY mrp.STAGE) = 1 THEN 'C'  ELSE 'U' END, 'DV0001', 'DMTN', 

      ROW_NUMBER() OVER	(ORDER BY mrp.STAGE), ROW_NUMBER() OVER	(ORDER BY mrp.STAGE) + 1,(SELECT a.SECUR_CODE FROM TL_USER a WHERE a.TLNANME = mrp.TLNAME),mrp.TLNAME,'Approve',STAGE, mrp.TLNAME, N'Chờ duyệt', DATEADD(SECOND,1,GETDATE())

      FROM MW_REQ_PROCESS mrp WHERE mrp.REQ_ID = @p_REQ_ID

    END

INSERT INTO dbo.MW_PROCESS

							(

								REQ_ID,

								PROCESS_ID,

								CHECKER_ID,

								APPROVE_DT,

								PROCESS_DESC,

								NOTES

							)

							VALUES

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

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

								@p_CHECKER_ID,        -- CHECKER_ID - varchar(100)

								GETDATE(), -- APPROVE_DT - datetime

								N'Nhân viên gửi phê duyệt',       -- PROCESS_DESC - nvarchar(1000)

								N'Nhân viên gửi phê duyệt thành công'        -- NOTES - nvarchar(1000)

							)

IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result,'' REQ_ID,'' REQ_ID, N'Phiếu yêu cầu: '+(SELECT REQ_ID FROM MW_REQ WHERE REQ_ID  = @p_REQ_ID)+N' đã được gửi phê duyệt thành công.' NOTICATION, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result,'' REQ_ID,''REQ_ID, '' NOTICATION

		RETURN '-1'

END

ABORT2:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result,'' REQ_ID,''REQ_ID,N'Cấp xử lý phải được lưu lại trước khi gửi duyệt' NOTICATION

		RETURN '-1'

End

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REQ_Upd]'

GO

ALTER PROCEDURE [dbo].[MW_REQ_Upd]

  @p_REQ_ID VARCHAR(20)= NULL,

  @p_REQ_CODE VARCHAR(15)= NULL,

  @p_REQ_TYPE VARCHAR(25) = NULL,

  @p_BRANCH_ID VARCHAR(25) = NULL,

  @p_DEP_ID VARCHAR(25) = NULL,

  @p_PROMOTION_ID VARCHAR(25)= NULL,

  @p_REGARDS_TO NVARCHAR(1000) = NULL,

  @p_REQUEST_DOC_CONTENT NVARCHAR(1000) = NULL,

  @p_AUTH_STATUS VARCHAR(1)= NULL,

  @p_QUANTITY_ALLOCATION VARCHAR(1000),

  @p_STATUS VARCHAR(15)= NULL,

  @p_RECORD_STATUS varchar(15)= NULL,

  @p_MAKER_ID	varchar(100)  = NULL,

  @p_CREATE_DT VARCHAR(30) = NULL,

  @p_APPROVE_DT varchar(25)= NULL,

  @p_CHECKER_ID varchar(100)= NULL,

  @p_BRANCH_RECEIVE_ID VARCHAR(50) = NULL,

  @p_BRANCH_RECEIVE_NAME NVARCHAR(1000) =NULL,

  @p_DEP_RECEIVE_ID VARCHAR(50) = NULL,

  @p_DEP_RECEIVE_NAME NVARCHAR(1000)= NULL,

  @p_XmlData XML = NULL,

  @p_XmlDataProcess XML = NULL

AS 

--Validation is here

DECLARE @ERRORSYS NVARCHAR(1500) = '' 

DECLARE @INDEX INT =1

--KHAI BAO BIEN CURSOR DETAIL

DECLARE @REQ_DT_ID varchar(15)= NULL,

        @REQ_ID VARCHAR(20)=NULL,

        @REQ_CODE varchar(15) =NULL,

        @MATERIAL_GROUP VARCHAR (200)= NULL,

        @MATERIAL_ID nvarchar(200) =NULL,

        @UNIT_ID VARCHAR(15) =NULL, 

        @QUANTITY_REQ DECIMAL(18,2)= NULL,

        @QUANTITY_ALLOCATION DECIMAL(18,2) = NULL,

        @REASON NVARCHAR(1000) =NULL,

        @DEP_USE VARCHAR(15) = NULL,

        @BRANCH_USE VARCHAR(15) = NULL,

        @BRANCH_NAME NVARCHAR(1000) = NULL,

        @ALLOCATED DECIMAL(18,2) = NULL

--KHAI BAO BIEN CURSOR Process

DECLARE @REQ_PROCESS_ID varchar(15),

        @TLNAME VARCHAR (200)= NULL,

        @PROCESS_STATUS nvarchar(200) =NULL,

        @NOTES NVARCHAR(1000) =NULL,

        @REQ_CODE_PROCESS varchar(15) =NULL,

        @STAGE INT

--

DECLARE @hdoc INT;

BEGIN TRANSACTION

IF(@p_STATUS = 'SendApp')

	BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' AS Result , ''  REQ_ID, N'Phiếu yêu cầu không được chỉnh sửa. Vui lòng kiểm tra lại' ErrorDesc

			RETURN '-1'

	END

IF(@p_AUTH_STATUS = 'R')

BEGIN

    DECLARE @USER_LOGIN VARCHAR(20) = @p_MAKER_ID

    IF((@p_STATUS = 'R_MAKERID' OR @p_STATUS = 'R_SENDAPP') AND (SELECT mr.MAKER_ID FROM MW_REQ mr WHERE mr.REQ_ID = @p_REQ_ID) != @USER_LOGIN)

    BEGIN

        ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  REQ_ID, N'Không được phép chỉnh phiếu, phiếu yêu cầu được trả về cho người tạo chỉnh sửa' ErrorDesc

  			RETURN '-1'

    END

    IF((@p_STATUS = 'R_DMTN') AND (((SELECT mr.MAKER_ID FROM MW_REQ mr WHERE mr.REQ_ID = @p_REQ_ID) = @USER_LOGIN) 

                                                                        OR ((SELECT mr.CHECKER_ID FROM MW_REQ mr WHERE mr.REQ_ID = @p_REQ_ID) = @USER_LOGIN)))

    BEGIN

        ROLLBACK TRANSACTION

  			SELECT '-1' AS Result , ''  REQ_ID, N'Không được phép chỉnh phiếu, phiếu yêu cầu được trả về cho đầu mối chỉnh sửa' ErrorDesc

  			RETURN '-1'

    END

END

---

		EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

		DECLARE XmlData CURSOR FOR

		SELECT *

		FROM

			OPENXML (@hdoc, '/Root/MWREQDT', 2)

			WITH (	REQ_DT_ID varchar(15),

              REQ_ID varchar(20),

              REQ_CODE varchar(15),

              MATERIAL_GROUP VARCHAR (200),

              MATERIAL_ID nvarchar(200),

              UNIT_ID VARCHAR(15), 

              QUANTITY_REQ DECIMAL(18,2),

              QUANTITY_ALLOCATION DECIMAL(18,2),

              REASON NVARCHAR(1000),

              DEP_USE VARCHAR(15),

              BRANCH_USE VARCHAR(15),

              BRANCH_NAME NVARCHAR(1000),

              ALLOCATED DECIMAL(18,2)

              );

		OPEN XmlData;

---

		EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlDataProcess;

		DECLARE XmlDataProcess CURSOR FOR

		SELECT * FROM OPENXML (@hdoc, '/Root/MWREQPROCESS', 2)

    WITH (    REQ_PROCESS_ID VARCHAR(15),

              TLNAME varchar(15),

              PROCESS_STATUS varchar(15),

              NOTES NVARCHAR(1000),

              REQ_ID varchar(15),

              STAGE INT);

		OPEN XmlDataProcess;

    IF(@p_STATUS = 'ADDNEW')

    BEGIN

       UPDATE MW_REQ SET  REQ_TYPE = @p_REQ_TYPE, 

                          BRANCH_ID = @p_BRANCH_ID, 

                          REGARDS_TO = @p_REGARDS_TO, 

                          REQUEST_DOC_CONTENT = @p_REQUEST_DOC_CONTENT, 

                          PROMOTION_ID = @p_PROMOTION_ID, DEP_ID = @p_DEP_ID,

                          BRANCH_RECEIVE_ID=@p_BRANCH_RECEIVE_ID,

                          BRANCH_RECEIVE_NAME=@p_BRANCH_RECEIVE_NAME,

                          DEP_RECEIVE_ID = @p_DEP_RECEIVE_ID,

                          DEP_RECEIVE_NAME=@p_DEP_RECEIVE_NAME 

                          WHERE REQ_ID = @p_REQ_ID

          DELETE MW_REQ_DT WHERE REQ_ID = @p_REQ_ID

          --

          FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

      		WHILE @@FETCH_STATUS = 0

      		BEGIN

            EXEC SYS_CodeMasters_Gen 'MW_REQ_DT', @REQ_DT_ID out

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

            IF(@QUANTITY_REQ=0)

            BEGIN

            	ROLLBACK TRANSACTION

        			CLOSE XmlData;

        			DEALLOCATE XmlData;

        			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': số lượng yêu cầu không thể đáp ứng.' ErrorDesc

        			RETURN '-1'

            END

            IF(ISNULL(@p_PROMOTION_ID,'') <> '' AND @MATERIAL_ID NOT IN (SELECT cpd.MATERIAL_ID FROM CM_PROMOTION_DT cpd WHERE cpd.PROMOTION_ID = @p_PROMOTION_ID))

            BEGIN

            	  ROLLBACK TRANSACTION

          			CLOSE XmlData;

          			DEALLOCATE XmlData;

          			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': loại vật liệu bạn chọn không có trong danh sách chương trình.' ErrorDesc

          			RETURN '-1'

            END

            --

            SET @REQ_ID = @p_REQ_ID

            INSERT INTO MW_REQ_DT (REQ_DT_ID,REQ_ID, REQ_CODE, MATERIAL_GROUP, MATERIAL_ID, UNIT_ID, QUANTITY_REQ, QUANTITY_ALLOCATION, REASON,DEP_USE,BRANCH_USE,BRANCH_NAME,ALLOCATED)

            VALUES (@REQ_DT_ID,@REQ_ID, @REQ_CODE, @MATERIAL_GROUP, @MATERIAL_ID, @UNIT_ID, @QUANTITY_REQ, @QUANTITY_ALLOCATION, @REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,0)

      	    --

            IF @@Error <> 0 GOTO ABORT;

          FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

          END

          CLOSE XmlData;

      		DEALLOCATE XmlData;

        --thêm lịch sử cập nhật

        --INSERT INTO MW_REQUEST_PROCESS () VALUES ()

    END

    IF(@p_STATUS = 'DMTN')

    BEGIN

      --Update quantity_allcation(số lượng cấp phát)

      DELETE MW_REQ_DT WHERE REQ_ID = @p_REQ_ID

      --

      FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

  		WHILE @@FETCH_STATUS = 0

  		BEGIN

        IF(@QUANTITY_ALLOCATION > @QUANTITY_REQ)

        BEGIN

        	ROLLBACK TRANSACTION

    			CLOSE XmlData;

    			DEALLOCATE XmlData;

    			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': số lượng cấp phát không được lớn hơn số lượng yêu cầu.' ErrorDesc

    			RETURN '-1'

        END

        IF(@QUANTITY_REQ=0)

        BEGIN

        	ROLLBACK TRANSACTION

    			CLOSE XmlData;

    			DEALLOCATE XmlData;

    			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': số lượng yêu cầu không thể đáp ứng.' ErrorDesc

    			RETURN '-1'

        END

        EXEC SYS_CodeMasters_Gen 'MW_REQ_DT', @REQ_DT_ID out

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

        --

        SET @INDEX = @INDEX +1

        SET @REQ_ID = @p_REQ_ID

        INSERT INTO MW_REQ_DT (REQ_DT_ID,REQ_ID, REQ_CODE, MATERIAL_GROUP, MATERIAL_ID, UNIT_ID, QUANTITY_REQ, QUANTITY_ALLOCATION, REASON,DEP_USE,BRANCH_USE,BRANCH_NAME,ALLOCATED)

        VALUES (@REQ_DT_ID,@REQ_ID, @REQ_CODE, @MATERIAL_GROUP, @MATERIAL_ID, @UNIT_ID, @QUANTITY_REQ, @QUANTITY_ALLOCATION, @REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,0)

  	    --

        IF @@Error <> 0 GOTO ABORT;

        FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

      END

      CLOSE XmlData;

  		DEALLOCATE XmlData;

      --Insert MW_REQ_PROCESS

      DELETE MW_REQ_PROCESS WHERE REQ_ID = @p_REQ_ID

        --

        FETCH NEXT FROM XmlDataProcess	INTO @REQ_PROCESS_ID,@TLNAME,@PROCESS_STATUS,@NOTES,@REQ_CODE_PROCESS,@STAGE

       -- DECLARE @STATUS_P VARCHAR(5) = 'C'

    		WHILE @@FETCH_STATUS = 0

    		BEGIN

          EXEC SYS_CodeMasters_Gen 'MW_REQ_PROCESS', @REQ_PROCESS_ID out

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

          --

          SET @REQ_CODE_PROCESS = @p_REQ_ID

          INSERT INTO MW_REQ_PROCESS (REQ_PROCESS_ID, TLNAME, PROCESS_STATUS, NOTES, REQ_ID,STAGE)

          VALUES (@REQ_PROCESS_ID, @TLNAME, @PROCESS_STATUS, @NOTES, @REQ_CODE_PROCESS,@STAGE)

          --INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, NOTES, STAGE) VALUES (@p_REQ_ID, 'LEVEL', @STATUS_P, 'TDV', @TLNAME, '','DMTN', N'Chờ duyệt',@STAGE)

    	    --

          IF @@Error <> 0 GOTO ABORT;

          --SET @STATUS_P = 'U'

          FETCH NEXT FROM XmlDataProcess	INTO @REQ_PROCESS_ID,@TLNAME,@PROCESS_STATUS,@NOTES,@REQ_CODE_PROCESS,@STAGE

        END

      	CLOSE XmlDataProcess;

    		DEALLOCATE XmlDataProcess;

        --thêm lịch sử add process

        --INSERT INTO MW_REQUEST_PROCESS () VALUES ()

    END

    --Người tạo cập nhật lại phiếu bị từ chối ở Bước TDV duyệt và đầu mối kietvt 18/10/2023

    IF(EXISTS(SELECT * FROM MW_REQ WHERE REQ_ID = @p_REQ_ID AND MAKER_ID = @p_MAKER_ID AND STATUS IN ('R_SENDAPP','DMTN_MAKER','R_MAKERID')))

    BEGIN

      UPDATE MW_REQ SET REQ_TYPE = @p_REQ_TYPE, 

                        BRANCH_ID = @p_BRANCH_ID, 

                        REGARDS_TO = @p_REGARDS_TO, 

                        REQUEST_DOC_CONTENT = @p_REQUEST_DOC_CONTENT, 

                        PROMOTION_ID = @p_PROMOTION_ID, 

                        DEP_ID = @p_DEP_ID, 

                        STATUS = 'ADDNEW', AUTH_STATUS = 'E',

                        BRANCH_RECEIVE_ID=@p_BRANCH_RECEIVE_ID,

                        BRANCH_RECEIVE_NAME=@p_BRANCH_RECEIVE_NAME,

                        DEP_RECEIVE_ID = @p_DEP_RECEIVE_ID,

                        DEP_RECEIVE_NAME=@p_DEP_RECEIVE_NAME 

                        WHERE REQ_ID = @p_REQ_ID

          DELETE MW_REQ_DT WHERE REQ_ID = @p_REQ_ID

          --

          FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

      		WHILE @@FETCH_STATUS = 0

      		BEGIN

            EXEC SYS_CodeMasters_Gen 'MW_REQ_DT', @REQ_DT_ID out

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

            --

            SET @REQ_ID = @p_REQ_ID

            INSERT INTO MW_REQ_DT (REQ_DT_ID,REQ_ID, REQ_CODE, MATERIAL_GROUP, MATERIAL_ID, UNIT_ID, QUANTITY_REQ, QUANTITY_ALLOCATION, REASON,DEP_USE,BRANCH_USE,BRANCH_NAME,ALLOCATED)

            VALUES (@REQ_DT_ID,@p_REQ_ID, @REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID, @UNIT_ID, @QUANTITY_REQ, @QUANTITY_ALLOCATION, @REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,0)

      	    --

            IF @@Error <> 0 GOTO ABORT;

            FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

          END

          CLOSE XmlData;

      		DEALLOCATE XmlData;

      -- Tạo lại lịch sử xử lý 

      INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID,NOTES,RECEPTION_DT)

  		       VALUES(@REQ_ID,'ADDNEW',N'Chỉnh sửa','NVTT','C',@p_BRANCH_ID,'',@p_DEP_ID, @p_MAKER_ID, N'Chỉnh sửa lại phiếu',GETDATE())

--  	  INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,PROCESS_NAME,DEP_ID, PROCESS_TYPE)

--  		       VALUES(@REQ_ID,'APPNEW','U','GDDV',@p_BRANCH_ID,'ADDNEW','',@p_DEP_ID, 'Approve')

    END

    --Phiếu trả về ở bước LEVEL

    IF(EXISTS(SELECT * FROM MW_REQ WHERE REQ_ID = @p_REQ_ID AND CHECKER_ID != @p_MAKER_ID AND MAKER_ID != @p_MAKER_ID AND STATUS = 'R_DMTN'))

    BEGIN

      UPDATE MW_REQ SET STATUS = 'DMTN', AUTH_STATUS = 'A' WHERE REQ_ID = @p_REQ_ID

      --Update quantity_allcation(số lượng cấp phát)

      DELETE MW_REQ_DT WHERE REQ_ID = @p_REQ_ID

      --

      FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

      		WHILE @@FETCH_STATUS = 0

      		BEGIN

            IF(@QUANTITY_ALLOCATION > @QUANTITY_REQ)

            BEGIN

            	ROLLBACK TRANSACTION

        			CLOSE XmlData;

        			DEALLOCATE XmlData;

        			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': số lượng cấp phát không được lớn hơn số lượng yêu cầu.' ErrorDesc

        			RETURN '-1'

            END

            EXEC SYS_CodeMasters_Gen 'MW_REQ_DT', @REQ_DT_ID out

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

            --

            SET @INDEX = @INDEX +1

            SET @REQ_ID = @p_REQ_ID

            INSERT INTO MW_REQ_DT (REQ_DT_ID,REQ_ID, REQ_CODE, MATERIAL_GROUP, MATERIAL_ID, UNIT_ID, QUANTITY_REQ, QUANTITY_ALLOCATION, REASON,DEP_USE,BRANCH_USE,BRANCH_NAME,ALLOCATED)

            VALUES (@REQ_DT_ID,@p_REQ_ID, @REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID, @UNIT_ID, @QUANTITY_REQ, @QUANTITY_ALLOCATION, @REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,0)

      	    --

            IF @@Error <> 0 GOTO ABORT;

            FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@REASON,@DEP_USE,@BRANCH_USE,@BRANCH_NAME,@ALLOCATED

      END

      CLOSE XmlData;

  		DEALLOCATE XmlData;

      --Insert MW_REQ_PROCESS

      DELETE MW_REQ_PROCESS WHERE REQ_ID = @p_REQ_ID

        --

        FETCH NEXT FROM XmlDataProcess	INTO @REQ_PROCESS_ID,@TLNAME,@PROCESS_STATUS,@NOTES,@REQ_CODE_PROCESS,@STAGE

       -- DECLARE @STATUS_P VARCHAR(5) = 'C'

    		WHILE @@FETCH_STATUS = 0

    		BEGIN

          EXEC SYS_CodeMasters_Gen 'MW_REQ_PROCESS', @REQ_PROCESS_ID out

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

          --

          SET @REQ_CODE_PROCESS = @p_REQ_ID

          INSERT INTO MW_REQ_PROCESS (REQ_PROCESS_ID, TLNAME, PROCESS_STATUS, NOTES, REQ_ID,STAGE)

          VALUES (@REQ_PROCESS_ID, @TLNAME, @PROCESS_STATUS, @NOTES, @REQ_CODE_PROCESS,@STAGE)

          --INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, NOTES, STAGE) VALUES (@p_REQ_CODE, 'LEVEL', @STATUS_P, 'TDV', @TLNAME, '','DMTN', N'Chờ duyệt',@STAGE)

    	    --

          IF @@Error <> 0 GOTO ABORT;

          --SET @STATUS_P = 'U'

          FETCH NEXT FROM XmlDataProcess	INTO @REQ_PROCESS_ID,@TLNAME,@PROCESS_STATUS,@NOTES,@REQ_CODE_PROCESS,@STAGE

        END

      	CLOSE XmlDataProcess;

    		DEALLOCATE XmlDataProcess;

        --thêm lịch sử add process

        --INSERT INTO MW_REQUEST_PROCESS () VALUES ()

         INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID,NOTES,RECEPTION_DT)

  		       VALUES(@REQ_ID,'DMTN',N'Chỉnh sửa','DVDM','C',@p_BRANCH_ID,'',@p_DEP_ID, @p_MAKER_ID, N'Chỉnh sửa lại phiếu',GETDATE())

    END

 INSERT INTO dbo.MW_PROCESS

							(

								REQ_ID,

								PROCESS_ID,

								CHECKER_ID,

								APPROVE_DT,

								PROCESS_DESC,

								NOTES

							)

							VALUES

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

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

								@p_MAKER_ID,        -- CHECKER_ID - varchar(100)

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

								N'Nhân viên cập nhật phiếu',       -- PROCESS_DESC - nvarchar(1000)

								N'Nhân viên cập nhật phiếu yêu cầu'        -- NOTES - nvarchar(1000)

							)

IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result,'' REQ_ID,'' REQ_ID, N'Phiếu yêu cầu: '+(SELECT REQ_ID FROM MW_REQ WHERE REQ_ID  = @p_REQ_ID)+N' đã được cập nhật thành công.' NOTICATION, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

    CLOSE XmlData;

		DEALLOCATE XmlData;

    CLOSE XmlDataProcess;

		DEALLOCATE XmlDataProcess;

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REJECT_LOG_Ins]'

GO

ALTER PROC [dbo].[MW_REJECT_LOG_Ins]

@p_LOG_ID	varchar(15)	= NULL,

@p_STAGE	varchar(10)	 = NULL,

@p_TRN_ID	varchar(15)	 = NULL,

@p_TRN_TYPE	nvarchar(100)	 = NULL,

@p_LOG_DT	VARCHAR(50)	 = NULL,

@p_AUTH_STATUS	varchar(3)	 = NULL,

@p_REASON	nvarchar(4000)	 = NULL,

@p_IS_LATEST	varchar(1)	 = NULL,

@p_REJECTED_BY	varchar(20)	 = NULL,

@p_REJECTED_DT	VARCHAR(50)	 = NULL,

@p_RETURN_STEP VARCHAR(25) = NULL

AS

--Validation is here

DECLARE @ERRORSYS NVARCHAR(1500) = '' 

  IF ( EXISTS ( SELECT * FROM MW_REJECT_LOG WHERE LOG_ID = @p_LOG_ID))

	SET @ERRORSYS = N'Mã '+@p_LOG_ID+ N' đã tồn tại trong hệ thống'

IF @ERRORSYS <> '' 

BEGIN

	SELECT '-1' Result, @ERRORSYS ErrorDesc

	RETURN '-1'

END 

IF (@p_REJECTED_BY = (SELECT mr.MAKER_ID FROM MW_REQ mr WHERE mr.REQ_ID = @p_TRN_ID))

BEGIN

	SELECT '-1' Result, N'Không được phép trả phiếu' ErrorDesc

	RETURN '-1'

END 

BEGIN TRANSACTION

DECLARE @l_LOG_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'MW_REJECT_LOG', @l_LOG_ID out

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

DECLARE @p_BRANCH_ID varchar(50),@p_DEP_ID VARCHAR(50)

SELECT @p_DEP_ID = DEP_ID, @p_BRANCH_ID = BRANCH_ID FROM MW_REQ WHERE REQ_ID = @p_TRN_ID

    IF((SELECT STATUS FROM MW_REQ WHERE REQ_ID = @p_TRN_ID ) LIKE 'SendApp' )

    BEGIN

    	 INSERT INTO MW_REJECT_LOG (LOG_ID, STAGE, TRN_ID, TRN_TYPE, LOG_DT, AUTH_STATUS, REASON, IS_LATEST, REJECTED_BY, REJECTED_DT)

            VALUES (@l_LOG_ID, @p_STAGE, @p_TRN_ID, @p_TRN_TYPE, CONVERT(DATETIME,@p_LOG_DT,103), @p_AUTH_STATUS, @p_REASON, @p_IS_LATEST, @p_REJECTED_BY, CONVERT(DATETIME,@p_REJECTED_DT,103));

       UPDATE MW_REQ SET STATUS = 'R_SENDAPP', AUTH_STATUS = 'R' WHERE REQ_ID = @p_TRN_ID

      -- UPDATE MW_REQUEST_PROCESS SET STATUS = 'F', NOTES = N'Phiếu từ chối phiếu ở bước TDV duyệt' WHERE REQ_ID = @p_TRN_ID

          UPDATE MW_REQUEST_PROCESS

          SET STATUS = 'F'

          WHERE REQ_ID = @p_TRN_ID AND PROCESS_ID = 'SENDAPP'

       INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID,NOTES)

       VALUES(@p_TRN_ID,'R_SENDAPP',N'Từ chối phiếu','F','',@p_BRANCH_ID,'LEVEL',@p_DEP_ID, 'REJECT',GETDATE(),@p_REJECTED_BY, N'Phiếu bị từ chối ở trường đơn vị và trả về cho người tạo chỉnh sửa')

   END

    IF ((SELECT STATUS FROM MW_REQ WHERE REQ_ID = @p_TRN_ID ) LIKE 'LEVEL')

    BEGIN  

            INSERT INTO MW_REJECT_LOG (LOG_ID, STAGE, TRN_ID, TRN_TYPE, LOG_DT, AUTH_STATUS, REASON, IS_LATEST, REJECTED_BY, REJECTED_DT,RETURN_STEP)

            VALUES (@l_LOG_ID, @p_STAGE, @p_TRN_ID, @p_TRN_TYPE, CONVERT(DATETIME,@p_LOG_DT,103), @p_AUTH_STATUS, @p_REASON, @p_IS_LATEST, @p_REJECTED_BY, CONVERT(DATETIME,@p_REJECTED_DT,103),@p_RETURN_STEP);

      IF(@p_STAGE = 'R_MAKERID')

      BEGIN

        UPDATE MW_REQ SET STATUS = 'R_MAKERID', AUTH_STATUS = 'R' WHERE REQ_ID = @p_TRN_ID

          UPDATE MW_REQUEST_PROCESS

          SET STATUS = 'F'

          WHERE REQ_ID = @p_TRN_ID AND PROCESS_ID = 'LEVEL'

       -- UPDATE MW_REQUEST_PROCESS SET STATUS = 'F', NOTES = N'Phiếu từ chối phiếu ở bước LEVEL duyệt trả về người tạo' WHERE REQ_ID = @p_TRN_ID

         INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID,NOTES)

             VALUES(@p_TRN_ID,'R_MAKERID',N'Từ chối phiếu','F','',@p_BRANCH_ID,'LEVEL',@p_DEP_ID, 'REJECT',GETDATE(),@p_REJECTED_BY, N'Phiếu bị từ chối ở cấp xử lý và trả về cho người tạo chỉnh sửa')

        DELETE MW_REQ_PROCESS WHERE REQ_ID = @p_TRN_ID

      END

      IF(@p_STAGE = 'R_DMTN')

      BEGIN

         UPDATE MW_REQUEST_PROCESS

          SET STATUS = 'F'

          WHERE REQ_ID = @p_TRN_ID AND PROCESS_ID = 'LEVEL'

        UPDATE MW_REQ SET STATUS = 'R_DMTN', AUTH_STATUS = 'R' WHERE REQ_ID = @p_TRN_ID

        INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID,NOTES)

             VALUES(@p_TRN_ID,'R_DMTN',N'Từ chối phiếu','F','',@p_BRANCH_ID,'LEVEL',@p_DEP_ID, 'REJECT',GETDATE(),@p_REJECTED_BY, N'Phiếu bị từ chối ở cấp xử lý và trả về cho đầu mối chỉnh sửa')

        DELETE MW_REQ_PROCESS WHERE REQ_ID = @p_TRN_ID

      END

    END

    IF ((SELECT STATUS FROM MW_REQ WHERE REQ_ID = @p_TRN_ID ) LIKE 'DMTN')

    BEGIN  

            INSERT INTO MW_REJECT_LOG (LOG_ID, STAGE, TRN_ID, TRN_TYPE, LOG_DT, AUTH_STATUS, REASON, IS_LATEST, REJECTED_BY, REJECTED_DT,RETURN_STEP)

            VALUES (@l_LOG_ID, @p_STAGE, @p_TRN_ID, @p_TRN_TYPE, CONVERT(DATETIME,@p_LOG_DT,103), @p_AUTH_STATUS, @p_REASON, @p_IS_LATEST, @p_REJECTED_BY, CONVERT(DATETIME,@p_REJECTED_DT,103),@p_RETURN_STEP);

      IF(@p_STAGE = 'DMTN_MAKER')

      BEGIN

        UPDATE MW_REQ SET STATUS = 'DMTN_MAKER', AUTH_STATUS = 'R' WHERE REQ_ID = @p_TRN_ID

      --  UPDATE MW_REQUEST_PROCESS SET STATUS = 'F', NOTES = N'Phiếu từ chối phiếu ở bước đầu mối duyệt trả về người tạo' WHERE REQ_ID = @p_TRN_ID AND PROCESS_ID = ''

          UPDATE MW_REQUEST_PROCESS

          SET STATUS = 'F'

          WHERE REQ_ID = @p_TRN_ID AND PROCESS_ID = 'DMTN'

              INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,CHECKER_ID,NOTES)

             VALUES(@p_TRN_ID,'DMTN_MAKER',N'Từ chối phiếu','F','DVDM',@p_BRANCH_ID,'SENDAPP',@p_DEP_ID, 'REJECT',GETDATE(),@p_REJECTED_BY, N'Phiếu bị từ chối ở đầu mối và trả về cho người tạo chỉnh sửa')

      END

    END

INSERT INTO dbo.MW_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)

VALUES(@p_TRN_ID,'REJECT',@p_REJECTED_BY,GETDATE(),N'Trả về ',@p_REASON)

    IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @l_LOG_ID  ID, N'Từ chối phiếu thành công' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, N'Phiếu' ID, N'từ chối thất bại' ErrorDesc

		RETURN '-1'

End

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REQ_App]'

GO

ALTER PROCEDURE [dbo].[MW_REQ_App]

  @p_REQ_ID VARCHAR(20),

  @p_REQ_CODE VARCHAR(15),

  @p_AUTH_STATUS VARCHAR(1),

  @p_STATUS VARCHAR(15),

  @p_CHECKER_ID VARCHAR(200),

  @p_APPROVE_DT VARCHAR(20),

  @p_USER_LOGIN VARCHAR(25)

AS

--Validation is

  DECLARE @ROLE_USER VARCHAR(25), @DEP_RECEIVE VARCHAR(25)

	DECLARE	@aStatus VARCHAR(1), @p_BRANCH_ID VARCHAR(50), @p_DEP_ID VARCHAR(50), @p_BRANCH_GIVES_ID VARCHAR(50)

	SELECT @aStatus = [AUTH_STATUS], @p_STATUS = STATUS, @p_BRANCH_ID = BRANCH_ID, @p_DEP_ID = DEP_ID, @p_BRANCH_GIVES_ID = BRANCH_GIVES_ID FROM MW_REQ WHERE REQ_ID = @p_REQ_ID

  DECLARE @CHECK_DMTN NVARCHAR(50)

  SELECT @CHECK_DMTN = B.ROLE_RECEIVE FROM MW_REQ A LEFT JOIN CM_REQ_TYPE B ON B.REQ_TYPE_ID = A.REQ_TYPE WHERE A.REQ_ID = @p_REQ_ID AND ISNULL(@p_DEP_ID,'') = ISNULL(B.DEP_RECEIVE,'')

	IF @aStatus = 'A' 

	BEGIN

		SELECT '0' as Result, '' ErrorDesc

		RETURN 0

	END

BEGIN TRANSACTION

  IF(@p_STATUS LIKE 'SendApp')

  BEGIN

    --IF(@CHECK_DMTN = NULL OR @CHECK_DMTN IS NULL OR @CHECK_DMTN = '' )

    IF(ISNULL(@CHECK_DMTN,'') <> '')-- AND ISNULL(@p_BRANCH_GIVES_ID,'') = 'DEP000000000048')

    BEGIN

    	  SET @p_AUTH_STATUS = 'A'

        SET @p_STATUS = 'DONE'

        UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, STATUS = @p_STATUS, APPROVE_DT = GETDATE(), CHECKER_ID = @p_USER_LOGIN WHERE REQ_ID = @p_REQ_ID	IF @@Error <> 0 GOTO ABORT

        INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, PARENT_PROCESS_ID, NOTES) VALUES (@p_REQ_CODE, 'DONE', 'C', 'APPNEW', N'Hoàn thành phiếu')

    END

    ELSE

    BEGIN

      SET @p_AUTH_STATUS = 'U'

      SET @p_STATUS = 'DMTN'

      UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103), STATUS = @p_STATUS

  	  WHERE REQ_ID = @p_REQ_ID	IF @@Error <> 0 GOTO ABORT

      --UPDATE MW_REQUEST_PROCESS SET STATUS = 'P', NOTES = N'Đã duyệt',CHECKER_ID = @p_USER_LOGIN, RECEPTION_DT = GETDATE() WHERE REQ_ID = @p_REQ_CODE AND PROCESS_ID = 'APPNEW'

      UPDATE MW_REQUEST_PROCESS

      SET STATUS = 'P'

      WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'SENDAPP'

      SELECT @ROLE_USER=B.ROLE_RECEIVE, @DEP_RECEIVE = B.DEP_RECEIVE

      FROM MW_REQ A

      LEFT JOIN CM_REQ_TYPE B ON B.REQ_TYPE_ID = A.REQ_TYPE

      WHERE A.REQ_ID = @p_REQ_ID

    	INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT,NOTES, CHECKER_ID)

      VALUES(@p_REQ_ID,'APPNEW',N'Trưởng đơn vị phê duyệt','P','GDDV',@p_BRANCH_ID,'ADDNEW',@DEP_RECEIVE, 'Approve',GETDATE(), N'Chấp thuận',@p_CHECKER_ID)

      INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE, CHECKER_ID, PROCESS_NAME, NOTES, RECEPTION_DT) 

                              VALUES (@p_REQ_ID, 'DMTN', 'C',@ROLE_USER,'DV0001','APPNEW',@DEP_RECEIVE,'SendApp',( SELECT STUFF((select ', ' + RE.TLNANME  

                                                                                                                  		FROM(

                                                                                                                  		SELECT * FROM dbo.FN_GET_USER_BY_ROLE_VB('DVDM','DV0001',@DEP_RECEIVE) ) RE

                                                                                                                  			FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')), N'Đơn vị đầu mối', N'Đơn vị đầu mối tiếp nhận',DATEADD(SECOND, 1, GETDATE()))

    END

  END

	IF(@p_STATUS LIKE 'LEVEL')

  BEGIN

    IF(EXISTS(SELECT * FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND DVKD_USER_APP = @p_CHECKER_ID AND PROCESS_ID = 'LEVEL' AND STATUS = 'U'))

    BEGIN

    	ROLLBACK TRANSACTION

			SELECT '-1' AS Result , ''  REQ_CODE, N'Phiếu yêu cầu chưa tới cấp duyệt tiếp theo.' ErrorDesc

			RETURN '-1'

    END

    IF(EXISTS (SELECT * FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND DVKD_USER_APP = @p_CHECKER_ID AND PROCESS_ID = 'LEVEL' AND STATUS = 'C'))

    BEGIN

      DECLARE @STAGE_CURRENT INT = (SELECT mrp.IS_LEAF FROM MW_REQUEST_PROCESS mrp WHERE  REQ_ID = @p_REQ_ID AND DVKD_USER_APP = @p_CHECKER_ID AND PROCESS_ID = 'LEVEL' AND STATUS = 'C')

      UPDATE MW_REQUEST_PROCESS SET STATUS = 'P', RECEPTION_DT = GETDATE(), APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103), NOTES = N'Chấp thuận' WHERE REQ_ID = @p_REQ_ID AND DVKD_USER_APP = @p_CHECKER_ID AND PROCESS_ID = 'LEVEL'

      UPDATE MW_REQ_PROCESS SET PROCESS_STATUS = 'Approve', AUTH_STATUS = 'A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103), NOTES = N'Chấp thuận' WHERE REQ_ID = @p_REQ_ID AND TLNAME = @p_CHECKER_ID

      IF(EXISTS(SELECT 1 FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'LEVEL' AND IS_LEAF = (@STAGE_CURRENT+1)))

      BEGIN

      	UPDATE MW_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_REQ_ID AND IS_LEAF = (@STAGE_CURRENT+1) AND PROCESS_ID = 'LEVEL'

      END

      ELSE

      BEGIN

        SET @p_AUTH_STATUS = 'A'

        SET @p_STATUS = 'DONE'

        --UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103), STATUS = @p_STATUS WHERE REQ_CODE = @p_REQ_CODE	IF @@Error <> 0 GOTO ABORT

        UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, STATUS = @p_STATUS WHERE REQ_ID = @p_REQ_ID	IF @@Error <> 0 GOTO ABORT

        INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, PARENT_PROCESS_ID, NOTES) VALUES (@p_REQ_ID, 'DONE', 'C', 'APPLEVEL', N'Hoàn thành phiếu')

      END

      --UPDATE MW_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_REQ_CODE AND STAGE = (SELECT MIN(STAGE) FROM MW_REQUEST_PROCESS WHERE STATUS = 'U' AND PROCESS_ID = 'LEVEL' AND REQ_ID = @p_REQ_CODE) 

    END

    IF(EXISTS (SELECT * FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND CHECKER_ID = @p_CHECKER_ID AND STATUS = 'U'))

    BEGIN

      ROLLBACK TRANSACTION

			SELECT '-1' AS Result , ''  REQ_CODE, N'Phiếu yêu cầu chưa tới cấp duyệt tiếp theo.' ErrorDesc

			RETURN '-1'

    END

--    IF((SELECT COUNT(*) FROM MW_REQ_PROCESS WHERE REQ_PROCESS_ID = @p_REQ_CODE) = (SELECT COUNT(*) FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_CODE AND STATUS = 'P'))

--    BEGIN

--       SET @p_AUTH_STATUS = 'A'

--       SET @p_STATUS = 'DONE'

--       UPDATE MW_REQ SET AUTH_STATUS = @p_AUTH_STATUS, CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103), STATUS = @p_STATUS WHERE REQ_CODE = @p_REQ_CODE	IF @@Error <> 0 GOTO ABORT

--       INSERT INTO MW_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, PARENT_PROCESS_ID, NOTES) VALUES (@p_REQ_CODE, 'DONE', 'P', 'APPLEVEL', N'Hoàn thành phiếu')

--       

--    END

  END

INSERT INTO dbo.MW_PROCESS

							(

								REQ_ID,

								PROCESS_ID,

								CHECKER_ID,

								APPROVE_DT,

								PROCESS_DESC,

								NOTES

							)

							VALUES

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

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

								@p_CHECKER_ID,        -- CHECKER_ID - varchar(100)

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

								N'Cấp xử lý phê duyệt',       -- PROCESS_DESC - nvarchar(1000)

								N'Cấp xử lý phê duyệt thành công'        -- NOTES - nvarchar(1000)

							)

COMMIT TRANSACTION

SELECT '0' as Result, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

End

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REQ_Ins]'

GO

ALTER PROCEDURE [dbo].[MW_REQ_Ins]

  @p_REQ_ID varchar(20),

  @p_REQ_CODE varchar(15),

  @p_REQ_TYPE nvarchar(200)= NULL,

  @p_DEP_ID VARCHAR(50) = NULL,

  @p_BRANCH_ID nvarchar(500)= NULL,

  @p_BRANCH_NAME NVARCHAR(1000)= NULL,

  @p_BRANCH_RECEIVE_ID VARCHAR(50) = NULL,

  @p_BRANCH_RECEIVE_NAME NVARCHAR(1000)= NULL,

  @p_DEP_RECEIVE_ID VARCHAR(50) = NULL,

  @p_DEP_RECEIVE_NAME NVARCHAR(1000)= NULL,

  @p_PROMOTION_ID VARCHAR(50)=NULL,

  @p_REGARDS_TO nvarchar(500)= NULL,

  @p_REQUEST_DOC_CONTENT NVARCHAR(500)= NULL,

  @p_STATUS VARCHAR(15)= NULL,

  @p_RECORD_STATUS varchar(15)= NULL,

  @p_MAKER_ID varchar(100)= NULL,

  @p_CREATE_DT varchar(25)= NULL,

  @p_AUTH_STATUS varchar(50)= NULL,

  @p_CHECKER_ID varchar(100)= NULL,

  @p_APPROVE_DT varchar(25)= NULL,

  @p_BRANCH_GIVES_ID VARCHAR(50) = NULL,

  @p_BRANCH_GIVES_NAME NVARCHAR(1000)= NULL,

  @p_DEP_GIVES_ID VARCHAR(50) = NULL,

  @p_DEP_GIVES_NAME NVARCHAR(1000)= NULL,

  @p_XmlData XML = NULL

AS

--Validation is here

DECLARE @ERRORSYS NVARCHAR(1500) = '' 

--KHAI BAO BIEN CURSOR DETAIL

DECLARE @REQ_DT_ID varchar(15)= NULL,

        @REQ_CODE varchar(15) =NULL,

        @MATERIAL_GROUP VARCHAR (200)= NULL,

        @MATERIAL_ID nvarchar(200) =NULL,

        @UNIT_ID VARCHAR(15) =NULL, 

        @QUANTITY_REQ DECIMAL(18,2)= NULL,

        @QUANTITY_ALLOCATION DECIMAL(18,2) = NULL,

        @DEP_ID VARCHAR(50) = NULL,

        @BRANCH_ID nvarchar(500)= NULL,

        @BRANCH_NAME NVARCHAR(1000)= NULL,

        @PROMOTION_ID VARCHAR(15) =NULL,

        @REASON NVARCHAR(1000) =NULL,

        @RECORD_STATUS varchar(15) =NULL,

        @MAKER_ID varchar(100) =NULL,

        @CREATE_DT varchar(25) =NULL,

        @AUTH_STATUS varchar(50) =NULL,

        @CHECKER_ID varchar(100) =NULL,

        @APPROVE_DT varchar(25) =NULL,

        @DEP_USE VARCHAR(15) =NULL,

        @BRANCH_USE VARCHAR(15) = NULL,

        @ALLOCATED DECIMAL(18,2)= NULL        

DECLARE @INDEX INT =1

DECLARE @hdoc INT;

--DECLARE @CM_PROMOTION_ID TABLE

--        (

--          PROMOTION_ID VARCHAR(20)

--        )

	---

		EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XmlData;

		DECLARE XmlData CURSOR FOR

		SELECT *

		FROM

			OPENXML (@hdoc, '/Root/MWREQDT', 2)

			WITH (	REQ_DT_ID varchar(15),

              REQ_ID VARCHAR(20),

              REQ_CODE varchar(15),

              MATERIAL_GROUP VARCHAR (200),

              MATERIAL_ID nvarchar(200),

              UNIT_ID VARCHAR(15), 

              QUANTITY_REQ DECIMAL(18,2),

              QUANTITY_ALLOCATION DECIMAL(18,2),

              PROMOTION_ID VARCHAR(15),

              REASON NVARCHAR(1000),

              DEP_USE VARCHAR(15),

              BRANCH_USE VARCHAR(15),

              BRANCH_NAME NVARCHAR(1000),

              ALLOCATED DECIMAL(18,2)

              );

		OPEN XmlData;

BEGIN TRANSACTION

  IF(CONVERT(DATETIME,(SELECT END_DATE FROM CM_PROMOTION WHERE PROMOTION_ID = @p_PROMOTION_ID),103) < CONVERT(DATETIME,GETDATE(),103))

    BEGIN

      ROLLBACK TRANSACTION

  		SELECT '-1' as Result, N''ID, N'Chương trình này đã quá hạn vui lòng chọn chương trình khác' ErrorDesc

  		RETURN '-1'

    END

DECLARE @l_REQ_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'MW_REQ', @l_REQ_ID out

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

--

    IF ( EXISTS ( SELECT * FROM MW_REQ WHERE REQ_CODE = @l_REQ_ID))

    	SET @ERRORSYS = N'Mã '+@l_REQ_ID+ N' đã tồn tại trong hệ thống'

    IF @ERRORSYS <> '' 

    BEGIN

      CLOSE XmlData;

  		DEALLOCATE XmlData;

  		ROLLBACK TRANSACTION

    	SELECT '-1' Result, @ERRORSYS ErrorDesc

    	RETURN '-1'

    END 

    IF (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '') SET @p_BRANCH_ID = (SELECT TU.TLSUBBRID FROM TL_USER TU WHERE TU.TLNANME = @p_MAKER_ID)

--

		SET @p_STATUS = 'ADDNEW'

    SET @p_AUTH_STATUS = 'E'

    SET @ALLOCATED = '0'

    --

        INSERT INTO MW_REQ (

        REQ_ID,

        REQ_CODE, 

        REQ_TYPE,

        DEP_ID,

        BRANCH_ID, 

        BRANCH_NAME,

        PROMOTION_ID,

        REGARDS_TO,

        IS_DONE,

        REQUEST_DOC_CONTENT, 

        STATUS,

        RECORD_STATUS,

        MAKER_ID, 

        CREATE_DT, 

        AUTH_STATUS, 

        CHECKER_ID, 

        APPROVE_DT,

        BRANCH_RECEIVE_ID,

        BRANCH_RECEIVE_NAME,

        DEP_RECEIVE_ID,

        DEP_RECEIVE_NAME,

        BRANCH_GIVES_ID,

        BRANCH_GIVES_NAME,

        DEP_GIVES_ID,

        DEP_GIVES_NAME)

        VALUES 

        (

        @l_REQ_ID,

        @p_REQ_CODE, 

        @p_REQ_TYPE,

        @p_DEP_ID, 

        @p_BRANCH_ID,

        @p_BRANCH_NAME, 

        @p_PROMOTION_ID,

        @p_REGARDS_TO, 

        '0',

        @p_REQUEST_DOC_CONTENT, 

        @p_STATUS,

        '1', 

        @p_MAKER_ID, 

        CONVERT(DATETIME,@p_CREATE_DT,103), 

        @p_AUTH_STATUS, 

        @p_CHECKER_ID, 

        CONVERT(DATETIME,@p_APPROVE_DT,103),

        @p_BRANCH_RECEIVE_ID,

        @p_BRANCH_RECEIVE_NAME,

        @p_DEP_RECEIVE_ID,

        @p_DEP_RECEIVE_NAME,

        @p_BRANCH_GIVES_ID,

        @p_BRANCH_GIVES_NAME,

        @p_DEP_GIVES_ID,

        @p_DEP_GIVES_NAME

        )

--        INSERT INTO @CM_PROMOTION_ID(MATERIAL_ID) 

--        SELECT cpd.MATERIAL_ID FROM CM_PROMOTION_DT cpd WHERE cpd.PROMOTION_ID = @p_PROMOTION_ID

--

    FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@PROMOTION_ID,@REASON,@DEP_USE, @BRANCH_USE,@BRANCH_NAME,@ALLOCATED

		WHILE @@FETCH_STATUS = 0

		BEGIN

      EXEC SYS_CodeMasters_Gen 'MW_REQ_DT', @REQ_DT_ID out

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

      IF(@QUANTITY_REQ=0)

        BEGIN

        	ROLLBACK TRANSACTION

    			CLOSE XmlData;

    			DEALLOCATE XmlData;

    			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': số lượng yêu cầu không thể đáp ứng.' ErrorDesc

    			RETURN '-1'

        END

      IF(ISNULL(@p_PROMOTION_ID,'') <> '' AND @MATERIAL_ID NOT IN (SELECT cpd.MATERIAL_ID FROM CM_PROMOTION_DT cpd WHERE cpd.PROMOTION_ID = @p_PROMOTION_ID))

      BEGIN

      	  ROLLBACK TRANSACTION

    			CLOSE XmlData;

    			DEALLOCATE XmlData;

    			SELECT '-1' as Result, '' REQ_ID, N'Danh sách yêu cầu, Dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': loại vật liệu bạn chọn không có trong danh sách chương trình.' ErrorDesc

    			RETURN '-1'

      END

      --

      SET @INDEX = @INDEX +1

      SET @p_REQ_ID = @l_REQ_ID

      INSERT INTO MW_REQ_DT (REQ_DT_ID, REQ_ID, REQ_CODE, MATERIAL_GROUP, MATERIAL_ID, UNIT_ID, QUANTITY_REQ, QUANTITY_ALLOCATION, PROMOTION_ID, REASON,DEP_USE, BRANCH_USE,BRANCH_NAME,ALLOCATED)

      VALUES (@REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@PROMOTION_ID,@REASON,@DEP_USE, @BRANCH_USE,@BRANCH_NAME, 0)

	    --

      IF @@Error <> 0 GOTO ABORT;

      FETCH NEXT FROM XmlData	INTO @REQ_DT_ID,@p_REQ_ID,@REQ_CODE,@MATERIAL_GROUP,@MATERIAL_ID,@UNIT_ID,@QUANTITY_REQ,@QUANTITY_ALLOCATION,@PROMOTION_ID,@REASON,@DEP_USE, @BRANCH_USE,@BRANCH_NAME,@ALLOCATED

    END

  	CLOSE XmlData;

		DEALLOCATE XmlData;

    BEGIN

--      INSERT INTO dbo.MW_PROCESS

--							(

--								REQ_ID,

--								PROCESS_ID,

--								CHECKER_ID,

--								APPROVE_DT,

--								PROCESS_DESC,

--								NOTES

--							)

--							VALUES

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

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

--								@p_MAKER_ID,        -- CHECKER_ID - varchar(100)

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

--								N'Nhân viên tạo phiếu',       -- PROCESS_DESC - nvarchar(1000)

--								N'Nhân viên tạo phiếu yêu cầu'        -- NOTES - nvarchar(1000)

--							)

      -- MỚI TẠO

      INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)

  		       VALUES(@l_REQ_ID,'ADDNEW',N'Nhân viên tạo phiếu','NVTT','C',@p_BRANCH_ID,'',@p_DEP_ID, @p_MAKER_ID,GETDATE(),N'Nhân viên tạo phiếu yêu cầu')

--  	  INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID, PROCESS_TYPE,RECEPTION_DT)

--  		       VALUES(@l_REQ_CODE,'APPNEW',N'CHỜ TDV DUYỆT','U','GDDV',@p_BRANCH_ID,'ADDNEW',@p_DEP_ID, 'Approve',GETDATE())

    END

    IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @l_REQ_ID  ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

    CLOSE XmlData;

		DEALLOCATE XmlData;

		ROLLBACK TRANSACTION

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

		RETURN '-1'

End

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REQ_DT_ById]'

GO

ALTER PROCEDURE [dbo].[MW_REQ_DT_ById]

@REQ_ID	varchar(15)

AS

SELECT A.*, B.PROMOTION_NAME, A.REQ_DT_ID AS REQ_DT_CODE, MM.MATERIAL_CODE, MG.GROUP_CODE AS MATERIAL_GROUP_CODE, MM.MATERIAL_NAME AS MATERIAL_NAME, MG.GROUP_NAME AS GROUP_NAME

FROM MW_REQ_DT A

LEFT JOIN MW_MATERIAL MM ON A.MATERIAL_ID = MM.MATERIAL_ID

LEFT JOIN MW_GROUP MG ON A.MATERIAL_GROUP = MG.GROUP_ID

LEFT JOIN CM_PROMOTION B ON B.PROMOTION_ID = A.PROMOTION_ID

WHERE  A.REQ_ID = @REQ_ID

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MW_REQ_ById]'

GO

ALTER PROCEDURE [dbo].[MW_REQ_ById]

@REQ_ID	varchar(15)

AS

--DECLARE @DONE_LEVEL VARCHAR(1) = CASE WHEN (NOT EXISTS(SELECT * FROM MW_REQ_PROCESS mrp WHERE mrp.REQ_ID = @REQ_ID AND mrp.AUTH_STATUS IS NULL)) THEN '1' ELSE '0' END

DECLARE @DVDM_USER VARCHAR(50) = (

SELECT TOP 1 A.CHECKER_ID 

FROM MW_REQUEST_PROCESS A 

WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DMTN' 

ORDER BY A.ID DESC)

SELECT A.*, B.DEP_NAME AS BRANCH_NAME,B.DEP_ID AS BRANCH_ID, C.REQ_TYPE_NAME, cp.PROMOTION_NAME, mrp.DVKD_USER_APP AS NEXT_APP_USER, @DVDM_USER AS DVDM_USER

FROM MW_REQ A

LEFT JOIN CM_DEPARTMENT B ON B.DEP_ID = A.BRANCH_ID

LEFT JOIN CM_REQ_TYPE C ON C.REQ_TYPE_ID = A.REQ_TYPE

LEFT JOIN MW_REQ_DT D ON D.REQ_ID = A.REQ_ID

LEFT JOIN CM_PROMOTION cp ON cp.PROMOTION_ID = A.PROMOTION_ID

LEFT JOIN MW_REQUEST_PROCESS mrp ON mrp.REQ_ID = @REQ_ID AND  mrp.STATUS = 'C' AND mrp.PROCESS_ID = 'LEVEL'

WHERE  A.REQ_ID = @REQ_ID

GO

ALTER PROCEDURE dbo.MW_REQ_ApproveNow

  @p_REQ_ID VARCHAR(15),

  @p_CHECKER_ID VARCHAR(200),

  @p_APPROVE_DT VARCHAR(20)

AS 

  DECLARE @Auth VARCHAR(1),@CheckerId VARCHAR(15),@status VARCHAR(20), @BranchId VARCHAR(15), @DepId VARCHAR(15)

  SELECT @Auth = mr.AUTH_STATUS,

         @CheckerId = mr.CHECKER_ID,

         @status = mr.STATUS

  FROM MW_REQ mr 

  WHERE mr.REQ_ID = @p_REQ_ID

  IF(@Auth = 'A')

  BEGIN

      SELECT '0' as Result, '' ErrorDesc

		  RETURN 0

  END

   IF(EXISTS(SELECT * FROM MW_REQ mr

            LEFT JOIN CM_REQ_TYPE crt ON crt.REQ_TYPE_ID = mr.REQ_TYPE

            LEFT JOIN CM_WARE cw ON crt.WARE_TYPE  = cw.WARE_ID

            WHERE cw.WARE_CODE IN ('02TE','03CT','04VT') AND mr.REQ_ID = @p_REQ_ID))

  BEGIN

	  SELECT '-1' as Result, N'Phiếu yêu cầu đối với kho vật liệu thẻ, phôi thẻ trắng, phôi thẻ cá thể hóa, bắt buộc gửi cấp xử lý tiếp theo, đầu mối không được phép duyệt' ErrorDesc

		RETURN '-1'

  END

BEGIN TRANSACTION

  IF(@status = 'DMTN' AND @CheckerId <> @p_CHECKER_ID)

    BEGIN  

        IF(EXISTS(SELECT * FROM MW_REQ_PROCESS mrp WHERE mrp.REQ_ID = @p_REQ_ID ))

        BEGIN

          GOTO ABORT3

        END

    	  SET @Auth = 'A'

        SET @status = 'DONE'

        SELECT @BranchId =  tugr.BRANCH_ID, @DepId =  tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) tugr

        UPDATE MW_REQ 

        SET AUTH_STATUS = @Auth, STATUS = @status, APPROVE_DT = GETDATE(), CHECKER_ID = @CheckerId

        WHERE REQ_ID = @p_REQ_ID	

        IF @@Error <> 0 GOTO ABORT

        UPDATE MW_REQUEST_PROCESS

        SET STATUS = 'P'

        WHERE REQ_ID = @p_REQ_ID

        INSERT INTO MW_REQUEST_PROCESS (REQ_ID,CHECKER_ID, PROCESS_ID,PROCESS_NAME,BRANCH_ID,DEP_ID,RECEPTION_DT, STATUS, PARENT_PROCESS_ID, NOTES) 

        VALUES (@p_REQ_ID,@p_CHECKER_ID, @status,N'Phê duyệt phiếu',@BranchId,@DepId,GETDATE() , 'P', 'DMTN', N'Hoàn thành phê duyệt phiếu ở bước đơn vị đầu mối phê duyệt')

    END

  ELSE

    BEGIN

        GOTO ABORT2

    END

INSERT INTO dbo.MW_PROCESS

							(

								REQ_ID,

								PROCESS_ID,

								CHECKER_ID,

								APPROVE_DT,

								PROCESS_DESC,

								NOTES

							)

							VALUES

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

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

								@p_CHECKER_ID,        -- CHECKER_ID - varchar(100)

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

								N'DVDM phê duyệt hoàn tất phiếu',       -- PROCESS_DESC - nvarchar(1000)

								N'DVDM phê duyệt hoàn tất phiếu thành công'        -- NOTES - nvarchar(1000)

							)

COMMIT TRANSACTION

SELECT '0' as Result, N'Phiếu '+ @p_REQ_ID + N' Được phê duyệt thành công' NOTICATION, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

END

ABORT2:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, N'Phiếu' + @p_REQ_ID + N'Không thể phê duyệt khi chưa đén bước xử lý này hoặc không được phép phê duyệt' ErrorDesc

		RETURN '-1'

END

ABORT3:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, N'Phiếu đã có cấp xử lý tiếp theo không thể phê duyệt tại bước này' ErrorDesc

		RETURN '-1'

End