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)

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'
	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
		-----------------------------------------------------------
		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 WHERE REQ_ID=@REQ_ID AND PROCESS_ID=@REF_ID AND TYPE_JOB=@TYPE_JOB
					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
		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
			)
			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
			ELSE
			BEGIN
				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

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