ALTER   PROCEDURE [dbo].[TR_REQ_PROCESS_CHILD_Ins]
@p_MAKER_ID VARCHAR(20),
@p_XMLData XML
AS
BEGIN TRANSACTION
		--- KHAI BAO CHUOI ID
		DECLARE @ID_RETURN_FOR_EMAIL VARCHAR(500) =''
		-----------------------------------------------------

		DECLARE @p_ROW_NUM INT = 0
		DECLARE @p_REQ_CODE VARCHAR(50)

		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),@REF_ID INT, @TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)

		FETCH NEXT FROM ListREQ INTO @REQ_ID,@PROCESS_ID,@TLNAME,@TYPE_JOB,@REF_ID
		WHILE @@FETCH_STATUS=0
		BEGIN

			SET @p_ROW_NUM = @p_ROW_NUM + 1
			SET @p_REQ_CODE = (SELECT TOP 1 REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID = @REQ_ID)

			IF(@TLNAME IS NULL OR @TLNAME = '')
			BEGIN
				CLOSE ListREQ
				DEALLOCATE ListREQ
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, 
					N'Điều phối phiếu yêu cầu số ' +@p_REQ_CODE+
					N'. Vui lòng chọn tên người được giao xử lý' ErrorDesc
				RETURN '-1'
			END

		-- BAT DAU GAN GIA TRI
		SET @ID_RETURN_FOR_EMAIL = @ID_RETURN_FOR_EMAIL +','+@REQ_ID
		--NEU LA DVKD THI MAC DINH LA CAP XU LY PHIEU - LUCTV 
		-- XOA QUYEN CU CUA USER
		DELETE FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_ID AND TLNAME =@TLNAME AND (STATUS_JOB ='C' OR PROCESS_ID =@REF_ID)
		IF(EXISTS(SELECT * FROM CM_DMMS WHERE BRANCH_ID <> 'DV0001' AND DMMS_ID =(SELECT TOP 1 DMMS_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@REQ_ID)))
		BEGIN
			IF(@TYPE_JOB ='KS')
			BEGIN
				SET @TYPE_JOB ='XL'
			END
		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)
		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
			IF(EXISTS(SELECT * 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
					UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET TLNAME=@TLNAME,MAKER_ID=@p_MAKER_ID,TRANFER_DT=GETDATE() WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB
					SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL = @TYPE_JOB)
					SET @FULLNAME =(SELECT 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
		END
		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
			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
			IF (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB='XL' AND STATUS_JOB='C'))
			BEGIN
				INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
			(
		    REQ_ID,
		    PROCESS_ID,
		    TLNAME,
		    TYPE_JOB,
		    LEVEL_JOB,
		    STATUS_JOB,
		    RECORD_STATUS,
			MAKER_ID,
			TRANFER_DT
			)
			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)
			@p_MAKER_ID,
			GETDATE()
		    )
			END
			ELSE
			BEGIN
					INSERT INTO dbo.PL_REQUEST_PROCESS_CHILD
			(
		    REQ_ID,
		    PROCESS_ID,
		    TLNAME,
		    TYPE_JOB,
		    LEVEL_JOB,
		    STATUS_JOB,
		    RECORD_STATUS,
			MAKER_ID,
			TRANFER_DT
			)
			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)
			@p_MAKER_ID,
			GETDATE()
		    )
			END

		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,
				MAKER_ID,
				TRANFER_DT
				)
				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)
				@p_MAKER_ID,
				GETDATE()
				)
		END
			SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@TYPE_JOB)
			SET @FULLNAME =(SELECT 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 ErrorDesc
RETURN 0
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT -1 as Result, '' ErrorDesc
		RETURN -1
End