/*INSERT INTO SYS_PREFIX(ID,Prefix,[Description]) VALUES('CM_REJECT_LOG','CMJL','')
INSERT INTO SYS_CODEMASTERS(Prefix,CurValue,Active) VALUES('CMJL','1','1')*/
ALTER PROC [dbo].[CM_REJECT_LOG_ByType_Ins]
@p_LOG_ID	varchar(15)	= NULL,
@p_STAGE	varchar(5)	 = NULL,
@p_TRN_ID	varchar(15)	 = NULL,
@p_TRN_TYPE	nvarchar(100)	 = NULL,
@p_LOG_DT	VARCHAR	 = NULL,
@p_AUTH_STAT	varchar(3)	 = NULL,
@p_REASON	nvarchar(500)	 = NULL,
@p_IS_LATEST	varchar(1)	 = NULL,
@p_REJECTED_BY	varchar(20)	 = NULL,
@p_REJECTED_DT	VARCHAR	 = NULL,
@p_TYPE VARCHAR(20) =NULL,
@p_PROCESS_ID VARCHAR(20),
@p_REF_ID INT=NULL
AS
BEGIN TRANSACTION
DECLARE @PARENT_PROCESS_ID VARCHAR(20),@ROLE_ID VARCHAR(20),@ROLE_DES NVARCHAR(200),@ROLE_PARENT_ID VARCHAR(20),@ROLE_DES_PARENT NVARCHAR(200),@COMPLETE BIT
					
