ALTER PROCEDURE [dbo].[TR_REQ_PAY_TRANSFER_Upd]
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_TRANSFER_TYPE	varchar(15)	= NULL,
@p_XMP_TEMP XML = NULL
AS
--Validation is here
/* 
*/
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_XMP_TEMP
	DECLARE TransferData CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/XmlData',2)
	WITH 
	(
		REQ_PAY_ID	varchar(15)  ,
		TRANSFER_MAKER	nvarchar(15) ,
		TRASFER_USER_RECIVE	varchar(15),
		TRANSFER_ACTION VARCHAR(15)
	)
	OPEN TransferData
	BEGIN TRANSACTION
		
		DECLARE @REQ_PAY_ID VARCHAR(15), @TRANSFER_MAKER VARCHAR(15),@TRASFER_USER_RECIVE VARCHAR(15),@TRANSFER_ACTION VARCHAR(15)
		DECLARE @INDEX INT =0
		FETCH NEXT FROM TransferData INTO @REQ_PAY_ID, @TRANSFER_MAKER, @TRASFER_USER_RECIVE,@TRANSFER_ACTION
		WHILE @@FETCH_STATUS = 0	
		BEGIN
				DELETE FROM  PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_PAY_ID
				SET @INDEX = @INDEX+1
				-- KIEM TRA NEU CHUA CHON NHAN VIEN XU LY SE BAO LOI
				IF(@TRASFER_USER_RECIVE ='' OR @TRASFER_USER_RECIVE IS NULL)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Nhân viên được giao xử lý không được phép để trống' ErrorDesc
					RETURN '-1'
				END
				IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@REQ_PAY_ID AND AUTH_STATUS_KT='A'))
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Phiếu đề nghị tạm ứng đã được kế toán duyệt' ErrorDesc
					RETURN '-1'
				END
				IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@REQ_PAY_ID AND AUTH_STATUS_KT='A'))
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Phiếu đề nghị thanh toán đã được kế toán duyệt' ErrorDesc
					RETURN '-1'
				END
				--IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@REQ_PAY_ID AND MAKER_ID_KT IS NOT NULL AND MAKER_ID_KT <>''))
				--BEGIN
				--	ROLLBACK TRANSACTION
				--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Phiếu đề nghị tạm ứng đã được kế toán cập nhật thông tin' ErrorDesc
				--	RETURN '-1'
				--END
				--IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@REQ_PAY_ID AND MAKER_ID_KT IS NOT NULL AND MAKER_ID_KT <>''))
				--BEGIN
				--	ROLLBACK TRANSACTION
				--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Phiếu đề nghị tạm ứng đã được kế toán cập nhật thông tin' ErrorDesc
				--	RETURN '-1'
				--END
				--IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@REQ_PAY_ID AND MAKER_ID_KT IS NOT NULL))
				--BEGIN
				--	ROLLBACK TRANSACTION
				--	SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Phiếu đề nghị thanh toán đã được kế toán cập nhật thông tin' ErrorDesc
				--	RETURN '-1'
				--END
				IF(NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_PAY_ID AND TLNAME =@TRANSFER_MAKER))
				BEGIN
					INSERT INTO PL_REQUEST_PROCESS_CHILD(REQ_ID,PROCESS_ID,TLNAME,TYPE_JOB,LEVEL_JOB,STATUS_JOB,RECORD_STATUS)
					VALUES (@REQ_PAY_ID,NULL,@TRANSFER_MAKER,'KS',CONVERT(VARCHAR(5),'1'),'P','1')
				END
				-- KHAI BAO MAX LEVEL
				DECLARE @MAX_LEVEL VARCHAR(5), @NEXT_LEVEL INT,  @PREV_LEVEL INT
				SET @MAX_LEVEL= (SELECT MAX(LEVEL_JOB) FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID=@REQ_PAY_ID)
				SET @NEXT_LEVEL = (SELECT CONVERT(INT,@MAX_LEVEL)) +1
				SET @PREV_LEVEL = (SELECT CONVERT(INT,@MAX_LEVEL)) -1
				IF(@TRANSFER_ACTION ='XL')
				BEGIN
					--UPDATE TR_REQ_ADVANCE_PAYMENT SET 
					--AUTH_STATUS_KT ='U',
					--TRANSFER_DT = CONVERT(DATE,GETDATE(),103),
					--TRANSFER_MAKER = @TRANSFER_MAKER,
					--TRASFER_USER_RECIVE = @TRASFER_USER_RECIVE
					--WHERE REQ_PAY_ID =@REQ_PAY_ID
					IF(NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_PAY_ID  AND TYPE_JOB =@TRANSFER_ACTION))
					BEGIN
						INSERT INTO PL_REQUEST_PROCESS_CHILD(REQ_ID,PROCESS_ID,TLNAME,TYPE_JOB,LEVEL_JOB,STATUS_JOB,RECORD_STATUS)
						VALUES (@REQ_PAY_ID,NULL,@TRASFER_USER_RECIVE,'XL',CONVERT(VARCHAR(5),@NEXT_LEVEL),'C','1')
						INSERT INTO PL_PROCESS VALUES (@REQ_PAY_ID,'TRANSF',@p_TRANSFER_MAKER, GETDATE(),N'Điều phối về nhân viên ' +@TRASFER_USER_RECIVE,N'Điều phối thanh toán/ tạm ứng')
					END
					ELSE
					BEGIN
						UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C',TYPE_JOB=@TRANSFER_ACTION,TLNAME = @TRASFER_USER_RECIVE WHERE REQ_ID =@REQ_PAY_ID  AND TYPE_JOB =@TRANSFER_ACTION
					END
					-- UPDATE BUOC TRUOC DO VE P
					--UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P',TYPE_JOB='KS' WHERE REQ_ID =@REQ_PAY_ID  AND LEVEL_JOB =@MAX_LEVEL
				END
				ELSE
				BEGIN
					IF(NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@REQ_PAY_ID  AND TYPE_JOB =@TRANSFER_ACTION))
					BEGIN
						INSERT INTO PL_REQUEST_PROCESS_CHILD(REQ_ID,PROCESS_ID,TLNAME,TYPE_JOB,LEVEL_JOB,STATUS_JOB,RECORD_STATUS)
						VALUES (@REQ_PAY_ID,NULL,@TRASFER_USER_RECIVE,'KS',CONVERT(VARCHAR(5),@NEXT_LEVEL),'C','1')
					END
					ELSE
					BEGIN
						UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C',TYPE_JOB='KS' WHERE REQ_ID =@REQ_PAY_ID  AND TLNAME =@TRASFER_USER_RECIVE
					END
					-- UPDATE BUOC TRUOC DO VE P
					--UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P',TYPE_JOB='KS' WHERE REQ_ID =@REQ_PAY_ID  AND LEVEL_JOB =@MAX_LEVEL
				END
			IF(@p_TRANSFER_TYPE='A')
			BEGIN
				UPDATE TR_REQ_ADVANCE_PAYMENT SET 
				AUTH_STATUS_KT ='U'
				WHERE REQ_PAY_ID =@REQ_PAY_ID
			END
			ELSE
			BEGIN
				UPDATE TR_REQ_PAYMENT SET 
				AUTH_STATUS_KT ='U'
				WHERE REQ_PAY_ID =@REQ_PAY_ID	
			END
		IF @@ERROR <> 0 GOTO ABORT
		FETCH NEXT FROM TransferData INTO @REQ_PAY_ID, @TRANSFER_MAKER, @TRASFER_USER_RECIVE,@TRANSFER_ACTION
		END
	CLOSE TransferData
	DEALLOCATE TransferData
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result, ''  REQ_PAY_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE TransferData
		DEALLOCATE TransferData
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
End