ALTER PROCEDURE dbo.PL_REQ_PROCESS_CHILD_Ins
@p_MAKER_ID VARCHAR(20),
@p_XMLData XML
AS
BEGIN TRANSACTION
		--- KHAI BAO CHUOI ID TRA VÊ PHUC VU VIEC GUI MAI;
		DECLARE @ID_RETURN_FOR_EMAIL VARCHAR(500) =''
		-------------------------------------------------
		Declare @hdoc INT
		Exec sp_xml_preparedocument @hdoc Output,@p_XMLData
		DECLARE ListREQ  CURSOR FOR
		SELECT *
		FROM OPENXML(@hDoc,'/Root/ListREQ',2)
		WITH 
		(
			REQ_ID	varchar(20)  ,
			PROCESS_ID	varchar(20),
			TLNAME VARCHAR(20),
			TYPE_JOB VARCHAR(20),
			REF_ID INT
		
		)
		OPEN ListREQ
DECLARE @LEVEL INT,@REQ_ID VARCHAR(20),@PROCESS_ID VARCHAR(20),@TYPE_JOB VARCHAR(20),@TLNAME VARCHAR(20),@DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)

DECLARE @REF_ID INT, @TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100), @USER_PROCESS_LEVEL INT

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)
SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)

DECLARE @lstCOST TABLE(
 COST_ID VARCHAR(20)
)

INSERT INTO @lstCOST
SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID


FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID
WHILE @@FETCH_STATUS=0
BEGIN
	IF(@TLNAME IS NULL OR @TLNAME='')
	BEGIN
			SELECT -1 Result, N'Vui lòng chọn người được giao xử lý phiếu số' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
	END
	IF(@TYPE_JOB IS NULL OR @TYPE_JOB='')
	BEGIN
			SELECT -1 Result, N'Vui lòng chọn vai trò của người được giao xử lý phiếu' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
	END
	IF(@TYPE_JOB ='TP')
	BEGIN
			--SELECT -1 Result, N'Vui lòng kéo thanh cuộn sang phải để chọn vai trò của người được giao xử lý phiếu. Chọn vai trò kiểm soát nếu nhân viên đóng vai trò phê duyệt kí nháy. Chọn vai trò xử lý phiếu nếu nhân viên trực tiếp xử lý tờ trình' ErrorDesc 
			--ROLLBACK TRANSACTION
			--RETURN '-1'
			SET @TYPE_JOB ='XL'
	END
	IF(EXISTS(SELECT RoleName FROM TL_USER WHERE TLNANME =@TLNAME AND RoleName IN ('GDDV','GDK','PTGD') AND @TYPE_JOB ='XL'))
	BEGIN
			SELECT -1 Result, N'Nếu chọn vai trò xử lý, bạn không được phép chọn người dùng thuộc nhóm quyền lãnh đạo. Vui lòng chọn người dùng khác xử lý phiếu' ErrorDesc 
			ROLLBACK TRANSACTION
			RETURN '-1'
	END
  IF (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB = 'XL' AND STATUS_JOB = 'P'))
  BEGIN
    SELECT -1 Result, N'Điều phối thất bại, phiếu đã được xử lý trước đó' ErrorDesc 
  	ROLLBACK TRANSACTION
  	RETURN '-1'
  END
  IF (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB = @TYPE_JOB AND TLNAME = @TLNAME))
  BEGIN
    SELECT -1 Result, N'Điều phối thất bại, nhân viên đã được điều phối cùng vai trò trước đó' ErrorDesc 
  	ROLLBACK TRANSACTION
  	RETURN '-1'
  END
		SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB 
			FROM dbo.PL_REQUEST_PROCESS_CHILD 
			WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID 
			ORDER BY LEVEL_JOB DESC),0)

		-- BAT DAU GAN GIA TRI
		SET @ID_RETURN_FOR_EMAIL = @ID_RETURN_FOR_EMAIL +','+@REQ_ID
		-----------------------------------------------------------
    -- TH: Công việc đã được điều phối trước đó
		IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB ))
		BEGIN
      -- TH: Phiếu đã được điều phối cho người khác xử lý trước đó
			IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB = @TYPE_JOB AND @TYPE_JOB = 'XL' AND TLNAME <> @TLNAME))
  		BEGIN
        IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME))
        BEGIN
          SET @USER_PROCESS_LEVEL = (SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME)
          DELETE PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME
          UPDATE PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB = LEVEL_JOB - 1 WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB > @USER_PROCESS_LEVEL
          -- Ghi đè người xử lý
  			  UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@TLNAME WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB
        END
        ELSE
        BEGIN
          -- Ghi đè người xử lý
  			  UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@TLNAME WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB
        END   
		  END
      ELSE
      BEGIN
        -- TH: Người dùng đã được điều phối trước đó
        IF (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME))
        BEGIN
          SET @USER_PROCESS_LEVEL = (SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME)
          IF (@TYPE_JOB = 'XL' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME and TYPE_JOB = 'KS'))
          BEGIN
            DELETE PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME
            UPDATE PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB = LEVEL_JOB - 1, STATUS_JOB = 'U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB > @USER_PROCESS_LEVEL
            INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
        		(
        		    REQ_ID,
        		    PROCESS_ID,
        		    TLNAME,
        		    TYPE_JOB,
        		    LEVEL_JOB,
        		    STATUS_JOB,
        		    RECORD_STATUS
        		)
        		VALUES
        		(   @REQ_ID, -- REQ_ID - varchar(20)
        		    @REF_ID, -- PROCESS_ID - varchar(20)
        		    @TLNAME, -- TLNAME - varchar(50)
        		    @TYPE_JOB, -- TYPE_JOB - varchar(50)
        		    @LEVEL,  -- LEVEL_JOB - int
        		    'C', -- STATUS_JOB - varchar(10)
        		    '1'  -- RECORD_STATUS - varchar(15)
    		    )
          END
          ELSE IF (@TYPE_JOB = 'KS' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME and TYPE_JOB = 'XL'))
          BEGIN
            DELETE PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME
            INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
        		(
        		    REQ_ID,
        		    PROCESS_ID,
        		    TLNAME,
        		    TYPE_JOB,
        		    LEVEL_JOB,
        		    STATUS_JOB,
        		    RECORD_STATUS
        		)
        		VALUES
        		(   @REQ_ID, -- REQ_ID - varchar(20)
        		    @REF_ID, -- PROCESS_ID - varchar(20)
        		    @TLNAME, -- TLNAME - varchar(50)
        		    @TYPE_JOB, -- TYPE_JOB - varchar(50)
        		    @LEVEL,  -- LEVEL_JOB - int
        		    'C', -- STATUS_JOB - varchar(10)
        		    '1'  -- RECORD_STATUS - varchar(15)
    		    )
          END
        END
        ELSE
        BEGIN
          IF (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID and TYPE_JOB = 'XL'))
          BEGIN
            UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB = LEVEL_JOB + 1 WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL
        		INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
        		(
        		    REQ_ID,
        		    PROCESS_ID,
        		    TLNAME,
        		    TYPE_JOB,
        		    LEVEL_JOB,
        		    STATUS_JOB,
        		    RECORD_STATUS
        		)
        		VALUES
        		(   @REQ_ID, -- REQ_ID - varchar(20)
        		    @REF_ID, -- PROCESS_ID - varchar(20)
        		    @TLNAME, -- TLNAME - varchar(50)
        		    @TYPE_JOB, -- TYPE_JOB - varchar(50)
        		    @LEVEL,  -- LEVEL_JOB - int
        		    'U', -- STATUS_JOB - varchar(10)
        		    '1'  -- RECORD_STATUS - varchar(15)
    		    )
          END
          ELSE
          BEGIN
            UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB = 'U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL
            INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
        		(
        		    REQ_ID,
        		    PROCESS_ID,
        		    TLNAME,
        		    TYPE_JOB,
        		    LEVEL_JOB,
        		    STATUS_JOB,
        		    RECORD_STATUS
        		)
        		VALUES
        		(   @REQ_ID, -- REQ_ID - varchar(20)
        		    @REF_ID, -- PROCESS_ID - varchar(20)
        		    @TLNAME, -- TLNAME - varchar(50)
        		    @TYPE_JOB, -- TYPE_JOB - varchar(50)
        		    @LEVEL + 1,  -- LEVEL_JOB - int
        		    'C', -- STATUS_JOB - varchar(10)
        		    '1'  -- RECORD_STATUS - varchar(15)
    		    )
          END
        END 
      END
      SET @TYPE_JOB_NAME =(SELECT TOP 1 CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ')
			SET @FULLNAME =(SELECT TOP 1 TLFullName FROM dbo.TL_USER WHERE TLNANME=@TLNAME)
			
			INSERT INTO dbo.PL_PROCESS
			(
			    REQ_ID,
			    PROCESS_ID,
			    CHECKER_ID,
			    APPROVE_DT,
			    PROCESS_DESC,
			    NOTES
			)
			VALUES
			(   @REQ_ID,        -- REQ_ID - varchar(15)
			    @PROCESS_ID,        -- PROCESS_ID - varchar(10)
			    @p_MAKER_ID,        -- CHECKER_ID - varchar(15)
			    GETDATE(), -- APPROVE_DT - datetime
			    N'Nhân viên tiếp nhận ' + @FULLNAME,       -- PROCESS_DESC - nvarchar(1000)
			    N'Điều phối '+@TYPE_JOB_NAME        -- NOTES - nvarchar(1000)
	    )
		END
    -- TH: Công việc chưa được điều phối trước đó
		ELSE
		BEGIN
		IF(@LEVEL=0)
		BEGIN
			SET @LEVEL=@LEVEL +1
				INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
			(
				REQ_ID,
				PROCESS_ID,
				TLNAME,
				TYPE_JOB,
				LEVEL_JOB,
				STATUS_JOB,
				RECORD_STATUS
			)
			VALUES
			(   @REQ_ID, -- REQ_ID - varchar(20)
				@REF_ID, -- PROCESS_ID - varchar(20)
				@p_MAKER_ID, -- TLNAME - varchar(50)
				'TP', -- TYPE_JOB - varchar(50)
				@LEVEL,  -- LEVEL_JOB - int
				'U', -- STATUS_JOB - varchar(10)
				'1'  -- RECORD_STATUS - varchar(15)
			)
		END

		IF(@TYPE_JOB='KS' AND EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL'))
		BEGIN
      IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL' AND TLNAME = @TLNAME))
      BEGIN
        UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TYPE_JOB = 'KS' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID  AND TLNAME = @TLNAME
      END
      ELSE
      BEGIN
        UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB=@LEVEL + 1  WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID  AND TYPE_JOB='XL'
  			SET @LEVEL=@LEVEL-1
  			UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL
        INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
  			(
  				REQ_ID,
  				PROCESS_ID,
  				TLNAME,
  				TYPE_JOB,
  				LEVEL_JOB,
  				STATUS_JOB,
  				RECORD_STATUS
  			)
  			VALUES
  			(   @REQ_ID, -- REQ_ID - varchar(20)
  				@REF_ID, -- PROCESS_ID - varchar(20)
  				@TLNAME, -- TLNAME - varchar(50)
  				@TYPE_JOB, -- TYPE_JOB - varchar(50)
  				@LEVEL + 1,  -- LEVEL_JOB - int
  				'U', -- STATUS_JOB - varchar(10)
  				'1'  -- RECORD_STATUS - varchar(15)
  		  )
      END
		END
		ELSE
		BEGIN
      IF (@TYPE_JOB = 'XL' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='KS' AND TLNAME = @TLNAME))
      BEGIN
        SET @USER_PROCESS_LEVEL = (SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME)
        DELETE PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TLNAME = @TLNAME
        UPDATE PL_REQUEST_PROCESS_CHILD SET LEVEL_JOB = LEVEL_JOB - 1, STATUS_JOB = 'U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB > @USER_PROCESS_LEVEL
        INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
    		(
    		    REQ_ID,
    		    PROCESS_ID,
    		    TLNAME,
    		    TYPE_JOB,
    		    LEVEL_JOB,
    		    STATUS_JOB,
    		    RECORD_STATUS
    		)
    		VALUES
    		(   @REQ_ID, -- REQ_ID - varchar(20)
    		    @REF_ID, -- PROCESS_ID - varchar(20)
    		    @TLNAME, -- TLNAME - varchar(50)
    		    @TYPE_JOB, -- TYPE_JOB - varchar(50)
    		    @LEVEL,  -- LEVEL_JOB - int
    		    'C', -- STATUS_JOB - varchar(10)
    		    '1'  -- RECORD_STATUS - varchar(15)
		    )
      END
      ELSE
      BEGIN
        UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND LEVEL_JOB=@LEVEL
    		INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
    		(
    		    REQ_ID,
    		    PROCESS_ID,
    		    TLNAME,
    		    TYPE_JOB,
    		    LEVEL_JOB,
    		    STATUS_JOB,
    		    RECORD_STATUS
    		)
    		VALUES
    		(   @REQ_ID, -- REQ_ID - varchar(20)
    		    @REF_ID, -- PROCESS_ID - varchar(20)
    		    @TLNAME, -- TLNAME - varchar(50)
    		    @TYPE_JOB, -- TYPE_JOB - varchar(50)
    		    @LEVEL + 1,  -- LEVEL_JOB - int
    		    'C', -- STATUS_JOB - varchar(10)
    		    '1'  -- RECORD_STATUS - varchar(15)
		    )
      END
		END
		SET @TYPE_JOB_NAME =(SELECT TOP 1 CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@TYPE_JOB)
		SET @FULLNAME =(SELECT  TOP 1 TLFullName FROM dbo.TL_USER WHERE TLNANME=@TLNAME)
			

		INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @REQ_ID,        -- REQ_ID - varchar(15)
					    @PROCESS_ID,        -- PROCESS_ID - varchar(10)
					    @p_MAKER_ID,        -- CHECKER_ID - varchar(15)
					    GETDATE(), -- APPROVE_DT - datetime
					    N'Nhân viên tiếp nhận ' + @FULLNAME,       -- PROCESS_DESC - nvarchar(1000)
					    N'Điều phối '+@TYPE_JOB_NAME        -- NOTES - nvarchar(1000)
					    )
		END
		FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID
END
CLOSE ListREQ

DEALLOCATE ListREQ
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT 0 as Result, @ID_RETURN_FOR_EMAIL AS ErrorDesc
RETURN 0
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 as Result, '' ErrorDesc
		RETURN -1
End