SET @COMPLETE=0
		DECLARE @p_MESSAGE NVARCHAR(100) = NULL
		IF(EXISTS (SELECT * FROM CM_REJECT_LOG WHERE TRN_ID = @p_TRN_ID))
		BEGIN
			UPDATE CM_REJECT_LOG SET IS_LATEST ='N' WHERE TRN_ID =@p_TRN_ID
		END
	
		-- END PHIEU DE THI THANH TOAN 19-11-2019

			--- PHIEU YEU CAU MUA SAM
				IF(@p_TRN_TYPE='PL_REQUEST_DOC')
				BEGIN	IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID='APPROVE'))
				BEGIN
					SET @p_MESSAGE =N'Trả về thất bại! Tờ trình chủ trương đã được duyệt'
					ROLLBACK TRANSACTION
					SELECT '0' as Result, @p_LOG_ID  LOG_ID ,@p_MESSAGE ErrorDesc
					RETURN '0'
				END
				ELSE
				BEGIN
				IF(@p_TYPE='CREATE')
				BEGIN
				UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID='REJECT',AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=N'Tờ trình bị trả về' WHERE PROCESS_ID='APPNEW' AND REQ_ID=@p_TRN_ID
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID <>'APPNEW' AND REQ_ID=@p_TRN_ID
				INSERT INTO dbo.PL_PROCESS
				(
				REQ_ID,
				PROCESS_ID,
				CHECKER_ID,
			    APPROVE_DT,
			    PROCESS_DESC,
				NOTES
				)
				VALUES
				(   @p_TRN_ID,        -- REQ_ID - varchar(15)
					'REJECT',        -- PROCESS_ID - varchar(10)
					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)
					GETDATE(), -- APPROVE_DT - datetime
					@p_REASON,
					N'Trả về người tạo'
					       -- PROCESS_DESC - nvarchar(1000)
				)
				 SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
				 SET @COMPLETE=1
				END
				ELSE IF(@p_TYPE='NVXL')
				BEGIN
					UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID 
					--UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'	
					--LUCTV 28 09 2020 DIEU CHINH LOI TRA VE NHUNG NHAN VIEN XU LY KHONG DUOC PHEP TRA VE TIEP
					UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'	
					IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'))
					BEGIN
						UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB ='KS'
					END
				INSERT INTO dbo.PL_PROCESS
				(
				REQ_ID,
				PROCESS_ID,
				CHECKER_ID,
			    APPROVE_DT,
			    PROCESS_DESC,
				NOTES
				)
				VALUES
				(   @p_TRN_ID,        -- REQ_ID - varchar(15)
					'REJECT',        -- PROCESS_ID - varchar(10)
					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)
					GETDATE(), -- APPROVE_DT - datetime
					@p_REASON,
					N'Trả về nhân viên xử lý'
					       -- PROCESS_DESC - nvarchar(1000)
				)
				 SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
				 	 SET @COMPLETE=1
				END
				ELSE IF(@p_TYPE='PRE_APP')
				BEGIN
					--SET @PARENT_PROCESS_ID =(SELECT TOP 1 PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
					--SET @ROLE_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
					--SET @ROLE_PARENT_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID)
					--SET @ROLE_DES=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)
					--SET @ROLE_DES_PARENT=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_PARENT_ID)
					
					--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=@ROLE_DES + N' đã trả về, chờ ' + @ROLE_DES_PARENT +N' xử lý' WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID

					--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID
					--UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PARENT_PROCESS_ID WHERE REQ_ID=@p_TRN_ID
					--IF(@PARENT_PROCESS_ID='APPNEW')
					--	UPDATE dbo.PL_REQUEST_DOC SET AUTH_STATUS='U' WHERE REQ_ID=@p_TRN_ID

					--INSERT INTO dbo.PL_PROCESS
					--(
					--REQ_ID,
					--PROCESS_ID,
					--CHECKER_ID,
					--APPROVE_DT,
					--PROCESS_DESC,
					--NOTES
					--)
					--VALUES
					--(   @p_TRN_ID,        -- REQ_ID - varchar(15)
					--	'REJECT',        -- PROCESS_ID - varchar(10)
					--	@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)
					--	GETDATE(), -- APPROVE_DT - datetime
					--	@p_REASON,N'Trả về ' + @ROLE_DES_PARENT
					--	       -- PROCESS_DESC - nvarchar(1000)
					--)
					 SET @p_MESSAGE =N'Tạm thời bạn nên trả phiếu về người tạo'
					 SET @COMPLETE=0
				END
				-- SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
			END		
			END
			ELSE IF(@p_TRN_TYPE='TR_REQUEST_DOC')
			BEGIN
			--IF(@p_STAGE='DVMS')
			--BEGIN
			--END
		
				IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID='APPROVE'))
				BEGIN
					SET @p_MESSAGE =N'Trả về thất bại! Phiếu yêu cầu đã được duyệt'
					ROLLBACK TRANSACTION
					SELECT '0' as Result, @p_LOG_ID  LOG_ID ,@p_MESSAGE ErrorDesc
					RETURN '0'
				END
				ELSE
				BEGIN
				IF(@p_TYPE='CREATE')
				BEGIN
				UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID='REJECT',AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=N'Phiếu bị trả về' WHERE PROCESS_ID='APPNEW' AND REQ_ID=@p_TRN_ID
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID <>'APPNEW' AND REQ_ID=@p_TRN_ID
				INSERT INTO dbo.PL_PROCESS
				(
				REQ_ID,
				PROCESS_ID,
				CHECKER_ID,
			    APPROVE_DT,
			    PROCESS_DESC,
				NOTES
				)
				VALUES
				(   @p_TRN_ID,        -- REQ_ID - varchar(15)
					'REJECT',        -- PROCESS_ID - varchar(10)
					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)
					GETDATE(), -- APPROVE_DT - datetime
					@p_REASON,N'Trả về người tạo'
					       -- PROCESS_DESC - nvarchar(1000)
				)
					 SET @COMPLETE=1
				 SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
				END
				ELSE IF(@p_TYPE='NVXL')
				BEGIN
				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='U' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID 
				--UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'
				--LUCTV 28 09 2020 DIEU CHINH LOI TRA VE NHUNG NHAN VIEN XU LY KHONG DUOC PHEP TRA VE TIEP
				--KIEM TRA NGUOI DIEU PHOI - NEU LA TP - TRA VE CHO NHAN VIEN XU LY THI SE TRA VE CHO
				UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'
				IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB='XL'))
				BEGIN
					UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C' WHERE REQ_ID =@p_TRN_ID AND PROCESS_ID=@p_REF_ID AND TYPE_JOB ='KS'
				END
				INSERT INTO dbo.PL_PROCESS
				(
				REQ_ID,
				PROCESS_ID,
				CHECKER_ID,
			    APPROVE_DT,
			    PROCESS_DESC,
				NOTES
				)
				VALUES
				(   @p_TRN_ID,        -- REQ_ID - varchar(15)
					'REJECT',        -- PROCESS_ID - varchar(10)
					@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)
					GETDATE(), -- APPROVE_DT - datetime
					@p_REASON,N'Trả về nhân viên xử lý'
					       -- PROCESS_DESC - nvarchar(1000)
				)
					 SET @COMPLETE=1
				 SET @p_MESSAGE =N'Phiếu yêu cầu đã được trả về thành công'
				END
				ELSE IF(@p_TYPE='PRE_APP')
				BEGIN
					--SET @PARENT_PROCESS_ID =(SELECT TOP 1 PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
					--SET @ROLE_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID)
					--SET @ROLE_PARENT_ID=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID)
					--SET @ROLE_DES=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)
					--SET @ROLE_DES_PARENT=(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_PARENT_ID)
					
					--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='R',NOTES=@ROLE_DES + N' đã trả về, chờ ' + @ROLE_DES_PARENT +N' xử lý' WHERE PROCESS_ID=@PARENT_PROCESS_ID AND REQ_ID=@p_TRN_ID
					--IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID=@PARENT_PROCESS_ID AND IS_HAS_CHILD=1))
					--BEGIN
					--	UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='R' WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID IN (SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_TRN_ID AND PROCESS_ID=@PARENT_PROCESS_ID AND IS_HAS_CHILD=1) AND LEVEL_JOB=1
					--END
					
					--IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE  REQ_ID =@p_TRN_ID AND PROCESS_ID='DVCM'))
					--BEGIN
					--		UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='R' WHERE REQ_ID=@p_TRN_ID
					--END
					
					--UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='U' WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_TRN_ID
					--UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PARENT_PROCESS_ID WHERE REQ_ID=@p_TRN_ID
					--IF(@PARENT_PROCESS_ID='APPNEW')
					--	UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U' WHERE REQ_ID=@p_TRN_ID


					--INSERT INTO dbo.PL_PROCESS
					--(
					--REQ_ID,
					--PROCESS_ID,
					--CHECKER_ID,
					--APPROVE_DT,
					--PROCESS_DESC,NOTES
					--)
					--VALUES
					--(   @p_TRN_ID,        -- REQ_ID - varchar(15)
					--	'REJECT',        -- PROCESS_ID - varchar(10)
					--	@p_REJECTED_BY,        -- CHECKER_ID - varchar(15)
					--	GETDATE(), -- APPROVE_DT - datetime
					--	@p_REASON,N'Trả về ' + @ROLE_DES_PARENT
					--	       -- PROCESS_DESC - nvarchar(1000)
					--)
					SET @p_MESSAGE =N'Tạm thời bạn nên trả phiếu về người tạo'
						 SET @COMPLETE=0
				END
				
				
				END	
			
		END

		-- END IF
		ELSE
		BEGIN
			SET @p_MESSAGE =N'Trả về giao dịch thành công'
		END

		--- INSERT VAO BANG CM_REJECT_LOG
		IF(@COMPLETE=1)
		BEGIN
		EXEC SYS_CodeMasters_Gen 'CM_REJECT_LOG', @p_LOG_ID out
		IF @p_LOG_ID='' OR @p_LOG_ID IS NULL GOTO ABORT
		INSERT INTO [dbo].[CM_REJECT_LOG] VALUES (@p_LOG_ID,@p_STAGE,@p_TRN_ID,@p_TRN_TYPE,CONVERT(DATETIME,GETDATE(),103),'R',@p_REASON,@p_IS_LATEST,@p_REJECTED_BY,CONVERT(DATETIME,GETDATE(),103))
		IF @@Error <> 0 GOTO ABORT
		END
COMMIT TRANSACTION

SELECT '0' as Result, @p_LOG_ID  LOG_ID ,@p_MESSAGE ErrorDesc
RETURN '0'
ABORT:
BEGIN

		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' LOG_ID, '' ErrorDesc
		RETURN '-1'
END